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