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

  )