VIEW: APPS.PA_PROJ_AP_INV_DIST_V2

Source

View Text - Preformatted

SELECT i.invoice_num , v.vendor_name , i.invoice_date , sat.invoice_distribution_id , 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 , (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, sat.pa_quantity,i.source,sat.LINE_TYPE_LOOKUP_CODE), 'N', sat.pa_quantity)) pa_quantity , i.invoice_currency_code , (DECODE(NVL(PO.accrue_on_receipt_flag,'N'), 'Y', PA_CMT_UTILS.get_inv_cmt(PO.po_distribution_id, 'Y', sat.pa_addition_flag, 0, PO.code_combination_id, 'AP',sat.invoice_id, sat.distribution_line_number), 'N', sat.amount)) denom_amount , g.currency_code , (DECODE(NVL(PO.accrue_on_receipt_flag,'N'), 'Y', PA_CMT_UTILS.get_inv_cmt(PO.po_distribution_id, 'N', sat.pa_addition_flag, 0, PO.code_combination_id, 'AP', sat.invoice_id, sat.distribution_line_number ), 'N', NVL(sat.base_amount,sat.amount))) amount , 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 , i.invoice_id , i.vendor_id , p.project_id , t.task_id , sat.expenditure_organization_id , 'FINANCIAL_ELEMENTS' FROM pa_projects p, pa_tasks t, ap_self_assessed_tax_dist_all sat, 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 = 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 = PO.po_distribution_id (+) AND NVL(PO.destination_type_code, 'EXPENSE') = 'EXPENSE' AND sat.project_id = t.project_id AND sat.task_id = t.task_id AND p.project_id = t.project_id AND sat.expenditure_type = es.expenditure_type AND et.expenditure_type = es.expenditure_type AND sat.line_type_lookup_code <> 'RETAINAGE' AND g.set_of_books_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 NVL(sat.TAX_RECOVERABLE_FLAG,'N') <> 'Y'

View Text - HTML Formatted

SELECT I.INVOICE_NUM
, V.VENDOR_NAME
, I.INVOICE_DATE
, SAT.INVOICE_DISTRIBUTION_ID
, 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
, (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
, SAT.PA_QUANTITY
, I.SOURCE
, SAT.LINE_TYPE_LOOKUP_CODE)
, 'N'
, SAT.PA_QUANTITY)) PA_QUANTITY
, I.INVOICE_CURRENCY_CODE
, (DECODE(NVL(PO.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, 'Y'
, PA_CMT_UTILS.GET_INV_CMT(PO.PO_DISTRIBUTION_ID
, 'Y'
, SAT.PA_ADDITION_FLAG
, 0
, PO.CODE_COMBINATION_ID
, 'AP'
, SAT.INVOICE_ID
, SAT.DISTRIBUTION_LINE_NUMBER)
, 'N'
, SAT.AMOUNT)) DENOM_AMOUNT
, G.CURRENCY_CODE
, (DECODE(NVL(PO.ACCRUE_ON_RECEIPT_FLAG
, 'N')
, 'Y'
, PA_CMT_UTILS.GET_INV_CMT(PO.PO_DISTRIBUTION_ID
, 'N'
, SAT.PA_ADDITION_FLAG
, 0
, PO.CODE_COMBINATION_ID
, 'AP'
, SAT.INVOICE_ID
, SAT.DISTRIBUTION_LINE_NUMBER )
, 'N'
, NVL(SAT.BASE_AMOUNT
, SAT.AMOUNT))) AMOUNT
, 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
, I.INVOICE_ID
, I.VENDOR_ID
, P.PROJECT_ID
, T.TASK_ID
, SAT.EXPENDITURE_ORGANIZATION_ID
, 'FINANCIAL_ELEMENTS'
FROM PA_PROJECTS P
, PA_TASKS T
, AP_SELF_ASSESSED_TAX_DIST_ALL SAT
, 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 = 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 = PO.PO_DISTRIBUTION_ID (+)
AND NVL(PO.DESTINATION_TYPE_CODE
, 'EXPENSE') = 'EXPENSE'
AND SAT.PROJECT_ID = T.PROJECT_ID
AND SAT.TASK_ID = T.TASK_ID
AND P.PROJECT_ID = T.PROJECT_ID
AND SAT.EXPENDITURE_TYPE = ES.EXPENDITURE_TYPE
AND ET.EXPENDITURE_TYPE = ES.EXPENDITURE_TYPE
AND SAT.LINE_TYPE_LOOKUP_CODE <> 'RETAINAGE'
AND G.SET_OF_BOOKS_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 NVL(SAT.TAX_RECOVERABLE_FLAG
, 'N') <> 'Y'