PJC_XCC_EXTRACT

Details

Schema: FUSION
Object owner: PJC
Object type: VIEW

Columns

Name
DATA_SET_ID SOURCE_HEADER_ID_1 SOURCE_HEADER_ID_2 TRANSACTION_NUMBER SOURCE_LINE_ID_1 SOURCE_LINE_ID_2 SOURCE_LINE_ID_3 SOURCE_LINE_ID_4 SOURCE_LINE_ID_5 SOURCE_LINE_ID_6 LINE_GROUP_CODE LINE_NUM LEDGER_ID BUSINESS_UNIT_ID DESTINATION_TYPE_CODE DOCUMENT_TYPE_CODE TRANSACTION_SOURCE_CODE TRANSACTION_SUBTYPE_CODE JE_SOURCE_CODE JE_CATEGORY_CODE BUDGET_DATE ACCOUNTING_DATE ENCUMBRANCE_TYPE_CODE ENTERED_CURRENCY UOM_CODE QUANTITY PRICE ENTERED_AMOUNT CONVERSION_TYPE_CODE CONVERSION_DATE LEDGER_AMOUNT LIQUIDATION_DATE LIQUIDATION_QUANTITY LIQUIDATION_AMOUNT LIQUIDATION_TRANS_TYPE_CODE LIQUIDATION_LINE_ID_1 LIQUIDATION_LINE_ID_2 LIQUIDATION_LINE_ID_3 LIQUIDATION_LINE_ID_4 LIQUIDATION_LINE_ID_5 LIQUIDATION_LINE_ID_6 STATISTICAL_AMOUNT BUDGET_CCID VENDOR_ID INVENTORY_ITEM_ID ORDER_TYPE_INFO CODE_COMBINATION_ID PJC_PROJECT_ID PJC_TASK_ID PJC_RESOURCE_ID PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_FUNDING_ALLOCATION_ID PJC_BILLABLE_FLAG PJC_CAPITALIZABLE_FLAG PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_ITEM_DATE PJC_ORGANIZATION_ID PJC_WORK_TYPE_ID CONTROL_BUDGET_ID HEADER_GROUP_CODE HEADER_NUM PJC_FUNDING_SOURCE PJC_TOP_RESOURCE_ID

Name

DATA_SET_ID

SOURCE_HEADER_ID_1

SOURCE_HEADER_ID_2

TRANSACTION_NUMBER

SOURCE_LINE_ID_1

SOURCE_LINE_ID_2

SOURCE_LINE_ID_3

SOURCE_LINE_ID_4

SOURCE_LINE_ID_5

SOURCE_LINE_ID_6

LINE_GROUP_CODE

LINE_NUM

LEDGER_ID

BUSINESS_UNIT_ID

DESTINATION_TYPE_CODE

DOCUMENT_TYPE_CODE

TRANSACTION_SOURCE_CODE

TRANSACTION_SUBTYPE_CODE

JE_SOURCE_CODE

JE_CATEGORY_CODE

BUDGET_DATE

ACCOUNTING_DATE

ENCUMBRANCE_TYPE_CODE

ENTERED_CURRENCY

UOM_CODE

QUANTITY

PRICE

ENTERED_AMOUNT

CONVERSION_TYPE_CODE

CONVERSION_DATE

LEDGER_AMOUNT

LIQUIDATION_DATE

LIQUIDATION_QUANTITY

LIQUIDATION_AMOUNT

LIQUIDATION_TRANS_TYPE_CODE

LIQUIDATION_LINE_ID_1

LIQUIDATION_LINE_ID_2

LIQUIDATION_LINE_ID_3

LIQUIDATION_LINE_ID_4

LIQUIDATION_LINE_ID_5

LIQUIDATION_LINE_ID_6

STATISTICAL_AMOUNT

BUDGET_CCID

VENDOR_ID

INVENTORY_ITEM_ID

ORDER_TYPE_INFO

CODE_COMBINATION_ID

PJC_PROJECT_ID

PJC_TASK_ID

PJC_RESOURCE_ID

PJC_CONTRACT_ID

PJC_CONTRACT_LINE_ID

PJC_FUNDING_ALLOCATION_ID

PJC_BILLABLE_FLAG

PJC_CAPITALIZABLE_FLAG

PJC_EXPENDITURE_TYPE_ID

PJC_EXPENDITURE_ITEM_DATE

PJC_ORGANIZATION_ID

PJC_WORK_TYPE_ID

CONTROL_BUDGET_ID

HEADER_GROUP_CODE

HEADER_NUM

PJC_FUNDING_SOURCE

PJC_TOP_RESOURCE_ID

Query

SQL_Statement
SELECT PATI.DATA_SET_ID DATA_SET_ID, TO_CHAR(PATI.EXPENDITURE_ITEM_ID) SOURCE_HEADER_ID_1, ' ' SOURCE_HEADER_ID_2, TO_CHAR(PATI.EXPENDITURE_ITEM_ID) TRANSACTION_NUMBER, TO_CHAR(PATI.EXPENDITURE_ITEM_ID) SOURCE_LINE_ID_1, TO_CHAR(PATI.CDL_LINE_NUM) SOURCE_LINE_ID_2, ' ' SOURCE_LINE_ID_3, ' ' SOURCE_LINE_ID_4, ' ' SOURCE_LINE_ID_5, ' ' SOURCE_LINE_ID_6, null LINE_GROUP_CODE, null LINE_NUM, BU.Primary_Ledger_Id LEDGER_ID, PATI.ORG_ID BUSINESS_UNIT_ID, null DESTINATION_TYPE_CODE, DOC.DOCUMENT_CODE DOCUMENT_TYPE_CODE, SRC.TRANSACTION_SOURCE TRANSACTION_SOURCE_CODE, null TRANSACTION_SUBTYPE_CODE, null JE_SOURCE_CODE, null JE_CATEGORY_CODE, NVL(TO_DATE(PATI.RESERVED_ATTRIBUTE10,'DD-MM-YYYY'),PATI.expenditure_item_date) BUDGET_DATE, PATI.GL_DATE ACCOUNTING_DATE, null ENCUMBRANCE_TYPE_CODE, PATI.DENOM_CURRENCY_CODE ENTERED_CURRENCY, null UOM_CODE, PATI.QUANTITY QUANTITY, null PRICE, DECODE(PATI.System_Linkage, 'BTC', PATI.Denom_Burdened_Cost, PATI.Denom_Raw_Cost) ENTERED_AMOUNT, PATI.PROJECT_RATE_TYPE CONVERSION_TYPE_CODE, PATI.PROJECT_RATE_DATE CONVERSION_DATE, DECODE(PATI.System_Linkage, 'BTC', PATI.Acct_Burdened_Cost, PATI.Acct_Raw_Cost) LEDGER_AMOUNT, null LIQUIDATION_DATE, null LIQUIDATION_QUANTITY, CASE WHEN ( TRANSACTION_SOURCE_CODE = 'OAP') THEN DENOM_RAW_COST WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc.document_code = 'RECEIPT' AND pati.DOC_REF_ID5 IN ('PO_DELIVERY_INTO_INVENTORY','ACQUISITION_COST_ADJUSTMENT','OSP_PO_DELIVERY_INTO_MFG','OSP_ACQ_COST_ADJUSTMENT')) THEN pati.BACKING_AMOUNT WHEN (TRANSACTION_SOURCE_CODE = 'OSCM') THEN DENOM_RAW_COST ELSE NULL END LIQUIDATION_AMOUNT, CASE WHEN( TRANSACTION_SOURCE_CODE = 'OAP') THEN 'INVOICE' WHEN( TRANSACTION_SOURCE_CODE = 'OSCM') THEN CASE WHEN doc.document_code = 'TRANSFER_ORDER' THEN 'RECEIPT_IMT_EXPENSE' WHEN (doc.document_code = 'RECEIPT' AND doc_entry.doc_entry_code = 'ITEM') THEN 'RECEIPT_EXPENSE' WHEN (doc.document_code = 'RECEIPT' and pati.DOC_REF_ID5 IN ('PO_DELIVERY_INTO_INVENTORY','ACQUISITION_COST_ADJUSTMENT','OSP_PO_DELIVERY_INTO_MFG','OSP_ACQ_COST_ADJUSTMENT')) THEN 'PURCHASE_ORDER' WHEN (doc.document_code = 'RECEIPT' AND doc_entry.doc_entry_code in ('NONREC_TAX','NONREC_TAX_INCLUSIVE','ERV','IPV','TERV','TIPV','TRV')) THEN CASE WHEN pati.backing_header_id1 is null AND pati.backing_dist_id1 is not null AND pati.backing_dist_id2 is not null THEN 'INVOICE' ELSE 'RECEIPT_EXPENSE' END ELSE 'RECEIPT_RETRO_PRICE' END END LIQUIDATION_TRANS_TYPE_CODE, CASE WHEN( TRANSACTION_SOURCE_CODE = 'OAP') THEN TO_CHAR(ORIGINAL_LINE_NUMBER) WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND (doc.document_code = 'TRANSFER_ORDER' OR doc_entry.doc_entry_code = 'ITEM') AND PATI.cmr_event_cost_id IS NOT NULL) THEN TO_CHAR(PATI.cmr_event_cost_id) WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND (doc.document_code = 'TRANSFER_ORDER' OR doc_entry.doc_entry_code = 'ITEM') AND PATI.cmr_event_cost_id IS NULL) THEN (select TO_CHAR(event_cost_id) from cmr_rcv_distributions where cmr_sub_ledger_id = (ORIGINAL_DIST_ID)) WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc_entry.doc_entry_code IN ('NONREC_TAX','NONREC_TAX_INCLUSIVE','ERV','IPV','TERV','TIPV','TRV') AND pati.backing_dist_id1 is not null AND pati.backing_dist_id2 is not null AND pati.backing_header_id1 is null) THEN TO_CHAR(pati.backing_dist_id1) WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc_entry.doc_entry_code IN ('NONREC_TAX','NONREC_TAX_INCLUSIVE','ERV','IPV','TERV','TIPV','TRV') AND PATI.cmr_event_cost_id IS NOT NULL) THEN TO_CHAR(PATI.cmr_event_cost_id) WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc_entry.doc_entry_code IN ('NONREC_TAX','NONREC_TAX_INCLUSIVE','ERV','IPV','TERV','TIPV','TRV') AND PATI.cmr_event_cost_id IS NULL) THEN (select TO_CHAR(event_cost_id) from cmr_rcv_distributions where cmr_sub_ledger_id = (ORIGINAL_DIST_ID)) WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc_entry.doc_entry_code IN ('PRICE_ADJ','NONREC_TAX_PRICE_ADJ','NONREC_TAX_PRICE_INCLUSIVE_ADJ')) THEN NVL(to_char(pati.cmr_event_cost_id),pjc_budgetary_control_int_pkg.getCMREventCostId(PATI.EXPENDITURE_ITEM_ID,pati.data_set_id,ORIGINAL_DIST_ID)) WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc.document_code = 'RECEIPT' and pati.DOC_REF_ID5 IN ('PO_DELIVERY_INTO_INVENTORY','ACQUISITION_COST_ADJUSTMENT','OSP_PO_DELIVERY_INTO_MFG','OSP_ACQ_COST_ADJUSTMENT')) THEN to_char(pati.BACKING_DIST_ID3) ELSE NULL END LIQUIDATION_LINE_ID_1, CASE WHEN( TRANSACTION_SOURCE_CODE = 'OAP') THEN TO_CHAR(ORIGINAL_DIST_ID) WHEN( TRANSACTION_SOURCE_CODE = 'OSCM' AND doc.document_code = 'TRANSFER_ORDER') THEN NULL WHEN( TRANSACTION_SOURCE_CODE = 'OSCM' AND doc_entry.doc_entry_code IN ('NONREC_TAX','NONREC_TAX_INCLUSIVE','ERV','IPV','TERV','TIPV','TRV') AND pati.backing_dist_id1 is not null AND pati.backing_dist_id2 is not null AND pati.backing_header_id1 is null) THEN TO_CHAR(pati.backing_dist_id2) WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc.document_code = 'RECEIPT' AND pati.DOC_REF_ID5 IN ('PO_DELIVERY_INTO_INVENTORY','ACQUISITION_COST_ADJUSTMENT','OSP_PO_DELIVERY_INTO_MFG','OSP_ACQ_COST_ADJUSTMENT')) THEN to_char(pati.BACKING_DIST_ID4) ELSE NULL END LIQUIDATION_LINE_ID_2, CASE WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc.document_code = 'RECEIPT' AND pati.DOC_REF_ID5 IN ('PO_DELIVERY_INTO_INVENTORY','ACQUISITION_COST_ADJUSTMENT','OSP_PO_DELIVERY_INTO_MFG','OSP_ACQ_COST_ADJUSTMENT')) THEN to_char(pati.BACKING_DIST_ID1) ELSE ' ' END LIQUIDATION_LINE_ID_3, CASE WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc.document_code = 'RECEIPT' AND pati.DOC_REF_ID5 IN ('PO_DELIVERY_INTO_INVENTORY','ACQUISITION_COST_ADJUSTMENT','OSP_PO_DELIVERY_INTO_MFG','OSP_ACQ_COST_ADJUSTMENT')) THEN to_char(pati.BACKING_DIST_ID2) ELSE ' ' END LIQUIDATION_LINE_ID_4, ' ' LIQUIDATION_LINE_ID_5, ' ' LIQUIDATION_LINE_ID_6, NULL STATISTICAL_AMOUNT, PATI.Budget_CCID BUDGET_CCID, PATI.VENDOR_ID VENDOR_ID, PATI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, null ORDER_TYPE_INFO, PATI.BUDGET_CCID CODE_COMBINATION_ID, PATI.PROJECT_ID PJC_PROJECT_ID, PATI.TASK_ID PJC_TASK_ID, PATI.PRBS_ELEMENT_ID PJC_RESOURCE_ID, PATI.CONTRACT_ID PJC_CONTRACT_ID, PATI.CONTRACT_LINE_ID PJC_CONTRACT_LINE_ID, PATI.FUNDING_ALLOCATION_ID PJC_FUNDING_ALLOCATION_ID, PATI.BILLABLE_FLAG PJC_BILLABLE_FLAG, PATI.CAPITALIZABLE_FLAG PJC_CAPITALIZABLE_FLAG, PATI.EXPENDITURE_TYPE_ID PJC_EXPENDITURE_TYPE_ID, PATI.EXPENDITURE_ITEM_DATE PJC_EXPENDITURE_ITEM_DATE, NVL(PATI.OVERRIDE_TO_ORGANIZATION_ID, PATI.ORGANIZATION_ID) PJC_ORGANIZATION_ID, PATI.WORK_TYPE_ID PJC_WORK_TYPE_ID, null CONTROL_BUDGET_ID, decode(PATI.PROCESS_GROUP_ID, null, null, PATI.PROCESS_GROUP_ID) HEADER_GROUP_CODE, decode(PATI.CDL_LINE_NUM,1,PATI.QUANTITY,PATI.EXPENDITURE_ITEM_ID) HEADER_NUM, pati.reserved_attribute1 pjc_funding_source, pati.TOP_PRBS_ELEMENT_ID pjc_top_resource_id FROM PJC_TXN_XFACE_ALL PATI, PJF_BU_IMPL_ALL_V BU, PJF_TXN_DOCUMENT_B DOC, pjf_txn_doc_entry_b doc_entry, PJF_TXN_SOURCES_B SRC WHERE PATI.ORG_ID = BU.ORG_ID AND PATI.DOCUMENT_ID = DOC.DOCUMENT_ID AND PATI.TRANSACTION_SOURCE_ID = SRC.TRANSACTION_SOURCE_ID AND PATI.TRANSACTION_STATUS_CODE = 'P' AND PATI.BUDGETARY_CONTROL_VAL_STATUS = 'NOT_ATTEMPTED' AND Doc.Document_Id = doc_entry.Document_Id AND PATI.document_id = doc.document_id AND PATI.doc_entry_id = doc_entry.doc_entry_id

SQL_Statement

SELECT PATI.DATA_SET_ID DATA_SET_ID,

TO_CHAR(PATI.EXPENDITURE_ITEM_ID) SOURCE_HEADER_ID_1,

' ' SOURCE_HEADER_ID_2,

TO_CHAR(PATI.EXPENDITURE_ITEM_ID) TRANSACTION_NUMBER,

TO_CHAR(PATI.EXPENDITURE_ITEM_ID) SOURCE_LINE_ID_1,

TO_CHAR(PATI.CDL_LINE_NUM) SOURCE_LINE_ID_2,

' ' SOURCE_LINE_ID_3,

' ' SOURCE_LINE_ID_4,

' ' SOURCE_LINE_ID_5,

' ' SOURCE_LINE_ID_6,

null LINE_GROUP_CODE,

null LINE_NUM,

BU.Primary_Ledger_Id LEDGER_ID,

PATI.ORG_ID BUSINESS_UNIT_ID,

null DESTINATION_TYPE_CODE,

DOC.DOCUMENT_CODE DOCUMENT_TYPE_CODE,

SRC.TRANSACTION_SOURCE TRANSACTION_SOURCE_CODE,

null TRANSACTION_SUBTYPE_CODE,

null JE_SOURCE_CODE,

null JE_CATEGORY_CODE,

NVL(TO_DATE(PATI.RESERVED_ATTRIBUTE10,'DD-MM-YYYY'),PATI.expenditure_item_date) BUDGET_DATE,

PATI.GL_DATE ACCOUNTING_DATE,

null ENCUMBRANCE_TYPE_CODE,

PATI.DENOM_CURRENCY_CODE ENTERED_CURRENCY,

null UOM_CODE,

PATI.QUANTITY QUANTITY,

null PRICE,

DECODE(PATI.System_Linkage, 'BTC', PATI.Denom_Burdened_Cost, PATI.Denom_Raw_Cost) ENTERED_AMOUNT,

PATI.PROJECT_RATE_TYPE CONVERSION_TYPE_CODE,

PATI.PROJECT_RATE_DATE CONVERSION_DATE,

DECODE(PATI.System_Linkage, 'BTC', PATI.Acct_Burdened_Cost, PATI.Acct_Raw_Cost) LEDGER_AMOUNT,

null LIQUIDATION_DATE,

null LIQUIDATION_QUANTITY,

CASE

WHEN ( TRANSACTION_SOURCE_CODE = 'OAP') THEN DENOM_RAW_COST

WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc.document_code = 'RECEIPT' AND pati.DOC_REF_ID5 IN ('PO_DELIVERY_INTO_INVENTORY','ACQUISITION_COST_ADJUSTMENT','OSP_PO_DELIVERY_INTO_MFG','OSP_ACQ_COST_ADJUSTMENT')) THEN pati.BACKING_AMOUNT

WHEN (TRANSACTION_SOURCE_CODE = 'OSCM') THEN DENOM_RAW_COST

ELSE NULL

END LIQUIDATION_AMOUNT,

CASE WHEN( TRANSACTION_SOURCE_CODE = 'OAP') THEN 'INVOICE'

WHEN( TRANSACTION_SOURCE_CODE = 'OSCM') THEN CASE WHEN doc.document_code = 'TRANSFER_ORDER' THEN 'RECEIPT_IMT_EXPENSE'

WHEN (doc.document_code = 'RECEIPT' AND doc_entry.doc_entry_code = 'ITEM') THEN 'RECEIPT_EXPENSE'

WHEN (doc.document_code = 'RECEIPT' and pati.DOC_REF_ID5 IN ('PO_DELIVERY_INTO_INVENTORY','ACQUISITION_COST_ADJUSTMENT','OSP_PO_DELIVERY_INTO_MFG','OSP_ACQ_COST_ADJUSTMENT')) THEN 'PURCHASE_ORDER'

WHEN (doc.document_code = 'RECEIPT' AND doc_entry.doc_entry_code in ('NONREC_TAX','NONREC_TAX_INCLUSIVE','ERV','IPV','TERV','TIPV','TRV')) THEN

CASE WHEN pati.backing_header_id1 is null AND

pati.backing_dist_id1 is not null AND

pati.backing_dist_id2 is not null THEN 'INVOICE'

ELSE 'RECEIPT_EXPENSE'

END

ELSE 'RECEIPT_RETRO_PRICE'

END

END LIQUIDATION_TRANS_TYPE_CODE,

CASE WHEN( TRANSACTION_SOURCE_CODE = 'OAP')

THEN TO_CHAR(ORIGINAL_LINE_NUMBER)

WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND (doc.document_code = 'TRANSFER_ORDER' OR doc_entry.doc_entry_code = 'ITEM') AND PATI.cmr_event_cost_id IS NOT NULL)

THEN TO_CHAR(PATI.cmr_event_cost_id)

WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND (doc.document_code = 'TRANSFER_ORDER' OR doc_entry.doc_entry_code = 'ITEM') AND PATI.cmr_event_cost_id IS NULL)

THEN (select TO_CHAR(event_cost_id) from cmr_rcv_distributions where cmr_sub_ledger_id = (ORIGINAL_DIST_ID))

WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc_entry.doc_entry_code IN ('NONREC_TAX','NONREC_TAX_INCLUSIVE','ERV','IPV','TERV','TIPV','TRV') AND pati.backing_dist_id1 is not null

AND pati.backing_dist_id2 is not null AND pati.backing_header_id1 is null) THEN TO_CHAR(pati.backing_dist_id1)

WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc_entry.doc_entry_code IN ('NONREC_TAX','NONREC_TAX_INCLUSIVE','ERV','IPV','TERV','TIPV','TRV') AND PATI.cmr_event_cost_id IS NOT NULL)

THEN TO_CHAR(PATI.cmr_event_cost_id)

WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc_entry.doc_entry_code IN ('NONREC_TAX','NONREC_TAX_INCLUSIVE','ERV','IPV','TERV','TIPV','TRV') AND PATI.cmr_event_cost_id IS NULL)

THEN (select TO_CHAR(event_cost_id) from cmr_rcv_distributions where cmr_sub_ledger_id = (ORIGINAL_DIST_ID))

WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc_entry.doc_entry_code IN ('PRICE_ADJ','NONREC_TAX_PRICE_ADJ','NONREC_TAX_PRICE_INCLUSIVE_ADJ')) THEN

NVL(to_char(pati.cmr_event_cost_id),pjc_budgetary_control_int_pkg.getCMREventCostId(PATI.EXPENDITURE_ITEM_ID,pati.data_set_id,ORIGINAL_DIST_ID))

WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc.document_code = 'RECEIPT' and pati.DOC_REF_ID5 IN ('PO_DELIVERY_INTO_INVENTORY','ACQUISITION_COST_ADJUSTMENT','OSP_PO_DELIVERY_INTO_MFG','OSP_ACQ_COST_ADJUSTMENT')) THEN

to_char(pati.BACKING_DIST_ID3)

ELSE NULL

END LIQUIDATION_LINE_ID_1,

CASE

WHEN( TRANSACTION_SOURCE_CODE = 'OAP') THEN TO_CHAR(ORIGINAL_DIST_ID)

WHEN( TRANSACTION_SOURCE_CODE = 'OSCM' AND doc.document_code = 'TRANSFER_ORDER') THEN NULL

WHEN( TRANSACTION_SOURCE_CODE = 'OSCM' AND doc_entry.doc_entry_code IN ('NONREC_TAX','NONREC_TAX_INCLUSIVE','ERV','IPV','TERV','TIPV','TRV') AND pati.backing_dist_id1 is not null

AND pati.backing_dist_id2 is not null AND pati.backing_header_id1 is null) THEN TO_CHAR(pati.backing_dist_id2)

WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc.document_code = 'RECEIPT' AND pati.DOC_REF_ID5 IN ('PO_DELIVERY_INTO_INVENTORY','ACQUISITION_COST_ADJUSTMENT','OSP_PO_DELIVERY_INTO_MFG','OSP_ACQ_COST_ADJUSTMENT')) THEN

to_char(pati.BACKING_DIST_ID4)

ELSE NULL

END LIQUIDATION_LINE_ID_2,

CASE

WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc.document_code = 'RECEIPT' AND pati.DOC_REF_ID5 IN ('PO_DELIVERY_INTO_INVENTORY','ACQUISITION_COST_ADJUSTMENT','OSP_PO_DELIVERY_INTO_MFG','OSP_ACQ_COST_ADJUSTMENT')) THEN

to_char(pati.BACKING_DIST_ID1)

ELSE ' '

END LIQUIDATION_LINE_ID_3,

CASE

WHEN (TRANSACTION_SOURCE_CODE = 'OSCM' AND doc.document_code = 'RECEIPT' AND pati.DOC_REF_ID5 IN ('PO_DELIVERY_INTO_INVENTORY','ACQUISITION_COST_ADJUSTMENT','OSP_PO_DELIVERY_INTO_MFG','OSP_ACQ_COST_ADJUSTMENT')) THEN

to_char(pati.BACKING_DIST_ID2)

ELSE ' '

END LIQUIDATION_LINE_ID_4,

' ' LIQUIDATION_LINE_ID_5,

' ' LIQUIDATION_LINE_ID_6,

NULL STATISTICAL_AMOUNT,

PATI.Budget_CCID BUDGET_CCID,

PATI.VENDOR_ID VENDOR_ID,

PATI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,

null ORDER_TYPE_INFO,

PATI.BUDGET_CCID CODE_COMBINATION_ID,

PATI.PROJECT_ID PJC_PROJECT_ID,

PATI.TASK_ID PJC_TASK_ID,

PATI.PRBS_ELEMENT_ID PJC_RESOURCE_ID,

PATI.CONTRACT_ID PJC_CONTRACT_ID,

PATI.CONTRACT_LINE_ID PJC_CONTRACT_LINE_ID,

PATI.FUNDING_ALLOCATION_ID PJC_FUNDING_ALLOCATION_ID,

PATI.BILLABLE_FLAG PJC_BILLABLE_FLAG,

PATI.CAPITALIZABLE_FLAG PJC_CAPITALIZABLE_FLAG,

PATI.EXPENDITURE_TYPE_ID PJC_EXPENDITURE_TYPE_ID,

PATI.EXPENDITURE_ITEM_DATE PJC_EXPENDITURE_ITEM_DATE,

NVL(PATI.OVERRIDE_TO_ORGANIZATION_ID, PATI.ORGANIZATION_ID) PJC_ORGANIZATION_ID,

PATI.WORK_TYPE_ID PJC_WORK_TYPE_ID,

null CONTROL_BUDGET_ID,

decode(PATI.PROCESS_GROUP_ID, null, null, PATI.PROCESS_GROUP_ID) HEADER_GROUP_CODE,

decode(PATI.CDL_LINE_NUM,1,PATI.QUANTITY,PATI.EXPENDITURE_ITEM_ID) HEADER_NUM,

pati.reserved_attribute1 pjc_funding_source,

pati.TOP_PRBS_ELEMENT_ID pjc_top_resource_id

FROM PJC_TXN_XFACE_ALL PATI,

PJF_BU_IMPL_ALL_V BU,

PJF_TXN_DOCUMENT_B DOC,

pjf_txn_doc_entry_b doc_entry,

PJF_TXN_SOURCES_B SRC

WHERE PATI.ORG_ID = BU.ORG_ID

AND PATI.DOCUMENT_ID = DOC.DOCUMENT_ID

AND PATI.TRANSACTION_SOURCE_ID = SRC.TRANSACTION_SOURCE_ID

AND PATI.TRANSACTION_STATUS_CODE = 'P'

AND PATI.BUDGETARY_CONTROL_VAL_STATUS = 'NOT_ATTEMPTED'

AND Doc.Document_Id = doc_entry.Document_Id

AND PATI.document_id = doc.document_id

AND PATI.doc_entry_id = doc_entry.doc_entry_id