1 PACKAGE BODY MSC_ATP_PUB AS
2 /* $Header: MSCEATPB.pls 120.15 2007/12/12 10:25:46 sbnaik ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_ATP_PUB';
4 NO_APS_INSTANCE CONSTANT INTEGER := 140;
5 PROF_TBL_NOT_IN_SYNC CONSTANT INTEGER := 170;
6 G_INV_CTP NUMBER := FND_PROFILE.value('INV_CTP');
7 G_CTO_FLAG NUMBER := 0;
8 G_CALL_ATP number :=2; --4421391, the flag will track whether debug/session is set or not.
9 --G_ATP_CHECK VARCHAR2(1) := 'N'; /* Bug 2249504 */
10 G_ATP_BOM_REC MRP_ATP_PUB.ATP_BOM_Rec_Typ;
11 --G_DB_PROFILE VARCHAR2(128); bug3049003 changed from G_DB_PROFILE to l_a2m_dblink
12
13 -- This package contains 2 public procedures : Call_ATP and Call_ATP_No_Commit.
14 -- Call_ATP and Call_ATP_No_Commit are almost the same except
15 -- Call ATP is a automonous transaction which will commit the data.
16 -- Call_ATP_No_Commit will be used by backlog scheduling and Call_ATP will be
17 -- used by OM and all the other caller. In order to maintain this package
18 -- easier, Call_ATP actually calls the Call_ATP_No_Commit and then do a commit
19
20
21
22 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
23
24 PROCEDURE Check_CTO(
25 p_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
26 p_session_id IN NUMBER,
27 --p_db_profile IN VARCHAR2,
28 --p_atp_check IN OUT NoCopy VARCHAR2,
29 --x_atp_bom_rec OUT NoCopy MRP_ATP_PUB.ATP_BOM_Rec_Typ,
30 x_return_status OUT NoCopy VARCHAR2,
31 x_msg_count OUT NoCopy NUMBER,
32 x_msg_data OUT NoCopy VARCHAR2 )
33 IS
34
35
36 BEGIN
37 --null out this procedure as it is not required after CTO rearchitecture project
38 null;
39 EXCEPTION
40 WHEN others THEN
41 null;
42 x_return_status := NVL(x_return_status, FND_API.G_RET_STS_ERROR);
43 --ROLLBACK;
44 END Check_CTO;
45 Procedure Update_Custom_Information(p_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ,
46 p_session_id IN NUMBER
47 )
48 IS
49 i number;
50 BEGIN
51 IF PG_DEBUG in ('Y', 'C') THEN
52 msc_sch_wb.atp_debug('Enter Update_Custom_Information');
53 END IF;
54
55 FORALL i in 1..p_atp_rec.inventory_item_id.count
56 update mrp_atp_schedule_temp
57 Set
58 inventory_item_name = p_atp_rec.inventory_item_name(i),
59 source_organization_id = p_atp_rec.source_organization_id(i),
60 source_organization_code = p_atp_rec.source_organization_code(i),
61 delivery_lead_time = p_atp_rec.delivery_lead_time(i),
62 freight_carrier = p_atp_rec.freight_carrier(i),
63 ship_method = p_atp_rec.ship_method(i),
64 scheduled_ship_date = p_atp_rec.ship_date(i), -- different
65 available_quantity = p_atp_rec.available_quantity(i),
66 requested_date_quantity = p_atp_rec.requested_date_quantity(i),
67 group_ship_date = p_atp_rec.group_ship_date(i),
68 group_arrival_date = p_atp_rec.group_arrival_date(i),
69 error_code = p_atp_rec.error_code(i),
70 end_pegging_id = p_atp_rec.end_pegging_id(i),
71 scheduled_arrival_date = p_atp_rec.arrival_date(i),
72 request_item_id = p_atp_rec.request_item_id(i),
73 request_item_name = p_atp_rec.request_item_name(i),
74 req_item_req_date_qty = p_atp_rec.req_item_req_date_qty(i),
75 req_item_available_date_qty = p_atp_rec.req_item_available_date_qty(i),
76 req_item_available_date = p_atp_rec.req_item_available_date(i),
77 sales_rep = p_atp_rec.sales_rep(i),
78 customer_contact = p_atp_rec.customer_contact(i)
79
80 WHERE session_id = p_session_id
81 and order_line_id = p_atp_rec.identifier(i)
82 --same line id may be shared by different items in case of ATO. So we add followig condition
83 and inventory_item_id = p_atp_rec.inventory_item_id(i);
84
85 IF PG_DEBUG in ('Y', 'C') THEN
86 msc_sch_wb.atp_debug('Number of rows update := ' ||SQL%ROWCOUNT);
87 msc_sch_wb.atp_debug('Exit Update_Custom_Information');
88 END IF;
89 Exception
90 WHEN OTHERS THEN
91 IF PG_DEBUG in ('Y', 'C') THEN
92 msc_sch_wb.atp_debug('Error Orrured in Update_Custom_Information := ' || SQLERRM);
93 END IF;
94
95 END Update_Custom_Information;
96
97
98 -- Added by ngoel on 6/15/2001 as post atp CTO processing needs to be part of calling module's
99 -- transaction and not of ATP autonomous transaction to maintain correct Demand picture.
100
101 PROCEDURE post_atp_cto(
102 p_session_id IN NUMBER,
103 p_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
104 x_return_status OUT NoCopy VARCHAR2,
105 x_msg_data OUT NoCopy VARCHAR2,
106 x_msg_count OUT NoCopy NUMBER
107 )
108 IS
109 BEGIN
110 --null out procedure as it is not required after CTO rearchitecture procedure
111 null;
112 EXCEPTION
113 WHEN others THEN
114 null;
115 END post_atp_cto;
116
117 --4421391
118 PROCEDURE enable_trace(
119 x_return_status OUT NoCopy NUMBER
120 )
121 IS
122 C INTEGER;
123 STATEMENT VARCHAR2(255);
124 ROWS_PROCESSED INTEGER;
125
126 BEGIN
127 IF PG_DEBUG in ('Y', 'C') THEN
128 msc_sch_wb.atp_debug('enable_trace: ' || 'Database Trace being enabled');
129 END IF;
130 C := DBMS_SQL.OPEN_CURSOR;
131 -- STATEMENT := 'ALTER SESSION SET SQL_TRACE=TRUE';
132 STATEMENT := 'ALTER SESSION SET events ' || '''' || '10046 trace name context forever, level 12' || '''' ;
133 DBMS_SQL.PARSE(C, STATEMENT, DBMS_SQL.NATIVE);
134 ROWS_PROCESSED := DBMS_SQL.EXECUTE(C);
135 DBMS_SQL.CLOSE_CURSOR(C);
136 EXCEPTION
137 WHEN others THEN
138 x_return_status :=-1;
139 IF PG_DEBUG in ('Y', 'C') THEN
140 msc_sch_wb.atp_debug('something wrong in enable_trace : ' || sqlcode);
141 msc_sch_wb.atp_debug('enable_trace: ' || sqlerrm);
142 END IF;
143 x_return_status := NVL(x_return_status, FND_API.G_RET_STS_ERROR);
144 END enable_trace;
145
146 PROCEDURE disable_trace(
147 x_return_status OUT NoCopy NUMBER
148 )
149 IS
150
151 C INTEGER;
152 STATEMENT VARCHAR2(255);
153 ROWS_PROCESSED INTEGER;
154 BEGIN
155 IF PG_DEBUG in ('Y', 'C') THEN
156 msc_sch_wb.atp_debug('disable_trace: ' || 'Database Trace disabled');
157 END IF;
158 C := DBMS_SQL.OPEN_CURSOR;
159 STATEMENT := 'ALTER SESSION SET SQL_TRACE=FALSE';
160 DBMS_SQL.PARSE(C, STATEMENT, DBMS_SQL.NATIVE);
161 ROWS_PROCESSED := DBMS_SQL.EXECUTE(C);
162 DBMS_SQL.CLOSE_CURSOR(C);
163 EXCEPTION
164 WHEN others THEN
165 x_return_status :=-1;
166 IF PG_DEBUG in ('Y', 'C') THEN
167 msc_sch_wb.atp_debug('something wrong in disable_trace : ' || sqlcode);
168 msc_sch_wb.atp_debug('disable_trace: ' || sqlerrm);
169 END IF;
170 x_return_status := NVL(x_return_status, FND_API.G_RET_STS_ERROR);
171 END disable_trace; --4421391
172
173 Procedure Subst_Workflow(p_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ)
174
175 IS
176 l_sales_rep VARCHAR2(250);
177 l_customer_contact VARCHAR2(250);
178 i NUMBER;
179 /* bug 4434875: rewrite query useing base tables
180 CURSOR C_CUSTCNT(p_cust_id number,
181 p_cust_site_id number) IS
182 select wf.name
183 from ra_site_uses_all rsua,
184 wf_roles wf,
185 fnd_user fnd,
186 ra_contacts ra
187 where ra.customer_id = p_cust_id
188 and rsua.site_use_id = p_cust_site_id
189 and ra.address_id = rsua.address_id
190 and ra.contact_id = fnd.customer_id
191 and fnd.start_date <= sysdate
192 and ( fnd.end_date IS NULL OR fnd.end_date >= trunc(sysdate))
193 and wf.orig_system = 'FND_USR'
194 and wf.orig_system_id = fnd.user_id
195 and wf.STATUS = 'ACTIVE';
196
197 */
198 CURSOR C_CUSTCNT(p_cust_id number,
199 p_cust_site_id number) IS
200 select wf.name
201 from wf_roles wf,
202 fnd_user fnd,
203 hz_cust_account_roles hcar
204 where hcar.cust_account_id = p_cust_id
205 and hcar.cust_acct_site_id = p_cust_site_id
206 and hcar.cust_account_role_id = fnd.customer_id
207 and fnd.start_date <= trunc(sysdate)
208 and ( fnd.end_date IS NULL OR fnd.end_date >= trunc(sysdate))
209 and wf.orig_system = 'FND_USR'
210 and wf.orig_system_id = fnd.user_id
211 and wf.STATUS = 'ACTIVE';
212
213 BEGIN
214 IF PG_DEBUG in ('Y', 'C') THEN
215 msc_sch_wb.atp_debug('Subst_Workflow: ' || '************** Product Subst Workflow **********');
216 END IF;
217
218 -- initiate workflow for product substitution
219 FOR i in 1..p_atp_rec.inventory_item_id.count LOOP
220 IF PG_DEBUG in ('Y', 'C') THEN
221 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'Subst flag := ' || NVL(p_atp_rec.subst_flag(i),2));
222 END IF;
223 IF NVL(p_atp_rec.subst_flag(i),2) = 1 and p_atp_rec.ACTION(i) <> 100 THEN
224
225 l_sales_rep := p_atp_rec.sales_rep(i);
226 l_customer_contact := p_atp_rec.customer_contact(i);
227 IF NVL(l_sales_rep, '@@@') = '@@@' AND NVL(p_atp_rec.calling_module(i), -99) = 660 THEN
228 IF PG_DEBUG in ('Y', 'C') THEN
229 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'Get sales rep');
230 END IF;
231 BEGIN
232
233 select min(wf.name)
234 into l_sales_rep
235 from wf_roles wf,
236 oe_order_lines_all oe
237 where oe.line_id = p_atp_rec.identifier(i)
238 and wf.orig_system= 'PER'
239 and wf.orig_system_id= oe.salesrep_id
240 and wf.status='ACTIVE';
241 EXCEPTION
242 WHEN OTHERS THEN
243 l_sales_rep := null;
244 END;
245 END IF;
246
247 IF PG_DEBUG in ('Y', 'C') THEN
248 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'customer id := ' || p_atp_rec.customer_id(i));
249 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'customer site id := ' || p_atp_rec.customer_site_id(i));
250 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'l_customer_contact := ' || NVL(l_customer_contact, '@@'));
251 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'calling_module := ' || NVL(p_atp_rec.calling_module(i), -99));
252 END IF;
253
254 IF NVL(l_customer_contact, '@@@') = '@@@' AND NVL(p_atp_rec.calling_module(i), -99) = 660 THEN
255 IF PG_DEBUG in ('Y', 'C') THEN
256 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'Get Customer conatct');
257 END IF;
258 OPEN C_CUSTCNT(p_atp_rec.customer_id(i), p_atp_rec.customer_site_id(i));
259 FETCH C_CUSTCNT INTO l_customer_contact;
260 CLOSE C_CUSTCNT;
261 END IF;
262 IF PG_DEBUG in ('Y', 'C') THEN
263 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'l_customer_contact := ' || l_customer_contact);
264 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'l_sales_rep := ' || l_sales_rep);
265 END IF;
266 IF NVL(l_sales_rep, '-99') <> '-99' or NVL(l_customer_contact, '-99') <> '-99' THEN
267 IF PG_DEBUG in ('Y', 'C') THEN
268 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'Initiate Substitute workflow');
269 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'request_item_name := ' || p_atp_rec.request_item_name(i));
270 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'inventory_item_name := ' || p_atp_rec.inventory_item_name(i));
271 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'order_number := '|| p_atp_rec.order_number(i));
272 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'identifier := ' || p_atp_rec.identifier(i));
273 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'Source_Organization_Code := ' || p_atp_rec.Source_Organization_Code(i));
274 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'quantity_ordered := ' || p_atp_rec.quantity_ordered(i));
275 END IF;
276 BEGIN
277 mrp_msc_exp_wf.start_substitute_workflow(
278 p_atp_rec.request_item_name(i),
279 p_atp_rec.inventory_item_name(i),
280 p_atp_rec.order_number(i),
281 p_atp_rec.identifier(i),
282 p_atp_rec.Source_Organization_Code(i),
283 p_atp_rec.Source_Organization_Code(i),
284 p_atp_rec.quantity_ordered(i),
285 p_atp_rec.quantity_ordered(i),
286 l_sales_rep,
287 l_customer_contact);
288 EXCEPTION
289 WHEN OTHERS THEN
290 null;
291 END;
292 END IF;
293
294 END IF;
295 END LOOP;
296 IF PG_DEBUG in ('Y', 'C') THEN
297 msc_sch_wb.atp_debug('Subst_Workflow: ' || 'End Subst workflow');
298 END IF;
299 END Subst_Workflow;
300
301
302 PROCEDURE Call_ATP_Commit (
303 p_session_id IN OUT NoCopy NUMBER,
304 p_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ,
305 x_atp_rec OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
306 x_atp_supply_demand OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
307 x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
308 x_atp_details OUT NOCOPY MRP_ATP_PUB.ATP_Details_Typ,
309 x_return_status OUT NoCopy VARCHAR2,
310 x_msg_data OUT NoCopy VARCHAR2,
311 x_msg_count OUT NoCopy NUMBER
312 ) IS
313 PRAGMA AUTONOMOUS_TRANSACTION;
314 i PLS_INTEGER;
315 cursor_name INTEGER;
316 rows_processed INTEGER;
317 l_count number;
318 l_a2m_dblink VARCHAR2(80); --bug3049003
319 l_instance_id number;--bug3049003
320 l_return_status VARCHAR2(60);--bug3049003
321 --l_db_profile VARCHAR2(128);
322 DBLINK_NOT_OPEN EXCEPTION;
323 PRAGMA EXCEPTION_INIT(DBLINK_NOT_OPEN, -2081);
324
325 BEGIN
326 IF PG_DEBUG in ('Y', 'C') THEN
327 msc_sch_wb.atp_debug('Begin Call_ATP_Commit');
328 END IF;
329
330 i := p_atp_rec.Calling_Module.FIRST;
331 IF i IS NOT NULL THEN
332 Call_ATP_No_Commit( p_session_id,
333 p_atp_rec,
334 x_atp_rec,
335 x_atp_supply_demand,
336 x_atp_period,
337 x_atp_details,
338 x_return_status,
339 x_msg_data,
340 x_msg_count);
341 END IF;
342
343 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
344 -- something wrong so we want to rollback;
345 IF PG_DEBUG in ('Y', 'C') THEN
346 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || 'expected error in Call_ATP_No_Commit');
347 END IF;
348 RAISE FND_API.G_EXC_ERROR ;
349 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
350 IF PG_DEBUG in ('Y', 'C') THEN
351 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || 'something wrong in Call_ATP_No_Commit');
352 END IF;
353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
354 END IF;
355
356 commit; -- AUTONOMOUS_TRANSACTION
357
358
359 IF PG_DEBUG in ('Y', 'C') THEN
360 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || 'Return Status : '||x_return_status);
361 END IF;
362
363 MSC_SATP_FUNC.get_dblink_profile(l_a2m_dblink,l_instance_id,l_return_status); --bug3049003 start
364 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
365 IF PG_DEBUG in ('Y', 'C') THEN
366 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || 'error in mrp_ap_apps_instances');
367 END IF;
368 END IF; --bug3049003 end
369 -- Set this to be checked later in exception while closing DB Link
370 -- Also, don't rollback in case this is set to -1 as this means ATP Transaction
371 -- is finished. This would prevent rolling back any changes in calling module.
372 i := -1;
373
374 -- Bug 1822005, modified Call_ATP to close DB Link after ATP Transaction is
375 -- finished by commit or rollback.
376 --l_db_profile := FND_PROFILE.value('MRP_ATP_DATABASE_LINK');
377 IF l_a2m_dblink IS NOT NULL THEN --bug3049003 changed from G_DB_PROFILE to l_a2m_dblink
378 IF PG_DEBUG in ('Y', 'C') THEN
379 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || 'after commit, before closing DB Link');
380 END IF;
381 cursor_name := dbms_sql.open_cursor;
382 DBMS_SQL.PARSE(cursor_name, 'alter session close database link ' ||l_a2m_dblink, --bug3049003 changed from G_DB_PROFILE to l_a2m_dblink
383 dbms_sql.native);
384
385 -- Added this block to handle the exception in case DB LInk wasn't open.
386 -- If not handled, this causes ORA-02081.
387 BEGIN
388 rows_processed := dbms_sql.execute(cursor_name);
389 EXCEPTION
390 WHEN DBLINK_NOT_OPEN THEN
391 IF PG_DEBUG in ('Y', 'C') THEN
392 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || 'inside DBLINK_NOT_OPEN');
393 END IF;
394 END;
395
396 DBMS_SQL.close_cursor(cursor_name);
397 IF PG_DEBUG in ('Y', 'C') THEN
398 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || 'after commit, after closing DB Link');
399 END IF;
400 END IF;
401
402 IF PG_DEBUG in ('Y', 'C') THEN
403 msc_sch_wb.atp_debug('End Call_ATP_Commit');
404 END IF;
405 EXCEPTION
406 WHEN others THEN
407 -- something wrong so we want to rollback;
408 IF PG_DEBUG in ('Y', 'C') THEN
409 msc_sch_wb.atp_debug('something wrong in Call_ATP_Commit : ' || sqlcode);
410 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || sqlerrm);
411 END IF;
412 x_return_status := NVL(x_return_status, FND_API.G_RET_STS_ERROR);
413
414 IF PG_DEBUG in ('Y', 'C') THEN
415 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || 'Return Status in excpetion : '||x_return_status);
416 END IF;
417
418 -- Error Handling Changes krajan
419 IF (x_atp_rec.inventory_item_id.COUNT = 0) THEN
420 x_atp_rec := p_atp_rec;
421 END IF;
422
423 -- Error Handling changes. Rollback for all cases.
424 IF NVL(i, -99) <> -1 THEN
425 -- This means the exception is raised within ATP Transaction.
426 ROLLBACK; --5195929 No need to have a save point as this is an Autonomous transaction
427 END IF;
428
429 IF PG_DEBUG in ('Y', 'C') THEN
430 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || 'after rollback, before closing DB Link');
431 END IF;
432 -- Close the DB Link in case it is open
433 -- Bug 1822005, modified Call_ATP to close DB Link after ATP Transaction is
434 -- finished by commit or rollback.
435 --l_db_profile := FND_PROFILE.value('MRP_ATP_DATABASE_LINK');
436 IF l_a2m_dblink IS NOT NULL THEN --bug3049003 changed from G_DB_PROFILE to l_a2m_dblink
437
438 cursor_name := dbms_sql.open_cursor;
439 DBMS_SQL.PARSE(cursor_name, 'alter session close database link ' ||l_a2m_dblink, --bug3049003 changed from G_DB_PROFILE to l_a2m_dblink
440 dbms_sql.native);
441
442 -- Added this block to handle the exception in case DB LInk wasn't open.
443 -- If not handled, this causes ORA-02081.
444 BEGIN
445 rows_processed := dbms_sql.execute(cursor_name);
446 EXCEPTION
447 WHEN DBLINK_NOT_OPEN THEN
448 IF PG_DEBUG in ('Y', 'C') THEN
449 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || 'inside DBLINK_NOT_OPEN exception');
450 END IF;
451 END;
452
453 DBMS_SQL.close_cursor(cursor_name);
454 IF PG_DEBUG in ('Y', 'C') THEN
455 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || 'after rollback, after closing DB Link');
456 END IF;
457 END IF;
458
459 END Call_ATP_Commit;
460
461
462 PROCEDURE Call_ATP (
463 p_session_id IN OUT NoCopy NUMBER,
464 p_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ,
465 x_atp_rec OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
466 x_atp_supply_demand OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
467 x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
468 x_atp_details OUT NOCOPY MRP_ATP_PUB.ATP_Details_Typ,
469 x_return_status OUT NoCopy VARCHAR2,
470 x_msg_data OUT NoCopy VARCHAR2,
471 x_msg_count OUT NoCopy NUMBER
472 ) IS
473
474 i PLS_INTEGER;
475 --l_db_profile VARCHAR2(128);
476 l_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
477
478 -- Bug 2387242 : krajan
479 -- Variables for SQL trace setup
480 C INTEGER;
481 STATEMENT VARCHAR2(255);
482 ROWS_PROCESSED INTEGER;
483 l_count number;
484 L_RETURN_NUM number;
485 cursor_name INTEGER; --5195929 for exception block
486 DBLINK_NOT_OPEN EXCEPTION; --5195929 for exception block
487 l_a2m_dblink VARCHAR2(80); --5195929 for db link
488 l_instance_id number; --5195929 for db link
489 l_return_status VARCHAR2(60); --5195929 for db link
490
491 BEGIN
492 --bug3609185 initialize API returm status to success
493 x_return_status := FND_API.G_RET_STS_SUCCESS;
494 G_CALL_ATP :=1; --4421391
495 L_RETURN_NUM := 1; --4421391
496 msc_sch_wb.set_session_id(p_session_id);
497 IF PG_DEBUG in ('Y', 'C') THEN
498 msc_sch_wb.atp_debug('Begin Call_ATP');
499 FOR i in 1..p_atp_rec.inventory_item_id.count LOOP
500 msc_sch_wb.atp_debug('Item # ' || i || ' := ' || p_atp_rec.inventory_item_id(i));
501 msc_sch_wb.atp_debug('LAD := ' || p_atp_rec.latest_acceptable_date(i));
502 msc_sch_wb.atp_debug('Ship date := ' || p_atp_rec.requested_ship_date(i));
503 msc_sch_wb.atp_debug('Qty := ' || p_atp_rec.quantity_ordered(i));
504 msc_sch_wb.atp_debug('Order number := ' || p_atp_rec.order_number(i));
505
506 END LOOP;
507
508
509 END IF;
510 SAVEPOINT start_of_call_atp; --5195929 added savepoint so that rollbak only
511 --undo the transactions till here
512 --bug3609185 removing second set_session_id as it is already done at line 916
513 --msc_sch_wb.set_session_id(p_session_id);
514 -- Bug 2387242 : krajan
515 -- Set Sql Trace.
516 IF order_sch_wb.mr_debug in ('T','C') THEN
517 enable_trace(L_RETURN_NUM); --4421391
518 IF L_RETURN_NUM = -1 then
519 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
520 END IF;
521 END IF;
522
523
524 i := p_atp_rec.Calling_Module.FIRST;
525 IF i IS NOT NULL THEN
526
527 -- Check if this is a demand modify from OM and profile option
528 -- is set to allow prescheduled order import without ATP, return ATP success.
529 IF NVL(p_atp_rec.Calling_Module(i), -99) = 660 AND
530 NVL(FND_PROFILE.value('MSC_OM_IMPORT_PRESCHEDULED'), 'N') = 'Y' THEN
531
532 IF PG_DEBUG in ('Y', 'C') THEN
533 msc_sch_wb.atp_debug('Call_ATP: ' || 'Inside Prescheduled order import support for OM');
534 END IF;
535
536 x_atp_rec := p_atp_rec;
537 FOR i in 1..p_atp_rec.Calling_Module.LAST LOOP
538 x_atp_rec.Available_quantity(i) := p_atp_rec.Quantity_Ordered(i);
539 x_atp_rec.Available_quantity(i) := p_atp_rec.Quantity_Ordered(i);
540 x_atp_rec.Error_Code(i) := 0;
541
542 -- ngoel 1/23/2001, need to set group dates and ship date
543 -- based on if there was a ship/ arrival set in the request.
544
545 --x_atp_rec.Ship_Date(i) := p_atp_rec.Requested_Ship_Date(i);
546 x_atp_rec.ship_date(i) := TRUNC(NVL(p_atp_rec.requested_ship_date(i),
547 p_atp_rec.requested_arrival_date(i) -
548 NVL(p_atp_rec.delivery_lead_time(i),0))
549 ) ;--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
550 --bug3609185 Setting the arrival date also.
551 x_atp_rec.arrival_date(i) := TRUNC(NVL(p_atp_rec.requested_arrival_date(i),
552 p_atp_rec.requested_ship_date(i) +
553 NVL(p_atp_rec.delivery_lead_time(i),0))
554 );--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
555
556 IF p_atp_rec.ship_set_name(i) IS NOT NULL THEN
557 -- ship set, set the group date
558 x_atp_rec.group_ship_date(i) := p_atp_rec.requested_ship_date(i);
559 ELSIF p_atp_rec.arrival_set_name(i) IS NOT NULL THEN
560 -- arrival set, set the group date
561 x_atp_rec.group_arrival_date(i) := p_atp_rec.requested_arrival_date(i);
562 END IF;
563
564 END LOOP;
565
566 IF PG_DEBUG in ('Y', 'C') THEN
567 msc_sch_wb.atp_debug('Call_ATP: ' || 'After Assigning values for x_atp_rec');
568 END IF;
569
570 ELSE
571
572
573 l_atp_rec := p_atp_rec;
574
575 -- Start 4279623
576 IF nvl(l_atp_rec.calling_module(1), -1) not in (724,-1) THEN
577 FORALL i in 1..l_atp_rec.action.COUNT
578 insert into msc_oe_data_temp
579 (
580 seq_id,
581 order_line_id,
582 oe_flag,
583 internal_org_id,
584 session_id
585 )
586 values
587 (
588 msc.msc_oe_data_temp_s.NEXTVAL,
589 l_atp_rec.identifier(i),
590 decode(l_atp_rec.oe_flag(i),'Y',(Select decode(MSC_ATP_PVT.G_INV_CTP, 5, l_atp_rec.OE_FLAG(i),
591 decode( prha.interface_source_code, 'MRP', 'Y', 'MSC', 'Y','CTO', 'Y', 'CTO-LOWER LEVEL', 'Y', 'N')) --4889943
592 from po_requisition_headers_all prha
593 where prha.requisition_header_id = l_atp_rec.attribute_01(i))), --5008194/FP 5054154
594
595 decode(l_atp_rec.oe_flag(i),'Y', (Select po.destination_organization_id
596 from po_requisition_lines_all po,
597 oe_order_lines_all oe
598 where oe.source_document_line_id = po.requisition_line_id
599 and oe.line_id = l_atp_rec.identifier(i)),NULL), --5008194/FP 5054154
600 p_session_id
601 );
602
603 IF PG_DEBUG in ('Y', 'C') THEN
604 msc_sch_wb.atp_debug('Call_ATP: Inserted data into msc_oe_data_temp');
605 msc_sch_wb.atp_debug('Records selected in mrp_oe_data_temp : ' || SQL%ROWCOUNT);
606 END IF;
607
608 select oe_flag,internal_org_id,seq_id
609 bulk collect into
610 l_atp_rec.oe_flag,
611 l_atp_rec.internal_org_id,
612 l_atp_rec.attribute_11
613 from msc_oe_data_temp
614 where session_id = p_session_id
615 order by seq_id;
616
617 IF PG_DEBUG in ('Y', 'C') THEN
618 msc_sch_wb.atp_debug('Call_ATP: selected data back into the l_atp_rec');
619 msc_sch_wb.atp_debug('Internal Org. Count :=' || l_atp_rec.internal_org_id.count);
620 msc_sch_wb.atp_debug('OE_FLAG count:= ' || l_atp_rec.oe_flag.count);
621 FOR l in 1..l_atp_rec.action.LAST LOOP
622 msc_sch_wb.atp_debug('call_atp: OE_FLAG : ' || l_atp_rec.oe_flag(l));
623 msc_sch_wb.atp_debug('call_atp: Internal Org.Id : ' || l_atp_rec.internal_org_id(l));
624 END LOOP;
625 END IF;
626 END IF;
627 -- End 4279623
628
629 /*
630 Bug: 5195929 logic used
631 if calling module not ( 724 or null) then
632 if Flag = False/Null
633 Call_atp_commit()
634 else
635 call_atp_no_commit()
636 */ --Incase calling module does not extends.
637 IF l_atp_rec.attribute_14.count = 0 THEN
638 l_atp_rec.attribute_14.extend;
639 END IF;
640 --5195929 Starts if it is OM scheduling and flag is set indicating donot commit.
641 IF nvl(p_atp_rec.Action(i),100) <> 100 and NVL(p_atp_rec.Calling_Module(i), -99) <> 724 and
642 nvl(l_atp_rec.attribute_14(1),2) = 1 THEN
643
644 MSC_SATP_FUNC.get_dblink_profile(l_a2m_dblink,l_instance_id,l_return_status);
645
646 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
647 IF PG_DEBUG in ('Y', 'C') THEN
648 msc_sch_wb.atp_debug('Call_ATP_Commit: ' || 'error in mrp_ap_apps_instances');
649 END IF;
650 END IF;
651
652 i := p_atp_rec.Calling_Module.FIRST;
653 IF i IS NOT NULL THEN
654 IF PG_DEBUG in ('Y', 'C') THEN
655 msc_sch_wb.atp_debug('ATP Running in non autonomous mode');
656 END IF;
657 Call_ATP_No_Commit( p_session_id,
658 p_atp_rec,
659 x_atp_rec,
660 x_atp_supply_demand,
661 x_atp_period,
662 x_atp_details,
663 x_return_status,
664 x_msg_data,
665 x_msg_count);
666 IF l_a2m_dblink IS NOT NULL THEN
667 IF PG_DEBUG in ('Y', 'C') THEN
668 msc_sch_wb.atp_debug(' before closing DB Link');
669 END IF;
670 cursor_name := dbms_sql.open_cursor;
671 DBMS_SQL.PARSE(cursor_name, 'alter session close database link ' ||l_a2m_dblink,
672 dbms_sql.native);
673 -- Added this block to handle the exception in case DB LInk wasn't open.
674 -- If not handled, this causes ORA-02081.
675 BEGIN
676 rows_processed := dbms_sql.execute(cursor_name);
677 EXCEPTION
678 WHEN DBLINK_NOT_OPEN THEN
679 IF PG_DEBUG in ('Y', 'C') THEN
680 msc_sch_wb.atp_debug('inside DBLINK_NOT_OPEN');
681 END IF;
682 END;
683 DBMS_SQL.close_cursor(cursor_name);
684 IF PG_DEBUG in ('Y', 'C') THEN
685 msc_sch_wb.atp_debug('after commit, after closing DB Link');
686 END IF;
687 END IF;
688 END IF;
689 ELSE
690 IF PG_DEBUG in ('Y', 'C') THEN
691 msc_sch_wb.atp_debug('ATP Running in autonomous mode'); --5195929 End
692 END IF;
693
694 Call_ATP_Commit( p_session_id,
695 l_atp_rec,
696 x_atp_rec,
697 x_atp_supply_demand,
698 x_atp_period,
699 x_atp_details,
700 x_return_status,
701 x_msg_data,
702 x_msg_count);
703 END IF; --5195929
704 END IF; --IF NVL(p_atp_rec.Calling_Module(i), -99) = 660 AND
705 END IF; --IF i IS NOT NULL THEN
706
707
708 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
709 -- something wrong so we want to rollback;
710 IF PG_DEBUG in ('Y', 'C') THEN
711 msc_sch_wb.atp_debug('expected error in Call_ATP_No_Commit');
712 END IF;
713 RAISE FND_API.G_EXC_ERROR ;
714 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
715 IF PG_DEBUG in ('Y', 'C') THEN
716 msc_sch_wb.atp_debug('something wrong in Call_ATP_No_Commit');
717 END IF;
718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
719 END IF;
720
721 IF PG_DEBUG in ('Y', 'C') THEN
722 msc_sch_wb.atp_debug('Call_ATP: ' || 'Return Status : '||x_return_status);
723 END IF;
724
725
726 -- Bug 2387242 : krajan
727 -- Set Sql Trace.
728 IF order_sch_wb.mr_debug in ('T','C') THEN
729 disable_trace(L_RETURN_NUM); --4421391
730 IF L_RETURN_NUM =-1 then
731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
732 END IF;
733 END IF;
734
735 IF PG_DEBUG in ('Y', 'C') THEN
736 msc_sch_wb.atp_debug('End Call_ATP');
737 END IF;
738 EXCEPTION
739
740 -- Error Handling fix : krajan
741 WHEN MSC_ATP_PUB.ATP_INVALID_OBJECTS_FOUND THEN
742 -- something wrong so we want to rollback;
743 IF PG_DEBUG in ('Y', 'C') THEN
744 msc_sch_wb.atp_debug('something wrong in Call_ATP : ' || sqlcode);
745 msc_sch_wb.atp_debug('Call_ATP: ' || sqlerrm);
746 msc_sch_wb.atp_debug('Call_ATP: Invalid Objects found.');
747 END IF;
748
749 -- Step1: Assign output record
750 IF (x_atp_rec.Inventory_item_id.COUNT = 0) THEN
751 IF (l_atp_rec.Inventory_item_id.COUNT = 0) THEN
752 x_atp_rec := p_atp_rec;
753 ELSE
754 x_atp_rec := l_atp_rec;
755 END IF;
756 END IF;
757 -- Step 2: Assign Error codes
758 FOR i IN 1..x_atp_rec.Calling_Module.LAST LOOP
759 IF ((NVL(x_atp_rec.Error_Code(i), -1)) in (-1,0,61,150)) THEN
760 x_atp_rec.Error_Code(i) := MSC_ATP_PVT.ATP_INVALID_OBJECTS;
761 END IF;
762 END LOOP;
763 -- Step 3 : Add error to UI display stack
764 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
765 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME , 'Call_ATP');
766 END IF;
767 x_return_status := NVL(x_return_status, FND_API.G_RET_STS_ERROR);
768 IF PG_DEBUG in ('Y', 'C') THEN
769 msc_sch_wb.atp_debug('Call_ATP: ' || 'Return Status in excpetion : '||x_return_status);
770 END IF;
771 -- End Error Handling Fix
772
773 -- Bug 2387242 : krajan
774 -- Set Sql Trace.
775 IF order_sch_wb.mr_debug in ('T','C') THEN
776 disable_trace(L_RETURN_NUM); --4421391
777 IF L_RETURN_NUM =-1 THEN
778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
779 END IF;
780 END IF;
781
782
783 WHEN others THEN
784 -- something wrong so we want to rollback;
785 IF PG_DEBUG in ('Y', 'C') THEN
786 msc_sch_wb.atp_debug('something wrong in Call_ATP : ' || sqlcode);
787 msc_sch_wb.atp_debug('Call_ATP: ' || sqlerrm);
788 END IF;
789
790 -- Error Handling fix : krajan
791 IF (x_atp_rec.Inventory_item_id.COUNT = 0) THEN
792 IF (l_atp_rec.Inventory_item_id.COUNT = 0) THEN
793 x_atp_rec := p_atp_rec;
794 ELSE
795 x_atp_rec := l_atp_rec;
796 END IF;
797 END IF;
798 FOR i IN 1..x_atp_rec.Calling_Module.LAST LOOP
799 IF ((NVL(x_atp_rec.Error_Code(i), -1)) in (-1,0,61,150)) THEN
800 x_atp_rec.Error_Code(i) := MSC_ATP_PVT.ATP_PROCESSING_ERROR;
801 END IF;
802 END LOOP;
803 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
804 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME , 'Call_ATP');
805 END IF;
806 -- End Error Handling Fix
807
808 -- Bug 2387242 : krajan
809 -- Set Sql Trace.
810 IF order_sch_wb.mr_debug in ('T','C') THEN
811 disable_trace(L_RETURN_NUM); --4421391
812 IF L_RETURN_NUM =-1 THEN
813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
814 END IF;
815 END IF;
816 --5195929 add to the exception block
817 IF nvl(p_atp_rec.Action(i),100) <> 100 and
818 NVL(p_atp_rec.Calling_Module(i), -99) <> 724 and
819 nvl(l_atp_rec.attribute_14(1),2) = 1 THEN
820 IF PG_DEBUG in ('Y', 'C') THEN
821 msc_sch_wb.atp_debug('before rollback, before closing DB Link');
822 END IF;
823 ROLLBACK TO SAVEPOINT start_of_call_atp;
824 IF PG_DEBUG in ('Y', 'C') THEN
825 msc_sch_wb.atp_debug('after rollback, before closing DB Link');
826 END IF;
827 IF l_a2m_dblink IS NOT NULL THEN
828 cursor_name := dbms_sql.open_cursor;
829 DBMS_SQL.PARSE(cursor_name, 'alter session close database link ' ||l_a2m_dblink,
830 dbms_sql.native);
831 BEGIN
832 rows_processed := dbms_sql.execute(cursor_name);
833 EXCEPTION
834 WHEN DBLINK_NOT_OPEN THEN
835 IF PG_DEBUG in ('Y', 'C') THEN
836 msc_sch_wb.atp_debug('inside DBLINK_NOT_OPEN exception');
837 END IF;
838 END;
839 DBMS_SQL.close_cursor(cursor_name);
840 IF PG_DEBUG in ('Y', 'C') THEN
841 msc_sch_wb.atp_debug('after rollback, after closing DB Link');
842 END IF;
843 END IF;
844 END IF;
845 --5195929 End
846 x_return_status := NVL(x_return_status, FND_API.G_RET_STS_ERROR);
847 IF PG_DEBUG in ('Y', 'C') THEN
848 msc_sch_wb.atp_debug('Call_ATP: ' || 'Return Status in excpetion : '||x_return_status);
849 END IF;
850
851 END Call_ATP;
852
853
854 PROCEDURE Call_ATP_No_Commit (
855 p_session_id IN OUT NoCopy NUMBER,
856 p_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ,
857 x_atp_rec OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
858 x_atp_supply_demand OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
859 x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
860 x_atp_details OUT NOCOPY MRP_ATP_PUB.ATP_Details_Typ,
861 x_return_status OUT NoCopy VARCHAR2,
862 x_msg_data OUT NoCopy VARCHAR2,
863 x_msg_count OUT NoCopy NUMBER
864 ) IS
865
866 l_instance_id NUMBER;
867 l_refresh_number NUMBER;
868 --l_db_profile VARCHAR2(128);
869
870 -- krajan : 2927155
871 l_end_refresh_number NUMBER;
872 -- savirine, Sep 6, 2001: declared l_dblink and l_return_status variables,
873 -- these variables will be used in the get_regions procedure call
874
875 l_dblink VARCHAR2(128);
876 l_return_status VARCHAR2(60);
877
878 l_assign_set_id NUMBER;
879 plsql_block VARCHAR2(10000);
880 i PLS_INTEGER;
881 l_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
882 l_atp_rec_temp MRP_ATP_PUB.ATP_Rec_Typ;
883 l_atp_bom_flag MRP_ATP_PUB.number_arr;
884 l_shipset_stat_rec MRP_ATP_PUB.shipset_status_rec_type;
885
886 l_source_organization_id NUMBER;
887 l_customer_id NUMBER;
888 l_to_organization_id NUMBER;
889 l_customer_site_id NUMBER;
890 l_from_location NUMBER;
891 l_to_location NUMBER;
892 l_ship_method VARCHAR2(30);
893 l_delivery_lead_time NUMBER;
894 l_requested_ship_date DATE;
895 l_a2m_dblink VARCHAR2(80);
896 -- Bugs 2020607, 2104018, 2031894, 1869748 New variables.
897 l_prev_work_ship_date DATE;
898 l_sysdate DATE;
899 l_past_due_ship_date NUMBER; --bug4291375
900 l_sysdate_orc_new DATE; --bug4291375
901 -- Bug 2368426
902 l_def_assign_set_id NUMBER DEFAULT NULL;
903 l_wf_profile varchar2(1);
904 -- Bug 2413888, 2281628
905 l_group_ship_date DATE;
906 l_group_arrival_date DATE;
907 l_start PLS_INTEGER;
908 l_end PLS_INTEGER;
909 -- Bug 2413888, 2281628
910
911 l_details_flag NUMBER := 2;
912
913 -- dsting setproc
914 l_line_status NUMBER;
915 l_set_status NUMBER;
916
917 -- rajjain 02/03/2003 Bug 2766713 Begin
918 l_set_fail_flag VARCHAR2(1) := 'N';
919 j PLS_INTEGER;
920 k PLS_INTEGER;
921 -- rajjain 02/03/2003 Bug 2766713 End
922
923 -- 2833417
924 l_ato_ship_date DATE;
925
926 -- ship_rec_cal
927 l_offsetted_date DATE;
928 l_sysdate_osc DATE;
929 l_sysdate_orc DATE; --bug3439591
930 l_trunc_sysdate DATE := TRUNC(sysdate); --bug3439591
931
932 -- For summary enhancement
933 l_summary_flag VARCHAR2(1);
934 l_enforce_model_lt VARCHAR2(1);
935
936 --bug3520746
937 l_node_id NUMBER;
938 l_rac_count NUMBER;
939
940 --bug3583705
941 l_encoded_text varchar2(4000);
942 l_msg_app varchar2(50);
943 l_msg_name varchar2(30);
944
945 --2814895
946 l_country varchar2(60);
947 l_state varchar2(150);
948 l_city varchar2(60);
949 l_postal_code varchar2(60);
950 l_party_site_id NUMBER;
951
952 l_session_loc_des VARCHAR2(100); --ATP Debug Workflow
953 l_spid_des NUMBER; --ATP Debug Workflow
954 l_trace_loc_des VARCHAR2(100); --ATP Debug Workflow
955 l_login_user VARCHAR2(255) := FND_GLOBAL.user_name ;
956
957 -- 4421391 Variables for SQL trace setup
958 C INTEGER;
959 STATEMENT VARCHAR2(255);
960 ROWS_PROCESSED INTEGER;
961 L_RETURN_NUM NUMBER;
962 L_MOVE_PAST_DUE_TO_SYSDATE varchar2(1); -- Bug 5584634/5618929
963
964 --custom_api
965 l_custom_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
966 l_modify_flag number;
967 l_custom_ret_sts varchar2(30);
968 BEGIN
969 --Started change for bug 4421391
970 L_RETURN_NUM := 1;
971 IF G_CALL_ATP = 2 THEN
972 msc_sch_wb.set_session_id(p_session_id);
973 IF order_sch_wb.mr_debug in ('T','C') THEN
974 enable_trace(L_RETURN_NUM);
975 IF L_RETURN_NUM =-1 THEN
976 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
977 END IF;
978 END IF;
979 END IF;
980
981 --4421391
982
983 IF PG_DEBUG in ('Y', 'C') THEN
984 msc_sch_wb.atp_debug('Begin Call_ATP_No_Commit');
985 END IF;
986
987 -- initialize API returm status to success
988 x_return_status := FND_API.G_RET_STS_SUCCESS;
989
990 -- created a savepoint
991 SAVEPOINT start_of_call_atp_no_commit;
992 IF PG_DEBUG in ('Y', 'C') THEN
993 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'oe_flag count : ' || p_atp_rec.oe_flag.count);
994 END IF;
995
996 l_atp_rec := p_atp_rec;
997
998 i := p_atp_rec.Calling_Module.FIRST;
999
1000 IF i IS NOT NULL THEN
1001
1002 IF PG_DEBUG in ('Y', 'C') THEN
1003 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'p_atp_rec.Calling_Module : '||p_atp_rec.Calling_Module(i));
1004 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'p_atp_rec.ship_set_name : '||p_atp_rec.ship_set_name(i));
1005 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'G_INV_CTP : '||G_INV_CTP);
1006 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'p_atp_rec.requested_ship_date : '
1007 ||p_atp_rec.requested_ship_date(i));
1008 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'p_atp_rec.requested_arrival_date : '
1009 ||p_atp_rec.requested_arrival_date(i));
1010 END IF;
1011
1012 -- Set l_cto_flag = 1 in case call is from OM or Configurator
1013 -- and a ship set is specified
1014 -- Change l_cto_flag to G_CTO_FLAG - ngoel 1/11/2001
1015
1016 -- Bug 2218892, moved this to Check_CTO as it is needed to be set while called from Call_ATP
1017 /*
1018 IF NVL(p_atp_rec.Calling_Module(i), -99) IN (-1, 660, 708) AND
1019 G_INV_CTP = 4 and p_atp_rec.ship_set_name(i) IS NOT NULL THEN
1020 G_CTO_FLAG := 1;
1021 END IF;
1022 IF PG_DEBUG in ('Y', 'C') THEN
1023 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'G_CTO_FLAG : '||G_CTO_FLAG);
1024 END IF;
1025 */
1026
1027 IF NVL(p_atp_rec.Calling_Module(i), -99) = 724 THEN
1028
1029 -- this is for planning server atp inquiry.
1030 -- get instance id from record of tables p_atp_rec.
1031
1032 l_instance_id := p_atp_rec.instance_id(i);
1033
1034 IF PG_DEBUG in ('Y', 'C') THEN
1035 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_instance_id = '||l_instance_id);
1036 END IF;
1037 -- get the assignment set from profile in msc. if it is null,
1038 -- the assignment get from msc_apps_instance.
1039 --diag_atp
1040 --read the assignment set from the attribute_03. If it passed
1041 -- then use it else use from the profile option
1042
1043 IF p_atp_rec.attribute_03.count >= 1 AND
1044 NVL(p_atp_rec.attribute_03(i), -1) <> -1
1045 THEN
1046 l_assign_set_id := p_atp_rec.attribute_03(i);
1047 ELSE
1048 l_assign_set_id := FND_PROFILE.value('MSC_ATP_ASSIGN_SET');
1049 END IF;
1050
1051 IF PG_DEBUG in ('Y', 'C') THEN
1052 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_assign_set_id = '||l_assign_set_id);
1053 END IF;
1054
1055 IF l_assign_set_id is NULL THEN
1056 SELECT assignment_set_id
1057 INTO l_assign_set_id
1058 FROM msc_apps_instances
1059 WHERE instance_id = l_instance_id;
1060 END IF;
1061
1062 ELSE
1063 -- G_DB_PROFILE := FND_PROFILE.value('MRP_ATP_DATABASE_LINK'); bug3049003 changed from G_DB_PROFILE to l_a2m_dblink
1064 --l_db_profile := FND_PROFILE.value('MRP_ATP_DATABASE_LINK');
1065 -- this request is from the sourcce instance
1066 -- get the assignment set from profile in mrp
1067
1068 --diag_atp
1069 --read the assignment set from the attribute_03. If it passed
1070 -- then use it else use from the profile option
1071 IF p_atp_rec.attribute_03.count >= 1 AND
1072 NVL(p_atp_rec.attribute_03(i), -1) <> -1
1073 THEN
1074 l_assign_set_id := p_atp_rec.attribute_03(i);
1075 ELSE
1076 l_assign_set_id := FND_PROFILE.value('MRP_ATP_ASSIGN_SET');
1077 END IF;
1078
1079 IF PG_DEBUG in ('Y', 'C') THEN
1080 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_assign_set_id = '||l_assign_set_id);
1081 END IF;
1082
1083 END IF;
1084
1085 /*
1086 ELSE
1087 -- this request is from the sourcce instance
1088 -- get the assignment set from profile in mrp
1089
1090 l_assign_set_id := FND_PROFILE.value('MRP_ATP_ASSIGN_SET');
1091
1092 -- get instance id from mrp_ap_apps_instances.
1093 BEGIN
1094 SELECT instance_id
1095 INTO l_instance_id
1096 FROM mrp_ap_apps_instances;
1097 EXCEPTION
1098 WHEN others THEN
1099 -- something wrong so we want to rollback
1100 IF PG_DEBUG in ('Y', 'C') THEN
1101 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'No instance Id record found in mrp_ap_apps_instances');
1102 END IF;
1103 x_atp_rec := l_atp_rec;
1104 RAISE FND_API.G_EXC_ERROR ;
1105 END;
1106 */
1107 END IF;
1108
1109 --s_cto_rearch
1110 g_atp_check := 'N';
1111
1112 IF PG_DEBUG in ('Y', 'C') THEN
1113 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Before Calling ATI for finding CTO-ATP Items');
1114 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_instance_id := ' || NVL(l_instance_id, -1));
1115 END IF;
1116
1117 IF l_instance_id IS NULL and NVL(p_atp_rec.Calling_Module(i), -99) <> 724 THEN
1118 IF PG_DEBUG in ('Y', 'C') THEN
1119 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Query from non-APS instance, find instance_id');
1120 END IF;
1121
1122 /* BEGIN
1123 SELECT instance_id, a2m_dblink
1124 INTO l_instance_id, l_a2m_dblink
1125 FROM mrp_ap_apps_instances;
1126
1127 EXCEPTION
1128 WHEN others THEN
1129 -- something wrong so we want to rollback;
1130 IF PG_DEBUG in ('Y', 'C') THEN
1131 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Error in mrp_ap_apps_instances : ' || sqlcode);
1132 END IF;
1133 x_atp_rec := l_atp_rec;
1134
1135 FOR i in 1..x_atp_rec.Calling_Module.LAST LOOP
1136 x_atp_rec.Error_Code(i) := NO_APS_INSTANCE;
1137 END LOOP;
1138
1139 RAISE FND_API.G_EXC_ERROR ;
1140 END;*/ --code commented for bug3049003
1141
1142 -- Uncommenting get_dblink_profile for bug 3632914
1143 MSC_SATP_FUNC.get_dblink_profile(l_a2m_dblink,l_instance_id,l_return_status); --bug3049003 start
1144
1145 IF PG_DEBUG in ('Y', 'C') THEN
1146 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_instance_id := ' || NVL(l_instance_id, -1));
1147 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_a2m_dblink := ' || l_a2m_dblink);
1148 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_return_status of := get_dblink_profile ' || l_return_status);
1149 END IF;
1150
1151 --bug3940999 dumping the profiles on source
1152 IF l_a2m_dblink is not NULL THEN
1153 MSC_SATP_FUNC.put_src_to_dstn_profiles(p_session_id,
1154 l_return_status);
1155 END IF;
1156 IF PG_DEBUG in ('Y', 'C') THEN
1157 msc_sch_wb.atp_debug('Call_ATP_No_Commit : ' || 'l_return_status of put_src_to_dstn_profiles:= ' || l_return_status);
1158 END IF;
1159
1160 /* bug 3623018: Do not raise this error here. Raise it only when some item is found to be atpbale
1161 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1162 x_atp_rec := l_atp_rec;
1163
1164 FOR i in 1..x_atp_rec.Calling_Module.LAST LOOP
1165 x_atp_rec.Error_Code(i) := NO_APS_INSTANCE;
1166 END LOOP;
1167
1168 RAISE FND_API.G_EXC_ERROR ;
1169
1170 END IF; ---bug3049003 end
1171 */
1172
1173 MSC_ATP_PVT.G_INSTANCE_ID := l_instance_id;
1174
1175 --- if MRP:ATP Database link profile option and mrp_ap_apps_instances table not in sync then
1176 --- raise an error
1177 /*IF (NVL(UPPER(l_a2m_dblink), -1) <> NVL(UPPER(G_DB_PROFILE), -1)) THEN
1178 IF PG_DEBUG in ('Y', 'C') THEN
1179 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'MRP:ATP DATABASE LINK profile option and MRP_AP_APPS_INSTANCES table
1180 not in SYNC');
1181 END IF;
1182 x_atp_rec := l_atp_rec;
1183
1184 FOR i in 1..x_atp_rec.Calling_Module.LAST LOOP
1185 x_atp_rec.Error_Code(i) := PROF_TBL_NOT_IN_SYNC;
1186 END LOOP;
1187
1188 RAISE FND_API.G_EXC_ERROR ;
1189 END IF;*/ --bug3049003 changed from G_DB_PROFILE to l_a2m_dblink
1190
1191 END IF;
1192
1193 MSC_ATP_PVT.G_CALLING_MODULE := NVL(l_atp_rec.Calling_Module(1), -99);
1194 SELECT mrp_ap_refresh_s.nextval
1195 INTO l_refresh_number
1196 FROM dual;
1197
1198 --s_cto_rearch: 24x7
1199 MSC_ATP_PVT.G_REFRESH_NUMBER := l_refresh_number;
1200 --e_cto_rearch: 24x7
1201
1202
1203 MSC_ATP_CTO.Check_Lines_For_CTO_ATP (l_atp_rec,
1204 p_session_id,
1205 l_a2m_dblink, --bug3049003 changed from G_DB_PROFILE to l_a2m_dblink
1206 --bug 3632914: If instance is not defined then
1207 --pass instance id as -1 so that error while inserting CTO
1208 -- sources doesn't occur as MSC_CTO_SOURCES has non-null instance_id column .
1209 nvl(l_instance_id, -1),
1210 l_return_status);
1211
1212 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1213 -- something wrong so we want to rollback;
1214 IF PG_DEBUG in ('Y', 'C') THEN
1215 msc_sch_wb.atp_debug('l_return_status := ' || l_return_status);
1216 msc_sch_wb.atp_debug('Call_ATP: ' || 'expected error in Call to Check_CTO');
1217 END IF;
1218 x_return_status := l_return_status;
1219 x_atp_rec := l_atp_rec;
1220 RAISE FND_API.G_EXC_ERROR;
1221 END IF;
1222
1223
1224 --l_customer_site_id := NULL;
1225 -- The above line is replaced by following code to support
1226 --- order import where request set contains requenst
1227 -- from many customers with different customer site
1228 -- Lets say we have customers in the order of C1, C2,C1
1229 -- If any info (ship method, lead time etc) is missing for
1230 -- request C1 then ATP will go in for first customer
1231 -- site and get its region info and will put into temp table.
1232 -- When C1 is processed again then code will go in only if
1233 --- ship method, lead time info is missing.
1234 --- If this info is missing then ATP will
1235 -- try to insert records into regions_temp tbale but will
1236 -- fail as info for C1 already exists. we trap this
1237 -- exception in Get_regions procedure in MSCSATPB.pls
1238
1239 IF l_atp_rec.inventory_item_id.count > 0 THEN
1240 l_customer_site_id := -1234;
1241 --2814895, added address parameters and party_site_id
1242 l_country := -1234;
1243 l_state := -1234;
1244 l_city := -1234;
1245 l_postal_code := -1234;
1246 l_party_site_id := -1234;
1247 ELSE
1248 l_customer_site_id := NULL;
1249 --2814895, added address parameters and party_site_id
1250 l_country := NULL;
1251 l_state := NULL;
1252 l_city := NULL;
1253 l_postal_code := NULL;
1254 l_party_site_id := NULL;
1255 END IF;
1256
1257 -- Bug 2413888, 2281628, 3000016
1258 -- Initialize group ship/arrival date variables
1259 l_start := l_atp_rec.ACTION.FIRST;
1260 MSC_ATP_PROC.Initialize_Set_Processing(l_atp_rec, l_start);
1261
1262 -- bug 2748730. Initialize the group_ship_date and group_arrival_date to the end of day
1263 l_group_ship_date := TRUNC(sysdate);--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
1264 l_group_arrival_date := TRUNC(sysdate);--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
1265 -- Bug 2413888, 2281628
1266
1267 j := l_atp_rec.inventory_item_id.FIRST;
1268
1269 WHILE j IS NOT NULL LOOP
1270 BEGIN --bug3583705
1271 -- savirine, Sep 05, 2001: call get_regions to get regions info to be used for ATP request.
1272
1273 IF PG_DEBUG in ('Y', 'C') THEN
1274 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'j : ' ||j);
1275 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'requested_ship_date : ' ||l_atp_rec.requested_ship_date(j));
1276 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'requested_arrival_date : ' ||l_atp_rec.requested_arrival_date(j));
1277 END IF;
1278 l_past_due_ship_date := 2; --bug4291375 resetting variable to no past due date
1279 -- 3000016
1280 IF G_ATP_CHECK = 'N' THEN
1281
1282 IF PG_DEBUG in ('Y', 'C') THEN
1283
1284 msc_sch_wb.atp_debug('Call_ATP_No_Commit: Item Is non atpable');
1285 END IF;
1286
1287 IF l_atp_rec.attribute_06(j) is null THEN
1288
1289 IF PG_DEBUG in ('Y', 'C') THEN
1290
1291 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || ' Invalid Item Org Combo');
1292 END IF;
1293
1294 l_atp_rec.error_code(j) := MSC_ATP_PVT.INVALID_ITEM_ORG_COMBINATION;
1295
1296 l_end := j;
1297 IF PG_DEBUG in ('Y', 'C') THEN
1298 msc_sch_wb.atp_debug('identifier := ' || l_atp_rec.identifier(l_end));
1299 msc_sch_wb.atp_debug('ato_model_line_id :== ' || l_atp_rec.ato_model_line_id(l_end));
1300 msc_sch_wb.atp_debug('bom_item_type := ' || l_atp_rec.bom_item_type(l_end));
1301 END IF;
1302 -- advance l_end to the end of the set
1303 while l_end < l_atp_rec.action.count() and
1304 (nvl(l_atp_rec.ship_set_name(l_end+1), -99) = nvl(l_atp_rec.ship_set_name(l_start), -99) and
1305 nvl(l_atp_rec.arrival_set_name(l_end+1), -99) = nvl(l_atp_rec.arrival_set_name(l_start), -99))
1306 loop
1307 mrp_atp_pvt.assign_atp_input_rec(l_atp_rec,l_end,x_atp_rec,x_return_status);
1308 l_end := l_atp_rec.inventory_item_id.next(l_end);
1309
1310 IF l_atp_rec.identifier(l_end) = l_atp_rec.ato_model_line_id(l_end) THEN
1311 MSC_ATP_CTO.G_MODEL_IS_PRESENT := 1;
1312 IF PG_DEBUG in ('Y', 'C') THEN
1313 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Model is present');
1314 END IF;
1315 END IF;
1316 end loop;
1317 mrp_atp_pvt.assign_atp_input_rec(l_atp_rec,l_end,x_atp_rec,x_return_status);
1318
1319 IF l_atp_rec.identifier(l_end) = l_atp_rec.ato_model_line_id(l_end) THEN
1320 MSC_ATP_CTO.G_MODEL_IS_PRESENT := 1;
1321 IF PG_DEBUG in ('Y', 'C') THEN
1322 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Model is present');
1323 END IF;
1324 END IF;
1325
1326
1327 MSC_ATP_PROC.Process_Set_Line(x_atp_rec, j, l_line_status);
1328 MSC_ATP_PVT.process_time_stamp_errors(x_atp_rec, j);--4460369
1329 IF (x_atp_rec.ship_set_name(l_start) is not null or
1330 x_atp_rec.arrival_set_name(l_start) is not null) and
1331 (l_end - l_start > 0)
1332 THEN
1333 MSC_ATP_PROC.Process_Set_Dates_Errors(x_atp_rec, 'S', l_set_status, l_start, l_end);
1334 END IF;
1335
1336 j := l_atp_rec.inventory_item_id.next(l_end);
1337 if j is null then
1338 exit;
1339 end if;
1340
1341 l_start := l_end + 1 ;
1342 l_end := NULL;
1343 IF l_start <= l_atp_rec.action.count THEN
1344 MSC_ATP_PROC.Initialize_Set_Processing(l_atp_rec, l_start);
1345 END IF;
1346 END IF;
1347 END IF; -- F G_ATP_CHECK := 'N' THEN
1348 IF PG_DEBUG in ('Y', 'C') THEN
1349 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || ' Before finding region based sourcing');
1350 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || l_atp_rec.customer_site_id(j));
1351 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || l_atp_rec.customer_country(j));
1352 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || l_atp_rec.customer_state(j));
1353 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || l_atp_rec.customer_city(j));
1354 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || l_atp_rec.customer_postal_code(j));
1355 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || l_atp_rec.party_site_id(j));
1356 END IF;
1357 IF (NVL(l_customer_site_id, -1) <> l_atp_rec.customer_site_id(j))
1358 --2814895, added checks for address parameters and party_site_id
1359 OR (NVL(l_party_site_id, -1) <> l_atp_rec.party_site_id(j))
1360 OR (NVL(l_country, -1) <> l_atp_rec.customer_country(j))
1361 OR (NVL(l_state, -1) <> l_atp_rec.customer_state(j))
1362 OR (NVL(l_city, -1) <> l_atp_rec.customer_city(j))
1363 OR (NVL(l_postal_code, -1) <> l_atp_rec.customer_postal_code(j))
1364 AND (NVL(l_atp_rec.calling_module(j), -99) <> 724) -- Bug 2085071 Fix
1365 AND (l_atp_rec.Source_Organization_id(j) IS NULL or l_atp_rec.ship_method(j) IS NULL or
1366 --- add condition for quantity_ordered so that get_regions is not called for unscheduling
1367 l_atp_rec.delivery_lead_time(j) IS NULL) and l_atp_rec.quantity_ordered(j) > 0 THEN
1368
1369 IF NVL(g_atp_check, '@') = 'N' THEN
1370 l_dblink := NULL;
1371 ELSE
1372 l_dblink := l_a2m_dblink; --bug3049003 changed from G_DB_PROFILE to l_a2m_dblink
1373 END IF;
1374
1375 l_customer_site_id := l_atp_rec.customer_site_id(j);
1376 --2814895, added for address parameters and party_site_id
1377 l_country := l_atp_rec.customer_country(j);
1378 l_state := l_atp_rec.customer_state(j);
1379 l_city := l_atp_rec.customer_city(j);
1380 l_postal_code := l_atp_rec.customer_postal_code(j);
1381 l_party_site_id := l_atp_rec.party_site_id(j);
1382
1383 MSC_SATP_FUNC.Get_Regions (
1384 p_customer_site_id => l_atp_rec.customer_site_id(j),
1385 p_calling_module => NVL(l_atp_rec.calling_module(j), -99), -- Bug 2085071 Fix
1386 -- i.e. Source (ERP) or Destination (724)
1387 p_instance_id => l_instance_id,
1388 p_session_id => p_session_id,
1389 p_dblink => l_dblink,
1390 x_return_status => l_return_status,
1391 p_location_id => NULL, --location_id
1392 p_location_source => NULL, --location_source
1393 p_supplier_site_id => NULL , --supplier_site_id
1394 -- 2814895, added address parameters, party_site_id and line_id
1395 -- as parameter to Get_Regions
1396 p_postal_code => l_atp_rec.customer_postal_code(j),
1397 p_city => l_atp_rec.customer_city(j),
1398 p_state => l_atp_rec.customer_state(j),
1399 p_country => l_atp_rec.customer_country(j),
1400 p_party_site_id => l_atp_rec.party_site_id(j),
1401 p_order_line_id => l_atp_rec.identifier(j) --2814895, added identifier for address parameters
1402 );
1403
1404 IF PG_DEBUG in ('Y', 'C') THEN
1405 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Get_Regions, return status : ' || l_return_status);
1406 END IF;
1407
1408 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1409 -- something wrong so we want to rollback;
1410 IF PG_DEBUG in ('Y', 'C') THEN
1411 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'expected error in Call to Get_Regions');
1412 END IF;
1413 x_atp_rec := l_atp_rec;
1414 --bug3583705 if this xcptn is raised then processing for all line will not stop
1415 -- as it will be handled in when others xcptn
1416 RAISE FND_API.G_EXC_ERROR ;
1417 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1418 IF PG_DEBUG in ('Y', 'C') THEN
1419 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'something wrong in Call to Get_Regions');
1420 END IF;
1421 x_atp_rec := l_atp_rec;
1422 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1423 END IF;
1424 END IF;
1425
1426 IF PG_DEBUG in ('Y', 'C') THEN
1427 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'g_atp_check := ' || g_atp_check);
1428 END IF;
1429
1430 IF NVL(g_atp_check, '@') = 'N' THEN
1431
1432 IF PG_DEBUG in ('Y', 'C') THEN
1433 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'No ATPable item or CTO model in the ATP request from source');
1434 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Set values for ship and group date and error code');
1435 END IF;
1436 -- we cannot assign this directly since the l_atp_rec may not be a
1437 -- complete record, that is, there are some elements that are not initialized before.
1438 -- x_atp_rec := l_atp_rec;
1439
1440 IF l_atp_rec.identifier(j) = l_atp_rec.ato_model_line_id(j) and l_atp_rec.bom_item_type(j) = 1 THEN
1441 MSC_ATP_CTO.G_MODEL_IS_PRESENT := 1;
1442 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Model is present');
1443 END IF;
1444 mrp_atp_pvt.assign_atp_input_rec(l_atp_rec,j,x_atp_rec,x_return_status);
1445
1446 if x_atp_rec.action(j) = 100 then
1447 x_atp_rec.error_code(j) := 61;
1448 else
1449 x_atp_rec.error_code(j) := 0;
1450 end if;
1451
1452 x_atp_rec.available_quantity(j) := x_atp_rec.quantity_ordered(j);
1453 x_atp_rec.requested_date_quantity(j) := x_atp_rec.quantity_ordered(j);
1454
1455 --Vivek - Calcuate Delievry Lead time
1456
1457 l_ship_method := x_atp_rec.ship_method(j);
1458 l_delivery_lead_time := x_atp_rec.delivery_lead_time(j);
1459
1460 IF PG_DEBUG in ('Y', 'C') THEN
1461 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_delivery_lead_time : ' || l_delivery_lead_time);
1462 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'quantity_ordered(' || j || ') : ' || x_atp_rec.quantity_ordered(j));
1463 END IF;
1464
1465 -- krajan : 2748041
1466 -- Defaulting the delivery lead time for ATO delete flag case
1467 -- and cancellation of non-atpable item case.
1468 -- By setting it to 0, we dont have to go through the whole delivery
1469 -- lead time calculation.
1470 if (x_atp_rec.quantity_ordered(j) = 0 ) then
1471 IF PG_DEBUG in ('Y', 'C') THEN
1472 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'G_CTO_FLAG is 2 for non_atpable item or');
1473 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'A non ATPable item is being cancelled');
1474 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Setting the delivery lead time to 0');
1475 END IF;
1476 l_delivery_lead_time := 0;
1477 --bug 3979115:
1478 x_atp_rec.receiving_cal_code(j) := MSC_CALENDAR.FOC;
1479 x_atp_rec.shipping_cal_code(j) := MSC_CALENDAR.FOC;
1480 x_atp_rec.intransit_cal_code(j) := MSC_CALENDAR.FOC;
1481 end if;
1482 -- end 2748041
1483
1484 /* Bug 3335268 - Moved the assignments out of the "IF" block as these variables
1485 are used after "END IF" as well.*/
1486 l_source_organization_id := x_atp_rec.source_organization_id(j);
1487 l_to_organization_id := x_atp_rec.organization_id(j);
1488 l_customer_id := x_atp_rec.customer_id(j);
1489 l_customer_site_id := x_atp_rec.customer_site_id(j);
1490
1491 IF (NVL(l_delivery_lead_time, -1) = -1) THEN
1492 -- Bug3593394 - Calculate DLT only if parameters have changed
1493 IF j=1
1494 --bug 3979115: If previous line is for unscheduling then recalculate again
1495 --as we do not calculate these values for line marked for unscheduling
1496 OR (x_atp_rec.quantity_ordered(j-1) = 0)
1497 OR x_atp_rec.source_organization_id(j) <> x_atp_rec.source_organization_id(j-1)
1498 OR NVL(x_atp_rec.internal_org_id(j),-1) <> NVL(x_atp_rec.internal_org_id(j-1),-1)
1499 OR NVL(x_atp_rec.organization_id(j),-1) <> NVL(x_atp_rec.organization_id(j-1),-1)
1500 OR NVL(x_atp_rec.customer_id(j),-1) <> NVL(x_atp_rec.customer_id(j-1),-1)
1501 OR NVL(x_atp_rec.customer_site_id(j),-1) <> NVL(x_atp_rec.customer_site_id(j-1),-1)
1502 --2814895, added for address parameters and party_site_id support
1503 OR NVL(x_atp_rec.party_site_id(j),-1) <> NVL(x_atp_rec.party_site_id(j-1),-1)
1504 OR ( NVL(x_atp_rec.customer_postal_code(j),-1) <> NVL(x_atp_rec.customer_postal_code(j-1),-1)
1505 OR NVL(x_atp_rec.customer_city(j),-1) <> NVL(x_atp_rec.customer_city(j-1),-1)
1506 OR NVL(x_atp_rec.customer_state(j),-1) <> NVL(x_atp_rec.customer_state(j-1),-1)
1507 OR NVL(x_atp_rec.customer_country(j),-1) <> NVL(x_atp_rec.customer_country(j-1),-1))
1508 OR NVL(x_atp_rec.ship_method(j),'@@@') <> NVL(x_atp_rec.ship_method(j-1),'@@@') THEN
1509 /* Bug 3335268 - Moved the assignments out of the "IF" block as these variables
1510 are used after "END IF" as well.
1511 l_source_organization_id := x_atp_rec.source_organization_id(j);
1512 l_to_organization_id := x_atp_rec.organization_id(j);
1513 l_customer_id := x_atp_rec.customer_id(j);
1514 l_customer_site_id := x_atp_rec.customer_site_id(j);
1515 */
1516
1517 IF PG_DEBUG in ('Y', 'C') THEN
1518 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_source_organization_id : ' || l_source_organization_id);
1519 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_to_organization_id : ' || l_to_organization_id);
1520 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_customer_id : ' || l_customer_id);
1521 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_customer_site_id : ' || l_customer_site_id);
1522 END IF;
1523
1524 l_from_location := MSC_SATP_FUNC.src_location_id(l_source_organization_id, null, null);
1525
1526 IF PG_DEBUG in ('Y', 'C') THEN
1527 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_from_location : ' || l_from_location);
1528 END IF;
1529
1530 -- Bug 3449812 - Base DLT on internal_org_id if available
1531 -- IF NVL(x_atp_rec.internal_org_id(j),l_to_organization_id) IS NOT NULL THEN
1532
1533 -- Bug 3515520, don't use org in case customer/site is populated
1534 IF (x_atp_rec.internal_org_id(j) IS NOT NULL) OR
1535 (l_to_organization_id IS NOT NULL AND (l_customer_id IS NULL AND l_customer_site_id IS NULL)) THEN
1536 l_to_location := MSC_SATP_FUNC.src_location_id(NVL(x_atp_rec.internal_org_id(j),l_to_organization_id), null, null);
1537 ELSIF (l_customer_id IS NOT NULL AND l_customer_site_id IS NOT NULL) THEN --2814895
1538 l_to_location := MSC_SATP_FUNC.src_location_id(null, l_customer_id, l_customer_site_id);
1539 END IF;
1540
1541 IF PG_DEBUG in ('Y', 'C') THEN
1542 msc_sch_wb.atp_debug('Call_ATP_NO_Commit: ' || 'l_to_location : ' || l_to_location);
1543 END IF;
1544
1545 -- dsting dlt
1546 -- IF x_atp_rec.internal_org_id(j) IS NOT NULL THEN
1547
1548 -- Bug 3515520, don't use org in case customer/site is populated
1549 IF (x_atp_rec.internal_org_id(j) IS NOT NULL) OR
1550 (l_to_organization_id IS NOT NULL
1551 AND ((l_customer_id IS NULL AND l_customer_site_id IS NULL)
1552 OR l_party_site_id IS NULL
1553 OR l_country IS NULL )) THEN --2814895, added conditions for address parameter and party_site_id
1554 IF PG_DEBUG in ('Y', 'C') THEN
1555 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Inside Org code');
1556 END IF;
1557 MSC_SATP_FUNC.get_src_transit_time(
1558 l_source_organization_id,
1559 l_from_location,
1560 NVL(x_atp_rec.internal_org_id(j), l_to_organization_id), -- Bug 3515520
1561 -- x_atp_rec.internal_org_id(j) -- Bug 3515520
1562 l_to_location,
1563 p_session_id,
1564 NULL,
1565 l_ship_method,
1566 l_delivery_lead_time);
1567 ELSIF ( l_customer_site_id is not NULL) THEN --2814895
1568 IF PG_DEBUG in ('Y', 'C') THEN
1569 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Inside customer code');
1570 END IF;
1571 MSC_SATP_FUNC.get_src_transit_time(
1572 l_source_organization_id,
1573 l_from_location,
1574 -- l_to_organization_id, -- Bug 3515520
1575 NULL, -- Bug 3515520
1576 l_to_location,
1577 p_session_id,
1578 l_customer_site_id,
1579 l_ship_method,
1580 l_delivery_lead_time,
1581 2 --2814895, partner_type for customer
1582 );
1583 ELSIF ( l_party_site_id is not NULL) THEN --2814895
1584 IF PG_DEBUG in ('Y', 'C') THEN
1585 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Inside party_site code');
1586 END IF;
1587 MSC_SATP_FUNC.get_src_transit_time(
1588 l_source_organization_id,
1589 l_from_location,
1590 NULL,
1591 l_to_location,
1592 p_session_id,
1593 l_party_site_id,
1594 l_ship_method,
1595 l_delivery_lead_time,
1596 4 --2814895, partner_type for party_site_id
1597 );
1598 ELSIF (l_country IS NOT NULL) THEN --2814895
1599 IF PG_DEBUG in ('Y', 'C') THEN
1600 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Inside address_parameter code');
1601 END IF;
1602 MSC_SATP_FUNC.get_src_transit_time(
1603 l_source_organization_id,
1604 l_from_location,
1605 NULL,
1606 l_to_location,
1607 p_session_id,
1608 x_atp_rec.identifier(j), --2814895, using identifier as partner_site_id for addres_parameters
1609 l_ship_method,
1610 l_delivery_lead_time,
1611 5 --2814895 , partner_type for address parametrs
1612 );
1613 END IF;
1614
1615 l_delivery_lead_time := CEIL(l_delivery_lead_time);
1616
1617 ELSE
1618
1619 -- Bug3593394 - Use from previous line
1620 IF PG_DEBUG in ('Y', 'C') THEN
1621 msc_sch_wb.atp_debug('Call_ATP_NO_Commit: ' || 'DLT parameters have not changed');
1622 END IF;
1623 l_delivery_lead_time := x_atp_rec.delivery_lead_time(j-1);
1624 l_ship_method := x_atp_rec.ship_method(j-1);
1625
1626 END IF;
1627
1628 END IF; -- IF (NVL(l_delivery_lead_time, -1) = -1) THEN
1629
1630 x_atp_rec.delivery_lead_time(j) := NVL(l_delivery_lead_time,0);
1631
1632 x_atp_rec.ship_method(j) := l_ship_method; /* Bug 2111591 */
1633
1634 /* ship_rec_cal changes begin
1635 Moved the code from here for ship_rec_cal inside else of override.
1636 x_atp_rec.ship_date(j) := TRUNC(NVL(x_atp_rec.requested_ship_date(j),
1637 (x_atp_rec.requested_arrival_date(j) -
1638 NVL(x_atp_rec.delivery_lead_time(j),0)))
1639 ) + MSC_ATP_PVT.G_END_OF_DAY;
1640
1641 -- Bugs 2020607, 2104018, 2031894, 1869748
1642 -- Begin Changes
1643
1644 l_sysdate := MSC_SATP_FUNC.src_next_work_day(
1645 x_atp_rec.source_organization_id(j),
1646 sysdate);
1647 IF l_sysdate IS NULL THEN
1648 IF PG_DEBUG in ('Y', 'C') THEN
1649 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Next date not found in Calendar');
1650 END IF;
1651 x_atp_rec.error_code(j) := MSC_ATP_PVT.NO_MATCHING_CAL_DATE;
1652 RAISE NO_DATA_FOUND;
1653 END IF;
1654
1655 IF x_atp_rec.ship_date(j) < l_sysdate THEN
1656 x_atp_rec.requested_date_quantity(j) := 0;
1657 ELSE
1658 x_atp_rec.requested_date_quantity(j) := x_atp_rec.quantity_ordered(j);
1659 END IF;
1660 l_prev_work_ship_date := MSC_SATP_FUNC.src_prev_work_day(
1661 x_atp_rec.source_organization_id(J),
1662 x_atp_rec.ship_date(j));
1663 IF l_prev_work_ship_date IS NULL THEN
1664 IF PG_DEBUG in ('Y', 'C') THEN
1665 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'prev date for ship date not found in Calendar');
1666 END IF;
1667 x_atp_rec.error_code(j) := MSC_ATP_PVT.NO_MATCHING_CAL_DATE;
1668 RAISE NO_DATA_FOUND;
1669 END IF;
1670
1671 -- Bug 2194850, time stamp not updating for schedule ship date for non-atpable items
1672 -- bug 1929645. In case we have the following case,
1673 -- today D0, requested_ship_date D10 5pm is a non-working day
1674 -- and l_atp_rec.ship_date is D9 (previous working day) 0:00.
1675 -- we want to return ship date as D9 0:00 instead of D10 5pm.
1676
1677 IF PG_DEBUG in ('Y', 'C') THEN
1678 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'requested_ship_date(j) : ' || to_char(x_atp_rec.requested_ship_date(j), 'dd/mm/yyyy hh:mi:ss'));
1679 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'ship_date(j) : ' || to_char(x_atp_rec.ship_date(j), 'dd/mm/yyyy hh:mi:ss'));
1680 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_prev_work_ship_date : ' || to_char(l_prev_work_ship_date, 'dd/mm/yyyy hh:mi:ss'));
1681 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'requested_arrival_date(j): ' || to_char(x_atp_rec.requested_arrival_date(j),
1682 'dd/mm/yyyy hh:mi:ss'));
1683 END IF;*/
1684
1685 /* Bug 3335268 - We dont require calendar/date calculation in unschedule cases */
1686 IF (x_atp_rec.quantity_ordered(j) = 0) THEN
1687
1688 IF (x_atp_rec.requested_ship_date(j) is not null) THEN
1689 x_atp_rec.ship_date(j) := TRUNC(x_atp_rec.requested_ship_date(j));--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
1690 x_atp_rec.arrival_date(j) := TRUNC(x_atp_rec.requested_ship_date(j));--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
1691 x_atp_rec.latest_acceptable_date(j) := GREATEST(NVL(x_atp_rec.latest_acceptable_date(j),
1692 x_atp_rec.requested_ship_date(j)),x_atp_rec.requested_ship_date(j)); --5224773
1693 ELSE
1694 x_atp_rec.ship_date(j) := TRUNC(x_atp_rec.requested_arrival_date(j));--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
1695 x_atp_rec.arrival_date(j) := TRUNC(x_atp_rec.requested_arrival_date(j));--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
1696 x_atp_rec.latest_acceptable_date(j) := GREATEST(NVL(x_atp_rec.latest_acceptable_date(j),
1697 x_atp_rec.requested_arrival_date(j)),x_atp_rec.requested_arrival_date(j)); --5224773
1698 END IF;
1699
1700 ELSE
1701 -- Bug 4000425 Checking for Null ship method.
1702 IF MSC_ATP_PVT.G_USE_SHIP_REC_CAL='Y' and x_atp_rec.ship_method(j) is not null THEN
1703 -- Bug3593394 - Calculate only if parameters have changed
1704 IF (j=1)
1705 --bug 3979115: If previous line is for unscheduling then recalculate again
1706 --as we do not calculate these values for line marked for unscheduling
1707 OR (x_atp_rec.quantity_ordered(j-1) = 0)
1708 OR (x_atp_rec.ship_method(j)<>x_atp_rec.ship_method(j-1)) THEN
1709 x_atp_rec.intransit_cal_code(j) := MSC_SATP_FUNC.src_get_calendar_code(null, null, null,
1710 x_atp_rec.ship_method(j), MSC_CALENDAR.VIC);
1711 ELSE
1712 x_atp_rec.intransit_cal_code(j) := x_atp_rec.intransit_cal_code(j-1);
1713 IF PG_DEBUG in ('Y', 'C') THEN
1714 msc_sch_wb.atp_debug('Call_ATP_NO_Commit: ' || 'Parameters have not changed for VIC');
1715 END IF;
1716 END IF;
1717 ELSE
1718 -- Bug 3647208
1719 x_atp_rec.intransit_cal_code(j) := MSC_CALENDAR.FOC;
1720 END IF;
1721
1722
1723 -- Bug 3449812 - base receiving calendar on internal_org_id if available
1724 --IF Nvl(x_atp_rec.internal_org_id(j),l_to_organization_id) IS NOT NULL THEN
1725
1726 -- Bug 3515520, don't use org in case customer/site is populated
1727 -- Bug3593394 - Calculate only if parameters have changed
1728 IF (j>1)
1729 --bug 3979115: If previous line is for unscheduling then recalculate again
1730 --as we do not calculate these values for line marked for unscheduling
1731 AND (x_atp_rec.quantity_ordered(j-1) > 0)
1732 AND NVL(x_atp_rec.internal_org_id(j),-1) = NVL(x_atp_rec.internal_org_id(j-1),-1)
1733 AND NVL(x_atp_rec.organization_id(j),-1) = NVL(x_atp_rec.organization_id(j-1),-1)
1734 AND NVL(x_atp_rec.customer_id(j),-1) = NVL(x_atp_rec.customer_id(j-1),-1)
1735 AND NVL(x_atp_rec.customer_site_id(j),-1) = NVL(x_atp_rec.customer_site_id(j-1),-1)
1736 AND NVL(x_atp_rec.ship_method(j),'@@@') = NVL(x_atp_rec.ship_method(j-1),'@@@') THEN
1737
1738 x_atp_rec.receiving_cal_code(j) := x_atp_rec.receiving_cal_code(j-1);
1739 IF PG_DEBUG in ('Y', 'C') THEN
1740 msc_sch_wb.atp_debug('Call_ATP_NO_Commit: ' || 'Parameters have not changed for ORC/CRC');
1741 END IF;
1742 ELSIF (x_atp_rec.internal_org_id(j) IS NOT NULL) OR
1743 (l_to_organization_id IS NOT NULL AND l_customer_id IS NULL AND l_customer_site_id IS NULL) THEN
1744 x_atp_rec.receiving_cal_code(j) := MSC_SATP_FUNC.src_get_calendar_code(null, null,
1745 Nvl(x_atp_rec.internal_org_id(j),l_to_organization_id),
1746 x_atp_rec.ship_method(j), MSC_CALENDAR.ORC);
1747 -- Bug 3449812 - handle case where both org and customer are null
1748 ELSIF l_customer_site_id IS NOT NULL AND MSC_ATP_PVT.G_USE_SHIP_REC_CAL='Y' THEN
1749 -- Bug 3647208 - Call CRC only if using ship/rec cal
1750 x_atp_rec.receiving_cal_code(j) := MSC_SATP_FUNC.src_get_calendar_code(l_customer_id, l_customer_site_id, null,
1751 x_atp_rec.ship_method(j), MSC_CALENDAR.CRC);
1752 ELSE
1753 x_atp_rec.receiving_cal_code(j) := MSC_CALENDAR.FOC;
1754 END IF;
1755
1756 -- Bug3593394 - Calculate only if parameters have changed
1757 -- Bug 4000425 Added NVL
1758 IF j=1
1759 --bug 3979115: If previous line is for unscheduling then recalculate again
1760 --as we do not calculate these values for line marked for unscheduling
1761 OR (x_atp_rec.quantity_ordered(j-1) = 0)
1762 OR NVL(x_atp_rec.ship_method(j),'@@@')<>NVL(x_atp_rec.ship_method(j-1),'@@@')
1763 OR x_atp_rec.source_organization_id(j) <> x_atp_rec.source_organization_id(j-1) THEN
1764 x_atp_rec.shipping_cal_code(j) := MSC_SATP_FUNC.src_get_calendar_code(null, null, l_source_organization_id,
1765 x_atp_rec.ship_method(j), MSC_CALENDAR.OSC);
1766 ELSE
1767 x_atp_rec.shipping_cal_code(j) := x_atp_rec.shipping_cal_code(j-1);
1768 IF PG_DEBUG in ('Y', 'C') THEN
1769 msc_sch_wb.atp_debug('Call_ATP_NO_Commit: ' || 'Parameters have not changed for OSC');
1770 END IF;
1771 END IF;
1772
1773 IF PG_DEBUG in ('Y', 'C') THEN
1774 msc_sch_wb.atp_debug('Call_ATP_NO_Commit: ' || 'receiving_cal_code : ' || x_atp_rec.receiving_cal_code(j));
1775 msc_sch_wb.atp_debug('Call_ATP_NO_Commit: ' || 'intransit_cal_code : ' || x_atp_rec.intransit_cal_code(j));
1776 msc_sch_wb.atp_debug('Call_ATP_NO_Commit: ' || 'shipping_cal_code : ' || x_atp_rec.shipping_cal_code(j));
1777 END IF;
1778 /* ship_rec_cal changes end */
1779
1780 -- Begin Bug 2232555, 2250456
1781
1782 -- bug 2649670 dsting. Handle override case for nonatpable items
1783 IF (NVL(x_atp_rec.override_flag(j), 'N') = 'Y') THEN
1784 msc_sch_wb.atp_debug('Call_ATP_No_Commit: Override case. Do not mess with the dates');
1785
1786 IF (x_atp_rec.requested_ship_date(j) is not null) THEN
1787 -- ship_rec_cal project changes. Honor atleast VIC and CRC to compute right arrival date
1788 -- x_atp_rec.arrival_date(j) := x_atp_rec.ship_date(j) + x_atp_rec.delivery_lead_time(j);
1789
1790 x_atp_rec.ship_date(j) := TRUNC(x_atp_rec.requested_ship_date(j));--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
1791
1792 x_atp_rec.arrival_date(j) := TRUNC(MSC_SATP_FUNC.SRC_THREE_STEP_CAL_OFFSET_DATE(
1793 x_atp_rec.ship_date(j), null, 0,
1794 x_atp_rec.intransit_cal_code(j), x_atp_rec.delivery_lead_time(j), 1,
1795 x_atp_rec.receiving_cal_code(j), 1
1796 ));--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
1797 ELSE
1798
1799 -- ship_rec_cal project changes. Honor atleast VIC and OSC to compute right ship date
1800 x_atp_rec.arrival_date(j) := TRUNC(x_atp_rec.requested_arrival_date(j));--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
1801
1802 x_atp_rec.ship_date(j) := TRUNC(MSC_SATP_FUNC.SRC_THREE_STEP_CAL_OFFSET_DATE(
1803 x_atp_rec.arrival_date(j), null, 0,
1804 x_atp_rec.intransit_cal_code(j), -1 * x_atp_rec.delivery_lead_time(j), -1,
1805 x_atp_rec.shipping_cal_code(j), -1
1806 ));--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
1807
1808 END IF;
1809 ELSE
1810 -- ship_rec_cal project changes begin
1811 -- Bug3593394 - Calculate only if parameters have changed
1812 IF j=1
1813 --bug 3979115: If previous line is for unscheduling then recalculate again
1814 --as we do not calculate these values for line marked for unscheduling
1815 OR (x_atp_rec.quantity_ordered(j-1) = 0)
1816 OR x_atp_rec.source_organization_id(j)<>x_atp_rec.source_organization_id(j-1) THEN
1817 l_sysdate := MSC_SATP_FUNC.src_next_work_day(
1818 x_atp_rec.source_organization_id(j),
1819 l_trunc_sysdate);
1820 END IF;
1821
1822 -- Bug3593394 - Calculate only if parameters have changed
1823 --bug 3687934: Calculate this date when l_sysdate_osc is null as well.
1824 --l_sysdate might be null for the second or later line if first line is overridden
1825 IF j=1
1826 --bug 3979115: If previous line is for unscheduling then recalculate again
1827 --as we do not calculate these values for line marked for unscheduling
1828 OR (x_atp_rec.quantity_ordered(j-1) = 0)
1829 OR x_atp_rec.shipping_cal_code(j)<>x_atp_rec.shipping_cal_code(j-1) or l_sysdate_osc is null THEN
1830 l_sysdate_osc := MSC_SATP_FUNC.src_next_work_day(
1831 x_atp_rec.shipping_cal_code(j),
1832 l_trunc_sysdate);
1833 END IF;
1834
1835 -- Bug3593394 - Calculate only if parameters have changed
1836 --bug 3687934: Calculate this date when l_sysdate_orc is null
1837 IF j=1
1838
1839 --bug 3979115: If previous line is for unscheduling then recalculate again
1840 --as we do not calculate these values for line marked for unscheduling
1841 OR (x_atp_rec.quantity_ordered(j-1) = 0)
1842 OR x_atp_rec.receiving_cal_code(j)<>x_atp_rec.receiving_cal_code(j-1) or l_sysdate_orc is null THEN
1843 l_sysdate_orc := MSC_SATP_FUNC.src_next_work_day(
1844 x_atp_rec.receiving_cal_code(j),
1845 l_trunc_sysdate); --bug3439591
1846 END IF;
1847
1848 --bug3583705 not required
1849 /*IF l_sysdate IS NULL OR l_sysdate_osc IS NULL or l_sysdate_orc IS NULL THEN
1850 IF PG_DEBUG in ('Y', 'C') THEN
1851 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Next date not found in Calendar');
1852 END IF;
1853 x_atp_rec.error_code(j) := MSC_ATP_PVT.NO_MATCHING_CAL_DATE;
1854 RAISE NO_DATA_FOUND;
1855 END IF;*/
1856
1857 -- First compute the ship date without adding end of day.
1858 IF x_atp_rec.requested_ship_date(j) IS NOT NULL THEN
1859 x_atp_rec.ship_date(j) := MSC_SATP_FUNC.SRC_PREV_WORK_DAY(
1860 x_atp_rec.shipping_cal_code(j),
1861 x_atp_rec.requested_ship_date(j));
1862 ELSE
1863 x_atp_rec.ship_date(j) := MSC_SATP_FUNC.SRC_THREE_STEP_CAL_OFFSET_DATE(
1864 x_atp_rec.requested_arrival_date(j), x_atp_rec.receiving_cal_code(j), -1,
1865 x_atp_rec.intransit_cal_code(j), -1 * x_atp_rec.delivery_lead_time(j), -1,
1866 x_atp_rec.shipping_cal_code(j), -1
1867 );
1868
1869 END IF;
1870 --bug3439591 start
1871 /* --commented as a part of 3439591
1872 -- Check if the ship date so computed is less than sysdate_osc.If yes we set quantity = 0.
1873 IF x_atp_rec.ship_date(j) < l_sysdate_osc THEN
1874 x_atp_rec.requested_date_quantity(j) := 0;
1875 ELSE
1876 x_atp_rec.requested_date_quantity(j) := x_atp_rec.quantity_ordered(j);
1877 END IF;
1878 */
1879 L_MOVE_PAST_DUE_TO_SYSDATE := NVL(FND_PROFILE.value('MSC_MOVE_PAST_DUE_TO_SYSDATE'), 'Y'); -- Bug 5584634/5618929
1880 IF PG_DEBUG in ('Y', 'C') THEN
1881 msc_sch_wb.atp_debug('Schedule: ' || 'MOVE_PAST_DUE_TO_SYS_DATE :'|| L_MOVE_PAST_DUE_TO_SYSDATE);
1882 END IF;
1883 if L_MOVE_PAST_DUE_TO_SYSDATE = 'Y' THEN -- Bug 5584634/5618929
1884 IF x_atp_rec.ship_date(j) < l_sysdate_osc THEN
1885
1886 x_atp_rec.ship_date(j) := l_sysdate_osc;
1887 l_past_due_ship_date := 1; --bug4291375 setting variable to point pass due request ship date.
1888 IF PG_DEBUG in ('Y', 'C') THEN
1889 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'New ship_date := ' || l_sysdate_osc);
1890 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Request date is less than sysdate');
1891 END IF;
1892 END IF;
1893
1894 IF x_atp_rec.requested_ship_date(j) IS NOT NULL THEN
1895 x_atp_rec.latest_acceptable_date(j) := GREATEST(NVL(x_atp_rec.latest_acceptable_date(j),
1896 x_atp_rec.requested_ship_date(j)),x_atp_rec.requested_ship_date(j),
1897 l_sysdate_osc);
1898 ELSE
1899 IF l_past_due_ship_date = 2 THEN
1900 x_atp_rec.latest_acceptable_date(j) := GREATEST(NVL(x_atp_rec.latest_acceptable_date(j),
1901 x_atp_rec.requested_arrival_date(j)),x_atp_rec.requested_arrival_date(j),
1902 l_sysdate_orc);
1903 ELSE --bug4291375 If requested ship date is past due date then LAD needs to be offseted by the lead time.
1904 --calculate date after offset lead time
1905 l_sysdate_orc_new := MSC_SATP_FUNC.SRC_THREE_STEP_CAL_OFFSET_DATE(
1906 l_trunc_sysdate, x_atp_rec.shipping_cal_code(j), 1,
1907 x_atp_rec.intransit_cal_code(j), x_atp_rec.delivery_lead_time(j), 1,
1908 x_atp_rec.receiving_cal_code(j), 1
1909 );
1910 -- Calculate LAD after lead time taken in consideration
1911 x_atp_rec.latest_acceptable_date(j) := GREATEST(NVL(x_atp_rec.latest_acceptable_date(j),
1912 x_atp_rec.requested_arrival_date(j)),x_atp_rec.requested_arrival_date(j),
1913 l_sysdate_orc_new);
1914 END IF;
1915 END IF;
1916 END IF;
1917 --bug3439591 end
1918
1919 -- Bug 2194850, time stamp not updating for schedule ship date for non-atpable items
1920 -- bug 1929645. In case we have the following case,
1921 -- today D0, requested_ship_date D10 5pm is a non-working day
1922 -- and l_atp_rec.ship_date is D9 (previous working day) 0:00.
1923 -- we want to return ship date as D9 0:00 instead of D10 5pm.
1924
1925 IF PG_DEBUG in ('Y', 'C') THEN
1926 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'requested_ship_date(j) : ' || to_char(x_atp_rec.requested_ship_date(j), 'dd/mm/yyyy hh:mi:ss'));
1927 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'ship_date(j) : ' || to_char(x_atp_rec.ship_date(j), 'dd/mm/yyyy hh:mi:ss'));
1928 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'requested_arrival_date(j): ' || to_char(x_atp_rec.requested_arrival_date(j),
1929 'dd/mm/yyyy hh:mi:ss'));
1930 msc_sch_wb.atp_debug('l_sysdate_orc_new := ' || l_sysdate_orc_new); --bug4291375
1931 msc_sch_wb.atp_debug('l_past_due_ship_date := ' || l_past_due_ship_date); --bug4291375
1932 END IF;
1933 -- ship_rec_cal project changes end
1934 --s_cto_rearch: bug 3169831 : Honor lead time only when profile option is turned on
1935 l_enforce_model_lt := NVL(FND_PROFILE.VALUE('MSC_ENFORCE_MODEL_LT'), 'Y');
1936
1937 IF PG_DEBUG in ('Y', 'C') THEN
1938 msc_sch_wb.atp_debug('l_enforce_model_lt := ' || l_enforce_model_lt);
1939 msc_sch_wb.atp_debug('atp lead time := ' || x_atp_rec.atp_lead_time(j));
1940 END IF;
1941
1942 -- 2833417 dsting add atp_lead_time if any
1943 if nvl(x_atp_rec.atp_lead_time(j), 0) > 0 and MSC_ATP_PVT.G_INV_CTP = 5
1944 and l_enforce_model_lt = 'Y' then
1945 -- we offset the lead time only in case of ODS ATP
1946 l_ato_ship_date := MSC_SATP_FUNC.src_date_offset(
1947 x_atp_rec.source_organization_id(j),
1948 l_sysdate,
1949 x_atp_rec.atp_lead_time(j));
1950 --bug3583705 not required.
1951 /*IF l_ato_ship_date IS NULL THEN
1952 msc_sch_wb.atp_debug('prev date for ship date not found in Calendar');
1953 x_atp_rec.error_code(j) := MSC_ATP_PVT.NO_MATCHING_CAL_DATE;
1954 RAISE NO_DATA_FOUND;
1955 END IF;*/
1956 else
1957 -- 2894867
1958 l_ato_ship_date := x_atp_rec.ship_date(j);
1959 end if;
1960 msc_sch_wb.atp_debug('l_ato_ship_date: ' || l_ato_ship_date);
1961
1962 -- dsting
1963 -- 2833417
1964 -- As part of ship_rec_cal remove the redundant if clause and compute the arrival date here.
1965 -- Recompute the ship date and subsequently arrive date.
1966
1967 x_atp_rec.ship_date(j) := TRUNC(GREATEST(x_atp_rec.ship_date(j), l_sysdate_osc, l_ato_ship_date));--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
1968
1969 x_atp_rec.arrival_date(j) := TRUNC(MSC_SATP_FUNC.SRC_THREE_STEP_CAL_OFFSET_DATE(
1970 x_atp_rec.ship_date(j), null, 0,
1971 x_atp_rec.intransit_cal_code(j), x_atp_rec.delivery_lead_time(j), 1,
1972 x_atp_rec.receiving_cal_code(j), 1
1973 ));--4460369 + MSC_ATP_PVT.G_END_OF_DAY;
1974 --bug3439591 start
1975 IF((x_atp_rec.requested_ship_date(j) IS NOT NULL) AND
1976 (trunc(x_atp_rec.ship_date(j))
1977 > trunc(x_atp_rec.latest_acceptable_date(j)))
1978 OR
1979 ((x_atp_rec.requested_arrival_date(j)IS NOT NULL) AND
1980 (trunc(x_atp_rec.arrival_date(j))
1981 > trunc(x_atp_rec.latest_acceptable_date(j))))) THEN
1982
1983 x_atp_rec.error_code(j) := MSC_ATP_PVT.ATP_ACCEPT_FAIL;
1984 x_atp_rec.requested_date_quantity(j) := 0;
1985 ELSE
1986 x_atp_rec.requested_date_quantity(j) := x_atp_rec.quantity_ordered(j);
1987 END IF;
1988
1989 IF PG_DEBUG in ('Y', 'C') THEN
1990 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_sysdate_osc : ' || l_sysdate_osc);
1991 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_sysdate_orc : ' || l_sysdate_orc);
1992 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'x_atp_rec.latest_acceptable_date(j) : ' || x_atp_rec.latest_acceptable_date(j));
1993 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'x_atp_rec.delivery_lead_time(j) : ' || x_atp_rec.delivery_lead_time(j));
1994 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'x_atp_rec.ship_date(j) : ' || x_atp_rec.ship_date(j));
1995 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'x_atp_rec.arrival_date(j) : ' || x_atp_rec.arrival_date(j));
1996 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'error_code(j) : ' || x_atp_rec.error_code(j));
1997 END IF;
1998 --bug3439591 end
1999 END IF; -- override bug 2649670
2000
2001 END IF; -- IF (x_atp_rec.quantity_ordered(j) = 0) THEN
2002 /* Bug 3335268 - changes end */
2003
2004
2005 -- End Changes Bug 2232555, 2250456
2006 -- Bug 2406242, 2463608
2007 /* Bug 3345563 - At this point arrival date has already been calculated.
2008 Done with Enforce Pur LT changes
2009 IF PG_DEBUG in ('Y', 'C') THEN
2010 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Arrival Date is always calculated ');
2011 END IF;
2012 IF x_atp_rec.requested_arrival_date(j) is not null THEN
2013 x_atp_rec.arrival_date(j) := GREATEST(x_atp_rec.arrival_date(j),
2014 x_atp_rec.requested_arrival_date(j));
2015 END IF;
2016 */
2017 -- End Bug 2406242, 2463608
2018
2019 IF (x_atp_rec.ship_set_name(j) IS NOT NULL OR
2020 x_atp_rec.arrival_set_name(j) IS NOT NULL) THEN
2021 IF PG_DEBUG in ('Y', 'C') THEN
2022 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Within Ship Set '||x_atp_rec.ship_set_name(j));
2023 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'OR Arrival Set '||x_atp_rec.arrival_set_name(j));
2024 END IF;
2025
2026 x_atp_rec.error_code(j) :=MSC_ATP_PVT.ATP_NOT_APPL;
2027 --bug 3365376: Pass earliest acceptable date as this date is used to calculate LAD for the line
2028 x_atp_rec.earliest_acceptable_date(j) := l_sysdate_osc; --bug3439591
2029 MSC_ATP_PROC.Process_Set_Line(x_atp_rec, j, l_line_status);
2030 MSC_ATP_PVT.process_time_stamp_errors(x_atp_rec, j);--4460369/4500382
2031 ELSE -- If in shipset or arrival set
2032 IF PG_DEBUG in ('Y', 'C') THEN
2033 msc_sch_wb.atp_debug('Call_ATP_No_Commit: Not a Set');
2034 msc_sch_wb.atp_debug('x_atp_rec.ship_date: ' || x_atp_rec.ship_date(j));
2035 msc_sch_wb.atp_debug('x_atp_rec.arrival_date: ' || x_atp_rec.arrival_date(j));
2036 END IF;
2037
2038 MSC_ATP_PVT.process_time_stamp_errors(x_atp_rec, j); --4967040:
2039 END IF;
2040
2041 -- Bug 2413888, 2281628
2042 -- 3000016 set l_end when we reach the end of a set, not the beginning of the next set
2043 IF j = l_atp_rec.action.count OR
2044 (NVL(l_atp_rec.Ship_Set_Name(l_start),-99) <>
2045 NVL(l_atp_rec.Ship_Set_Name(j+1),-100) AND
2046 NVL(l_atp_rec.Arrival_Set_Name(l_start),-99) <>
2047 NVL(l_atp_rec.Arrival_Set_Name(j+1),-100))
2048 THEN
2049 l_end := j;
2050 IF PG_DEBUG in ('Y', 'C') THEN
2051 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'setting l_end = '||l_end);
2052 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Ship_set_name '||x_atp_rec.ship_set_name(j));
2053 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Ship_set_name '||x_atp_rec.ship_set_name(l_start));
2054 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || l_atp_rec.action.count);
2055 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Arrival_set '||x_atp_rec.Arrival_set_name(j));
2056 END IF;
2057 END IF;
2058 -- Bug 2413888, 2281628
2059
2060 -- Bug 2413888, 2281628
2061 -- For ship_set or arrival_set cases the group date was not getting
2062 -- handled correctly before. Below is the fix.
2063
2064 -- 3000016
2065 IF l_end IS NOT NULL THEN
2066 IF (x_atp_rec.ship_set_name(j) is not null or
2067 x_atp_rec.arrival_set_name(j) is not null) and
2068 (l_end - l_start > 0)
2069 THEN
2070 MSC_ATP_PROC.Process_Set_Dates_Errors(x_atp_rec, 'S', l_set_status, l_start, l_end);
2071 END IF;
2072 l_start := l_end + 1 ;
2073 l_end := NULL;
2074 IF l_start <= l_atp_rec.action.count THEN
2075 MSC_ATP_PROC.Initialize_Set_Processing(l_atp_rec, l_start);
2076 END IF;
2077 IF PG_DEBUG in ('Y', 'C') THEN
2078 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_start: '||l_start);
2079 END IF;
2080 END IF;
2081 -- End of changes for Bug 2413888, 2281628
2082
2083 IF PG_DEBUG in ('Y', 'C') THEN
2084 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'ship_date(j) after : ' || to_char(x_atp_rec.ship_date(j), 'dd/mm/yyyy hh:mi:ss'));
2085 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'x_atp_rec.arrival_date(j) after : ' || to_char(x_atp_rec.arrival_date(j), 'dd/mm/yyyy hh:mi:ss'));
2086 END IF;
2087
2088 END IF; -- IF NVL(g_atp_check, '@') = 'N' THEN
2089 j := l_atp_rec.inventory_item_id.NEXT(j);
2090 EXCEPTION --bug3583705 start
2091 WHEN MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL THEN
2092 IF PG_DEBUG in ('Y', 'C') THEN
2093 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || ' NO_MATCHING_DATE_IN_CAL');
2094 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_start: '||l_start);
2095 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'j: '||j);
2096 END IF;
2097 l_end := l_start;
2098
2099 WHILE l_end is not null and
2100 (nvl(l_atp_rec.ship_set_name(l_end), -99) = nvl(l_atp_rec.ship_set_name(l_start), -99) and
2101 nvl(l_atp_rec.arrival_set_name(l_end), -99) = nvl(l_atp_rec.arrival_set_name(l_start), -99))
2102 LOOP
2103 IF l_end > j THEN
2104 mrp_atp_pvt.assign_atp_input_rec(l_atp_rec,l_end,x_atp_rec,x_return_status);
2105 END IF;
2106
2107 IF (x_atp_rec.error_code(l_end) IS NULL) or (x_atp_rec.error_code(l_end) IN (0,61,150)) THEN
2108 x_atp_rec.error_code(l_end) := MSC_ATP_PVT.NO_MATCHING_CAL_DATE;
2109 END IF;
2110
2111 l_end := l_atp_rec.inventory_item_id.next(l_end);
2112 IF PG_DEBUG in ('Y', 'C') THEN
2113 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_end: '||l_end);
2114 END IF;
2115 END LOOP;
2116
2117 j := l_end;
2118 l_start := l_end;
2119 l_end := NULL;
2120
2121 IF l_start <= l_atp_rec.action.count THEN
2122 MSC_ATP_PROC.Initialize_Set_Processing(l_atp_rec, l_start);
2123 END IF;
2124 IF PG_DEBUG in ('Y', 'C') THEN
2125 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_start: '||l_start);
2126 END IF;
2127
2128 WHEN OTHERS THEN
2129 IF PG_DEBUG in ('Y', 'C') THEN
2130 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || ' NO_MATCHING_DATE_IN_CAL');
2131 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_start: '||l_start);
2132 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'j: '||j);
2133 END IF;
2134 /* Check if this is actually coming from a calendar routine*/
2135 l_encoded_text := fnd_message.GET_ENCODED;
2136 IF l_encoded_text IS NULL THEN
2137 l_msg_app := NULL;
2138 l_msg_name := NULL;
2139 ELSE
2140 fnd_message.parse_encoded(l_encoded_text, l_msg_app, l_msg_name);
2141 END IF;
2142 l_end := l_start;
2143
2144 WHILE l_end is not null and
2145 (nvl(l_atp_rec.ship_set_name(l_end), -99) = nvl(l_atp_rec.ship_set_name(l_start), -99) and
2146 nvl(l_atp_rec.arrival_set_name(l_end), -99) = nvl(l_atp_rec.arrival_set_name(l_start), -99))
2147 LOOP
2148
2149 IF l_end > j THEN
2150 mrp_atp_pvt.assign_atp_input_rec(l_atp_rec,l_end,x_atp_rec,x_return_status);
2151 END IF;
2152
2153 IF (x_atp_rec.error_code(l_end) IS NULL) or (x_atp_rec.error_code(l_end) IN (0,61,150)) THEN
2154 IF l_msg_app='MRP' AND l_msg_name='GEN-DATE OUT OF BOUNDS' THEN
2155 x_atp_rec.error_code(l_end) := MSC_ATP_PVT.NO_MATCHING_CAL_DATE;
2156 IF PG_DEBUG in ('Y', 'C') THEN
2157 msc_sch_wb.atp_debug('Call_ATP_No_Commit: NO_MATCHING_CAL_DATE');
2158 END IF;
2159 ELSE
2160 x_atp_rec.error_code(l_end) := MSC_ATP_PVT.ATP_PROCESSING_ERROR; -- ATP Processing Error
2161 IF PG_DEBUG in ('Y', 'C') THEN
2162 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ATP_PROCESSING_ERROR');
2163 END IF;
2164 END IF;
2165 END IF;
2166 l_end := l_atp_rec.inventory_item_id.next(l_end);
2167 END LOOP;
2168
2169 j := l_end;
2170 l_start := l_end;
2171 l_end := NULL;
2172
2173 IF l_start <= l_atp_rec.action.count THEN
2174 MSC_ATP_PROC.Initialize_Set_Processing(l_atp_rec, l_start);
2175 END IF;
2176 IF PG_DEBUG in ('Y', 'C') THEN
2177 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_start: '||l_start);
2178 END IF;
2179
2180 END; --bug3583705 end
2181
2182 END LOOP; -- FOR j in 1..l_atp_rec.inventory_item_id.LAST LOOP
2183
2184 IF NVL(g_atp_check, '@') = 'N' THEN
2185
2186 -- Delete records from MSC_REGIONS_TEMP before returning back to calling
2187 -- application so as to clean up the table for another request within
2188 -- same session for Region Level Sourcing Support
2189
2190 DELETE msc_regions_temp
2191 WHERE session_id = p_session_id;
2192
2193 IF PG_DEBUG in ('Y', 'C') THEN
2194 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Rows deleted from msc_regions_temp : '||sql%rowcount);
2195 END IF;
2196
2197 --bug3940999 Delete temp records before exiting
2198 DELETE msc_atp_src_profile_temp
2199 WHERE session_id = p_session_id;
2200
2201 IF PG_DEBUG in ('Y', 'C') THEN
2202 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Rows deleted from msc_atp_src_profile_temp : '||sql%rowcount);
2203 END IF;
2204
2205 -- No ATP needed as No ATPable item or CTO model in the ATP request from source.
2206
2207 -- Bug 2280196 Base Bug 2262291 Added by : krajan
2208 IF PG_DEBUG in ('Y', 'C') THEN
2209 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Put into temp table for MSCBALWB');
2210 END IF;
2211 MSC_ATP_UTILS.put_into_temp_table(
2212 NULL,
2213 p_session_id,
2214 x_atp_rec,
2215 x_atp_supply_demand,
2216 x_atp_period,
2217 x_atp_details,
2218 MSC_ATP_UTILS.RESULTS_MODE,
2219 x_return_status,
2220 x_msg_data,
2221 x_msg_count);
2222 -- End of change - Bug 2280196
2223
2224 ---since we update instead of insert, we need to get the updated data as well.
2225 --but we do it only for PDS case where model is present in the request
2226 IF MSC_ATP_CTO.G_MODEL_IS_PRESENT = 1 THEN
2227 MSC_ATP_UTILS.Get_From_Temp_Table(
2228 null,
2229 p_session_id,
2230 x_atp_rec,
2231 x_atp_supply_demand,
2232 x_atp_period,
2233 x_atp_details,
2234 MSC_ATP_UTILS.RESULTS_MODE,
2235 x_return_status,
2236 x_msg_data,
2237 x_msg_count,
2238 l_details_flag);
2239
2240 END IF;
2241
2242 -- krajan : 2748041
2243 -- If it is an ATO delete case,then call the POST ATP CTO procedure
2244 if (NVL(G_CTO_FLAG, -1) = 2 ) then
2245 IF PG_DEBUG in ('Y', 'C') THEN
2246 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'ATO Delete Flag Case - setting G_CTO_FLAG to 3');
2247 END IF;
2248 G_CTO_FLAG := 3;
2249 end if;
2250
2251 --4421391
2252 IF G_CALL_ATP = 2 THEN
2253 -- Set Sql Trace.
2254 IF order_sch_wb.mr_debug in ('T','C') THEN
2255 disable_trace(L_RETURN_NUM);
2256 IF L_RETURN_NUM =-1 THEN
2257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2258 END IF;
2259 END IF;
2260 END IF;
2261
2262 RETURN;
2263 END IF;
2264
2265 /* SELECT mrp_ap_refresh_s.nextval
2266 INTO l_refresh_number
2267 FROM dual;
2268
2269 --s_cto_rearch: 24x7
2270 MSC_ATP_PVT.G_REFRESH_NUMBER := l_refresh_number;
2271 --e_cto_rearch: 24x7
2272 */
2273
2274 /* bug 3623018: Check if instance id is found or not. If not then raise an error */
2275 IF l_instance_id IS NULL and NVL(p_atp_rec.Calling_Module(i), -99) <> 724 THEN
2276 x_atp_rec := l_atp_rec;
2277 FOR i in 1..x_atp_rec.Calling_Module.LAST LOOP
2278 x_atp_rec.Error_Code(i) := NO_APS_INSTANCE;
2279 END LOOP;
2280
2281 RAISE FND_API.G_EXC_ERROR ;
2282 END IF;
2283
2284 IF l_a2m_dblink IS NULL THEN --bug3049003 changed from G_DB_PROFILE to l_a2m_dblink
2285
2286
2287
2288 -- ngoel 10/15/2001, modified to call MSC_NATP_PVT.Call_Schedule_New instead of
2289 -- MSC_ATP_PVT.Call_Schedule as part of changes to split ATP source and destination patches.
2290
2291 MSC_NATP_PVT.Call_Schedule_New(
2292 p_session_id,
2293 l_atp_rec,
2294 l_instance_id,
2295 l_assign_set_id,
2296 l_refresh_number,
2297 --x_atp_rec,
2298 l_atp_rec_temp,
2299 x_return_status,
2300 x_msg_data,
2301 x_msg_count,
2302 x_atp_supply_demand,
2303 x_atp_period,
2304 x_atp_details);
2305 ---custom_api chnages: Call customer API for calculating arrival datea
2306 IF PG_DEBUG in ('Y', 'C') THEN
2307 msc_sch_wb.atp_debug('Before calling Custom_Post_ATP_API');
2308 END IF;
2309 MSC_ATP_CUSTOM.Custom_Post_ATP_API(l_atp_rec_temp,
2310 l_custom_atp_rec,
2311 l_modify_flag,
2312 l_custom_ret_sts);
2313 IF PG_DEBUG in ('Y', 'C') THEN
2314 msc_sch_wb.atp_debug('After calling Custom_Post_ATP_API');
2315 msc_sch_wb.atp_debug('l_custom_ret_sts := ' || l_custom_ret_sts);
2316 END IF;
2317
2318 IF l_modify_flag = 1 and l_custom_atp_rec.inventory_item_id.count > 0
2319 and l_custom_ret_sts = FND_API.G_RET_STS_SUCCESS THEN
2320
2321 msc_sch_wb.atp_debug('Assign Custom rec to ATP output rec');
2322 l_atp_rec_temp := l_custom_atp_rec;
2323
2324 END IF;
2325
2326 MSC_ATP_UTILS.put_into_temp_table(
2327 l_a2m_dblink, --bug3049003 changed from G_DB_PROFILE to l_a2m_dblink
2328 --l_db_profile,
2329 p_session_id,
2330 --x_atp_rec,
2331 l_atp_rec_temp,
2332 x_atp_supply_demand,
2333 x_atp_period,
2334 x_atp_details,
2335 MSC_ATP_UTILS.RESULTS_MODE,
2336 x_return_status,
2337 x_msg_data,
2338 x_msg_count);
2339
2340 ---since we update instead of insert, we need to get the updated data as well.
2341 --but we do it only for PDS case where model is present in the request
2342 IF MSC_ATP_CTO.G_MODEL_IS_PRESENT = 1 THEN
2343 MSC_ATP_UTILS.Get_From_Temp_Table(
2344 null,
2345 p_session_id,
2346 --x_atp_rec,
2347 l_atp_rec_temp,
2348 x_atp_supply_demand,
2349 x_atp_period,
2350 x_atp_details,
2351 MSC_ATP_UTILS.RESULTS_MODE,
2352 x_return_status,
2353 x_msg_data,
2354 x_msg_count,
2355 l_details_flag);
2356
2357 END IF;
2358
2359 IF p_atp_rec.attribute_04.count > 0 AND
2360 NVL(p_atp_rec.attribute_04(1),0) = 1
2361 THEN
2362 MSC_ATP_UTILS.Retrieve_Period_And_SD_Data(p_session_id,
2363 x_atp_period,
2364 x_atp_supply_demand);
2365 END IF;
2366
2367 ELSE
2368 /* s_cto_rearch- ATP is called from the source.
2369 MSC_ATP_UTILS.put_into_temp_table(
2370 NULL,
2371 p_session_id,
2372 l_atp_rec,
2373 x_atp_supply_demand,
2374 x_atp_period,
2375 x_atp_details,
2376 MSC_ATP_UTILS.REQUEST_MODE,
2377 x_return_status,
2378 x_msg_data,
2379 x_msg_count);
2380
2381 IF PG_DEBUG in ('Y', 'C') THEN
2382 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'instance_id = '||l_instance_id);
2383 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'before remote procedure call');
2384 END IF;
2385 e_cto_reach */
2386
2387 --bug3520746
2388 BEGIN
2389 SELECT count(*)
2390 into l_rac_count
2391 from gv$instance;
2392 IF l_rac_count > 1 then
2393 l_node_id := userenv('INSTANCE');
2394 IF PG_DEBUG in ('Y', 'C') THEN
2395 msc_sch_wb.atp_debug('RAC Instance id is:' || l_node_id);
2396 END IF;
2397 ELSE
2398 l_node_id := null;
2399 END IF;
2400 EXCEPTION
2401 WHEN OTHERS THEN
2402 l_node_id := null;
2403 END;
2404 IF PG_DEBUG in ('Y', 'C') THEN
2405 msc_sch_wb.atp_debug('node id is:' || l_node_id);
2406 END IF;
2407
2408 --bug3520746 pass the node to the remote call in case of RAC
2409 /* added inv_ctp, default assignment set in procedure call for bug 2368426 starts */
2410 --bug3049003 changed from G_DB_PROFILE to l_a2m_dblink
2411 --Bug3593394 - Pass ship rec profile from source
2412 --bug3940999 removed profile parameters which are passed thru table
2413 plsql_block := 'BEGIN MSC_ATP_PVT.CALL_SCHEDULE_REMOTE'
2414 ||'@'||l_a2m_dblink||'(
2415 :session_id,
2416 :instance_id,
2417 :assign_set_id,
2418 :refresh_number,
2419 :def_assign_set_id,
2420 :atp_debug_flag,
2421 :session_loc_des,
2422 :spid_des,
2423 :trace_loc_des,
2424 :node_id
2425 ); END;';
2426
2427 IF PG_DEBUG in ('Y', 'C') THEN
2428 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'plsql_block' || plsql_block);
2429 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'p_session_id ' || p_session_id);
2430 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_instance_id ' || l_instance_id);
2431 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_assign_set_id ' || l_assign_set_id);
2432 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_refresh_number ' || l_refresh_number);
2433 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_def_assign_set_id ' || l_def_assign_set_id);
2434 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'MSC_ATP_DEBUG ' || FND_PROFILE.value('MSC_ATP_DEBUG'));
2435 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'l_node_id ' || l_node_id);
2436 END IF;
2437
2438 EXECUTE IMMEDIATE plsql_block USING
2439 p_session_id,
2440 l_instance_id,
2441 l_assign_set_id,
2442 l_refresh_number,
2443 l_def_assign_set_id,
2444 FND_PROFILE.value('MSC_ATP_DEBUG'),
2445 OUT l_session_loc_des, --ATP Debug Workflow --added OUT, 4727103
2446 OUT l_spid_des, --ATP Debug Workflow
2447 OUT l_trace_loc_des, --ATP Debug Workflow
2448 l_node_id; --Bug3593394
2449 --bug3520746 End changes.
2450 /* added inv_ctp, default assignment set in procedure call for bug 2368426 ends */
2451
2452 IF p_atp_rec.attribute_04.count > 0 AND
2453 NVL(p_atp_rec.attribute_04(1),0) = 1
2454 THEN
2455 l_details_flag := 1;
2456 ELSE
2457 l_details_flag := 2;
2458 END IF;
2459
2460 MSC_ATP_UTILS.Get_From_Temp_Table(
2461 null,
2462 p_session_id,
2463 --x_atp_rec,
2464 l_atp_rec_temp,
2465 x_atp_supply_demand,
2466 x_atp_period,
2467 x_atp_details,
2468 MSC_ATP_UTILS.RESULTS_MODE,
2469 x_return_status,
2470 x_msg_data,
2471 x_msg_count,
2472 l_details_flag);
2473
2474 ---custom_api changes: Call customer API for calculating arrival datea
2475 IF PG_DEBUG in ('Y', 'C') THEN
2476 msc_sch_wb.atp_debug('Before calling Custom_Post_ATP_API');
2477 END IF;
2478 MSC_ATP_CUSTOM.Custom_Post_ATP_API(l_atp_rec_temp,
2479 l_custom_atp_rec,
2480 l_modify_flag,
2481 l_custom_ret_sts);
2482
2483 IF PG_DEBUG in ('Y', 'C') THEN
2484 msc_sch_wb.atp_debug('after calling Custom_Post_ATP_API');
2485 msc_sch_wb.atp_debug('l_custom_ret_sts := ' || l_custom_ret_sts);
2486 msc_sch_wb.atp_debug('l_modify_flag := ' || l_modify_flag);
2487 END IF;
2488
2489 IF l_modify_flag = 1 and l_custom_atp_rec.inventory_item_id.count > 0
2490 and l_custom_ret_sts = FND_API.G_RET_STS_SUCCESS THEN
2491
2492 l_atp_rec_temp := l_custom_atp_rec;
2493 ---update information in mrp_atp_schedule_temp
2494 MSC_ATP_PUB.Update_Custom_Information(l_atp_rec_temp,
2495 p_session_id);
2496 END IF;
2497
2498 -- end custom_api changes
2499
2500 -- Debug message added for bug 2368426
2501 IF l_atp_rec_temp.error_code(1) = 230 THEN
2502 IF PG_DEBUG in ('Y', 'C') THEN
2503 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'INV_CTP profile not in sync with destination');
2504 END IF;
2505 END IF;
2506
2507 END IF;
2508
2509 x_atp_rec := l_atp_rec_temp;
2510
2511 -- 2688113 : krajan : Copy ato_delete_flag
2512 IF PG_DEBUG in ('Y', 'C') THEN
2513 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Extending ATO_Delete Flag');
2514 END IF;
2515 x_atp_rec.ato_delete_flag := p_atp_rec.ato_delete_flag;
2516
2517 IF G_INV_CTP = 4 THEN
2518 --call substitution workflow
2519 IF x_atp_rec.action(1) <> 100 and x_atp_rec.calling_module(i) = 660 THEN
2520 l_wf_profile := NVL(fnd_profile.value('MSC_ALLOCATED_ATP_WORKFLOW'), 'N');
2521 IF PG_DEBUG in ('Y', 'C') THEN
2522 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'WF Profile:' || l_wf_profile);
2523 END IF;
2524 IF l_wf_profile = 'Y' THEN
2525 Subst_workflow(x_atp_rec);
2526 END IF;
2527 END IF;
2528 END IF;
2529
2530 IF PG_DEBUG in ('Y', 'C') THEN
2531 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'x_return_status : '||x_return_status);
2532 END IF;
2533
2534 -- Added by ngoel 10/18/2000. This procedure is called if there
2535 -- was a CTO model included in the request and either BOM was supplied
2536 -- by CTO previously or this is a rescheduling request. This procedure will
2537 -- store the demands for CTO model and its components and reconstruct
2538 -- the reduced ship set into original ship set. This procedure
2539 -- is needed to support multi-level, multi-org CTO models from
2540 -- OM and Configurator.
2541
2542 i := x_atp_rec.Action.FIRST;
2543 IF PG_DEBUG in ('Y', 'C') THEN
2544 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'before x_atp_rec.Action: '||x_atp_rec.Action(i));
2545 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'before l_atp_rec_temp.Error_Code: '||i||':'||
2546 l_atp_rec_temp.Error_Code(i));
2547 END IF;
2548
2549
2550 -- krajan : 2927155
2551 -- For summary enhancement - refresh number bumping up begins
2552 --Collection Enhancement changes start
2553 IF ((G_INV_CTP = 4) AND ( NVL (p_atp_rec.action(p_atp_rec.calling_module.FIRST), -99) in (110,120) ))THEN
2554 -- need to do this only for PDS cases
2555 IF PG_DEBUG in ('Y', 'C') THEN
2556 msc_sch_wb.atp_debug('Call_ATP_No_Commit: --Now processing refresh numbers--');
2557 msc_sch_wb.atp_debug('Call_ATP_No_Commit: First check if this is required because of summary');
2558 END IF;
2559 -- check if summary is enabled
2560 -- Bug 3762695: Use source side profile value for 'MSC: Enable ATP Summary mode'
2561 l_summary_flag := 'N';
2562 IF NVL( FND_PROFILE.VALUE('MSC_ENABLE_ATP_SUMMARY'), 'N') = 'Y' THEN
2563 BEGIN
2564 select summary_flag
2565 into l_summary_flag
2566 from mrp_atp_details_temp
2567 where session_id = p_session_id
2568 and summary_flag = 'Y'
2569 and record_type = 3 --record_type check added for performance
2570 and rownum = 1;
2571 EXCEPTION
2572 When Others then
2573 l_summary_flag := 'N';
2574 IF PG_DEBUG in ('Y', 'C') THEN
2575 msc_sch_wb.atp_debug('Inside exception');
2576 END IF;
2577 END;
2578 END IF;
2579
2580
2581 SELECT DECODE( NVL(l_summary_flag, 'N'), 'Y', mrp_ap_refresh_s.nextval, (NVL(lrn, -1) +1))
2582 INTO l_end_refresh_number
2583 FROM mrp_ap_apps_instances;
2584
2585 IF PG_DEBUG in ('Y', 'C') THEN
2586 msc_sch_wb.atp_debug('l_end_refresh_number ' || l_end_refresh_number);
2587 msc_sch_wb.atp_debug('l_summary_flag ' || l_summary_flag );
2588 END IF;
2589
2590 IF (l_a2m_dblink IS NOT NULL) THEN
2591 plsql_block := 'BEGIN MSC_ATP_PUB.UPDATE_TABLES'
2592 ||'@'||l_a2m_dblink||'(
2593 :p_summary_flag,
2594 :p_end_refresh_number,
2595 :p_refresh_number,
2596 :p_session_id);
2597 END;';
2598 IF PG_DEBUG in ('Y', 'C') THEN
2599 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'plsql_block ' || plsql_block);
2600 END IF;
2601 EXECUTE IMMEDIATE plsql_block USING
2602 l_summary_flag,
2603 l_end_refresh_number,
2604 l_refresh_number,
2605 p_session_id;
2606 ELSE
2607 UPDATE_TABLES(l_summary_flag,l_end_refresh_number,l_refresh_number,p_session_id);
2608
2609 END IF;
2610 END IF;
2611
2612 --ATP Debug Workflow, atp debug changes.
2613
2614 IF PG_DEBUG in ('Y', 'C') THEN
2615 MSC_WF_ALLOC_ATP.DEBUG_WF ( p_session_id,
2616 l_login_user,
2617 l_session_loc_des,
2618 l_trace_loc_des,
2619 l_spid_des );
2620 END IF;
2621
2622 --Collection Enhancement changes end
2623 --aksaxena 1/23/2004
2624 --Code commented out as a part of Collection Enhancement changes
2625 --For collection enhancement one new private procedure is made
2626 --Update_table which will be called across dblink instead of
2627 --making multiple update statements across dblink.
2628
2629 --bug3520746 Delete temp records before exiting
2630 delete from msc_regions_temp where session_id = p_session_id;
2631
2632 IF PG_DEBUG in ('Y', 'C') THEN
2633 msc_sch_wb.atp_debug('Rows deleted from msc_regions_temp:'|| sql%rowcount);
2634 END IF;
2635
2636 --bug3940999 Delete temp records before exiting
2637 delete from msc_atp_src_profile_temp where session_id = p_session_id;
2638
2639 IF PG_DEBUG in ('Y', 'C') THEN
2640 msc_sch_wb.atp_debug('Rows deleted from msc_atp_src_profile_temp:'|| sql%rowcount);
2641 msc_sch_wb.atp_debug('End Call_ATP_No_Commit');
2642 END IF;
2643
2644 IF G_CALL_ATP = 2 THEN --4421391
2645 IF order_sch_wb.mr_debug in ('T','C') THEN
2646 disable_trace(L_RETURN_NUM);
2647 IF L_RETURN_NUM =-1 THEN
2648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2649 END IF;
2650 END IF;
2651 END IF;
2652
2653 EXCEPTION
2654 WHEN MSC_ATP_PUB.ATP_INVALID_OBJECTS_FOUND THEN
2655 IF PG_DEBUG in ('Y', 'C') THEN
2656 msc_sch_wb.atp_debug('Error in Call_ATP_No_Commit :'||sqlcode);
2657 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Invalid Objects found');
2658 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || sqlerrm);
2659 END IF;
2660 x_return_status := NVL(x_return_status, FND_API.G_RET_STS_ERROR);
2661 IF PG_DEBUG in ('Y', 'C') THEN
2662 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'shipset count ' ||p_atp_rec.error_code.count);
2663 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Exception x_return_status : '||x_return_status);
2664 END IF;
2665
2666 --ATP Debug Workflow, atp debug changes.
2667
2668 IF PG_DEBUG in ('Y', 'C') THEN
2669 MSC_WF_ALLOC_ATP.DEBUG_WF ( p_session_id,
2670 l_login_user,
2671 l_session_loc_des,
2672 l_trace_loc_des,
2673 l_spid_des );
2674 END IF;
2675
2676 BEGIN
2677 ROLLBACK TO SAVEPOINT start_of_call_atp_no_commit;
2678 EXCEPTION
2679 WHEN OTHERS THEN
2680 IF PG_DEBUG in ('Y', 'C') THEN
2681 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || sqlerrm);
2682 END IF;
2683 IF (sqlcode = -1086) THEN
2684 null;
2685 END IF;
2686 END;
2687 IF (x_atp_rec.Inventory_item_id.COUNT = 0) THEN
2688 IF (l_atp_rec.Inventory_item_id.COUNT = 0) THEN
2689 x_atp_rec:= p_atp_rec;
2690 ELSE
2691 x_atp_rec:= l_atp_rec;
2692 END IF;
2693 END IF;
2694 FOR i IN 1..x_atp_rec.Action.COUNT LOOP
2695 IF NVL (x_atp_rec.error_code(i),-1) in (-1,0,61,150) THEN
2696 x_atp_rec.Error_Code(i) := MSC_ATP_PVT.ATP_INVALID_OBJECTS;
2697 END IF;
2698 END LOOP;
2699
2700 --bug3520746 Delete temp records before exiting
2701 delete from msc_regions_temp where session_id = p_session_id;
2702 IF PG_DEBUG in ('Y', 'C') THEN
2703 msc_sch_wb.atp_debug('Rows deleted from msc_regions_temp:'|| sql%rowcount);
2704 END IF;
2705
2706 --bug3940999 Delete temp records before exiting
2707 delete from msc_atp_src_profile_temp where session_id = p_session_id;
2708 IF PG_DEBUG in ('Y', 'C') THEN
2709 msc_sch_wb.atp_debug('Rows deleted from msc_atp_src_profile_temp:'|| sql%rowcount);
2710 END IF;
2711
2712 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2713 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME , 'Call_ATP_No_Commit');
2714 END IF;
2715
2716 --- now we put the record back into temp table.
2717 --- ATP inquiry gets data from mrp_atp_schedule_temp table.
2718 --- If something goes wrong in ATP inquiry then we never get to the point to
2719 --- put the data back in the above table
2720 --AS a result we dont get a clue from ATP inquiry form as to what went wrong
2721 MSC_ATP_UTILS.put_into_temp_table(
2722 NULL, -- G_DB_PROFILE, dsting insert into source not dest
2723 p_session_id,
2724 x_atp_rec,
2725 x_atp_supply_demand,
2726 x_atp_period,
2727 x_atp_details,
2728 MSC_ATP_UTILS.REQUEST_MODE,
2729 x_return_status,
2730 x_msg_data,
2731 x_msg_count);
2732
2733 IF G_CALL_ATP = 2 THEN --4421391
2734 -- Set Sql Trace.
2735 IF order_sch_wb.mr_debug in ('T','C') THEN
2736 disable_trace(L_RETURN_NUM);
2737 IF L_RETURN_NUM =-1 THEN
2738 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2739 END IF;
2740 END IF;
2741 END IF;
2742
2743
2744 WHEN others THEN
2745 x_return_status := NVL(x_return_status, FND_API.G_RET_STS_ERROR);
2746 IF PG_DEBUG in ('Y', 'C') THEN
2747 msc_sch_wb.atp_debug('Error in Call_ATP_No_Commit :'||sqlcode);
2748 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || sqlerrm);
2749 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'shipset count ' ||p_atp_rec.error_code.count);
2750 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Exception x_return_status : '||x_return_status);
2751 END IF;
2752
2753 --bug3520746 Delete temp records before exiting
2754 delete from msc_regions_temp where session_id = p_session_id;
2755 IF PG_DEBUG in ('Y', 'C') THEN
2756 msc_sch_wb.atp_debug('Rows deleted from msc_regions_temp:'|| sql%rowcount);
2757 END IF;
2758
2759 --bug3940999 Delete temp records before exiting
2760 delete from msc_atp_src_profile_temp where session_id = p_session_id;
2761 IF PG_DEBUG in ('Y', 'C') THEN
2762 msc_sch_wb.atp_debug('Rows deleted from msc_atp_src_profile_temp:'|| sql%rowcount);
2763 END IF;
2764
2765 --ATP Debug Workflow, atp debug changes.
2766
2767 IF PG_DEBUG in ('Y', 'C') THEN
2768 MSC_WF_ALLOC_ATP.DEBUG_WF ( p_session_id,
2769 l_login_user,
2770 l_session_loc_des,
2771 l_trace_loc_des,
2772 l_spid_des );
2773 END IF;
2774
2775 BEGIN
2776 ROLLBACK TO SAVEPOINT start_of_call_atp_no_commit;
2777 EXCEPTION
2778 WHEN OTHERS THEN
2779 IF PG_DEBUG in ('Y', 'C') THEN
2780 msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || sqlerrm);
2781 END IF;
2782 IF (sqlcode = -1086) THEN
2783 null;
2784 END IF;
2785 END;
2786
2787 -- Error Handling Changes : krajan
2788 IF (x_atp_rec.Inventory_item_id.COUNT = 0) THEN
2789 IF (l_atp_rec.Inventory_item_id.COUNT = 0) THEN
2790 x_atp_rec:= p_atp_rec;
2791 ELSE
2792 x_atp_rec:= l_atp_rec;
2793 END IF;
2794 END IF;
2795 FOR i IN 1..x_atp_rec.Action.COUNT LOOP
2796 IF NVL (x_atp_rec.error_code(i),-1) in (-1,0,61,150) THEN
2797 x_atp_rec.Error_Code(i) := MSC_ATP_PVT.ATP_PROCESSING_ERROR;
2798 END IF;
2799 END LOOP;
2800 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2801 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME , 'Call_ATP_No_Commit');
2802 END IF;
2803 -- End Error Handling Changes
2804
2805 --- now we put the record back into temp table.
2806 --- ATP inquiry gets data from mrp_atp_schedule_temp table.
2807 --- If something goes wrong in ATP inquiry then we never get to the point to
2808 --- put the data back in the above table
2809 --AS a result we dont get a clue from ATP inquiry form as to what went wrong
2810 MSC_ATP_UTILS.put_into_temp_table(
2811 NULL, -- G_DB_PROFILE, dsting insert into src not dest
2812 p_session_id,
2813 x_atp_rec,
2814 x_atp_supply_demand,
2815 x_atp_period,
2816 x_atp_details,
2817 MSC_ATP_UTILS.REQUEST_MODE,
2818 x_return_status,
2819 x_msg_data,
2820 x_msg_count);
2821
2822 IF G_CALL_ATP = 2 THEN --4421391
2823 -- Set Sql Trace.
2824 IF order_sch_wb.mr_debug in ('T','C') THEN
2825 disable_trace(L_RETURN_NUM);
2826 IF L_RETURN_NUM =-1 THEN
2827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2828 END IF;
2829 END IF;
2830 END IF;
2831
2832
2833 END Call_ATP_No_Commit;
2834 /*******************************************************************************|
2835 New procedure as a part of Collection Enhancement changes
2836 |*******************************************************************************/
2837 PROCEDURE UPDATE_TABLES (p_summary_flag IN VARCHAR2,
2838 p_end_refresh_number IN NUMBER ,
2839 p_refresh_number IN NUMBER ,
2840 p_session_id IN NUMBER)
2841
2842 IS
2843
2844 l_end_refresh_number NUMBER := NULL;
2845 l_identifier1 MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
2846 l_identifier2 MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
2847 l_identifier3 MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
2848
2849 BEGIN
2850 IF PG_DEBUG in ('Y', 'C') THEN
2851 msc_sch_wb.atp_debug('Inside Procedure UPDATE_TABLES');
2852 msc_sch_wb.atp_debug('UPDATE_TABLES : p_end_refresh_number ' || p_end_refresh_number);
2853 msc_sch_wb.atp_debug('UPDATE_TABLES : p_refresh_number ' || p_refresh_number);
2854 msc_sch_wb.atp_debug('UPDATE_TABLES : p_summary_flag ' || p_summary_flag);
2855 msc_sch_wb.atp_debug('UPDATE_TABLES : p_session_id ' || p_session_id);
2856
2857 END IF;
2858 IF p_summary_flag = 'Y' THEN
2859 -- Summary is enabled in destination and at least one line did not result in PDS-ODS switch
2860 -- Now need to check if net summary is running or if latest_refresh_number has increased
2861 -- Currently doing this using the brute force method of going by msc_demands. Need to investigate
2862 -- possibility of using ATP pegging - potential issue there may be unschedules
2863
2864 select max(decode(p.summary_flag,
2865 MSC_POST_PRO.G_SF_NET_SUMMARY_RUNNING, p_refresh_number+1,
2866 p.latest_refresh_number))
2867 into l_end_refresh_number
2868 from msc_plans p,
2869 msc_demands d,
2870 mrp_atp_details_temp madt
2871 where d.plan_id = p.plan_id
2872 and d.refresh_number = p_refresh_number
2873 and d.plan_id = madt.identifier2
2874 and d.demand_id = madt.identifier3
2875 and d.sr_instance_id = madt.identifier1
2876 and madt.session_id = p_session_id
2877 -- and madt.supply_demand_type = 1
2878 -- Bug 3629191
2879 -- Supply_demand_type check is removed as it will filter out record in Unscheduling case.
2880 -- It will not fetch unwanted records (supply etc) as there is a filter on refresh number.
2881 -- Sql performance will be same after removing the check
2882 and madt.record_type = 3
2883 and NVL(madt.identifier2, -1) > 0
2884 and madt.identifier3 is not NULL
2885 and madt.identifier1 is not NULL;
2886
2887 IF PG_DEBUG in ('Y', 'C') THEN
2888 msc_sch_wb.atp_debug('No of rows selected ' || SQL%ROWCOUNT );
2889 END IF;
2890
2891 IF l_end_refresh_number > p_refresh_number THEN
2892 -- need to bump up all records
2893 IF PG_DEBUG in ('Y', 'C') THEN
2894 msc_sch_wb.atp_debug('Update 5 tables');
2895 END IF;
2896
2897 -- Bug 3517529: anbansal
2898 -- Used identifier1, identifier2 and identifier3 from pegging records to have additional check on
2899 -- sr_instance_id, plan_id and demand_id while updating tables ( msc_demands, msc_supplies,
2900 -- msc_resource_requirements, msc_alloc_demands, msc-alloc_supplies) in demand priority case
2901 -- to improve the performance.
2902
2903 SELECT identifier2,
2904 identifier3,
2905 identifier1
2906 BULK COLLECT INTO
2907 l_identifier2,
2908 l_identifier3,
2909 l_identifier1
2910 FROM MRP_ATP_DETAILS_TEMP
2911 where session_id = p_session_id
2912 and record_type = 3
2913 and NVL(identifier2, -1) > 0
2914 and identifier3 is not NULL
2915 and identifier1 is not NULL;
2916
2917 IF PG_DEBUG in ('Y', 'C') THEN
2918 msc_sch_wb.atp_debug('No of rows selected by BULK SELECT ' || SQL%ROWCOUNT );
2919 END IF;
2920
2921 FORALL i in 1..l_identifier1.COUNT
2922 update msc_demands
2923 set refresh_number = p_end_refresh_number
2924 where refresh_number = p_refresh_number
2925 and plan_id = l_identifier2(i)
2926 and demand_id = l_identifier3(i)
2927 and sr_instance_id = l_identifier1(i);
2928
2929 IF PG_DEBUG in ('Y', 'C') THEN
2930 msc_sch_wb.atp_debug('No of rows updated:msc_demands ' || SQL%ROWCOUNT );
2931 END IF;
2932
2933 FORALL i in 1..l_identifier1.COUNT
2934 update msc_supplies
2935 set refresh_number = p_end_refresh_number
2936 where refresh_number = p_refresh_number
2937 and plan_id = l_identifier2(i)
2938 and transaction_id = l_identifier3(i)
2939 and sr_instance_id = l_identifier1(i);
2940
2941 IF PG_DEBUG in ('Y', 'C') THEN
2942 msc_sch_wb.atp_debug('No of rows updated: msc_supplies ' || SQL%ROWCOUNT );
2943 END IF;
2944
2945 FORALL i in 1..l_identifier1.COUNT
2946 update msc_resource_requirements
2947 set refresh_number = p_end_refresh_number
2948 where refresh_number = p_refresh_number
2949 and plan_id = l_identifier2(i)
2950 and transaction_id = l_identifier3(i)
2951 and sr_instance_id = l_identifier1(i);
2952
2953 IF PG_DEBUG in ('Y', 'C') THEN
2954 msc_sch_wb.atp_debug('No of rows updated: msc_resource_requirements ' || SQL%ROWCOUNT );
2955 END IF;
2956
2957 FORALL i in 1..l_identifier1.COUNT
2958 update msc_alloc_demands
2959 set refresh_number = p_end_refresh_number
2960 where refresh_number = p_refresh_number
2961 and plan_id = l_identifier2(i)
2962 and parent_demand_id = l_identifier3(i)
2963 and sr_instance_id = l_identifier1(i);
2964
2965 IF PG_DEBUG in ('Y', 'C') THEN
2966 msc_sch_wb.atp_debug('No of rows updated: msc_alloc_demands ' || SQL%ROWCOUNT );
2967 END IF;
2968
2969 FORALL i in 1..l_identifier1.COUNT
2970 update msc_alloc_supplies
2971 set refresh_number = p_end_refresh_number
2972 where refresh_number = p_refresh_number
2973 and plan_id = l_identifier2(i)
2974 and parent_transaction_id = l_identifier3(i)
2975 and sr_instance_id = l_identifier1(i);
2976
2977 IF PG_DEBUG in ('Y', 'C') THEN
2978 msc_sch_wb.atp_debug('No of rows updated: msc_alloc_supplies ' || SQL%ROWCOUNT );
2979 END IF;
2980
2981 ELSE
2982 l_end_refresh_number := null;
2983 END IF;
2984
2985 END IF;
2986
2987 IF (l_end_refresh_number is null) THEN
2988 IF PG_DEBUG in ('Y', 'C') THEN
2989 msc_sch_wb.atp_debug('Need to bump refresh number up for 24X7');
2990 END IF;
2991
2992 -- Changed for the testing of Bug 3517529, But missed to remove it
2993 -- Changing back to the correct condition.
2994 -- IF p_refresh_number < (p_refresh_number + 1) THEN
2995 IF p_refresh_number < p_end_refresh_number THEN
2996 IF PG_DEBUG in ('Y', 'C') THEN
2997 msc_sch_wb.atp_debug('Update 1 Table');
2998 END IF;
2999
3000 update msc_demands
3001 set refresh_number = p_end_refresh_number
3002 --we need to update POD for model components
3003 where origination_type in (6,30,1)
3004 --refresh number will be populated either for SO or POD ofmodel entities
3005 and refresh_number is not null
3006 and (plan_id, demand_id, sr_instance_id) in
3007 ( select identifier2,
3008 identifier3,
3009 identifier1
3010 from mrp_atp_details_temp
3011 where session_id = p_session_id
3012 and supply_demand_type = 1
3013 and record_type = 3
3014 and NVL(identifier2, -1) > 0
3015 and identifier3 is not NULL
3016 and identifier1 is not NULL
3017 );
3018
3019 IF PG_DEBUG in ('Y', 'C') THEN
3020 msc_sch_wb.atp_debug('No of rows updated: msc_demands ' || SQL%ROWCOUNT );
3021 END IF;
3022 END IF;
3023 END IF;
3024 END UPDATE_TABLES;
3025
3026 END MSC_ATP_PUB;
[Home] [Help]