Lower Deduction Cost Details
Introduction:
This report will provide LDC details.
Cause of the issue:
To get the remaining amount pan wise.
How do we solve:
So, extract the LDC details instead of vendor wise because duplicate data.
SELECT
a.*
FROM
(
SELECT
xld.vendor_name “Vendor Name”,
xld.amount_limit “Amount Limit”,
–SUM(jatt.taxable_amount) “Consumed Amount”,
SUM(DECODE(JATT.TDS_EVENT,’PREPAYMENT APPLICATION’,-(jatt.taxable_amount),jatt.taxable_amount)) “Consumed Amount”,
xld.certificate_no “Certificate Number”,
xld.pan_no “Pan Number”,
jati.tds_section “TDS Code”,
jati.tds_tax_rate “TDS Rate”,
xld.from_date “From Date”,
xld.to_date “Upto date”,
xld.operating_unit “Operating Unit”,
xld.entity_name “Entity Name”
FROM
xxfki.xxfki_ldc_details xld,
ap.ap_suppliers aps,
ap.ap_invoices_all aia,
ja.jai_ap_tds_invoices jati,
apps.hr_operating_units hou,
ja.jai_ap_tds_thhold_trxs jatt,
ja.jai_ap_tds_thhold_grps jatg
WHERE
1 = 1
— AND upper(xld.vendor_name) = upper(aps.vendor_name)
AND upper(xld.pan_no)=upper(aps.global_attribute1)
AND aps.vendor_id = aia.vendor_id
AND aia.invoice_id = jati.invoice_id
AND aia.org_id = jati.organization_id
AND xld.operating_unit = hou.name
AND hou.organization_id = jati.organization_id
— and xld.VENDOR_NAME LIKE ‘ADHAAN SOLUTION PRIVATE LIMITED’
AND jati.tds_tax_rate = xld.percentage
AND aia.gl_date BETWEEN xld.from_date AND xld.to_date
— AND aia.gl_date BETWEEN ’01-06-2021′ AND ’30-06-2021′
AND aia.cancelled_date IS NULL
AND upper(jati.tds_section) = upper(xld.tds_code)
AND jatt.invoice_id = jati.invoice_id
AND jatt.threshold_grp_id=jatg.threshold_grp_id
AND jatg.vendor_id=aia.vendor_id
AND upper(jati.tds_section) = upper(jatg.SECTION_CODE)
GROUP BY
xld.vendor_name,
xld.certificate_no,
xld.pan_no,
jati.tds_section,
jati.tds_tax_rate,
xld.from_date,
xld.to_date,
xld.amount_limit,
xld.operating_unit,
xld.entity_name
) a
WHERE
1 = 1
AND “Amount Limit” > “Consumed Amount”
