AP_PPM_PROJECT_SOURCES

Details

Schema: FUSION
Object owner: AP
Object type: VIEW

Columns

Name
SOURCE_HEADER_ID_1 SOURCE_HEADER_ID_2 SOURCE_LINE_ID_1 SOURCE_LINE_ID_2 SOURCE_LINE_ID_3 SOURCE_LINE_ID_4 SOURCE_LINE_ID_5 SOURCE_LINE_ID_6 FUNDS_STATUS PJC_BILLABLE_FLAG PJC_CAPITALIZABLE_FLAG PJC_CONTEXT_CATEGORY PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_EXPENDITURE_ITEM_DATE PJC_EXPENDITURE_TYPE_ID PJC_FUNDING_ALLOCATION_ID PJC_ORGANIZATION_ID PJC_PROJECT_ID PJC_RESERVED_ATTRIBUTE1 PJC_RESERVED_ATTRIBUTE10 PJC_RESERVED_ATTRIBUTE2 PJC_RESERVED_ATTRIBUTE3 PJC_RESERVED_ATTRIBUTE4 PJC_RESERVED_ATTRIBUTE5 PJC_RESERVED_ATTRIBUTE6 PJC_RESERVED_ATTRIBUTE7 PJC_RESERVED_ATTRIBUTE8 PJC_RESERVED_ATTRIBUTE9 PJC_TASK_ID PJC_USER_DEF_ATTRIBUTE1 PJC_USER_DEF_ATTRIBUTE10 PJC_USER_DEF_ATTRIBUTE2 PJC_USER_DEF_ATTRIBUTE3 PJC_USER_DEF_ATTRIBUTE4 PJC_USER_DEF_ATTRIBUTE5 PJC_USER_DEF_ATTRIBUTE6 PJC_USER_DEF_ATTRIBUTE7 PJC_USER_DEF_ATTRIBUTE8 PJC_USER_DEF_ATTRIBUTE9 PJC_WORK_TYPE_ID LIQUIDATION_AMOUNT LIQUIDATION_BASE_AMOUNT PARENT_REVERSAL_ID LINE_TYPE_LOOKUP_CODE TRANSACTION_SUBTYPE_CODE

Name

SOURCE_HEADER_ID_1

SOURCE_HEADER_ID_2

SOURCE_LINE_ID_1

SOURCE_LINE_ID_2

SOURCE_LINE_ID_3

SOURCE_LINE_ID_4

SOURCE_LINE_ID_5

SOURCE_LINE_ID_6

FUNDS_STATUS

PJC_BILLABLE_FLAG

PJC_CAPITALIZABLE_FLAG

PJC_CONTEXT_CATEGORY

PJC_CONTRACT_ID

PJC_CONTRACT_LINE_ID

PJC_EXPENDITURE_ITEM_DATE

PJC_EXPENDITURE_TYPE_ID

PJC_FUNDING_ALLOCATION_ID

PJC_ORGANIZATION_ID

PJC_PROJECT_ID

PJC_RESERVED_ATTRIBUTE1

PJC_RESERVED_ATTRIBUTE10

PJC_RESERVED_ATTRIBUTE2

PJC_RESERVED_ATTRIBUTE3

PJC_RESERVED_ATTRIBUTE4

PJC_RESERVED_ATTRIBUTE5

PJC_RESERVED_ATTRIBUTE6

PJC_RESERVED_ATTRIBUTE7

PJC_RESERVED_ATTRIBUTE8

PJC_RESERVED_ATTRIBUTE9

PJC_TASK_ID

PJC_USER_DEF_ATTRIBUTE1

PJC_USER_DEF_ATTRIBUTE10

PJC_USER_DEF_ATTRIBUTE2

PJC_USER_DEF_ATTRIBUTE3

PJC_USER_DEF_ATTRIBUTE4

PJC_USER_DEF_ATTRIBUTE5

PJC_USER_DEF_ATTRIBUTE6

PJC_USER_DEF_ATTRIBUTE7

PJC_USER_DEF_ATTRIBUTE8

PJC_USER_DEF_ATTRIBUTE9

PJC_WORK_TYPE_ID

LIQUIDATION_AMOUNT

LIQUIDATION_BASE_AMOUNT

PARENT_REVERSAL_ID

LINE_TYPE_LOOKUP_CODE

TRANSACTION_SUBTYPE_CODE

Query

SQL_Statement
SELECT AID.INVOICE_ID SOURCE_HEADER_ID_1 , ' ' SOURCE_HEADER_ID_2 , AID.INVOICE_LINE_NUMBER SOURCE_LINE_ID_1 , AID.INVOICE_DISTRIBUTION_ID SOURCE_LINE_ID_2 , ' ' SOURCE_LINE_ID_3 , ' ' SOURCE_LINE_ID_4 , ' ' SOURCE_LINE_ID_5 , ' ' SOURCE_LINE_ID_6 , AID.FUNDS_STATUS , AID.PJC_BILLABLE_FLAG , AID.PJC_CAPITALIZABLE_FLAG , AID.PJC_CONTEXT_CATEGORY , AID.PJC_CONTRACT_ID , AID.PJC_CONTRACT_LINE_ID , AID.PJC_EXPENDITURE_ITEM_DATE , AID.PJC_EXPENDITURE_TYPE_ID , AID.PJC_FUNDING_ALLOCATION_ID , AID.PJC_ORGANIZATION_ID , AID.PJC_PROJECT_ID , AID.PJC_RESERVED_ATTRIBUTE1 , AID.PJC_RESERVED_ATTRIBUTE10 , AID.PJC_RESERVED_ATTRIBUTE2 , AID.PJC_RESERVED_ATTRIBUTE3 , AID.PJC_RESERVED_ATTRIBUTE4 , AID.PJC_RESERVED_ATTRIBUTE5 , AID.PJC_RESERVED_ATTRIBUTE6 , AID.PJC_RESERVED_ATTRIBUTE7 , AID.PJC_RESERVED_ATTRIBUTE8 , AID.PJC_RESERVED_ATTRIBUTE9 , AID.PJC_TASK_ID , AID.PJC_USER_DEF_ATTRIBUTE1 , AID.PJC_USER_DEF_ATTRIBUTE10 , AID.PJC_USER_DEF_ATTRIBUTE2 , AID.PJC_USER_DEF_ATTRIBUTE3 , AID.PJC_USER_DEF_ATTRIBUTE4 , AID.PJC_USER_DEF_ATTRIBUTE5 , AID.PJC_USER_DEF_ATTRIBUTE6 , AID.PJC_USER_DEF_ATTRIBUTE7 , AID.PJC_USER_DEF_ATTRIBUTE8 , AID.PJC_USER_DEF_ATTRIBUTE9 , AID.PJC_WORK_TYPE_ID, CASE WHEN (AID.po_distribution_id IS NOT NULL AND nvl(pod.accrue_on_receipt_flag, 'N') = 'N') THEN CASE WHEN (aid.line_type_lookup_code = 'NONREC_TAX' and aid.prepay_distribution_id IS NOT NULL) THEN AID.AMOUNT - (NVL(AID.QUANTITY_VARIANCE, 0) + NVL(AID.AMOUNT_VARIANCE, 0) + NVL( AP_COMMITMENT_CONTROL_PKG.GET_PREPAY_TOTAL_TAX_VARIANCE(AID.INVOICE_DISTRIBUTION_ID),0)) WHEN (aid.line_type_lookup_code in ('ITEM', 'ACCRUAL', 'NONREC_TAX')) THEN AID.AMOUNT - (NVL(AID.QUANTITY_VARIANCE, 0) + NVL(AID.AMOUNT_VARIANCE, 0)) ELSE NULL END ELSE NULL END LIQUIDATION_AMOUNT , CASE WHEN (AID.po_distribution_id IS NOT NULL AND nvl(pod.accrue_on_receipt_flag, 'N') = 'N') THEN CASE WHEN (aid.line_type_lookup_code in ('ITEM', 'ACCRUAL', 'NONREC_TAX')) THEN AID.BASE_AMOUNT - (NVL(AID.BASE_QUANTITY_VARIANCE, 0) + NVL(AID.BASE_AMOUNT_VARIANCE, 0)) ELSE NULL END ELSE NULL END LIQUIDATION_BASE_AMOUNT, AID.PARENT_REVERSAL_ID, AID.LINE_TYPE_LOOKUP_CODE, DECODE(AID.PO_DISTRIBUTION_ID,NULL,'UNMATCHED_INVOICE', 'MATCHED_INVOICE') TRANSACTION_SUBTYPE_CODE FROM AP_INVOICE_DISTRIBUTIONS_ALL AID, PO_DISTRIBUTIONS_ALL POD WHERE AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID (+) UNION ALL SELECT ASTD.INVOICE_ID SOURCE_HEADER_ID_1 , ' ' SOURCE_HEADER_ID_2 , ASTD.INVOICE_LINE_NUMBER SOURCE_LINE_ID_1 , ASTD.INVOICE_DISTRIBUTION_ID SOURCE_LINE_ID_2 , ' ' SOURCE_LINE_ID_3 , ' ' SOURCE_LINE_ID_4 , ' ' SOURCE_LINE_ID_5 , ' ' SOURCE_LINE_ID_6 , FUNDS_STATUS , PJC_BILLABLE_FLAG , PJC_CAPITALIZABLE_FLAG , PJC_CONTEXT_CATEGORY , PJC_CONTRACT_ID , PJC_CONTRACT_LINE_ID , PJC_EXPENDITURE_ITEM_DATE , PJC_EXPENDITURE_TYPE_ID , PJC_FUNDING_ALLOCATION_ID , PJC_ORGANIZATION_ID , PJC_PROJECT_ID , PJC_RESERVED_ATTRIBUTE1 , PJC_RESERVED_ATTRIBUTE10 , PJC_RESERVED_ATTRIBUTE2 , PJC_RESERVED_ATTRIBUTE3 , PJC_RESERVED_ATTRIBUTE4 , PJC_RESERVED_ATTRIBUTE5 , PJC_RESERVED_ATTRIBUTE6 , PJC_RESERVED_ATTRIBUTE7 , PJC_RESERVED_ATTRIBUTE8 , PJC_RESERVED_ATTRIBUTE9 , PJC_TASK_ID , PJC_USER_DEF_ATTRIBUTE1 , PJC_USER_DEF_ATTRIBUTE10 , PJC_USER_DEF_ATTRIBUTE2 , PJC_USER_DEF_ATTRIBUTE3 , PJC_USER_DEF_ATTRIBUTE4 , PJC_USER_DEF_ATTRIBUTE5 , PJC_USER_DEF_ATTRIBUTE6 , PJC_USER_DEF_ATTRIBUTE7 , PJC_USER_DEF_ATTRIBUTE8 , PJC_USER_DEF_ATTRIBUTE9 , PJC_WORK_TYPE_ID, NULL LIQUIDATION_AMOUNT, NULL LIQUIDATION_BASE_AMOUNT, ASTD.PARENT_REVERSAL_ID, ASTD.LINE_TYPE_LOOKUP_CODE, DECODE(ASTD.PO_DISTRIBUTION_ID,NULL,'UNMATCHED_INVOICE', 'MATCHED_INVOICE') TRANSACTION_SUBTYPE_CODE FROM AP_SELF_ASSESSED_TAX_DIST_ALL ASTD

SQL_Statement

SELECT AID.INVOICE_ID SOURCE_HEADER_ID_1 ,

' ' SOURCE_HEADER_ID_2 ,

AID.INVOICE_LINE_NUMBER SOURCE_LINE_ID_1 ,

AID.INVOICE_DISTRIBUTION_ID SOURCE_LINE_ID_2 ,

' ' SOURCE_LINE_ID_3 ,

' ' SOURCE_LINE_ID_4 ,

' ' SOURCE_LINE_ID_5 ,

' ' SOURCE_LINE_ID_6 ,

AID.FUNDS_STATUS ,

AID.PJC_BILLABLE_FLAG ,

AID.PJC_CAPITALIZABLE_FLAG ,

AID.PJC_CONTEXT_CATEGORY ,

AID.PJC_CONTRACT_ID ,

AID.PJC_CONTRACT_LINE_ID ,

AID.PJC_EXPENDITURE_ITEM_DATE ,

AID.PJC_EXPENDITURE_TYPE_ID ,

AID.PJC_FUNDING_ALLOCATION_ID ,

AID.PJC_ORGANIZATION_ID ,

AID.PJC_PROJECT_ID ,

AID.PJC_RESERVED_ATTRIBUTE1 ,

AID.PJC_RESERVED_ATTRIBUTE10 ,

AID.PJC_RESERVED_ATTRIBUTE2 ,

AID.PJC_RESERVED_ATTRIBUTE3 ,

AID.PJC_RESERVED_ATTRIBUTE4 ,

AID.PJC_RESERVED_ATTRIBUTE5 ,

AID.PJC_RESERVED_ATTRIBUTE6 ,

AID.PJC_RESERVED_ATTRIBUTE7 ,

AID.PJC_RESERVED_ATTRIBUTE8 ,

AID.PJC_RESERVED_ATTRIBUTE9 ,

AID.PJC_TASK_ID ,

AID.PJC_USER_DEF_ATTRIBUTE1 ,

AID.PJC_USER_DEF_ATTRIBUTE10 ,

AID.PJC_USER_DEF_ATTRIBUTE2 ,

AID.PJC_USER_DEF_ATTRIBUTE3 ,

AID.PJC_USER_DEF_ATTRIBUTE4 ,

AID.PJC_USER_DEF_ATTRIBUTE5 ,

AID.PJC_USER_DEF_ATTRIBUTE6 ,

AID.PJC_USER_DEF_ATTRIBUTE7 ,

AID.PJC_USER_DEF_ATTRIBUTE8 ,

AID.PJC_USER_DEF_ATTRIBUTE9 ,

AID.PJC_WORK_TYPE_ID,

CASE WHEN (AID.po_distribution_id IS NOT NULL

AND nvl(pod.accrue_on_receipt_flag, 'N') = 'N') THEN

CASE WHEN (aid.line_type_lookup_code = 'NONREC_TAX'

and aid.prepay_distribution_id IS NOT NULL) THEN

AID.AMOUNT -

(NVL(AID.QUANTITY_VARIANCE, 0)

+ NVL(AID.AMOUNT_VARIANCE, 0)

+ NVL( AP_COMMITMENT_CONTROL_PKG.GET_PREPAY_TOTAL_TAX_VARIANCE(AID.INVOICE_DISTRIBUTION_ID),0))

WHEN (aid.line_type_lookup_code in ('ITEM', 'ACCRUAL', 'NONREC_TAX')) THEN

AID.AMOUNT -

(NVL(AID.QUANTITY_VARIANCE, 0)

+ NVL(AID.AMOUNT_VARIANCE, 0))

ELSE NULL END

ELSE NULL END LIQUIDATION_AMOUNT ,

CASE WHEN (AID.po_distribution_id IS NOT NULL

AND nvl(pod.accrue_on_receipt_flag, 'N') = 'N') THEN

CASE WHEN (aid.line_type_lookup_code in ('ITEM', 'ACCRUAL', 'NONREC_TAX')) THEN

AID.BASE_AMOUNT -

(NVL(AID.BASE_QUANTITY_VARIANCE, 0)

+ NVL(AID.BASE_AMOUNT_VARIANCE, 0))

ELSE NULL END

ELSE NULL END LIQUIDATION_BASE_AMOUNT,

AID.PARENT_REVERSAL_ID,

AID.LINE_TYPE_LOOKUP_CODE,

DECODE(AID.PO_DISTRIBUTION_ID,NULL,'UNMATCHED_INVOICE',

'MATCHED_INVOICE') TRANSACTION_SUBTYPE_CODE

FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,

PO_DISTRIBUTIONS_ALL POD

WHERE AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID (+)

UNION ALL

SELECT ASTD.INVOICE_ID SOURCE_HEADER_ID_1 ,

' ' SOURCE_HEADER_ID_2 ,

ASTD.INVOICE_LINE_NUMBER SOURCE_LINE_ID_1 ,

ASTD.INVOICE_DISTRIBUTION_ID SOURCE_LINE_ID_2 ,

' ' SOURCE_LINE_ID_3 ,

' ' SOURCE_LINE_ID_4 ,

' ' SOURCE_LINE_ID_5 ,

' ' SOURCE_LINE_ID_6 ,

FUNDS_STATUS ,

PJC_BILLABLE_FLAG ,

PJC_CAPITALIZABLE_FLAG ,

PJC_CONTEXT_CATEGORY ,

PJC_CONTRACT_ID ,

PJC_CONTRACT_LINE_ID ,

PJC_EXPENDITURE_ITEM_DATE ,

PJC_EXPENDITURE_TYPE_ID ,

PJC_FUNDING_ALLOCATION_ID ,

PJC_ORGANIZATION_ID ,

PJC_PROJECT_ID ,

PJC_RESERVED_ATTRIBUTE1 ,

PJC_RESERVED_ATTRIBUTE10 ,

PJC_RESERVED_ATTRIBUTE2 ,

PJC_RESERVED_ATTRIBUTE3 ,

PJC_RESERVED_ATTRIBUTE4 ,

PJC_RESERVED_ATTRIBUTE5 ,

PJC_RESERVED_ATTRIBUTE6 ,

PJC_RESERVED_ATTRIBUTE7 ,

PJC_RESERVED_ATTRIBUTE8 ,

PJC_RESERVED_ATTRIBUTE9 ,

PJC_TASK_ID ,

PJC_USER_DEF_ATTRIBUTE1 ,

PJC_USER_DEF_ATTRIBUTE10 ,

PJC_USER_DEF_ATTRIBUTE2 ,

PJC_USER_DEF_ATTRIBUTE3 ,

PJC_USER_DEF_ATTRIBUTE4 ,

PJC_USER_DEF_ATTRIBUTE5 ,

PJC_USER_DEF_ATTRIBUTE6 ,

PJC_USER_DEF_ATTRIBUTE7 ,

PJC_USER_DEF_ATTRIBUTE8 ,

PJC_USER_DEF_ATTRIBUTE9 ,

PJC_WORK_TYPE_ID,

NULL LIQUIDATION_AMOUNT,

NULL LIQUIDATION_BASE_AMOUNT,

ASTD.PARENT_REVERSAL_ID,

ASTD.LINE_TYPE_LOOKUP_CODE,

DECODE(ASTD.PO_DISTRIBUTION_ID,NULL,'UNMATCHED_INVOICE',

'MATCHED_INVOICE') TRANSACTION_SUBTYPE_CODE

FROM AP_SELF_ASSESSED_TAX_DIST_ALL ASTD