Attachment 2986 Details for Bug 9260 – Against_call_id_9260_NOIDA.csv

Against_call_id_9260_NOIDA.csv

Against_call_id_9260.GWF (text/plain), 75.91 KB, created by Sharad on 2022-08-24 17:29:05 IST

(hide)

Description:

Filename:

MIME Type:

Creator: Sharad

Created: 2022-08-24 17:29:05 IST

Size: 75.91 KB

patch

obsolete

>"REM WORKSPACETAB0",Query1,,171
>SELECT * FROM (
>SELECT	 A.DATA_SOURCE
>        ,A.COMP_CODE
>		,A.BILL_NO
>		,A.INVOICE_DATE
>		,A.SUPP_CODE
>        ,A.SUPP_BILL
>        ,A.SUPP_DATE
>        ,A.DOCUMENT_TYPE
>		,NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME
>		,A.DATA_TYPE
>		,A.DATA_DESC
>		,A.COMMODITY_TYPE
>		--,A.COMMODITY_CODE
>		,A.TAXABLE_AMT
>		,A.IGST_RATE
>		,A.CGST_RATE
>		,A.SGST_RATE
>		,A.CESS_RATE
>		,A.IGST_AMOUNT
>		,A.CGST_AMOUNT
>		,A.SGST_AMOUNT
>		,A.CESS_AMOUNT
>		,A.CESS_ON_QTY
>		,A.TOTAL_GST
>		,A.BILL_AMNT
>        ,A.GST_R_FLAG
>        ,A.GST_CREDIT_FLAG
>        ,A.STATUS
>        ,NVL(CASE WHEN A.DOCUMENT_TYPE 	IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ( REF_COMPANY, 'FG_TRANSFER_DR' )
>              WHEN A.DOCUMENT_TYPE 		IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ( REF_COMPANY, 'FG_RECEIVED_DR' )
>              WHEN A.DOCUMENT_TYPE 		IN ('AP'      ) AND A.STATUS != 'Z' THEN AP_DR_ACCOUNT_CODE
>        ELSE NULL
>        END,GRN_DR_ACCOUNT_CODE) DR_ACCOUNT_CODE
>        , ACNT_MODEL
>        , STORE_TYPE
>FROM (
>		SELECT
>		 A.DATA_SOURCE            DATA_SOURCE
>		,A.DOCUMENT_DATE          INVOICE_DATE
>		,A.DOCUMENT_STRING    	  BILL_NO
>		,A.DOCUMENT_TYPE 	      DOCUMENT_TYPE
>        ,A.AREF_NUMBER            SUPP_BILL
>        ,A.AREF_DATE              SUPP_DATE
>		,A.COMPANY_CODE           COMP_CODE
>		,A.SUPPLIER_CODE          SUPP_CODE
>		,0    		  			  DATA_TYPE
>		,'PB'                     DATA_DESC
>		,CASE WHEN ALLOW_GST_CREDIT = 'Y' THEN
>        CASE WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') ELSE DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') END
>    ELSE
>        DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') END
>   COMMODITY_TYPE
>		--,A.COMMODITY_CODE         	 COMMODITY_CODE
>		,SUM(A.TAXABLE_AMT) 	  	 TAXABLE_AMT
>        ,A.IGST_RATE
>        ,A.CGST_RATE
>        ,A.SGST_RATE
>        ,A.CESS_RATE
>        ,SUM(A.IGST_AMOUNT)      IGST_AMOUNT
>        ,SUM(A.CGST_AMOUNT) 	 CGST_AMOUNT
>        ,SUM(A.SGST_AMOUNT) 	 SGST_AMOUNT
>        ,SUM(A.CESS_AMOUNT) 	 CESS_AMOUNT
>        ,SUM(A.CESS_ON_QTY) 	 CESS_ON_QTY
>        ,SUM(NVL(IGST_AMOUNT, 0) + NVL(CGST_AMOUNT, 0) + NVL(SGST_AMOUNT, 0) + NVL(CESS_AMOUNT, 0) + NVL(CESS_ON_QTY, 0)) TOTAL_GST
>        -- Comment by ss on dt. 24/03/2018
>        --,ROUND(SUM(A.BILL_AMOUNT)) BILL_AMNT
>        ,ROUND(SUM(A.BILL_AMOUNT)- SUM(NVL(OTHR_AMOUNT,0))- SUM(NVL(IGST_OTHERS,0))- SUM(NVL(CGST_OTHERS,0))- SUM(NVL(SGST_OTHERS,0)))  BILL_AMNT
>        ,A.GST_REVERSE_FLAG                   GST_R_FLAG
>        ,A.ALLOW_GST_CREDIT                   GST_CREDIT_FLAG
>        ,A.STATUS
>        ,(SELECT COMPANY_CODE FROM VW_COMPANY_DETAIL WHERE SUPPLIER_CODE = A.SUPPLIER_CODE) REF_COMPANY
>        ,(SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = A.ITEM_CLASS AND COMPANY_CODE = A.COMPANY_CODE) ACNT_MODEL
>        ,(SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ) STORE_TYPE
>		,CASE WHEN A.DOCUMENT_TYPE IN ('44'		 ) AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = A.ITEM_CLASS AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE)
>        ELSE
>		 PKG_IC.GET_POSTING_ACCOUNT (A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE)
>        END GRN_DR_ACCOUNT_CODE
>		,CASE WHEN A.DOCUMENT_TYPE IN ('AP'		 ) AND A.STATUS != 'Z' THEN ( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE  COMPANY  = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE )
>        ELSE
>		 NULL
>        END AP_DR_ACCOUNT_CODE
>        FROM VW_PURCHASE_LEDGER A, COMMODITIES B
>		WHERE  PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '07AAACD1952B1Z8'
>		/*
>			-- Parameter add supplier gst number
>			-- By ss on dt. 10/08/2018
>		*/
>		--AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>		-- Add new parameter by ss on dt. 24/03/2018
>		--AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>  		AND (( 'E' ='E' AND A.DOCUMENT_DATE  BETWEEN  '01/04/2022' AND  '30/04/2022' )  OR ( 'E' ='B' AND A.AREF_DATE BETWEEN  '01/04/2022' AND  '30/04/2022' ))
>		AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>		GROUP BY A.DATA_SOURCE,A.DOCUMENT_DATE,A.DOCUMENT_STRING,A.DOCUMENT_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE,B.COMMODITY_TYPE, A.COMMODITY_CODE ,A.GST_REVERSE_FLAG ,A.ALLOW_GST_CREDIT ,A.STATUS, A.ITEM_CLASS, A.STORE_CODE,A.ITEM_CODE
>
>        UNION ALL
>		SELECT
>		 A.DATA_SOURCE           DATA_SOURCE
>		,A.LINK_REF_DATE         INVOICE_DATE
>		,A.LINK_REF_STRING       BILL_NO
>		,A.LINK_REF_TYPE         DOCUMENT_TYPE
>        ,A.AREF_NUMBER           SUPP_BILL
>        ,A.AREF_DATE             SUPP_DATE
>		,A.COMPANY_CODE          COMP_CODE
>		,A.SUPPLIER_CODE         SUPP_CODE
>		,1                       DATA_TYPE
>		,'OH'                    DATA_DESC
>		,CASE WHEN ALLOW_GST_CREDIT = 'Y' THEN
>        CASE WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') ELSE DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') END
>    ELSE
>        DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') END
>   COMMODITY_TYPE
>		--,A.COMMODITY_CODE        COMMODITY_CODE
>        ,SUM(A.TAXABLE_AMT) 	 TAXABLE_AMT
>        ,A.IGST_RATE
>        ,A.CGST_RATE
>        ,A.SGST_RATE
>        ,NULL CESS_RATE
>        ,SUM(A.IGST_OTHERS) 	 IGST_AMOUNT
>        ,SUM(A.CGST_OTHERS) 	 CGST_AMOUNT
>        ,SUM(A.SGST_OTHERS) 	 SGST_AMOUNT
>        ,NULL CESS_AMOUNT
>        ,NULL CESS_ON_QTY
>        ,SUM(NVL(IGST_OTHERS, 0) + NVL(CGST_OTHERS, 0) + NVL(SGST_OTHERS, 0))  TOTAL_GST
>        ,ROUND(SUM(NVL(IGST_OTHERS, 0) + NVL(CGST_OTHERS, 0) + NVL(SGST_OTHERS, 0) + NVL(TAXABLE_AMT,0)))			        				    BILL_AMNT
>        ,A.GST_REVERSE_FLAG                   GST_R_FLAG
>        ,A.ALLOW_GST_CREDIT                   GST_CREDIT_FLAG
>        ,A.STATUS          STATUS
>        ,(SELECT COMPANY_CODE FROM VW_COMPANY_DETAIL WHERE SUPPLIER_CODE = A.SUPPLIER_CODE) REF_COMPANY
>        ,(SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) AND COMPANY_CODE = A.COMPANY_CODE) ACNT_MODEL
>		,(SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ) STORE_TYPE
>		,CASE WHEN A.VOUCHER_TYPE IN ('44'		 ) AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE)
>        ELSE
>		 PKG_IC.GET_POSTING_ACCOUNT (A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE)
>        END GRN_DR_ACCOUNT_CODE
>		,CASE WHEN A.VOUCHER_TYPE IN ('AP'		 ) AND A.STATUS != 'Z' THEN ( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE  COMPANY  = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE )
>        ELSE
>		 NULL
>        END AP_DR_ACCOUNT_CODE
>		FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B
>		WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '07AAACD1952B1Z8'
>		/*
>			-- Parameter add supplier gst number
>			-- By ss on dt. 10/08/2018
>		*/
>		--AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>		-- Add new parameter by ss on dt. 24/03/2018
>		--AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>		/*
>		-- Bgn --
>		-- Against call id - 8839
>		--Communication gap by both user. So revert back all change.
>			-- Bgn --
>			-- Against call id - 8839
>			AND A.LINK_SPV_TYPE IS NOT NULL
>			-- End --
>		-- End --
>		*/
>  		AND (( 'E' ='E' AND A.LINK_REF_DATE BETWEEN  '01/04/2022' AND  '30/04/2022' )  OR ( 'E' ='B' AND A.AREF_DATE BETWEEN  '01/04/2022' AND  '30/04/2022' ))
>		AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>		GROUP BY A.DATA_SOURCE,A.LINK_REF_DATE,A.AREF_DATE, A.LINK_REF_STRING,A.LINK_REF_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE,B.COMMODITY_TYPE/*,A.COMMODITY_CODE*/,A.GST_REVERSE_FLAG,A.ALLOW_GST_CREDIT,A.STATUS,PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE), A.STORE_CODE,A.VOUCHER_TYPE,A.ITEM_CODE
>) A--, GL_POSTING_DETAILS B
>--WHERE A.COMP_CODE = B.COMPANY
>--AND A.BILL_NO = B.VOUCHER_TYPE||'/'||B.VOUCHER_SERIES||'/'||RTRIM(LTRIM(TO_CHAR(B.VOUCHER_NUMBER,'000000')))
>--AND B.AMOUNT < 0
>--WHERE DATA_SOURCE IN ('FGP')--NOT IN  ('FGR')
>ORDER BY  COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE
>)
>WHERE DR_ACCOUNT_CODE IS NULL
>--WHERE DATA_SOURCE = 'APV'
>ORDER BY 1, 3
>"REM WORKSPACETAB1","MAKE QUERY",,259
>SELECT
>	  A.DATA_SOURCE
>	, A.COMP_CODE
>	, A.BILL_NO
>	, A.INVOICE_DATE
>	, A.SUPP_CODE
>	, A.SUPP_BILL
>	, A.SUPP_DATE
>	, A.DOCUMENT_TYPE
>	, NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME
>	, A.DATA_TYPE
>	, A.DATA_DESC
>	, A.COMMODITY_TYPE
>	, SUM(A.TAXABLE_AMT)  				TAXABLE_AMT
>	, A.IGST_RATE
>	, A.CGST_RATE
>	, A.SGST_RATE
>	, A.CESS_RATE
>	, SUM(A.IGST_AMOUNT)				IGST_AMOUNT
>	, SUM(A.CGST_AMOUNT) 				CGST_AMOUNT
>	, SUM(A.SGST_AMOUNT) 				SGST_AMOUNT
>	, SUM(A.CESS_AMOUNT)				CESS_AMOUNT
>	, SUM(A.CESS_ON_QTY) 				CESS_ON_QTY
>	, SUM(A.TOTAL_GST  ) 				TOTAL_GST
>	, ROUND(SUM(A.BILL_AMNT))			BILL_AMNT
>	, A.GST_R_FLAG
>	, A.GST_CREDIT_FLAG
>	, A.STATUS
>	, NVL(
>			CASE
>				WHEN A.DOCUMENT_TYPE 	IN ('W7', 'DC', 'W5', '49') THEN DR_ACCOUNT_CODE
>				WHEN A.DOCUMENT_TYPE 	IN ('AP'      ) 			THEN AP_DR_ACCOUNT_CODE
>			ELSE
>				NULL
>			END
>		, GRN_DR_ACCOUNT_CODE
>		) DR_ACCOUNT_CODE
>FROM (
>		SELECT
>			  A.DATA_SOURCE            																							DATA_SOURCE
>			, A.DOCUMENT_DATE          																							INVOICE_DATE
>			, A.DOCUMENT_STRING    	  																							BILL_NO
>			, A.DOCUMENT_TYPE 	      																							DOCUMENT_TYPE
>			, A.AREF_NUMBER            																							SUPP_BILL
>			, A.AREF_DATE              																							SUPP_DATE
>			, A.COMPANY_CODE           																							COMP_CODE
>			, A.SUPPLIER_CODE          																							SUPP_CODE
>			, 0    		  			  																							DATA_TYPE
>			, 'PB'                     																							DATA_DESC
>		    , CASE
>				WHEN ALLOW_GST_CREDIT = 'Y' THEN
>        										CASE
>        											WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$')
>        										ELSE
>        											DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3')
>        										END
>    		  ELSE
>        	  	DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4')
>      		  END																												COMMODITY_TYPE
>			, A.TAXABLE_AMT 	  																								TAXABLE_AMT
>			, A.IGST_RATE
>			, A.CGST_RATE
>			, A.SGST_RATE
>			, A.CESS_RATE
>			, A.IGST_AMOUNT		      																							IGST_AMOUNT
>			, A.CGST_AMOUNT		 	 																							CGST_AMOUNT
>			, A.SGST_AMOUNT 	 																								SGST_AMOUNT
>			, A.CESS_AMOUNT 	 																								CESS_AMOUNT
>			, A.CESS_ON_QTY 	 																								CESS_ON_QTY
>			, NVL(IGST_AMOUNT, 0)
>			+ NVL(CGST_AMOUNT, 0)
>			+ NVL(SGST_AMOUNT, 0)
>			+ NVL(CESS_AMOUNT, 0)
>			+ NVL(CESS_ON_QTY, 0) 																								TOTAL_GST
>			,(
>			  A.BILL_AMOUNT
>			- NVL(OTHR_AMOUNT,0)
>			- NVL(IGST_OTHERS,0)
>			- NVL(CGST_OTHERS,0)
>			- NVL(SGST_OTHERS,0)
>			 ) 																													BILL_AMNT
>			, A.GST_REVERSE_FLAG        																						GST_R_FLAG
>			, A.ALLOW_GST_CREDIT        																						GST_CREDIT_FLAG
>			, A.STATUS
>	        , (
>	        	SELECT COMPANY_CODE
>	        	FROM VW_COMPANY_DETAIL
>	        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>	  		   ) 																												REF_COMPANY
>	        , (
>	        	SELECT ACNT_MODEL
>	        	FROM ITEM_CLASSES
>	        	WHERE ITEM_CLASS = A.ITEM_CLASS
>	        	AND COMPANY_CODE = A.COMPANY_CODE
>	          )																													ACNT_MODEL
>	        , (
>	        	SELECT STORE_TYPE
>	        	FROM VW_STORES
>	        	WHERE COMPANY_CODE 	= A.COMPANY_CODE
>	        	AND STORE_CODE 		= A.STORE_CODE
>	          ) 																												STORE_TYPE
>			,
>				CASE
>					WHEN A.DOCUMENT_TYPE 	IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																											        	SELECT COMPANY_CODE
>																											        	FROM VW_COMPANY_DETAIL
>																											        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																											  		   ), 'FG_TRANSFER_DR' )
>					WHEN A.DOCUMENT_TYPE 	IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																											        	SELECT COMPANY_CODE
>																											        	FROM VW_COMPANY_DETAIL
>																											        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																											  		   ), 'FG_RECEIVED_DR' )
>				ELSE
>					NULL
>				END																												DR_ACCOUNT_CODE
>			, CASE
>				WHEN A.DOCUMENT_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = A.ITEM_CLASS AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN
>					PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE)
>	        	ELSE
>			 		PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE)
>	        	END 																											GRN_DR_ACCOUNT_CODE
>			, CASE
>				WHEN A.DOCUMENT_TYPE IN ('AP') AND A.STATUS != 'Z' THEN
>					( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE  COMPANY  = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE )
>	    	  ELSE
>			 	NULL
>	     	  END 																												AP_DR_ACCOUNT_CODE
>        FROM VW_PURCHASE_LEDGER A, COMMODITIES B
>		WHERE  PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '09AAACD1952B1Z4'
>		/*
>			-- Parameter add supplier gst number
>			-- By ss on dt. 10/08/2018
>		*/
>		--AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>		-- Add new parameter by ss on dt. 24/03/2018
>		--AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>  		AND (( 'E' ='E' AND A.DOCUMENT_DATE  BETWEEN  '01/06/2022' AND  '30/06/2022' )  OR ( 'E' ='B' AND A.AREF_DATE BETWEEN  '01/06/2022' AND  '30/06/2022' ))
>		AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>		--GROUP BY A.DATA_SOURCE,A.DOCUMENT_DATE,A.DOCUMENT_STRING,A.DOCUMENT_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE,B.COMMODITY_TYPE, A.COMMODITY_CODE ,A.GST_REVERSE_FLAG ,A.ALLOW_GST_CREDIT ,A.STATUS, A.ITEM_CLASS, A.STORE_CODE,A.ITEM_CODE
>
>        UNION ALL
>
>		SELECT
>			  A.DATA_SOURCE           																							DATA_SOURCE
>			, A.LINK_REF_DATE         																							INVOICE_DATE
>			, A.LINK_REF_STRING       																							BILL_NO
>			, A.LINK_REF_TYPE         																							DOCUMENT_TYPE
>	        , A.AREF_NUMBER           																							SUPP_BILL
>	        , A.AREF_DATE             																							SUPP_DATE
>			, A.COMPANY_CODE          																							COMP_CODE
>			, A.SUPPLIER_CODE         																							SUPP_CODE
>			, 1                       																							DATA_TYPE
>			, 'OH'                    																							DATA_DESC
>			, CASE
>			  	WHEN ALLOW_GST_CREDIT = 'Y' THEN
>	          		CASE
>	          			WHEN GST_REVERSE_FLAG = 'N' THEN
>	          				NVL(B.COMMODITY_TYPE, '$')
>	          		ELSE
>	          			DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3')
>	          		END
>	    	  ELSE
>	        	DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4')
>	          END																												COMMODITY_TYPE
>			, A.TAXABLE_AMT 	 																								TAXABLE_AMT
>			, A.IGST_RATE
>			, A.CGST_RATE
>			, A.SGST_RATE
>	        , NULL 																												CESS_RATE
>	        , A.IGST_OTHERS		 	 																							IGST_AMOUNT
>	        , A.CGST_OTHERS 		 																							CGST_AMOUNT
>	        , A.SGST_OTHERS 	 																								SGST_AMOUNT
>	        , NULL CESS_AMOUNT
>	        , NULL CESS_ON_QTY
>	        ,(
>	          NVL(IGST_OTHERS, 0)
>	        + NVL(CGST_OTHERS, 0)
>	        + NVL(SGST_OTHERS, 0)
>	          )																													TOTAL_GST
>	        ,(
>	          NVL(IGST_OTHERS, 0)
>	        + NVL(CGST_OTHERS, 0)
>	        + NVL(SGST_OTHERS, 0)
>	        + NVL(TAXABLE_AMT, 0)
>	          )			        																								BILL_AMNT
>	        , A.GST_REVERSE_FLAG                   																				GST_R_FLAG
>	        , A.ALLOW_GST_CREDIT                   																				GST_CREDIT_FLAG
>	        , A.STATUS          																								STATUS
>	        , (
>	          	SELECT COMPANY_CODE
>	          	FROM VW_COMPANY_DETAIL
>	          	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>	           ) 																												REF_COMPANY
>	        , (
>	        	SELECT ACNT_MODEL
>	        	FROM ITEM_CLASSES
>	        	WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE)
>	        	AND COMPANY_CODE = A.COMPANY_CODE
>	           ) 																												ACNT_MODEL
>			, (
>				SELECT STORE_TYPE
>				FROM VW_STORES
>				WHERE COMPANY_CODE = A.COMPANY_CODE
>				AND STORE_CODE = A.STORE_CODE
>			   ) 																												STORE_TYPE
>			,
>				CASE
>					WHEN A.VOUCHER_TYPE 	IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																											        	SELECT COMPANY_CODE
>																											        	FROM VW_COMPANY_DETAIL
>																											        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																											  		   ), 'FG_TRANSFER_DR' )
>					WHEN A.VOUCHER_TYPE 	IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																											        	SELECT COMPANY_CODE
>																											        	FROM VW_COMPANY_DETAIL
>																											        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																											  		   ), 'FG_RECEIVED_DR' )
>				ELSE
>					NULL
>				END																												DR_ACCOUNT_CODE
>			, CASE
>			  	WHEN A.VOUCHER_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN
>			  		PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE)
>	          ELSE
>			 	PKG_IC.GET_POSTING_ACCOUNT (A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE)
>	          END 																												GRN_DR_ACCOUNT_CODE
>			, CASE
>			  	WHEN A.VOUCHER_TYPE IN ('AP') AND A.STATUS != 'Z' THEN
>			  	( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE  COMPANY  = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE )
>	          ELSE
>			 	NULL
>	          END 																												AP_DR_ACCOUNT_CODE
>		FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B
>		WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '09AAACD1952B1Z4'
>		/*
>			-- Parameter add supplier gst number
>			-- By ss on dt. 10/08/2018
>		*/
>		--AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>		-- Add new parameter by ss on dt. 24/03/2018
>		--AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>		/*
>		-- Bgn --
>		-- Against call id - 8839
>		--Communication gap by both user. So revert back all change.
>			-- Bgn --
>			-- Against call id - 8839
>			AND A.LINK_SPV_TYPE IS NOT NULL
>			-- End --
>		-- End --
>		*/
>  		AND (( 'E' ='E' AND A.LINK_REF_DATE BETWEEN  '01/06/2022' AND  '30/06/2022' )  OR ( 'E' ='B' AND A.AREF_DATE BETWEEN  '01/06/2022' AND  '30/06/2022' ))
>		AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>--		GROUP BY A.DATA_SOURCE,A.LINK_REF_DATE,A.AREF_DATE, A.LINK_REF_STRING,A.LINK_REF_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE,B.COMMODITY_TYPE/*,A.COMMODITY_CODE*/,A.GST_REVERSE_FLAG,A.ALLOW_GST_CREDIT,A.STATUS,PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE), A.STORE_CODE,A.VOUCHER_TYPE,A.ITEM_CODE
>	) A
>GROUP BY A.DATA_SOURCE, A.COMP_CODE, A.BILL_NO, A.INVOICE_DATE, A.SUPP_CODE, A.SUPP_BILL, A.SUPP_DATE, A.DOCUMENT_TYPE, A.DATA_TYPE, A.DATA_DESC, A.COMMODITY_TYPE, A.IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE, A.GST_R_FLAG, A.GST_CREDIT_FLAG, A.STATUS, DR_ACCOUNT_CODE, AP_DR_ACCOUNT_CODE, GRN_DR_ACCOUNT_CODE
>ORDER BY  COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE
>
>"REM WORKSPACETAB2","FINAL QUERY FOR PROCEDURE",,317
>INSERT INTO TT$INV_WISE_PURCHASE_DTL
>(
>	  COMMODITY_TYPE
>	, COMMODITY_DESC
>	, COMP_CODE
>	, SHORT_NAME
>	, DOCUMENT_STRING
>	, DOCUMENT_DATE
>	, SUPP_BILL_NUMBER
>	, SUPP_BILL_DATE
>	, SUPPLIER_CODE
>	, SUPPLIER_NAME
>	, SUPPLIER_GST_NUMBER
>	, DATA_DESC
>	, ACCOUNT_CODE
>	, ACCOUNT_NAME
>	, SUB_ACCOUNT_CODE
>	, SUB_ACCOUNT_NAME
>	, TAXABLE_AMOUNT
>	, IGST_RATE
>	, IGST_AMOUNT
>	, CGST_RATE
>	, CGST_AMOUNT
>	, SGST_RATE
>	, SGST_AMOUNT
>	, CESS_RATE
>	, CESS_AMOUNT
>	, CESS_ON_QTY
>	, TOTAL_GST
>	, BILL_AMOUNT
>)
>SELECT
>	  COMMODITY_TYPE
>	, COMMODITY_DESC
>	, COMP_CODE
>	, SHORT_NAME
>	, BILL_NO
>	, INVOICE_DATE
>	, SUPP_BILL
>	, SUPP_DATE
>	, SUPP_CODE
>	, SUPP_NAME
>	, GST_NUMBER
>	, DATA_DESC
>	, SUBSTR(DR_ACCOUNT_CODE,0,5) ACC_CODE
>	, PKG_GL.GET_ACCOUNT_NAME(SUBSTR(DR_ACCOUNT_CODE,0,5))  ACC_DESC
>	, SUBSTR(DR_ACCOUNT_CODE,6,9) SUB_ACC_CODE
>	, PKG_GL.GET_ACCOUNT_NAME(DR_ACCOUNT_CODE) SUB_ACC_DESC
>	, TAXABLE_AMT
>	, IGST_RATE
>	, IGST_AMOUNT
>	, CGST_RATE
>	, CGST_AMOUNT
>	, SGST_RATE
>	, SGST_AMOUNT
>	, CESS_RATE
>	, CESS_AMOUNT
>	, CESS_ON_QTY
>	, TOTAL_GST
>	, BILL_AMNT
>FROM
>	(
>
>		SELECT
>			  A.DATA_SOURCE
>			, A.COMP_CODE
>			, PKG_CONTROLS.GET_COMPANY_INFO(A.COMP_CODE, 'SHORT_NAME') SHORT_NAME
>			, A.BILL_NO
>			, A.INVOICE_DATE
>			, A.SUPP_CODE
>			, (SELECT GST_NUMBER FROM VW_SUPPLIERS WHERE COMPANY_CODE = A.COMP_CODE AND SUPPLIER_CODE = A.SUPP_CODE) GST_NUMBER
>			, A.SUPP_BILL
>			, A.SUPP_DATE
>			, A.DOCUMENT_TYPE
>			, NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME
>			, A.DATA_TYPE
>			, A.DATA_DESC
>			, A.COMMODITY_TYPE
>			, CASE
>			  	WHEN A.COMMODITY_TYPE = '#1' THEN
>					'PURCHASE OF GOODS UNDER REVERSE CHARGE'
>				WHEN A.COMMODITY_TYPE = '#2' THEN
>					'PURCHASE OF GOODS UNDER GST CREDIT NOT ALLOWED'
>				WHEN A.COMMODITY_TYPE = '#3' THEN
>					'PURCHASE OF SERVICES UNDER REVERSE CHARGE'
>				WHEN A.COMMODITY_TYPE = '#4' THEN
>					'PURCHASE OF SERVICES UNDER GST NOT ALLOWED'
>				WHEN A.COMMODITY_TYPE = '$' THEN
>					'PURCHASES WITH MISSING HSN'
>				ELSE
>					NVL(PKG_POPLISTS.POPDATA_DESCRIPTION('CMDTTYPE',A.COMMODITY_TYPE),A.COMMODITY_TYPE)
>			  END 								COMMODITY_DESC
>			, SUM(A.TAXABLE_AMT)  				TAXABLE_AMT
>			, A.IGST_RATE
>			, A.CGST_RATE
>			, A.SGST_RATE
>			, A.CESS_RATE
>			, SUM(A.IGST_AMOUNT)				IGST_AMOUNT
>			, SUM(A.CGST_AMOUNT) 				CGST_AMOUNT
>			, SUM(A.SGST_AMOUNT) 				SGST_AMOUNT
>			, SUM(A.CESS_AMOUNT)				CESS_AMOUNT
>			, SUM(A.CESS_ON_QTY) 				CESS_ON_QTY
>			, SUM(A.TOTAL_GST  ) 				TOTAL_GST
>			, ROUND(SUM(A.BILL_AMNT))			BILL_AMNT
>			, A.GST_R_FLAG
>			, A.GST_CREDIT_FLAG
>			, A.STATUS
>			, NVL(
>					CASE
>						WHEN A.DOCUMENT_TYPE 	IN ('W7', 'DC', 'W5', '49') THEN DR_ACCOUNT_CODE
>						WHEN A.DOCUMENT_TYPE 	IN ('AP'      ) 			THEN AP_DR_ACCOUNT_CODE
>					ELSE
>						NULL
>					END
>				, GRN_DR_ACCOUNT_CODE
>				) DR_ACCOUNT_CODE
>		FROM (
>				SELECT
>					  A.DATA_SOURCE            																							DATA_SOURCE
>					, A.DOCUMENT_DATE          																							INVOICE_DATE
>					, A.DOCUMENT_STRING    	  																							BILL_NO
>					, A.DOCUMENT_TYPE 	      																							DOCUMENT_TYPE
>					, A.AREF_NUMBER            																							SUPP_BILL
>					, A.AREF_DATE              																							SUPP_DATE
>					, A.COMPANY_CODE           																							COMP_CODE
>					, A.SUPPLIER_CODE          																							SUPP_CODE
>					, 0    		  			  																							DATA_TYPE
>					, 'PB'                     																							DATA_DESC
>				    , CASE
>						WHEN ALLOW_GST_CREDIT = 'Y' THEN
>		        										CASE
>		        											WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$')
>		        										ELSE
>		        											DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3')
>		        										END
>		    		  ELSE
>		        	  	DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4')
>		      		  END																												COMMODITY_TYPE
>					, A.TAXABLE_AMT 	  																								TAXABLE_AMT
>					, A.IGST_RATE
>					, A.CGST_RATE
>					, A.SGST_RATE
>					, A.CESS_RATE
>					, A.IGST_AMOUNT		      																							IGST_AMOUNT
>					, A.CGST_AMOUNT		 	 																							CGST_AMOUNT
>					, A.SGST_AMOUNT 	 																								SGST_AMOUNT
>					, A.CESS_AMOUNT 	 																								CESS_AMOUNT
>					, A.CESS_ON_QTY 	 																								CESS_ON_QTY
>					, NVL(IGST_AMOUNT, 0)
>					+ NVL(CGST_AMOUNT, 0)
>					+ NVL(SGST_AMOUNT, 0)
>					+ NVL(CESS_AMOUNT, 0)
>					+ NVL(CESS_ON_QTY, 0) 																								TOTAL_GST
>					,(
>					  A.BILL_AMOUNT
>					- NVL(OTHR_AMOUNT,0)
>					- NVL(IGST_OTHERS,0)
>					- NVL(CGST_OTHERS,0)
>					- NVL(SGST_OTHERS,0)
>					 ) 																													BILL_AMNT
>					, A.GST_REVERSE_FLAG        																						GST_R_FLAG
>					, A.ALLOW_GST_CREDIT        																						GST_CREDIT_FLAG
>					, A.STATUS
>			        , (
>			        	SELECT COMPANY_CODE
>			        	FROM VW_COMPANY_DETAIL
>			        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>			  		   ) 																												REF_COMPANY
>			        , (
>			        	SELECT ACNT_MODEL
>			        	FROM ITEM_CLASSES
>			        	WHERE ITEM_CLASS = A.ITEM_CLASS
>			        	AND COMPANY_CODE = A.COMPANY_CODE
>			          )																													ACNT_MODEL
>			        , (
>			        	SELECT STORE_TYPE
>			        	FROM VW_STORES
>			        	WHERE COMPANY_CODE 	= A.COMPANY_CODE
>			        	AND STORE_CODE 		= A.STORE_CODE
>			          ) 																												STORE_TYPE
>					,
>						CASE
>							WHEN A.DOCUMENT_TYPE 	IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																													        	SELECT COMPANY_CODE
>																													        	FROM VW_COMPANY_DETAIL
>																													        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																													  		   ), 'FG_TRANSFER_DR' )
>							WHEN A.DOCUMENT_TYPE 	IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																													        	SELECT COMPANY_CODE
>																													        	FROM VW_COMPANY_DETAIL
>																													        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																													  		   ), 'FG_RECEIVED_DR' )
>						ELSE
>							NULL
>						END																												DR_ACCOUNT_CODE
>					, CASE
>						WHEN A.DOCUMENT_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = A.ITEM_CLASS AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN
>							PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE)
>			        	ELSE
>					 		PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE)
>			        	END 																											GRN_DR_ACCOUNT_CODE
>					, CASE
>						WHEN A.DOCUMENT_TYPE IN ('AP') AND A.STATUS != 'Z' THEN
>							( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE  COMPANY  = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE )
>			    	  ELSE
>					 	NULL
>			     	  END 																												AP_DR_ACCOUNT_CODE
>				FROM VW_PURCHASE_LEDGER A, COMMODITIES B
>				WHERE  PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = :P_GST_NUMBER
>				AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>				AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>				AND (( :P_RCPT_BILL_DATE ='E' AND A.DOCUMENT_DATE  BETWEEN  :P_QRY_BGN_DATE AND  :P_QRY_END_DATE )  OR ( :P_RCPT_BILL_DATE ='B' AND A.AREF_DATE BETWEEN  :P_QRY_BGN_DATE AND  :P_QRY_END_DATE ))
>				AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>
>		        UNION ALL
>
>				SELECT
>					  A.DATA_SOURCE           																							DATA_SOURCE
>					, A.LINK_REF_DATE         																							INVOICE_DATE
>					, A.LINK_REF_STRING       																							BILL_NO
>					, A.LINK_REF_TYPE         																							DOCUMENT_TYPE
>			        , A.AREF_NUMBER           																							SUPP_BILL
>			        , A.AREF_DATE             																							SUPP_DATE
>					, A.COMPANY_CODE          																							COMP_CODE
>					, A.SUPPLIER_CODE         																							SUPP_CODE
>					, 1                       																							DATA_TYPE
>					, 'OH'                    																							DATA_DESC
>					, CASE
>					  	WHEN ALLOW_GST_CREDIT = 'Y' THEN
>			          		CASE
>			          			WHEN GST_REVERSE_FLAG = 'N' THEN
>			          				NVL(B.COMMODITY_TYPE, '$')
>			          		ELSE
>			          			DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3')
>			          		END
>			    	  ELSE
>			        	DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4')
>			          END																												COMMODITY_TYPE
>					, A.TAXABLE_AMT 	 																								TAXABLE_AMT
>					, A.IGST_RATE
>					, A.CGST_RATE
>					, A.SGST_RATE
>			        , NULL 																												CESS_RATE
>			        , A.IGST_OTHERS		 	 																							IGST_AMOUNT
>			        , A.CGST_OTHERS 		 																							CGST_AMOUNT
>			        , A.SGST_OTHERS 	 																								SGST_AMOUNT
>			        , NULL CESS_AMOUNT
>			        , NULL CESS_ON_QTY
>			        ,(
>			          NVL(IGST_OTHERS, 0)
>			        + NVL(CGST_OTHERS, 0)
>			        + NVL(SGST_OTHERS, 0)
>			          )																													TOTAL_GST
>			        ,(
>			          NVL(IGST_OTHERS, 0)
>			        + NVL(CGST_OTHERS, 0)
>			        + NVL(SGST_OTHERS, 0)
>			        + NVL(TAXABLE_AMT, 0)
>			          )			        																								BILL_AMNT
>			        , A.GST_REVERSE_FLAG                   																				GST_R_FLAG
>			        , A.ALLOW_GST_CREDIT                   																				GST_CREDIT_FLAG
>			        , A.STATUS          																								STATUS
>			        , (
>			          	SELECT COMPANY_CODE
>			          	FROM VW_COMPANY_DETAIL
>			          	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>			           ) 																												REF_COMPANY
>			        , (
>			        	SELECT ACNT_MODEL
>			        	FROM ITEM_CLASSES
>			        	WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE)
>			        	AND COMPANY_CODE = A.COMPANY_CODE
>			           ) 																												ACNT_MODEL
>					, (
>						SELECT STORE_TYPE
>						FROM VW_STORES
>						WHERE COMPANY_CODE = A.COMPANY_CODE
>						AND STORE_CODE = A.STORE_CODE
>					   ) 																												STORE_TYPE
>					,
>						CASE
>							WHEN A.VOUCHER_TYPE 	IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																													        	SELECT COMPANY_CODE
>																													        	FROM VW_COMPANY_DETAIL
>																													        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																													  		   ), 'FG_TRANSFER_DR' )
>							WHEN A.VOUCHER_TYPE 	IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																													        	SELECT COMPANY_CODE
>																													        	FROM VW_COMPANY_DETAIL
>																													        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																													  		   ), 'FG_RECEIVED_DR' )
>						ELSE
>							NULL
>						END																												DR_ACCOUNT_CODE
>					, CASE
>					  	WHEN A.VOUCHER_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN
>					  		PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE)
>			          ELSE
>					 	PKG_IC.GET_POSTING_ACCOUNT (A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE)
>			          END 																												GRN_DR_ACCOUNT_CODE
>					, CASE
>					  	WHEN A.VOUCHER_TYPE IN ('AP') AND A.STATUS != 'Z' THEN
>					  	( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE  COMPANY  = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE )
>			          ELSE
>					 	NULL
>			          END 																												AP_DR_ACCOUNT_CODE
>				FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B
>				FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B
>				WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = :P_GST_NUMBER
>				AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>				AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>		  		AND (( :P_RCPT_BILL_DATE ='E' AND A.LINK_REF_DATE BETWEEN  :P_QRY_BGN_DATE AND  :P_QRY_END_DATE )  OR ( :P_RCPT_BILL_DATE ='B' AND A.AREF_DATE BETWEEN  :P_QRY_BGN_DATE AND  :P_QRY_END_DATE ))
>				AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>			) A
>		GROUP BY A.DATA_SOURCE, A.COMP_CODE, A.BILL_NO, A.INVOICE_DATE, A.SUPP_CODE, A.SUPP_BILL, A.SUPP_DATE, A.DOCUMENT_TYPE, A.DATA_TYPE, A.DATA_DESC, A.COMMODITY_TYPE, A.IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE, A.GST_R_FLAG, A.GST_CREDIT_FLAG, A.STATUS, DR_ACCOUNT_CODE, AP_DR_ACCOUNT_CODE, GRN_DR_ACCOUNT_CODE
>		)
>       ORDER BY  COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE
>"REM WORKSPACETAB3",Query43,,1
>EXEC PKG_PR.INV_WISE_PURCH_DTL('09AAACD1952B1Z4', NULL, NULL, 'E', '01/06/2022', '30/06/2022')
>"REM WORKSPACETAB4",Query44,,4
>SELECT * FROM TT$INV_WISE_PURCHASE_DTL
>WHERE ACCOUNT_CODE IS NULL
>AND STATUS !='Z'
>ORDER BY  COMMODITY_TYPE, DOCUMENT_DATE, DOCUMENT_STRING, DATA_TYPE
>"REM WORKSPACETAB5",Query41,,339
>/*
>DELETE FROM TT$INV_WISE_PURCHASE_DTL;
>INSERT INTO TT$INV_WISE_PURCHASE_DTL
>(
>	  COMMODITY_TYPE
>	, COMMODITY_DESC
>	, COMP_CODE
>	, SHORT_NAME
>	, DOCUMENT_STRING
>	, DOCUMENT_DATE
>	, SUPP_BILL_NUMBER
>	, SUPP_BILL_DATE
>	, SUPPLIER_CODE
>	, SUPPLIER_NAME
>	, SUPPLIER_GST_NUMBER
>	, DATA_DESC
>	, ACCOUNT_CODE
>	, ACCOUNT_NAME
>	, SUB_ACCOUNT_CODE
>	, SUB_ACCOUNT_NAME
>	, TAXABLE_AMOUNT
>	, IGST_RATE
>	, IGST_AMOUNT
>	, CGST_RATE
>	, CGST_AMOUNT
>	, SGST_RATE
>	, SGST_AMOUNT
>	, CESS_RATE
>	, CESS_AMOUNT
>	, CESS_ON_QTY
>	, TOTAL_GST
>	, BILL_AMOUNT
>)
>*/
>SELECT
>--/*
>
>	  COMMODITY_TYPE
>	, COMMODITY_DESC
>	, COMP_CODE
>	, SHORT_NAME
>	, BILL_NO
>	, INVOICE_DATE
>	, SUPP_BILL
>	, SUPP_DATE
>	, SUPP_CODE
>	, SUPP_NAME
>	, GST_NUMBER
>	, DATA_DESC
>	, SUBSTR(DR_ACCOUNT_CODE,0,5) ACC_CODE
>	, PKG_GL.GET_ACCOUNT_NAME(SUBSTR(DR_ACCOUNT_CODE,0,5))  ACC_DESC
>	, SUBSTR(DR_ACCOUNT_CODE,6,9) SUB_ACC_CODE
>	, PKG_GL.GET_ACCOUNT_NAME(DR_ACCOUNT_CODE) SUB_ACC_DESC
>	, TAXABLE_AMT
>	, IGST_RATE
>	, IGST_AMOUNT
>	, CGST_RATE
>	, CGST_AMOUNT
>	, SGST_RATE
>	, SGST_AMOUNT
>	, CESS_RATE
>	, CESS_AMOUNT
>	, CESS_ON_QTY
>	, TOTAL_GST
>	, BILL_AMNT
>	, STATUS
>--*/
>/*
>      --DOCUMENT_TYPE
>	  SUM(TAXABLE_AMT)
>	, SUM(IGST_AMOUNT)
>	, SUM(CGST_AMOUNT)
>	, SUM(SGST_AMOUNT)
>	, SUM(CESS_AMOUNT)
>	, SUM(CESS_ON_QTY)
>	, SUM(TOTAL_GST)
>	, ROUND(SUM(BILL_AMNT))
>*/
>FROM
>	(
>
>		SELECT
>			  A.DATA_SOURCE
>			, A.COMP_CODE
>			, PKG_CONTROLS.GET_COMPANY_INFO(A.COMP_CODE, 'SHORT_NAME') SHORT_NAME
>			, A.BILL_NO
>			, A.INVOICE_DATE
>			, A.SUPP_CODE
>			, (SELECT GST_NUMBER FROM VW_SUPPLIERS WHERE COMPANY_CODE = A.COMP_CODE AND SUPPLIER_CODE = A.SUPP_CODE) GST_NUMBER
>			, A.SUPP_BILL
>			, A.SUPP_DATE
>			, A.DOCUMENT_TYPE
>			, NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME
>			, A.DATA_TYPE
>			, A.DATA_DESC
>			, A.COMMODITY_TYPE
>			, CASE
>			  	WHEN A.COMMODITY_TYPE = '#1' THEN
>					'PURCHASE OF GOODS UNDER REVERSE CHARGE'
>				WHEN A.COMMODITY_TYPE = '#2' THEN
>					'PURCHASE OF GOODS UNDER GST CREDIT NOT ALLOWED'
>				WHEN A.COMMODITY_TYPE = '#3' THEN
>					'PURCHASE OF SERVICES UNDER REVERSE CHARGE'
>				WHEN A.COMMODITY_TYPE = '#4' THEN
>					'PURCHASE OF SERVICES UNDER GST NOT ALLOWED'
>				WHEN A.COMMODITY_TYPE = '$' THEN
>					'PURCHASES WITH MISSING HSN'
>				ELSE
>					NVL(PKG_POPLISTS.POPDATA_DESCRIPTION('CMDTTYPE',A.COMMODITY_TYPE),A.COMMODITY_TYPE)
>			  END 								COMMODITY_DESC
>			, SUM(A.TAXABLE_AMT)  				TAXABLE_AMT
>			, A.IGST_RATE
>			, A.CGST_RATE
>			, A.SGST_RATE
>			, A.CESS_RATE
>			, SUM(A.IGST_AMOUNT)				IGST_AMOUNT
>			, SUM(A.CGST_AMOUNT) 				CGST_AMOUNT
>			, SUM(A.SGST_AMOUNT) 				SGST_AMOUNT
>			, SUM(A.CESS_AMOUNT)				CESS_AMOUNT
>			, SUM(A.CESS_ON_QTY) 				CESS_ON_QTY
>			, SUM(A.TOTAL_GST  ) 				TOTAL_GST
>			, SUM(A.BILL_AMNT)					BILL_AMNT
>			, A.GST_R_FLAG
>			, A.GST_CREDIT_FLAG
>			, A.STATUS
>			, NVL(
>					CASE
>						WHEN A.DOCUMENT_TYPE 	IN ('W7', 'DC', 'W5', '49') THEN DR_ACCOUNT_CODE
>						WHEN A.DOCUMENT_TYPE 	IN ('AP'      ) 			THEN AP_DR_ACCOUNT_CODE
>					ELSE
>						NULL
>					END
>				, GRN_DR_ACCOUNT_CODE
>				) DR_ACCOUNT_CODE
>		FROM (
>				SELECT
>					  A.DATA_SOURCE            																							DATA_SOURCE
>					, A.DOCUMENT_DATE          																							INVOICE_DATE
>					, A.DOCUMENT_STRING    	  																							BILL_NO
>					, A.DOCUMENT_TYPE 	      																							DOCUMENT_TYPE
>					, A.AREF_NUMBER            																							SUPP_BILL
>					, A.AREF_DATE              																							SUPP_DATE
>					, A.COMPANY_CODE           																							COMP_CODE
>					, A.SUPPLIER_CODE          																							SUPP_CODE
>					, 0    		  			  																							DATA_TYPE
>					, 'PB'                     																							DATA_DESC
>				    , CASE
>						WHEN ALLOW_GST_CREDIT = 'Y' THEN
>		        										CASE
>		        											WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$')
>		        										ELSE
>		        											DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3')
>		        										END
>		    		  ELSE
>		        	  	DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4')
>		      		  END																												COMMODITY_TYPE
>					, A.TAXABLE_AMT 	  																								TAXABLE_AMT
>					, A.IGST_RATE
>					, A.CGST_RATE
>					, A.SGST_RATE
>					, A.CESS_RATE
>					, A.IGST_AMOUNT		      																							IGST_AMOUNT
>					, A.CGST_AMOUNT		 	 																							CGST_AMOUNT
>					, A.SGST_AMOUNT 	 																								SGST_AMOUNT
>					, A.CESS_AMOUNT 	 																								CESS_AMOUNT
>					, A.CESS_ON_QTY 	 																								CESS_ON_QTY
>					, NVL(IGST_AMOUNT, 0)
>					+ NVL(CGST_AMOUNT, 0)
>					+ NVL(SGST_AMOUNT, 0)
>					+ NVL(CESS_AMOUNT, 0)
>					+ NVL(CESS_ON_QTY, 0) 																								TOTAL_GST
>					,(
>					  A.BILL_AMOUNT
>					- NVL(OTHR_AMOUNT,0)
>					- NVL(IGST_OTHERS,0)
>					- NVL(CGST_OTHERS,0)
>					- NVL(SGST_OTHERS,0)
>					  )																													BILL_AMNT
>					, A.GST_REVERSE_FLAG        																						GST_R_FLAG
>					, A.ALLOW_GST_CREDIT        																						GST_CREDIT_FLAG
>					, A.STATUS
>			        , (
>			        	SELECT COMPANY_CODE
>			        	FROM VW_COMPANY_DETAIL
>			        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>			  		   ) 																												REF_COMPANY
>			        , (
>			        	SELECT ACNT_MODEL
>			        	FROM ITEM_CLASSES
>			        	WHERE ITEM_CLASS = A.ITEM_CLASS
>			        	AND COMPANY_CODE = A.COMPANY_CODE
>			          )																													ACNT_MODEL
>			        , (
>			        	SELECT STORE_TYPE
>			        	FROM VW_STORES
>			        	WHERE COMPANY_CODE 	= A.COMPANY_CODE
>			        	AND STORE_CODE 		= A.STORE_CODE
>			          ) 																												STORE_TYPE
>					,
>						CASE
>							WHEN A.DOCUMENT_TYPE 	IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																													        	SELECT COMPANY_CODE
>																													        	FROM VW_COMPANY_DETAIL
>																													        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																													  		   ), 'FG_TRANSFER_DR' )
>							WHEN A.DOCUMENT_TYPE 	IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																													        	SELECT COMPANY_CODE
>																													        	FROM VW_COMPANY_DETAIL
>																													        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																													  		   ), 'FG_RECEIVED_DR' )
>						ELSE
>							NULL
>						END																												DR_ACCOUNT_CODE
>					, CASE
>						WHEN A.DOCUMENT_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = A.ITEM_CLASS AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN
>							PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE)
>			        	ELSE
>					 		PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE)
>			        	END 																											GRN_DR_ACCOUNT_CODE
>					, CASE
>						WHEN A.DOCUMENT_TYPE IN ('AP') AND A.STATUS != 'Z' THEN
>							( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE  COMPANY  = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE )
>			    	  ELSE
>					 	NULL
>			     	  END 																												AP_DR_ACCOUNT_CODE
>				FROM VW_PURCHASE_LEDGER A, COMMODITIES B
>				WHERE  PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '09AAACD1952B1Z4'
>				--AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>				--AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>				AND (( 'E' ='E' AND A.DOCUMENT_DATE  BETWEEN  '01/06/2022' AND  '30/06/2022' )  OR ( 'E' ='B' AND A.AREF_DATE BETWEEN  '01/06/2022' AND  '30/06/2022' ))
>				AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>
>		        UNION ALL
>
>				SELECT
>					  A.DATA_SOURCE           																							DATA_SOURCE
>					, A.LINK_REF_DATE         																							INVOICE_DATE
>					, A.LINK_REF_STRING       																							BILL_NO
>					, A.LINK_REF_TYPE         																							DOCUMENT_TYPE
>			        , A.AREF_NUMBER           																							SUPP_BILL
>			        , A.AREF_DATE             																							SUPP_DATE
>					, A.COMPANY_CODE          																							COMP_CODE
>					, A.SUPPLIER_CODE         																							SUPP_CODE
>					, 1                       																							DATA_TYPE
>					, 'OH'                    																							DATA_DESC
>					, CASE
>					  	WHEN ALLOW_GST_CREDIT = 'Y' THEN
>			          		CASE
>			          			WHEN GST_REVERSE_FLAG = 'N' THEN
>			          				NVL(B.COMMODITY_TYPE, '$')
>			          		ELSE
>			          			DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3')
>			          		END
>			    	  ELSE
>			        	DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4')
>			          END																												COMMODITY_TYPE
>					, A.TAXABLE_AMT 	 																								TAXABLE_AMT
>					, A.IGST_RATE
>					, A.CGST_RATE
>					, A.SGST_RATE
>			        , NULL 																												CESS_RATE
>			        , A.IGST_OTHERS		 	 																							IGST_AMOUNT
>			        , A.CGST_OTHERS 		 																							CGST_AMOUNT
>			        , A.SGST_OTHERS 	 																								SGST_AMOUNT
>			        , NULL CESS_AMOUNT
>			        , NULL CESS_ON_QTY
>			        ,(
>			          NVL(IGST_OTHERS, 0)
>			        + NVL(CGST_OTHERS, 0)
>			        + NVL(SGST_OTHERS, 0)
>			          )																													TOTAL_GST
>			        ,(
>			          NVL(IGST_OTHERS, 0)
>			        + NVL(CGST_OTHERS, 0)
>			        + NVL(SGST_OTHERS, 0)
>			        + NVL(TAXABLE_AMT, 0)
>			          )		        																									BILL_AMNT
>			        , A.GST_REVERSE_FLAG                   																				GST_R_FLAG
>			        , A.ALLOW_GST_CREDIT                   																				GST_CREDIT_FLAG
>			        , A.STATUS          																								STATUS
>			        , (
>			          	SELECT COMPANY_CODE
>			          	FROM VW_COMPANY_DETAIL
>			          	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>			           ) 																												REF_COMPANY
>			        , (
>			        	SELECT ACNT_MODEL
>			        	FROM ITEM_CLASSES
>			        	WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE)
>			        	AND COMPANY_CODE = A.COMPANY_CODE
>			           ) 																												ACNT_MODEL
>					, (
>						SELECT STORE_TYPE
>						FROM VW_STORES
>						WHERE COMPANY_CODE = A.COMPANY_CODE
>						AND STORE_CODE = A.STORE_CODE
>					   ) 																												STORE_TYPE
>					,
>						CASE
>							WHEN A.VOUCHER_TYPE 	IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																													        	SELECT COMPANY_CODE
>																													        	FROM VW_COMPANY_DETAIL
>																													        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																													  		   ), 'FG_TRANSFER_DR' )
>							WHEN A.VOUCHER_TYPE 	IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																													        	SELECT COMPANY_CODE
>																													        	FROM VW_COMPANY_DETAIL
>																													        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																													  		   ), 'FG_RECEIVED_DR' )
>						ELSE
>							NULL
>						END																												DR_ACCOUNT_CODE
>					, CASE
>					  	WHEN A.VOUCHER_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN
>					  		PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE)
>			          ELSE
>					 	PKG_IC.GET_POSTING_ACCOUNT (A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE)
>			          END 																												GRN_DR_ACCOUNT_CODE
>					, CASE
>					  	WHEN A.VOUCHER_TYPE IN ('AP') AND A.STATUS != 'Z' THEN
>					  	( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE  COMPANY  = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE )
>			          ELSE
>					 	NULL
>			          END 																												AP_DR_ACCOUNT_CODE
>				FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B
>				WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '09AAACD1952B1Z4'
>				--AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>				--AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>		  		AND (( 'E' ='E' AND A.LINK_REF_DATE BETWEEN  '01/06/2022' AND  '30/06/2022' )  OR ( 'E' ='B' AND A.AREF_DATE BETWEEN  '01/06/2022' AND  '30/06/2022' ))
>				AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>			) A
>		GROUP BY A.DATA_SOURCE, A.COMP_CODE, A.BILL_NO, A.INVOICE_DATE, A.SUPP_CODE, A.SUPP_BILL, A.SUPP_DATE, A.DOCUMENT_TYPE, A.DATA_TYPE, A.DATA_DESC, A.COMMODITY_TYPE, A.IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE, A.GST_R_FLAG, A.GST_CREDIT_FLAG, A.STATUS, DR_ACCOUNT_CODE, AP_DR_ACCOUNT_CODE, GRN_DR_ACCOUNT_CODE
>		)
>		WHERE DR_ACCOUNT_CODE IS NULL
>		AND STATUS != 'Z'
>		--WHERE DOCUMENT_TYPE IN ('45', '44', 'W5')
>       --GROUP BY DOCUMENT_TYPE
>       ORDER BY  COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE
>
>"REM WORKSPACETAB6",Query42,,149
>SELECT
>		/*
>		DATA_SOURCE
>        ,COMP_CODE
>		,BILL_NO
>		,INVOICE_DATE
>		,SUPP_CODE
>        ,SUPP_BILL
>        ,SUPP_DATE
>        ,DOCUMENT_TYPE
>		,NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME
>		,DATA_TYPE
>		,DATA_DESC
>		,COMMODITY_TYPE
>		,COMMODITY_CODE
>		,TAXABLE_AMT
>		,IGST_RATE
>		,CGST_RATE
>		,SGST_RATE
>		,CESS_RATE
>		,IGST_AMOUNT
>		,CGST_AMOUNT
>		,SGST_AMOUNT
>		,CESS_AMOUNT
>		,CESS_ON_QTY
>		,TOTAL_GST
>		,BILL_AMNT
>        ,GST_R_FLAG
>        ,GST_CREDIT_FLAG
>         ,STATUS
>         */
>      DOCUMENT_TYPE
>    , SUM(TAXABLE_AMT)
>	, SUM(IGST_AMOUNT)
>	, SUM(CGST_AMOUNT)
>	, SUM(SGST_AMOUNT)
>	, SUM(CESS_AMOUNT)
>	, SUM(CESS_ON_QTY)
>	, SUM(TOTAL_GST)
>	, SUM(BILL_AMNT)
>FROM (
>		SELECT
>		 A.DATA_SOURCE            DATA_SOURCE
>		,A.DOCUMENT_DATE          INVOICE_DATE
>		,A.DOCUMENT_STRING    	  BILL_NO
>		,A.DOCUMENT_TYPE 	      DOCUMENT_TYPE
>        ,A.AREF_NUMBER            SUPP_BILL
>        ,A.AREF_DATE              SUPP_DATE
>		,A.COMPANY_CODE           COMP_CODE
>		,A.SUPPLIER_CODE          SUPP_CODE
>		,0    		  			  DATA_TYPE
>		,'PB'                     DATA_DESC
>		,CASE WHEN ALLOW_GST_CREDIT = 'Y' THEN
>        CASE WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') ELSE DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') END
>    ELSE
>        DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') END
>   COMMODITY_TYPE
>		,A.COMMODITY_CODE         	 COMMODITY_CODE
>		,SUM(A.TAXABLE_AMT) 	  	 TAXABLE_AMT
>        ,A.IGST_RATE
>        ,A.CGST_RATE
>        ,A.SGST_RATE
>        ,A.CESS_RATE
>        ,SUM(A.IGST_AMOUNT)      IGST_AMOUNT
>        ,SUM(A.CGST_AMOUNT) 	 CGST_AMOUNT
>        ,SUM(A.SGST_AMOUNT) 	 SGST_AMOUNT
>        ,SUM(A.CESS_AMOUNT) 	 CESS_AMOUNT
>        ,SUM(A.CESS_ON_QTY) 	 CESS_ON_QTY
>        ,SUM(NVL(IGST_AMOUNT, 0) + NVL(CGST_AMOUNT, 0) + NVL(SGST_AMOUNT, 0) + NVL(CESS_AMOUNT, 0) + NVL(CESS_ON_QTY, 0)) TOTAL_GST
>        -- Comment by ss on dt. 24/03/2018
>        --,ROUND(SUM(A.BILL_AMOUNT)) BILL_AMNT
>        ,ROUND(SUM(A.BILL_AMOUNT)- SUM(NVL(OTHR_AMOUNT,0))- SUM(NVL(IGST_OTHERS,0))- SUM(NVL(CGST_OTHERS,0))- SUM(NVL(SGST_OTHERS,0)))  BILL_AMNT
>        ,A.GST_REVERSE_FLAG                   GST_R_FLAG
>        ,A.ALLOW_GST_CREDIT                   GST_CREDIT_FLAG
>        ,A.STATUS
>		FROM VW_PURCHASE_LEDGER A, COMMODITIES B
>		WHERE  PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '09AAACD1952B1Z4'
>		/*
>			-- Parameter add supplier gst number
>			-- By ss on dt. 10/08/2018
>		*/
>		--AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>		-- Add new parameter by ss on dt. 24/03/2018
>		--AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>  		AND (( 'E' ='E' AND A.DOCUMENT_DATE  BETWEEN  '01/06/2022' AND  '30/06/2022' )  OR ( 'E' ='B' AND A.AREF_DATE BETWEEN   '01/06/2022' AND  '30/06/2022' ))
>		AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>		GROUP BY A.DATA_SOURCE,A.DOCUMENT_DATE,A.DOCUMENT_STRING,A.DOCUMENT_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE,B.COMMODITY_TYPE, A.COMMODITY_CODE ,A.GST_REVERSE_FLAG ,A.ALLOW_GST_CREDIT ,A.STATUS
>
>        UNION ALL
>		SELECT
>		 A.DATA_SOURCE           DATA_SOURCE
>		,A.LINK_REF_DATE         INVOICE_DATE
>		,A.LINK_REF_STRING       BILL_NO
>		,A.LINK_REF_TYPE         DOCUMENT_TYPE
>        ,A.AREF_NUMBER           SUPP_BILL
>        ,A.AREF_DATE             SUPP_DATE
>		,A.COMPANY_CODE          COMP_CODE
>		,A.SUPPLIER_CODE         SUPP_CODE
>		,1                       DATA_TYPE
>		,'OH'                    DATA_DESC
>		,CASE WHEN ALLOW_GST_CREDIT = 'Y' THEN
>        CASE WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') ELSE DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') END
>    ELSE
>        DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') END
>   COMMODITY_TYPE
>		,A.COMMODITY_CODE        COMMODITY_CODE
>        ,SUM(A.TAXABLE_AMT) 	 TAXABLE_AMT
>        ,A.IGST_RATE
>        ,A.CGST_RATE
>        ,A.SGST_RATE
>        ,NULL CESS_RATE
>        ,SUM(A.IGST_OTHERS) 	 IGST_AMOUNT
>        ,SUM(A.CGST_OTHERS) 	 CGST_AMOUNT
>        ,SUM(A.SGST_OTHERS) 	 SGST_AMOUNT
>        ,NULL CESS_AMOUNT
>        ,NULL CESS_ON_QTY
>        ,SUM(NVL(IGST_OTHERS, 0) + NVL(CGST_OTHERS, 0) + NVL(SGST_OTHERS, 0))  TOTAL_GST
>        ,ROUND(SUM(NVL(IGST_OTHERS, 0) + NVL(CGST_OTHERS, 0) + NVL(SGST_OTHERS, 0) + NVL(TAXABLE_AMT,0)))			        				    BILL_AMNT
>        ,A.GST_REVERSE_FLAG                   GST_R_FLAG
>        ,A.ALLOW_GST_CREDIT                   GST_CREDIT_FLAG
>        ,A.STATUS          STATUS
>		FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B
>		WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '09AAACD1952B1Z4'
>		/*
>			-- Parameter add supplier gst number
>			-- By ss on dt. 10/08/2018
>		*/
>		--AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>		-- Add new parameter by ss on dt. 24/03/2018
>		--AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>		/*
>		-- Bgn --
>		-- Against call id - 8839
>		--Communication gap by both user. So revert back all change.
>			-- Bgn --
>			-- Against call id - 8839
>			AND A.LINK_SPV_TYPE IS NOT NULL
>			-- End --
>		-- End --
>		*/
>  		AND (( 'E' ='E' AND A.LINK_REF_DATE BETWEEN  '01/06/2022' AND  '30/06/2022' )  OR ( 'E' ='B' AND A.AREF_DATE BETWEEN  '01/06/2022' AND  '30/06/2022' ))
>		AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>		GROUP BY A.DATA_SOURCE,A.LINK_REF_DATE,A.AREF_DATE, A.LINK_REF_STRING,A.LINK_REF_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE,B.COMMODITY_TYPE,A.COMMODITY_CODE
>,A.GST_REVERSE_FLAG,A.ALLOW_GST_CREDIT,A.STATUS )
>
>		WHERE DOCUMENT_TYPE IN ('45', '44', 'W5')
>GROUP BY DOCUMENT_TYPE
>--ORDER BY  COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE
>
>"REM WORKSPACETAB7",Query40,,1
>SELECT * FROM TT$INV_WISE_PURCHASE_DTL
>"REM WORKSPACETAB8",Query38,,1
>SELECT * FROM TT$INV_WISE_PURCHASE_DTL
>"REM WORKSPACETAB9",Query39,,1
>DESC TT$PURCHASE_GSTR_2A
>"REM WORKSPACETAB10",Query34,,1
>desc xyz
>"REM WORKSPACETAB11","ALTER SCRIPT",,35
>DROP TABLE TT$INV_WISE_PURCHASE_DTL;
>CREATE GLOBAL TEMPORARY TABLE TT$INV_WISE_PURCHASE_DTL
>(
>	COMMODITY_TYPE  		VARCHAR2(10),
>	COMMODITY_DESC  		VARCHAR2(100),
>	COMP_CODE       		VARCHAR2(3),
>	SHORT_NAME      		VARCHAR2(100),
>	DOCUMENT_STRING 		VARCHAR2(30),
>	DOCUMENT_DATE   		DATE,
>	SUPP_BILL_NUMBER    	VARCHAR2(30),
>	SUPP_BILL_DATE      	DATE,
>	SUPPLIER_CODE       	VARCHAR2(10),
>	SUPPLIER_NAME       	VARCHAR2(100),
>	SUPPLIER_GST_NUMBER     VARCHAR2(50),
>	DATA_TYPE				NUMBER(1),
>	DATA_DESC       		VARCHAR2(2),
>	ACCOUNT_CODE        	VARCHAR2(5),
>	ACCOUNT_NAME	        VARCHAR2(255),
>	SUB_ACCOUNT_CODE    	VARCHAR2(4),
>	SUB_ACCOUNT_NAME    	VARCHAR2(255),
>	TAXABLE_AMOUNT     		NUMBER,
>	IGST_RATE       		NUMBER,
>	IGST_AMOUNT     		NUMBER,
>	CGST_RATE       		NUMBER,
>	CGST_AMOUNT     		NUMBER,
>	SGST_RATE       		NUMBER,
>	SGST_AMOUNT     		NUMBER,
>	CESS_RATE       		NUMBER,
>	CESS_AMOUNT     		NUMBER,
>	CESS_ON_QTY     		NUMBER,
>	TOTAL_GST       		NUMBER,
>	BILL_AMOUNT			    NUMBER,
>	STATUS					VARCHAR2(1)
>)
>ON COMMIT PRESERVE ROWS;
>"REM WORKSPACETAB12",Query36,,1
>DESC ALL_SUPPLIERS
>"REM WORKSPACETAB13",Query31,,1
>SELECT * FROM VW_SUPPLIERS
>"REM WORKSPACETAB14",Query27,,3
>SELECT * FROM ALL_TAB_COLUMNS
>WHERE COLUMN_NAME LIKE '%IGST%'
>AND OWNER = 'IMISMAIN'
>"REM WORKSPACETAB15",Query37,,1
>DESC TT$PURCHASE_GSTR_2A
>"REM WORKSPACETAB16",Query33,,1
>select * from TT$RELATED_PARTIES_DETAIL
>"REM WORKSPACETAB17",Query29,,1
>DESC TT$GST_RETURNS_DATA
>"REM WORKSPACETAB18",Query32,,0
>"REM WORKSPACETAB19",Query28,,1
>DESC TT$SALE_BILL_ITEMS
>"REM WORKSPACETAB20","FINAL REPORT QUERY",,237
>SELECT
>	  A.DATA_SOURCE
>	, A.COMP_CODE
>	, A.BILL_NO
>	, A.INVOICE_DATE
>	, A.SUPP_CODE
>	, A.SUPP_BILL
>	, A.SUPP_DATE
>	, A.DOCUMENT_TYPE
>	, NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME
>	, A.DATA_TYPE
>	, A.DATA_DESC
>	, A.COMMODITY_TYPE
>	, SUM(A.TAXABLE_AMT)  				TAXABLE_AMT
>	, A.IGST_RATE
>	, A.CGST_RATE
>	, A.SGST_RATE
>	, A.CESS_RATE
>	, SUM(A.IGST_AMOUNT)				IGST_AMOUNT
>	, SUM(A.CGST_AMOUNT) 				CGST_AMOUNT
>	, SUM(A.SGST_AMOUNT) 				SGST_AMOUNT
>	, SUM(A.CESS_AMOUNT)				CESS_AMOUNT
>	, SUM(A.CESS_ON_QTY) 				CESS_ON_QTY
>	, SUM(A.TOTAL_GST  ) 				TOTAL_GST
>	, SUM(A.BILL_AMNT  )				BILL_AMNT
>	, A.GST_R_FLAG
>	, A.GST_CREDIT_FLAG
>	, A.STATUS
>	, NVL(
>			CASE
>				WHEN A.DOCUMENT_TYPE 	IN ('W7', 'DC', 'W5', '49') THEN DR_ACCOUNT_CODE
>				WHEN A.DOCUMENT_TYPE 	IN ('AP'      ) 			THEN AP_DR_ACCOUNT_CODE
>			ELSE
>				NULL
>			END
>		, GRN_DR_ACCOUNT_CODE
>		) DR_ACCOUNT_CODE
>FROM (
>		SELECT
>			  A.DATA_SOURCE            																							DATA_SOURCE
>			, A.DOCUMENT_DATE          																							INVOICE_DATE
>			, A.DOCUMENT_STRING    	  																							BILL_NO
>			, A.DOCUMENT_TYPE 	      																							DOCUMENT_TYPE
>			, A.AREF_NUMBER            																							SUPP_BILL
>			, A.AREF_DATE              																							SUPP_DATE
>			, A.COMPANY_CODE           																							COMP_CODE
>			, A.SUPPLIER_CODE          																							SUPP_CODE
>			, 0    		  			  																							DATA_TYPE
>			, 'PB'                     																							DATA_DESC
>		    , CASE
>				WHEN ALLOW_GST_CREDIT = 'Y' THEN
>        										CASE
>        											WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$')
>        										ELSE
>        											DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3')
>        										END
>    		  ELSE
>        	  	DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4')
>      		  END																												COMMODITY_TYPE
>			, A.TAXABLE_AMT 	  																								TAXABLE_AMT
>			, A.IGST_RATE
>			, A.CGST_RATE
>			, A.SGST_RATE
>			, A.CESS_RATE
>			, A.IGST_AMOUNT		      																							IGST_AMOUNT
>			, A.CGST_AMOUNT		 	 																							CGST_AMOUNT
>			, A.SGST_AMOUNT 	 																								SGST_AMOUNT
>			, A.CESS_AMOUNT 	 																								CESS_AMOUNT
>			, A.CESS_ON_QTY 	 																								CESS_ON_QTY
>			, NVL(IGST_AMOUNT, 0)
>			+ NVL(CGST_AMOUNT, 0)
>			+ NVL(SGST_AMOUNT, 0)
>			+ NVL(CESS_AMOUNT, 0)
>			+ NVL(CESS_ON_QTY, 0) 																								TOTAL_GST
>			,(
>			  A.BILL_AMOUNT
>			- NVL(OTHR_AMOUNT,0)
>			- NVL(IGST_OTHERS,0)
>			- NVL(CGST_OTHERS,0)
>			- NVL(SGST_OTHERS,0)
>			 ) 																													BILL_AMNT
>			, A.GST_REVERSE_FLAG        																						GST_R_FLAG
>			, A.ALLOW_GST_CREDIT        																						GST_CREDIT_FLAG
>			, A.STATUS
>	        , (
>	        	SELECT COMPANY_CODE
>	        	FROM VW_COMPANY_DETAIL
>	        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>	  		   ) 																												REF_COMPANY
>	        , (
>	        	SELECT ACNT_MODEL
>	        	FROM ITEM_CLASSES
>	        	WHERE ITEM_CLASS = A.ITEM_CLASS
>	        	AND COMPANY_CODE = A.COMPANY_CODE
>	          )																													ACNT_MODEL
>	        , (
>	        	SELECT STORE_TYPE
>	        	FROM VW_STORES
>	        	WHERE COMPANY_CODE 	= A.COMPANY_CODE
>	        	AND STORE_CODE 		= A.STORE_CODE
>	          ) 																												STORE_TYPE
>			,
>				CASE
>					WHEN A.DOCUMENT_TYPE 	IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																											        	SELECT COMPANY_CODE
>																											        	FROM VW_COMPANY_DETAIL
>																											        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																											  		   ), 'FG_TRANSFER_DR' )
>					WHEN A.DOCUMENT_TYPE 	IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																											        	SELECT COMPANY_CODE
>																											        	FROM VW_COMPANY_DETAIL
>																											        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																											  		   ), 'FG_RECEIVED_DR' )
>				ELSE
>					NULL
>				END																												DR_ACCOUNT_CODE
>			, CASE
>				WHEN A.DOCUMENT_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = A.ITEM_CLASS AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN
>					PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE)
>	        	ELSE
>			 		PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE)
>	        	END 																											GRN_DR_ACCOUNT_CODE
>			, CASE
>				WHEN A.DOCUMENT_TYPE IN ('AP') AND A.STATUS != 'Z' THEN
>					( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE  COMPANY  = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE )
>	    	  ELSE
>			 	NULL
>	     	  END 																												AP_DR_ACCOUNT_CODE
>        FROM VW_PURCHASE_LEDGER A, COMMODITIES B
>		WHERE  PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = :P_GST_NUMBER
>		AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>		AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>  		AND (( :P_RCPT_BILL_DATE ='E' AND A.DOCUMENT_DATE  BETWEEN  :P_QRY_BGN_DATE AND  :P_QRY_END_DATE )  OR ( :P_RCPT_BILL_DATE ='B' AND A.AREF_DATE BETWEEN  :P_QRY_BGN_DATE AND  :P_QRY_END_DATE ))
>		AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>		
>        UNION ALL
>
>		SELECT
>			  A.DATA_SOURCE           																							DATA_SOURCE
>			, A.LINK_REF_DATE         																							INVOICE_DATE
>			, A.LINK_REF_STRING       																							BILL_NO
>			, A.LINK_REF_TYPE         																							DOCUMENT_TYPE
>	        , A.AREF_NUMBER           																							SUPP_BILL
>	        , A.AREF_DATE             																							SUPP_DATE
>			, A.COMPANY_CODE          																							COMP_CODE
>			, A.SUPPLIER_CODE         																							SUPP_CODE
>			, 1                       																							DATA_TYPE
>			, 'OH'                    																							DATA_DESC
>			, CASE
>			  	WHEN ALLOW_GST_CREDIT = 'Y' THEN
>	          		CASE
>	          			WHEN GST_REVERSE_FLAG = 'N' THEN
>	          				NVL(B.COMMODITY_TYPE, '$')
>	          		ELSE
>	          			DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3')
>	          		END
>	    	  ELSE
>	        	DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4')
>	          END																												COMMODITY_TYPE
>			, A.TAXABLE_AMT 	 																								TAXABLE_AMT
>			, A.IGST_RATE
>			, A.CGST_RATE
>			, A.SGST_RATE
>	        , NULL 																												CESS_RATE
>	        , A.IGST_OTHERS		 	 																							IGST_AMOUNT
>	        , A.CGST_OTHERS 		 																							CGST_AMOUNT
>	        , A.SGST_OTHERS 	 																								SGST_AMOUNT
>	        , NULL CESS_AMOUNT
>	        , NULL CESS_ON_QTY
>	        ,(
>	          NVL(IGST_OTHERS, 0)
>	        + NVL(CGST_OTHERS, 0)
>	        + NVL(SGST_OTHERS, 0)
>	          )																													TOTAL_GST
>	        ,(
>	          NVL(IGST_OTHERS, 0)
>	        + NVL(CGST_OTHERS, 0)
>	        + NVL(SGST_OTHERS, 0)
>	        + NVL(TAXABLE_AMT, 0)
>	          )			        																								BILL_AMNT
>	        , A.GST_REVERSE_FLAG                   																				GST_R_FLAG
>	        , A.ALLOW_GST_CREDIT                   																				GST_CREDIT_FLAG
>	        , A.STATUS          																								STATUS
>	        , (
>	          	SELECT COMPANY_CODE
>	          	FROM VW_COMPANY_DETAIL
>	          	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>	           ) 																												REF_COMPANY
>	        , (
>	        	SELECT ACNT_MODEL
>	        	FROM ITEM_CLASSES
>	        	WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE)
>	        	AND COMPANY_CODE = A.COMPANY_CODE
>	           ) 																												ACNT_MODEL
>			, (
>				SELECT STORE_TYPE
>				FROM VW_STORES
>				WHERE COMPANY_CODE = A.COMPANY_CODE
>				AND STORE_CODE = A.STORE_CODE
>			   ) 																												STORE_TYPE
>			,
>				CASE
>					WHEN A.VOUCHER_TYPE 	IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																											        	SELECT COMPANY_CODE
>																											        	FROM VW_COMPANY_DETAIL
>																											        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																											  		   ), 'FG_TRANSFER_DR' )
>					WHEN A.VOUCHER_TYPE 	IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ((
>																											        	SELECT COMPANY_CODE
>																											        	FROM VW_COMPANY_DETAIL
>																											        	WHERE SUPPLIER_CODE = A.SUPPLIER_CODE
>																											  		   ), 'FG_RECEIVED_DR' )
>				ELSE
>					NULL
>				END																												DR_ACCOUNT_CODE
>			, CASE
>			  	WHEN A.VOUCHER_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN
>			  		PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE)
>	          ELSE
>			 	PKG_IC.GET_POSTING_ACCOUNT (A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE)
>	          END 																												GRN_DR_ACCOUNT_CODE
>			, CASE
>			  	WHEN A.VOUCHER_TYPE IN ('AP') AND A.STATUS != 'Z' THEN
>			  	( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE  COMPANY  = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE )
>	          ELSE
>			 	NULL
>	          END 																												AP_DR_ACCOUNT_CODE
>		FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B
>		WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = :P_GST_NUMBER
>		AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>		AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>  		AND (( :P_RCPT_BILL_DATE ='E' AND A.LINK_REF_DATE BETWEEN  :P_QRY_BGN_DATE AND  :P_QRY_END_DATE )  OR ( :P_RCPT_BILL_DATE ='B' AND A.AREF_DATE BETWEEN  :P_QRY_BGN_DATE AND  :P_QRY_END_DATE ))
>		AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>	) A
>GROUP BY A.DATA_SOURCE, A.COMP_CODE, A.BILL_NO, A.INVOICE_DATE, A.SUPP_CODE, A.SUPP_BILL, A.SUPP_DATE, A.DOCUMENT_TYPE, A.DATA_TYPE, A.DATA_DESC, A.COMMODITY_TYPE, A.IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE, A.GST_R_FLAG, A.GST_CREDIT_FLAG, A.STATUS, DR_ACCOUNT_CODE, AP_DR_ACCOUNT_CODE, GRN_DR_ACCOUNT_CODE
>ORDER BY  COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE
>
>"REM WORKSPACETAB21",Query27,,3
>SELECT * FROM ITEMS
>WHERE ITEM_CODE = 'GN-OTH-SWL'
>ORDER BY COMPANY
>"REM WORKSPACETAB22",Query29,,135
>SELECT	DATA_SOURCE
>        ,COMP_CODE
>		,BILL_NO
>		,INVOICE_DATE
>		,SUPP_CODE
>        ,SUPP_BILL
>        ,SUPP_DATE
>        ,DOCUMENT_TYPE
>		,NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME
>		,DATA_TYPE
>		,DATA_DESC
>		,COMMODITY_TYPE
>		,COMMODITY_CODE
>		,TAXABLE_AMT
>		,IGST_RATE
>		,CGST_RATE
>		,SGST_RATE
>		,CESS_RATE
>		,IGST_AMOUNT
>		,CGST_AMOUNT
>		,SGST_AMOUNT
>		,CESS_AMOUNT
>		,CESS_ON_QTY
>		,TOTAL_GST
>		,BILL_AMNT
>        ,GST_R_FLAG
>        ,GST_CREDIT_FLAG
>         ,STATUS
>FROM (
>		SELECT
>		 A.DATA_SOURCE            DATA_SOURCE
>		,A.DOCUMENT_DATE          INVOICE_DATE
>		,A.DOCUMENT_STRING    	  BILL_NO
>		,A.DOCUMENT_TYPE 	      DOCUMENT_TYPE
>        ,A.AREF_NUMBER            SUPP_BILL
>        ,A.AREF_DATE              SUPP_DATE
>		,A.COMPANY_CODE           COMP_CODE
>		,A.SUPPLIER_CODE          SUPP_CODE
>		,0    		  			  DATA_TYPE
>		,'PB'                     DATA_DESC
>		,CASE WHEN ALLOW_GST_CREDIT = 'Y' THEN
>        CASE WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') ELSE DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') END
>    ELSE
>        DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') END
>   COMMODITY_TYPE
>		,A.COMMODITY_CODE         	 COMMODITY_CODE
>		,SUM(A.TAXABLE_AMT) 	  	 TAXABLE_AMT
>        ,A.IGST_RATE
>        ,A.CGST_RATE
>        ,A.SGST_RATE
>        ,A.CESS_RATE
>        ,SUM(A.IGST_AMOUNT)      IGST_AMOUNT
>        ,SUM(A.CGST_AMOUNT) 	 CGST_AMOUNT
>        ,SUM(A.SGST_AMOUNT) 	 SGST_AMOUNT
>        ,SUM(A.CESS_AMOUNT) 	 CESS_AMOUNT
>        ,SUM(A.CESS_ON_QTY) 	 CESS_ON_QTY
>        ,SUM(NVL(IGST_AMOUNT, 0) + NVL(CGST_AMOUNT, 0) + NVL(SGST_AMOUNT, 0) + NVL(CESS_AMOUNT, 0) + NVL(CESS_ON_QTY, 0)) TOTAL_GST
>        -- Comment by ss on dt. 24/03/2018
>        --,ROUND(SUM(A.BILL_AMOUNT)) BILL_AMNT
>        ,ROUND(SUM(A.BILL_AMOUNT)- SUM(NVL(OTHR_AMOUNT,0))- SUM(NVL(IGST_OTHERS,0))- SUM(NVL(CGST_OTHERS,0))- SUM(NVL(SGST_OTHERS,0)))  BILL_AMNT
>        ,A.GST_REVERSE_FLAG                   GST_R_FLAG
>        ,A.ALLOW_GST_CREDIT                   GST_CREDIT_FLAG
>        ,A.STATUS
>		FROM VW_PURCHASE_LEDGER A, COMMODITIES B
>		WHERE  PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = :P_GST_NUMBER
>		/*
>			-- Parameter add supplier gst number
>			-- By ss on dt. 10/08/2018
>		*/
>		AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>		-- Add new parameter by ss on dt. 24/03/2018
>		AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>  		AND (( :P_RCPT_BILL_DATE ='E' AND A.DOCUMENT_DATE  BETWEEN  :P_QRY_BGN_DATE AND  :P_QRY_END_DATE )  OR ( :P_RCPT_BILL_DATE ='B' AND A.AREF_DATE BETWEEN  :P_QRY_BGN_DATE AND  :P_QRY_END_DATE ))
>		AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>		GROUP BY A.DATA_SOURCE,A.DOCUMENT_DATE,A.DOCUMENT_STRING,A.DOCUMENT_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE,B.COMMODITY_TYPE, A.COMMODITY_CODE ,A.GST_REVERSE_FLAG ,A.ALLOW_GST_CREDIT ,A.STATUS
>
>        UNION ALL
>		SELECT
>		 A.DATA_SOURCE           DATA_SOURCE
>		,A.LINK_REF_DATE         INVOICE_DATE
>		,A.LINK_REF_STRING       BILL_NO
>		,A.LINK_REF_TYPE         DOCUMENT_TYPE
>        ,A.AREF_NUMBER           SUPP_BILL
>        ,A.AREF_DATE             SUPP_DATE
>		,A.COMPANY_CODE          COMP_CODE
>		,A.SUPPLIER_CODE         SUPP_CODE
>		,1                       DATA_TYPE
>		,'OH'                    DATA_DESC
>		,CASE WHEN ALLOW_GST_CREDIT = 'Y' THEN
>        CASE WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') ELSE DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') END
>    ELSE
>        DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') END
>   COMMODITY_TYPE
>		,A.COMMODITY_CODE        COMMODITY_CODE
>        ,SUM(A.TAXABLE_AMT) 	 TAXABLE_AMT
>        ,A.IGST_RATE
>        ,A.CGST_RATE
>        ,A.SGST_RATE
>        ,NULL CESS_RATE
>        ,SUM(A.IGST_OTHERS) 	 IGST_AMOUNT
>        ,SUM(A.CGST_OTHERS) 	 CGST_AMOUNT
>        ,SUM(A.SGST_OTHERS) 	 SGST_AMOUNT
>        ,NULL CESS_AMOUNT
>        ,NULL CESS_ON_QTY
>        ,SUM(NVL(IGST_OTHERS, 0) + NVL(CGST_OTHERS, 0) + NVL(SGST_OTHERS, 0))  TOTAL_GST
>        ,ROUND(SUM(NVL(IGST_OTHERS, 0) + NVL(CGST_OTHERS, 0) + NVL(SGST_OTHERS, 0) + NVL(TAXABLE_AMT,0)))			        				    BILL_AMNT
>        ,A.GST_REVERSE_FLAG                   GST_R_FLAG
>        ,A.ALLOW_GST_CREDIT                   GST_CREDIT_FLAG
>        ,A.STATUS          STATUS
>		FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B
>		WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = :P_GST_NUMBER
>		/*
>			-- Parameter add supplier gst number
>			-- By ss on dt. 10/08/2018
>		*/
>		AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%')
>		-- Add new parameter by ss on dt. 24/03/2018
>		AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%')
>		/*
>		-- Bgn --
>		-- Against call id - 8839
>		--Communication gap by both user. So revert back all change.
>			-- Bgn --
>			-- Against call id - 8839
>			AND A.LINK_SPV_TYPE IS NOT NULL
>			-- End --
>		-- End --
>		*/
>  		AND (( :P_RCPT_BILL_DATE ='E' AND A.LINK_REF_DATE BETWEEN  :P_QRY_BGN_DATE AND  :P_QRY_END_DATE )  OR ( :P_RCPT_BILL_DATE ='B' AND A.AREF_DATE BETWEEN  :P_QRY_BGN_DATE AND  :P_QRY_END_DATE ))
>		AND A.COMMODITY_CODE = B.COMMODITY_CODE (+)
>		GROUP BY A.DATA_SOURCE,A.LINK_REF_DATE,A.AREF_DATE, A.LINK_REF_STRING,A.LINK_REF_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE,B.COMMODITY_TYPE,A.COMMODITY_CODE
>,A.GST_REVERSE_FLAG,A.ALLOW_GST_CREDIT,A.STATUS )
>
>ORDER BY  COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE
>
>"REM WORKSPACETAB23",Query28,,2
>SELECT * FROM USER_IDS
>WHERE USER_ID = 'E20661'
>"REM WORKSPACETAB24",Query16,,4
>GRN
>FGR
>FGP
>APV
>"REM WORKSPACETAB25",Query20,,1
>DESC SERVICE_RECEIVED_NOTES
>"REM WORKSPACETAB26",Query9,,6
>SELECT *--DISTINCT DATA_SOURCE, DOCUMENT_TYPE
>FROM VW_PURCHASE_LEDGER A
>WHERE  PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '07AAACD1952B1Z8'
>AND A.DOCUMENT_DATE  BETWEEN  '01/04/2022' AND  '30/04/2022'
>AND A.DATA_SOURCE IN ('APV')
>order by DOCUMENT_NUMBER
>"REM WORKSPACETAB27",Query18,,5
>SELECT *--DISTINCT DATA_SOURCE, DOCUMENT_TYPE
>FROM VW_OVERHEAD_LEDGER A
>WHERE  PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '07AAACD1952B1Z8'
>AND A.VOUCHER_DATE  BETWEEN  '01/04/2022' AND  '30/04/2022'
>AND A.DATA_SOURCE IN ('GRN')
>"REM WORKSPACETAB28",Query19,,3
>SELECT * FROM VW_STORES
>WHERE COMPANY_CODE = '01'
>AND STORE_CODE = '022'
>"REM WORKSPACETAB29",Query17,,3
>SELECT * FROM ITEM_CLASSES
>WHERE ITEM_CLASS = 0
>AND COMPANY_CODE = '01'
>"REM WORKSPACETAB30","MISC AP (AP)",,8
>SELECT * FROM GL_POSTING_DETAILS
>WHERE COMPANY = '01'
>AND VOUCHER_TYPE = 'AP'
>AND VOUCHER_SERIES = '2204'
>AND VOUCHER_NUMBER = 1
>AND AMOUNT < 0
>
>-- AP/2204/000001
>"REM WORKSPACETAB31",Query24,,5
>select * from ACCOUNTS_PAYABLE_VOUCHERS
>WHERE COMPANY = '01'
>AND VOUCHER_TYPE = 'AP'
>AND VOUCHER_SERIES = '2204'
>AND VOUCHER_NUMBER = 000005
>"REM WORKSPACETAB32",Query25,,5
>select * from AP_VOUCHER_MISC_DETAILS
>WHERE COMPANY = '01'
>AND VOUCHER_TYPE = 'AP'
>AND VOUCHER_SERIES = '2204'
>AND VOUCHER_NUMBER = 000005
>"REM WORKSPACETAB33",Query23,,5
>              select  GL_CODE,GL_SUB_CODE
>
>              from   EXPENSE_CODES
>              where  company  = '01'
>              and expense_code = 'INSURNCE-MOTOR_CAR';
>"REM WORKSPACETAB34",Query22,,6
>          select account_code, sub_account_code
>          from  ap_controls a, suppliers s
>          where a.company = '01'
>          and s.company = a.company
>          and s.type_of_supplier = a.type_of_supplier
>          and s.supplier_code = '3006326'
>"REM WORKSPACETAB35","FG STOCK RECEIPT FROM TRANSFER (W5)",,8
>SELECT * FROM GL_POSTING_DETAILS
>WHERE COMPANY = '01'
>AND VOUCHER_TYPE = 'W5'
>AND VOUCHER_SERIES = '2022'
>AND VOUCHER_NUMBER = 85
>ORDER BY GL_POSTING_SEQ_NUMBER
>
>-- W5/2022/000085
>"REM WORKSPACETAB36",Query8,,1
>SELECT PKG_GL.GET_ACCOUNT_NAME('10597'), PKG_GL.GET_ACCOUNT_NAME('105970003') FROM DUAL
>"REM WORKSPACETAB37",Query7,,9
>SELECT /*TRANSACTION_ITEM_CODE, TRANSACTION_STOCK_TYPE,*/ SUM(TRANSACTION_ITEM_QTY), SUM(TRANSACTION_ITEM_VALUE)
>FROM WAREHOUSE_TRANSACTION_ITEMS
>WHERE COMPANY = '01'
>AND TRANSACTION_TYPE = 'W5'
>AND TRANSACTION_SERIES = '2022'
>AND TRANSACTION_NO = 000001
>--AND AMOUNT < 0
>--ORDER BY GL_POSTING_SEQ_NUMBER
>--GROUP BY TRANSACTION_ITEM_CODE, TRANSACTION_STOCK_TYPE
>"REM WORKSPACETAB38","FG STOCK RECEIPT AGAINST FG GRN (49)",,10
>SELECT * FROM GL_POSTING_DETAILS
>WHERE COMPANY = '04'
>AND VOUCHER_TYPE = '49'
>AND VOUCHER_SERIES = '2204'
>AND VOUCHER_NUMBER = 3
>AND AMOUNT < 0
>AND UPPER(NARRATION) LIKE '%PURCHASE OF GOODS%'
>ORDER BY GL_POSTING_SEQ_NUMBER
>
>--449/2204/000003
>"REM WORKSPACETAB39",Query11,,9
>SELECT * FROM FG_RECEIVED_NOTES
>WHERE COMPANY_CODE = '01'
>AND DOCUMENT_TYPE = '49'
>AND DOCUMENT_SERIES = '2204'
>AND DOCUMENT_NUMBER = 2
>--AND AMOUNT < 0
>--AND UPPER(NARRATION) LIKE '%PURCHASE OF GOODS%'
>--ORDER BY GL_POSTING_SEQ_NUMBER
>--
>"REM WORKSPACETAB40",Query15,,4
>SELECT *--COMPANY_CODE
>FROM VW_COMPANY_DETAIL
>WHERE SUPPLIER_CODE = '3001297'
>
>"REM WORKSPACETAB41","FG STOVK RECEIPT AGAINT GRN (44)",,7
>SELECT * FROM GL_POSTING_DETAILS
>WHERE COMPANY = '01'
>AND VOUCHER_TYPE = '44'
>AND VOUCHER_SERIES = 'W224'
>AND VOUCHER_NUMBER = 000001
>AND AMOUNT < 0
>ORDER BY GL_POSTING_SEQ_NUMBER
>"REM WORKSPACETAB42",Query16,,6
>SELECT * FROM GOODS_RECEIVED_NOTES
>WHERE COMPANY = '01'
>AND GRN_TYPE = '44'
>AND GRN_SERIES = 'G224'
>AND GRN_NO = 1
>
>"REM WORKSPACETAB43",Query21,,5
>SELECT * FROM GOODS_RECEIVED_NOTE_ITEMS
>WHERE COMPANY = '01'
>AND GRN_TYPE = '44'
>AND GRN_SERIES = 'G224'
>AND GRN_NO = 1

Actions: View

Attachments on bug 9260: 2935 | 2983 | 2986 | 2987 | 2992 | 2994