Each staging table represents anEE1 Extract. The staging tables are highlighted in red. 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 staging 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.
-- M101 Through Staging Tables
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_DSTRB_LN_NO = BLK1.NY_PS_DSTRB_LN_NO
)
)
SELECT DISTINCT BTX.NY_BLK_AGENCY_ID,
btx.ny_ps_line_no,
POH.BUSINESS_UNIT,
POD.BUSINESS_UNIT_GL,
'01' Segment_number,
Pch.CNTRCT_STATUS,
TO_CHAR(poh.last_dttm_update, 'MM/DD/YY') LAST_UPDATED,
DECODE(pol.CNTRCT_ID, ' ', 'E', 'T') Document_type,
Ccp.NY_DEPT_CD,
ccp.ny_cost_ctr,
ccp.ny_variable,
ccp.ny_year,
POH.VENDOR_ID,
POH.PO_STATUS,
Pch.ny_amnd_seq_id,
TO_CHAR(KTA.KK_TRAN_DT, 'MMDDYY') KK_TRAN_DT,
KEL.KK_POSTED_AMT,
KEL.activity,
POH.PO_ID,
POD.DEPTID,
POD.ACCOUNT,
POD.OPERATING_UNIT,
POD.PRODUCT,
POD.FUND_CODE,
POD.CLASS_FLD,
POD.PROGRAM_CODE,
POD.BUDGET_REF,
POD.AFFILIATE,
POD.AFFILIATE_INTRA1,
POD.AFFILIATE_INTRA2,
POD.CHARTFIELD1,
POD.CHARTFIELD2,
POD.CHARTFIELD3,
POD.PROJECT_ID,
POD.ACTIVITY_ID,
KTA.DEPTID bud_deptid,
KTA.FUND_CODE bud_fund,
KTA.PROGRAM_CODE bud_program_code,
KTA.BUDGET_REF bud_budref,
KTA.ACCOUNT bud_account,
POl.CNTRCT_IDcontract_number,
POD.LINE_NBR,
POD.SCHED_NBR,
POD.DISTRIB_LINE_NUM ,
KTA.OPERATING_UNIT bud_operating_unit,
KTA.PRODUCT bud_product,
KTA.CLASS_FLD bud_class_fld,
KTA.CHARTFIELD1 bud_chartfield1,
KTA.CHARTFIELD2 bud_chartfield2,
KTA.CHARTFIELD3 bud_chartfield3,
KTA.BUSINESS_UNIT_PC bud_business_unit_pc,
KTA.PROJECT_ID bud_project_id,
KTA.ACTIVITY_ID bud_activity_id,
KTA.RESOURCE_TYPE bud_resource_type,
KTA.LEDGER_GROUP Bud_ledger_group
FROM PS_NY_PO_HDR_STG poh,
PS_NY_PO_LINE_STG pol,
PS_NY_PO_DIST_STGpod,
PS_NY_KK_LIQ_STG kel,
BTX ,
PS_NY_KK_HDR_STG KTH,
PS_NY_KK_LINE_STG KTL,
PS_NY_KK_LOG_STGkta,
PS_NY_CONT_HDR_STG pch,
(select * from PS_NY_M081_CST_CTR where NY_STATUS='A') ccp
WHERE 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'
AND pod.BUSINESS_UNIT = kth.BUSINESS_UNIT
AND pod.po_ID = kth.po_id
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
AND kta.ledger_group = kel.ledger_group
-- SOURCE LN
AND kth.kk_tran_id = ktl.kk_tran_id
AND kth.kk_tran_dt = ktl.kk_tran_dt
--ACTIVITY LOG
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
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.PRODUCT =ccp.product(+)
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(+)