消息102，级别15，状态1，行38 [英] Msg 102, level 15, state 1, line 38

查看：80
本文介绍了消息102，级别15，状态1，行38的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

USE [FMSUAT]

GO

/ ******对象：StoredProcedure [dbo]。[sp_Generate_GL_Journals_NEW]脚本日期：11/20/2018 7 ：57：47 AM ****** /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO



- ============================== ===============

- 作者：<作者，Sean Cudd / Kathy Hurley>

- 创建日期：<创建日期，11/30 / 2009,11 / 15/2018>

- 说明：<说明,,从AS400生成PeopleSoft G / L日记帐分录发布总帐批次，使用工作表>

- ================================== ===========

ALTER PROCEDURE [dbo]。[sp_Generate_GL_Journals_NEW]

- 在此处添加存储过程的参数

@System char（10），

@Library char（10），

@busUnit char（5），

@ acctPeriod int = 0，

@batchNbr int = 0





AS

BEGIN

LINENO 0

- 添加SET NOCOUNT ON以防止来自

的额外结果集 - 干扰SELECT语句。

SET NOCOUNT ON;



DECLARE @SchemaPath varchar（50）;

DECLARE @timestamp varchar（50）;

DECLARE @GLAcct char（10）;

DECLARE @IC_Flag varchar（1）;

DECLARE @Inter_Company varchar（1000）;

DECLARE @Non_Inter_Company varchar（1000）;

DECLARE @Is_It_IC varchar（1000）;





SET @SchemaPath = dbo.GetAS400SchemaPath（@System）+ @Library;



SET @IC_Flag ='';

SET @GLAcct ='';





SET @timestamp = cast（@acctPeriod as char（6））+ CAST（15 AS CHAR（2））;



从PS_CG_WF_PRIOR_JGN​​删除;

EXEC dbo.sp_Generate_Pre_JGEN_file @System，@ Library，@ busUnit，@ acctPeriod，@ batnbr;





--- Th以下插入是非公司间批次----



SET @Non_Inter_Company =插入PS_JGEN_ACCT_ENTRY

（SEQUENCENO，
BUSINESS_UNIT，

TRANSACTION_ID，

LEDGER_GROUP，

LEDGER，

ACCOUNTING_DT，
APPL_JRNL_ID，

BUSINESS_UNIT_GL，

FISCAL_YEAR，

ACCOUNTING_PERIOD，

JOURNAL_ID，
JOURNAL_DATE，

JOURNAL_LINE，

账户，

ALTACCT，

DEPTID，
OPERATING_UNIT，

产品，

FUND_CODE，

CLASS_FLD，

PROGRAM_CODE，
BUDGET_REF，

附属公司，

AFFILIATE_INTRA1，

AFFILIATE_INTRA2，

CHARTFIELD1，

CHARTFIELD2，

CHARTFIELD3，

PROJECT_ID，

CURRENCY_CD，

STATISTICS_CODE，

FOREIGN_CURRENCY，

RT_TYPE，

RATE_MULT，

RATE_DIV，

MONETARY_AMOUNT，

FOREIGN_AMOUNT，

STATISTIC_AMOUNT，

MOVEMENT_FLAG，

DOC_TYPE，

DOC_SEQ_NBR，

DOC_SEQ_DATE，

JRNL_LN_REF，

LINE_DESCR，

IU_SYS_TRAN_CD，

IU_TRAN_CD，

IU_ANCHOR_FLG，

GL_DISTRIB_STATUS，

PROCESS_INSTANCE ，

DTTM_STAMP）

SELECT ROW_NUMBER（）OVER（订购TRANSACTION_ID）作为SEQUENCENO，*来自

（SELECT

'+ @ busUnit +'AS BUSINESS_UNIT，

TRANSACTION_ID，

'ACTUALS'作为LEDGER_GROUP，

'实际'作为LEDGER ，

'+ @timestamp +'AS ACCOUNTING_DT，

'GENERIC'As APPL_JRNL_ID，

CASE WHEN'+ @ busUnit + '<> CG_GLACCOUNT2然后CG_GLACCOUNT2 ELSE'+ @ busUnit +'结束为BUSINESS_UNIT_GL，

FISCAL_YEAR，

ACCOUNTING_PERIOD，

''AS JOURNAL_ID，

'+ @timestamp +'AS JOURNAL_DT，

JOURNAL_LINE，

账户，

ALTACCT，

DEPTID，

OPERATING_UNIT，

产品，

FUND_CODE，

CLASS_FLD，

PROGRAM_CODE，

BUDGET_REF，

附属公司，

AFFILIATE_INTRA1，

AFFILIATE_INTRA2，

CHARTFIELD1，

CHARTFIELD2，

CHARTFIELD3，

PROJECT_ID，

CURRENCY_CD，

STATISTICS_CODE，

FOREIGN_CURRENCY，

RT_TYPE，

RATE_MULT，

RATE_DIV，

MONETARY_AMOUNT，

FOREIGN_AMOUNT，

STATISTIC_AMOUNT，

MOVEMENT_FLAG，

DOC_TYPE，

DOC_SEQ_NBR，

DOC_SEQ_DATE，

JRNL_LN_REF，

LINE_DESCR，

IU_SYS_TRAN_CD，

IU_TRAN_CD，

IU_ANCHOR_FLG，

GL_DISTRIB_STATUS，

PROCESS_INSTANCE，

DTTM_STAMP

来自PS_CG_WF_PRIOR_JGN​​）GL_ENTRIES;





---以下是对于公司间批次



SET @Inter_Company =INSERT INTO PS_JGEN_ACCT_ENTRY

（SEQUENCENO，

BUSINESS_UNIT，

TRANSACTION_ID，

LEDGER_GROUP，

LEDGER，

ACCOUNTING_DT，

APPL_JRNL_ID，

BUSINESS_UNIT_GL，

FISCAL_YEAR，

ACCOUNTING_PERIOD，

JOURNAL_ID，

JOURNAL_DATE，

JOURNAL_LINE，

帐户，

ALTACCT，

DEPTID，

OPERATING_UNIT，

产品，

FUND_CODE，

CLASS_FLD，

PROGRAM_CODE，

BUDGET_REF，

联盟，

AFFILIATE_INTRA1，

AFFILIATE_INTRA2，

CHARTFIELD1，

CHARTFIELD2，

CHARTFIELD3，

PROJECT_ID，

CURRENCY_CD，

STATISTICS_CODE，

FOREIGN_CURRENCY，

RT_TYPE，

RATE_MULT，

RATE_DIV，

MONETARY_AMOUNT，

FOREIGN_AMOUNT，

STATISTIC_AMOUNT，

MOVEMENT_FLAG，

DOC_TYPE，

DOC_SEQ_NBR，

DOC_SEQ_DATE，

JRNL_LN_REF，

LINE_DESCR，

IU_SYS_TRAN_CD，

IU_TRAN_CD，

IU_ANCHOR_FLG，

GL_DISTRIB_STATUS，

PROCESS_INSTANCE，

DTTM_STAMP）

SELECT ROW_NUMBER（）OVER（按TRANSACTION_ID排序）作为SEQUENCENO，*来自



（SELECT

'+ @ busUnit +'AS BUSINESS_UNIT，

TRANSACTION_ID，

'ACTUALS'AS LEDGER_GROUP，

'实际'为LEDGER，

'+ @timestamp +'AS ACCOUNTING_DT，

'GENERIC'为APPL_JRNL_ID，
'+ @ busUnit +'AS BUSINESS_UNIT_GL，

FISCAL_YEAR，

ACCOUNTING_PERIOD，

''AS JOURNAL_ID，

'+ @timestamp +'AS JOURNAL_DT，

JOURNAL_LINE，

账户，

ALTACCT，

DEPTID，

OPERATING_UNIT，

产品，

FUND_CODE，

CLASS_FLD，

PROGRAM_CODE，

BUDGET_REF，

关联，

AFFILIATE_INTRA1，

AFFILIATE_INTRA2，

CHARTFIELD1，

CHARTFIELD2，

CHARTFIELD3，

PROJECT_ID，

CURRENCY_CD，

STATISTICS_CODE，

FOREIGN_CURRENCY，

RT_TYPE，

RATE_MULT，

RATE_DIV，

MONETARY_AMOUNT，

FOREIGN_AMOUNT，

STATI STIC_AMOUNT，

MOVEMENT_FLAG，

DOC_TYPE，

DOC_SEQ_NBR，

DOC_SEQ_DATE，

JRNL_LN_REF，

LINE_DESCR，

IU_SYS_TRAN_CD，

IU_TRAN_CD，

IU_ANCHOR_FLG，

GL_DISTRIB_STATUS，

PROCESS_INSTANCE，

DTTM_STAMP

来自PS_CG_WF_PRIOR_JGN​​

UNION

SELECT

'+ @ busUnit +'AS BUSINESS_UNIT，

TRANSACTION_ID，

'ACTABLE'作为LEDGER_GROUP，

'实际'作为LEDGER，

'+ @timestamp +'AS ACCOUNTING_DT，

'GEN ERIC'ASPL_JRNL_ID，

CASE WHEN'+ @ busUnit +'<> CG_GLACCOUNT2然后CG_GLACCOUNT2 ELSE'+ @ busUnit +'结束为BUSINESS_UNIT_GL，

FISCAL_YEAR，

ACCOUNTING_PERIOD，

''AS JOURNAL_ID，

'+ @timestamp +'AS JOURNAL_DT，

JOURNAL_LINE，

账户，

ALTACCT，

DEPTID，

OPERATING_UNIT，

产品，

FUND_CODE，

CLASS_FLD，

PROGRAM_CODE，

BUDGET_REF，

附属公司，

AFFILIATE_INTRA1，

AFFILIATE_INTRA2，

CHARTFIELD1，

CHARTFIELD2，

CHART FIELD3，

PROJECT_ID，

CURRENCY_CD，

STATISTICS_CODE，

FOREIGN_CURRENCY，

RT_TYPE，

RATE_MULT，

RATE_DIV，

MONETARY_AMOUNT，

FOREIGN_AMOUNT，

STATISTIC_AMOUNT，

MOVEMENT_FLAG，

DOC_TYPE，

DOC_SEQ_NBR，

DOC_SEQ_DATE，

JRNL_LN_REF，

LINE_DESCR，

IU_SYS_TRAN_CD，

IU_TRAN_CD，

IU_ANCHOR_FLG，

GL_DISTRIB_STATUS，

PROCESS_INSTANCE，

DTTM_STAMP

来自PS_CG_WF_PRIOR_JGN​​）GL_ENTRIES;



----检查这是否是公司间批次并相应地调用相应的插入语句。



SELECT @IC_Flag = CG_INTERCOMPANY来自PS_CG_WF_PRIOR_JGN​​;



PRINT @IC_Flag;



IF（@ IC_Flag ='Y'）

BEGIN

打印'我是公司间';

EXEC（@Inter_Company）;

PRINT'从@Inter_company的执行官回来';

结束



ELSE





BEGIN

打印'它不是公司间';

EXEC（@Non_Inter_Company）;

PRINT'从@Non_Inter_company的执行官回来';

结束



结束



我尝试了什么：



我有谷歌搜索错误，在BEGIN之后放置LINENO 0试图找到与错误一致，并运行SQL独立。没运气。任何想法都将不胜感激。

USE [FMSUAT]
GO
/****** Object: StoredProcedure [dbo].[sp_Generate_GL_Journals_NEW] Script Date: 11/20/2018 7:57:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

-- =============================================
-- Author: <Author,,Sean Cudd/Kathy Hurley>
-- Create date: <Create Date,,11/30/2009, 11/15/2018>
-- Description: <Description,,Generate PeopleSoft G/L Journal Entries from the AS400 posted G/L Batches,using work table>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Generate_GL_Journals_NEW]
-- Add the parameters for the stored procedure here
@System char(10),
@Library char(10),
@busUnit char(5),
@acctPeriod int = 0,
@batchNbr int = 0


AS
BEGIN
LINENO 0
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @SchemaPath varchar(50);
DECLARE @timestamp varchar(50);
DECLARE @GLAcct char(10);
DECLARE @IC_Flag varchar(1);
DECLARE @Inter_Company varchar(1000);
DECLARE @Non_Inter_Company varchar(1000);
DECLARE @Is_It_IC varchar(1000);


SET @SchemaPath = dbo.GetAS400SchemaPath(@System) + @Library;

SET @IC_Flag = ' ';
SET @GLAcct = ' ';


SET @timestamp = cast(@acctPeriod as char(6)) + CAST(15 AS CHAR(2));

DELETE FROM PS_CG_WF_PRIOR_JGN;
EXEC dbo.sp_Generate_Pre_JGEN_file @System, @Library, @busUnit, @acctPeriod, @batchNbr;


--- The following insert is for non intercompany batch ----

SET @Non_Inter_Company = "INSERT INTO PS_JGEN_ACCT_ENTRY
(SEQUENCENO,
BUSINESS_UNIT,
TRANSACTION_ID,
LEDGER_GROUP,
LEDGER,
ACCOUNTING_DT,
APPL_JRNL_ID,
BUSINESS_UNIT_GL,
FISCAL_YEAR,
ACCOUNTING_PERIOD,
JOURNAL_ID,
JOURNAL_DATE,
JOURNAL_LINE,
ACCOUNT,
ALTACCT,
DEPTID,
OPERATING_UNIT,
PRODUCT,
FUND_CODE,
CLASS_FLD,
PROGRAM_CODE,
BUDGET_REF,
AFFILIATE,
AFFILIATE_INTRA1,
AFFILIATE_INTRA2,
CHARTFIELD1,
CHARTFIELD2,
CHARTFIELD3,
PROJECT_ID,
CURRENCY_CD,
STATISTICS_CODE,
FOREIGN_CURRENCY,
RT_TYPE,
RATE_MULT,
RATE_DIV,
MONETARY_AMOUNT,
FOREIGN_AMOUNT,
STATISTIC_AMOUNT,
MOVEMENT_FLAG,
DOC_TYPE,
DOC_SEQ_NBR,
DOC_SEQ_DATE,
JRNL_LN_REF,
LINE_DESCR,
IU_SYS_TRAN_CD,
IU_TRAN_CD,
IU_ANCHOR_FLG,
GL_DISTRIB_STATUS,
PROCESS_INSTANCE,
DTTM_STAMP)
SELECT ROW_NUMBER() OVER (ORDER BY TRANSACTION_ID) AS SEQUENCENO, * FROM
( SELECT
'" + @busUnit + "' AS BUSINESS_UNIT,
TRANSACTION_ID,
'ACTUALS' AS LEDGER_GROUP,
'ACTUALS' AS LEDGER,
'" + @timestamp + "' AS ACCOUNTING_DT,
'GENERIC' AS APPL_JRNL_ID,
CASE WHEN '" + @busUnit + "' <> CG_GLACCOUNT2 THEN CG_GLACCOUNT2 ELSE '" + @busUnit + "' END AS BUSINESS_UNIT_GL,
FISCAL_YEAR,
ACCOUNTING_PERIOD,
'' AS JOURNAL_ID,
'" + @timestamp + "' AS JOURNAL_DT,
JOURNAL_LINE,
ACCOUNT,
ALTACCT,
DEPTID,
OPERATING_UNIT,
PRODUCT,
FUND_CODE,
CLASS_FLD,
PROGRAM_CODE,
BUDGET_REF,
AFFILIATE,
AFFILIATE_INTRA1,
AFFILIATE_INTRA2,
CHARTFIELD1,
CHARTFIELD2,
CHARTFIELD3,
PROJECT_ID,
CURRENCY_CD,
STATISTICS_CODE,
FOREIGN_CURRENCY,
RT_TYPE,
RATE_MULT,
RATE_DIV,
MONETARY_AMOUNT,
FOREIGN_AMOUNT,
STATISTIC_AMOUNT,
MOVEMENT_FLAG,
DOC_TYPE,
DOC_SEQ_NBR,
DOC_SEQ_DATE,
JRNL_LN_REF,
LINE_DESCR,
IU_SYS_TRAN_CD,
IU_TRAN_CD,
IU_ANCHOR_FLG,
GL_DISTRIB_STATUS,
PROCESS_INSTANCE,
DTTM_STAMP
FROM PS_CG_WF_PRIOR_JGN ) GL_ENTRIES ";


--- The following is for an intercompany batch

SET @Inter_Company = "INSERT INTO PS_JGEN_ACCT_ENTRY
(SEQUENCENO,
BUSINESS_UNIT,
TRANSACTION_ID,
LEDGER_GROUP,
LEDGER,
ACCOUNTING_DT,
APPL_JRNL_ID,
BUSINESS_UNIT_GL,
FISCAL_YEAR,
ACCOUNTING_PERIOD,
JOURNAL_ID,
JOURNAL_DATE,
JOURNAL_LINE,
ACCOUNT,
ALTACCT,
DEPTID,
OPERATING_UNIT,
PRODUCT,
FUND_CODE,
CLASS_FLD,
PROGRAM_CODE,
BUDGET_REF,
AFFILIATE,
AFFILIATE_INTRA1,
AFFILIATE_INTRA2,
CHARTFIELD1,
CHARTFIELD2,
CHARTFIELD3,
PROJECT_ID,
CURRENCY_CD,
STATISTICS_CODE,
FOREIGN_CURRENCY,
RT_TYPE,
RATE_MULT,
RATE_DIV,
MONETARY_AMOUNT,
FOREIGN_AMOUNT,
STATISTIC_AMOUNT,
MOVEMENT_FLAG,
DOC_TYPE,
DOC_SEQ_NBR,
DOC_SEQ_DATE,
JRNL_LN_REF,
LINE_DESCR,
IU_SYS_TRAN_CD,
IU_TRAN_CD,
IU_ANCHOR_FLG,
GL_DISTRIB_STATUS,
PROCESS_INSTANCE,
DTTM_STAMP)
SELECT ROW_NUMBER() OVER (ORDER BY TRANSACTION_ID) AS SEQUENCENO, * FROM

( SELECT
'" + @busUnit + "' AS BUSINESS_UNIT,
TRANSACTION_ID,
'ACTUALS' AS LEDGER_GROUP,
'ACTUALS' AS LEDGER,
'" + @timestamp + "' AS ACCOUNTING_DT,
'GENERIC' AS APPL_JRNL_ID,
'" + @busUnit + "' AS BUSINESS_UNIT_GL,
FISCAL_YEAR,
ACCOUNTING_PERIOD,
'' AS JOURNAL_ID,
'" + @timestamp + "' AS JOURNAL_DT,
JOURNAL_LINE,
ACCOUNT,
ALTACCT,
DEPTID,
OPERATING_UNIT,
PRODUCT,
FUND_CODE,
CLASS_FLD,
PROGRAM_CODE,
BUDGET_REF,
AFFILIATE,
AFFILIATE_INTRA1,
AFFILIATE_INTRA2,
CHARTFIELD1,
CHARTFIELD2,
CHARTFIELD3,
PROJECT_ID,
CURRENCY_CD,
STATISTICS_CODE,
FOREIGN_CURRENCY,
RT_TYPE,
RATE_MULT,
RATE_DIV,
MONETARY_AMOUNT,
FOREIGN_AMOUNT,
STATISTIC_AMOUNT,
MOVEMENT_FLAG,
DOC_TYPE,
DOC_SEQ_NBR,
DOC_SEQ_DATE,
JRNL_LN_REF,
LINE_DESCR,
IU_SYS_TRAN_CD,
IU_TRAN_CD,
IU_ANCHOR_FLG,
GL_DISTRIB_STATUS,
PROCESS_INSTANCE,
DTTM_STAMP
FROM PS_CG_WF_PRIOR_JGN
UNION
SELECT
'" + @busUnit + "' AS BUSINESS_UNIT,
TRANSACTION_ID,
'ACTUALS' AS LEDGER_GROUP,
'ACTUALS' AS LEDGER,
'" + @timestamp + "' AS ACCOUNTING_DT,
'GENERIC' AS APPL_JRNL_ID,
CASE WHEN '" + @busUnit + "' <> CG_GLACCOUNT2 THEN CG_GLACCOUNT2 ELSE '" + @busUnit + "' END AS BUSINESS_UNIT_GL,
FISCAL_YEAR,
ACCOUNTING_PERIOD,
'' AS JOURNAL_ID,
'" + @timestamp + "' AS JOURNAL_DT,
JOURNAL_LINE,
ACCOUNT,
ALTACCT,
DEPTID,
OPERATING_UNIT,
PRODUCT,
FUND_CODE,
CLASS_FLD,
PROGRAM_CODE,
BUDGET_REF,
AFFILIATE,
AFFILIATE_INTRA1,
AFFILIATE_INTRA2,
CHARTFIELD1,
CHARTFIELD2,
CHARTFIELD3,
PROJECT_ID,
CURRENCY_CD,
STATISTICS_CODE,
FOREIGN_CURRENCY,
RT_TYPE,
RATE_MULT,
RATE_DIV,
MONETARY_AMOUNT,
FOREIGN_AMOUNT,
STATISTIC_AMOUNT,
MOVEMENT_FLAG,
DOC_TYPE,
DOC_SEQ_NBR,
DOC_SEQ_DATE,
JRNL_LN_REF,
LINE_DESCR,
IU_SYS_TRAN_CD,
IU_TRAN_CD,
IU_ANCHOR_FLG,
GL_DISTRIB_STATUS,
PROCESS_INSTANCE,
DTTM_STAMP
FROM PS_CG_WF_PRIOR_JGN ) GL_ENTRIES";

---- check to see if this is an intercompany batch and call the appropriate insert statement accordingly.

SELECT @IC_Flag = CG_INTERCOMPANY FROM PS_CG_WF_PRIOR_JGN;

PRINT @IC_Flag;

IF (@IC_Flag = 'Y')
BEGIN
PRINT ' Y it is inter company';
EXEC(@Inter_Company);
PRINT 'Back from exec of @Inter_company';
END

ELSE


BEGIN
PRINT ' N its not intercompany' ;
EXEC(@Non_Inter_Company);
PRINT 'Back from exec of @Non_Inter_company';
END

END

What I have tried:

I have googled error, placed the LINENO 0 after the BEGIN to try to locate the line with the error, and ran the SQL standalone. No luck. Any thoughts would be greatly appreciated.

推荐答案

你从哪里复制/粘贴它？向他们（作者）询问你的问题！



此外 - 我们不知道你的数据表是什么样的或任何东西。



换句话说： 你知道你在做什么吗？
Where did you copy/paste it from? Ask them (the authors) about your problem!

Furthermore - we have no clue as to what your data tables look like or anything.

Put another way:   have you any idea at all what you are doing?


确定的问题：你正在构建的命令超过1800个字符，你将它放入 DECLARE @Non_Inter_Company varchar（1000）;

次要警告：变量应该是NVarChar。

可能的问题：连接在一起的连接问题查询。



建议：

1-将变量更改为nvarchar（4000）

2-尝试不连接的项目
Definite problem: the command you are building is well over 1800 characters, and you are placing it into DECLARE @Non_Inter_Company varchar(1000);
Secondary warning: the variable should be NVarChar.
Possible problem: concatenation issues with the pieced together query.

Recommendations:
1- Change the variable to nvarchar(4000)
2- Try without the concatenated items


这篇关于消息102，级别15，状态1，行38的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆