1 PACKAGE BODY MSC_ATP_CTO AS
2 /* $Header: MSCCTOPB.pls 120.9 2007/12/12 10:24:17 sbnaik ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_ATP_CTO';
5
6 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
7
8 PROCEDURE Check_Lines_For_CTO_ATP (
9 p_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
10 p_session_id IN number,
11 p_dblink IN varchar2,
12 p_instance_id IN Number,
13 x_return_status OUT NoCopy VARCHAR2
14 ) IS
15
16 l_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
17 l_atp_period MRP_ATP_PUB.ATP_Period_Typ;
18 l_atp_details MRP_ATP_PUB.ATP_Details_Typ;
19 l_msg_data VARCHAR2(30);
20 l_msg_count VARCHAR2(30);
21 l_return_status VARCHAR2(30);
22 l_atp_count number;
23 l_error_code number;
24 BEGIN
25 IF PG_DEBUG in ('Y', 'C') THEN
26 msc_sch_wb.atp_debug('***** Begin Check_Lines_For_CTO_ATP *****');
27 END IF;
28
29 --- put data in mrp_atp_schedule_temp, call put_into_temp
30 MSC_ATP_UTILS.put_into_temp_table(
31 NULL,
32 p_session_id,
33 p_atp_rec,
34 l_atp_supply_demand,
35 l_atp_period,
36 l_atp_details,
37 MSC_ATP_UTILS.REQUEST_MODE,
38 l_return_status,
39 l_msg_data,
40 l_msg_count);
41
42
43 IF PG_DEBUG in ('Y', 'C') THEN
44 msc_sch_wb.atp_debug('***** After putting the data into temp table *****');
45 END IF;
46 ----now call CTO for matching and option dependent sourcing
47 --IF MSC_ATP_PVT.G_INV_CTP = 4 then -- call matching API for both ODS and PDS
48 IF NOT( MSC_ATP_PVT.G_CALLING_MODULE = 724) THEN
49 IF PG_DEBUG in ('Y', 'C') THEN
50 msc_sch_wb.atp_debug('***** Call Matching *****');
51 END IF;
52
53 MSC_ATP_CTO.Match_CTO_Lines(p_session_id, p_dblink, p_instance_id, l_return_status);
54
55 IF PG_DEBUG in ('Y', 'C') THEN
56 msc_sch_wb.atp_debug('***** l_return_status := ' || l_return_status);
57 msc_sch_wb.atp_debug('*** G_RET_STS_SUCCESS := ' || FND_API.G_RET_STS_SUCCESS);
58 END IF;
59 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
60 IF PG_DEBUG in ('Y', 'C') THEN
61 msc_sch_wb.atp_debug('***** Error Occured While Matching *****');
62 END IF;
63 l_error_code := MSC_ATP_PVT.ERROR_WHILE_MATCHING;
64 p_atp_rec.error_code(1) := MSC_ATP_PVT.ATP_PROCESSING_ERROR;
65 RAISE FND_API.G_EXC_ERROR ;
66
67 END IF;
68
69 IF PG_DEBUG in ('Y', 'C') THEN
70 msc_sch_wb.atp_debug('***** AFTER Call Matching *****');
71
72 END IF;
73
74 ---now transfer data into CTO BOM
75 --we do this step when we need to make it
76 --populate_cto_bom(p_session_id, p_dblink);
77 ELSE
78 --- Call from 724. Always enable ATP
79 MSC_ATP_PUB.G_ATP_CHECK := 'Y';
80 END IF;
81
82 ---now check if non atpable item exist or not
83 IF MSC_ATP_PUB.G_ATP_CHECK = 'N' THEN
84 IF PG_DEBUG in ('Y', 'C') THEN
85 msc_sch_wb.atp_debug('***** Check if any ATPAble item exisst or not ****');
86 END IF;
87 select count(*)
88 into l_atp_count
89 from mrp_atp_schedule_temp
90 where session_id = p_session_id
91 and order_line_id = NVL(ato_model_line_id, order_line_id)
92 --bug 3378648
93 and status_flag in (99,4) --4658238
94 ---Bug 3687934
95 --- GOP for non-atpable items: We go to destination if source organization is not provided.
96 and (NVL(atp_flag, 'N') <> 'N' or atp_components_flag <> 'N' or source_organization_id is null);
97
98 IF PG_DEBUG in ('Y', 'C') THEN
99 msc_sch_wb.atp_debug('***** After check for ATPable item, l_atp_count := ' || l_atp_count );
100 END IF;
101
102 IF l_atp_count > 0 THEN
103 MSC_ATP_PUB.G_ATP_CHECK := 'Y';
104 END IF;
105
106
107 END IF;
108
109
110 --- now we need to populte the data back into atp_rec_type if
111 --- 1. single database setup
112 --- 2. distributed setup and no items are atpable
113
114 IF (p_dblink is null and MSC_ATP_PVT.G_CALLING_MODULE <> 724) or MSC_ATP_PUB.G_ATP_CHECK = 'N' THEN
115 IF PG_DEBUG in ('Y', 'C') THEN
116 msc_sch_wb.atp_debug('***** db link is null or no itmes are atpable, get the data');
117 END IF;
118
119 MSC_ATP_UTILS.Get_From_Temp_Table(
120 null,
121 p_session_id,
122 p_atp_rec,
123 l_atp_supply_demand,
124 l_atp_period,
125 l_atp_details,
126 MSC_ATP_UTILS.REQUEST_MODE,
127 l_return_status,
128 l_msg_data,
129 l_msg_count,
130 2); -- details_flag
131 IF PG_DEBUG in ('Y', 'C') THEN
132 msc_sch_wb.atp_debug('***** number of records := ' || p_atp_rec.inventory_item_id.count );
133 END IF;
134
135 END IF;
136 EXCEPTION
137
138 WHEN OTHERS THEN
139 IF PG_DEBUG in ('Y', 'C') THEN
140 msc_sch_wb.atp_debug('***** Errror Occured in Check_Lines_For_CTO_ATP');
141 msc_sch_wb.atp_debug('Sql Error := ' || SQLERRM);
142 END IF;
143 p_atp_rec.error_code(1) := MSC_ATP_PVT.ATP_PROCESSING_ERROR;
144 x_return_status := FND_API.G_RET_STS_ERROR;
145
146 END Check_Lines_For_CTO_ATP;
147
148 Procedure Match_CTO_Lines(P_session_id IN Number,
149 p_dblink IN varchar2,
150 p_instance_id IN number,
151 x_return_status OUT NOCOPY VARCHAR2)
152 IS
153
154 l_cto_lines_for_match CTO_Configured_Item_GRP.CTO_MATCH_REC_TYPE;
155 l_cto_sources CTO_OSS_SOURCE_PK.OSS_ORGS_LIST_REC_TYPE;
156 i number;
157 l_return_status varchar2(30);
158 l_msg_count number;
159 l_msg_data varchar2(100);
160 l_action varchar2(10);
161 l_match_found varchar2(1);
162 l_source varchar2(10);
163
164 BEGIN
165
166 ---first check if there are any ATPable Models/ATO items or not
167
168 IF PG_DEBUG in ('Y', 'C') THEN
169 msc_sch_wb.atp_debug('Inside Call Matching');
170 msc_sch_wb.atp_debug('P_session_id := ' || P_session_id);
171 msc_sch_wb.atp_debug('p_dblink := ' || p_dblink);
172 msc_sch_wb.atp_debug('p_instance_id := '|| p_instance_id);
173 END IF;
174 --- distinct is needed because for GOP, UI calls same
175 -- lines with different warehouse.
176 -- CTO has unique index on line id. If distinct in not used
177 -- the CTO is called with multiple lines with same line id.
178 -- This fails the unique index they have
179 select distinct
180 mast.order_line_id,
181 mast.Parent_line_id,
182 mast.ATO_Model_Line_Id,
183 mast.Top_Model_line_id,
184 mast.inventory_item_id,
185 mast.Component_Code,
186 mast.Component_Sequence_ID,
187 mast.validation_org,
188 mast.Quantity_Ordered,
189 mast.UOM_CODE,
190 -- 3555026: pass source organization id to CTO only when call comes from SO pad
191 decode(NVL(mast.calling_module, -1), -1, null, mast.source_organization_id)
192 bulk collect into
193 l_cto_lines_for_match.line_id,
194 l_cto_lines_for_match.LINK_TO_LINE_ID,
195 l_cto_lines_for_match.ATO_LINE_ID,
196 l_cto_lines_for_match.TOP_MODEL_LINE_ID,
197 l_cto_lines_for_match.INVENTORY_ITEM_ID,
198 l_cto_lines_for_match.COMPONENT_CODE,
199 l_cto_lines_for_match.COMPONENT_SEQUENCE_ID ,
200 l_cto_lines_for_match.VALIDATION_ORG,
201 l_cto_lines_for_match.ORDERED_QUANTITY,
202 l_cto_lines_for_match.ORDER_QUANTITY_UOM,
203 --pass source org to CTO
204 l_cto_lines_for_match.SHIP_FROM_ORG_ID
205 from mrp_atp_schedule_temp mast
206 where Session_id = p_session_id and
207 --bug 3378648: Look only at ATP inserted data
208 status_flag in (99,4) and --4658238
209 Ato_model_line_id in
210 (select mast_1.ato_model_line_id from
211 mrp_atp_schedule_temp mast_1
212 where mast_1.session_id = p_session_id
213 --bug 3378648
214 and status_flag in (99,4) --4658238
215 and mast_1.order_line_id = mast_1.ato_model_line_id
216 and (mast_1.atp_flag <> 'N' or mast_1.atp_components_flag <> 'N')
217 and mast_1.QUANTITY_ORDERED > 0);
218
219 IF PG_DEBUG in ('Y', 'C') THEN
220 msc_sch_wb.atp_debug(' after SQL to see if ATPable models are there or not');
221 msc_sch_wb.atp_debug('Number of ATPAble components := '
222 || l_cto_lines_for_match.line_id.count);
223 END IF;
224 ---- Now see if we have any atpable CTO Models or not
225
226
227 IF l_cto_lines_for_match.inventory_item_id.count > 0 THEN
228 --- we found some lines to match. Some Top level ATPable models are present
229 -- call CTO
230 IF PG_DEBUG in ('Y', 'C') THEN
231 msc_sch_wb.atp_debug(' ATPable models are present, call cto api for matching');
232 END IF;
233 BEGIN
234 --some atpable model is there. We need to go to destonation. st the atp check flag
235 MSC_ATP_PUB.G_ATP_CHECK := 'Y';
236 --CTO team has asked for this. The need to differentiate between calls from GOP or other modules
237
238 l_source := 'GOP';
239 --call cto api for matching and other information
240 CTO_GOP_INTERFACE_PK.CTO_GOP_WRAPPER_API(
241 l_action,
242 l_source,
243 l_cto_lines_for_match,
244 l_cto_sources,
245 l_return_status,
246 l_msg_count,
247 l_msg_data);
248
249 IF PG_DEBUG in ('Y', 'C') THEN
250 msc_sch_wb.atp_debug(' l_return_status := ' || l_return_status);
251 END IF;
252
253
254 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
255 IF PG_DEBUG in ('Y', 'C') THEN
256 msc_sch_wb.atp_debug(' Unhandeled ecpetion occured in call to CTO API');
257 msc_sch_wb.atp_debug('Error := ' || sqlerrm);
258 END IF;
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 RAISE FND_API.G_EXC_ERROR ;
261
262 END IF;
263
264 EXCEPTION
265 WHEN OTHERS THEN
266 IF PG_DEBUG in ('Y', 'C') THEN
267 msc_sch_wb.atp_debug(' Unhandeled ecpetion occured in call to CTO API');
268 msc_sch_wb.atp_debug('Error := ' || sqlerrm);
269 END IF;
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 RAISE FND_API.G_EXC_ERROR ;
272 ---match fail
273 END;
274
275 -- update mrp_atp_schedule_temp with match info Praent_ato_lin_id information
276 IF PG_DEBUG in ('Y', 'C') THEN
277 msc_sch_wb.atp_debug(' l_cto_lines_for_match count := ' || l_cto_lines_for_match.inventory_item_id.count);
278 msc_sch_wb.atp_debug('matched_item_id count := ' || l_cto_lines_for_match.config_item_id.count);
279 msc_sch_wb.atp_debug('gop_parent_ato_line_id count := ' || l_cto_lines_for_match.gop_parent_ato_line_id.count);
280 msc_sch_wb.atp_debug('wip_supply_type count := ' || l_cto_lines_for_match.wip_supply_type.count);
281 msc_sch_wb.atp_debug('oss_error_code count := ' || l_cto_lines_for_match.oss_error_code.count);
282 FOR i in 1..l_cto_lines_for_match.inventory_item_id.count LOOP
283 msc_sch_wb.atp_debug('counter := ' || i);
284 msc_sch_wb.atp_debug('item id := ' || l_cto_lines_for_match.inventory_item_id(i));
285 msc_sch_wb.atp_debug('gop_parent_ato_line_id := ' || l_cto_lines_for_match.gop_parent_ato_line_id(i));
286 msc_sch_wb.atp_debug('matched_item_id := ' || l_cto_lines_for_match.config_item_id(i));
287 msc_sch_wb.atp_debug('wip_supply_type := ' || l_cto_lines_for_match.wip_supply_type(i));
288 msc_sch_wb.atp_debug('oss_error_code := ' || l_cto_lines_for_match.oss_error_code(i));
289 END LOOP;
290
291 END IF;
292 --update information returned by match API
293 FORALL i in 1..l_cto_lines_for_match.inventory_item_id.count
294 UPDATE mrp_atp_schedule_temp
295 SET ATO_Parent_Model_Line_Id = l_cto_lines_for_match.gop_parent_ato_line_id(i),
296 match_item_id = l_cto_lines_for_match.config_item_id(i),
297 wip_supply_type = l_cto_lines_for_match.wip_supply_type(i),
298 oss_error_code = l_cto_lines_for_match.oss_error_code(i),
299 error_code = l_cto_lines_for_match.oss_error_code(i)
300 WHERE session_id = p_session_id
301 --bug 3378648:
302 and status_flag in (99,4) --4658238
303 and order_line_id = l_cto_lines_for_match.line_id(i);
304
305 IF PG_DEBUG in ('Y', 'C') THEN
306 msc_sch_wb.atp_debug(' After Update of CTO data');
307 msc_sch_wb.atp_debug('Lines updated := ' || SQL%ROWCOUNT);
308 msc_sch_wb.atp_debug('Process CTO sources, count := ' || l_cto_sources.org_id.count);
309 END IF;
310
311 ---transfer option specific data
312 Process_CTO_Sources(p_dblink,
313 p_session_id,
314 l_cto_sources,
315 p_instance_id);
316
317 IF PG_DEBUG in ('Y', 'C') THEN
318 msc_sch_wb.atp_debug(' After processing CTO sources');
319 END IF;
320
321 END IF; --IF l_cto_lines_for_match.inventory_item_id.count > 0 TH
322 EXCEPTION
323 WHEN OTHERS THEN
324 msc_sch_wb.atp_debug('Error Occured := ' || SQLERRM);
325 x_return_status := FND_API.G_RET_STS_ERROR;
326
327 END Match_CTO_Lines;
328
329 Procedure Process_CTO_Sources(p_dblink IN varchar2,
330 p_session_id IN number,
331 p_cto_sources IN CTO_OSS_SOURCE_PK.OSS_ORGS_LIST_REC_TYPE,
332 p_instance_id IN NUMBER)
333 IS
334 l_dblink varchar2(30);
335 l_sql_stmt varchar2(10000);
336 i number;
337 l_user_id number;
338 l_sysdate date;
339 i number;
340
341 BEGIN
342 IF PG_DEBUG in ('Y', 'C') THEN
343 msc_sch_wb.atp_debug('Inside Process CTO Source');
344 msc_sch_wb.atp_debug('Print CTO OSS Data');
345 FOR i in 1..p_cto_sources.Inventory_item_id.count LOOP
346 msc_sch_wb.atp_debug('Source # := ' || i);
347 msc_sch_wb.atp_debug('Line_id := ' || p_cto_sources.line_id(i));
348 msc_sch_wb.atp_debug('Inventory_item_id := ' || p_cto_sources.Inventory_item_id(i));
349 msc_sch_wb.atp_debug('Org_id := '|| p_cto_sources.Org_id(i));
350 msc_sch_wb.atp_debug('Vendor_id := ' || p_cto_sources.Vendor_id(i));
351 msc_sch_wb.atp_debug('Vendor_site := ' || p_cto_sources.Vendor_site(i));
352 msc_sch_wb.atp_debug('ato_line_id := ' || p_cto_sources.ato_line_id(i));
353 msc_sch_wb.atp_debug('make_flag := ' || p_cto_sources.make_flag(i));
354 END LOOP;
355 END IF;
356 --code to transfer data from pl/sql to CTO source table.
357 l_user_id := FND_GLOBAL.user_id;
358 l_sysdate := sysdate;
359
360 l_dblink := '@' || p_dblink;
361
362 /*s_cto_rearch: 24x7 --now CTO sources is session specific. We donot need to maintain sattsu flag
363 IF p_dblink is null then
364 update msc_cto_sources
365 set status_flag = 2
366 where ato_line_id in (select order_line_id
367 from mrp_atp_schedule_temp
368 where session_id = p_session_id
369 and order_line_id = ato_model_line_id);
370 IF PG_DEBUG in ('Y', 'C') THEN
371 msc_sch_wb.atp_debug('Number of rows updated := ' || SQL%ROWCOUNT);
372 END IF;
373 Else
374 IF PG_DEBUG in ('Y', 'C') THEN
375 msc_sch_wb.atp_debug('Delete CTO Sources locally');
376 END IF;
377 delete msc_cto_sources
378 where line_id in (select order_line_id
379 from mrp_atp_schedule_temp
380 where session_id = p_session_id
381 and order_line_id = ato_model_line_id);
382
383 IF PG_DEBUG in ('Y', 'C') THEN
384 msc_sch_wb.atp_debug('Number of rows deleted := ' || SQL%ROWCOUNT);
385 msc_sch_wb.atp_debug('Update CTO Sources across DB');
386 END IF;
387 l_sql_stmt := 'Update msc_cto_sources' || l_dblink;
388 IF PG_DEBUG in ('Y', 'C') THEN
389 msc_sch_wb.atp_debug('sql stmt := ' || l_sql_stmt);
390 END IF;
391 l_sql_stmt := l_sql_stmt || ' set status_flag = 2 '
392 || ' where ato_line_id in (select order_line_id '
393 || ' from mrp_atp_schedule_temp '
394 || ' where session_id = :p_session_id '
395 || ' and order_line_id = ato_model_line_id)';
396 IF PG_DEBUG in ('Y', 'C') THEN
397 msc_sch_wb.atp_debug('sql stmt := ' || l_sql_stmt);
398 END IF;
399 EXECUTE IMMEDIATE l_sql_stmt using p_session_id;
400 IF PG_DEBUG in ('Y', 'C') THEN
401 msc_sch_wb.atp_debug(' After Updating CTO Sources across DB');
402 msc_sch_wb.atp_debug('Number of rows updated := ' || SQL%ROWCOUNT);
403 END IF;
404 END IF;
405
406 e_cto_rearch: 24x7 */
407
408 IF PG_DEBUG in ('Y', 'C') THEN
409 msc_sch_wb.atp_debug('Now Insert New data');
410 END IF;
411
412 IF p_cto_sources.line_id.count > 0 THEN
413 --now insert the latest data into msc_cto_sources
414 IF PG_DEBUG in ('Y', 'C') THEN
415 msc_sch_wb.atp_debug('Now Insert New data in Local Tbale');
416 END IF;
417
418 FORALL i in 1..p_cto_sources.line_id.count --LOOP
419 insert into msc_cto_sources
420 (line_id,
421 inventory_item_id,
422 organization_id,
423 supplier_id,
424 supplier_site_code,
425 status_flag,
426 sr_instance_id,
427 ato_line_id,
428 make_flag,
429 created_by,
430 creation_date,
431 last_updated_by,
432 last_update_date,
433 refresh_number,
434 session_id)
435 values
436 ( p_cto_sources.line_id(i),
437 p_cto_sources.Inventory_item_id(i),
438 p_cto_sources.Org_id(i),
439 p_cto_sources.Vendor_id(i),
440 p_cto_sources.Vendor_site(i),
441 1,
442 p_instance_id,
443 p_cto_sources.ato_line_id(i),
444 p_cto_sources.make_flag(i),
445 l_user_id,
446 l_sysdate,
447 l_user_id,
448 l_sysdate,
449 MSC_ATP_PVT.G_REFRESH_NUMBER,
450 p_session_id);
451 --END LOOP;
452
453 IF PG_DEBUG in ('Y', 'C') THEN
454 msc_sch_wb.atp_debug('After Insert New data in Local Table');
455 msc_sch_wb.atp_debug('Number of rows inserted := ' || SQL%ROWCOUNT);
456 END IF;
457
458 If p_dblink is not null THEN
459 IF PG_DEBUG in ('Y', 'C') THEN
460 msc_sch_wb.atp_debug('Transfer Data Across DBLink');
461 END IF;
462 --now transfer the data across dblink
463 l_sql_stmt := 'Insert into msc_cto_sources' || l_dblink;
464 l_sql_stmt := l_sql_stmt || ' ( LINE_ID, ORGANIZATION_ID, SUPPLIER_ID,
465 SUPPLIER_SITE_CODE, STATUS_FLAG, INVENTORY_ITEM_ID,
466 SR_INSTANCE_ID, ATO_LINE_ID, CREATION_DATE,
467 CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE,
468 MAKE_FLAG, refresh_number, session_id)
469 Select LINE_ID, ORGANIZATION_ID, SUPPLIER_ID,
470 SUPPLIER_SITE_CODE, STATUS_FLAG, INVENTORY_ITEM_ID,
471 SR_INSTANCE_ID, ATO_LINE_ID, CREATION_DATE,
472 CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE,
473 MAKE_FLAG, refresh_number, session_id from msc_cto_sources
474 where session_id = :p_session_id
475 and line_id in (select order_line_id
476 from mrp_atp_schedule_temp
477 where session_id = :p_session_id
478 --bug 3378648
479 and status_flag = 99
480 and order_line_id = ato_model_line_id)';
481 IF PG_DEBUG in ('Y', 'C') THEN
482 msc_sch_wb.atp_debug('l_sql_stmt := ' || l_sql_stmt);
483 END IF;
484 EXECUTE IMMEDIATE l_sql_stmt using p_session_id, p_session_id;
485 IF PG_DEBUG in ('Y', 'C') THEN
486 msc_sch_wb.atp_debug('After Transfering Data Across DBLink');
487 msc_sch_wb.atp_debug('Number of rows transfered across dblink := ' || SQL%ROWCOUNT);
488 END IF;
489
490 END IF;
491 END IF;
492 END Process_CTO_Sources;
493
494
495 Procedure Get_Mandatory_Components(p_plan_id IN NUMBER,
496 p_instance_id IN NUMBER,
497 p_organization_id IN NUMBER,
498 p_sr_inventory_item_id IN NUMBER,
499 p_quantity IN NUMBER,
500 p_request_date IN DATE,
501 p_dest_inv_item_id IN NUMBER,
502 x_mand_comp_info_rec OUT NOCOPY MSC_ATP_CTO.mand_comp_info_rec
503 )
504
505 IS
506 l_inventory_item_id number;
507 l_process_seq_id number;
508 l_routing_seq_id number;
509 l_bill_seq_id number;
510 l_op_seq_id number;
511 l_return_status varchar2(30);
512 i number;
513 l_sysdate date; --4137608
514 BEGIN
515
516 IF PG_DEBUG in ('Y', 'C') THEN
517 msc_sch_wb.atp_debug('Get_Mandatory_Components: Inside Get Mandatory Components');
518 msc_sch_wb.atp_debug('Get_Mandatory_Components: p_dest_inv_item_id := ' || p_dest_inv_item_id);
519 msc_sch_wb.atp_debug('Get_Mandatory_Components:p_sr_inventory_item_id := ' || p_sr_inventory_item_id);
520 msc_sch_wb.atp_debug('Get_Mandatory_Components: p_plan_id := ' || p_plan_id);
521 msc_sch_wb.atp_debug('p_instance_id := ' || p_instance_id);
522 msc_sch_wb.atp_debug('p_quantity := '|| p_quantity);
523 msc_sch_wb.atp_debug('p_request_date := ' || p_request_date);
524 msc_sch_wb.atp_debug('MSC_ATP_PVT.G_PTF_DATE := '|| MSC_ATP_PVT.G_PTF_DATE); --4137608
525 l_sysdate := trunc(sysdate); --4137608
526 END IF;
527 ----first get the destination inventory_item_id
528 IF p_dest_inv_item_id is not null THEN
529 l_inventory_item_id := p_dest_inv_item_id;
530 ELSE
531 l_inventory_item_id := MSC_ATP_FUNC. get_inv_item_id(p_instance_id,
532 p_sr_inventory_item_id,
533 null,
534 p_organization_id);
535 END IF;
536
537 IF PG_DEBUG in ('Y', 'C') THEN
538 msc_sch_wb.atp_debug('Get_Mandatory_Components: l_inventory_item_id := ' || l_inventory_item_id);
539 END IF;
540
541 ---- Now get the process effectivity
542 MSC_ATP_PROC.get_process_effectivity(
543 p_plan_id,
544 l_inventory_item_id,
545 p_organization_id,
546 p_instance_id,
547 p_request_date,
548 p_quantity,
549 l_process_seq_id,
550 l_routing_seq_id,
551 l_bill_seq_id,
552 l_op_seq_id, --4570421
553 l_return_status);
554
555
556 IF PG_DEBUG in ('Y', 'C') THEN
557 msc_sch_wb.atp_debug('Get_Mandatory_Components: l_bill_seq_id := ' || l_bill_seq_id);
558 msc_sch_wb.atp_debug('Get_Mandatory_Components: l_op_seq_id := ' || l_op_seq_id); --4570421
559 END IF;
560 --- now get the components
561 BEGIN
562 SELECT msi.sr_inventory_item_id,
563 --4570421
564 --round(mbc.usage_quantity * p_quantity, 6),
565 ROUND ((decode (NVL (MSC_ATP_PVT.G_ORG_INFO_REC.org_type,1), MSC_ATP_PVT.DISCRETE_ORG, decode ( nvl(mbc.scaling_type, 1), 1, (MBC.USAGE_QUANTITY*p_quantity),
566 2, MBC.USAGE_QUANTITY),
567 MSC_ATP_PVT.OPM_ORG, decode (nvl (mbc.scaling_type, 1), 0, MBC.USAGE_QUANTITY,
568 1, (MBC.USAGE_QUANTITY*p_quantity),
569 2, MBC.USAGE_QUANTITY,
570 3, (MBC.USAGE_QUANTITY*p_quantity),
571 4, (MBC.USAGE_QUANTITY*p_quantity),
572 5, (MBC.USAGE_QUANTITY*p_quantity))
573 ))--/NVL (mbc.component_yield_factor, 1) --4767982
574 ,6),
575 msi.atp_flag,
576 msi.atp_components_flag,
577 msi.aggregate_time_fence_date, -- For time_phased_atp
578 msi.bom_item_type,
579 msi.fixed_lead_time,
580 msi.variable_lead_time,
581 msi.inventory_item_id,
582 msi.uom_code,
583 --4570421
584 mbc.scaling_type,
585 mbc.scale_multiple,
586 mbc.scale_rounding_variance,
587 mbc.rounding_direction,
588 mbc.component_yield_factor, --4570421
589 MBC.USAGE_QUANTITY*mbc.component_yield_factor, --4775920
590 NVL (MSC_ATP_PVT.G_ORG_INFO_REC.org_type,1) --4775920
591 BULK COLLECT INTO
592 x_mand_comp_info_rec.sr_inventory_item_id,
593 x_mand_comp_info_rec.quantity,
594 x_mand_comp_info_rec.atp_flag,
595 x_mand_comp_info_rec.atp_components_flag,
596 x_mand_comp_info_rec.atf_date, --For time_phased_atp
597 x_mand_comp_info_rec.bom_item_type,
598 x_mand_comp_info_rec.fixed_lead_time,
599 x_mand_comp_info_rec.variable_lead_time,
600 x_mand_comp_info_rec.dest_inventory_item_id,
601 x_mand_comp_info_rec.uom_code,
602 --4570421
603 x_mand_comp_info_rec.scaling_type,
604 x_mand_comp_info_rec.scale_multiple,
605 x_mand_comp_info_rec.scale_rounding_variance,
606 x_mand_comp_info_rec.rounding_direction,
607 x_mand_comp_info_rec.component_yield_factor, --4570421
608 x_mand_comp_info_rec.usage_qty, --4775920
609 x_mand_comp_info_rec.organization_type --4775920
610
611 FROM MSC_SYSTEM_ITEMS MSI,
612 MSC_BOM_COMPONENTS MBC
613 WHERE Mbc.plan_id = p_plan_id
614 AND mbc.sr_instance_id = p_instance_id
615 AND mbc.bill_sequence_id = l_bill_seq_id
616 AND mbc.using_assembly_id = l_inventory_item_id
617 AND mbc.organization_id = p_organization_id
618 AND mbc.optional_component = 2 --- choose mandatory comps
619 -- do not honor atp_flag for smcs
620 ---AND mbc.ATP_FLAG = 1 --- chose ATPable components
621 AND mbc.USAGE_QUANTITY > 0
622 AND msi.inventory_item_id = mbc.inventory_item_id
623 AND msi.organization_Id = mbc.organization_id
624 AND msi.plan_id = mbc.plan_id
625 AND msi.sr_instance_id = mbc.sr_instance_id
626 AND msi.bom_item_type = 4 -- chose always standard comp as option class will be passed by OM
627 AND (msi.atp_flag <> 'N' or msi.atp_components_flag <> 'N')
628 --4137608
629 -- effective date should be greater than or equal to greatest of PTF date, sysdate and request date
630 -- disable date should be less than or equal to greatest of PTF date, sysdate and request date
631 AND TRUNC(NVL(MBC.DISABLE_DATE, GREATEST(p_request_date, l_sysdate, MSC_ATP_PVT.G_PTF_DATE)+1)) >=
632 TRUNC(GREATEST(p_request_date, l_sysdate, MSC_ATP_PVT.G_PTF_DATE))
633 AND TRUNC(MBC.EFFECTIVITY_DATE) <=
634 TRUNC(GREATEST(p_request_date, l_sysdate, MSC_ATP_PVT.G_PTF_DATE));
635 --4137608
636 /*AND trunc(mbc.effectivity_date) <= trunc(p_request_date)
637 AND nvl(trunc(mbc.disable_date), trunc(p_request_date))
638 >= trunc(p_request_date);*/
639 EXCEPTION
640 WHEN OTHERS THEN
641 IF PG_DEBUG in ('Y', 'C') THEN
642 msc_sch_wb.atp_debug('Get_Mandatory_Components: Error in get mand comp := ' || sqlerrm);
643 END IF;
644
645 END;
646
647 IF PG_DEBUG in ('Y', 'C') THEN
648 msc_sch_wb.atp_debug('Get_Mandatory_Components: mand comp count := ' || x_mand_comp_info_rec.sr_inventory_item_id.count);
649 FOR i in 1..x_mand_comp_info_rec.sr_inventory_item_id.count LOOP
650 msc_sch_wb.atp_debug('Get_Mandatory_Components: i := ' || i);
651 msc_sch_wb.atp_debug('Get_Mandatory_Components: sr_inv_id := ' || x_mand_comp_info_rec.sr_inventory_item_id(i));
652 msc_sch_wb.atp_debug('Get_Mandatory_Components: quantity := ' || x_mand_comp_info_rec.quantity(i));
653 END LOOP;
654 msc_sch_wb.atp_debug('Get_Mandatory_Components: End Get_mandatory_components');
655 END IF;
656 EXCEPTION
657 WHEN OTHERS THEN
658 IF PG_DEBUG in ('Y', 'C') THEN
659 msc_sch_wb.atp_debug('Error in get mand comp := ' || sqlerrm);
660 END IF;
661 END Get_Mandatory_Components;
662
663 Procedure Validate_CTO_Sources (P_SOURCE_LIST IN OUT NOCOPY MRP_ATP_PVT.Atp_Source_Typ,
664 p_line_ids IN MRP_ATP_PUB.number_arr,
665 p_instance_id IN number,
666 p_session_id IN number,
667 x_return_status OUT NOCOPY varchar2)
668 IS
669
670 l_cto_source_list MRP_ATP_PVT.Atp_Source_Typ;
671
672 l_match_source_list MRP_ATP_PVT.Atp_Source_Typ;
673
674 l_count number;
675
676 l_parent_src_cntr number;
677 l_cto_source_cntr number;
678 l_cto_source_found number;
679 l_item_count number;
680 i number;
681
682 l_org_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
683 l_line_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
684 l_sup_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
685
686 BEGIN
687 IF PG_DEBUG in ('Y', 'C') THEN
688 msc_sch_wb.atp_debug('Validate CTO Source');
689 msc_sch_wb.atp_debug('p_line_ids.count := ' || p_line_ids.count);
690 FOR i in 1..P_SOURCE_LIST.organization_id.count LOOP
691 msc_sch_wb.atp_debug('Source # := ' || i);
692 msc_sch_wb.atp_debug('Organization_Id := ' || P_SOURCE_LIST.organization_id(i));
693 msc_sch_wb.atp_debug('Instance_Id := ' || P_SOURCE_LIST.Instance_Id(i));
694 msc_sch_wb.atp_debug('Supplier_Id := ' || P_SOURCE_LIST.Supplier_Id(i));
695 msc_sch_wb.atp_debug('Supplier_Site_Id := ' || P_SOURCE_LIST.Supplier_Site_Id(i));
696 msc_sch_wb.atp_debug('Rank := ' || P_SOURCE_LIST.rank(i));
697 END LOOP;
698 END IF;
699
700 IF p_line_ids.count = 1 THEN
701 IF PG_DEBUG in ('Y', 'C') THEN
702 msc_sch_wb.atp_debug('line count := 1');
703 END IF;
704 select organization_id,
705 tp_id,
706 partner_site_id,
707 make_flag
708 bulk collect into
709 l_cto_source_list.organization_id,
710 l_cto_source_list.supplier_id,
711 l_cto_source_list.supplier_site_id,
712 l_cto_source_list.make_flag
713 from
714 (
715 select mcs.organization_id,
716 /* bug 3628958: if null is directly used in union clause then error is raised that datatype do not match
717 null tp_id,
718 null partner_site_id,
719 */
720 to_number(null) tp_id,
721 to_number(null) partner_site_id,
722 make_flag
723 from msc_cto_sources mcs
724 where mcs.line_id = p_line_ids(1)
725 and mcs.sr_instance_id = p_instance_id
726 --and mcs.status_flag = 1
727 and mcs.session_id = p_session_id
728 and mcs.organization_id is not null
729
730 UNION ALL
731 --bug 3628958
732 --select null organization_id,
733 select to_number(null) organization_id,
734 mtil.tp_id,
735 mtps.partner_site_id,
736 make_flag
737 from msc_cto_sources mcs,
738 msc_tp_id_lid mtil,
739 msc_trading_partner_sites mtps
740 where mcs.line_id = p_line_ids(1)
741 and mcs.sr_instance_id = p_instance_id
742 --and mcs.status_flag = 1
743 and mcs.session_id = p_session_id
744 and mcs.supplier_id is not null
745 and mcs.supplier_site_code is not null
746 and mcs.supplier_id = mtil.sr_tp_id
747 and mtil.partner_type = 1
748 and mcs.sr_instance_id = mtil.sr_instance_id
749 and mtil.tp_id = mtps.partner_id
750 and mtps.partner_type = 1
751 and mcs.supplier_site_code = mtps.tp_site_code
752 );
753 /* select nvl(mcs.organization_id,0),
754 nvl(mtil.tp_id,0),
755 nvl(mtps.partner_site_id, 0),
756 make_flag
757 bulk collect into
758 l_cto_source_list.organization_id,
759 l_cto_source_list.supplier_id,
760 l_cto_source_list.supplier_site_id,
761 l_cto_source_list.make_flag
762 from msc_cto_sources mcs,
763 msc_tp_id_lid mtil,
764 msc_trading_partner_sites mtps
765 where mcs.line_id = p_line_ids(1)
766 and mcs.sr_instance_id = p_instance_id
767 and mcs.status_flag = 1
768 and ( mcs.organization_id is not null
769 or ( mcs.supplier_id is not null
770 and mcs.supplier_site_code is not null
771 and mcs.supplier_id = mtil.sr_tp_id
772 and mtil.partner_type = 1
773 and mcs.sr_instance_id = mtil.sr_instance_id
774 and mtil.tp_id = mtps.partner_id
775 and mtps.partner_type = 1
776 and mcs.supplier_site_code = mtps.tp_site_code
777 ));
778 */
779 IF PG_DEBUG in ('Y', 'C') THEN
780 msc_sch_wb.atp_debug('CTO sources count := ' || l_cto_source_list.organization_id.count);
781 END IF;
782
783 ELSE
784
785 IF PG_DEBUG in ('Y', 'C') THEN
786 msc_sch_wb.atp_debug('Number of line > 1');
787 END IF;
788
789 BEGIN
790 ---first find out how many items have OSS specific rules
791 select count(distinct mcs.line_id)
792 into l_item_count
793 from msc_cto_sources mcs,
794 msc_ship_set_temp msst
795 where mcs.line_id = msst.line_id
796 and mcs.sr_instance_id = p_instance_id
797 --and mcs.status_flag = 1;
798 and session_id = p_session_id;
799
800 EXCEPTION
801 WHEN NO_DATA_FOUND THEN
802 l_item_count := 0;
803 END;
804
805 IF PG_DEBUG in ('Y', 'C') THEN
806 msc_sch_wb.atp_debug('Number of lines having OSS rule := ' || l_item_count);
807 END IF;
808
809 --now select common orgs for the itmes which have OSS
810
811 IF l_item_count > 0 THEN
812
813 IF PG_DEBUG in ('Y', 'C') THEN
814 select mcs.line_id,
815 nvl(mcs.organization_id, -1),
816 mcs.supplier_id
817 bulk collect into
818 l_line_id,
819 l_org_id,
820 l_sup_id
821 from msc_cto_sources mcs,
822 msc_ship_set_temp msst
823 where mcs.line_id = msst.line_id
824 and mcs.sr_instance_id = p_instance_id
825 --and mcs.status_flag = 1;
826 and mcs.session_id = p_session_id;
827
828 FOR i in 1..l_line_id.count LOOP
829 msc_sch_wb.atp_debug(' OSS # := ' || i);
830 msc_sch_wb.atp_debug('Line id := ' || l_line_id(i));
831 msc_sch_wb.atp_debug(' Org := ' || l_org_id(i));
832 msc_sch_wb.atp_debug('sup id := ' || l_sup_id(i));
833 END LOOP;
834
835 END IF;
836 select nvl(mcs.organization_id,0),
837 null,
838 null,
839 null
840 bulk collect into
841 l_cto_source_list.organization_id,
842 l_cto_source_list.supplier_id,
843 l_cto_source_list.supplier_site_id,
844 l_cto_source_list.make_flag
845 from msc_cto_sources mcs,
846 msc_ship_set_temp msst
847 where mcs.line_id = msst.line_id
848 and mcs.sr_instance_id = p_instance_id
849 --and mcs.status_flag = 1
850 and mcs.session_id = p_session_id
851 and mcs.organization_id is not null
852 -- here we dont link on suppliers as we could have
853 --more than one item only at top level. Since drop ship is not supported
854 -- we can safely ignore suppliers
855 group by mcs.organization_id
856 having count(*) = l_item_count;
857 END IF;
858 IF PG_DEBUG in ('Y', 'C') THEN
859 msc_sch_wb.atp_debug('CTO sources count := ' || l_cto_source_list.organization_id.count);
860 END IF;
861
862 END IF;
863
864 IF PG_DEBUG in ('Y', 'C') THEN
865 FOR i in 1..l_cto_source_list.organization_id.count LOOP
866 msc_sch_wb.atp_debug('CTO Sources');
867 msc_sch_wb.atp_debug('Organization_id := ' || l_cto_source_list.organization_id(i));
868 msc_sch_wb.atp_debug('Supplier _ID := ' || l_cto_source_list.Supplier_Id(i));
869 msc_sch_wb.atp_debug('supplier Site Id := ' || l_cto_source_list.Supplier_site_id(i));
870 END LOOP;
871 END IF;
872
873 IF p_line_ids.count > 1 and l_item_count > 0 and l_cto_source_list.organization_id.count = 0 THEN
874
875 IF PG_DEBUG in ('Y', 'C') THEN
876 msc_sch_wb.atp_debug('Ship set, but no common OSS sources');
877 END IF;
878 --null out output table
879 P_SOURCE_LIST := l_match_source_list;
880 x_return_status := MSC_ATP_PVT.CTO_OSS_ERROR;
881
882 ELSIF l_cto_source_list.organization_id.count > 0 THEN
883 FOR l_parent_src_cntr in 1..p_source_list.organization_id.count LOOP
884 IF PG_DEBUG in ('Y', 'C') THEN
885 msc_sch_wb.atp_debug('l_parent_src_cntr := ' || l_parent_src_cntr);
886 msc_sch_wb.atp_debug('Model Source org := ' || p_source_list.organization_id(l_parent_src_cntr));
887 END IF;
888 FOR l_cto_source_cntr in 1..l_cto_source_list.organization_id.count LOOP
889
890 IF PG_DEBUG in ('Y', 'C') THEN
891 msc_sch_wb.atp_debug('l_cto_source_cntr := ' || l_cto_source_cntr);
892 msc_sch_wb.atp_debug('CTO Source org := ' || l_cto_source_list.organization_id(l_cto_source_cntr));
893 END IF;
894 IF ( p_source_list.organization_id(l_parent_src_cntr) =
895 l_cto_source_list.organization_id(l_cto_source_cntr) OR
896 (p_source_list.supplier_id(l_parent_src_cntr) =
897 l_cto_source_list.supplier_id(l_cto_source_cntr) AND
898 p_source_list.supplier_site_id(l_parent_src_cntr) =
899 l_cto_source_list.supplier_site_id(l_cto_source_cntr))) AND
900 p_source_list.instance_id(l_parent_src_cntr) = p_instance_id THEN
901
902 IF p_source_list.Source_Type(l_parent_src_cntr) = MSC_ATP_PVT.MAKE AND
903 NVL(l_cto_source_list.make_flag(l_cto_source_cntr), 'Y') = 'N' THEN
904
905 IF PG_DEBUG in ('Y', 'C') THEN
906 msc_sch_wb.atp_debug('Source Type := ' || p_source_list.Source_Type(l_parent_src_cntr));
907 msc_sch_wb.atp_debug('Make flag from CTO := ' ||l_cto_source_list.make_flag(l_cto_source_cntr));
908 msc_sch_wb.atp_debug('OSS Restricted source, cannot make in this org');
909 END IF;
910
911 ELSE
912
913 IF PG_DEBUG in ('Y', 'C') THEN
914 msc_sch_wb.atp_debug('Matching org found');
915 msc_sch_wb.atp_debug('Extend sources array and add org to it');
916 END IF;
917
918 --a matching source found
919 MSC_ATP_CTO.Extend_Sources_Rec(l_match_source_list);
920
921 l_count := l_match_source_list.Organization_Id.count;
922
923 l_match_source_list.Organization_Id(l_count) :=
924 p_source_list.Organization_Id(l_parent_src_cntr);
925 l_match_source_list.Instance_Id(l_count) :=
926 p_source_list.Instance_Id(l_parent_src_cntr);
927 l_match_source_list.Supplier_Id(l_count) :=
928 p_source_list.Supplier_Id(l_parent_src_cntr);
929 l_match_source_list.Supplier_Site_Id(l_count) :=
930 p_source_list.Supplier_Site_Id(l_parent_src_cntr);
931 l_match_source_list.Rank(l_count) :=
932 p_source_list.Rank(l_parent_src_cntr);
933 l_match_source_list.Source_Type(l_count) :=
934 p_source_list.Source_Type(l_parent_src_cntr);
935 l_match_source_list.Lead_Time(l_count) :=
936 p_source_list.Lead_Time(l_parent_src_cntr);
937 l_match_source_list.Ship_Method(l_count) :=
938 p_source_list.Ship_Method(l_parent_src_cntr);
939 l_match_source_list.Preferred(l_count) :=
940 p_source_list.Preferred(l_parent_src_cntr);
941
942 EXIT;
943
944 END IF;
945 END IF;
946 END LOOP;
947
948 END LOOP; -- FOR l_parent_src_cntr in 1..p_source_list.organization_id.count LOOP
949
950 P_SOURCE_LIST := l_match_source_list;
951 IF PG_DEBUG in ('Y', 'C') THEN
952 msc_sch_wb.atp_debug('Number of sources returned from validate CTO sources := '
953 || P_SOURCE_LIST.organization_id.count);
954 END IF;
955 IF l_match_source_list.organization_id.count = 0 THEN
956
957 x_return_status := MSC_ATP_PVT.CTO_OSS_ERROR;
958 END IF;
959
960 END IF; -- IF l_cto_source_list.organization_id.count >
961
962
963
964 END Validate_CTO_Sources;
965
966 Procedure Extend_Sources_Rec(P_Source_Rec IN OUT NOCOPY MRP_ATP_PVT.Atp_Source_Typ)
967 IS
968 BEGIN
969 P_Source_Rec.Organization_Id.extend;
970 P_Source_Rec.Instance_Id.extend;
971 P_Source_Rec.Supplier_Id.extend;
972 P_Source_Rec.Supplier_Site_Id.extend;
973 P_Source_Rec.Rank.extend;
974 P_Source_Rec.Source_Type.extend;
975 P_Source_Rec.Lead_Time.extend;
976 P_Source_Rec.Ship_Method.extend;
977 P_Source_Rec.Preferred.extend;
978 P_Source_Rec.make_flag.extend;
979 P_Source_Rec.Sup_Cap_Type.extend;
980 END Extend_Sources_Rec;
981
982 procedure Populate_Cto_Bom(p_session_id IN number,
983 p_refresh_number IN number,
984 p_dblink IN varchar2)
985 IS
986 l_dblink varchar2(30);
987 l_sql_stmt varchar2(1000);
988 BEGIN
989
990 IF PG_DEBUG in ('Y', 'C') THEN
991 msc_sch_wb.atp_debug('Inside Populate_Cto_Bom');
992 msc_sch_wb.atp_debug('p_session_id := ' || p_session_id);
993 END IF;
994
995 --delete data for old session
996 delete msc_cto_bom where session_id = p_session_id;
997 ---first insert into local database
998 insert into msc_cto_bom
999 (SR_INVENTORY_ITEM_ID,
1000 inventory_item_id,
1001 LINE_ID,
1002 TOP_MODEL_LINE_ID,
1003 ATO_PARENT_MODEL_LINE_ID,
1004 ATO_MODEL_LINE_ID,
1005 MATCH_ITEM_ID,
1006 WIP_SUPPLY_TYPE,
1007 SESSION_ID,
1008 BOM_ITEM_TYPE,
1009 QUANTITY,
1010 PARENT_LINE_ID,
1011 sr_instance_id,
1012 refresh_number)
1013 SELECT distinct
1014 mast.inventory_item_id sr_inventory_item_id,
1015 mil.inventory_item_id inventory_item_id,
1016 mast.ORDER_LINE_ID,
1017 mast.top_model_line_id,
1018 mast.ato_parent_model_line_id,
1019 mast.ato_model_line_id,
1020 mast.match_item_id,
1021 mast.wip_supply_type,
1022 mast.session_id,
1023 mast.BOM_ITEM_TYPE,
1024 mast.QUANTITY_ORDERED,
1025 mast.parent_line_id,
1026 MSC_ATP_PVT.G_INSTANCE_ID,
1027 p_refresh_number
1028 FROM mrp_atp_schedule_temp mast,
1029 msc_item_id_lid mil
1030 where session_id = p_session_id
1031 --bug 3378648
1032 and status_flag = 99
1033 and ato_model_line_id is not null -- transfer ATO model enteties only;
1034 and mil.sr_instance_id = mast.sr_instance_id (+)
1035 and mil.sr_inventory_item_id = mast.inventory_item_id (+);
1036 -- we need outer join just in case item is not collected
1037
1038 IF PG_DEBUG in ('Y', 'C') THEN
1039 msc_sch_wb.atp_debug('Rows Inserted := ' || SQL%ROWCOUNT);
1040 END IF;
1041
1042 IF p_dblink is not null THEN
1043 -- now transfer the data accross the database link
1044 --- in case of distributed transaction
1045 l_dblink := '@' || p_dblink;
1046 l_sql_stmt := 'Insert into Msc_CTO_Bom' || l_dblink;
1047 l_sql_stmt := l_sql_stmt ||
1048 ' Select * from Msc_CTO_Bom where session_id = :p_session_id';
1049
1050
1051 EXECUTE IMMEDIATE l_sql_stmt USING p_session_id;
1052 END IF;
1053
1054
1055 END Populate_Cto_Bom;
1056
1057
1058 Procedure Get_CTO_BOM(p_session_id IN NUMBER,
1059 p_comp_rec OUT NOCOPY MRP_ATP_PVT.Atp_Comp_Typ,
1060 p_line_id IN NUMBER,
1061 p_request_date IN DATE,
1062 p_request_quantity IN NUMBER,
1063 p_parent_so_quantity IN NUMBER,
1064 p_inventory_item_id IN NUMBER,
1065 p_organization_id IN NUMBER,
1066 p_plan_id IN NUMBER,
1067 p_instance_id IN NUMBER,
1068 p_fixed_lt IN NUMBER,
1069 p_variable_lt IN NUMBER)
1070 IS
1071
1072 l_lead_time number;
1073 l_mso_lead_time_factor number;
1074 i number;
1075 l_process_seq_id NUMBER; --4929084
1076 l_routing_seq_id NUMBER;
1077 l_bill_seq_id NUMBER;
1078 l_op_seq_id NUMBER;
1079 l_return_status VARCHAR2(1);
1080 l_inventory_item_id NUMBER;
1081
1082 BEGIN
1083
1084 IF PG_DEBUG in ('Y', 'C') THEN
1085 msc_sch_wb.atp_debug('Get_CTO_BOM: Inside get_cto_bom');
1086 msc_sch_wb.atp_debug('Get_CTO_BOM: p_line_id := ' || p_line_id);
1087 msc_sch_wb.atp_debug('Get_CTO_BOM: p_request_date := ' || p_request_date);
1088 msc_sch_wb.atp_debug('Get_CTO_BOM: p_request_quantity := ' || p_request_quantity);
1089 msc_sch_wb.atp_debug('Get_CTO_BOM: p_parent_so_quantity := ' || p_parent_so_quantity);
1090 msc_sch_wb.atp_debug('Get_CTO_BOM: p_inventory_item_id := ' || p_inventory_item_id);
1091 msc_sch_wb.atp_debug('Get_CTO_BOM: p_organization_id := ' || p_organization_id);
1092 msc_sch_wb.atp_debug('Get_CTO_BOM: p_plan_id := ' || p_plan_id);
1093 msc_sch_wb.atp_debug('Get_CTO_BOM: p_instance_id := ' || p_instance_id);
1094 msc_sch_wb.atp_debug('Get_CTO_BOM: p_fixed_lt := ' || p_fixed_lt);
1095 msc_sch_wb.atp_debug('Get_CTO_BOM: p_variable_lt := ' || p_variable_lt);
1096 msc_sch_wb.atp_debug('Get_CTO_BOM: p_session_id := ' || p_session_id);
1097 END IF;
1098 --first get the lead time from msc_system_itmes
1099 --- this query can't be put with the query below there are no common linking columns
1100 /* BEGIN
1101 select fixed_lead_time, variable_lead_time
1102 into l_fixed_lt, l_variable_lt
1103 from msc_system_items
1104 where plan_id = p_plan_id
1105 and sr_instance_id = p_instance_id
1106 and sr_inventory_item_id = p_inventory_item_id
1107 and organization_id = p_organization_id;
1108 EXCEPTION
1109 WHEN OTHERS THEN
1110 l_fixed_lt := 0;
1111 l_variable_lt := 0;
1112 END;
1113 */
1114
1115 --4929084
1116 l_inventory_item_id := MSC_ATP_FUNC. get_inv_item_id(p_instance_id,
1117 p_inventory_item_id,
1118 null,
1119 p_organization_id);
1120
1121 IF PG_DEBUG in ('Y', 'C') THEN
1122 msc_sch_wb.atp_debug('Get_CTO_BOM: l_inventory_item_id := ' || l_inventory_item_id);
1123 END IF;
1124
1125 ---- Now get the process effectivity
1126 MSC_ATP_PROC.get_process_effectivity(
1127 p_plan_id,
1128 l_inventory_item_id,
1129 p_organization_id,
1130 p_instance_id,
1131 p_request_date,
1132 p_request_quantity,
1133 l_process_seq_id,
1134 l_routing_seq_id,
1135 l_bill_seq_id,
1136 l_op_seq_id, --4570421
1137 l_return_status);
1138
1139
1140 l_mso_lead_time_factor := MSC_ATP_PVT.G_MSO_LEAD_TIME_FACTOR;
1141
1142 l_lead_time := CEIL((NVL(p_fixed_lt,0) + NVL(p_variable_lt, 0)* p_request_quantity)*
1143 (1 + l_mso_lead_time_factor));
1144
1145 IF PG_DEBUG in ('Y', 'C') THEN
1146 msc_sch_wb.atp_debug('Get_CTO_BOM: l_lead_time := ' || l_lead_time);
1147 msc_sch_wb.atp_debug('Get_CTO_BOM: G_INSTANCE_ID := ' || MSC_ATP_PVT.G_INSTANCE_ID);
1148 END IF;
1149
1150 SELECT mcb.sr_INVENTORY_ITEM_ID,
1151 (mcb.quantity / p_parent_so_quantity) * p_request_quantity ,
1152 c2.calendar_date,
1153 l_lead_time,
1154 mcb.wip_supply_type,
1155 mcb.LINE_ID,
1156 mcb.parent_line_id,
1157 mcb.TOP_MODEL_LINE_ID,
1158 mcb.ATO_PARENT_MODEL_LINE_ID,
1159 mcb.ATO_MODEL_LINE_ID,
1160 mcb.MATCH_ITEM_ID,
1161 mcb.BOM_ITEM_TYPE,
1162 mcb.quantity,
1163 NVL(msi.fixed_lead_time, 0),
1164 NVL(msi.variable_lead_time, 0),
1165 mcb.oss_error_code,
1166 msi.atp_flag,
1167 msi.atp_components_flag,
1168 msi.aggregate_time_fence_date, -- For time_phased_atp
1169 msi.inventory_item_id,
1170 msi.uom_code, --bug3110023
1171 mbc.usage_quantity*mbc.component_yield_factor, --4775920
1172 NVL (MSC_ATP_PVT.G_ORG_INFO_REC.org_type,1) --4775920
1173 BULK COLLECT INTO
1174 p_comp_rec.inventory_item_id,
1175 p_comp_rec.comp_usage,
1176 p_comp_rec.requested_date,
1177 p_comp_rec.lead_time,
1178 p_comp_rec.wip_supply_type,
1179 p_comp_rec.assembly_identifier,
1180 p_comp_rec.parent_line_id,
1181 p_comp_rec.TOP_MODEL_LINE_ID,
1182 p_comp_rec.ATO_PARENT_MODEL_LINE_ID,
1183 p_comp_rec.ATO_MODEL_LINE_ID,
1184 p_comp_rec.MATCH_ITEM_ID,
1185 p_comp_rec.BOM_ITEM_TYPE,
1186 p_comp_rec.parent_so_quantity,
1187 p_comp_rec.fixed_lt,
1188 p_comp_rec.variable_lt,
1189 p_comp_rec.oss_error_code,
1190 p_comp_rec.atp_flag,
1191 p_comp_rec.atp_components_flag,
1192 p_comp_rec.atf_date, -- For time_phased_atp
1193 p_comp_rec.dest_inventory_item_id,
1194 p_comp_rec.comp_uom, --bug3110023
1195 p_comp_rec.usage_qty, --4775920
1196 p_comp_rec.organization_type --4775920
1197 FROM msc_cto_bom mcb,
1198 msc_calendar_dates c1,
1199 msc_calendar_dates c2,
1200 msc_trading_partners tp,
1201 msc_system_items msi,
1202 msc_bom_components mbc,
1203 MSC_OPERATION_COMPONENTS MOC
1204 WHERE mcb.session_id = p_session_id
1205 AND mcb.sr_instance_id = MSC_ATP_PVT.G_INSTANCE_ID -- this is the instance id of the calling module
1206 AND mcb.PARENT_LINE_ID = p_line_id
1207 AND mcb.sr_inventory_item_id = msi.sr_inventory_item_id (+)
1208 AND p_organization_id = msi.organization_id (+)
1209 AND p_instance_id = msi.sr_instance_id (+)
1210 AND p_plan_id = msi.plan_id(+)
1211 ---bug 3644238: truncate date else appropriate date wouldn't be found in msc_calendar tables.
1212 AND c1.calendar_date = trunc(p_request_date)
1213 AND c1.sr_instance_id = tp.sr_instance_id
1214 AND c1.calendar_code = tp.calendar_code
1215 AND c1.exception_set_id = tp.calendar_exception_set_id
1216 AND tp.sr_instance_id = p_instance_id -- instance id of the org id from which we are calling
1217 AND tp.sr_tp_id = p_organization_id
1218 AND tp.partner_type = 3
1219 AND c2.seq_num = c1.prior_seq_num - l_lead_time
1220 AND c2.calendar_code = tp.calendar_code
1221 AND c2.sr_instance_id = tp.sr_instance_id
1222 AND c2.exception_set_id = tp.calendar_exception_set_id
1223 and mbc.inventory_item_id = msi.inventory_item_id ---4570421
1224 and mbc.plan_id = msi.plan_id
1225 and mbc.sr_instance_id = msi.sr_instance_id
1226 and mbc.bill_sequence_id = l_bill_seq_id
1227 and MOC.PLAN_ID(+) = p_plan_id --4929084
1228 and MOC.SR_INSTANCE_ID(+) = p_instance_id
1229 and MOC.ORGANIZATION_ID(+) = p_organization_id
1230 and MOC.BILL_SEQUENCE_ID(+) = l_bill_seq_id
1231 and MOC.ROUTING_SEQUENCE_ID(+) = l_routing_seq_id
1232 and MOC.COMPONENT_SEQUENCE_ID(+) = mbc.COMPONENT_SEQUENCE_ID
1233 and MOC.OPERATION_SEQUENCE_ID(+) = l_op_seq_id;
1234
1235 IF PG_DEBUG in ('Y', 'C') THEN
1236 msc_sch_wb.atp_debug('Get_CTO_BOM: components retrieved := ' || p_comp_rec.inventory_item_id.count);
1237 FOR i in 1..p_comp_rec.inventory_item_id.count LOOP
1238 msc_sch_wb.atp_debug('Get_CTO_BOM: Component # ' || i || ': ' || p_comp_rec.inventory_item_id(i));
1239 msc_sch_wb.atp_debug('Get_CTO_BOM: fixed lead time :=' || p_comp_rec.fixed_lt(i));
1240 msc_sch_wb.atp_debug('Get_CTO_BOM: variable lead time := ' || p_comp_rec.variable_lt(i));
1241 msc_sch_wb.atp_debug('Get_CTO_BOM: uom code := ' || p_comp_rec.comp_uom(i)); --bug3110023
1242 END LOOP;
1243 msc_sch_wb.atp_debug('Get_CTO_BOM: END get_cto_bom');
1244 END IF;
1245
1246 END Get_CTO_BOM;
1247
1248 Procedure Maintain_OS_Sourcing(p_instance_id IN Number,
1249 p_atp_rec IN MRP_ATP_PUB.atp_rec_typ,
1250 p_status IN Number)
1251 IS
1252 i number;
1253 BEGIN
1254
1255 IF p_status = MSC_ATP_CTO.Success THEN
1256 -- delete the old data
1257 FORALL i in 1..p_atp_rec.inventory_item_id.count
1258 Delete from msc_cto_sources
1259 where sr_instance_id = p_instance_id
1260 and ato_line_id = p_atp_rec.identifier(i)
1261 and status_flag = 2;
1262
1263 ELSIF p_status = MSC_ATP_CTO.FAIL THEN
1264 --first delete the new data
1265 FORALL i in 1..p_atp_rec.inventory_item_id.count
1266 Delete from msc_cto_sources
1267 where sr_instance_id = p_instance_id
1268 and ato_line_id = p_atp_rec.identifier(i)
1269 and status_flag = 1;
1270
1271 --update the status flag to 1 on the old data
1272 FORALL i in 1..p_atp_rec.inventory_item_id.count
1273 UPDATE msc_cto_sources
1274 set status_flag = 1
1275 where sr_instance_id = p_instance_id
1276 and ato_line_id = p_atp_rec.identifier(i)
1277 and status_flag = 2;
1278
1279 END IF;
1280
1281 END Maintain_OS_Sourcing;
1282
1283
1284 PROCEDURE Check_Base_Model_For_Cap_Check(p_config_inventory_item_id IN NUMBER,
1285 p_base_model_id IN NUMBER,
1286 p_request_date IN DATE,
1287 p_instance_id IN NUMBER,
1288 p_plan_id IN NUMBER,
1289 p_organization_id IN NUMBER,
1290 p_quantity IN NUMBER,
1291 x_model_sr_inv_id OUT NOCOPY NUMBER,
1292 x_check_model_capacity_flag OUT NOCOPY NUMBER)
1293
1294 IS
1295
1296 l_process_seq_id number;
1297 l_routing_seq_id number;
1298 l_bill_seq_id number;
1299 l_op_seq_id number; --4570421
1300 l_return_status varchar2(1);
1301 l_atp_flag varchar2(1);
1302 l_atp_comp_flag varchar2(1);
1303
1304 BEGIN
1305 IF PG_DEBUG in ('Y', 'C') THEN
1306 msc_sch_wb.atp_debug('Inside Check_Base_Model_For_Cap_Check');
1307 END IF;
1308 --first get base model's flags
1309 Select atp_flag, atp_components_flag, sr_inventory_item_id
1310 into l_atp_flag, l_atp_comp_flag, x_model_sr_inv_id
1311 from msc_system_items msi
1312 where msi.inventory_item_id = p_base_model_id
1313 and msi.sr_instance_id = p_instance_id
1314 and msi.plan_id = p_plan_id
1315 and msi.organization_id = p_organization_id;
1316
1317 IF PG_DEBUG in ('Y', 'C') THEN
1318 msc_sch_wb.atp_debug('ATP flag for base model is := ' || l_atp_flag );
1319 msc_sch_wb.atp_debug('ATP comp flag for base model is := ' || l_atp_comp_flag);
1320 END IF;
1321
1322 IF NOT (l_atp_flag = 'Y' and l_atp_comp_flag = 'N') THEN
1323
1324 IF PG_DEBUG in ('Y', 'C') THEN
1325 msc_sch_wb.atp_debug('ATP Flag for model is set to not check just the model capacity');
1326 END IF;
1327 x_check_model_capacity_flag := 2;
1328 ELSE
1329 IF PG_DEBUG in ('Y', 'C') THEN
1330 msc_sch_wb.atp_debug('ATP Flag for model is set to check the model capacity');
1331 msc_sch_wb.atp_debug('Check Model bom level attribute');
1332 END IF;
1333
1334 ---- Now get the process effectivity
1335 MSC_ATP_PROC.get_process_effectivity(
1336 p_plan_id,
1337 p_config_inventory_item_id,
1338 p_organization_id,
1339 p_instance_id,
1340 p_request_date,
1341 p_quantity,
1342 l_process_seq_id,
1343 l_routing_seq_id,
1344 l_bill_seq_id,
1345 l_op_seq_id, --4570421
1346 l_return_status);
1347
1348 IF PG_DEBUG in ('Y', 'C') THEN
1349
1350 msc_sch_wb.atp_debug('After Selecting process effectivity');
1351 msc_sch_wb.atp_debug('l_process_seq_id := ' || l_process_seq_id);
1352 msc_sch_wb.atp_debug('l_routing_seq_id := ' || l_routing_seq_id);
1353 msc_sch_wb.atp_debug('l_bill_seq_id := ' || l_bill_seq_id);
1354 msc_sch_wb.atp_debug('l_op_seq_id := ' || l_op_seq_id);
1355 msc_sch_wb.atp_debug('l_return_status := ' || l_return_status);
1356
1357 END IF;
1358
1359 ---now select the bom level atp flag to see if we need need to do capacity check
1360 SELECT NVL(mbc.atp_flag, 2)
1361 INTO x_check_model_capacity_flag
1362 from msc_bom_components mbc
1363 where mbc.BILL_SEQUENCE_ID = l_bill_seq_id
1364 and mbc.PLAN_ID = p_plan_id
1365 and mbc.SR_INSTANCE_ID = p_instance_id
1366 and mbc.ORGANIZATION_ID = p_organization_id
1367 and mbc.INVENTORY_ITEM_ID = p_base_model_id;
1368
1369 IF PG_DEBUG in ('Y', 'C') THEN
1370 msc_sch_wb.atp_debug('After Selectng bom level atp flag');
1371 msc_sch_wb.atp_debug('x_check_model_capacity_flag := ' || x_check_model_capacity_flag);
1372 END IF;
1373
1374
1375 END IF;
1376
1377 IF PG_DEBUG in ('Y', 'C') THEN
1378 msc_sch_wb.atp_debug('END Check_Base_Model_For_Cap_Check');
1379 END IF;
1380
1381 EXCEPTION
1382 WHEN NO_DATA_FOUND THEN
1383
1384 IF PG_DEBUG in ('Y', 'C') THEN
1385 msc_sch_wb.atp_debug('No data found in Check_Base_Model_For_Capacity_Check');
1386 END IF;
1387
1388 x_check_model_capacity_flag := 2;
1389
1390 END Check_Base_Model_For_Cap_Check;
1391
1392 END MSC_ATP_CTO;
[Home] [Help]