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

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