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