Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more: (untagged)
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.
Posted
Updated 20-Nov-18 4:03am

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?
 
Share this answer
 
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900