AP_TRANSFER_PA_INVOICES

Details

Schema: FUSION
Object owner: AP
Object type: VIEW

Columns

Name
INVOICE_ID INVOICE_DISTRIBUTION_ID INVOICE_LINE_NUMBER PARENT_REVERSAL_ID CANCELLATION_FLAG AMOUNT_VARIANCE BASE_AMOUNT_VARIANCE DIST_CODE_COMBINATION_ID ACCOUNTING_DATE AMOUNT DESCRIPTION QUANTITY_INVOICED UNIT_PRICE ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 DISTRIBUTION_LINE_NUMBER LINE_TYPE_LOOKUP_CODE PO_DISTRIBUTION_ID BASE_AMOUNT PA_ADDITION_FLAG REQUEST_ID REVERSAL_FLAG PJC_CONTEXT_CATEGORY PJC_PROJECT_ID PJC_TASK_ID PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_ITEM_DATE PJC_ORGANIZATION_ID PJC_BILLABLE_FLAG PJC_CAPITALIZABLE_FLAG PJC_WORK_TYPE_ID PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_FUNDING_ALLOCATION_ID PJC_RESERVED_ATTRIBUTE1 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_RESERVED_ATTRIBUTE10 PJC_USER_DEF_ATTRIBUTE1 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_USER_DEF_ATTRIBUTE10 RECEIPT_CURRENCY_AMOUNT RECEIPT_CURRENCY_CODE RECEIPT_CONVERSION_RATE ACCRUE_ON_RECEIPT_FLAG PREPAY_DISTRIBUTION_ID HISTORICAL_FLAG COST_RATE_FLAG EXPENDITURE_TYPE PO_HEADER_ID PO_LINE_ID

Name

INVOICE_ID

INVOICE_DISTRIBUTION_ID

INVOICE_LINE_NUMBER

PARENT_REVERSAL_ID

CANCELLATION_FLAG

AMOUNT_VARIANCE

BASE_AMOUNT_VARIANCE

DIST_CODE_COMBINATION_ID

ACCOUNTING_DATE

AMOUNT

DESCRIPTION

QUANTITY_INVOICED

UNIT_PRICE

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

DISTRIBUTION_LINE_NUMBER

LINE_TYPE_LOOKUP_CODE

PO_DISTRIBUTION_ID

BASE_AMOUNT

PA_ADDITION_FLAG

REQUEST_ID

REVERSAL_FLAG

PJC_CONTEXT_CATEGORY

PJC_PROJECT_ID

PJC_TASK_ID

PJC_EXPENDITURE_TYPE_ID

PJC_EXPENDITURE_ITEM_DATE

PJC_ORGANIZATION_ID

PJC_BILLABLE_FLAG

PJC_CAPITALIZABLE_FLAG

PJC_WORK_TYPE_ID

PJC_CONTRACT_ID

PJC_CONTRACT_LINE_ID

PJC_FUNDING_ALLOCATION_ID

PJC_RESERVED_ATTRIBUTE1

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_RESERVED_ATTRIBUTE10

PJC_USER_DEF_ATTRIBUTE1

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_USER_DEF_ATTRIBUTE10

RECEIPT_CURRENCY_AMOUNT

RECEIPT_CURRENCY_CODE

RECEIPT_CONVERSION_RATE

ACCRUE_ON_RECEIPT_FLAG

PREPAY_DISTRIBUTION_ID

HISTORICAL_FLAG

COST_RATE_FLAG

EXPENDITURE_TYPE

PO_HEADER_ID

PO_LINE_ID

Query

SQL_Statement
SELECT DIST.INVOICE_ID, DIST.invoice_distribution_id , DIST.invoice_line_number , DIST.PARENT_REVERSAL_ID, DIST.cancellation_flag, DIST.amount_variance, DIST.base_amount_variance, DECODE(PO.ACCRUE_ON_RECEIPT_FLAG, 'Y',PO.CODE_COMBINATION_ID, DIST.DIST_CODE_COMBINATION_ID) DIST_CODE_COMBINATION_ID, DIST.ACCOUNTING_DATE, DIST.AMOUNT, DIST.DESCRIPTION, DIST.QUANTITY_INVOICED, DIST.UNIT_PRICE, DIST.ATTRIBUTE_CATEGORY, DIST.ATTRIBUTE1, DIST.ATTRIBUTE2, DIST.ATTRIBUTE3, DIST.ATTRIBUTE4, DIST.ATTRIBUTE5, DIST.ATTRIBUTE6, DIST.ATTRIBUTE7, DIST.ATTRIBUTE8, DIST.ATTRIBUTE9, DIST.ATTRIBUTE10, DIST.ATTRIBUTE11, DIST.ATTRIBUTE12, DIST.ATTRIBUTE13, DIST.ATTRIBUTE14, DIST.ATTRIBUTE15, DIST.DISTRIBUTION_LINE_NUMBER, DIST.LINE_TYPE_LOOKUP_CODE, NVL(PO.PO_DISTRIBUTION_ID,DIST.PO_DISTRIBUTION_ID) PO_DISTRIBUTION_ID, DECODE(dist.line_type_lookup_code,'PREPAY', (SELECT AP_UTILITIES_PKG.AP_ROUND_CURRENCY (DIST.AMOUNTINV.EXCHANGE_RATE,ASP.base_currency_code) FROM ap_invoices_all INV, ap_invoice_distributions DIST2, ap_system_parameters_all ASP WHERE DIST2.invoice_distribution_id = DIST.prepay_distribution_id AND DIST2.invoice_id = INV.invoice_id AND ASP.org_id = inv.org_id) ,DIST.BASE_AMOUNT) BASE_AMOUNT, DIST.PA_ADDITION_FLAG, DIST.REQUEST_ID, DIST.REVERSAL_FLAG, NVL(DIST.PJC_CONTEXT_CATEGORY,PO.PJC_CONTEXT_CATEGORY) PJC_CONTEXT_CATEGORY, /BUG 29706924: start: Added NVL logic/ NVL(DIST.PJC_PROJECT_ID,PO.PJC_PROJECT_ID) PJC_PROJECT_ID, NVL(DIST.PJC_TASK_ID,PO.PJC_TASK_ID) PJC_TASK_ID, NVL(DIST.PJC_EXPENDITURE_TYPE_ID,PO.PJC_EXPENDITURE_TYPE_ID) PJC_EXPENDITURE_TYPE_ID, NVL(DIST.PJC_EXPENDITURE_ITEM_DATE,PO.PJC_EXPENDITURE_ITEM_DATE) PJC_EXPENDITURE_ITEM_DATE, NVL(DIST.PJC_ORGANIZATION_ID,PO.PJC_ORGANIZATION_ID) PJC_ORGANIZATION_ID, NVL(DIST.PJC_BILLABLE_FLAG,PO.PJC_BILLABLE_FLAG) PJC_BILLABLE_FLAG, NVL(DIST.PJC_CAPITALIZABLE_FLAG,PO.PJC_CAPITALIZABLE_FLAG) PJC_CAPITALIZABLE_FLAG, NVL(DIST.PJC_WORK_TYPE_ID,PO.PJC_WORK_TYPE_ID) PJC_WORK_TYPE_ID, NVL(DIST.PJC_CONTRACT_ID,PO.PJC_CONTRACT_ID) PJC_CONTRACT_ID, NVL(DIST.PJC_CONTRACT_LINE_ID,PO.PJC_CONTRACT_LINE_ID) PJC_CONTRACT_LINE_ID, NVL(DIST.PJC_FUNDING_ALLOCATION_ID,PO.PJC_FUNDING_ALLOCATION_ID) PJC_FUNDING_ALLOCATION_ID, NVL(DIST.PJC_RESERVED_ATTRIBUTE1,PO.PJC_RESERVED_ATTRIBUTE1) PJC_RESERVED_ATTRIBUTE1, NVL(DIST.PJC_RESERVED_ATTRIBUTE2,PO.PJC_RESERVED_ATTRIBUTE2) PJC_RESERVED_ATTRIBUTE2, NVL(DIST.PJC_RESERVED_ATTRIBUTE3,PO.PJC_RESERVED_ATTRIBUTE3) PJC_RESERVED_ATTRIBUTE3, NVL(DIST.PJC_RESERVED_ATTRIBUTE4,PO.PJC_RESERVED_ATTRIBUTE4) PJC_RESERVED_ATTRIBUTE4, NVL(DIST.PJC_RESERVED_ATTRIBUTE5,PO.PJC_RESERVED_ATTRIBUTE5) PJC_RESERVED_ATTRIBUTE5, NVL(DIST.PJC_RESERVED_ATTRIBUTE6,PO.PJC_RESERVED_ATTRIBUTE6) PJC_RESERVED_ATTRIBUTE6, NVL(DIST.PJC_RESERVED_ATTRIBUTE7,PO.PJC_RESERVED_ATTRIBUTE7) PJC_RESERVED_ATTRIBUTE7, NVL(DIST.PJC_RESERVED_ATTRIBUTE8,PO.PJC_RESERVED_ATTRIBUTE8) PJC_RESERVED_ATTRIBUTE8, NVL(DIST.PJC_RESERVED_ATTRIBUTE9,PO.PJC_RESERVED_ATTRIBUTE9) PJC_RESERVED_ATTRIBUTE9, NVL(DIST.PJC_RESERVED_ATTRIBUTE10,PO.PJC_RESERVED_ATTRIBUTE10) PJC_RESERVED_ATTRIBUTE10, NVL(DIST.PJC_USER_DEF_ATTRIBUTE1,PO.PJC_USER_DEF_ATTRIBUTE1) PJC_USER_DEF_ATTRIBUTE1, NVL(DIST.PJC_USER_DEF_ATTRIBUTE2,PO.PJC_USER_DEF_ATTRIBUTE2) PJC_USER_DEF_ATTRIBUTE2, NVL(DIST.PJC_USER_DEF_ATTRIBUTE3,PO.PJC_USER_DEF_ATTRIBUTE3) PJC_USER_DEF_ATTRIBUTE3, NVL(DIST.PJC_USER_DEF_ATTRIBUTE4,PO.PJC_USER_DEF_ATTRIBUTE4) PJC_USER_DEF_ATTRIBUTE4, NVL(DIST.PJC_USER_DEF_ATTRIBUTE5,PO.PJC_USER_DEF_ATTRIBUTE5) PJC_USER_DEF_ATTRIBUTE5, NVL(DIST.PJC_USER_DEF_ATTRIBUTE6,PO.PJC_USER_DEF_ATTRIBUTE6) PJC_USER_DEF_ATTRIBUTE6, NVL(DIST.PJC_USER_DEF_ATTRIBUTE7,PO.PJC_USER_DEF_ATTRIBUTE7) PJC_USER_DEF_ATTRIBUTE7, NVL(DIST.PJC_USER_DEF_ATTRIBUTE8,PO.PJC_USER_DEF_ATTRIBUTE8) PJC_USER_DEF_ATTRIBUTE8, NVL(DIST.PJC_USER_DEF_ATTRIBUTE9,PO.PJC_USER_DEF_ATTRIBUTE9) PJC_USER_DEF_ATTRIBUTE9, NVL(DIST.PJC_USER_DEF_ATTRIBUTE10,PO.PJC_USER_DEF_ATTRIBUTE10) PJC_USER_DEF_ATTRIBUTE10,/BUG 29706924: end: Added NVL logic/ DIST.receipt_currency_amount, DIST.receipt_currency_code, DIST.receipt_conversion_rate, PO.ACCRUE_ON_RECEIPT_FLAG, DIST.prepay_distribution_id, DIST.historical_flag, NVL(TYPE.cost_rate_flag,TYPE2.cost_rate_flag) cost_rate_flag, /BUG 29706924: Added NVL logic/ NVL(TYPE.expenditure_type_name,TYPE2.expenditure_type_name) expenditure_type, /BUG 29706924: Added NVL logic/ PO.po_header_id, /bug 30851756/ PO.po_line_id /bug 30851756/ FROM ap_invoice_distributions DIST, po_distributions PO , po_lines_all POL, PJF_EXP_TYPES_VL TYPE, PJF_EXP_TYPES_VL TYPE2 /BUG 29706924: Additional table for PO/ WHERE (DIST.PJC_Project_Id > 0 OR PO.PJC_PROJECT_ID > 0) AND DIST.po_distribution_id = PO.po_distribution_id (+) AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT' AND po.po_line_id = pol.po_line_id(+) AND DIST.pjc_expenditure_type_id = TYPE.expenditure_type_id(+) AND PO.pjc_expenditure_type_id = TYPE2.expenditure_type_id(+) /BUG 29706924: Additional table for PO*/ AND NOT EXISTS (SELECT 'X' FROM ap_invoice_distributions apdist, po_distributions pod, egp_system_items si, po_lines_all pol, po_system_parameters psp WHERE apdist.invoice_distribution_id = dist.charge_applicable_to_dist_id AND apdist.po_distribution_id = pod.po_distribution_id AND pod.po_line_id = pol.po_line_id AND si.inventory_item_id = pol.item_id AND si.comms_nl_trackable_flag = 'Y' AND psp.prc_bu_id = pol.prc_bu_id AND si.organization_id = psp.inventory_organization_id)

SQL_Statement

SELECT

DIST.INVOICE_ID,

DIST.invoice_distribution_id ,

DIST.invoice_line_number ,

DIST.PARENT_REVERSAL_ID,

DIST.cancellation_flag, DIST.amount_variance,

DIST.base_amount_variance,

DECODE(PO.ACCRUE_ON_RECEIPT_FLAG, 'Y',PO.CODE_COMBINATION_ID,

DIST.DIST_CODE_COMBINATION_ID)

DIST_CODE_COMBINATION_ID,

DIST.ACCOUNTING_DATE,

DIST.AMOUNT,

DIST.DESCRIPTION,

DIST.QUANTITY_INVOICED,

DIST.UNIT_PRICE,

DIST.ATTRIBUTE_CATEGORY,

DIST.ATTRIBUTE1,

DIST.ATTRIBUTE2,

DIST.ATTRIBUTE3,

DIST.ATTRIBUTE4,

DIST.ATTRIBUTE5,

DIST.ATTRIBUTE6,

DIST.ATTRIBUTE7,

DIST.ATTRIBUTE8,

DIST.ATTRIBUTE9,

DIST.ATTRIBUTE10,

DIST.ATTRIBUTE11,

DIST.ATTRIBUTE12,

DIST.ATTRIBUTE13,

DIST.ATTRIBUTE14,

DIST.ATTRIBUTE15,

DIST.DISTRIBUTION_LINE_NUMBER,

DIST.LINE_TYPE_LOOKUP_CODE,

NVL(PO.PO_DISTRIBUTION_ID,DIST.PO_DISTRIBUTION_ID) PO_DISTRIBUTION_ID,

DECODE(dist.line_type_lookup_code,'PREPAY',

(SELECT AP_UTILITIES_PKG.AP_ROUND_CURRENCY

(DIST.AMOUNT*INV.EXCHANGE_RATE,ASP.base_currency_code)

FROM ap_invoices_all INV,

ap_invoice_distributions DIST2,

ap_system_parameters_all ASP

WHERE DIST2.invoice_distribution_id =

DIST.prepay_distribution_id

AND DIST2.invoice_id = INV.invoice_id

AND ASP.org_id = inv.org_id)

,DIST.BASE_AMOUNT) BASE_AMOUNT,

DIST.PA_ADDITION_FLAG,

DIST.REQUEST_ID,

DIST.REVERSAL_FLAG,

NVL(DIST.PJC_CONTEXT_CATEGORY,PO.PJC_CONTEXT_CATEGORY) PJC_CONTEXT_CATEGORY, /*BUG 29706924: start: Added NVL logic*/

NVL(DIST.PJC_PROJECT_ID,PO.PJC_PROJECT_ID) PJC_PROJECT_ID,

NVL(DIST.PJC_TASK_ID,PO.PJC_TASK_ID) PJC_TASK_ID,

NVL(DIST.PJC_EXPENDITURE_TYPE_ID,PO.PJC_EXPENDITURE_TYPE_ID) PJC_EXPENDITURE_TYPE_ID,

NVL(DIST.PJC_EXPENDITURE_ITEM_DATE,PO.PJC_EXPENDITURE_ITEM_DATE) PJC_EXPENDITURE_ITEM_DATE,

NVL(DIST.PJC_ORGANIZATION_ID,PO.PJC_ORGANIZATION_ID) PJC_ORGANIZATION_ID,

NVL(DIST.PJC_BILLABLE_FLAG,PO.PJC_BILLABLE_FLAG) PJC_BILLABLE_FLAG,

NVL(DIST.PJC_CAPITALIZABLE_FLAG,PO.PJC_CAPITALIZABLE_FLAG) PJC_CAPITALIZABLE_FLAG,

NVL(DIST.PJC_WORK_TYPE_ID,PO.PJC_WORK_TYPE_ID) PJC_WORK_TYPE_ID,

NVL(DIST.PJC_CONTRACT_ID,PO.PJC_CONTRACT_ID) PJC_CONTRACT_ID,

NVL(DIST.PJC_CONTRACT_LINE_ID,PO.PJC_CONTRACT_LINE_ID) PJC_CONTRACT_LINE_ID,

NVL(DIST.PJC_FUNDING_ALLOCATION_ID,PO.PJC_FUNDING_ALLOCATION_ID) PJC_FUNDING_ALLOCATION_ID,

NVL(DIST.PJC_RESERVED_ATTRIBUTE1,PO.PJC_RESERVED_ATTRIBUTE1) PJC_RESERVED_ATTRIBUTE1,

NVL(DIST.PJC_RESERVED_ATTRIBUTE2,PO.PJC_RESERVED_ATTRIBUTE2) PJC_RESERVED_ATTRIBUTE2,

NVL(DIST.PJC_RESERVED_ATTRIBUTE3,PO.PJC_RESERVED_ATTRIBUTE3) PJC_RESERVED_ATTRIBUTE3,

NVL(DIST.PJC_RESERVED_ATTRIBUTE4,PO.PJC_RESERVED_ATTRIBUTE4) PJC_RESERVED_ATTRIBUTE4,

NVL(DIST.PJC_RESERVED_ATTRIBUTE5,PO.PJC_RESERVED_ATTRIBUTE5) PJC_RESERVED_ATTRIBUTE5,

NVL(DIST.PJC_RESERVED_ATTRIBUTE6,PO.PJC_RESERVED_ATTRIBUTE6) PJC_RESERVED_ATTRIBUTE6,

NVL(DIST.PJC_RESERVED_ATTRIBUTE7,PO.PJC_RESERVED_ATTRIBUTE7) PJC_RESERVED_ATTRIBUTE7,

NVL(DIST.PJC_RESERVED_ATTRIBUTE8,PO.PJC_RESERVED_ATTRIBUTE8) PJC_RESERVED_ATTRIBUTE8,

NVL(DIST.PJC_RESERVED_ATTRIBUTE9,PO.PJC_RESERVED_ATTRIBUTE9) PJC_RESERVED_ATTRIBUTE9,

NVL(DIST.PJC_RESERVED_ATTRIBUTE10,PO.PJC_RESERVED_ATTRIBUTE10) PJC_RESERVED_ATTRIBUTE10,

NVL(DIST.PJC_USER_DEF_ATTRIBUTE1,PO.PJC_USER_DEF_ATTRIBUTE1) PJC_USER_DEF_ATTRIBUTE1,

NVL(DIST.PJC_USER_DEF_ATTRIBUTE2,PO.PJC_USER_DEF_ATTRIBUTE2) PJC_USER_DEF_ATTRIBUTE2,

NVL(DIST.PJC_USER_DEF_ATTRIBUTE3,PO.PJC_USER_DEF_ATTRIBUTE3) PJC_USER_DEF_ATTRIBUTE3,

NVL(DIST.PJC_USER_DEF_ATTRIBUTE4,PO.PJC_USER_DEF_ATTRIBUTE4) PJC_USER_DEF_ATTRIBUTE4,

NVL(DIST.PJC_USER_DEF_ATTRIBUTE5,PO.PJC_USER_DEF_ATTRIBUTE5) PJC_USER_DEF_ATTRIBUTE5,

NVL(DIST.PJC_USER_DEF_ATTRIBUTE6,PO.PJC_USER_DEF_ATTRIBUTE6) PJC_USER_DEF_ATTRIBUTE6,

NVL(DIST.PJC_USER_DEF_ATTRIBUTE7,PO.PJC_USER_DEF_ATTRIBUTE7) PJC_USER_DEF_ATTRIBUTE7,

NVL(DIST.PJC_USER_DEF_ATTRIBUTE8,PO.PJC_USER_DEF_ATTRIBUTE8) PJC_USER_DEF_ATTRIBUTE8,

NVL(DIST.PJC_USER_DEF_ATTRIBUTE9,PO.PJC_USER_DEF_ATTRIBUTE9) PJC_USER_DEF_ATTRIBUTE9,

NVL(DIST.PJC_USER_DEF_ATTRIBUTE10,PO.PJC_USER_DEF_ATTRIBUTE10) PJC_USER_DEF_ATTRIBUTE10,/*BUG 29706924: end: Added NVL logic*/

DIST.receipt_currency_amount,

DIST.receipt_currency_code,

DIST.receipt_conversion_rate,

PO.ACCRUE_ON_RECEIPT_FLAG,

DIST.prepay_distribution_id,

DIST.historical_flag,

NVL(TYPE.cost_rate_flag,TYPE2.cost_rate_flag) cost_rate_flag, /*BUG 29706924: Added NVL logic*/

NVL(TYPE.expenditure_type_name,TYPE2.expenditure_type_name) expenditure_type, /*BUG 29706924: Added NVL logic*/

PO.po_header_id, /*bug 30851756*/

PO.po_line_id /*bug 30851756*/

FROM ap_invoice_distributions DIST,

po_distributions PO ,

po_lines_all POL,

PJF_EXP_TYPES_VL TYPE,

PJF_EXP_TYPES_VL TYPE2 /*BUG 29706924: Additional table for PO*/

WHERE (DIST.PJC_Project_Id > 0 OR PO.PJC_PROJECT_ID > 0)

AND DIST.po_distribution_id = PO.po_distribution_id (+)

AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'

AND po.po_line_id = pol.po_line_id(+)

AND DIST.pjc_expenditure_type_id = TYPE.expenditure_type_id(+)

AND PO.pjc_expenditure_type_id = TYPE2.expenditure_type_id(+) /*BUG 29706924: Additional table for PO*/

AND NOT EXISTS

(SELECT 'X'

FROM ap_invoice_distributions apdist,

po_distributions pod,

egp_system_items si,

po_lines_all pol,

po_system_parameters psp

WHERE apdist.invoice_distribution_id = dist.charge_applicable_to_dist_id

AND apdist.po_distribution_id = pod.po_distribution_id

AND pod.po_line_id = pol.po_line_id

AND si.inventory_item_id = pol.item_id

AND si.comms_nl_trackable_flag = 'Y'

AND psp.prc_bu_id = pol.prc_bu_id

AND si.organization_id = psp.inventory_organization_id)