CREATE OR REPLACE PROCEDURE C##AD.TEST1 (p_Pinstance_ID IN NUMBER)
AS
v_Resultstr VARCHAR2 (2000);
v_Message VARCHAR2 (2000);
v_Result NUMBER := 1;
v_Record_ID NUMBER;
v_AD_User_ID NUMBER;
-- 最小bucket 就是一小時如果備用去一分鐘這一小時就不能再給[不同工單使用]
-- 最小bucket 就是10分鐘時如果備用去一分鐘這10分鐘就不能再給[不同工單使用]
--每一Resource 每小時負荷 只要有一分鐘被占用就算 [已使用]
--每一天如過滿載紀錄下來
--從今天起算 100天排程
--CREATE TYPE zm_varray IS VARRAY(144000) OF INTEGER; -- 100天*24小時*60分
--CREATE TYPE zh_varray IS VARRAY(2400) OF INTEGER; -- 100天*24小時
--CREATE TYPE zd_varray IS VARRAY(100) OF INTEGER; -- 100天
-- CREATE TABLE z_varray_resource(z_resource_id NUMBER(10,0), z_zm zm_varray, zh zh_varray, zd zd_varray);
-- select * from z_varray_resource
CURSOR cur_parameter (pp_pinstance NUMBER)
IS
SELECT i.AD_Client_ID,
i.AD_Org_ID,
i.Record_id,
i.AD_User_ID,
p.ParameterName,
p.p_String, p.p_String_TO,
p.p_Number, p.p_Number_TO,
p.p_Date , p.p_Date_TO
FROM AD_pinstance I, AD_pinstance_para p
WHERE i.AD_Pinstance_ID = p.AD_Pinstance_ID(+)
AND i.AD_pinstance_ID = pp_Pinstance
ORDER BY p.SeqNo;
CURSOR cur_PP
IS
SELECT Value, Name, Z_Item_ID, Z_UOM_ID, NVL(Qty,0) Qty, Z_PP_ID, StartDate, CompleteDate
FROM Z_PP
ORDER BY Value;
CURSOR cur_ItemOperation (x_Z_Item_ID NUMBER)
IS
SELECT i.Z_Item_ID, i.Name ItemName,
o.Z_Operation_ID, o.Name OperationName,
r.Z_Resource_ID, r.Name ResourceName,
u.Z_UOM_ID, u.Name UOMName,
NVL(io.Usage ,0) Usage,
NVL(io.Min_Transfer_Qty,0) Min_Transfer_Qty,
io.Z_ItemOperation_ID
FROM Z_ItemOperation io
INNER JOIN Z_Resource r ON io.Z_Resource_ID=r.Z_Resource_ID
INNER JOIN Z_Item i ON io.Z_Item_ID=i.Z_Item_ID
INNER JOIN Z_Operation o ON io.Z_Operation_ID=o.Z_Operation_ID
INNER JOIN Z_UOM u ON io.Z_UOM_ID=u.Z_UOM_ID
WHERE io.Z_Item_ID=x_Z_Item_ID
ORDER BY io.Value;
CURSOR cur_Resource ( x_Z_Resource_ID NUMBER)
IS
SELECT r.Value, r.Name, r.Z_Shift_ID
FROM Z_Resource r
INNER JOIN Z_Shift s ON s.Z_Shift_ID=r.Z_Shift_ID
WHERE Z_Resource_ID=x_Z_Resource_ID;
/*
"9-000-A0";"切削 ";"五號研磨機";"MIN";10.0;99999
"9-000-A0";"切削 ";"五號研磨機";"MIN";10.0;99999
"9-000-A0";"切削 ";"組裝二線";"MIN";1;0
*/
CURSOR cur_ProductionPlan ( x_Z_Resource_ID NUMBER)
IS
SELECT pp.Value PP_Value,pp.Name PP_Name,i.Name ItemName, NVL(Qty,0) Qty, StartDate, CompleteDate
FROM Z_PP pp
INNER JOIN Z_Item i ON pp.Z_Item_ID=i.Z_Item_ID;
-- UPDATE Z_PP SET STARTDATE = TO_DATE('2014-02-01','YYYY-MM-DD') WHERE VALUE='100';
-- UPDATE Z_PP SET STARTDATE = TO_DATE('2014-02-01','YYYY-MM-DD') WHERE VALUE='100';;
-- SELECT * FROM Z_PP
--ALTER TABLE Z_PP DROP COLUMN STRATDATE
--ADD StartDate DATE
--ALTER TABLE Z_PP ADD CompleteDate DATE
CURSOR cur_Shift ( x_Z_Shift_ID NUMBER)
IS
SELECT Value,Name,FROMTIME,TOTIME,
TO_NUMBER(TO_CHAR(FROMTIME,'HH24')) F_HH,TO_NUMBER(TO_CHAR(FROMTIME,'MI')) F_MI,
TO_NUMBER(TO_CHAR(TOTIME, 'HH24')) T_HH,TO_NUMBER(TO_CHAR(TOTIME,'MI')) T_MI
FROM Z_SHIFT;
-- WHERE Z_Shift_ID=x_Z_Shift_ID;
/*
"991";"991";"9-000-A0";35;0;0
"100";"100";"9-000-A0";20;0;0
*/
-- C_QuotationLine.Line:=@SQL=SELECT COALESCE(MAX(Line),0)+10 AS DefaultValue FROM C_QuotationLine WHERE C_Quotation_ID=@C_Quotation_ID@
-- C_QuotationLine.M_Product_ID:=RJ_QuoLine.M_Product_ID
-- C_QuotationLine.QtyEntered:=RJ_QuoLine.QtyFG
v_SHIT_HH NUMBER (10,0);
v_D NUMBER (10,0);
v_H NUMBER (10,0);
v_M NUMBER (10,0);
v_Qty NUMBER (10,0);
v_Qty_B NUMBER (10,0);
v_1 NUMBER (10,0);
v_2 NUMBER (10,0);
v_3 NUMBER (10,0);
v_4 NUMBER (10,0);
vhh NUMBER (10,0);
vdd NUMBER (10,0);
vmm NUMBER (10,0);
xhh NUMBER (10,0);
xdd NUMBER (10,0);
xmm NUMBER (10,0);
v_Day NUMBER (10,0);
v_Min NUMBER (10,0);
v_Min_Transfer_Qty NUMBER (10,0);
v_zm zm_varray; -- IS VARRAY(144000) OF INTEGER
v_zm_clean zm_varray;
v_upd CHAR(1) := 'N';
v_s_str NVARCHAR2(200);
v_c_str NVARCHAR2(200);
v_item_last NUMBER (10,0);
v_seq NUMBER (10,0);
v_seq1 NUMBER (10,0);
v_open NUMBER (10,0);
v_close NUMBER (10,0);
v_tMI NUMBER (10,0);
v_fMI NUMBER (10,0);
--SELECT * FROM Z_Resource_Detail
--SELECT * FROM Z_Resource_Load
/* CREATE TABLE Z_Resource_Split
(Z_Resource_Split_ID NUMBER (10,0),
Z_Resource_Detail_ID NUMBER (10,0),
Z_Resource_ID NUMBER (10,0),
Z_ItemOperation_ID NUMBER (10,0),
z_PP_ID NUMBER (10,0),
Z_Item_ID NUMBER (10,0),
Z_Operation_ID NUMBER (10,0),
Qty NUMBER (10,0),
LoadMin NUMBER (10,0),
StartNo NUMBER (10,0),
CompleteNo NUMBER (10,0),
StartDateChr NVARCHAR2(200),
CompleteDateChr NVARCHAR2(200) )*/
CURSOR cur_Resource_Load ( x_Z_Resource_ID NUMBER)
IS
SELECT * FROM Z_Resource_Load
WHERE Z_Resource_ID=x_Z_Resource_ID;
CURSOR cur_Resource_Time ( x_Z_Resource_ID NUMBER)
IS
SELECT r.Z_Resource_ID, --r.Value , r.Name, s.Value, s.Name,
TO_NUMBER(TO_CHAR(FromTime,'HH24'),'99') fHH,TO_NUMBER(TO_CHAR(FromTime,'MI'),'99') fMI,
TO_NUMBER(TO_CHAR(ToTime, 'HH24'),'99') tHH,TO_NUMBER(TO_CHAR(ToTime, 'MI'),'99') tMI
FROM Z_Resource r
INNER JOIN Z_Shift s ON r.Z_Shift_ID=s.Z_Shift_ID
WHERE r.Z_Resource_ID=x_Z_Resource_ID;
BEGIN
DBMS_OUTPUT.put_line ('Updating PInstance - Processing ' || p_pinstance_id);
v_resultstr := 'PInstanceNotFound';
UPDATE AD_PInstance
SET Created = SYSDATE,
IsProcessing = 'Y'
WHERE AD_PInstance_ID = p_Pinstance_ID;
COMMIT;
v_resultstr := 'ReadingParameters';
DBMS_OUTPUT.put_line (v_resultstr);
FOR P IN cur_parameter (p_pinstance_id)
LOOP
DBMS_OUTPUT.put_line (' P.Record_ID='|| P.Record_ID);
--p_Record_ID := P.Record_ID;
--p_AD_User_ID := P.AD_User_ID;
--p_AD_Client_ID := P.AD_Client_ID;
--p_AD_Org_ID := P.AD_Org_ID;
-- IF (P.parametername = 'DateFG' ) THEN p_DateFG := P.p_Date;
-- p_DateFG_TO := P.p_Date_TO; DBMS_OUTPUT.put_line (' DateFG =' || p_DateFG);
-- ELSIF (P.parametername = 'C_BPartner_ID') THEN p_C_BPartner_ID := P.p_Number;
-- p_C_BPartner_ID_TO := P.p_Number_TO;DBMS_OUTPUT.put_line (' C_BPartner_ID=' || p_C_BPartner_ID);
-- ELSIF (P.parametername = 'M_Product_ID' ) THEN p_M_Product_ID := P.p_Number;
-- p_M_Product_ID_TO := P.p_Number_TO;DBMS_OUTPUT.put_line (' M_Product_ID =' || p_M_Product_ID);
-- ELSE DBMS_OUTPUT.put_line ('*** Unknown Parameter=' || P.parametername);
-- END IF;
END LOOP;
--DBMS_OUTPUT.put_line ('p_Record_ID='||p_Record_ID);
IF p_Pinstance_ID IS NULL THEN
DBMS_OUTPUT.put_line ('p_Pinstance_ID IS NULL !!!');
--v_Pinstance_ID :=1;
--v_Record_ID :=1;
END IF;
EXECUTE IMMEDIATE('TRUNCATE TABLE Z_Resource_Split');
EXECUTE IMMEDIATE('TRUNCATE TABLE Z_Resource_Load');
EXECUTE IMMEDIATE('TRUNCATE TABLE Z_Resource_Detail');
--EXECUTE IMMEDIATE('TRUNCATE TABLE Z_Resource_Load');
FOR a IN cur_PP LOOP
v_item_last := 0;
FOR p IN cur_ItemOperation(a.Z_Item_ID) LOOP
v_Qty_B := a.Qty;
IF p.Min_Transfer_Qty < 1 THEN
v_Min_Transfer_Qty := v_Qty_B; --p.Min_Transfer_Qty;
ELSE
v_Min_Transfer_Qty := p.Min_Transfer_Qty;
END IF;
DBMS_OUTPUT.put_line (' v_Qty_B='||v_Qty_B||' ,v_Min_Transfer_Qty='||v_Min_Transfer_Qty);
WHILE v_Qty_B > 0 LOOP
IF v_Qty_B > v_Min_Transfer_Qty THEN
v_Qty := v_Min_Transfer_Qty;
v_Qty_B := v_Qty_B - v_Min_Transfer_Qty;
ELSE
v_Qty := v_Qty_B; -- Sublot Qty
v_Qty_B := 0; -- Balance Qty
END IF;
--DBMS_OUTPUT.put_line ('after v_Qty_B='||v_Qty_B);
v_M := v_Qty * p.Usage;
v_Day := NVL(TRUNC(a.StartDate,'DD'),TRUNC(SYSDATE,'DD')) - TRUNC( SYSDATE,'Year');
v_Min := v_Day * 24 * 60 + 8 * 60;
-- 31*24*60 以過時分鐘
-- StartDate
-- DBMS_OUTPUT.put_line ('p.v_M='||v_M);
v_upd := 'N';
v_zm := NULL;
v_zm := zm_varray(); --create empty varray
v_zm.EXTEND(144000);
FOR z IN cur_Resource_Load (p.Z_Resource_ID) LOOP
v_zm := z.zm;
v_upd := 'Y';
END LOOP;
IF v_upd = 'N' THEN
FOR z IN cur_Resource_Time (p.Z_Resource_ID) LOOP
v_1 := 1;
WHILE v_1 < 100 LOOP -- 第100天的第三班會 out-of-boundary
IF NVL(z.fHH,0) > NVL(z.tHH,0) THEN
v_tMI := (NVL(z.tHH,0) + 24) * 60 + NVL(z.tMI,0);
ELSE
v_tMI := (NVL(z.tHH,0) + 0) * 60 + NVL(z.tMI,0);
END IF;
v_fMI := (z.fHH + 0) * 60 + z.fMI;
v_3 := ((v_1 - 1)* 24 * 60) + v_fMI; -- 從 8 *60 = 480 起
v_4 := ((v_1 - 1)* 24 * 60) + v_tMI; -- 到 17*60=1020
-- IF v_3 < 0 THEN v_3 := 0; END IF;
-- IF v_3 > 144000 THEN v_3 := 144000; END IF;
-- IF v_4 > 144000 THEN v_4 := 144000; END IF;
v_2 := v_3;
WHILE v_2 < v_4 LOOP
v_zm(v_2):= 0; -- 可生產時間
v_2 := v_2+1;
END LOOP;
v_1 := v_1 + 1;
END LOOP;
-- r.Z_Resource_ID, r.Value, r.Name, s.Value, s.Name,
-- TO_NUMBER(TO_CHAR(FromTime,'HH24'),'99') fHH,TO_NUMBER(TO_CHAR(FromTime,'MI'),'99') fMI,
-- TO_NUMBER(TO_CHAR(ToTime, 'HH24'),'99') tHH,TO_NUMBER(TO_CHAR(ToTime, 'MI'),'99') tHH
END LOOP;
END IF;
v_1 :=0;
v_2 :=0;
v_3 :=0;
v_4 :=0;
v_1 := 1;
v_1 := v_Min +1; --從計劃日開始找空杯子
IF v_item_last > v_1 THEN --同一Item 上一個完工時間
v_1 := v_item_last;
END IF;
WHILE v_1 < 144001 LOOP
IF v_zm(v_1) = 0 THEN --假設是正推排程
v_2 := v_1; --起點 v_2
v_1 := 999999;
END IF;
v_1 := v_1+1;
END LOOP;
v_SEQ := SEQ_RESOURCE_DETAIL.NEXTVAL;
-- v_3 := v_2 + v_M; -- 截止點
v_1 := v_2; -- 大於 144001 ? issue
v_3 :=1;
v_open :=0;
v_close :=0;
WHILE v_1 < 144001 LOOP
IF v_zm(v_1) = 0 THEN
IF v_open = 0 THEN
v_open := v_1;
END IF;
/*
IF v_close > 0 THEN
--關閉時段
v_SEQ1 := SEQ_RESOURCE_SPLIT.NEXTVAL;
vdd := FLOOR( v_close / 1440);
vhh := FLOOR((v_close - vdd * 1440)/60);
vmm := v_close - vdd * 1440 - vhh * 60;
xdd := FLOOR( v_open / 1440);
xhh := FLOOR((v_open - xdd * 1440)/60);
xmm := v_open - xdd * 1440 - xhh * 60;
v_s_str := TO_CHAR((TRUNC(SYSDATE,'Year')+ vdd),'YYYY-MM-DD')||' '||vhh||':'||vmm;
v_c_str := TO_CHAR((TRUNC(SYSDATE,'Year')+ xdd),'YYYY-MM-DD')||' '||xhh||':'||xmm;
INSERT INTO Z_Resource_Split(Z_Resource_Detail_ID, Z_Resource_Split_ID, Z_Resource_ID,
Z_Operation_ID, Z_Item_ID, Z_PP_ID,
Qty, LoadMin, StartNo, CompleteNo, StartDateChr, CompleteDateChr, Z_ItemOperation_ID)
VALUES(v_SEQ, v_SEQ1, p.Z_Resource_ID,
p.Z_Operation_ID, a.Z_Item_ID, a.Z_PP_ID,
a.Qty, v_M, v_open, v_close, v_s_str, v_c_str, p.Z_ItemOperation_ID);
v_close := 0;
END IF;
*/
v_zm(v_1) := 1; --已被使用= 1
IF v_3 > v_M THEN
v_4 := v_1;
v_1 := 999999;
END IF;
v_3 := v_3+1;
ELSE
-- IF v_close = 0 THEN
-- v_close:= v_1;
--END IF;
v_close := v_1;
IF v_open > 0 THEN
--關閉時段
v_SEQ1 := SEQ_RESOURCE_SPLIT.NEXTVAL;
vdd := FLOOR( v_open / 1440);
vhh := FLOOR((v_open - vdd * 1440)/60);
vmm := v_open - vdd * 1440 - vhh * 60;
xdd := FLOOR( v_close / 1440);
xhh := FLOOR((v_close - xdd * 1440)/60);
xmm := v_close - xdd * 1440 - xhh * 60;
v_s_str := TO_CHAR((TRUNC(SYSDATE,'Year')+ vdd),'YYYY-MM-DD')||' '||vhh||':'||vmm;
v_c_str := TO_CHAR((TRUNC(SYSDATE,'Year')+ xdd),'YYYY-MM-DD')||' '||xhh||':'||xmm;
INSERT INTO Z_Resource_Split(Z_Resource_Detail_ID, Z_Resource_Split_ID, Z_Resource_ID,
Z_Operation_ID, Z_Item_ID, Z_PP_ID,
Qty, LoadMin, StartNo, CompleteNo, StartDateChr, CompleteDateChr, Z_ItemOperation_ID)
VALUES(v_SEQ, v_SEQ1, p.Z_Resource_ID,
p.Z_Operation_ID, a.Z_Item_ID, a.Z_PP_ID,
a.Qty, v_M, v_open, v_close, v_s_str, v_c_str, p.Z_ItemOperation_ID);
v_open := 0;
END IF;
END IF;
v_1 := v_1+1;
END LOOP;
v_close := v_4;
IF v_open > 0 THEN
--關閉時段
v_SEQ1 := SEQ_RESOURCE_SPLIT.NEXTVAL;
vdd := FLOOR( v_open / 1440);
vhh := FLOOR((v_open - vdd * 1440)/60);
vmm := v_open - vdd * 1440 - vhh * 60;
xdd := FLOOR( v_close / 1440);
xhh := FLOOR((v_close - xdd * 1440)/60);
xmm := v_close - xdd * 1440 - xhh * 60;
v_s_str := TO_CHAR((TRUNC(SYSDATE,'Year')+ vdd),'YYYY-MM-DD')||' '||vhh||':'||vmm;
v_c_str := TO_CHAR((TRUNC(SYSDATE,'Year')+ xdd),'YYYY-MM-DD')||' '||xhh||':'||xmm;
INSERT INTO Z_Resource_Split(Z_Resource_Detail_ID, Z_Resource_Split_ID, Z_Resource_ID,
Z_Operation_ID, Z_Item_ID, Z_PP_ID,
Qty, LoadMin, StartNo, CompleteNo, StartDateChr, CompleteDateChr, Z_ItemOperation_ID)
VALUES(v_SEQ, v_SEQ1, p.Z_Resource_ID,
p.Z_Operation_ID, a.Z_Item_ID, a.Z_PP_ID,
a.Qty, v_M, v_open, v_close, v_s_str, v_c_str, p.Z_ItemOperation_ID);
v_open := 0;
END IF;
v_item_last := v_4;
--SELECT TRUNC(SYSDATE,'Year') + 1 FROM DUAL
vdd := FLOOR( v_2 / 1440);
vhh := FLOOR((v_2 - vdd * 1440)/60);
vmm := v_2 - vdd * 1440 - vhh * 60;
xdd := FLOOR( v_4 / 1440);
xhh := FLOOR((v_4 - xdd * 1440)/60);
xmm := v_4 - xdd * 1440 - xhh * 60;
v_s_str := TO_CHAR((TRUNC(SYSDATE,'Year')+ vdd),'YYYY-MM-DD')||' '||vhh||':'||vmm;
v_c_str := TO_CHAR((TRUNC(SYSDATE,'Year')+ xdd),'YYYY-MM-DD')||' '||xhh||':'||xmm;
DBMS_OUTPUT.put_line ('p.Z_Resource_ID='||p.Z_Resource_ID||' ,v_M='||v_M||
' ,起點='||v_2 ||' ,截止點='||v_3 ||
' ,起點='|| (TRUNC(SYSDATE,'Year')+ vdd) ||' hh='||vhh||' mm='||vmm||
' ,截止點='|| (TRUNC(SYSDATE,'Year')+ xdd) ||' hh='||xhh||' mm='||xmm
);
INSERT INTO Z_Resource_Detail(Z_Resource_Detail_ID, Z_Resource_ID, Z_Operation_ID, Z_Item_ID, Z_PP_ID,
Qty, LoadMin, StartNo, CompleteNo, StartDateChr, CompleteDateChr, Z_ItemOperation_ID)
VALUES(v_SEQ, p.Z_Resource_ID, p.Z_Operation_ID, a.Z_Item_ID, a.Z_PP_ID,
a.Qty, v_M, v_2, v_3, v_s_str, v_c_str, p.Z_ItemOperation_ID);
IF v_upd ='Y' THEN
UPDATE Z_Resource_Load SET zm=v_zm WHERE Z_Resource_ID=p.Z_Resource_ID;
ELSE
INSERT INTO Z_Resource_Load(Z_Resource_ID,ZM)VALUES(p.Z_Resource_ID,v_zm);
END IF;
END LOOP; --WHILE v_Qty_B > 0 LOOP
END LOOP;
END LOOP;
--DBMS_OUTPUTput_line ('Updating PInstance - Finished ' || v_message);
<<FINISH_PROCESS>>
-- Update AD_PInstance
DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
UPDATE AD_PInstance
SET Updated = SysDate,
IsProcessing = 'N',
Result = v_Result, -- 1=success
ErrorMsg = v_Message
WHERE AD_PInstance_ID=p_PInstance_ID;
COMMIT;
RETURN;
EXCEPTION
WHEN OTHERS THEN
v_ResultStr := v_ResultStr || ': ' || SQLERRM || ' - ' || v_Message;
DBMS_OUTPUT.PUT_LINE(v_ResultStr);
ROLLBACK;
UPDATE AD_PInstance
SET Updated = SysDate,
IsProcessing = 'N',
Result = 0, -- failure
ErrorMsg = v_ResultStr
WHERE AD_PInstance_ID=p_PInstance_ID;
COMMIT;
RETURN;
END TEST1;
/
沒有留言:
張貼留言