
CREATE SEQUENCE SEQ_EXTERNAL_VOUCHER_USE_ID
AS INTEGER
START WITH 1;

INSERT INTO EXTERNAL_VOUCHER_USES(TENANT_NO,POS_CD,EXTERNAL_VOUCHER_USE_ID,EXTERNAL_VOUCHER_USE_TS,EXTERNAL_VOUCHER_VALUE,CANCEL_REASON,EXTERNAL_VOUCHER_CD,USED_POS_CD,USED_DRAWER_NO,USED_POS_SESSION_ID,USED_POS_PAYMENT_ID,USED_SALES_INV_ID,
USED_SALES_INV_CD,COMPANY_NO,DEPARTMENT_NO,BUSINESSUNIT_NO,BUSINESSUNIT_NM,CUSTOMER_NO,CUSTOMER_SALUTATION,CUSTOMER_TITLE,CUSTOMER_FIRST_NM,CUSTOMER_CONTACT_NM,CUSTOMER_STREET,CUSTOMER_COUNTRY_CD,CUSTOMER_POSTAL_CD,
CUSTOMER_CITY,VOUCHER_ORIGIN,EMPLOYEE_NO,EMPLOYEE_NM,VOUCHER_STATE )
SELECT pp.TENANT_NO
	,pp.pos_cd
	,next value for SEQ_EXTERNAL_VOUCHER_USE_ID
	,pp.PAYMENT_TS
	,pp.PAYMENT
	
	,pp.CANCEL_REASON
	,pp.PAYMENT_REF
	,pp.pos_cd
	,pp.DRAWER_NO
	,pp.POS_SESSION_ID
	,pp.POS_PAYMENT_ID
	,pp.SALES_INV_ID
	,si.SALES_INV_CD AS USED_SALES_INV_CD
	,si.COMPANY_NO
	,si.DEPARTMENT_NO
	,si.BUSINESSUNIT_NO
	,si.BU_NM_1
	,si.CUSTOMER_NO
	,si.CUSTOMER_SALUTATION
	,si.CUSTOMER_TITLE
	,si.CUSTOMER_FIRST_NM
	,si.CUSTOMER_CONTACT_NM
	,si.CUSTOMER_STREET
	,si.CUSTOMER_COUNTRY_CD
	,si.CUSTOMER_POSTAL_CD
	,si.CUSTOMER_CITY
,'GS'  
	,si.EMPLOYEE_NO
	,si.EMPLOYEE_NM
	,case when pp.payment_direction = 1 THEN 3 ELSE 5 END
FROM POS_PAYMENTS pp
join SALES_INVS si on pp.tenant_no = si.tenant_no and pp.pos_cd = si.pos_cd and pp.SALES_INV_ID = si.SALES_INV_ID

where pp.payment_type = 2  and SALES_VOUCHER_ID IS NULL and pp.EXTERNAL_VOUCHER_USES_ID IS NULL ;




DROP SEQUENCE SEQ_EXTERNAL_VOUCHER_USE_ID RESTRICT;



UPDATE POS_PAYMENTS 
SET EXTERNAL_VOUCHER_USES_ID = 
(SELECT EXTERNAL_VOUCHER_USE_ID FROM EXTERNAL_VOUCHER_USES t
where  t.tenant_no = POS_PAYMENTS.tenant_no and t.pos_cd = POS_PAYMENTS.pos_cd and t.USED_POS_CD = POS_PAYMENTS.pos_cd AND t.USED_DRAWER_NO = POS_PAYMENTS.DRAWER_NO  AND t.USED_POS_SESSION_ID = POS_PAYMENTS.POS_SESSION_ID AND t.USED_POS_PAYMENT_ID = POS_PAYMENTS.POS_PAYMENT_ID 
);

UPDATE POS_PAYMENTS 
SET EXTERNAL_VOUCHER_POS_CD = 
(SELECT USED_POS_CD FROM EXTERNAL_VOUCHER_USES t
where  t.tenant_no = POS_PAYMENTS.tenant_no and t.pos_cd = POS_PAYMENTS.pos_cd and t.USED_POS_CD = POS_PAYMENTS.pos_cd AND t.USED_DRAWER_NO = POS_PAYMENTS.DRAWER_NO  AND t.USED_POS_SESSION_ID = POS_PAYMENTS.POS_SESSION_ID AND t.USED_POS_PAYMENT_ID = POS_PAYMENTS.POS_PAYMENT_ID 
);


UPDATE EXTERNAL_VOUCHER_USES SET CANCEL_FOR_EXTERNAL_VOUCHER_USE_ID =
(SELECT ppCanceled.EXTERNAL_VOUCHER_USES_ID FROM POS_PAYMENTS pp
JOIN POS_PAYMENTS ppCanceled 
ON pp.CANCEL_FOR_POS_SESSION_ID = ppCanceled.POS_SESSION_ID AND pp.CANCEL_FOR_DRAWER_NO = ppCanceled.DRAWER_NO AND pp.CANCEL_FOR_POS_PAYMENT_ID = ppCanceled.POS_PAYMENT_ID
WHERE pp.EXTERNAL_VOUCHER_USES_ID = EXTERNAL_VOUCHER_USES.EXTERNAL_VOUCHER_USE_ID
 );




INSERT INTO conditions (TENANT_NO, CONDITION_CD,CONDITION_NM,CARD_WITH_SERIAL_NUMBER)  (SELECT tenant_no, 'scard' , 'S-Card', 1 from tenants )  ;


UPDATE CUSTOMER_CONTRACT_CONDITIONS set condition_cd = 'scard'
WHERE   customer_contract_no = 2 AND condition_cd IS NULL ;
