1 PACKAGE BODY IEC_RLCTRL_PVT AS
2 /* $Header: IECRCPVB.pls 115.0.1157.1 2002/03/14 08:56:24 pkm ship $ */
3
4 TYPE BULK_COLLECT_COLUMN_N IS TABLE OF NUMBER(15);
5
6 PROCEDURE MAKE_LIST_ENTRIES_AVAILABLE ( P_LIST_HEADER_ID IN NUMBER
7 , P_DNU_REASON_CODE IN NUMBER
8 , P_COMMIT IN BOOLEAN
9 , X_RETURN_STATUS OUT VARCHAR2)
10 IS
11
12 L_LIST_ENTRY_IDS BULK_COLLECT_COLUMN_N := BULK_COLLECT_COLUMN_N();
13
14 BEGIN
15
16 SAVEPOINT SP1;
17
18 X_RETURN_STATUS := 'S';
19
20 IF P_DNU_REASON_CODE IS NOT NULL THEN
21
22 SELECT LIST_ENTRY_ID
23 BULK COLLECT INTO L_LIST_ENTRY_IDS
24 FROM AMS_LIST_ENTRIES
25 WHERE LIST_HEADER_ID = P_LIST_HEADER_ID AND DO_NOT_USE_FLAG = 'Y' AND DO_NOT_USE_REASON = P_DNU_REASON_CODE;
26
27 ELSE
28
29 SELECT LIST_ENTRY_ID
30 BULK COLLECT INTO L_LIST_ENTRY_IDS
31 FROM AMS_LIST_ENTRIES
32 WHERE LIST_HEADER_ID = P_LIST_HEADER_ID AND DO_NOT_USE_FLAG = 'Y';
33
34 END IF;
35
36 IF L_LIST_ENTRY_IDS IS NOT NULL AND L_LIST_ENTRY_IDS.COUNT > 0 THEN
37
38 FORALL I IN L_LIST_ENTRY_IDS.FIRST..L_LIST_ENTRY_IDS.LAST
39 UPDATE AMS_LIST_ENTRIES
40 SET DO_NOT_USE_FLAG = 'N'
41 WHERE LIST_ENTRY_ID = L_LIST_ENTRY_IDS(I);
42
43 FORALL I IN L_LIST_ENTRY_IDS.FIRST..L_LIST_ENTRY_IDS.LAST
44 UPDATE IEC_G_RETURN_ENTRIES
45 SET DO_NOT_USE_FLAG = 'N'
46 WHERE LIST_ENTRY_ID = L_LIST_ENTRY_IDS(I);
47
48 END IF;
49
50 IF P_COMMIT THEN
51 COMMIT;
52 END IF;
53
54 EXCEPTION
55 WHEN OTHERS THEN
56 ROLLBACK TO SP1;
57 X_RETURN_STATUS := 'E';
58
59 END MAKE_LIST_ENTRIES_AVAILABLE;
60
61
62 END IEC_RLCTRL_PVT;
[Home] [Help]