Attachment 1836 Details for Bug 5993 – SS_0859_YB_SERVER_Against call id - 5993

SS_0859_YB_SERVER_Against call id - 5993

SS_0859_YB_SERVER_Against call id - 5993.sql (text/x-vhdl), 19.84 KB, created by Sharad on 2021-04-05 13:07:18 IST

(hide)

Description:

Filename:

MIME Type:

Creator: Sharad

Created: 2021-04-05 13:07:18 IST

Size: 19.84 KB

patch

obsolete

>-- Alter script --
>
>ALTER TABLE EXCISE_INVOICE
>ADD (
>		PI_RECID                   NUMBER(16),
>		PROFORMA_TYPE              VARCHAR2(2),
>		PROFORMA_SERIES            VARCHAR2(4),
>		PROFORMA_NO                NUMBER(6)
>	);
>
>ALTER TABLE EXCISE_INVOICE_ITEMS
>ADD (
>		SALES_TAX_PLAN         VARCHAR2(10),
>		TRADE_DISCOUNT_RATE    NUMBER(5,2),
>		TRADE_DISCOUNT_AMOUNT  NUMBER(18,2)
>	);
>	
>-----------------------------------------------------------------
>
>-- Trigger -- 
>
>-- EXCISE_INVOICE_TRG -- 
>
>-- Line No.:- 152
>
>	-- Bgn --
>	-- Against call ID - 5993
>	-- By ss on dt. 31/03/2021 
>	O.PI_RECID                	:= :O.PI_RECID; 
>	O.PROFORMA_TYPE             := :O.PROFORMA_TYPE; 
>	O.PROFORMA_SERIES           := :O.PROFORMA_SERIES; 
>	O.PROFORMA_NO               := :O.PROFORMA_NO; 
>	-- End --
>	
>-- Line No.:- 282
>
>    -- Bgn --
>	-- Against call ID - 5993
>	-- By ss on dt. 31/03/2021 
>	N.PI_RECID                	:= :N.PI_RECID; 
>	N.PROFORMA_TYPE             := :N.PROFORMA_TYPE; 
>	N.PROFORMA_SERIES           := :N.PROFORMA_SERIES; 
>	N.PROFORMA_NO               := :N.PROFORMA_NO; 
>	-- End --  
>
>--------------------------------------------------------------------------------------------
>-- Trigger -- 
>
>-- EXCISE_INVOICE_ITEMS_TRG -- 
>
>-- Line No.:- 101
>
>		-- Bgn --
>		-- Against call ID - 5993
>		-- By ss on dt. 31/03/2021		
>		O.SALES_TAX_PLAN                := :O.SALES_TAX_PLAN;
>		O.TRADE_DISCOUNT_RATE           := :O.TRADE_DISCOUNT_RATE;
>		O.TRADE_DISCOUNT_AMOUNT         := :O.TRADE_DISCOUNT_AMOUNT;
>		-- End --
>	
>-- Line No.:- 191
>
>		-- Bgn --
>		-- Against call ID - 5993
>		-- By ss on dt. 31/03/2021		
>		N.SALES_TAX_PLAN                := :N.SALES_TAX_PLAN;
>		N.TRADE_DISCOUNT_RATE           := :N.TRADE_DISCOUNT_RATE;
>		N.TRADE_DISCOUNT_AMOUNT         := :N.TRADE_DISCOUNT_AMOUNT;
>		-- End --  
>
>
>--------------------------------------------------------------------------------------------
>
>-- Package PKG_SB --  New 
>
>-- Bgn --
>-- Against call ID - 5993 
>-- By ss on dt. 31/03/2021
>PROCEDURE GENERATED_EXCISE_INVOICE
> (P_COMPANY_CODE IN VARCHAR2
> ,P_CUSTOMER_CODE IN VARCHAR2
> ,P_PROFORMA_TYPE IN VARCHAR2
> ,P_PROFORMA_SERIES IN VARCHAR2
> ,P_ENTRY_NO IN NUMBER
> ,P_STORE_CODE VARCHAR2
> ,P_INVOICE_RECID IN OUT NUMBER
> );
>-- End --
>
>-------------------------------------------------
>-- PACKAGE BODY -- PKG_SB.GENERATED_EXCISE_INVOICE -- New
>
>-- Bgn --
>-- Against call ID - 5993 
>-- By ss on dt. 31/03/2021
>PROCEDURE GENERATED_EXCISE_INVOICE
> (P_COMPANY_CODE IN VARCHAR2
> ,P_CUSTOMER_CODE IN VARCHAR2
> ,P_PROFORMA_TYPE IN VARCHAR2
> ,P_PROFORMA_SERIES IN VARCHAR2
> ,P_ENTRY_NO IN NUMBER
> ,P_STORE_CODE VARCHAR2
> ,P_INVOICE_RECID IN OUT NUMBER
> )
> IS
>
>	P_PROFORMA_DATE					DATE;
>	V_MODE_OF_TRANSPORT   			VARCHAR2(10) := PKG_POPLISTS.FIRST_CODE('TPTSMODE');
>	V_INSURANCE_POLICY_NO 			VARCHAR2(30) := NULL;
>	V_NO              				NUMBER;
>	V_NET_VALUE       				NUMBER;
>	I_ENTRY_NO                      NUMBER;
>	V_SALES_TAX_FORM				VARCHAR2(10);
>
>	V_BOL_RECID						NUMBER;
>	V_ERR_STRING					VARCHAR2(1000);
>    V_CUSTOMER_SALES                NUMBER; --BUGZILLA CALL ID 4372 
>    
>    -- Bgn -- 
>    
>	V_COUNTRY_CODE 		VARCHAR2(10);
>	V_SRC_STATE_CODE 	VARCHAR2(10);
>	V_TGT_STATE_CODE 	VARCHAR2(10);
>	V_ASSESSABLE_RATE	NUMBER;
>	V_POLICY_NO 		VARCHAR2(50);
>	V_PRODUCT_COUNT		NUMBER; 
>	V_PRODUCT_TYPE		VARCHAR2(10);
>	V_EXCISE_INV_TYPE	VARCHAR2(2);
>	V_EXCISE_SERIES		VARCHAR2(4);
>    
>    CURSOR C1 ( X_COMPANY_CODE VARCHAR2, X_INVOICE_RECID NUMBER )IS 
>		SELECT 
>			  EI.COMPANY
>			, EI.PRICE_METHOD
>			, EI.CUSTOMER_CODE
>			, EI.XFR_COMPANY
>			, EI.TAX_TYPE
>			, EII.STORE_ID
>			, EII.ITEM_CODE
>			, EII.STOCK_TYPE
>			, EI.INVOICE_DATE
>			, EII.MAX_RETAIL_PRICE
>			, NVL(EII.NO_OF_PACKS,EII.ITEM_QTY) NO_OF_PACKS
>			, EII.PARENT_RECID
>			, EII.EII_RECID
>			, EI.INVOICE_TYPE
>			, EI.INVOICE_SERIES
>			, EI.ENTRY_NO
>			, EII.PRODUCT_TYPE
>			, EII.SEQ_NO 
>			, EI.STATUS 
>		FROM EXCISE_INVOICE EI, EXCISE_INVOICE_ITEMS EII
>		WHERE EI.EI_RECID 	=	EII.PARENT_RECID
>		AND EI.COMPANY 		=	X_COMPANY_CODE
>		AND EI.EI_RECID		=	X_INVOICE_RECID
>		ORDER BY EII.SEQ_NO;
>		
>	-- End --  	
>BEGIN
>
>	IF P_PROFORMA_TYPE = '7A' THEN
>		V_INVOICE_TYPE 	:= 'I5';
>	ELSE
>		V_INVOICE_TYPE 	:= 'I1';
>	END IF;
>
>	V_EXCISE_INV_TYPE	:=	'LI';
>	PKG_VOUCHERS.GET_ENTRY_NUMBER(P_COMPANY_CODE, V_EXCISE_INV_TYPE, I_ENTRY_NO);
>	
>	BEGIN
>		SELECT PROFORMA_DATE
>		INTO  P_PROFORMA_DATE
>		FROM  PROFORMA_INVOICES
>		WHERE COMPANY          = P_COMPANY_CODE
>		AND   PROFORMA_TYPE    = P_PROFORMA_TYPE
>		AND   ENTRY_NO         = P_ENTRY_NO;
>	EXCEPTION
>		WHEN NO_DATA_FOUND THEN NULL;
>		WHEN TOO_MANY_ROWS THEN NULL;
>	END;
>	
>	PKG_VOUCHERS.GET_VOUCHER_SERIES(P_COMPANY_CODE, V_EXCISE_INV_TYPE, P_PROFORMA_DATE, V_EXCISE_SERIES, V_ERR_STRING);
>	IF V_ERR_STRING IS NOT NULL THEN
>		RAISE_APPLICATION_ERROR(-20001, V_ERR_STRING);
>	END IF;
>
>	P_SERIES := GET_INVOICE_SERIES (P_COMPANY_CODE, P_PROFORMA_TYPE, P_PROFORMA_SERIES, NULL, P_ENTRY_NO, P_STORE_CODE, P_CUSTOMER_CODE, P_PROFORMA_DATE, NULL, NULL, V_SALES_TAX_FORM, P_SERIES);
>
>	BEGIN
>		SELECT IMIS_RECID.NEXTVAL
>		INTO   P_INVOICE_RECID
>		FROM   DUAL;
>	END;
>
>    --BUGZILLA CALL ID 4372
>    V_CUSTOMER_SALES := ABS(PKG_AR.GET_CUSTOMER_SALES (P_COMPANY_CODE ,P_CUSTOMER_CODE ,P_PROFORMA_DATE ));
>    --END 
>	
>	BEGIN
>		SELECT COUNT(DISTINCT(PRODUCT_TYPE))
>		INTO V_PRODUCT_COUNT
>		FROM PROFORMA_INVOICE_ITEMS
>		WHERE 	COMPANY          = P_COMPANY_CODE
>		AND 	PROFORMA_TYPE    = P_PROFORMA_TYPE
>		AND 	ENTRY_NO         = P_ENTRY_NO;
>	EXCEPTION
>		WHEN NO_DATA_FOUND THEN NULL;
>		WHEN TOO_MANY_ROWS THEN NULL; 
>	END;
>	
>	IF NVL(V_PRODUCT_COUNT,0) > 1 THEN    				
>		--Sharad Sharma	30/03/2021
>		--Proper Message displayed
>		RAISE_APPLICATION_ERROR(-20001, 'Create proforma only one product type. ');
>	END IF;
>
>	BEGIN
>		SELECT DISTINCT(PRODUCT_TYPE)
>		INTO V_PRODUCT_TYPE
>		FROM PROFORMA_INVOICE_ITEMS
>		WHERE 	COMPANY          = P_COMPANY_CODE
>		AND 	PROFORMA_TYPE    = P_PROFORMA_TYPE
>		AND 	ENTRY_NO         = P_ENTRY_NO;
>	EXCEPTION
>		WHEN NO_DATA_FOUND THEN NULL;
>		WHEN TOO_MANY_ROWS THEN NULL; 
>	END;
>
>	BEGIN
>		SELECT 	POLICY_NUMBER
>		INTO 	V_INSURANCE_POLICY_NO
>		FROM 	INSURANCE_POLICIES
>		WHERE 	COMPANY 		=	P_COMPANY_CODE
>		AND   	P_PROFORMA_DATE BETWEEN POLICY_VALID_FROM AND POLICY_VALID_TO
>		AND 	STATUS 			=	'A'
>		AND 	POLICY_TYPE 	=	'M';
>	EXCEPTION
>		WHEN NO_DATA_FOUND THEN NULL;
>		WHEN TOO_MANY_ROWS THEN NULL;
>	END;
>	BEGIN
>		INSERT INTO EXCISE_INVOICE
>			(EI_RECID
>			,COMPANY
>			,INVOICE_TYPE
>			,INVOICE_SERIES
>			,ENTRY_NO
>			,INVOICE_NO
>			,INVOICE_DATE
>			,PI_RECID
>			,PROFORMA_TYPE
>			,PROFORMA_SERIES
>			,PROFORMA_NO
>--			,SALE_ORDER_TYPE
>--			,SALE_ORDER_SERIES
>--			,SALE_ORDER_NO
>			,PRODUCT_TYPE
>			,CURRENCY
>			,EXCHANGE_RATE
>			,ORDER_TYPE
>			,ORDER_DOCUMENT_TYPE
>			,TYPE_OF_SALE
>			,CUSTOMER_CODE
>			,DELIVERY_ADDRESS_CODE
>			--SK Tomar	26/09/2018	1.0.7/1.1
>			--Insert values for customer and address code in invoice
>			,SHIP_TO_CUSTOMER
>			,SHIP_TO_ADRS_CODE
>			,NET_VALUE_OF_INVOICE
>			,CUSTOMER_PO_NO
>			,CUSTOMER_PO_DATE
>			,DEPARTMENT_CODE
>			,PROJECT
>			,AGENT_CODE
>			,SALE_TARGET_CODE
>			,TAX_TYPE
>			,ST_FORM_CODE
>			,VAT_INCLUSIVE
>			,ST_FORM_NO
>			,TAX_AMOUNT
>			,CREDIT_PERIOD
>			,PRICE_METHOD
>			,MODE_OF_TRANSPORT
>			,OVERALL_DISCOUNT_PERCENT
>			,OVERALL_DISCOUNT_AMOUNT
>			,COST_OF_GOODS
>			,POSTAGE_AMOUNT
>			,TURNOVER_TAX_RATE
>			,TURNOVER_TAX_AMOUNT
>			,FREIGHT_AMOUNT
>			,AGRICULTURE_TAX_RATE
>			,AGRICULTURE_TAX_AMOUNT
>			,LUXURY_TAX_RATE
>			,LUXURY_TAX_AMOUNT
>			,ROUND_OF_AMOUNT
>			,BOOKING_FROM
>			,BOOKING_TO
>			,BANK_CODE
>			,TRANSPORTER_CODE
>			,TYPE_OF_FREIGHT
>			,BILTY_NO
>			,BILTY_DATE
>			,GROSS_WEIGHT
>			,GATE_PASS_NO
>			,REMARKS
>			,STATUS
>			,CREATED_BY
>			,CREATED_ON
>			,INSURANCE_POLICY_NO
>			--,FORM49_NO
>			,OC_NO
>			,TOTAL_CASES
>			--,DISPATCH_CODE
>			,STORE_ID
>			 -- Add By Mr. Ms Dt 25/06/2017 For GST --
>			,IGST_AMOUNT
>			,CGST_AMOUNT
>			,SGST_AMOUNT
>			,CESS_AMOUNT
>			-- Dt 25/06/2017 For GST --
>			,CESS_ON_QTY --Add By MS Dt. 16/07/2017 For GST Cess on Qty
>   			,CUSTOMER_SALES --bUGZILLA CALL ID 4372 
>   			,SALE_INVOICE_TYPE
>   			,SALE_INVOICE_SERIES
>			)			
>		SELECT
>			P_INVOICE_RECID
>			,COMPANY
>			,V_EXCISE_INV_TYPE
>			,V_EXCISE_SERIES
>			,I_ENTRY_NO
>			,NULL
>			,PROFORMA_DATE
>			,PI_RECID
>			,PROFORMA_TYPE
>			,PROFORMA_SERIES
>			,PROFORMA_NO
>--			,SALE_ORDER_TYPE
>--			,SALE_ORDER_SERIES
>--			,SALE_ORDER_NO
>			,V_PRODUCT_TYPE
>			,CURRENCY
>			,EXCHANGE_RATE
>			,ORDER_TYPE
>			,ORDER_DOCUMENT_TYPE
>			,TYPE_OF_SALE
>			,CUSTOMER_CODE
>			,DELIVERY_ADDRESS_CODE
>			--SK Tomar	26/09/2018	1.0.7/1.1
>			--Insert values for customer and address code in invoice
>			,SHIP_TO_CUSTOMER
>			,SHIP_TO_ADRS_CODE
>			,NET_VALUE_OF_PROFORMA
>			,CUSTOMER_PO_NO
>			,CUSTOMER_PO_DATE
>			,DEPARTMENT_CODE
>			,PROJECT
>			,AGENT_CODE
>			,SALE_TARGET_CODE
>			,TAX_TYPE
>			,ST_FORM_CODE
>			,VAT_INCLUSIVE
>			,ST_FORM_NO
>			,TAX_AMOUNT
>			,CREDIT_PERIOD
>			,PRICE_METHOD
>			,V_MODE_OF_TRANSPORT
>			,OVERALL_DISCOUNT_PERCENT
>			,NVL(OVERALL_DISCOUNT_AMOUNT,0)
>			,COST_OF_GOODS - NVL(OVERALL_DISCOUNT_AMOUNT,0)
>			,POSTAGE_AMOUNT
>			,TURNOVER_TAX_RATE
>			,TURNOVER_TAX_AMOUNT
>			,FREIGHT_AMOUNT
>			,AGRICULTURE_TAX_RATE
>			,AGRICULTURE_TAX_AMOUNT
>			,LUXURY_TAX_RATE
>			,LUXURY_TAX_AMOUNT
>			,ROUND_OF_AMOUNT
>			,BOOKING_FROM
>			,BOOKING_TO
>			,BANK_CODE
>			,TRANSPORTER_CODE
>			,NVL(TYPE_OF_FREIGHT,'T')
>			,BILTY_NO
>			,BILTY_DATE
>			,GROSS_WEIGHT
>			,GATE_PASS_NO
>			,'Autogenerated Invoice'
>			,'O'
>			,PKG_SESSIONS.GET_USER_CODE
>			,SYSDATE
>			,V_INSURANCE_POLICY_NO
>--			,FORM49_NO
>			,OC_NO
>			,TOTAL_NO_OF_CASES
>--			,DISPATCH_CODE
>			,STORE_CODE
>			/* Add By Mr. Ms Dt 25/06/2017 For GST */
>			,IGST_AMOUNT
>			,CGST_AMOUNT
>			,SGST_AMOUNT
>			,CESS_AMOUNT
>			/* Dt 25/06/2017 For GST */
>			,CESS_ON_QTY --Add By MS Dt. 16/07/2017 For GST Cess on Qty
>   			,V_CUSTOMER_SALES --bUGZILLA CALL ID 4372  
>   			,V_INVOICE_TYPE
>   			,P_SERIES
>		FROM 	PROFORMA_INVOICES
>		WHERE 	COMPANY          = P_COMPANY_CODE
>		AND 	PROFORMA_TYPE    = P_PROFORMA_TYPE
>		AND 	ENTRY_NO         = P_ENTRY_NO;
>	EXCEPTION
>		WHEN NO_DATA_FOUND THEN NULL;
>		WHEN TOO_MANY_ROWS THEN NULL;
>	END;
>
>	/*---------------------------- INSERT INVOICE DETAILS -----------------------------------------------*/
>	BEGIN
>		INSERT INTO EXCISE_INVOICE_ITEMS
>			(EII_RECID
>			,PARENT_RECID
>			,COMPANY
>			,INVOICE_TYPE
>			,INVOICE_SERIES
>			,ENTRY_NO
>			,INVOICE_NO
>			,SEQ_NO
>--			,PROFORMA_TYPE
>--			,PROFORMA_SERIES
>--			,PROFORMA_NO
>--			,SO_SEQ_NO
>			,INVOICE_DATE
>			,PRODUCT_TYPE
>			,ITEM_CODE
>			,STOCK_TYPE
>			,STORE_ID
>			,FOC_ITEM
>			,ITEM_NAME
>			,AGENT_CODE
>			,ITEM_PRICE
>			,FREE_PRICE
>			,DISCOUNT_PRCNT_2
>			,DISCOUNT_AMOUNT_1
>			,DISCOUNT_PRCNT_3
>			,DISCOUNT_AMOUNT_2
>			,DISCOUNT_PRCNT_4
>			,DISCOUNT_AMOUNT_3
>			,DISCOUNT_PRCNT_1
>			,DISCOUNT_AMOUNT_4
>			,DISCOUNT_CODE_1
>			,DISCOUNT_CODE_2
>			,DISCOUNT_CODE_3
>			,DISCOUNT_CODE_4
>			,NET_DISCOUNT_AMOUNT
>			,NET_PRICE,UNIT_SIZE
>			,UNITS_IN_PACK
>			,NO_OF_PACKS
>			,ITEM_QTY
>			,TAX_CODE
>			,TAX_RATE
>			,SURCHARGE
>			,CREDITABLE_FLAG
>			,TAX_AMOUNT
>			,TURNOVER_TAX_RATE
>			,TURNOVER_TAX_AMOUNT
>			,INVOICE_VALUE
>			,REMARKS
>			,STATUS
>			,LUXURY_TAX_RATE
>			,LUXURY_TAX_AMOUNT
>			,TURNOVER_TAX_ON
>--			,SANCTION_NO
>--			,SANCTION_DATE
>			,TRADE_DISCOUNT_RATE
>			,TRADE_DISCOUNT_AMOUNT
>			,MAX_RETAIL_PRICE
>			,SALES_TAX_PLAN
>			/* Add By Mr. Ms Dt 25/06/2017 For GST */
>			,IGST_RATE
>			,IGST_AMOUNT
>			,CGST_RATE
>			,CGST_AMOUNT
>			,SGST_RATE
>			,SGST_AMOUNT
>			,CESS_RATE
>			,CESS_AMOUNT
>			/* Dt 25/06/2017 For GST */
>			,CESS_ON_QTY --Add By MS Dt. 16/07/2017 For GST Cess on Qty
>			--SK Tomar	06/05/2019
>			,COMMODITY_CODE
>			,EXCISE_TARIFF 
>			,CUSTOMER_CODE
>			)
>		SELECT
>			IMIS_RECID.NEXTVAL
>			,P_INVOICE_RECID
>			,COMPANY
>			,V_EXCISE_INV_TYPE
>			,V_EXCISE_SERIES
>			,I_ENTRY_NO
>			,V_NO
>			,SEQ_NO
>--			,PROFORMA_TYPE
>--			,PROFORMA_SERIES
>--			,PROFORMA_NO
>--			,SEQ_NO
>			,PROFORMA_DATE
>			,PRODUCT_TYPE
>			,ITEM_CODE
>			,STOCK_TYPE
>			,STORE_ID
>			,FOC_ITEM
>			,ITEM_NAME
>			,AGENT_CODE
>			,ITEM_PRICE
>			,FREE_PRICE
>			,DISCOUNT_PRCNT_2
>			,DISCOUNT_AMOUNT_1
>			,DISCOUNT_PRCNT_3
>			,DISCOUNT_AMOUNT_2
>			,DISCOUNT_PRCNT_4
>			,DISCOUNT_AMOUNT_3
>			,DISCOUNT_PRCNT_1
>			,DISCOUNT_AMOUNT_4
>			,DISCOUNT_CODE_1
>			,DISCOUNT_CODE_2
>			,DISCOUNT_CODE_3
>			,DISCOUNT_CODE_4
>			,NET_DISCOUNT_AMOUNT
>			,NET_PRICE
>			,UNIT_SIZE
>			,UNITS_IN_PACK
>			,NO_OF_PACKS
>			,ITEM_QTY
>			,TAX_CODE
>			,TAX_RATE
>			,SURCHARGE
>			,CREDITABLE_FLAG
>			,TAX_AMOUNT
>			,TURNOVER_TAX_RATE
>			,TURNOVER_TAX_AMOUNT
>			,SALE_ORDER_VALUE
>			,'Autogenerated Invoice Items'
>			,'O'
>			,LUXURY_TAX_PERCENT
>			,LUXURY_TAX_AMOUNT
>			,TURNOVER_TAX_ON
>--			,SANCTION_NO
>--			,SANCTION_DATE
>			,TRADE_DISCOUNT_RATE
>			,TRADE_DISCOUNT_AMOUNT
>			,MAX_RETAIL_PRICE
>			,SALES_TAX_PLAN
>			/* Add By Mr. Ms Dt 25/06/2017 For GST */
>			,IGST_RATE
>			,IGST_AMOUNT
>			,CGST_RATE
>			,CGST_AMOUNT
>			,SGST_RATE
>			,SGST_AMOUNT
>			,CESS_RATE
>			,CESS_AMOUNT
>			/* Dt 25/06/2017 For GST */
>			,CESS_ON_QTY --Add By MS Dt. 16/07/2017 For GST Cess on Qty
>			,PKG_FG.GET_COMMODITY_CODE (ITEM_CODE)
>			,PKG_FG.GET_HSN_CODE (COMPANY, ITEM_CODE, STOCK_TYPE)
>			,P_CUSTOMER_CODE
>		FROM PROFORMA_INVOICE_ITEMS
>		WHERE COMPANY        = P_COMPANY_CODE
>		AND PROFORMA_TYPE    = P_PROFORMA_TYPE
>		AND ENTRY_NO         = P_ENTRY_NO;
>	EXCEPTION
>		WHEN NO_DATA_FOUND THEN NULL;
>	END;
>/* ---------------------- INSERT WAREHOUSE TXN ITEM BATCH DTL RECORD  ------------------------------*/
>	/*
>	BEGIN
>		INSERT INTO WAREHOUSE_TXN_ITEM_BATCH_DTL
>			( 
>			  COMPANY
>			, TRANSACTION_TYPE
>			, TRANSACTION_SERIES
>			, ENTRY_NO
>			, SEQ_NO
>			, BATCH_NO
>			, TRANSACTION_ITEM_CODE
>			, TRANSACTION_STOCK_TYPE
>			, TRANSACTION_STORE_ID
>			, TRANSACTION_ITEM_QTY
>			, TRANSACTION_ITEM_RATE
>			, TRANSACTION_ITEM_VALUE
>			, NO_OF_PACKAGES
>			, UNITS_IN_PACK
>			, PACKING_QTY
>			, STATUS
>			, REMARKS
>			, POLICY_NO
>			, MFG_COMPANY
>			, MFG_BATCH_NO
>			, BATCH_MFG_DATE
>			, BATCH_EXPIRY_DATE
>			)
>		SELECT	
>			  W.COMPANY
>			, V_EXCISE_INV_TYPE
>			, V_EXCISE_SERIES
>			, I_ENTRY_NO
>			, W.SEQ_NO
>			, W.BATCH_NO
>			, W.TRANSACTION_ITEM_CODE
>			, W.TRANSACTION_STOCK_TYPE
>			, W.TRANSACTION_STORE_ID
>			, W.TRANSACTION_ITEM_QTY
>			, W.TRANSACTION_ITEM_RATE
>			, W.TRANSACTION_ITEM_VALUE
>			, W.NO_OF_PACKAGES
>			, W.UNITS_IN_PACK
>			, W.PACKING_QTY
>			, W.STATUS
>			, W.REMARKS
>			, W.POLICY_NO
>			, W.MFG_COMPANY
>			, W.MFG_BATCH_NO
>			, W.BATCH_MFG_DATE
>			, W.BATCH_EXPIRY_DATE
>		FROM WAREHOUSE_TXN_ITEM_BATCH_DTL W
>		WHERE W.COMPANY            	= P_COMPANY_CODE
>		AND W.TRANSACTION_TYPE   	= P_PROFORMA_TYPE
>		AND W.ENTRY_NO           	= P_ENTRY_NO;
>	EXCEPTION
>		WHEN NO_DATA_FOUND THEN NULL;
>	END;
>
>	-- Delete from Warehouse txn item batch dtl for 74 Type
>	DELETE FROM WAREHOUSE_TXN_ITEM_BATCH_DTL W
>	WHERE W.COMPANY            	= P_COMPANY_CODE
>	AND W.TRANSACTION_TYPE   	= P_PROFORMA_TYPE
>	AND W.ENTRY_NO           	= P_ENTRY_NO; 	
>	*/
>	
>	-- Update from Warehouse txn item batch dtl for 74 Type
>	UPDATE	WAREHOUSE_TXN_ITEM_BATCH_DTL W SET TRANSACTION_TYPE = V_EXCISE_INV_TYPE, TRANSACTION_SERIES = V_EXCISE_SERIES, ENTRY_NO	= I_ENTRY_NO	   
>	WHERE W.COMPANY            	= P_COMPANY_CODE
>	AND W.TRANSACTION_TYPE   	= P_PROFORMA_TYPE
>	AND W.ENTRY_NO           	= P_ENTRY_NO; 	
>		
>/*------------------------- SMS SENDING PROCEDURE----------------------------------------*/
>	BEGIN
>		SELECT NET_VALUE_OF_PROFORMA
>		INTO V_NET_VALUE
>		FROM PROFORMA_INVOICES
>		WHERE COMPANY        = P_COMPANY_CODE
>		AND PROFORMA_TYPE    = P_PROFORMA_TYPE
>		AND ENTRY_NO         = P_ENTRY_NO;
>	EXCEPTION
>		WHEN NO_DATA_FOUND THEN NULL;
>		WHEN TOO_MANY_ROWS THEN NULL;
>	END;
>
>	BEGIN
>		UPDATE PROFORMA_INVOICES
>			SET  INV_RECID        = P_INVOICE_RECID
>				,INVOICE_TYPE     = V_INVOICE_TYPE
>				,INVOICE_SERIES   = P_SERIES
>				,INVOICE_NO       = V_NO
>		WHERE COMPANY        = P_COMPANY_CODE
>		AND PROFORMA_TYPE    = P_PROFORMA_TYPE
>		AND ENTRY_NO         = P_ENTRY_NO;
>	END;
>
>	FOR R1 IN C1 ( P_COMPANY_CODE, P_INVOICE_RECID ) LOOP 
>	
>		BEGIN
>			SELECT 	STATE_CODE 
>			INTO V_SRC_STATE_CODE 
>			FROM VW_COMPANY_DETAIL 
>			WHERE COMPANY_CODE = R1.COMPANY;
>		EXCEPTION
>			WHEN NO_DATA_FOUND THEN NULL;
>			WHEN TOO_MANY_ROWS THEN NULL;
>		END;		
>		IF R1.XFR_COMPANY IS NOT NULL THEN
>			BEGIN		
>				SELECT STATE_CODE    , COUNTRY_CODE
>				INTO V_TGT_STATE_CODE, V_COUNTRY_CODE 
>				FROM VW_COMPANY_DETAIL 
>				WHERE COMPANY_CODE = R1.XFR_COMPANY;
>			EXCEPTION
>				WHEN NO_DATA_FOUND THEN NULL;
>				WHEN TOO_MANY_ROWS THEN NULL;
>			END;		
>		ELSE
>			BEGIN
>				SELECT STATE_CODE    , COUNTRY_CODE  
>				INTO V_TGT_STATE_CODE, V_COUNTRY_CODE 
>				FROM CUSTOMERS 
>				WHERE COMPANY 		= 	R1.COMPANY 
>				AND CUSTOMER_CODE 	=	R1.CUSTOMER_CODE;
>			EXCEPTION
>				WHEN NO_DATA_FOUND THEN NULL;
>				WHEN TOO_MANY_ROWS THEN NULL;
>			END;					
>		END IF; 
>		
>		V_ASSESSABLE_RATE	:=	0;
>		V_ASSESSABLE_RATE	:=	PKG_SL.CALC_CUST_PRICE (R1.COMPANY, R1.PRICE_METHOD, V_COUNTRY_CODE, V_SRC_STATE_CODE, V_TGT_STATE_CODE, R1.TAX_TYPE, R1.STORE_ID, R1.ITEM_CODE, R1.STOCK_TYPE, R1.INVOICE_DATE, R1.MAX_RETAIL_PRICE );
>		V_ASSESSABLE_RATE	:=	PKG_POINTERS.GET_TARIFF_PRICE; 
>		
>        UPDATE EXCISE_INVOICE_ITEMS SET ASSESSABLE_VALUE = ( V_ASSESSABLE_RATE * NVL(NO_OF_PACKS, ITEM_QTY))
>		WHERE COMPANY		= 	R1.COMPANY
>		AND PARENT_RECID 	=	R1.PARENT_RECID
>		AND EII_RECID		=	R1.EII_RECID;
>					 	
>		PKG_SB.DUTY_GEN
>	 		( R1.COMPANY
>	 		, R1.PRICE_METHOD
>	 		, V_COUNTRY_CODE
>	 		, V_SRC_STATE_CODE
>	 		, V_TGT_STATE_CODE
>	 		, R1.TAX_TYPE
>	 		, R1.ITEM_CODE
>	 		, R1.STOCK_TYPE
>	 		, R1.INVOICE_DATE
>	 		, R1.MAX_RETAIL_PRICE
>	 		, R1.NO_OF_PACKS
>	 		, R1.PARENT_RECID
>	 		, R1.EII_RECID
>	 		, R1.INVOICE_TYPE
>	 		, R1.INVOICE_SERIES
>	 		, R1.ENTRY_NO
>	 		, R1.PRODUCT_TYPE
>	 		, R1.SEQ_NO 
>	 		, R1.STATUS
>	 		); 
>	END LOOP;		
>
>END;
>-- End --
>
>--------------------------------------------------------------------------------------------------------------
>
>-- PKG_SB.PROFORMA_APPROVED --
>
>
>-- Line no.:- 7121
> 
>	-- Bgn --
>	-- Against call ID - 5993 
>	-- By ss on dt. 31/03/2021	 
>	IF PKG_CONTROLS.GET_COMPANY_INFO( P_COMPANY_CODE, 'COMPANY_TYPE') IN ( 'U' ) THEN
>		GENERATED_EXCISE_INVOICE  (P_COMPANY_CODE, P_CUSTOMER_CODE, P_PROFORMA_TYPE, P_PROFORMA_SERIES, P_ENTRY_NO, V_STORE_CODE, P_INVOICE_RECID);
>	ELSE
>	
>-- Line no.:- 7128 
>	END IF; 
>	-- End --
>
>---------------------------------------------------------------------------------------------------------------
>
>-- PKG_SB.PROFORMA_APPROVAL --
>
>
>-- Line no.:- 7768
> 
>	-- Bgn --
>	-- Against call ID - 5993 
>	-- By ss on dt. 31/03/2021	
>	IF PKG_CONTROLS.GET_COMPANY_INFO( P_COMPANY_CODE, 'COMPANY_TYPE') IN ( 'U' ) THEN
>		GENERATED_EXCISE_INVOICE  (P_COMPANY_CODE, P_CUSTOMER_CODE, P_PROFORMA_TYPE, P_PROFORMA_SERIES, P_ENTRY_NO, V_STORE_CODE, P_INVOICE_RECID);
>	ELSE
>	
>-- Line no.:- 7775
>
>	END IF; 
>	-- End --
>
>---------------------------------------------------------------------------------------------------------------
>-- PKG_FG.TPC_EXCISE_INVOICE --
>
>-- Line no.:- 22019
>
>				-- Bgn --
>				-- Against call ID - 5993 
>				-- By ss on dt. 31/03/2021	
>				IF n.INVOICE_TYPE IN ( 'LI') THEN
>					UPDATE 	PROFORMA_INVOICES
>					SET   	STATUS 		= 'Z'
>					WHERE 	COMPANY 	= n.COMPANY
>					AND   	PI_RECID 	= n.PI_RECID;
>				END IF;
>				-- End --
>
>-----------------------------------------------------------------------------------------------------------------
>
>-- PKG_SB.PREPARE_PROFORMA2 -- 
>
>
> 
>	-- Bgn --                   
>	-- Against call ID - 5993 
>	-- By ss on dt. 31/03/2021	
>	IF PKG_CONTROLS.GET_COMPANY_INFO( P_COMPANY_CODE, 'COMPANY_TYPE') IN ( 'U' ) THEN
>		PKG_SB.PREPARE_BOL_SLIPS(P_COMPANY_CODE, NULL, V_BOL_RECID, P_ERR_STRING);	 
>	END IF;
>

Actions: View

Attachments on bug 5993: 1836