SELECT
aia.invoice_num,
aia.creation_date invoice_creation_date,
jai.amount base_amount,
(
SELECT
vendor_name
FROM
ap_suppliers asa
WHERE
asa.vendor_id = aia.vendor_id
) vendor_name,
jai.tax_amount,
jai.actual_section_code tds_section_code--,jai.TAX_CATEGORY_ID,jai.INVOICE_ID,jai.INVOICE_DISTRIBUTION_ID,jai.ACTUAL_TAX_ID
,
jai.tax_type,
(
SELECT
rate_percentage
FROM
jai_tax_rate_details_v
WHERE
effective_to IS NULL
AND tax_rate_id = jai.actual_tax_id
) tax_rate,
(
SELECT
registration_number
FROM
jai_party_reg_lines jprl,
jai_party_regs jpr
WHERE
registration_type_code = 'PAN'
AND jprl.party_reg_id = jpr.party_reg_id
AND party_id = aia.vendor_id
AND jprl.effective_to IS NULL
AND party_site_id = aia.vendor_site_id
) pan_number,
(
SELECT
registration_number
FROM
jai_party_reg_lines jprl,
jai_party_regs jpr
WHERE
jprl.registration_type_code IN ( 'GSTN', 'GSTIN' )
AND jprl.party_reg_id = jpr.party_reg_id
AND party_id = aia.vendor_id
AND effective_to IS NULL
AND party_site_id = aia.vendor_site_id
) gstin_number
FROM
jai_ap_wthld_inv_taxes jai,
ap_invoices_all aia
--,apps.ap_invoice_distributions_all aid
WHERE
jai.tax_line_no > 0
AND jai.invoice_id = aia.invoice_id
AND aia.cancelled_date IS NULL
AND apps.AP_INVOICES_PKG.GET_POSTING_STATUS( aia.invoice_id ) ='Y' --Accounted
AND trunc(aia.creation_date) BETWEEN '01-OCT-2023' AND '30-NOV-2023'
-- AND jai.INVOICE_DISTRIBUTION_ID = aid.INVOICE_DISTRIBUTION_ID
ORDER BY
aia.creation_date;