[Home] [Help]
PACKAGE BODY: APPS.MSC_SATP_FUNC
Source
1 PACKAGE BODY MSC_SATP_FUNC AS
2 /* $Header: MSCSATPB.pls 120.5.12010000.2 2009/08/24 06:58:11 sbnaik ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_SATP_FUNC';
4 G_INV_CTP NUMBER := FND_PROFILE.value('INV_CTP');
5
6
7 --Following Functions Are used for calculating delivery lead time on the source
8
9 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
10
11 FUNCTION src_location_id(
12 p_organization_id IN NUMBER,
13 p_customer_id IN NUMBER,
14 p_customer_site_id IN NUMBER
15 )
16 RETURN NUMBER IS
17 L_location_id NUMBER;
18 BEGIN
19
20 IF (p_organization_id IS NOT NULL) THEN
21 -- bug 2974334. Change the SQL into static.
22 SELECT location_id
23 into l_location_id
24 --bug 3346564
25 --from HR_ORGANIZATION_UNITS
26 from HR_ALL_ORGANIZATION_UNITS
27 where organization_id = p_organization_id;
28
29 ELSIF (p_customer_id IS NOT NULL and p_customer_site_id IS NOT NULL) THEN
30 -- Bug 2793404, Bug discovered by Sony
31 -- bug 2974334. Change the SQL into static.
32 select location_id
33 into l_location_id
34 from PO_LOCATION_ASSOCIATIONS
35 where SITE_USE_ID = p_customer_site_id;
36
37 -- End Bug 2793404
38
39 END IF;
40 return l_location_id;
41 EXCEPTION
42 WHEN NO_DATA_FOUND THEN
43 RETURN null;
44 END src_location_id;
45
46 -- savirine added parameters p_session_id and p_partner_site_id on Sep 24, 2001.
47
48 FUNCTION src_interloc_transit_time (
49 p_from_location_id IN NUMBER,
50 p_to_location_id IN NUMBER,
51 p_ship_method IN VARCHAR2,
52 p_session_id IN NUMBER := NULL,
53 p_partner_site_id IN NUMBER := NULL)
54 return NUMBER IS
55
56 l_intransit_time NUMBER;
57 l_level NUMBER;
58
59 BEGIN
60
61 BEGIN
62 select intransit_time
63 into l_intransit_time
64 from mtl_interorg_ship_methods
65 where from_location_id = p_from_location_id
66 and to_location_id = p_to_location_id
67 and ship_method = p_ship_method
68 and rownum = 1;
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN
71
72 -- ngoel 9/25/2001, need to select most specific lead time based on regions
73 -- bug 2974334. Change the SQL into static.
74 SELECT intransit_time,
75 ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
76 INTO l_intransit_time, l_level
77 FROM mtl_interorg_ship_methods mism,
78 msc_regions_temp mrt
79 WHERE mism.from_location_id = p_from_location_id
80 AND mism.ship_method = p_ship_method
81 AND mism.to_region_id = mrt.region_id
82 AND mrt.session_id = p_session_id
83 AND mrt.partner_site_id = p_partner_site_id
84 ORDER BY 2;
85
86 END;
87 return l_intransit_time;
88 EXCEPTION
89 WHEN NO_DATA_FOUND THEN
90 return null;
91 END src_interloc_transit_time;
92
93 -- savirine added parameters p_session_id and p_partner_site_id on Sep 24, 2001.
94
95 FUNCTION src_default_ship_method (
96 p_from_location_id IN NUMBER,
97 p_to_location_id IN NUMBER,
98 p_session_id IN NUMBER := NULL,
99 p_partner_site_id IN NUMBER := NULL)
100 return VARCHAR2 IS
101 l_ship_method VARCHAR2(204);
102 l_level NUMBER;
103 BEGIN
104 BEGIN
105 SELECT ship_method
106 INTO l_ship_method
107 FROM mtl_interorg_ship_methods
108 WHERE from_location_id = p_from_location_id
109 AND to_location_id = p_to_location_id
110 AND default_flag = 1
111 AND rownum = 1;
112 EXCEPTION
113 WHEN NO_DATA_FOUND THEN
114
115 -- ngoel 9/25/2001, need to select most specific ship method based on regions.
116 -- bug 2974334. Change the SQL into static.
117 SELECT ship_method,
118 ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
119 INTO l_ship_method, l_level
120 FROM mtl_interorg_ship_methods mism,
121 msc_regions_temp mrt
122 WHERE mism.from_location_id = p_from_location_id
123 AND mism.to_region_id = mrt.region_id
124 AND mrt.session_id = p_session_id
125 AND mrt.partner_site_id = p_partner_site_id
126 AND default_flag = 1
127 ORDER BY 2;
128
129 END;
130 return l_ship_method;
131 EXCEPTION
132 WHEN NO_DATA_FOUND THEN
133 return null;
134 END src_default_ship_method;
135
136
137 FUNCTION src_ship_method (
138 p_from_org_id IN NUMBER,
139 p_to_org_id IN NUMBER
140 )
141 return VARCHAR2 IS
142 l_ship_method VARCHAR2(30);
143 BEGIN
144 -- bug 2974334. Change the SQL into static.
145 select ship_method
146 into l_ship_method
147 from mtl_interorg_ship_methods
148 where from_organization_id = p_from_org_id
149 and to_organization_id = p_to_org_id
150 and default_flag = 1
151 and rownum = 1;
152
153 return l_ship_method;
154 EXCEPTION
155 WHEN NO_DATA_FOUND THEN
156 return null;
157 END src_ship_method;
158
159 -- savirine added parameters p_session_id and p_partner_site_id on Sep 24, 2001.
160
161 FUNCTION src_default_intransit_time(
162 p_from_location_id IN NUMBER,
163 p_to_location_id IN NUMBER,
164 p_session_id IN NUMBER := NULL,
165 p_partner_site_id IN NUMBER := NULL)
166 return NUMBER IS
167 l_intransit_time NUMBER;
168 l_level NUMBER;
169 BEGIN
170 BEGIN
171 SELECT intransit_time
172 INTO l_intransit_time
173 FROM mtl_interorg_ship_methods
174 WHERE from_location_id = p_from_location_id
175 AND to_location_id = p_to_location_id
176 AND default_flag = 1
177 AND rownum = 1;
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180
181 -- ngoel 9/25/2001, need to select most specific lead time based on regions
182 -- bug 2974334. Change the SQL into static.
183 SELECT intransit_time,
184 ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
185 INTO l_intransit_time, l_level
186 FROM mtl_interorg_ship_methods mism,
187 msc_regions_temp mrt
188 WHERE mism.from_location_id = p_from_location_id
189 AND mism.default_flag = 1
190 AND mism.to_region_id = mrt.region_id
191 AND mrt.session_id = p_session_id
192 AND mrt.partner_site_id = p_partner_site_id
193 ORDER BY 2;
194 END;
195 return l_intransit_time;
196 EXCEPTION
197 WHEN NO_DATA_FOUND THEN
198 return null;
199 END src_default_intransit_time;
200
201 FUNCTION src_intransit_time (
202 p_from_org_id IN NUMBER,
203 p_to_org_id IN NUMBER)
204 return NUMBER IS
205
206 l_intransit_time NUMBER;
207
208 BEGIN
209 -- bug 2974334. Change the SQL into static.
210 select intransit_time
211 into l_intransit_time
212 from mtl_interorg_ship_methods
213 where from_organization_id = p_from_org_id
214 and to_organization_id = p_to_org_id
215 and default_flag = 1
216 and rownum = 1;
217
218 return l_intransit_time;
219
220 EXCEPTION
221 WHEN NO_DATA_FOUND THEN
222 return null;
223 END src_intransit_time;
224
225 FUNCTION src_prev_work_day ( p_organization_id IN NUMBER,
226 p_date IN DATE)
227 return DATE IS
228
229 l_return_date DATE;
230 l_first_work_day DATE; --bug3583705
231 l_last_work_day DATE; --bug3583705
232
233 BEGIN
234 -- Note: Compared to a similar function in MSC_CALENDAR
235 -- the default of daily bucket is used here
236 -- bug 2974334. Change the SQL into static.
237 SELECT cal.prior_date
238 INTO l_return_date
239 FROM bom_calendar_dates cal,
240 mtl_parameters org
241 WHERE cal.calendar_code = org.calendar_code
242 AND cal.exception_set_id = org.calendar_exception_set_id
243 AND cal.calendar_date = TRUNC(p_date)
244 AND org.organization_id = p_organization_id;
245 RETURN l_return_date;
246 EXCEPTION
247 WHEN NO_DATA_FOUND THEN --bug3583705
248 IF MSC_CALENDAR.G_RETAIN_DATE = 'Y' THEN
249 BEGIN
250 SELECT min(calendar_date), max(calendar_date)
251 INTO l_first_work_day, l_last_work_day
252 FROM BOM_CALENDAR_DATES cal,
253 mtl_parameters org
254 WHERE cal.calendar_code = org.calendar_code
255 AND cal.exception_set_id = org.calendar_exception_set_id
256 AND org.organization_id = p_organization_id
257 AND cal.seq_num is not null;
258 EXCEPTION
259 WHEN NO_DATA_FOUND THEN
260 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
261 END;
262
263 IF p_date >= l_last_work_day THEN
264 l_return_date := l_last_work_day;
265 ELSIF p_date <= l_first_work_day THEN
266 l_return_date := l_first_work_day;
267 ELSE
268 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
269 END IF;
270 ELSE
271 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
272 APP_EXCEPTION.RAISE_EXCEPTION;
273 END IF;
274 RETURN l_return_date;
275 END src_prev_work_day;
276
277 FUNCTION src_next_work_day ( p_organization_id IN NUMBER,
278 p_date IN DATE)
279 return DATE IS
280
281 l_return_date DATE;
282 l_first_work_day DATE; --bug3583705
283 l_last_work_day DATE; --bug3583705
284 BEGIN
285 -- Note: Compared to a similar function in MSC_CALENDAR
286 -- the default of daily bucket is used here.
287
288
289 -- bug 2974334. Change the SQL into static.
290 SELECT cal.next_date
291 INTO l_return_date
292 FROM bom_calendar_dates cal,
293 mtl_parameters org
294 WHERE cal.calendar_code = org.calendar_code
295 AND cal.exception_set_id = org.calendar_exception_set_id
296 AND cal.calendar_date = TRUNC(p_date)
297 AND org.organization_id = p_organization_id;
298
299 RETURN l_return_date;
300 EXCEPTION
301 WHEN NO_DATA_FOUND THEN --bug3583705
302 IF MSC_CALENDAR.G_RETAIN_DATE = 'Y' THEN
303 BEGIN
304 SELECT min(calendar_date), max(calendar_date)
305 INTO l_first_work_day, l_last_work_day
306 FROM BOM_CALENDAR_DATES cal,
307 mtl_parameters org
308 WHERE cal.calendar_code = org.calendar_code
309 AND cal.exception_set_id = org.calendar_exception_set_id
310 AND org.organization_id = p_organization_id
311 AND cal.seq_num is not null;
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
315 END;
316
317 IF p_date >= l_last_work_day THEN
318 l_return_date := l_last_work_day;
319 ELSIF p_date <= l_first_work_day THEN
320 l_return_date := l_first_work_day;
321 ELSE
322 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
323 END IF;
324 ELSE
325 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
326 APP_EXCEPTION.RAISE_EXCEPTION;
327 END IF;
328 RETURN l_return_date;
329 END src_next_work_day;
330
331 -- dsting 2833417
332 FUNCTION src_date_offset ( p_organization_id IN NUMBER,
333 p_date IN DATE,
334 p_days IN NUMBER
335 )
336 return DATE IS
337
338 l_return_date DATE;
339 l_days NUMBER;
340 l_first_work_day DATE; --bug3583705
341 l_last_work_day DATE; --bug3583705
342 BEGIN
343 -- Note: Compared to a similar function in MSC_CALENDAR
344 -- the default of daily bucket is used here
345
346
347 IF p_days < 0 THEN
348 l_days := FLOOR(p_days);
349 ELSE
350 l_days := CEIL(p_days);
351 END IF;
352
353 -- bug 2974334. Change the SQL into static. Also combined the 2 SQL's into one.
354 SELECT cal2.calendar_date
355 INTO l_return_date
356 FROM bom_calendar_dates cal1,
357 bom_calendar_dates cal2,
358 mtl_parameters org
359 WHERE cal1.calendar_code = org.calendar_code
360 AND cal1.exception_set_id = org.calendar_exception_set_id
361 AND cal1.calendar_date = TRUNC(p_date)
362 AND org.organization_id = p_organization_id
363 AND cal2.exception_set_id = cal1.exception_set_id
364 AND cal2.calendar_code = cal1.calendar_code
365 AND cal2.seq_num = cal1.prior_seq_num + l_days;
366 RETURN l_return_date;
367
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN --bug3583705
370 IF MSC_CALENDAR.G_RETAIN_DATE = 'Y' THEN
371 BEGIN
372 SELECT min(calendar_date), max(calendar_date)
373 INTO l_first_work_day, l_last_work_day
374 FROM BOM_CALENDAR_DATES cal,
375 mtl_parameters org
376 WHERE cal.calendar_code = org.calendar_code
377 AND cal.exception_set_id = org.calendar_exception_set_id
378 AND org.organization_id = p_organization_id
379 AND cal.seq_num is not null;
380 EXCEPTION
381 WHEN NO_DATA_FOUND THEN
382 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
383 END;
384
385 IF p_date >= l_last_work_day THEN
386 l_return_date := l_last_work_day;
387 ELSIF p_date <= l_first_work_day THEN
388 l_return_date := l_first_work_day;
389 ELSE
390 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
391 END IF;
392 ELSE
393 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
394 APP_EXCEPTION.RAISE_EXCEPTION;
395 END IF;
396 RETURN l_return_date;
397 END src_date_offset;
398
399 -- ngoel 7/31/2001, modified to accept p_index as a parameter to determine
400 -- index length by which ATP_REC_TYP needs to be extended, default is 1.
401
402 PROCEDURE Extend_Atp (
403 p_atp_tab IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
404 x_return_status OUT NoCopy VARCHAR2,
405 p_index IN NUMBER := 1
406 ) IS
407 Begin
408 IF PG_DEBUG in ('Y', 'C') THEN
409 msc_sch_wb.atp_debug('***** Begin Extend_Atp Procedure *****');
410 END IF;
411
412 x_return_status := FND_API.G_RET_STS_SUCCESS;
413 msc_atp_global.extend_atp(p_atp_tab,
414 x_return_status,
415 p_index);
416 /* --s_cto_enhc
417 p_atp_tab.Row_Id.Extend(p_index);
418 p_atp_tab.Instance_Id.Extend(p_index);
419 p_atp_tab.Inventory_Item_Id.Extend(p_index);
420 p_atp_tab.Inventory_Item_Name.Extend(p_index);
421 p_atp_tab.Source_Organization_Id.Extend(p_index);
422 p_atp_tab.Organization_Id.Extend(p_index);
423 p_atp_tab.Source_Organization_Code.Extend(p_index);
424 p_atp_tab.Identifier.Extend(p_index);
425 p_atp_tab.Demand_Source_Header_Id.Extend(p_index);
426 p_atp_tab.Demand_Source_Delivery.Extend(p_index);
427 p_atp_tab.Demand_Source_Type.Extend(p_index);
428 p_atp_tab.Scenario_Id.Extend(p_index);
429 p_atp_tab.Calling_Module.Extend(p_index);
430 p_atp_tab.Customer_Id.Extend(p_index);
431 p_atp_tab.Customer_Site_Id.Extend(p_index);
432 p_atp_tab.Destination_Time_Zone.Extend(p_index);
433 p_atp_tab.Quantity_Ordered.Extend(p_index);
434 p_atp_tab.Quantity_UOM.Extend(p_index);
435 p_atp_tab.Requested_Ship_Date.Extend(p_index);
436 p_atp_tab.Requested_Arrival_Date.Extend(p_index);
437 p_atp_tab.Earliest_Acceptable_Date.Extend(p_index);
438 p_atp_tab.Latest_Acceptable_Date.Extend(p_index);
439 p_atp_tab.Delivery_Lead_Time.Extend(p_index);
440 p_atp_tab.Freight_Carrier.Extend(p_index);
441 p_atp_tab.Ship_Method.Extend(p_index);
442 p_atp_tab.Demand_Class.Extend(p_index);
443 p_atp_tab.Ship_Set_Name.Extend(p_index);
444 p_atp_tab.Arrival_Set_Name.Extend(p_index);
445 p_atp_tab.Override_Flag.Extend(p_index);
446 p_atp_tab.Action.Extend(p_index);
447 p_atp_tab.Ship_Date.Extend(p_index);
448 p_atp_tab.Arrival_Date.Extend(p_index);
449 p_atp_tab.Available_Quantity.Extend(p_index);
450 p_atp_tab.Requested_Date_Quantity.Extend(p_index);
451 p_atp_tab.Group_Ship_Date.Extend(p_index);
452 p_atp_tab.Group_Arrival_Date.Extend(p_index);
453 p_atp_tab.Vendor_Id.Extend(p_index);
454 p_atp_tab.Vendor_Name.Extend(p_index);
455 p_atp_tab.Vendor_Site_Id.Extend(p_index);
456 p_atp_tab.Vendor_Site_Name.Extend(p_index);
457 p_atp_tab.Insert_Flag.Extend(p_index);
458 p_atp_tab.OE_Flag.Extend(p_index);
459 p_atp_tab.Error_Code.Extend(p_index);
460 p_atp_tab.Atp_Lead_Time.Extend(p_index);
461 p_atp_tab.Message.Extend(p_index);
462 p_atp_tab.End_Pegging_Id.Extend(p_index);
463 p_atp_tab.Order_Number.Extend(p_index);
464 p_atp_tab.Old_Source_Organization_Id.Extend(p_index);
465 p_atp_tab.Old_Demand_Class.Extend(p_index);
466 p_atp_tab.ato_delete_flag.Extend(p_index); -- added by ngoel 6/15/2001
467 p_atp_tab.attribute_05.Extend(p_index); -- added by ngoel 7/31/2001
468 p_atp_tab.attribute_06.Extend(p_index); -- added by ngoel 8/09/2001
469 p_atp_tab.attribute_07.Extend(p_index); -- added for bug 2392456
470 p_atp_tab.attribute_01.Extend(p_index); -- added by ngoel 10/12/2001
471 p_atp_tab.customer_name.Extend(p_index); -- added by ngoel 10/12/2001
472 p_atp_tab.customer_class.Extend(p_index); -- added by ngoel 10/12/2001
473 p_atp_tab.customer_location.Extend(p_index); -- added by ngoel 10/12/2001
474 p_atp_tab.customer_country.Extend(p_index); -- added by ngoel 10/12/2001
475 p_atp_tab.customer_state.Extend(p_index); -- added by ngoel 10/12/2001
476 p_atp_tab.customer_city.Extend(p_index); -- added by ngoel 10/12/2001
477 p_atp_tab.customer_postal_code.Extend(p_index); -- added by ngoel 10/12/2001
478
479 --- added for product substitution
480 p_atp_tab.substitution_typ_code.Extend(p_index);
481 p_atp_tab.req_item_detail_flag.Extend(p_index);
482 p_atp_tab.request_item_id.Extend(p_index);
483 p_atp_tab.req_item_req_date_qty.Extend(p_index);
484 p_atp_tab.req_item_available_date.Extend(p_index);
485 p_atp_tab.req_item_available_date_qty.Extend(p_index);
486 p_atp_tab.request_item_name.Extend(p_index);
487 p_atp_tab.old_inventory_item_id.Extend(p_index);
488 p_atp_tab.sales_rep.Extend(p_index);
489 p_atp_tab.customer_contact.Extend(p_index);
490 p_atp_tab.subst_flag.Extend(p_index);
491
492 --diag_atp
493 p_atp_tab.attribute_02.Extend(p_index);
494
495 -- 24x7 Support
496 p_atp_tab.attribute_04.Extend(p_index);
497 p_atp_tab.attribute_08.Extend(p_index); -- 24x7
498 e_cto_ench */
499 -- msc_sch_wb.atp_debug('***** End Extend_Atp Procedure *****');
500
501 END Extend_Atp;
502
503
504 PROCEDURE Assign_Atp_Input_Rec (
505 p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
506 p_index IN NUMBER,
507 x_atp_table IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
508 x_return_status OUT NoCopy VARCHAR2
509 ) IS
510
511 l_count PLS_INTEGER;
512 Begin
513 IF PG_DEBUG in ('Y', 'C') THEN
514 msc_sch_wb.atp_debug('***** Begin Assign_Atp_Input_Rec Procedure *****');
515 END IF;
516
517 x_return_status := FND_API.G_RET_STS_SUCCESS;
518
519 -- copy the p_index th elements in p_atp_table and append it to x_atp_table
520
521 l_count := x_atp_table.Action.COUNT;
522
523 MSC_SATP_FUNC.Extend_Atp(x_atp_table, x_return_status, 1);
524
525 -- only under old OE we will have p_atp_table.Row_Id populated.
526
527 IF p_atp_table.Row_Id.Exists(p_index) THEN
528 x_atp_table.Row_Id(l_count + 1):= p_atp_table.Row_Id(p_index);
529 END IF;
530
531 IF p_atp_table.Instance_Id.Exists(p_index) THEN
532 x_atp_table.Instance_Id(l_count + 1):= p_atp_table.Instance_Id(p_index);
533 END IF;
534
535 x_atp_table.Inventory_Item_Id(l_count + 1):=
536 p_atp_table.Inventory_Item_Id(p_index);
537
538 IF p_atp_table.Inventory_Item_Name.Exists(p_index) THEN
539 x_atp_table.Inventory_Item_Name(l_count + 1):=
540 p_atp_table.Inventory_Item_Name(p_index);
541 END IF;
542
543 IF p_atp_table.Source_Organization_Code.Exists(p_index) THEN
544 x_atp_table.Source_Organization_code(l_count + 1) :=
545 p_atp_table.Source_Organization_code(p_index);
546 END IF;
547 x_atp_table.Source_Organization_Id(l_count + 1):=
548 p_atp_table.Source_Organization_Id(p_index);
549 IF p_atp_table.Organization_Id.Exists(p_index) THEN
550 x_atp_table.Organization_Id(l_count + 1) :=
551 p_atp_table.Organization_Id(p_index);
552 END IF;
553
554 x_atp_table.Identifier(l_count + 1):= p_atp_table.Identifier(p_index);
555 x_atp_table.Customer_Id(l_count + 1):= p_atp_table.Customer_Id(p_index);
556 x_atp_table.Customer_Site_Id(l_count + 1):=
557 p_atp_table.Customer_Site_Id(p_index);
558 x_atp_table.Destination_Time_Zone(l_count + 1):=
559 p_atp_table.Destination_Time_Zone(p_index);
560 x_atp_table.Quantity_Ordered(l_count + 1):=
561 p_atp_table.Quantity_Ordered(p_index);
562 x_atp_table.Quantity_UOM(l_count + 1):=
563 p_atp_table.Quantity_UOM(p_index);
564 x_atp_table.Requested_Ship_Date(l_count + 1):=
565 p_atp_table.Requested_Ship_Date(p_index);
566 x_atp_table.Requested_Arrival_Date(l_count + 1):=
567 p_atp_table.Requested_Arrival_Date(p_index);
568 IF p_atp_table.Earliest_Acceptable_Date.COUNT > 0 THEN
569 x_atp_table.Earliest_Acceptable_Date(l_count + 1):=
570 p_atp_table.Earliest_Acceptable_Date(p_index);
571 END IF;
572 x_atp_table.Latest_Acceptable_Date(l_count + 1):=
573 p_atp_table.Latest_Acceptable_Date(p_index);
574 x_atp_table.Delivery_Lead_Time(l_count + 1):=
575 p_atp_table.Delivery_Lead_Time(p_index);
576 x_atp_table.Freight_Carrier(l_count + 1):=
577 p_atp_table.Freight_Carrier(p_index);
578 x_atp_table.Ship_Method(l_count + 1):=
579 p_atp_table.Ship_Method(p_index);
580 x_atp_table.Demand_Class(l_count + 1):=
581 p_atp_table.Demand_Class(p_index);
582 x_atp_table.Ship_Set_Name(l_count + 1):=
583 p_atp_table.Ship_Set_Name(p_index);
584 x_atp_table.Arrival_Set_Name(l_count + 1):=
585 p_atp_table.Arrival_Set_Name(p_index);
586 x_atp_table.Override_Flag(l_count + 1):=
587 p_atp_table.Override_Flag(p_index);
588 x_atp_table.Action(l_count + 1):= p_atp_table.Action(p_index);
589 x_atp_table.Insert_Flag(l_count + 1):= p_atp_table.Insert_Flag(p_index);
590
591 IF p_atp_table.Calling_Module.Exists(p_index) THEN
592 x_atp_table.Calling_Module(l_count+1) :=
593 p_atp_table.Calling_Module(p_index);
594 END IF;
595
596 IF p_atp_table.Scenario_Id.Exists(p_index) THEN
597 x_atp_table.Scenario_Id(l_count + 1):=
598 p_atp_table.Scenario_Id(p_index);
599 END IF;
600
601 IF p_atp_table.Ship_Date.Exists(p_index) THEN
602 x_atp_table.Ship_Date(l_count+1):=
603 p_atp_table.Ship_Date(p_index);
604 END IF;
605 IF PG_DEBUG in ('Y', 'C') THEN
606 msc_sch_wb.atp_debug('Extend_Atp: ' || 'test1 = '||1);
607 END IF;
608 IF p_atp_table.arrival_date.Exists(p_index) THEN
609 x_atp_table.arrival_Date(l_count+1):=
610 p_atp_table.arrival_date(p_index);
611 END IF;
612
613 IF p_atp_table.Available_Quantity.Exists(p_index) THEN
614 x_atp_table.Available_Quantity(l_count + 1):=
615 p_atp_table.Available_Quantity(p_index);
616 END IF;
617
618 IF p_atp_table.Requested_Date_Quantity.Exists(p_index) THEN
619 x_atp_table.Requested_Date_Quantity(l_count + 1):=
620 p_atp_table.Requested_Date_Quantity(p_index);
621 END IF;
622
623 IF p_atp_table.Group_Ship_Date.Exists(p_index) THEN
624 x_atp_table.Group_Ship_Date(l_count + 1):=
625 p_atp_table.Group_Ship_Date(p_index);
626 END IF;
627
628 IF p_atp_table.Group_Arrival_Date.Exists(p_index) THEN
629 x_atp_table.Group_Arrival_Date(l_count + 1):=
630 p_atp_table.Group_Arrival_Date(p_index);
631 END IF;
632
633 IF p_atp_table.Vendor_Id.Exists(p_index) THEN
634 x_atp_table.Vendor_Id(l_count + 1):=
635 p_atp_table.Vendor_Id(p_index);
636 END IF;
637
638 IF p_atp_table.Vendor_Name.Exists(p_index) THEN
639 x_atp_table.Vendor_Name(l_count + 1):=
640 p_atp_table.Vendor_Name(p_index);
641 END IF;
642
643 IF p_atp_table.Vendor_Site_Id.Exists(p_index) THEN
644 x_atp_table.Vendor_Site_Id(l_count + 1):=
645 p_atp_table.Vendor_Site_Id(p_index);
646 END IF;
647
648 IF p_atp_table.Vendor_Site_Name.Exists(p_index) THEN
649 x_atp_table.Vendor_Site_Name(l_count + 1):=
650 p_atp_table.Vendor_Site_Name(p_index);
651 END IF;
652
653 IF p_atp_table.Error_Code.Exists(p_index) THEN
654 x_atp_table.Error_Code(l_count + 1):=
655 p_atp_table.Error_Code(p_index);
656 END IF;
657
658 IF p_atp_table.Message.Exists(p_index) THEN
659 x_atp_table.Message(l_count + 1):= p_atp_table.Message(p_index);
660 END IF;
661
662 IF p_atp_table.OE_Flag.Exists(p_index) THEN
663 x_atp_table.OE_Flag(l_count + 1):= p_atp_table.OE_Flag(p_index);
664 END IF;
665
666 IF p_atp_table.Atp_Lead_Time.Exists(p_index) THEN
667 x_atp_table.Atp_Lead_Time(l_count + 1):=
668 p_atp_table.Atp_Lead_Time(p_index);
669 END IF;
670
671 IF p_atp_table.Demand_Source_Header_Id.Exists(p_index) THEN
672 x_atp_table.Demand_Source_Header_Id(l_count + 1):=
673 p_atp_table.Demand_Source_Header_Id(p_index);
674 END IF;
675
676 IF p_atp_table.Demand_Source_Delivery.Exists(p_index) THEN
677 x_atp_table.Demand_Source_Delivery(l_count + 1):=
678 p_atp_table.Demand_Source_Delivery(p_index);
679 END IF;
680
681 IF p_atp_table.Demand_Source_Type.Exists(p_index) THEN
682 x_atp_table.Demand_Source_Type(l_count + 1):=
683 p_atp_table.Demand_Source_Type(p_index);
684 END IF;
685
686 IF p_atp_table.End_Pegging_Id.Exists(p_index) THEN
687 x_atp_table.End_Pegging_Id(l_count + 1):=
688 p_atp_table.End_Pegging_Id(p_index);
689 END IF;
690
691 IF p_atp_table.Order_Number.Exists(p_index) THEN
692 x_atp_table.Order_Number(l_count + 1):=
693 p_atp_table.Order_Number(p_index);
694
695 -- 24x7 Bug 2840734
696 if (NVL(MSC_ATP_PVT.G_SYNC_ATP_CHECK,'N') = 'Y') AND
697 (p_atp_table.Order_Number(p_index) is NULL) AND
698 (p_atp_table.attribute_08(p_index) is not NULL) THEN
699
700 IF PG_DEBUG in ('Y', 'C') THEN
701 msc_sch_wb.atp_debug('Calling 24x7 SO Processing');
702 END IF;
703 MSC_ATP_24x7.Parse_Sales_Order_Number (p_atp_table.attribute_08(p_index),
704 x_atp_table.Order_number(l_count + 1)
705 );
706 end if;
707 END IF;
708
709 IF p_atp_table.Old_Source_Organization_Id.Exists(p_index) THEN
710 x_atp_table.Old_Source_Organization_Id(l_count + 1) :=
711 p_atp_table.Old_Source_Organization_Id(p_index);
712 END IF;
713
714 IF p_atp_table.Old_Demand_Class.Exists(p_index) THEN
715 x_atp_table.Old_Demand_Class(l_count + 1):=
716 p_atp_table.Old_Demand_Class(p_index);
717 END IF;
718
719 IF p_atp_table.ato_delete_flag.Exists(p_index) THEN
720 x_atp_table.ato_delete_flag(l_count + 1):=
721 p_atp_table.ato_delete_flag(p_index);
722 END IF;
723
724 IF p_atp_table.attribute_05.Exists(p_index) THEN
725 x_atp_table.attribute_05(l_count + 1):=
726 p_atp_table.attribute_05(p_index);
727 END IF;
728
729 IF p_atp_table.attribute_06.Exists(p_index) THEN
730 x_atp_table.attribute_06(l_count + 1):=
731 p_atp_table.attribute_06(p_index);
732 END IF;
733
734 -- changes for 2392456
735 IF p_atp_table.attribute_07.Exists(p_index) THEN
736 x_atp_table.attribute_07(l_count + 1):=
737 p_atp_table.attribute_07(p_index);
738 END IF;
739 --plan by request date
740 IF p_atp_table.original_request_date.COUNT > 0 THEN
741 x_atp_table.original_request_date(l_count + 1):=
742 p_atp_table.original_request_date(p_index);
743 END IF;
744
745 IF p_atp_table.ship_set_name.COUNT > 0 THEN
746 x_atp_table.ship_set_name(l_count + 1):=
747 p_atp_table.ship_set_name(p_index);
748 END IF;
749
750 IF p_atp_table.arrival_set_name.COUNT > 0 THEN
751 x_atp_table.arrival_set_name(l_count + 1):=
752 p_atp_table.arrival_set_name(p_index);
753 END IF;
754 IF p_atp_table.override_flag.COUNT > 0 THEN
755 x_atp_table.arrival_set_name(l_count + 1):=
756 p_atp_table.arrival_set_name(p_index);
757 END IF;
758 --end of plan by request date change
759 --- subst chnages
760 IF p_atp_table.substitution_typ_code.Exists(p_index) THEN
761 x_atp_table.substitution_typ_code(l_count + 1):=
762 p_atp_table.substitution_typ_code(p_index);
763 END IF;
764
765
766 IF p_atp_table.req_item_detail_flag.Exists(p_index) THEN
767 x_atp_table.req_item_detail_flag(l_count + 1):=
768 p_atp_table.req_item_detail_flag(p_index);
769 END IF;
770
771 IF p_atp_table.request_item_id.Exists(p_index) THEN
772 x_atp_table.request_item_id(l_count + 1):=
773 p_atp_table.request_item_id(p_index);
774 END IF;
775
776 IF p_atp_table.req_item_req_date_qty.Exists(p_index) THEN
777 x_atp_table.req_item_req_date_qty(l_count + 1):=
778 p_atp_table.req_item_req_date_qty(p_index);
779 END IF;
780
781 IF p_atp_table.req_item_available_date.Exists(p_index) THEN
782 x_atp_table.req_item_available_date(l_count + 1):=
783 p_atp_table.req_item_available_date(p_index);
784 END IF;
785
786 IF p_atp_table.req_item_available_date_qty.Exists(p_index) THEN
787 x_atp_table.req_item_available_date_qty(l_count + 1):=
788 p_atp_table.req_item_available_date_qty(p_index);
789 END IF;
790
791 IF p_atp_table.request_item_name.Exists(p_index) THEN
792 x_atp_table.request_item_name(l_count + 1):=
793 p_atp_table.request_item_name(p_index);
794 END IF;
795
796 IF p_atp_table.old_inventory_item_id.Exists(p_index) THEN
797 x_atp_table.old_inventory_item_id(l_count +1) :=
798 p_atp_table.old_inventory_item_id(p_index);
799 END IF;
800
801 IF p_atp_table.sales_rep.Exists(p_index) THEN
802 x_atp_table.sales_rep(l_count +1) :=
803 p_atp_table.sales_rep(p_index);
804 END IF;
805
806 IF p_atp_table.customer_contact.Exists(p_index) THEN
807 x_atp_table.customer_contact(l_count +1) :=
808 p_atp_table.customer_contact(p_index);
809 END IF;
810 IF p_atp_table.subst_flag.Exists(p_index) THEN
811 x_atp_table.subst_flag(l_count +1) :=
812 p_atp_table.subst_flag(p_index);
813 END IF;
814
815 --diag_atp
816 IF p_atp_table.attribute_02.Exists(p_index) THEN
817 x_atp_table.attribute_02(l_count +1) :=
818 p_atp_table.attribute_02(p_index);
819 END IF;
820
821 -- 24x7 Changes
822 IF p_atp_table.attribute_04.Exists(p_index) THEN
823 x_atp_table.attribute_04(l_count + 1):=
824 p_atp_table.attribute_04(p_index);
825 END IF;
826
827 IF p_atp_table.attribute_08.Exists(p_index) THEN
828 x_atp_table.attribute_08(l_count + 1):=
829 p_atp_table.attribute_08(p_index);
830 END IF;
831
832 IF p_atp_table.sequence_number.Exists(p_index) THEN
833 x_atp_table.sequence_number(l_count + 1):=
834 p_atp_table.sequence_number(p_index);
835 END IF;
836
837 ----s_cto_rearch
838 IF p_atp_table.Top_Model_line_id.Exists(p_index) THEN
839 x_atp_table.Top_Model_line_id(l_count + 1):=
840 p_atp_table.Top_Model_line_id(p_index);
841
842 END IF;
843
844 IF PG_DEBUG in ('Y', 'C') THEN
845 msc_sch_wb.atp_debug('Extend_Atp: ' || 'test1 = '||2);
846 END IF;
847 IF p_atp_table.ATO_Parent_Model_Line_Id.Exists(p_index) THEN
848 x_atp_table.ATO_Parent_Model_Line_Id(l_count + 1):=
849 p_atp_table.ATO_Parent_Model_Line_Id(p_index);
850 END IF;
851
852 IF p_atp_table.ATO_Model_Line_Id.Exists(p_index) THEN
853 x_atp_table.ATO_Model_Line_Id(l_count + 1):=
854 p_atp_table.ATO_Model_Line_Id(p_index);
855 END IF;
856
857 IF p_atp_table.Parent_line_id.Exists(p_index) THEN
858 x_atp_table.Parent_line_id(l_count + 1):=
859 p_atp_table.Parent_line_id(p_index);
860 END IF;
861
862 IF p_atp_table.match_item_id.Exists(p_index) THEN
863 x_atp_table.match_item_id(l_count + 1):=
864 p_atp_table.match_item_id(p_index);
865 END IF;
866
867 IF p_atp_table.matched_item_name.Exists(p_index) THEN
868 x_atp_table.matched_item_name(l_count + 1):=
869 p_atp_table.matched_item_name(p_index);
870 END IF;
871
872 IF p_atp_table.Config_item_line_id.Exists(p_index) THEN
873 x_atp_table.Config_item_line_id(l_count + 1):=
874 p_atp_table.Config_item_line_id(p_index);
875 END IF;
876
877 IF p_atp_table.Validation_Org.Exists(p_index) THEN
878 x_atp_table.Validation_Org(l_count + 1):=
879 p_atp_table.Validation_Org(p_index);
880 END IF;
881
882 IF PG_DEBUG in ('Y', 'C') THEN
883 msc_sch_wb.atp_debug('Extend_Atp: ' || 'test1 = '||3);
884 END IF;
885 IF p_atp_table.Component_Sequence_ID.Exists(p_index) THEN
886 x_atp_table.Component_Sequence_ID(l_count + 1):=
887 p_atp_table.Component_Sequence_ID(p_index);
888 END IF;
889
890 IF p_atp_table.Component_Code.Exists(p_index) THEN
891 x_atp_table.Component_Code(l_count + 1):=
892 p_atp_table.Component_Code(p_index);
893 END IF;
894
895 IF p_atp_table.line_number.Exists(p_index) THEN
896 x_atp_table.line_number(l_count + 1):=
897 p_atp_table.line_number(p_index);
898 END IF;
899
900 IF p_atp_table.included_item_flag.Exists(p_index) THEN
901 x_atp_table.included_item_flag(l_count + 1):=
902 p_atp_table.included_item_flag(p_index);
903 END IF;
904
905 IF PG_DEBUG in ('Y', 'C') THEN
906 msc_sch_wb.atp_debug('Extend_Atp: ' || 'test1 = '||4);
907 END IF;
908
909 IF p_atp_table.atp_flag.Exists(p_index) THEN
910 x_atp_table.atp_flag(l_count + 1):=
911 p_atp_table.atp_flag(p_index);
912 END IF;
913
914 IF p_atp_table.atp_components_flag.Exists(p_index) THEN
915 x_atp_table.atp_components_flag(l_count + 1):=
916 p_atp_table.atp_components_flag(p_index);
917 END IF;
918
919 IF p_atp_table.wip_supply_type.Exists(p_index) THEN
920 x_atp_table.wip_supply_type(l_count + 1):=
921 p_atp_table.wip_supply_type(p_index);
922 END IF;
923
924 IF PG_DEBUG in ('Y', 'C') THEN
925 msc_sch_wb.atp_debug('Extend_Atp: ' || 'test1 = '||5);
926 END IF;
927 IF p_atp_table.bom_item_type.Exists(p_index) THEN
928 x_atp_table.bom_item_type(l_count + 1):=
929 p_atp_table.bom_item_type(p_index);
930 END IF;
931
932 IF p_atp_table.mandatory_item_flag.Exists(p_index) THEN
933 x_atp_table.mandatory_item_flag(l_count + 1):=
934 p_atp_table.mandatory_item_flag(p_index);
935 END IF;
936
937
938 IF p_atp_table.attribute_11.Exists(p_index) THEN
939 x_atp_table.attribute_11(l_count + 1):=
940 p_atp_table.attribute_11(p_index);
941 END IF;
942
943 IF p_atp_table.attribute_12.Exists(p_index) THEN
944 x_atp_table.attribute_12(l_count + 1):=
945 p_atp_table.attribute_12(p_index);
946 END IF;
947
948 IF PG_DEBUG in ('Y', 'C') THEN
949 msc_sch_wb.atp_debug('Extend_Atp: ' || 'test1 = '||1);
950 END IF;
951
952 IF p_atp_table.attribute_13.Exists(p_index) THEN
953 x_atp_table.attribute_13(l_count + 1):=
954 p_atp_table.attribute_13(p_index);
955 END IF;
956
957 IF p_atp_table.attribute_14.Exists(p_index) THEN
958 x_atp_table.attribute_14(l_count + 1):=
959 p_atp_table.attribute_14(p_index);
960 END IF;
961
962 IF p_atp_table.attribute_15.Exists(p_index) THEN
963 x_atp_table.attribute_15(l_count + 1):=
964 p_atp_table.attribute_15(p_index);
965 END IF;
966
967 IF p_atp_table.attribute_16.Exists(p_index) THEN
968 x_atp_table.attribute_16(l_count + 1):=
969 p_atp_table.attribute_16(p_index);
970 END IF;
971
972 IF p_atp_table.attribute_17.Exists(p_index) THEN
973 x_atp_table.attribute_17(l_count + 1):=
974 p_atp_table.attribute_17(p_index);
975 END IF;
976
977 IF p_atp_table.attribute_18.Exists(p_index) THEN
978 x_atp_table.attribute_18(l_count + 1):=
979 p_atp_table.attribute_18(p_index);
980 END IF;
981
982 IF p_atp_table.attribute_19.Exists(p_index) THEN
983 x_atp_table.attribute_19(l_count + 1):=
984 p_atp_table.attribute_19(p_index);
985 END IF;
986
987 IF p_atp_table.attribute_20.Exists(p_index) THEN
988 x_atp_table.attribute_20(l_count + 1):=
989 p_atp_table.attribute_20(p_index);
990 END IF;
991
992 IF p_atp_table.Attribute_21.Exists(p_index) THEN
993 x_atp_table.Attribute_21(l_count + 1):=
994 p_atp_table.Attribute_21(p_index);
995 END IF;
996
997 IF p_atp_table.attribute_22.Exists(p_index) THEN
998 x_atp_table.attribute_22(l_count + 1):=
999 p_atp_table.attribute_22(p_index);
1000 END IF;
1001
1002 IF p_atp_table.attribute_23.Exists(p_index) THEN
1003 x_atp_table.attribute_23(l_count + 1):=
1004 p_atp_table.attribute_23(p_index);
1005 END IF;
1006
1007 IF p_atp_table.attribute_24.Exists(p_index) THEN
1008 x_atp_table.attribute_24(l_count + 1):=
1009 p_atp_table.attribute_24(p_index);
1010 END IF;
1011
1012 IF p_atp_table.attribute_25.Exists(p_index) THEN
1013 x_atp_table.attribute_25(l_count + 1):=
1014 p_atp_table.attribute_25(p_index);
1015 END IF;
1016
1017 IF p_atp_table.attribute_26.Exists(p_index) THEN
1018 x_atp_table.attribute_26(l_count + 1):=
1019 p_atp_table.attribute_26(p_index);
1020 END IF;
1021
1022 IF p_atp_table.attribute_27.Exists(p_index) THEN
1023 x_atp_table.attribute_27(l_count + 1):=
1024 p_atp_table.attribute_27(p_index);
1025 END IF;
1026
1027 IF p_atp_table.attribute_28.Exists(p_index) THEN
1028 x_atp_table.attribute_28(l_count + 1):=
1029 p_atp_table.attribute_28(p_index);
1030 END IF;
1031
1032 IF p_atp_table.attribute_29.Exists(p_index) THEN
1033 x_atp_table.attribute_29(l_count + 1):=
1034 p_atp_table.attribute_29(p_index);
1035 END IF;
1036
1037 IF p_atp_table.attribute_30.Exists(p_index) THEN
1038 x_atp_table.attribute_30(l_count + 1):=
1039 p_atp_table.attribute_30(p_index);
1040 END IF;
1041
1042 IF p_atp_table.atf_date.Exists(p_index) THEN
1043 x_atp_table.atf_date(l_count + 1):=
1044 p_atp_table.atf_date(p_index);
1045 END IF;
1046
1047 -- Bug 3449812
1048 IF p_atp_table.internal_org_id.Exists(p_index) THEN
1049 x_atp_table.internal_org_id(l_count + 1):=
1050 p_atp_table.internal_org_id(p_index);
1051 END IF;
1052
1053 --2814895
1054 IF p_atp_table.customer_country.Exists(p_index) THEN
1055 x_atp_table.customer_country(l_count + 1):=
1056 p_atp_table.customer_country(p_index);
1057 END IF;
1058 IF p_atp_table.customer_city.Exists(p_index) THEN
1059 x_atp_table.customer_city(l_count + 1):=
1060 p_atp_table.customer_city(p_index);
1061 END IF;
1062 IF p_atp_table.customer_state.Exists(p_index) THEN
1063 x_atp_table.customer_state(l_count + 1):=
1064 p_atp_table.customer_state(p_index);
1065 END IF;
1066 IF p_atp_table.customer_postal_code.Exists(p_index) THEN
1067 x_atp_table.customer_postal_code(l_count + 1):=
1068 p_atp_table.customer_postal_code(p_index);
1069 END IF;
1070
1071 IF p_atp_table.party_site_id.Exists(p_index) THEN
1072 x_atp_table.party_site_id(l_count + 1):=
1073 p_atp_table.party_site_id(p_index);
1074 END IF;
1075
1076
1077 -- Bug 3328421
1078 IF p_atp_table.first_valid_ship_arrival_date.Exists(p_index) THEN
1079 x_atp_table.first_valid_ship_arrival_date(l_count + 1):=
1080 p_atp_table.first_valid_ship_arrival_date(p_index);
1081 END IF;
1082 --Bug 4500382
1083 IF p_atp_table.part_of_set.Exists(p_index) THEN
1084 x_atp_table.part_of_set(l_count + 1):=
1085 p_atp_table.part_of_set(p_index);
1086 --4500382 ENDS
1087 END IF;
1088 ---e_cto_rearch
1089 IF PG_DEBUG in ('Y', 'C') THEN
1090 msc_sch_wb.atp_debug('***** End Assign_Atp_Input_Rec Procedure *****');
1091 END IF;
1092 END Assign_Atp_Input_Rec;
1093
1094
1095 PROCEDURE Assign_Atp_Output_Rec (
1096 p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
1097 x_atp_table IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
1098 x_return_status OUT NoCopy VARCHAR2
1099 ) IS
1100
1101 l_atp_count PLS_INTEGER;
1102 l_count PLS_INTEGER;
1103 Begin
1104
1105 IF PG_DEBUG in ('Y', 'C') THEN
1106 msc_sch_wb.atp_debug('***** Begin Assign_Atp_Output_Rec Procedure *****');
1107 END IF;
1108
1109 x_return_status := FND_API.G_RET_STS_SUCCESS;
1110
1111 -- append the p_atp_table to x_atp_tablecopy
1112
1113 l_count := x_atp_table.Action.COUNT;
1114
1115 IF nvl(l_count, 0) > 0 THEN
1116 IF PG_DEBUG in ('Y', 'C') THEN
1117 msc_sch_wb.atp_debug('Assign_Atp_Output_Rec: ' || 'x_atp_table.Action : ' || x_atp_table.Action(l_count));
1118 END IF;
1119 END IF;
1120
1121 FOR l_atp_count in 1..p_atp_table.Action.COUNT LOOP
1122
1123 MSC_SATP_FUNC.Extend_Atp(x_atp_table, x_return_status, 1);
1124
1125 x_atp_table.Row_Id(l_count + l_atp_count):= p_atp_table.Row_Id(l_atp_count);
1126 x_atp_table.Instance_Id(l_count + l_atp_count):=
1127 p_atp_table.Instance_Id(l_atp_count);
1128 x_atp_table.Inventory_Item_Id(l_count + l_atp_count):=
1129 p_atp_table.Inventory_Item_Id(l_atp_count);
1130 x_atp_table.Inventory_Item_Name(l_count + l_atp_count):=
1131 p_atp_table.Inventory_Item_Name(l_atp_count);
1132 x_atp_table.Source_Organization_Id(l_count + l_atp_count):=
1133 p_atp_table.Source_Organization_Id(l_atp_count);
1134 x_atp_table.Organization_Id(l_count + l_atp_count):=
1135 p_atp_table.Organization_Id(l_atp_count);
1136 x_atp_table.Source_Organization_Code(l_count + l_atp_count):=
1137 p_atp_table.Source_Organization_Code(l_atp_count);
1138 x_atp_table.Identifier(l_count + l_atp_count):=
1139 p_atp_table.Identifier(l_atp_count);
1140 x_atp_table.Calling_Module(l_count+l_atp_count) :=
1141 p_atp_table.Calling_Module(l_atp_count);
1142 x_atp_table.Scenario_Id(l_count + l_atp_count):=
1143 p_atp_table.Scenario_Id(l_atp_count);
1144 x_atp_table.Customer_Id(l_count + l_atp_count):=
1145 p_atp_table.Customer_Id(l_atp_count);
1146 x_atp_table.Customer_Site_Id(l_count + l_atp_count):=
1147 p_atp_table.Customer_Site_Id(l_atp_count);
1148 x_atp_table.Destination_Time_Zone(l_count + l_atp_count):=
1149 p_atp_table.Destination_Time_Zone(l_atp_count);
1150 x_atp_table.Quantity_Ordered(l_count + l_atp_count):=
1151 p_atp_table.Quantity_Ordered(l_atp_count);
1152 x_atp_table.Quantity_UOM(l_count + l_atp_count):=
1153 p_atp_table.Quantity_UOM(l_atp_count);
1154 x_atp_table.Requested_Ship_Date(l_count + l_atp_count):=
1155 p_atp_table.Requested_Ship_Date(l_atp_count);
1156 x_atp_table.Requested_Arrival_Date(l_count + l_atp_count):=
1157 p_atp_table.Requested_Arrival_Date(l_atp_count);
1158 x_atp_table.Earliest_Acceptable_Date(l_count + l_atp_count):=
1159 p_atp_table.Earliest_Acceptable_Date(l_atp_count);
1160 x_atp_table.Latest_Acceptable_Date(l_count + l_atp_count):=
1161 p_atp_table.Latest_Acceptable_Date(l_atp_count);
1162 x_atp_table.Delivery_Lead_Time(l_count + l_atp_count):=
1163 p_atp_table.Delivery_Lead_Time(l_atp_count);
1164 x_atp_table.Freight_Carrier(l_count + l_atp_count):=
1165 p_atp_table.Freight_Carrier(l_atp_count);
1166 x_atp_table.Ship_Method(l_count + l_atp_count):=
1167 p_atp_table.Ship_Method(l_atp_count);
1168 x_atp_table.Demand_Class(l_count + l_atp_count):=
1169 p_atp_table.Demand_Class(l_atp_count);
1170 x_atp_table.Ship_Set_Name(l_count + l_atp_count):=
1171 p_atp_table.Ship_Set_Name(l_atp_count);
1172 x_atp_table.Arrival_Set_Name(l_count + l_atp_count):=
1173 p_atp_table.Arrival_Set_Name(l_atp_count);
1174 x_atp_table.Override_Flag(l_count + l_atp_count):=
1175 p_atp_table.Override_Flag(l_atp_count);
1176 x_atp_table.Action(l_count + l_atp_count):= p_atp_table.Action(l_atp_count);
1177 x_atp_table.Ship_Date(l_count + l_atp_count):=
1178 p_atp_table.Ship_Date(l_atp_count);
1179 x_atp_table.Arrival_Date(l_count + l_atp_count):=
1180 p_atp_table.Arrival_Date(l_atp_count);
1181 x_atp_table.Available_Quantity(l_count + l_atp_count):=
1182 p_atp_table.Available_Quantity(l_atp_count);
1183 x_atp_table.Requested_Date_Quantity(l_count + l_atp_count):=
1184 p_atp_table.Requested_Date_Quantity(l_atp_count);
1185 x_atp_table.Group_Ship_Date(l_count + l_atp_count):=
1186 p_atp_table.Group_Ship_Date(l_atp_count);
1187 x_atp_table.Group_Arrival_Date(l_count + l_atp_count):=
1188 p_atp_table.Group_Arrival_Date(l_atp_count);
1189 x_atp_table.Vendor_Id(l_count + l_atp_count):=
1190 p_atp_table.Vendor_Id(l_atp_count);
1191 x_atp_table.Vendor_Name(l_count + l_atp_count):=
1192 p_atp_table.Vendor_Name(l_atp_count);
1193 x_atp_table.Vendor_Site_Id(l_count + l_atp_count):=
1194 p_atp_table.Vendor_Site_Id(l_atp_count);
1195 x_atp_table.Vendor_Site_Name(l_count + l_atp_count):=
1196 p_atp_table.Vendor_Site_Name(l_atp_count);
1197 x_atp_table.Insert_Flag(l_count + l_atp_count):=
1198 p_atp_table.Insert_Flag(l_atp_count);
1199 x_atp_table.Error_Code(l_count + l_atp_count):=
1200 p_atp_table.Error_Code(l_atp_count);
1201 x_atp_table.Message(l_count + l_atp_count):=
1202 p_atp_table.Message(l_atp_count);
1203 x_atp_table.OE_Flag(l_count + l_atp_count):=
1204 p_atp_table.OE_Flag(l_atp_count);
1205 x_atp_table.Atp_Lead_Time(l_count + l_atp_count):=
1206 p_atp_table.Atp_Lead_Time(l_atp_count);
1207 x_atp_table.Demand_Source_Header_Id(l_count + l_atp_count):=
1208 p_atp_table.Demand_Source_Header_Id(l_atp_count);
1209 x_atp_table.Demand_Source_Delivery(l_count + l_atp_count):=
1210 p_atp_table.Demand_Source_Delivery(l_atp_count);
1211 x_atp_table.Demand_Source_Type(l_count + l_atp_count):=
1212 p_atp_table.Demand_Source_Type(l_atp_count);
1213 x_atp_table.End_Pegging_Id(l_count + l_atp_count):=
1214 p_atp_table.End_Pegging_Id(l_atp_count);
1215 x_atp_table.Order_Number(l_count + l_atp_count):=
1216 p_atp_table.Order_Number(l_atp_count);
1217 x_atp_table.Old_Source_Organization_Id(l_count + l_atp_count):=
1218 p_atp_table.Old_Source_Organization_Id(l_atp_count);
1219 x_atp_table.Old_Demand_Class(l_count + l_atp_count):=
1220 p_atp_table.Old_Demand_Class(l_atp_count);
1221
1222 x_atp_table.ato_delete_flag(l_count + l_atp_count):=
1223 p_atp_table.ato_delete_flag(l_atp_count);
1224 x_atp_table.attribute_05(l_count + l_atp_count):=
1225 p_atp_table.attribute_05(l_atp_count);
1226 x_atp_table.attribute_06(l_count + l_atp_count):=
1227 p_atp_table.attribute_06(l_atp_count);
1228
1229 -- changes for bug 2392456
1230 x_atp_table.attribute_07(l_count + l_atp_count):=
1231 p_atp_table.attribute_07(l_atp_count);
1232
1233 --- product substitution changes
1234
1235 x_atp_table.substitution_typ_code(l_count + l_atp_count):=
1236 p_atp_table.substitution_typ_code(l_atp_count);
1237
1238 x_atp_table.req_item_detail_flag(l_count + l_atp_count):=
1239 p_atp_table.req_item_detail_flag(l_atp_count);
1240
1241 x_atp_table.request_item_id(l_count + l_atp_count):=
1242 p_atp_table.request_item_id(l_atp_count);
1243
1244 x_atp_table.req_item_req_date_qty(l_count + l_atp_count):=
1245 p_atp_table.req_item_req_date_qty(l_atp_count);
1246
1247 x_atp_table.req_item_available_date(l_count + l_atp_count):=
1248 p_atp_table.req_item_available_date(l_atp_count);
1249
1250 x_atp_table.req_item_available_date_qty(l_count + l_atp_count):=
1251 p_atp_table.req_item_available_date_qty(l_atp_count);
1252
1253 x_atp_table.request_item_name(l_count + l_atp_count):=
1254 p_atp_table.request_item_name(l_atp_count);
1255
1256 x_atp_table.old_inventory_item_id(l_count + l_atp_count):=
1257 p_atp_table.old_inventory_item_id(l_atp_count);
1258
1259 x_atp_table.sales_rep(l_count + l_atp_count):=
1260 p_atp_table.sales_rep(l_atp_count);
1261
1262 x_atp_table.customer_contact(l_count + l_atp_count):=
1263 p_atp_table.customer_contact(l_atp_count);
1264
1265 x_atp_table.subst_flag(l_count + l_atp_count):=
1266 p_atp_table.subst_flag(l_atp_count);
1267
1268 --diag_atp
1269
1270 x_atp_table.attribute_02(l_count + l_atp_count):=
1271 p_atp_table.attribute_02(l_atp_count);
1272
1273 -- 24x7 ATP
1274 x_atp_table.attribute_04(l_count + l_atp_count):=
1275 p_atp_table.attribute_04(l_atp_count);
1276
1277 x_atp_table.attribute_08(l_count + l_atp_count):=
1278 p_atp_table.attribute_08(l_atp_count);
1279
1280
1281 x_atp_table.sequence_number(l_count + l_atp_count):=
1282 p_atp_table.sequence_number(l_atp_count);
1283 ----s_cto_rearch
1284 x_atp_table.Top_Model_line_id(l_count + l_atp_count):=
1285 p_atp_table.Top_Model_line_id(l_atp_count);
1286
1287
1288 x_atp_table.ATO_Parent_Model_Line_Id(l_count + l_atp_count):=
1289 p_atp_table.ATO_Parent_Model_Line_Id(l_atp_count);
1290
1291 x_atp_table.ATO_Model_Line_Id(l_count + l_atp_count):=
1292 p_atp_table.ATO_Model_Line_Id(l_atp_count);
1293
1294 x_atp_table.Parent_line_id(l_count + l_atp_count):=
1295 p_atp_table.Parent_line_id(l_atp_count);
1296
1297 x_atp_table.match_item_id(l_count + l_atp_count):=
1298 p_atp_table.match_item_id(l_atp_count);
1299
1300 x_atp_table.matched_item_name(l_count + l_atp_count):=
1301 p_atp_table.matched_item_name(l_atp_count);
1302
1303 x_atp_table.Config_item_line_id(l_count + l_atp_count):=
1304 p_atp_table.Config_item_line_id(l_atp_count);
1305
1306 x_atp_table.Validation_Org(l_count + l_atp_count):=
1307 p_atp_table.Validation_Org(l_atp_count);
1308
1309 x_atp_table.Component_Sequence_ID(l_count + l_atp_count):=
1310 p_atp_table.Component_Sequence_ID(l_atp_count);
1311
1312 x_atp_table.Component_Code(l_count + l_atp_count):=
1313 p_atp_table.Component_Code(l_atp_count);
1314
1315 x_atp_table.line_number(l_count + l_atp_count):=
1316 p_atp_table.line_number(l_atp_count);
1317
1318 x_atp_table.included_item_flag(l_count + l_atp_count):=
1319 p_atp_table.included_item_flag(l_atp_count);
1320
1321 x_atp_table.included_item_flag(l_count + l_atp_count):=
1322 p_atp_table.included_item_flag(l_atp_count);
1323
1324 x_atp_table.atp_flag(l_count + l_atp_count):=
1325 p_atp_table.atp_flag(l_atp_count);
1326
1327 x_atp_table.atp_components_flag(l_count + l_atp_count):=
1328 p_atp_table.atp_components_flag(l_atp_count);
1329
1330 x_atp_table.wip_supply_type(l_count + l_atp_count):=
1331 p_atp_table.wip_supply_type(l_atp_count);
1332
1333 x_atp_table.bom_item_type(l_count + l_atp_count):=
1334 p_atp_table.bom_item_type(l_atp_count);
1335
1336 x_atp_table.mandatory_item_flag(l_count + l_atp_count):=
1337 p_atp_table.mandatory_item_flag(l_atp_count);
1338
1339 x_atp_table.mandatory_item_flag(l_count + l_atp_count):=
1340 p_atp_table.mandatory_item_flag(l_atp_count);
1341
1342 x_atp_table.attribute_11(l_count + l_atp_count):=
1343 p_atp_table.attribute_11(l_atp_count);
1344
1345 x_atp_table.attribute_12(l_count + l_atp_count):=
1346 p_atp_table.attribute_12(l_atp_count);
1347
1348
1349 x_atp_table.attribute_13(l_count + l_atp_count):=
1350 p_atp_table.attribute_13(l_atp_count);
1351
1352 x_atp_table.attribute_14(l_count + l_atp_count):=
1353 p_atp_table.attribute_14(l_atp_count);
1354
1355 x_atp_table.attribute_15(l_count + l_atp_count):=
1356 p_atp_table.attribute_15(l_atp_count);
1357
1358 x_atp_table.attribute_16(l_count + l_atp_count):=
1359 p_atp_table.attribute_16(l_atp_count);
1360
1361 x_atp_table.attribute_17(l_count + l_atp_count):=
1362 p_atp_table.attribute_17(l_atp_count);
1363
1364 x_atp_table.attribute_18(l_count + l_atp_count):=
1365 p_atp_table.attribute_18(l_atp_count);
1366
1367 x_atp_table.attribute_19(l_count + l_atp_count):=
1368 p_atp_table.attribute_19(l_atp_count);
1369
1370 x_atp_table.attribute_20(l_count + l_atp_count):=
1371 p_atp_table.attribute_20(l_atp_count);
1372
1373 x_atp_table.Attribute_21(l_count + l_atp_count):=
1374 p_atp_table.Attribute_21(l_atp_count);
1375
1376 x_atp_table.attribute_22(l_count + l_atp_count):=
1377 p_atp_table.attribute_22(l_atp_count);
1378
1379 x_atp_table.attribute_23(l_count + l_atp_count):=
1380 p_atp_table.attribute_23(l_atp_count);
1381
1382 x_atp_table.attribute_24(l_count + l_atp_count):=
1383 p_atp_table.attribute_24(l_atp_count);
1384
1385 x_atp_table.attribute_25(l_count + l_atp_count):=
1386 p_atp_table.attribute_25(l_atp_count);
1387
1388 x_atp_table.attribute_26(l_count + l_atp_count):=
1389 p_atp_table.attribute_26(l_atp_count);
1390
1391 x_atp_table.attribute_27(l_count + l_atp_count):=
1392 p_atp_table.attribute_27(l_atp_count);
1393
1394 x_atp_table.attribute_28(l_count + l_atp_count):=
1395 p_atp_table.attribute_28(l_atp_count);
1396
1397 x_atp_table.attribute_29(l_count + l_atp_count):=
1398 p_atp_table.attribute_29(l_atp_count);
1399
1400 x_atp_table.attribute_30(l_count + l_atp_count):=
1401 p_atp_table.attribute_30(l_atp_count);
1402
1403 x_atp_table.atf_date(l_count + l_atp_count):=
1404 p_atp_table.atf_date(l_atp_count);
1405
1406 ---e_cto_rearch
1407
1408 -- ship_rec_cal changes begin
1409 x_atp_table.receiving_cal_code(l_count + l_atp_count):=
1410 p_atp_table.receiving_cal_code(l_atp_count);
1411
1412 x_atp_table.intransit_cal_code(l_count + l_atp_count):=
1413 p_atp_table.intransit_cal_code(l_atp_count);
1414
1415 x_atp_table.shipping_cal_code(l_count + l_atp_count):=
1416 p_atp_table.shipping_cal_code(l_atp_count);
1417
1418 x_atp_table.manufacturing_cal_code(l_count + l_atp_count):=
1419 p_atp_table.manufacturing_cal_code(l_atp_count);
1420
1421 x_atp_table.plan_id(l_count + l_atp_count):=
1422 p_atp_table.plan_id(l_atp_count);
1423 -- ship_rec_cal changes end
1424
1425 -- Bug 3449812
1426 x_atp_table.internal_org_id(l_count + l_atp_count):=
1427 p_atp_table.internal_org_id(l_atp_count);
1428
1429
1430 -- Bug 3328421
1431 x_atp_table.first_valid_ship_arrival_date(l_count + l_atp_count):=
1432 p_atp_table.first_valid_ship_arrival_date(l_atp_count);
1433
1434 x_atp_table.part_of_set(l_count + l_atp_count):=
1435 p_atp_table.part_of_set(l_atp_count);
1436 END LOOP;
1437
1438 IF PG_DEBUG in ('Y', 'C') THEN
1439 msc_sch_wb.atp_debug('***** End Assign_Atp_Output_Rec Procedure *****');
1440 END IF;
1441
1442 END Assign_Atp_Output_Rec;
1443
1444
1445 PROCEDURE Extend_Atp_Period (
1446 p_atp_period IN OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
1447 x_return_status OUT NoCopy VARCHAR2
1448 ) IS
1449
1450 Begin
1451
1452 -- msc_sch_wb.atp_debug('***** Begin Extend_Atp_Period Procedure *****');
1453
1454 x_return_status := FND_API.G_RET_STS_SUCCESS;
1455
1456 p_atp_period.Level.Extend;
1457 p_atp_period.Inventory_Item_Id.Extend;
1458 p_atp_period.Request_Item_Id.Extend;
1459 p_atp_period.Organization_Id.Extend;
1460 p_atp_period.Department_Id.Extend;
1461 p_atp_period.Resource_Id.Extend;
1462 p_atp_period.Supplier_Id.Extend;
1463 p_atp_period.Supplier_Site_Id.Extend;
1464 p_atp_period.From_Organization_Id.Extend;
1465 p_atp_period.From_Location_Id.Extend;
1466 p_atp_period.To_Organization_Id.Extend;
1467 p_atp_period.To_Location_Id.Extend;
1468 p_atp_period.Ship_Method.Extend;
1469 p_atp_period.Uom.Extend;
1470 p_atp_period.Total_Supply_Quantity.Extend;
1471 p_atp_period.Total_Demand_Quantity.Extend;
1472 p_atp_period.Period_Start_Date.Extend;
1473 p_atp_period.Period_End_Date.Extend;
1474 p_atp_period.Period_Quantity.Extend;
1475 p_atp_period.Identifier1.Extend;
1476 p_atp_period.Identifier2.Extend;
1477 p_atp_period.Identifier.Extend;
1478 p_atp_period.Scenario_Id.Extend;
1479 p_atp_period.Cumulative_Quantity.Extend;
1480 p_atp_period.Pegging_Id.Extend;
1481 p_atp_period.End_Pegging_Id.Extend;
1482 -- ssurendr: additional fields for allocation w/b start
1483 p_atp_period.Identifier4.Extend;
1484 p_atp_period.Demand_Class.Extend;
1485 p_atp_period.Class.Extend;
1486 p_atp_period.Customer_Id.Extend;
1487 p_atp_period.Customer_Site_Id.Extend;
1488 p_atp_period.Allocated_Supply_Quantity.Extend;
1489 p_atp_period.Supply_Adjustment_Quantity.Extend;
1490 p_atp_period.Backward_Forward_Quantity.Extend;
1491 p_atp_period.Backward_Quantity.Extend;
1492 p_atp_period.Demand_Adjustment_Quantity.Extend;
1493 p_atp_period.Adjusted_Availability_Quantity.Extend;
1494 p_atp_period.Adjusted_Cum_Quantity.Extend;
1495 p_atp_period.Unallocated_Supply_Quantity.Extend;
1496 p_atp_period.Unallocated_Demand_Quantity.Extend;
1497 p_atp_period.Unallocated_Net_Quantity.Extend;
1498 -- ssurendr: additional fields for allocation w/b end
1499 -- time_phased_atp
1500 p_atp_period.total_bucketed_demand_quantity.Extend;
1501 -- msc_sch_wb.atp_debug('***** End Extend_Atp_Period Procedure *****');
1502
1503 END Extend_Atp_Period;
1504
1505
1506 PROCEDURE Extend_Atp_Supply_Demand (
1507 p_atp_supply_demand IN OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
1508 x_return_status OUT NoCopy VARCHAR2,
1509 p_index IN NUMBER -- added by rajjain 12/10/2002
1510 ) IS
1511 Begin
1512 IF PG_DEBUG in ('Y', 'C') THEN
1513 msc_sch_wb.atp_debug('***** Begin Extend_Atp_Supply_Demand Procedure *****');
1514 msc_sch_wb.atp_debug('Extend_Atp_Supply_Demand: ' || 'p_index = ' || p_index);
1515 END IF;
1516
1517 x_return_status := FND_API.G_RET_STS_SUCCESS;
1518
1519 -- rajjain added p_index 12/10/2002
1520 p_atp_supply_demand.Level.Extend(p_index);
1521 p_atp_supply_demand.Inventory_Item_Id.Extend(p_index);
1522 p_atp_supply_demand.Request_Item_Id.Extend(p_index);
1523 p_atp_supply_demand.Organization_Id.Extend(p_index);
1524 p_atp_supply_demand.Department_Id.Extend(p_index);
1525 p_atp_supply_demand.Resource_Id.Extend(p_index);
1526 p_atp_supply_demand.Supplier_Id.Extend(p_index);
1527 p_atp_supply_demand.Supplier_Site_Id.Extend(p_index);
1528 p_atp_supply_demand.From_Organization_Id.Extend(p_index);
1529 p_atp_supply_demand.From_Location_Id.Extend(p_index);
1530 p_atp_supply_demand.To_Organization_Id.Extend(p_index);
1531 p_atp_supply_demand.To_Location_Id.Extend(p_index);
1532 p_atp_supply_demand.Ship_Method.Extend(p_index);
1533 p_atp_supply_demand.Uom.Extend(p_index);
1534 p_atp_supply_demand.Identifier1.Extend(p_index);
1535 p_atp_supply_demand.Identifier2.Extend(p_index);
1536 p_atp_supply_demand.Identifier3.Extend(p_index);
1537 p_atp_supply_demand.Identifier4.Extend(p_index);
1538 p_atp_supply_demand.Supply_Demand_Type.Extend(p_index);
1539 p_atp_supply_demand.Supply_Demand_Source_Type.Extend(p_index);
1540 p_atp_supply_demand.Supply_Demand_Source_Type_Name.Extend(p_index);
1541 p_atp_supply_demand.Supply_Demand_Date.Extend(p_index);
1542 p_atp_supply_demand.Supply_Demand_Quantity.Extend(p_index);
1543 p_atp_supply_demand.Identifier.Extend(p_index);
1544 p_atp_supply_demand.Scenario_Id.Extend(p_index);
1545 p_atp_supply_demand.Disposition_Type.Extend(p_index);
1546 p_atp_supply_demand.Disposition_Name.Extend(p_index);
1547 p_atp_supply_demand.Pegging_Id.Extend(p_index);
1548 p_atp_supply_demand.End_Pegging_Id.Extend(p_index);
1549 -- time_phased_atp change begin
1550 p_atp_supply_demand.Original_Item_Id.Extend(p_index);
1551 p_atp_supply_demand.Original_Supply_Demand_Type.Extend(p_index);
1552 p_atp_supply_demand.Original_Demand_Date.Extend(p_index);
1553 p_atp_supply_demand.Original_Demand_Quantity.Extend(p_index);
1554 p_atp_supply_demand.Allocated_Quantity.Extend(p_index);
1555 p_atp_supply_demand.Pf_Display_Flag.Extend(p_index);
1556 -- time_phased_atp change end
1557
1558 IF PG_DEBUG in ('Y', 'C') THEN
1559 msc_sch_wb.atp_debug('***** End Extend_Atp_Supply_Demand Procedure *****');
1560 END IF;
1561
1562 END Extend_Atp_Supply_Demand;
1563
1564 -- rajjain begin 12/10/2002
1565 PROCEDURE Trim_Atp_Supply_Demand (
1566 p_atp_supply_demand IN OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
1567 x_return_status OUT NoCopy VARCHAR2,
1568 p_index IN NUMBER
1569 ) IS
1570 Begin
1571 IF PG_DEBUG in ('Y', 'C') THEN
1572 msc_sch_wb.atp_debug('***** Begin Trim_Atp_Supply_Demand Procedure *****');
1573 msc_sch_wb.atp_debug('Trim_Atp_Supply_Demand: ' || 'p_index = ' || p_index);
1574 END IF;
1575 x_return_status := FND_API.G_RET_STS_SUCCESS;
1576
1577 p_atp_supply_demand.Level.Trim(p_index);
1578 p_atp_supply_demand.Inventory_Item_Id.Trim(p_index);
1579 p_atp_supply_demand.Request_Item_Id.Trim(p_index);
1580 p_atp_supply_demand.Organization_Id.Trim(p_index);
1581 p_atp_supply_demand.Department_Id.Trim(p_index);
1582 p_atp_supply_demand.Resource_Id.Trim(p_index);
1583 p_atp_supply_demand.Supplier_Id.Trim(p_index);
1584 p_atp_supply_demand.Supplier_Site_Id.Trim(p_index);
1585 p_atp_supply_demand.From_Organization_Id.Trim(p_index);
1586 p_atp_supply_demand.From_Location_Id.Trim(p_index);
1587 p_atp_supply_demand.To_Organization_Id.Trim(p_index);
1588 p_atp_supply_demand.To_Location_Id.Trim(p_index);
1589 p_atp_supply_demand.Ship_Method.Trim(p_index);
1590 p_atp_supply_demand.Uom.Trim(p_index);
1591 p_atp_supply_demand.Identifier1.Trim(p_index);
1592 p_atp_supply_demand.Identifier2.Trim(p_index);
1593 p_atp_supply_demand.Identifier3.Trim(p_index);
1594 p_atp_supply_demand.Identifier4.Trim(p_index);
1595 p_atp_supply_demand.Supply_Demand_Type.Trim(p_index);
1596 p_atp_supply_demand.Supply_Demand_Source_Type.Trim(p_index);
1597 p_atp_supply_demand.Supply_Demand_Source_Type_Name.Trim(p_index);
1598 p_atp_supply_demand.Supply_Demand_Date.Trim(p_index);
1599 p_atp_supply_demand.Supply_Demand_Quantity.Trim(p_index);
1600 p_atp_supply_demand.Identifier.Trim(p_index);
1601 p_atp_supply_demand.Scenario_Id.Trim(p_index);
1602 p_atp_supply_demand.Disposition_Type.Trim(p_index);
1603 p_atp_supply_demand.Disposition_Name.Trim(p_index);
1604 p_atp_supply_demand.Pegging_Id.Trim(p_index);
1605 p_atp_supply_demand.End_Pegging_Id.Trim(p_index);
1606 -- time_phased_atp change begin
1607 p_atp_supply_demand.Original_Item_Id.Extend(p_index);
1608 p_atp_supply_demand.Original_Supply_Demand_Type.Extend(p_index);
1609 p_atp_supply_demand.Original_Demand_Date.Extend(p_index);
1610 p_atp_supply_demand.Original_Demand_Quantity.Extend(p_index);
1611 p_atp_supply_demand.Allocated_Quantity.Extend(p_index);
1612 p_atp_supply_demand.Pf_Display_Flag.Extend(p_index);
1613 -- time_phased_atp change end
1614
1615 IF PG_DEBUG in ('Y', 'C') THEN
1616 msc_sch_wb.atp_debug('***** End Trim_Atp_Supply_Demand Procedure *****');
1617 END IF;
1618
1619 END Trim_Atp_Supply_Demand;
1620 -- rajjain end 12/10/2002
1621
1622 -- ngoel 9/28/2001, added this function for use in View MSC_SCATP_SOURCES_V to support
1623 -- Region Level Sourcing.
1624
1625 FUNCTION Get_Session_id
1626 RETURN NUMBER
1627 IS
1628 BEGIN
1629 RETURN order_sch_wb.debug_session_id;
1630 END Get_Session_id;
1631
1632 -- savirine, Aug 29, 2001: created the procedure get_regions. This would be called to get region information
1633 -- and store in MSC_REGIONS_TEMP table.
1634
1635 -- savirine, Sep 5, 2001: added parameters p_session_id and p_dblink and changed the region info selection
1636 -- to dynamic sql so that if the ATP request is coming from the source and both ERP and APS
1637 -- instances are different ( if the p_dblink is not null it means both
1638 -- ERP and APS Instances are differnt), customer address info will be selected from HZ tables
1639 -- and the region info will be selected from WSH Tables.
1640
1641 -- Procedure: Get_Regions
1642 --
1643 -- Purpose: Obtains information of the region by matching all address attributes for the customer site
1644 --
1645
1646 PROCEDURE Get_Regions_Old (
1647 p_customer_site_id IN NUMBER,
1648 p_calling_module IN NUMBER, -- i.e. Source (ERP) or Destination (724)
1649 p_instance_id IN NUMBER,
1650 p_session_id IN NUMBER,
1651 p_dblink IN VARCHAR2,
1652 --2814895
1653 -- Adding address parameters of customer
1654 p_postal_code IN VARCHAR2, --4505374
1655 p_city IN VARCHAR2,
1656 p_state IN VARCHAR2,
1657 p_country IN VARCHAR2,
1658 p_order_line_id IN NUMBER,
1659 x_return_status OUT NOCOPY VARCHAR2 ) IS
1660
1661 l_postal_code VARCHAR2(60);
1662 l_city VARCHAR2(60);
1663 l_state VARCHAR2(150); -- Increase field size for UTF reasons Bug 2890899
1664 l_country VARCHAR2(150); -- Increase field size for UTF reasons Bug 2890899
1665
1666 l_cnt NUMBER;
1667 l_stmt VARCHAR2(4000);
1668 l_dynstring VARCHAR2(128) := NULL;
1669 l_region_id NUMBER;
1670
1671 -- Bug 3010834: Backport bug 2882331 to 11.5.9 to be included in I.1
1672 l_stmt1 VARCHAR2(4000) := NULL; -- bug 2882331. forward porting fix
1673 -- Variables added to avoid repeated calls to NVL(LENGTH(var)) bug 2882331
1674 l_postal_code_length NUMBER := 0;
1675 l_state_length NUMBER := 0;
1676 l_country_length NUMBER := 0;
1677
1678 -- Partner type values added for supplier intransit LT project
1679 l_customer_type NUMBER := 2;
1680 l_partner_site_id NUMBER; --2814895
1681
1682 BEGIN
1683 -- Bug 2732267 Change the debug message qualifier from Get_Regions to Get_Regions_Old
1684 IF PG_DEBUG in ('Y', 'C') THEN
1685 msc_sch_wb.atp_debug('Begin Get_Regions_Old');
1686 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'p_customer_site_id : ' || p_customer_site_id);
1687 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'p_calling_module : ' || p_calling_module);
1688 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'p_instance_id : ' || p_instance_id);
1689 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'p_session_id : ' || p_session_id);
1690 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'p_dblink : ' || p_dblink);
1691 END IF;
1692
1693 x_return_status := FND_API.G_RET_STS_SUCCESS;
1694 IF p_calling_module <> 724 THEN
1695 BEGIN
1696 /********************* BUG 2085071 Fix ************************/
1697 /* Old Select statement -- Incorrect and hence commented out.
1698 SELECT a.postal_code, a.city, a.state, a.country
1699 INTO l_postal_code, l_city, l_state, l_country
1700 FROM hz_locations a, hz_party_sites s
1701 WHERE a.location_id = s.location_id
1702 AND s.party_site_id = p_customer_site_id;
1703 */
1704
1705 /* New Select Statement */
1706 -- For bug 2732267 select province if state is not specified
1707 IF ((p_country is not null) --2814895, use address parameter directly when they are passed by calling module
1708 AND (p_customer_site_id is NULL)) THEN
1709
1710 l_postal_code := p_postal_code;
1711 l_city := p_city ;
1712 l_state := p_state;
1713 l_country := p_country ;
1714
1715 l_partner_site_id := p_order_line_id;
1716 l_customer_type := 5; --2814895, 5 for address parameters
1717
1718 ELSE --2814895
1719
1720 l_partner_site_id := p_customer_site_id; --2814895
1721
1722 SELECT LOC.POSTAL_CODE, LOC.CITY, NVL(LOC.STATE, LOC.PROVINCE), LOC.COUNTRY
1723 INTO l_postal_code, l_city, l_state, l_country
1724 FROM HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
1725 HZ_PARTY_SITES PARTY_SITE,
1726 HZ_LOCATIONS LOC,
1727 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1728 WHERE LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1729 AND PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
1730 AND SITE_USES_ALL.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1731 AND SITE_USES_ALL.site_use_id = p_customer_site_id;
1732
1733 END IF;
1734 /********************* BUG 2085071 Fix ************************/
1735
1736 IF PG_DEBUG in ('Y', 'C') THEN
1737 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_postal_code : ' || l_postal_code);
1738 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_city : ' || l_city);
1739 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_state : ' || l_state);
1740 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_country : ' || l_country);
1741 END IF;
1742
1743 -- Bug 3010834: Backport bug 2882331 to 11.5.9 to be included in I.1
1744 -- Length Variables assigned the NVL(LENGTH(var)) to avoid repeated calls bug 2882331.
1745 -- If length of these variables is <= 3, join with var_code else join with var
1746 l_postal_code_length := NVL(LENGTH(l_postal_code), 0);
1747 l_state_length := NVL(LENGTH(l_state), 0);
1748 l_country_length := NVL(LENGTH(l_country), 0);
1749
1750 IF PG_DEBUG in ('Y', 'C') THEN
1751 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'Length(l_postal_code) : ' || l_postal_code_length);
1752 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'Length(l_state) : ' || l_state_length);
1753 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'Length(l_country) : ' || l_country_length);
1754 END IF;
1755
1756
1757 l_cnt := 0;
1758
1759 FOR i in REVERSE 0..3 LOOP
1760
1761 BEGIN
1762 l_stmt := 'SELECT region_id
1763 FROM WSH_REGIONS_V';
1764
1765 -- Bug 3010834: Backport bug 2882331 to 11.5.9 to be included in I.1
1766 IF l_cnt <= 3 THEN
1767 IF l_country_length <= 3 THEN
1768 l_stmt := l_stmt || ' WHERE country_code = :l_country';
1769 ELSE
1770 -- 2778393 : krajan : case insensitivity
1771 l_stmt := l_stmt || ' WHERE UPPER(country) = UPPER(:l_country)';
1772 END IF;
1773 -- bug 2882331. forward porting fix for COUNTRY+ZIP
1774 l_stmt1 := l_stmt;
1775 END IF;
1776
1777 -- Bug 3010834: Backport bug 2882331 to 11.5.9 to be included in I.1
1778 IF l_cnt <= 2 THEN
1779 -- krajan : 2778393 : Check if State record is nULL
1780 IF l_state_length = 0 THEN
1781 l_stmt := l_stmt || ' AND state_code is NULL';
1782 l_stmt := l_stmt || ' AND state is NULL';
1783 -- We dont need DECODE stmt anymore.
1784 ELSIF l_state_length <= 3 THEN
1785 l_stmt := l_stmt || ' AND nvl(state_code,:l_state2) = :l_state3';
1786 ELSE
1787 -- 2778393 : krajan : case insensitivity
1788 l_stmt := l_stmt || ' AND UPPER(nvl(state,:l_state4)) = UPPER(:l_state5)';
1789 END IF;
1790 END IF;
1791
1792 IF l_cnt <= 1 THEN
1793 -- 2778393 : krajan : case insensitivity
1794 l_stmt := l_stmt || ' AND UPPER(city) = UPPER(:l_city)';
1795 END IF;
1796
1797 -- Bug 3010834: Backport bug 2882331 to 11.5.9 to be included in I.1
1798 IF l_cnt = 0 THEN
1799 IF l_postal_code_length > 0 THEN
1800 -- use l_stmt1 instead of l_stmt
1801 -- Bug 3010834 F/w port 2979572 Use Postal code from when Postal Code to is null
1802 l_stmt1 := l_stmt1 || ' AND postal_code_from <= :l_postal_code1
1803 AND nvl(postal_code_to, postal_code_from) >= :l_postal_code2
1804 AND region_type = :counter AND rownum = 1';
1805 END IF;
1806 END IF;
1807
1808 l_stmt := l_stmt || ' AND region_type = :counter AND rownum = 1';
1809 IF PG_DEBUG in ('Y', 'C') THEN
1810 msc_sch_wb.atp_debug('Get_Regions_Old: searching region_type: ' || i);
1811 IF l_cnt = 0 THEN
1812 msc_sch_wb.atp_debug('Get_Regions_Old: l_stmt1: '||l_stmt1);
1813 ELSE
1814 msc_sch_wb.atp_debug('Get_Regions_Old: l_stmt: ' || l_stmt);
1815 END IF;
1816 END IF;
1817
1818 -- Bug 3010834: Backport bug 2882331 to 11.5.9 to be included in I.1
1819 IF l_cnt = 0 THEN
1820 IF l_postal_code_length > 0 THEN
1821 execute immediate l_stmt1 INTO l_region_id
1822 -- Bug 3010834 F/w port 2979572 Use Postal code from when Postal Code to is null
1823 using l_country, l_postal_code,l_postal_code, i;
1824 ELSE
1825 RAISE NO_DATA_FOUND;
1826 END IF;
1827 ELSIF l_cnt = 1 THEN
1828 IF (l_state_length = 0) THEN
1829 execute immediate l_stmt INTO l_region_id
1830 using l_country, l_city, i;
1831 ELSE
1832 execute immediate l_stmt INTO l_region_id
1833 using l_country, l_state,l_state,l_city, i;
1834 END IF;
1835 ELSIF l_cnt = 2 THEN
1836 IF (l_state_length = 0) THEN
1837 execute immediate l_stmt INTO l_region_id
1838 using l_country, i;
1839 ELSE
1840 execute immediate l_stmt INTO l_region_id
1841 using l_country, l_state,l_state,i;
1842 END IF;
1843 ELSIF l_cnt = 3 THEN
1844 execute immediate l_stmt INTO l_region_id
1845 using l_country, i;
1846 END IF;
1847
1848 IF PG_DEBUG in ('Y', 'C') THEN
1849 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_cnt: ' || l_cnt);
1850 END IF;
1851 exit; -- to exit the loop.
1852
1853 EXCEPTION
1854 WHEN NO_DATA_FOUND THEN
1855 l_cnt := l_cnt + 1;
1856
1857 END;
1858
1859 END LOOP; -- FOR i in 3..0 LOOP
1860
1861 IF l_region_id is NOT NULL THEN
1862
1863 IF p_dblink IS NOT NULL THEN
1864 l_dynstring := '@'||p_dblink;
1865 END IF;
1866
1867 IF PG_DEBUG in ('Y', 'C') THEN
1868 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_dynstring: ' || l_dynstring);
1869 END IF;
1870
1871 BEGIN
1872 -- Modified the SQL for bug 2484964. For better performance
1873 -- avoid the sub-query.
1874 -- also update Partner_type for supplier intransit LT project
1875 l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
1876 ' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
1877 -- SELECT :p_session_id, commented for performance tuning bug 2484964
1878 (SELECT DISTINCT :p_session_id,
1879 :p_customer_site_id,
1880 region_id,
1881 region_type,
1882 ''N'',
1883 :partner_type
1884 FROM WSH_REGIONS
1885 START WITH region_id = :l_region_id
1886 CONNECT BY PRIOR parent_region_id = region_id)';
1887
1888 IF PG_DEBUG in ('Y', 'C') THEN
1889 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_stmt : ' || l_stmt);
1890 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'p_session_id : ' || p_session_id);
1891 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'p_customer_site_id : ' || p_customer_site_id);
1892 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_region_id : ' || l_region_id);
1893 END IF;
1894
1895 EXECUTE immediate l_stmt
1896 using p_session_id, l_partner_site_id, l_customer_type, l_region_id; --2814895
1897
1898 -- also update Partner_type for supplier intransit LT project
1899 -- partner_type is also included in the where clause
1900 --2814895, changed l_customer site_id to l_partner_site_id
1901 l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
1902 ' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
1903 SELECT :p_session_id,
1904 :l_partner_site_id,
1905 a.region_id,
1906 a.zone_level,
1907 ''Y'',
1908 :partner_type
1909 FROM WSH_REGIONS a, WSH_ZONE_REGIONS b
1910 WHERE a.region_id = b.parent_region_id
1911 AND a.region_type = 10
1912 AND a.zone_level IS NOT NULL
1913 AND b.region_id IN (
1914 SELECT c.region_id
1915 FROM msc_regions_temp' || l_dynstring || ' c
1916 WHERE c.session_id = :p_session_id1
1917 AND c.partner_site_id = :p_partner_site_id1
1918 AND c.partner_type = :partner_type1)';
1919
1920 IF PG_DEBUG in ('Y', 'C') THEN
1921 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_stmt : ' || l_stmt);
1922 END IF;
1923
1924 EXECUTE immediate l_stmt using p_session_id, l_partner_site_id, l_customer_type,
1925 p_session_id, l_partner_site_id, l_customer_type; --2814895
1926 EXCEPTION
1927 WHEN DUP_VAL_ON_INDEX THEN
1928 --- if we come here then that means that
1929 -- region info for this customer has already been inserted
1930 -- This would happen from order imports where one order may contain
1931 -- requests from many customer sites. If a same customer is
1932 --- seperated by one or more customers then we would come in this
1933 -- procedure both time. One second time this exception will be raised.
1934 IF PG_DEBUG in ('Y', 'C') THEN
1935 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'Regions for this customer are already there in the temp table');
1936 END IF;
1937 END;
1938 END IF; -- IF l_region_id is NOT NULL THEN
1939
1940 END;
1941 ELSE -- IF p_calling_module <> 724 THEN
1942
1943 BEGIN
1944
1945 SELECT mtps.postal_code, mtps.city, mtps.state, mtps.country
1946 INTO l_postal_code, l_city, l_state, l_country
1947 FROM msc_trading_partner_sites mtps,
1948 msc_tp_site_id_lid tpsid
1949 WHERE tpsid.sr_tp_site_id = p_customer_site_id
1950 AND tpsid.sr_instance_id = p_instance_id
1951 AND rownum = 1
1952 AND tpsid.partner_type = 2
1953 AND tpsid.tp_site_id = mtps.partner_site_id;
1954
1955 IF PG_DEBUG in ('Y', 'C') THEN
1956 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_postal_code : ' || l_postal_code);
1957 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_city : ' || l_city);
1958 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_state : ' || l_state);
1959 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_country : ' || l_country);
1960 END IF;
1961
1962 -- Bug 3010834: Backport bug 2882331 to 11.5.9 to be included in I.1
1963 -- Length Variables assigned the NVL(LENGTH(var)) to avoid repeated calls bug 2882331.
1964 -- If length of these variables is <= 3, join with var_code else join with var
1965 l_postal_code_length := NVL(LENGTH(l_postal_code), 0);
1966 l_state_length := NVL(LENGTH(l_state), 0);
1967 l_country_length := NVL(LENGTH(l_country), 0);
1968
1969 IF PG_DEBUG in ('Y', 'C') THEN
1970 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'Length(l_postal_code) : ' || l_postal_code_length);
1971 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'Length(l_state) : ' || l_state_length);
1972 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'Length(l_country) : ' || l_country_length);
1973 END IF;
1974
1975
1976 l_cnt := 0;
1977
1978 FOR i in REVERSE 0..3 LOOP
1979 BEGIN
1980 l_stmt := 'SELECT region_id
1981 FROM MSC_REGIONS
1982 WHERE sr_instance_id = :p_instance_id';
1983
1984 -- Bug 3010834: Backport bug 2882331 to 11.5.9 to be included in I.1
1985 IF l_cnt <= 3 THEN
1986 IF l_country_length <= 3 THEN
1987 l_stmt := l_stmt || ' AND country_code = :l_country';
1988 ELSE
1989 -- 2778393 : krajan : case insensitivity
1990 l_stmt := l_stmt || ' AND UPPER(country) = UPPER(:l_country)';
1991 END IF;
1992 -- bug 2882331. forward porting fix
1993 l_stmt1 := l_stmt;
1994 END IF;
1995
1996 -- Bug 3010834: Backport bug 2882331 to 11.5.9 to be included in I.1
1997 IF l_cnt <= 2 THEN
1998 -- krajan : 2778393 : Check if State record is nULL
1999 IF l_state_length = 0 THEN
2000 l_stmt := l_stmt || ' AND state_code is NULL';
2001 l_stmt := l_stmt || ' AND state is NULL';
2002 -- l_stmt := l_stmt || ' AND DECODE (:l_state,0,0,0) = 0'; We dont need this now
2003 ELSIF l_state_length <= 3 THEN
2004 l_stmt := l_stmt || ' AND nvl(state_code,:l_state2) = :l_state3';
2005 ELSE
2006 -- 2778393 : krajan : case insensitivity
2007 -- l_stmt := l_stmt || ' AND UPPER(state) = UPPER(:l_state)';
2008 l_stmt := l_stmt || ' AND UPPER(nvl(state,:l_state4)) = UPPER(:l_state5)';
2009 END IF;
2010 END IF;
2011
2012 IF l_cnt <= 1 THEN
2013 l_stmt := l_stmt || ' AND UPPER(city) = UPPER(:l_city)';
2014 END IF;
2015
2016 -- Bug 3010834: Backport bug 2882331 to 11.5.9 to be included in I.1
2017 IF l_cnt = 0 THEN
2018 IF l_postal_code_length > 0 THEN
2019 -- Bug 3010834 F/w port 2979572 Use Postal code from when Postal Code to is null
2020 l_stmt1 := l_stmt1 || ' AND postal_code_from <= :l_postal_code1
2021 AND nvl(postal_code_to, postal_code_from) >= :l_postal_code2
2022 AND region_type = :counter AND rownum = 1';
2023 END IF;
2024 END IF;
2025
2026 l_stmt := l_stmt || ' AND region_type = :counter AND rownum = 1';
2027
2028 IF PG_DEBUG in ('Y', 'C') THEN
2029 msc_sch_wb.atp_debug('Get_Regions_Old: searching region_type : ' || i);
2030 IF l_cnt = 0 THEN
2031 msc_sch_wb.atp_debug('Get_Regions_Old: l_stmt1: ' ||l_stmt1);
2032 ELSE
2033 msc_sch_wb.atp_debug('Get_Regions_Old: l_stmt : ' || l_stmt);
2034 END IF;
2035 END IF;
2036
2037 -- Bug 3010834: Backport bug 2882331 to 11.5.9 to be included in I.1
2038 IF l_cnt = 0 THEN
2039 IF l_postal_code_length > 0 THEN
2040 execute immediate l_stmt1 INTO l_region_id
2041 -- Bug 3010834 F/w port 2979572 Use Postal code from when Postal Code to is null
2042 using p_instance_id,l_country, l_postal_code,l_postal_code, i;
2043 ELSE
2044 RAISE NO_DATA_FOUND;
2045 END IF;
2046
2047 ELSIF l_cnt = 1 THEN
2048 IF l_state_length = 0 THEN
2049 execute immediate l_stmt INTO l_region_id
2050 -- using p_instance_id, l_country, l_state, l_city, i; We dont need to pass state
2051 using p_instance_id, l_country, l_city, i;
2052 ELSE
2053 execute immediate l_stmt INTO l_region_id
2054 using p_instance_id, l_country, l_state,l_state, l_city,i;
2055 END IF;
2056
2057 ELSIF l_cnt = 2 THEN
2058 IF l_state_length = 0 THEN
2059 execute immediate l_stmt INTO l_region_id
2060 -- using p_instance_id, l_country, l_state,i; No need to pass state.
2061 using p_instance_id, l_country, i;
2062 ELSE
2063 execute immediate l_stmt INTO l_region_id
2064 using p_instance_id, l_country, l_state,l_state,i;
2065 END IF;
2066
2067 ELSIF l_cnt = 3 THEN
2068 execute immediate l_stmt INTO l_region_id
2069 using p_instance_id, l_country, i;
2070 END IF;
2071
2072 IF PG_DEBUG in ('Y', 'C') THEN
2073 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_cnt: ' || l_cnt);
2074 END IF;
2075 exit; -- to exit the loop.
2076
2077 EXCEPTION
2078 WHEN NO_DATA_FOUND THEN
2079 l_cnt := l_cnt + 1;
2080
2081 END;
2082 END LOOP;
2083
2084 IF l_region_id is NOT NULL THEN
2085
2086 IF PG_DEBUG in ('Y', 'C') THEN
2087 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'l_region_id: ' || l_region_id);
2088 END IF;
2089
2090 -- Bug 2837366 : krajan : Catch the DUP_VAL_ON_INDEX error
2091 -- also update Partner_type for supplier intransit LT project
2092 BEGIN
2093 INSERT INTO msc_regions_temp
2094 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2095 -- Begin Bug 2498174
2096 -- Changed Query to enhance performance
2097 SELECT DISTINCT p_session_id,
2098 p_customer_site_id,
2099 region_id,
2100 region_type,
2101 'N',
2102 l_customer_type -- For supplier intransit LT project
2103 FROM MSC_REGIONS
2104 WHERE sr_instance_id = p_instance_id
2105 START WITH region_id = l_region_id
2106 CONNECT BY PRIOR parent_region_id = region_id;
2107 -- Removed Subquery for performance Bug 2498174
2108 -- End Bug 2498174
2109
2110 IF PG_DEBUG in ('Y', 'C') THEN
2111 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'After Region Fetch for ' || l_region_id);
2112 END IF;
2113
2114 -- Begin Bug 2498174
2115 -- Ensure that regions and zones query has instance_id filter
2116 -- Changed Query to enhance performance
2117 INSERT INTO msc_regions_temp
2118 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2119 SELECT p_session_id,
2120 p_customer_site_id,
2121 a.region_id,
2122 a.zone_level,
2123 'Y',
2124 l_customer_type -- For supplier intransit LT project
2125 FROM MSC_REGIONS_TEMP c, MSC_ZONE_REGIONS b, MSC_REGIONS a
2126 WHERE a.region_id = b.parent_region_id
2127 AND c.region_id = b.region_id
2128 AND a.sr_instance_id = b.sr_instance_id
2129 AND b.sr_instance_id = p_instance_id
2130 AND a.region_type = 10
2131 AND a.zone_level IS NOT NULL
2132 AND c.session_id = p_session_id
2133 AND c.partner_site_id = p_customer_site_id
2134 AND c.partner_type = l_customer_type; -- For supplier intransit LT project
2135 -- End Bug 2498174
2136 EXCEPTION
2137 WHEN DUP_VAL_ON_INDEX THEN
2138 IF PG_DEBUG in ('Y', 'C') THEN
2139 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'Regions for this customer are already there in the temp table');
2140 END IF;
2141 END;
2142
2143 END IF; -- IF l_region_id is NOT NULL THEN
2144 END;
2145
2146 END IF;
2147 EXCEPTION
2148 WHEN NO_DATA_FOUND THEN
2149 IF PG_DEBUG in ('Y', 'C') THEN
2150 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'Customer with following customer_site_id does not exist: ' || p_customer_site_id);
2151 END IF;
2152 return;
2153 WHEN OTHERS THEN
2154 IF (SQLCODE = -942) THEN
2155 IF PG_DEBUG in ('Y', 'C') THEN
2156 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'Table/View doesnt exist');
2157 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'Continue as normal');
2158 END IF;
2159 return;
2160 ELSE
2161 IF PG_DEBUG in ('Y', 'C') THEN
2162 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'sqlcode : ' || sqlcode || ' : ' || sqlerrm);
2163 msc_sch_wb.atp_debug('Get_Regions_Old: ' || 'Error for Customer with customer_site_id : ' || p_customer_site_id);
2164 END IF;
2165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2166 return;
2167 END IF;
2168 END Get_Regions_Old;
2169
2170
2171 -- Get Regions functionality using the Regions to Locations mapping table
2172 -- krajan : refer to design document for bug 2359231
2173
2174 PROCEDURE Get_Regions_Shipping (
2175 p_customer_site_id IN NUMBER,
2176 p_calling_module IN NUMBER, -- i.e. Source (ERP) or Destination (724)
2177 p_instance_id IN NUMBER,
2178 p_session_id IN NUMBER,
2179 p_dblink IN VARCHAR2,
2180 x_return_status OUT NOCOPY VARCHAR2,
2181 p_location_id IN NUMBER ,
2182 p_location_source IN VARCHAR2,
2183 p_supplier_site_id IN NUMBER DEFAULT NULL,-- For supplier intransit LT project
2184 p_party_site_id IN NUMBER) IS --2814895
2185
2186
2187 l_postal_code VARCHAR2(60);
2188 l_city VARCHAR2(60);
2189 l_state VARCHAR2(60);
2190 l_country VARCHAR2(60);
2191
2192 l_cnt NUMBER;
2193 l_stmt VARCHAR2(4000);
2194 l_dynstring VARCHAR2(128) := NULL;
2195
2196 l_region_id NUMBER;
2197 l_region_type NUMBER;
2198
2199 --bug 2744106: Change hard coded strings to bind variables
2200 l_YES VARCHAR2(1) := 'Y';
2201 l_NO VARCHAR2(1) := 'N';
2202 l_HZ VARCHAR2(2) := 'HZ';
2203
2204 -- Partner type values added for supplier intransit LT project
2205 l_vendor_type NUMBER := 1;
2206 l_customer_type NUMBER := 2;
2207 l_party_type NUMBER := 4; --2814895
2208 l_partner_type NUMBER := 2; --2814895, default as 2
2209
2210 BEGIN
2211 IF PG_DEBUG in ('Y', 'C') THEN
2212 msc_sch_wb.atp_debug('Begin Get_regions_SHIPPING');
2213 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'p_customer_site_id : ' || p_customer_site_id);
2214 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'p_calling_module : ' || p_calling_module);
2215 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'p_instance_id : ' || p_instance_id);
2216 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'p_session_id : ' || p_session_id);
2217 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'p_dblink : ' || p_dblink);
2218 END IF;
2219
2220 x_return_status := FND_API.G_RET_STS_SUCCESS;
2221 -- Always use destination table if supplier site is passed
2222 -- IF p_calling_module <> 724 AND nvl(p_supplier_site_id,-1)=-1 THEN -- For supplier intransit LT project
2223 -- Bug 3497370 - Handle null calling_module
2224 IF nvl(p_calling_module,-99) <> 724 AND nvl(p_supplier_site_id,-1)=-1 THEN -- For supplier intransit LT project
2225 BEGIN
2226
2227 IF p_dblink IS NOT NULL THEN
2228 l_dynstring := '@'||p_dblink;
2229 END IF;
2230 IF PG_DEBUG in ('Y', 'C') THEN
2231 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'l_dynstring: ' || l_dynstring);
2232 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'New Code starting ');
2233 END IF;
2234 if (p_customer_site_id <> -1) THEN
2235 --process as usual
2236 IF PG_DEBUG in ('Y', 'C') THEN
2237 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Customer_Site_id is populated : ' || p_customer_site_id);
2238 END IF;
2239 --bug 2744106: chnage hard coded stings to bind variables
2240 -- bug 2974334. Change the SQL into static if dbink is null.
2241
2242 /* bug 3425497: First insert into table locally and then transfer over dblink
2243 IF p_dblink IS NOT NULL THEN
2244
2245 -- also update Partner_type for supplier intransit LT project
2246 l_stmt := ' INSERT INTO msc_regions_temp' || l_dynstring ||
2247 ' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2248 SELECT :p_session_id,
2249 :p_customer_site_id1,
2250 region_id,
2251 region_type,
2252 :l_NO,
2253 :partner_type
2254 FROM WSH_REGION_LOCATIONS
2255 WHERE location_id IN
2256 (SELECT LOC.LOCATION_ID
2257 FROM HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
2258 HZ_PARTY_SITES PARTY_SITE,
2259 HZ_LOCATIONS LOC,
2260 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2261 WHERE LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2262 AND PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
2263 AND SITE_USES_ALL.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
2264 AND SITE_USES_ALL.site_use_id = :p_customer_site_id
2265 )
2266 AND location_source = :l_HZ
2267 AND region_id is not null'; -- 2837468
2268
2269 EXECUTE immediate l_stmt
2270 using p_session_id, p_customer_site_id,l_NO, l_customer_type, p_customer_site_id, l_HZ;
2271
2272 ELSE -- bug 2974334. Change the SQL into static if dbink is null.
2273
2274 -- also update Partner_type for supplier intransit LT project
2275 INSERT INTO msc_regions_temp
2276 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2277 SELECT p_session_id, p_customer_site_id, region_id, region_type, l_NO, l_customer_type
2278 FROM WSH_REGION_LOCATIONS
2279 WHERE location_id IN
2280 (SELECT LOC.LOCATION_ID
2281 FROM HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
2282 HZ_PARTY_SITES PARTY_SITE,
2283 HZ_LOCATIONS LOC,
2284 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2285 WHERE LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2286 AND PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
2287 AND SITE_USES_ALL.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
2288 AND SITE_USES_ALL.site_use_id = p_customer_site_id
2289 )
2290 AND location_source = l_HZ
2291 AND region_id is not null; -- 2837468
2292
2293 END IF;
2294 */
2295
2296 INSERT INTO msc_regions_temp
2297 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2298 SELECT p_session_id, p_customer_site_id, wrl.region_id, wrl.region_type, l_NO, l_partner_type --2814895
2299 FROM WSH_REGION_LOCATIONS WRL,
2300 HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
2301 HZ_PARTY_SITES PARTY_SITE,
2302 HZ_LOCATIONS LOC,
2303 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2304 WHERE WRL.location_id = LOC.LOCATION_ID
2305 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2306 AND PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
2307 AND SITE_USES_ALL.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
2308 AND SITE_USES_ALL.site_use_id = p_customer_site_id
2309 AND WRL.location_source = l_HZ
2310 AND WRL.region_id is not null; -- 2837468
2311
2312 IF PG_DEBUG in ('Y', 'C') THEN
2313 msc_sch_wb.atp_debug('Row Count := ' || SQL%ROWCOUNT);
2314 END IF;
2315
2316 ELSIF (NVL(p_party_site_id, -1) <> -1) THEN --2814895, only adding it if nvl(p_calling_module,-99) <> 724
2317
2318 l_partner_type := l_party_type;
2319
2320 INSERT INTO msc_regions_temp
2321 (session_id,partner_site_id,region_id,region_type,zone_flag, partner_type) --2814895
2322 SELECT p_session_id,p_party_site_id,wrl.region_id,wrl.region_type,l_NO,l_partner_type
2323 FROM WSH_REGION_LOCATIONS WRL,
2324 HZ_PARTY_SITES PARTY_SITE
2325 WHERE WRL.location_id = PARTY_SITE.LOCATION_ID
2326 AND PARTY_SITE.party_site_id = p_party_site_id
2327 AND WRL.location_source = l_HZ
2328 AND WRL.region_id is not null;
2329
2330 IF PG_DEBUG in ('Y', 'C') THEN --2814895
2331 msc_sch_wb.atp_debug('Row Count := ' || SQL%ROWCOUNT);
2332 END IF;
2333 else
2334 IF PG_DEBUG in ('Y', 'C') THEN
2335 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Going by the location ID');
2336 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Location ID : ' ||p_location_id);
2337 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Location Src: ' || p_location_source);
2338 END IF;
2339
2340 -- bug 2974334. Change the SQL into static if dbink is null
2341 /* 3425497: first insert locally.
2342 IF p_dblink IS NOT NULL THEN
2343
2344 -- also update Partner_type for supplier intransit LT project
2345 l_stmt := 'INSERT INTO msc_regions_temp' || l_dynstring ||
2346 ' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2347 SELECT :p_session_id,
2348 -1,
2349 region_id,
2350 region_type,
2351 :l_NO,
2352 :partner_type
2353 FROM WSH_REGION_LOCATIONS
2354 WHERE location_id = :p_location_id
2355 AND location_source = :p_location_source
2356 AND region_id is not null'; --2837468
2357
2358 IF PG_DEBUG in ('Y', 'C') THEN
2359 msc_sch_wb.atp_debug ('Get_Regions_Shipping: ' || 'l_stmt = ' || l_stmt);
2360 END IF;
2361
2362 EXECUTE immediate l_stmt
2363 using p_session_id, l_NO, p_location_id, p_location_source;
2364
2365 ELSE
2366
2367 -- also update Partner_type for supplier intransit LT project
2368 INSERT INTO msc_regions_temp
2369 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2370 SELECT p_session_id, -1, region_id, region_type, l_NO, l_customer_type
2371 FROM WSH_REGION_LOCATIONS
2372 WHERE location_id = p_location_id
2373 AND location_source = p_location_source
2374 AND region_id is not null; --2837468
2375
2376 END IF; -- bug 2974334. Change the SQL into static if dbink is null
2377 */
2378 -- also update Partner_type for supplier intransit LT project
2379 INSERT INTO msc_regions_temp
2380 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2381 SELECT p_session_id, -1, region_id, region_type, l_NO, l_customer_type --2814895
2382 FROM WSH_REGION_LOCATIONS
2383 WHERE location_id = p_location_id
2384 AND location_source = p_location_source
2385 AND region_id is not null; --2837468
2386
2387 IF PG_DEBUG in ('Y', 'C') THEN
2388 msc_sch_wb.atp_debug('Row Count := ' || SQL%ROWCOUNT);
2389 END IF;
2390 END if;
2391
2392 --- RMEHRA : Sql Performance Tuning
2393 -- changed SQL
2394 -- bug 2974334. Change the SQL into static if dbink is null
2395
2396 /* 3425497: Insert data locally
2397 IF p_dblink IS NOT NULL THEN
2398
2399 -- also update Partner_type for supplier intransit LT project
2400 -- partner_type is also included in the where clause
2401 l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
2402 ' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2403 SELECT DISTINCT :p_session_id,
2404 :p_customer_site_id,
2405 a.region_id,
2406 a.zone_level,
2407 :l_YES,
2408 :partner_type
2409 FROM WSH_REGIONS a, WSH_ZONE_REGIONS b,
2410 MSC_REGIONS_TEMP' || l_dynstring || ' c
2411 WHERE a.region_id = b.parent_region_id
2412 AND a.region_type = 10
2413 AND a.zone_level IS NOT NULL
2414 AND b.region_id = c.region_id
2415 AND c.session_id = :p_session_id1
2416 AND c.partner_site_id = :p_customer_site_id1
2417 AND c.partner_type = :partner_type1'; -- For supplier intransit LT project
2418
2419
2420 IF PG_DEBUG in ('Y', 'C') THEN
2421 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'l_stmt : ' || l_stmt);
2422 END IF;
2423
2424
2425 EXECUTE immediate l_stmt using p_session_id, p_customer_site_id, l_YES,
2426 l_customer_type, p_session_id, p_customer_site_id, l_customer_type;
2427
2428 ELSE
2429
2430 -- also update Partner_type for supplier intransit LT project
2431 -- partner_type is also included in the where clause
2432 INSERT into msc_regions_temp
2433 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2434 SELECT DISTINCT p_session_id,
2435 p_customer_site_id,
2436 a.region_id,
2437 a.zone_level,
2438 l_YES,
2439 l_customer_type
2440 FROM WSH_REGIONS a, WSH_ZONE_REGIONS b, MSC_REGIONS_TEMP c
2441 WHERE a.region_id = b.parent_region_id
2442 AND a.region_type = 10
2443 AND a.zone_level IS NOT NULL
2444 AND b.region_id = c.region_id
2445 AND c.session_id = p_session_id
2446 AND c.partner_site_id = p_customer_site_id
2447 AND c.partner_type = l_customer_type; -- For supplier intransit LT project
2448
2449 END IF; -- bug 2974334. Change the SQL into static if dbink is null
2450
2451 */
2452
2453 -- also update Partner_type for supplier intransit LT project
2454 -- partner_type is also included in the where clause
2455 INSERT into msc_regions_temp
2456 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2457 SELECT DISTINCT p_session_id,
2458 p_customer_site_id,
2459 a.region_id,
2460 a.zone_level,
2461 l_YES,
2462 l_customer_type
2463 FROM WSH_REGIONS a, WSH_ZONE_REGIONS b, MSC_REGIONS_TEMP c
2464 WHERE a.region_id = b.parent_region_id
2465 AND a.region_type = 10
2466 AND a.zone_level IS NOT NULL
2467 AND b.region_id = c.region_id
2468 AND c.session_id = p_session_id
2469 AND c.partner_site_id = decode(l_partner_type, l_customer_type, p_customer_site_id, p_party_site_id) --2814895
2470 AND c.partner_type = l_partner_type; -- 2814895 -- For supplier intransit LT project
2471
2472 IF PG_DEBUG in ('Y', 'C') THEN
2473 msc_sch_wb.atp_debug('Row Count := ' || SQL%ROWCOUNT);
2474 END IF;
2475 /*bug3520746 do not push the data across dblink
2476 --bug 3425497: Now if dblink is not null then transfer the data across dblink
2477 IF p_dblink IS NOT NULL THEN
2478 --first insert data across dblink
2479 l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
2480 ' (SESSION_ID, PARTNER_SITE_ID, REGION_ID, REGION_TYPE, ZONE_FLAG, PARTNER_TYPE)
2481 select SESSION_ID, PARTNER_SITE_ID, REGION_ID, REGION_TYPE, ZONE_FLAG, PARTNER_TYPE
2482 from msc_regions_temp
2483 where session_id = :p_session_id';
2484 IF PG_DEBUG in ('Y', 'C') THEN
2485 msc_sch_wb.atp_debug('l_stmt:= ' || l_stmt);
2486 END IF;
2487
2488 execute immediate l_stmt using p_session_id;
2489 IF PG_DEBUG in ('Y', 'C') THEN
2490 msc_sch_wb.atp_debug('Row Count := ' || SQL%ROWCOUNT);
2491 END IF;
2492
2493 --now delete the data locally
2494 delete msc_regions_temp where session_id = p_session_id;
2495 END IF;
2496 */
2497 EXCEPTION
2498 WHEN DUP_VAL_ON_INDEX THEN
2499 IF PG_DEBUG in ('Y', 'C') THEN
2500 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Regions for this customer are already there in the temp table');
2501 END IF;
2502 END;
2503 ELSE -- IF p_calling_module <> 724 AND nvl(p_supplier_site_id,-1)=-1 THEN
2504
2505 IF nvl(p_customer_site_id,-1) <> -1 THEN -- For supplier intransit LT project
2506
2507 BEGIN
2508 /* Replace IN clause with = join
2509 -- also update Partner_type for supplier intransit LT project
2510 insert into msc_regions_temp
2511 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2512 select p_session_id,
2513 p_customer_site_id,
2514 region_id,
2515 region_type,
2516 'N',
2517 l_customer_type
2518 from msc_region_locations
2519 where location_id in (
2520 select location_id
2521 from msc_tp_site_id_lid tpsid
2522 where tpsid.sr_instance_id = p_instance_id
2523 and tpsid.sr_tp_site_id = p_customer_site_id
2524 and tpsid.partner_type = 2
2525 )
2526 and sr_instance_id = p_instance_id
2527 and region_id is not null
2528 and location_source = 'HZ';
2529
2530 */
2531
2532 insert into msc_regions_temp
2533 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2534 select p_session_id,
2535 p_customer_site_id,
2536 mrl.region_id,
2537 mrl.region_type,
2538 'N',
2539 l_customer_type
2540 from msc_region_locations mrl,
2541 msc_tp_site_id_lid tpsid
2542
2543 where mrl.location_id = tpsid.location_id
2544 and tpsid.sr_instance_id = p_instance_id
2545 and tpsid.sr_tp_site_id = p_customer_site_id
2546 and tpsid.partner_type = 2
2547 and mrl.sr_instance_id = p_instance_id
2548 and mrl.region_id is not null
2549 and mrl.location_source = 'HZ';
2550
2551 /* 3425497: Replace in clause with = joins
2552
2553 -- Insert Zones data
2554 -- also update Partner_type for supplier intransit LT project
2555 -- partner_type is also included in the where clause
2556 INSERT INTO msc_regions_temp
2557 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2558 SELECT DISTINCT p_session_id,
2559 p_customer_site_id,
2560 a.region_id,
2561 a.zone_level,
2562 'Y',
2563 l_customer_type
2564 FROM MSC_REGIONS a, MSC_ZONE_REGIONS b
2565 WHERE a.region_id = b.parent_region_id
2566 AND a.region_type = 10
2567 AND a.zone_level IS NOT NULL
2568 AND a.sr_instance_id = b.sr_instance_id
2569 and b.sr_instance_id = p_instance_id
2570 AND b.region_id IN (
2571 SELECT c.region_id
2572 FROM msc_regions_temp c
2573 WHERE c.session_id = p_session_id
2574 AND c.partner_site_id = p_customer_site_id
2575 AND c.partner_type = l_customer_type -- For supplier intransit LT project
2576 );
2577 */
2578
2579 INSERT INTO msc_regions_temp
2580 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2581 SELECT DISTINCT p_session_id,
2582 p_customer_site_id,
2583 a.region_id,
2584 a.zone_level,
2585 'Y',
2586 l_customer_type
2587 FROM MSC_REGIONS a, MSC_ZONE_REGIONS b, msc_regions_temp c
2588 WHERE a.region_id = b.parent_region_id
2589 AND a.region_type = 10
2590 AND a.zone_level IS NOT NULL
2591 AND a.sr_instance_id = b.sr_instance_id
2592 and b.sr_instance_id = p_instance_id
2593 AND b.region_id = c.region_id
2594 AND c.session_id = p_session_id
2595 AND c.partner_site_id = p_customer_site_id
2596 AND c.partner_type = l_customer_type -- For supplier intransit LT project
2597 ;
2598 EXCEPTION
2599 -- Bug 2837366 : krajan : Catch exception
2600 WHEN DUP_VAL_ON_INDEX THEN
2601 IF PG_DEBUG in ('Y', 'C') THEN
2602 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Regions for this customer are already there in the temp table');
2603 END IF;
2604 WHEN NO_DATA_FOUND THEN
2605 IF PG_DEBUG in ('Y', 'C') THEN
2606 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Customer with following customer_site_id does not exist: ');
2607 END IF;
2608 END;
2609
2610 ELSE -- IF nvl(p_customer_site_id,-1) <> -1 THEN -- For supplier intransit LT project
2611
2612 BEGIN
2613 -- Populating region data for supplier site
2614 insert into msc_regions_temp
2615 (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2616 select p_session_id,
2617 p_supplier_site_id,
2618 region_id,
2619 region_type,
2620 null, -- not required anymore because collected data is already translated
2621 l_vendor_type
2622 from msc_region_sites
2623 where vendor_site_id = p_supplier_site_id
2624 and sr_instance_id = p_instance_id;
2625 EXCEPTION
2626 WHEN DUP_VAL_ON_INDEX THEN
2627 IF PG_DEBUG in ('Y', 'C') THEN
2628 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Regions for this supplier are already there in the temp table');
2629 END IF;
2630 WHEN NO_DATA_FOUND THEN
2631 IF PG_DEBUG in ('Y', 'C') THEN
2632 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Region data for the following supplier_site_id does not exist: ' || p_supplier_site_id);
2633 END IF;
2634 END;
2635
2636 END IF; -- IF nvl(p_customer_site_id,-1) <> -1 THEN -- For supplier intransit LT project
2637
2638 END IF;
2639 EXCEPTION
2640 WHEN NO_DATA_FOUND THEN
2641 IF PG_DEBUG in ('Y', 'C') THEN
2642 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Customer with following customer_site_id does not exist: ' || p_customer_site_id);
2643 END IF;
2644 return;
2645 WHEN OTHERS THEN
2646 IF (SQLCODE = -942) THEN
2647 IF PG_DEBUG in ('Y', 'C') THEN
2648 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Table/View doesnt exist');
2649 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Continue as normal');
2650 END IF;
2651 return;
2652 ELSE
2653 IF PG_DEBUG in ('Y', 'C') THEN
2654 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'sqlcode : ' || sqlcode || ' : ' || sqlerrm);
2655 msc_sch_wb.atp_debug('sqlcode : ' || sqlcode || ' : ' || sqlerrm);
2656 msc_sch_wb.atp_debug('Get_Regions_Shipping: ' || 'Error for Customer with customer_site_id : ' || p_customer_site_id);
2657 END IF;
2658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2659
2660 --bug 3425497: delete data from local table
2661 delete msc_regions_temp where session_id = p_session_id;
2662 return;
2663 END IF;
2664 END Get_Regions_Shipping;
2665
2666 PROCEDURE get_src_transit_time (
2667 p_from_org_id IN NUMBER,
2668 p_from_loc_id IN NUMBER,
2669 p_to_org_id IN NUMBER,
2670 p_to_loc_id IN NUMBER,
2671 p_session_id IN NUMBER,
2672 p_partner_site_id IN NUMBER,
2673 x_ship_method IN OUT NOCOPY VARCHAR2,
2674 x_intransit_time OUT NOCOPY NUMBER,
2675 p_partner_type IN NUMBER --2814895
2676 )
2677 IS
2678 l_level NUMBER;
2679 -- Bug 4000425
2680 --l_ship_method VARCHAR2(30) := x_ship_method;
2681
2682 CURSOR c_lead_time
2683 IS
2684 SELECT intransit_time,
2685 ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
2686 FROM mtl_interorg_ship_methods mism,
2687 msc_regions_temp mrt
2688 WHERE mism.from_location_id = p_from_loc_id
2689 AND mism.ship_method = x_ship_method
2690 AND mism.to_region_id = mrt.region_id
2691 AND mrt.session_id = p_session_id
2692 --AND mrt.partner_type = 2 -- For supplier intransit LT project
2693 AND mrt.partner_site_id = p_partner_site_id --2814895
2694 AND mrt.partner_type = NVL(p_partner_type,2)
2695 ORDER BY 2;
2696
2697 CURSOR c_default_lead_time
2698 IS
2699 SELECT ship_method, intransit_time,
2700 ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
2701 FROM mtl_interorg_ship_methods mism,
2702 msc_regions_temp mrt
2703 WHERE mism.from_location_id = p_from_loc_id
2704 AND mism.default_flag = 1
2705 AND mism.to_region_id = mrt.region_id
2706 AND mrt.session_id = p_session_id
2707 AND mrt.partner_site_id = p_partner_site_id
2708 --2814895
2709 AND mrt.partner_type = NVL(p_partner_type,2)
2710 --AND mrt.partner_type = 2 -- For supplier intransit LT project
2711 ORDER BY 3; -- was earlier ordered wrongly by 2. changed it to 3 along with supplier intransit LT changes
2712
2713 BEGIN
2714
2715 IF PG_DEBUG in ('Y', 'C') THEN
2716 msc_sch_wb.atp_debug('********** get_src_transit_time **********');
2717 msc_sch_wb.atp_debug('get_src_transit_time: ' || 'org from: ' || p_from_org_id ||
2718 ' to: ' || p_to_org_id);
2719 msc_sch_wb.atp_debug('get_src_transit_time: ' || 'loc from: ' || p_from_loc_id ||
2720 ' to: ' || p_to_loc_id);
2721 msc_sch_wb.atp_debug('get_src_transit_time: ' || 'session_id: ' || p_session_id);
2722 msc_sch_wb.atp_debug('get_src_transit_time: ' || 'partner_site: ' || p_partner_site_id);
2723 msc_sch_wb.atp_debug('get_src_transit_time: ' || 'ship method: ' || x_ship_method);
2724 END IF;
2725
2726
2727 -- if the receipt org or the ship method is NULL
2728 -- then get the default time
2729
2730 IF p_from_loc_id IS NOT NULL THEN
2731 BEGIN
2732 IF x_ship_method IS NOT NULL THEN
2733 SELECT intransit_time
2734 INTO x_intransit_time
2735 FROM mtl_interorg_ship_methods
2736 WHERE from_location_id = p_from_loc_id
2737 AND to_location_id = p_to_loc_id
2738 AND ship_method = x_ship_method
2739 AND rownum = 1;
2740 ELSE
2741 SELECT ship_method, intransit_time
2742 INTO x_ship_method, x_intransit_time
2743 FROM mtl_interorg_ship_methods
2744 WHERE from_location_id = p_from_loc_id
2745 AND to_location_id = p_to_loc_id
2746 AND default_flag = 1
2747 AND rownum = 1;
2748 END IF;
2749 EXCEPTION WHEN NO_DATA_FOUND THEN
2750 IF PG_DEBUG in ('Y', 'C') THEN
2751 msc_sch_wb.atp_debug('get_src_transit_time: ' || 'Using region level transit times');
2752 END IF;
2753 IF x_ship_method IS NOT NULL THEN
2754 OPEN c_lead_time;
2755 FETCH c_lead_time INTO x_intransit_time, l_level;
2756 CLOSE c_lead_time;
2757 ELSE
2758 OPEN c_default_lead_time;
2759 FETCH c_default_lead_time INTO x_ship_method,
2760 x_intransit_time,
2761 l_level;
2762 CLOSE c_default_lead_time;
2763 END IF;
2764 END;
2765 END IF;
2766
2767 IF (x_intransit_time is NULL AND
2768 p_from_org_id IS NOT NULL AND p_to_org_id IS NOT NULL) THEN
2769
2770 BEGIN
2771 IF x_ship_method IS NOT NULL THEN
2772 select intransit_time
2773 into x_intransit_time
2774 from mtl_interorg_ship_methods
2775 where from_organization_id = p_from_org_id
2776 and to_organization_id = p_to_org_id
2777 and ship_method = x_ship_method
2778 and rownum = 1;
2779 ELSE
2780 select ship_method, intransit_time
2781 into x_ship_method, x_intransit_time
2782 from mtl_interorg_ship_methods
2783 where from_organization_id = p_from_org_id
2784 and to_organization_id = p_to_org_id
2785 and default_flag = 1
2786 and rownum = 1;
2787 END IF;
2788
2789 EXCEPTION WHEN NO_DATA_FOUND THEN
2790 null;
2791 END;
2792 END IF;
2793
2794 IF x_intransit_time IS NULL AND x_ship_method IS NOT NULL THEN
2795 -- call myself with null ship method to get defaults
2796 x_ship_method := NULL;
2797 get_src_transit_time(p_from_org_id,
2798 p_from_loc_id,
2799 p_to_org_id,
2800 p_to_loc_id,
2801 p_session_id,
2802 p_partner_site_id,
2803 x_ship_method,
2804 x_intransit_time,
2805 p_partner_type --2814895
2806 );
2807 /* -- Bug 4000425 Return transit time and ship method as null if not found.
2808 IF x_intransit_time IS NULL THEN
2809 x_intransit_time := 0;
2810 x_ship_method := l_ship_method;
2811 END IF;*/
2812 END IF;
2813
2814 IF PG_DEBUG in ('Y', 'C') THEN
2815 msc_sch_wb.atp_debug('get_src_transit_time: ' || 'transit_time:' || x_intransit_time);
2816 msc_sch_wb.atp_debug('get_src_transit_time: ' || 'ship_method:' || x_ship_method);
2817 END IF;
2818
2819 END get_src_transit_time;
2820
2821 -- Get Regions Wrapper
2822 -- krajan : bug 2359231
2823 -- Call Get_Regions_old or Get_regions_shipping based on the data in the mapping tables
2824 PROCEDURE Get_Regions (
2825 p_customer_site_id IN NUMBER,
2826 p_calling_module IN NUMBER, -- i.e. Source (ERP) or Destination (724)
2827 p_instance_id IN NUMBER,
2828 p_session_id IN NUMBER,
2829 p_dblink IN VARCHAR2,
2830 x_return_status OUT NOCOPY VARCHAR2,
2831 p_location_id IN NUMBER ,
2832 p_location_source IN VARCHAR2,
2833 p_supplier_site_id IN NUMBER,-- For supplier intransit LT project
2834 -- 2814895
2835 -- Adding new address of customer and party_site
2836 p_postal_code IN VARCHAR2,
2837 p_city IN VARCHAR2,
2838 p_state IN VARCHAR2,
2839 p_country IN VARCHAR2,
2840 p_party_site_id IN NUMBER,
2841 p_order_line_id IN NUMBER --2814895, for address parameters
2842 ) IS -- For supplier intransit LT project
2843
2844 l_api_to_use number;
2845
2846 l_temp_var number;
2847 BEGIN
2848 if PG_DEBUG in ('Y','C') then
2849 msc_sch_wb.atp_debug ('---------------Get Regions..................');
2850 msc_sch_wb.atp_debug (' Customer Site ID : ' || p_customer_site_id );
2851 msc_sch_wb.atp_debug (' Calling Module : ' || p_calling_module);
2852 msc_sch_wb.atp_debug (' Instance ID : ' || p_instance_id);
2853 msc_sch_wb.atp_debug (' Session ID : ' || p_session_id);
2854 msc_sch_wb.atp_debug (' DB Link : ' || p_dblink);
2855 msc_sch_wb.atp_debug (' Location ID : ' || p_location_id);
2856 msc_sch_wb.atp_debug (' Location source : ' || p_location_source );
2857 end if;
2858
2859 l_api_to_use := 1;
2860
2861 IF ((p_country is not null) --2814895, use the get_regions_old in case only address parameters are given
2862 AND (p_customer_site_id is NULL)
2863 AND (p_party_site_id is NULL) ) THEN
2864 l_api_to_use := 2;
2865 END IF;
2866
2867 -- If supplier site is passed then always use the new API
2868 IF p_supplier_site_id IS NULL THEN -- For supplier intransit LT project
2869 begin
2870 -- Bug 3497370 - Handle null calling module
2871 -- if (p_calling_module = 724) then
2872 if (nvl(p_calling_module,-99) = 724) then
2873
2874 select 1
2875 into l_temp_var
2876 from msc_region_locations
2877 where sr_instance_id = p_instance_id
2878 and rownum = 1;
2879
2880 else
2881 -- bug 2974334. Change the SQL into static
2882 select 1
2883 into l_temp_var
2884 from wsh_region_locations
2885 where rownum = 1;
2886
2887 end if;
2888 exception
2889 when others then
2890 IF PG_DEBUG in ('Y', 'C') THEN
2891 msc_sch_wb.atp_debug ('Get_Regions: ' || 'Error Code : ' || sqlerrm);
2892 msc_sch_wb.atp_debug ('Get_REgions API: Unable to get status of msc(wsh)_region_locations');
2893 msc_sch_wb.atp_debug ('Get Regions API: Switching to OLD get_regions');
2894 END IF;
2895 l_api_to_use := 2;
2896 end;
2897 end if;
2898
2899 IF PG_DEBUG in ('Y', 'C') THEN
2900 msc_sch_wb.atp_debug ('Get_Regions API: API to Use : ' || l_api_to_use);
2901 END IF;
2902 if (l_api_to_use = 1) then
2903 MSC_SATP_FUNC.Get_Regions_shipping (
2904 p_customer_site_id,
2905 p_calling_module,
2906 p_instance_id,
2907 p_session_id,
2908 p_dblink,
2909 x_return_status,
2910 p_location_id,
2911 p_location_source,
2912 p_supplier_site_id, -- For supplier intransit LT project
2913 p_party_site_id ); --2814895
2914
2915 else
2916 MSC_SATP_FUNC.Get_Regions_Old (
2917 p_customer_site_id,
2918 p_calling_module,
2919 p_instance_id,
2920 p_session_id,
2921 p_dblink,
2922 --2814895
2923 -- Adding new address of customer
2924 p_postal_code,
2925 p_city,
2926 p_state,
2927 p_country,
2928 p_order_line_id,
2929 x_return_status);
2930 end if;
2931 end get_regions;
2932
2933
2934 /* New Extend ATP API
2935 krajan: 05-Aug-2003
2936
2937 Inputs:
2938 MRP_ATP_PUB.atp_rec_typ to extend
2939 p_tot_size : total size to extend to
2940
2941 Working:
2942 p_tot_size is used to calculate the length of the final array:
2943 value > 0 : extend everything to p_tot_size
2944 value = 0 : extend everything to inv. item id + 1 (extend by 1)
2945 value < 0 : extend everything to inv. item id size
2946 */
2947 procedure new_extend_atp (
2948 p_atp_tab IN OUT NOCOPY MRP_ATP_PUB.atp_rec_typ,
2949 p_tot_size IN number,
2950 x_return_status OUT NOCOPY varchar2
2951 )
2952 IS
2953 reclength number;
2954 totlength number;
2955
2956 BEGIN
2957
2958 IF PG_DEBUG in ('Y', 'C') THEN
2959 msc_sch_wb.atp_debug('***** Begin New_Extend_Atp Procedure *****');
2960 END IF;
2961
2962 x_return_status := FND_API.G_RET_STS_SUCCESS;
2963
2964 if p_tot_size > 0 then
2965 totlength := p_tot_size;
2966 elsif p_tot_size = 0 then
2967 totlength := p_atp_tab.inventory_item_id.count + 1;
2968 else
2969 totlength := p_atp_tab.inventory_item_id.count;
2970 end if;
2971
2972
2973 reclength := p_atp_tab.Row_Id.count;
2974 if (reclength < totlength) then
2975 p_atp_tab.Row_Id.extend (totlength - reclength);
2976 end if;
2977
2978
2979 reclength := p_atp_tab.Instance_Id.count;
2980 if (reclength < totlength) then
2981 p_atp_tab.Instance_Id.extend (totlength - reclength);
2982 end if;
2983
2984
2985 reclength := p_atp_tab.Inventory_Item_Id.count;
2986 if (reclength < totlength) then
2987 p_atp_tab.Inventory_Item_Id.extend (totlength - reclength);
2988 end if;
2989
2990
2991 reclength := p_atp_tab.Inventory_Item_Name.count;
2992 if (reclength < totlength) then
2993 p_atp_tab.Inventory_Item_Name.extend (totlength - reclength);
2994 end if;
2995
2996
2997 reclength := p_atp_tab.Source_Organization_Id.count;
2998 if (reclength < totlength) then
2999 p_atp_tab.Source_Organization_Id.extend (totlength - reclength);
3000 end if;
3001
3002
3003 reclength := p_atp_tab.Source_Organization_Code.count;
3004 if (reclength < totlength) then
3005 p_atp_tab.Source_Organization_Code.extend (totlength - reclength);
3006 end if;
3007
3008
3009 reclength := p_atp_tab.Organization_Id.count;
3010 if (reclength < totlength) then
3011 p_atp_tab.Organization_Id.extend (totlength - reclength);
3012 end if;
3013
3014
3015 reclength := p_atp_tab.Identifier.count;
3016 if (reclength < totlength) then
3017 p_atp_tab.Identifier.extend (totlength - reclength);
3018 end if;
3019
3020
3021 reclength := p_atp_tab.Demand_Source_Header_Id.count;
3022 if (reclength < totlength) then
3023 p_atp_tab.Demand_Source_Header_Id.extend (totlength - reclength);
3024 end if;
3025
3026
3027 reclength := p_atp_tab.Demand_Source_Delivery.count;
3028 if (reclength < totlength) then
3029 p_atp_tab.Demand_Source_Delivery.extend (totlength - reclength);
3030 end if;
3031
3032
3033 reclength := p_atp_tab.Demand_Source_Type.count;
3034 if (reclength < totlength) then
3035 p_atp_tab.Demand_Source_Type.extend (totlength - reclength);
3036 end if;
3037
3038
3039 reclength := p_atp_tab.Scenario_Id.count;
3040 if (reclength < totlength) then
3041 p_atp_tab.Scenario_Id.extend (totlength - reclength);
3042 end if;
3043
3044
3045 reclength := p_atp_tab.Calling_Module.count;
3046 if (reclength < totlength) then
3047 p_atp_tab.Calling_Module.extend (totlength - reclength);
3048 end if;
3049
3050
3051 reclength := p_atp_tab.Customer_Id.count;
3052 if (reclength < totlength) then
3053 p_atp_tab.Customer_Id.extend (totlength - reclength);
3054 end if;
3055
3056
3057 reclength := p_atp_tab.Customer_Site_Id.count;
3058 if (reclength < totlength) then
3059 p_atp_tab.Customer_Site_Id.extend (totlength - reclength);
3060 end if;
3061
3062
3063 reclength := p_atp_tab.Destination_Time_Zone.count;
3064 if (reclength < totlength) then
3065 p_atp_tab.Destination_Time_Zone.extend (totlength - reclength);
3066 end if;
3067
3068
3069 reclength := p_atp_tab.Quantity_Ordered.count;
3070 if (reclength < totlength) then
3071 p_atp_tab.Quantity_Ordered.extend (totlength - reclength);
3072 end if;
3073
3074
3075 reclength := p_atp_tab.Quantity_UOM.count;
3076 if (reclength < totlength) then
3077 p_atp_tab.Quantity_UOM.extend (totlength - reclength);
3078 end if;
3079
3080
3081 reclength := p_atp_tab.Requested_Ship_Date.count;
3082 if (reclength < totlength) then
3083 p_atp_tab.Requested_Ship_Date.extend (totlength - reclength);
3084 end if;
3085
3086
3087 reclength := p_atp_tab.Requested_Arrival_Date.count;
3088 if (reclength < totlength) then
3089 p_atp_tab.Requested_Arrival_Date.extend (totlength - reclength);
3090 end if;
3091
3092
3093 reclength := p_atp_tab.Earliest_Acceptable_Date.count;
3094 if (reclength < totlength) then
3095 p_atp_tab.Earliest_Acceptable_Date.extend (totlength - reclength);
3096 end if;
3097
3098
3099 reclength := p_atp_tab.Latest_Acceptable_Date.count;
3100 if (reclength < totlength) then
3101 p_atp_tab.Latest_Acceptable_Date.extend (totlength - reclength);
3102 end if;
3103
3104
3105 reclength := p_atp_tab.Delivery_Lead_Time.count;
3106 if (reclength < totlength) then
3107 p_atp_tab.Delivery_Lead_Time.extend (totlength - reclength);
3108 end if;
3109
3110
3111 reclength := p_atp_tab.Freight_Carrier.count;
3112 if (reclength < totlength) then
3113 p_atp_tab.Freight_Carrier.extend (totlength - reclength);
3114 end if;
3115
3116
3117 reclength := p_atp_tab.Ship_Method.count;
3118 if (reclength < totlength) then
3119 p_atp_tab.Ship_Method.extend (totlength - reclength);
3120 end if;
3121
3122
3123 reclength := p_atp_tab.Demand_Class.count;
3124 if (reclength < totlength) then
3125 p_atp_tab.Demand_Class.extend (totlength - reclength);
3126 end if;
3127
3128
3129 reclength := p_atp_tab.Ship_Set_Name.count;
3130 if (reclength < totlength) then
3131 p_atp_tab.Ship_Set_Name.extend (totlength - reclength);
3132 end if;
3133
3134
3135 reclength := p_atp_tab.Arrival_Set_Name.count;
3136 if (reclength < totlength) then
3137 p_atp_tab.Arrival_Set_Name.extend (totlength - reclength);
3138 end if;
3139
3140
3141 reclength := p_atp_tab.Override_Flag.count;
3142 if (reclength < totlength) then
3143 p_atp_tab.Override_Flag.extend (totlength - reclength);
3144 end if;
3145
3146
3147 reclength := p_atp_tab.Action.count;
3148 if (reclength < totlength) then
3149 p_atp_tab.Action.extend (totlength - reclength);
3150 end if;
3151
3152
3153 reclength := p_atp_tab.Ship_Date.count;
3154 if (reclength < totlength) then
3155 p_atp_tab.Ship_Date.extend (totlength - reclength);
3156 end if;
3157
3158
3159 reclength := p_atp_tab.Arrival_date.count;
3160 if (reclength < totlength) then
3161 p_atp_tab.Arrival_date.extend (totlength - reclength);
3162 end if;
3163
3164 reclength := p_atp_tab.original_request_date.count;
3165 if (reclength < totlength) then
3166 p_atp_tab.original_request_date.extend (totlength - reclength);
3167 end if;
3168
3169 reclength := p_atp_tab.Available_Quantity.count;
3170 if (reclength < totlength) then
3171 p_atp_tab.Available_Quantity.extend (totlength - reclength);
3172 end if;
3173
3174
3175 reclength := p_atp_tab.Requested_Date_Quantity.count;
3176 if (reclength < totlength) then
3177 p_atp_tab.Requested_Date_Quantity.extend (totlength - reclength);
3178 end if;
3179
3180
3181 reclength := p_atp_tab.Group_Ship_Date.count;
3182 if (reclength < totlength) then
3183 p_atp_tab.Group_Ship_Date.extend (totlength - reclength);
3184 end if;
3185
3186
3187 reclength := p_atp_tab.Group_Arrival_Date.count;
3188 if (reclength < totlength) then
3189 p_atp_tab.Group_Arrival_Date.extend (totlength - reclength);
3190 end if;
3191
3192
3193 reclength := p_atp_tab.Vendor_Id.count;
3194 if (reclength < totlength) then
3195 p_atp_tab.Vendor_Id.extend (totlength - reclength);
3196 end if;
3197
3198
3199 reclength := p_atp_tab.Vendor_Name.count;
3200 if (reclength < totlength) then
3201 p_atp_tab.Vendor_Name.extend (totlength - reclength);
3202 end if;
3203
3204
3205 reclength := p_atp_tab.Vendor_Site_Id.count;
3206 if (reclength < totlength) then
3207 p_atp_tab.Vendor_Site_Id.extend (totlength - reclength);
3208 end if;
3209
3210
3211 reclength := p_atp_tab.Vendor_Site_Name.count;
3212 if (reclength < totlength) then
3213 p_atp_tab.Vendor_Site_Name.extend (totlength - reclength);
3214 end if;
3215
3216
3217 reclength := p_atp_tab.Insert_Flag.count;
3218 if (reclength < totlength) then
3219 p_atp_tab.Insert_Flag.extend (totlength - reclength);
3220 end if;
3221
3222
3223 reclength := p_atp_tab.OE_Flag.count;
3224 if (reclength < totlength) then
3225 p_atp_tab.OE_Flag.extend (totlength - reclength);
3226 end if;
3227
3228
3229 reclength := p_atp_tab.Atp_Lead_Time.count;
3230 if (reclength < totlength) then
3231 p_atp_tab.Atp_Lead_Time.extend (totlength - reclength);
3232 end if;
3233
3234
3235 reclength := p_atp_tab.Error_Code.count;
3236 if (reclength < totlength) then
3237 p_atp_tab.Error_Code.extend (totlength - reclength);
3238 end if;
3239
3240
3241 reclength := p_atp_tab.Message.count;
3242 if (reclength < totlength) then
3243 p_atp_tab.Message.extend (totlength - reclength);
3244 end if;
3245
3246
3247 reclength := p_atp_tab.End_Pegging_Id.count;
3248 if (reclength < totlength) then
3249 p_atp_tab.End_Pegging_Id.extend (totlength - reclength);
3250 end if;
3251
3252
3253 reclength := p_atp_tab.Order_Number.count;
3254 if (reclength < totlength) then
3255 p_atp_tab.Order_Number.extend (totlength - reclength);
3256 end if;
3257
3258
3259 reclength := p_atp_tab.Old_Source_Organization_Id.count;
3260 if (reclength < totlength) then
3261 p_atp_tab.Old_Source_Organization_Id.extend (totlength - reclength);
3262 end if;
3263
3264
3265 reclength := p_atp_tab.Old_Demand_Class.count;
3266 if (reclength < totlength) then
3267 p_atp_tab.Old_Demand_Class.extend (totlength - reclength);
3268 end if;
3269
3270
3271 reclength := p_atp_tab.ato_delete_flag.count;
3272 if (reclength < totlength) then
3273 p_atp_tab.ato_delete_flag.extend (totlength - reclength);
3274 end if;
3275
3276
3277 reclength := p_atp_tab.attribute_01.count;
3278 if (reclength < totlength) then
3279 p_atp_tab.attribute_01.extend (totlength - reclength);
3280 end if;
3281
3282
3283 reclength := p_atp_tab.attribute_02.count;
3284 if (reclength < totlength) then
3285 p_atp_tab.attribute_02.extend (totlength - reclength);
3286 end if;
3287
3288
3289 reclength := p_atp_tab.attribute_03.count;
3290 if (reclength < totlength) then
3291 p_atp_tab.attribute_03.extend (totlength - reclength);
3292 end if;
3293
3294
3295 reclength := p_atp_tab.attribute_04.count;
3296 if (reclength < totlength) then
3297 p_atp_tab.attribute_04.extend (totlength - reclength);
3298 end if;
3299
3300
3301 reclength := p_atp_tab.attribute_05.count;
3302 if (reclength < totlength) then
3303 p_atp_tab.attribute_05.extend (totlength - reclength);
3304 end if;
3305
3306
3307 reclength := p_atp_tab.attribute_06.count;
3308 if (reclength < totlength) then
3309 p_atp_tab.attribute_06.extend (totlength - reclength);
3310 end if;
3311
3312
3313 reclength := p_atp_tab.attribute_07.count;
3314 if (reclength < totlength) then
3315 p_atp_tab.attribute_07.extend (totlength - reclength);
3316 end if;
3317
3318
3319 reclength := p_atp_tab.attribute_08.count;
3320 if (reclength < totlength) then
3321 p_atp_tab.attribute_08.extend (totlength - reclength);
3322 end if;
3323
3324
3325 reclength := p_atp_tab.attribute_09.count;
3326 if (reclength < totlength) then
3327 p_atp_tab.attribute_09.extend (totlength - reclength);
3328 end if;
3329
3330
3331 reclength := p_atp_tab.attribute_10.count;
3332 if (reclength < totlength) then
3333 p_atp_tab.attribute_10.extend (totlength - reclength);
3334 end if;
3335
3336
3337 reclength := p_atp_tab.customer_name.count;
3338 if (reclength < totlength) then
3339 p_atp_tab.customer_name.extend (totlength - reclength);
3340 end if;
3341
3342
3343 reclength := p_atp_tab.customer_class.count;
3344 if (reclength < totlength) then
3345 p_atp_tab.customer_class.extend (totlength - reclength);
3346 end if;
3347
3348
3349 reclength := p_atp_tab.customer_location.count;
3350 if (reclength < totlength) then
3351 p_atp_tab.customer_location.extend (totlength - reclength);
3352 end if;
3353
3354
3355 reclength := p_atp_tab.customer_country.count;
3356 if (reclength < totlength) then
3357 p_atp_tab.customer_country.extend (totlength - reclength);
3358 end if;
3359
3360
3361 reclength := p_atp_tab.customer_state.count;
3362 if (reclength < totlength) then
3363 p_atp_tab.customer_state.extend (totlength - reclength);
3364 end if;
3365
3366
3367 reclength := p_atp_tab.customer_city.count;
3368 if (reclength < totlength) then
3369 p_atp_tab.customer_city.extend (totlength - reclength);
3370 end if;
3371
3372
3373 reclength := p_atp_tab.customer_postal_code.count;
3374 if (reclength < totlength) then
3375 p_atp_tab.customer_postal_code.extend (totlength - reclength);
3376 end if;
3377
3378 --2814895
3379 reclength := p_atp_tab.party_site_id.count;
3380 if (reclength < totlength) then
3381 p_atp_tab.party_site_id.extend (totlength - reclength);
3382 end if;
3383
3384 reclength := p_atp_tab.substitution_typ_code.count;
3385 if (reclength < totlength) then
3386 p_atp_tab.substitution_typ_code.extend (totlength - reclength);
3387 end if;
3388
3389
3390 reclength := p_atp_tab.req_item_detail_flag.count;
3391 if (reclength < totlength) then
3392 p_atp_tab.req_item_detail_flag.extend (totlength - reclength);
3393 end if;
3394
3395
3396 reclength := p_atp_tab.request_item_id.count;
3397 if (reclength < totlength) then
3398 p_atp_tab.request_item_id.extend (totlength - reclength);
3399 end if;
3400
3401
3402 reclength := p_atp_tab.req_item_req_date_qty.count;
3403 if (reclength < totlength) then
3404 p_atp_tab.req_item_req_date_qty.extend (totlength - reclength);
3405 end if;
3406
3407
3408 reclength := p_atp_tab.req_item_available_date.count;
3409 if (reclength < totlength) then
3410 p_atp_tab.req_item_available_date.extend (totlength - reclength);
3411 end if;
3412
3413
3414 reclength := p_atp_tab.req_item_available_date_qty.count;
3415 if (reclength < totlength) then
3416 p_atp_tab.req_item_available_date_qty.extend (totlength - reclength);
3417 end if;
3418
3419
3420 reclength := p_atp_tab.request_item_name.count;
3421 if (reclength < totlength) then
3422 p_atp_tab.request_item_name.extend (totlength - reclength);
3423 end if;
3424
3425
3426 reclength := p_atp_tab.old_inventory_item_id.count;
3427 if (reclength < totlength) then
3428 p_atp_tab.old_inventory_item_id.extend (totlength - reclength);
3429 end if;
3430
3431
3432 reclength := p_atp_tab.sales_rep.count;
3433 if (reclength < totlength) then
3434 p_atp_tab.sales_rep.extend (totlength - reclength);
3435 end if;
3436
3437
3438 reclength := p_atp_tab.customer_contact.count;
3439 if (reclength < totlength) then
3440 p_atp_tab.customer_contact.extend (totlength - reclength);
3441 end if;
3442
3443
3444 reclength := p_atp_tab.subst_flag.count;
3445 if (reclength < totlength) then
3446 p_atp_tab.subst_flag.extend (totlength - reclength);
3447 end if;
3448
3449
3450 reclength := p_atp_tab.Top_Model_line_id.count;
3451 if (reclength < totlength) then
3452 p_atp_tab.Top_Model_line_id.extend (totlength - reclength);
3453 end if;
3454
3455
3456 reclength := p_atp_tab.ATO_Parent_Model_Line_Id.count;
3457 if (reclength < totlength) then
3458 p_atp_tab.ATO_Parent_Model_Line_Id.extend (totlength - reclength);
3459 end if;
3460
3461
3462 reclength := p_atp_tab.ATO_Model_Line_Id.count;
3463 if (reclength < totlength) then
3464 p_atp_tab.ATO_Model_Line_Id.extend (totlength - reclength);
3465 end if;
3466
3467
3468 reclength := p_atp_tab.Parent_line_id.count;
3469 if (reclength < totlength) then
3470 p_atp_tab.Parent_line_id.extend (totlength - reclength);
3471 end if;
3472
3473
3474 reclength := p_atp_tab.match_item_id.count;
3475 if (reclength < totlength) then
3476 p_atp_tab.match_item_id.extend (totlength - reclength);
3477 end if;
3478
3479 reclength := p_atp_tab.matched_item_name.count;
3480 if (reclength < totlength) then
3481 p_atp_tab.matched_item_name.extend (totlength - reclength);
3482 end if;
3483
3484
3485 reclength := p_atp_tab.Config_item_line_id.count;
3486 if (reclength < totlength) then
3487 p_atp_tab.Config_item_line_id.extend (totlength - reclength);
3488 end if;
3489
3490
3491 reclength := p_atp_tab.Validation_Org.count;
3492 if (reclength < totlength) then
3493 p_atp_tab.Validation_Org.extend (totlength - reclength);
3494 end if;
3495
3496
3497 reclength := p_atp_tab.Component_Sequence_ID.count;
3498 if (reclength < totlength) then
3499 p_atp_tab.Component_Sequence_ID.extend (totlength - reclength);
3500 end if;
3501
3502
3503 reclength := p_atp_tab.Component_Code.count;
3504 if (reclength < totlength) then
3505 p_atp_tab.Component_Code.extend (totlength - reclength);
3506 end if;
3507
3508
3509 reclength := p_atp_tab.line_number.count;
3510 if (reclength < totlength) then
3511 p_atp_tab.line_number.extend (totlength - reclength);
3512 end if;
3513
3514
3515 reclength := p_atp_tab.included_item_flag.count;
3516 if (reclength < totlength) then
3517 p_atp_tab.included_item_flag.extend (totlength - reclength);
3518 end if;
3519
3520 reclength := p_atp_tab.atp_flag.count;
3521 if (reclength < totlength) then
3522 p_atp_tab.atp_flag.extend (totlength - reclength);
3523 end if;
3524
3525 reclength := p_atp_tab.atp_components_flag.count;
3526 if (reclength < totlength) then
3527 p_atp_tab.atp_components_flag.extend (totlength - reclength);
3528 end if;
3529
3530 reclength := p_atp_tab.wip_supply_type.count;
3531 if (reclength < totlength) then
3532 p_atp_tab.wip_supply_type.extend (totlength - reclength);
3533 end if;
3534
3535 reclength := p_atp_tab.bom_item_type.count;
3536 if (reclength < totlength) then
3537 p_atp_tab.bom_item_type.extend (totlength - reclength);
3538 end if;
3539
3540 reclength := p_atp_tab.mandatory_item_flag.count;
3541 if (reclength < totlength) then
3542 p_atp_tab.mandatory_item_flag.extend (totlength - reclength);
3543 end if;
3544
3545 reclength := p_atp_tab.pick_components_flag.count;
3546 if (reclength < totlength) then
3547 p_atp_tab.pick_components_flag.extend (totlength - reclength);
3548 end if;
3549
3550 reclength := p_atp_tab.base_model_id.count;
3551 if (reclength < totlength) then
3552 p_atp_tab.base_model_id.extend (totlength - reclength);
3553 end if;
3554
3555
3556 reclength := p_atp_tab.OSS_ERROR_CODE.count;
3557 if (reclength < totlength) then
3558 p_atp_tab.OSS_ERROR_CODE.extend (totlength - reclength);
3559 end if;
3560
3561 reclength := p_atp_tab.matched_item_name.count;
3562 if (reclength < totlength) then
3563 p_atp_tab.matched_item_name.extend (totlength - reclength);
3564 end if;
3565
3566 reclength := p_atp_tab.cascade_model_info_to_comp.count;
3567 if (reclength < totlength) then
3568 p_atp_tab.cascade_model_info_to_comp.extend (totlength - reclength);
3569 end if;
3570
3571 reclength := p_atp_tab.firm_flag.count;
3572 if (reclength < totlength) then
3573 p_atp_tab.firm_flag.extend (totlength - reclength);
3574 end if;
3575
3576 reclength := p_atp_tab.order_line_number.count;
3577 if (reclength < totlength) then
3578 p_atp_tab.order_line_number.extend (totlength - reclength);
3579 end if;
3580
3581 reclength := p_atp_tab.option_number.count;
3582 if (reclength < totlength) then
3583 p_atp_tab.option_number.extend (totlength - reclength);
3584 end if;
3585
3586 reclength := p_atp_tab.shipment_number.count;
3587 if (reclength < totlength) then
3588 p_atp_tab.shipment_number.extend (totlength - reclength);
3589 end if;
3590
3591 reclength := p_atp_tab.item_desc.count;
3592 if (reclength < totlength) then
3593 p_atp_tab.item_desc.extend (totlength - reclength);
3594 end if;
3595
3596 reclength := p_atp_tab.old_line_schedule_date.count;
3597 if (reclength < totlength) then
3598 p_atp_tab.old_line_schedule_date.extend (totlength - reclength);
3599 end if;
3600
3601 reclength := p_atp_tab.old_source_organization_code.count;
3602 if (reclength < totlength) then
3603 p_atp_tab.old_source_organization_code.extend (totlength - reclength);
3604 end if;
3605
3606 reclength := p_atp_tab.firm_source_org_id.count;
3607 if (reclength < totlength) then
3608 p_atp_tab.firm_source_org_id.extend (totlength - reclength);
3609 end if;
3610
3611 reclength := p_atp_tab.firm_source_org_code.count;
3612 if (reclength < totlength) then
3613 p_atp_tab.firm_source_org_code.extend (totlength - reclength);
3614 end if;
3615
3616 reclength := p_atp_tab.firm_ship_date.count;
3617 if (reclength < totlength) then
3618 p_atp_tab.firm_ship_date.extend (totlength - reclength);
3619 end if;
3620
3621 reclength := p_atp_tab.firm_arrival_date.count;
3622 if (reclength < totlength) then
3623 p_atp_tab.firm_arrival_date.extend (totlength - reclength);
3624 end if;
3625
3626 reclength := p_atp_tab.ship_method_text.count;
3627 if (reclength < totlength) then
3628 p_atp_tab.ship_method_text.extend (totlength - reclength);
3629 end if;
3630
3631 reclength := p_atp_tab.ship_set_id.count;
3632 if (reclength < totlength) then
3633 p_atp_tab.ship_set_id.extend (totlength - reclength);
3634 end if;
3635
3636 reclength := p_atp_tab.arrival_set_id.count;
3637 if (reclength < totlength) then
3638 p_atp_tab.arrival_set_id.extend (totlength - reclength);
3639 end if;
3640
3641 reclength := p_atp_tab.PROJECT_ID.count;
3642 if (reclength < totlength) then
3643 p_atp_tab.PROJECT_ID.extend (totlength - reclength);
3644 end if;
3645
3646 reclength := p_atp_tab.TASK_ID.count;
3647 if (reclength < totlength) then
3648 p_atp_tab.TASK_ID.extend (totlength - reclength);
3649 end if;
3650
3651 reclength := p_atp_tab.PROJECT_NUMBER.count;
3652 if (reclength < totlength) then
3653 p_atp_tab.PROJECT_NUMBER.extend (totlength - reclength);
3654 end if;
3655
3656 reclength := p_atp_tab.TASK_NUMBER.count;
3657 if (reclength < totlength) then
3658 p_atp_tab.TASK_NUMBER.extend (totlength - reclength);
3659 end if;
3660
3661 reclength := p_atp_tab.attribute_11.count;
3662 if (reclength < totlength) then
3663 p_atp_tab.attribute_11.extend (totlength - reclength);
3664 end if;
3665
3666
3667 reclength := p_atp_tab.attribute_12.count;
3668 if (reclength < totlength) then
3669 p_atp_tab.attribute_12.extend (totlength - reclength);
3670 end if;
3671
3672
3673 reclength := p_atp_tab.attribute_13.count;
3674 if (reclength < totlength) then
3675 p_atp_tab.attribute_13.extend (totlength - reclength);
3676 end if;
3677
3678
3679 reclength := p_atp_tab.attribute_14.count;
3680 if (reclength < totlength) then
3681 p_atp_tab.attribute_14.extend (totlength - reclength);
3682 end if;
3683
3684
3685 reclength := p_atp_tab.attribute_15.count;
3686 if (reclength < totlength) then
3687 p_atp_tab.attribute_15.extend (totlength - reclength);
3688 end if;
3689
3690
3691 reclength := p_atp_tab.attribute_16.count;
3692 if (reclength < totlength) then
3693 p_atp_tab.attribute_16.extend (totlength - reclength);
3694 end if;
3695
3696
3697 reclength := p_atp_tab.attribute_17.count;
3698 if (reclength < totlength) then
3699 p_atp_tab.attribute_17.extend (totlength - reclength);
3700 end if;
3701
3702
3703 reclength := p_atp_tab.attribute_18.count;
3704 if (reclength < totlength) then
3705 p_atp_tab.attribute_18.extend (totlength - reclength);
3706 end if;
3707
3708
3709 reclength := p_atp_tab.attribute_19.count;
3710 if (reclength < totlength) then
3711 p_atp_tab.attribute_19.extend (totlength - reclength);
3712 end if;
3713
3714
3715 reclength := p_atp_tab.attribute_20.count;
3716 if (reclength < totlength) then
3717 p_atp_tab.attribute_20.extend (totlength - reclength);
3718 end if;
3719
3720
3721 reclength := p_atp_tab.Attribute_21.count;
3722 if (reclength < totlength) then
3723 p_atp_tab.Attribute_21.extend (totlength - reclength);
3724 end if;
3725
3726
3727 reclength := p_atp_tab.attribute_22.count;
3728 if (reclength < totlength) then
3729 p_atp_tab.attribute_22.extend (totlength - reclength);
3730 end if;
3731
3732
3733 reclength := p_atp_tab.attribute_23.count;
3734 if (reclength < totlength) then
3735 p_atp_tab.attribute_23.extend (totlength - reclength);
3736 end if;
3737
3738
3739 reclength := p_atp_tab.attribute_24.count;
3740 if (reclength < totlength) then
3741 p_atp_tab.attribute_24.extend (totlength - reclength);
3742 end if;
3743
3744
3745 reclength := p_atp_tab.attribute_25.count;
3746 if (reclength < totlength) then
3747 p_atp_tab.attribute_25.extend (totlength - reclength);
3748 end if;
3749
3750
3751 reclength := p_atp_tab.attribute_26.count;
3752 if (reclength < totlength) then
3753 p_atp_tab.attribute_26.extend (totlength - reclength);
3754 end if;
3755
3756
3757 reclength := p_atp_tab.attribute_27.count;
3758 if (reclength < totlength) then
3759 p_atp_tab.attribute_27.extend (totlength - reclength);
3760 end if;
3761
3762
3763 reclength := p_atp_tab.attribute_28.count;
3764 if (reclength < totlength) then
3765 p_atp_tab.attribute_28.extend (totlength - reclength);
3766 end if;
3767
3768
3769 reclength := p_atp_tab.attribute_29.count;
3770 if (reclength < totlength) then
3771 p_atp_tab.attribute_29.extend (totlength - reclength);
3772 end if;
3773
3774
3775 reclength := p_atp_tab.attribute_30.count;
3776 if (reclength < totlength) then
3777 p_atp_tab.attribute_30.extend (totlength - reclength);
3778 end if;
3779
3780 reclength := p_atp_tab.atf_date.count;
3781 if (reclength < totlength) then
3782 p_atp_tab.atf_date.extend (totlength - reclength);
3783 end if;
3784
3785 reclength := p_atp_tab.plan_id.count;
3786 if (reclength < totlength) then
3787 p_atp_tab.plan_id.extend (totlength - reclength);
3788 end if;
3789
3790 reclength := p_atp_tab.original_request_date.count;
3791 if (reclength < totlength) then
3792 p_atp_tab.original_request_date.extend (totlength - reclength);
3793 end if;
3794
3795 reclength := p_atp_tab.receiving_cal_code.count;
3796 if (reclength < totlength) then
3797 p_atp_tab.receiving_cal_code.extend (totlength - reclength);
3798 end if;
3799
3800 reclength := p_atp_tab.intransit_cal_code.count;
3801 if (reclength < totlength) then
3802 p_atp_tab.intransit_cal_code.extend (totlength - reclength);
3803 end if;
3804
3805 reclength := p_atp_tab.shipping_cal_code.count;
3806 if (reclength < totlength) then
3807 p_atp_tab.shipping_cal_code.extend (totlength - reclength);
3808 end if;
3809
3810 reclength := p_atp_tab.manufacturing_cal_code.count;
3811 if (reclength < totlength) then
3812 p_atp_tab.manufacturing_cal_code.extend (totlength - reclength);
3813 end if;
3814
3815 -- Bug 3449812
3816 reclength := p_atp_tab.internal_org_id.count;
3817 if (reclength < totlength) then
3818 p_atp_tab.internal_org_id.extend (totlength - reclength);
3819 end if;
3820
3821 -- Bug 3328421
3822 reclength := p_atp_tab.first_valid_ship_arrival_date.count;
3823 if (reclength < totlength) then
3824 p_atp_tab.first_valid_ship_arrival_date.extend (totlength - reclength);
3825 end if;
3826 --4500382 Starts
3827 reclength := p_atp_tab.part_of_set.count;
3828 if (reclength < totlength) then
3829 p_atp_tab.part_of_set.extend (totlength - reclength);
3830 end if;
3831 --4500382 ENDS
3832
3833 EXCEPTION
3834 when others then
3835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3836 msc_sch_wb.atp_debug ('Exception in New extend');
3837 msc_sch_wb.atp_debug ('Sqlcode ' || sqlcode);
3838 msc_sch_wb.atp_debug ('Sqlerr ' || sqlerrm);
3839 END new_extend_atp;
3840
3841 /*--------------------------------------------------------------------------
3842 | Begin Functions added for ship_rec_cal project
3843 +-------------------------------------------------------------------------*/
3844
3845 FUNCTION Src_Get_Calendar_Code(
3846 p_customer_id IN number,
3847 p_customer_site_id IN number,
3848 p_organization_id IN number,
3849 p_ship_method_code IN varchar2,
3850 p_calendar_type IN integer -- One of OSC, CRC or VIC
3851 ) RETURN VARCHAR2
3852 IS
3853 l_calendar_code VARCHAR2(14) := MSC_CALENDAR.FOC;
3854 l_ship_method_code VARCHAR2(50) := NVL(p_ship_method_code, '@@@');
3855 l_customer_site_id NUMBER := NVL(p_customer_site_id, -1);
3856 l_calendar_type VARCHAR2(15); -- Bug 3449812
3857
3858 BEGIN
3859 IF PG_DEBUG in ('Y','C') THEN
3860 msc_sch_wb.atp_debug ('***** Begin Function Src_Get_Calendar_Code *****');
3861 msc_sch_wb.atp_debug ('________________Input________________');
3862 msc_sch_wb.atp_debug (' Customer ID : ' || p_customer_id );
3863 msc_sch_wb.atp_debug (' Customer Site ID : ' || p_customer_site_id );
3864 msc_sch_wb.atp_debug (' Organization ID : ' || p_organization_id);
3865 msc_sch_wb.atp_debug (' Ship Method Code : ' || p_ship_method_code);
3866 msc_sch_wb.atp_debug (' Calendar Type : ' || p_calendar_type);
3867 msc_sch_wb.atp_debug (' G_USE_SHIP_REC_CAL : ' || MSC_ATP_PVT.G_USE_SHIP_REC_CAL);
3868 msc_sch_wb.atp_debug (' ');
3869 END IF;
3870
3871 -- Bug 3647208 - Move the check inside individual "IF"s
3872 -- IF MSC_ATP_PVT.G_USE_SHIP_REC_CAL='Y' THEN
3873 -- l_calendar_code is already initialized to FOC
3874
3875 -- case 1. Searching for a valid customer receiving calendar (CRC)
3876 IF (p_calendar_type = MSC_CALENDAR.CRC) THEN
3877
3878 -- Bug 3647208 - Move the check inside individual "IF"s
3879 IF MSC_ATP_PVT.G_USE_SHIP_REC_CAL='Y' THEN
3880 -- l_calendar_code is already initialized to FOC
3881
3882 -- Using the fact the length of association_types carrier_customer_site (21),
3883 -- carrier_customer (16), customer_site (13), customer (8) can be used to order by
3884
3885 SELECT calendar_code
3886 INTO l_calendar_code
3887 FROM (SELECT wca.CALENDAR_CODE
3888 FROM WSH_CARRIERS wc,
3889 WSH_CALENDAR_ASSIGNMENTS wca,
3890 WSH_CARRIER_SERVICES wcs,
3891 WSH_CARRIER_SERVICES wcs1
3892 WHERE wc.FREIGHT_CODE(+) = wca.FREIGHT_CODE
3893 AND wc.CARRIER_ID = wcs.CARRIER_ID(+)
3894 AND wca.CARRIER_ID = wcs1.CARRIER_ID(+)
3895 AND wca.ENABLED_FLAG = 'Y'
3896 AND wca.CUSTOMER_ID = p_customer_id
3897 AND wca.CALENDAR_TYPE in ('RECEIVING', 'CARRIER')
3898 AND NVL(wca.CUSTOMER_SITE_USE_ID, l_customer_site_id) = l_customer_site_id
3899 AND NVL(decode(wca.ASSOCIATION_TYPE,
3900 'CARRIER',wcs1.SHIP_METHOD_CODE,
3901 'CARRIER_SITE',wcs1.SHIP_METHOD_CODE,
3902 wcs.SHIP_METHOD_CODE),
3903 l_ship_method_code) = l_ship_method_code
3904 AND wca.ASSOCIATION_TYPE in ('VENDOR_SITE','CUSTOMER_SITE','VENDOR','CUSTOMER','ORGANIZATION','CARRIER')
3905 ORDER BY LENGTH(decode(wca.association_type,
3906 'CUSTOMER', decode(wca.CALENDAR_TYPE,'CARRIER','CARRIER_CUSTOMER','CUSTOMER'),
3907 'CUSTOMER_SITE',decode(wca.CALENDAR_TYPE,'CARRIER','CARRIER_CUSTOMER_SITE','CUSTOMER_SITE'))) DESC)
3908 WHERE ROWNUM = 1;
3909
3910 END IF;
3911
3912 -- case 2. Org's Shipping Calendar (OSC)
3913 -- Bug 3449812 - Added support for ORC for ISOs
3914 ELSIF (p_calendar_type = MSC_CALENDAR.OSC OR p_calendar_type = MSC_CALENDAR.ORC) THEN
3915
3916 -- Bug 3647208 - Move the check inside individual "IF"s
3917 IF MSC_ATP_PVT.G_USE_SHIP_REC_CAL='Y' THEN
3918
3919 -- Using the fact the length of association_types carrier_organization (20),
3920 -- organization (12) can be used to order by
3921
3922 IF p_calendar_type = MSC_CALENDAR.ORC THEN
3923 l_calendar_type := 'RECEIVING';
3924 ELSE
3925 l_calendar_type := 'SHIPPING';
3926 END IF;
3927
3928 SELECT calendar_code
3929 INTO l_calendar_code
3930 FROM (SELECT wca.CALENDAR_CODE
3931 FROM WSH_CARRIERS wc,
3932 WSH_CALENDAR_ASSIGNMENTS wca,
3933 WSH_CARRIER_SERVICES wcs,
3934 WSH_CARRIER_SERVICES wcs1
3935 WHERE wc.FREIGHT_CODE(+) = wca.FREIGHT_CODE
3936 AND wc.CARRIER_ID = wcs.CARRIER_ID(+)
3937 AND wca.CARRIER_ID = wcs1.CARRIER_ID(+)
3938 AND wca.ENABLED_FLAG = 'Y'
3939 AND wca.ORGANIZATION_ID = p_organization_id
3940 AND wca.CALENDAR_TYPE in (l_calendar_type, 'CARRIER') -- Bug 3449812
3941 AND NVL(decode(wca.ASSOCIATION_TYPE,
3942 'CARRIER',wcs1.SHIP_METHOD_CODE,
3943 'CARRIER_SITE',wcs1.SHIP_METHOD_CODE,
3944 wcs.SHIP_METHOD_CODE),
3945 l_ship_method_code) = l_ship_method_code
3946 AND wca.ASSOCIATION_TYPE in ('VENDOR_SITE','CUSTOMER_SITE','VENDOR','CUSTOMER','ORGANIZATION','CARRIER')
3947 ORDER BY LENGTH(decode(wca.association_type,
3948 'ORGANIZATION', decode(wca.CALENDAR_TYPE,'CARRIER','CARRIER_ORGANIZATION','ORGANIZATION'))) DESC)
3949 WHERE ROWNUM = 1;
3950
3951 ELSE
3952
3953 -- Bug 3647208 - For b/w compatibility use OMC instead of ORC/OSC
3954 -- Raise exception so that the OMC query gets executed.
3955 IF PG_DEBUG in ('Y','C') THEN
3956 msc_sch_wb.atp_debug ('Src_Get_Calendar_Code :' || ' Use OMC instead on ORC/OSC');
3957 END IF;
3958 RAISE NO_DATA_FOUND;
3959
3960 END IF;
3961
3962 -- case 3. Searching for valid Intransit Calendar (VIC)
3963 ELSIF (p_calendar_type = MSC_CALENDAR.VIC) THEN
3964
3965 -- Bug 3647208 - Move the check inside individual "IF"s
3966 IF MSC_ATP_PVT.G_USE_SHIP_REC_CAL='Y' THEN
3967 -- l_calendar_code is already initialized to FOC
3968
3969 SELECT wca.CALENDAR_CODE
3970 INTO l_calendar_code
3971 FROM WSH_CALENDAR_ASSIGNMENTS wca,
3972 WSH_CARRIER_SERVICES wcs
3973 WHERE wca.CARRIER_ID = wcs.CARRIER_ID
3974 AND wca.CALENDAR_TYPE = 'CARRIER'
3975 AND wca.ASSOCIATION_TYPE = 'CARRIER'
3976 AND wca.ENABLED_FLAG = 'Y'
3977 AND NVL(wcs.SHIP_METHOD_CODE, l_ship_method_code) = l_ship_method_code;
3978
3979 END IF;
3980
3981 END IF;
3982
3983 IF PG_DEBUG in ('Y','C') THEN
3984 msc_sch_wb.atp_debug ('________________Output________________');
3985 msc_sch_wb.atp_debug (' Calendar Code : ' || l_calendar_code);
3986 msc_sch_wb.atp_debug (' ');
3987 END IF;
3988
3989 RETURN l_calendar_code;
3990
3991 EXCEPTION
3992 WHEN NO_DATA_FOUND THEN
3993 -- Bug 3449812 - Added support for ORC for ISOs
3994 IF (p_calendar_type = MSC_CALENDAR.ORC OR p_calendar_type = MSC_CALENDAR.OSC) THEN
3995 -- Return OMC.
3996 SELECT calendar_code
3997 INTO l_calendar_code
3998 FROM MTL_PARAMETERS
3999 WHERE ORGANIZATION_ID = p_organization_id
4000 AND CALENDAR_EXCEPTION_SET_ID = -1;
4001 END IF;
4002
4003 IF PG_DEBUG in ('Y','C') THEN
4004 msc_sch_wb.atp_debug ('****** No Data Found Exception *******');
4005 msc_sch_wb.atp_debug ('________________Output________________');
4006 msc_sch_wb.atp_debug (' Calendar Code : ' || l_calendar_code);
4007 msc_sch_wb.atp_debug (' ');
4008 END IF;
4009
4010 RETURN l_calendar_code;
4011
4012 END Src_Get_Calendar_Code;
4013
4014 -- Overloaded Functions driven by calendar_code rather than org_id
4015 FUNCTION Src_NEXT_WORK_DAY(
4016 p_calendar_code IN varchar2,
4017 p_calendar_date IN date
4018 ) RETURN DATE
4019 IS
4020 l_next_work_day DATE;
4021 l_first_work_day DATE;
4022 l_last_work_day DATE;
4023 BEGIN
4024 IF (p_calendar_code IS NULL) OR
4025 (p_calendar_date IS NULL) THEN
4026 --RETURN NULL; bug3583705
4027 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
4028 END IF;
4029
4030 IF (p_calendar_code = MSC_CALENDAR.FOC) THEN
4031 RETURN p_calendar_date;
4032 END IF;
4033
4034 BEGIN
4035
4036 SELECT NEXT_DATE
4037 INTO l_next_work_day
4038 FROM BOM_CALENDAR_DATES
4039 WHERE CALENDAR_CODE = p_calendar_code
4040 AND EXCEPTION_SET_ID = -1
4041 AND CALENDAR_DATE = TRUNC(p_calendar_date);
4042
4043 EXCEPTION
4044 WHEN NO_DATA_FOUND THEN --bug3583705
4045 IF MSC_CALENDAR.G_RETAIN_DATE = 'Y' THEN
4046 BEGIN
4047 SELECT min(calendar_date), max(calendar_date)
4048 INTO l_first_work_day, l_last_work_day
4049 FROM BOM_CALENDAR_DATES
4050 WHERE CALENDAR_CODE = p_calendar_code
4051 AND SEQ_NUM is not null;
4052 EXCEPTION
4053 WHEN NO_DATA_FOUND THEN
4054 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
4055 END;
4056
4057 IF p_calendar_date >= l_last_work_day THEN
4058 l_next_work_day := l_last_work_day;
4059 ELSIF p_calendar_date <= l_first_work_day THEN
4060 l_next_work_day := l_first_work_day;
4061 ELSE
4062 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
4063 END IF;
4064 ELSE
4065 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
4066 APP_EXCEPTION.RAISE_EXCEPTION;
4067 END IF;
4068 END;
4069 RETURN l_next_work_day;
4070
4071 END Src_NEXT_WORK_DAY;
4072
4073 FUNCTION Src_PREV_WORK_DAY(
4074 p_calendar_code IN varchar2,
4075 p_calendar_date IN date
4076 ) RETURN DATE
4077 IS
4078 l_prev_work_day DATE;
4079 l_first_work_day DATE;
4080 l_last_work_day DATE;
4081 BEGIN
4082 IF (p_calendar_code IS NULL) OR
4083 (p_calendar_date IS NULL) THEN
4084 --RETURN NULL; bug3583705
4085 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
4086 END IF;
4087
4088 IF (p_calendar_code = MSC_CALENDAR.FOC) THEN
4089 RETURN p_calendar_date;
4090 END IF;
4091
4092 BEGIN
4093
4094 SELECT PRIOR_DATE
4095 INTO l_prev_work_day
4096 FROM BOM_CALENDAR_DATES
4097 WHERE CALENDAR_CODE = p_calendar_code
4098 AND EXCEPTION_SET_ID = -1
4099 AND CALENDAR_DATE = TRUNC(p_calendar_date);
4100
4101 EXCEPTION
4102 WHEN NO_DATA_FOUND THEN --bug3583705
4103 IF MSC_CALENDAR.G_RETAIN_DATE = 'Y' THEN
4104 BEGIN
4105 SELECT min(calendar_date), max(calendar_date)
4106 INTO l_first_work_day, l_last_work_day
4107 FROM BOM_CALENDAR_DATES
4108 WHERE CALENDAR_CODE = p_calendar_code
4109 AND SEQ_NUM is not null;
4110 EXCEPTION
4111 WHEN NO_DATA_FOUND THEN
4112 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
4113 END;
4114
4115 IF p_calendar_date >= l_last_work_day THEN
4116 l_prev_work_day := l_last_work_day;
4117 ELSIF p_calendar_date <= l_first_work_day THEN
4118 l_prev_work_day := l_first_work_day;
4119 ELSE
4120 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
4121 END IF;
4122 ELSE
4123 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
4124 APP_EXCEPTION.RAISE_EXCEPTION;
4125 END IF;
4126 END;
4127
4128 RETURN l_prev_work_day;
4129
4130 END Src_PREV_WORK_DAY;
4131
4132 FUNCTION Src_DATE_OFFSET(
4133 p_calendar_code IN varchar2,
4134 p_calendar_date IN date,
4135 p_days_offset IN number,
4136 p_offset_type IN number
4137 ) RETURN DATE
4138 IS
4139 l_offsetted_day DATE;
4140 l_days_offset NUMBER;
4141 l_first_work_day DATE;
4142 l_last_work_day DATE;
4143 BEGIN
4144 IF (p_calendar_code IS NULL) OR
4145 (p_calendar_date IS NULL) OR
4146 (p_days_offset IS NULL) THEN
4147 --RETURN NULL; bug3583705
4148 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
4149 END IF;
4150
4151 IF (p_days_offset = 0) and (p_calendar_code = MSC_CALENDAR.FOC) THEN
4152 RETURN p_calendar_date;
4153 ELSIF (p_days_offset = 0) and (p_offset_type = -1) THEN
4154 l_offsetted_day := MSC_SATP_FUNC.SRC_PREV_WORK_DAY(
4155 p_calendar_code,
4156 p_calendar_date);
4157 ELSIF (p_days_offset = 0) and (p_offset_type = +1) THEN --bug3558412
4158 l_offsetted_day := MSC_SATP_FUNC.SRC_NEXT_WORK_DAY(
4159 p_calendar_code,
4160 p_calendar_date);
4161 ELSE
4162 IF p_days_offset > 0 THEN
4163 l_days_offset := CEIL(p_days_offset);
4164 ELSE
4165 l_days_offset := FLOOR(p_days_offset);
4166 END IF;
4167
4168 IF p_calendar_code = MSC_CALENDAR.FOC THEN
4169 RETURN p_calendar_date + l_days_offset;
4170 END IF;
4171
4172 IF p_days_offset > 0 THEN
4173 BEGIN
4174 SELECT cal2.calendar_date
4175 INTO l_offsetted_day
4176 FROM BOM_CALENDAR_DATES cal1, BOM_CALENDAR_DATES cal2
4177 WHERE cal1.calendar_code = p_calendar_code
4178 AND cal1.exception_set_id = -1
4179 AND cal1.calendar_date = TRUNC(p_calendar_date)
4180 AND cal2.calendar_code = cal1.calendar_code
4181 AND cal2.exception_set_id = cal1.exception_set_id
4182 AND cal2.seq_num = cal1.prior_seq_num + l_days_offset; --bug3558412
4183 --AND cal2.seq_num = cal1.next_seq_num + l_days_offset;
4184
4185 EXCEPTION
4186 WHEN NO_DATA_FOUND THEN --bug3583705
4187 IF MSC_CALENDAR.G_RETAIN_DATE = 'Y' THEN
4188 BEGIN
4189 SELECT min(calendar_date), max(calendar_date)
4190 INTO l_first_work_day, l_last_work_day
4191 FROM BOM_CALENDAR_DATES
4192 WHERE CALENDAR_CODE = p_calendar_code
4193 AND SEQ_NUM is not null;
4194 EXCEPTION
4195 WHEN NO_DATA_FOUND THEN
4196 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
4197 END;
4198
4199 IF p_calendar_date >= l_last_work_day THEN
4200 l_offsetted_day := l_last_work_day;
4201 ELSIF p_calendar_date <= l_first_work_day THEN
4202 l_offsetted_day := l_first_work_day;
4203 ELSE
4204 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
4205 END IF;
4206 ELSE
4207 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
4208 APP_EXCEPTION.RAISE_EXCEPTION;
4209 END IF;
4210 END;
4211 ELSE
4212 BEGIN
4213 SELECT cal2.calendar_date
4214 INTO l_offsetted_day
4215 FROM BOM_CALENDAR_DATES cal1, BOM_CALENDAR_DATES cal2
4216 WHERE cal1.calendar_code = p_calendar_code
4217 AND cal1.exception_set_id = -1
4218 AND cal1.calendar_date = TRUNC(p_calendar_date)
4219 AND cal2.calendar_code = cal1.calendar_code
4220 AND cal2.exception_set_id = cal1.exception_set_id
4221 AND cal2.seq_num = cal1.next_seq_num + l_days_offset; --bug3558412
4222 --AND cal2.seq_num = cal1.prior_seq_num + l_days_offset;
4223
4224 EXCEPTION
4225 WHEN NO_DATA_FOUND THEN --bug3583705
4226 IF MSC_CALENDAR.G_RETAIN_DATE = 'Y' THEN
4227 BEGIN
4228 SELECT min(calendar_date), max(calendar_date)
4229 INTO l_first_work_day, l_last_work_day
4230 FROM BOM_CALENDAR_DATES
4231 WHERE CALENDAR_CODE = p_calendar_code
4232 AND SEQ_NUM is not null;
4233 EXCEPTION
4234 WHEN NO_DATA_FOUND THEN
4235 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
4236 END;
4237
4238 IF p_calendar_date >= l_last_work_day THEN
4239 l_offsetted_day := l_last_work_day;
4240 ELSIF p_calendar_date <= l_first_work_day THEN
4241 l_offsetted_day := l_first_work_day;
4242 ELSE
4243 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
4244 END IF;
4245 ELSE
4246 FND_MESSAGE.SET_NAME('MRP', 'GEN-DATE OUT OF BOUNDS');
4247 APP_EXCEPTION.RAISE_EXCEPTION;
4248 END IF;
4249 END;
4250 END IF;
4251 END IF;
4252
4253 RETURN l_offsetted_day;
4254
4255 END Src_DATE_OFFSET;
4256
4257 FUNCTION SRC_THREE_STEP_CAL_OFFSET_DATE(
4258 p_input_date IN Date,
4259 p_first_cal_code IN VARCHAR2,
4260 p_first_cal_validation_type IN NUMBER,
4261 p_second_cal_code IN VARCHAR2,
4262 p_offset_days IN NUMBER,
4263 p_second_cal_validation_type IN NUMBER,
4264 p_third_cal_code IN VARCHAR2,
4265 p_third_cal_validation_type IN NUMBER
4266 ) RETURN DATE
4267 IS
4268 l_first_date DATE := NULL;
4269 l_second_date DATE := NULL;
4270 l_output_date DATE := NULL;
4271
4272 BEGIN
4273 IF PG_DEBUG in ('Y','C') THEN
4274 msc_sch_wb.atp_debug ('***** Begin Function THREE_STEP_CAL_OFFSET_DATE *****');
4275 msc_sch_wb.atp_debug ('________________Input________________');
4276 msc_sch_wb.atp_debug (' Input Date : ' || p_input_date );
4277 msc_sch_wb.atp_debug (' First Cal Code : ' || p_first_cal_code );
4278 msc_sch_wb.atp_debug (' Second Cal Code : ' || p_second_cal_code );
4279 msc_sch_wb.atp_debug (' Third Cal Code : ' || p_third_cal_code );
4280 msc_sch_wb.atp_debug (' Days Offset : ' || p_offset_days );
4281 msc_sch_wb.atp_debug (' ');
4282 END IF;
4283 -- First date is computed using p_input_date, first calendar and its validation_type
4284 IF p_first_cal_code = MSC_CALENDAR.FOC THEN
4285 l_first_date := p_input_date;
4286 ELSIF p_first_cal_validation_type = -1 THEN
4287 l_first_date := MSC_SATP_FUNC.SRC_PREV_WORK_DAY(
4288 p_first_cal_code,
4289 p_input_date);
4290 ELSIF p_first_cal_validation_type = 1 THEN
4291 l_first_date := MSC_SATP_FUNC.SRC_NEXT_WORK_DAY(
4292 p_first_cal_code,
4293 p_input_date);
4294 ELSE
4295 l_first_date := p_input_date;
4296 END IF;
4297
4298 IF PG_DEBUG in ('Y','C') THEN
4299 msc_sch_wb.atp_debug (' Date after validation on first cal: ' || l_first_date );
4300 END IF;
4301
4302 -- Second date is computed using first date, 2nd calendar and offset days
4303 IF (p_offset_days = 0) and (p_second_cal_code = MSC_CALENDAR.FOC) THEN
4304 l_second_date := l_first_date;
4305 ELSIF (p_offset_days = 0) and (p_second_cal_validation_type = -1) THEN
4306 l_second_date := MSC_SATP_FUNC.SRC_PREV_WORK_DAY(
4307 p_second_cal_code,
4308 l_first_date);
4309 ELSIF (p_offset_days = 0) and (p_second_cal_validation_type = 1) THEN
4310 l_second_date := MSC_SATP_FUNC.SRC_NEXT_WORK_DAY(
4311 p_second_cal_code,
4312 l_first_date);
4313 ELSIF p_second_cal_code = MSC_CALENDAR.FOC THEN
4314 l_second_date := l_first_date + p_offset_days;
4315 ELSIF p_offset_days > 0 THEN
4316 ---Bug 6625744 start---
4317 l_first_date:=MSC_SATP_FUNC.SRC_NEXT_WORK_DAY(
4318 p_second_cal_code,
4319 l_first_date);
4320 ---Bug 6625744 end---
4321 l_second_date := MSC_SATP_FUNC.SRC_DATE_OFFSET(
4322 p_second_cal_code,
4323 l_first_date,
4324 p_offset_days,
4325 +1);
4326 ELSIF p_offset_days < 0 THEN
4327 ---Bug 6625744 start---
4328 l_first_date:=MSC_SATP_FUNC.SRC_PREV_WORK_DAY(
4329 p_second_cal_code,
4330 l_first_date);
4331 ---Bug 6625744 end---
4332 l_second_date := MSC_SATP_FUNC.SRC_DATE_OFFSET(
4333 p_second_cal_code,
4334 l_first_date,
4335 p_offset_days,
4336 -1);
4337 ELSE
4338 l_second_date := l_first_date;
4339 END IF;
4340
4341 IF PG_DEBUG in ('Y','C') THEN
4342 msc_sch_wb.atp_debug (' Date after offset using second cal: ' || l_second_date );
4343 END IF;
4344
4345 -- Third date = Output Date is computed using 2nd date, 3rd calendar and validation_type
4346 IF p_third_cal_code = MSC_CALENDAR.FOC THEN
4347 l_output_date := l_second_date;
4348 ELSIF p_third_cal_validation_type = -1 THEN
4349 l_output_date := MSC_SATP_FUNC.SRC_PREV_WORK_DAY(
4350 p_third_cal_code,
4351 l_second_date);
4352 ELSIF p_third_cal_validation_type = 1 THEN
4353 l_output_date := MSC_SATP_FUNC.SRC_NEXT_WORK_DAY(
4354 p_third_cal_code,
4355 l_second_date);
4356 ELSE
4357 l_output_date := l_second_date;
4358 END IF;
4359
4360 IF PG_DEBUG in ('Y','C') THEN
4361 msc_sch_wb.atp_debug (' Date after validation on third cal: ' || l_output_date );
4362 END IF;
4363
4364 RETURN l_output_date;
4365
4366 END SRC_THREE_STEP_CAL_OFFSET_DATE;
4367
4368 /*--------------------------------------------------------------------------
4369 | End Functions added for ship_rec_cal project
4370 +-------------------------------------------------------------------------*/
4371
4372 /*--------------------------------------------------------------------------
4373 | Begin Functions added for collection enhancement project-3049003
4374 +-------------------------------------------------------------------------*/
4375
4376 PROCEDURE get_dblink_profile(
4377 x_dblink OUT NOCOPY VARCHAR2,
4378 x_instance_id OUT NOCOPY NUMBER,
4379 x_return_status OUT NOCOPY VARCHAR2
4380 )
4381 IS
4382
4383 Begin
4384 IF PG_DEBUG in ('Y', 'C') THEN
4385 msc_sch_wb.atp_debug('Begin get_dblink_profile');
4386 END IF;
4387
4388 x_return_status := FND_API.G_RET_STS_SUCCESS;
4389
4390 --Bug3765793 adding trim functions to remove spaces from db_link
4391 SELECT instance_id, ltrim(rtrim(a2m_dblink))
4392 INTO x_instance_id, x_dblink
4393 FROM mrp_ap_apps_instances;
4394
4395 IF PG_DEBUG in ('Y', 'C') THEN
4396 msc_sch_wb.atp_debug('get_dblink_profile: ' || 'x_instance_id := ' || NVL(x_instance_id, -1));
4397 msc_sch_wb.atp_debug('get_dblink_profile: ' || 'a2m_dblink := ' || NVL(x_dblink,'NULL'));
4398 END IF;
4399 EXCEPTION
4400 WHEN others THEN
4401 -- something wrong so we want to rollback;
4402 IF PG_DEBUG in ('Y', 'C') THEN
4403 msc_sch_wb.atp_debug('Error in mrp_ap_apps_instances : ' || sqlcode);
4404 END IF;
4405 x_return_status := FND_API.G_RET_STS_ERROR;
4406
4407 End get_dblink_profile;
4408
4409 /*--------------------------------------------------------------------------
4410 | End Functions added for collection enhancement project-aksaxena
4411 +-------------------------------------------------------------------------*/
4412
4413 /*--------------------------------------------------------------------------
4414 | Begin Procedure get_src_to_dstn_profiles added for supporting Multiple
4415 | allocation enh -3940999 Inserts values of profiles at source in table
4416 | msc_atp_src_profile_temp..
4417 +-------------------------------------------------------------------------*/
4418 PROCEDURE put_src_to_dstn_profiles(
4419 p_session_id IN NUMBER,
4420 x_return_status OUT NoCopy VARCHAR2
4421 ) IS
4422
4423 l_profile_name MRP_ATP_PUB.char255_arr := MRP_ATP_PUB.char255_arr();
4424 j NUMBER := 1;
4425 l_user_id number;
4426 l_count number;
4427 l_sysdate DATE := TRUNC(sysdate);
4428
4429 BEGIN
4430 -- initialize API returm status to success
4431 x_return_status := FND_API.G_RET_STS_SUCCESS;
4432
4433 l_user_id := FND_GLOBAL.USER_ID;
4434 l_count := 10; --optional_fw
4435
4436 IF PG_DEBUG in ('Y', 'C') THEN
4437 msc_sch_wb.atp_debug('enter put_src_to_dstn_profiles');
4438 msc_sch_wb.atp_debug('put_src_to_dstn_profiles: ' || 'p_session_id := ' || p_session_id);
4439 msc_sch_wb.atp_debug('put_src_to_dstn_profiles: ' || 'l_user_id := ' || l_user_id);
4440 END IF;
4441
4442 -- Delete records from msc_atp_src_profile_temp in case there are any records
4443 -- with similar session id.
4444 Delete from msc_atp_src_profile_temp where session_id = p_session_id;
4445
4446 IF PG_DEBUG in ('Y', 'C') THEN
4447 msc_sch_wb.atp_debug('***** After Deleting data for old session ****');
4448 msc_sch_wb.atp_debug('put_src_to_dstn_profiles: ' || 'Number of rows deleted ' || SQL%ROWCOUNT);
4449 END IF;
4450
4451 l_profile_name.extend(l_count);
4452 l_profile_name(1) := 'MSC_ALLOCATION_METHOD';
4453 l_profile_name(2) := 'MSC_ALLOCATED_ATP';
4454 l_profile_name(3) := 'MSC_CLASS_HIERARCHY';
4455 l_profile_name(4) := 'INV_CTP';
4456 l_profile_name(5) := 'MSC_ALLOCATED_ATP_WORKFLOW';
4457 l_profile_name(6) := 'MSC_USE_SHIP_REC_CAL';
4458 l_profile_name(7) := 'MSC_MOVE_PAST_DUE_TO_SYSDATE'; --6316476
4459 l_profile_name(8) := 'MSC_ZERO_ALLOC_PERC'; --6359986
4460 l_profile_name(9) := 'MSC_ATP_CHECK_INT_SALES_ORDERS'; --6485306
4461 l_profile_name(10) := 'MSC_ENHANCED_FORWARD_ATP'; --optional_fw
4462
4463 FOR j in 1..l_count LOOP
4464 INSERT INTO msc_atp_src_profile_temp
4465 (
4466 session_id,
4467 profile_name,
4468 profile_value,
4469 creation_date,
4470 created_by,
4471 last_update_date,
4472 last_updated_by,
4473 last_update_login
4474 )
4475 values
4476 (p_session_id,
4477 l_profile_name(j),
4478 fnd_profile.value(l_profile_name(j)),
4479 l_sysdate,
4480 l_user_id,
4481 l_sysdate,
4482 l_user_id,
4483 l_user_id
4484 );
4485 END LOOP;
4486
4487 IF PG_DEBUG in ('Y', 'C') THEN
4488 msc_sch_wb.atp_debug('put_src_to_dstn_profiles: ' || 'Rows inserted ' || SQL%ROWCOUNT );
4489 END IF;
4490
4491 EXCEPTION
4492 WHEN OTHERS THEN
4493 IF PG_DEBUG in ('Y', 'C') THEN
4494 msc_sch_wb.atp_debug(' Error in put_src_to_dstn_profiles '||substr(sqlerrm,1,100));
4495 END IF;
4496 x_return_status := FND_API.G_RET_STS_ERROR;
4497 END put_src_to_dstn_profiles;
4498 /*--------------------------------------------------------------------------
4499 | End Procedure put_src_to_dstn_profiles added for Multiple allocation enh -3940999
4500 +-------------------------------------------------------------------------*/
4501
4502 /*--------------------------------------------------------------------------
4503 | Begin Procedure get_src_to_dstn_profiles added for supporting Multiple
4504 | allocation enh -3940999 Inserts values of profiles at destination by
4505 | reading them from table msc_atp_src_profile_temp at source.
4506 +-------------------------------------------------------------------------*/
4507
4508 PROCEDURE get_src_to_dstn_profiles(
4509 p_dblink IN VARCHAR2,
4510 p_session_id IN NUMBER,
4511 x_return_status OUT NoCopy VARCHAR2
4512 ) IS
4513
4514 l_profile_name MRP_ATP_PUB.char255_arr := MRP_ATP_PUB.char255_arr();
4515 j NUMBER;
4516 l_dynstring VARCHAR2(128) := NULL;
4517 l_user_id number;
4518 l_sql_stmt varchar2(2000);
4519
4520 BEGIN
4521 -- initialize API returm status to success
4522 x_return_status := FND_API.G_RET_STS_SUCCESS;
4523
4524 IF PG_DEBUG in ('Y', 'C') THEN
4525 msc_sch_wb.atp_debug('enter get_src_to_dstn_profiles');
4526 msc_sch_wb.atp_debug('get_src_to_dstn_profiles: ' || 'p_session_id := ' || p_session_id);
4527 msc_sch_wb.atp_debug('get_src_to_dstn_profiles ' || 'p_dblink := ' || NVL(p_dblink,'NULL'));
4528 END IF;
4529
4530 l_dynstring := '@'||p_dblink;
4531 l_user_id := FND_GLOBAL.USER_ID;
4532
4533 -- Delete records from msc_atp_src_profile_temp in case there are any records
4534 -- with similar session id.
4535 Delete from msc_atp_src_profile_temp where session_id = p_session_id;
4536
4537 IF PG_DEBUG in ('Y', 'C') THEN
4538 msc_sch_wb.atp_debug('***** After Deleting data for old session ****');
4539 msc_sch_wb.atp_debug('get_src_to_dstn_profiles: ' || 'Number of rows deleted ' || SQL%ROWCOUNT);
4540 END IF;
4541
4542 l_sql_stmt :=
4543 'Insert into msc_atp_src_profile_temp
4544 (session_id,
4545 profile_name,
4546 profile_value,
4547 creation_date,
4548 created_by,
4549 last_update_date,
4550 last_updated_by,
4551 last_update_login';
4552
4553 l_sql_stmt := l_sql_stmt ||
4554 ' )select
4555 session_id,
4556 profile_name,
4557 profile_value,
4558 creation_date,
4559 created_by,
4560 last_update_date,
4561 last_updated_by,
4562 last_update_login';
4563
4564 l_sql_stmt := l_sql_stmt || ' from msc_atp_src_profile_temp' || l_dynstring ||
4565 ' where session_id = :p_session_id';
4566
4567 EXECUTE IMMEDIATE l_sql_stmt USING p_session_id;
4568
4569 IF PG_DEBUG in ('Y', 'C') THEN
4570 msc_sch_wb.atp_debug('After Inserting the profiles in msc_atp_src_profile_temp');
4571 msc_sch_wb.atp_debug('l_sql_stmt= ' || l_sql_stmt);
4572 msc_sch_wb.atp_debug('rows inserted = ' || SQL%ROWCOUNT);
4573 END IF;
4574
4575 EXCEPTION
4576 WHEN OTHERS THEN
4577 IF PG_DEBUG in ('Y', 'C') THEN
4578 msc_sch_wb.atp_debug(' Error in get_src_to_dstn_profiles '||substr(sqlerrm,1,100));
4579 END IF;
4580 x_return_status := FND_API.G_RET_STS_ERROR;
4581 END get_src_to_dstn_profiles;
4582 /*--------------------------------------------------------------------------
4583 | End Procedure get_src_to_dstn_profiles added for Multiple allocation enh -3940999
4584 +-------------------------------------------------------------------------*/
4585
4586 END MSC_SATP_FUNC;