1 PACKAGE BODY cto_auto_dropship_pk AS
2 /*$Header: CTODROPB.pls 120.4 2010/07/21 07:48:34 abhissri ship $ */
3 /*============================================================================+
4 | Copyright (c) 1999 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8 | |
9 | FILE NAME : CTODROPB.pls |
10 | DESCRIPTION: |
11 | Contain all CTO and WF related APIs for AutoCreate Purchase |
12 | Requisitions. This Package creates the following |
13 | Procedures |
14 | 1. AUTO_CREATE_DROPSHIP |
15 | Functions |
16 | HISTORY : |
17 | 22-FEB-2002 : Created By Sushant Sawant |
18 | |
19 | Modified on 14-MAY-2002 by Sushant Sawant |
20 | Fixed Bug 2367220 |
21 | 17-DEC-2003 : Bugfix 3319313 |
22 | - fixed source code issue |
23 | - fixed OQ-CQ issue in the cursor |
24 | - Replaced fnd_file with oe_debug_pub for consistency |
25 | 01-Jun-2005 : Renga Kannan Added NOCOPY HINT for all out parameters.
26
27 | 05-Jul-2005 : Renga Kannan Modified code for MOAC project
28 | |
29 | |
30 =============================================================================*/
31
32 g_pkg_name CONSTANT VARCHAR2(30) := 'CTO_AUTO_DROPSHIP_PK';
33 gMrpAssignmentSet NUMBER ;
34
35
36 /**************************************************************************
37 Procedure: AUTO_CREATE_DROPSHIP
38 Parameters: p_sales_order NUMBER -- Sales Order number.
39 p_dummy_field VARCHAR2 -- Dummy field for the Concurrent Request.
40 p_sales_order_line_id NUMBER -- Sales Order Line number.
41 p_organization_id VARCHAR2 -- Ship From Organization ID.
42 current_organization_id NUMBER -- Current Org ID
43 p_offset_days NUMBER -- Offset days.
44
45 Description: This procedure is called from the concurrent progran to run the
46 AutoCreate DropShip Requisitions.
47 *****************************************************************************/
48 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
49
50 PROCEDURE auto_create_dropship (
51 errbuf OUT NOCOPY VARCHAR2,
52 retcode OUT NOCOPY VARCHAR2,
53 p_sales_order NUMBER,
54 p_dummy_field VARCHAR2,
55 p_sales_order_line_id NUMBER,
56 p_organization_id VARCHAR2,
57 current_organization_id NUMBER, -- VARCHAR2,
58 p_offset_days NUMBER ) AS
59
60
61 -- following cursor will select the sales order lines to be processed.
62 -- it will pick all the Booked and scheduled Orders for ATO items.
63 -- with WF status at
64 -- for the parameters Organization_id, Sales Order Number, Sales Order line id
65 -- with in the specified number of Offset days
66
67 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
68 Bug 4574073: Performance issue: Removed the cursor and changed to dyanamic
69 sql which would join only the required tables to improve performance. Also, we
70 will drive from Oe table to workflow tables if lineid or order number are passed.
71 Other wise drive from workflow table to oe table. This is to enable effective use
72 of the index on item_type, item_key on wf_item_activity_statuses.
73 Locking issue: This happens when this program is run in parallel with AutoCreate
74 Purchase requisition. To reduce record_locked exceptions to bare minimum, we will
75 process from a array and commit after each record.
76 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
77 -- local variables
78 p_po_quantity NUMBER := NULL;
79 l_stmt_num NUMBER;
80 p_dummy VARCHAR2(2000);
81 lSourceCode VARCHAR2(100);
82 v_rsv_quantity NUMBER(8,2);
83 v_sourcing_rule_exists VARCHAR2(100);
84 v_sourcing_org NUMBER;
85 v_source_type NUMBER;
86 v_transit_lead_time NUMBER;
87 v_exp_error_code NUMBER;
88 v_rec_count NUMBER := 0;
89 v_rec_count_noerr NUMBER := 0;
90 conc_status BOOLEAN ;
91 current_error_code VARCHAR2(20) := NULL;
92 v_x_error_msg_count NUMBER;
93 v_x_hold_result_out VARCHAR2(1);
94 v_x_hold_return_status VARCHAR2(1);
95 v_x_error_msg VARCHAR2(150);
96 x_return_status VARCHAR2(1);
97 l_organization_id NUMBER;
98 p_new_order_quantity NUMBER(8,2);
99 l_res BOOLEAN;
100 l_batch_id NUMBER;
101 v_activity_status_code VARCHAR2(10);
102 l_inv_quantity NUMBER;
103
104 l_request_id NUMBER;
105 l_program_id NUMBER;
106 l_source_document_type_id NUMBER;
107 l_wip_org_id NUMBER ;
108
109 l_active_activity VARCHAR2(8);
110 l_current_org_id Number; -- MOAC change
111
112 xuserid Number;
113 xrespid number;
114 xrespapplid Number;
115 x_msg_count Number;
116 x_msg_data Varchar2(1000);
117 -- bug4574073 : new variables.
118 sql_stmt VARCHAR2(5000);
119 drive_mark NUMBER := 0;
120 i NUMBER;
121 dummy NUMBER;
122 indx NUMBER;
123
124 record_locked EXCEPTION;
125 pragma exception_init (record_locked, -54);
126 /*invalid_cursor EXCEPTION;
127 pragma exception_init (invalid_cursor, -1001);
128
129 Type lines_rec_type is record
130 (
131 line_id number,
132 org_id number,
133 ship_from_org_id number,
134 schedule_ship_date date
135 );
136 eligible_lines_rec lines_rec_type;
137 TYPE eligibleCurTyp is REF CURSOR ;
138 eligible_lines eligibleCurTyp;*/
139
140 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
141 TYPE date_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
142 line_id_arr num_tab;
143 org_id_arr num_tab;
144 ship_from_org_id_arr num_tab;
145 schedule_ship_date_arr date_tab;
146 BEGIN
147
148 -- initialize the program_id and the request_id from the concurrent request.
149 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
150 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
151
152 -- set the return status.
153 x_return_status := FND_API.G_RET_STS_SUCCESS ;
154
155 -- Set the return code to success
156
157 RETCODE := 0;
158
159 lSourceCode := FND_PROFILE.VALUE('ONT_SOURCE_CODE'); --bugfix 3319313
160 IF PG_DEBUG <> 0 THEN
161 oe_debug_pub.add('l_source_code = '||lsourcecode);
162 END IF;
163
164 -- set the batch_id to the request_id
165 l_batch_id := FND_GLOBAL.CONC_REQUEST_ID;
166
167 -- Log all the input parameters
168 l_stmt_num := 1;
169
170 -- for all the sales order lines (entered, booked )
171 -- Given parameters.
172 IF PG_DEBUG <> 0 THEN
173 oe_debug_pub.add('+---------------------------------------------------------------------------+');
174 oe_debug_pub.add('+------------------ Parameters passed into the procedure ------------------+');
175 oe_debug_pub.add('Sales order : '||p_sales_order );
176 oe_debug_pub.add('Sales Order Line ID[Model] : '||to_char(p_sales_order_line_id));
177 oe_debug_pub.add('Organization_id : '||p_organization_id);
178 oe_debug_pub.add('Offset Days : '||to_char(p_offset_days));
179 oe_debug_pub.add('+---------------------------------------------------------------------------+');
180 END IF;
181
182 l_organization_id := p_organization_id;
183
184 -- Open loop for selecting all the eligible lines.
185 -- Opening the cursor. This cursor selects the eligible oe lines based on the
186
187 l_organization_id := p_organization_id;
188
189 /******************Begin Bugfix 4384668 ***************************************/
190 /*
191 NOTE: We are doing a insert into bom_cto_order_lines_temp select .... using dyanamic sql
192 followed by a select ...bulk collect... from bom_cto_order_lines_temp. A more efficient
193 approach would be to do a direct select..bulk collect.. from oe_order_lines...
194 in the dyanamic sql instead of going via the GTT. However bulk collect with dyanamic sql
195 is supported 9i onwards. For 11.5.9, we need to make it compatible with 8i database as well.
196 This restriction may not be there in R12. Please keep this in mind while front porting.
197 */
198
199 delete from bom_cto_order_lines_temp;
200
201 drive_mark := 0;
202 sql_stmt := 'INSERT INTO bom_cto_order_lines_temp (line_id, org_id, ship_from_org_id, schedule_ship_date, inventory_item_id) '||
203 'SELECT oel.line_id, oel.org_id, oel.ship_from_org_id, oel.schedule_ship_date, 1 '||
204 'from oe_order_lines_all oel, '||
205 ' mtl_system_items msi, '||
206 ' wf_item_activity_statuses was, '||
207 ' wf_process_activities WPA '||
208 'where oel.inventory_item_id = msi.inventory_item_id '||
209 'and oel.ship_from_org_id = msi.organization_id '||
210 'and oel.source_type_code = ''EXTERNAL'' '||
211 'and msi.bom_item_type = 4 '||
212 'and oel.open_flag = ''Y'' '||
213 'and (oel.cancelled_flag is null '||
214 ' or oel.cancelled_flag = ''N'') '||
215 'and oel.booked_flag = ''Y'' '||
216 'and oel.ordered_quantity > 0 '||
217 'and msi.replenish_to_order_flag = ''Y'' '||
218 'and msi.pick_components_flag = ''N'' '||
219 'and was.item_type = ''OEOL'' '||
220 'and was.activity_status = ''NOTIFIED'' '||
221 'and was.item_type = wpa.activity_item_type '||
222 'and was.process_activity = wpa.instance_id '||
223 'and wpa.activity_name in '||
224 '(''EXECUTECONCPROGAFAS'', ''CREATE_SUPPLY_ORDER_ELIGIBLE'', ''PURCHASE RELEASE ELIGIBLE'') ';
225
226 /* We want to do an explicit to_char() when order_number or line_id
227 * parameter is passed because we are driving from OEL->WAS. If we are driving
228 * from WF tables into OE then to_char() should not be used.
229 *
230 * Here, the problem was because of the implicit type conversion that was happening on the WAS side.
231 * That was preventing the item_key column of the WAS PK index from being used during index access.
232 * It was effectively using the index only on the item_type column and that is the reason why it was slow.
233 */
234
235 if p_sales_order is null and p_sales_order_line_id is null then
236 sql_stmt := sql_stmt ||' and was.item_key = oel.line_id ' ;
237 else
238 sql_stmt := sql_stmt ||' and was.item_key = to_char(oel.line_id) ' ;
239 end if;
240
241 /* Given an Order Number */
242 -- Do we really need to validate against mtl_sales_orders and oe_transaction_types_tl ?
243 -- Will there at all exist any order_number in oe_order_header which shall ever fail this validation ?
244 if p_sales_order is not null then
245 drive_mark := drive_mark + 1;
246 sql_stmt := sql_stmt || ' and oel.header_id in' ||
247 ' (select oeh.header_id '||
248 ' from oe_order_headers_all oeh, '||
249 ' oe_transaction_types_tl oet, '||
250 ' mtl_sales_orders mso '||
251 ' where oeh.order_number = to_char( :p_sales_order) '||
252 ' and oeh.order_type_id = oet.transaction_type_id '||
253 ' and mso.segment1 = to_char(oeh.order_number) '||
254 ' and mso.segment2 = oet.name '||
255 ' and oet.language = (select language_code '||
256 ' from fnd_languages'||
257 ' where installed_flag = ''B'')' ||
258 ' ) ' ;
259 end if;
260
261 /* Given a Order Line ID */
262 if p_sales_order_line_id is not null then
263 drive_mark := drive_mark + 2;
264 sql_stmt := sql_stmt ||' and oel.line_id in (select oelc.line_id '||
265 'from oe_order_lines_all oelc '||
266 'where oelc.ato_line_id = :p_sales_order_line_id '||
267 'and (oelc.item_type_code = ''CONFIG'' '||
268 --Adding INCLUDED item type code for SUN ER#9793792
269 --' or (oelc.item_type_code in (''STANDARD'',''OPTION'') '||
270 ' or (oelc.item_type_code in (''STANDARD'',''OPTION'',''INCLUDED'') '||
271 ' and ato_line_id = line_id)) '||
272 ') ';
273 end if;
274
275 /* Given an Organization */
276 if p_organization_id is not null then
277 drive_mark := drive_mark + 4;
278 sql_stmt := sql_stmt ||' and oel.ship_from_org_id = :l_organization_id ';
279 end if;
280
281 /* Given Offset days */
282 if p_offset_days is not null then
283 drive_mark := drive_mark + 8;
284 sql_stmt := sql_stmt ||' and (SYSDATE + nvl(:p_offset_days, 0)) >= '||
285 '(select CAL.CALENDAR_DATE '||
286 ' from bom_calendar_dates cal, '||
287 ' mtl_parameters mp '||
288 ' where mp.organization_id = oel.ship_from_org_id '||
289 ' and cal.calendar_code = mp.calendar_code '||
290 ' and cal.exception_set_id = mp.calendar_exception_set_id '||
291 ' and cal.seq_num = '||
292 ' (select cal2.prior_seq_num - '||
293 ' (ceil(nvl(msi.fixed_lead_time,0) + '||
294 ' nvl(msi.variable_lead_time,0) * '||
295 ' INV_CONVERT.inv_um_convert '||
296 ' (oel.inventory_item_id, '||
297 ' null, '||
298 ' oel.ordered_quantity , '||
299 ' oel.order_quantity_uom, '||
300 ' msi.primary_uom_code, '||
301 ' null, '||
302 ' null) '||
303 ' )) '||
304 ' from bom_calendar_dates cal2 '||
305 ' where cal2.calendar_code = mp.calendar_code '||
306 ' and cal2.exception_set_id = mp.calendar_exception_set_id '||
307 ' and cal2.calendar_date =trunc(oel.schedule_ship_date) '||
308 ' )) ';
309
310 end if;
311
312 IF PG_DEBUG <> 0 THEN
313 oe_debug_pub.add ('The dyanamic sql generated is');
314 oe_debug_pub.add ('SQL: ' || substr(sql_stmt,1, 1500));
315 oe_debug_pub.add (substr(sql_stmt,1501,3000));
316 oe_debug_pub.add ('The drive_mark is '||drive_mark);
317 END IF;
318
319 /*
320 Below, we execute the sql statement according to which parameters
321 we have selected. The drive_mark variable tells us which parameters
322 we are using, so we are sure to send the right ones to SQL.
323 */
324
325 if (drive_mark = 0) then
326 -- No (optional) parameter is passed
327 EXECUTE IMMEDIATE sql_stmt;
328
329 elsif (drive_mark = 1) then
330 -- Only Order_Number is passed
331 EXECUTE IMMEDIATE sql_stmt USING p_sales_order;
332
333 elsif (drive_mark = 2) then
334 -- Only Line_Id is passed
335 EXECUTE IMMEDIATE sql_stmt USING p_sales_order_line_id;
336
337 elsif (drive_mark = 3) then
338 -- Order Number and Line_Id is passed
339 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id;
340
341 elsif (drive_mark = 4) then
342 -- Only Orgn_Id is passed
343 EXECUTE IMMEDIATE sql_stmt USING l_organization_id;
344
345 elsif (drive_mark = 5) then
346 -- Order_Number and Orgn_Id is passed
347 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, l_organization_id;
348
349 elsif (drive_mark = 6) then
350 -- Line_id and Orgn_Id is passed
351 EXECUTE IMMEDIATE sql_stmt USING p_sales_order_line_id, l_organization_id;
352
353 elsif (drive_mark = 7) then
354 -- Order_number, Line_Id and Orgn_Id is passed
355 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id, l_organization_id;
356
357 elsif (drive_mark = 8) then
358 -- Offset_Days is passed
359 EXECUTE IMMEDIATE sql_stmt USING p_offset_days;
360
361 elsif (drive_mark = 9) then
362 -- Order_Number and Offset_Days is passed
363 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_offset_days;
364
365 elsif (drive_mark = 10) then
366 -- Line_id and Offset_Days is passed
367 EXECUTE IMMEDIATE sql_stmt USING p_sales_order_line_id, p_offset_days;
368
369 elsif (drive_mark = 11) then
370 -- Order_Number, Line_id and Offset_Days is passed
371 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id, p_offset_days;
372
373 elsif (drive_mark = 12) then
374 -- Organization_id and Offset_Days is passed
375 EXECUTE IMMEDIATE sql_stmt USING l_organization_id, p_offset_days;
376
377 elsif (drive_mark = 13) then
378 -- Order_Number, Organization_id and Offset_Days is passed
379 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, l_organization_id, p_offset_days;
380
381 elsif (drive_mark = 14) then
382 -- Line_id, Organization_id and Offset_Days is passed
383 EXECUTE IMMEDIATE sql_stmt USING p_sales_order_line_id, l_organization_id, p_offset_days;
384
385 elsif (drive_mark = 15) then
386 -- Order_Number, Line_id, Organization_id and Offset_Days is passed
387 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id, l_organization_id, p_offset_days;
388
389 else
390 oe_debug_pub.add ('INCORRECT COMBINATION of parameters');
391
392 end if;
393
394 select line_id, org_id, ship_from_org_id, schedule_ship_date
395 BULK COLLECT INTO line_id_arr, org_id_arr, ship_from_org_id_arr, schedule_ship_date_arr
396 from bom_cto_order_lines_temp;
397 i := line_id_arr.first;
398 WHILE i is not null
399 /*
400 -- Open loop for selecting all the eligible lines.
401 -- Opening the cursor. This cursor selects the eligible oe lines based on the
402 FOR so_line IN oe_lines_cur (
403 p_sales_order,
404 p_sales_order_line_id,
405 l_organization_id,
406 p_offset_days) */
407 Loop
408 -- count of the records selected by the cursor
409 v_rec_count := v_rec_count + 1;
410
411 -- Log all the record being processed.
412 IF PG_DEBUG <> 0 THEN
413 oe_debug_pub.add('+-------- Processing for --------------------------------------------------+');
414 oe_debug_pub.add('Sales order : '||p_sales_order );
415 oe_debug_pub.add('Sales Order Line ID[Config] : '||to_char(line_id_arr(i)));
416 oe_debug_pub.add('Ship from Org : '||to_char(ship_from_org_id_arr(i)));
417 END IF;
418
419 l_stmt_num := 10;
420
421 -- bug 4384668: First check if the line is locked by another process. If locked then
422 -- continue processing for the next line
423 savepoint begin_line;
424 begin
425 select line_id into dummy
426 from oe_order_lines_all
427 where line_id = line_id_arr(i)
428 and source_type_code = 'EXTERNAL'
429 and open_flag = 'Y'
430 and booked_flag = 'Y'
431 and ordered_quantity > 0
432 and (cancelled_flag is null
433 or cancelled_flag = 'N')
434 and ship_from_org_id = ship_from_org_id_arr(i)
435 and schedule_ship_date = schedule_ship_date_arr(i)
436 FOR UPDATE NOWAIT;
437 exception
438 when record_locked then
439 IF PG_DEBUG <> 0 THEN
440 oe_debug_pub.add('This line is locked by another process ');
441 END IF;
442 goto EndOfLoop;
443
444 when no_data_found then
445 IF PG_DEBUG <> 0 THEN
446 oe_debug_pub.add('This line is no longer eligible for processing ');
447 END IF;
448 goto EndOfLoop;
449
450 end;
451 -- end bug 4384668
452
453 -- bugfix 3319313: update all lines with the program ID and request_id here.
454 update oe_order_lines_all
455 set program_id = l_program_id,
456 request_id = l_request_id
457 where line_id = line_id_arr(i);
458
459 l_stmt_num := 50;
460 -- Added by Renga Kannan for MOAC project
461 -- switch the org context if the context is different
462
463 l_current_org_id := nvl(MO_GLOBAL.get_current_org_id,-99);
464
465 If l_current_org_id <> org_id_arr(i) then
466
467 OE_ORDER_CONTEXT_GRP.set_created_by_context(
468 p_header_id => null,
469 p_line_id => line_id_arr(i),
470 x_orig_user_id => xUserId,
471 x_orig_resp_id => xRespId,
472 x_orig_resp_appl_id => xrespapplid,
473 x_return_status => x_return_status,
474 x_msg_count => x_msg_count,
475 x_msg_data => x_msg_data);
476 If x_return_status = FND_API.G_RET_STS_ERROR then
477 if PG_DEBUG <> 0 then
478 oe_debug_pub.add('Set_created_by_context API ended with expected error',1);
479 end if;
480 raise FND_API.G_EXC_ERROR;
481 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
482 if PG_DEBUG <> 0 then
483 oe_debug_pub.add('Set_created_by_context API ended with unexpected error',1);
484 end if;
485 raise FND_API.G_EXC_UNEXPECTED_ERROR;
486 end if; /*x_return_staus = FND_API.G_EXC_ERROR */
487 l_current_org_id := org_id_arr(i);
488 End if; /* l_current_org_id <> so_lines.org_id */
489
490 -- We just need to push the workflow further. Workflow will take care of rest.
491
492 -- We need to update the workflow status only if the
493 -- status is in CREATE_SUPPLY_ORDER_ELIGIBLE
494
495 CTO_WORKFLOW_API_PK.query_wf_activity_status( 'OEOL' ,
496 line_id_arr(i) ,
497 'CREATE_SUPPLY_ORDER_ELIGIBLE',
498 'CREATE_SUPPLY_ORDER_ELIGIBLE',
499 l_active_activity );
500
501 IF l_active_activity = 'NOTIFIED' THEN
502
503 l_stmt_num := 60;
504 l_res := cto_workflow_api_pk.complete_activity(
505 p_itemtype=>'OEOL',
506 p_itemkey =>line_id_arr(i),
507 p_activity_name=>'CREATE_SUPPLY_ORDER_ELIGIBLE',
508 p_result_code=>'COMPLETE');
509 IF NOT l_res THEN
510 oe_debug_pub.add('auto_create_dropship: ' || 'Error occurred in updating the workflow status - Stmt_num'||to_char(l_stmt_num),1);
511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
512 END IF;
513
514 ELSE
515
516 l_stmt_num := 70;
517
518 -- We need to update the workflow status only if the
519 -- status is in PURCHASE RELEASE ELIGIBLE
520
521 CTO_WORKFLOW_API_PK.query_wf_activity_status( 'OEOL' ,
522 line_id_arr(i) ,
526 IF l_active_activity = 'NOTIFIED' THEN
523 'PURCHASE RELEASE ELIGIBLE',
524 'PURCHASE RELEASE ELIGIBLE',
525 l_active_activity );
527
528 l_res := cto_workflow_api_pk.complete_activity(
529 p_itemtype=>'OEOL',
530 p_itemkey =>line_id_arr(i),
531 p_activity_name=>'PURCHASE RELEASE ELIGIBLE',
532 p_result_code=>'COMPLETE');
533
534 IF NOT l_res THEN
535 oe_debug_pub.add('auto_create_dropship: ' || 'Error occurred in updating the workflow status - Stmt_num'||to_char(l_stmt_num),1);
536 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
537 END IF;
538 END IF;
539 END IF;
540
541 --bug 4384668: Added the commit
542 commit; -- This shall release the lock. Since we are processing from array, snapshot errors will not occur.
543 <<EndOfLoop>>
544 i := line_id_arr.next(i); --4384668
545
546 END LOOP; -- Sales Order Lines.
547
548 IF PG_DEBUG <> 0 THEN
549 oe_debug_pub.add('+---------------------------------------------------------------------------+');
550 oe_debug_pub.add('The Batch ID for this run was : '||to_char(l_batch_id));
551 oe_debug_pub.add('+---------------------------------------------------------------------------+');
552 oe_debug_pub.add('Number of records Processed : '||to_char(v_rec_count));
553 END IF;
554
555 -- The following part of the code
556 -- is modified by Renga Kannan on 11/12/01
557 -- In the case of RETCODE = 1 it should complete the batch program with Warning
558
559 IF RETCODE = 1 THEN
560
561 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
562
563 ELSE
564
565 RETCODE := 0 ;
566 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
567
568 END IF;
569
570 COMMIT ;
571
572
573 EXCEPTION
574 WHEN FND_API.G_EXC_ERROR THEN
575 oe_debug_pub.add('auto_create_dropship: ' || ':exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
576 x_return_status := FND_API.G_RET_STS_ERROR;
577 RETCODE := 2;
578 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
579
580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
581 oe_debug_pub.add('auto_create_dropship: ' || ':exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
583 RETCODE := 2;
584 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
585
586 WHEN OTHERS THEN
587 oe_debug_pub.add('auto_create_dropship: ' || ':exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
589 RETCODE := 2;
590 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
591
592 END auto_create_dropship;
593
594 END cto_auto_dropship_pk;
[Home] [Help]