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

Query to get Payables and Receivables Project Details (Project Cash Flow Report)

with gl_period_dates as ( 

       select period_name, 

              year_start_date, 

              add_months(year_start_date,12)-1 year_end_date, 

              quarter_start_date, 

              add_months(quarter_start_date,3)-1 quarter_end_date,

              start_date period_start_date, 

              end_date period_end_date

       from   gl_periods 

       where  period_set_name='Global Calendar'

       and    period_year=:p_year 

       and   (quarter_num IN (:p_quarter) OR coalesce(:p_quarter, NULL) IS NULL)

       and   (period_name IN (:p_month) OR coalesce(:p_month, NULL) IS NULL)),

receivables as (

select fnbuv.bu_id, 

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              pcpl.contract_id,

              fnbuv.bu_name,

              xlp.name le_name,

              ppavl.name, 

              ppavl.segment1,

OCH.contract_number,

              SUM(NVL(aps.amount_applied,0)) cash_in,

-- SUM(NVL(xal.ACCOUNTED_DR, 0)-NVL(xal.ACCOUNTED_CR, 0)) billing_amt

(SELECT sum(NVL(PROJECT_CURR_BILLED_AMT,0)) 

          FROM

          pjb_inv_line_dists idl

          

          WHERE 1=1

          and idl.CONTRACT_ID = och.id

          and  invoice_id in ( select invoice_id from pjb_invoice_headers 

  where

          contract_id = idl.contract_id  

  and invoice_status_code = 'ACCEPTED')

          ) billing_amt

FROM   ra_customer_trx_all rct,

okc_k_headers_all_b och,

xla_transaction_entities xlate,

xla_events xet,

xla_ae_headers xah,

xla_ae_lines xal,

ar_payment_schedules_all aps,

fun_names_business_units_v fnbuv,

xle_entity_profiles xlp,

gl_period_dates gpd,

              pjf_projects_all_vl ppavl,

              (select distinct project_id, 

                      contract_id, 

                      major_version

               from   pjb_cntrct_proj_links ) pcpl

WHERE to_char(rct.interface_header_attribute1) = to_char(och.contract_number)

AND   to_char(rct.interface_header_attribute2) = to_char(och.id)

AND   rct.set_of_books_id = xah.ledger_id

       and   pcpl.contract_id=och.id

       and   pcpl.project_id=ppavl.project_id

       --and   och.version_type='C'

AND   och.major_version = (

         SELECT max(och1.major_version)

         FROM okc_k_headers_all_b och1

         WHERE och1.id = OCH.id)

       AND   pcpl.major_version = och.major_version  

       AND   xet.entity_id = xlate.entity_id

       AND   xet.application_id = xlate.application_id

       AND   rct.customer_trx_id = xlate.source_id_int_1

       AND   xal.ae_header_id = xah.ae_header_id

       AND   xet.event_id = xah.event_id

       AND   xah.ledger_id = Xal.ledger_id

   AND   xlp.LEGAL_EMPLOYER_FLAG='Y'

       AND   xal.accounting_class_code = 'RECEIVABLE'

       AND   rct.customer_trx_id = aps.customer_trx_id

       AND   fnbuv.bu_id = rct.org_id

       AND   xlp.legal_entity_id = rct.legal_entity_id

       and   xal.period_name = gpd.period_name

   AND   xlate.entity_code='TRANSACTIONS'

   and   xlate.ledger_id=xah.ledger_id

       and   trunc(xal.accounting_date) between gpd.period_start_date and gpd.period_end_date

       /* --- Prompts --- */

       and  (ppavl.project_id IN (:p_project_id) OR COALESCE(:p_project_id, NULL) IS NULL)

       and  (ppavl.carrying_out_organization_id IN (:p_carrying_out_organization_id) OR COALESCE(:p_carrying_out_organization_id, NULL) IS NULL)

       and  (fnbuv.bu_id IN (:p_business_unit_id) OR COALESCE(:p_business_unit_id, NULL) IS NULL)

       and  (xlp.legal_entity_id IN (:p_legal_entity_id) OR COALESCE(:p_legal_entity_id, NULL) IS NULL)

       and  (och.id IN (:p_contract_id) OR COALESCE(:p_contract_id, NULL) IS NULL)

and exists ( 

              select 'Y'

              from   gl_seg_val_hier_cf cf,

                     fnd_tree_version_vl ftvvl,

                     xla_mapping_set_values xmsv

              where  cf.tree_structure_code='GL_ACCT_FLEX'

              and    ftvvl.tree_code=cf.tree_code

              and    xmsv.enabled_flag='Y'

              and    ftvvl.tree_version_id=cf.tree_version_id

              and    xmsv.value_constant=cf.dep0_pk1_value

              and    xmsv.input_value_constant1=TO_CHAR(ppavl.carrying_out_organization_id)

              and    exists (

                     select 'Y'

                     from   xla_mapping_set_flavors xmsf, 

                            fnd_id_flex_structures fifs

                     where  xmsf.stripe_id=fifs.id_flex_num

                     and    fifs.id_flex_code='GL#'

                     and    fifs.id_flex_structure_code='xxxx' 

                     and    xmsf.mapping_set_code=xmsv.mapping_set_code

                     and    xmsf.mapping_set_flavor_id=xmsv.mapping_set_flavor_id)

              and    sysdate between ftvvl.effective_start_date and ftvvl.effective_end_date

              and    sysdate between xmsv.effective_start_date and xmsv.effective_end_date

              /* --- Prompts --- */

              and   (cf.dep0_pk1_value in (:p_lob_profit_centre) or coalesce(:p_lob_profit_centre,null) is null)

              and   (cf.dep27_pk1_value in (:p_lob_sub_business_group) or coalesce(:p_lob_sub_business_group,null) is null)

              and   (cf.dep28_pk1_value in (:p_lob_business_group) or coalesce(:p_lob_business_group,null) is null)

              and   (cf.dep29_pk1_value in (:p_lob_service_bundle) or coalesce(:p_lob_service_bundle,null) is null)

              and   (cf.dep30_pk1_value in (:p_lob_division) or coalesce(:p_lob_division,null) is null)

              /* --- End Prompts --- */

              and    cf.tree_code='XXXXX'

              and    xmsv.mapping_set_code ='WDAP_LOB_POO'

              union all 

              select 'Y' 

              from   dual 

              where  coalesce(:p_lob_profit_centre,null) is null

              and    coalesce(:p_lob_sub_business_group,null) is null

              and    coalesce(:p_lob_business_group,null) is null

              and    coalesce(:p_lob_service_bundle,null) is null

              and    coalesce(:p_lob_division,null) is null)

       /* --- End Prompts --- */ 

       and    1=1

GROUP BY fnbuv.bu_id, 

          och.id,

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              pcpl.contract_id,

              fnbuv.bu_name,

              xlp.name,

              ppavl.name, 

              ppavl.segment1,

OCH.contract_number),

payables as (

SELECT fnbuv.bu_id, 

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              aia.invoice_id,              

              fnbuv.bu_name,

              xlp.name le_name,

              ppavl.name, 

              ppavl.segment1, 

SUM(NVL(aipa.INVOICE_BASE_AMOUNT, 0)) cash_out,

SUM(NVL(xal.ACCOUNTED_DR, 0)) liability_amt

FROM   ap_invoices_all aia,

ap_invoice_payments_all aipa,

fun_names_business_units_v fnbuv,

xla_transaction_entities xte,

xla_events xe,

xla_ae_headers xah,

xla_ae_lines xal,

xle_entity_profiles xlp,

gl_period_dates gpd,

              pjf_projects_all_vl ppavl

WHERE  1 = 1

AND   aipa.invoice_id = aia.invoice_id

       and   aia.project_id=ppavl.project_id

AND   fnbuv.bu_id = aia.org_id

AND   aia.invoice_id = xte.source_id_int_1

AND   aia.set_of_books_id = xah.ledger_id

AND   xe.entity_id = xah.entity_id

AND   xte.entity_id = xe.entity_id

AND   xal.ae_header_id = xah.ae_header_id

AND   xte.application_id = xah.application_id

and   xte.ledger_id=xah.ledger_id

    AND   xte.entity_code='TRANSACTIONS'

AND   xal.accounting_class_code = 'LIABILITY'

AND   xlp.legal_entity_id = aia.legal_entity_id

       and   xal.period_name = gpd.period_name

       and   trunc(xal.accounting_date) between gpd.period_start_date and gpd.period_end_date

       /* --- Prompts --- */

       and  (fnbuv.bu_id IN (:p_business_unit_id) OR COALESCE(:p_business_unit_id, NULL) IS NULL)

       and  (xlp.legal_entity_id IN (:p_legal_entity_id) OR COALESCE(:p_legal_entity_id, NULL) IS NULL)

       and  (ppavl.project_id IN (:p_project_id) OR COALESCE(:p_project_id, NULL) IS NULL)

       and  (ppavl.carrying_out_organization_id IN (:p_carrying_out_organization_id) OR COALESCE(:p_carrying_out_organization_id, NULL) IS NULL)

       and exists ( 

              select 'Y'

              from   gl_seg_val_hier_cf cf,

                     fnd_tree_version_vl ftvvl,

                     xla_mapping_set_values xmsv

              where  cf.tree_structure_code='GL_ACCT_FLEX'

              and    ftvvl.tree_code=cf.tree_code

              and    xmsv.enabled_flag='Y'

              and    ftvvl.tree_version_id=cf.tree_version_id

              and    xmsv.value_constant=cf.dep0_pk1_value

              and    xmsv.input_value_constant1=TO_CHAR(ppavl.carrying_out_organization_id) 

              and    exists (

                     select 'Y'

                     from   xla_mapping_set_flavors xmsf, 

                            fnd_id_flex_structures fifs

                     where  xmsf.stripe_id=fifs.id_flex_num

                     and    fifs.id_flex_code='GL#'

                     and    fifs.id_flex_structure_code='xxxxx' 

                     and    xmsf.mapping_set_code=xmsv.mapping_set_code

                     and    xmsf.mapping_set_flavor_id=xmsv.mapping_set_flavor_id)

              and    sysdate between ftvvl.effective_start_date and ftvvl.effective_end_date

              and    sysdate between xmsv.effective_start_date and xmsv.effective_end_date

              /* --- Prompts --- */

              and   (cf.dep0_pk1_value in (:p_lob_profit_centre) or coalesce(:p_lob_profit_centre,null) is null)

              and   (cf.dep27_pk1_value in (:p_lob_sub_business_group) or coalesce(:p_lob_sub_business_group,null) is null)

              and   (cf.dep28_pk1_value in (:p_lob_business_group) or coalesce(:p_lob_business_group,null) is null)

              and   (cf.dep29_pk1_value in (:p_lob_service_bundle) or coalesce(:p_lob_service_bundle,null) is null)

              and   (cf.dep30_pk1_value in (:p_lob_division) or coalesce(:p_lob_division,null) is null)

              /* --- End Prompts --- */

              and    cf.tree_code='XXXXX'

              and    xmsv.mapping_set_code ='WDAP_LOB_POO'

              union all 

              select 'Y' 

              from   dual 

              where  coalesce(:p_lob_profit_centre,null) is null

              and    coalesce(:p_lob_sub_business_group,null) is null

              and    coalesce(:p_lob_business_group,null) is null

              and    coalesce(:p_lob_service_bundle,null) is null

              and    coalesce(:p_lob_division,null) is null)

       /* --- End Prompts --- */ 

       and    1=1

GROUP BY fnbuv.bu_id, 

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              aia.invoice_id,              

              fnbuv.bu_name,

              xlp.name,

              ppavl.name, 

              ppavl.segment1)

SELECT ar.carrying_out_organization_id,

       ar.bu_name,  

       ar.le_name,        

ar.segment1, 

       ar.name project_name, 

       nvl(ar.cash_in,0) cash_in,

nvl(ar.billing_amt,0) billing_amt,

nvl(ap.cash_out,0) cash_out,

nvl(ap.liability_amt,0) liability_amt,

(nvl(ar.cash_in,0)-nvl(ap.cash_out,0)) cash_flow,

(nvl(ar.billing_amt,0)-nvl(ar.cash_in,0)) receivable_amt,

(nvl(ap.liability_amt,0)-nvl(ap.cash_out,0)) payable_amt

FROM   receivables ar, 

       payables ap 

WHERE  ar.bu_id=ap.bu_id(+)

AND    ar.legal_entity_id=ap.legal_entity_id(+)

AND    ar.project_id=ap.project_id (+)

order by ar.segment1,

       ar.bu_name, 

       ar.le_name