1 PACKAGE BODY cto_auto_dropship_pk AS
2 /*$Header: CTODROPB.pls 120.3 2006/02/13 11:51:49 rekannan noship $ */
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 ' or (oelc.item_type_code in (''STANDARD'',''OPTION'') '||
272
269 ' and ato_line_id = line_id)) '||
270 ') ';
271 end if;
273 /* Given an Organization */
274 if p_organization_id is not null then
275 drive_mark := drive_mark + 4;
276 sql_stmt := sql_stmt ||' and oel.ship_from_org_id = :l_organization_id ';
277 end if;
278
279 /* Given Offset days */
280 if p_offset_days is not null then
281 drive_mark := drive_mark + 8;
282 sql_stmt := sql_stmt ||' and (SYSDATE + nvl(:p_offset_days, 0)) >= '||
283 '(select CAL.CALENDAR_DATE '||
284 ' from bom_calendar_dates cal, '||
285 ' mtl_parameters mp '||
286 ' where mp.organization_id = oel.ship_from_org_id '||
287 ' and cal.calendar_code = mp.calendar_code '||
288 ' and cal.exception_set_id = mp.calendar_exception_set_id '||
289 ' and cal.seq_num = '||
290 ' (select cal2.prior_seq_num - '||
291 ' (ceil(nvl(msi.fixed_lead_time,0) + '||
292 ' nvl(msi.variable_lead_time,0) * '||
293 ' INV_CONVERT.inv_um_convert '||
294 ' (oel.inventory_item_id, '||
295 ' null, '||
296 ' oel.ordered_quantity , '||
297 ' oel.order_quantity_uom, '||
298 ' msi.primary_uom_code, '||
299 ' null, '||
300 ' null) '||
301 ' )) '||
302 ' from bom_calendar_dates cal2 '||
303 ' where cal2.calendar_code = mp.calendar_code '||
304 ' and cal2.exception_set_id = mp.calendar_exception_set_id '||
305 ' and cal2.calendar_date =trunc(oel.schedule_ship_date) '||
306 ' )) ';
307
308 end if;
309
310 IF PG_DEBUG <> 0 THEN
311 oe_debug_pub.add ('The dyanamic sql generated is');
312 oe_debug_pub.add ('SQL: ' || substr(sql_stmt,1, 1500));
313 oe_debug_pub.add (substr(sql_stmt,1501,3000));
314 oe_debug_pub.add ('The drive_mark is '||drive_mark);
315 END IF;
316
317 /*
318 Below, we execute the sql statement according to which parameters
319 we have selected. The drive_mark variable tells us which parameters
320 we are using, so we are sure to send the right ones to SQL.
321 */
322
323 if (drive_mark = 0) then
324 -- No (optional) parameter is passed
325 EXECUTE IMMEDIATE sql_stmt;
326
327 elsif (drive_mark = 1) then
328 -- Only Order_Number is passed
329 EXECUTE IMMEDIATE sql_stmt USING p_sales_order;
330
331 elsif (drive_mark = 2) then
332 -- Only Line_Id is passed
333 EXECUTE IMMEDIATE sql_stmt USING p_sales_order_line_id;
334
335 elsif (drive_mark = 3) then
336 -- Order Number and Line_Id is passed
337 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id;
338
339 elsif (drive_mark = 4) then
340 -- Only Orgn_Id is passed
341 EXECUTE IMMEDIATE sql_stmt USING l_organization_id;
342
343 elsif (drive_mark = 5) then
344 -- Order_Number and Orgn_Id is passed
345 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, l_organization_id;
346
347 elsif (drive_mark = 6) then
348 -- Line_id and Orgn_Id is passed
349 EXECUTE IMMEDIATE sql_stmt USING p_sales_order_line_id, l_organization_id;
350
351 elsif (drive_mark = 7) then
352 -- Order_number, Line_Id and Orgn_Id is passed
353 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id, l_organization_id;
354
355 elsif (drive_mark = 8) then
356 -- Offset_Days is passed
357 EXECUTE IMMEDIATE sql_stmt USING p_offset_days;
358
359 elsif (drive_mark = 9) then
360 -- Order_Number and Offset_Days is passed
361 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_offset_days;
362
363 elsif (drive_mark = 10) then
364 -- Line_id and Offset_Days is passed
365 EXECUTE IMMEDIATE sql_stmt USING p_sales_order_line_id, p_offset_days;
366
367 elsif (drive_mark = 11) then
368 -- Order_Number, Line_id and Offset_Days is passed
369 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id, p_offset_days;
370
371 elsif (drive_mark = 12) then
372 -- Organization_id and Offset_Days is passed
373 EXECUTE IMMEDIATE sql_stmt USING l_organization_id, p_offset_days;
374
375 elsif (drive_mark = 13) then
376 -- Order_Number, Organization_id and Offset_Days is passed
377 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, l_organization_id, p_offset_days;
378
379 elsif (drive_mark = 14) then
380 -- Line_id, Organization_id and Offset_Days is passed
381 EXECUTE IMMEDIATE sql_stmt USING p_sales_order_line_id, l_organization_id, p_offset_days;
382
386
383 elsif (drive_mark = 15) then
384 -- Order_Number, Line_id, Organization_id and Offset_Days is passed
385 EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id, l_organization_id, p_offset_days;
387 else
388 oe_debug_pub.add ('INCORRECT COMBINATION of parameters');
389
390 end if;
391
392 select line_id, org_id, ship_from_org_id, schedule_ship_date
393 BULK COLLECT INTO line_id_arr, org_id_arr, ship_from_org_id_arr, schedule_ship_date_arr
394 from bom_cto_order_lines_temp;
395 i := line_id_arr.first;
396 WHILE i is not null
397 /*
398 -- Open loop for selecting all the eligible lines.
399 -- Opening the cursor. This cursor selects the eligible oe lines based on the
400 FOR so_line IN oe_lines_cur (
401 p_sales_order,
402 p_sales_order_line_id,
403 l_organization_id,
404 p_offset_days) */
405 Loop
406 -- count of the records selected by the cursor
407 v_rec_count := v_rec_count + 1;
408
409 -- Log all the record being processed.
410 IF PG_DEBUG <> 0 THEN
411 oe_debug_pub.add('+-------- Processing for --------------------------------------------------+');
412 oe_debug_pub.add('Sales order : '||p_sales_order );
413 oe_debug_pub.add('Sales Order Line ID[Config] : '||to_char(line_id_arr(i)));
414 oe_debug_pub.add('Ship from Org : '||to_char(ship_from_org_id_arr(i)));
415 END IF;
416
417 l_stmt_num := 10;
418
419 -- bug 4384668: First check if the line is locked by another process. If locked then
420 -- continue processing for the next line
421 savepoint begin_line;
422 begin
423 select line_id into dummy
424 from oe_order_lines_all
425 where line_id = line_id_arr(i)
426 and source_type_code = 'EXTERNAL'
427 and open_flag = 'Y'
428 and booked_flag = 'Y'
429 and ordered_quantity > 0
430 and (cancelled_flag is null
431 or cancelled_flag = 'N')
432 and ship_from_org_id = ship_from_org_id_arr(i)
433 and schedule_ship_date = schedule_ship_date_arr(i)
434 FOR UPDATE NOWAIT;
435 exception
436 when record_locked then
437 IF PG_DEBUG <> 0 THEN
438 oe_debug_pub.add('This line is locked by another process ');
439 END IF;
440 goto EndOfLoop;
441
442 when no_data_found then
443 IF PG_DEBUG <> 0 THEN
444 oe_debug_pub.add('This line is no longer eligible for processing ');
445 END IF;
446 goto EndOfLoop;
447
448 end;
449 -- end bug 4384668
450
451 -- bugfix 3319313: update all lines with the program ID and request_id here.
452 update oe_order_lines_all
453 set program_id = l_program_id,
454 request_id = l_request_id
455 where line_id = line_id_arr(i);
456
457 l_stmt_num := 50;
458 -- Added by Renga Kannan for MOAC project
459 -- switch the org context if the context is different
460
461 l_current_org_id := nvl(MO_GLOBAL.get_current_org_id,-99);
462
463 If l_current_org_id <> org_id_arr(i) then
464
465 OE_ORDER_CONTEXT_GRP.set_created_by_context(
466 p_header_id => null,
467 p_line_id => line_id_arr(i),
468 x_orig_user_id => xUserId,
469 x_orig_resp_id => xRespId,
470 x_orig_resp_appl_id => xrespapplid,
471 x_return_status => x_return_status,
472 x_msg_count => x_msg_count,
473 x_msg_data => x_msg_data);
474 If x_return_status = FND_API.G_RET_STS_ERROR then
475 if PG_DEBUG <> 0 then
476 oe_debug_pub.add('Set_created_by_context API ended with expected error',1);
477 end if;
478 raise FND_API.G_EXC_ERROR;
479 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
480 if PG_DEBUG <> 0 then
481 oe_debug_pub.add('Set_created_by_context API ended with unexpected error',1);
482 end if;
483 raise FND_API.G_EXC_UNEXPECTED_ERROR;
484 end if; /*x_return_staus = FND_API.G_EXC_ERROR */
485 l_current_org_id := org_id_arr(i);
486 End if; /* l_current_org_id <> so_lines.org_id */
487
488 -- We just need to push the workflow further. Workflow will take care of rest.
489
490 -- We need to update the workflow status only if the
491 -- status is in CREATE_SUPPLY_ORDER_ELIGIBLE
492
493 CTO_WORKFLOW_API_PK.query_wf_activity_status( 'OEOL' ,
494 line_id_arr(i) ,
495 'CREATE_SUPPLY_ORDER_ELIGIBLE',
496 'CREATE_SUPPLY_ORDER_ELIGIBLE',
497 l_active_activity );
498
499 IF l_active_activity = 'NOTIFIED' THEN
500
501 l_stmt_num := 60;
502 l_res := cto_workflow_api_pk.complete_activity(
503 p_itemtype=>'OEOL',
504 p_itemkey =>line_id_arr(i),
505 p_activity_name=>'CREATE_SUPPLY_ORDER_ELIGIBLE',
506 p_result_code=>'COMPLETE');
507 IF NOT l_res THEN
508 oe_debug_pub.add('auto_create_dropship: ' || 'Error occurred in updating the workflow status - Stmt_num'||to_char(l_stmt_num),1);
509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
510 END IF;
511
512 ELSE
513
514 l_stmt_num := 70;
515
516 -- We need to update the workflow status only if the
517 -- status is in PURCHASE RELEASE ELIGIBLE
518
519 CTO_WORKFLOW_API_PK.query_wf_activity_status( 'OEOL' ,
520 line_id_arr(i) ,
521 'PURCHASE RELEASE ELIGIBLE',
522 'PURCHASE RELEASE ELIGIBLE',
523 l_active_activity );
524 IF l_active_activity = 'NOTIFIED' THEN
525
526 l_res := cto_workflow_api_pk.complete_activity(
527 p_itemtype=>'OEOL',
528 p_itemkey =>line_id_arr(i),
529 p_activity_name=>'PURCHASE RELEASE ELIGIBLE',
530 p_result_code=>'COMPLETE');
531
532 IF NOT l_res THEN
533 oe_debug_pub.add('auto_create_dropship: ' || 'Error occurred in updating the workflow status - Stmt_num'||to_char(l_stmt_num),1);
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535 END IF;
536 END IF;
537 END IF;
538
539 --bug 4384668: Added the commit
540 commit; -- This shall release the lock. Since we are processing from array, snapshot errors will not occur.
541 <<EndOfLoop>>
542 i := line_id_arr.next(i); --4384668
543
544 END LOOP; -- Sales Order Lines.
545
546 IF PG_DEBUG <> 0 THEN
547 oe_debug_pub.add('+---------------------------------------------------------------------------+');
548 oe_debug_pub.add('The Batch ID for this run was : '||to_char(l_batch_id));
549 oe_debug_pub.add('+---------------------------------------------------------------------------+');
550 oe_debug_pub.add('Number of records Processed : '||to_char(v_rec_count));
551 END IF;
552
553 -- The following part of the code
554 -- is modified by Renga Kannan on 11/12/01
555 -- In the case of RETCODE = 1 it should complete the batch program with Warning
556
557 IF RETCODE = 1 THEN
558
559 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
560
561 ELSE
562
563 RETCODE := 0 ;
564 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
565
566 END IF;
567
568 COMMIT ;
569
570
571 EXCEPTION
572 WHEN FND_API.G_EXC_ERROR THEN
573 oe_debug_pub.add('auto_create_dropship: ' || ':exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
574 x_return_status := FND_API.G_RET_STS_ERROR;
575 RETCODE := 2;
576 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
577
578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
579 oe_debug_pub.add('auto_create_dropship: ' || ':exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
581 RETCODE := 2;
582 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
583
584 WHEN OTHERS THEN
585 oe_debug_pub.add('auto_create_dropship: ' || ':exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
587 RETCODE := 2;
588 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
589
592 END cto_auto_dropship_pk;
590 END auto_create_dropship;
591
[Home] [Help]