Capturing withholding Tax deduction before validation of Invoice

https://aporaclepayables.blogspot.com/2018/11/capturing-withholding-tax-deduction.html

select

INVOICE_ID ,
null INVOICE_DISTRIBUTION_ID ,
null TAX_NAME,
  sum(AMT) AMT,
null GL_COSTCNTR,
null GL_LOC,
null GL_LOB,
null GL_PROD,
  sum(round(PROJ_TAX))   PROJ_TAX ,
   sum(round(PROJ_TAX))   TAX_SUM ,
  sum(CNT) EXCNT ,
RATE_PROJ TAX_RATE,
SECTION
FROM (


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


select
INVOICE_ID ,
null INVOICE_DISTRIBUTION_ID ,
TAX_NAME,
RATE_PROJ,
sum(AMT) AMT,
null GL_COSTCNTR,
null GL_LOC,
null GL_LOB,
null GL_PROD,
sum(PROJ_TAX) PROJ_TAX,
sum(CNT) CNT,
null TAX_RATE,
SECTION

from
(

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

SELECT
g.GROUP_ID
, AIDA.INVOICE_ID
          , null Invoice_Distribution_Id
          ,  g.NAME  TAX_NAME
          , ( AIDA.AMOUNT)  AMT
/*          ,(SELECT FVT.DESCRIPTION
                 FROM   APPLSYS.FND_FLEX_VALUES_TL FVT,
APPLSYS.FND_FLEX_VALUES FFV
                 WHERE  FFV.FLEX_VALUE_SET_ID =   1016491  --DOM COST
CENTRE VS'1014611'
                 AND    FFV.FLEX_VALUE_ID = FVT.FLEX_VALUE_ID
                 AND    FVT.FLEX_VALUE_MEANING = gcc.SEGMENT3
            ) GL_COSTCNTR
           ,(SELECT FVT.DESCRIPTION
                 FROM   APPLSYS.FND_FLEX_VALUES_TL FVT,
APPLSYS.FND_FLEX_VALUES FFV
                 WHERE  FFV.FLEX_VALUE_SET_ID =   1016490 --DOM LOCATION
VS'1014608'
                 AND    FFV.FLEX_VALUE_ID = FVT.FLEX_VALUE_ID
                 AND    FVT.FLEX_VALUE_MEANING = gcc.SEGMENT2
            ) GL_LOC
           ,(SELECT FVT.DESCRIPTION
                 FROM   APPLSYS.FND_FLEX_VALUES_TL FVT,
APPLSYS.FND_FLEX_VALUES FFV
                 WHERE  FFV.FLEX_VALUE_SET_ID =   1016487  --DOM COMPANY
VALUE SET '1014609'
                 AND    FFV.FLEX_VALUE_ID = FVT.FLEX_VALUE_ID
                 AND    FVT.FLEX_VALUE_MEANING = gcc.SEGMENT1
            ) GL_LOB
           ,(SELECT FVT.DESCRIPTION
                 FROM   APPLSYS.FND_FLEX_VALUES_TL FVT,
APPLSYS.FND_FLEX_VALUES FFV
                 WHERE  FFV.FLEX_VALUE_SET_ID =   1016494  --DOM PRODUCT
VS'1014610'
                 AND    FFV.FLEX_VALUE_ID = FVT.FLEX_VALUE_ID
                 AND    FVT.FLEX_VALUE_MEANING = gcc.SEGMENT6
            ) GL_PROD*/
             , CASE WHEN ATC.AWT_PERIOD_TYPE='AWT - Property' THEN
             
((least(AIDA.AMOUNT,nVL(ATR.END_AMOUNT,AIDA.AMOUNT))-ATR.START_AMOUNT)*ATR.TAX_RATE/100)
              ELSE
              case  when nvl(EXC.CNT,0) >= 1  then  0
                      when  nvl(EXC.CNT,0) >=  0  then   
((AIDA.AMOUNT*ATR.TAX_RATE/100) )
                        else  0
                      end
              END PROJ_TAX
            , nvl(EXC.CNT,0)  CNT
            ,ATC.ATTRIBUTE4  TAX_RATE
            ,ATC.ATTRIBUTE1||'-'||ATC.ATTRIBUTE2||'-'||ATC.ATTRIBUTE3 
SECTION
            ,ATR.TAX_RATE  RATE_PROJ
FROM
AP_AWT_GROUPS g
,AP_INVOICE_DISTRIBUTIONS_ALL AIDA
        ,AP_AWT_GROUP_TAXES_ALL    ATG
        ,AP_TAX_CODES_ALL          ATC
        ,AP_AWT_TAX_RATES_ALL      ATR
        ,GL_CODE_COMBINATIONS      GCC
        ,(
           select  1 CNT, x.tax_name
           from    AP_AWT_TAX_RATES_ALL x
           where   x.vendor_id=   ( select aia.vendor_id from 
ap_invoices_all  aia  where aia.invoice_id = &P_INVOICE_ID  )
           /*
VENDOR_ID 45
INVOICE_DATE  12/15/2016
ORG_ID  82
INVOICE_ID  11463

*/
              and  /*'12-JUN-2016'*/ ( select trunc(aia.gl_date) from 
ap_invoices_all  aia  where aia.invoice_id = &P_INVOICE_ID  )
/*'15-DEC-2017'*/  BETWEEN trunc(x.start_date) and  trunc( x.end_date)
              /*
VENDOR_ID 45
INVOICE_DATE  12/15/2016
ORG_ID  82
INVOICE_ID  11463

*/
              and  x.org_id =  /*865 */  ( select aia.org_id from 
ap_invoices_all  aia  where aia.invoice_id = &P_INVOICE_ID  )
              /*
VENDOR_ID 45
INVOICE_DATE  12/15/2016
ORG_ID  82
INVOICE_ID  11463

*/
         ) exc
WHERE
g.GROUP_ID = atg.GROUP_ID
and AIDA.PAY_AWT_GROUP_ID = ATG.GROUP_ID
AND    AIDA.ORG_ID           = ATG.ORG_ID
AND    ATG.TAX_NAME          = ATC.NAME
AND    AIDA.ORG_ID           = ATC.ORG_ID
AND    ATC.NAME              = ATR.TAX_NAME
AND    AIDA.ORG_ID           = ATR.ORG_ID
AND    ATR.RATE_TYPE         = 'STANDARD'
AND    AIDA.INVOICE_ID       =    &P_INVOICE_ID --3450660 --
/*
VENDOR_ID 45
INVOICE_DATE  12/15/2016
ORG_ID  82
INVOICE_ID  11463

*/

AND    NVL(AIDA.REVERSAL_FLAG,'N')    <> 'Y'
AND    ATR.END_DATE IS NULL
AND    AIDA.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND    ATG.TAX_NAME = EXC.TAX_NAME(+)

-----------------------------------------------------------
)

GROUP  BY   INVOICE_ID ,TAX_NAME , RATE_PROJ,SECTION



-------------------------------------------------------------------
) Q1

GROUP BY
--INVOICE_ID,Invoice_Distribution_Id,TAX_NAME,AMT,GL_COSTCNTR,GL_LOC,GL_LOB,GL_PROD,PROJ_TAX,TAX_RATE,SECTION,cnt
INVOICE_ID ,
--TAX_NAME,
RATE_PROJ ,
SECTION
ORDER  BY 1,6

Joining AP_CHECKS_ALL with XLA tables

https://aporaclepayables.blogspot.com/2018/11/select-distinct-select-count-gcc.html

SELECT DISTINCT

(

select   count(*) --  gcc.segmentx
from
xla_ae_headers a, xla_ae_lines b, gl_code_combinations c, xla_events d 
,xla.xla_transaction_entities ent
where  ent.source_id_int_1 =  aca.check_id   --Joining here
and  a.AE_HEADER_ID=b.AE_HEADER_ID
and a.LEDGER_ID=b.LEDGER_ID
and a.APPLICATION_ID=b.APPLICATION_ID
and b.CODE_COMBINATION_ID=c.CODE_COMBINATION_ID
and a.EVENT_ID=d.EVENT_ID
and d.APPLICATION_ID=a.APPLICATION_ID
and d.APPLICATION_ID=b.APPLICATION_ID
and a.ENTITY_ID=ent.entity_id
and d.ENTITY_ID=ent.entity_id
and ent.application_id=a.APPLICATION_ID
and a.APPLICATION_ID=200
and b.APPLICATION_ID=200
and b.ACCOUNTING_CLASS_CODE ='CASH'
and a.je_category_name = '&P_JE_CATEGORY'
)   Payment_Account  ,

AIA.GL_DATE,
                AIA.DOC_SEQUENCE_VALUE "AP Number",
                 aca.DOC_SEQUENCE_VALUE "PV Number",
                 --asa.segment1 "Supplier Number"/*,
                 asa.vendor_name "Supplier Name",
                 aia.invoice_num "Invoice Number",
                 aia.INVOICE_DATE "Invoice Date",
                 aia.invoice_amount INV_AMT,
                 (select sum(ln.AMOUNT)
                    from ap_invoice_lines_all ln, gl_code_combinations 
gcc
                   where ln.INVOICE_ID = aia.INVOICE_ID
                     and gcc.SEGMENT3 like '1049%'
                     and ln.DEFAULT_DIST_CCID = gcc.CODE_COMBINATION_ID) 
Sales_Tax_Charged,
                 null "Total Amount",
                 null "Income Tax Withholding",
                 aia.PAYMENT_REASON_COMMENTS "Account Head",

                 NULL                  "Sales Tax Withholding",
                 AIPA.AMOUNT           "Payment Amount",
                 ASA.NUM_1099          "NTN No",
               --  ppf.NATIONAL_IDENTIFIER NIC_NO,
  (select ppf.NATIONAL_IDENTIFIER from
                    per_people_f          ppf
                    where ppf.PERSON_ID =asa.EMPLOYEE_ID
                    and rownum =1) NIC_NO,
                 asa.ATTRIBUTE1        "Individual / Company",
                 aia.DESCRIPTION       "Description",
                 aca.CHECK_DATE        "Payment Date",
                 aca.CHECK_NUMBER,
                 aca.BANK_ACCOUNT_NAME "Bank" ,
                 aia.invoice_id ,
         '.'||gcc.SEGMENT3||'.'  LIAB_ACC,

   (
    select AAA.DESCRIPTION
       from fnd_flex_values_vl AAA
     where   AAA.FLEX_VALUE = gcc.SEGMENT3
     and    AAA.flex_value_set_id =  1016491
     )   LIAB_ACC_DESC,
     aia.INVOICE_CURRENCY_CODE,
     decode(aia.PAYMENT_STATUS_FLAG,'N','UNPAID','Y','PAID','P','PARTIAL' 
)   PAYMENT_STATUS
   FROM ap_checks_all           aca,
        ap_invoice_payments_all aipa,
        ap_invoices_all         aia,
        ap_suppliers            asa,
          gl_code_combinations  gcc

  WHERE aca.check_id = aipa.check_id
    AND aipa.invoice_id = aia.invoice_id
    AND aia.vendor_id = asa.vendor_id
    and aia.CANCELLED_DATE is null
    and aca.STATUS_LOOKUP_CODE != 'VOIDED'
      and  aia.ACCTS_PAY_CODE_COMBINATION_ID  =  gcc.CODE_COMBINATION_ID
       --AND aipa.ACCRUAL_POSTED_FLAG = 'N'
       --AND POSTED_FLAG = 'N'
       ------------------------
       AND TRUNC (aca.CHECK_DATE) BETWEEN   
nvl(:P_frm_ch_date,aca.CHECK_DATE) AND nvl(:P_to_ch_date,aca.CHECK_DATE)

AND TRUNC (aia.GL_DATE) BETWEEN nvl(:P_frm_INv_date,aia.GL_DATE) AND 
nvl(:P_to_INV_date,aia.GL_DATE)
          and     asa.segment1 = nvl(:P_from_vendr,asa.segment1 )
and  (  :P_PS =  'A'  or  :P_PS = aia.PAYMENT_STATUS_FLAG   )
union all

select
null,
aia.GL_DATE,
aia.DOC_SEQUENCE_VALUE"AP Number",
NVL(null,0) "PV Number",
s.VENDOR_NAME "Supplier Name",
aia.INVOICE_NUM "Invoice Number",
aia.INVOICE_DATE "Invoice Date"
,aia.INVOICE_AMOUNT INV_AMT
,(select sum(ln.AMOUNT)
                    from ap_invoice_lines_all ln, gl_code_combinations 
gcc
                   where ln.INVOICE_ID = aia.INVOICE_ID
                     and gcc.SEGMENT3 like '1049%'
                     and ln.DEFAULT_DIST_CCID = gcc.CODE_COMBINATION_ID) 
Sales_Tax_Charged
                     ,null "Total Amount",
                 null "Income Tax Withholding",
                 aia.PAYMENT_REASON_COMMENTS "Account Head"
                 ,NULL                  "Sales Tax Withholding",
                 NVL(NULL,0)           "Payment Amount",
                 S.NUM_1099          "NTN No",
               --  ppf.NATIONAL_IDENTIFIER NIC_NO,

  (select ppf.NATIONAL_IDENTIFIER from
                    per_people_f          ppf
                    where ppf.PERSON_ID =s.EMPLOYEE_ID
                    and rownum =1) NIC_NO,


               s.ATTRIBUTE1        "Individual / Company",
                 aia.DESCRIPTION       "Description"

                 ,null       "Payment Date",
                nvL( null,0) CHECK_NUMBER,
                 null "Bank" ,
                 aia.invoice_id ,
              '.'||gcc.SEGMENT3||'.'    LIAB_ACC,


   (
    select AAA.DESCRIPTION
       from fnd_flex_values_vl AAA
     where   AAA.FLEX_VALUE = gcc.SEGMENT3
     and    AAA.flex_value_set_id =  1016491
     )   LIAB_ACC_DESC,
     aia.INVOICE_CURRENCY_CODE,
     decode(aia.PAYMENT_STATUS_FLAG,'N','UNPAID','Y','PAID','P','PARTIAL' 
)   PAYMENT_STATUS
from

ap_invoices_all aia
,ap_suppliers s
--,ap_invoice_payments_all aipa
,  gl_code_combinations  gcc

where

s.VENDOR_ID = aia.VENDOR_ID

--and aia.AMOUNT_PAID is null
and NVL(aia.AMOUNT_PAID,0) = 0
   and  aia.ACCTS_PAY_CODE_COMBINATION_ID  =  gcc.CODE_COMBINATION_ID
--and ppf.PERSON_ID = s.EMPLOYEE_ID
AND TRUNC (aia.GL_DATE) BETWEEN nvl(:P_frm_INv_date,aia.GL_DATE) AND 
nvl(:P_to_INV_date,aia.GL_DATE)
          and     s.segment1 = nvl(:P_from_vendr,s.segment1 )
and  (  :P_PS =  'A'  or  :P_PS = aia.PAYMENT_STATUS_FLAG   )