Oracle Retail Knowledge Base

sales_discount.sql

3 min read

Source: DMF/PuC/AIF/FelipeBkp/sales_discount.sql

 
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 DISC_TYPE, TO_CHAR(TO_DATE(DAY_DT, 'DD-MON-RR'), 'YYYY'), sum(SLS_DISC_AMT_LCL) as SLS_DISC_AMT_LCL, sum(RET_DISC_AMT_LCL) as RET_DISC_AMT_LCL   from (
--regular discount
select 
        SLS_TRX_ID
        ,ITEM AS PROD_IT_NUM
        ,ORG_NUM
        ,DAY_DT
        ,TRIM(TO_CHAR(MIN_NUM,'0000'))  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
        ,TRIM(TO_CHAR(MIN_NUM,'0000')) 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)
 -- GROUP BY DISC_TYPE, TO_CHAR(TO_DATE(DAY_DT, 'DD-MON-RR'), 'YYYY')
 
 
-- co_head_id is mandatory 
SELECT * FROM ORI_SALES_DISCOUNT_CTRL
WHERE co_head_id IS NULL
 
UPDATE ORI_SALES_DISCOUNT_CTRL
SET co_head_id = '-1'
WHERE co_head_id IS NULL
 
--- must match records on w_promo_d_rtl_tmp
-- select promo_comp_num, promo_parent_num from w+promo_d_rtl_tmp where ptromo_parent_num < 0    --- -3 -2
UPDATE ORI_SALES_DISCOUNT_CTRL
SET promo_comp_id = '-3'
WHERE promo_comp_id IS NULL
 
 
 
UPDATE ORI_SALES_DISCOUNT_CTRL
SET promo_parent_id = '-2'
WHERE promo_comp_id = '-3'
 
 
UPDATE ORI_SALES_DISCOUNT_CTRL
SET COUPON_NUMBER = '-1',
    COUPON_REF_NUMBER = '-1'
WHERE COUPON_NUMBER IS NULL
 
 
 
SELECT 
SLS_TRX_ID
,PROD_IT_NUM
,ORG_NUM
,TO_CHAR(DAY_DT, 'YYYYMMDD') AS DAY_DT
,LPAD(TO_CHAR(MIN_NUM), 4, '0') AS MIN_NUM
,REVISION_NUM
,DISC_TYPE
,COUPON_NUMBER
,COUPON_REF_NUMBER
,CO_HEAD_ID
,CO_LINE_ID
,CUST_REF_TYPE
,CUST_REF_NUMBER
,RTL_TYPE_CODE
,SLS_QTY
,SLS_AMT_LCL
,SLS_DISC_AMT_LCL
,RET_QTY
,RET_AMT_LCL
,RET_DISC_AMT_LCL
,EXCHANGE_DT
,AUX1_CHANGED_ON_DT
,AUX2_CHANGED_ON_DT
,AUX3_CHANGED_ON_DT
,AUX4_CHANGED_ON_DT
,CHANGED_BY_ID
,CHANGED_ON_DT
,CREATED_BY_ID
,CREATED_ON_DT
,DATASOURCE_NUM_ID
,DELETE_FLG
,DOC_CURR_CODE
,ETL_THREAD_VAL
,GLOBAL1_EXCHANGE_RATE
,GLOBAL2_EXCHANGE_RATE
,GLOBAL3_EXCHANGE_RATE
,INTEGRATION_ID
,LOC_CURR_CODE
,LOC_EXCHANGE_RATE
,TENANT_ID
,X_CUSTOM
,X_CUSTOM1
,X_CUSTOM2
,X_CUSTOM3
,X_CUSTOM4
,EMPLOYEE_NUM
,PROMO_COMP_ID
,PROMO_PARENT_ID
,SALES_PERSON_ID
,CASHIER_ID
,FLEX1_CHAR_VALUE
,FLEX2_CHAR_VALUE
,FLEX3_CHAR_VALUE
,FLEX4_CHAR_VALUE
,ORIG_SLS_AMT_LCL
,ORIG_RET_AMT_LCL
,SLS_PROFIT_AMT_LCL
,RET_PROFIT_AMT_LCL
,SLS_RPROF_AMT_LCL
,RET_RPROF_AMT_LCL
,REGISTER_ID
,POS_TRX_ID
FROM ORI_SALES_DISCOUNT_CTRL

Graph View