[Home] [Help]
PACKAGE BODY: APPS.MSC_RELEASE_PK
Source
1 PACKAGE BODY msc_release_pk AS
2 /* $Header: MSCARELB.pls 120.9.12010000.2 2008/12/26 19:22:05 ahoque ship $ */
3 WIP_DIS_MASS_LOAD CONSTANT INTEGER := 1;
4 PO_MASS_LOAD CONSTANT INTEGER := 8;
5 PO_REQUISITION CONSTANT INTEGER := 2;
6 WIP_DISCRETE_JOB CONSTANT INTEGER := 3;
7 MAKE CONSTANT INTEGER := 1;
8 BUY CONSTANT INTEGER := 2;
9 PLANNED_ORDER CONSTANT INTEGER := 5;
10 PLANNED_NEW_BUY_ORDER CONSTANT INTEGER:= 76;
11 PLANNED_IRO CONSTANT INTEGER:= 77;
12 PLANNED_ERO CONSTANT INTEGER:= 78;
13 PLANNED_TRANSFER CONSTANT INTEGER:=51;
14 CANCEL CONSTANT INTEGER := 2;
15 RESCHEDULE CONSTANT INTEGER := 2;
16 PURCHASE_ORDER CONSTANT INTEGER := 1;
17 UNRELEASED_NO_CHARGES CONSTANT INTEGER := 1;
18 STANDARD_ITEM CONSTANT INTEGER := 4;
19 NO_AUTO_RELEASE CONSTANT INTEGER := 5;
20 NO_KANBAN_RELEASE CONSTANT INTEGER := 6;
21
22 MRP_PLAN CONSTANT INTEGER := 1;
23 MPS_PLAN CONSTANT INTEGER := 2;
24 MPP_PLAN CONSTANT INTEGER := 3;
25 DRP_PLAN CONSTANT INTEGER := 5;
26 SRP_PLAN CONSTANT INTEGER:= 8;
27
28
29 MRP_PLANNED_ITEM CONSTANT INTEGER := 3;
30 MPS_PLANNED_ITEM CONSTANT INTEGER := 4;
31 MPPMRP_PLANNED_ITEM CONSTANT INTEGER := 7;
32 MPPMPS_PLANNED_ITEM CONSTANT INTEGER := 8;
33
34 NULL_VALUE CONSTANT INTEGER := -23453;
35 MAGIC_STRING CONSTANT VARCHAR2(10) := '734jkhJK24';
36 BUFFER_SIZE_LEN CONSTANT INTEGER := 1000000;
37 NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
38
39 SYS_YES CONSTANT INTEGER := 1;
40 SYS_NO CONSTANT INTEGER := 2;
41
42 G_APPS107 CONSTANT NUMBER := 1;
43 G_APPS110 CONSTANT NUMBER := 2;
44 G_APPS115 CONSTANT NUMBER := 3;
45
46 ERO_LOAD CONSTANT NUMBER := 128;
47 IRO_LOAD CONSTANT NUMBER := 256;
48 TRANSFER_LOAD CONSTANT NUMBER := 32;
49 DRP_REQ_RESCHED constant integer := 64; -- drp release
50
51
52 -- ========================================================================
53
54 var_released_instance_count NUMBER;
55
56 var_loaded_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
57 var_loaded_reqs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
58 var_loaded_scheds MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
59 var_resched_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
60 var_resched_reqs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
61 var_wip_req_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
62 var_req_load_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
63 var_req_resched_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
64 var_released_instance_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
65 var_loaded_lot_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
66 var_resched_lot_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
67 var_osfm_req_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
68 var_resched_eam_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
69 var_eam_req_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
70 var_loaded_int_reqs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
71 var_resched_int_reqs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
72 var_int_req_load_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
73 var_int_req_resched_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
74 var_loaded_int_repair_orders MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
75 var_int_repair_orders_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
76 var_loaded_ext_repair_orders MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
77 var_ext_repair_orders_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
78 var_released_instance msc_rel_wf.NumTblTyp:= msc_rel_wf.NumTblTyp(0);
79 var_po_res_id msc_rel_wf.NumTblTyp:= msc_rel_wf.NumTblTyp(0);
80 var_po_res_count msc_rel_wf.NumTblTyp:= msc_rel_wf.NumTblTyp(0);
81 var_po_pwb_count msc_rel_wf.NumTblTyp:= msc_rel_wf.NumTblTyp(0);
82 var_count_po NUMBER := 0;
83
84
85 -- ========================================================================
86 --
87 -- Selects the rows in MSC_SUPPLIES for all orgs in a given plan
88 -- that meet the auto-release criteria and release those planned orders and reschedules
89 --
90 -- ========================================================================
91
92 PROCEDURE msc_auto_release(
93 errbuf OUT NOCOPY VARCHAR2,
94 retcode OUT NOCOPY NUMBER,
95 arg_plan_id IN NUMBER,
96 arg_org_id IN NUMBER,
97 arg_instance_id IN NUMBER,
98 arg_use_start_date IN VARCHAR2) IS
99
100 VERSION CONSTANT CHAR(80) :=
101 '$Header: MSCARELB.pls 120.9.12010000.2 2008/12/26 19:22:05 ahoque ship $';
102
103 counter NUMBER := 0;
104
105 var_sql_stmt VARCHAR2(2000);
106
107 var_allow_release NUMBER;
108 var_instance_code VARCHAR2(10);
109
110 var_dblink VARCHAR2(128);
111 var_apps_ver NUMBER;
112 v_dblink VARCHAR2(128);
113
114 var_user_id NUMBER;
115 var_po_group_by NUMBER;
116 var_po_batch_number NUMBER;
117 var_wip_group_id NUMBER;
118 var_transaction_id NUMBER;
119 var_sr_instance_id NUMBER;
120 var_supplier_id NUMBER;
121 var_supplier_site_id NUMBER;
122 var_source_supplier_id NUMBER;
123 var_source_supplier_site_id NUMBER;
124 var_plan_id NUMBER;
125 var_start_date DATE;
126 var_demand_class VARCHAR(30);
127 var_wip_class_code VARCHAR(10);
128 var_wip_job_number NUMBER; -- <=== new
129 var_plan_type NUMBER;
130 var_make_buy_code NUMBER;
131 var_primary_uom_code VARCHAR(3);
132 var_planner_employee_id NUMBER;
133 var_employee_id NUMBER;
134 var_default_job_prefix VARCHAR(80);
135 var_job_prefix VARCHAR(80);
136 var_firm_jobs VARCHAR(80) := 'N';
137 var_impl_status_code NUMBER;
138 var_location_id NUMBER;
139 var_count NUMBER;
140 var_org_code VARCHAR(7);
141 var_org_id NUMBER;
142 var_prev_org_id NUMBER := -1;
143 var_inventory_item_id NUMBER;
144 var_sr_inventory_item_id NUMBER;
145 var_prev_inventory_item_id NUMBER := -1;
146 var_item VARCHAR(50);
147 var_new_schedule_date DATE;
148 var_new_order_quantity NUMBER;
149 var_order_type NUMBER;
150 var_load_type NUMBER;
151 var_planner_code VARCHAR(10);
152 var_debug BOOLEAN := FALSE;
153 var_entity VARCHAR(30);
154 var_buf VARCHAR2(2000);
155 var_project_id NUMBER;
156 var_sales_order_line_id NUMBER;
157
158 err_msg_1 VARCHAR2(30);
159 err_class_1 VARCHAR2(10);
160 err_msg_2 VARCHAR2(30);
161 err_class_2 VARCHAR2(10);
162
163 --Added to fix the bug#2538765
164 var_user_name VARCHAR2(100):= NULL;
165 var_resp_name VARCHAR2(100):= NULL;
166 var_application_name VARCHAR2(240):= NULL;
167 --Added to fix the bug#2538765
168
169 -- for 3298070
170 lv_plan_name VARCHAR2(100):=NULL;
171
172 v_comp_days_tol number := NVL(FND_PROFILE.VALUE('MSC_AUTO_REL_COMP_TOLERANCE'),0);
173
174 v_wip_group_id NUMBER;
175 v_po_batch_number NUMBER;
176
177 lv_plan_release_profile number;
178 invalid_plan EXCEPTION;
179
180 cursor plan_type_c(v_plan_id number) is
181 select curr_plan_type
182 from msc_plans
183 where plan_id = v_plan_id;
184
185 l_def_pref_id number;
186 l_plan_type number;
187 p_where_clause varchar2(3000):= NULL;
188 p_total_rows NUMBER;
189 p_succ_rows NUMBER;
190 p_error_rows NUMBER;
191
192
193 --
194 -- PLANNING_MAKE_BUY_CODE cannot be used to determine whether an item is
195 -- a make or buy item for time-phased make-buy item, instead use
196 --
197 -- Curr org Source org Item type
198 -- -------- ---------- ---------
199 -- A.X A.X Make (always populated for Make item) - Same Org in Same Instance
200 -- A.X B.X Buy ( Same Org-Id across Instances)
201 -- X Y Buy
202 -- A.X B.Y Buy ( Diff Org-Id across Instances)
203 -- X - Buy (for Vendor)
204 --
205 -- MRP_PLANNING_CODE cannot be used to determine whether or not an item
206 -- should be released based on the plan type (MPS/MRP) for Supply Chain
207 -- Planning, instead use
208 --
209 -- In source plan Release
210 -- -------------- -------
211 -- Yes No (items from input supply schedules)
212 -- No Yes (items from input demand schedules)
213 --
214
215 CURSOR planned_orders_and_reschedules IS
216 SELECT
217 mr.transaction_id,
218 mr.sr_instance_id,
219 mr.plan_id,
220 mr.organization_id,
221 mr.inventory_item_id,
222 nvl(mr.supplier_id,mt.modeled_supplier_id),
223 nvl(mr.supplier_site_id,mt.modeled_supplier_site_id),
224 nvl(mr.source_supplier_id,mt.modeled_supplier_id),
225 nvl(mr.source_supplier_site_id,mt.modeled_supplier_site_id),
226 mr.new_schedule_date,
227 mr.new_order_quantity,
228 mr.order_type,
229 decode(mr.order_type,
230 PLANNED_ORDER, decode(decode(mr.sr_instance_id,
231 mr.source_sr_instance_id, DECODE(mr.source_organization_id,mr.organization_id,MAKE,BUY),
232 BUY),
233 MAKE, decode(CFM_ROUTING_FLAG, 3,5,1),
234 BUY, 8),
235 PLANNED_NEW_BUY_ORDER, decode(decode(mr.sr_instance_id,
236 mr.source_sr_instance_id, DECODE(mr.source_organization_id,mr.organization_id,MAKE,BUY),
237 BUY),
238 MAKE, decode(CFM_ROUTING_FLAG, 3,5,1),
239 BUY, 8),
240 PO_REQUISITION, 16,
241 PURCHASE_ORDER, 20,
242 WIP_DISCRETE_JOB, decode(CFM_ROUTING_FLAG, 3,6,4)),
243 msi.uom_code,
244 mpl.employee_id,
245 decode(mr.sr_instance_id,mr.source_sr_instance_id,
246 DECODE(mr.source_organization_id,mr.organization_id,1,2),
247 2),
248 msi.sr_inventory_item_id,
249 nvl(mr.implement_project_id,mr.project_id)
250 FROM msc_calendar_dates cal1,
251 msc_calendar_dates cal2,
252 msc_planners mpl,
253 msc_trading_partners mparam,
254 msc_system_items msi,
255 msc_system_items rsi,
256 msc_supplies mr,
257 msc_plan_organizations_v mpo,
258 msc_plans mps,
259 msc_trading_partners mt
260 WHERE mpo.organization_id = arg_org_id
261 AND mpo.plan_id = arg_plan_id
262 AND mpo.sr_instance_id = arg_instance_id
263 AND mr.sr_instance_id = mpo.sr_instance_id
264 AND mr.organization_id = mpo.planned_organization
265 AND mr.plan_id = mpo.plan_id
266 AND mr.plan_id = mps.plan_id
267 --for bug#2881012
268 AND ( mr.order_type in (PLANNED_ORDER, PLANNED_NEW_BUY_ORDER)
269 OR ( mr.order_type IN (PURCHASE_ORDER,PO_REQUISITION,WIP_DISCRETE_JOB)
270 and mps.release_reschedules = SYS_YES
271 and ( mr.disposition_status_type = CANCEL -- 2 is cancel
272 or ( mr.reschedule_flag = RESCHEDULE -- 2 => reschedule
273 and mr.new_schedule_date <> mr.old_schedule_date
274 )
275 )
276 )
277 )
278 AND (NVL(mr.schedule_compress_days, 0) = 0 OR
279 mr.schedule_compress_days <= v_comp_days_tol )
280 AND decode(nvl(mr.reschedule_days,0),0,mr.new_order_placement_date,decode(sign(trunc(mr.new_order_placement_date)-trunc(mr.old_order_placement_date)),1,mr.old_order_placement_date,-1,mr.new_order_placement_date))
281 BETWEEN TRUNC(var_start_date) AND TRUNC(cal2.calendar_date)
282 AND msi.organization_id = mr.organization_id
283 AND msi.sr_instance_id = mr.sr_instance_id
284 AND msi.inventory_item_id = mr.inventory_item_id
285 AND msi.plan_id = -1
286 AND msi.bom_item_type = 4
287 AND NVL(msi.release_time_fence_code, 5) NOT IN (5,6)
288 /* for bug#2881012 following is not for reschedules. Hence
289 reschedules skip these filters with mr.order_type <> PLANNED_ORDER*/
290 AND
291 ( mr.order_type not in (PLANNED_ORDER,PLANNED_NEW_BUY_ORDER)
292 OR (
293 ( msi.build_in_wip_flag = 1
294 AND msi.repetitive_type = 1 /* 1:NO, 2:YES */
295 AND decode(mr.sr_instance_id,mr.source_sr_instance_id,
296 DECODE(mr.source_organization_id, mr.organization_id, 1,2)
297 ,2) = 1
298 )
299 OR ( msi.purchasing_enabled_flag = 1
300 AND decode(mr.sr_instance_id,mr.source_sr_instance_id,
301 DECODE(mr.source_organization_id, mr.organization_id, 1, 2)
302 ,2) = 2
303 )
304 )
305 )
306 AND rsi.organization_id = mr.organization_id
307 AND rsi.plan_id = mr.plan_id
308 AND rsi.sr_instance_id = mr.sr_instance_id
309 AND rsi.inventory_item_id = mr.inventory_item_id
310 AND ( NVL(rsi.in_source_plan, 2) <> 1
311 OR
312 mr.transaction_id in (
313 select a.transaction_id from msc_supplies a, msc_supplies b,MSC_DESIGNATORS d, msc_system_items e
314 where a.schedule_designator_id is not null
315 and a.schedule_designator_id = b.schedule_designator_id
316 and a.organization_id = mr.organization_id
317 and a.organization_id = b.organization_id
318 and a.transaction_id= b.transaction_id
319 and a.sr_instance_id =b.sr_instance_id
320 and b.plan_id=-1
321 and a.inventory_item_id =e.inventory_item_id
322 and a.plan_id = e.plan_id
323 and a.sr_instance_id = e.sr_instance_id
324 and a.organization_id = e.organization_id
325 and NVL(e.in_source_plan, 2) =1
326 and a.plan_id=mr.plan_id
327 and b.SCHEDULE_ORIGINATION_TYPE =1
328 and a.schedule_designator_id = d.DESIGNATOR_ID
329 and d.designator not in (SELECT DISTINCT compile_designator
330 FROM MSC_PLANS
331 where sr_instance_id=a.sr_instance_id )
332
333 ))
334 AND decode(mps.curr_plan_type,
335 SRP_PLAN,SYS_YES,
336 DRP_PLAN, SYS_YES,
337 MRP_PLAN, decode(lv_plan_release_profile,
338 SYS_YES, SYS_YES,
339 decode(rsi.mrp_planning_code,
340 MRP_PLANNED_ITEM, SYS_YES,
341 MPPMRP_PLANNED_ITEM, SYS_YES,
342 SYS_NO
343 )
344 ),
345 MPS_PLAN, decode(lv_plan_release_profile,
346 SYS_YES, decode(rsi.mrp_planning_code,
347 MRP_PLANNED_ITEM, SYS_NO,
348 SYS_YES
349 ),
350 decode(rsi.mrp_planning_code,
351 MPS_PLANNED_ITEM, SYS_YES,
352 MPPMPS_PLANNED_ITEM,SYS_YES,
353 SYS_NO
354 )
355 ),
356 MPP_PLAN, decode(rsi.mrp_planning_code,
357 MPS_PLANNED_ITEM, SYS_NO,
358 MRP_PLANNED_ITEM, SYS_NO,
359 SYS_YES
360 ),
361 SYS_NO
362 ) = SYS_YES
363 AND mpl.organization_id (+) = msi.organization_id
364 AND mpl.planner_code (+) = NVL(msi.planner_code, MAGIC_STRING)
365 AND mpl.sr_instance_id(+) = msi.sr_instance_id
366 AND mparam.sr_tp_id = mr.organization_id
367 AND mparam.sr_instance_id= mr.sr_instance_id
368 AND mparam.partner_type= 3
369 AND cal1.sr_instance_id= mr.sr_instance_id
370 AND cal1.calendar_code = mparam.calendar_code
371 AND cal1.exception_set_id = mparam.calendar_exception_set_id
372 AND cal1.calendar_date = TRUNC(var_start_date)
373 AND cal2.sr_instance_id = mr.sr_instance_id
374 AND cal2.calendar_code = cal1.calendar_code
375 AND cal2.exception_set_id = cal1.exception_set_id
376 AND cal2.seq_num = cal1.next_seq_num +
377 NVL(DECODE(msi.release_time_fence_code,
378 1, msi.cumulative_total_lead_time,
379 2, msi.cum_manufacturing_lead_time,
380 3, msi.full_lead_time,
381 4, msi.release_time_fence_days,
382 0),
383 0)
384 -- bug fix for 2261963 to filter planned orders that have already been released --
385 AND NVL(mr.implemented_quantity, 0) + NVL(mr.quantity_in_process, 0)
386 < mr.new_order_quantity
387 AND mt.sr_instance_id(+) = mr.source_sr_instance_id
388 AND mt.sr_tp_id(+) = mr.source_organization_id
389 AND mt.partner_type(+) = 3
390 AND (mr.releasable = MSC_Rel_Plan_PUB.RELEASABLE or mr.releasable is null )
391 AND mr.batch_id is NULL
392 -- shikyu changes
393 AND not exists (select 1 from msc_system_items msi1 , msc_trading_partners mtp
394 where msi1.inventory_item_id = mr.inventory_item_id
395 and msi1.organization_id = mr.organization_id
396 and msi1.plan_id = mr.plan_id
397 AND msi1.sr_instance_id = mr.sr_instance_id
398 and nvl(msi1.release_time_fence_code,-1) = 7
399 and mtp.sr_tp_id = msi1.organization_id
400 and mtp.sr_instance_id = msi1.sr_instance_id
401 and mtp.partner_type=3
402 and (mtp.modeled_supplier_id is not null OR mtp.modeled_supplier_site_id is not null))
403 ORDER BY 2;
404
405 cursor ROs_to_release is
406 Select
407 ms.Transaction_id,
408 ms.order_type,
409 ms.sr_instance_id ,
410 ms.plan_id
411 from
412 msc_supplies ms,
413 msc_system_items msi,
414 msc_trading_partners mtp,
418 ms.plan_id =arg_plan_id
415 msc_calendar_dates cal1,
416 msc_calendar_dates cal2
417 where
419 and ms.order_type in (PLANNED_IRO,PLANNED_ERO)
420 and ms.inventory_item_id = msi.inventory_item_id
421 and ms.sr_instance_id =msi.sr_instance_id
422 and msi.plan_id = arg_plan_id
423 and msi.bom_item_type = 4
424 and msi.release_time_fence_code NOT IN (5,6)
425 and ms.organization_id =msi.organization_id
426 and mtp.sr_tp_id = ms.organization_id
427 and mtp.sr_instance_id= ms.sr_instance_id
428 and mtp.partner_type= 3
429 and cal1.sr_instance_id = ms.sr_instance_id
430 and cal1.calendar_code = mtp.calendar_code
431 and cal1.exception_set_id = mtp.calendar_exception_set_id
432 and cal1.calendar_date = TRUNC(var_start_date)
433 and cal2.sr_instance_id = ms.sr_instance_id
434 and cal2.calendar_code = cal1.calendar_code
435 and cal2.exception_set_id = cal1.exception_set_id
436 and cal2.seq_num = cal1.next_seq_num +
437 NVL(DECODE(msi.release_time_fence_code,
438 1, msi.cumulative_total_lead_time,
439 2, msi.cum_manufacturing_lead_time,
440 3, msi.full_lead_time,
441 4, msi.release_time_fence_days,
442 0),
443 0)
444 and nvl(ms.new_order_placement_date, ms.new_schedule_date) BETWEEN TRUNC(var_start_date)
445 and TRUNC(cal2.calendar_date)
446 and (ms.releasable =0 or ms.releasable is null)
447 and ms.batch_id is null;
448
449 cursor transfer_orders_to_release is
450 Select
451 ms.Transaction_id,
452 ms.order_type,
453 ms.sr_instance_id ,
454 ms.plan_id
455 from
456 msc_supplies ms,
457 msc_system_items msi,
458 msc_trading_partners mtp,
459 msc_calendar_dates cal1,
460 msc_calendar_dates cal2
461 where
462 ms.plan_id =arg_plan_id
463 and ms.order_type in (PLANNED_TRANSFER)
464 and ms.inventory_item_id = msi.inventory_item_id
465 and ms.sr_instance_id =msi.sr_instance_id
466 and msi.plan_id = arg_plan_id
467 and msi.bom_item_type = 4
468 and msi.release_time_fence_code NOT IN (5,6)
469 and ms.organization_id =msi.organization_id
470 and mtp.sr_tp_id = ms.organization_id
471 and mtp.sr_instance_id= ms.sr_instance_id
472 and mtp.partner_type= 3
473 and cal1.sr_instance_id = ms.sr_instance_id
474 and cal1.calendar_code = mtp.calendar_code
475 and cal1.exception_set_id = mtp.calendar_exception_set_id
476 and cal1.calendar_date = TRUNC(var_start_date)
477 and cal2.sr_instance_id = ms.sr_instance_id
478 and cal2.calendar_code = cal1.calendar_code
479 and cal2.exception_set_id = cal1.exception_set_id
480 and cal2.seq_num = cal1.next_seq_num +
481 NVL(DECODE(msi.release_time_fence_code,
482 1, msi.cumulative_total_lead_time,
483 2, msi.cum_manufacturing_lead_time,
484 3, msi.full_lead_time,
485 4, msi.release_time_fence_days,
486 0),
487 0)
488 and nvl(ms.new_order_placement_date, ms.new_schedule_date) BETWEEN TRUNC(var_start_date)
489 and TRUNC(cal2.calendar_date)
490 and (ms.releasable =0 or ms.releasable is null)
491 and ms.batch_id is null
492 and
493 not exists (select 1 from msc_full_pegging mfp,
494 msc_demands md,
495 msc_supplies ms1
496 where mfp.sr_instance_id = ms.sr_instance_id and
497 mfp.plan_id = ms.plan_id and
498 mfp.transaction_id =ms.transaction_id and
499 mfp.demand_id =md.demand_id and
500 mfp.sr_instance_id = md.sr_instance_id and
501 mfp.plan_id = md.plan_id and
502 md.origination_type =78 and
503 md.disposition_id = ms1.transaction_id and
504 md.sr_instance_id =ms1.sr_instance_id and
505 md.plan_id = ms1.plan_id and
506 ms1.order_type =79 and
507 rownum <2);
508
509 BEGIN
510 retcode := 0;
511 errbuf := NULL;
512
513 select decode(FND_PROFILE.VALUE('MSC_DRP_RELEASE_FROM_MRP'),'Y',SYS_YES,SYS_NO)
514 into lv_plan_release_profile
515 from dual;
516
517 var_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
518
519 SELECT ALLOW_RELEASE_FLAG,
520 INSTANCE_CODE,
521 APPS_VER,
522 DECODE( M2A_DBLINK,
523 NULL, NULL_DBLINK,
524 '@'||M2A_DBLINK)
525 INTO var_allow_release,
526 var_instance_code,
527 var_apps_ver,
528 var_dblink
529 FROM MSC_APPS_INSTANCES
533
530 WHERE INSTANCE_ID= arg_instance_id;
531
532 if ( var_apps_ver >= 3 ) then
534 IF var_allow_release = 2 THEN
535
536 var_sql_stmt:=
537 'SELECT mar.A2M_DBLINK '
538 ||' FROM MRP_AP_APPS_INSTANCES_ALL'||var_dblink||' mar'
539 ||' WHERE mar.ALLOW_RELEASE_FLAG = 1 ';
540
541 EXECUTE IMMEDIATE var_sql_stmt INTO v_dblink ;
542
543 fnd_message.set_name('MSC', 'MSC_AUTO_RELEASE_WARN');
544 fnd_message.set_token('A2M_DBLINK',v_dblink);
545 fnd_message.set_token('SOURCE_INSTANCE',var_instance_code);
546 var_buf:= fnd_message.get;
547 fnd_file.put_line(FND_FILE.LOG, var_buf);
548
549
550 retcode := 1;
551 errbuf := var_buf;
552 RETURN;
553
554 END IF;
555
556 end if;
557
558 -- ------------------------------------------------------------------------
559 -- Validate the plan
560 -- ------------------------------------------------------------------------
561 BEGIN
562 var_entity := 'Plan Validation';
563 msc_valid_plan_pkg.msc_valid_plan(arg_plan_id, 'Y', 'Y', 'Y', 'N');
564
565 EXCEPTION
566 WHEN OTHERS THEN
567 fnd_message.set_name('MRP', 'UNSUCCESSFUL PLAN VALIDATION');
568 var_buf:= fnd_message.get;
569 fnd_file.put_line(FND_FILE.LOG, var_buf);
570
571 retcode := 2;
572 errbuf := var_buf;
573 RETURN;
574 END;
575
576 -- ------------------------------------------------------------------------
577 -- Setup
578 -- ------------------------------------------------------------------------
579
580 SELECT DECODE( M2A_DBLINK, NULL, ' ', '@'||M2A_DBLINK),
581 APPS_VER
582 INTO var_dblink,
583 var_apps_ver
584 FROM MSC_APPS_INSTANCES
585 WHERE INSTANCE_ID= arg_instance_id;
586
587 --Fix for the bug#2538765. This enables the auto-release to use the user level
588 --profile value defined in the source instance.
589
590 SELECT FND_GLOBAL.USER_NAME,
591 FND_GLOBAL.RESP_NAME,
592 FND_GLOBAL.APPLICATION_NAME
593 INTO var_user_name,
594 var_resp_name,
595 var_application_name
596 FROM dual;
597
598 var_sql_stmt:=
599 'BEGIN'
600 ||' MRP_AP_REL_PLAN_PUB.INITIALIZE'||var_dblink
601 ||'( :var_user_name,'
602 ||' :var_resp_name,'
603 ||' :var_application_name,'
604 ||' :v_wip_group_id,'
605 ||' :v_po_batch_number);'
606
607 ||'END;';
608
609 EXECUTE IMMEDIATE var_sql_stmt
610 USING var_user_name,
611 var_resp_name,
612 IN var_application_name,
613 OUT v_wip_group_id,
614 OUT v_po_batch_number;
615
616
617 var_sql_stmt:=
618 'SELECT FND_GLOBAL.USER_ID,'
619 ||' FND_PROFILE.VALUE'||var_dblink||'(''WIP_JOB_PREFIX''),'
620 ||' FND_PROFILE.VALUE'||var_dblink||'(''MRP_LOAD_REQ_GROUP_BY'')'
621 ||' FROM DUAL';
622
623 EXECUTE IMMEDIATE var_sql_stmt INTO var_user_id,
624 var_default_job_prefix,
625 var_po_group_by;
626
627 var_entity := 'Setup';
628
629 SELECT mp.curr_plan_type,
630 DECODE(UPPER(arg_use_start_date),
631 'Y', mp.plan_start_date, 'N', sysdate, sysdate),
632 sched.demand_class,
633 mp.compile_designator
634 INTO var_plan_type,
635 var_start_date,
636 var_demand_class,
637 lv_plan_name
638 FROM msc_plans mp,
639 msc_designators sched
640 WHERE sched.organization_id(+)= mp.organization_id
641 AND sched.designator(+) = mp.compile_designator
642 AND sched.sr_instance_id(+) = mp.sr_instance_id
643 AND mp.plan_id = arg_plan_id;
644
645 IF var_debug THEN
646 var_buf := '+++++++++++++++++';
647 fnd_file.put_line(FND_FILE.LOG, var_buf);
648 var_buf := 'User ID : '||var_user_id;
649 fnd_file.put_line(FND_FILE.LOG, var_buf);
650 var_buf := 'Owning org : '||arg_org_id;
651 fnd_file.put_line(FND_FILE.LOG, var_buf);
652 var_buf := 'Plan Name : '||lv_plan_name;
653 fnd_file.put_line(FND_FILE.LOG, var_buf);
654 var_buf := 'Plan type : '||var_plan_type;
655 fnd_file.put_line(FND_FILE.LOG, var_buf);
656 var_buf := 'Use start date : '||arg_use_start_date;
657 fnd_file.put_line(FND_FILE.LOG, var_buf);
658 var_buf := 'Start date : '||var_start_date;
659 fnd_file.put_line(FND_FILE.LOG, var_buf);
660 var_buf := 'WIP default job prefix : '||var_default_job_prefix;
661 fnd_file.put_line(FND_FILE.LOG, var_buf);
662 var_buf := 'WIP group ID : '||var_wip_group_id;
663 fnd_file.put_line(FND_FILE.LOG, var_buf);
664 var_buf := 'PO group by : '||var_po_group_by;
665 fnd_file.put_line(FND_FILE.LOG, var_buf);
666 var_buf := 'PO batch number : '||var_po_batch_number;
670 END IF;
667 fnd_file.put_line(FND_FILE.LOG, var_buf);
668 var_buf := 'Demand Class : '||var_demand_class;
669 fnd_file.put_line(FND_FILE.LOG, var_buf);
671
672 begin
673 -- populating batch_id from destination side seq.
674 Execute immediate 'select mrp_workbench_query_s.nextval
675 FROM DUAL '
676 into MSC_Rel_Plan_PUB.g_batch_id;
677 exception when others then
678 fnd_file.put_line(FND_FILE.LOG, sqlerrm);
679 end;
680
681 fnd_file.put_line(FND_FILE.LOG, MSC_Rel_Plan_PUB.g_batch_id);
682 -- ------------------------------------------------------------------------
683 -- Get planned orders and reschedules that meet the auto-release criteria
684 -- ------------------------------------------------------------------------
685 var_entity := 'Planned Orders';
686
687 OPEN planned_orders_and_reschedules;
688
689 LOOP
690
691 var_entity := 'Fetch Planned Orders';
692
693 FETCH planned_orders_and_reschedules INTO var_transaction_id,
694 var_sr_instance_id,
695 var_plan_id,
696 var_org_id,
697 var_inventory_item_id,
698 var_supplier_id,
699 var_supplier_site_id,
700 var_source_supplier_id,
701 var_source_supplier_site_id,
702 var_new_schedule_date,
703 var_new_order_quantity,
704 var_order_type,
705 var_load_type,
706 var_primary_uom_code,
707 var_planner_employee_id,
708 var_make_buy_code,
709 var_sr_inventory_item_id,
710 var_project_id;
711
712 EXIT WHEN planned_orders_and_reschedules%NOTFOUND;
713
714
715 UPDATE msc_supplies
716 SET batch_id = MSC_Rel_Plan_PUB.g_batch_id
717 WHERE transaction_id = var_transaction_id
718 AND sr_instance_id = var_sr_instance_id
719 AND plan_id = var_plan_id ;
720
721 /*VMI Check -- fwd port 3181822 */
722
723 IF (not (((var_order_type = 5) AND (var_make_buy_code = BUY and
724 (MSC_UTIL.get_vmi_flag(var_plan_id,
725 var_sr_instance_id,
726 var_org_id,
727 var_inventory_item_id,
728 var_source_supplier_id,
729 var_source_supplier_site_id)= 1)))
730 OR (var_order_type in (1,2) AND (MSC_UTIL.get_vmi_flag(var_plan_id,
731 var_sr_instance_id,
732 var_org_id,
733 var_inventory_item_id,
734 var_supplier_id,
735 var_supplier_site_id)= 1)))) THEN
736
737
738 -- ----------------------------------------------------------------------
739 -- Get organization dependent info
740 -- ----------------------------------------------------------------------
741 IF (var_org_id <> var_prev_org_id) THEN
742
743 var_entity := 'PO Location';
744
745 BEGIN
746 SELECT DECODE(tps.sr_tp_site_id, -1, NULL,tps.sr_tp_site_id)
747 INTO var_location_id
748 FROM msc_trading_partner_sites tps
749 WHERE tps.sr_tp_id = var_org_id
750 AND tps.sr_instance_id= arg_instance_id
751 AND tps.partner_type= 3;
752
753 EXCEPTION
754 WHEN NO_DATA_FOUND THEN
755 var_location_id := NULL;
756 WHEN OTHERS THEN
757 var_buf := var_entity||': '||sqlerrm;
758 fnd_file.put_line(FND_FILE.LOG, var_buf);
759
760 ROLLBACK;
761
762 CLOSE planned_orders_and_reschedules;
763
764 retcode := 2;
765 errbuf := var_buf;
766 RETURN;
767 END;
768
769 IF var_debug THEN
770 var_buf := '=================';
771 fnd_file.put_line(FND_FILE.LOG, var_buf);
772 var_buf := 'Organization ID : '||var_org_id;
773 fnd_file.put_line(FND_FILE.LOG, var_buf);
774 var_buf := 'Location ID : '||var_location_id;
775 fnd_file.put_line(FND_FILE.LOG, var_buf);
776 END IF;
777 END IF; /* Org ID */
778
779 -- ----------------------------------------------------------------------
780 -- Get item dependent info
781 -- ----------------------------------------------------------------------
782 IF (var_prev_inventory_item_id <> var_inventory_item_id) THEN
783
784 var_sql_stmt:=
785 'BEGIN'
786 ||' :var_wip_class_code := wip_common.default_acc_class'||var_dblink||'('
787 ||' :var_org_id,'
788 ||' :var_sr_inventory_item_id,'
789 ||' 1,'
790 ||' :var_project_id,'
791 ||' :err_msg_1,'
795 ||' END;';
792 ||' :err_class_1,'
793 ||' :err_msg_2,'
794 ||' :err_class_2);'
796
797 EXECUTE IMMEDIATE var_sql_stmt
798 USING OUT var_wip_class_code,
799 IN var_org_id,
800 IN var_sr_inventory_item_id,
801 IN var_project_id,
802 OUT err_msg_1,
803 OUT err_class_1,
804 OUT err_msg_2,
805 OUT err_class_2;
806
807
808 IF (var_wip_class_code is NULL) THEN
809 BEGIN
810
811 var_entity := 'WIP Discrete Class';
812
813 var_sql_stmt:=
814 'SELECT wp.default_discrete_class'
815 ||' FROM wip_parameters'||var_dblink||' wp'
816 ||' WHERE wp.organization_id = :var_org_id';
817
818 EXECUTE IMMEDIATE var_sql_stmt
819 INTO var_wip_class_code
820 USING var_org_id;
821
822 EXCEPTION
823 WHEN NO_DATA_FOUND THEN
824 var_wip_class_code := NULL;
825 WHEN OTHERS THEN
826 var_buf := var_entity||': '||sqlerrm;
827 fnd_file.put_line(FND_FILE.LOG, var_buf);
828
829 ROLLBACK;
830 CLOSE planned_orders_and_reschedules;
831 retcode := 2;
832 errbuf := var_buf;
833 RETURN;
834 END;
835 END IF;
836 END IF; /* Item Id */
837
838 IF (var_make_buy_code = MAKE OR var_order_type = WIP_DISCRETE_JOB) THEN
839
840 -- --------------------------------------------------------------------
841 -- Get WIP Jobs parameter
842 -- --------------------------------------------------------------------
843 -- IF var_planner_employee_id IS NULL THEN
844
845 -- var_impl_status_code := UNRELEASED_NO_CHARGES;
846 -- var_job_prefix := var_default_job_prefix;
847
848 -- ELSE
849
850 BEGIN
851 var_entity := 'WIP Job Status';
852 open Plan_type_c(var_plan_id);
853 fetch plan_type_c into l_plan_type;
854 close plan_type_c;
855 l_def_pref_id := msc_get_name.get_default_pref_id(var_user_id);
856 var_impl_status_code:= msc_get_name.GET_preference('ORDERS_DEFAULT_JOB_STATUS', l_def_pref_id, l_plan_type);
857 var_wip_class_code:= msc_get_name.GET_preference('JOB_CLASS_CODE', l_def_pref_id, l_plan_type);
858 var_firm_jobs:= msc_get_name.GET_preference('ORDERS_FIRM_JOBS', l_def_pref_id, l_plan_type);
859 /*
860 OPEN job_status;
861 FETCH job_status INTO var_impl_status_code, var_wip_class_code,
862 var_firm_jobs;
863 */
864
865
866 var_job_prefix := var_default_job_prefix; /* Bug 2571601 */
867
868 IF var_impl_status_code is null THEN
869 var_impl_status_code := UNRELEASED_NO_CHARGES;
870 var_job_prefix := var_default_job_prefix;
871 END IF;
872
873 -- CLOSE job_status;
874 END;
875
876 -- END IF;
877
878 IF var_debug THEN
879 var_buf := '----------------- ';
880 fnd_file.put_line(FND_FILE.LOG, var_buf);
881 var_buf := 'Item ID : '||var_inventory_item_id;
882 fnd_file.put_line(FND_FILE.LOG, var_buf);
883 var_buf := 'Item Make/Buy : '||var_make_buy_code;
884 fnd_file.put_line(FND_FILE.LOG, var_buf);
885 var_buf := 'WIP class code : '||var_wip_class_code;
886 fnd_file.put_line(FND_FILE.LOG, var_buf);
887 var_buf := 'Date : '||var_new_schedule_date;
888 fnd_file.put_line(FND_FILE.LOG, var_buf);
889 var_buf := 'Quantity : '||var_new_order_quantity;
890 fnd_file.put_line(FND_FILE.LOG, var_buf);
891 var_buf := 'Planner emp ID : '||var_planner_employee_id;
892 fnd_file.put_line(FND_FILE.LOG, var_buf);
893 var_buf := 'WIP job status : '||var_impl_status_code;
894 fnd_file.put_line(FND_FILE.LOG, var_buf);
895 var_buf := 'WIP job prefix : '||var_job_prefix;
896 fnd_file.put_line(FND_FILE.LOG, var_buf);
897 var_buf := 'Order Type : '||var_order_type;
898 fnd_file.put_line(FND_FILE.LOG, var_buf);
899 END IF;
900
901 -- --------------------------------------------------------------------
902 -- -- Update WIP Jobs planned orders and reschedules
903 -- --------------------------------------------------------------------
904 var_entity := 'WIP Planned Orders';
905 IF(var_order_type=PLANNED_ORDER)
906 /*discrete job reschedules don't require new name*/
907 THEN
908 var_sql_stmt:=
909 'SELECT wip_job_number_s.nextval'||var_dblink||' FROM dual';
910
911 EXECUTE IMMEDIATE var_sql_stmt
912 INTO var_wip_job_number;
913 END IF;
914
915 UPDATE msc_supplies
916 SET old_order_quantity = new_order_quantity,
917 quantity_in_process = new_order_quantity,
918 implement_date = new_schedule_date,
919 implement_quantity = new_order_quantity,
923 implement_job_name = decode(var_order_type,PLANNED_ORDER,var_job_prefix||to_char(var_wip_job_number)
920 implement_firm = DECODE(var_firm_jobs, 'Y', 1, 2),
921
922 /*for discrete job reschedules existing name is populated */
924 ,PLANNED_NEW_BUY_ORDER,var_job_prefix||to_char(var_wip_job_number),3,order_number),
925
926 /*implement status code must be set to cancel (7) in case of reschedules */
927 implement_status_code = DECODE(disposition_status_type, CANCEL,7, var_impl_status_code),
928 load_type = var_load_type,
929
930 /*this is 1 for reschedules */
931 reschedule_flag=decode(order_type,PLANNED_ORDER,reschedule_flag,PLANNED_NEW_BUY_ORDER,reschedule_flag,1),
932
933 /*for reschedules it is 1 */
934 implement_as = decode(order_type,PLANNED_ORDER,WIP_DISCRETE_JOB,PLANNED_NEW_BUY_ORDER,WIP_DISCRETE_JOB,1),
935 implement_wip_class_code = var_wip_class_code,
936 implement_source_org_id = NULL,
937 implement_supplier_id = NULL,
938 implement_supplier_site_id = NULL,
939 implement_project_id = project_id,
940 implement_task_id = task_id,
941 implement_unit_number = unit_number,
942 implement_demand_class = var_demand_class,
943 last_updated_by = var_user_id
944 WHERE transaction_id = var_transaction_id
945 AND sr_instance_id = var_sr_instance_id
946 AND plan_id = var_plan_id
947 AND batch_id=msc_rel_plan_pub.g_batch_id;
948
949 ELSIF (var_make_buy_code = BUY OR var_order_type IN (PURCHASE_ORDER,PO_REQUISITION)) THEN
950
951 -- IR/ISO enhancement.
952 -- For DRP/ASCP plan, we need to check if the ISO is present in
953 -- the same plan. If, so, we need to update the load_type to 64
954
955 IF (var_order_type = PO_REQUISITION) THEN
956 BEGIN
957 var_sales_order_line_id := NULL;
958 SELECT sales_order_line_id
959 INTO var_sales_order_line_id
960 FROM msc_demands
961 WHERE
962 plan_id = var_plan_id
963 AND sr_instance_id = var_sr_instance_id
964 AND disposition_id = var_transaction_id
965 and origination_type = 30
966 and rownum = 1;
967
968 EXCEPTION
969 WHEN NO_DATA_FOUND THEN
970 var_sales_order_line_id := NULL;
971 END;
972
973 IF (var_sales_order_line_id IS NOT NULL) THEN
974 var_load_type := DRP_REQ_RESCHED;
975 END IF;
976
977 END IF;
978
979 -- --------------------------------------------------------------------
980 -- Verify PO Reqs parameters
981 --
982 -- If Planner is not an active employee, do not release
983 -- --------------------------------------------------------------------
984 var_entity := 'Employee';
985
986 SELECT count(*)
987 INTO var_count
988 FROM msc_planners
989 WHERE employee_id = var_planner_employee_id
990 AND current_employee_flag= 1;
991
992 IF var_debug THEN
993 var_buf := '----------------- ';
994 fnd_file.put_line(FND_FILE.LOG, var_buf);
995 var_buf := 'Item ID : '||var_inventory_item_id;
996 fnd_file.put_line(FND_FILE.LOG, var_buf);
997 var_buf := 'Item Make/Buy : '||var_make_buy_code;
998 fnd_file.put_line(FND_FILE.LOG, var_buf);
999 var_buf := 'Date : '||var_new_schedule_date;
1000 fnd_file.put_line(FND_FILE.LOG, var_buf);
1001 var_buf := 'Quantity : '||var_new_order_quantity;
1002 fnd_file.put_line(FND_FILE.LOG, var_buf);
1003 var_buf := 'Planner emp ID : '||var_planner_employee_id;
1004 fnd_file.put_line(FND_FILE.LOG, var_buf);
1005 var_buf := 'Active employee : '||var_count;
1006 fnd_file.put_line(FND_FILE.LOG, var_buf);
1007 var_buf := 'Order Type : '||var_order_type;
1008 fnd_file.put_line(FND_FILE.LOG, var_buf);
1009 END IF;
1010
1011 IF (var_count = 0) THEN
1012
1013 -- PO Req is not released because Planner is not an active employee
1014
1015 var_entity := 'Non-released PO Req';
1016
1017 SELECT param.organization_code,
1018 msi.item_name,
1019 msi.planner_code
1020 INTO var_org_code, var_item, var_planner_code
1021 FROM msc_system_items msi,
1022 msc_trading_partners param,
1023 msc_supplies mr
1024 WHERE mr.transaction_id = var_transaction_id
1025 AND mr.sr_instance_id = var_sr_instance_id
1026 AND mr.plan_id = var_plan_id
1027 AND msi.organization_id = mr.organization_id
1028 AND msi.inventory_item_id = mr.inventory_item_id
1029 AND msi.sr_instance_id = mr.sr_instance_id
1030 AND msi.plan_id = -1
1031 AND param.sr_tp_id = mr.organization_id
1032 AND param.sr_instance_id = mr.sr_instance_id
1033 AND param.partner_type= 3;
1034
1035 /* bug 4258346 - set retcode = 1 so that the program will complete in warning
1036 if the planner is not an active employee */
1037
1038 retcode := 1;
1039
1040 var_buf := '................. ';
1041 fnd_file.put_line(FND_FILE.LOG, var_buf);
1042
1043 fnd_message.set_name('MRP', 'MRP_UNRELEASED_ORDER1');
1044 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1045
1046 fnd_message.set_name('MRP', 'MRP_UNRELEASED_ORDER2');
1047 fnd_message.set_token('PLANNER_VALUE', var_planner_code);
1048 fnd_message.set_token('ORG_VALUE', var_org_code);
1049 fnd_message.set_token('ITEM_VALUE', var_item);
1050 fnd_message.set_token('DATE_VALUE', to_char(var_new_schedule_date));
1051 fnd_message.set_token('QTY_VALUE', to_char(var_new_order_quantity));
1052 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1053
1054 ELSE
1055
1056 -- ------------------------------------------------------------------
1057 -- Update PO Reqs planned orders and reschedules
1058 -- ------------------------------------------------------------------
1059 var_entity := 'PO Planned Orders';
1060
1061 UPDATE msc_supplies
1062 SET old_order_quantity = new_order_quantity,
1063 quantity_in_process = new_order_quantity,
1064 implement_date = new_schedule_date,
1065
1066 /*implement quantity is 0 in case of cancels */
1067 implement_quantity = decode(disposition_status_type, 2,0,new_order_quantity),
1068 load_type = var_load_type,
1069
1070 /*this is 1 for reschedules */
1071 reschedule_flag=decode(order_type,PLANNED_ORDER,reschedule_flag,PLANNED_NEW_BUY_ORDER,reschedule_flag,1),
1072
1073 /*for reschedules it is 1 */
1074 implement_as = decode(order_type,PLANNED_ORDER,PO_REQUISITION,PLANNED_NEW_BUY_ORDER,PO_REQUISITION,1),
1075
1076 /*implement status code must be set to cancel (7) in case of reschedules */
1077 implement_status_code = DECODE(disposition_status_type, CANCEL,7, null),
1078
1079 implement_firm = firm_planned_type,
1080 implement_dock_date = new_dock_date,
1081 implement_employee_id = var_planner_employee_id,
1082 implement_uom_code = var_primary_uom_code,
1083 implement_location_id = var_location_id,
1084 implement_source_org_id = source_organization_id,
1085 implement_supplier_id = source_supplier_id,
1086 implement_supplier_site_id = source_supplier_site_id,
1087 implement_project_id = project_id,
1088 implement_task_id = task_id,
1089 implement_unit_number = unit_number,
1090 implement_demand_class = NULL,
1091 last_updated_by = var_user_id
1092 WHERE transaction_id = var_transaction_id
1093 AND sr_instance_id = var_sr_instance_id
1094 AND plan_id = var_plan_id
1095 AND batch_id=MSC_REL_PLAN_PUB.g_batch_id;
1096
1097
1098 END IF; /* Count */
1099
1100 END IF; /* Make Buy code */
1101
1102 var_prev_org_id := var_org_id;
1103 var_prev_inventory_item_id := var_inventory_item_id;
1104
1105
1106 END IF; /*VMI Check -- fwd port 3181822 */
1107
1108 END LOOP;
1109
1113
1110 CLOSE planned_orders_and_reschedules;
1111
1112 COMMIT WORK;
1114 p_where_clause :=null ;
1115
1116 If var_plan_type =8 then
1117 OPEN ROs_to_release;
1118 LOOP
1119 var_entity := 'Fetch repair orders ';
1120
1121
1122 FETCH ROs_to_release INTO var_transaction_id,var_order_type,var_sr_instance_id,var_plan_id ;
1123 EXIT WHEN ROs_to_release%NOTFOUND;
1124 p_where_clause := ' transaction_id = ' || var_transaction_id ;
1125
1126
1127 MSC_SELECT_ALL_FOR_RELEASE_PUB.Update_Implement_Attrib(p_where_clause ,
1128 NULL,
1129 NULL,
1130 NULL,
1131 NULL,
1132 NULL,
1133 p_total_rows ,
1134 p_succ_rows ,
1135 p_error_rows
1136 );
1137
1138
1139 UPDATE msc_supplies
1140 SET batch_id = MSC_Rel_Plan_PUB.g_batch_id,
1141 load_type =decode(var_order_type,PLANNED_ERO,ERO_LOAD,IRO_LOAD)
1142 WHERE transaction_id = var_transaction_id
1143 AND sr_instance_id = var_sr_instance_id
1144 AND plan_id = var_plan_id ;
1145
1146 END LOOP;
1147 CLOSE ROs_to_release;
1148 END IF ;
1149
1150 commit;
1151
1152 If var_plan_type in (5,8) then
1153 OPEN transfer_orders_to_release;
1154 LOOP
1155 var_entity := 'Fetch transfer orders ';
1156
1157 FETCH transfer_orders_to_release INTO var_transaction_id,var_order_type,var_sr_instance_id,
1158 var_plan_id ;
1159 EXIT WHEN transfer_orders_to_release%NOTFOUND;
1160 p_where_clause := ' transaction_id = ' || var_transaction_id ;
1161
1162 MSC_SELECT_ALL_FOR_RELEASE_PUB.Update_Implement_Attrib(p_where_clause ,
1163 NULL,
1164 NULL,
1165 NULL,
1166 NULL,
1167 NULL,
1168 p_total_rows ,
1169 p_succ_rows ,
1170 p_error_rows
1171 );
1172 UPDATE msc_supplies
1173 SET batch_id = MSC_Rel_Plan_PUB.g_batch_id,
1174 load_type =TRANSFER_LOAD
1175 WHERE transaction_id = var_transaction_id
1176 AND sr_instance_id = var_sr_instance_id
1177 AND plan_id = var_plan_id ;
1178
1179 END LOOP;
1180 CLOSE transfer_orders_to_release;
1181 END IF ;
1182 commit ;
1183 -- ------------------------------------------------------------------------
1184 -- Release the planned orders and reschedules
1185 -- ------------------------------------------------------------------------
1186 var_entity := 'Release';
1187 MSC_Rel_Plan_PUB.msc_release_plan_sc
1188 (arg_plan_id, arg_org_id, arg_instance_id,
1189 arg_org_id, arg_instance_id, lv_plan_name, var_user_id,
1190 var_po_group_by, var_po_batch_number, var_wip_group_id,
1191 var_loaded_jobs, var_loaded_reqs, var_loaded_scheds,
1192 var_resched_jobs, var_resched_reqs, var_wip_req_id,
1193 var_req_load_id, var_req_resched_id, var_released_instance_id, NULL,NULL,
1194 var_loaded_lot_jobs, var_resched_lot_jobs,var_osfm_req_id,
1195 var_resched_eam_jobs,var_eam_req_id,
1196 var_loaded_int_reqs,var_resched_int_reqs,var_int_req_load_id,var_int_req_resched_id,
1197 var_loaded_int_repair_orders,var_int_repair_orders_id,var_loaded_ext_repair_orders,
1198 var_ext_repair_orders_id);
1199
1200
1201 /*purchase order reschedules require separate function call */
1202 msc_rel_wf.reschedule_purchase_orders
1203 (arg_plan_id,arg_org_id,arg_instance_id,arg_org_id,
1204 arg_instance_id,var_count_po,var_released_instance,
1205 var_po_res_id,var_po_res_count,var_po_pwb_count);
1206
1207 UPDATE msc_plans
1208 SET release_reschedules=2
1209 WHERE plan_id=arg_plan_id;
1210 commit;
1211
1212 var_buf := '+++++++++++++++++ ';
1213 fnd_file.put_line(FND_FILE.LOG, var_buf);
1214
1215 var_released_instance_count:= var_released_instance_id.count;
1216
1217 DECLARE
1218 i number;
1219 lv_instance_code varchar2(3);
1220
1221 BEGIN
1222
1223 FOR i IN 1..var_released_instance_id.count LOOP
1224
1225 select instance_code
1226 into lv_instance_code
1227 from msc_apps_instances
1228 where instance_id= var_released_instance_id(i);
1229
1230 fnd_message.set_name('MSC', 'MSC_AR_LOADED_INSTANCE');
1231 fnd_message.set_token('INSTANCE', lv_instance_code);
1232 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1233
1234 fnd_message.set_name('MRP', 'LOADED_WIP');
1235 fnd_message.set_token('VALUE', to_char(var_loaded_jobs(i)));
1236 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1237
1238 fnd_message.set_name('MRP', 'LOADED_WIP');
1242 fnd_message.set_name('MRP', 'LOADED_PO');
1239 fnd_message.set_token('VALUE', to_char(var_loaded_lot_jobs(i))||' lot Jobs');
1240 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1241
1243 fnd_message.set_token('VALUE', to_char(var_loaded_reqs(i)));
1244 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1245
1246 fnd_message.set_name('MRP', 'LOAD_WIP_REQUEST_ID');
1247 fnd_message.set_token('VALUE', to_char(var_wip_req_id(i)));
1248 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1249
1250 fnd_message.set_name('MRP', 'LOAD_WIP_REQUEST_ID');
1251 fnd_message.set_token('VALUE', to_char(var_osfm_req_id(i))||' OSFM Request Id');
1252 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1253
1254 fnd_message.set_name('MRP', 'LOAD_PO_REQUEST_ID');
1255 fnd_message.set_token('VALUE', to_char(var_req_load_id(i)));
1256 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1257
1258 fnd_message.set_name('MSC', 'LOADED_RESCHEDULED_JOB');
1259 fnd_message.set_token('VALUE', to_char(var_resched_jobs(i)));
1260 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1261
1262 fnd_message.set_name('MSC', 'LOADED_RESCHEDULED_REQS');
1263 fnd_message.set_token('VALUE', to_char(var_resched_reqs(i)));
1264 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1265
1266 fnd_file.put_line(FND_FILE.LOG, 'Loaded internal repair order :'|| var_loaded_int_repair_orders(i));
1267 fnd_file.put_line(FND_FILE.LOG, 'Loaded external repair order :'|| var_loaded_ext_repair_orders(i));
1268
1269 /* Bug 2595278 - added Null condition */
1270 IF ((var_loaded_jobs(i) > 0) AND ((var_wip_req_id(i) = 0) OR (var_wip_req_id(i) IS NULL))) THEN
1271 fnd_file.new_line(FND_FILE.LOG, 1);
1272 fnd_message.set_name('MRP', 'CANNOT SUBMIT REQUEST-JOBS');
1273 var_buf := fnd_message.get;
1274 fnd_file.put_line(FND_FILE.LOG, var_buf);
1275
1276 retcode := 2;
1277 errbuf := var_buf;
1278 END IF;
1279
1280
1281 IF ((var_loaded_lot_jobs(i) > 0) AND (var_osfm_req_id(i) = 0)) THEN
1282 fnd_file.new_line(FND_FILE.LOG, 1);
1283 fnd_message.set_name('MRP', 'CANNOT SUBMIT LOT-JOBS INTERFACE');
1284 var_buf := fnd_message.get;
1285 fnd_file.put_line(FND_FILE.LOG, var_buf);
1286
1287 retcode := 2;
1288 errbuf := var_buf;
1289 END IF;
1290
1291 IF ((var_loaded_reqs(i) > 0) AND (var_req_load_id(i) = 0)) THEN
1292 fnd_file.new_line(FND_FILE.LOG, 1);
1293 fnd_message.set_name('MRP', 'CANNOT SUBMIT REQUEST-REQS');
1294 var_buf := fnd_message.get;
1295 fnd_file.put_line(FND_FILE.LOG, var_buf);
1296
1297 retcode := 2;
1298 errbuf := var_buf;
1299 END IF;
1300
1301 IF ((var_loaded_int_repair_orders(i) > 0) AND (var_int_repair_orders_id(i) = 0)) THEN
1302 var_buf:= 'cannot submit the internal repair order request';
1303 fnd_file.put_line(FND_FILE.LOG, var_buf);
1304
1305 retcode := 2;
1306 errbuf := var_buf;
1307 END IF;
1308
1309 IF ((var_loaded_ext_repair_orders(i) > 0) AND (var_ext_repair_orders_id(i) = 0)) THEN
1310 var_buf:= 'cannot submit the external repair order request';
1311 fnd_file.put_line(FND_FILE.LOG, var_buf);
1312
1313 retcode := 2;
1314 errbuf := var_buf;
1315 END IF;
1316
1317 END LOOP;
1318
1319 var_buf := '++++++++++';
1320 fnd_file.put_line(FND_FILE.LOG, var_buf);
1321 var_buf := 'PO reschedules';
1322 fnd_file.put_line(FND_FILE.LOG, var_buf);
1323
1324 FOR i IN 1..var_count_po LOOP
1325 select instance_code
1326 into lv_instance_code
1327 from msc_apps_instances
1328 where instance_id= var_released_instance(i);
1329
1330 fnd_message.set_name('MSC', 'MSC_AR_LOADED_INSTANCE');
1331 fnd_message.set_token('INSTANCE', lv_instance_code);
1332 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1333 fnd_message.set_name('MSC', 'LOADED_RESCHEDULED_PO');
1334 fnd_message.set_token('VALUE', to_char(var_po_res_count(i)));
1335 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1336 END LOOP;
1337
1338 END;
1339 RETURN;
1340
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343
1344 IF planned_orders_and_reschedules%ISOPEN THEN CLOSE planned_orders_and_reschedules; END IF;
1345
1346 var_buf := var_entity||': '||sqlerrm;
1347 fnd_file.put_line(FND_FILE.LOG, var_buf);
1348
1349 ROLLBACK;
1350 retcode := 2;
1351 errbuf := var_buf;
1352 RETURN;
1353 END msc_auto_release;
1354
1355 PROCEDURE msc_web_service_release (
1356 pPlan_id Number,
1357 Use_Plan_start_date Varchar2,
1358 RETCODE OUT NOCOPY Number,
1359 ERRMSG OUT NOCOPY Varchar2,
1360 REQ_ID OUT NOCOPY ReqTblTyp
1361 ) IS
1362 v_instance_id NUMBER;
1363 v_owning_org_id NUMBER;
1364
1365 i number;
1366 j number := 1;
1367 lv_instance_code msc_apps_instances.INSTANCE_CODE%TYPE;
1368
1369 BEGIN
1370 --get instance_id and org_for the given plan.
1371 BEGIN
1372 SELECT mp.sr_instance_id,
1373 mp.organization_id
1374 INTO v_instance_id,
1375 v_owning_org_id
1376 FROM msc_plans mp
1377 WHERE mp.plan_id = pPlan_id;
1378 EXCEPTION
1379 WHEN NO_DATA_FOUND THEN
1380 ERRMSG := 'Not a Valid plan ID';
1381 RETCODE := MSC_UTIL.G_ERROR;
1382 RETURN;
1383 WHEN OTHERS THEN
1384 ERRMSG := SQLERRM;
1385 RETCODE := MSC_UTIL.G_ERROR;
1386 RETURN;
1387 END;
1388
1389 BEGIN
1390 msc_auto_release(ERRMSG,
1391 RETCODE,
1392 pPlan_id,
1393 v_owning_org_id,
1394 v_instance_id,
1395 Use_Plan_start_date);
1396
1397
1398 FOR i IN 1..var_released_instance_id.count LOOP
1399
1400 select instance_code
1401 into lv_instance_code
1402 from msc_apps_instances
1403 where instance_id= var_released_instance_id(i);
1404
1405 REQ_ID(j).instanceCode := lv_instance_code;
1406 REQ_ID(j).ReqID := var_wip_req_id(i);
1407 REQ_ID(j).ReqType := 'Loaded WIP Jobs :' || var_loaded_jobs(i) ;
1408 j := j + 1;
1409
1410 REQ_ID(j).instanceCode := lv_instance_code;
1411 REQ_ID(j).ReqID := var_osfm_req_id(i);
1412 REQ_ID(j).ReqType := 'Loaded Lot Jobs :' || var_loaded_lot_jobs(i) ;
1413 REQ_ID(j).ReqType := REQ_ID(j).ReqType || ' Resched Lot Jobs :' || var_resched_lot_jobs(i) ;
1414 j := j + 1;
1415
1416
1417 REQ_ID(j).instanceCode := lv_instance_code;
1418 REQ_ID(j).ReqID := var_req_load_id(i);
1419 REQ_ID(j).ReqType := 'Loaded PR :' || var_loaded_reqs(i);
1420 j := j + 1;
1421
1422 REQ_ID(j).instanceCode := lv_instance_code;
1423 REQ_ID(j).ReqID := var_resched_jobs(i);
1424 REQ_ID(j).ReqType := 'Resched PR :' || var_resched_reqs(i);
1425 j := j + 1;
1426
1427 REQ_ID(j).instanceCode := lv_instance_code;
1428 REQ_ID(j).ReqID := var_eam_req_id(i);
1429 REQ_ID(j).ReqType := 'Resched EAM jobs:' || var_resched_eam_jobs(i);
1430 j := j + 1;
1431
1432 REQ_ID(j).instanceCode := lv_instance_code;
1433 REQ_ID(j).ReqID := var_int_req_load_id(i);
1434 REQ_ID(j).ReqType := 'Loaded Int Reqs:' || var_loaded_int_reqs(i);
1435 j := j + 1;
1436
1437 REQ_ID(j).instanceCode := lv_instance_code;
1438 REQ_ID(j).ReqID := var_int_req_resched_id(i);
1439 REQ_ID(j).ReqType := 'Resched Int Reqs:' || var_resched_int_reqs(i);
1440 j := j + 1;
1441
1442 REQ_ID(j).instanceCode := lv_instance_code;
1443 REQ_ID(j).ReqID := var_int_repair_orders_id(i);
1444 REQ_ID(j).ReqType := 'IROs :' || var_loaded_int_repair_orders(i);
1445 j := j + 1;
1446
1447 REQ_ID(j).instanceCode := lv_instance_code;
1448 REQ_ID(j).ReqID := var_ext_repair_orders_id(i);
1449 REQ_ID(j).ReqType := 'EROs :' || var_loaded_ext_repair_orders(i);
1450 j := j + 1;
1451
1452
1453 /* Bug 2595278 - added Null condition */
1454 IF ((var_loaded_jobs(i) > 0) AND ((var_wip_req_id(i) = 0) OR (var_wip_req_id(i) IS NULL))) THEN
1455 fnd_file.new_line(FND_FILE.LOG, 1);
1456 fnd_message.set_name('MRP', 'CANNOT SUBMIT REQUEST-JOBS');
1457 ERRMSG := ERRMSG || ' ' || fnd_message.get;
1458 RETCODE := MSC_UTIL.G_ERROR;
1459 END IF;
1460
1461
1462 IF ((var_loaded_lot_jobs(i) > 0) AND (var_osfm_req_id(i) = 0)) THEN
1463 fnd_file.new_line(FND_FILE.LOG, 1);
1464 fnd_message.set_name('MRP', 'CANNOT SUBMIT LOT-JOBS INTERFACE');
1465 ERRMSG := ERRMSG || ' ' || fnd_message.get;
1466 RETCODE := MSC_UTIL.G_ERROR;
1467 END IF;
1468
1469 IF ((var_loaded_reqs(i) > 0) AND (var_req_load_id(i) = 0)) THEN
1470 fnd_file.new_line(FND_FILE.LOG, 1);
1471 fnd_message.set_name('MRP', 'CANNOT SUBMIT REQUEST-REQS');
1472 ERRMSG := ERRMSG || ' ' ||fnd_message.get;
1473 RETCODE := MSC_UTIL.G_ERROR;
1474 END IF;
1475
1476 IF ((var_loaded_int_repair_orders(i) > 0) AND (var_int_repair_orders_id(i) = 0)) THEN
1477 ERRMSG := ERRMSG || ' ' ||' cannot submit the internal repair order request';
1478 RETCODE := MSC_UTIL.G_ERROR;
1479 END IF;
1480
1481 IF ((var_loaded_ext_repair_orders(i) > 0) AND (var_ext_repair_orders_id(i) = 0)) THEN
1482 ERRMSG := ERRMSG || ' ' || 'cannot submit the external repair order request';
1483 RETCODE := MSC_UTIL.G_ERROR;
1484 END IF;
1485 END LOOP;
1486
1487
1488 FOR i IN 1..var_count_po LOOP
1489 select instance_code
1490 into lv_instance_code
1491 from msc_apps_instances
1492 where instance_id= var_released_instance(i);
1493
1494 REQ_ID(j).instanceCode := lv_instance_code;
1495 REQ_ID(j).ReqID := '';
1496 REQ_ID(j).ReqType := 'Resched PO :' || var_po_res_count(i);
1497 j := j + 1;
1498 END LOOP;
1499
1500 EXCEPTION
1501 WHEN OTHERS THEN
1502 ERRMSG := SQLERRM;
1503 RETCODE := MSC_UTIL.G_ERROR;
1504 END;
1505
1506 RETCODE := MSC_UTIL.G_SUCCESS;
1507 EXCEPTION
1508 WHEN OTHERS THEN
1509 ERRMSG := SQLERRM;
1510 RETCODE := MSC_UTIL.G_ERROR;
1511 END msc_web_service_release;
1512
1513 END msc_release_pk;