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