[Home] [Help]
PACKAGE BODY: APPS.MSC_PQ_UTILS
Source
1 PACKAGE BODY MSC_PQ_UTILS AS
2 /* $Header: MSCPQUTB.pls 120.14.12010000.2 2008/07/08 13:44:19 skakani ship $ */
3
4 G_PQ_ERROR_MESSAGE VARCHAR2(2000);
5
6 g_among_values among_values_tab;
7 g_CATEGORY_SET_ID NUMBER;
8 g_query_id NUMBER;
9 g_obj_sequence_id NUMBER;
10 g_sequence_id NUMBER;
11
12 g_items_list_exists NUMBER;
13
14 FUNCTION validate_index_use(p_query_id IN NUMBER,
15 p_query_type IN NUMBER) RETURN NUMBER;
16
17 CURSOR detailQCur(p_query_id IN NUMBER) IS
18 SELECT pqt.DETAIL_QUERY_ID query_id,mpq.query_type
19 FROM MSC_PQ_TYPES pqt,
20 msc_personal_queries mpq
21 WHERE pqt.query_id = p_query_id
22 AND mpq.query_id = pqt.DETAIL_QUERY_ID;
23
24 CURSOR WlExcepCur(p_query_id IN NUMBER) IS
25 SELECT 1
26 FROM MSC_PQ_TYPES pqt
27 WHERE pqt.query_id = p_query_id
28 AND pqt.DETAIL_QUERY_ID IS NULL;
29
30 FUNCTION Get_Pref(p_plan_id NUMBER, p_preference in varchar2) RETURN NUMBER is
31 l_pref_value number;
32 l_def_pref_id number;
33 l_plan_type number;
34
35 CURSOR c_plan_type(v_plan_id NUMBER) IS
36 SELECT curr_plan_type
37 FROM msc_plans
38 WHERE plan_id = v_plan_id;
39 BEGIN
40 OPEN c_plan_type(p_plan_id);
41 FETCH c_plan_type INTO l_plan_type;
42 CLOSE c_plan_type;
43 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
44 --l_pref_value:= msc_get_name.get_preference('CATEGORY_SET_ID',l_def_pref_id, l_plan_type);
45 l_pref_value:= msc_get_name.get_preference(p_preference,l_def_pref_id, l_plan_type);
46 RETURN l_pref_value;
47
48 END Get_Pref;
49
50 PROCEDURE Parse_exceptions(p_plan_id IN NUMBER,
51 p_where_clause IN VARCHAR2) IS
52
53 TYPE excepTyp IS REF CURSOR;
54 c_exceptions excepTyp;
55
56 TYPE excepRecTyp IS RECORD ( exception_id msc_exception_details_v.exception_id%type,
57 sr_instance_Id msc_exception_details_v.sr_instance_Id%type,
58 organization_id msc_exception_details_v.organization_id%type,
59 inventory_item_id msc_exception_details_v.inventory_item_id%type,
60 supplier_id msc_exception_details_v.supplier_id%type,
61 supplier_site_id msc_exception_details_v.supplier_site_id%type,
62 transaction_id msc_exception_details_v.transaction_id%type,
63 demand_id msc_exception_details_v.demand_id%type,
64 exception_type msc_exception_details_v.exception_type%type,
65 from_date msc_exception_details_v.from_date%type,
66 to_date msc_exception_details_v.to_date%type,
67 budget_violation_date msc_exception_details_v.budget_violation_date%type,
68 department_id msc_exception_details_v.department_id%type,
69 resource_id msc_exception_details_v.resource_id%type,
70 end_pegging_id msc_exception_details_v.end_pegging_id%type,
71 operation_seq_num msc_exception_details_v.operation_seq_num%type,
72 resource_seq_num msc_exception_details_v.resource_seq_num%type);
73 rec_exceptions excepRecTyp;
74 TYPE numList IS TABLE Of NUMBER Index By BINARY_INTEGER;
75 l_list numList;
76 l_item_list numList;
77
78 /*CURSOR C_supply_demand(p_plan_id IN NUMBER,
79 p_instance_id IN NUMBER,
80 p_organization_id IN NUMBER,
81 p_inventory_item_id IN NUMBER,
82 p_from_date IN DATE,
83 p_to_date IN DATE ) IS
84 SELECT transaction_id
85 FROM msc_orders_v
86 WHERE plan_id = p_plan_id
87 AND sr_instance_id = p_instance_id
88 AND organization_id = p_organization_id
89 AND inventory_item_id = p_inventory_item_id
90 AND trunc(new_due_date) >= trunc(p_from_date)
91 AND trunc(new_due_date) < trunc(p_to_date);*/
92
93 CURSOR C_supply_demand1(p_plan_id IN NUMBER,
94 p_instance_id IN NUMBER,
95 p_organization_id IN NUMBER,
96 p_inventory_item_id IN NUMBER,
97 p_from_date IN DATE,
98 p_to_date IN DATE ) IS
99 SELECT sup.transaction_id
100 --,sup.new_schedule_date new_due_date
101 FROM msc_supplies sup,
102 msc_system_items msi ,
103 msc_item_categories mic
104 WHERE mic.sr_instance_id = sup.sr_instance_id
105 AND mic.organization_id = sup.organization_id
106 AND mic.inventory_item_id = sup.inventory_item_id
107 AND
108 sup.plan_id = msi.plan_id
109 AND sup.sr_instance_id = msi.sr_instance_id
110 AND sup.organization_id = msi.organization_id
111 AND sup.inventory_item_id = msi.inventory_item_id
112 AND
113 sup.plan_id = p_plan_id
114 AND sup.sr_instance_id = p_instance_id
115 AND sup.organization_id = p_organization_id
116 AND sup.inventory_item_id = p_inventory_item_id
117 AND sup.new_schedule_date >= p_from_date
118 AND sup.new_schedule_date < p_to_date;
119 --AND trunc(sup.new_schedule_date) >= p_from_date
120 --AND trunc(sup.new_schedule_date) < p_to_date;
121
122 CURSOR C_supply_demand2(p_plan_id IN NUMBER,
123 p_instance_id IN NUMBER,
124 p_organization_id IN NUMBER,
125 p_inventory_item_id IN NUMBER,
126 p_from_date IN DATE,
127 p_to_date IN DATE ) IS
128 SELECT dem.demand_id transaction_id
129 --,dem.using_assembly_demand_date new_due_date
130 FROM msc_demands dem,
131 msc_system_items msi,
132 msc_item_categories mic
133 WHERE mic.sr_instance_id = dem.sr_instance_id
134 AND mic.organization_id = dem.organization_id
135 AND mic.inventory_item_id = dem.inventory_item_id
136 AND
137 dem.plan_id = msi.plan_id
138 AND dem.sr_instance_id = msi.sr_instance_id
139 AND dem.organization_id = msi.organization_id
140 AND dem.inventory_item_id = msi.inventory_item_id
141 AND dem.origination_type <> 52
142 AND
143 dem.plan_id = p_plan_id
144 AND dem.sr_instance_id = p_instance_id
145 AND dem.organization_id = p_organization_id
146 AND dem.inventory_item_id = p_inventory_item_id
147 AND dem.using_assembly_demand_date >= p_from_date
148 AND dem.using_assembly_demand_date < p_to_date;
149 --AND trunc(dem.using_assembly_demand_date) >= p_from_date
150 --AND trunc(dem.using_assembly_demand_date) < p_to_date;
151
152 CURSOR C_supply_demand3(p_plan_id IN NUMBER,
153 p_instance_id IN NUMBER,
154 p_organization_id IN NUMBER,
155 p_inventory_item_id IN NUMBER,
156 p_from_date IN DATE,
157 p_to_date IN DATE ) IS
158 SELECT mso.demand_id transaction_id
159 --,mso.requirement_date new_due_date
160 FROM msc_sales_orders mso,
161 msc_system_items msi ,
162 msc_item_categories mic
163 WHERE mso.sr_instance_id = mic.sr_instance_id
164 AND mso.inventory_item_id = mic.inventory_item_id
165 AND mso.organization_id = mic.organization_id
166 AND
167 mso.sr_instance_id = msi.sr_instance_id
168 AND mso.inventory_item_id = msi.inventory_item_id
169 AND mso.organization_id = msi.organization_id
170 AND mso.reservation_type = 1
171 --AND msi.plan_id = -1
172 AND
173 msi.plan_id = p_plan_id
174 AND mso.sr_instance_id = p_instance_id
175 AND mso.organization_id = p_organization_id
176 AND mso.inventory_item_id = p_inventory_item_id
177 AND mso.requirement_date >= p_from_date
178 AND mso.requirement_date < p_to_date;
179 --AND trunc(mso.requirement_date) >= p_from_date
180 --AND trunc(mso.requirement_date) < p_to_date;
181
182 CURSOR C_supply_demand4(p_plan_id IN NUMBER,
183 p_instance_id IN NUMBER,
184 p_organization_id IN NUMBER,
185 p_inventory_item_id IN NUMBER,
186 p_from_date IN DATE,
187 p_to_date IN DATE ) IS
188 SELECT jro.transaction_id
189 --,jro.reco_date_required new_due_date
190 FROM msc_job_requirement_ops jro,
191 msc_system_items msi ,
192 msc_item_categories mic
193 WHERE mic.sr_instance_id = jro.sr_instance_id
194 AND mic.organization_id = jro.organization_id
195 AND mic.inventory_item_id = jro.component_item_id
196 --AND jro.plan_id = -1
197 AND jro.plan_id = msi.plan_id
198 AND jro.sr_instance_id = msi.sr_instance_id
199 AND jro.organization_id = msi.organization_id
200 AND jro.component_item_id = msi.inventory_item_id
201 AND
202 jro.plan_id = p_plan_id
203 AND jro.sr_instance_id = p_instance_id
204 AND jro.organization_id = p_organization_id
205 AND jro.component_item_id = p_inventory_item_id
206 AND jro.reco_date_required >= p_from_date
207 AND jro.reco_date_required < p_to_date;
208 --AND trunc(jro.reco_date_required) >= p_from_date
209 --AND trunc(jro.reco_date_required) < p_to_date;
210
211 CURSOR C_supply_demand5(p_plan_id IN NUMBER,
212 p_instance_id IN NUMBER,
213 p_organization_id IN NUMBER,
214 p_inventory_item_id IN NUMBER,
215 p_from_date IN DATE,
216 p_to_date IN DATE ) IS
217 SELECT sup.transaction_id
218 --,nvl(sup.new_ship_date,sup.new_schedule_date) new_due_date
219 FROM msc_supplies sup,
220 msc_system_items msi ,
221 msc_item_categories mic,
222 msc_plans mp
223 WHERE mic.sr_instance_id = sup.sr_instance_id
224 AND mic.organization_id = sup.organization_id
225 AND mic.inventory_item_id = sup.inventory_item_id
226 AND
227 sup.plan_id = msi.plan_id
228 AND sup.sr_instance_id = msi.sr_instance_id
229 AND sup.organization_id = msi.organization_id
230 AND sup.inventory_item_id = msi.inventory_item_id
231 AND mp.plan_id = sup.plan_id
232 AND mp.plan_type = 5
233 AND sup.order_type = 51
234 AND
235 sup.plan_id = p_plan_id
236 AND sup.sr_instance_id = p_instance_id
237 AND sup.organization_id = p_organization_id
238 AND sup.inventory_item_id = p_inventory_item_id
239 AND nvl(sup.new_ship_date,sup.new_schedule_date) >= p_from_date
240 AND nvl(sup.new_ship_date,sup.new_schedule_date) < p_to_date;
241 --AND trunc(nvl(sup.new_ship_date,sup.new_schedule_date)) >= p_from_date
242 --AND trunc(nvl(sup.new_ship_date,sup.new_schedule_date)) < p_to_date;
243
244 CURSOR C_supply_demand6(p_plan_id IN NUMBER,
245 p_instance_id IN NUMBER,
246 p_organization_id IN NUMBER,
247 p_inventory_item_id IN NUMBER,
248 p_from_date IN DATE,
249 p_to_date IN DATE ) IS
250 SELECT dem.demand_id transaction_id
251 --,nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date) new_due_date
252 FROM msc_demands dem,
253 msc_plans mp,
254 msc_system_items msi ,
255 msc_item_categories mic
256 WHERE mic.sr_instance_id = dem.sr_instance_id
257 AND mic.organization_id = dem.organization_id
258 AND mic.inventory_item_id = dem.inventory_item_id
259 AND
260 dem.plan_id = msi.plan_id
261 AND dem.sr_instance_id = msi.sr_instance_id
262 AND dem.organization_id = msi.organization_id
263 AND dem.inventory_item_id = msi.inventory_item_id
264 AND mp.plan_id = dem.plan_id
265 AND mp.plan_type = 5
266 AND (( dem.origination_type = 1
267 AND dem.source_organization_id <> dem.organization_id )
268 OR
269 ( dem.origination_type = 30
270 AND dem.demand_source_type = 8 ))
271 AND
272 dem.plan_id = p_plan_id
273 AND dem.sr_instance_id = p_instance_id
274 AND dem.organization_id = p_organization_id
275 AND dem.inventory_item_id = p_inventory_item_id
276 AND nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date) >= p_from_date
277 AND nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date) < p_to_date;
278 --AND trunc(nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date)) >= p_from_date
279 --AND trunc(nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date)) < p_to_date;
280
281 CURSOR C_supply_demand7(p_plan_id IN NUMBER,
282 p_instance_id IN NUMBER,
283 p_organization_id IN NUMBER,
284 p_inventory_item_id IN NUMBER,
285 p_from_date IN DATE,
286 p_to_date IN DATE ) IS
287 SELECT dem.demand_id transaction_id
288 --,nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date) new_due_date
289 FROM msc_demands dem,
290 msc_plans mp,
291 msc_system_items msi ,
292 msc_item_categories mic
293 WHERE mic.sr_instance_id = dem.sr_instance_id
294 AND mic.organization_id = dem.organization_id
295 AND mic.inventory_item_id = dem.inventory_item_id
296 AND
297 dem.plan_id = msi.plan_id
298 AND dem.sr_instance_id = msi.sr_instance_id
299 AND dem.organization_id = msi.organization_id
300 AND dem.inventory_item_id = msi.inventory_item_id
301 AND mp.plan_id = dem.plan_id
302 AND mp.plan_type = 5
303 AND dem.origination_type = 30
304 AND dem.demand_source_type = 8
305 AND
306 dem.plan_id = p_plan_id
307 AND dem.sr_instance_id = p_instance_id
308 AND dem.organization_id = p_organization_id
309 AND dem.inventory_item_id = p_inventory_item_id
310 AND nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date) >= p_from_date
311 AND nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date) < p_to_date;
312 --AND trunc(nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date)) >= p_from_date
313 --AND trunc(nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date)) < p_to_date;
314
315 CURSOR c_supp_cap_overload_exception(p_plan_id NUMBER ,
316 p_sr_instance_id NUMBER,
317 p_supplier_id NUMBER,
318 p_supplier_site_id NUMBER,
319 p_inventory_item_id NUMBER,
320 p_consumption_date DATE) is
321 SELECT distinct supply_id
322 FROM msc_supplier_requirements
323 WHERE plan_id = p_plan_id
324 AND sr_instance_id = p_sr_instance_id
325 AND supplier_id = p_supplier_id
326 AND supplier_site_id = p_supplier_site_id
327 AND inventory_item_id = p_inventory_item_id
328 AND trunc(consumption_date) = trunc(p_consumption_date);
329 l_sql_stmt VARCHAR2(32000);
330
331 CURSOR c_sd_49(p_plan_id NUMBER,
332 p_sr_instance_id NUMBER,
333 p_demand_id NUMBER) IS
334 SELECT demand_id
335 FROM msc_demands
336 WHERE plan_id = p_plan_id
337 AND sr_instance_id = p_sr_instance_id
338 AND group_id IN (SELECT group_id
339 FROM msc_demands
340 WHERE plan_id = p_plan_id
341 AND sr_instance_id = p_sr_instance_id
342 AND demand_id = p_demand_id);
343
344 CURSOR c_demand_84 (p_plan_id NUMBER,
345 p_excp_id NUMBER) IS
346 SELECT md.demand_id
347 FROM msc_exception_details med,
348 msc_demands md
349 WHERE med.plan_id = md.plan_id
350 AND med.exception_type = 84
351 AND med.exception_detail_id = p_excp_id
352 AND med.plan_id = p_plan_id
353 AND ( med.number1= md.demand_id
354 OR med.number1 = md.original_demand_id);
355
356 CURSOR RES_TRANS_C(p_plan_id NUMBER,
357 p_inst_id NUMBER,
358 p_org_id NUMBER,
359 p_dept_id NUMBER,
360 p_res_id NUMBER) IS
361 SELECT supply_id
362 FROM msc_resource_requirements
363 WHERE plan_id = p_plan_id
364 AND sr_instance_id = p_inst_id
365 AND organization_id = p_org_id
366 AND department_id = p_dept_id
367 AND resource_id = p_res_id;
368
369 CURSOR RES_TRANS_C1(p_plan_id NUMBER,
370 p_inst_id NUMBER,
371 p_org_id NUMBER,
372 p_dept_id NUMBER,
373 p_res_id NUMBER,
374 p_supply_id NUMBER,
375 p_op_seq NUMBER,
376 p_res_seq NUMBER) IS
377 SELECT supply_id,transaction_id
378 FROM msc_resource_requirements
379 WHERE plan_id = p_plan_id
380 AND sr_instance_id = p_inst_id
381 AND organization_id = p_org_id
382 AND department_id = p_dept_id
383 AND resource_id = p_res_id
384 AND supply_id = p_supply_id
385 AND nvl(operation_seq_num,-1) = nvl(p_op_seq, nvl(operation_seq_num,-1))
386 AND nvl(resource_seq_num,-1) = nvl(p_res_seq, nvl(resource_seq_num,-1))
387 AND parent_id = 2;
388
389 CURSOR RES_TRANS_C2(p_plan_id NUMBER,
390 p_inst_id NUMBER,
391 p_org_id NUMBER,
392 p_dept_id NUMBER,
393 p_res_id NUMBER,
394 p_from_date DATE,
395 p_to_date DATE) IS
396 SELECT supply_id
397 FROM msc_resource_requirements
398 WHERE plan_id = p_plan_id
399 AND sr_instance_id = p_inst_id
400 AND organization_id = p_org_id
401 AND department_id = p_dept_id
402 AND resource_id = p_res_id
403 AND ( ( trunc(start_date) >= p_from_date
404 AND NVL(trunc(end_date),p_to_date) <= p_to_date)
405 OR ( p_from_date BETWEEN trunc(start_date)
406 AND NVL(trunc(end_date),p_to_date))
407 OR ( p_to_date BETWEEN trunc(start_date) AND NVL(trunc(end_date),p_to_date))
408 OR ( trunc(start_date) <= p_from_date
409 AND NVL(trunc(end_date),p_to_date) >= p_to_date) );
410
411 CURSOR RES_TRANS_C3(p_plan_id NUMBER,
412 p_inst_id NUMBER,
413 p_org_id NUMBER,
414 p_dept_id NUMBER,
415 p_res_id NUMBER,
416 p_from_date DATE,
417 p_to_date DATE) IS
418 SELECT supply_id
419 FROM msc_resource_requirements r,
420 msc_supplies s,
421 msc_system_items i
422 WHERE r.plan_id = p_plan_id
423 AND r.sr_instance_id = p_inst_id
424 AND r.organization_id = p_org_id
425 AND r.department_id = p_dept_id
426 AND r.resource_id = p_res_id
427 AND s.plan_id = r.plan_id
428 AND s.transaction_id = r.supply_id
429 AND s.plan_id = i.plan_id
430 AND s.sr_instance_id = i.sr_instance_id
431 AND s.organization_id = i.organization_id
432 AND s.inventory_item_id = i.inventory_item_id
433 AND trunc(s.need_by_date - (i.fixed_lead_time + (i.variable_lead_time*s.new_order_quantity)))
434 BETWEEN p_from_date AND p_to_date;
435
436 CURSOR PEG_TRANS_C(p_plan_id NUMBER,
437 p_end_peg_id NUMBER) IS
438 SELECT transaction_id
439 FROM msc_full_pegging
440 WHERE plan_id = p_plan_id
441 AND end_pegging_id = p_end_peg_id;
442
443 CURSOR get_bucket_dates(p_plan_id NUMBER,
444 p_date DATE) IS
445 SELECT trunc(bkt_start_date), trunc(bkt_end_date)
446 FROM msc_plan_buckets
447 WHERE plan_id = p_plan_id
448 AND p_date between bkt_start_date and bkt_end_date;
449
450 l_transaction_id NUMBER;
451
452 BEGIN
453 l_sql_stmt := ' SELECT exception_id , sr_instance_Id, organization_id, '||
454 ' inventory_item_id, supplier_id , supplier_site_id, '||
455 ' transaction_id , demand_id , exception_type, '||
456 ' from_date , to_date, budget_violation_date, '||
457 ' department_id , resource_id, end_pegging_id, '||
458 ' operation_seq_num, resource_seq_num '||
459 ' FROM msc_exception_details_v med '||
460 ' WHERE med.plan_id = :plan_id '||
461 ' AND nvl(med.category_set_id,2) = :category_set_id ';
462
463 l_sql_stmt := l_sql_stmt ||' AND '|| p_where_clause;
464 --KSA_DEBUG(SYSDATE,'l_sql_stmt ...'||l_sql_stmt,'Parse_exceptions');
465 IF g_category_set_id IS NULL THEN
466 g_category_set_id := Get_Pref(p_plan_id, 'CATEGORY_SET_ID');
467 END IF;
468 --KSA_DEBUG(SYSDATE,'p_plan_id ...'||p_plan_id||'and g_category_set_id...'||g_category_set_id,'Parse_exceptions');
469 OPEN c_exceptions for l_sql_stmt using p_plan_id, g_category_set_id;
470 LOOP
471 FETCH c_exceptions INTO rec_exceptions;
472 EXIT WHEN c_exceptions%NOTFOUND;
473 IF rec_exceptions.exception_type =20 THEN
474 l_item_list(l_item_list.count()+1) := rec_exceptions.inventory_item_id;
475 END IF;
476 IF rec_exceptions.exception_type =28 THEN
477 FOR rec_supp_cap_overload_excp IN c_supp_cap_overload_exception
478 (p_plan_id ,
479 rec_exceptions.sr_instance_id,
480 rec_exceptions.supplier_id,
481 rec_exceptions.supplier_site_id,
482 rec_exceptions.inventory_item_id,
483 rec_exceptions.from_date) LOOP
484 --populate_temp_table(rec_supp_cap_overload_excp.supply_id);
485 l_list(l_list.count()+1) := rec_supp_cap_overload_excp.supply_id;
486 END LOOP;
487 /*ELSIF rec_exceptions.exception_type in (52,49,84,85,86,87,88,89,90,92,93) THEN
488 NULL;
489 ELSIF (rec_exceptions.exception_type <> 48) AND
490 (rec_exceptions.transaction_id IS NOT NULL
491 OR (rec_exceptions.department_id IS NOT NULL
492 AND rec_exceptions.resource_id IS NOT NULL)) THEN
493 mrp_exception_details.g_resource_req_rows_selected := mrp_exception_details.g_resource_req_rows_selected + 1;
494 */
495 END IF;
496
497 IF rec_exceptions.exception_type = 49 THEN
498 OPEN c_sd_49(p_plan_id,
499 rec_exceptions.sr_instance_id,
500 rec_exceptions.demand_id);
501 LOOP
502 FETCH c_sd_49 into l_transaction_id;
503 EXIT WHEN C_sd_49%NOTFOUND;
504 --supply/demand
505 --populate_temp_table(l_transaction_id);
506 l_list(l_list.count()+1) := l_transaction_id;
507 END LOOP;
508 CLOSE c_sd_49;
509 ELSIF rec_exceptions.exception_type = 84 then
510 DECLARE
511 l_temp number;
512 BEGIN
513 OPEN c_demand_84(p_plan_id,
514 rec_exceptions.exception_id );
515 FETCH c_demand_84 into l_temp;
516 CLOSE c_demand_84;
517
518 --populate_temp_table(l_temp);
519 l_list(l_list.count()+1) := l_temp;
520 END;
521 ELSIF rec_exceptions.exception_type = 85 then
522 DECLARE
523 l_from_date DATE;
524 l_to_date DATE;
525 BEGIN
526 OPEN get_bucket_dates(p_plan_id,
527 rec_exceptions.budget_violation_date);
528 FETCH get_bucket_dates INTO l_from_date, l_to_date;
529 CLOSE get_bucket_dates;
530
531 OPEN C_supply_demand1(p_plan_id,
532 rec_exceptions.sr_instance_id,
533 rec_exceptions.organization_id,
534 rec_exceptions.inventory_item_id,
535 l_from_date,
536 l_to_date );
537
538 LOOP
539 FETCH C_supply_demand1 into l_transaction_id;
540 EXIT WHEN C_supply_demand1%NOTFOUND;
541 --populate_temp_table(l_transaction_id);
542 l_list(l_list.count()+1) := l_transaction_id;
543 END LOOP;
544 CLOSE C_supply_demand1;
545
546 OPEN C_supply_demand2(p_plan_id,
547 rec_exceptions.sr_instance_id,
548 rec_exceptions.organization_id,
549 rec_exceptions.inventory_item_id,
550 l_from_date,
551 l_to_date );
552
553 LOOP
554 FETCH C_supply_demand2 into l_transaction_id;
555 EXIT WHEN C_supply_demand2%NOTFOUND;
556 --populate_temp_table(l_transaction_id);
557 l_list(l_list.count()+1) := l_transaction_id;
558 END LOOP;
559 CLOSE C_supply_demand2;
560
561 IF p_plan_id = -1 THEN
562 OPEN C_supply_demand3(p_plan_id,
563 rec_exceptions.sr_instance_id,
564 rec_exceptions.organization_id,
565 rec_exceptions.inventory_item_id,
566 l_from_date,
567 l_to_date );
568
569 LOOP
570 FETCH C_supply_demand3 into l_transaction_id;
571 EXIT WHEN C_supply_demand3%NOTFOUND;
572 --populate_temp_table(l_transaction_id);
573 l_list(l_list.count()+1) := l_transaction_id;
574 END LOOP;
575 CLOSE C_supply_demand3;
576
577 OPEN C_supply_demand4(p_plan_id,
578 rec_exceptions.sr_instance_id,
579 rec_exceptions.organization_id,
580 rec_exceptions.inventory_item_id,
581 l_from_date,
582 l_to_date );
583
584 LOOP
585 FETCH C_supply_demand4 into l_transaction_id;
586 EXIT WHEN C_supply_demand4%NOTFOUND;
587 --populate_temp_table(l_transaction_id);
588 l_list(l_list.count()+1) := l_transaction_id;
589 END LOOP;
590 CLOSE C_supply_demand4;
591 END IF;
592
593 OPEN C_supply_demand5(p_plan_id,
594 rec_exceptions.sr_instance_id,
595 rec_exceptions.organization_id,
596 rec_exceptions.inventory_item_id,
597 l_from_date,
598 l_to_date );
599
600 LOOP
601 FETCH C_supply_demand5 into l_transaction_id;
602 EXIT WHEN C_supply_demand5%NOTFOUND;
603 --populate_temp_table(l_transaction_id);
604 l_list(l_list.count()+1) := l_transaction_id;
605 END LOOP;
606 CLOSE C_supply_demand5;
607
608 OPEN C_supply_demand6(p_plan_id,
609 rec_exceptions.sr_instance_id,
610 rec_exceptions.organization_id,
611 rec_exceptions.inventory_item_id,
612 l_from_date,
613 l_to_date );
614
615 LOOP
616 FETCH C_supply_demand6 into l_transaction_id;
617 EXIT WHEN C_supply_demand6%NOTFOUND;
618 --populate_temp_table(l_transaction_id);
619 l_list(l_list.count()+1) := l_transaction_id;
620 END LOOP;
621 CLOSE C_supply_demand6;
622
623 OPEN C_supply_demand7(p_plan_id,
624 rec_exceptions.sr_instance_id,
625 rec_exceptions.organization_id,
626 rec_exceptions.inventory_item_id,
627 l_from_date,
628 l_to_date );
629
630 LOOP
631 FETCH C_supply_demand7 into l_transaction_id;
632 EXIT WHEN C_supply_demand7%NOTFOUND;
633 --populate_temp_table(l_transaction_id);
634 l_list(l_list.count()+1) := l_transaction_id;
635 END LOOP;
636 CLOSE C_supply_demand7;
637
638 END;
639 ELSIF rec_exceptions.demand_id IS NOT NULL
640 AND rec_exceptions.end_pegging_id IS NOT NULL THEN
641 OPEN PEG_TRANS_C(p_plan_id,
642 rec_exceptions.end_pegging_id);
643 LOOP
644 FETCH PEG_TRANS_C INTO l_transaction_id;
645 EXIT WHEN PEG_TRANS_C%NOTFOUND;
646 --populate_temp_table(l_transaction_id);
647 l_list(l_list.count()+1) := l_transaction_id;
648 END LOOP;
649 CLOSE PEG_TRANS_C;
650 --populate_temp_table(rec_exceptions.demand_id);
651 l_list(l_list.count()+1) := rec_exceptions.demand_id;
652 --KSA_DEBUG(SYSDATE,'demand_id and end_pegging_id are not null...2','Parse_exceptions');
653 ELSIF rec_exceptions.resource_id IS NOT NULL THEN
654 IF rec_exceptions.exception_type = 36 THEN
655 OPEN RES_TRANS_C3(p_plan_id,
656 rec_exceptions.sr_instance_id,
657 rec_exceptions.organization_id,
658 rec_exceptions.department_id,
659 rec_exceptions.resource_id,
660 rec_exceptions.from_date,
661 rec_exceptions.to_date);
662 LOOP
663 FETCH RES_TRANS_C3 INTO l_transaction_id;
664 EXIT WHEN RES_TRANS_C3%NOTFOUND;
665 --populate_temp_table(l_transaction_id);
666 l_list(l_list.count()+1) := l_transaction_id;
667 --KSA_DEBUG(SYSDATE,'resource_idis not nulland ex typ 36...','Parse_exceptions');
668 END LOOP;
669 CLOSE RES_TRANS_C3;
670 ELSIF rec_exceptions.transaction_id IS NOT NULL THEN
671 DECLARE
672 l_res_transaction_id NUMBER;
673 BEGIN
674 OPEN RES_TRANS_C1(p_plan_id,
675 rec_exceptions.sr_instance_id,
676 rec_exceptions.organization_id,
677 rec_exceptions.department_id,
678 rec_exceptions.resource_id,
679 rec_exceptions.transaction_id,
680 rec_exceptions.operation_seq_num,
681 rec_exceptions.resource_seq_num);
682 LOOP
683 FETCH RES_TRANS_C1 INTO l_transaction_id, l_res_transaction_id;
684 EXIT WHEN RES_TRANS_C1%NOTFOUND;
685
686 --populate_temp_table(l_transaction_id);
687 l_list(l_list.count()+1) := l_transaction_id;
688 --populate_temp_table(l_res_transaction_id);
689 l_list(l_list.count()+1) := l_res_transaction_id;
690 --KSA_DEBUG(SYSDATE,'resource_idis not null and tr_id is not null...','Parse_exceptions');
691 END LOOP;
692 CLOSE RES_TRANS_C1;
693 END;
694 ELSIF rec_exceptions.from_date IS NOT NULL
695 AND rec_exceptions.to_date IS NOT NULL THEN
696
697 OPEN RES_TRANS_C2(p_plan_id,
698 rec_exceptions.sr_instance_id,
699 rec_exceptions.organization_id,
700 rec_exceptions.department_id,
701 rec_exceptions.resource_id,
702 rec_exceptions.from_date,
703 rec_exceptions.to_date);
704 LOOP
705 FETCH RES_TRANS_C2 INTO l_transaction_id;
706 EXIT WHEN RES_TRANS_C2%NOTFOUND;
707 --populate_temp_table(l_transaction_id);
708 l_list(l_list.count()+1) := l_transaction_id;
709 --KSA_DEBUG(SYSDATE,'resource_idis not null and from and to dates are not null...','Parse_exceptions');
710 END LOOP;
711 CLOSE RES_TRANS_C2;
712 ELSE
713 OPEN RES_TRANS_C(p_plan_id,
714 rec_exceptions.sr_instance_id,
715 rec_exceptions.organization_id,
716 rec_exceptions.department_id,
717 rec_exceptions.resource_id);
718
719 LOOP
720 FETCH RES_TRANS_C INTO l_transaction_id;
721 EXIT WHEN RES_TRANS_C%NOTFOUND;
722 --populate_temp_table(l_transaction_id);
723 l_list(l_list.count()+1) := l_transaction_id;
724 END LOOP;
725 CLOSE RES_TRANS_C;
726 END IF;
727 END IF;
728
729 IF rec_exceptions.transaction_id IS NOT NULL THEN
730 --populate_temp_table(rec_exceptions.transaction_id);
731 l_list(l_list.count()+1) := rec_exceptions.transaction_id;
732 ELSIF rec_exceptions.demand_id IS NOT NULL THEN
733 --populate_temp_table(rec_exceptions.demand_id);
734 l_list(l_list.count()+1) := rec_exceptions.demand_id;
735 END IF;
736 END LOOP;
737 DECLARE
738 v_insert_stmt VARCHAR2(2000);
739 BEGIN
740
741 FORALL i IN 1..l_list.count()
742 INSERT INTO MSC_FORM_QUERY
743 (QUERY_ID, NUMBER1, NUMBER2, NUMBER3,
744 LAST_UPDATE_DATE, LAST_UPDATED_BY ,
745 CREATION_DATE, CREATED_BY,
746 LAST_UPDATE_LOGIN )
747 VALUES (g_query_id, g_obj_sequence_id, g_sequence_id, l_list(i),
748 SYSDATE, fnd_global.user_id,
749 SYSDATE, fnd_global.user_id,
750 fnd_global.login_id);
751
752 IF l_item_list.count() > 0 THEN
753 g_items_list_exists := l_item_list.count();
754 FORALL i IN 1..l_item_list.count()
755 INSERT INTO MSC_FORM_QUERY
756 (QUERY_ID, NUMBER1, NUMBER2, NUMBER4,
757 LAST_UPDATE_DATE, LAST_UPDATED_BY ,
758 CREATION_DATE, CREATED_BY,
759 LAST_UPDATE_LOGIN )
760 VALUES (g_query_id, g_obj_sequence_id, g_sequence_id, l_item_list(i),
761 SYSDATE, fnd_global.user_id,
762 SYSDATE, fnd_global.user_id,
763 fnd_global.login_id);
764 ELSE
765 g_items_list_exists := 0;
766 END IF;
767 END;
768 EXCEPTION
769 WHEN OTHERS THEN
770 --KSA_DEBUG(SYSDATE,'Error: '||sqlerrm(sqlcode),'Parse_exceptions');
771 RAISE;
772 END Parse_exceptions;
773
774 -- for criticality matrix , this function returns where clause
775 -- for a specific category_id ( msc_pq_types.object_type)
776 FUNCTION build_where_clause_new(p_query_id IN NUMBER DEFAULT NULL,
777 p_source_type IN NUMBER DEFAULT NULL,
778 P_object_type IN NUMBER DEFAULT NULL)
779 RETURN VARCHAR2 IS
780 CURSOR c_excp_criteria (p_object_type NUMBER) IS
781 SELECT field_name,
782 field_type,
783 condition,
784 low_value,
785 high_value,
786 hidden_from_field,
787 data_set,
788 source_type,
789 object_type,
790 lov_type,
791 sequence,
792 object_sequence_id
793 FROM msc_selection_criteria_v
794 WHERE folder_id = p_query_id
795 AND active_flag = 1
796 AND condition IS NOT NULL
797 AND source_type = p_source_type
798 AND object_type = p_object_type;
799
800 CURSOR c_and_or (p_object_type NUMBER) IS
801 SELECT COUNT(*)
802 FROM msc_pq_types
803 WHERE query_id = p_query_id
804 AND source_type = p_source_type
805 AND object_type = p_object_type
806 AND NVL(and_or_flag,1) = 1;
807
808
809 l_row_count NUMBER ;
810 l_criticality_where VARCHAR2(100);
811
812 l_where_clause_segment VARCHAR2(2000);
813 l_where2_clause_segment VARCHAR2(2000);
814 where_clause_segment VARCHAR2(32000);
815
816 l_field_name VARCHAR2(50);
817 l_data_set VARCHAR2(50);
818 l_data_type VARCHAR2(50);
819 l_temp_match_str VARCHAR2(10);
820 l_and_or number;
821 l_match_str VARCHAR2(10);
822
823 begin
824 l_row_count := 0;
825 OPEN c_and_or(p_object_type);
826 FETCH c_and_or INTO l_and_or;
827 CLOSE c_and_or;
828
829 IF l_and_or = 0 THEN
830 l_match_str := ' OR ';
831 ELSE
832 l_match_str := ' AND ';
833 END IF;
834
835 FOR c_criteria_row IN c_excp_criteria(p_object_type) LOOP
836 l_row_count := l_row_count + 1;
837 IF (l_row_count = 1) THEN
838 l_temp_match_str := '';
839 ELSE
840 l_temp_match_str := l_match_str;
841 END IF;
842
843 l_field_name := c_criteria_row.field_name ;
844 l_data_set := c_criteria_row.data_set;
845 l_data_type := c_criteria_row.field_type;
846 l_where_clause_segment := get_where_clause
847 (c_criteria_row.sequence,
848 c_criteria_row.object_sequence_id,
849 l_field_name,
850 c_criteria_row.condition,
851 c_criteria_row.low_value,
852 c_criteria_row.high_value,
853 c_criteria_row.hidden_from_field,
854 l_data_set,
855 l_data_type,
856 c_criteria_row.lov_type,
857 l_temp_match_str,
858 NULL);
859 IF l_where2_clause_segment IS NULL THEN
860 l_where2_clause_segment := '( '||l_where_clause_segment||' ) ';
861 ELSE
862 l_where2_clause_segment := l_where2_clause_segment ||
863 l_temp_match_str||' ( ' ||
864 l_where_clause_segment ||' ) ';
865
866 END IF;
867 END LOOP;
868 IF where_clause_segment IS NULL THEN
869 where_clause_segment :=l_where2_clause_segment ;
870 ELSE
871 where_clause_segment := where_clause_segment ||
872 ' OR ( ' ||
873 l_where2_clause_segment ||' ) ';
874 END if;
875 l_where_clause_segment := NULL;
876 l_where2_clause_segment := NULL;
877 RETURN where_clause_segment;
878 END build_where_clause_new;
879
880 FUNCTION build_where_clause(p_query_id IN NUMBER DEFAULT NULL,
881 P_source_type IN NUMBER DEFAULT NULL)
882 RETURN VARCHAR2 IS
883
884 CURSOR c_criteria IS
885 SELECT field_name,
886 field_type,
887 condition,
888 DECODE(field_name , 'PLANNING_MAKE_BUY_CODE',
889 hidden_from_field, low_value ) low_value,
890 high_value,
891 hidden_from_field,
892 data_set,
893 source_type,
894 object_type,
895 lov_type,
896 sequence,
897 object_sequence_id
898 FROM msc_selection_criteria_v
899 WHERE folder_id = p_query_id
900 AND active_flag = 1
901 AND condition IS NOT NULL
902 ORDER BY source_type, object_type, field_name;
903
904 CURSOR c_excp_criteria (p_object_type NUMBER) IS
905 SELECT field_name,
906 field_type,
907 condition,
908 low_value,
909 high_value,
910 hidden_from_field,
911 data_set,
912 source_type,
913 object_type,
914 lov_type,
915 sequence,
916 object_sequence_id
917 FROM msc_selection_criteria_v
918 WHERE folder_id = p_query_id
919 AND active_flag = 1
920 AND condition IS NOT NULL
921 AND source_type = p_source_type
922 AND object_type = p_object_type;
923
924 CURSOR c_excp_type IS
925 SELECT DISTINCT object_type
926 FROM msc_selection_criteria_v
927 WHERE folder_id = p_query_id
928 AND active_flag = 1
929 AND source_type = p_source_type;
930
931 CURSOR c_and_or IS
932 SELECT count(*)
933 FROM msc_personal_queries
934 WHERE query_id = p_query_id
935 AND NVL(and_or_flag,1) = 1;
936
937 CURSOR c_excp_and_or (p_object_type NUMBER) IS
938 SELECT COUNT(*)
939 FROM msc_pq_types
940 WHERE query_id = p_query_id
941 AND source_type = p_source_type
942 AND object_type = p_object_type
943 AND NVL(and_or_flag,1) = 1;
944
945 l_and_or NUMBER;
946 l_match_str VARCHAR2(10);
947 l_temp_match_str VARCHAR2(10);
948 l_row_count NUMBER ;
949 l_excp_where VARCHAR2(100);
950
951 l_where_clause_segment VARCHAR2(2000);
952 l_where2_clause_segment VARCHAR2(2000);
953 where_clause_segment VARCHAR2(32000);
954
955 l_field_name VARCHAR2(50);
956 l_data_set VARCHAR2(50);
957 l_data_type VARCHAR2(50);
958
959 begin
960 l_row_count := 0;
961 IF p_source_type = 0 THEN
962 OPEN c_and_or;
963 FETCH c_and_or INTO l_and_or;
964 CLOSE c_and_or;
965 IF l_and_or = 0 THEN
966 l_match_str := ' OR ';
967 ELSE
968 l_match_str := ' AND ';
969 END if;
970
971 FOR c_criteria_row IN c_criteria LOOP
972 l_row_count := l_row_count + 1;
973 IF (l_row_count = 1) THEN
974 l_temp_match_str := '';
975 ELSE
976 l_temp_match_str := l_match_str;
977 END IF;
978 l_excp_where := '';
979 l_field_name := c_criteria_row.field_name ;
980 l_data_set := c_criteria_row.data_set;
981 l_data_type := c_criteria_row.field_type;
982 l_where_clause_segment := get_where_clause
983 (c_criteria_row.sequence,
984 c_criteria_row.object_sequence_id,
985 l_field_name,
986 c_criteria_row.condition,
987 c_criteria_row.low_value,
988 c_criteria_row.high_value,
989 c_criteria_row.hidden_from_field,
990 l_data_set,
991 l_data_type,
992 c_criteria_row.lov_type,
993 l_temp_match_str,
994 l_excp_where);
995 IF where_clause_segment IS NULL THEN
996 where_clause_segment := ' ( '||l_where_clause_segment||' ) ';
997 ELSE
998 where_clause_segment := where_clause_segment||
999 l_match_str ||
1000 ' ( '||l_where_clause_segment||' ) ';
1001 END IF;
1002 END LOOP;
1003 ELSE
1004 FOR c_excp_type_row IN c_excp_type LOOP
1005 OPEN c_excp_and_or(c_excp_type_row.object_type);
1006 FETCH c_excp_and_or INTO l_and_or;
1007 CLOSE c_excp_and_or;
1008
1009 IF l_and_or = 0 THEN
1010 l_match_str := ' OR ';
1011 ELSE
1012 l_match_str := ' AND ';
1013 END IF;
1014
1015 l_excp_where := ' ( exception_type = ' ||
1016 c_excp_type_row.object_type ||
1017 ' AND source_type = ' ||
1018 p_source_type ||
1019 ' ) AND ';
1020 FOR c_criteria_row IN c_excp_criteria(c_excp_type_row.object_type) LOOP
1021 l_row_count := l_row_count + 1;
1022 IF (l_row_count = 1) THEN
1023 l_temp_match_str := '';
1024 ELSE
1025 l_temp_match_str := l_match_str;
1026 END IF;
1027
1028 l_field_name := c_criteria_row.field_name ;
1029 l_data_set := c_criteria_row.data_set;
1030 l_data_type := c_criteria_row.field_type;
1031 l_where_clause_segment := get_where_clause
1032 (c_criteria_row.sequence,
1033 c_criteria_row.object_sequence_id,
1034 l_field_name,
1035 c_criteria_row.condition,
1036 c_criteria_row.low_value,
1037 c_criteria_row.high_value,
1038 c_criteria_row.hidden_from_field,
1039 l_data_set,
1040 l_data_type,
1041 c_criteria_row.lov_type,
1042 l_temp_match_str,
1043 NULL);
1044
1045 IF l_where2_clause_segment IS NULL THEN
1046 l_where2_clause_segment := '( '||l_where_clause_segment||' ) ';
1047 ELSE
1048 l_where2_clause_segment := l_where2_clause_segment ||
1049 l_temp_match_str||' ( ' ||
1050 l_where_clause_segment ||' ) ';
1051
1052 END IF;
1053 END LOOP;
1054
1055 IF where_clause_segment IS NULL THEN
1056 where_clause_segment := ' ( '||l_excp_where||' ( ' ||
1057 l_where2_clause_segment ||' )) ';
1058 ELSE
1059 where_clause_segment := where_clause_segment ||
1060 ' OR ( ' ||
1061 l_excp_where ||
1062 l_where2_clause_segment ||' ) ';
1063 END IF;
1064
1065 l_where_clause_segment := NULL;
1066 l_where2_clause_segment := NULL;
1067 END LOOP;
1068 END IF;
1069 RETURN where_clause_segment;
1070 END build_where_clause;
1071
1072 FUNCTION build_order_where_clause(p_query_id IN NUMBER,
1073 p_plan_id IN NUMBER)
1074 RETURN VARCHAR2 IS
1075
1076 CURSOR c_Ord_criteria(p_query_id IN NUMBER,
1077 p_object_type IN NUMBER,
1078 p_source_type IN NUMBER,
1079 p_sequence_id IN NUMBER) IS
1080 SELECT field_name,
1081 field_type,
1082 condition,
1083 low_value,
1084 high_value,
1085 hidden_from_field,
1086 data_set,
1087 source_type,
1088 object_type,
1089 lov_type,
1090 sequence,
1091 object_sequence_id
1092 FROM msc_selection_criteria_v
1093 WHERE folder_id = p_query_id
1094 AND active_flag = 1
1095 AND condition IS NOT NULL
1096 AND source_type = p_source_type
1097 AND object_type = p_object_type
1098 AND object_sequence_id = p_sequence_id;
1099
1100 CURSOR c_ord_type(p_query_id IN NUMBER) IS
1101 SELECT object_type,
1102 source_type,
1103 sequence_id,
1104 and_or_flag
1105 FROM msc_pq_types
1106 WHERE query_id = p_query_id
1107 AND active_flag = 1
1108 ORDER BY sequence_id;
1109
1110 l_match_str VARCHAR2(10);
1111 l_temp_match_str VARCHAR2(10);
1112 l_row_count NUMBER ;
1113 l_p_row_count NUMBER ;
1114
1115 l_where_clause_segment VARCHAR2(2000);
1116 l_where2_clause_segment VARCHAR2(2000);
1117
1118 where_clause_segment VARCHAR2(32000);
1119 l_excp_where_clause_segment VARCHAR2(32000);
1120
1121 l_field_name VARCHAR2(50);
1122 l_data_set VARCHAR2(50);
1123 l_data_type VARCHAR2(50);
1124 l_merge_criteria NUMBER;
1125 l_build_Excp_where NUMBER;
1126 l_criteria_row_seq NUMBER;
1127
1128 BEGIN
1129 --KSA_DEBUG(SYSDATE,'inside...','build_order_where_clause');
1130
1131 l_p_row_count := 0;
1132 FOR c_ord_type_row IN c_ord_type(p_query_id) LOOP
1133 l_p_row_count := l_p_row_count + 1;
1134
1135 l_match_str := ' OR ';
1136 l_build_Excp_where := 0;
1137 l_excp_where_clause_segment := NULL;
1138
1139 --KSA_DEBUG(SYSDATE,'Object type is '||c_ord_type_row.object_type,'build_order_where_clause');
1140 l_row_count := 0;
1141 FOR c_criteria_row IN c_Ord_criteria(p_query_id,
1142 c_ord_type_row.object_type,
1143 c_ord_type_row.source_type,
1144 c_ord_type_row.sequence_id)
1145 LOOP
1146 l_row_count := l_row_count + 1;
1147 l_merge_criteria := 1;
1148 IF l_row_count = 1 AND l_p_row_count = 1 THEN
1149 l_temp_match_str := '';
1150 ELSIF l_row_count = 1 AND l_p_row_count > 1 THEN
1151 l_temp_match_str := ' OR ';
1152 l_merge_criteria := 0;
1153 ELSE
1154 IF c_ord_type_row.and_or_flag = 2 THEN
1155 l_temp_match_str := ' OR ';
1156 l_merge_criteria := 0;
1157 ELSE
1158 l_temp_match_str := ' AND ';
1159 l_merge_criteria := 1;
1160 END IF;
1161 END IF;
1162
1163 l_field_name := c_criteria_row.field_name ;
1164 l_data_set := c_criteria_row.data_set;
1165 l_data_type := c_criteria_row.field_type;
1166 --KSA_DEBUG(SYSDATE,'l_field_name...'||l_field_name,'MSC_PQ_UTILS.build_order_where_clause');
1167 l_where_clause_segment := MSC_PQ_UTILS.get_where_clause(c_criteria_row.sequence,
1168 c_criteria_row.object_sequence_id,
1169 l_field_name,
1170 c_criteria_row.condition,
1171 c_criteria_row.low_value,
1172 c_criteria_row.high_value,
1173 c_criteria_row.hidden_from_field,
1174 l_data_set,
1175 l_data_type,
1176 c_criteria_row.lov_type,
1177 l_temp_match_str, NULL);
1178 --KSA_DEBUG(SYSDATE,'l_where_clause_segment...'||l_where_clause_segment,'MSC_PQ_UTILS.build_order_where_clause');
1179 IF l_field_name = 'EXCEPTION_TYPE' THEN
1180 IF l_merge_criteria = 1 THEN
1181 l_build_Excp_where := 1;
1182 l_criteria_row_seq := c_criteria_row.sequence;
1183 IF l_excp_where_clause_segment IS NULL THEN
1184 l_excp_where_clause_segment := '( '||l_where_clause_segment||' ) ';
1185 ELSE
1186 l_excp_where_clause_segment := l_excp_where_clause_segment||
1187 l_temp_match_str||' ( '||
1188 l_where_clause_segment||' ) ';
1189 END IF;
1190 ELSE
1191 MSC_PQ_UTILS.build_Excp_where(p_query_id,
1192 c_ord_type_row.sequence_id,
1193 c_criteria_row.sequence,
1194 p_plan_id,
1195 l_where_clause_segment,
1196 l_where_clause_segment);
1197 END IF;
1198 END IF;
1199 IF l_field_name = 'EXCEPTION_TYPE' AND l_merge_criteria = 1 THEN
1200 NULL; -- Do not merge Exceptions where clause at this point.
1201 ELSE
1202 IF l_where2_clause_segment IS NULL THEN
1203 l_where2_clause_segment := '( '||l_where_clause_segment||' ) ';
1204 ELSE
1205 l_where2_clause_segment := l_where2_clause_segment||
1206 l_temp_match_str||' ( '||
1207 l_where_clause_segment||' ) ';
1208 END IF;
1209 END IF;
1210 IF l_field_name like '%ITEM_SEGMENTS%'
1211 OR l_field_name like '%SUPPLIER%'
1212 OR l_field_name like '%CUSTOMER%' THEN
1213 --OR l_field_name like '%ORGANIZATION%' THEN
1214 IF l_excp_where_clause_segment IS NULL THEN
1215 l_excp_where_clause_segment := '( '||l_where_clause_segment||' ) ';
1216 ELSE
1217 l_excp_where_clause_segment := l_excp_where_clause_segment||
1218 l_temp_match_str||' ( '||
1219 l_where_clause_segment||' ) ';
1220 END IF;
1221 END IF;
1222 END LOOP;
1223 IF l_build_Excp_where = 1 THEN
1224 MSC_PQ_UTILS.build_Excp_where(p_query_id,
1225 c_ord_type_row.sequence_id,
1226 l_criteria_row_seq,
1227 p_plan_id,
1228 l_excp_where_clause_segment,
1229 l_where_clause_segment);
1230 l_where2_clause_segment := l_where2_clause_segment||
1231 l_temp_match_str||' ( '||
1232 l_where_clause_segment||' ) ';
1233 END IF;
1234 IF where_clause_segment IS NULL THEN
1235 where_clause_segment := ' ( '||l_where2_clause_segment||' ) ';
1236 ELSE
1237 where_clause_segment := where_clause_segment||' OR ( '||
1238 l_where2_clause_segment||' ) ';
1239 END IF;
1240
1241 l_where_clause_segment := NULL;
1242 l_where2_clause_segment := NULL;
1243 END LOOP;
1244 --KSA_DEBUG(SYSDATE,'Exiting...','build_order_where_clause');
1245 RETURN where_clause_segment;
1246 EXCEPTION
1247 WHEN OTHERS THEN
1248 --KSA_DEBUG(SYSDATE,'Error...'||sqlerrm(sqlcode),'MSC_PQ_UTILS.build_order_where_clause');
1249 RETURN (NULL);
1250 END build_order_where_clause;
1251
1252 FUNCTION get_where_clause (sequence NUMBER,
1253 obj_sequence NUMBER,
1254 field_name IN OUT NOCOPY VARCHAR2,
1255 operator NUMBER,
1256 low VARCHAR2,
1257 high VARCHAR2,
1258 hidden_from VARCHAR2,
1259 data_set IN OUT NOCOPY varchar2,
1260 data_type IN OUT NOCOPY VARCHAR2,
1261 lov_type IN NUMBER,
1262 p_match_str IN VARCHAR2,
1263 p_excp_where IN VARCHAR2)
1264 RETURN VARCHAR2 IS
1265 low_value VARCHAR2(200);
1266 high_value VARCHAR2(200);
1267 translated_op VARCHAR2(30);
1268 where_clause_segment VARCHAR2(32000);
1269 BEGIN
1270 --KSA_DEBUG(SYSDATE,'inside...','get_where_clause');
1271 IF operator IN (11, 14) THEN
1272 IF data_type IN ('MULTI','ORG') THEN
1273 IF data_type = 'ORG' THEN
1274 data_set := '('||REPLACE(data_set,':',',')||')';
1275 END IF;
1276 END IF;
1277
1278 IF p_excp_where IS NULL
1279 and data_type IN ('CHAR','DATE','NUMBER')
1280 and data_set IS NOT NULL THEN
1281 IF data_type IN ('DATE') THEN
1282 field_name := ' trunc( '||data_set||') ' ;
1283 ELSE
1284 field_name := data_set;
1285 END IF;
1286 ELSIF data_type IN ('DATE') THEN
1287 field_name := ' trunc( '||field_name||') ' ;
1288 END IF;
1289 low_value := '';
1290 ELSE
1291 --little trick to get correct field_name for exceptons
1292 IF p_excp_where IS NULL THEN
1293 if data_type IN ('CHAR','DATE','NUMBER') AND data_set IS NOT NULL THEN
1294 field_name := data_set;
1295 ELSIF data_type IN ('MULTI') THEN
1296 IF ( field_name LIKE 'DEMAND%ITEM_SEGMENTS' ) THEN
1297 field_name := 'ITEM_SEGMENTS';
1298 ELSIF ( field_name LIKE 'DEMAND%PRODUCT_FAMILY' ) THEN
1299 field_name := 'PRODUCT_FAMILY';
1300 ELSIF ( INSTR(field_name,'~') > 0 ) THEN
1301 field_name := SUBSTR(field_name, instr(field_name,'~')+1);
1302 END IF;
1303 END IF;
1304 END IF;
1305
1306 IF operator = 13 THEN
1307 low_value := RTRIM(LTRIM(low));
1308 IF ( SUBSTR(low_value, LENGTH(low_value),1) <> '%') THEN
1309 low_value := low_value||'%';
1310 END IF;
1311 low_value := ''''||REPLACE(low_value, '''', '''''') ||'''';
1312 ELSIF data_type = 'CHAR' THEN
1313 low_value := ''''||REPLACE(low, '''', '''''') ||'''';
1314 IF (operator = 9) OR (operator = 10) THEN
1315 high_value := ''''||REPLACE(high, '''', '''''') ||'''';
1316 ELSE
1317 high_value := REPLACE(high, '''', '''''');
1318 END IF;
1319 ELSIF data_type = 'DATE' THEN
1320 IF operator = 12 THEN
1321 low_value := low;
1322 high_value := high;
1323 ELSE
1324 low_value := 'fnd_date.displaydate_to_date('||''''||low||''''||')';
1325 IF (operator = 9) OR (operator = 10) THEN
1326 high_value := 'fnd_date.displaydate_to_date('||''''||high||''''||')';
1327 ELSE
1328 high_value := high;
1329 END IF;
1330 END IF;
1331 field_name := ' trunc( '||field_name||') ' ;
1332 ELSIF data_type = 'NUMBER' THEN
1333 low_value := NVL(hidden_from, low);
1334 low_value := ''''||low_value||'''';
1335
1336 high_value :=''''||high||'''';
1337 ELSIF data_type IN ('ORG','MULTI') THEN
1338 low_value := ''''||low||'''';
1339 high_value :=''''||high||'''';
1340 IF data_type IN ('MULTI') THEN
1341 IF (field_name LIKE '%PRODUCT_FAMILY') THEN
1342 field_name := 'PRODUCT_FAMILY';
1343 ELSIF (field_name LIKE '%ITEM_SEGMENTS') THEN
1344 field_name := 'ITEM_SEGMENTS';
1345 ELSIF (field_name LIKE '%ORDER_TYPE') THEN
1346 field_name := 'ORDER_TYPE';
1347 ELSIF (field_name LIKE '%EXCEPTION_TYPE') THEN
1348 field_name := 'EXCEPTION_TYPE';
1349 END IF;
1350 END IF;
1351 END IF;
1352 END IF;
1353 IF operator = 1 THEN translated_op := ' = ';
1354 ELSIF operator = 2 THEN translated_op := ' <> ';
1355 ELSIF operator = 3 THEN translated_op := ' >= ';
1356 ELSIF operator = 4 THEN translated_op := ' <= ';
1357 ELSIF operator = 5 THEN translated_op := ' > ';
1358 ELSIF operator = 6 THEN translated_op := ' < ';
1359 ELSIF operator = 7 THEN translated_op := ' IS NOT NULL ';
1360 ELSIF operator = 8 THEN translated_op := ' IS NULL ';
1361 ELSIF operator = 9 THEN translated_op := ' BETWEEN ';
1362 ELSIF operator = 10 THEN translated_op := ' NOT BETWEEN ';
1363 ELSIF operator = 11 THEN translated_op := ' IN ';
1364 ELSIF operator = 12 THEN translated_op := ' BETWEEN ';
1365 ELSIF operator = 13 THEN translated_op := ' LIKE ';
1366 ELSIF operator = 14 THEN translated_op := ' NOT IN ';-- FOR Orders query
1367 END IF;
1368 IF operator IN (12) THEN -- rolling dates
1369 IF (high_value IS NULL) THEN
1370 where_clause_segment := where_clause_segment ||
1371 field_name ||
1372 translated_op ||
1373 ' trunc(sysdate) AND trunc(sysdate) + '||
1374 low_value ;
1375 ELSE
1376 where_clause_segment := where_clause_segment ||
1377 field_name ||
1378 translated_op ||
1379 ' trunc(sysdate) + ' ||
1380 low_value ||
1381 ' AND ' ||
1382 ' trunc(sysdate) + ' ||
1383 high_value ;
1384 END IF;
1385 ELSIF operator IN (9,10) THEN -- operator is BETWEEN or OUTSIDE
1386 where_clause_segment := where_clause_segment ||
1387 field_name ||
1388 translated_op ||
1389 low_value ||
1390 ' AND ' ||
1391 high_value ;
1392 ELSIF operator IN (8,7) THEN -- operator is IS NOT NULL or IS NULL
1393 where_clause_segment := where_clause_segment ||
1394 field_name ||
1395 translated_op ;
1396 ELSIF operator IN (1,2) AND data_type IN ('ORG') THEN
1397 where_clause_segment := where_clause_segment ||
1398 SUBSTR(data_set,
1399 1, INSTR(data_set,':')-1) ||
1400 translated_op ||
1401 SUBSTR(hidden_from,
1402 1,INSTR(hidden_from,':')-1)||
1403 ' AND ' ||
1404 SUBSTR(data_set, INSTR(data_set,':')+1)
1405 ||translated_op||SUBSTR(hidden_from,INSTR(hidden_from,':')+1);
1406 ELSIF operator IN (1,2) AND data_type IN ('MULTI') THEN
1407 --KSA_DEBUG(SYSDATE,'operation...'||operator,'get_where_clause');
1408 where_clause_segment := where_clause_segment||
1409 data_set ||
1410 translated_op ||
1411 hidden_from ;
1412 ELSIF operator IN (11,14) THEN -- operator is AMONG
1413 --KSA_DEBUG(SYSDATE,'where_clause_segment...'||where_clause_segment,'MSC_PQ_UTILS.get_where_clause');
1414 where_clause_segment := where_clause_segment ||
1415 get_among_where_clause (sequence, obj_sequence,translated_op, field_name,
1416 operator, low, high, hidden_from, data_set, data_type)||' ';
1417
1418 ELSIF operator = 13 THEN
1419 where_clause_segment := where_clause_segment || ' upper('||field_name||') '
1420 || translated_op|| UPPER(low_value);
1421 ELSE
1422 where_clause_segment := where_clause_segment ||
1423 field_name || translated_op||low_value ;
1424 END IF;
1425 --KSA_DEBUG(SYSDATE,'where_clause_segment...'||where_clause_segment,'get_where_clause');
1426 RETURN where_clause_segment;
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 --KSA_DEBUG(SYSDATE,'Error...'||sqlerrm(sqlcode),'MSC_PQ_UTILS.get_where_clause');
1430 RAISE;
1431 END get_where_clause;
1432
1433 PROCEDURE retrieve_values (p_folder_id number) IS
1434 --or_rg_name VARCHAR2(30) := 'SCOPE1_RG';
1435 --or_rg_id RecordGroup;
1436 --gc_id GroupColumn;
1437
1438 current_row NUMBER;
1439
1440 CURSOR among_values IS
1441 SELECT msc.folder_object,
1442 mav.sequence,
1443 mav.object_sequence,
1444 mav.field_name,
1445 DECODE(msc.field_type,
1446 'DATE', fnd_date.date_to_displaydate(
1447 fnd_date.canonical_to_date(mav.or_values)),
1448 'NUMBER', DECODE(mc.lov_type,
1449 1, TO_CHAR(
1450 fnd_number.canonical_to_number(mav.or_values)),
1451 mav.or_values),
1452 mav.or_values) or_values,
1453 mav.hidden_values
1454 FROM msc_among_values mav,
1455 msc_selection_criteria msc,
1456 msc_criteria mc
1457 WHERE mav.folder_id = p_folder_id
1458 AND msc.folder_id=mav.folder_id
1459 AND mc.folder_object =msc.folder_object
1460 AND mc.field_name = msc.field_name
1461 AND msc.sequence = mav.sequence
1462 AND nvl(msc.object_sequence_id,-1) = nvl(mav.object_sequence,-1);
1463
1464 among_values_rec among_values%ROWTYPE;
1465
1466 CURSOR c_delete IS
1467 SELECT distinct field_name
1468 FROM msc_among_values
1469 WHERE folder_id = p_folder_id;
1470
1471 l_name varchar2(50);
1472
1473 BEGIN
1474 --KSA_DEBUG(SYSDATE,'inside...p_folder_id'||p_folder_id,'MSC_PQ_UTILS.retrieve_values');
1475
1476 /*OPEN c_delete;
1477 LOOP
1478 FETCH c_delete into l_name;
1479 EXIT WHEN c_delete%notfound;
1480 delete_rows(l_name);
1481 end loop;
1482 CLOSE c_delete;*/
1483 clear_values;
1484
1485 OPEN among_values;
1486 Loop
1487 FETCH among_values INTO among_values_rec;
1488 EXIT WHEN among_values%NOTFOUND;
1489 store_values(among_values_rec.sequence,
1490 among_values_rec.object_sequence,
1491 among_values_rec.field_name,
1492 among_values_rec.or_values,
1493 among_values_rec.hidden_values);
1494 END LOOP;
1495 CLOSE among_values;
1496 --KSA_DEBUG(SYSDATE,'exiting...','MSC_PQ_UTILS.retrieve_values');
1497 END retrieve_values;
1498
1499 FUNCTION get_among_where_clause (sequence NUMBER,
1500 obj_sequence NUMBER,
1501 t_operator VARCHAR2,
1502 field_name IN OUT NOCOPY VARCHAR2,
1503 operator NUMBER,
1504 low VARCHAR2,
1505 high VARCHAR2,
1506 hidden_from VARCHAR2,
1507 data_set IN OUT NOCOPY VARCHAR2,
1508 datatype IN OUT NOCOPY VARCHAR2)
1509 RETURN VARCHAR2 IS
1510
1511 --p_or_rg_name CONSTANT VARCHAR2(30) := 'SCOPE1_RG';
1512 --or_rg_name VARCHAR2(30) := p_or_rg_name;
1513 tmp_str VARCHAR2(5);
1514 total_rows NUMBER;
1515 value_list VARCHAR2(1000);
1516 current_value VARCHAR2(155);
1517 v_one_record VARCHAR2(100);
1518 BEGIN
1519 IF operator NOT IN (11,14) THEN -- operator is not AMONG
1520 RETURN '11=11';
1521 END IF;
1522 --KSA_DEBUG(SYSDATE,'inside...field_name is '||field_name,'get_among_where_clause');
1523 value_list :=NULL;
1524 total_rows := g_among_values.count; --Get_Group_Row_Count(or_rg_name);
1525 IF total_rows <= 0 THEN
1526 RETURN '11=11';
1527 END IF;
1528
1529 FOR counter IN 1..total_rows LOOP
1530 IF NOT g_among_values.exists(counter) THEN
1531 NULL;
1532 ELSIF sequence = g_among_values(counter).SEQUENCE AND
1533 obj_sequence = g_among_values(counter).OBJECT_SEQUENCE AND
1534 field_name = g_among_values(counter).FIELD_NAME AND
1535 g_among_values(counter).OR_VALUES IS NOT NULL THEN
1536 --Get_Group_Number_Cell(or_rg_name||'.SEQUENCE', counter) AND
1537 --Get_Group_Char_Cell(or_rg_name||'.OR_VALUES', counter) IS NOT NULL THEN
1538 IF datatype <> 'ORG' THEN
1539 IF datatype = 'MULTI' THEN
1540 current_value := g_among_values(counter).HIDDEN_VALUES; --Get_Group_Char_Cell(or_rg_name||'.HIDDEN_VALUES', counter);
1541 ELSE
1542 current_value := NVL(g_among_values(counter).HIDDEN_VALUES,
1543 g_among_values(counter).OR_VALUES);
1544 --NVL(Get_Group_Char_Cell(or_rg_name||'.HIDDEN_VALUES', counter),
1545 -- Get_Group_Char_Cell(or_rg_name||'.OR_VALUES', counter));
1546 IF datatype='DATE' THEN
1547 current_value :=
1548 'fnd_date.displaydate_to_date('||''''||current_value||''''||')';
1549 ELSIF datatype ='CHAR' THEN
1550 current_value :=''''||REPLACE(current_value, '''', '''''')||'''';
1551 ELSIF datatype = 'NUMBER' THEN
1552 current_value :=''''||current_value||'''';
1553 END IF;
1554 END IF;
1555 ELSE -- datatype = 'ORG'
1556 v_one_record := g_among_values(counter).HIDDEN_VALUES; --Get_Group_Char_Cell(or_rg_name||'.HIDDEN_VALUES', counter);
1557 current_value := '('||
1558 SUBSTR(v_one_record,1,INSTR(v_one_record,':')-1) ||','||
1559 SUBSTR(v_one_record,INSTR(v_one_record,':')+1)||')';
1560 END IF;
1561 value_list :=value_list || tmp_str ||current_value;
1562 tmp_str :=', ';
1563 END IF;
1564 END LOOP;
1565 --KSA_DEBUG(SYSDATE,'Exiting, value_list is '||value_list,'MSC_PQ_UTILS.get_among_where_clause');
1566 IF datatype IN ('ORG','MULTI') THEN
1567 RETURN ' (1=1 AND (' ||data_set || t_operator ||' ( '||value_list||'))) ';
1568 ELSE
1569 RETURN ' (1=1 AND (' ||field_name || t_operator ||' ( '||value_list||'))) ';
1570 END IF;
1571 END get_among_where_clause;
1572
1573 PROCEDURE build_Excp_where(p_query_id IN NUMBER,
1574 p_obj_sequence_id IN NUMBER,
1575 p_sequence_id IN NUMBER,
1576 p_plan_id IN NUMBER,
1577 p_where_clause IN VARCHAR2,
1578 p_excp_where_clause IN OUT NOCOPY VARCHAR2,
1579 p_match_str IN VARCHAR2 DEFAULT ' AND ') IS
1580 --v_excp_str VARCHAR2(1000);
1581 --v_excp_where VARCHAR2(1000);
1582 --v_insert_stmt VARCHAR2(2000);
1583 v_delete_stmt VARCHAR2(2000);
1584 l_where_clause VARCHAR2(32000);
1585 --v_sysdate := SYSDATE;
1586 BEGIN
1587
1588 p_excp_where_clause := NULL;
1589
1590 g_query_id := p_query_id;
1591 g_obj_sequence_id := p_obj_sequence_id;
1592 g_sequence_id := p_sequence_id;
1593
1594 l_where_clause := REPLACE(REPLACE(p_where_clause,'SOURCE_SR_INSTANCE_ID','SOURCE_ORG_INSTANCE_ID'),'VENDOR_ID','SUPPLIER_ID');
1595 /*v_excp_str := ' SELECT DISTINCT '||p_query_id||', '||p_obj_sequence_id||', '||p_sequence_id ||
1596 ', med.TRANSACTION_ID, med.INVENTORY_ITEM_ID ' ||
1597 ', med.SR_INSTANCE_ID,med.ORGANIZATION_ID ' ||
1598 ', SYSDATE, fnd_global.user_id,SYSDATE, fnd_global.user_id, fnd_global.login_id'||
1599 ' FROM MSC_EXCEPTION_DETAILS_V med ';*/
1600 /*v_excp_str := ' SELECT DISTINCT '||p_query_id||', '||p_obj_sequence_id||', '||p_sequence_id ||
1601 ', med.TRANSACTION_ID, med.INVENTORY_ITEM_ID ' ||
1602 ', SYSDATE, fnd_global.user_id,SYSDATE, fnd_global.user_id, fnd_global.login_id'||
1603 ' FROM MSC_EXCEPTION_DETAILS_V med ';*/
1604 /*v_excp_where := ' WHERE med.plan_id = '||p_plan_id ||
1605 ' AND NVL(med.category_set_id,2) = 2' ||
1606 ' AND ( med.ORDER_NUMBER IS NOT NULL '||
1607 ' OR med.INVENTORY_ITEM_ID IS NOT NULL)';*/
1608 /*v_excp_where := ' WHERE med.plan_id = '||p_plan_id ||
1609 ' AND NVL(med.category_set_id,2) = 2' ||
1610 ' AND ( med.TRANSACTION_ID IS NOT NULL '||
1611 ' OR med.INVENTORY_ITEM_ID IS NOT NULL)';*/
1612 /*v_insert_stmt := ' INSERT INTO MSC_FORM_QUERY (QUERY_ID,'||
1613 ' NUMBER1,NUMBER2,CHAR1,NUMBER3,' ||
1614 ' NUMBER4,NUMBER5,' ||
1615 ' LAST_UPDATE_DATE, LAST_UPDATED_BY , ' ||
1616 ' CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN )';*/
1617 /*v_insert_stmt := ' INSERT INTO MSC_FORM_QUERY (QUERY_ID,'||
1618 ' NUMBER1,NUMBER2,NUMBER3,NUMBER4,' ||
1619 ' LAST_UPDATE_DATE, LAST_UPDATED_BY , ' ||
1620 ' CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN )';*/
1621 --KSA_DEBUG(SYSDATE,'STMT IS '||v_insert_stmt||' '||v_excp_str||' '||v_excp_where||' '||p_where_clause,'build_Excp_where');
1622 v_delete_stmt := 'DELETE MSC_FORM_QUERY WHERE QUERY_ID = :p_query_id'
1623 ||' AND NUMBER1 = :p_obj_sequence_id AND NUMBER2 = :p_sequence_id';
1624 --KSA_DEBUG(SYSDATE,'del STMT IS '||v_delete_stmt,'build_Excp_where');
1625 execute immediate v_delete_stmt using p_query_id, p_obj_sequence_id, p_sequence_id;
1626 --msc_get_name.execute_dsql(v_delete_stmt);
1627 --KSA_DEBUG(SYSDATE,'ex where is '||v_insert_stmt||v_excp_str||v_excp_where||' AND '||l_where_clause,'build_Excp_where');
1628 --msc_get_name.execute_dsql(v_insert_stmt||v_excp_str||v_excp_where||' AND '||l_where_clause);
1629 --KSA_DEBUG(SYSDATE,'where is '||l_where_clause,'build_Excp_where');
1630 Parse_exceptions(p_plan_id, l_where_clause);
1631 IF g_items_list_exists > 0 THEN
1632 p_excp_where_clause := '('||
1633 ' TRANSACTION_ID IN (SELECT NUMBER3 FROM MSC_FORM_QUERY '||
1634 ' WHERE QUERY_ID = '||p_query_id ||
1635 ' AND NUMBER1 = '||p_obj_sequence_id||
1636 ' AND NUMBER2 = '|| p_sequence_id ||
1637 ' AND NUMBER3 IS NOT NULL' ||')' ||
1638 ' OR '||
1639 ' INVENTORY_ITEM_ID '||
1640 ' IN (SELECT NUMBER4 FROM MSC_FORM_QUERY '||
1641 ' WHERE QUERY_ID = '||p_query_id||
1642 ' AND NUMBER1 = '||p_obj_sequence_id||
1643 ' AND NUMBER2 = '|| p_sequence_id||
1644 ' AND NUMBER4 IS NOT NULL)'||
1645 ')';
1646 ELSE
1647 p_excp_where_clause := '('||
1648 ' TRANSACTION_ID IN (SELECT NUMBER3 FROM MSC_FORM_QUERY '||
1649 ' WHERE QUERY_ID = '||p_query_id ||
1650 ' AND NUMBER1 = '||p_obj_sequence_id||
1651 ' AND NUMBER2 = '|| p_sequence_id ||
1652 ' AND NUMBER3 IS NOT NULL' ||')' ||
1653 ')';
1654 END IF;
1655 END build_Excp_where;
1656
1657 PROCEDURE store_values(p_sequence IN NUMBER,
1658 p_obj_sequence IN NUMBER,
1659 p_field_name IN VARCHAR2,
1660 p_or_values IN VARCHAR2,
1661 p_hidden_values IN VARCHAR2) IS
1662 l_count NUMBER;
1663 BEGIN
1664 --KSA_DEBUG(SYSDATE,'inside...p_field_name'||p_field_name,'MSC_PQ_UTILS.store_values');
1665 l_count := g_among_values.count;
1666 g_among_values(l_count+1).sequence := p_sequence;
1667 g_among_values(l_count+1).object_sequence := p_obj_sequence;
1668 g_among_values(l_count+1).field_name := p_field_name;
1669 g_among_values(l_count+1).or_values := p_or_values;
1670 g_among_values(l_count+1).hidden_values := p_hidden_values;
1671 --KSA_DEBUG(SYSDATE,'exiting...','MSC_PQ_UTILS.store_values');
1672 END store_values;
1673
1674 PROCEDURE clear_values IS
1675 BEGIN
1676 g_among_values.delete;
1677 END clear_values;
1678
1679 PROCEDURE delete_rows(p_field_name in varchar2) IS
1680 total_rows NUMBER;
1681 deleted_rows NUMBER:=0;
1682 l_cur_field_name varchar2(100);
1683 BEGIN
1684 --KSA_DEBUG(SYSDATE,'inside...p_field_name'||p_field_name,'delete_rows');
1685 total_rows :=g_among_values.count;
1686 IF total_rows > 0 THEN
1687 FOR counter IN 1 .. total_rows LOOP
1688 --KSA_DEBUG(SYSDATE,'total_rows...'||total_rows,'delete_rows');
1689 IF g_among_values.exists(counter - deleted_rows) THEN
1690 l_cur_field_name := g_among_values(counter - deleted_rows).FIELD_NAME;
1691 IF p_field_name = l_cur_field_name then
1692 g_among_values.delete(counter - deleted_rows);
1693 deleted_rows :=deleted_rows+1;
1694 total_rows :=total_rows-1;
1695 END if;
1696 END IF;
1697 END Loop;
1698 END IF;
1699 --KSA_DEBUG(SYSDATE,'exiting...','delete_rows');
1700 END delete_rows;
1701
1702
1703
1704 FUNCTION validate_index_use(p_query_id IN NUMBER,
1705 p_query_type IN NUMBER) RETURN NUMBER IS
1706
1707 CURSOR c_validate IS
1708 SELECT count(*)
1709 FROM msc_selection_criteria msc,
1710 msc_personal_queries mpq
1711 WHERE (mpq.query_id = msc.folder_id
1712 AND mpq.query_id = p_query_id and msc.active_flag=1)
1713 AND ( ( mpq.query_type = 1
1714 AND field_name in ('BUYER_NAME', 'ITEM_SEGMENTS',
1715 'ABC_CLASS_NAME','CATEGORY',
1716 'ORGANIZATION_CODE', 'PLANNER_CODE') ) --item
1717 OR( mpq.query_type = 2
1718 AND field_name in ('RESOURCE_CODE', 'ORGANIZATION_CODE', 'DEPARTMENT_LINE_CODE') ) --res
1719 OR( (mpq.query_type = 4 and source_type = 1)
1720 AND ( field_name like '%ORGANIZATION_CODE%'
1721 OR field_name like '%PLANNER_CODE%'
1722 OR field_name like '%RESOURCE_CODE%'
1723 OR field_name like '%DEPARTMENT_CODE%'
1724 OR field_name like '%ITEM_SEGMENTS%'
1725 OR field_name like '%CATEGORY_NAME%'
1726 OR field_name like '%ITEM_NAME%') ) --excp
1727 OR ( (mpq.query_type = 4 and source_type = 2))
1728 OR ( mpq.query_type = 5
1729 AND field_name in ('ORGANIZATION_CODE', 'ITEM_NAME',
1730 'CATEGORY_NAME', 'SUPPLIER_NAME',
1731 'BUYER_NAME', 'PLANNER_CODE') ) --supplier
1732 OR (mpq.query_type = 6)); --loads
1733
1734 CURSOR c_ord_qry(p_query_id IN NUMBER) IS
1735 SELECT mpt.source_type, mpt.object_type, mpt.sequence_id
1736 FROM msc_pq_types mpt
1737 WHERE mpt.query_id = p_query_id
1738 AND mpt.active_flag = 1;
1739
1740 CURSOR c_validate_ord(p_query_id IN NUMBER,
1741 P_source_type IN NUMBER,
1742 P_object_type IN NUMBER,
1743 P_sequence_id IN NUMBER) IS
1744 SELECT COUNT(*)
1745 FROM msc_selection_criteria msc,
1746 msc_pq_types mpt,
1747 msc_personal_queries mpq
1748 WHERE (mpq.query_id = mpt.query_id
1749 AND mpq.query_id = p_query_id
1750 AND mpt.active_flag = 1
1751 AND msc.folder_id = mpt.query_id
1752 AND msc.source_type = mpt.source_type
1753 AND msc.object_type = mpt.object_type
1754 AND msc.object_sequence_id = mpt.sequence_id
1755 AND msc.active_flag=1
1756 AND mpt.source_type = P_source_type
1757 AND mpt.object_type = P_object_type
1758 AND mpt.sequence_id = P_sequence_id)
1759 AND ( msc.field_name like '%ORGANIZATION_CODE%'
1760 OR msc.field_name like '%PLANNER_CODE%'
1761 OR msc.field_name like '%ITEM_SEGMENTS%'
1762 OR msc.field_name like '%CATEGORY_NAME%'
1763 OR msc.field_name like '%ITEM_NAME%');--orders
1764
1765 CURSOR c_validate_wl IS
1766 SELECT count(*)
1767 FROM msc_selection_criteria msc,
1768 msc_personal_queries mpq
1769 WHERE mpq.query_id = msc.folder_id
1770 AND mpq.query_id = p_query_id
1771 AND msc.active_flag = 1
1772 AND mpq.query_type = 10
1773 AND (( source_type = 1
1774 AND ( field_name like '%ORGANIZATION_CODE%'
1775 OR field_name like '%PLANNER_CODE%'
1776 OR field_name like '%RESOURCE_CODE%'
1777 OR field_name like '%DEPARTMENT_CODE%'
1778 OR field_name like '%ITEM_SEGMENTS%'
1779 OR field_name like '%CATEGORY_NAME%'
1780 OR field_name like '%ITEM_NAME%') ) --excp
1781 OR source_type = 2);
1782
1783 l_temp number;
1784
1785 CURSOR c_groupby is
1786 SELECT distinct field_name
1787 FROM msc_selection_criteria
1788 WHERE folder_id = p_query_id
1789 AND NVL(count_by,2) = 1;
1790
1791 l_dummy_field varchar2(100);
1792 l_profile varchar2(10);
1793 l_msg VARCHAR2(2000);
1794 l_warnning NUMBER;
1795 l_query_exists NUMBER;
1796
1797 index_validation_error EXCEPTION;
1798 PRAGMA EXCEPTION_INIT(index_validation_error, -20009);
1799 BEGIN
1800 --KSA_DEBUG(SYSDATE,'VI p_query_id <> '||p_query_id||' p_query_Type '||p_query_type,'validate_index_use');
1801 l_query_exists := 0;
1802 l_warnning := 1;
1803 IF p_query_type = 10 THEN
1804 FOR detailQrec IN detailQCur(p_query_id) LOOP
1805 l_warnning := validate_index_use(detailQrec.query_id, detailQrec.query_type);
1806 IF l_warnning < 1 THEN
1807 raise index_validation_error;
1808 END IF;
1809 l_query_exists := 1; -- at least one query exists
1810 END LOOP;
1811 -- ----------------------------------------
1812 -- Now check for index usage for exceptions
1813 -- ----------------------------------------
1814 --p_query_type := 4;
1815 --ELSE
1816 --p_query_type := p_query_type;
1817 END IF;
1818 IF p_query_type = 9 THEN
1819 FOR rec_ord_qry IN c_ord_qry(p_query_id) LOOP
1820 l_temp := 0;
1821 OPEN c_validate_ord(p_query_id ,
1822 rec_ord_qry.source_type,
1823 rec_ord_qry.object_type,
1824 rec_ord_qry.sequence_id);
1825 FETCH c_validate_ord into l_temp;
1826 IF l_temp = 0 THEN
1827 CLOSE c_validate_ord;
1828 EXIT;
1829 END IF;
1830 CLOSE c_validate_ord;
1831 END LOOP;
1832 ELSIF p_query_type = 10 THEN
1833 open c_validate_wl;
1834 fetch c_validate_wl into l_temp;
1835 close c_validate_wl;
1836 ELSE
1837 open c_validate;
1838 fetch c_validate into l_temp;
1839 close c_validate;
1840 END IF;
1841 l_profile := nvl(FND_PROFILE.VALUE('MSC_PQUERY_EXEC_WITH_CRITERIA'), 'Y');
1842 l_temp := nvl(l_temp,0);
1843 if (l_temp = 0 and l_profile = 'Y' ) then
1844 if (p_query_type = 1) then
1845 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_ITEM_YES');
1846 elsif (p_query_type = 2) then
1847 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_RES_YES');
1848 elsif (p_query_type = 4) THEN
1849 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_EXCP_YES');
1850 elsif (p_query_type = 5) then
1851 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_SUPP_YES');
1852 elsif (p_query_type = 9) THEN -- orders
1853 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_ORD_YES');
1854 elsif (p_query_type = 9) THEN -- orders
1855 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_ORD_YES');
1856 elsif (p_query_type = 10) THEN -- Wlist
1857 NULL;
1858 --fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_ORD_YES');
1859 else
1860 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK');
1861 end if;
1862 l_msg:= fnd_message.get;
1863 l_warnning := -1;
1864 raise index_validation_error;
1865 end if;
1866
1867 if (l_temp = 0 and l_profile = 'N' ) then
1868 if (p_query_type = 1) then
1869 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_ITEM_NO');
1870 elsif (p_query_type = 2) then
1871 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_RES_NO');
1872 elsif (p_query_type = 4) THEN
1873 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_EXCP_NO');
1874 elsif (p_query_type = 5) then
1875 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_SUPP_NO');
1876 elsif (p_query_type = 9) THEN -- orders
1877 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_ORD_NO');
1878 elsif (p_query_type = 10) THEN -- Wlist
1879 NULL;
1880 else
1881 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK');
1882 end if;
1883 --fnd_message.hint;
1884 l_warnning := 0;
1885 l_msg:= fnd_message.get;
1886 raise index_validation_error;
1887 end if;
1888
1889 if (p_query_type = 4) then
1890 l_temp := 0;
1891 open c_groupby;
1892 loop
1893 fetch c_groupby into l_dummy_field;
1894 exit when c_groupby%notfound;
1895 l_temp := l_temp + 1;
1896 end loop;
1897 close c_groupby;
1898
1899 if (l_temp > 5) then
1900 fnd_message.set_name('MSC', 'MSC_PQ_GROUPBY_CHECK');
1901 --fnd_message.error;
1902 l_msg := FND_MESSAGE.get;
1903 l_warnning := -1;
1904 raise index_validation_error;
1905 end if;
1906 end if;
1907
1908 RETURN l_warnning; -- 1, successful validation
1909 EXCEPTION
1910 WHEN index_validation_error THEN
1911 G_PQ_ERROR_MESSAGE := l_msg;
1912 RETURN l_warnning;
1913 WHEN OTHERS THEN
1914 G_PQ_ERROR_MESSAGE := sqlerrm(sqlcode);
1915 RETURN -2;
1916 end validate_index_use;
1917
1918 PROCEDURE delete_from_results_table(p_query_id IN NUMBER,
1919 p_plan_id IN NUMBER) IS
1920 BEGIN
1921 --KSA_DEBUG(SYSDATE,' p_query_id <> '||p_query_id,'delete_from_results_table');
1922 DELETE msc_pq_results
1923 WHERE query_id = p_query_id
1924 AND plan_id = p_plan_id ;
1925 END delete_from_results_table;
1926
1927 PROCEDURE execute_one(p_plan_id IN NUMBER,
1928 p_calledFromUI IN NUMBER,
1929 p_partOfWorklist IN NUMBER,
1930 p_query_id IN NUMBER,
1931 p_query_type IN NUMBER,
1932 p_execute_flag BOOLEAN DEFAULT TRUE,
1933 p_master_query_id IN NUMBER DEFAULT NULL) IS
1934
1935 where_clause_segment VARCHAR2(32000);
1936 where_clause_segment2 VARCHAR2(32000);
1937
1938 CURSOR c_query_name (p_query NUMBER) IS
1939 SELECT query_name
1940 FROM msc_personal_queries
1941 WHERE query_id = p_query;
1942
1943 l_query_name VARCHAR2(80);
1944 test BOOLEAN;
1945
1946 CURSOR c_plans IS
1947 SELECT compile_designator
1948 FROM msc_plans
1949 WHERE plan_id = -1;
1950
1951 l_plan VARCHAR2(30);
1952 l_query_type_temp NUMBER;
1953 l_master_query_id NUMBER;
1954 l_response NUMBER;
1955 l_category_id NUMBER;
1956 l_dummy NUMBER;
1957 BEGIN
1958 -- for criticality matrix , we are using p_master_query_id
1959 -- to pass category_id to the build_where_clause function
1960 if p_query_type <> 12 then
1961 l_master_query_id := p_master_query_id;
1962 else
1963 l_category_id := p_master_query_id;
1964 end if;
1965
1966 IF NOT p_execute_flag THEN
1967 RETURN;
1968 END IF;
1969 --KSA_DEBUG(SYSDATE,'q p_query_id <> '||p_query_id||' p_query_Type '||p_query_type,'execute_one');
1970 IF p_query_type <> 10 AND p_partOfWorklist = 1 THEN
1971 NULL;
1972 ELSE
1973 l_response := validate_index_use(p_query_id, p_query_type);
1974 /* IF l_response < 0 THEN
1975 Raise_Application_Error(-20001,G_PQ_ERROR_MESSAGE);
1976 RETURN; -- need to show some error
1977 END IF;*/
1978 END IF;
1979
1980 --KSA_DEBUG(SYSDATE,'before execute query, '||p_query_id||' p_query_Type '||p_query_type,'execute_one');
1981 IF p_query_type = 10 THEN
1982 delete_from_results_table(p_query_id,
1983 p_plan_id);
1984 FOR detailQrec IN detailQCur(p_query_id) LOOP
1985 execute_one(p_plan_id,
1986 0, -- not called from UI
1987 1, -- p_partOfWorklist
1988 detailQrec.query_id,
1989 detailQrec.query_type,
1990 TRUE, --p_execute_flag
1991 p_query_id); -- master query_id
1992 --l_query_exists := 1; -- at least one query exists
1993 END LOOP;
1994 -- ----------------------------------------
1995 -- Now check for index usage for exceptions
1996 -- ----------------------------------------
1997 l_query_type_temp := 4;
1998 ELSE
1999 l_query_type_temp := p_query_type;
2000 END IF;
2001 IF p_query_type IN (1,2,5,6) THEN
2002 retrieve_values(p_query_id);
2003 where_clause_segment := build_where_clause(p_query_id, 0);
2004 IF (where_clause_segment is null) THEN
2005 where_clause_segment := ' ( -99 = -99 ) ';
2006 END IF;
2007 where_clause_segment := ' ( '||where_clause_segment||' ) ';
2008 -- KSA_DEBUG(SYSDATE,'w clause <> '||where_clause_segment,'execute_one');
2009 msc_pers_queries.populate_result_table(p_query_id,
2010 p_query_type,
2011 p_plan_id,
2012 where_clause_segment,
2013 p_execute_flag,
2014 l_master_query_id);
2015 ELSIF p_query_type = 9 then
2016 msc_pq_utils.retrieve_values(p_query_id);
2017 where_clause_segment := msc_pq_utils.build_order_where_clause
2018 (p_query_id,
2019 p_plan_id);
2020 IF (where_clause_segment IS NULL) THEN
2021 where_clause_segment := ' ( -99 = -99 ) ';
2022 END IF;
2023 where_clause_segment := ' ( '||where_clause_segment||' ) ';
2024 -- -----------------------------------
2025 -- This need to be set in the UI code.
2026 -- -----------------------------------
2027 --msc_popup_pvt.g_order_where_clause := ' AND '||where_clause_segment;
2028 /* Not required to populate results table if executed from UI.*/
2029 --KSA_DEBUG(SYSDATE,'*2* w clause <> '||where_clause_segment,'execute_one');
2030 IF p_calledFromUI <> 1 THEN
2031 msc_pers_queries.populate_result_table
2032 (p_query_id,
2033 p_query_type,
2034 p_plan_id,
2035 where_clause_segment,
2036 p_execute_flag,
2037 l_master_query_id);
2038 END IF;
2039
2040 ELSIF p_query_type = 12 THEN
2041 retrieve_values(p_query_id);
2042 where_clause_segment := build_where_clause_new(p_query_id, 100, l_category_id);
2043 msc_pers_queries.populate_result_table(p_query_id,
2044 p_query_type, -1,where_clause_segment,
2045 p_execute_flag, p_master_query_id, p_partOfWorklist);
2046
2047
2048 ELSIF p_query_type IN (4,10) THEN
2049 IF P_QUERY_TYPE = 10 THEN
2050 -- --------------------------
2051 -- Check if worklist also contains
2052 -- Exceptions. If yes, continue to process them
2053 -- Else, do nothing.
2054 -- -------------------------------
2055 l_dummy := 0;
2056 OPEN WlExcepCur(p_query_id);
2057 FETCH WlExcepCur INTO l_dummy;
2058 IF WlExcepCur%NOTFOUND THEN
2059 l_dummy :=-99; -- Just to flag no process
2060 END IF;
2061 CLOSE WlExcepCur;
2062 END IF;
2063 IF p_query_type = 4 OR
2064 (p_query_type = 10 AND l_dummy <> -99) THEN
2065 retrieve_values(p_query_id);
2066 where_clause_segment := build_where_clause(p_query_id, 1);
2067 where_clause_segment2 := build_where_clause(p_query_id, 2);
2068 IF (where_clause_segment IS NULL) THEN
2069 where_clause_segment := ' ( -99 = -99 ) ';
2070 END IF;
2071 IF (where_clause_segment2 IS NULL) THEN
2072 where_clause_segment2 := ' ( -99 = -99 ) ';
2073 END IF;
2074 IF where_clause_segment IS NOT NULL THEN
2075 where_clause_segment := ' ( '||where_clause_segment||' ) ';
2076 -- KSA_DEBUG(SYSDATE,'*3* w clause <> '||where_clause_segment,'execute_one');
2077 msc_pers_queries.populate_result_table(p_query_id,
2078 p_query_type,
2079 p_plan_id,
2080 where_clause_segment,
2081 p_execute_flag,
2082 l_master_query_id);
2083 END IF;
2084 IF where_clause_segment2 IS NOT NULL THEN
2085 where_clause_segment2 := ' ( '||where_clause_segment2||' ) ';
2086 msc_pers_queries.populate_result_table(p_query_id,
2087 p_query_type, -1,where_clause_segment2,
2088 p_execute_flag,l_master_query_id);
2089 END IF;
2090 END IF;
2091 END IF;
2092
2093 /*OPEN c_query_name(p_query_id);
2094 FETCH c_query_name into l_query_name;
2095 CLOSE c_query_name;*/
2096
2097
2098 COMMIT; --test := APP_FORM.QUIETCOMMIT;
2099 --do_key('commit_form'); --quietcommit is there..
2100
2101 --copy(p_query_id, 'viewby_control.query_id');
2102 --copy(l_query_name, 'viewby_control.query_name');
2103 --set_item_property('viewby_control.query_name', item_is_valid, property_on);
2104
2105
2106 EXCEPTION
2107 WHEN OTHERS THEN
2108 DECLARE
2109 l_error VARCHAR2(255);
2110 BEGIN
2111 l_error := substr(sqlerrm(sqlcode),1,250);
2112 G_PQ_ERROR_MESSAGE:= l_error;
2113 --COPY(l_error,'GLOBAL.PQ_ERROR_MESSAGE');
2114 END;
2115 RAISE;
2116 END execute_one;
2117
2118 FUNCTION get_error RETURN VARCHAR2 IS
2119 BEGIN
2120 RETURN G_PQ_ERROR_MESSAGE;
2121 END get_error;
2122
2123 PROCEDURE set_impl IS
2124
2125 BEGIN
2126 NULL;
2127 EXCEPTION
2128 WHEN OTHERS THEN
2129 NULL;
2130 END set_impl;
2131
2132 PROCEDURE plans_release IS
2133
2134 BEGIN
2135 set_impl;
2136 EXCEPTION
2137 WHEN OTHERS THEN
2138 NULL;
2139 END plans_release;
2140
2141 FUNCTION get_release_status(p_sr_instance_id IN NUMBER,
2142 P_instance_code IN OUT NOCOPY VARCHAR2)
2143 RETURN NUMBER IS
2144 l_allow_release_flag NUMBER;
2145
2146 CURSOR cur_release_flag(p_instance_id NUMBER) IS
2147 SELECT DECODE(apps_ver,3,NVL(allow_release_flag ,2),
2148 4,NVL(allow_release_flag ,2),1) allow_release_flag,
2149 instance_code
2150 FROM msc_apps_instances
2151 WHERE instance_id = nvl(p_sr_instance_id,-1);
2152 BEGIN
2153 OPEN cur_release_flag(p_sr_instance_id);
2154 FETCH cur_release_flag into l_allow_release_flag,p_instance_code;
2155 IF cur_release_flag%NOTFOUND THEN
2156 l_allow_release_flag := 2;
2157 END IF;
2158 CLOSE cur_release_flag;
2159 RETURN( l_allow_release_flag) ;
2160 END get_release_status;
2161
2162 PROCEDURE release_status(errbuf OUT NOCOPY VARCHAR2,
2163 retcode OUT NOCOPY NUMBER,
2164 p_plan_id IN NUMBER,
2165 p_transaction_Id IN NUMBER,
2166 p_sr_Instance_Id NUMBER) IS
2167
2168 v_err_msg varchar2(80);
2169 l_instance_code varchar2(30);
2170 BEGIN
2171 If get_release_status(p_sr_Instance_Id,l_instance_code) = 2 then
2172 fnd_message.set_name('MSC','MSC_ALLOW_RELEASE_INSTANCE');
2173 fnd_message.set_token('INSTANCE',l_instance_code);
2174 errbuf := fnd_message.get;
2175 retcode := 1;
2176 end if;
2177 END release_status;
2178
2179 -- ---------------------------------------------
2180 -- This program will be called from
2181 -- MSC_GET_BIS_VALUES.ui_post_plan (MSCBISUB.PLS)
2182 -- ----------------------------------------------
2183
2184 PROCEDURE execute_plan_queries(errbuf OUT NOCOPY VARCHAR2,
2185 retcode OUT NOCOPY NUMBER,
2186 p_plan_id IN NUMBER) IS
2187 CURSOR cur_queries IS
2188 SELECT plq.query_Id
2189 FROM msc_plan_queries plq,
2190 msc_personal_queries pq
2191 WHERE plq.plan_id = p_plan_Id
2192 AND plq.query_id = pq.query_id
2193 AND pq.query_type = 9;
2194
2195 CURSOR cur_orders(p_transaction_Id IN NUMBER) IS
2196 SELECT sr_instance_id,organization_id, organization_code
2197 FROM msc_orders_v
2198 WHERE plan_id = p_plan_Id
2199 AND p_transaction_Id = p_transaction_Id;
2200 -- and <order type restrictions>
2201
2202 CURSOR check_release_method IS
2203 SELECT AUTO_RELEASE_METHOD
2204 FROM msc_plans
2205 WHERE plan_id = p_plan_Id;
2206
2207 TYPE r_cursor is REF CURSOR;
2208 t_cur r_cursor;
2209 rec_orders cur_orders%ROWTYPE;
2210
2211 l_stmt VARCHAR2(2000);
2212 l_list VARCHAR2(500);
2213
2214 l_transaction_id NUMBER;
2215 l_auto_release NUMBER;
2216 l_user_id NUMBER :=FND_PROFILE.VALUE('USER_ID');
2217 l_errbuf VARCHAR2(2000);
2218 l_retcode NUMBER;
2219 BEGIN
2220 -- ---------------------------------
2221 -- Check for release method.
2222 -- If auto release is not based on
2223 -- 'Orders' query, do nothing.
2224 -- ---------------------------------
2225 OPEN check_release_method;
2226 FETCH check_release_method INTO l_auto_release;
2227 CLOSE check_release_method;
2228 IF NVL(l_auto_release,0) <> 3 THEN
2229 RETURN;
2230 END IF;
2231 FOR rec_queries IN cur_queries LOOP
2232 execute_one(p_plan_id,
2233 2,
2234 2,
2235 rec_queries.query_id,
2236 9);
2237 IF l_list IS NULL THEN
2238 l_list:= rec_queries.query_id;
2239 ELSE
2240 l_list:= l_list||','||rec_queries.query_id;
2241 END IF;
2242 END LOOP;
2243 IF l_List IS NOT NULL THEN
2244 l_list := '('||l_list||')';
2245 l_stmt := 'SELECT DISTINCT TRANSACTION_ID FROM MSC_PQ_RESULTS '||
2246 'WHERE QUERy_ID IN '||l_lIST;
2247 OPEN t_cur FOR l_stmt;
2248 LOOP
2249 FETCH t_cur INTO l_transaction_id;
2250 EXIT WHEN t_cur%NOTFOUND;
2251 OPEN cur_orders(l_transaction_id);
2252 FETCH cur_orders INTO rec_orders;
2253 IF cur_orders%FOUND THEN
2254 release_status(l_errbuf,l_retcode,
2255 p_plan_id, rec_orders.sr_instance_id,
2256 rec_orders.organization_id);
2257 IF l_retcode < 0 THEN
2258 plans_release;
2259 END IF;
2260 END IF;
2261 CLOSE cur_orders;
2262 END LOOP;
2263 CLOSE t_cur;
2264 END IF;
2265 retcode := 0;
2266 EXCEPTION
2267 WHEN OTHERS THEN
2268 errbuf := 'unknown error'||sqlerrm(sqlcode);
2269 retcode := 1;
2270 END execute_plan_queries;
2271
2272 -- ---------------------------------------------
2273 -- This program will be called from
2274 -- MSC_GET_BIS_VALUES.ui_post_plan (MSCBISUB.PLS)
2275 -- ----------------------------------------------
2276
2277 PROCEDURE execute_plan_worklists(errbuf OUT NOCOPY VARCHAR2,
2278 retcode OUT NOCOPY NUMBER,
2279 p_plan_id IN NUMBER) IS
2280 CURSOR cur_worklists IS
2281 SELECT plq.query_Id,QUERY_TYPE
2282 FROM msc_plan_queries plq,
2283 msc_personal_queries pq
2284 WHERE plq.plan_id = p_plan_Id
2285 AND plq.query_id = pq.query_id
2286 AND pq.query_type = 10;
2287
2288 l_errbuf VARCHAR2(2000);
2289 l_retcode NUMBER;
2290 BEGIN
2291
2292 FOR rec_worklists IN cur_worklists LOOP
2293 execute_one(p_plan_id,
2294 0,
2295 0,
2296 rec_worklists.query_id,
2297 rec_worklists.query_type,
2298 TRUE,
2299 rec_worklists.query_id);
2300 END LOOP;
2301
2302 retcode := 0;
2303 EXCEPTION
2304 WHEN OTHERS THEN
2305 errbuf := 'unknown error'||sqlerrm(sqlcode);
2306 retcode := 1;
2307 END execute_plan_worklists;
2308
2309 END MSC_PQ_UTILS;