PLSQL Query to get WHT and GST Amounts separately on Each Item Line

https://aporaclepayables.blogspot.com/2018/06/plsql-query-to-get-wht-and-gst-amounts.html

select g.DOC_SEQUENCE_VALUE    APN_No,
       g.INVOICE_NUM,
       g.INVOICE_DATE,
       d.ACCOUNTING_DATE       Distribution_GL_DATE,
       d.description,
       e.cleared_date,
       b.VENDOR_NAME,
       d.line_type_lookup_code Line_Type,
   
       (case
         when d.ATTRIBUTE3 is null then
          gc.SEGMENT1 || '-' || gc.SEGMENT2 || '-' || gc.SEGMENT3 || '-' ||
          gc.SEGMENT4 || '-' || gc.SEGMENT5 || '-' || gc.SEGMENT6 || '-' ||
          gc.SEGMENT7 || '-' || gc.SEGMENT8
         when d.ATTRIBUTE3 is not null then
          (select gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
                  gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
                  gcc.segment7 || '-' || gcc.segment8
             from gl_code_combinations gcc
            where gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
                  gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' ||
                  gcc.segment7 || '-' || gcc.segment8 = d.ATTRIBUTE3)
       end
   
       ) Account,
   
       NVL(d.base_amount, d.amount) Amount,

------Please Modify the below Section according to your Tax Setup (For my Case)----------

       (select SUM(NVL(d2.base_amount, d2.amount))
          from ap_invoice_distributions_all d2
         where d2.awt_related_id = d.invoice_distribution_id
           and d2.line_type_lookup_code = 'AWT'
           and d2.DESCRIPTION <> 'GST-SRO98'
           and d2.invoice_id = d.invoice_id) WHT,
   
       (select SUM(NVL(d2.base_amount, d2.amount))
          from ap_invoice_distributions_all d2
         where d2.awt_related_id = d.invoice_distribution_id
           and d2.line_type_lookup_code = 'AWT'
           and d2.DESCRIPTION = 'GST-SRO98'
           and d2.invoice_id = d.invoice_id) GST,

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

       /*
       (select ''''||GRP.NAME
       from AP_AWT_GROUPS GRP
       where GRP.GROUP_ID = D.AWT_ORIGIN_GROUP_ID) AWT_GROUP,
       */
       (select '''' || grp2.name
          from AP_AWT_GROUPS GRP2
         where grp2.group_id = d.pay_awt_group_id) ITEM_GROUP,
       
         (select  sum(gpr4.TAX_RATE)
          from AP_AWT_GROUPS GRP2, AP_AWT_GROUP_TAXES_ALL GPR3,   AP_AWT_TAX_RATES_all gpr4
         where grp2.GROUP_ID = gpr3.GROUP_ID
         and gpr3.TAX_NAME=gpr4.TAX_NAME
         and gpr4.ORG_ID =82
         and gpr4.RATE_TYPE = 'STANDARD'
         and gpr3.ORG_ID=82
         and gpr4.END_DATE is null
         and grp2.group_id = d.pay_awt_group_id
         and gpr3.GROUP_ID=d.PAY_AWT_GROUP_ID
        -- and grp2.Description like '%GST%'
        ) Tax_Rate,
       
        -- select * from AP_AWT_TAX_RATES_ALL
     
        NVL(d.base_amount, d.amount) +  NVL((select SUM(NVL(d2.base_amount, d2.amount))
          from ap_invoice_distributions_all d2
         where d2.awt_related_id = d.invoice_distribution_id
           and d2.line_type_lookup_code = 'AWT'
           and d2.DESCRIPTION <> 'GST-SRO98'
           and d2.invoice_id = d.invoice_id),0) +   NVL((select SUM(NVL(d2.base_amount, d2.amount))
          from ap_invoice_distributions_all d2
         where d2.awt_related_id = d.invoice_distribution_id
           and d2.line_type_lookup_code = 'AWT'
           and d2.DESCRIPTION = 'GST-SRO98'
           and d2.invoice_id = d.invoice_id),0) Amount_Paid_Each_Item,
         
       g.AMOUNT_PAID Total_Amount_Paid,
       g.GL_DATE HEADER_GL_DATE

  from ap_invoice_distributions_All d,
       AP_INVOICE_LINES_All         L,
       ap_invoices_all              g,
       ap_suppliers                 b,
       AP_INVOICE_PAYMENTS_ALL      c,
       ap_checks_all                e,
       gl_code_combinations         gc
 WHERE D.INVOICE_ID = L.INVOICE_ID
   AND D.INVOICE_LINE_NUMBER = L.LINE_NUMBER
   and g.INVOICE_ID = L.INVOICE_ID
   and g.INVOICE_ID = d.INVOICE_ID
   and g.INVOICE_ID = c.INVOICE_ID
   and l.INVOICE_ID = c.INVOICE_ID
   and d.INVOICE_ID = c.INVOICE_ID
   and e.CHECK_ID = c.CHECK_ID
   and gc.CODE_COMBINATION_ID = d.DIST_CODE_COMBINATION_ID
   
   and g.ORG_ID = L.org_id
   and L.org_id = d.Org_id
   and l.ORG_ID = c.ORG_ID
   and c.ORG_ID = g.org_id
   and e.ORG_ID = c.ORG_ID
   and b.VENDOR_ID = g.VENDOR_ID
   and g.org_id = 82
   and c.REVERSAL_FLAG = 'N'
   and trunc(e.CLEARED_DATE) between NVL('01-JUL-14', e.CLEARED_DATE) and
       NVL('30-JUN-18', e.CLEARED_DATE)
      -- and b.VENDOR_NAME  = 'Distributors'
    --   and g.DOC_SEQUENCE_VALUE =16011198
       and d.line_type_lookup_code = 'ITEM'
--and g.INVOICE_ID=115693
--  and g.GL_DATE between '01-JUN-2017' and '30-JUN-2017'
--and d.invoice_id = 94273 -- 3511993--3547454

 order by g.DOC_SEQUENCE_VALUE

How to Change AP Invoice Number

https://aporaclepayables.blogspot.com/2018/06/ap-invoice-number-is-saved-in-following.html


Please apply it on TEST Instance first.

AP Invoice Number is stored in the below tables till Payment Accounting. Please check your Invoice_Id in all tables below


select * from AP_INVOICES_ALL a WHERE invoice_id = '149806' --Invoice_Num


select * from AP_DOCUMENTS_PAYABLE where calling_app_doc_unique_ref2 = '149806'  --Calling_App_Doc_Ref_Number (If AP_INVOICES_ALL  table is updated for Invoice_Num. It will automatically changed in this table)
   

select * from ZX_LINES_DET_FACTORS where trx_id = '149806' --TRX_NUMBER
 
      
SELECT * FROM IBY_DOCS_PAYABLE_ALL idp WHERE idp.CALLING_APP_DOC_UNIQUE_REF2 = '149806' --Calling_App_Doc_Ref_Number

                
select * from xla_transaction_entities_upg xte where xte.SOURCE_ID_INT_1 = '149806'  --Transaction_Number
   
                     
select * from xla_ae_headers where ae_header_id = 466374 --If found in Description (Please search here for ae_header_id)
 
                               
select * from gl_je_headers where je_header_id = 560651  --If found in Description (Please search here for je_header_id)

Fully paid prepayment showing “Unpaid” Status

https://aporaclepayables.blogspot.com/2018/06/fully-paid-prepayment-showing-unpaid.html

SOLUTION

Run the following statement to find the issue data
select * from AP_INVOICES_ALL where invoice_id =&invoice_id

If the invoice is fully paid, the status PAYMENT_STATUS_FLAG should be Y.

For implementing the solution, please execute the following steps:

Step 1. Ensure that you have backup your system before applying the recommended solution.

Step 2. Run the following scripts in a TEST environment first:

A. create table SR_XXX_aia as select * from ap_invoices_all where invoice_id =&invoice_id;

B. update ap_invoices_all set payment_status_flag = ‘Y’ where invoice_id =&invoice_id;


Step 3. If the result are satisfied, issue a commit.

Commit;

Step 4. Confirm that the data is corrected when viewed in the Oracle Applications.