[Home] [Help]
PACKAGE BODY: APPS.CSF_AUTO_COMMIT_PVT
Source
1 package body csf_auto_commit_pvt as
2 /* $Header: CSFVCMTB.pls 120.0.12020000.2 2012/07/25 13:23:20 knathsh ship $ */
3
4 --================================================--
5 -- private constants, types, variables and cursors --
6 --================================================--
7
8 g_errbuf_success constant varchar2(250) :=
9 'Program completed successfully. ';
10 g_errbuf_warning constant varchar2(250) :=
11 'Program completed with exceptions. ';
12 g_errbuf_error constant varchar2(250) :=
13 'Program terminated with exceptions. ';
14 g_uom_hours varchar2(60) := null;
15 g_retcode_success constant number := 0;
16 g_retcode_warning constant number := 1;
17 g_retcode_error constant number := 2;
18 conc_fail EXCEPTION;
19 TYPE requests IS TABLE of NUMBER(15) INDEX BY BINARY_INTEGER;
20
21 --=============================================--
22 -- private procedure and function declarations --
23 --=============================================--
24
25 procedure print ( p_data varchar2 );
26 procedure printlog ( p_data varchar2 );
27 function convert_to_days
28 ( p_duration number
29 , p_uom varchar2
30 , p_uom_hours varchar2
31 )
32 return number;
33 --===========================================--
34 -- public procedure and function definitions --
35 --===========================================--
36
37 ------------------------------------------
38 -- procedure update_planned_task_status --
39 ------------------------------------------
40 procedure update_planned_task_status
41 ( x_errbuf OUT NOCOPY VARCHAR2
42 , x_retcode OUT NOCOPY VARCHAR2
43 , p_task_source IN VARCHAR2 DEFAULT NULL
44 , p_task_query_flag IN VARCHAR2 DEFAULT NULL
45 , p_terr_flag IN VARCHAR2 DEFAULT NULL
46 , p_query_id IN VARCHAR2 DEFAULT NULL
47 , p_commit_horizon IN NUMBER DEFAULT NULL
48 , p_commit_horizon_uom IN VARCHAR2 DEFAULT NULL
49 , p_from_task_id IN NUMBER DEFAULT NULL
50 , p_to_task_id IN NUMBER DEFAULT NULL
51 , p_commit_horizon_from IN NUMBER DEFAULT NULL
52 , p_commit_uom_from IN VARCHAR2 DEFAULT NULL
53 )
54 is
55
56 --
57 -- variables for API output parameters
58 --
59 l_return_status varchar2(2000);
60 l_msg_data varchar2(2000);
61 l_msg_count number;
62 l_task_ovn number;
63 l_task_status_name varchar2(2000);
64 l_task_status_id number;
65 l1 Number;
66 --
67 --
68 l_query_id number;
69 --
70 --
71 ---------------------------------------
72 l_first_task_id number;
73 l_last_task_id number;
74
75 p_res_id jtf_number_table ;
76 p_res_type jtf_varchar2_table_2000 ;
77 p_res_name jtf_varchar2_table_2000 ;
78 p_res_typ_name jtf_varchar2_table_2000 ;
79 p_res_key jtf_varchar2_table_2000 ;
80 l_task_id_tbl jtf_number_table;
81 l_query VARCHAR2(2000);
82
83 CURSOR C_Terr_Resource
84 IS SELECT DISTINCT TR.RESOURCE_ID RESOURCE_ID,
85 TR.RESOURCE_TYPE RESOURCE_TYPE,
86 TR.RESOURCE_NAME RESOURCE_NAME,
87 CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME( TR.RESOURCE_TYPE ) RESOURCE_TYPE_NAME,
88 TR.RESOURCE_ID||'-'||TR.RESOURCE_TYPE
89 FROM CSF_SELECTED_RESOURCES_V TR
90 ORDER BY UPPER(TR.RESOURCE_NAME);
91
92
93
94 TYPE ref_cursor_type IS REF CURSOR;
95 TYPE task_split_tbl_type IS TABLE OF jtf_tasks_b.task_split_flag%TYPE;
96 -- REF Cursor to form different query based on different conditions.
97 c_task_list ref_cursor_type;
98
99
100 x_request_id NUMBER;
101 l_primary_request_stack REQUESTS;
102 g_batch_total NUMBER := 0;
103 l_completed_batch_count NUMBER :=0 ;
104 l_temp_id NUMBER := 0;
108 l_dev_status VARCHAR2(80);
105 primary_ptr NUMBER := 1;
106 l_call_status BOOLEAN ;
107 l_dev_phase VARCHAR2(80);
109 l_dummy1 VARCHAR2(500);
110 l_dummy2 VARCHAR2(500);
111 l_dummy3 VARCHAR2(500);
112 unfinished BOOLEAN := TRUE;
113
114 l_sleep_time NUMBER := 20;
115 l_sleep_time_char VARCHAR2(30);
116 l_failed_request_id NUMBER;
117 l_convert_dur_to_day NUMBER;
118 l_start_date DATE;
119 l_end_date DATE;
120 l_commit_horizon BOOLEAN:=FALSE;
121 l_tot_tasks_queried NUMBER := 0;
122 begin
123 --
124 -- initialise
125 --
126 l_query_id := fnd_profile.value('CSF_DEFAULT_AUTO_COMMIT_QUERY');
127 fnd_message.set_name('CSF', 'CSF_AUTO_COMMIT_STARTED');
128 print(fnd_message.get);
129 --
130 x_errbuf := g_errbuf_success;
131 x_retcode := g_retcode_success;
132 fnd_msg_pub.Initialize;
133 --
134
135
136 If p_query_id is not null then
137 l_query_id := p_query_id;
138 csf_tasks_pub.commit_schedule(
139 p_api_version => 1.0
140 , p_init_msg_list => fnd_api.g_false
141 , p_commit => fnd_api.g_true
142 , x_return_status => l_return_status
143 , x_msg_count => l_msg_count
144 , x_msg_data => l_msg_data
145 , p_query_id => l_query_id
146 , p_task_source => p_task_source
147 , p_commit_horizon => p_commit_horizon
148 , p_commit_horizon_uom => p_commit_horizon_uom
149 , p_from_task_id => p_from_task_id
150 , p_to_task_id => p_to_task_id
151 , p_commit_horizon_from => p_commit_horizon_from
152 , p_commit_uom_from => p_commit_uom_from
153 );
154 ELSIF (p_task_source is not null and p_task_source ='TERRITORY') and (p_from_task_id is not null and p_from_task_id is not null)
155 THEN
156
157 csf_tasks_pub.commit_schedule(
158 p_api_version => 1.0
159 , p_init_msg_list => fnd_api.g_false
160 , p_commit => fnd_api.g_true
161 , x_return_status => l_return_status
162 , x_msg_count => l_msg_count
163 , x_msg_data => l_msg_data
164 , p_query_id => -9999
165 , p_task_source => p_task_source
166 , p_commit_horizon => p_commit_horizon
167 , p_commit_horizon_uom => p_commit_horizon_uom
168 , p_from_task_id => p_from_task_id
169 , p_to_task_id => p_to_task_id
170 , p_commit_horizon_from => p_commit_horizon_from
171 , p_commit_uom_from => p_commit_uom_from
172 );
173
174 elsif (p_task_source is not null and p_task_source ='TERRITORY') and (p_from_task_id is null and p_from_task_id is null)
175 then
176
177 IF p_commit_horizon IS NOT NULL AND p_commit_horizon_uom IS NOT NULL
178 THEN
179 l_convert_dur_to_day :=convert_to_days(p_commit_horizon,p_commit_horizon_uom, g_uom_hours);
180 l_end_date :=sysdate+ l_convert_dur_to_day;
181 l_commit_horizon :=TRUE;
182 END IF;
183 l_convert_dur_to_day:=0;
184 IF p_commit_horizon_from IS NOT NULL AND p_commit_uom_from IS NOT NULL
185 THEN
186 l_convert_dur_to_day :=convert_to_days(p_commit_horizon_from,p_commit_uom_from, g_uom_hours);
187 l_start_date :=sysdate-l_convert_dur_to_day;
188 ELSE
189 l_start_date :=sysdate;
190 END IF;
191
192 fnd_message.set_name('CSF', 'CSF_AUTO_COMMIT_PARAMS');
193 fnd_message.set_token('SOURCE_FLAG', p_task_source);
194 fnd_message.set_token('QUERY', p_query_id);
195 fnd_message.set_token('COMMIT_HORIZON', p_commit_horizon||' '||p_commit_horizon_uom);
196 fnd_message.set_token('FROMDATE', to_char(l_start_date,'dd-mon-rrrr hh24:mi:ss'));
197 fnd_message.set_token('TODATE', to_char(l_end_date,'dd-mon-rrrr hh24:mi:ss'));
198 printlog(fnd_message.get);
199
200
201 OPEN c_terr_resource;
202 FETCH c_terr_resource
203 BULK COLLECT INTO
204 p_res_id
205 , p_res_type
206 , p_res_name
207 , p_res_typ_name
208 , p_res_key;
209 CLOSE c_terr_resource;
210 printlog(' ===================================================================================================');
211
212
213
214
215 IF not (l_commit_horizon)
216 THEN
217
218 l_query := 'SELECT DISTINCT NVL(t.parent_task_id, t.task_id) task_id
219 FROM jtf_tasks_b t
220 , jtf_task_assignments ta
221 , jtf_task_statuses_b ts
222 , (SELECT TO_NUMBER(SUBSTR(column_value
223 , 1
224 , INSTR(column_value, ''-'', 1, 1) - 1
225 )
226 )resource_id
227 ,SUBSTR(column_value
228 , INSTR(column_value, ''-'', 1, 1) + 1
229 , LENGTH(column_value)
230 ) resource_type
231 FROM TABLE(CAST(:p_res_key AS jtf_varchar2_table_2000))
232 ) res_info
233 WHERE ta.resource_id = res_info.resource_id
234 AND ta.resource_type_code = res_info.resource_type
235 AND ts.task_status_id = ta.assignment_status_id
239 AND NVL(ts.assigned_flag, ''N'') <> ''Y''
236 AND NVL(ts.closed_flag, ''N'') = ''N''
237 AND NVL(ts.completed_flag, ''N'') = ''N''
238 AND NVL(ts.cancelled_flag, ''N'') = ''N''
240 AND NVL(ts.working_flag, ''N'') <> ''Y''
241 AND t.task_id = ta.task_id
242 AND ta.booking_start_date>=:l_start_date
243 AND t.task_type_id NOT IN (20,21)
244 AND NVL(t.deleted_flag, ''N'') <> ''Y''
245 AND t.source_object_type_code = ''SR''
246 ORDER BY 1 ASC';
247 OPEN c_task_list FOR l_query USING p_res_key,l_start_date;
248 ELSE
249
250 l_query := 'SELECT DISTINCT NVL(t.parent_task_id, t.task_id) task_id
251 FROM jtf_tasks_b t
252 , jtf_task_assignments ta
253 , jtf_task_statuses_b ts
254 , (SELECT TO_NUMBER(SUBSTR(column_value
255 , 1
256 , INSTR(column_value, ''-'', 1, 1) - 1
257 )
258 )resource_id
259 ,SUBSTR(column_value
260 , INSTR(column_value, ''-'', 1, 1) + 1
261 , LENGTH(column_value)
262 ) resource_type
263 FROM TABLE(CAST(:1 AS jtf_varchar2_table_2000))
264 ) res_info
265 WHERE ta.resource_id = res_info.resource_id
266 AND ta.resource_type_code = res_info.resource_type
267 AND ts.task_status_id = ta.assignment_status_id
268 AND NVL(ts.closed_flag, ''N'') = ''N''
269 AND NVL(ts.completed_flag, ''N'') = ''N''
270 AND NVL(ts.cancelled_flag, ''N'') = ''N''
271 AND NVL(ts.assigned_flag, ''N'') <> ''Y''
272 AND NVL(ts.working_flag, ''N'') <> ''Y''
273 AND t.task_id = ta.task_id
274 AND ta.booking_start_date between :2 AND :3
275 AND t.task_type_id NOT IN (20,21)
276 AND NVL(t.deleted_flag, ''N'') <> ''Y''
277 AND t.source_object_type_code = ''SR''
278 ORDER BY 1 ASC';
279 printlog(' Query '||l_query);
280 OPEN c_task_list FOR l_query USING p_res_key,l_start_date,l_end_date;
281 END IF;
282
283 l_primary_request_stack.delete;
284 LOOP
285 FETCH c_task_list BULK COLLECT INTO l_task_id_tbl LIMIT 150;
286 IF l_task_id_tbl.count > 0
287 THEN
288 l_first_task_id :=l_task_id_tbl(l_task_id_tbl.FIRST);
289 l_last_task_id :=l_task_id_tbl(l_task_id_tbl.LAST);
290 l_tot_tasks_queried:=l_tot_tasks_queried+l_task_id_tbl.COUNT;
291 END IF;
292 if l_first_task_id is not null
293 then
294
295 x_request_id :=
296 fnd_request.submit_request(
297 application => 'CSF'
298 , program => 'CSF_AUTO_COMMIT'
299 , description => NULL
300 , start_time => NULL
301 , sub_request => NULL
302 , argument1 => p_task_source
303 , argument2 => p_task_query_flag
304 , argument3 => p_terr_flag
305 , argument4 => p_query_id
306 , argument5 => p_commit_horizon
307 , argument6 => p_commit_horizon_uom
308 , argument7 => l_first_task_id
309 , argument8 => l_last_task_id
310 , argument9 => p_commit_horizon_from
311 , argument10 => p_commit_uom_from
312
313 );
314 commit;
315 g_batch_total := g_batch_total+1;
316 l_primary_request_stack(g_batch_total) := x_request_id;
317 fnd_message.set_name('CSF', 'CSF_AUTO_COMMIT_PARALLEL');
318 fnd_message.set_token('REQUEST_ID', x_request_id);
319 fnd_message.set_token('FROMTASKID', l_first_task_id);
320 fnd_message.set_token('TOTASKID', l_last_task_id);
321 printlog(fnd_message.get);
322 end if;
323
324
325 EXIT WHEN c_task_list%NOTFOUND;
326 END LOOP;
327
328
329 fnd_message.set_name('CSF', 'CSF_AUTO_COMMIT_RESOURCES');
330 fnd_message.set_token('NOOFTASKS', l_tot_tasks_queried);
331 fnd_message.set_token('COUNT', p_res_key.count);
332 print(fnd_message.get);
333
334 IF g_batch_total >0
335 THEN
336
337 l_return_status := fnd_api.g_ret_sts_success;
338 primary_ptr := 1;
339 fnd_message.set_name('CSF', 'CSF_AUTO_COMMIT_PRG_TOTAL');
340 fnd_message.set_token('TOTAL', g_batch_total);
341 printlog(fnd_message.get);
342 END IF;
343 printlog(' ===================================================================================================');
344
345 dbms_lock.sleep(l_sleep_time);
346
347 IF g_batch_total >0
348 THEN
349 WHILE l_completed_batch_count <= g_batch_total
350 LOOP
351
352 IF l_primary_request_stack(primary_ptr) IS NOT NULL
353 THEN
354 l_call_status := fnd_concurrent.get_request_status
355 ( request_id => l_primary_request_stack(primary_ptr),
356 phase => l_dummy1,
357 status => l_dummy2,
358 dev_phase => l_dev_phase,
359 dev_status => l_dev_status,
360 message => l_dummy3);
361 IF (NOT l_call_status) THEN
362
363 fnd_message.set_name('CSF', 'CSF_AUTO_COMMIT_PRG_ERR');
364 fnd_message.set_token('ERROR', l_failed_request_id);
365 print(fnd_message.get);
366 l_failed_request_id := l_primary_request_stack(primary_ptr);
367 raise fnd_api.g_exc_unexpected_error;
368 END IF;
369 IF l_dev_phase = 'COMPLETE'
370 THEN
371 l_failed_request_id := l_primary_request_stack(primary_ptr);
372 l_primary_request_stack(primary_ptr) := null;
373 l_completed_batch_count := l_completed_batch_count +1;
374 IF l_dev_status = 'ERROR'
375 THEN
376
377 fnd_message.set_name('CSF', 'CSF_AUTO_COMMIT_PRG_ERR');
378 fnd_message.set_token('ERROR', l_failed_request_id);
379 print(fnd_message.get);
380 raise fnd_api.g_exc_unexpected_error;
381 ELSIF l_dev_status = 'WARNING'
382 THEN
383 fnd_message.set_name('CSF', 'CSF_AUTO_COMMIT_PRG_WAR');
384 fnd_message.set_token('WARNING', l_failed_request_id);
385 print(fnd_message.get);
386 l_return_status := 'W'; --WARNING;
387 ELSE
388 fnd_message.set_name('CSF', 'CSF_AUTO_COMMIT_PRG_COMP');
389 fnd_message.set_token('COMPLETED', l_failed_request_id);
390 print(fnd_message.get);
391 END IF;
392
393 END IF;
394 END IF;
395 primary_ptr := primary_ptr+1;
396
397 IF l_completed_batch_count = g_batch_total THEN
398 l_completed_batch_count := l_completed_batch_count+1;
399 unfinished := FALSE;
400 ELSE
401 IF primary_ptr > g_batch_total
402 THEN
403 dbms_lock.sleep(l_sleep_time);
404 primary_ptr := 1;
405 END IF;
406 END IF;
407 END LOOP;
408 ELSE
409 fnd_message.set_name('CSF','CSF_NO_TASK_FOR_RESOURCE');
410 print(fnd_message.get);
411 l_return_status := 'W'; --WARNING;
412 END IF;
413 printlog(' =================================================================================================================');
414 END IF;
415 --
416
417 if l_return_status <> fnd_api.g_ret_sts_success
418 then
419
420 raise fnd_api.g_exc_unexpected_error;
421 end if;
422 --
423 --
424 if l_msg_count > 0 then
425 FOR counter IN REVERSE 1..l_msg_count
426 LOOP
427 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
428 print(l_msg_data);
429 end loop;
430 end if;
431 fnd_message.set_name('CSF', 'CSF_AUTO_COMMIT_DONE');
432 print(fnd_message.get);
433
434 exception
435 when fnd_api.g_exc_unexpected_error then
436
437 x_errbuf := g_errbuf_warning;
438 x_retcode := g_retcode_warning;
439 if FND_MSG_PUB.Count_Msg > 0 then
440 FOR counter IN REVERSE 1..FND_MSG_PUB.Count_Msg
441 LOOP
442 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
443 print(l_msg_data);
444 end loop;
445 end if;
446 when others then
447
448 x_errbuf := g_errbuf_error;
449 x_retcode := g_retcode_error;
450
451 if l_msg_count > 0 then
452 FOR counter IN REVERSE 1..l_msg_count
453 LOOP
454 fnd_msg_pub.get(counter,FND_API.G_FALSE,l_msg_data,l1);
455 print(l_msg_data);
456 end loop;
457 end if;
458 fnd_message.set_name('CSF', 'CSF_AUTO_COMMIT_EXCEPTION');
459 fnd_message.set_token('P_MESSAGE', sqlerrm);
460 print(fnd_message.get);
461 fnd_message.set_name('CSF', 'CSF_AUTO_COMMIT_ABORT');
462 print(fnd_message.get);
463 end update_planned_task_status;
464
465 --============================================--
466 -- private procedure and function definitions --
467 --============================================--
468
469 -----------------------------------------------------
470 -- print a message to the output and the log file ---
471 -----------------------------------------------------
472 procedure print ( p_data varchar2 )
473 is
474 begin
475 fnd_file.put_line(fnd_file.output, p_data);
476 fnd_file.put_line(fnd_file.log, p_data);
477 end print;
478
479 procedure printlog ( p_data varchar2 )
480 is
481 begin
482 fnd_file.put_line(fnd_file.log, p_data);
483 end printlog;
484
485
486 function convert_to_days
487 ( p_duration number
488 , p_uom varchar2
489 , p_uom_hours varchar2
490 )
491 return number
492 is
493 l_value number;
494 begin
495 l_value := inv_convert.inv_um_convert
496 ( item_id => 0
497 , precision => 20
498 , from_quantity => p_duration
499 , from_unit => p_uom
500 , to_unit => p_uom_hours
501 , from_name => null
502 , to_name => null
503 );
504 return l_value/24;
505 end convert_to_days;
506 begin
507 g_uom_hours := fnd_profile.value('CSF_UOM_HOURS');
508 end csf_auto_commit_pvt;