POR_REQ_XLA_DETAILS

Details

Schema: FUSION
Object owner: POR
Object type: VIEW

Query

SQL_Statement
( SELECT REQ.DATA_SET_ID, REQ.REQUISITION_HEADER_ID, REQ.REQUISITION_NUMBER, REQ.REQUISITION_LINE_ID, REQ.DISTRIBUTION_ID, -99 BURDEN_DISTRIBUTION_ID, 'N' BURDEN_COST_FLAG, REQ.LINE_GROUP_CODE, REQ.DISTRIBUTION_ID LINE_NUMBER, REQ.PRIMARY_LEDGER_ID, REQ.REQ_BU_ID, REQ.DESTINATION_TYPE_CODE, REQ.TRANSACTION_SUBTYPE_CODE, REQ.BUDGET_DATE AS DIST_BUDGET_DATE, REQ.CURRENCY_CODE, REQ.RATE_TYPE AS REQ_LINE_RATE_TYPE, REQ.RATE_DATE AS REQ_LINE_RATE_DATE, REQ.RATE AS REQ_LINE_RATE, REQ.UOM_CODE, REQ.DISTRIBUTION_QUANTITY, REQ.UNIT_PRICE, REQ.DISTRIBUTION_CURRENCY_AMOUNT + NVL(REQ.NONRECOVERABLE_CURRENCY_TAX, 0) DISTRIBUTION_CURRENCY_AMOUNT, REQ.DISTRIBUTION_AMOUNT + NVL(REQ.NONRECOVERABLE_TAX, 0) DISTRIBUTION_AMOUNT, REQ.CODE_COMBINATION_ID AS REQ_CODE_COMBINATION_ID, REQ.VENDOR_ID, REQ.ITEM_ID, REQ.PJC_PROJECT_ID, REQ.PJC_TASK_ID, REQ.PJC_CONTRACT_ID, REQ.PJC_CONTRACT_LINE_ID, REQ.PJC_FUNDING_ALLOCATION_ID, REQ.PJC_BILLABLE_FLAG, REQ.PJC_CAPITALIZABLE_FLAG, REQ.PJC_EXPENDITURE_TYPE_ID, REQ.PJC_EXPENDITURE_ITEM_DATE, REQ.PJC_ORGANIZATION_ID, REQ.PJC_WORK_TYPE_ID, REQ.FUNDS_STATUS, REQ.CREATED_BY, REQ.CREATION_DATE, REQ.LAST_UPDATED_BY, REQ.LAST_UPDATE_DATE, REQ.LAST_UPDATE_LOGIN, REQ.OBJECT_VERSION_NUMBER, REQ.EVENT_ID, REQ.NONRECOVERABLE_TAX, REQ.NONRECOVERABLE_CURRENCY_TAX, REQ.ORDER_TYPE_LOOKUP_CODE, GE.ENCUMBRANCE_TYPE_ID, 'POR_REQ_DISTRIBUTIONS_ALL' as TRX_DIST_TYPE, (CASE WHEN (REQ.FUNDS_STATUS in ('RESERVED_PASSED', 'RESERVED_WARNING', 'RESERVED_NO_CONTROL_BUD', 'RESERVED_CHANGED', 'RESERVED')) THEN 'Y' WHEN (REQ.FUNDS_STATUS in ('NOT_ATTEMPTED', 'NOT_RESERVED') AND REQ.EVENT_ID is not null ) THEN 'Y' ELSE 'N' END) TRX_BC_COMPLETION_STATUS FROM POR_REQ_BCEA_DATA REQ, GL_ENCUMBRANCE_TYPES_B GE WHERE GE.encumbrance_type_code = 'Commitment') union all (SELECT REQ.DATA_SET_ID, REQ.REQUISITION_HEADER_ID, REQ.REQUISITION_NUMBER, REQ.REQUISITION_LINE_ID, REQ.DISTRIBUTION_ID LINE_NUMBER, BURDEN.BURDEN_DISTRIBUTION_ID BURDEN_DISTRIBUTION_ID, 'Y' BURDEN_COST_FLAG, REQ.LINE_GROUP_CODE, REQ.LINE_NUMBER, REQ.PRIMARY_LEDGER_ID, REQ.REQ_BU_ID, REQ.DESTINATION_TYPE_CODE, REQ.TRANSACTION_SUBTYPE_CODE, REQ.BUDGET_DATE AS DIST_BUDGET_DATE, REQ.CURRENCY_CODE, REQ.RATE_TYPE AS REQ_LINE_RATE_TYPE, REQ.RATE_DATE AS REQ_LINE_RATE_DATE, REQ.RATE AS REQ_LINE_RATE, REQ.UOM_CODE, REQ.DISTRIBUTION_QUANTITY, REQ.UNIT_PRICE, decode (burden.line_type, 'RESERVE', BURDEN.ENTERED_BURDEN_COST, BURDEN.ENTERED_BURDEN_COST * -1) DISTRIBUTION_CURRENCY_AMOUNT, decode (burden.line_type, 'RESERVE', BURDEN.LEDGER_BURDEN_COST, BURDEN.LEDGER_BURDEN_COST * -1) DISTRIBUTION_AMOUNT, BURDEN.BURDEN_CCID CODE_COMBINATION_ID, REQ.VENDOR_ID, REQ.ITEM_ID, REQ.PJC_PROJECT_ID, REQ.PJC_TASK_ID, REQ.PJC_CONTRACT_ID, REQ.PJC_CONTRACT_LINE_ID, REQ.PJC_FUNDING_ALLOCATION_ID, REQ.PJC_BILLABLE_FLAG, REQ.PJC_CAPITALIZABLE_FLAG, BURDEN.BURDEN_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_TYPE_ID, REQ.PJC_EXPENDITURE_ITEM_DATE, REQ.PJC_ORGANIZATION_ID, REQ.PJC_WORK_TYPE_ID, REQ.FUNDS_STATUS, BURDEN.CREATED_BY, BURDEN.CREATION_DATE, BURDEN.LAST_UPDATED_BY, BURDEN.LAST_UPDATE_DATE, BURDEN.LAST_UPDATE_LOGIN, 1 OBJECT_VERSION_NUMBER, REQ.EVENT_ID, REQ.NONRECOVERABLE_TAX, REQ.NONRECOVERABLE_CURRENCY_TAX, REQ.ORDER_TYPE_LOOKUP_CODE, GE.ENCUMBRANCE_TYPE_ID, 'PJC_XCC_BURDEN_DISTS' as TRX_DIST_TYPE, (CASE WHEN (REQ.FUNDS_STATUS in ('RESERVED_PASSED', 'RESERVED_WARNING', 'RESERVED_NO_CONTROL_BUD', 'RESERVED_CHANGED', 'RESERVED')) THEN 'Y' WHEN (REQ.FUNDS_STATUS in ('NOT_ATTEMPTED', 'NOT_RESERVED') AND REQ.EVENT_ID is not null ) THEN 'Y' ELSE 'N' END) TRX_BC_COMPLETION_STATUS FROM POR_REQ_BCEA_DATA REQ, PJC_XCC_BURDEN_DISTS BURDEN, GL_ENCUMBRANCE_TYPES_B GE WHERE GE.encumbrance_type_code = 'Commitment'and REQ.DATA_SET_ID = BURDEN.DATA_SET_ID and BURDEN.source_line_id_1 = TO_CHAR(REQ.REQUISITION_LINE_ID) and BURDEN.source_line_id_2 = TO_CHAR(REQ.DISTRIBUTION_ID) and BURDEN.transaction_type_code = 'REQUISITION' and BURDEN.source_header_id_1 = TO_CHAR(REQ.REQUISITION_HEADER_ID) and BURDEN.source_header_id_2 = ' ' and BURDEN.source_line_id_3 = ' ' and BURDEN.source_line_id_4 = ' ' and BURDEN.source_line_id_5 = ' ' and BURDEN.source_line_id_6 = ' ')

SQL_Statement

( SELECT

REQ.DATA_SET_ID,

REQ.REQUISITION_HEADER_ID,

REQ.REQUISITION_NUMBER,

REQ.REQUISITION_LINE_ID,

REQ.DISTRIBUTION_ID,

-99 BURDEN_DISTRIBUTION_ID,

'N' BURDEN_COST_FLAG,

REQ.LINE_GROUP_CODE,

REQ.DISTRIBUTION_ID LINE_NUMBER,

REQ.PRIMARY_LEDGER_ID,

REQ.REQ_BU_ID,

REQ.DESTINATION_TYPE_CODE,

REQ.TRANSACTION_SUBTYPE_CODE,

REQ.BUDGET_DATE AS DIST_BUDGET_DATE,

REQ.CURRENCY_CODE,

REQ.RATE_TYPE AS REQ_LINE_RATE_TYPE,

REQ.RATE_DATE AS REQ_LINE_RATE_DATE,

REQ.RATE AS REQ_LINE_RATE,

REQ.UOM_CODE,

REQ.DISTRIBUTION_QUANTITY,

REQ.UNIT_PRICE,

REQ.DISTRIBUTION_CURRENCY_AMOUNT + NVL(REQ.NONRECOVERABLE_CURRENCY_TAX, 0) DISTRIBUTION_CURRENCY_AMOUNT,

REQ.DISTRIBUTION_AMOUNT + NVL(REQ.NONRECOVERABLE_TAX, 0)

DISTRIBUTION_AMOUNT,

REQ.CODE_COMBINATION_ID AS REQ_CODE_COMBINATION_ID,

REQ.VENDOR_ID,

REQ.ITEM_ID,

REQ.PJC_PROJECT_ID,

REQ.PJC_TASK_ID,

REQ.PJC_CONTRACT_ID,

REQ.PJC_CONTRACT_LINE_ID,

REQ.PJC_FUNDING_ALLOCATION_ID,

REQ.PJC_BILLABLE_FLAG,

REQ.PJC_CAPITALIZABLE_FLAG,

REQ.PJC_EXPENDITURE_TYPE_ID,

REQ.PJC_EXPENDITURE_ITEM_DATE,

REQ.PJC_ORGANIZATION_ID,

REQ.PJC_WORK_TYPE_ID,

REQ.FUNDS_STATUS,

REQ.CREATED_BY,

REQ.CREATION_DATE,

REQ.LAST_UPDATED_BY,

REQ.LAST_UPDATE_DATE,

REQ.LAST_UPDATE_LOGIN,

REQ.OBJECT_VERSION_NUMBER,

REQ.EVENT_ID,

REQ.NONRECOVERABLE_TAX,

REQ.NONRECOVERABLE_CURRENCY_TAX,

REQ.ORDER_TYPE_LOOKUP_CODE,

GE.ENCUMBRANCE_TYPE_ID,

'POR_REQ_DISTRIBUTIONS_ALL' as TRX_DIST_TYPE,

(CASE WHEN (REQ.FUNDS_STATUS in ('RESERVED_PASSED',

'RESERVED_WARNING', 'RESERVED_NO_CONTROL_BUD', 'RESERVED_CHANGED', 'RESERVED')) THEN 'Y'

WHEN (REQ.FUNDS_STATUS in ('NOT_ATTEMPTED', 'NOT_RESERVED') AND REQ.EVENT_ID is not null ) THEN 'Y'

ELSE 'N' END) TRX_BC_COMPLETION_STATUS

FROM

POR_REQ_BCEA_DATA REQ,

GL_ENCUMBRANCE_TYPES_B GE

WHERE

GE.encumbrance_type_code = 'Commitment')

union all

(SELECT

REQ.DATA_SET_ID,

REQ.REQUISITION_HEADER_ID,

REQ.REQUISITION_NUMBER,

REQ.REQUISITION_LINE_ID,

REQ.DISTRIBUTION_ID LINE_NUMBER,

BURDEN.BURDEN_DISTRIBUTION_ID BURDEN_DISTRIBUTION_ID,

'Y' BURDEN_COST_FLAG,

REQ.LINE_GROUP_CODE,

REQ.LINE_NUMBER,

REQ.PRIMARY_LEDGER_ID,

REQ.REQ_BU_ID,

REQ.DESTINATION_TYPE_CODE,

REQ.TRANSACTION_SUBTYPE_CODE,

REQ.BUDGET_DATE AS DIST_BUDGET_DATE,

REQ.CURRENCY_CODE,

REQ.RATE_TYPE AS REQ_LINE_RATE_TYPE,

REQ.RATE_DATE AS REQ_LINE_RATE_DATE,

REQ.RATE AS REQ_LINE_RATE,

REQ.UOM_CODE,

REQ.DISTRIBUTION_QUANTITY,

REQ.UNIT_PRICE,

decode (burden.line_type, 'RESERVE', BURDEN.ENTERED_BURDEN_COST, BURDEN.ENTERED_BURDEN_COST * -1) DISTRIBUTION_CURRENCY_AMOUNT,

decode (burden.line_type, 'RESERVE', BURDEN.LEDGER_BURDEN_COST, BURDEN.LEDGER_BURDEN_COST * -1) DISTRIBUTION_AMOUNT,

BURDEN.BURDEN_CCID CODE_COMBINATION_ID,

REQ.VENDOR_ID,

REQ.ITEM_ID,

REQ.PJC_PROJECT_ID,

REQ.PJC_TASK_ID,

REQ.PJC_CONTRACT_ID,

REQ.PJC_CONTRACT_LINE_ID,

REQ.PJC_FUNDING_ALLOCATION_ID,

REQ.PJC_BILLABLE_FLAG,

REQ.PJC_CAPITALIZABLE_FLAG,

BURDEN.BURDEN_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_TYPE_ID,

REQ.PJC_EXPENDITURE_ITEM_DATE,

REQ.PJC_ORGANIZATION_ID,

REQ.PJC_WORK_TYPE_ID,

REQ.FUNDS_STATUS,

BURDEN.CREATED_BY,

BURDEN.CREATION_DATE,

BURDEN.LAST_UPDATED_BY,

BURDEN.LAST_UPDATE_DATE,

BURDEN.LAST_UPDATE_LOGIN,

1 OBJECT_VERSION_NUMBER,

REQ.EVENT_ID,

REQ.NONRECOVERABLE_TAX,

REQ.NONRECOVERABLE_CURRENCY_TAX,

REQ.ORDER_TYPE_LOOKUP_CODE,

GE.ENCUMBRANCE_TYPE_ID,

'PJC_XCC_BURDEN_DISTS' as TRX_DIST_TYPE,

(CASE WHEN (REQ.FUNDS_STATUS in ('RESERVED_PASSED',

'RESERVED_WARNING', 'RESERVED_NO_CONTROL_BUD', 'RESERVED_CHANGED', 'RESERVED')) THEN 'Y'

WHEN (REQ.FUNDS_STATUS in ('NOT_ATTEMPTED', 'NOT_RESERVED') AND REQ.EVENT_ID is not null ) THEN 'Y'

ELSE 'N' END) TRX_BC_COMPLETION_STATUS

FROM

POR_REQ_BCEA_DATA REQ,

PJC_XCC_BURDEN_DISTS BURDEN,

GL_ENCUMBRANCE_TYPES_B GE

WHERE

GE.encumbrance_type_code = 'Commitment'and REQ.DATA_SET_ID = BURDEN.DATA_SET_ID

and BURDEN.source_line_id_1 = TO_CHAR(REQ.REQUISITION_LINE_ID)

and BURDEN.source_line_id_2 = TO_CHAR(REQ.DISTRIBUTION_ID)

and BURDEN.transaction_type_code = 'REQUISITION'

and BURDEN.source_header_id_1 = TO_CHAR(REQ.REQUISITION_HEADER_ID)

and BURDEN.source_header_id_2 = ' '

and BURDEN.source_line_id_3 = ' '

and BURDEN.source_line_id_4 = ' '

and BURDEN.source_line_id_5 = ' '

and BURDEN.source_line_id_6 = ' ')