Declare @C_Id int = 1 , @LoginUser_Id int = 1, @Month int = 1, @Year int = 2023, @CurrencyToId int =1, @P_Id int = 123, @I_Id int = 156, @fromCt int = 1, @toCt int = 40, @BatchId VARCHAR(10) = '01' SET NOCOUNT ON; -- Insert statements for procedure here /* --===Transaction Data==== */ IF OBJECT_ID('TempDB..#VCHR_HDR') IS NOT NULL DROP TABLE #VCHR_HDR SELECT A1.Name As [BUSINESS_UNIT], IM.Number AS [ID], CONVERT(VARCHAR(26), IM.BillingDate, 23) As [DT], 'CORP' AS [SETID], AIM.Code AS [_ID], ACM.Custom1 AS [LOC], ACM.Custom2 AS [ADDRESS_SEQ_NUM], ACM.Custom3 AS [ADDR_SEQ_NUM], CONVERT(VARCHAR(26), IM.CreatedDate, 23) AS [DT], CAST(ROUND(IM.TotalAmount,2) as varchar) AS [GROSS_AMT], CUM.CurrencyAcronym AS [CURRENCY_CD], CONVERT(VARCHAR(26), IM.CreatedDate, 23) AS [DTTM_CREATED], CONVERT(VARCHAR(26), IM.CreatedDate, 23) AS [DTTM_MODIFIED], (CONVERT(VARCHAR(10), IM.PeriodFrom, 105) +' to '+ CONVERT(VARCHAR(10), IM.PeriodTo, 105)) AS [DESCR], A10.AName As [DESCR254_MIXED], CONVERT(MONEY, IPFD.AllocatedCharges) AS [MERCHANDISE_AMT], '1' AS [DISTRIB_LINE_NUM], A2.AName As [UNIT_GL], A3.AName As [code], A4.AName As [DEPTID], A5.AName As [PROJECT_ID], A7.AName As [ACTIVITY_ID], A6.AName As [RESOURCE_TYPE], A8.AnName As [RESOURCE_CATEGORY], ROW_NUMBER() over (ORDER BY IPFD.FileDetailId ASC) AS ID, ROW_NUMBER() over (ORDER BY IPFD.FileDetailId ASC) AS [VOUCHER_LINE_NUM], ROW_NUMBER() over (ORDER BY IPFD.FileDetailId ASC) AS LineId INTO #VCHR_HDR FROM PaymentFiles IPF INNER JOIN FilesDetails IPFD ON IPFD.FileId =IPF.Id INNER JOIN Master IM ON IM.Id = IPFD.id INNER JOIN AMaster AM ON IM.Id = AM.Id INNER JOIN ArMaster AVM ON AM.Id = AVM.Id INNER JOIN AmMaster ACM ON ACM.Id = AM.Id INNER JOIN Aemaster AIM ON AM.Id =AIM.Id INNER JOIN CMaster CUM ON CUM.Id =AM.CurrencyCode LEFT JOIN AMaster A1 ON IPFD.Allocation1 = A1.Id AND A1.TypeId = 1 LEFT JOIN AMaster A2 ON IPFD.Allocation2 = A2.Id AND A2.TypeId = 2 LEFT JOIN AMaster A3 ON IPFD.Allocation3 = A3.Id AND A3.TypeId = 3 LEFT JOIN AMaster A4 ON IPFD.Allocation4 = A4.Id AND A4.TypeId = 4 LEFT JOIN AMaster A5 ON IPFD.Allocation5 = A5.Id AND A5.TypeId = 5 LEFT JOIN AMaster A6 ON IPFD.Allocation6 = A6.Id AND A6.TypeId = 6 LEFT JOIN AMaster A7 ON IPFD.Allocation7 = A7.Id AND A7.TypeId = 7 LEFT JOIN AMaster A8 ON IPFD.Allocation8 = A8.Id AND A8.TypeId = 8 LEFT JOIN AMaster A10 ON IPFD.Allocation10 = A10.Id AND A10.TypeId = 10 INNER JOIN ClnMaster COM ON AM.Id = COM.Id INNER JOIN CtMaster CM ON COM.Id = CM.Id where COM.Id = @C_Id AND IPFD.eId = @P_Id AND IPFD.Charges <> 0 AND IM.Id = @I_Id /* --=========== */ IF OBJECT_ID('TempDB..#VCHR_HDR_QV') IS NOT NULL DROP TABLE #VCHR_HDR_QV SELECT TOP 1 hdr.[BUSINESS_UNIT], 'NEXT' As [VOUCHER_ID], --(CASE WHEN (SELECT COUNT(A.ID) FROM #VCHR_HDR A) <= 200 THEN hdr.[_ID] -- ELSE CONCAT(hdr.[_ID], '-', @BatchId) -- END)AS [_ID], hdr.[_ID], hdr.[_DT], hdr.[_SETID], hdr.[_ID], hdr.[VNDR_LOC], hdr.[ADDRESS_SEQ_NUM], hdr.[REMIT_ADDR_SEQ_NUM], '' AS [GRP_AP_ID], 'TEM' AS [ORIGIN], 'GSG' AS [OPRID], hdr.[ACCOUNTING_DT], '' AS [DST_CNTRL_ID], '' AS [VOUCHER_ID_RELATED], (SELECT CAST(ROUND(SUM(T.[MERCHANDISE_AMT]),2) as varchar) FROM #VCHR_HDR AS T WHERE T.ID BETWEEN @fromCt AND @toCt) AS [GROSS_AMT], '0' AS [DSCNT_AMT], '0' AS [SALETX_AMT], '0' AS [FREIGHT_AMT], '' AS [PYMNT_TERMS_CD], hdr.[TXN_CURRENCY_CD], '' AS [RT_TYPE], '0' AS [RATE_MULT], '0' AS [RATE_DIV], '0' AS [_ENTRD_AMT], '' AS [MATCH_ACTION], '' AS [MATCH_STATUS_VCHR], '' AS [CUR_RT_SOURCE], '' AS [DSCNT_AMT_FLG], '' AS [DUE_DT_FLG], '' AS [DUE_DT], '' AS [DSCNT_DUE_DT], '' AS [_DCLRTN_POINT], '' AS [_CALC_TYPE], '0' AS [_RECOVERY_PCT], '' AS [_CALC_GROSS_NET], '' AS [_RECALC_FLG], '' AS [_CALC_FRGHT_FLG], '' AS [_RGSTRN_SELLER], '' AS [COUNTRY_SHIP_FROM], '' AS [STATE_SHIP_FROM], '' AS [COUNTRY_SHIP_TO], '' AS [STATE_SHIP_TO], '' AS [COUNTRY_LOC_SELLER], '' AS [STATE_LOC_SELLER], '' AS [COUNTRY_LOC_BUYER], '' AS [STATE_LOC_BUYER], '' AS [COUNTRY__BILLFR], '' AS [COUNTRY__BILLTO], '' AS [COUNTRY__SUPPLY], '' AS [STATE__SUPPLY], '' AS [COUNTRY__PERFRM], '' AS [STATE__PERFRM], '' AS [STATE__DEFAULT], '' AS [_EXCPTN_TYPE], '' AS [_EXCPTN_CERTIF], '' AS [DESCR254_MIXED], '0' AS [PROCESS_INSTANCE], '' AS [BUSINESS_UNIT_PO], '' AS [PO_ID], '' AS [BILL_OF_LADING], '' AS [CARRIER_ID], '' AS [PACKSLIP_NO], '0' AS [MISC_CHRG_AMT], '' AS [SHIP_DATE], '' AS [RECEIPT_DT], '' AS [RECEIPT_DOC_DT], '' AS [BUSINESS_UNIT_RECV], '' AS [RECEIVER_ID], 'N' AS [IN_PROCESS_FLG], '' AS [VCHR_BLD_CODE], '' AS [TAX_EXEMPT], '' AS [_CALC_MISC_FLG], '' AS [PAY_TRM_BSE_DT_OPT], 'B' AS [VCHR_BLD_STATUS], hdr.[DTTM_CREATED], hdr.[DTTM_MODIFIED], '' AS [DTTM_PROCESSED], '0' AS [VCHR_BLD_COUNT], 'N' AS [VCHR_BLD_PAY_OPT], '0' AS [VCHR_PREPAY_AMT], '' AS [PRO_NUMBER], 'N' AS [VCHR_BLD_ERROR_FLG], 'REG' AS [VOUCHER_STYLE], '' AS [INSPECT_DT], '' AS [INV_RECPT_DT], '' AS [IMAGE_DATE], '' AS [IMAGE_REF_ID], '' AS [PAY_SCHEDULE_TYPE], '' AS [VCHR_APPRVL_FLG], '' AS [APPR_RULE_SET], '' AS [BUSPROCNAME], '' AS [LC_ID], 'D' AS [POST_VOUCHER], '' AS [DOC_TYPE], '' AS [DSCNT_PRORATE_FLG], 'XML' AS [VCHR_SRC], '' AS [MISC_CHARGE_CODE], '' AS [FRGHT_CHARGE_CODE], '' AS [SALETX_CHARGE_CODE], '' AS [CNTRCT_SETID], '' AS [CNTRCT_ID], '0' AS [ECQUEUEINSTANCE], '' AS [ECTRANSID], '' AS [ECTRANSINOUTSW], '' AS [EIN_FEDERAL], '' AS [EIN_STATE_LOCAL], '0' AS [ERS_INV_SEQ], '' AS [ERS_TYPE], 'N' AS [PREPAID_AUTO_APPLY], '' AS [PREPAID_REF], '' AS [TAX_GRP], '' AS [TAX_PYMNT_TYPE], '' AS [_CF_ANLSYS_TYPE], '' AS [_ROUND_RULE], '' AS [_TREATMENT_GRP], '' AS [VOUCHER_TYPE], (Select COUNT(T.ID) From #VCHR_HDR AS T WHERE T.ID BETWEEN @fromCt AND @toCt) AS [VCHR_TTL_LINES], (Select COUNT(T.ID) From #VCHR_HDR AS T WHERE T.ID BETWEEN @fromCt AND @toCt) AS [TOTAL_DISTRIBS], '' AS [BUSINESS_UNIT_AR], '' AS [CUST_ID], '' AS [ITEM], '0' AS [ITEM_LINE], 'C' AS [VCHR_QV_TEMPLATE], '' AS [REQUESTOR_ID], '0' AS [LS_KEY], '' AS [USER_VCHR_CHAR1], '' AS [USER_VCHR_CHAR2], '0' AS [USER_VCHR_DEC], '' AS [USER_VCHR_DATE], '0' AS [USER_VCHR_NUM1], '' AS [USER_HDR_CHAR1], '' AS [MATCH_CNTRL_ID], '' AS [TERMS_BASIS_DT] INTO #VCHR_HDR_QV FROM #VCHR_HDR AS hdr IF OBJECT_ID('TempDB..#VCHR_LINE_QV') IS NOT NULL DROP TABLE #VCHR_LINE_QV SELECT hdr.ID, hdr.[BUSINESS_UNIT], 'NEXT' AS [VOUCHER_ID], ROW_NUMBER() over (ORDER BY (SELECT 1)) AS [VOUCHER_LINE_NUM], '' AS [BUSINESS_UNIT_PO], '' AS [PO_ID], '0' AS [LINE_NBR], '0' AS [SCHED_NBR], hdr.[DESCR], hdr.[DESCR254_MIXED], CAST(ROUND(hdr.[MERCHANDISE_AMT],2) as Varchar) AS [MERCHANDISE_AMT], '' AS [ITM_SETID], '' AS [INV_ITEM_ID], '0' AS [QTY_VCHR], '' AS [UNIT_OF_MEASURE], '0' AS [UNIT_PRICE], '' AS [TAX_CD_], '' AS [BUSINESS_UNIT_RECV], '' AS [RECEIVER_ID], '0' AS [RECV_LN_NBR], '0' AS [RECV_SHIP_SEQ_NBR], '' AS [MATCH_LINE_OPT], 'A' AS [DISTRIB_MTHD_FLG], hdr.[TXN_CURRENCY_CD], '' AS [SHIPTO_ID], '0' AS [ADDR_SEQ_NUM_SHIP], '' AS [SUT_BASE_ID], '' AS [TAX_CD_SUT], '' AS [SUT_EXCPTN_TYPE], '' AS [SUT_EXCPTN_CERTIF], '' AS [_TXN_TYPE_CD], '' AS [_USE_ID], '0' AS [PROCESS_INSTANCE], '' AS [BUSINESS_UNIT_GL], '' AS [ACCOUNT], '' AS [ALTACCT], '' AS [DEPTID], '' AS [OPERATING_UNIT], '' AS [PRODUCT], '' AS [FUND_CODE], '' AS [CLASS_FLD], '' AS [PROGRAM_CODE], '' AS [BUDGET_REF], '' AS [AFFILIATE], '' AS [AFFILIATE_INTRA1], '' AS [AFFILIATE_INTRA2], '' AS [CHARTFIELD1], '' AS [CHARTFIELD2], '' AS [CHARTFIELD3], '' AS [BUSINESS_UNIT_PC], '' AS [PROJECT_ID], '' AS [ACTIVITY_ID], '' AS [RESOURCE_TYPE], '' AS [RESOURCE_CATEGORY], '' AS [RESOURCE_SUB_CAT], '' AS [ANALYSIS_TYPE], '' AS [ENTRY_EVENT], 'N' AS [VCHR_BLD_MATCH_FLG], '' AS [ULTIMATE_USE_CD], 'E' AS [SUT_APPLICABILITY], '' AS [_APPLICABILITY], '' AS [DSCNT_APPL_FLG], '' AS [SPEEDCHART_KEY], (Select COUNT(T.ID) From #VCHR_HDR AS T WHERE T.ID BETWEEN @fromCt AND @toCt) AS [MAX_DISTRIB_LINE], (Select COUNT(T.ID) From #VCHR_HDR AS T WHERE T.ID BETWEEN @fromCt AND @toCt) AS [TOTAL_DISTRIBS], '' AS [_ID], '' AS [VNDR_LOC], '' AS [VOUCHER_ID_RELATED], '' AS [BUS_UNIT_RELATED], '' AS [BUSINESS_UNIT_RTV], '' AS [CNTRCT_SETID], '' AS [CNTRCT_ID], '0' AS [CNTRCT_LINE_NBR], '0' AS [ECQUEUEINSTANCE], '' AS [ECTRANSID], '' AS [ECTRANSINOUTSW], '' AS [INSPECT_DT], '' AS [RECEIPT_DT], '' AS [RELEASE_NBR], '' AS [RTV_ID], '' AS [RTV_LN_NBR], '0' AS [STATISTIC_AMOUNT], 'N' AS [TAX_DSCNT_FLG], 'N' AS [TAX_FRGHT_FLG], 'N' AS [TAX_MISC_FLG], 'N' AS [TAX__FLG], '' AS [PHYSICAL_NATURE], '' AS [_RCRD_INPT_FLG], '' AS [_RCRD_OUTPT_FLG], '' AS [_TREATMENT], '' AS [_SVC_SUPPLY_FLG], '' AS [_SERVICE_TYPE], '' AS [COUNTRY_LOC_BUYER], '' AS [STATE_LOC_BUYER], '' AS [COUNTRY_LOC_SELLER], '' AS [STATE_LOC_SELLER], '' AS [COUNTRY__SUPPLY], '' AS [STATE__SUPPLY], '' AS [COUNTRY__PERFRM], '' AS [STATE__PERFRM], '' AS [STATE_SHIP_FROM], '' AS [STATE__DEFAULT], '' AS [JRNL_LN_REF], '' AS [OPEN_ITEM_KEY], '' AS [REQUESTOR_ID], '' AS [APPR_STATUS], '' AS [CATEGORY_ID], '' AS [SUT_FREIGHT_CD], '' AS [SUT_PRODUCT_CD], '' AS [TAX_GROUP], '' AS [TAX_JOB_NUM], '' AS [TITLE_PASSAGE], '' AS [SHIP_TYPE_ID], '' AS [TAX_USER_AREA], '0' AS [FREIGHT_TERMS], '' AS [_LN_ENT_AMT], '' AS [_RECEIPT], '' AS [_RGSTRN_SELLER], '' AS [TRANS_DT], '' AS [WTHD_SW], '' AS [WTHD_CD], '' AS [USER_VCHR_CHAR1], '' AS [USER_VCHR_CHAR2], '' AS [USER_VCHR_DEC], '' AS [USER_VCHR_DATE], '' AS [USER_VCHR_NUM1], '' AS [USER_LINE_CHAR1], '' AS [USER_SCHED_CHAR1] INTO #VCHR_LINE_QV FROM #VCHR_HDR AS hdr WHERE hdr.ID BETWEEN @fromCt AND @toCt IF OBJECT_ID('TempDB..#VCHR_DIST_QV') IS NOT NULL DROP TABLE #VCHR_DIST_QV SELECT 'R' AS '@class', hdr.ID, hdr.LineId, hdr.[BUSINESS_UNIT], 'NEXT' AS [VOUCHER_ID], ROW_NUMBER() over (ORDER BY (SELECT 1)) AS [VOUCHER_LINE_NUM], '1' AS [DISTRIB_LINE_NUM], hdr.[BUSINESS_UNIT_GL], hdr.[ACCOUNT], '' AS [ALTACCT], hdr.[DEPTID], '' AS [STATISTICS_CODE], '0' AS [STATISTIC_AMOUNT], '0' AS [QTY_VCHR], '' AS [DESCR], CAST(ROUND(hdr.[MERCHANDISE_AMT],2) as Varchar) AS [MERCHANDISE_AMT], '' AS [BUSINESS_UNIT_PO], '' AS [PO_ID], '0' AS [LINE_NBR], '0' AS [SCHED_NBR], '0' AS [PO_DIST_LINE_NUM], '' AS [BUSINESS_UNIT_AM], '' AS [PROFILE_ID], '' AS [_TXN_TYPE_CD], '' AS [_APORT_CNTRL], '' AS [BUSINESS_UNIT_RECV], '' AS [RECEIVER_ID], '0' AS [RECV_LN_NBR], '0' AS [RECV_DIST_LINE_NUM], '' AS [BUDGET_DT], '' AS [OPERATING_UNIT], '' AS [PRODUCT], '' AS [FUND_CODE], '' AS [CLASS_FLD], '' AS [PROGRAM_CODE], '' AS [BUDGET_REF], '' AS [AFFILIATE], '' AS [AFFILIATE_INTRA1], '' AS [AFFILIATE_INTRA2], '' AS [CHARTFIELD1], '' AS [CHARTFIELD2], '' AS [CHARTFIELD3], 'MIC00' As [BUSINESS_UNIT_PC], hdr.[PROJECT_ID], hdr.[ACTIVITY_ID], hdr.[RESOURCE_TYPE], hdr.[RESOURCE_CATEGORY], '' AS [RESOURCE_SUB_CAT], '' AS [ANALYSIS_TYPE], '0' AS [PROCESS_INSTANCE], '' AS [ASSET_FLG], '' AS [COST_TYPE], '' AS [ASSET_ID], '' AS [ENTRY_EVENT], '' AS [JRNL_LN_REF], '' AS [ECTRANSID], '' AS [ECQUEUEINSTANCE], '' AS [ECTRANSINOUTSW], '0' AS [RECV_SHIP_SEQ_NBR], hdr.[TXN_CURRENCY_CD], '' AS [USER_VCHR_CHAR1], '' AS [USER_VCHR_CHAR2], '0' AS [USER_VCHR_DEC], '' AS [USER_VCHR_DATE], '0' AS [USER_VCHR_NUM1], '' AS [USER_DIST_CHAR1], '' AS [OPEN_ITEM_KEY], '0' AS [_RECOVERY_PCT], '0' AS [_REBATE_PCT], '0' AS [_CALC_AMT], '0' AS [_BASIS_AMT], '0' AS [_RCVRY_AMT], '0' AS [_NRCVR_AMT], '0' AS [_REBATE_AMT], '0' AS [_TRANS_AMT], '0' AS [TAX_CD__PCT], '0' AS [_INV_AMT], '0' AS [_NONINV_AMT], '' AS [BUSINESS_UNIT_WO], '' AS [WO_ID], '0' AS [WO_TASK_ID], '' AS [RSRC_TYPE], '0' AS [RES_LN_NBR] INTO #VCHR_DIST_QV FROM #VCHR_HDR AS hdr WHERE hdr.ID BETWEEN @fromCt AND @toCt IF OBJECT_ID('TempDB..#PSCAMA') IS NOT NULL DROP TABLE #PSCAMA SELECT 'R' AS '@class', 'ENG' AS [LANGUAGE], 'A' AS [AUDIT_ACTN], 'ENG' AS [BASE_LANGUAGE_CD], '' AS [MSG_SEQ_FLG], '0' AS [PROCESS_INSTANCE], '' AS [PUBLISH_RULE_ID], '' AS [MSGNODENAME] INTO #PSCAMA /* --==FieldTypes Data==*/ /*--PSCAMA--*/ IF OBJECT_ID('TempDB..#PSCAMA_FieldTypes') IS NOT NULL DROP TABLE #PSCAMA_FieldTypes SELECT 'R' AS '@class', 'CHAR' AS 'LANGUAGE_CD/@type', 'CHAR' AS 'AUDIT_ACTN/@type', 'CHAR' AS 'BASE_LANGUAGE_CD/@type', 'CHAR' AS 'MSG_SEQ_FLG/@type', 'NUMBER' AS 'PROCESS_INSTANCE/@type', 'CHAR' AS 'PUBLISH_RULE_ID/@type', 'CHAR' AS 'MSGNODENAME/@type' INTO #PSCAMA_FieldTypes /*--VCHR_HDR_QV--*/ IF OBJECT_ID('TempDB..#VCHR_HDR_QV_FieldTypes') IS NOT NULL DROP TABLE #VCHR_HDR_QV_FieldTypes SELECT 'R' AS '@class', 'CHAR' AS 'BUSINESS_UNIT/@type', 'CHAR' AS 'VOUCHER_ID/@type', 'CHAR' AS '_ID/@type', 'DATE' AS '_DT/@type', 'CHAR' AS '_SETID/@type', 'CHAR' AS '_ID/@type', 'CHAR' AS 'VNDR_LOC/@type', 'NUMBER' AS 'ADDRESS_SEQ_NUM/@type', 'NUMBER' AS 'REMIT_ADDR_SEQ_NUM/@type', 'CHAR' AS 'GRP_AP_ID/@type', 'CHAR' AS 'ORIGIN/@type', 'CHAR' AS 'OPRID/@type', 'DATE' AS 'ACCOUNTING_DT/@type', 'CHAR' AS 'DST_CNTRL_ID/@type', 'CHAR' AS 'VOUCHER_ID_RELATED/@type', 'NUMBER' AS 'GROSS_AMT/@type', 'NUMBER' AS 'DSCNT_AMT/@type', 'NUMBER' AS 'SALETX_AMT/@type', 'NUMBER' AS 'FREIGHT_AMT/@type', 'CHAR' AS 'PYMNT_TERMS_CD/@type', 'CHAR' AS 'TXN_CURRENCY_CD/@type', 'CHAR' AS 'RT_TYPE/@type', 'NUMBER' AS 'RATE_MULT/@type', 'NUMBER' AS 'RATE_DIV/@type', 'NUMBER' AS '_ENTRD_AMT/@type', 'CHAR' AS 'MATCH_ACTION/@type', 'CHAR' AS 'MATCH_STATUS_VCHR/@type', 'CHAR' AS 'CUR_RT_SOURCE/@type', 'CHAR' AS 'DSCNT_AMT_FLG/@type', 'CHAR' AS 'DUE_DT_FLG/@type', 'DATE' AS 'DUE_DT/@type', 'DATE' AS 'DSCNT_DUE_DT/@type', 'CHAR' AS '_DCLRTN_POINT/@type', 'CHAR' AS '_CALC_TYPE/@type', 'NUMBER' AS '_RECOVERY_PCT/@type', 'CHAR' AS '_CALC_GROSS_NET/@type', 'CHAR' AS '_RECALC_FLG/@type', 'CHAR' AS '_CALC_FRGHT_FLG/@type', 'CHAR' AS '_RGSTRN_SELLER/@type', 'CHAR' AS 'COUNTRY_SHIP_FROM/@type', 'CHAR' AS 'STATE_SHIP_FROM/@type', 'CHAR' AS 'COUNTRY_SHIP_TO/@type', 'CHAR' AS 'STATE_SHIP_TO/@type', 'CHAR' AS 'COUNTRY_LOC_SELLER/@type', 'CHAR' AS 'STATE_LOC_SELLER/@type', 'CHAR' AS 'COUNTRY_LOC_BUYER/@type', 'CHAR' AS 'STATE_LOC_BUYER/@type', 'CHAR' AS 'COUNTRY__BILLFR/@type', 'CHAR' AS 'COUNTRY__BILLTO/@type', 'CHAR' AS 'COUNTRY__SUPPLY/@type', 'CHAR' AS 'STATE__SUPPLY/@type', 'CHAR' AS 'COUNTRY__PERFRM/@type', 'CHAR' AS 'STATE__PERFRM/@type', 'CHAR' AS 'STATE__DEFAULT/@type', 'CHAR' AS '_EXCPTN_TYPE/@type', 'CHAR' AS '_EXCPTN_CERTIF/@type', 'CHAR' AS 'DESCR254_MIXED/@type', 'NUMBER' AS 'PROCESS_INSTANCE/@type', 'CHAR' AS 'BUSINESS_UNIT_PO/@type', 'CHAR' AS 'PO_ID/@type', 'CHAR' AS 'BILL_OF_LADING/@type', 'CHAR' AS 'CARRIER_ID/@type', 'CHAR' AS 'PACKSLIP_NO/@type', 'NUMBER' AS 'MISC_CHRG_AMT/@type', 'DATE' AS 'SHIP_DATE/@type', 'DATE' AS 'RECEIPT_DT/@type', 'DATE' AS 'RECEIPT_DOC_DT/@type', 'CHAR' AS 'BUSINESS_UNIT_RECV/@type', 'CHAR' AS 'RECEIVER_ID/@type', 'CHAR' AS 'IN_PROCESS_FLG/@type', 'CHAR' AS 'VCHR_BLD_CODE/@type', 'CHAR' AS 'TAX_EXEMPT/@type', 'CHAR' AS '_CALC_MISC_FLG/@type', 'CHAR' AS 'PAY_TRM_BSE_DT_OPT/@type', 'CHAR' AS 'VCHR_BLD_STATUS/@type', 'DATETIME' AS 'DTTM_CREATED/@type', 'DATETIME' AS 'DTTM_MODIFIED/@type', 'DATETIME' AS 'DTTM_PROCESSED/@type', 'NUMBER' AS 'VCHR_BLD_COUNT/@type', 'CHAR' AS 'VCHR_BLD_PAY_OPT/@type', 'NUMBER' AS 'VCHR_PREPAY_AMT/@type', 'CHAR' AS 'PRO_NUMBER/@type', 'CHAR' AS 'VCHR_BLD_ERROR_FLG/@type', 'CHAR' AS 'VOUCHER_STYLE/@type', 'DATE' AS 'INSPECT_DT/@type', 'DATE' AS 'INV_RECPT_DT/@type', 'DATE' AS 'IMAGE_DATE/@type', 'CHAR' AS 'IMAGE_REF_ID/@type', 'CHAR' AS 'PAY_SCHEDULE_TYPE/@type', 'CHAR' AS 'VCHR_APPRVL_FLG/@type', 'CHAR' AS 'APPR_RULE_SET/@type', 'CHAR' AS 'BUSPROCNAME/@type', 'CHAR' AS 'LC_ID/@type', 'CHAR' AS 'POST_VOUCHER/@type', 'CHAR' AS 'DOC_TYPE/@type', 'CHAR' AS 'DSCNT_PRORATE_FLG/@type', 'CHAR' AS 'VCHR_SRC/@type', 'CHAR' AS 'MISC_CHARGE_CODE/@type', 'CHAR' AS 'FRGHT_CHARGE_CODE/@type', 'CHAR' AS 'SALETX_CHARGE_CODE/@type', 'CHAR' AS 'CNTRCT_SETID/@type', 'CHAR' AS 'CNTRCT_ID/@type', 'NUMBER' AS 'ECQUEUEINSTANCE/@type', 'CHAR' AS 'ECTRANSID/@type', 'CHAR' AS 'ECTRANSINOUTSW/@type', 'CHAR' AS 'EIN_FEDERAL/@type', 'CHAR' AS 'EIN_STATE_LOCAL/@type', 'NUMBER' AS 'ERS_INV_SEQ/@type', 'CHAR' AS 'ERS_TYPE/@type', 'CHAR' AS 'PREPAID_AUTO_APPLY/@type', 'CHAR' AS 'PREPAID_REF/@type', 'CHAR' AS 'TAX_GRP/@type', 'CHAR' AS 'TAX_PYMNT_TYPE/@type', 'CHAR' AS '_CF_ANLSYS_TYPE/@type', 'CHAR' AS '_ROUND_RULE/@type', 'CHAR' AS '_TREATMENT_GRP/@type', 'CHAR' AS 'VOUCHER_TYPE/@type', 'NUMBER' AS 'VCHR_TTL_LINES/@type', 'NUMBER' AS 'TOTAL_DISTRIBS/@type', 'CHAR' AS 'BUSINESS_UNIT_AR/@type', 'CHAR' AS 'CUST_ID/@type', 'CHAR' AS 'ITEM/@type', 'NUMBER' AS 'ITEM_LINE/@type', 'CHAR' AS 'VCHR_QV_TEMPLATE/@type', 'CHAR' AS 'REQUESTOR_ID/@type', 'NUMBER' AS 'LS_KEY/@type', 'CHAR' AS 'USER_VCHR_CHAR1/@type', 'CHAR' AS 'USER_VCHR_CHAR2/@type', 'NUMBER' AS 'USER_VCHR_DEC/@type', 'DATE' AS 'USER_VCHR_DATE/@type', 'NUMBER' AS 'USER_VCHR_NUM1/@type', 'CHAR' AS 'USER_HDR_CHAR1/@type', 'CHAR' AS 'MATCH_CNTRL_ID/@type', 'DATE' AS 'TERMS_BASIS_DT/@type' INTO #VCHR_HDR_QV_FieldTypes /*--VCHR_LINE_QV--*/ IF OBJECT_ID('TempDB..#VCHR_LINE_QV_FieldTypes') IS NOT NULL DROP TABLE #VCHR_LINE_QV_FieldTypes SELECT 'R' AS '@class', 'CHAR' AS 'BUSINESS_UNIT/@type', 'CHAR' AS 'VOUCHER_ID/@type', 'NUMBER' AS 'VOUCHER_LINE_NUM/@type', 'CHAR' AS 'BUSINESS_UNIT_PO/@type', 'CHAR' AS 'PO_ID/@type', 'NUMBER' AS 'LINE_NBR/@type', 'NUMBER' AS 'SCHED_NBR/@type', 'CHAR' AS 'DESCR/@type', 'CHAR' AS 'DESCR254_MIXED/@type', 'NUMBER' AS 'MERCHANDISE_AMT/@type', 'CHAR' AS 'ITM_SETID/@type', 'CHAR' AS 'INV_ITEM_ID/@type', 'NUMBER' AS 'QTY_VCHR/@type', 'CHAR' AS 'UNIT_OF_MEASURE/@type', 'NUMBER' AS 'UNIT_PRICE/@type', 'CHAR' AS 'TAX_CD_/@type', 'CHAR' AS 'BUSINESS_UNIT_RECV/@type', 'CHAR' AS 'RECEIVER_ID/@type', 'NUMBER' AS 'RECV_LN_NBR/@type', 'NUMBER' AS 'RECV_SHIP_SEQ_NBR/@type', 'CHAR' AS 'MATCH_LINE_OPT/@type', 'CHAR' AS 'DISTRIB_MTHD_FLG/@type', 'CHAR' AS 'TXN_CURRENCY_CD/@type', 'CHAR' AS 'SHIPTO_ID/@type', 'NUMBER' AS 'ADDR_SEQ_NUM_SHIP/@type', 'CHAR' AS 'SUT_BASE_ID/@type', 'CHAR' AS 'TAX_CD_SUT/@type', 'CHAR' AS 'SUT_EXCPTN_TYPE/@type', 'CHAR' AS 'SUT_EXCPTN_CERTIF/@type', 'CHAR' AS '_TXN_TYPE_CD/@type', 'CHAR' AS '_USE_ID/@type', 'NUMBER' AS 'PROCESS_INSTANCE/@type', 'CHAR' AS 'BUSINESS_UNIT_GL/@type', 'CHAR' AS 'ACCOUNT/@type', 'CHAR' AS 'ALTACCT/@type', 'CHAR' AS 'DEPTID/@type', 'CHAR' AS 'OPERATING_UNIT/@type', 'CHAR' AS 'PRODUCT/@type', 'CHAR' AS 'FUND_CODE/@type', 'CHAR' AS 'CLASS_FLD/@type', 'CHAR' AS 'PROGRAM_CODE/@type', 'CHAR' AS 'BUDGET_REF/@type', 'CHAR' AS 'AFFILIATE/@type', 'CHAR' AS 'AFFILIATE_INTRA1/@type', 'CHAR' AS 'AFFILIATE_INTRA2/@type', 'CHAR' AS 'CHARTFIELD1/@type', 'CHAR' AS 'CHARTFIELD2/@type', 'CHAR' AS 'CHARTFIELD3/@type', 'CHAR' AS 'BUSINESS_UNIT_PC/@type', 'CHAR' AS 'PROJECT_ID/@type', 'CHAR' AS 'ACTIVITY_ID/@type', 'CHAR' AS 'RESOURCE_TYPE/@type', 'CHAR' AS 'RESOURCE_CATEGORY/@type', 'CHAR' AS 'RESOURCE_SUB_CAT/@type', 'CHAR' AS 'ANALYSIS_TYPE/@type', 'CHAR' AS 'ENTRY_EVENT/@type', 'CHAR' AS 'VCHR_BLD_MATCH_FLG/@type', 'CHAR' AS 'ULTIMATE_USE_CD/@type', 'CHAR' AS 'SUT_APPLICABILITY/@type', 'CHAR' AS '_APPLICABILITY/@type', 'CHAR' AS 'DSCNT_APPL_FLG/@type', 'CHAR' AS 'SPEEDCHART_KEY/@type', 'NUMBER' AS 'MAX_DISTRIB_LINE/@type', 'NUMBER' AS 'TOTAL_DISTRIBS/@type', 'CHAR' AS '_ID/@type', 'CHAR' AS 'VNDR_LOC/@type', 'CHAR' AS 'VOUCHER_ID_RELATED/@type', 'CHAR' AS 'BUS_UNIT_RELATED/@type', 'CHAR' AS 'BUSINESS_UNIT_RTV/@type', 'CHAR' AS 'CNTRCT_SETID/@type', 'CHAR' AS 'CNTRCT_ID/@type', 'NUMBER' AS 'CNTRCT_LINE_NBR/@type', 'NUMBER' AS 'ECQUEUEINSTANCE/@type', 'CHAR' AS 'ECTRANSID/@type', 'CHAR' AS 'ECTRANSINOUTSW/@type', 'DATE' AS 'INSPECT_DT/@type', 'DATE' AS 'RECEIPT_DT/@type', 'NUMBER' AS 'RELEASE_NBR/@type', 'CHAR' AS 'RTV_ID/@type', 'NUMBER' AS 'RTV_LN_NBR/@type', 'NUMBER' AS 'STATISTIC_AMOUNT/@type', 'CHAR' AS 'TAX_DSCNT_FLG/@type', 'CHAR' AS 'TAX_FRGHT_FLG/@type', 'CHAR' AS 'TAX_MISC_FLG/@type', 'CHAR' AS 'TAX__FLG/@type', 'CHAR' AS 'PHYSICAL_NATURE/@type', 'CHAR' AS '_RCRD_INPT_FLG/@type', 'CHAR' AS '_RCRD_OUTPT_FLG/@type', 'CHAR' AS '_TREATMENT/@type', 'CHAR' AS '_SVC_SUPPLY_FLG/@type', 'CHAR' AS '_SERVICE_TYPE/@type', 'CHAR' AS 'COUNTRY_LOC_BUYER/@type', 'CHAR' AS 'STATE_LOC_BUYER/@type', 'CHAR' AS 'COUNTRY_LOC_SELLER/@type', 'CHAR' AS 'STATE_LOC_SELLER/@type', 'CHAR' AS 'COUNTRY__SUPPLY/@type', 'CHAR' AS 'STATE__SUPPLY/@type', 'CHAR' AS 'COUNTRY__PERFRM/@type', 'CHAR' AS 'STATE__PERFRM/@type', 'CHAR' AS 'STATE_SHIP_FROM/@type', 'CHAR' AS 'STATE__DEFAULT/@type', 'CHAR' AS 'JRNL_LN_REF/@type', 'CHAR' AS 'OPEN_ITEM_KEY/@type', 'CHAR' AS 'REQUESTOR_ID/@type', 'CHAR' AS 'APPR_STATUS/@type', 'CHAR' AS 'CATEGORY_ID/@type', 'CHAR' AS 'SUT_FREIGHT_CD/@type', 'CHAR' AS 'SUT_PRODUCT_CD/@type', 'CHAR' AS 'TAX_GROUP/@type', 'CHAR' AS 'TAX_JOB_NUM/@type', 'CHAR' AS 'TITLE_PASSAGE/@type', 'CHAR' AS 'SHIP_TYPE_ID/@type', 'CHAR' AS 'TAX_USER_AREA/@type', 'CHAR' AS 'FREIGHT_TERMS/@type', 'NUMBER' AS '_LN_ENT_AMT/@type', 'CHAR' AS '_RECEIPT/@type', 'CHAR' AS '_RGSTRN_SELLER/@type', 'DATE' AS 'TRANS_DT/@type', 'CHAR' AS 'WTHD_SW/@type', 'CHAR' AS 'WTHD_CD/@type', 'CHAR' AS 'USER_VCHR_CHAR1/@type', 'CHAR' AS 'USER_VCHR_CHAR2/@type', 'NUMBER' AS 'USER_VCHR_DEC/@type', 'DATE' AS 'USER_VCHR_DATE/@type', 'NUMBER' AS 'USER_VCHR_NUM1/@type', 'CHAR' AS 'USER_LINE_CHAR1/@type', 'CHAR' AS 'USER_SCHED_CHAR1/@type' INTO #VCHR_LINE_QV_FieldTypes /*--VCHR_DIST_QV--*/ IF OBJECT_ID('TempDB..#VCHR_DIST_QV_FieldTypes') IS NOT NULL DROP TABLE #VCHR_DIST_QV_FieldTypes SELECT 'R' AS '@class', 'CHAR' AS 'BUSINESS_UNIT/@type', 'CHAR' AS 'VOUCHER_ID/@type', 'NUMBER' AS 'VOUCHER_LINE_NUM/@type', 'NUMBER' AS 'DISTRIB_LINE_NUM/@type', 'CHAR' AS 'BUSINESS_UNIT_GL/@type', 'CHAR' AS 'ACCOUNT/@type', 'CHAR' AS 'ALTACCT/@type', 'CHAR' AS 'DEPTID/@type', 'CHAR' AS 'STATISTICS_CODE/@type', 'NUMBER' AS 'STATISTIC_AMOUNT/@type', 'NUMBER' AS 'QTY_VCHR/@type', 'CHAR' AS 'DESCR/@type', 'NUMBER' AS 'MERCHANDISE_AMT/@type', 'CHAR' AS 'BUSINESS_UNIT_PO/@type', 'CHAR' AS 'PO_ID/@type', 'NUMBER' AS 'LINE_NBR/@type', 'NUMBER' AS 'SCHED_NBR/@type', 'NUMBER' AS 'PO_DIST_LINE_NUM/@type', 'CHAR' AS 'BUSINESS_UNIT_AM/@type', 'CHAR' AS 'PROFILE_ID/@type', 'CHAR' AS '_TXN_TYPE_CD/@type', 'CHAR' AS '_APORT_CNTRL/@type', 'CHAR' AS 'BUSINESS_UNIT_RECV/@type', 'CHAR' AS 'RECEIVER_ID/@type', 'NUMBER' AS 'RECV_LN_NBR/@type', 'NUMBER' AS 'RECV_DIST_LINE_NUM/@type', 'DATE' AS 'BUDGET_DT/@type', 'CHAR' AS 'OPERATING_UNIT/@type', 'CHAR' AS 'PRODUCT/@type', 'CHAR' AS 'FUND_CODE/@type', 'CHAR' AS 'CLASS_FLD/@type', 'CHAR' AS 'PROGRAM_CODE/@type', 'CHAR' AS 'BUDGET_REF/@type', 'CHAR' AS 'AFFILIATE/@type', 'CHAR' AS 'AFFILIATE_INTRA1/@type', 'CHAR' AS 'AFFILIATE_INTRA2/@type', 'CHAR' AS 'CHARTFIELD1/@type', 'CHAR' AS 'CHARTFIELD2/@type', 'CHAR' AS 'CHARTFIELD3/@type', 'CHAR' AS 'BUSINESS_UNIT_PC/@type', 'CHAR' AS 'PROJECT_ID/@type', 'CHAR' AS 'ACTIVITY_ID/@type', 'CHAR' AS 'RESOURCE_TYPE/@type', 'CHAR' AS 'RESOURCE_CATEGORY/@type', 'CHAR' AS 'RESOURCE_SUB_CAT/@type', 'CHAR' AS 'ANALYSIS_TYPE/@type', 'NUMBER' AS 'PROCESS_INSTANCE/@type', 'CHAR' AS 'ASSET_FLG/@type', 'CHAR' AS 'COST_TYPE/@type', 'CHAR' AS 'ASSET_ID/@type', 'CHAR' AS 'ENTRY_EVENT/@type', 'CHAR' AS 'JRNL_LN_REF/@type', 'CHAR' AS 'ECTRANSID/@type', 'NUMBER' AS 'ECQUEUEINSTANCE/@type', 'CHAR' AS 'ECTRANSINOUTSW/@type', 'NUMBER' AS 'RECV_SHIP_SEQ_NBR/@type', 'CHAR' AS 'TXN_CURRENCY_CD/@type', 'CHAR' AS 'USER_VCHR_CHAR1/@type', 'CHAR' AS 'USER_VCHR_CHAR2/@type', 'NUMBER' AS 'USER_VCHR_DEC/@type', 'DATE' AS 'USER_VCHR_DATE/@type', 'NUMBER' AS 'USER_VCHR_NUM1/@type', 'CHAR' AS 'USER_DIST_CHAR1/@type', 'CHAR' AS 'OPEN_ITEM_KEY/@type', 'NUMBER' AS '_RECOVERY_PCT/@type', 'NUMBER' AS '_REBATE_PCT/@type', 'NUMBER' AS '_CALC_AMT/@type', 'NUMBER' AS '_BASIS_AMT/@type', 'NUMBER' AS '_RCVRY_AMT/@type', 'NUMBER' AS '_NRCVR_AMT/@type', 'NUMBER' AS '_REBATE_AMT/@type', 'NUMBER' AS '_TRANS_AMT/@type', 'NUMBER' AS 'TAX_CD__PCT/@type', 'NUMBER' AS '_INV_AMT/@type', 'NUMBER' AS '_NONINV_AMT/@type', 'CHAR' AS 'BUSINESS_UNIT_WO/@type', 'CHAR' AS 'WO_ID/@type', 'NUMBER' AS 'WO_TASK_ID/@type', 'CHAR' AS 'RSRC_TYPE/@type', 'NUMBER' AS 'RES_LN_NBR/@type' INTO #VCHR_DIST_QV_FieldTypes /*--======= */ DECLARE @strQuery0 VARCHAR(MAX); DECLARE @strQuery1 VARCHAR(MAX); DECLARE @strQuery2 VARCHAR(MAX) = ''; DECLARE @strQuery3 VARCHAR(MAX); DECLARE @strQuery4 VARCHAR(MAX); SET @strQuery0 = ' SELECT '' EM_VOUCHER_IN MT_GSG_TEM EM_VOUCHER_IN.VERSION_1 async MT_FN VERSION_1 '' AS XMLHeaderSchema SELECT ----FieldTypes (SELECT (SELECT * FROM #VCHR_HDR_QV_FieldTypes FOR XML PATH(''VCHR_HDR_QV''),TYPE), (SELECT * FROM #VCHR_LINE_QV_FieldTypes FOR XML PATH(''VCHR_LINE_QV''),TYPE), (SELECT * FROM #VCHR_DIST_QV_FieldTypes FOR XML PATH(''VCHR_DIST_QV''),TYPE), (SELECT * FROM #PSCAMA_FieldTypes FOR XML PATH(''PSCAMA''), TYPE) FOR XML PATH(''FieldTypes''),TYPE), ----/FieldTypes ---- (SELECT --============--- (SELECT ''R'' AS ''@class'', (SELECT * FROM #VCHR_HDR_QV FOR XML PATH(''''),TYPE) '; DECLARE @VCHR_LINE_QV_Columns VARCHAR(max), @VCHR_DIST_QV_Columns VARCHAR(max) SELECT @VCHR_LINE_QV_Columns = COALESCE(@VCHR_LINE_QV_Columns + ', ', '') + '[' + Name + ']' FROM tempdb.sys.columns WHERE OBJECT_ID = OBJECT_ID('tempdb..#VCHR_LINE_QV') AND name NOT IN ('ID', 'LineId') ORDER BY tempdb.sys.columns.column_id ASC SELECT @VCHR_DIST_QV_Columns = COALESCE(@VCHR_DIST_QV_Columns + ', ', '') + '[' + Name + ']' FROM tempdb.sys.columns WHERE OBJECT_ID = OBJECT_ID('tempdb..#VCHR_Dist_QV') AND name NOT IN ('ID', 'LineId') ORDER BY tempdb.sys.columns.column_id ASC DECLARE @i INT = @fromCt DECLARE @lineCount INT; SET @lineCount = (Select MAX(h.ID) FROM #VCHR_HDR AS h WHERE h.ID BETWEEN @fromCt AND @toCt) WHILE(@i <= @lineCount) --- when @lineCount = 500 then it tooks 15 mins when @lineCount = 1200 it gives error BEGIN SET @strQuery2 = @strQuery2 + '--============--- ,(SELECT ''R'' AS ''@class'', (SELECT ' + @VCHR_LINE_QV_Columns + ' FROM #VCHR_LINE_QV Where ID = ' + convert(varchar(10),@i) +' FOR XML PATH(''''),TYPE), (SELECT ' + @VCHR_DIST_QV_Columns + ' FROM #VCHR_DIST_QV Where ID = ' + convert(varchar(10),@i) +' FOR XML PATH(''VCHR_DIST_QV''),TYPE ) FOR XML PATH(''VCHR_LINE_QV''),TYPE) --============--- '; SELECT @i = @i + 1 END SET @strQuery3 = 'FOR XML PATH(''VCHR_HDR_QV''),TYPE), --=============--- (SELECT * FROM #PSCAMA FOR XML PATH(''PSCAMA''), TYPE) FOR XML PATH(''Transaction''),ROOT (''MsgData''), TYPE) ---- FOR XML PATH(''''), ROOT(''EM_VOUCHER_IN'') '; SET @strQuery4 = 'SELECT '']]> '' AS Footer '; -- if u comment below then 1ll above codes took 2-3 mins --- when @lineCount = 500 then below execution statement tooks 15 mins when @lineCount = 1200 it gives below error -- the query processor ran out of stack space during query optimization please simplify your query EXECUTE(@strQuery0 + @strQuery1 + @strQuery2 + @strQuery3 + @strQuery4) ------ it stucks here