VIEW: APPS.PA_PROJ_AP_INV_DISTRIBUTIONS

Source

View Text - Preformatted

SELECT i.invoice_num ,v.vendor_name ,i.invoice_date ,d.distribution_line_number ,d.description ,d.accounting_date ,decode(d.posted_flag,'Y','Y','N') ,DECODE(AP_INVOICES_PKG.GET_APPROVAL_STATUS(I.INVOICE_ID, I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG, I.INVOICE_TYPE_LOOKUP_CODE),'APPROVED','Y','N') APPROVED_FLAG ,PA_CMT_UTILS.get_inv_cmt(po1.po_distribution_id, 'Y', d.pa_addition_flag, PA_CMT_UTILS.get_inv_var(d.invoice_distribution_id, 'Y', d.amount_variance, d.quantity_variance), po1.code_combination_id, 'AP' ,i.invoice_id ,d.distribution_line_number ,d.invoice_distribution_id ,nvl(po1.accrue_on_receipt_flag,'N') ,po1.po_line_id ,'Y' ,d.amount ,d.project_id ,d.line_type_lookup_code ,d.pa_quantity ,i.source ,nvl(g.sla_ledger_cash_basis_flag,'N') ,i.invoice_type_lookup_code ,d.historical_flag ,d.Prepay_amount_remaining ) ,i.invoice_currency_code ,PA_CMT_UTILS.get_inv_cmt(po1.po_distribution_id, 'Y', d.pa_addition_flag, PA_CMT_UTILS.get_inv_var(d.invoice_distribution_id, 'Y', d.amount_variance, d.quantity_variance), po1.code_combination_id, 'AP' ,i.invoice_id ,d.distribution_line_number ,d.invoice_distribution_id ,nvl(po1.accrue_on_receipt_flag,'N') ,po1.po_line_id ,'N' ,d.amount ,d.project_id ,d.line_type_lookup_code ,d.pa_quantity ,i.source ,nvl(g.sla_ledger_cash_basis_flag,'N') ,i.invoice_type_lookup_code ,d.historical_flag ,d.Prepay_amount_remaining ) ,g.currency_code ,PA_CMT_UTILS.get_inv_cmt(po1.po_distribution_id, 'N', d.pa_addition_flag, PA_CMT_UTILS.get_inv_var(d.invoice_distribution_id, 'N', nvl(base_amount_variance,d.amount_variance), nvl(d.base_quantity_variance,d.quantity_variance)), po1.code_combination_id, 'AP' ,i.invoice_id ,d.distribution_line_number ,d.invoice_distribution_id ,nvl(po1.accrue_on_receipt_flag,'N') ,po1.po_line_id ,'N' ,nvl(d.base_amount,d.amount) ,d.project_id ,d.line_type_lookup_code ,d.pa_quantity ,i.source ,nvl(g.sla_ledger_cash_basis_flag,'N') ,i.invoice_type_lookup_code ,d.historical_flag ,d.Prepay_amount_remaining ) ,decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),TO_DATE(NULL),d.exchange_date) ,decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),NULL,d.exchange_rate_type) ,decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),TO_NUMBER(NULL),i.exchange_rate) ,d.receipt_currency_code ,d.receipt_currency_amount ,d.receipt_conversion_rate ,p.segment1 ,p.name ,t.task_number ,t.task_name ,d.expenditure_type ,et.expenditure_category ,et.revenue_category_code ,d.expenditure_item_date ,o.name ,i.invoice_id ,i.vendor_id ,p.project_id ,t.task_id ,o.organization_id ,'FINANCIAL_ELEMENTS' ,d.invoice_line_number ,d.invoice_distribution_id ,nvl(g.sla_ledger_cash_basis_flag,'N') , d.award_id award_set_id ,PTE.cbs_element_id FROM GL_LEDGERS g, ap_invoices_all i, po_vendors v, hr_organization_units o, pa_expend_typ_sys_links es, pa_expenditure_types et , pa_tasks t, PA_TASKS_EXPEND_V PTE, po_distributions_all po1, ap_invoice_distributions_all d, pa_projects_all p WHERE i.vendor_id = v.vendor_id AND i.invoice_id = d.invoice_id AND decode(d.pa_addition_flag,'G','Y','Z','Y','T','Y','E','Y','F','Y',null,'N',d.pa_addition_flag) <> 'Y' AND ( es.system_linkage_function = 'VI' OR ( es.system_linkage_function = 'ER' AND v.employee_id IS NOT NULL )) 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.project_id = p.project_id AND d.task_id = PTE.TASK_ID AND T.TASK_ID = PTE.ACTUAL_TASK_ID AND d.expenditure_organization_id = o.organization_id AND d.expenditure_type = es.expenditure_type AND et.expenditure_type = es.expenditure_type 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','VI') AND d.line_type_lookup_code NOT IN ( 'REC_TAX', 'RETAINAGE') 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.reversal_flag,'N') <> 'Y' UNION SELECT i.invoice_num ,v.vendor_name ,i.invoice_date ,sat.distribution_line_number ,sat.description ,sat.accounting_date ,decode(sat.posted_flag,'Y','Y','N') ,DECODE(AP_INVOICES_PKG.GET_APPROVAL_STATUS(I.INVOICE_ID, I.INVOICE_AMOUNT, I.PAYMENT_STATUS_FLAG, I.INVOICE_TYPE_LOOKUP_CODE),'APPROVED','Y','N') APPROVED_FLAG ,PA_CMT_UTILS.get_inv_cmt(po1.po_distribution_id, 'Y', sat.pa_addition_flag, PA_CMT_UTILS.get_inv_var(sat.invoice_distribution_id, 'Y', sat.amount_variance, sat.quantity_variance), po1.code_combination_id, 'AP' ,i.invoice_id ,sat.distribution_line_number ,sat.invoice_distribution_id ,nvl(po1.accrue_on_receipt_flag,'N') ,po1.po_line_id ,'Y' ,sat.amount ,sat.project_id ,sat.line_type_lookup_code ,sat.pa_quantity ,i.source ,nvl(g.sla_ledger_cash_basis_flag,'N') ,i.invoice_type_lookup_code ,i.historical_flag ,sat.Prepay_amount_remaining ) ,i.invoice_currency_code ,PA_CMT_UTILS.get_inv_cmt(po1.po_distribution_id, 'Y', sat.pa_addition_flag, PA_CMT_UTILS.get_inv_var(sat.invoice_distribution_id, 'Y', sat.amount_variance, sat.quantity_variance), po1.code_combination_id, 'AP' ,i.invoice_id ,sat.distribution_line_number ,sat.invoice_distribution_id ,nvl(po1.accrue_on_receipt_flag,'N') ,po1.po_line_id ,'N' ,sat.amount ,sat.project_id ,sat.line_type_lookup_code ,sat.pa_quantity ,i.source ,nvl(g.sla_ledger_cash_basis_flag,'N') ,i.invoice_type_lookup_code ,i.historical_flag ,sat.Prepay_amount_remaining ) ,g.currency_code ,PA_CMT_UTILS.get_inv_cmt(po1.po_distribution_id, 'N', sat.pa_addition_flag, PA_CMT_UTILS.get_inv_var(sat.invoice_distribution_id, 'N', nvl(base_amount_variance,sat.amount_variance), nvl(sat.base_quantity_variance,sat.quantity_variance)), po1.code_combination_id, 'AP' ,i.invoice_id ,sat.distribution_line_number ,sat.invoice_distribution_id ,nvl(po1.accrue_on_receipt_flag,'N') ,po1.po_line_id ,'N' ,nvl(sat.base_amount,sat.amount) ,sat.project_id ,sat.line_type_lookup_code ,sat.pa_quantity ,i.source ,nvl(g.sla_ledger_cash_basis_flag,'N') ,i.invoice_type_lookup_code ,i.historical_flag ,sat.Prepay_amount_remaining ) ,decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),TO_DATE(NULL),i.exchange_date) ,decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),NULL,i.exchange_rate_type) ,decode(NVL(g.currency_code,'-99'),NVL(i.invoice_currency_code,'-99'),TO_NUMBER(NULL),i.exchange_rate) ,sat.receipt_currency_code ,sat.receipt_currency_amount ,sat.receipt_conversion_rate ,p.segment1 ,p.name ,t.task_number ,t.task_name ,sat.expenditure_type ,et.expenditure_category ,et.revenue_category_code ,sat.expenditure_item_date ,o.name ,i.invoice_id ,i.vendor_id ,p.project_id ,t.task_id ,o.organization_id ,'FINANCIAL_ELEMENTS' ,sat.invoice_line_number ,sat.invoice_distribution_id ,nvl(g.sla_ledger_cash_basis_flag,'N') , sat.award_id award_set_id ,PTE.cbs_element_id FROM GL_LEDGERS g, ap_invoices_all i, po_vendors v, hr_organization_units o, pa_expend_typ_sys_links es, pa_expenditure_types et , pa_tasks t, PA_TASKS_EXPEND_V PTE, po_distributions_all po1, ap_self_assessed_tax_dist_all sat, pa_projects_all p WHERE i.vendor_id = v.vendor_id AND i.invoice_id = sat.invoice_id AND decode(sat.pa_addition_flag,'G','Y','Z','Y','T','Y','E','Y','F','Y',null,'N',sat.pa_addition_flag) <> 'Y' AND ( es.system_linkage_function = 'VI' OR ( es.system_linkage_function = 'ER' AND v.employee_id IS NOT NULL )) AND sat.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 sat.po_distribution_id is NULL ) ) AND nvl(po1.destination_type_code, 'EXPENSE') = 'EXPENSE' AND sat.project_id = p.project_id AND sat.task_id = PTE.TASK_ID AND T.TASK_ID = PTE.ACTUAL_TASK_ID AND sat.expenditure_organization_id = o.organization_id AND sat.expenditure_type = es.expenditure_type AND et.expenditure_type = es.expenditure_type AND g.ledger_id = sat.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','VI') AND sat.line_type_lookup_code NOT IN ( 'REC_TAX', 'RETAINAGE') AND ( ( sat.prepay_distribution_id is NULL ) OR ( sat.prepay_distribution_id is not null and exists ( select 1 from ap_self_assessed_tax_dist_all sat2 where sat2.invoice_distribution_id = sat.prepay_distribution_id and NVL(i.historical_flag, 'N') = 'Y' ) and sat.po_distribution_id is NULL ) ) AND ( ( NVL(po1.accrue_on_receipt_flag ,'N') = 'N' ) OR ( NVL(po1.accrue_on_receipt_flag ,'N') = 'Y' and sat.line_type_lookup_code not in ('NONREC_TAX') ) ) AND nvl(sat.reversal_flag,'N') <> 'Y'

View Text - HTML Formatted

SELECT I.INVOICE_NUM
, V.VENDOR_NAME
, I.INVOICE_DATE
, D.DISTRIBUTION_LINE_NUMBER
, D.DESCRIPTION
, D.ACCOUNTING_DATE
, DECODE(D.POSTED_FLAG
, 'Y'
, 'Y'
, 'N')
, DECODE(AP_INVOICES_PKG.GET_APPROVAL_STATUS(I.INVOICE_ID
, I.INVOICE_AMOUNT
, I.PAYMENT_STATUS_FLAG
, I.INVOICE_TYPE_LOOKUP_CODE)
, 'APPROVED'
, 'Y'
, 'N') APPROVED_FLAG
, PA_CMT_UTILS.GET_INV_CMT(PO1.PO_DISTRIBUTION_ID
, 'Y'
, D.PA_ADDITION_FLAG
, PA_CMT_UTILS.GET_INV_VAR(D.INVOICE_DISTRIBUTION_ID
, 'Y'
, D.AMOUNT_VARIANCE
, D.QUANTITY_VARIANCE)
, PO1.CODE_COMBINATION_ID
, 'AP'
, I.INVOICE_ID
, D.DISTRIBUTION_LINE_NUMBER
, D.INVOICE_DISTRIBUTION_ID
, NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, PO1.PO_LINE_ID
, 'Y'
, D.AMOUNT
, D.PROJECT_ID
, D.LINE_TYPE_LOOKUP_CODE
, D.PA_QUANTITY
, I.SOURCE
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, I.INVOICE_TYPE_LOOKUP_CODE
, D.HISTORICAL_FLAG
, D.PREPAY_AMOUNT_REMAINING )
, I.INVOICE_CURRENCY_CODE
, PA_CMT_UTILS.GET_INV_CMT(PO1.PO_DISTRIBUTION_ID
, 'Y'
, D.PA_ADDITION_FLAG
, PA_CMT_UTILS.GET_INV_VAR(D.INVOICE_DISTRIBUTION_ID
, 'Y'
, D.AMOUNT_VARIANCE
, D.QUANTITY_VARIANCE)
, PO1.CODE_COMBINATION_ID
, 'AP'
, I.INVOICE_ID
, D.DISTRIBUTION_LINE_NUMBER
, D.INVOICE_DISTRIBUTION_ID
, NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, PO1.PO_LINE_ID
, 'N'
, D.AMOUNT
, D.PROJECT_ID
, D.LINE_TYPE_LOOKUP_CODE
, D.PA_QUANTITY
, I.SOURCE
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, I.INVOICE_TYPE_LOOKUP_CODE
, D.HISTORICAL_FLAG
, D.PREPAY_AMOUNT_REMAINING )
, G.CURRENCY_CODE
, PA_CMT_UTILS.GET_INV_CMT(PO1.PO_DISTRIBUTION_ID
, 'N'
, D.PA_ADDITION_FLAG
, PA_CMT_UTILS.GET_INV_VAR(D.INVOICE_DISTRIBUTION_ID
, 'N'
, NVL(BASE_AMOUNT_VARIANCE
, D.AMOUNT_VARIANCE)
, NVL(D.BASE_QUANTITY_VARIANCE
, D.QUANTITY_VARIANCE))
, PO1.CODE_COMBINATION_ID
, 'AP'
, I.INVOICE_ID
, D.DISTRIBUTION_LINE_NUMBER
, D.INVOICE_DISTRIBUTION_ID
, NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, PO1.PO_LINE_ID
, 'N'
, NVL(D.BASE_AMOUNT
, D.AMOUNT)
, D.PROJECT_ID
, D.LINE_TYPE_LOOKUP_CODE
, D.PA_QUANTITY
, I.SOURCE
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, I.INVOICE_TYPE_LOOKUP_CODE
, D.HISTORICAL_FLAG
, D.PREPAY_AMOUNT_REMAINING )
, DECODE(NVL(G.CURRENCY_CODE
, '-99')
, NVL(I.INVOICE_CURRENCY_CODE
, '-99')
, TO_DATE(NULL)
, D.EXCHANGE_DATE)
, DECODE(NVL(G.CURRENCY_CODE
, '-99')
, NVL(I.INVOICE_CURRENCY_CODE
, '-99')
, NULL
, D.EXCHANGE_RATE_TYPE)
, DECODE(NVL(G.CURRENCY_CODE
, '-99')
, NVL(I.INVOICE_CURRENCY_CODE
, '-99')
, TO_NUMBER(NULL)
, I.EXCHANGE_RATE)
, D.RECEIPT_CURRENCY_CODE
, D.RECEIPT_CURRENCY_AMOUNT
, D.RECEIPT_CONVERSION_RATE
, P.SEGMENT1
, P.NAME
, T.TASK_NUMBER
, T.TASK_NAME
, D.EXPENDITURE_TYPE
, ET.EXPENDITURE_CATEGORY
, ET.REVENUE_CATEGORY_CODE
, D.EXPENDITURE_ITEM_DATE
, O.NAME
, I.INVOICE_ID
, I.VENDOR_ID
, P.PROJECT_ID
, T.TASK_ID
, O.ORGANIZATION_ID
, 'FINANCIAL_ELEMENTS'
, D.INVOICE_LINE_NUMBER
, D.INVOICE_DISTRIBUTION_ID
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, D.AWARD_ID AWARD_SET_ID
, PTE.CBS_ELEMENT_ID
FROM GL_LEDGERS G
, AP_INVOICES_ALL I
, PO_VENDORS V
, HR_ORGANIZATION_UNITS O
, PA_EXPEND_TYP_SYS_LINKS ES
, PA_EXPENDITURE_TYPES ET
, PA_TASKS T
, PA_TASKS_EXPEND_V PTE
, PO_DISTRIBUTIONS_ALL PO1
, AP_INVOICE_DISTRIBUTIONS_ALL D
, PA_PROJECTS_ALL P
WHERE I.VENDOR_ID = V.VENDOR_ID
AND I.INVOICE_ID = D.INVOICE_ID
AND DECODE(D.PA_ADDITION_FLAG
, 'G'
, 'Y'
, 'Z'
, 'Y'
, 'T'
, 'Y'
, 'E'
, 'Y'
, 'F'
, 'Y'
, NULL
, 'N'
, D.PA_ADDITION_FLAG) <> 'Y'
AND ( ES.SYSTEM_LINKAGE_FUNCTION = 'VI' OR ( ES.SYSTEM_LINKAGE_FUNCTION = 'ER'
AND V.EMPLOYEE_ID IS NOT NULL ))
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.PROJECT_ID = P.PROJECT_ID
AND D.TASK_ID = PTE.TASK_ID
AND T.TASK_ID = PTE.ACTUAL_TASK_ID
AND D.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID
AND D.EXPENDITURE_TYPE = ES.EXPENDITURE_TYPE
AND ET.EXPENDITURE_TYPE = ES.EXPENDITURE_TYPE
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'
, 'VI')
AND D.LINE_TYPE_LOOKUP_CODE NOT IN ( 'REC_TAX'
, 'RETAINAGE')
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.REVERSAL_FLAG
, 'N') <> 'Y' UNION SELECT I.INVOICE_NUM
, V.VENDOR_NAME
, I.INVOICE_DATE
, SAT.DISTRIBUTION_LINE_NUMBER
, SAT.DESCRIPTION
, SAT.ACCOUNTING_DATE
, DECODE(SAT.POSTED_FLAG
, 'Y'
, 'Y'
, 'N')
, DECODE(AP_INVOICES_PKG.GET_APPROVAL_STATUS(I.INVOICE_ID
, I.INVOICE_AMOUNT
, I.PAYMENT_STATUS_FLAG
, I.INVOICE_TYPE_LOOKUP_CODE)
, 'APPROVED'
, 'Y'
, 'N') APPROVED_FLAG
, PA_CMT_UTILS.GET_INV_CMT(PO1.PO_DISTRIBUTION_ID
, 'Y'
, SAT.PA_ADDITION_FLAG
, PA_CMT_UTILS.GET_INV_VAR(SAT.INVOICE_DISTRIBUTION_ID
, 'Y'
, SAT.AMOUNT_VARIANCE
, SAT.QUANTITY_VARIANCE)
, PO1.CODE_COMBINATION_ID
, 'AP'
, I.INVOICE_ID
, SAT.DISTRIBUTION_LINE_NUMBER
, SAT.INVOICE_DISTRIBUTION_ID
, NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, PO1.PO_LINE_ID
, 'Y'
, SAT.AMOUNT
, SAT.PROJECT_ID
, SAT.LINE_TYPE_LOOKUP_CODE
, SAT.PA_QUANTITY
, I.SOURCE
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, I.INVOICE_TYPE_LOOKUP_CODE
, I.HISTORICAL_FLAG
, SAT.PREPAY_AMOUNT_REMAINING )
, I.INVOICE_CURRENCY_CODE
, PA_CMT_UTILS.GET_INV_CMT(PO1.PO_DISTRIBUTION_ID
, 'Y'
, SAT.PA_ADDITION_FLAG
, PA_CMT_UTILS.GET_INV_VAR(SAT.INVOICE_DISTRIBUTION_ID
, 'Y'
, SAT.AMOUNT_VARIANCE
, SAT.QUANTITY_VARIANCE)
, PO1.CODE_COMBINATION_ID
, 'AP'
, I.INVOICE_ID
, SAT.DISTRIBUTION_LINE_NUMBER
, SAT.INVOICE_DISTRIBUTION_ID
, NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, PO1.PO_LINE_ID
, 'N'
, SAT.AMOUNT
, SAT.PROJECT_ID
, SAT.LINE_TYPE_LOOKUP_CODE
, SAT.PA_QUANTITY
, I.SOURCE
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, I.INVOICE_TYPE_LOOKUP_CODE
, I.HISTORICAL_FLAG
, SAT.PREPAY_AMOUNT_REMAINING )
, G.CURRENCY_CODE
, PA_CMT_UTILS.GET_INV_CMT(PO1.PO_DISTRIBUTION_ID
, 'N'
, SAT.PA_ADDITION_FLAG
, PA_CMT_UTILS.GET_INV_VAR(SAT.INVOICE_DISTRIBUTION_ID
, 'N'
, NVL(BASE_AMOUNT_VARIANCE
, SAT.AMOUNT_VARIANCE)
, NVL(SAT.BASE_QUANTITY_VARIANCE
, SAT.QUANTITY_VARIANCE))
, PO1.CODE_COMBINATION_ID
, 'AP'
, I.INVOICE_ID
, SAT.DISTRIBUTION_LINE_NUMBER
, SAT.INVOICE_DISTRIBUTION_ID
, NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, PO1.PO_LINE_ID
, 'N'
, NVL(SAT.BASE_AMOUNT
, SAT.AMOUNT)
, SAT.PROJECT_ID
, SAT.LINE_TYPE_LOOKUP_CODE
, SAT.PA_QUANTITY
, I.SOURCE
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, I.INVOICE_TYPE_LOOKUP_CODE
, I.HISTORICAL_FLAG
, SAT.PREPAY_AMOUNT_REMAINING )
, DECODE(NVL(G.CURRENCY_CODE
, '-99')
, NVL(I.INVOICE_CURRENCY_CODE
, '-99')
, TO_DATE(NULL)
, I.EXCHANGE_DATE)
, DECODE(NVL(G.CURRENCY_CODE
, '-99')
, NVL(I.INVOICE_CURRENCY_CODE
, '-99')
, NULL
, I.EXCHANGE_RATE_TYPE)
, DECODE(NVL(G.CURRENCY_CODE
, '-99')
, NVL(I.INVOICE_CURRENCY_CODE
, '-99')
, TO_NUMBER(NULL)
, I.EXCHANGE_RATE)
, SAT.RECEIPT_CURRENCY_CODE
, SAT.RECEIPT_CURRENCY_AMOUNT
, SAT.RECEIPT_CONVERSION_RATE
, P.SEGMENT1
, P.NAME
, T.TASK_NUMBER
, T.TASK_NAME
, SAT.EXPENDITURE_TYPE
, ET.EXPENDITURE_CATEGORY
, ET.REVENUE_CATEGORY_CODE
, SAT.EXPENDITURE_ITEM_DATE
, O.NAME
, I.INVOICE_ID
, I.VENDOR_ID
, P.PROJECT_ID
, T.TASK_ID
, O.ORGANIZATION_ID
, 'FINANCIAL_ELEMENTS'
, SAT.INVOICE_LINE_NUMBER
, SAT.INVOICE_DISTRIBUTION_ID
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, SAT.AWARD_ID AWARD_SET_ID
, PTE.CBS_ELEMENT_ID
FROM GL_LEDGERS G
, AP_INVOICES_ALL I
, PO_VENDORS V
, HR_ORGANIZATION_UNITS O
, PA_EXPEND_TYP_SYS_LINKS ES
, PA_EXPENDITURE_TYPES ET
, PA_TASKS T
, PA_TASKS_EXPEND_V PTE
, PO_DISTRIBUTIONS_ALL PO1
, AP_SELF_ASSESSED_TAX_DIST_ALL SAT
, PA_PROJECTS_ALL P
WHERE I.VENDOR_ID = V.VENDOR_ID
AND I.INVOICE_ID = SAT.INVOICE_ID
AND DECODE(SAT.PA_ADDITION_FLAG
, 'G'
, 'Y'
, 'Z'
, 'Y'
, 'T'
, 'Y'
, 'E'
, 'Y'
, 'F'
, 'Y'
, NULL
, 'N'
, SAT.PA_ADDITION_FLAG) <> 'Y'
AND ( ES.SYSTEM_LINKAGE_FUNCTION = 'VI' OR ( ES.SYSTEM_LINKAGE_FUNCTION = 'ER'
AND V.EMPLOYEE_ID IS NOT NULL ))
AND SAT.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 SAT.PO_DISTRIBUTION_ID IS NULL ) )
AND NVL(PO1.DESTINATION_TYPE_CODE
, 'EXPENSE') = 'EXPENSE'
AND SAT.PROJECT_ID = P.PROJECT_ID
AND SAT.TASK_ID = PTE.TASK_ID
AND T.TASK_ID = PTE.ACTUAL_TASK_ID
AND SAT.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID
AND SAT.EXPENDITURE_TYPE = ES.EXPENDITURE_TYPE
AND ET.EXPENDITURE_TYPE = ES.EXPENDITURE_TYPE
AND G.LEDGER_ID = SAT.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'
, 'VI')
AND SAT.LINE_TYPE_LOOKUP_CODE NOT IN ( 'REC_TAX'
, 'RETAINAGE')
AND ( ( SAT.PREPAY_DISTRIBUTION_ID IS NULL ) OR ( SAT.PREPAY_DISTRIBUTION_ID IS NOT NULL
AND EXISTS ( SELECT 1
FROM AP_SELF_ASSESSED_TAX_DIST_ALL SAT2
WHERE SAT2.INVOICE_DISTRIBUTION_ID = SAT.PREPAY_DISTRIBUTION_ID
AND NVL(I.HISTORICAL_FLAG
, 'N') = 'Y' )
AND SAT.PO_DISTRIBUTION_ID IS NULL ) )
AND ( ( NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N') = 'N' ) OR ( NVL(PO1.ACCRUE_ON_RECEIPT_FLAG
, 'N') = 'Y'
AND SAT.LINE_TYPE_LOOKUP_CODE NOT IN ('NONREC_TAX') ) )
AND NVL(SAT.REVERSAL_FLAG
, 'N') <> 'Y'