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