[Home] [Help]
PACKAGE BODY: APPS.MSC_PQ_UTILS
Source
1 PACKAGE BODY MSC_PQ_UTILS AS
2 /* $Header: MSCPQUTB.pls 120.18.12020000.2 2012/10/19 09:07:46 skathera ship $ */
3
4 G_PQ_ERROR_MESSAGE VARCHAR2(2000);
5 g_delimiter2 VARCHAR2(10) := fnd_global.local_chr(10);
6 g_delimiter1 VARCHAR2(10) := fnd_global.local_chr(9);
7 g_delimiter VARCHAR2(10) := 'chr(9)';
8
9 g_among_values among_values_tab;
10 g_CATEGORY_SET_ID NUMBER;
11 g_query_id NUMBER;
12 g_obj_sequence_id NUMBER;
13 g_sequence_id NUMBER;
14
15 g_items_list_exists NUMBER;
16
17 FUNCTION validate_index_use(p_query_id IN NUMBER,
18 p_query_type IN NUMBER) RETURN NUMBER;
19
20 PROCEDURE set_top_action(p_plan_id IN NUMBER, p_query_id IN NUMBER);
21
22 CURSOR detailQCur(p_query_id IN NUMBER) IS
23 SELECT pqt.DETAIL_QUERY_ID query_id,mpq.query_type
24 FROM MSC_PQ_TYPES pqt,
25 msc_personal_queries mpq
26 WHERE pqt.query_id = p_query_id
27 AND mpq.query_id = pqt.DETAIL_QUERY_ID;
28
29 CURSOR WlExcepCur(p_query_id IN NUMBER) IS
30 SELECT 1
31 FROM MSC_PQ_TYPES pqt
32 WHERE pqt.query_id = p_query_id
33 AND pqt.DETAIL_QUERY_ID IS NULL;
34
35 FUNCTION Get_Pref(p_plan_id NUMBER, p_preference in varchar2) RETURN NUMBER is
36 l_pref_value number;
37 l_def_pref_id number;
38 l_plan_type number;
39
40 CURSOR c_plan_type(v_plan_id NUMBER) IS
41 SELECT curr_plan_type
42 FROM msc_plans
43 WHERE plan_id = v_plan_id;
44 BEGIN
45 OPEN c_plan_type(p_plan_id);
46 FETCH c_plan_type INTO l_plan_type;
47 CLOSE c_plan_type;
48 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id,l_plan_type);
49 l_pref_value:= msc_get_name.get_preference(p_preference,l_def_pref_id, l_plan_type);
50 RETURN l_pref_value;
51
52 END Get_Pref;
53
54 PROCEDURE Parse_exceptions(p_plan_id IN NUMBER,
55 p_where_clause IN VARCHAR2) IS
56
57 TYPE excepTyp IS REF CURSOR;
58 c_exceptions excepTyp;
59
60 TYPE excepRecTyp IS RECORD ( exception_id msc_exception_details_v.exception_id%type,
61 sr_instance_Id msc_exception_details_v.sr_instance_Id%type,
62 organization_id msc_exception_details_v.organization_id%type,
63 inventory_item_id msc_exception_details_v.inventory_item_id%type,
64 supplier_id msc_exception_details_v.supplier_id%type,
65 supplier_site_id msc_exception_details_v.supplier_site_id%type,
66 transaction_id msc_exception_details_v.transaction_id%type,
67 demand_id msc_exception_details_v.demand_id%type,
68 exception_type msc_exception_details_v.exception_type%type,
69 from_date msc_exception_details_v.from_date%type,
70 to_date msc_exception_details_v.to_date%type,
71 budget_violation_date msc_exception_details_v.budget_violation_date%type,
72 department_id msc_exception_details_v.department_id%type,
73 resource_id msc_exception_details_v.resource_id%type,
74 end_pegging_id msc_exception_details_v.end_pegging_id%type,
75 operation_seq_num msc_exception_details_v.operation_seq_num%type,
76 resource_seq_num msc_exception_details_v.resource_seq_num%type);
77 rec_exceptions excepRecTyp;
78 TYPE numList IS TABLE Of NUMBER Index By BINARY_INTEGER;
79 l_list numList;
80 l_item_list numList;
81
82 /*CURSOR C_supply_demand(p_plan_id IN NUMBER,
83 p_instance_id IN NUMBER,
84 p_organization_id IN NUMBER,
85 p_inventory_item_id IN NUMBER,
86 p_from_date IN DATE,
87 p_to_date IN DATE ) IS
88 SELECT transaction_id
89 FROM msc_orders_v
90 WHERE plan_id = p_plan_id
91 AND sr_instance_id = p_instance_id
92 AND organization_id = p_organization_id
93 AND inventory_item_id = p_inventory_item_id
94 AND trunc(new_due_date) >= trunc(p_from_date)
95 AND trunc(new_due_date) < trunc(p_to_date);*/
96
97 CURSOR C_supply_demand1(p_plan_id IN NUMBER,
98 p_instance_id IN NUMBER,
99 p_organization_id IN NUMBER,
100 p_inventory_item_id IN NUMBER,
101 p_from_date IN DATE,
102 p_to_date IN DATE ) IS
103 SELECT sup.transaction_id
104 --,sup.new_schedule_date new_due_date
105 FROM msc_supplies sup,
106 msc_system_items msi ,
107 msc_item_categories mic
108 WHERE mic.sr_instance_id = sup.sr_instance_id
109 AND mic.organization_id = sup.organization_id
110 AND mic.inventory_item_id = sup.inventory_item_id
111 AND
112 sup.plan_id = msi.plan_id
113 AND sup.sr_instance_id = msi.sr_instance_id
114 AND sup.organization_id = msi.organization_id
115 AND sup.inventory_item_id = msi.inventory_item_id
116 AND
117 sup.plan_id = p_plan_id
118 AND sup.sr_instance_id = p_instance_id
119 AND sup.organization_id = p_organization_id
120 AND sup.inventory_item_id = p_inventory_item_id
121 AND sup.new_schedule_date >= p_from_date
122 AND sup.new_schedule_date < p_to_date;
123 --AND trunc(sup.new_schedule_date) >= p_from_date
124 --AND trunc(sup.new_schedule_date) < p_to_date;
125
126 CURSOR C_supply_demand2(p_plan_id IN NUMBER,
127 p_instance_id IN NUMBER,
128 p_organization_id IN NUMBER,
129 p_inventory_item_id IN NUMBER,
130 p_from_date IN DATE,
131 p_to_date IN DATE ) IS
132 SELECT dem.demand_id transaction_id
133 --,dem.using_assembly_demand_date new_due_date
134 FROM msc_demands dem,
135 msc_system_items msi,
136 msc_item_categories mic
137 WHERE mic.sr_instance_id = dem.sr_instance_id
138 AND mic.organization_id = dem.organization_id
139 AND mic.inventory_item_id = dem.inventory_item_id
140 AND
141 dem.plan_id = msi.plan_id
142 AND dem.sr_instance_id = msi.sr_instance_id
143 AND dem.organization_id = msi.organization_id
144 AND dem.inventory_item_id = msi.inventory_item_id
145 AND dem.origination_type <> 52
146 AND
147 dem.plan_id = p_plan_id
148 AND dem.sr_instance_id = p_instance_id
149 AND dem.organization_id = p_organization_id
150 AND dem.inventory_item_id = p_inventory_item_id
151 AND dem.using_assembly_demand_date >= p_from_date
152 AND dem.using_assembly_demand_date < p_to_date;
153 --AND trunc(dem.using_assembly_demand_date) >= p_from_date
154 --AND trunc(dem.using_assembly_demand_date) < p_to_date;
155
156 CURSOR C_supply_demand3(p_plan_id IN NUMBER,
157 p_instance_id IN NUMBER,
158 p_organization_id IN NUMBER,
159 p_inventory_item_id IN NUMBER,
160 p_from_date IN DATE,
161 p_to_date IN DATE ) IS
162 SELECT mso.demand_id transaction_id
163 --,mso.requirement_date new_due_date
164 FROM msc_sales_orders mso,
165 msc_system_items msi ,
166 msc_item_categories mic
167 WHERE mso.sr_instance_id = mic.sr_instance_id
168 AND mso.inventory_item_id = mic.inventory_item_id
169 AND mso.organization_id = mic.organization_id
170 AND
171 mso.sr_instance_id = msi.sr_instance_id
172 AND mso.inventory_item_id = msi.inventory_item_id
173 AND mso.organization_id = msi.organization_id
174 AND mso.reservation_type = 1
175 --AND msi.plan_id = -1
176 AND
177 msi.plan_id = p_plan_id
178 AND mso.sr_instance_id = p_instance_id
179 AND mso.organization_id = p_organization_id
180 AND mso.inventory_item_id = p_inventory_item_id
181 AND mso.requirement_date >= p_from_date
182 AND mso.requirement_date < p_to_date;
183 --AND trunc(mso.requirement_date) >= p_from_date
184 --AND trunc(mso.requirement_date) < p_to_date;
185
186 CURSOR C_supply_demand4(p_plan_id IN NUMBER,
187 p_instance_id IN NUMBER,
188 p_organization_id IN NUMBER,
189 p_inventory_item_id IN NUMBER,
190 p_from_date IN DATE,
191 p_to_date IN DATE ) IS
192 SELECT jro.transaction_id
193 --,jro.reco_date_required new_due_date
194 FROM msc_job_requirement_ops jro,
195 msc_system_items msi ,
196 msc_item_categories mic
197 WHERE mic.sr_instance_id = jro.sr_instance_id
198 AND mic.organization_id = jro.organization_id
199 AND mic.inventory_item_id = jro.component_item_id
200 --AND jro.plan_id = -1
201 AND jro.plan_id = msi.plan_id
202 AND jro.sr_instance_id = msi.sr_instance_id
203 AND jro.organization_id = msi.organization_id
204 AND jro.component_item_id = msi.inventory_item_id
205 AND
206 jro.plan_id = p_plan_id
207 AND jro.sr_instance_id = p_instance_id
208 AND jro.organization_id = p_organization_id
209 AND jro.component_item_id = p_inventory_item_id
210 AND jro.reco_date_required >= p_from_date
211 AND jro.reco_date_required < p_to_date;
212 --AND trunc(jro.reco_date_required) >= p_from_date
213 --AND trunc(jro.reco_date_required) < p_to_date;
214
215 CURSOR C_supply_demand5(p_plan_id IN NUMBER,
216 p_instance_id IN NUMBER,
217 p_organization_id IN NUMBER,
218 p_inventory_item_id IN NUMBER,
219 p_from_date IN DATE,
220 p_to_date IN DATE ) IS
221 SELECT sup.transaction_id
222 --,nvl(sup.new_ship_date,sup.new_schedule_date) new_due_date
223 FROM msc_supplies sup,
224 msc_system_items msi ,
225 msc_item_categories mic,
226 msc_plans mp
227 WHERE mic.sr_instance_id = sup.sr_instance_id
228 AND mic.organization_id = sup.organization_id
229 AND mic.inventory_item_id = sup.inventory_item_id
230 AND
231 sup.plan_id = msi.plan_id
232 AND sup.sr_instance_id = msi.sr_instance_id
233 AND sup.organization_id = msi.organization_id
234 AND sup.inventory_item_id = msi.inventory_item_id
235 AND mp.plan_id = sup.plan_id
236 AND mp.plan_type = 5
237 AND sup.order_type = 51
238 AND
239 sup.plan_id = p_plan_id
240 AND sup.sr_instance_id = p_instance_id
241 AND sup.organization_id = p_organization_id
242 AND sup.inventory_item_id = p_inventory_item_id
243 AND nvl(sup.new_ship_date,sup.new_schedule_date) >= p_from_date
244 AND nvl(sup.new_ship_date,sup.new_schedule_date) < p_to_date;
245 --AND trunc(nvl(sup.new_ship_date,sup.new_schedule_date)) >= p_from_date
246 --AND trunc(nvl(sup.new_ship_date,sup.new_schedule_date)) < p_to_date;
247
248 CURSOR C_supply_demand6(p_plan_id IN NUMBER,
249 p_instance_id IN NUMBER,
250 p_organization_id IN NUMBER,
251 p_inventory_item_id IN NUMBER,
252 p_from_date IN DATE,
253 p_to_date IN DATE ) IS
254 SELECT dem.demand_id transaction_id
255 --,nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date) new_due_date
256 FROM msc_demands dem,
257 msc_plans mp,
258 msc_system_items msi ,
259 msc_item_categories mic
260 WHERE mic.sr_instance_id = dem.sr_instance_id
261 AND mic.organization_id = dem.organization_id
262 AND mic.inventory_item_id = dem.inventory_item_id
263 AND
264 dem.plan_id = msi.plan_id
265 AND dem.sr_instance_id = msi.sr_instance_id
266 AND dem.organization_id = msi.organization_id
267 AND dem.inventory_item_id = msi.inventory_item_id
268 AND mp.plan_id = dem.plan_id
269 AND mp.plan_type = 5
270 AND (( dem.origination_type = 1
271 AND dem.source_organization_id <> dem.organization_id )
272 OR
273 ( dem.origination_type = 30
274 AND dem.demand_source_type = 8 ))
275 AND
276 dem.plan_id = p_plan_id
277 AND dem.sr_instance_id = p_instance_id
278 AND dem.organization_id = p_organization_id
279 AND dem.inventory_item_id = p_inventory_item_id
280 AND nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date) >= p_from_date
281 AND nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date) < p_to_date;
282 --AND trunc(nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date)) >= p_from_date
283 --AND trunc(nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date)) < p_to_date;
284
285 CURSOR C_supply_demand7(p_plan_id IN NUMBER,
286 p_instance_id IN NUMBER,
287 p_organization_id IN NUMBER,
288 p_inventory_item_id IN NUMBER,
289 p_from_date IN DATE,
290 p_to_date IN DATE ) IS
291 SELECT dem.demand_id transaction_id
292 --,nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date) new_due_date
293 FROM msc_demands dem,
294 msc_plans mp,
295 msc_system_items msi ,
296 msc_item_categories mic
297 WHERE mic.sr_instance_id = dem.sr_instance_id
298 AND mic.organization_id = dem.organization_id
299 AND mic.inventory_item_id = dem.inventory_item_id
300 AND
301 dem.plan_id = msi.plan_id
302 AND dem.sr_instance_id = msi.sr_instance_id
303 AND dem.organization_id = msi.organization_id
304 AND dem.inventory_item_id = msi.inventory_item_id
305 AND mp.plan_id = dem.plan_id
306 AND mp.plan_type = 5
307 AND dem.origination_type = 30
308 AND dem.demand_source_type = 8
309 AND
310 dem.plan_id = p_plan_id
311 AND dem.sr_instance_id = p_instance_id
312 AND dem.organization_id = p_organization_id
313 AND dem.inventory_item_id = p_inventory_item_id
314 AND nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date) >= p_from_date
315 AND nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date) < p_to_date;
316 --AND trunc(nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date)) >= p_from_date
317 --AND trunc(nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date)) < p_to_date;
318
319 CURSOR c_supp_cap_overload_exception(p_plan_id NUMBER ,
320 p_sr_instance_id NUMBER,
321 p_supplier_id NUMBER,
322 p_supplier_site_id NUMBER,
323 p_inventory_item_id NUMBER,
324 p_consumption_date DATE) is
325 SELECT distinct supply_id
326 FROM msc_supplier_requirements
327 WHERE plan_id = p_plan_id
328 AND sr_instance_id = p_sr_instance_id
329 AND supplier_id = p_supplier_id
330 AND supplier_site_id = p_supplier_site_id
331 AND inventory_item_id = p_inventory_item_id
332 AND trunc(consumption_date) = trunc(p_consumption_date);
333 l_sql_stmt VARCHAR2(32000);
334
335 CURSOR c_sd_49(p_plan_id NUMBER,
336 p_sr_instance_id NUMBER,
337 p_demand_id NUMBER) IS
338 SELECT demand_id
339 FROM msc_demands
340 WHERE plan_id = p_plan_id
341 AND sr_instance_id = p_sr_instance_id
342 AND group_id IN (SELECT group_id
343 FROM msc_demands
344 WHERE plan_id = p_plan_id
345 AND sr_instance_id = p_sr_instance_id
346 AND demand_id = p_demand_id);
347
348 CURSOR c_demand_84 (p_plan_id NUMBER,
349 p_excp_id NUMBER) IS
350 SELECT md.demand_id
351 FROM msc_exception_details med,
352 msc_demands md
353 WHERE med.plan_id = md.plan_id
354 AND med.exception_type = 84
355 AND med.exception_detail_id = p_excp_id
356 AND med.plan_id = p_plan_id
357 AND ( med.number1= md.demand_id
358 OR med.number1 = md.original_demand_id);
359
360 CURSOR RES_TRANS_C(p_plan_id NUMBER,
361 p_inst_id NUMBER,
362 p_org_id NUMBER,
363 p_dept_id NUMBER,
364 p_res_id NUMBER) IS
365 SELECT supply_id
366 FROM msc_resource_requirements
367 WHERE plan_id = p_plan_id
368 AND sr_instance_id = p_inst_id
369 AND organization_id = p_org_id
370 AND department_id = p_dept_id
371 AND resource_id = p_res_id;
372
373 CURSOR RES_TRANS_C1(p_plan_id NUMBER,
374 p_inst_id NUMBER,
375 p_org_id NUMBER,
376 p_dept_id NUMBER,
377 p_res_id NUMBER,
378 p_supply_id NUMBER,
379 p_op_seq NUMBER,
380 p_res_seq NUMBER) IS
381 SELECT supply_id,transaction_id
382 FROM msc_resource_requirements
383 WHERE plan_id = p_plan_id
384 AND sr_instance_id = p_inst_id
385 AND organization_id = p_org_id
386 AND department_id = p_dept_id
387 AND resource_id = p_res_id
388 AND supply_id = p_supply_id
389 AND nvl(operation_seq_num,-1) = nvl(p_op_seq, nvl(operation_seq_num,-1))
390 AND nvl(resource_seq_num,-1) = nvl(p_res_seq, nvl(resource_seq_num,-1))
391 AND parent_id = 2;
392
393 CURSOR RES_TRANS_C2(p_plan_id NUMBER,
394 p_inst_id NUMBER,
395 p_org_id NUMBER,
396 p_dept_id NUMBER,
397 p_res_id NUMBER,
398 p_from_date DATE,
399 p_to_date DATE) IS
400 SELECT supply_id
401 FROM msc_resource_requirements
402 WHERE plan_id = p_plan_id
403 AND sr_instance_id = p_inst_id
404 AND organization_id = p_org_id
405 AND department_id = p_dept_id
406 AND resource_id = p_res_id
407 AND ( ( trunc(start_date) >= p_from_date
408 AND NVL(trunc(end_date),p_to_date) <= p_to_date)
409 OR ( p_from_date BETWEEN trunc(start_date)
410 AND NVL(trunc(end_date),p_to_date))
411 OR ( p_to_date BETWEEN trunc(start_date) AND NVL(trunc(end_date),p_to_date))
412 OR ( trunc(start_date) <= p_from_date
413 AND NVL(trunc(end_date),p_to_date) >= p_to_date) );
414
415 CURSOR RES_TRANS_C3(p_plan_id NUMBER,
416 p_inst_id NUMBER,
417 p_org_id NUMBER,
418 p_dept_id NUMBER,
419 p_res_id NUMBER,
420 p_from_date DATE,
421 p_to_date DATE) IS
422 SELECT supply_id
423 FROM msc_resource_requirements r,
424 msc_supplies s,
425 msc_system_items i
426 WHERE r.plan_id = p_plan_id
427 AND r.sr_instance_id = p_inst_id
428 AND r.organization_id = p_org_id
429 AND r.department_id = p_dept_id
430 AND r.resource_id = p_res_id
431 AND s.plan_id = r.plan_id
432 AND s.transaction_id = r.supply_id
433 AND s.plan_id = i.plan_id
434 AND s.sr_instance_id = i.sr_instance_id
435 AND s.organization_id = i.organization_id
436 AND s.inventory_item_id = i.inventory_item_id
437 AND trunc(s.need_by_date - (i.fixed_lead_time + (i.variable_lead_time*s.new_order_quantity)))
438 BETWEEN p_from_date AND p_to_date;
439
440 CURSOR PEG_TRANS_C(p_plan_id NUMBER,
441 p_end_peg_id NUMBER) IS
442 SELECT transaction_id
443 FROM msc_full_pegging
444 WHERE plan_id = p_plan_id
445 AND end_pegging_id = p_end_peg_id;
446
447 CURSOR get_bucket_dates(p_plan_id NUMBER,
448 p_date DATE) IS
449 SELECT trunc(bkt_start_date), trunc(bkt_end_date)
450 FROM msc_plan_buckets
451 WHERE plan_id = p_plan_id
452 AND p_date between bkt_start_date and bkt_end_date;
453
454 l_transaction_id NUMBER;
455
456 BEGIN
457 l_sql_stmt := ' SELECT exception_id , sr_instance_Id, organization_id, '||
458 ' inventory_item_id, supplier_id , supplier_site_id, '||
459 ' transaction_id , demand_id , exception_type, '||
460 ' from_date , to_date, budget_violation_date, '||
461 ' department_id , resource_id, end_pegging_id, '||
462 ' operation_seq_num, resource_seq_num '||
463 ' FROM msc_exception_details_v med '||
464 ' WHERE med.plan_id = :plan_id '||
465 ' AND nvl(med.category_set_id,2) = :category_set_id ';
466
467 l_sql_stmt := l_sql_stmt ||' AND '|| p_where_clause;
468 --KSA_DEBUG(SYSDATE,'l_sql_stmt ...'||l_sql_stmt,'Parse_exceptions');
469 IF g_category_set_id IS NULL THEN
470 g_category_set_id := Get_Pref(p_plan_id, 'CATEGORY_SET_ID');
471 END IF;
472 --KSA_DEBUG(SYSDATE,'p_plan_id ...'||p_plan_id||'and g_category_set_id...'||g_category_set_id,'Parse_exceptions');
473 OPEN c_exceptions for l_sql_stmt using p_plan_id, g_category_set_id;
474 LOOP
475 FETCH c_exceptions INTO rec_exceptions;
476 EXIT WHEN c_exceptions%NOTFOUND;
477 IF rec_exceptions.exception_type =20 THEN
478 l_item_list(l_item_list.count()+1) := rec_exceptions.inventory_item_id;
479 END IF;
480 IF rec_exceptions.exception_type =28 THEN
481 FOR rec_supp_cap_overload_excp IN c_supp_cap_overload_exception
482 (p_plan_id ,
483 rec_exceptions.sr_instance_id,
484 rec_exceptions.supplier_id,
485 rec_exceptions.supplier_site_id,
486 rec_exceptions.inventory_item_id,
487 rec_exceptions.from_date) LOOP
488 --populate_temp_table(rec_supp_cap_overload_excp.supply_id);
489 l_list(l_list.count()+1) := rec_supp_cap_overload_excp.supply_id;
490 END LOOP;
491 /*ELSIF rec_exceptions.exception_type in (52,49,84,85,86,87,88,89,90,92,93) THEN
492 NULL;
493 ELSIF (rec_exceptions.exception_type <> 48) AND
494 (rec_exceptions.transaction_id IS NOT NULL
495 OR (rec_exceptions.department_id IS NOT NULL
496 AND rec_exceptions.resource_id IS NOT NULL)) THEN
497 mrp_exception_details.g_resource_req_rows_selected := mrp_exception_details.g_resource_req_rows_selected + 1;
498 */
499 END IF;
500
501 IF rec_exceptions.exception_type = 49 THEN
502 OPEN c_sd_49(p_plan_id,
503 rec_exceptions.sr_instance_id,
504 rec_exceptions.demand_id);
505 LOOP
506 FETCH c_sd_49 into l_transaction_id;
507 EXIT WHEN C_sd_49%NOTFOUND;
508 --supply/demand
509 --populate_temp_table(l_transaction_id);
510 l_list(l_list.count()+1) := l_transaction_id;
511 END LOOP;
512 CLOSE c_sd_49;
513 ELSIF rec_exceptions.exception_type = 84 then
514 DECLARE
515 l_temp number;
516 BEGIN
517 OPEN c_demand_84(p_plan_id,
518 rec_exceptions.exception_id );
519 FETCH c_demand_84 into l_temp;
520 CLOSE c_demand_84;
521
522 --populate_temp_table(l_temp);
523 l_list(l_list.count()+1) := l_temp;
524 END;
525 ELSIF rec_exceptions.exception_type = 85 then
526 DECLARE
527 l_from_date DATE;
528 l_to_date DATE;
529 BEGIN
530 OPEN get_bucket_dates(p_plan_id,
531 rec_exceptions.budget_violation_date);
532 FETCH get_bucket_dates INTO l_from_date, l_to_date;
533 CLOSE get_bucket_dates;
534
535 OPEN C_supply_demand1(p_plan_id,
536 rec_exceptions.sr_instance_id,
537 rec_exceptions.organization_id,
538 rec_exceptions.inventory_item_id,
539 l_from_date,
540 l_to_date );
541
542 LOOP
543 FETCH C_supply_demand1 into l_transaction_id;
544 EXIT WHEN C_supply_demand1%NOTFOUND;
545 --populate_temp_table(l_transaction_id);
546 l_list(l_list.count()+1) := l_transaction_id;
547 END LOOP;
548 CLOSE C_supply_demand1;
549
550 OPEN C_supply_demand2(p_plan_id,
551 rec_exceptions.sr_instance_id,
552 rec_exceptions.organization_id,
553 rec_exceptions.inventory_item_id,
554 l_from_date,
555 l_to_date );
556
557 LOOP
558 FETCH C_supply_demand2 into l_transaction_id;
559 EXIT WHEN C_supply_demand2%NOTFOUND;
560 --populate_temp_table(l_transaction_id);
561 l_list(l_list.count()+1) := l_transaction_id;
562 END LOOP;
563 CLOSE C_supply_demand2;
564
565 IF p_plan_id = -1 THEN
566 OPEN C_supply_demand3(p_plan_id,
567 rec_exceptions.sr_instance_id,
568 rec_exceptions.organization_id,
569 rec_exceptions.inventory_item_id,
570 l_from_date,
571 l_to_date );
572
573 LOOP
574 FETCH C_supply_demand3 into l_transaction_id;
575 EXIT WHEN C_supply_demand3%NOTFOUND;
576 --populate_temp_table(l_transaction_id);
577 l_list(l_list.count()+1) := l_transaction_id;
578 END LOOP;
579 CLOSE C_supply_demand3;
580
581 OPEN C_supply_demand4(p_plan_id,
582 rec_exceptions.sr_instance_id,
583 rec_exceptions.organization_id,
584 rec_exceptions.inventory_item_id,
585 l_from_date,
586 l_to_date );
587
588 LOOP
589 FETCH C_supply_demand4 into l_transaction_id;
590 EXIT WHEN C_supply_demand4%NOTFOUND;
591 --populate_temp_table(l_transaction_id);
592 l_list(l_list.count()+1) := l_transaction_id;
593 END LOOP;
594 CLOSE C_supply_demand4;
595 END IF;
596
597 OPEN C_supply_demand5(p_plan_id,
598 rec_exceptions.sr_instance_id,
599 rec_exceptions.organization_id,
600 rec_exceptions.inventory_item_id,
601 l_from_date,
602 l_to_date );
603
604 LOOP
605 FETCH C_supply_demand5 into l_transaction_id;
606 EXIT WHEN C_supply_demand5%NOTFOUND;
607 --populate_temp_table(l_transaction_id);
608 l_list(l_list.count()+1) := l_transaction_id;
609 END LOOP;
610 CLOSE C_supply_demand5;
611
612 OPEN C_supply_demand6(p_plan_id,
613 rec_exceptions.sr_instance_id,
614 rec_exceptions.organization_id,
615 rec_exceptions.inventory_item_id,
616 l_from_date,
617 l_to_date );
618
619 LOOP
620 FETCH C_supply_demand6 into l_transaction_id;
621 EXIT WHEN C_supply_demand6%NOTFOUND;
622 --populate_temp_table(l_transaction_id);
623 l_list(l_list.count()+1) := l_transaction_id;
624 END LOOP;
625 CLOSE C_supply_demand6;
626
627 OPEN C_supply_demand7(p_plan_id,
628 rec_exceptions.sr_instance_id,
629 rec_exceptions.organization_id,
630 rec_exceptions.inventory_item_id,
631 l_from_date,
632 l_to_date );
633
634 LOOP
635 FETCH C_supply_demand7 into l_transaction_id;
636 EXIT WHEN C_supply_demand7%NOTFOUND;
637 --populate_temp_table(l_transaction_id);
638 l_list(l_list.count()+1) := l_transaction_id;
639 END LOOP;
640 CLOSE C_supply_demand7;
641
642 END;
643 ELSIF rec_exceptions.demand_id IS NOT NULL
644 AND rec_exceptions.end_pegging_id IS NOT NULL THEN
645 OPEN PEG_TRANS_C(p_plan_id,
646 rec_exceptions.end_pegging_id);
647 LOOP
648 FETCH PEG_TRANS_C INTO l_transaction_id;
649 EXIT WHEN PEG_TRANS_C%NOTFOUND;
650 --populate_temp_table(l_transaction_id);
651 l_list(l_list.count()+1) := l_transaction_id;
652 END LOOP;
653 CLOSE PEG_TRANS_C;
654 --populate_temp_table(rec_exceptions.demand_id);
655 l_list(l_list.count()+1) := rec_exceptions.demand_id;
656 --KSA_DEBUG(SYSDATE,'demand_id and end_pegging_id are not null...2','Parse_exceptions');
657 ELSIF rec_exceptions.resource_id IS NOT NULL THEN
658 IF rec_exceptions.exception_type = 36 THEN
659 OPEN RES_TRANS_C3(p_plan_id,
660 rec_exceptions.sr_instance_id,
661 rec_exceptions.organization_id,
662 rec_exceptions.department_id,
663 rec_exceptions.resource_id,
664 rec_exceptions.from_date,
665 rec_exceptions.to_date);
666 LOOP
667 FETCH RES_TRANS_C3 INTO l_transaction_id;
668 EXIT WHEN RES_TRANS_C3%NOTFOUND;
669 --populate_temp_table(l_transaction_id);
670 l_list(l_list.count()+1) := l_transaction_id;
671 --KSA_DEBUG(SYSDATE,'resource_idis not nulland ex typ 36...','Parse_exceptions');
672 END LOOP;
673 CLOSE RES_TRANS_C3;
674 ELSIF rec_exceptions.transaction_id IS NOT NULL THEN
675 DECLARE
676 l_res_transaction_id NUMBER;
677 BEGIN
678 OPEN RES_TRANS_C1(p_plan_id,
679 rec_exceptions.sr_instance_id,
680 rec_exceptions.organization_id,
681 rec_exceptions.department_id,
682 rec_exceptions.resource_id,
683 rec_exceptions.transaction_id,
684 rec_exceptions.operation_seq_num,
685 rec_exceptions.resource_seq_num);
686 LOOP
687 FETCH RES_TRANS_C1 INTO l_transaction_id, l_res_transaction_id;
688 EXIT WHEN RES_TRANS_C1%NOTFOUND;
689
690 --populate_temp_table(l_transaction_id);
691 l_list(l_list.count()+1) := l_transaction_id;
692 --populate_temp_table(l_res_transaction_id);
693 l_list(l_list.count()+1) := l_res_transaction_id;
694 --KSA_DEBUG(SYSDATE,'resource_idis not null and tr_id is not null...','Parse_exceptions');
695 END LOOP;
696 CLOSE RES_TRANS_C1;
697 END;
698 ELSIF rec_exceptions.from_date IS NOT NULL
699 AND rec_exceptions.to_date IS NOT NULL THEN
700
701 OPEN RES_TRANS_C2(p_plan_id,
702 rec_exceptions.sr_instance_id,
703 rec_exceptions.organization_id,
704 rec_exceptions.department_id,
705 rec_exceptions.resource_id,
706 rec_exceptions.from_date,
707 rec_exceptions.to_date);
708 LOOP
709 FETCH RES_TRANS_C2 INTO l_transaction_id;
710 EXIT WHEN RES_TRANS_C2%NOTFOUND;
711 --populate_temp_table(l_transaction_id);
712 l_list(l_list.count()+1) := l_transaction_id;
713 --KSA_DEBUG(SYSDATE,'resource_idis not null and from and to dates are not null...','Parse_exceptions');
714 END LOOP;
715 CLOSE RES_TRANS_C2;
716 ELSE
717 OPEN RES_TRANS_C(p_plan_id,
718 rec_exceptions.sr_instance_id,
719 rec_exceptions.organization_id,
720 rec_exceptions.department_id,
721 rec_exceptions.resource_id);
722
723 LOOP
724 FETCH RES_TRANS_C INTO l_transaction_id;
725 EXIT WHEN RES_TRANS_C%NOTFOUND;
726 --populate_temp_table(l_transaction_id);
727 l_list(l_list.count()+1) := l_transaction_id;
728 END LOOP;
729 CLOSE RES_TRANS_C;
730 END IF;
731 END IF;
732
733 IF rec_exceptions.transaction_id IS NOT NULL THEN
734 --populate_temp_table(rec_exceptions.transaction_id);
735 l_list(l_list.count()+1) := rec_exceptions.transaction_id;
736 ELSIF rec_exceptions.demand_id IS NOT NULL THEN
737 --populate_temp_table(rec_exceptions.demand_id);
738 l_list(l_list.count()+1) := rec_exceptions.demand_id;
739 END IF;
740 END LOOP;
741 DECLARE
742 v_insert_stmt VARCHAR2(2000);
743 BEGIN
744
745 FORALL i IN 1..l_list.count()
746 INSERT INTO MSC_FORM_QUERY
747 (QUERY_ID, NUMBER1, NUMBER2, NUMBER3,
748 LAST_UPDATE_DATE, LAST_UPDATED_BY ,
749 CREATION_DATE, CREATED_BY,
750 LAST_UPDATE_LOGIN )
751 VALUES (g_query_id, g_obj_sequence_id, g_sequence_id, l_list(i),
752 SYSDATE, fnd_global.user_id,
753 SYSDATE, fnd_global.user_id,
754 fnd_global.login_id);
755
756 IF l_item_list.count() > 0 THEN
757 g_items_list_exists := l_item_list.count();
758 FORALL i IN 1..l_item_list.count()
759 INSERT INTO MSC_FORM_QUERY
760 (QUERY_ID, NUMBER1, NUMBER2, NUMBER4,
761 LAST_UPDATE_DATE, LAST_UPDATED_BY ,
762 CREATION_DATE, CREATED_BY,
763 LAST_UPDATE_LOGIN )
764 VALUES (g_query_id, g_obj_sequence_id, g_sequence_id, l_item_list(i),
765 SYSDATE, fnd_global.user_id,
766 SYSDATE, fnd_global.user_id,
767 fnd_global.login_id);
768 ELSE
769 g_items_list_exists := 0;
770 END IF;
771 END;
772 EXCEPTION
773 WHEN OTHERS THEN
774 --KSA_DEBUG(SYSDATE,'Error: '||sqlerrm(sqlcode),'Parse_exceptions');
775 RAISE;
776 END Parse_exceptions;
777
778 -- for criticality matrix , this function returns where clause
779 -- for a specific category_id ( msc_pq_types.object_type)
780 FUNCTION build_where_clause_new(p_query_id IN NUMBER DEFAULT NULL,
781 p_source_type IN NUMBER DEFAULT NULL,
782 P_object_type IN NUMBER DEFAULT NULL)
783 RETURN VARCHAR2 IS
784 CURSOR c_excp_criteria (p_object_type NUMBER) IS
785 SELECT field_name,
786 field_type,
787 condition,
788 low_value,
789 high_value,
790 hidden_from_field,
791 data_set,
792 source_type,
793 object_type,
794 lov_type,
795 sequence,
796 object_sequence_id
797 FROM msc_selection_criteria_v
798 WHERE folder_id = p_query_id
799 AND active_flag = 1
800 AND condition IS NOT NULL
801 AND source_type = p_source_type
802 AND object_type = p_object_type;
803
804 CURSOR c_and_or (p_object_type NUMBER) IS
805 SELECT COUNT(*)
806 FROM msc_pq_types
807 WHERE query_id = p_query_id
808 AND source_type = p_source_type
809 AND object_type = p_object_type
810 AND NVL(and_or_flag,1) = 1;
811
812
813 l_row_count NUMBER ;
814 l_criticality_where VARCHAR2(100);
815
816 l_where_clause_segment VARCHAR2(2000);
817 l_where2_clause_segment VARCHAR2(2000);
818 where_clause_segment VARCHAR2(32000);
819
820 l_field_name VARCHAR2(50);
821 l_data_set VARCHAR2(50);
822 l_data_type VARCHAR2(50);
823 l_temp_match_str VARCHAR2(10);
824 l_and_or number;
825 l_match_str VARCHAR2(10);
826
827 begin
828 l_row_count := 0;
829 OPEN c_and_or(p_object_type);
830 FETCH c_and_or INTO l_and_or;
831 CLOSE c_and_or;
832
833 IF l_and_or = 0 THEN
834 l_match_str := ' OR ';
835 ELSE
836 l_match_str := ' AND ';
837 END IF;
838
839 FOR c_criteria_row IN c_excp_criteria(p_object_type) LOOP
840 l_row_count := l_row_count + 1;
841 IF (l_row_count = 1) THEN
842 l_temp_match_str := '';
843 ELSE
844 l_temp_match_str := l_match_str;
845 END IF;
846
847 l_field_name := c_criteria_row.field_name ;
848 l_data_set := c_criteria_row.data_set;
849 l_data_type := c_criteria_row.field_type;
850 l_where_clause_segment := get_where_clause
851 (c_criteria_row.sequence,
852 c_criteria_row.object_sequence_id,
853 l_field_name,
854 c_criteria_row.condition,
855 c_criteria_row.low_value,
856 c_criteria_row.high_value,
857 c_criteria_row.hidden_from_field,
858 l_data_set,
859 l_data_type,
860 c_criteria_row.lov_type,
861 l_temp_match_str,
862 NULL);
863 IF l_where2_clause_segment IS NULL THEN
864 l_where2_clause_segment := '( '||l_where_clause_segment||' ) ';
865 ELSE
866 l_where2_clause_segment := l_where2_clause_segment ||
867 l_temp_match_str||' ( ' ||
868 l_where_clause_segment ||' ) ';
869
870 END IF;
871 END LOOP;
872 IF where_clause_segment IS NULL THEN
873 where_clause_segment :=l_where2_clause_segment ;
874 ELSE
875 where_clause_segment := where_clause_segment ||
876 ' OR ( ' ||
877 l_where2_clause_segment ||' ) ';
878 END if;
879 l_where_clause_segment := NULL;
880 l_where2_clause_segment := NULL;
881 RETURN where_clause_segment;
882 END build_where_clause_new;
883
884 FUNCTION build_where_clause(p_query_id IN NUMBER DEFAULT NULL,
885 P_source_type IN NUMBER DEFAULT NULL)
886 RETURN VARCHAR2 IS
887
888 CURSOR c_criteria IS
889 SELECT field_name,
890 field_type,
891 condition,
892 DECODE(field_name , 'PLANNING_MAKE_BUY_CODE',
893 hidden_from_field, low_value ) low_value,
894 high_value,
895 hidden_from_field,
896 data_set,
897 source_type,
898 object_type,
899 lov_type,
900 sequence,
901 object_sequence_id
902 FROM msc_selection_criteria_v
903 WHERE folder_id = p_query_id
904 AND active_flag = 1
905 AND condition IS NOT NULL
906 ORDER BY source_type, object_type, field_name;
907
908 CURSOR c_excp_criteria (p_object_type NUMBER) IS
909 SELECT field_name,
910 field_type,
911 condition,
912 low_value,
913 high_value,
914 hidden_from_field,
915 data_set,
916 source_type,
917 object_type,
918 lov_type,
919 sequence,
920 object_sequence_id
921 FROM msc_selection_criteria_v
922 WHERE folder_id = p_query_id
923 AND active_flag = 1
924 AND condition IS NOT NULL
925 AND source_type = p_source_type
926 AND object_type = p_object_type;
927
928 CURSOR c_excp_type IS
929 SELECT DISTINCT object_type
930 FROM msc_selection_criteria_v
931 WHERE folder_id = p_query_id
932 AND active_flag = 1
933 AND source_type = p_source_type;
934
935 CURSOR c_and_or IS
936 SELECT count(*)
937 FROM msc_personal_queries
938 WHERE query_id = p_query_id
939 AND NVL(and_or_flag,1) = 1;
940
941 CURSOR c_excp_and_or (p_object_type NUMBER) IS
942 SELECT COUNT(*)
943 FROM msc_pq_types
944 WHERE query_id = p_query_id
945 AND source_type = p_source_type
946 AND object_type = p_object_type
947 AND NVL(and_or_flag,1) = 1;
948
949 l_and_or NUMBER;
950 l_match_str VARCHAR2(10);
951 l_temp_match_str VARCHAR2(10);
952 l_row_count NUMBER ;
953 l_excp_where VARCHAR2(100);
954
955 l_where_clause_segment VARCHAR2(2000);
956 l_where2_clause_segment VARCHAR2(2000);
957 where_clause_segment VARCHAR2(32000);
958
959 l_field_name VARCHAR2(50);
960 l_data_set VARCHAR2(50);
961 l_data_type VARCHAR2(50);
962
963 begin
964 l_row_count := 0;
965 IF p_source_type = 0 THEN
966 OPEN c_and_or;
967 FETCH c_and_or INTO l_and_or;
968 CLOSE c_and_or;
969 IF l_and_or = 0 THEN
970 l_match_str := ' OR ';
971 ELSE
972 l_match_str := ' AND ';
973 END if;
974
975 FOR c_criteria_row IN c_criteria LOOP
976 l_row_count := l_row_count + 1;
977 IF (l_row_count = 1) THEN
978 l_temp_match_str := '';
979 ELSE
980 l_temp_match_str := l_match_str;
981 END IF;
982 l_excp_where := '';
983 l_field_name := c_criteria_row.field_name ;
984 l_data_set := c_criteria_row.data_set;
985 l_data_type := c_criteria_row.field_type;
986 l_where_clause_segment := get_where_clause
987 (c_criteria_row.sequence,
988 c_criteria_row.object_sequence_id,
989 l_field_name,
990 c_criteria_row.condition,
991 c_criteria_row.low_value,
992 c_criteria_row.high_value,
993 c_criteria_row.hidden_from_field,
994 l_data_set,
995 l_data_type,
996 c_criteria_row.lov_type,
997 l_temp_match_str,
998 l_excp_where);
999 IF where_clause_segment IS NULL THEN
1000 where_clause_segment := ' ( '||l_where_clause_segment||' ) ';
1001 ELSE
1002 where_clause_segment := where_clause_segment||
1003 l_match_str ||
1004 ' ( '||l_where_clause_segment||' ) ';
1005 END IF;
1006 END LOOP;
1007 ELSE
1008 FOR c_excp_type_row IN c_excp_type LOOP
1009 OPEN c_excp_and_or(c_excp_type_row.object_type);
1010 FETCH c_excp_and_or INTO l_and_or;
1011 CLOSE c_excp_and_or;
1012
1013 IF l_and_or = 0 THEN
1014 l_match_str := ' OR ';
1015 ELSE
1016 l_match_str := ' AND ';
1017 END IF;
1018
1019 l_excp_where := ' ( exception_type = ' ||
1020 c_excp_type_row.object_type ||
1021 ' AND source_type = ' ||
1022 p_source_type ||
1023 ' ) AND ';
1024 FOR c_criteria_row IN c_excp_criteria(c_excp_type_row.object_type) LOOP
1025 l_row_count := l_row_count + 1;
1026 IF (l_row_count = 1) THEN
1027 l_temp_match_str := '';
1028 ELSE
1029 l_temp_match_str := l_match_str;
1030 END IF;
1031
1032 l_field_name := c_criteria_row.field_name ;
1033 l_data_set := c_criteria_row.data_set;
1034 l_data_type := c_criteria_row.field_type;
1035 l_where_clause_segment := get_where_clause
1036 (c_criteria_row.sequence,
1037 c_criteria_row.object_sequence_id,
1038 l_field_name,
1039 c_criteria_row.condition,
1040 c_criteria_row.low_value,
1041 c_criteria_row.high_value,
1042 c_criteria_row.hidden_from_field,
1043 l_data_set,
1044 l_data_type,
1045 c_criteria_row.lov_type,
1046 l_temp_match_str,
1047 NULL);
1048
1049 IF l_where2_clause_segment IS NULL THEN
1050 l_where2_clause_segment := '( '||l_where_clause_segment||' ) ';
1051 ELSE
1052 l_where2_clause_segment := l_where2_clause_segment ||
1053 l_temp_match_str||' ( ' ||
1054 l_where_clause_segment ||' ) ';
1055
1056 END IF;
1057 END LOOP;
1058
1059 IF where_clause_segment IS NULL THEN
1060 where_clause_segment := ' ( '||l_excp_where||' ( ' ||
1061 l_where2_clause_segment ||' )) ';
1062 ELSE
1063 where_clause_segment := where_clause_segment ||
1064 ' OR ( ' ||
1065 l_excp_where ||
1066 l_where2_clause_segment ||' ) ';
1067 END IF;
1068
1069 l_where_clause_segment := NULL;
1070 l_where2_clause_segment := NULL;
1071 END LOOP;
1072 END IF;
1073 RETURN where_clause_segment;
1074 END build_where_clause;
1075
1076 FUNCTION build_order_where_clause(p_query_id IN NUMBER,
1077 p_plan_id IN NUMBER)
1078 RETURN VARCHAR2 IS
1079
1080 CURSOR c_Ord_criteria(p_query_id IN NUMBER,
1081 p_object_type IN NUMBER,
1082 p_source_type IN NUMBER,
1083 p_sequence_id IN NUMBER) IS
1084 SELECT decode(field_name,'CUSTOMER_SITE','CUSTOMER_SITE_NAME',field_name) field_name,
1085 field_type,
1086 condition,
1087 low_value,
1088 high_value,
1089 hidden_from_field,
1090 data_set,
1091 source_type,
1092 object_type,
1093 lov_type,
1094 sequence,
1095 object_sequence_id
1096 FROM msc_selection_criteria_v
1097 WHERE folder_id = p_query_id
1098 AND active_flag = 1
1099 AND condition IS NOT NULL
1100 AND source_type = p_source_type
1101 AND object_type = p_object_type
1102 AND object_sequence_id = p_sequence_id;
1103
1104 CURSOR c_ord_type(p_query_id IN NUMBER) IS
1105 SELECT object_type,
1106 source_type,
1107 sequence_id,
1108 and_or_flag
1109 FROM msc_pq_types
1110 WHERE query_id = p_query_id
1111 AND active_flag = 1
1112 ORDER BY sequence_id;
1113
1114 l_match_str VARCHAR2(10);
1115 l_temp_match_str VARCHAR2(10);
1116 l_row_count NUMBER ;
1117 l_p_row_count NUMBER ;
1118
1119 l_where_clause_segment VARCHAR2(2000);
1120 l_where2_clause_segment VARCHAR2(2000);
1121
1122 where_clause_segment VARCHAR2(32000);
1123 l_excp_where_clause_segment VARCHAR2(32000);
1124
1125 l_field_name VARCHAR2(50);
1126 l_data_set VARCHAR2(50);
1127 l_data_type VARCHAR2(50);
1128 l_merge_criteria NUMBER;
1129 l_build_Excp_where NUMBER;
1130 l_criteria_row_seq NUMBER;
1131
1132 BEGIN
1133 --KSA_DEBUG(SYSDATE,'inside...','build_order_where_clause');
1134
1135 l_p_row_count := 0;
1136 FOR c_ord_type_row IN c_ord_type(p_query_id) LOOP
1137 l_p_row_count := l_p_row_count + 1;
1138
1139 l_match_str := ' OR ';
1140 l_build_Excp_where := 0;
1141 l_excp_where_clause_segment := NULL;
1142
1143 --KSA_DEBUG(SYSDATE,'Object type is '||c_ord_type_row.object_type,'build_order_where_clause');
1144 l_row_count := 0;
1145 FOR c_criteria_row IN c_Ord_criteria(p_query_id,
1146 c_ord_type_row.object_type,
1147 c_ord_type_row.source_type,
1148 c_ord_type_row.sequence_id)
1149 LOOP
1150 l_row_count := l_row_count + 1;
1151 l_merge_criteria := 1;
1152 IF l_row_count = 1 AND l_p_row_count = 1 THEN
1153 l_temp_match_str := '';
1154 ELSIF l_row_count = 1 AND l_p_row_count > 1 THEN
1155 l_temp_match_str := ' OR ';
1156 l_merge_criteria := 0;
1157 ELSE
1158 IF c_ord_type_row.and_or_flag = 2 THEN
1159 l_temp_match_str := ' OR ';
1160 l_merge_criteria := 0;
1161 ELSE
1162 l_temp_match_str := ' AND ';
1163 l_merge_criteria := 1;
1164 END IF;
1165 END IF;
1166
1167 l_field_name := c_criteria_row.field_name ;
1168 l_data_set := c_criteria_row.data_set;
1169 l_data_type := c_criteria_row.field_type;
1170 --KSA_DEBUG(SYSDATE,'l_field_name...'||l_field_name,'MSC_PQ_UTILS.build_order_where_clause');
1171 l_where_clause_segment := MSC_PQ_UTILS.get_where_clause(c_criteria_row.sequence,
1172 c_criteria_row.object_sequence_id,
1173 l_field_name,
1174 c_criteria_row.condition,
1175 c_criteria_row.low_value,
1176 c_criteria_row.high_value,
1177 c_criteria_row.hidden_from_field,
1178 l_data_set,
1179 l_data_type,
1180 c_criteria_row.lov_type,
1181 l_temp_match_str, NULL);
1182 --KSA_DEBUG(SYSDATE,'l_where_clause_segment...'||l_where_clause_segment,'MSC_PQ_UTILS.build_order_where_clause');
1183 IF l_field_name = 'EXCEPTION_TYPE' THEN
1184 IF l_merge_criteria = 1 THEN
1185 l_build_Excp_where := 1;
1186 l_criteria_row_seq := c_criteria_row.sequence;
1187 IF l_excp_where_clause_segment IS NULL THEN
1188 l_excp_where_clause_segment := '( '||l_where_clause_segment||' ) ';
1189 ELSE
1190 l_excp_where_clause_segment := l_excp_where_clause_segment||
1191 l_temp_match_str||' ( '||
1192 l_where_clause_segment||' ) ';
1193 END IF;
1194 ELSE
1195 MSC_PQ_UTILS.build_Excp_where(p_query_id,
1196 c_ord_type_row.sequence_id,
1197 c_criteria_row.sequence,
1198 p_plan_id,
1199 l_where_clause_segment,
1200 l_where_clause_segment);
1201 END IF;
1202 END IF;
1203 IF l_field_name = 'EXCEPTION_TYPE' AND l_merge_criteria = 1 THEN
1204 NULL; -- Do not merge Exceptions where clause at this point.
1205 ELSE
1206 IF l_where2_clause_segment IS NULL THEN
1207 l_where2_clause_segment := '( '||l_where_clause_segment||' ) ';
1208 ELSE
1209 l_where2_clause_segment := l_where2_clause_segment||
1210 l_temp_match_str||' ( '||
1211 l_where_clause_segment||' ) ';
1212 END IF;
1213 END IF;
1214 IF l_field_name like '%ITEM_SEGMENTS%'
1215 OR l_field_name like '%SUPPLIER%'
1216 OR l_field_name like '%CUSTOMER%' THEN
1217 --OR l_field_name like '%ORGANIZATION%' THEN
1218 IF l_excp_where_clause_segment IS NULL THEN
1219 l_excp_where_clause_segment := '( '||l_where_clause_segment||' ) ';
1220 ELSE
1221 l_excp_where_clause_segment := l_excp_where_clause_segment||
1222 l_temp_match_str||' ( '||
1223 l_where_clause_segment||' ) ';
1224 END IF;
1225 END IF;
1226 END LOOP;
1227 IF l_build_Excp_where = 1 THEN
1228 MSC_PQ_UTILS.build_Excp_where(p_query_id,
1229 c_ord_type_row.sequence_id,
1230 l_criteria_row_seq,
1231 p_plan_id,
1232 l_excp_where_clause_segment,
1233 l_where_clause_segment);
1234 l_where2_clause_segment := l_where2_clause_segment||
1235 l_temp_match_str||' ( '||
1236 l_where_clause_segment||' ) ';
1237 END IF;
1238 IF where_clause_segment IS NULL THEN
1239 where_clause_segment := ' ( '||l_where2_clause_segment||' ) ';
1240 ELSE
1241 where_clause_segment := where_clause_segment||' OR ( '||
1242 l_where2_clause_segment||' ) ';
1243 END IF;
1244
1245 l_where_clause_segment := NULL;
1246 l_where2_clause_segment := NULL;
1247 END LOOP;
1248 --KSA_DEBUG(SYSDATE,'Exiting...','build_order_where_clause');
1249 RETURN where_clause_segment;
1250 EXCEPTION
1251 WHEN OTHERS THEN
1252 --KSA_DEBUG(SYSDATE,'Error...'||sqlerrm(sqlcode),'MSC_PQ_UTILS.build_order_where_clause');
1253 RETURN (NULL);
1254 END build_order_where_clause;
1255
1256 FUNCTION get_where_clause (sequence NUMBER,
1257 obj_sequence NUMBER,
1258 field_name IN OUT NOCOPY VARCHAR2,
1259 operator NUMBER,
1260 low VARCHAR2,
1261 high VARCHAR2,
1262 hidden_from VARCHAR2,
1263 data_set IN OUT NOCOPY varchar2,
1264 data_type IN OUT NOCOPY VARCHAR2,
1265 lov_type IN NUMBER,
1266 p_match_str IN VARCHAR2,
1267 p_excp_where IN VARCHAR2)
1268 RETURN VARCHAR2 IS
1269 low_value VARCHAR2(200);
1270 high_value VARCHAR2(200);
1271 translated_op VARCHAR2(30);
1272 where_clause_segment VARCHAR2(32000);
1273 BEGIN
1274 --KSA_DEBUG(SYSDATE,'inside...','get_where_clause');
1275 IF operator IN (11, 14) THEN
1276 IF data_type IN ('MULTI','ORG') THEN
1277 IF data_type = 'ORG' THEN
1278 data_set := '('||REPLACE(data_set,':',',')||')';
1279 END IF;
1280 END IF;
1281
1282 IF p_excp_where IS NULL
1283 and data_type IN ('CHAR','DATE','NUMBER')
1284 and data_set IS NOT NULL THEN
1285 IF data_type IN ('DATE') THEN
1286 field_name := ' trunc( '||data_set||') ' ;
1287 ELSE
1288 field_name := data_set;
1289 END IF;
1290 ELSIF data_type IN ('DATE') THEN
1291 field_name := ' trunc( '||field_name||') ' ;
1292 END IF;
1293 low_value := '';
1294 ELSE
1295 --little trick to get correct field_name for exceptons
1296 IF p_excp_where IS NULL THEN
1297 if data_type IN ('CHAR','DATE','NUMBER') AND data_set IS NOT NULL THEN
1298 field_name := data_set;
1299 ELSIF data_type IN ('MULTI') THEN
1300 IF ( field_name LIKE 'DEMAND%ITEM_SEGMENTS' ) THEN
1301 field_name := 'ITEM_SEGMENTS';
1302 ELSIF ( field_name LIKE 'DEMAND%PRODUCT_FAMILY' ) THEN
1303 field_name := 'PRODUCT_FAMILY';
1304 ELSIF ( INSTR(field_name,'~') > 0 ) THEN
1305 field_name := SUBSTR(field_name, instr(field_name,'~')+1);
1306 END IF;
1307 END IF;
1308 END IF;
1309
1310 IF operator = 13 THEN
1311 low_value := RTRIM(LTRIM(low));
1312 IF ( SUBSTR(low_value, LENGTH(low_value),1) <> '%') THEN
1313 low_value := low_value||'%';
1314 END IF;
1315 low_value := ''''||REPLACE(low_value, '''', '''''') ||'''';
1316 ELSIF data_type = 'CHAR' THEN
1317 low_value := ''''||REPLACE(low, '''', '''''') ||'''';
1318 IF (operator = 9) OR (operator = 10) THEN
1319 high_value := ''''||REPLACE(high, '''', '''''') ||'''';
1320 ELSE
1321 high_value := REPLACE(high, '''', '''''');
1322 END IF;
1323 ELSIF data_type = 'DATE' THEN
1324 IF operator = 12 THEN
1325 low_value := low;
1326 high_value := high;
1327 ELSE
1328 low_value := 'fnd_date.displaydate_to_date('||''''||low||''''||')';
1329 IF (operator = 9) OR (operator = 10) THEN
1330 high_value := 'fnd_date.displaydate_to_date('||''''||high||''''||')';
1331 ELSE
1332 high_value := high;
1333 END IF;
1334 END IF;
1335 field_name := ' trunc( '||field_name||') ' ;
1336 ELSIF data_type = 'NUMBER' THEN
1337 low_value := NVL(hidden_from, low);
1338 low_value := ''''||low_value||'''';
1339
1340 high_value :=''''||high||'''';
1341 ELSIF data_type IN ('ORG','MULTI') THEN
1342 low_value := ''''||low||'''';
1343 high_value :=''''||high||'''';
1344 IF data_type IN ('MULTI') THEN
1345 IF (field_name LIKE '%PRODUCT_FAMILY') THEN
1346 field_name := 'PRODUCT_FAMILY';
1347 ELSIF (field_name LIKE '%ITEM_SEGMENTS') THEN
1348 field_name := 'ITEM_SEGMENTS';
1349 ELSIF (field_name LIKE '%ORDER_TYPE') THEN
1350 field_name := 'ORDER_TYPE';
1351 ELSIF (field_name LIKE '%EXCEPTION_TYPE') THEN
1352 field_name := 'EXCEPTION_TYPE';
1353 END IF;
1354 END IF;
1355 END IF;
1356 END IF;
1357 IF operator = 1 THEN translated_op := ' = ';
1358 ELSIF operator = 2 THEN translated_op := ' <> ';
1359 ELSIF operator = 3 THEN translated_op := ' >= ';
1360 ELSIF operator = 4 THEN translated_op := ' <= ';
1361 ELSIF operator = 5 THEN translated_op := ' > ';
1362 ELSIF operator = 6 THEN translated_op := ' < ';
1363 ELSIF operator = 7 THEN translated_op := ' IS NOT NULL ';
1364 ELSIF operator = 8 THEN translated_op := ' IS NULL ';
1365 ELSIF operator = 9 THEN translated_op := ' BETWEEN ';
1366 ELSIF operator = 10 THEN translated_op := ' NOT BETWEEN ';
1367 ELSIF operator = 11 THEN translated_op := ' IN ';
1368 ELSIF operator = 12 THEN translated_op := ' BETWEEN ';
1369 ELSIF operator = 13 THEN translated_op := ' LIKE ';
1370 ELSIF operator = 14 THEN translated_op := ' NOT IN ';-- FOR Orders query
1371 END IF;
1372 IF operator IN (12) THEN -- rolling dates
1373 IF (high_value IS NULL) THEN
1374 where_clause_segment := where_clause_segment ||
1375 field_name ||
1376 translated_op ||
1377 ' trunc(sysdate) AND trunc(sysdate) + '||
1378 low_value ;
1379 ELSE
1380 where_clause_segment := where_clause_segment ||
1381 field_name ||
1382 translated_op ||
1383 ' trunc(sysdate) + ' ||
1384 low_value ||
1385 ' AND ' ||
1386 ' trunc(sysdate) + ' ||
1387 high_value ;
1388 END IF;
1389 ELSIF operator IN (9,10) THEN -- operator is BETWEEN or OUTSIDE
1390 where_clause_segment := where_clause_segment ||
1391 field_name ||
1392 translated_op ||
1393 low_value ||
1394 ' AND ' ||
1395 high_value ;
1396 ELSIF operator IN (8,7) THEN -- operator is IS NOT NULL or IS NULL
1397 where_clause_segment := where_clause_segment ||
1398 field_name ||
1399 translated_op ;
1400 ELSIF operator IN (1,2) AND data_type IN ('ORG') THEN
1401 --if field_name = 'ORDER_TYPE_TEXT' then
1402 IF operator = 2 then
1403 where_clause_segment := where_clause_segment ||
1404 field_name ||
1405 translated_op ||
1406 low_value;
1407 /*translated_op := ' NOT IN ';
1408 where_clause_segment := where_clause_segment||
1409 '(('||replace(data_set,':',',')||')' ||
1410 translated_op ||
1411 '('||replace(hidden_from,':',',')||'))' ;*/
1412 ELSE
1413 where_clause_segment := where_clause_segment ||
1414 SUBSTR(data_set,
1415 1, INSTR(data_set,':')-1) ||
1416 translated_op ||
1417 SUBSTR(hidden_from,
1418 1,INSTR(hidden_from,':')-1)||
1419 ' AND ' ||
1420 SUBSTR(data_set, INSTR(data_set,':')+1)
1421 ||translated_op||SUBSTR(hidden_from,INSTR(hidden_from,':')+1);
1422 END IF;
1423 ELSIF operator IN (1,2) AND data_type IN ('MULTI') THEN
1424 --KSA_DEBUG(SYSDATE,'operation...'||operator,'get_where_clause');
1425 where_clause_segment := where_clause_segment||
1426 data_set ||
1427 translated_op ||
1428 hidden_from ;
1429 ELSIF operator IN (11,14) THEN -- operator is AMONG
1430 --KSA_DEBUG(SYSDATE,'where_clause_segment...'||where_clause_segment,'MSC_PQ_UTILS.get_where_clause');
1431 where_clause_segment := where_clause_segment ||
1432 get_among_where_clause (sequence, obj_sequence,translated_op, field_name,
1433 operator, low, high, hidden_from, data_set, data_type)||' ';
1434
1435 ELSIF operator = 13 THEN
1436 where_clause_segment := where_clause_segment || ' upper('||field_name||') '
1437 || translated_op|| UPPER(low_value);
1438 ELSE
1439 where_clause_segment := where_clause_segment ||
1440 field_name || translated_op||low_value ;
1441 END IF;
1442 --KSA_DEBUG(SYSDATE,'where_clause_segment...'||where_clause_segment,'get_where_clause');
1443 RETURN where_clause_segment;
1444 EXCEPTION
1445 WHEN OTHERS THEN
1446 --KSA_DEBUG(SYSDATE,'Error...'||sqlerrm(sqlcode),'MSC_PQ_UTILS.get_where_clause');
1447 RAISE;
1448 END get_where_clause;
1449
1450 PROCEDURE retrieve_values (p_folder_id number) IS
1451 --or_rg_name VARCHAR2(30) := 'SCOPE1_RG';
1452 --or_rg_id RecordGroup;
1453 --gc_id GroupColumn;
1454
1455 current_row NUMBER;
1456
1457 CURSOR among_values IS
1458 SELECT msc.folder_object,
1459 mav.sequence,
1460 mav.object_sequence,
1461 mav.field_name,
1462 DECODE(msc.field_type,
1463 'DATE', fnd_date.date_to_displaydate(
1464 fnd_date.canonical_to_date(mav.or_values)),
1465 'NUMBER', DECODE(mc.lov_type,
1466 1, TO_CHAR(
1467 fnd_number.canonical_to_number(mav.or_values)),
1468 mav.or_values),
1469 mav.or_values) or_values,
1470 mav.hidden_values
1471 FROM msc_among_values mav,
1472 msc_selection_criteria msc,
1473 msc_criteria mc
1474 WHERE mav.folder_id = p_folder_id
1475 AND msc.folder_id=mav.folder_id
1476 AND mc.folder_object =msc.folder_object
1477 AND mc.field_name = msc.field_name
1478 AND msc.sequence = mav.sequence
1479 AND nvl(msc.object_sequence_id,-1) = nvl(mav.object_sequence,-1);
1480
1481 among_values_rec among_values%ROWTYPE;
1482
1483 CURSOR c_delete IS
1484 SELECT distinct field_name
1485 FROM msc_among_values
1486 WHERE folder_id = p_folder_id;
1487
1488 l_name varchar2(50);
1489
1490 BEGIN
1491 --KSA_DEBUG(SYSDATE,'inside...p_folder_id'||p_folder_id,'MSC_PQ_UTILS.retrieve_values');
1492
1493 /*OPEN c_delete;
1494 LOOP
1495 FETCH c_delete into l_name;
1496 EXIT WHEN c_delete%notfound;
1497 delete_rows(l_name);
1498 end loop;
1499 CLOSE c_delete;*/
1500 clear_values;
1501
1502 OPEN among_values;
1503 Loop
1504 FETCH among_values INTO among_values_rec;
1505 EXIT WHEN among_values%NOTFOUND;
1506 store_values(among_values_rec.sequence,
1507 among_values_rec.object_sequence,
1508 among_values_rec.field_name,
1509 among_values_rec.or_values,
1510 among_values_rec.hidden_values);
1511 END LOOP;
1512 CLOSE among_values;
1513 --KSA_DEBUG(SYSDATE,'exiting...','MSC_PQ_UTILS.retrieve_values');
1514 END retrieve_values;
1515
1516 FUNCTION get_among_where_clause (sequence NUMBER,
1517 obj_sequence NUMBER,
1518 t_operator VARCHAR2,
1519 field_name IN OUT NOCOPY VARCHAR2,
1520 operator NUMBER,
1521 low VARCHAR2,
1522 high VARCHAR2,
1523 hidden_from VARCHAR2,
1524 data_set IN OUT NOCOPY VARCHAR2,
1525 datatype IN OUT NOCOPY VARCHAR2)
1526 RETURN VARCHAR2 IS
1527
1528 --p_or_rg_name CONSTANT VARCHAR2(30) := 'SCOPE1_RG';
1529 --or_rg_name VARCHAR2(30) := p_or_rg_name;
1530 tmp_str VARCHAR2(5);
1531 total_rows NUMBER;
1532 value_list VARCHAR2(1000);
1533 current_value VARCHAR2(155);
1534 v_one_record VARCHAR2(100);
1535 BEGIN
1536 IF operator NOT IN (11,14) THEN -- operator is not AMONG
1537 RETURN '11=11';
1538 END IF;
1539 --KSA_DEBUG(SYSDATE,'inside...field_name is '||field_name,'get_among_where_clause');
1540 value_list :=NULL;
1541 total_rows := g_among_values.count; --Get_Group_Row_Count(or_rg_name);
1542 IF total_rows <= 0 THEN
1543 RETURN '11=11';
1544 END IF;
1545
1546 FOR counter IN 1..total_rows LOOP
1547 --KSA_DEBUG(SYSDATE,'obj_sequence is '||obj_sequence||' and g_among_values('||counter||').OBJECT_SEQUENCE is '||g_among_values(counter).OBJECT_SEQUENCE,'get_among_where_clause');
1548 IF NOT g_among_values.exists(counter) THEN
1549 NULL;
1550 ELSIF sequence = g_among_values(counter).SEQUENCE AND
1551 nvl(obj_sequence,-99) = nvl(g_among_values(counter).OBJECT_SEQUENCE,-99) AND -- W/L testing for items query
1552 field_name = g_among_values(counter).FIELD_NAME AND
1553 g_among_values(counter).OR_VALUES IS NOT NULL THEN
1554 --Get_Group_Number_Cell(or_rg_name||'.SEQUENCE', counter) AND
1555 --Get_Group_Char_Cell(or_rg_name||'.OR_VALUES', counter) IS NOT NULL THEN
1556 IF datatype <> 'ORG' THEN
1557 IF datatype = 'MULTI' THEN
1558 current_value := g_among_values(counter).HIDDEN_VALUES; --Get_Group_Char_Cell(or_rg_name||'.HIDDEN_VALUES', counter);
1559 ELSE
1560 current_value := NVL(g_among_values(counter).HIDDEN_VALUES,
1561 g_among_values(counter).OR_VALUES);
1562 --NVL(Get_Group_Char_Cell(or_rg_name||'.HIDDEN_VALUES', counter),
1563 -- Get_Group_Char_Cell(or_rg_name||'.OR_VALUES', counter));
1564 IF datatype='DATE' THEN
1565 current_value :=
1566 'fnd_date.displaydate_to_date('||''''||current_value||''''||')';
1567 ELSIF datatype ='CHAR' THEN
1568 current_value :=''''||REPLACE(current_value, '''', '''''')||'''';
1569 ELSIF datatype = 'NUMBER' THEN
1570 current_value :=''''||current_value||'''';
1571 END IF;
1572 END IF;
1573 ELSE -- datatype = 'ORG'
1574 v_one_record := g_among_values(counter).HIDDEN_VALUES; --Get_Group_Char_Cell(or_rg_name||'.HIDDEN_VALUES', counter);
1575 current_value := '('||
1576 SUBSTR(v_one_record,1,INSTR(v_one_record,':')-1) ||','||
1577 SUBSTR(v_one_record,INSTR(v_one_record,':')+1)||')';
1578 END IF;
1579 value_list :=value_list || tmp_str ||current_value;
1580 tmp_str :=', ';
1581 END IF;
1582 END LOOP;
1583 --KSA_DEBUG(SYSDATE,'Exiting, value_list is '||value_list,'MSC_PQ_UTILS.get_among_where_clause');
1584 IF datatype IN ('ORG','MULTI') THEN
1585 RETURN ' (1=1 AND (' ||data_set || t_operator ||' ( '||value_list||'))) ';
1586 ELSE
1587 RETURN ' (1=1 AND (' ||field_name || t_operator ||' ( '||value_list||'))) ';
1588 END IF;
1589 END get_among_where_clause;
1590
1591 PROCEDURE build_Excp_where(p_query_id IN NUMBER,
1592 p_obj_sequence_id IN NUMBER,
1593 p_sequence_id IN NUMBER,
1594 p_plan_id IN NUMBER,
1595 p_where_clause IN VARCHAR2,
1596 p_excp_where_clause IN OUT NOCOPY VARCHAR2,
1597 p_match_str IN VARCHAR2 DEFAULT ' AND ') IS
1598 --v_excp_str VARCHAR2(1000);
1599 --v_excp_where VARCHAR2(1000);
1600 --v_insert_stmt VARCHAR2(2000);
1601 v_delete_stmt VARCHAR2(2000);
1602 l_where_clause VARCHAR2(32000);
1603 --v_sysdate := SYSDATE;
1604 type l_chartype is table of varchar2(32000) index by binary_integer;
1605 l_wc l_chartype;
1606 BEGIN
1607
1608 p_excp_where_clause := NULL;
1609
1610 g_query_id := p_query_id;
1611 g_obj_sequence_id := p_obj_sequence_id;
1612 g_sequence_id := p_sequence_id;
1613
1614
1615
1616 l_where_clause := REPLACE(REPLACE(p_where_clause,'SOURCE_SR_INSTANCE_ID','SOURCE_ORG_INSTANCE_ID'),'VENDOR_ID','SUPPLIER_ID');
1617 --KSA_DEBUG(SYSDATE,'l_where_clause_segment...'||p_where_clause,'MSC_PQ_UTILS.build_Excp_where');
1618 /*if instr(p_where_clause, ' AND ') > 0 THEN
1619 FOR I IN 1..LENGTH(p_where_clause) LOOP
1620 l_pos := instr(p_where_clause, ' AND ')
1621 ---
1622 */
1623 /*v_excp_str := ' SELECT DISTINCT '||p_query_id||', '||p_obj_sequence_id||', '||p_sequence_id ||
1624 ', med.TRANSACTION_ID, med.INVENTORY_ITEM_ID ' ||
1625 ', med.SR_INSTANCE_ID,med.ORGANIZATION_ID ' ||
1626 ', SYSDATE, fnd_global.user_id,SYSDATE, fnd_global.user_id, fnd_global.login_id'||
1627 ' FROM MSC_EXCEPTION_DETAILS_V med ';*/
1628 /*v_excp_str := ' SELECT DISTINCT '||p_query_id||', '||p_obj_sequence_id||', '||p_sequence_id ||
1629 ', med.TRANSACTION_ID, med.INVENTORY_ITEM_ID ' ||
1630 ', SYSDATE, fnd_global.user_id,SYSDATE, fnd_global.user_id, fnd_global.login_id'||
1631 ' FROM MSC_EXCEPTION_DETAILS_V med ';*/
1632 /*v_excp_where := ' WHERE med.plan_id = '||p_plan_id ||
1633 ' AND NVL(med.category_set_id,2) = 2' ||
1634 ' AND ( med.ORDER_NUMBER IS NOT NULL '||
1635 ' OR med.INVENTORY_ITEM_ID IS NOT NULL)';*/
1636 /*v_excp_where := ' WHERE med.plan_id = '||p_plan_id ||
1637 ' AND NVL(med.category_set_id,2) = 2' ||
1638 ' AND ( med.TRANSACTION_ID IS NOT NULL '||
1639 ' OR med.INVENTORY_ITEM_ID IS NOT NULL)';*/
1640 /*v_insert_stmt := ' INSERT INTO MSC_FORM_QUERY (QUERY_ID,'||
1641 ' NUMBER1,NUMBER2,CHAR1,NUMBER3,' ||
1642 ' NUMBER4,NUMBER5,' ||
1643 ' LAST_UPDATE_DATE, LAST_UPDATED_BY , ' ||
1644 ' CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN )';*/
1645 /*v_insert_stmt := ' INSERT INTO MSC_FORM_QUERY (QUERY_ID,'||
1646 ' NUMBER1,NUMBER2,NUMBER3,NUMBER4,' ||
1647 ' LAST_UPDATE_DATE, LAST_UPDATED_BY , ' ||
1648 ' CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN )';*/
1649 --KSA_DEBUG(SYSDATE,'STMT IS '||v_insert_stmt||' '||v_excp_str||' '||v_excp_where||' '||p_where_clause,'build_Excp_where');
1650 v_delete_stmt := 'DELETE MSC_FORM_QUERY WHERE QUERY_ID = :p_query_id'
1651 ||' AND NUMBER1 = :p_obj_sequence_id AND NUMBER2 = :p_sequence_id';
1652 --KSA_DEBUG(SYSDATE,'del STMT IS '||v_delete_stmt,'build_Excp_where');
1653 execute immediate v_delete_stmt using p_query_id, p_obj_sequence_id, p_sequence_id;
1654 --msc_get_name.execute_dsql(v_delete_stmt);
1655 --KSA_DEBUG(SYSDATE,'ex where is '||v_insert_stmt||v_excp_str||v_excp_where||' AND '||l_where_clause,'build_Excp_where');
1656 --msc_get_name.execute_dsql(v_insert_stmt||v_excp_str||v_excp_where||' AND '||l_where_clause);
1657 --KSA_DEBUG(SYSDATE,'where is '||l_where_clause,'build_Excp_where');
1658 Parse_exceptions(p_plan_id, l_where_clause);
1659 IF g_items_list_exists > 0 THEN
1660 p_excp_where_clause := '('||
1661 ' TRANSACTION_ID IN (SELECT NUMBER3 FROM MSC_FORM_QUERY '||
1662 ' WHERE QUERY_ID = '||p_query_id ||
1663 ' AND NUMBER1 = '||p_obj_sequence_id||
1664 ' AND NUMBER2 = '|| p_sequence_id ||
1665 ' AND NUMBER3 IS NOT NULL' ||')' ||
1666 ' OR '||
1667 ' INVENTORY_ITEM_ID '||
1668 ' IN (SELECT NUMBER4 FROM MSC_FORM_QUERY '||
1669 ' WHERE QUERY_ID = '||p_query_id||
1670 ' AND NUMBER1 = '||p_obj_sequence_id||
1671 ' AND NUMBER2 = '|| p_sequence_id||
1672 ' AND NUMBER4 IS NOT NULL)'||
1673 ')';
1674 ELSE
1675 p_excp_where_clause := '('||
1676 ' TRANSACTION_ID IN (SELECT NUMBER3 FROM MSC_FORM_QUERY '||
1677 ' WHERE QUERY_ID = '||p_query_id ||
1678 ' AND NUMBER1 = '||p_obj_sequence_id||
1679 ' AND NUMBER2 = '|| p_sequence_id ||
1680 ' AND NUMBER3 IS NOT NULL' ||')' ||
1681 ')';
1682 END IF;
1683 END build_Excp_where;
1684
1685 PROCEDURE store_values(p_sequence IN NUMBER,
1686 p_obj_sequence IN NUMBER,
1687 p_field_name IN VARCHAR2,
1688 p_or_values IN VARCHAR2,
1689 p_hidden_values IN VARCHAR2) IS
1690 l_count NUMBER;
1691 BEGIN
1692 --KSA_DEBUG(SYSDATE,'inside...p_field_name'||p_field_name,'MSC_PQ_UTILS.store_values');
1693 l_count := g_among_values.count;
1694 g_among_values(l_count+1).sequence := p_sequence;
1695 g_among_values(l_count+1).object_sequence := p_obj_sequence;
1696 g_among_values(l_count+1).field_name := p_field_name;
1697 g_among_values(l_count+1).or_values := p_or_values;
1698 g_among_values(l_count+1).hidden_values := p_hidden_values;
1699 --KSA_DEBUG(SYSDATE,'exiting...','MSC_PQ_UTILS.store_values');
1700 END store_values;
1701
1702 PROCEDURE clear_values IS
1703 BEGIN
1704 g_among_values.delete;
1705 END clear_values;
1706
1707 PROCEDURE delete_rows(p_field_name in varchar2) IS
1708 total_rows NUMBER;
1709 deleted_rows NUMBER:=0;
1710 l_cur_field_name varchar2(100);
1711 BEGIN
1712 --KSA_DEBUG(SYSDATE,'inside...p_field_name'||p_field_name,'delete_rows');
1713 total_rows :=g_among_values.count;
1714 IF total_rows > 0 THEN
1715 FOR counter IN 1 .. total_rows LOOP
1716 --KSA_DEBUG(SYSDATE,'total_rows...'||total_rows,'delete_rows');
1717 IF g_among_values.exists(counter - deleted_rows) THEN
1718 l_cur_field_name := g_among_values(counter - deleted_rows).FIELD_NAME;
1719 IF p_field_name = l_cur_field_name then
1720 g_among_values.delete(counter - deleted_rows);
1721 deleted_rows :=deleted_rows+1;
1722 total_rows :=total_rows-1;
1723 END if;
1724 END IF;
1725 END Loop;
1726 END IF;
1727 --KSA_DEBUG(SYSDATE,'exiting...','delete_rows');
1728 END delete_rows;
1729
1730
1731
1732 FUNCTION validate_index_use(p_query_id IN NUMBER,
1733 p_query_type IN NUMBER) RETURN NUMBER IS
1734
1735 CURSOR c_validate IS
1736 SELECT count(*)
1737 FROM msc_selection_criteria msc,
1738 msc_personal_queries mpq
1739 WHERE (mpq.query_id = msc.folder_id
1740 AND mpq.query_id = p_query_id and msc.active_flag=1)
1741 AND ( ( mpq.query_type = 1
1742 AND field_name in ('BUYER_NAME', 'ITEM_SEGMENTS',
1743 'ABC_CLASS_NAME','CATEGORY',
1744 'ORGANIZATION_CODE', 'PLANNER_CODE') ) --item
1745 OR( mpq.query_type = 2
1746 AND field_name in ('RESOURCE_CODE', 'ORGANIZATION_CODE', 'DEPARTMENT_LINE_CODE') ) --res
1747 OR( (mpq.query_type = 4 and source_type = 1)
1748 AND ( field_name like '%ORGANIZATION_CODE%'
1749 OR field_name like '%PLANNER_CODE%'
1750 OR field_name like '%RESOURCE_CODE%'
1751 OR field_name like '%DEPARTMENT_CODE%'
1752 OR field_name like '%ITEM_SEGMENTS%'
1753 OR field_name like '%CATEGORY_NAME%'
1754 OR field_name like '%ITEM_NAME%') ) --excp
1755 OR ( (mpq.query_type = 4 and source_type = 2))
1756 OR ( mpq.query_type = 5
1757 AND field_name in ('ORGANIZATION_CODE', 'ITEM_NAME',
1758 'CATEGORY_NAME', 'SUPPLIER_NAME',
1759 'BUYER_NAME', 'PLANNER_CODE') ) --supplier
1760 OR (mpq.query_type = 6)); --loads
1761
1762 CURSOR c_ord_qry(p_query_id IN NUMBER) IS
1763 SELECT mpt.source_type, mpt.object_type, mpt.sequence_id
1764 FROM msc_pq_types mpt
1765 WHERE mpt.query_id = p_query_id
1766 AND mpt.active_flag = 1;
1767
1768 CURSOR c_validate_ord(p_query_id IN NUMBER,
1769 P_source_type IN NUMBER,
1770 P_object_type IN NUMBER,
1771 P_sequence_id IN NUMBER) IS
1772 SELECT COUNT(*)
1773 FROM msc_selection_criteria msc,
1774 msc_pq_types mpt,
1775 msc_personal_queries mpq
1776 WHERE (mpq.query_id = mpt.query_id
1777 AND mpq.query_id = p_query_id
1778 AND mpt.active_flag = 1
1779 AND msc.folder_id = mpt.query_id
1780 AND msc.source_type = mpt.source_type
1781 AND msc.object_type = mpt.object_type
1782 AND msc.object_sequence_id = mpt.sequence_id
1783 AND msc.active_flag=1
1784 AND mpt.source_type = P_source_type
1785 AND mpt.object_type = P_object_type
1786 AND mpt.sequence_id = P_sequence_id)
1787 AND ( msc.field_name like '%ORGANIZATION_CODE%'
1788 OR msc.field_name like '%PLANNER_CODE%'
1789 OR msc.field_name like '%ITEM_SEGMENTS%'
1790 OR msc.field_name like '%CATEGORY_NAME%'
1791 OR msc.field_name like '%ITEM_NAME%');--orders
1792
1793 CURSOR c_validate_wl IS
1794 SELECT count(*)
1795 FROM msc_selection_criteria msc,
1796 msc_personal_queries mpq
1797 WHERE mpq.query_id = msc.folder_id
1798 AND mpq.query_id = p_query_id
1799 AND msc.active_flag = 1
1800 AND mpq.query_type = 10
1801 AND (( source_type = 1
1802 AND ( field_name like '%ORGANIZATION_CODE%'
1803 OR field_name like '%PLANNER_CODE%'
1804 OR field_name like '%RESOURCE_CODE%'
1805 OR field_name like '%DEPARTMENT_CODE%'
1806 OR field_name like '%ITEM_SEGMENTS%'
1807 OR field_name like '%CATEGORY_NAME%'
1808 OR field_name like '%ITEM_NAME%') ) --excp
1809 OR source_type = 2);
1810
1811 l_temp number;
1812
1813 CURSOR c_groupby is
1814 SELECT distinct field_name
1815 FROM msc_selection_criteria
1816 WHERE folder_id = p_query_id
1817 AND NVL(count_by,2) = 1;
1818
1819 l_dummy_field varchar2(100);
1820 l_profile varchar2(10);
1821 l_msg VARCHAR2(2000);
1822 l_warnning NUMBER;
1823 l_query_exists NUMBER;
1824
1825 index_validation_error EXCEPTION;
1826 PRAGMA EXCEPTION_INIT(index_validation_error, -20009);
1827 BEGIN
1828 --KSA_DEBUG(SYSDATE,'VI p_query_id <> '||p_query_id||' p_query_Type '||p_query_type,'validate_index_use');
1829 l_query_exists := 0;
1830 l_warnning := 1;
1831 IF p_query_type = 10 THEN
1832 FOR detailQrec IN detailQCur(p_query_id) LOOP
1833 l_warnning := validate_index_use(detailQrec.query_id, detailQrec.query_type);
1834 IF l_warnning < 1 THEN
1835 raise index_validation_error;
1836 END IF;
1837 l_query_exists := 1; -- at least one query exists
1838 END LOOP;
1839 -- ----------------------------------------
1840 -- Now check for index usage for exceptions
1841 -- ----------------------------------------
1842 --p_query_type := 4;
1843 --ELSE
1844 --p_query_type := p_query_type;
1845 END IF;
1846 IF p_query_type = 9 THEN
1847 FOR rec_ord_qry IN c_ord_qry(p_query_id) LOOP
1848 l_temp := 0;
1849 OPEN c_validate_ord(p_query_id ,
1850 rec_ord_qry.source_type,
1851 rec_ord_qry.object_type,
1852 rec_ord_qry.sequence_id);
1853 FETCH c_validate_ord into l_temp;
1854 IF l_temp = 0 THEN
1855 CLOSE c_validate_ord;
1856 EXIT;
1857 END IF;
1858 CLOSE c_validate_ord;
1859 END LOOP;
1860 ELSIF p_query_type = 10 THEN
1861 open c_validate_wl;
1862 fetch c_validate_wl into l_temp;
1863 close c_validate_wl;
1864 ELSE
1865 open c_validate;
1866 fetch c_validate into l_temp;
1867 close c_validate;
1868 END IF;
1869 l_profile := nvl(FND_PROFILE.VALUE('MSC_PQUERY_EXEC_WITH_CRITERIA'), 'Y');
1870 l_temp := nvl(l_temp,0);
1871 if (l_temp = 0 and l_profile = 'Y' ) then
1872 if (p_query_type = 1) then
1873 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_ITEM_YES');
1874 elsif (p_query_type = 2) then
1875 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_RES_YES');
1876 elsif (p_query_type = 4) THEN
1877 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_EXCP_YES');
1878 elsif (p_query_type = 5) then
1879 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_SUPP_YES');
1880 elsif (p_query_type = 9) THEN -- orders
1881 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_ORD_YES');
1882 elsif (p_query_type = 9) THEN -- orders
1883 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_ORD_YES');
1884 elsif (p_query_type = 10) THEN -- Wlist
1885 NULL;
1886 --fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_ORD_YES');
1887 else
1888 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK');
1889 end if;
1890 l_msg:= fnd_message.get;
1891 l_warnning := -1;
1892 raise index_validation_error;
1893 end if;
1894
1895 if (l_temp = 0 and l_profile = 'N' ) then
1896 if (p_query_type = 1) then
1897 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_ITEM_NO');
1898 elsif (p_query_type = 2) then
1899 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_RES_NO');
1900 elsif (p_query_type = 4) THEN
1901 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_EXCP_NO');
1902 elsif (p_query_type = 5) then
1903 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_SUPP_NO');
1904 elsif (p_query_type = 9) THEN -- orders
1905 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK_ORD_NO');
1906 elsif (p_query_type = 10) THEN -- Wlist
1907 NULL;
1908 else
1909 fnd_message.set_name('MSC', 'MSC_PQ_INDEX_CHECK');
1910 end if;
1911 --fnd_message.hint;
1912 l_warnning := 0;
1913 l_msg:= fnd_message.get;
1914 raise index_validation_error;
1915 end if;
1916
1917 if (p_query_type = 4) then
1918 l_temp := 0;
1919 open c_groupby;
1920 loop
1921 fetch c_groupby into l_dummy_field;
1922 exit when c_groupby%notfound;
1923 l_temp := l_temp + 1;
1924 end loop;
1925 close c_groupby;
1926
1927 if (l_temp > 5) then
1928 fnd_message.set_name('MSC', 'MSC_PQ_GROUPBY_CHECK');
1929 --fnd_message.error;
1930 l_msg := FND_MESSAGE.get;
1931 l_warnning := -1;
1932 raise index_validation_error;
1933 end if;
1934 end if;
1935
1936 RETURN l_warnning; -- 1, successful validation
1937 EXCEPTION
1938 WHEN index_validation_error THEN
1939 G_PQ_ERROR_MESSAGE := l_msg;
1940 RETURN l_warnning;
1941 WHEN OTHERS THEN
1942 G_PQ_ERROR_MESSAGE := sqlerrm(sqlcode);
1943 RETURN -2;
1944 end validate_index_use;
1945
1946 PROCEDURE delete_from_results_table(p_query_id IN NUMBER,
1947 p_plan_id IN NUMBER) IS
1948 BEGIN
1949 --KSA_DEBUG(SYSDATE,' p_query_id <> '||p_query_id,'delete_from_results_table');
1950 DELETE msc_pq_results
1951 WHERE query_id = p_query_id
1952 AND plan_id = p_plan_id ;
1953 END delete_from_results_table;
1954
1955 PROCEDURE execute_one(p_plan_id IN NUMBER,
1956 p_calledFromUI IN NUMBER,
1957 p_partOfWorklist IN NUMBER,
1958 p_query_id IN NUMBER,
1959 p_query_type IN NUMBER,
1960 p_execute_flag BOOLEAN DEFAULT TRUE,
1961 p_master_query_id IN NUMBER DEFAULT NULL) IS
1962
1963 where_clause_segment VARCHAR2(32000);
1964 where_clause_segment2 VARCHAR2(32000);
1965
1966 CURSOR c_query_name (p_query NUMBER) IS
1967 SELECT query_name
1968 FROM msc_personal_queries
1969 WHERE query_id = p_query;
1970
1971 l_query_name VARCHAR2(80);
1972 test BOOLEAN;
1973
1974 CURSOR c_plans IS
1975 SELECT compile_designator
1976 FROM msc_plans
1977 WHERE plan_id = -1;
1978
1979 l_plan VARCHAR2(30);
1980 l_query_type_temp NUMBER;
1981 l_master_query_id NUMBER;
1982 l_response NUMBER;
1983 l_category_id NUMBER;
1984 l_dummy NUMBER;
1985 BEGIN
1986 -- for criticality matrix , we are using p_master_query_id
1987 -- to pass category_id to the build_where_clause function
1988 if p_query_type <> 12 then
1989 l_master_query_id := p_master_query_id;
1990 else
1991 l_category_id := p_master_query_id;
1992 end if;
1993
1994 IF NOT p_execute_flag THEN
1995 RETURN;
1996 END IF;
1997 --KSA_DEBUG(SYSDATE,'q p_query_id <> '||p_query_id||' p_query_Type '||p_query_type,'execute_one');
1998 IF p_query_type <> 10 AND p_partOfWorklist = 1 THEN
1999 NULL;
2000 ELSE
2001 l_response := validate_index_use(p_query_id, p_query_type);
2002 /* IF l_response < 0 THEN
2003 Raise_Application_Error(-20001,G_PQ_ERROR_MESSAGE);
2004 RETURN; -- need to show some error
2005 END IF;*/
2006 END IF;
2007
2008 --KSA_DEBUG(SYSDATE,'before execute query, '||p_query_id||' p_query_Type '||p_query_type,'execute_one');
2009 IF g_category_set_id IS NULL THEN
2010 g_category_set_id := Get_Pref(p_plan_id, 'CATEGORY_SET_ID');
2011 END IF;
2012 IF p_query_type = 10 THEN
2013 delete_from_results_table(p_query_id,
2014 p_plan_id);
2015 FOR detailQrec IN detailQCur(p_query_id) LOOP
2016 execute_one(p_plan_id,
2017 0, -- not called from UI
2018 1, -- p_partOfWorklist
2019 detailQrec.query_id,
2020 detailQrec.query_type,
2021 TRUE, --p_execute_flag
2022 p_query_id); -- master query_id
2023 --l_query_exists := 1; -- at least one query exists
2024 END LOOP;
2025 -- ----------------------------------------
2026 -- Now check for index usage for exceptions
2027 -- ----------------------------------------
2028 l_query_type_temp := 4;
2029 ELSE
2030 l_query_type_temp := p_query_type;
2031 END IF;
2032 IF p_query_type IN (1,2,5,6) THEN
2033 retrieve_values(p_query_id);
2034 where_clause_segment := build_where_clause(p_query_id, 0);
2035 IF (where_clause_segment is null) THEN
2036 where_clause_segment := ' ( -99 = -99 ) ';
2037 END IF;
2038 where_clause_segment := where_clause_segment|| 'AND CATEGORY_SET_ID = '||g_category_set_id;
2039 where_clause_segment := ' ( '||where_clause_segment||' ) ';
2040 --KSA_DEBUG(SYSDATE,'w clause <> '||where_clause_segment,'execute_one');
2041 msc_pers_queries.populate_result_table(p_query_id,
2042 p_query_type,
2043 p_plan_id,
2044 where_clause_segment,
2045 p_execute_flag,
2046 l_master_query_id);
2047 ELSIF p_query_type = 9 then
2048 msc_pq_utils.retrieve_values(p_query_id);
2049 where_clause_segment := msc_pq_utils.build_order_where_clause
2050 (p_query_id,
2051 p_plan_id);
2052 IF (where_clause_segment IS NULL) THEN
2053 where_clause_segment := ' ( -99 = -99 ) ';
2054 END IF;
2055 where_clause_segment := ' ( '||where_clause_segment||' ) ';
2056
2057 --KSA_DEBUG(SYSDATE,'w clause <> '||where_clause_segment,'execute_one');
2058 -- -----------------------------------
2059 -- This need to be set in the UI code.
2060 -- -----------------------------------
2061 --msc_popup_pvt.g_order_where_clause := ' AND '||where_clause_segment;
2062 /* Not required to populate results table if executed from UI.*/
2063 --KSA_DEBUG(SYSDATE,'*2* w clause <> '||where_clause_segment,'execute_one');
2064 IF p_calledFromUI <> 1 THEN
2065 where_clause_segment := where_clause_segment|| 'AND CATEGORY_SET_ID = '||g_category_set_id;
2066
2067 msc_pers_queries.populate_result_table
2068 (p_query_id,
2069 p_query_type,
2070 p_plan_id,
2071 where_clause_segment,
2072 p_execute_flag,
2073 l_master_query_id);
2074 END IF;
2075
2076 ELSIF p_query_type = 12 THEN
2077 retrieve_values(p_query_id);
2078 where_clause_segment := build_where_clause_new(p_query_id, 100, l_category_id);
2079 msc_pers_queries.populate_result_table(p_query_id,
2080 p_query_type, -1,where_clause_segment,
2081 p_execute_flag, p_master_query_id, p_partOfWorklist);
2082
2083
2084 ELSIF p_query_type IN (4,10) THEN
2085 IF P_QUERY_TYPE = 10 THEN
2086 -- --------------------------
2087 -- Check if worklist also contains
2088 -- Exceptions. If yes, continue to process them
2089 -- Else, do nothing.
2090 -- -------------------------------
2091 l_dummy := 0;
2092 OPEN WlExcepCur(p_query_id);
2093 FETCH WlExcepCur INTO l_dummy;
2094 IF WlExcepCur%NOTFOUND THEN
2095 l_dummy :=-99; -- Just to flag no process
2096 END IF;
2097 CLOSE WlExcepCur;
2098 END IF;
2099 IF p_query_type = 4 OR
2100 (p_query_type = 10 AND l_dummy <> -99) THEN
2101 retrieve_values(p_query_id);
2102 where_clause_segment := build_where_clause(p_query_id, 1);
2103 where_clause_segment2 := build_where_clause(p_query_id, 2);
2104 IF (where_clause_segment IS NULL) THEN
2105 where_clause_segment := ' ( -99 = -99 ) ';
2106 END IF;
2107 IF (where_clause_segment2 IS NULL) THEN
2108 where_clause_segment2 := ' ( -99 = -99 ) ';
2109 END IF;
2110 IF where_clause_segment IS NOT NULL THEN
2111 where_clause_segment := ' ( '||where_clause_segment||' ) ';
2112 --KSA_DEBUG(SYSDATE,'*3* w clause <> '||where_clause_segment,'execute_one');
2113 msc_pers_queries.populate_result_table(p_query_id,
2114 p_query_type,
2115 p_plan_id,
2116 where_clause_segment,
2117 p_execute_flag,
2118 l_master_query_id);
2119 END IF;
2120 IF where_clause_segment2 IS NOT NULL THEN
2121 where_clause_segment2 := ' ( '||where_clause_segment2||' ) ';
2122 msc_pers_queries.populate_result_table(p_query_id,
2123 p_query_type, -1,where_clause_segment2,
2124 p_execute_flag,l_master_query_id);
2125 END IF;
2126 END IF;
2127 IF P_QUERY_TYPE = 10 THEN
2128 -- --------------------------
2129 --Run worklist Summarization
2130 -- pass -99
2131 -- -------------------------------
2132 --KSA_DEBUG(SYSDATE,'before execute populate_result_table, '||p_query_id||' l_master_query_id '||l_master_query_id,'execute_one');
2133 msc_pers_queries.populate_result_table(p_query_id,
2134 -99,
2135 p_plan_id,
2136 '',
2137 p_execute_flag,
2138 l_master_query_id);
2139 set_top_action(p_plan_id, p_query_id);
2140 END IF;
2141 END IF;
2142
2143 /*OPEN c_query_name(p_query_id);
2144 FETCH c_query_name into l_query_name;
2145 CLOSE c_query_name;*/
2146
2147
2148 COMMIT; --test := APP_FORM.QUIETCOMMIT;
2149 --do_key('commit_form'); --quietcommit is there..
2150
2151 --copy(p_query_id, 'viewby_control.query_id');
2152 --copy(l_query_name, 'viewby_control.query_name');
2153 --set_item_property('viewby_control.query_name', item_is_valid, property_on);
2154
2155
2156 EXCEPTION
2157 WHEN OTHERS THEN
2158 DECLARE
2159 l_error VARCHAR2(255);
2160 BEGIN
2161 l_error := substr(sqlerrm(sqlcode),1,250);
2162 G_PQ_ERROR_MESSAGE:= l_error;
2163 --COPY(l_error,'GLOBAL.PQ_ERROR_MESSAGE');
2164 END;
2165 RAISE;
2166 END execute_one;
2167
2168 FUNCTION get_error RETURN VARCHAR2 IS
2169 BEGIN
2170 RETURN G_PQ_ERROR_MESSAGE;
2171 END get_error;
2172
2173 PROCEDURE set_impl IS
2174
2175 BEGIN
2176 NULL;
2177 EXCEPTION
2178 WHEN OTHERS THEN
2179 NULL;
2180 END set_impl;
2181
2182 PROCEDURE plans_release IS
2183
2184 BEGIN
2185 set_impl;
2186 EXCEPTION
2187 WHEN OTHERS THEN
2188 NULL;
2189 END plans_release;
2190
2191 FUNCTION get_release_status(p_sr_instance_id IN NUMBER,
2192 P_instance_code IN OUT NOCOPY VARCHAR2)
2193 RETURN NUMBER IS
2194 l_allow_release_flag NUMBER;
2195
2196 CURSOR cur_release_flag(p_instance_id NUMBER) IS
2197 SELECT DECODE(apps_ver,3,NVL(allow_release_flag ,2),
2198 4,NVL(allow_release_flag ,2),1) allow_release_flag,
2199 instance_code
2200 FROM msc_apps_instances
2201 WHERE instance_id = nvl(p_sr_instance_id,-1);
2202 BEGIN
2203 OPEN cur_release_flag(p_sr_instance_id);
2204 FETCH cur_release_flag into l_allow_release_flag,p_instance_code;
2205 IF cur_release_flag%NOTFOUND THEN
2206 l_allow_release_flag := 2;
2207 END IF;
2208 CLOSE cur_release_flag;
2209 RETURN( l_allow_release_flag) ;
2210 END get_release_status;
2211
2212 PROCEDURE release_status(errbuf OUT NOCOPY VARCHAR2,
2213 retcode OUT NOCOPY NUMBER,
2214 p_plan_id IN NUMBER,
2215 p_transaction_Id IN NUMBER,
2216 p_sr_Instance_Id NUMBER) IS
2217
2218 v_err_msg varchar2(80);
2219 l_instance_code varchar2(30);
2220 BEGIN
2221 If get_release_status(p_sr_Instance_Id,l_instance_code) = 2 then
2222 fnd_message.set_name('MSC','MSC_ALLOW_RELEASE_INSTANCE');
2223 fnd_message.set_token('INSTANCE',l_instance_code);
2224 errbuf := fnd_message.get;
2225 retcode := 1;
2226 end if;
2227 END release_status;
2228
2229 -- ---------------------------------------------
2230 -- This program will be called from
2231 -- MSC_GET_BIS_VALUES.ui_post_plan (MSCBISUB.PLS)
2232 -- ----------------------------------------------
2233
2234 PROCEDURE execute_plan_queries(errbuf OUT NOCOPY VARCHAR2,
2235 retcode OUT NOCOPY NUMBER,
2236 p_plan_id IN NUMBER) IS
2237 CURSOR cur_queries IS
2238 SELECT plq.query_Id,pq.query_type
2239 FROM msc_plan_queries plq,
2240 msc_personal_queries pq
2241 WHERE plq.plan_id = p_plan_Id
2242 AND plq.query_id = pq.query_id;
2243 --AND pq.query_type = 9;
2244
2245 CURSOR cur_orders(p_transaction_Id IN NUMBER) IS
2246 SELECT sr_instance_id,organization_id, organization_code
2247 FROM msc_orders_v
2248 WHERE plan_id = p_plan_Id
2249 AND p_transaction_Id = p_transaction_Id;
2250 -- and <order type restrictions>
2251
2252 CURSOR check_release_method IS
2253 SELECT AUTO_RELEASE_METHOD
2254 FROM msc_plans
2255 WHERE plan_id = p_plan_Id;
2256
2257 TYPE r_cursor is REF CURSOR;
2258 t_cur r_cursor;
2259 rec_orders cur_orders%ROWTYPE;
2260
2261 l_stmt VARCHAR2(2000);
2262 l_list VARCHAR2(500);
2263
2264 l_transaction_id NUMBER;
2265 l_auto_release NUMBER;
2266 l_user_id NUMBER :=FND_PROFILE.VALUE('USER_ID');
2267 l_errbuf VARCHAR2(2000);
2268 l_retcode NUMBER;
2269 BEGIN
2270 -- ---------------------------------
2271 -- Check for release method.
2272 -- If auto release is not based on
2273 -- 'Orders' query, do nothing.
2274 -- ---------------------------------
2275 OPEN check_release_method;
2276 FETCH check_release_method INTO l_auto_release;
2277 CLOSE check_release_method;
2278 IF NVL(l_auto_release,0) not in ( 3,4) THEN
2279 RETURN;
2280 END IF;
2281 MSC_pers_queries.purge_plan(p_plan_id);
2282 FOR rec_queries IN cur_queries LOOP
2283 execute_one(p_plan_id,
2284 2,
2285 2,
2286 rec_queries.query_id,
2287 rec_queries.query_type);
2288 IF l_list IS NULL THEN
2289 l_list:= rec_queries.query_id;
2290 ELSE
2291 l_list:= l_list||','||rec_queries.query_id;
2292 END IF;
2293 END LOOP;
2294 /*
2295 IF l_List IS NOT NULL THEN
2296 l_list := '('||l_list||')';
2297 l_stmt := 'SELECT DISTINCT TRANSACTION_ID FROM MSC_PQ_RESULTS '||
2298 'WHERE QUERy_ID IN '||l_lIST;
2299 OPEN t_cur FOR l_stmt;
2300 LOOP
2301 FETCH t_cur INTO l_transaction_id;
2302 EXIT WHEN t_cur%NOTFOUND;
2303 OPEN cur_orders(l_transaction_id);
2304 FETCH cur_orders INTO rec_orders;
2305 IF cur_orders%FOUND THEN
2306 release_status(l_errbuf,l_retcode,
2307 p_plan_id, rec_orders.sr_instance_id,
2308 rec_orders.organization_id);
2309 IF l_retcode < 0 THEN
2310 plans_release;
2311 END IF;
2312 END IF;
2313 CLOSE cur_orders;
2314 END LOOP;
2315 CLOSE t_cur;
2316 END IF;*/
2317 retcode := 0;
2318 EXCEPTION
2319 WHEN OTHERS THEN
2320 errbuf := 'unknown error'||sqlerrm(sqlcode);
2321 retcode := 1;
2322 END execute_plan_queries;
2323
2324 -- ---------------------------------------------
2325 -- This program will be called from
2326 -- MSC_GET_BIS_VALUES.ui_post_plan (MSCBISUB.PLS)
2327 -- ----------------------------------------------
2328
2329 PROCEDURE execute_plan_worklists(errbuf OUT NOCOPY VARCHAR2,
2330 retcode OUT NOCOPY NUMBER,
2331 p_plan_id IN NUMBER) IS
2332 CURSOR cur_worklists IS
2333 SELECT plq.query_Id,QUERY_TYPE
2334 FROM msc_plan_queries plq,
2335 msc_personal_queries pq
2336 WHERE plq.plan_id = p_plan_Id
2337 AND plq.query_id = pq.query_id
2338 AND pq.query_type = 10;
2339
2340 l_errbuf VARCHAR2(2000);
2341 l_retcode NUMBER;
2342 BEGIN
2343
2344 FOR rec_worklists IN cur_worklists LOOP
2345 execute_one(p_plan_id,
2346 0,
2347 0,
2348 rec_worklists.query_id,
2349 rec_worklists.query_type,
2350 TRUE,
2351 rec_worklists.query_id);
2352 END LOOP;
2353
2354 retcode := 0;
2355 EXCEPTION
2356 WHEN OTHERS THEN
2357 errbuf := 'unknown error'||sqlerrm(sqlcode);
2358 retcode := 1;
2359 END execute_plan_worklists;
2360
2361 PROCEDURE set_top_action(p_plan_id IN NUMBER, p_query_id IN NUMBER) IS
2362 cursor c_top_priority(p_PRIORITY in number) is
2363 SELECT nvl(pqt.DETAIL_QUERY_ID, pqt.query_id) query_id
2364 FROM MSC_PQ_TYPES pqt
2365 WHERE pqt.query_id = p_query_id
2366 and pqt.PRIORITY = p_priority;
2367 --AND pqt.DETAIL_QUERY_ID is not null;
2368
2369 cursor c_top_actions is
2370 select distinct priority
2371 from msc_pq_results
2372 where plan_id = p_plan_id
2373 and query_id = p_query_id
2374 and SUMMARY_DATA = 1;
2375 l_query_id number;
2376 begin
2377 for rec_top_actions in c_top_actions loop
2378 l_query_id := 0;
2379 open c_top_priority(rec_top_actions.priority);
2380 fetch c_top_priority into l_query_id;
2381 close c_top_priority;
2382 if nvl(l_query_id,0) <> 0 then
2383 update msc_pq_results
2384 set DETAIL_QUERY_ID = l_query_id
2385 where plan_id = p_plan_id
2386 and query_id = p_query_id
2387 and SUMMARY_DATA = 1
2388 and priority = rec_top_actions.priority;
2389 end if;
2390 end loop;
2391 end set_top_action;
2392
2393 /**Worksheet Code**/
2394 PROCEDURE build_wrksh_query
2395 (
2396 p_plan_id IN NUMBER DEFAULT NULL,
2397 p_query_id IN NUMBER DEFAULT NULL,
2398 P_source_type IN NUMBER DEFAULT NULL,
2399 p_select_clause OUT NOCOPY VARCHAR2,
2400 p_from_clause OUT NOCOPY VARCHAR2,
2401 p_where_clause OUT NOCOPY VARCHAR2,
2402 P_column_prompts OUT NOCOPY VARCHAR2,
2403 p_select_clause_dmd OUT NOCOPY VARCHAR2,
2404 p_from_clause_dmd OUT NOCOPY VARCHAR2,
2405 p_where_clause_dmd OUT NOCOPY VARCHAR2,
2406 p_return_code OUT NOCOPY VARCHAR2
2407 )
2408 IS
2409 /***Filter***/
2410 CURSOR c_criteria (p_folder_object VARCHAR2)
2411 IS
2412 SELECT field_name ,
2413 data_set
2414 FROM msc_selection_criteria_v
2415 WHERE folder_id=p_query_id
2416 AND active_flag =1
2417 AND condition IS NOT NULL
2418 AND folder_object =p_folder_object
2419 ORDER BY field_name ,
2420 data_set ;
2421 CURSOR c_folder_criteria
2422 IS
2423 SELECT DISTINCT FOLDER_objecT
2424 FROM msc_selection_criteria_v
2425 WHERE folder_id=p_query_id
2426 AND active_flag =1
2427 AND FOLDER_objecT LIKE 'MSC_WRKSH_FLTR%'
2428 AND condition IS NOT NULL
2429 ORDER BY FOLDER_objecT;
2430 /***Result**/
2431 CURSOR c_criteria_2 (p_folder_object VARCHAR2)
2432 IS
2433 SELECT mc.folder_object,
2434 mc.field_name ,
2435 mc.sql_statement ,
2436 mc.sql_statement2 ,
2437 fl.meaning
2438 FROM msc_selection_criteria_v mc,
2439 fnd_lookups fl
2440 WHERE mc.folder_id=p_query_id
2441 AND mc.active_flag =1
2442 AND mc.condition IS NULL
2443 AND folder_object =p_folder_object
2444 AND mc.field_name =fl.lookup_code
2445 AND mc.folder_object =fl.lookup_type
2446 ORDER BY mc.folder_object ,
2447 mc.field_name ;
2448
2449 CURSOR c_folder_criteria_2
2450 IS
2451 SELECT DISTINCT FOLDER_objecT
2452 FROM msc_selection_criteria_v
2453 WHERE folder_id=p_query_id
2454 AND active_flag =1
2455 AND FOLDER_objecT LIKE 'MSC_WRKSH_RSLT%'
2456 AND condition IS NULL
2457 ORDER BY FOLDER_objecT;
2458
2459 /***Other***/
2460 CURSOR c_criteria_other
2461 IS
2462 SELECT field_name ,
2463 hidden_from_field,
2464 from_field,folder_object
2465 FROM msc_selection_criteria
2466 WHERE folder_id=p_query_id
2467 AND active_flag =1
2468 AND condition IS NULL
2469 AND folder_object ='MSC_WRKSH_FLTR_OTHER'
2470 ORDER BY field_name ;
2471
2472 l_item_flag varchar2(1):='N';
2473 l_enditem_flag varchar2(1):='N';
2474 l_order_flag varchar2(1):='N';
2475 l_demand_flag varchar2(1):='N';
2476 l_mic1_flag varchar2(1):='N';
2477 l_mic2_flag varchar2(1):='N';
2478 l_mic3_flag varchar2(1):='N';
2479 l_mic4_flag varchar2(1):='N';
2480 l_mic5_flag varchar2(1):='N';
2481 l_mic6_flag varchar2(1):='N';
2482 l_wu1_flag varchar2(1):='N';
2483 l_wu2_flag varchar2(1):='N';
2484 l_wu3_flag varchar2(1):='N';
2485 l_mfpw1_flag varchar2(1):='N';
2486 l_mfpw2_flag varchar2(1):='N';
2487 l_mfpe1_flag varchar2(1):='N';
2488
2489 l_Eitem_flag varchar2(1):='N';
2490 l_Eenditem_flag varchar2(1):='N';
2491 l_Eorder_flag varchar2(1):='N';
2492 l_Edemand_flag varchar2(1):='N';
2493 l_Ewhere_flag varchar2(1):='N';
2494
2495 /*Where clause check filter/Result*/
2496 l_wmic1_flag varchar2(1):='N';
2497 l_wmic2_flag varchar2(1):='N';
2498 l_wmic3_flag varchar2(1):='N';
2499 l_wmic4_flag varchar2(1):='N';
2500 l_wmic5_flag varchar2(1):='N';
2501 l_wmic6_flag varchar2(1):='N';
2502 l_wwu1_flag varchar2(1):='N';
2503 l_wwu2_flag varchar2(1):='N';
2504 l_wwu3_flag varchar2(1):='N';
2505 /*where clause check others tab*/
2506 l_omic1_flag varchar2(1):='N';
2507 l_omic2_flag varchar2(1):='N';
2508 l_omic3_flag varchar2(1):='N';
2509 l_omic4_flag varchar2(1):='N';
2510 l_omic5_flag varchar2(1):='N';
2511 l_omic6_flag varchar2(1):='N';
2512 l_owu1_flag varchar2(1):='N';
2513 l_owu2_flag varchar2(1):='N';
2514 l_owu3_flag varchar2(1):='N';
2515
2516 l_field_name VARCHAR2(50);
2517 l_data_set VARCHAR2(50);
2518 l_data_type VARCHAR2(50);
2519 l_folder_object VARCHAR2(30);
2520 from_clause_segment VARCHAR2(32000) := NULL;
2521 where_clause_segment VARCHAR2(32000) :=NULL;
2522 entity_segment VARCHAR2(2000) :=NULL;
2523 where_clause VARCHAR2(32000) :=NULL;
2524 i NUMBER :=1;
2525 /**Result**/
2526 l_field_name_2 VARCHAR2(50);
2527 l_sql_statement_2 VARCHAR2(32000);
2528 l_folder_object_2 VARCHAR2(30);
2529 select_clause_segment VARCHAR2(32000):= NULL;
2530 where_clause_segment_2 VARCHAR2(32000):=NULL;
2531 entity_segment_2 VARCHAR2(2000) :=NULL;
2532 L_meaning VARCHAR2(200) :=NULL;
2533 j NUMBER :=1;
2534 column_prompts_segment VARCHAR2(32000) :=NULL;
2535 p_query VARCHAR2(32000):= NULL;
2536
2537 /**Result Demnad**/
2538 l_field_name_dmd VARCHAR2(50);
2539 l_sql_statement_dmd VARCHAR2(32000);
2540 l_folder_object_dmd VARCHAR2(30);
2541 select_clause_dmd VARCHAR2(32000):= NULL;
2542 where_clause_dmd VARCHAR2(32000):=NULL;
2543 from_clause_dmd VARCHAR2(32000) := NULL;
2544 select_clause_dmd2 VARCHAR2(32000):= NULL;
2545 where_clause_dmd2 VARCHAR2(32000):=NULL;
2546 from_clause_dmd2 VARCHAR2(32000) := NULL;
2547
2548 /***Other***/
2549 l_field_name_3 VARCHAR2(50);
2550 l_hidden_field VARCHAR2(50);
2551 l_from_field VARCHAR2(50);
2552 l_folder_object_3 VARCHAR2(30);
2553 l_return_code VARCHAR2(5000):=NULL;
2554
2555
2556 BEGIN
2557 retrieve_values(p_query_id);
2558 where_clause :=MSC_PQ_UTILS.build_where_clause_wrksh(p_query_id,0);
2559 FOR c_f_criteria_row IN c_folder_criteria
2560 LOOP
2561 FOR c_criteria_row IN c_criteria(c_f_criteria_row.fOLDER_object)
2562 LOOP
2563
2564 l_field_name := c_criteria_row.field_name ;
2565 l_data_set := c_criteria_row.data_set;
2566 l_folder_object := c_f_criteria_row.fOLDER_object ;
2567 IF l_folder_object ='MSC_WRKSH_FLTR_ITEM' THEN
2568 IF l_field_name IN ('ITEM_SEGMENTS','PLANNER_CODE') THEN
2569 IF l_item_flag='N' THEN
2570 from_clause_segment :=from_clause_segment||',msc_system_items i';
2571 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
2572 l_item_flag:='Y';
2573 END IF;
2574 elsif l_field_name ='CATEGORY1' THEN
2575 l_wmic1_flag:='Y';
2576 IF l_item_flag='N' THEN
2577 from_clause_segment :=from_clause_segment||',msc_system_items i';
2578 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
2579 l_item_flag:='Y';
2580 END IF;
2581 IF l_mic1_flag='N' THEN
2582 l_mic1_flag:='Y';
2583 from_clause_segment :=from_clause_segment||',msc_item_categories mic1';
2584 where_clause_segment := where_clause_segment|| ' and i.sr_instance_id=mic1.sr_instance_id and i.organization_id=mic1.organization_id and i.inventory_item_id=mic1.inventory_item_id ';
2585
2586 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic1';
2587 where_clause_dmd := where_clause_dmd|| ' and i.sr_instance_id=mic1.sr_instance_id and i.organization_id=mic1.organization_id and i.inventory_item_id=mic1.inventory_item_id ';
2588 end if;
2589 elsif l_field_name ='CATEGORY2' THEN
2590 l_wmic2_flag:='Y';
2591 IF l_item_flag='N' THEN
2592 from_clause_segment :=from_clause_segment||',msc_system_items i';
2593 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
2594 l_item_flag:='Y';
2595 END IF;
2596 IF l_mic2_flag='N' THEN
2597 l_mic2_flag:='Y';
2598 from_clause_segment :=from_clause_segment||',msc_item_categories mic2';
2599 where_clause_segment := where_clause_segment|| ' and i.sr_instance_id=mic2.sr_instance_id and i.organization_id=mic2.organization_id and i.inventory_item_id=mic2.inventory_item_id ';
2600 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic2';
2601 where_clause_dmd := where_clause_dmd|| ' and i.sr_instance_id=mic2.sr_instance_id and i.organization_id=mic2.organization_id and i.inventory_item_id=mic2.inventory_item_id ';
2602 END IF;
2603 elsif l_field_name ='CATEGORY3' THEN
2604 l_wmic3_flag:='Y';
2605 IF l_item_flag='N' THEN
2606 from_clause_segment :=from_clause_segment||',msc_system_items i';
2607 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
2608 l_item_flag:='Y';
2609 END IF;
2610 IF l_mic3_flag='N' THEN
2611 l_mic3_flag:='Y';
2612 from_clause_segment :=from_clause_segment||',msc_item_categories mic3 ';
2613 where_clause_segment:= where_clause_segment|| ' and i.sr_instance_id=mic3.sr_instance_id and i.organization_id=mic3.organization_id and i.inventory_item_id=mic3.inventory_item_id ';
2614 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic3';
2615 where_clause_dmd := where_clause_dmd|| ' and i.sr_instance_id=mic3.sr_instance_id and i.organization_id=mic3.organization_id and i.inventory_item_id=mic3.inventory_item_id ';
2616 END IF;
2617 elsif l_field_name ='WHERE_USED_CATEGORY1' THEN
2618 l_wwu1_flag:='Y';
2619 IF l_wu1_flag='N' THEN
2620 l_wu1_flag:='Y';
2621 from_clause_segment :=from_clause_segment||',msc_item_categories wu1';
2622 where_clause_segment:= where_clause_segment|| ' and mfpw2.sr_instance_id=wu1.sr_instance_id and mfpw2.organization_id=wu1.organization_id and mfpw2.inventory_item_id=wu1.inventory_item_id ';
2623 end if;
2624 elsif l_field_name ='WHERE_USED_CATEGORY2' THEN
2625 l_wwu2_flag:='Y';
2626 IF l_wu2_flag='N' THEN
2627 l_wu2_flag:='Y';
2628 from_clause_segment :=from_clause_segment||',msc_item_categories wu2';
2629 where_clause_segment:= where_clause_segment|| ' and mfpw2.sr_instance_id=wu2.sr_instance_id and mfpw2.organization_id=wu2.organization_id and mfpw2.inventory_item_id=wu2.inventory_item_id ';
2630 end if;
2631 elsif l_field_name ='WHERE_USED_CATEGORY3' THEN
2632 l_wwu3_flag:='Y';
2633 IF l_wu3_flag='N' THEN
2634 l_wu3_flag:='Y';
2635 from_clause_segment :=from_clause_segment||',msc_item_categories wu3';
2636 where_clause_segment:= where_clause_segment|| ' and mfpw2.sr_instance_id=wu3.sr_instance_id and mfpw2.organization_id=wu3.organization_id and mfpw2.inventory_item_id=wu3.inventory_item_id ';
2637 end if;
2638 END IF;
2639 END IF;
2640 IF l_field_name LIKE 'WHERE_USED_CATEGORY%' THEN
2641 IF l_Ewhere_flag ='N' THEN
2642 entity_segment:=entity_segment||'WHERE_USED:';
2643 l_Ewhere_flag :='Y';
2644 END IF;
2645 IF l_order_flag='N' THEN
2646 l_order_flag:='Y';
2647 from_clause_segment :=from_clause_segment||',msc_supplies sup';
2648 where_clause_segment :=where_clause_segment||' and i.plan_id=sup.plan_id and i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id and i.inventory_item_id=sup.inventory_item_id ';
2649 END IF;
2650 IF l_mfpw1_flag='N' and l_mfpw2_flag='N' THEN
2651 l_mfpw1_flag:='Y';
2652 l_mfpw2_flag:='Y';
2653 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw1,msc_full_pegging mfpw2 ';
2654 where_clause_segment:= where_clause_segment|| ' and mfpw1.plan_id=sup.plan_id and mfpw1.transaction_id=sup.transaction_id and mfpw1.plan_id=mfpw2.plan_id and mfpw1.prev_pegging_id=mfpw2.pegging_id ';
2655 END IF;
2656 if l_mfpw2_flag='N' and l_mfpw1_flag='Y' THEN
2657 l_mfpw2_flag:='Y';
2658 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw2';
2659 where_clause_segment:=where_clause_segment||' and mfpw1.plan_id=mfpw2.plan_id and mfpw1.prev_pegging_id=mfpw2.pegging_id ';
2660 end if;
2661 END IF;
2662
2663 IF l_folder_object ='MSC_WRKSH_FLTR_ENDITEM' THEN
2664 IF l_field_name ='ITEM_SEGMENTS' THEN
2665 IF l_enditem_flag='N' THEN
2666 from_clause_segment :=from_clause_segment||',msc_system_items ei';
2667 from_clause_dmd :=from_clause_dmd||',msc_system_items ei';
2668 l_enditem_flag:='Y';
2669 END IF;
2670 elsif l_field_name ='CATEGORY1' THEN
2671 l_wmic4_flag:='Y';
2672 IF l_enditem_flag='N' THEN
2673 from_clause_segment :=from_clause_segment||',msc_system_items ei';
2674 from_clause_dmd :=from_clause_dmd||',msc_system_items ei';
2675 l_enditem_flag:='Y';
2676 END IF;
2677 IF l_mic4_flag='N' THEN
2678 l_mic4_flag:='Y';
2679 from_clause_segment :=from_clause_segment||',msc_item_categories mic4';
2680 where_clause_segment := where_clause_segment|| ' and ei.sr_instance_id=mic4.sr_instance_id and ei.organization_id=mic4.organization_id and ei.inventory_item_id=mic4.inventory_item_id ';
2681 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic4';
2682 where_clause_dmd := where_clause_dmd|| ' and ei.sr_instance_id=mic4.sr_instance_id and ei.organization_id=mic4.organization_id and ei.inventory_item_id=mic4.inventory_item_id ';
2683 end if;
2684 elsif l_field_name ='CATEGORY2' THEN
2685 l_wmic5_flag:='Y';
2686 IF l_enditem_flag='N' THEN
2687 from_clause_segment :=from_clause_segment||',msc_system_items ei';
2688 from_clause_dmd :=from_clause_dmd||',msc_system_items ei';
2689 l_enditem_flag:='Y';
2690 END IF;
2691 IF l_mic5_flag='N' THEN
2692 l_mic5_flag:='Y';
2693 from_clause_segment :=from_clause_segment||',msc_item_categories mic5';
2694 where_clause_segment := where_clause_segment|| ' and ei.sr_instance_id=mic5.sr_instance_id and ei.organization_id=mic5.organization_id and ei.inventory_item_id=mic5.inventory_item_id ';
2695 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic5';
2696 where_clause_dmd := where_clause_dmd|| ' and ei.sr_instance_id=mic5.sr_instance_id and ei.organization_id=mic5.organization_id and ei.inventory_item_id=mic5.inventory_item_id ';
2697 end if;
2698 elsif l_field_name ='CATEGORY3' THEN
2699 l_wmic6_flag:='Y';
2700 IF l_enditem_flag='N' THEN
2701 from_clause_segment :=from_clause_segment||',msc_system_items ei';
2702 from_clause_dmd :=from_clause_dmd||',msc_system_items ei';
2703 l_enditem_flag:='Y';
2704 END IF;
2705 IF l_mic6_flag='N' THEN
2706 l_mic6_flag:='Y';
2707 from_clause_segment :=from_clause_segment||',msc_item_categories mic6';
2708 where_clause_segment:= where_clause_segment|| ' and ei.sr_instance_id=mic6.sr_instance_id and ei.organization_id=mic6.organization_id and ei.inventory_item_id=mic6.inventory_item_id ';
2709 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic6';
2710 where_clause_dmd := where_clause_dmd|| ' and ei.sr_instance_id=mic6.sr_instance_id and ei.organization_id=mic6.organization_id and ei.inventory_item_id=mic6.inventory_item_id ';
2711 end if;
2712 END IF;
2713 END IF;
2714 IF l_folder_object ='MSC_WRKSH_FLTR_ORDER' THEN
2715 IF l_field_name IN ('ORGANIZATION_CODE','FIRM_PLANNED_TYPE','NEW_DUE_DATE') THEN
2716 IF l_item_flag='N' THEN
2717 from_clause_segment :=from_clause_segment||',msc_system_items i';
2718 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
2719 l_item_flag:='Y';
2720 END IF;
2721 IF l_order_flag='N' THEN
2722 l_order_flag:='Y';
2723 from_clause_segment :=from_clause_segment||',msc_supplies sup';
2724 where_clause_segment := where_clause_segment||' and i.plan_id=sup.plan_id and i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id and i.inventory_item_id=sup.inventory_item_id ';
2725 from_clause_dmd :=from_clause_dmd||',msc_demands dmd';
2726 where_clause_dmd := where_clause_dmd||' and i.plan_id=dmd.plan_id and i.sr_instance_id=dmd.sr_instance_id and i.organization_id=dmd.organization_id and i.inventory_item_id=dmd.inventory_item_id ';
2727 END IF;
2728 END IF;
2729 END IF;
2730 IF l_folder_object ='MSC_WRKSH_FLTR_DEMAND' THEN
2731 IF l_field_name IN ( 'ORGANIZATION_CODE','NEW_DUE_DATE') THEN
2732 IF l_enditem_flag='N' THEN
2733 from_clause_segment :=from_clause_segment||',msc_system_items ei';
2734 l_enditem_flag:='Y';
2735 END IF;
2736 IF l_demand_flag='N' THEN
2737 l_demand_flag:='Y';
2738 from_clause_segment :=from_clause_segment||',msc_demands dem';
2739 where_clause_segment := where_clause_segment||' and ei.plan_id=dem.plan_id and ei.sr_instance_id=dem.sr_instance_id and ei.organization_id=dem.organization_id and ei.inventory_item_id=dem.inventory_item_id ';
2740 END IF;
2741 END IF;
2742 END IF;
2743 END LOOP;
2744 IF l_folder_object ='MSC_WRKSH_FLTR_ITEM' AND l_Eitem_flag ='N' THEN
2745 entity_segment :=entity_segment||'ITEM:';
2746 l_Eitem_flag :='Y';
2747 elsIF l_folder_object='MSC_WRKSH_FLTR_ENDITEM' AND l_Eenditem_flag ='N' THEN
2748 entity_segment :=entity_segment||'ENDASSEMBLY:';
2749 l_Eenditem_flag :='Y';
2750 ELSIF l_folder_object='MSC_WRKSH_FLTR_ORDER' AND l_Eorder_flag ='N' THEN
2751 entity_segment :=entity_segment||'ORDER:';
2752 l_Eorder_flag :='Y';
2753 ELSIF l_folder_object='MSC_WRKSH_FLTR_DEMAND' AND l_Edemand_flag ='N' THEN
2754 entity_segment :=entity_segment||'DEMAND:';
2755 l_Edemand_flag :='Y';
2756 END IF;
2757 END LOOP;
2758 /***select**/
2759 FOR c_f_criteria_row_2 IN c_folder_criteria_2
2760 LOOP
2761 FOR c_criteria_row_2 IN c_criteria_2(c_f_criteria_row_2.fOLDER_object)
2762 LOOP
2763 IF j=1 THEN
2764 NULL;
2765 j :=2 ;
2766 --select_clause_segment:=' SELECT DISTINCT ';
2767 select_clause_segment:=' SELECT ';
2768 select_clause_dmd:=' UNION ALL SELECT ';
2769 ELSE
2770 -- select_clause_segment :=select_clause_segment||'|'||'|'||''''||g_delimiter||''''||'|'||'|';
2771 select_clause_segment :=select_clause_segment||'|'||'|'||g_delimiter||'|'||'|';
2772 select_clause_dmd :=select_clause_dmd||'|'||'|'||g_delimiter||'|'||'|';
2773 column_prompts_segment:=column_prompts_segment||g_delimiter1;
2774 END IF;
2775 l_field_name_2 := c_criteria_row_2.field_name ;
2776 l_sql_statement_2 := c_criteria_row_2.sql_statement;
2777 l_folder_object_2 := c_f_criteria_row_2.fOLDER_object ;
2778 IF l_folder_object_2 ='MSC_WRKSH_RSLT_ORDER' THEN
2779 l_sql_statement_dmd := c_criteria_row_2.sql_statement2;
2780 else
2781 l_sql_statement_dmd := c_criteria_row_2.sql_statement;
2782 END IF;
2783
2784 L_meaning :=c_criteria_row_2.MEANING;
2785 select_clause_segment :=select_clause_segment||l_sql_statement_2;
2786 select_clause_dmd :=select_clause_dmd||l_sql_statement_dmd;
2787 column_prompts_segment :=column_prompts_segment||L_meaning;
2788 --column_list_segment :=column_list_segment||l_sql_statement_2;
2789 IF l_folder_object_2 ='MSC_WRKSH_RSLT_ITEM' THEN
2790 IF l_item_flag='N' THEN
2791 from_clause_segment :=from_clause_segment||',msc_system_items i';
2792 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
2793 l_item_flag:='Y';
2794 END IF;
2795 IF l_field_name_2 ='ITEM_CATEGORY1' THEN
2796 l_wmic1_flag:='Y';
2797 IF l_item_flag='N' THEN
2798 from_clause_segment :=from_clause_segment||',msc_system_items i';
2799 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
2800 l_item_flag:='Y';
2801 END IF;
2802 IF l_mic1_flag='N' THEN
2803 l_mic1_flag:='Y';
2804 from_clause_segment :=from_clause_segment||',msc_item_categories mic1';
2805 where_clause_segment := where_clause_segment|| ' and i.sr_instance_id=mic1.sr_instance_id and i.organization_id=mic1.organization_id and i.inventory_item_id=mic1.inventory_item_id ';
2806 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic1';
2807 where_clause_dmd := where_clause_dmd|| ' and i.sr_instance_id=mic1.sr_instance_id and i.organization_id=mic1.organization_id and i.inventory_item_id=mic1.inventory_item_id ';
2808
2809 END IF;
2810 elsif l_field_name_2 ='ITEM_CATEGORY2' THEN
2811 l_wmic2_flag:='Y';
2812 IF l_item_flag='N' THEN
2813 from_clause_segment :=from_clause_segment||',msc_system_items i';
2814 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
2815 l_item_flag:='Y';
2816 END IF;
2817 IF l_mic2_flag='N' THEN
2818 l_mic2_flag:='Y';
2819 from_clause_segment :=from_clause_segment||',msc_item_categories mic2';
2820 where_clause_segment := where_clause_segment|| ' and i.sr_instance_id=mic2.sr_instance_id and i.organization_id=mic2.organization_id and i.inventory_item_id=mic2.inventory_item_id ';
2821 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic2';
2822 where_clause_dmd := where_clause_dmd|| ' and i.sr_instance_id=mic2.sr_instance_id and i.organization_id=mic2.organization_id and i.inventory_item_id=mic2.inventory_item_id ';
2823 END IF;
2824 elsif l_field_name_2 ='ITEM_CATEGORY3' THEN
2825 l_wmic3_flag:='Y';
2826 IF l_item_flag='N' THEN
2827 from_clause_segment :=from_clause_segment||',msc_system_items i';
2828 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
2829 l_item_flag:='Y';
2830 END IF;
2831 IF l_mic3_flag='N' THEN
2832 l_mic3_flag:='Y';
2833 from_clause_segment :=from_clause_segment||',msc_item_categories mic3';
2834 where_clause_segment := where_clause_segment|| ' and i.sr_instance_id=mic3.sr_instance_id and i.organization_id=mic3.organization_id and i.inventory_item_id=mic3.inventory_item_id ';
2835 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic3';
2836 where_clause_dmd := where_clause_dmd|| ' and i.sr_instance_id=mic3.sr_instance_id and i.organization_id=mic3.organization_id and i.inventory_item_id=mic3.inventory_item_id ';
2837 END IF;
2838 END IF;
2839 END IF;
2840 IF l_folder_object_2 ='MSC_WRKSH_RSLT_ENDITEM' THEN
2841 IF l_enditem_flag='N' THEN
2842 from_clause_segment :=from_clause_segment||',msc_system_items ei';
2843 from_clause_dmd :=from_clause_dmd||',msc_system_items ei';
2844 l_enditem_flag:='Y';
2845 END IF;
2846 IF l_field_name_2 ='ITEM_CATEGORY1' THEN
2847 l_wmic4_flag:='Y';
2848 IF l_enditem_flag='N' THEN
2849 from_clause_segment :=from_clause_segment||',msc_system_items ei';
2850 from_clause_dmd :=from_clause_dmd||',msc_system_items ei';
2851 l_enditem_flag:='Y';
2852 END IF;
2853 IF l_mic4_flag='N' THEN
2854 l_mic4_flag:='Y';
2855 from_clause_segment :=from_clause_segment||',msc_item_categories mic4';
2856 where_clause_segment := where_clause_segment|| ' and ei.sr_instance_id=mic4.sr_instance_id and ei.organization_id=mic4.organization_id and ei.inventory_item_id=mic4.inventory_item_id ';
2857 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic4';
2858 where_clause_dmd := where_clause_dmd|| ' and ei.sr_instance_id=mic4.sr_instance_id and ei.organization_id=mic4.organization_id and ei.inventory_item_id=mic4.inventory_item_id ';
2859 END IF;
2860 elsif l_field_name_2 ='ITEM_CATEGORY2' THEN
2861 l_wmic5_flag:='Y';
2862 IF l_enditem_flag='N' THEN
2863 from_clause_segment :=from_clause_segment||',msc_system_items ei';
2864 from_clause_dmd :=from_clause_dmd||',msc_system_items ei';
2865 l_enditem_flag:='Y';
2866 END IF;
2867 IF l_mic5_flag='N' THEN
2868 l_mic5_flag:='Y';
2869 from_clause_segment :=from_clause_segment||',msc_item_categories mic5';
2870 where_clause_segment := where_clause_segment|| ' and ei.sr_instance_id=mic5.sr_instance_id and ei.organization_id=mic5.organization_id and ei.inventory_item_id=mic5.inventory_item_id ';
2871 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic5';
2872 where_clause_dmd := where_clause_dmd|| ' and ei.sr_instance_id=mic5.sr_instance_id and ei.organization_id=mic5.organization_id and ei.inventory_item_id=mic5.inventory_item_id ';
2873 END IF;
2874 elsif l_field_name_2 ='ITEM_CATEGORY3' THEN
2875 l_wmic6_flag:='Y';
2876 IF l_enditem_flag='N' THEN
2877 from_clause_segment :=from_clause_segment||',msc_system_items ei';
2878 from_clause_dmd :=from_clause_dmd||',msc_system_items ei';
2879 l_enditem_flag:='Y';
2880 END IF;
2881 IF l_mic6_flag='N' THEN
2882 l_mic6_flag:='Y';
2883 from_clause_segment :=from_clause_segment||',msc_item_categories mic6';
2884 where_clause_segment := where_clause_segment|| ' and ei.sr_instance_id=mic6.sr_instance_id and ei.organization_id=mic6.organization_id and ei.inventory_item_id=mic6.inventory_item_id ';
2885 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic6';
2886 where_clause_dmd := where_clause_dmd|| ' and ei.sr_instance_id=mic6.sr_instance_id and ei.organization_id=mic6.organization_id and ei.inventory_item_id=mic6.inventory_item_id ';
2887 END IF;
2888 END IF;
2889 END IF;
2890 IF l_folder_object_2 ='MSC_WRKSH_RSLT_ORDER' THEN
2891 IF l_item_flag='N' THEN
2892 from_clause_segment :=from_clause_segment||',msc_system_items i';
2893 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
2894 l_item_flag:='Y';
2895 END IF;
2896 IF l_order_flag='N' THEN
2897 l_order_flag:='Y';
2898 from_clause_segment :=from_clause_segment||',msc_supplies sup';
2899 where_clause_segment :=where_clause_segment||' and i.plan_id=sup.plan_id and i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id and i.inventory_item_id=sup.inventory_item_id ';
2900 from_clause_dmd :=from_clause_dmd||' ,msc_demands dmd';
2901 where_clause_dmd := where_clause_dmd|| ' and i.plan_id=dmd.plan_id and i.sr_instance_id=dmd.sr_instance_id and i.organization_id=dmd.organization_id and i.inventory_item_id=dmd.inventory_item_id ';
2902 END IF;
2903 IF l_field_name_2 ='IWU_CATEGORY1' THEN
2904 l_wwu1_flag:='Y';
2905 IF l_wu1_flag='N' THEN
2906 l_wu1_flag:='Y';
2907 from_clause_segment :=from_clause_segment||',msc_item_categories wu1';
2908 where_clause_segment := where_clause_segment|| ' and mfpw2.sr_instance_id=wu1.sr_instance_id and mfpw2.organization_id=wu1.organization_id and mfpw2.inventory_item_id=wu1.inventory_item_id ';
2909 END IF;
2910 elsif l_field_name_2 ='IWU_CATEGORY2' THEN
2911 l_wwu2_flag:='Y';
2912 IF l_wu2_flag='N' THEN
2913 l_wu2_flag:='Y';
2914 from_clause_segment :=from_clause_segment||',msc_item_categories wu2';
2915 where_clause_segment := where_clause_segment|| ' and mfpw2.sr_instance_id=wu2.sr_instance_id
2916 and mfpw2.organization_id=wu2.organization_id and mfpw2.inventory_item_id=wu2.inventory_item_id ';
2917 END IF;
2918 elsif l_field_name_2 ='IWU_CATEGORY3' THEN
2919 l_wwu3_flag:='Y';
2920 IF l_wu3_flag='N' THEN
2921 l_wu3_flag:='Y';
2922 from_clause_segment :=from_clause_segment||',msc_item_categories wu3';
2923 where_clause_segment := where_clause_segment|| ' and mfpw2.sr_instance_id=wu3.sr_instance_id
2924 and mfpw2.organization_id=wu3.organization_id and mfpw2.inventory_item_id=wu3.inventory_item_id ';
2925 END IF;
2926 END IF;
2927 IF l_field_name_2 LIKE 'IWU_CATEGORY%' THEN
2928 IF l_Ewhere_flag ='N' THEN
2929 entity_segment:=entity_segment||'WHERE_USED:';
2930 l_Ewhere_flag :='Y';
2931 end if;
2932 IF l_mfpw1_flag='N' and l_mfpw2_flag='N' THEN
2933 l_mfpw1_flag:='Y';
2934 l_mfpw2_flag:='Y';
2935 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw1,msc_full_pegging mfpw2 ';
2936 where_clause_segment := where_clause_segment|| ' and mfpw1.plan_id=sup.plan_id and mfpw1.transaction_id=sup.transaction_id and mfpw1.plan_id=mfpw2.plan_id and mfpw1.prev_pegging_id=mfpw2.pegging_id ';
2937 END IF;
2938 if l_mfpw2_flag='N' and l_mfpw1_flag='Y' THEN
2939 l_mfpw2_flag:='Y';
2940 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw2';
2941 where_clause_segment:=where_clause_segment||' and mfpw1.plan_id=mfpw2.plan_id and mfpw1.prev_pegging_id=mfpw2.pegging_id ';
2942 end if;
2943 END IF;
2944 END IF;
2945 IF l_folder_object_2 ='MSC_WRKSH_RSLT_DEMAND' THEN
2946 IF l_demand_flag='N' THEN
2947 l_demand_flag:='Y';
2948 from_clause_segment :=from_clause_segment||',msc_demands dem';
2949 END IF;
2950 --IF l_field_name_2 IN ('ITEM_SEGMENTS','ORGANIZATION_CODE') THEN
2951 IF l_enditem_flag='N' THEN
2952 from_clause_segment :=from_clause_segment||',msc_system_items ei';
2953 l_enditem_flag:='Y';
2954 END IF;
2955 IF l_mfpw1_flag ='N' AND l_mfpe1_flag='N' THEN
2956 l_mfpw1_flag :='Y';
2957 l_mfpe1_flag :='Y';
2958 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw1,msc_full_pegging mfpe1';
2959 where_clause_segment := where_clause_segment|| ' and mfpe1.prev_pegging_id is null
2960 and mfpe1.plan_id=ei.plan_id and mfpe1.sr_instance_id=ei.sr_instance_id
2961 and mfpe1.organization_id=ei.organization_id and mfpe1.inventory_item_id=ei.inventory_item_id
2962 and mfpw1.plan_id=sup.plan_id and mfpw1.transaction_id=sup.transaction_id
2963 and mfpw1.plan_id=mfpe1.plan_id and mfpw1.end_pegging_id=mfpe1.end_pegging_id and mfpe1.plan_id=dem.plan_id (+)
2964 and mfpe1.demand_id=dem.demand_id (+)';
2965 END IF;
2966 IF l_order_flag ='N' THEN
2967 l_order_flag :='Y';
2968 from_clause_segment :=from_clause_segment||',msc_supplies sup';
2969 where_clause_segment := where_clause_segment||' and i.plan_id=sup.plan_id and i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id
2970 and i.inventory_item_id=sup.inventory_item_id ';
2971 END IF;
2972 -- END IF;
2973 END IF;
2974 END LOOP;
2975 IF l_folder_object_2 ='MSC_WRKSH_RSLT_ITEM' AND l_Eitem_flag ='N' THEN
2976 entity_segment :=entity_segment||'ITEM:';
2977 l_Eitem_flag :='Y';
2978 elsIF l_folder_object_2='MSC_WRKSH_RSLT_ENDITEM' AND l_Eenditem_flag ='N' THEN
2979 entity_segment :=entity_segment||'ENDASSEMBLY:';
2980 l_Eenditem_flag :='Y';
2981 ELSIF l_folder_object_2='MSC_WRKSH_RSLT_ORDER' AND l_Eorder_flag ='N' THEN
2982 entity_segment :=entity_segment||'ORDER:';
2983 l_Eorder_flag :='Y';
2984 ELSIF l_folder_object_2='MSC_WRKSH_RSLT_DEMAND' AND l_Edemand_flag ='N' THEN
2985 entity_segment :=entity_segment||'DEMAND:';
2986 l_Edemand_flag :='Y';
2987 END IF;
2988 END LOOP;
2989 /***select end**/
2990 /***Other start***/
2991 FOR c_criteria_other_row IN c_criteria_other
2992 LOOP
2993 l_field_name_3 := c_criteria_other_row.field_name ;
2994 l_hidden_field := c_criteria_other_row.hidden_from_field ;
2995 l_from_field := c_criteria_other_row.from_field ;
2996 l_folder_object_3 := c_criteria_other_row.folder_object ;
2997 --debug_plan_event('l_folder_object_3'||l_folder_object_3);
2998 --debug_plan_event('l_field_name_3'||l_field_name_3);
2999 IF l_folder_object_3 ='MSC_WRKSH_FLTR_OTHER' THEN
3000 if l_field_name_3 ='ITEM_CATEGORY_SET1' THEN
3001 l_omic1_flag :='Y';
3002 IF l_item_flag ='N' THEN
3003 from_clause_segment :=from_clause_segment||',msc_system_items i';
3004 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
3005 l_item_flag :='Y';
3006 END IF;
3007 IF l_mic1_flag ='N' THEN
3008 l_mic1_flag :='Y';
3009 from_clause_segment :=from_clause_segment||',msc_item_categories mic1';
3010 where_clause_segment:= where_clause_segment|| ' and i.sr_instance_id=mic1.sr_instance_id and i.organization_id=mic1.organization_id and i.inventory_item_id=mic1.inventory_item_id and mic1.category_set_id='||l_hidden_field;
3011 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic1';
3012 where_clause_dmd:= where_clause_dmd|| ' and i.sr_instance_id=mic1.sr_instance_id and i.organization_id=mic1.organization_id and i.inventory_item_id=mic1.inventory_item_id and mic1.category_set_id='||l_hidden_field;
3013 ELSIF l_mic1_flag ='Y' THEN
3014 where_clause_segment:= where_clause_segment|| ' and mic1.category_set_id='||l_hidden_field;
3015 where_clause_dmd:= where_clause_dmd|| ' and mic1.category_set_id='||l_hidden_field;
3016 END IF;
3017 -- debug_plan_event('l_mic1_flag'||' and mic1.category_set_id='||l_hidden_field);
3018 elsif l_field_name_3 ='ITEM_CATEGORY_SET2' THEN
3019 l_omic2_flag :='Y';
3020 IF l_item_flag ='N' THEN
3021 from_clause_segment :=from_clause_segment||',msc_system_items i';
3022 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
3023 l_item_flag :='Y';
3024 END IF;
3025 IF l_mic2_flag ='N' THEN
3026 l_mic2_flag :='Y';
3027 from_clause_segment :=from_clause_segment||',msc_item_categories mic2';
3028 where_clause_segment:= where_clause_segment|| ' and i.sr_instance_id=mic2.sr_instance_id and i.organization_id=mic1.organization_id and i.inventory_item_id=mic2.inventory_item_id and mic2.category_set_id='||l_hidden_field;
3029 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic2';
3030 where_clause_dmd:= where_clause_dmd|| ' and i.sr_instance_id=mic2.sr_instance_id and i.organization_id=mic2.organization_id and i.inventory_item_id=mic2.inventory_item_id and mic1.category_set_id='||l_hidden_field;
3031 ELSIF l_mic2_flag ='Y' THEN
3032 where_clause_segment:= where_clause_segment|| ' and mic2.category_set_id='||l_hidden_field;
3033 where_clause_dmd:= where_clause_dmd|| ' and mic2.category_set_id='||l_hidden_field;
3034 END IF;
3035 elsif l_field_name_3 ='ITEM_CATEGORY_SET3' THEN
3036 l_omic3_flag :='Y';
3037 IF l_item_flag ='N' THEN
3038 from_clause_segment :=from_clause_segment||',msc_system_items i';
3039 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
3040 l_item_flag :='Y';
3041 END IF;
3042 IF l_mic3_flag ='N' THEN
3043 l_mic3_flag :='Y';
3044 from_clause_segment :=from_clause_segment||',msc_item_categories mic3';
3045 where_clause_segment:= where_clause_segment|| ' and i.sr_instance_id=mic3.sr_instance_id and i.organization_id=mic3.organization_id and i.inventory_item_id=mic3.inventory_item_id and mic3.category_set_id='||l_hidden_field;
3046 from_clause_dmd :=from_clause_dmd||',msc_item_categories mic3';
3047 where_clause_dmd:= where_clause_dmd|| ' and i.sr_instance_id=mic3.sr_instance_id and i.organization_id=mic3.organization_id and i.inventory_item_id=mic3.inventory_item_id and mic1.category_set_id='||l_hidden_field;
3048 ELSIF l_mic3_flag ='Y' THEN
3049 where_clause_segment:= where_clause_segment|| ' and mic3.category_set_id='||l_hidden_field;
3050 where_clause_dmd:= where_clause_dmd|| ' and mic3.category_set_id='||l_hidden_field;
3051 END IF;
3052 elsif l_field_name_3 ='WHEREUSED_CATEGORY_SET1' THEN
3053 l_owu1_flag :='Y';
3054 IF l_item_flag ='N' THEN
3055 from_clause_segment :=from_clause_segment||',msc_system_items i';
3056 l_item_flag :='Y';
3057 END IF;
3058 IF l_wu1_flag ='N' THEN
3059 l_wu1_flag :='Y';
3060 from_clause_segment :=from_clause_segment||',msc_item_categories wu1';
3061 where_clause_segment:= where_clause_segment|| ' and mfpw2.sr_instance_id=wu1.sr_instance_id and mfpw2.organization_id=wu1.organization_id and mfpw2.inventory_item_id=wu1.inventory_item_id
3062 and wu1.category_set_id='||l_hidden_field;
3063 elsIF l_wu1_flag ='Y' THEN
3064 where_clause_segment:= where_clause_segment|| ' and wu1.category_set_id='||l_hidden_field;
3065 END IF;
3066 IF l_order_flag ='N' THEN
3067 l_order_flag :='Y';
3068 from_clause_segment :=from_clause_segment||',msc_supplies sup';
3069 where_clause_segment := where_clause_segment||' and i.plan_id=sup.plan_id and i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id and i.inventory_item_id=sup.inventory_item_id ';
3070 END IF;
3071 IF l_mfpw1_flag ='N' AND l_mfpw2_flag='N' THEN
3072 l_mfpw1_flag :='Y';
3073 l_mfpw2_flag :='Y';
3074 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw1,msc_full_pegging mfpw2 ';
3075 where_clause_segment:= where_clause_segment|| ' and mfpw1.plan_id=sup.plan_id and mfpw1.transaction_id=sup.transaction_id and mfpw1.plan_id=mfpw2.plan_id and mfpw1.prev_pegging_id=mfpw2.pegging_id ';
3076 END IF;
3077 elsif l_field_name_3 ='WHEREUSED_CATEGORY_SET2' THEN
3078 l_owu2_flag :='Y';
3079 IF l_item_flag ='N' THEN
3080 from_clause_segment :=from_clause_segment||',msc_system_items i';
3081 l_item_flag :='Y';
3082 END IF;
3083 IF l_wu2_flag ='N' THEN
3084 l_wu2_flag :='Y';
3085 from_clause_segment :=from_clause_segment||',msc_item_categories wu2';
3086 where_clause_segment:= where_clause_segment|| ' and mfpw2.sr_instance_id=wu2.sr_instance_id
3087 and mfpw2.organization_id=wu2.organization_id and mfpw2.inventory_item_id=wu2.inventory_item_id and wu2.category_set_id='||l_hidden_field;
3088 elsIF l_wu2_flag ='Y' THEN
3089 where_clause_segment:= where_clause_segment|| ' and wu2.category_set_id='||l_hidden_field;
3090 END IF;
3091 IF l_order_flag ='N' THEN
3092 l_order_flag :='Y';
3093 from_clause_segment :=from_clause_segment||',msc_supplies sup';
3094 where_clause_segment := where_clause_segment||' and i.plan_id=sup.plan_id
3095 and i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id
3096 and i.inventory_item_id=sup.inventory_item_id ';
3097 END IF;
3098 IF l_mfpw1_flag ='N' AND l_mfpw2_flag='N' THEN
3099 l_mfpw1_flag :='Y';
3100 l_mfpw2_flag :='Y';
3101 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw1,msc_full_pegging mfpw2 ';
3102 where_clause_segment:= where_clause_segment|| ' and mfpw1.plan_id=sup.plan_id
3103 and mfpw1.transaction_id=sup.transaction_id and mfpw1.plan_id=mfpw2.plan_id
3104 and mfpw1.prev_pegging_id=mfpw2.pegging_id ';
3105 END IF;
3106 elsif l_field_name_3 ='WHEREUSED_CATEGORY_SET3' THEN
3107 l_owu3_flag :='Y';
3108 IF l_item_flag ='N' THEN
3109 from_clause_segment :=from_clause_segment||',msc_system_items i';
3110 l_item_flag :='Y';
3111 END IF;
3112 IF l_wu3_flag ='N' THEN
3113 l_wu3_flag :='Y';
3114 from_clause_segment :=from_clause_segment||',msc_item_categories wu3';
3115 where_clause_segment:= where_clause_segment|| ' and mfpw2.sr_instance_id=wu3.sr_instance_id and mfpw2.organization_id=wu3.organization_id and mfpw2.inventory_item_id=wu3.inventory_item_id and wu3.category_set_id='||l_hidden_field;
3116 elsIF l_wu3_flag ='Y' THEN
3117 where_clause_segment:= where_clause_segment|| ' and wu3.category_set_id='||l_hidden_field;
3118 END IF;
3119 IF l_order_flag ='N' THEN
3120 l_order_flag :='Y';
3121 from_clause_segment :=from_clause_segment||',msc_supplies sup';
3122 where_clause_segment := where_clause_segment||' and i.plan_id=sup.plan_id
3123 and i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id
3124 and i.inventory_item_id=sup.inventory_item_id ';
3125 END IF;
3126 IF l_mfpw1_flag ='N' AND l_mfpw2_flag='N' THEN
3127 l_mfpw1_flag :='Y';
3128 l_mfpw2_flag :='Y';
3129 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw1,msc_full_pegging mfpw2 ';
3130 where_clause_segment:= where_clause_segment|| ' and mfpw1.plan_id=sup.plan_id
3131 and mfpw1.transaction_id=sup.transaction_id and mfpw1.plan_id=mfpw2.plan_id and mfpw1.prev_pegging_id=mfpw2.pegging_id ';
3132 END IF;
3133 END IF;
3134 if l_field_name_3 like 'WHEREUSED_CATEGORY_SET%' THEN
3135 entity_segment:=entity_segment||'WHERE_USED:';
3136 if l_mfpw2_flag='N' and l_mfpw1_flag='Y' THEN
3137 l_mfpw2_flag:='Y';
3138 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw2';
3139 where_clause_segment:=where_clause_segment||' and mfpw1.plan_id=mfpw2.plan_id and mfpw1.prev_pegging_id=mfpw2.pegging_id ';
3140 end if;
3141 end if;
3142 END IF;
3143 END LOOP;
3144 --debug_plan_event('where_clause_segment'||where_clause_segment);
3145
3146 /**Other End***/
3147
3148 /*Category check for filter/Result/Other tab*/
3149 IF (l_wmic1_flag='Y' and l_omic1_flag='N') then
3150 l_return_code:=l_return_code||' MSC_CATEGORY1_MSG';
3151 END IF;
3152
3153 IF l_wmic2_flag='Y' and l_omic2_flag='N' then
3154 l_return_code:=l_return_code||' MSC_CATEGORY2_MSG';
3155 END IF;
3156
3157 IF l_wmic3_flag='Y' and l_omic3_flag='N' then
3158 l_return_code:=l_return_code||' MSC_CATEGORY3_MSG';
3159 END IF;
3160
3161 /* IF l_wmic4_flag='Y' and l_omic4_flag='N' then
3162 l_return_code:=l_return_code||' MSC_CATEGORY4_MSG';
3163 END IF;
3164
3165 IF l_wmic5_flag='Y' and l_omic5_flag='N' then
3166 l_return_code:=l_return_code||' MSC_CATEGORY5_MSG';
3167 END IF;
3168
3169 IF l_wmic6_flag='Y' and l_omic6_flag='N' then
3170 l_return_code:=l_return_code||' MSC_CATEGORY6_MSG';
3171 END IF;*/
3172
3173 IF l_wwu1_flag='Y' and l_owu1_flag='N' then
3174 l_return_code:=l_return_code||' MSC_WCATEGORY1_MSG';
3175 END IF;
3176
3177 IF l_wwu2_flag='Y' and l_owu2_flag='N' then
3178 l_return_code:=l_return_code||' MSC_WCATEGORY2_MSG';
3179 END IF;
3180
3181 IF l_wwu3_flag='Y' and l_owu3_flag='N' then
3182 l_return_code:=l_return_code||' MSC_WCATEGORY3_MSG';
3183 END IF;
3184
3185
3186 /***Common joins for Result or Filter tab selection of entity*/
3187 IF entity_segment LIKE '%ITEM%' THEN
3188 IF l_item_flag ='N' THEN
3189 from_clause_segment :=from_clause_segment||',msc_system_items i';
3190 from_clause_dmd :=from_clause_dmd||',msc_system_items i';
3191 l_item_flag :='Y';
3192 END IF;
3193 IF entity_segment LIKE '%ENDASSEMBLY%' AND entity_segment NOT LIKE '%ORDER%' AND entity_segment NOT LIKE '%DEMAND%' THEN
3194 IF l_order_flag ='N' THEN
3195 l_order_flag :='Y';
3196 from_clause_segment :=from_clause_segment||',msc_supplies sup';
3197 where_clause_segment := where_clause_segment||' and i.plan_id=sup.plan_id
3198 and i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id and i.inventory_item_id=sup.inventory_item_id ';
3199 from_clause_dmd :=from_clause_dmd||' ,msc_demands dmd';
3200 where_clause_dmd := where_clause_dmd|| ' and i.plan_id=dmd.plan_id and i.sr_instance_id=dmd.sr_instance_id and i.organization_id=dmd.organization_id and i.inventory_item_id=dmd.inventory_item_id ';
3201 END IF;
3202 IF l_enditem_flag ='N' THEN
3203 from_clause_segment :=from_clause_segment||',msc_system_items ei';
3204 from_clause_dmd :=from_clause_dmd||',msc_system_items ei';
3205 l_enditem_flag :='Y';
3206 END IF;
3207 IF l_mfpw1_flag ='N' AND l_mfpe1_flag='N' THEN
3208 l_mfpw1_flag :='Y';
3209 l_mfpe1_flag :='Y';
3210 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw1,msc_full_pegging mfpe1';
3211 where_clause_segment := where_clause_segment|| ' and mfpe1.prev_pegging_id is null and mfpe1.plan_id=ei.plan_id and mfpe1.sr_instance_id=ei.sr_instance_id and mfpe1.organization_id=ei.organization_id';
3212 where_clause_segment := where_clause_segment|| ' and mfpe1.inventory_item_id=ei.inventory_item_id and mfpw1.plan_id=sup.plan_id and mfpw1.transaction_id=sup.transaction_id and mfpw1.plan_id=mfpe1.plan_id and mfpw1.end_pegging_id=mfpe1.end_pegging_id ';
3213 from_clause_dmd :=from_clause_dmd||',msc_full_pegging mfpw1,msc_full_pegging mfpe1';
3214 where_clause_dmd := where_clause_dmd|| ' and mfpe1.prev_pegging_id is null and mfpe1.plan_id=ei.plan_id and mfpe1.sr_instance_id=ei.sr_instance_id and mfpe1.organization_id=ei.organization_id';
3215 where_clause_dmd := where_clause_dmd|| ' and mfpe1.inventory_item_id=ei.inventory_item_id and mfpw1.plan_id=dmd.plan_id and mfpw1.transaction_id=dmd.demand_id and mfpw1.plan_id=mfpe1.plan_id and mfpw1.end_pegging_id=mfpe1.end_pegging_id ';
3216 END IF;
3217 END IF;
3218 IF entity_segment NOT LIKE '%ENDASSEMBLY%' AND entity_segment LIKE '%ORDER%' AND entity_segment NOT LIKE '%DEMAND%' THEN
3219 IF l_order_flag ='N' THEN
3220 l_order_flag :='Y';
3221 from_clause_segment :=from_clause_segment||',msc_supplies sup';
3222 where_clause_segment := where_clause_segment||' and i.plan_id=sup.plan_idand i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id and i.inventory_item_id=sup.inventory_item_id ';
3223 from_clause_dmd :=from_clause_dmd||' ,msc_demands dmd';
3224 where_clause_dmd := where_clause_dmd|| ' and i.plan_id=dmd.plan_id and i.sr_instance_id=dmd.sr_instance_id and i.organization_id=dmd.organization_id and i.inventory_item_id=dmd.inventory_item_id ';
3225 END IF;
3226 END IF;
3227 IF entity_segment NOT LIKE '%ENDASSEMBLY%' AND entity_segment NOT LIKE '%ORDER%' AND entity_segment LIKE '%DEMAND%' THEN
3228 IF l_order_flag ='N' THEN
3229 l_order_flag :='Y';
3230 from_clause_segment :=from_clause_segment||',msc_supplies sup';
3231 where_clause_segment := where_clause_segment||' and i.plan_id=sup.plan_id
3232 and i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id and i.inventory_item_id=sup.inventory_item_id ';
3233 END IF;
3234 IF l_demand_flag ='N' THEN
3235 l_demand_flag :='Y';
3236 from_clause_segment :=from_clause_segment||',msc_demands dem';
3237 END IF;
3238 IF l_enditem_flag ='N' THEN
3239 from_clause_segment :=from_clause_segment||',msc_system_items ei';
3240 l_enditem_flag :='Y';
3241 END IF;
3242 IF l_mfpw1_flag ='N' AND l_mfpe1_flag='N' THEN
3243 l_mfpw1_flag :='Y';
3244 l_mfpe1_flag :='Y';
3245 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw1,msc_full_pegging mfpe1';
3246 where_clause_segment := where_clause_segment|| ' and mfpe1.prev_pegging_id is null
3247 and mfpe1.plan_id=ei.plan_id and mfpe1.sr_instance_id=ei.sr_instance_id
3248 and mfpe1.organization_id=ei.organization_id and mfpe1.inventory_item_id=ei.inventory_item_id
3249 and mfpw1.plan_id=sup.plan_id and mfpw1.transaction_id=sup.transaction_id and mfpw1.plan_id=mfpe1.plan_id and mfpw1.end_pegging_id=mfpe1.end_pegging_id ';
3250 END IF;
3251 END IF;
3252 IF entity_segment LIKE '%ENDASSEMBLY%' AND entity_segment NOT LIKE '%ORDER%' AND entity_segment LIKE '%DEMAND%' THEN
3253 IF l_order_flag ='N' THEN
3254 l_order_flag :='Y';
3255 from_clause_segment :=from_clause_segment||',msc_supplies sup';
3256 where_clause_segment := where_clause_segment||' and i.plan_id=sup.plan_id and i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id and i.inventory_item_id=sup.inventory_item_id ';
3257 END IF;
3258 IF l_demand_flag ='N' THEN
3259 l_demand_flag :='Y';
3260 from_clause_segment :=from_clause_segment||',msc_demands dem';
3261 END IF;
3262 IF l_enditem_flag ='N' THEN
3263 from_clause_segment :=from_clause_segment||',msc_system_items ei';
3264 l_enditem_flag :='Y';
3265 END IF;
3266 IF l_mfpw1_flag ='N' AND l_mfpe1_flag='N' THEN
3267 l_mfpw1_flag :='Y';
3268 l_mfpe1_flag :='Y';
3269 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw1,msc_full_pegging mfpe1';
3270 where_clause_segment := where_clause_segment|| ' and mfpe1.prev_pegging_id is null and mfpe1.plan_id=ei.plan_id and mfpe1.sr_instance_id=ei.sr_instance_id and mfpe1.organization_id=ei.organization_id';
3271 where_clause_segment := where_clause_segment|| ' and mfpe1.inventory_item_id=ei.inventory_item_id and mfpw1.plan_id=sup.plan_id and mfpw1.transaction_id=sup.transaction_id and mfpw1.plan_id=mfpe1.plan_id and mfpw1.end_pegging_id=mfpe1.end_pegging_id ';
3272 END IF;
3273 END IF;
3274 IF entity_segment LIKE '%ENDASSEMBLY%' AND entity_segment LIKE '%ORDER%' AND entity_segment NOT LIKE '%DEMAND%' THEN
3275 IF l_order_flag ='N' THEN
3276 l_order_flag :='Y';
3277 from_clause_segment :=from_clause_segment||',msc_supplies sup';
3278 where_clause_segment := where_clause_segment||' and i.plan_id=sup.plan_id and i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id and i.inventory_item_id=sup.inventory_item_id ';
3279 from_clause_dmd :=from_clause_dmd||' ,msc_demands dmd';
3280 where_clause_dmd := where_clause_dmd|| ' and i.plan_id=dmd.plan_id and i.sr_instance_id=dmd.sr_instance_id and i.organization_id=dmd.organization_id and i.inventory_item_id=dmd.inventory_item_id ';
3281 END IF;
3282 IF l_enditem_flag ='N' THEN
3283 from_clause_segment :=from_clause_segment||',msc_system_items ei';
3284 from_clause_dmd :=from_clause_dmd||' ,msc_system_items ei';
3285 l_enditem_flag :='Y';
3286 END IF;
3287 IF l_mfpw1_flag ='N' AND l_mfpe1_flag='N' THEN
3288 l_mfpw1_flag :='Y';
3289 l_mfpe1_flag :='Y';
3290 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw1,msc_full_pegging mfpe1';
3291 where_clause_segment := where_clause_segment|| ' and mfpe1.prev_pegging_id is null and mfpe1.plan_id=ei.plan_id and mfpe1.sr_instance_id=ei.sr_instance_id and mfpe1.organization_id=ei.organization_id';
3292 where_clause_segment := where_clause_segment|| ' and mfpe1.inventory_item_id=ei.inventory_item_id and mfpw1.plan_id=sup.plan_id and mfpw1.transaction_id=sup.transaction_id and mfpw1.plan_id=mfpe1.plan_id and mfpw1.end_pegging_id=mfpe1.end_pegging_id ';
3293 from_clause_dmd :=from_clause_dmd||',msc_full_pegging mfpw1,msc_full_pegging mfpe1';
3294 where_clause_dmd := where_clause_dmd|| ' and mfpe1.prev_pegging_id is null and mfpe1.plan_id=ei.plan_id and mfpe1.sr_instance_id=ei.sr_instance_id and mfpe1.organization_id=ei.organization_id';
3295 where_clause_dmd := where_clause_dmd|| ' and mfpe1.inventory_item_id=ei.inventory_item_id and mfpw1.plan_id=dmd.plan_id and mfpw1.transaction_id=dmd.demand_id and mfpw1.plan_id=mfpe1.plan_id and mfpw1.end_pegging_id=mfpe1.end_pegging_id ';
3296 END IF;
3297 END IF;
3298 IF entity_segment LIKE '%ENDASSEMBLY%' AND entity_segment LIKE '%ORDER%' AND entity_segment LIKE '%DEMAND%' THEN
3299 IF l_enditem_flag ='N' THEN
3300 from_clause_segment :=from_clause_segment||',msc_system_items ei';
3301 l_enditem_flag :='Y';
3302 END IF;
3303 IF l_mfpw1_flag ='N' AND l_mfpe1_flag='N' THEN
3304 l_mfpw1_flag :='Y';
3305 l_mfpe1_flag :='Y';
3306 from_clause_segment :=from_clause_segment||',msc_full_pegging mfpw1,msc_full_pegging mfpe1';
3307 where_clause_segment := where_clause_segment||
3308 ' and mfpe1.prev_pegging_id is null and mfpe1.plan_id=ei.plan_id and mfpe1.sr_instance_id=ei.sr_instance_id
3309 and mfpe1.organization_id=ei.organization_id';
3310 where_clause_segment := where_clause_segment||' and mfpe1.inventory_item_id=ei.inventory_item_id and mfpw1.plan_id=sup.plan_id and mfpw1.transaction_id=sup.transaction_id';
3311 where_clause_segment := where_clause_segment||' and mfpw1.plan_id=mfpe1.plan_id and mfpw1.end_pegging_id=mfpe1.end_pegging_id and mfpe1.plan_id=dem.plan_id (+) and mfpe1.demand_id=dem.demand_id (+) ';
3312 END IF;
3313 IF l_demand_flag ='N' THEN
3314 l_demand_flag :='Y';
3315 from_clause_segment :=from_clause_segment||',msc_demands dem';
3316 END IF;
3317 IF l_order_flag ='N' THEN
3318 l_order_flag :='Y';
3319 from_clause_segment :=from_clause_segment||',msc_supplies sup';
3320 where_clause_segment := where_clause_segment||' and i.plan_id=sup.plan_id and i.sr_instance_id=sup.sr_instance_id and i.organization_id=sup.organization_id and i.inventory_item_id=sup.inventory_item_id ';
3321 END IF;
3322 END IF;
3323 END IF;
3324 IF entity_segment LIKE '%ITEM%' THEN
3325 where_clause_segment := where_clause_segment||' and i.plan_id ='||p_plan_id;
3326 where_clause_dmd := where_clause_dmd||' and i.plan_id ='||p_plan_id;
3327 end if;
3328
3329 IF entity_segment LIKE '%ENDASSEMBLY%' THEN
3330 where_clause_segment := where_clause_segment||' and ei.plan_id ='||p_plan_id;
3331 where_clause_dmd := where_clause_dmd||' and ei.plan_id ='||p_plan_id;
3332 end if;
3333 IF entity_segment LIKE '%ORDER%' THEN
3334 where_clause_segment := where_clause_segment||' and sup.plan_id ='||p_plan_id;
3335 where_clause_dmd := where_clause_dmd||' and dmd.plan_id ='||p_plan_id;
3336 end if;
3337 IF entity_segment LIKE '%DEMAND%' THEN
3338 where_clause_segment := where_clause_segment||' and dem.plan_id ='||p_plan_id;
3339 end if;
3340 /***Common joins for Result or Filter tab selection of entity- end*/
3341 where_clause_dmd2:=where_clause;
3342 /***select demands**/
3343 IF (entity_segment NOT LIKE '%DEMAND%') and (entity_segment NOT LIKE '%WHERE_USED%') AND
3344 (entity_segment <> 'ITEM:') and (entity_segment <> 'ENDASSEMBLY:') then
3345 IF where_clause IS NOT NULL THEN
3346 where_clause_dmd2:=REPLACE(REPLACE(where_clause_dmd2,'sup.FIRM_PLANNED_TYPE','decode(dmd.ORG_FIRM_FLAG,1,1,3,2,2)'),'sup.NEW_SCHEDULE_DATE','dmd.USING_ASSEMBLY_DEMAND_DATE');
3347 END IF;
3348 IF where_clause IS NOT NULL AND where_clause_dmd IS NOT NULL THEN
3349 where_clause_dmd :=where_clause_dmd2||' '||where_clause_dmd;
3350 elsif where_clause IS NULL AND where_clause_dmd IS NOT NULL THEN
3351 where_clause_dmd :=where_clause_dmd2||' 1=1 '||where_clause_dmd;
3352 elsif where_clause IS NOT NULL AND where_clause_dmd IS NULL THEN
3353 where_clause_dmd :=where_clause_dmd2;
3354 ELSE
3355 where_clause_dmd :=' 1=1 ';
3356 END IF;
3357 from_clause_dmd :=' FROM '||substr(from_clause_dmd,instr(from_clause_dmd,',',1)+1);
3358 p_where_clause_dmd :=' WHERE '||where_clause_dmd;
3359 p_from_clause_dmd :=from_clause_dmd;
3360 p_select_clause_dmd :=select_clause_dmd;
3361 else
3362 p_where_clause_dmd :=NULL;
3363 p_from_clause_dmd :=NULL;
3364 p_select_clause_dmd :=NULL;
3365 END IF;
3366
3367 IF where_clause IS NOT NULL AND where_clause_segment IS NOT NULL THEN
3368 where_clause :=where_clause||' '||where_clause_segment;
3369 elsif where_clause IS NULL AND where_clause_segment IS NOT NULL THEN
3370 where_clause :=where_clause||' 1=1 '||where_clause_segment;
3371 elsif where_clause IS NOT NULL AND where_clause_segment IS NULL THEN
3372 where_clause :=where_clause;
3373 ELSE
3374 where_clause :=' 1=1 ';
3375 END IF;
3376
3377 from_clause_segment :=' FROM '||substr(from_clause_segment,instr(from_clause_segment,',',1)+1);
3378 p_where_clause :=' WHERE '||where_clause;
3379 p_from_clause :=from_clause_segment;
3380 p_select_clause :=select_clause_segment;
3381 P_column_prompts:=column_prompts_SEGMENT;
3382 IF LTRIM(select_clause_segment,' ') IS NULL THEN
3383 l_return_code:=l_return_code||' MSC_RESULTLAYOUT_MSG';
3384 end if;
3385 p_return_code :=l_return_code;
3386 END build_wrksh_query;
3387
3388 PROCEDURE Export_wsh
3389 (
3390 p_query_name IN VARCHAR2,
3391 p_plan_id IN NUMBER,
3392 p_query_id IN NUMBER,
3393 p_file_id OUT NOCOPY NUMBER,
3394 p_return_code OUT NOCOPY VARCHAR2
3395 )
3396 IS
3397 l_return_code VARCHAR2(10000):=NULL;
3398 output_string VARCHAR2(32767);
3399 file_open BOOLEAN := FALSE;
3400 db_file NUMBER;
3401 mime_type VARCHAR2(255);
3402 i INTEGER :=1;
3403 debug_export BOOLEAN := FALSE;
3404 delimiter VARCHAR2(4) := ','; -- Default is comma, export ER for R12.2
3405 quote VARCHAR2(4) := ''; -- Default is null for tab. export ER for R12.2
3406 clmn_prompts VARCHAR2(32000) :=NULL;
3407 exist_flag VARCHAR2(1) :='N';
3408 where_clause VARCHAR2(32000):=NULL;
3409 from_clause VARCHAR2(32000):=NULL;
3410 select_clause VARCHAR2(32000) :=NULL;
3411 prompts_clause VARCHAR2(32000) :=NULL;
3412 where_clause_dmd VARCHAR2(32000):=NULL;
3413 from_clause_dmd VARCHAR2(32000):=NULL;
3414 select_clause_dmd VARCHAR2(32000) :=NULL;
3415 count_row NUMBER :=0;
3416 count_row1 NUMBER :=0;
3417 query_stmt VARCHAR2(32767):=NULL;
3418 l_query_name VARCHAR2(80) :=p_query_name;
3419 l_plan_id NUMBER := p_plan_id;
3420 l_query_id NUMBER := p_query_id;
3421 filename VARCHAR2(256);
3422 plan_name VARCHAR2(50);
3423 wsh_id NUMBER :=0;
3424 fileid NUMBER :=0;
3425 TYPE cursor_type
3426 IS
3427 REF
3428 CURSOR;
3429 exp_cursor cursor_type;
3430 TYPE dyn_record
3431 IS
3432 RECORD
3433 (
3434 output VARCHAR2(32767) );
3435 dyn_rec dyn_record;
3436 BEGIN
3437
3438 IF l_plan_id >0 THEN
3439 BEGIN
3440 SELECT COMPILE_DESIGNATOR INTO plan_name FROM MSC_PLANS WHERE plan_id=l_plan_id;
3441 EXCEPTION
3442 WHEN OTHERS THEN
3443 plan_name:=NULL;
3444 l_return_code:=l_return_code||' Error getting plan name for plan id: '||l_plan_id ||SQLERRM;
3445 END;
3446 filename :=plan_name||'-'||l_query_name;
3447 BEGIN
3448 SELECT COUNT(1) INTO count_row FROM fnd_lobs WHERE file_name=filename;
3449 IF count_row >0 THEN
3450 exist_flag:='Y';
3451 END IF;
3452 EXCEPTION
3453 WHEN no_data_found THEN
3454 exist_flag:='N';
3455 WHEN too_many_rows THEN
3456 exist_flag:='Y';
3457 l_return_code:=l_return_code||' Too many rows in fnd_lobs for file name'|| filename;
3458 WHEN OTHERS THEN
3459 exist_flag:=NULL;
3460 l_return_code:=l_return_code||' Error for file name'|| filename||' '||SQLERRM;
3461 END;
3462 END IF;
3463 IF exist_flag ='Y' THEN
3464 BEGIN
3465 DELETE FROM fnd_lobs WHERE file_name=filename;
3466 exist_flag :='N';
3467 EXCEPTION
3468 WHEN OTHERS THEN
3469 exist_flag:=NULL;
3470 l_return_code:=l_return_code||' Error getting file id for file name'|| filename||' '||SQLERRM;
3471 END;
3472
3473 BEGIN
3474 select count(1) into count_row1 from msc_plan_queries
3475 where plan_id=l_plan_id
3476 and query_id=l_query_id;
3477 EXCEPTION
3478 WHEN NO_DATA_FOUND THEN
3479 count_row1:=0;
3480 WHEN OTHERS THEN
3481 count_row1:=null;
3482 l_return_code:=l_return_code||' Error getting file id (for delete) in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3483 END;
3484 If count_row1=1 then
3485 BEGIN
3486 update msc_plan_queries
3487 set file_id=NULL,
3488 LAST_UPDATED_BY=fnd_global.user_id,
3489 LAST_UPDATE_DATE =SYSDATE,
3490 LAST_UPDATE_LOGIN=fnd_global.user_id
3491 where plan_id=l_plan_id
3492 and query_id=l_query_id;
3493 EXCEPTION
3494 WHEN OTHERS THEN
3495 -- wsh_id:=-123;
3496 l_return_code:=l_return_code||' Error updating file id (for delete) in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3497 END;
3498 end if;
3499
3500 END IF;
3501 IF exist_flag ='N' THEN
3502 mime_type := NVL(fnd_profile.value('FND_EXPORT_MIME_TYPE'), 'text/tab-separated-values');
3503 -- mime_type := 'text/comma-separated-values';
3504 db_file := fnd_gfm.file_create(file_name=>filename,content_type => mime_type, program_name => 'export');
3505 file_open := TRUE;
3506 output_string := NULL;
3507 build_wrksh_query(l_plan_id, l_query_id,
3508 NULL,
3509 select_clause,
3510 from_clause,
3511 where_clause,
3512 prompts_clause,
3513 select_clause_dmd,
3514 from_clause_dmd,
3515 where_clause_dmd,
3516 l_return_code);
3517 IF select_clause_dmd IS NOT NULL AND from_clause_dmd IS NOT NULL AND where_clause_dmd IS NOT NULL THEN
3518 query_stmt:=select_clause||' '||from_clause||' '||where_clause||' '||select_clause_dmd||' '||from_clause_dmd||' '||where_clause_dmd;
3519 ELSE
3520 query_stmt:=select_clause||' '||from_clause||' '||where_clause;
3521 END IF;
3522
3523 IF l_return_code IS NULL THEN
3524 clmn_prompts:=prompts_clause;
3525 fnd_gfm.file_write_line(db_file,clmn_prompts);
3526 OPEN exp_cursor FOR query_stmt;
3527 LOOP
3528 FETCH exp_cursor INTO dyn_rec;
3529 EXIT
3530 WHEN exp_cursor%NOTFOUND;
3531 /* IF i=1 THEN
3532 -- debug_plan_event('prompts_clause'||prompts_clause);
3533 -- SELECT prompts_clause INTO clmn_prompts FROM dual;
3534 -- debug_plan_event('clmn_prompts'||clmn_prompts);
3535 clmn_prompts:=prompts_clause;
3536
3537 fnd_gfm.file_write_line(db_file,clmn_prompts);
3538 i:=i+1;
3539 END IF;*/
3540 output_string:=dyn_rec.output;
3541 fnd_gfm.file_write_line(db_file,output_string);
3542 END LOOP;
3543 CLOSE exp_cursor;
3544 file_open := FALSE;
3545 END IF;
3546 BEGIN
3547 SELECT NVL(file_id,-123) INTO wsh_id FROM fnd_lobs WHERE file_name=filename;
3548 EXCEPTION
3549 WHEN OTHERS THEN
3550 wsh_id:=-123;
3551 l_return_code:=l_return_code||' Error getting file id for file name After file export '|| filename||' '||SQLERRM;
3552 END;
3553 IF wsh_id>0 then
3554 BEGIN
3555 select count(1) into count_row1 from msc_plan_queries
3556 where plan_id=l_plan_id
3557 and query_id=l_query_id;
3558 EXCEPTION
3559 WHEN NO_DATA_FOUND THEN
3560 count_row1:=0;
3561 WHEN OTHERS THEN
3562 count_row1:=null;
3563 l_return_code:=l_return_code||' Error getting file id in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3564 END;
3565 If count_row1=1 then
3566 BEGIN
3567 update msc_plan_queries
3568 set file_id=wsh_id,
3569 LAST_UPDATED_BY=fnd_global.user_id,
3570 LAST_UPDATE_DATE =SYSDATE,
3571 LAST_UPDATE_LOGIN=fnd_global.user_id
3572 where plan_id=l_plan_id
3573 and query_id=l_query_id;
3574 EXCEPTION
3575 WHEN OTHERS THEN
3576 wsh_id:=-123;
3577 l_return_code:=l_return_code||' Error updating file id in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3578 END;
3579 elsif count_row1=0 then
3580 BEGIN
3581 INSERT INTO msc_plan_queries
3582 (PLAN_ID,
3583 QUERY_ID,
3584 LAST_UPDATE_DATE,
3585 LAST_UPDATED_BY ,
3586 CREATION_DATE,
3587 CREATED_BY,
3588 LAST_UPDATE_LOGIN,
3589 FILE_ID )
3590 VALUES (l_plan_id,
3591 l_query_id,
3592 SYSDATE,
3593 fnd_global.user_id,
3594 SYSDATE,
3595 fnd_global.user_id,
3596 fnd_global.login_id,
3597 wsh_id);
3598 EXCEPTION
3599 WHEN OTHERS THEN
3600 wsh_id:=-123;
3601 l_return_code:=l_return_code||' Error Inserting record in msc_plan_queries for file name '|| filename||' '||SQLERRM;
3602 END;
3603 end if;
3604 end if;
3605 end if;
3606 p_return_code:=l_return_code;
3607 p_file_id:=wsh_id;
3608 COMMIT;
3609
3610 END Export_wsh;
3611
3612 -- ---------------------------------------------
3613 -- This program will be called from
3614 -- MSC_GET_BIS_VALUES.ui_post_plan (MSCBISUB.PLS)
3615 -- ----------------------------------------------
3616
3617 PROCEDURE execute_plan_worksheets(errbuf OUT NOCOPY VARCHAR2,
3618 retcode OUT NOCOPY NUMBER,
3619 p_plan_id IN NUMBER) IS
3620 CURSOR cur_worksheets IS
3621 SELECT plq.query_Id,QUERY_TYPE,pq.query_name
3622 FROM msc_plan_queries plq,
3623 msc_personal_queries pq
3624 WHERE plq.plan_id = p_plan_Id
3625 AND plq.query_id = pq.query_id
3626 AND pq.query_type = 11;
3627
3628 l_errbuf VARCHAR2(2000);
3629 l_retcode NUMBER;
3630 l_file_id NUMBER :=NULL;
3631 l_return_code VARCHAR2(2000) :=NULL;
3632 BEGIN
3633
3634 FOR rec_worksheets IN cur_worksheets LOOP
3635 BEGIN
3636 Export_wsh
3637 (
3638 rec_worksheets.query_name ,
3639 p_plan_id ,
3640 rec_worksheets.query_id ,
3641 l_file_id ,
3642 l_return_code
3643 );
3644 EXCEPTION
3645 WHEN OTHERS THEN
3646 errbuf := 'Error'||l_return_code;
3647 retcode := 1;
3648 END;
3649
3650 END LOOP;
3651
3652 retcode := 0;
3653 EXCEPTION
3654 WHEN OTHERS THEN
3655 errbuf := 'unknown error'||sqlerrm(sqlcode);
3656 retcode := 1;
3657 END execute_plan_worksheets;
3658
3659 FUNCTION get_where_clause_wrksh (sequence NUMBER,
3660 obj_sequence NUMBER,
3661 field_name IN OUT NOCOPY VARCHAR2,
3662 operator NUMBER,
3663 low VARCHAR2,
3664 high VARCHAR2,
3665 hidden_from VARCHAR2,
3666 data_set IN OUT NOCOPY varchar2,
3667 data_type IN OUT NOCOPY VARCHAR2,
3668 lov_type IN NUMBER,
3669 p_match_str IN VARCHAR2,
3670 p_excp_where IN VARCHAR2)
3671 RETURN VARCHAR2 IS
3672 low_value VARCHAR2(200);
3673 high_value VARCHAR2(200);
3674 translated_op VARCHAR2(30);
3675 where_clause_segment VARCHAR2(32000);
3676 BEGIN
3677 --KSA_DEBUG(SYSDATE,'inside...','get_where_clause');
3678 --debug_plan_event('inside...get_where_clause');
3679 -- debug_plan_event('operator '||operator);
3680 IF operator IN (11, 14) THEN
3681
3682 IF data_type IN ('MULTI','ORG') THEN
3683 IF data_type = 'ORG' THEN
3684 data_set := '('||REPLACE(data_set,':',',')||')';
3685 END IF;
3686 END IF;
3687
3688 IF p_excp_where IS NULL
3689 and data_type IN ('CHAR','DATE','NUMBER')
3690 and data_set IS NOT NULL THEN
3691 IF data_type IN ('DATE') THEN
3692 field_name := ' trunc( '||data_set||') ' ;
3693 ELSE
3694 field_name := data_set;
3695 END IF;
3696 ELSIF data_type IN ('DATE') THEN
3697 field_name := ' trunc( '||field_name||') ' ;
3698 END IF;
3699 low_value := '';
3700 ELSE
3701 --little trick to get correct field_name for exceptons
3702 IF p_excp_where IS NULL THEN
3703 if data_type IN ('CHAR','DATE','NUMBER') AND data_set IS NOT NULL THEN
3704 field_name := data_set;
3705 ELSIF data_type IN ('MULTI') THEN
3706 IF ( field_name LIKE '%ITEM_SEGMENTS%' ) THEN
3707 field_name := substr(data_set,1,instr(data_set,'.',1)-1)||'.ITEM_NAME';
3708 ELSIF ( field_name LIKE '%CATEGORY%' ) THEN
3709 field_name := data_set;
3710 ELSIF ( INSTR(field_name,'~') > 0 ) THEN
3711 field_name := SUBSTR(field_name, instr(field_name,'~')+1);
3712 END IF;
3713 END IF;
3714 END IF;
3715
3716 IF operator = 13 THEN
3717 low_value := RTRIM(LTRIM(low));
3718 IF ( SUBSTR(low_value, LENGTH(low_value),1) <> '%') THEN
3719 low_value := low_value||'%';
3720 END IF;
3721 low_value := ''''||REPLACE(low_value, '''', '''''') ||'''';
3722 ELSIF data_type = 'CHAR' THEN
3723 low_value := ''''||REPLACE(low, '''', '''''') ||'''';
3724 IF (operator = 9) OR (operator = 10) THEN
3725 high_value := ''''||REPLACE(high, '''', '''''') ||'''';
3726 ELSE
3727 high_value := REPLACE(high, '''', '''''');
3728 END IF;
3729 ELSIF data_type = 'DATE' THEN
3730 IF operator = 12 THEN
3731 low_value := low;
3732 high_value := high;
3733 ELSE
3734 low_value := 'fnd_date.displaydate_to_date('||''''||low||''''||')';
3735 IF (operator = 9) OR (operator = 10) THEN
3736 high_value := 'fnd_date.displaydate_to_date('||''''||high||''''||')';
3737 ELSE
3738 high_value := high;
3739 END IF;
3740 END IF;
3741 field_name := ' trunc( '||field_name||') ' ;
3742 ELSIF data_type = 'NUMBER' THEN
3743 low_value := NVL(hidden_from, low);
3744 low_value := ''''||low_value||'''';
3745
3746 high_value :=''''||high||'''';
3747 ELSIF data_type IN ('ORG','MULTI') THEN
3748 low_value := ''''||low||'''';
3749 high_value :=''''||high||'''';
3750 IF data_type IN ('MULTI') THEN
3751 IF ( field_name LIKE '%ITEM_SEGMENTS%' ) THEN
3752 field_name := substr(data_set,1,instr(data_set,'.',1)-1)||'.ITEM_NAME';
3753 ELSIF ( field_name LIKE '%CATEGORY%' ) THEN
3754 field_name := data_set;
3755 ELSIF ( INSTR(field_name,'~') > 0 ) THEN
3756 field_name := SUBSTR(field_name, instr(field_name,'~')+1);
3757 END IF;
3758 END IF;
3759 IF ( field_name LIKE '%ORGANIZATION_CODE%' ) THEN
3760 field_name := substr(data_set,1,instr(data_set,'.',1)-1)||'.ORGANIZATION_CODE';
3761 end if;
3762 END IF;
3763 END IF;
3764
3765 IF operator = 1 THEN translated_op := ' = ';
3766 ELSIF operator = 2 and field_name = 'EXCEPTION_TYPE' THEN translated_op := ' = ';
3767 ELSIF operator = 2 THEN translated_op := ' <> ';
3768 ELSIF operator = 3 THEN translated_op := ' >= ';
3769 ELSIF operator = 4 THEN translated_op := ' <= ';
3770 ELSIF operator = 5 THEN translated_op := ' > ';
3771 ELSIF operator = 6 THEN translated_op := ' < ';
3772 ELSIF operator = 7 THEN translated_op := ' IS NOT NULL ';
3773 ELSIF operator = 8 THEN translated_op := ' IS NULL ';
3774 ELSIF operator = 9 THEN translated_op := ' BETWEEN ';
3775 ELSIF operator = 10 THEN translated_op := ' NOT BETWEEN ';
3776 ELSIF operator = 11 THEN translated_op := ' IN ';
3777 ELSIF operator = 12 THEN translated_op := ' BETWEEN ';
3778 ELSIF operator = 13 THEN translated_op := ' LIKE ';
3779 ELSIF operator = 14 THEN translated_op := ' NOT IN ';-- FOR Orders query
3780 END IF;
3781 IF operator IN (12) THEN -- rolling dates
3782 IF (high_value IS NULL) THEN
3783 where_clause_segment := where_clause_segment ||
3784 field_name ||
3785 translated_op ||
3786 ' trunc(sysdate) AND trunc(sysdate) + '||
3787 low_value ;
3788 ELSE
3789 where_clause_segment := where_clause_segment ||
3790 field_name ||
3791 translated_op ||
3792 ' trunc(sysdate) + ' ||
3793 low_value ||
3794 ' AND ' ||
3795 ' trunc(sysdate) + ' ||
3796 high_value ;
3797 END IF;
3798 ELSIF operator IN (9,10) THEN -- operator is BETWEEN or OUTSIDE
3799 where_clause_segment := where_clause_segment ||
3800 field_name ||
3801 translated_op ||
3802 low_value ||
3803 ' AND ' ||
3804 high_value ;
3805 ELSIF operator IN (8,7) THEN -- operator is IS NOT NULL or IS NULL
3806 where_clause_segment := where_clause_segment ||
3807 field_name ||
3808 translated_op ;
3809 ELSIF operator IN (1,2) AND data_type IN ('ORG') THEN
3810 --if field_name = 'ORDER_TYPE_TEXT' then
3811 IF operator = 2 then
3812 where_clause_segment := where_clause_segment ||
3813 field_name ||
3814 translated_op ||
3815 low_value;
3816
3817 ELSE
3818 where_clause_segment := where_clause_segment ||
3819 SUBSTR(data_set,
3820 1, INSTR(data_set,':')-1) ||
3821 translated_op ||
3822 SUBSTR(hidden_from,
3823 1,INSTR(hidden_from,':')-1)||
3824 ' AND ' ||
3825 SUBSTR(data_set, INSTR(data_set,':')+1)
3826 ||translated_op||SUBSTR(hidden_from,INSTR(hidden_from,':')+1);
3827 END IF;
3828 ELSIF operator IN (1,2) AND data_type IN ('MULTI') THEN
3829 --KSA_DEBUG(SYSDATE,'operation...'||operator,'get_where_clause');
3830 where_clause_segment := where_clause_segment||
3831 data_set ||
3832 translated_op ||
3833 hidden_from ;
3834 ELSIF operator IN (11,14) THEN -- operator is AMONG
3835 --debug_plan_event('operator '||operator);
3836 --KSA_DEBUG(SYSDATE,'where_clause_segment...'||where_clause_segment,'MSC_PQ_UTILS.get_where_clause');
3837 where_clause_segment := where_clause_segment ||
3838 get_among_where_clause (sequence, obj_sequence,translated_op, field_name,
3839 operator, low, high, hidden_from, data_set, data_type)||' ';
3840 --debug_plan_event('operator '||operator||where_clause_segment);
3841
3842 ELSIF operator = 13 THEN
3843 IF ( field_name LIKE '%ITEM_SEGMENTS%' ) THEN
3844 field_name := substr(data_set,1,instr(data_set,'.',1)-1)||'.ITEM_NAME';
3845 ELSIF ( field_name LIKE '%ORGANIZATION_CODE%' ) THEN
3846 field_name := substr(data_set,1,instr(data_set,'.',1)-1)||'.ORGANIZATION_CODE';
3847 END IF;
3848 where_clause_segment := where_clause_segment || ' upper('||field_name||') '
3849 || translated_op|| UPPER(low_value);
3850 ELSE
3851 where_clause_segment := where_clause_segment ||
3852 field_name || translated_op||low_value ;
3853 END IF;
3854 --KSA_DEBUG(SYSDATE,'where_clause_segment...'||where_clause_segment,'get_where_clause');
3855 RETURN where_clause_segment;
3856 EXCEPTION
3857 WHEN OTHERS THEN
3858 --KSA_DEBUG(SYSDATE,'Error...'||sqlerrm(sqlcode),'MSC_PQ_UTILS.get_where_clause');
3859 RAISE;
3860 END get_where_clause_wrksh;
3861
3862 FUNCTION build_where_clause_wrksh(p_query_id IN NUMBER DEFAULT NULL,
3863 P_source_type IN NUMBER DEFAULT NULL)
3864 RETURN VARCHAR2 IS
3865
3866 CURSOR c_criteria IS
3867 SELECT field_name,
3868 field_type,
3869 condition,
3870 DECODE(field_name , 'PLANNING_MAKE_BUY_CODE',
3871 hidden_from_field, low_value ) low_value,
3872 high_value,
3873 hidden_from_field,
3874 data_set,
3875 source_type,
3876 object_type,
3877 lov_type,
3878 sequence,
3879 object_sequence_id
3880 FROM msc_selection_criteria_v
3881 WHERE folder_id = p_query_id
3882 AND active_flag = 1
3883 AND condition IS NOT NULL
3884 AND folder_object LIKE 'MSC_WRKSH_FLTR%'
3885 AND data_set is NOT NULL
3886 ORDER BY source_type, object_type, field_name;
3887
3888 CURSOR c_and_or IS
3889 SELECT count(*)
3890 FROM msc_personal_queries
3891 WHERE query_id = p_query_id
3892 AND NVL(and_or_flag,1) = 1;
3893
3894
3895
3896 l_and_or NUMBER;
3897 l_match_str VARCHAR2(10);
3898 l_temp_match_str VARCHAR2(10);
3899 l_row_count NUMBER ;
3900 l_excp_where VARCHAR2(100);
3901
3902 l_where_clause_segment VARCHAR2(2000);
3903 l_where2_clause_segment VARCHAR2(2000);
3904 where_clause_segment VARCHAR2(32000);
3905
3906 l_field_name VARCHAR2(50);
3907 l_data_set VARCHAR2(50);
3908 l_data_type VARCHAR2(50);
3909
3910 begin
3911 l_row_count := 0;
3912 IF p_source_type = 0 THEN
3913 OPEN c_and_or;
3914 FETCH c_and_or INTO l_and_or;
3915 CLOSE c_and_or;
3916 IF l_and_or = 0 THEN
3917 l_match_str := ' OR ';
3918 ELSE
3919 l_match_str := ' AND ';
3920 END if;
3921 --debug_plan_event('before loop...');
3922 FOR c_criteria_row IN c_criteria LOOP
3923 --KSA_DEBUG(SYSDATE,'inside loop...','build_where_clause');
3924 l_row_count := l_row_count + 1;
3925 IF (l_row_count = 1) THEN
3926 l_temp_match_str := '';
3927 ELSE
3928 l_temp_match_str := l_match_str;
3929 END IF;
3930 l_excp_where := '';
3931 l_field_name := c_criteria_row.field_name ;
3932 l_data_set := c_criteria_row.data_set;
3933 l_data_type := c_criteria_row.field_type;
3934 --debug_plan_event(l_field_name||l_data_set||l_data_type);
3935 l_where_clause_segment := get_where_clause_wrksh
3936 (c_criteria_row.sequence,
3937 c_criteria_row.object_sequence_id,
3938 l_field_name,
3939 c_criteria_row.condition,
3940 c_criteria_row.low_value,
3941 c_criteria_row.high_value,
3942 c_criteria_row.hidden_from_field,
3943 l_data_set,
3944 l_data_type,
3945 c_criteria_row.lov_type,
3946 l_temp_match_str,
3947 l_excp_where);
3948 IF where_clause_segment IS NULL THEN
3949 where_clause_segment := ' ( '||l_where_clause_segment||' ) ';
3950 ELSE
3951 where_clause_segment := where_clause_segment||
3952 l_match_str ||
3953 ' ( '||l_where_clause_segment||' ) ';
3954 END IF;
3955 --debug_plan_event(where_clause_segment);
3956 END LOOP;
3957 END IF;
3958 -- debug_plan_event('2'||where_clause_segment);
3959 RETURN where_clause_segment;
3960 END build_where_clause_wrksh;
3961
3962 END MSC_PQ_UTILS;