with
ACC_STATUS AS
(SELECT Decode(ap_invoices_pkg.get_posting_status(aia.invoice_id)
,'Y', 'Fully Accounted'
,'P','Partially Accounted'
,'N','Not Accounted'
,'D','Draft Accounted') as ACCOUNTING_STATUS, AIA.INVOICE_ID
FROM AP_INVOICES_ALL AIA)
,ASSETINFO AS
(SELECT
FAB.SERIAL_NUMBER, INV.INVOICE_ID, FAI.INVOICE_LINE_NUMBER,sum(fai.fixed_assets_cost)
FROM
FA_ADDITIONS_B FAB,
FA_ASSET_INVOICES FAI,
AP_INVOICES_ALL INV
WHERE
FAB.ASSET_ID=FAI.ASSET_ID
AND FAI.INVOICE_ID = INV.INVOICE_ID
and trunc(INV.creation_date)>= :p_invoice_creation_date_from
and trunc(INV.creation_date)<= :p_invoice_creation_date_to
and fai.book_type_code like '%CORPORATE%'
GROUP BY
FAB.SERIAL_NUMBER, INV.INVOICE_ID, FAI.INVOICE_LINE_NUMBER
having sum(fai.fixed_assets_cost)<> 0)
select
aia.source SOURCE
,aia.invoice_num INVOICE_NUMBER
,to_char(aia.invoice_date,'MM-DD-YYYY','nls_date_language=english') INVOICE_DATE
,to_char(aia.creation_date,'MM-DD-YYYY','nls_date_language=english') INVOICE_CREATION_DATE
,aia.invoice_amount INVOICE_AMOUNT
,aia.invoice_currency_code CURRENCY
,aila.line_number INVOICE_LINE_NUMBER
,aila.line_type_lookup_code LINE_TYPE
,aila.quantity_invoiced QUANTITY
,aila.unit_meas_lookup_code UNIT_OF_MEASURE
,aila.unit_price UNIT_PRICE
,aila.amount LINE_AMOUNT
,aila.description LINE_DESCRIPTION
,aila.asset_book_type_code ASSET_BOOK
,ptv.task_number TASK_ID
,petv.expenditure_type_name EXPENDITURE_TYPE
,to_char(aila.pjc_expenditure_item_date,'MM-DD-YYYY','nls_date_language=english') EXPENDITURE_DATE
,aila.pjc_user_def_attribute1 PO_NUMBER
,aila.cancelled_flag CANCELLED_STATUS
,to_char(apsa.due_date,'MM-DD-YYYY','nls_date_language=english') INVOICE_DUE_DATE
,att.name PAYMENT_TERMS
,gcc.segment1 COMAPANY_ID
,gcc.segment2 FACILITY
,gcc.segment3 COST_CENTER
,gcc.segment4 ACCOUNT
,gcc.segment5 LOB
,gcc.segment6 ACTIVITY
,gcc.segment7 INTERCOMPANY
,gcc.segment8 FUTURE
, psv.vendor_name SUPPLIER_NAME
,psv.segment1 SUPPLIER_NUMBER
,pssam.vendor_site_code SUPPLIER_SITE
,haou.name BUSINESS_UNIT
,hla.location_code SHIP_TO_LOCATION_CODE
,fcb.segment2 ASSET_CATEGORY
,ppab.segment1 PROJECT_NUMBER
,haou1.name EXPENDITURE_ORGANIZATION
,to_char((select check_date from ap_checks_all aca, ap_invoice_payments_all aipa where aipa.invoice_id = aia.invoice_id and aca.check_id = aipa.check_id and aca.status_lookup_code <> 'VOIDED'),'MM-DD-YYYY','nls_date_language=english') PAYEMENT_DATE --added condition for EAP_2262
,(select check_number from ap_checks_all aca, ap_invoice_payments_all aipa where aipa.invoice_id = aia.invoice_id and aca.check_id = aipa.check_id and aca.status_lookup_code <> 'VOIDED') PAYMENT_NUMBER --added condition for EAP_2262
,DECODE(AP_INVOICES_PKG.GET_APPROVAL_STATUS(aia.invoice_id, aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'CANCELLED', 'Cancelled',
'Validated') VALIDATION_STATUS
,aia.approval_status APPROVAL_STATUS
,zfpc.CLASSIFICATION_NAME PRODUCT_CATEGORY_NAME
,('https://' || adds.external_virtual_host || '/fscmUI/faces/deeplink?objType=AP_VIEWINVOICE&action=VIEW&objKey=InvoiceId=' || aia.invoice_id) AS "INVOICE_URL"
,assetinfo.serial_number SEQUENCE_NUMBER
from
ap_invoices_all aia
,ap_invoice_lines_all aila
,ap_payment_schedules_all apsa
,gl_code_combinations gcc
,ACC_STATUS acc
,ask_deployed_domains adds
,assetinfo -- EAP-2428
,PJF_TASKS_V ptv
,PJF_EXP_TYPES_VL petv
,poz_suppliers_v psv
,poz_supplier_sites_all_m pssam
,ap_terms_tl att
,hr_all_organization_units haou
,hr_locations_all hla
,fa_categories_b fcb
,pjf_projects_all_b ppab
,hr_all_organization_units haou1
,ZX_FC_PRODUCT_CATEGORIES_V zfpc
where 1=1
and aia.invoice_id = aila.invoice_id
and aia.invoice_id = apsa.invoice_id
and aila.default_dist_ccid = gcc.code_combination_id (+)
and acc.invoice_id = aia.invoice_id
and adds.deployed_domain_name = 'FADomain'
and upper(apsa.payment_method_code) = 'CAPEXLEASE'
and trunc(aia.creation_date)>= :p_invoice_creation_date_from
and trunc(aia.creation_date)<= :p_invoice_creation_date_to
and assetinfo.invoice_id(+)= aia.invoice_id -- EAP-2428
and assetinfo.invoice_line_number(+) = aila.line_number -- EAP-2428
and ptv.task_id(+) = aila.pjc_task_id
and ptv.project_id(+) = aila.pjc_project_id
and petv.expenditure_type_id(+)= aila.pjc_expenditure_type_id
and psv.vendor_id = aia.vendor_id
and pssam.vendor_site_id = aia.vendor_site_id
and att.term_id=aia.terms_id
and att.language = 'US'
and haou.organization_id = aia.org_id
and aila.ship_to_location_id = hla.ship_to_location_id(+)
and fcb.category_id(+) = aila.asset_category_id
and ppab.project_id(+) = aila.pjc_project_id
and haou1.organization_id(+) = aila.pjc_organization_id
and zfpc.CLASSIFICATION_CODE(+) = aila.product_category
AND TRUNC(SYSDATE) BETWEEN TRUNC(zfpc.EFFECTIVE_FROM(+)) AND TRUNC(NVL(zfpc.EFFECTIVE_TO(+),SYSDATE+1))
order by aia.creation_date asc, aia.invoice_num asc, aila.line_number asc