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.