AP_TRANSFER_INV_PA_SELF_TAX_V

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 COST_RATE_FLAG EXPENDITURE_TYPE PO_HEADER_ID PO_LINE_ID

Query

SQL_Statement

SELECT APSELFTAX.INVOICE_ID, APSELFTAX.invoice_distribution_id , APSELFTAX.invoice_line_number , APSELFTAX.PARENT_REVERSAL_ID, APSELFTAX.cancellation_flag, APSELFTAX.amount_variance, APSELFTAX.base_amount_variance, DECODE(PO.ACCRUE_ON_RECEIPT_FLAG, 'Y',PO.CODE_COMBINATION_ID, APSELFTAX.DIST_CODE_COMBINATION_ID) DIST_CODE_COMBINATION_ID, APSELFTAX.ACCOUNTING_DATE, APSELFTAX.AMOUNT, APSELFTAX.DESCRIPTION, APSELFTAX.QUANTITY_INVOICED, APSELFTAX.UNIT_PRICE, APSELFTAX.ATTRIBUTE_CATEGORY, APSELFTAX.ATTRIBUTE1, APSELFTAX.ATTRIBUTE2, APSELFTAX.ATTRIBUTE3, APSELFTAX.ATTRIBUTE4, APSELFTAX.ATTRIBUTE5, APSELFTAX.ATTRIBUTE6, APSELFTAX.ATTRIBUTE7, APSELFTAX.ATTRIBUTE8, APSELFTAX.ATTRIBUTE9, APSELFTAX.ATTRIBUTE10, APSELFTAX.ATTRIBUTE11, APSELFTAX.ATTRIBUTE12, APSELFTAX.ATTRIBUTE13, APSELFTAX.ATTRIBUTE14, APSELFTAX.ATTRIBUTE15, APSELFTAX.DISTRIBUTION_LINE_NUMBER, APSELFTAX.LINE_TYPE_LOOKUP_CODE, NVL(PO.PO_DISTRIBUTION_ID,APSELFTAX.PO_DISTRIBUTION_ID) PO_DISTRIBUTION_ID, DECODE(APSELFTAX.line_type_lookup_code,'PREPAY', (SELECT AP_UTILITIES_PKG.AP_ROUND_CURRENCY (APSELFTAX.AMOUNT*INV.EXCHANGE_RATE,ASP.base_currency_code) FROM ap_invoices_all INV, AP_SELF_ASSESSED_TAX_DIST_ALL DIST2, ap_system_parameters_all ASP WHERE DIST2.invoice_distribution_id = APSELFTAX.prepay_distribution_id AND DIST2.invoice_id = INV.invoice_id AND ASP.org_id = INV.org_id) ,APSELFTAX.BASE_AMOUNT) BASE_AMOUNT, APSELFTAX.PA_ADDITION_FLAG, APSELFTAX.REQUEST_ID, APSELFTAX.REVERSAL_FLAG, NVL(APSELFTAX.PJC_CONTEXT_CATEGORY,PO.PJC_CONTEXT_CATEGORY) PJC_CONTEXT_CATEGORY, /*BUG 29706924: start: Added NVL logic*/ NVL(APSELFTAX.PJC_PROJECT_ID,PO.PJC_PROJECT_ID) PJC_PROJECT_ID, NVL(APSELFTAX.PJC_TASK_ID,PO.PJC_TASK_ID) PJC_TASK_ID, NVL(APSELFTAX.PJC_EXPENDITURE_TYPE_ID,PO.PJC_EXPENDITURE_TYPE_ID) PJC_EXPENDITURE_TYPE_ID, NVL(APSELFTAX.PJC_EXPENDITURE_ITEM_DATE,PO.PJC_EXPENDITURE_ITEM_DATE) PJC_EXPENDITURE_ITEM_DATE, NVL(APSELFTAX.PJC_ORGANIZATION_ID,PO.PJC_ORGANIZATION_ID) PJC_ORGANIZATION_ID, NVL(APSELFTAX.PJC_BILLABLE_FLAG,PO.PJC_BILLABLE_FLAG) PJC_BILLABLE_FLAG, NVL(APSELFTAX.PJC_CAPITALIZABLE_FLAG,PO.PJC_CAPITALIZABLE_FLAG) PJC_CAPITALIZABLE_FLAG, NVL(APSELFTAX.PJC_WORK_TYPE_ID,PO.PJC_WORK_TYPE_ID) PJC_WORK_TYPE_ID, NVL(APSELFTAX.PJC_CONTRACT_ID,PO.PJC_CONTRACT_ID) PJC_CONTRACT_ID, NVL(APSELFTAX.PJC_CONTRACT_LINE_ID,PO.PJC_CONTRACT_LINE_ID) PJC_CONTRACT_LINE_ID, NVL(APSELFTAX.PJC_FUNDING_ALLOCATION_ID,PO.PJC_FUNDING_ALLOCATION_ID) PJC_FUNDING_ALLOCATION_ID, NVL(APSELFTAX.PJC_RESERVED_ATTRIBUTE1,PO.PJC_RESERVED_ATTRIBUTE1) PJC_RESERVED_ATTRIBUTE1, NVL(APSELFTAX.PJC_RESERVED_ATTRIBUTE2,PO.PJC_RESERVED_ATTRIBUTE2) PJC_RESERVED_ATTRIBUTE2, NVL(APSELFTAX.PJC_RESERVED_ATTRIBUTE3,PO.PJC_RESERVED_ATTRIBUTE3) PJC_RESERVED_ATTRIBUTE3, NVL(APSELFTAX.PJC_RESERVED_ATTRIBUTE4,PO.PJC_RESERVED_ATTRIBUTE4) PJC_RESERVED_ATTRIBUTE4, NVL(APSELFTAX.PJC_RESERVED_ATTRIBUTE5,PO.PJC_RESERVED_ATTRIBUTE5) PJC_RESERVED_ATTRIBUTE5, NVL(APSELFTAX.PJC_RESERVED_ATTRIBUTE6,PO.PJC_RESERVED_ATTRIBUTE6) PJC_RESERVED_ATTRIBUTE6, NVL(APSELFTAX.PJC_RESERVED_ATTRIBUTE7,PO.PJC_RESERVED_ATTRIBUTE7) PJC_RESERVED_ATTRIBUTE7, NVL(APSELFTAX.PJC_RESERVED_ATTRIBUTE8,PO.PJC_RESERVED_ATTRIBUTE8) PJC_RESERVED_ATTRIBUTE8, NVL(APSELFTAX.PJC_RESERVED_ATTRIBUTE9,PO.PJC_RESERVED_ATTRIBUTE9) PJC_RESERVED_ATTRIBUTE9, NVL(APSELFTAX.PJC_RESERVED_ATTRIBUTE10,PO.PJC_RESERVED_ATTRIBUTE10) PJC_RESERVED_ATTRIBUTE10, NVL(APSELFTAX.PJC_USER_DEF_ATTRIBUTE1,PO.PJC_USER_DEF_ATTRIBUTE1) PJC_USER_DEF_ATTRIBUTE1, NVL(APSELFTAX.PJC_USER_DEF_ATTRIBUTE2,PO.PJC_USER_DEF_ATTRIBUTE2) PJC_USER_DEF_ATTRIBUTE2, NVL(APSELFTAX.PJC_USER_DEF_ATTRIBUTE3,PO.PJC_USER_DEF_ATTRIBUTE3) PJC_USER_DEF_ATTRIBUTE3, NVL(APSELFTAX.PJC_USER_DEF_ATTRIBUTE4,PO.PJC_USER_DEF_ATTRIBUTE4) PJC_USER_DEF_ATTRIBUTE4, NVL(APSELFTAX.PJC_USER_DEF_ATTRIBUTE5,PO.PJC_USER_DEF_ATTRIBUTE5) PJC_USER_DEF_ATTRIBUTE5, NVL(APSELFTAX.PJC_USER_DEF_ATTRIBUTE6,PO.PJC_USER_DEF_ATTRIBUTE6) PJC_USER_DEF_ATTRIBUTE6, NVL(APSELFTAX.PJC_USER_DEF_ATTRIBUTE7,PO.PJC_USER_DEF_ATTRIBUTE7) PJC_USER_DEF_ATTRIBUTE7, NVL(APSELFTAX.PJC_USER_DEF_ATTRIBUTE8,PO.PJC_USER_DEF_ATTRIBUTE8) PJC_USER_DEF_ATTRIBUTE8, NVL(APSELFTAX.PJC_USER_DEF_ATTRIBUTE9,PO.PJC_USER_DEF_ATTRIBUTE9) PJC_USER_DEF_ATTRIBUTE9, NVL(APSELFTAX.PJC_USER_DEF_ATTRIBUTE10,PO.PJC_USER_DEF_ATTRIBUTE10) PJC_USER_DEF_ATTRIBUTE10, /*BUG 29706924: end: Added NVL logic*/ APSELFTAX.receipt_currency_amount, APSELFTAX.receipt_currency_code, APSELFTAX.receipt_conversion_rate, PO.ACCRUE_ON_RECEIPT_FLAG, APSELFTAX.prepay_distribution_id, 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_SELF_ASSESSED_TAX_DIST_ALL APSELFTAX, po_distributions PO , po_lines_all POL, PJF_EXP_TYPES_VL TYPE, PJF_EXP_TYPES_VL TYPE2 /*BUG 29706924: Additional table for PO*/ WHERE (APSELFTAX.PJC_Project_Id > 0 OR PO.PJC_PROJECT_ID > 0) AND APSELFTAX.po_distribution_id = PO.po_distribution_id (+) AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT' AND po.po_line_id = pol.po_line_id(+) AND APSELFTAX.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_SELF_ASSESSED_TAX_DIST_ALL apselftax, po_distributions pod, egp_system_items si, po_lines_all pol, po_system_parameters psp WHERE apselftax.invoice_distribution_id = APSELFTAX.charge_applicable_to_dist_id AND apselftax.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)