This Query is used for getting the AP invoice for TDS during the current financial Year.
Script:
SELECT
SEGMENT1 SUPPLIER_NUMBER
,VENDOR_NAME SUPPLIER_NAME
,ACTUAL_SECTION_CODE SECTION_CODE
,aia.invoice_num
,aia.invoice_date
, jt.TAX_AMOUNT tax_amount
,SUM(JT.AMOUNT) INVOICE_LINE_AMOUNT
FROM
AP_SUPPLIERS APS
,AP_INVOICES_ALL AIA
,JAI_AP_WTHLD_INV_TAXES JT
WHERE APS.VENDOR_ID = AIA.VENDOR_ID
AND AIA.INVOICE_ID = JT.INVOICE_ID
AND REGIME_CODE = ‘TDS’
AND INVOICE_DATE BETWEEN
(SELECT TO_DATE(’01-04′ || EXTRACT(YEAR FROM add_months(to_date(SYSDATE,’DD-MM-RRRR’), -3)),’DD-MM-RRRR’) P_FROM_DATE from dual) AND
(select TO_DATE(’31-03′ || EXTRACT(YEAR FROM add_months(to_date(SYSDATE,’DD-MM-RRRR’), 9)),’DD-MM-RRRR’) P_TO_DATE from dual)
AND VENDOR_NAME = NVL(:P_SUPPLIER_NAME,VENDOR_NAME)
AND SEGMENT1 = NVL(:P_SUPPLIER_NUMBER,SEGMENT1 )
AND ACTUAL_SECTION_CODE= NVL(:P_SECTION_CODE,ACTUAL_SECTION_CODE)
AND SEGMENT1 IN ( SELECT REGEXP_SUBSTR(NVL(:P_SUPPLIERS,SEGMENT1),'[^,]+’, 1, LEVEL) FROM DUAL
CONNECT BY
REGEXP_SUBSTR(NVL(:P_SUPPLIERS,SEGMENT1), ‘[^,]+’, 1, LEVEL)IS NOT NULL )
GROUP BY
SEGMENT1
,VENDOR_NAME
,ACTUAL_SECTION_CODE
,jt.TAX_AMOUNT
,aia.invoice_num
,aia.invoice_date
Do drop a note by writing us at Dileep.dinesh@doyensys.com or use the comment section below to ask your questions
