[Home] [Help]
PACKAGE BODY: APPS.CTO_WIP_UTIL
Source
1 package body CTO_WIP_UTIL as
2 /* $Header: CTOWIPUB.pls 120.4.12020000.2 2012/07/05 09:42:07 ntungare ship $ */
3 /*============================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8 | |
9 | FILE NAME : CTOWIPUB.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 | June 7, 99 Angela Makalintal Initial version |
24 | May 7, 01 Sajani Sheth Support for partial FAS |
25 | Sep 14, 01 Shashi Bhaskaran Fixed bug 1988967 |
26 | While selecting from wsh_delivery_details |
27 | we should check source_code='OE' |
28 | Sep 26, 01 Shashi Bhaskaran Fixed bug 2017099 |
29 | Check with ordered_quantity(OQ) instead of OQ-CQ |
30 | where CQ=cancelled_quantity. When a line is |
31 | is canceled, OQ gets reflected. |
32 | |
33 | Oct 24, 01 Shashi Bhaskaran Fixed bug 2074290 |
34 | Convert the ordered_quantity into Primary UOM for|
35 | comparing with get_reserved_qty. |
36 | |
37 | Oct 25, 02 Kundan Sarkar Bugfix 2644849 (2620282 in br)|
38 | Insert bom revision info in |
39 | wip_job_schedule_interface |
40 |
41 | DEC 12, 2002 Kiran Konada
42 | Added code for ML SUPPLy fetaure
43 |
44 | Sep 23, 2003 Renga Kannan |
45 | Changed the following two table acecss to |
46 | view. This change is recommended by shipping |
47 | team to avoid getting inbound/dropship lines.
48 | WSH_NEW_DELIVERIES to WSH_NEW_DELIVERIES_OB_GRP_V
49 WSH_DELIVERY_DETAILS to WSH_DELIVERY_DETAILS_OB_GRP_V
50 This changes brings a wsh dependency to our code
51 the wsh pre-req for this change is 3125046
52 | June 1, 05 Renga Kannann Added nocopy hint
53 =============================================================================*/
54
55
56 -- Global constant holding the package name
57 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_ORDER_BOOK_UTIL';
58
59 /*****************************************************************************
60 Procedure: insert_wip_interface
61 Parameters: p_model_line_id - line id of the configuration item in
62 oe_order_lines_all
63 p_wip_seq - group id to be used in interface table
64 x_error_message - error message if insert fails
65 x_message_name - name of error message if insert
66 fails
67
68 Description: This function inserts a record into the
69 WIP_JOB_SCHEDULE_INTERFACE table for the creation of
70 work orders.
71
72 *****************************************************************************/
73
74
75 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
76
77 PROCEDURE insert_wip_interface(
78 p_line_id in number,
79 p_wip_seq in number,
80 p_status_type in number,
81 p_class_code in varchar2,
82 p_conc_request_id IN NUMBER,
83 p_conc_program_id IN NUMBER,
84 p_conc_login_id IN NUMBER,
85 p_user_id IN NUMBER,
86 p_appl_conc_program_id IN NUMBER,
87 x_return_status out NOCOPY varchar2,
88 x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
89 x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */
90 )
91
92 IS
93
94 --ml supply var's
95 l_mlsupply_parameter number := 0;
96 l_return_status varchar2(1);
97 l_error_message varchar2(400) := null;
98 l_message_name varchar2(30) := null;
99
100 x_groupID number := null;
101
102 l_sch_method number;
103 l_status number;
104
105
106
107 l_stmt_num number := 0;
108 l_user_id varchar2(255);
109 lDepPlanFlag varchar2(1);
110 l_ordered_qty number := 0; -- order line qty
111 l_partial_qty number := 0; -- order qty - reserved qty
112 l_wo_created_qty number := 0; --
113 l_current_qty number := 0;
114
115 l_routing_count number := 0; -- added for 14157494
116
117 insert_error exception;
118
119 CURSOR c_delivery_lines IS
120 select sum(wdd.requested_quantity) pQuantity,
121 -- Note: bug 1661094: wdd.requested_quantity is in primary uom
122 wda.delivery_id, wdd.load_seq_number lsn
123 from WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
124 wsh_delivery_assignments wda
125 where wdd.source_line_id = p_line_id
126 and wda.delivery_detail_id = wdd.delivery_detail_id
127 and wdd.source_code = 'OE' -- bugfix 1988967: only OE lines should be picked since
128 -- wsh_delivery_details can have lines related to
129 -- containers (source_code=WSH)
130 group by wdd.load_seq_number, wda.delivery_id
131 order by wda.delivery_id, wdd.load_seq_number;
132
133 BEGIN
134
135 x_return_status := FND_API.G_RET_STS_SUCCESS;
136
137 l_stmt_num := 100;
138
139 select nvl(oel.dep_plan_required_flag, 'N')
140 into lDepPlanFlag
141 from oe_order_lines_all oel
142 where oel.line_id = p_line_id;
143
144 --
145 -- Changes to support supply creation for partial qty
146 -- Getting qty for which supply needs to be created
147 --
148 l_stmt_num := 130;
149
150 --
151 -- bugfix 2095043: Added call to CTO_WIP_WRAPPER.Get_NotInv_Qty
152 -- Get_NotInv_Qty will return ordered_quantity (in primary UOM) if shipping_interface_flag is Y
153 -- Otherwise, it will return qty that has NOT been inventory interfaced.
154 --
155 -- All quantities are in primary UOM.
156 --
157
158 l_partial_qty := CTO_WIP_WRAPPER.Get_NotInv_Qty(p_line_id) - CTO_WIP_WRAPPER.Get_Reserved_Qty(p_line_id);
159
160
161 IF PG_DEBUG <> 0 THEN
162 oe_debug_pub.add('insert_wip_interface: ' || 'Partial qty for WO creation (in primary UOM) : '||to_char(l_partial_qty), 2);
163
164 oe_debug_pub.add('insert_wip_interface: ' || 'Dep Plan Flag for line_id '||to_char(p_line_id)||' is '||lDepPlanFlag, 2);
165 END IF;
166
167 -- added for 14157494: Begin -- Check if any routing is present for the line_id
168 SELECT count(1)
169 INTO l_routing_count
170 FROM bom_operational_routings bor,
171 oe_order_lines_all oel
172 WHERE oel.line_id = p_line_id
173 AND oel.INVENTORY_ITEM_ID = bor.assembly_item_id
174 AND oel.ship_from_org_id = bor.organization_id
175 AND ROWNUM = 1;
176
177 IF PG_DEBUG <> 0 THEN
178 oe_debug_pub.add('insert_wip_interface: l_routing_count :'|| to_char(l_routing_count) ,1);
179 END IF;
180 -- added for 14157494: End
181
182 IF lDepPlanFlag = 'N' THEN
183 --
184 -- No departure planning.
185 -- Create 1 work order for order line
186 --
187
188 l_stmt_num := 140;
189
190 --looking for ML supply org parameter value
191
192 SELECT ENABLE_LOWER_LEVEL_SUPPLY
193 INTO l_mlsupply_parameter
194 FROM bom_parameters bp,
195 oe_order_lines_all oel
196 WHERE oel.line_id = p_line_id
197 AND oel.ship_from_org_id = bp.organization_id;
198
199 IF PG_DEBUG <> 0 THEN
200 oe_debug_pub.add('insert_wip_interface: ' || 'enavle lower level supply value is '|| l_mlsupply_parameter , 4);
201
202
203 END IF;
204
205 IF (l_mlsupply_parameter in (2,3)) THEN --auto created config =2, auto created configs + ato items = 3
206
207
208
209
210 IF PG_DEBUG <> 0 THEN
211 oe_debug_pub.add('insert_wip_interface: ' || 'Before call to create_subassembly_jobs with enable lower supply param '|| l_mlsupply_parameter , 4);
212
213
214 END IF;
215
216
217 l_stmt_num := 141;
218 CTO_SUBASSEMBLY_SUP_PK.create_subassembly_jobs(
219 l_mlsupply_parameter,
220 p_line_id,
221 l_partial_qty,
222 p_wip_seq ,
223 p_status_type ,
224 p_class_code ,
225 p_conc_request_id ,
226 p_conc_program_id ,
227 p_conc_login_id ,
228 p_user_id ,
229 p_appl_conc_program_id ,
230 l_return_status,
231 l_error_message,
232 l_message_name
233 );
234
235 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
236 IF PG_DEBUG <> 0 THEN
237 oe_debug_pub.add ('insert_wip_interface: ' || 'failed after get_wroking_day' || l_return_status ,1);
238 oe_debug_pub.add ('insert_wip_interface: ' || 'error message' || l_error_message ,1);
239 END IF;
240 RAISE FND_API.G_EXC_ERROR;
241 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
242 IF PG_DEBUG <> 0 THEN
243 oe_debug_pub.add ('insert_wip_interface: ' || ' failed after call to get_working_day' || l_return_status ,1);
244 oe_debug_pub.add ('insert_wip_interface: ' || 'error message' || l_error_message ,1);
245 END IF;
246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247 ELSE
248 IF PG_DEBUG <> 0 THEN
249 oe_debug_pub.add('insert_wip_interface: ' || 'success from create_Subassembly_jobs ' ,1);
250 oe_debug_pub.add('insert_wip_interface: ' || l_error_message ,1);
251 END IF;
252 END IF;
253
254
255
256
257
258 END IF; --l_mlsupply_parameter
259
260
261 -- Fixed bug 5346922
262 -- Removed the decode stmt for status type column in the insert
263 l_stmt_num := 150;
264 insert into wip_job_schedule_interface
265 (last_update_date,
266 last_updated_by,
267 creation_date,
268 created_by,
269 last_update_login,
270 request_id,
271 program_id,
272 program_application_id,
273 program_update_date,
274 group_id,
275 source_code,
276 source_line_id,
277 process_phase,
278 process_status,
279 organization_id,
280 load_type,
281 status_type,
282 last_unit_completion_date,
283 primary_item_id,
284 wip_supply_type,
285 class_code,
286 firm_planned_flag,
287 demand_class,
288 start_quantity,
289 bom_revision_date,
290 routing_revision_date,
291 project_id,
292 task_id,
293 due_date,
294 bom_revision /* 2620282 : Insert bom revision info */
295 )
296 select SYSDATE, /* Last_Updated_Date */
297 p_user_id, /* Last_Updated_By */
298 SYSDATE, /* Creation_Date */
299 p_user_id, /* Created_By */
300 p_conc_login_id, /* Last_Update_Login */
301 p_conc_request_id, /* Request_ID */
302 p_conc_program_id, /* Program_ID */
303 p_appl_conc_program_id, /* Program_Application_ID */
304 SYSDATE, /* Last Update Date */
305 p_wip_seq, /* group_id */
306 'WICDOL', /* source_code */
307 oel.line_id, /* source line id */
308 WIP_CONSTANTS.ML_VALIDATION, /* process_phase */
309 WIP_CONSTANTS.PENDING, /* process_status */
310 oel.ship_from_org_id, /* organization id */
311 WIP_CONSTANTS.CREATE_JOB, /* Load_Type */
312 nvl(p_status_type, WIP_CONSTANTS.UNRELEASED),/* Status_Type */
313 oel.schedule_ship_date, /* Date Completed */
314 oel.inventory_item_id, /* Primary_Item_Id */
315 WIP_CONSTANTS.BASED_ON_BOM, /* Wip_Supply_Type */
316 decode(p_class_code, null, null
317 , p_class_code), /* Accouting Class */
318 2, /* Firm_Planned_Flag */
319 oel.demand_class_code, /* Demand Class */
320 l_partial_qty, /* Start Quantity: (in primary uom) */ --bugfix 2074290
321 trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
322 'MI')+1/(60*24), /* BOM_Revision_Date */
323 --Bugfix 14157494
324 --greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
325 decode(l_routing_count, 0, null, greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE)),
326 /* Routing_Revision_Date */
327 oel.project_id, /* Project_ID */
328 oel.task_id, /* Task_ID */
329 oel.schedule_ship_date,
330 BOM_REVISIONS.get_item_revision_fn
331 ( 'ALL',
332 'ALL',
333 oel.ship_from_org_id,
334 oel.inventory_item_id,
335 (trunc (greatest(nvl(cal.calendar_date,SYSDATE),
336 SYSDATE),'MI')+1/(60*24) )
337 ) /* 2620282 : Insert bom revision info */
338 from bom_calendar_dates cal,
339 mtl_parameters mp,
340 wip_parameters wp,
341 mtl_system_items msi,
342 oe_order_lines_all oel
343 where oel.line_id = p_line_id
344 and mp.organization_id = oel.ship_from_org_id
345 and wp.organization_id = mp.organization_id
346 and msi.organization_id = oel.ship_from_org_id
347 and msi.inventory_item_id = oel.inventory_item_id
348 and cal.calendar_code = mp.calendar_code
349 and cal.exception_set_id = mp.calendar_exception_set_id
350 and cal.seq_num =
351 (select greatest(1, (cal2.prior_seq_num -
352 (ceil(nvl(msi.fixed_lead_time,0) +
353 nvl(msi.variable_lead_time,0) *
354 l_partial_qty --bugfix 2074290: this is in primary uom
355 ))))
356 from bom_calendar_dates cal2
357 where cal2.calendar_code = mp.calendar_code
358 and cal2.exception_set_id =
359 mp.calendar_exception_set_id
360 and cal2.calendar_date =
361 trunc(oel.schedule_ship_date)
362 );
363
364 if (SQL%ROWCOUNT > 0) then
365 IF PG_DEBUG <> 0 THEN
366 oe_debug_pub.add('insert_wip_interface: ' || 'Number of Rows Inserted in WJSI: ' || to_char(SQL%ROWCOUNT));
367 END IF;
368 x_return_status := FND_API.G_RET_STS_SUCCESS;
369 else
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 cto_msg_pub.cto_message('BOM', 'BOM_ATO_PROCESS_ERROR');
372 raise INSERT_ERROR ;
373 end if;
374
375
376
377
378
379
380
381
382 ELSE
383 --
384 -- Departure planned order line
385 -- Create 1 work order for each unique combination of
386 -- delivery_id and load_seq_number.
387 -- The requested quantity should be a sum of all the lines for
388 -- each combination
389 --
390
391 l_stmt_num := 160;
392 l_wo_created_qty := 0;
393 l_current_qty := 0;
394
395 for lNextRec IN c_delivery_lines LOOP
396
397 IF PG_DEBUG <> 0 THEN
398 oe_debug_pub.add('insert_wip_interface: ' || 'line_id = '||to_char(p_line_id), 2);
399
400 oe_debug_pub.add('insert_wip_interface: ' || 'delivery_id = '||to_char(lNextRec.delivery_id), 2);
401
402 oe_debug_pub.add('insert_wip_interface: ' || 'lsn = '||to_char(lNextRec.lsn), 2);
403
404 oe_debug_pub.add('insert_wip_interface: ' || 'Qty = '||to_char(lNextRec.pQuantity), 2);
405 END IF;
406
407 l_current_qty := lNextRec.pQuantity;
408 IF PG_DEBUG <> 0 THEN
409 oe_debug_pub.add('insert_wip_interface: ' || 'l_current_qty::'||to_char(l_current_qty), 2);
410 END IF;
411
412 IF l_current_qty > l_partial_qty - l_wo_created_qty THEN
413 l_current_qty := l_partial_qty - l_wo_created_qty;
414 END IF;
415 IF PG_DEBUG <> 0 THEN
416 oe_debug_pub.add('insert_wip_interface: ' || 'New l_current_qty::'||to_char(l_current_qty), 2);
417 END IF;
418
419
420 --looking for ML supply org parameter value
421
422 l_stmt_num := 169;
423
424 SELECT ENABLE_LOWER_LEVEL_SUPPLY
425 INTO l_mlsupply_parameter
426 FROM bom_parameters bp,
427 oe_order_lines_all oel
428 WHERE oel.line_id = p_line_id
429 AND oel.ship_from_org_id = bp.organization_id;
430
431 IF (l_mlsupply_parameter in (2,3)) THEN --auto created config =2, auto created configs + ato items = 3
432
433
434 l_stmt_num := 169;
435 CTO_SUBASSEMBLY_SUP_PK.create_subassembly_jobs(
436 l_mlsupply_parameter,
437 p_line_id,
438 l_current_qty ,
439 p_wip_seq ,
440 p_status_type ,
441 p_class_code ,
442 p_conc_request_id ,
443 p_conc_program_id ,
444 p_conc_login_id ,
445 p_user_id ,
446 p_appl_conc_program_id ,
447 l_return_status,
448 l_error_message,
449 l_message_name
450 );
451
452 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
453 IF PG_DEBUG <> 0 THEN
454 oe_debug_pub.add ('insert_wip_interface: ' || 'failed after get_wroking_day' || l_return_status ,1);
455 oe_debug_pub.add ('insert_wip_interface: ' || 'error message' || l_error_message ,1);
456 END IF;
457 RAISE FND_API.G_EXC_ERROR;
458 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
459 IF PG_DEBUG <> 0 THEN
460 oe_debug_pub.add ('insert_wip_interface: ' || ' failed after call to get_working_day' || l_return_status ,1);
461 oe_debug_pub.add ('insert_wip_interface: ' || 'error message' || l_error_message ,1);
462 END IF;
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
464 ELSE
465 IF PG_DEBUG <> 0 THEN
466 oe_debug_pub.add('insert_wip_interface: ' || 'success from get_working_day ' ,1);
467 oe_debug_pub.add('insert_wip_interface: ' || l_error_message ,1);
468 END IF;
469 END IF;
470
471
472
473
474
475 END IF; --l_mlsupply_parameter
476
477
478 -- Fixed bug 5346922
479 -- Removed the decode for supply type
480 l_stmt_num := 170;
481 insert into wip_job_schedule_interface
482 (last_update_date,
483 last_updated_by,
484 creation_date,
485 created_by,
486 last_update_login,
487 request_id,
488 program_id,
489 program_application_id,
490 program_update_date,
491 group_id,
492 source_code,
493 source_line_id,
494 process_phase,
495 process_status,
496 organization_id,
497 load_type,
498 status_type,
499 last_unit_completion_date,
500 primary_item_id,
501 wip_supply_type,
502 class_code,
503 firm_planned_flag,
504 demand_class,
505 start_quantity,
506 bom_revision_date,
507 routing_revision_date,
508 project_id,
509 task_id,
510 due_date,
511 delivery_id,
512 build_sequence,
513 bom_revision /* 2620282 : Insert bom revision info */
514 )
515 select SYSDATE, /* Last_Updated_Date */
516 p_user_id, /* Last_Updated_By */
517 SYSDATE, /* Creation_Date */
518 p_user_id, /* Created_By */
519 p_conc_login_id, /* Last_Update_Login */
520 p_conc_request_id, /* Request_ID */
521 p_conc_program_id, /* Program_ID */
522 p_appl_conc_program_id, /* Program_Application_ID */
523 SYSDATE, /* Last Update Date */
524 p_wip_seq, /* group_id */
525 'WICDOL', /* source_code */
526 oel.line_id, /* source line id */
527 WIP_CONSTANTS.ML_VALIDATION, /* process_phase */
528 WIP_CONSTANTS.PENDING, /* process_status */
529 oel.ship_from_org_id, /* organization id */
530 WIP_CONSTANTS.CREATE_JOB, /* Load_Type */
531 nvl(p_status_type, WIP_CONSTANTS.UNRELEASED),/* Status_Type */
532 oel.schedule_ship_date, /* Date Completed */
533 oel.inventory_item_id, /* Primary_Item_Id */
534 WIP_CONSTANTS.BASED_ON_BOM, /* Wip_Supply_Type */
535 decode(p_class_code, null, null
536 , p_class_code),
537 /* Accouting Class */
538 2, /* Firm_Planned_Flag */
539 oel.demand_class_code, /* Demand Class */
540 INV_CONVERT.inv_um_convert(oel.inventory_item_id, --item_id
541 5, -- bugfix 2204376: pass precision of 5
542 l_current_qty,
543 oel.order_quantity_uom, --from uom
544 msi.primary_uom_code, --to uom
545 null, --from name
546 null --to name
547 ), /* start qty */
548 trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
549 'MI')+1/(60*24), /* BOM_Revision_Date */
550 --Bugfix 14157494
551 --greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
552 decode(l_routing_count, 0, null, greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE)),
553 /* Routing_Revision_Date */
554 oel.project_id, /* Project_ID */
555 oel.task_id, /* Task_ID */
556 oel.schedule_ship_date,
557 lNextRec.delivery_id,
558 lNextRec.lsn,
559 BOM_REVISIONS.get_item_revision_fn
560 ( 'ALL',
561 'ALL',
562 oel.ship_from_org_id,
563 oel.inventory_item_id,
564 (trunc (greatest(nvl(cal.calendar_date,SYSDATE),
565 SYSDATE),'MI')+1/(60*24) )
566 ) /* 2620282 : Insert bom revision info */
567 from bom_calendar_dates cal,
568 mtl_parameters mp,
569 wip_parameters wp,
570 mtl_system_items msi,
571 oe_order_lines_all oel
572 where oel.line_id = p_line_id
573 and mp.organization_id = oel.ship_from_org_id
574 and wp.organization_id = mp.organization_id
575 and msi.organization_id = oel.ship_from_org_id
576 and msi.inventory_item_id = oel.inventory_item_id
577 and cal.calendar_code = mp.calendar_code
578 and cal.exception_set_id = mp.calendar_exception_set_id
579 and cal.seq_num =
580 (select greatest(1, (cal2.prior_seq_num -
581 (ceil(nvl(msi.fixed_lead_time,0) +
582 nvl(msi.variable_lead_time,0) *
583 INV_CONVERT.inv_um_convert -- bugfix 1661094:
584 (oel.inventory_item_id, -- added conversion logic
585 5, -- bugfix 2204376: pass precision of 5
586 l_current_qty,
587 oel.order_quantity_uom,
588 msi.primary_uom_code,
589 null,
590 null)
591 ))))
592 from bom_calendar_dates cal2
593 where cal2.calendar_code = mp.calendar_code
594 and cal2.exception_set_id =
595 mp.calendar_exception_set_id
596 and cal2.calendar_date =
597 trunc(oel.schedule_ship_date)
598 );
599
600
601 if (SQL%ROWCOUNT > 0) then
602 IF PG_DEBUG <> 0 THEN
603 oe_debug_pub.add('insert_wip_interface: ' || 'Number of Rows Inserted in WJSI for departure planned : ' ||
604 to_char(SQL%ROWCOUNT),1);
605 END IF;
606 x_return_status := FND_API.G_RET_STS_SUCCESS;
607 else
608 x_return_status := FND_API.G_RET_STS_ERROR;
609 cto_msg_pub.cto_message('BOM', 'BOM_ATO_PROCESS_ERROR');
610 raise INSERT_ERROR ;
611 end if;
612
613 l_stmt_num := 180;
614 l_wo_created_qty := l_wo_created_qty + l_current_qty;
615
616 IF PG_DEBUG <> 0 THEN
617 oe_debug_pub.add('insert_wip_interface: ' || 'Qty of wo created::'||to_char(l_wo_created_qty),2);
618 END IF;
619
620 IF (l_wo_created_qty >= l_partial_qty) THEN
621 IF PG_DEBUG <> 0 THEN
622 oe_debug_pub.add('insert_wip_interface: ' || 'Exiting out of partial qty loop',2);
623 END IF;
624 EXIT;
625 END IF;
626
627 END LOOP;
628 END IF;
629
630 EXCEPTION
631
632
633 when FND_API.G_EXC_ERROR then
634
635 x_return_status := FND_API.G_RET_STS_ERROR;
636 x_error_message := 'CTOWIPUB.insert wip interface expected excpn: ';
637
638
639 IF PG_DEBUG <> 0 THEN
640 oe_debug_pub.add('insert_wip_interface: ' || ' expected excpn: ' || x_error_message,1);
641 END IF;
642
643
644
645 when FND_API.G_EXC_UNEXPECTED_ERROR then
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
647 x_error_message := 'CTOWIPUB.insert wip interface N expected excpn: '|| ':' ||
648 substrb(sqlerrm,1,100) ;
649
650
651 IF PG_DEBUG <> 0 THEN
652 oe_debug_pub.add('insert_wip_inetrface: ' || ' UN expected excpn: ' || x_error_message,1);
653 END IF;
654
655
656
657 when NO_DATA_FOUND then
658 x_error_message := 'CTOWIPUB.insert_wip_interface raised no-data-found: '|| ':' ||
659 substrb(sqlerrm,1,100);
660 x_return_status := FND_API.G_RET_STS_ERROR;
661 IF PG_DEBUG <> 0 THEN
662 oe_debug_pub.add('insert_wip_interface: ' || x_error_message,1);
663 END IF;
664 cto_msg_pub.cto_message('BOM', 'BOM_ATO_PROCESS_ERROR');
665
666 when INSERT_ERROR then
667 x_return_status := FND_API.G_RET_STS_ERROR;
668 x_error_message := 'CTOWIPUB.insert_wip_interface raised INSERT_ERROR:' || to_char(l_stmt_num)|| ':' ||
669 substrb(sqlerrm,1,100);
670 IF PG_DEBUG <> 0 THEN
671 oe_debug_pub.add('insert_wip_interface: ' || x_error_message,1);
672 END IF;
673
674 when OTHERS then
675 x_return_status := FND_API.G_RET_STS_ERROR;
676 x_error_message := 'CTOWIPUB.insert_wip_interface raised OTHERS excpn: ' || to_char(l_stmt_num)|| ':' ||
677 substrb(sqlerrm,1,100) ;
678
679 IF PG_DEBUG <> 0 THEN
680 oe_debug_pub.add('insert_wip_interface: ' || x_error_message,1);
681 END IF;
682 cto_msg_pub.cto_message('BOM', 'BOM_ATO_PROCESS_ERROR');
683 OE_MSG_PUB.Add_Exc_Msg
684 ( G_PKG_NAME
685 , 'insert_wip_interface'
686 );
687
688 END insert_wip_interface;
689
690 function departure_plan_required(p_line_id IN NUMBER
691 ) return integer
692
693 IS
694 l_eligible_line NUMBER := 0;
695 BEGIN
696 select 1
697 into l_eligible_line
698 from oe_order_lines_all oel,
699 mtl_customer_items mci
700 where oel.line_id = p_line_id
701 and oel.ordered_item_id = mci.customer_item_id (+)
702 and ((oel.item_identifier_type <> 'CUST')
703 or (oel.item_identifier_type = 'CUST'
704 and mci.dep_plan_prior_bld_flag <> 'Y')
705 or (validate_delivery_id(to_number(p_line_id)) = 1));
706
707 -- Do not need to be departure planned
708 return 0;
709
710 EXCEPTION
711
712 when NO_DATA_FOUND then
713 return 1;
714
715 when OTHERS then
716 OE_MSG_PUB.Add_Exc_Msg
717 ( G_PKG_NAME
718 , 'departure_plan_required'
719 );
720 return 2;
721
722 END departure_plan_required;
723
724 function validate_delivery_id(p_line_id IN NUMBER
725 ) return integer
726
727 IS
728
729 l_eligible_line NUMBER := 0;
730
731 BEGIN
732
733 select 1
734 into l_eligible_line
735 from oe_order_lines_all oel
736 where exists (select 'Exists'
737 from WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
738 wsh_delivery_assignments wda,
739 mtl_customer_items mci
740 where wdd.source_line_id = oel.line_id
741 and mci.customer_item_id = oel.ordered_item_id
742 and wda.delivery_detail_id = wdd.delivery_detail_id
743 and mci.dep_plan_prior_bld_flag = 'Y'
744 and oel.shipping_interfaced_flag = 'Y'
745 and wda.delivery_id is not NULL
746 and wdd.source_code = 'OE' -- bugfix 1988967
747 )
748 and oel.line_id = p_line_id;
749
750 return 1;
751
752
753 EXCEPTION
754
755 when NO_DATA_FOUND then
756 return 0;
757
758 when OTHERS then
759 return 0;
760
761 END validate_delivery_id;
762
763
764 PROCEDURE Delivery_Planned(p_line_id IN NUMBER,
765 x_result_out OUT NOCOPY VARCHAR2,
766 x_return_status OUT NOCOPY VARCHAR2,
767 x_msg_count OUT NOCOPY NUMBER,
768 x_msg_data OUT NOCOPY VARCHAR2)
769 IS
770
771 l_assigned varchar2(100);
772 l_planned varchar2(100);
773 l_imported varchar2(100);
774
775 Delivery_Not_Planned EXCEPTION;
776
777 BEGIN
778
779 --
780 -- Verify that delivery lines have been imported and quantities
781 -- for all delivey lines add up to the total requested qty
782 --
783
784 BEGIN
785 select 'IMPORTED'
786 into l_imported
787 from oe_order_lines_all oel,
788 WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
789 mtl_system_items msi --bugfix 2074290: added msi
790 where oel.line_id = p_line_id
791 and wdd.source_line_id = oel.line_id
792 and oel.inventory_item_id = msi.inventory_item_id --bugfix 2074290: added joins
793 and oel.ship_from_org_id = msi.organization_id
794 and wdd.source_code = 'OE' -- bugfix 1988967
795 -- begin bugfix 2074290: convert OQ to primary uom since WDD stores requested qty in primary uom
796 and INV_CONVERT.inv_um_convert
797 (oel.inventory_item_id,
798 5, -- bugfix 2204376: pass precision of 5
799 oel.ordered_quantity,
800 oel.order_quantity_uom,
801 msi.primary_uom_code,
802 null,
803 null) = (select nvl(sum(wdd1.requested_quantity), 0) -- bugfix 2017099
804 --end bugfix 2074290
805 from WSH_DELIVERY_DETAILS_OB_GRP_V wdd1
806 where wdd1.source_line_id = oel.line_id
807 and wdd1.source_code = 'OE') --bugfix 1988967
808 and rownum = 1;
809
810 EXCEPTION
811 when NO_DATA_FOUND then
812 IF PG_DEBUG <> 0 THEN
813 oe_debug_pub.add('Delivery_Planned: ' || 'Delivery lines HAVE NOT BEEN IMPORTED for order line '||to_char(p_line_id), 2);
814 END IF;
815 RAISE Delivery_Not_Planned;
816
817 END; /* Block checking if delivery lines are imported*/
818
819
820 --
821 -- Verify that deliveries have been assigned to all delivery lines
822 --
823
824 BEGIN
825
826 select 'NOTASSIGNED'
827 into l_assigned
828 from oe_order_lines_all oel,
829 WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
830 wsh_delivery_assignments wda
831 where oel.line_id = p_line_id
832 and wdd.source_line_id = oel.line_id
833 and wdd.source_code = 'OE' --bugfix 1988967
834 --and oel.ordered_quantity - nvl(oel.cancelled_quantity, 0) = (select nvl(sum(wdd1.requested_quantity), 0)
835 --from wsh_delivery_details wdd1
836 --where wdd1.source_line_id = oel.line_id)
837 and wda.delivery_detail_id = wdd.delivery_detail_id
838 and wda.delivery_id is null
839 and rownum = 1;
840
841 IF l_assigned = 'NOTASSIGNED' THEN
842 IF PG_DEBUG <> 0 THEN
843 oe_debug_pub.add('Delivery_Planned: ' || 'Deliveries have NOT BEEN ASSIGNED for line '||to_char(p_line_id), 1);
844 END IF;
845 RAISE Delivery_Not_Planned;
846 END IF; /* if delivery not assigned for any delivery line*/
847
848 EXCEPTION
849 when no_data_found then
850 IF PG_DEBUG <> 0 THEN
851 oe_debug_pub.add('Delivery_Planned: ' || 'Deliveries HAVE BEEN ASSIGNED for all delivery lines for order line '||to_char(p_line_id), 2);
852 END IF;
853
854 END; /* Block checking for delivery assignments*/
855
856 --
857 -- Deliveries have been assigned for all delivery lines.
858 -- Check if all deliveries are planned
859 --
860
861 BEGIN
862
863 select 'NOTPLANNED'
864 into l_planned
865 from oe_order_lines_all oel,
866 WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
867 wsh_delivery_assignments wda,
868 WSH_NEW_DELIVERIES_OB_GRP_V wnd
869 where oel.line_id = p_line_id
870 and wdd.source_line_id = oel.line_id
871 and wdd.source_code = 'OE' --bugfix 1988967
872 and wda.delivery_detail_id = wdd.delivery_detail_id
873 and wda.delivery_id = wnd.delivery_id
874 and nvl(wnd.planned_flag,'N') = 'N'
875 and rownum=1;
876
877 IF l_planned = 'NOTPLANNED' THEN
878 IF PG_DEBUG <> 0 THEN
879 oe_debug_pub.add('Delivery_Planned: ' || 'Deliveries have been assigned, but NOT PLANNED yet for line '||to_char(p_line_id), 1);
880 END IF;
881 RAISE Delivery_Not_Planned;
882 END IF; /* if delivery not planned*/
883
884 EXCEPTION
885 when NO_DATA_FOUND then
886 IF PG_DEBUG <> 0 THEN
887 oe_debug_pub.add('Delivery_Planned: ' || 'Deliveries HAVE BEEN PLANNED for order line '||to_char(p_line_id), 2);
888 END IF;
889
890 END; /* Block checking if deliveries planned */
891
892 --
893 -- Deliveries have been assigned and planned
894 --
895
896 x_return_status := FND_API.G_RET_STS_SUCCESS;
897 x_result_out := FND_API.G_TRUE;
898
899 EXCEPTION
900 when DELIVERY_NOT_PLANNED then
901 x_return_status := FND_API.G_RET_STS_SUCCESS;
902 x_result_out := FND_API.G_FALSE;
903
904 when FND_API.G_EXC_ERROR then
905 IF PG_DEBUG <> 0 THEN
906 oe_debug_pub.add('Delivery_Planned: ' || 'CTO_WIP_UTIL.Delivery_Planned::exp error', 1);
907 END IF;
908 x_return_status := FND_API.G_RET_STS_ERROR;
909 CTO_MSG_PUB.Count_And_Get
910 (p_msg_count => x_msg_count
911 ,p_msg_data => x_msg_data
912 );
913
914 when FND_API.G_EXC_UNEXPECTED_ERROR then
915 IF PG_DEBUG <> 0 THEN
916 oe_debug_pub.add('Delivery_Planned: ' || 'CTO_WIP_UTIL.Delivery_Planned::unexp error', 1);
917 END IF;
918 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
919 CTO_MSG_PUB.Count_And_Get
920 (p_msg_count => x_msg_count
921 ,p_msg_data => x_msg_data
922 );
923
924 when OTHERS then
925 IF PG_DEBUG <> 0 THEN
926 oe_debug_pub.add('Delivery_Planned: ' || 'CTO_WIP_UTIL.Delivery_Planned::others', 1);
927 END IF;
928 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
929 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
930 then
931 FND_MSG_PUB.Add_Exc_Msg
932 ( 'CTO_WIP_UTIL'
933 , 'Delivery_Planned'
934 );
935 end if;
936 CTO_MSG_PUB.Count_And_Get
937 (p_msg_count => x_msg_count
938 ,p_msg_data => x_msg_data
939 );
940
941 END Delivery_Planned;
942
943
944
945 end CTO_WIP_UTIL;