1 PACKAGE BODY aso_atp_int as
2 /* $Header: asoiatpb.pls 120.13 2006/09/18 23:34:36 skulkarn ship $ */
3
4 -- API Name: Check_ATP
5 -- Type : Public
6 -- Pre-Req : Assumption is that p_qte_line_tbl and p_shipment_tbl are
7 -- synchronised. The same index should hold values for a
8 -- particular line.
9 -- History
10 -- 12/12/2002 hyang - bug 2707989, changed default value to number for
11 -- l_api_version_number.
12 -- 06/03/04 skulkarn - bug 3604265, changed description to segment1 in cursor
13 -- c_description
14
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_ATP_INT';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoiatpb.pls';
17
18
19 PROCEDURE Call_ATP_Commit (p_session_id OUT NOCOPY /* file.sql.39 change */ NUMBER, p_dblink IN VARCHAR2 )
20 IS
21 PRAGMA AUTONOMOUS_TRANSACTION;
22 cursor_name NUMBER;
23 cursor_c1 NUMBER;
24 rows_1 NUMBER;
25 rows_2 NUMBER;
26 l_statement VARCHAR2(200);
27 l_session_id NUMBER;
28 BEGIN
29
30 cursor_name := dbms_sql.open_cursor;
31 DBMS_SQL.PARSE(cursor_name, 'alter session close database link ' ||p_dblink, dbms_sql.native);
32
33 cursor_c1 := dbms_sql.open_cursor;
34 DBMS_SQL.PARSE(cursor_c1, 'Select MRP_ATP_SCHEDULE_TEMP_S.NextVal@' || p_dblink ||' From Dual', dbms_sql.native);
35
36 BEGIN
37 dbms_sql.define_column( cursor_c1, 1, l_session_id );
38 rows_1 := dbms_sql.execute(cursor_c1);
39 if dbms_sql.fetch_rows( cursor_c1 ) > 0 then
40 dbms_sql.column_value( cursor_c1, 1, l_session_id );
41
42 end if;
43 p_session_id := l_session_id;
44 END;
45
46
47 DBMS_SQL.close_cursor(cursor_c1);
48 commit;
49
50 BEGIN
51 rows_2 := dbms_sql.execute(cursor_name);
52 EXCEPTION
53 WHEN OTHERS THEN
54 null;
55 END;
56
57 DBMS_SQL.close_cursor(cursor_name);
58
59
60 END Call_ATP_Commit;
61
62
63
64
65
66
67 PROCEDURE Extend_ATP (p_atp_tbl IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
68 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
69 IS
70 BEGIN
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72
73 p_atp_tbl.Row_Id.Extend;
74 p_atp_tbl.Inventory_Item_Id.Extend;
75 p_atp_tbl.Inventory_Item_Name.Extend;
76 p_atp_tbl.Source_Organization_Id.Extend;
77 p_atp_tbl.Source_Organization_Code.Extend;
78 p_atp_tbl.Organization_Id.Extend;
79 p_atp_tbl.Identifier.Extend;
80 --p_atp_tbl.Demand_Source_Header_Id.Extend;
81 --p_atp_tbl.Demand_Source_Delivery.Extend;
82 --p_atp_tbl.Demand_Source_Type.Extend;
83 p_atp_tbl.Scenario_Id.Extend;
84 p_atp_tbl.Calling_Module.Extend;
85 p_atp_tbl.Customer_Id.Extend;
86 p_atp_tbl.Customer_Site_Id.Extend;
87 p_atp_tbl.Destination_Time_Zone.Extend;
88 p_atp_tbl.Quantity_Ordered.Extend;
89 p_atp_tbl.Quantity_UOM.Extend;
90 p_atp_tbl.Requested_Ship_Date.Extend;
91 p_atp_tbl.Requested_Arrival_Date.Extend;
92 p_atp_tbl.Earliest_Acceptable_Date.Extend;
93 p_atp_tbl.Latest_Acceptable_Date.Extend;
94 p_atp_tbl.Delivery_Lead_Time.Extend;
95 p_atp_tbl.Freight_Carrier.Extend;
96 p_atp_tbl.Ship_Method.Extend;
97 p_atp_tbl.Demand_Class.Extend;
98 p_atp_tbl.Ship_Set_Name.Extend;
99 p_atp_tbl.Arrival_Set_Name.Extend;
100 p_atp_tbl.Override_Flag.Extend;
101 p_atp_tbl.Action.Extend;
102 p_atp_tbl.Ship_Date.Extend;
103 p_atp_tbl.Available_Quantity.Extend;
104 p_atp_tbl.Requested_Date_Quantity.Extend;
105 p_atp_tbl.Group_Ship_Date.Extend;
106 p_atp_tbl.Group_Arrival_Date.Extend;
107 p_atp_tbl.Vendor_Id.Extend;
108 p_atp_tbl.Vendor_Name.Extend;
109 p_atp_tbl.Vendor_Site_Id.Extend;
110 p_atp_tbl.Vendor_Site_Name.Extend;
111 p_atp_tbl.Insert_Flag.Extend;
112 p_atp_tbl.OE_Flag.Extend;
113 p_atp_tbl.Error_Code.Extend;
114 --p_atp_tbl.Atp_Lead_Time.Extend;
115 p_atp_tbl.Message.Extend;
116
117 END Extend_ATP;
118
119
120 PROCEDURE Populate_Output_Table( p_atp_rec IN MRP_ATP_PUB.ATP_REC_TYP,
121 x_aso_atp_tbl OUT NOCOPY ASO_ATP_INT.ATP_TBL_TYP,
122 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
123 AS
124
125 l_index NUMBER;
126
127 cursor c_ship_from_org_name(p_ship_from_org_id number) is
128 select name
129 from oe_ship_from_orgs_v
130 where organization_id = p_ship_from_org_id;
131
132 cursor c_inv_item_desc(p_inv_item_id number, p_organization_id number) is
133 select padded_concatenated_segments, description
134 from mtl_system_items_kfv
135 where inventory_item_id = p_inv_item_id
136 and organization_id = p_organization_id;
137
138 cursor c_uom_meaning(p_inv_item_id number, p_organization_id number, p_uom_code varchar2) is
139 select unit_of_measure
140 from mtl_item_uoms_view
141 where inventory_item_id = p_inv_item_id
142 and organization_id = p_organization_id
143 and uom_code = p_uom_code;
144
145 cursor c_meaning(p_lookup_type varchar2, p_view_application_id number, p_lookup_code varchar2) is
146 select meaning
147 from fnd_lookup_values
148 where lookup_type = p_lookup_type
149 and view_application_id = p_view_application_id
150 and lookup_code = p_lookup_code
151 and enabled_flag = 'Y'
152 and language = USERENV('LANG')
153 and trunc(nvl(start_date_active,sysdate)) <= trunc(sysdate)
154 and trunc(nvl(end_date_active,sysdate)) >= trunc(sysdate);
155
156 cursor c_qty_on_hand(p_inv_item_id number, p_organization_id number) is
157 select total_qoh
158 from mtl_onhand_items_v
159 where organization_id = p_organization_id
160 and inventory_item_id = p_inv_item_id;
161
162 cursor c_request_date_type(p_quote_header_id number) is
163 select nvl(request_date_type,'SHIP'),shipment_id
164 from aso_shipments
165 where quote_header_id = p_quote_header_id;
166
167 cursor c_request_date_type_meaning(p_lookup_code varchar2) is
168 select l.meaning
169 from oe_lookups l
170 where l.lookup_type = 'REQUEST_DATE_TYPE' and
171 l.enabled_flag = 'Y'
172 and trunc(sysdate) between nvl(start_date_active,trunc(sysdate))
173 and nvl(end_date_active,trunc(sysdate))
174 and l.lookup_code = p_lookup_code;
175
176 cursor c_error_desc(p_error_code varchar2) is
177 select meaning
178 from mfg_lookups
179 where lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
180 and lookup_code = p_error_code;
181
182 cursor c_get_shipment_id (p_qte_header_id number, p_qte_line_id number) is
183 select shipment_id
184 from aso_shipments
185 where quote_header_id = p_qte_header_id
186 and quote_line_id = p_qte_line_id;
187
188 BEGIN
189 x_return_status := FND_API.G_RET_STS_SUCCESS;
190
191 If p_atp_rec.inventory_item_id IS NOT NULL Then
192
193 if aso_debug_pub.g_debug_flag = 'Y' then
194 aso_debug_pub.add('Populate_Output_Table: inside if for p_atp_rec.inventory_item_id',1,'N');
195 end if;
196
197 For i in p_atp_rec.inventory_item_id.FIRST .. p_atp_rec.inventory_item_id.LAST Loop
198
199 if aso_debug_pub.g_debug_flag = 'Y' then
200 aso_debug_pub.add('Populate_Output_Table: inside loop for p_atp_rec.inventory_item_id',1,'N');
201 end if;
202
203 if p_atp_rec.inventory_item_id.EXISTS(i) then
204
205 x_aso_atp_tbl(i).inventory_item_id := p_atp_rec.inventory_item_id(i);
206 x_aso_atp_tbl(i).inventory_item_name := p_atp_rec.inventory_item_name(i);
207 x_aso_atp_tbl(i).source_organization_id := p_atp_rec.source_organization_id(i);
208 x_aso_atp_tbl(i).source_organization_code := p_atp_rec.source_organization_code(i);
209 x_aso_atp_tbl(i).identifier := p_atp_rec.identifier(i);
210 x_aso_atp_tbl(i).customer_id := p_atp_rec.customer_id(i);
211 x_aso_atp_tbl(i).customer_site_id := p_atp_rec.customer_site_id(i);
212 x_aso_atp_tbl(i).Quantity_Ordered := p_atp_rec.Quantity_Ordered(i);
213 x_aso_atp_tbl(i).Quantity_UOM := p_atp_rec.Quantity_UOM(i);
214 x_aso_atp_tbl(i).Requested_Ship_Date := p_atp_rec.Requested_Ship_Date(i);
215 x_aso_atp_tbl(i).Ship_Date := p_atp_rec.Ship_Date(i);
216 x_aso_atp_tbl(i).Available_Quantity := p_atp_rec.Available_Quantity(i);
217 x_aso_atp_tbl(i).Request_Date_Quantity := p_atp_rec.Requested_Date_Quantity(i);
218 x_aso_atp_tbl(i).Error_Code := p_atp_rec.Error_Code(i);
219 x_aso_atp_tbl(i).Message := p_atp_rec.Message(i);
220 --x_aso_atp_tbl(i).request_date_type := p_atp_rec.request_date_type(i);
221 x_aso_atp_tbl(i).demand_class_code := p_atp_rec.demand_class(i);
222 x_aso_atp_tbl(i).ship_set_name := p_atp_rec.ship_set_name(i);
223 x_aso_atp_tbl(i).arrival_set_name := p_atp_rec.arrival_set_name(i);
224 x_aso_atp_tbl(i).line_number := p_atp_rec.line_number(i);
225 x_aso_atp_tbl(i).group_ship_date := p_atp_rec.group_ship_date(i);
226 x_aso_atp_tbl(i).requested_arrival_date := p_atp_rec.requested_arrival_date(i);
227 x_aso_atp_tbl(i).ship_method_code := p_atp_rec.ship_method(i);
228 --x_aso_atp_tbl(i).quantity_on_hand := p_atp_rec.quantity_on_hand(i);
229 x_aso_atp_tbl(i).quote_header_id := p_atp_rec.demand_source_header_id(i);
230 x_aso_atp_tbl(i).calling_module := p_atp_rec.calling_module(i);
231 x_aso_atp_tbl(i).quote_number := p_atp_rec.order_number(i);
232 x_aso_atp_tbl(i).ato_line_id := p_atp_rec.ato_model_line_id(i);
233 x_aso_atp_tbl(i).ref_line_id := p_atp_rec.parent_line_id(i);
234 x_aso_atp_tbl(i).top_model_line_id := p_atp_rec.top_model_line_id(i);
235 x_aso_atp_tbl(i).action := p_atp_rec.action(i);
236 x_aso_atp_tbl(i).arrival_date := p_atp_rec.arrival_date(i);
237 x_aso_atp_tbl(i).organization_id := p_atp_rec.validation_org(i);
238 x_aso_atp_tbl(i).component_code := p_atp_rec.component_code(i);
239 x_aso_atp_tbl(i).component_sequence_id := p_atp_rec.component_sequence_id(i);
240 x_aso_atp_tbl(i).included_item_flag := p_atp_rec.included_item_flag(i);
241 x_aso_atp_tbl(i).cascade_model_info_to_comp := p_atp_rec.cascade_model_info_to_comp(i);
242 --x_aso_atp_tbl(i).ship_to_party_site_id := p_atp_rec.ship_to_party_site_id(i);
243 x_aso_atp_tbl(i).country := p_atp_rec.customer_country(i);
244 x_aso_atp_tbl(i).state := p_atp_rec.customer_state(i);
245 x_aso_atp_tbl(i).city := p_atp_rec.customer_city(i);
246 x_aso_atp_tbl(i).postal_code := p_atp_rec.customer_postal_code(i);
247 x_aso_atp_tbl(i).match_item_id := p_atp_rec.match_item_id(i);
248
249 open c_ship_from_org_name(p_atp_rec.source_organization_id(i));
250 fetch c_ship_from_org_name into x_aso_atp_tbl(i).source_organization_name;
251 close c_ship_from_org_name;
252
253 open c_inv_item_desc(p_atp_rec.inventory_item_id(i), p_atp_rec.validation_org(i));
254 fetch c_inv_item_desc into x_aso_atp_tbl(i).padded_concatenated_segments, x_aso_atp_tbl(i).inventory_item_description;
255 close c_inv_item_desc;
256
257 open c_uom_meaning(p_atp_rec.inventory_item_id(i), p_atp_rec.validation_org(i), p_atp_rec.quantity_uom(i));
258 fetch c_uom_meaning into x_aso_atp_tbl(i).uom_meaning;
259 close c_uom_meaning;
260
261 open c_meaning('SHIP_METHOD', 3, p_atp_rec.ship_method(i));
262 fetch c_meaning into x_aso_atp_tbl(i).ship_method_meaning;
263 close c_meaning;
264
265 open c_meaning('DEMAND_CLASS', 3, p_atp_rec.demand_class(i));
266 fetch c_meaning into x_aso_atp_tbl(i).demand_class_meaning;
267 close c_meaning;
268
269 -- always getting the header shipment request date type as line shipment request date type is not supported
270 open c_request_date_type(p_atp_rec.demand_source_header_id(i));
271 fetch c_request_date_type into x_aso_atp_tbl(i).request_date_type,x_aso_atp_tbl(i).shipment_id;
272 close c_request_date_type;
273
274 IF x_aso_atp_tbl(i).request_date_type IS NOT NULL THEN
275 open c_request_date_type_meaning(x_aso_atp_tbl(i).request_date_type);
276 fetch c_request_date_type_meaning into x_aso_atp_tbl(i).request_date_type_meaning;
277 close c_request_date_type_meaning;
278 END IF;
279
280 open c_error_desc(p_atp_rec.error_code(i));
281 fetch c_error_desc into x_aso_atp_tbl(i).error_description;
282 close c_error_desc;
283
284 if p_atp_rec.ato_model_line_id(i) is not null and p_atp_rec.ato_model_line_id(i) = p_atp_rec.identifier(i) then
285
286 open c_qty_on_hand(p_atp_rec.match_item_id(i), p_atp_rec.validation_org(i));
287 fetch c_qty_on_hand into x_aso_atp_tbl(i).quantity_on_hand;
288 close c_qty_on_hand;
289
290 if aso_debug_pub.g_debug_flag = 'Y' then
291 aso_debug_pub.add('p_atp_rec.match_item_id: ' || p_atp_rec.match_item_id(i),1,'N');
292 aso_debug_pub.add('x_aso_atp_tbl(i).quantity_on_hand: ' || x_aso_atp_tbl(i).quantity_on_hand,1,'N');
293 end if;
294
295 elsif nvl(p_atp_rec.top_model_line_id(i), 0) <> p_atp_rec.identifier(i) and p_atp_rec.ato_model_line_id(i) is null then
296
297 open c_qty_on_hand(p_atp_rec.inventory_item_id(i), p_atp_rec.validation_org(i));
298 fetch c_qty_on_hand into x_aso_atp_tbl(i).quantity_on_hand;
299 close c_qty_on_hand;
300
301 if aso_debug_pub.g_debug_flag = 'Y' then
302 aso_debug_pub.add('x_aso_atp_tbl(i).quantity_on_hand: ' || x_aso_atp_tbl(i).quantity_on_hand,1,'N');
303 end if;
304
305 end if;
306
307 open c_get_shipment_id(p_atp_rec.demand_source_header_id(i), p_atp_rec.identifier(i));
308 fetch c_get_shipment_id into x_aso_atp_tbl(i).shipment_id;
309 close c_get_shipment_id;
310
311 if aso_debug_pub.g_debug_flag = 'Y' then
312 aso_debug_pub.add('p_atp_rec.inventory_item_id: ' || p_atp_rec.inventory_item_id(i),1,'N');
313 aso_debug_pub.add('p_atp_rec.inventory_item_name: ' || p_atp_rec.inventory_item_name(i),1,'N');
314 aso_debug_pub.add('p_atp_rec.source_organization_code: ' || p_atp_rec.source_organization_code(i),1,'N');
315 aso_debug_pub.add('p_atp_rec.source_organization_id: ' || p_atp_rec.source_organization_id(i),1,'N');
316 aso_debug_pub.add('p_atp_rec.identifier: ' || p_atp_rec.identifier(i),1,'N');
317 aso_debug_pub.add('p_atp_rec.customer_id: ' || p_atp_rec.customer_id(i),1,'N');
318 aso_debug_pub.add('p_atp_rec.customer_site_id: ' || p_atp_rec.customer_site_id(i),1,'N');
319 aso_debug_pub.add('p_atp_rec.Quantity_Ordered: ' || p_atp_rec.Quantity_Ordered(i),1,'N');
320 aso_debug_pub.add('p_atp_rec.Quantity_UOM: ' || p_atp_rec.Quantity_UOM(i),1,'N');
321 aso_debug_pub.add('p_atp_rec.Requested_Ship_Date: ' || p_atp_rec.Requested_Ship_Date(i),1,'N');
322 aso_debug_pub.add('p_atp_rec.Ship_Date: ' || p_atp_rec.Ship_Date(i),1,'N');
326 aso_debug_pub.add('p_atp_rec.Message: ' || p_atp_rec.Message(i),1,'N');
323 aso_debug_pub.add('p_atp_rec.Available_Quantity: ' || p_atp_rec.Available_Quantity(i),1,'N');
324 aso_debug_pub.add('p_atp_rec.Requested_Date_Quantity: ' || p_atp_rec.Requested_Date_Quantity(i),1,'N');
325 aso_debug_pub.add('p_atp_rec.Error_Code: ' || p_atp_rec.Error_Code(i),1,'N');
327 --aso_debug_pub.add('p_atp_rec.request_date_type: ' || p_atp_rec.request_date_type(i),1,'N');
328 aso_debug_pub.add('p_atp_rec.demand_class: ' || p_atp_rec.demand_class(i),1,'N');
329 aso_debug_pub.add('p_atp_rec.ship_set_name: ' || p_atp_rec.ship_set_name(i),1,'N');
330 aso_debug_pub.add('p_atp_rec.arrival_set_name: ' || p_atp_rec.arrival_set_name(i),1,'N');
331 aso_debug_pub.add('p_atp_rec.line_number: ' || p_atp_rec.line_number(i),1,'N');
332 aso_debug_pub.add('p_atp_rec.group_ship_date: ' || p_atp_rec.group_ship_date(i),1,'N');
333 aso_debug_pub.add('p_atp_rec.requested_arrival_date: ' || p_atp_rec.requested_arrival_date(i),1,'N');
334 aso_debug_pub.add('p_atp_rec.ship_method: ' || p_atp_rec.ship_method(i),1,'N');
335 aso_debug_pub.add('p_atp_rec.demand_source_header_id: ' || p_atp_rec.demand_source_header_id(i),1,'N');
336 aso_debug_pub.add('p_atp_rec.calling_module: ' || p_atp_rec.calling_module(i),1,'N');
337 aso_debug_pub.add('p_atp_rec.order_number: ' || p_atp_rec.order_number(i),1,'N');
338 aso_debug_pub.add('p_atp_rec.ato_model_line_id: ' || p_atp_rec.ato_model_line_id(i),1,'N');
339 aso_debug_pub.add('p_atp_rec.parent_line_id: ' || p_atp_rec.parent_line_id(i),1,'N');
340 aso_debug_pub.add('p_atp_rec.top_model_line_id: ' || p_atp_rec.top_model_line_id(i),1,'N');
341 aso_debug_pub.add('p_atp_rec.match_item_id: ' || p_atp_rec.match_item_id(i),1,'N');
342 aso_debug_pub.add('p_atp_rec.action: ' || p_atp_rec.action(i),1,'N');
343 aso_debug_pub.add('p_atp_rec.arrival_date: ' || p_atp_rec.arrival_date(i),1,'N');
344 aso_debug_pub.add('p_atp_rec.validation_org: ' || p_atp_rec.validation_org(i),1,'N');
345 aso_debug_pub.add('p_atp_rec.component_code: ' || p_atp_rec.component_code(i),1,'N');
346 aso_debug_pub.add('p_atp_rec.component_sequence_id: ' || p_atp_rec.component_sequence_id(i),1,'N');
347 aso_debug_pub.add('p_atp_rec.included_item_flag: ' || p_atp_rec.included_item_flag(i),1,'N');
348 aso_debug_pub.add('p_atp_rec.cascade_model_info_to_comp: ' || p_atp_rec.cascade_model_info_to_comp(i),1,'N');
349 --aso_debug_pub.add('p_atp_rec.ship_to_party_site_id: ' || p_atp_rec.ship_to_party_site_id(i),1,'N');
350 aso_debug_pub.add('p_atp_rec.customer_country: ' || p_atp_rec.customer_country(i),1,'N');
351 aso_debug_pub.add('p_atp_rec.customer_state: ' || p_atp_rec.customer_state(i),1,'N');
352 aso_debug_pub.add('p_atp_rec.customer_city: ' || p_atp_rec.customer_city(i),1,'N');
353 aso_debug_pub.add('p_atp_rec.customer_postal_code: ' || p_atp_rec.customer_postal_code(i),1,'N');
354 end if;
355
356 end if;
357
358 End Loop;
359
360 if aso_debug_pub.g_debug_flag = 'Y' then
361
362 for i in 1 .. x_aso_atp_tbl.count loop
363
364 aso_debug_pub.add('x_aso_atp_tbl('||i||').source_organization_name: '|| x_aso_atp_tbl(i).source_organization_name, 1, 'N');
365 aso_debug_pub.add('x_aso_atp_tbl('||i||').padded_concatenated_segments: '|| x_aso_atp_tbl(i).padded_concatenated_segments, 1, 'N');
366 aso_debug_pub.add('x_aso_atp_tbl('||i||').inventory_item_description: '|| x_aso_atp_tbl(i).inventory_item_description, 1, 'N');
367 aso_debug_pub.add('x_aso_atp_tbl('||i||').uom_meaning: '|| x_aso_atp_tbl(i).uom_meaning, 1, 'N');
368 aso_debug_pub.add('x_aso_atp_tbl('||i||').quantity_on_hand: '|| x_aso_atp_tbl(i).quantity_on_hand, 1, 'N');
369 aso_debug_pub.add('x_aso_atp_tbl('||i||').ship_method_meaning: '|| x_aso_atp_tbl(i).ship_method_meaning, 1, 'N');
370 aso_debug_pub.add('x_aso_atp_tbl('||i||').demand_class_meaning: '|| x_aso_atp_tbl(i).demand_class_meaning, 1, 'N');
371 aso_debug_pub.add('x_aso_atp_tbl('||i||').organization_id: '|| x_aso_atp_tbl(i).organization_id, 1, 'N');
372 aso_debug_pub.add('x_aso_atp_tbl('||i||').shipment_id: '|| x_aso_atp_tbl(i).shipment_id, 1, 'N');
373 end loop;
374
375 end if;
376
377 End If;
378
379 END Populate_Output_Table;
380
381
382
383 PROCEDURE Do_Check_ATP(
384 P_Api_Version_Number IN NUMBER,
385 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
386 p_qte_header_rec IN ASO_QUOTE_PUB.QTE_HEADER_REC_TYPE,
387 p_qte_line_tbl IN ASO_QUOTE_PUB.qte_line_tbl_type := ASO_QUOTE_PUB.G_MISS_QTE_LINE_TBL,
388 p_shipment_tbl IN ASO_QUOTE_PUB.shipment_tbl_type := ASO_QUOTE_PUB.G_MISS_SHIPMENT_TBL,
389 p_entire_quote_flag IN VARCHAR2 :='N',
390 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
391 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
392 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
393 X_atp_tbl OUT NOCOPY /* file.sql.39 change */ aso_atp_int.atp_tbl_typ)
394 AS
395
396 l_api_name CONSTANT VARCHAR2(30) := 'Do_Check_ATP' ;
397 l_api_version_number CONSTANT NUMBER := 1.0;
398
399 l_session_id number;
400 l_sysdate date;
401 l_atp_rec mrp_atp_pub.atp_rec_typ;
402 l_atp_rec_out mrp_atp_pub.atp_rec_typ;
403 l_atp_supply_demand mrp_atp_pub.atp_supply_demand_typ;
404 l_atp_period mrp_atp_pub.atp_period_typ;
405 l_atp_details mrp_atp_pub.atp_details_typ;
406 l_null_aso_atp_typ aso_atp_int.atp_rec_typ;
407 l_mrp_database_link Varchar2(128);
408 l_statement Varchar2(500);
409 l_ship_from_org_id Number ;
410 l_profile_name Varchar2(240);
411 l_customer_id NUMBER;
412 l_cust_ship_site_id NUMBER;
413 l_mrp_customer_id NUMBER;
414 l_mrp_ship_site_id NUMBER;
415 l_use_sourcing_rule VARCHAR2(10);
416 l_file VARCHAR2(200);
417
418 l_qte_line_tbl aso_quote_pub.qte_line_tbl_type := p_qte_line_tbl;
419 l_shipment_tbl aso_quote_pub.shipment_tbl_type := p_shipment_tbl;
420 l_qte_line_rec aso_quote_pub.qte_line_rec_type;
421 l_shipment_rec aso_quote_pub.shipment_rec_type;
422 --l_aso_atp_tbl aso_atp_int.atp_tbl_typ;
423
424 cursor c_description(p_inventory_item_id number,p_organization_id number) is
425 -- this cursor has been modified to select segment1 instead of description, see bug 3604265
426 select segment1
427 from mtl_system_items_vl
428 where inventory_item_id = p_inventory_item_id
429 and organization_id = p_organization_id;
430
431 cursor c_config_dtl(p_qte_line_id number) is
432 select a.quote_line_id, b.item_type_code, a.config_header_id, a.config_revision_num,a.component_code,
433 a.config_item_id, a.ref_line_id, a.top_model_line_id, a.ato_line_id, a.component_sequence_id,
434 b.ship_model_complete_flag
435 from aso_quote_line_details a, aso_quote_lines_all b
436 where b.quote_line_id = p_qte_line_id
437 and b.quote_line_id = a.quote_line_id
438 and a.ref_type_code = 'CONFIG';
439
440 cursor c_quote_line_id(p_config_header_id number, p_config_revision_num number, p_config_item_id number) is
441 select quote_line_id
442 from aso_quote_line_details
443 where config_header_id = p_config_header_id
444 and config_revision_num = p_config_revision_num
445 and config_item_id = p_config_item_id;
446
447 cursor c_smc(p_quote_line_id number) is
448 select ship_model_complete_flag
449 from aso_quote_lines_all
450 where quote_line_id = p_quote_line_id;
451
452 cursor c_quote_number(p_quote_header_id number) is
453 select quote_number, cust_account_id
454 from aso_quote_headers_all
455 where quote_header_id = p_quote_header_id;
456
457 cursor c_configuration_rows(p_config_header_id number, p_config_revision_num number) is
458 select a.quote_line_id, b.shipment_id
459 from aso_quote_line_details a, aso_shipments b
460 where a.quote_line_id = b.quote_line_id
461 and a.config_header_id = p_config_header_id
462 and a.config_revision_num = p_config_revision_num
463 order by a.bom_sort_order;
464
465 cursor c_ato_rows(p_config_header_id number, p_config_revision_num number, p_ato_line_id number) is
466 select a.quote_line_id, b.shipment_id
467 from aso_quote_line_details a, aso_shipments b
468 where a.quote_line_id = b.quote_line_id
469 and a.config_header_id = p_config_header_id
470 and a.config_revision_num = p_config_revision_num
471 and a.ato_line_id = p_ato_line_id
472 order by a.bom_sort_order;
473
474 cursor c_top_model_line_id(p_config_header_id number, p_config_revision_num number) is
475 select quote_line_id from aso_quote_line_details
476 where config_header_id = p_config_header_id
477 and config_revision_num = p_config_revision_num
478 and ref_line_id is null
479 and ref_type_code = 'CONFIG';
480
481 cursor c_shipment_id(p_quote_line_id number) is
482 select shipment_id from aso_shipments
483 where quote_line_id = p_quote_line_id;
484
485 cursor c_request_date_type(p_shipment_id number) is
486 select request_date_type from aso_shipments
487 where shipment_id = p_shipment_id;
488
489 -- filtering out the service items and the component models
490 -- before passing the records to ATP
491 cursor get_ordered_lines (p_qte_header_id number) is
492 select quote_line_id
493 from aso_pvt_quote_lines_bali_v
494 where quote_header_id = p_qte_header_id
495 and nvl(service_item_flag,'N') = 'N'
496 and instance_id is null
497 and nvl(config_model_type,'X') <> 'N' ;
498
499 cursor get_no_of_lines(p_qte_header_id number) is
500 select count(quote_line_id)
501 from aso_quote_lines_all
502 where quote_header_id = p_qte_header_id;
503
504 cursor c_get_warehouse (p_qte_line_id number, p_qte_header_id number ) is
505 select ship_from_org_id
506 from aso_shipments
507 where quote_line_id = p_qte_line_id
508 and quote_header_id = p_qte_header_id;
509
510 cursor c_get_ids (p_qte_line_id number) is
511 select ato_line_id,top_model_line_id
512 from aso_quote_line_details
513 where quote_line_id = p_qte_line_id;
514
515 cursor c_get_request_date (p_qte_line_id number, p_qte_header_id number,p_date_type varchar2 ) is
516 select request_date
517 from aso_shipments
518 where quote_line_id = p_qte_line_id
519 and quote_header_id = p_qte_header_id
520 and nvl(request_date_type,'SHIP') = p_date_type;
521
522 cursor c_get_ship_method (p_qte_line_id number, p_qte_header_id number ) is
523 select ship_method_code
524 from aso_shipments
525 where quote_line_id = p_qte_line_id
526 and quote_header_id = p_qte_header_id;
527
528 cursor c_get_demand_code (p_qte_line_id number, p_qte_header_id number ) is
529 select demand_class_code
530 from aso_shipments
531 where quote_line_id = p_qte_line_id
532 and quote_header_id = p_qte_header_id;
533
534
535 l_ship_model_complete_flag varchar2(1);
536 l_quote_line_id number;
537 l_model_quote_line_id number;
538 l_ato_quote_line_id number;
539 l_index number := 0;
540 l_top_model_line_id number;
541 -- bug 3604265
542 l_segment1 varchar2(40);
543
544 TYPE Varchar2_Search_Tbl_Type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
545 TYPE Number_Search_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
546
547 l_smc_search_tbl Varchar2_Search_Tbl_Type;
548 l_qte_line_search_tbl Number_Search_Tbl_Type;
549 l_shipment_search_tbl Number_Search_Tbl_Type;
550 l_config_hdr_search_tbl Number_Search_Tbl_Type;
551 l_ato_tbl Number_Search_Tbl_Type;
552 l_ato_line_id_tbl Number_Search_Tbl_Type;
553 l_db_shipment_rec aso_quote_pub.shipment_rec_type;
554 l_db_qte_line_rec aso_quote_pub.qte_line_rec_type;
555 l_check_atp_for_whole_quote varchar2(1) := fnd_api.g_false;
556 l_ship_to_party_site_id number;
557 l_ship_to_cust_account_id number;
558 lx_cust_acct_site_use_id number;
559 l_out_qte_line_number_tbl aso_line_num_int.Out_Line_Number_Tbl_Type;
560 l_in_qte_line_number_tbl aso_line_num_int.In_Line_Number_Tbl_Type;
561
562 l_search_tbl Number_Search_Tbl_Type;
563 l_qte_line_id_from_bali number;
564 l_no_of_lines number;
565 l_new_qte_line_tbl aso_quote_pub.qte_line_tbl_type ;
566 l_new_shipment_tbl aso_quote_pub.shipment_tbl_type ;
567
568 l_cascade_ship_from_org_id number;
569 l_cascade_request_date date;
570 l_cascade_ship_method_code varchar2(30);
571 l_cascade_demand_class_code varchar2(30);
572 l_ato_line_id number;
573 l_model_line_id number;
574 l_shipment_id number;
575 x_new_msg_data varchar2(3000);
576 m integer;
577 l_hdr_shipment_rec aso_quote_pub.shipment_rec_type;
578 l_hdr_shipment_tbl aso_quote_pub.shipment_tbl_type ;
579 BEGIN
580 -- Standard Start of API savepoint
581 SAVEPOINT DO_CHECK_ATP_INT;
582
583 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
584
585 IF aso_debug_pub.g_debug_flag = 'Y' THEN
586 aso_debug_pub.add('do_check_atp: Begin');
587 END IF;
588
589 -- Standard call to check for call compatibility.
590 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
591 p_api_version_number,
592 l_api_name,
593 G_PKG_NAME) THEN
594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
595 END IF;
596
597 -- Initialize message list if p_init_msg_list is set to TRUE.
598 IF FND_API.to_Boolean( p_init_msg_list ) THEN
599 FND_MSG_PUB.initialize;
600 END IF;
601
602 x_return_status := fnd_api.g_ret_sts_success;
603
604 if aso_debug_pub.g_debug_flag = 'Y' then
605 aso_debug_pub.add('do_check_atp: ********Printing the Input to Check ATP API *************', 1, 'Y');
606 aso_debug_pub.add('do_check_atp: p_qte_header_rec.quote_header_id: '|| p_qte_header_rec.quote_header_id, 1, 'Y');
610 aso_debug_pub.add('do_check_atp: p_qte_line_tbl('||i||').quote_line_id: '|| p_qte_line_tbl(i).quote_line_id, 1, 'Y');
607 aso_debug_pub.add('do_check_atp: p_entire_quote_flag: '|| p_entire_quote_flag, 1, 'Y');
608 aso_debug_pub.add('do_check_atp: p_qte_line_tbl.count: '|| p_qte_line_tbl.count, 1, 'Y');
609 for i in 1..p_qte_line_tbl.count loop
611 aso_debug_pub.add('do_check_atp: p_qte_line_tbl('||i||').quote_header_id: '|| p_qte_line_tbl(i).quote_header_id, 1, 'Y');
612 end loop;
613 aso_debug_pub.add('do_check_atp: p_shipment_tbl.count: '|| p_shipment_tbl.count, 1, 'Y');
614 for i in 1..p_shipment_tbl.count loop
615 aso_debug_pub.add('do_check_atp: p_shipment_tbl('||i||').ship_method_code: '|| p_shipment_tbl(i).ship_method_code, 1, 'Y');
616 aso_debug_pub.add('do_check_atp: p_shipment_tbl('||i||').ship_from_org_id: '|| p_shipment_tbl(i).ship_from_org_id, 1, 'Y');
617 aso_debug_pub.add('do_check_atp: p_shipment_tbl('||i||').demand_class_code: '|| p_shipment_tbl(i).demand_class_code, 1, 'Y');
618 aso_debug_pub.add('do_check_atp: p_shipment_tbl('||i||').request_date: '|| p_shipment_tbl(i).request_date, 1, 'Y');
619 aso_debug_pub.add('do_check_atp: p_shipment_tbl('||i||').shipment_id: '|| p_shipment_tbl(i).shipment_id, 1, 'Y');
620 aso_debug_pub.add('do_check_atp: p_shipment_tbl('||i||').quote_line_id: '|| p_shipment_tbl(i).quote_line_id, 1, 'Y');
621 end loop;
622 aso_debug_pub.add('do_check_atp: **************************************************************', 1, 'Y');
623 end if;
624
625
626
627 if p_qte_line_tbl.count = 0 and p_shipment_tbl.count = 0 then
628
629 if p_qte_header_rec.quote_header_id is null or p_qte_header_rec.quote_header_id = fnd_api.g_miss_num then
630
631 if aso_debug_pub.g_debug_flag = 'Y' THEN
632 aso_debug_pub.add('do_check_atp: p_qte_line_tbl and p_shipment_tbl is null. Also p_qte_header_rec.quote_header_id is null');
633 end if;
634
635 if fnd_msg_pub.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
636 FND_MESSAGE.Set_Name('ASO', 'ASO_API_ALL_MISSING_PARAM');
637 FND_MESSAGE.Set_Token('API_NAME', 'Check_ATP', FALSE);
638 FND_MESSAGE.Set_Token('PARAMETER', 'p_qte_header_rec', FALSE);
639 FND_MSG_PUB.ADD;
640 end if;
641 x_return_status := fnd_api.g_ret_sts_error;
642 raise fnd_api.g_exc_error;
643
644 else
645 /*
646 l_qte_line_tbl := aso_utility_pvt.query_qte_line_rows_atp(p_qte_header_rec.quote_header_id);
647 for i in 1..l_qte_line_tbl.count loop
648 l_shipment_rec := aso_utility_pvt.query_line_shipment_row_atp(p_qte_header_rec.quote_header_id,
649 l_qte_line_tbl(i).quote_line_id);
650 l_shipment_tbl(l_shipment_tbl.count + 1 ) := l_shipment_rec;
651 end loop; */
652 --l_shipment_tbl := aso_utility_pvt.query_line_shipment_rows_atp(p_qte_header_rec.quote_header_id);
653 l_check_atp_for_whole_quote := fnd_api.g_true;
654 end if;
655
656 end if;
657
658 if p_qte_line_tbl.count >= 1 then
659
660 open get_no_of_lines(p_qte_header_rec.quote_header_id);
661 fetch get_no_of_lines into l_no_of_lines;
662 close get_no_of_lines;
663
664 if p_qte_line_tbl.count = l_no_of_lines then
665
666 if aso_debug_pub.g_debug_flag = 'Y' then
667 aso_debug_pub.add('do_check_atp: No of lines passed is equal to no lines in db, hence whole qte is true', 1, 'Y');
668 end if;
669 l_check_atp_for_whole_quote := fnd_api.g_true;
670 end if;
671
672 end if;
673
674 if ((p_entire_quote_flag = 'Y') or (l_check_atp_for_whole_quote = fnd_api.g_true)) then
675
676 if aso_debug_pub.g_debug_flag = 'Y' then
677 aso_debug_pub.add('do_check_atp: Getting the quote lines and shipment from db', 1, 'Y');
678 end if;
679 l_qte_line_tbl := aso_utility_pvt.query_qte_line_rows_atp(p_qte_header_rec.quote_header_id);
680 for i in 1..l_qte_line_tbl.count loop
681 l_shipment_rec := aso_utility_pvt.query_line_shipment_row_atp(p_qte_header_rec.quote_header_id,
682 l_qte_line_tbl(i).quote_line_id);
683 l_shipment_tbl(i) := l_shipment_rec;
684 end loop;
685 l_check_atp_for_whole_quote := fnd_api.g_true;
686 end if;
687
688 if aso_debug_pub.g_debug_flag = 'Y' then
689 aso_debug_pub.add('do_check_atp: Before creating the search tables', 1, 'Y');
690 end if;
691
692
693 --create quote line search table
694 for i in 1..p_qte_line_tbl.count loop
695
696 if p_qte_line_tbl(i).quote_line_id is not null and p_qte_line_tbl(i).quote_line_id <> fnd_api.g_miss_num then
697
698 if aso_debug_pub.g_debug_flag = 'Y' then
699 aso_debug_pub.add('do_check_atp: p_qte_line_tbl(i).quote_line_id: ' || p_qte_line_tbl(i).quote_line_id,1,'Y');
700 end if;
701
702 l_qte_line_search_tbl(p_qte_line_tbl(i).quote_line_id) := i;
703
704 else
705 if aso_debug_pub.g_debug_flag = 'Y' then
706 aso_debug_pub.add('do_check_atp: p_qte_line_tbl(i).quote_line_id: ' || p_qte_line_tbl(i).quote_line_id,1,'Y');
707 aso_debug_pub.add('do_check_atp: Quote_line_id is passed as nulll or g_miss_num in line record' ,1,'Y');
708 end if;
709
710 if fnd_msg_pub.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
711 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_INFO');
712 FND_MESSAGE.Set_Token('COLUMN', 'Quote_Line_Id', FALSE);
713 FND_MSG_PUB.ADD;
714 end if;
715 x_return_status := fnd_api.g_ret_sts_error;
716 raise fnd_api.g_exc_error;
717
718 end if;
719
720 end loop;
721
722 --create shipment search table
723 --Assumption is p_qte_line_tbl and p_shipment_tbl are one-to-one mapping
724 --For each quote line in p_qte_line_tbl there exist a shipment record in p_shipment_tbl
725
726 for i in 1..p_shipment_tbl.count loop
727
728 if p_shipment_tbl(i).shipment_id is not null and p_shipment_tbl(i).shipment_id <> fnd_api.g_miss_num then
729
730 if aso_debug_pub.g_debug_flag = 'Y' then
731 aso_debug_pub.add('do_check_atp: p_shipment_tbl(i).shipment_id: ' || p_shipment_tbl(i).shipment_id,1,'Y');
732 end if;
733
734 l_shipment_search_tbl(p_shipment_tbl(i).shipment_id) := i;
735
736 else
737
738 if aso_debug_pub.g_debug_flag = 'Y' then
739 aso_debug_pub.add('do_check_atp: p_shipment_tbl(i).shipment_id: ' || p_shipment_tbl(i).shipment_id,1,'Y');
740 aso_debug_pub.add('do_check_atp: shipment_id is passed as nulll or g_miss_num in shipment record' ,1,'Y');
741 end if;
742
743 open c_shipment_id(p_qte_line_tbl(i).quote_line_id);
744 fetch c_shipment_id into l_shipment_tbl(i).shipment_id;
745 close c_shipment_id;
746
747 l_shipment_search_tbl(l_shipment_tbl(i).shipment_id) := i;
748 /*
749 if fnd_msg_pub.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
750 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_INFO');
751 FND_MESSAGE.Set_Token('COLUMN', 'Shipment_Id', FALSE);
752 FND_MSG_PUB.ADD;
753 end if;
754 x_return_status := fnd_api.g_ret_sts_error;
755 raise fnd_api.g_exc_error;
756 */
757
758 end if;
759
760 end loop;
761
762 if aso_debug_pub.g_debug_flag = 'Y' then
763 aso_debug_pub.add('do_check_atp: After Creating the search tables ' ,1,'Y');
764 aso_debug_pub.add('do_check_atp: l_shipment_search_tbl.count : '||l_shipment_search_tbl.count ,1,'Y');
765 aso_debug_pub.add('do_check_atp: l_qte_line_search_tbl.count : '||l_qte_line_search_tbl.count ,1,'Y');
766 end if;
767
768 -- now honor the values that have been passed
769 /* for i in 1..p_qte_line_tbl.count loop
770 for k in 1..l_qte_line_tbl.count loop
771 if l_qte_line_tbl(k).quote_line_id = p_qte_line_tbl(i).quote_line_id then
772 l_qte_line_tbl(k) := p_qte_line_tbl(i);
773 end if;
774 end loop;
775 end loop; */
776
777 -- We need to honor values passed from UI in shipment tbl
778 -- hence, values in l_shipment_tbl need to be overwritten if they are passed
779
780 if p_shipment_tbl.count > 0 then
781
782 for i in 1..p_shipment_tbl.count loop
783
784 if (p_shipment_tbl(i).shipment_id is null or p_shipment_tbl(i).shipment_id = fnd_api.g_miss_num) then
785 open c_shipment_id(p_qte_line_tbl(i).quote_line_id);
786 fetch c_shipment_id into l_shipment_id;
787 close c_shipment_id;
788 else
789 l_shipment_id := p_shipment_tbl(i).shipment_id;
790 end if;
791
792 if (l_shipment_search_tbl.exists(l_shipment_id)) then
793
794 -- get the index for the corresponding record in the l_shipment_tbl
795 -- this is so becoz the no of records in search tbl may not be
796 -- equal to the no of records in l_shipment_tbl, see bug 4665436
797 for k in 1..l_shipment_tbl.count loop
798 if (l_shipment_tbl(k).shipment_id = l_shipment_id) then
799 l_index := k;
800 exit;
801 end if;
802 end loop;
803
804 if aso_debug_pub.g_debug_flag = 'Y' then
805 aso_debug_pub.add('do_check_atp: Record found in shipment search tbl ' ,1,'Y');
806 aso_debug_pub.add('do_check_atp: l_shipment_tbl.count : '||l_shipment_tbl.count ,1,'Y');
807 aso_debug_pub.add('do_check_atp: index is : '|| l_index ,1,'Y');
808 end if;
809
810 if p_shipment_tbl(i).ship_method_code <> fnd_api.g_miss_char then
811 l_shipment_tbl(l_index).ship_method_code := p_shipment_tbl(i).ship_method_code;
812 end if;
813
814 if p_shipment_tbl(i).demand_class_code <> fnd_api.g_miss_char then
815 l_shipment_tbl(l_index).demand_class_code := p_shipment_tbl(i).demand_class_code;
816 end if;
817
818 if p_shipment_tbl(i).ship_from_org_id <> fnd_api.g_miss_num then
819 l_shipment_tbl(l_index).ship_from_org_id := p_shipment_tbl(i).ship_from_org_id;
820 end if;
821
822 if p_shipment_tbl(i).request_date <> fnd_api.g_miss_date then
823 l_shipment_tbl(l_index).request_date := p_shipment_tbl(i).request_date;
824 end if;
825
826 end if; -- end if for shipment search tbl
827
828 end loop; -- end loop for shipment tbl count
829 end if; -- end if for shipment tbl count
830
831 -- make sure variable gets reset
832 l_shipment_id := null;
833
834 if aso_debug_pub.g_debug_flag = 'Y' then
835 aso_debug_pub.add('do_check_atp:After honoring input and creating seacrh tbl l_qte_line_tbl.count: ' || l_qte_line_tbl.count,1,'Y');
836 aso_debug_pub.add('do_check_atp:After honoring input and creating seacrh tbl l_shipment_tbl.count: ' || l_shipment_tbl.count,1,'Y');
837 aso_debug_pub.add('do_check_atp: p_qte_line_tbl.count: ' || p_qte_line_tbl.count,1,'Y');
838 aso_debug_pub.add('do_check_atp: l_qte_line_search_tbl.count: ' || l_qte_line_search_tbl.count,1,'Y');
839 aso_debug_pub.add('do_check_atp: l_check_atp_for_whole_quote: '|| l_check_atp_for_whole_quote, 1, 'Y');
840 end if;
841
842 if l_check_atp_for_whole_quote = fnd_api.g_false then
843
844 --Add configured lines to input table if it is not passed
845 for i in 1..p_qte_line_tbl.count loop
846
847 if aso_debug_pub.g_debug_flag = 'Y' then
848 aso_debug_pub.add('do_check_atp: p_qte_line_tbl('||i||').quote_line_id: ' || p_qte_line_tbl(i).quote_line_id,1,'Y');
849 end if;
850
854 aso_debug_pub.add('do_check_atp: row.quote_line_id: '||row.quote_line_id);
851 for row in c_config_dtl(p_qte_line_tbl(i).quote_line_id) loop
852
853 if aso_debug_pub.g_debug_flag = 'Y' then
855 aso_debug_pub.add('do_check_atp: row.item_type_code: '||row.item_type_code);
856 aso_debug_pub.add('do_check_atp: row.config_header_id: '||row.config_header_id);
857 aso_debug_pub.add('do_check_atp: row.config_revision_num: '||row.config_revision_num);
858 aso_debug_pub.add('do_check_atp: row.component_code: '||row.component_code);
859 aso_debug_pub.add('do_check_atp: row.config_item_id: '||row.config_item_id);
860 aso_debug_pub.add('do_check_atp: row.ref_line_id: '||row.ref_line_id);
861 aso_debug_pub.add('do_check_atp: row.top_model_line_id: '||row.top_model_line_id);
862 aso_debug_pub.add('do_check_atp: row.ato_line_id: '||row.ato_line_id);
863 aso_debug_pub.add('do_check_atp: row.component_sequence_id: '||row.component_sequence_id);
864 aso_debug_pub.add('do_check_atp: row.ship_model_complete_flag: '||row.ship_model_complete_flag);
865 end if;
866
867 if row.item_type_code in('MDL', 'CFG') and row.config_header_id is not null then
868
869 if not l_config_hdr_search_tbl.exists(row.config_header_id) then
870
871 if aso_debug_pub.g_debug_flag = 'Y' then
872 aso_debug_pub.add('do_check_atp: Inside not l_config_hdr_search_tbl.exists(row.config_header_id) cond.');
873 end if;
874
875 --If it is a non SMC PTO then do not add all the configuration lines from database if calling application is not passing
876
877 if nvl(row.ship_model_complete_flag, 'N') = 'Y' then
878
879 if aso_debug_pub.g_debug_flag = 'Y' then
880 aso_debug_pub.add('do_check_atp: Inside row.ship_model_complete_flag = Y cond: ');
881 end if;
882
883 --Add it to search table
884 --l_smc_search_tbl(row.top_model_line_id) := l_ship_model_complete_flag;
885 l_config_hdr_search_tbl(row.config_header_id) := row.top_model_line_id;
886
887 if aso_debug_pub.g_debug_flag = 'Y' then
888 aso_debug_pub.add('do_check_atp: After adding to l_config_hdr_search_tbl.');
889 end if;
890
891 for k in c_configuration_rows(row.config_header_id, row.config_revision_num) loop
892
893 if aso_debug_pub.g_debug_flag = 'Y' then
894 aso_debug_pub.add('do_check_atp: k.quote_line_id: ' || k.quote_line_id,1,'Y');
895 aso_debug_pub.add('do_check_atp: k.shipment_id: ' || k.shipment_id,1,'Y');
896 end if;
897
898 if not l_qte_line_search_tbl.exists(k.quote_line_id) then
899
900 if aso_debug_pub.g_debug_flag = 'Y' then
901 aso_debug_pub.add('Quote line id does not exist in l_qte_line_search_tbl, so add it to l_qte_line_tbl from database');
902 end if;
903
904 l_qte_line_rec := aso_utility_pvt.query_qte_line_row(k.quote_line_id);
905 l_qte_line_tbl(l_qte_line_tbl.count + 1) := l_qte_line_rec;
906
907 end if;
908
909 if not l_shipment_search_tbl.exists(k.shipment_id) then
910
911 if aso_debug_pub.g_debug_flag = 'Y' then
912 aso_debug_pub.add('shipment id does not exist in l_shipment_search_tbl, so add it to l_shipment_tbl from database');
913 end if;
914
915 l_shipment_rec := aso_utility_pvt.query_shipment_row(k.shipment_id);
916 l_shipment_rec.qte_line_index := l_qte_line_tbl.count;
917 l_shipment_tbl(l_shipment_tbl.count + 1) := l_shipment_rec;
918
919 end if;
920
921 end loop;
922
923 elsif row.item_type_code = 'MDL' and row.ato_line_id is null then
924
925 --This is a Non SMC PTO Model Line, so pass all the lines of this configuration to ATP
926
927 l_config_hdr_search_tbl(row.config_header_id) := row.top_model_line_id;
928
929 if aso_debug_pub.g_debug_flag = 'Y' then
930 aso_debug_pub.add('do_check_atp: After adding to l_config_hdr_search_tbl: ');
931 end if;
932
933 for k in c_configuration_rows(row.config_header_id, row.config_revision_num) loop
934
935 if aso_debug_pub.g_debug_flag = 'Y' then
936 aso_debug_pub.add('do_check_atp: k.quote_line_id: ' || k.quote_line_id,1,'Y');
937 aso_debug_pub.add('do_check_atp: k.shipment_id: ' || k.shipment_id,1,'Y');
938 end if;
939
943 aso_debug_pub.add('Quote line id does not exist in l_qte_line_search_tbl, so add it to l_qte_line_tbl from database');
940 if not l_qte_line_search_tbl.exists(k.quote_line_id) then
941
942 if aso_debug_pub.g_debug_flag = 'Y' then
944 end if;
945
946 l_qte_line_rec := aso_utility_pvt.query_qte_line_row(k.quote_line_id);
947 l_qte_line_tbl(l_qte_line_tbl.count + 1) := l_qte_line_rec;
948
949 end if;
950
951 if not l_shipment_search_tbl.exists(k.shipment_id) then
952
953 if aso_debug_pub.g_debug_flag = 'Y' then
954 aso_debug_pub.add('shipment id does not exist in l_shipment_search_tbl, so add it to l_shipment_tbl from database');
955 end if;
956
957 l_shipment_rec := aso_utility_pvt.query_shipment_row(k.shipment_id);
958 l_shipment_rec.qte_line_index := l_qte_line_tbl.count;
959 l_shipment_tbl(l_shipment_tbl.count + 1) := l_shipment_rec;
960
961 end if;
962
963 end loop;
964
965 elsif row.ato_line_id is not null then
966
967 if aso_debug_pub.g_debug_flag = 'Y' then
968 aso_debug_pub.add('do_check_atp: Inside row.ato_line_id is not null condition.');
969 aso_debug_pub.add('do_check_atp: row.ato_line_id: ' || row.ato_line_id);
970 end if;
971
972 --This line is a Model or component of a ATO configuration under the non smc PTO model
973 --OR the root ATO model line
974 --Add complete ATO configuration to input quote line and shipment table
975
976 if not l_ato_line_id_tbl.exists(row.ato_line_id) then
977
978 if aso_debug_pub.g_debug_flag = 'Y' then
979 aso_debug_pub.add('do_check_atp: Inside row.ato_line_id does not exist in search tbl.');
980 end if;
981
982 if row.item_type_code = 'MDL' then
983 l_config_hdr_search_tbl(row.config_header_id) := row.top_model_line_id;
984 end if;
985
986 for k in c_ato_rows(row.config_header_id, row.config_revision_num, row.ato_line_id) loop
987
988 if aso_debug_pub.g_debug_flag = 'Y' then
989 aso_debug_pub.add('do_check_atp: k.quote_line_id: ' || k.quote_line_id,1,'Y');
990 aso_debug_pub.add('do_check_atp: k.shipment_id: ' || k.shipment_id,1,'Y');
991 end if;
992
993 if not l_qte_line_search_tbl.exists(k.quote_line_id) then
994
995 if aso_debug_pub.g_debug_flag = 'Y' then
996 aso_debug_pub.add('Quote line id does not exist in l_qte_line_search_tbl, so add it to l_qte_line_tbl from database');
997 end if;
998
999 l_qte_line_rec := aso_utility_pvt.query_qte_line_row(k.quote_line_id);
1000 l_qte_line_tbl(l_qte_line_tbl.count + 1) := l_qte_line_rec;
1001
1002 end if;
1003
1004 if not l_shipment_search_tbl.exists(k.shipment_id) then
1005
1006 if aso_debug_pub.g_debug_flag = 'Y' then
1007 aso_debug_pub.add('shipment id does not exist in l_shipment_search_tbl, so add it to l_shipment_tbl from database');
1008 end if;
1009
1010 l_shipment_rec := aso_utility_pvt.query_shipment_row(k.shipment_id);
1011 l_shipment_rec.qte_line_index := l_qte_line_tbl.count;
1012 l_shipment_tbl(l_shipment_tbl.count + 1) := l_shipment_rec;
1013
1014 end if;
1015
1016 end loop;
1017
1018 l_ato_line_id_tbl(row.ato_line_id) := row.ato_line_id;
1019
1020 end if; --if not l_ato_line_id_tbl.exists(row.ato_line_id)
1021
1022 end if; --if nvl(l_ship_model_complete_flag, 'N') = 'Y'
1023
1024 end if; --if not l_config_hdr_search_tbl.exists(row.config_header_id)
1025
1026 end if; --if row.item_type_code in('MDL', 'CFG') and row.config_header_id is not null
1027
1028 end loop; --for row in c_config_dtl(p_qte_line_tbl(i).quote_line_id) loop
1029
1030 end loop; --for i in 1..p_qte_line_tbl.count loop
1031
1032 end if; --l_check_atp_for_whole_quote = fnd_api.g_false
1033
1034
1035 /* Get profile value for ASO: ATP Use Sourcing Rules. If the value is null or 'N'
1036 then get l_ship_from_org_id from the profile ASO_SHIP_FROM_ORG_ID and pass the
1037 source_organization_id as the value of l_ship_from_org_id.
1038 */
1039
1040 l_use_sourcing_rule := fnd_profile.value(name => 'ASO_ATP_USE_SOURCING_RULE');
1041
1042 if aso_debug_pub.g_debug_flag = 'Y' then
1043 aso_debug_pub.add('do_check_atp: ASO: Use Sourcing Rule profile value is: '||l_use_sourcing_rule,1,'Y');
1044 end if;
1045
1046 if l_use_sourcing_rule IS NULL OR l_use_sourcing_rule = 'N' then
1047
1048 -- Get the value for Ship from org Id.
1049 l_ship_from_org_id := fnd_profile.value(name => 'ASO_SHIP_FROM_ORG_ID');
1050
1051 if aso_debug_pub.g_debug_flag = 'Y' then
1052 aso_debug_pub.add('do_check_atp: ASO: Default Ship From Org profile value is: '||l_ship_from_org_id, 1, 'Y');
1053 end if;
1054
1055 end if;
1056
1057 if l_qte_line_tbl.count > 0 then
1058
1059 if aso_debug_pub.g_debug_flag = 'Y' then
1060 aso_debug_pub.add('do_check_atp: Before call to aso_line_num_int.reset_line_num procedure', 1, 'Y');
1061 end if;
1062
1063 aso_line_num_int.reset_line_num;
1064 l_in_qte_line_number_tbl(1).quote_line_id := l_qte_line_tbl(1).quote_line_id;
1065
1066 if aso_debug_pub.g_debug_flag = 'Y' then
1067 aso_debug_pub.add('do_check_atp: Before call to aso_line_num_int.aso_ui_line_number procedure', 1, 'Y');
1068 end if;
1069
1070 aso_line_num_int.aso_ui_line_number( p_in_Line_number_tbl => l_in_qte_line_number_tbl,
1071 x_out_line_number_tbl => l_out_qte_line_number_tbl);
1072
1073
1074 if aso_debug_pub.g_debug_flag = 'Y' then
1075 aso_debug_pub.add('do_check_atp: After call to aso_line_num_int.aso_ui_line_number procedure', 1, 'Y');
1076 end if;
1077
1078 end if;
1079
1080
1081
1082 /* Logic for ordering the output by UI Line Number */
1083 for i in 1..l_qte_line_tbl.count loop
1084 l_search_tbl(l_qte_line_tbl(i).quote_line_id) := i;
1085 end loop;
1086
1087 open get_ordered_lines(p_qte_header_rec.quote_header_id);
1088 loop
1089 fetch get_ordered_lines into l_qte_line_id_from_bali;
1090 exit when get_ordered_lines%NOTFOUND;
1091 if l_search_tbl.exists(l_qte_line_id_from_bali) then
1092 l_new_qte_line_tbl(l_new_qte_line_tbl.count +1) := l_qte_line_tbl(l_search_tbl(l_qte_line_id_from_bali) );
1093 l_new_shipment_tbl(l_new_shipment_tbl.count + 1) := l_shipment_tbl(l_search_tbl(l_qte_line_id_from_bali) );
1094 end if;
1095 end loop;
1096 Close get_ordered_lines;
1097
1098 l_qte_line_tbl := l_new_qte_line_tbl;
1099 l_shipment_tbl := l_new_shipment_tbl;
1100
1101 /* End Logic for Ordering Output */
1102
1103 -- if qte line tbl count is 0 that means quote has all service items or all trade-ins, then return
1104 if l_qte_line_tbl.count = 0 then
1105 x_return_status := fnd_api.g_ret_sts_success;
1106 return;
1107 end if;
1108
1109 if aso_debug_pub.g_debug_flag = 'Y' then
1110 aso_debug_pub.add('do_check_atp: ********************************************************', 1, 'Y');
1111 aso_debug_pub.add('do_check_atp: Printing the data in l_qte_line_tbl and l_shipment_tbl', 1, 'Y');
1112 for i in 1..l_qte_line_tbl.count loop
1113 aso_debug_pub.add('do_check_atp: l_qte_line_tbl('||i||').quote_line_id: '|| l_qte_line_tbl(i).quote_line_id, 1, 'Y');
1114 end loop;
1115 for i in 1..l_shipment_tbl.count loop
1116 aso_debug_pub.add('do_check_atp: l_shipment_tbl('||i||').ship_method_code: '|| l_shipment_tbl(i).ship_method_code, 1, 'Y');
1117 aso_debug_pub.add('do_check_atp: l_shipment_tbl('||i||').ship_from_org_id: '|| l_shipment_tbl(i).ship_from_org_id, 1, 'Y');
1118 aso_debug_pub.add('do_check_atp: l_shipment_tbl('||i||').demand_class_code: '|| l_shipment_tbl(i).demand_class_code, 1, 'Y');
1119 aso_debug_pub.add('do_check_atp: l_shipment_tbl('||i||').request_date: '|| l_shipment_tbl(i).request_date, 1, 'Y');
1120 aso_debug_pub.add('do_check_atp: l_shipment_tbl('||i||').quote_line_id: '|| l_shipment_tbl(i).quote_line_id, 1, 'Y');
1121 aso_debug_pub.add('do_check_atp: l_shipment_tbl('||i||').shipment_id: '|| l_shipment_tbl(i).shipment_id, 1, 'Y');
1122 end loop;
1123 aso_debug_pub.add('do_check_atp: ********************************************************', 1, 'Y');
1124 end if;
1125
1126 for i in 1 .. l_qte_line_tbl.count loop
1127
1128 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1129 aso_debug_pub.add('do_check_atp: Before call to MSC_GLOBAL_ATP.EXTEND_ATP', 1, 'Y');
1130 END IF;
1131
1132 MSC_ATP_GLOBAL.EXTEND_ATP(l_atp_rec, x_return_status, 1);
1133
1134 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1135 aso_debug_pub.add('do_check_atp: After call to MSC_GLOBAL_ATP.EXTEND_ATP: x_return_status: '||x_return_status);
1136 END IF;
1137
1138 If x_return_status <> FND_API.G_RET_STS_SUCCESS Then
1139
1140 IF x_return_status = FND_API.G_RET_STS_ERROR then
1141 RAISE FND_API.G_EXC_ERROR;
1142 ELSE
1143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1144 END IF;
1145
1146 End if;
1147
1148 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1149 aso_debug_pub.add('do_check_atp: l_db_qte_line_rec.quote_line_i: '|| ASO_ATP_INT.ATPQUERY);
1150 aso_debug_pub.add('l_qte_line_tbl('||i||').quote_line_id: '|| l_qte_line_tbl(i).quote_line_id);
1154 l_db_qte_line_rec := aso_utility_pvt.query_qte_line_row(l_qte_line_tbl(i).quote_line_id);
1151 END IF;
1152
1153 if l_check_atp_for_whole_quote = fnd_api.g_false then
1155 else
1156 l_db_qte_line_rec := l_qte_line_tbl(i);
1157 end if;
1158
1159 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1160 aso_debug_pub.add('do_check_atp: ASO_ATP_INT.ATPQUERY: '|| ASO_ATP_INT.ATPQUERY);
1161 aso_debug_pub.add('do_check_atp: fnd_global.prog_appl_id: '|| fnd_global.prog_appl_id);
1162 aso_debug_pub.add('l_db_qte_line_rec.inventory_item_id: '|| l_db_qte_line_rec.inventory_item_id);
1163 aso_debug_pub.add('l_db_qte_line_rec.quantity: '|| l_db_qte_line_rec.quantity);
1164 aso_debug_pub.add('l_db_qte_line_rec.uom_code: '|| l_db_qte_line_rec.uom_code);
1165 aso_debug_pub.add('l_db_qte_line_rec.organization_id: '|| l_db_qte_line_rec.organization_id);
1166 aso_debug_pub.add('l_db_qte_line_rec.quote_header_id: '|| l_db_qte_line_rec.quote_header_id);
1167 END IF;
1168
1169 l_atp_rec.action(i) := ASO_ATP_INT.ATPQUERY;
1170 l_atp_rec.calling_module(i) := fnd_global.prog_appl_id;
1171 l_atp_rec.inventory_item_id(i) := l_db_qte_line_rec.inventory_item_id;
1172 l_atp_rec.validation_org(i) := l_db_qte_line_rec.organization_id;
1173 l_atp_rec.identifier(i) := l_db_qte_line_rec.quote_line_id;
1174 l_atp_rec.quantity_ordered(i) := l_db_qte_line_rec.quantity;
1175 l_atp_rec.quantity_uom(i) := l_db_qte_line_rec.uom_code;
1176 l_atp_rec.demand_source_header_id(i) := l_db_qte_line_rec.quote_header_id;
1177 l_atp_rec.included_item_flag(i) := 2;
1178 --l_atp_rec.cascade_model_info_to_comp(i) := 2;
1179 l_atp_rec.line_number(i) := aso_line_num_int.get_ui_line_number(l_db_qte_line_rec.quote_line_id);
1180
1181 open c_description(l_db_qte_line_rec.inventory_item_id, l_db_qte_line_rec.organization_id);
1182 fetch c_description into l_atp_rec.inventory_item_name(i);
1183 close c_description;
1184
1185 open c_quote_number(l_db_qte_line_rec.quote_header_id);
1186 fetch c_quote_number into l_atp_rec.order_number(i), l_atp_rec.customer_id(i);
1187 close c_quote_number;
1188
1189 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1190 aso_debug_pub.add('l_atp_rec.order_number('||i||'): '|| l_atp_rec.order_number(i));
1191 aso_debug_pub.add('l_atp_rec.customer_id('||i||'): '|| l_atp_rec.customer_id(i));
1192 END IF;
1193
1194
1195 If l_shipment_tbl.EXISTS(i) Then
1196
1197 -- query the hdr shipment rec
1198 l_hdr_shipment_tbl := aso_utility_pvt.query_shipment_rows(p_qte_header_rec.quote_header_id,null);
1199
1200 if l_hdr_shipment_tbl.count > 0 then
1201 l_hdr_shipment_rec := l_hdr_shipment_tbl(1);
1202 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1203 aso_debug_pub.add('Header Shipment Request_date_type: '|| l_hdr_shipment_rec.request_date_type );
1204 END IF;
1205 end if;
1206
1207
1208 if l_check_atp_for_whole_quote = fnd_api.g_false then
1209 l_db_shipment_rec := aso_utility_pvt.query_shipment_row(l_shipment_tbl(i).shipment_id);
1210 else
1211 l_db_shipment_rec := l_shipment_tbl(i);
1212 end if;
1213
1214 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1215 aso_debug_pub.add('do_check_atp: ****** Database shipment record has following data ******', 1, 'Y');
1216 aso_debug_pub.add('do_check_atp: l_db_shipment_rec.ship_method_code: '|| l_shipment_tbl(i).ship_method_code, 1, 'Y');
1217 aso_debug_pub.add('do_check_atp: l_db_shipment_rec.ship_from_org_id: '|| l_shipment_tbl(i).ship_from_org_id, 1, 'Y');
1218 aso_debug_pub.add('do_check_atp: l_db_shipment_rec.demand_class_code: '|| l_shipment_tbl(i).demand_class_code, 1, 'Y');
1219 aso_debug_pub.add('do_check_atp: l_db_shipment_rec.request_date: '|| l_shipment_tbl(i).request_date, 1, 'Y');
1220 aso_debug_pub.add('do_check_atp: l_db_shipment_rec.request_date_type: '|| l_shipment_tbl(i).request_date_type, 1, 'Y');
1221 aso_debug_pub.add('do_check_atp: l_db_shipment_rec.quote_line_id: '|| l_shipment_tbl(i).quote_line_id, 1, 'Y');
1222 aso_debug_pub.add('do_check_atp: l_db_shipment_rec.shipment_id: '|| l_shipment_tbl(i).shipment_id, 1, 'Y');
1223 aso_debug_pub.add('do_check_atp: ****** End of Database shipment record data ******', 1, 'Y');
1224 END IF;
1225 -- fix for bug 4724470, over-riding the line req date type with the hdr record req date type
1226 -- this is becoz req date type at line level is not supported and hence over-written
1227 -- with the hdr value
1228 l_db_shipment_rec.request_date_type := l_hdr_shipment_rec.request_date_type;
1229
1230
1231
1232
1233 if l_shipment_tbl(i).ship_from_org_id is not null and l_shipment_tbl(i).ship_from_org_id <> fnd_api.g_miss_num then
1234 l_atp_rec.source_organization_id(i) := l_shipment_tbl(i).ship_from_org_id;
1235
1236 else
1237
1238 open c_get_ids( l_qte_line_tbl(i).quote_line_id );
1239 fetch c_get_ids into l_ato_line_id,l_top_model_line_id;
1240 close c_get_ids;
1241 -- if the line is an option under ATO
1242 if (l_ato_line_id is not null and l_top_model_line_id is not null )then
1243 -- check if record has been passed in , then honor that
1244 open c_shipment_id(l_ato_line_id);
1245 fetch c_shipment_id into l_shipment_id;
1246 close c_shipment_id;
1247 if (l_shipment_search_tbl.exists(l_shipment_id) and
1248 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_from_org_id is not null) and
1249 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_from_org_id <> fnd_api.g_miss_num))) then
1250 l_atp_rec.source_organization_id(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_from_org_id;
1251 else
1252 -- try to cascade database info from ATO Model
1253 open c_get_warehouse(l_ato_line_id, l_qte_line_tbl(i).quote_header_id);
1254 fetch c_get_warehouse into l_cascade_ship_from_org_id;
1255 close c_get_warehouse;
1256 if l_cascade_ship_from_org_id is not null and l_cascade_ship_from_org_id <> fnd_api.g_miss_num then
1257 l_atp_rec.source_organization_id(i) := l_cascade_ship_from_org_id;
1258 else
1259 -- try to cascade from top model
1260 if l_ato_line_id <> l_top_model_line_id then
1261 -- check if PTO Model record has been passed in , then honor that
1262 open c_shipment_id(l_top_model_line_id);
1263 fetch c_shipment_id into l_shipment_id;
1264 close c_shipment_id;
1265 if (l_shipment_search_tbl.exists(l_shipment_id) and
1266 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_from_org_id is not null) and
1267 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_from_org_id <> fnd_api.g_miss_num))) then
1268 l_atp_rec.source_organization_id(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_from_org_id;
1269 else
1270 open c_get_warehouse(l_top_model_line_id, l_qte_line_tbl(i).quote_header_id);
1271 fetch c_get_warehouse into l_cascade_ship_from_org_id;
1272 close c_get_warehouse;
1273 if l_cascade_ship_from_org_id is not null and l_cascade_ship_from_org_id <> fnd_api.g_miss_num then
1274 l_atp_rec.source_organization_id(i) := l_cascade_ship_from_org_id;
1275 end if;
1276 end if;
1277 end if; -- ato and model are not same end if
1278 end if;
1279 end if; -- shipment tbl exists end if
1280 -- if it as a option under an PTO Model
1281 elsif (l_ato_line_id is null and l_top_model_line_id is not null )then
1282 open c_shipment_id(l_top_model_line_id);
1283 fetch c_shipment_id into l_shipment_id;
1284 close c_shipment_id;
1285 if (l_shipment_search_tbl.exists(l_shipment_id) and
1286 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_from_org_id is not null) and
1287 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_from_org_id <> fnd_api.g_miss_num))) then
1288 l_atp_rec.source_organization_id(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_from_org_id;
1289 else
1290 open c_get_warehouse(l_top_model_line_id, l_qte_line_tbl(i).quote_header_id);
1291 fetch c_get_warehouse into l_cascade_ship_from_org_id;
1292 close c_get_warehouse;
1293 if l_cascade_ship_from_org_id is not null and l_cascade_ship_from_org_id <> fnd_api.g_miss_num then
1294 l_atp_rec.source_organization_id(i) := l_cascade_ship_from_org_id;
1295 end if;
1296 end if;
1297 end if; -- ato line id and model line id not null end if
1298
1299 end if; -- original end if
1300
1301 if l_atp_rec.source_organization_id(i) is null or l_atp_rec.source_organization_id(i) = fnd_api.g_miss_num then
1302 if l_db_shipment_rec.ship_from_org_id is not null and l_db_shipment_rec.ship_from_org_id <> fnd_api.g_miss_num then
1303 l_atp_rec.source_organization_id(i) := l_db_shipment_rec.ship_from_org_id;
1304 else
1305 if nvl(l_use_sourcing_rule,'N') = 'Y' then
1306 l_atp_rec.source_organization_id(i) := null;
1307 else
1308 -- if after everything it is still null, get value from profile
1309 l_atp_rec.source_organization_id(i) := l_ship_from_org_id;
1310 end if; -- check for profile
1311 end if;
1312 end if;
1313
1314
1315 if aso_debug_pub.g_debug_flag = 'Y' then
1316 aso_debug_pub.add('l_atp_rec.source_organization_id('||i||'): '|| l_atp_rec.source_organization_id(i));
1320
1317 aso_debug_pub.add('l_db_shipment_rec.request_date_type: '|| l_db_shipment_rec.request_date_type);
1318 aso_debug_pub.add('l_shipment_tbl('||i||').request_date: '|| l_shipment_tbl(i).request_date);
1319 end if;
1321 if (l_db_shipment_rec.request_date_type is null or l_db_shipment_rec.request_date_type = 'SHIP'
1322 or l_db_shipment_rec.request_date_type = fnd_api.g_miss_char) then
1323
1324 if ((l_shipment_tbl(i).request_date <> fnd_api.g_miss_date) and (l_shipment_tbl(i).request_date is not null)) then
1325 l_atp_rec.requested_ship_date(i) := l_shipment_tbl(i).request_date;
1326 else
1327 open c_get_ids( l_qte_line_tbl(i).quote_line_id );
1328 fetch c_get_ids into l_ato_line_id,l_top_model_line_id;
1329 close c_get_ids;
1330 if aso_debug_pub.g_debug_flag = 'Y' then
1331 aso_debug_pub.add('l_ato_line_id: '|| l_ato_line_id);
1332 aso_debug_pub.add('l_top_model_line_id: '|| l_top_model_line_id);
1333 end if;
1334 -- if the line is an option under ATO
1335 if (l_ato_line_id is not null and l_top_model_line_id is not null )then
1336 -- check if record has been passed in , then honor that
1337 open c_shipment_id(l_ato_line_id);
1338 fetch c_shipment_id into l_shipment_id;
1339 close c_shipment_id;
1340 if (l_shipment_search_tbl.exists(l_shipment_id) and
1341 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date is not null) and
1342 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date<> fnd_api.g_miss_date))) then
1343 if aso_debug_pub.g_debug_flag = 'Y' then
1344 aso_debug_pub.add('getting the request date from input ');
1345 end if;
1346 l_atp_rec.requested_ship_date(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date;
1347 else
1348 -- try to cascade database info from ATO Model
1349 open c_get_request_date(l_ato_line_id, l_qte_line_tbl(i).quote_header_id,'SHIP');
1350 fetch c_get_request_date into l_cascade_request_date;
1351 close c_get_request_date;
1352 if aso_debug_pub.g_debug_flag = 'Y' then
1353 aso_debug_pub.add('l_cascade_request_date: '|| l_cascade_request_date );
1354 end if;
1355 if l_cascade_request_date is not null and l_cascade_request_date <> fnd_api.g_miss_date then
1356 l_atp_rec.requested_ship_date(i) := l_cascade_request_date;
1357 else
1358 -- try to cascade from top model
1359 if l_ato_line_id <> l_top_model_line_id then
1360 -- check if PTO Model record has been passed in , then honor that
1361 open c_shipment_id(l_top_model_line_id);
1362 fetch c_shipment_id into l_shipment_id;
1363 close c_shipment_id;
1364 if (l_shipment_search_tbl.exists(l_shipment_id) and
1365 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date is not null) and
1366 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date <> fnd_api.g_miss_date))) then
1367 l_atp_rec.requested_ship_date(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date;
1368 else
1369 open c_get_request_date(l_top_model_line_id, l_qte_line_tbl(i).quote_header_id,'SHIP');
1370 fetch c_get_request_date into l_cascade_request_date;
1371 close c_get_request_date;
1372 if l_cascade_request_date is not null and l_cascade_request_date <> fnd_api.g_miss_date then
1373 l_atp_rec.requested_ship_date(i) := l_cascade_request_date;
1374 end if;
1375 end if;
1376 end if; -- ato and model are not same end if
1377 end if;
1378 end if; -- shipment tbl exists end if
1379 -- if it as a option under an PTO Model
1380 elsif (l_ato_line_id is null and l_top_model_line_id is not null )then
1381 open c_shipment_id(l_top_model_line_id);
1382 fetch c_shipment_id into l_shipment_id;
1383 close c_shipment_id;
1384 if (l_shipment_search_tbl.exists(l_shipment_id) and
1385 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date is not null) and
1386 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date <> fnd_api.g_miss_date))) then
1387 l_atp_rec.requested_ship_date(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date ;
1388 else
1389 open c_get_request_date(l_top_model_line_id, l_qte_line_tbl(i).quote_header_id,'SHIP');
1390 fetch c_get_request_date into l_cascade_request_date;
1391 close c_get_request_date;
1392 if l_cascade_request_date is not null and l_cascade_request_date <> fnd_api.g_miss_date then
1393 l_atp_rec.requested_ship_date(i) := l_cascade_request_date;
1394 end if;
1395 end if;
1396 end if; -- ato line id and model line id not null end if
1397
1398 end if; -- original end if
1399 -- even after trying to cascade value is null then get from db
1400 if ( l_atp_rec.requested_ship_date(i) is null or l_atp_rec.requested_ship_date(i) = fnd_api.g_miss_date) then
1401 if ((l_db_shipment_rec.request_date is not null) and (l_db_shipment_rec.request_date <> fnd_api.g_miss_date)) then
1402 l_atp_rec.requested_ship_date(i) := l_db_shipment_rec.request_date;
1403 else
1404
1405 -- fix for bug 4724374 if db value is null get the value from the hdr record
1406 if ( l_hdr_shipment_rec.request_date is not null and l_hdr_shipment_rec.request_date <> fnd_api.g_miss_date ) then
1407 l_atp_rec.requested_ship_date(i) := l_hdr_shipment_rec.request_date;
1408 else
1409 if aso_debug_pub.g_debug_flag = 'Y' then
1410 aso_debug_pub.add('Setting the request date to sysdate' );
1411 end if;
1412 l_atp_rec.requested_ship_date(i) := sysdate;
1413 end if; -- end if for the hdr rec check
1414 end if;
1415 end if;
1416 elsif l_db_shipment_rec.request_date_type = 'ARRIVAL' then
1417
1418 if ((l_shipment_tbl(i).request_date <> fnd_api.g_miss_date) and (l_shipment_tbl(i).request_date is not null)) then
1419 l_atp_rec.requested_arrival_date(i) := l_shipment_tbl(i).request_date;
1420 else
1421 open c_get_ids( l_qte_line_tbl(i).quote_line_id );
1422 fetch c_get_ids into l_ato_line_id,l_top_model_line_id;
1423 close c_get_ids;
1424 -- if the line is an option under ATO
1425 if (l_ato_line_id is not null and l_top_model_line_id is not null )then
1426 -- check if record has been passed in , then honor that
1427 open c_shipment_id(l_ato_line_id);
1428 fetch c_shipment_id into l_shipment_id;
1429 close c_shipment_id;
1430 if (l_shipment_search_tbl.exists(l_shipment_id) and
1431 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date is not null) and
1432 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date<> fnd_api.g_miss_date))) then
1433 l_atp_rec.requested_arrival_date(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date;
1434 else
1435 -- try to cascade database info from ATO Model
1436 open c_get_request_date(l_ato_line_id, l_qte_line_tbl(i).quote_header_id,'ARRIVAL');
1437 fetch c_get_request_date into l_cascade_request_date;
1438 close c_get_request_date;
1439 if l_cascade_request_date is not null and l_cascade_request_date <> fnd_api.g_miss_date then
1440 l_atp_rec.requested_arrival_date(i) := l_cascade_request_date;
1441 else
1442 -- try to cascade from top model
1443 if l_ato_line_id <> l_top_model_line_id then
1444 -- check if PTO Model record has been passed in , then honor that
1445 open c_shipment_id(l_top_model_line_id);
1446 fetch c_shipment_id into l_shipment_id;
1447 close c_shipment_id;
1448 if (l_shipment_search_tbl.exists(l_shipment_id) and
1449 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date is not null) and
1450 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date <> fnd_api.g_miss_date))) then
1451 l_atp_rec.requested_arrival_date(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date;
1452 else
1453 open c_get_request_date(l_top_model_line_id, l_qte_line_tbl(i).quote_header_id,'ARRIVAL');
1454 fetch c_get_request_date into l_cascade_request_date;
1455 close c_get_request_date;
1456 if l_cascade_request_date is not null and l_cascade_request_date <> fnd_api.g_miss_date then
1457 l_atp_rec.requested_arrival_date(i) := l_cascade_request_date;
1458 end if;
1459 end if;
1460 end if; -- ato and model are not same end if
1461 end if;
1462 end if; -- shipment tbl exists end if
1463 -- if it as a option under an PTO Model
1464 elsif (l_ato_line_id is null and l_top_model_line_id is not null )then
1465 open c_shipment_id(l_top_model_line_id);
1466 fetch c_shipment_id into l_shipment_id;
1467 close c_shipment_id;
1468 if (l_shipment_search_tbl.exists(l_shipment_id) and
1469 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date is not null) and
1470 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date <> fnd_api.g_miss_date))) then
1471 l_atp_rec.requested_arrival_date(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).request_date ;
1472 else
1473 open c_get_request_date(l_top_model_line_id, l_qte_line_tbl(i).quote_header_id,'ARRIVAL');
1474 fetch c_get_request_date into l_cascade_request_date;
1475 close c_get_request_date;
1476 if l_cascade_request_date is not null and l_cascade_request_date <> fnd_api.g_miss_date then
1477 l_atp_rec.requested_arrival_date(i) := l_cascade_request_date;
1478 end if;
1479 end if;
1480 end if; -- ato line id and model line id not null end if
1481
1482 end if; -- original end if
1483 -- even after trying to cascade value is null then get from db
1484 if ( l_atp_rec.requested_arrival_date(i) is null or l_atp_rec.requested_arrival_date(i) = fnd_api.g_miss_date) then
1485 if ((l_db_shipment_rec.request_date is not null) and (l_db_shipment_rec.request_date <> fnd_api.g_miss_date)) then
1486 l_atp_rec.requested_arrival_date(i) := l_db_shipment_rec.request_date;
1487 else
1488 -- fix for bug 4724374
1489 if ( l_hdr_shipment_rec.request_date is not null and l_hdr_shipment_rec.request_date <> fnd_api.g_miss_date) then
1490 l_atp_rec.requested_arrival_date(i) := l_hdr_shipment_rec.request_date;
1491 else
1492 l_atp_rec.requested_arrival_date(i) := sysdate;
1493 end if; -- end if for the hdr rec check
1494 end if;
1495 end if;
1496
1497
1498 end if;
1499
1500 if aso_debug_pub.g_debug_flag = 'Y' then
1501 aso_debug_pub.add('l_shipment_tbl('||i||').ship_method_code: '|| l_shipment_tbl(i).ship_method_code);
1502 end if;
1503
1504 if (l_shipment_tbl(i).ship_method_code <> fnd_api.g_miss_char and l_shipment_tbl(i).ship_method_code is not null) then
1505 l_atp_rec.ship_method(i) := l_shipment_tbl(i).ship_method_code;
1506
1507 else
1508 open c_get_ids( l_qte_line_tbl(i).quote_line_id );
1509 fetch c_get_ids into l_ato_line_id,l_top_model_line_id;
1510 close c_get_ids;
1511 -- if the line is an option under ATO
1512 if (l_ato_line_id is not null and l_top_model_line_id is not null )then
1513 -- check if record has been passed in , then honor that
1514 open c_shipment_id(l_ato_line_id);
1515 fetch c_shipment_id into l_shipment_id;
1516 close c_shipment_id;
1517 if (l_shipment_search_tbl.exists(l_shipment_id) and
1518 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_method_code is not null) and
1519 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_method_code<> fnd_api.g_miss_char))) then
1520 l_atp_rec.ship_method(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_method_code;
1521 else
1522 -- try to cascade database info from ATO Model
1523 open c_get_ship_method(l_ato_line_id, l_qte_line_tbl(i).quote_header_id);
1524 fetch c_get_ship_method into l_cascade_ship_method_code;
1525 close c_get_ship_method;
1526 if l_cascade_ship_method_code is not null and l_cascade_ship_method_code<> fnd_api.g_miss_char then
1527 l_atp_rec.ship_method(i) := l_cascade_ship_method_code;
1528 else
1529 -- try to cascade from top model
1530 if l_ato_line_id <> l_top_model_line_id then
1531 -- check if PTO Model record has been passed in , then honor that
1532 open c_shipment_id(l_top_model_line_id);
1533 fetch c_shipment_id into l_shipment_id;
1534 close c_shipment_id;
1535 if (l_shipment_search_tbl.exists(l_shipment_id) and
1536 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_method_code is not null) and
1537 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_method_code<> fnd_api.g_miss_char))) then
1538 l_atp_rec.ship_method(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_method_code;
1539 else
1540 open c_get_ship_method(l_top_model_line_id, l_qte_line_tbl(i).quote_header_id);
1541 fetch c_get_ship_method into l_cascade_ship_method_code;
1542 close c_get_ship_method;
1543 if l_cascade_ship_method_code is not null and l_cascade_ship_method_code<> fnd_api.g_miss_char then
1544 l_atp_rec.ship_method(i) := l_cascade_ship_method_code;
1545 end if;
1546 end if;
1547 end if; -- ato and model are not same end if
1548 end if;
1549 end if; -- shipment tbl exists end if
1550 -- if it as a option under an PTO Model
1551 elsif (l_ato_line_id is null and l_top_model_line_id is not null )then
1552 open c_shipment_id(l_top_model_line_id);
1553 fetch c_shipment_id into l_shipment_id;
1554 close c_shipment_id;
1555 if (l_shipment_search_tbl.exists(l_shipment_id) and
1556 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_method_code is not null) and
1557 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_method_code<> fnd_api.g_miss_char))) then
1558 l_atp_rec.ship_method(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).ship_method_code;
1559 else
1560 open c_get_ship_method(l_top_model_line_id, l_qte_line_tbl(i).quote_header_id);
1561 fetch c_get_ship_method into l_cascade_ship_method_code;
1562 close c_get_ship_method;
1563 if l_cascade_ship_method_code is not null and l_cascade_ship_method_code<> fnd_api.g_miss_char then
1564 l_atp_rec.ship_method(i) := l_cascade_ship_method_code;
1565 end if;
1566 end if;
1567 end if; -- ato line id and model line id not null end if
1568
1569 end if; -- original end if
1570
1571 if l_atp_rec.ship_method(i) is null or l_atp_rec.ship_method(i) = fnd_api.g_miss_char then
1572 if (l_db_shipment_rec.ship_method_code is not null and l_db_shipment_rec.ship_method_code <> fnd_api.g_miss_char) then
1573 l_atp_rec.ship_method(i) := l_db_shipment_rec.ship_method_code;
1574 else
1575 l_atp_rec.ship_method(i) := aso_shipment_pvt.get_ship_method_code(p_qte_header_id => l_qte_line_tbl(i).quote_header_id,
1576 p_qte_line_id => l_shipment_tbl(i).quote_line_id);
1577 end if;
1578 end if;
1579
1580 if aso_debug_pub.g_debug_flag = 'Y' then
1581 aso_debug_pub.add('l_shipment_tbl('||i||').demand_class_code: '|| l_shipment_tbl(i).demand_class_code);
1582 end if;
1583
1584
1585
1586 if (l_shipment_tbl(i).demand_class_code <> fnd_api.g_miss_char and l_shipment_tbl(i).demand_class_code is not null) then
1587 l_atp_rec.demand_class(i) := l_shipment_tbl(i).demand_class_code;
1588
1589 else
1590 open c_get_ids( l_qte_line_tbl(i).quote_line_id );
1591 fetch c_get_ids into l_ato_line_id,l_top_model_line_id;
1592 close c_get_ids;
1593 -- if the line is an option under ATO
1594 if (l_ato_line_id is not null and l_top_model_line_id is not null )then
1595 -- check if record has been passed in , then honor that
1596 open c_shipment_id(l_ato_line_id);
1597 fetch c_shipment_id into l_shipment_id;
1598 close c_shipment_id;
1599 if (l_shipment_search_tbl.exists(l_shipment_id) and
1603 else
1600 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).demand_class_code is not null) and
1601 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).demand_class_code<> fnd_api.g_miss_char))) then
1602 l_atp_rec.demand_class(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).demand_class_code;
1604 -- try to cascade database info from ATO Model
1605 open c_get_demand_code(l_ato_line_id, l_qte_line_tbl(i).quote_header_id);
1606 fetch c_get_demand_code into l_cascade_demand_class_code;
1607 close c_get_demand_code;
1608 if l_cascade_demand_class_code is not null and l_cascade_demand_class_code<> fnd_api.g_miss_char then
1609 l_atp_rec.demand_class(i) := l_cascade_demand_class_code;
1610 else
1611 -- try to cascade from top model
1612 if l_ato_line_id <> l_top_model_line_id then
1613 -- check if PTO Model record has been passed in , then honor that
1614 open c_shipment_id(l_top_model_line_id);
1615 fetch c_shipment_id into l_shipment_id;
1616 close c_shipment_id;
1617 if (l_shipment_search_tbl.exists(l_shipment_id) and
1618 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).demand_class_code is not null) and
1619 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).demand_class_code<> fnd_api.g_miss_char))) then
1620 l_atp_rec.demand_class(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).demand_class_code;
1621 else
1622 open c_get_demand_code(l_top_model_line_id, l_qte_line_tbl(i).quote_header_id);
1623 fetch c_get_demand_code into l_cascade_demand_class_code;
1624 close c_get_demand_code;
1625 if l_cascade_demand_class_code is not null and l_cascade_demand_class_code<> fnd_api.g_miss_char then
1626 l_atp_rec.demand_class(i) := l_cascade_demand_class_code;
1627 end if;
1628 end if;
1629 end if; -- ato and model are not same end if
1630 end if;
1631 end if; -- shipment tbl exists end if
1632 -- if it as a option under an PTO Model
1633 elsif (l_ato_line_id is null and l_top_model_line_id is not null )then
1634 open c_shipment_id(l_top_model_line_id);
1635 fetch c_shipment_id into l_shipment_id;
1636 close c_shipment_id;
1637 if (l_shipment_search_tbl.exists(l_shipment_id) and
1638 ((p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).demand_class_code is not null) and
1639 (p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).demand_class_code<> fnd_api.g_miss_char))) then
1640 l_atp_rec.demand_class(i) := p_shipment_tbl( l_shipment_search_tbl(l_shipment_id)).demand_class_code;
1641 else
1642 open c_get_demand_code(l_top_model_line_id, l_qte_line_tbl(i).quote_header_id);
1643 fetch c_get_demand_code into l_cascade_demand_class_code;
1644 close c_get_demand_code;
1645 if l_cascade_demand_class_code is not null and l_cascade_demand_class_code<> fnd_api.g_miss_char then
1646 l_atp_rec.demand_class(i) := l_cascade_demand_class_code;
1647 end if;
1648 end if;
1649 end if; -- ato line id and model line id not null end if
1650
1651 end if; -- original end if
1652
1653 if l_atp_rec.demand_class(i) = fnd_api.g_miss_char or l_atp_rec.demand_class(i) is null then
1654 if (l_db_shipment_rec.demand_class_code is not null and l_db_shipment_rec.demand_class_code <> fnd_api.g_miss_char) then
1655 l_atp_rec.demand_class(i) := l_db_shipment_rec.demand_class_code;
1656 else
1657 l_atp_rec.demand_class(i) := aso_shipment_pvt.get_demand_class_code(p_qte_header_id => l_qte_line_tbl(i).quote_header_id,
1658 p_qte_line_id => l_shipment_tbl(i).quote_line_id);
1659 end if;
1660 end if;
1661
1662
1663 if aso_debug_pub.g_debug_flag = 'Y' then
1664 aso_debug_pub.add('l_db_shipment_rec.ship_to_party_site_id: '|| l_db_shipment_rec.ship_to_party_site_id);
1665 aso_debug_pub.add('l_db_shipment_rec.ship_to_cust_account_id: '|| l_db_shipment_rec.ship_to_cust_account_id);
1666 end if;
1667
1668
1669 if l_db_shipment_rec.ship_to_party_site_id is not null then
1670 --l_atp_rec.customer_site_id(i) := l_db_shipment_rec.ship_to_party_site_id;
1671 l_ship_to_party_site_id := l_db_shipment_rec.ship_to_party_site_id;
1672 else
1673 l_ship_to_party_site_id := aso_shipment_pvt.get_ship_to_party_site_id(
1674 p_qte_header_id => l_qte_line_tbl(i).quote_header_id,
1675 p_qte_line_id => l_shipment_tbl(i).quote_line_id);
1676 end if;
1677
1678 if l_db_shipment_rec.ship_to_cust_account_id is not null then
1679 l_ship_to_cust_account_id := l_db_shipment_rec.ship_to_cust_account_id;
1680 else
1681 l_ship_to_cust_account_id := aso_shipment_pvt.get_ship_to_cust_account_id(
1682 p_qte_header_id => l_qte_line_tbl(i).quote_header_id,
1683 p_qte_line_id => l_shipment_tbl(i).quote_line_id);
1687
1684 end if;
1685
1686 if l_ship_to_party_site_id is not null and l_ship_to_cust_account_id is not null then
1688 ASO_MAP_QUOTE_ORDER_INT.get_acct_site_uses ( p_party_site_id => l_ship_to_party_site_id,
1689 p_acct_site_type => 'SHIP_TO',
1690 p_cust_account_id => l_ship_to_cust_account_id,
1691 x_return_status => x_return_status,
1692 x_site_use_id => lx_cust_acct_site_use_id );
1693
1694 if aso_debug_pub.g_debug_flag = 'Y' then
1695 aso_debug_pub.add('After call to ASO_MAP_QUOTE_ORDER_INT.get_acct_site_uses: x_return_status '|| x_return_status);
1696 aso_debug_pub.add('lx_cust_acct_site_use_id: '|| lx_cust_acct_site_use_id);
1697 end if;
1698
1699 l_atp_rec.customer_site_id(i) := lx_cust_acct_site_use_id;
1700
1701 elsif l_ship_to_party_site_id is not null then
1702
1703 --UnComment this after you get the ATP patch
1704 --l_atp_rec.ship_to_party_site_id(i) := l_ship_to_party_site_id;
1705 null;
1706
1707 else
1708
1709 l_atp_rec.customer_country(i) := l_shipment_tbl(i).ship_to_country;
1710 l_atp_rec.customer_state(i) := l_shipment_tbl(i).ship_to_state;
1711 l_atp_rec.customer_city(i) := l_shipment_tbl(i).ship_to_city;
1712 l_atp_rec.customer_postal_code(i) := l_shipment_tbl(i).ship_to_postal_code;
1713
1714 end if;
1715
1716 End If; --if shipment record exists
1717
1718
1719 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1720 aso_debug_pub.add('After End if for shipment record exists');
1721 END IF;
1722
1723 for j in c_config_dtl(l_qte_line_tbl(i).quote_line_id) loop
1724
1725 if aso_debug_pub.g_debug_flag = 'Y' then
1726 aso_debug_pub.add('do_check_atp: l_qte_line_tbl('||i||').quote_line_id: '||l_qte_line_tbl(i).quote_line_id);
1727 aso_debug_pub.add('do_check_atp: quote_line_id: '||j.quote_line_id);
1728 aso_debug_pub.add('do_check_atp: item_type_code: '||j.item_type_code);
1729 aso_debug_pub.add('do_check_atp: config_header_id: '||j.config_header_id);
1730 aso_debug_pub.add('do_check_atp: config_revision_num: '||j.config_revision_num);
1731 aso_debug_pub.add('do_check_atp: component_code: '||j.component_code);
1732 aso_debug_pub.add('do_check_atp: config_item_id: '||j.config_item_id);
1733 aso_debug_pub.add('do_check_atp: ref_line_id: '||j.ref_line_id);
1734 aso_debug_pub.add('do_check_atp: ato_line_id: '||j.ato_line_id);
1735 aso_debug_pub.add('do_check_atp: top_model_line_id: '||j.top_model_line_id);
1736 aso_debug_pub.add('do_check_atp: component_sequence_id: '||j.component_sequence_id);
1737 aso_debug_pub.add('do_check_atp: ship_model_complete_flag: '||j.ship_model_complete_flag);
1738 end if;
1739
1740 if j.config_header_id is not null then
1741
1742 if aso_debug_pub.g_debug_flag = 'Y' then
1743 aso_debug_pub.add('j.config_header_id is not null. This is a configuration line.');
1744 end if;
1745
1746 if nvl(j.ship_model_complete_flag, 'N') = 'Y' then
1747 l_atp_rec.ship_set_name(i) := j.top_model_line_id;
1748 end if;
1749
1750 l_atp_rec.ato_model_line_id(i) := j.ato_line_id;
1751 l_atp_rec.top_model_line_id(i) := j.top_model_line_id;
1752 l_atp_rec.parent_line_id(i) := j.ref_line_id;
1753 l_atp_rec.component_code(i) := j.component_code;
1754 l_atp_rec.component_sequence_id(i) := j.component_sequence_id;
1755
1756 end if;
1757
1758 end loop; -- End of c_config_dtl cursor loop
1759
1760 End Loop; -- End of l_qte_line_tbl loop
1761
1762
1763 --Print the input parameters to MRP call_atp procedure
1764
1765 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1766
1767 aso_debug_pub.add('After the qte line tbl loop ');
1768
1769 if l_atp_rec.identifier IS NOT NULL then
1770
1771 for i in l_atp_rec.identifier.FIRST .. l_atp_rec.identifier.LAST loop
1772
1773 if l_atp_rec.identifier.EXISTS(i) then
1774
1775 aso_debug_pub.add('do_check_atp: l_atp_rec.identifier('||i||'): '|| l_atp_rec.identifier(i));
1776 aso_debug_pub.add('do_check_atp: l_atp_rec.parent_line_id('||i||'): '|| l_atp_rec.parent_line_id(i));
1777 aso_debug_pub.add('do_check_atp: l_atp_rec.top_model_line_id('||i||'): '|| l_atp_rec.top_model_line_id(i));
1778 aso_debug_pub.add('do_check_atp: l_atp_rec.ato_model_line_id('||i||'): '|| l_atp_rec.ato_model_line_id(i));
1779 aso_debug_pub.add('do_check_atp: l_atp_rec.component_code('||i||'): '|| l_atp_rec.component_code(i));
1780 aso_debug_pub.add('do_check_atp: l_atp_rec.component_sequence_id('||i||'): '|| l_atp_rec.component_sequence_id(i));
1781 aso_debug_pub.add('do_check_atp: l_atp_rec.ship_set_name('||i||'): '|| l_atp_rec.ship_set_name(i));
1782 aso_debug_pub.add('do_check_atp: l_atp_rec.action('||i||'): '|| l_atp_rec.action(i));
1783 aso_debug_pub.add('do_check_atp: l_atp_rec.calling_module('||i||'): '|| l_atp_rec.calling_module(i));
1787 aso_debug_pub.add('do_check_atp: l_atp_rec.quantity_uom('||i||'): '|| l_atp_rec.quantity_uom(i));
1784 aso_debug_pub.add('do_check_atp: l_atp_rec.inventory_item_id('||i||'): '|| l_atp_rec.inventory_item_id(i));
1785 aso_debug_pub.add('do_check_atp: l_atp_rec.validation_org('||i||'): '|| l_atp_rec.validation_org(i));
1786 aso_debug_pub.add('do_check_atp: l_atp_rec.quantity_ordered('||i||'): '|| l_atp_rec.quantity_ordered(i));
1788 aso_debug_pub.add('do_check_atp: l_atp_rec.demand_source_header_id('||i||'): '|| l_atp_rec.demand_source_header_id(i));
1789 aso_debug_pub.add('do_check_atp: l_atp_rec.customer_id('||i||'): '|| l_atp_rec.customer_id(i));
1790 aso_debug_pub.add('do_check_atp: l_atp_rec.customer_site_id('||i||'): '|| l_atp_rec.customer_site_id(i));
1791 aso_debug_pub.add('do_check_atp: l_atp_rec.ship_method('||i||'): '|| l_atp_rec.ship_method(i));
1792 aso_debug_pub.add('do_check_atp: l_atp_rec.order_number('||i||'): '|| l_atp_rec.order_number(i));
1793 aso_debug_pub.add('do_check_atp: l_atp_rec.source_organization_id('||i||'): '|| l_atp_rec.source_organization_id(i));
1794 aso_debug_pub.add('do_check_atp: l_atp_rec.demand_class('||i||'): '|| l_atp_rec.demand_class(i));
1795 aso_debug_pub.add('do_check_atp: l_atp_rec.requested_arrival_date('||i||'): '|| l_atp_rec.requested_arrival_date(i));
1796 aso_debug_pub.add('do_check_atp: l_atp_rec.requested_ship_date('||i||'): '|| l_atp_rec.requested_ship_date(i));
1797 aso_debug_pub.add('do_check_atp: l_atp_rec.customer_country('||i||'): '|| l_atp_rec.customer_country(i));
1798 aso_debug_pub.add('do_check_atp: l_atp_rec.customer_state('||i||'): '|| l_atp_rec.customer_state(i));
1799 aso_debug_pub.add('do_check_atp: l_atp_rec.customer_city('||i||'): '|| l_atp_rec.customer_city(i));
1800 aso_debug_pub.add('do_check_atp: l_atp_rec.customer_postal_code('||i||'): '|| l_atp_rec.customer_postal_code(i));
1801 end if;
1802
1803 end loop;
1804
1805 end if;
1806
1807 END IF; -- end if for debug
1808
1809
1810 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1811 aso_debug_pub.add('do_check_atp: Before call to msc_atp_global.get_atp_session_id procedure',1,'Y');
1812 END IF;
1813
1814 MSC_ATP_GLOBAL.Get_ATP_Session_Id(l_session_id, x_return_status);
1815
1816 if x_return_status <> fnd_api.g_ret_sts_success then
1817 raise fnd_api.g_exc_unexpected_error;
1818 end if;
1819
1820 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1821 aso_debug_pub.add('do_check_atp: After call to msc_atp_global.get_atp_session_id procedure',1,'Y');
1822 aso_debug_pub.add('do_check_atp: Before call to mrp_atp_pub.call_atp procedure',1,'Y');
1823 END IF;
1824
1825 MRP_ATP_PUB.CALL_ATP(l_session_id,
1826 l_atp_rec,
1827 l_atp_rec_out,
1828 l_atp_supply_demand,
1829 l_atp_period,
1830 l_atp_details,
1831 x_return_status,
1832 x_new_msg_data,
1833 x_msg_count);
1834
1835 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1836 aso_debug_pub.add('do_check_atp: After call to mrp_atp_pub.call_atp procedure',1,'Y');
1837 aso_debug_pub.add('do_check_atp: x_return_status: '|| x_return_status ,1,'Y');
1838 aso_debug_pub.add('Error msg: '||x_new_msg_data,1,'Y');
1839 END IF;
1840
1841 if x_return_status <> fnd_api.g_ret_sts_success then
1842 raise fnd_api.g_exc_unexpected_error;
1843 end if;
1844
1845
1846
1847 /* added new debug messages */
1848
1849 if aso_debug_pub.g_debug_flag = 'Y' then
1850
1851 aso_debug_pub.add('do_check_atp: Printing the atp_rec_out ',1,'Y');
1852
1853 if l_atp_rec_out.inventory_item_id IS NOT NULL then
1854
1855 for i in l_atp_rec_out.inventory_item_id.FIRST .. l_atp_rec_out.inventory_item_id.LAST loop
1856
1857 if l_atp_rec_out.inventory_item_id.EXISTS(i) then
1858 aso_debug_pub.add('do_check_atp: l_atp_rec_out.inventory_item_id '|| l_atp_rec_out.inventory_item_id(i),1,'Y');
1859 end if;
1860
1861 if l_atp_rec_out.identifier.EXISTS(i) then
1862 aso_debug_pub.add('do_check_atp: l_atp_rec_out.identifier '|| l_atp_rec_out.identifier(i),1,'Y');
1863 end if;
1864
1865 if l_atp_rec_out.Error_Code.EXISTS(i) then
1866 aso_debug_pub.add('do_check_atp: l_atp_rec_out.Error_Code '|| l_atp_rec_out.Error_Code(i),1,'Y');
1867 end if;
1868
1869 end loop;
1870
1871 end if;
1872
1873 end if; -- checking the debug flag if
1874
1875 /* end of new debug messages */
1876
1877 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1878 aso_debug_pub.add('do_check_atp:Before calling populate_output_table' );
1879 END IF;
1880
1881 populate_output_table(l_atp_rec_out,x_atp_tbl,x_return_status);
1882
1883 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1884 aso_debug_pub.add('do_check_atp:After calling populate_output_table: x_return_status: '|| x_return_status );
1885 END IF;
1886
1887 -- Logic for ordering the output by UI Line Number
1888 /* for i in 1..x_atp_tbl.count loop
1889 l_search_tbl(x_atp_tbl(i).Identifier) := i;
1890 end loop;
1891
1892 open get_ordered_lines(p_qte_header_rec.quote_header_id);
1893 loop
1894 fetch get_ordered_lines into l_qte_line_id_from_bali;
1898 end if;
1895 exit when get_ordered_lines%NOTFOUND;
1896 if l_search_tbl.exists(l_qte_line_id_from_bali) then
1897 x_new_atp_tbl(x_new_atp_tbl.count + 1) := x_atp_tbl( l_search_tbl(l_qte_line_id_from_bali) );
1899 end loop;
1900 Close get_ordered_lines;
1901
1902 --x_atp_tbl := null;
1903 x_atp_tbl := x_new_atp_tbl; */
1904 -- End Logic for Ordering Output
1905
1906 /*
1907 if l_check_atp_for_whole_quote = fnd_api.g_false then
1908
1909 for i in 1..p_qte_line_tbl.count loop
1910
1911 l_aso_atp_tbl(i) := x_atp_tbl(i);
1912
1913 end loop;
1914
1915 x_atp_tbl := l_aso_atp_tbl;
1916
1917 end if;
1918 */
1919
1920
1921 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1922 aso_debug_pub.add('do_check_atp: END' );
1923 END IF;
1924
1925 EXCEPTION
1926
1927 WHEN FND_API.G_EXC_ERROR THEN
1928 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1929 P_API_NAME => L_API_NAME
1930 ,P_PKG_NAME => G_PKG_NAME
1931 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1932 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
1933 ,X_MSG_COUNT => X_MSG_COUNT
1934 ,X_MSG_DATA => X_MSG_DATA
1935 ,X_RETURN_STATUS => X_RETURN_STATUS);
1936
1937 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1938 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1939 P_API_NAME => L_API_NAME
1940 ,P_PKG_NAME => G_PKG_NAME
1941 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1942 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
1943 ,X_MSG_COUNT => X_MSG_COUNT
1944 ,X_MSG_DATA => X_MSG_DATA
1945 ,X_RETURN_STATUS => X_RETURN_STATUS);
1946
1947 WHEN OTHERS THEN
1948 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1949 P_API_NAME => L_API_NAME
1950 ,P_PKG_NAME => G_PKG_NAME
1951 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1952 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
1953 ,P_SQLCODE => SQLCODE
1954 ,P_SQLERRM => SQLERRM
1955 ,X_MSG_COUNT => X_MSG_COUNT
1956 ,X_MSG_DATA => X_MSG_DATA
1957 ,X_RETURN_STATUS => X_RETURN_STATUS);
1958
1959 END do_check_atp;
1960
1961
1962
1963 PROCEDURE Check_ATP(
1964 P_Api_Version_Number IN NUMBER,
1965 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1966 p_qte_header_rec IN ASO_QUOTE_PUB.QTE_HEADER_REC_TYPE,
1967 p_qte_line_tbl IN ASO_QUOTE_PUB.qte_line_tbl_type := ASO_QUOTE_PUB.G_MISS_QTE_LINE_TBL,
1968 p_shipment_tbl IN ASO_QUOTE_PUB.shipment_tbl_type := ASO_QUOTE_PUB.G_MISS_SHIPMENT_TBL,
1969 p_entire_quote_flag IN VARCHAR2 := 'N',
1970 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1971 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1972 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1973 X_atp_tbl OUT NOCOPY /* file.sql.39 change */ aso_atp_int.atp_tbl_typ)
1974 AS
1975 l_api_name CONSTANT VARCHAR2(30) := 'Check_ATP' ;
1976 l_api_version_number CONSTANT NUMBER := 1.0;
1977 l_session_id number;
1978 l_sysdate date;
1979 l_atp_rec mrp_atp_pub.atp_rec_typ;
1980 l_atp_rec_out mrp_atp_pub.atp_rec_typ;
1981 l_atp_supply_demand mrp_atp_pub.atp_supply_demand_typ;
1982 l_atp_period mrp_atp_pub.atp_period_typ;
1983 l_atp_details mrp_atp_pub.atp_details_typ;
1984 l_null_aso_atp_typ aso_atp_int.atp_rec_typ;
1985 l_mrp_database_link Varchar2(128);
1986 l_statement Varchar2(500);
1987 l_ship_from_org_id Number ;
1988 l_profile_name Varchar2(240);
1989 l_customer_id NUMBER;
1990 l_cust_ship_site_id NUMBER;
1991 l_mrp_customer_id NUMBER;
1992 l_mrp_ship_site_id NUMBER;
1993 l_use_sourcing_rule VARCHAR2(10);
1994 l_file VARCHAR2(200);
1995
1996 -- Cursor to check whether the customer exists in MRP collection.
1997 CURSOR mrp_cust(p_customer_id NUMBER) is SELECT TP_ID
1998 FROM msc_tp_id_lid tp
1999 WHERE tp.SR_TP_ID = p_customer_id
2000 AND tp.PARTNER_TYPE = 2;
2001
2002 -- Cursor to check whether ship to site exists in MRP Collection.
2003 CURSOR mrp_ship_site(p_customer_site_id NUMBER) IS
2004 SELECT TP_SITE_ID
2005 FROM msc_tp_site_id_lid tpsite
2006 WHERE tpsite.SR_TP_SITE_ID = p_customer_site_id
2007 AND tpsite.PARTNER_TYPE = 2;
2008
2009 l_aps_version number;
2010
2011 BEGIN
2012 -- Standard Start of API savepoint
2013 SAVEPOINT CHECK_ATP_INT;
2014
2015 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
2016
2017 -- Standard call to check for call compatibility.
2018 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2019 p_api_version_number,
2020 l_api_name,
2021 G_PKG_NAME)
2022 THEN
2023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2024 END IF;
2025
2026
2027 -- Initialize message list if p_init_msg_list is set to TRUE.
2028 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2029 FND_MSG_PUB.initialize;
2033 If p_qte_line_tbl.FIRST IS NULL or p_shipment_tbl.FIRST IS NULL Then
2030 END IF;
2031
2032 /*
2034 Return;
2035 End IF;
2036 */
2037
2038 l_aps_version := MSC_ATP_GLOBAL.Get_APS_Version;
2039
2040 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2041 aso_debug_pub.add('Check_atp : l_aps_version: '|| l_aps_version,1,'Y');
2042 aso_debug_pub.add('Check_atp : use sourcing rule is '||l_use_sourcing_rule,1,'Y');
2043 END IF;
2044
2045 if l_aps_version = 10 then
2046
2047 do_check_atp(
2048 P_Api_Version_Number => 1.0,
2049 P_Init_Msg_List => FND_API.G_FALSE,
2050 p_qte_header_rec => p_qte_header_rec,
2051 p_qte_line_tbl => p_qte_line_tbl,
2052 p_shipment_tbl => p_shipment_tbl,
2053 p_entire_quote_flag => p_entire_quote_flag,
2054 x_return_status => x_return_status,
2055 x_msg_count => x_msg_count,
2056 x_msg_data => x_msg_data,
2057 x_atp_tbl => x_atp_tbl);
2058
2059 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2060 aso_debug_pub.add('Check_atp :After calling do_check_atp',1,'Y');
2061 aso_debug_pub.add('Check_atp :x_return_status: '|| x_return_status ,1,'Y');
2062 END IF;
2063
2064
2065 if x_return_status = FND_API.G_RET_STS_ERROR then
2066 RAISE FND_API.G_EXC_ERROR;
2067 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2068 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2069 end if;
2070
2071 else
2072
2073
2074 Select MRP_ATP_SCHEDULE_TEMP_S.NextVal
2075 Into l_session_id
2076 From Dual;
2077
2078 /* Get profile value for ASO: ATP Use Sourcing Rules. If the value is null or 'N
2079 ' then get l_ship_from_org_id from the profile ASO_SHIP_FROM_ORG_ID and pass the
2080 source_organization_id as the value of l_ship_from_org_id else get Default Custo
2081 mer and site id for ATP and pass Source Organization id as null and a combinatio
2082 n of customer and site id so that mrp api can use sourcing rules */
2083
2084 l_use_sourcing_rule := fnd_profile.value(name => 'ASO_ATP_USE_SOURCING_RULE');
2085
2086 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2087 aso_debug_pub.add('Check_atp : use sourcing rule is '||l_use_sourcing_rule,1,'Y');
2088 END IF;
2089
2090 IF l_use_sourcing_rule IS NULL OR l_use_sourcing_rule = 'N' THEN
2091
2092 -- Get the value for Ship from org Id.
2093 l_ship_from_org_id := fnd_profile.value(name => 'ASO_SHIP_FROM_ORG_ID');
2094 If l_ship_from_org_id IS NULL Then
2095 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2096 select user_profile_option_name
2097 into l_profile_name
2098 from fnd_profile_options_vl
2099 where profile_option_name = 'ASO_SHIP_FROM_ORG_ID';
2100
2101 FND_MESSAGE.Set_Name('ASO', 'ASO_API_NO_PROFILE_VALUE');
2102 fnd_message.set_token('PROFILE', l_profile_name);
2103 FND_MSG_PUB.ADD;
2104 END IF;
2105 raise FND_API.G_EXC_ERROR;
2106 End If;
2107
2108 ELSE
2109 -- Sourcing rule is 'Y' so get the default customer and site id.
2110
2111 -- To get the Default Customer Id from profile ASO: Atp Default Customer
2112 l_customer_id := fnd_profile.value(name => 'ASO_ATP_DEFAULT_CUSTOMER_ID');
2113
2114 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2115 aso_debug_pub.add('Check_atp: Default Customer Id from profile'||l_customer_id,1,'Y');
2116 END IF;
2117
2118 IF l_customer_id IS NULL Then
2119 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2120 select user_profile_option_name
2121 into l_profile_name
2122 from fnd_profile_options_vl
2123 where profile_option_name = 'ASO_ATP_DEFAULT_CUSTOMER_ID';
2124
2125 FND_MESSAGE.Set_Name('ASO', 'ASO_API_NO_PROFILE_VALUE');
2126 fnd_message.set_token('PROFILE', l_profile_name);
2127 FND_MSG_PUB.ADD;
2128 END IF;
2129 raise FND_API.G_EXC_ERROR;
2130 END IF;
2131
2132
2133 -- To get the Default Customer Ship to site Id from profile ASO: Atp Default SHip to Site id.
2134
2135 l_cust_ship_site_id:= fnd_profile.value(name => 'ASO_ATP_SHIP_TO_SITE_ID');
2136
2137 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2138 aso_debug_pub.add('Check_atp : Default Customer Site Id from profile'||l_cust_ship_site_id);
2139 END IF;
2140
2141 IF l_cust_ship_site_id IS NULL Then
2142 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2143 select user_profile_option_name
2144 into l_profile_name
2145 from fnd_profile_options_vl
2146 where profile_option_name = 'ASO_ATP_SHIP_TO_SITE_ID';
2147
2148 FND_MESSAGE.Set_Name('ASO', 'ASO_API_NO_PROFILE_VALUE');
2149 fnd_message.set_token('PROFILE', l_profile_name);
2150 FND_MSG_PUB.ADD;
2151 END IF;
2152 raise FND_API.G_EXC_ERROR;
2153 END IF;
2154
2155 END IF;
2156
2157 For curr_index IN p_qte_line_tbl.FIRST .. p_qte_line_tbl.LAST Loop
2158 If p_qte_line_tbl.EXISTS(curr_index) Then
2159 Extend_ATP(l_atp_rec, x_return_status);
2160
2161 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2162 aso_debug_pub.add('Check_Atp : Inventory Id for Atp is '||p_qte_line_tbl(curr_index).inventory_item_id);
2163 END IF;
2164
2165 l_atp_rec.inventory_item_id(curr_index) := p_qte_line_tbl(curr_index).inventory_item_id;
2166
2167
2168 IF l_use_sourcing_rule IS NULL OR l_use_sourcing_rule = 'N' THEN
2172 -- multi-org ATP
2169 l_atp_rec.source_organization_id(curr_index) := l_ship_from_org_id;
2170 ELSE
2171 -- Always pass null for source_organization_id. This will enable
2173 l_atp_rec.source_organization_id(curr_index) := null;
2174 END IF;
2175
2176 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2177 aso_debug_pub.add('Check_atp : Source Organization ID is '||l_atp_rec.source_organization_id(curr_index),1,'Y');
2178 aso_debug_pub.add('Check_atp : Organization ID is '||p_qte_line_tbl(curr_index).organization_id,1,'Y');
2179 END IF;
2180
2181 l_atp_rec.organization_id(curr_index) :=
2182 p_qte_line_tbl(curr_index).organization_id;
2183 l_atp_rec.identifier(curr_index) :=
2184 p_qte_line_tbl(curr_index).quote_line_id;
2185 l_atp_rec.quantity_ordered(curr_index) :=
2186 p_qte_line_tbl(curr_index).quantity;
2187 l_atp_rec.quantity_uom(curr_index) :=
2188 p_qte_line_tbl(curr_index).uom_code ;
2189
2190 If p_shipment_tbl.EXISTS(curr_index) Then
2191 l_atp_rec.requested_ship_date(curr_index) :=
2192 p_shipment_tbl(curr_index).request_date;
2193
2194 if p_shipment_tbl( curr_index ).ship_from_org_id is null
2195 or p_shipment_tbl( curr_index ).ship_from_org_id = FND_API.G_MISS_NUM then
2196 /* Changes for the Save Warehouse */
2197
2198 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2199 aso_debug_pub.add('Check_atp: Source Organization ID is '||l_atp_rec.source_organization_id(curr_index));
2200 END IF;
2201
2202 /* Need to pass customer id, ship to site id and ship method id only if
2203 the use sourcing level profile is 'Yes' */
2204
2205 IF l_use_sourcing_rule = 'Y' THEN
2206 IF p_shipment_tbl(curr_index).ship_to_cust_account_id is NOT NULL
2207 AND p_shipment_tbl(curr_index).ship_to_party_site_id is NOT NULL
2208 THEN
2209 -- Check whether Customer and site id exists in MRP Collection.
2210
2211 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2212 aso_debug_pub.add('Check_atp :Before checking for customer in mrp collection',1,'Y');
2213 END IF;
2214
2215 open mrp_cust(p_shipment_tbl(curr_index).ship_to_cust_account_id);
2216 fetch mrp_cust into l_mrp_customer_id;
2217 close mrp_cust;
2218
2219 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2220 aso_debug_pub.add('Check_atp :After checking for customer in mrp collection',1,'Y');
2221 END IF;
2222
2223 IF l_mrp_customer_id IS NOT NULL THEN
2224
2225 open mrp_ship_site(p_shipment_tbl(curr_index).ship_to_party_site_id);
2226 fetch mrp_ship_site into l_mrp_ship_site_id;
2227 close mrp_ship_site;
2228
2229 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2230 aso_debug_pub.add('Check_atp :After checking for cust site in mrp collection');
2231 END IF;
2232
2233 IF l_mrp_ship_site_id IS NOT NULL THEN
2234
2235 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2236
2237 aso_debug_pub.add('Check_atp :Real Customer id'||
2238 p_shipment_tbl(curr_index).ship_to_cust_account_id||'
2239 and site id'||p_shipment_tbl(curr_index).ship_to_party_site_id,1,'Y');
2240
2241 END IF;
2242
2243 l_atp_rec.customer_id(curr_index) := p_shipment_tbl(curr_index).ship_to_cust_account_id;
2244 l_atp_rec.customer_site_id(curr_index) := p_shipment_tbl(curr_index).ship_to_party_site_id;
2245 ELSE
2246 -- Pass the default customer and site id from profiles.
2247
2248 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2249 aso_debug_pub.add('Check_atp :no data in MRP, use the default cust id '||l_customer_id ||' and ship site '||l_cust_ship_site_id,1,'Y');
2250 END IF;
2251
2252 l_atp_rec.customer_id(curr_index) := l_customer_id;
2253 l_atp_rec.customer_site_id(curr_index) := l_cust_ship_site_id;
2254
2255 END IF;
2256
2257 END IF;
2258
2259 ELSE
2260 -- Pass the default customer and site id from profiles.
2261 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2262 aso_debug_pub.add('Check_atp :cust and site id are null, use the default cust id '||l_customer_id ||' and ship site '||l_cust_ship_site_id,1,'Y');
2263 END IF;
2264
2265 l_atp_rec.customer_id(curr_index) := l_customer_id;
2266 l_atp_rec.customer_site_id(curr_index) := l_cust_ship_site_id;
2267 END IF;
2268
2269 END IF;-- for sourcing_rule = 'Y'
2270
2271 else
2272
2273 l_atp_rec.Source_Organization_Id(curr_index) := p_shipment_tbl(curr_index).ship_from_org_id;
2274 end if;
2275
2276 l_atp_rec.ship_method(curr_index) := p_shipment_tbl(curr_index).ship_method_code;
2277
2278 End If;
2279
2280 --have to populate action code. waiting for a reply back from mrp.
2281 --for now i have defined a variable in the specification.
2282 l_atp_rec.action(curr_index) := ASO_ATP_INT.ATPQUERY;
2283
2284 -- 02/06/2001 - bug1630636 (ashukla)
2285 -- populate the calling module. This is the application_id for
2286 -- Oracle Oracle Capture.
2287
2288 -- 03/15/2001 - This should be populated from the application id
2289 -- in the environment, using fnd_global
2290
2291 l_atp_rec.calling_module(curr_index) := fnd_global.prog_appl_id;
2292
2293 End If;
2294 End Loop;
2295
2296 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2297 aso_debug_pub.add('Check_atp: Source Organization ID is '||l_atp_rec.source_organization_id(1));
2298 aso_debug_pub.add('Check_atp :Before calling mrp api ',1,'Y');
2299 END IF;
2300
2301 MRP_ATP_PUB.CALL_ATP(l_session_id,
2302 l_atp_rec,
2303 l_atp_rec_out,
2304 l_atp_supply_demand,
2305 l_atp_period,
2306 l_atp_details,
2307 x_return_status,
2308 x_msg_data,
2309 x_msg_count);
2310
2311 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2312 aso_debug_pub.add('Check_atp :After calling mrp api',1,'Y');
2313 aso_debug_pub.add('Check_atp :x_return_status === '|| x_return_status ,1,'Y');
2314 END IF;
2315
2316 If x_return_status <> FND_API.G_RET_STS_SUCCESS Then
2317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2318 End if;
2319
2320 populate_output_table(l_atp_rec_out,x_atp_tbl,x_return_status);
2321
2322 end if;
2323
2324 EXCEPTION
2325 WHEN FND_API.G_EXC_ERROR THEN
2326 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2327 P_API_NAME => L_API_NAME
2328 ,P_PKG_NAME => G_PKG_NAME
2329 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2330 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
2331 ,X_MSG_COUNT => X_MSG_COUNT
2332 ,X_MSG_DATA => X_MSG_DATA
2333 ,X_RETURN_STATUS => X_RETURN_STATUS);
2334
2335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2336 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2337 P_API_NAME => L_API_NAME
2338 ,P_PKG_NAME => G_PKG_NAME
2339 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2340 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
2341 ,X_MSG_COUNT => X_MSG_COUNT
2342 ,X_MSG_DATA => X_MSG_DATA
2343 ,X_RETURN_STATUS => X_RETURN_STATUS);
2344
2345 WHEN OTHERS THEN
2346 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2347 P_API_NAME => L_API_NAME
2348 ,P_PKG_NAME => G_PKG_NAME
2349 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
2350 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
2351 ,P_SQLCODE => SQLCODE
2352 ,P_SQLERRM => SQLERRM
2353 ,X_MSG_COUNT => X_MSG_COUNT
2354 ,X_MSG_DATA => X_MSG_DATA
2355 ,X_RETURN_STATUS => X_RETURN_STATUS);
2356
2357 END Check_ATP;
2358
2359 PROCEDURE Check_ATP(
2360 P_Api_Version_Number IN NUMBER,
2361 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2362 p_qte_line_tbl IN ASO_QUOTE_PUB.qte_line_tbl_type,
2363 p_shipment_tbl IN ASO_QUOTE_PUB.shipment_tbl_type,
2364 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2365 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2366 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2367 X_atp_tbl OUT NOCOPY /* file.sql.39 change */ aso_atp_int.atp_tbl_typ
2368 )
2369 IS
2370
2371 l_qte_header_rec aso_quote_pub.qte_header_rec_type;
2372
2373 Begin
2374 x_return_status := fnd_api.g_ret_sts_success;
2375 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
2376
2377 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2378 aso_debug_pub.add('ASO_ATP_INT: Check_Atp Begin', 1, 'Y');
2379 END IF;
2380
2381 -- Initialize message list if p_init_msg_list is set to TRUE.
2382 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2383 FND_MSG_PUB.initialize;
2384 END IF;
2385
2386 if p_qte_line_tbl.count > 0 then
2387 l_qte_header_rec.quote_header_id := p_qte_line_tbl(1).quote_header_id;
2388 end if;
2389
2390 Check_Atp( P_Api_Version_Number => P_Api_Version_Number,
2391 P_Init_Msg_List => FND_API.G_FALSE,
2392 p_qte_header_rec => l_qte_header_rec,
2393 p_qte_line_tbl => p_qte_line_tbl,
2394 p_shipment_tbl => p_shipment_tbl,
2395 x_return_status => x_return_status,
2396 x_msg_count => x_msg_count,
2397 x_msg_data => x_msg_data,
2398 x_atp_tbl => x_atp_tbl);
2399
2400 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2401 aso_debug_pub.add('Check_atp :After calling check_atp overloaded procedure',1,'Y');
2402 aso_debug_pub.add('Check_atp :x_return_status: '|| x_return_status ,1,'Y');
2403 END IF;
2404
2405 EXCEPTION
2406
2407 WHEN OTHERS THEN
2408
2409 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2410 aso_debug_pub.add('Check_Atp (not overloaded): Inside when others exception', 1, 'N');
2411 END IF;
2412
2413 End Check_ATP;
2414
2415
2416 PROCEDURE update_configuration(
2417 P_Api_Version_Number IN NUMBER,
2418 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2419 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2420 p_qte_header_rec IN ASO_QUOTE_PUB.QTE_HEADER_REC_TYPE,
2421 p_qte_line_dtl_tbl IN ASO_QUOTE_PUB.qte_line_dtl_tbl_type := ASO_QUOTE_PUB.G_MISS_QTE_LINE_DTL_TBL,
2422 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2423 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2424 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2425 )
2426 IS
2427
2428 l_api_name CONSTANT VARCHAR2(30) := 'update_configuration' ;
2429 l_api_version_number CONSTANT NUMBER := 1.0;
2430 l_qte_line_dtl_tbl ASO_QUOTE_PUB.qte_line_dtl_tbl_type := ASO_QUOTE_PUB.G_MISS_QTE_LINE_DTL_TBL;
2431
2432 Cursor get_line_details(l_qte_header_id NUMBER) IS
2433 SELECT b.config_header_id,b.config_revision_num,b.quote_line_id
2434 from aso_quote_lines_all a, aso_quote_line_details b
2435 where a.quote_line_id = b.quote_line_id
2436 and a. quote_header_id = l_qte_header_id
2437 and b.ref_line_id is null
2438 and b.ref_type_code = 'CONFIG';
2439
2440 Cursor get_line_id(l_config_header_id Number,l_config_revision_num Number) IS
2441 select a.quote_line_id
2442 from aso_quote_lines_all a, aso_quote_line_details b
2443 where a.quote_line_id = b.quote_line_id
2444 and b.config_header_id = l_config_header_id
2445 and b.config_revision_num = l_config_revision_num
2446 and ref_type_code = 'CONFIG'
2447 and ref_line_id is null;
2448
2449 Cursor get_no_of_lines(l_qte_header_id NUMBER) IS
2450 select count(a.quote_line_id)
2451 from aso_quote_lines_all a, aso_quote_line_details b
2452 where a.quote_line_id = b.quote_line_id
2453 and a. quote_header_id = l_qte_header_id
2454 and b.ref_line_id is null
2455 and b.ref_type_code = 'CONFIG';
2456
2457
2458 Cursor get_cz_data(l_config_header_id Number,l_config_revision_num Number) IS
2459 select a.ato_config_item_id,b.quote_line_detail_id
2460 from cz_config_details_v a, aso_quote_line_details b
2461 where a.config_hdr_id = b.config_header_id
2462 and a.config_rev_nbr = b.config_revision_num
2463 and b.config_header_id = l_config_header_id
2464 and b.config_revision_num = l_config_revision_num
2465 order by b.bom_sort_order;
2466
2467 Cursor get_ato_line(l_config_header_id Number,l_config_revision_num Number,l_config_item_id Number) IS
2468 select quote_line_id
2469 from aso_quote_line_details
2470 where config_header_id = l_config_header_id
2471 and config_revision_num = l_config_revision_num
2472 and config_item_id = l_config_item_id;
2473
2474 l_config_hdr_id NUMBER;
2475 l_config_rev_nbr NUMBER;
2476 l_qte_line_id NUMBER;
2477 l_count NUMBER;
2478 l_ato_item_id NUMBER;
2479 l_line_detail_id NUMBER;
2480 l_ato_line_id NUMBER;
2481 i INTEGER;
2482 Begin
2483
2484 -- Standard Start of API savepoint
2485 SAVEPOINT UPDATE_CONFIGURATION_INT;
2486
2487 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
2488
2489 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2490 aso_debug_pub.add('update_configuration: Begin');
2491 END IF;
2492
2493 -- Standard call to check for call compatibility.
2494 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2495 p_api_version_number,
2496 l_api_name,
2497 G_PKG_NAME) THEN
2498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2499 END IF;
2500
2501 -- Initialize message list if p_init_msg_list is set to TRUE.
2502 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2503 FND_MSG_PUB.initialize;
2504 END IF;
2505
2506 x_return_status := fnd_api.g_ret_sts_success;
2507 -- check to see if the quote header info is missing
2508 if p_qte_header_rec.quote_header_id is null or p_qte_header_rec.quote_header_id = fnd_api.g_miss_num then
2509
2510 if aso_debug_pub.g_debug_flag = 'Y' THEN
2511 aso_debug_pub.add('p_qte_header_rec.quote_header_id is null');
2512 end if;
2513
2514 if fnd_msg_pub.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2515 FND_MESSAGE.Set_Name('ASO', 'ASO_API_ALL_MISSING_PARAM');
2516 FND_MESSAGE.Set_Token('API_NAME', 'UPDATE_CONFIGURATION', FALSE);
2517 FND_MESSAGE.Set_Token('PARAMETER', 'p_qte_header_rec', FALSE);
2518 FND_MSG_PUB.ADD;
2519 end if;
2520 x_return_status := fnd_api.g_ret_sts_error;
2521 raise fnd_api.g_exc_error;
2522 end if;
2523
2524
2525 if aso_debug_pub.g_debug_flag = 'Y' then
2526 aso_debug_pub.add('update_configuration: p_qte_line_dtl_tbl.count: '|| p_qte_line_dtl_tbl.count, 1, 'Y');
2527 end if;
2528
2529 if p_qte_line_dtl_tbl.count = 0 then
2530
2531 OPEN get_line_details(p_qte_header_rec.quote_header_id);
2532 loop
2533 fetch get_line_details into l_config_hdr_id,l_config_rev_nbr,l_qte_line_id;
2534 exit when get_line_details%NOTFOUND;
2535 i := l_qte_line_dtl_tbl.count + 1;
2536 l_qte_line_dtl_tbl(i).quote_line_id := l_qte_line_id;
2537 l_qte_line_dtl_tbl(i).config_header_id := l_config_hdr_id;
2538 l_qte_line_dtl_tbl(i).config_revision_num := l_config_rev_nbr;
2539 end loop;
2540 close get_line_details;
2541 else
2542 -- check to see if only the model lines are passed or not
2543 open get_no_of_lines(p_qte_header_rec.quote_header_id);
2544 fetch get_no_of_lines into l_count;
2545 close get_no_of_lines;
2546
2547 if p_qte_line_dtl_tbl.count > l_count then
2548 if fnd_msg_pub.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2549 FND_MESSAGE.Set_Name('ASO', 'ASO_API_ALL_MISSING_PARAM');
2550 FND_MESSAGE.Set_Token('API_NAME', 'UPDATE_CONFIGURATION', FALSE);
2551 FND_MESSAGE.Set_Token('PARAMETER', 'p_qte_line_dtl_tbl', FALSE);
2552 FND_MSG_PUB.ADD;
2553 end if;
2554 else
2555 l_qte_line_dtl_tbl := p_qte_line_dtl_tbl;
2556 end if;
2557
2558 end if;
2559
2563
2560 if aso_debug_pub.g_debug_flag = 'Y' then
2561 aso_debug_pub.add('update_configuration: l_qte_line_dtl_tbl.count: '|| l_qte_line_dtl_tbl.count, 1, 'Y');
2562 end if;
2564 for i in 1..l_qte_line_dtl_tbl.count loop
2565
2566 -- check to see if the qte line detail table is properly populated before processing the row
2567 if (((l_qte_line_dtl_tbl(i).config_header_id is null) or (l_qte_line_dtl_tbl(i).config_header_id = FND_API.G_MISS_NUM)) or
2568 ((l_qte_line_dtl_tbl(i).config_revision_num is null) or (l_qte_line_dtl_tbl(i).config_revision_num = FND_API.G_MISS_NUM))) then
2569
2570 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
2571 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_MISSING_COLUMN' );
2572 FND_MESSAGE.Set_Token ('COLUMN' , '.config_header_id', FALSE );
2573 FND_MSG_PUB.ADD;
2574 END IF;
2575 RAISE FND_API.G_EXC_ERROR;
2576 end if;
2577
2578 if (l_qte_line_dtl_tbl(i).quote_line_id is null) or (l_qte_line_dtl_tbl(i).quote_line_id = FND_API.G_MISS_NUM) then
2579 open get_line_id(l_qte_line_dtl_tbl(i).config_header_id,l_qte_line_dtl_tbl(i).config_revision_num);
2580 fetch get_line_id into l_qte_line_dtl_tbl(i).quote_line_id;
2581 close get_line_id;
2582 end if;
2583
2584 open get_cz_data(l_qte_line_dtl_tbl(i).config_header_id,l_qte_line_dtl_tbl(i).config_revision_num);
2585 loop
2586 fetch get_cz_data into l_ato_item_id,l_line_detail_id;
2587 exit when get_cz_data%notfound;
2588
2589 open get_ato_line(l_qte_line_dtl_tbl(i).config_header_id,l_qte_line_dtl_tbl(i).config_revision_num,l_ato_item_id);
2590 fetch get_ato_line into l_ato_line_id;
2591 close get_ato_line;
2592
2593 update aso_quote_line_details
2594 set top_model_line_id = l_qte_line_dtl_tbl(i).quote_line_id,
2595 ato_line_id = nvl(l_ato_line_id,null)
2596 where quote_line_detail_id = l_line_detail_id;
2597 end loop;
2598 close get_cz_data;
2599
2600 end loop;
2601
2602
2603 IF fnd_api.to_boolean (p_commit) THEN
2604 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2605 aso_debug_pub.ADD ('Commiting the work',1,'N');
2606 END IF;
2607 COMMIT WORK;
2608 END IF;
2609
2610 fnd_msg_pub.count_and_get(p_encoded => 'F',
2611 p_count => x_msg_count,
2612 p_data => x_msg_data);
2613 for k in 1..x_msg_count loop
2614 x_msg_data := fnd_msg_pub.get(p_msg_index => k,
2615 p_encoded => 'F');
2616 end loop;
2617
2618 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2619 aso_debug_pub.add('update_configuration: END' );
2620 END IF;
2621
2622
2623 EXCEPTION
2624 WHEN FND_API.G_EXC_ERROR THEN
2625 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2626 P_API_NAME => L_API_NAME
2627 ,P_PKG_NAME => G_PKG_NAME
2628 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2629 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
2630 ,X_MSG_COUNT => X_MSG_COUNT
2631 ,X_MSG_DATA => X_MSG_DATA
2632 ,X_RETURN_STATUS => X_RETURN_STATUS);
2633
2634 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2635 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2636 P_API_NAME => L_API_NAME
2637 ,P_PKG_NAME => G_PKG_NAME
2638 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2639 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
2640 ,X_MSG_COUNT => X_MSG_COUNT
2641 ,X_MSG_DATA => X_MSG_DATA
2642 ,X_RETURN_STATUS => X_RETURN_STATUS);
2643
2644 WHEN OTHERS THEN
2645 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2646 P_API_NAME => L_API_NAME
2647 ,P_PKG_NAME => G_PKG_NAME
2648 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
2649 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
2650 ,P_SQLCODE => SQLCODE
2651 ,P_SQLERRM => SQLERRM
2652 ,X_MSG_COUNT => X_MSG_COUNT
2653 ,X_MSG_DATA => X_MSG_DATA
2654 ,X_RETURN_STATUS => X_RETURN_STATUS);
2655
2656 End update_configuration;
2657
2658 End aso_atp_int;
[Home] [Help]