Attachment 1409 Details for Bug 4940 – SS_0741_IMIS_Against call - 4940_cr

SS_0741_IMIS_Against call - 4940_cr

SS_0741_IMIS_Against call - 4940_cr.sql (text/x-vhdl), 27.33 KB, created by Sharad on 2020-11-05 15:09:00 IST

(hide)

Description:

Filename:

MIME Type:

Creator: Sharad

Created: 2020-11-05 15:09:00 IST

Size: 27.33 KB

patch

obsolete

>--VW_SUPPLIER_CR_NOTES
>
>CREATE OR REPLACE VIEW VW_SUPPLIER_CR_NOTES ( SUPPLIER_CR_NOTES_RECID, COMPANY_CODE, DOCUMENT_TYPE, DOCUMENT_SERIES, ENTRY_NUMBER, DOCUMENT_NUMBER, DOCUMENT_DATE, CR_NOTE_REF_TYPE, CR_NOTE_SUB_TYPE, XFR_COMPANY, SUPPLIER_CODE, ADDRESS_CODE, COST_CENTRE, DEPARTMENT_CODE, NET_CREDIT_AMOUNT, UNADJUSTED_AMOUNT, LINK_REF_COMPANY, LINK_REF_TYPE, LINK_REF_SERIES, LINK_REF_NUMBER, IGST_AMOUNT, CGST_AMOUNT, SGST_AMOUNT, CESS_AMOUNT, CESS_ON_QTY, STATUS, REMARKS, CREATED_BY, CREATED_ON, LAST_UPD_BY, LAST_UPD_ON ) AS
>SELECT
> DBN_RECID					SUPPLIER_CR_NOTES_RECID
>,COMPANY					COMPANY_CODE
>,DEBIT_NOTE_TYPE			DOCUMENT_TYPE
>,DEBIT_NOTE_SERIES 			DOCUMENT_SERIES
>,ENTRY_NO					ENTRY_NUMBER
>,DEBIT_NOTE_NO				DOCUMENT_NUMBER
>,DEBIT_NOTE_DATE			DOCUMENT_DATE
>,CATEGORY_CODE				CR_NOTE_REF_TYPE
>,TYPE_OF_DRNOTE				CR_NOTE_SUB_TYPE
>,XFR_COMPANY				XFR_COMPANY
>,SUPPLIER_CODE				SUPPLIER_CODE
>,ADDRESS_CODE				ADDRESS_CODE
>,PROJECT					COST_CENTRE
>,DEPARTMENT_CODE			DEPARTMENT_CODE
>,SPV_NET_DEBIT_AMOUNT		NET_CREDIT_AMOUNT
>,UNADJUSTED_debit_AMOUNT	UNADJUSTED_AMOUNT
>,REF_COMPANY				LINK_REF_COMPANY
>,REF_TYPE					LINK_REF_TYPE
>,REF_SERIES					LINK_REF_SERIES
>,REF_NO						LINK_REF_NUMBER
>,IGST_AMOUNT				IGST_AMOUNT
>,CGST_AMOUNT				CGST_AMOUNT
>,SGST_AMOUNT				SGST_AMOUNT
>,CESS_AMOUNT				CESS_AMOUNT
>,CESS_ON_QTY				CESS_ON_QTY
>,STATUS						STATUS
>,REMARKS					REMARKS
>,CREATED_BY					CREATED_BY
>,CREATED_ON					CREATED_ON
>,LAST_UPD_BY				LAST_UPD_BY
>,LAST_UPD_ON				LAST_UPD_ON
>FROM	SPLR_DEBIT_NOTES
>-- Bgn --
>-- Against call id - 4940 
>-- By ss on dt. 04/11/2020
>-- As per create New voucher type for Credit note to Supplier without GST
>-- Comment Old Code 
>-- WHERE 	DEBIT_NOTE_TYPE IN ( '51', 'CN' ) 
>WHERE 	DEBIT_NOTE_TYPE IN ( '51', 'CN', 'C5' )
>-- End --
>
>------------------------------------------------------------------------------------------
>
>INSERT INTO COMPANY_VOUCHERS (COMPANY,VOUCHER_TYPE,NEXT_NUMBER,SHORT_NAME,CREATED_BY,CREATED_ON,LAST_UPD_BY,LAST_UPD_ON,STATUS,HO_NEXT_NUMBER,AUTO_SERIES,SERIES_USAGE,MODULE_CODE,AUTO_POST_GL,REPORT_CODE)
>SELECT
>	  COMPANY
>	, 'C5'
>	, 1
>	, 'CrN-w/o-GST'
>	, user
>	, SYSDATE
>	, NULL
>	, NULL
>	, STATUS
>	, HO_NEXT_NUMBER
>	, AUTO_SERIES
>	, SERIES_USAGE
>	, MODULE_CODE
>	, AUTO_POST_GL
>	, REPORT_CODE
>FROM COMPANY_VOUCHERS
>WHERE VOUCHER_TYPE = 'CN'
>ORDER BY 1;
>
>------------------------------------------------------------------------------------------------------------
>
>-- VW_SUPPLIER_LEDGER
>
>CREATE OR REPLACE VIEW VW_SUPPLIER_LEDGER ( ENTERPRISE_ID, COMPANY, REF_TABLE, VOUCHERS_RECID, VOUCHER_TYPE, VOUCHER_SERIES, VOUCHER_NO, SEQ_NUMBER, ENTRY_NO, VOUCHER_DATE, SUPPLIER_CODE, SUPPLIER_NAME, SUPPLIER_CLASS, SUPPLIER_GROUP, TYPE_OF_SUPPLIER, STATION, STATE, ECC_CODE, CST_NUMBER, LST_NUMBER, TYPE_OF_COMPANY, DUE_DATE, SUPPLIER_BILL_NO, SUPPLIER_BILL_DATE, VOUCHER_AMOUNT, DEBIT_AMOUNT, CREDIT_AMOUNT, STATUS, REMARKS ) AS
>SELECT   S.ENTERPRISE_ID
>		,A.COMPANY
>		,A.REF_TABLE
>		,A.VOUCHERS_RECID
>		,A.VOUCHER_TYPE
>		,A.VOUCHER_SERIES
>		,A.VOUCHER_NO
>		--28/04/2017	Release column seq_number
>		,A.SEQ_NUMBER
>		,A.ENTRY_NO
>		,A.VOUCHER_DATE
>		,A.SUPPLIER_CODE
>		,S.SUPPLIER_NAME SUPPLIER_NAME
>		--13/06/2016	Release column supplier class as supplier class
>		,S.SUPPLIER_CLASS SUPPLIER_CLASS
>		,S.SUPPLIER_GROUP SUPPLIER_GROUP
>		,S.SUPPLIER_GROUP TYPE_OF_SUPPLIER
>		,S.CITY_NAME STATION
>		,S.STATE_CODE STATE
>		,D.ECC_CODE
>		,D.CST_NUMBER
>		,D.LST_NUMBER
>		,S.IS_SMALL_SCALE TYPE_OF_COMPANY
>		,A.DUE_DATE
>		,A.SUPPLIER_BILL_NO
>		,A.SUPPLIER_BILL_DATE
>		,NVL(A.CREDIT_AMOUNT,0) - NVL(A.DEBIT_AMOUNT,0) VOUCHER_AMOUNT
>		,A.DEBIT_AMOUNT
>		,A.CREDIT_AMOUNT
>		,A.STATUS
>		,A.REMARKS
>
>FROM	(
>			SELECT	 COMPANY
>					,'PVS'					REF_TABLE
>					,PV_RECID				VOUCHERS_RECID
>					,VOUCHER_TYPE   		VOUCHER_TYPE
>					,VOUCHER_SERIES 		VOUCHER_SERIES
>					,VOUCHER_NUMBER 		VOUCHER_NO
>					,1 						SEQ_NUMBER
>					,ENTRY_NO       		ENTRY_NO
>					,VOUCHER_DATE   		VOUCHER_DATE
>					,SUPPLIER_CODE  		SUPPLIER_CODE
>					,NULL 		  			DUE_DATE
>					,NULL					SUPPLIER_BILL_NO
>					,NULL					SUPPLIER_BILL_DATE
>					--SK Tomar 27/04/2016  Used AMOUNT field instead of CASH_AMOUNT
>					,DECODE	(VOUCHER_TYPE, '60', AMOUNT
>							 			 , '62', AMOUNT - NVL(LINKED_PAYMENT,0)
>							 			 , '65', AMOUNT - NVL(LINKED_PAYMENT,0) - NVL(TDS_AMOUNT,0)
>							)  				DEBIT_AMOUNT
>					--,DECODE(VOUCHER_TYPE,'60', AMOUNT,'62',AMOUNT,'65',CASH_AMOUNT - NVL(TDS_AMOUNT,0)) DEBIT_AMOUNT
>					,NULL           		CREDIT_AMOUNT
>					,POSTING_STATUS 		STATUS
>					,NARRATION      		REMARKS
>			FROM  	PAYMENT_VOUCHERS
>			WHERE	COMPANY != '##'
>			AND   	POSTING_STATUS !='Z'
>			AND  	(PAYMENT_TO_DEPOSITOR IS NULL OR PAYMENT_TO_DEPOSITOR = 'N')
>			--SK Tomar 09/05/2016  releasing non-zero amounts
>			AND	  	AMOUNT - NVL(TDS_AMOUNT,0) - NVL(LINKED_PAYMENT,0) <> 0
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'SPV'					REF_TABLE
>					,SP_RECID				VOUCHERS_RECID
>					,SPV_TYPE     			VOUCHER_TYPE
>					,SPV_SERIES   			VOUCHER_SERIES
>					,SPV_NO       			VOUCHER_NO
>					,1 						SEQ_NUMBER
>					,ENTRY_NO     			ENTRY_NO
>					,SPV_DATE     			VOUCHER_DATE
>					,SPV_SPLR     			SUPPLIER_CODE
>					,DUE_DATE	 			DUE_DATE
>					,SPV_SPLR_BILL_NUMBER 	SUPPLIER_BILL_NO
>					,SPV_SPLR_BILL_DATE 	SUPPLIER_BILL_DATE
>					,NULL         			DEBIT_AMOUNT
>					,SPV_NET_AMOUNT_LOCAL 	CREDIT_AMOUNT
>					,STATUS       			STATUS
>					,REMARKS      			REMARKS
>			FROM  	SP_VOUCHERS
>			WHERE 	COMPANY != '##'
>			AND    	STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'DND'					REF_TABLE
>					,DBN_RECID				VOUCHERS_RECID
>					,DEBIT_NOTE_TYPE    	VOUCHER_TYPE
>					,DEBIT_NOTE_SERIES  	VOUCHER_SERIES
>					,DEBIT_NOTE_NO      	VOUCHER_NO
>					,1 						SEQ_NUMBER
>					,ENTRY_NO           	ENTRY_NO
>					,DEBIT_NOTE_DATE    	VOUCHER_DATE
>					,SUPPLIER_CODE      	SUPPLIER_CODE
>					,NULL 		       		DUE_DATE
>					,NULL			   		SUPPLIER_BILL_NO
>					,NULL			   		SUPPLIER_BILL_DATE
>					,SPV_NET_DEBIT_LOCAL 	DEBIT_AMOUNT
>					,NULL               	CREDIT_AMOUNT
>					,STATUS
>					,REMARKS
>			FROM  	SPLR_DEBIT_NOTES
>			WHERE 	COMPANY != '##'
>			AND    	STATUS !='Z'  
>			-- Bgn --
>			-- Against call id - 4940 
>			-- By ss on dt. 05/11/2020
>			-- As per create New voucher type for Debit note to Supplier without GST (D3)
>			-- Comment Old Code 
>			-- AND   	DEBIT_NOTE_TYPE  IN ('D1','53','DN')--Modify By MS Dt. 26/09/2017 For New Check GST Debit Note 'DN' 
>			AND   	DEBIT_NOTE_TYPE  IN ('D1','53','DN', 'D3')--Modify By SS Dt. 05/11/2020 For New Check GST Debit Note without GST 'D3'
>			-- End --
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'DNC'					REF_TABLE
>					,DBN_RECID				VOUCHERS_RECID
>					,DEBIT_NOTE_TYPE    	VOUCHER_TYPE
>					,DEBIT_NOTE_SERIES  	VOUCHER_SERIES
>					,DEBIT_NOTE_NO      	VOUCHER_NO
>					,1 						SEQ_NUMBER
>					,ENTRY_NO           	ENTRY_NO
>					,DEBIT_NOTE_DATE    	VOUCHER_DATE
>					,SUPPLIER_CODE      	SUPPLIER_CODE
>					,DUE_DATE 		   		DUE_DATE
>					,NULL			   		SUPPLIER_BILL_NO
>					,DEBIT_NOTE_DATE		SUPPLIER_BILL_DATE
>					,NULL               	DEBIT_AMOUNT
>					,SPV_NET_DEBIT_LOCAL	CREDIT_AMOUNT
>					,STATUS
>					,REMARKS
>			FROM  	SPLR_DEBIT_NOTES
>			WHERE 	COMPANY != '##'
>			-- Bgn --
>			-- Against call id - 4940 
>			-- By ss on dt. 05/11/2020
>			-- As per create New voucher type for Credit note to Supplier without GST (C5)
>			-- Comment Old Code 
>			--AND   	DEBIT_NOTE_TYPE  IN ('C1','51') 
>			AND   	DEBIT_NOTE_TYPE  IN ('C1', '51', 'C5')--Modify By SS Dt. 05/11/2020 For New Check GST Credit Note without GST 'C5'
>			-- End --
>			AND    	STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'AP1'					REF_TABLE
>					,APV_RECID				VOUCHERS_RECID
>					,VOUCHER_TYPE   		VOUCHER_TYPE
>					,VOUCHER_SERIES 		VOUCHER_SERIES
>					,VOUCHER_NUMBER 		VOUCHER_NO
>					,1 						SEQ_NUMBER
>					,ENTRY_NO       		ENTRY_NO
>					,VOUCHER_DATE   		VOUCHER_DATE
>					,SUPPLIER_CODE  		SUPPLIER_CODE
>					,DUE_DATE	   			DUE_DATE
>					,SUPPLIER_REFERENCE		SUPPLIER_BILL_NO
>					,SUPPLIER_REFERENCE_DATE  SUPPLIER_BILL_DATE
>					,NULL           		DEBIT_AMOUNT
>					--SK Tomar	20/09/2017
>					--Using supplier_amount_local ( bill amount ) to credit supplier
>					/*
>					,DECODE(VOUCHER_TYPE,'AP', DECODE(TAX_TYPE,'SRV',(NVL(COST_OF_GOODS,0) + NVL(COST_OF_SERVICES,0) + NVL(OTHER_DEDUCTION1,0)),(NVL(COST_OF_GOODS,0) + NVL(COST_OF_SERVICES,0) + NVL(OTHER_DEDUCTION1,0) + CASE WHEN NVL(GST_REVERSE_FLAG, 'N') = 'N' THEN NVL(ST_AMOUNT,0) ELSE 0 END))
>											 , DECODE(TAX_TYPE,'SRV',(NVL(COST_OF_GOODS,0) + NVL(COST_OF_SERVICES,0) + NVL(OTHER_DEDUCTION ,0)),(NVL(COST_OF_GOODS,0) + NVL(COST_OF_SERVICES,0) + NVL(OTHER_DEDUCTION ,0) + CASE WHEN NVL(GST_REVERSE_FLAG, 'N') = 'N' THEN NVL(ST_AMOUNT,0) ELSE 0 END))) CREDIT_AMOUNT
>					*/
>					,SUPPLIER_AMOUNT_LOCAL
>					,STATUS         		STATUS
>					,NARRATION      		REMARKS
>			FROM   	ACCOUNTS_PAYABLE_VOUCHERS
>			WHERE  	COMPANY != '##'
>			AND    	VOUCHER_TYPE != 'A1'
>			AND    	STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'DAP'					REF_TABLE
>					,APV_RECID				VOUCHERS_RECID
>					,VOUCHER_TYPE   		VOUCHER_TYPE
>					,VOUCHER_SERIES 		VOUCHER_SERIES
>					,VOUCHER_NUMBER 		VOUCHER_NO
>					,1 						SEQ_NUMBER
>					,ENTRY_NO       		ENTRY_NO
>					,VOUCHER_DATE   		VOUCHER_DATE
>					,SUPPLIER_CODE  		SUPPLIER_CODE
>					,DUE_DATE 		  		DUE_DATE
>					,SUPPLIER_REFERENCE 	SUPPLIER_BILL_NO
>					,SUPPLIER_REFERENCE_DATE  SUPPLIER_BILL_DATE
>					,NULL           		DEBIT_AMOUNT
>					,SUPPLIER_AMOUNT 		CREDIT_AMOUNT
>					,STATUS         		STATUS
>					,NARRATION      		REMARKS
>			FROM   	VW_DIRECT_AP_VOUCHERS
>			WHERE  	COMPANY != '##'
>			AND    	VOUCHER_TYPE = 'A1'
>			AND     STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'AP2'					REF_TABLE
>					,APV_RECID				VOUCHERS_RECID
>					,VOUCHER_TYPE   		VOUCHER_TYPE
>					,VOUCHER_SERIES 		VOUCHER_SERIES
>					,VOUCHER_NUMBER 		VOUCHER_NO
>					,1 						SEQ_NUMBER
>					,ENTRY_NO       		ENTRY_NO
>					,VOUCHER_DATE   		VOUCHER_DATE
>					,SUPPLIER_CODE  		SUPPLIER_CODE
>					,DUE_DATE	   			DUE_DATE
>					,SUPPLIER_REFERENCE 	SUPPLIER_BILL_NO
>					,SUPPLIER_REFERENCE_DATE  SUPPLIER_BILL_DATE
>					,NVL(ESI_AMOUNT,0)  	DEBIT_AMOUNT
>					,NULL           		CREDIT_AMOUNT
>					,STATUS         		STATUS
>					,'ESI DEDUCTED' 		REMARKS
>			FROM  	ACCOUNTS_PAYABLE_VOUCHERS
>			WHERE 	COMPANY != '##'
>			AND   	ESI_AMOUNT >0
>			AND    	STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'AP3'					REF_TABLE
>					,APV_RECID				VOUCHERS_RECID
>					,VOUCHER_TYPE   		VOUCHER_TYPE
>					,VOUCHER_SERIES 		VOUCHER_SERIES
>					,VOUCHER_NUMBER 		VOUCHER_NO
>					,1 						SEQ_NUMBER
>					,ENTRY_NO       		ENTRY_NO
>					,VOUCHER_DATE   		VOUCHER_DATE
>					,SUPPLIER_CODE  		SUPPLIER_CODE
>					,DUE_DATE	   			DUE_DATE
>					,SUPPLIER_REFERENCE		SUPPLIER_BILL_NO
>					,SUPPLIER_REFERENCE_DATE  SUPPLIER_BILL_DATE
>					,NVL(PF_AMOUNT,0)   	DEBIT_AMOUNT
>					,NULL           		CREDIT_AMOUNT
>					,STATUS        			STATUS
>					,'PF DEDUCTED' 			REMARKS
>			FROM  	ACCOUNTS_PAYABLE_VOUCHERS
>			WHERE 	COMPANY != '##'
>			AND   	PF_AMOUNT > 0
>			AND    	STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'AP4'					REF_TABLE
>					,APV_RECID				VOUCHERS_RECID
>					,VOUCHER_TYPE   		VOUCHER_TYPE
>					,VOUCHER_SERIES 		VOUCHER_SERIES
>					,VOUCHER_NUMBER 		VOUCHER_NO
>					,1 						SEQ_NUMBER
>					,ENTRY_NO       		ENTRY_NO
>					,VOUCHER_DATE   		VOUCHER_DATE
>					,SUPPLIER_CODE  		SUPPLIER_CODE
>					,DUE_DATE	   			DUE_DATE
>					,SUPPLIER_REFERENCE SUPPLIER_BILL_NO
>					,SUPPLIER_REFERENCE_DATE  SUPPLIER_BILL_DATE
>					,DECODE(VOUCHER_TYPE,'AP',NVL(OTHER_DEDUCTION1,0),NVL(OTHER_DEDUCTION,0))    DEBIT_AMOUNT
>					,NULL           		CREDIT_AMOUNT
>					,STATUS         		STATUS
>					,'OTHER DEDUCTIONS :- '||DEDUCTION_DESC REMARKS
>			FROM  	ACCOUNTS_PAYABLE_VOUCHERS
>			WHERE 	COMPANY != '##'
>			AND   	DECODE(VOUCHER_TYPE,'AP',OTHER_DEDUCTION1,OTHER_DEDUCTION) > 0
>			AND    	STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'AP5'					REF_TABLE
>					,APV_RECID				VOUCHERS_RECID
>					,VOUCHER_TYPE   		VOUCHER_TYPE
>					,VOUCHER_SERIES 		VOUCHER_SERIES
>					,VOUCHER_NUMBER 		VOUCHER_NO
>					,1 						SEQ_NUMBER
>					,ENTRY_NO       		ENTRY_NO
>					,VOUCHER_DATE   		VOUCHER_DATE
>					,SUPPLIER_CODE  		SUPPLIER_CODE
>					,DUE_DATE	   			DUE_DATE
>					,SUPPLIER_REFERENCE		SUPPLIER_BILL_NO
>					,SUPPLIER_REFERENCE_DATE  SUPPLIER_BILL_DATE
>					,DECODE(VOUCHER_TYPE,'AP',NVL(OTHER_DEDUCTION,0),NULL)   DEBIT_AMOUNT
>					,NULL           		CREDIT_AMOUNT
>					,STATUS         		STATUS
>					,'RETENTION AMOUNT'		REMARKS
>			FROM  	ACCOUNTS_PAYABLE_VOUCHERS
>			WHERE 	COMPANY != '##'
>			AND 	VOUCHER_TYPE='AP'
>			AND   	OTHER_DEDUCTION > 0
>			AND    	STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'WHT'					REF_TABLE
>					,PARENT_RECID			VOUCHERS_RECID
>					,VOUCHER_TYPE    		VOUCHER_TYPE
>					,VOUCHER_SERIES  		VOUCHER_SERIES
>					,VOUCHER_NUMBER  		VOUCHER_NO
>					,1 						SEQ_NUMBER
>					,ENTRY_NO        		ENTRY_NO
>					,VOUCHER_DATE    		VOUCHER_DATE
>					,PARTY_CODE      		SUPPLIER_CODE
>					,NULL	 	    		DUE_DATE
>					,NULL		    		SUPPLIER_BILL_NO
>					,NULL		    		SUPPLIER_BILL_DATE
>					,DECODE(VOUCHER_TYPE,'JV', WITH_HOLD_TAX_AMOUNT,	'53', NULL, WITH_HOLD_TAX_AMOUNT)  DEBIT_AMOUNT
>					,DECODE(VOUCHER_TYPE,'53',WITH_HOLD_TAX_AMOUNT,	NULL)            CREDIT_AMOUNT
>					,STATUS          		STATUS
>					,('TDS DEDUCTED'||CHR(10)||NARRATION) REMARKS
>			FROM  	WITH_HOLD_TAX_DETAILS A
>			WHERE 	COMPANY != '##'
>			AND    	STATUS !='Z'
>			AND   	(PARTY_TYPE = DECODE(VOUCHER_TYPE,'JV','S') OR PARTY_TYPE IS NULL)
>			AND		REF_COMPANY IS NULL
>			-- In case of Interunit Transactions thru Misc Payment Voucher TDS entry is generated in Supplier JV Detail
>			-- It is therefore exempted from supplier ledger
>			AND 	NOT EXISTS ( SELECT 1 FROM SUPPLIER_JV_DETAILS B, JOURNAL_VOUCHERS C WHERE B.COMPANY = C.COMPANY AND B.PARENT_RECID = C.JV_RECID AND A.COMPANY = A.COMPANY AND PARENT_RECID = A.PARENT_RECID AND C.INTER_BRANCH_TRANS_FLAG = 'Y')
>
>			UNION ALL
>			SELECT	 COMPANY
>					,'SJV'					REF_TABLE
>					,PARENT_RECID			VOUCHERS_RECID
>					,VOUCHER_TYPE    		VOUCHER_TYPE
>					,VOUCHER_SERIES  		VOUCHER_SERIES
>					,VOUCHER_NUMBER  		VOUCHER_NO
>					,SEQ_NO					SEQ_NUMBER
>					,ENTRY_NO        		ENTRY_NO
>					,VOUCHER_DATE    		VOUCHER_DATE
>					,SUPPLIER_CODE   		SUPPLIER_CODE
>					,NULL	  	    		DUE_DATE
>					,NULL		    		SUPPLIER_BILL_NO
>					,VOUCHER_DATE    		SUPPLIER_BILL_DATE
>					,DECODE(SIGN(NET_AMOUNT),-1,-NET_AMOUNT,0) DEBIT_AMOUNT
>					,DECODE(SIGN(NET_AMOUNT),1,NET_AMOUNT,0) CREDIT_AMOUNT
>					,STATUS          		STATUS
>					,NARRATION       		REMARKS
>			FROM   	SUPPLIER_JV_DETAILS A
>			WHERE  	COMPANY != '##'
>			AND     STATUS !='Z'
>
>			--SK Tomar	09/08/2017
>			--Due to GST, Tax Invoices to Supplier are included in supplier ledger
>			UNION ALL
>			SELECT 	 COMPANY
>					,'RMS'					REF_TABLE
>					,RP_RECID				VOUCHERS_RECID
>					,RP_TYPE     			VOUCHER_TYPE
>					,RP_SERIES   			VOUCHER_SERIES
>					,RP_NO       			VOUCHER_NO
>					,1 						SEQ_NUMBER
>					,ENTRY_NO     			ENTRY_NO
>					,RP_DATE     			VOUCHER_DATE
>					,SUPPLIER_cODE 			SUPPLIER_CODE
>					,NULL		 			DUE_DATE
>					,NULL 	SUPPLIER_BILL_NO
>					,NULL 	SUPPLIER_BILL_DATE
>					,RP_AMOUNT     			DEBIT_AMOUNT
>					,NULL					CREDIT_AMOUNT
>					,STATUS       			STATUS
>					,REMARKS      			REMARKS
>			FROM  	RM_PM_SALES
>			WHERE 	COMPANY != '##'
>			AND		RP_TYPE = 'I1'
>			AND    	STATUS !='Z'
>
>		) A, COMPANIES B, VW_ENT_SUPPLIERS S, ALL_SUPPLIERS D, SUPPLIERS CS
>WHERE  	A.COMPANY = B.COMPANY
>AND  	A.SUPPLIER_CODE = S.SUPPLIER_CODE
>AND		B.ENTERPRISE_ID = S.ENTERPRISE_ID
>AND  	A.SUPPLIER_CODE = D.SUPPLIER_CODE
>--Modify By MS Dt. 23/09/2019
>--Bugzilla Call ID 1259
>--AND     D.SUPPLIER_GROUP    != 'SP'
>--AND     D.SUPPLIER_CLASS	!= '090' 
>--As per telephonically discuss supplier type hendling company wise   
>AND     CS.SUPPLIER_CODE	= A.SUPPLIER_CODE
>AND     CS.COMPANY 			= A.COMPANY 	     
>AND     CS.SUPPLIER_TYPE	!= '090'    
>--End by MS  
>--*/
>/*
>SELECT   A.COMPANY
>		,A.REF_TABLE
>		,A.VOUCHERS_RECID
>		,A.VOUCHER_TYPE
>		,A.VOUCHER_SERIES
>		,A.VOUCHER_NO
>		,A.ENTRY_NO
>		,A.VOUCHER_DATE
>		,A.SUPPLIER_CODE
>		,S.SUPPLIER_NAME_ENGLISH SUPPLIER_NAME
>		,S.TYPE_OF_SUPPLIER SUPPLIER_GROUP
>		,S.SUPPLIER_TYPE TYPE_OF_SUPPLIER
>		,S.CITY STATION
>		,S.STATE_CODE STATE
>		,S.ECC_CODE
>		,S.CST_REG_NUM
>		,S.LST_REG_NUM
>		,S.TYPE_OF_COMPANY
>		,A.DUE_DATE
>		,A.SUPPLIER_BILL_NO
>		,A.SUPPLIER_BILL_DATE
>		,NVL(A.CREDIT_AMOUNT,0) - NVL(A.DEBIT_AMOUNT,0) VOUCHER_AMOUNT
>		,A.DEBIT_AMOUNT
>		,A.CREDIT_AMOUNT
>		,A.STATUS
>		,A.REMARKS
>
>FROM	(
>			SELECT	 COMPANY
>					,'PVS'			REF_TABLE
>					,PV_RECID		VOUCHERS_RECID
>					,VOUCHER_TYPE   VOUCHER_TYPE
>					,VOUCHER_SERIES VOUCHER_SERIES
>					,VOUCHER_NUMBER VOUCHER_NO
>					,ENTRY_NO       ENTRY_NO
>					,VOUCHER_DATE   VOUCHER_DATE
>					,SUPPLIER_CODE  SUPPLIER_CODE
>					,NULL 		  DUE_DATE
>					,NULL			  SUPPLIER_BILL_NO
>					,NULL			  SUPPLIER_BILL_DATE
>					,DECODE(VOUCHER_TYPE,'60', AMOUNT,'62',AMOUNT,'65',CASH_AMOUNT - NVL(TDS_AMOUNT,0)) DEBIT_AMOUNT
>					,NULL           CREDIT_AMOUNT
>					,POSTING_STATUS STATUS
>					,NARRATION      REMARKS
>			FROM  PAYMENT_VOUCHERS
>			WHERE COMPANY != '##'
>			AND   POSTING_STATUS !='Z'
>			AND  (PAYMENT_TO_DEPOSITOR IS NULL OR PAYMENT_TO_DEPOSITOR = 'N')
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'SPV'			REF_TABLE
>					,SP_RECID		VOUCHERS_RECID
>					,SPV_TYPE     VOUCHER_TYPE
>					,SPV_SERIES   VOUCHER_SERIES
>					,SPV_NO       VOUCHER_NO
>					,ENTRY_NO     ENTRY_NO
>					,SPV_DATE     VOUCHER_DATE
>					,SPV_SPLR     SUPPLIER_CODE
>					,DUE_DATE	 DUE_DATE
>					,SPV_SPLR_BILL_NUMBER SUPPLIER_BILL_NO
>					,SPV_SPLR_BILL_DATE SUPPLIER_BILL_DATE
>					,NULL         DEBIT_AMOUNT
>					,SPV_NET_AMOUNT_LOCAL CREDIT_AMOUNT
>					,STATUS       STATUS
>					,REMARKS      REMARKS
>			FROM  SP_VOUCHERS
>			WHERE COMPANY != '##'
>			AND    STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'DND'			REF_TABLE
>					,DBN_RECID		VOUCHERS_RECID
>					,DEBIT_NOTE_TYPE    VOUCHER_TYPE
>					,DEBIT_NOTE_SERIES  VOUCHER_SERIES
>					,DEBIT_NOTE_NO      VOUCHER_NO
>					,ENTRY_NO           ENTRY_NO
>					,DEBIT_NOTE_DATE    VOUCHER_DATE
>					,SUPPLIER_CODE      SUPPLIER_CODE
>					,NULL 		       DUE_DATE
>					,NULL			   SUPPLIER_BILL_NO
>					,NULL			   SUPPLIER_BILL_DATE
>					,SPV_NET_DEBIT_LOCAL DEBIT_AMOUNT
>					,NULL               CREDIT_AMOUNT
>					,STATUS
>					,REMARKS
>			FROM  SPLR_DEBIT_NOTES
>			WHERE COMPANY != '##'
>			AND    STATUS !='Z'
>			AND   DEBIT_NOTE_TYPE  IN ('D1','53')
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'DNC'			REF_TABLE
>					,DBN_RECID		VOUCHERS_RECID
>					,DEBIT_NOTE_TYPE    VOUCHER_TYPE
>					,DEBIT_NOTE_SERIES  VOUCHER_SERIES
>					,DEBIT_NOTE_NO      VOUCHER_NO
>					,ENTRY_NO           ENTRY_NO
>					,DEBIT_NOTE_DATE    VOUCHER_DATE
>					,SUPPLIER_CODE      SUPPLIER_CODE
>					,DUE_DATE 		   	DUE_DATE
>					,NULL			   	SUPPLIER_BILL_NO
>					,DEBIT_NOTE_DATE	SUPPLIER_BILL_DATE
>					,NULL               DEBIT_AMOUNT
>					,SPV_NET_DEBIT_LOCAL CREDIT_AMOUNT
>					,STATUS
>					,REMARKS
>			FROM  SPLR_DEBIT_NOTES
>			WHERE COMPANY != '##'
>			AND   DEBIT_NOTE_TYPE  IN ('C1','51')
>			AND    STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'AP1'			REF_TABLE
>					,APV_RECID		VOUCHERS_RECID
>					,VOUCHER_TYPE   VOUCHER_TYPE
>					,VOUCHER_SERIES VOUCHER_SERIES
>					,VOUCHER_NUMBER VOUCHER_NO
>					,ENTRY_NO       ENTRY_NO
>					,VOUCHER_DATE   VOUCHER_DATE
>					,SUPPLIER_CODE  SUPPLIER_CODE
>					,DUE_DATE	   DUE_DATE
>					,SUPPLIER_REFERENCE  SUPPLIER_BILL_NO
>					,SUPPLIER_REFERENCE_DATE  SUPPLIER_BILL_DATE
>					,NULL           DEBIT_AMOUNT
>					,DECODE(VOUCHER_TYPE,'AP',decode(tax_type,'SRV',(NVL(COST_OF_GOODS,0) + NVL(COST_OF_SERVICES,0)+ NVL(OTHER_DEDUCTION1,0)),(NVL(COST_OF_GOODS,0)+NVL(COST_OF_SERVICES,0)+NVL(ST_AMOUNT,0)+ NVL(OTHER_DEDUCTION1,0))),
>					decode(tax_type,'SRV',(NVL(COST_OF_GOODS,0) + NVL(COST_OF_SERVICES,0) + NVL(OTHER_DEDUCTION,0)),(NVL(COST_OF_GOODS,0)+NVL(COST_OF_SERVICES,0) + NVL(OTHER_DEDUCTION,0)+NVL(ST_AMOUNT,0)))) CREDIT_AMOUNT
>					,STATUS         STATUS
>					,NARRATION      REMARKS
>			FROM   ACCOUNTS_PAYABLE_VOUCHERS
>			WHERE  COMPANY != '##'
>			AND    VOUCHER_TYPE != 'A1'
>			AND    STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'DAP'			REF_TABLE
>					,APV_RECID		VOUCHERS_RECID
>					,VOUCHER_TYPE   VOUCHER_TYPE
>					,VOUCHER_SERIES VOUCHER_SERIES
>					,VOUCHER_NUMBER VOUCHER_NO
>					,ENTRY_NO       ENTRY_NO
>					,VOUCHER_DATE   VOUCHER_DATE
>					,SUPPLIER_CODE  SUPPLIER_CODE
>					,DUE_DATE 		  DUE_DATE
>					,SUPPLIER_REFERENCE  SUPPLIER_BILL_NO
>					,SUPPLIER_REFERENCE_DATE  SUPPLIER_BILL_DATE
>					,NULL           DEBIT_AMOUNT
>					,SUPPLIER_AMOUNT CREDIT_AMOUNT
>					,STATUS         STATUS
>					,NARRATION      REMARKS
>			FROM   VW_DIRECT_AP_VOUCHERS
>			WHERE  COMPANY != '##'
>			AND    VOUCHER_TYPE = 'A1'
>			AND     STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'AP2'			REF_TABLE
>					,APV_RECID		VOUCHERS_RECID
>					,VOUCHER_TYPE   VOUCHER_TYPE
>					,VOUCHER_SERIES VOUCHER_SERIES
>					,VOUCHER_NUMBER VOUCHER_NO
>					,ENTRY_NO       ENTRY_NO
>					,VOUCHER_DATE   VOUCHER_DATE
>					,SUPPLIER_CODE  SUPPLIER_CODE
>					,DUE_DATE	   DUE_DATE
>					,SUPPLIER_REFERENCE  SUPPLIER_BILL_NO
>					,SUPPLIER_REFERENCE_DATE  SUPPLIER_BILL_DATE
>					,NVL(ESI_AMOUNT,0)   DEBIT_AMOUNT
>					,NULL           CREDIT_AMOUNT
>					,STATUS         STATUS
>					,'ESI DEDUCTED' REMARKS
>			FROM  ACCOUNTS_PAYABLE_VOUCHERS
>			WHERE COMPANY != '##'
>			AND   ESI_AMOUNT >0
>			AND    STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'AP3'			REF_TABLE
>					,APV_RECID		VOUCHERS_RECID
>					,VOUCHER_TYPE   VOUCHER_TYPE
>					,VOUCHER_SERIES VOUCHER_SERIES
>					,VOUCHER_NUMBER VOUCHER_NO
>					,ENTRY_NO       ENTRY_NO
>					,VOUCHER_DATE   VOUCHER_DATE
>					,SUPPLIER_CODE  SUPPLIER_CODE
>					,DUE_DATE	   DUE_DATE
>					,SUPPLIER_REFERENCE  SUPPLIER_BILL_NO
>					,SUPPLIER_REFERENCE_DATE  SUPPLIER_BILL_DATE
>					,NVL(PF_AMOUNT,0)   DEBIT_AMOUNT
>					,NULL           CREDIT_AMOUNT
>					,STATUS        STATUS
>					,'PF DEDUCTED' REMARKS
>			FROM  ACCOUNTS_PAYABLE_VOUCHERS
>			WHERE COMPANY != '##'
>			AND   PF_AMOUNT > 0
>			AND    STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'AP4'			REF_TABLE
>					,APV_RECID		VOUCHERS_RECID
>					,VOUCHER_TYPE   VOUCHER_TYPE
>					,VOUCHER_SERIES VOUCHER_SERIES
>					,VOUCHER_NUMBER VOUCHER_NO
>					,ENTRY_NO       ENTRY_NO
>					,VOUCHER_DATE   VOUCHER_DATE
>					,SUPPLIER_CODE  SUPPLIER_CODE
>					,DUE_DATE	   DUE_DATE
>					,SUPPLIER_REFERENCE  SUPPLIER_BILL_NO
>					,SUPPLIER_REFERENCE_DATE  SUPPLIER_BILL_DATE
>					,DECODE(VOUCHER_TYPE,'AP',NVL(OTHER_DEDUCTION1,0),NVL(OTHER_DEDUCTION,0))    DEBIT_AMOUNT
>					,NULL           CREDIT_AMOUNT
>					,STATUS         STATUS
>					,'OTHER DEDUCTIONS :- '||DEDUCTION_DESC REMARKS
>			FROM  ACCOUNTS_PAYABLE_VOUCHERS
>			WHERE COMPANY != '##'
>			AND   DECODE(VOUCHER_TYPE,'AP',OTHER_DEDUCTION1,OTHER_DEDUCTION) > 0
>			AND    STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'AP5'			REF_TABLE
>					,APV_RECID		VOUCHERS_RECID
>					,VOUCHER_TYPE   VOUCHER_TYPE
>					,VOUCHER_SERIES VOUCHER_SERIES
>					,VOUCHER_NUMBER VOUCHER_NO
>					,ENTRY_NO       ENTRY_NO
>					,VOUCHER_DATE   VOUCHER_DATE
>					,SUPPLIER_CODE  SUPPLIER_CODE
>					,DUE_DATE	   DUE_DATE
>					,SUPPLIER_REFERENCE  SUPPLIER_BILL_NO
>					,SUPPLIER_REFERENCE_DATE  SUPPLIER_BILL_DATE
>					,DECODE(VOUCHER_TYPE,'AP',NVL(OTHER_DEDUCTION,0),NULL)   DEBIT_AMOUNT
>					,NULL           CREDIT_AMOUNT
>					,STATUS         STATUS
>					,'RETENTION AMOUNT ' REMARKS
>			FROM  	ACCOUNTS_PAYABLE_VOUCHERS
>			WHERE 	COMPANY != '##'
>			AND 	VOUCHER_TYPE='AP'
>			AND   	OTHER_DEDUCTION > 0
>			AND    	STATUS !='Z'
>
>			UNION ALL
>			SELECT 	 COMPANY
>					,'WHT'			REF_TABLE
>					,PARENT_RECID		VOUCHERS_RECID
>					,VOUCHER_TYPE    VOUCHER_TYPE
>					,VOUCHER_SERIES  VOUCHER_SERIES
>					,VOUCHER_NUMBER  VOUCHER_NO
>					,ENTRY_NO        ENTRY_NO
>					,VOUCHER_DATE    VOUCHER_DATE
>					,PARTY_CODE      SUPPLIER_CODE
>					,NULL	 	    DUE_DATE
>					,NULL		    SUPPLIER_BILL_NO
>					,NULL		    SUPPLIER_BILL_DATE
>					,DECODE(VOUCHER_TYPE,'JV', WITH_HOLD_TAX_AMOUNT,	'53', NULL, WITH_HOLD_TAX_AMOUNT)  DEBIT_AMOUNT
>					,DECODE(VOUCHER_TYPE,'53',WITH_HOLD_TAX_AMOUNT,	NULL)            CREDIT_AMOUNT
>					,STATUS          STATUS
>					,('TDS DEDUCTED'||CHR(10)||NARRATION) REMARKS
>			FROM  WITH_HOLD_TAX_DETAILS
>			WHERE COMPANY != '##'
>			AND    STATUS !='Z'
>			AND   (PARTY_TYPE = DECODE(VOUCHER_TYPE,'JV','S') OR PARTY_TYPE IS NULL)
>
>			UNION ALL
>			SELECT	 COMPANY
>					,'SJV'			REF_TABLE
>					,PARENT_RECID	VOUCHERS_RECID
>					,VOUCHER_TYPE    VOUCHER_TYPE
>					,VOUCHER_SERIES  VOUCHER_SERIES
>					,VOUCHER_NUMBER  VOUCHER_NO
>					,ENTRY_NO        ENTRY_NO
>					,VOUCHER_DATE    VOUCHER_DATE
>					,SUPPLIER_CODE   SUPPLIER_CODE
>					,NULL	  	    DUE_DATE
>					,NULL		    SUPPLIER_BILL_NO
>					,VOUCHER_DATE    SUPPLIER_BILL_DATE
>					,DECODE(SIGN(NET_AMOUNT),-1,-NET_AMOUNT,0) DEBIT_AMOUNT
>					,DECODE(SIGN(NET_AMOUNT),1,NET_AMOUNT,0) CREDIT_AMOUNT
>					,STATUS          STATUS
>					,NARRATION       REMARKS
>			FROM   SUPPLIER_JV_DETAILS
>			WHERE  COMPANY != '##'
>			AND     STATUS !='Z'
>
>		) A, SUPPLIERS S
>WHERE  A.COMPANY = S.COMPANY
>AND    A.SUPPLIER_CODE = S.SUPPLIER_CODE
>*/
>------------------------------------------------------------------------------------------------------------------------
>-- Trigger
>-- SPLR_DEBIT_NOTES_TPC 
>
>-- Line No. - 19
>
>		-- Bgn --
>		-- Against call id - 4940 
>		-- By ss on dt. 05/11/2020
>		-- As per create New voucher type for Credit note to Supplier without GST (C5)
>		-- Comment Old Code
>		/*
>		
>-- Line No. - 26
>
>		*/
>		IF o.DEBIT_NOTE_TYPE IN('51', '52', 'C1', 'C2', 'C5') THEN
>		-- End -- 
>		
>		
>-- Line No. - 33
>
>		-- Bgn --
>		-- Against call id - 4940 
>		-- By ss on dt. 05/11/2020
>		-- As per create New voucher type for Debit note to Supplier without GST (D3)
>		-- Comment Old Code 
>        /*
>
>-- Line No. - 40
>
>		*/
>		ELSIF o.DEBIT_NOTE_TYPE IN('53', '54', 'D1', 'D2', 'DN', 'D3') THEN
>		-- End --
>
>-- Line No. - 102
>
>			-- Bgn --
>			-- Against call id - 4940 
>			-- By ss on dt. 05/11/2020
>			-- As per create New voucher type for Credit note to Supplier without GST (C5)
>			-- Comment Old Code 
>			/*
>			
>
>-- Line No. - 109
>
>			*/
>			IF n.DEBIT_NOTE_TYPE IN('51', '52', 'C1', 'C2', 'C5') THEN			
>			-- End --  
>			
>-- Line No. - 166
>
>			-- Bgn --
>			-- Against call id - 4940 
>			-- By ss on dt. 05/11/2020
>			-- As per create New voucher type for Credit note to Supplier without GST (C5)
>			-- Comment Old Code 
>			/*
>
>-- Line No. - 173
>
>			*/
>			IF n.DEBIT_NOTE_TYPE IN('51', '52', 'C1', 'C2', 'C5') THEN 
>			-- End --	
>			
>-- Line No. 179
>
>			-- Bgn --
>			-- Against call id - 4940 
>			-- By ss on dt. 05/11/2020
>			-- As per create New voucher type for Debit note to Supplier without GST (D3)
>			-- Comment Old Code 
>			/*
>			
>-- Line No. 186
>
>			*/
>			ELSIF n.DEBIT_NOTE_TYPE IN('53', '54', 'D1', 'D2', 'DN', 'D3') THEN
>			-- End --
>               
>-- Line No. 215
>
>				-- Bgn --
>				-- Against call id - 4940 
>				-- By ss on dt. 05/11/2020
>				-- As per create New voucher type for Credit note to Supplier without GST (C5)
>				-- Comment Old Code 
>				/*
>				
>				
>-- Line No. - 222
>
>				*/
>				IF n.DEBIT_NOTE_TYPE IN('51', '52', 'C1', 'C2', 'C5') THEN 
>				-- End --

Actions: View

Attachments on bug 4940: 1381 | 1409