Source: DMF/PuC/AIF/DR1 scripts/DEAL_INCOME.sql
INSERT INTO ORI_DEALS_HISTORY_REJECTED
(
LOG_LVL,
ERROR_MSG,
ITEM,
ORG_NUM,
DAY_DT,
DEAL_QTY,
DEAL_COST_AMT_LCL,
DEAL_RTL_AMT_LCL,
DEAL_PURCH_QTY,
DEAL_PURCH_COST_AMT_LCL,
DEAL_PURCH_RTL_AMT_LCL,
DELETE_FLG,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
DOC_CURR_CODE,
ETL_THREAD_VAL,
DATASOURCE_NUM_ID,
FLEX1_NUM_VALUE,
FLEX2_NUM_VALUE,
FLEX3_NUM_VALUE,
FLEX4_NUM_VALUE,
FLEX5_NUM_VALUE,
FLEX6_NUM_VALUE,
FLEX7_NUM_VALUE,
FLEX8_NUM_VALUE,
FLEX9_NUM_VALUE,
FLEX10_NUM_VALUE,
FLEX11_NUM_VALUE,
FLEX12_NUM_VALUE,
FLEX13_NUM_VALUE,
FLEX14_NUM_VALUE,
FLEX15_NUM_VALUE,
FLEX16_NUM_VALUE,
FLEX17_NUM_VALUE,
FLEX18_NUM_VALUE,
FLEX19_NUM_VALUE,
FLEX20_NUM_VALUE,
DEAL_ID,
DEAL_FIXED_QTY,
DEAL_FIXED_COST_AMT_LCL,
DEAL_FIXED_RTL_AMT_LCL,
DEAL_ISSUE_QTY,
DEAL_ISSUE_COST_AMT_LCL,
DEAL_ISSUE_RTL_AMT_LCL,
INSERTED_AT
)
WITH src AS (
SELECT
r.ITEM,
LTRIM(r.ORG_NUM,'0') AS ORG_NUM_KEY,
r.DAY_DT,
r.DEAL_PURCH_QTY,
r.DEAL_PURCH_COST_AMT_LCL,
r.COST_VARIANCE_AMOUNT,
r.INVENTORY_AMOUNT,
r.COST_VARIANCE_QTY,
r.INVENTORY_QTY,
r.LOC_CURR_CODE,
r.DOC_CURR_CODE
FROM ORI_DEALS_HISTORY_RAW r
),
/* WH validation (HISTORY) - exatamente como você pediu */
wh_map AS (
SELECT DISTINCT
wc.WH AS WH_FINAL,
LTRIM(TO_CHAR(wx.WH_SLT),'0') AS WH_KEY
FROM wh_ctrl wc, xref_wh wx
WHERE wc.wh = LTRIM(TO_CHAR(wx.WH_SLT),'0')
),
/* dataset no layout do DEAL_INCOME (Excel) */
deal_income_hist AS (
SELECT
s.ITEM,
CASE WHEN w.WH_FINAL IS NOT NULL THEN TO_CHAR(w.WH_FINAL) ELSE s.ORG_NUM_KEY END AS ORG_NUM,
s.DAY_DT,
CAST(NULL AS NUMBER) AS DEAL_QTY,
CAST(NULL AS NUMBER) AS DEAL_COST_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_RTL_AMT_LCL,
s.DEAL_PURCH_QTY,
s.DEAL_PURCH_COST_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_PURCH_RTL_AMT_LCL,
CAST(NULL AS VARCHAR2(1)) AS DELETE_FLG,
CAST(NULL AS NUMBER) AS GLOBAL1_EXCHANGE_RATE,
CAST(NULL AS NUMBER) AS GLOBAL2_EXCHANGE_RATE,
CAST(NULL AS NUMBER) AS GLOBAL3_EXCHANGE_RATE,
s.LOC_CURR_CODE,
CAST(NULL AS NUMBER) AS LOC_EXCHANGE_RATE,
s.DOC_CURR_CODE,
CAST(NULL AS NUMBER) AS ETL_THREAD_VAL,
CAST(NULL AS NUMBER) AS DATASOURCE_NUM_ID,
/* extras -> FLEX1..4 */
s.COST_VARIANCE_AMOUNT AS FLEX1_NUM_VALUE,
s.INVENTORY_AMOUNT AS FLEX2_NUM_VALUE,
s.COST_VARIANCE_QTY AS FLEX3_NUM_VALUE,
s.INVENTORY_QTY AS FLEX4_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX5_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX6_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX7_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX8_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX9_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX10_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX11_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX12_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX13_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX14_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX15_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX16_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX17_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX18_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX19_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX20_NUM_VALUE,
CAST(NULL AS VARCHAR2(30)) AS DEAL_ID,
CAST(NULL AS NUMBER) AS DEAL_FIXED_QTY,
CAST(NULL AS NUMBER) AS DEAL_FIXED_COST_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_FIXED_RTL_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_ISSUE_QTY,
CAST(NULL AS NUMBER) AS DEAL_ISSUE_COST_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_ISSUE_RTL_AMT_LCL
FROM src s
LEFT JOIN wh_map w
ON w.WH_KEY = s.ORG_NUM_KEY
),
/* =========================================================
VALIDATIONS (igual DMF)
========================================================= */
-- mandatory
err_null AS (
SELECT 'E' AS LOG_LVL, 'ERROR_NULL' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM deal_income_hist s
WHERE s.ITEM IS NULL OR s.ORG_NUM IS NULL OR s.DAY_DT IS NULL
),
-- duplicate (usando a chave que você está usando na prática)
dup_keys AS (
SELECT ITEM, ORG_NUM, DAY_DT, COUNT(*) cnt
FROM deal_income_hist
GROUP BY ITEM, ORG_NUM, DAY_DT
HAVING COUNT(*) > 1
),
err_dup AS (
SELECT 'E' AS LOG_LVL, 'ERROR_DUPLICATE' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM deal_income_hist s
JOIN dup_keys d
ON s.ITEM = d.ITEM
AND s.ORG_NUM = d.ORG_NUM
AND s.DAY_DT = d.DAY_DT
),
-- item exists
err_item AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_ITEM' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM deal_income_hist s
WHERE s.ITEM IS NOT NULL
AND s.ITEM NOT IN (SELECT ITEM FROM ITEM_MASTER_CTRL WHERE CTRL_STATUS='C')
),
-- org exists (store OR wh by your rule)
err_org AS (
SELECT 'E' AS LOG_LVL, 'ERROR_INVALID_ORG' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM deal_income_hist s
WHERE s.ORG_NUM IS NOT NULL
AND s.ORG_NUM NOT IN (SELECT TO_CHAR(store) FROM STORE_ADD_CTRL WHERE CTRL_STATUS='C')
AND s.ORG_NUM NOT IN (
SELECT TO_CHAR(wc.WH)
FROM wh_ctrl wc, xref_wh wx
WHERE wc.wh = LTRIM(TO_CHAR(wx.WH_SLT),'0')
)
),
/* =========================================================
DTYPE / FORMATTING (baseado no DEAL_INCOME.xlsx)
- ITEM VARCHAR2(80)
- ORG_NUM VARCHAR2(30)
- DAY_DT DATE (sem hora)
- LOC_CURR_CODE VARCHAR2(30)
- DOC_CURR_CODE VARCHAR2(30)
- DEAL_ID VARCHAR2(30)
- DEAL_PURCH_QTY / DEAL_PURCH_COST_AMT_LCL: NUMBER(30,4)
- FLEX1..4: NUMBER(18,4)
========================================================= */
err_dtype AS (
SELECT 'E' AS LOG_LVL,'ERROR_DTYPE_ITEM' AS ERROR_MSG,s.*,SYSDATE AS INSERTED_AT
FROM deal_income_hist s
WHERE s.ITEM IS NOT NULL AND LENGTHB(TO_CHAR(s.ITEM)) > 80
UNION ALL
SELECT 'E','ERROR_DTYPE_ORG_NUM',s.*,SYSDATE
FROM deal_income_hist s
WHERE s.ORG_NUM IS NOT NULL AND LENGTHB(TO_CHAR(s.ORG_NUM)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_DAY_DT',s.*,SYSDATE
FROM deal_income_hist s
WHERE s.DAY_DT IS NOT NULL AND TRUNC(s.DAY_DT) <> s.DAY_DT
UNION ALL
SELECT 'E','ERROR_DTYPE_LOC_CURR_CODE',s.*,SYSDATE
FROM deal_income_hist s
WHERE s.LOC_CURR_CODE IS NOT NULL AND LENGTHB(TO_CHAR(s.LOC_CURR_CODE)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_DOC_CURR_CODE',s.*,SYSDATE
FROM deal_income_hist s
WHERE s.DOC_CURR_CODE IS NOT NULL AND LENGTHB(TO_CHAR(s.DOC_CURR_CODE)) > 30
UNION ALL
SELECT 'E','ERROR_DTYPE_DEAL_ID',s.*,SYSDATE
FROM deal_income_hist s
WHERE s.DEAL_ID IS NOT NULL AND LENGTHB(TO_CHAR(s.DEAL_ID)) > 30
/* NUMBER(30,4) => inteiro até 10^(26) - 1 e escala 4 */
UNION ALL
SELECT 'E','ERROR_DTYPE_DEAL_PURCH_QTY',s.*,SYSDATE
FROM deal_income_hist s
WHERE s.DEAL_PURCH_QTY IS NOT NULL
AND ( ABS(TRUNC(s.DEAL_PURCH_QTY)) >= POWER(10,26)
OR s.DEAL_PURCH_QTY <> ROUND(s.DEAL_PURCH_QTY,4) )
UNION ALL
SELECT 'E','ERROR_DTYPE_DEAL_PURCH_COST_AMT_LCL',s.*,SYSDATE
FROM deal_income_hist s
WHERE s.DEAL_PURCH_COST_AMT_LCL IS NOT NULL
AND ( ABS(TRUNC(s.DEAL_PURCH_COST_AMT_LCL)) >= POWER(10,26)
OR s.DEAL_PURCH_COST_AMT_LCL <> ROUND(s.DEAL_PURCH_COST_AMT_LCL,4) )
/* FLEXn NUMBER(18,4) => inteiro até 10^(14) - 1 e escala 4 */
UNION ALL
SELECT 'E','ERROR_DTYPE_FLEX1_NUM_VALUE',s.*,SYSDATE
FROM deal_income_hist s
WHERE s.FLEX1_NUM_VALUE IS NOT NULL
AND ( ABS(TRUNC(s.FLEX1_NUM_VALUE)) >= POWER(10,14)
OR s.FLEX1_NUM_VALUE <> ROUND(s.FLEX1_NUM_VALUE,4) )
UNION ALL
SELECT 'E','ERROR_DTYPE_FLEX2_NUM_VALUE',s.*,SYSDATE
FROM deal_income_hist s
WHERE s.FLEX2_NUM_VALUE IS NOT NULL
AND ( ABS(TRUNC(s.FLEX2_NUM_VALUE)) >= POWER(10,14)
OR s.FLEX2_NUM_VALUE <> ROUND(s.FLEX2_NUM_VALUE,4) )
UNION ALL
SELECT 'E','ERROR_DTYPE_FLEX3_NUM_VALUE',s.*,SYSDATE
FROM deal_income_hist s
WHERE s.FLEX3_NUM_VALUE IS NOT NULL
AND ( ABS(TRUNC(s.FLEX3_NUM_VALUE)) >= POWER(10,14)
OR s.FLEX3_NUM_VALUE <> ROUND(s.FLEX3_NUM_VALUE,4) )
UNION ALL
SELECT 'E','ERROR_DTYPE_FLEX4_NUM_VALUE',s.*,SYSDATE
FROM deal_income_hist s
WHERE s.FLEX4_NUM_VALUE IS NOT NULL
AND ( ABS(TRUNC(s.FLEX4_NUM_VALUE)) >= POWER(10,14)
OR s.FLEX4_NUM_VALUE <> ROUND(s.FLEX4_NUM_VALUE,4) )
),
all_err AS (
SELECT * FROM err_null
UNION ALL SELECT * FROM err_dup
UNION ALL SELECT * FROM err_item
UNION ALL SELECT * FROM err_org
UNION ALL SELECT * FROM err_dtype
)
SELECT
LOG_LVL,
ERROR_MSG,
ITEM,
ORG_NUM,
DAY_DT,
DEAL_QTY,
DEAL_COST_AMT_LCL,
DEAL_RTL_AMT_LCL,
DEAL_PURCH_QTY,
DEAL_PURCH_COST_AMT_LCL,
DEAL_PURCH_RTL_AMT_LCL,
DELETE_FLG,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
DOC_CURR_CODE,
ETL_THREAD_VAL,
DATASOURCE_NUM_ID,
FLEX1_NUM_VALUE,
FLEX2_NUM_VALUE,
FLEX3_NUM_VALUE,
FLEX4_NUM_VALUE,
FLEX5_NUM_VALUE,
FLEX6_NUM_VALUE,
FLEX7_NUM_VALUE,
FLEX8_NUM_VALUE,
FLEX9_NUM_VALUE,
FLEX10_NUM_VALUE,
FLEX11_NUM_VALUE,
FLEX12_NUM_VALUE,
FLEX13_NUM_VALUE,
FLEX14_NUM_VALUE,
FLEX15_NUM_VALUE,
FLEX16_NUM_VALUE,
FLEX17_NUM_VALUE,
FLEX18_NUM_VALUE,
FLEX19_NUM_VALUE,
FLEX20_NUM_VALUE,
DEAL_ID,
DEAL_FIXED_QTY,
DEAL_FIXED_COST_AMT_LCL,
DEAL_FIXED_RTL_AMT_LCL,
DEAL_ISSUE_QTY,
DEAL_ISSUE_COST_AMT_LCL,
DEAL_ISSUE_RTL_AMT_LCL,
INSERTED_AT
FROM all_err;
COMMIT;
------------------------------------------------------------------------
------------------------------------------------------------------------
--- LOAD STAGE---------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
INSERT INTO ORI_DEAL_INCOME_CTRL
(
ITEM,
ORG_NUM,
DAY_DT,
DEAL_QTY,
DEAL_COST_AMT_LCL,
DEAL_RTL_AMT_LCL,
DEAL_PURCH_QTY,
DEAL_PURCH_COST_AMT_LCL,
DEAL_PURCH_RTL_AMT_LCL,
DELETE_FLG,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
DOC_CURR_CODE,
ETL_THREAD_VAL,
DATASOURCE_NUM_ID,
FLEX1_NUM_VALUE,
FLEX2_NUM_VALUE,
FLEX3_NUM_VALUE,
FLEX4_NUM_VALUE,
FLEX5_NUM_VALUE,
FLEX6_NUM_VALUE,
FLEX7_NUM_VALUE,
FLEX8_NUM_VALUE,
FLEX9_NUM_VALUE,
FLEX10_NUM_VALUE,
FLEX11_NUM_VALUE,
FLEX12_NUM_VALUE,
FLEX13_NUM_VALUE,
FLEX14_NUM_VALUE,
FLEX15_NUM_VALUE,
FLEX16_NUM_VALUE,
FLEX17_NUM_VALUE,
FLEX18_NUM_VALUE,
FLEX19_NUM_VALUE,
FLEX20_NUM_VALUE,
DEAL_ID,
DEAL_FIXED_QTY,
DEAL_FIXED_COST_AMT_LCL,
DEAL_FIXED_RTL_AMT_LCL,
DEAL_ISSUE_QTY,
DEAL_ISSUE_COST_AMT_LCL,
DEAL_ISSUE_RTL_AMT_LCL,
CREATE_ID,
CREATE_DATETIME
)
WITH src AS (
SELECT
r.ITEM,
LTRIM(r.ORG_NUM,'0') AS ORG_NUM_KEY,
r.DAY_DT,
r.DEAL_PURCH_QTY,
r.DEAL_PURCH_COST_AMT_LCL,
r.COST_VARIANCE_AMOUNT,
r.INVENTORY_AMOUNT,
r.COST_VARIANCE_QTY,
r.INVENTORY_QTY,
r.LOC_CURR_CODE,
r.DOC_CURR_CODE
FROM ORI_DEALS_HISTORY_RAW r
),
/* WH validation for history (your rule) */
wh_map AS (
SELECT DISTINCT
wc.WH AS WH_FINAL,
LTRIM(TO_CHAR(wx.WH_SLT),'0') AS WH_KEY
FROM wh_ctrl wc, xref_wh wx
WHERE wc.wh = LTRIM(TO_CHAR(wx.WH_SLT),'0')
),
deal_income_hist AS (
SELECT
s.ITEM,
CASE WHEN w.WH_FINAL IS NOT NULL THEN TO_CHAR(w.WH_FINAL) ELSE s.ORG_NUM_KEY END AS ORG_NUM,
s.DAY_DT,
CAST(NULL AS NUMBER) AS DEAL_QTY,
CAST(NULL AS NUMBER) AS DEAL_COST_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_RTL_AMT_LCL,
s.DEAL_PURCH_QTY,
s.DEAL_PURCH_COST_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_PURCH_RTL_AMT_LCL,
CAST(NULL AS VARCHAR2(1)) AS DELETE_FLG,
CAST(NULL AS NUMBER) AS GLOBAL1_EXCHANGE_RATE,
CAST(NULL AS NUMBER) AS GLOBAL2_EXCHANGE_RATE,
CAST(NULL AS NUMBER) AS GLOBAL3_EXCHANGE_RATE,
s.LOC_CURR_CODE,
CAST(NULL AS NUMBER) AS LOC_EXCHANGE_RATE,
s.DOC_CURR_CODE,
CAST(NULL AS NUMBER) AS ETL_THREAD_VAL,
CAST(NULL AS NUMBER) AS DATASOURCE_NUM_ID,
/* extras -> FLEX */
s.COST_VARIANCE_AMOUNT AS FLEX1_NUM_VALUE,
s.INVENTORY_AMOUNT AS FLEX2_NUM_VALUE,
s.COST_VARIANCE_QTY AS FLEX3_NUM_VALUE,
s.INVENTORY_QTY AS FLEX4_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX5_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX6_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX7_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX8_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX9_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX10_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX11_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX12_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX13_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX14_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX15_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX16_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX17_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX18_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX19_NUM_VALUE,
CAST(NULL AS NUMBER) AS FLEX20_NUM_VALUE,
CAST(NULL AS VARCHAR2(30)) AS DEAL_ID,
CAST(NULL AS NUMBER) AS DEAL_FIXED_QTY,
CAST(NULL AS NUMBER) AS DEAL_FIXED_COST_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_FIXED_RTL_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_ISSUE_QTY,
CAST(NULL AS NUMBER) AS DEAL_ISSUE_COST_AMT_LCL,
CAST(NULL AS NUMBER) AS DEAL_ISSUE_RTL_AMT_LCL
FROM src s
LEFT JOIN wh_map w
ON w.WH_KEY = s.ORG_NUM_KEY
),
/* duplicates to exclude */
dup_keys AS (
SELECT ITEM, ORG_NUM, DAY_DT
FROM deal_income_hist
GROUP BY ITEM, ORG_NUM, DAY_DT
HAVING COUNT(*) > 1
)
SELECT
h.ITEM,
h.ORG_NUM,
h.DAY_DT,
h.DEAL_QTY,
h.DEAL_COST_AMT_LCL,
h.DEAL_RTL_AMT_LCL,
h.DEAL_PURCH_QTY,
h.DEAL_PURCH_COST_AMT_LCL,
h.DEAL_PURCH_RTL_AMT_LCL,
h.DELETE_FLG,
h.GLOBAL1_EXCHANGE_RATE,
h.GLOBAL2_EXCHANGE_RATE,
h.GLOBAL3_EXCHANGE_RATE,
h.LOC_CURR_CODE,
h.LOC_EXCHANGE_RATE,
h.DOC_CURR_CODE,
h.ETL_THREAD_VAL,
h.DATASOURCE_NUM_ID,
h.FLEX1_NUM_VALUE,
h.FLEX2_NUM_VALUE,
h.FLEX3_NUM_VALUE,
h.FLEX4_NUM_VALUE,
h.FLEX5_NUM_VALUE,
h.FLEX6_NUM_VALUE,
h.FLEX7_NUM_VALUE,
h.FLEX8_NUM_VALUE,
h.FLEX9_NUM_VALUE,
h.FLEX10_NUM_VALUE,
h.FLEX11_NUM_VALUE,
h.FLEX12_NUM_VALUE,
h.FLEX13_NUM_VALUE,
h.FLEX14_NUM_VALUE,
h.FLEX15_NUM_VALUE,
h.FLEX16_NUM_VALUE,
h.FLEX17_NUM_VALUE,
h.FLEX18_NUM_VALUE,
h.FLEX19_NUM_VALUE,
h.FLEX20_NUM_VALUE,
h.DEAL_ID,
h.DEAL_FIXED_QTY,
h.DEAL_FIXED_COST_AMT_LCL,
h.DEAL_FIXED_RTL_AMT_LCL,
h.DEAL_ISSUE_QTY,
h.DEAL_ISSUE_COST_AMT_LCL,
h.DEAL_ISSUE_RTL_AMT_LCL,
'DMF' AS CREATE_ID,
SYSDATE AS CREATE_DATETIME
FROM deal_income_hist h
WHERE
/* 1) mandatory */
h.ITEM IS NOT NULL
AND h.ORG_NUM IS NOT NULL
AND h.DAY_DT IS NOT NULL
/* 2) no duplicates */
AND NOT EXISTS (
SELECT 1
FROM dup_keys d
WHERE d.ITEM = h.ITEM
AND d.ORG_NUM = h.ORG_NUM
AND d.DAY_DT = h.DAY_DT
)
/* 3) item exists */
AND h.ITEM IN (SELECT ITEM FROM ITEM_MASTER_CTRL WHERE CTRL_STATUS='C')
/* 4) org exists (store OR wh per your rule) */
AND (
h.ORG_NUM IN (SELECT TO_CHAR(store) FROM STORE_ADD_CTRL WHERE CTRL_STATUS='C')
OR h.ORG_NUM IN (
SELECT TO_CHAR(wc.WH)
FROM wh_ctrl wc, xref_wh wx
WHERE wc.wh = LTRIM(TO_CHAR(wx.WH_SLT),'0')
)
)
/* 5) dtype/format validations (deal_income) */
AND LENGTHB(TO_CHAR(h.ITEM)) <= 80
AND LENGTHB(TO_CHAR(h.ORG_NUM)) <= 30
AND TRUNC(h.DAY_DT) = h.DAY_DT
AND (h.LOC_CURR_CODE IS NULL OR LENGTHB(TO_CHAR(h.LOC_CURR_CODE)) <= 30)
AND (h.DOC_CURR_CODE IS NULL OR LENGTHB(TO_CHAR(h.DOC_CURR_CODE)) <= 30)
AND (h.DEAL_ID IS NULL OR LENGTHB(TO_CHAR(h.DEAL_ID)) <= 30)
/* NUMBER(30,4) checks */
AND (h.DEAL_PURCH_QTY IS NULL
OR (ABS(TRUNC(h.DEAL_PURCH_QTY)) < POWER(10,26) AND h.DEAL_PURCH_QTY = ROUND(h.DEAL_PURCH_QTY,4)))
AND (h.DEAL_PURCH_COST_AMT_LCL IS NULL
OR (ABS(TRUNC(h.DEAL_PURCH_COST_AMT_LCL)) < POWER(10,26) AND h.DEAL_PURCH_COST_AMT_LCL = ROUND(h.DEAL_PURCH_COST_AMT_LCL,4)))
/* FLEX NUMBER(18,4) checks for flex1..4 */
AND (h.FLEX1_NUM_VALUE IS NULL
OR (ABS(TRUNC(h.FLEX1_NUM_VALUE)) < POWER(10,14) AND h.FLEX1_NUM_VALUE = ROUND(h.FLEX1_NUM_VALUE,4)))
AND (h.FLEX2_NUM_VALUE IS NULL
OR (ABS(TRUNC(h.FLEX2_NUM_VALUE)) < POWER(10,14) AND h.FLEX2_NUM_VALUE = ROUND(h.FLEX2_NUM_VALUE,4)))
AND (h.FLEX3_NUM_VALUE IS NULL
OR (ABS(TRUNC(h.FLEX3_NUM_VALUE)) < POWER(10,14) AND h.FLEX3_NUM_VALUE = ROUND(h.FLEX3_NUM_VALUE,4)))
AND (h.FLEX4_NUM_VALUE IS NULL
OR (ABS(TRUNC(h.FLEX4_NUM_VALUE)) < POWER(10,14) AND h.FLEX4_NUM_VALUE = ROUND(h.FLEX4_NUM_VALUE,4)));
COMMIT;
SELECT * FROM ORI_DEAL_INCOME_STG;
SELECT
(SELECT COUNT(*) FROM ORI_DEALS_HISTORY_RAW) AS RAW_CNT,
(SELECT COUNT(*) FROM ORI_DEAL_INCOME_STG) AS STG_CNT,
(SELECT COUNT(*) FROM ORI_DEALS_HISTORY_REJECTED) AS REJECTED_CNT,
(SELECT COUNT(*) FROM ORI_DEAL_INCOME_CTRL) AS CTRL_CNT
FROM dual;
SELECT
ERROR_MSG,
COUNT(*) AS QTD
FROM ORI_DEALS_HISTORY_REJECTED
GROUP BY ERROR_MSG
ORDER BY QTD DESC;
SELECT *
FROM ORI_DEALS_HISTORY_REJECTED
FETCH FIRST 50 ROWS ONLY;
SELECT MIN(DAY_DT), MAX(DAY_DT) FROM ORI_DEAL_INCOME_CTRL