Source: DMF/PuC/AIF/DR1 scripts/DMF_SALES_TENDER_VALIDATIONS.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Truncate validation tables
------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRUNCATE TABLE ORI_SALES_TENDER_REJECT;
TRUNCATE TABLE ORI_SALES_TENDER_TMP;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- (A) MATERIALIZE SOURCE ONCE (STAGE)
-- Isto evita recalcular tndr_src N vezes e evita ler RAW 2x (reject + tmp)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ORI_SALES_TENDER_STAGE PURGE';
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
CREATE TABLE ORI_SALES_TENDER_STAGE
NOLOGGING
PARALLEL 8
AS
SELECT /*+ PARALLEL(r 8) */
r.SLS_TRX_ID AS SLS_TRX_ID,
x.TENDER_TYPE_ID_ORACLE AS TENDER_TYPE_ID,
LTRIM(r.ORG_NUM, '0') AS ORG_NUM,
r.DAY_DT AS DAY_DT,
r.REVISION_NUM AS REVISION_NUM,
x.TENDER_TYPE_GROUP_ORACLE AS TENDER_TYPE_GROUP,
'-1' AS CASHIER_ID,
NVL(LTRIM(r.REGISTER_ID, '0'), '0') AS REGISTER_ID,
r.VOUCHER_NUM AS VOUCHER_NUM,
r.VOUCHER_AGE AS VOUCHER_AGE,
r.COUPON_NUM AS COUPON_NUM,
r.COUPON_REF_NUM AS COUPON_REF_NUM,
r.TNDR_SLS_AMT_LCL AS TNDR_SLS_AMT_LCL,
r.TNDR_RET_AMT_LCL AS TNDR_RET_AMT_LCL,
r.EXCHANGE_DT AS EXCHANGE_DT,
r.AUX1_CHANGED_ON_DT AS AUX1_CHANGED_ON_DT,
r.AUX2_CHANGED_ON_DT AS AUX2_CHANGED_ON_DT,
r.AUX3_CHANGED_ON_DT AS AUX3_CHANGED_ON_DT,
r.AUX4_CHANGED_ON_DT AS AUX4_CHANGED_ON_DT,
r.CHANGED_BY_ID AS CHANGED_BY_ID,
r.CHANGED_ON_DT AS CHANGED_ON_DT,
r.CREATED_BY_ID AS CREATED_BY_ID,
r.CREATED_ON_DT AS CREATED_ON_DT,
1 AS DATASOURCE_NUM_ID,
r.DELETE_FLG AS DELETE_FLG,
r.DOC_CURR_CODE AS DOC_CURR_CODE,
r.ETL_THREAD_VAL AS ETL_THREAD_VAL,
r.GLOBAL1_EXCHANGE_RATE AS GLOBAL1_EXCHANGE_RATE,
r.GLOBAL2_EXCHANGE_RATE AS GLOBAL2_EXCHANGE_RATE,
r.GLOBAL3_EXCHANGE_RATE AS GLOBAL3_EXCHANGE_RATE,
r.SLS_TRX_ID || '~'
|| NVL(TO_CHAR(x.TENDER_TYPE_ID_ORACLE), 'NOXREF') || '~'
|| LTRIM(r.ORG_NUM, '0') || '~'
|| TO_CHAR(r.DAY_DT,'YYYYMMDD') AS INTEGRATION_ID,
'EUR' AS LOC_CURR_CODE,
r.LOC_EXCHANGE_RATE AS LOC_EXCHANGE_RATE,
r.TENANT_ID AS TENANT_ID,
r.X_CUSTOM AS X_CUSTOM,
r.FLEX1_CHAR_VALUE AS FLEX1_CHAR_VALUE,
r.FLEX2_CHAR_VALUE AS FLEX2_CHAR_VALUE,
r.FLEX3_CHAR_VALUE AS FLEX3_CHAR_VALUE,
r.FLEX4_CHAR_VALUE AS FLEX4_CHAR_VALUE,
CAST(NULL AS DATE) AS UPDATED_AT,
/* Auxiliares para valiações */
CASE
WHEN NVL(r.TNDR_SLS_AMT_LCL,0) <> 0 AND NVL(r.TNDR_RET_AMT_LCL,0) = 0 THEN 'SALE'
WHEN NVL(r.TNDR_RET_AMT_LCL,0) <> 0 AND NVL(r.TNDR_SLS_AMT_LCL,0) = 0 THEN 'RETURN'
ELSE NULL
END AS EXPECTED_TRAN_TYPE,
/* DUP COUNT (substitui duplicate_keys GROUP BY) */
COUNT(*) OVER (
PARTITION BY
r.SLS_TRX_ID || '~'
|| NVL(TO_CHAR(x.TENDER_TYPE_ID_ORACLE), 'NOXREF') || '~'
|| LTRIM(r.ORG_NUM, '0') || '~'
|| TO_CHAR(r.DAY_DT,'YYYYMMDD')
) AS DUP_CNT
FROM ORI_SALES_TENDER_RAW r
LEFT JOIN XREF_TENDER_TYPE x
ON x.TENDER_TYPE_ID_SAP = r.TENDER_TYPE_ID
;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- (B) INSERT REJECTED RECORDS TO ORI_SALES_TENDER_REJECT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ORI_SALES_TENDER_REJECT (
LOG_LVL, ERROR_MSG,
SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT, REVISION_NUM,
TENDER_TYPE_GROUP, CASHIER_ID, REGISTER_ID,
VOUCHER_NUM, VOUCHER_AGE, COUPON_NUM, COUPON_REF_NUM,
TNDR_SLS_AMT_LCL, TNDR_RET_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,
FLEX1_CHAR_VALUE, FLEX2_CHAR_VALUE, FLEX3_CHAR_VALUE, FLEX4_CHAR_VALUE,
UPDATED_AT, INSERTED_AT
)
WITH
tndr_src AS (
SELECT * FROM ORI_SALES_TENDER_STAGE
),
---------------------------------------------------------------------------------
-- 2) RAP formatting data types
---------------------------------------------------------------------------------
error_rows_formatting_dtype AS (
SELECT 'E' AS LOG_LVL,'ERROR_LEN_SLS_TRX_ID' AS ERROR_MSG, r.*, SYSDATE AS INSERTED_AT FROM tndr_src r WHERE r.SLS_TRX_ID IS NOT NULL AND LENGTHB(r.SLS_TRX_ID) > 30
UNION ALL SELECT 'E','ERROR_LEN_TENDER_TYPE_ID', r.*, SYSDATE FROM tndr_src r WHERE r.TENDER_TYPE_ID IS NOT NULL AND LENGTHB(r.TENDER_TYPE_ID) > 30
UNION ALL SELECT 'E','ERROR_LEN_ORG_NUM', r.*, SYSDATE FROM tndr_src r WHERE r.ORG_NUM IS NOT NULL AND LENGTHB(r.ORG_NUM) > 80
UNION ALL SELECT 'E','ERROR_LEN_TENDER_TYPE_GROUP', r.*, SYSDATE FROM tndr_src r WHERE r.TENDER_TYPE_GROUP IS NOT NULL AND LENGTHB(r.TENDER_TYPE_GROUP) > 6
UNION ALL SELECT 'E','ERROR_LEN_REGISTER_ID', r.*, SYSDATE FROM tndr_src r WHERE r.REGISTER_ID IS NOT NULL AND LENGTHB(r.REGISTER_ID) > 5
UNION ALL SELECT 'E','ERROR_LEN_VOUCHER_NUM', r.*, SYSDATE FROM tndr_src r WHERE r.VOUCHER_NUM IS NOT NULL AND LENGTHB(r.VOUCHER_NUM) > 25
UNION ALL SELECT 'E','ERROR_LEN_COUPON_NUM', r.*, SYSDATE FROM tndr_src r WHERE r.COUPON_NUM IS NOT NULL AND LENGTHB(r.COUPON_NUM) > 40
UNION ALL SELECT 'E','ERROR_LEN_COUPON_REF_NUM', r.*, SYSDATE FROM tndr_src r WHERE r.COUPON_REF_NUM IS NOT NULL AND LENGTHB(r.COUPON_REF_NUM) > 16
UNION ALL SELECT 'E','ERROR_LEN_CHANGED_BY_ID', r.*, SYSDATE FROM tndr_src r WHERE r.CHANGED_BY_ID IS NOT NULL AND LENGTHB(r.CHANGED_BY_ID) > 80
UNION ALL SELECT 'E','ERROR_LEN_CREATED_BY_ID', r.*, SYSDATE FROM tndr_src r WHERE r.CREATED_BY_ID IS NOT NULL AND LENGTHB(r.CREATED_BY_ID) > 80
UNION ALL SELECT 'E','ERROR_LEN_DELETE_FLG', r.*, SYSDATE FROM tndr_src r WHERE r.DELETE_FLG IS NOT NULL AND LENGTHB(r.DELETE_FLG) > 1
UNION ALL SELECT 'E','ERROR_LEN_DOC_CURR_CODE', r.*, SYSDATE FROM tndr_src r WHERE r.DOC_CURR_CODE IS NOT NULL AND LENGTHB(r.DOC_CURR_CODE) > 30
UNION ALL SELECT 'E','ERROR_LEN_INTEGRATION_ID', r.*, SYSDATE FROM tndr_src r WHERE r.INTEGRATION_ID IS NOT NULL AND LENGTHB(r.INTEGRATION_ID) > 80
UNION ALL SELECT 'E','ERROR_LEN_TENANT_ID', r.*, SYSDATE FROM tndr_src r WHERE r.TENANT_ID IS NOT NULL AND LENGTHB(r.TENANT_ID) > 80
UNION ALL SELECT 'E','ERROR_LEN_X_CUSTOM', r.*, SYSDATE FROM tndr_src r WHERE r.X_CUSTOM IS NOT NULL AND LENGTHB(r.X_CUSTOM) > 10
UNION ALL SELECT 'E','ERROR_LEN_FLEX1', r.*, SYSDATE FROM tndr_src r WHERE r.FLEX1_CHAR_VALUE IS NOT NULL AND LENGTHB(r.FLEX1_CHAR_VALUE) > 80
UNION ALL SELECT 'E','ERROR_LEN_FLEX2', r.*, SYSDATE FROM tndr_src r WHERE r.FLEX2_CHAR_VALUE IS NOT NULL AND LENGTHB(r.FLEX2_CHAR_VALUE) > 80
UNION ALL SELECT 'E','ERROR_LEN_FLEX3', r.*, SYSDATE FROM tndr_src r WHERE r.FLEX3_CHAR_VALUE IS NOT NULL AND LENGTHB(r.FLEX3_CHAR_VALUE) > 80
UNION ALL SELECT 'E','ERROR_LEN_FLEX4', r.*, SYSDATE FROM tndr_src r WHERE r.FLEX4_CHAR_VALUE IS NOT NULL AND LENGTHB(r.FLEX4_CHAR_VALUE) > 80
UNION ALL SELECT 'E','ERROR_REVISION_NUM_DTYPE', r.*, SYSDATE FROM tndr_src r WHERE r.REVISION_NUM IS NOT NULL AND (r.REVISION_NUM <> TRUNC(r.REVISION_NUM) OR ABS(r.REVISION_NUM) >= 1000)
UNION ALL SELECT 'E','ERROR_DATASOURCE_NUM_ID_DTYPE', r.*, SYSDATE FROM tndr_src r WHERE r.DATASOURCE_NUM_ID IS NOT NULL AND (r.DATASOURCE_NUM_ID <> TRUNC(r.DATASOURCE_NUM_ID) OR ABS(r.DATASOURCE_NUM_ID) >= POWER(10,10))
UNION ALL SELECT 'E','ERROR_ETL_THREAD_VAL_DTYPE', r.*, SYSDATE FROM tndr_src r WHERE r.ETL_THREAD_VAL IS NOT NULL AND (r.ETL_THREAD_VAL <> TRUNC(r.ETL_THREAD_VAL) OR ABS(r.ETL_THREAD_VAL) >= POWER(10,4))
UNION ALL SELECT 'E','ERROR_TNDR_SLS_AMT_LCL_DTYPE', r.*, SYSDATE FROM tndr_src r WHERE r.TNDR_SLS_AMT_LCL IS NOT NULL AND (ABS(r.TNDR_SLS_AMT_LCL - TRUNC(r.TNDR_SLS_AMT_LCL, 4)) > 0 OR ABS(r.TNDR_SLS_AMT_LCL) >= POWER(10,14))
UNION ALL SELECT 'E','ERROR_TNDR_RET_AMT_LCL_DTYPE', r.*, SYSDATE FROM tndr_src r WHERE r.TNDR_RET_AMT_LCL IS NOT NULL AND (ABS(r.TNDR_RET_AMT_LCL - TRUNC(r.TNDR_RET_AMT_LCL, 4)) > 0 OR ABS(r.TNDR_RET_AMT_LCL) >= POWER(10,14))
UNION ALL SELECT 'E','ERROR_GLOBAL1_EXCHANGE_RATE_DTYPE', r.*, SYSDATE FROM tndr_src r WHERE r.GLOBAL1_EXCHANGE_RATE IS NOT NULL AND (ABS(r.GLOBAL1_EXCHANGE_RATE - TRUNC(r.GLOBAL1_EXCHANGE_RATE, 7)) > 0 OR ABS(r.GLOBAL1_EXCHANGE_RATE) >= POWER(10,15))
UNION ALL SELECT 'E','ERROR_GLOBAL2_EXCHANGE_RATE_DTYPE', r.*, SYSDATE FROM tndr_src r WHERE r.GLOBAL2_EXCHANGE_RATE IS NOT NULL AND (ABS(r.GLOBAL2_EXCHANGE_RATE - TRUNC(r.GLOBAL2_EXCHANGE_RATE, 7)) > 0 OR ABS(r.GLOBAL2_EXCHANGE_RATE) >= POWER(10,15))
UNION ALL SELECT 'E','ERROR_GLOBAL3_EXCHANGE_RATE_DTYPE', r.*, SYSDATE FROM tndr_src r WHERE r.GLOBAL3_EXCHANGE_RATE IS NOT NULL AND (ABS(r.GLOBAL3_EXCHANGE_RATE - TRUNC(r.GLOBAL3_EXCHANGE_RATE, 7)) > 0 OR ABS(r.GLOBAL3_EXCHANGE_RATE) >= POWER(10,15))
UNION ALL SELECT 'E','ERROR_LOC_EXCHANGE_RATE_DTYPE', r.*, SYSDATE FROM tndr_src r WHERE r.LOC_EXCHANGE_RATE IS NOT NULL AND (ABS(r.LOC_EXCHANGE_RATE - TRUNC(r.LOC_EXCHANGE_RATE, 7)) > 0 OR ABS(r.LOC_EXCHANGE_RATE) >= POWER(10,15))
),
---------------------------------------------------------------------------------
-- 3) Validate mandatory fields are not null
---------------------------------------------------------------------------------
error_rows_null_fields AS (
SELECT 'E' AS LOG_LVL, 'ERROR_INVALID_NULL' AS ERROR_MSG, f.*, SYSDATE AS INSERTED_AT
FROM tndr_src f
WHERE f.SLS_TRX_ID IS NULL
OR f.TENDER_TYPE_ID IS NULL
OR f.ORG_NUM IS NULL
OR f.DAY_DT IS NULL
OR f.REVISION_NUM IS NULL
OR f.CASHIER_ID IS NULL
OR f.REGISTER_ID IS NULL
OR f.DATASOURCE_NUM_ID IS NULL
OR f.INTEGRATION_ID IS NULL
),
---------------------------------------------------------------------------------
-- 4) Duplicate business key (analytic)
---------------------------------------------------------------------------------
error_rows_duplicate_keys AS (
SELECT 'E' AS LOG_LVL, 'ERROR_DUPLICATE_INTEGRATION_ID' AS ERROR_MSG, f.*, SYSDATE AS INSERTED_AT
FROM tndr_src f
WHERE f.DUP_CNT > 1
),
---------------------------------------------------------------------------------
-- 5.a) Validate store exist and is active
---------------------------------------------------------------------------------
error_rows_store_missing AS (
SELECT 'E' AS LOG_LVL, 'ERROR_NO_VALID_STORE' AS ERROR_MSG, f.*, SYSDATE AS INSERTED_AT
FROM tndr_src f
WHERE f.ORG_NUM IS NULL
OR NOT EXISTS (
SELECT 1
FROM store_add_ctrl s
WHERE s.ctrl_status = 'C'
AND s.store = f.ORG_NUM
)
),
---------------------------------------------------------------------------------
-- 5.b) Validate tender amounts and transaction type (SALE/RETURN)
---------------------------------------------------------------------------------
error_rows_tndr_amt_both_filled AS (
SELECT 'E' AS LOG_LVL, 'ERROR_TNDR_AMT_BOTH_FILLED' AS ERROR_MSG, f.*, SYSDATE AS INSERTED_AT
FROM tndr_src f
WHERE NVL(f.TNDR_SLS_AMT_LCL,0) <> 0
AND NVL(f.TNDR_RET_AMT_LCL,0) <> 0
),
error_rows_tndr_amt_not_positive AS (
SELECT 'E' AS LOG_LVL, 'ERROR_TNDR_AMT_NOT_POSITIVE' AS ERROR_MSG, f.*, SYSDATE AS INSERTED_AT
FROM tndr_src f
WHERE NVL(f.TNDR_SLS_AMT_LCL, 0) <= 0
AND NVL(f.TNDR_RET_AMT_LCL, 0) <= 0
),
error_rows_sales_ctrl_missing AS (
SELECT 'E' AS LOG_LVL, 'ERROR_NO_ORI_SALES_CTRL' AS ERROR_MSG, f.*, SYSDATE AS INSERTED_AT
FROM tndr_src f
WHERE f.EXPECTED_TRAN_TYPE IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM ORI_SALES_CTRL c
WHERE c.SLS_TRX_ID = f.SLS_TRX_ID
AND c.ORG_NUM = f.ORG_NUM
AND c.DAY_DT = f.DAY_DT
)
),
error_rows_tran_type_mismatch AS (
SELECT 'E' AS LOG_LVL, 'ERROR_TRAN_TYPE_MISMATCH' AS ERROR_MSG, f.*, SYSDATE AS INSERTED_AT
FROM tndr_src f
JOIN ORI_SALES_CTRL c
ON c.SLS_TRX_ID = f.SLS_TRX_ID
AND c.ORG_NUM = f.ORG_NUM
AND c.DAY_DT = f.DAY_DT
WHERE f.EXPECTED_TRAN_TYPE IS NOT NULL
AND UPPER(TRIM(c.TRAN_TYPE)) <> f.EXPECTED_TRAN_TYPE
),
---------------------------------------------------------------------------------
-- 6) Return all rows with errors
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT * FROM error_rows_formatting_dtype
UNION ALL SELECT * FROM error_rows_null_fields
UNION ALL SELECT * FROM error_rows_duplicate_keys
UNION ALL SELECT * FROM error_rows_store_missing
UNION ALL SELECT * FROM error_rows_tndr_amt_both_filled
UNION ALL SELECT * FROM error_rows_tndr_amt_not_positive
UNION ALL SELECT * FROM error_rows_sales_ctrl_missing
UNION ALL SELECT * FROM error_rows_tran_type_mismatch
)
SELECT
LOG_LVL, ERROR_MSG,
SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT, REVISION_NUM,
TENDER_TYPE_GROUP, CASHIER_ID, REGISTER_ID,
VOUCHER_NUM, VOUCHER_AGE, COUPON_NUM, COUPON_REF_NUM,
TNDR_SLS_AMT_LCL, TNDR_RET_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,
FLEX1_CHAR_VALUE, FLEX2_CHAR_VALUE, FLEX3_CHAR_VALUE, FLEX4_CHAR_VALUE,
UPDATED_AT, INSERTED_AT
FROM all_error_rows;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- (C) Insert accepted rows to ORI_SALES_TENDER_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ORI_SALES_TENDER_TMP (
SLS_TRX_ID, TENDER_TYPE_ID, ORG_NUM, DAY_DT, REVISION_NUM,
TENDER_TYPE_GROUP, CASHIER_ID, REGISTER_ID,
VOUCHER_NUM, VOUCHER_AGE, COUPON_NUM, COUPON_REF_NUM,
TNDR_SLS_AMT_LCL, TNDR_RET_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,
FLEX1_CHAR_VALUE, FLEX2_CHAR_VALUE, FLEX3_CHAR_VALUE, FLEX4_CHAR_VALUE
)
SELECT
f.SLS_TRX_ID, f.TENDER_TYPE_ID, f.ORG_NUM, f.DAY_DT, f.REVISION_NUM,
f.TENDER_TYPE_GROUP, f.CASHIER_ID, f.REGISTER_ID,
f.VOUCHER_NUM, f.VOUCHER_AGE, f.COUPON_NUM, f.COUPON_REF_NUM,
f.TNDR_SLS_AMT_LCL, f.TNDR_RET_AMT_LCL,
f.EXCHANGE_DT,
f.AUX1_CHANGED_ON_DT, f.AUX2_CHANGED_ON_DT, f.AUX3_CHANGED_ON_DT, f.AUX4_CHANGED_ON_DT,
f.CHANGED_BY_ID, f.CHANGED_ON_DT,
f.CREATED_BY_ID, f.CREATED_ON_DT,
f.DATASOURCE_NUM_ID, f.DELETE_FLG,
f.DOC_CURR_CODE, f.ETL_THREAD_VAL,
f.GLOBAL1_EXCHANGE_RATE, f.GLOBAL2_EXCHANGE_RATE, f.GLOBAL3_EXCHANGE_RATE,
f.INTEGRATION_ID,
f.LOC_CURR_CODE, f.LOC_EXCHANGE_RATE,
f.TENANT_ID, f.X_CUSTOM,
f.FLEX1_CHAR_VALUE, f.FLEX2_CHAR_VALUE, f.FLEX3_CHAR_VALUE, f.FLEX4_CHAR_VALUE
FROM ORI_SALES_TENDER_STAGE f
WHERE NOT EXISTS (
SELECT 1
FROM ORI_SALES_TENDER_REJECT r
WHERE r.LOG_LVL = 'E'
AND NVL(f.INTEGRATION_ID,'-1') = NVL(r.INTEGRATION_ID,'-1')
);
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- REJECTION SUMMARY
------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT ERROR_MSG, COUNT(*) AS NUM_RECORDS, COUNT(DISTINCT INTEGRATION_ID) AS NUM_KEYS
FROM ORI_SALES_TENDER_REJECT
GROUP BY ERROR_MSG
UNION ALL
SELECT 'ALL SALES TENDER (ACCEPTED)' AS ERROR_MSG, COUNT(*) AS NUM_RECORDS, COUNT(DISTINCT INTEGRATION_ID) AS NUM_KEYS
FROM ORI_SALES_TENDER_TMP
ORDER BY NUM_RECORDS DESC;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- (D) Cleanup stage
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ORI_SALES_TENDER_STAGE PURGE';
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/