الى محترفين برمجة الاوراكل ممكن المساعدة

عندي شاشة فيها تضاف اجراءات الصيانه للمعدات مثل التكييف والميكانيكا وغيرها وكل صنف له ترميز عباره عن اربع ارقام ومجموعات تكون تحت هذا الرمز عباره عن تكويد لكا مجموعه يضاف لهاجراءات الصيانه وبعد الحفظ فيه زار عند ضغطه ينشى كروت الصيانه سنوي وربع سنوي وشهري ونصف سنوي بعدذلك يتم الانتقال الى شاشة كروت الصيانه وهوعباره عن 12 كرت يتم انزال الاجراات الصيانه لكل مجموعه وتكون مختلفه عن المجموعه الاخرى المشكله هو عند انزال الاجرات في شاشة كروت الصيانه تكون الاجراات مختلطه ارجوا حل المشكله بان تكون عند انزال الاجرات تكون مستقله لكل مجموعه سرفق الاكواد ولكم جزيل الشكر

CREATE MAINTENANCE SCHEDULE كود انشاء جدول الصيانه

BEGIN

IF :GRP_INV.MAINT_CODE IS NULL OR :GRP_INV.GRP_INV_NO IS NULL OR

:GRP_INV.ZONE_CODE IS NULL OR :GRP_INV.MAINT_DATE IS NULL OR

:GRP_INV.CARD_YEAR IS NULL THEN

MESSAGE('?CEI ?? E???E C?E?C?CE ?E? ??OC? E?C?CE C???C?E ??????E C???? C????? ?');

RAISE FORM_TRIGGER_FAILURE;

ELSE

CRT_MCARD(:GRP_INV.MAINT_CODE,:GRP_INV.ZONE_CODE,

:GRP_INV.GRP_INV_NO,:GRP_INV.MAINT_DATE,:GRP_INV.CARD_YEAR,:GRP_INV.TEAM_CODE);

END IF;

END;

-----------------------

DELETE ALL CARDS PROCEDURES كود تنضيف الجدول المضاف اليه اجراات الصيانه في شاشة كروت الصيانه

DELETE FROM ZC_MAINT_CARD_D

WHERE TEAM_CODE=:MAINT_CARD.TEAM_CODE;

COMMIT;

GO_BLOCK('ZC_MAINT_CARD_D');

GO_ITEM('ZC_MAINT_CARD_D.SITE_CODE');

EXECUTE_QUERY;

-----------------------

Procedure Grp كود انزال اجرات الصيانه هذا الي فيه المشكله

DECLARE

X NUMBER;

Z NUMBER;

A VARCHAR2(200);

CURSOR C1 IS SELECT TEAM_CODE,ACTION_SER,MAINT_ACTION,ACTION_OK,DEFECT_NOTE

FROM ZC_PROC_GRP

WHERE TEAM_CODE = :MAINT_CARD.TEAM_CODE;

V$TEAM_CODE VARCHAR2(20);

--V$PROC_SER NUMBER;

V$ACTION_SER NUMBER;

V$MAINT_ACTION VARCHAR2(200);

V$DEFECT_NOTE VARCHAR2(200);

V$ACTION_OK VARCHAR2(1);

--V$MCARD_NO NUMBER :=:MAINT_CARD.MCARD_NO;

V$MAINT_DATE DATE;

V$ZONE_CODE VARCHAR2(5):=:MAINT_CARD.ZONE_CODE;

CONT1 NUMBER;

CONT2 NUMBER;

CONT3 NUMBER;

CARD_N NUMBER:=0;

BEGIN

SELECT NVL(COUNT(MAINT_ACTION),0) INTO CONT1

FROM ZC_MAINT_CARD_D

WHERE TEAM_CODE = :MAINT_CARD.TEAM_CODE

AND MCARD_NO= :MAINT_CARD.MCARD_NO;

BEGIN

SELECT NVL(COUNT(MAINT_ACTION ),0) INTO CONT2

FROM ZC_PROC_GRP

WHERE TEAM_CODE = :MAINT_CARD.TEAM_CODE;

EXCEPTION

WHEN NO_DATA_FOUND THEN

Set_Alert_Property('INFO', ALERT_MESSAGE_TEXT, 'NO PROCEDURES FOUND IN MAIN TABLE...');

IF SHOW_ALERT('INFO') IS NULL

THEN

NULL;

END IF;

END;

SELECT NVL(COUNT(*),0) INTO CONT3

FROM ZC_MAINT_CARD

WHERE GRP_INV_NO=:MAINT_CARD.GRP_INV_NO;

IF CONT2 > CONT1

THEN

OPEN C1;

LOOP

FETCH C1 INTO

V$TEAM_CODE,

-- V$PROC_SER,

V$ACTION_SER,

V$MAINT_ACTION,

V$ACTION_OK,

V$DEFECT_NOTE;

EXIT WHEN C1%NOTFOUND;

BEGIN

IF V$ACTION_SER>CONT1

THEN

CARD_N:=0;

FOR I IN 1..CONT3 LOOP

CARD_N:=CARD_N+1;

V$MAINT_DATE:=NULL;

SELECT MAINT_DATE INTO V$MAINT_DATE

FROM ZC_MAINT_CARD

WHERE MCARD_NO=CARD_N AND GRP_INV_NO=:MAINT_CARD.GRP_INV_NO;

INSERT INTO ZC_MAINT_CARD_D

(

TEAM_CODE,

ACTION_SER,

MAINT_ACTION,

ACTION_OK,

MCARD_NO,

DEFECT_NOTE,

ZONE_CODE,

MAINT_DATE

)

VALUES

(

V$TEAM_CODE,

V$ACTION_SER,

V$MAINT_ACTION,

V$ACTION_OK,

CARD_N,

V$DEFECT_NOTE,

V$ZONE_CODE,

V$MAINT_DATE

);

--WHERE ZT_MAINT_CARD_D.MCARD_NO=V$MCARD_NO;

END LOOP;

COMMIT;

ELSE

NULL;

END IF;

/* EXCEPTION

WHEN OTHERS

THEN

NULL;

WHEN DUP_VAL_ON_INDEX THEN

UPDATE ZT_MAINT_CARD_D

SET TEAM_CODE = V$TEAM_CODE,

MCARD_NO = V$MCARD_NO,

MAINT_DATE = V$MAINT_DATE,

ACTION_SER = V$ACTION_SER,

MAINT_ACTION = V$MAINT_ACTION,

ACTION_OK = V$ACTION_OK,

DEFECT_NOTE = V$DEFECT_NOTE,

ZONE_CODE = V$ZONE_CODE

WHERE TEAM_CODE = :MAINT_CARD.TEAM_CODE

AND MCARD_NO = :MAINT_CARD.MCARD_NO

AND MAINT_DATE = :MAINT_CARD.MAINT_DATE

AND ACTION_SER = :ACTION_SER;

-- WHEN OTHERS THEN

-- NULL;*/

END;

END LOOP;

CLOSE C1;

COMMIT;

EXECUTE_QUERY;

ELSE

Set_Alert_Property('INFO', ALERT_MESSAGE_TEXT, 'Mintenance Procedure is already exist ...');

IF SHOW_ALERT('INFO') IS NULL

THEN

NULL;

END IF;

END IF;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

-----------

ZC_PROC_GRP جدول اجرااات الصيانه

DECLARE

X NUMBER;

Z NUMBER;

A VARCHAR2(200);

CURSOR C1 IS SELECT TEAM_CODE,ACTION_SER,MAINT_ACTION,ACTION_OK,DEFECT_NOTE

FROM ZC_PROC_GRP

WHERE TEAM_CODE = :MAINT_CARD.TEAM_CODE;

V$TEAM_CODE VARCHAR2(20);

--V$PROC_SER NUMBER;

V$ACTION_SER NUMBER;

V$MAINT_ACTION VARCHAR2(200);

V$DEFECT_NOTE VARCHAR2(200);

V$ACTION_OK VARCHAR2(1);

--V$MCARD_NO NUMBER :=:MAINT_CARD.MCARD_NO;

V$MAINT_DATE DATE;

V$ZONE_CODE VARCHAR2(5):=:MAINT_CARD.ZONE_CODE;

CONT1 NUMBER;

CONT2 NUMBER;

CONT3 NUMBER;

CARD_N NUMBER:=0;

BEGIN

SELECT NVL(COUNT(MAINT_ACTION),0) INTO CONT1

FROM ZC_MAINT_CARD_D

WHERE TEAM_CODE = :MAINT_CARD.TEAM_CODE

AND MCARD_NO= :MAINT_CARD.MCARD_NO;

BEGIN

SELECT NVL(COUNT(MAINT_ACTION ),0) INTO CONT2

FROM ZC_PROC_GRP

WHERE TEAM_CODE = :MAINT_CARD.TEAM_CODE;

EXCEPTION

WHEN NO_DATA_FOUND THEN

Set_Alert_Property('INFO', ALERT_MESSAGE_TEXT, 'NO PROCEDURES FOUND IN MAIN TABLE...');

IF SHOW_ALERT('INFO') IS NULL

THEN

NULL;

END IF;

END;

SELECT NVL(COUNT(*),0) INTO CONT3

FROM ZC_MAINT_CARD

WHERE GRP_INV_NO=:MAINT_CARD.GRP_INV_NO;

IF CONT2 > CONT1

THEN

OPEN C1;

LOOP

FETCH C1 INTO

V$TEAM_CODE,

-- V$PROC_SER,

V$ACTION_SER,

V$MAINT_ACTION,

V$ACTION_OK,

V$DEFECT_NOTE;

EXIT WHEN C1%NOTFOUND;

BEGIN

IF V$ACTION_SER>CONT1

THEN

CARD_N:=0;

FOR I IN 1..CONT3 LOOP

CARD_N:=CARD_N+1;

V$MAINT_DATE:=NULL;

SELECT MAINT_DATE INTO V$MAINT_DATE

FROM ZC_MAINT_CARD

WHERE MCARD_NO=CARD_N AND GRP_INV_NO=:MAINT_CARD.GRP_INV_NO;

INSERT INTO ZC_MAINT_CARD_D

(

TEAM_CODE,

ACTION_SER,

MAINT_ACTION,

ACTION_OK,

MCARD_NO,

DEFECT_NOTE,

ZONE_CODE,

MAINT_DATE

)

VALUES

(

V$TEAM_CODE,

V$ACTION_SER,

V$MAINT_ACTION,

V$ACTION_OK,

CARD_N,

V$DEFECT_NOTE,

V$ZONE_CODE,

V$MAINT_DATE

);

--WHERE ZT_MAINT_CARD_D.MCARD_NO=V$MCARD_NO;

END LOOP;

COMMIT;

ELSE

NULL;

END IF;

/* EXCEPTION

WHEN OTHERS

THEN

NULL;

WHEN DUP_VAL_ON_INDEX THEN

UPDATE ZT_MAINT_CARD_D

SET TEAM_CODE = V$TEAM_CODE,

MCARD_NO = V$MCARD_NO,

MAINT_DATE = V$MAINT_DATE,

ACTION_SER = V$ACTION_SER,

MAINT_ACTION = V$MAINT_ACTION,

ACTION_OK = V$ACTION_OK,

DEFECT_NOTE = V$DEFECT_NOTE,

ZONE_CODE = V$ZONE_CODE

WHERE TEAM_CODE = :MAINT_CARD.TEAM_CODE

AND MCARD_NO = :MAINT_CARD.MCARD_NO

AND MAINT_DATE = :MAINT_CARD.MAINT_DATE

AND ACTION_SER = :ACTION_SER;

-- WHEN OTHERS THEN

-- NULL;*/

END;

END LOOP;

CLOSE C1;

COMMIT;

EXECUTE_QUERY;

ELSE

Set_Alert_Property('INFO', ALERT_MESSAGE_TEXT, 'Mintenance Procedure is already exist ...');

IF SHOW_ALERT('INFO') IS NULL

THEN

NULL;

END IF;

END IF;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

-----------