[Home] [Help]
PACKAGE BODY: APPS.BOM_ATO_NEW_ATP_PK
Source
1 package body BOM_ATO_NEW_ATP_PK as
2 /* $Header: BOMNATPB.pls 115.0 99/08/18 12:49:59 porting shi $ */
3
4 function config_link_atp(
5 RTOMLine in number,
6 dSrcHdrId in number,
7 dSrcType in number,
8 OrgId in number,
9 error_message out varchar2,
10 message_name out varchar2)
11
12 return integer is
13
14 p_atp_table MRP_ATP_PUB.ATP_Rec_Typ;
15 p_instance_id integer := -1;
16 p_session_id number := 101;
17 x_atp_table MRP_ATP_PUB.ATP_Rec_Typ;
18 x_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
19 x_atp_period MRP_ATP_PUB.ATP_Period_Typ;
20 x_atp_details MRP_ATP_PUB.ATP_Details_Typ;
21 x_return_status VARCHAR2(1);
22 x_msg_count number;
23 x_msg_data varchar2(200);
24
25 temp number := null;
26 temp1 date := null;
27 stmt number;
28 atp_error exception;
29 begin
30
31 /*---------------------------------------------------------------+
32 We need to inform ATP to modify demand (quanatity zero) for
33 the rows that have been deactivated in mtl_demand by the Schedule
34 function, as part of 'link Config' action
35 +----------------------------------------------------------------*/
36 select inventory_item_id,
37 organization_id,
38 demand_id,
39 primary_uom_quantity,
40 uom_code,
41 requirement_date,
42 demand_class,
43 temp, -- calling module
44 temp, -- customer_id
45 temp, -- customer_site_id
46 temp, -- destination_time_zone
47 temp1, -- requested arrival_date
48 temp1, -- latest acceptable_date
49 temp, -- delivery lead time
50 temp, -- Freight_Carrier
51 temp, -- Ship_Method
52 temp, --Ship_Set_Name
53 temp, -- Arrival_Set_Name
54 temp, -- Override_Flag
55 temp, -- Action
56 temp1, -- Ship_date
57 temp, -- available_quantity
58 temp, -- requested_date_quantity
59 temp1, -- group_ship_date
60 temp1, -- group_arrival_date
61 temp, -- vendor_id
62 temp, -- vendor_site_id
63 temp, -- insert_flag
64 temp, -- error_code
65 temp -- Message
66 bulk collect into
67 p_atp_table.Inventory_Item_Id ,
68 p_atp_table.Source_Organization_Id ,
69 p_atp_table.Identifier ,
70 p_atp_table.Quantity_Ordered ,
71 p_atp_table.Quantity_UOM ,
72 p_atp_table.Requested_Ship_Date ,
73 p_atp_table.Demand_Class ,
74 p_atp_table.Calling_Module ,
75 p_atp_table.Customer_Id ,
76 p_atp_table.Customer_Site_Id ,
77 p_atp_table.Destination_Time_Zone ,
78 p_atp_table.Requested_Arrival_Date ,
79 p_atp_table.Latest_Acceptable_Date ,
80 p_atp_table.Delivery_Lead_Time ,
81 p_atp_table.Freight_Carrier ,
82 p_atp_table.Ship_Method ,
83 p_atp_table.Ship_Set_Name ,
84 p_atp_table.Arrival_Set_Name ,
85 p_atp_table.Override_Flag ,
86 p_atp_table.Action ,
87 p_atp_table.Ship_Date ,
88 p_atp_table.Available_Quantity ,
89 p_atp_table.Requested_Date_Quantity ,
90 p_atp_table.Group_Ship_Date ,
91 p_atp_table.Group_Arrival_Date ,
92 p_atp_table.Vendor_Id ,
93 p_atp_table.Vendor_Site_Id ,
94 p_atp_table.Insert_Flag ,
95 p_atp_table.Error_Code ,
96 p_atp_table.Message
97 from mtl_demand md
98 where md.RTO_MODEL_SOURCE_LINE = RTOMLine
99 and md.demand_source_header_id = dSrcHdrId
100 and md.demand_source_type = dSrcType
101 and md.organization_id = OrgId
102 and md.primary_uom_quantity > 0
103 and md.config_status = 80
104 and md.row_status_flag = 2
105 and md.demand_type not in (1,2);
106
107 /*---------------------------------------------------------------+
108 We also need to inform ATP to add demand (quanatity zero) for
109 the mandatory components that have been inserted (derived demand)
110 in mtl_demand by the Schedule function, as part of
111 'link Config' action
112 +----------------------------------------------------------------*/
113
114 select inventory_item_id,
115 organization_id,
116 demand_id,
117 primary_uom_quantity,
118 uom_code,
119 requirement_date,
120 demand_class,
121 temp, -- calling module
122 temp, -- customer_id
123 temp, -- customer_site_id
124 temp, -- destination_time_zone
125 temp1, -- requested arrival_date
126 temp1, -- latest acceptable_date
127 temp, -- delivery lead time
128 temp, -- Freight_Carrier
129 temp, -- Ship_Method
130 temp, --Ship_Set_Name
131 temp, -- Arrival_Set_Name
132 temp, -- Override_Flag
133 temp, -- Action
134 temp1, -- Ship_date
135 temp, -- available_quantity
136 temp, -- requested_date_quantity
137 temp1, -- group_ship_date
138 temp1, -- group_arrival_date
139 temp, -- vendor_id
140 temp, -- vendor_site_id
141 temp, -- insert_flag
142 temp, -- error_code
143 temp -- Message
144 bulk collect into
145 p_atp_table.Inventory_Item_Id ,
146 p_atp_table.Source_Organization_Id ,
147 p_atp_table.Identifier ,
148 p_atp_table.Quantity_Ordered ,
149 p_atp_table.Quantity_UOM ,
150 p_atp_table.Requested_Ship_Date ,
151 p_atp_table.Demand_Class ,
152 p_atp_table.Calling_Module ,
153 p_atp_table.Customer_Id ,
154 p_atp_table.Customer_Site_Id ,
155 p_atp_table.Destination_Time_Zone ,
156 p_atp_table.Requested_Arrival_Date ,
157 p_atp_table.Latest_Acceptable_Date ,
158 p_atp_table.Delivery_Lead_Time ,
159 p_atp_table.Freight_Carrier ,
160 p_atp_table.Ship_Method ,
161 p_atp_table.Ship_Set_Name ,
162 p_atp_table.Arrival_Set_Name ,
163 p_atp_table.Override_Flag ,
164 p_atp_table.Action ,
165 p_atp_table.Ship_Date ,
166 p_atp_table.Available_Quantity ,
167 p_atp_table.Requested_Date_Quantity ,
168 p_atp_table.Group_Ship_Date ,
169 p_atp_table.Group_Arrival_Date ,
170 p_atp_table.Vendor_Id ,
171 p_atp_table.Vendor_Site_Id ,
172 p_atp_table.Insert_Flag ,
173 p_atp_table.Error_Code ,
174 p_atp_table.Message
175 from mtl_demand md
176 where md.RTO_MODEL_SOURCE_LINE = RTOMLine
177 and md.DEMAND_SOURCE_HEADER_ID = dSrcHdrId
178 and md.DEMAND_SOURCE_TYPE = dSrcType
179 and md.ORGANIZATION_ID = OrgId
180 and md.PRIMARY_UOM_QUANTITY > 0
181 and md.config_status =20
182 and md.demand_type in (4,5)
183 and md.row_status_flag = 1
184 and md.parent_demand_id is null;
185
186 -- call atp module
187
188 MRP_ATP_PUB.Call_ATP(
189 p_session_id,
190 p_atp_table,
191 x_atp_table,
192 x_atp_supply_demand,
193 x_atp_period,
194 x_atp_details,
195 x_return_status,
196 x_msg_data,
197 x_msg_count);
198
199 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
200 or x_return_status = FND_API.G_RET_STS_ERROR ) then
201 raise atp_error;
202 else
203 return (1);
204 END IF;
205
206 exception
207
208 when atp_error then
209 error_message := 'BOMNATPB:'||to_char(stmt)||':'||' ATP API returned Error';
210 message_name := 'BOM_ATO_LINK_ERROR';
211 return(0);
212
213 when others then
214 error_message := 'BOMNATPB:'||to_char(stmt)||':'||substrb(sqlerrm,1,150);
215 message_name := 'BOM_ATO_LINK_ERROR';
216 return(0);
217
218 end config_link_atp;
219
220 function config_delink_atp (
221 RTOMLine in number,
222 dSrcHdrId in number,
223 dSrcType in number,
224 OrgId in number,
225 error_message out varchar2,
226 message_name out varchar2)
227 return integer is
228
229 p_atp_table MRP_ATP_PUB.ATP_Rec_Typ;
230 p_instance_id integer := -1;
231 p_session_id number := 101;
232 x_atp_table MRP_ATP_PUB.ATP_Rec_Typ;
233 x_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
234 x_atp_period MRP_ATP_PUB.ATP_Period_Typ;
235 x_atp_details MRP_ATP_PUB.ATP_Details_Typ;
236 x_return_status VARCHAR2(1);
237 x_msg_count number;
238 x_msg_data varchar2(200);
239
240 temp number := null;
241 temp1 date := null;
242 stmt number;
243 atp_error exception;
244
245 begin
246
247
248 /*---------------------------------------------------------------+
249 We need to inform ATP to modify demand (quanatity zero) for
250 the mandatory components (derived demand) in mtl_demand
251 that will be deleted by the Schedule function, as part of
252 'delink Config' action
253 +----------------------------------------------------------------*/
254 stmt := 10;
255 select inventory_item_id,
256 organization_id,
257 demand_id,
258 primary_uom_quantity,
259 uom_code,
260 requirement_date,
261 demand_class,
262 temp, -- calling module
263 temp, -- customer_id
264 temp, -- customer_site_id
265 temp, -- destination_time_zone
266 temp1, -- requested arrival_date
267 temp1, -- latest acceptable_date
268 temp, -- delivery lead time
269 temp, -- Freight_Carrier
270 temp, -- Ship_Method
271 temp, --Ship_Set_Name
272 temp, -- Arrival_Set_Name
273 temp, -- Override_Flag
274 temp, -- Action
275 temp1, -- Ship_date
276 temp, -- available_quantity
277 temp, -- requested_date_quantity
278 temp1, -- group_ship_date
279 temp1, -- group_arrival_date
280 temp, -- vendor_id
281 temp, -- vendor_site_id
282 temp, -- insert_flag
283 temp, -- error_code
284 temp -- Message
285 bulk collect into
286 p_atp_table.Inventory_Item_Id ,
287 p_atp_table.Source_Organization_Id ,
288 p_atp_table.Identifier ,
289 p_atp_table.Quantity_Ordered ,
290 p_atp_table.Quantity_UOM ,
291 p_atp_table.Requested_Ship_Date ,
292 p_atp_table.Demand_Class ,
293 p_atp_table.Calling_Module ,
294 p_atp_table.Customer_Id ,
295 p_atp_table.Customer_Site_Id ,
296 p_atp_table.Destination_Time_Zone ,
297 p_atp_table.Requested_Arrival_Date ,
298 p_atp_table.Latest_Acceptable_Date ,
299 p_atp_table.Delivery_Lead_Time ,
300 p_atp_table.Freight_Carrier ,
301 p_atp_table.Ship_Method ,
302 p_atp_table.Ship_Set_Name ,
303 p_atp_table.Arrival_Set_Name ,
304 p_atp_table.Override_Flag ,
305 p_atp_table.Action ,
306 p_atp_table.Ship_Date ,
307 p_atp_table.Available_Quantity ,
308 p_atp_table.Requested_Date_Quantity ,
309 p_atp_table.Group_Ship_Date ,
310 p_atp_table.Group_Arrival_Date ,
311 p_atp_table.Vendor_Id ,
312 p_atp_table.Vendor_Site_Id ,
313 p_atp_table.Insert_Flag ,
314 p_atp_table.Error_Code ,
315 p_atp_table.Message
316 from mtl_demand md
317 where md.RTO_MODEL_SOURCE_LINE = RTOMLine
318 and md.demand_source_header_id = dSrcHdrId
319 and md.demand_source_type = dSrcType
320 and md.organization_id = OrgId
321 and md.config_status = 20
322 and md.row_status_flag = 1
323 and md.parent_demand_id is null
324 and md.primary_uom_quantity > 0
325 and md.demand_type in (4,5);
326
327 /*---------------------------------------------------------------+
328 We also need to inform ATP to add demand for
329 the model components rows in mtl_demand that will be re-activated
330 by the schedule function, as part of 'delink Config' action
331 +----------------------------------------------------------------*/
332
333 select inventory_item_id,
334 organization_id,
335 demand_id,
336 primary_uom_quantity,
337 uom_code,
338 requirement_date,
339 demand_class,
340 temp, -- calling module
341 temp, -- customer_id
342 temp, -- customer_site_id
343 temp, -- destination_time_zone
344 temp1, -- requested arrival_date
348 temp, -- Ship_Method
345 temp1, -- latest acceptable_date
346 temp, -- delivery lead time
347 temp, -- Freight_Carrier
349 temp, --Ship_Set_Name
350 temp, -- Arrival_Set_Name
351 temp, -- Override_Flag
352 temp, -- Action
353 temp1, -- Ship_date
354 temp, -- available_quantity
355 temp, -- requested_date_quantity
356 temp1, -- group_ship_date
357 temp1, -- group_arrival_date
358 temp, -- vendor_id
359 temp, -- vendor_site_id
360 temp, -- insert_flag
361 temp, -- error_code
362 temp -- Message
363 bulk collect into
364 p_atp_table.Inventory_Item_Id ,
365 p_atp_table.Source_Organization_Id ,
366 p_atp_table.Identifier ,
367 p_atp_table.Quantity_Ordered ,
368 p_atp_table.Quantity_UOM ,
369 p_atp_table.Requested_Ship_Date ,
370 p_atp_table.Demand_Class ,
371 p_atp_table.Calling_Module ,
372 p_atp_table.Customer_Id ,
373 p_atp_table.Customer_Site_Id ,
374 p_atp_table.Destination_Time_Zone ,
375 p_atp_table.Requested_Arrival_Date ,
376 p_atp_table.Latest_Acceptable_Date ,
377 p_atp_table.Delivery_Lead_Time ,
378 p_atp_table.Freight_Carrier ,
379 p_atp_table.Ship_Method ,
380 p_atp_table.Ship_Set_Name ,
381 p_atp_table.Arrival_Set_Name ,
382 p_atp_table.Override_Flag ,
383 p_atp_table.Action ,
384 p_atp_table.Ship_Date ,
385 p_atp_table.Available_Quantity ,
386 p_atp_table.Requested_Date_Quantity ,
387 p_atp_table.Group_Ship_Date ,
388 p_atp_table.Group_Arrival_Date ,
389 p_atp_table.Vendor_Id ,
390 p_atp_table.Vendor_Site_Id ,
391 p_atp_table.Insert_Flag ,
392 p_atp_table.Error_Code ,
393 p_atp_table.Message
394 from mtl_demand md
395 where md.rto_model_source_line = RTOMLine
396 and md.demand_source_header_id = dSrcHdrId
397 and md.demand_source_type = dSrcType
398 and md.organization_id = orgId
399 and md.primary_uom_quantity > 0
400 and md.config_status =80
401 and md.row_status_flag = 2;
402
403 /*---------------------------------------+
404 call atp module
405 +----------------------------------------*/
406
407 MRP_ATP_PUB.Call_ATP(
408 p_session_id,
409 p_atp_table,
410 x_atp_table,
411 x_atp_supply_demand,
412 x_atp_period,
413 x_atp_details,
414 x_return_status,
415 x_msg_data,
416 x_msg_count);
417
418 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
419 or x_return_status = FND_API.G_RET_STS_ERROR ) then
420 raise atp_error;
421 else
422 return (1);
423 END IF;
424
425 exception
426 when atp_error then
427 error_message := 'BOMNATPB:'||to_char(stmt)||':'||' ATP API returned Error';
428 message_name := 'BOM_ATO_LINK_ERROR';
429 return(0);
430
431 when others then
432 error_message := 'BOMNATPB:'||to_char(stmt)||':'||substrb(sqlerrm,1,150);
433 message_name := 'BOM_ATO_LINK_ERROR';
434 return(0);
435
436 end config_delink_atp;
437 end BOM_ATO_NEW_ATP_PK;