VIEW: APPS.PA_PROJ_AP_INV_DIST_V1

Source

View Text - Preformatted

SELECT i.invoice_num , v.vendor_name , i.invoice_date , d.invoice_distribution_id , 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 , (DECODE(NVL(PO.accrue_on_receipt_flag,'N'), 'Y', PA_CMT_UTILS.get_rcpt_qty(PO.po_distribution_id,0,0,0,'AP', PO.PO_LINE_ID,p.project_id,t.task_id,PO.po_distribution_id, d.pa_quantity,i.source,d.LINE_TYPE_LOOKUP_CODE), 'N', d.pa_quantity)) pa_quantity , i.invoice_currency_code , PA_CMT_UTILS.get_inv_cmt(PO.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) , PO.code_combination_id, 'AP' ,i.invoice_id ,d.distribution_line_number ,d.invoice_distribution_id , NVL(PO.accrue_on_receipt_flag,'N') ,PO.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 ) denorm_amount , g.currency_code , PA_CMT_UTILS.get_inv_cmt(PO.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)), PO.code_combination_id, 'AP' ,i.invoice_id ,d.distribution_line_number ,d.invoice_distribution_id , NVL(PO.accrue_on_receipt_flag,'N') ,PO.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 ) amount , 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),d.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 , i.invoice_id , i.vendor_id , p.project_id , t.task_id , d.expenditure_organization_id , 'FINANCIAL_ELEMENTS' FROM pa_projects p, pa_tasks t, ap_invoice_distributions_all d, gl_sets_of_books g, ap_invoices_all i, po_vendors v, pa_expend_typ_sys_links es, pa_expenditure_types et , po_distributions_all po 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' OR (d.pa_addition_flag = 'G' AND d.prepay_amount_remaining > 0 )) AND ( es.system_linkage_function = 'VI' OR ( es.system_linkage_function = 'ER' AND v.employee_id IS NOT NULL )) AND d.po_distribution_id = PO.po_distribution_id (+) AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE' AND d.project_id = t.project_id AND d.task_id = t.task_id AND p.project_id = t.project_id AND d.expenditure_type = es.expenditure_type AND d.line_type_lookup_code <> 'RETAINAGE' AND et.expenditure_type = es.expenditure_type AND g.set_of_books_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 NVL(d.TAX_RECOVERABLE_FLAG,'N') <> 'Y' 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 ) )

View Text - HTML Formatted

SELECT I.INVOICE_NUM
, V.VENDOR_NAME
, I.INVOICE_DATE
, D.INVOICE_DISTRIBUTION_ID
, 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
, (DECODE(NVL(PO.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, 'Y'
, PA_CMT_UTILS.GET_RCPT_QTY(PO.PO_DISTRIBUTION_ID
, 0
, 0
, 0
, 'AP'
, PO.PO_LINE_ID
, P.PROJECT_ID
, T.TASK_ID
, PO.PO_DISTRIBUTION_ID
, D.PA_QUANTITY
, I.SOURCE
, D.LINE_TYPE_LOOKUP_CODE)
, 'N'
, D.PA_QUANTITY)) PA_QUANTITY
, I.INVOICE_CURRENCY_CODE
, PA_CMT_UTILS.GET_INV_CMT(PO.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)
, PO.CODE_COMBINATION_ID
, 'AP'
, I.INVOICE_ID
, D.DISTRIBUTION_LINE_NUMBER
, D.INVOICE_DISTRIBUTION_ID
, NVL(PO.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, PO.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 ) DENORM_AMOUNT
, G.CURRENCY_CODE
, PA_CMT_UTILS.GET_INV_CMT(PO.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))
, PO.CODE_COMBINATION_ID
, 'AP'
, I.INVOICE_ID
, D.DISTRIBUTION_LINE_NUMBER
, D.INVOICE_DISTRIBUTION_ID
, NVL(PO.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, PO.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 ) AMOUNT
, 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)
, D.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
, I.INVOICE_ID
, I.VENDOR_ID
, P.PROJECT_ID
, T.TASK_ID
, D.EXPENDITURE_ORGANIZATION_ID
, 'FINANCIAL_ELEMENTS'
FROM PA_PROJECTS P
, PA_TASKS T
, AP_INVOICE_DISTRIBUTIONS_ALL D
, GL_SETS_OF_BOOKS G
, AP_INVOICES_ALL I
, PO_VENDORS V
, PA_EXPEND_TYP_SYS_LINKS ES
, PA_EXPENDITURE_TYPES ET
, PO_DISTRIBUTIONS_ALL PO
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' OR (D.PA_ADDITION_FLAG = 'G'
AND D.PREPAY_AMOUNT_REMAINING > 0 ))
AND ( ES.SYSTEM_LINKAGE_FUNCTION = 'VI' OR ( ES.SYSTEM_LINKAGE_FUNCTION = 'ER'
AND V.EMPLOYEE_ID IS NOT NULL ))
AND D.PO_DISTRIBUTION_ID = PO.PO_DISTRIBUTION_ID (+)
AND NVL(PO.DESTINATION_TYPE_CODE
, 'EXPENSE') = 'EXPENSE'
AND D.PROJECT_ID = T.PROJECT_ID
AND D.TASK_ID = T.TASK_ID
AND P.PROJECT_ID = T.PROJECT_ID
AND D.EXPENDITURE_TYPE = ES.EXPENDITURE_TYPE
AND D.LINE_TYPE_LOOKUP_CODE <> 'RETAINAGE'
AND ET.EXPENDITURE_TYPE = ES.EXPENDITURE_TYPE
AND G.SET_OF_BOOKS_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 NVL(D.TAX_RECOVERABLE_FLAG
, 'N') <> 'Y'
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 ) )