Source: DMF/PuC/AIF/Allocation_Hist.sql
/*
DROP TABLE ORI_ALLOC_RAW;
CREATE TABLE ORI_ALLOC_RAW
("ALLOC_NO" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",
"ORDER_NO" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",
"STATUS" VARCHAR2(1 BYTE) COLLATE "USING_NLS_COMP",
"PO_TYPE" VARCHAR2(6 BYTE) COLLATE "USING_NLS_COMP",
"ALLOC_METHOD" VARCHAR2(1 BYTE) COLLATE "USING_NLS_COMP",
"RELEASE_DATE" DATE,
"DOC" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",
"DOC_TYPE" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",
"CHANGED_BY_ID" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",
"CHANGED_ON_DT" DATE,
"CREATED_BY_ID" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",
"CREATED_ON_DT" DATE,
"AUX1_CHANGED_ON_DT" DATE,
"AUX2_CHANGED_ON_DT" DATE,
"AUX3_CHANGED_ON_DT" DATE,
"AUX4_CHANGED_ON_DT" DATE,
"SRC_EFF_FROM_DT" DATE,
"SRC_EFF_TO_DT" DATE,
"DELETE_FLG" CHAR(1 BYTE) COLLATE "USING_NLS_COMP",
"DATASOURCE_NUM_ID" NUMBER,
"INTEGRATION_ID" VARCHAR2(80 BYTE) COLLATE "USING_NLS_COMP",
"TENANT_ID" VARCHAR2(80 BYTE) COLLATE "USING_NLS_COMP",
"X_CUSTOM" VARCHAR2(10 BYTE) COLLATE "USING_NLS_COMP",
"SOURCE_WH" VARCHAR2(30 CHAR) COLLATE "USING_NLS_COMP",
"ALLOC_DESC" VARCHAR2(300 CHAR) COLLATE "USING_NLS_COMP",
"ORDER_TYPE" VARCHAR2(30 CHAR) COLLATE "USING_NLS_COMP",
"CONTEXT_TYPE" VARCHAR2(10 CHAR) COLLATE "USING_NLS_COMP",
"CONTEXT_VALUE" VARCHAR2(30 CHAR) COLLATE "USING_NLS_COMP",
"COMMENT_DESC" VARCHAR2(300 CHAR) COLLATE "USING_NLS_COMP",
"ALLOC_PARENT" NUMBER(30,0),
"ORIGIN_IND" VARCHAR2(10 CHAR) COLLATE "USING_NLS_COMP",
"CLOSE_DATE" DATE,
"PROD_NUM" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",
"ORG_NUM" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",
"DAY_DT" DATE,
"ITEM_AVG_COST_AMT_LCL" NUMBER(20,4),
"TRANSFERRED_QTY" NUMBER(20,4),
"ALLOCATED_QTY" NUMBER(20,4),
"PRESCALED_QTY" NUMBER(20,4),
"DISTRO_QTY" NUMBER(20,4),
"SELECTED_QTY" NUMBER(20,4),
"CANCELLED_QTY" NUMBER(20,4),
"RECEIVED_QTY" NUMBER(20,4),
"RECONCILED_QTY" NUMBER(20,4),
"PO_RCVD_QTY" NUMBER(20,4),
"NON_SCALE_IND" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",
"IN_STORE_DATE" DATE,
"WF_ORDER_NO" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",
"DOC_CURR_CODE" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",
"LOC_CURR_CODE" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",
"CREATE_ID" VARCHAR2(254 BYTE) COLLATE "USING_NLS_COMP",
"CREATE_DATETIME" DATE,
"CTRL_STATUS" VARCHAR2(1 BYTE) COLLATE "USING_NLS_COMP",
"CTRL_DATETIME" DATE,
"CTRL_ERROR_MSG" VARCHAR2(2000 BYTE) COLLATE "USING_NLS_COMP"
);
DROP TABLE ORI_ALLOC_REJECTED;
CREATE TABLE ORI_ALLOC_REJECTED NOLOGGING AS
SELECT CAST(NULL AS VARCHAR2(10)) AS LOG_LVL
,CAST(NULL AS VARCHAR2(1000)) AS ERROR_MSG
,t.*
FROM ORI_ALLOC_RAW t;
ALTER TABLE ORI_ALLOC_REJECTED ADD INSERTED_AT DATE DEFAULT SYSDATE NOT NULL ENABLE;
DROP SEQUENCE ALLOC_MIGR_SEQ;
CREATE SEQUENCE ALLOC_MIGR_SEQ
START WITH 6000000000
INCREMENT BY 1
CACHE 100
NOCYCLE;
ALTER SEQUENCE ALLOC_MIGR_SEQ RESTART START WITH 6000000000;
DROP TABLE XREF_ALLOC;
CREATE TABLE XREF_ALLOC (
SAP_ALLOC_ID VARCHAR2(10),
ORACLE_ALLOC_ID NUMBER(10),
ITEM VARCHAR2(25)
);
CREATE INDEX DMF_XREF_ALLOC_IDX1
ON XREF_ALLOC (SAP_ALLOC_ID,ITEM);
TRUNCATE TABLE ORI_ALLOC_REJECTED;
TRUNCATE TABLE ORI_ALLOC_RAW;
TRUNCATE TABLE XREF_ALLOC;
TRUNCATE TABLE ORI_ALLOC_HEADER_CTRL;
TRUNCATE TABLE ORI_ALLOC_DETAIL_CTRL;
COMMIT;
-- TEMP TABLES
SELECT * FROM PUC_IBC_PO_XDOC_STORE_TMP;
SELECT * FROM PUC_IBC_REPL_STORE_TMP;
SELECT * FROM PUC_TRD_PARTY_PO_XDOC_STORE_TMP;
*/
SET SERVEROUTPUT ON;
ALTER SEQUENCE ALLOC_MIGR_SEQ RESTART START WITH 6000000000;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ALLOC HEADER AND DETAIL RAW DATA - LEG1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('1. ALLOC HEADER AND DETAIL RAW DATA - IBC X-DOCKING ALLOCATION - LEG 1 - LOADING');
END;
/
--ALLOC HEADER AND DEATAIL
INSERT INTO /*+ PARALLEL(24) */ ORI_ALLOC_RAW (
ALLOC_NO,ORDER_NO,STATUS,PO_TYPE,ALLOC_METHOD,RELEASE_DATE,DOC,DOC_TYPE,DELETE_FLG,DATASOURCE_NUM_ID,INTEGRATION_ID,SOURCE_WH,ALLOC_DESC,ORDER_TYPE,COMMENT_DESC,ALLOC_PARENT,ORIGIN_IND,CLOSE_DATE, --HEADER
PROD_NUM,ORG_NUM,DAY_DT,ITEM_AVG_COST_AMT_LCL,TRANSFERRED_QTY,ALLOCATED_QTY,PRESCALED_QTY,CANCELLED_QTY,RECEIVED_QTY,PO_RCVD_QTY,NON_SCALE_IND,IN_STORE_DATE,DOC_CURR_CODE,LOC_CURR_CODE, --DETAIL
CTRL_STATUS,CTRL_DATETIME --CTRL
)
WITH PUC_ALLOC as (
SELECT /*+ PARALLEL(24) */
*
FROM PUC_IBC_PO_XDOC_STORE_TMP
WHERE ALLOC_2 IS NOT NULL
AND STORE IS NOT NULL -- ELIMINAR ALLOCS COM TODAS AS LINHAS CANCELADAS
),
ALLOC_HEADER AS (
SELECT /*+ PARALLEL(24) */
A.*,
EKKO.KNUMV,
EKKO.WAERS,
EKKO.BEDAT
FROM PUC_ALLOC A
JOIN SAP_SLT_PROD_PV1.EKKO EKKO ON EKKO.EBELN = A.ALLOC_1 AND EKKO.BEDAT >= 20240101
),
ALLOC_DETAIL AS (
SELECT /*+ PARALLEL(24) */
ALLOC_1,
WAERS,
BEDAT,
IBC_PO,
IBC_ORACLE_WH,
BUYING_ORACLE_WH,
MATNR,
EBELP,
GLD,
SELLING_PRICE,
NETPR,
MIN(STATUS) STATUS,
MAX(AEDAT) AEDAT, -- update date
SUM(tsf_qty) as alloc_qty,
SUM(cancel_qty) as cancel_qty,
SUM(ship_qty) as ship_qty ,
SUM(received_qty) as received_qty
FROM (
SELECT /*+ PARALLEL(24) */
A.*,
EKPO.MATNR,
EKPO.EBELP,
CASE
WHEN EKPO.ELIKZ = 'X' THEN 'C'
WHEN EKPO.LOEKZ = 'L' THEN 'C'
WHEN EKPO.MENGE = EKET.WEMNG THEN 'C'
ELSE 'A'
END STATUS,
EKPO.AEDAT, --UPDATE DATE
EKPO.NETPR,
decode(EKPO.LOEKZ,'L',0,EKPO.MENGE) tsf_qty,
decode(EKPO.LOEKZ,'L',EKPO.MENGE,0) cancel_qty,
nvl(LIPS.LFIMG,0) ship_qty,
EKET.WEMNG as received_qty,
KONV_Z101.KBETR AS GLD,
KONV_ZWVH.KBETR AS SELLING_PRICE
FROM ALLOC_HEADER A
JOIN SAP_SLT_PROD_PV1.EKPO EKPO ON EKPO.EBELN = A.ALLOC_1 AND EKPO.UEBPO = '00000'
LEFT JOIN SAP_SLT_PROD_PV1.EKET EKET on EKET.EBELN = EKPO.EBELN AND EKET.EBELP = EKPO.EBELP
LEFT JOIN SAP_SLT_PROD_PV1.LIPS LIPS on LIPS.VGBEL = EKPO.EBELN AND TO_NUMBER(LIPS.VGPOS) = TO_NUMBER(EKPO.EBELP)
LEFT JOIN SAP_SLT_PROD_PV1.LIKP LIKP on LIKP.VBELN = LIPS.VBELN
LEFT JOIN KONV_Z101 KONV_Z101 on KONV_Z101.KNUMV = A.KNUMV AND TO_NUMBER(KONV_Z101.KPOSN) = TO_NUMBER(EKPO.EBELP)
LEFT JOIN KONV_ZWVH KONV_ZWVH on KONV_ZWVH.KNUMV = A.KNUMV AND TO_NUMBER(KONV_ZWVH.KPOSN) = TO_NUMBER(EKPO.EBELP)
) t group by ALLOC_1,WAERS,BEDAT,IBC_PO,IBC_ORACLE_WH,BUYING_ORACLE_WH,KNUMV,MATNR,EBELP,GLD,SELLING_PRICE,NETPR)
SELECT /*+ PARALLEL(24) */
ALLOC_MIGR_SEQ.NEXTVAL AS ALLOC_NO,
IBC_PO AS ORDER_NO,
STATUS AS STATUS,
NULL AS PO_TYPE,
'A' AS ALLOC_METHOD,
TO_DATE(BEDAT,'YYYYMMDD') AS RELEASE_DATE,
NULL AS DOC,
CASE
WHEN IBC_PO IS NOT NULL THEN
'PO'
ELSE
NULL
END AS DOC_TYPE,
'N' AS DELETE_FLG,
'1' AS DATASOURCE_NUM_ID,
ALLOC_1 || '-' || MATNR AS INTEGRATION_ID,
IBC_ORACLE_WH AS SOURCE_WH,
'IBC X-DOCKING ALLOCATION - LEG 1 - ' || ALLOC_1 AS ALLOC_DESC,
'PREDIST' AS ORDER_TYPE,
ALLOC_1 AS COMMENT_DESC,
NULL AS ALLOC_PARENT,
'EG' AS ORIGIN_IND,
DECODE(STATUS,'C', TO_DATE(AEDAT,'YYYYMMDD'),NULL) AS CLOSE_DATE,
MATNR AS PROD_NUM,
BUYING_ORACLE_WH AS ORG_NUM,
TO_DATE(BEDAT,'YYYYMMDD') AS DAY_DT,
NVL(GLD,SELLING_PRICE) AS ITEM_AVG_COST_AMT_LCL,
ship_qty AS TRANSFERRED_QTY,
CASE
WHEN alloc_qty - cancel_qty < 0 THEN
alloc_qty
ELSE
alloc_qty
END AS ALLOCATED_QTY,
CASE
WHEN alloc_qty = 0 AND cancel_qty > 0 THEN
cancel_qty
ELSE
alloc_qty
END AS PRESCALED_QTY,
CASE
WHEN status = 'C' and cancel_qty = 0 THEN
alloc_qty
ELSE
cancel_qty
END AS CANCELLED_QTY,
received_qty AS RECEIVED_QTY,
received_qty AS PO_RCVD_QTY,
'N' AS NON_SCALE_IND,
TO_DATE(BEDAT,'YYYYMMDD') + 30 AS IN_STORE_DATE,
WAERS AS DOC_CURR_CODE,
'EUR' AS LOC_CURR_CODE,
'I' AS CTRL_STATUS,
SYSDATE AS CTRL_DATETIME
FROM ALLOC_DETAIL;
COMMIT;
--------------
BEGIN
DBMS_OUTPUT.PUT_LINE('2. ALLOC HEADER AND DETAIL RAW DATA - IBC REPLNISHMENT ALLOCATION - LEG 1 - LOADING');
END;
/
--------------
--ALLOC HEADER AND DEATAIL
INSERT INTO /*+ PARALLEL(24) */ ORI_ALLOC_RAW (
ALLOC_NO,ORDER_NO,STATUS,PO_TYPE,ALLOC_METHOD,RELEASE_DATE,DOC,DOC_TYPE,DELETE_FLG,DATASOURCE_NUM_ID,INTEGRATION_ID,SOURCE_WH,ALLOC_DESC,ORDER_TYPE,COMMENT_DESC,ALLOC_PARENT,ORIGIN_IND,CLOSE_DATE, --HEADER
PROD_NUM,ORG_NUM,DAY_DT,ITEM_AVG_COST_AMT_LCL,TRANSFERRED_QTY,ALLOCATED_QTY,PRESCALED_QTY,CANCELLED_QTY,RECEIVED_QTY,PO_RCVD_QTY,NON_SCALE_IND,IN_STORE_DATE,DOC_CURR_CODE,LOC_CURR_CODE, --DETAIL
CTRL_STATUS,CTRL_DATETIME --CTRL
)
WITH PUC_ALLOC as (
SELECT /*+ PARALLEL(24) */
*
FROM PUC_IBC_REPL_STORE_TMP
WHERE ALLOC_2 IS NOT NULL
AND STORE IS NOT NULL -- ELIMINAR ALLOCS COM TODAS AS LINHAS CANCELADAS
),
ALLOC_HEADER AS (
SELECT /*+ PARALLEL(24) */
A.*,
EKKO.KNUMV,
EKKO.WAERS,
EKKO.BEDAT
FROM PUC_ALLOC A
JOIN SAP_SLT_PROD_PV1.EKKO EKKO ON EKKO.EBELN = A.ALLOC_1 AND EKKO.BEDAT >= 20240101
),
ALLOC_DETAIL AS (
SELECT /*+ PARALLEL(24) */
ALLOC_1,
WAERS,
BEDAT,
IBC_ORACLE_WH,
BUYING_ORACLE_WH,
MATNR,
EBELP,
GLD,
SELLING_PRICE,
NETPR,
MIN(STATUS) STATUS,
MAX(AEDAT) AEDAT, -- update date
SUM(tsf_qty) as alloc_qty,
SUM(cancel_qty) as cancel_qty,
SUM(ship_qty) as ship_qty ,
SUM(received_qty) as received_qty
FROM (
SELECT /*+ PARALLEL(24) */
A.*,
EKPO.MATNR,
EKPO.EBELP,
CASE
WHEN EKPO.ELIKZ = 'X' THEN 'C'
WHEN EKPO.LOEKZ = 'L' THEN 'C'
WHEN EKPO.MENGE = EKET.WEMNG THEN 'C'
ELSE 'A'
END STATUS,
EKPO.AEDAT, --UPDATE DATE
EKPO.NETPR,
decode(EKPO.LOEKZ,'L',0,EKPO.MENGE) tsf_qty,
decode(EKPO.LOEKZ,'L',EKPO.MENGE,0) cancel_qty,
nvl(LIPS.LFIMG,0) ship_qty,
EKET.WEMNG as received_qty,
KONV_Z101.KBETR AS GLD,
KONV_ZWVH.KBETR AS SELLING_PRICE
FROM ALLOC_HEADER A
JOIN SAP_SLT_PROD_PV1.EKPO EKPO ON EKPO.EBELN = A.ALLOC_1 AND EKPO.UEBPO = '00000'
LEFT JOIN SAP_SLT_PROD_PV1.EKET EKET on EKET.EBELN = EKPO.EBELN AND EKET.EBELP = EKPO.EBELP
LEFT JOIN SAP_SLT_PROD_PV1.LIPS LIPS on LIPS.VGBEL = EKPO.EBELN AND TO_NUMBER(LIPS.VGPOS) = TO_NUMBER(EKPO.EBELP)
LEFT JOIN SAP_SLT_PROD_PV1.LIKP LIKP on LIKP.VBELN = LIPS.VBELN
LEFT JOIN KONV_Z101 KONV_Z101 on KONV_Z101.KNUMV = A.KNUMV AND TO_NUMBER(KONV_Z101.KPOSN) = TO_NUMBER(EKPO.EBELP)
LEFT JOIN KONV_ZWVH KONV_ZWVH on KONV_ZWVH.KNUMV = A.KNUMV AND TO_NUMBER(KONV_ZWVH.KPOSN) = TO_NUMBER(EKPO.EBELP)
) t group by ALLOC_1,WAERS,BEDAT,IBC_ORACLE_WH,BUYING_ORACLE_WH,KNUMV,MATNR,EBELP,GLD,SELLING_PRICE,NETPR)
SELECT /*+ PARALLEL(24) */
ALLOC_MIGR_SEQ.NEXTVAL AS ALLOC_NO,
NULL AS ORDER_NO,
STATUS AS STATUS,
NULL AS PO_TYPE,
'A' AS ALLOC_METHOD,
TO_DATE(BEDAT,'YYYYMMDD') AS RELEASE_DATE,
NULL AS DOC,
NULL AS DOC_TYPE,
'N' AS DELETE_FLG,
'1' AS DATASOURCE_NUM_ID,
ALLOC_1 || '-' || MATNR AS INTEGRATION_ID,
IBC_ORACLE_WH AS SOURCE_WH,
'IBC REPLNISHMENT ALLOCATION - LEG 1 - ' || ALLOC_1 AS ALLOC_DESC,
'PREDIST' AS ORDER_TYPE,
ALLOC_1 AS COMMENT_DESC,
NULL AS ALLOC_PARENT,
'EG' AS ORIGIN_IND,
DECODE(STATUS,'C', TO_DATE(AEDAT,'YYYYMMDD'),NULL) AS CLOSE_DATE,
MATNR AS PROD_NUM,
BUYING_ORACLE_WH AS ORG_NUM,
TO_DATE(BEDAT,'YYYYMMDD') AS DAY_DT,
NVL(GLD,SELLING_PRICE) AS ITEM_AVG_COST_AMT_LCL,
ship_qty AS TRANSFERRED_QTY,
CASE
WHEN alloc_qty - cancel_qty < 0 THEN
alloc_qty
ELSE
alloc_qty
END AS ALLOCATED_QTY,
CASE
WHEN alloc_qty = 0 AND cancel_qty > 0 THEN
cancel_qty
ELSE
alloc_qty
END AS PRESCALED_QTY,
CASE
WHEN status = 'C' and cancel_qty = 0 THEN
alloc_qty
ELSE
cancel_qty
END AS CANCELLED_QTY,
received_qty AS RECEIVED_QTY,
NULL AS PO_RCVD_QTY,
'N' AS NON_SCALE_IND,
TO_DATE(BEDAT,'YYYYMMDD') + 30 AS IN_STORE_DATE,
WAERS AS DOC_CURR_CODE,
'EUR' AS LOC_CURR_CODE,
'I' AS CTRL_STATUS,
SYSDATE AS CTRL_DATETIME
FROM ALLOC_DETAIL;
COMMIT;
--------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('3. ALLOC HEADER AND DETAIL RAW DATA - THIRD PARTY X-DOCKING ALLOCATION - LEG 1 - LOADING');
END;
/
------------------
--ALLOC HEADER AND DEATAIL
INSERT INTO /*+ PARALLEL(24) */ ORI_ALLOC_RAW (
ALLOC_NO,ORDER_NO,STATUS,PO_TYPE,ALLOC_METHOD,RELEASE_DATE,DOC,DOC_TYPE,DELETE_FLG,DATASOURCE_NUM_ID,INTEGRATION_ID,SOURCE_WH,ALLOC_DESC,ORDER_TYPE,COMMENT_DESC,ALLOC_PARENT,ORIGIN_IND,CLOSE_DATE, --HEADER
PROD_NUM,ORG_NUM,DAY_DT,ITEM_AVG_COST_AMT_LCL,TRANSFERRED_QTY,ALLOCATED_QTY,PRESCALED_QTY,CANCELLED_QTY,RECEIVED_QTY,PO_RCVD_QTY,NON_SCALE_IND,IN_STORE_DATE,DOC_CURR_CODE,LOC_CURR_CODE, --DETAIL
CTRL_STATUS,CTRL_DATETIME --CTRL
)
WITH PUC_ALLOC as (
SELECT /*+ PARALLEL(8) */
*
FROM PUC_TRD_PARTY_PO_XDOC_STORE_TMP
WHERE ALLOC_1 IS NOT NULL
AND STORE IS NOT NULL -- ELIMINAR ALLOCS COM TODAS AS LINHAS CANCELADAS
--AND ROWNUM = 1
),
ALLOC_HEADER AS (
SELECT /*+ PARALLEL(8) */
A.*,
EKKO.KNUMV,
EKKO.WAERS,
EKKO.BEDAT
FROM PUC_ALLOC A
JOIN SAP_SLT_PROD_PV1.EKKO EKKO ON EKKO.EBELN = A.ALLOC_1 AND EKKO.BEDAT >= 20240101
),
ALLOC_DETAIL AS (
SELECT /*+ PARALLEL(8) */
ALLOC_1,
WAERS,
BEDAT,
EXCL_PO,
BUYING_ORACLE_WH,
oracle_STORE,
MATNR,
EBELP,
GLD,
SELLING_PRICE,
NETPR,
MIN(STATUS) STATUS,
MAX(AEDAT) AEDAT, -- update date
SUM(tsf_qty) as alloc_qty,
SUM(cancel_qty) as cancel_qty,
SUM(ship_qty) as ship_qty ,
SUM(received_qty) as received_qty,
delivery_date
FROM (
SELECT /*+ PARALLEL(8) */
A.*,
EKPO.MATNR,
EKPO.EBELP,
CASE
WHEN EKPO.ELIKZ = 'X' THEN 'C'
WHEN EKPO.LOEKZ = 'L' THEN 'C'
WHEN EKPO.MENGE = EKET.WEMNG THEN 'C'
ELSE 'A'
END STATUS,
EKPO.AEDAT, --UPDATE DATE
EKPO.NETPR,
decode(EKPO.LOEKZ,'L',0,EKPO.MENGE) tsf_qty,
decode(EKPO.LOEKZ,'L',EKPO.MENGE,0) cancel_qty,
nvl(LIPS.LFIMG,0) ship_qty,
EKET.WEMNG as received_qty,
EKET.EINDT as delivery_date,
KONV_Z101.KBETR AS GLD,
KONV_ZWVH.KBETR AS SELLING_PRICE
FROM ALLOC_HEADER A
JOIN SAP_SLT_PROD_PV1.EKPO EKPO ON EKPO.EBELN = A.ALLOC_1 AND EKPO.UEBPO = '00000'
LEFT JOIN SAP_SLT_PROD_PV1.EKET EKET on EKET.EBELN = EKPO.EBELN AND EKET.EBELP = EKPO.EBELP
LEFT JOIN SAP_SLT_PROD_PV1.LIPS LIPS on LIPS.VGBEL = EKPO.EBELN AND TO_NUMBER(LIPS.VGPOS) = TO_NUMBER(EKPO.EBELP)
LEFT JOIN SAP_SLT_PROD_PV1.LIKP LIKP on LIKP.VBELN = LIPS.VBELN
LEFT JOIN KONV_Z101 KONV_Z101 on KONV_Z101.KNUMV = A.KNUMV AND TO_NUMBER(KONV_Z101.KPOSN) = TO_NUMBER(EKPO.EBELP)
LEFT JOIN KONV_ZWVH KONV_ZWVH on KONV_ZWVH.KNUMV = A.KNUMV AND TO_NUMBER(KONV_ZWVH.KPOSN) = TO_NUMBER(EKPO.EBELP)
) t group by ALLOC_1,WAERS,BEDAT,EXCL_PO,BUYING_ORACLE_WH,oracle_STORE,KNUMV,MATNR,EBELP,GLD,SELLING_PRICE,NETPR,delivery_date)
SELECT /*+ PARALLEL(8) */
ALLOC_MIGR_SEQ.NEXTVAL AS ALLOC_NO,
EXCL_PO AS ORDER_NO,
STATUS AS STATUS,
NULL AS PO_TYPE,
'A' AS ALLOC_METHOD,
TO_DATE(delivery_date,'YYYYMMDD') AS RELEASE_DATE,
NULL AS DOC,
'PO' AS DOC_TYPE,
'N' AS DELETE_FLG,
'1' AS DATASOURCE_NUM_ID,
ALLOC_1 || '-' || MATNR AS INTEGRATION_ID,
BUYING_ORACLE_WH AS SOURCE_WH,
'THIRD PARTY X-DOCKING ALLOCATION - LEG 1 - ' || ALLOC_1 AS ALLOC_DESC,
'PREDIST' AS ORDER_TYPE,
ALLOC_1 AS COMMENT_DESC,
NULL AS ALLOC_PARENT,
'EG' AS ORIGIN_IND,
DECODE(STATUS,'C', TO_DATE(AEDAT,'YYYYMMDD'),NULL) AS CLOSE_DATE,
MATNR AS PROD_NUM,
oracle_STORE AS ORG_NUM,
TO_DATE(BEDAT,'YYYYMMDD') AS DAY_DT,
NVL(GLD,SELLING_PRICE) AS ITEM_AVG_COST_AMT_LCL,
ship_qty AS TRANSFERRED_QTY,
CASE
WHEN alloc_qty - cancel_qty < 0 THEN
alloc_qty
ELSE
alloc_qty
END AS ALLOCATED_QTY,
CASE
WHEN alloc_qty = 0 AND cancel_qty > 0 THEN
cancel_qty
ELSE
alloc_qty
END AS PRESCALED_QTY,
CASE
WHEN status = 'C' and cancel_qty = 0 THEN
alloc_qty
ELSE
cancel_qty
END AS CANCELLED_QTY,
received_qty AS RECEIVED_QTY,
received_qty AS PO_RCVD_QTY,
'N' AS NON_SCALE_IND,
TO_DATE(delivery_date,'YYYYMMDD') + 30 AS IN_STORE_DATE,
WAERS AS DOC_CURR_CODE,
'EUR' AS LOC_CURR_CODE,
'I' AS CTRL_STATUS,
SYSDATE AS CTRL_DATETIME
FROM ALLOC_DETAIL;
COMMIT;
insert into XREF_ALLOC select comment_desc,alloc_no,prod_num from ORI_ALLOC_RAW;
commit;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ALLOC HEADER AND DETAIL RAW DATA - LEG1
------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('4. ALLOC HEADER AND DETAIL RAW DATA - IBC X-DOCKING ALLOCATION - LEG 2 - LOADING');
END;
/
--ALLOC HEADER AND DEATAIL
INSERT INTO /*+ PARALLEL(24) */ ORI_ALLOC_RAW (
ALLOC_NO,ORDER_NO,STATUS,PO_TYPE,ALLOC_METHOD,RELEASE_DATE,DOC,DOC_TYPE,DELETE_FLG,DATASOURCE_NUM_ID,INTEGRATION_ID,SOURCE_WH,ALLOC_DESC,ORDER_TYPE,COMMENT_DESC,ALLOC_PARENT,ORIGIN_IND,CLOSE_DATE, --HEADER
PROD_NUM,ORG_NUM,DAY_DT,ITEM_AVG_COST_AMT_LCL,TRANSFERRED_QTY,ALLOCATED_QTY,PRESCALED_QTY,CANCELLED_QTY,RECEIVED_QTY,PO_RCVD_QTY,NON_SCALE_IND,IN_STORE_DATE,DOC_CURR_CODE,LOC_CURR_CODE, --DETAIL
CTRL_STATUS,CTRL_DATETIME --CTRL
)
WITH PUC_ALLOC as (
SELECT /*+ PARALLEL(24) */
*
FROM PUC_IBC_PO_XDOC_STORE_TMP
WHERE ALLOC_2 IS NOT NULL
AND STORE IS NOT NULL -- ELIMINAR ALLOCS COM TODAS AS LINHAS CANCELADAS
),
ALLOC_HEADER AS (
SELECT /*+ PARALLEL(24) */
A.*,
EKKO.KNUMV,
EKKO.WAERS,
EKKO.BEDAT
FROM PUC_ALLOC A
JOIN SAP_SLT_PROD_PV1.EKKO EKKO ON EKKO.EBELN = A.ALLOC_2 AND EKKO.BEDAT >= 20240101
),
ALLOC_DETAIL AS (
SELECT /*+ PARALLEL(24) */
ALLOC_1,
ALLOC_2,
WAERS,
BEDAT,
BUYING_ORACLE_WH,
oracle_STORE,
MATNR,
EBELP,
GLD,
SELLING_PRICE,
NETPR,
MIN(STATUS) STATUS,
MAX(AEDAT) AEDAT, -- update date
SUM(tsf_qty) as alloc_qty,
SUM(cancel_qty) as cancel_qty,
SUM(ship_qty) as ship_qty ,
SUM(received_qty) as received_qty
FROM (
SELECT /*+ PARALLEL(24) */
A.*,
EKPO.MATNR,
EKPO.EBELP,
CASE
WHEN EKPO.ELIKZ = 'X' THEN 'C'
WHEN EKPO.LOEKZ = 'L' THEN 'C'
WHEN EKPO.MENGE = EKET.WEMNG THEN 'C'
ELSE 'A'
END STATUS,
EKPO.AEDAT, --UPDATE DATE
EKPO.NETPR,
decode(EKPO.LOEKZ,'L',0,EKPO.MENGE) tsf_qty,
decode(EKPO.LOEKZ,'L',EKPO.MENGE,0) cancel_qty,
nvl(LIPS.LFIMG,0) ship_qty,
EKET.WEMNG as received_qty,
KONV_Z101.KBETR AS GLD,
KONV_ZWVH.KBETR AS SELLING_PRICE
FROM ALLOC_HEADER A
JOIN SAP_SLT_PROD_PV1.EKPO EKPO ON EKPO.EBELN = A.ALLOC_2 AND EKPO.UEBPO = '00000'
LEFT JOIN SAP_SLT_PROD_PV1.EKET EKET on EKET.EBELN = EKPO.EBELN AND EKET.EBELP = EKPO.EBELP
LEFT JOIN SAP_SLT_PROD_PV1.LIPS LIPS on LIPS.VGBEL = EKPO.EBELN AND TO_NUMBER(LIPS.VGPOS) = TO_NUMBER(EKPO.EBELP)
LEFT JOIN SAP_SLT_PROD_PV1.LIKP LIKP on LIKP.VBELN = LIPS.VBELN
LEFT JOIN KONV_Z101 KONV_Z101 on KONV_Z101.KNUMV = A.KNUMV AND TO_NUMBER(KONV_Z101.KPOSN) = TO_NUMBER(EKPO.EBELP)
LEFT JOIN KONV_ZWVH KONV_ZWVH on KONV_ZWVH.KNUMV = A.KNUMV AND TO_NUMBER(KONV_ZWVH.KPOSN) = TO_NUMBER(EKPO.EBELP)
) t group by ALLOC_1,ALLOC_2,WAERS,BEDAT,BUYING_ORACLE_WH,oracle_STORE,KNUMV,MATNR,EBELP,GLD,SELLING_PRICE,NETPR)
SELECT /*+ PARALLEL(24) */
ALLOC_MIGR_SEQ.NEXTVAL AS ALLOC_NO,
NULL AS ORDER_NO,
STATUS AS STATUS,
NULL AS PO_TYPE,
'A' AS ALLOC_METHOD,
TO_DATE(BEDAT,'YYYYMMDD') AS RELEASE_DATE,
X.ORACLE_ALLOC_ID AS DOC,
'ALLOC' AS DOC_TYPE,
'N' AS DELETE_FLG,
'1' AS DATASOURCE_NUM_ID,
ALLOC_2 || '-' || MATNR AS INTEGRATION_ID,
BUYING_ORACLE_WH AS SOURCE_WH,
'IBC X-DOCKING ALLOCATION - LEG 2 - ' || ALLOC_2 AS ALLOC_DESC,
'AUTOMATIC' AS ORDER_TYPE,
ALLOC_2 AS COMMENT_DESC,
NULL AS ALLOC_PARENT,
'EG' AS ORIGIN_IND,
DECODE(STATUS,'C', TO_DATE(AEDAT,'YYYYMMDD'),NULL) AS CLOSE_DATE,
MATNR AS PROD_NUM,
oracle_STORE AS ORG_NUM,
TO_DATE(BEDAT,'YYYYMMDD') AS DAY_DT,
NVL(GLD,SELLING_PRICE) AS ITEM_AVG_COST_AMT_LCL,
ship_qty AS TRANSFERRED_QTY,
CASE
WHEN alloc_qty - cancel_qty < 0 THEN
alloc_qty
ELSE
alloc_qty
END AS ALLOCATED_QTY,
CASE
WHEN alloc_qty = 0 AND cancel_qty > 0 THEN
cancel_qty
ELSE
alloc_qty
END AS PRESCALED_QTY,
CASE
WHEN status = 'C' and cancel_qty = 0 THEN
alloc_qty
ELSE
cancel_qty
END AS CANCELLED_QTY,
received_qty AS RECEIVED_QTY,
NULL AS PO_RCVD_QTY,
'N' AS NON_SCALE_IND,
TO_DATE(BEDAT,'YYYYMMDD') + 30 AS IN_STORE_DATE,
WAERS AS DOC_CURR_CODE,
'EUR' AS LOC_CURR_CODE,
'I' AS CTRL_STATUS,
SYSDATE AS CTRL_DATETIME
FROM ALLOC_DETAIL
JOIN XREF_ALLOC x on x.SAP_ALLOC_ID = alloc_1 and item = matnr;
COMMIT;
--------------
BEGIN
DBMS_OUTPUT.PUT_LINE('5. ALLOC HEADER AND DETAIL RAW DATA - IBC REPLNISHMENT ALLOCATION - LEG 2 - LOADING');
END;
/
--------------
--ALLOC HEADER AND DEATAIL
INSERT INTO /*+ PARALLEL(24) */ ORI_ALLOC_RAW (
ALLOC_NO,ORDER_NO,STATUS,PO_TYPE,ALLOC_METHOD,RELEASE_DATE,DOC,DOC_TYPE,DELETE_FLG,DATASOURCE_NUM_ID,INTEGRATION_ID,SOURCE_WH,ALLOC_DESC,ORDER_TYPE,COMMENT_DESC,ALLOC_PARENT,ORIGIN_IND,CLOSE_DATE, --HEADER
PROD_NUM,ORG_NUM,DAY_DT,ITEM_AVG_COST_AMT_LCL,TRANSFERRED_QTY,ALLOCATED_QTY,PRESCALED_QTY,CANCELLED_QTY,RECEIVED_QTY,PO_RCVD_QTY,NON_SCALE_IND,IN_STORE_DATE,DOC_CURR_CODE,LOC_CURR_CODE, --DETAIL
CTRL_STATUS,CTRL_DATETIME --CTRL
)
WITH PUC_ALLOC as (
SELECT /*+ PARALLEL(24) */
*
FROM PUC_IBC_REPL_STORE_TMP
WHERE ALLOC_2 IS NOT NULL
AND STORE IS NOT NULL -- ELIMINAR ALLOCS COM TODAS AS LINHAS CANCELADAS
),
ALLOC_HEADER AS (
SELECT /*+ PARALLEL(24) */
A.*,
EKKO.KNUMV,
EKKO.WAERS,
EKKO.BEDAT
FROM PUC_ALLOC A
JOIN SAP_SLT_PROD_PV1.EKKO EKKO ON EKKO.EBELN = A.ALLOC_2 AND EKKO.BEDAT >= 20240101
),
ALLOC_DETAIL AS (
SELECT /*+ PARALLEL(24) */
ALLOC_1,
ALLOC_2,
WAERS,
BEDAT,
BUYING_ORACLE_WH,
oracle_STORE,
MATNR,
EBELP,
GLD,
SELLING_PRICE,
NETPR,
MIN(STATUS) STATUS,
MAX(AEDAT) AEDAT, -- update date
SUM(tsf_qty) as alloc_qty,
SUM(cancel_qty) as cancel_qty,
SUM(ship_qty) as ship_qty ,
SUM(received_qty) as received_qty
FROM (
SELECT /*+ PARALLEL(24) */
A.*,
EKPO.MATNR,
EKPO.EBELP,
CASE
WHEN EKPO.ELIKZ = 'X' THEN 'C'
WHEN EKPO.LOEKZ = 'L' THEN 'C'
WHEN EKPO.MENGE = EKET.WEMNG THEN 'C'
ELSE 'A'
END STATUS,
EKPO.AEDAT, --UPDATE DATE
EKPO.NETPR,
decode(EKPO.LOEKZ,'L',0,EKPO.MENGE) tsf_qty,
decode(EKPO.LOEKZ,'L',EKPO.MENGE,0) cancel_qty,
nvl(LIPS.LFIMG,0) ship_qty,
EKET.WEMNG as received_qty,
KONV_Z101.KBETR AS GLD,
KONV_ZWVH.KBETR AS SELLING_PRICE
FROM ALLOC_HEADER A
JOIN SAP_SLT_PROD_PV1.EKPO EKPO ON EKPO.EBELN = A.ALLOC_2 AND EKPO.UEBPO = '00000'
LEFT JOIN SAP_SLT_PROD_PV1.EKET EKET on EKET.EBELN = EKPO.EBELN AND EKET.EBELP = EKPO.EBELP
LEFT JOIN SAP_SLT_PROD_PV1.LIPS LIPS on LIPS.VGBEL = EKPO.EBELN AND TO_NUMBER(LIPS.VGPOS) = TO_NUMBER(EKPO.EBELP)
LEFT JOIN SAP_SLT_PROD_PV1.LIKP LIKP on LIKP.VBELN = LIPS.VBELN
LEFT JOIN KONV_Z101 KONV_Z101 on KONV_Z101.KNUMV = A.KNUMV AND TO_NUMBER(KONV_Z101.KPOSN) = TO_NUMBER(EKPO.EBELP)
LEFT JOIN KONV_ZWVH KONV_ZWVH on KONV_ZWVH.KNUMV = A.KNUMV AND TO_NUMBER(KONV_ZWVH.KPOSN) = TO_NUMBER(EKPO.EBELP)
) t group by ALLOC_1,ALLOC_2,WAERS,BEDAT,BUYING_ORACLE_WH,oracle_STORE,KNUMV,MATNR,EBELP,GLD,SELLING_PRICE,NETPR)
SELECT /*+ PARALLEL(24) */
ALLOC_MIGR_SEQ.NEXTVAL AS ALLOC_NO,
NULL AS ORDER_NO,
STATUS AS STATUS,
NULL AS PO_TYPE,
'A' AS ALLOC_METHOD,
TO_DATE(BEDAT,'YYYYMMDD') AS RELEASE_DATE,
X.ORACLE_ALLOC_ID AS DOC,
'ALLOC' AS DOC_TYPE,
'N' AS DELETE_FLG,
'1' AS DATASOURCE_NUM_ID,
ALLOC_2 || '-' || MATNR AS INTEGRATION_ID,
BUYING_ORACLE_WH AS SOURCE_WH,
'IBC REPLNISHMENT ALLOCATION - LEG 2 - ' || ALLOC_2 AS ALLOC_DESC,
'AUTOMATIC' AS ORDER_TYPE,
ALLOC_2 AS COMMENT_DESC,
NULL AS ALLOC_PARENT,
'EG' AS ORIGIN_IND,
DECODE(STATUS,'C', TO_DATE(AEDAT,'YYYYMMDD'),NULL) AS CLOSE_DATE,
MATNR AS PROD_NUM,
oracle_STORE AS ORG_NUM,
TO_DATE(BEDAT,'YYYYMMDD') AS DAY_DT,
NVL(GLD,SELLING_PRICE) AS ITEM_AVG_COST_AMT_LCL,
ship_qty AS TRANSFERRED_QTY,
CASE
WHEN alloc_qty - cancel_qty < 0 THEN
alloc_qty
ELSE
alloc_qty
END AS ALLOCATED_QTY,
CASE
WHEN alloc_qty = 0 AND cancel_qty > 0 THEN
cancel_qty
ELSE
alloc_qty
END AS PRESCALED_QTY,
CASE
WHEN status = 'C' and cancel_qty = 0 THEN
alloc_qty
ELSE
cancel_qty
END AS CANCELLED_QTY,
received_qty AS RECEIVED_QTY,
NULL AS PO_RCVD_QTY,
'N' AS NON_SCALE_IND,
TO_DATE(BEDAT,'YYYYMMDD') + 30 AS IN_STORE_DATE,
WAERS AS DOC_CURR_CODE,
'EUR' AS LOC_CURR_CODE,
'I' AS CTRL_STATUS,
SYSDATE AS CTRL_DATETIME
FROM ALLOC_DETAIL
JOIN XREF_ALLOC x on x.SAP_ALLOC_ID = alloc_1 and item = matnr;
COMMIT;
insert into XREF_ALLOC select comment_desc,alloc_no,prod_num from ORI_ALLOC_RAW where INSTR(alloc_desc, 'LEG 2') > 0 ;
commit;
BEGIN
DBMS_OUTPUT.PUT_LINE('6. STARTING TRANSFER DETAIL VALIDATIONS - REJECTED LOADING');
END;
/
INSERT INTO /*+ PARALLEL(24) */ ORI_ALLOC_REJECTED
WITH alloc_hist as (
SELECT *
FROM ORI_ALLOC_RAW
),
---------------------------------------------------------------------------------
-- 1. a) Validate ORG_NUM is a migrated store or wh (target location)
---------------------------------------------------------------------------------
error_rows_missing_location AS (
SELECT /*+ PARALLEL(24) */
'E' AS LOG_LVL, 'ERROR_MISSING_TO_LOCATION' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM alloc_hist s
WHERE s.ORG_NUM IS NULL
),
---------------------------------------------------------------------------------
-- 2. a) Validate ORG_NUM is a migrated wh (source wh)
---------------------------------------------------------------------------------
error_rows_missing_source_wh AS (
SELECT /*+ PARALLEL(24) */
'E' AS LOG_LVL, 'ERROR_MISSING_SOURCE_WH' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM alloc_hist s
WHERE s.SOURCE_WH IS NULL
),
---------------------------------------------------------------------------------
-- 3. a) Validate items exist in master data
---------------------------------------------------------------------------------
error_rows_missing_items AS (
SELECT /*+ PARALLEL(24) */
'E' AS LOG_LVL, 'ERROR_MISSING_ITEM_MASTER' AS ERROR_MSG, s.*, SYSDATE AS INSERTED_AT
FROM alloc_hist s
WHERE s.PROD_NUM NOT IN (SELECT ITEM FROM dmf2.ITEM_MASTER_CTRL WHERE ctrl_status = 'C') -- validate if it is using dmf or dmf2
),
---------------------------------------------------------------------------------
-- 4. Return all rows with errors/warnings
---------------------------------------------------------------------------------
all_error_rows AS (
SELECT /*+ PARALLEL(24) */ * FROM error_rows_missing_location
union all
SELECT /*+ PARALLEL(24) */ * FROM error_rows_missing_source_wh
union all
SELECT /*+ PARALLEL(24) */ * FROM error_rows_missing_items
)
SELECT /*+ PARALLEL(24) */ * FROM all_error_rows;
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('5. TRANSFER HEADER - LOADING CTRL TABLE ');
END;
/
-- Rows that passed validation are inserted in ORI_ALLOC_HEADER_CTRL
INSERT INTO /*+ PARALLEL(24) */ ORI_ALLOC_HEADER_CTRL
SELECT /*+ PARALLEL(24) */
ALLOC_NO
,ORDER_NO
,STATUS
,PO_TYPE
,ALLOC_METHOD
,RELEASE_DATE
,DOC
,DOC_TYPE
,NULL AS CHANGED_BY_ID
,NULL AS CHANGED_ON_DT
,NULL AS CREATED_BY_ID
,NULL AS CREATED_ON_DT
,NULL AS AUX1_CHANGED_ON_DT
,NULL AS AUX2_CHANGED_ON_DT
,NULL AS AUX3_CHANGED_ON_DT
,NULL AS AUX4_CHANGED_ON_DT
,NULL AS SRC_EFF_FROM_DT
,NULL AS SRC_EFF_TO_DT
,DELETE_FLG
,DATASOURCE_NUM_ID
,INTEGRATION_ID
,NULL AS TENANT_ID
,NULL AS X_CUSTOM
,SOURCE_WH
,ALLOC_DESC
,ORDER_TYPE
,NULL AS CONTEXT_TYPE
,NULL AS CONTEXT_VALUE
,COMMENT_DESC
,NULL AS ALLOC_PARENT
,ORIGIN_IND
,CLOSE_DATE
,CREATE_ID
,CREATE_DATETIME
,CTRL_STATUS
,CTRL_DATETIME
,CTRL_ERROR_MSG
FROM ORI_ALLOC_RAW S
WHERE NOT EXISTS (
SELECT 1
FROM ORI_ALLOC_REJECTED r
WHERE LOG_LVL = 'E'
AND NVL(s.ALLOC_NO, '-1') = NVL(r.ALLOC_NO, '-1')
);
COMMIT;
BEGIN
DBMS_OUTPUT.PUT_LINE('6. TRANSFER DETAIL - LOADING CTRL TABLE ');
END;
/
-- Rows that passed validation are inserted in ORI_ALLOC_DETAIL_CTRL
INSERT INTO /*+ PARALLEL(24) */ ORI_ALLOC_DETAIL_CTRL
SELECT /*+ PARALLEL(24) */
ALLOC_NO
,PROD_NUM
,ORG_NUM
,DAY_DT
,ORDER_NO
,ITEM_AVG_COST_AMT_LCL
,TRANSFERRED_QTY
,ALLOCATED_QTY
,PRESCALED_QTY
,NULL AS DISTRO_QTY
,NULL AS SELECTED_QTY
,CANCELLED_QTY
,RECEIVED_QTY
,NULL AS RECONCILED_QTY
,PO_RCVD_QTY
,NON_SCALE_IND
,IN_STORE_DATE
,NULL AS WF_ORDER_NO
--,NULL AS EXCHANGE_DT
,NULL AS CREATED_BY_ID
,NULL AS CREATED_ON_DT
,NULL AS CHANGED_BY_ID
,NULL AS CHANGED_ON_DT
,NULL AS AUX1_CHANGED_ON_DT
,NULL AS AUX2_CHANGED_ON_DT
,NULL AS AUX3_CHANGED_ON_DT
,NULL AS AUX4_CHANGED_ON_DT
,NULL AS
,NULL AS SRC_EFF_FROM_DT
,DELETE_FLG AS SRC_TO_FROM_DT
,DOC_CURR_CODE
,LOC_CURR_CODE
--,NULL AS ETL_THREAD_VAL
--,NULL AS GLOBAL1_EXCHANGE_RATE
--,NULL AS GLOBAL2_EXCHANGE_RATE
--,NULL AS GLOBAL3_EXCHANGE_RATE
,DATASOURCE_NUM_ID
,INTEGRATION_ID
--,NULL AS LOC_EXCHANGE_RATE
,NULL AS TENANT_ID
,NULL AS X_CUSTOM
,CREATE_ID
,CREATE_DATETIME
,CTRL_STATUS
,CTRL_DATETIME
,CTRL_ERROR_MSG
FROM ORI_ALLOC_RAW S
WHERE NOT EXISTS (
SELECT 1
FROM ORI_ALLOC_REJECTED r
WHERE LOG_LVL = 'E'
AND NVL(s.ALLOC_NO, '-1') = NVL(r.ALLOC_NO, '-1')
);
COMMIT;
-----------------------------
select 'TOTAL OF LINES', count(1) from ORI_ALLOC_raw
union all
SELECT error_msg, count(1) from ORI_ALLOc_rejected group by error_msg;
SELECT * FROM ORI_ALLOC_HEADER_CTRL;
SELECT * FROM ORI_ALLOC_DETAIL_CTRL;
SELECT COUNT(1) FROM ORI_ALLOC_HEADER_CTRL;
SELECT COUNT(1) FROM ORI_ALLOC_DETAIL_CTRL;