[Home] [Help]
PACKAGE BODY: APPS.MSC_RELEASE_PK
Source
1 PACKAGE BODY msc_release_pk AS
2 /* $Header: MSCARELB.pls 120.32.12020000.4 2012/11/14 04:59:07 zzhen 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 RP_MRP_PLAN CONSTANT INTEGER := 101; --RP Auto Rel
28 RP_MPS_PLAN CONSTANT INTEGER := 102; --RP Auto Rel
29 RP_MPP_PLAN CONSTANT INTEGER := 103; --RP Auto Rel
30
31
32 MRP_PLANNED_ITEM CONSTANT INTEGER := 3;
33 MPS_PLANNED_ITEM CONSTANT INTEGER := 4;
34 MPPMRP_PLANNED_ITEM CONSTANT INTEGER := 7;
35 MPPMPS_PLANNED_ITEM CONSTANT INTEGER := 8;
36
37 NULL_VALUE CONSTANT INTEGER := -23453;
38 MAGIC_STRING CONSTANT VARCHAR2(10) := '734jkhJK24';
39 BUFFER_SIZE_LEN CONSTANT INTEGER := 1000000;
40 NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
41
42 SYS_YES CONSTANT INTEGER := 1;
43 SYS_NO CONSTANT INTEGER := 2;
44
45 G_APPS107 CONSTANT NUMBER := 1;
46 G_APPS110 CONSTANT NUMBER := 2;
47 G_APPS115 CONSTANT NUMBER := 3;
48
49 ERO_LOAD CONSTANT NUMBER := 128;
50 IRO_LOAD CONSTANT NUMBER := 256;
51 TRANSFER_LOAD CONSTANT NUMBER := 32;
52 DRP_REQ_RESCHED constant integer := 64; -- drp release
53
54 PLANNED_REP_WO CONSTANT INTEGER := 79;
55 RESCHEDULE_EAM_CMRO CONSTANT INTEGER := 70;
56
57 CMRO_EAM_MASS_LOAD CONSTANT INTEGER := 257;
58 CMRO_EAM_RESCHED CONSTANT INTEGER := 258;
59 -- ========================================================================
60
61 var_released_instance_count NUMBER;
62
63 var_loaded_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
64 var_loaded_reqs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
65 var_loaded_scheds MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
66 var_resched_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
67 var_resched_reqs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
68 var_wip_req_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
69 var_req_load_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
70 var_req_resched_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
71 var_released_instance_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
72 var_loaded_lot_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
73 var_resched_lot_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
74 var_osfm_req_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
75 var_resched_eam_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
76 var_eam_req_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
77 var_loaded_int_reqs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
78 var_resched_int_reqs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
79 var_int_req_load_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
80 var_int_req_resched_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
81 var_loaded_int_repair_orders MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
82 var_int_repair_orders_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
83 var_loaded_ext_repair_orders MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
84 var_ext_repair_orders_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
85 var_loaded_eam_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
86 var_loaded_eam_req_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
87 var_loaded_cmro_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
88 var_loaded_cmro_req_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
89 var_resched_cmro_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
90 var_resched_cmro_req_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
91 var_released_instance msc_rel_wf.NumTblTyp:= msc_rel_wf.NumTblTyp(0);
92 var_po_res_id msc_rel_wf.NumTblTyp:= msc_rel_wf.NumTblTyp(0);
93 var_po_res_count msc_rel_wf.NumTblTyp:= msc_rel_wf.NumTblTyp(0);
94 var_po_pwb_count msc_rel_wf.NumTblTyp:= msc_rel_wf.NumTblTyp(0);
95 var_count_po NUMBER := 0;
96
97
98 -- ========================================================================
99 --
100 -- Selects the rows in MSC_SUPPLIES for all orgs in a given plan
101 -- that meet the auto-release criteria and release those planned orders and reschedules
102 --
103 -- ========================================================================
104 PROCEDURE msc_auto_release(
105 errbuf OUT NOCOPY VARCHAR2,
106 retcode OUT NOCOPY NUMBER,
107 arg_plan_id IN NUMBER,
108 arg_org_id IN NUMBER,
109 arg_instance_id IN NUMBER,
110 arg_use_start_date IN VARCHAR2) IS
111
112 VERSION CONSTANT CHAR(80) :=
113 '$Header: MSCARELB.pls 120.32.12020000.4 2012/11/14 04:59:07 zzhen ship $';
114
115 counter NUMBER := 0;
116
117 var_sql_stmt VARCHAR2(2000);
118
119 var_allow_release NUMBER;
120 var_instance_code VARCHAR2(10);
121
122 var_dblink VARCHAR2(128);
123 var_apps_ver NUMBER;
124 v_dblink VARCHAR2(128);
125
126 var_user_id NUMBER;
127 var_po_group_by NUMBER;
128 var_po_batch_number NUMBER;
129 var_wip_group_id NUMBER;
130 var_transaction_id NUMBER;
131 var_sr_instance_id NUMBER;
132 var_supplier_id NUMBER;
133 var_supplier_site_id NUMBER;
134 var_source_supplier_id NUMBER;
135 var_source_supplier_site_id NUMBER;
136 var_plan_id NUMBER;
137 var_start_date DATE;
138 var_demand_class VARCHAR(30);
139 var_wip_class_code VARCHAR(10);
140 var_wip_job_number NUMBER; -- <=== new
141 var_plan_type NUMBER;
142 var_make_buy_code NUMBER;
143 var_primary_uom_code VARCHAR(3);
144 var_planner_employee_id NUMBER;
145 var_employee_id NUMBER;
146 var_default_job_prefix VARCHAR(80);
147 var_job_prefix VARCHAR(80);
148 var_firm_jobs VARCHAR(80) := 'N';
149 var_impl_status_code NUMBER;
150 var_location_id NUMBER;
151 var_count NUMBER;
152 var_org_code VARCHAR(7);
153 var_org_id NUMBER;
154 var_prev_org_id NUMBER := -1;
155 var_inventory_item_id NUMBER;
156 var_sr_inventory_item_id NUMBER;
157 var_prev_inventory_item_id NUMBER := -1;
158 var_item VARCHAR(50);
159 var_new_schedule_date DATE;
160 var_cal2_date DATE; --Printing the date from cal2 10325294
161 var_new_order_quantity NUMBER;
162 var_order_type NUMBER;
163 var_load_type NUMBER;
164 var_planner_code VARCHAR(10);
165 var_debug BOOLEAN := FALSE;
166 var_entity VARCHAR(30);
167 var_buf VARCHAR2(2000);
168 var_project_id NUMBER;
169 var_sales_order_line_id NUMBER;
170 var_disp_type NUMBER;
171 var_release_status NUMBER;
172
173 err_msg_1 VARCHAR2(30);
174 err_class_1 VARCHAR2(10);
175 err_msg_2 VARCHAR2(30);
176 err_class_2 VARCHAR2(10);
177
178 --Added to fix the bug#2538765
179 var_user_name VARCHAR2(100):= NULL;
180 var_resp_name VARCHAR2(100):= NULL;
181 var_application_name VARCHAR2(240):= NULL;
182 --Added to fix the bug#2538765
183
184 -- for 3298070
185 lv_plan_name VARCHAR2(100):=NULL;
186
187 v_comp_days_tol number := NVL(FND_PROFILE.VALUE('MSC_AUTO_REL_COMP_TOLERANCE'),0);
188
189 v_wip_group_id NUMBER;
190 v_po_batch_number NUMBER;
191 v_rp_plan NUMBER :=0; -- Check if plan is an RP plan
192
193 lv_plan_release_profile number;
194 invalid_plan EXCEPTION;
195 var_alternate_bom varchar2(109); -- bug 13807697
196 var_alternate_routing varchar2(93);
197 lv_arg_org_id_new NUMBER ;
198 lv_arg_instance_id_new NUMBER ;
199 cursor plan_type_c(v_plan_id number) is
200 select curr_plan_type
201 from msc_plans
202 where plan_id = v_plan_id;
203
204 lv_auto_release_method number;
205 l_def_pref_id number;
206 l_plan_type number;
207 p_where_clause varchar2(3000):= NULL;
208 p_total_rows NUMBER;
209 p_succ_rows NUMBER;
210 p_error_rows NUMBER;
211 lv_sql_stmt varchar2(3000):= NULL;
212 lv_sql_stmt_rp varchar2(3000):= NULL;
213
214 --
215 -- PLANNING_MAKE_BUY_CODE cannot be used to determine whether an item is
216 -- a make or buy item for time-phased make-buy item, instead use
217 --
218 -- Curr org Source org Item type
219 -- -------- ---------- ---------
220 -- A.X A.X Make (always populated for Make item) - Same Org in Same Instance
221 -- A.X B.X Buy ( Same Org-Id across Instances)
222 -- X Y Buy
223 -- A.X B.Y Buy ( Diff Org-Id across Instances)
224 -- X - Buy (for Vendor)
225 --
226 -- MRP_PLANNING_CODE cannot be used to determine whether or not an item
227 -- should be released based on the plan type (MPS/MRP) for Supply Chain
228 -- Planning, instead use
229 --
230 -- In source plan Release
231 -- -------------- -------
232 -- Yes No (items from input supply schedules)
233 -- No Yes (items from input demand schedules)
234 --
235
236 CURSOR planned_orders_and_reschedules IS
237 SELECT
238 mr.transaction_id,
239 mr.sr_instance_id,
240 mr.plan_id,
241 mr.organization_id,
242 mr.inventory_item_id,
243 nvl(mr.supplier_id,mt.modeled_supplier_id),
244 nvl(mr.supplier_site_id,mt.modeled_supplier_site_id),
245 nvl(mr.source_supplier_id,mt.modeled_supplier_id),
246 nvl(mr.source_supplier_site_id,mt.modeled_supplier_site_id),
247 mr.new_schedule_date,
248 mr.new_order_quantity,
249 mr.order_type,
250 decode(mr.order_type,
251 PLANNED_ORDER, decode(decode(mr.sr_instance_id,
252 mr.source_sr_instance_id, DECODE(mr.source_organization_id,mr.organization_id,MAKE,BUY),
253 BUY),
254 MAKE, decode(CFM_ROUTING_FLAG, 3,5,1),
255 BUY, 8),
256 PLANNED_NEW_BUY_ORDER, decode(decode(mr.sr_instance_id,
257 mr.source_sr_instance_id, DECODE(mr.source_organization_id,mr.organization_id,MAKE,BUY),
258 BUY),
259 MAKE, decode(CFM_ROUTING_FLAG, 3,5,1),
260 BUY, 8),
261 PO_REQUISITION, 16,
262 PURCHASE_ORDER, 20,
263 WIP_DISCRETE_JOB, decode(CFM_ROUTING_FLAG, 3,6,4)),
264 msi.uom_code,
265 mpl.employee_id,
266 decode(mr.sr_instance_id,mr.source_sr_instance_id,
267 DECODE(mr.source_organization_id,mr.organization_id,1,2),
268 2),
269 msi.sr_inventory_item_id,
270 nvl(mr.implement_project_id,mr.project_id),
271 mr.disposition_status_type
272 ,cal2.calendar_date -- date also to print 10325294
273 FROM msc_calendar_dates cal1,
274 msc_calendar_dates cal2,
275 msc_planners mpl,
276 msc_trading_partners mparam,
277 msc_system_items msi,
278 msc_system_items rsi,
279 msc_supplies mr,
280 msc_plan_organizations_v mpo,
281 msc_plans mps,
282 msc_trading_partners mt
283 WHERE -- mpo.organization_id = arg_org_id
284 mpo.plan_id = arg_plan_id
285 -- AND mpo.sr_instance_id = arg_instance_id
286 AND mr.sr_instance_id = mpo.sr_instance_id
287 AND mr.organization_id = mpo.planned_organization
288 AND mr.plan_id = mpo.plan_id
289 AND mr.plan_id = mps.plan_id
290 --for bug#2881012
291 AND ( mr.order_type in (PLANNED_ORDER, PLANNED_NEW_BUY_ORDER)
292 OR ( mr.order_type IN (PURCHASE_ORDER,PO_REQUISITION,WIP_DISCRETE_JOB)
293 and ( mps.release_reschedules = SYS_YES or NVL(mps.INCLUDE_RESCHEDULES, SYS_NO) = SYS_YES )
294 and ( mr.disposition_status_type = CANCEL -- 2 is cancel
295 or ( mr.reschedule_flag = RESCHEDULE -- 2 => reschedule
296 and mr.new_schedule_date <> mr.old_schedule_date
297 and (nvl(mr.reschedule_days,0) <> 0) -- 8726490 , 9064626
298 )
299 )
300 )
301 )
302 AND (NVL(mr.schedule_compress_days, 0) = 0 OR
303 mr.schedule_compress_days <= v_comp_days_tol )
304 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))
305 <= TRUNC(cal2.calendar_date) --bug8351869
306 AND msi.organization_id = mr.organization_id
307 AND msi.sr_instance_id = mr.sr_instance_id
308 AND msi.inventory_item_id = mr.inventory_item_id
309 AND msi.plan_id = -1
310 AND msi.bom_item_type = 4
311 AND NVL(msi.release_time_fence_code, 5) NOT IN (5,6,7)
312 /* for bug#2881012 following is not for reschedules. Hence
313 reschedules skip these filters with mr.order_type <> PLANNED_ORDER*/
314 AND
315 ( mr.order_type not in (PLANNED_ORDER,PLANNED_NEW_BUY_ORDER)
316 OR (
317 ( msi.build_in_wip_flag = 1
318 AND msi.repetitive_type = 1 /* 1:NO, 2:YES */
319 AND decode(mr.sr_instance_id,mr.source_sr_instance_id,
320 DECODE(mr.source_organization_id, mr.organization_id, 1,2)
321 ,2) = 1
322 )
323 OR ( msi.purchasing_enabled_flag = 1
324 AND decode(mr.sr_instance_id,mr.source_sr_instance_id,
325 DECODE(mr.source_organization_id, mr.organization_id, 1, 2)
326 ,2) = 2
327 )
328 )
329 )
330 AND rsi.organization_id = mr.organization_id
331 AND rsi.plan_id = mr.plan_id
332 AND rsi.sr_instance_id = mr.sr_instance_id
333 AND rsi.inventory_item_id = mr.inventory_item_id
334 AND ( NVL(rsi.in_source_plan, 2) <> 1
335 OR
336 mr.transaction_id in (
337 select a.transaction_id from msc_supplies a, msc_supplies b,MSC_DESIGNATORS d, msc_system_items e
338 where a.schedule_designator_id is not null
339 and a.schedule_designator_id = b.schedule_designator_id
340 and a.organization_id = mr.organization_id
341 and a.organization_id = b.organization_id
342 and a.transaction_id= b.transaction_id
343 and a.sr_instance_id =b.sr_instance_id
344 and b.plan_id=-1
345 and a.inventory_item_id =e.inventory_item_id
346 and a.plan_id = e.plan_id
347 and a.sr_instance_id = e.sr_instance_id
348 and a.organization_id = e.organization_id
349 and NVL(e.in_source_plan, 2) =1
350 and a.plan_id=mr.plan_id
351 and b.SCHEDULE_ORIGINATION_TYPE =1
352 and a.schedule_designator_id = d.DESIGNATOR_ID
353 and d.designator not in (SELECT DISTINCT compile_designator
354 FROM MSC_PLANS
355 where sr_instance_id=a.sr_instance_id )
356
357 ))
358 AND decode(mps.curr_plan_type,
359 SRP_PLAN,SYS_YES,
360 DRP_PLAN, SYS_YES,
361 MRP_PLAN, decode(lv_plan_release_profile,
362 SYS_YES, SYS_YES,
363 decode(rsi.mrp_planning_code,
364 MRP_PLANNED_ITEM, SYS_YES,
365 MPPMRP_PLANNED_ITEM, SYS_YES,
366 SYS_NO
367 )
368 ),
369 MPS_PLAN, decode(lv_plan_release_profile,
370 SYS_YES, decode(rsi.mrp_planning_code,
371 MRP_PLANNED_ITEM, SYS_NO,
372 SYS_YES
373 ),
374 decode(rsi.mrp_planning_code,
375 MPS_PLANNED_ITEM, SYS_YES,
376 MPPMPS_PLANNED_ITEM,SYS_YES,
377 SYS_NO
378 )
379 ),
380 MPP_PLAN, decode(rsi.mrp_planning_code,
381 MPS_PLANNED_ITEM, SYS_NO,
382 MRP_PLANNED_ITEM, SYS_NO,
383 SYS_YES
384 ),
385 RP_MRP_PLAN, decode(lv_plan_release_profile,
386 SYS_YES, SYS_YES,
387 decode(rsi.mrp_planning_code,
388 MRP_PLANNED_ITEM, SYS_YES,
389 MPPMRP_PLANNED_ITEM, SYS_YES,
390 SYS_NO
391 )
392 ), --RP Auto Rel
393 RP_MPS_PLAN, decode(lv_plan_release_profile,
394 SYS_YES, decode(rsi.mrp_planning_code,
395 MRP_PLANNED_ITEM, SYS_NO,
396 SYS_YES
397 ),
398 decode(rsi.mrp_planning_code,
399 MPS_PLANNED_ITEM, SYS_YES,
400 MPPMPS_PLANNED_ITEM,SYS_YES,
401 SYS_NO
402 )
403 ), --RP Auto Rel
404 RP_MPP_PLAN, decode(rsi.mrp_planning_code,
405 MPS_PLANNED_ITEM, SYS_NO,
406 MRP_PLANNED_ITEM, SYS_NO,
407 SYS_YES
408 ), SYS_NO --RP Auto Rel
409 ) = SYS_YES
410 AND mpl.organization_id (+) = msi.organization_id
411 AND mpl.planner_code (+) = NVL(msi.planner_code, MAGIC_STRING)
412 AND mpl.sr_instance_id(+) = msi.sr_instance_id
413 AND mparam.sr_tp_id = mr.organization_id
414 AND mparam.sr_instance_id= mr.sr_instance_id
415 AND mparam.partner_type= 3
416 AND cal1.sr_instance_id= mr.sr_instance_id
417 AND cal1.calendar_code = mparam.calendar_code
418 AND cal1.exception_set_id = mparam.calendar_exception_set_id
419 AND cal1.calendar_date = TRUNC(var_start_date)
420 AND cal2.sr_instance_id = mr.sr_instance_id
421 AND cal2.calendar_code = cal1.calendar_code
422 AND cal2.exception_set_id = cal1.exception_set_id
423 AND cal2.seq_num = cal1.next_seq_num +
424 NVL(DECODE(rsi.release_time_fence_code,
425 1, rsi.cumulative_total_lead_time,
426 2, rsi.cum_manufacturing_lead_time,
427 3, rsi.full_lead_time,
428 4, rsi.release_time_fence_days,
429 0), -- Selecting the rtf days from planned data instead of collected data 10325294
430 0)
431 -- bug fix for 2261963 to filter planned orders that have already been released --
432 AND NVL(mr.implemented_quantity, 0) + NVL(mr.quantity_in_process, 0)
433 < mr.new_order_quantity
434 AND mt.sr_instance_id(+) = mr.source_sr_instance_id
435 AND mt.sr_tp_id(+) = mr.source_organization_id
436 AND mt.partner_type(+) = 3
437 -- AND (mr.releasable = MSC_Rel_Plan_PUB.RELEASABLE or mr.releasable is null )
438 AND ( ( NVL(mps.auto_release_method,-1) IN (3,4) AND mr.releasable = 99) -- Query based autorel
439 OR
440 ( NVL(mps.auto_release_method,-1) NOT IN (3,4) AND NVL(mr.releasable,0) =0 )-- other autorel
441 )
442 AND mr.batch_id is NULL
443 -- shikyu changes
444 AND not exists (select 1 from msc_system_items msi1 , msc_trading_partners mtp
445 where msi1.inventory_item_id = mr.inventory_item_id
446 and msi1.organization_id = mr.organization_id
447 and msi1.plan_id = mr.plan_id
448 AND msi1.sr_instance_id = mr.sr_instance_id
449 and nvl(msi1.release_time_fence_code,-1) = 7
450 and mtp.sr_tp_id = msi1.organization_id
451 and mtp.sr_instance_id = msi1.sr_instance_id
452 and mtp.partner_type=3
453 and (mtp.modeled_supplier_id is not null OR mtp.modeled_supplier_site_id is not null))
454 ORDER BY 2;
455
456 cursor ROs_to_release is
457 Select
458 ms.Transaction_id,
459 ms.order_type,
460 ms.sr_instance_id ,
461 ms.plan_id
462 from
463 msc_supplies ms,
464 msc_plans mps,
465 msc_system_items msi,
466 msc_trading_partners mtp,
467 msc_calendar_dates cal1,
468 msc_calendar_dates cal2
469 where
470 ms.plan_id =arg_plan_id
471 and ms.order_type in (PLANNED_IRO,PLANNED_ERO)
472 and ms.inventory_item_id = msi.inventory_item_id
473 and ms.sr_instance_id =msi.sr_instance_id
474 and msi.plan_id = arg_plan_id
475 and msi.bom_item_type = 4
476 and msi.release_time_fence_code NOT IN (5,6,7)
477 and ms.organization_id =msi.organization_id
478 and mtp.sr_tp_id = ms.organization_id
479 and mtp.sr_instance_id= ms.sr_instance_id
480 and mtp.partner_type= 3
481 and cal1.sr_instance_id = ms.sr_instance_id
482 and cal1.calendar_code = mtp.calendar_code
483 and cal1.exception_set_id = mtp.calendar_exception_set_id
484 and cal1.calendar_date = TRUNC(var_start_date)
485 and cal2.sr_instance_id = ms.sr_instance_id
486 and cal2.calendar_code = cal1.calendar_code
487 and cal2.exception_set_id = cal1.exception_set_id
488 and cal2.seq_num = cal1.next_seq_num +
489 NVL(DECODE(msi.release_time_fence_code,
490 1, msi.cumulative_total_lead_time,
491 2, msi.cum_manufacturing_lead_time,
492 3, msi.full_lead_time,
493 4, msi.release_time_fence_days,
494 0),
495 0)
496 and nvl(ms.new_order_placement_date, ms.new_schedule_date) BETWEEN TRUNC(var_start_date)
497 and TRUNC(cal2.calendar_date)
498 and ms.plan_id = mps.plan_id
499 AND ( ( NVL(mps.auto_release_method,-1) IN (3,4) AND ms.releasable = 99) -- Query based autorel
500 OR
501 ( NVL(mps.auto_release_method,-1) NOT IN (3,4) AND NVL(ms.releasable,0) =0 )-- other autorel
502 )
503 --and (ms.releasable =0 or ms.releasable is null)
504 and ms.batch_id is null;
505
506 cursor transfer_orders_to_release is
507 Select
508 ms.Transaction_id,
509 ms.order_type,
510 ms.sr_instance_id ,
511 ms.plan_id
512 from
513 msc_supplies ms,
514 msc_plans mps,
515 msc_system_items msi,
516 msc_trading_partners mtp,
517 msc_calendar_dates cal1,
518 msc_calendar_dates cal2
519 where
520 ms.plan_id =arg_plan_id
521 and ms.order_type in (PLANNED_TRANSFER)
522 and ms.inventory_item_id = msi.inventory_item_id
523 and ms.sr_instance_id =msi.sr_instance_id
524 and msi.plan_id = arg_plan_id
525 and msi.bom_item_type = 4
526 and msi.release_time_fence_code NOT IN (5,6,7)
527 and ms.organization_id =msi.organization_id
528 and mtp.sr_tp_id = ms.organization_id
529 and mtp.sr_instance_id= ms.sr_instance_id
530 and mtp.partner_type= 3
531 and cal1.sr_instance_id = ms.sr_instance_id
532 and cal1.calendar_code = mtp.calendar_code
533 and cal1.exception_set_id = mtp.calendar_exception_set_id
534 and cal1.calendar_date = TRUNC(var_start_date)
535 and cal2.sr_instance_id = ms.sr_instance_id
536 and cal2.calendar_code = cal1.calendar_code
537 and cal2.exception_set_id = cal1.exception_set_id
538 and cal2.seq_num = cal1.next_seq_num +
539 NVL(DECODE(msi.release_time_fence_code,
540 1, msi.cumulative_total_lead_time,
541 2, msi.cum_manufacturing_lead_time,
542 3, msi.full_lead_time,
543 4, msi.release_time_fence_days,
544 0),
545 0)
546 and nvl(ms.new_order_placement_date, ms.new_schedule_date) BETWEEN TRUNC(var_start_date)
547 and TRUNC(cal2.calendar_date)
548 and ms.plan_id = mps.plan_id
549 AND ( (NVL(mps.auto_release_method,-1) IN (3,4) AND ms.releasable = 99) -- Query based autorel
550 OR
551 (NVL(mps.auto_release_method,-1) NOT IN (3,4) AND NVL(ms.releasable,0) =0 )-- other autorel
552 )
553 --and (ms.releasable =0 or ms.releasable is null)
554 and ms.batch_id is null
555 and
556 not exists (select 1 from msc_full_pegging mfp,
557 msc_demands md,
558 msc_supplies ms1
559 where mfp.sr_instance_id = ms.sr_instance_id and
560 mfp.plan_id = ms.plan_id and
561 mfp.transaction_id =ms.transaction_id and
562 mfp.demand_id =md.demand_id and
563 mfp.sr_instance_id = md.sr_instance_id and
564 mfp.plan_id = md.plan_id and
565 md.origination_type =78 and
566 md.disposition_id = ms1.transaction_id and
567 md.sr_instance_id =ms1.sr_instance_id and
568 md.plan_id = ms1.plan_id and
569 ms1.order_type =79 and
570 rownum <2);
571
572 cursor eam_cmro_orders_to_release is
573 Select
574 ms.Transaction_id,
575 ms.order_type,
576 --decode(ms.order_type,)
577 ms.sr_instance_id ,
578 ms.plan_id
579 from
580 msc_supplies ms,
581 msc_plans mps,
582 msc_system_items msi,
583 msc_trading_partners mtp,
584 msc_calendar_dates cal1,
585 msc_calendar_dates cal2
586 where
587 ms.plan_id =arg_plan_id
588 and ms.order_type in (PLANNED_REP_WO,RESCHEDULE_EAM_CMRO) --79,70
589 and ms.inventory_item_id = msi.inventory_item_id
590 and ms.sr_instance_id =msi.sr_instance_id
591 and msi.plan_id = arg_plan_id
592 and msi.bom_item_type = 4
593 and msi.release_time_fence_code NOT IN (5,6,7)
594 and ms.organization_id =msi.organization_id
595 and mtp.sr_tp_id = ms.organization_id
596 and mtp.sr_instance_id= ms.sr_instance_id
597 and mtp.partner_type= 3
598 and cal1.sr_instance_id = ms.sr_instance_id
599 and cal1.calendar_code = mtp.calendar_code
600 and cal1.exception_set_id = mtp.calendar_exception_set_id
601 and cal1.calendar_date = TRUNC(var_start_date)
602 and cal2.sr_instance_id = ms.sr_instance_id
603 and cal2.calendar_code = cal1.calendar_code
604 and cal2.exception_set_id = cal1.exception_set_id
605 and cal2.seq_num = cal1.next_seq_num +
606 NVL(DECODE(msi.release_time_fence_code,
607 1, msi.cumulative_total_lead_time,
608 2, msi.cum_manufacturing_lead_time,
609 3, msi.full_lead_time,
610 4, msi.release_time_fence_days,
611 0),
612 0)
613 and nvl(ms.new_order_placement_date, ms.new_schedule_date) BETWEEN TRUNC(var_start_date)
614 and TRUNC(cal2.calendar_date)
615 and ms.plan_id = mps.plan_id
616 AND ( ( NVL(mps.auto_release_method,-1) IN (3,4) AND ms.releasable = 99) -- Query based autorel
617 OR
618 ( NVL(mps.auto_release_method,-1) NOT IN (3,4) AND NVL(ms.releasable,0) =0 )-- other autorel
619 )
620 --and (ms.releasable =0 or ms.releasable is null)
621 and ms.batch_id is null;
622
623 BEGIN
624 retcode := 0;
625 errbuf := NULL;
626 select organization_id,
627 sr_instance_id
628 into lv_arg_org_id_new,
629 lv_arg_instance_id_new
630 from msc_plans
631 where plan_id = arg_plan_id ;
632
633 select decode(FND_PROFILE.VALUE('MSC_DRP_RELEASE_FROM_MRP'),'Y',SYS_YES,SYS_NO)
634 into lv_plan_release_profile
635 from dual;
636
637 var_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
638
639 SELECT ALLOW_RELEASE_FLAG,
640 INSTANCE_CODE,
641 APPS_VER,
642 DECODE( M2A_DBLINK,
643 NULL, NULL_DBLINK,
644 '@'||M2A_DBLINK)
645 INTO var_allow_release,
646 var_instance_code,
647 var_apps_ver,
648 var_dblink
649 FROM MSC_APPS_INSTANCES
650 WHERE INSTANCE_ID= lv_arg_instance_id_new;
651
652 if ( var_apps_ver >= 3 ) then
653
654 IF var_allow_release = 2 THEN
655
656 var_sql_stmt:=
657 'SELECT mar.A2M_DBLINK '
658 ||' FROM MRP_AP_APPS_INSTANCES_ALL'||var_dblink||' mar'
659 ||' WHERE mar.ALLOW_RELEASE_FLAG = 1 ';
660
661 EXECUTE IMMEDIATE var_sql_stmt INTO v_dblink ;
662
663 fnd_message.set_name('MSC', 'MSC_AUTO_RELEASE_WARN');
664 fnd_message.set_token('A2M_DBLINK',v_dblink);
665 fnd_message.set_token('SOURCE_INSTANCE',var_instance_code);
666 var_buf:= fnd_message.get;
667 fnd_file.put_line(FND_FILE.LOG, var_buf);
668
669
670 retcode := 1;
671 errbuf := var_buf;
672 RETURN;
673
674 END IF;
675
676 end if;
677 /*
678 For RP Auto release profile options are stored in MSC_PLAN_PROFILES table
679 So obtain them from the table
680 */
681 SELECT plan_type
682 INTO var_plan_type
683 FROM msc_plans
684 WHERE plan_id = arg_plan_id;
685
686 IF (var_plan_type >= RP_MRP_PLAN)
687 THEN
688 v_rp_plan := 1;
689 END IF;
690
691 IF (v_rp_plan = 1)
692 THEN
693 lv_sql_stmt_rp := ' SELECT nvl(profile_value,0)'
694 ||' FROM msc_plan_profiles'
695 ||' WHERE plan_id='|| arg_plan_id
696 ||' AND profile_code=''MSC_AUTO_REL_COMP_TOLERANCE''';
697 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'the sql stmt is : '|| lv_sql_stmt_rp);
698 execute immediate lv_sql_stmt_rp into v_comp_days_tol;
699 END IF;
700 -- ------------------------------------------------------------------------
701 -- Validate the plan
702 -- ------------------------------------------------------------------------
703 BEGIN
704 var_entity := 'Plan Validation';
705 msc_valid_plan_pkg.msc_valid_plan(arg_plan_id, 'Y', 'Y', 'Y', 'N');
706
707 EXCEPTION
708 WHEN OTHERS THEN
709
710 var_buf:= fnd_message.get;
711 --var_buf:= sqlerrm(sqlcode);
712 fnd_file.put_line(FND_FILE.LOG, var_buf);
713
714 fnd_message.set_name('MRP', 'UNSUCCESSFUL PLAN VALIDATION');
715 var_buf:= fnd_message.get;
716 fnd_file.put_line(FND_FILE.LOG, var_buf);
717
718 retcode := 2;
719 errbuf := var_buf;
720 RETURN;
721 END;
722
723 -- ------------------------------------------------------------------------
724 -- Setup
725 -- ------------------------------------------------------------------------
726
727 SELECT DECODE( M2A_DBLINK, NULL, ' ', '@'||M2A_DBLINK),
728 APPS_VER
729 INTO var_dblink,
730 var_apps_ver
731 FROM MSC_APPS_INSTANCES
732 WHERE INSTANCE_ID= lv_arg_instance_id_new;
733
734 --Fix for the bug#2538765. This enables the auto-release to use the user level
735 --profile value defined in the source instance.
736
737 SELECT FND_GLOBAL.USER_NAME,
738 FND_GLOBAL.RESP_NAME,
739 FND_GLOBAL.APPLICATION_NAME
740 INTO var_user_name,
741 var_resp_name,
742 var_application_name
743 FROM dual;
744
745 var_sql_stmt:=
746 'BEGIN'
747 ||' MRP_AP_REL_PLAN_PUB.INITIALIZE'||var_dblink
748 ||'( :var_user_name,'
749 ||' :var_resp_name,'
750 ||' :var_application_name,'
751 ||' :v_wip_group_id,'
752 ||' :v_po_batch_number);'
753
754 ||'END;';
755
756 EXECUTE IMMEDIATE var_sql_stmt
757 USING var_user_name,
758 var_resp_name,
759 IN var_application_name,
760 OUT v_wip_group_id,
761 OUT v_po_batch_number;
762
763
764 var_sql_stmt:=
765 'SELECT FND_GLOBAL.USER_ID,'
766 ||' FND_PROFILE.VALUE'||var_dblink||'(''WIP_JOB_PREFIX''),'
767 ||' FND_PROFILE.VALUE'||var_dblink||'(''MRP_LOAD_REQ_GROUP_BY'')'
768 ||' FROM DUAL';
769
770 EXECUTE IMMEDIATE var_sql_stmt INTO var_user_id,
771 var_default_job_prefix,
772 var_po_group_by;
773
774 var_entity := 'Setup';
775
776 SELECT mp.curr_plan_type,
777 DECODE(UPPER(arg_use_start_date),
778 'Y', mp.plan_start_date, 'N', sysdate, sysdate),
779 sched.demand_class,
780 mp.compile_designator,
781 NVL(auto_release_method,0)
782 INTO var_plan_type,
783 var_start_date,
784 var_demand_class,
785 lv_plan_name,
786 lv_auto_release_method
787 FROM msc_plans mp,
788 msc_designators sched
789 WHERE sched.organization_id(+)= mp.organization_id
790 AND sched.designator(+) = mp.compile_designator
791 AND sched.sr_instance_id(+) = mp.sr_instance_id
792 AND mp.plan_id = arg_plan_id;
793
794 IF var_debug THEN
795 var_buf := '+++++++++++++++++';
796 fnd_file.put_line(FND_FILE.LOG, var_buf);
797 var_buf := 'User ID : '||var_user_id;
798 fnd_file.put_line(FND_FILE.LOG, var_buf);
799 var_buf := 'Owning org : '||lv_arg_org_id_new;
800 fnd_file.put_line(FND_FILE.LOG, var_buf);
801 var_buf := 'Plan Name : '||lv_plan_name;
802 fnd_file.put_line(FND_FILE.LOG, var_buf);
803 var_buf := 'Plan type : '||var_plan_type;
804 fnd_file.put_line(FND_FILE.LOG, var_buf);
805 var_buf := 'Use start date : '||arg_use_start_date;
806 fnd_file.put_line(FND_FILE.LOG, var_buf);
807 var_buf := 'Start date : '||var_start_date;
808 fnd_file.put_line(FND_FILE.LOG, var_buf);
809 var_buf := 'WIP default job prefix : '||var_default_job_prefix;
810 fnd_file.put_line(FND_FILE.LOG, var_buf);
811 var_buf := 'WIP group ID : '||var_wip_group_id;
812 fnd_file.put_line(FND_FILE.LOG, var_buf);
813 var_buf := 'PO group by : '||var_po_group_by;
814 fnd_file.put_line(FND_FILE.LOG, var_buf);
815 var_buf := 'PO batch number : '||var_po_batch_number;
816 fnd_file.put_line(FND_FILE.LOG, var_buf);
817 var_buf := 'Demand Class : '||var_demand_class;
818 fnd_file.put_line(FND_FILE.LOG, var_buf);
819 var_buf := 'Auto Release Method : '||lv_auto_release_method;
820 fnd_file.put_line(FND_FILE.LOG, var_buf);
821
822 END IF;
823
824 begin
825 -- populating batch_id from destination side seq.
826 Execute immediate 'select mrp_workbench_query_s.nextval
827 FROM DUAL '
828 into MSC_Rel_Plan_PUB.g_batch_id;
829 exception when others then
830 fnd_file.put_line(FND_FILE.LOG, sqlerrm);
831 end;
832
833 fnd_file.put_line(FND_FILE.LOG, MSC_Rel_Plan_PUB.g_batch_id);
834
835 IF ( lv_auto_release_method in (3,4) and var_plan_type = SRP_PLAN ) THEN
836 Begin
837 lv_sql_stmt:= ' BEGIN '
838 ||' MSC_PQ_UTILS.EXECUTE_PLAN_QUERIES(:ERRBUF,:RETCODE,:arg_plan_id) ; '
839 ||' END ; ' ;
840
841 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* Call UI API ****** ');
842 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,lv_sql_stmt);
843 EXECUTE IMMEDIATE lv_sql_stmt USING OUT ERRBUF, OUT RETCODE, IN arg_plan_id ;
844 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' *******End UI API ***********');
845 QueryBasedAutoRelease(arg_plan_id,ERRBUF,RETCODE);
846
847 EXCEPTION WHEN OTHERS THEN
848
849 ROLLBACK;
850 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, ERRBUF);
851 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, DBMS_UTILITY.FORMAT_ERROR_STACK);
852 RAISE;
853
854 End ;
855
856 END IF;
857
858 -- ------------------------------------------------------------------------
859 -- Get planned orders and reschedules that meet the auto-release criteria
860 -- ------------------------------------------------------------------------
861 var_entity := 'Planned Orders';
862
863 OPEN planned_orders_and_reschedules;
864
865 LOOP
866
867 var_entity := 'Fetch Planned Orders';
868
869 FETCH planned_orders_and_reschedules INTO var_transaction_id,
870 var_sr_instance_id,
871 var_plan_id,
872 var_org_id,
873 var_inventory_item_id,
874 var_supplier_id,
875 var_supplier_site_id,
876 var_source_supplier_id,
877 var_source_supplier_site_id,
878 var_new_schedule_date,
879 var_new_order_quantity,
880 var_order_type,
881 var_load_type,
882 var_primary_uom_code,
883 var_planner_employee_id,
884 var_make_buy_code,
885 var_sr_inventory_item_id,
886 var_project_id,
887 var_disp_type,
888 var_cal2_date;
889
890 EXIT WHEN planned_orders_and_reschedules%NOTFOUND;
891
892 IF var_debug THEN
893
894 var_buf := 'Release Time fence Date: '||to_char(var_cal2_date);
895 fnd_file.put_line(FND_FILE.LOG, var_buf);
896
897 END IF; --10325294
898
899 UPDATE msc_supplies
900 SET batch_id = MSC_Rel_Plan_PUB.g_batch_id
901 WHERE transaction_id = var_transaction_id
902 AND sr_instance_id = var_sr_instance_id
903 AND plan_id = var_plan_id ;
904
905 /* For RP Auto Release */
906 var_release_status := NULL;
907 IF (var_plan_type IN (RP_MRP_PLAN, RP_MPS_PLAN, RP_MPP_PLAN)) THEN
908
909 IF (var_order_type = 5)THEN
910 var_release_status := 13; -- Released
911 ELSIF (var_disp_type = 2) THEN
912 var_release_status := 12; -- Cancelled
913 ELSE
914 var_release_status := 11; -- Rescheduled
915 END IF;
916 END IF;
917 /*VMI Check -- fwd port 3181822 */
918
919 IF (not (((var_order_type = 5) AND (var_make_buy_code = BUY and
920 (MSC_UTIL.get_vmi_flag(var_plan_id,
921 var_sr_instance_id,
922 var_org_id,
923 var_inventory_item_id,
924 var_source_supplier_id,
925 var_source_supplier_site_id)= 1)))
926 OR (var_order_type in (1,2) AND (MSC_UTIL.get_vmi_flag(var_plan_id,
927 var_sr_instance_id,
928 var_org_id,
929 var_inventory_item_id,
930 var_supplier_id,
931 var_supplier_site_id)= 1)))) THEN
932
933
934 -- ----------------------------------------------------------------------
935 -- Get organization dependent info
936 -- ----------------------------------------------------------------------
937 IF (var_org_id <> var_prev_org_id) THEN
938
939 var_entity := 'PO Location';
940
941 BEGIN
942 SELECT DECODE(tps.sr_tp_site_id, -1, NULL,tps.sr_tp_site_id)
943 INTO var_location_id
944 FROM msc_trading_partner_sites tps
945 WHERE tps.sr_tp_id = var_org_id
946 AND tps.sr_instance_id= lv_arg_instance_id_new
947 AND tps.partner_type= 3;
948
949 EXCEPTION
950 WHEN NO_DATA_FOUND THEN
951 var_location_id := NULL;
952 WHEN OTHERS THEN
953 var_buf := var_entity||': '||sqlerrm;
954 fnd_file.put_line(FND_FILE.LOG, var_buf);
955
956 ROLLBACK;
957
958 CLOSE planned_orders_and_reschedules;
959
960 retcode := 2;
961 errbuf := var_buf;
962 RETURN;
963 END;
964
965 IF var_debug THEN
966 var_buf := '=================';
967 fnd_file.put_line(FND_FILE.LOG, var_buf);
968 var_buf := 'Organization ID : '||var_org_id;
969 fnd_file.put_line(FND_FILE.LOG, var_buf);
970 var_buf := 'Location ID : '||var_location_id;
971 fnd_file.put_line(FND_FILE.LOG, var_buf);
972 END IF;
973 END IF; /* Org ID */
974
975 -- ----------------------------------------------------------------------
976 -- Get item dependent info
977 -- ----------------------------------------------------------------------
978 IF (var_prev_inventory_item_id <> var_inventory_item_id) THEN
979
980 var_sql_stmt:=
981 'BEGIN'
982 ||' :var_wip_class_code := wip_common.default_acc_class'||var_dblink||'('
983 ||' :var_org_id,'
984 ||' :var_sr_inventory_item_id,'
985 ||' 1,'
986 ||' :var_project_id,'
987 ||' :err_msg_1,'
988 ||' :err_class_1,'
989 ||' :err_msg_2,'
990 ||' :err_class_2);'
991 ||' END;';
992
993 EXECUTE IMMEDIATE var_sql_stmt
994 USING OUT var_wip_class_code,
995 IN var_org_id,
996 IN var_sr_inventory_item_id,
997 IN var_project_id,
998 OUT err_msg_1,
999 OUT err_class_1,
1000 OUT err_msg_2,
1001 OUT err_class_2;
1002
1003
1004 IF (var_wip_class_code is NULL) THEN
1005 BEGIN
1006
1007 var_entity := 'WIP Discrete Class';
1008
1009 var_sql_stmt:=
1010 'SELECT wp.default_discrete_class'
1011 ||' FROM wip_parameters'||var_dblink||' wp'
1012 ||' WHERE wp.organization_id = :var_org_id';
1013
1014 EXECUTE IMMEDIATE var_sql_stmt
1015 INTO var_wip_class_code
1016 USING var_org_id;
1017
1018 EXCEPTION
1019 WHEN NO_DATA_FOUND THEN
1020 var_wip_class_code := NULL;
1021 WHEN OTHERS THEN
1022 var_buf := var_entity||': '||sqlerrm;
1023 fnd_file.put_line(FND_FILE.LOG, var_buf);
1024
1025 ROLLBACK;
1026 CLOSE planned_orders_and_reschedules;
1027 retcode := 2;
1028 errbuf := var_buf;
1029 RETURN;
1030 END;
1031 END IF;
1032 END IF; /* Item Id */
1033
1034 IF (var_make_buy_code = MAKE OR var_order_type = WIP_DISCRETE_JOB) THEN
1035
1036 -- --------------------------------------------------------------------
1037 -- Get WIP Jobs parameter
1038 -- --------------------------------------------------------------------
1039 -- IF var_planner_employee_id IS NULL THEN
1040
1041 -- var_impl_status_code := UNRELEASED_NO_CHARGES;
1042 -- var_job_prefix := var_default_job_prefix;
1043
1044 -- ELSE
1045
1046 BEGIN
1047 var_entity := 'WIP Job Status';
1048 open Plan_type_c(var_plan_id);
1049 fetch plan_type_c into l_plan_type;
1050 close plan_type_c;
1051 l_def_pref_id := msc_get_name.get_default_pref_id(var_user_id);
1052 var_impl_status_code:= msc_get_name.GET_preference('ORDERS_DEFAULT_JOB_STATUS', l_def_pref_id, l_plan_type);
1053 var_wip_class_code:= msc_get_name.GET_preference('JOB_CLASS_CODE', l_def_pref_id, l_plan_type);
1054 var_firm_jobs:= msc_get_name.GET_preference('ORDERS_FIRM_JOBS', l_def_pref_id, l_plan_type);
1055 /*
1056 OPEN job_status;
1057 FETCH job_status INTO var_impl_status_code, var_wip_class_code,
1058 var_firm_jobs;
1059 */
1060
1061
1062 var_job_prefix := var_default_job_prefix; /* Bug 2571601 */
1063
1064 IF var_impl_status_code is null THEN
1065 var_impl_status_code := UNRELEASED_NO_CHARGES;
1066 var_job_prefix := var_default_job_prefix;
1067 END IF;
1068
1069 -- CLOSE job_status;
1070 END;
1071
1072 -- END IF;
1073
1074 IF var_debug THEN
1075 var_buf := '----------------- ';
1076 fnd_file.put_line(FND_FILE.LOG, var_buf);
1077 var_buf := 'Item ID : '||var_inventory_item_id;
1078 fnd_file.put_line(FND_FILE.LOG, var_buf);
1079 var_buf := 'Item Make/Buy : '||var_make_buy_code;
1080 fnd_file.put_line(FND_FILE.LOG, var_buf);
1081 var_buf := 'WIP class code : '||var_wip_class_code;
1082 fnd_file.put_line(FND_FILE.LOG, var_buf);
1083 var_buf := 'New Schedule Date : '||var_new_schedule_date;
1084 fnd_file.put_line(FND_FILE.LOG, var_buf);
1085 var_buf := 'Quantity : '||var_new_order_quantity;
1086 fnd_file.put_line(FND_FILE.LOG, var_buf);
1087 var_buf := 'Planner emp ID : '||var_planner_employee_id;
1088 fnd_file.put_line(FND_FILE.LOG, var_buf);
1089 var_buf := 'WIP job status : '||var_impl_status_code;
1090 fnd_file.put_line(FND_FILE.LOG, var_buf);
1091 var_buf := 'WIP job prefix : '||var_job_prefix;
1092 fnd_file.put_line(FND_FILE.LOG, var_buf);
1093 var_buf := 'Order Type : '||var_order_type;
1094 fnd_file.put_line(FND_FILE.LOG, var_buf);
1095 END IF;
1096
1097 -- --------------------------------------------------------------------
1098 -- -- Update WIP Jobs planned orders and reschedules
1099 -- --------------------------------------------------------------------
1100 var_entity := 'WIP Planned Orders';
1101 IF(var_order_type=PLANNED_ORDER)
1102 /*discrete job reschedules don't require new name*/
1103 THEN
1104 var_sql_stmt:=
1105 'SELECT wip_job_number_s.nextval'||var_dblink||' FROM dual';
1106
1107 EXECUTE IMMEDIATE var_sql_stmt
1108 INTO var_wip_job_number;
1109 END IF;
1110
1111 /* Lets get the alternate_bom_designator and
1112 alternate_routing_designator here in case plan has recommended -- bug 13807697
1113 */
1114
1115 /* Getting Alternate BOM */
1116
1117 begin
1118
1119 select
1120 mb.alternate_bom_designator
1121 into var_alternate_bom
1122 from msc_supplies ms,
1123 msc_process_effectivity mpe,
1124 msc_boms mb
1125 where
1126 ms.plan_id = var_plan_id -- Plan_id
1127 and ms.order_type = 5 -- Order type for planned order
1128 and ms.plan_id = mpe.plan_id
1129 and ms.process_seq_id = mpe.process_sequence_id
1130 and mpe.plan_id = mb.plan_id
1131 and mpe.bill_sequence_id = mb.bill_sequence_id(+)
1132 and mpe.sr_instance_id = mb.sr_instance_id
1133 and ms.transaction_id= var_transaction_id;
1134
1135 exception
1136 when no_data_found then
1137 var_alternate_bom := null;
1138 when others then
1139 var_buf := var_entity||': '||sqlerrm;
1140 fnd_file.put_line(FND_FILE.LOG, var_buf);
1141 ROLLBACK;
1142 end;
1143
1144 /* Getting Alternate routing */
1145
1146 begin
1147
1148 select
1149 mr.alternate_routing_designator
1150 into var_alternate_routing
1151 from msc_supplies ms,
1152 msc_process_effectivity mpe,
1153 msc_routings mr
1154 where
1155 ms.plan_id = var_plan_id -- Plan_id
1156 and ms.order_type = 5 -- Order type for planned order
1157 and ms.plan_id = mpe.plan_id
1158 and ms.process_seq_id = mpe.process_sequence_id
1159 and mpe.plan_id = mr.plan_id
1160 and mpe.routing_sequence_id = mr.routing_sequence_id(+)
1161 and mpe.sr_instance_id = mr.sr_instance_id
1162 and ms.transaction_id= var_transaction_id;
1163
1164 exception
1165 when no_data_found then
1166 var_alternate_routing := null;
1167 when others then
1168 var_buf := var_entity||': '||sqlerrm;
1169 fnd_file.put_line(FND_FILE.LOG, var_buf);
1170 ROLLBACK;
1171 end;
1172
1173
1174 UPDATE msc_supplies
1175 SET old_order_quantity = new_order_quantity,
1176 quantity_in_process = new_order_quantity,
1177 implement_date = new_schedule_date,
1178 implement_dock_date = new_dock_date, -- Added for Ferring Enhancement - bug 14751383 / 14358365
1179 implement_quantity = new_order_quantity,
1180 implement_firm = DECODE(var_firm_jobs, 'Y', 1, 2),
1181
1182 /*for discrete job reschedules existing name is populated */
1183 implement_job_name = decode(var_order_type,PLANNED_ORDER,var_job_prefix||to_char(var_wip_job_number)
1184 ,PLANNED_NEW_BUY_ORDER,var_job_prefix||to_char(var_wip_job_number),3,order_number),
1185
1186 /*implement status code must be set to cancel (7) in case of reschedules */
1187 implement_status_code = DECODE(disposition_status_type, CANCEL,7, var_impl_status_code),
1188 load_type = var_load_type,
1189
1190 /*this is 1 for reschedules */
1191 reschedule_flag=decode(order_type,PLANNED_ORDER,reschedule_flag,PLANNED_NEW_BUY_ORDER,reschedule_flag,1),
1192
1193 /*for reschedules it is 1 */
1194 implement_as = decode(order_type,PLANNED_ORDER,WIP_DISCRETE_JOB,PLANNED_NEW_BUY_ORDER,WIP_DISCRETE_JOB,1),
1195 implement_wip_class_code = var_wip_class_code,
1196 implement_source_org_id = NULL,
1197 implement_supplier_id = NULL,
1198 implement_supplier_site_id = NULL,
1199 implement_project_id = project_id,
1200 implement_task_id = task_id,
1201 implement_unit_number = unit_number,
1202 implement_demand_class = var_demand_class,
1203 implement_alternate_bom = var_alternate_bom,
1204 implement_alternate_routing= var_alternate_routing,
1205 release_status = var_release_status,
1206 last_updated_by = var_user_id
1207 WHERE transaction_id = var_transaction_id
1208 AND sr_instance_id = var_sr_instance_id
1209 AND plan_id = var_plan_id;
1210
1211 ELSIF (var_make_buy_code = BUY OR var_order_type IN (PURCHASE_ORDER,PO_REQUISITION)) THEN
1212
1213 -- IR/ISO enhancement.
1214 -- For DRP/ASCP plan, we need to check if the ISO is present in
1215 -- the same plan. If, so, we need to update the load_type to 64
1216
1217 IF (var_order_type = PO_REQUISITION) THEN
1218 BEGIN
1219 var_sales_order_line_id := NULL;
1220 SELECT sales_order_line_id
1221 INTO var_sales_order_line_id
1222 FROM msc_demands
1223 WHERE
1224 plan_id = var_plan_id
1225 AND sr_instance_id = var_sr_instance_id
1226 AND disposition_id = var_transaction_id
1227 and origination_type = 30
1228 and rownum = 1;
1229
1230 EXCEPTION
1231 WHEN NO_DATA_FOUND THEN
1232 var_sales_order_line_id := NULL;
1233 END;
1234
1235 IF (var_sales_order_line_id IS NOT NULL) THEN
1236 var_load_type := DRP_REQ_RESCHED;
1237 END IF;
1238
1239 END IF;
1240
1241 -- --------------------------------------------------------------------
1242 -- Verify PO Reqs parameters
1243 --
1244 -- If Planner is not an active employee, do not release
1245 -- --------------------------------------------------------------------
1246 var_entity := 'Employee';
1247
1248 SELECT count(*)
1249 INTO var_count
1250 FROM msc_planners
1251 WHERE employee_id = var_planner_employee_id
1252 AND current_employee_flag= 1;
1253
1254 IF var_debug THEN
1255 var_buf := '----------------- ';
1256 fnd_file.put_line(FND_FILE.LOG, var_buf);
1257 var_buf := 'Item ID : '||var_inventory_item_id;
1258 fnd_file.put_line(FND_FILE.LOG, var_buf);
1259 var_buf := 'Item Make/Buy : '||var_make_buy_code;
1260 fnd_file.put_line(FND_FILE.LOG, var_buf);
1261 var_buf := 'New Schedule Date : '||var_new_schedule_date;
1262 fnd_file.put_line(FND_FILE.LOG, var_buf);
1263 var_buf := 'Quantity : '||var_new_order_quantity;
1264 fnd_file.put_line(FND_FILE.LOG, var_buf);
1265 var_buf := 'Planner emp ID : '||var_planner_employee_id;
1266 fnd_file.put_line(FND_FILE.LOG, var_buf);
1267 var_buf := 'Active employee : '||var_count;
1268 fnd_file.put_line(FND_FILE.LOG, var_buf);
1269 var_buf := 'Order Type : '||var_order_type;
1270 fnd_file.put_line(FND_FILE.LOG, var_buf);
1271 END IF;
1272
1273 IF (var_count = 0) THEN
1274
1275 -- PO Req is not released because Planner is not an active employee
1276
1277 var_entity := 'Non-released PO Req';
1278
1279 SELECT param.organization_code,
1280 msi.item_name,
1281 msi.planner_code
1282 INTO var_org_code, var_item, var_planner_code
1283 FROM msc_system_items msi,
1284 msc_trading_partners param,
1285 msc_supplies mr
1286 WHERE mr.transaction_id = var_transaction_id
1287 AND mr.sr_instance_id = var_sr_instance_id
1288 AND mr.plan_id = var_plan_id
1289 AND msi.organization_id = mr.organization_id
1290 AND msi.inventory_item_id = mr.inventory_item_id
1291 AND msi.sr_instance_id = mr.sr_instance_id
1292 AND msi.plan_id = -1
1293 AND param.sr_tp_id = mr.organization_id
1294 AND param.sr_instance_id = mr.sr_instance_id
1295 AND param.partner_type= 3;
1296
1297 /* bug 4258346 - set retcode = 1 so that the program will complete in warning
1298 if the planner is not an active employee */
1299
1300 retcode := 1;
1301
1302 var_buf := '................. ';
1303 fnd_file.put_line(FND_FILE.LOG, var_buf);
1304
1305 fnd_message.set_name('MRP', 'MRP_UNRELEASED_ORDER1');
1306 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1307
1308 fnd_message.set_name('MRP', 'MRP_UNRELEASED_ORDER2');
1309 fnd_message.set_token('PLANNER_VALUE', var_planner_code);
1310 fnd_message.set_token('ORG_VALUE', var_org_code);
1311 fnd_message.set_token('ITEM_VALUE', var_item);
1312 fnd_message.set_token('DATE_VALUE', to_char(var_new_schedule_date));
1313 fnd_message.set_token('QTY_VALUE', to_char(var_new_order_quantity));
1314 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1315
1316 ELSE
1317
1318 -- ------------------------------------------------------------------
1319 -- Update PO Reqs planned orders and reschedules
1320 -- ------------------------------------------------------------------
1321 var_entity := 'PO Planned Orders';
1322
1323 UPDATE msc_supplies
1324 SET old_order_quantity = new_order_quantity,
1325 quantity_in_process = new_order_quantity,
1326 implement_date = new_schedule_date,
1327
1328 /*implement quantity is 0 in case of cancels */
1329 implement_quantity = decode(disposition_status_type, 2,0,new_order_quantity),
1330 load_type = var_load_type,
1331
1332 /*this is 1 for reschedules */
1333 reschedule_flag=decode(order_type,PLANNED_ORDER,reschedule_flag,PLANNED_NEW_BUY_ORDER,reschedule_flag,1),
1334
1335 /*for reschedules it is 1 */
1336 implement_as = decode(order_type,PLANNED_ORDER,PO_REQUISITION,PLANNED_NEW_BUY_ORDER,PO_REQUISITION,1),
1337
1338 /*implement status code must be set to cancel (7) in case of reschedules */
1339 implement_status_code = DECODE(disposition_status_type, CANCEL,7, null),
1340
1341 implement_firm = firm_planned_type,
1342 implement_dock_date = new_dock_date,
1343 implement_ship_date = new_ship_date, --9849059; checked in with RP auto release fix
1344 implement_employee_id = var_planner_employee_id,
1345 implement_uom_code = var_primary_uom_code,
1346 implement_location_id = var_location_id,
1347 implement_source_org_id = source_organization_id,
1348 implement_supplier_id = source_supplier_id,
1349 implement_supplier_site_id = source_supplier_site_id,
1350 implement_project_id = project_id,
1351 implement_task_id = task_id,
1352 implement_unit_number = unit_number,
1353 implement_demand_class = NULL,
1354 release_status = var_release_status,
1355 last_updated_by = var_user_id
1356 WHERE transaction_id = var_transaction_id
1357 AND sr_instance_id = var_sr_instance_id
1358 AND plan_id = var_plan_id;
1359
1360
1361 END IF; /* Count */
1362
1363 END IF; /* Make Buy code */
1364
1365 var_prev_org_id := var_org_id;
1366 var_prev_inventory_item_id := var_inventory_item_id;
1367
1368
1369 END IF; /*VMI Check -- fwd port 3181822 */
1370
1371 END LOOP;
1372
1373 CLOSE planned_orders_and_reschedules;
1374
1375 COMMIT WORK;
1376
1377 p_where_clause :=null ;
1378
1379 If var_plan_type =8 then
1380 OPEN ROs_to_release;
1381 LOOP
1382 var_entity := 'Fetch repair orders ';
1383
1384
1385 FETCH ROs_to_release INTO var_transaction_id,var_order_type,var_sr_instance_id,var_plan_id ;
1386 EXIT WHEN ROs_to_release%NOTFOUND;
1387 p_where_clause := ' transaction_id = ' || var_transaction_id || ' and plan_id = ' || var_plan_id ;
1388
1389
1390 MSC_SELECT_ALL_FOR_RELEASE_PUB.Update_Implement_Attrib(p_where_clause ,
1391 NULL,
1392 NULL,
1393 NULL,
1394 NULL,
1395 NULL,
1396 p_total_rows ,
1397 p_succ_rows ,
1398 p_error_rows
1399 );
1400
1401
1402 UPDATE msc_supplies
1403 SET batch_id = MSC_Rel_Plan_PUB.g_batch_id,
1404 load_type =decode(var_order_type,PLANNED_ERO,ERO_LOAD,IRO_LOAD)
1405 WHERE transaction_id = var_transaction_id
1406 AND sr_instance_id = var_sr_instance_id
1407 AND plan_id = var_plan_id ;
1408
1409 END LOOP;
1410 CLOSE ROs_to_release;
1411 END IF ;
1412
1413 commit;
1414
1415 If var_plan_type in (5,8) then
1416 OPEN transfer_orders_to_release;
1417 LOOP
1418 var_entity := 'Fetch transfer orders ';
1419
1420 FETCH transfer_orders_to_release INTO var_transaction_id,var_order_type,var_sr_instance_id,
1421 var_plan_id ;
1422 EXIT WHEN transfer_orders_to_release%NOTFOUND;
1423 p_where_clause := ' transaction_id = ' || var_transaction_id || ' and plan_id = ' || var_plan_id;
1424
1425 MSC_SELECT_ALL_FOR_RELEASE_PUB.Update_Implement_Attrib(p_where_clause ,
1426 NULL,
1427 NULL,
1428 NULL,
1429 NULL,
1430 NULL,
1431 p_total_rows ,
1432 p_succ_rows ,
1433 p_error_rows
1434 );
1435 UPDATE msc_supplies
1436 SET batch_id = MSC_Rel_Plan_PUB.g_batch_id,
1437 load_type =TRANSFER_LOAD
1438 WHERE transaction_id = var_transaction_id
1439 AND sr_instance_id = var_sr_instance_id
1440 AND plan_id = var_plan_id ;
1441
1442 END LOOP;
1443 CLOSE transfer_orders_to_release;
1444 END IF ;
1445 commit ;
1446
1447
1448 If (var_plan_type =1
1449 and MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y') then
1450 OPEN eam_cmro_orders_to_release;
1451 LOOP
1452 var_entity := 'Fetch eam cmro orders ';
1453
1454 FETCH eam_cmro_orders_to_release INTO var_transaction_id,var_order_type,var_sr_instance_id,
1455 var_plan_id ;
1456 EXIT WHEN eam_cmro_orders_to_release%NOTFOUND;
1457
1458 UPDATE msc_supplies
1459 SET batch_id = MSC_Rel_Plan_PUB.g_batch_id,
1460 /* update rest of the implement columns here */
1461 old_order_quantity = new_order_quantity,
1462 quantity_in_process = new_order_quantity,
1463 implement_date = new_schedule_date,
1464 implement_quantity = new_order_quantity,
1465 implement_firm = 1,
1466 load_type = decode(var_order_type,PLANNED_REP_WO,CMRO_EAM_MASS_LOAD,RESCHEDULE_EAM_CMRO,CMRO_EAM_RESCHED),
1467 reschedule_flag = DECODE(var_order_type,PLANNED_REP_WO,reschedule_flag,1),
1468 implement_as = decode(var_order_type,PLANNED_REP_WO,CMRO_EAM_MASS_LOAD,RESCHEDULE_EAM_CMRO,CMRO_EAM_RESCHED),
1469 implement_project_id = project_id,
1470 implement_task_id = task_id,
1471 implement_unit_number = unit_number,
1472 release_status = var_release_status,
1473 last_updated_by = var_user_id
1474 WHERE transaction_id = var_transaction_id
1475 AND sr_instance_id = var_sr_instance_id
1476 AND plan_id = var_plan_id ;
1477
1478 /*????update other columns in msc_supplies if UI is not doing it....
1479 wht abt the wip class code , job name etc.. who will populate this*/
1480
1481 END LOOP;
1482 CLOSE eam_cmro_orders_to_release;
1483 END IF ;
1484 commit ;
1485 -- ------------------------------------------------------------------------
1486 -- Release the planned orders and reschedules
1487 -- ------------------------------------------------------------------------
1488 var_entity := 'Release';
1489 update msc_supplies
1490 set releasable= null where releasable = 99 ;
1491
1492 /*purchase order reschedules require separate function call */
1493 msc_rel_wf.reschedule_purchase_orders
1494 (arg_plan_id,lv_arg_org_id_new,lv_arg_instance_id_new,lv_arg_org_id_new,
1495 lv_arg_instance_id_new,var_count_po,var_released_instance,
1496 var_po_res_id,var_po_res_count,var_po_pwb_count);
1497
1498 MSC_Rel_Plan_PUB.msc_release_plan_sc
1499 (arg_plan_id, lv_arg_org_id_new, lv_arg_instance_id_new,
1500 lv_arg_org_id_new, lv_arg_instance_id_new, lv_plan_name, var_user_id,
1501 var_po_group_by, var_po_batch_number, var_wip_group_id,
1502 var_loaded_jobs, var_loaded_reqs, var_loaded_scheds,
1503 var_resched_jobs, var_resched_reqs, var_wip_req_id,
1504 var_req_load_id, var_req_resched_id, var_released_instance_id, NULL,NULL,
1505 var_loaded_lot_jobs, var_resched_lot_jobs,var_osfm_req_id,
1506 var_resched_eam_jobs,var_eam_req_id,
1507 var_loaded_int_reqs,var_resched_int_reqs,var_int_req_load_id,var_int_req_resched_id,
1508 var_loaded_int_repair_orders,var_int_repair_orders_id,var_loaded_ext_repair_orders,
1509 var_ext_repair_orders_id,var_loaded_eam_jobs,var_loaded_eam_req_id,
1510 var_resched_cmro_jobs,var_resched_cmro_req_id,var_loaded_cmro_jobs,var_loaded_cmro_req_id);
1511
1512 UPDATE msc_plans
1513 SET release_reschedules=2
1514 WHERE plan_id=arg_plan_id
1515 AND var_plan_type < RP_MRP_PLAN; -- For RP Plan keep the flag unchanged
1516 commit;
1517
1518 var_buf := '+++++++++++++++++ ';
1519 fnd_file.put_line(FND_FILE.LOG, var_buf);
1520
1521 var_released_instance_count:= var_released_instance_id.count;
1522
1523 DECLARE
1524 i number;
1525 lv_instance_code varchar2(3);
1526
1527 BEGIN
1528
1529 FOR i IN 1..var_released_instance_id.count LOOP
1530
1531 select instance_code
1532 into lv_instance_code
1533 from msc_apps_instances
1534 where instance_id= var_released_instance_id(i);
1535
1536 fnd_message.set_name('MSC', 'MSC_AR_LOADED_INSTANCE');
1537 fnd_message.set_token('INSTANCE', lv_instance_code);
1538 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1539
1540 fnd_message.set_name('MRP', 'LOADED_WIP');
1541 fnd_message.set_token('VALUE', to_char(var_loaded_jobs(i)));
1542 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1543
1544 fnd_message.set_name('MRP', 'LOADED_WIP');
1545 fnd_message.set_token('VALUE', to_char(var_loaded_lot_jobs(i))||' lot Jobs');
1546 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1547
1548 fnd_message.set_name('MRP', 'LOADED_PO');
1549 fnd_message.set_token('VALUE', to_char(var_loaded_reqs(i)));
1550 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1551
1552 fnd_message.set_name('MRP', 'LOAD_WIP_REQUEST_ID');
1553 fnd_message.set_token('VALUE', to_char(var_wip_req_id(i)));
1554 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1555
1556 fnd_message.set_name('MRP', 'LOAD_WIP_REQUEST_ID');
1557 fnd_message.set_token('VALUE', to_char(var_osfm_req_id(i))||' OSFM Request Id');
1558 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1559
1560 fnd_message.set_name('MRP', 'LOAD_PO_REQUEST_ID');
1561 fnd_message.set_token('VALUE', to_char(var_req_load_id(i)));
1562 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1563
1564 fnd_message.set_name('MSC', 'LOADED_RESCHEDULED_JOB');
1565 fnd_message.set_token('VALUE', to_char(var_resched_jobs(i)));
1566 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1567
1568 fnd_message.set_name('MSC', 'LOADED_RESCHEDULED_REQS');
1569 fnd_message.set_token('VALUE', to_char(var_resched_reqs(i)));
1570 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1571
1572 fnd_file.put_line(FND_FILE.LOG, 'Loaded internal repair order :'|| var_loaded_int_repair_orders(i));
1573 fnd_file.put_line(FND_FILE.LOG, 'Loaded external repair order :'|| var_loaded_ext_repair_orders(i));
1574
1575 fnd_file.put_line(FND_FILE.LOG, 'Loaded Eam Jobs :'|| var_loaded_eam_jobs(i));
1576 fnd_file.put_line(FND_FILE.LOG, 'Loaded Reschedule Eam Work orders :'|| var_resched_eam_jobs(i));
1577
1578 fnd_file.put_line(FND_FILE.LOG, 'Loaded CMRO Jobs :'|| var_loaded_cmro_jobs(i));
1579 fnd_file.put_line(FND_FILE.LOG, 'Loaded Reschedule CMRO Work orders :'|| var_resched_cmro_jobs(i));
1580
1581
1582 /* Bug 2595278 - added Null condition */
1583 IF ((var_loaded_jobs(i) > 0) AND ((var_wip_req_id(i) = 0) OR (var_wip_req_id(i) IS NULL))) THEN
1584 fnd_file.new_line(FND_FILE.LOG, 1);
1585 fnd_message.set_name('MRP', 'CANNOT SUBMIT REQUEST-JOBS');
1586 var_buf := fnd_message.get;
1587 fnd_file.put_line(FND_FILE.LOG, var_buf);
1588
1589 retcode := 2;
1590 errbuf := var_buf;
1591 END IF;
1592
1593
1594 IF ((var_loaded_lot_jobs(i) > 0) AND (var_osfm_req_id(i) = 0)) THEN
1595 fnd_file.new_line(FND_FILE.LOG, 1);
1596 fnd_message.set_name('MRP', 'CANNOT SUBMIT LOT-JOBS INTERFACE');
1597 var_buf := fnd_message.get;
1598 fnd_file.put_line(FND_FILE.LOG, var_buf);
1599
1600 retcode := 2;
1601 errbuf := var_buf;
1602 END IF;
1603
1604 IF ((var_loaded_reqs(i) > 0) AND (var_req_load_id(i) = 0)) THEN
1605 fnd_file.new_line(FND_FILE.LOG, 1);
1606 fnd_message.set_name('MRP', 'CANNOT SUBMIT REQUEST-REQS');
1607 var_buf := fnd_message.get;
1608 fnd_file.put_line(FND_FILE.LOG, var_buf);
1609
1610 retcode := 2;
1611 errbuf := var_buf;
1612 END IF;
1613
1614 IF ((var_loaded_int_repair_orders(i) > 0) AND (var_int_repair_orders_id(i) = 0)) THEN
1615 var_buf:= 'cannot submit the internal repair order request';
1616 fnd_file.put_line(FND_FILE.LOG, var_buf);
1617
1618 retcode := 2;
1619 errbuf := var_buf;
1620 END IF;
1621
1622 IF ((var_loaded_ext_repair_orders(i) > 0) AND (var_ext_repair_orders_id(i) = 0)) THEN
1623 var_buf:= 'cannot submit the external repair order request';
1624 fnd_file.put_line(FND_FILE.LOG, var_buf);
1625
1626 retcode := 2;
1627 errbuf := var_buf;
1628 END IF;
1629
1630
1631 IF ((var_loaded_eam_jobs(i) > 0) AND ((var_loaded_eam_req_id(i) = 0) OR (var_loaded_eam_req_id(i) IS NULL))) THEN
1632 fnd_file.new_line(FND_FILE.LOG, 1);
1633 fnd_message.set_name('MRP', 'CANNOT SUBMIT EAM_JOBS INTERFACE');
1634 var_buf := fnd_message.get;
1635 fnd_file.put_line(FND_FILE.LOG, var_buf);
1636
1637 retcode := 2;
1638 errbuf := var_buf;
1639 END IF;
1640
1641
1642 IF ((var_resched_eam_jobs(i) > 0) AND (var_eam_req_id(i) = 0)) THEN
1643 fnd_file.new_line(FND_FILE.LOG, 1);
1644 fnd_message.set_name('MRP', 'CANNOT SUBMIT RESCHED_EAM INTERFACE');
1645 var_buf := fnd_message.get;
1646 fnd_file.put_line(FND_FILE.LOG, var_buf);
1647
1648 retcode := 2;
1649 errbuf := var_buf;
1650 END IF;
1651
1652 IF ((var_loaded_cmro_jobs(i) > 0) AND ((var_loaded_cmro_req_id(i) = 0) OR (var_loaded_cmro_req_id(i) IS NULL))) THEN
1653 fnd_file.new_line(FND_FILE.LOG, 1);
1654 fnd_message.set_name('MRP', 'CANNOT SUBMIT CMRO_JOBS INTERFACE');
1655 var_buf := fnd_message.get;
1656 fnd_file.put_line(FND_FILE.LOG, var_buf);
1657
1658 retcode := 2;
1659 errbuf := var_buf;
1660 END IF;
1661
1662
1663 IF ((var_resched_cmro_jobs(i) > 0) AND (var_resched_cmro_req_id(i) = 0)) THEN
1664 fnd_file.new_line(FND_FILE.LOG, 1);
1665 fnd_message.set_name('MRP', 'CANNOT SUBMIT RESCHED_CMRO INTERFACE');
1666 var_buf := fnd_message.get;
1667 fnd_file.put_line(FND_FILE.LOG, var_buf);
1668
1669 retcode := 2;
1670 errbuf := var_buf;
1671 END IF;
1672
1673 END LOOP;
1674
1675 var_buf := '++++++++++';
1676 fnd_file.put_line(FND_FILE.LOG, var_buf);
1677 var_buf := 'PO reschedules';
1678 fnd_file.put_line(FND_FILE.LOG, var_buf);
1679
1680 FOR i IN 1..var_count_po LOOP
1681 select instance_code
1682 into lv_instance_code
1683 from msc_apps_instances
1684 where instance_id= var_released_instance(i);
1685
1686 fnd_message.set_name('MSC', 'MSC_AR_LOADED_INSTANCE');
1687 fnd_message.set_token('INSTANCE', lv_instance_code);
1688 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1689 fnd_message.set_name('MSC', 'LOADED_RESCHEDULED_PO');
1690 fnd_message.set_token('VALUE', to_char(var_po_res_count(i)));
1691 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1692 END LOOP;
1693
1694 END;
1695 RETURN;
1696
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699
1700 IF planned_orders_and_reschedules%ISOPEN THEN CLOSE planned_orders_and_reschedules; END IF;
1701
1702 var_buf := var_entity||': '||sqlerrm;
1703 fnd_file.put_line(FND_FILE.LOG, var_buf);
1704
1705 ROLLBACK;
1706 retcode := 2;
1707 errbuf := var_buf;
1708 RETURN;
1709 END msc_auto_release;
1710
1711 PROCEDURE msc_web_service_release (
1712 pPlan_id Number,
1713 Use_Plan_start_date Varchar2,
1714 RETCODE OUT NOCOPY Number,
1715 ERRMSG OUT NOCOPY Varchar2,
1716 REQ_ID OUT NOCOPY ReqTblTyp
1717 ) IS
1718 v_instance_id NUMBER;
1719 v_owning_org_id NUMBER;
1720
1721 i number;
1722 j number := 1;
1723 lv_instance_code msc_apps_instances.INSTANCE_CODE%TYPE;
1724
1725 BEGIN
1726 --get instance_id and org_for the given plan.
1727 BEGIN
1728 SELECT mp.sr_instance_id,
1729 mp.organization_id
1730 INTO v_instance_id,
1731 v_owning_org_id
1732 FROM msc_plans mp
1733 WHERE mp.plan_id = pPlan_id;
1734 EXCEPTION
1735 WHEN NO_DATA_FOUND THEN
1736 ERRMSG := 'Not a Valid plan ID';
1737 RETCODE := MSC_UTIL.G_ERROR;
1738 RETURN;
1739 WHEN OTHERS THEN
1740 ERRMSG := SQLERRM;
1741 RETCODE := MSC_UTIL.G_ERROR;
1742 RETURN;
1743 END;
1744
1745 BEGIN
1746 msc_auto_release(ERRMSG,
1747 RETCODE,
1748 pPlan_id,
1749 v_owning_org_id,
1750 v_instance_id,
1751 Use_Plan_start_date);
1752
1753
1754 FOR i IN 1..var_released_instance_id.count LOOP
1755
1756 select instance_code
1757 into lv_instance_code
1758 from msc_apps_instances
1759 where instance_id= var_released_instance_id(i);
1760
1761 REQ_ID(j).instanceCode := lv_instance_code;
1762 REQ_ID(j).ReqID := var_wip_req_id(i);
1763 REQ_ID(j).ReqType := 'Loaded WIP Jobs :' || var_loaded_jobs(i) ;
1764 j := j + 1;
1765
1766 REQ_ID(j).instanceCode := lv_instance_code;
1767 REQ_ID(j).ReqID := var_osfm_req_id(i);
1768 REQ_ID(j).ReqType := 'Loaded Lot Jobs :' || var_loaded_lot_jobs(i) ;
1769 REQ_ID(j).ReqType := REQ_ID(j).ReqType || ' Resched Lot Jobs :' || var_resched_lot_jobs(i) ;
1770 j := j + 1;
1771
1772
1773 REQ_ID(j).instanceCode := lv_instance_code;
1774 REQ_ID(j).ReqID := var_req_load_id(i);
1775 REQ_ID(j).ReqType := 'Loaded PR :' || var_loaded_reqs(i);
1776 j := j + 1;
1777
1778 REQ_ID(j).instanceCode := lv_instance_code;
1779 REQ_ID(j).ReqID := var_resched_jobs(i);
1780 REQ_ID(j).ReqType := 'Resched PR :' || var_resched_reqs(i);
1781 j := j + 1;
1782
1783 REQ_ID(j).instanceCode := lv_instance_code;
1784 REQ_ID(j).ReqID := var_eam_req_id(i);
1785 REQ_ID(j).ReqType := 'Resched EAM jobs:' || var_resched_eam_jobs(i);
1786 j := j + 1;
1787
1788 REQ_ID(j).instanceCode := lv_instance_code;
1789 REQ_ID(j).ReqID := var_int_req_load_id(i);
1790 REQ_ID(j).ReqType := 'Loaded Int Reqs:' || var_loaded_int_reqs(i);
1791 j := j + 1;
1792
1793 REQ_ID(j).instanceCode := lv_instance_code;
1794 REQ_ID(j).ReqID := var_int_req_resched_id(i);
1795 REQ_ID(j).ReqType := 'Resched Int Reqs:' || var_resched_int_reqs(i);
1796 j := j + 1;
1797
1798 REQ_ID(j).instanceCode := lv_instance_code;
1799 REQ_ID(j).ReqID := var_int_repair_orders_id(i);
1800 REQ_ID(j).ReqType := 'IROs :' || var_loaded_int_repair_orders(i);
1801 j := j + 1;
1802
1803 REQ_ID(j).instanceCode := lv_instance_code;
1804 REQ_ID(j).ReqID := var_ext_repair_orders_id(i);
1805 REQ_ID(j).ReqType := 'EROs :' || var_loaded_ext_repair_orders(i);
1806 j := j + 1;
1807
1808
1809 REQ_ID(j).instanceCode := lv_instance_code;
1810 REQ_ID(j).ReqID := var_loaded_eam_req_id(i);
1811 REQ_ID(j).ReqType := 'EAM new jobs :' || var_loaded_eam_jobs(i);
1812 j := j + 1;
1813
1814 REQ_ID(j).instanceCode := lv_instance_code;
1815 REQ_ID(j).ReqID := var_loaded_eam_req_id(i);
1816 REQ_ID(j).ReqType := 'CMRO new jobs :' || var_loaded_cmro_jobs(i);
1817 j := j + 1;
1818
1819 REQ_ID(j).instanceCode := lv_instance_code;
1820 REQ_ID(j).ReqID := var_loaded_eam_req_id(i);
1821 REQ_ID(j).ReqType := 'Resched CMRO jobs :' || var_resched_cmro_jobs(i);
1822 j := j + 1;
1823
1824 /* Bug 2595278 - added Null condition */
1825 IF ((var_loaded_jobs(i) > 0) AND ((var_wip_req_id(i) = 0) OR (var_wip_req_id(i) IS NULL))) THEN
1826 fnd_file.new_line(FND_FILE.LOG, 1);
1827 fnd_message.set_name('MRP', 'CANNOT SUBMIT REQUEST-JOBS');
1828 ERRMSG := ERRMSG || ' ' || fnd_message.get;
1829 RETCODE := MSC_UTIL.G_ERROR;
1830 END IF;
1831
1832
1833 IF ((var_loaded_lot_jobs(i) > 0) AND (var_osfm_req_id(i) = 0)) THEN
1834 fnd_file.new_line(FND_FILE.LOG, 1);
1835 fnd_message.set_name('MRP', 'CANNOT SUBMIT LOT-JOBS INTERFACE');
1836 ERRMSG := ERRMSG || ' ' || fnd_message.get;
1837 RETCODE := MSC_UTIL.G_ERROR;
1838 END IF;
1839
1840 IF ((var_loaded_reqs(i) > 0) AND (var_req_load_id(i) = 0)) THEN
1841 fnd_file.new_line(FND_FILE.LOG, 1);
1842 fnd_message.set_name('MRP', 'CANNOT SUBMIT REQUEST-REQS');
1843 ERRMSG := ERRMSG || ' ' ||fnd_message.get;
1844 RETCODE := MSC_UTIL.G_ERROR;
1845 END IF;
1846
1847 IF ((var_loaded_int_repair_orders(i) > 0) AND (var_int_repair_orders_id(i) = 0)) THEN
1848 ERRMSG := ERRMSG || ' ' ||' cannot submit the internal repair order request';
1849 RETCODE := MSC_UTIL.G_ERROR;
1850 END IF;
1851
1852 IF ((var_loaded_ext_repair_orders(i) > 0) AND (var_ext_repair_orders_id(i) = 0)) THEN
1853 ERRMSG := ERRMSG || ' ' || 'cannot submit the external repair order request';
1854 RETCODE := MSC_UTIL.G_ERROR;
1855 END IF;
1856
1857 IF ((var_loaded_eam_jobs(i) > 0) AND ((var_loaded_eam_req_id(i) = 0) OR (var_loaded_eam_req_id(i) IS NULL))) THEN
1858 ERRMSG := ERRMSG || ' ' || 'cannot submit the eam new job creation request';
1859 RETCODE := MSC_UTIL.G_ERROR;
1860 END IF;
1861
1862
1863 IF ((var_resched_eam_jobs(i) > 0) AND (var_eam_req_id(i) = 0)) THEN
1864 ERRMSG := ERRMSG || ' ' || 'cannot submit the resched eam jobs request';
1865 RETCODE := MSC_UTIL.G_ERROR;
1866 END IF;
1867
1868 IF ((var_loaded_cmro_jobs(i) > 0) AND ((var_loaded_cmro_req_id(i) = 0) OR (var_loaded_cmro_req_id(i) IS NULL))) THEN
1869 ERRMSG := ERRMSG || ' ' || 'cannot submit the cmro new job creation request';
1870 RETCODE := MSC_UTIL.G_ERROR;
1871 END IF;
1872
1873
1874 IF ((var_resched_cmro_jobs(i) > 0) AND (var_resched_cmro_req_id(i) = 0)) THEN
1875 ERRMSG := ERRMSG || ' ' || 'cannot submit the resched cmro jobs request';
1876 RETCODE := MSC_UTIL.G_ERROR;
1877 END IF;
1878
1879 END LOOP;
1880
1881
1882 FOR i IN 1..var_count_po LOOP
1883 select instance_code
1884 into lv_instance_code
1885 from msc_apps_instances
1886 where instance_id= var_released_instance(i);
1887
1888 REQ_ID(j).instanceCode := lv_instance_code;
1889 REQ_ID(j).ReqID := '';
1890 REQ_ID(j).ReqType := 'Resched PO :' || var_po_res_count(i);
1891 j := j + 1;
1892 END LOOP;
1893
1894 EXCEPTION
1895 WHEN OTHERS THEN
1896 ERRMSG := SQLERRM;
1897 RETCODE := MSC_UTIL.G_ERROR;
1898 END;
1899
1900 RETCODE := MSC_UTIL.G_SUCCESS;
1901 EXCEPTION
1902 WHEN OTHERS THEN
1903 ERRMSG := SQLERRM;
1904 RETCODE := MSC_UTIL.G_ERROR;
1905 END msc_web_service_release;
1906
1907 PROCEDURE QueryBasedAutoRelease(
1908 pPlan_id Number,
1909 RETCODE OUT NOCOPY Number,
1910 ERRMSG OUT NOCOPY Varchar2)
1911 IS
1912
1913 lv_auto_release_method NUMBER ;
1914 lv_inc_rescheds NUMBER ;
1915 V_TAB V_TAB_TYPE;
1916 Cursor queries is SELECT Distinct Mpers.query_type||' - '||Mpq.Applied_to
1917 FROM
1918 MSC_PLAN_QUERIES mpq,
1919 MSC_PQ_RESULTS mpr,
1920 MSC_PERSONAL_QUERIES mpers
1921 Where
1922 mpers.query_type in (1,4,5,9)
1923 AND mpq.plan_id = pPlan_id
1924 AND mpr.plan_id=mpq.plan_id
1925 AND mpq.query_id = mpers.query_id
1926 AND mpr.query_id = mpq.query_id ;
1927
1928
1929
1930 BEGIN
1931 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* In API ****** ');
1932 Select
1933 NVL(Mp.auto_release_method,-1) ,
1934 NVL(Mp.include_reschedules,-1)
1935 INTO
1936 lv_auto_release_method,
1937 lv_inc_rescheds
1938 From MSC_PLANS mp
1939 Where mp.plan_id = pPlan_id ;
1940
1941
1942
1943 OPEN queries;
1944 FETCH queries BULK COLLECT INTO V_TAB;
1945 FOR x IN 1..V_TAB.Count LOOP
1946 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,V_TAB(x));
1947 IF (V_TAB(x) = '1 - 1' ) THEN
1948 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 1-1 ****** ');
1949 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
1950 ( select ms.transaction_id
1951 from MSC_SUPPLIES ms,
1952 MSC_PQ_RESULTS mpr,
1953 MSC_PLAN_QUERIES mpq,
1954 MSC_PERSONAL_QUERIES mpers
1955 where mpr.plan_id = pPlan_id
1956 and ms.plan_id = mpr.plan_id
1957 and ms.sr_instance_id = mpr.sr_instance_id
1958 and ms.inventory_item_id = mpr.inventory_item_id
1959 and mpr.query_id =Mpq.Query_id
1960 and mpers.query_type = 1
1961 AND mpq.plan_id = pPlan_id
1962 AND mpr.plan_id=mpq.plan_id
1963 AND mpq.query_id = mpers.query_id
1964 AND mpr.query_id = mpq.query_id
1965 and Mpq.Applied_to = 1
1966 ) ;
1967
1968 --Item Query/Applied to Item-org
1969 ElsIF (V_TAB(x) = '1 - 2' ) THEN
1970 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 1-2 ****** ');
1971 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
1972 (Select ms.transaction_id
1973 from MSC_SUPPLIES ms,
1974 MSC_PQ_RESULTS mpr,
1975 MSC_PLAN_QUERIES mpq,
1976 MSC_PERSONAL_QUERIES mpers
1977 where mpr.plan_id = pPlan_id
1978 and ms.plan_id = mpr.plan_id
1979 and ms.sr_instance_id = mpr.sr_instance_id
1980 and ms.inventory_item_id = mpr.inventory_item_id
1981 and ms.organization_id = mpr.organization_id
1982 and mpr.query_id =Mpq.Query_id
1983 and mpers.query_type = 1
1984 AND mpq.plan_id = pPlan_id
1985 AND mpr.plan_id=mpq.plan_id
1986 AND mpq.query_id = mpers.query_id
1987 AND mpr.query_id = mpq.query_id
1988 and Mpq.Applied_to = 2
1989 ) ;
1990
1991 --Item Query/Applied to Supersession
1992 ElsIF (V_TAB(x) = '1 - 3' ) THEN
1993 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 1-3 ****** ');
1994 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
1995 (Select ms.transaction_id
1996 from MSC_SUPPLIES ms,
1997 MSC_PQ_RESULTS mpr,
1998 MSC_ITEM_SUBSTITUTES mis
1999 ,MSC_PLAN_QUERIES mpq,
2000 MSC_PERSONAL_QUERIES mpers
2001 where mpr.plan_id = pPlan_id
2002 and (mis.lower_item_id = mpr.inventory_item_id OR mis.higher_item_id = mpr.inventory_item_id)
2003 and mis.sr_instance_id = mpr.sr_instance_id
2004 and mis.plan_id = mpr.plan_id
2005 and mis.relationship_type = 8 ----------Rel Type SuperSession
2006 and ms.inventory_item_id in( mis.higher_item_id ,mis.Lower_item_id )
2007 and ms.sr_instance_id = mis.sr_instance_id
2008 and ms.plan_id = mis.plan_id
2009 and mpr.query_id = Mpq.Query_id
2010 and mpers.query_type = 1
2011 AND mpq.plan_id = pPlan_id
2012 AND mpr.plan_id=mpq.plan_id
2013 AND mpq.query_id = mpers.query_id
2014 AND mpr.query_id = mpq.query_id
2015 and Mpq.Applied_to = 3
2016 ) ;
2017
2018 --Item Query/Applied to Supersession-org
2019 ElsIF (V_TAB(x) = '1 - 4' ) THEN
2020 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 1-4 ****** ');
2021 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2022 (Select ms.transaction_id
2023 from MSC_SUPPLIES ms,
2024 MSC_PQ_RESULTS mpr,
2025 MSC_ITEM_SUBSTITUTES mis
2026 ,MSC_PLAN_QUERIES mpq,
2027 MSC_PERSONAL_QUERIES mpers
2028 where mpr.plan_id = pPlan_id
2029 and (mis.lower_item_id = mpr.inventory_item_id OR mis.higher_item_id = mpr.inventory_item_id)
2030 and mis.sr_instance_id = mpr.sr_instance_id
2031 and mis.plan_id = mpr.plan_id
2032 and mis.relationship_type = 8 ---------------------------- Rel Type SuperSession
2033 and ms.inventory_item_id in ( mis.higher_item_id ,mis.Lower_item_id )
2034 and ms.organization_id = mpr.organization_id
2035 and ms.sr_instance_id = mis.sr_instance_id
2036 and ms.plan_id = mis.plan_id
2037 and mpr.query_id =Mpq.Query_id
2038 and mpers.query_type = 1
2039 AND mpq.plan_id = pPlan_id
2040 AND mpr.plan_id=mpq.plan_id
2041 AND mpq.query_id = mpers.query_id
2042 AND mpr.query_id = mpq.query_id
2043 and Mpq.Applied_to = 4
2044 ) ;
2045
2046
2047 ---*********************** Query Type = Exception ***********************
2048 -- Exceptions Query/Applied to Item
2049 ElsIF (V_TAB(x) = '4 - 1' ) THEN
2050 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 4-1 ****** ');
2051 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2052 (Select ms.transaction_id
2053 from MSC_SUPPLIES ms,
2054 MSC_PQ_RESULTS mpr,
2055 MSC_EXCEPTION_DETAILS med
2056 ,MSC_PLAN_QUERIES mpq,
2057 MSC_PERSONAL_QUERIES mpers
2058 where mpr.plan_id = pPlan_id
2059 and mpr.query_id =Mpq.Query_id
2060 and mpr.summary_data =2
2061 and med.plan_id = mpr.plan_id
2062 and med.exception_detail_id = mpr.exception_id
2063 and med.sr_instance_id = mpr.sr_instance_id
2064 and med.inventory_item_id <> -1
2065 and med.TRANSACTION_ID IS NULL
2066 and med.DEMAND_ID IS NULL
2067 and ms.plan_id = med.plan_id
2068 and ms.inventory_item_id = med.inventory_item_id
2069 and ms.sr_instance_id = med.sr_instance_id
2070 and mpers.query_type = 4
2071 AND mpq.plan_id = pPlan_id
2072 AND mpr.plan_id=mpq.plan_id
2073 AND mpq.query_id = mpers.query_id
2074 AND mpr.query_id = mpq.query_id
2075 and Mpq.Applied_to = 1
2076 ) ;
2077
2078 ElsIF (V_TAB(x) = '4 - 2' ) THEN
2079 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 4-2 ****** ');
2080 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2081 ( Select ms.transaction_id
2082 from MSC_SUPPLIES ms,
2083 MSC_PQ_RESULTS mpr,
2084 MSC_EXCEPTION_DETAILS med
2085 ,MSC_PLAN_QUERIES mpq,
2086 MSC_PERSONAL_QUERIES mpers
2087 where mpr.plan_id = pPlan_id
2088 and mpr.query_id =Mpq.Query_id
2089 and mpr.summary_data =2
2090 and med.plan_id = mpr.plan_id
2091 and med.exception_detail_id = mpr.exception_id
2092 and med.sr_instance_id = mpr.sr_instance_id
2093 and med.inventory_item_id <> 1
2094 and med.TRANSACTION_ID IS NULL
2095 and med.DEMAND_ID IS NULL
2096 and ms.plan_id = med.plan_id
2097 and ms.inventory_item_id = med.inventory_item_id
2098 and ms.organization_id = med.organization_id
2099 and ms.sr_instance_id = med.sr_instance_id
2100 and mpers.query_type = 4
2101 AND mpq.plan_id = pPlan_id
2102 AND mpr.plan_id=mpq.plan_id
2103 AND mpq.query_id = mpers.query_id
2104 AND mpr.query_id = mpq.query_id
2105 and Mpq.Applied_to = 2
2106 ) ;
2107
2108
2109 ElsIF (V_TAB(x) = '4 - 3' ) THEN
2110 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 4-3 ****** ');
2111 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2112 ( Select ms.transaction_id
2113 from MSC_SUPPLIES ms,
2114 MSC_PQ_RESULTS mpr,
2115 MSC_EXCEPTION_DETAILS med,
2116 MSC_ITEM_SUBSTITUTES mis
2117 ,MSC_PLAN_QUERIES mpq,
2118 MSC_PERSONAL_QUERIES mpers
2119 where mpr.plan_id = pPlan_id
2120 and mpr.query_id =Mpq.Query_id
2121 and mpr.summary_data =2
2122 and med.plan_id = mpr.plan_id
2123 and med.exception_detail_id = mpr.exception_id
2124 and med.sr_instance_id = mpr.sr_instance_id
2125 and med.inventory_item_id <> 1
2126 and med.TRANSACTION_ID IS NULL
2127 and med.DEMAND_ID IS NULL
2128 and ( mis.lower_item_id = med.inventory_item_id or mis.higher_item_id = med.inventory_item_id )
2129 and mis.sr_instance_id = med.sr_instance_id
2130 and mis.plan_id = med.plan_id
2131 and mis.relationship_type = 8
2132 and ms.plan_id = mis.plan_id
2133 and ms.inventory_item_id in ( mis.higher_item_id , mis.lower_item_id)
2134 and ms.sr_instance_id = mis.sr_instance_id
2135 and mpers.query_type = 4
2136 AND mpq.plan_id = pPlan_id
2137 AND mpr.plan_id=mpq.plan_id
2138 AND mpq.query_id = mpers.query_id
2139 AND mpr.query_id = mpq.query_id
2140 and Mpq.Applied_to = 3
2141 );
2142
2143 ElsIF (V_TAB(x) = '4 - 4' ) THEN
2144 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 4-4 ****** ');
2145 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2146 (Select ms.transaction_id
2147 from MSC_SUPPLIES ms,
2148 MSC_PQ_RESULTS mpr,
2149 MSC_EXCEPTION_DETAILS med,
2150 MSC_ITEM_SUBSTITUTES mis
2151 ,MSC_PLAN_QUERIES mpq,
2152 MSC_PERSONAL_QUERIES mpers
2153 where mpr.plan_id = pPlan_id
2154 and mpr.query_id =Mpq.Query_id
2155 and mpr.summary_data =2
2156 and med.plan_id = mpr.plan_id
2157 and med.exception_detail_id = mpr.exception_id
2158 and med.sr_instance_id = mpr.sr_instance_id
2159 and med.inventory_item_id <> 1
2160 and med.TRANSACTION_ID IS NULL
2161 and med.DEMAND_ID IS NULL
2162 and ( mis.lower_item_id = med.inventory_item_id or mis.higher_item_id = med.inventory_item_id )
2163 and mis.sr_instance_id = med.sr_instance_id
2164 and mis.plan_id = med.plan_id
2165 and mis.relationship_type = 8
2166 and ms.plan_id = mis.plan_id
2167 and ms.inventory_item_id in ( mis.higher_item_id , mis.lower_item_id)
2168 and ms.organization_id = med.organization_id
2169 and ms.sr_instance_id = mis.sr_instance_id
2170 and mpers.query_type = 4
2171 AND mpq.plan_id = pPlan_id
2172 AND mpr.plan_id=mpq.plan_id
2173 AND mpq.query_id = mpers.query_id
2174 AND mpr.query_id = mpq.query_id
2175 and Mpq.Applied_to = 4
2176 ) ;
2177
2178 ---*********************** Query Type = Order ***********************
2179
2180 ElsIF (V_TAB(x) = '9 - 1' ) THEN
2181 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-1 ****** ');
2182 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2183 (Select ms.transaction_id
2184 from MSC_SUPPLIES ms,
2185 MSC_PQ_RESULTS mpr,
2186 MSC_SUPPLIES ms2
2187 ,MSC_PLAN_QUERIES mpq,
2188 MSC_PERSONAL_QUERIES mpers
2189 where mpr.plan_id = pPlan_id
2190 and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
2191 and ms2.ROWID = mpr.row_id
2192 and mpr.plan_id= ms2.plan_id
2193 and ms.plan_id= ms2.plan_id
2194 and ms.sr_instance_id = ms2.sr_instance_id
2195 and ms.inventory_item_id = ms2.inventory_item_id
2196 and mpers.query_type = 9
2197 AND mpq.plan_id = pPlan_id
2198 AND mpr.plan_id=mpq.plan_id
2199 AND mpq.query_id = mpers.query_id
2200 AND mpr.query_id = mpq.query_id
2201 and Mpq.Applied_to = 1
2202 union all
2203 Select ms.transaction_id
2204 from MSC_SUPPLIES ms,
2205 MSC_PQ_RESULTS mpr,
2206 MSC_DEMANDS md
2207 ,MSC_PLAN_QUERIES mpq,
2208 MSC_PERSONAL_QUERIES mpers
2209 where mpr.plan_id = pPlan_id
2210 and mpr.SOURCE_TABLE = 'MSC_DEMANDS'
2211 and md.ROWID = mpr.row_id
2212 and mpr.plan_id= md.plan_id
2213 and ms.plan_id= md.plan_id
2214 and ms.sr_instance_id = md.sr_instance_id
2215 and ms.inventory_item_id = md.inventory_item_id
2216 and mpr.query_id =Mpq.Query_id
2217 and mpers.query_type = 9
2218 AND mpq.plan_id = pPlan_id
2219 AND mpr.plan_id=mpq.plan_id
2220 AND mpq.query_id = mpers.query_id
2221 AND mpr.query_id = mpq.query_id
2222 and Mpq.Applied_to = 1
2223 ) ;
2224
2225
2226 ElsIF (V_TAB(x) = '9 - 2' ) THEN
2227 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-2 ****** ');
2228 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2229 (Select ms.transaction_id
2230 from MSC_SUPPLIES ms,
2231 MSC_PQ_RESULTS mpr,
2232 MSC_SUPPLIES ms2
2233 ,MSC_PLAN_QUERIES mpq,
2234 MSC_PERSONAL_QUERIES mpers
2235 where mpr.plan_id = pPlan_id
2236 and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
2237 and ms2.ROWID = mpr.row_id
2238 and mpr.plan_id= ms2.plan_id
2239 and mpr.sr_instance_id= ms2.sr_instance_id
2240 and ms.plan_id= ms2.plan_id
2241 and ms.sr_instance_id = ms2.sr_instance_id
2242 and ms.inventory_item_id = ms2.inventory_item_id
2243 and ms.organization_id = ms2.organization_id
2244 and mpr.query_id =Mpq.Query_id
2245 and mpers.query_type = 9
2246 AND mpq.plan_id = pPlan_id
2247 AND mpr.plan_id=mpq.plan_id
2248 AND mpq.query_id = mpers.query_id
2249 AND mpr.query_id = mpq.query_id
2250 and Mpq.Applied_to = 2
2251 union all
2252 Select ms.transaction_id
2253 from MSC_SUPPLIES ms,
2254 MSC_PQ_RESULTS mpr,
2255 MSC_DEMANDS md
2256 ,MSC_PLAN_QUERIES mpq,
2257 MSC_PERSONAL_QUERIES mpers
2258 where mpr.plan_id = pPlan_id
2259 and mpr.SOURCE_TABLE = 'MSC_DEMANDS'
2260 and md.ROWID = mpr.row_id
2261 and mpr.plan_id= md.plan_id
2262 and mpr.sr_instance_id= md.sr_instance_id
2263 and ms.plan_id= md.plan_id
2264 and ms.sr_instance_id = md.sr_instance_id
2265 and ms.inventory_item_id = md.inventory_item_id
2266 and ms.organization_id = md.organization_id
2267 and mpr.query_id =Mpq.Query_id
2268 and mpers.query_type = 9
2269 AND mpq.plan_id = pPlan_id
2270 AND mpr.plan_id=mpq.plan_id
2271 AND mpq.query_id = mpers.query_id
2272 AND mpr.query_id = mpq.query_id
2273 and Mpq.Applied_to = 2
2274 ) ;
2275
2276
2277 ElsIF (V_TAB(x) = '9 - 3' ) THEN
2278 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-3 ****** ');
2279 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2280 (Select ms.transaction_id
2281 from MSC_SUPPLIES ms,
2282 MSC_PQ_RESULTS mpr,
2283 MSC_SUPPLIES ms2,
2284 MSC_ITEM_SUBSTITUTES mis
2285 ,MSC_PLAN_QUERIES mpq,
2286 MSC_PERSONAL_QUERIES mpers
2287 where mpr.plan_id = pPlan_id
2288 and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
2289 and ms2.ROWID = mpr.row_id
2290 and mpr.plan_id= ms2.plan_id
2291 and mpr.sr_instance_id= ms2.sr_instance_id
2292 and (ms2.inventory_item_id = mis.lower_item_id OR ms2.inventory_item_id = mis.higher_item_id )
2293 --and ms2.organization_id = mis.organization_id
2294 and ms2.sr_instance_id = mis.sr_instance_id
2295 and ms2.plan_id = mis.plan_id
2296 and mis.relationship_type = 8 ---------------------------- Rel Type SuperSession
2297 and ms.plan_id= mis.plan_id
2298 and ms.sr_instance_id = mis.sr_instance_id
2299 and ms.inventory_item_id in ( mis.higher_item_id , mis.Lower_item_id)
2300 and mpr.query_id =Mpq.Query_id
2301 and mpers.query_type = 9
2302 AND mpq.plan_id = pPlan_id
2303 AND mpr.plan_id=mpq.plan_id
2304 AND mpq.query_id = mpers.query_id
2305 AND mpr.query_id = mpq.query_id
2306 and Mpq.Applied_to = 3
2307 union all
2308 Select ms.transaction_id
2309 from MSC_SUPPLIES ms,
2310 MSC_PQ_RESULTS mpr,
2311 MSC_DEMANDS md,
2312 MSC_ITEM_SUBSTITUTES mis
2313 ,MSC_PLAN_QUERIES mpq,
2314 MSC_PERSONAL_QUERIES mpers
2315 where mpr.plan_id = pPlan_id
2316 and mpr.SOURCE_TABLE = 'MSC_DEMANDS'
2317 and md.ROWID = mpr.row_id
2318 and mpr.plan_id= md.plan_id
2319 and mpr.sr_instance_id= md.sr_instance_id
2320 and ( md.inventory_item_id = mis.lower_item_id OR md.inventory_item_id = mis.higher_item_id )
2321 --and md.organization_id = mis.organization_id
2322 and md.sr_instance_id = mis.sr_instance_id
2323 and md.plan_id = mis.plan_id
2324 and mis.relationship_type = 8 ---------------------------- Rel Type SuperSession
2325 and ms.plan_id= mis.plan_id
2326 and ms.sr_instance_id = mis.sr_instance_id
2327 and ms.inventory_item_id in (mis.higher_item_id ,mis.lower_item_id )
2328 and mpr.query_id =Mpq.Query_id
2329 and mpers.query_type = 9
2330 AND mpq.plan_id = pPlan_id
2331 AND mpr.plan_id=mpq.plan_id
2332 AND mpq.query_id = mpers.query_id
2333 AND mpr.query_id = mpq.query_id
2334 and Mpq.Applied_to = 3
2335 ) ;
2336
2337 ElsIF (V_TAB(x) = '9 - 4' ) THEN
2338 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-4 ****** ');
2339 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2340 (Select ms.transaction_id
2341 from MSC_SUPPLIES ms,
2342 MSC_PQ_RESULTS mpr,
2343 MSC_SUPPLIES ms2,
2344 MSC_ITEM_SUBSTITUTES mis
2345 ,MSC_PLAN_QUERIES mpq,
2346 MSC_PERSONAL_QUERIES mpers
2347 where mpr.plan_id = pPlan_id
2348 and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
2349 and ms2.ROWID = mpr.row_id
2350 and mpr.plan_id= ms2.plan_id
2351 and mpr.sr_instance_id= ms2.sr_instance_id
2352 and (ms2.inventory_item_id = mis.lower_item_id OR ms2.inventory_item_id = mis.higher_item_id )
2353 --and ms2.organization_id = mis.organization_id
2354 and ms2.sr_instance_id = mis.sr_instance_id
2355 and ms2.plan_id = mis.plan_id
2356 and mis.relationship_type = 8 ---------------------------- Rel Type SuperSession
2357 and ms.plan_id= ms2.plan_id
2358 and ms.sr_instance_id = ms2.sr_instance_id
2359 and ms.inventory_item_id in (mis.higher_item_id ,mis.lower_item_id )
2360 and ms.organization_id = ms2.organization_id
2361 and mpr.query_id =Mpq.Query_id
2362 and mpers.query_type = 9
2363 AND mpq.plan_id = pPlan_id
2364 AND mpr.plan_id=mpq.plan_id
2365 AND mpq.query_id = mpers.query_id
2366 AND mpr.query_id = mpq.query_id
2367 and Mpq.Applied_to = 4
2368 union all
2369 Select ms.transaction_id
2370 from MSC_SUPPLIES ms,
2371 MSC_PQ_RESULTS mpr,
2372 MSC_DEMANDS md,
2373 MSC_ITEM_SUBSTITUTES mis
2374 ,MSC_PLAN_QUERIES mpq,
2375 MSC_PERSONAL_QUERIES mpers
2376 where mpr.plan_id = pPlan_id
2377 and mpr.SOURCE_TABLE = 'MSC_DEMANDS'
2378 and md.ROWID = mpr.row_id
2379 and mpr.plan_id= md.plan_id
2380 and mpr.sr_instance_id= md.sr_instance_id
2381 and ( md.inventory_item_id = mis.lower_item_id OR md.inventory_item_id = mis.higher_item_id )
2382 --and md.organization_id = mis.organization_id
2383 and md.sr_instance_id = mis.sr_instance_id
2384 and md.plan_id = mis.plan_id
2385 and mis.relationship_type = 8 ---------------------------- Rel Type SuperSession
2386 and ms.plan_id= md.plan_id
2387 and ms.sr_instance_id = md.sr_instance_id
2388 and ms.inventory_item_id in (mis.higher_item_id ,mis.lower_item_id )
2389 and ms.organization_id = md.organization_id
2390 and mpr.query_id =Mpq.Query_id
2391 and mpers.query_type = 9
2392 AND mpq.plan_id = pPlan_id
2393 AND mpr.plan_id=mpq.plan_id
2394 AND mpq.query_id = mpers.query_id
2395 AND mpr.query_id = mpq.query_id
2396 and Mpq.Applied_to = 4
2397 ) ;
2398
2399
2400 ElsIF (V_TAB(x) = '9 - 5' ) THEN
2401 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 9-5 ****** ');
2402 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2403 (Select ms.transaction_id
2404 from MSC_SUPPLIES ms,
2405 MSC_PQ_RESULTS mpr
2406 ,MSC_PLAN_QUERIES mpq,
2407 MSC_PERSONAL_QUERIES mpers
2408 where mpr.plan_id = pPlan_id
2409 and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
2410 and ms.ROWID = mpr.row_id
2411 and mpr.plan_id= ms.plan_id
2412 and mpr.sr_instance_id= ms.sr_instance_id
2413 and mpr.query_id =Mpq.Query_id
2414 and mpers.query_type = 9
2415 AND mpq.plan_id = pPlan_id
2416 AND mpr.plan_id=mpq.plan_id
2417 AND mpq.query_id = mpers.query_id
2418 AND mpr.query_id = mpq.query_id
2419 and Mpq.Applied_to = 5
2420 ) ;
2421 -- Not relasing if order type is forecast
2422
2423 ---*********************** Query Type = Supplier ***********************
2424
2425 ElsIF (V_TAB(x) = '5 - 1' ) THEN
2426 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 5-1 ****** ');
2427 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2428 (select ms.transaction_id
2429 from MSC_SUPPLIES ms,
2430 MSC_PQ_RESULTS mpr,
2431 --MSC_SUPPLIES ms2
2432 MSC_ITEM_SUPPLIERS ms2
2433 ,MSC_PLAN_QUERIES mpq,
2434 MSC_PERSONAL_QUERIES mpers
2435 where mpr.plan_id = pPlan_id
2436 and ms2.plan_id=mpr.plan_id
2437 and ms2.sr_instance_id = mpr.sr_instance_id
2438 and ms2.inventory_item_id = mpr.inventory_item_id
2439 and ms2.supplier_id = mpr.supplier_id
2440 and NVL(ms2.supplier_site_id, -99999) = NVL( mpr.supplier_site_id,-99999)
2441 and ms.plan_id = ms2.plan_id
2442 and ms.sr_instance_id = ms2.sr_instance_id
2443 and ms.inventory_item_id = ms2.inventory_item_id
2444 and mpr.query_id =Mpq.Query_id
2445 and mpers.query_type = 5
2446 AND mpq.plan_id = pPlan_id
2447 AND mpr.plan_id=mpq.plan_id
2448 AND mpq.query_id = mpers.query_id
2449 AND mpr.query_id = mpq.query_id
2450 and Mpq.Applied_to = 1
2451 ) ;
2452
2453 ElsIF (V_TAB(x) = '5 - 2' ) THEN
2454 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 5-2 ****** ');
2455 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2456 (select ms.transaction_id
2457 from MSC_SUPPLIES ms,
2458 MSC_PQ_RESULTS mpr,
2459 --MSC_SUPPLIES ms2
2460 MSC_ITEM_SUPPLIERS ms2
2461 ,MSC_PLAN_QUERIES mpq,
2462 MSC_PERSONAL_QUERIES mpers
2463 where mpr.plan_id = pPlan_id
2464 and ms2.plan_id=mpr.plan_id
2465 and ms2.sr_instance_id = mpr.sr_instance_id
2466 and ms2.inventory_item_id = mpr.inventory_item_id
2467 and ms2.organization_id = NVL(mpr.organization_id , ms2.organization_id)
2468 and ms2.supplier_id = mpr.supplier_id
2469 and NVL(ms2.supplier_site_id, -99999) = NVL( mpr.supplier_site_id,-99999)
2470 and ms.plan_id = ms2.plan_id
2471 and ms.sr_instance_id = ms2.sr_instance_id
2472 and ms.inventory_item_id = ms2.inventory_item_id
2473 and ms.organization_id = ms2.organization_id
2474 and mpr.query_id =Mpq.Query_id
2475 and mpers.query_type = 5
2476 AND mpq.plan_id = pPlan_id
2477 AND mpr.plan_id=mpq.plan_id
2478 AND mpq.query_id = mpers.query_id
2479 AND mpr.query_id = mpq.query_id
2480 and Mpq.Applied_to = 2
2481 );
2482
2483 ElsIF (V_TAB(x) = '5 - 3' ) THEN
2484 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 5-3 ****** ');
2485 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2486 ( select ms.transaction_id
2487 from MSC_SUPPLIES ms,
2488 MSC_PQ_RESULTS mpr,
2489 MSC_ITEM_SUBSTITUTES mis,
2490 --MSC_SUPPLIES ms2
2491 MSC_ITEM_SUPPLIERS ms2
2492 ,MSC_PLAN_QUERIES mpq,
2493 MSC_PERSONAL_QUERIES mpers
2494 where mpr.plan_id = pPlan_id
2495 and mpr.query_id =Mpq.Query_id
2496 And ms2.plan_id = mpr.plan_id
2497 And ms2.sr_instance_id = mpr.sr_instance_id
2498 and ms2.inventory_item_id = mpr.inventory_item_id
2499 and ms2.supplier_id = mpr.supplier_id
2500 and NVL(ms2.supplier_site_id, -99999) = NVL( mpr.supplier_site_id,-99999)
2501 and ( mis.lower_item_id = ms2.inventory_item_id OR mis.higher_item_id = ms2.inventory_item_id)
2502 and mis.plan_id=ms2.plan_id
2503 and mis.sr_instance_id = ms2.sr_instance_id
2504 and mis.relationship_type = 8
2505 and ms.sr_instance_id = mis.sr_instance_id
2506 and ms.inventory_item_id in ( mis.higher_item_id , mis.lower_item_id)
2507 and mpers.query_type = 5
2508 AND mpq.plan_id = pPlan_id
2509 AND mpr.plan_id=mpq.plan_id
2510 AND mpq.query_id = mpers.query_id
2511 AND mpr.query_id = mpq.query_id
2512 and Mpq.Applied_to = 3
2513 ) ;
2514
2515 ElsIF (V_TAB(x) = '5 - 4' ) THEN
2516 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* 5-4 ****** ');
2517 Update MSC_SUPPLIES set releasable=9999 where transaction_id in
2518 (select ms.transaction_id
2519 from MSC_SUPPLIES ms,
2520 MSC_PQ_RESULTS mpr,
2521 MSC_ITEM_SUBSTITUTES mis,
2522 --MSC_SUPPLIES ms2
2523 MSC_ITEM_SUPPLIERS ms2
2524 ,MSC_PLAN_QUERIES mpq,
2525 MSC_PERSONAL_QUERIES mpers
2526 where mpr.plan_id = pPlan_id
2527 and mpr.query_id =Mpq.Query_id
2528 And ms2.plan_id = mpr.plan_id
2529 And ms2.sr_instance_id = mpr.sr_instance_id
2530 and ms2.inventory_item_id = mpr.inventory_item_id
2531 and ms2.organization_id = NVL(mpr.organization_id , ms2.organization_id)
2532 and ms2.supplier_id = mpr.supplier_id
2533 and NVL(ms2.supplier_site_id, -99999) = NVL( mpr.supplier_site_id,-99999)
2534 and ( mis.lower_item_id = ms2.inventory_item_id OR mis.higher_item_id = ms2.inventory_item_id)
2535 and mis.plan_id=ms2.plan_id
2536 and mis.sr_instance_id = ms2.sr_instance_id
2537 and mis.relationship_type = 8
2538 and ms.sr_instance_id = mis.sr_instance_id
2539 and ms.inventory_item_id in ( mis.higher_item_id , mis.lower_item_id)
2540 and ms.organization_id = ms2.organization_id
2541 and mpers.query_type = 5
2542 AND mpq.plan_id = pPlan_id
2543 AND mpr.plan_id=mpq.plan_id
2544 AND mpq.query_id = mpers.query_id
2545 AND mpr.query_id = mpq.query_id
2546 and Mpq.Applied_to = 4
2547 ) ;
2548 ELSE
2549 lv_auto_release_method := lv_auto_release_method ;
2550
2551 END IF;
2552
2553 END LOOP;
2554
2555 IF ( lv_auto_release_method = 3) THEN -- IF Auto Rel method is Release
2556
2557 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* In IF ****** ');
2558 UPDATE MSC_SUPPLIES
2559 set releasable = 99
2560 where NVL(releasable,-1) = 9999
2561 and plan_id = pPlan_id ;
2562
2563 ElsIF( lv_auto_release_method = 4) THEN -- IF Auto Rel method is Block
2564 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'******* In Else ****** ');
2565 UPDATE MSC_SUPPLIES
2566 set releasable = 99
2567 where NVL(releasable,-1) <> 9999
2568 and plan_id = pPlan_id ;
2569
2570 UPDATE MSC_SUPPLIES
2571 set releasable = NULL
2572 where NVL(releasable,-1) = 9999
2573 and plan_id = pPlan_id ;
2574 ELSE
2575 lv_auto_release_method := lv_auto_release_method ;
2576 END IF;
2577
2578 END QueryBasedAutoRelease;
2579
2580
2581 END msc_release_pk;