1 PACKAGE MSC_Rel_Plan_PUB AS
2 /* $Header: MSCPRELS.pls 120.6.12010000.2 2008/09/11 11:52:35 parega ship $ */
3
4 -- Start of Comments
5 -- API name MSC_Release_Plan_Sc
6 -- Type Public
7 -- Procedure
8 --
9 -- Pre-reqs
10 --
11 -- Parameters
12 --
13 -- Version Current version = 1.0
14 -- Initial version = 1.0
15 --
16 -- Notes
17 --
18 -- End of Comments
19
20 -- New Data Type
21
22 TYPE NumTblTyp IS TABLE OF NUMBER;
23
24 -- CONSTANTS --
25 SYS_YES CONSTANT INTEGER := 1;
26 SYS_NO CONSTANT INTEGER := 2;
27
28 RELEASE_ATTEMPTED CONSTANT NUMBER := 1;
29 NOT_RELEASABLE CONSTANT NUMBER := -1;
30 RELEASABLE CONSTANT NUMBER := 0;
31
32 REQ_GRP_ALL_ON_ONE CONSTANT INTEGER := 1; -- PO group by
33 REQ_GRP_ITEM CONSTANT INTEGER := 2;
34 REQ_GRP_BUYER CONSTANT INTEGER := 3;
35 REQ_GRP_PLANNER CONSTANT INTEGER := 4;
36 REQ_GRP_VENDOR CONSTANT INTEGER := 5;
37 REQ_GRP_ONE_EACH CONSTANT INTEGER := 6;
38 REQ_GRP_CATEGORY CONSTANT INTEGER := 7;
39 REQ_GRP_LOCATION CONSTANT INTEGER := 8;
40
41 WIP_DIS_MASS_LOAD CONSTANT INTEGER := 1;
42 WIP_REP_MASS_LOAD CONSTANT INTEGER := 2;
43 WIP_DIS_MASS_RESCHEDULE CONSTANT INTEGER := 4;
44 PO_MASS_LOAD CONSTANT INTEGER := 8;
45 PO_MASS_RESCHEDULE CONSTANT INTEGER := 16;
46
47 DRP_REQ_LOAD constant integer := 32; -- drp release
48 DRP_REQ_RESCHED constant integer := 64; -- drp release
49
50 ERO_LOAD CONSTANT NUMBER := 128;
51 IRO_LOAD CONSTANT NUMBER := 256;
52
53 PURCHASE_ORDER CONSTANT INTEGER := 1; -- order type lookup
54 PURCH_REQ CONSTANT INTEGER := 2;
55 WORK_ORDER CONSTANT INTEGER := 3;
56 REPETITVE_SCHEDULE CONSTANT INTEGER := 4;
57 PLANNED_ORDER CONSTANT INTEGER := 5;
58 MATERIAL_TRANSFER CONSTANT INTEGER := 6;
59 NONSTD_JOB CONSTANT INTEGER := 7;
60 RECEIPT_PURCH_ORDER CONSTANT INTEGER := 8;
61 REQUIREMENT CONSTANT INTEGER := 9;
62 FPO_SUPPLY CONSTANT INTEGER := 10;
63
64 NOT_UNDER_REV_CONTROL CONSTANT INTEGER := 1;
65 UNDER_REV_CONTROL CONSTANT INTEGER := 2;
66
67 PURCHASING_BY_REV CONSTANT INTEGER := 1;
68 NOT_PURCHASING_BY_REV CONSTANT INTEGER := 2;
69
70
71 LT_RESOURCE CONSTANT INTEGER := 1; -- wip details load type
72 LT_COMPONENT CONSTANT INTEGER := 2;
73 LT_OPERATION CONSTANT INTEGER := 3;
74 LT_RESOURCE_USAGE CONSTANT INTEGER := 4;
75
76 SUBST_DELETE CONSTANT INTEGER := 1; -- wip details substitution
77 SUBST_ADD CONSTANT INTEGER := 2; -- type
78 SUBST_CHANGE CONSTANT INTEGER := 3;
79
80 -- Variables --
81 v_hour_uom VARCHAR2(4);
82 v_purchasing_by_rev NUMBER;
83 v_instance_code_length NUMBER;
84
85 g_batch_id NUMBER := -1;
86 g_prev_batch_id NUMBER := g_batch_id;
87 G_SPP_SPLIT_YN VARCHAR2(1) := NVL(FND_PROFILE.VALUE('MSO_ENABLE_ROUNDING_OF_FRACTIONAL_SUPPLIES_FOR_SRP'),'N');
88
89 -- Procedures --
90 PROCEDURE MSC_RELEASE_PLAN_SC
91 ( arg_plan_id IN NUMBER
92 , arg_log_org_id IN NUMBER
93 , arg_log_sr_instance IN NUMBER
94 , arg_org_id IN NUMBER
95 , arg_sr_instance IN NUMBER
96 , arg_compile_desig IN VARCHAR2
97 , arg_user_id IN NUMBER
98 , arg_po_group_by IN NUMBER
99 , arg_po_batch_number IN NUMBER
100 , arg_wip_group_id IN NUMBER
101 , arg_loaded_jobs IN OUT NOCOPY NumTblTyp
102 , arg_loaded_reqs IN OUT NOCOPY NumTblTyp
103 , arg_loaded_scheds IN OUT NOCOPY NumTblTyp
104 , arg_resched_jobs IN OUT NOCOPY NumTblTyp
105 , arg_resched_reqs IN OUT NOCOPY NumTblTyp
106 , arg_wip_req_id IN OUT NOCOPY NumTblTyp
107 , arg_req_load_id IN OUT NOCOPY NumTblTyp
108 , arg_req_resched_id IN OUT NOCOPY NumTblTyp
109 , arg_released_instance IN OUT NOCOPY NumTblTyp
110 , arg_mode IN VARCHAR2 DEFAULT NULL
111 , arg_transaction_id IN NUMBER DEFAULT NULL
112 , arg_loaded_lot_jobs IN OUT NOCOPY NumTblTyp
113 , arg_resched_lot_jobs IN OUT NOCOPY NumTblTyp
114 , arg_osfm_req_id IN OUT NOCOPY NumTblTyp
115 -- the following 2 parameters added for dsr
116 , arg_resched_eam_jobs IN OUT NOCOPY NumTblTyp
117 , arg_eam_req_id IN OUT NOCOPY NumTblTyp
118 -- the following 4 parameters added for drp release
119 , arg_loaded_int_reqs IN OUT NOCOPY NumTblTyp
120 , arg_resched_int_reqs IN OUT NOCOPY NumTblTyp
121 , arg_int_req_load_id IN OUT NOCOPY NumTblTyp
122 , arg_int_req_resched_id IN OUT NOCOPY NumTblTyp
123 , arg_loaded_int_repair_orders IN OUT NOCOPY NumTblTyp -- for release of IRO
124 , arg_int_repair_orders_id IN OUT NOCOPY NumTblTyp --for release of IRO
125 , arg_loaded_ext_repair_orders IN OUT NOCOPY NumTblTyp -- for release of ERO
126 , arg_ext_repair_orders_id IN OUT NOCOPY NumTblTyp --for release of ERO
127 );
128
129
130 PROCEDURE MSC_RELEASE_PLAN_SC
131 ( arg_plan_id IN NUMBER
132 , arg_log_org_id IN NUMBER
133 , arg_log_sr_instance IN NUMBER
134 , arg_org_id IN NUMBER
135 , arg_sr_instance IN NUMBER
136 , arg_compile_desig IN VARCHAR2
137 , arg_user_id IN NUMBER
138 , arg_po_group_by IN NUMBER
139 , arg_po_batch_number IN NUMBER
140 , arg_wip_group_id IN NUMBER
141 , arg_loaded_jobs IN OUT NOCOPY NumTblTyp
142 , arg_loaded_reqs IN OUT NOCOPY NumTblTyp
143 , arg_loaded_scheds IN OUT NOCOPY NumTblTyp
144 , arg_resched_jobs IN OUT NOCOPY NumTblTyp
145 , arg_resched_reqs IN OUT NOCOPY NumTblTyp
146 , arg_wip_req_id IN OUT NOCOPY NumTblTyp
147 , arg_req_load_id IN OUT NOCOPY NumTblTyp
148 , arg_req_resched_id IN OUT NOCOPY NumTblTyp
149 , arg_released_instance IN OUT NOCOPY NumTblTyp
150 , arg_mode IN VARCHAR2 DEFAULT NULL
151 , arg_transaction_id IN NUMBER DEFAULT NULL
152 -- the following 4 parameters added for drp release
153 , arg_loaded_int_reqs IN OUT NOCOPY NumTblTyp
154 , arg_resched_int_reqs IN OUT NOCOPY NumTblTyp
155 , arg_int_req_load_id IN OUT NOCOPY NumTblTyp
156 , arg_int_req_resched_id IN OUT NOCOPY NumTblTyp
157 , arg_loaded_int_repair_orders IN OUT NOCOPY NumTblTyp -- for release of IRO
158 , arg_int_repair_orders_id IN OUT NOCOPY NumTblTyp --for release of IRO
159 , arg_loaded_ext_repair_orders IN OUT NOCOPY NumTblTyp -- for release of ERO
160 , arg_ext_repair_orders_id IN OUT NOCOPY NumTblTyp --for release of ERO
161 );
162
163 PROCEDURE LOAD_MSC_INTERFACE
164 ( arg_dblink IN VARCHAR2
165 , arg_plan_id IN NUMBER
166 , arg_log_org_id IN NUMBER
167 , arg_org_instance IN NUMBER
168 , arg_owning_org_id IN NUMBER
169 , arg_owning_instance IN NUMBER
170 , arg_compile_desig IN VARCHAR2
171 , arg_user_id IN NUMBER
172 , arg_po_group_by IN NUMBER
173 , arg_po_batch_number IN NUMBER
174 , arg_wip_group_id IN NUMBER
175 , arg_loaded_jobs IN OUT NOCOPY NUMBER
176 , arg_loaded_lot_jobs IN OUT NOCOPY NUMBER
177 ,arg_resched_lot_jobs IN OUT NOCOPY NUMBER
178 , arg_loaded_reqs IN OUT NOCOPY NUMBER
179 , arg_loaded_scheds IN OUT NOCOPY NUMBER
180 , arg_resched_jobs IN OUT NOCOPY NUMBER
181 , arg_resched_reqs IN OUT NOCOPY NUMBER
182 , arg_wip_req_id IN OUT NOCOPY NUMBER
183 ,arg_osfm_req_id IN OUT NOCOPY NUMBER
184 , arg_req_load_id IN OUT NOCOPY NUMBER
185 , arg_req_resched_id IN OUT NOCOPY NUMBER
186 , arg_mode IN VARCHAR2 DEFAULT NULL
187 , arg_transaction_id IN NUMBER DEFAULT NULL
188 , l_apps_ver IN VARCHAR2
189 -- dsr
190 , arg_resched_eam_jobs IN OUT NOCOPY NUMBER
191 , arg_eam_req_id IN OUT NOCOPY NUMBER
192 -- the following 4 parameters added for drp release
193 , arg_loaded_int_reqs IN OUT NOCOPY Number
194 , arg_resched_int_reqs IN OUT NOCOPY Number
195 , arg_int_req_load_id IN OUT NOCOPY Number
196 , arg_int_req_resched_id IN OUT NOCOPY Number
197 , arg_loaded_int_repair_orders IN OUT NOCOPY Number -- for release of IRO
198 , arg_int_repair_orders_id IN OUT NOCOPY Number --for release of IRO
199 , arg_loaded_ext_repair_orders IN OUT NOCOPY Number -- for release of ERO
200 , arg_ext_repair_orders_id IN OUT NOCOPY Number --for release of ERO
201 );
202
203
204
205 FUNCTION load_wip_discrete_jobs
206 ( arg_plan_id IN NUMBER
207 , arg_log_org_id IN NUMBER
208 , arg_org_instance IN NUMBER
209 , arg_owning_org_id IN NUMBER
210 , arg_owning_instance IN NUMBER
211 , arg_user_id IN NUMBER
212 , arg_wip_group_id IN NUMBER
213 , arg_mode IN VARCHAR2
214 , arg_transaction_id IN NUMBER
215 , l_apps_ver IN VARCHAR2
216 )RETURN NUMBER;
217
218
219 FUNCTION load_osfm_lot_jobs
220 ( arg_plan_id IN NUMBER
221 , arg_log_org_id IN NUMBER
222 , arg_org_instance IN NUMBER
223 , arg_owning_org_id IN NUMBER
224 , arg_owning_instance IN NUMBER
225 , arg_user_id IN NUMBER
226 , arg_wip_group_id IN NUMBER
227 , arg_mode IN VARCHAR2
228 , arg_transaction_id IN NUMBER,
229 l_apps_ver IN VARCHAR2
230 )RETURN NUMBER;
231
232
233 FUNCTION reschedule_osfm_lot_jobs
234 ( arg_plan_id IN NUMBER
235 , arg_log_org_id IN NUMBER
236 , arg_org_instance IN NUMBER
237 , arg_owning_org_id IN NUMBER
238 , arg_owning_instance IN NUMBER
239 , arg_user_id IN NUMBER
240 , arg_wip_group_id IN NUMBER
241 , arg_mode IN VARCHAR2
242 , arg_transaction_id IN NUMBER
243 )RETURN NUMBER;
244
245
246 FUNCTION reschedule_wip_discrete_jobs
247 ( arg_plan_id IN NUMBER
248 , arg_log_org_id IN NUMBER
249 , arg_org_instance IN NUMBER
250 , arg_owning_org_id IN NUMBER
251 , arg_owning_instance IN NUMBER
252 , arg_user_id IN NUMBER
253 , arg_wip_group_id IN NUMBER
254 , arg_mode IN VARCHAR2
255 , arg_transaction_id IN NUMBER
256 , l_apps_ver IN VARCHAR2
257 , arg_load_type IN NUMBER DEFAULT NULL -- dsr
258 )RETURN NUMBER;
259
260 FUNCTION load_repetitive_schedules
261 ( arg_plan_id IN NUMBER
262 , arg_log_org_id IN NUMBER
263 , arg_org_instance IN NUMBER
264 , arg_owning_org_id IN NUMBER
265 , arg_owning_instance IN NUMBER
266 , arg_user_id IN NUMBER
267 , arg_wip_group_id IN NUMBER
268 , arg_mode IN VARCHAR2
269 , arg_transaction_id IN NUMBER
270 )RETURN NUMBER;
271
272 FUNCTION load_po_requisitions
273 ( arg_plan_id IN NUMBER
274 , arg_log_org_id IN NUMBER
275 , arg_org_instance IN NUMBER
276 , arg_owning_org_id IN NUMBER
277 , arg_owning_instance IN NUMBER
278 , arg_user_id IN NUMBER
279 , arg_po_group_by IN NUMBER
280 , arg_po_batch_number IN NUMBER
281 , arg_mode IN VARCHAR2
282 , arg_transaction_id IN NUMBER
283 )RETURN NUMBER;
284
285 FUNCTION reschedule_po
286 ( arg_plan_id IN NUMBER
287 , arg_log_org_id IN NUMBER
288 , arg_org_instance IN NUMBER
289 , arg_owning_org_id IN NUMBER
290 , arg_owning_instance IN NUMBER
291 , arg_user_id IN NUMBER
292 , arg_po_batch_number IN NUMBER
293 , arg_mode IN VARCHAR2
294 , arg_transaction_id IN NUMBER
295 )RETURN NUMBER;
296
297 FUNCTION reschedule_po_wf
298 ( arg_dblink IN VARCHAR2
299 , arg_plan_id IN NUMBER
300 , arg_log_org_id IN NUMBER
301 , arg_org_instance IN NUMBER
302 , arg_owning_org_id IN NUMBER
303 , arg_owning_instance IN NUMBER
304 , arg_user_id IN NUMBER
305 , arg_mode IN VARCHAR2
306 , arg_transaction_id IN NUMBER
307 )RETURN NUMBER;
308
309 FUNCTION GET_CAL_DATE ( lv_inst_id in number
310 , lv_calendar_date in date
311 ,lv_calendar_code in varchar2) return date ;
312 FUNCTION GET_COPRODUCT_QTY ( inst_id in number , pln_id in number ,disp_id in number, bill_seq_id in number) return number ;
313
314 FUNCTION GET_REV_CUM_YIELD (inst_id in number , pln_id in number, process_seq_id in number,trans_id in number,org_id in number) return number ;
315
316 FUNCTION GET_REV_CUM_YIELD_DISC (inst_id IN NUMBER,
317 pln_id IN NUMBER,
318 process_seq_id IN NUMBER,
319 trans_id IN NUMBER,
320 org_id IN NUMBER,
321 org_type IN NUMBER)
322 RETURN NUMBER;
323
324 FUNCTION GET_REV_CUM_YIELD_DISC_COMP( inst_id IN NUMBER
325 ,pln_id IN NUMBER
326 ,process_seq_id IN NUMBER
327 ,trans_id IN NUMBER
328 ,org_id IN NUMBER
329 ,org_type IN NUMBER
330 ,op_seq_num IN NUMBER
331 )
332 RETURN NUMBER;
333
334
335 FUNCTION GET_USAGE_QUANTITY ( p_plan_id IN NUMBER
336 ,p_inst_id IN NUMBER
337 ,p_org_id IN NUMBER
338 ,p_using_assy_id IN NUMBER
339 ,p_comp_seq_id IN NUMBER) RETURN NUMBER;
340 FUNCTION GET_WIP_SUPPLY_TYPE ( p_plan_id IN NUMBER
341 ,p_inst_id IN NUMBER
342 ,p_process_seq_id IN NUMBER
343 ,p_item_id IN NUMBER
344 ,p_comp_item_id IN NUMBER
345 ,p_org_id IN NUMBER) RETURN NUMBER;
346
347 /* Added these @ functions to get the SR_TP_ID and the TP_SITE_LOCATION
348 based on the arguments */
349 FUNCTION GET_MODELED_SR_TP_ID (pMODELED_SUPPLIER_ID IN NUMBER,
350 pSR_INSTANCE_ID IN NUMBER)
351 RETURN NUMBER;
352
353 FUNCTION GET_MODELED_TP_SITE_CODE (pMODELED_SUPPLIER_ID IN NUMBER,
354 pMODELED_SUPPLIER_SITE_ID IN NUMBER,
355 pSR_INSTANCE_ID IN NUMBER)
356 RETURN VARCHAR2;
357
358 FUNCTION Decode_Sales_Order_Number(p_order_number_string in VARCHAR2)
359 return NUMBER;
360 PROCEDURE POPULATE_ISO_IN_SOURCE(
361 l_dblink IN varchar2,
362 l_arg_po_batch_number IN number,
363 l_arg_owning_instance IN number,
364 l_arg_po_group_by IN number,
365 l_arg_plan_id IN number,
366 l_arg_log_org_id IN number,
367 l_arg_owning_org_id IN number,
368 l_arg_org_instance IN number,
369 l_arg_mode IN varchar2,
370 l_arg_transaction_id IN number,
371 arg_loaded_int_reqs IN OUT NOCOPY number,
372 arg_resched_int_reqs IN OUT NOCOPY number,
373 p_load_type IN number);
374
375 PROCEDURE POPULATE_ISO_IN_SOURCE_2(
376 l_dblink IN varchar2,
377 l_arg_po_batch_number IN number,
378 l_arg_owning_instance IN number,
379 l_arg_po_group_by IN number,
380 l_arg_plan_id IN number,
381 l_arg_log_org_id IN number,
382 l_arg_owning_org_id IN number,
386 arg_loaded_int_reqs IN OUT NOCOPY number,
383 l_arg_org_instance IN number,
384 l_arg_mode IN varchar2,
385 l_arg_transaction_id IN number,
387 arg_resched_int_reqs IN OUT NOCOPY number,
388 p_load_type IN number);
389
390 Procedure Release_IRO(
391 p_dblink IN varchar2,
392 p_arg_iro_batch_number IN number,
393 p_arg_owning_instance IN number,
394 p_arg_po_group_by IN number,
395 p_arg_plan_id IN number,
396 p_arg_log_org_id IN number,
397 p_arg_owning_org_id IN number,
398 p_arg_org_instance IN number,
399 p_arg_mode IN varchar2,
400 p_arg_transaction_id IN number,
401 p_arg_loaded_int_repair_orders IN OUT NOCOPY number,
402 p_load_type IN number);
403
404 Procedure Release_IRO_2(
405 p_dblink IN varchar2,
406 p_arg_iro_batch_number IN number,
407 p_arg_owning_instance IN number,
408 p_arg_po_group_by IN number,
409 p_arg_plan_id IN number,
410 p_arg_log_org_id IN number,
411 p_arg_owning_org_id IN number,
412 p_arg_org_instance IN number,
413 p_arg_mode IN varchar2,
414 p_arg_transaction_id IN number,
415 p_arg_loaded_int_repair_orders IN OUT NOCOPY number,
416 p_load_type IN number);
417
418
419 PROCEDURE Release_Ero(
420 p_dblink IN VARCHAR2,
421 p_arg_ero_batch_number IN number,
422 p_arg_owning_instance IN NUMBER,
423 p_arg_po_group_by IN NUMBER,
424 p_arg_plan_id IN NUMBER,
425 p_arg_log_org_id IN NUMBER,
426 p_arg_owning_org_id IN NUMBER,
427 p_arg_org_instance IN NUMBER,
428 p_arg_mode IN VARCHAR2,
429 p_arg_transaction_id IN NUMBER,
430 p_arg_loaded_ext_repair_orders IN OUT nocopy NUMBER,
431 p_load_type IN NUMBER);
432 PROCEDURE Release_Ero_2(
433 p_dblink IN VARCHAR2,
434 p_arg_ero_batch_number IN number,
435 p_arg_owning_instance IN NUMBER,
436 p_arg_po_group_by IN NUMBER,
437 p_arg_plan_id IN NUMBER,
438 p_arg_log_org_id IN NUMBER,
439 p_arg_owning_org_id IN NUMBER,
440 p_arg_org_instance IN NUMBER,
441 p_arg_mode IN VARCHAR2,
442 p_arg_transaction_id IN NUMBER,
443 p_arg_loaded_ext_repair_orders IN OUT nocopy NUMBER,
444 p_load_type IN NUMBER);
445
446 END MSC_Rel_Plan_PUB;
[Home] [Help]