[Home] [Help]
PACKAGE BODY: APPS.MSC_SCH_WB
Source
1 PACKAGE BODY msc_sch_wb AS
2 /* $Header: MSCOSCWB.pls 120.6.12010000.1 2008/11/18 00:35:16 cmsops ship $ */
3
4
5 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
6
7 PROCEDURE GET_ATP_RESULT (
8 x_session_id IN number,
9 commit_flag IN NUMBER,
10 call_oe IN NUMBER,
11 x_msg_count OUT NoCopy number,
12 x_msg_data OUT NoCopy varchar2,
13 x_return_status OUT NoCopy varchar2,
14 p_diagnostic_atp IN number DEFAULT 2
15 )
16 IS
17 x_atp_rec MRP_ATP_PUB.atp_rec_typ;
18 x_atp_rec_out MRP_ATP_PUB.atp_rec_typ;
19 x_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
20 x_atp_period MRP_ATP_PUB.ATP_Period_Typ;
21 x_atp_details MRP_ATP_PUB.ATP_Details_Typ;
22 a_session_id NUMBER := x_session_id;
23
24 char_1_null varchar2(2000) := NULL;
25 char_30_null varchar2(30) := NULL;
26 number_null number := null;
27 number_one number := 1;
28 date_null date := null;
29 i NUMBER := 1;
30
31 sql_stmt VARCHAR2(1000);
32 l_count NUMBER := 0;
33 begin
34 order_sch_wb.debug_session_id := x_session_id;
35
36 IF PG_DEBUG in ('Y', 'C') THEN
37 atp_debug('GET_ATP_RESULT: ' || 'got here');
38 select count(*)
39 into l_count
40 from mrp_atp_schedule_temp
41 where session_id = x_session_id
42 and status_flag = 1;
43 atp_debug('GET_ATP_RESULT: sending that many records to ATP : ' || l_count);
44 END IF;
45
46
47 SELECT
48 Rowidtochar(a.ROWID),
49 a.inventory_item_id,
50 a.inventory_item_name,
51 a.organization_id,
52 a.sr_instance_id,
53 Decode(override_flag,'Y',
54 Nvl(a.firm_source_org_id,a.source_organization_id),
55 a.source_organization_id),
56 Decode(override_flag,'Y',
57 Nvl(a.firm_source_org_code,a.source_organization_code),
58 a.source_organization_code),
59 a.order_line_id,
60 a.Scenario_Id,
61 a.Order_Header_Id,
62 a.order_number,
63 a.Calling_Module,
64 a.Customer_Id,
65 a.Customer_Site_Id,
66 a.Destination_Time_Zone,
67 a.quantity_ordered,
68 a.uom_code,
69 Decode(override_flag,'Y',
70 Nvl(a.firm_ship_date,a.requested_ship_date),
71 a.requested_ship_date),
72 Decode(override_flag,'Y',
73 Nvl(a.firm_arrival_date,a.requested_arrival_date),
74 a.requested_arrival_date),
75 date_null, -- a.Earliest_Acceptable_Date,
76 a.Latest_Acceptable_Date,
77 a.Delivery_Lead_Time,
78 a.Freight_Carrier,
79 a.Ship_Method,
80 a.Demand_Class,
81 nvl(a.ship_set_name,
82 a.ship_set_id+Nvl(a.source_organization_id,0)), -- a.Ship_Set_Name,
83 -- When it is put back into the table the name will be used.
84 a.arrival_set_id, --a.Arrival_Set_Name
85 -- we don't append source_org since they can be different
86 -- and we don't need it since we don't have pick sources
87 a.Override_Flag,
88 a.Action,
89 date_null, --a.Ship_Date, ??? scheduled_ship_date
90 number_null, -- a.Available_Quantity,
91 number_null, -- a.Requested_Date_Quantity,
92 date_null, -- a.Group_Ship_Date,
93 date_null, -- a.Group_Arrival_Date,
94 a.Vendor_Id,
95 a.Vendor_Name,
96 a.Vendor_Site_Id,
97 a.Vendor_Site_Name,
98 a.Insert_Flag,
99 number_null, -- a.Error_Code,
100 char_1_null, -- a.Error_Message
101 a.old_source_organization_id,
102 a.old_demand_class,
103 a.atp_lead_time, -- bug 1303240
104 number_one, --substitution_typ_code,
105 number_one, -- REQ_ITEM_DETAIL_FLAG
106 p_diagnostic_atp,
107 a.assignment_set_id,
108 a.sequence_number,
109 a.firm_flag,
110 a.order_line_number,
111 a.option_number,
112 a.shipment_number,
113 a.item_desc,
114 a.old_line_schedule_date,
115 a.old_source_organization_code,
116 a.firm_source_org_id,
117 a.firm_source_org_code,
118 a.firm_ship_date,
119 a.firm_arrival_date,
120 a.ship_method_text,
121 a.ship_set_id,
122 a.arrival_set_id,
123 a.PROJECT_ID,
124 a.TASK_ID,
125 a.PROJECT_NUMBER,
126 a.TASK_NUMBER,
127 a.Top_Model_line_id,
128 a.ATO_Model_Line_Id,
129 a.Parent_line_id,
130 a.Config_item_line_id,
131 a.Validation_Org,
132 a.Component_Sequence_ID,
133 a.Component_Code,
134 a.line_number,
135 a.included_item_flag
136 BULK collect into
137 x_atp_rec.row_id,
138 x_atp_rec.Inventory_Item_Id,
139 x_atp_rec.Inventory_Item_Name,
140 x_atp_rec.organization_id,
141 x_atp_rec.instance_id,
142 x_atp_rec.Source_Organization_Id,
143 x_atp_rec.Source_Organization_Code,
144 x_atp_rec.Identifier,
145 x_atp_rec.Scenario_Id,
146 x_atp_rec.Demand_Source_Header_Id,
147 x_atp_rec.order_number,
148 x_atp_rec.Calling_Module,
149 x_atp_rec.Customer_Id,
150 x_atp_rec.Customer_Site_Id,
151 x_atp_rec.Destination_Time_Zone,
152 x_atp_rec.Quantity_Ordered,
153 x_atp_rec.Quantity_UOM,
154 x_atp_rec.Requested_Ship_Date,
155 x_atp_rec.Requested_Arrival_Date,
156 x_atp_rec.Earliest_Acceptable_Date,
157 x_atp_rec.Latest_Acceptable_Date,
158 x_atp_rec.Delivery_Lead_Time,
159 x_atp_rec.Freight_Carrier,
160 x_atp_rec.Ship_Method,
161 x_atp_rec.Demand_Class,
162 x_atp_rec.Ship_Set_Name,
163 x_atp_rec.Arrival_Set_Name,
164 x_atp_rec.Override_Flag,
165 x_atp_rec.Action,
166 x_atp_rec.Ship_Date,
167 x_atp_rec.Available_Quantity,
168 x_atp_rec.Requested_Date_Quantity,
169 x_atp_rec.Group_Ship_Date,
170 x_atp_rec.Group_Arrival_Date,
171 x_atp_rec.Vendor_Id,
172 x_atp_rec.Vendor_Name,
173 x_atp_rec.Vendor_Site_Id,
174 x_atp_rec.Vendor_Site_Name,
175 x_atp_rec.Insert_Flag,
176 x_atp_rec.Error_Code,
177 x_atp_rec.message,
178 x_atp_rec.old_source_organization_id,
179 x_atp_rec.old_demand_class,
180 x_atp_rec.atp_lead_time, -- bug 1303240
181 x_atp_rec.substitution_typ_code,
182 x_atp_rec.REQ_ITEM_DETAIL_FLAG,
183 x_atp_rec.attribute_02, -- ATP Pegging
184 x_atp_rec.attribute_03,
185 x_atp_rec.sequence_number,
186 x_atp_rec.firm_flag,
187 x_atp_rec.order_line_number,
188 x_atp_rec.option_number,
189 x_atp_rec.shipment_number,
190 x_atp_rec.item_desc,
191 x_atp_rec.old_line_schedule_date,
192 x_atp_rec.old_source_organization_code,
193 x_atp_rec.firm_source_org_id,
194 x_atp_rec.firm_source_org_code,
195 x_atp_rec.firm_ship_date,
196 x_atp_rec.firm_arrival_date,
197 x_atp_rec.ship_method_text,
198 x_atp_rec.ship_set_id,
199 x_atp_rec.arrival_set_id,
200 x_atp_rec.PROJECT_ID,
201 x_atp_rec.TASK_ID,
202 x_atp_rec.PROJECT_NUMBER,
203 x_atp_rec.TASK_NUMBER,
204 x_atp_rec.Top_Model_line_id,
205 x_atp_rec.ATO_Model_Line_Id,
206 x_atp_rec.Parent_line_id,
207 x_atp_rec.Config_item_line_id,
208 x_atp_rec.Validation_Org,
209 x_atp_rec.Component_Sequence_ID,
210 x_atp_rec.Component_Code,
211 x_atp_rec.line_number,
212 x_atp_rec.included_item_flag
213 from mrp_atp_schedule_temp a
214 where a.session_id = x_session_id
215 and a.status_flag = 1
216 order by a.source_organization_code,
217 a.sequence_number,
218 a.arrival_set_id,
219 a.ship_set_id,
220 a.line_number,
221 a.shipment_number,
222 nvl(a.option_number, -1);
223
224 if x_atp_rec.inventory_item_id.count > 0 THEN
225 FOR j IN 1..x_atp_rec.inventory_item_id.count LOOP
226 IF PG_DEBUG in ('Y', 'C') THEN
227 atp_debug('GET_ATP_RESULT: ' || ' ************************************************ ');
228 atp_debug('GET_ATP_RESULT: ' || 'x_atp_table.Inventory_Item_Id: '
229 ||to_char(x_atp_rec.Inventory_Item_Id(j)) );
230 atp_debug('GET_ATP_RESULT: ' || 'x_atp_table.inventory_item_name: '
231 ||x_atp_rec.Inventory_Item_Name(j) );
232 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.organization_id :'
233 || to_char(x_atp_rec.organization_id(j)) );
234 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.instance_id:'
235 || to_char(x_atp_rec.instance_id(j)) );
236 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Source_Organization_Id:'
237 || to_char(x_atp_rec.Source_Organization_Id(j)) );
238 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Source_Organization_Code:'
239 || x_atp_rec.Source_Organization_Code(j));
240 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.order header id:'
241 || to_char(x_atp_rec.demand_source_header_id(j)) );
242 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.order number:'
243 || to_char(x_atp_rec.order_number(j)) );
244 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.order line id:'
245 || to_char(x_atp_rec.identifier(j)) );
246 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.order scenario id:'
247 || to_char(x_atp_rec.scenario_id(j)) );
248 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.calling_module:'
249 || to_char(x_atp_rec.calling_module(j)) );
250 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.cust id:'
251 || to_char(x_atp_rec.customer_id(j)) );
252 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.cust site id:'
253 || to_char(x_atp_rec.customer_site_id(j)) );
254 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Quantity_Ordered:'
255 || to_char(x_atp_rec.Quantity_Ordered(j)) );
256 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Quantity_UOM:'
257 || x_atp_rec.Quantity_UOM(j) );
258 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Requested_Ship_Date:'
259 || to_char(x_atp_rec.Requested_Ship_Date(j)) );
260 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Requested_Arrival_Date:'
261 || to_char(x_atp_rec.Requested_Arrival_Date(j)) );
262 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Latest_Acceptable_Date:'
263 || to_char(x_atp_rec.Latest_Acceptable_Date(j)) );
264 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Delivery_Lead_Time:'
265 || to_char(x_atp_rec.Delivery_Lead_Time(j)) );
266 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Freight_Carrier:'
267 || x_atp_rec.Freight_Carrier(j) );
268 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Ship_Method:'
269 || x_atp_rec.Ship_Method(j) );
270 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Demand_Class:'
271 || x_atp_rec.Demand_Class (j));
272 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Override_Flag:'
273 || x_atp_rec.Override_Flag (j));
274 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Action:'
275 || to_char(x_atp_rec.Action(j)) );
276 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.ship_set_name:'
277 || x_atp_rec.ship_set_name(j) );
278 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.arrival_set_name:'
279 || x_atp_rec.arrival_set_name(j) );
280 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.vendor_id:'
281 || to_char(x_atp_rec.vendor_id(j)) );
282 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.vendor_site_id:'
283 || to_char(x_atp_rec.vendor_site_id(j)) );
284 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Insert_Flag:'
285 || to_char(x_atp_rec.Insert_Flag(j)) );
286 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Insert_Flag:'
287 || to_char(x_atp_rec.old_source_organization_id(j)) );
288 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Insert_Flag:'
289 || x_atp_rec.old_demand_class(j) );
290 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Atp_Lead_time:'
291 || x_atp_rec.Atp_Lead_time(j) );
292 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.substitution_typ_code:'
293 || x_atp_rec.substitution_typ_code(j) );
294 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.REQ_ITEM_DETAIL_FLAG:'
295 || x_atp_rec.REQ_ITEM_DETAIL_FLAG(j) );
296 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.diagnostic_atp:'
297 || x_atp_rec.attribute_02(j) );
298 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.assignment_set: '
299 || x_atp_rec.attribute_03(j) );
300 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.top_model_line_id: '
301 || x_atp_rec.top_model_line_id(j) );
302 atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.ato_model_line_id: '
303 || x_atp_rec.ato_model_line_id(j) );
304 END IF;
305 END LOOP;
306
307 END IF;
308
309 if x_atp_rec.inventory_item_id.count > 0 THEN
310 IF PG_DEBUG in ('Y', 'C') THEN
311 atp_debug('GET_ATP_RESULT: ' || ' Before calling scheduling '||x_atp_rec.inventory_item_id.COUNT);
312 END IF;
313
314 -- 2709847, modified to call MRP procedures instead of MSC procedures as planning manager fix to insert
315 -- Sales Order Line ID is invoked in MRP procedure.
316
317 MSC_SATP_FUNC.new_extend_atp(x_atp_rec,
318 x_atp_rec.inventory_item_id.count,
319 x_return_status);
320
321 IF PG_DEBUG in ('Y', 'C') THEN
322 atp_debug('GET_ATP_RESULT: after new_extend_atp'|| x_return_status);
323 END IF;
324
325 IF x_return_status <> 'E' THEN
326
327 IF commit_flag = 1 THEN
328 MRP_ATP_PUB.call_atp
329 (a_session_id,
330 x_atp_rec,
331 x_atp_rec_out,
332 x_atp_supply_demand,
333 x_atp_period,
334 x_atp_details,
335 x_return_status,
336 x_msg_data,
337 x_msg_count);
338 ELSE
339 MRP_ATP_PUB.call_atp_no_commit
340 (a_session_id,
341 x_atp_rec,
342 x_atp_rec_out,
343 x_atp_supply_demand,
344 x_atp_period,
345 x_atp_details,
346 x_return_status,
347 x_msg_data,
348 x_msg_count);
349 END IF;
350
351 END IF;
352
353 END IF;
354
355 IF PG_DEBUG in ('Y', 'C') THEN
356 atp_debug('GET_ATP_RESULT: ' || 'After calling Scheduling '||x_return_status||' $ '||x_msg_data||' $ '||x_atp_rec_out.inventory_item_id.count);
357 END IF;
358 if x_return_status = 'E' then
359 IF PG_DEBUG in ('Y', 'C') THEN
360 atp_debug('GET_ATP_RESULT: ' || ' err '||x_msg_data||' '||x_msg_count);
361 END IF;
362 end if;
363
364 if x_atp_rec_out.inventory_item_id.count > 0 then
365 IF PG_DEBUG in ('Y', 'C') THEN
366 atp_debug('GET_ATP_RESULT: ' || ' sched date '||x_atp_rec_out.ship_date.count);
367 atp_debug('GET_ATP_RESULT: ' || ' SD '||x_atp_supply_demand.level.count);
368 atp_debug('GET_ATP_RESULT: ' || ' period '||x_atp_period.level.count);
369 atp_debug('GET_ATP_RESULT: ' || ' details '||x_atp_details.level.count);
370 END IF;
371 end if;
372
373 IF call_oe = 1 THEN
374 IF PG_DEBUG in ('Y', 'C') THEN
375 atp_debug('GET_ATP_RESULT: ' || ' before call_oe_api ');
376 END IF;
377 msc_bal_utils.call_oe_api(x_atp_rec_out,
378 x_msg_count,
379 x_msg_data,
380 x_return_status);
381 END IF;
382
383 IF x_return_status = 'E' THEN
384 IF PG_DEBUG in ('Y', 'C') THEN
385 atp_debug('GET_ATP_RESULT: ' || ' error in call_oe_api '||x_msg_data);
386 END IF;
387 END IF;
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 IF PG_DEBUG in ('Y', 'C') THEN
392 atp_debug(' Exception in get_atp_results '||Substr(Sqlerrm,1,100));
393 END IF;
394 x_return_status := 'E';
395 x_msg_data := Substr(Sqlerrm,1,100);
396 END get_atp_result;
397
398 FUNCTION get_label(p_demand_class varchar2) return varchar2 is
399
400 cursor demand_class_desc_c(v_demand_class varchar2) is
401 select distinct meaning
402 from msc_demand_classes
403 where demand_class = v_demand_class;
404
405 l_ret_value varchar2(80);
406 begin
407 if p_demand_class = '-1' OR
408 p_demand_class = 'OTHER' then
409 l_ret_value := 'OTHER';
410 else
411 open demand_class_desc_c(p_demand_class);
412 fetch demand_class_desc_c into l_ret_value;
413 close demand_class_desc_c;
414 end if;
415 return l_ret_value;
416 END get_label;
417
418 FUNCTION get_alloc_rule_variables return NUMBER
419 IS
420 begin
421 IF (MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF = 'Y' AND
422 MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF = 'Y') THEN
423
424 msc_sch_wb.atp_debug ( 'get_alloc_rule_variables : TWO rules are being used');
425
426 msc_sch_wb.atp_debug(' MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF '
427 ||MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF );
428
429 msc_sch_wb.atp_debug('MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF ' ||
430 MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF);
431 return 1; -- I will need to display two allocation rules in the library
432 ELSIF
433 (MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF <> 'Y' AND
434 MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF = 'Y') THEN
435 return 2; -- inly PF rule is used
436 ELSE
437 return 0; -- just single member allocation rule or standard items.
438 END IF;
439
440
441 END;
442
443
444 PROCEDURE delete_lines
445 ( p_session_id NUMBER,
446 p_where_clause varchar2) IS
447 sqlstmt VARCHAR2(100);
448 BEGIN
449 order_sch_wb.debug_session_id := p_session_id;
450
451 IF PG_DEBUG in ('Y', 'C') THEN
452 atp_debug('delete_lines: ' || ' deleting all rows for session_id 1 '||p_session_id);
453 END IF;
454 sqlstmt := 'DELETE FROM mrp_atp_schedule_temp mast '||
455 'WHERE '||p_where_clause||' and mast.session_id = :session_id';
456 execute immediate sqlstmt using p_session_id;
457
458 IF PG_DEBUG in ('Y', 'C') THEN
459 atp_debug('delete_lines: ' || ' deleting all rows for session_id 2 '||p_session_id);
460 END IF;
461
462 DELETE FROM mrp_atp_details_temp madt
463 WHERE madt.session_id = p_session_id;
464 IF PG_DEBUG in ('Y', 'C') THEN
465 atp_debug('delete_lines: ' || ' deleting all rows for session_id 3 '||p_session_id);
466 END IF;
467
468 commit;
469
470 END delete_lines;
471
472 PROCEDURE get_supply_sources_local(
473 x_dblink IN VARCHAR2,
474 x_session_id IN NUMBER,
475 x_sr_instance_id IN NUMBER,
476 x_assignment_set_id IN NUMBER,
477 x_plan_id IN NUMBER,
478 x_calling_inst IN VARCHAR2,
479 x_ret_status OUT NoCopy VARCHAR2,
480 x_error_mesg OUT NoCopy VARCHAR2)
481 IS
482 sql_stmt VARCHAR2(300);
483 l_dynstring VARCHAR2(129);
484 l_calling_module NUMBER;
485 l_customer_site_id NUMBER;
486 l_return_status VARCHAR2(1);
487 l_cursor integer;
488 rows_processed number;
489 DBLINK_NOT_OPEN EXCEPTION;
490 PRAGMA EXCEPTION_INIT(DBLINK_NOT_OPEN, -2081);
491 --bug3610706
492 l_node_id NUMBER;
493 l_rac_count NUMBER;
494
495 cursor cto_related_case (p_session_id NUMBER) IS
496 select 1
497 from mrp_atp_schedule_temp
498 where session_id = p_session_id
499 and ato_model_line_id is NOT NULL
500 and status_flag = 4
501 and rownum = 1;
502
503 cto_exists NUMBER :=0;
504 BEGIN
505
506 IF PG_DEBUG in ('Y', 'C') THEN
507 msc_sch_wb.atp_debug('****Begin Get_Supply_Sources_Local');
508 END IF;
509
510 OPEN cto_related_case(x_session_id);
511 FETCH cto_related_case INTO cto_exists;
512 CLOSE cto_related_case;
513
514 --bug3610706 start
515 BEGIN
516 SELECT count(*)
517 into l_rac_count
518 from gv$instance;
519 IF l_rac_count > 1 then
520 l_node_id := userenv('INSTANCE');
521 IF PG_DEBUG in ('Y', 'C') THEN
522 msc_sch_wb.atp_debug('RAC Instance id is:' || l_node_id);
523 END IF;
524 ELSE
525 l_node_id := null;
526 END IF;
527 EXCEPTION
528 WHEN OTHERS THEN
529 l_node_id := null;
530 END;
531 IF PG_DEBUG in ('Y', 'C') THEN
532 msc_sch_wb.atp_debug('node id is:' || l_node_id);
533 END IF;
534 --bug3610706 end
535 IF PG_DEBUG in ('Y', 'C') THEN
536 msc_sch_wb.atp_debug(' get_supply_sources_local: cto_related_case : ' ||
537 cto_exists);
538 END IF;
539
540
541 IF cto_exists = 1 THEN
542 IF PG_DEBUG in ('Y', 'C') THEN
543 msc_sch_wb.atp_debug(' get_supply_sources_local ' ||
544 ' calling MSC_ATP_UTILS.Update_line_item_properties');
545 END IF;
546
547 MSC_ATP_UTILS.Update_Line_Item_Properties(p_session_id => x_session_id);
548
549 MSC_ATP_CTO. Match_CTO_Lines(p_session_id => x_session_id,
550 p_dblink => x_dblink,
551 p_instance_id => x_sr_instance_id,
552 x_return_status => l_return_status);
553 IF l_return_status <> 'S' THEN
554 IF PG_DEBUG in ('Y', 'C') THEN
555 msc_sch_wb.atp_debug( 'get_supply_sources_local '||
556 ' sth wrong was in MSC_ATP_CTO. Match_CTO_Lines '||
557 ' error is '||
558 l_return_status );
559 END IF;
560 END IF;
561
562 END IF;
563
564
565 IF x_dblink IS NOT NULL AND x_calling_inst = 'APPS' THEN
566 l_dynstring := '@'||x_dblink;
567 END IF;
568 IF PG_DEBUG in ('Y', 'C') THEN
569 msc_sch_wb.atp_debug('get_supply_sources_local: ' || 'l_dynstring : ' || l_dynstring);
570 END IF;
571
572 -- savirine, Sep 06, 2001: added the get_regions call to get the get_regions info which will be used for ATP request.
573
574 l_calling_module := Null;
575 l_customer_site_id := Null;
576
577 IF x_calling_inst = 'APPS' THEN
578 l_calling_module := null;
579 ELSE
580 l_calling_module := 724; -- 724 means the calling module is APS.
581 END IF;
582
583 SELECT DISTINCT mast.customer_site_id
584 INTO l_customer_site_id
585 FROM mrp_atp_schedule_temp mast
586 WHERE mast.session_id = x_session_id
587 AND status_flag = 4;
588
589 IF PG_DEBUG in ('Y', 'C') THEN
590 msc_sch_wb.atp_debug('get_supply_sources_local: ' || 'l_calling_module : ' || l_calling_module);
591 msc_sch_wb.atp_debug('get_supply_sources_local: ' || 'l_customer_site_id : ' || l_customer_site_id);
592 msc_sch_wb.atp_debug('get_supply_sources_local: ' || 'x_assignment_set_id : ' || x_assignment_set_id);
593 END IF;
594
595 MSC_SATP_FUNC.Get_Regions (
596 p_customer_site_id => l_customer_site_id,
597 p_calling_module => NVL(l_calling_module, -99),
598 -- i.e. Source (ERP) or Destination (724)
599 p_instance_id => x_sr_instance_id,
600 p_session_id => x_session_id,
601 p_dblink => x_dblink,
602 x_return_status => l_return_status );
603
604 IF PG_DEBUG in ('Y', 'C') THEN
605 msc_sch_wb.atp_debug('get_supply_sources_local: ' || 'Get_Regions, return status : ' || l_return_status);
606 END IF;
607
608 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
609 -- something wrong so we want to rollback;
610 IF PG_DEBUG in ('Y', 'C') THEN
611 msc_sch_wb.atp_debug('get_supply_sources_local: ' || 'expected error in Call to Get_Regions');
612 END IF;
613 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
614 IF PG_DEBUG in ('Y', 'C') THEN
615 msc_sch_wb.atp_debug('get_supply_sources_local: ' || 'something wrong in Call to Get_Regions');
616 END IF;
617 END IF;
618 --bug3610706 pass the node to the remote call in case of RAC
619 sql_stmt :=
620 ' begin msc_atp_proc.get_supply_sources'||l_dynstring||
621 '(:session_id, :instance_id, :assgn_id, '||
622 ' :plan_id, :inst,:l_ret_status, :l_error_mesg,:node_id); end;';
623
624 IF PG_DEBUG in ('Y', 'C') THEN
625 msc_sch_wb.atp_debug('get_supply_sources_local: ' || 'sql_stmt : ' || sql_stmt);
626 END IF;
627
628 execute immediate sql_stmt using x_session_id, x_sr_instance_id,
629 x_assignment_set_id, x_plan_id, x_calling_inst,
630 OUT x_ret_status, OUT x_error_mesg,l_node_id;
631
632 IF x_dblink IS NOT NULL then
633 l_cursor := dbms_sql.open_cursor;
634 -- mark distributed transaction boundary
635 -- will need to do a manual clean up (commit) of the distributed
636 -- operation, else subsequent operations fail w/ ora-02080 (bug 2218999)
637 commit;
638 DBMS_SQL.PARSE ( l_cursor,
639 'alter session close database link ' ||x_dblink,
640 dbms_sql.native
641 );
642 BEGIN
643 rows_processed := dbms_sql.execute(l_cursor);
644 EXCEPTION
645 WHEN DBLINK_NOT_OPEN THEN
646 IF PG_DEBUG in ('Y', 'C') THEN
647 msc_sch_wb.atp_debug('get_supply_sources_local: ' || 'inside DBLINK_NOT_OPEN');
648 END IF;
649 END;
650 end if;
651
652 IF PG_DEBUG in ('Y', 'C') THEN
653 msc_sch_wb.atp_debug('get_supply_sources_local: ' || ' After calling PATPB');
654 msc_sch_wb.atp_debug('get_supply_sources_local: ' || 'x_error_mesg := ' || x_error_mesg);
655 msc_sch_wb.atp_debug('get_supply_sources_local: ' || 'x_ret_status := ' || x_ret_status);
656 END IF;
657 END get_supply_sources_local;
658
659
660 PROCEDURE get_atp_rule_name (
661 dblink VARCHAR2,
662 item_id NUMBER,
663 org_id NUMBER,
664 sr_instance_id NUMBER,
665 atp_rule_name OUT NoCopy VARCHAR2,
666 inst VARCHAR2)
667 IS
668 sqlstring VARCHAR2(2000);
669 l_dblink VARCHAR2(128) := NULL;
670 l_cursor integer;
671 rows_processed number;
672 DBLINK_NOT_OPEN EXCEPTION;
673 PRAGMA EXCEPTION_INIT(DBLINK_NOT_OPEN, -2081);
674 BEGIN
675 IF dblink IS NOT NULL THEN
676 l_dblink := '@'||dblink;
677 END IF;
678
679 IF inst = order_sch_wb.server THEN
680 -- called from Planning server
681 -- msc_item_id is passed in for item_id
682 sqlstring :=
683 ' SELECT rule_name '||
684 ' FROM msc_atp_rules'||l_dblink||' mar, '||
685 ' msc_system_items'||l_dblink||' msi '||
686 ' WHERE '||
687 ' mar.rule_id = msi.atp_rule_id '||
688 ' AND mar.sr_instance_id = msi.sr_instance_id '||
689 ' AND msi.sr_instance_id = :sr_instance_id '||
690 ' AND msi.organization_id = :org_id '||
691 ' AND msi.inventory_item_id = :item_id '||
692 ' AND msi.plan_id = -1';
693
694 BEGIN
695 EXECUTE IMMEDIATE sqlstring INTO atp_rule_name
696 using sr_instance_id, org_id, item_id;
697
698 IF l_dblink IS NOT NULL then
699 l_cursor := dbms_sql.open_cursor;
700 -- mark distributed transaction boundary
701 -- will need to do a manual clean up (commit) of the distributed
702 -- operation, else subsequent operations fail w/ ora-02080 (bug 2218999)
703 commit;
704 DBMS_SQL.PARSE ( l_cursor,
705 'alter session close database link ' ||l_dblink,
706 dbms_sql.native
707 );
708 BEGIN
709 rows_processed := dbms_sql.execute(l_cursor);
710 EXCEPTION
711 WHEN DBLINK_NOT_OPEN THEN
712 IF PG_DEBUG in ('Y', 'C') THEN
713 msc_sch_wb.atp_debug('get_atp_rule_name: ' || 'inside DBLINK_NOT_OPEN');
714 END IF;
715 END;
716 end if;
717
718 RETURN;
719 EXCEPTION
720 WHEN no_data_found THEN
721 NULL;
722 END;
723
724 sqlstring :=
725 'SELECT rule_name '||
726 ' FROM msc_atp_rules'||l_dblink||' mar, '||
727 ' msc_trading_partners'||l_dblink||' mtp '||
728 ' WHERE '||
729 ' mar.rule_id = mtp.default_atp_rule_id '||
730 ' AND mar.sr_instance_id = mtp.sr_instance_id '||
731 ' AND mtp.sr_tp_id = :org_id '||
732 ' AND mtp.sr_instance_id = :sr_instance_id '||
733 ' AND mtp.partner_type = 3';
734
735 BEGIN
736 EXECUTE IMMEDIATE sqlstring INTO atp_rule_name
737 using org_id, sr_instance_id;
738
739 RETURN;
740 EXCEPTION
741 WHEN no_data_found THEN
742 RETURN;
743 END;
744 ELSE
745 -- called from apps instance
746 BEGIN
747 SELECT rule_name
748 INTO atp_rule_name
749 FROM mtl_atp_rules mar,
750 mtl_system_items msi
751 WHERE
752 mar.rule_id = msi.atp_rule_id
753 AND msi.organization_id = org_id
754 AND msi.inventory_item_id = item_id;
755 RETURN;
756 EXCEPTION
757 WHEN no_data_found THEN
758 NULL;
759 END;
760
761 BEGIN
762 SELECT rule_name
763 INTO atp_rule_name
764 FROM mtl_atp_rules mar,
765 mtl_parameters mp
766 WHERE
767 mar.rule_id = mp.default_atp_rule_id
768 AND mp.organization_id = org_id;
769 RETURN;
770 EXCEPTION
771 WHEN no_data_found THEN
772 RETURN;
773 END;
774 END IF;
775
776 END get_atp_rule_name;
777
778 PROCEDURE get_msc_assign_set(x_dblink VARCHAR2,
779 x_assignment_set_id IN OUT NoCopy NUMBER,
780 x_sr_instance_id NUMBER,
781 x_ret_code OUT NoCopy VARCHAR2,
782 x_err_mesg OUT NoCopy VARCHAR2) IS
783 sqlstring VARCHAR2(500);
784 l_dblink VARCHAR2(128) := NULL;
785 l_msc_assign_set_id NUMBER;
786 l_cursor integer;
787 rows_processed number;
788 DBLINK_NOT_OPEN EXCEPTION;
789 PRAGMA EXCEPTION_INIT(DBLINK_NOT_OPEN, -2081);
790 BEGIN
791
792 x_ret_code := 'S';
793 x_err_mesg := NULL;
794
795 IF x_dblink IS NOT NULL THEN
796 l_dblink := '@'||x_dblink;
797 IF PG_DEBUG in ('Y', 'C') THEN
798 msc_sch_wb.atp_debug(' get_msc_assign_set dblink '||l_dblink);
799 END IF;
800 END IF;
801
802 sqlstring :=
803 ' select assignment_set_id '||
804 ' from msc_assignment_sets'||l_dblink||' '||
805 ' where sr_instance_id = :x_sr_instance_id '||
806 ' and sr_assignment_set_id = :x_assignment_set_id';
807 execute immediate sqlstring INTO l_msc_assign_set_id
808 using x_sr_instance_id, x_assignment_set_id;
809
810 x_assignment_set_id := l_msc_assign_set_id;
811
812 IF PG_DEBUG in ('Y', 'C') THEN
813 atp_debug('get_msc_assign_set: ' ||
814 ' msc assignment_set_id = '||l_msc_assign_set_id);
815 END IF;
816
817 IF l_dblink IS NOT NULL then
818 l_cursor := dbms_sql.open_cursor;
819
820 -- mark distributed transaction boundary
821 -- will need to do a manual clean up (commit) of the distributed
822 -- operation, else subsequent operations fail w/ ora-02080 (bug 2218999)
823 commit;
824 DBMS_SQL.PARSE ( l_cursor,
825 'alter session close database link ' ||l_dblink,
826 dbms_sql.native
827 );
828 BEGIN
829 rows_processed := dbms_sql.execute(l_cursor);
830 EXCEPTION
831 WHEN DBLINK_NOT_OPEN THEN
832 IF PG_DEBUG in ('Y', 'C') THEN
833 atp_debug('get_assignment_set: ' || 'inside DBLINK_NOT_OPEN');
834 END IF;
835 END;
836 END IF;
837
838 EXCEPTION
839 WHEN no_data_found THEN
840 x_ret_code := 'E';
841 x_err_mesg := substr(sqlerrm,1,100);
842
843 END get_msc_assign_set;
844
845 PROCEDURE get_assignment_set (
846 x_dblink VARCHAR2,
847 x_assignment_set_id OUT NoCopy NUMBER,
848 -- This we return what is on the server (MSC)
849 x_assignment_set_name OUT NoCopy VARCHAR2,
850 x_plan_id OUT NoCopy NUMBER,
851 x_plan_name OUT NoCopy VARCHAR2,
852 x_sr_instance_id NUMBER,
853 x_inst VARCHAR2,
854 x_ret_code OUT NoCopy VARCHAR2,
855 x_err_mesg OUT NoCopy VARCHAR2)
856 IS
857 sqlstring VARCHAR2(500);
858 l_dblink VARCHAR2(128) := NULL;
859 l_assign_set_id NUMBER;
860 l_assign_name VARCHAR2(34);
861 l_cursor integer;
862 rows_processed number;
863 DBLINK_NOT_OPEN EXCEPTION;
864 PRAGMA EXCEPTION_INIT(DBLINK_NOT_OPEN, -2081);
865 BEGIN
866 x_ret_code := 'S';
867 x_err_mesg := NULL;
868
869 IF x_dblink IS NOT NULL THEN
870 l_dblink := '@'||x_dblink;
871 IF PG_DEBUG in ('Y', 'C') THEN
872 msc_sch_wb.atp_debug(' get_assignment_set dblink '||l_dblink);
873 END IF;
874 END IF;
875
876
877 IF x_inst = order_sch_wb.apps THEN
878
879 x_assignment_set_id := fnd_profile.value('MRP_ATP_ASSIGN_SET');
880
881 if x_assignment_set_id is null then
882
883 get_profile('MSC_ATP_ASSIGN_SET', x_assignment_set_id);
884
885 sqlstring :=
886 ' SELECT assignment_set_name '||
887 ' FROM msc_assignment_sets' ||l_dblink||' '||
888 ' WHERE assignment_set_id = :x_assignment_set_id '||
889 ' AND sr_instance_id = :x_sr_instance_id';
890 execute immediate sqlstring INTO l_assign_name
891 using x_assignment_set_id, x_sr_instance_id;
892 x_assignment_set_name := l_assign_name;
893
894 IF l_dblink IS NOT NULL then
895 l_cursor := dbms_sql.open_cursor;
896 -- mark distributed transaction boundary
897 -- will need to do a manual clean up (commit) of the distributed
898 -- operation, else subsequent operations fail w/ ora-02080 (bug 2218999)
899 commit;
900 DBMS_SQL.PARSE ( l_cursor,
901 'alter session close database link ' ||l_dblink,
902 dbms_sql.native
903 );
904 BEGIN
905 rows_processed := dbms_sql.execute(l_cursor);
906 EXCEPTION
907 WHEN DBLINK_NOT_OPEN THEN
908 IF PG_DEBUG in ('Y', 'C') THEN
909 msc_sch_wb.atp_debug('get_assignment_set: ' || 'inside DBLINK_NOT_OPEN');
910 END IF;
911 END;
912 end if;
913
914 IF x_assignment_set_name IS NULL THEN
915 -- could be using a assgn set in a diff instance.
916 x_assignment_set_id := NULL;
917 END IF;
918
919 else
920
921 IF PG_DEBUG in ('Y', 'C') THEN
922 atp_debug('get_assignment_set: ' || ' apps x_assignment_set_id = '||x_assignment_set_id);
923 END IF;
924 SELECT mas.assignment_set_name
925 INTO x_assignment_set_name
926 FROM mrp_assignment_sets mas
927 WHERE mas.assignment_set_id = x_assignment_set_id;
928
929 -- Bug # 2744339
930 -- end if; -- check mrp assignment set for region level sourcing
931
932 sqlstring :=
933 ' select assignment_set_id '||
934 ' from msc_assignment_sets'||l_dblink||' '||
935 ' where sr_instance_id = :x_sr_instance_id '||
936 ' and sr_assignment_set_id = :x_assignment_set_id';
937 execute immediate sqlstring INTO l_assign_set_id
938 using x_sr_instance_id, x_assignment_set_id;
939 x_assignment_set_id := l_assign_set_id;
940
941
942 END IF;
943
944 IF PG_DEBUG in ('Y', 'C') THEN
945 atp_debug('get_assignment_set: ' || ' apps x_assignment_set_id = '||x_assignment_set_id);
946 END IF;
947
948 ELSE
949 -- Server.
950
951 x_assignment_set_id := fnd_profile.value('MSC_ATP_ASSIGN_SET');
952 IF PG_DEBUG in ('Y', 'C') THEN
953 atp_debug('get_assignment_set: ' || ' server x_assignment_set_id = '||x_assignment_set_id);
954 END IF;
955 IF x_assignment_set_id IS NULL THEN
956
957 SELECT mas.assignment_set_id,mas.assignment_set_name
958 INTO x_assignment_set_id, x_assignment_set_name
959 FROM msc_apps_instances mai, msc_assignment_sets mas
960 WHERE mai.instance_id = x_sr_instance_id
961 AND mas.assignment_set_id = mai.assignment_set_id
962 AND mas.sr_instance_id = mai.instance_id;
963
964 IF PG_DEBUG in ('Y', 'C') THEN
965 atp_debug('get_assignment_set: ' || ' server from msc_apps...x_assignment_set_id = '||x_assignment_set_id);
966 END IF;
967 ELSE
968 SELECT mas.assignment_set_name
969 INTO x_assignment_set_name
970 FROM msc_assignment_sets mas
971 WHERE mas.assignment_set_id = x_assignment_set_id
972 AND mas.sr_instance_id = x_sr_instance_id;
973
974 IF x_assignment_set_name IS NULL THEN
975 -- could be using a assgn set in a diff instance.
976 x_assignment_set_id := NULL;
977 END IF;
978 END IF;
979 END IF;
980
981 EXCEPTION
982 WHEN no_data_found THEN
983 x_ret_code := 'E';
984 x_err_mesg := substr(sqlerrm,1,100);
985 END get_assignment_set;
986
987 PROCEDURE atp_debug(buf IN VARCHAR2) IS
988 BEGIN
989 --IF order_sch_wb.mr_debug = 'Y' THEN
990 IF order_sch_wb.mr_debug in ('Y','C') THEN
991 IF order_sch_wb.file_or_terminal = 1 THEN
992 mrp_timing(buf);
993 ELSE
994 -- dbms_output.put_line(buf);
995 null;
996 END IF;
997 END IF;
998 END atp_debug;
999
1000 PROCEDURE MRP_TIMING(buf IN VARCHAR2)
1001 IS
1002 fname utl_file.file_type ;
1003 BEGIN
1004 IF (utl_file.is_open(fname)) THEN
1005 utl_file.put(fname, 'atp_session: '||order_sch_wb.debug_session_id||' '||buf);
1006 utl_file.fflush(fname);
1007 utl_file.fclose(fname);
1008 ELSE
1009 if order_sch_wb.file_dir is null then
1010 --select ltrim(rtrim(substr(value, instr(value,',',-1,1)+1)))
1011 -- into order_sch_wb.file_dir from v$parameter where name= 'utl_file_dir';
1012 /*bug 3374136 changes start*/
1013 select ltrim(rtrim(value)) into order_sch_wb.file_dir from
1014 (select value from v$parameter2 where name='utl_file_dir' order by rownum desc)
1015 where rownum <2;
1016 /*bug 3374136 changes end*/
1017 end if;
1018 -- dbms_output.put_line('dir '||order_sch_wb.file_dir||order_sch_wb.debug_session_id);
1019
1020 fname := utl_file.fopen(order_sch_wb.file_dir,'session-'||order_sch_wb.debug_session_id,'a');
1021 utl_file.put(fname, buf);
1022 utl_file.fflush(fname);
1023 utl_file.fclose(fname);
1024 END IF;
1025 return;
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 -- dbms_output.put_line('Exception in mrp_timing '||Sqlerrm);
1029 return;
1030 END MRP_TIMING;
1031
1032
1033 PROCEDURE get_period_atp_strings(
1034 p_is_allocated BOOLEAN,
1035 p_session_id NUMBER,
1036 p_old_session_id number,
1037 p_dmd_flag number,
1038 p_end_pegging_id number,
1039 p_pegging_id NUMBER,
1040 p_organization_id NUMBER,
1041 p_sr_instance_id NUMBER,
1042 p_inst VARCHAR2,
1043 p_supply_str OUT NoCopy VARCHAR2,
1044 p_demand_str OUT NoCopy VARCHAR2,
1045 p_bkd_demand_str OUT NoCopy VARCHAR2,
1046 p_net_atp_str OUT NoCopy VARCHAR2,
1047 p_cum_atp_str OUT NoCopy VARCHAR2,
1048 p_row_hdr_str OUT NoCopy VARCHAR2,
1049 p_date_str OUT NoCopy VARCHAR2,
1050 p_week_str OUT NoCopy VARCHAR2,
1051 p_period_str OUT NoCopy VARCHAR2
1052 ) is
1053
1054 v_inv_item varchar2(40);
1055 v_org_code varchar2(7);
1056 v_resource_code varchar2(10);
1057 --p_inv_item varchar2(40);
1058 --p_org_code varchar2(7);
1059 l_pivot_hdr VARCHAR2(400); --bug 2246200
1060 p_resource_code varchar2(10);
1061 v_total_supply_qty number;
1062 v_total_demand_qty number;
1063 v_week_start_date date;
1064 v_week_end_date date;
1065 v_period_start_date date;
1066 v_period_end_date date;
1067 v_wk_start_date date;
1068 v_pr_start_date date;
1069 v_period_qty number;
1070 v_cumulative_qty number;
1071 l_old_cum number := 0;
1072
1073 rec_cnt number;
1074 day_gap number;
1075
1076 x_atp_period_string order_sch_wb.atp_period_string_typ;
1077
1078 -- The week and period is calculated based on the source org of the line.
1079 -- We may need to do it for the org in which that pegging node is
1080 cursor wk_cur is select b.week_start_date
1081 from mtl_parameters p, bom_cal_week_start_dates b
1082 where p.calendar_exception_set_id = b.exception_set_id
1083 and p.calendar_code = b.calendar_code
1084 and p.organization_id = p_organization_id
1085 and b.week_start_date > v_week_start_date
1086 and b.week_start_date <= v_week_end_date;
1087
1088 cursor pr_cur is select b.period_start_date
1089 from mtl_parameters p, bom_period_start_dates b
1090 where p.calendar_exception_set_id = b.exception_set_id
1091 and p.calendar_code = b.calendar_code
1092 and p.organization_id = p_organization_id
1093 and b.period_start_date > v_period_start_date
1094 and b.period_start_date <= v_period_end_date;
1095
1096 cursor msc_wk_cur is
1097 select b.week_start_date
1098 from msc_trading_partners p, msc_cal_week_start_dates b
1099 where p.calendar_exception_set_id = b.exception_set_id
1100 AND p.sr_instance_id = p_sr_instance_id
1101 AND b.sr_instance_id = p.sr_instance_id
1102 and p.calendar_code = b.calendar_code
1103 and p.sr_tp_id = p_organization_id
1104 AND p.partner_type = 3
1105 and b.week_start_date > v_week_start_date
1106 and b.week_start_date <= v_week_end_date;
1107
1108 cursor msc_pr_cur is
1109 select b.period_start_date
1110 from msc_trading_partners p, MSC_period_START_DATES b
1111 where p.calendar_exception_set_id = b.exception_set_id
1112 AND p.sr_instance_id = p_sr_instance_id
1113 AND b.sr_instance_id = p.sr_instance_id
1114 and p.calendar_code = b.calendar_code
1115 and p.sr_tp_id = p_organization_id
1116 AND p.partner_type = 3
1117 and b.period_start_date > v_period_start_date
1118 and b.period_start_date <= v_period_end_date;
1119
1120 cursor get_item_name is
1121 SELECT DISTINCT
1122 inventory_item_name||order_sch_wb.delim||owb_tree.lookups(5) -- 'Item'
1123 ||order_sch_wb.delim||source_organization_code
1124 FROM mrp_atp_schedule_temp
1125 WHERE end_pegging_id = p_end_pegging_id;
1126
1127 BEGIN
1128
1129 p_row_hdr_str := NULL;
1130 p_date_str := 'CH';
1131 p_week_str := 'CH';
1132 p_period_str := 'CH';
1133 p_net_atp_str := NULL;
1134 p_cum_atp_str := NULL;
1135 p_bkd_demand_str := NULL;
1136
1137
1138 msc_owb_tree.get_lookups;
1139
1140 IF NOT p_is_allocated THEN
1141
1142 IF p_pegging_id IS NOT NULL THEN
1143 SELECT Decode(department_id, NULL,
1144 Decode(supplier_id, NULL,
1145 inventory_item_name||order_sch_wb.delim||owb_tree.lookups(5)||order_sch_wb.delim||organization_code,
1146 -- 'Item'
1147 supplier_name||order_sch_wb.delim||owb_tree.lookups(17)||order_sch_wb.delim||supplier_site_name),
1148 department_code||order_sch_wb.delim||Decode(resource_code, NULL, owb_tree.lookups(18),owb_tree.lookups(6))||order_sch_wb.delim||Decode(resource_code, NULL, ' ', resource_code))
1149 -- blank above is so that pivot table will get some value to display
1150 -- for line. otherwise following values shift left.
1151 INTO l_pivot_hdr
1152 FROM mrp_atp_details_temp
1153 WHERE pegging_id = p_pegging_id
1154 AND record_type = 3;
1155 END IF;
1156
1157 IF l_pivot_hdr IS NULL THEN
1158 -- It will be null if pegging_id is not specified or if it is specified
1159 -- and it is an item.
1160
1161 open get_item_name;
1162 fetch get_item_name into l_pivot_hdr;
1163 close get_item_name;
1164
1165 END IF;
1166
1167 select
1168 Nvl(total_supply_quantity,0),
1169 Nvl(total_demand_quantity,0),
1170 period_start_date,
1171 Nvl(period_end_date,period_start_date),
1172 -- hack to avoid NULL
1173 Nvl(period_quantity,0),
1174 Nvl(cumulative_quantity,0),
1175 Nvl(total_bucketed_demand_quantity,0)
1176 bulk collect INTO
1177 x_atp_period_string.Total_Supply_Quantity,
1178 x_atp_period_string.Total_Demand_Quantity,
1179 x_atp_period_string.Period_Start_Date,
1180 x_atp_period_string.Period_End_Date,
1181 x_atp_period_string.Period_Quantity,
1182 x_atp_period_string.Cumulative_Quantity,
1183 x_atp_period_string.Bucketed_Quantity
1184 FROM MRP_ATP_DETAILS_TEMP
1185 WHERE
1186 record_type = 1
1187 AND (( p_pegging_id IS NULL and end_pegging_id = p_end_pegging_id)
1188 OR pegging_id = p_pegging_id)
1189 order by period_start_date;
1190
1191 IF PG_DEBUG in ('Y', 'C') THEN
1192 msc_sch_wb.atp_debug('get_period_atp_strings: ' || ' count '||x_atp_period_string.total_supply_quantity.COUNT);
1193 END IF;
1194
1195
1196 ELSE -- if allocated
1197
1198 if p_dmd_flag = 0 then
1199 select
1200 Nvl(a.total_supply_quantity,0),
1201 Nvl(a.total_demand_quantity,0),
1202 b.period_start_date,
1203 Nvl(b.period_end_date,b.period_start_date),
1204 -- hack to avoid NULL
1205 Nvl(a.period_quantity,0),
1206 Nvl(a.cumulative_quantity,0),
1207 Nvl(a.total_bucketed_demand_quantity,0)
1208 bulk collect INTO
1209 x_atp_period_string.Total_Supply_Quantity,
1210 x_atp_period_string.Total_Demand_Quantity,
1211 x_atp_period_string.Period_Start_Date,
1212 x_atp_period_string.Period_End_Date,
1213 x_atp_period_string.Period_Quantity,
1214 x_atp_period_string.Cumulative_Quantity,
1215 x_atp_period_string.Bucketed_Quantity
1216 FROM MRP_ATP_DETAILS_TEMP a,
1217 MRP_ATP_DETAILS_TEMP b
1218 WHERE
1219 a.record_type (+) = 1
1220 and a.session_id (+) = p_session_id
1221 and b.session_id = p_old_session_id
1222 and b.record_type = 1
1223 and a.period_start_date(+) = b.period_start_date
1224 order by b.period_start_date;
1225 else
1226 select
1227 Nvl(total_supply_quantity,0),
1228 Nvl(total_demand_quantity,0),
1229 period_start_date,
1230 Nvl(period_end_date,period_start_date),
1231 -- hack to avoid NULL
1232 Nvl(period_quantity,0),
1233 Nvl(cumulative_quantity,0),
1234 Nvl(total_bucketed_demand_quantity,0)
1235 bulk collect INTO
1236 x_atp_period_string.Total_Supply_Quantity,
1237 x_atp_period_string.Total_Demand_Quantity,
1238 x_atp_period_string.Period_Start_Date,
1239 x_atp_period_string.Period_End_Date,
1240 x_atp_period_string.Period_Quantity,
1241 x_atp_period_string.Cumulative_Quantity,
1242 x_atp_period_string.Bucketed_Quantity
1243 FROM MRP_ATP_DETAILS_TEMP
1244 WHERE
1245 record_type = 1
1246 and session_id = p_session_id
1247 order by period_start_date;
1248 end if;
1249
1250
1251 IF PG_DEBUG in ('Y', 'C') THEN
1252 msc_sch_wb.atp_debug('get_period_atp_strings: ' || ' count '||x_atp_period_string.total_supply_quantity.COUNT);
1253 END IF;
1254
1255 END IF; -- if not allocated
1256
1257 IF x_atp_period_string.total_supply_quantity.COUNT > 0 THEN
1258
1259 p_row_hdr_str := 'RowHeader';
1260 p_date_str := p_date_str||order_sch_wb.delim||'Dy';
1261 p_week_str := p_week_str||order_sch_wb.delim||'Wk';
1262 p_period_str := p_period_str||order_sch_wb.delim||'Pr';
1263 p_net_atp_str := 'GD'||order_sch_wb.delim||'New-End';
1264 p_cum_atp_str := 'GD'||order_sch_wb.delim||'New-End';
1265 p_supply_str := 'GD'||order_sch_wb.delim||'New-End';
1266 p_demand_str := 'GD'||order_sch_wb.delim||'New-End';
1267 p_bkd_demand_str := 'GD'||order_sch_wb.delim||'New-End';
1268
1269 v_week_start_date := x_atp_period_string.period_start_date(1);
1270 v_period_start_date := x_atp_period_string.period_start_date(1);
1271 v_week_end_date := x_atp_period_string.period_end_date(x_atp_period_string.period_end_date.count);
1272 v_period_end_date := v_week_end_date;
1273 IF order_sch_wb.mr_debug = 'Y' THEN
1274 IF PG_DEBUG in ('Y', 'C') THEN
1275 msc_sch_wb.atp_debug('get_period_atp_strings: ' || '$$$ '||v_week_start_date||' '||v_week_end_date||' '||v_period_start_date||' '||v_period_end_date);
1276 END IF;
1277 END IF;
1278
1279
1280 FOR j IN 1..x_atp_period_string.total_supply_quantity.COUNT loop
1281 p_supply_str := p_supply_str||order_sch_wb.delim||Rtrim(To_char(x_atp_period_string.total_supply_quantity(j),order_sch_wb.mrn_canonical_num),'.');
1282 p_demand_str := p_demand_str||order_sch_wb.delim||Rtrim(To_char(x_atp_period_string.total_demand_quantity(j),order_sch_wb.mrn_canonical_num),'.');
1283 p_bkd_demand_str := p_bkd_demand_str||order_sch_wb.delim||Rtrim(To_char(x_atp_period_string.bucketed_quantity(j),order_sch_wb.mrn_canonical_num),'.');
1284 p_net_atp_str := p_net_atp_str||order_sch_wb.delim||Rtrim(To_char(x_atp_period_string.period_quantity(j),order_sch_wb.mrn_canonical_num),'.');
1285
1286 if (p_dmd_flag = 0 ) AND (x_atp_period_string.cumulative_quantity(j) = 0) THEN
1287 p_cum_atp_str := p_cum_atp_str||order_sch_wb.delim||Rtrim(To_char(l_old_cum,order_sch_wb.mrn_canonical_num),'.');
1288 else
1289 p_cum_atp_str := p_cum_atp_str||order_sch_wb.delim||Rtrim(To_char(x_atp_period_string.cumulative_quantity(j),order_sch_wb.mrn_canonical_num),'.');
1290 l_old_cum := x_atp_period_string.cumulative_quantity(j);
1291 end if;
1292
1293 p_date_str := p_date_str||order_sch_wb.delim||To_char(x_atp_period_string.period_start_date(j),order_sch_wb.MRD_CANONICAL_DATE);
1294 -- dbms_output.put_line(x_atp_period_string.period_start_date(j));
1295 IF x_atp_period_string.period_start_date(j) <>
1296 x_atp_period_string.period_end_date(j) THEN
1297 day_gap := x_atp_period_string.period_end_date(j) -
1298 x_atp_period_string.period_start_date(j);
1299 for i in 1..day_gap loop
1300 p_supply_str := p_supply_str||order_sch_wb.delim||0;
1301 p_demand_str := p_demand_str||order_sch_wb.delim||0;
1302 p_bkd_demand_str := p_bkd_demand_str||order_sch_wb.delim||0;
1303 p_net_atp_str := p_net_atp_str||order_sch_wb.delim||0;
1304 p_cum_atp_str := p_cum_atp_str||order_sch_wb.delim||Rtrim(To_char(l_old_cum,order_sch_wb.mrn_canonical_num),'.');
1305 x_atp_period_string.period_start_date(j) := x_atp_period_string.period_start_date(j) + 1;
1306 p_date_str := p_date_str||order_sch_wb.delim||To_char(x_atp_period_string.period_start_date(j),order_sch_wb.MRD_CANONICAL_DATE);
1307 -- dbms_output.put_line('!! '||x_atp_period_string.period_start_date(j));
1308 END LOOP;
1309 END IF;
1310 END LOOP;
1311
1312 p_week_str := p_week_str||order_sch_wb.delim||To_char(v_week_start_date,order_sch_wb.mrd_canonical_date);
1313 IF p_inst = 'SERVER' THEN
1314 open msc_wk_cur;
1315 LOOP
1316 fetch msc_wk_cur into v_wk_start_date;
1317 EXIT WHEN msc_wk_cur%NOTFOUND;
1318 p_week_str := p_week_str||order_sch_wb.delim||To_char(v_wk_start_date,order_sch_wb.MRD_CANONICAL_DATE);
1319 end loop;
1320 close msc_wk_cur;
1321 ELSE
1322 open wk_cur;
1323 LOOP
1324 fetch wk_cur into v_wk_start_date;
1325 EXIT WHEN wk_cur%NOTFOUND;
1326 p_week_str := p_week_str||order_sch_wb.delim||To_char(v_wk_start_date,order_sch_wb.MRD_CANONICAL_DATE);
1327 end loop;
1328 close wk_cur;
1329 END IF;
1330
1331
1332 p_period_str := p_period_str||order_sch_wb.delim||To_char(v_period_start_date,order_sch_wb.MRD_CANONICAL_DATE);
1333 IF p_inst = 'SERVER' THEN
1334 open msc_pr_cur;
1335 loop
1336 fetch msc_pr_cur into v_pr_start_date;
1337 EXIT WHEN msc_pr_cur%NOTFOUND;
1338 p_period_str := p_period_str||order_sch_wb.delim||To_char(v_pr_start_date,order_sch_wb.MRD_CANONICAL_DATE);
1339 end loop;
1340 close msc_pr_cur;
1341 ELSE
1342 open pr_cur;
1343 loop
1344 fetch pr_cur into v_pr_start_date;
1345 EXIT WHEN pr_cur%NOTFOUND;
1346 p_period_str := p_period_str||order_sch_wb.delim||To_char(v_pr_start_date,order_sch_wb.MRD_CANONICAL_DATE);
1347 end loop;
1348 close pr_cur;
1349 END IF;
1350 p_date_str := p_date_str||order_sch_wb.delim||'End';
1351 p_week_str := p_week_str||order_sch_wb.delim||'End';
1352 p_period_str := p_period_str||order_sch_wb.delim||'End';
1353
1354 IF NOT p_is_allocated THEN
1355 p_row_hdr_str := p_row_hdr_str||order_sch_wb.delim||l_pivot_hdr
1356 ||order_sch_wb.delim||owb_tree.lookups(13)||order_sch_wb.delim||
1357 owb_tree.lookups(14)||order_sch_wb.delim||owb_tree.lookups(27)||order_sch_wb.delim||owb_tree.lookups(15)||order_sch_wb.delim||
1358 owb_tree.lookups(16)||order_sch_wb.delim||'End';
1359 ELSE
1360 IF p_pegging_id is null THEN
1361 p_row_hdr_str:= owb_tree.lookups(6)||order_sch_wb.delim;
1362 ELSE
1363 p_row_hdr_str:= owb_tree.lookups(5)||order_sch_wb.delim;
1364 END IF;
1365 p_row_hdr_str := p_row_hdr_str||
1366 owb_tree.lookups(13)||order_sch_wb.delim||
1367 owb_tree.lookups(14)||order_sch_wb.delim||owb_tree.lookups(27)||order_sch_wb.delim||
1368 owb_tree.lookups(15)||order_sch_wb.delim||
1369 owb_tree.lookups(16)||order_sch_wb.delim||'End';
1370 END IF;
1371
1372 p_supply_str := p_supply_str||order_sch_wb.delim||'End';
1373 p_demand_str := p_demand_str||order_sch_wb.delim||'End';
1374 p_bkd_demand_str := p_bkd_demand_str||order_sch_wb.delim||'End';
1375 p_net_atp_str := p_net_atp_str||order_sch_wb.delim||'End';
1376 p_cum_atp_str := p_cum_atp_str||order_sch_wb.delim||'End';
1377 END IF;
1378 EXCEPTION
1379 WHEN OTHERS THEN
1380 IF PG_DEBUG in ('Y', 'C') THEN
1381 msc_sch_wb.atp_debug('get_period_atp_strings: ' || ' excp in get_period_strings '||substr(Sqlerrm, 1, 100));
1382 END IF;
1383
1384 IF wk_cur%isopen THEN
1385 CLOSE wk_cur;
1386 END IF;
1387 IF msc_wk_cur%isopen THEN
1388 CLOSE msc_wk_cur;
1389 END IF;
1390 IF pr_cur%isopen THEN
1391 CLOSE pr_cur;
1392 END IF;
1393 IF msc_pr_cur%isopen THEN
1394 CLOSE msc_pr_cur;
1395 END IF;
1396
1397 END get_period_atp_strings;
1398
1399 PROCEDURE cleanup_data (p_session_id in number) IS
1400 begin
1401 delete from mrp_atp_schedule_temp
1402 where status_flag = 1
1403 and session_id = p_session_id;
1404
1405 update mrp_atp_schedule_temp
1406 set status_flag = 1
1407 where session_id = p_session_id
1408 and status_flag = 2;
1409 end cleanup_data;
1410
1411
1412 PROCEDURE calc_exceptions(
1413 p_session_id IN NUMBER,
1414 x_return_status OUT NoCopy VARCHAR2,
1415 x_msg_data OUT NoCopy VARCHAR2,
1416 x_msg_count OUT NoCopy NUMBER
1417 )
1418 IS
1419 -- PRAGMA AUTONOMOUS_TRANSACTION;
1420 BEGIN
1421 x_return_status := 'S';
1422
1423 IF PG_DEBUG in ('Y', 'C') THEN
1424 msc_sch_wb.atp_debug(' Inside calc_exceptions '||p_session_id);
1425 END IF;
1426
1427 UPDATE mrp_atp_schedule_temp mast
1428 set
1429 mast.exception1 = Decode(error_code,53,1,52,1,100,1,0),
1430 mast.exception2 = Decode(error_code,0,
1431 Decode(Sign(trunc(mast.scheduled_ship_date) -
1432 Nvl(trunc(mast.old_line_schedule_date),trunc(mast.scheduled_ship_date)))
1433 ,1,1,0),
1434 0), -- later than old sched date
1435 mast.exception3 = Decode(error_code,0,
1436 Decode(Sign(trunc(mast.scheduled_ship_date) +
1437 Decode(trunc(mast.requested_ship_date),NULL,Nvl(mast.delivery_lead_time,0),0)
1438 -trunc(mast.promise_date)),1,1,0), 0),
1439 -- later than promise date. Consider sched ship/arrival date depending on whether
1440 -- requested date was ship/arrival.
1441 mast.exception4= Decode(error_code,0,
1442 Decode(Sign(trunc(mast.SCHEDULED_SHIP_DATE) + Decode(trunc(mast.requested_ship_date),NULL,
1443 Nvl(mast.delivery_lead_time,0),0)
1444 - NVL(trunc(mast.requested_ship_date),trunc(mast.requested_arrival_date))),1,1,0),
1445 0), -- later than request date
1446 mast.exception5 = 0, -- insufficient margin
1447 mast.exception6 = Decode(error_code,0,
1448 Decode(substr(mast.SOURCE_ORGANIZATION_CODE, instr(mast.SOURCE_ORGANIZATION_CODE,':')+1,3),
1449 Nvl(mast.OLD_SOURCE_ORGANIZATION_CODE,
1450 substr(mast.SOURCE_ORGANIZATION_CODE, instr(mast.SOURCE_ORGANIZATION_CODE,':')+1,3)),0,1),
1451 0),
1452 mast.exception7 = Decode(error_code,0,0,52,0,53,0,100,0,NULL,0,1),
1453 mast.exception8 = 0,
1454 mast.exception9 = 0,
1455 mast.exception10 = 0,
1456 mast.exception11 = 0,
1457 mast.exception12 = 0,
1458 mast.exception13 = 0,
1459 mast.exception14 = 0,
1460 mast.exception15 = 0
1461 WHERE session_id = p_session_id
1462 AND scenario_id = 1
1463 AND status_flag = 2;
1464
1465 IF PG_DEBUG in ('Y', 'C') THEN
1466 msc_sch_wb.atp_debug(' After update in calc_exceptions ');
1467 END IF;
1468
1469 COMMIT;
1470
1471 EXCEPTION
1472 WHEN OTHERS THEN
1473 IF PG_DEBUG in ('Y', 'C') THEN
1474 atp_debug('calc_exceptions: ' || ' exception in calc_excep - '||substr(sqlerrm,1,100));
1475 END IF;
1476 x_return_status := 'E';
1477
1478 END calc_exceptions;
1479
1480 FUNCTION get_supply_demand_source_name
1481 (
1482 organization_id IN NUMBER,
1483 supply_demand_source_type IN NUMBER,
1484 supply_demand_source_id IN NUMBER
1485 ) RETURN VARCHAR2 IS
1486 supply_demand_source_name VARCHAR2(200);
1487 BEGIN
1488 if supply_demand_source_type = 1 then
1489 SELECT SEGMENT1
1490 INTO supply_demand_source_name
1491 FROM PO_HEADERS
1492 WHERE PO_HEADER_ID=supply_demand_source_id;
1493 elsif supply_demand_source_type = 2 then
1494 SELECT CONCATENATED_SEGMENTS
1495 INTO supply_demand_source_name
1496 FROM mtl_sales_orders_kfv
1497 WHERE SALES_ORDER_ID = supply_demand_source_id;
1498 elsif supply_demand_source_type = 3 THEN
1499 SELECT CONCATENATED_SEGMENTS
1500 INTO supply_demand_source_name
1501 FROM gl_code_combinations_kfv
1502 where CHART_OF_ACCOUNTS_ID = order_sch_wb.PARAMETER_CHART_OF_ACCOUNTS_ID
1503 and CODE_COMBINATION_ID = supply_demand_source_id;
1504 elsif (supply_demand_source_type = 4) or (supply_demand_source_type = 5) then
1505 SELECT WIP_ENTITY_NAME
1506 INTO supply_demand_source_name
1507 FROM WIP_ENTITIES
1508 WHERE WIP_ENTITY_ID=supply_demand_source_id;
1509 elsif supply_demand_source_type = 6 then
1510 SELECT CONCATENATED_SEGMENTS
1511 INTO supply_demand_source_name
1512 FROM mtl_generic_dispositions_kfv
1513 WHERE ORGANIZATION_ID = ORGANIZATION_ID
1514 AND DISPOSITION_ID = supply_demand_source_id;
1515 elsif supply_demand_source_type = 8 then
1516 SELECT SHIPMENT_NUM
1517 INTO supply_demand_source_name
1518 FROM RCV_SHIPMENT_HEADERS
1519 WHERE SHIPMENT_HEADER_ID=supply_demand_source_id;
1520 elsif supply_demand_source_type = 9 THEN
1521 SELECT SCHEDULE_DESIGNATOR
1522 INTO supply_demand_source_name
1523 FROM MRP_SCHEDULE_DATES
1524 WHERE MPS_TRANSACTION_ID=supply_demand_source_id
1525 AND SCHEDULE_LEVEL = 2
1526 AND SUPPLY_DEMAND_TYPE = 2;
1527 elsif supply_demand_source_type = 10 then
1528 SELECT SEGMENT1
1529 INTO supply_demand_source_name
1530 FROM PO_REQUISITION_HEADERS
1531 WHERE REQUISITION_HEADER_ID=supply_demand_source_id;
1532 elsif supply_demand_source_type = 11 THEN
1533 NULL;
1534 -- If it is resource supply, there is no identifier
1535 -- :SD_DETAIL.sd_type := :PARAMETER.resource_supply;
1536 elsif supply_demand_source_type is not null THEN
1537 supply_demand_source_name := order_sch_wb.form_field_C_COLUMN1;
1538 end if;
1539
1540 RETURN supply_demand_source_name;
1541
1542 END get_supply_demand_source_name;
1543
1544 PROCEDURE pipe_utility(
1545 p_session_id IN NUMBER,
1546 p_command IN VARCHAR2,
1547 p_message IN OUT NoCopy VARCHAR2,
1548 p_message_count OUT NoCopy NUMBER, -- Right now just 0 or 1
1549 x_return_status OUT NoCopy VARCHAR2,
1550 x_msg_data OUT NoCopy VARCHAR2,
1551 x_msg_count OUT NoCopy NUMBER
1552 )
1553 IS
1554 ret NUMBER;
1555 empty_pipe EXCEPTION;
1556 PRAGMA EXCEPTION_INIT (EMPTY_PIPE, -6556);
1557 BEGIN
1558 x_return_status := 'S';
1559 x_msg_data := NULL;
1560 x_msg_count := NULL;
1561
1562 IF p_command = 'CREATE' THEN
1563 ret := DBMS_PIPE.CREATE_PIPE
1564 (pipename => 'session-'||p_session_id,
1565 maxpipesize => 5000,
1566 private => FALSE);
1567 IF ret <> 0 THEN
1568 IF PG_DEBUG in ('Y', 'C') THEN
1569 atp_debug('pipe_utility: ' || ' Unable to open pipe ');
1570 END IF;
1571 x_return_status := 'E';
1572 RETURN;
1573 END IF;
1574 dbms_pipe.reset_buffer; -- good to reset it to clear things
1575 ELSIF p_command = 'REMOVE' THEN
1576 ret := dbms_pipe.remove_pipe('session-'||p_session_id);
1577 IF ret <> 0 THEN
1578 IF PG_DEBUG in ('Y', 'C') THEN
1579 atp_debug('pipe_utility: ' || ' Unable to remove pipe ');
1580 END IF;
1581 x_return_status := 'E';
1582 RETURN;
1583 END IF;
1584 ELSIF p_command = 'PURGE' THEN
1585 dbms_pipe.purge('session-'||p_session_id);
1586
1587 ELSIF p_command = 'OMERROR' THEN
1588 -- we need to create a new independent pipe for this
1589
1590 ret := DBMS_PIPE.CREATE_PIPE
1591 (pipename => 'OMERROR-'||p_session_id,
1592 maxpipesize => 5000,
1593 private => FALSE);
1594 IF ret <> 0 THEN
1595 IF PG_DEBUG in ('Y', 'C') THEN
1596 atp_debug('pipe_utility: FOR OMERROR' || ' Unable to open pipe ');
1597 END IF;
1598 x_return_status := 'E';
1599 RETURN;
1600 END IF;
1601 dbms_pipe.reset_buffer;
1602 dbms_pipe.pack_message(p_message);
1603 ret := dbms_pipe.send_message('OMERROR-'||p_session_id, 0);
1604
1605 ELSIF p_command = 'CHECK_OM' THEN
1606 p_message := NULL;
1607 ret := dbms_pipe.receive_message('OMERROR-'||p_session_id, 0);
1608 ret := dbms_pipe.next_item_type;
1609 dbms_pipe.unpack_message(p_message);
1610 dbms_pipe.purge('OMERROR-'||p_session_id);
1611
1612 ELSIF p_command = 'SEND' THEN
1613 --msc_sch_wb.atp_debug(' b4 sending mesg '||p_message);
1614 p_message := Nvl(p_message,fnd_date.date_to_canonical(Sysdate));
1615
1616 dbms_pipe.pack_message(p_message);
1617 ret := dbms_pipe.send_message('session-'||p_session_id, 0); -- 0 implies no block
1618 IF ret <> 0 THEN
1619 IF PG_DEBUG in ('Y', 'C') THEN
1620 null;
1621 -- msc_sch_wb.atp_debug('pipe_utility: ' || ' ERROR/Warning : ret code when sending message '||ret);
1622 END IF;
1623 END IF;
1624 IF PG_DEBUG in ('Y', 'C') THEN
1625 msc_sch_wb.atp_debug('pipe_utility: ' || ' sent mesg '||p_message);
1626 END IF;
1627 ELSIF p_command = 'RECEIVE' THEN
1628 p_message := NULL;
1629 ret := dbms_pipe.receive_message('session-'||p_session_id, 0);
1630 IF ret <> 0 THEN
1631 IF PG_DEBUG in ('Y', 'C') THEN
1632 null;
1633 -- msc_sch_wb.atp_debug('pipe_utility: ' || ' ERROR/Warning : ret code when recv message '||ret);
1634 END IF;
1635 END IF;
1636
1637 -- When 'END' is sent then it means that ATP is done
1638 p_message_count := 0;
1639 ret := dbms_pipe.next_item_type;
1640 IF ret <> 0 THEN
1641 dbms_pipe.unpack_message(p_message);
1642 p_message_count := p_message_count + 1;
1643 END IF;
1644 END IF;
1645
1646 EXCEPTION
1647 WHEN EMPTY_PIPE THEN
1648 NULL;
1649 WHEN OTHERS THEN
1650 IF PG_DEBUG in ('Y', 'C') THEN
1651 atp_debug(' Exception in pipe_utility '||p_command||Substr(Sqlerrm,1,100));
1652 END IF;
1653 x_return_status := 'E';
1654 x_msg_data := Substr(Sqlerrm,1,100);
1655 END pipe_utility;
1656
1657 PROCEDURE set_session_id(p_session_id IN NUMBER)
1658 IS
1659 BEGIN
1660 order_sch_wb.mr_debug := NVL(fnd_profile.value('MSC_ATP_DEBUG'),'N');
1661 order_sch_wb.debug_session_id := p_session_id;
1662 MSC_ATP_PVT.G_SESSION_ID := p_session_id;
1663
1664 END set_session_id;
1665
1666 PROCEDURE extend_other_cols(x_other_cols IN OUT NoCopy order_sch_wb.other_cols_typ, amount NUMBER)
1667 IS
1668 BEGIN
1669 x_other_cols.row_index.extend(amount);
1670 x_other_cols.org_code.extend(amount);
1671 x_other_cols.ship_method_text.extend(amount);
1672 x_other_cols.vendor_name.extend(amount);
1673 x_other_cols.vendor_site_name.extend(amount);
1674 x_other_cols.sr_supplier_id.extend(amount);
1675 x_other_cols.sr_supplier_site_id.extend(amount);
1676 END extend_other_cols;
1677
1678 PROCEDURE commit_db IS
1679 BEGIN
1680 COMMIT;
1681 END commit_db;
1682
1683 PROCEDURE get_master_org(p_master_org_id OUT NoCopy NUMBER)
1684 IS
1685 l_sql VARCHAR2(500);
1686 l_org_id NUMBER;
1687 BEGIN
1688 /* removed oe api since it was using autonomous call.
1689
1690 l_sql := 'begin :l_master_org_id := oe_sys_parameters.value(''MASTER_ORGANIZATION_ID''); end; ';
1691 execute immediate l_sql using OUT l_org_id;
1692 */
1693
1694 p_master_org_id := null;
1695
1696 SELECT
1697 master_organization_id
1698 INTO p_master_org_id
1699 FROM oe_system_parameters_all
1700 WHERE NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV
1701 ('CLIENT_INFO'),1 ,1),' ', NULL,
1702 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
1703 NVL(l_org_id, NVL(TO_NUMBER(DECODE(SUBSTRB
1704 (USERENV('CLIENT_INFO'),1,1),' ', NULL,
1705 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99));
1706
1707 EXCEPTION
1708
1709 WHEN NO_DATA_FOUND THEN
1710
1711 p_master_org_id := null;
1712
1713 WHEN OTHERS THEN
1714
1715 p_master_org_id := null;
1716
1717 END get_master_org;
1718
1719 PROCEDURE get_profile(profile_name VARCHAR2, profile_value OUT NoCopy NUMBER)
1720 IS
1721 l_atp_link VARCHAR2(255);
1722 sqlstmt VARCHAR2(255) := 'begin :profile_value := fnd_profile.value';
1723 -- atp_cursor integer;
1724 -- atp_rows_processed number;
1725 l_cursor integer;
1726 rows_processed number;
1727 DBLINK_NOT_OPEN EXCEPTION;
1728 PRAGMA EXCEPTION_INIT(DBLINK_NOT_OPEN, -2081);
1729 l_return_status varchar2(60);
1730 l_instance_id number;
1731 BEGIN
1732
1733 -- l_atp_link := fnd_profile.value('MRP_ATP_DATABASE_LINK');
1734 MSC_SATP_FUNC.get_dblink_profile(l_atp_link, l_instance_id, l_return_status);
1735 IF PG_DEBUG in ('Y', 'C') THEN
1736 msc_sch_wb.atp_debug(' get_profile '||l_atp_link);
1737 END IF;
1738 IF l_atp_link IS NOT NULL THEN
1739 sqlstmt := sqlstmt||'@'||l_atp_link;
1740 END IF;
1741 sqlstmt := sqlstmt||'('''||profile_name||'''); END; ';
1742
1743 IF PG_DEBUG in ('Y', 'C') THEN
1744 msc_sch_wb.atp_debug(' get_profile '||sqlstmt);
1745 END IF;
1746 execute immediate sqlstmt using OUT profile_value;
1747
1748 IF l_atp_link IS NOT NULL then
1749 -- mark distributed transaction boundary
1750 -- will need to do a manual clean up (commit) of the distributed
1751 -- operation, else subsequent operations fail w/ ora-02080 (bug 2218999)
1752 commit;
1753 l_cursor := dbms_sql.open_cursor;
1754 DBMS_SQL.PARSE ( l_cursor,
1755 'alter session close database link ' ||l_atp_link,
1756 dbms_sql.native
1757 );
1758 BEGIN
1759 rows_processed := dbms_sql.execute(l_cursor);
1760 EXCEPTION
1761 WHEN DBLINK_NOT_OPEN THEN
1762 IF PG_DEBUG in ('Y', 'C') THEN
1763 msc_sch_wb.atp_debug('get_profile: ' || 'inside DBLINK_NOT_OPEN');
1764 END IF;
1765 END;
1766 end if;
1767
1768 IF PG_DEBUG in ('Y', 'C') THEN
1769 msc_sch_wb.atp_debug(' get_profile '||profile_value);
1770 END IF;
1771 END get_profile;
1772
1773 PROCEDURE get_session_id(p_db_link in varchar2 default NULL,p_session_id out NoCopy varchar2 ) IS
1774 sql_stmt varchar2(200);
1775 l_null_db_link varchar2(100) := NULL;
1776 l_db_link varchar2(100) ;
1777 l_cursor integer;
1778 rows_processed number;
1779 DBLINK_NOT_OPEN EXCEPTION;
1780 PRAGMA EXCEPTION_INIT(DBLINK_NOT_OPEN, -2081);
1781 BEGIN
1782 if ( p_db_link is NULL) then
1783 select mrp_atp_schedule_temp_s.nextval
1784 into p_session_id
1785 from dual;
1786 else
1787 l_db_link := '@'||p_db_link;
1788 --sql_stmt := ' select mrp_atp_schedule_temp_s.nextval from dual@'||p_db_link;
1789 sql_stmt := 'begin msc_sch_wb.get_session_id'||l_db_link||'(:l_null_db_link,:p_session_id); end;';
1790 EXECUTE IMMEDIATE sql_stmt using l_null_db_link, out p_session_id;
1791
1792 l_cursor := dbms_sql.open_cursor;
1793 commit;
1794 DBMS_SQL.PARSE ( l_cursor, 'alter session close database link ' ||p_db_link, dbms_sql.native);
1795 BEGIN
1796 rows_processed := dbms_sql.execute(l_cursor);
1797 EXCEPTION
1798 WHEN DBLINK_NOT_OPEN THEN
1799 IF PG_DEBUG in ('Y', 'C') THEN
1800 msc_sch_wb.atp_debug('get_session_id: ' || 'inside DBLINK_NOT_OPEN');
1801 END IF;
1802 END;
1803
1804 end if;
1805 END get_session_id;
1806
1807 PROCEDURE get_g_atp_error_code (x_atp_err_code OUT NoCopy NUMBER) IS
1808 BEGIN
1809 x_atp_err_code := MSC_SCH_WB.G_ATP_ERROR_CODE ;
1810 END get_g_atp_error_code;
1811
1812 PROCEDURE update_constraint_path(p_session_id IN NUMBER,
1813 p_return_error IN OUT NoCopy VARCHAR2) IS
1814 CURSOR get_constr_peg_id IS
1815 select distinct end_pegging_id
1816 from mrp_atp_details_temp
1817 where session_id = p_session_id
1818 and record_type = 3
1819 and constraint_type is not NULL;
1820
1821 sql_stmt VARCHAR2(4000);
1822 l_end_peg_id_list VARCHAR2(3000):= '0';
1823 l_end_peg_id VARCHAR2(10):= '-1';
1824
1825 --bug 3751114
1826 l_peg_record_type NUMBER := 3;
1827 l_constraint_path_flag NUMBER := 1;
1828
1829 BEGIN
1830
1831 OPEN get_constr_peg_id;
1832 LOOP
1833 FETCH get_constr_peg_id INTO l_end_peg_id;
1834 EXIT WHEN get_constr_peg_id%NOTFOUND;
1835 l_end_peg_id_list := l_end_peg_id||' ,'||l_end_peg_id_list;
1836 END LOOP;
1837 CLOSE get_constr_peg_id;
1838
1839 IF PG_DEBUG in ('Y', 'C') THEN
1840 msc_sch_wb.atp_debug('l_end_peg_id_list in update_constr ' ||
1841 l_end_peg_id_list);
1842 END IF;
1843
1844 IF l_end_peg_id <> '-1' THEN
1845
1846 sql_stmt := 'UPDATE mrp_atp_details_temp
1847 set constrained_path = ' || l_constraint_path_flag ||
1848 ' where record_type = ' || l_peg_record_type ||
1849 ' and session_id = '||p_session_id||
1850 ' and pegging_id in
1851 (select pegging_id
1852 from mrp_atp_details_temp
1853 where record_type = ' || l_peg_record_type ||
1854 ' and session_id ='||p_session_id||
1855 ' start with session_id = '||p_session_id||
1856 ' and record_type = ' || l_peg_record_type ||
1857 ' and end_pegging_id in ('||l_end_peg_id_list||') ' ||
1858 ' and constraint_type is not null
1859 connect by pegging_id = PRIOR parent_pegging_id
1860 and record_type = ' || l_peg_record_type ||
1861 ' and session_id = '||p_session_id||')';
1862 EXECUTE IMMEDIATE sql_stmt;
1863 commit;
1864
1865 END IF;
1866 EXCEPTION
1867 WHEN OTHERS THEN
1868 IF PG_DEBUG in ('Y', 'C') THEN
1869 msc_sch_wb.atp_debug('Excp in update_constraint_path '||
1870 Substr(Sqlerrm, 1,100));
1871 p_return_error := 'E';
1872 END IF;
1873
1874 END update_constraint_path ;
1875
1876 PROCEDURE get_ato_comp_details(p_session_id IN NUMBER,
1877 p_child_ato_id IN NUMBER,
1878 p_organization_id IN NUMBER,
1879 x_days_late IN OUT NoCopy NUMBER,
1880 x_error_code IN OUT NoCopy VARCHAR2) IS
1881
1882 CURSOR ato_details_with_org(p_inventory_item_id NUMBER,
1883 p_session_id NUMBER,
1884 p_organization_id NUMBER ) IS
1885 select
1886 trunc(mast.supply_demand_date - mast.required_date)
1887 from mrp_atp_details_temp mast
1888 where --mast.constraint_type is not NULL
1889 mast.session_id = p_session_id
1890 and mast.record_type = 3
1891 and mast.supply_demand_type = 2
1892 and mast.inventory_item_id = p_inventory_item_id
1893 and mast.organization_id = p_organization_id;
1894
1895 CURSOR ato_details_no_org( p_inventory_item_id NUMBER,
1896 p_session_id NUMBER)
1897 IS
1898 select trunc(mast.supply_demand_date - mast.required_date) date_diff
1899 from mrp_atp_details_temp mast
1900 where --mast.constrained_path is not NULL
1901 mast.session_id = p_session_id
1902 and mast.record_type = 3
1903 and mast.supply_demand_type = 2
1904 and mast.inventory_item_id = p_inventory_item_id
1905 order by date_diff DESC;
1906
1907
1908
1909 BEGIN
1910 IF p_organization_id is NOT NULL THEN
1911 OPEN ato_details_with_org(p_child_ato_id,
1912 p_session_id,
1913 p_organization_id);
1914 FETCH ato_details_with_org INTO
1915 x_days_late;
1916 CLOSE ato_details_with_org;
1917
1918 ELSE
1919 OPEN ato_details_no_org(p_child_ato_id,
1920 p_session_id);
1921 FETCH ato_details_no_org INTO
1922 x_days_late;
1923 CLOSE ato_details_no_org;
1924 END IF;
1925
1926 EXCEPTION
1927 WHEN OTHERS THEN
1928 IF PG_DEBUG in ('Y', 'C') THEN
1929 msc_sch_wb.atp_debug('Excp in msc_sch_wb.get_ato_comp_details '||
1930 Substr(Sqlerrm, 1,100));
1931 END IF;
1932
1933
1934 END get_ato_comp_details;
1935
1936
1937 END msc_sch_wb;