DMF_SALES_DISCOUNT.sql

Source: DMF/PuC/AIF/DR1 scripts/DMF_SALES_DISCOUNT.sql

INSERT INTO ORI_SALES_DISCOUNT_CTRL 
(	SLS_TRX_ID,PROD_IT_NUM,ORG_NUM,DAY_DT,MIN_NUM,REVISION_NUM
	,DISC_TYPE  -- for the future this field must be reviewed
	,COUPON_NUMBER,COUPON_REF_NUMBER,CO_HEAD_ID,CO_LINE_ID,CUST_REF_TYPE,CUST_REF_NUMBER,RTL_TYPE_CODE
	,EXCHANGE_DT,SLS_QTY,SLS_AMT_LCL
	,SLS_DISC_AMT_LCL--regular discount
	,RET_QTY,RET_AMT_LCL
	,RET_DISC_AMT_LCL--regular discount
	,DATASOURCE_NUM_ID,DOC_CURR_CODE,ETL_THREAD_VAL 
	,INTEGRATION_ID -- TEM QUE SER UNICO POR LINHA NA TABELA DE DISCOUNT
	,LOC_CURR_CODE,EMPLOYEE_NUM,PROMO_COMP_ID,PROMO_PARENT_ID,SALES_PERSON_ID,CASHIER_ID
) 
--regular discount
select 
        SLS_TRX_ID
        ,ITEM AS PROD_IT_NUM
        ,ORG_NUM
        ,DAY_DT
        ,LPAD(MIN_NUM, 2, '0') AS MIN_NUM 
        ,1 as REVISION_NUM
        ,'NEWPRC' AS DISC_TYPE  -- for the future this field must be reviewes
        , '-1' AS COUPON_NUMBER -- W_RTL_COUPON_D
        , NULL AS COUPON_REF_NUMBER -- W_RTL_COUPON_D
        ,CO_HEAD_ID
        ,CO_LINE_ID
        ,CUSTOMER_TYPE AS CUST_REF_TYPE
        ,CUSTOMER_NUM AS CUST_REF_NUMBER
        ,RTL_TYPE_CODE
        ,DAY_DT AS EXCHANGE_DT -- NEW
        ,SLS_QTY
        ,SLS_AMT_LCL
        ,SLSPR_DISC_AMT_LCL AS SLS_DISC_AMT_LCL--regular discount
        ,RET_QTY
        ,RET_AMT_LCL
        ,RETPR_DISC_AMT_LCL AS RET_DISC_AMT_LCL--regular discount
        ,1 AS DATASOURCE_NUM_ID
        ,DOC_CURR_CODE
        ,1 AS ETL_THREAD_VAL -- NEW
        ,ORG_NUM || '~' || ITEM || '~' || SLS_TRX_ID || '~' || TO_CHAR(DAY_DT, 'YYYYMMDD') || '~' || CO_HEAD_ID || '~' || CO_LINE_ID || '~' || 'EMPDSC' || '~' || '-1' AS INTEGRATION_ID -- REVISAR SE ESTÁ CORRETO (TEM QUE SER UNICO POR LINHA NA TABELA DE DISCOUNT?)
        ,LOC_CURR_CODE
        ,'-1' AS EMPLOYEE_NUM-- NEW
        ,'-2' AS PROMO_COMP_ID-- NEW
        ,'-2' AS PROMO_PARENT_ID-- NEW
        ,'-1' AS SALES_PERSON_ID-- NEW
        ,'-1' AS CASHIER_ID-- NEW
from ori_sales_ctrl
where (SLSPR_DISC_AMT_LCL > 0 and SLSPR_DISC_AMT_LCL is not null) or (RETPR_DISC_AMT_LCL > 0 and RETPR_DISC_AMT_LCL is not null)
union all
 --employee discount
select 
        SLS_TRX_ID
        ,ITEM
        ,ORG_NUM
        ,DAY_DT
        ,LPAD(MIN_NUM, 2, '0') AS MIN_NUM -- precisa formatar???
        ,1 as REVISION_NUM
        ,'EMPDSC' AS DISC_TYPE
        ,'-1' AS COUPON_NUMBER -- validar campo mandatorio. PUC nao trabalhar com CUPOM
        , NULL AS COUPON_REF_NUMBER -- validar campo mandatorio. PUC nao trabalhar com CUPOM
        ,CO_HEAD_ID
        ,CO_LINE_ID
        ,CUSTOMER_TYPE AS CUST_REF_TYPE
        ,CUSTOMER_NUM AS CUST_REF_NUMBER
        ,RTL_TYPE_CODE
        ,DAY_DT AS EXCHANGE_DT -- NEW
        ,SLS_QTY
        ,SLS_AMT_LCL
        ,SLS_EMP_DISC_AMT_LCL AS SLS_DISC_AMT_LCL--employee discount
        ,RET_QTY
        ,RET_AMT_LCL
        ,RET_EMP_DISC_AMT_LCL AS RET_DISC_AMT_LCL -- employee discount
        ,1 AS DATASOURCE_NUM_ID
        ,DOC_CURR_CODE
        ,1 AS ETL_THREAD_VAL -- NEW
        ,ORG_NUM || '~' || ITEM || '~' || SLS_TRX_ID || '~' || TO_CHAR(DAY_DT, 'YYYYMMDD') || '~' || CO_HEAD_ID || '~' || CO_LINE_ID || '~' || 'EMPDSC' || '~' || '-1' AS INTEGRATION_ID -- REVISAR SE ESTÁ CORRETO (TEM QUE SER UNICO POR LINHA NA TABELA DE DISCOUNT?)
        ,LOC_CURR_CODE
        ,'-1' AS EMPLOYEE_NUM-- NEW
        ,'-2' AS PROMO_COMP_ID-- NEW
        ,'-2' AS PROMO_PARENT_ID-- NEW
        ,'-1' AS SALES_PERSON_ID-- NEW
        ,'-1' AS CASHIER_ID-- NEW
from ori_sales_ctrl
where (SLS_EMP_DISC_AMT_LCL > 0 and SLS_EMP_DISC_AMT_LCL is not null) or (RET_EMP_DISC_AMT_LCL  > 0 and RET_EMP_DISC_AMT_LCL  is not null)
;
COMMIT;