
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, SUBSTR(t.sales_credit_cd, 1, 10), t.sales_credit_cd, 0 from  (SELECT tenant_no , SALES_CREDIT_CD FROM CUSTOMER_CONTRACT_CONDITIONS_mig where customer_contract_no = 4 AND condition_cd IS NULL GROUP BY tenant_no , SALES_CREDIT_CD) t) ;

INSERT INTO MARKET_CONDITIONS(TENANT_NO,COMPANY_NO,DEPARTMENT_NO,MARKET_NO,CONDITION_CD,SALES_CREDIT_CD) 
(SELECT tenant_no,company_no,department_no,market_no,SUBSTR(sales_credit_cd, 1, 10),SALES_CREDIT_CD from  (SELECT tenant_no ,company_no, department_no, market_no, SALES_CREDIT_CD FROM CUSTOMER_CONTRACT_CONDITIONS_mig where customer_contract_no = 4 AND condition_cd IS NULL GROUP BY tenant_no ,company_no, department_no, market_no, SALES_CREDIT_CD) t) ;


UPDATE CUSTOMER_CONTRACT_CONDITIONS set condition_cd = 
(SELECT SUBSTR(sales_credit_cd, 1, 10) FROM CUSTOMER_CONTRACT_CONDITIONS_mig mig 
	WHERE CUSTOMER_CONTRACT_CONDITIONS.tenant_no = mig.tenant_no
	  AND CUSTOMER_CONTRACT_CONDITIONS.company_no = mig.company_no
	  AND CUSTOMER_CONTRACT_CONDITIONS.department_no = mig.department_no
	  AND CUSTOMER_CONTRACT_CONDITIONS.customer_no = mig.customer_no
	   AND CUSTOMER_CONTRACT_CONDITIONS.customer_contract_no = mig.customer_contract_no
	   AND CUSTOMER_CONTRACT_CONDITIONS.valid_from = mig.valid_from
	   )
WHERE   customer_contract_no = 4 AND condition_cd IS NULL ;


UPDATE SALES_CREDIT_BALANCE set condition_cd = SUBSTR(sales_credit_cd, 1, 10) WHERE  customer_contract_no = 4 AND condition_cd IS NULL ;


INSERT INTO conditions (TENANT_NO, CONDITION_CD,CONDITION_NM,CARD_WITH_SERIAL_NUMBER)  (SELECT tenant_no, 'PL' || CAST (SALES_PRICELIST_ID AS  CHAR(1)), 'PL' || CAST (SALES_PRICELIST_ID AS  CHAR(1)), 0 from  (SELECT tenant_no , SALES_PRICELIST_ID FROM CUSTOMER_CONTRACT_CONDITIONS_mig where customer_contract_no = 2 AND condition_cd IS NULL GROUP BY tenant_no , SALES_PRICELIST_ID) t) ;

INSERT INTO MARKET_CONDITIONS(TENANT_NO,COMPANY_NO,DEPARTMENT_NO,MARKET_NO,CONDITION_CD,SALES_PRICELIST_ID) 
(SELECT tenant_no,company_no,department_no,market_no,'PL' || CAST (SALES_PRICELIST_ID AS  CHAR(1)),SALES_PRICELIST_ID from  (SELECT tenant_no ,company_no, department_no, market_no, SALES_PRICELIST_ID FROM CUSTOMER_CONTRACT_CONDITIONS_mig where customer_contract_no = 2 AND condition_cd IS NULL GROUP BY tenant_no ,company_no, department_no, market_no, SALES_PRICELIST_ID) t) ;


UPDATE CUSTOMER_CONTRACT_CONDITIONS set condition_cd = 
(SELECT 'PL' || CAST (SALES_PRICELIST_ID AS  CHAR(1)) FROM CUSTOMER_CONTRACT_CONDITIONS_mig mig 
	WHERE CUSTOMER_CONTRACT_CONDITIONS.tenant_no = mig.tenant_no
	  AND CUSTOMER_CONTRACT_CONDITIONS.company_no = mig.company_no
	  AND CUSTOMER_CONTRACT_CONDITIONS.department_no = mig.department_no
	  AND CUSTOMER_CONTRACT_CONDITIONS.customer_no = mig.customer_no
	   AND CUSTOMER_CONTRACT_CONDITIONS.customer_contract_no = mig.customer_contract_no
	   AND CUSTOMER_CONTRACT_CONDITIONS.valid_from = mig.valid_from
	   )
WHERE   customer_contract_no = 2 AND condition_cd IS NULL ;


INSERT INTO conditions (TENANT_NO, CONDITION_CD,CONDITION_NM,CARD_WITH_SERIAL_NUMBER)  (SELECT tenant_no, SUBSTR(t.ITEM_CONDITION_CD, 1, 10), t.ITEM_CONDITION_CD, 0 from  (SELECT tenant_no , ITEM_CONDITION_CD FROM CUSTOMER_CONTRACT_CONDITIONS_mig where customer_contract_no = 3 AND condition_cd IS NULL GROUP BY tenant_no , ITEM_CONDITION_CD) t) ;

INSERT INTO MARKET_CONDITIONS(TENANT_NO,COMPANY_NO,DEPARTMENT_NO,MARKET_NO,CONDITION_CD,ITEM_CONDITION_CD) 
(SELECT tenant_no,company_no,department_no,market_no,SUBSTR(ITEM_CONDITION_CD, 1, 10),ITEM_CONDITION_CD from  (SELECT tenant_no ,company_no, department_no, market_no, ITEM_CONDITION_CD FROM CUSTOMER_CONTRACT_CONDITIONS_mig where customer_contract_no = 3 AND condition_cd IS NULL GROUP BY tenant_no ,company_no, department_no, market_no, ITEM_CONDITION_CD) t) ;


UPDATE CUSTOMER_CONTRACT_CONDITIONS set condition_cd = 
(SELECT SUBSTR(ITEM_CONDITION_CD, 1, 10) FROM CUSTOMER_CONTRACT_CONDITIONS_mig mig 
	WHERE CUSTOMER_CONTRACT_CONDITIONS.tenant_no = mig.tenant_no
	  AND CUSTOMER_CONTRACT_CONDITIONS.company_no = mig.company_no
	  AND CUSTOMER_CONTRACT_CONDITIONS.department_no = mig.department_no
	  AND CUSTOMER_CONTRACT_CONDITIONS.customer_no = mig.customer_no
	   AND CUSTOMER_CONTRACT_CONDITIONS.customer_contract_no = mig.customer_contract_no
	   AND CUSTOMER_CONTRACT_CONDITIONS.valid_from = mig.valid_from
	   )
WHERE   customer_contract_no = 3 AND condition_cd IS NULL ;
