AP Invoice Distributions PLSQL QUERY (Item Line with WHT Deduction)

http://aporaclepayables.blogspot.com/2017/06/select-g.html

select g.DOC_SEQUENCE_VALUE APN_No,
       g.GL_DATE HEADER_GL_DATE,
       d.ACCOUNTING_DATE Distribution_GL_DATE,
       b.VENDOR_NAME,
       d.DESCRIPTION,
       d.line_type_lookup_code,
       -- d.invoice_distribution_id,
       -- d.awt_related_id,
       NVL(d.base_amount, d.amount) Amount,
       --  d.awt_group_id,
       -- d.awt_tax_rate_id,
       --  L.PAY_AWT_GROUP_ID,
       --  L.AWT_GROUP_ID,
       (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.invoice_id = d.invoice_id) WHT,
       /*    (select tr.tax_rate
                 from ap_awt_tax_rates_all tr
                where tr.tax_rate_id = d.awt_tax_rate_id) TAX_RATE,*/
       /*     (select tr.Tax_Name
                 from ap_awt_tax_rates_all tr
                where tr.tax_rate_id = d.awt_tax_rate_id) TAX_name,*/
       (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 nvl(d2.BASE_AMOUNT, d2.amount)
          from ap_invoice_distributions_all d2
         where d2.invoice_distribution_id = d.awt_related_id
           and d2.line_type_lookup_code = 'ITEM'
           and d2.invoice_id = d.invoice_id) ACTUAL_AMOUNT*/
  from ap_invoice_distributions_All d,
       AP_INVOICE_LINES_All         L,
       ap_invoices_all              g,
       ap_suppliers                 b
 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.ORG_ID = L.org_id
   and L.org_id = d.Org_id
   and b.VENDOR_ID = g.VENDOR_ID
   and g.org_id = 376
--and d.invoice_id = 94273 -- 3511993--3547454

/*    select sum(NVL(base_amount,amount)) from ap_invoice_distributions_all --4308
   where org_id=376
   and line_type_lookup_code = 'AWT'
*/

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

select --distinct g.invoice_id, g.AMOUNT_PAID     --4473  --706556268    8834072
 g.AMOUNT_PAID,
 g.DOC_SEQUENCE_VALUE APN_No,
 g.GL_DATE HEADER_GL_DATE,
 d.ACCOUNTING_DATE Distribution_GL_DATE,
 b.VENDOR_NAME,
 d.DESCRIPTION,
 d.line_type_lookup_code,
 gc.SEGMENT1||'-'||gc.SEGMENT2||'-'||gc.SEGMENT3||'-'||gc.SEGMENT4||'-'||gc.SEGMENT5||'-'||gc.SEGMENT6||'-'||gc.SEGMENT7||'-'||gc.SEGMENT8 Account,
 -- d.invoice_distribution_id,
 -- d.awt_related_id,
 NVL(d.base_amount, d.amount) Amount,
 --  d.awt_group_id,
 -- d.awt_tax_rate_id,
 --  L.PAY_AWT_GROUP_ID,
 --  L.AWT_GROUP_ID,
 (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.invoice_id = d.invoice_id) WHT,
 /*    (select tr.tax_rate
                  from ap_awt_tax_rates_all tr
                 where tr.tax_rate_id = d.awt_tax_rate_id) TAX_RATE,*/
 /*     (select tr.Tax_Name
                  from ap_awt_tax_rates_all tr
                 where tr.tax_rate_id = d.awt_tax_rate_id) TAX_name,*/
 (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 nvl(d2.BASE_AMOUNT, d2.amount)
          from ap_invoice_distributions_all d2
         where d2.invoice_distribution_id = d.awt_related_id
           and d2.line_type_lookup_code = 'ITEM'
           and d2.invoice_id = d.invoice_id) ACTUAL_AMOUNT*/

  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 d.ACCOUNTING_EVENT_ID = c.ACCOUNTING_EVENT_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 = 376
   and c.REVERSAL_FLAG = 'N'
   and trunc(e.CLEARED_DATE) between '01-DEC-2016' and '31-DEC-2017'
--and g.INVOICE_ID=115693
--  and g.GL_DATE between '01-JUN-2017' and '30-JUN-2017'
--and d.invoice_id = 94273 -- 3511993--3547454

/*    select sum(NVL(base_amount,amount)) from ap_invoice_distributions_all --4308
   where org_id=376
   and line_type_lookup_code = 'AWT'
*/

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

  select NVL(n.base_amount, n.amount) Amount
          from AP_INVOICE_PAYMENTS_ALL      k,
         ap_invoice_distributions_all n --778
        ,
         ap_checks_all                x
         where k.INVOICE_ID = n.INVOICE_ID
           and k.CHECK_ID = x.CHECK_ID
           and k.org_id = x.org_id
           and k.org_id = n.org_id
           and k.ORG_ID = 376 --108478
           and k.REVERSAL_FLAG = 'N'
             
           and x.CLEARED_DATE between '01-DEC-2016' and '31-DEC-2017'
              --and k.INVOICE_ID = 115693
           and n.line_type_lookup_code = 'AWT'