德意志血統 iDempiere 超越土產ERP 困境的最佳選擇: 2月 2014

2014年2月27日星期四

IDempiere RJ_GenCashLine_P

CREATE OR REPLACE PROCEDURE C302.RJ_GenCashLine_P (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;

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 = pp_Pinstance
AND i.AD_Pinstance_ID = p.AD_Pinstance_ID(+)
ORDER BY p.SeqNo;

CURSOR cur_Line (x_C_Cash_ID NUMBER)
IS
SELECT *
FROM C_CashLine
WHERE C_Cash_ID = x_C_Cash_ID;

CURSOR cur_Cash (x_C_BankStatemen_ID NUMBER)
IS
SELECT *
FROM C_Cash
WHERE C_Cash_ID = x_C_BankStatemen_ID;

v_DocumentNo VARCHAR(20) := NULL;
v_NextHeader_ID NUMBER;
v_Next_ID NUMBER;
v_AD_Client_ID NUMBER;
v_AD_Org_ID NUMBER;
v_Line NUMBER;

p_Ref_Cash VARCHAR(22);
p_Ref_Cash_ID NUMBER(10,0);
No_C_Cash EXCEPTION;

BEGIN

v_resultstr := 'PInstanceNotFound';

UPDATE AD_PInstance
SET Created = SYSDATE,
IsProcessing = 'Y'
WHERE AD_PInstance_ID = p_Pinstance_ID;

COMMIT;

v_resultstr := 'ReadingParameters';

FOR P IN cur_parameter (p_pinstance_id)
LOOP
v_Record_ID := p.Record_ID;
v_AD_User_ID := P.AD_User_ID;
v_AD_Client_ID := P.AD_Client_ID;
v_AD_Org_ID := P.AD_Org_ID;
END LOOP;

v_message:='測試是否有 v_Record_ID';
IF NVL(v_Record_ID,0)=0 THEN
RAISE No_C_Cash;
END IF;

p_Ref_Cash_ID := 0;
FOR p IN cur_Cash (v_Record_ID)
LOOP
p_Ref_Cash := p.Ref_Cash;

END LOOP;
p_Ref_Cash_ID:= TO_NUMBER(p_Ref_Cash,'9999999999');

v_Line:=0;

FOR p IN cur_Line (p_Ref_Cash_ID)
LOOP
v_Line := v_Line + 10;
--@SQL=SELECT COALESCE(MAX(Line),0)+10 FROM C_CashLine WHERE C_Cash_ID=@C_Cash_ID@
AD_Sequence_Next ('C_CashLine',1000000, v_Next_ID);
INSERT INTO C_CashLine(
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
C_CashLine_ID,C_Cash_ID, Line,
Description, --:=來源C_CashLine.Description
CashType, --:=來源C_CashLine.CashType:
C_Currency_ID, --:=來源C_CashLine.C_Currency_ID
Amount, --:=0
C_BankAccount_ID, --:=來源C_CashLine.C_BankAccount_ID
C_Charge_ID, --:=來源C_CashLine.C_Charge_ID
C_Invoice_ID, --:=來源C_CashLine.C_Invoice_ID
DiscountAmt, --:=0
WriteoffAmt --:=0
)VALUES(
v_AD_Client_ID, v_AD_Org_ID, 'Y', SYSDATE, v_AD_User_ID, SYSDATE, v_AD_User_ID,
v_Next_ID, v_Record_ID, v_Line,
p.Description, --:=來源C_CashLine.Description
p.CashType, --:=來源C_CashLine.CashType:
p.C_Currency_ID, --:=來源C_CashLine.C_Currency_ID
0, --Amount:=0
p.C_BankAccount_ID, --:=來源C_CashLine.C_BankAccount_ID
p.C_Charge_ID, --:=來源C_CashLine.C_Charge_ID
p.C_Invoice_ID, --:=來源C_CashLine.C_Invoice_ID
0, --DiscountAmt:=0
0 --WriteoffAmt:=0
);

END LOOP;

<<finish_process>>
DBMS_OUTPUT.put_line ('Updating PInstance - Finished ' || v_message);

UPDATE ad_pinstance
SET updated = SYSDATE,
isprocessing = 'N',
result = v_result,
errormsg = v_message
WHERE ad_pinstance_id = p_pinstance_id;

COMMIT;
RETURN;
EXCEPTION

WHEN No_C_Cash
THEN -- does not handle RAISEd exception

v_message := v_resultstr || ': ' || SQLERRM || ' - ' || v_message;
DBMS_OUTPUT.put_line (v_message);

WHEN OTHERS
THEN

v_message := v_resultstr || ': ' || SQLERRM || ' - ' || v_message;

DBMS_OUTPUT.put_line (v_message);
ROLLBACK;

UPDATE ad_pinstance
SET updated = SYSDATE,
isprocessing = 'N',
RESULT = 0,
errormsg = v_message
WHERE ad_pinstance_id = p_pinstance_id;

COMMIT;
RETURN;

END RJ_GenCashLine_P ;
/

2014年2月26日星期三

iDempiere ASCP TEST

DECLARE
P_PINSTANCE_ID NUMBER;

BEGIN
P_PINSTANCE_ID := 0;

C##AD.TEST1 ( P_PINSTANCE_ID );
COMMIT;
END;

select Z_Shift_ID, ToTime, ToTime + 12/24 FROM Z_Shift
update Z_Shift set ToTime=ToTime-10/24 where Z_Shift_ID=1000002

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
commit
休息時間
換線時間換線最少 ? pc 才排上去
-- z_resource_deatil
SELECT
pp.Value PPValue,im.Value ItemValue, rd.Qty,
io.Value ItemOperation,op.Value OperationValue, rs.Value ResourceValue,
rd.StartDateChr,rd.CompleteDateChr, sl.StartDateChr,sl.CompleteDateChr
/*rd.Z_Resource_Detail_ID, rs.Name ResourceName, pp.Name PPName, im.Name ItemName, op.Name OperationName, rd.LoadMin, rd.StartNo,rd.CompleteNo, */
FROM Z_Resource_Detail rd
INNER JOIN Z_ItemOperation io ON io.Z_ItemOperation_ID=rd.Z_ItemOperation_ID
INNER JOIN Z_PP pp ON pp.Z_PP_ID=rd.Z_PP_ID
INNER JOIN Z_Item im ON im.Z_Item_ID=rd.Z_Item_ID
INNER JOIN Z_Resource rs ON rs.Z_Resource_ID=rd.Z_Resource_ID
INNER JOIN Z_Operation op ON op.Z_Operation_ID=rd.Z_Operation_ID
LEFT OUTER JOIN Z_Resource_Split sl ON rd.Z_Resource_Detail_ID=sl.Z_Resource_Detail_ID
ORDER BY rd.Z_Resource_Detail_ID,Z_Resource_Split_ID

SELECT * FROM Z_Resource_Split
-- ALTER TABLE Z_Resource_Detail ADD StartDateChr NVARCHAR2(200);
-- ALTER TABLE Z_Resource_Detail ADD CompleteDateChr NVARCHAR2(200);

iDempiere ASCP

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;
/

Start After End :
Time
Move 4.00 Hours
Queue 0.00 Hours
Setup 0.00 Hours
Separation
Minimum 4.00 Hours
Maximum 7.00 Hours

In a routing diagram, links between operations display the precedence relationship as text labels on the links between operations.

The following screen shows the properties for a routing link in which you set precedence relationships:

the picture is described in the document text

This diagram illustrates the temporal constraints between operations:

Designating Primary Operations

To designate primary operations:

Access the routing diagram window.
Right-click the operation that you want to designate.
Select Primary Operation.
The operation now appears with a blue border around its name.
The following diagrams provide an example of assigning a primary operation.

As a result of selecting Primary Operation, the screen below shows a routing with the last operation designated as the primary operation:

Creating a New Routing and Routing Diagram

To create a new routing and routing diagram:

From the Model Workspace, right-click All Routings or right-click a routing folder and choose New Routing.
Type a name for the routing and press Enter.
Double-click the new routing.
An empty workbook tab opens.
Open the All Operations folder and drag the operations you want to include in the diagram into the empty work pane.
On the toolbar, click the Insert Link icon.
To link two operations, click the first operation, and then click the second.

Setting Routing Properties

Use the routing Properties window to assigning properties.

To access the routing Properties window, right-click a routing from the Model Workspace and select Properties. The routing Properties window display 4 tabs: General, Attributes, Resource Assignments, and Where Used.

Setting Routing Properties - General Tab

To set general properties complete these fields:

From the Model Workspace, right-click a routing and select Properties.
Select the General tab.
Complete these fields:

Routing Properties - General Tab Field Description

Code Enter a unique code for the routing.

Notes Enter any routing-specific notes.

Routing Properties - General Tab Field	Description
Code	Enter a unique code for the routing.
Notes	Enter any routing-specific notes.

Setting Routing Properties - Attributes Tab

To set routing property attributes:

From the Model Workspace, right-click a routing and select Properties.
Select the Attributes tab.

Complete these fields:


Routing Properties - Attributes Tab Field	Description
Routing Type	Select a routing type from the list. Options are: Production - Select if your routing produces one or more items. Maintenance - Select if your routing produces no items or is used for cMRO or other maintenance activities. This type of routing consumes time and may consume items. For example, a work order to clean a machine would be a maintenance routing. Routings of this type may produce an item, but are not required to. EAM - Select if your routing represents activities modeled in the Enterprise Asset Management system. Note: If a routing is currently assigned to a work order, you cannot change the routing type or structure without first removing the associated work orders.
Use this Routing for Work Orders only	Select this option if routing should only run when it is associated with a work order. For special configurations or one-off production, you may want to create an alternate routing that produces an item using a different set of operations. The system identifies these routings so that the solver does not try to use them to fulfill demand. Routings identified for use with work orders are only run when they are specified in a work order. If demand exists for an item, but no associated work order exists, the solver does not run a routing that is marked to be used in work orders only.
Schedule Work Order operations according to their unit of effort	This option breaks up associated work order operations into their individual units of effort. By dividing operations into smaller units of effort, scheduling is both more flexible and accurate. This principle is explained in more detail in the examples, which follow this section. See "Scheduling Work Orders According to Units of Effort Overview," Configuring Solver Options, Production Scheduling.
Adjacent operations preferred	When enabled, this flag instructs the Production Scheduling solver to schedule all work order operations adjacent to each other when the operations are broken down into their units of effort.
Ship Buffer	Assign a ship buffer if you have enabled Theory of Constraints. See "Working with Buffers," Creating Production Scheduling Models, Production Scheduling.

Setting Routing Properties - Resource Assignment Tab

Alternate resources are common in many manufacturing operations. These alternate resources are modeled through the use of resource sets. In many cases, this resource set recurs in an upstream or downstream operation. In many cases, it is important that once a resource is selected in an upstream operation, it must also be selected in a downstream operation.

To perform consistent resource assignment within only a selected group of operations within a routing, setup operation resource assignment groups within a given routing. For example, operation steps 10, 20, and 30 must run on the same resource. However, operation steps 40, 50, and 60, even though they share a common resource with the preceding operations, can run on the alternate.

Use the Routing Properties - Resource Assignment tab to set up alternate resources.

To set routing property resource assignments:

From the Model Workspace, right-click a routing and select Properties.
Select the Resource Assignment tab.
All ungrouped operations are listed in the left column and sorted in alphanumeric order.
Select the Add New Group button.
A default group is created.
To rename the group, select F2, or double-click the group name.
On the left side of the list, highlight the operation, or operations, that you wish to group together.
Click the right arrow button to move the highlighted operations to the right side of the list.
The operations are assigned to the highlighted group in the right column.
Repeat the process to create additional resource assignment groups for any operation within a routing.

Note: If an operation is not assigned to a group it can run on any resource.

If using All of Sets, for example more than one resource such as a machine 1 and tool 1, or machine 2 and tool 2, consistent resource assignment applies to the group. Meaning the group must be the same.

See "Creating All of Sets," Creating Production Scheduling Models, Production Scheduling.

Setting Routing Properties - Where Used Tab

The Routing Properties - Where Used tab displays:

Routing in the event that a routing is embedded in another routing.
Work orders that contain this routing.

You can double-click a routing or a work order in the list to open it.

Oracle ERP / APSCP 資料

Oracle ERP / APSCP 資料

Starts After End 上一個 [工序] 完成後 S>E

Starts At End 上一個 [工序] 完成時 S@E

Starts After Start 上一個 [工序] 開始後 S>S

Starts At Start 上一個 [工序] 開始時 S@S

Ends At End 上一個 [工序] 完成時 E@E

Production Scheduling supports the following precedence relationships:


Name	Diagram Label	Description
Starts After End	S>E	One operation starts after the end of the previous operation, according to the time intervals displayed in the Minimum and Maximum separation fields.
Starts At End	S@E	One operation starts at the exact end of the previous operation.
Starts After Start	S>S	One operation starts after the start of the previous operation, according to the time intervals displayed in the Minimum and Maximum separation fields.
Starts At Start	S@S	One operation starts at the same time as the other operation.
Ends At End	E@E	One operation ends at the same time as the end of the other operation.
Minimum or Maximum	+	When S>S or S>E, indicates that the precedence relationship has a minimum or maximum separation.

iDempiere 先進排程

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);
--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;

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;
-- 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;

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 NVL(v_zm(v_1),0) < 1 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 NVL(v_zm(v_1),0) < 1 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;
/

訂閱：文章 (Atom)

2014年2月27日 星期四