Source: DMF/PuC/AIF/Price_Clearance.sql
SELECT COUNT(*) FROM ORI_PRICE_CLEARANCE_RAW; -- 589353707
SELECT COUNT(*) FROM INIT_PRICE_RAW; -- 580791037
SELECT COUNT(*) FROM PRICE_HISTORY_RAW; -- 219504965
SELECT * FROM INIT_PRICE_RAW WHERE ITEM = '000000186504810040' AND STORE = 385; -- 580791037
SELECT * FROM PRICE_HISTORY_RAW WHERE ITEM = '000000186504810040' AND STORE = 385; -- 219504965
--SELECT * FROM ORI_PRICE_CLEARANCE_RAW WHERE ITEM = '000000186504810040' AND STORE = 385; -- 589353707
SELECT * FROM dmf2.INIT_PRICE_RAW where store = 437;
select * from dmf2.PRICE_HISTORY_RAW where store = 773;
SELECT * FROM dmf2.INIT_PRICE_delta_RAW where store = 773;
select * from dmf2.PRICE_HISTORY_delta_RAW where store = 773;
select * from ori_price_ctrl
insert ori_price_ctrl select 773 from ori_price_ctrl where store = 446; -- copy data changing ORG_NUM from 446 to 773
insert ori_price_ctrl select 772 from ori_price_ctrl where store = 446; -- copy data changing ORG_NUM from 446 to 772
insert ori_price_ctrl select 437 from ori_price_ctrl where store = 446; -- copy data changing ORG_NUM from 446 to 437
SELECT * FROM store_add_ctrl WHERE online_store_ind = 'Y' ;
------------------------------
-- black price - regular price
select distinct kschl from SAP_SLT_PROD_PV1.A073;
select * from SAP_SLT_PROD_PV1.A073 where matnr = '000000000001965209';
SELECT a.matnr ITEM
,0 ZONE_NODE_TYPE
--,il.loc ZONE_NODE_ID
,xr.oracle_id as ZONE_NODE_ID
,TO_DATE(a.datab, 'YYYYMMDD') EFFECTIVE_DATE
,NULL DIFF_ID
,TO_NUMBER(a.vtweg, '999999999999999.9999') PRICE_CHANGE_RETAIL
,NULL PRICE_CHANGE_GROUPING
,NULL PRICE_CHANGE_GROUP_DESCRIPTION
,NULL REASON_CODE
,4 STATUS
,'EA' SELLING_UOM
--,PCK_RCIB_INTEGRATION_GV.G_ctrl_status_unprocessed CTRL_STATUS
--,q.seq_no LAST_CHANGE_SEQ_NO
,'DMF_DELTA' CREATE_ID
,SYSDATE CREATE_DATETIME
,NULL LAST_UPDATE_ID
,NULL LAST_UPDATE_DATETIME
,NULL CTRL_ID
FROM --queue_price_change_delta q
--,
sap_slt_prod_pv1.a073 a
,sap_slt_prod_pv1.t001w t
--,mig_il il
,xref_location xr
WHERE 1=1
--q.ctrl_status = 'N' -- Status New
--AND q.target_event = RCIB_API_REGPRCCHG.REGPRCCHG_ADD
--AND q.item = a.matnr
--AND q.sales_org = a.vkorg
--AND q.effective_date = a.datab
AND a.vkorg = t.vkorg
AND a.datbi = '99991231'
AND t.vlfkz = 'A' -- Only stores
AND REGEXP_LIKE(t.werks, '^[0-9]') -- Only werks represented by numbers
AND t.werks = xr.sap_id
AND xr.oracle_type = 'S'
--AND il.loc = xr.oracle_id
--AND il.item = a.matnr
and t.werks in ('0438')
order by a.datab desc
;
select * from sap_slt_prod_pv1.t001w
-- red price - clearance
select distinct kschl from SAP_SLT_PROD_PV1.A071;
select * from SAP_SLT_PROD_PV1.A071 where matnr = '000000000001965209' and werks = '0003';
select * from SAP_SLT_PROD_PV1.konp where KNUMH = '3542708856';
SELECT A.MATNR, M.MATNR, A.DATBI, A.DATAB, A.KNUMH, KBETR
FROM SAP_SLT_PROD_PV1.A071 A,
SAP_SLT_PROD_PV1.MARA M,
SAP_SLT_PROD_PV1.KONP K
WHERE 1=1
AND M.SATNR = A.MATNR
AND A.KNUMH = K.KNUMH
AND TO_DATE(A.DATBI, 'YYYYMMDD') = TO_DATE('99991231', 'YYYYMMDD')
AND M.matnr = '000000196520900540'
AND WERKS = '0003';
select 1 from SAP_SLT_PROD_PV1.t001w where length(werks) <> 4
SELECT *
FROM SAP_SLT_PROD_PV1.A073 A;
SELECT *
FROM SAP_SLT_PROD_PV1.A073 A,
SAP_SLT_PROD_PV1.MARA M,
SAP_SLT_PROD_PV1.KONP K
WHERE M.SATNR = A.MATNR
AND A.KNUMH = K.KNUMH
AND A.KSCHL = K.KSCHL
AND TO_DATE(A.DATBI, 'YYYYMMDD') = TO_DATE('99991231', 'YYYYMMDD')
AND A.MATNR = '000000000002145573';
select * from item_master_ctrl where item = '000000196520900540';
select satnr, m.* from SAP_SLT_PROD_PV1.mara m where matnr = '000000196520900540';
select * from rpm_clearance_detail_ctrl where ctrl_status <> 'A';
-----------------
SELECT DISTINCT
A.MATNR,
M.MATNR,
NULL,
0,
xr.oracle_id,
A.VTWEG,
NULL,
NULL,
NULL,
NULL,
'DMF_FULL',
SYSDATE,
'A', -- approved - PROCESS_FULL_TO_RCIB_STG vai fazer lock destes registos
SYSDATE,
NULL
FROM SAP_SLT_PROD_PV1.MARA M,
SAP_SLT_PROD_PV1.A071 A,
JOIN XREF_LOCATION XR
ON LTRIM(XR.SAP_ID,'0') = LTRIM(A.WERKS,'0')
AND XR.SAP_TYPE = 'S'
AND XR.ORACLE_TYPE = 'S'
WHERE TO_DATE(A.DATBI, 'YYYYMMDD') = TO_DATE('99991231', 'YYYYMMDD')
AND M.SATNR = A.MATNR
AND EXISTS (SELECT 1
FROM ITEM_LOC_CTRL IL
WHERE IL.ITEM = M.MATNR
AND IL.IL_TYPE <> 'Z'
AND IL.LOC_TYPE = 'S'
AND IL.CTRL_STATUS = 'C'
AND IL.LOC = XR.ORACLE_ID) and M.matnr = '000000196520900540' AND xr.oracle_id = 3;
MERGE INTO item_loc il
USING (
SELECT /*+ MATERIALIZE */
im.item,
--rl.location,
r.standard_retail
FROM rpm_item_zone_price r
JOIN item_master im
ON im.item = r.item
/*JOIN rpm_zone_location rl
ON rl.zone_id = r.zone_id*/
WHERE im.tran_level = im.item_level
AND im.item_level = 1
AND r.standard_retail > 0
) src
ON (
il.item = src.item --AND il.loc = src.location
)
WHEN MATCHED THEN
UPDATE SET
il.unit_retail = src.standard_retail,
il.regular_unit_retail = src.standard_retail,
il.selling_unit_retail = src.standard_retail;
DECLARE
l_rows NUMBER;
BEGIN
LOOP
UPDATE /*+ PARALLEL(item_loc, 8) */ item_loc
SET regular_unit_retail = unit_retail,
selling_unit_retail = unit_retail
WHERE ROWID IN (
SELECT ROWID
FROM item_loc
WHERE loc_type = 'W'
AND unit_retail > 0
AND SELLING_UNIT_RETAIL IS NULL
AND ROWNUM <= 100000
);
l_rows := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(l_rows || ' rows updated.');
EXIT WHEN l_rows = 0;
END LOOP;
END;
/
MERGE INTO item_loc w
USING (
SELECT il.item,
st.default_wh AS wh,
MIN(il.unit_retail) AS unit_retail
FROM item_loc il
JOIN item_master im
ON im.item = il.item and im.tran_level = im.item_level
JOIN store st
ON st.store = il.loc
WHERE il.loc_type = 'S'
and unit_retail > 0
GROUP BY il.item, st.default_wh
) src
ON (
w.item = src.item
AND w.loc = src.wh
AND w.loc_type = 'W'
)
WHEN MATCHED THEN
UPDATE SET w.unit_retail = src.unit_retail--,
--w.REGULAR_UNIT_RETAIL = src.unit_retail,
--w.SELLING_UNIT_RETAIL = src.unit_retail
;
DECLARE
l_sql CLOB;
BEGIN
FOR r IN (
select distinct z.item, standard_retail, zone_id from dmf2.rpm_item_zone_price_ctrl z, dmf2.item_master_ctrl c where c.ctrl_status = 'C' and c.item = z.item and c.item_level = c.tran_level and c.item_level = 1
)
LOOP
l_sql :=
'UPDATE item_loc '
|| 'SET unit_retail = ' || NVL(TO_CHAR(r.standard_retail), 'NULL')
|| ', regular_unit_retail = ' || NVL(TO_CHAR(r.standard_retail), 'NULL')
|| ', selling_unit_retail = ' || NVL(TO_CHAR(r.standard_retail), 'NULL')
|| ' WHERE item = ''' || r.item || '''' || ';';
DBMS_OUTPUT.PUT_LINE(l_sql);
END LOOP;
END;
/