[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) AND (nvl(p_atp_rec.attribute_02(1), -1) <> 3) 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) 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);
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
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 *****');
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
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
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';
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,
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
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;
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:
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;
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
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.
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
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
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:
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';
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
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 ;
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:
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 ;
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);
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
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);
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));
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));
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));
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));
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;
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:
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;
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
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:
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
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,
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
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:
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));
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));
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));
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));
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));
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));
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;
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;
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.
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;
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');
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
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);
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 '
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');
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:
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 */
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
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
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
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
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,
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');
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;
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;
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);
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);
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);
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
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
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;
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
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
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(
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
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,
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:
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));
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');
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;
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
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
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,
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));
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));
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));
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));
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;
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;
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:
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
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,
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:
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
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:
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;
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:
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;
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),
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:
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;
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;
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:
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
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:
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
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
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) =
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:
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
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:
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
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);
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:
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
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;
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
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);
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);
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);
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);
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);
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);
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);
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);
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;
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
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
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
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(
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,
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 ,
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));
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;
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');
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;
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:
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:
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
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
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:
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
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
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(
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);
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);
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:
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;
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:
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
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:
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;
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
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: