Prepayment Cleared & Reconciled - Refunded by Supplier in the next month

 https://aporaclepayables.blogspot.com/2025/08/prepayment-refunded-by-supplier.html

P2P with Retention and Retetnion Release

 https://aporaclepayables.blogspot.com/2025/08/p2p-with-retention-and-retetnion-release.html

Record of advance without any Cash Outflow and recovery for the same

 https://aporaclepayables.blogspot.com/2025/08/we-are-main-contractor-and-have-engaged.html

We are the main contractor and have engaged a subcontractor to work for our client. Normally, we would pay an advance to the subcontractor. But in this case, the client has directly paid the advance to the subcontractor on our behalf. From our accounting perspective as the main contractor, how should we record this transaction

How Petty Cash workround works in AP

 https://aporaclepayables.blogspot.com/2025/05/how-petty-cash-workround-works-in-ap.html

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:

Warning control in Payables Module if the invoice date is 3 months older than GL date or vice versa - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/08/warning-control-in-payables-module-if.html

When New Block Instance
LINE_SUM_FOLDER

Condition:
abs(Months_between(:INV_SUM_FOLDER.INVOICE_DATE,:INV_SUM_FOLDER.GL_DATE)) > 3

Warning
Message: Number of Months between Invoice & GL Date Exceeds 3 Months.


OR, You can put Error on Header. So user cannot proceed with the Invoice.


When Validate Record
INV_SUM_FOLDER

Condition:
abs(Months_between(:INV_SUM_FOLDER.INVOICE_DATE,:INV_SUM_FOLDER.GL_DATE)) > 3

Error
Message: Number of Months between Invoice & GL Date Exceeds 3 Months.

Inconsistent Quantity Info Error on Invoice (Inconsistent quantity, price, amount combination) - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/inconsistent-quantity-info-error-on.html

Inconsistent Quantity Info Error on Invoice (Inconsistent quantity, price, amount combination) - Oracle EBS R12


 Unit Price * Quantity = Amount  ( Rounding Issue)

Invoice Stucked on Needs Revalidation status. Not allowing us to Validate or Cancel the same - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/invoice-stucked-on-needs-revalidation.html

Match status flag on posted invoice and self assessed tax distributions is not 'A'. Such invoices will be picked by Invoice Validation Program though invoices are shown as 'Validated' / 'Needs Revalidation' on invoice Workbench. Further if match status flag is 'N' on posted distributions, then cancellation might fail with "AP_UNSUCCESSFUL_CANCELLATION" message.

You can update the match_status_flag as 'A'

How to make Bank Account Transfers from Oracle AP - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/bank-account-transfers-from-ap-oracle.html


How you can make Bank Account Transfers through Oracle AP if Cash Management is not Implemented.

USD TO PKR

PKR TO USD

Expense of Foreign Currency and Payment will be done on Exchange of Currency (No Foreign Currency Bank Account) - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/foreign-currency-expense-on-functional.html

If you don't have Foreign Currency Bank Account and you want to book Expense of Foreign Currency and Payment will be done on Exchange of Currency. Then the Payment Process will be as follows:

Invoice and Payment Exchange Rates are same in my case.
Exchange Rate = 150

		Entered DR	Entered CR	Accounted DR	Accounted CR
Foreign Currency Invoice	Expense	5000		750000
	Liability		5000		750000
Foreign Currency Payment	Liability	5000		750000
	Foreign Currency Clearing Bank Account		5000		750000
Functional Currency JV	Foreign Currency Clearing Bank Account			750000
	Functional Currency Bank Account				750000