| Summary: | tcs not included | ||
|---|---|---|---|
| Product: | [01.IMIS] IMIS AP | Reporter: | Pushkin Kumar <pushkin.kumar> |
| Component: | IMIS AP FORMS AND REPORTS | Assignee: | Mahender <mahender.singh> |
| Status: | RESOLVED NOTHINGTOFIX | ||
| Severity: | enhancement | CC: | sharad.sharma |
| Priority: | --- | ||
| Version: | unspecified | ||
| Hardware: | PC | ||
| OS: | Windows | ||
| Database: | Module ID: | ||
| Attachments: | Party Details | ||
DONE CHECK AND CONFIRM CURSOR C1 (X_BGN_DATE DATE, X_END_DATE DATE )IS
/* BUGZILLA CALL ID 6374
Modification reason becuase PAN_NUMBER same and customer code is diffent
SELECT PAN_NUMBER,CUSTOMER_CODE,CUSTOMER_NAME, SALE_VALUE, PAYMENT_RECEIVED, FOR_TCS_AMT, TCS_AMOUNT
FROM
(
SELECT PAN_NUMBER,CUSTOMER_CODE,CUSTOMER_NAME, SUM(SALE_VALUE) SALE_VALUE, SUM(PAYMENT_RECEIVED) PAYMENT_RECEIVED, SUM(FOR_TCS_AMT) FOR_TCS_AMT, ROUND((SUM(FOR_TCS_AMT) * .075) /100) TCS_AMOUNT
FROM
(
SELECT C.PAN_NUMBER,C.CUSTOMER_CODE,C.CUSTOMER_NAME,SUM(VOUCHER_AMOUNT) SALE_VALUE , 0 PAYMENT_RECEIVED, 0 FOR_TCS_AMT
FROM VW_CUSTOMER_LEDGER L, ALL_CUSTOMERS C
WHERE L.COMPANY IN ( SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = PKG_CONTROLS.GET_FIRST_PARENT(P_COMPANY_CODE) )
AND VOUCHER_DATE BETWEEN ( SELECT DISTINCT YEAR_BGN_DATE
FROM VW_FISCAL_YEARS
WHERE X_BGN_DATE BETWEEN YEAR_BGN_DATE AND YEAR_END_DATE
)
AND X_END_DATE
AND L.CUSTOMER_CODE = C.CUSTOMER_CODE
AND L.VOUCHER_TYPE = 'I1'
GROUP BY C.PAN_NUMBER,C.CUSTOMER_CODE,C.CUSTOMER_NAME
UNION ALL
SELECT C.PAN_NUMBER,C.CUSTOMER_CODE,C.CUSTOMER_NAME,0 SALE_VALUE , SUM(VOUCHER_AMOUNT) PAYMENT_RECEIVED_AMT, 0 FOR_TCS_AMT
FROM VW_CUSTOMER_LEDGER L, ALL_CUSTOMERS C
WHERE L.COMPANY IN ( SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = PKG_CONTROLS.GET_FIRST_PARENT(P_COMPANY_CODE) )
AND VOUCHER_DATE BETWEEN (
SELECT DISTINCT YEAR_BGN_DATE
FROM VW_FISCAL_YEARS
WHERE X_BGN_DATE BETWEEN YEAR_BGN_DATE AND YEAR_END_DATE
)
AND X_END_DATE
AND L.CUSTOMER_CODE = C.CUSTOMER_CODE
AND L.VOUCHER_TYPE IN ('LP','82','JV')
AND L.VOUCHER_SERIES NOT LIKE 'R%'
GROUP BY C.PAN_NUMBER,C.CUSTOMER_CODE,C.CUSTOMER_NAME
UNION ALL
SELECT C.PAN_NUMBER,C.CUSTOMER_CODE,C.CUSTOMER_NAME,0 SALE_VALUE , SUM(NET_AMOUNT_RECEIVED) PAYMENT_RECEIVED_AMT, 0 FOR_TCS_AMT
FROM PAYMENTS_RECEIVED L, ALL_CUSTOMERS C
WHERE L.COMPANY IN ( SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = PKG_CONTROLS.GET_FIRST_PARENT(P_COMPANY_CODE) )
AND L.VOUCHER_DATE BETWEEN (
SELECT DISTINCT YEAR_BGN_DATE
FROM VW_FISCAL_YEARS
WHERE X_BGN_DATE BETWEEN YEAR_BGN_DATE AND YEAR_END_DATE
)
AND X_END_DATE
AND L.CUSTOMER_CODE = C.CUSTOMER_CODE
AND L.VOUCHER_TYPE IN ('82')
AND L.STATUS IN ('A','Y')
AND INTER_BRANCH_TRANS_FLAG = 'Y'
GROUP BY C.PAN_NUMBER,C.CUSTOMER_CODE,C.CUSTOMER_NAME
UNION ALL
SELECT C.PAN_NUMBER,C.CUSTOMER_CODE,C.CUSTOMER_NAME,0 SALE_VALUE , 0 PAYMENT_RECEIVED_AMT, SUM(VOUCHER_AMOUNT) FOR_TCS_AMT
FROM VW_CUSTOMER_LEDGER L, ALL_CUSTOMERS C
WHERE L.COMPANY IN ( SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = PKG_CONTROLS.GET_FIRST_PARENT(P_COMPANY_CODE) )
AND VOUCHER_DATE BETWEEN X_BGN_DATE AND X_END_DATE
AND L.CUSTOMER_CODE = C.CUSTOMER_CODE
AND L.VOUCHER_TYPE IN ('LP','82','JV')
AND L.STATUS IN ('A','Y')
AND L.VOUCHER_SERIES NOT LIKE 'R%'
GROUP BY C.PAN_NUMBER,C.CUSTOMER_CODE,C.CUSTOMER_NAME
UNION ALL
SELECT C.PAN_NUMBER,C.CUSTOMER_CODE,C.CUSTOMER_NAME,0 SALE_VALUE , 0 PAYMENT_RECEIVED_AMT, SUM(NET_AMOUNT_RECEIVED) FOR_TCS_AMT
FROM PAYMENTS_RECEIVED L, ALL_CUSTOMERS C
WHERE L.COMPANY IN ( SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = PKG_CONTROLS.GET_FIRST_PARENT(P_COMPANY_CODE) )
AND L.VOUCHER_DATE BETWEEN X_BGN_DATE AND X_END_DATE
AND L.CUSTOMER_CODE = C.CUSTOMER_CODE
AND L.VOUCHER_TYPE IN ('82')
AND L.STATUS IN ('A','Y')
AND INTER_BRANCH_TRANS_FLAG = 'Y'
GROUP BY C.PAN_NUMBER,C.CUSTOMER_CODE,C.CUSTOMER_NAME
)
GROUP BY PAN_NUMBER,CUSTOMER_CODE,CUSTOMER_NAME
) */
SELECT PAN_NUMBER,CUSTOMER_CODE,CUSTOMER_NAME, SALE_VALUE, PAYMENT_RECEIVED, FOR_TCS_AMT, TCS_AMOUNT
FROM
(
SELECT D.PAN_NUMBER,A.CUSTOMER_CODE,A.CUSTOMER_NAME, SUM(SALE_VALUE) SALE_VALUE, SUM(PAYMENT_RECEIVED) PAYMENT_RECEIVED, SUM(FOR_TCS_AMT) FOR_TCS_AMT, ROUND((SUM(FOR_TCS_AMT) * .075) /100) TCS_AMOUNT
FROM
(
SELECT C.PAN_NUMBER,SUM(VOUCHER_AMOUNT) SALE_VALUE , 0 PAYMENT_RECEIVED, 0 FOR_TCS_AMT
FROM VW_CUSTOMER_LEDGER L, ALL_CUSTOMERS C
WHERE L.COMPANY IN ( SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = PKG_CONTROLS.GET_FIRST_PARENT(P_COMPANY_CODE) )
AND VOUCHER_DATE BETWEEN ( SELECT DISTINCT YEAR_BGN_DATE
FROM VW_FISCAL_YEARS
WHERE X_BGN_DATE BETWEEN YEAR_BGN_DATE AND YEAR_END_DATE
)
AND X_END_DATE
AND L.CUSTOMER_CODE = C.CUSTOMER_CODE
AND L.VOUCHER_TYPE = 'I1'
GROUP BY C.PAN_NUMBER
UNION ALL
SELECT C.PAN_NUMBER,0 SALE_VALUE , SUM(VOUCHER_AMOUNT) PAYMENT_RECEIVED_AMT, 0 FOR_TCS_AMT
FROM VW_CUSTOMER_LEDGER L, ALL_CUSTOMERS C
WHERE L.COMPANY IN ( SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = PKG_CONTROLS.GET_FIRST_PARENT(P_COMPANY_CODE) )
AND VOUCHER_DATE BETWEEN (
SELECT DISTINCT YEAR_BGN_DATE
FROM VW_FISCAL_YEARS
WHERE X_BGN_DATE BETWEEN YEAR_BGN_DATE AND YEAR_END_DATE
)
AND X_END_DATE
AND L.CUSTOMER_CODE = C.CUSTOMER_CODE
AND L.VOUCHER_TYPE IN ('LP','82','JV')
AND L.VOUCHER_SERIES NOT LIKE 'R%'
GROUP BY C.PAN_NUMBER
UNION ALL
SELECT C.PAN_NUMBER,0 SALE_VALUE , SUM(NET_AMOUNT_RECEIVED) PAYMENT_RECEIVED_AMT, 0 FOR_TCS_AMT
FROM PAYMENTS_RECEIVED L, ALL_CUSTOMERS C
WHERE L.COMPANY IN ( SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = PKG_CONTROLS.GET_FIRST_PARENT(P_COMPANY_CODE) )
AND L.VOUCHER_DATE BETWEEN (
SELECT DISTINCT YEAR_BGN_DATE
FROM VW_FISCAL_YEARS
WHERE X_BGN_DATE BETWEEN YEAR_BGN_DATE AND YEAR_END_DATE
)
AND X_END_DATE
AND L.CUSTOMER_CODE = C.CUSTOMER_CODE
AND L.VOUCHER_TYPE IN ('82')
AND L.STATUS IN ('A','Y')
AND INTER_BRANCH_TRANS_FLAG = 'Y'
GROUP BY C.PAN_NUMBER
UNION ALL
SELECT C.PAN_NUMBER,0 SALE_VALUE , 0 PAYMENT_RECEIVED_AMT, SUM(VOUCHER_AMOUNT) FOR_TCS_AMT
FROM VW_CUSTOMER_LEDGER L, ALL_CUSTOMERS C
WHERE L.COMPANY IN ( SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = PKG_CONTROLS.GET_FIRST_PARENT(P_COMPANY_CODE) )
AND VOUCHER_DATE BETWEEN X_BGN_DATE AND X_END_DATE
AND L.CUSTOMER_CODE = C.CUSTOMER_CODE
AND L.VOUCHER_TYPE IN ('LP','82','JV')
AND L.STATUS IN ('A','Y')
AND L.VOUCHER_SERIES NOT LIKE 'R%'
GROUP BY C.PAN_NUMBER
UNION ALL
SELECT C.PAN_NUMBER,0 SALE_VALUE , 0 PAYMENT_RECEIVED_AMT, SUM(NET_AMOUNT_RECEIVED) FOR_TCS_AMT
FROM PAYMENTS_RECEIVED L, ALL_CUSTOMERS C
WHERE L.COMPANY IN ( SELECT COMPANY_CODE FROM VW_COMPANIES WHERE STATUS = 'A' CONNECT BY PRIOR COMPANY_CODE = PARENT_COMPANY START WITH PARENT_COMPANY = PKG_CONTROLS.GET_FIRST_PARENT(P_COMPANY_CODE) )
AND L.VOUCHER_DATE BETWEEN X_BGN_DATE AND X_END_DATE
AND L.CUSTOMER_CODE = C.CUSTOMER_CODE
AND L.VOUCHER_TYPE IN ('82')
AND L.STATUS IN ('A','Y')
AND INTER_BRANCH_TRANS_FLAG = 'Y'
GROUP BY C.PAN_NUMBER
) D , ALL_CUSTOMERS A , CUSTOMERS C
WHERE D.PAN_NUMBER = A.PAN_NUMBER
AND A.CUSTOMER_CODE = C.CUSTOMER_CODE
AND C.COMPANY = P_COMPANY_CODE
GROUP BY D.PAN_NUMBER,A.CUSTOMER_CODE,A.CUSTOMER_NAME
)
WHERE TCS_AMOUNT <> 0
AND PAYMENT_RECEIVED > 5000000
ORDER BY PAN_NUMBER,CUSTOMER_NAME,CUSTOMER_CODE;
Dear Sir, Procedure := PKG_AR.GEN_TCS_DEBIT_NOTE Mark CC To Mayukh Sir ID is Mayukh.dhar@dsdrinks.in Created attachment 1879 [details]
Party Details
Dear Sir/
Customer in attached file with different customer code and the same pan no. are not included in tcs report so please do the needful
|
Dear Sir, Following Parties are double reflect in report. 1. UAV WELLNESS PRIVATE LIMITED 2. JUNEJA AGENCY 3. GOYAL TRADERS and following party reflect in negative 1. ANUGRAH TRADERS