Sales.sql

Source: DMF/PuC/AIF/Sales.sql

select count(1) from ori_sales_tmp; -- 64489743
select count(1) from ori_sales_ctrl; --64489743
select * from ori_sales_ctrl where SLSPR_DISC_AMT_LCL > 0 and SLS_EMP_DISC_AMT_LCL > 0; --64489743
 
select count(1) from (
select distinct org_num from ori_sales_raw); --191
 
select count(1) from ori_sales_raw;
 
select distinct item from ori_sales_raw where promo_id is not null
 
ALTER SESSION DISABLE PARALLEL DML;
 
truncate table ORI_SALES_DISCOUNT_CTRL;
select count(1) from ORI_SALES_DISCOUNT_CTRL; --64489743
 
 
INSERT INTO ORI_SALES_DISCOUNT_CTRL 
    (SLS_TRX_ID
        ,PROD_IT_NUM
        ,ORG_NUM
        ,DAY_DT
        ,MIN_NUM -- precisa formatar???
        ,REVISION_NUM
        ,DISC_TYPE  -- for the future this field must be reviewed
        ,COUPON_NUMBER -- validar campo mandatorio
        ,COUPON_REF_NUMBER -- validar campo mandatorio
        ,CO_HEAD_ID
        ,CO_LINE_ID
        ,CUST_REF_TYPE
        ,CUST_REF_NUMBER
        ,RTL_TYPE_CODE
        ,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
        ,INTEGRATION_ID -- REVISAR SE ESTÁ CORRETO (TEM QUE SER UNICO POR LINHA NA TABELA DE DISCOUNT?)
        ,LOC_CURR_CODE
    ) 
select SLS_TRX_ID
        ,PROD_IT_NUM
        ,ORG_NUM
        ,DAY_DT
        ,MIN_NUM -- precisa formatar???
        ,REVISION_NUM
        ,DISC_TYPE  -- for the future this field must be reviewed
        ,COUPON_NUMBER -- validar campo mandatorio
        ,COUPON_REF_NUMBER -- validar campo mandatorio
        ,CO_HEAD_ID
        ,CO_LINE_ID
        ,CUST_REF_TYPE
        ,CUST_REF_NUMBER
        ,RTL_TYPE_CODE
        ,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
        ,INTEGRATION_ID -- REVISAR SE ESTÁ CORRETO (TEM QUE SER UNICO POR LINHA NA TABELA DE DISCOUNT?)
        ,LOC_CURR_CODE from (
--regular discount
select 
        SLS_TRX_ID
        ,ITEM AS PROD_IT_NUM
        ,ORG_NUM
        ,DAY_DT
        ,LPAD(MIN_NUM, 2, '0') AS MIN_NUM -- precisa formatar???
        ,1 as REVISION_NUM
        ,'NEWPRC' AS DISC_TYPE  -- for the future this field must be reviewes
        , NULL AS COUPON_NUMBER -- validar campo mandatorio
        , NULL AS COUPON_REF_NUMBER -- validar campo mandatorio
        ,CO_HEAD_ID
        ,CO_LINE_ID
        ,CUSTOMER_TYPE AS CUST_REF_TYPE
        ,CUSTOMER_NUM AS CUST_REF_NUMBER
        ,RTL_TYPE_CODE
        ,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
        ,SLS_TRX_ID || '~' || DAY_DT || '~' || ORG_NUM  || '~' || ITEM AS INTEGRATION_ID -- REVISAR SE ESTÁ CORRETO (TEM QUE SER UNICO POR LINHA NA TABELA DE DISCOUNT?)
        ,LOC_CURR_CODE
  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
        , NULL 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
        ,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
        ,SLS_TRX_ID || '~' || DAY_DT || '~' || ORG_NUM  || '~' || ITEM AS INTEGRATION_ID -- REVISAR SE ESTÁ CORRETO (TEM QUE SER UNICO POR LINHA NA TABELA DE DISCOUNT?)
        ,LOC_CURR_CODE
  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)
)