[Home] [Help]
PACKAGE BODY: APPS.OKE_K_ORGANIZER_PKG
Source
1 package body OKE_K_ORGANIZER_PKG as
2 /* $Header: OKEKORGB.pls 115.12 2003/04/30 20:59:06 syho ship $ */
3
4 --
5 -- Public Procedures and Functions
6 --
7
8
9 --
10 -- Procedure: populate_query_node
11 --
12 -- Purpose: return the tree node data based on the passed in where clause
13 --
14 -- Parameters:
15 -- (IN) x_user_valuse varchar2 passed in where clause
16 -- x_icon varchar2 tree node icon
17 -- x_tree_object varchar2 tree name
18 -- x_node_state number tree node state
19 -- x_low_value number low range associated w/current node
20 -- x_high_value number high range associated w/current node
21 --
22 -- (OUT) x_tree_data_table fnd_apptree.node_tbl_type store return tree node data
23 -- x_return_status varchar2 status
24 --
25
26 PROCEDURE populate_query_node (x_user_value IN varchar2,
27 x_icon IN varchar2,
28 x_tree_object IN varchar2,
29 x_node_state IN number,
30 x_low_value IN number,
31 x_high_value IN number,
32 x_tree_data_table OUT NOCOPY fnd_apptree.node_tbl_type,
33 x_return_status OUT NOCOPY varchar2) is
34
35 l_str varchar2(10000);
36 l_cur_hd number;
37 l_row_processed number;
38 l_k_number varchar2(120);
39 l_k_header_id number;
40 i number := 1;
41 j number := 1;
42 begin
43
44 l_str := 'select k_number, k_header_id
45 from oke_k_headers_secure_v k
46 where ' || x_user_value ||
47 ' order by k_number';
48
49 l_cur_hd := dbms_sql.open_cursor;
50
51 dbms_sql.parse(l_cur_hd, l_str, dbms_sql.native);
52 dbms_sql.define_column(l_cur_hd, 1, l_k_number, 120);
53 dbms_sql.define_column(l_cur_hd, 2, l_k_header_id);
54
55 l_row_processed := dbms_sql.execute(l_cur_hd);
56
57 loop
58
59 if dbms_sql.fetch_rows(l_cur_hd) > 0 then
60
61 if j >= x_low_value then
62
63 dbms_sql.column_value(l_cur_hd, 1, l_k_number);
64 dbms_sql.column_value(l_cur_hd, 2, l_k_header_id);
65
66 x_tree_data_table(i).state := x_node_state;
67 x_tree_data_table(i).depth := 1;
68 x_tree_data_table(i).label := l_k_number;
69 x_tree_data_table(i).icon := x_icon;
70 x_tree_data_table(i).value := l_k_header_id;
71 x_tree_data_table(i).type := x_tree_object;
72 i := i + 1;
73
74 end if;
75
76 else
77
78 exit;
79
80 end if;
81
82 exit when j >= x_high_value;
83 j := j + 1;
84
85 end loop;
86
87 x_return_status := 'S';
88 dbms_sql.close_cursor(l_cur_hd);
89
90 exception
91 when OTHERS then
92 x_return_status := 'E';
93
94 end populate_query_node;
95
96
97 --
98 -- Procedure: fifo_log
99 --
100 -- Purpose: update the contract documents log for user
101 --
102 -- Parameters:
103 -- (IN) x_user_id number user id
104 -- x_k_header_id number contract document id
105 -- x_object_name varchar2 tree object name
106 --
107
108 PROCEDURE fifo_log(x_user_id number,
109 x_k_header_id number,
110 x_object_name varchar2) is
111
112 log_size NUMBER := FND_PROFILE.VALUE('OKE_K_FIFO_LOG');
113
114 --
115 -- Making this procedure as AUTONOMOUS transaction.
116 --
117 PRAGMA AUTONOMOUS_TRANSACTION;
118
119 begin
120
121 --
122 -- Step 1 : Create entry if not exists; use sequence 0
123 --
124 INSERT INTO oke_k_fifo_logs
125 ( user_log_id
126 , k_header_id
127 , sequence
128 , object_name
129 , last_update_date
130 , last_updated_by
131 , creation_date
132 , created_by
133 , last_update_login )
134 SELECT oke_k_fifo_logs_s.nextval
135 , X_K_Header_ID
136 , 0
137 , X_Object_Name
138 , sysdate
139 , X_User_ID
140 , sysdate
141 , X_User_ID
142 , null
143 FROM dual
144 WHERE NOT EXISTS (
145 SELECT null
146 FROM oke_k_fifo_logs
147 WHERE k_header_id = X_K_Header_ID
148 AND object_name = X_Object_Name
149 AND created_by = X_User_ID
150 );
151
152 --
153 -- Step 2 : Update entry to sequence 0 if already exists
154 --
155 UPDATE oke_k_fifo_logs
156 SET last_update_date = sysdate
157 , last_updated_by = X_User_ID
158 , sequence = 0
159 WHERE k_header_id = X_K_Header_ID
160 AND object_name = X_Object_Name
161 AND created_by = X_User_ID
162 AND sequence <> 0;
163
164 --
165 -- Step 3 : Renumber sequence from 1 while retaining order
166 --
167 UPDATE oke_k_fifo_logs l1
168 SET sequence = (
169 SELECT count(1)
170 FROM oke_k_fifo_logs l2
171 WHERE l2.object_name = l1.object_name
172 AND l2.created_by = l1.created_by
173 AND l2.last_update_date >= l1.last_update_date )
174 WHERE object_name = X_Object_Name
175 AND created_by = X_User_ID;
176
177 IF ( sql%rowcount > nvl(log_size , 6) ) THEN
178 --
179 -- Step 4 : Prune entries older than profile setting
180 --
181 DELETE FROM oke_k_fifo_logs
182 WHERE object_name = X_Object_Name
183 AND created_by = X_User_ID
184 AND sequence > nvl(log_size , 6);
185
186 END IF;
187
188 --
189 -- This commit is needed to release the lock. The lock causes
190 -- multiple calls to this function to hang. This resolves the
191 -- hanging problem of the organizer calling any other forms.
192 --
193 commit;
194
195 end fifo_log;
196
197
198 --
199 -- Procedure: get_party_name
200 --
201 -- Purpose: get the customer/contractor name for the contract document
202 --
203 -- Parameters:
204 -- (IN) x_role varchar2 party role
205 -- x_k_header_id number contract document id
206 --
207 -- (OUT) x_party_name varchar2 party name
208 --
209
210
211 PROCEDURE get_party_name(x_role IN varchar2 ,
212 x_k_header_id IN number ,
213 x_party_name OUT NOCOPY varchar2 ) is
214 cursor c_num is
215 select object1_id1,
216 object1_id2,
217 jtot_object1_code
218 from okc_k_party_roles_b
219 where dnz_chr_id = x_k_header_id
220 and rle_code = x_role;
221
222 cursor c_party_table(l_object varchar2) is
223 select from_table,
224 where_clause
225 from jtf_objects_b
226 where object_code = l_object;
227
228 l_num c_num%ROWTYPE;
229 l_name c_party_table%ROWTYPE;
230 l_code varchar2(50);
231 i number := 0;
232 l_str varchar2(10000);
233 l_cur_hd number;
234 l_id1 varchar2(40);
235 l_id2 varchar2(200);
236 l_row_processed number;
237
238 begin
239
240 for l_num in c_num loop
241
242 i := i + 1;
243
244 if (i >= 2) then
245 exit;
246 end if;
247
248 l_code := l_num.jtot_object1_code;
249 l_id1 := l_num.object1_id1;
250
251 if (l_num.object1_id2 <> '#') then
252 l_id2 := l_num.object1_id2;
253 end if;
254
255 end loop;
256
257 if (i >= 2) then
258
259 fnd_message.set_name('OKE', 'OKE_MULTIPLE_PROMPT');
260 x_party_name := fnd_message.get;
261
262 elsif (i = 1) then
263
264 open c_party_table(l_code);
265 fetch c_party_table into l_name;
266 close c_party_table;
267 /*
268 l_str := 'select name from ' || l_name.from_table
269 || ' where id1 = ' || to_number(l_id1);
270
271 if (l_id2 is not null) then
272 l_str := l_str || ' and id2 = ' || to_number(l_id2);
273 end if;
274 */
275
276 l_str := 'select name from ' || l_name.from_table
277 || ' where id1 = :id1';
278
279 if (l_id2 is not null) then
280 l_str := l_str || ' and id2 = :id2';
281 end if;
282
283 if (l_name.where_clause is not null) then
284 l_str := l_str || ' and ' || l_name.where_clause;
285 end if;
286
287 l_cur_hd := dbms_sql.open_cursor;
288
289 dbms_sql.parse(l_cur_hd, l_str, dbms_sql.native);
290 dbms_sql.bind_variable(l_cur_hd, 'id1', to_number(l_id1));
291 if (l_id2 is not null) then
292 dbms_sql.bind_variable(l_cur_hd, 'id2', to_number(l_id2));
293 end if;
294 dbms_sql.define_column(l_cur_hd, 1, x_party_name, 360);
295
296 l_row_processed := dbms_sql.execute(l_cur_hd);
297
298 loop
299
300 if dbms_sql.fetch_rows(l_cur_hd) > 0 then
301 dbms_sql.column_value(l_cur_hd, 1, x_party_name);
302 else
303 exit;
304 end if;
305
306 end loop;
307
308 dbms_sql.close_cursor(l_cur_hd);
309
310 end if;
311
312 exception
313 when OTHERS then
314 x_party_name := null;
315
316 end get_party_name;
317
318 end OKE_K_ORGANIZER_PKG;