Query to get invoice page using Deeplink

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