Oracle Retail Knowledge Base

SalesDiscount.sql

2 min read

Source: DMF/PuC/AIF/SalesDiscount.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')
;
 
 
SELECT TO_CHAR(TO_DATE('09-MAY-25', 'DD-MON-RR'), 'YYYY') FROM ori_sales_ctrl WHERE SLS_TRX_ID = 2025050900370000000251

Graph View