Source: DMF/PuC/AIF/SalesTender.sql
select * from xref_tender_type;
select count(1) from ori_sales_ctrl; --64489743
select count(1) from ori_sales_tender_raw; 62602828
select * from ori_sales_tender_ctrl;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Truncate validation tables
------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRUNCATE TABLE ORI_SALES_TENDER_REJECT;
TRUNCATE TABLE ORI_SALES_TENDER_TMP;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_SALES_TENDER_REJECT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ORI_SALES_TENDER_REJECT (
LOG_LVL, ERROR_MSG,
TNDR_TYPE_ID, TNDR_TYPE_GRP_ID,
CREATED_BY_ID, CHANGED_BY_ID,
CREATED_ON_DT, CHANGED_ON_DT,
AUX1_CHANGED_ON_DT, AUX2_CHANGED_ON_DT, AUX3_CHANGED_ON_DT, AUX4_CHANGED_ON_DT,
DELETE_FLG,
DATASOURCE_NUM_ID, ETL_PROC_WID,
INTEGRATION_ID, TENANT_ID, X_CUSTOM,
UPDATED_AT, INSERTED_AT
)
WITH
---------------------------------------------------------------------------------
-- 1) Dataset (RAW -> TMP)
---------------------------------------------------------------------------------
tndr_src AS (
SELECT
/* Placeholder: depois cruzar com XREF_TENDER_TYPES p/ ORACLE_ID */
r.TENDER_TYPE_ID AS TNDR_TYPE_ID,
r.TENDER_TYPE_GROUP AS TNDR_TYPE_GRP_ID,
r.CREATED_BY_ID AS CREATED_BY_ID,
r.CHANGED_BY_ID AS CHANGED_BY_ID,
r.CREATED_ON_DT AS CREATED_ON_DT,
r.CHANGED_ON_DT AS CHANGED_ON_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.DELETE_FLG AS DELETE_FLG,
1 AS DATASOURCE_NUM_ID,
1 AS ETL_PROC_WID,
r.SLS_TRX_ID || '~'
|| r.TENDER_TYPE_ID || '~'
|| r.ORG_NUM || '~'
|| TO_CHAR(r.DAY_DT,'YYYYMMDD') AS INTEGRATION_ID,
r.TENANT_ID AS TENANT_ID,
r.X_CUSTOM AS X_CUSTOM,
CAST(NULL AS DATE) AS UPDATED_AT,
/* Auxiliares para valiações */
r.ORG_NUM AS ORG_NUM,
r.SLS_TRX_ID AS SLS_TRX_ID,
r.TNDR_SLS_AMT_LCL AS TNDR_SLS_AMT_LCL,
r.TNDR_RET_AMT_LCL AS TNDR_RET_AMT_LCL,
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
FROM ORI_SALES_TENDER_RAW r
),
---------------------------------------------------------------------------------
-- 1. b) RAP formatting data types
---------------------------------------------------------------------------------
error_rows_formatting_dtype AS (
SELECT 'E','ERROR_DTYPE_TNDR_TYPE_ID',r.*,SYSDATE FROM tndr_src r WHERE r.TNDR_TYPE_ID IS NOT NULL AND LENGTHB(r.TNDR_TYPE_ID) > 50
UNION ALL
SELECT 'E','ERROR_DTYPE_TNDR_TYPE_GRP_ID',r.*,SYSDATE FROM tndr_src r WHERE r.TNDR_TYPE_GRP_ID IS NOT NULL AND LENGTHB(r.TNDR_TYPE_GRP_ID) > 50
UNION ALL
SELECT 'E','ERROR_DTYPE_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_DTYPE_CREATED_BY_ID',r.*,SYSDATE FROM tndr_src r WHERE r.CREATED_BY_ID IS NOT NULL AND (ABS(r.CREATED_BY_ID) >= POWER(10,10) OR r.CREATED_BY_ID <> TRUNC(r.CREATED_BY_ID))
UNION ALL
SELECT 'E','ERROR_DTYPE_CHANGED_BY_ID',r.*,SYSDATE FROM tndr_src r WHERE r.CHANGED_BY_ID IS NOT NULL AND (ABS(r.CHANGED_BY_ID) >= POWER(10,10) OR r.CHANGED_BY_ID <> TRUNC(r.CHANGED_BY_ID))
UNION ALL
SELECT 'E','ERROR_DTYPE_DATASOURCE_NUM_ID',r.*,SYSDATE FROM tndr_src r WHERE r.DATASOURCE_NUM_ID IS NOT NULL AND (ABS(r.DATASOURCE_NUM_ID) >= POWER(10,10) OR r.DATASOURCE_NUM_ID <> TRUNC(r.DATASOURCE_NUM_ID))
UNION ALL
SELECT 'E','ERROR_DTYPE_ETL_PROC_WID',r.*,SYSDATE FROM tndr_src r WHERE r.ETL_PROC_WID IS NOT NULL AND (ABS(r.ETL_PROC_WID) >= POWER(10,10) OR r.ETL_PROC_WID <> TRUNC(r.ETL_PROC_WID))
UNION ALL
SELECT 'E','ERROR_DTYPE_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_DTYPE_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_DTYPE_X_CUSTOM',r.*,SYSDATE FROM tndr_src r WHERE r.X_CUSTOM IS NOT NULL AND LENGTHB(r.X_CUSTOM) > 10
),
---------------------------------------------------------------------------------
-- 2) 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.TNDR_TYPE_ID IS NULL
OR f.DATASOURCE_NUM_ID IS NULL
OR f.ETL_PROC_WID IS NULL
OR f.INTEGRATION_ID IS NULL
),
---------------------------------------------------------------------------------
-- 5) Validate duplicate business key: INTEGRATION_ID
---------------------------------------------------------------------------------
duplicate_keys AS (
SELECT INTEGRATION_ID, COUNT(*) AS duplicate_cnt
FROM tndr_src
GROUP BY INTEGRATION_ID
HAVING COUNT(*) > 1
),
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
JOIN duplicate_keys d
ON NVL(f.INTEGRATION_ID,'-1') = NVL(d.INTEGRATION_ID,'-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)
---------------------------------------------------------------------------------
/* Os dois montantes não podem estar preenchidos ao mesmo tempo */
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
),
/* (2) Se esperamos SALE/RETURN, tem de existir a venda em ORI_SALES_CTRL */
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
)
),
---------------------------------------------------------------------------------
-- 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_sales_ctrl_missing
UNION ALL SELECT * FROM error_rows_tran_type_mismatch
)
SELECT
LOG_LVL, ERROR_MSG,
TNDR_TYPE_ID, TNDR_TYPE_GRP_ID,
CREATED_BY_ID, CHANGED_BY_ID,
CREATED_ON_DT, CHANGED_ON_DT,
AUX1_CHANGED_ON_DT, AUX2_CHANGED_ON_DT, AUX3_CHANGED_ON_DT, AUX4_CHANGED_ON_DT,
DELETE_FLG,
DATASOURCE_NUM_ID, ETL_PROC_WID,
INTEGRATION_ID, TENANT_ID,
X_CUSTOM,
UPDATED_AT, inserted_at
FROM all_error_rows;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert accepted rows to ORI_SALES_TENDER_TMP
-- (Rows NOT in REJECTED with LOG_LVL='E' by business key)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ORI_SALES_TENDER_TMP (
TNDR_TYPE_ID, TNDR_TYPE_GRP_ID,
CREATED_BY_ID, CHANGED_BY_ID,
CREATED_ON_DT, CHANGED_ON_DT,
AUX1_CHANGED_ON_DT, AUX2_CHANGED_ON_DT, AUX3_CHANGED_ON_DT, AUX4_CHANGED_ON_DT,
DELETE_FLG,
DATASOURCE_NUM_ID, ETL_PROC_WID,
INTEGRATION_ID, TENANT_ID, X_CUSTOM
)
WITH
---------------------------------------------------------------------------------
-- 1) Final Dataset (IGUAL ao bloco de REJECTED)
---------------------------------------------------------------------------------
tndr_src AS (
SELECT
/* Placeholder: depois cruzar com XREF_TENDER_TYPES p/ ORACLE_ID */
r.TENDER_TYPE_ID AS TNDR_TYPE_ID,
r.TENDER_TYPE_GROUP AS TNDR_TYPE_GRP_ID,
r.CREATED_BY_ID AS CREATED_BY_ID,
r.CHANGED_BY_ID AS CHANGED_BY_ID,
r.CREATED_ON_DT AS CREATED_ON_DT,
r.CHANGED_ON_DT AS CHANGED_ON_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.DELETE_FLG AS DELETE_FLG,
1 AS DATASOURCE_NUM_ID,
1 AS ETL_PROC_WID,
r.SLS_TRX_ID || '~'
|| r.TENDER_TYPE_ID || '~'
|| r.ORG_NUM || '~'
|| TO_CHAR(r.DAY_DT,'YYYYMMDD') AS INTEGRATION_ID,
r.TENANT_ID AS TENANT_ID,
r.X_CUSTOM AS X_CUSTOM
FROM ORI_SALES_TENDER_RAW r
)
SELECT
f.TNDR_TYPE_ID, f.TNDR_TYPE_GRP_ID,
f.CREATED_BY_ID, f.CHANGED_BY_ID,
f.CREATED_ON_DT, f.CHANGED_ON_DT,
f.AUX1_CHANGED_ON_DT, f.AUX2_CHANGED_ON_DT, f.AUX3_CHANGED_ON_DT, f.AUX4_CHANGED_ON_DT,
f.DELETE_FLG,
f.DATASOURCE_NUM_ID, f.ETL_PROC_WID,
f.INTEGRATION_ID, f.TENANT_ID, f.X_CUSTOM
FROM tndr_src 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;
truncate table xref_tender_type;
select count(1) from xref_tender_type;
insert into xref_tender_type values ('ALIP',1,'OTHERS');
insert into xref_tender_type values ('AMEP',2,'OTHERS');
insert into xref_tender_type values ('AMEX',3020,'CCARD');
insert into xref_tender_type values ('BANC',3,'OTHERS');
insert into xref_tender_type values ('BANK',4,'OTHERS');
insert into xref_tender_type values ('BONU',10003,'OTHERS');
insert into xref_tender_type values ('CAAL',5,'CCARD');
insert into xref_tender_type values ('CASH',1000,'CASH');
insert into xref_tender_type values ('CCOD',10007,'OTHERS');
insert into xref_tender_type values ('CHEQ',1000,'CASH');
insert into xref_tender_type values ('COUP',6,'COUPON');
insert into xref_tender_type values ('CUPY',7,'OTHERS');
insert into xref_tender_type values ('DINE',8,'CCARD');
insert into xref_tender_type values ('DIRE',9,'OTHERS');
insert into xref_tender_type values ('DIV.',10,'OTHERS');
insert into xref_tender_type values ('EC',11,'CCARD');
insert into xref_tender_type values ('ECMC',3010,'CCARD');
insert into xref_tender_type values ('FORC',1010,'CASH');
insert into xref_tender_type values ('GCGT',12,'VOUCH');
insert into xref_tender_type values ('GICA',13,'VOUCH');
insert into xref_tender_type values ('GKAK',14,'VOUCH');
insert into xref_tender_type values ('GUTS',15,'VOUCH');
insert into xref_tender_type values ('IDEA',10000,'PAYPAL');
insert into xref_tender_type values ('INVO',16,'OTHERS');
insert into xref_tender_type values ('JCB',17,'CCARD');
insert into xref_tender_type values ('MAST',3010,'CCARD');
insert into xref_tender_type values ('MMO',18,'OTHERS');
insert into xref_tender_type values ('OGKF',19,'OTHERS');
insert into xref_tender_type values ('OTHE',20,'CCARD');
insert into xref_tender_type values ('PAYP',10000,'PAYPAL');
insert into xref_tender_type values ('PIN',21,'OTHERS');
insert into xref_tender_type values ('PR24',22,'OTHERS');
insert into xref_tender_type values ('RUCK',23,'CASH');
insert into xref_tender_type values ('RUND',24,'CASH');
insert into xref_tender_type values ('VISA',3000,'CCARD');
insert into xref_tender_type values ('VPAY',25,'CCARD');
insert into xref_tender_type values ('WCPY',26,'OTHERS');
insert into xref_tender_type values ('AVSG',27,'OTHERS');
insert into xref_tender_type values ('BAK1',28,'OTHERS');
insert into xref_tender_type values ('BAK2',29,'OTHERS');
insert into xref_tender_type values ('CAS2',30,'OTHERS');
insert into xref_tender_type values ('TWNT',31,'OTHERS');