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