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