ITSPivot Team
To: Statewide Financial System (SFS) Bulkload Agencies
From:ITS Pivot team
Subject: SQL Template for M101 Recreation at Agencies
Each table represents anEE1 Extract. To make it easier, the table alias has been used as the extract abbreviation. POH,POL,POD,KEL,KTH,KTL,KTA,BTX, PCH, and CCP/M081extracts need to be loaded prior to running the below query. The table names will need to be changed to the customized state agency table names.There are a lot of fields available in PCH extract thatcan be included if required.
SELECT *
FROM (WITH BTX AS (
SELECT BLK1.*
FROM PS_NY_BULK_TRANS BLK1
WHERE BLK1.NY_TRANS_CD = 'POD'
AND (BLK1.NY_BLK_LOAD_ID) = (
SELECT MAX (BLK2.NY_BLK_LOAD_ID)
FROM PS_NY_BULK_TRANS BLK2
WHERE BLK2.NY_TRANS_CD = BLK1.NY_TRANS_CD
AND BLK2.NY_PS_BU = BLK1.NY_PS_BU
AND BLK2.NY_PS_DOC_ID = BLK1.NY_PS_DOC_ID
AND BLK2.NY_PS_LINE_NO = BLK1.NY_PS_LINE_NO
AND BLK2.NY_PS_SCHED_NO = BLK1.NY_PS_SCHED_NO
AND BLK2.NY_PS_DSTRB_LN_NO = BLK1.NY_PS_DSTRB_LN_NO ) ) , PCH AS (
SELECT DISTINCT CH.SETID
, CH.CNTRCT_ID
, CH.CNTRCT_STATUS
, CHE.NY_AMND_SEQ_ID
FROM PS_CNTRCT_HDR CH
, PS_NY_CNTR_AMD_HDR CHE
WHERE CH.SETID = CHE.SETID
AND CH.CNTRCT_ID = CHE.CNTRCT_ID
AND CH.VERSION_NBR = (
SELECT MAX(VERSION_NBR)
FROM PS_CNTRCT_HDR CH1
WHERE CH.SETID = CH1.SETID
AND CH.CNTRCT_ID = CH1.CNTRCT_ID )
AND CHE.NY_AMND_SEQ_ID = (
SELECT MAX(CHE1.NY_AMND_SEQ_ID)
FROM PS_NY_CNTR_AMD_HDR CHE1
WHERE CHE.SETID = CHE1.SETID
AND CHE.CNTRCT_ID = CHE1.CNTRCT_ID ) )
SELECT DISTINCT '10' || RPAD(NVL(BTX.NY_BLK_AGENCY_ID
,' ')
,5
,' ') || RPAD(nvl(''
,' ')
,7) || DECODE(TRIM(BTX.NY_PS_LINE_NO)
, ''
, RPAD(nvl(''
,' ')
,3)
, LPAD(BTX.NY_PS_LINE_NO
, 3
, '0')) || RPAD(NVL( POH.BUSINESS_UNIT
,' ')
,5
,' ') || RPAD(NVL(POD.BUSINESS_UNIT_GL
,' ')
,5
,' ') || '01' || RPAD(NVL(PCH.CNTRCT_STATUS
,' ')
,1
,' ') || RPAD(NVL(TO_CHAR(POH.LAST_DTTM_UPDATE
, 'MMDDYY')
,' ')
,6
,' ') || RPAD(DECODE(POL.CNTRCT_ID
, ' '
, 'E'
, 'T')
,1
,' ') || RPAD(NVL(CCP.NY_DEPT_CODE
,' ')
,2
,' ') || RPAD(NVL(CCP.NY_COST_CTR
,' ')
,6
,' ') || RPAD(NVL(CCP.NY_VARIABLE
,' ')
,2
,' ') || RPAD(NVL(CCP.NY_YEAR
,' ')
,2
,' ') || RPAD(NVL(POH.VENDOR_ID
,' ')
,10
,' ') || RPAD(NVL(CASE WHEN POH.PO_STATUS = 'X' THEN 'X' WHEN POL.CANCEL_STATUS = 'X'
AND POH.PO_STATUS = 'PX' THEN 'PX' WHEN POL.CANCEL_STATUS = 'X'
AND POH.PO_STATUS NOT IN ('PX') THEN 'X' WHEN POL.CANCEL_STATUS = 'X' THEN 'X' WHEN POD.DISTRIB_LN_STATUS = 'X' THEN 'X' WHEN POD.DISTRIB_LN_STATUS = 'C' THEN 'C' ELSE NVL (POH.PO_STATUS
, ' ') END
,' ')
,2
,' ') || RPAD(nvl(''
,' ')
,7) || RPAD(nvl(''
,' ')
,7) || RPAD(nvl(''
,' ')
,4) || CASE WHEN TRIM(PCH.NY_AMND_SEQ_ID) IS NULL THEN LPAD(' '
,3) ELSE LPAD(PCH.NY_AMND_SEQ_ID
,3
,'0') END || RPAD(nvl(''
,' ')
,5) || RPAD(NVL(TO_CHAR(KTA.KK_TRAN_DT
, 'MMDDYY')
,' ')
,6
,' ')|| RPAD(nvl(''
,' ')
,2) || CASE WHEN (KEL.MONETARY_AMOUNT <0 ) THEN '-' ELSE '+' END || CASE WHEN (KEL.MONETARY_AMOUNT <0 ) THEN nvl(SUBSTR(LTRIM(TO_CHAR(NVL(-KEL.MONETARY_AMOUNT
,0)
,'09999999999999999999999.99'))
,13
,14)
,'00000000000.00') ELSE nvl(SUBSTR(LTRIM(TO_CHAR(NVL(KEL.MONETARY_AMOUNT
,0)
,'09999999999999999999999.99'))
,13
,14)
,'00000000000.00') END || CASE WHEN (KEL.ACTIVITY <0 ) THEN '-' ELSE '+' END || CASE WHEN (KEL.ACTIVITY <0 ) THEN nvl(SUBSTR(LTRIM(TO_CHAR(NVL(-KEL.ACTIVITY
,0)
,'09999999999999999999999.99'))
,13
,14)
,'00000000000.00') ELSE nvl(SUBSTR(LTRIM(TO_CHAR(NVL(KEL.ACTIVITY
,0)
,'09999999999999999999999.99'))
,13
,14)
,'00000000000.00') END || RPAD(nvl(''
,' ')
,7)|| RPAD(NVL(POH.PO_ID
,' ')
,10
,' ') || RPAD(NVL(POD.DEPTID
,' ')
,10
,' ') || RPAD(NVL(POD.ACCOUNT
,' ')
,10
,' ') || RPAD(NVL(POD.OPERATING_UNIT
,' ')
,8
,' ') || RPAD(NVL(POD.PRODUCT
,' ')
,6
,' ') || RPAD(NVL(POD.FUND_CODE
,' ')
,5
,' ') || RPAD(NVL(POD.CLASS_FLD
,' ')
,5
,' ') || RPAD(NVL(POD.PROGRAM_CODE
,' ')
,5
,' ') || RPAD(NVL(POD.BUDGET_REF
,' ')
,8
,' ') || RPAD(NVL(POD.AFFILIATE
,' ')
,5
,' ') || RPAD(NVL(POD.AFFILIATE_INTRA1
,' ')
,10
,' ') || RPAD(NVL(POD.AFFILIATE_INTRA2
,' ')
,10
,' ') || RPAD(NVL(POD.CHARTFIELD1
,' ')
,10
,' ') || RPAD(NVL(POD.CHARTFIELD2
,' ')
,10
,' ') || RPAD(NVL(POD.CHARTFIELD3
,' ')
,10
,' ') || RPAD(NVL(POD.PROJECT_ID
,' ')
,15
,' ') || RPAD(NVL(POD.ACTIVITY_ID
,' ')
,15
,' ') || RPAD(NVL(KTA.DEPTID
,' ')
,10
,' ') || RPAD(NVL(KTA.FUND_CODE
,' ')
,5
,' ') || RPAD(NVL(KTA.PROGRAM_CODE
,' ')
,5
,' ') || RPAD(NVL(KTA.BUDGET_REF
,' ')
,8
,' ')|| RPAD(nvl(''
,' ')
,2) || RPAD(NVL(KTA.ACCOUNT
,' ')
,10
,' ') || RPAD(NVL(POL.CNTRCT_ID
,' ')
,25
,' ') || LPAD(NVL(TO_CHAR(NVL(POD.LINE_NBR
,0))
,' ')
,5
,'0') || LPAD(NVL(TO_CHAR(NVL(POD.SCHED_NBR
,0))
,' ')
,3
,'0') || LPAD(NVL(TO_CHAR(NVL(POD.DISTRIB_LINE_NUM
,0))
,' ')
,5
,'0') || RPAD(NVL(KTA.OPERATING_UNIT
,' ')
,8
,' ') || RPAD(NVL(KTA.PRODUCT
,' ')
,6
,' ') || RPAD(NVL(KTA.CLASS_FLD
,' ')
,5
,' ') || RPAD(NVL(KTA.CHARTFIELD1
,' ')
,10
,' ') || RPAD(NVL(KTA.CHARTFIELD2
,' ')
,10
,' ') || RPAD(NVL(KTA.CHARTFIELD3
,' ')
,10
,' ') || RPAD(NVL(KTA.BUSINESS_UNIT_PC
,' ')
,5
,' ') || RPAD(NVL(KTA.PROJECT_ID
,' ')
,15
,' ') || RPAD(NVL(KTA.ACTIVITY_ID
,' ')
,15
,' ') || RPAD(NVL(KTA.RESOURCE_TYPE
,' ')
,5
,' ') || RPAD(NVL(KTA.LEDGER_GROUP
,' ')
,10
,' ')
FROM PS_NY_KK_LIQ_STG KEL
, PS_PO_HDR POH
, PS_PO_LINE POL
, PS_PO_LINE_DISTRIB POD
, BTX
, PS_KK_SOURCE_HDR KTH
, PS_KK_SOURCE_LN KTL
, PS_KK_ACTIVITY_LOG KTA
, PCH
, (
SELECT *
FROM PS_NY_COST_CTR_TBL NY_COS
WHERE NY_COS.EFFDT = (
SELECT MAX(A.effdt)
FROM ps_ny_cost_ctr_tbl a
WHERE NY_COS.business_unit = A.BUSINESS_UNIT
AND NY_COS.ny_cost_center = A.ny_cost_center
AND a.effdt <= trunc(sysdate) )
AND NY_STATUS='A' ) CCP
WHERE
--PURCHASE ORDER AND LINE
POH.BUSINESS_UNIT = POL.BUSINESS_UNIT
AND POH.PO_ID = POL.PO_ID
AND POL.BUSINESS_UNIT = POD.BUSINESS_UNIT
AND POL.PO_ID = POD.PO_ID
AND POL.LINE_NBR = POD.LINE_NBR
AND POH.BUDGET_HDR_STATUS >'E'
--KK SOURCE HEADER
AND POD.BUSINESS_UNIT = KTH.BUSINESS_UNIT
AND POD.PO_ID = KTH.PO_ID
--KK SOURCE LINE
AND POD.LINE_NBR = KTL.LINE_NBR
AND POD.SCHED_NBR = KTL.SCHED_NBR
AND POD.DISTRIB_LINE_NUM = KTL.DISTRIB_LINE_NUM
--LIQUIDATION
AND KTH.KK_TRAN_ID = KEL.KK_TRAN_ID
AND KTH.KK_TRAN_DT = KEL.KK_TRAN_DT
AND KTL.KK_TRAN_LN = KEL.KK_TRAN_LN
--ACTIVITY LOG
AND KTA.LEDGER_GROUP = KEL.LEDGER_GROUP
AND KTH.KK_TRAN_ID = KTL.KK_TRAN_ID
AND KTH.KK_TRAN_DT = KTL.KK_TRAN_DT
AND KTL.KK_TRAN_ID = KTA.KK_TRAN_ID
AND KTL.KK_TRAN_DT = KTA.KK_TRAN_DT
AND KTL.KK_TRAN_LN = KTA.KK_TRAN_LN
AND KTA.LEDGER LIKE '%EN'
-- Bulkload Join
-- This section is only for DOT
AND KTA.RVRSL_FLG > 'Y'
AND KTA.seqnbr = (
SELECT MAX(SEQNBR)
FROM PS_KK_ACTIVITY_LOG KTA1
WHERE KTA.KK_TRAN_ID = KTA1.KK_TRAN_ID
AND KTA.kk_tran_dt = KTA1.kk_tran_dt
AND KTA.kk_tran_LN = KTA1.kk_tran_LN
AND KTA.LEDGER = KTA1.LEDGER
AND KTA.LEDGER_GROUP = KTA1.LEDGER_GROUP)
AND POD.BUSINESS_UNIT = BTX.NY_PS_BU (+)
AND POD.PO_ID = BTX.NY_PS_DOC_ID (+)
AND POD.LINE_NBR = BTX.NY_PS_LINE_NO (+)
AND POD.SCHED_NBR = BTX.NY_PS_SCHED_NO (+)
AND POD.DISTRIB_LINE_NUM = BTX.NY_PS_DSTRB_LN_NO (+)
-- Contract Join
AND PCH.CNTRCT_ID(+) = POL.CNTRCT_ID
AND PCH.SETID(+) = POL.CNTRCT_SETID
AND POD.DEPTID = CCP.DEPTID(+)
AND POD.OPERATING_UNIT = CCP.OPERATING_UNIT(+)
AND POD.FUND_CODE = CCP.FUND_CODE(+)
AND POD.CLASS_FLD = CCP.CLASS_FLD(+)
AND POD.PROGRAM_CODE = CCP.PROGRAM_CODE(+)
AND POD.BUDGET_REF = CCP.BUDGET_REF(+)
AND POD.AFFILIATE = CCP.AFFILIATE(+)
AND POD.AFFILIATE_INTRA1 = CCP.AFFILIATE_INTRA1(+)
AND POD.AFFILIATE_INTRA2 = CCP.AFFILIATE_INTRA2(+)
AND POD.CHARTFIELD1 = CCP.CHARTFIELD1(+)
AND POD.CHARTFIELD2 = CCP.CHARTFIELD2(+)
AND POD.CHARTFIELD3 = CCP.CHARTFIELD3(+)
AND POD.PROJECT_ID = CCP.PROJECT_ID(+)
AND POD.ACTIVITY_ID = CCP.ACTIVITY_ID(+)
-- This criteria is for all agencies except DOT
AND POD.PRODUCT = CCP.PRODUCT(+)
AND ( POD.BUSINESS_UNIT = :1
OR POD.BUSINESS_UNIT_GL = :2)
AND TRUNC(KEL.KK_DATETIME_STAMP) BETWEEN %DateIn(:3) AND %DateIn(:4))