[Home] [Help]
PACKAGE BODY: APPS.CZ_OM_ATP_CALLBACK
Source
1 PACKAGE BODY cz_om_atp_callback AS
2 /* $Header: czatpcbb.pls 120.3 2007/05/29 19:59:02 qmao ship $ */
3
4 /*
5 12/05/2002 bug 2614660 fix: all lines related with vendor_name are removed
6 using (IN OUT) in extend_atp_record and append_mandatory_comps
7
8 01/23/2003 bug 2737013 fix
9 1. For consistency, dynamically call MSC_SATP_FUNC.extend_atp to extend atp_rec
10 instead of managing it locally in extend_atp_record
11 2. Remove counter=1 block and counter-checking code in the main procedure,
12 call_atp, i.e., treat counter=1 in the same way as counter>1
13 3. Need to check if ATP is configured on a remote DB across a dblink. If profile
14 option 'MRP_ATP_DATABASE_LINK' is set, get atp_session_id from remote DB.
15 Otherwise, obtain it from local DB
16 4. Retrieve any possible error message even when ATP call's return status is success
17
18 08/2003 APS patch set J support
19
20 05/20/2004 change in get atp session id by calling the new api: MSC_ATP_GLOBAL.get_atp_session_id
21 (This requires ATP's new patch 3604429 which contains the ATP team's change for
22 APS patch set J in the old patch 3052937)
23 */
24
25 EXPLODE_TYPE_INCLUDED CONSTANT VARCHAR2(10):= 'INCLUDED';
26 BOM_BILL_NOT_EXISTS CONSTANT NUMBER := 9998;
27 APS_VERSION_PATCHSET_J CONSTANT NUMBER := 10;
28 aps_version NUMBER;
29
30 extend_atp_rec_exc EXCEPTION;
31
32 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
33 TYPE uom_tbl_type IS TABLE OF bom_explosions.PRIMARY_UOM_CODE%TYPE INDEX BY PLS_INTEGER;
34
35 --------------------------------------------------------------------------------
36 -- based on oe_order_sch_util (not a public procedure)
37 -- having code here gives us more control anyway, some fields were added
38 -- Used only when MSC_SATP_FUNC.extend_atp does not exist
39 PROCEDURE extend_atp_record_local(p_count IN NUMBER)
40 IS
41 l_count NUMBER;
42 BEGIN
43 l_count := p_count;
44 g_atp_rec.Inventory_Item_Id.extend(l_count);
45 g_atp_rec.Source_Organization_Id.extend(l_count);
46 g_atp_rec.organization_id.extend(l_count);
47 g_atp_rec.Identifier.extend(l_count);
48 g_atp_rec.Calling_Module.extend(l_count);
49 g_atp_rec.Customer_Id.extend(l_count);
50 g_atp_rec.Customer_Site_Id.extend(l_count);
51 g_atp_rec.Destination_Time_Zone.extend(l_count);
52 g_atp_rec.Quantity_Ordered.extend(l_count);
53 g_atp_rec.Quantity_UOM.extend(l_count);
54 g_atp_rec.Requested_Ship_Date.extend(l_count);
55 g_atp_rec.Requested_Arrival_Date.extend(l_count);
56 g_atp_rec.Earliest_Acceptable_Date.extend(l_count);
57 g_atp_rec.Latest_Acceptable_Date.extend(l_count);
58 g_atp_rec.Delivery_Lead_Time.extend(l_count);
59 g_atp_rec.Atp_Lead_Time.extend(l_count);
60 g_atp_rec.Freight_Carrier.extend(l_count);
61 g_atp_rec.Ship_Method.extend(l_count);
62 g_atp_rec.Demand_Class.extend(l_count);
63 g_atp_rec.Ship_Set_Name.extend(l_count);
64 g_atp_rec.Arrival_Set_Name.extend(l_count);
65 g_atp_rec.Override_Flag.extend(l_count);
66 g_atp_rec.Action.extend(l_count);
67 g_atp_rec.ship_date.extend(l_count);
68 g_atp_rec.Available_Quantity.extend(l_count);
69 g_atp_rec.Requested_Date_Quantity.extend(l_count);
70 g_atp_rec.Group_Ship_Date.extend(l_count);
71 g_atp_rec.Group_Arrival_Date.extend(l_count);
72 g_atp_rec.Vendor_Id.extend(l_count);
73 g_atp_rec.Vendor_Site_Id.extend(l_count);
74 g_atp_rec.Insert_Flag.extend(l_count);
75 g_atp_rec.Error_Code.extend(l_count);
76 g_atp_rec.Message.extend(l_count);
77
78 -- OE doesn't provide these, might as well try them out
79 g_atp_rec.row_id.extend(l_count);
80 g_atp_rec.instance_id.extend(l_count);
81 g_atp_rec.inventory_item_name.extend(l_count);
82 g_atp_rec.source_organization_code.extend(l_count);
83 g_atp_rec.demand_source_header_id.extend(l_count);
84 g_atp_rec.demand_source_delivery.extend(l_count);
85 g_atp_rec.demand_source_type.extend(l_count);
86 g_atp_rec.scenario_id.extend(l_count);
87 -- g_atp_rec.vendor_name.extend(l_count); -- bug 2614660 fix
88 g_atp_rec.vendor_site_name.extend(l_count);
89 g_atp_rec.oe_flag.extend(l_count);
90 g_atp_rec.end_pegging_id.extend(l_count);
91
92 END extend_atp_record_local;
93
94 --------------------------------------------------------------------------------
95 -- pre-115.18: extend atp rec locally
96 --
97 -- 115.18 or later: call MSC_SATP_FUNC.extend_atp dynamically. If the MSC proc
98 -- does not exist, extend atp rec locally
99 --
100 -- aps patch set j: call msc new api msc_global_atp.extend_atp
101 --
102 PROCEDURE extend_atp_record(p_count IN NUMBER) IS
103
104 BEGIN
105 g_count := p_count;
106
107 IF (aps_version < APS_VERSION_PATCHSET_J) THEN
108 EXECUTE IMMEDIATE
109 'BEGIN MSC_SATP_FUNC.extend_atp ' ||
110 ' (p_atp_tab =>cz_om_atp_callback.g_atp_rec ' ||
111 ' ,x_return_status => cz_om_atp_callback.g_return_status ' ||
112 ' ,p_index => cz_om_atp_callback.g_count); ' ||
113 ' END;';
114 ELSE
115 MSC_ATP_GLOBAL.extend_atp(g_atp_rec, g_return_status, p_count);
116 END IF;
117
118 IF (g_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
119 RAISE extend_atp_rec_exc;
120 END IF;
121
122 EXCEPTION
123 WHEN extend_atp_rec_exc THEN
124 RAISE;
125
126 WHEN OTHERS THEN
127 IF (aps_version < APS_VERSION_PATCHSET_J) THEN
128 extend_atp_record_local(p_count);
129 ELSE
130 RAISE;
131 END IF;
132
133 END extend_atp_record;
134
135 --------------------------------------------------------------------------------
136 -- Appends mandatory components returned from cto_config_item_pk
137 -- to the original ATP record generated from cz_atp_requests
138 PROCEDURE append_mandatory_comps(p_mc_atp_rec IN MRP_ATP_PUB.atp_rec_typ) IS
139 l_atp_rec_length NUMBER;
140 l_atp_rec_index NUMBER;
141 l_num_mandatory_comps NUMBER;
142 i NUMBER;
143
144 BEGIN
145 -- number of items in ATP record
146 l_atp_rec_length := g_atp_rec.quantity_ordered.COUNT;
147 -- number of mandatory components to add
148 l_num_mandatory_comps := p_mc_atp_rec.quantity_ordered.COUNT;
149
150 extend_atp_record(l_num_mandatory_comps);
151
152 FOR i in 1..l_num_mandatory_comps LOOP
153 l_atp_rec_index := i + l_atp_rec_length;
154
155 g_atp_rec.calling_module(l_atp_rec_index) := p_mc_atp_rec.calling_module(i);
156 g_atp_rec.identifier(l_atp_rec_index) := p_mc_atp_rec.identifier(i);
157 g_atp_rec.ship_set_name(l_atp_rec_index) := p_mc_atp_rec.ship_set_name(i);
158 g_atp_rec.action(l_atp_rec_index) := p_mc_atp_rec.action(i);
159 g_atp_rec.organization_id(l_atp_rec_index) := p_mc_atp_rec.organization_id(i);
160 g_atp_rec.source_organization_id(l_atp_rec_index) :=
161 p_mc_atp_rec.source_organization_id(i);
162 g_atp_rec.customer_id(l_atp_rec_index) := p_mc_atp_rec.customer_id(i);
163 g_atp_rec.customer_site_id(l_atp_rec_index) := p_mc_atp_rec.customer_site_id(i);
164 g_atp_rec.requested_ship_date(l_atp_rec_index) :=
165 p_mc_atp_rec.requested_ship_date(i);
166 g_atp_rec.inventory_item_id(l_atp_rec_index) :=
167 p_mc_atp_rec.inventory_item_id(i);
168 g_atp_rec.quantity_uom(l_atp_rec_index) := p_mc_atp_rec.quantity_uom(i);
169 g_atp_rec.quantity_ordered(l_atp_rec_index) := p_mc_atp_rec.quantity_ordered(i);
170 g_atp_rec.destination_time_zone(l_atp_rec_index) :=
171 p_mc_atp_rec.destination_time_zone(i);
172 g_atp_rec.requested_arrival_date(l_atp_rec_index) :=
173 p_mc_atp_rec.requested_arrival_date(i);
174 g_atp_rec.earliest_acceptable_date(l_atp_rec_index) :=
175 p_mc_atp_rec.earliest_acceptable_date(i);
176 g_atp_rec.latest_acceptable_date(l_atp_rec_index) :=
177 p_mc_atp_rec.latest_acceptable_date(i);
178 g_atp_rec.delivery_lead_time(l_atp_rec_index) :=
179 p_mc_atp_rec.delivery_lead_time(i);
180 g_atp_rec.atp_lead_time(l_atp_rec_index) :=
181 p_mc_atp_rec.atp_lead_time(i);
182 g_atp_rec.freight_carrier(l_atp_rec_index) :=
183 p_mc_atp_rec.freight_carrier(i);
184 g_atp_rec.ship_method(l_atp_rec_index) := p_mc_atp_rec.ship_method(i);
185 g_atp_rec.demand_class(l_atp_rec_index) := p_mc_atp_rec.demand_class(i);
186 g_atp_rec.arrival_set_name(l_atp_rec_index) :=
187 p_mc_atp_rec.arrival_set_name(i);
188 g_atp_rec.override_flag(l_atp_rec_index) :=
189 p_mc_atp_rec.override_flag(i);
190 g_atp_rec.ship_date(l_atp_rec_index) := p_mc_atp_rec.ship_date(i);
191 g_atp_rec.group_ship_date(l_atp_rec_index) :=
192 p_mc_atp_rec.group_ship_date(i);
193 g_atp_rec.available_quantity(l_atp_rec_index) :=
194 p_mc_atp_rec.available_quantity(i);
195 g_atp_rec.requested_date_quantity(l_atp_rec_index) :=
196 p_mc_atp_rec.requested_date_quantity(i);
197 g_atp_rec.group_arrival_date(l_atp_rec_index) :=
198 p_mc_atp_rec.group_arrival_date(i);
199 g_atp_rec.vendor_id(l_atp_rec_index) := p_mc_atp_rec.vendor_id(i);
200 g_atp_rec.vendor_site_id(l_atp_rec_index) :=
201 p_mc_atp_rec.vendor_site_id(i);
202 g_atp_rec.insert_flag(l_atp_rec_index) := p_mc_atp_rec.insert_flag(i);
203 g_atp_rec.error_code(l_atp_rec_index) := p_mc_atp_rec.error_code(i);
204 g_atp_rec.message(l_atp_rec_index) := p_mc_atp_rec.message(i);
205 g_atp_rec.row_id(l_atp_rec_index) := p_mc_atp_rec.row_id(i);
206 g_atp_rec.instance_id(l_atp_rec_index) := p_mc_atp_rec.instance_id(i);
207 g_atp_rec.inventory_item_name(l_atp_rec_index) :=
208 p_mc_atp_rec.inventory_item_name(i);
209 g_atp_rec.source_organization_code(l_atp_rec_index) :=
210 p_mc_atp_rec.source_organization_code(i);
211 g_atp_rec.demand_source_header_id(l_atp_rec_index) :=
212 p_mc_atp_rec.demand_source_header_id(i);
213 g_atp_rec.demand_source_delivery(l_atp_rec_index) :=
214 p_mc_atp_rec.demand_source_delivery(i);
215 g_atp_rec.demand_source_type(l_atp_rec_index) :=
216 p_mc_atp_rec.demand_source_type(i);
217 g_atp_rec.scenario_id(l_atp_rec_index) := p_mc_atp_rec.scenario_id(i);
218 -- g_atp_rec.vendor_name(l_atp_rec_index) := p_mc_atp_rec.vendor_name(i); -- bug 2614660 fix
219 g_atp_rec.vendor_site_name(l_atp_rec_index) :=
220 p_mc_atp_rec.vendor_site_name(i);
221 g_atp_rec.oe_flag(l_atp_rec_index) := p_mc_atp_rec.oe_flag(i);
222 g_atp_rec.end_pegging_id(l_atp_rec_index) := p_mc_atp_rec.end_pegging_id(i);
223 END LOOP;
224
225 END append_mandatory_comps;
226
227 --------------------------------------------------------------------------------
228 -- bug 5723470: cto only returns ATO's MCs, so we need to get PTO's MCs on our own
229 -- g_atp_rec contains the recs from cz runtime as well as ATO MCs (if any) from cto
230 -- p_count number of atp records from cz runtime
231 PROCEDURE get_pto_mandatory_components(p_top_item_id IN NUMBER
232 ,p_organization_id IN NUMBER
233 ,p_count IN NUMBER
234 ,x_itm_tbl OUT NOCOPY num_tbl_type
235 ,x_qty_tbl OUT NOCOPY num_tbl_type
236 ,x_uom_tbl OUT NOCOPY uom_tbl_type
237 ,x_ret_status OUT NOCOPY NUMBER
238 ,x_msg_data OUT NOCOPY VARCHAR2
239 )
240 IS
241 l_pto_flag mtl_system_items_b.pick_components_flag%TYPE;
242 l_grp_id NUMBER;
243 l_err_code NUMBER;
244 l_err_msg VARCHAR2(2000);
245
246 l_itm_tbl num_tbl_type;
247 l_qty_tbl num_tbl_type;
248 l_uom_tbl uom_tbl_type;
249 l_count INTEGER;
250
251 BEGIN
252 x_ret_status := 0;
253
254 SELECT UPPER(pick_components_flag) INTO l_pto_flag
255 FROM mtl_system_items_b
256 WHERE inventory_item_id = p_top_item_id AND organization_id = p_organization_id;
257
258 IF l_pto_flag <> 'Y' THEN
259 RETURN;
260 END IF;
261
262 l_count := 0;
263 FOR i IN g_atp_rec.inventory_item_id.FIRST .. p_count LOOP
264 IF g_atp_rec.inventory_item_id(i) <> p_top_item_id THEN
265 SELECT UPPER(pick_components_flag) INTO l_pto_flag
266 FROM mtl_system_items_b
267 WHERE inventory_item_id = g_atp_rec.inventory_item_id(i)
268 AND organization_id = p_organization_id;
269 END IF;
270
271 -- l_err_code := 0;
272 IF l_pto_flag = 'Y' THEN
273 bompnord.bmxporder_explode_for_order(
274 org_id => p_organization_id,
275 copy_flag => 2,
276 expl_type => EXPLODE_TYPE_INCLUDED,
277 order_by => 2,
278 grp_id => l_grp_id,
279 session_id => 0,
280 levels_to_explode => 60,
281 item_id => g_atp_rec.inventory_item_id(i),
282 rev_date => to_char(SYSDATE,'YYYY/MM/DD HH24:MI'),
283 user_id => 0,
284 commit_flag => 'N',
285 err_msg => l_err_msg,
286 error_code => l_err_code);
287
288 IF l_err_code = 0 THEN
289 l_itm_tbl.DELETE;
290 l_qty_tbl.DELETE;
291 l_uom_tbl.DELETE;
292 -- Note bom explosion with included mode only contains mc std items
293 -- otherwise we would need to check bom_item_type = 4
294 SELECT component_item_id, extended_quantity, primary_uom_code
295 BULK COLLECT INTO l_itm_tbl, l_qty_tbl, l_uom_tbl
296 FROM bom_explosions
297 WHERE explosion_type = EXPLODE_TYPE_INCLUDED
298 AND plan_level > 0
299 AND extended_quantity > 0
300 AND TOP_BILL_SEQUENCE_ID =
301 (SELECT bill_sequence_id
302 FROM bom_bill_of_materials
303 WHERE assembly_item_id = g_atp_rec.inventory_item_id(i)
304 AND organization_id = p_organization_id
305 AND ALTERNATE_BOM_DESIGNATOR IS NULL)
306 AND EFFECTIVITY_DATE <= SYSDATE AND DISABLE_DATE > SYSDATE; -- AND check_atp = 1?
307
308 IF l_itm_tbl.COUNT > 0 THEN
309 FOR j IN l_itm_tbl.FIRST .. l_itm_tbl.LAST LOOP
310 l_count := l_count + 1;
311 x_itm_tbl(l_count) := l_itm_tbl(j);
312 x_qty_tbl(l_count) := l_qty_tbl(j) * g_atp_rec.quantity_ordered(i);
313 x_uom_tbl(l_count) := l_uom_tbl(j);
314 END LOOP;
315 END IF;
316
317 ELSIF l_err_code <> BOM_BILL_NOT_EXISTS THEN
318 x_ret_status := l_err_code;
319 x_msg_data := 'Bom exploding error with item ' || to_char(g_atp_rec.inventory_item_id(i))
320 || substr(l_err_msg, 1, 1950);
321 RETURN;
322 END IF;
323 END IF;
324 END LOOP;
325 END get_pto_mandatory_components;
326
327 PROCEDURE set_null_fields(p_counter IN NUMBER)
328 IS
329 BEGIN
330 -- providing these either because they are output params or because
331 -- oe_order_sch_util provides them
332 g_atp_rec.destination_time_zone(p_counter) := null;
333 g_atp_rec.requested_arrival_date(p_counter) := null;
334 g_atp_rec.earliest_acceptable_date(p_counter) := null;
335 g_atp_rec.latest_acceptable_date(p_counter) := null;
336 g_atp_rec.delivery_lead_time(p_counter) := null;
337 g_atp_rec.atp_lead_time(p_counter) := null;
338 g_atp_rec.freight_carrier(p_counter) := null;
339 g_atp_rec.ship_method(p_counter) := null;
340 g_atp_rec.demand_class(p_counter) := null;
341 g_atp_rec.arrival_set_name(p_counter) := null;
342 g_atp_rec.override_flag(p_counter) := null;
343 g_atp_rec.ship_date(p_counter) := null;
344 g_atp_rec.group_ship_date(p_counter) := null;
345 g_atp_rec.available_quantity(p_counter) := null;
346 g_atp_rec.requested_date_quantity(p_counter) := null;
347 g_atp_rec.group_arrival_date(p_counter) := null;
348 g_atp_rec.vendor_id(p_counter) := null;
349 g_atp_rec.vendor_site_id(p_counter) := null;
350 g_atp_rec.insert_flag(p_counter) := null;
351 g_atp_rec.error_code(p_counter) := null;
352 g_atp_rec.message(p_counter) := null;
353
354 -- the OE procedure doesn't provide these, but I might as well try
355 g_atp_rec.row_id(p_counter) := null;
356 g_atp_rec.instance_id(p_counter) := null;
357 g_atp_rec.inventory_item_name(p_counter) := null;
358 g_atp_rec.source_organization_code(p_counter) := null;
359 g_atp_rec.demand_source_header_id(p_counter) := null;
360 g_atp_rec.demand_source_delivery(p_counter) := null;
361 g_atp_rec.demand_source_type(p_counter) := null;
362 g_atp_rec.scenario_id(p_counter) := null;
363 -- g_atp_rec.vendor_name(p_counter) := null; -- bug 2614660 fix
364 g_atp_rec.vendor_site_name(p_counter) := null;
365 g_atp_rec.oe_flag(p_counter) := null;
366 g_atp_rec.end_pegging_id(p_counter) := null;
367 END set_null_fields;
368
369 --------------------------------------------------------------------------------
370 -- Main procedure, calls MRP ATP procedure
371 --
372 -- REQUIRED params - p_config_session_key, p_requested_date
373 -- and one of the following:
374 -- 1. p_warehouse_id
375 -- 2. p_ship_to_org_id
376 -- 3. p_customer_id and p_customer_site_id
377 -- NOTES: For 11.5.1, p_warehouse_id is REQUIRED
378 -- If p_requested_date is NULL, SYSDATE will be used
379 PROCEDURE call_atp (p_config_session_key IN VARCHAR2,
380 p_warehouse_id IN NUMBER,
381 p_ship_to_org_id IN NUMBER,
382 p_customer_id IN NUMBER,
383 p_customer_site_id IN NUMBER,
384 p_requested_date IN DATE,
385 p_ship_to_group_date OUT NOCOPY DATE) IS
386 PRAGMA AUTONOMOUS_TRANSACTION;
387
388 CURSOR atp_requests IS
389 SELECT atp_request_id, seq_no, ps_node_id, item_key, item_key_type,
390 quantity, uom_code, config_item_id, parent_config_item_id,
391 ato_config_item_id, component_sequence_id
392 FROM cz_atp_requests
393 WHERE configurator_session_key = p_config_session_key
394 ORDER BY seq_no;
395
399 = p_config_session_key ORDER BY seq_no FOR UPDATE OF
396 -- future: add need_by_date, days_late
397 CURSOR update_atp_requests IS
398 SELECT * FROM cz_atp_requests WHERE configurator_session_key
400 ship_to_date, msg_data;
401
402 l_counter NUMBER;
403 l_requested_date DATE;
404 l_ship_date DATE;
405 l_atp_row_count NUMBER;
406 l_atp_row atp_requests%ROWTYPE;
407 l_atp_update_row update_atp_requests%ROWTYPE;
408 l_atp_rec MRP_ATP_PUB.atp_rec_typ;
409 l_ship_set_name VARCHAR2(30);
410 l_top_model_line_id NUMBER;
411 l_validation_org NUMBER;
412 l_pto_model_flag mtl_system_items.pick_components_flag%TYPE;
413
414 -- variables used for mandatory component addition
415 l_mc_result NUMBER;
416 l_mc_atp_rec MRP_ATP_PUB.atp_rec_typ;
417 l_mc_error VARCHAR2(2000);
418 l_mc_message_name VARCHAR2(100);
419 l_mc_tbl_name VARCHAR2(100);
420 l_mc_exception EXCEPTION;
421
422 l_pto_mc_exception EXCEPTION;
423 l_itm_tbl num_tbl_type;
424 l_qty_tbl num_tbl_type;
425 l_uom_tbl uom_tbl_type;
426 l_err_code NUMBER;
427
428 -- inventory item ID of model
429 l_model_inv_item_id NUMBER;
430
431 l_action NUMBER;
432 l_cz_appl_id NUMBER;
433 l_atp_session_id NUMBER;
434 l_atp_supply_demand MRP_ATP_PUB.atp_supply_demand_typ;
435 l_atp_period MRP_ATP_PUB.atp_period_typ;
436 l_atp_details MRP_ATP_PUB.atp_details_typ;
437 l_return_status VARCHAR2(10);
438 l_msg_data VARCHAR2(2000);
439 l_msg_count NUMBER;
440 l_error_msg_count NUMBER;
441
442 l_warehouse_id_exc EXCEPTION;
443 l_call_atp_exc EXCEPTION;
444 l_validation_org_exc EXCEPTION;
445 l_atp_session_id_exc EXCEPTION;
446
447 l_ndebug NUMBER;
448
449 empty_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
450 BEGIN
451 l_ndebug := 0;
452 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
453 'CZ session ' || p_config_session_key || ': starting cz_om_atp_callback.call_atp',
454 fnd_log.LEVEL_PROCEDURE);
455
456 aps_version := MSC_ATP_GLOBAL.get_aps_version;
457 l_ndebug := 1;
458 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
459 'aps_version ' || aps_version, fnd_log.LEVEL_PROCEDURE);
460
461 g_atp_rec := empty_atp_rec;
462
463 -- 11.5.1 limitation, warehouse_id is REQUIRED
464 IF p_warehouse_id IS NULL AND aps_version < APS_VERSION_PATCHSET_J THEN
465 raise l_warehouse_id_exc;
466 END IF;
467
468 IF p_requested_date IS NULL THEN
469 l_requested_date := sysdate;
470 ELSE
471 l_requested_date := p_requested_date;
472 END IF;
473
474 -- determine array size
475 SELECT count(*) INTO l_atp_row_count FROM cz_atp_requests
476 WHERE configurator_session_key = p_config_session_key;
477
478 extend_atp_record(l_atp_row_count);
479 l_ndebug := 2;
480
481 l_counter := 1;
482 -- 100 is value for OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK
483 -- don't know why the number isn't stored in a constant
484 -- 100 - ATP Inquiry (see ATP white paper)
485 l_action := 100;
486 l_cz_appl_id := 708;
487 l_ship_set_name := 'A';
488 FOR l_atp_row IN atp_requests LOOP
489 IF (aps_version >= APS_VERSION_PATCHSET_J) THEN
490 IF (l_counter = 1) THEN
491 l_top_model_line_id := l_atp_row.config_item_id;
492 l_validation_org := cz_utils.conv_num(oe_sys_parameters.value('MASTER_ORGANIZATION_ID'));
493 IF (l_validation_org IS NULL) THEN
494 RAISE l_validation_org_exc;
495 END IF;
496 END IF;
497 g_atp_rec.ato_model_line_id(l_counter) := l_atp_row.ato_config_item_id;
498 g_atp_rec.parent_line_id(l_counter) := l_atp_row.parent_config_item_id;
499 g_atp_rec.top_model_line_id(l_counter) := l_top_model_line_id;
500 -- Let MRP/MSC explode std MCs for PTO components? 1 or null - No, 2 - Yes
501 -- Note MRP/MSC always explodes ATO's std MCs
502 g_atp_rec.included_item_flag(l_counter) := 2;
503 g_atp_rec.validation_org(l_counter) := l_validation_org;
504 g_atp_rec.component_sequence_id(l_counter) := l_atp_row.component_sequence_id;
505 g_atp_rec.component_code(l_counter) :=
506 substr(l_atp_row.item_key, 1, instr(l_atp_row.item_key, ':')-1);
507 END IF;
508
509 g_atp_rec.calling_module(l_counter) := l_cz_appl_id;
510 g_atp_rec.identifier(l_counter) := l_atp_row.config_item_id; -- line_id
511 g_atp_rec.ship_set_name(l_counter) := l_ship_set_name;
512 g_atp_rec.action(l_counter) := l_action;
513 g_atp_rec.organization_id(l_counter) := p_ship_to_org_id;
514 g_atp_rec.source_organization_id(l_counter) := p_warehouse_id;
515 g_atp_rec.customer_id(l_counter) := p_customer_id;
516 g_atp_rec.customer_site_id(l_counter) := p_customer_site_id;
517 g_atp_rec.requested_ship_date(l_counter) := l_requested_date;
518 g_atp_rec.inventory_item_id(l_counter) :=
519 cz_atp_callback_util.inv_item_id_from_item_key(l_atp_row.item_key);
520 g_atp_rec.quantity_uom(l_counter) := l_atp_row.uom_code;
521 g_atp_rec.quantity_ordered(l_counter) := l_atp_row.quantity;
522 set_null_fields(l_counter);
526 l_ndebug := 3;
523 l_counter := l_counter + 1;
524 END LOOP;
525
527
528 IF (aps_version < APS_VERSION_PATCHSET_J) THEN
529 -- add mandatory components to g_atp_rec
530 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
531 'Before calling get_mandatory_comps.', fnd_log.LEVEL_PROCEDURE);
532 l_model_inv_item_id := g_atp_rec.inventory_item_id(1);
533 l_mc_result :=
534 cto_config_item_pk.get_mandatory_components(g_atp_rec,
535 p_warehouse_id, l_model_inv_item_id, l_mc_atp_rec,
536 l_mc_error, l_mc_message_name, l_mc_tbl_name);
537
538 l_ndebug := 4;
539 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
540 'After calling get_mandatory_comps.', fnd_log.LEVEL_PROCEDURE);
541
542 IF l_mc_result = 0 THEN
543 raise l_mc_exception;
544 END IF;
545
546 append_mandatory_comps(l_mc_atp_rec);
547
548 get_pto_mandatory_components(l_model_inv_item_id
549 ,cz_atp_callback_util.validation_org_for_cfg_model(p_config_session_key)
550 ,l_atp_row_count
551 ,l_itm_tbl
552 ,l_qty_tbl
553 ,l_uom_tbl
554 ,l_err_code
555 ,l_msg_data);
556 IF l_err_code <> 0 THEN
557 RAISE l_pto_mc_exception;
558 END IF;
559
560 IF l_itm_tbl.COUNT > 0 THEN
561 extend_atp_record(l_itm_tbl.COUNT);
562 l_counter := l_atp_row_count + l_mc_atp_rec.inventory_item_id.COUNT + 1;
563 FOR i IN l_itm_tbl.FIRST .. l_itm_tbl.LAST LOOP
564 g_atp_rec.calling_module(l_counter) := l_cz_appl_id;
565 g_atp_rec.identifier(l_counter) := i;
566 g_atp_rec.ship_set_name(l_counter) := l_ship_set_name;
567 g_atp_rec.action(l_counter) := l_action;
568 g_atp_rec.organization_id(l_counter) := p_ship_to_org_id;
569 g_atp_rec.source_organization_id(l_counter) := p_warehouse_id;
570 g_atp_rec.customer_id(l_counter) := p_customer_id;
571 g_atp_rec.customer_site_id(l_counter) := p_customer_site_id;
572 g_atp_rec.requested_ship_date(l_counter) := l_requested_date;
573 g_atp_rec.inventory_item_id(l_counter) := l_itm_tbl(i);
574 g_atp_rec.quantity_uom(l_counter) := l_uom_tbl(i);
575 g_atp_rec.quantity_ordered(l_counter) := l_qty_tbl(i);
576 set_null_fields(l_counter);
577 l_counter := l_counter + 1;
578 END LOOP;
579 END IF;
580 END IF;
581 l_ndebug := 5;
582
583 -- calculate ATP
584 MSC_ATP_GLOBAL.get_atp_session_id(l_atp_session_id, l_return_status);
585 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
586 raise l_atp_session_id_exc;
587 END IF;
588
589 l_ndebug := 6;
590 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
591 'CZ session ' || p_config_session_key || ': calling mrp_atp_pub.call_atp with ATP session ID '
592 || l_atp_session_id, fnd_log.LEVEL_PROCEDURE);
593 l_atp_rec := g_atp_rec;
594 MRP_ATP_PUB.call_atp(l_atp_session_id, l_atp_rec,
595 g_atp_rec, l_atp_supply_demand,
596 l_atp_period, l_atp_details,
597 l_return_status, l_msg_data,
598 l_msg_count);
599 l_ndebug := 7;
600 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
601 'CZ session ' || p_config_session_key || ': mrp_atp_pub.call_atp - Ret.Status : '
602 || l_return_status, fnd_log.LEVEL_PROCEDURE);
603 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
604 'CZ session ' || p_config_session_key || ': mrp_atp_pub.call_atp - Msg.Data : '
605 || l_msg_data, fnd_log.LEVEL_PROCEDURE);
606 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
607 'CZ session ' || p_config_session_key || ': mrp_atp_pub.call_atp - Msg.Count : '
608 || l_msg_count, fnd_log.LEVEL_PROCEDURE);
609
610 -- For expected errors, ATP populates the error_code for each record in atp_rec_typ.
611 -- The error_code is defined by the lookup_type 'MTL_DEMAND_INTERFACE_ERRORS' in the
612 -- table mfg_lookups.
613 -- Display error messages in ATP notification window by selecting 'meaning' for the given error_codes
614 -- bug 2737013 fix: retrieve any possible error message even when the return status is success
615 IF l_return_status = FND_API.G_RET_STS_ERROR OR l_return_status = FND_API.G_RET_STS_SUCCESS THEN
616 l_error_msg_count := g_atp_rec.error_code.COUNT;
617 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
618 'Known Error ... Count : ' || l_error_msg_count, fnd_log.LEVEL_STATEMENT);
619
620 -- Note: In aps patchset j, the returned g_atp_rec.message from MRP is uninitialized
621 -- (see bug 3358937). So here we use l_atp_rec.message for storing err msg instead.
622 -- here we are assuming g_atp_rec.error_code always initialized
623 FOR i in 1..l_error_msg_count LOOP
624 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
625 'Get err message ' || i || ' Err Code :' || g_atp_rec.error_code(i),
626 fnd_log.LEVEL_STATEMENT);
627 if g_atp_rec.error_code(i) is null or g_atp_rec.error_code(i) = -99
628 OR g_atp_rec.error_code(i) = 61 -- filter out "atp not applicable" message
632 SELECT meaning INTO l_atp_rec.message(i)
629 OR g_atp_rec.error_code(i) = 0 then
630 l_atp_rec.message(i) := NULL;
631 else
633 FROM mfg_lookups
634 WHERE lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
635 AND lookup_code = g_atp_rec.error_code(i);
636 end if;
637 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
638 'Lookup Err. Message : ' || l_atp_rec.message(i),
639 fnd_log.LEVEL_STATEMENT);
640 END LOOP;
641
642 -- will display error message using x_msg_data returned from MRP_ATP_PUB.CALL_ATP
643 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
644 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
645 'UnExpected Error from ...', fnd_log.LEVEL_STATEMENT);
646 RAISE l_call_atp_exc;
647 END IF;
648
649 -- run through results, populate cz_atp_requests
650 -- NOTE: this may need to change, depending on what happens
651 -- with mandatory comps
652
653 l_ndebug := 8;
654 l_counter := 1;
655 FOR l_atp_update_row IN update_atp_requests LOOP
656 -- no date should be displayed if error code is -99
657 if (g_atp_rec.error_code(l_counter) = -99) then
658 l_ship_date := null;
659 else
660 l_ship_date := g_atp_rec.ship_date(l_counter);
661 end if;
662
663 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
664 p_config_session_key || ': updating ATP requests rec, l_ship_date: ' ||
665 to_char(l_ship_date, 'MM-DD-YYYY HH24:MI:SS'),
666 fnd_log.LEVEL_STATEMENT);
667
668 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
669 'Updating ATP requests rec, message: ' || l_atp_rec.message(l_counter),
670 fnd_log.LEVEL_STATEMENT);
671
672 -- if atp returns need_by_date and days_late in g_atp_rec
673 -- we will update the fields here
674
675 UPDATE cz_atp_requests SET msg_data = l_atp_rec.message(l_counter),
676 ship_to_date = l_ship_date
677 WHERE CURRENT OF update_atp_requests;
678
679 l_counter := l_counter + 1;
680 END LOOP;
681 COMMIT;
682
683 l_ndebug := 9;
684 -- return group ship date
685 -- the following logic will be changed because there are some special cases
686 p_ship_to_group_date := g_atp_rec.group_ship_date(1);
687 IF (aps_version >= APS_VERSION_PATCHSET_J) THEN
688 SELECT pick_components_flag INTO l_pto_model_flag
689 FROM mtl_system_items
690 WHERE inventory_item_id = g_atp_rec.inventory_item_id(1)
691 AND organization_id = l_validation_org;
692
693 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
694 'Top model is a pto? ' || l_pto_model_flag, fnd_log.LEVEL_STATEMENT);
695
696 IF (upper(l_pto_model_flag) <> 'Y') THEN
697 p_ship_to_group_date := g_atp_rec.ship_date(1);
698 END IF;
699
700 -- if atp provides an api for retrieving need_by_date and days_late,
701 -- we will need to put those outputs into cz_atp_requests
702
703 END IF;
704
705 l_ndebug := 10;
706 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
707 'CZ session ' || p_config_session_key || ': returning group ship date: '
708 || To_char(p_ship_to_group_date), fnd_log.LEVEL_PROCEDURE);
709 g_atp_rec := empty_atp_rec;
710
711 EXCEPTION
712 WHEN extend_atp_rec_exc THEN
713 g_atp_rec := empty_atp_rec;
714 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
715 'Unexpected error in cz_om_atp_callback returned from MSC_SATP_FUNC.extend_atp',
716 fnd_log.LEVEL_ERROR);
717 UPDATE cz_atp_requests SET msg_data = 'Error in executing MSC_SATP_FUNC.extend_atp'
718 WHERE configurator_session_key = p_config_session_key
719 AND seq_no = 1;
720 COMMIT;
721
722 WHEN l_validation_org_exc THEN
723 g_atp_rec := empty_atp_rec;
724 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
725 'Unexpected error in cz_om_atp_callback: NULL organization id returned from ' ||
726 'oe_sys_parameters.value(MASTER_ORGANIZATION_ID)', fnd_log.LEVEL_ERROR);
727 UPDATE cz_atp_requests SET msg_data = 'Error in executing oe_sys_parameters.value for ' ||
728 'MASTER_ORGANIZATION_ID: Null value returned'
729 WHERE configurator_session_key = p_config_session_key
730 AND seq_no = 1;
731 COMMIT;
732
733 WHEN l_mc_exception THEN
734 g_atp_rec := empty_atp_rec;
735 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
736 'Error in mandatory comp expl proc: '|| l_mc_error,
737 fnd_log.LEVEL_ERROR);
738 UPDATE cz_atp_requests SET msg_data =
739 'Mandatory comp expl error: ' || l_mc_error
740 WHERE configurator_session_key = p_config_session_key
741 AND seq_no = 1;
742 COMMIT;
743 WHEN l_warehouse_id_exc THEN
744 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
745 'No warehouse supplied to ATP call.', fnd_log.LEVEL_ERROR);
746 UPDATE cz_atp_requests SET msg_data = CZ_UTILS.GET_TEXT('CZ_ATP_NO_WAREHOUSE')
747 WHERE configurator_session_key = p_config_session_key
748 AND seq_no = 1;
749 COMMIT;
750
751 WHEN l_atp_session_id_exc THEN
752 l_msg_data := 'Fail in getting an atp session id from MSC_ATP_GLOBAL';
753 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
754 l_msg_data, fnd_log.LEVEL_ERROR);
755 UPDATE cz_atp_requests SET msg_data = l_msg_data
756 WHERE configurator_session_key = p_config_session_key
757 AND seq_no = 1;
758 COMMIT;
759
760 WHEN l_call_atp_exc THEN
761 g_atp_rec := empty_atp_rec;
762 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug,
763 'Unexpected error in cz_om_atp_callback returned from mrp_atp_pub: ' || l_msg_data,
764 fnd_log.LEVEL_ERROR);
765
766 UPDATE cz_atp_requests SET msg_data =
767 'Unexpected error in cz_om_atp_callback returned from mrp_atp_pub: ' || l_msg_data
768 WHERE configurator_session_key = p_config_session_key
769 AND seq_no = 1;
770 COMMIT;
771 WHEN OTHERS THEN
772 g_atp_rec := empty_atp_rec;
773 l_msg_data := 'Unexpected error in cz_om_atp_callback statement ' || l_ndebug || ': '
774 || substr(SQLERRM, 1, 1500);
775 cz_utils.log_report('cz_om_atp_callback', 'call_atp', l_ndebug, l_msg_data,
776 fnd_log.LEVEL_UNEXPECTED);
777 UPDATE cz_atp_requests SET msg_data = l_msg_data
778 WHERE configurator_session_key = p_config_session_key
779 AND seq_no = 1;
780 COMMIT;
781 END call_atp;
782
783 END cz_om_atp_callback;