Invoice Speedometer Balances query (ITEM, RETAINAGE, PREPAY, SUBTOTAL, TAX, MISC, FREGHT, TOTAL)

 select distinct 

 

 ------------------------------------

-- decode(c.invoice_type_lookup_code , 'PREPAYMENT'  ,  c.attribute4 ,

 nvl(c.attribute4 ,

 decode(

 (

 select   count( distinct  pod.po_header_id  )    

 from   ap_invoice_distributions_all   d , po_distributions_all  pod ,   po_headers_all poh

 where  d.invoice_id = a.invoice_id

 and   d.po_distribution_id   is  not  null

 and   d.po_distribution_id = pod.po_distribution_id

 and    pod.po_header_id = poh.po_header_id

 ) ,

 0 , null,

 1 ,  (

 select   max( poh.segment1  )    

 from   ap_invoice_distributions_all   d , po_distributions_all  pod ,   po_headers_all poh

 where  d.invoice_id = a.invoice_id

 and   d.po_distribution_id   is  not  null

 and   d.po_distribution_id = pod.po_distribution_id

 and    pod.po_header_id = poh.po_header_id

 ) ,

 'Multiple POs'

 

 ) ) PO_NUMBER  , 

 

 --------------------------------------------

 ---Projects---

  decode(

 (

 select   count( distinct  d.project_id  )    

 from   ap_invoice_distributions_all   d , pa_projects_all ppa

 where  d.invoice_id = a.invoice_id

 and   d.project_id   is  not  null

 and   d.project_id = ppa.project_id

 ) ,

 0 , null,

 1 ,  (

 select   max( ppa.segment1  )    

 from   ap_invoice_distributions_all   d , pa_projects_all ppa

 where  d.invoice_id = a.invoice_id

 and   d.project_id   is  not  null

 and   d.project_id = ppa.project_id

 ) ,

 'Multiple Projects'

 

 )  Project_Number  , 

 

 

 

 -----------------------------------------------

 

  d.name OU , a.invoice_id , c.invoice_num, c.invoice_type_lookup_code , c.invoice_date, 

  c.invoice_currency_code , c.exchange_rate INVOICE_EXCH_RATE,

  

  

  

  

  

   (nvl(c.base_amount , c.invoice_amount)) BASE_CCY_INVOICE_AMOUNT,

  (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code in (  'ACCRUAL', 'ITEM', 'IPV' , 'ERV')

   ) BASE_CCY_ITEM_LINE,

   

   (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code in (  'RETAINAGE')

   ) BASE_CCY_RETAINAGE,

   

       (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code = 'PREPAY'

   ) BASE_CCY_PREPAY,

   

   

   ---------------

   

   

  (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code in (  'ACCRUAL', 'ITEM', 'IPV' , 'ERV' )

   ) +

   

   (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code in (  'RETAINAGE')

   ) +

   

       (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code = 'PREPAY'

   ) BASE_CCY_SUBTOTAL,

   -----------------

   

   (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code in ( 'REC_TAX' ,'NONREC_TAX')

   ) BASE_CCY_TAX, 

   

   

      (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code = 'MISCELLANEOUS'

   ) BASE_CCY_MISCELLANEOUS,

     

      (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code = 'FREIGHT'

   ) BASE_CCY_FREIGHT,

   

   

   -----------

   (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code in (  'ACCRUAL', 'ITEM', 'IPV' , 'ERV' )

   ) +

   

   (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code in (  'RETAINAGE')

   ) +

   

       (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code = 'PREPAY'

   ) +

   

    

   (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code in ( 'REC_TAX' ,'NONREC_TAX')

   ) + 

   

   

      (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code = 'MISCELLANEOUS'

   ) +

     

      (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code = 'FREIGHT'

   ) BASE_CCY_TOTAL 

   

   ------------

   

      

    /* , (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code = 'IPV'

   ) IPV

*/

  /* , (select   nvl(sum(nvl(b.base_amount , b.amount)),0)

   from ap_invoice_distributions_all b

   where a.invoice_id = b.invoice_id

   and a.org_id = b.org_id 

   and b.line_type_lookup_code = 'ERV'

   ) ERV

*/

   from ap_invoice_distributions_all a, ap_invoices_all c, hr_operating_units d

   where a.invoice_id = c.invoice_id

   and a.org_id = c.org_id

   and c.org_id = d.organization_id

  

   and c.cancelled_date is null

   --and a.line_type_lookup_code = 'ERV'

   --and c.invoice_num =  'ERS-500011378-355503'  --  '140480'  

   and

   (

   &p_cs_proj  is  null  or  ( &p_cs_proj  is  not null  and

   a.project_id in (

   select ff.project_id from pa_projects_all ff  

   where ff.org_id = a.org_id

   and  instr(&p_cs_proj , ff.segment1) > 0

   )

   )

   )

   

   and 

   

     (

   &p_cs_po_num  is  null  or  ( &p_cs_po_num  is  not null  and

   a.po_distribution_id in (

   select hh.po_distribution_id from po_distributions_all hh, po_headers_all gg  where  

   hh.po_header_id = gg.po_header_id 

   and hh.org_id = gg.org_id

   and gg.org_id = a.org_id

   and instr(&p_cs_po_num , gg.segment1) > 0

   )

   )

   )

 and a.org_id = nvl (&P_ORG_ID , a.org_id) 

PLSQL query to fetch Approver Name in AME Approver Groups

 https://aporaclepayables.blogspot.com/2022/08/plsql-query-to-fetch-approver-name-in.html

SELECT *

  FROM (select AAG.NAME, ac.APPLICATION_NAME,  ame_approver_type_pkg.getApproverDisplayName3(agi.parameter) APPROVER,

               appTypesLookUp.Meaning APPROVER_TYPE,

               agi.order_number ORDER_NUMBER

          from ame_approval_group_items agi, AME_APPROVAL_GROUPS_VL AAG, AME_APPROVAL_GROUP_CONFIG AAGC,

         ame_calling_apps_vl ac, 

               (select *

                  from wf_roles

                 where status = 'ACTIVE'

                   AND nvl(expiration_date, sysdate) >= sysdate

                   AND orig_system in ('PER', 'POS', 'FND_USR')) roles,

               FND_LOOKUPS appTypesLookUp

         where sysdate between agi.start_date and

               nvl(agi.end_date - (1 / 86400), sysdate)

           AND agi.parameter_name = 'wf_roles_name'

           AND roles.name(+) = agi.parameter

           and AAG.APPROVAL_GROUP_ID = agi.approval_group_id

           and ac.APPLICATION_id = AAGC.Application_Id 

         --  and AAGC.Application_Id = -1

           and AAGC.Approval_Group_Id = AAG.APPROVAL_GROUP_ID

           AND appTypesLookUp.lookup_type = 'FND_WF_ORIG_SYSTEMS'

           AND appTypesLookUp.lookup_code =

               ame_approver_type_pkg.getApproverOrigSystem2(agi.parameter)

        --   AND agi.approval_group_id = :1

        union

        select AAG.NAME, AC.Application_name, apg.name               APPROVER,

               appTypesLookUp.Meaning APPROVER_TYPE,

               agi.order_number       ORDER_NUMBER

          from ame_approval_groups      apg,

               ame_approval_group_items agi,

               FND_LOOKUPS              appTypesLookUp,  AME_APPROVAL_GROUPS_VL AAG, ame_calling_apps_vl ac,

                AME_APPROVAL_GROUP_CONFIG  AAGC

         where sysdate between agi.start_date and

               nvl(agi.end_date - (1 / 86400), sysdate)

           AND sysdate between apg.start_date and

               nvl(apg.end_date - (1 / 86400), sysdate)

           AND agi.parameter_name = 'OAM_group_id'

           AND agi.parameter = to_char(apg.approval_group_id)

           and AAG.APPROVAL_GROUP_ID = agi.approval_group_id

           and AAGC.Approval_Group_Id = AAG.APPROVAL_GROUP_ID

           and ac.APPLICATION_id = AAGC.Application_Id 

           AND appTypesLookUp.lookup_type = 'AME_NESTED_GROUP'

           AND appTypesLookUp.lookup_code = 'NESTED_GROUP'

         --  and AAGC.Application_Id = -1

         --  AND agi.approval_group_id = :2

           ) QRSLT

 ORDER BY ORDER_NUMBER

Formula to find GST (Inclusive and Exclusive)

https://aporaclepayables.blogspot.com/2021/08/formula-to-find-gst-amount-from-invoice.html

Formula to Find GST Amount from Total Invoice Amount (If Invoice Amount is inclusive of GST Amount)

A Total Invoice Amount incl. 13% sales Tax 403416.78 B Formula to calculate Invoice Amount from the Total Invoice Amount =403416.78/((100+13)/100) 357006 A-B GST 13%      46,410.78

OR

A	Total Invoice Amount incl. 13% Sales Tax	403,416.78
B	Formula to calculate GST 13% from the Total Invoice Amount =403416.78*(13/(100+13))	46,410.78
A-B	Invoice Amount exclusive of 13% Sales Tax	357,006.00

-----------------------------------------------------------------------------------------------------------------------------

Formula to Find GST Amount from Invoice Amount (If Invoice Amount is Exclusive of GST Amount)

A	Invoice Amount exclusive of 13% Sales Tax	357006
B	GST	13%
C=A*B	Formula to calculate GST from Invoice Amount=357006*13%	46410.78
A+C	Total Invoice Amount incl. 13% Sales Tax	403416.78

-----------------------------------------------------------------------------------------------------------------------------

Formula to find Invoice Amount from GST Amount 

A	GST (13%)	46410.78
B	Invoice Amount exclusive of 13% Sales Tax =46410.78/13%	357006
A+B	Total Invoice Amount incl. 13% Sales Tax	403416.78

Active Invoice Remit-To Bank Account Number from Supplier Header Banking Details

 select 

party_id,

"Supplier Name", "vendor_site_code",

"Vendor Category", "Supplier Number",

 "Concern Dept","Supplier Creation date",

 "Supplier Inactive date" ,  -- "Supplier site Inactive date",

  "Status",

 NVL(  "Payment method on Site","Payment method on Supplier") "Payment method" ,

 "Account Number",   "Address", "Email"

 from ( select   sup.party_id,

sup.vendor_name "Supplier Name",

ss.vendor_site_code  "vendor_site_code",

 sup.vendor_type_lookup_code "Vendor Category",

       sup.segment1 "Supplier Number",

       sup.attribute1 "Concern Dept",

        trunc(sup.creation_date) "Supplier Creation date",

        

         trunc(sup.end_date_active) "Supplier Inactive date",

         

        -- trunc( ss.inactive_date) "Supplier site Inactive date",

        

          (case when trunc(sup.end_date_active) >= sysdate then 'ACTIVE'

         when sup.end_date_active is null then 'ACTIVE'

           else 'INACTIVE' end)  "Status",

          

      (select rr.payment_method_code

          from apps.IBY_EXT_PARTY_PMT_MTHDS rr

         where rr.primary_flag = 'Y'

           and rr.ext_pmt_party_id =

               (select bb.ext_payee_id

                  from apps.iby_external_payees_all bb

                 where bb.org_id is null

                  /*This One Addedd */  and bb.party_site_id is null

                   and bb.payee_party_id =

                       (select cc.payee_party_id   

                          from apps.iby_external_payees_all cc

                         where cc.org_id = ss.org_id

                           and cc.party_site_id = ss.party_site_id

                           and cc.supplier_site_id = ss.vendor_site_id))) "Payment method on Supplier",   

         

         

       (  SELECT  ieppm.payment_method_code

       

       FROM apps.ap_supplier_sites_all assa,

            apps.ap_suppliers sup1,

            apps.iby_external_payees_all iepa,

            apps.iby_ext_party_pmt_mthds ieppm

      WHERE  sup1.vendor_id = assa.vendor_id

        --AND assa.pay_site_flag = 'Y'

        AND assa.vendor_site_id = iepa.supplier_site_id

        AND iepa.ext_payee_id = ieppm.ext_pmt_party_id

          and iepa.org_id = assa.org_id

        AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE)

            )

            and sup1.vendor_id = sup.vendor_id

        AND assa.vendor_site_id = ss.vendor_site_id

       and iepa.org_id = ss.org_id

        AND ieppm.primary_flag = 'Y'

   )  "Payment method on Site",

   

         (   SELECT

 accts.bank_account_num

from apps.iby_pmt_instr_uses_all uses,

apps.iby_external_payees_all payee,

apps.iby_ext_bank_accounts accts,

--apps.fnd_currencies_vl fc,

apps.HZ_PARTIES bank,

apps.HZ_ORGANIZATION_PROFILES bankProfile,

apps.CE_BANK_BRANCHES_V branch

where uses.instrument_type = 'BANKACCOUNT'

and payee.ext_payee_id = uses.ext_pmt_party_id

and payee.payee_party_id =sup.party_id

and uses.instrument_id = accts.ext_bank_account_id

--and fc.currency_code(+) = accts.currency_code

AND sysdate between NVL(accts.start_date, sysdate-1) AND

NVL(accts.end_date, sysdate+1)

AND accts.bank_id = bank.party_id(+)

AND accts.bank_id = bankProfile.party_id(+)

AND accts.branch_id = branch.branch_party_id(+)

and SYSDATE between TRUNC(bankProfile.effective_start_date(+)) and

NVL(TRUNC(bankProfile.effective_end_date(+)), SYSDATE + 1)

and SYSDATE between TRUNC(uses.start_date(+)) and

NVL(TRUNC(uses.end_date(+)), SYSDATE + 1)

--and ( trunc(uses.end_date) is null or trunc(uses.end_date) >= sysdate)

--and trunc(uses.end_date) is null

and nvl(payee.org_id,693) = 693

and uses.order_of_preference = ( SELECT

min(

uses.order_of_preference)

from apps.iby_pmt_instr_uses_all uses,

apps.iby_external_payees_all payee,

apps.iby_ext_bank_accounts accts,

apps.fnd_currencies_vl fc,

apps.HZ_PARTIES bank,

apps.HZ_ORGANIZATION_PROFILES bankProfile,

apps.CE_BANK_BRANCHES_V branch

where uses.instrument_type = 'BANKACCOUNT'

and payee.ext_payee_id = uses.ext_pmt_party_id

and payee.payee_party_id = sup.party_id

and uses.instrument_id = accts.ext_bank_account_id

and fc.currency_code(+) = accts.currency_code

AND sysdate between NVL(accts.start_date, sysdate-1) AND

NVL(accts.end_date, sysdate+1)

AND accts.bank_id = bank.party_id(+)

AND accts.bank_id = bankProfile.party_id(+)

AND accts.branch_id = branch.branch_party_id(+)

and SYSDATE between TRUNC(bankProfile.effective_start_date(+)) and

NVL(TRUNC(bankProfile.effective_end_date(+)), SYSDATE + 1)

and SYSDATE between TRUNC(uses.start_date(+)) and

NVL(TRUNC(uses.end_date(+)), SYSDATE + 1)

--and ( trunc(uses.end_date) is null or trunc(uses.end_date) >= sysdate)

--and trunc(uses.end_date) is null

and nvl(payee.org_id,693) = 693  )

                )  "Account Number",

       ss.address_line1 || ', ' || ss.address_line2 || ', ' ||ss.address_line3 "Address",

      -- sup.num_1099 CNIC,

      -- sup.vat_registration_num NTN,

      

                           

                         

                  

                

     /*           

                 (  SELECT  max(accts.bank_account_name)

          from iby_pmt_instr_uses_all   uses,

               iby_external_payees_all  payee,

               iby_ext_bank_accounts    accts,

               fnd_currencies_vl        fc,

               HZ_PARTIES               bank,

               HZ_ORGANIZATION_PROFILES bankProfile,

               CE_BANK_BRANCHES_V       branch

         where uses.instrument_type = 'BANKACCOUNT'

           and payee.ext_payee_id = uses.ext_pmt_party_id

        and payee.payee_party_id = sup.party_id

           and uses.instrument_id = accts.ext_bank_account_id

           and fc.currency_code(+) = accts.currency_code

           AND sysdate between NVL(accts.start_date, sysdate) AND

               NVL(accts.end_date, sysdate)

           AND accts.bank_id = bank.party_id(+)

           AND accts.bank_id = bankProfile.party_id(+)

           AND accts.branch_id = branch.branch_party_id(+)

           and SYSDATE between TRUNC(bankProfile.effective_start_date(+)) and

               NVL(TRUNC(bankProfile.effective_end_date(+)), SYSDATE + 1)

               and nvl(payee.org_id,693) = 693

              -- and (trunc(uses.end_date) is null or trunc(uses.end_date) not between '01-DEC-2017' and '30-NOV-2021')

                )  "Account title",

                

                (  SELECT  max(trunc(accts.creation_date))

          from iby_pmt_instr_uses_all   uses,

               iby_external_payees_all  payee,

               iby_ext_bank_accounts    accts,

               fnd_currencies_vl        fc,

               HZ_PARTIES               bank,

               HZ_ORGANIZATION_PROFILES bankProfile,

               CE_BANK_BRANCHES_V       branch

         where uses.instrument_type = 'BANKACCOUNT'

           and payee.ext_payee_id = uses.ext_pmt_party_id

        and payee.payee_party_id = sup.party_id

           and uses.instrument_id = accts.ext_bank_account_id

           and fc.currency_code(+) = accts.currency_code

           AND sysdate between NVL(accts.start_date, sysdate) AND

               NVL(accts.end_date, sysdate)

           AND accts.bank_id = bank.party_id(+)

           AND accts.bank_id = bankProfile.party_id(+)

           AND accts.branch_id = branch.branch_party_id(+)

           and SYSDATE between TRUNC(bankProfile.effective_start_date(+)) and

               NVL(TRUNC(bankProfile.effective_end_date(+)), SYSDATE + 1)

               and nvl(payee.org_id,693) = 693

            --   and (trunc(uses.end_date) is null or trunc(uses.end_date) not between '01-DEC-2017' and '30-NOV-2021')

                )  "Account Opening date",

       null "Account Title",

       null "Account Opening date",

       ( 

       select hcp1.phone_number

from hz_contact_points  hcp1

where hcp1.owner_table_id(+) = ss.party_site_id

           and hcp1.CONTACT_POINT_TYPE(+) = 'PHONE'

           and hcp1.phone_line_type(+) = 'GEN'

           and hcp1.status(+) = 'A'

           and hcp1.owner_table_name(+) = 'HZ_PARTY_SITES'

           and hcp1.primary_flag(+) = 'Y'

) "MOBILE NUMBER",

*/

       ( 

       select hcp2.email_address

from apps.hz_contact_points  hcp2

where hcp2.owner_table_id(+) = ss.party_site_id

           and hcp2.CONTACT_POINT_TYPE(+) = 'EMAIL'

           and hcp2.status(+) = 'A'

           and hcp2.owner_table_name(+) = 'HZ_PARTY_SITES'

           and hcp2.primary_flag(+) = 'Y'

)  "Email"

/*       sup.vendor_type_lookup_code "Occupation/Services type",

       trunc(sup.last_update_date) "Last Updation date",

     

             

   (select user_name  from fnd_user  v where v.user_id = sup.created_by) "Vendor Openned By",

   

   

   (select count(b.invoice_id)  

   from ap_checks_all a, ap_invoice_payments_all b 

   where a.check_id = b.check_id

  -- and ( a.void_date is null or trunc(a.void_date) not between '01-DEC-2017' and '30-NOV-2021')

   and a.vendor_id = sup.vendor_id

   and a.vendor_site_id = ss.vendor_site_id and a.org_id = ss.org_id

   ) "Total Number of purchases", 

   (

    select sum(b.amount)  

    from ap_checks_all a, ap_invoice_payments_all b 

    where a.check_id = b.check_id

  -- and ( a.void_date is null or trunc(a.void_date) not between '01-DEC-2017' and '30-NOV-2021')

   and a.vendor_id = sup.vendor_id

   and a.vendor_site_id = ss.vendor_site_id and a.org_id = ss.org_id

   ) "Total Payment received",

 

  

   null "CAAML Status"*/

   

  FROM apps.ap_suppliers sup, apps.ap_supplier_sites_all ss

 WHERE sup.vendor_id = ss.vendor_id

 --and trunc(sup.creation_date) between '01-DEC-2017' and '30-NOV-2021'

 --and sup.vendor_name ='NOOR ALAM'

  and ss.org_id = 693

  order by sup.segment1 desc

  )

   

How to get Payment Method of a supplier from (Supplier Level or Site Level)

select  

"Supplier Name", "vendor_site_code",

"Vendor Category", "Supplier Number",

 "Concern Dept","Supplier Creation date",

 "Supplier Inactive date" ,  -- "Supplier site Inactive date",

  "Status",

 NVL(  "Payment method on Site","Payment method on Supplier") "Payment method" ,

 "Account Number",   "Address", "Email"

 from ( select

sup.vendor_name "Supplier Name",

ss.vendor_site_code  "vendor_site_code",

 sup.vendor_type_lookup_code "Vendor Category",

       sup.segment1 "Supplier Number",

       sup.attribute1 "Concern Dept",

        trunc(sup.creation_date) "Supplier Creation date",

        

         trunc(sup.end_date_active) "Supplier Inactive date",

         

        -- trunc( ss.inactive_date) "Supplier site Inactive date",

        

          (case when trunc(sup.end_date_active) >= sysdate then 'ACTIVE'

         when sup.end_date_active is null then 'ACTIVE'

           else 'INACTIVE' end)  "Status",

          

      (select rr.payment_method_code

          from apps.IBY_EXT_PARTY_PMT_MTHDS rr

         where rr.primary_flag = 'Y'

           and rr.ext_pmt_party_id =

               (select bb.ext_payee_id

                  from apps.iby_external_payees_all bb

                 where bb.org_id is null

                  /*This One Addedd */  and bb.party_site_id is null

                   and bb.payee_party_id =

                       (select cc.payee_party_id   

                          from apps.iby_external_payees_all cc

                         where cc.org_id = ss.org_id

                           and cc.party_site_id = ss.party_site_id

                           and cc.supplier_site_id = ss.vendor_site_id))) "Payment method on Supplier",   

         

         

       (  SELECT  ieppm.payment_method_code

       

       FROM apps.ap_supplier_sites_all assa,

            apps.ap_suppliers sup1,

            apps.iby_external_payees_all iepa,

            apps.iby_ext_party_pmt_mthds ieppm

      WHERE  sup1.vendor_id = assa.vendor_id

        --AND assa.pay_site_flag = 'Y'

        AND assa.vendor_site_id = iepa.supplier_site_id

        AND iepa.ext_payee_id = ieppm.ext_pmt_party_id

          and iepa.org_id = assa.org_id

        AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE)

            )

            and sup1.vendor_id = sup.vendor_id

        AND assa.vendor_site_id = ss.vendor_site_id

       and iepa.org_id = ss.org_id

        AND ieppm.primary_flag = 'Y'

   )  "Payment method on Site",

   

              (  SELECT  max(accts.bank_account_num)

          from apps.iby_pmt_instr_uses_all   uses,

               apps.iby_external_payees_all  payee,

               apps.iby_ext_bank_accounts    accts,

               apps.fnd_currencies_vl        fc,

               apps.HZ_PARTIES               bank,

               apps.HZ_ORGANIZATION_PROFILES bankProfile,

               apps.CE_BANK_BRANCHES_V       branch

         where uses.instrument_type = 'BANKACCOUNT'

           and payee.ext_payee_id = uses.ext_pmt_party_id

        and payee.payee_party_id = sup.party_id

           and uses.instrument_id = accts.ext_bank_account_id

           and fc.currency_code(+) = accts.currency_code

           AND sysdate between NVL(accts.start_date, sysdate-1) AND

               NVL(accts.end_date, sysdate+1)

           AND accts.bank_id = bank.party_id(+)

           AND accts.bank_id = bankProfile.party_id(+)

           AND accts.branch_id = branch.branch_party_id(+)

           and SYSDATE between TRUNC(bankProfile.effective_start_date(+)) and

               NVL(TRUNC(bankProfile.effective_end_date(+)), SYSDATE + 1)

               and nvl(payee.org_id,693) = 693

             --  and (trunc(uses.end_date) is null or trunc(uses.end_date) not between '01-DEC-2017' and '30-NOV-2021')

                )  "Account Number",

       ss.address_line1 || ', ' || ss.address_line2 || ', ' ||ss.address_line3 "Address",

      -- sup.num_1099 CNIC,

      -- sup.vat_registration_num NTN,

      

                           

                         

                  

                

     /*           

                 (  SELECT  max(accts.bank_account_name)

          from iby_pmt_instr_uses_all   uses,

               iby_external_payees_all  payee,

               iby_ext_bank_accounts    accts,

               fnd_currencies_vl        fc,

               HZ_PARTIES               bank,

               HZ_ORGANIZATION_PROFILES bankProfile,

               CE_BANK_BRANCHES_V       branch

         where uses.instrument_type = 'BANKACCOUNT'

           and payee.ext_payee_id = uses.ext_pmt_party_id

        and payee.payee_party_id = sup.party_id

           and uses.instrument_id = accts.ext_bank_account_id

           and fc.currency_code(+) = accts.currency_code

           AND sysdate between NVL(accts.start_date, sysdate) AND

               NVL(accts.end_date, sysdate)

           AND accts.bank_id = bank.party_id(+)

           AND accts.bank_id = bankProfile.party_id(+)

           AND accts.branch_id = branch.branch_party_id(+)

           and SYSDATE between TRUNC(bankProfile.effective_start_date(+)) and

               NVL(TRUNC(bankProfile.effective_end_date(+)), SYSDATE + 1)

               and nvl(payee.org_id,693) = 693

              -- and (trunc(uses.end_date) is null or trunc(uses.end_date) not between '01-DEC-2017' and '30-NOV-2021')

                )  "Account title",

                

                (  SELECT  max(trunc(accts.creation_date))

          from iby_pmt_instr_uses_all   uses,

               iby_external_payees_all  payee,

               iby_ext_bank_accounts    accts,

               fnd_currencies_vl        fc,

               HZ_PARTIES               bank,

               HZ_ORGANIZATION_PROFILES bankProfile,

               CE_BANK_BRANCHES_V       branch

         where uses.instrument_type = 'BANKACCOUNT'

           and payee.ext_payee_id = uses.ext_pmt_party_id

        and payee.payee_party_id = sup.party_id

           and uses.instrument_id = accts.ext_bank_account_id

           and fc.currency_code(+) = accts.currency_code

           AND sysdate between NVL(accts.start_date, sysdate) AND

               NVL(accts.end_date, sysdate)

           AND accts.bank_id = bank.party_id(+)

           AND accts.bank_id = bankProfile.party_id(+)

           AND accts.branch_id = branch.branch_party_id(+)

           and SYSDATE between TRUNC(bankProfile.effective_start_date(+)) and

               NVL(TRUNC(bankProfile.effective_end_date(+)), SYSDATE + 1)

               and nvl(payee.org_id,693) = 693

            --   and (trunc(uses.end_date) is null or trunc(uses.end_date) not between '01-DEC-2017' and '30-NOV-2021')

                )  "Account Opening date",

       null "Account Title",

       null "Account Opening date",

       ( 

       select hcp1.phone_number

from hz_contact_points  hcp1

where hcp1.owner_table_id(+) = ss.party_site_id

           and hcp1.CONTACT_POINT_TYPE(+) = 'PHONE'

           and hcp1.phone_line_type(+) = 'GEN'

           and hcp1.status(+) = 'A'

           and hcp1.owner_table_name(+) = 'HZ_PARTY_SITES'

           and hcp1.primary_flag(+) = 'Y'

) "MOBILE NUMBER",

*/

       ( 

       select hcp2.email_address

from apps.hz_contact_points  hcp2

where hcp2.owner_table_id(+) = ss.party_site_id

           and hcp2.CONTACT_POINT_TYPE(+) = 'EMAIL'

           and hcp2.status(+) = 'A'

           and hcp2.owner_table_name(+) = 'HZ_PARTY_SITES'

           and hcp2.primary_flag(+) = 'Y'

)  "Email"

/*       sup.vendor_type_lookup_code "Occupation/Services type",

       trunc(sup.last_update_date) "Last Updation date",

     

             

   (select user_name  from fnd_user  v where v.user_id = sup.created_by) "Vendor Openned By",

   

   

   (select count(b.invoice_id)  

   from ap_checks_all a, ap_invoice_payments_all b 

   where a.check_id = b.check_id

  -- and ( a.void_date is null or trunc(a.void_date) not between '01-DEC-2017' and '30-NOV-2021')

   and a.vendor_id = sup.vendor_id

   and a.vendor_site_id = ss.vendor_site_id and a.org_id = ss.org_id

   ) "Total Number of purchases", 

   (

    select sum(b.amount)  

    from ap_checks_all a, ap_invoice_payments_all b 

    where a.check_id = b.check_id

  -- and ( a.void_date is null or trunc(a.void_date) not between '01-DEC-2017' and '30-NOV-2021')

   and a.vendor_id = sup.vendor_id

   and a.vendor_site_id = ss.vendor_site_id and a.org_id = ss.org_id

   ) "Total Payment received",

 

  

   null "CAAML Status"*/

   

  FROM apps.ap_suppliers sup, apps.ap_supplier_sites_all ss

 WHERE sup.vendor_id = ss.vendor_id

 --and trunc(sup.creation_date) between '01-DEC-2017' and '30-NOV-2021'

-- and sup.vendor_name ='Jubilee Life insurance (Account # 0330000019231008)'

  and ss.org_id = 693

  order by sup.segment1 desc

  )

   

 

Bank, Bank Account, Cheque Book PLSQL Query - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/12/bank-bank-account-cheque-book-plsql.html

select a.bank_name,b.BANK_ACCOUNT_NAME, b.BANK_ACCOUNT_NUM, b.CURRENCY_CODE,  b.IBAN_NUMBER, b.EFT_USER_NUM,
c.SEGMENT1, c.SEGMENT9 , c.SEGMENT10, c.SEGMENT2, c.SEGMENT3, c.SEGMENT4, c.SEGMENT5, c.SEGMENT6,c.SEGMENT7,c.SEGMENT8, c.SEGMENT11
,
(SELECT gl_flexfields_pkg.get_description_sql

(
50232388,--- chart of account id
5,----- Position of segment
segment3 ---- Segment value
)

FROM gl_code_combinations g where g.CODE_COMBINATION_ID = c.CODE_COMBINATION_ID ) Description
,d.PAYMENT_DOCUMENT_NAME , d.PAPER_STOCK_TYPE , d.FIRST_AVAILABLE_DOCUMENT_NUM
from ce_banks_v a,   ce_bank_accounts b , gl_code_combinations c  ,ce_payment_documents d
where  a.bank_party_id = b.BANK_ID
and b.ASSET_CODE_COMBINATION_ID = c.CODE_COMBINATION_ID
and b.BANK_ACCOUNT_ID = d.INTERNAL_BANK_ACCOUNT_ID
--and trunc(b.CREATION_DATE) =  '13-DEC-2019'

--order by c.SEGMENT3 asc

Work around for Cross Currency Payments - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/10/work-around-for-cross-currency-payments.html

1. Make Invoice in EUR.

2. Create dummy EUR Payment Clearing Account and do Payment.

3. Do a Fund Transfer from PKR to EUR.

4. Source Bank Account will be PKR and Destination Bank Account will be EUR.

Below are the Entries generated after above scenario: