meet my clipboard Options

[Guest_jaffar_*]

dati paste/ctrl-v. nu trisati !

incep eu:

http://www.bimmerinfo.com/E36_OBC_tricks.htm

[Darren]

truncate table syn_mf602_dh;

/********************************/

insert into syn_mf602_DH (cod_div_wiz, cod_fil_wiz, itemkey, Demand_Counter, First_Invoice, Sort, Demand_History)
(SELECT st.cod_divizie, st.cod_filiala, stm.itemkey,
0 DemandCounter, min(nvl(st.valuedate,next_day(sysdate,'Sunday')-7)) finv,
TO_CHAR (st.valuedate, 'YYYYIW') AS sort,
SUM (nvl(stm.supplyquantity,0)) sales
FROM wiz_company100.stock@cons.world st,
wiz_company100.stockmoves@cons.world stm
WHERE st.ID = stm.stockid
AND st.status =1
AND st.documentid IN (1, 2, 9)
AND (st.valuedate >= (NEXT_DAY (SYSDATE, 'Sunday') - (105 * 7)))
GROUP BY st.cod_divizie, st.cod_filiala,
stm.itemkey,
TO_CHAR (st.valuedate, 'YYYYIW')
union all
SELECT st.cod_divizie, st.cod_filiala, stm.itemkey,
0 DemandCounter, min(nvl(st.valuedate,next_day(sysdate,'Sunday')-7)) finv,
TO_CHAR (st.valuedate, 'YYYYIW') AS sort,
SUM (nvl(stm.supplyquantity,0)) sales
FROM wiz_company250.stock@cons.world st,
wiz_company250.stockmoves@cons.world stm
WHERE st.ID = stm.stockid
AND st.status =1
AND st.documentid IN (1, 2, 9)
AND (st.valuedate >= (NEXT_DAY (SYSDATE, 'Sunday') - (105 * 7)))
GROUP BY st.cod_divizie, st.cod_filiala,
stm.itemkey,
TO_CHAR (st.valuedate, 'YYYYIW')
union all
SELECT st.cod_divizie, st.cod_filiala, stm.itemkey,
0 DemandCounter, min(nvl(st.valuedate,next_day(sysdate,'Sunday')-7)) finv,
TO_CHAR (st.valuedate, 'YYYYIW') AS sort,
SUM (nvl(stm.supplyquantity,0)) sales
FROM wiz_company500.stock@cons.world st,
wiz_company500.stockmoves@cons.world stm
WHERE st.ID = stm.stockid
AND st.status =1
AND st.documentid IN (1, 2, 9)
AND (st.valuedate >= (NEXT_DAY (SYSDATE, 'Sunday') - (105 * 7)))
GROUP BY st.cod_divizie, st.cod_filiala,
stm.itemkey,
TO_CHAR (st.valuedate, 'YYYYIW')
union all
SELECT st.cod_divizie, st.cod_filiala, stm.itemkey,
0 DemandCounter, min(nvl(st.valuedate,next_day(sysdate,'Sunday')-7)) finv,
TO_CHAR (st.valuedate, 'YYYYIW') AS sort,
SUM (nvl(stm.supplyquantity,0)) sales
FROM wiz_company800.stock@cons.world st,
wiz_company800.stockmoves@cons.world stm
WHERE st.ID = stm.stockid
AND st.status =1
AND st.documentid IN (1, 2, 9)
AND (st.valuedate >= (NEXT_DAY (SYSDATE, 'Sunday') - (105 * 7)))
GROUP BY st.cod_divizie, st.cod_filiala,
stm.itemkey,
TO_CHAR (st.valuedate, 'YYYYIW')
);
commit;


/************************************/

update SYN_MF602_DH set
demand_counter=((to_number(TO_CHAR (sysdate, 'YYYY'))-to_number(to_char(first_invoice,'YYYY')))*52+(to_number(to_char(sysdate,'IW'))-to_number(to_char(first_invoice,'IW'))))
;

commit;




/***********************************/

truncate table syn_mf602_dh_cons;
insert into syn_mf602_DH_cons (prod_code, Demand_History, Demand_Counter, Sort, First_Invoice)
(select fd.Item_prefix || ia.sync_group, sum(nvl(dh.DEMAND_HISTORY,0)), max(dh.DEMAND_COUNTER), dh.SORT, min(dh.first_invoice)
from items_all ia, syn_mf602_DH dh, (select fdes.*, i.itemkey from forecast_desagg fdes, items_all i where i.cod_divizie=fdes.cod_divizie and fdes.man_id<>0 and (i.sortgroup between fdes.FROM_SGRP and fdes.TO_SGRP) and fdes.interface_id=1) fd
where fd.cod_div_wiz=dh.cod_div_wiz and ia.cod_divizie=fd.cod_divizie and
fd.itemkey=dh.itemkey and ia.itemkey=fd.itemkey and
dh.cod_fil_wiz=fd.cod_fil_wiz
group by fd.Item_prefix || ia.sync_group, fd.Item_prefix, ia.sync_group, dh.sort);
commit;

/***********************************/


truncate table syn_mf732_dh_cons;
insert into syn_mf732_dh_cons (prod_code, sort, demand_history)
(select prod_code, sort, demand_history from syn_mf602_dh_cons);
commit;


/***********************************/

truncate table syn_mf602;
insert into SYN_MF602 (PROD_CODE, ITEM_PREFIX, SYNC_GROUP, DESCRIPTION, UNIT_COST, LEAD_TIME, MIN_OQ, MAX_OQ, MOQ,
SEASONAL_KEY, PARAM_REF_KEY, DEMAND_HISTORY, BACK_ORDERS, CURRENT_STOCK, OUTSTANDING_ORDERS, DEMAND_TO_DATE, DEMAND_COUNTER, TOTAL_BACK_ORDERS,FREE_TEXT1, FREE_TEXT2, FREE_TEXT3, Multi_VAU, Manual_Buffer_Stock, Manual_Buffer_Override, SUPERSEDED_KEY)
(select fd.Item_prefix || ia.sync_group as prod_code,fd.Item_prefix, ia.sync_group, min(ia.description) description, max(ia.pret_ach*ia.status) pret_ach, max(ia.lead_time) lead_time,
0 as MIN_OQ, 9999999 as MAX_OQ, max(ia.MOQ_MULTIPLIER) MOQ,
'ZZZZZZZZZZZZZZZ' as SEASONAL_KEY,
'ZZ' as PARAM_REF_KEY,
0 as DEMAND_HISTORY,
0 as BACK_ORDERS,
sum (nvl(DA.STOC_AGENTI_REAL,0)+nvl(DA.STOC_DEPOZIT_REAL,0)) Current_Stock,
0 as Total_Outstand_Ord,
0 as DemandToDate,
0 as DemandCounter,
sum(nvl(DA.Comenzi_viitor,0)) TotalBackOrders,
min(ia.filter) as free_text1,
substr(min(ia.category),1,25) as free_text2,
'' as free_text3,
max(nvl(ia.MULTI_VAU,0)) as multi_vau,
0 as manual_buffer_stock,
'' as manual_buffer_override,
max(nvl(ia.superseded_pk,'')) as superseded_key
from items_all ia, dssrprd da, (select fdes.*, i.itemkey from forecast_desagg fdes, items_all i where i.cod_divizie=fdes.cod_divizie and fdes.man_id<>0 and (i.sortgroup between fdes.FROM_SGRP and fdes.TO_SGRP) and fdes.interface_id=1) fd
where fd.cod_divizie=da.cod_divizie(+) and ia.cod_divizie=fd.cod_divizie and
fd.itemkey=da.cod_produs(+) and ia.itemkey=fd.itemkey and
da.cod_filiala(+)=fd.cod_filiala
group by fd.Item_prefix || ia.sync_group, fd.Item_prefix, ia.sync_group);
commit;