Source: DMF/PuC/AIF/TSF_Hist_trancode3032.sql
CREATE TABLE ORI_TRANSFER_INV_CTRL NOLOGGING PARALLEL 24 AS
SELECT /*+ PARALLEL(24) */
ITEM,
ORG_NUM,
DAY_DT,
FROM_ORG_NUM,
TSF_TYPE_CODE,
sum(TSF_TO_LOC_QTY) as TSF_TO_LOC_QTY,
sum(TSF_TO_LOC_COST_AMT_LCL) as TSF_TO_LOC_COST_AMT_LCL,
sum(TSF_TO_LOC_RTL_AMT_LCL) as TSF_TO_LOC_RTL_AMT_LCL,
sum(TSF_FROM_LOC_QTY) as TSF_FROM_LOC_QTY,
sum(TSF_FROM_LOC_COST_AMT_LCL) as TSF_FROM_LOC_COST_AMT_LCL,
sum(TSF_FROM_LOC_RTL_AMT_LCL) as TSF_FROM_LOC_RTL_AMT_LCL,
'N' AS FROM_CLEARANCE_FLG,
'N' AS TO_CLEARANCE_FLG,
LOC_CURR_CODE,
FROM_DOC_CURR_CODE,
FROM_LOC_CURR_CODE,
DOC_CURR_CODE,
DELETE_FLG,
DATASOURCE_NUM_ID
FROM (
select /*+ PARALLEL(24) */
PROD_IT_NUM AS ITEM,
ORG_NUM AS ORG_NUM,
D.DAY_DT AS DAY_DT,
FROM_LOC AS FROM_ORG_NUM,
CASE
WHEN TSF_TYPE = 'IC' THEN
'I'
ELSE
'B'
END AS TSF_TYPE_CODE,
SHIP_QTY AS TSF_TO_LOC_QTY,
SHIP_QTY*TSF_AVG_COST_AMT_LCL AS TSF_TO_LOC_COST_AMT_LCL,
/*SHIP_QTY * (select /*+ PARALLEL(24) */ /*SELLING_UNIT_RTL_AMT_LCL
from (SELECT /*+ PARALLEL(24) */ /**
FROM PRICE_HIST_RAW_TSF hist
WHERE d.PROD_IT_NUM = hist.ITEM
and d.org_num = hist.ORG_NUM
and D.DAY_DT >= hist.DAY_DT
order by hist.DAY_DT desc)
where rownum = 1) AS TSF_TO_LOC_RTL_AMT_LCL,*/
SHIP_QTY*TSF_AVG_COST_AMT_LCL AS TSF_TO_LOC_RTL_AMT_LCL, -- VALIDAR
SHIP_QTY AS TSF_FROM_LOC_QTY,
SHIP_QTY*TSF_AVG_COST_AMT_LCL AS TSF_FROM_LOC_COST_AMT_LCL,
/*SHIP_QTY * (select /*+ PARALLEL(24) */ /*SELLING_UNIT_RTL_AMT_LCL
from (SELECT /*+ PARALLEL(24) */ /**
FROM PRICE_HIST_RAW_TSF hist
WHERE d.PROD_IT_NUM = hist.ITEM
and h.FROM_LOC = hist.ORG_NUM
and D.DAY_DT >= hist.DAY_DT
order by hist.DAY_DT desc)
where rownum = 1) AS TSF_FROM_LOC_RTL_AMT_LCL,*/
SHIP_QTY*TSF_AVG_COST_AMT_LCL AS TSF_FROM_LOC_RTL_AMT_LCL,
CAST(NULL AS VARCHAR2(1)) AS FROM_CLEARANCE_FLG,
CAST(NULL AS VARCHAR2(1)) AS TO_CLEARANCE_FLG,
CAST(NULL AS NUMBER(22,7)) AS GLOBAL1_EXCHANGE_RATE,
CAST(NULL AS NUMBER(22,7)) AS GLOBAL2_EXCHANGE_RATE,
CAST(NULL AS NUMBER(22,7)) AS GLOBAL3_EXCHANGE_RATE,
CAST(NULL AS NUMBER(22,7)) AS LOC_EXCHANGE_RATE,
LOC_CURR_CODE AS LOC_CURR_CODE,
DOC_CURR_CODE AS FROM_DOC_CURR_CODE,
LOC_CURR_CODE AS FROM_LOC_CURR_CODE,
CAST(NULL AS NUMBER(22,7)) AS FROM_LOC_EXCHANGE_RATE,
CAST(NULL AS NUMBER(22,7)) AS FROM_GLOBAL1_EXCHANGE_RATE,
CAST(NULL AS NUMBER(22,7)) AS FROM_GLOBAL2_EXCHANGE_RATE,
CAST(NULL AS NUMBER(22,7)) AS FROM_GLOBAL3_EXCHANGE_RATE,
DOC_CURR_CODE AS DOC_CURR_CODE,
CAST(NULL AS NUMBER(4)) AS ETL_THREAD_VAL,
'N' AS DELETE_FLG,
'1' AS DATASOURCE_NUM_ID
from ORI_TSF_HEADER_CTRL h,
ORI_TSF_DETAIL_CTRL d
where h.tsf_no = d.tsf_no
AND SHIP_QTY > 0
UNION ALL
-- alloc
select /*+ PARALLEL(24) */
PROD_NUM AS ITEM,
ORG_NUM AS ORG_NUM,
D.DAY_DT AS DAY_DT,
SOURCE_WH AS FROM_ORG_NUM,
'B' AS TSF_TYPE_CODE,
TRANSFERRED_QTY AS TSF_TO_LOC_QTY,
TRANSFERRED_QTY*ITEM_AVG_COST_AMT_LCL AS TSF_TO_LOC_COST_AMT_LCL,
/*TRANSFERRED_QTY * (select /*+ PARALLEL(24) */ /*SELLING_UNIT_RTL_AMT_LCL
from (SELECT /*+ PARALLEL(24) */ /**
FROM PRICE_HIST_RAW_TSF hist
WHERE d.PROD_IT_NUM = hist.ITEM
and d.org_num = hist.ORG_NUM
and D.DAY_DT >= hist.DAY_DT
order by hist.DAY_DT desc)
where rownum = 1) AS TSF_TO_LOC_RTL_AMT_LCL,*/
TRANSFERRED_QTY*ITEM_AVG_COST_AMT_LCL AS TSF_TO_LOC_RTL_AMT_LCL, -- VALIDAR
TRANSFERRED_QTY AS TSF_FROM_LOC_QTY,
TRANSFERRED_QTY*ITEM_AVG_COST_AMT_LCL AS TSF_FROM_LOC_COST_AMT_LCL,
/*TRANSFERRED_QTY * (select /*+ PARALLEL(24) */ /*SELLING_UNIT_RTL_AMT_LCL
from (SELECT /*+ PARALLEL(24) */ /**
FROM PRICE_HIST_RAW_TSF hist
WHERE d.PROD_IT_NUM = hist.ITEM
and h.FROM_LOC = hist.ORG_NUM
and D.DAY_DT >= hist.DAY_DT
order by hist.DAY_DT desc)
where rownum = 1) AS TSF_FROM_LOC_RTL_AMT_LCL,*/
TRANSFERRED_QTY*ITEM_AVG_COST_AMT_LCL AS TSF_FROM_LOC_RTL_AMT_LCL,
CAST(NULL AS VARCHAR2(1)) AS FROM_CLEARANCE_FLG,
CAST(NULL AS VARCHAR2(1)) AS TO_CLEARANCE_FLG,
CAST(NULL AS NUMBER(22,7)) AS GLOBAL1_EXCHANGE_RATE,
CAST(NULL AS NUMBER(22,7)) AS GLOBAL2_EXCHANGE_RATE,
CAST(NULL AS NUMBER(22,7)) AS GLOBAL3_EXCHANGE_RATE,
CAST(NULL AS NUMBER(22,7)) AS LOC_EXCHANGE_RATE,
LOC_CURR_CODE AS LOC_CURR_CODE,
DOC_CURR_CODE AS FROM_DOC_CURR_CODE,
LOC_CURR_CODE AS FROM_LOC_CURR_CODE,
CAST(NULL AS NUMBER(22,7)) AS FROM_LOC_EXCHANGE_RATE,
CAST(NULL AS NUMBER(22,7)) AS FROM_GLOBAL1_EXCHANGE_RATE,
CAST(NULL AS NUMBER(22,7)) AS FROM_GLOBAL2_EXCHANGE_RATE,
CAST(NULL AS NUMBER(22,7)) AS FROM_GLOBAL3_EXCHANGE_RATE,
DOC_CURR_CODE AS DOC_CURR_CODE,
CAST(NULL AS NUMBER(4)) AS ETL_THREAD_VAL,
'N' AS DELETE_FLG,
'1' AS DATASOURCE_NUM_ID
from ORI_ALLOC_HEADER_CTRL h,
ORI_ALLOC_DETAIL_CTRL d
where h.ALLOC_no = d.ALLOC_no
AND TRANSFERRED_QTY > 0
)
GROUP BY ITEM, ORG_NUM, DAY_DT, FROM_ORG_NUM, TSF_TYPE_CODE, FROM_CLEARANCE_FLG, TO_CLEARANCE_FLG, LOC_CURR_CODE, FROM_DOC_CURR_CODE, FROM_LOC_CURR_CODE, DOC_CURR_CODE, DELETE_FLG, DATASOURCE_NUM_ID
;
select * from ORI_TRANSFER_INV_CTRL
/*drop table PRICE_HIST_RAW_TSF;
-- quase 2h para criar a tabela
CREATE TABLE PRICE_HIST_RAW_TSF NOLOGGING PARALLEL 24 AS
select /*+ PARALLEL(24) */
/* r.*
from ORI_PRICE_CTRL r
join ORI_TSF_DETAIL_CTRL t on t.PROD_IT_NUM = r.item
left join V_PUC_TSF v on v.tsf = t.tsf_no and v.from_loc_type = 'S' and r.ORG_NUM = v.from_loc
left join V_PUC_TSF v2 on v2.tsf = t.tsf_no and v2.to_loc_type = 'S' and r.ORG_NUM = v2.to_loc
where v.tsf is null or v2.tsf is null
;
CREATE INDEX PRICE_HIST_RAW_TSF
ON ORI_PRICE_CTRL (ITEM, ORG_NUM, DAY_DT DESC, SELLING_UNIT_RTL_AMT_LCL);
*/
-- 000000192522803132 2992 23-SEP-25 415 B 2 44.73 2 44.73 EUR EUR EUR EUR N 1
/*
SELECT /*+ PARALLEL(24) */
/* price FROM PRICE_HISTORY_RAW WHERE ITEM IN (
'000000113217900010') and start_date <= '08-NOV-24' order by start_date desc
FETCH FIRST 1 ROW ONLY;*/
select count(1) from dmf.ori_price_clearance_raw;