[Home] [Help]
PACKAGE BODY: APPS.CTO_WIP_UTIL
Source
1 package body CTO_WIP_UTIL as
2 /* $Header: CTOWIPUB.pls 120.4 2006/06/28 01:31:37 rekannan noship $ */
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
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
168 IF lDepPlanFlag = 'N' THEN
169 --
170 -- No departure planning.
171 -- Create 1 work order for order line
172 --
173
174 l_stmt_num := 140;
175
176 --looking for ML supply org parameter value
177
178 SELECT ENABLE_LOWER_LEVEL_SUPPLY
179 INTO l_mlsupply_parameter
180 FROM bom_parameters bp,
181 oe_order_lines_all oel
182 WHERE oel.line_id = p_line_id
183 AND oel.ship_from_org_id = bp.organization_id;
184
185 IF PG_DEBUG <> 0 THEN
186 oe_debug_pub.add('insert_wip_interface: ' || 'enavle lower level supply value is '|| l_mlsupply_parameter , 4);
187
188
189 END IF;
190
191 IF (l_mlsupply_parameter in (2,3)) THEN --auto created config =2, auto created configs + ato items = 3
192
193
194
195
196 IF PG_DEBUG <> 0 THEN
197 oe_debug_pub.add('insert_wip_interface: ' || 'Before call to create_subassembly_jobs with enable lower supply param '|| l_mlsupply_parameter , 4);
198
199
200 END IF;
201
202
203 l_stmt_num := 141;
204 CTO_SUBASSEMBLY_SUP_PK.create_subassembly_jobs(
205 l_mlsupply_parameter,
206 p_line_id,
207 l_partial_qty,
208 p_wip_seq ,
209 p_status_type ,
210 p_class_code ,
211 p_conc_request_id ,
212 p_conc_program_id ,
213 p_conc_login_id ,
214 p_user_id ,
215 p_appl_conc_program_id ,
216 l_return_status,
217 l_error_message,
218 l_message_name
219 );
220
221 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
222 IF PG_DEBUG <> 0 THEN
223 oe_debug_pub.add ('insert_wip_interface: ' || 'failed after get_wroking_day' || l_return_status ,1);
224 oe_debug_pub.add ('insert_wip_interface: ' || 'error message' || l_error_message ,1);
225 END IF;
226 RAISE FND_API.G_EXC_ERROR;
227 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
228 IF PG_DEBUG <> 0 THEN
229 oe_debug_pub.add ('insert_wip_interface: ' || ' failed after call to get_working_day' || l_return_status ,1);
230 oe_debug_pub.add ('insert_wip_interface: ' || 'error message' || l_error_message ,1);
231 END IF;
232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
233 ELSE
234 IF PG_DEBUG <> 0 THEN
235 oe_debug_pub.add('insert_wip_interface: ' || 'success from create_Subassembly_jobs ' ,1);
236 oe_debug_pub.add('insert_wip_interface: ' || l_error_message ,1);
237 END IF;
238 END IF;
239
240
241
242
243
244 END IF; --l_mlsupply_parameter
245
246
247 -- Fixed bug 5346922
248 -- Removed the decode stmt for status type column in the insert
249 l_stmt_num := 150;
250 insert into wip_job_schedule_interface
251 (last_update_date,
252 last_updated_by,
253 creation_date,
254 created_by,
255 last_update_login,
256 request_id,
257 program_id,
258 program_application_id,
259 program_update_date,
260 group_id,
261 source_code,
262 source_line_id,
263 process_phase,
264 process_status,
265 organization_id,
266 load_type,
267 status_type,
268 last_unit_completion_date,
269 primary_item_id,
270 wip_supply_type,
271 class_code,
272 firm_planned_flag,
273 demand_class,
274 start_quantity,
275 bom_revision_date,
276 routing_revision_date,
277 project_id,
278 task_id,
279 due_date,
280 bom_revision /* 2620282 : Insert bom revision info */
281 )
282 select SYSDATE, /* Last_Updated_Date */
283 p_user_id, /* Last_Updated_By */
284 SYSDATE, /* Creation_Date */
285 p_user_id, /* Created_By */
286 p_conc_login_id, /* Last_Update_Login */
287 p_conc_request_id, /* Request_ID */
288 p_conc_program_id, /* Program_ID */
289 p_appl_conc_program_id, /* Program_Application_ID */
290 SYSDATE, /* Last Update Date */
291 p_wip_seq, /* group_id */
292 'WICDOL', /* source_code */
293 oel.line_id, /* source line id */
294 WIP_CONSTANTS.ML_VALIDATION, /* process_phase */
295 WIP_CONSTANTS.PENDING, /* process_status */
296 oel.ship_from_org_id, /* organization id */
297 WIP_CONSTANTS.CREATE_JOB, /* Load_Type */
298 nvl(p_status_type, WIP_CONSTANTS.UNRELEASED),/* Status_Type */
299 oel.schedule_ship_date, /* Date Completed */
300 oel.inventory_item_id, /* Primary_Item_Id */
301 WIP_CONSTANTS.BASED_ON_BOM, /* Wip_Supply_Type */
302 decode(p_class_code, null, null
303 , p_class_code), /* Accouting Class */
304 2, /* Firm_Planned_Flag */
305 oel.demand_class_code, /* Demand Class */
306 l_partial_qty, /* Start Quantity: (in primary uom) */ --bugfix 2074290
307 trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
308 'MI')+1/(60*24), /* BOM_Revision_Date */
309 greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
310 /* Routing_Revision_Date */
311 oel.project_id, /* Project_ID */
312 oel.task_id, /* Task_ID */
313 oel.schedule_ship_date,
314 BOM_REVISIONS.get_item_revision_fn
315 ( 'ALL',
316 'ALL',
317 oel.ship_from_org_id,
318 oel.inventory_item_id,
319 (trunc (greatest(nvl(cal.calendar_date,SYSDATE),
320 SYSDATE),'MI')+1/(60*24) )
321 ) /* 2620282 : Insert bom revision info */
322 from bom_calendar_dates cal,
323 mtl_parameters mp,
324 wip_parameters wp,
325 mtl_system_items msi,
326 oe_order_lines_all oel
327 where oel.line_id = p_line_id
328 and mp.organization_id = oel.ship_from_org_id
329 and wp.organization_id = mp.organization_id
330 and msi.organization_id = oel.ship_from_org_id
331 and msi.inventory_item_id = oel.inventory_item_id
332 and cal.calendar_code = mp.calendar_code
333 and cal.exception_set_id = mp.calendar_exception_set_id
334 and cal.seq_num =
335 (select greatest(1, (cal2.prior_seq_num -
336 (ceil(nvl(msi.fixed_lead_time,0) +
337 nvl(msi.variable_lead_time,0) *
338 l_partial_qty --bugfix 2074290: this is in primary uom
339 ))))
340 from bom_calendar_dates cal2
341 where cal2.calendar_code = mp.calendar_code
342 and cal2.exception_set_id =
343 mp.calendar_exception_set_id
344 and cal2.calendar_date =
345 trunc(oel.schedule_ship_date)
346 );
347
348 if (SQL%ROWCOUNT > 0) then
349 IF PG_DEBUG <> 0 THEN
350 oe_debug_pub.add('insert_wip_interface: ' || 'Number of Rows Inserted in WJSI: ' || to_char(SQL%ROWCOUNT));
351 END IF;
352 x_return_status := FND_API.G_RET_STS_SUCCESS;
353 else
354 x_return_status := FND_API.G_RET_STS_ERROR;
355 cto_msg_pub.cto_message('BOM', 'BOM_ATO_PROCESS_ERROR');
356 raise INSERT_ERROR ;
357 end if;
358
359
360
361
362
363
364
365
366 ELSE
367 --
368 -- Departure planned order line
369 -- Create 1 work order for each unique combination of
370 -- delivery_id and load_seq_number.
371 -- The requested quantity should be a sum of all the lines for
372 -- each combination
373 --
374
375 l_stmt_num := 160;
376 l_wo_created_qty := 0;
377 l_current_qty := 0;
378
379 for lNextRec IN c_delivery_lines LOOP
380
381 IF PG_DEBUG <> 0 THEN
382 oe_debug_pub.add('insert_wip_interface: ' || 'line_id = '||to_char(p_line_id), 2);
383
384 oe_debug_pub.add('insert_wip_interface: ' || 'delivery_id = '||to_char(lNextRec.delivery_id), 2);
385
386 oe_debug_pub.add('insert_wip_interface: ' || 'lsn = '||to_char(lNextRec.lsn), 2);
387
388 oe_debug_pub.add('insert_wip_interface: ' || 'Qty = '||to_char(lNextRec.pQuantity), 2);
389 END IF;
390
391 l_current_qty := lNextRec.pQuantity;
392 IF PG_DEBUG <> 0 THEN
393 oe_debug_pub.add('insert_wip_interface: ' || 'l_current_qty::'||to_char(l_current_qty), 2);
394 END IF;
395
396 IF l_current_qty > l_partial_qty - l_wo_created_qty THEN
397 l_current_qty := l_partial_qty - l_wo_created_qty;
398 END IF;
399 IF PG_DEBUG <> 0 THEN
400 oe_debug_pub.add('insert_wip_interface: ' || 'New l_current_qty::'||to_char(l_current_qty), 2);
401 END IF;
402
403
404 --looking for ML supply org parameter value
405
406 l_stmt_num := 169;
407
408 SELECT ENABLE_LOWER_LEVEL_SUPPLY
409 INTO l_mlsupply_parameter
410 FROM bom_parameters bp,
411 oe_order_lines_all oel
412 WHERE oel.line_id = p_line_id
413 AND oel.ship_from_org_id = bp.organization_id;
414
415 IF (l_mlsupply_parameter in (2,3)) THEN --auto created config =2, auto created configs + ato items = 3
416
417
418 l_stmt_num := 169;
419 CTO_SUBASSEMBLY_SUP_PK.create_subassembly_jobs(
420 l_mlsupply_parameter,
421 p_line_id,
422 l_current_qty ,
423 p_wip_seq ,
424 p_status_type ,
425 p_class_code ,
426 p_conc_request_id ,
427 p_conc_program_id ,
428 p_conc_login_id ,
429 p_user_id ,
430 p_appl_conc_program_id ,
431 l_return_status,
432 l_error_message,
433 l_message_name
434 );
435
436 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
437 IF PG_DEBUG <> 0 THEN
438 oe_debug_pub.add ('insert_wip_interface: ' || 'failed after get_wroking_day' || l_return_status ,1);
439 oe_debug_pub.add ('insert_wip_interface: ' || 'error message' || l_error_message ,1);
440 END IF;
441 RAISE FND_API.G_EXC_ERROR;
442 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
443 IF PG_DEBUG <> 0 THEN
444 oe_debug_pub.add ('insert_wip_interface: ' || ' failed after call to get_working_day' || l_return_status ,1);
445 oe_debug_pub.add ('insert_wip_interface: ' || 'error message' || l_error_message ,1);
446 END IF;
447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
448 ELSE
449 IF PG_DEBUG <> 0 THEN
450 oe_debug_pub.add('insert_wip_interface: ' || 'success from get_working_day ' ,1);
451 oe_debug_pub.add('insert_wip_interface: ' || l_error_message ,1);
452 END IF;
453 END IF;
454
455
456
457
458
459 END IF; --l_mlsupply_parameter
460
461
462 -- Fixed bug 5346922
463 -- Removed the decode for supply type
464 l_stmt_num := 170;
465 insert into wip_job_schedule_interface
466 (last_update_date,
467 last_updated_by,
468 creation_date,
469 created_by,
470 last_update_login,
471 request_id,
472 program_id,
473 program_application_id,
474 program_update_date,
475 group_id,
476 source_code,
477 source_line_id,
478 process_phase,
479 process_status,
480 organization_id,
481 load_type,
482 status_type,
483 last_unit_completion_date,
484 primary_item_id,
485 wip_supply_type,
486 class_code,
487 firm_planned_flag,
488 demand_class,
489 start_quantity,
490 bom_revision_date,
491 routing_revision_date,
492 project_id,
493 task_id,
494 due_date,
495 delivery_id,
496 build_sequence,
497 bom_revision /* 2620282 : Insert bom revision info */
498 )
499 select SYSDATE, /* Last_Updated_Date */
500 p_user_id, /* Last_Updated_By */
501 SYSDATE, /* Creation_Date */
502 p_user_id, /* Created_By */
503 p_conc_login_id, /* Last_Update_Login */
504 p_conc_request_id, /* Request_ID */
505 p_conc_program_id, /* Program_ID */
506 p_appl_conc_program_id, /* Program_Application_ID */
507 SYSDATE, /* Last Update Date */
508 p_wip_seq, /* group_id */
509 'WICDOL', /* source_code */
510 oel.line_id, /* source line id */
511 WIP_CONSTANTS.ML_VALIDATION, /* process_phase */
512 WIP_CONSTANTS.PENDING, /* process_status */
513 oel.ship_from_org_id, /* organization id */
514 WIP_CONSTANTS.CREATE_JOB, /* Load_Type */
515 nvl(p_status_type, WIP_CONSTANTS.UNRELEASED),/* Status_Type */
516 oel.schedule_ship_date, /* Date Completed */
517 oel.inventory_item_id, /* Primary_Item_Id */
518 WIP_CONSTANTS.BASED_ON_BOM, /* Wip_Supply_Type */
519 decode(p_class_code, null, null
520 , p_class_code),
521 /* Accouting Class */
522 2, /* Firm_Planned_Flag */
523 oel.demand_class_code, /* Demand Class */
524 INV_CONVERT.inv_um_convert(oel.inventory_item_id, --item_id
525 5, -- bugfix 2204376: pass precision of 5
526 l_current_qty,
527 oel.order_quantity_uom, --from uom
528 msi.primary_uom_code, --to uom
529 null, --from name
530 null --to name
531 ), /* start qty */
532 trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
533 'MI')+1/(60*24), /* BOM_Revision_Date */
534 greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
535 /* Routing_Revision_Date */
536 oel.project_id, /* Project_ID */
537 oel.task_id, /* Task_ID */
538 oel.schedule_ship_date,
539 lNextRec.delivery_id,
540 lNextRec.lsn,
541 BOM_REVISIONS.get_item_revision_fn
542 ( 'ALL',
543 'ALL',
544 oel.ship_from_org_id,
545 oel.inventory_item_id,
546 (trunc (greatest(nvl(cal.calendar_date,SYSDATE),
547 SYSDATE),'MI')+1/(60*24) )
548 ) /* 2620282 : Insert bom revision info */
549 from bom_calendar_dates cal,
550 mtl_parameters mp,
551 wip_parameters wp,
552 mtl_system_items msi,
553 oe_order_lines_all oel
554 where oel.line_id = p_line_id
555 and mp.organization_id = oel.ship_from_org_id
556 and wp.organization_id = mp.organization_id
557 and msi.organization_id = oel.ship_from_org_id
558 and msi.inventory_item_id = oel.inventory_item_id
559 and cal.calendar_code = mp.calendar_code
560 and cal.exception_set_id = mp.calendar_exception_set_id
561 and cal.seq_num =
562 (select greatest(1, (cal2.prior_seq_num -
563 (ceil(nvl(msi.fixed_lead_time,0) +
564 nvl(msi.variable_lead_time,0) *
565 INV_CONVERT.inv_um_convert -- bugfix 1661094:
566 (oel.inventory_item_id, -- added conversion logic
567 5, -- bugfix 2204376: pass precision of 5
568 l_current_qty,
569 oel.order_quantity_uom,
570 msi.primary_uom_code,
571 null,
572 null)
573 ))))
574 from bom_calendar_dates cal2
575 where cal2.calendar_code = mp.calendar_code
576 and cal2.exception_set_id =
577 mp.calendar_exception_set_id
578 and cal2.calendar_date =
579 trunc(oel.schedule_ship_date)
580 );
581
582
583 if (SQL%ROWCOUNT > 0) then
584 IF PG_DEBUG <> 0 THEN
585 oe_debug_pub.add('insert_wip_interface: ' || 'Number of Rows Inserted in WJSI for departure planned : ' ||
586 to_char(SQL%ROWCOUNT),1);
587 END IF;
588 x_return_status := FND_API.G_RET_STS_SUCCESS;
589 else
590 x_return_status := FND_API.G_RET_STS_ERROR;
591 cto_msg_pub.cto_message('BOM', 'BOM_ATO_PROCESS_ERROR');
592 raise INSERT_ERROR ;
593 end if;
594
595 l_stmt_num := 180;
596 l_wo_created_qty := l_wo_created_qty + l_current_qty;
597
598 IF PG_DEBUG <> 0 THEN
599 oe_debug_pub.add('insert_wip_interface: ' || 'Qty of wo created::'||to_char(l_wo_created_qty),2);
600 END IF;
601
602 IF (l_wo_created_qty >= l_partial_qty) THEN
603 IF PG_DEBUG <> 0 THEN
604 oe_debug_pub.add('insert_wip_interface: ' || 'Exiting out of partial qty loop',2);
605 END IF;
606 EXIT;
607 END IF;
608
609 END LOOP;
610 END IF;
611
612 EXCEPTION
613
614
615 when FND_API.G_EXC_ERROR then
616
617 x_return_status := FND_API.G_RET_STS_ERROR;
618 x_error_message := 'CTOWIPUB.insert wip interface expected excpn: ';
619
620
621 IF PG_DEBUG <> 0 THEN
622 oe_debug_pub.add('insert_wip_interface: ' || ' expected excpn: ' || x_error_message,1);
623 END IF;
624
625
626
627 when FND_API.G_EXC_UNEXPECTED_ERROR then
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
629 x_error_message := 'CTOWIPUB.insert wip interface N expected excpn: '|| ':' ||
630 substrb(sqlerrm,1,100) ;
631
632
633 IF PG_DEBUG <> 0 THEN
634 oe_debug_pub.add('insert_wip_inetrface: ' || ' UN expected excpn: ' || x_error_message,1);
635 END IF;
636
637
638
639 when NO_DATA_FOUND then
640 x_error_message := 'CTOWIPUB.insert_wip_interface raised no-data-found: '|| ':' ||
641 substrb(sqlerrm,1,100);
642 x_return_status := FND_API.G_RET_STS_ERROR;
643 IF PG_DEBUG <> 0 THEN
644 oe_debug_pub.add('insert_wip_interface: ' || x_error_message,1);
645 END IF;
646 cto_msg_pub.cto_message('BOM', 'BOM_ATO_PROCESS_ERROR');
647
648 when INSERT_ERROR then
649 x_return_status := FND_API.G_RET_STS_ERROR;
650 x_error_message := 'CTOWIPUB.insert_wip_interface raised INSERT_ERROR:' || to_char(l_stmt_num)|| ':' ||
651 substrb(sqlerrm,1,100);
652 IF PG_DEBUG <> 0 THEN
653 oe_debug_pub.add('insert_wip_interface: ' || x_error_message,1);
654 END IF;
655
656 when OTHERS then
657 x_return_status := FND_API.G_RET_STS_ERROR;
658 x_error_message := 'CTOWIPUB.insert_wip_interface raised OTHERS excpn: ' || to_char(l_stmt_num)|| ':' ||
659 substrb(sqlerrm,1,100) ;
660
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 OE_MSG_PUB.Add_Exc_Msg
666 ( G_PKG_NAME
667 , 'insert_wip_interface'
668 );
669
670 END insert_wip_interface;
671
672 function departure_plan_required(p_line_id IN NUMBER
673 ) return integer
674
675 IS
676 l_eligible_line NUMBER := 0;
677 BEGIN
678 select 1
679 into l_eligible_line
680 from oe_order_lines_all oel,
681 mtl_customer_items mci
682 where oel.line_id = p_line_id
683 and oel.ordered_item_id = mci.customer_item_id (+)
684 and ((oel.item_identifier_type <> 'CUST')
685 or (oel.item_identifier_type = 'CUST'
686 and mci.dep_plan_prior_bld_flag <> 'Y')
687 or (validate_delivery_id(to_number(p_line_id)) = 1));
688
689 -- Do not need to be departure planned
690 return 0;
691
692 EXCEPTION
693
694 when NO_DATA_FOUND then
695 return 1;
696
697 when OTHERS then
698 OE_MSG_PUB.Add_Exc_Msg
699 ( G_PKG_NAME
700 , 'departure_plan_required'
701 );
702 return 2;
703
704 END departure_plan_required;
705
706 function validate_delivery_id(p_line_id IN NUMBER
707 ) return integer
708
709 IS
710
711 l_eligible_line NUMBER := 0;
712
713 BEGIN
714
715 select 1
716 into l_eligible_line
717 from oe_order_lines_all oel
718 where exists (select 'Exists'
719 from WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
720 wsh_delivery_assignments wda,
721 mtl_customer_items mci
722 where wdd.source_line_id = oel.line_id
723 and mci.customer_item_id = oel.ordered_item_id
724 and wda.delivery_detail_id = wdd.delivery_detail_id
725 and mci.dep_plan_prior_bld_flag = 'Y'
726 and oel.shipping_interfaced_flag = 'Y'
727 and wda.delivery_id is not NULL
728 and wdd.source_code = 'OE' -- bugfix 1988967
729 )
730 and oel.line_id = p_line_id;
731
732 return 1;
733
734
735 EXCEPTION
736
737 when NO_DATA_FOUND then
738 return 0;
739
740 when OTHERS then
741 return 0;
742
743 END validate_delivery_id;
744
745
746 PROCEDURE Delivery_Planned(p_line_id IN NUMBER,
747 x_result_out OUT NOCOPY VARCHAR2,
748 x_return_status OUT NOCOPY VARCHAR2,
749 x_msg_count OUT NOCOPY NUMBER,
750 x_msg_data OUT NOCOPY VARCHAR2)
751 IS
752
753 l_assigned varchar2(100);
754 l_planned varchar2(100);
755 l_imported varchar2(100);
756
757 Delivery_Not_Planned EXCEPTION;
758
759 BEGIN
760
761 --
762 -- Verify that delivery lines have been imported and quantities
763 -- for all delivey lines add up to the total requested qty
764 --
765
766 BEGIN
767 select 'IMPORTED'
768 into l_imported
769 from oe_order_lines_all oel,
770 WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
771 mtl_system_items msi --bugfix 2074290: added msi
772 where oel.line_id = p_line_id
773 and wdd.source_line_id = oel.line_id
774 and oel.inventory_item_id = msi.inventory_item_id --bugfix 2074290: added joins
775 and oel.ship_from_org_id = msi.organization_id
776 and wdd.source_code = 'OE' -- bugfix 1988967
777 -- begin bugfix 2074290: convert OQ to primary uom since WDD stores requested qty in primary uom
778 and INV_CONVERT.inv_um_convert
779 (oel.inventory_item_id,
780 5, -- bugfix 2204376: pass precision of 5
781 oel.ordered_quantity,
782 oel.order_quantity_uom,
783 msi.primary_uom_code,
784 null,
785 null) = (select nvl(sum(wdd1.requested_quantity), 0) -- bugfix 2017099
786 --end bugfix 2074290
787 from WSH_DELIVERY_DETAILS_OB_GRP_V wdd1
788 where wdd1.source_line_id = oel.line_id
789 and wdd1.source_code = 'OE') --bugfix 1988967
790 and rownum = 1;
791
792 EXCEPTION
793 when NO_DATA_FOUND then
794 IF PG_DEBUG <> 0 THEN
795 oe_debug_pub.add('Delivery_Planned: ' || 'Delivery lines HAVE NOT BEEN IMPORTED for order line '||to_char(p_line_id), 2);
796 END IF;
797 RAISE Delivery_Not_Planned;
798
799 END; /* Block checking if delivery lines are imported*/
800
801
802 --
803 -- Verify that deliveries have been assigned to all delivery lines
804 --
805
806 BEGIN
807
808 select 'NOTASSIGNED'
809 into l_assigned
810 from oe_order_lines_all oel,
811 WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
812 wsh_delivery_assignments wda
813 where oel.line_id = p_line_id
814 and wdd.source_line_id = oel.line_id
815 and wdd.source_code = 'OE' --bugfix 1988967
816 --and oel.ordered_quantity - nvl(oel.cancelled_quantity, 0) = (select nvl(sum(wdd1.requested_quantity), 0)
817 --from wsh_delivery_details wdd1
818 --where wdd1.source_line_id = oel.line_id)
819 and wda.delivery_detail_id = wdd.delivery_detail_id
820 and wda.delivery_id is null
821 and rownum = 1;
822
823 IF l_assigned = 'NOTASSIGNED' THEN
824 IF PG_DEBUG <> 0 THEN
825 oe_debug_pub.add('Delivery_Planned: ' || 'Deliveries have NOT BEEN ASSIGNED for line '||to_char(p_line_id), 1);
826 END IF;
827 RAISE Delivery_Not_Planned;
828 END IF; /* if delivery not assigned for any delivery line*/
829
830 EXCEPTION
831 when no_data_found then
832 IF PG_DEBUG <> 0 THEN
833 oe_debug_pub.add('Delivery_Planned: ' || 'Deliveries HAVE BEEN ASSIGNED for all delivery lines for order line '||to_char(p_line_id), 2);
834 END IF;
835
836 END; /* Block checking for delivery assignments*/
837
838 --
839 -- Deliveries have been assigned for all delivery lines.
840 -- Check if all deliveries are planned
841 --
842
843 BEGIN
844
845 select 'NOTPLANNED'
846 into l_planned
847 from oe_order_lines_all oel,
848 WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
849 wsh_delivery_assignments wda,
850 WSH_NEW_DELIVERIES_OB_GRP_V wnd
851 where oel.line_id = p_line_id
852 and wdd.source_line_id = oel.line_id
853 and wdd.source_code = 'OE' --bugfix 1988967
854 and wda.delivery_detail_id = wdd.delivery_detail_id
855 and wda.delivery_id = wnd.delivery_id
856 and nvl(wnd.planned_flag,'N') = 'N'
857 and rownum=1;
858
859 IF l_planned = 'NOTPLANNED' THEN
860 IF PG_DEBUG <> 0 THEN
861 oe_debug_pub.add('Delivery_Planned: ' || 'Deliveries have been assigned, but NOT PLANNED yet for line '||to_char(p_line_id), 1);
862 END IF;
863 RAISE Delivery_Not_Planned;
864 END IF; /* if delivery not planned*/
865
866 EXCEPTION
867 when NO_DATA_FOUND then
868 IF PG_DEBUG <> 0 THEN
869 oe_debug_pub.add('Delivery_Planned: ' || 'Deliveries HAVE BEEN PLANNED for order line '||to_char(p_line_id), 2);
870 END IF;
871
872 END; /* Block checking if deliveries planned */
873
874 --
875 -- Deliveries have been assigned and planned
876 --
877
878 x_return_status := FND_API.G_RET_STS_SUCCESS;
879 x_result_out := FND_API.G_TRUE;
880
881 EXCEPTION
882 when DELIVERY_NOT_PLANNED then
883 x_return_status := FND_API.G_RET_STS_SUCCESS;
884 x_result_out := FND_API.G_FALSE;
885
886 when FND_API.G_EXC_ERROR then
887 IF PG_DEBUG <> 0 THEN
888 oe_debug_pub.add('Delivery_Planned: ' || 'CTO_WIP_UTIL.Delivery_Planned::exp error', 1);
889 END IF;
890 x_return_status := FND_API.G_RET_STS_ERROR;
891 CTO_MSG_PUB.Count_And_Get
892 (p_msg_count => x_msg_count
893 ,p_msg_data => x_msg_data
894 );
895
896 when FND_API.G_EXC_UNEXPECTED_ERROR then
897 IF PG_DEBUG <> 0 THEN
898 oe_debug_pub.add('Delivery_Planned: ' || 'CTO_WIP_UTIL.Delivery_Planned::unexp error', 1);
899 END IF;
900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
901 CTO_MSG_PUB.Count_And_Get
902 (p_msg_count => x_msg_count
903 ,p_msg_data => x_msg_data
904 );
905
906 when OTHERS then
907 IF PG_DEBUG <> 0 THEN
908 oe_debug_pub.add('Delivery_Planned: ' || 'CTO_WIP_UTIL.Delivery_Planned::others', 1);
909 END IF;
910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
911 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
912 then
913 FND_MSG_PUB.Add_Exc_Msg
914 ( 'CTO_WIP_UTIL'
915 , 'Delivery_Planned'
916 );
917 end if;
918 CTO_MSG_PUB.Count_And_Get
919 (p_msg_count => x_msg_count
920 ,p_msg_data => x_msg_data
921 );
922
923 END Delivery_Planned;
924
925
926
927 end CTO_WIP_UTIL;