Untitled

--IF NOT EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMI_APPS')
--BEGIN
--	CREATE DATABASE FAMI_APPS
--END

USE FAMI_APPS
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiordernotif')
BEGIN
	CREATE TABLE famiordernotif
	(
		POSID varchar(100),
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY(POSID)
	)
END
GO


IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderHH')
BEGIN
	CREATE TABLE famiorderHH
	(
		OrderID varchar(100),
		Phone varchar(100),
		account_name VARCHAR(200),
		StoreCode varchar(100),
		OrderType varchar(100),
		PickUpTime varchar(100),
		Addr_Label varchar(100),
		Addr_Desc varchar(200),
		Addr_Map varchar(8000),
		Addr_phone_address varchar(100),
		Addr_note_address varchar(100),
		Addr_receiver varchar(100),
		Addr_longitude varchar(100),
		Addr_latitude varchar(100),
		TenderID varchar(100),
		TenderType varchar(100),
		TenderReferenceNo varchar(100),
		ShippingPrice DECIMAL(19,5),
		ShippingDisc DECIMAL(19,5),
		ShippingProvider varchar(100),
		ShippingReferenceNo varchar(100),
		ShippingTracking varchar(8000),
		Shipping_reference_no_tarif varchar(100),
		ShippingCourier varchar(8000),
		ShippingDesc varchar(200),
		TOTAL DECIMAL(19,5),
		DPP DECIMAL(19,5),
		TAX DECIMAL(19,5),
		Total_Tendered DECIMAL(19,5),
		ServicesFee DECIMAL(19,5),
		ServicesFeeFMI DECIMAL(19,5),
		ServicesFeeFMIDisc DECIMAL(19,5),
		Cashback_used  DECIMAL(19,5),
		Cashback_borned DECIMAL(19,5),
		CouponShippingDiscID VARCHAR(100),
		CouponSalesDiscID VARCHAR(100),
		CouponDiscAmt DECIMAL(19,5),
		ReferenceNo varchar(100),
		Note varchar(8000),
		Rate varchar(100),
		Review varchar(8000),
		OrderSts varchar(100),
		ORDERDT DATETIME,
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		RCPOSID VARCHAR(10),
		RCPOSMSG VARCHAR(8000),
		RCPOSDT datetime,
		POSReceiptNo VARCHAR(100),
		MotherStoreCode VARCHAR(100),
		Businessday VARCHAR(100),
		POSID VARCHAR(100),
		SHIFTID VARCHAR(100),
		RCUPDTRCPT VARCHAR(100),
		RMUPDTRCPT VARCHAR(200),
		DTUPDTRCPT DATETIME,
		RCUPDTRTN VARCHAR(100),
		RMUPDTRTN VARCHAR(200),
		DTUPDTRTN DATETIME,
		RTNJSON VARCHAR(8000),
		ISRTNFULL INT,
		RCPOSIDS2 VARCHAR(10),
		RCPOSMSGS2 VARCHAR(8000),
		RCPOSDTS2 DATETIME,
		RCKTCS2 VARCHAR(10),
		RCKTCMSGS2 VARCHAR(8000),
		RCKTCDTS2 datetime,
		KTCJSONS2 VARCHAR(8000),
		QUEUENO VARCHAR(100),
		S2NOTIF INT,
		RCRQPICKUP VARCHAR(10),
		RMRQPICKUP VARCHAR(8000),
		DTRQPICKUP DATETIME,
		JSONRQPICKUP VARCHAR(8000),
		IsCALLCSO varchar(10),
		CALLCSODT datetime,
		RCCALLCSO varchar(10),
		RMCALLCSO varchar(8000),
		ISGROCERY int,
		CouponSukaSukaID VARCHAR(100),
		CouponSukaSukaValue DECIMAL(19,5),
		RCSUPPLY VARCHAR(10),
		RMSUPPLY VARCHAR(100),
		RDSUPPLY DATETIME,
		ISSYNCSUPPLY INT,
		ISSYNCDTSUPPLY DATETIME,
		RTN_AMT DECIMAL(19,5),
		PRIMARY KEY (OrderID)
	)

	CREATE INDEX famiorderHH_idx1 ON famiorderHH(POSReceiptNo)
END
GO


IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderS2HH')
BEGIN
	CREATE TABLE famiorderS2HH
	(
		OrderID varchar(100),
		Phone varchar(100),
		account_name VARCHAR(200),
		StoreCode varchar(100),
		OrderType varchar(100),
		PickUpTime varchar(100),
		Addr_Label varchar(100),
		Addr_Desc varchar(200),
		Addr_Map varchar(8000),
		Addr_phone_address varchar(100),
		Addr_note_address varchar(100),
		Addr_receiver varchar(100),
		Addr_longitude varchar(100),
		Addr_latitude varchar(100),
		TenderID varchar(100),
		TenderType varchar(100),
		TenderReferenceNo varchar(100),
		ShippingPrice DECIMAL(19,5),
		ShippingDisc DECIMAL(19,5),
		ShippingProvider varchar(100),
		ShippingReferenceNo varchar(100),
		ShippingTracking varchar(8000),
		Shipping_reference_no_tarif varchar(100),
		ShippingCourier varchar(8000),
		ShippingDesc varchar(200),
		TOTAL DECIMAL(19,5),
		DPP DECIMAL(19,5),
		TAX DECIMAL(19,5),
		Total_Tendered DECIMAL(19,5),
		ServicesFee DECIMAL(19,5),
		ServicesFeeFMI DECIMAL(19,5),
		ServicesFeeFMIDisc DECIMAL(19,5),
		Cashback_used  DECIMAL(19,5),
		Cashback_borned DECIMAL(19,5),
		CouponShippingDiscID VARCHAR(100),
		CouponSalesDiscID VARCHAR(100),
		CouponDiscAmt DECIMAL(19,5),
		ReferenceNo varchar(100),
		Note varchar(8000),
		Rate varchar(100),
		Review varchar(8000),
		OrderSts varchar(100),
		ORDERDT DATETIME,
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		RCPOSID VARCHAR(10),
		RCPOSMSG VARCHAR(8000),
		RCPOSDT datetime,
		POSReceiptNo VARCHAR(100),
		MotherStoreCode VARCHAR(100),
		Businessday VARCHAR(100),
		POSID VARCHAR(100),
		SHIFTID VARCHAR(100),
		RCUPDTRCPT VARCHAR(100),
		RMUPDTRCPT VARCHAR(200),
		DTUPDTRCPT DATETIME,
		RCUPDTRTN VARCHAR(100),
		RMUPDTRTN VARCHAR(200),
		DTUPDTRTN DATETIME,
		RTNJSON VARCHAR(8000),
		ISRTNFULL INT,
		RCPOSIDS2 VARCHAR(10),
		RCPOSMSGS2 VARCHAR(8000),
		RCPOSDTS2 DATETIME,
		RCKTCS2 VARCHAR(10),
		RCKTCMSGS2 VARCHAR(8000),
		RCKTCDTS2 datetime,
		KTCJSONS2 VARCHAR(8000),
		QUEUENO VARCHAR(100),
		S2NOTIF INT,
		RCRQPICKUP VARCHAR(10),
		RMRQPICKUP VARCHAR(8000),
		DTRQPICKUP DATETIME,
		JSONRQPICKUP VARCHAR(8000),
		IsCALLCSO varchar(10),
		CALLCSODT datetime,
		RCCALLCSO varchar(10),
		RMCALLCSO varchar(8000),
		ISGROCERY int,
		CouponSukaSukaID VARCHAR(100),
		CouponSukaSukaValue DECIMAL(19,5),
		RCSUPPLY VARCHAR(10),
		RMSUPPLY VARCHAR(100),
		RDSUPPLY DATETIME,
		ISSYNCSUPPLY INT,
		ISSYNCDTSUPPLY DATETIME,
		RTN_AMT DECIMAL(19,5),
		PRIMARY KEY (OrderID)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderHT')
BEGIN
	CREATE TABLE famiorderHT
	(
		SESSIONID varchar(100),
		OrderID varchar(100),
		Phone varchar(100),
		account_name VARCHAR(200),
		StoreCode varchar(100),
		OrderType varchar(100),
		PickUpTime varchar(100),
		Addr_Label varchar(100),
		Addr_Desc varchar(200),
		Addr_Map varchar(8000),
		Addr_phone_address varchar(100),
		Addr_note_address varchar(100),
		Addr_receiver varchar(100),
		Addr_longitude varchar(100),
		Addr_latitude varchar(100),
		TenderID varchar(100),
		TenderType varchar(100),
		TenderReferenceNo varchar(100),
		ShippingPrice DECIMAL(19,5),
		ShippingDisc DECIMAL(19,5),
		ShippingProvider varchar(100),
		ShippingReferenceNo varchar(100),
		ShippingTracking varchar(8000),
		Shipping_reference_no_tarif varchar(100),
		ShippingCourier varchar(8000),
		ShippingDesc varchar(200),
		TOTAL DECIMAL(19,5),
		DPP DECIMAL(19,5),
		TAX DECIMAL(19,5),
		Total_Tendered DECIMAL(19,5),
		ServicesFee DECIMAL(19,5),
		ServicesFeeFMI DECIMAL(19,5),
		ServicesFeeFMIDisc DECIMAL(19,5),
		Cashback_used  DECIMAL(19,5),
		Cashback_borned DECIMAL(19,5),
		CouponShippingDiscID VARCHAR(100),
		CouponSalesDiscID VARCHAR(100),
		CouponDiscAmt DECIMAL(19,5),
		ReferenceNo varchar(100),
		Note varchar(8000),
		Rate varchar(100),
		Review varchar(8000),
		OrderSts varchar(100),
		ORDERDT DATETIME,
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		RCPOSID VARCHAR(10),
		RCPOSMSG VARCHAR(8000),
		RCPOSDT datetime,
		POSReceiptNo VARCHAR(100),
		MotherStoreCode VARCHAR(100),
		Businessday VARCHAR(100),
		POSID VARCHAR(100),
		SHIFTID VARCHAR(100),
		CouponSukaSukaID VARCHAR(100),
		CouponSukaSukaValue DECIMAL(19,5),
		PRIMARY KEY (SESSIONID,OrderID)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderDH')
BEGIN
	CREATE TABLE famiorderDH
	(
		OrderID varchar(100),
		LNITMSEQ INT,
		ItemCode varchar(100),
		Barcode varchar(100),
		ItemDescriptionLong varchar(200),
		ItemDescriptionShort varchar(100),
		ItemType varchar(100),
		MerchCat varchar(100),
		TaxCode varchar(100),
		CategoryID varchar(100),
		UnitPrice DECIMAL(19,5),
		QTY DECIMAL(17,5),
		QTYPROMO DECIMAL(17,5),
		DISC DECIMAL(19,5),
		SUBTOTAL DECIMAL(19,5),
		DPP DECIMAL(19,5),
		TAX DECIMAL(19,5),
		VIPID VARCHAR(100),
		TOTAL DECIMAL(19,5),
		TOTALPROMO DECIMAL(19,5),
		ADDITIONAL VARCHAR(8000),
		TOPPING VARCHAR(8000),
		TOPINGPRICE DECIMAL(19,5),
		PROMOCODE VARCHAR(100),
		NOTE VARCHAR(8000),
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (OrderID,LNITMSEQ)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderS2DH')
BEGIN
	CREATE TABLE famiorderS2DH
	(
		OrderID varchar(100),
		LNITMSEQ INT,
		ItemCode varchar(100),
		Barcode varchar(100),
		ItemDescriptionLong varchar(200),
		ItemDescriptionShort varchar(100),
		ItemType varchar(100),
		MerchCat varchar(100),
		TaxCode varchar(100),
		CategoryID varchar(100),
		UnitPrice DECIMAL(19,5),
		QTY DECIMAL(17,5),
		QTYPROMO DECIMAL(17,5),
		DISC DECIMAL(19,5),
		SUBTOTAL DECIMAL(19,5),
		DPP DECIMAL(19,5),
		TAX DECIMAL(19,5),
		VIPID VARCHAR(100),
		TOTAL DECIMAL(19,5),
		TOTALPROMO DECIMAL(19,5),
		ADDITIONAL VARCHAR(8000),
		TOPPING VARCHAR(8000),
		TOPINGPRICE DECIMAL(19,5),
		PROMOCODE VARCHAR(100),
		NOTE VARCHAR(8000),
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (OrderID,LNITMSEQ)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderSuppliesHH')
BEGIN
	CREATE TABLE famiorderSuppliesHH
	(
		OrderID varchar(100),
		IsConfirmed int,
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (OrderID,IsConfirmed)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderSuppliesDH')
BEGIN
	CREATE TABLE famiorderSuppliesDH
	(
		OrderID varchar(100),
		Barcode varchar(100),
		Qty Decimal(17,5),
		IsSync int,
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (OrderID,Barcode)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderSuppliesT')
BEGIN
	CREATE TABLE famiorderSuppliesT
	(
		SESSIONID varchar(100),
		OrderID varchar(100),
		Barcode varchar(100),
		Qty Decimal(17,5),
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (SESSIONID,OrderID,Barcode)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderS2SuppliesHH')
BEGIN
	CREATE TABLE famiorderS2SuppliesHH
	(
		OrderID varchar(100),
		IsConfirmed int,
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (OrderID,IsConfirmed)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderS2SuppliesDH')
BEGIN
	CREATE TABLE famiorderS2SuppliesDH
	(
		OrderID varchar(100),
		Barcode varchar(100),
		Qty Decimal(17,5),
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (OrderID,Barcode)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderDT')
BEGIN
	CREATE TABLE famiorderDT
	(
		SESSIONID varchar(100),
		OrderID varchar(100),
		LNITMSEQ INT,
		ItemCode varchar(100),
		Barcode varchar(100),
		ItemDescriptionLong varchar(200),
		ItemDescriptionShort varchar(100),
		ItemType varchar(100),
		MerchCat varchar(100),
		TaxCode varchar(100),
		CategoryID varchar(100),
		UnitPrice DECIMAL(19,5),
		QTY DECIMAL(17,5),
		QTYPROMO DECIMAL(17,5),
		DISC DECIMAL(19,5),
		SUBTOTAL DECIMAL(19,5),
		DPP DECIMAL(19,5),
		TAX DECIMAL(19,5),
		VIPID VARCHAR(100),
		TOTAL DECIMAL(19,5),
		TOTALPROMO DECIMAL(19,5),
		ADDITIONAL VARCHAR(8000),
		TOPPING VARCHAR(8000),
		TOPINGPRICE DECIMAL(19,5),
		PROMOCODE VARCHAR(100),
		NOTE VARCHAR(8000),
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (SESSIONID,OrderID,LNITMSEQ)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderAddonDH')
BEGIN
	CREATE TABLE famiorderAddonDH
	(
		OrderID varchar(100),
		LNITMSEQ INT,
		ORD INT,
		ItemCode varchar(100),
		Barcode varchar(100),
		ItemDescriptionLong varchar(200),
		ItemDescriptionShort varchar(100),
		ItemType varchar(100),
		MerchCat varchar(100),
		TaxCode varchar(100),
		UnitPrice DECIMAL(19,5),
		QTY DECIMAL(17,5),
		DISC DECIMAL(19,5),
		DPP DECIMAL(19,5),
		TAX DECIMAL(19,5),
		TOTAL DECIMAL(19,5),
		PROMOCODE VARCHAR(100),
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (OrderID,LNITMSEQ,ORD)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderAddonS2DH')
BEGIN
	CREATE TABLE famiorderAddonS2DH
	(
		OrderID varchar(100),
		LNITMSEQ INT,
		ORD INT,
		ItemCode varchar(100),
		Barcode varchar(100),
		ItemDescriptionLong varchar(200),
		ItemDescriptionShort varchar(100),
		ItemType varchar(100),
		MerchCat varchar(100),
		TaxCode varchar(100),
		UnitPrice DECIMAL(19,5),
		QTY DECIMAL(17,5),
		DISC DECIMAL(19,5),
		DPP DECIMAL(19,5),
		TAX DECIMAL(19,5),
		TOTAL DECIMAL(19,5),
		PROMOCODE VARCHAR(100),
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (OrderID,LNITMSEQ,ORD)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderAddonDT')
BEGIN
	CREATE TABLE famiorderAddonDT
	(
		SESSIONID varchar(100),
		OrderID varchar(100),
		LNITMSEQ INT,
		ORD INT,
		ItemCode varchar(100),
		Barcode varchar(100),
		ItemDescriptionLong varchar(200),
		ItemDescriptionShort varchar(100),
		ItemType varchar(100),
		MerchCat varchar(100),
		TaxCode varchar(100),
		UnitPrice DECIMAL(19,5),
		QTY DECIMAL(17,5),
		DISC DECIMAL(19,5),
		DPP DECIMAL(19,5),
		TAX DECIMAL(19,5),
		TOTAL DECIMAL(19,5),
		PROMOCODE VARCHAR(100),
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (SESSIONID,OrderID,LNITMSEQ,ORD)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderPH')
BEGIN
	CREATE TABLE famiorderPH
	(
		OrderID varchar(100),
		StoreCode varchar(100),
		PROMOCODE VARCHAR(100),
		LIMITQTY INT,
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (OrderID,PROMOCODE)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderS2PH')
BEGIN
	CREATE TABLE famiorderS2PH
	(
		OrderID varchar(100),
		StoreCode varchar(100),
		PROMOCODE VARCHAR(100),
		LIMITQTY INT,
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (OrderID,PROMOCODE)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderPT')
BEGIN
	CREATE TABLE famiorderPT
	(
		SESSIONID varchar(100),
		OrderID varchar(100),
		StoreCode varchar(100),
		PROMOCODE VARCHAR(100),
		LIMITQTY INT,
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (SESSIONID,OrderID,PROMOCODE)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderFH')
BEGIN
	CREATE TABLE famiorderFH
	(
		OrderID varchar(100),
		S1 varchar(100),
		S1DT datetime,
		S2 varchar(100),
		S2DT datetime,
		QueueNo varchar(100),
		POSReceiptNo varchar(100),
		CashierID varchar(100),
		RPCK_STS varchar(100),
		RPCK_DT datetime,
		call_cso varchar(100),
		call_csoDT datetime,
		finish_cso varchar(100),
		finishcsoDT datetime,
		RTN_STS varchar(100),
		RTN_JSON varchar(8000),
		RFN_STS varchar(100),
		RFN_JSON varchar(8000),
		isFullReturn varchar(100), 
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		S1RC varchar(100),
		S1RM varchar(8000),
		S1RD datetime,
		S2RC varchar(100),
		S2RM varchar(8000),
		S2RD datetime,
		RTN_AMT DECIMAL(19,5),
		SUPLY_JSON VARCHAR(8000),
		KITCHEN_JSON VARCHAR(8000),
		Businessday VARCHAR(100),
		POSID VARCHAR(100),
		SHIFTID VARCHAR(100),
		MotherStoreCode VARCHAR(100),
		JSONRQPICKUP VARCHAR(8000),
		ISGROCERY VARCHAR(100),
		PRIMARY KEY (OrderID)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderS2FH')
BEGIN
	CREATE TABLE famiorderS2FH
	(
		OrderID varchar(100),
		S1 varchar(100),
		S1DT datetime,
		S2 varchar(100),
		S2DT datetime,
		QueueNo varchar(100),
		POSReceiptNo varchar(100),
		CashierID varchar(100),
		RPCK_STS varchar(100),
		RPCK_DT datetime,
		call_cso varchar(100),
		call_csoDT datetime,
		finish_cso varchar(100),
		finishcsoDT datetime,
		RTN_STS varchar(100),
		RTN_JSON varchar(8000),
		RFN_STS varchar(100),
		RFN_JSON varchar(8000),
		isFullReturn varchar(100), 
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		S1RC varchar(100),
		S1RM varchar(8000),
		S1RD datetime,
		S2RC varchar(100),
		S2RM varchar(8000),
		S2RD datetime,
		RTN_AMT DECIMAL(19,5),
		SUPLY_JSON VARCHAR(8000),
		KITCHEN_JSON VARCHAR(8000),
		Businessday VARCHAR(100),
		POSID VARCHAR(100),
		SHIFTID VARCHAR(100),
		MotherStoreCode VARCHAR(100),
		JSONRQPICKUP VARCHAR(8000),
		ISGROCERY VARCHAR(100),
		PRIMARY KEY (OrderID)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderFT')
BEGIN
	CREATE TABLE famiorderFT
	(
		SESSIONID varchar(100),
		OrderID varchar(100),
		S1 varchar(100),
		S1DT datetime,
		S2 varchar(100),
		S2DT datetime,
		QueueNo varchar(100),
		POSReceiptNo varchar(100),
		CashierID varchar(100),
		RPCK_STS varchar(100),
		RPCK_DT datetime,
		call_cso varchar(100),
		call_csoDT datetime,
		finish_cso varchar(100),
		finishcsoDT datetime,
		RTN_STS varchar(100),
		RTN_JSON varchar(8000),
		RFN_STS varchar(100),
		RFN_JSON varchar(8000),
		isFullReturn varchar(100), 
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		S1RC varchar(100),
		S1RM varchar(8000),
		S1RD datetime,
		S2RC varchar(100),
		S2RM varchar(8000),
		S2RD datetime,
		RTN_AMT DECIMAL(19,5),
		SUPLY_JSON VARCHAR(8000),
		KITCHEN_JSON VARCHAR(8000),
		Businessday VARCHAR(100),
		POSID VARCHAR(100),
		SHIFTID VARCHAR(100),
		MotherStoreCode VARCHAR(100),
		JSONRQPICKUP VARCHAR(8000),
		ISGROCERY VARCHAR(100),
		PRIMARY KEY (SESSIONID,OrderID)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderSubsT')
BEGIN
	CREATE TABLE famiorderSubsT
	(
		SESSIONID varchar(100),
		OrderID varchar(100),
		old_article varchar(100),
		old_qty DECIMAL(17,5),
		old_price DECIMAL(19,5),
		old_barcode VARCHAR(100),
		new_article varchar(100),
		new_qty DECIMAL(17,5),
		new_price DECIMAL(19,5),
		new_barcode VARCHAR(100),
		trx_date DATETIME,
		NOTE VARCHAR(8000),
		detail_id varchar(100),
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (SESSIONID,OrderID,detail_id)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderSubsH')
BEGIN
	CREATE TABLE famiorderSubsH
	(
		OrderID varchar(100),
		old_article varchar(100),
		old_qty DECIMAL(17,5),
		old_price DECIMAL(19,5),
		old_barcode VARCHAR(100),
		new_article varchar(100),
		new_qty DECIMAL(17,5),
		new_price DECIMAL(19,5),
		new_barcode VARCHAR(100),
		trx_date DATETIME,
		NOTE VARCHAR(8000),
		detail_id varchar(100),
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (OrderID,detail_id)
	)
END
GO

IF NOT EXISTS (SELECT '' from sysobjects where name = 'famiorderS2SubsH')
BEGIN
	CREATE TABLE famiorderS2SubsH
	(
		OrderID varchar(100),
		old_article varchar(100),
		old_qty DECIMAL(17,5),
		old_price DECIMAL(19,5),
		old_barcode VARCHAR(100),
		new_article varchar(100),
		new_qty DECIMAL(17,5),
		new_price DECIMAL(19,5),
		new_barcode VARCHAR(100),
		trx_date DATETIME,
		NOTE VARCHAR(8000),
		detail_id varchar(100),
		[CRUSRID] [varchar](100) NOT NULL,
		[CREATDT] [datetime] NOT NULL,
		[MDFUSRID] [varchar](100) NOT NULL,
		[MODIFDT] [datetime] NOT NULL,
		PRIMARY KEY (OrderID,detail_id)
	)
END
GO

IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_CORE_POST_TRANSACTION')
DROP PROC FAMIAPPS_EY_CORE_POST_TRANSACTION
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_DELETE_ALL_TEMP_TABLE')
DROP PROC FAMIAPPS_EY_DELETE_ALL_TEMP_TABLE
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_SAVE_HEADER_TRANSACTION_TEMP')
DROP PROC FAMIAPPS_EY_SAVE_HEADER_TRANSACTION_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_SAVE_DETAIL_TRANSACTION_TEMP')
DROP PROC FAMIAPPS_EY_SAVE_DETAIL_TRANSACTION_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_SAVE_DETAIL_ADDON_TRANSACTION_TEMP')
DROP PROC FAMIAPPS_EY_SAVE_DETAIL_ADDON_TRANSACTION_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_SAVE_PROMO_HAPPEN_TEMP')
DROP PROC FAMIAPPS_EY_SAVE_PROMO_HAPPEN_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_SAVE_ORDER_TEMP_TO_HIST')
DROP PROC FAMIAPPS_EY_SAVE_ORDER_TEMP_TO_HIST
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_EXECUTE_AUTOMATIC_TRANSACTION')
DROP PROC FAMIAPPS_EY_EXECUTE_AUTOMATIC_TRANSACTION
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_UPDATE_RECEIPTNO_SUBMIT')
DROP PROC FAMIAPPS_UPDATE_RECEIPTNO_SUBMIT
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_DISPLAY_ORDER_UNSUBMIT_RECEIPTNO')
DROP PROC FAMIAPPS_DISPLAY_ORDER_UNSUBMIT_RECEIPTNO
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_DISPLAY_RETURN_JSON_TO_BE_SEND')
DROP PROC FAMIAPPS_EY_DISPLAY_RETURN_JSON_TO_BE_SEND
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_UPDATE_RETURN_JSON_SENT_STATUS')
DROP PROC FAMIAPPS_EY_UPDATE_RETURN_JSON_SENT_STATUS
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_SAVE_ORDER_TEMP_TO_HIST_S2')
DROP PROC FAMIAPPS_EY_SAVE_ORDER_TEMP_TO_HIST_S2
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_GENERATE_ORDER_KITCHEN_JSON_S2')
DROP PROC FAMIAPPS_GENERATE_ORDER_KITCHEN_JSON_S2
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_UPDATE_ORDER_KITCHEN_JSON_S2')
DROP PROC FAMIAPPS_UPDATE_ORDER_KITCHEN_JSON_S2
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_DISPLAY_ORDER_TO_GENERATE_KITCHEN_JSON_S2')
DROP PROC FAMIAPPS_DISPLAY_ORDER_TO_GENERATE_KITCHEN_JSON_S2
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_DISPLAY_REQUEST_PICKUP_DELIVERY_ORDER_S2')
DROP PROC FAMIAPPS_DISPLAY_REQUEST_PICKUP_DELIVERY_ORDER_S2
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_UPDATE_REQUEST_PICKUP_DELIVERY_ORDER_S2')
DROP PROC FAMIAPPS_UPDATE_REQUEST_PICKUP_DELIVERY_ORDER_S2
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_DISPLAY_REQUEST_TO_KITCHEN_ORDER_S2')
DROP PROC FAMIAPPS_DISPLAY_REQUEST_TO_KITCHEN_ORDER_S2
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_UPDATE_ORDER_SEND_TO_KITCHEN_S2')
DROP PROC FAMIAPPS_UPDATE_ORDER_SEND_TO_KITCHEN_S2
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_DISPLAY_AUTO_RECEIVED_ORDER')
DROP PROC FAMIAPPS_DISPLAY_AUTO_RECEIVED_ORDER
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_SAVE_FULFILLMENT_TEMP')
DROP PROC FAMIAPPS_EY_SAVE_FULFILLMENT_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_DISPLAY_SUPPLY_JSON_TO_BE_SEND_S2')
DROP PROC FAMIAPPS_EY_DISPLAY_SUPPLY_JSON_TO_BE_SEND_S2
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_UPDATE_SUPPLY_JSON_SENT_STATUS_S2')
DROP PROC FAMIAPPS_EY_UPDATE_SUPPLY_JSON_SENT_STATUS_S2
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_SAVE_SUPPLIES_TEMP')
DROP PROC FAMIAPPS_EY_SAVE_SUPPLIES_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_ADD_SUPPLIES_TO_SALES_TRANSACTION')
DROP PROC FAMIAPPS_EY_ADD_SUPPLIES_TO_SALES_TRANSACTION
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_EY_SAVE_SUBSTITUTION_TEMP')
DROP PROC FAMIAPPS_EY_SAVE_SUBSTITUTION_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'FAMIAPPS_SAVE_KITCHEN_TRANSACTION')
DROP PROC FAMIAPPS_SAVE_KITCHEN_TRANSACTION
GO



CREATE PROC FAMIAPPS_DISPLAY_ORDER_UNSUBMIT_RECEIPTNO
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		SELECT OrderID,POSReceiptNo,RCPOSID,RCPOSMSG,Businessday,POSID,SHIFTID,MotherStoreCode 
		FROM famiorderHH WHERE RCUPDTRCPT<>'200'
		AND POSReceiptNo <> ''
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_UPDATE_RECEIPTNO_SUBMIT
@OrderID varchar(100),
@RCUPDTRCPT VARCHAR(100),
@RMUPDTRCPT VARCHAR(200),
@DTUPDTRCPT DATETIME
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		UPDATE famiorderHH SET RCUPDTRCPT = @RCUPDTRCPT, 
		RMUPDTRCPT = @RMUPDTRCPT,DTUPDTRCPT = @DTUPDTRCPT
		WHERE OrderID = @OrderID
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_EY_SAVE_ORDER_TEMP_TO_HIST
@SESSIONID varchar(100)
as
set nocount on
BEGIN TRY
	DECLARE @OrderID varchar(100)
	DECLARE CSR CURSOR FOR SELECT OrderID FROM famiorderHT WHERE SESSIONID = @SESSIONID
	OPEN CSR
	FETCH NEXT FROM CSR INTO @OrderID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			BEGIN TRAN
				IF EXISTS (SELECT '' FROM famiorderHH WHERE OrderID = @OrderID)
				BEGIN
					DELETE FROM famiorderDH WHERE OrderID = @OrderID
					INSERT INTO famiorderDH(OrderID,LNITMSEQ,ItemCode,Barcode,ItemDescriptionLong,ItemDescriptionShort,
					ItemType,MerchCat,TaxCode,CategoryID,UnitPrice,QTY,QTYPROMO,DISC,SUBTOTAL,DPP,TAX,
					VIPID,TOTAL,TOTALPROMO,ADDITIONAL,TOPPING,TOPINGPRICE,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,NOTE)
					SELECT OrderID,LNITMSEQ,ItemCode,Barcode,ItemDescriptionLong,ItemDescriptionShort,
					ItemType,MerchCat,TaxCode,CategoryID,UnitPrice,QTY,QTYPROMO,DISC,SUBTOTAL,DPP,TAX,
					VIPID,TOTAL,TOTALPROMO,ADDITIONAL,TOPPING,TOPINGPRICE,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,NOTE FROM famiorderDT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					DELETE FROM famiorderAddonDH WHERE OrderID = @OrderID
					INSERT INTO famiorderAddonDH(OrderID,LNITMSEQ,ORD,ItemCode,
					Barcode,ItemDescriptionLong,ItemDescriptionShort,ItemType,MerchCat,TaxCode,UnitPrice,QTY,DISC,DPP,TAX,TOTAL,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT OrderID,LNITMSEQ,ORD,ItemCode,
					Barcode,ItemDescriptionLong,ItemDescriptionShort,ItemType,MerchCat,TaxCode,UnitPrice,QTY,DISC,DPP,TAX,TOTAL,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT FROM famiorderAddonDT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
				
					DELETE FROM famiorderPH WHERE OrderID = @OrderID
					INSERT INTO famiorderPH(OrderID,StoreCode,PROMOCODE,LIMITQTY,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT OrderID,StoreCode,PROMOCODE,LIMITQTY,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT FROM famiorderPT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					DELETE FROM famiorderFH WHERE OrderID = @OrderID
					INSERT INTO famiorderFH(OrderID,S1,S1DT,S2,S2DT,QueueNo,POSReceiptNo,CashierID,RPCK_STS,
					RPCK_DT,call_cso,call_csoDT,finish_cso,finishcsoDT,RTN_STS,RTN_JSON,RFN_STS,RFN_JSON,isFullReturn,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,
					S1RC,S1RM,S1RD,S2RC,S2RM,S2RD,RTN_AMT,SUPLY_JSON,KITCHEN_JSON,Businessday,POSID,SHIFTID,MotherStoreCode,JSONRQPICKUP,ISGROCERY)
					SELECT OrderID,S1,S1DT,S2,S2DT,QueueNo,POSReceiptNo,CashierID,RPCK_STS,
					RPCK_DT,call_cso,call_csoDT,finish_cso,finishcsoDT,RTN_STS,RTN_JSON,RFN_STS,RFN_JSON,isFullReturn,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,
					S1RC,S1RM,S1RD,S2RC,S2RM,S2RD,RTN_AMT,SUPLY_JSON,KITCHEN_JSON,Businessday,POSID,SHIFTID,MotherStoreCode,JSONRQPICKUP,ISGROCERY
					FROM famiorderFT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					DELETE FROM famiordersubsH WHERE OrderID = @OrderID
					INSERT INTO famiordersubsH(OrderID,old_article,old_qty,old_price,old_barcode,
					new_article,new_qty,new_price,new_barcode,trx_date,NOTE,detail_id,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT OrderID,old_article,old_qty,old_price,old_barcode,
					new_article,new_qty,new_price,new_barcode,trx_date,NOTE,detail_id,CRUSRID,CREATDT,MDFUSRID,MODIFDT
					FROM famiordersubsT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					--ADDING SUPPLIES
					IF EXISTS (SELECT '' FROM famiorderSuppliesT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID)
					BEGIN 
						IF NOT EXISTS (SELECT '' FROM famiorderSuppliesHH WHERE OrderID = @OrderID)
						BEGIN
							DELETE FROM famiorderSuppliesDH WHERE OrderID = @OrderID
							INSERT INTO famiorderSuppliesDH(OrderID,Barcode,Qty,IsSync,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
							SELECT OrderID,Barcode,Qty,0,CRUSRID,CREATDT,MDFUSRID,MODIFDT
							FROM famiorderSuppliesT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

							INSERT INTO famiorderSuppliesHH(OrderID,IsConfirmed,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
							VALUES (@OrderID,0,'SYSTEMS',GETDATE(),'','1900-01-01')
						END
					END

					--Update basic data : Order Status, Shipping Reference, Shipping Tracking, Rate, Review, Account Name
					UPDATE FamiorderHH SET OrderSts = famiorderHT.OrderSts,
					ShippingReferenceNo = famiorderHT.ShippingReferenceNo,
					ShippingTracking = famiorderHT.ShippingTracking,
					Rate = famiorderHT.Rate,
					Review = famiorderHT.Review,
					account_name = famiorderHT.account_name,
					TenderType = famiorderHT.TenderType
					FROM famiorderHT WHERE SESSIONID = @SESSIONID AND FamiorderHH.OrderID = @OrderID
					AND FamiorderHH.OrderID = famiorderHT.OrderID

					--Update dari S2 : Queue, CallCSO, CallCSODT
					UPDATE FamiorderHH SET 
					QUEUENO = famiorderFT.QueueNo,
					IsCALLCSO = famiorderFT.call_cso,
					CALLCSODT = famiorderFT.call_csoDT
					FROM famiorderFT WHERE SESSIONID = @SESSIONID AND FamiorderHH.OrderID = @OrderID
					AND FamiorderHH.OrderID = famiorderFT.OrderID

					UPDATE famiorderHT SET RCPOSID = '200',RCPOSMSG = 'SUCCESS', RCPOSDT = GETDATE() WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
				END
				ELSE
				BEGIN
					DECLARE @ISGROCERY AS INT = 0
					IF EXISTS (
					SELECT Barcode FROM famiorderDT WHERE 
					Barcode NOT IN (SELECT Barcode FROM RETAIL_MASTER.dbo.MItemKitchenModule)
					AND SESSIONID = @SESSIONID AND OrderID = @OrderID)
					BEGIN
						SET @ISGROCERY = 1
					END


					INSERT INTO famiorderHH(OrderID,Phone,account_name,StoreCode,OrderType,PickUpTime,
					Addr_Label,Addr_Desc,Addr_Map,Addr_phone_address,Addr_note_address,Addr_receiver,Addr_longitude,Addr_latitude,
					TenderID,TenderType,TenderReferenceNo,
					ShippingPrice,ShippingDisc,ShippingProvider,ShippingReferenceNo,ShippingTracking,Shipping_reference_no_tarif,ShippingCourier,ShippingDesc,
					TOTAL,DPP,TAX,Total_Tendered,ServicesFee,ServicesFeeFMI,ServicesFeeFMIDisc,Cashback_used,Cashback_borned,
					CouponShippingDiscID,CouponSalesDiscID,CouponDiscAmt,ReferenceNo,Note,Rate,Review,OrderSts,ORDERDT,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,
					RCPOSID,RCPOSMSG,RCPOSDT,POSReceiptNo,MotherStoreCode,Businessday,POSID,SHIFTID,
					RCUPDTRCPT,RMUPDTRCPT,DTUPDTRCPT,
					RCUPDTRTN,RMUPDTRTN,DTUPDTRTN,RTNJSON,ISRTNFULL,
					RCPOSIDS2,RCPOSMSGS2,RCPOSDTS2,RCKTCS2,RCKTCMSGS2,RCKTCDTS2,KTCJSONS2,QUEUENO,S2NOTIF,
					RCRQPICKUP,RMRQPICKUP,DTRQPICKUP,JSONRQPICKUP,
					isCALLCSO,CALLCSODT,RCCALLCSO,RMCALLCSO,ISGROCERY,CouponSukaSukaID,CouponSukaSukaValue,
					RCSUPPLY,RMSUPPLY,RDSUPPLY,ISSYNCSUPPLY,ISSYNCDTSUPPLY,RTN_AMT)
					SELECT OrderID,Phone,account_name,StoreCode,OrderType,PickUpTime,
					Addr_Label,Addr_Desc,Addr_Map,Addr_phone_address,Addr_note_address,Addr_receiver,Addr_longitude,Addr_latitude,
					TenderID,TenderType,TenderReferenceNo,
					ShippingPrice,ShippingDisc,ShippingProvider,ShippingReferenceNo,ShippingTracking,Shipping_reference_no_tarif,ShippingCourier,ShippingDesc,
					TOTAL,DPP,TAX,Total_Tendered,ServicesFee,ServicesFeeFMI,ServicesFeeFMIDisc,Cashback_used,Cashback_borned,
					CouponShippingDiscID,CouponSalesDiscID,CouponDiscAmt,ReferenceNo,Note,Rate,Review,OrderSts,ORDERDT,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,
					RCPOSID,RCPOSMSG,RCPOSDT,POSReceiptNo,MotherStoreCode,Businessday,POSID,SHIFTID,
					'','','1900-01-01',
					'','','1900-01-01','',0,
					'','','1900-01-01','','','1900-01-01','','',0,
					'','','1900-01-01','',
					'0','1900-01-01','','',@ISGROCERY,CouponSukaSukaID,CouponSukaSukaValue,
					0,'','1900-01-01',0,'1900-01-01',0
					FROM famiorderHT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
				
					INSERT INTO famiorderDH(OrderID,LNITMSEQ,ItemCode,Barcode,ItemDescriptionLong,ItemDescriptionShort,
					ItemType,MerchCat,TaxCode,CategoryID,UnitPrice,QTY,QTYPROMO,DISC,SUBTOTAL,DPP,TAX,
					VIPID,TOTAL,TOTALPROMO,ADDITIONAL,TOPPING,TOPINGPRICE,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,NOTE)
					SELECT OrderID,LNITMSEQ,ItemCode,Barcode,ItemDescriptionLong,ItemDescriptionShort,
					ItemType,MerchCat,TaxCode,CategoryID,UnitPrice,QTY,QTYPROMO,DISC,SUBTOTAL,DPP,TAX,
					VIPID,TOTAL,TOTALPROMO,ADDITIONAL,TOPPING,TOPINGPRICE,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,NOTE FROM famiorderDT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					INSERT INTO famiorderAddonDH(OrderID,LNITMSEQ,ORD,ItemCode,
					Barcode,ItemDescriptionLong,ItemDescriptionShort,ItemType,MerchCat,TaxCode,UnitPrice,QTY,DISC,DPP,TAX,TOTAL,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT OrderID,LNITMSEQ,ORD,ItemCode,
					Barcode,ItemDescriptionLong,ItemDescriptionShort,ItemType,MerchCat,TaxCode,UnitPrice,QTY,DISC,DPP,TAX,TOTAL,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT FROM famiorderAddonDT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
				
					INSERT INTO famiorderPH(OrderID,StoreCode,PROMOCODE,LIMITQTY,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT OrderID,StoreCode,PROMOCODE,LIMITQTY,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT FROM famiorderPT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					INSERT INTO famiorderFH(OrderID,S1,S1DT,S2,S2DT,QueueNo,POSReceiptNo,CashierID,RPCK_STS,
					RPCK_DT,call_cso,call_csoDT,finish_cso,finishcsoDT,RTN_STS,RTN_JSON,RFN_STS,RFN_JSON,isFullReturn,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,
					S1RC,S1RM,S1RD,S2RC,S2RM,S2RD,RTN_AMT,SUPLY_JSON,KITCHEN_JSON,Businessday,POSID,SHIFTID,MotherStoreCode,JSONRQPICKUP,ISGROCERY)
					SELECT OrderID,S1,S1DT,S2,S2DT,QueueNo,POSReceiptNo,CashierID,RPCK_STS,
					RPCK_DT,call_cso,call_csoDT,finish_cso,finishcsoDT,RTN_STS,RTN_JSON,RFN_STS,RFN_JSON,isFullReturn,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,
					S1RC,S1RM,S1RD,S2RC,S2RM,S2RD,RTN_AMT,SUPLY_JSON,KITCHEN_JSON,Businessday,POSID,SHIFTID,MotherStoreCode,JSONRQPICKUP,ISGROCERY
					FROM famiorderFT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
			
					INSERT INTO famiordersubsH(OrderID,old_article,old_qty,old_price,old_barcode,
					new_article,new_qty,new_price,new_barcode,trx_date,NOTE,detail_id,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT OrderID,old_article,old_qty,old_price,old_barcode,
					new_article,new_qty,new_price,new_barcode,trx_date,NOTE,detail_id,CRUSRID,CREATDT,MDFUSRID,MODIFDT
					FROM famiordersubsT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					--Start Recovery if needed
					--Update ReceiptNo
					UPDATE famiorderHH SET POSReceiptNo = famiorderFH.POSReceiptNo,
					RCPOSID = '200', RCPOSMSG = 'SUCCESS', RCPOSDT = famiorderFH.S1DT,
					MotherStoreCode = '0000007777',RCUPDTRCPT = '200',RMUPDTRCPT = 'SUCCESS',
					DTUPDTRCPT = famiorderFH.S1DT
					FROM famiorderFH WHERE famiorderHH.OrderID = famiorderFH.OrderID
					AND ISNULL(famiorderHH.POSReceiptNo,'') = '' AND ISNULL(famiorderFH.POSReceiptNo,'') <> ''
					--Update ReturnJSON
					UPDATE famiorderHH SET RTNJSON = famiorderFH.RTN_JSON, 
					RTN_AMT = famiorderFH.RTN_AMT,
					RCUPDTRTN = '200', RMUPDTRTN = 'SUCCESS',DTUPDTRTN = famiorderFH.S1DT,
					ISRTNFULL = famiorderFH.isFullReturn
					FROM famiorderFH WHERE famiorderHH.OrderID = famiorderFH.OrderID
					AND ISNULL(famiorderHH.RTNJSON,'') = '' AND ISNULL(famiorderFH.RTN_JSON,'') <> ''
					--End Recovery if needed

					--Update dari S2 : Queue, CallCSO, CallCSODT
					UPDATE FamiorderHH SET 
					QUEUENO = famiorderFT.QueueNo,
					IsCALLCSO = famiorderFT.call_cso,
					CALLCSODT = famiorderFT.call_csoDT
					FROM famiorderFT WHERE SESSIONID = @SESSIONID AND FamiorderHH.OrderID = @OrderID
					AND FamiorderHH.OrderID = famiorderFT.OrderID

					UPDATE famiorderHT SET RCPOSID = '200',RCPOSMSG = 'SUCCESS', RCPOSDT = GETDATE() WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
				END
			COMMIT
		END TRY
		BEGIN CATCH
			IF @@trancount  > 0
			BEGIN
				ROLLBACK
			END 
			DECLARE @ERROR AS VARCHAR(MAX)=''
			SET @ERROR = ERROR_MESSAGE()
			UPDATE famiorderHT SET RCPOSID = '400',RCPOSMSG = @ERROR, RCPOSDT = GETDATE() WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
		END CATCH	
		FETCH NEXT FROM CSR INTO @OrderID
	END
	CLOSE CSR
	DEALLOCATE CSR

	SELECT OrderID,RCPOSID,RCPOSMSG,RCPOSDT FROM famiorderHT WHERE SESSIONID = @SESSIONID
END TRY
BEGIN CATCH
	IF @@trancount  > 0
	BEGIN
		ROLLBACK
	END
	IF CURSOR_STATUS('global','CSR')>=-1
	BEGIN
		CLOSE CSR
		DEALLOCATE CSR
	END
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_EY_SAVE_ORDER_TEMP_TO_HIST_S2
@SESSIONID varchar(100)
as
set nocount on
BEGIN TRY
	DECLARE @OrderID varchar(100)
	DECLARE CSR CURSOR FOR SELECT OrderID FROM famiorderHT WHERE SESSIONID = @SESSIONID
	OPEN CSR
	FETCH NEXT FROM CSR INTO @OrderID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			BEGIN TRAN
				IF EXISTS (SELECT '' FROM famiorderS2HH WHERE OrderID = @OrderID)
				BEGIN
					
					DELETE FROM famiorderS2DH WHERE OrderID = @OrderID
					INSERT INTO famiorderS2DH(OrderID,LNITMSEQ,ItemCode,Barcode,ItemDescriptionLong,ItemDescriptionShort,
					ItemType,MerchCat,TaxCode,CategoryID,UnitPrice,QTY,QTYPROMO,DISC,SUBTOTAL,DPP,TAX,
					VIPID,TOTAL,TOTALPROMO,ADDITIONAL,TOPPING,TOPINGPRICE,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,NOTE)
					SELECT OrderID,LNITMSEQ,ItemCode,Barcode,ItemDescriptionLong,ItemDescriptionShort,
					ItemType,MerchCat,TaxCode,CategoryID,UnitPrice,QTY,QTYPROMO,DISC,SUBTOTAL,DPP,TAX,
					VIPID,TOTAL,TOTALPROMO,ADDITIONAL,TOPPING,TOPINGPRICE,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,NOTE FROM famiorderDT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					DELETE FROM famiorderAddonS2DH WHERE OrderID = @OrderID
					INSERT INTO famiorderAddonS2DH(OrderID,LNITMSEQ,ORD,ItemCode,
					Barcode,ItemDescriptionLong,ItemDescriptionShort,ItemType,MerchCat,TaxCode,UnitPrice,QTY,DISC,DPP,TAX,TOTAL,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT OrderID,LNITMSEQ,ORD,ItemCode,
					Barcode,ItemDescriptionLong,ItemDescriptionShort,ItemType,MerchCat,TaxCode,UnitPrice,QTY,DISC,DPP,TAX,TOTAL,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT FROM famiorderAddonDT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
				
					DELETE FROM famiorderS2PH WHERE OrderID = @OrderID
					INSERT INTO famiorderS2PH(OrderID,StoreCode,PROMOCODE,LIMITQTY,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT OrderID,StoreCode,PROMOCODE,LIMITQTY,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT FROM famiorderPT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					DELETE FROM famiorderS2FH WHERE OrderID = @OrderID
					INSERT INTO famiorderS2FH(OrderID,S1,S1DT,S2,S2DT,QueueNo,POSReceiptNo,CashierID,RPCK_STS,
					RPCK_DT,call_cso,call_csoDT,finish_cso,finishcsoDT,RTN_STS,RTN_JSON,RFN_STS,RFN_JSON,isFullReturn,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,
					S1RC,S1RM,S1RD,S2RC,S2RM,S2RD,RTN_AMT,SUPLY_JSON,KITCHEN_JSON,Businessday,POSID,SHIFTID,MotherStoreCode,JSONRQPICKUP,ISGROCERY)
					SELECT OrderID,S1,S1DT,S2,S2DT,QueueNo,POSReceiptNo,CashierID,RPCK_STS,
					RPCK_DT,call_cso,call_csoDT,finish_cso,finishcsoDT,RTN_STS,RTN_JSON,RFN_STS,RFN_JSON,isFullReturn,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,
					S1RC,S1RM,S1RD,S2RC,S2RM,S2RD,RTN_AMT,SUPLY_JSON,KITCHEN_JSON,Businessday,POSID,SHIFTID,MotherStoreCode,JSONRQPICKUP,ISGROCERY
					FROM famiorderFT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					DELETE FROM famiorderS2subsH WHERE OrderID = @OrderID
					INSERT INTO famiorderS2subsH(OrderID,old_article,old_qty,old_price,old_barcode,
					new_article,new_qty,new_price,new_barcode,trx_date,NOTE,detail_id,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT OrderID,old_article,old_qty,old_price,old_barcode,
					new_article,new_qty,new_price,new_barcode,trx_date,NOTE,detail_id,CRUSRID,CREATDT,MDFUSRID,MODIFDT
					FROM famiordersubsT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					--Update basic data : Order Status, Shipping Reference, Shipping Tracking, Rate, Review
					UPDATE famiorderS2HH SET OrderSts = famiorderHT.OrderSts,
					ShippingReferenceNo = famiorderHT.ShippingReferenceNo,
					ShippingTracking = famiorderHT.ShippingTracking,
					Rate = famiorderHT.Rate,
					Review = famiorderHT.Review,
					account_name = famiorderHT.account_name
					FROM famiorderHT WHERE SESSIONID = @SESSIONID AND famiorderS2HH.OrderID = @OrderID
					AND famiorderS2HH.OrderID = famiorderHT.OrderID

					--Update dari S1 : ReceiptNo, IsReturnedFull, CALL_CSO
					UPDATE famiorderS2HH SET 
					POSReceiptNo = famiorderFT.POSReceiptNo,
					ISRTNFULL = famiorderFT.isFullReturn,
					RTNJSON = famiorderFT.RTN_JSON,
					RTN_AMT = famiorderFT.RTN_AMT,
					IsCALLCSO = famiorderFT.call_cso,
					CALLCSODT = famiorderFT.call_csoDT
					FROM famiorderFT WHERE SESSIONID = @SESSIONID AND famiorderS2HH.OrderID = @OrderID
					AND famiorderS2HH.OrderID = famiorderFT.OrderID

					UPDATE famiorderHT SET RCPOSID = '200',RCPOSMSG = 'SUCCESS', RCPOSDT = GETDATE() WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
				END
				ELSE
				BEGIN
					DECLARE @ISGROCERY AS INT = 0
					IF EXISTS (
					SELECT Barcode FROM famiorderDT WHERE 
					Barcode NOT IN (SELECT Barcode FROM RETAIL_MASTER.dbo.MItemKitchenModule)
					AND SESSIONID = @SESSIONID AND OrderID = @OrderID)
					BEGIN
						SET @ISGROCERY = 1
					END

					INSERT INTO famiorderS2HH(OrderID,Phone,account_name,StoreCode,OrderType,PickUpTime,
					Addr_Label,Addr_Desc,Addr_Map,Addr_phone_address,Addr_note_address,Addr_receiver,Addr_longitude,Addr_latitude,
					TenderID,TenderType,TenderReferenceNo,
					ShippingPrice,ShippingDisc,ShippingProvider,ShippingReferenceNo,ShippingTracking,Shipping_reference_no_tarif,ShippingCourier,ShippingDesc,
					TOTAL,DPP,TAX,Total_Tendered,ServicesFee,ServicesFeeFMI,ServicesFeeFMIDisc,Cashback_used,Cashback_borned,
					CouponShippingDiscID,CouponSalesDiscID,CouponDiscAmt,ReferenceNo,Note,Rate,Review,OrderSts,ORDERDT,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,
					RCPOSID,RCPOSMSG,RCPOSDT,POSReceiptNo,MotherStoreCode,Businessday,POSID,SHIFTID,
					RCUPDTRCPT,RMUPDTRCPT,DTUPDTRCPT,
					RCUPDTRTN,RMUPDTRTN,DTUPDTRTN,RTNJSON,ISRTNFULL,
					RCPOSIDS2,RCPOSMSGS2,RCPOSDTS2,RCKTCS2,RCKTCMSGS2,RCKTCDTS2,KTCJSONS2,QUEUENO,S2NOTIF,
					RCRQPICKUP,RMRQPICKUP,DTRQPICKUP,JSONRQPICKUP,
					isCALLCSO,CALLCSODT,RCCALLCSO,RMCALLCSO,ISGROCERY,CouponSukaSukaID,CouponSukaSukaValue,
					RCSUPPLY,RMSUPPLY,RDSUPPLY,ISSYNCSUPPLY,ISSYNCDTSUPPLY,RTN_AMT)
					SELECT OrderID,Phone,account_name,StoreCode,OrderType,PickUpTime,
					Addr_Label,Addr_Desc,Addr_Map,Addr_phone_address,Addr_note_address,Addr_receiver,Addr_longitude,Addr_latitude,
					TenderID,TenderType,TenderReferenceNo,
					ShippingPrice,ShippingDisc,ShippingProvider,ShippingReferenceNo,ShippingTracking,Shipping_reference_no_tarif,ShippingCourier,ShippingDesc,
					TOTAL,DPP,TAX,Total_Tendered,ServicesFee,ServicesFeeFMI,ServicesFeeFMIDisc,Cashback_used,Cashback_borned,
					CouponShippingDiscID,CouponSalesDiscID,CouponDiscAmt,ReferenceNo,Note,Rate,Review,OrderSts,ORDERDT,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,
					RCPOSID,RCPOSMSG,RCPOSDT,POSReceiptNo,MotherStoreCode,Businessday,POSID,SHIFTID,
					'','','1900-01-01',
					'','','1900-01-01','',0,
					'','','1900-01-01','','','1900-01-01','','',0,
					'','','1900-01-01','',
					'0','1900-01-01','','',@ISGROCERY,CouponSukaSukaID,CouponSukaSukaValue,
					0,'','1900-01-01',0,'1900-01-01',0
					FROM famiorderHT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
				
					INSERT INTO famiorderS2DH(OrderID,LNITMSEQ,ItemCode,Barcode,ItemDescriptionLong,ItemDescriptionShort,
					ItemType,MerchCat,TaxCode,CategoryID,UnitPrice,QTY,QTYPROMO,DISC,SUBTOTAL,DPP,TAX,
					VIPID,TOTAL,TOTALPROMO,ADDITIONAL,TOPPING,TOPINGPRICE,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,NOTE)
					SELECT OrderID,LNITMSEQ,ItemCode,Barcode,ItemDescriptionLong,ItemDescriptionShort,
					ItemType,MerchCat,TaxCode,CategoryID,UnitPrice,QTY,QTYPROMO,DISC,SUBTOTAL,DPP,TAX,
					VIPID,TOTAL,TOTALPROMO,ADDITIONAL,TOPPING,TOPINGPRICE,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,NOTE FROM famiorderDT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					INSERT INTO famiorderAddonS2DH(OrderID,LNITMSEQ,ORD,ItemCode,
					Barcode,ItemDescriptionLong,ItemDescriptionShort,ItemType,MerchCat,TaxCode,UnitPrice,QTY,DISC,DPP,TAX,TOTAL,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT OrderID,LNITMSEQ,ORD,ItemCode,
					Barcode,ItemDescriptionLong,ItemDescriptionShort,ItemType,MerchCat,TaxCode,UnitPrice,QTY,DISC,DPP,TAX,TOTAL,PROMOCODE,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT FROM famiorderAddonDT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
				
					INSERT INTO famiorderS2PH(OrderID,StoreCode,PROMOCODE,LIMITQTY,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT OrderID,StoreCode,PROMOCODE,LIMITQTY,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT FROM famiorderPT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					INSERT INTO famiorderS2FH(OrderID,S1,S1DT,S2,S2DT,QueueNo,POSReceiptNo,CashierID,RPCK_STS,
					RPCK_DT,call_cso,call_csoDT,finish_cso,finishcsoDT,RTN_STS,RTN_JSON,RFN_STS,RFN_JSON,isFullReturn,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,
					S1RC,S1RM,S1RD,S2RC,S2RM,S2RD,RTN_AMT,SUPLY_JSON,KITCHEN_JSON,Businessday,POSID,SHIFTID,MotherStoreCode,JSONRQPICKUP,ISGROCERY)
					SELECT OrderID,S1,S1DT,S2,S2DT,QueueNo,POSReceiptNo,CashierID,RPCK_STS,
					RPCK_DT,call_cso,call_csoDT,finish_cso,finishcsoDT,RTN_STS,RTN_JSON,RFN_STS,RFN_JSON,isFullReturn,
					CRUSRID,CREATDT,MDFUSRID,MODIFDT,
					S1RC,S1RM,S1RD,S2RC,S2RM,S2RD,RTN_AMT,SUPLY_JSON,KITCHEN_JSON,Businessday,POSID,SHIFTID,MotherStoreCode,JSONRQPICKUP,ISGROCERY
					FROM famiorderFT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID

					INSERT INTO famiorderS2subsH(OrderID,old_article,old_qty,old_price,old_barcode,
					new_article,new_qty,new_price,new_barcode,trx_date,NOTE,detail_id,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT OrderID,old_article,old_qty,old_price,old_barcode,
					new_article,new_qty,new_price,new_barcode,trx_date,NOTE,detail_id,CRUSRID,CREATDT,MDFUSRID,MODIFDT
					FROM famiordersubsT WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
					
					--Start Recovery if needed
					UPDATE famiorderS2HH SET QUEUENO = famiorderS2FH.QueueNo,
					RCKTCS2 = '200', RCKTCMSGS2 = 'SUCCESS', RCKTCDTS2 = famiorderS2FH.S2DT
					FROM famiorderS2FH WHERE famiorderS2HH.OrderID = famiorderS2FH.OrderID
					AND ISNULL(famiorderS2HH.QUEUENO,'') = '' AND ISNULL(famiorderS2FH.QueueNo,'') <> ''
					--End Recovery if needed
					
					--Update dari S1 : ReceiptNo, IsReturnedFull, CALL_CSO
					UPDATE famiorderS2HH SET 
					POSReceiptNo = famiorderFT.POSReceiptNo,
					ISRTNFULL = famiorderFT.isFullReturn,
					RTNJSON = famiorderFT.RTN_JSON,
					IsCALLCSO = famiorderFT.call_cso,
					CALLCSODT = famiorderFT.call_csoDT
					FROM famiorderFT WHERE SESSIONID = @SESSIONID AND famiorderS2HH.OrderID = @OrderID
					AND famiorderS2HH.OrderID = famiorderFT.OrderID

					UPDATE famiorderHT SET RCPOSID = '200',RCPOSMSG = 'SUCCESS', RCPOSDT = GETDATE() WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
					
					INSERT INTO famiordernotif(POSID,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
					SELECT DISTINCT POSID,@SESSIONID,GETDATE(),'','1900-01-01' FROM RETAIL_TRANSACTION.dbo.TrBusinessday
					WHERE POSID NOT IN (SELECT POSID FROM famiordernotif)
					AND POSID NOT IN (SELECT POSID FROM RETAIL_SYSTEMS.dbo.MSetupPOSKiosk)
				END
			COMMIT
		END TRY
		BEGIN CATCH
			IF @@trancount  > 0
			BEGIN
				ROLLBACK
			END 
			DECLARE @ERROR AS VARCHAR(MAX)=''
			SET @ERROR = ERROR_MESSAGE()
			UPDATE famiorderHT SET RCPOSID = '400',RCPOSMSG = @ERROR, RCPOSDT = GETDATE() WHERE SESSIONID = @SESSIONID AND OrderID = @OrderID
		END CATCH	
		FETCH NEXT FROM CSR INTO @OrderID
	END
	CLOSE CSR
	DEALLOCATE CSR

	SELECT OrderID,RCPOSID,RCPOSMSG,RCPOSDT FROM famiorderHT WHERE SESSIONID = @SESSIONID
END TRY
BEGIN CATCH
	IF @@trancount  > 0
	BEGIN
		ROLLBACK
	END
	IF CURSOR_STATUS('global','CSR')>=-1
	BEGIN
		CLOSE CSR
		DEALLOCATE CSR
	END
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO


CREATE PROC FAMIAPPS_EY_DELETE_ALL_TEMP_TABLE
@SESSIONID varchar(100)
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		DELETE FROM famiorderHT WHERE SESSIONID = @SESSIONID
		DELETE FROM famiorderDT WHERE SESSIONID = @SESSIONID
		DELETE FROM famiorderAddonDT WHERE SESSIONID = @SESSIONID
		DELETE FROM famiorderPT WHERE SESSIONID = @SESSIONID
		DELETE FROM famiorderFT WHERE SESSIONID = @SESSIONID
		DELETE FROM famiorderSuppliesT WHERE SESSIONID = @SESSIONID
		DELETE FROM famiorderSubsT WHERE SESSIONID = @SESSIONID
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_EY_SAVE_FULFILLMENT_TEMP
@SESSIONID varchar(100),
@OrderID varchar(100),
@S1 varchar(100),
@S1DT datetime,
@S2 varchar(100),
@S2DT datetime,
@QueueNo varchar(100),
@POSReceiptNo varchar(100),
@CashierID varchar(100),
@RPCK_STS varchar(100),
@RPCK_DT datetime,
@call_cso varchar(100),
@call_csoDT datetime,
@finish_cso varchar(100),
@finishcsoDT datetime,
@RTN_STS varchar(100),
@RTN_JSON varchar(8000),
@RFN_STS varchar(100),
@RFN_JSON varchar(8000),
@isFullReturn varchar(100), 
@SYSUSR VARCHAR(100),
@S1RC varchar(100),
@S1RM varchar(8000),
@S1RD datetime,
@S2RC varchar(100),
@S2RM varchar(8000),
@S2RD datetime,
@RTN_AMT DECIMAL(19,5),
@SUPLY_JSON VARCHAR(8000),
@KITCHEN_JSON VARCHAR(8000),
@Businessday VARCHAR(100),
@POSID VARCHAR(100),
@SHIFTID VARCHAR(100),
@MotherStoreCode VARCHAR(100),
@JSONRQPICKUP VARCHAR(8000),
@ISGROCERY VARCHAR(100)
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		INSERT INTO famiorderFT(SESSIONID,OrderID,S1,S1DT,S2,S2DT,QueueNo,POSReceiptNo,CashierID,RPCK_STS,
		RPCK_DT,call_cso,call_csoDT,finish_cso,finishcsoDT,RTN_STS,RTN_JSON,RFN_STS,RFN_JSON,isFullReturn,
		CRUSRID,CREATDT,MDFUSRID,MODIFDT,
		S1RC,S1RM,S1RD,S2RC,S2RM,S2RD,RTN_AMT,SUPLY_JSON,KITCHEN_JSON,Businessday,POSID,SHIFTID,MotherStoreCode,JSONRQPICKUP,ISGROCERY)
		VALUES(@SESSIONID,@OrderID,@S1,@S1DT,@S2,@S2DT,@QueueNo,@POSReceiptNo,@CashierID,@RPCK_STS,
		@RPCK_DT,@call_cso,@call_csoDT,@finish_cso,@finishcsoDT,@RTN_STS,@RTN_JSON,@RFN_STS,@RFN_JSON,@isFullReturn,
		@SYSUSR,GETDATE(),'','1900-01-01',
		@S1RC,@S1RM,@S1RD,@S2RC,@S2RM,@S2RD,@RTN_AMT,@SUPLY_JSON,@KITCHEN_JSON,@Businessday,@POSID,@SHIFTID,@MotherStoreCode,@JSONRQPICKUP,@ISGROCERY)
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_EY_SAVE_PROMO_HAPPEN_TEMP
@SESSIONID varchar(100),
@OrderID varchar(100),
@StoreCode varchar(100),
@PROMOCODE VARCHAR(100),
@LIMITQTY INT,
@SYSUSR VARCHAR(100)
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		INSERT INTO famiorderPT(SESSIONID,OrderID,StoreCode,PROMOCODE,LIMITQTY,
		CRUSRID,CREATDT,MDFUSRID,MODIFDT)
		VALUES(@SESSIONID,@OrderID,@StoreCode,@PROMOCODE,@LIMITQTY,
		@SYSUSR,GETDATE(),'','1900-01-01')
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO


CREATE PROC FAMIAPPS_EY_SAVE_DETAIL_ADDON_TRANSACTION_TEMP
@SESSIONID varchar(100),
@OrderID varchar(100),
@LNITMSEQ INT,
@ORD INT,
@ItemCode varchar(100),
@Barcode varchar(100),
@ItemDescriptionLong varchar(200),
@ItemDescriptionShort varchar(100),
@ItemType varchar(100),
@MerchCat varchar(100),
@TaxCode varchar(100),
@UnitPrice DECIMAL(19,5),
@QTY DECIMAL(17,5),
@DISC DECIMAL(19,5),
@DPP DECIMAL(19,5),
@TAX DECIMAL(19,5),
@TOTAL DECIMAL(19,5),
@PROMOCODE VARCHAR(100),
@SYSUSR VARCHAR(100)
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		INSERT INTO famiorderAddonDT(SESSIONID,OrderID,LNITMSEQ,ORD,ItemCode,
		Barcode,ItemDescriptionLong,ItemDescriptionShort,ItemType,MerchCat,TaxCode,UnitPrice,QTY,DISC,DPP,TAX,TOTAL,PROMOCODE,
		CRUSRID,CREATDT,MDFUSRID,MODIFDT)
		VALUES (@SESSIONID,@OrderID,@LNITMSEQ,@ORD,@ItemCode,
		@Barcode,@ItemDescriptionLong,@ItemDescriptionShort,@ItemType,@MerchCat,@TaxCode,@UnitPrice,@QTY,@DISC,@DPP,@TAX,@TOTAL,@PROMOCODE,
		@SYSUSR,GETDATE(),'','1900-01-01')
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_EY_SAVE_DETAIL_TRANSACTION_TEMP
@SESSIONID varchar(100),
@OrderID varchar(100),
@LNITMSEQ INT,
@ItemCode varchar(100),
@Barcode varchar(100),
@ItemDescriptionLong varchar(200),
@ItemDescriptionShort varchar(100),
@ItemType varchar(100),
@MerchCat varchar(100),
@TaxCode varchar(100),
@CategoryID varchar(100),
@UnitPrice DECIMAL(19,5),
@QTY DECIMAL(17,5),
@QTYPROMO DECIMAL(17,5),
@DISC DECIMAL(19,5),
@SUBTOTAL DECIMAL(19,5),
@DPP DECIMAL(19,5),
@TAX DECIMAL(19,5),
@VIPID VARCHAR(100),
@TOTAL DECIMAL(19,5),
@TOTALPROMO DECIMAL(19,5),
@ADDITIONAL VARCHAR(8000),
@TOPPING VARCHAR(8000),
@TOPINGPRICE DECIMAL(19,5),
@PROMOCODE VARCHAR(100),
@SYSUSR VARCHAR(100),
@NOTE VARCHAR(8000)
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		INSERT INTO famiorderDT(SESSIONID,OrderID,LNITMSEQ,ItemCode,Barcode,ItemDescriptionLong,ItemDescriptionShort,
		ItemType,MerchCat,TaxCode,CategoryID,UnitPrice,QTY,QTYPROMO,DISC,SUBTOTAL,DPP,TAX,
		VIPID,TOTAL,TOTALPROMO,ADDITIONAL,TOPPING,TOPINGPRICE,PROMOCODE,
		CRUSRID,CREATDT,MDFUSRID,MODIFDT,NOTE)
		VALUES (@SESSIONID,@OrderID,@LNITMSEQ,@ItemCode,@Barcode,@ItemDescriptionLong,@ItemDescriptionShort,
		@ItemType,@MerchCat,@TaxCode,@CategoryID,@UnitPrice,@QTY,@QTYPROMO,@DISC,@SUBTOTAL,@DPP,@TAX,
		@VIPID,@TOTAL,@TOTALPROMO,@ADDITIONAL,@TOPPING,@TOPINGPRICE,@PROMOCODE,
		@SYSUSR,GETDATE(),'','1900-01-01',@NOTE)
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_EY_SAVE_HEADER_TRANSACTION_TEMP
@SESSIONID varchar(100),
@OrderID varchar(100),
@Phone varchar(100),
@StoreCode varchar(100),
@OrderType varchar(100),
@PickUpTime varchar(100),
@Addr_Label varchar(100),
@Addr_Desc varchar(200),
@Addr_Map varchar(8000),
@Addr_phone_address varchar(100),
@Addr_note_address varchar(100),
@Addr_receiver varchar(100),
@Addr_longitude varchar(100),
@Addr_latitude varchar(100),
@TenderID varchar(100),
@TenderType varchar(100),
@TenderReferenceNo varchar(100),
@ShippingPrice DECIMAL(19,5),
@ShippingDisc DECIMAL(19,5),
@ShippingProvider varchar(100),
@ShippingReferenceNo varchar(100),
@ShippingTracking varchar(8000),
@Shipping_reference_no_tarif varchar(100),
@ShippingCourier varchar(8000),
@ShippingDesc varchar(200),
@TOTAL DECIMAL(19,5),
@DPP DECIMAL(19,5),
@TAX DECIMAL(19,5),
@Total_Tendered DECIMAL(19,5),
@ServicesFee DECIMAL(19,5),
@ServicesFeeFMI DECIMAL(19,5),
@ServicesFeeFMIDisc DECIMAL(19,5),
@Cashback_used  DECIMAL(19,5),
@Cashback_borned DECIMAL(19,5),
@CouponShippingDiscID VARCHAR(100),
@CouponSalesDiscID VARCHAR(100),
@CouponDiscAmt DECIMAL(19,5),
@ReferenceNo varchar(100),
@Note varchar(8000),
@Rate varchar(100),
@Review varchar(8000),
@OrderSts varchar(100),
@ORDERDT DATETIME,
@SYSUSR VARCHAR(100),
@account_name VARCHAR(200),
@CouponSukaSukaID VARCHAR(100),
@CouponSukaSukaValue DECIMAL(19,5)
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		INSERT INTO famiorderHT(SESSIONID,OrderID,Phone,account_name,StoreCode,OrderType,PickUpTime,
		Addr_Label,Addr_Desc,Addr_Map,Addr_phone_address,Addr_note_address,Addr_receiver,Addr_longitude,Addr_latitude,
		TenderID,TenderType,TenderReferenceNo,
		ShippingPrice,ShippingDisc,ShippingProvider,ShippingReferenceNo,ShippingTracking,Shipping_reference_no_tarif,ShippingCourier,ShippingDesc,
		TOTAL,DPP,TAX,Total_Tendered,ServicesFee,ServicesFeeFMI,ServicesFeeFMIDisc,Cashback_used,Cashback_borned,
		CouponShippingDiscID,CouponSalesDiscID,CouponDiscAmt,ReferenceNo,Note,Rate,Review,OrderSts,ORDERDT,
		CRUSRID,CREATDT,MDFUSRID,MODIFDT,
		RCPOSID,RCPOSMSG,RCPOSDT,POSReceiptNo,MotherStoreCode,Businessday,POSID,SHIFTID,CouponSukaSukaID,CouponSukaSukaValue)
		VALUES (@SESSIONID,@OrderID,@Phone,@account_name,@StoreCode,@OrderType,@PickUpTime,
		@Addr_Label,@Addr_Desc,@Addr_Map,@Addr_phone_address,@Addr_note_address,@Addr_receiver,@Addr_longitude,@Addr_latitude,
		@TenderID,@TenderType,@TenderReferenceNo,
		@ShippingPrice,@ShippingDisc,@ShippingProvider,@ShippingReferenceNo,@ShippingTracking,@Shipping_reference_no_tarif,@ShippingCourier,@ShippingDesc,
		@TOTAL,@DPP,@TAX,@Total_Tendered,@ServicesFee,@ServicesFeeFMI,@ServicesFeeFMIDisc,@Cashback_used,@Cashback_borned,
		@CouponShippingDiscID,@CouponSalesDiscID,@CouponDiscAmt,@ReferenceNo,@Note,@Rate,@Review,@OrderSts,@ORDERDT,
		@SYSUSR,GETDATE(),'','1900-01-01',
		'','','1900-01-01','','','','','',@CouponSukaSukaID,@CouponSukaSukaValue)
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO


CREATE PROC FAMIAPPS_EY_CORE_POST_TRANSACTION
@COMPUTERIP VARCHAR(100),
@StoreCode VARCHAR(100),
@POSID VARCHAR(100),
@SHIFTID VARCHAR(100),
@Businessday VARCHAR(100),
@order_id varchar(100),
@SYS_USR VARCHAR(100),
@LSMEMBERSHIP varchar(100),
@COMPANYID varchar(100),
@COMPANYPCID varchar(100)
as
set nocount on
BEGIN TRY
	--Check Mother StoreCode
	IF @StoreCode <> '0000007777'
		RAISERROR ('MOTHER STORE IS INVALID',16,1)

	--Check Order_ID Status Downloaded
	IF EXISTS (SELECT '' FROM famiorderHH WHERE OrderID = @order_id AND POSReceiptNo <> '')
	BEGIN
		RAISERROR ('ORDER NO WAS DOWNLOADED BEFORE',16,1)
	END
	IF EXISTS (SELECT '' FROM RETAIL_TRANSACTION.dbo.TrSalesHH WHERE ORIDOCNUMBR = @order_id)
	BEGIN
		DECLARE @LATEST_DT DATETIME,@LATEST_POSReceiptNo VARCHAR(100),@LATEST_MotherStoreCode VARCHAR(100), @LATEST_Businessday VARCHAR(100), @LATEST_POSID VARCHAR(100), @LATEST_SHIFTID VARCHAR(100)
		SELECT 
		@LATEST_DT = CREATDT,
		@LATEST_POSReceiptNo=DOCNUMBR, 
		@LATEST_MotherStoreCode = StoreCode,
		@LATEST_Businessday = BusinessDay,
		@LATEST_POSID = POSID,
		@LATEST_SHIFTID = SHIFTID
		FROM RETAIL_TRANSACTION.dbo.TrSalesHH WHERE ORIDOCNUMBR = @order_id
		UPDATE famiorderHH SET POSReceiptNo = @LATEST_POSReceiptNo, MotherStoreCode = @LATEST_MotherStoreCode, Businessday = @LATEST_Businessday, POSID = @POSID, SHIFTID = @LATEST_SHIFTID, 
		RCPOSID = '200', RCPOSMSG = 'SUCCESS',RCPOSDT = @LATEST_DT,
		MDFUSRID = @SYS_USR,MODIFDT = GETDATE()
		WHERE OrderID = @order_id
	END

	--Check POSID Online
	DECLARE @POSID_ONLINE AS VARCHAR(100) = ''
	SET @POSID_ONLINE = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'POS_ID_ONLINE')
	SET @POSID_ONLINE = ISNULL(@POSID_ONLINE,'')
	IF @POSID <> @POSID_ONLINE
	BEGIN
		RAISERROR ('POS ID ONLINE IS INVALID',16,1)
	END

	DECLARE 
	@FulfillStore varchar(100),@OrderType varchar(100),@PickUpTime VARCHAR(100),@TenderID varchar(100),@TenderType varchar(100),@TenderReferenceNo varchar(100),
	@ShippingPrice DECIMAL(19,5),@ShippingDisc DECIMAL(19,5),@ShippingProvider varchar(100),@Shipping_reference_no_tarif varchar(100),@TOTAL_APPS DECIMAL(19,5),@DPP_APPS DECIMAL(19,5),@TAX_APPS DECIMAL(19,5),
	@ServicesFee DECIMAL(19,5),@ServicesFeeFMI DECIMAL(19,5),@ServicesFeeFMIDisc DECIMAL(19,5),
	@Total_Tendered DECIMAL(19,5),
	@Cashback_used  DECIMAL(19,5),@Cashback_borned DECIMAL(19,5),@CouponShippingDiscID VARCHAR(100),@CouponSalesDiscID VARCHAR(100),@CouponDiscAmt DECIMAL(19,5),
	@OrderSts varchar(100),@ORDERDT DATETIME,@RCPOSID VARCHAR(10),
	@CouponSukaSukaID VARCHAR(100), @CouponSukaSukaValue DECIMAL(19,5)
	DECLARE CSR CURSOR FOR SELECT StoreCode,OrderType,PickUpTime,TenderID,TenderType,TenderReferenceNo,
	ShippingPrice,ShippingDisc,ShippingProvider,Shipping_reference_no_tarif,TOTAL,DPP,TAX,ServicesFee,ServicesFeeFMI,ServicesFeeFMIDisc,Total_Tendered,
	Cashback_used,Cashback_borned,CouponShippingDiscID,CouponSalesDiscID,CouponDiscAmt,
	OrderSts,ORDERDT,RCPOSID,CouponSukaSukaID,CouponSukaSukaValue FROM famiorderHH WHERE OrderID = @order_id
	OPEN CSR
	FETCH NEXT FROM CSR INTO @FulfillStore,@OrderType,@PickUpTime,@TenderID,@TenderType,@TenderReferenceNo,
	@ShippingPrice,@ShippingDisc,@ShippingProvider,@Shipping_reference_no_tarif,@TOTAL_APPS,@DPP_APPS,@TAX_APPS,@ServicesFee,@ServicesFeeFMI,@ServicesFeeFMIDisc,@Total_Tendered,
	@Cashback_used,@Cashback_borned,@CouponShippingDiscID,@CouponSalesDiscID,@CouponDiscAmt,
	@OrderSts,@ORDERDT,@RCPOSID,
	@CouponSukaSukaID,@CouponSukaSukaValue
	CLOSE CSR
	DEALLOCATE CSR

	--Check is successed
	IF ISNULL(@RCPOSID,'') <> '200'
	BEGIN
		--Check status delivery/pickup
		IF @OrderType = 'delivery'
		BEGIN
			IF @OrderSts > 3
				RAISERROR ('UNPAID',16,1)
			IF @OrderSts = 0
				RAISERROR ('ORDER CANCELED',16,1)
			IF ISNULL(@Shipping_reference_no_tarif,'') = ''
				RAISERROR ('SHIPPING REQUEST PICK UP NO IS BLANK',16,1)
		END
		ELSE IF @OrderType = 'pickup'
		BEGIN
			IF @OrderSts > 2
				RAISERROR ('UNPAID',16,1)
			IF @OrderSts = 0
				RAISERROR ('ORDER CANCELED',16,1)
			IF ISNULL(@PickUpTime,'') = ''
				RAISERROR ('PICK UP TIME IS BLANK',16,1)
		END

		DECLARE @Correct_Total as DECIMAL(19,5)=0
		SET @Correct_Total = 0
		--Check total paid vs total salesss
		SET @Correct_Total = ISNULL(@Total_Tendered,0) - 
		((ISNULL(@ShippingPrice,0) + ISNULL(@TOTAL_APPS,0) + (ISNULL(@ServicesFeeFMI,0) - ISNULL(@ServicesFeeFMIDisc,0)))
		- (ISNULL(@ShippingDisc,0) + ISNULL(@Cashback_used,0) + ISNULL(@CouponDiscAmt,0) + ISNULL(@CouponSukaSukaValue,0)))
		IF @Correct_Total <> 0 
			RAISERROR ('TOTAL PAID <> TOTAL SALES',16,1)
			
		--Check total header vs detail + toping
		SET @Correct_Total = 0
		DECLARE @Detail_Total as DECIMAL(19,5) = 0
		SET @Detail_Total = (SELECT ISNULL(SUM(ISNULL(TOTAL,0)+ISNULL(TOPINGPRICE,0)),0) FROM famiorderDH WHERE OrderID = @order_id)
		SET @Correct_Total = ISNULL(@TOTAL_APPS,0) - ISNULL(@Detail_Total,0)
		IF @Correct_Total > 10 or @Correct_Total < 0
			RAISERROR ('TOTAL HEADER <> TOTAL DETAIL',16,1)

		--Check Shipping Cost
		IF ISNULL(@ShippingPrice,0) < ISNULL(@ShippingDisc,0)
			RAISERROR ('SHIPPING PRICE < SHIPPING DISC',16,1)

		--Check Tender not NULL
		IF ISNULL(@TenderID,'') = '' OR ISNULL(@TenderType,'') = '' OR ISNULL(@TenderReferenceNo,'') = ''
			RAISERROR ('PAYMENT DATA IS NOT CORRECT',16,1)
			
		--Check Coupon Sales
		IF ISNULL(@CouponDiscAmt,0) <> 0
			IF ISNULL(@CouponSalesDiscID,'')=''
				RAISERROR ('COUPON SALES IS BLANK',16,1)
		--Check Coupon Delivery
		IF ISNULL(@ShippingDisc,0) <> 0
			IF ISNULL(@CouponShippingDiscID,'')=''
				RAISERROR ('COUPON SHIPPING IS BLANK',16,1)
		--Check Coupon Suka Suka
		IF ISNULL(@CouponSukaSukaValue,0) <> 0
			IF ISNULL(@CouponSukaSukaID,'')=''
				RAISERROR ('COUPON SUKA SUKA IS BLANK',16,1)
			
		--Check Total Toping
		DECLARE @TopingDetail DECIMAL(19,5)=0
		DECLARE @TopingExt DECIMAL(19,5)=0
		SET @TopingDetail = (SELECT ISNULL(SUM(ISNULL(TOPINGPRICE,0)),0) FROM famiorderDH WHERE OrderID = @order_id)
		SET @TopingExt = (SELECT ISNULL(SUM(ISNULL(TOTAL,0)),0) FROM famiorderAddonDH WHERE OrderID = @order_id)
		IF ISNULL(@TopingDetail,0) <> ISNULL(@TopingExt,0)
			RAISERROR ('TOTAL TOPING IS NOT MATCH',16,1)

		--Check PromoCode is Blank
		IF EXISTS (SELECT '' FROM famiorderDH WHERE OrderID = @order_id AND ISNULL(PROMOCODE,'') = '' AND ISNULL(DISC,0)<>0)
			RAISERROR ('PROMO CODE IS BLANK',16,1)

		----Check TOTAL = DPP + TAX
		--IF ISNULL(@TOTAL_APPS,0) <> ISNULL(@DPP_APPS,0) + ISNULL(@TAX_APPS,0)
		--	RAISERROR ('TOTAL, DPP, TAX IS NOT MATCH',16,1)
			
		--Check PromoCode Happen
		IF EXISTS (SELECT '' FROM famiorderDH WHERE OrderID = @order_id AND ISNULL(PROMOCODE,'') <> '' AND ISNULL(PROMOCODE,'') NOT IN
					(SELECT ISNULL(PROMOCODE,'') FROM famiorderPH WHERE OrderID = @order_id))
				RAISERROR ('PROMO HAPPEN NOT MATCH WITH SALES DETAIL',16,1)
		IF EXISTS (SELECT '' FROM famiorderPH WHERE OrderID = @order_id AND ISNULL(PROMOCODE,'') <> '' AND ISNULL(PROMOCODE,'') NOT IN
					(SELECT ISNULL(PROMOCODE,'') FROM famiorderDH WHERE OrderID = @order_id))
				RAISERROR ('PROMO HAPPEN NOT MATCH WITH SALES DETAIL',16,1)
	
		--Check Item all Exists
		IF EXISTS (SELECT '' FROM famiorderDH WHERE OrderID = @order_id AND FORMAT(CONVERT(INT,ItemCode), '000000000000000000') 
			NOT IN (SELECT ItemCode FROM RETAIL_MASTER.dbo.MItem))
		BEGIN
			DECLARE @NOT_REGISTERED_ITEM VARCHAR(8000) = (SELECT TOP 1 'ITEM IS NOT REGISTERED.' + ItemCode + '|' + ItemDescriptionLong + '|' + Barcode FROM famiorderDH WHERE OrderID = @order_id AND FORMAT(CONVERT(INT,ItemCode), '000000000000000000') 
					NOT IN (SELECT ItemCode FROM RETAIL_MASTER.dbo.MItem))
			RAISERROR (@NOT_REGISTERED_ITEM,16,1)
		END

		BEGIN TRAN
		DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesT WHERE COMPUTERIP = @COMPUTERIP
		DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesTenderT WHERE COMPUTERIP = @COMPUTERIP
		DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesTenderTT WHERE COMPUTERIP = @COMPUTERIP
		DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesAdditionalT WHERE COMPUTERIP = @COMPUTERIP
			
		--Sales Detail Processing
		DECLARE @MSG_ERR VARCHAR(8000) = ''
		DECLARE @SALES_TAX as decimal(19,5)=0
		DECLARE @order_num as int,@Barcode varchar(100), @article as varchar(100), @qty as decimal(17,5)=0, @price as decimal(19,5) = 0, @notes as varchar(500)='',@disc as decimal(19,5)=0,@promocode as varchar(100) = ''
		DECLARE CSR_DOWNLOAD CURSOR FOR 
						SELECT ROW_NUMBER() OVER(ORDER BY Barcode ASC),Barcode,ItemCode,QTY,UnitPrice,Notes,DISC,PROMOCODE
						FROM
						(
							SELECT Barcode,ItemCode,QTY,UnitPrice,
							RTRIM(ISNULL(ADDITIONAL,'') + 
							CASE 
							WHEN ISNULL(TOPPING,'') <> '' THEN 
								'|' 
							ELSE 
								'' 
							END 
							+ ISNULL(TOPPING,'') + 
							CASE 
							WHEN ISNULL(NOTE,'') <> '' THEN 
								' | ' 
							ELSE 
								'' 
							END 
							+ ISNULL(NOTE,''))
							as Notes,
							DISC,
							ISNULL(PROMOCODE,'') as PROMOCODE FROM famiorderDH WHERE OrderID = @order_id
							UNION ALL
							SELECT 'DELIVERY COST','DELIVERY COST',1,ISNULL(ShippingPrice,0),'',ISNULL(ShippingDisc,0),ISNULL(CouponShippingDiscID,'') FROM famiorderHH WHERE OrderID = @order_id
							AND ISNULL(ShippingPrice,0)>0
							UNION ALL
							SELECT Barcode,ItemCode,Qty,UnitPrice,'',Disc,ISNULL(PROMOCODE,'') as PROMOCODE FROM famiorderAddonDH WHERE OrderID = @order_id
							UNION ALL
							SELECT 'SERVICE FEE','000000000000121662',1,ISNULL(ServicesFeeFMI,0),'',ISNULL(ServicesFeeFMIDisc,0),
							CASE WHEN ISNULL(ServicesFeeFMIDisc,0) = 0 THEN '' ELSE 'SRV FEE DISC' END FROM famiorderHH WHERE OrderID = @order_id
							AND ISNULL(ServicesFeeFMI,0) > 0
						)SALES_DETAIL
		OPEN CSR_DOWNLOAD
		FETCH NEXT FROM CSR_DOWNLOAD INTO @order_num,@Barcode,@article,@qty,@price,@notes,@disc,@promocode
		WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE
				@ItemDescriptionLong varchar(500)='',
				@ItemDescriptionShort varchar(50)='',
				@ItemType varchar(20)='',
				@TaxCode varchar(20)='',
				@OpenPriceFlg bit='',
				@ItemValue as decimal(19,5)=0,
				@ReturnAble as bit=0,
				@MerchCat varchar(50)='',
				@MerchCatDesc varchar(100)='',
				@Category varchar(50)='',
				@CategoryDesc varchar(100)='',
				@Department varchar(50)='',
				@DepartmentDesc varchar(100)='',
				@Division varchar(50)='',
				@DivisionDesc varchar(100)='',
				@Tax decimal(19,5) = 0,
				@Subtotal decimal(19,5) = 0,
				@DPP decimal(19,5) = 0,
				@Total decimal(19,5) = 0
				
			SELECT TOP 1 @ItemDescriptionLong = ItemDescriptionLong,  
			@ItemDescriptionShort = ItemDescriptionShort, @ItemType = ItemType,
			@TaxCode = TaxCode, @OpenPriceFlg = OpenPriceFlg,
			@ReturnAble = ReturnAble,
			@MerchCat = MItem.MerchCat, @MerchCatDesc = MerchCatDesc,
			@Category = Category, @CategoryDesc = CategoryDesc,
			@Department = Department, @DepartmentDesc = DepartmentDesc, 
			@Division = Division, @DivisionDesc = DivisionDesc
			FROM RETAIL_MASTER.dbo.MItem 
			LEFT JOIN RETAIL_MASTER.dbo.MMerchandiseCategory ON MItem.MerchCat = MMerchandiseCategory.MerchCat
			where Barcode = @Barcode
			AND TaxCode <> ''

			SET @SALES_TAX = ISNULL((SELECT TOP 1 CONVERT(float,TaxValue)+1 FROM RETAIL_SYSTEMS.dbo.MTaxSetup WHERE TaxCode = @TaxCode AND GETDATE() BETWEEN StartDT AND EndDT ORDER BY CREATDT DESC),0)

			IF @Barcode = ''
			BEGIN
				SET @MSG_ERR = @article + 'is not registered.' + @article + ' ' + @Barcode
				RAISERROR (@MSG_ERR,16,1)
			END

			IF ISNULL(@MerchCat,'') = ''
			BEGIN
				SET @MSG_ERR = @article + 'is not registered.' + @article + ' ' + @Barcode
				RAISERROR (@MSG_ERR,16,1)
			END

			SET @Subtotal = @qty * (@price - @disc)
			SET @Total = @Subtotal
			IF @TaxCode <> 0
			BEGIN
				SET @Tax = ROUND(@Subtotal - (@Subtotal/@SALES_TAX),0)
			END
			SET @DPP = @Subtotal - @Tax

			INSERT INTO RETAIL_TRANSACTION.dbo.TrSalesT
			(StoreCode,BusinessDay,POSID,SHIFTID,COMPUTERIP,LNITMSEQ,Barcode,ItemCode,ItemDescriptionLong,ItemDescriptionShort,ItemType,TaxCode,MerchCat,MerchCatDesc,
			Category,CategoryDesc,Department,DepartmentDesc,Division,DivisionDesc,OpenPriceFlg,UnitPrice,Qty,Disc,Subtotal,DPP,Tax,Total,QtySales,SalesDisc,SalesSubtotal,SalesDPP,SalesTax,SalesTotal,QtyReturned,ReturnedDisc,ReturnedSubtotal,ReturnedDPP,ReturnedTax,ReturnedTotal,ItemValue,CRUSRID,CREATDT,MDFUSRID,MODIFDT,ReturnAble,PromoCode)
			VALUES 
			(@StoreCode,@BusinessDay,@POSID,@SHIFTID,@COMPUTERIP,@order_num,@Barcode,
			@article,@ItemDescriptionLong,@ItemDescriptionShort,@ItemType,@TaxCode,@MerchCat,@MerchCatDesc,
			@Category,@CategoryDesc,@Department,@DepartmentDesc,@Division,@DivisionDesc,@OpenPriceFlg,@price,@qty,@disc,@Subtotal,@DPP,@Tax,@Total,@qty,@disc,@Subtotal,@DPP,@Tax,@Total,0,0,0,0,0,0,@ItemValue,@SYS_USR,GETDATE(),'','1900-01-01 00:00:00.000',@ReturnAble,@promocode)

			INSERT INTO RETAIL_TRANSACTION.dbo.TrSalesAdditionalT(COMPUTERIP,LNITMSEQ,USERDEFINEID,USERDEFINEVALUE,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
			VALUES 
			(@COMPUTERIP,@order_num,@order_num,@notes,@SYS_USR,GETDATE(),'','1900-01-01 00:00:00.000')

			FETCH NEXT FROM CSR_DOWNLOAD INTO @order_num,@Barcode,@article,@qty,@Price,@notes,@disc,@promocode
		END
		CLOSE CSR_DOWNLOAD
		DEALLOCATE CSR_DOWNLOAD

		--Payment Processing
		DECLARE @paymentType varchar(100) = '',@ReferenceNo varchar(100) = '',@PaidAmount DECIMAL(19,5) = 0
		DECLARE @TotalPaidAmount DECIMAL(19,5)=0
		DECLARE CSR_DOWNLOAD_PAYMENT CURSOR FOR SELECT TenderID + '#' + TenderType as PaymentType,TenderReferenceNo,Total_Tendered FROM famiorderHH 
												WHERE OrderID = @order_id
												UNION ALL
												SELECT 'CASHBACK',phone,Cashback_used FROM famiorderHH
												WHERE OrderID = @order_id
												AND Cashback_used>0
												UNION ALL
												SELECT 'VOUCHER','COUPON DISCOUNT',CouponDiscAmt FROM famiorderHH
												WHERE OrderID = @order_id
												AND CouponDiscAmt>0
												UNION ALL
												SELECT 'VOUCHER','COUPON SUKA SUKA',CouponSukaSukaValue FROM famiorderHH
												WHERE OrderID = @order_id
												AND CouponSukaSukaValue>0
		OPEN CSR_DOWNLOAD_PAYMENT
		FETCH NEXT FROM CSR_DOWNLOAD_PAYMENT INTO @paymentType,@ReferenceNo,@PaidAmount
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @TotalPaidAmount = @TotalPaidAmount + @PaidAmount
			EXEC RETAIL_TRANSACTION.dbo.TRX_EY_SAVE_SALES_TRANSACTION_TENDER_TEMP 
			@StoreCode, @BusinessDay, @POSID, @SHIFTID, @COMPUTERIP, @paymentType, @PaidAmount, @PaidAmount, @ReferenceNo, @SYS_USR
			FETCH NEXT FROM CSR_DOWNLOAD_PAYMENT INTO @paymentType,@ReferenceNo,@PaidAmount
		END
		CLOSE CSR_DOWNLOAD_PAYMENT
		DEALLOCATE CSR_DOWNLOAD_PAYMENT

		
		--Check Detail VS Header
		DECLARE @DETAIL_POS DECIMAL(19,5)=0
		SET @DETAIL_POS = (SELECT ISNULL(SUM(TOTAL),0) FROM RETAIL_TRANSACTION.dbo.TrSalesT WHERE  COMPUTERIP = @COMPUTERIP)
		IF ISNULL(@TotalPaidAmount,0) - ISNULL(@DETAIL_POS,0) > 100 
		BEGIN
			DECLARE @MSG_ERR_1 as  VARCHAR(8000) = 'SALES DETAIL (' + CONVERT(VARCHAR(MAX),@DETAIL_POS) + ') AND PAYMENT (' + CONVERT(VARCHAR(MAX),@TotalPaidAmount) + ') ARE NOT MATCH'
			RAISERROR (@MSG_ERR_1,16,1)
		END
			
			

		--DPP, Items, Tax Processing
		DECLARE @DPP_TOTAL as decimal(19,5) = 0, @Items_TOTAL as int = 0, @Tax_TOTAL as decimal(19,5) = 0
		SET @DPP_TOTAL = (SELECT ISNULL(SUM(DPP),0) FROM RETAIL_TRANSACTION.dbo.TrSalesT
		WHERE COMPUTERIP = @COMPUTERIP
		AND TaxCode <> '0'
		AND Barcode NOT IN (SELECT Barcode FROM RETAIL_MASTER.dbo.MItemDonation))
		SET @DPP_TOTAL = ISNULL(@DPP_TOTAL,0) 

		SELECT @Items_TOTAL = ISNULL(ItemCount,0),
		@Tax_TOTAL = ISNULL(Tax,0)
		FROM
		(
			SELECT ISNULL(SUM(Total),0) as Total,@DPP as DPP,ISNULL(SUM(Tax),0) as Tax,ISNULL(SUM(Subtotal),0) as Subtotal FROM RETAIL_TRANSACTION.dbo.TrSalesT
			WHERE COMPUTERIP = @COMPUTERIP
		)TOTAL
		LEFT JOIN
		(
			SELECT CEILING(ISNULL(SUM(Qty),0)) as ItemCount
			FROM RETAIL_TRANSACTION.dbo.TrSalesT WHERE COMPUTERIP = @COMPUTERIP 
			AND Barcode NOT IN (SELECT Barcode FROM RETAIL_MASTER.dbo.MItemReceiptDisallow)
		)ITEM_COUNT ON 1=1

		EXEC RETAIL_TRANSACTION.dbo.TRX_EY_POST_SALES_TRANSACTION
		@StoreCode,@BusinessDay,@POSID,@SHIFTID,@COMPUTERIP,@TotalPaidAmount,@DPP_TOTAL,
		@Tax_TOTAL,@TotalPaidAmount,@Items_TOTAL,@TotalPaidAmount,0,@TotalPaidAmount,
		@order_id,'','',@SYS_USR,'','SALES TRANSACTION','',@LSMEMBERSHIP,@COMPANYID,@COMPANYPCID,
		0,0,0

		DECLARE @DOCNUMBR_POST VARCHAR(100) = (SELECT TOP 1 DOCNUMBR FROM RETAIL_TRANSACTION.dbo.TrSalesHH WHERE ORIDOCNUMBR = @order_id ORDER BY CREATDT DESC)
		
		--Promo Happen
		DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesPromoH WHERE DOCNUMBR = @DOCNUMBR_POST 
		INSERT INTO RETAIL_TRANSACTION.dbo.TrSalesPromoH(DOCNUMBR,DOCTYPE,StoreCode,BusinessDay,POSID,SHIFTID,PromoCode,LimitQty,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
		select @DOCNUMBR_POST,'SALES TRANSACTION',@StoreCode,@Businessday,@POSID,@SHIFTID,PROMOCODE,LIMITQTY,@SYS_USR,GETDATE(),'','1900-01-01' 
		from famiorderPH WHERE OrderID = @order_id AND ISNULL(LIMITQTY,0)>0

		EXEC RETAIL_TRANSACTION.dbo.TRX_EY_POST_AGE_GENDER_CUSTOMER
		@StoreCode,
		@BusinessDay,
		@POSID,
		@SHIFTID,
		@SYS_USR,
		@DOCNUMBR_POST,
		'SALES TRANSACTION',
		'FAMIAPPS'

		UPDATE RETAIL_TRANSACTION.dbo.TrKitchenHH SET TRXSOURCE = @order_id WHERE DOCNUMBR = @DOCNUMBR_POST
		UPDATE famiorderHH SET POSReceiptNo = @DOCNUMBR_POST, MotherStoreCode = @StoreCode, Businessday = @Businessday, POSID = @POSID, SHIFTID = @SHIFTID, 
		RCPOSID = '200', RCPOSMSG = 'SUCCESS',RCPOSDT = GETDATE(),
		MDFUSRID = @SYS_USR,MODIFDT = GETDATE()
		WHERE OrderID = @order_id
		
	COMMIT
END
END TRY
BEGIN CATCH
	IF @@trancount  > 0
	BEGIN
		ROLLBACK
	END
	IF CURSOR_STATUS('global','CSR_DOWNLOAD')>=-1
	BEGIN
		CLOSE CSR_DOWNLOAD
		DEALLOCATE CSR_DOWNLOAD
	END
	IF CURSOR_STATUS('global','CSR_DOWNLOAD_PAYMENT')>=-1
	BEGIN
		CLOSE CSR_DOWNLOAD_PAYMENT
		DEALLOCATE CSR_DOWNLOAD_PAYMENT
	END
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	IF NOT EXISTS (SELECT '' FROM famiorderHH WHERE OrderID = @order_id AND RCPOSID = '200')
	BEGIN
		UPDATE famiorderHH SET RCPOSID = '400',RCPOSMSG = @ERROR_ALL, RCPOSDT = GETDATE() WHERE OrderID = @order_id
	END
	RAISERROR(@ERROR_ALL,16,1)
END CATCH		
set nocount off
GO

CREATE PROC FAMIAPPS_EY_EXECUTE_AUTOMATIC_TRANSACTION
@SESSIONID varchar(100),
@StoreCode VARCHAR(100),
@POSID VARCHAR(100),
@LSMEMBERSHIP varchar(100), --IT-ERVIN\SQL2014
@COMPANYID varchar(100), --FMIMEMBER
@COMPANYPCID varchar(100), --FMI_MEMBERSHIP
@SYSUSR VARCHAR(100)
as
set nocount on
BEGIN TRY
	--Check POSID Online
	DECLARE @POSID_ONLINE AS VARCHAR(100) = ''
	SET @POSID_ONLINE = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'POS_ID_ONLINE')
	SET @POSID_ONLINE = ISNULL(@POSID_ONLINE,'')
	IF @POSID <> @POSID_ONLINE
	BEGIN
		RAISERROR ('POS ID ONLINE IS INVALID',16,1)
	END
	--Check MotherStore
	IF @StoreCode <> '0000007777'
		RAISERROR ('MOTHER STORE IS INVALID',16,1)

	DECLARE @Businessday VARCHAR(100)='', @SHIFTID VARCHAR(100)=''
	SELECT @Businessday = BusinessDay, @SHIFTID = SHIFTID FROM RETAIL_TRANSACTION.dbo.TrBatchD 
	WHERE POSID = @POSID AND StoreCode = @StoreCode AND BatchSts = 0

	IF ISNULL(@Businessday,'') = ''
		RAISERROR ('BUSINESSDAY IS BLANK',16,1)
	IF ISNULL(@SHIFTID,'') = ''
		RAISERROR ('SHIFT IS BLANK',16,1)
	
	CREATE TABLE #EXECUTE_ORDER
	(
		OrderID VARCHAR(100)
	)

	INSERT INTO #EXECUTE_ORDER(OrderID)
	SELECT OrderID FROM famiorderHH WHERE RCPOSID <> '200' AND OrderSts <> 0

	DECLARE @OrderID VARCHAR(100) = ''
	DECLARE CSR_AUTO CURSOR FOR SELECT OrderID FROM #EXECUTE_ORDER
	OPEN CSR_AUTO
	FETCH NEXT FROM CSR_AUTO INTO @OrderID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			EXEC FAMIAPPS_EY_CORE_POST_TRANSACTION @SESSIONID,@StoreCode,
			@POSID_ONLINE,@SHIFTID,@Businessday,@OrderID,@SYSUSR,@LSMEMBERSHIP,@COMPANYID,@COMPANYPCID
		END TRY
		BEGIN CATCH
		END CATCH
		FETCH NEXT FROM CSR_AUTO INTO @OrderID
	END
	CLOSE CSR_AUTO
	DEALLOCATE CSR_AUTO

	SELECT OrderID,RCPOSID,RCPOSMSG,RCPOSDT,POSReceiptNo,Businessday,POSID,SHIFTID,
	Businessday,MotherStoreCode
	FROM famiorderHH WHERE OrderID IN (SELECT OrderID FROM #EXECUTE_ORDER)
	AND ISNULL(POSReceiptNO,'') <> ''
END TRY
BEGIN CATCH
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_EY_DISPLAY_RETURN_JSON_TO_BE_SEND
as
set nocount on
BEGIN TRY
	SELECT OrderID,ISRTNFULL,RTNJSON,ISNULL(RTN_AMT,0) AS RTN_AMT FROM famiorderHH where RTNJSON <> '' AND (RCUPDTRTN = '' OR RCUPDTRTN <> '200')
END TRY
BEGIN CATCH
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_EY_UPDATE_RETURN_JSON_SENT_STATUS
@OrderID VARCHAR(100),
@RC VARCHAR(100),
@RM VARCHAR(100),
@RDT DATETIME
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		UPDATE famiorderHH 
		SET RCUPDTRTN = @RC,
		RMUPDTRTN = @RM,
		DTUPDTRTN = @RDT
		WHERE OrderID = @OrderID
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_EY_SAVE_SUPPLIES_TEMP
@SESSIONID VARCHAR(100),
@OrderID VARCHAR(100),
@Barcode VARCHAR(100),
@Qty DECIMAL(17,5),
@SYSUSR VARCHAR(100)
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		INSERT INTO famiorderSuppliesT
		(SESSIONID,OrderID,Barcode,Qty,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
		VALUES 
		(@SESSIONID,@OrderID,@Barcode,@Qty,@SYSUSR,GETDATE(),'','1900-01-01')
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_EY_ADD_SUPPLIES_TO_SALES_TRANSACTION
as
set nocount on
BEGIN TRY
	DECLARE @OrderID VARCHAR(100) = ''
	DECLARE CSR_AUTO CURSOR FOR SELECT OrderID FROM famiorderSuppliesHH WHERE IsConfirmed = 0
	OPEN CSR_AUTO
	FETCH NEXT FROM CSR_AUTO INTO @OrderID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			BEGIN TRAN
				DECLARE @DOCNUMBR AS VARCHAR(100) = ''
				SET @DOCNUMBR = (SELECT POSReceiptNo FROM famiorderHH WHERE OrderID = @OrderID)
				IF EXISTS (SELECT '' FROM RETAIL_TRANSACTION.dbo.TrSalesHH WHERE DOCNUMBR = @DOCNUMBR)
					AND EXISTS(SELECT '' FROM famiorderHH WHERE ISSYNCSUPPLY = 0 AND OrderID = @OrderID)
				BEGIN
					DECLARE @DOCTYPE VARCHAR(100)='', @StoreCode VARCHAR(100) = '',@Businessday VARCHAR(100)='',
					@POSID VARCHAR(100)='',@SHIFTID VARCHAR(100)=''
					select TOP 1 @DOCTYPE = DOCTYPE, @StoreCode = StoreCode, @Businessday = BusinessDay, 
					@POSID = POSID, @SHIFTID = SHIFTID from RETAIL_TRANSACTION.dbo.TrsalesHH WHERE DOCNUMBR = @DOCNUMBR

					DECLARE @LNITMSEQ_MAX INT = (SELECT MAX(LNITMSEQ) FROM RETAIL_TRANSACTION.dbo.TrSalesDH 
					WHERE DOCNUMBR = @DOCNUMBR)

					INSERT INTO RETAIL_TRANSACTION.dbo.TrSalesDH(DOCNUMBR,DOCTYPE,StoreCode,BusinessDay,POSID,
					SHIFTID,LNITMSEQ,Barcode,ItemCode,ItemDescriptionLong,ItemDescriptionShort,ItemType,TaxCode,
					MerchCat,MerchCatDesc,Category,CategoryDesc,
					Department,DepartmentDesc,Division,DivisionDesc,OpenPriceFlg,UnitPrice,Qty,Disc,Subtotal,DPP,Tax,Total,QtySales,SalesDisc,SalesSubtotal,SalesDPP,
					SalesTax,SalesTotal,QtyReturned,ReturnedDisc,ReturnedSubtotal,ReturnedDPP,ReturnedTax,ReturnedTotal,ItemValue,PromoCode,CRUSRID,CREATDT,MDFUSRID,MODIFDT,ReturnAble)
					SELECT @DOCNUMBR,@DOCTYPE,@StoreCode,@Businessday,@POSID,
					@SHIFTID,LNITMSEQ + @LNITMSEQ_MAX, X.Barcode,ItemCode,ItemDescriptionLong,ItemDescriptionShort,ItemType,TaxCode, 
					MItem.MerchCat,MerchCatDesc,Category,CategoryDesc,
					Department,DepartmentDesc,Division,DivisionDesc,OpenPriceFlg,0,Qty,0,0,0,0,0,Qty,0,0,0,
					0,0,0,0,0,0,0,0,0,'','ADD_SUPP',GETDATE(),'','1900-01-01',ReturnAble
					FROM
					(
						SELECT ROW_NUMBER() OVER(ORDER BY famiorderSuppliesDH.Barcode ASC) AS LNITMSEQ,famiorderSuppliesDH.Barcode,Qty FROM famiorderSuppliesDH 
						WHERE OrderID = @OrderID
						AND IsSync = 0
					)X
					LEFT JOIN RETAIL_MASTER.dbo.MItem ON X.Barcode = MItem.Barcode
					LEFT JOIN RETAIL_MASTER.dbo.MMerchandiseCategory ON MItem.MerchCat = MMerchandiseCategory.MerchCat
					WHERE ItemDescriptionLong is not null

					UPDATE famiorderSuppliesDH SET IsSync = 1 WHERE OrderID = @OrderID
					AND Barcode IN
					(
						SELECT X.Barcode
						FROM
						(
							SELECT ROW_NUMBER() OVER(ORDER BY famiorderSuppliesDH.Barcode ASC) AS LNITMSEQ,famiorderSuppliesDH.Barcode,Qty FROM famiorderSuppliesDH 
							WHERE OrderID = @OrderID
							AND IsSync = 0
						)X
						LEFT JOIN RETAIL_MASTER.dbo.MItem ON X.Barcode = MItem.Barcode
						LEFT JOIN RETAIL_MASTER.dbo.MMerchandiseCategory ON MItem.MerchCat = MMerchandiseCategory.MerchCat
						WHERE ItemDescriptionLong is not null
					)

					UPDATE famiorderSuppliesHH SET IsConfirmed = 1, MDFUSRID = 'ADD_SUP_OK', MODIFDT = GETDATE()
					WHERE OrderID = @OrderID
				
					UPDATE famiorderHH SET ISSYNCSUPPLY = 1, ISSYNCDTSUPPLY = GETDATE() 
					WHERE OrderID = @OrderID
				END
			COMMIT
		END TRY
		BEGIN CATCH
			ROLLBACK
			UPDATE famiorderSuppliesHH SET MDFUSRID = 'ADD_SUP_NOTOK', MODIFDT = GETDATE()
			WHERE OrderID = @OrderID
		END CATCH
		FETCH NEXT FROM CSR_AUTO INTO @OrderID
	END
	CLOSE CSR_AUTO
	DEALLOCATE CSR_AUTO
END TRY
BEGIN CATCH
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH		
set nocount off
GO

CREATE PROC FAMIAPPS_EY_SAVE_SUBSTITUTION_TEMP
@SESSIONID VARCHAR(100),
@OrderID VARCHAR(100),
@old_article varchar(100),
@old_qty DECIMAL(17,5),
@old_price DECIMAL(19,5),
@old_barcode VARCHAR(100),
@new_article varchar(100),
@new_qty DECIMAL(17,5),
@new_price DECIMAL(19,5),
@new_barcode VARCHAR(100),
@trx_date DATETIME,
@NOTE VARCHAR(8000),
@detail_id varchar(100),
@SYSUSR VARCHAR(100)
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		INSERT INTO famiordersubsT
		(SESSIONID,OrderID,old_article,old_qty,old_price,old_barcode,
		new_article,new_qty,new_price,new_barcode,trx_date,NOTE,detail_id,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
		VALUES 
		(@SESSIONID,@OrderID,@old_article,@old_qty,@old_price,@old_barcode,
		@new_article,@new_qty,@new_price,@new_barcode,@trx_date,@NOTE,@detail_id,
		@SYSUSR,GETDATE(),'','1900-01-01')
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO


------------------------------------------------------S2-------------------------------------------------------------
CREATE PROC FAMIAPPS_DISPLAY_ORDER_TO_GENERATE_KITCHEN_JSON_S2
@StoreCode as VARCHAR(100)
as
set nocount on
BEGIN TRY
	SELECT OrderID,account_name FROM famiorderS2HH WHERE ISNULL(KTCJSONS2,'') = '' AND StoreCode = @StoreCode
	AND ISNULL(KTCJSONS2,'') <> 'X'
END TRY
BEGIN CATCH
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_SAVE_KITCHEN_TRANSACTION
@OrderID VARCHAR(100)
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		IF NOT EXISTS (SELECT '' FROM RETAIL_TRANSACTION.dbo.TrKitchenHH WHERE DOCNUMBR = @OrderID)
		BEGIN
			DECLARE @StoreCode VARCHAR(100) = ''
			SET @StoreCode = (SELECT TOP 1 StoreCode FROM FamiOrderS2HH WHERE OrderID = @OrderID)
			INSERT INTO RETAIL_TRANSACTION.dbo.TrKitchenHH(
			DOCNUMBR,DOCTYPE,StoreCode,BusinessDay,POSID,SHIFTID,
			QUEUENO,TRXSOURCE,SHA256STR,JSONROW,RC,RM,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
			SELECT 
			OrderID,'SALES TRANSACTION',@StoreCode,'','','',
			'???','','','','','','SYSTEMS',GETDATE(),'','1900-01-01' 
			FROM FamiOrderS2HH WHERE OrderID = @OrderID
			
			INSERT INTO RETAIL_TRANSACTION.dbo.TrKitchenDH(DOCNUMBR,DOCTYPE,StoreCode,BusinessDay,POSID,SHIFTID,
			LNITMSEQ,ORD,OrderLineNo,
			ArticleNo,ArticleName,Qty,Price,OrderNotes,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
			SELECT OrderID,'SALES TRANSACTION',@StoreCode,'','','',
			LNITMSEQ,ROW_NUMBER() OVER(ORDER BY LNITMSEQ ASC) as ORD,ROW_NUMBER() OVER(ORDER BY LNITMSEQ ASC) as OrderLineNo,
			ItemCode as ArticleNo,ItemDescriptionLong as ArticleName,QTY as Qty,UnitPrice as Price,
			RTRIM(ISNULL(ADDITIONAL,'') + 
							CASE 
							WHEN ISNULL(TOPPING,'') <> '' THEN 
								'|' 
							ELSE 
								'' 
							END 
							+ ISNULL(TOPPING,'') + 
							CASE 
							WHEN ISNULL(NOTE,'') <> '' THEN 
								' | ' 
							ELSE 
								'' 
							END 
							+ ISNULL(NOTE,''))
			as OrderNotes,
			'SYSTEMS',GETDATE(),'','1900-01-01'
			FROM famiorderS2DH WHERE OrderID = @OrderID
		END	
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO



CREATE PROC FAMIAPPS_GENERATE_ORDER_KITCHEN_JSON_S2
@OrderID VARCHAR(100),
@StoreCode VARCHAR(100)
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		DECLARE @JSONKITCHEN AS VARCHAR(8000) =  ''
		SET @JSONKITCHEN = (SELECT KTCJSONS2 FROM famiorderS2HH WHERE OrderID = @OrderID AND StoreCode = @StoreCode)
		IF ISNULL(@JSONKITCHEN,'')=''
		BEGIN
			SELECT UnitPrice as Price,ItemCode as ArticleNo, 
			RTRIM(ISNULL(ADDITIONAL,'') + 
							CASE 
							WHEN ISNULL(TOPPING,'') <> '' THEN 
								'|' 
							ELSE 
								'' 
							END 
							+ ISNULL(TOPPING,'') + 
							CASE 
							WHEN ISNULL(NOTE,'') <> '' THEN 
								' | ' 
							ELSE 
								'' 
							END 
							+ ISNULL(NOTE,''))
			as OrderNotes,
			ItemDescriptionLong as ArticleName,ROW_NUMBER() OVER(ORDER BY LNITMSEQ ASC) as OrderLineNo,QTY as Qty
			FROM famiorderS2DH WHERE OrderID = @OrderID
		END
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_UPDATE_ORDER_KITCHEN_JSON_S2
@OrderID VARCHAR(100),
@KTCJSONS2 VARCHAR(8000),
@StoreCode varchar(100)
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		UPDATE famiorderS2HH SET KTCJSONS2 = @KTCJSONS2 WHERE OrderID  = @OrderID AND StoreCode = @StoreCode
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_DISPLAY_REQUEST_PICKUP_DELIVERY_ORDER_S2
@StoreCode varchar(100)
as
set nocount on
BEGIN TRY
	DECLARE @IS_AUTO_RECEIVED AS INT = 0 
	SET @IS_AUTO_RECEIVED = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'FAMIAPPS_AUTO')
	SET @IS_AUTO_RECEIVED = ISNULL(@IS_AUTO_RECEIVED,0)

	IF @IS_AUTO_RECEIVED = 1
	BEGIN
		SELECT OrderID,Shipping_reference_no_tarif,ShippingProvider FROM famiorderS2HH where RCRQPICKUP <> '200' AND OrderType = 'delivery' AND StoreCode = @StoreCode
	END
	ELSE
	BEGIN
		SELECT OrderID,Shipping_reference_no_tarif,ShippingProvider FROM famiorderS2HH
		WHERE GETDATE() >= DATEADD(minute,2,ORDERDT)
		AND RCRQPICKUP <> '200' AND OrderType = 'delivery' AND StoreCode = @StoreCode
	END	
END TRY
BEGIN CATCH
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_UPDATE_REQUEST_PICKUP_DELIVERY_ORDER_S2
@StoreCode varchar(100),
@OrderID varchar(100),
@RCRQPICKUP varchar(10),
@RMRQPICKUP varchar(8000),
@JSONRQPICKUP varchar(8000),
@order_no_resi varchar(100),
@order_live_tracking_url varchar(8000),
@order_status varchar(100)
as
set nocount on
BEGIN TRY
	UPDATE famiorderS2HH 
	SET RCRQPICKUP = @RCRQPICKUP,
	RMRQPICKUP = @RMRQPICKUP,
	DTRQPICKUP = GETDATE(),
	JSONRQPICKUP = @JSONRQPICKUP,
	ShippingReferenceNo = @order_no_resi,
	ShippingTracking =  @order_live_tracking_url,
	ShippingDesc =  @order_status
	WHERE OrderID = @OrderID
	AND StoreCode = @StoreCode
END TRY
BEGIN CATCH
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_DISPLAY_REQUEST_TO_KITCHEN_ORDER_S2
@StoreCode varchar(100)
as
set nocount on
BEGIN TRY
	declare @limit_min int = -30
	declare @limit_max int = 30

	IF EXISTS 
	(
		SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID  = 'KITCHEN_LIMIT_TIME'
	)
	BEGIN
		SET @limit_min = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID  = 'KITCHEN_LIMIT_TIME') * -1
		SET @limit_max = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID  = 'KITCHEN_LIMIT_TIME')
	END
	ELSE
	BEGIN
		SET @limit_min = -30
		SET @limit_max = 30
	END

	DECLARE @IS_AUTO AS VARCHAR(100) = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'FAMIAPPS_AUTO')
	SET @IS_AUTO = ISNULL(@IS_AUTO,'0')

	DECLARE @IS_KITCHEN_SYSTEM AS VARCHAR(100) = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'IS_KITCHEN_MODULE')
	SET @IS_KITCHEN_SYSTEM = ISNULL(@IS_KITCHEN_SYSTEM,'0')

	IF @IS_KITCHEN_SYSTEM = 1
	BEGIN
		IF @IS_AUTO = 1
		BEGIN
			EXEC('
				SELECT OrderID,KTCJSONS2,OrderType,QUEUENO,account_name,RCKTCS2
				FROM
				(
					SELECT OrderID,KTCJSONS2,OrderType,ISNULL(QUEUENO,'''') as QUEUENO,account_name,RCKTCS2 FROM famiorderS2HH where ISNULL(QUEUENO,'''') = '''' AND OrderType = ''delivery'' AND StoreCode = '''+@StoreCode+''' AND OrderSts NOT IN (0,1)
					UNION
					SELECT OrderID,KTCJSONS2,OrderType,ISNULL(QUEUENO,'''') as QUEUENO,account_name,RCKTCS2 FROM famiorderS2HH 
					where ISNULL(QUEUENO,'''') = '''' AND OrderType = ''pickup''  
					AND SUBSTRING(CONVERT(VARCHAR,GETDATE(),108),1,5) BETWEEN 
					SUBSTRING(CONVERT(VARCHAR,DATEADD(MINUTE,'+@limit_min+',CONVERT(TIME,PickUpTime,108))),1,5) AND
					SUBSTRING(CONVERT(VARCHAR,DATEADD(MINUTE,'+@limit_max+',CONVERT(TIME,PickUpTime,108))),1,5)
					AND StoreCode = '''+@StoreCode+''' AND OrderSts NOT IN (0,1)
					UNION
					SELECT OrderID,KTCJSONS2,OrderType,ISNULL(QUEUENO,'''') as QUEUENO,account_name,RCKTCS2 FROM famiorderS2HH 
					where ISNULL(QUEUENO,'''') = '''' AND OrderType = ''pickup'' 
					AND CONVERT(varchar,ORDERDT,112) + REPLACE(CONVERT(varchar,PickUpTime,108),'':'','''') < CONVERT(varchar,GETDATE(),112) + REPLACE(CONVERT(varchar,GETDATE(),108),'':'','''')
					AND StoreCode = '''+@StoreCode+''' AND OrderSts NOT IN (0,1)
					UNION
					SELECT OrderID,KTCJSONS2,OrderType,ISNULL(QUEUENO,'''') as QUEUENO,account_name,RCKTCS2 FROM famiorderS2HH
					WHERE ISNULL(QUEUENO,'''') <> '''' AND RCKTCS2 NOT IN (''0'',''200'')
				)SALES
				WHERE KTCJSONS2 <> ''X''
			')
		END
		ELSE
		BEGIN
			EXEC('
				SELECT OrderID,KTCJSONS2,OrderType,QUEUENO,account_name,RCKTCS2
				FROM
				(
					SELECT OrderID,KTCJSONS2,OrderType,ISNULL(QUEUENO,'''') as QUEUENO,account_name,RCKTCS2 FROM famiorderS2HH 
					where ISNULL(QUEUENO,'''') = '''' AND OrderType = ''pickup''  
					AND SUBSTRING(CONVERT(VARCHAR,GETDATE(),108),1,5) BETWEEN 
					SUBSTRING(CONVERT(VARCHAR,DATEADD(MINUTE,'+@limit_min+',CONVERT(TIME,PickUpTime,108))),1,5) AND
					SUBSTRING(CONVERT(VARCHAR,DATEADD(MINUTE,'+@limit_max+',CONVERT(TIME,PickUpTime,108))),1,5)
					AND StoreCode = '''+@StoreCode+''' AND OrderSts NOT IN (0,1)
					UNION
					SELECT OrderID,KTCJSONS2,OrderType,ISNULL(QUEUENO,'''') as QUEUENO,account_name,RCKTCS2 FROM famiorderS2HH
					WHERE ISNULL(QUEUENO,'''') <> '''' AND RCKTCS2 NOT IN (''0'',''200'')
					UNION
					SELECT OrderID,KTCJSONS2,OrderType,ISNULL(QUEUENO,'''') as QUEUENO,account_name,RCKTCS2 FROM famiorderS2HH 
					where ISNULL(QUEUENO,'''') = '''' AND OrderType = ''pickup'' 
					AND CONVERT(varchar,ORDERDT,112) + REPLACE(CONVERT(varchar,PickUpTime,108),'':'','''') < CONVERT(varchar,GETDATE(),112) + REPLACE(CONVERT(varchar,GETDATE(),108),'':'','''')
					AND StoreCode = '''+@StoreCode+''' AND OrderSts NOT IN (0,1)
					UNION
					SELECT OrderID,KTCJSONS2,OrderType,ISNULL(QUEUENO,'''') as QUEUENO,account_name,RCKTCS2 FROM famiorderS2HH 
					where ISNULL(QUEUENO,'''') = '''' AND OrderType = ''delivery'' AND StoreCode = '''+@StoreCode+'''
					AND GETDATE() >= DATEADD(minute,4,ORDERDT) AND OrderSts NOT IN (0,1)
				)SALES
				WHERE KTCJSONS2 <> ''X''
			')
		END
	END
END TRY
BEGIN CATCH
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_UPDATE_ORDER_SEND_TO_KITCHEN_S2
@StoreCode VARCHAR(100),
@OrderID VARCHAR(100),
@QUEUENO VARCHAR(100),
@RC VARCHAR(10),
@RM VARCHAR(8000)
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		IF EXISTS (SELECT '' FROM famiorderS2HH WHERE OrderID  = @OrderID AND StoreCode = @StoreCode AND QUEUENO <> '')
		BEGIN
			UPDATE famiorderS2HH SET RCKTCS2 = @RC, RCKTCMSGS2 = @RM, RCKTCDTS2 = GETDATE()
			WHERE OrderID  = @OrderID AND StoreCode = @StoreCode
		END 
		ELSE
		BEGIN
			UPDATE famiorderS2HH SET QUEUENO = @QUEUENO,RCKTCS2 = @RC, RCKTCMSGS2 = @RM, RCKTCDTS2 = GETDATE()
			WHERE OrderID  = @OrderID AND StoreCode = @StoreCode
		END
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_DISPLAY_AUTO_RECEIVED_ORDER
as
set nocount on
BEGIN TRY
	SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'FAMIAPPS_AUTO'
END TRY
BEGIN CATCH
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_EY_DISPLAY_SUPPLY_JSON_TO_BE_SEND_S2
as
set nocount on
BEGIN TRY
	SELECT famiorderS2HH.OrderID,RCSUPPLY,SUPLY_JSON FROM famiorderS2HH
	LEFT JOIN famiorderS2FH ON famiorderS2HH.OrderID = famiorderS2FH.OrderID
	WHERE SUPLY_JSON <> '' AND RCSUPPLY <> '200'
END TRY
BEGIN CATCH
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

CREATE PROC FAMIAPPS_EY_UPDATE_SUPPLY_JSON_SENT_STATUS_S2
@OrderID VARCHAR(100),
@RC VARCHAR(100),
@RM VARCHAR(100),
@RDT DATETIME
as
set nocount on
BEGIN TRY
	BEGIN TRAN
		UPDATE famiorderS2HH 
		SET RCSUPPLY = @RC,
		RMSUPPLY = @RM,
		RDSUPPLY = @RDT
		WHERE OrderID = @OrderID
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK
	DECLARE @ERROR_ALL AS VARCHAR(MAX)
	SET @ERROR_ALL = ERROR_MESSAGE()
	RAISERROR(@ERROR_ALL,16,1)
END CATCH	
set nocount off
GO

------------------------------------------------------------------------------
IF EXISTS (SELECT '' FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'V_FAMI_APPS')
BEGIN
	UPDATE RETAIL_SYSTEMS.dbo.MSetup SET SETUPVALUE = 'V.51.0' WHERE SETUPID = 'V_FAMI_APPS'
END
ELSE
BEGIN
	INSERT INTO RETAIL_SYSTEMS.dbo.MSetup(SETUPID,SETUPVALUE,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
	VALUES ('V_FAMI_APPS','V.51.0','SYSTEMS',GETDATE(),'','1900-01-01')
END

------------------------------------------------------------------------------