PO_XLA_DETAILS

Details

Schema: FUSION
Object owner: PO
Object type: VIEW

Columns

Name
DATA_SET_ID PO_HEADER_ID PO_VERSION_ID DOCUMENT_NUMBER PO_LINE_ID PO_LINE_LOCATION_ID PO_DISTRIBUTION_ID DISTRIBUTION_CHANGE_TYPE LINE_GROUP_CODE LINE_NUMBER PRIMARY_LEDGER_ID REQ_BU_ID DESTINATION_TYPE_CODE TRANSACTION_SUBTYPE_CODE DIST_BUDGET_DATE CURRENCY_CODE UOM_CODE DISTRIBUTION_QUANTITY UNIT_PRICE DISTRIBUTION_AMOUNT LEDGER_CURRENCY_RATE ORDER_TYPE_LOOKUP_CODE CODE_COMBINATION_ID VENDOR_ID ITEM_ID FUNDS_STATUS PJC_PROJECT_ID PJC_TASK_ID PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_FUNDING_ALLOCATION_ID PJC_EXPENDITURE_TYPE_ID PJC_ORGANIZATION_ID PJC_WORK_TYPE_ID PJC_BILLABLE_FLAG PJC_CAPITALIZABLE_FLAG PJC_EXPENDITURE_ITEM_DATE CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN OBJECT_VERSION_NUMBER BACKING_REQ_QUANTITY BACKING_REQ_AMOUNT LIQUIDATION_DATE BACKING_REQ_DIST_ID BACKING_REQ_LINE_ID EVENT_ID EVENT_DATE EVENT_TYPE_CODE BC_EVENT TRX_DIST_TYPE REQ_DIST_TYPE TRX_BC_COMPLETION_STATUS ENCUMBRANCE_TYPE_ID BUS_FLOW_REQ_ENTITY_CODE LEDGER_DISTRIBUTION_AMOUNT BURDEN_DISTRIBUTION_ID BACKING_BURDEN_DISTRIBUTION_ID BURDEN_COST_FLAG BACKING_REQ_BC_ENABLED_FLAG BACKING_REQ_EVENT_ID SCHEDULE_DESCRIPTION DISTRIBUTION_RATE_DATE

Name

DATA_SET_ID

PO_HEADER_ID

PO_VERSION_ID

DOCUMENT_NUMBER

PO_LINE_ID

PO_LINE_LOCATION_ID

PO_DISTRIBUTION_ID

DISTRIBUTION_CHANGE_TYPE

LINE_GROUP_CODE

LINE_NUMBER

PRIMARY_LEDGER_ID

REQ_BU_ID

DESTINATION_TYPE_CODE

TRANSACTION_SUBTYPE_CODE

DIST_BUDGET_DATE

CURRENCY_CODE

UOM_CODE

DISTRIBUTION_QUANTITY

UNIT_PRICE

DISTRIBUTION_AMOUNT

LEDGER_CURRENCY_RATE

ORDER_TYPE_LOOKUP_CODE

CODE_COMBINATION_ID

VENDOR_ID

ITEM_ID

FUNDS_STATUS

PJC_PROJECT_ID

PJC_TASK_ID

PJC_CONTRACT_ID

PJC_CONTRACT_LINE_ID

PJC_FUNDING_ALLOCATION_ID

PJC_EXPENDITURE_TYPE_ID

PJC_ORGANIZATION_ID

PJC_WORK_TYPE_ID

PJC_BILLABLE_FLAG

PJC_CAPITALIZABLE_FLAG

PJC_EXPENDITURE_ITEM_DATE

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

OBJECT_VERSION_NUMBER

BACKING_REQ_QUANTITY

BACKING_REQ_AMOUNT

LIQUIDATION_DATE

BACKING_REQ_DIST_ID

BACKING_REQ_LINE_ID

EVENT_ID

EVENT_DATE

EVENT_TYPE_CODE

BC_EVENT

TRX_DIST_TYPE

REQ_DIST_TYPE

TRX_BC_COMPLETION_STATUS

ENCUMBRANCE_TYPE_ID

BUS_FLOW_REQ_ENTITY_CODE

LEDGER_DISTRIBUTION_AMOUNT

BURDEN_DISTRIBUTION_ID

BACKING_BURDEN_DISTRIBUTION_ID

BURDEN_COST_FLAG

BACKING_REQ_BC_ENABLED_FLAG

BACKING_REQ_EVENT_ID

SCHEDULE_DESCRIPTION

DISTRIBUTION_RATE_DATE

Query

SQL_Statement
SELECT PO_BCEA_DATA.DATA_SET_ID DATA_SET_ID, PO_BCEA_DATA.PO_HEADER_ID PO_HEADER_ID, decode(PO_BCEA_DATA.reservation_backout_flag,'Y', -1 ,1) * PO_BCEA_DATA.PO_VERSION_ID PO_VERSION_ID, PO_BCEA_DATA.DOCUMENT_NUMBER DOCUMENT_NUMBER, PO_BCEA_DATA.PO_LINE_ID PO_LINE_ID, PO_BCEA_DATA.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID, PO_BCEA_DATA.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID, PO_BCEA_DATA.DISTRIBUTION_CHANGE_TYPE DISTRIBUTION_CHANGE_TYPE, PO_BCEA_DATA.LINE_GROUP_CODE LINE_GROUP_CODE, PO_BCEA_DATA.PO_DISTRIBUTION_ID LINE_NUMBER, PO_BCEA_DATA.PRIMARY_LEDGER_ID PRIMARY_LEDGER_ID, PO_BCEA_DATA.REQ_BU_ID REQ_BU_ID, PO_BCEA_DATA.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE, PO_BCEA_DATA.TRANSACTION_SUBTYPE_CODE TRANSACTION_SUBTYPE_CODE, PO_BCEA_DATA.BUDGET_DATE DIST_BUDGET_DATE, PO_BCEA_DATA.CURRENCY_CODE CURRENCY_CODE, PO_BCEA_DATA.UOM_CODE UOM_CODE, PO_BCEA_DATA.DISTRIBUTION_QUANTITY DISTRIBUTION_QUANTITY, PO_BCEA_DATA.UNIT_PRICE UNIT_PRICE, PO_BCEA_DATA.DISTRIBUTION_AMOUNT DISTRIBUTION_AMOUNT, PO_BCEA_DATA.LEDGER_CURRENCY_RATE LEDGER_CURRENCY_RATE, PO_BCEA_DATA.ORDER_TYPE_LOOKUP_CODE ORDER_TYPE_LOOKUP_CODE, PO_BCEA_DATA.CODE_COMBINATION_ID CODE_COMBINATION_ID, PO_BCEA_DATA.VENDOR_ID VENDOR_ID, PO_BCEA_DATA.ITEM_ID ITEM_ID, PO_BCEA_DATA.FUNDS_STATUS FUNDS_STATUS, PO_BCEA_DATA.PJC_PROJECT_ID PJC_PROJECT_ID, PO_BCEA_DATA.PJC_TASK_ID PJC_TASK_ID, PO_BCEA_DATA.PJC_CONTRACT_ID PJC_CONTRACT_ID, PO_BCEA_DATA.PJC_CONTRACT_LINE_ID PJC_CONTRACT_LINE_ID, PO_BCEA_DATA.PJC_FUNDING_ALLOCATION_ID PJC_FUNDING_ALLOCATION_ID, PO_BCEA_DATA.PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_TYPE_ID, PO_BCEA_DATA.PJC_ORGANIZATION_ID PJC_ORGANIZATION_ID, PO_BCEA_DATA.PJC_WORK_TYPE_ID PJC_WORK_TYPE_ID, PO_BCEA_DATA.PJC_BILLABLE_FLAG PJC_BILLABLE_FLAG, PO_BCEA_DATA.PJC_CAPITALIZABLE_FLAG PJC_CAPITALIZABLE_FLAG, PO_BCEA_DATA.PJC_EXPENDITURE_ITEM_DATE PJC_EXPENDITURE_ITEM_DATE, PO_BCEA_DATA.CREATED_BY CREATED_BY, PO_BCEA_DATA.CREATION_DATE CREATION_DATE, PO_BCEA_DATA.LAST_UPDATED_BY LAST_UPDATED_BY, PO_BCEA_DATA.LAST_UPDATE_DATE LAST_UPDATE_DATE, PO_BCEA_DATA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, PO_BCEA_DATA.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, PO_BCEA_DATA.BACKING_REQ_QUANTITY BACKING_REQ_QUANTITY, PO_BCEA_DATA.BACKING_REQ_AMOUNT BACKING_REQ_AMOUNT, PO_BCEA_DATA.LIQUIDATION_DATE LIQUIDATION_DATE, PO_BCEA_DATA.BACKING_REQ_DIST_ID BACKING_REQ_DIST_ID, PO_BCEA_DATA.BACKING_REQ_LINE_ID BACKING_REQ_LINE_ID, PO_BCEA_DATA.EVENT_ID EVENT_ID, PO_BCEA_DATA.EVENT_DATE EVENT_DATE, PO_BCEA_DATA.EVENT_TYPE_CODE EVENT_TYPE_CODE, PO_BCEA_DATA.BC_EVENT BC_EVENT , 'PO_DISTRIBUTIONS_ALL' TRX_DIST_TYPE, 'POR_REQ_DISTRIBUTIONS_ALL' REQ_DIST_TYPE, (CASE WHEN (PO_BCEA_DATA.funds_status in ('RESERVED_PASSED', 'RESERVED_WARNING', 'RESERVED_NO_CONTROL_BUD')) THEN 'Y' WHEN (PO_BCEA_DATA.funds_status ='NOT_ATTEMPTED' AND PO_BCEA_DATA.EVENT_ID is not null) THEN 'Y' ELSE 'N' END ) TRX_BC_COMPLETION_STATUS, GET.ENCUMBRANCE_TYPE_ID ENCUMBRANCE_TYPE_ID, 'REQ' BUS_FLOW_REQ_ENTITY_CODE, (PO_BCEA_DATA.DISTRIBUTION_AMOUNT * PO_BCEA_DATA.LEDGER_CURRENCY_RATE) LEDGER_DISTRIBUTION_AMOUNT, -99 BURDEN_DISTRIBUTION_ID, -99 BACKING_BURDEN_DISTRIBUTION_ID, 'N' BURDEN_COST_FLAG, nvl(rh.budget_control_enabled_flag,'N') BACKING_REQ_BC_ENABLED_FLAG, PO_BCEA_DATA.BACKING_REQ_EVENT_ID, POLL.DESCRIPTION SCHEDULE_DESCRIPTION, POD.RATE_DATE DISTRIBUTION_RATE_DATE FROM PO_BCEA_DATA, GL_ENCUMBRANCE_TYPES_B GET, por_requisition_headers_all rh, por_requisition_lines_all rl, po_line_locations_all POLL, po_distributions_all POD WHERE GET.ENCUMBRANCE_TYPE_CODE = 'Obligation' and rl.requisition_header_id = rh.requisition_header_id (+) and PO_BCEA_DATA.BACKING_REQ_LINE_ID = rl.requisition_line_id (+) and PO_BCEA_DATA.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID and PO_BCEA_DATA.po_distribution_id = POD.po_distribution_id union all (SELECT PO_BCEA_DATA.DATA_SET_ID DATA_SET_ID, PO_BCEA_DATA.PO_HEADER_ID PO_HEADER_ID, decode(PO_BCEA_DATA.reservation_backout_flag, 'Y',-1,1) * PO_BCEA_DATA.PO_VERSION_ID PO_VERSION_ID, PO_BCEA_DATA.DOCUMENT_NUMBER DOCUMENT_NUMBER, PO_BCEA_DATA.PO_LINE_ID PO_LINE_ID, PO_BCEA_DATA.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID, PO_BCEA_DATA.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID, PO_BCEA_DATA.DISTRIBUTION_CHANGE_TYPE DISTRIBUTION_CHANGE_TYPE, PO_BCEA_DATA.LINE_GROUP_CODE LINE_GROUP_CODE, PO_BCEA_DATA.LINE_NUMBER LINE_NUMBER, PO_BCEA_DATA.PRIMARY_LEDGER_ID PRIMARY_LEDGER_ID, PO_BCEA_DATA.REQ_BU_ID REQ_BU_ID, PO_BCEA_DATA.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE, PO_BCEA_DATA.TRANSACTION_SUBTYPE_CODE TRANSACTION_SUBTYPE_CODE, PO_BCEA_DATA.BUDGET_DATE DIST_BUDGET_DATE, PO_BCEA_DATA.CURRENCY_CODE CURRENCY_CODE, PO_BCEA_DATA.UOM_CODE UOM_CODE, PO_BCEA_DATA.DISTRIBUTION_QUANTITY DISTRIBUTION_QUANTITY, PO_BCEA_DATA.UNIT_PRICE UNIT_PRICE, decode (burden.TRANSACTION_TYPE_CODE, 'PURCHASE_ORDER', decode(burden.line_type, 'RESERVE', BURDEN.ENTERED_BURDEN_COST, BURDEN.ENTERED_BURDEN_COST * -1),0 ) DISTRIBUTION_AMOUNT, PO_BCEA_DATA.LEDGER_CURRENCY_RATE LEDGER_CURRENCY_RATE, PO_BCEA_DATA.ORDER_TYPE_LOOKUP_CODE ORDER_TYPE_LOOKUP_CODE, BURDEN.BURDEN_CCID CODE_COMBINATION_ID, PO_BCEA_DATA.VENDOR_ID VENDOR_ID, PO_BCEA_DATA.ITEM_ID ITEM_ID, PO_BCEA_DATA.FUNDS_STATUS FUNDS_STATUS, PO_BCEA_DATA.PJC_PROJECT_ID PJC_PROJECT_ID, PO_BCEA_DATA.PJC_TASK_ID PJC_TASK_ID, PO_BCEA_DATA.PJC_CONTRACT_ID PJC_CONTRACT_ID, PO_BCEA_DATA.PJC_CONTRACT_LINE_ID PJC_CONTRACT_LINE_ID, PO_BCEA_DATA.PJC_FUNDING_ALLOCATION_ID PJC_FUNDING_ALLOCATION_ID, BURDEN.BURDEN_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_TYPE_ID, PO_BCEA_DATA.PJC_ORGANIZATION_ID PJC_ORGANIZATION_ID, PO_BCEA_DATA.PJC_WORK_TYPE_ID PJC_WORK_TYPE_ID, PO_BCEA_DATA.PJC_BILLABLE_FLAG PJC_BILLABLE_FLAG, PO_BCEA_DATA.PJC_CAPITALIZABLE_FLAG PJC_CAPITALIZABLE_FLAG, PO_BCEA_DATA.PJC_EXPENDITURE_ITEM_DATE PJC_EXPENDITURE_ITEM_DATE, BURDEN.CREATED_BY CREATED_BY, BURDEN.CREATION_DATE CREATION_DATE, BURDEN.LAST_UPDATED_BY LAST_UPDATED_BY, BURDEN.LAST_UPDATE_DATE LAST_UPDATE_DATE, BURDEN.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, PO_BCEA_DATA.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, PO_BCEA_DATA.BACKING_REQ_QUANTITY BACKING_REQ_QUANTITY, BURDEN.BACKING_COST BACKING_REQ_AMOUNT, PO_BCEA_DATA.LIQUIDATION_DATE LIQUIDATION_DATE, decode(BURDEN.BACKING_COST, null, to_number(null),PO_BCEA_DATA.BACKING_REQ_DIST_ID) BACKING_REQ_DIST_ID, decode(BURDEN.BACKING_COST, null, to_number(null),PO_BCEA_DATA.BACKING_REQ_LINE_ID) BACKING_REQ_LINE_ID, PO_BCEA_DATA.EVENT_ID EVENT_ID, PO_BCEA_DATA.EVENT_DATE EVENT_DATE, PO_BCEA_DATA.EVENT_TYPE_CODE EVENT_TYPE_CODE, PO_BCEA_DATA.BC_EVENT BC_EVENT , 'PJC_XCC_BURDEN_DISTS' TRX_DIST_TYPE, 'PJC_XCC_BURDEN_DISTS' REQ_DIST_TYPE, (CASE WHEN (PO_BCEA_DATA.funds_status in ('RESERVED_PASSED', 'RESERVED_WARNING', 'RESERVED_NO_CONTROL_BUD')) THEN 'Y' WHEN (PO_BCEA_DATA.funds_status ='NOT_ATTEMPTED' AND PO_BCEA_DATA.EVENT_ID is not null) THEN 'Y' ELSE 'N' END ) TRX_BC_COMPLETION_STATUS, GET.ENCUMBRANCE_TYPE_ID ENCUMBRANCE_TYPE_ID, 'REQ' BUS_FLOW_REQ_ENTITY_CODE, decode (burden.TRANSACTION_TYPE_CODE, 'PURCHASE_ORDER', decode(burden.line_type, 'RESERVE', BURDEN.LEDGER_BURDEN_COST, BURDEN.LEDGER_BURDEN_COST * -1),0 ) LEDGER_DISTRIBUTION_AMOUNT, BURDEN.BURDEN_DISTRIBUTION_ID BURDEN_DISTRIBUTION_ID, decode(BURDEN.BACKING_COST, null, to_number(null),BURDEN.BACKING_BURDEN_DIST_ID) BACKING_BURDEN_DISTRIBUTION_ID, 'Y' BURDEN_COST_FLAG, nvl(rh.budget_control_enabled_flag,'N') BACKING_REQ_BC_ENABLED_FLAG, PO_BCEA_DATA.BACKING_REQ_EVENT_ID, POLL.DESCRIPTION SCHEDULE_DESCRIPTION, POD.RATE_DATE DISTRIBUTION_RATE_DATE FROM PJC_XCC_BURDEN_DISTS BURDEN, PO_BCEA_DATA, GL_ENCUMBRANCE_TYPES_B GET, por_requisition_headers_all rh, por_requisition_lines_all rl, po_line_locations_all POLL, po_distributions_all POD WHERE GET.ENCUMBRANCE_TYPE_CODE = 'Obligation' and rl.requisition_header_id = rh.requisition_header_id (+) and PO_BCEA_DATA.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID and PO_BCEA_DATA.BACKING_REQ_LINE_ID = rl.requisition_line_id (+) and PO_BCEA_DATA.po_distribution_id = POD.po_distribution_id and PO_BCEA_DATA.DATA_SET_ID = BURDEN.DATA_SET_ID and BURDEN.parent_source_line_id_1 = to_char(PO_BCEA_DATA.PO_LINE_ID) and BURDEN.parent_source_line_id_2 = to_char(PO_BCEA_DATA.PO_LINE_LOCATION_ID) and BURDEN.parent_transaction_type_code = 'PURCHASE_ORDER' and BURDEN.parent_source_header_id_1 = to_char(PO_BCEA_DATA.PO_HEADER_ID) and BURDEN.parent_source_header_id_2 = nvl(to_char(PO_BCEA_DATA.PO_VERSION_ID), ' ') and BURDEN.parent_source_line_id_3 = to_char(po_bcea_data.PO_DISTRIBUTION_ID) and BURDEN.parent_source_line_id_4 = decode(BURDEN.source_action_code, 'FINALLY_CLOSE_PO', PO_BCEA_DATA.DATA_SET_ID,'CF_FINALLY_CLOSE_PO', PO_BCEA_DATA.DATA_SET_ID, 'REOPEN_PO',PO_BCEA_DATA.DATA_SET_ID, 'CF_REOPEN_PO',PO_BCEA_DATA.DATA_SET_ID, nvl(to_char(PO_BCEA_DATA.PO_VERSION_ID), ' ')) and BURDEN.transaction_type_code = 'PURCHASE_ORDER' )

SQL_Statement

SELECT

PO_BCEA_DATA.DATA_SET_ID DATA_SET_ID,

PO_BCEA_DATA.PO_HEADER_ID PO_HEADER_ID,

decode(PO_BCEA_DATA.reservation_backout_flag,'Y', -1 ,1) *

PO_BCEA_DATA.PO_VERSION_ID PO_VERSION_ID,

PO_BCEA_DATA.DOCUMENT_NUMBER DOCUMENT_NUMBER,

PO_BCEA_DATA.PO_LINE_ID PO_LINE_ID,

PO_BCEA_DATA.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID,

PO_BCEA_DATA.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID,

PO_BCEA_DATA.DISTRIBUTION_CHANGE_TYPE DISTRIBUTION_CHANGE_TYPE,

PO_BCEA_DATA.LINE_GROUP_CODE LINE_GROUP_CODE,

PO_BCEA_DATA.PO_DISTRIBUTION_ID LINE_NUMBER,

PO_BCEA_DATA.PRIMARY_LEDGER_ID PRIMARY_LEDGER_ID,

PO_BCEA_DATA.REQ_BU_ID REQ_BU_ID,

PO_BCEA_DATA.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE,

PO_BCEA_DATA.TRANSACTION_SUBTYPE_CODE TRANSACTION_SUBTYPE_CODE,

PO_BCEA_DATA.BUDGET_DATE DIST_BUDGET_DATE,

PO_BCEA_DATA.CURRENCY_CODE CURRENCY_CODE,

PO_BCEA_DATA.UOM_CODE UOM_CODE,

PO_BCEA_DATA.DISTRIBUTION_QUANTITY DISTRIBUTION_QUANTITY,

PO_BCEA_DATA.UNIT_PRICE UNIT_PRICE,

PO_BCEA_DATA.DISTRIBUTION_AMOUNT DISTRIBUTION_AMOUNT,

PO_BCEA_DATA.LEDGER_CURRENCY_RATE LEDGER_CURRENCY_RATE,

PO_BCEA_DATA.ORDER_TYPE_LOOKUP_CODE ORDER_TYPE_LOOKUP_CODE,

PO_BCEA_DATA.CODE_COMBINATION_ID CODE_COMBINATION_ID,

PO_BCEA_DATA.VENDOR_ID VENDOR_ID,

PO_BCEA_DATA.ITEM_ID ITEM_ID,

PO_BCEA_DATA.FUNDS_STATUS FUNDS_STATUS,

PO_BCEA_DATA.PJC_PROJECT_ID PJC_PROJECT_ID,

PO_BCEA_DATA.PJC_TASK_ID PJC_TASK_ID,

PO_BCEA_DATA.PJC_CONTRACT_ID PJC_CONTRACT_ID,

PO_BCEA_DATA.PJC_CONTRACT_LINE_ID PJC_CONTRACT_LINE_ID,

PO_BCEA_DATA.PJC_FUNDING_ALLOCATION_ID PJC_FUNDING_ALLOCATION_ID,

PO_BCEA_DATA.PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_TYPE_ID,

PO_BCEA_DATA.PJC_ORGANIZATION_ID PJC_ORGANIZATION_ID,

PO_BCEA_DATA.PJC_WORK_TYPE_ID PJC_WORK_TYPE_ID,

PO_BCEA_DATA.PJC_BILLABLE_FLAG PJC_BILLABLE_FLAG,

PO_BCEA_DATA.PJC_CAPITALIZABLE_FLAG PJC_CAPITALIZABLE_FLAG,

PO_BCEA_DATA.PJC_EXPENDITURE_ITEM_DATE PJC_EXPENDITURE_ITEM_DATE,

PO_BCEA_DATA.CREATED_BY CREATED_BY,

PO_BCEA_DATA.CREATION_DATE CREATION_DATE,

PO_BCEA_DATA.LAST_UPDATED_BY LAST_UPDATED_BY,

PO_BCEA_DATA.LAST_UPDATE_DATE LAST_UPDATE_DATE,

PO_BCEA_DATA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN,

PO_BCEA_DATA.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER,

PO_BCEA_DATA.BACKING_REQ_QUANTITY BACKING_REQ_QUANTITY,

PO_BCEA_DATA.BACKING_REQ_AMOUNT BACKING_REQ_AMOUNT,

PO_BCEA_DATA.LIQUIDATION_DATE LIQUIDATION_DATE,

PO_BCEA_DATA.BACKING_REQ_DIST_ID BACKING_REQ_DIST_ID,

PO_BCEA_DATA.BACKING_REQ_LINE_ID BACKING_REQ_LINE_ID,

PO_BCEA_DATA.EVENT_ID EVENT_ID,

PO_BCEA_DATA.EVENT_DATE EVENT_DATE,

PO_BCEA_DATA.EVENT_TYPE_CODE EVENT_TYPE_CODE,

PO_BCEA_DATA.BC_EVENT BC_EVENT ,

'PO_DISTRIBUTIONS_ALL' TRX_DIST_TYPE,

'POR_REQ_DISTRIBUTIONS_ALL' REQ_DIST_TYPE,

(CASE WHEN (PO_BCEA_DATA.funds_status in ('RESERVED_PASSED',

'RESERVED_WARNING', 'RESERVED_NO_CONTROL_BUD')) THEN 'Y'

WHEN (PO_BCEA_DATA.funds_status ='NOT_ATTEMPTED' AND

PO_BCEA_DATA.EVENT_ID is not null) THEN 'Y'

ELSE 'N' END ) TRX_BC_COMPLETION_STATUS,

GET.ENCUMBRANCE_TYPE_ID ENCUMBRANCE_TYPE_ID,

'REQ' BUS_FLOW_REQ_ENTITY_CODE,

(PO_BCEA_DATA.DISTRIBUTION_AMOUNT * PO_BCEA_DATA.LEDGER_CURRENCY_RATE) LEDGER_DISTRIBUTION_AMOUNT,

-99 BURDEN_DISTRIBUTION_ID,

-99 BACKING_BURDEN_DISTRIBUTION_ID,

'N' BURDEN_COST_FLAG,

nvl(rh.budget_control_enabled_flag,'N') BACKING_REQ_BC_ENABLED_FLAG,

PO_BCEA_DATA.BACKING_REQ_EVENT_ID,

POLL.DESCRIPTION SCHEDULE_DESCRIPTION,

POD.RATE_DATE DISTRIBUTION_RATE_DATE

FROM

PO_BCEA_DATA,

GL_ENCUMBRANCE_TYPES_B GET,

por_requisition_headers_all rh,

por_requisition_lines_all rl,

po_line_locations_all POLL,

po_distributions_all POD

WHERE

GET.ENCUMBRANCE_TYPE_CODE = 'Obligation'

and rl.requisition_header_id = rh.requisition_header_id (+)

and PO_BCEA_DATA.BACKING_REQ_LINE_ID = rl.requisition_line_id (+)

and PO_BCEA_DATA.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID

and PO_BCEA_DATA.po_distribution_id = POD.po_distribution_id

union all

(SELECT

PO_BCEA_DATA.DATA_SET_ID DATA_SET_ID,

PO_BCEA_DATA.PO_HEADER_ID PO_HEADER_ID,

decode(PO_BCEA_DATA.reservation_backout_flag, 'Y',-1,1) *

PO_BCEA_DATA.PO_VERSION_ID PO_VERSION_ID,

PO_BCEA_DATA.DOCUMENT_NUMBER DOCUMENT_NUMBER,

PO_BCEA_DATA.PO_LINE_ID PO_LINE_ID,

PO_BCEA_DATA.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID,

PO_BCEA_DATA.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID,

PO_BCEA_DATA.DISTRIBUTION_CHANGE_TYPE DISTRIBUTION_CHANGE_TYPE,

PO_BCEA_DATA.LINE_GROUP_CODE LINE_GROUP_CODE,

PO_BCEA_DATA.LINE_NUMBER LINE_NUMBER,

PO_BCEA_DATA.PRIMARY_LEDGER_ID PRIMARY_LEDGER_ID,

PO_BCEA_DATA.REQ_BU_ID REQ_BU_ID,

PO_BCEA_DATA.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE,

PO_BCEA_DATA.TRANSACTION_SUBTYPE_CODE TRANSACTION_SUBTYPE_CODE,

PO_BCEA_DATA.BUDGET_DATE DIST_BUDGET_DATE,

PO_BCEA_DATA.CURRENCY_CODE CURRENCY_CODE,

PO_BCEA_DATA.UOM_CODE UOM_CODE,

PO_BCEA_DATA.DISTRIBUTION_QUANTITY DISTRIBUTION_QUANTITY,

PO_BCEA_DATA.UNIT_PRICE UNIT_PRICE,

decode (burden.TRANSACTION_TYPE_CODE, 'PURCHASE_ORDER', decode(burden.line_type, 'RESERVE', BURDEN.ENTERED_BURDEN_COST, BURDEN.ENTERED_BURDEN_COST * -1),0 ) DISTRIBUTION_AMOUNT, PO_BCEA_DATA.LEDGER_CURRENCY_RATE LEDGER_CURRENCY_RATE,

PO_BCEA_DATA.ORDER_TYPE_LOOKUP_CODE ORDER_TYPE_LOOKUP_CODE,

BURDEN.BURDEN_CCID CODE_COMBINATION_ID,

PO_BCEA_DATA.VENDOR_ID VENDOR_ID,

PO_BCEA_DATA.ITEM_ID ITEM_ID,

PO_BCEA_DATA.FUNDS_STATUS FUNDS_STATUS,

PO_BCEA_DATA.PJC_PROJECT_ID PJC_PROJECT_ID,

PO_BCEA_DATA.PJC_TASK_ID PJC_TASK_ID,

PO_BCEA_DATA.PJC_CONTRACT_ID PJC_CONTRACT_ID,

PO_BCEA_DATA.PJC_CONTRACT_LINE_ID PJC_CONTRACT_LINE_ID,

PO_BCEA_DATA.PJC_FUNDING_ALLOCATION_ID PJC_FUNDING_ALLOCATION_ID,

BURDEN.BURDEN_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_TYPE_ID,

PO_BCEA_DATA.PJC_ORGANIZATION_ID PJC_ORGANIZATION_ID,

PO_BCEA_DATA.PJC_WORK_TYPE_ID PJC_WORK_TYPE_ID,

PO_BCEA_DATA.PJC_BILLABLE_FLAG PJC_BILLABLE_FLAG,

PO_BCEA_DATA.PJC_CAPITALIZABLE_FLAG PJC_CAPITALIZABLE_FLAG,

PO_BCEA_DATA.PJC_EXPENDITURE_ITEM_DATE PJC_EXPENDITURE_ITEM_DATE,

BURDEN.CREATED_BY CREATED_BY,

BURDEN.CREATION_DATE CREATION_DATE,

BURDEN.LAST_UPDATED_BY LAST_UPDATED_BY,

BURDEN.LAST_UPDATE_DATE LAST_UPDATE_DATE,

BURDEN.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN,

PO_BCEA_DATA.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER,

PO_BCEA_DATA.BACKING_REQ_QUANTITY BACKING_REQ_QUANTITY,

BURDEN.BACKING_COST BACKING_REQ_AMOUNT,

PO_BCEA_DATA.LIQUIDATION_DATE LIQUIDATION_DATE,

decode(BURDEN.BACKING_COST, null, to_number(null),PO_BCEA_DATA.BACKING_REQ_DIST_ID) BACKING_REQ_DIST_ID,

decode(BURDEN.BACKING_COST, null, to_number(null),PO_BCEA_DATA.BACKING_REQ_LINE_ID) BACKING_REQ_LINE_ID,

PO_BCEA_DATA.EVENT_ID EVENT_ID,

PO_BCEA_DATA.EVENT_DATE EVENT_DATE,

PO_BCEA_DATA.EVENT_TYPE_CODE EVENT_TYPE_CODE,

PO_BCEA_DATA.BC_EVENT BC_EVENT ,

'PJC_XCC_BURDEN_DISTS' TRX_DIST_TYPE,

'PJC_XCC_BURDEN_DISTS' REQ_DIST_TYPE,

(CASE WHEN (PO_BCEA_DATA.funds_status in ('RESERVED_PASSED',

'RESERVED_WARNING', 'RESERVED_NO_CONTROL_BUD')) THEN 'Y'

WHEN (PO_BCEA_DATA.funds_status ='NOT_ATTEMPTED' AND

PO_BCEA_DATA.EVENT_ID is not null) THEN 'Y'

ELSE 'N' END ) TRX_BC_COMPLETION_STATUS,

GET.ENCUMBRANCE_TYPE_ID ENCUMBRANCE_TYPE_ID,

'REQ' BUS_FLOW_REQ_ENTITY_CODE,

decode (burden.TRANSACTION_TYPE_CODE, 'PURCHASE_ORDER', decode(burden.line_type, 'RESERVE', BURDEN.LEDGER_BURDEN_COST, BURDEN.LEDGER_BURDEN_COST * -1),0 ) LEDGER_DISTRIBUTION_AMOUNT,

BURDEN.BURDEN_DISTRIBUTION_ID BURDEN_DISTRIBUTION_ID,

decode(BURDEN.BACKING_COST, null, to_number(null),BURDEN.BACKING_BURDEN_DIST_ID) BACKING_BURDEN_DISTRIBUTION_ID,

'Y' BURDEN_COST_FLAG,

nvl(rh.budget_control_enabled_flag,'N') BACKING_REQ_BC_ENABLED_FLAG,

PO_BCEA_DATA.BACKING_REQ_EVENT_ID,

POLL.DESCRIPTION SCHEDULE_DESCRIPTION,

POD.RATE_DATE DISTRIBUTION_RATE_DATE

FROM

PJC_XCC_BURDEN_DISTS BURDEN,

PO_BCEA_DATA,

GL_ENCUMBRANCE_TYPES_B GET,

por_requisition_headers_all rh,

por_requisition_lines_all rl,

po_line_locations_all POLL,

po_distributions_all POD

WHERE

GET.ENCUMBRANCE_TYPE_CODE = 'Obligation'

and rl.requisition_header_id = rh.requisition_header_id (+)

and PO_BCEA_DATA.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID

and PO_BCEA_DATA.BACKING_REQ_LINE_ID = rl.requisition_line_id (+)

and PO_BCEA_DATA.po_distribution_id = POD.po_distribution_id

and PO_BCEA_DATA.DATA_SET_ID = BURDEN.DATA_SET_ID

and BURDEN.parent_source_line_id_1 = to_char(PO_BCEA_DATA.PO_LINE_ID)

and BURDEN.parent_source_line_id_2 = to_char(PO_BCEA_DATA.PO_LINE_LOCATION_ID)

and BURDEN.parent_transaction_type_code = 'PURCHASE_ORDER'

and BURDEN.parent_source_header_id_1 = to_char(PO_BCEA_DATA.PO_HEADER_ID)

and BURDEN.parent_source_header_id_2 = nvl(to_char(PO_BCEA_DATA.PO_VERSION_ID), ' ')

and BURDEN.parent_source_line_id_3 = to_char(po_bcea_data.PO_DISTRIBUTION_ID)

and BURDEN.parent_source_line_id_4 = decode(BURDEN.source_action_code, 'FINALLY_CLOSE_PO', PO_BCEA_DATA.DATA_SET_ID,'CF_FINALLY_CLOSE_PO', PO_BCEA_DATA.DATA_SET_ID, 'REOPEN_PO',PO_BCEA_DATA.DATA_SET_ID, 'CF_REOPEN_PO',PO_BCEA_DATA.DATA_SET_ID, nvl(to_char(PO_BCEA_DATA.PO_VERSION_ID), ' '))

and BURDEN.transaction_type_code = 'PURCHASE_ORDER' )