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 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
No comments:
Post a Comment