/*
<VERSION> 1.0.0.7 </VERSION>
ADDED: Fixed to return set @@ERROR to @ERROR_CD to return actual error
<VERSION> 1.0.0.6 </VERSION>
ADDED: TO ENABLE THIS IMPORTSALES2 TO BE ABLE IMPORT IN THE SALES FOR DIFFERENT POS NO (ONE OUTLET THAT HAVING MANY POS)
THE THING AFFECTED IS THE AVERAGE COST BECAUSE OF THE SA_OUTLET_CD IS GETTING FROM THE FILENAME AND AFFECT THE SPUPDAVGCOST
SO NOW IS GET THE SA_OUT_CD FROM CONTROL.DBF
<VERSION> 1.0.0.5 </VERSION>
MODIFIED BY YOHAN
ADDED: UPDATING THE STAFF_S_CD AT HEADER TABLE IS NOT WORKING AND CURRENTLY THERE IS SOME TRANSACTION IS NOT HAVING THIS STAFF_S_CD SO CHANGE AS BELOW
PREVIOUS:
DECLARE CUR_STAFF_PURCH CURSOR FOR SELECT CO_CD, SA_OUT_CD, POS_NO, EMP_CD, BN_DT, TRN_DT, RCP_NO FROM #TEMP_EMPTRAN
CHANGE TO:
DECLARE CUR_STAFF_PURCH CURSOR FOR SELECT CO_CD, SA_OUT_CD, POS_NO, EMP_CD, BN_DT, TRN_DT, RCP_NO FROM EMPTRAN
<VERSION> 1.0.0.4 </VERSION>
MODIFIED BY YOHAN
ADDED: - IMPORT THE DEPOSIT TABLE
- IMPORT THE PRICECHG TABLE
- IMPORT THE STAFF PURCHASE TABLE
<VERSION> 1.0.0.3 </VERSION>
*/
CREATE PROCEDURE [dbo].[spImportSales2]
@DIRNAME AS VARCHAR(100)=NULL,
@FILENAME AS VARCHAR(200)=NULL,
@RESULT INT=0 OUTPUT
AS
DECLARE @TBLNAME AS VARCHAR(100)
DECLARE @PREFIX AS VARCHAR(2)
DECLARE @SA_OUTLET_CD AS VARCHAR(20)
DECLARE @BN_DT AS VARCHAR(20)
DECLARE @TEMP_TABLE AS VARCHAR(50)
DECLARE @TEMP_HEADER AS VARCHAR(50)
DECLARE @TEMP_DETAIL AS VARCHAR(50)
DECLARE @TEMP_PAYMT AS VARCHAR(50)
DECLARE @TEMP_IBALANCE AS VARCHAR(50)
DECLARE @SQLSTRING VARCHAR(3000)
DECLARE @POSNO VARCHAR(10)
DECLARE @ACC_SA_OUT_CD VARCHAR(10)
DECLARE @TOTAL_PROCESSED INT
DECLARE @ERROR_CD INT
SET @TOTAL_PROCESSED=0
-- SELECT * FROM TRN_POS_LOG WHERE [FILENAME]=@FILENAME AND LOG_TYPE='S' --s for sales
IF NOT EXISTS(SELECT * FROM TRN_POS_LOG WHERE [FILENAME]=@FILENAME AND LOG_TYPE='S')
BEGIN
-- Getting the sa_outlet_cd from the filename is not correct if one outlet has many pos
-- so the solution currently is to get from control.dbf that has been imported in
--GET SALES OUTLET CODE FROM FILENAME
/* SELECT @SA_OUTLET_CD=SUBSTRING(@FILENAME, CHARINDEX('.', @FILENAME)+1, 5) */
SELECT @SA_OUTLET_CD = SA_OUT_CD FROM #TEMP_CONTROL
--GET POSNO
SELECT @POSNO=SUBSTRING(@FILENAME, CHARINDEX('.', @FILENAME)+1, 5)
SELECT @ACC_SA_OUT_CD = SA_OUT_CD FROM MAS_POS WHERE POS_NO = @POSNO
--IF @ACC_SA_OUT_CD <> '' @SA_OUTLET_CD = @ACC_SA_OUT_CD
--GENERATE BUSINESS DATE FROM FILENAME
SELECT @BN_DT=SUBSTRING(@FILENAME,1,2)+' '+
CASE SUBSTRING(@FILENAME,3,2)
WHEN '01' THEN 'JAN'
WHEN '02' THEN 'FEB'
WHEN '03' THEN 'MAR'
WHEN '04' THEN 'APR'
WHEN '05' THEN 'MAY'
WHEN '06' THEN 'JUN'
WHEN '07' THEN 'JUL'
WHEN '08' THEN 'AUG'
WHEN '09' THEN 'SEP'
WHEN '10' THEN 'OCT'
WHEN '11' THEN 'NOV'
WHEN '12' THEN 'DEC'
END+' '+SUBSTRING(@FILENAME,5,2)
BEGIN TRAN
--############## UPDATE IDETAIL,IBALANCE,IHEADER DATE FOLLOW WITH BUS_DATE IN CONTROL
DECLARE @BUSS_DT DATETIME
SELECT @BUSS_DT=BUS_DATE FROM #TEMP_CONTROL
UPDATE #TEMP_IBALANCE SET BN_DATE = @BUSS_DT
UPDATE #TEMP_IHEADER SET BN_DT = @BUSS_DT
UPDATE #TEMP_IDETAIL SET BN_DT = @BUSS_DT
--############## UPDATE IDETAIL,IBALANCE,IHEADER DATE FOLLOW WITH BUS_DATE IN CONTROL
--##############
--HEADER
--##############
INSERT INTO HEADER
SELECT * FROM #TEMP_HEADER
UPDATE HEADER SET ROUND_AMT=0 WHERE ROUND_AMT IS NULL
--##############
--HEADER
--##############
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--DETAIL
--##############
INSERT INTO DETAIL
(CO_CD, SA_OUT_CD, POS_NO, SHF_NO, STAFF_C_CD,
STAFF_S_CD, TRN_NO, RCP_NO, ORD_NO, TRN_DT, TRN_TM, VOID_TM, BN_DT, TRN_TYPE,
PROD_CD, PROD_G01, PROD_G02, PROD_SIZE, PROD_COLOR, PROD_NM, SA_QTY, SA_PRICE,
SA_AMOUNT, PROD_COST, DISCOUNT, MULTI_DSC, NO_DISC, BARCODE, INV_QTY, TAX_1, TAX_2,
TAX_3, TAX_4, BONUS_PT, BUY_TYPE, P_LEVEL, PROMO_CD, PROMO_AMT, PROD_COMM, PRT_BILL,
PRT_KPT, KPT_NO, CONDIMENT, SEARCH_GRP, PROD_TYPE, STK_CONTRL)
SELECT CO_CD, SA_OUT_CD, POS_NO, SHF_NO, STAFF_C_CD,
STAFF_S_CD, TRN_NO, RCP_NO, ORD_NO, TRN_DT, TRN_TM, VOID_TM, BN_DT, TRN_TYPE,
PROD_CD, PROD_G01, PROD_G02, PROD_SIZE, PROD_COLOR, PROD_NM, SA_QTY, SA_PRICE,
SA_AMOUNT, PROD_COST, DISCOUNT, MULTI_DSC, NO_DISC, BARCODE, INV_QTY, TAX_1, TAX_2,
TAX_3, TAX_4, BONUS_PT, BUY_TYPE, P_LEVEL, PROMO_CD, PROMO_AMT, PROD_COMM, PRT_BILL,
PRT_KPT, KPT_NO, CONDIMENT, SEARCH_GRP, PROD_TYPE, STK_CONTRL FROM #TEMP_DETAIL SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
/*
--UPDATE AVECOST OF IDETAIL
EXEC spUpdAveCost @SA_OUTLET_CD , @BN_DT
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--UPDATE PROD TYPE OF IDETAIL
EXEC spUpdDetailProdType @SA_OUTLET_CD , @BN_DT
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
*/
--##############
--DETAIL
--##############
--##############
--PAYMENT
--##############
INSERT INTO PAYMENT
SELECT * FROM #TEMP_PAYMENT
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--PAYMENT
--##############
--##############
--CONTROL
--##############
INSERT INTO CONTROL
SELECT * FROM #TEMP_CONTROL
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--CONTROL
--##############
--##############
--HOUR SALE
--##############
INSERT INTO HOURSALE
SELECT * FROM #TEMP_HOURSALE
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--HOUR SALE
--##############
--##############
--PLU SALE
--##############
EXEC spImportPLUSALE @BN_DT, @ACC_SA_OUT_CD ,'N'
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--PLU SALE
--##############
--##############
--TRAN SALE
--##############
INSERT INTO TRANSALE
SELECT * FROM #TEMP_TRANSALE
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
EXEC spImportOutletTransale @SA_OUTLET_CD, @BN_DT
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--EXEC spIMPORTNEWTRANSALE @SA_OUTLET_CD, @BN_DT
--##############
--TRAN SALE
--##############
--##############
--DEPOSIT
--##############
/* INSERT INTO DEPOSIT
SELECT * FROM #TEMP_DEPOSIT
IF @@ERROR<>0
BEGIN
SET @RESULT=@@ERROR
ROLLBACK TRAN
RETURN
END
*/
--##############
--DEPOSIT
--##############
--##############
--PRICECHG
--##############
/*
INSERT INTO TRN_POS_CHG
SELECT * FROM #TEMP_PRICECHG
IF @@ERROR<>0
BEGIN
SET @RESULT=@@ERROR
ROLLBACK TRAN
RETURN
END
*/
--##############
---PRICECHG
--##############
--##############
--EMPTRAN
--##############
/*
INSERT INTO EMPTRAN
SELECT * FROM #TEMP_EMPTRAN
IF @@ERROR<>0
BEGIN
SET @RESULT=@@ERROR
ROLLBACK TRAN
RETURN
END
-- TO RECORD THE STAFF PURCHASE
DECLARE @CO_CD VARCHAR(5)
DECLARE @SA_OUT_CD VARCHAR(5)
DECLARE @POS_NO VARCHAR(5)
DECLARE @EMP_CD VARCHAR(15)
DECLARE @BN_DT2 DATETIME
DECLARE @TRN_DT DATETIME
DECLARE @RCP_NO VARCHAR(30)
--- CHANGES AS VERSION 1.0.0.5
---- DECLARE CUR_STAFF_PURCH CURSOR FOR SELECT CO_CD, SA_OUT_CD, POS_NO, EMP_CD, BN_DT, TRN_DT, RCP_NO FROM #TEMP_EMPTRAN
DECLARE CUR_STAFF_PURCH CURSOR FOR SELECT CO_CD, SA_OUT_CD, POS_NO, EMP_CD, BN_DT, TRN_DT, RCP_NO FROM EMPTRAN
OPEN CUR_STAFF_PURCH
FETCH CUR_STAFF_PURCH INTO @CO_CD, @SA_OUT_CD, @POS_NO, @EMP_CD, @BN_DT2, @TRN_DT, @RCP_NO
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE HEADER SET STAFF_S_CD = @EMP_CD WHERE CO_CD=@CO_CD AND SA_OUT_CD=@SA_OUT_CD AND POS_NO=@POS_NO AND BN_DT=@BN_DT2 AND TRN_DT=@TRN_DT AND RCP_NO=@RCP_NO
FETCH CUR_STAFF_PURCH INTO @CO_CD, @SA_OUT_CD, @POS_NO, @EMP_CD, @BN_DT2, @TRN_DT, @RCP_NO
END
*/
--##############
---EMPTRAN
--##############
--##############
--IBALANCE
--##############
--ADDED BY MAGENDREN FOR SALES TO SALES ORDER POSTING FOR DMK
--BEGIN
INSERT INTO IBALANCE
(BN_DATE, STK_LOC, PROD_CD, PROD_G01, PROD_G02, PROD_NM, STOCK_UOM, LAST_COST, PRE_COST, AVE_COST,
OPEN_STK, STK_RECV, STK_RETURN, TRANFERIN, TRANFEROUT, STK_USAGE, STK_ADJ, STK_WASTE, STK_STAFF, STK_GUEST,
BOM, STK_CONV, STK_VAR, TDY_USAGE, STK_CLOSE )
SELECT BN_DATE, STK_LOC, PROD_CD, PROD_G01, PROD_G02, PROD_NM, STOCK_UOM, LAST_COST, PRE_COST, AVE_COST,
OPEN_STK, STK_RECV, STK_RETURN, TRANFERIN, TRANFEROUT, STK_USAGE, STK_ADJ, STK_WASTE, STK_STAFF, STK_GUEST,
BOM, STK_CONV, STK_VAR, TDY_USAGE, STK_CLOSE FROM #TEMP_IBALANCE SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--IBALANCE
--##############
--##############
--IHEADER
--##############
--BEGIN
INSERT INTO IHEADER
(CO_CD,SA_OUT_CD,STK_LOC,POS_NO,STAFF_C_CD,TRN_TYPE,REF_NO,TRN_NO,TRN_DT,BN_DT,GRS_AMT,DISCOUNT,
COST,TAX_AMT_1,TAX_AMT_2,ROUND_AMT,LOC_TO,LOC_FROM,REMARKS,VENDOR_CD)
SELECT CO_CD,SA_OUT_CD,STK_LOC,POS_NO,STAFF_C_CD,TRN_TYPE,REF_NO,TRN_NO,TRN_DT,BN_DT,GRS_AMT,DISCOUNT,
COST,TAX_AMT_1,TAX_AMT_2,ROUND_AMT,LOC_TO,LOC_FROM,REMARKS,VENDOR_CD FROM #TEMP_IHEADER SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--IHEADER
--##############
--##############
--IDETAIL
--##############
--BEGIN
INSERT INTO IDETAIL
(CO_CD,SA_OUT_CD,STK_LOC,REF_NO,TRN_NO,TRN_DT,BN_DT,TRN_TYPE,PROD_CD,PROD_G01,PROD_G02,
PROD_SIZE,PROD_COLOR,PROD_NM,UOM,CONV,SA_QTY,SA_AMOUNT,PROD_COST,DISCOUNT,BARCODE,INV_QTY,
TAX_1,TAX_2,PROD_TYPE,STK_CONTRL)
SELECT CO_CD,SA_OUT_CD,STK_LOC,REF_NO,TRN_NO,TRN_DT,BN_DT,TRN_TYPE,PROD_CD,PROD_G01,PROD_G02,
PROD_SIZE,PROD_COLOR,PROD_NM,UOM,CONV,SA_QTY,SA_AMOUNT,PROD_COST,DISCOUNT,BARCODE,INV_QTY,
TAX_1,TAX_2,PROD_TYPE,STK_CONTRL FROM #TEMP_IDETAIL SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--IDETAIL
--##############
UPDATE IHEADER SET [STATUS]='P'
UPDATE IDETAIL SET [STATUS]='P'
UPDATE IHEADER SET REF_DT=BN_DT where REF_DT is null
UPDATE IDETAIL SET REF_DATE=BN_DT where REF_DATE is null
--##############
--POST STOCK TRANSFER
--##############
EXEC spImportStkTransfer @BN_DT
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--POST STOCK TRANSFER
--##############
--##############
--POST INVENTORY
--##############
--ADDED BY KIT FOR SALES TO SALES ORDER POSTING FOR DMK
--BEGIN
DECLARE @LINK_TYPE VARCHAR(2)
SELECT @LINK_TYPE=UPPER(LINK_TYPE) FROM SA_OUT WHERE SA_OL_CD=@SA_OUTLET_CD
IF @LINK_TYPE='C'
BEGIN
EXEC spSalesToSalesOrder @BN_DT, @SA_OUTLET_CD
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
END
ELSE
BEGIN
EXEC spPostInventory @BN_DT, @SA_OUTLET_CD, @TOTAL_PROCESSED, @FILENAME
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
END
--##############
--POST INVENTORY
--##############
--##############
--INSERT INTO LOG TABLE
--##############
INSERT INTO TRN_POS_LOG
(FILENAME, ADD_DATE, USR_ID, LOG_TYPE, SA_OUTLET_CD)
VALUES
(@FILENAME, GETDATE(), 'AUTO', 'S', @SA_OUTLET_CD)
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--INSERT INTO LOG TABLE
--##############
--##############
--ADDITIONAL STEPS
--##############
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='spCustomizedDayend')
BEGIN
EXEC spCustomizedDayend @ACC_SA_OUT_CD, @BN_DT
IF @@ERROR<>0
BEGIN
SET @RESULT=@@ERROR
ROLLBACK TRAN
RETURN
END
END
--##############
--ADDITIONAL STEPS
--##############
COMMIT TRAN
END --END OF IF STATEMENT
GO
