PLSQL Query for Tax Detail with Certificate - Oracle EBS R12

https://aporaclepayables.blogspot.com/2018/08/plsql-query-for-tax-detail-with.html

PLSQL Query for Tax Detail with Certificate


--select * from hr_operating_units

SELECT
null ID_TYPE
,vnd.vat_registration_num SUPNTN
,null CNIC
,null PASSPORT
,null MOBILE
,vnd.vendor_name
,null BUSINESSNAME
,aidl1.DIST_CODE_COMBINATION_ID
,vnds.address_line1||' '||vnds.address_line2||' '||vnds.address_line3||' '||vnds.city ADDRESS
,atg.description TAXNAME
,null PAYMENTSECTION
,to_char(aca.cleared_date,'yyyymmdd') CLEAREDDATE
,SUM (nvl (aidl1.base_amount, aidl1.amount)) TAXABLE_AMOUNT,
aidl1.Description Line_Description
,null TAXRATE
,SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))) TAX_AMOUNT

,case when nvl(SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))),0)>0 then
'Y'
 ELSE
'N' END TAX_DEDUCTED

,case when nvl(SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))),0)>0 then
'R'
 ELSE
'NR' END R_NR

,SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))) TAX_DEPOSITED

,null DEPOSITDATE
,null CPR_NO
,null PROVISION

/*,APTAX.CERTIFICATE_NUMBER CERTIFICATE_NO
,APTAX.START_DATE CERTIFICATE_DATE
,APTAX.ATTRIBUTE1 CERTIFICATE_AUTHORITY
*/
,(select CERTIFICATE_NUMBER from AP_AWT_TAX_RATES_ALL
  where
  -- (start_date between :p_date_from and :p_date_to or end_date between :p_date_from and :p_date_to)
  org_id  =376--:P_ORG_ID   --  added by IACS ( IMRAN 27-july-2015 )
  and aca.cleared_date BETWEEN START_DATE AND NVL(END_DATE,'31-DEC-2099')
  and vendor_id=aia.vendor_id and vendor_site_id=aia.vendor_site_id and RATE_TYPE='CERTIFICATE'
  and tax_name=atg.tax_name) CERTIFICATE_NO

,(select START_DATE from AP_AWT_TAX_RATES_ALL
  where
  -- (start_date between :p_date_from and :p_date_to or end_date between :p_date_from and :p_date_to)
   org_id  =376--:P_ORG_ID   --  added by IACS ( IMRAN 27-july-2015 )
  and  aca.cleared_date BETWEEN START_DATE AND NVL(END_DATE,'31-DEC-2099')
  and vendor_id=aia.vendor_id and vendor_site_id=aia.vendor_site_id and RATE_TYPE='CERTIFICATE'
  and tax_name=atg.tax_name) CERTIFICATE_DATE

,(select ATTRIBUTE1 from AP_AWT_TAX_RATES_ALL
  where
  -- (start_date between :p_date_from and :p_date_to or end_date between :p_date_from and :p_date_to)
   org_id  =376--:P_ORG_ID   --  added by IACS ( IMRAN 27-july-2015 )
   AND aca.cleared_date BETWEEN START_DATE AND NVL(END_DATE,'31-DEC-2099')
  and vendor_id=aia.vendor_id and vendor_site_id=aia.vendor_site_id and RATE_TYPE='CERTIFICATE'
  and tax_name=atg.tax_name) CERTIFICATE_AUTHORITY
,atg.GROUP_NAME TAXCD
,aca.doc_sequence_value inv_apv_num
,aia.DOC_SEQUENCE_VALUE inv_apn_num
,aca.bank_account_name BANK
,aidl.WITHHOLDING_TAX_CODE_ID, atg.TAX_ID, -- ADDED ON 10-JUN-15
gcc.SEGMENT4 Account,
gl_flexfields_pkg.get_description_sql 
                                      (gcc.CHART_OF_ACCOUNTS_ID ,--- chart of account id 
                                       4,----- Position of segment 
                                       gcc.segment4 ---- Segment value 
                                      )  ACC_DESC
/*,(selectgcc.SEGMENT4 from ap_invoice_distributions_all al ,gl_code_combinations gcc where gcc.CODE_COMBINATION_ID = al.DIST_CODE_COMBINATION_ID
and al.INVOICE_ID = aia.INVOICE_ID ) ACC*/
FROM ( select * from ap_invoice_distributions_all
       WHERE  org_id  =376/*:P_ORG_ID*/   and  NVL(WITHHOLDING_TAX_CODE_ID,-1) NOT IN (
                                                     SELECT TAX_ID
                                                     FROM Ap_Tax_Codes_All
                                                     WHERE org_id  = 376--:P_ORG_ID   --  added by IACS ( IMRAN 27-july-2015 )
                                                     AND DESCRIPTION LIKE 'GST-SRO98'
                                                   )
     ) aidl
,    ap_invoice_distributions_all aidl1
--,    ap_awt_groups atg
/*
,      (SELECT
      AG.GROUP_ID, AG.NAME GROUP_NAME, AG.DESCRIPTION, AGT.TAX_NAME
      FROM
      AP_AWT_GROUPS AG,
      AP_AWT_GROUP_TAXES_ALL AGT
      WHERE 1=1
      AND AG.GROUP_ID = AGT.GROUP_ID) atg*/ -- 10-JUN-14

,     (SELECT
      AG.GROUP_ID, AG.NAME GROUP_NAME, AG.DESCRIPTION, AGT.TAX_NAME, TCA.TAX_ID
      FROM
      AP_AWT_GROUPS AG,
      AP_AWT_GROUP_TAXES_ALL AGT,
      AP_TAX_CODES_ALL TCA
      WHERE 1=1
      AND  AGT.org_id  =376--:P_ORG_ID    --  added by IACS ( IMRAN 27-july-2015 )
      AND AG.GROUP_ID = AGT.GROUP_ID
      AND AGT.TAX_NAME = TCA.NAME
      --AND AG.NAME IN ('2E8PST0','2E8')
      ) atg


,    ap_invoices_all aia
,    ap_suppliers vnd
,    ap_supplier_sites_all vnds
,    ap_invoice_payments_all aipa
,    ap_checks_all aca
,gl_code_combinations gcc

/*,    ( select * from AP_AWT_TAX_RATES_ALL
       where (  :p_date_from   BETWEEN START_DATE AND END_DATE OR :p_date_to BETWEEN START_DATE AND END_DATE )
     ) aptax
*/
WHERE 1=1
 AND  aia.org_id  =376--:P_ORG_ID    --  added by IACS ( IMRAN 27-july-2015 )
     AND aidl1.invoice_distribution_id = aidl.awt_related_id
     AND aidl1.PAY_awt_group_id = atg.group_id -- PAY_AWT_GROUP_ID column is used in R12.2.4 and AWT_GROUP_ID column is used in R12.0.6
     AND aidl.WITHHOLDING_TAX_CODE_ID=atg.TAX_ID -- ADDED ON 10-JUN-15
     AND aia.invoice_id = aidl.invoice_id
     --and gcc.CODE_COMBINATION_ID=aidl.DIST_CODE_COMBINATION_ID
     and gcc.CODE_COMBINATION_ID=aidl1.DIST_CODE_COMBINATION_ID
     AND vnd.vendor_id = aia.vendor_id
     AND vnd.vendor_id = vnds.vendor_id
     AND aia.vendor_site_id = vnds.vendor_site_id
     AND aipa.invoice_id = aia.invoice_id
     AND aca.check_id = aipa.check_id
--     AND aia.vendor_id= aptax.vendor_id(+)
--     AND aia.vendor_site_id=aptax.vendor_site_id(+)
     AND aca.cleared_date IS NOT NULL
     AND (aipa.reversal_flag = 'N' OR aipa.reversal_flag is null)
     AND (aidl.reversal_flag = 'N' or aidl.reversal_flag is null)
     AND (aidl1.reversal_flag = 'N' or aidl1.reversal_flag is null)
    AND trunc(aca.cleared_date) BETWEEN '01-JUL-2016'and'30-JUN-2017 '--nvl (:p_date_from, aca.cleared_date) AND nvl (:p_date_to, aca.cleared_date)                               --  '01-MAR-2014'  and  '31-MAR-2014'    --
     --AND vnd.vendor_name BETWEEN nvl (:cf_from_vendor_dsp, vnd.vendor_name) AND nvl(:cf_to_vendor_dsp, vnd.vendor_name)
GROUP BY
         atg.GROUP_NAME
,        atg.tax_name
,        atg.description
,        vnd.vendor_name
,        vnd.segment1
,        vnds.address_line1
,        vnds.address_line2
,        vnds.address_line3
,        vnds.city
,        vnd.vat_registration_num
,        aia.doc_sequence_value
,        aca.doc_sequence_value
,        aca.bank_account_name
,        aia.invoice_currency_code
,        aca.cleared_date
,        aia.invoice_id
,        aia.vendor_id, aia.vendor_site_id,gcc.SEGMENT4, aidl1.DIST_CODE_COMBINATION_ID
,aidl.WITHHOLDING_TAX_CODE_ID, atg.TAX_ID -- ADDED ON 10-JUN-15
,aidl1.Description,gcc.CHART_OF_ACCOUNTS_ID
--,        APTAX.TAX_NAME, APTAX.START_DATE, APTAX.CERTIFICATE_NUMBER, APTAX.ATTRIBUTE1
ORDER BY
        TAXCD,
       aca.cleared_date,
       aia.doc_sequence_value

ASC

PLSQL Query for Withholding Tax Detail - Oracle EBS R12

https://aporaclepayables.blogspot.com/2018/08/plsql-query-for-withholding-tax-detail.html


PLSQL Query for Withholding Tax Detail 


SELECT aia.invoice_id,
       gcc.SEGMENT2 location
     
       ------------------------ Aggregating multiple invoice distribution lines using SUM/Decode Combination -----------------------------------------------------------------------------
     
      ,
       atg.description          Payment_Section,
       vnd.vat_registration_num TaxPayer_NTN,
       vnd.attribute13          TaxPayer_CNIC
     
       --,        SUM (aidl1.amount)  - SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (aidl.amount))) dist_net_amount_ent
       --,        SUM (nvl (aidl1.base_amount, aidl1.amount)) - SUM (decode (aidl.line_type_lookup_code, 'AWT', abs (nvl (aidl.base_amount, aidl.amount)))) dist_net_amount_fnc
     
       --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ,
       aidl.IRS_NO,NVL(TAX_TYPE,'No Tax type')TAX_TYPE,Section,Tax_exemption,
       --,        aia.invoice_currency_code inv_currency
     
       vnd.vendor_name Taxpayer_name
       --,        vnd.segment1 vendor_code
      ,
       vnds.city TaxPayer_City,
       vnds.address_line1 || ' ' || vnds.address_line2 || ' ' ||
       vnds.address_line3 TaxPayer_Address,
       vnds.attribute14 TaxPayer_Status,
       vnds.attribute15 TaxPayer_Business_Name
       --,        SUM (aidl1.amount) dist_amount_ent
      ,
       SUM(nvl(aidl1.base_amount, aidl1.amount)) Taxable_Amount,
       aidl1.DESCRIPTION Line_Description,
       --,        SUM (decode (aidl.line_type_lookup_code, 'AWT', abs(aidl.amount))) dist_tax_amount_ent
     
       SUM(decode(aidl.line_type_lookup_code,
                  'AWT',
                  abs(nvl(aidl.base_amount, aidl.amount)))) Tax_Amount,
       vnd.segment1 Supplier_No,
       aca.doc_sequence_value APV_NO,
       aca.cleared_date APV_POSTED_DATE,
       aia.doc_sequence_value APN_NO,
       atg.NAME Tax_Code,
       aca.CURRENCY_CODE
       --,        atg.description Tax_Group_Desc
      ,
       aca.bank_account_name,
       U.USER_NAME,
       gcc.SEGMENT4 Account,
gl_flexfields_pkg.get_description_sql 
                                      (gcc.CHART_OF_ACCOUNTS_ID ,--- chart of account id 
                                       4,----- Position of segment 
                                       gcc.segment4 ---- Segment value 
                                      )  ACC_DESC
  FROM -- ap_invoice_distributions_all aidl                                                               -- used for tax grouping     --- Marked on 15 September 2010 By Muhammad Raheem (Saad)
       -- Distribution line calculating tax on sables basis, that create multiple TAX lines so these lines where sum to make single TAX line
        (SELECT sum(amount) amount,
                sum(base_amount) base_amount,
                line_type_lookup_code,
                PAY_awt_group_id, -- PAY_AWT_GROUP_ID column is used in R12.2.4 and AWT_GROUP_ID column is used in R12.0.6
                reversal_flag,
                invoice_id,
                awt_related_id,(SELECT TCA.ATTRIBUTE6
                   FROM Ap_Tax_Codes_All TCA
                  where TCA.TAX_ID = NVL(WITHHOLDING_TAX_CODE_ID, -1)) Tax_exemption,
                (SELECT TCA.ATTRIBUTE5
                   FROM Ap_Tax_Codes_All TCA
                  where TCA.TAX_ID = NVL(WITHHOLDING_TAX_CODE_ID, -1)) Section,
                (SELECT TCA.ATTRIBUTE4
                   FROM Ap_Tax_Codes_All TCA
                  where TCA.TAX_ID = NVL(WITHHOLDING_TAX_CODE_ID, -1)) IRS_NO
                  ,
                  (SELECT TCA.ATTRIBUTE3
                   FROM Ap_Tax_Codes_All TCA
                  where TCA.TAX_ID = NVL(WITHHOLDING_TAX_CODE_ID, -1)) TAX_TYPE
           FROM ap_invoice_distributions_all
          WHERE org_id = 376
                 ------------------------ CHANGE TAX TYPE PARAMETER ------------------------------------------------------ 
                 AND  NVL(WITHHOLDING_TAX_CODE_ID,-1)
                 IN
                 (
                  SELECT TAX_ID
                 FROM Ap_Tax_Codes_All  TCA
                 WHERE  ORG_ID=   376
                 --AND    nvl(TCA.ATTRIBUTE3,'NA') = nvl( :P_TAX_TYPE , nvl(TCA.ATTRIBUTE3,'NA')  )
                 )
               -------------------------------------------------------------------------------------------
                 AND  NVL(WITHHOLDING_TAX_CODE_ID,-1)
                 NOT IN
                 (                                               --- ADDED ON 28 FEB -- --  added org_id by IACS ( IMRAN 27-july-2015 )
                 SELECT TAX_ID FROM Ap_Tax_Codes_All
                 WHERE  org_id  =376
                 AND  DESCRIPTION LIKE 'GST-SRO98'
                 )       --- ADDED ON 28 FEB  --  added org_id by IACS ( IMRAN 27-july-2015 )
                 group by PAY_awt_group_id, reversal_flag, line_type_lookup_code, invoice_id, awt_related_id,attribute3,WITHHOLDING_TAX_CODE_ID
                 ) aidl    -- used for tax grouping   
,        ap_invoice_distributions_all aidl1                                                                        -- used for item grouping
,        ap_awt_groups atg
,        ap_invoices_all aia
,        ap_suppliers vnd
,        ap_supplier_sites_all vnds
,        ap_invoice_payments_all aipa
,        ap_checks_all aca
,        fnd_user u
,        gl_code_combinations  gcc
   WHERE
--
-- -------* key join codition *------------
                 aidl1.org_id  =376-- ADDED ON 31-JUL-15
     and aia.org_id=376-- ADDED ON 31-JUL-15
     and aipa.org_id=376-- ADDED ON 31-JUL-15
     and aca.org_id=376  -- ADDED ON 31-JUL-15
     and aia.org_id=aidl1.org_id -- ADDED ON 31-JUL-15
     and aia.org_id=aipa.org_id -- ADDED ON 31-JUL-15
     and aia.org_id=aca.org_id  -- ADDED ON 31-JUL-15
     AND     aidl1.invoice_distribution_id = aidl.awt_related_id
-----------------------------------------------------
     AND aidl1.PAY_awt_group_id = atg.group_id -- PAY_AWT_GROUP_ID column is used in R12.2.4 and AWT_GROUP_ID column is used in R12.0.6
     AND aia.invoice_id = aidl.invoice_id
     AND vnd.vendor_id = aia.vendor_id
     AND vnd.vendor_id = vnds.vendor_id
     AND aia.vendor_site_id = vnds.vendor_site_id
     AND aipa.invoice_id = aia.invoice_id
     AND aca.check_id = aipa.check_id
    -- and  gcc.SEGMENT2  between  nvl(:P_FROM_LOCATION  ,gcc.SEGMENT2 )  and  nvl( :P_TO_LOCATION ,gcc.SEGMENT2 ) 
     AND aca.cleared_date IS NOT NULL
     AND aidl.amount <> '0'                                                                                       -- do not print zero value tax lines
     AND (aipa.reversal_flag = 'N' OR aipa.reversal_flag is null)
     AND (aidl.reversal_flag = 'N' or aidl.reversal_flag is null)
     AND (aidl1.reversal_flag = 'N' or aidl1.reversal_flag is null)
-- -----------Parameters-------------------
     AND trunc(aca.cleared_date) BETWEEN '01-JUL-2016' and '30-JUN-2017' --nvl (:p_date_from, aca.cleared_date) AND nvl (:p_date_to, aca.cleared_date)
     --AND atg.NAME BETWEEN nvl (:p_tax_code_from, atg.NAME) AND nvl (:p_tax_code_to, atg.NAME)
--     AND aia.doc_sequence_value = nvl (:p_apn_num, aia.doc_sequence_value)
     --AND vnd.vendor_id BETWEEN nvl (:p_vendor_from, vnd.vendor_id) AND nvl(:p_vendor_to, vnd.vendor_id)
     --and aca.CURRENCY_CODE between nvl (:CCY_from,aca.CURRENCY_CODE) and nvl (:CCY_to,aca.CURRENCY_CODE)
     and u.USER_ID=aia.CREATED_BY
    -- and U.user_id=NVL(:P_USER_ID,U.USER_ID)
     and  aidl1.DIST_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
   
-- ----------------------------------------------
GROUP BY
         atg.NAME
,        atg.description
,        vnd.vendor_name
,        vnd.segment1
,        vnds.city
,        vnd.vat_registration_num
,        aia.doc_sequence_value
,        aca.doc_sequence_value
,        aia.invoice_currency_code
,        aca.cleared_date
,        aia.invoice_id
,        aca.currency_code
,        vnds.address_line1
,        vnds.address_line2
,        vnds.address_line3
,        vnds.attribute14
,        vnds.attribute15
,        vnd.attribute13
,        aca.bank_account_name
,        aidl.IRS_NO,TAX_TYPE,U.USER_NAME,Section,Tax_exemption
,gcc.SEGMENT2, gcc.SEGMENT4,aidl1.DESCRIPTION,gcc.CHART_OF_ACCOUNTS_ID

ORDER BY
       Tax_Code,
       aca.cleared_date,
       aia.doc_sequence_value
ASC;