1 PACKAGE MSC_ATP_PROC AUTHID CURRENT_USER AS
2 /* $Header: MSCPATPS.pls 120.4.12010000.2 2009/09/13 18:13:00 sbnaik ship $ */
3
4
5 -- For supplier intransit LT project
6 G_VENDOR_SITE_ID MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
7 G_latest_ship_date_set DATE; --4460369
8 G_latest_arr_date_set DATE; --4460369
9
10 PROCEDURE add_inf_time_fence_to_period(
11 p_level IN NUMBER,
12 p_identifier IN NUMBER,
13 p_scenario_id IN NUMBER,
14 p_inventory_item_id IN NUMBER,
15 p_request_item_id IN NUMBER,
16 p_organization_id IN NUMBER,
17 p_supplier_id IN NUMBER,
18 p_supplier_site_id IN NUMBER,
19 p_infinite_time_fence_date IN DATE,
20 x_atp_period IN OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ
21 );
22
23 PROCEDURE get_period_data_from_SD_temp(
24 x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ
25 );
26
27 -- New procedure added as part of time_phased_atp to fix the
28 -- issue of not displaying correct quantities in ATP SD Window when
29 -- user opens ATP SD window from ATP pegging in allocated scenarios
30 PROCEDURE Get_Alloc_Data_From_Sd_Temp(
31 x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
32 x_return_status OUT NOCOPY VARCHAR2
33 );
34
35 PROCEDURE Atp_Sources (
36 p_instance_id IN NUMBER,
37 p_plan_id IN NUMBER,
38 p_inventory_item_id IN NUMBER,
39 p_organization_id IN NUMBER,
40 p_customer_id IN NUMBER,
41 p_customer_site_id IN NUMBER,
42 p_assign_set_id IN NUMBER,
43 --s_cto_rearch
44 p_Item_Sourcing_Info_Rec IN MSC_ATP_CTO.Item_Sourcing_Info_Rec,
45 p_session_id IN NUMBER,
46 --e_cto_rearch
47 --p_ship_set_item IN MRP_ATP_PUB.number_arr,
48 x_atp_sources OUT NoCopy MRP_ATP_PVT.Atp_Source_Typ,
49 x_return_status OUT NoCopy VARCHAR2,
50 p_partner_type IN NUMBER DEFAULT NULL, --2814895
51 p_party_site_id IN NUMBER DEFAULT NULL, --2814895
52 p_order_line_id IN NUMBER DEFAULT NULL --2814895
53 );
54
55 PROCEDURE item_sources_extend(p_item_sourcing_rec
56 IN OUT NOCOPY MSC_ATP_CTO.Item_Sourcing_Info_Rec);
57
58 PROCEDURE Atp_Consume_Range (
59 p_atp_qty IN OUT NoCopy MRP_ATP_PUB.number_arr,
60 p_start_idx IN NUMBER,
61 p_end_idx IN NUMBER
62 );
63
64 PROCEDURE Atp_Consume (
65 p_atp_qty IN OUT NoCopy MRP_ATP_PUB.number_arr,
66 p_counter IN NUMBER
67 );
68
69
70 PROCEDURE Details_Output (
71 p_atp_period IN MRP_ATP_PUB.ATP_Period_Typ,
72 p_atp_supply_demand IN MRP_ATP_PUB.ATP_Supply_Demand_Typ,
73 x_atp_period IN OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
74 x_atp_supply_demand IN OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
75 x_return_status OUT NoCopy VARCHAR2
76 );
77
78
79 PROCEDURE get_dept_res_code (
80 p_instance_id IN NUMBER,
81 p_department_id IN NUMBER,
82 p_resource_id IN NUMBER,
83 p_organization_id IN NUMBER,
84 x_department_code OUT NoCopy VARCHAR2,
85 x_resource_code OUT NoCopy VARCHAR2
86 );
87
88
89 PROCEDURE Get_SD_Period_Rec(
90 p_atp_period IN MRP_ATP_PUB.ATP_Period_Typ,
91 p_atp_supply_demand IN MRP_ATP_PUB.ATP_Supply_Demand_Typ,
92 p_identifier IN NUMBER,
93 p_scenario_id IN NUMBER,
94 p_new_scenario_id IN NUMBER,
95 x_atp_period IN OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
96 x_atp_supply_demand IN OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
97 x_return_status OUT NoCopy VARCHAR2
98 );
99
100
101 PROCEDURE get_org_default_info (
102 p_instance_id IN NUMBER,
103 p_organization_id IN NUMBER,
104 x_default_atp_rule_id OUT NoCopy NUMBER,
105 x_calendar_code OUT NoCopy VARCHAR2,
106 x_calendar_exception_set_id OUT NoCopy NUMBER,
107 x_default_demand_class OUT NoCopy VARCHAR2,
108 x_org_code OUT NoCopy VARCHAR2
109 );
110
111
112 PROCEDURE inv_primary_uom_conversion (
113 p_instance_id IN NUMBER,
114 p_organization_id IN NUMBER,
115 p_inventory_item_id IN NUMBER,
116 p_uom_code IN VARCHAR2,
117 x_primary_uom_code OUT NoCopy VARCHAR2,
118 x_conversion_rate OUT NoCopy NUMBER
119 );
120
121 PROCEDURE Extend_Atp_Sources (
122 p_atp_sources IN OUT NOCOPY MRP_ATP_PVT.Atp_Source_Typ,
123 x_return_status OUT NoCopy VARCHAR2
124 );
125
126
127 Procedure Get_Plan_Info(
128 p_instance_id IN NUMBER,
129 p_inventory_item_id IN NUMBER,
130 p_organization_id IN NUMBER,
131 p_demand_class IN VARCHAR2,
132 -- x_plan_id OUT NoCopy NUMBER, commented for bug 2392456
133 -- x_assign_set_id OUT NoCopy NUMBER commented for bug 2392456
134 x_plan_info_rec OUT NoCopy MSC_ATP_PVT.plan_info_rec, -- added for bug 2392456
135 p_parent_plan_id IN NUMBER DEFAULT NULL, --bug3510475
136 p_time_phased_atp IN VARCHAR2 DEFAULT 'N'
137 );
138
139
140 PROCEDURE Atp_Backward_Consume(
141 p_atp_qty IN OUT NoCopy MRP_ATP_PUB.number_arr
142 );
143
144
145 PROCEDURE Atp_Accumulate(
146 p_atp_qty IN OUT NoCopy MRP_ATP_PUB.number_arr
147 );
148
149 PROCEDURE ADD_COPRODUCTS(
150 p_plan_id IN NUMBER,
151 p_instance_id IN NUMBER,
152 p_org_id IN NUMBER,
153 p_inv_item_id IN NUMBER,
154 p_request_date IN DATE,
155 p_demand_class IN VARCHAR2,
156 p_assembly_qty IN NUMBER,
157 p_parent_pegging_id IN NUMBER,
158 p_rounding_flag IN number, -- 2869830
159 p_refresh_number IN NUMBER, -- For summary enhancement
160 p_disposition_id IN NUMBER -- 3766179
161 );
162
163
164 PROCEDURE get_Supply_Sources(
165 x_session_id IN NUMBER,
166 x_sr_instance_id IN NUMBER,
167 x_assignment_set_id IN NUMBER,
168 x_plan_id IN NUMBER,
169 x_calling_inst IN VARCHAR2,
170 x_ret_status OUT NoCopy VARCHAR2,
171 x_error_mesg OUT NoCopy VARCHAR2,
172 p_node_id IN NUMBER DEFAULT null, --bug3610706
173 p_requested_date IN DATE DEFAULT null -- 8524794
174 );
175
176
177 PROCEDURE msc_calculate_source_attrib
178 ( l_customer_id NUMBER,
179 l_ship_to_site_use_id NUMBER,
180 l_dest_org_id NUMBER,
181 l_dest_instance_id NUMBER,
182 counter NUMBER,
183 x_atp_sources IN OUT NoCopy mrp_atp_pvt.atp_source_typ,
184 x_other_cols IN OUT NoCopy order_sch_wb.other_cols_typ);
185
186
187 PROCEDURE insert_atp_sources
188 (x_session_id NUMBER,
189 x_dblink VARCHAR2,
190 x_calling_inst VARCHAR2,
191 x_atp_sources mrp_atp_pvt.atp_source_typ,
192 x_other_cols order_sch_wb.other_cols_typ);
193
194
195
196 PROCEDURE SHOW_SUMMARY_QUANTITY(p_instance_id IN NUMBER,
197 p_plan_id IN NUMBER,
198 p_organization_id IN NUMBER,
199 p_inventory_item_id IN NUMBER,
200 p_sd_date IN DATE,
201 p_resource_id IN NUMBER,
202 p_department_id IN NUMBER,
203 p_supplier_id IN NUMBER,
204 p_supplier_site_id IN NUMBER,
205 p_dc_flag IN NUMBER,
206 p_demand_class IN VARCHAR2,
207 p_mode IN NUMBER
208 );
209
210
211 PROCEDURE GET_ITEM_ATTRIBUTES(p_instance_id IN NUMBER,
212 p_plan_id IN NUMBER,
213 p_inventory_item_id IN NUMBER,
214 p_organization_id IN NUMBER,
215 p_item_attribute_rec IN OUT NoCopy MSC_ATP_PVT.item_attribute_rec);
216
217
218 PROCEDURE GET_ORG_ATTRIBUTES (
219 p_instance_id IN NUMBER,
220 p_organization_id IN NUMBER,
221 x_org_attribute_rec OUT NoCopy MSC_ATP_PVT.org_attribute_rec);
222
223 PROCEDURE get_global_org_info (
224 p_instance_id IN NUMBER,
225 p_organization_id IN NUMBER);
226
227 PROCEDURE get_global_item_info (p_instance_id IN NUMBER,
228 p_plan_id IN NUMBER,
229 p_inventory_item_id IN NUMBER,
230 p_organization_id IN NUMBER,
231 p_item_attribute_rec IN MSC_ATP_PVT.item_attribute_rec);
232
233 -- New Procedure Supplier Capacity and Lead Time (SCLT) Project.
234
235 PROCEDURE get_global_plan_info (p_instance_id IN NUMBER,
236 p_inventory_item_id IN NUMBER,
237 p_organization_id IN NUMBER,
238 p_demand_class IN VARCHAR2,
239 p_parent_plan_id IN NUMBER DEFAULT NULL, --bug3510475
240 p_time_phased_atp IN VARCHAR2 DEFAULT 'N');
241
242 --ubadrina bug 2265012 begin
243
244 TYPE Ship_Method_Rec IS RECORD (
245 ship_method VARCHAR2(30),
246 ship_method_text VARCHAR2(80),
247 intransit_time NUMBER);
248
249 g_ship_method_rec Ship_Method_Rec;
250
251 --ubadrina bug 2265012 end
252
253 --(ssurendr) Bug 2865389 OPM fix. Created a new procedure get_process_effectivity
254 --to get Process seq id,routing seq Id and Bill seq Id.
255 PROCEDURE get_process_effectivity (
256 p_plan_id IN NUMBER,
257 p_item_id IN NUMBER,
258 p_organization_id IN NUMBER,
259 p_sr_instance_id IN NUMBER,
260 p_new_schedule_date IN DATE,
261 p_requested_quantity IN NUMBER,
262 x_process_seq_id OUT NOCOPY NUMBER,
263 x_routing_seq_id OUT NOCOPY NUMBER,
264 x_bill_seq_id OUT NOCOPY NUMBER,
265 x_op_seq_id OUT NOCOPY NUMBER, --4570421
266 x_return_status OUT NOCOPY varchar2);
267
268 ---diag_atp
269 Procedure get_infinite_time_fence_date (p_instance_id IN NUMBER,
270 p_inventory_item_id IN NUMBER,
271 p_organization_id IN NUMBER,
272 p_plan_id IN NUMBER,
273 x_infinite_time_fence_date OUT NoCopy DATE,
274 x_atp_rule_name OUT NoCopy VARCHAR2,
275 -- Bug 3036513 Add additional parameters
276 -- with defaults for resource infinite time fence.
277 p_resource_id IN NUMBER DEFAULT NULL,
278 p_department_id IN NUMBER DEFAULT NULL);
279
280
281 PROCEDURE Get_Shipping_Methods (
282
283 p_from_organization_id IN number,
284 p_to_organization_id IN number,
285 p_to_customer_id IN number,
286 p_to_customer_site_id IN number,
287 p_from_instance_id IN number,
288 p_to_instance_id IN number,
289 p_session_id IN number,
290 p_calling_module IN number,
291 x_return_status OUT NOCOPY varchar2
292 );
293
294 PROCEDURE ATP_Shipping_Lead_Time (
295 p_from_loc_id IN NUMBER, -- From Location ID
296 p_to_customer_site_id IN NUMBER, -- To Customer Site ID
297 p_session_id IN NUMBER, -- A Unique Session ID
298 x_ship_method IN OUT NOCOPY VARCHAR2, -- Ship Method to Use
299 x_intransit_time OUT NOCOPY NUMBER, -- The calculated in-transit Lead time
300 -- Will be -1 when an error is encountered.
301 x_return_status OUT NOCOPY VARCHAR2 -- A return status variable
302 -- FND_API.G_RET_STS_SUCCESS - on success
306
303 -- FND_API.G_RET_STS_ERROR - on expected error
304 -- FND_API.G_RET_STS_UNEXP_ERROR - on unexpected error
305 );
307 PROCEDURE Initialize_Set_Processing(
308 p_set IN MRP_ATP_PUB.ATP_Rec_Typ,
309 p_start IN NUMBER DEFAULT 1
310 );
311
312 PROCEDURE Process_Set_Line(
313 p_set IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
314 i IN NUMBER,
315 x_line_status OUT NOCOPY NUMBER
316 );
317
318 PROCEDURE Process_Set_Dates_Errors(
319 p_set IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
320 p_src_dest IN VARCHAR2,
321 x_set_status OUT NOCOPY NUMBER,
322 p_start IN NUMBER DEFAULT NULL,
323 p_end IN NUMBER DEFAULT NULL
324 );
325
326 PROCEDURE Update_Set_SD_Dates(
327 p_set IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
328 p_arrival_set IN mrp_atp_pub.date_arr
329 );
330
331 PROCEDURE get_transit_time (
332 p_from_loc_id IN NUMBER,
333 p_from_instance_id IN NUMBER,
334 p_to_loc_id IN NUMBER,
335 p_to_instance_id IN NUMBER,
336 p_session_id IN NUMBER,
337 p_partner_site_id IN NUMBER,
338 x_ship_method IN OUT NoCopy VARCHAR2,
339 x_intransit_time OUT NoCopy NUMBER,
340 p_supplier_site_id IN NUMBER DEFAULT NULL,-- For supplier intransit LT project
341 p_partner_type IN NUMBER DEFAULT NULL,--2814895
342 p_party_site_id IN NUMBER DEFAULT NULL,--2814895
343 p_order_line_id IN NUMBER DEFAULT NULL --2814895
344 );
345
346 PROCEDURE get_delivery_lead_time(
347 p_from_org_id IN NUMBER,
348 p_from_loc_id IN NUMBER,
349 p_instance_id IN NUMBER,
350 p_to_org_id IN NUMBER,
351 p_to_loc_id IN NUMBER,
352 p_to_instance_id IN NUMBER,
353 p_customer_id IN NUMBER,
354 p_customer_site_id IN NUMBER,
355 p_supplier_id IN NUMBER,
356 p_supplier_site_id IN NUMBER,
357 p_session_id IN NUMBER,
358 p_partner_site_id IN NUMBER,
359 p_ship_method IN OUT NoCopy VARCHAR2,
360 x_delivery_lead_time OUT NoCopy NUMBER,
361 p_partner_type IN NUMBER DEFAULT NULL, --2814895
362 p_party_site_id IN NUMBER DEFAULT NULL, --2814895
363 p_order_line_id IN NUMBER DEFAULT NULL --2814895
364 );
365
366 -- append p2 to p1
367 PROCEDURE number_arr_cat (
368 p1 IN OUT NOCOPY mrp_atp_pub.number_arr,
369 p2 IN mrp_atp_pub.number_arr
370 );
371
372 -- append p2 to p1
373 PROCEDURE date_arr_cat (
374 p1 IN OUT NOCOPY mrp_atp_pub.date_arr,
375 p2 IN mrp_atp_pub.date_arr
376 );
377
378 -- loop through peg_ids and remove s/d recs
379 PROCEDURE cleanup_set(
380 p_instance_id IN number,
381 p_plan_id IN number,
382 peg_ids IN mrp_atp_pub.number_arr,
383 dmd_class_flag IN mrp_atp_pub.number_arr
384 );
385
386 -- supplier intransit LT
387 PROCEDURE Get_Supplier_Regions (p_vendor_site_id IN NUMBER,
388 p_calling_module IN NUMBER,
389 p_instance_id IN NUMBER,
390 x_return_status OUT NOCOPY VARCHAR2
391 );
392
393 -- supplier intransit LT
394 /*--ATP_Intransit_LT-----------------------------------------------------------
395 | - Generic API to find intransit lead times to be called mainly by
396 | products other than GOP.
397 | o p_src_dest - whether being called from source or
398 | destination - 1:Source; 2:Destination
399 | supp-org is supported only from destination
400 | o p_session_id - unique number identifying the current session
401 | o p_from_org_id - used in org-org and org-cust scenario, should
402 | be null in supp-org case, not required if
403 | p_from_loc_id is provided
404 | o p_from_loc_id - used in org-cust scenario, should be null in
405 | org-org and supp-org cases, not required if
406 | p_from_org_id is provided
407 | o p_from_vendor_site_id - used in supp-org scenario, should be null in
408 | org-org and supp-org cases, source id is expected
409 | o p_from_instance_id - from party's instance id, not required when
410 | called from source
411 | o p_to_org_id - used in org-org and supp-org scenario, should
412 | be null in org-cust case
413 | o p_to_loc_id - used in org-cust scenario, should be null in
414 | org-org and supp-org cases, not required if
415 | p_to_customer_site_id is provided
416 | o p_to_customer_site_id - used in org-cust scenario, should be null in
417 | org-org and supp-org cases, not required if
418 | p_to_loc_id is provided
419 | o p_to_instance_id - to party's instance id, not required when called
420 | from source
421 | o p_ship_method - default ship method is used if not passed. if
422 | the passed ship method does not exist in shipping
423 | network then default ship method is returned
424 | o x_intransit_lead_time - intrasit lead time
425 | o x_return_status - return status
426 +----------------------------------------------------------------------------*/
427
428 PROCEDURE ATP_Intransit_LT (p_src_dest IN NUMBER,
429 p_session_id IN NUMBER,
433 p_from_instance_id IN NUMBER,
430 p_from_org_id IN NUMBER,
431 p_from_loc_id IN NUMBER,
432 p_from_vendor_site_id IN NUMBER,
434 p_to_org_id IN NUMBER,
435 p_to_loc_id IN NUMBER,
436 p_to_customer_site_id IN NUMBER,
437 p_to_instance_id IN NUMBER,
438 p_ship_method IN OUT NoCopy VARCHAR2,
439 x_intransit_lead_time OUT NoCopy NUMBER,
440 x_return_status OUT NOCOPY VARCHAR2
441 );
442
443 END MSC_ATP_PROC;
444