PACKAGE BODY: APPS.IEC_RLC_CTRL_PVT

Source

1 PACKAGE BODY IEC_RLC_CTRL_PVT AS
2 /* $Header: IECVRLCB.pls 115.19 2002/02/21 13:11:45 pkm ship    $ */
3 
4 
5 PROCEDURE RELEASE_CONTROL
6    (P_LIST_ENTRY_ID_TAB         IEC_CPN_RLSE_STTGY_PVT.LIST_ENTRY_ID
7    ,P_LIST_HEADER_ID            NUMBER
8    ,P_RLSE_CTRL_ID		NUMBER
9    ,P_VIEW_NAME			VARCHAR2
10    ,X_LIST_ENTRY_ID_TAB     OUT IEC_CPN_RLSE_STTGY_PVT.LIST_ENTRY_ID
11    )
12 AS
13 l_where_clause VARCHAR2(4000) := null;
14 l_list_entry_id_list VARCHAR2(4000) := null;
15 l_list_entry_id_count NUMBER(10) := 0;
16 l_rlse_stmt	VARCHAR2(4000) := null;
17 l_dyn_cursor_id	INTEGER;
18 l_dummy		INTEGER;
19 l_use_count     NUMBER :=1;
20 l_list_entry_id_col NUMBER(15);
21 BEGIN
22       if(P_RLSE_CTRL_ID > 0 ) then
23       IEC_WHERECLAUSE_PVT.getWhereClause(P_RLSE_CTRL_ID,'RLC',l_where_clause);
24       if(l_where_clause IS NOT NULL) then
25         if(P_LIST_ENTRY_ID_TAB.count > 0) then
26           l_list_entry_id_list := '(';
27           for k in 1..P_LIST_ENTRY_ID_TAB.count
28 	  loop
29 	  if(k >1)
30 	  then
31 	      l_list_entry_id_list := l_list_entry_id_list || ',';
32           end if;
33 	  l_list_entry_id_list := l_list_entry_id_list || ' '|| P_LIST_ENTRY_ID_TAB(K);
34 	  l_list_entry_id_count := l_list_entry_id_count + 1;
35           end loop;
36 
37           l_list_entry_id_list := l_list_entry_id_list ||')';
38           l_rlse_stmt := 'select list_entry_id from '|| P_VIEW_NAME
39 		|| ' where list_entry_id in '|| l_list_entry_id_list
40 		|| ' and list_header_id = '|| P_LIST_HEADER_ID || ' and ('||
41 		l_where_clause || ' )';
42  --         DBMS_OUTPUT.PUT_LINE('after insert stmt');
43           l_dyn_cursor_id := DBMS_SQL.OPEN_CURSOR;
44           DBMS_SQL.PARSE(l_dyn_cursor_id,l_rlse_stmt,DBMS_SQL.V7);
45           DBMS_SQL.DEFINE_COLUMN(l_dyn_cursor_id,1,l_list_entry_id_col);
46           l_dummy := DBMS_SQL.EXECUTE(l_dyn_cursor_id);
47 
48           loop
49           if DBMS_SQL.FETCH_ROWs(l_dyn_cursor_id) = 0
50           then
51             l_use_count := -1;
52 	    exit;
53           end if;
54 
55 	  DBMS_SQL.column_value(l_dyn_cursor_id,1,l_list_entry_id_col);
56 	  X_LIST_ENTRY_ID_TAB(l_use_count) := l_list_entry_id_col;
57 	  l_use_count := l_use_count + 1;
58           end loop;
59 
60           DBMS_SQL.CLOSE_CURSOR(l_dyn_cursor_id);
61 
62         end if;
63       end if;
64       end if;
65       Exception
66 	when NO_DATA_FOUND then
67 	  if DBMS_SQL.IS_OPEN(l_dyn_cursor_id)
68           then
69             DBMS_SQL.CLOSE_CURSOR(l_dyn_cursor_id);
70           end if;
71         when OTHERS then
72 	  if DBMS_SQL.IS_OPEN(l_dyn_cursor_id)
73           then
74             DBMS_SQL.CLOSE_CURSOR(l_dyn_cursor_id);
75           end if;
76           raise;
77 END;
78 
79 PROCEDURE RELEASE_CONTROL_REASSIGNALL
80    (P_LIST_HEADER_ID		NUMBER
81 	 ,P_DO_NOT_USE_REASON NUMBER
82    )
83 AS
84 BEGIN
85 	UPDATE AMS_LIST_ENTRIES
86 	set DO_NOT_USE_FLAG='N',DO_NOT_USE_REASON=null where LIST_HEADER_ID= P_LIST_HEADER_ID and DO_NOT_USE_FLAG='Y' and DO_NOT_USE_REASON=P_DO_NOT_USE_REASON;
87 commit;
88 END;
89 
90 
91 PROCEDURE RELEASE_CONTROL_REASSIGN
92    (P_LIST_HEADER_ID		NUMBER
93    )
94 AS
95 BEGIN
96   RELEASE_CONTROL_REASSIGNALL(P_LIST_HEADER_ID,8);
97 END;
98 
99 PROCEDURE RELEASE_CONTROL_MODIFY
100    (P_RLSE_CTRL_ID		NUMBER
101    )
102 AS
103 	cursor c_list_rlse is
104 	select list_header_id from ams_list_headers_all where release_control_alg_id = P_RLSE_CTRL_ID;
105 BEGIN
106 	FOR v_list_rlse IN c_list_rlse LOOP
107 		RELEASE_CONTROL_REASSIGN(v_list_rlse.list_header_id);
108   END LOOP;
109 END;
110 
111 END IEC_RLC_CTRL_PVT;
112