PJC_PRJ_AP_INV_DIST_V

Details

• Schema: FUSION

• Object owner: PJC

• Object type: VIEW

Columns

Name

INVOICE_NUMBER VENDOR_NAME INVOICE_DATE DISTRIBUTION_LINE_NUMBER DESCRIPTION GL_DATE POSTED_FLAG APPROVED_FLAG QUANTITY DENOM_CURRENCY_CODE DENOM_AMOUNT ACCT_CURRENCY_CODE AMOUNT ACCT_RATE_DATE ACCT_RATE_TYPE ACCT_EXCHANGE_RATE RECEIPT_CURRENCY_CODE RECEIPT_CURRENCY_AMOUNT RECEIPT_EXCHANGE_RATE PROJECT_NUMBER PROJECT_NAME TASK_NUMBER TASK_NAME EXPENDITURE_TYPE EXPENDITURE_CATEGORY REVENUE_CATEGORY EXPENDITURE_ITEM_DATE EXPENDITURE_ORGANIZATION INVOICE_ID VENDOR_ID PROJECT_ID PROJECT_TYPE_ID PROJECT_CURRENCY_CODE PROJ_CURR_CONV_DATE_TYPE_CODE PROJ_CURRENCY_CONV_DATE PROJ_CURR_RATE_TYPE TASK_ID EXPENDITURE_ORGANIZATION_ID RESOURCE_CLASS INVOICE_LINE_NUMBER INVOICE_DISTRIBUTION_ID CASH_BASIS_ACCT_IMPL AWARD_SET_ID ORG_ID EXPENDITURE_TYPE_ID EXP_TYPE_COST_RATE_FLAG EXPENDITURE_CATEGORY_ID LINE_TYPE_LOOKUP_CODE BILLABLE_FLAG CAPITALIZABLE_FLAG WORK_TYPE_ID CONTRACT_ID FUNDING_SOURCE_ID FUNDING_ALLOCATION_ID CONTRACT_LINE_ID INVENTORY_ITEM_ID

Query

SQL_Statement

SELECT vw.INVOICE_NUMBER ,vw.VENDOR_NAME ,vw.INVOICE_DATE ,vw.DISTRIBUTION_LINE_NUMBER ,vw.DESCRIPTION ,vw.GL_DATE ,vw.POSTED_FLAG ,DECODE(vw.APPROVAL_STATUS,'APPROVED','Y','AVAILABLE','Y','UNPAID','Y','N') APPROVED_FLAG ,vw.QUANTITY ,vw.DENOM_CURRENCY_CODE ,vw.DENOM_AMOUNT ,vw.ACCT_CURRENCY_CODE ,vw.AMOUNT ,vw.ACCT_RATE_DATE ,vw.ACCT_RATE_TYPE ,vw.ACCT_EXCHANGE_RATE ,vw.RECEIPT_CURRENCY_CODE ,vw.RECEIPT_CURRENCY_AMOUNT ,vw.RECEIPT_EXCHANGE_RATE ,vw.PROJECT_NUMBER ,vw.PROJECT_NAME ,vw.TASK_NUMBER ,vw.TASK_NAME ,vw.EXPENDITURE_TYPE ,vw.EXPENDITURE_CATEGORY ,vw.REVENUE_CATEGORY ,vw.EXPENDITURE_ITEM_DATE ,vw.EXPENDITURE_ORGANIZATION ,vw.INVOICE_ID ,vw.VENDOR_ID ,vw.PROJECT_ID ,vw.PROJECT_TYPE_ID ,vw.PROJECT_CURRENCY_CODE ,vw.PROJ_CURR_CONV_DATE_TYPE_CODE ,vw.PROJ_CURRENCY_CONV_DATE ,vw.PROJ_CURR_RATE_TYPE ,vw.TASK_ID ,vw.EXPENDITURE_ORGANIZATION_ID ,vw.RESOURCE_CLASS ,vw.INVOICE_LINE_NUMBER ,vw.INVOICE_DISTRIBUTION_ID ,vw.CASH_BASIS_ACCT_IMPL ,vw.AWARD_SET_ID ,vw.ORG_ID ,vw.EXPENDITURE_TYPE_ID ,vw.EXP_TYPE_COST_RATE_FLAG ,vw.expenditure_category_id ,vw.LINE_TYPE_LOOKUP_CODE ,vw.BILLABLE_FLAG ,vw.CAPITALIZABLE_FLAG ,vw.WORK_TYPE_ID ,vw.CONTRACT_ID/*Added for grants uptake*/ ,vw.FUNDING_SOURCE_ID/*Added for grants uptake*/ ,vw.FUNDING_ALLOCATION_ID/*Added for grants uptake*/ ,vw.CONTRACT_LINE_ID/*Added for grants uptake*/ ,vw.inventory_item_id FROM (SELECT i.invoice_num INVOICE_NUMBER ,v.vendor_name VENDOR_NAME ,i.invoice_date INVOICE_DATE ,d.distribution_line_number DISTRIBUTION_LINE_NUMBER ,d.description DESCRIPTION ,d.accounting_date GL_DATE ,decode(d.posted_flag,'Y','Y','N') POSTED_FLAG ,AP_INVOICES_PKG.GET_APPROVAL_STATUS(I.INVOICE_ID, I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG, I.INVOICE_TYPE_LOOKUP_CODE) APPROVAL_STATUS , DECODE(po1.accrue_on_receipt_flag,'Y',PJC_CMT_UTILS.get_inv_cmt(po1.po_distribution_id, 'Y', d.pa_addition_flag, nvl(d.amount_variance, 0), po1.code_combination_id, 'AP', i.invoice_id, d.distribution_line_number, d.invoice_distribution_id, AP_INVOICES_PKG.GET_APPROVAL_STATUS(I.INVOICE_ID, I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG, I.INVOICE_TYPE_LOOKUP_CODE)),d.pa_quantity) QUANTITY , i.invoice_currency_code DENOM_CURRENCY_CODE , DECODE(i.invoice_type_lookup_code,'PREPAYMENT',NVL(d.prepay_amount_remaining,0),d.Amount) DENOM_AMOUNT , g.currency_code ACCT_CURRENCY_CODE ,DECODE(i.invoice_type_lookup_code,'PREPAYMENT' ,pjf_currency_grp.round_currency_amount( (NVL(d.prepay_amount_remaining,0) * decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),1,i.exchange_rate)),g.currency_code) ,d.base_amount) amount ,decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),TO_DATE(NULL),i.exchange_date) ACCT_RATE_DATE ,decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),NULL,i.exchange_rate_type) ACCT_RATE_TYPE ,decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),TO_NUMBER(NULL),i.exchange_rate) ACCT_EXCHANGE_RATE ,d.receipt_currency_code RECEIPT_CURRENCY_CODE ,d.receipt_currency_amount RECEIPT_CURRENCY_AMOUNT ,d.receipt_conversion_rate RECEIPT_EXCHANGE_RATE ,p.segment1 PROJECT_NUMBER ,p.name PROJECT_NAME ,t.task_number TASK_NUMBER ,t.task_name TASK_NAME ,d.expenditure_type EXPENDITURE_TYPE ,cat.EXPENDITURE_CATEGORY_NAME EXPENDITURE_CATEGORY ,et.revenue_category_code REVENUE_CATEGORY ,d.PJC_EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE ,o.name EXPENDITURE_ORGANIZATION ,i.invoice_id INVOICE_ID ,i.vendor_id VENDOR_ID ,p.project_id PROJECT_ID ,p.project_type_id PROJECT_TYPE_ID ,p.project_currency_code PROJECT_CURRENCY_CODE ,p.Currency_Conv_Date_Type_Code PROJ_CURR_CONV_DATE_TYPE_CODE ,p.Currency_Conv_Date PROJ_CURRENCY_CONV_DATE ,p.Currency_Conv_Rate_Type PROJ_CURR_RATE_TYPE ,t.task_id TASK_ID ,o.organization_id EXPENDITURE_ORGANIZATION_ID ,'FINANCIAL_ELEMENTS' RESOURCE_CLASS ,d.invoice_line_number INVOICE_LINE_NUMBER ,d.invoice_distribution_id INVOICE_DISTRIBUTION_ID ,nvl(g.sla_ledger_cash_basis_flag,'N') CASH_BASIS_ACCT_IMPL ,d.award_id AWARD_SET_ID ,d.org_id ORG_ID ,d.pjc_expenditure_type_id EXPENDITURE_TYPE_ID ,et.cost_rate_flag EXP_TYPE_COST_RATE_FLAG ,cat.expenditure_category_id expenditure_category_id ,d.line_type_lookup_code LINE_TYPE_LOOKUP_CODE ,d.pjc_billable_flag BILLABLE_FLAG ,d.pjc_capitalizable_flag CAPITALIZABLE_FLAG ,d.pjc_work_type_id WORK_TYPE_ID ,d.pjc_contract_id CONTRACT_ID/*Added for grants uptake*/ ,d.pjc_reserved_attribute1 FUNDING_SOURCE_ID/*Added for grants uptake*/ ,d.pjc_funding_allocation_id FUNDING_ALLOCATION_ID/*Added for grants uptake*/ ,d.pjc_contract_line_id CONTRACT_LINE_ID/*Added for grants uptake*/ , l.inventory_item_id ,i.INVOICE_TYPE_LOOKUP_CODE FROM GL_LEDGERS g, ap_invoices_all i, POZ_SUPPLIERS_V v, HR_ORGANIZATION_V o, PJF_EXPEND_TYP_SYS_LINKS es, PJF_EXP_TYPES_B et , PJF_TASKS_V t, po_distributions_all po1, ap_invoice_distributions_all d, PJF_PROJECTS_ALL_VL p, PJF_EXP_CATEGORIES_VL CAT, ap_invoice_lines_all l WHERE i.vendor_id = v.vendor_id(+) /* Bug 21312777 - this is for expense report invoices where the vendor_id = -10016 */ AND i.invoice_id = d.invoice_id AND decode(d.pa_addition_flag,'G','Y','Z','Y','T','Y','E','Y','F','Y','A','Y',null,'N',d.pa_addition_flag) <> 'Y' AND d.po_distribution_id = po1.po_distribution_id (+) AND nvl(po1.distribution_type,'XXX') <> 'PREPAYMENT' AND ( ( i.invoice_type_lookup_code <> 'PREPAYMENT' ) OR ( i.invoice_type_lookup_code = 'PREPAYMENT' and d.po_distribution_id is NULL ) ) AND nvl(po1.destination_type_code, 'EXPENSE') = 'EXPENSE' AND d.pjc_project_id = p.project_id AND d.pjc_task_id = t.task_id AND d.pjc_organization_id = o.organization_id AND d.pjc_expenditure_type_id = es.expenditure_type_id AND et.expenditure_type_id = es.expenditure_type_id AND g.ledger_id = d.set_of_books_id AND nvl(i.source, 'xxx') not in ( 'Oracle Project Accounting','PA_IC_INVOICES') AND ES.SYSTEM_LINKAGE_FUNCTION = DECODE( I.INVOICE_TYPE_LOOKUP_CODE, 'EXPENSE REPORT','ER','PAYMENT REQUEST','ER','VI') AND d.line_type_lookup_code <> 'REC_TAX' AND ( ( d.prepay_distribution_id is NULL ) OR ( d.prepay_distribution_id is not null and exists ( select 1 from ap_invoice_distributions_all d2 where d2.invoice_distribution_id = d.prepay_distribution_id and NVL(d2.historical_flag, 'N') = 'Y' ) and d.po_distribution_id is NULL ) ) AND ( ( NVL(PO1.ACCRUE_ON_RECEIPT_FLAG ,'N') = 'N' ) OR ( NVL(PO1.ACCRUE_ON_RECEIPT_FLAG ,'N') = 'Y' AND D.LINE_TYPE_LOOKUP_CODE NOT IN ( 'ACCRUAL', 'ITEM', 'PREPAY', 'NONREC_TAX') AND NVL(D.INVENTORY_TRANSFER_STATUS,'N') <> 'Y' )) AND D.AMOUNT <> 0 AND NVL(d.reversal_flag,'N') <> 'Y' AND CAT.EXPENDITURE_CATEGORY_ID = ET.EXPENDITURE_CATEGORY_ID AND O.CLASSIFICATION_CODE = 'PA_EXPENDITURE_ORG' AND NVL(d.PJC_EXPENDITURE_ITEM_DATE, TRUNC(SYSDATE)) BETWEEN NVL(O.EFFECTIVE_START_DATE,TRUNC(SYSDATE)) AND NVL(O.EFFECTIVE_END_DATE,TRUNC(SYSDATE)) and l.invoice_id = d.invoice_id and l.line_number = d.invoice_line_number AND d.line_type_lookup_code <> 'RETAINAGE' UNION ALL SELECT i.invoice_num INVOICE_NUMBER ,v.vendor_name VENDOR_NAME ,i.invoice_date INVOICE_DATE ,d.distribution_line_number DISTRIBUTION_LINE_NUMBER ,d.description DESCRIPTION ,d.accounting_date GL_DATE ,decode(d.posted_flag,'Y','Y','N') POSTED_FLAG ,AP_INVOICES_PKG.GET_APPROVAL_STATUS(I.INVOICE_ID, I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG, I.INVOICE_TYPE_LOOKUP_CODE) APPROVAL_STATUS , d.amount QUANTITY , i.invoice_currency_code DENOM_CURRENCY_CODE , d.Amount DENOM_AMOUNT , g.currency_code ACCT_CURRENCY_CODE ,d.Base_Amount AMOUNT ,decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),TO_DATE(NULL),i.exchange_date) ACCT_RATE_DATE ,decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),NULL,i.exchange_rate_type) ACCT_RATE_TYPE ,decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),TO_NUMBER(NULL),i.exchange_rate) ACCT_EXCHANGE_RATE ,d.receipt_currency_code RECEIPT_CURRENCY_CODE ,d.receipt_currency_amount RECEIPT_CURRENCY_AMOUNT ,d.receipt_conversion_rate RECEIPT_EXCHANGE_RATE ,p.segment1 PROJECT_NUMBER ,p.name PROJECT_NAME ,t.task_number TASK_NUMBER ,t.task_name TASK_NAME ,d.expenditure_type EXPENDITURE_TYPE ,cat.EXPENDITURE_CATEGORY_NAME EXPENDITURE_CATEGORY ,et.revenue_category_code REVENUE_CATEGORY ,d.PJC_EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE ,o.name EXPENDITURE_ORGANIZATION ,i.invoice_id INVOICE_ID ,i.vendor_id VENDOR_ID ,p.project_id PROJECT_ID ,p.project_type_id PROJECT_TYPE_ID ,p.project_currency_code PROJECT_CURRENCY_CODE ,p.Currency_Conv_Date_Type_Code PROJ_CURR_CONV_DATE_TYPE_CODE ,p.Currency_Conv_Date PROJ_CURRENCY_CONV_DATE ,p.Currency_Conv_Rate_Type PROJ_CURR_RATE_TYPE ,t.task_id TASK_ID ,o.organization_id EXPENDITURE_ORGANIZATION_ID ,'FINANCIAL_ELEMENTS' RESOURCE_CLASS ,d.invoice_line_number INVOICE_LINE_NUMBER ,d.invoice_distribution_id INVOICE_DISTRIBUTION_ID ,nvl(g.sla_ledger_cash_basis_flag,'N') CASH_BASIS_ACCT_IMPL ,d.award_id AWARD_SET_ID ,d.org_id ORG_ID ,d.pjc_expenditure_type_id EXPENDITURE_TYPE_ID ,et.cost_rate_flag EXP_TYPE_COST_RATE_FLAG ,cat.expenditure_category_id expenditure_category_id ,d.line_type_lookup_code LINE_TYPE_LOOKUP_CODE ,d.pjc_billable_flag BILLABLE_FLAG ,d.pjc_capitalizable_flag CAPITALIZABLE_FLAG ,d.pjc_work_type_id WORK_TYPE_ID ,d.pjc_contract_id CONTRACT_ID/*Added for grants uptake*/ ,d.pjc_reserved_attribute1 FUNDING_SOURCE_ID/*Added for grants uptake*/ ,d.pjc_funding_allocation_id FUNDING_ALLOCATION_ID/*Added for grants uptake*/ ,d.pjc_contract_line_id CONTRACT_LINE_ID/*Added for grants uptake*/ , l.inventory_item_id ,i.INVOICE_TYPE_LOOKUP_CODE FROM GL_LEDGERS g, ap_invoices_all i, POZ_SUPPLIERS_V v, HR_ORGANIZATION_V o, PJF_EXPEND_TYP_SYS_LINKS es, PJF_EXP_TYPES_B et , PJF_TASKS_V t, po_distributions_all po1, ap_self_assessed_tax_dist_all d, PJF_PROJECTS_ALL_VL p, PJF_EXP_CATEGORIES_VL CAT, ap_invoice_lines_all l WHERE i.vendor_id = v.vendor_id(+) /* Bug 21312777 - this is for expense report invoices where the vendor_id = -10016 */ AND i.invoice_id = d.invoice_id AND decode(d.pa_addition_flag,'G','Y','Z','Y','T','Y','E','Y','F','Y','A','Y',null,'N',d.pa_addition_flag) <> 'Y' AND d.po_distribution_id = po1.po_distribution_id (+) AND nvl(po1.distribution_type,'XXX') <> 'PREPAYMENT' AND ( ( i.invoice_type_lookup_code <> 'PREPAYMENT' ) OR ( i.invoice_type_lookup_code = 'PREPAYMENT' and d.po_distribution_id is NULL ) ) AND nvl(po1.destination_type_code, 'EXPENSE') = 'EXPENSE' AND d.pjc_project_id = p.project_id AND d.pjc_task_id = t.task_id AND d.pjc_organization_id = o.organization_id AND d.pjc_expenditure_type_id = es.expenditure_type_id AND et.expenditure_type_id = es.expenditure_type_id AND g.ledger_id = d.set_of_books_id AND nvl(i.source, 'xxx') not in ( 'Oracle Project Accounting','PA_IC_INVOICES') AND ES.SYSTEM_LINKAGE_FUNCTION = DECODE( I.INVOICE_TYPE_LOOKUP_CODE, 'EXPENSE REPORT','ER','PAYMENT REQUEST','ER','VI') AND d.line_type_lookup_code <> 'REC_TAX' AND ( ( d.prepay_distribution_id is NULL ) OR ( d.prepay_distribution_id is not null and exists ( select 1 from ap_invoice_distributions_all d2 where d2.invoice_distribution_id = d.prepay_distribution_id and NVL(d2.historical_flag, 'N') = 'Y' ) and d.po_distribution_id is NULL ) ) AND ( ( NVL(PO1.ACCRUE_ON_RECEIPT_FLAG ,'N') = 'N' ) OR ( NVL(PO1.ACCRUE_ON_RECEIPT_FLAG ,'N') = 'Y' AND D.LINE_TYPE_LOOKUP_CODE NOT IN ( 'ACCRUAL', 'ITEM', 'PREPAY', 'NONREC_TAX') AND NVL(D.INVENTORY_TRANSFER_STATUS,'N') <> 'Y' )) AND D.AMOUNT <> 0 AND NVL(d.reversal_flag,'N') <> 'Y' AND CAT.EXPENDITURE_CATEGORY_ID = ET.EXPENDITURE_CATEGORY_ID AND O.CLASSIFICATION_CODE = 'PA_EXPENDITURE_ORG' AND NVL(d.PJC_EXPENDITURE_ITEM_DATE, TRUNC(SYSDATE)) BETWEEN NVL(O.EFFECTIVE_START_DATE,TRUNC(SYSDATE)) AND NVL(O.EFFECTIVE_END_DATE,TRUNC(SYSDATE)) and l.invoice_id = d.invoice_id and l.line_number = d.invoice_line_number AND d.line_type_lookup_code <> 'RETAINAGE' ) vw WHERE (vw.APPROVAL_STATUS IN ('APPROVED','NEEDS REAPPROVAL','NEVER APPROVED','UNAPPROVED') AND vw.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT') OR (vw.APPROVAL_STATUS IN ('AVAILABLE','NEEDS REAPPROVAL','UNAPPROVED','UNPAID','NEVER APPROVED') AND vw.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT')