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