[Home] [Help]
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(
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
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:
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
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 *****');
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
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
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';
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,
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
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;
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:
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;
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
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.
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
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
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:
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';
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
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 ;
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:
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 ;
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);
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
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);
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));
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));
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));
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));
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;
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:
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;
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
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:
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
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,
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
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:
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));
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));
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));
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));
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));
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));
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;
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;
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.
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;
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');
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
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);
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 '
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');
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:
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 */
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
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
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
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
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,
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');
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;
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;
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);
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);
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);
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
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
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;
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
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
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(
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
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,
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:
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));
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');
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;
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
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
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,
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));
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));
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));
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));
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;
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;
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:
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
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,
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:
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
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:
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;
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:
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;
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),
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:
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;
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;
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:
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
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:
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
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
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) =
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:
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
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:
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
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);
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:
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
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;
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
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);
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);
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);
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);
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);
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);
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);
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);
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;
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
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
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
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(
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,
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 ,
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));
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;
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');
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;
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:
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:
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
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
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:
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
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
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(
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);
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);
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:
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;
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:
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
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:
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;
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
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: