1 package body CTO_WIP_WRAPPER as
2 /* $Header: CTOWIPWB.pls 120.11.12000000.2 2007/07/17 11:19:25 abhissri ship $ */
3 /*============================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8 | |
9 | FILE NAME : CTOWIPWB.pls |
10 | |
11 | DESCRIPTION: |
12 | This file creates the utilities that are required to create |
13 | work orders for ATO configurations. |
14 | |
15 | insert_wip_interface - inserts a record into |
16 | WIP_JOB_SCHEDULE_INTERFACE for |
17 | WIP_MASS_LOAD to create work orders |
18 | |
19 | To Do: Handle Errors. Need to discuss with Usha and Girish what |
20 | error information to include in Notification. |
21 | |
22 | HISTORY : |
23 | August 14, 99 Angela Makalintal Initial version |
24 | February 26, 01 Shashi Bhaskaran bugfix 1642355 |
25 | May 7, 01 Sajani Sheth Support for partial FAS |
26 | June 16, 01 Shashi Bhaskaran bugfix 1835357 |
27 | Replaced fnd_file calls with oe_debug_pub |
28 | August 16,2001 Kiran Konada, fix for bug#1874380 |
29 | to support ATO item under a PTO. |
30 | item_type_code for an ato item under PTO |
31 | is 'OPTION' and top_model_line_id will NOT be|
32 | null, UNLIKE an ato item order, where |
33 | item_type_code = 'Standard' and |
34 | top_model_lined_id is null |
35 | This fix has actually been provided in |
36 | branched code 115.15.1155.4 |
37 | |
38 | |
39 | 08/29/2001 Renga Kannan |
40 | Modified the code for Porcuring config |
41 | This batch program should not pick up |
42 | the Buy config/ATO item orders |
43 | This check is added to get_order_lines |
44 | procedure. |
45 | |
46 | Sep 26, 01 Shashi Bhaskaran Fixed bug 2017099 |
47 | Check with ordered_quantity(OQ) instead of OQ-CQ |
48 | where CQ=cancelled_quantity. When a line is |
49 | is canceled, OQ gets reflected. |
50 | |
51 | Oct 24, 01 Shashi Bhaskaran Fixed bug 2074290 |
52 | Convert the ordered_quantity into Primary UOM for|
53 | comparing with get_reserved_qty. |
54 | |
55 | Feb 18, 02 Shashi Bhaskaran Fixed bug 2227841 |
56 | Performance: Removed call to GET_NOTINV_QTY and |
57 | GET_RESERVED_QTY from get_order_lines main cursor|
58 | and added soon after fetching the cursor. |
59 | |
60 | Feb 27, 02 Shashi Bhaskaran Fixed bug 2243672 |
61 | Set the org context using OM's API |
62 | |
63 | Jun 05, 02 Shashi Bhaskaran Fixed bug 2388802 |
64 | Because of the earlier fix (2227841), the cursor |
65 | picked up the non-eligbile rows and locked them. |
66 | Removed the for update clause from the cursor and|
67 | locked it for the real eligible row. |
68 | |
69 | Oct 24, 02 Kundan Sarkar Fixed bug 2628896 |
70 | To propagate fix 2420381 from branch to main |
71 | |
72 | Oct 25, 02 Kundan Sarkar Bugfix 2644849 (2620282 in br)|
73 | Sales order not seen in LOV while doing WIP |
74 | completion as revision info is not passed |
75 | while reserving the sales order against work |
76 | order. |
77 | |
78 | Dec 06, 02 Kundan Sarkar Bugfix 2698837 (2681321 in br)|
79 | Not creating work order in shipping org if the |
80 | item is sourced from a different org and sourcing|
81 | rule is "TRANSFER FROM". |
82 | |
83 | May 07, 03 Kundan Sarkar Bugfix 2930170 |
84 | May 09, 03 (2868148 and 2946071 in br) |
85 | 2868148: |
86 | Considering supply from Flow Schedule before |
87 | creating supply through AFAS to prevent multiple|
88 | work order creation. |
89 | 2946071: |
90 | Need to handle null condition when flow_supply |
91 | returns NO_DATA_FOUND |
92
93
94 Sep 23, 2003 Renga Kannan |
95 | Changed the following two table acecss to |
96 | view. This change is recommended by shipping |
97 | team to avoid getting inbound/dropship lines.
98 V
99 WSH_DELIVERY_DETAILS to WSH_DELIVERY_DETAILS_OB_
100 GRP_V
101 This changes brings a wsh dependency to our code
102 the wsh pre-req for this change is 3125046
103
104 |24-SEP-2003 : Kiran Konada
105 | Chnages for patchset-J
106 | with mutiple sources enhancement ,
107 | expected error from query sourcing org has been removed
108 | source_type =66 refers to mutiple sourcing
109 |
110 | statements after call to query org has been modified to look at
111 | source type =66 instead of expected error status
112 |
113 |
114 |19-Nov-2003 : Kiran Konada
115 | bugfix 2885568
116 | There was a full table scan on wip_discrete_jobs
117 | unique index present on wip_enity_id and organization_id
118 | hece, joined oe_order_lines_all and got the ship_from _org_id
119 |
120 | original query
121 | select dj.wip_entity_id, we.wip_entity_name
122 | into l_wip_entity_id, l_job_name
123 | from wip_discrete_jobs dj, wip_entities we|
124 | where dj.wip_entity_id = we.wip_entity_id
125 | and dj.source_line_id = l_line_id
126 | and dj.source_code = 'WICDOL'
127 |
128 | Changed query
129 |
130 | added follwoing where clause
131 |
132 | and oel.line_id = l_line_id
133 | and dj.primary_item_id = oel.inventory_item_id
134 | and oel.ship_from_org_id = dj.organization_id ;
135 |
136 |
137 |25-Nov-2003 Bugfix 3202934 :
138 | Reservation getting created even if job creation
139 | fails
140 |
141 |10-JUN-2003 KIran Konada
142 | -- bugfix 3618441 (front port bug#3631702) :
143 | This is also dependent on WIP valuset change
144 | front port fix #3682313
145 |
146 |
147 |13-AUG-2004 Sushant Sawant
148 | -- bugfix 3828248
149 | front ported bug 3718374 to improve performance for cursor c_work_order_eligible.
150 |
151 |
152 |
153 |
154 |20-AUG-2004 Sushant Sawant
155 | /* Fix for bug 3777065
156 | ** Original cursor c_work_order_eligible had performance issues due to additional conditions for workflow status.
157 | ** repetition of conditions related to offset days has been removed.
158 | ** This cursor has been split. The new approach is to insert the data into a temp table using the same sql statement
159 | ** without the workflow conditions and then filter the data with the additional conditions for work flow status.
160 | ** The cursor c_work_order_eligible will now be using bom_cto_order_lines_gt, wf_item_activity_statuses
161 | ** and wf_process_activities tables.
162 | */
163 /*=============================================================================*/
164
165 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
166
167
168
169 -- bugfix 3136206
170 PROCEDURE unlock_line_id(p_line_id IN NUMBER)
171 IS
172
173 BEGIN
174
175
176 UPDATE oe_order_lines_all oel
177 SET oel.program_id = null
178 WHERE oel.line_id = p_line_id
179 AND oel.program_id = -99;
180
181 IF (sql%rowcount > 0) THEN
182 IF (PG_DEBUG <> 0) THEN
183 oe_debug_pub.add ('unlock_line_id: ' || 'unlocked line_id '||p_line_id);
184 END IF;
185 END IF;
186
187 EXCEPTION
188
189 when others then
190 IF PG_DEBUG <> 0 THEN
191 OE_DEBUG_PUB.add ('unlock_line_id: error: ' || sqlerrm);
192
193 END IF;
194
195 END UnLock_Line_Id;
196
197 -- end bugfix 3136206
198
199 /*****************************************************************************
200 Procedure: get_order_lines
201
202 Parameters:
203 p_org_id - Organization ID from user input
204 p_offset_days - Number of days added to the current date
205 and compared to the release date
206 p_load_type - Load Type values are:
207 1. 1 - Configuration Items
208 2. 2 - ATO Items
209 3. 3 - Configuration Items and ATO Items
210 p_class_code - Accounting Class code for job creation (user
211 parameter)
212 p_status_type - Unreleased, Released (user input)
213 p_order_number - Specific order number to process (user input)
214 p_conc_request_id - concurrent request ID
215 p_conc_program_id - concurrent program ID
216 p_conc_login_id - concurrent login ID
217 p_user_id - User ID
218 p_appl_conc_program_id - Application Concurrent Program ID
219 x_orders_loaded - Number of rows inserted into interface table
220 x_wip_seq i - Group ID in interface table
221 x_message_name - Error message name
222 x_message_text - Error message text
223
224 Description: This function inserts a record into the
225 WIP_JOB_SCHEDULE_INTERFACE table for the creation of
226 work orders through AutoCreate FAS in OM.
227
228 *****************************************************************************/
229
230
231
232
233 FUNCTION get_order_lines(p_org_id IN NUMBER,
234 p_offset_days IN NUMBER,
235 p_load_type IN NUMBER,
236 p_class_code IN varchar2,
237 p_status_type IN NUMBER,
238 p_order_number IN NUMBER,
239 p_line_id IN NUMBER,
240 p_conc_request_id IN NUMBER,
241 p_conc_program_id IN NUMBER,
242 p_conc_login_id IN NUMBER,
243 p_user_id IN NUMBER,
244 p_appl_conc_program_id IN NUMBER,
245 x_orders_loaded OUT NoCopy NUMBER,
246 x_wip_seq OUT NoCopy NUMBER,
247 x_message_name OUT NoCopy VARCHAR2,
248 x_message_text OUT NoCopy VARCHAR2
249 )
250 return integer
251
252 IS
253
254 l_x_hold_result_out VARCHAR2(1);
255 l_x_hold_return_status VARCHAR(1);
256 l_x_error_msg_count NUMBER;
257 l_x_error_msg VARCHAR2(240);
258 l_x_result_out VARCHAR2(1);
259 l_x_return_status VARCHAR2(1);
260 l_x_error_message VARCHAR2(1000);
261 l_x_message_name VARCHAR2(30);
262 l_x_msg_count NUMBER;
263 l_x_msg_data VARCHAR2(2000);
264 l_rows_selected NUMBER := 0;
265 l_rows_inserted NUMBER := 0;
266 l_rows_on_hold NUMBER := 0;
267 l_rows_dep_plan NUMBER := 0;
268 l_rows_errored NUMBER := 0;
269 l_line_id NUMBER := p_line_id;
270 l_org_id NUMBER := p_org_id;
271 l_order_number NUMBER := p_order_number;
272 -- bugfix 4056151: commented out lSourceCode since its no longer used.
273 -- lSourceCode VARCHAR2(30);
274 l_dep_plan_flag VARCHAR2(1);
275 l_planned VARCHAR2(10);
276 l_stmt_num NUMBER;
277
278 Not_Planned EXCEPTION;
279
280 -- Begin Bugfix 4056151: Added REF Cursor and new variables
281
282 TYPE WorkOrderCurTyp is REF CURSOR ;
283 WorkOrder WorkOrderCurTyp;
284
285 TYPE WorkOrderRecTyp is RECORD (
286 line_id number,
287 ship_from_org_id number,
288 header_id number,
289 org_id number,
290 ato_line_id number,
291 inventory_item_id number);
292 WorkOrder_Rec WorkOrderRecTyp;
293
294 sql_stmt VARCHAR2(5000);
295 drive_mark NUMBER := 0;
296
297 -- End Bugfix 4056151: Added REF Cursor.
298
299 -- The following variable declaration is added by Renga Kannan for
300 -- Procuring Configuration Project
301 -- Added on 08/29/01
302
303 l_sourcing_rule_exists varchar2(1);
304 l_inventory_item_id mtl_system_items.inventory_item_id%type;
305 l_ship_from_org_id mtl_system_items.organization_id%type;
306 l_source_type Number;
307 l_sourcing_org Number;
308 l_transit_lead_time Number;
309 l_exp_error_code Number;
310
311 -- End of Addition
312
313 -- bugfix 2053360 : declare a new exception
314 record_locked EXCEPTION;
315 pragma exception_init (record_locked, -54);
316
317 -- bugfix 2243672 : declare new variables
318 lOperUnit Number := -1; -- bugfix 3014000: default value changed to -1
319 xUserId Number;
320 xRespId Number;
321 xRespApplId Number;
322
323 -- bugfix 3014000
324 l_client_org_id Number;
325 l_offset_days NUMBER; -- bugfix 4064726
326
327 --as part of OPM project
328 l_can_create_supply varchar2(1);
329 l_message varchar2(100);
330 BEGIN
331 /****************************************************************
332 Select Eligible Records based on parameters and workflow status.
333 ****************************************************************/
334
335 IF PG_DEBUG <> 0 THEN
336 oe_debug_pub.add('get_order_lines: ' || 'Begin Get Order Lines.', 1);
337
338 oe_debug_pub.add('get_order_lines: ' || 'Organization ID: '||to_char(p_org_id), 1);
339
340 oe_debug_pub.add('get_order_lines: ' || 'Offset: '||to_char(p_offset_days), 1);
341
342 oe_debug_pub.add('get_order_lines: ' || 'Load Type: '||to_char(p_load_type), 1);
343
344 oe_debug_pub.add('get_order_lines: ' || 'Class Code: '||p_class_code, 1);
345
346 oe_debug_pub.add('get_order_lines: ' || 'Status Type: '||to_char(p_status_type), 1);
347
348 oe_debug_pub.add('get_order_lines: ' || 'Order Number: '||to_char(p_order_number), 1);
349
350 oe_debug_pub.add('get_order_lines: ' || 'Line ID: ' ||to_char(p_line_id), 1);
351 END IF;
352
353 x_wip_seq := -1;
354 x_orders_loaded := 0;
355 /*------------------------------------------------+
356 If IN parameter equal -1, then parameter value was not
357 entered. We NULL out the equivalent local variable
358 so that it will not be used in the SQL.
359 +-------------------------------------------------*/
360 if (p_line_id = -1) then
361 l_line_id := NULL;
362 end if;
363
364 if (p_org_id = -1) then
365 l_org_id := NULL;
366 end if;
367
368 if (p_order_number = -1) then
369 l_order_number := NULL;
370 end if;
371
372 --begin bugfix 4064726
373 if p_offset_days = -10000 then
374 l_offset_days := null ;
375
376 IF PG_DEBUG <> 0 THEN
377 oe_debug_pub.add('get_order_lines: ' || 'initialized Offset to null : '||to_char(l_offset_days), 1);
378 END IF;
379
380 else
381 l_offset_days := p_offset_days ;
382 end if;
383 --end bugfix 4064726
384
385 -- Begin Bugfix 4056151: Added Dynamic Clause
386
387 if (l_order_number IS NOT NULL) or (l_line_id IS NOT NULL) then
388 sql_stmt := 'select /*+ ordered index (WAS WF_ITEM_ACTIVITY_STATUSES_PK) */ '||
389 ' oel.line_id line_id, '||
390 ' oel.ship_from_org_id ship_from_org_id, '||
391 ' oel.header_id header_id, '||
392 ' nvl(oel.org_id,-1) org_id, ' ||
393 ' oel.ato_line_id ato_line_id, '||
394 ' oel.inventory_item_id inventory_item_id '||
395 'from '||
396 ' oe_order_lines_all OEL, '||
397 ' wf_item_activity_statuses WAS, '||
398 ' wf_process_activities WPA, '||
399 ' mtl_system_items MSI ';
400 else
401 -- Use a different order sequence of tables
402
403
404 -- (FP 5510153) bug 5409829: Added the hint for index WF_ITEM_ACTIVITY_STATUSES_N1. This will improve performance
405 -- only if WF_ITEM_ACTIVITY_STATUSES_N1 has the column process_activity. So WF patch 4730872 is a prereq.
406 -- for FP we do not need that WF patch as a pre-req as System Test env's already show that required column PROCESS_ACTIVITY is part
407 -- of index WF_ITEM_ACTIVITY_STATUSES_N1. Also, latest version of file afwfnp.odf in fnd_top has this change
408
409 sql_stmt := 'select /*+ ordered index (WPA WF_PROCESS_ACTIVITIES_N1) index (WAS WF_ITEM_ACTIVITY_STATUSES_N1) */ '||
410 ' oel.line_id line_id, '||
411 ' oel.ship_from_org_id ship_from_org_id, '||
412 ' oel.header_id header_id, '||
413 ' nvl(oel.org_id,-1) org_id, ' ||
414 ' oel.ato_line_id ato_line_id, '||
415 ' oel.inventory_item_id inventory_item_id '||
416 'from '||
417 ' wf_process_activities WPA, '||
418 ' wf_item_activity_statuses WAS, '||
419 ' oe_order_lines_all OEL, '||
420 ' mtl_system_items MSI ';
421 end if;
422
423
424 -- rkaza. 07/29/2005. bug 4438574. Item type code will be standard for
425 -- independent ato item and option for ato under pto.
426
427 sql_stmt := sql_stmt ||
428 'where oel.inventory_item_id = msi.inventory_item_id '||
429 'and oel.ship_from_org_id = msi.organization_id '||
430 'and msi.bom_item_type = 4 '|| -- STANDARD
431 'and oel.open_flag = ''Y'' '||
432 'and oel.ato_line_id is not null '||
433 'and (oel.item_type_code = ''CONFIG'' ' ||
434 'or (oel.ato_line_id = oel.line_id ' ||
435 'and oel.item_type_code = ''OPTION'') ' ||
436 'or (oel.ato_line_id = oel.line_id ' ||
437 'and oel.item_type_code = ''STANDARD'' ' ||
438 'and oel.top_model_line_id is null)) ' ||
439 'and nvl(oel.cancelled_flag, ''N'') = ''N'' '||
440 'and oel.booked_flag = ''Y'' '||
441 'and oel.schedule_status_code = ''SCHEDULED'' '||
442 'and oel.ordered_quantity > 0 '||
443 'and msi.replenish_to_order_flag = ''Y'' '||
444 'and msi.build_in_wip_flag = ''Y'' '||
445 'and msi.pick_components_flag = ''N'' '||
446 'and was.item_type = ''OEOL'' '||
447 'and was.activity_status = ''NOTIFIED'' '||
448 'and was.item_type = wpa.activity_item_type '||
449 'and was.process_activity = wpa.instance_id '||
450 'and wpa.activity_name in '||
451 '(''EXECUTECONCPROGAFAS'', ''CREATE_SUPPLY_ORDER_ELIGIBLE'', ''SHIP_LINE'') '||
452 'and ((wpa.activity_name = ''EXECUTECONCPROGAFAS'' and oel.program_id = -99) ' ||
453 -- spawned thru workflow
454 ' OR '||
455 ' (wpa.activity_name<>''EXECUTECONCPROGAFAS'' and nvl(oel.program_id,0)<>-99)) '||
456 -- spawned thru SRS
457 'and not exists (select ''1'' '||
458 'from bom_operational_routings bor ' ||
459 'where bor.assembly_item_id = oel.inventory_item_id '||
460 'and bor.organization_id = oel.ship_from_org_id ' ||
461 'and bor.alternate_routing_designator is NULL '||
462 'and nvl(bor.cfm_routing_flag, 2) = 1) ' ;
463
464 /* We want to do an explicit to_char() when order_number or line_id
465 * parameter is passed because we are driving from OEL->WAS. If we are driving
466 * from WF tables into OE then to_char() should not be used.
467 *
468 * Here, the problem was because of the implicit type conversion that was happening on the WAS side.
469 * That was preventing the item_key column of the WAS PK index from being used during index access.
470 * It was effectively using the index only on the item_type column and that is the reason why it was slow.
471 */
472
473 if (l_order_number IS NOT NULL) or (l_line_id IS NOT NULL) then
474 sql_stmt := sql_stmt ||
475 'and was.item_key = to_char(oel.line_id) ';
476 else
477 sql_stmt := sql_stmt ||
478 'and was.item_key = oel.line_id ';
479 end if;
480
481
482 if (l_order_number IS NOT NULL) then
483 sql_stmt := sql_stmt ||
484 'and oel.header_id in (select oeh.header_id '||
485 'from oe_order_headers_all oeh ' ||
486 'where oeh.order_number = :l_order_number) ';
487 drive_mark := drive_mark + 1;
488 end if;
489
490 if (l_line_id is NOT NULL) then
491 sql_stmt := sql_stmt ||
492 'and oel.line_id in '||
493 '(select oelc.line_id '||
494 ' from oe_order_lines_all oelc '||
495 ' where (oelc.ato_line_id = :l_line_id '||--5108885
496 'and oelc.item_type_code = ''CONFIG'') '||
497 'or (oelc.line_id = :l_line_id '||
498 'and oelc.item_type_code = ''STANDARD'' ' ||
499 'and oelc.top_model_line_id is null) '||
500 'or (oelc.line_id = :l_line_id '|| -- ATO item within PTO
501 'and oelc.ato_line_id = oelc.line_id '||
502 'and oelc.item_type_code = ''OPTION'')) '; -- fix for bug#1874380
503 drive_mark := drive_mark + 2;
504 end if;
505
506 if (l_org_id is NOT NULL) then
507 sql_stmt := sql_stmt ||
508 'and oel.ship_from_org_id = :l_org_id ';
509 drive_mark := drive_mark + 4;
510 end if;
511
512 if (l_line_id is NULL AND p_load_type IS NOT NULL) then
513 if (p_load_type = 1) then
514 --
515 -- Given Load Type = 1: Config Items
516 --
517 sql_stmt := sql_stmt ||
518 'and oel.item_type_code = ''CONFIG'' '||
519 'and msi.base_item_id is not null ';
520
521 elsif (p_load_type = 2) then
522 --
523 -- Given Load Type = 2: ATO items
524 --
525 sql_stmt := sql_stmt ||
526 'and oel.item_type_code in (''STANDARD'', ''OPTION'') '||
527 'and oel.ato_line_id = oel.line_id ';
528
529 elsif (p_load_type = 3) then
530 --
531 -- Given Load Type = 3: Both Config and ATO items
532 --
533 sql_stmt := sql_stmt ||
534 'and (oel.item_type_code = ''CONFIG'' '||
535 'or (oel.item_type_code in (''STANDARD'', ''OPTION'') '||
536 'and oel.ato_line_id = oel.line_id)) ';
537 end if;
538
539 end if;
540
541 -- bugfix 4064726 : Include offset days condition only when offset days parameter is passed.
542
543 if (l_line_id is NULL AND l_offset_days IS NOT NULL) then
544 drive_mark := drive_mark + 8;
545 sql_stmt := sql_stmt ||
546 'and SYSDATE >= '||
547 '(select CAL.CALENDAR_DATE '||
548 'from bom_calendar_dates cal, '||
549 'mtl_parameters mp '||
550 'where mp.organization_id = oel.ship_from_org_id '||
551 'and cal.calendar_code = mp.calendar_code '||
552 'and cal.exception_set_id = mp.calendar_exception_set_id '||
553 'and cal.seq_num = '||
554 '(select cal2.prior_seq_num '||
555 '- nvl(:p_offset_days, 0) '||
556 '- (ceil(nvl(msi.fixed_lead_time,0) '||
557 '+ nvl(msi.variable_lead_time,0) '||
558 '* (INV_CONVERT.inv_um_convert( '||
559 'oel.inventory_item_id, '||
560 '5, '||
561 -- bugfix 2204376: pass precision of 5
562 'oel.ordered_quantity, '||
563 'oel.order_quantity_uom, '||
564 'msi.primary_uom_code, '||
565 'null, '||
566 'null ) '||
567 '- CTO_WIP_WRAPPER.GET_RESERVED_QTY(oel.line_id)) )) '||
568 --bugfix 3034619: added parenthesis
569 --bugfix 2074290: convert the OQ and then
570 -- subtract from get_reserved_qty
571 'from bom_calendar_dates cal2 '||
572 'where cal2.calendar_code = mp.calendar_code '||
573 'and cal2.exception_set_id = mp.calendar_exception_set_id '||
574 'and cal2.calendar_date = trunc(oel.schedule_ship_date))) ';
575 end if; /* load_type check ends */
576
577 sql_stmt := sql_stmt ||
578 'order by oel.org_id, oel.line_id';
579
580 IF PG_DEBUG <> 0 THEN
581 oe_debug_pub.add ('SQL: ' || substr(sql_stmt,1, 1500));
582 oe_debug_pub.add (substr(sql_stmt,1501,3000));
583 oe_debug_pub.add ('drive_mark = '||drive_mark );
584 END IF;
585
586 /*
587 Below, we execute the sql statement according to which parameters
588 we have selected. The drive_mark variable tells us which parameters
589 we are using, so we are sure to send the right ones to SQL.
590 */
591
592 if (drive_mark = 0) then
593 -- No (optional) parameter is passed
594 Open WorkOrder FOR sql_stmt;
595
596 elsif (drive_mark = 1) then
597 -- Only Order_Number is passed
598 Open WorkOrder FOR sql_stmt USING l_order_number;
599
600 elsif (drive_mark = 2) then
601 -- Only Line_Id is passed
602 Open WorkOrder FOR sql_stmt USING l_line_id, l_line_id, l_line_id;
603
604 elsif (drive_mark = 3) then
605 -- Order Number and Line_Id is passed
606 Open WorkOrder FOR sql_stmt USING l_order_number, l_line_id, l_line_id, l_line_id;
607
608 elsif (drive_mark = 4) then
609 -- Only Orgn_Id is passed
610 Open WorkOrder FOR sql_stmt USING l_org_id;
611
612 elsif (drive_mark = 5) then
613 -- Order_Number and Orgn_Id is passed
614 Open WorkOrder FOR sql_stmt USING l_order_number, l_org_id;
615
616 elsif (drive_mark = 6) then
617 -- Line_id and Orgn_Id is passed
618 Open WorkOrder FOR sql_stmt USING l_line_id, l_line_id, l_line_id, l_org_id;
619
620 elsif (drive_mark = 7) then
621 -- Order_number, Line_Id and Orgn_Id is passed
622 Open WorkOrder FOR sql_stmt USING l_order_number, l_line_id, l_line_id, l_line_id, l_org_id;
623
624 elsif (drive_mark = 8) then
625 -- Offset_Days is passed
626 Open WorkOrder FOR sql_stmt USING p_offset_days;
627
628 elsif (drive_mark = 9) then
629 -- Order_Number and Offset_Days is passed
630 Open WorkOrder FOR sql_stmt USING l_order_number, p_offset_days;
631
632 elsif (drive_mark = 10) then
633 -- Line_id and Offset_Days is passed
634 Open WorkOrder FOR sql_stmt USING l_line_id, p_offset_days;
635
636 elsif (drive_mark = 11) then
637 -- Order_Number, Line_id and Offset_Days is passed
638 Open WorkOrder FOR sql_stmt USING l_order_number, l_line_id, p_offset_days;
639
640 elsif (drive_mark = 12) then
641 -- Organization_id and Offset_Days is passed
642 Open WorkOrder FOR sql_stmt USING l_org_id, p_offset_days;
643
644 elsif (drive_mark = 13) then
645 -- Order_Number, Organization_id and Offset_Days is passed
646 Open WorkOrder FOR sql_stmt USING l_order_number, l_org_id, p_offset_days;
647
648 elsif (drive_mark = 14) then
649 -- Line_id, Organization_id and Offset_Days is passed
650 Open WorkOrder FOR sql_stmt USING l_line_id, l_line_id, l_line_id, l_org_id, p_offset_days;
651
652 elsif (drive_mark = 15) then
653 -- Order_Number, Line_id, Organization_id and Offset_Days is passed
654 Open WorkOrder FOR sql_stmt USING l_order_number, l_line_id, l_line_id, l_line_id, l_org_id, p_offset_days;
655
656 else
657 IF PG_DEBUG <> 0 THEN
658 oe_debug_pub.add ('INCORRECT COMBINATION of parameters');
659 END IF;
660
661 end if;
662
663 -- End Bugfix 4056151: End of Dynamic SQL creation
664
665 IF PG_DEBUG <> 0 THEN
666 oe_debug_pub.add ('Opened. System Time : '||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
667 END IF;
668
669
670 -- begin bugfix 3014000
671 -- Added for MOAC project.
672 -- Deriving the current org using MO GLOBAL API
673 lOperUnit := nvl(MO_GLOBAL.get_current_org_id,-99);
674
675 IF PG_DEBUG <> 0 THEN
676 oe_debug_pub.add('get_order_lines: '|| 'MO:operating Unit :' || lOperUnit, 2);
677 END IF;
678 -- end bugfix 3014000
679
680 --
681 -- Bugfix 4056151: Replaced implicit cursor with explicit cursor.
682 -- Replaced "WorkOrder_Rec" with "WorkOrder_Rec" through out the code.
683 --
684
685
686 /* After discussions with performance team, it was decided to use bugfix 4056151
687 instead of bugfix 3777065. To view the fix for 3777065, please checkout
688 the previous version.
689 */
690
691
692 l_stmt_num := 1;
693 LOOP
694 FETCH WorkOrder INTO WorkOrder_Rec;
695 EXIT WHEN WorkOrder%NOTFOUND;
696 IF PG_DEBUG <> 0 THEN
697 oe_debug_pub.add ('Fetched. System Time : '||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
698 END IF;
699 -- begin bugfix 3014000: Moved the debug stmt here
700
701 IF PG_DEBUG <> 0 THEN
702 oe_debug_pub.add('----------------------------------------', 5);
703 oe_debug_pub.add('Processing line_id: '||WorkOrder_Rec.line_id, 5);
704 oe_debug_pub.add('----------------------------------------', 5);
705 END IF;
706
707
708 --
709 -- Begin Bugfix 2243672
710 --
711 if (lOperUnit <> WorkOrder_Rec.org_id ) then
712 --
713 -- Bugfix 2310559: We will call regular fnd_client_info.set_org_context instead of
714 -- OM's API Set_Created_By_Context because the conc programs submitted via WF, call
715 -- the FND_WF_STANDARD.callback function at the end. This program tries to
716 -- retrieve the value of the profile - CONC_REQUEST_ID but cannot find the
717 -- profile value as it was cleared during apps_initialize call. Thus resulting in
718 -- failures later.
719 --
720
721 --
722 -- We will call this API only when there is a change in the org.
723 --
724
725 -- Bugfix 3014000: When called from WF, there's no need to set the context since
726 -- its already set by workflow.
727 -- When called from SRS, set the context only when ORG_ID is different
728 -- from OEL.org_id.
729 -- Caveat: Single org customers should NOT set the MO:Operating unit for the resp
730 -- which is used to run autocreate FAS. If so, OM will defer the activity.
731
732 -- begin bugfix 3014000
733 -- commenting out... : FND_CLIENT_INFO.Set_Org_Context ( WorkOrder_Rec.org_id );
734
735 IF PG_DEBUG <> 0 THEN
736 oe_debug_pub.add('get_order_lines: '|| 'Setting the Org Context to '||WorkOrder_Rec.org_id ||
737 ' by calling OE_Order_Context_GRP.Set_Created_By_Context.', 5);
738 END IF;
739
740 OE_Order_Context_GRP.Set_Created_By_Context (
741 p_header_id => NULL
742 ,p_line_id => WorkOrder_Rec.line_id
743 ,x_orig_user_id => xUserId
744 ,x_orig_resp_id => xRespId
745 ,x_orig_resp_appl_id => xRespApplId
746 ,x_return_status => l_x_return_status
747 ,x_msg_count => l_x_msg_count
748 ,x_msg_data => l_x_msg_data );
749
750 if l_x_return_status = FND_API.G_RET_STS_ERROR THEN
751 IF PG_DEBUG <> 0 THEN
752 oe_debug_pub.add('get_order_lines: '|| 'Expected Error in Set_Created_By_Context.');
753 END IF;
754 raise FND_API.G_EXC_ERROR;
755
756 elsif l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
757 IF PG_DEBUG <> 0 THEN
758 oe_debug_pub.add('get_order_lines: '|| 'UnExpected Error in Set_Created_By_Context.');
759 END IF;
760 raise FND_API.G_EXC_UNEXPECTED_ERROR;
761
762 end if;
763 else
764 IF PG_DEBUG <> 0 THEN
765 oe_debug_pub.add('get_order_lines: '|| 'NOT Setting the Org Context since MO:Operating Unit = OEL.org_id.', 5);
766 END IF;
767 end if;
768 -- end bugfix 3014000
769
770
771 lOperUnit := WorkOrder_Rec.org_id; -- bugfix 3014000 : OE api will set the mo oper unit in cache.
772 -- Instead of again querying up profile, we can safely use WorkOrder_Rec.org_id
773
774 --
775 -- End Bugfix 2243672
776 --
777
778 --
779 -- bugfix 2227841: Moved the function calls from the cursor for
780 -- performance reasons.
781 --
782
783
784 -- bugfix 2868148
785 -- added new function GET_FLOW_QTY to consider flow supply during discrete job creation
786 /***************************************************************************************************
787 Scenarios
788 Order Qty = 10
789
790 Pln Qty Compl Qty Flow_Qty Get_Reserved_qty Total Supply Get_Notinv_qty Net Supply
791 (PQ) (CQ) FQ = PQ- CQ (RQ) TS = RQ + FQ (WDD) WDD - TS
792 = 0 if <=0 Create supply
793 = PQ-CQ if >0 if > 0
794
795 6 6 0 6 6 10 10-6 = 4
796 6 1 5 1 6 10 10-6 = 4
797 6 0 6 0 6 10 10-6 = 4
798 6 8 0* 8 8 10 10-8 = 2
799 10 10 0 10 10 10 10-10 = 0
800 10 1 9 1 10 10 10-10 = 0
801 10 0 10 0 10 10 10-10 = 0
802 10 12 0* 10 10 10 10-10 = 0
803
804 * 0 since FQ = -2
805 ****************************************************************************************************/
806 --Bugfix 6146803: Checking of GET_NOTINV_QTY - GET_RESERVED_QTY and locking the corresponding order
807 -- line has to be atomic. So commenting out the following part.
808 /*if ( GET_NOTINV_QTY(WorkOrder_Rec.line_id) - GET_RESERVED_QTY(WorkOrder_Rec.line_id) <= 0 )
809 then
810 IF PG_DEBUG <> 0 THEN
811 oe_debug_pub.add('get_order_lines: ' || 'get_notinv_qty() - get_reserved_qty() <= 0.',2);
812
813 oe_debug_pub.add('get_order_lines: ' || 'This line ('||WorkOrder_Rec.line_id||
814 ') is not eligible for creation of workorder.',2);
815 END IF;
816 goto end_of_loop;
817 end if;
818 */ --Bugfix 6146803
819 -- Check wip supply type. If it is not 1=Discrete or 0=None, then, ignore this line.
820 if WIP_ATO_UTILS.check_wip_supply_type (
821 p_so_header_id => WorkOrder_Rec.header_id,
822 p_so_line => WorkOrder_Rec.line_id,
823 p_so_delivery => NULL,
824 p_org_id => WorkOrder_Rec.ship_from_org_id) not in (0,1)
825 then
826 IF PG_DEBUG <> 0 THEN
827 oe_debug_pub.add('get_order_lines: ' || 'check_wip_supply_type() returned a value not in (0,1). ',2);
828
829 oe_debug_pub.add('get_order_lines: ' || 'This line ('||WorkOrder_Rec.line_id||
830 ') is not eligible for creation of workorder.', 2);
831 END IF;
832 goto end_of_loop;
833 end if;
834
835 -- end bugfix 2227841
836
837 -- This should be the incremented only after the line has passed the previous checks.
838 -- bug 6146803: Move this line to after obtaining the lock.
839 --l_rows_selected := l_rows_selected + 1;
840
841 IF PG_DEBUG <> 0 THEN
842 oe_debug_pub.add('get_order_lines: ' || 'Looking the sourcing information...',1);
843 END IF;
844
845 l_stmt_num := 2;
846
847 -- Bug 6146803: Checking of GET_NOTINV_QTY - GET_RESERVED_QTY and locking the corresponding order
848 -- line has to be atomic.
849
850 begin
851 SELECT ship_from_org_id
852 INTO l_ship_from_org_id
853 FROM OE_ORDER_LINES_ALL
854 WHERE line_id = WorkOrder_Rec.ato_line_id --- bug fix 5207010 . We should lock based on ATO line id
855 and (GET_NOTINV_QTY(WorkOrder_Rec.line_id) - GET_RESERVED_QTY(WorkOrder_Rec.line_id)) > 0
856 FOR UPDATE NOWAIT; -- bugfix 2388802: lock the row which is really eligible
857 l_inventory_item_id := WorkOrder_Rec.inventory_item_id;
858 exception
859 WHEN record_locked THEN
860 IF PG_DEBUG <> 0 THEN
861 OE_DEBUG_PUB.add ('get_order_lines: ' || 'Could not lock line id '|| to_char(WorkOrder_Rec.line_id) ||' for update.');
862
863 OE_DEBUG_PUB.add ('get_order_lines: ' || 'This line is being processed by another process.',1);
864 END IF;
865 goto end_of_loop;
866 -- bugfix 2420381: added the excpn for better handling of error.
867
868 -- Bug 6146803: No data found shall happen if GET_NOTINV_QTY - GET_RESERVED_QTY <= 0
869 WHEN NO_DATA_FOUND THEN
870 IF PG_DEBUG <> 0 THEN
871 oe_debug_pub.add('get_order_lines: ' || 'get_notinv_qty() - get_reserved_qty() <= 0.',2);
872
873 oe_debug_pub.add('get_order_lines: ' || 'This line ('||WorkOrder_Rec.line_id||
874 ') is not eligible for creation of workorder.',2);
875 END IF;
876 goto end_of_loop;
877
878 WHEN others THEN
879 IF PG_DEBUG <> 0 THEN
880 oe_debug_pub.add('get_order_lines: ' || 'others exception while locking line '||WorkOrder_Rec.line_id||':'||sqlerrm,1);
881 END IF;
882 goto end_of_loop;
883 end;
884
885 l_rows_selected := l_rows_selected + 1; --Bugfix 6146803
886
887 /* bugfix 3136206: added the following debug stmt */
888 IF PG_DEBUG <> 0 THEN
889 oe_debug_pub.add('get_order_lines: ' || 'successfully locked line '|| WorkOrder_Rec.line_id);
890 END IF;
891
892 l_stmt_num := 3;
893
894 --replaced query_socuring_org call (change done as part of OPM project)
895 --check_cto_can_creat_supply is a wrapper over query_socuring_org
896 --and custom api CTO_CUSTOM_SUPPLY_CHECK_PK.Check_Supply.
897 --Enhacement for R12 is, AFAS should not create supply
898 --if custom api returns 'N'
899
900 CTO_UTILITY_PK.check_cto_can_create_supply (
901 P_config_item_id => l_inventory_item_id,
902 P_org_id => l_ship_from_org_id,
903 x_can_create_supply => l_can_create_supply,--declare
904 p_source_type =>l_source_type,
905 x_return_status =>l_x_return_status,
906 X_msg_count =>l_x_msg_count,
907 X_msg_data =>l_x_msg_data,
908 x_sourcing_org =>l_sourcing_org,
909 X_message =>l_message --declare
910 );
911
912 IF PG_DEBUG <> 0 THEN
913 oe_debug_pub.add('get_order_lines: ' || 'Return status from Query_sourcing_org = '||l_x_return_status,1);
914 END IF;
915
916 IF l_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
917
918 IF l_can_create_supply = 'N' THEN --opm
919 IF PG_DEBUG <> 0 THEN
920 --would identify if line is skipped becuase of custom hook
921 oe_debug_pub.add('get_order_lines: ' ||l_message,1);
922 END IF;
923
924 IF nvl(l_source_type,1) = 66 THEN --Kiran Konada
925 IF PG_DEBUG <> 0 THEN
926 oe_debug_pub.add('get_order_lines: ' || 'Multiple sourcing defined for this item in this org...Supply will be created by planning. ',1);
927 END IF;
928 l_rows_errored := l_rows_errored + 1;
929 goto end_of_loop;
930 END IF;
931
932 END IF; --l_can_create_supply
933
934 ELSIF l_x_return_status = FND_API.G_RET_STS_ERROR THEN
935 IF PG_DEBUG <> 0 THEN
936 oe_debug_pub.add('get_order_lines: ' || 'Expected error occurred in Query_sourcing_org...',1);
937 END IF;
938 l_rows_errored := l_rows_errored + 1;
939 -- we do not want to raise error here, since we want to process remaining lines
940 goto end_of_loop;
941
942 ELSIF l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
943 IF PG_DEBUG <> 0 THEN
944 oe_debug_pub.add('get_order_lines: ' || 'Unexpected error occurred in Query_sourcing_org procedure...',1);
945 END IF;
946 l_rows_errored := l_rows_errored + 1;
947 -- we do not want to raise error here, since we want to process remaining lines
948 goto end_of_loop;
949 END IF;
950
951 IF l_can_create_supply = 'Y' THEN --as part of OPM enhancement
952 if nvl(l_source_type,1) = 3
953 then
954 IF PG_DEBUG <> 0 THEN
955 oe_debug_pub.add('get_order_lines: ' || 'It is a BUY configuration Need not process this...',1);
956 END IF;
957 l_rows_selected := l_rows_selected - 1;
958
959 -- Start 2681321: if the source type is TRANSFER FROM, we should not create a workorder
960
961 elsif nvl(l_source_type,2) = 1 then
962 IF PG_DEBUG <> 0 THEN
963 oe_debug_pub.add('TRANSFER FROM sourcing rule exists. Work order need not be created.',1);
964 END IF;
965 l_rows_selected := l_rows_selected - 1;
966
967 -- End 2681321
968 else
969
970 l_stmt_num := 4;
971 if (l_rows_selected - l_rows_errored = 1) then
972 select wip_job_schedule_interface_s.nextval
973 into x_wip_seq
974 from dual;
975 end if;
976
977 IF PG_DEBUG <> 0 THEN
978 OE_DEBUG_PUB.add('get_order_lines: ' || 'Get_Order_lines: ' ||
979 ' Procesing Order Line ' ||
980 to_char(WorkOrder_Rec.line_id), 2);
981 END IF;
982
983
984 l_stmt_num := 5;
985
986 /* bugfix 4051282: check for activity hold and generic hold */
987 OE_HOLDS_PUB.Check_Holds(p_api_version => 1.0,
988 p_line_id => WorkOrder_Rec.line_id,
989 p_wf_item => 'OEOL',
990 p_wf_activity => 'CREATE_SUPPLY',
991 x_result_out => l_x_hold_result_out,
992 x_return_status => l_x_return_status,
993 x_msg_count => l_x_error_msg_count,
994 x_msg_data => l_x_error_msg);
995
996 if (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) then
997
998 IF PG_DEBUG <> 0 THEN
999 OE_DEBUG_PUB.add('get_order_lines: ' || 'FAILED in Hold Check: ' || l_x_return_status ||
1000 ' For Order Line ' || to_char(WorkOrder_Rec.line_id), 1);
1001
1002 OE_DEBUG_PUB.add('get_order_lines: ' || 'Continuing with next line..',3);
1003 END IF;
1004 else
1005
1006 IF PG_DEBUG <> 0 THEN
1007 OE_DEBUG_PUB.add('get_order_lines: ' || 'Success in Hold Check: ' || l_x_return_status ||
1008 ' For Order Line ' || to_char(WorkOrder_Rec.line_id), 1);
1009 END IF;
1010
1011 if (l_x_hold_result_out = FND_API.G_TRUE) then
1012
1013 IF PG_DEBUG <> 0 THEN
1014 OE_DEBUG_PUB.add('get_order_lines: ' || 'Hold Found on order line ' || to_char(WorkOrder_Rec.line_id), 1);
1015
1016 OE_DEBUG_PUB.add('get_order_lines: ' || 'Continuing with next line..',3);
1017 END IF;
1018
1019 l_rows_on_hold := l_rows_on_hold + 1;
1020
1021 else
1022
1023 /* If Departure Planning is required, check if deliveries have been assigned and planned */
1024
1025 BEGIN
1026 IF PG_DEBUG <> 0 THEN
1027 OE_DEBUG_PUB.add('get_order_lines: ' || 'before dep plan',1);
1028 END IF;
1029
1030 l_stmt_num := 6;
1031
1032 select nvl(oel.dep_plan_required_flag,'N')
1033 into l_dep_plan_flag
1034 from oe_order_lines_all oel
1035 where oel.line_id = WorkOrder_Rec.line_id;
1036
1037 IF PG_DEBUG <> 0 THEN
1038 OE_DEBUG_PUB.add('get_order_lines: ' || 'l_dep_plan_flag::'||l_dep_plan_flag, 2);
1039 END IF;
1040
1041 if l_dep_plan_flag = 'Y' then
1042
1043 l_stmt_num := 7;
1044
1045 --
1046 -- For partially reserved sales orders, we will
1047 -- still check if the order line is Delivery
1048 -- Planned for the entire quantity
1049 --
1050 CTO_WIP_UTIL.Delivery_Planned(
1051 p_line_id => WorkOrder_Rec.line_id,
1052 x_result_out => l_x_result_out,
1053 x_return_status => l_x_return_status,
1054 x_msg_count => l_x_msg_count,
1055 x_msg_data => l_x_msg_data);
1056
1057 if (l_x_return_status = FND_API.G_RET_STS_ERROR) then
1058 IF PG_DEBUG <> 0 THEN
1059 OE_DEBUG_PUB.add('get_order_lines: ' ||
1060 'Expected error in Delivery_Planned: '
1061 || l_x_return_status
1062 || ' For Order Line '
1063 ||to_char(WorkOrder_Rec.line_id), 1);
1064 END IF;
1065 raise FND_API.G_EXC_ERROR;
1066
1067 elsif (l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1068 IF PG_DEBUG <> 0 THEN
1069 OE_DEBUG_PUB.add('get_order_lines: ' ||
1070 'UnExpected error in Delivery_Planned: '
1071 || l_x_return_status
1072 || ' For Order Line '
1073 ||to_char(WorkOrder_Rec.line_id), 1);
1074 END IF;
1075 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1076
1077 else
1078 IF PG_DEBUG <> 0 THEN
1079 OE_DEBUG_PUB.add('get_order_lines: ' ||
1080 'Success in Delivery_Planned: '
1081 || l_x_return_status
1082 || ' For Order Line '
1083 ||to_char(WorkOrder_Rec.line_id), 1);
1084 END IF;
1085
1086 if (l_x_result_out = FND_API.G_FALSE) then
1087 IF PG_DEBUG <> 0 THEN
1088 OE_DEBUG_PUB.add('get_order_lines: ' ||
1089 'Order line not delivery planned'
1090 ||to_char(WorkOrder_Rec.line_id), 1);
1091 END IF;
1092
1093 l_rows_dep_plan := l_rows_dep_plan+1;
1094 raise NOT_PLANNED;
1095 end if;
1096 end if;
1097 end if; /* dep_plan_flag = Y */
1098
1099 l_stmt_num := 8;
1100
1101 CTO_WIP_UTIL.insert_wip_interface(
1102 p_line_id => WorkOrder_Rec.line_id,
1103 p_wip_seq => x_wip_seq,
1104 p_status_type => p_status_type,
1105 p_class_code => p_class_code,
1106 p_conc_request_id => p_conc_request_id,
1107 p_conc_program_id => p_conc_program_id,
1108 p_conc_login_id => p_conc_login_id,
1109 p_user_id => p_user_id,
1110 p_appl_conc_program_id => p_appl_conc_program_id,
1111 x_return_status => l_x_return_status,
1112 x_error_message => l_x_error_message,
1113 x_message_name => l_x_message_name);
1114
1115 if (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1116 -- We dont want to raise error here, since we want to process other lines.
1117
1118 IF PG_DEBUG <> 0 THEN
1119 oe_debug_pub.add('get_order_lines: ' || 'Insert Error Message: ' || l_x_error_message,1);
1120
1121 OE_DEBUG_PUB.add('get_order_lines: ' || 'FAILED in Insert WIP Interface: ' || l_x_return_status ||
1122 'For Order Line ' || to_char(WorkOrder_Rec.line_id), 1);
1123 END IF;
1124
1125 else
1126
1127 IF PG_DEBUG <> 0 THEN
1128 OE_DEBUG_PUB.add('get_order_lines: ' || 'Success in Insert WIP Interface: ' || l_x_return_status ||
1129 'For Order Line ' || to_char(WorkOrder_Rec.line_id), 1);
1130 END IF;
1131
1132 l_rows_inserted := l_rows_inserted + 1;
1133
1134 end if; /* end of insert into wip_job_schedule_interface*/
1135
1136 EXCEPTION
1137 when NOT_PLANNED then
1138 IF PG_DEBUG <> 0 THEN
1139 OE_DEBUG_PUB.add('get_order_lines: ' || 'Deliveries not planned, not inserting into wjsi', 2);
1140 END IF;
1141
1142 END; /*sub-block for delivery planned lines*/
1143
1144 end if; /* end of l_x_hold_result_out = TRUE */
1145
1146 end if; /* end of hold return status = success */
1147
1148 end if; /* End of Buy sourcing check l_source_type = 3 */
1149
1150 END IF;--l_can_create_supply
1151
1152 << end_of_loop>>
1153 null;
1154 end loop;
1155
1156 IF PG_DEBUG <> 0 THEN
1157 oe_debug_pub.add('get_order_lines: ' || '****************************************', 5);
1158
1159 OE_DEBUG_PUB.add('get_order_lines: ' || 'Number of Order Lines Selected: ' ||
1160 to_char(l_rows_selected), 1);
1161
1162 OE_DEBUG_PUB.add('get_order_lines: ' || 'Number of Rows on Hold: ' ||
1163 to_char(l_rows_on_hold), 1);
1164
1165 OE_DEBUG_PUB.add('get_order_lines: ' || 'Number of Rows not Departure Planned: ' ||
1166 to_char(l_rows_dep_plan), 1);
1167
1168 OE_DEBUG_PUB.add('get_order_lines: ' || 'Number of Rows in error: ' ||
1169 to_char(l_rows_errored), 1);
1170
1171 OE_DEBUG_PUB.add('get_order_lines: ' || 'Number of Order Lines Inserted: ' ||
1172 to_char(l_rows_inserted), 1);
1173
1174 OE_DEBUG_PUB.add('get_order_lines: ' || 'WIP Group ID: ' ||
1175 to_char(x_wip_seq), 1);
1176
1177 oe_debug_pub.add('get_order_lines: ' || '****************************************', 5);
1178 END IF;
1179
1180 x_orders_loaded := l_rows_inserted;
1181
1182 -- Begin Bugfix 2019487:
1183 -- If run thru SO pad (progress order), the cursor should pick that record for progressing.
1184 -- If not, return FAILURE. If we dont return FAILURE, the WF will end normal and
1185 -- move to Ship Line Notified without creating work-order and reservations.
1186 -- This could happen if this line was picked by FAS earlier but was terminated (as in PAXAR's case)
1187 -- Also, if insert into wip_job_schedule_interface fails for this order line, we will error out.
1188
1189
1190
1191 if (l_line_id is not null AND x_orders_loaded = 0) then
1192
1193 IF PG_DEBUG <> 0 THEN
1194 oe_debug_pub.add ('get_order_lines: ' || 'CTOWIPWB.get_order_lines: Line '||l_line_id||
1195 ' is NOT eligible for Autocreate FAS anymore.',1);
1196 END IF;
1197
1198 -- Try to get the job info if it was created..
1199 declare
1200 l_job_name wip_entities.wip_entity_name%type;
1201 l_wip_entity_id wip_entities.wip_entity_id%type;
1202 begin
1203
1204 l_stmt_num := 9;
1205
1206 select dj.wip_entity_id, we.wip_entity_name
1207 into l_wip_entity_id, l_job_name
1208 from wip_discrete_jobs dj, wip_entities we,
1209 oe_order_lines_all oel
1210 where dj.wip_entity_id = we.wip_entity_id
1211 and dj.source_line_id = l_line_id
1212 and dj.source_code = 'WICDOL'
1213 --bugfix 2885568 to remove full table scan on wip_discrete_jobs
1214 --use unique index of wip_enitity _id and organization_id
1215 and oel.line_id = l_line_id
1216 and dj.primary_item_id = oel.inventory_item_id --for using index wdj_N1
1217 and oel.ship_from_org_id = dj.organization_id ;--for using index wdj_u1
1218 --end bugfix 2885568
1219
1220 IF PG_DEBUG <> 0 THEN
1221 oe_debug_pub.add ('get_order_lines: ' || 'Info: A WIP job ( '||l_job_name||' ) was created for this line already.',1);
1222 END IF;
1223
1224 exception
1225 when no_data_found then
1226 IF PG_DEBUG <> 0 THEN
1227 oe_debug_pub.add ('get_order_lines: ' || 'Could not find a WIP job. Records probably stuck in wip job schedule interface.',1);
1228 END IF;
1229
1230 when others then
1231 IF PG_DEBUG <> 0 THEN
1232 oe_debug_pub.add ('get_order_lines: ' || 'Error while fetching the WIP job. : '||substr(sqlerrm,1,200),1);
1233
1234 oe_debug_pub.add ('get_order_lines: ' || 'Continuing..',1);
1235 END IF;
1236 end;
1237 --return 0; -- bugfix 2105156: no need to return an error
1238 end if;
1239 -- End Bugfix 2019487:
1240 return 1;
1241
1242 EXCEPTION
1243 -- bugfix 2053360 : handle the record_locked exception.
1244
1245 WHEN record_locked THEN
1246 IF PG_DEBUG <> 0 THEN
1247 OE_DEBUG_PUB.add ('get_order_lines: ' || 'Could not lock line id '|| to_char(p_line_id) ||' for update.',1);
1248
1249 OE_DEBUG_PUB.add ('get_order_lines: ' || 'This line is being processed by another process.',1);
1250 END IF;
1251 unlock_line_id (p_line_id); -- bugfix 3136206
1252 return 1; -- return success otherwise, w/f will be in retry mode ! --bugfix 2105156
1253
1254
1255 WHEN FND_API.G_EXC_ERROR THEN
1256 IF PG_DEBUG <> 0 THEN
1257 OE_DEBUG_PUB.add('get_order_lines: ' || 'Expected Error in CTOWIPWB.get_order_lines (stmt: '||l_stmt_num||')' ,1);
1258 END IF;
1259 unlock_line_id (p_line_id); -- bugfix 3136206
1260 return 1; -- should not error out in case of excpected error
1261
1262 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1263 IF PG_DEBUG <> 0 THEN
1264 OE_DEBUG_PUB.add('get_order_lines: ' || 'Unxpected Error in CTOWIPWB.get_order_lines (stmt: '||l_stmt_num||')' ,1);
1265 END IF;
1266 unlock_line_id (p_line_id); -- bugfix 3136206
1267 return 0;
1268
1269 WHEN NO_DATA_FOUND THEN
1270 IF PG_DEBUG <> 0 THEN
1271 OE_DEBUG_PUB.add('get_order_lines: ' || 'CTOWIPWB.get_order_lines :: No Rows Found.',1);
1272 END IF;
1273 -- bugfix 2420381: commented out the re-initialization of x_orders_loaded.
1274 -- x_orders_loaded := 0;
1275 unlock_line_id (p_line_id); -- bugfix 3136206
1276 return 1;
1277
1278
1279 WHEN OTHERS THEN
1280 IF PG_DEBUG <> 0 THEN
1281 OE_DEBUG_PUB.add('get_order_lines: ' || 'Error in CTOWIPWB.get_order_lines (stmt: '||l_stmt_num||'):'||
1282 substrb(sqlerrm, 1, 150),1);
1283 END IF;
1284 unlock_line_id (p_line_id); -- bugfix 3136206
1285 return 0;
1286
1287
1288 END get_order_lines;
1289
1290
1291 /*****************************************************************************
1292 Procedure: reserve_work_order
1293 Parameters: p_model_line_id - line id of the configuration item in
1294 oe_order_lines_all
1295 p_wip_seq - group id to be used in interface table
1296 x_error_message - error message if insert fails
1297 x_message_name - name of error message if insert
1298 fails
1299
1300 Description: This function inserts a record into the
1301 WIP_JOB_SCHEDULE_INTERFACE table for the creation of
1302 work orders.
1303
1304 *****************************************************************************/
1305
1306 FUNCTION reserve_wo_to_so(p_wip_seq IN NUMBER,
1307 p_message_text VARCHAR2,
1308 p_message_name VARCHAR2
1309 )
1310
1311 RETURN integer
1312
1313
1314 IS
1315 -- WorkOrder_Rec number;
1316
1317 /* Reservation Variables */
1318 l_rec_reserve CTO_RESERVE_CONFIG.rec_reserve;
1319 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1320 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
1321 l_rsrv_qty number := 0;
1322 l_rsrv_id number;
1323 l_status varchar2(1);
1324 l_activity_status varchar2(8);
1325 l_status_type number;
1326
1327 l_err_num number;
1328 l_msg_txt varchar(240);
1329 l_msg_name varchar(30);
1330 l_msg_count number;
1331 l_stmt_num number;
1332 lSourceCode varchar2(30);
1333
1334 -- begin bugfix 3014000
1335 lOperUnit number;
1336 l_client_org_id number;
1337 xUserId number;
1338 xRespId number;
1339 xRespApplId number;
1340 l_x_return_status varchar2(1);
1341 l_x_msg_count number;
1342 l_x_msg_data varchar2(2000);
1343 -- end bugfix 3014000
1344
1345 lLineId number; -- bugfix 3136206
1346 resv_counter number := 0; -- bugfix 3136206
1347
1348 -- Bugfix 1661094: We will use the inventory API to convert wei.start_quantity, which is in
1349 -- primary UOM back to ordered UOM. Otherwise we will be passing primary_quantity and ordered UOM.
1350 -- eg. If ordered_quantity was 1 and ordered_uom was 'DZ', then,
1351 -- wei.start_quanity will be 12 (if primary_uom is 'EA').
1352 -- Without converting, we will be passing 12 and 'DZ' which is incorrect.
1353
1354 /* Cursor to select records to reserve */
1355
1356 cursor c_wip_job_records is
1357 select mso.sales_order_id,
1358 oel.line_id,
1359 oel.ship_from_org_id,
1360 oel.inventory_item_id,
1361 oel.order_quantity_uom,
1362 --oel.ordered_quantity,
1363 oel.source_document_type_id, -- bugfix 1799874: to check if it is an internal SO or regular
1364 INV_CONVERT.inv_um_convert -- bugfix 1661094: added conversion logic
1365 (oel.inventory_item_id,
1366 5, -- bugfix 2204376: pass precision of 5
1367 wei.start_quantity,
1368 msi.primary_uom_code,
1369 oel.order_quantity_uom,
1370 null,
1371 null) start_quantity,
1372 inv_reservation_global.g_source_type_wip,
1373 wei.wip_entity_id,
1374 oel.schedule_ship_date,
1375 -- Passing revision info only if revision_qty_control_code
1376 -- is not equal to 1
1377 -- wei.bom_revision
1378 -- 2620282: Selecting bom revision info
1379 decode( nvl(msi.revision_qty_control_code , 1 ) , 1, NULL , wei.bom_revision) bom_revision,
1380 oel.org_id -- bugfix 3014000
1381 from wip_job_schedule_interface wei,
1382 oe_order_lines_all oel,
1383 mtl_sales_orders mso,
1384 oe_order_headers_all oeh,
1385 --oe_order_types_v oet
1386 oe_transaction_types_tl oet,
1387 mtl_system_items msi -- bugfix 1661094:
1388 where wei.group_id = p_wip_seq
1389 and wei.source_line_id = oel.line_id
1390 and oeh.header_id = oel.header_id
1391 and oet.transaction_type_id = oeh.order_type_id
1392 and mso.segment1 = to_char(oeh.order_number)
1393 and mso.segment2 = oet.name
1394 and oet.language = (select language_code
1395 from fnd_languages
1396 where installed_flag = 'B')
1397 and mso.segment3 = lSourceCode
1398 and wei.load_type = WIP_CONSTANTS.CREATE_JOB
1399 and wei.organization_id = oel.ship_from_org_id
1400 and wei.process_phase = WIP_CONSTANTS.ML_COMPLETE
1401 and wei.process_status = WIP_CONSTANTS.COMPLETED -- 3202934
1402 and msi.inventory_item_id = oel.inventory_item_id
1403 and msi.organization_id = oel.ship_from_org_id;
1404
1405 BEGIN
1406 IF PG_DEBUG <> 0 THEN
1407 oe_debug_pub.add('reserve_wo_to_so: ' || 'Beginning Reservation Loop.',1);
1408 END IF;
1409 lSourceCode := fnd_profile.value('ONT_SOURCE_CODE');
1410 IF PG_DEBUG <> 0 THEN
1411 oe_debug_pub.add('reserve_wo_to_so: ' || 'lSourceCode: ' ||lSourceCode,2);
1412 END IF;
1413
1414 -- begin bugfix 3014000
1415
1416 -- Change for MOAC
1417 lOperUnit := nvl(MO_GLOBAL.get_current_org_id,-99);
1418 -- End of MOAC change
1419 IF PG_DEBUG <> 0 THEN
1420 oe_debug_pub.add('reserve_wo_to_so: '|| 'MO:operating Unit :' || lOperUnit, 2);
1421 END IF;
1422
1423 -- end bugfix 3014000
1424
1425 for WorkOrder_Rec in c_wip_job_records loop
1426
1427 resv_counter := resv_counter + 1; -- bugfix 3136206
1428 lLineId := WorkOrder_Rec.line_id; -- bugfix 3136206
1429
1430 -- begin bugfix 3014000
1431 if ( lOperUnit <> WorkOrder_Rec.org_id) then
1432 --
1433 -- Bugfix 3104000: When called from WF, there's no need to set the context since
1434 -- its already set by workflow.
1435 -- When called from SRS, set the context only when ORG_ID is different
1436 -- from OEL.org_id.
1437 -- Caveat: Single org customers should NOT set the MO:Operating unit for the resp
1438 -- which is used to run autocreate FAS. If so, OM will defer the activity.
1439
1440 IF PG_DEBUG <> 0 THEN
1441 oe_debug_pub.add('reserve_wo_to_so: '|| 'Setting the Org Context again to '||WorkOrder_Rec.org_id ||
1442 ' by calling OE_Order_Context_GRP.Set_Created_By_Context.', 5);
1443 END IF;
1444
1445 OE_Order_Context_GRP.Set_Created_By_Context (
1446 p_header_id => NULL
1447 ,p_line_id => WorkOrder_Rec.line_id
1448 ,x_orig_user_id => xUserId
1449 ,x_orig_resp_id => xRespId
1450 ,x_orig_resp_appl_id => xRespApplId
1451 ,x_return_status => l_x_return_status
1452 ,x_msg_count => l_x_msg_count
1453 ,x_msg_data => l_x_msg_data );
1454
1455 if l_x_return_status = FND_API.G_RET_STS_ERROR THEN
1456 oe_debug_pub.add('reserve_wo_to_so: '|| 'Expected Error in Set_Created_By_Context.');
1457 raise FND_API.G_EXC_ERROR;
1458
1459 elsif l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1460 oe_debug_pub.add('reserve_wo_to_so: '|| 'UnExpected Error in Set_Created_By_Context.');
1461 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1462
1463 end if;
1464
1465 else
1466 IF PG_DEBUG <> 0 THEN
1467 oe_debug_pub.add('reserve_wo_to_so: '|| 'NOT Setting the Org Context since MO:Operating Unit = OEL.org_id.', 5);
1468 END IF;
1469 end if;
1470
1471 lOperUnit := WorkOrder_Rec.org_id; -- OE api will set the mo oper unit in cache.
1472 -- Instead of again querying up profile, we can safely use WorkOrder_Rec.org_id
1473
1474 -- end bugfix 3014000
1475
1476 IF PG_DEBUG <> 0 THEN
1477 oe_debug_pub.add('reserve_wo_to_so: ' || 'ss::in loop::qty::'||WorkOrder_Rec.start_quantity,2);
1478 END IF;
1479
1480 l_rec_reserve.f_header_id := WorkOrder_Rec.sales_order_id;
1481 l_rec_reserve.f_line_id := WorkOrder_Rec.line_id;
1482 l_rec_reserve.f_mfg_org_id := WorkOrder_Rec.ship_from_org_id;
1483 l_rec_reserve.f_item_id := WorkOrder_Rec.inventory_item_id;
1484 l_rec_reserve.f_order_qty_uom := WorkOrder_Rec.order_quantity_uom;
1485 --l_rec_reserve.f_quantity := WorkOrder_Rec.ordered_quantity;
1486 l_rec_reserve.f_quantity := WorkOrder_Rec.start_quantity;
1487 l_rec_reserve.f_supply_source := inv_reservation_global.g_source_type_wip;
1488 l_rec_reserve.f_supply_header_id := WorkOrder_Rec.wip_entity_id;
1489 l_rec_reserve.f_ship_date := WorkOrder_Rec.schedule_ship_date;
1490 l_rec_reserve.f_source_document_type_id := WorkOrder_Rec.source_document_type_id; -- bugfix 1799874
1491 l_rec_reserve.f_bom_revision := WorkOrder_Rec.bom_revision; -- 2620282 : Passing bom revision info
1492
1493 /*----------------------------------------------------------+
1494 Reserve the sales order against the work order.
1495 If reservation is unsuccessful, purge discrete jobs created.
1496 +----------------------------------------------------------*/
1497
1498 IF PG_DEBUG <> 0 THEN
1499 oe_debug_pub.add('reserve_wo_to_so: ' || 'Reserving Line ID ' ||
1500 to_char(WorkOrder_Rec.line_id) ||
1501 'to WIP Entitiy ID ' ||
1502 to_char(WorkOrder_Rec.wip_entity_id),2);
1503 END IF;
1504
1505
1506 CTO_RESERVE_CONFIG.reserve_config(
1507 p_rec_reserve => l_rec_reserve,
1508 x_rsrv_qty => l_rsrv_qty,
1509 x_rsrv_id => l_rsrv_id,
1510 x_return_status => l_status,
1511 x_msg_txt => l_msg_txt,
1512 x_msg_name => l_msg_name );
1513
1514 IF PG_DEBUG <> 0 THEN
1515 oe_debug_pub.add('reserve_wo_to_so: ' || 'Reservation Result: ' || l_status,1);
1516
1517 oe_debug_pub.add('reserve_wo_to_so: ' || 'Reservation Msg: ' || l_msg_txt,1);
1518 END IF;
1519
1520 if (l_status <> FND_API.G_RET_STS_SUCCESS) then
1521 /*---------------------------------------------------+
1522 Reservation unsuccessful. We had originally discussed
1523 purging the work order if reservation failed, but
1524 Biju Baby had explained that there were accounting
1525 periods that had to be considered if we did that, which
1526 made the process too complicated. Instead, we will
1527 cancel the work order created.
1528 +---------------------------------------------------*/
1529 IF PG_DEBUG <> 0 THEN
1530 oe_debug_pub.add('reserve_wo_to_so: ' ||
1531 'Reservation FAILED for line id ' ||
1532 to_char(WorkOrder_Rec.line_id) ||
1533 ' and WIP Entity ID: ' ||
1534 to_char(WorkOrder_Rec.wip_entity_id) ||
1535 '.',1);
1536
1537 oe_debug_pub.add('reserve_wo_to_so: ' || 'Message Text = '||l_msg_txt,1);
1538 END IF;
1539
1540 l_stmt_num := 100;
1541 update wip_discrete_jobs
1542 set status_type = 7 -- CANCELLED
1543 where wip_entity_id = WorkOrder_Rec.wip_entity_id;
1544
1545 l_stmt_num := 110;
1546 update wip_job_schedule_interface
1547 set process_phase = WIP_CONSTANTS.ML_VALIDATION,
1548 process_status = WIP_CONSTANTS.RUNNING
1549 where wip_entity_id = WorkOrder_Rec.wip_entity_id
1550 and group_id = p_wip_seq;
1551
1552 else
1553 /*--------------------------------------------------+
1554 This is the equivalent of the feedback loop in 11.0.
1555 If the order is placed on hold during AutoCreate FAS,
1556 WIP will put the corresponding work order on hold.
1557 +--------------------------------------------------*/
1558 IF PG_DEBUG <> 0 THEN
1559 oe_debug_pub.add('reserve_wo_to_so: ' ||
1560 'Reservation Success for line id ' ||
1561 to_char(WorkOrder_Rec.line_id) ||
1562 ' and WIP Entity ID: ' ||
1563 to_char(WorkOrder_Rec.wip_entity_id) ||
1564 '.',1);
1565 END IF;
1566 BEGIN
1567
1568 l_stmt_num := 115;
1569 select status_type
1570 into l_status_type
1571 from wip_job_schedule_interface
1572 where group_id = p_wip_seq
1573 and source_line_id = WorkOrder_Rec.line_id
1574 and last_update_date <> creation_date
1575 and rownum = 1;
1576
1577 if (l_status_type = 6) then -- ON HOLD
1578
1579 WIP_SO_RESERVATIONS.respond_to_change_order(
1580 p_org_id => WorkOrder_Rec.ship_from_org_id,
1581 p_header_id => WorkOrder_Rec.sales_order_id,
1582 p_line_id => WorkOrder_Rec.line_id,
1583 x_status => l_status,
1584 x_msg_count => l_msg_count,
1585 x_msg_data => l_msg_txt);
1586
1587 IF PG_DEBUG <> 0 THEN
1588 oe_debug_pub.add('reserve_wo_to_so: ' || 'Return Status from respond to change order ' || l_status,1);
1589
1590 oe_debug_pub.add('reserve_wo_to_so: ' || 'Message Txt from respond to change order: ' || l_msg_txt,2);
1591 END IF;
1592
1593 if (l_status <> FND_API.G_RET_STS_SUCCESS) then
1594
1595 IF PG_DEBUG <> 0 THEN
1596 oe_debug_pub.add('reserve_wo_to_so: ' ||
1597 'Deleting Reservation for ' ||
1598 'line ID: ' ||
1599 to_char(WorkOrder_Rec.line_id) ||
1600 'to WIP Entity ID ' ||
1601 to_char(WorkOrder_Rec.wip_entity_id),2);
1602 END IF;
1603
1604 l_rsv_rec.reservation_id := l_rsrv_id;
1605
1606 INV_RESERVATION_PUB.delete_reservation
1607 (
1608 p_api_version_number => 1.0
1609 , p_init_msg_lst => fnd_api.g_true
1610 , x_return_status => l_status
1611 , x_msg_count => l_msg_count
1612 , x_msg_data => l_msg_txt
1613 , p_rsv_rec => l_rsv_rec
1614 , p_serial_number => l_dummy_sn
1615 );
1616
1617 IF PG_DEBUG <> 0 THEN
1618 oe_debug_pub.add('reserve_wo_to_so: ' || 'Return Status from delete reservation: ' || l_status,1);
1619
1620 oe_debug_pub.add('reserve_wo_to_so: ' || 'Msg Txt from delete reservation: ' || l_msg_txt,1);
1621 END IF;
1622
1623 l_stmt_num := 130;
1624 update wip_discrete_jobs
1625 set status_type = 7 -- CANCEL
1626 where wip_entity_id = WorkOrder_Rec.wip_entity_id;
1627
1628 l_stmt_num := 140;
1629 update wip_job_schedule_interface
1630 set process_phase = WIP_CONSTANTS.ML_VALIDATION,
1631 process_status = WIP_CONSTANTS.RUNNING
1632 where wip_entity_id = WorkOrder_Rec.wip_entity_id
1633 and group_id = p_wip_seq;
1634
1635 end if; /* respond to change order not successful */
1636
1637 end if; /* status_type = 6 */
1638
1639 EXCEPTION
1640
1641 when NO_DATA_FOUND then
1642
1643 if (l_stmt_num <> 115) then
1644 IF PG_DEBUG <> 0 THEN
1645 oe_debug_pub.add('reserve_wo_to_so: ' ||
1646 'No data found in feedback loop. '
1647 || 'Statement: ' || to_char(l_stmt_num)
1648 || substrb(sqlerrm, 1, 150),1);
1649 END IF;
1650
1651 end if;
1652
1653 END;
1654
1655 end if; /* reservation status not successful */
1656
1657 /* Bugfix 2105156 : Release the manual lock */
1658 UPDATE oe_order_lines_all
1659 SET program_id = null
1660 WHERE line_id = WorkOrder_Rec.line_id
1661 AND program_id = -99;
1662
1663 end loop; /* loop through wip job schedules for order line */
1664
1665 -- begin bugfix 3136206
1666 if resv_counter = 0 then
1667 oe_debug_pub.add('Warning: No reservations made. Check for errors in WJSI');
1668 -- unlock the lines. Update the program_id to null.
1669 update oe_order_lines_all
1670 set program_id = null
1671 where program_id = -99
1672 and line_id in (select wei.source_line_id
1673 from wip_job_schedule_interface wei
1674 where wei.group_id = p_wip_seq);
1675 end if;
1676 -- end bugfix 3136206
1677
1678 IF PG_DEBUG <> 0 THEN
1679 oe_debug_pub.add('reserve_wo_to_so: ' || 'End of Reservation',1);
1680 END IF;
1681
1682 return 1;
1683
1684 EXCEPTION
1685
1686 --begin bugfix 3014000
1687 WHEN FND_API.G_EXC_ERROR THEN
1688 OE_DEBUG_PUB.add('reserve_wo_to_so: '|| 'Expected Error in CTOWIPWB.reserve_wo_to_so (stmt: '||l_stmt_num||')' );
1689 unlock_line_id (lLineId); -- bugfix 3136206
1690 return 1; -- should not error out in case of excpected error
1691
1692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1693 OE_DEBUG_PUB.add('reserve_wo_to_so: '|| 'Unxpected Error in CTOWIPWB.reserve_wo_to_so (stmt: '||l_stmt_num||')' );
1694 unlock_line_id (lLineId); -- bugfix 3136206
1695 return 0;
1696
1697 -- end bugfix 3014000
1698
1699 when NO_DATA_FOUND then
1700 OE_DEBUG_PUB.ADD('reserve_wo_to_so: ' || 'Error in CTOWIPWB.reserve_wo_to_so: '
1701 || 'Statement: ' || to_char(l_stmt_num)
1702 || substrb(sqlerrm, 1, 150),1);
1703 unlock_line_id (lLineId); -- bugfix 3136206
1704 return 0;
1705
1706 when OTHERS then
1707 OE_DEBUG_PUB.add('reserve_wo_to_so: ' || 'Error in CTOWIPWB.reserve_wo_to_so: '
1708 || substrb(sqlerrm, 1, 150),1);
1709
1710 unlock_line_id (lLineId); -- bugfix 3136206
1711 return 0;
1712
1713 END reserve_wo_to_so;
1714
1715 /***********************************************************************************************
1716 Function: Get_Reserved_Qty
1717
1718 Parameters:
1719 pLineId - Line id of order line being processed
1720
1721 Description: This function is to support AutoCreate Supply for
1722 Partial Order Qty
1723 It returns the quantity for which supply has been created.
1724 Reserved Qty = Qty in mtl_reservations
1725 + Qty in wip_job_schedule_interface
1726 In case of an error, 0 qty will be returned.
1727
1728 Modified by :
1729 Renga Kannan 09/20/01 removed the debug messages
1730 SBhaskaran 11/02/01 Bugfix 2074290
1731 Note: All quantities here are in Primary UOM.
1732 *************************************************************************************************/
1733
1734 FUNCTION Get_Reserved_Qty(pLineId IN NUMBER)
1735 RETURN number
1736
1737 IS
1738
1739 -- Cursor to get reserved qty
1740 cursor c_mtl_rsv_qty is
1741
1742 -- bugfix 2074290: convert the reservation_quantity into primary UOM.
1743 -- Reservation_Quantity will not be in primary UOM in case of manual reservation.
1744 -- If the reservations are created by autocreate FAS, it will be in primary uom.
1745
1746 select nvl(sum(INV_CONVERT.inv_um_convert
1747 (oel.inventory_item_id,
1748 5, -- bugfix 2204376: pass precision of 5
1749 mr.reservation_quantity,
1750 mr.reservation_uom_code,
1751 msi.primary_uom_code,
1752 null,
1753 null)),0)
1754 from mtl_reservations mr,
1755 oe_order_headers_all oeh,
1756 oe_order_lines_all oel,
1757 mtl_system_items msi --bugfix 2074290: added msi
1758 where oel.line_id = pLineId
1759 and oel.header_id = oeh.header_id
1760 and mr.demand_source_line_id = oel.line_id
1761 and mr.organization_id = oel.ship_from_org_id
1762 and oel.inventory_item_id = msi.inventory_item_id --bugfix 2074290: added joins
1763 and oel.ship_from_org_id = msi.organization_id
1764 and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10,
1765 INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
1766 INV_RESERVATION_GLOBAL.g_source_type_oe); --bugfix 1799874
1767
1768
1769 -- Cursor to get qty in wip_job_schedule_interface
1770 -- This cursor was modified for bugs 2074290, 2435875, 2455900
1771 -- Bugfix 4254404: We should treat COMPLETED records as supply due to concurrency reasons.
1772
1773 cursor c_wjsi_qty is
1774 select nvl(sum(wjs.start_quantity), 0)
1775 from wip_job_schedule_interface wjs,
1776 oe_order_lines_all oel
1777 where oel.line_id = pLineId
1778 and wjs.source_line_id = oel.line_id
1779 and (wjs.process_status = WIP_CONSTANTS.PENDING
1780 or wjs.process_status = WIP_CONSTANTS.RUNNING
1781 or wjs.process_status = WIP_CONSTANTS.COMPLETED);
1782
1783 /* begin bugfix 2868148 : This cursor calculates supply created by flow schedule. */
1784
1785 cursor flow_supply is
1786 select nvl(sum(planned_quantity - quantity_completed),0) -- 2946071
1787 from wip_flow_schedules
1788 where demand_source_line = to_char(pLineId)
1789 and demand_source_type = inv_reservation_global.g_source_type_oe;
1790
1791 /* end bugfix 2868148 */
1792
1793 l_mtl_rsv_qty number := 0;
1794 l_wjsi_qty number := 0;
1795 l_flow_qty number := 0; --bugfix 2868148
1796 l_reserved_qty number := 0;
1797 lStmtNum number := 0;
1798
1799 BEGIN
1800
1801 --oe_debug_pub.add('Entering Get_Reserved_Qty', 6);
1802
1803 lStmtNum := 10;
1804 OPEN c_mtl_rsv_qty;
1805 FETCH c_mtl_rsv_qty INTO l_mtl_rsv_qty;
1806 CLOSE c_mtl_rsv_qty;
1807
1808
1809 lStmtNum := 20;
1810 OPEN c_wjsi_qty;
1811 FETCH c_wjsi_qty INTO l_wjsi_qty;
1812 CLOSE c_wjsi_qty;
1813
1814
1815 lStmtNum := 30;
1816
1817 -- begin bugfix 2868148
1818
1819 lStmtNum := 30;
1820 OPEN flow_supply;
1821 FETCH flow_supply INTO l_flow_qty;
1822 CLOSE flow_supply;
1823
1824 /**
1825 In case of overcompletion when
1826 sum (planned_qty - quantity_completed) is < 0 ,
1827 flow_qty will be returned as 0 to prevent extra
1828 supply creation with discrete jobs.
1829 **/
1830
1831 if l_flow_qty < 0 then /* planned_qty - qty_completed can be less than 0 in case of over-compl*/
1832 l_flow_qty := 0;
1833 end if;
1834
1835
1836 l_reserved_qty := l_mtl_rsv_qty + l_wjsi_qty + l_flow_qty;
1837
1838
1839 -- end bugfix 2868148
1840
1841
1842 return(l_reserved_qty);
1843
1844 EXCEPTION
1845 WHEN others THEN
1846 IF PG_DEBUG <> 0 THEN
1847 oe_debug_pub.add('Get_Reserved_Qty: ' || 'Others exception in Get_Reserved_Qty::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
1848 END IF;
1849 unlock_line_id (pLineId); -- bugfix 3136206
1850 return(0);
1851
1852 END Get_Reserved_Qty;
1853
1854
1855 -- begin bugfix 2095043
1856 /*****************************************************************************
1857 Function : GET_NOTINV_QTY
1858 Parameters : pLineId - Line id of order line being processed
1859
1860 Description :
1861 If shipping_interfaced flag is 'Y', then, return OQ in primary UOM.
1862
1863 If shipping_interfaced flag is 'N', then, loop thru wsh_delivery_detail
1864 to find out how much has been NOT been inventory-interfaced.
1865 If it fails to find a record in WDD, return 0.
1866
1867 All quantities are in primary UOM.
1868
1869 *****************************************************************************/
1870
1871 FUNCTION Get_NotInv_Qty(pLineId IN NUMBER)
1872 RETURN number
1873 IS
1874 l_quantity NUMBER; /* in primary UOM */
1875
1876 BEGIN
1877 -- If shipping_interfaced_flag is 'N', it means WDD is not populated.
1878 -- Pick ordered_quantity (in primary UOM) from OEOL in that case.
1879 --
1880 -- If shipping_interfaced_flag is 'Y', it means WDD is populated.
1881 -- Sum the requested_quantity in WDD for which inv interface is NOT run.
1882 -- If it returns null, all of them has been interfaced to inventory.
1883 -- Return 0 in that case.
1884 --
1885 -- Note : Requested_Quantity in WDD is in primary UOM
1886
1887 select decode( max(shipping_interfaced_flag),
1888 'N', max(INV_CONVERT.inv_um_convert
1889 (oel.inventory_item_id,
1890 5, -- bugfix 2204376: pass precision of 5
1891 oel.ordered_quantity,
1892 oel.order_quantity_uom,
1893 msi.primary_uom_code,
1894 null,
1895 null)),
1896 'Y', nvl(sum(wdd.requested_quantity), 0) )
1897 into l_quantity
1898 from WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
1899 oe_order_lines_all oel,
1900 mtl_system_items msi
1901 where oel.line_id = pLineId
1902 and oel.inventory_item_id = msi.inventory_item_id
1903 and oel.ship_from_org_id = msi.organization_id
1904 and wdd.source_line_id(+) = oel.line_id
1905 and wdd.source_code(+) = 'OE'
1906 and nvl(wdd.inv_interfaced_flag(+),'N') = 'N'
1907 and nvl(released_status(+),'N') <> 'D';
1908
1909 return (l_quantity);
1910
1911 EXCEPTION
1912 when OTHERS then
1913 IF PG_DEBUG <> 0 THEN
1914 oe_debug_pub.add('Get_NotInv_Qty: ' || 'Others exception in Get_NotInv_Qty::'||sqlerrm, 1);
1915 END IF;
1916 unlock_line_id (pLineId); -- bugfix 3136206
1917 return(0);
1918 END Get_NotInv_Qty;
1919 -- end bugfix 2095043
1920
1921
1922 end CTO_WIP_WRAPPER;
[Home] [Help]