1 PACKAGE MSC_ATP_PROC AS
2 /* $Header: MSCPATPS.pls 120.4 2007/12/12 10:34:06 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 );
174
175
176 PROCEDURE msc_calculate_source_attrib
177 ( l_customer_id NUMBER,
178 l_ship_to_site_use_id NUMBER,
179 l_dest_org_id NUMBER,
180 l_dest_instance_id NUMBER,
181 counter NUMBER,
182 x_atp_sources IN OUT NoCopy mrp_atp_pvt.atp_source_typ,
183 x_other_cols IN OUT NoCopy order_sch_wb.other_cols_typ);
184
185
186 PROCEDURE insert_atp_sources
187 (x_session_id NUMBER,
188 x_dblink VARCHAR2,
189 x_calling_inst VARCHAR2,
190 x_atp_sources mrp_atp_pvt.atp_source_typ,
191 x_other_cols order_sch_wb.other_cols_typ);
192
193
194
195 PROCEDURE SHOW_SUMMARY_QUANTITY(p_instance_id IN NUMBER,
196 p_plan_id IN NUMBER,
197 p_organization_id IN NUMBER,
198 p_inventory_item_id IN NUMBER,
199 p_sd_date IN DATE,
200 p_resource_id IN NUMBER,
201 p_department_id IN NUMBER,
202 p_supplier_id IN NUMBER,
203 p_supplier_site_id IN NUMBER,
204 p_dc_flag IN NUMBER,
205 p_demand_class IN VARCHAR2,
206 p_mode IN NUMBER
207 );
208
209
210 PROCEDURE GET_ITEM_ATTRIBUTES(p_instance_id IN NUMBER,
211 p_plan_id IN NUMBER,
212 p_inventory_item_id IN NUMBER,
213 p_organization_id IN NUMBER,
214 p_item_attribute_rec IN OUT NoCopy MSC_ATP_PVT.item_attribute_rec);
215
216
217 PROCEDURE GET_ORG_ATTRIBUTES (
218 p_instance_id IN NUMBER,
219 p_organization_id IN NUMBER,
220 x_org_attribute_rec OUT NoCopy MSC_ATP_PVT.org_attribute_rec);
221
222 PROCEDURE get_global_org_info (
223 p_instance_id IN NUMBER,
224 p_organization_id IN NUMBER);
225
226 PROCEDURE get_global_item_info (p_instance_id IN NUMBER,
227 p_plan_id IN NUMBER,
228 p_inventory_item_id IN NUMBER,
229 p_organization_id IN NUMBER,
230 p_item_attribute_rec IN MSC_ATP_PVT.item_attribute_rec);
231
232 -- New Procedure Supplier Capacity and Lead Time (SCLT) Project.
233
234 PROCEDURE get_global_plan_info (p_instance_id IN NUMBER,
235 p_inventory_item_id IN NUMBER,
236 p_organization_id IN NUMBER,
237 p_demand_class IN VARCHAR2,
238 p_parent_plan_id IN NUMBER DEFAULT NULL, --bug3510475
239 p_time_phased_atp IN VARCHAR2 DEFAULT 'N');
240
241 --ubadrina bug 2265012 begin
242
243 TYPE Ship_Method_Rec IS RECORD (
244 ship_method VARCHAR2(30),
245 ship_method_text VARCHAR2(80),
246 intransit_time NUMBER);
247
248 g_ship_method_rec Ship_Method_Rec;
249
250 --ubadrina bug 2265012 end
251
252 --(ssurendr) Bug 2865389 OPM fix. Created a new procedure get_process_effectivity
253 --to get Process seq id,routing seq Id and Bill seq Id.
254 PROCEDURE get_process_effectivity (
255 p_plan_id IN NUMBER,
256 p_item_id IN NUMBER,
257 p_organization_id IN NUMBER,
258 p_sr_instance_id IN NUMBER,
259 p_new_schedule_date IN DATE,
260 p_requested_quantity IN NUMBER,
261 x_process_seq_id OUT NOCOPY NUMBER,
262 x_routing_seq_id OUT NOCOPY NUMBER,
263 x_bill_seq_id OUT NOCOPY NUMBER,
264 x_op_seq_id OUT NOCOPY NUMBER, --4570421
265 x_return_status OUT NOCOPY varchar2);
266
267 ---diag_atp
268 Procedure get_infinite_time_fence_date (p_instance_id IN NUMBER,
269 p_inventory_item_id IN NUMBER,
270 p_organization_id IN NUMBER,
271 p_plan_id IN NUMBER,
272 x_infinite_time_fence_date OUT NoCopy DATE,
273 x_atp_rule_name OUT NoCopy VARCHAR2,
274 -- Bug 3036513 Add additional parameters
275 -- with defaults for resource infinite time fence.
276 p_resource_id IN NUMBER DEFAULT NULL,
277 p_department_id IN NUMBER DEFAULT NULL);
278
279
280 PROCEDURE Get_Shipping_Methods (
281
282 p_from_organization_id IN number,
283 p_to_organization_id IN number,
284 p_to_customer_id IN number,
285 p_to_customer_site_id IN number,
286 p_from_instance_id IN number,
287 p_to_instance_id IN number,
288 p_session_id IN number,
289 p_calling_module IN number,
290 x_return_status OUT NOCOPY varchar2
291 );
292
293 PROCEDURE ATP_Shipping_Lead_Time (
294 p_from_loc_id IN NUMBER, -- From Location ID
295 p_to_customer_site_id IN NUMBER, -- To Customer Site ID
296 p_session_id IN NUMBER, -- A Unique Session ID
297 x_ship_method IN OUT NOCOPY VARCHAR2, -- Ship Method to Use
298 x_intransit_time OUT NOCOPY NUMBER, -- The calculated in-transit Lead time
299 -- Will be -1 when an error is encountered.
300 x_return_status OUT NOCOPY VARCHAR2 -- A return status variable
301 -- FND_API.G_RET_STS_SUCCESS - on success
302 -- FND_API.G_RET_STS_ERROR - on expected error
303 -- FND_API.G_RET_STS_UNEXP_ERROR - on unexpected error
304 );
305
306 PROCEDURE Initialize_Set_Processing(
307 p_set IN MRP_ATP_PUB.ATP_Rec_Typ,
308 p_start IN NUMBER DEFAULT 1
309 );
310
311 PROCEDURE Process_Set_Line(
312 p_set IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
313 i IN NUMBER,
314 x_line_status OUT NOCOPY NUMBER
315 );
316
317 PROCEDURE Process_Set_Dates_Errors(
318 p_set IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
319 p_src_dest IN VARCHAR2,
320 x_set_status OUT NOCOPY NUMBER,
321 p_start IN NUMBER DEFAULT NULL,
322 p_end IN NUMBER DEFAULT NULL
323 );
324
325 PROCEDURE Update_Set_SD_Dates(
326 p_set IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
327 p_arrival_set IN mrp_atp_pub.date_arr
328 );
329
330 PROCEDURE get_transit_time (
331 p_from_loc_id IN NUMBER,
332 p_from_instance_id IN NUMBER,
333 p_to_loc_id IN NUMBER,
334 p_to_instance_id IN NUMBER,
335 p_session_id IN NUMBER,
336 p_partner_site_id IN NUMBER,
337 x_ship_method IN OUT NoCopy VARCHAR2,
338 x_intransit_time OUT NoCopy NUMBER,
339 p_supplier_site_id IN NUMBER DEFAULT NULL,-- For supplier intransit LT project
340 p_partner_type IN NUMBER DEFAULT NULL,--2814895
341 p_party_site_id IN NUMBER DEFAULT NULL,--2814895
342 p_order_line_id IN NUMBER DEFAULT NULL --2814895
343 );
344
345 PROCEDURE get_delivery_lead_time(
346 p_from_org_id IN NUMBER,
347 p_from_loc_id IN NUMBER,
348 p_instance_id IN NUMBER,
349 p_to_org_id IN NUMBER,
350 p_to_loc_id IN NUMBER,
351 p_to_instance_id IN NUMBER,
352 p_customer_id IN NUMBER,
353 p_customer_site_id IN NUMBER,
354 p_supplier_id IN NUMBER,
355 p_supplier_site_id IN NUMBER,
356 p_session_id IN NUMBER,
357 p_partner_site_id IN NUMBER,
358 p_ship_method IN OUT NoCopy VARCHAR2,
359 x_delivery_lead_time OUT NoCopy NUMBER,
360 p_partner_type IN NUMBER DEFAULT NULL, --2814895
361 p_party_site_id IN NUMBER DEFAULT NULL, --2814895
362 p_order_line_id IN NUMBER DEFAULT NULL --2814895
363 );
364
365 -- append p2 to p1
366 PROCEDURE number_arr_cat (
367 p1 IN OUT NOCOPY mrp_atp_pub.number_arr,
368 p2 IN mrp_atp_pub.number_arr
369 );
370
371 -- append p2 to p1
372 PROCEDURE date_arr_cat (
373 p1 IN OUT NOCOPY mrp_atp_pub.date_arr,
374 p2 IN mrp_atp_pub.date_arr
375 );
376
377 -- loop through peg_ids and remove s/d recs
378 PROCEDURE cleanup_set(
379 p_instance_id IN number,
380 p_plan_id IN number,
381 peg_ids IN mrp_atp_pub.number_arr,
382 dmd_class_flag IN mrp_atp_pub.number_arr
383 );
384
385 -- supplier intransit LT
386 PROCEDURE Get_Supplier_Regions (p_vendor_site_id IN NUMBER,
387 p_calling_module IN NUMBER,
388 p_instance_id IN NUMBER,
389 x_return_status OUT NOCOPY VARCHAR2
390 );
391
392 -- supplier intransit LT
393 /*--ATP_Intransit_LT-----------------------------------------------------------
394 | - Generic API to find intransit lead times to be called mainly by
395 | products other than GOP.
396 | o p_src_dest - whether being called from source or
397 | destination - 1:Source; 2:Destination
398 | supp-org is supported only from destination
399 | o p_session_id - unique number identifying the current session
400 | o p_from_org_id - used in org-org and org-cust scenario, should
401 | be null in supp-org case, not required if
402 | p_from_loc_id is provided
403 | o p_from_loc_id - used in org-cust scenario, should be null in
404 | org-org and supp-org cases, not required if
405 | p_from_org_id is provided
406 | o p_from_vendor_site_id - used in supp-org scenario, should be null in
407 | org-org and supp-org cases, source id is expected
408 | o p_from_instance_id - from party's instance id, not required when
409 | called from source
410 | o p_to_org_id - used in org-org and supp-org scenario, should
411 | be null in org-cust case
412 | o p_to_loc_id - used in org-cust scenario, should be null in
413 | org-org and supp-org cases, not required if
414 | p_to_customer_site_id is provided
415 | o p_to_customer_site_id - used in org-cust scenario, should be null in
416 | org-org and supp-org cases, not required if
417 | p_to_loc_id is provided
418 | o p_to_instance_id - to party's instance id, not required when called
419 | from source
420 | o p_ship_method - default ship method is used if not passed. if
421 | the passed ship method does not exist in shipping
422 | network then default ship method is returned
423 | o x_intransit_lead_time - intrasit lead time
424 | o x_return_status - return status
425 +----------------------------------------------------------------------------*/
426
427 PROCEDURE ATP_Intransit_LT (p_src_dest IN NUMBER,
428 p_session_id IN NUMBER,
429 p_from_org_id IN NUMBER,
430 p_from_loc_id IN NUMBER,
431 p_from_vendor_site_id IN NUMBER,
432 p_from_instance_id IN NUMBER,
433 p_to_org_id IN NUMBER,
434 p_to_loc_id IN NUMBER,
435 p_to_customer_site_id IN NUMBER,
436 p_to_instance_id IN NUMBER,
437 p_ship_method IN OUT NoCopy VARCHAR2,
438 x_intransit_lead_time OUT NoCopy NUMBER,
439 x_return_status OUT NOCOPY VARCHAR2
440 );
441
442 END MSC_ATP_PROC;
443