[Home] [Help]
PACKAGE BODY: APPS.MSC_ATP_UTILS
Source
1 PACKAGE BODY MSC_ATP_UTILS AS
2 /* $Header: MSCUATPB.pls 120.9 2007/12/12 10:42:48 sbnaik ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_ATP_UTILS';
5
6 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
7
8 PROCEDURE put_into_temp_table
9 (
10 x_dblink IN VARCHAR2,
11 x_session_id IN NUMBER,
12 x_atp_rec IN MRP_ATP_PUB.atp_rec_typ,
13 x_atp_supply_demand IN MRP_ATP_PUB.ATP_Supply_Demand_Typ,
14 x_atp_period IN MRP_ATP_PUB.ATP_Period_Typ,
15 x_atp_details IN MRP_ATP_PUB.ATP_Details_Typ,
16 x_mode IN NUMBER,
17 x_return_status OUT NoCopy VARCHAR2,
18 x_msg_data OUT NoCopy VARCHAR2,
19 x_msg_count OUT NoCopy NUMBER
20 ) IS
21 --PRAGMA AUTONOMOUS_TRANSACTION;
22
23 l_dynstring VARCHAR2(128) := NULL;
24 sql_stmt VARCHAR2(10000);
25
26 -- bug 2974324. Redundant Variables removed from here.
27
28
29 BEGIN
30 -- initialize API returm status to success
31 x_return_status := FND_API.G_RET_STS_SUCCESS;
32
33 IF x_dblink IS NOT NULL THEN
34 l_dynstring := '@'||x_dblink;
35 END IF;
36
37 IF PG_DEBUG in ('Y', 'C') THEN
38 msc_sch_wb.atp_debug('put_into_temp_table: ' || 'session_id : '||x_session_id);
39 msc_sch_wb.atp_debug('enter put_into_temp_table');
40 msc_sch_wb.atp_debug('put_into_temp_table: ' || 'l_dynstring = '||l_dynstring);
41 -- bug 2974324. Repeated statements removed from here.
42 END IF;
43
44 /* -- bug3378648:we dont need this sql as this should be done locally
45
46 IF x_mode = RESULTS_MODE AND x_dblink IS NOT NULL THEN
47 -- Deletes any records in the
48 -- cchen : add database link to the subquery
49 sql_stmt :=
50 'DELETE FROM MRP_ATP_DETAILS_TEMP'||l_dynstring||
51 ' WHERE session_id = :x_session_id '||
52 ' and order_line_id in ( '||
53 ' select order_line_id from mrp_atp_schedule_temp'||l_dynstring||
54 ' where session_id = :x_session_id_1 '||
55 ' and status_flag = 1) '||
56 ' and record_type <> 3';
57
58
59 EXECUTE IMMEDIATE sql_stmt USING x_session_id, x_session_id;
60
61 IF PG_DEBUG in ('Y', 'C') THEN
62 msc_sch_wb.atp_debug('put_into_temp_table: ' || 'delete details temp rows := '|| SQL%ROWCOUNT);
63 END IF;
64 END IF;
65 /* -- bug3378648:we dont need this sql as this should be done locally
66
67 -- moved deleting old records from mrp_atp_details_temp to call_schedule_remote
68
69 /*
70 MSC_ATP_UTILS.PUT_SD_DATA(x_atp_supply_demand, x_dblink, x_session_id);
71 IF PG_DEBUG in ('Y', 'C') THEN
72 msc_sch_wb.atp_debug('put_into_temp_table: ' || ' Inserted supply demand records ');
73 END IF;
74
75 -- MSC_ATP_UTILS.PUT_PERIOD_DATA(x_atp_period, x_dblink, x_session_id);
76 -- dsting call it with null because we'll transfer it later
77 -- I'm not really expecting anything here
78 IF PG_DEBUG in ('Y', 'C') THEN
79 msc_sch_wb.atp_debug('put_into_temp_table: ' || ' dsting expect 0 period recs: ' || x_atp_period.level.count);
80 END IF;
81 MSC_ATP_UTILS.PUT_PERIOD_DATA(x_atp_period, NULL, x_session_id);
82
83 IF PG_DEBUG in ('Y', 'C') THEN
84 msc_sch_wb.atp_debug('put_into_temp_table: ' || ' Inserted period records ');
85 msc_sch_wb.atp_debug('enter put_into_temp_table :30');
86 END IF;
87
88 -- dsting transfer it later
89 -- MSC_ATP_UTILS.PUT_Pegging_Data(x_session_id, x_dblink);
90
91 IF PG_DEBUG in ('Y', 'C') THEN
92 msc_sch_wb.atp_debug('put_into_temp_table: ' || 'Inserted Pegging Records');
93 END IF;
94 */
95
96 MSC_ATP_UTILS.Put_Scheduling_data(x_atp_rec, x_mode, x_dblink, x_session_id);
97
98 IF PG_DEBUG in ('Y', 'C') THEN
99 msc_sch_wb.atp_debug('put_into_temp_table: ' || ' Inserted main records ');
100 END IF;
101
102 --commit; -- autonomous transaction
103
104 EXCEPTION
105
106 -- Bug 2458308 : krajan
107 -- error Handling
108
109 WHEN MSC_ATP_PUB.ATP_INVALID_OBJECTS_FOUND THEN
110 -- bug 2974324. Redundant cursor statements removed from here.
111 IF PG_DEBUG in ('Y', 'C') THEN
112 msc_sch_wb.atp_debug('ATP Invalid Objects Found in put_Into_temp_table ');
113 msc_sch_wb.atp_debug('put_into_temp_table: ' || ' Error in MSCUATPB.pls '||substr(sqlerrm,1,100));
114 END IF;
115 x_msg_data := substr(sqlerrm,1,100);
116 x_return_status := FND_API.G_RET_STS_ERROR;
117 -- IF l_dynstring is null THEN
118 -- ROLLBACK;
119 -- END IF;
120 RAISE MSC_ATP_PUB.ATP_INVALID_OBJECTS_FOUND;
121
122 WHEN OTHERS THEN
123 -- bug 2974324. Redundant cursor statements removed from here.
124 IF PG_DEBUG in ('Y', 'C') THEN
125 msc_sch_wb.atp_debug('put_into_temp_table: ' || ' Error in MSCUATPB.pls '||substr(sqlerrm,1,100));
126 END IF;
127 x_msg_data := substr(sqlerrm,1,100);
128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
129
130 -- Bug 2458308 : krajan
131 -- Commented out rollbacks
132 -- IF l_dynstring is null THEN
133 -- ROLLBACK;
134 -- END IF;
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136
137 END put_into_temp_table;
138
139
140 PROCEDURE get_from_temp_table
141 (
142 x_dblink IN VARCHAR2,
143 x_session_id IN NUMBER,
144 x_atp_rec OUT NoCopy MRP_ATP_PUB.atp_rec_typ,
145 x_atp_supply_demand OUT NoCopy MRP_ATP_PUB.ATP_Supply_Demand_Typ,
146 x_atp_period OUT NoCopy MRP_ATP_PUB.ATP_Period_Typ,
147 x_atp_details OUT NoCopy MRP_ATP_PUB.ATP_Details_Typ,
148 x_mode IN NUMBER,
149 x_return_status OUT NoCopy VARCHAR2,
150 x_msg_data OUT NoCopy VARCHAR2,
151 x_msg_count OUT NoCopy NUMBER,
152 p_details_flag IN NUMBER
153 ) IS
154
155 sched_cv mrp_atp_utils.SchedCurTyp;
156 sched_rec mrp_atp_utils.Schedule_Temp;
157 details_rec mrp_atp_utils.Details_Temp;
158 i PLS_INTEGER := 1;
159 j PLS_INTEGER := 1;
160 l_dynstring VARCHAR2(128) := NULL;
161 sql_stmt VARCHAR2(10000);
162 temp number ;
163 l_status_flag pls_integer;
164 l_mso_lead_time_factor number;
165 BEGIN
166
167 IF PG_DEBUG in ('Y', 'C') THEN
168 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'Entering get from temp table');
169 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'p_details_falg := ' || p_details_flag);
170 END IF;
171 -- initialize API returm status to success
172 x_return_status := FND_API.G_RET_STS_SUCCESS;
173
174 IF x_dblink IS NOT NULL THEN
175 l_dynstring := '@'||x_dblink;
176 END IF;
177
178
179 ---s_cto_rearch
180 IF x_dblink is not null and x_mode = REQUEST_MODE THEN
181
182 --bug 3378648
183 --delete the old data if any; This data will exist in case of Global order promising
184 Delete mrp_atp_schedule_temp
185 where session_id = x_session_id
186 and status_flag in (1, 2, 99);
187 --and status_flag in (1, 99);
188
189 --delete local detail data
190 delete mrp_atp_details_temp
191 where session_id = x_session_id;
192
193 --transfer the date from source to dest mrp_atp_schedule_temp
194 MSC_ATP_UTILS.Transfer_scheduling_data(x_session_id, x_dblink, REQUEST_MODE);
195 END IF;
196 ---e_cto_rearch
197
198 IF PG_DEBUG in ('Y', 'C') THEN
199 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'get from temp table, l_dynstring = '||l_dynstring);
200 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'get from temp table, x_session_id = '||x_session_id);
201 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'get from temp table, x_mode = ' || x_mode);
202 END IF;
203
204 -- cchen: rewrite this sql_stmt. based on the mode we either have status 1
205 -- or 2 in the where clause
206
207 -- bug 1878093, pass atp_flag from mtl_system_items to destination
208 -- for use in case item has not been collected as yet.
209
210 --pegging enhancement: If on same database then use bulk collect
211 --- can't use bulk collect in case of distributed set up becauase its not supported.
212 --s_cto_rearch
213 -- IF l_dynstring is null THEN
214 --e_cto_rearch
215 l_mso_lead_time_factor := MSC_ATP_PVT.G_MSO_LEAD_TIME_FACTOR;
216 IF x_mode = results_mode THEN
217 l_status_flag := 2; -- changed form 1 to 2
218 ELSE
219 l_status_flag := 99;
220 END IF;
221
222 IF PG_DEBUG in ('Y', 'C') THEN
223 msc_sch_wb.atp_debug('l_status_flag := ' || l_status_flag);
224 END IF;
225
226 SELECT
227 ACTION
228 ,CALLING_MODULE
229 ,ORDER_HEADER_ID
230 ,ORDER_LINE_ID
231 ,INVENTORY_ITEM_ID
232 ,ORGANIZATION_ID
233 ,SR_INSTANCE_ID
234 ,ORDER_NUMBER
235 ,SOURCE_ORGANIZATION_ID
236 ,CUSTOMER_ID
237 ,CUSTOMER_SITE_ID
238 ,DESTINATION_TIME_ZONE
239 ,QUANTITY_ORDERED
240 ,UOM_CODE
241 ,REQUESTED_SHIP_DATE
242 ,REQUESTED_ARRIVAL_DATE
243 ,LATEST_ACCEPTABLE_DATE
244 ,DELIVERY_LEAD_TIME
245 ,FREIGHT_CARRIER
246 ,SHIP_METHOD
247 ,DEMAND_CLASS
248 ,SHIP_SET_NAME
249 ,ARRIVAL_SET_NAME
250 ,OVERRIDE_FLAG
251 ,SCHEDULED_SHIP_DATE
252 -- rajjain 02/21/2003 Bug 2815484
253 ,SCHEDULED_ARRIVAL_DATE
254 ,AVAILABLE_QUANTITY
255 ,REQUESTED_DATE_QUANTITY
256 ,GROUP_SHIP_DATE
257 ,GROUP_ARRIVAL_DATE
258 ,VENDOR_ID
259 ,VENDOR_SITE_ID
260 ,INSERT_FLAG
261 ,ERROR_CODE
262 ,INVENTORY_ITEM_NAME
263 ,SOURCE_ORGANIZATION_CODE
264 ,SCENARIO_ID
265 ,VENDOR_NAME
266 ,VENDOR_SITE_NAME
267 ,MDI_ROWID
268 ,DEMAND_SOURCE_TYPE
269 ,DEMAND_SOURCE_DELIVERY
270 /* --bug 4078703: always pass atp_lead_time back to OM
271 ,DECODE(MSC_ATP_PVT.G_INV_CTP, 5,
272 Decode(order_line_id, ato_model_line_id,
273 decode(bom_item_type, 1,
274 (fixed_lt + (variable_lt * QUANTITY_ORDERED)) * (1 + l_mso_lead_time_factor), 0), 0), 0)
275 */
276 ,atp_lead_time
277 ,OE_FLAG
278 ,END_PEGGING_ID
279 ,OLD_SOURCE_ORGANIZATION_ID
280 ,OLD_DEMAND_CLASS
281 --,ATTRIBUTE_06
282 ,SUBSTITUTION_TYP_CODE
283 ,REQ_ITEM_DETAIL_FLAG
284 ,OLD_INVENTORY_ITEM_ID
285 ,REQUEST_ITEM_ID
286 ,REQUEST_ITEM_NAME
287 ,REQ_ITEM_AVAILABLE_DATE
288 ,REQ_ITEM_AVAILABLE_DATE_QTY
289 ,REQ_ITEM_REQ_DATE_QTY
290 ,SALES_REP
291 ,CUSTOMER_CONTACT
292 ,SUBST_FLAG
293 ,diagnostic_atp_flag
294 ---columns for CTO project
295 ,Top_Model_line_id,
296 ATO_Parent_Model_Line_Id,
297 ATO_Model_Line_Id,
298 Parent_line_id,
299 match_item_id,
300 Config_item_line_id,
301 Validation_Org,
302 Component_Sequence_ID,
303 Component_Code,
304 line_number,
305 included_item_flag,
306 atp_flag,
307 atp_components_flag,
308 bom_item_type,
309 pick_components_flag,
310 OSS_ERROR_CODE,
311 sequence_number,
312 original_request_date,
313 --bug 3508529: add extra coumns
314
315 --bug 3508529: add columns that are present in atp rec type
316 null, --earliest_acceptable_date,
317 error_message, --message,
318 null, --ato_delete_flag,
319 null, --attribute_01,
320 null, --attribute_03,
321 null, --attribute_04,
322 null, --attribute_05,
323 compile_designator, --attribute_07,
324 null, --attribute_08,
325 null, --attribute_09,
326 null, --attribute_10,
327 customer_name, --customer_name,
328 null, --customer_class,
329 customer_location, --customer_location,
330 customer_country, --null, --customer_country, 2814895
331 customer_state, --null, --customer_state, 2814895
332 customer_city, --null, --customer_city, 2814895
333 customer_postal_code, --null, --customer_postal_code, 2814895
334 atp_flag, --atp_flag,
335 wip_supply_type, --wip_supply_type,
336 mandatory_item_flag, --mandatory_item_flag,
337 null, --base_model_id,
338 matched_item_name, --matched_item_name,
339 cascade_model_info_to_comp, --cascade_model_info_to_comp,
340 firm_flag, --firm_flag,
341 order_line_number, --order_line_number,
342 option_number, --option_number,
343 shipment_number, --shipment_number,
344 item_desc, --item_desc,
345 old_line_schedule_date, --old_line_schedule_date,
346 old_source_organization_code, --old_source_organization_code,
347 firm_source_org_id, --firm_source_org_id,
348 firm_source_org_code, --firm_source_org_code,
349 firm_ship_date, --firm_ship_date,
350 firm_arrival_date, --firm_arrival_date,
351 ship_method_text, --ship_method_text,
352 ship_set_id, --ship_set_id,
353 arrival_set_id, --arrival_set_id,
354 project_id, --project_id,
355 task_id, --task_id,
356 null, --project_number,
357 null, --task_number
358 null, --attribute_11,
359 null, --attribute_12,
360 null, --attribute_13,
361 null, --attribute_14,
362 null, --attribute_15,
363 null, --attribute_16,
364 null, --attribute_17,
365 null, --attribute_18,
366 null, --attribute_19,
367 null, --attribute_20,
368 null, --attribute_21,
369 null, --attribute_22,
370 null, --attribute_23,
371 null, --attribute_24,
372 null, --attribute_25,
373 null, --attribute_26,
374 null, --attribute_27,
375 null, --attribute_28,
376 null, --attribute_29,
377 null, --attribute_30,
378 null, --atf_date,
379 plan_id, --plan_id,
380 null, --receiving_cal_code,
381 null, --intransit_cal_code,
382 null, --shipping_cal_code,
383 null, --manufacturing_cal_code
384 --end bug 3508529: add all columns available in atp_rec_type
385 internal_org_id, -- Bug 3449812
386 first_valid_ship_arrival_date, --bug 3328421
387 party_site_id, --2814895
388 part_of_set --4500382
389
390 BULK COLLECT INTO
391
392 x_atp_rec.action,
393 x_atp_rec.calling_module,
394 x_atp_rec.Demand_Source_Header_Id,
395 x_atp_rec.identifier,
396 x_atp_rec.inventory_item_id,
397 x_atp_rec.organization_id,
398 x_atp_rec.instance_id,
399 x_atp_rec.order_number,
400 x_atp_rec.source_organization_id,
401 x_atp_rec.customer_id,
402 x_atp_rec.customer_site_id,
403 x_atp_rec.destination_time_zone,
404 x_atp_rec.quantity_ordered,
405 x_atp_rec.quantity_uom,
406 x_atp_rec.requested_ship_date,
407 x_atp_rec.requested_arrival_date,
408 x_atp_rec.latest_acceptable_date,
409 x_atp_rec.delivery_lead_time,
410 x_atp_rec.freight_carrier,
411 x_atp_rec.ship_method,
412 x_atp_rec.demand_class,
413 x_atp_rec.ship_set_name,
414 x_atp_rec.arrival_set_name,
415 x_atp_rec.override_flag,
416 x_atp_rec.Ship_Date,
417 -- rajjain 02/21/2003 Bug 2815484
418 x_atp_rec.Arrival_Date,
419 x_atp_rec.available_quantity,
420 x_atp_rec.requested_date_quantity,
421 x_atp_rec.group_ship_date,
422 x_atp_rec.group_arrival_date,
423 x_atp_rec.vendor_id,
424 x_atp_rec.vendor_site_id,
425 x_atp_rec.insert_flag,
426 x_atp_rec.error_code,
427 x_atp_rec.Inventory_Item_Name,
428 x_atp_rec.Source_Organization_Code,
429 x_atp_rec.Scenario_Id,
430 x_atp_rec.vendor_name,
431 x_atp_rec.vendor_site_name,
432 x_atp_rec.row_id,
433 x_atp_rec.Demand_Source_Type,
434 x_atp_rec.demand_source_delivery,
435 x_atp_rec.atp_lead_time,
436 x_atp_rec.oe_flag,
437 x_atp_rec.end_pegging_id,
438 x_atp_rec.old_source_organization_id,
439 x_atp_rec.old_demand_class,
440 --x_atp_rec.attribute_06,
441 x_atp_rec.substitution_typ_code,
442 x_atp_rec.req_item_detail_flag,
443 x_atp_rec.old_inventory_item_id,
444 x_atp_rec.request_item_id,
445 x_atp_rec.request_item_name,
446 x_atp_rec.req_item_available_date,
447 x_atp_rec.req_item_available_date_qty,
448 x_atp_rec.req_item_req_date_qty,
449 x_atp_rec.sales_rep,
450 x_atp_rec.customer_contact,
451 x_atp_rec.subst_flag,
452 x_atp_rec.attribute_02,
453 ---columns for CTO project
454 x_atp_rec.Top_Model_line_id,
455 x_atp_rec.ATO_Parent_Model_Line_Id,
456 x_atp_rec.ATO_Model_Line_Id,
457 x_atp_rec.Parent_line_id,
458 x_atp_rec.match_item_id,
459 x_atp_rec.Config_item_line_id,
460 x_atp_rec.Validation_Org,
461 x_atp_rec.Component_Sequence_ID,
462 x_atp_rec.Component_Code,
463 x_atp_rec.line_number,
464 x_atp_rec.included_item_flag,
465 x_atp_rec.attribute_06,
466 x_atp_rec.atp_components_flag,
467 x_atp_rec.bom_item_type,
468 x_atp_rec.pick_components_flag,
469 x_atp_rec.OSS_ERROR_CODE,
470 x_atp_rec.sequence_number,
471 x_atp_rec.original_request_date,
472 --bug 3508529: add columns that are present in atp rec type
473 x_atp_rec.earliest_acceptable_date,
474 x_atp_rec.message,
475 x_atp_rec.ato_delete_flag,
476 x_atp_rec.attribute_01,
477 x_atp_rec.attribute_03,
478 x_atp_rec.attribute_04,
479 x_atp_rec.attribute_05,
480 x_atp_rec.attribute_07,
481 x_atp_rec.attribute_08,
482 x_atp_rec.attribute_09,
483 x_atp_rec.attribute_10,
484 x_atp_rec.customer_name,
485 x_atp_rec.customer_class,
486 x_atp_rec.customer_location,
487 x_atp_rec.customer_country,
488 x_atp_rec.customer_state,
489 x_atp_rec.customer_city,
490 x_atp_rec.customer_postal_code,
491 x_atp_rec.atp_flag,
492 x_atp_rec.wip_supply_type,
493 x_atp_rec.mandatory_item_flag,
494 x_atp_rec.base_model_id,
495 x_atp_rec.matched_item_name,
496 x_atp_rec.cascade_model_info_to_comp,
497 x_atp_rec.firm_flag,
498 x_atp_rec.order_line_number,
499 x_atp_rec.option_number,
500 x_atp_rec.shipment_number,
501 x_atp_rec.item_desc,
502 x_atp_rec.old_line_schedule_date,
503 x_atp_rec.old_source_organization_code,
504 x_atp_rec.firm_source_org_id,
505 x_atp_rec.firm_source_org_code,
506 x_atp_rec.firm_ship_date,
507 x_atp_rec.firm_arrival_date,
508 x_atp_rec.ship_method_text,
509 x_atp_rec.ship_set_id,
510 x_atp_rec.arrival_set_id,
511 x_atp_rec.project_id,
512 x_atp_rec.task_id,
513 x_atp_rec.project_number,
514 x_atp_rec.task_number,
515 x_atp_rec.attribute_11,
516 x_atp_rec.attribute_12,
517 x_atp_rec.attribute_13,
518 x_atp_rec.attribute_14,
519 x_atp_rec.attribute_15,
520 x_atp_rec.attribute_16,
521 x_atp_rec.attribute_17,
522 x_atp_rec.attribute_18,
523 x_atp_rec.attribute_19,
524 x_atp_rec.attribute_20,
525 x_atp_rec.attribute_21,
526 x_atp_rec.attribute_22,
527 x_atp_rec.attribute_23,
528 x_atp_rec.attribute_24,
529 x_atp_rec.attribute_25,
530 x_atp_rec.attribute_26,
531 x_atp_rec.attribute_27,
532 x_atp_rec.attribute_28,
533 x_atp_rec.attribute_29,
534 x_atp_rec.attribute_30,
535 x_atp_rec.atf_date,
536 x_atp_rec.plan_id,
537 x_atp_rec.receiving_cal_code,
538 x_atp_rec.intransit_cal_code,
539 x_atp_rec.shipping_cal_code,
540 x_atp_rec.manufacturing_cal_code,
541 x_atp_rec.internal_org_id, -- Bug 3449812
542 x_atp_rec.first_valid_ship_arrival_date, --bug 3328421
543 x_atp_rec.party_site_id, --2814895
544 x_atp_rec.part_of_set --4500382
545
546 FROM mrp_atp_schedule_temp
547 WHERE session_id = x_session_id
548 AND status_flag = l_status_flag
549 AND NVL(mandatory_item_flag, 2) = 2
550 AND ORDER_LINE_ID = DECODE( x_mode, MSC_ATP_UTILS.RESULTS_MODE, ORDER_LINE_ID,
551 NVL(ATO_Model_Line_Id, ORDER_LINE_ID))
552 ORDER BY sequence_number;
553
554 IF PG_DEBUG in ('Y', 'C') THEN
555 msc_sch_wb.atp_debug('Records Retrieved := ' || x_atp_rec.inventory_item_id.count );
556 END IF;
557
558 --bug3520746 Begin Changes
559 --bug3610706 added the condition for de-centrlized env.
560 --insert into local regions table from Source
561 IF x_mode = REQUEST_MODE AND x_dblink IS NOT NULL THEN
562 sql_stmt :=
563 'INSERT INTO MSC_REGIONS_TEMP(
564 session_id,
565 partner_site_id,
566 region_id,
567 region_type,
568 zone_flag,
569 partner_type
570 )
571 (SELECT
572 session_id,
573 partner_site_id,
574 region_id,
575 region_type,
576 zone_flag,
577 partner_type
578 FROM msc_regions_temp' || l_dynstring || '
579 WHERE session_id = :x_session_id)';
580 EXECUTE IMMEDIATE sql_stmt USING x_session_id;
581
582 IF PG_DEBUG in ('Y', 'C') THEN
583 msc_sch_wb.atp_debug('Rows inserted in msc_regions_temp:'|| sql%rowcount);
584 END IF;
585 END IF;
586 --bug3520746 End Changes
587 ---s_cto_rearch
588 /* ELSE
589 sql_stmt :=
590 'SELECT
591 ACTION
592 ,CALLING_MODULE
593 ,SESSION_ID
594 ,ORDER_HEADER_ID
595 ,ORDER_LINE_ID
596 ,INVENTORY_ITEM_ID
597 ,ORGANIZATION_ID
598 ,SR_INSTANCE_ID
599 ,ORGANIZATION_CODE
600 ,ORDER_NUMBER
601 ,SOURCE_ORGANIZATION_ID
602 ,CUSTOMER_ID
603 ,CUSTOMER_SITE_ID
604 ,DESTINATION_TIME_ZONE
605 ,QUANTITY_ORDERED
606 ,UOM_CODE
607 ,REQUESTED_SHIP_DATE
608 ,REQUESTED_ARRIVAL_DATE
609 ,LATEST_ACCEPTABLE_DATE
610 ,DELIVERY_LEAD_TIME
611 ,FREIGHT_CARRIER
612 ,SHIP_METHOD
613 ,DEMAND_CLASS
614 ,SHIP_SET_NAME
615 ,SHIP_SET_ID
616 ,ARRIVAL_SET_NAME
617 ,ARRIVAL_SET_ID
618 ,OVERRIDE_FLAG
619 ,SCHEDULED_SHIP_DATE
620 ,SCHEDULED_ARRIVAL_DATE
621 ,AVAILABLE_QUANTITY
622 ,REQUESTED_DATE_QUANTITY
623 ,GROUP_SHIP_DATE
624 ,GROUP_ARRIVAL_DATE
625 ,VENDOR_ID
626 ,VENDOR_SITE_ID
627 ,INSERT_FLAG
628 ,ERROR_CODE
629 ,ERROR_MESSAGE
630 ,SEQUENCE_NUMBER
631 ,FIRM_FLAG
632 ,INVENTORY_ITEM_NAME
633 ,SOURCE_ORGANIZATION_CODE
634 ,INSTANCE_ID1
635 ,ORDER_LINE_NUMBER
636 ,SHIPMENT_NUMBER
637 ,OPTION_NUMBER
638 ,PROMISE_DATE
639 ,CUSTOMER_NAME
640 ,CUSTOMER_LOCATION
641 ,OLD_LINE_SCHEDULE_DATE
642 ,OLD_SOURCE_ORGANIZATION_CODE
643 ,SCENARIO_ID
644 ,VENDOR_NAME
645 ,VENDOR_SITE_NAME
646 ,STATUS_FLAG
647 ,MDI_ROWID
648 ,DEMAND_SOURCE_TYPE
649 ,DEMAND_SOURCE_DELIVERY
650 ,ATP_LEAD_TIME
651 ,OE_FLAG
652 ,ITEM_DESC
653 ,INTRANSIT_LEAD_TIME
654 ,SHIP_METHOD_TEXT
655 ,END_PEGGING_ID
656 ,PROJECT_ID
657 ,TASK_ID
658 ,PROJECT_NUMBER
659 ,TASK_NUMBER
660 ,OLD_SOURCE_ORGANIZATION_ID
661 ,OLD_DEMAND_CLASS
662 ,EXCEPTION1
663 ,EXCEPTION2
664 ,EXCEPTION3
665 ,EXCEPTION4
666 ,EXCEPTION5
667 ,EXCEPTION6
668 ,EXCEPTION7
669 ,EXCEPTION8
670 ,EXCEPTION9
671 ,EXCEPTION10
672 ,EXCEPTION11
673 ,EXCEPTION12
674 ,EXCEPTION13
675 ,EXCEPTION14
676 ,EXCEPTION15
677 ,ATTRIBUTE_06
678 ,SUBSTITUTION_TYP_CODE
679 ,REQ_ITEM_DETAIL_FLAG
680 ,OLD_INVENTORY_ITEM_ID
681 ,REQUEST_ITEM_ID
682 ,REQUEST_ITEM_NAME
683 ,REQ_ITEM_AVAILABLE_DATE
684 ,REQ_ITEM_AVAILABLE_DATE_QTY
685 ,REQ_ITEM_REQ_DATE_QTY
686 ,SALES_REP
687 ,CUSTOMER_CONTACT
688 ,SUBST_FLAG ' ;
689
690 --diag_atp
691 IF MSC_ATP_PVT.G_APPS_VER >= 3 THEN
692 sql_stmt := sql_stmt || ', diagnostic_atp_flag ';
693 ELSE
694 sql_stmt := sql_stmt || ', 2'; -- non-diagnostic for older sources
695 END IF;
696
697 sql_stmt := sql_stmt ||
698 'FROM mrp_atp_schedule_temp'||l_dynstring||'
699 WHERE session_id = :x_session_id';
700
701 IF PG_DEBUG in ('Y', 'C') THEN
702 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'sql_stmt ' || sql_stmt);
703 END IF;
704
705 IF x_mode = results_mode THEN
706 sql_stmt := sql_stmt || ' AND status_flag = 2'; -- changed form 1 to 2
707 ELSE
708 sql_stmt := sql_stmt || ' AND status_flag = 99';
709 END IF;
710
711 -- Bug 2341719 Use the sequence number as ordering tool.
712 sql_stmt := sql_stmt || ' ORDER BY sequence_number ';
713 -- End Bug 2341719 .
714
715 IF PG_DEBUG in ('Y', 'C') THEN
716 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'sql_stmt ' || sql_stmt);
717 END IF;
718
719 OPEN sched_cv FOR sql_stmt USING x_session_id;
720
721 IF PG_DEBUG in ('Y', 'C') THEN
722 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'after open sched_cv ' );
723 END IF;
724
725 LOOP
726 IF PG_DEBUG in ('Y', 'C') THEN
727 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'get from temp table, in LOOP,line 1');
728 END IF;
729
730 FETCH sched_cv INTO sched_rec;
731 temp := SQLCODE;
732 IF PG_DEBUG in ('Y', 'C') THEN
733 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'after fetch: SQLCODE = '||temp);
734 END IF;
735
736 EXIT WHEN sched_cv%NOTFOUND;
737 -- process record
738
739 IF PG_DEBUG in ('Y', 'C') THEN
740 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'get from temp table, in LOOP,after exit');
741 END IF;
742
743 MSC_SATP_FUNC.Extend_Atp(x_atp_rec, x_return_status, 1);
744
745 x_atp_rec.row_id(i) := sched_rec.mdi_rowid;
746 x_atp_rec.inventory_item_id(i) :=sched_rec.inventory_item_id;
747 x_atp_rec.Inventory_Item_Name(i) := sched_rec.Inventory_Item_Name;
748 x_atp_rec.instance_id(i) := sched_rec.sr_instance_id;
749 x_atp_rec.source_organization_id(i):=sched_rec.source_organization_id;
750 x_atp_rec.Source_Organization_Code(i) := sched_rec.Source_Organization_Code;
751 x_atp_rec.identifier(i) := sched_rec.order_line_id; -- different
752 x_atp_rec.order_number(i) := sched_rec.order_number;
753 x_atp_rec.Demand_Source_Header_Id(i) := sched_rec.order_header_id;
754 x_atp_rec.Demand_Source_Type(i) := sched_rec.Demand_Source_Type;
755 x_atp_rec.demand_source_delivery(i) :=sched_rec.demand_source_delivery;
756 x_atp_rec.atp_lead_time(i) := sched_rec.atp_lead_time;
757 x_atp_rec.Scenario_Id(i) := sched_rec.Scenario_Id;
758 x_atp_rec.calling_module(i) := sched_rec.calling_module;
759 x_atp_rec.customer_id(i) := sched_rec.customer_id;
760 x_atp_rec.customer_site_id(i) := sched_rec.customer_site_id;
761 x_atp_rec.destination_time_zone(i) :=sched_rec.destination_time_zone;
762 x_atp_rec.quantity_ordered(i) := sched_rec.quantity_ordered;
763 x_atp_rec.quantity_uom(i) := sched_rec.uom_code;
764 x_atp_rec.requested_ship_date(i) := sched_rec.requested_ship_date;
765 x_atp_rec.requested_arrival_date(i) :=sched_rec.requested_arrival_date;
766 x_atp_rec.latest_acceptable_date(i) :=sched_rec.latest_acceptable_date;
767 x_atp_rec.delivery_lead_time(i) := sched_rec.delivery_lead_time;
768 x_atp_rec.freight_carrier(i) :=sched_rec.freight_carrier;
769 x_atp_rec.ship_method(i) :=sched_rec.ship_method;
770 x_atp_rec.demand_class(i) :=sched_rec.demand_class;
771 x_atp_rec.ship_set_name(i) := sched_rec.ship_set_name;
772 x_atp_rec.arrival_set_name(i) :=sched_rec.arrival_set_name ;
773 x_atp_rec.override_flag(i) :=sched_rec.override_flag;
774 x_atp_rec.action(i) :=sched_rec.action;
775 x_atp_rec.vendor_id(i) := sched_rec.vendor_id;
776 x_atp_rec.vendor_site_id(i) :=sched_rec.vendor_site_id;
777 x_atp_rec.insert_flag(i) := sched_rec.insert_flag;
778 x_atp_rec.Ship_Date(i) := sched_rec.scheduled_ship_date;
779 -- rajjain 02/21/2003 Bug 2815484
780 x_atp_rec.Arrival_Date(i) := sched_rec.scheduled_arrival_date;
781 x_atp_rec.available_quantity(i):= sched_rec.available_quantity;
782 x_atp_rec.requested_date_quantity(i) := sched_rec.requested_date_quantity;
783 x_atp_rec.group_ship_date(i) := sched_rec.group_ship_date;
784 x_atp_rec.group_arrival_date(i) := sched_rec.group_arrival_date;
785 x_atp_rec.vendor_name(i) := sched_rec.vendor_name;
786 x_atp_rec.vendor_site_name(i) := sched_rec.vendor_site_name;
787 x_atp_rec.error_code(i) := sched_rec.error_code;
788 x_atp_rec.oe_flag(i) := sched_rec.oe_flag;
789 x_atp_rec.end_pegging_id(i) := sched_rec.end_pegging_id;
790 x_atp_rec.old_source_organization_id(i)
791 := sched_rec.old_source_organization_id;
792 x_atp_rec.old_demand_class(i) := sched_rec.old_demand_class;
793 x_atp_rec.attribute_06(i) := sched_rec.attribute_06;
794 x_atp_rec.organization_id(i)
795 := sched_rec.organization_id;
796 x_atp_rec.substitution_typ_code(i) := sched_rec.substitution_typ_code;
797 x_atp_rec.req_item_detail_flag(i) := sched_rec.req_item_detail_flag;
798 x_atp_rec.old_inventory_item_id(i) := sched_rec.old_inventory_item_id;
799 x_atp_rec.request_item_id(i) := sched_rec.request_item_id;
800 x_atp_rec.request_item_name(i) := sched_rec.request_item_name;
801 x_atp_rec.req_item_req_date_qty(i) := sched_rec.req_item_req_date_qty;
802 x_atp_rec.req_item_available_date(i) := sched_rec.req_item_available_date;
803 x_atp_rec.req_item_available_date_qty(i) := sched_rec.req_item_available_date_qty;
804 x_atp_rec.sales_rep(i) := sched_rec.sales_rep;
805 x_atp_rec.customer_contact(i) := sched_rec.customer_contact;
806 x_atp_rec.subst_flag(i) := sched_rec.subst_flag;
807
808 --diag_atp
809 x_atp_rec.attribute_02(i) := sched_rec.diagnostic_atp_flag;
810 IF PG_DEBUG in ('Y', 'C') THEN
811 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'Diagnostic flag: ' || sched_rec.diagnostic_atp_flag);
812 END IF;
813 i := i + 1;
814
815 END LOOP;
816 CLOSE sched_cv;
817
818 END IF;
819
820 */
821 --e_cto_reach
822 IF x_mode = RESULTS_MODE and NVL(p_details_flag, 2) = 1 THEN
823 MSC_ATP_UTILS.Retrieve_Period_And_SD_Data(x_session_id,
824 x_atp_period,
825 x_atp_supply_demand);
826
827 IF PG_DEBUG in ('Y', 'C') THEN
828 msc_sch_wb.atp_debug('leaving get_from_temp_table');
829 END IF;
830 END IF; -- If x_mode = results_mode
831
832 EXCEPTION
833
834 WHEN MSC_ATP_PUB.ATP_INVALID_OBJECTS_FOUND THEN
835 x_return_status := FND_API.G_RET_STS_ERROR;
836 IF PG_DEBUG in ('Y', 'C') THEN
837 msc_sch_wb.atp_debug('error in get_from_temp_table' || sqlerrm);
838 msc_sch_wb.atp_debug('get_from_temp_table: ' || 'Invalid Objects found');
839 END IF;
840 RAISE MSC_ATP_PUB.ATP_INVALID_OBJECTS_FOUND;
841
842 WHEN OTHERS THEN
843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
844 IF PG_DEBUG in ('Y', 'C') THEN
845 msc_sch_wb.atp_debug('error in get_from_temp_table' || sqlerrm);
846 END IF;
847 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
848
849 END get_from_temp_table;
850
851 FUNCTION Call_ATP_11(
852 p_group_id NUMBER,
853 p_session_id NUMBER,
854 p_insert_flag NUMBER,
855 p_partial_flag NUMBER,
856 p_err_message IN OUT NoCopy VARCHAR2)
857 RETURN NUMBER is
858
859 v_dummy NUMBER := 0;
860 x_atp_rec MRP_ATP_PUB.atp_rec_typ;
861 x_atp_rec_out MRP_ATP_PUB.atp_rec_typ;
862 x_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
863 x_atp_period MRP_ATP_PUB.ATP_Period_Typ;
864 x_atp_details MRP_ATP_PUB.ATP_Details_Typ;
865 x_return_status VARCHAR2(1);
866 x_msg_data VARCHAR2(200);
867 x_msg_count NUMBER;
868 x_session_id NUMBER;
869
870 ato_exists VARCHAR2(1) := 'N';
871 j NUMBER;
872
873 BEGIN
874
875 SELECT NVL(count(*),0)
876 INTO v_dummy
877 FROM mtl_demand_interface mdi3,
878 mtl_demand_interface mdi
879 WHERE mdi3.demand_source_header_id = mdi.demand_source_header_id
880 AND mdi3.demand_source_line = mdi.demand_source_line
881 AND mdi3.demand_source_delivery = mdi.demand_source_delivery
882 AND mdi3.demand_source_type = mdi.demand_source_type
883 AND mdi3.schedule_group_id = mdi.schedule_group_id
884 AND mdi3.atp_group_id <> mdi.atp_group_id
885 AND mdi3.transaction_process_order < mdi.transaction_process_order
886 AND mdi.atp_group_id = p_group_id;
887
888 -- this takes care of the case when call is made for rows already
889 -- processed. Eg. change orders case
890 IF (v_dummy <> 0) THEN
891 IF PG_DEBUG in ('Y', 'C') THEN
892 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' Calling API 02 ');
893 END IF;
894
895 return(INV_EXTATP_GRP.G_ALL_SUCCESS);
896 END IF;
897
898 -- Need to add logic where multiple same item requests are
899 -- grouped and sent to ATP API, and updates also must be appropriate.
900
901 SELECT
902 rowidTochar(mdi.ROWID) row_id, /* unique identifier */
903 mdi.inventory_item_id,
904 mdi.organization_id, /* source organization id */
905 NVL(mdi.demand_source_line,-1), /* identifier */
906 NVL(mdi.demand_source_header_id, -1),
907 NVL(mdi.demand_source_type, -1),
908 mdi.Demand_Source_Delivery,
909 mdi.atp_lead_time,
910 -- NULL, /* scenario id */
911 NULL, /* calling module - not used */
912 NULL, /* customer_id - Not needed since source org is known*/
913 NULL, /* customer site id */
914 NULL, /* dest time zone */
915 nvl(mdi.primary_uom_quantity, mdi.line_item_quantity), /* quantity */
916 nvl(msi.primary_uom_code, mdi.line_item_uom), /* UOM */
917 mdi.requirement_date request_date, /* requirement_date */
918 NULL, /* requested arrival date */
919 mdi.latest_acceptable_date, /* Latest_Acceptable_Date */
920 NULL, /* Delivery_Lead_Time */
921 NULL, /* Freight_Carrier */
922 NULL, /* Ship_Method */
923 mdi.demand_class, /* Demand_Class */
924 Decode(p_partial_flag,0,'Ship Set',NULL), /* Ship_Set_Name */
925 NULL, /* Arrival_Set_Name */
926 NULL, /* Override_Flag */
927 Nvl(mdi.action_code,100), /*ATP action code - eg.ATP inquiry,demand */
928 NULL, /* Ship_Date */
929 NULL, /* Available_Quantity */
930 NULL, /* Requested_Date_Quantity */
931 NULL, /* Group_Available_Date */
932 NULL, /* Group_Arrival_Date */
933 NULL, /* Vendor_Id */
934 NULL, /* Vendor_Site_Id */
935 p_insert_flag, /* Insert_Flag */
936 NULL, /* Error_Code */
937 NULL /* Message */
938 bulk collect INTO
939 x_atp_rec.ROW_ID,
940 x_atp_rec.inventory_item_id,
941 x_atp_rec.source_organization_id,
942 x_atp_rec.identifier,
943 x_atp_rec.Demand_Source_Header_Id,
944 x_atp_rec.Demand_Source_Type,
945 x_atp_rec.Demand_Source_Delivery,
946 x_atp_rec.atp_lead_time,
947 -- x_atp_rec.scenario_id,
948 x_atp_rec.calling_module,
949 x_atp_rec.customer_id,
950 x_atp_rec.customer_site_id,
951 x_atp_rec.destination_time_zone,
952 x_atp_rec.quantity_ordered,
953 x_atp_rec.quantity_uom,
954 x_atp_rec.requested_ship_date,
955 x_atp_rec.requested_arrival_date,
956 x_atp_rec.latest_acceptable_date,
957 x_atp_rec.delivery_lead_time,
958 x_atp_rec.freight_carrier,
959 x_atp_rec.ship_method,
960 x_atp_rec.demand_class,
961 x_atp_rec.ship_set_name,
962 x_atp_rec.arrival_set_name,
963 x_atp_rec.override_flag,
964 x_atp_rec.action,
965 x_atp_rec.ship_date,
966 x_atp_rec.available_quantity,
967 x_atp_rec.requested_date_quantity,
968 x_atp_rec.group_ship_date,
969 x_atp_rec.group_arrival_date,
970 x_atp_rec.vendor_id,
971 x_atp_rec.vendor_site_id,
972 x_atp_rec.insert_flag,
973 x_atp_rec.error_code,
974 x_atp_rec.message
975 FROM
976 mrp_ap_apps_instances mai,
977 ORG_ORGANIZATION_DEFINITIONS ood,
978 MTL_SYSTEM_ITEMS msi,
979 MTL_DEMAND_INTERFACE mdi
980 WHERE mdi.atp_group_id = p_group_id
981 AND ((mdi.demand_type = 1
982 AND EXISTS (SELECT 'ATO Model exists'
983 FROM mtl_demand_interface
984 WHERE atp_group_id = mdi.atp_group_id
985 AND demand_type = 1))
986 OR NOT EXISTS (SELECT 'ATO Model exists'
987 FROM mtl_demand_interface
988 WHERE atp_group_id = mdi.atp_group_id
989 AND demand_type = 1))
990 AND Nvl(mdi.process_flag,1) = 1
991 AND nvl(mdi.error_code,61) = 61
992 AND ood.organization_id = mdi.organization_id
993 AND msi.organization_id = mdi.organization_id
994 AND msi.inventory_item_id = mdi.inventory_item_id;
995
996 -- Update mtl_demand_interface with the right return values
997 -- call atp module
998 IF PG_DEBUG in ('Y', 'C') THEN
999 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' Calling API '||x_atp_rec.Inventory_Item_Id.COUNT);
1000 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 0 '||x_atp_rec.inventory_item_id(1));
1001 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 1 '||x_atp_rec.Source_Organization_Id(1));
1002 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 2 '||x_atp_rec.Identifier(1));
1003 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 3 '||x_atp_rec.Calling_Module(1));
1004 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 4 '||x_atp_rec.Quantity_Ordered(1));
1005 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 5 '||x_atp_rec.Quantity_UOM(1));
1006 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 6 '||x_atp_rec.Requested_Ship_Date(1));
1007 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 7 '||x_atp_rec.Latest_Acceptable_Date(1));
1008 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 8 '||x_atp_rec.Action(1));
1009 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 9 '||x_atp_rec.Insert_Flag(1));
1010 END IF;
1011
1012 x_session_id := p_session_id;
1013
1014 MSC_ATP_PUB.Call_ATP(
1015 x_session_id,
1016 x_atp_rec,
1017 x_atp_rec_out,
1018 x_atp_supply_demand,
1019 x_atp_period,
1020 x_atp_details,
1021 x_return_status,
1022 x_msg_data,
1023 x_msg_count);
1024
1025 IF PG_DEBUG in ('Y', 'C') THEN
1026 msc_sch_wb.atp_debug('Call_ATP_11: ' || 'x_atp_rec_out.Ship_Date:'
1027 || to_char(x_atp_rec_out.Ship_Date(1)) );
1028 msc_sch_wb.atp_debug('Call_ATP_11: ' || 'x_atp_rec_out.Available_Quantity:'
1029 || to_char(x_atp_rec_out.Available_Quantity(1)) );
1030 msc_sch_wb.atp_debug('Call_ATP_11: ' || 'x_atp_rec_out.Requested_Date_Quantity:'
1031 || to_char(x_atp_rec_out.Requested_Date_Quantity(1)) );
1032 END IF;
1033
1034
1035 IF x_return_status <> FND_API.g_ret_sts_success THEN
1036 FOR j IN 1..x_atp_rec_out.Inventory_Item_Id.COUNT loop
1037 UPDATE mtl_demand_interface set
1038 LAST_UPDATE_DATE = SYSDATE,
1039 Error_Code = X_atp_rec_out.error_code(j),
1040 Err_Explanation = X_atp_rec_out.message(j)
1041 WHERE rowid = Chartorowid(X_atp_rec_out.row_id(j));
1042 END LOOP;
1043
1044 IF PG_DEBUG in ('Y', 'C') THEN
1045 msc_sch_wb.atp_debug('Call_ATP_11: ' || 'Errpr :'||x_msg_data);
1046 END IF;
1047 ELSE
1048
1049 IF x_atp_rec_out.Inventory_Item_Id.COUNT > 0 THEN
1050 IF PG_DEBUG in ('Y', 'C') THEN
1051 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' atp_rec_out count '||x_atp_rec_out.ship_date.COUNT);
1052 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 00 '||x_atp_rec_out.row_id(1));
1053 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 00 '||x_atp_rec_out.requested_date_quantity(1));
1054 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 11 '||x_atp_rec_out.ship_date(1));
1055 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 22 '||x_atp_rec_out.available_quantity(1));
1056 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 33 '||x_atp_rec_out.group_ship_date(1));
1057 END IF;
1058 END IF;
1059
1060 IF x_atp_rec_out.Inventory_Item_Id.COUNT > 0 THEN
1061 FOR j IN 1..x_atp_rec_out.Inventory_Item_Id.COUNT LOOP
1062 IF PG_DEBUG in ('Y', 'C') THEN
1063 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' atp_rec_out '||j);
1064 END IF;
1065 -- FORALL j IN 1..x_atp_rec_out.Inventory_Item_Id.COUNT
1066 -- For all was giving wierd problems in Updating date variables.
1067
1068 -- ?? Infinite_time_fence_date is being updated in inlatp.ppc
1069 IF PG_DEBUG in ('Y', 'C') THEN
1070 msc_sch_wb.atp_debug('Call_ATP_11: ' || 'christine, request date atp q is'||x_Atp_rec_out.requested_date_quantity(j));
1071 END IF;
1072 UPDATE mtl_demand_interface
1073 SET Request_Date_ATP_Quantity = x_Atp_rec_out.requested_date_quantity(j),
1074 Request_ATP_Date = To_date(To_char(X_atp_rec_out.ship_date(j), 'J'),'J'),
1075 Request_ATP_Date_Quantity = X_atp_rec_out.available_quantity(j),
1076 Group_Available_Date = NVL(X_atp_rec_out.group_ship_date(j),
1077 requirement_date),
1078 Error_Code = X_atp_rec_out.error_code(j),
1079 Err_Explanation = X_atp_rec_out.message(j)
1080 WHERE rowid = Chartorowid(X_atp_rec_out.row_id(j));
1081 END LOOP;
1082 END IF;
1083
1084 IF x_atp_supply_demand.Inventory_Item_Id.COUNT > 0 THEN
1085
1086 IF PG_DEBUG in ('Y', 'C') THEN
1087 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' atp_sd count '||x_atp_supply_demand.Inventory_Item_Id.COUNT);
1088 END IF;
1089
1090 FOR j IN 1..x_atp_supply_demand.inventory_item_id.COUNT LOOP
1091 IF PG_DEBUG in ('Y', 'C') THEN
1092 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' atp_sd '||j);
1093 END IF;
1094 INSERT INTO MTL_SUPPLY_DEMAND_TEMP
1095 (
1096 RECORD_TYPE,
1097 SUPPLY_DEMAND_SOURCE_TYPE,
1098 ON_HAND_QUANTITY,
1099 QUANTITY,
1100 DISPOSITION_TYPE,
1101 DISPOSITION_ID,
1102 SUPPLY_DEMAND_TYPE,
1103 REQUIREMENT_DATE,
1104 SEQ_NUM,
1105 GROUP_ID,
1106 LAST_UPDATE_DATE,
1107 LAST_UPDATED_BY,
1108 CREATION_DATE,
1109 CREATED_BY,
1110 LAST_UPDATE_LOGIN,
1111 INVENTORY_ITEM_ID,
1112 ORGANIZATION_ID,
1113 C_COLUMN1,
1114 C_COLUMN8)
1115 VALUES(
1116 'SD',
1117 x_atp_supply_demand.supply_demand_source_type(j),
1118 NULL, -- New ATP does not calculate this
1119 ROUND(x_atp_supply_demand.supply_demand_quantity(j),5),
1120 x_atp_supply_demand.disposition_type(j),
1121 x_atp_supply_demand.identifier3(j),
1122 x_atp_supply_demand.supply_demand_type(j),
1123 x_atp_supply_demand.supply_demand_date(j),
1124 p_group_id,
1125 p_session_id,
1126 SYSDATE,
1127 0,
1128 SYSDATE,
1129 0,
1130 -1,
1131 x_atp_supply_demand.inventory_item_id(j),
1132 x_atp_supply_demand.organization_id(j),
1133 NULL, -- We don't need this since the form handles all cases.
1134 x_atp_supply_demand.disposition_name(j)
1135 );
1136
1137 END LOOP;
1138 END IF;
1139 IF x_atp_period.inventory_item_id.COUNT > 0 THEN
1140 IF PG_DEBUG in ('Y', 'C') THEN
1141 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' atp_period count '||x_atp_period.Inventory_Item_Id.COUNT);
1142 END IF;
1143
1144 FOR j IN 1..x_atp_period.inventory_item_id.COUNT LOOP
1145 IF PG_DEBUG in ('Y', 'C') THEN
1146 msc_sch_wb.atp_debug('Call_ATP_11: ' || ' atp period '||j);
1147 END IF;
1148 INSERT INTO MTL_SUPPLY_DEMAND_TEMP
1149 (
1150 ATP_PERIOD_START_DATE,
1151 ATP_PERIOD_END_DATE,
1152 ATP_PERIOD_TOTAL_SUPPLY,
1153 ATP_PERIOD_TOTAL_DEMAND,
1154 ATP,
1155 RECORD_TYPE,
1156 SEQ_NUM,
1157 GROUP_ID,
1158 LAST_UPDATE_DATE,
1159 LAST_UPDATED_BY,
1160 CREATION_DATE,
1161 CREATED_BY,
1162 LAST_UPDATE_LOGIN,
1163 PERIOD_NET_AVAILABLE,
1164 INVENTORY_ITEM_ID,
1165 ORGANIZATION_ID)
1166 VALUES(
1167 x_atp_period.Period_Start_Date(j),
1168 x_atp_period.Period_End_Date(j),
1169 Round(x_atp_period.total_supply_quantity(j),5),
1170 Round(x_atp_period.total_demand_quantity(j),5),
1171 Round(x_atp_period.cumulative_quantity(j), 5),
1172 'ATP',
1173 p_group_id,
1174 p_session_id,
1175 SYSDATE,
1176 0,
1177 SYSDATE,
1178 0,
1179 -1,
1180 Round(x_atp_period.period_quantity(j),5),
1181 x_atp_period.inventory_item_id(j),
1182 x_atp_period.organization_id(j));
1183 END LOOP;
1184 END IF;
1185
1186 END IF;
1187
1188 return(INV_EXTATP_GRP.G_ALL_SUCCESS);
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 p_err_message := substr(sqlerrm,1,100);
1192 return(INV_EXTATP_GRP.G_RETURN_ERROR);
1193 End Call_ATP_11;
1194
1195 PROCEDURE extend_mast( mast_rec IN OUT NoCopy mrp_atp_utils.mrp_atp_schedule_temp_typ,
1196 x_ret_code OUT NoCopy varchar2,
1197 x_ret_status OUT NoCopy varchar2) IS
1198 BEGIN
1199 mast_rec.rowid_char.extend(1);
1200 mast_rec.sequence_number.extend(1);
1201 mast_rec.firm_flag.extend(1);
1202 mast_rec.order_line_number.extend(1);
1203 mast_rec.option_number.extend(1);
1204 mast_rec.shipment_number.extend(1);
1205 mast_rec.item_desc.extend(1);
1206 mast_rec.customer_name.extend(1);
1207 mast_rec.customer_location.extend(1);
1208 mast_rec.ship_set_name.extend(1);
1209 mast_rec.arrival_set_name.extend(1);
1210 mast_rec.requested_ship_date.extend(1);
1211 mast_rec.requested_arrival_date.extend(1);
1212 mast_rec.old_line_schedule_date.extend(1);
1213 mast_rec.old_source_organization_code.extend(1);
1214 mast_rec.firm_source_org_id.extend(1);
1215 mast_rec.firm_source_org_code.extend(1);
1216 mast_rec.firm_ship_date.extend(1);
1217 mast_rec.firm_arrival_date.extend(1);
1218 mast_rec.ship_method_text.extend(1);
1219 mast_rec.ship_set_id.extend(1);
1220 mast_rec.arrival_set_id.extend(1);
1221 mast_rec.project_id.extend(1);
1222 mast_rec.task_id.extend(1);
1223 mast_rec.project_number.extend(1);
1224 mast_rec.task_number.extend(1);
1225 EXCEPTION
1226 WHEN OTHERS THEN
1227 IF PG_DEBUG in ('Y', 'C') THEN
1228 msc_sch_wb.atp_debug('Excp in extend_mast : '||Substr(Sqlerrm,1,100));
1229 END IF;
1230 END extend_mast;
1231
1232
1233 PROCEDURE trim_mast( mast_rec IN OUT NoCopy mrp_atp_utils.mrp_atp_schedule_temp_typ,
1234 x_ret_code OUT NoCopy varchar2,
1235 x_ret_status OUT NoCopy varchar2) IS
1236 BEGIN
1237 mast_rec.rowid_char.trim(1);
1238 mast_rec.sequence_number.trim(1);
1239 mast_rec.firm_flag.trim(1);
1240 mast_rec.order_line_number.trim(1);
1241 mast_rec.option_number.trim(1);
1242 mast_rec.shipment_number.trim(1);
1243 mast_rec.item_desc.trim(1);
1244 mast_rec.customer_name.trim(1);
1245 mast_rec.customer_location.trim(1);
1246 mast_rec.ship_set_name.trim(1);
1247 mast_rec.arrival_set_name.trim(1);
1248 mast_rec.requested_ship_date.trim(1);
1249 mast_rec.requested_arrival_date.trim(1);
1250 mast_rec.old_line_schedule_date.trim(1);
1251 mast_rec.old_source_organization_code.trim(1);
1252 mast_rec.firm_source_org_id.trim(1);
1253 mast_rec.firm_source_org_code.trim(1);
1254 mast_rec.firm_ship_date.trim(1);
1255 mast_rec.firm_arrival_date.trim(1);
1256 mast_rec.ship_method_text.trim(1);
1257 mast_rec.ship_set_id.trim(1);
1258 mast_rec.arrival_set_id.trim(1);
1259 mast_rec.project_id.trim(1);
1260 mast_rec.task_id.trim(1);
1261 mast_rec.project_number.trim(1);
1262 mast_rec.task_number.trim(1);
1263 EXCEPTION
1264 WHEN OTHERS THEN
1265 IF PG_DEBUG in ('Y', 'C') THEN
1266 msc_sch_wb.atp_debug('Excp in trim_mast : '||Substr(Sqlerrm,1,100));
1267 END IF;
1268 END trim_mast;
1269
1270 -- Bug 2974324. Redundant test procedure removed from here
1271
1272
1273 -- Added on 10/16/00 by ngoel for inserting BOM data into MSC_BOM_TEMP
1274 -- table when ATP is called with CTO models from OM or Configurator.
1275
1276 PROCEDURE put_into_bom_temp_table(
1277 p_session_id IN NUMBER,
1278 p_dblink IN VARCHAR2,
1279 p_atp_bom_rec IN MRP_ATP_PUB.ATP_BOM_Rec_Typ,
1280 x_return_status OUT NoCopy VARCHAR2,
1281 x_msg_data OUT NoCopy VARCHAR2,
1282 x_msg_count OUT NoCopy NUMBER)
1283 IS
1284
1285 j PLS_INTEGER;
1286 l_dynstring VARCHAR2(128) := NULL;
1287 sql_stmt VARCHAR2(10000);
1288
1289 BEGIN
1290
1291 IF PG_DEBUG in ('Y', 'C') THEN
1292 msc_sch_wb.atp_debug('****Begin put_into_bom_temp_table ****');
1293 END IF;
1294 -- initialize API returm status to success
1295 x_return_status := FND_API.G_RET_STS_SUCCESS;
1296
1297 IF p_dblink IS NOT NULL THEN
1298 l_dynstring := '@'||p_dblink;
1299 END IF;
1300
1301 -- Delete records from msc_bom_temp_table in case there are any records
1302 -- with similar session id.
1303
1304 -- bug 2974324. Changed the dynamic SQL to static if db_link is null
1305 IF p_dblink IS NULL THEN
1306 DELETE msc_bom_temp WHERE session_id = p_session_id;
1307 ELSE
1308 sql_stmt := 'DELETE msc_bom_temp'||l_dynstring|| ' WHERE session_id = :session_id';
1309
1310 IF PG_DEBUG in ('Y', 'C') THEN
1311 msc_sch_wb.atp_debug('put_into_bom_temp_table: ' || 'sql_stmt : '||sql_stmt);
1312 END IF;
1313
1314 EXECUTE IMMEDIATE sql_stmt using p_session_id;
1315 END IF;
1316
1317 IF PG_DEBUG in ('Y', 'C') THEN
1318 msc_sch_wb.atp_debug('put_into_bom_temp_table: ' || 'After deleting from msc_bom_temp table');
1319 END IF;
1320
1321 j := p_atp_bom_rec.assembly_identifier.FIRST;
1322 IF PG_DEBUG in ('Y', 'C') THEN
1323 msc_sch_wb.atp_debug('put_into_bom_temp_table: ' || 'j = '||j);
1324 END IF;
1325
1326 -- bug 2974324. Changed the dynamic SQL to static if db_link is null
1327 IF p_dblink IS NOT NULL THEN
1328
1329 WHILE j IS NOT NULL LOOP
1330 IF PG_DEBUG in ('Y', 'C') THEN
1331 msc_sch_wb.atp_debug('put_into_bom_temp_table: ' || 'in loop j = '||j ||' : '|| 'item_id : ' ||
1332 p_atp_bom_rec.assembly_item_id(j) || ' : '||
1333 'comp item_id : ' ||p_atp_bom_rec.component_item_id(j)||
1334 ' atp flag : ' || p_atp_bom_rec.atp_check(j));
1335 END IF;
1336 sql_stmt := 'INSERT INTO msc_bom_temp'||l_dynstring|| ' (
1337 session_id,
1338 assembly_identifier,
1339 assembly_item_id,
1340 component_identifier,
1341 component_item_id,
1342 quantity,
1343 fixed_lt,
1344 variable_lt,
1345 effective_date,
1346 disable_date,
1347 atp_check,
1348 wip_supply_type,
1349 smc_flag,
1350 pre_process_lt,
1351 source_organization_id, -- krajan: 2400614
1352 atp_flag -- krajan: 2462661
1353 )
1354 VALUES (
1355 :session_id,
1356 :assembly_identifier,
1357 :assembly_item_id,
1358 :component_identifier,
1359 :component_item_id,
1360 :quantity,
1361 :fixed_lt,
1362 :variable_lt,
1363 :effective_date,
1364 :disable_date,
1365 :atp_check,
1366 :wip_supply_type,
1367 :smc_flag,
1368 :pre_process_lt,
1369 -- krajan : 2400614
1370 :source_organization_id,
1371 -- krajan : 2462661
1372 :atp_flag
1373 )';
1374
1375
1376 IF PG_DEBUG in ('Y', 'C') THEN
1377 msc_sch_wb.atp_debug('put_into_bom_temp_table: ' || 'after insert into bom_temp');
1378 END IF;
1379
1380 EXECUTE IMMEDIATE sql_stmt using
1381 p_session_id,
1382 p_atp_bom_rec.assembly_identifier(j),
1383 p_atp_bom_rec.assembly_item_id(j),
1384 p_atp_bom_rec.component_identifier(j),
1385 p_atp_bom_rec.component_item_id(j),
1386 p_atp_bom_rec.quantity(j),
1387 p_atp_bom_rec.fixed_lt(j),
1388 p_atp_bom_rec.variable_lt(j),
1389 p_atp_bom_rec.effective_date(j),
1390 p_atp_bom_rec.disable_date(j),
1391 p_atp_bom_rec.atp_check(j),
1392 p_atp_bom_rec.wip_supply_type(j),
1393 p_atp_bom_rec.smc_flag(j),
1394 p_atp_bom_rec.pre_process_lt(j),
1395 -- krajan: 2400614
1396 p_atp_bom_rec.source_organization_id(j),
1397 -- krajan: 2462661
1398 p_atp_bom_rec.atp_flag(j);
1399
1400 j := p_atp_bom_rec.assembly_identifier.NEXT(j);
1401
1402 END LOOP;
1403
1404 ELSE -- bug 2974324. Changed the dynamic SQL to static if db_link is null
1405
1406 FORALL j in 1..p_atp_bom_rec.assembly_identifier.COUNT
1407 INSERT INTO msc_bom_temp (
1408 session_id,
1409 assembly_identifier,
1410 assembly_item_id,
1411 component_identifier,
1412 component_item_id,
1413 quantity,
1414 fixed_lt,
1415 variable_lt,
1416 effective_date,
1417 disable_date,
1418 atp_check,
1419 wip_supply_type,
1420 smc_flag,
1421 pre_process_lt,
1422 source_organization_id,
1423 atp_flag)
1424 VALUES(
1425 p_session_id,
1426 p_atp_bom_rec.assembly_identifier(j),
1427 p_atp_bom_rec.assembly_item_id(j),
1428 p_atp_bom_rec.component_identifier(j),
1429 p_atp_bom_rec.component_item_id(j),
1430 p_atp_bom_rec.quantity(j),
1431 p_atp_bom_rec.fixed_lt(j),
1432 p_atp_bom_rec.variable_lt(j),
1433 p_atp_bom_rec.effective_date(j),
1434 p_atp_bom_rec.disable_date(j),
1435 p_atp_bom_rec.atp_check(j),
1436 p_atp_bom_rec.wip_supply_type(j),
1437 p_atp_bom_rec.smc_flag(j),
1438 p_atp_bom_rec.pre_process_lt(j),
1439 p_atp_bom_rec.source_organization_id(j),
1440 p_atp_bom_rec.atp_flag(j));
1441
1442 END IF; -- bug 2974324. Changed the dynamic SQL to static if db_link is null
1443
1444
1445 IF PG_DEBUG in ('Y', 'C') THEN
1446 msc_sch_wb.atp_debug('****End put_into_bom_temp_table ****');
1447 END IF;
1448 EXCEPTION
1449 WHEN OTHERS THEN
1450 IF PG_DEBUG in ('Y', 'C') THEN
1451 msc_sch_wb.atp_debug(' Error in put_into_bom_temp_table '||substr(sqlerrm,1,100));
1452 END IF;
1453 x_msg_data := substr(sqlerrm,1,100);
1454 x_return_status := FND_API.G_RET_STS_ERROR;
1455 END put_into_bom_temp_table;
1456
1457
1458 PROCEDURE Put_Period_Data (
1459 p_atp_period IN MRP_ATP_PUB.ATP_Period_Typ,
1460 p_dblink IN VARCHAR2,
1461 p_session_id IN NUMBER )
1462 IS
1463 sql_stmt VARCHAR2(10000);
1464 rows_processed NUMBER;
1465 cur_handler NUMBER;
1466 l_user_id NUMBER;
1467 l_sysdate DATE;
1468 BEGIN
1469
1470 IF p_atp_period.level.COUNT > 0 THEN
1471
1472 IF PG_DEBUG in ('Y', 'C') THEN
1473 msc_sch_wb.atp_debug('PROCEDURE Put_Period_Data');
1474 msc_sch_wb.atp_debug('Put_Period_Data: ' || ' period records '||p_atp_period.level.COUNT);
1475 END IF;
1476
1477 l_user_id := FND_GLOBAL.user_id;
1478 l_sysdate := sysdate;
1479
1480 IF p_dblink IS NULL THEN
1481 FORALL j IN 1..p_atp_period.level.COUNT
1482
1483 INSERT INTO mrp_atp_details_temp
1484 (
1485 session_id,
1486 scenario_id,
1487 order_line_id,
1488 atp_LEVEL,
1489 inventory_item_id,
1490 request_item_id,
1491 organization_id,
1492 department_id,
1493 resource_id,
1494 supplier_id,
1495 supplier_site_id,
1496 from_organization_id,
1497 from_location_id,
1498 to_organization_id,
1499 to_location_id,
1500 ship_method,
1501 uom_code,
1502 total_supply_quantity,
1503 total_demand_quantity,
1504 total_bucketed_demand_quantity, -- time_phased_atp
1505 period_start_date,
1506 period_end_date,
1507 period_quantity,
1508 cumulative_quantity,
1509 identifier1,
1510 identifier2,
1511 record_type,
1512 pegging_id,
1513 end_pegging_id
1514 -- dsting
1515 , creation_date
1516 , created_by
1517 , last_update_date
1518 , last_updated_by
1519 , last_update_login
1520 )
1521 VALUES
1522 (
1523 p_session_id,
1524 p_atp_period.scenario_id(j),
1525 p_atp_period.identifier(j),
1526 p_atp_period.level(j),
1527 p_atp_period.inventory_item_id(j),
1528 p_atp_period.request_item_id(j),
1529 p_atp_period.organization_id(j),
1530 p_atp_period.department_id(j),
1531 p_atp_period.resource_id(j),
1532 p_atp_period.supplier_id(j),
1533 p_atp_period.supplier_site_id(j),
1534 p_atp_period.from_organization_id(j),
1535 p_atp_period.from_location_id(j),
1536 p_atp_period.to_organization_id(j),
1537 p_atp_period.to_location_id(j),
1538 p_atp_period.ship_method(j),
1539 p_atp_period.uom(j),
1540 p_atp_period.total_supply_quantity(j),
1541 p_atp_period.total_demand_quantity(j),
1542 p_atp_period.total_bucketed_demand_quantity(j), -- time_phased_atp
1543 p_atp_period.period_start_date(j),
1544 p_atp_period.period_end_date(j),
1545 p_atp_period.period_quantity(j),
1546 p_atp_period.cumulative_quantity(j),
1547 p_atp_period.identifier1(j),
1548 p_atp_period.identifier2(j),
1549 1,
1550 p_atp_period.pegging_id(j),
1551 p_atp_period.end_pegging_id(j)
1552 -- dsting
1553 , l_sysdate -- creation_date
1554 , L_USER_ID -- created_by
1555 , l_sysdate -- last_update_date
1556 , L_USER_ID -- update_by
1557 , L_USER_ID -- login_by
1558 );
1559
1560 ELSE -- IF x_dblink IS NULL THEN
1561 IF PG_DEBUG in ('Y', 'C') THEN
1562 msc_sch_wb.atp_debug(' XXX Put_Period_Data should not be called with dblink');
1563 END IF;
1564
1565 -- dsting: added stuff for creation_date, created_by,
1566 -- last_update_date, last_updated_by, last_update_login
1567
1568 sql_stmt := '
1569 INSERT INTO mrp_atp_details_temp'||p_dblink||'
1570 (
1571 session_id,
1572 scenario_id,
1573 order_line_id,
1574 atp_LEVEL,
1575 inventory_item_id,
1576 request_item_id,
1577 organization_id,
1578 department_id,
1579 resource_id,
1580 supplier_id,
1581 supplier_site_id,
1582 from_organization_id,
1583 from_location_id,
1584 to_organization_id,
1585 to_location_id,
1586 ship_method,
1587 uom_code,
1588 total_supply_quantity,
1589 total_demand_quantity,
1590 total_bucketed_demand_quantity, -- time_phased_atp
1591 period_start_date,
1592 period_end_date,
1593 period_quantity,
1594 cumulative_quantity,
1595 identifier1,
1596 identifier2,
1597 record_type,
1598 pegging_id,
1599 end_pegging_id
1600 , creation_date
1601 , created_by
1602 , last_update_date
1603 , last_updated_by
1604 , last_update_login
1605 )
1606 VALUES
1607 (
1608 :x_session_id,
1609 :scenario_id,
1610 :identifier,
1611 :atp_level,
1612 :inventory_item_id,
1613 :request_item_id,
1614 :organization_id,
1615 :department_id,
1616 :resource_id,
1617 :supplier_id,
1618 :supplier_site_id,
1619 :from_organization_id,
1620 :from_location_id,
1621 :to_organization_id,
1622 :to_location_id,
1623 :ship_method,
1624 :uom,
1625 :total_supply_quantity,
1626 :total_demand_quantity,
1627 :total_bucketed_demand_quantity, -- time_phased_atp
1628 :period_start_date,
1629 :period_end_date,
1630 :period_quantity,
1631 :cumulative_quantity,
1632 :identifier1,
1633 :identifier2,
1634 1,
1635 :pegging_id,
1636 :end_pegging_id
1637 , sysdate
1638 , :created_by
1639 , sysdate
1640 , :created_by
1641 , :created_by
1642 )';
1643
1644 -- Obtain cursor handler for sql_stmt
1645 cur_handler := DBMS_SQL.OPEN_CURSOR;
1646
1647 -- Parse cursor handler for sql_stmt
1648 DBMS_SQL.PARSE(cur_handler, sql_stmt, DBMS_SQL.NATIVE);
1649
1650 IF PG_DEBUG in ('Y', 'C') THEN
1651 msc_sch_wb.atp_debug('Put_Period_Data: ' || 'enter put_into_temp_table :just before execute');
1652 END IF;
1653
1654 FOR j IN 1..p_atp_period.level.COUNT LOOP
1655
1656 -- Bind variables in the loop for insert.
1657
1658 DBMS_SQL.BIND_VARIABLE(cur_handler, ':x_session_id', p_session_id);
1659 DBMS_SQL.BIND_VARIABLE(cur_handler, ':scenario_id', p_atp_period.scenario_id(j));
1660 DBMS_SQL.BIND_VARIABLE(cur_handler, ':identifier', p_atp_period.identifier(j));
1661 DBMS_SQL.BIND_VARIABLE(cur_handler, ':atp_level', p_atp_period.Level(j));
1662 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Inventory_Item_Id', p_atp_period.Inventory_Item_Id(j));
1663 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Request_Item_Id', p_atp_period.Request_Item_Id(j));
1664 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Organization_Id', p_atp_period.Organization_Id(j));
1665 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Department_Id', p_atp_period.Department_Id(j));
1666 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Resource_Id', p_atp_period.Resource_Id(j));
1667 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Supplier_Id', p_atp_period.Supplier_Id(j));
1668 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Supplier_Site_Id', p_atp_period.Supplier_Site_Id(j));
1669 DBMS_SQL.BIND_VARIABLE(cur_handler, ':From_Organization_Id', p_atp_period.From_Organization_Id(j));
1670 DBMS_SQL.BIND_VARIABLE(cur_handler, ':From_Location_Id', p_atp_period.From_Location_Id(j));
1671 DBMS_SQL.BIND_VARIABLE(cur_handler, ':To_Organization_Id', p_atp_period.To_Organization_Id(j));
1672 DBMS_SQL.BIND_VARIABLE(cur_handler, ':To_Location_Id', p_atp_period.To_Location_Id(j));
1673 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Ship_Method', p_atp_period.Ship_Method(j));
1674 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Uom', p_atp_period.Uom(j));
1675 DBMS_SQL.BIND_VARIABLE(cur_handler, ':total_supply_quantity', p_atp_period.total_supply_quantity(j));
1676 DBMS_SQL.BIND_VARIABLE(cur_handler, ':total_demand_quantity', p_atp_period.total_demand_quantity(j));
1677 -- time_phased_atp
1678 DBMS_SQL.BIND_VARIABLE(cur_handler, ':total_bucketed_demand_quantity', p_atp_period.total_bucketed_demand_quantity(j));
1679 DBMS_SQL.BIND_VARIABLE(cur_handler, ':period_start_date', p_atp_period.period_start_date(j));
1680 DBMS_SQL.BIND_VARIABLE(cur_handler, ':period_end_date', p_atp_period.period_end_date(j));
1681 DBMS_SQL.BIND_VARIABLE(cur_handler, ':period_quantity', p_atp_period.period_quantity(j));
1682 DBMS_SQL.BIND_VARIABLE(cur_handler, ':cumulative_quantity', p_atp_period.cumulative_quantity(j));
1683 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Identifier1', p_atp_period.Identifier1(j));
1684 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Identifier2', p_atp_period.Identifier2(j));
1685 DBMS_SQL.BIND_VARIABLE(cur_handler, ':pegging_id', p_atp_period.pegging_id(j));
1686 DBMS_SQL.BIND_VARIABLE(cur_handler, ':end_pegging_id', p_atp_period.end_pegging_id(j));
1687
1688 -- dsting
1689 DBMS_SQL.BIND_VARIABLE(cur_handler, ':created_by',
1690 L_USER_ID);
1691
1692 -- Execute the cursor
1693 rows_processed := DBMS_SQL.EXECUTE(cur_handler);
1694
1695 END LOOP;
1696
1697 IF PG_DEBUG in ('Y', 'C') THEN
1698 msc_sch_wb.atp_debug('Put_Period_Data: ' || 'enter put_into_temp_table :after execute');
1699 END IF;
1700
1701 -- Close the cursor in case it is open
1702 IF DBMS_SQL.IS_OPEN(cur_handler) THEN
1703 DBMS_SQL.CLOSE_CURSOR(cur_handler);
1704 END IF;
1705
1706 END IF; -- IF x_dblink IS NULL THEN
1707
1708 END IF;
1709 END Put_Period_Data;
1710
1711 -- dsting unused after s/d changes
1712 PROCEDURE Put_Pegging_data (p_session_id IN NUMBER,
1713 p_dblink IN VARCHAR2)
1714
1715 IS
1716
1717 SQL_STMT VARCHAR2(10000);
1718 l_user_id number;
1719 BEGIN
1720 -- for pegging part: we are not storing pegging records in record of
1721 -- tables. The pegging records will always be there in the server,
1722 -- if the source instance is different than the server, we need to
1723 -- put it back into source.
1724
1725 l_user_id := FND_GLOBAL.USER_ID;
1726 IF PG_DEBUG in ('Y', 'C') THEN
1727 msc_sch_wb.atp_debug('Put_Pegging_data: ' || 'p_dblink = ' ||p_dblink);
1728 END IF;
1729
1730 IF p_dblink is not null THEN
1731
1732 IF PG_DEBUG in ('Y', 'C') THEN
1733 msc_sch_wb.atp_debug('Put_Pegging_data: ' || 'inserting pegging into source side');
1734 END IF;
1735 -- Bug 1761545, added component_identifier in pegging for MATO
1736
1737 -- dsting: added creation_date, created_by,
1738 -- last_update_date, last_updated_by, last_update_login
1739
1740 sql_stmt := '
1741 INSERT INTO mrp_atp_details_temp@'||p_dblink|| '
1742 (session_id,
1743 order_line_id,
1744 pegging_id,
1745 parent_pegging_id,
1746 atp_level,
1747 record_type,
1748 organization_id,
1749 organization_code,
1750 identifier1,
1751 identifier2,
1752 identifier3,
1753 inventory_item_id,
1754 inventory_item_name,
1755 resource_id,
1756 resource_code,
1757 department_id,
1758 department_code,
1759 supplier_id,
1760 supplier_name,
1761 supplier_site_id,
1762 supplier_site_name,
1763 scenario_id,
1764 source_type,
1765 supply_demand_source_type,
1766 supply_demand_quantity,
1767 supply_demand_type,
1768 supply_demand_date,
1769 end_pegging_id,
1770 constraint_flag,
1771 number1,
1772 char1,
1773 component_identifier,
1774 allocated_quantity,
1775 batchable_flag,
1776 -- 2152184
1777 request_item_id,
1778 ptf_date
1779 -- dsting
1780 , creation_date
1781 , created_by
1782 , last_update_date
1783 , last_updated_by
1784 , last_update_login )
1785 SELECT
1786 session_id,
1787 order_line_id,
1788 pegging_id,
1789 parent_pegging_id,
1790 atp_level,
1791 record_type,
1792 organization_id,
1793 organization_code,
1794 identifier1,
1795 identifier2,
1796 identifier3,
1797 inventory_item_id,
1798 inventory_item_name,
1799 resource_id,
1800 resource_code,
1801 department_id,
1802 department_code,
1803 supplier_id,
1804 supplier_name,
1805 supplier_site_id,
1806 supplier_site_name,
1807 scenario_id,
1808 source_type,
1809 supply_demand_source_type,
1810 supply_demand_quantity,
1811 supply_demand_type,
1812 supply_demand_date,
1813 end_pegging_id,
1814 constraint_flag,
1815 number1,
1816 char1,
1817 component_identifier,
1818 allocated_quantity,
1819 batchable_flag,
1820 -- 2152184
1821 request_item_id,
1822 ptf_date
1823 , sysdate
1824 , :created_by
1825 , sysdate
1826 , :created_by
1827 , :created_by
1828 FROM mrp_atp_details_temp
1829 WHERE record_type = 3
1830 AND session_id = :x_session_id ';
1831
1832 EXECUTE IMMEDIATE sql_stmt USING
1833 -- dsting
1834 l_user_id,
1835 l_user_id,
1836 l_user_id,
1837 p_session_id;
1838
1839 IF PG_DEBUG in ('Y', 'C') THEN
1840 msc_sch_wb.atp_debug('Put_Pegging_data: ' || 'inserted pegging into source side');
1841 END IF;
1842
1843
1844 -- after the insert is done, delete the pegging records
1845 -- at the server since they are not needed anymore
1846 begin
1847 DELETE from mrp_atp_details_temp
1848 WHERE record_type = 3
1849 AND session_id = p_session_id;
1850 exception
1851 when others then null;
1852 end;
1853
1854 END IF;
1855 END Put_Pegging_data;
1856
1857 Procedure Put_Scheduling_data(p_atp_rec IN MRP_ATP_PUB.atp_rec_typ,
1858 p_mode IN NUMBER,
1859 p_dblink IN VARCHAR2,
1860 p_session_id IN NUMBER
1861 )
1862 IS
1863
1864 j NUMBER;
1865 l_dynstring VARCHAR2(128) := NULL;
1866 sql_stmt VARCHAR2(10000);
1867 l_atp_rec MRP_ATP_PUB.atp_rec_typ;
1868 l_status_flag NUMBER := 99; -- bug 2974324. Initialize l_status_flag to 99 here.
1869 l_sequence_number MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(); -- for bug 2974324.
1870 found NUMBER;
1871
1872 mast_rec mrp_atp_utils.mrp_atp_schedule_temp_typ;
1873 mast_rec_insert mrp_atp_utils.mrp_atp_schedule_temp_typ;
1874 TYPE mastcurtyp IS REF CURSOR;
1875 mast_cursor mastcurtyp;
1876 l_ret_code VARCHAR2(1);
1877 l_ret_status VARCHAR2(100);
1878 cur_handler NUMBER;
1879 rows_processed NUMBER;
1880 l_plan_name varchar2(10); -- for bug 2392456
1881 l_user_id number;
1882 l_count number; -- for bug 2974324
1883
1884 BEGIN
1885
1886 l_user_id := FND_GLOBAL.USER_ID;
1887 IF p_dblink IS NOT NULL THEN
1888 l_dynstring := '@' || p_dblink;
1889 END IF;
1890
1891 l_count := p_atp_rec.Inventory_Item_Id.COUNT; -- Bug 2974324
1892
1893 ---s_cto_rearch
1894 IF l_count > 0 THEN
1895 IF p_mode = RESULTS_MODE THEN
1896 IF PG_DEBUG in ('Y', 'C') THEN
1897 msc_sch_wb.atp_debug('Put_Scheduling_data in results mode: ' || ' output records '|| l_count );
1898 END IF;
1899 MSC_ATP_UTILS.Put_sch_data_result_mode(p_atp_rec, p_dblink, p_session_id);
1900 ELSE
1901 MSC_ATP_UTILS.Put_sch_Data_Request_mode(p_atp_rec, p_session_id);
1902 END IF;
1903 END IF;
1904 ---e_cto_rearch
1905
1906
1907 END Put_Scheduling_data;
1908
1909 PROCEDURE Retrieve_Period_and_SD_Data(
1910 p_session_id IN NUMBER,
1911 x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
1912 x_atp_supply_demand OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ
1913 ) IS
1914
1915 BEGIN
1916
1917 IF PG_DEBUG in ('Y', 'C') THEN
1918 msc_sch_wb.atp_debug('PROCEDURE Retrieve_Period_And_SD_Data');
1919 END IF;
1920
1921 -----------------
1922 -- Period Data --
1923 -----------------
1924 SELECT
1925 scenario_id,
1926 order_line_id,
1927 atp_LEVEL,
1928 inventory_item_id,
1929 request_item_id,
1930 organization_id,
1931 department_id,
1932 resource_id,
1933 supplier_id,
1934 supplier_site_id,
1935 from_organization_id,
1936 from_location_id,
1937 to_organization_id,
1938 to_location_id,
1939 ship_method,
1940 uom_code,
1941 total_supply_quantity,
1942 total_demand_quantity,
1943 total_bucketed_demand_quantity, -- time_phased_atp
1944 period_start_date,
1945 period_end_date,
1946 period_quantity,
1947 cumulative_quantity,
1948 identifier1,
1949 identifier2,
1950 pegging_id,
1951 end_pegging_id
1952 BULK COLLECT INTO
1953 x_atp_period.scenario_id,
1954 x_atp_period.identifier,
1955 x_atp_period.level,
1956 x_atp_period.inventory_item_id,
1957 x_atp_period.request_item_id,
1958 x_atp_period.organization_id,
1959 x_atp_period.department_id,
1960 x_atp_period.resource_id,
1961 x_atp_period.supplier_id,
1962 x_atp_period.supplier_site_id,
1963 x_atp_period.from_organization_id,
1964 x_atp_period.from_location_id,
1965 x_atp_period.to_organization_id,
1966 x_atp_period.to_location_id,
1967 x_atp_period.ship_method,
1968 x_atp_period.uom,
1969 x_atp_period.total_supply_quantity,
1970 x_atp_period.total_demand_quantity,
1971 x_atp_period.total_bucketed_demand_quantity, -- time_phased_atp
1972 x_atp_period.period_start_date,
1973 x_atp_period.period_end_date,
1974 x_atp_period.period_quantity,
1975 x_atp_period.cumulative_quantity,
1976 x_atp_period.identifier1,
1977 x_atp_period.identifier2,
1978 x_atp_period.pegging_id,
1979 x_atp_period.end_pegging_id
1980 FROM mrp_atp_details_temp
1981 WHERE session_id = p_session_id
1982 and record_type = 1
1983 and pegging_id in (select pegging_id from mrp_atp_details_temp
1984 where session_id = p_session_id
1985 and record_type = 3);
1986
1987 IF PG_DEBUG in ('Y', 'C') THEN
1988 msc_sch_wb.atp_debug('Retrieve_Period_and_SD_Data: ' || ' Rows period data: ' || SQL%ROWCOUNT);
1989 END IF;
1990
1991 -------------
1992 -- SD Data --
1993 -------------
1994 SELECT ORDER_LINE_ID
1995 ,PEGGING_ID
1996 ,ATP_LEVEL
1997 ,REQUEST_ITEM_ID
1998 ,INVENTORY_ITEM_ID
1999 ,ORGANIZATION_ID
2000 ,DEPARTMENT_ID
2001 ,RESOURCE_ID
2002 ,SUPPLIER_ID
2003 ,SUPPLIER_SITE_ID
2004 ,FROM_ORGANIZATION_ID
2005 ,FROM_LOCATION_ID
2006 ,TO_ORGANIZATION_ID
2007 ,TO_LOCATION_ID
2008 ,SHIP_METHOD
2009 ,UOM_CODE
2010 ,IDENTIFIER1
2011 ,IDENTIFIER2
2012 ,IDENTIFIER3
2013 ,IDENTIFIER4
2014 ,SUPPLY_DEMAND_TYPE
2015 ,SUPPLY_DEMAND_DATE
2016 ,SUPPLY_DEMAND_QUANTITY
2017 ,SUPPLY_DEMAND_SOURCE_TYPE
2018 ,SCENARIO_ID
2019 ,DISPOSITION_TYPE
2020 ,DISPOSITION_NAME
2021 ,SUPPLY_DEMAND_SOURCE_TYPE_NAME
2022 ,END_PEGGING_ID
2023 bulk collect into
2024
2025 x_atp_supply_demand.identifier,
2026 x_atp_supply_demand.pegging_id,
2027 x_atp_supply_demand.Level,
2028 x_atp_supply_demand.Request_Item_Id,
2029 x_atp_supply_demand.Inventory_Item_Id,
2030 x_atp_supply_demand.Organization_Id,
2031 x_atp_supply_demand.Department_Id,
2032 x_atp_supply_demand.Resource_Id,
2033 x_atp_supply_demand.Supplier_Id,
2034 x_atp_supply_demand.Supplier_Site_Id,
2035 x_atp_supply_demand.From_Organization_Id,
2036 x_atp_supply_demand.From_Location_Id,
2037 x_atp_supply_demand.To_Organization_Id,
2038 x_atp_supply_demand.To_Location_Id,
2039 x_atp_supply_demand.Ship_Method,
2040 x_atp_supply_demand.Uom,
2041 x_atp_supply_demand.Identifier1,
2042 x_atp_supply_demand.Identifier2,
2043 x_atp_supply_demand.Identifier3,
2044 x_atp_supply_demand.Identifier4,
2045 x_atp_supply_demand.Supply_Demand_Type,
2046 x_atp_supply_demand.Supply_Demand_Date,
2047 x_atp_supply_demand.supply_demand_quantity,
2048 x_atp_supply_demand.Supply_Demand_Source_Type,
2049 x_atp_supply_demand.scenario_id,
2050 x_atp_supply_demand.disposition_type,
2051 x_atp_supply_demand.disposition_name,
2052 x_atp_supply_demand.Supply_Demand_Source_Type_name,
2053 x_atp_supply_demand.end_pegging_id
2054 FROM mrp_atp_details_temp
2055 WHERE session_id = p_session_id
2056 and record_type = 2
2057 and pegging_id in (select pegging_id from mrp_atp_details_temp
2058 where session_id = p_session_id
2059 and record_type = 3);
2060
2061
2062 IF PG_DEBUG in ('Y', 'C') THEN
2063 msc_sch_wb.atp_debug('Retrieve_Period_and_SD_Data: ' || ' Rows SD data: ' || SQL%ROWCOUNT);
2064 END IF;
2065
2066 END Retrieve_Period_and_SD_Data;
2067
2068 /*
2069 * dsting 10/1/02
2070 *
2071 * IF dblink is set then
2072 * transfer data from dest mrp_atp_details_temp to src
2073 * ELSE it's a nondistributed setup
2074 * transfer data from mrp_atp_Details_temp to pl/sql tables
2075 *
2076 */
2077 PROCEDURE Transfer_mrp_atp_details_temp(
2078 p_dblink IN VARCHAR2,
2079 p_session_id IN NUMBER
2080 ) IS
2081 sql_stmt VARCHAR2(10000);
2082 l_std_cols CONSTANT VARCHAR2(3000) := '
2083 SESSION_ID
2084 ,ORDER_LINE_ID
2085 ,PEGGING_ID
2086 ,PARENT_PEGGING_ID
2087 ,ATP_LEVEL
2088 ,REQUEST_ITEM_ID
2089 ,INVENTORY_ITEM_ID
2090 ,INVENTORY_ITEM_NAME
2091 ,ORGANIZATION_ID
2092 ,ORGANIZATION_CODE
2093 ,DEPARTMENT_ID
2094 ,DEPARTMENT_CODE
2095 ,RESOURCE_ID
2096 ,RESOURCE_CODE
2097 ,SUPPLIER_ID
2098 ,SUPPLIER_NAME
2099 ,SUPPLIER_SITE_ID
2100 ,SUPPLIER_SITE_NAME
2101 ,FROM_ORGANIZATION_ID
2102 ,FROM_ORGANIZATION_CODE
2103 ,FROM_LOCATION_ID
2104 ,FROM_LOCATION_CODE
2105 ,TO_ORGANIZATION_ID
2106 ,TO_ORGANIZATION_CODE
2107 ,TO_LOCATION_ID
2108 ,TO_LOCATION_CODE
2109 ,SHIP_METHOD
2110 ,UOM_CODE
2111 ,IDENTIFIER1
2112 ,IDENTIFIER2
2113 ,IDENTIFIER3
2114 ,IDENTIFIER4
2115 ,SUPPLY_DEMAND_TYPE
2116 ,SUPPLY_DEMAND_DATE
2117 ,SUPPLY_DEMAND_QUANTITY
2118 ,SUPPLY_DEMAND_SOURCE_TYPE
2119 ,ALLOCATED_QUANTITY
2120 ,SOURCE_TYPE
2121 ,RECORD_TYPE
2122 ,TOTAL_SUPPLY_QUANTITY
2123 ,TOTAL_DEMAND_QUANTITY
2124 ,PERIOD_START_DATE
2125 ,PERIOD_QUANTITY
2126 ,CUMULATIVE_QUANTITY
2127 ,WEIGHT_CAPACITY
2128 ,VOLUME_CAPACITY
2129 ,WEIGHT_UOM
2130 ,VOLUME_UOM
2131 ,PERIOD_END_DATE
2132 ,SCENARIO_ID
2133 ,DISPOSITION_TYPE
2134 ,DISPOSITION_NAME
2135 ,REQUEST_ITEM_NAME
2136 ,SUPPLY_DEMAND_SOURCE_TYPE_NAME
2137 ,END_PEGGING_ID
2138 ,CONSTRAINT_FLAG
2139 ,NUMBER1
2140 ,CHAR1
2141 ,COMPONENT_IDENTIFIER
2142 ,BATCHABLE_FLAG
2143 ,DEST_INV_ITEM_ID
2144 ,SUPPLIER_ATP_DATE
2145 ,SUMMARY_FLAG
2146 ,PTF_DATE ';
2147
2148 l_apps_v3_cols CONSTANT VARCHAR2(3000) := '
2149 ,CREATION_DATE
2150 ,CREATED_BY
2151 ,LAST_UPDATE_DATE
2152 ,LAST_UPDATED_BY
2153 ,LAST_UPDATE_LOGIN
2154 ,PEGGING_TYPE
2155 ,FIXED_LEAD_TIME
2156 ,VARIABLE_LEAD_TIME
2157 ,PREPROCESSING_LEAD_TIME
2158 ,PROCESSING_LEAD_TIME
2159 ,POSTPROCESSING_LEAD_TIME
2160 ,INTRANSIT_LEAD_TIME
2161 ,ATP_RULE_ID
2162 ,ALLOCATION_RULE
2163 ,INFINITE_TIME_FENCE
2164 ,SUBSTITUTION_WINDOW
2165 ,REQUIRED_QUANTITY
2166 ,ROUNDING_CONTROL
2167 ,ATP_FLAG
2168 ,ATP_COMPONENT_FLAG
2169 ,REQUIRED_DATE
2170 ,OPERATION_SEQUENCE_ID
2171 ,SOURCING_RULE_NAME
2172 ,OFFSET
2173 ,EFFICIENCY
2174 ,UTILIZATION
2175 ,OWNING_DEPARTMENT
2176 ,REVERSE_CUM_YIELD
2177 ,BASIS_TYPE
2178 ,USAGE
2179 ,CONSTRAINT_TYPE
2180 ,CONSTRAINT_DATE
2181 ,ATP_RULE_NAME
2182 ,PLAN_NAME
2183 ,constrained_path
2184 ,TOTAL_BUCKETED_DEMAND_QUANTITY -- time_phased_atp
2185 ,aggregate_time_fence_date, -- Bug 3279014
2186 UNALLOCATED_QUANTITY, -- Bug 3282426
2187 PF_DISPLAY_FLAG ,
2188 ORIGINAL_DEMAND_DATE,
2189 ORIGINAL_DEMAND_QUANTITY,
2190 ORIGINAL_ITEM_ID,
2191 ORIGINAL_SUPPLY_DEMAND_TYPE,
2192 BASE_MODEL_ID,
2193 BASE_MODEL_NAME,
2194 MODEL_SD_FLAG,
2195 ERROR_CODE,
2196 NONATP_FLAG,
2197 ORIG_CUSTOMER_SITE_NAME, --3263368
2198 ORIG_CUSTOMER_NAME, --3263368
2199 ORIG_DEMAND_CLASS, --3263368
2200 ORIG_REQUEST_DATE, --3263368
2201 COMPONENT_YIELD_FACTOR, --4570421
2202 SCALING_TYPE, --4570421
2203 ROUNDING_DIRECTION, --4570421
2204 SCALE_ROUNDING_VARIANCE, --4570421
2205 SCALE_MULTIPLE, --4570421
2206 ORGANIZATION_TYPE --4775920
2207 ';
2208
2209 BEGIN
2210
2211 IF PG_DEBUG in ('Y', 'C') THEN
2212 msc_sch_wb.atp_debug('Transfer_mrp_atp_details_temp: ' || 'PROCEDURE Transfer_SD_And_Period_Data');
2213 END IF;
2214
2215 IF p_dblink IS NOT NULL THEN
2216 -- transfer period (record_type 1) and s/d (2) and pegging (3) data
2217 -- that appear in the pegging tree
2218 IF PG_DEBUG in ('Y', 'C') THEN
2219 msc_sch_wb.atp_debug('Transfer_mrp_atp_details_temp: ' || 'apps_ver: ' || MSC_ATP_PVT.G_APPS_VER);
2220 END IF;
2221
2222 sql_stmt := 'Insert into mrp_atp_details_temp@' || p_dblink || ' (';
2223
2224 IF MSC_ATP_PVT.G_APPS_VER >= 3 THEN
2225 sql_stmt := sql_stmt || l_std_cols || l_apps_v3_cols ||
2226 ' ) select ' || l_std_cols || l_apps_v3_cols;
2227 ELSE
2228 sql_stmt := sql_stmt || l_std_cols ||
2229 ' ) select ' || l_std_cols;
2230 END IF;
2231
2232 sql_stmt := sql_stmt ||
2233 'from mrp_atp_details_temp
2234 where session_id = :p_session_id
2235 and record_type in (1, 2, 3)
2236 and pegging_id in (Select pegging_id from mrp_atp_details_temp
2237 where session_id = :p_session_id
2238 and record_type = 3)';
2239 IF PG_DEBUG in ('Y', 'C') THEN
2240 msc_sch_wb.atp_debug('Transfer_mrp_atp_details_temp: ' || 'dsting: '||sql_stmt);
2241 END IF;
2242 execute immediate sql_stmt using p_session_id , p_session_id;
2243
2244 IF PG_DEBUG in ('Y', 'C') THEN
2245 msc_sch_wb.atp_debug('Transfer_mrp_atp_details_temp: ' || 'dsting: '|| SQL%ROWCOUNT);
2246 END IF;
2247 END IF;
2248
2249 END Transfer_mrp_atp_details_temp;
2250
2251 /*
2252 * dsting 10/16/02
2253 *
2254 * Copy the supply/demand records with pegging_id = p_old_pegging_id
2255 * and give them the new pegging_id p_pegging_id
2256 *
2257 * Right now this is only used for the fix for bug 2621270
2258 */
2259 PROCEDURE Copy_MRP_SD_Recs(
2260 p_old_pegging_id NUMBER,
2261 p_pegging_id NUMBER
2262 ) IS
2263 sql_stmt VARCHAR2(3000);
2264 who_cols VARCHAR2(100);
2265 t1 NUMBER;
2266 BEGIN
2267 IF PG_DEBUG in ('Y', 'C') THEN
2268 msc_sch_wb.atp_debug('PROCEDURE Copy_MRP_SD_Recs');
2269 msc_sch_wb.atp_debug('Copy_MRP_SD_Recs: ' || ' p_old_pegging_id: ' || p_old_pegging_id);
2270 msc_sch_wb.atp_debug('Copy_MRP_SD_Recs: ' || ' p_pegging_id: ' || p_pegging_id);
2271 END IF;
2272
2273 INSERT INTO mrp_atp_details_temp (
2274 session_id,
2275 scenario_id,
2276 order_line_id,
2277 ATP_Level,
2278 Inventory_Item_Id,
2279 Request_Item_Id,
2280 Organization_Id,
2281 Department_Id,
2282 Resource_Id,
2283 Supplier_Id,
2284 Supplier_Site_Id,
2285 From_Organization_Id,
2286 From_Location_Id,
2287 To_Organization_Id,
2288 To_Location_Id,
2289 Ship_Method,
2290 Uom_code,
2291 Identifier1,
2292 Identifier2,
2293 Identifier3,
2294 Identifier4,
2295 Supply_Demand_Type,
2296 Supply_Demand_Source_Type,
2297 Supply_Demand_Source_type_name,
2298 Supply_Demand_Date,
2299 supply_demand_quantity,
2300 disposition_type,
2301 disposition_name,
2302 record_type,
2303 pegging_id,
2304 end_pegging_id,
2305 creation_date,
2306 created_by,
2307 last_update_date,
2308 last_updated_by,
2309 last_update_login
2310 )
2311 SELECT
2312 MSC_ATP_PVT.G_SESSION_ID,
2313 scenario_id,
2314 order_line_id,
2315 ATP_Level,
2316 Inventory_Item_Id,
2317 Request_Item_Id,
2318 Organization_Id,
2319 Department_Id,
2320 Resource_Id,
2321 Supplier_Id,
2322 Supplier_Site_Id,
2323 From_Organization_Id,
2324 From_Location_Id,
2325 To_Organization_Id,
2326 To_Location_Id,
2327 Ship_Method,
2328 Uom_code,
2329 Identifier1,
2330 Identifier2,
2331 Identifier3,
2332 Identifier4,
2333 Supply_Demand_Type,
2334 Supply_Demand_Source_Type,
2335 Supply_Demand_Source_type_name,
2336 Supply_Demand_Date,
2337 supply_demand_quantity,
2338 disposition_type,
2339 disposition_name,
2340 2,
2341 p_pegging_id,
2342 end_pegging_id,
2343 creation_date,
2344 created_by,
2345 last_update_date,
2346 last_updated_by,
2347 last_update_login
2348 FROM mrp_atp_details_temp
2349 where pegging_id = p_old_pegging_id
2350 and record_type = 2;
2351
2352 IF PG_DEBUG in ('Y', 'C') THEN
2353 msc_sch_wb.atp_debug('Copy_MRP_SD_Recs: ' || ' Num rows copied: ' || SQL%ROWCOUNT);
2354 END IF;
2355
2356 END Copy_MRP_SD_Recs;
2357
2358 Procedure Process_Supply_Demand_details( p_dblink IN varchar2,
2359 p_session_id IN number,
2360 x_atp_supply_demand OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ)
2361 IS
2362 j PLS_INTEGER := 1;
2363 sql_stmt VARCHAR2(10000);
2364 sched_cv mrp_atp_utils.SchedCurTyp;
2365 details_rec mrp_atp_utils.Details_Temp;
2366 l_dynstring VARCHAR2(128) := NULL;
2367
2368 BEGIN
2369
2370 IF p_dblink IS NOT NULL THEN
2371 l_dynstring := '@'||p_dblink;
2372 END IF;
2373
2374 IF l_dynstring is not null then
2375 ---distributed database:
2376 IF PG_DEBUG in ('Y', 'C') THEN
2377 msc_sch_wb.atp_debug('Process_Supply_Demand_details: ' || 'Distributed environment. Put from dest to source table');
2378 END IF;
2379 sql_stmt := 'Insert into mrp_atp_details_temp' || l_dynstring ||
2380 '(select * from mrp_atp_details_temp
2381 where session_id = :p_session_id
2382 and record_type = 2
2383 and pegging_id in (Select pegging_id from mrp_atp_details_temp
2384 where session_id = :p_session_id
2385 and record_type = 3))';
2386 execute immediate sql_stmt using p_session_id , p_session_id;
2387
2388 ELSE --- IF p_dblink is not null then
2389 --- non-distributed environment
2390 IF PG_DEBUG in ('Y', 'C') THEN
2391 msc_sch_wb.atp_debug('Process_Supply_Demand_details: ' || 'Non Distributed env. Put SD details from temp table to pl/sql tables');
2392 END IF;
2393 j := 1;
2394 SELECT ORDER_LINE_ID
2395 ,PEGGING_ID
2396 ,ATP_LEVEL
2397 ,REQUEST_ITEM_ID
2398 ,INVENTORY_ITEM_ID
2399 ,ORGANIZATION_ID
2400 ,DEPARTMENT_ID
2401 ,RESOURCE_ID
2402 ,SUPPLIER_ID
2403 ,SUPPLIER_SITE_ID
2404 ,FROM_ORGANIZATION_ID
2405 ,FROM_LOCATION_ID
2406 ,TO_ORGANIZATION_ID
2407 ,TO_LOCATION_ID
2408 ,SHIP_METHOD
2409 ,UOM_CODE
2410 ,IDENTIFIER1
2411 ,IDENTIFIER2
2412 ,IDENTIFIER3
2413 ,IDENTIFIER4
2414 ,SUPPLY_DEMAND_TYPE
2415 ,SUPPLY_DEMAND_DATE
2416 ,SUPPLY_DEMAND_QUANTITY
2417 ,SUPPLY_DEMAND_SOURCE_TYPE
2418 ,SCENARIO_ID
2419 ,DISPOSITION_TYPE
2420 ,DISPOSITION_NAME
2421 ,SUPPLY_DEMAND_SOURCE_TYPE_NAME
2422 ,END_PEGGING_ID
2423 bulk collect into
2424
2425 x_atp_supply_demand.identifier,
2426 x_atp_supply_demand.pegging_id,
2427 x_atp_supply_demand.Level,
2428 x_atp_supply_demand.Request_Item_Id,
2429 x_atp_supply_demand.Inventory_Item_Id,
2430 x_atp_supply_demand.Organization_Id,
2431 x_atp_supply_demand.Department_Id,
2432 x_atp_supply_demand.Resource_Id,
2433 x_atp_supply_demand.Supplier_Id,
2434 x_atp_supply_demand.Supplier_Site_Id,
2435 x_atp_supply_demand.From_Organization_Id,
2436 x_atp_supply_demand.From_Location_Id,
2437 x_atp_supply_demand.To_Organization_Id,
2438 x_atp_supply_demand.To_Location_Id,
2439 x_atp_supply_demand.Ship_Method,
2440 x_atp_supply_demand.Uom,
2441 x_atp_supply_demand.Identifier1,
2442 x_atp_supply_demand.Identifier2,
2443 x_atp_supply_demand.Identifier3,
2444 x_atp_supply_demand.Identifier4,
2445 x_atp_supply_demand.Supply_Demand_Type,
2446 x_atp_supply_demand.Supply_Demand_Date,
2447 x_atp_supply_demand.supply_demand_quantity,
2448 x_atp_supply_demand.Supply_Demand_Source_Type,
2449 x_atp_supply_demand.scenario_id,
2450 x_atp_supply_demand.disposition_type,
2451 x_atp_supply_demand.disposition_name,
2452 x_atp_supply_demand.Supply_Demand_Source_Type_name,
2453 x_atp_supply_demand.end_pegging_id
2454 FROM mrp_atp_details_temp
2455 WHERE session_id = p_session_id
2456 and record_type = 2
2457 and pegging_id in (select pegging_id from mrp_atp_details_temp
2458 where session_id = p_session_id
2459 and record_type = 3);
2460
2461
2462 END IF;
2463
2464 END Process_Supply_Demand_Details;
2465
2466 ------------------------------------------------------------------------
2467
2468 /*
2469 * dsting 10/1/02 supply/demand performance enh
2470 *
2471 * DEPRECATED
2472 *
2473 * Put_SD_Data should not be doing anything now
2474 * p_atp_supply_demand should have 0 records in it
2475 * since sd data is never stored in pl/sql tables during processing
2476 *
2477 */
2478 PROCEDURE Put_SD_Data (
2479 p_atp_supply_demand IN MRP_ATP_PUB.ATP_Supply_Demand_Typ,
2480 p_dblink IN VARCHAR2,
2481 p_session_id IN NUMBER )
2482 IS
2483
2484 sql_stmt VARCHAR2(10000);
2485 rows_processed NUMBER;
2486 cur_handler NUMBER;
2487 l_user_id number;
2488 BEGIN
2489
2490 l_user_id := FND_GLOBAL.USER_ID;
2491
2492 IF p_atp_supply_demand.level.COUNT > 0 THEN
2493 IF PG_DEBUG in ('Y', 'C') THEN
2494 msc_sch_wb.atp_debug('Put_SD_Data: ' || ' SD records '||p_atp_supply_demand.level.COUNT);
2495 msc_sch_wb.atp_debug('XXX should not have sd records in Put_SD_Data');
2496 END IF;
2497
2498 IF p_dblink IS NULL THEN
2499
2500 FORALL j IN 1..p_atp_supply_demand.level.COUNT
2501 INSERT INTO mrp_atp_details_temp
2502 (
2503 session_id,
2504 scenario_id,
2505 order_line_id,
2506 ATP_Level,
2507 Inventory_Item_Id,
2508 Request_Item_Id,
2509 Organization_Id,
2510 Department_Id,
2511 Resource_Id,
2512 Supplier_Id,
2513 Supplier_Site_Id,
2514 From_Organization_Id,
2515 From_Location_Id,
2516 To_Organization_Id,
2517 To_Location_Id,
2518 Ship_Method,
2519 Uom_code,
2520 Identifier1,
2521 Identifier2,
2522 Identifier3,
2523 Identifier4,
2524 Supply_Demand_Type,
2525 Supply_Demand_Source_Type,
2526 Supply_Demand_Source_type_name,
2527 Supply_Demand_Date,
2528 supply_demand_quantity,
2529 disposition_type,
2530 disposition_name,
2531 record_type,
2532 pegging_id,
2533 end_pegging_id
2534 -- dsting
2535 , creation_date
2536 , created_by
2537 , last_update_date
2538 , last_updated_by
2539 , last_update_login
2540 )
2541 VALUES
2542 (
2543 p_session_id,
2544 p_atp_supply_demand.scenario_id(j),
2545 p_atp_supply_demand.identifier(j),
2546 p_atp_supply_demand.LEVEL(j),
2547 p_atp_supply_demand.Inventory_Item_Id(j),
2548 p_atp_supply_demand.Request_Item_Id(j),
2549 p_atp_supply_demand.Organization_Id(j),
2550 p_atp_supply_demand.Department_Id(j),
2551 p_atp_supply_demand.Resource_Id(j),
2552 p_atp_supply_demand.Supplier_Id(j),
2553 p_atp_supply_demand.Supplier_Site_Id(j),
2554 p_atp_supply_demand.From_Organization_Id(j),
2555 p_atp_supply_demand.From_Location_Id(j),
2556 p_atp_supply_demand.To_Organization_Id(j),
2557 p_atp_supply_demand.To_Location_Id(j),
2558 p_atp_supply_demand.Ship_Method(j),
2559 p_atp_supply_demand.Uom(j),
2560 p_atp_supply_demand.Identifier1(j),
2561 p_atp_supply_demand.Identifier2(j),
2562 p_atp_supply_demand.Identifier3(j),
2563 p_atp_supply_demand.Identifier4(j),
2564 p_atp_supply_demand.Supply_Demand_Type(j),
2565 p_atp_supply_demand.Supply_Demand_Source_Type(j),
2566 p_atp_supply_demand.Supply_Demand_Source_Type_name(j),
2567 p_atp_supply_demand.Supply_Demand_Date(j),
2568 p_atp_supply_demand.supply_demand_quantity(j),
2569 p_atp_supply_demand.disposition_type(j),
2570 p_atp_supply_demand.disposition_name(j),
2571 2,
2572 p_atp_supply_demand.pegging_id(j),
2573 p_atp_supply_demand.end_pegging_id(j)
2574 -- dsting
2575 , sysdate -- creation_date
2576 , l_user_id -- created_by
2577 , sysdate -- last_update_date
2578 , l_user_id -- updated_by
2579 , l_user_id -- login_by
2580 );
2581
2582
2583 ELSE -- OF IF x_dblink IS NULL THEN
2584
2585 -- dsting: added stuff for creation_date, created_by,
2586 -- last_update_date, last_updated_by, last_update_login
2587
2588 sql_stmt := '
2589 INSERT INTO mrp_atp_details_temp@'||p_dblink||'
2590 (
2591 session_id,
2592 scenario_id,
2593 order_line_id,
2594 ATP_Level,
2595 Inventory_Item_Id,
2596 Request_Item_Id,
2597 Organization_Id,
2598 Department_Id,
2599 Resource_Id,
2600 Supplier_Id,
2601 Supplier_Site_Id,
2602 From_Organization_Id,
2603 From_Location_Id,
2604 To_Organization_Id,
2605 To_Location_Id,
2606 Ship_Method,
2607 Uom_code,
2608 Identifier1,
2609 Identifier2,
2610 Identifier3,
2611 Identifier4,
2612 Supply_Demand_Type,
2613 Supply_Demand_Source_Type,
2614 Supply_Demand_Source_type_name,
2615 Supply_Demand_Date,
2616 supply_demand_quantity,
2617 disposition_type,
2618 disposition_name,
2619 record_type,
2620 pegging_id,
2621 end_pegging_id
2622 , creation_date
2623 , created_by
2624 , last_update_date
2625 , last_updated_by
2626 , last_update_login
2627 )
2628 VALUES
2629 (
2630 :x_session_id,
2631 :scenario_id,
2632 :identifier,
2633 :atp_level,
2634 :Inventory_Item_Id,
2635 :Request_Item_Id,
2636 :Organization_Id,
2637 :Department_Id,
2638 :Resource_Id,
2639 :Supplier_Id,
2640 :Supplier_Site_Id,
2641 :From_Organization_Id,
2642 :From_Location_Id,
2643 :To_Organization_Id,
2644 :To_Location_Id,
2645 :Ship_Method,
2646 :Uom_Code,
2647 :Identifier1,
2648 :Identifier2,
2649 :Identifier3,
2650 :Identifier4,
2651 :Supply_Demand_Type,
2652 :Supply_Demand_Source_Type,
2653 :name,
2654 :Supply_Demand_Date,
2655 :supply_demand_quantity,
2656 :disposition_type,
2657 :disposition_name,
2658 2,
2659 :pegging_id,
2660 :end_pegging_id
2661 , sysdate
2662 , :created_by
2663 , sysdate
2664 , :last_updated_by
2665 , :last_update_login
2666 )';
2667
2668 IF PG_DEBUG in ('Y', 'C') THEN
2669 msc_sch_wb.atp_debug('Put_SD_Data: ' || 'enter put_into_temp_table in between values and execute ');
2670 END IF;
2671
2672 -- Obtain cursor handler for sql_stmt
2673 cur_handler := DBMS_SQL.OPEN_CURSOR;
2674
2675 -- Parse cursor handler for sql_stmt
2676 DBMS_SQL.PARSE(cur_handler, sql_stmt, DBMS_SQL.NATIVE);
2677
2678 FOR j IN 1..p_atp_supply_demand.level.COUNT LOOP
2679 -- Bind variables in the loop for insert.
2680
2681 DBMS_SQL.BIND_VARIABLE(cur_handler, ':x_session_id', p_session_id);
2682 DBMS_SQL.BIND_VARIABLE(cur_handler, ':scenario_id', p_atp_supply_demand.scenario_id(j));
2683 DBMS_SQL.BIND_VARIABLE(cur_handler, ':identifier', p_atp_supply_demand.identifier(j));
2684 DBMS_SQL.BIND_VARIABLE(cur_handler, ':atp_level', p_atp_supply_demand.Level(j));
2685 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Inventory_Item_Id', p_atp_supply_demand.Inventory_Item_Id(j));
2686 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Request_Item_Id', p_atp_supply_demand.Request_Item_Id(j));
2687 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Organization_Id', p_atp_supply_demand.Organization_Id(j));
2688 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Department_Id', p_atp_supply_demand.Department_Id(j));
2689 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Resource_Id', p_atp_supply_demand.Resource_Id(j));
2690 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Supplier_Id', p_atp_supply_demand.Supplier_Id(j));
2691 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Supplier_Site_Id', p_atp_supply_demand.Supplier_Site_Id(j));
2692 DBMS_SQL.BIND_VARIABLE(cur_handler, ':From_Organization_Id', p_atp_supply_demand.From_Organization_Id(j));
2693 DBMS_SQL.BIND_VARIABLE(cur_handler, ':From_Location_Id', p_atp_supply_demand.From_Location_Id(j));
2694 DBMS_SQL.BIND_VARIABLE(cur_handler, ':To_Organization_Id', p_atp_supply_demand.To_Organization_Id(j));
2695 DBMS_SQL.BIND_VARIABLE(cur_handler, ':To_Location_Id', p_atp_supply_demand.To_Location_Id(j));
2696 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Ship_Method', p_atp_supply_demand.Ship_Method(j));
2697 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Uom_Code', p_atp_supply_demand.Uom(j));
2698 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Identifier1', p_atp_supply_demand.Identifier1(j));
2699 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Identifier2', p_atp_supply_demand.Identifier2(j));
2700 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Identifier3', p_atp_supply_demand.Identifier3(j));
2701 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Identifier4', p_atp_supply_demand.Identifier4(j));
2702 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Supply_Demand_Type', p_atp_supply_demand.Supply_Demand_Type(j));
2703 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Supply_Demand_Source_Type', p_atp_supply_demand.Supply_Demand_Source_Type(j));
2704 DBMS_SQL.BIND_VARIABLE(cur_handler, ':name', p_atp_supply_demand.Supply_Demand_Source_Type_name(j));
2705 DBMS_SQL.BIND_VARIABLE(cur_handler, ':Supply_Demand_Date', p_atp_supply_demand.Supply_Demand_Date(j));
2706 DBMS_SQL.BIND_VARIABLE(cur_handler, ':supply_demand_quantity', p_atp_supply_demand.supply_demand_quantity(j));
2707 DBMS_SQL.BIND_VARIABLE(cur_handler, ':disposition_type', p_atp_supply_demand.disposition_type(j));
2708 DBMS_SQL.BIND_VARIABLE(cur_handler, ':disposition_name', p_atp_supply_demand.disposition_name(j));
2709 DBMS_SQL.BIND_VARIABLE(cur_handler, ':pegging_id', p_atp_supply_demand.pegging_id(j));
2710 DBMS_SQL.BIND_VARIABLE(cur_handler, ':end_pegging_id', p_atp_supply_demand.end_pegging_id(j));
2711 DBMS_SQL.BIND_VARIABLE(cur_handler, ':created_by', l_user_id);
2712 DBMS_SQL.BIND_VARIABLE(cur_handler, ':last_updated_by', l_user_id);
2713 DBMS_SQL.BIND_VARIABLE(cur_handler, ':last_update_login', l_user_id);
2714
2715 -- Execute the cursor
2716 rows_processed := DBMS_SQL.EXECUTE(cur_handler);
2717
2718 END LOOP;
2719 IF PG_DEBUG in ('Y', 'C') THEN
2720 msc_sch_wb.atp_debug('Put_SD_Data: ' || 'enter put_into_temp_table :20');
2721 END IF;
2722
2723 -- Close the cursor in case it is open
2724 IF DBMS_SQL.IS_OPEN(cur_handler) THEN
2725 DBMS_SQL.CLOSE_CURSOR(cur_handler);
2726 END IF;
2727
2728 END IF; -- IF x_db_link IS NULL
2729 END IF;
2730
2731 END Put_SD_Data;
2732
2733 procedure Update_Line_Item_Properties(p_session_id IN NUMBER,
2734 Action IN NUMBER) --3720018
2735 IS
2736 BEGIN
2737 IF PG_DEBUG in ('Y', 'C') THEN
2738 msc_sch_wb.atp_debug('Inside Update_Line_Item_Properties');
2739 msc_sch_wb.atp_debug('Update item properties');
2740 END IF;
2741 update mrp_atp_schedule_temp mast
2742 --bug 4078703: Populate atp_lead time as this lead time is required
2743 --on OM sales order lies to support misc. functionalities in inv and
2744 --other module. Here we populte it on top model line only. This value is populated
2745 -- option class and items in put_sch_data_resulst_mode procedure
2746 set (atp_flag, atp_components_flag, bom_item_type, pick_components_flag, fixed_lt, variable_lt, atp_lead_time) =
2747 (Select msi.atp_flag,
2748 decode(MSC_ATP_PVT.G_INV_CTP, 5,
2749 --IF ATP flag for PTO model/ATO model is other than 'N' then we still go to destination
2750 -- Thats why atp components flag is set as it is for PTO ato models
2751 decode(mast.order_line_id, mast.ato_model_line_id, msi.atp_components_flag,
2752 decode(msi.pick_components_flag, 'Y', msi.atp_components_flag, 'N')) ,
2753 msi.atp_components_flag ),
2754 msi.bom_item_type,
2755 msi.pick_components_flag,
2756 msi.fixed_lead_time,
2757 msi.VARIABLE_LEAD_TIME,
2758 ---bug 4078703: populate ATP lead time
2759 CEIL(decode(mast.order_line_id, mast.ato_model_line_id,
2760 decode(bom_item_type, 1,
2761 (NVL(msi.fixed_lead_time, 0) + (NVL(msi.VARIABLE_LEAD_TIME, 0) * mast.quantity_ordered)) * (1 + MSC_ATP_PVT.G_MSO_LEAD_TIME_FACTOR), 0), null))
2762 from mtl_system_items msi
2763 where msi.organization_id = nvl(mast.source_organization_id, mast.validation_org)
2764 and msi.inventory_item_id = mast.inventory_item_id)
2765 where mast.session_id = p_session_id
2766 --bug 3378648
2767 and mast.status_flag in (99,4)--4658238
2768 and (mast.source_organization_id is not null
2769 or mast.validation_org is not null);
2770
2771 IF PG_DEBUG in ('Y', 'C') THEN
2772 msc_sch_wb.atp_debug('After updating item properties');
2773 msc_sch_wb.atp_debug('Rows updated := ' || SQL%ROWCOUNT);
2774 END IF;
2775
2776 --3720018, this query will update old_source_organization_id and old_demand_class
2777 -- in case of atp inquiry for a scheduled line from sales order pad
2778 IF ( NVL(Action, -1) = 100 ) THEN
2779 update mrp_atp_schedule_temp mast
2780 set (mast.old_source_organization_id, mast.Old_Demand_Class )=
2781 (SELECT mast.Source_Organization_Id,
2782 NVL(mast.Old_Demand_Class, mast.demand_class)
2783 from oe_order_lines_all o
2784 where o.line_id = mast.order_line_id and
2785 o.schedule_ship_date is not NULL
2786 )
2787 where mast.Old_Source_Organization_Id is NULL and
2788 mast.session_id = p_session_id;
2789 END IF;
2790 IF PG_DEBUG in ('Y', 'C') THEN
2791 msc_sch_wb.atp_debug('After updating old_source_organization_id and old_demand_class');
2792 msc_sch_wb.atp_debug('Rows updated := ' || SQL%ROWCOUNT);
2793 END IF;
2794 -- 3720018
2795
2796 END Update_Line_item_properties;
2797
2798 procedure Put_Sch_data_Request_Mode(p_atp_rec IN MRP_ATP_PUB.atp_rec_typ,
2799 p_session_id IN NUMBER)
2800 IS
2801 l_status_flag NUMBER := 99;
2802 j NUMBER;
2803 l_user_id number;
2804 l_sequence_number MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
2805 l_count number;
2806
2807 begin
2808
2809 IF PG_DEBUG in ('Y', 'C') THEN
2810 msc_sch_wb.atp_debug('enter Put_Sch_data_Request_Mode');
2811 END IF;
2812
2813 l_user_id := FND_GLOBAL.USER_ID;
2814 l_count := p_atp_rec.inventory_item_id.count;
2815 --- Delete Old Data
2816 Delete from mrp_atp_schedule_temp where session_id = p_session_id
2817 --bug 3378648: delete only ATP relevent data
2818 and status_flag in (1,2, 99);
2819
2820 IF PG_DEBUG in ('Y', 'C') THEN
2821 msc_sch_wb.atp_debug('***** After Deleting data for old session ****');
2822 END IF;
2823
2824 l_sequence_number.Extend(l_count);
2825
2826 IF PG_DEBUG in ('Y', 'C') THEN
2827 msc_sch_wb.atp_debug('After Extending Sequence array');
2828 END IF;
2829 IF nvl(p_atp_rec.calling_module(1), -1) in (-1, 724,-99) THEN
2830 FOR j in 1..l_count LOOP
2831 l_sequence_number(j) := j;
2832 END LOOP;
2833 IF PG_DEBUG in ('Y', 'C') THEN
2834 msc_sch_wb.atp_debug('l_sequence_number.count := ' || l_sequence_number.count);
2835 msc_sch_wb.atp_debug('l_count := ' || l_count);
2836 END IF;
2837 END IF;
2838
2839
2840 FORALL j in 1..l_count
2841 INSERT INTO mrp_atp_schedule_temp
2842 (
2843 mdi_rowid,
2844 session_id,
2845 scenario_id,
2846 sr_instance_id,
2847 inventory_item_id ,
2848 inventory_item_name,
2849 source_organization_id,
2850 source_organization_code,
2851 order_header_id, -- add
2852 Demand_Source_Delivery,
2853 Demand_Source_Type,
2854 atp_lead_time,
2855 order_line_id, -- different
2856 order_number,
2857 calling_module,
2858 customer_id,
2859 customer_site_id,
2860 destination_time_zone,
2861 quantity_ordered,
2862 uom_code,
2863 requested_ship_date,
2864 requested_arrival_date,
2865 latest_acceptable_date,
2866 delivery_lead_time,
2867 freight_carrier,
2868 ship_method,
2869 demand_class,
2870 ship_set_name,
2871 arrival_set_name,
2872 override_flag,
2873 action,
2874 scheduled_ship_date, -- different
2875 available_quantity,
2876 requested_date_quantity,
2877 group_ship_date,
2878 group_arrival_date,
2879 vendor_id,
2880 vendor_name,
2881 vendor_site_id,
2882 vendor_site_name,
2883 insert_flag,
2884 error_code,
2885 error_Message,
2886 status_flag,
2887 oe_flag,
2888 end_pegging_id,
2889 old_source_organization_id,
2890 old_demand_class,
2891 scheduled_arrival_date,
2892 attribute_06,
2893 organization_id,
2894 substitution_typ_code,
2895 req_item_detail_flag,
2896 old_inventory_item_id,
2897 request_item_id,
2898 request_item_name,
2899 req_item_req_date_qty,
2900 req_item_available_date_qty,
2901 req_item_available_date,
2902 sales_rep,
2903 customer_contact,
2904 subst_flag,
2905 creation_date,
2906 created_by,
2907 last_update_date,
2908 last_updated_by,
2909 last_update_login,
2910 diagnostic_atp_flag,
2911 sequence_number,
2912 source_doc_id,
2913 ---columns for CTO project
2914 Top_Model_line_id,
2915 ATO_Parent_Model_Line_Id,
2916 ATO_Model_Line_Id,
2917 Parent_line_id,
2918 match_item_id,
2919 Config_item_line_id,
2920 Validation_Org,
2921 Component_Sequence_ID,
2922 Component_Code,
2923 line_number,
2924 included_item_flag,
2925 atp_flag,
2926 firm_flag,
2927 order_line_number,
2928 option_number,
2929 shipment_number,
2930 item_desc,
2931 old_line_schedule_date,
2932 old_source_organization_code,
2933 firm_source_org_id,
2934 firm_source_org_code,
2935 firm_ship_date,
2936 firm_arrival_date,
2937 ship_method_text,
2938 ship_set_id,
2939 arrival_set_id,
2940 PROJECT_ID,
2941 TASK_ID,
2942 PROJECT_NUMBER,
2943 TASK_NUMBER,
2944 original_request_date,
2945 CASCADE_MODEL_INFO_TO_COMP,
2946 internal_org_id, --4279623
2947 customer_country, --2814895
2948 customer_state,
2949 customer_city,
2950 customer_postal_code,
2951 party_site_id,
2952 part_of_set ---4500382
2953
2954 )
2955 values
2956 (
2957 p_atp_rec.row_id(j),
2958 p_session_id,
2959 NVL(p_atp_rec.scenario_id(j), -1),
2960 MSC_ATP_PVT.G_INSTANCE_ID,
2961 --p_atp_rec.instance_id(j),
2962 p_atp_rec.inventory_item_id(j) ,
2963 p_atp_rec.inventory_item_name(j),
2964 p_atp_rec.source_organization_id(j),
2965 p_atp_rec.source_organization_code(j),
2966 nvl(p_atp_rec.demand_source_header_id(j), -1),
2967 p_atp_rec.demand_source_delivery(j),
2968 p_atp_rec.demand_source_type(j),
2969 p_atp_rec.atp_lead_time(j),
2970 NVL(p_atp_rec.identifier(j),0),
2971 p_atp_rec.order_number(j),
2972 p_atp_rec.calling_module(j),
2973 p_atp_rec.customer_id(j),
2974 p_atp_rec.customer_site_id(j),
2975 p_atp_rec.destination_time_zone(j),
2976 p_atp_rec.quantity_ordered(j),
2977 p_atp_rec.quantity_uom(j),
2978 p_atp_rec.requested_ship_date(j),
2979 p_atp_rec.requested_arrival_date(j),
2980 p_atp_rec.latest_acceptable_date(j),
2981 p_atp_rec.delivery_lead_time(j),
2982 p_atp_rec.freight_carrier(j),
2983 p_atp_rec.ship_method(j),
2984 p_atp_rec.demand_class(j),
2985 p_atp_rec.ship_set_name(j),
2986 p_atp_rec.arrival_set_name(j),
2987 p_atp_rec.override_flag(j),
2988 p_atp_rec.action(j),
2989 p_atp_rec.ship_date(j),
2990 p_atp_rec.available_quantity(j),
2991 p_atp_rec.requested_date_quantity(j),
2992 p_atp_rec.group_ship_date(j),
2993 p_atp_rec.group_arrival_date(j),
2994 p_atp_rec.vendor_id(j),
2995 p_atp_rec.vendor_name(j),
2996 p_atp_rec.vendor_site_id(j),
2997 p_atp_rec.vendor_site_name(j),
2998 p_atp_rec.insert_flag(j),
2999 p_atp_rec.error_code(j),
3000 p_atp_rec.message(j),
3001 l_status_flag,
3002 p_atp_rec.oe_flag(j),
3003 p_atp_rec.end_pegging_id(j),
3004 p_atp_rec.old_source_organization_id(j),
3005 p_atp_rec.old_demand_class(j),
3006 p_atp_rec.arrival_date(j),
3007 p_atp_rec.attribute_06(j),
3008 p_atp_rec.organization_id(j),
3009 p_atp_rec.substitution_typ_code(j),
3010 p_atp_rec.req_item_detail_flag(j),
3011 p_atp_rec.old_inventory_item_id(j),
3012 p_atp_rec.request_item_id(j),
3013 p_atp_rec.request_item_name(j),
3014 p_atp_rec.req_item_req_date_qty(j),
3015 p_atp_rec.req_item_available_date_qty(j),
3016 p_atp_rec.req_item_available_date(j),
3017 p_atp_rec.sales_rep(j),
3018 p_atp_rec.customer_contact(j),
3019 p_atp_rec.subst_flag(j),
3020 sysdate,
3021 l_user_id,
3022 sysdate,
3023 l_user_id,
3024 l_user_id,
3025 p_atp_rec.attribute_02(j),
3026 decode( nvl(p_atp_rec.calling_module(j),-1),724,l_sequence_number(j),
3027 -99,l_sequence_number(j),
3028 -1, l_sequence_number(j),
3029 p_atp_rec.attribute_11(j)),
3030 p_atp_rec.attribute_01(j),
3031 --cto_attribute
3032 p_atp_rec.Top_Model_line_id(j),
3033 p_atp_rec.ATO_Parent_Model_Line_Id(j),
3034 p_atp_rec.ATO_Model_Line_Id(j),
3035 p_atp_rec.Parent_line_id(j),
3036 p_atp_rec.match_item_id(j),
3037 p_atp_rec.Config_item_line_id(j),
3038 p_atp_rec.Validation_Org(j),
3039 p_atp_rec.Component_Sequence_ID(j),
3040 p_atp_rec.Component_Code(j),
3041 p_atp_rec.line_number(j),
3042 p_atp_rec.included_item_flag(j),
3043 decode(p_atp_rec.source_organization_id(j), null, 'Y', null),
3044 p_atp_rec.firm_flag(j),
3045 p_atp_rec.order_line_number(j),
3046 p_atp_rec.option_number(j),
3047 p_atp_rec.shipment_number(j),
3048 p_atp_rec.item_desc(j),
3049 p_atp_rec.old_line_schedule_date(j),
3050 p_atp_rec.old_source_organization_code(j),
3051 p_atp_rec.firm_source_org_id(j),
3052 p_atp_rec.firm_source_org_code(j),
3053 p_atp_rec.firm_ship_date(j),
3054 p_atp_rec.firm_arrival_date(j),
3055 p_atp_rec.ship_method_text(j),
3056 p_atp_rec.ship_set_id(j),
3057 p_atp_rec.arrival_set_id(j),
3058 p_atp_rec.PROJECT_ID(j),
3059 p_atp_rec.TASK_ID(j),
3060 p_atp_rec.PROJECT_NUMBER(j),
3061 p_atp_rec.TASK_NUMBER(j),
3062 p_atp_rec.original_request_date(j),
3063 p_atp_rec.CASCADE_MODEL_INFO_TO_COMP(j),
3064 p_atp_rec.internal_org_id(j), --4279623
3065 p_atp_rec.customer_country(j), --2814895
3066 p_atp_rec.customer_state(j),
3067 p_atp_rec.customer_city(j),
3068 p_atp_rec.customer_postal_code(j),
3069 p_atp_rec.party_site_id(j),
3070 nvl(p_atp_rec.part_of_set(j),'N') --4500382
3071 );
3072
3073
3074 ---now update item attributes
3075 --we have already inserted atp_flag = 'Y' if source organization_id is null
3076 -- if source organzation_id is provided then atp_flag is inserted as null
3077 --If we have invalid org-item combination then atp_flag will remain null
3078 -- else it will be updated from atp_flag of nvl(src_ord, validation_org)
3079 IF PG_DEBUG in ('Y', 'C') THEN
3080 msc_sch_wb.atp_debug('After Inserting the data in request mode');
3081 msc_sch_wb.atp_debug('rows inserted = ' || SQL%ROWCOUNT);
3082 END IF;
3083
3084 IF MSC_ATP_PVT.G_CALLING_MODULE <> 724 THEN
3085 /*
3086 update mrp_atp_schedule_temp mast
3087 set (atp_flag, atp_components_flag, bom_item_type, pick_components_flag, fixed_lt, variable_lt) =
3088 (Select msi.atp_flag,
3089 decode(MSC_ATP_PVT.G_INV_CTP, 5,
3090 --IF ATP flag for PTO model/ATO model is other than 'N' then we still go to destination
3091 -- Thats why atp components flag is set as it is for PTO ato models
3092 decode(mast.order_line_id, mast.ato_model_line_id, msi.atp_components_flag,
3093 decode(msi.pick_components_flag, 'Y', msi.atp_components_flag, 'N')) ,
3094 msi.atp_components_flag ),
3095 msi.bom_item_type,
3096 msi.pick_components_flag,
3097 msi.fixed_lead_time,
3098 msi.VARIABLE_LEAD_TIME
3099 from mtl_system_items msi
3100 where msi.organization_id = nvl(mast.source_organization_id, mast.validation_org)
3101 and msi.inventory_item_id = mast.inventory_item_id)
3102 where mast.session_id = p_session_id
3103 --bug 3378648: only update request data
3104 and status_flag = 99
3105 and (mast.source_organization_id is not null
3106 or mast.validation_org is not null);
3107
3108 IF PG_DEBUG in ('Y', 'C') THEN
3109 msc_sch_wb.atp_debug('After updating item properties');
3110 msc_sch_wb.atp_debug('Rows updated := ' || SQL%ROWCOUNT);
3111 END IF;
3112 */
3113 MSC_ATP_UTILS.Update_Line_item_properties(p_session_id, p_atp_rec.action(1)); --3720018
3114 /*
3115 -- Bug 3449812 - Removed IF to populate internal_org in all cases
3116 -- IF MSC_ATP_PVT.G_INV_CTP = 4 THEN
3117 --add condition to fiter based on atp query
3118 --removing the condition below to suport OE flag for all modules.
3119 --and (MSC_ATP_PVT.G_CALLING_MODULE IN (-1, 660)) THEN
3120 IF PG_DEBUG in ('Y', 'C') THEN
3121 msc_sch_wb.atp_debug('Check if OE flag has been turned on or not');
3122 END IF;
3123 select count(*)
3124 into l_count
3125 from mrp_atp_schedule_temp mast
3126 where mast.session_id = p_session_id
3127 --bug 3378648
3128 and status_flag = 99
3129 and mast.OE_FLAG = 'Y';
3130
3131 IF PG_DEBUG in ('Y', 'C') THEN
3132 msc_sch_wb.atp_debug('l_count for OE Flag := ' || l_count);
3133 END IF;
3134
3135 IF l_count > 0 then
3136
3137 update mrp_atp_schedule_temp mast
3138 set OE_FLAG =
3139 (Select decode(MSC_ATP_PVT.G_INV_CTP, 5, mast.OE_FLAG,
3140 decode( prha.interface_source_code, 'MRP', 'Y', 'MSC', 'Y', 'N'))
3141 from po_requisition_headers_all prha
3142 where prha.requisition_header_id = mast.source_doc_id),
3143 INTERNAL_ORG_ID = -- Bug 3449812
3144 (Select po.destination_organization_id
3145 from po_requisition_lines_all po,
3146 oe_order_lines_all oe
3147 where oe.source_document_line_id = po.requisition_line_id
3148 and oe.line_id = mast.order_line_id)
3149 where mast.session_id = p_session_id
3150 --bug 3378648: only update request data
3151 and status_flag = 99
3152 and mast.OE_FLAG = 'Y';
3153
3154 IF PG_DEBUG in ('Y', 'C') THEN
3155 msc_sch_wb.atp_debug('After updating OE Flag ');
3156 END IF;
3157
3158 END IF;
3159
3160 -- END IF; */
3161 ELSE
3162 IF PG_DEBUG in ('Y', 'C') THEN
3163 msc_sch_wb.atp_debug('Call from destination Instance. No need to update atp flags or oe flags');
3164 END IF;
3165 END IF;
3166 IF PG_DEBUG in ('Y', 'C') THEN
3167 msc_sch_wb.atp_debug('exit Put_Sch_data_Request_Mode');
3168 END IF;
3169 EXCEPTION
3170 WHEN OTHERS THEN
3171 msc_sch_wb.atp_debug('Something wrong in Put_Sch_data_Request_Mode');
3172 msc_sch_wb.atp_debug('Sql Err := ' || sqlerrm);
3173
3174 END Put_Sch_Data_Request_Mode;
3175
3176 Procedure Put_Sch_data_result_mode(p_atp_rec IN MRP_ATP_PUB.atp_rec_typ,
3177 p_dblink IN VARCHAR2,
3178 p_session_id IN NUMBER)
3179 IS
3180 j NUMBER;
3181 l_dynstring VARCHAR2(128) := NULL;
3182 sql_stmt VARCHAR2(10000);
3183 l_atp_rec MRP_ATP_PUB.atp_rec_typ;
3184 l_status_flag NUMBER := 99; -- bug 2974324. Initialize l_status_flag to 99 here.
3185 l_sequence_number MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr(); -- for bug 2974324.
3186 found NUMBER;
3187
3188 mast_rec mrp_atp_utils.mrp_atp_schedule_temp_typ;
3189 mast_rec_insert mrp_atp_utils.mrp_atp_schedule_temp_typ;
3190 TYPE mastcurtyp IS REF CURSOR;
3191 mast_cursor mastcurtyp;
3192 l_ret_code VARCHAR2(1);
3193 l_ret_status VARCHAR2(1000);
3194 cur_handler NUMBER;
3195 rows_processed NUMBER;
3196 l_plan_name varchar2(10); -- for bug 2392456
3197 l_user_id number;
3198 l_count number; -- for bug 2974324
3199 l_count_temp number;
3200
3201
3202 BEGIN
3203
3204
3205 IF PG_DEBUG in ('Y', 'C') THEN
3206 msc_sch_wb.atp_debug('enter Put_Sch_data_result_mode');
3207 msc_sch_wb.atp_debug('G_INCLUDED_ITEM_IS_PRESENT :=' || MSC_ATP_CTO.G_INCLUDED_ITEM_IS_PRESENT);
3208 END IF;
3209
3210 -- bug 2974324. Set l_status_flag to 2 here.
3211 l_status_flag := SYS_NO;
3212 l_count := p_atp_rec.inventory_item_id.count;
3213
3214
3215 msc_sch_wb.atp_debug('l_count := '|| l_count);
3216 /*l_sequence_number.extend(l_count);
3217 FOR j in 1..l_count LOOP
3218 l_sequence_number(j) := j;
3219 END LOOP; */
3220 IF PG_DEBUG in ('Y', 'C') THEN
3221 msc_sch_wb.atp_debug(' insert data for Call from other modules');
3222 FOR j in 1..l_count LOOP
3223 msc_sch_wb.atp_debug('j');
3224 msc_sch_wb.atp_debug('Identifier := ' || p_atp_rec.identifier(j));
3225 msc_sch_wb.atp_debug('Mandatory flag := ' || p_atp_rec.mandatory_item_flag(j));
3226 msc_sch_wb.atp_debug('sequence number := ' || p_atp_rec.sequence_number(j));
3227 msc_sch_wb.atp_debug('atp_lead_time := ' || p_atp_rec.atp_lead_time(j));
3228
3229 END LOOP;
3230 END IF;
3231
3232 FORALL j in 1..l_count
3233 ---bug 3295956: Merge two update sqls in 1. Update ship method on component lines from
3234 -- model line. Cascade info to components based on value of cascade_model_info_to_comp attribute
3235 UPDATE MRP_ATP_SCHEDULE_TEMP
3236 SET
3237
3238 scenario_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3239 NVL(p_atp_rec.scenario_id(j), -1), scenario_id),
3240 inventory_item_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3241 p_atp_rec.inventory_item_id(j), inventory_item_id),
3242 inventory_item_name = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3243 p_atp_rec.inventory_item_name(j), inventory_item_name),
3244 source_organization_id =Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3245 p_atp_rec.source_organization_id(j),
3246 Decode(nvl(cascade_model_info_to_comp, 1), 1,
3247 p_atp_rec.source_organization_id(j), null)),
3248 source_organization_code = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3249 p_atp_rec.source_organization_code(j),
3250 Decode(nvl(cascade_model_info_to_comp, 1), 1,
3251 p_atp_rec.source_organization_code(j), null)),
3252 order_header_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3253 nvl(p_atp_rec.demand_source_header_id(j), -1), null),
3254 Demand_Source_Type = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3255 p_atp_rec.demand_source_type(j), null),
3256 delivery_lead_time = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3257 p_atp_rec.delivery_lead_time(j),
3258 Decode(nvl(cascade_model_info_to_comp, 1), 1,
3259 p_atp_rec.delivery_lead_time(j), null)),
3260 ship_method = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3261 p_atp_rec.ship_method(j),
3262 Decode(nvl(cascade_model_info_to_comp, 1), 1,
3263 p_atp_rec.ship_method(j), null)),
3264 demand_class = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3265 p_atp_rec.demand_class(j), null),
3266 scheduled_ship_date = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3267 p_atp_rec.ship_date(j),
3268 Decode(nvl(cascade_model_info_to_comp, 1), 1,
3269 p_atp_rec.ship_date(j),null)),
3270 available_quantity = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3271 p_atp_rec.available_quantity(j), null),
3272 requested_date_quantity = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3273 p_atp_rec.requested_date_quantity(j), null),
3274 group_ship_date = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3275 p_atp_rec.group_ship_date(j),
3276 Decode(nvl(cascade_model_info_to_comp, 1), 1,
3277 p_atp_rec.group_ship_date(j), null)),
3278 group_arrival_date = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3279 p_atp_rec.group_arrival_date(j),
3280 Decode(nvl(cascade_model_info_to_comp, 1), 1,
3281 p_atp_rec.group_arrival_date(j),null)),
3282 error_code = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3283 p_atp_rec.error_code(j),
3284 Decode(error_code, null,decode(p_atp_rec.error_code(j), 150, 0, 61, 0, 0, 0, MSC_ATP_PVT.GROUPEL_ERROR))),
3285 error_Message = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3286 p_atp_rec.message(j), null),
3287 status_flag = 2,
3288 end_pegging_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3289 p_atp_rec.end_pegging_id(j),
3290 Decode(nvl(cascade_model_info_to_comp, 1), 1,
3291 p_atp_rec.end_pegging_id(j),null)),
3292 scheduled_arrival_date = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3293 p_atp_rec.arrival_date(j),
3294 Decode(nvl(cascade_model_info_to_comp, 1), 1,
3295 p_atp_rec.arrival_date(j),null)),
3296 organization_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3297 p_atp_rec.organization_id(j), null),
3298 request_item_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3299 p_atp_rec.request_item_id(j), null),
3300 request_item_name = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3301 p_atp_rec.request_item_name(j), null),
3302 req_item_req_date_qty = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3303 p_atp_rec.req_item_req_date_qty(j), null),
3304 req_item_available_date_qty = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3305 p_atp_rec.req_item_available_date_qty(j), null),
3306 req_item_available_date =Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3307 p_atp_rec.req_item_available_date(j), null),
3308 sales_rep = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3309 p_atp_rec.sales_rep(j), null),
3310 customer_contact = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3311 p_atp_rec.customer_contact(j), null),
3312 compile_designator = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3313 DECODE(MSC_ATP_PVT.G_INV_CTP, 4, p_atp_rec.attribute_07(j), null), null),
3314 subst_flag = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3315 p_atp_rec.subst_flag(j), null),
3316 match_item_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3317 p_atp_rec.match_item_id(j), null),
3318 matched_item_name = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
3319 p_atp_rec.matched_item_name(j), null),
3320 plan_id = p_atp_rec.plan_id(j),
3321 --bug 3328421
3322 first_valid_ship_arrival_date = p_atp_rec.first_valid_ship_arrival_date(j),
3323 --bug 4078703: update atp_lead_time on options
3324 --atp_lead_time = Decode(order_line_id, NVL(ato_model_line_id, order_line_id), 0, p_atp_rec.atp_lead_time(j))
3325 -- atp_lead_time = Decode(order_line_id, NVL(ato_model_line_id, order_line_id), NULL, p_atp_rec.atp_lead_time(j))
3326 atp_lead_time = Decode(ato_model_line_id, null, null, order_line_id, 0, p_atp_rec.atp_lead_time(j))
3327
3328 where session_id = p_session_id
3329 --bug 3378648: update only request data
3330 and status_flag = 99
3331 and NVL(ato_model_line_id, order_line_id) = p_atp_rec.identifier(j)
3332 and NVL(p_atp_rec.mandatory_item_flag(j), 2) = 2
3333 --bug 3347424: added this condition so that line corresponding
3334 --to particular warehouse is update in case of global order promising
3335 --bug 3373467: Following condition doesn't work if no sources are found.
3336 -- in that case source orgs remian null and we were not updating any thing.
3337 and NVL(source_organization_id, NVL(p_atp_rec.source_organization_id(j), -1)) =
3338 NVL(p_atp_rec.source_organization_id(j), -1);
3339 --and sequence_number = p_atp_rec.sequence_number(j);
3340 --add condition for inv id and seq id
3341 IF PG_DEBUG in ('Y', 'C') THEN
3342 msc_sch_wb.atp_debug('After Updating the table');
3343 msc_sch_wb.atp_debug('Rows Updated := ' || SQL%ROWCOUNT);
3344 END IF;
3345
3346 IF MSC_ATP_CTO.G_INCLUDED_ITEM_IS_PRESENT =1 THEN
3347 ---included items are present. Insert them into table
3348 IF PG_DEBUG in ('Y', 'C') THEN
3349 msc_sch_wb.atp_debug('Included Items are present');
3350 END IF;
3351 --couldn't find a wayt to insert data selectively from
3352 -- pl/sql table to temp table. Therefore, copying the records for the time being.
3353 FOR j in 1..p_atp_rec.inventory_item_id.count LOOP
3354 IF NVL(p_atp_rec.mandatory_item_flag(j), 2) = 1 THEN
3355 IF PG_DEBUG in ('Y', 'C') THEN
3356 msc_sch_wb.atp_debug('Add Included item for line := ' || j);
3357 END IF;
3358 MSC_SATP_FUNC.Assign_Atp_Input_Rec(p_atp_rec,
3359 j,
3360 l_atp_rec,
3361 l_ret_status );
3362 END IF;
3363 END LOOP;
3364
3365 IF PG_DEBUG in ('Y', 'C') THEN
3366 msc_sch_wb.atp_debug('Number of included items := ' || l_atp_rec.inventory_item_id.count);
3367 END IF;
3368 l_count := l_atp_rec.inventory_item_id.count;
3369 FORALL j in 1..l_count
3370 INSERT INTO mrp_atp_schedule_temp
3371 (
3372 mdi_rowid,
3373 session_id,
3374 scenario_id,
3375 sr_instance_id,
3376 inventory_item_id,
3377 inventory_item_name,
3378 source_organization_id,
3379 source_organization_code,
3380 order_header_id, -- add
3381 Demand_Source_Delivery,
3382 Demand_Source_Type,
3383 atp_lead_time,
3384 order_line_id, -- different
3385 order_number,
3386 calling_module,
3387 customer_id,
3388 customer_site_id,
3389 destination_time_zone,
3390 quantity_ordered,
3391 uom_code,
3392 requested_ship_date,
3393 requested_arrival_date,
3394 latest_acceptable_date,
3395 delivery_lead_time,
3396 freight_carrier,
3397 ship_method,
3398 demand_class,
3399 ship_set_name,
3400 arrival_set_name,
3401 override_flag,
3402 action,
3403 scheduled_ship_date, -- different
3404 available_quantity,
3405 requested_date_quantity,
3406 group_ship_date,
3407 group_arrival_date,
3408 vendor_id,
3409 vendor_name,
3410 vendor_site_id,
3411 vendor_site_name,
3412 insert_flag,
3413 error_code,
3414 error_Message,
3415 status_flag,
3416 oe_flag,
3417 end_pegging_id,
3418 old_source_organization_id,
3419 old_demand_class,
3420 scheduled_arrival_date,
3421 attribute_06,
3422 organization_id,
3423 substitution_typ_code,
3424 req_item_detail_flag,
3425 old_inventory_item_id,
3426 request_item_id,
3427 request_item_name,
3428 req_item_req_date_qty,
3429 req_item_available_date_qty,
3430 req_item_available_date,
3431 sales_rep,
3432 customer_contact,
3433 compile_designator, -- added for bug 2392456
3434 subst_flag,
3435 creation_date,
3436 created_by,
3437 last_update_date,
3438 last_updated_by,
3439 last_update_login,
3440 diagnostic_atp_flag,
3441 sequence_number,
3442 mandatory_item_flag,
3443 --bug 3328421:
3444 first_valid_ship_arrival_date
3445 )
3446 VALUES
3447 (
3448 l_atp_rec.row_id(j),
3449 p_session_id,
3450 NVL(l_atp_rec.scenario_id(j), -1),
3451 l_atp_rec.instance_id(j),
3452 l_atp_rec.inventory_item_id(j),
3453 l_atp_rec.inventory_item_name(j),
3454 l_atp_rec.source_organization_id(j),
3455 l_atp_rec.source_organization_code(j),
3456 nvl(l_atp_rec.demand_source_header_id(j), -1),
3457 l_atp_rec.demand_source_delivery(j),
3458 l_atp_rec.demand_source_type(j),
3459 l_atp_rec.atp_lead_time(j),
3460 NVL(l_atp_rec.identifier(j),0),
3461 l_atp_rec.order_number(j),
3462 l_atp_rec.calling_module(j),
3463 l_atp_rec.customer_id(j),
3464 l_atp_rec.customer_site_id(j),
3465 l_atp_rec.destination_time_zone(j),
3466 l_atp_rec.quantity_ordered(j),
3467 l_atp_rec.quantity_uom(j),
3468 l_atp_rec.requested_ship_date(j),
3469 l_atp_rec.requested_arrival_date(j),
3470 l_atp_rec.latest_acceptable_date(j),
3471 l_atp_rec.delivery_lead_time(j),
3472 l_atp_rec.freight_carrier(j),
3473 l_atp_rec.ship_method(j),
3474 l_atp_rec.demand_class(j),
3475 l_atp_rec.ship_set_name(j),
3476 l_atp_rec.arrival_set_name(j),
3477 l_atp_rec.override_flag(j),
3478 l_atp_rec.action(j),
3479 l_atp_rec.ship_date(j),
3480 l_atp_rec.available_quantity(j),
3481 l_atp_rec.requested_date_quantity(j),
3482 l_atp_rec.group_ship_date(j),
3483 l_atp_rec.group_arrival_date(j),
3484 l_atp_rec.vendor_id(j),
3485 l_atp_rec.vendor_name(j),
3486 l_atp_rec.vendor_site_id(j),
3487 l_atp_rec.vendor_site_name(j),
3488 l_atp_rec.insert_flag(j),
3489 l_atp_rec.error_code(j),
3490 l_atp_rec.message(j),
3491 l_status_flag,
3492 l_atp_rec.oe_flag(j),
3493 l_atp_rec.end_pegging_id(j),
3494 l_atp_rec.old_source_organization_id(j),
3495 l_atp_rec.old_demand_class(j),
3496 l_atp_rec.arrival_date(j),
3497 l_atp_rec.attribute_06(j),
3498 l_atp_rec.organization_id(j),
3499 l_atp_rec.substitution_typ_code(j),
3500 l_atp_rec.req_item_detail_flag(j),
3501 l_atp_rec.old_inventory_item_id(j),
3502 l_atp_rec.request_item_id(j),
3503 l_atp_rec.request_item_name(j),
3504 l_atp_rec.req_item_req_date_qty(j),
3505 l_atp_rec.req_item_available_date_qty(j),
3506 l_atp_rec.req_item_available_date(j),
3507 l_atp_rec.sales_rep(j),
3508 l_atp_rec.customer_contact(j),
3509 DECODE(MSC_ATP_PVT.G_INV_CTP, 4, l_atp_rec.attribute_07(j), null ),
3510 l_atp_rec.subst_flag(j),
3511 sysdate,
3512 l_user_id,
3513 sysdate,
3514 l_user_id,
3515 l_user_id,
3516 l_atp_rec.attribute_02(j),
3517 l_atp_rec.sequence_number(j),
3518 l_atp_rec.mandatory_item_flag(j),
3519 --bug 3328421
3520 l_atp_rec.first_valid_ship_arrival_date(j)
3521 );
3522 --where NVL(p_atp_rec.mandatory_item_flag(j), 2) = 1;
3523 IF PG_DEBUG in ('Y', 'C') THEN
3524 msc_sch_wb.atp_debug('Number of Rows Inserted := ' || SQL%ROWCOUNT);
3525 END IF;
3526 END IF; --- IF MSC_ATP_CTO.G_INCLUDED_ITEM_IS_PRESENT =1 THEN
3527
3528
3529
3530
3531 --now update the data for ATO models
3532 --bug 3347424: update plan_id only for scheduling requests
3533 --For global ATP we get multiple lines with same line_id. as a result update was failing.
3534 IF MSC_ATP_CTO.G_MODEL_IS_PRESENT = 1 and p_atp_rec.action(1) <> 100 THEN
3535
3536 IF PG_DEBUG in ('Y', 'C') THEN
3537 msc_sch_wb.atp_debug('Model is present, update model component data');
3538 END IF;
3539 --bug 3295956: cascade ship method and delivery lead time from model to components
3540 -- Cascade info from model to components based on cascade_model_info_to_comp attribute. This is for istore.
3541 /* Update mrp_atp_schedule_temp mast_1
3542 set (scheduled_ship_date, end_pegging_id, scheduled_arrival_date, status_flag,
3543 group_ship_date, group_arrival_date, plan_id, ship_method, delivery_lead_time,
3544 source_organization_id, error_code) =
3545 (select Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, scheduled_ship_date, null),
3546 Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, end_pegging_id, null),
3547 Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, scheduled_arrival_date, null),
3548 2,
3549 Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, group_ship_date, null),
3550 Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, group_arrival_date, null),
3551 plan_id,
3552 Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, ship_method, null),
3553 Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, delivery_lead_time, null),
3554 Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, source_organization_id, null),
3555 decode(error_code, 150, 0, 61, 0, 0, 0, MSC_ATP_PVT.GROUPEL_ERROR)
3556 from mrp_atp_schedule_temp mast_2 where
3557 mast_2.session_id = p_session_id and
3558 mast_2.order_line_id = mast_1.ato_model_line_id and
3559 mast_2.source_organization_id = NVL(mast_1.source_organization_id, mast_2.source_organization_id)
3560 )
3561 where mast_1.session_id = p_session_id
3562 and mast_1.ato_model_line_id is not null
3563 and mast_1.order_line_id <> mast_1.ato_model_line_id;
3564 IF PG_DEBUG in ('Y', 'C') THEN
3565 msc_sch_wb.atp_debug('Number of Rows updated := ' || SQL%ROWCOUNT);
3566 END IF;
3567 */
3568
3569 ---update plan_id on msc_cto_bom and msc_cto_sources for 24x7
3570 update msc_cto_bom mcb
3571 set plan_id = (select plan_id
3572 from mrp_atp_schedule_temp mast
3573 where mast.session_id = p_session_id
3574 and mast.order_line_id = mcb.line_id
3575 )
3576 where mcb.session_id = p_session_id;
3577
3578 IF PG_DEBUG in ('Y', 'C') THEN
3579 msc_sch_wb.atp_debug('Number of Rows updated n msc_cto_bom := ' || SQL%ROWCOUNT);
3580 END IF;
3581
3582 update msc_cto_sources mcs
3583 set plan_id = (select plan_id
3584 from mrp_atp_schedule_temp mast
3585 where mast.session_id = p_session_id
3586 and mast.order_line_id = mcs.line_id
3587 )
3588 where mcs.session_id = p_session_id;
3589
3590 IF PG_DEBUG in ('Y', 'C') THEN
3591 msc_sch_wb.atp_debug('Number of Rows updated in msc_cto_sources := ' || SQL%ROWCOUNT);
3592 END IF;
3593 END IF;
3594
3595
3596 --now transfer the data across dblink
3597 IF p_dblink is not null then
3598 Transfer_Scheduling_data(p_session_id,
3599 p_dblink,
3600 RESULTS_MODE);
3601 END IF;
3602
3603 IF PG_DEBUG in ('Y', 'C') THEN
3604 msc_sch_wb.atp_debug('exit Put_Sch_data_Result_Mode');
3605 END IF;
3606
3607 END Put_Sch_Data_Result_Mode;
3608
3609 Procedure Transfer_Scheduling_data(p_session_id IN Number,
3610 p_dblink IN VARCHAR2,
3611 p_mode IN NUMBER)
3612
3613
3614 IS
3615 l_sql_stmt varchar2(20000);
3616 l_status_flag number;
3617 l_tnsfer_sts_flag number;
3618 L_dest_DBLINK varchar2(128);
3619 l_source_dblink varchar2(128);
3620 BEGIN
3621
3622 IF PG_DEBUG in ('Y', 'C') THEN
3623 msc_sch_wb.atp_debug('enter Transfer_Scheduling_data');
3624 END IF;
3625
3626 IF p_mode = RESULTS_MODE THEN
3627 l_status_flag := 2;
3628 l_tnsfer_sts_flag := 2;
3629 ELSE
3630 l_status_flag := 1;
3631 l_tnsfer_sts_flag := 99;
3632 END IF;
3633
3634 IF p_mode = RESULTS_MODE then
3635 L_source_DBLINK := '@' || p_dblink;
3636 ELSE
3637 l_dest_dblink := '@' || p_dblink;
3638 END IF;
3639
3640 IF PG_DEBUG in ('Y', 'C') THEN
3641 msc_sch_wb.atp_debug('Before deleting old data');
3642 END IF;
3643
3644 /* bug 3378649: delete any data locally
3645 --delete any old data
3646 IF p_mode = RESULTS_MODE THEN
3647 l_sql_stmt :=
3648 'DELETE FROM MRP_ATP_SCHEDULE_TEMP'||L_source_DBLINK||
3649 ' WHERE session_id = :p_session_id '||
3650 ' and status_flag in (1, 99, 2) ';
3651
3652 EXECUTE IMMEDIATE l_sql_stmt USING p_session_id;
3653 ELSE
3654 IF PG_DEBUG in ('Y', 'C') THEN
3655 msc_sch_wb.atp_debug('deleting old data in local table');
3656 END IF;
3657
3658 DELETE FROM MRP_ATP_SCHEDULE_TEMP
3659 WHERE session_id = p_session_id
3660 and status_flag in (1, 99, 2);
3661 END IF;
3662 */
3663
3664 IF PG_DEBUG in ('Y', 'C') THEN
3665 msc_sch_wb.atp_debug('Number of rows deleted := ' || SQL%ROWCOUNT);
3666 msc_sch_wb.atp_debug('After deleting old data');
3667 msc_sch_wb.atp_debug('l_status_flag := ' || l_status_flag);
3668 msc_sch_wb.atp_debug('l_tnsfer_sts_flag := ' || l_tnsfer_sts_flag);
3669 END IF;
3670
3671 l_sql_stmt :=
3672 'Insert into mrp_atp_schedule_temp' || L_source_DBLINK ||
3673 ' (mdi_rowid,
3674 session_id,
3675 scenario_id,
3676 sr_instance_id,
3677 inventory_item_id,
3678 inventory_item_name,
3679 source_organization_id,
3680 source_organization_code,
3681 order_header_id,
3682 Demand_Source_Delivery,
3683 Demand_Source_Type,
3684 atp_lead_time,
3685 order_line_id,
3686 order_number,
3687 calling_module,
3688 customer_id,
3689 customer_site_id,
3690 destination_time_zone,
3691 quantity_ordered,
3692 uom_code,
3693 requested_ship_date,
3694 requested_arrival_date,
3695 latest_acceptable_date,
3696 delivery_lead_time,
3697 freight_carrier,
3698 ship_method,
3699 demand_class,
3700 ship_set_name,
3701 arrival_set_name,
3702 override_flag,
3703 action,
3704 scheduled_ship_date,
3705 available_quantity,
3706 requested_date_quantity,
3707 group_ship_date,
3708 group_arrival_date,
3709 vendor_id,
3710 vendor_name,
3711 vendor_site_id,
3712 vendor_site_name,
3713 insert_flag,
3714 error_code,
3715 error_Message,
3716 status_flag,
3717 oe_flag,
3718 end_pegging_id,
3719 old_source_organization_id,
3720 old_demand_class,
3721 scheduled_arrival_date,
3722 attribute_06,
3723 organization_id,
3724 substitution_typ_code,
3725 req_item_detail_flag,
3726 old_inventory_item_id,
3727 request_item_id,
3728 request_item_name,
3729 req_item_req_date_qty,
3730 req_item_available_date_qty,
3731 req_item_available_date,
3732 sales_rep,
3733 customer_contact,
3734 compile_designator,
3735 subst_flag';
3736 IF MSC_ATP_PVT.G_APPS_VER >= 3 THEN
3737 l_sql_stmt := l_sql_stmt ||
3738 ', creation_date,
3739 created_by,
3740 last_update_date,
3741 last_updated_by,
3742 last_update_login,
3743 diagnostic_atp_flag,
3744 sequence_number,
3745 firm_flag,
3746 order_line_number,
3747 option_number,
3748 shipment_number,
3749 item_desc,
3750 customer_name,
3751 customer_location,
3752 old_line_schedule_date,
3753 old_source_organization_code,
3754 firm_source_org_id,
3755 firm_source_org_code,
3756 firm_ship_date,
3757 firm_arrival_date,
3758 ship_method_text,
3759 ship_set_id,
3760 arrival_set_id,
3761 project_id,
3762 task_id,
3763 project_number,
3764 task_number,
3765 Top_Model_line_id,
3766 ATO_Parent_Model_Line_Id,
3767 ATO_Model_Line_Id,
3768 Parent_line_id,
3769 match_item_id,
3770 matched_item_name,
3771 Config_item_line_id,
3772 Validation_Org,
3773 Component_Sequence_ID,
3774 Component_Code,
3775 line_number,
3776 included_item_flag,
3777 atp_flag,
3778 atp_components_flag,
3779 wip_supply_type,
3780 bom_item_type,
3781 pick_components_flag,
3782 OSS_ERROR_CODE,
3783 original_request_date,
3784 mandatory_item_flag,
3785 CASCADE_MODEL_INFO_TO_COMP,
3786 INTERNAL_ORG_ID, -- Bug 3449812
3787 first_valid_ship_arrival_date, -- bug 3328421
3788 customer_country, --2814895
3789 customer_state, --2814895
3790 customer_city, --2814895
3791 customer_postal_code, --2814895
3792 party_site_id, --2814895
3793 part_of_set --4500382
3794 ';
3795 END IF;
3796
3797 l_sql_stmt := l_sql_stmt ||
3798 ' ) select
3799 mdi_rowid,
3800 session_id,
3801 scenario_id,
3802 sr_instance_id,
3803 inventory_item_id,
3804 inventory_item_name,
3805 source_organization_id,
3806 source_organization_code,
3807 order_header_id,
3808 Demand_Source_Delivery,
3809 Demand_Source_Type,
3810 atp_lead_time,
3811 order_line_id,
3812 order_number,
3813 calling_module,
3814 customer_id,
3815 customer_site_id,
3816 destination_time_zone,
3817 quantity_ordered,
3818 uom_code,
3819 requested_ship_date,
3820 requested_arrival_date,
3821 latest_acceptable_date,
3822 delivery_lead_time,
3823 freight_carrier,
3824 ship_method,
3825 demand_class,
3826 ship_set_name,
3827 arrival_set_name,
3828 override_flag,
3829 action,
3830 scheduled_ship_date,
3831 available_quantity,
3832 requested_date_quantity,
3833 group_ship_date,
3834 group_arrival_date,
3835 vendor_id,
3836 vendor_name,
3837 vendor_site_id,
3838 vendor_site_name,
3839 insert_flag,
3840 error_code,
3841 error_Message, ' ||
3842 l_tnsfer_sts_flag || ',
3843 oe_flag,
3844 end_pegging_id,
3845 old_source_organization_id,
3846 old_demand_class,
3847 scheduled_arrival_date,
3848 attribute_06,
3849 organization_id,
3850 substitution_typ_code,
3851 req_item_detail_flag,
3852 old_inventory_item_id,
3853 request_item_id,
3854 request_item_name,
3855 req_item_req_date_qty,
3856 req_item_available_date_qty,
3857 req_item_available_date,
3858 sales_rep,
3859 customer_contact,
3860 compile_designator,
3861 subst_flag';
3862
3863 IF MSC_ATP_PVT.G_APPS_VER >= 3 THEN
3864 l_sql_stmt := l_sql_stmt ||
3865 ',creation_date,
3866 created_by,
3867 last_update_date,
3868 last_updated_by,
3869 last_update_login,
3870 diagnostic_atp_flag,
3871 sequence_number,
3872 firm_flag,
3873 order_line_number,
3874 option_number,
3875 shipment_number,
3876 item_desc,
3877 customer_name,
3878 customer_location,
3879 old_line_schedule_date,
3880 old_source_organization_code,
3881 firm_source_org_id,
3882 firm_source_org_code,
3883 firm_ship_date,
3884 firm_arrival_date,
3885 ship_method_text,
3886 ship_set_id,
3887 arrival_set_id,
3888 project_id,
3889 task_id,
3890 project_number,
3891 task_number,
3892 Top_Model_line_id,
3893 ATO_Parent_Model_Line_Id,
3894 ATO_Model_Line_Id,
3895 Parent_line_id,
3896 match_item_id,
3897 matched_item_name,
3898 Config_item_line_id,
3899 Validation_Org,
3900 Component_Sequence_ID,
3901 Component_Code,
3902 line_number,
3903 included_item_flag,
3904 atp_flag,
3905 atp_components_flag,
3906 wip_supply_type,
3907 bom_item_type,
3908 pick_components_flag,
3909 OSS_ERROR_CODE,
3910 original_request_date,
3911 mandatory_item_flag,
3912 CASCADE_MODEL_INFO_TO_COMP,
3913 INTERNAL_ORG_ID, -- Bug 3449812
3914 first_valid_ship_arrival_date, --bug 3328421
3915 customer_country, --2814895
3916 customer_state, --2814895
3917 customer_city, --2814895
3918 customer_postal_code, --2814895
3919 party_site_id, --2814895
3920 part_of_set --4500382
3921 ';
3922 END IF;
3923
3924 l_sql_stmt := l_sql_stmt || ' from MRP_ATP_SCHEDULE_TEMP' || l_dest_dblink ||
3925 ' where session_id = :p_session_id
3926 and status_flag = ' || l_tnsfer_sts_flag ;
3927
3928
3929 IF PG_DEBUG in ('Y', 'C') THEN
3930 msc_sch_wb.atp_debug('Transfer_Scheduling_data: ' || l_sql_stmt);
3931 END IF;
3932 EXECUTE IMMEDIATE l_sql_stmt USING p_session_id;
3933
3934 IF PG_DEBUG in ('Y', 'C') THEN
3935 msc_sch_wb.atp_debug('Rows Transfered: ' || SQL%ROWCOUNT);
3936 END IF;
3937
3938
3939 IF PG_DEBUG in ('Y', 'C') THEN
3940 msc_sch_wb.atp_debug('exit Transfer_Scheduling_data');
3941 END IF;
3942 EXCEPTION
3943 WHEN OTHERS THEN
3944 IF PG_DEBUG in ('Y', 'C') THEN
3945 msc_sch_wb.atp_debug('Error Occured while transfering the data accros db link');
3946 msc_sch_wb.atp_debug('errro := ' ||SQLERRM);
3947 END IF;
3948 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3949
3950 END Transfer_Scheduling_data;
3951
3952 /* Bug 5598066: Function to Truncate demand to 6 decimal places.
3953 Also if the 7th point if 9, it will be a 1 increase in the 6th point. */
3954
3955 FUNCTION Truncate_Demand (p_demand_qty IN NUMBER)
3956 Return NUMBER
3957 IS
3958 l_truncated_demand NUMBER;
3959 BEGIN
3960
3961 l_truncated_demand := (floor((p_demand_qty + 0.0000001) * 1000000.0)/1000000.0) ;
3962
3963 RETURN l_truncated_demand ;
3964 EXCEPTION
3965 WHEN OTHERS THEN
3966 IF PG_DEBUG in ('Y', 'C') THEN
3967 msc_sch_wb.atp_debug('Error in function Truncate_Demand: '||sqlerrm);
3968 END IF;
3969 END Truncate_Demand;
3970
3971
3972 END MSC_ATP_UTILS;
3973