[Home] [Help]
365: SELECT wpa_to.process_name || ':' || wpa_to.activity_name full_activity_name,
366: wias_to.item_type,
367: wias_to.item_key
368: FROM wf_item_activity_statuses wias_to,
369: wf_process_activities wpa_to,
370: wf_activities wa,
371: wf_item_activity_statuses wias_from,
372: wf_activity_transitions wat,
373: wf_items wi
477: SELECT wpa_to.process_name || ':' || wpa_to.activity_name,
478: wias_to.item_type
479: INTO l_activity, l_item_type
480: FROM wf_item_activity_statuses wias_to,
481: wf_process_activities wpa_to,
482: wf_activities wa,
483: wf_item_activity_statuses wias_from,
484: wf_activity_transitions wat
485: WHERE wpa_to.instance_id= wias_to.process_activity
617: SELECT wpa_to.process_name || ':' || wpa_to.activity_name,
618: wias_to.item_type
619: INTO l_activity, l_item_type
620: FROM wf_item_activity_statuses wias_to,
621: wf_process_activities wpa_to,
622: wf_activities wa,
623: wf_item_activity_statuses wias_from,
624: wf_activity_transitions wat
625: WHERE wpa_to.instance_id= wias_to.process_activity
820: and oh.line_id IS NULL
821: and oh.hold_source_id =:hold_source_id )';
822: IF p_item_type is not null and p_activity_name is not null then
823: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
824: , wf_process_activities wpa
825: where was.process_activity = wpa.instance_id
826: and item_type = :p_item_type
827: and item_key = to_char(h.header_id)
828: and activity_name = :l_activity_name
906: and oh.line_id IS NULL
907: and oh.hold_source_id =:hold_source_id )';
908: IF p_item_type is not null and p_activity_name is not null then
909: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
910: , wf_process_activities wpa
911: where was.process_activity = wpa.instance_id
912: and item_type = :p_item_type
913: and item_key = to_char(h.header_id)
914: and activity_name = :l_activity_name
1087: and oh.line_id = ol.line_id
1088: and oh.hold_source_id = :hold_source_id )';
1089: IF p_item_type is not null and p_activity_name is not null then
1090: l_sqlmt :=l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
1091: , wf_process_activities wpa
1092: where was.process_activity = wpa.instance_id
1093: and item_type = :p_item_type
1094: and item_key = to_char(ol.line_id)
1095: and activity_name = :l_activity_name
1190: and oh.hold_source_id =:hold_source_id )';
1191:
1192: IF p_item_type is not null and p_activity_name is not null then
1193: l_sqlmt :=l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
1194: , wf_process_activities wpa
1195: where was.process_activity = wpa.instance_id
1196: and item_type = :p_item_type
1197: and item_key = to_char(ol.line_id)
1198: and activity_name = :l_activity_name
8265: and oh.line_id = ol.line_id
8266: and oh.hold_source_id = :hold_source_id )';
8267: IF p_item_type is not null and p_activity_name is not null then
8268: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
8269: , wf_process_activities wpa
8270: where was.process_activity = wpa.instance_id
8271: and item_type = :p_item_type
8272: and item_key = to_char(ol.line_id)
8273: and activity_name = :l_activity_name
8342: and oh.hold_source_id =:hold_source_id )';
8343:
8344: IF p_item_type is not null and p_activity_name is not null then
8345: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
8346: , wf_process_activities wpa
8347: where was.process_activity = wpa.instance_id
8348: and item_type = :p_item_type
8349: and item_key = to_char(ol.line_id)
8350: and activity_name = :l_activity_name
8592: and oh.line_id = ol.line_id
8593: and oh.hold_source_id =:hold_source_id )';
8594: IF p_item_type is not null and p_activity_name is not null then
8595: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
8596: , wf_process_activities wpa
8597: where was.process_activity = wpa.instance_id
8598: and item_type = :p_item_type
8599: and item_key = to_char(ol.line_id)
8600: and activity_name = :l_activity_name
8663: and oh.line_id = ol.line_id
8664: and oh.hold_source_id =:hold_source_id )';
8665: IF p_item_type is not null and p_activity_name is not null then
8666: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
8667: , wf_process_activities wpa
8668: where was.process_activity = wpa.instance_id
8669: and item_type = :p_item_type
8670: and item_key = to_char(ol.line_id)
8671: and activity_name = :l_activity_name
8807: and oh.line_id = ol.line_id
8808: and oh.hold_source_id = :hold_source_id )';
8809: IF p_item_type is not null and p_activity_name is not null then
8810: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
8811: , wf_process_activities wpa
8812: where was.process_activity = wpa.instance_id
8813: and item_type = :p_item_type
8814: and item_key = to_char(ol.line_id)
8815: and activity_name = :l_activity_name
8880: and oh.line_id = ol.line_id
8881: and oh.hold_source_id = :hold_source_id )';
8882: IF p_item_type is not null and p_activity_name is not null then
8883: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
8884: , wf_process_activities wpa
8885: where was.process_activity = wpa.instance_id
8886: and item_type = :p_item_type
8887: and item_key = to_char(ol.line_id)
8888: and activity_name = :l_activity_name
9022: and oh.line_id = ol.line_id
9023: and oh.hold_source_id =:hold_source_id )';
9024: IF p_item_type is not null and p_activity_name is not null then
9025: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
9026: , wf_process_activities wpa
9027: where was.process_activity = wpa.instance_id
9028: and item_type = :p_item_type
9029: and item_key = to_char(ol.line_id)
9030: and activity_name = :l_activity_name
9094: and oh.line_id = ol.line_id
9095: and oh.hold_source_id =:hold_source_id )';
9096: IF p_item_type is not null and p_activity_name is not null then
9097: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
9098: , wf_process_activities wpa
9099: where was.process_activity = wpa.instance_id
9100: and item_type = :p_item_type
9101: and item_key = to_char(ol.line_id)
9102: and activity_name = :l_activity_name
9235: and oh.line_id = ol.line_id
9236: and oh.hold_source_id =:hold_source_id )';
9237: IF p_item_type is not null and p_activity_name is not null then
9238: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
9239: , wf_process_activities wpa
9240: where was.process_activity = wpa.instance_id
9241: and item_type = :p_item_type
9242: and item_key = to_char(ol.line_id)
9243: and activity_name = :l_activity_name
9308: and oh.line_id = ol.line_id
9309: and oh.hold_source_id =:hold_source_id )';
9310: IF p_item_type is not null and p_activity_name is not null then
9311: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
9312: , wf_process_activities wpa
9313: where was.process_activity = wpa.instance_id
9314: and item_type = :p_item_type
9315: and item_key = to_char(ol.line_id)
9316: and activity_name = :l_activity_name
9450: and oh.line_id = ol.line_id
9451: and oh.hold_source_id =:hold_source_id )';
9452: IF p_item_type is not null and p_activity_name is not null then
9453: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
9454: , wf_process_activities wpa
9455: where was.process_activity = wpa.instance_id
9456: and item_type = :p_item_type
9457: and item_key = to_char(ol.line_id)
9458: and activity_name = :l_activity_name
9522: and oh.line_id = ol.line_id
9523: and oh.hold_source_id =:hold_source_id )';
9524: IF p_item_type is not null and p_activity_name is not null then
9525: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
9526: , wf_process_activities wpa
9527: where was.process_activity = wpa.instance_id
9528: and item_type = :p_item_type
9529: and item_key = to_char(ol.line_id)
9530: and activity_name = :l_activity_name
9667: and oh.line_id = ol.line_id
9668: and oh.hold_source_id =:hold_source_id )';
9669: IF p_item_type is not null and p_activity_name is not null then
9670: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
9671: , wf_process_activities wpa
9672: where was.process_activity = wpa.instance_id
9673: and item_type = :p_item_type
9674: and item_key = to_char(ol.line_id)
9675: and activity_name = :l_activity_name
9740: and oh.line_id = ol.line_id
9741: and oh.hold_source_id =:hold_source_id )';
9742: IF p_item_type is not null and p_activity_name is not null then
9743: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
9744: , wf_process_activities wpa
9745: where was.process_activity = wpa.instance_id
9746: and item_type = :p_item_type
9747: and item_key = to_char(ol.line_id)
9748: and activity_name = :l_activity_name
9972: AND oh.line_id = ol.line_id
9973: AND oh.hold_source_id =:hold_source_id )';
9974: IF p_item_type is not null and p_activity_name is not null then
9975: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
9976: , wf_process_activities wpa
9977: where was.process_activity = wpa.instance_id
9978: and item_type = :p_item_type
9979: and item_key = to_char(ol.line_id)
9980: and activity_name = :l_activity_name
10045: AND oh.line_id = ol.line_id
10046: AND oh.hold_source_id =:hold_source_id )';
10047: IF p_item_type is not null and p_activity_name is not null then
10048: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
10049: , wf_process_activities wpa
10050: where was.process_activity = wpa.instance_id
10051: and item_type = :p_item_type
10052: and item_key = to_char(ol.line_id)
10053: and activity_name = :l_activity_name
10187: AND oh.line_id = ol.line_id
10188: AND oh.hold_source_id =:hold_source_id )';
10189: IF p_item_type is not null and p_activity_name is not null then
10190: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
10191: , wf_process_activities wpa
10192: where was.process_activity = wpa.instance_id
10193: and item_type = :p_item_type
10194: and item_key = to_char(ol.line_id)
10195: and activity_name = :l_activity_name
10259: AND oh.line_id = ol.line_id
10260: AND oh.hold_source_id =:hold_source_id )';
10261: IF p_item_type is not null and p_activity_name is not null then
10262: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
10263: , wf_process_activities wpa
10264: where was.process_activity = wpa.instance_id
10265: and item_type = :p_item_type
10266: and item_key = to_char(ol.line_id)
10267: and activity_name = :l_activity_name
10404: and oh.line_id = ol.line_id
10405: and oh.hold_source_id =:hold_source_id )';
10406: IF p_item_type is not null and p_activity_name is not null then
10407: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
10408: , wf_process_activities wpa
10409: where was.process_activity = wpa.instance_id
10410: and item_type = :p_item_type
10411: and item_key = to_char(ol.line_id)
10412: and activity_name = :l_activity_name
10476: and oh.line_id = ol.line_id
10477: and oh.hold_source_id =:hold_source_id )';
10478: IF p_item_type is not null and p_activity_name is not null then
10479: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
10480: , wf_process_activities wpa
10481: where was.process_activity = wpa.instance_id
10482: and item_type = :p_item_type
10483: and item_key = to_char(ol.line_id)
10484: and activity_name = :l_activity_name
10728: and oh.line_id = ol.line_id
10729: and oh.hold_source_id =:hold_source_id )';
10730: IF p_item_type is not null and p_activity_name is not null then
10731: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
10732: , wf_process_activities wpa
10733: where was.process_activity = wpa.instance_id
10734: and item_type = :p_item_type
10735: and item_key = to_char(ol.line_id)
10736: and activity_name = :l_activity_name
10800: and oh.line_id = ol.line_id
10801: and oh.hold_source_id =:hold_source_id )';
10802: IF p_item_type is not null and p_activity_name is not null then
10803: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
10804: , wf_process_activities wpa
10805: where was.process_activity = wpa.instance_id
10806: and item_type = :p_item_type
10807: and item_key = to_char(ol.line_id)
10808: and activity_name = :l_activity_name
10943: and oh.line_id = ol.line_id
10944: and oh.hold_source_id =:hold_source_id )';
10945: IF p_item_type is not null and p_activity_name is not null then
10946: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
10947: , wf_process_activities wpa
10948: where was.process_activity = wpa.instance_id
10949: and item_type = :p_item_type
10950: and item_key = to_char(ol.line_id)
10951: and activity_name = :l_activity_name
11015: and oh.line_id = ol.line_id
11016: and oh.hold_source_id =:hold_source_id )';
11017: IF p_item_type is not null and p_activity_name is not null then
11018: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
11019: , wf_process_activities wpa
11020: where was.process_activity = wpa.instance_id
11021: and item_type = :p_item_type
11022: and item_key = to_char(ol.line_id)
11023: and activity_name = :l_activity_name
11157: and oh.line_id = ol.line_id
11158: and oh.hold_source_id =:hold_source_id )';
11159: IF p_item_type is not null and p_activity_name is not null then
11160: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
11161: , wf_process_activities wpa
11162: where was.process_activity = wpa.instance_id
11163: and item_type = :p_item_type
11164: and item_key = to_char(ol.line_id)
11165: and activity_name = :l_activity_name
11229: and oh.line_id = ol.line_id
11230: and oh.hold_source_id =:hold_source_id )';
11231: IF p_item_type is not null and p_activity_name is not null then
11232: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
11233: , wf_process_activities wpa
11234: where was.process_activity = wpa.instance_id
11235: and item_type = :p_item_type
11236: and item_key = to_char(ol.line_id)
11237: and activity_name = :l_activity_name
11369: and oh.line_id = ol.line_id
11370: and oh.hold_source_id =:hold_source_id )';
11371: IF p_item_type is not null and p_activity_name is not null then
11372: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
11373: , wf_process_activities wpa
11374: where was.process_activity = wpa.instance_id
11375: and item_type = :p_item_type
11376: and item_key = to_char(ol.line_id)
11377: and activity_name = :l_activity_name
11440: and oh.line_id = ol.line_id
11441: and oh.hold_source_id =:hold_source_id )';
11442: IF p_item_type is not null and p_activity_name is not null then
11443: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
11444: , wf_process_activities wpa
11445: where was.process_activity = wpa.instance_id
11446: and item_type = :p_item_type
11447: and item_key = to_char(ol.line_id)
11448: and activity_name = :l_activity_name
11558: where oh.header_id = h.header_id
11559: and oh.hold_source_id =:hold_source_id2 )';
11560: IF p_item_type is not null and p_activity_name is not null then
11561: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
11562: , wf_process_activities wpa
11563: where was.process_activity = wpa.instance_id
11564: and item_type = :p_item_type
11565: and item_key = to_char(h.header_id)
11566: and activity_name = :l_activity_name
11618: and oh.line_id = ol.line_id
11619: and oh.hold_source_id =:hold_source_id )';
11620: IF p_item_type is not null and p_activity_name is not null then
11621: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
11622: , wf_process_activities wpa
11623: where was.process_activity = wpa.instance_id
11624: and item_type = :p_item_type
11625: and item_key = to_char(ol.line_id)
11626: and activity_name = :l_activity_name
11764: where oh.header_id = h.header_id
11765: and oh.hold_source_id =:hold_source_id )';
11766: IF p_item_type is not null and p_activity_name is not null then
11767: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
11768: , wf_process_activities wpa
11769: where was.process_activity = wpa.instance_id
11770: and item_type = :p_item_type
11771: and item_key = to_char(h.header_id)
11772: and activity_name = :l_activity_name
11822: where oh.header_id = h.header_id
11823: and oh.hold_source_id =:hold_source_id )';
11824: IF p_item_type is not null and p_activity_name is not null then
11825: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
11826: , wf_process_activities wpa
11827: where was.process_activity = wpa.instance_id
11828: and item_type = :p_item_type
11829: and item_key = to_char(h.header_id)
11830: and activity_name = :l_activity_name
12205: and oh.line_id = ol.line_id
12206: and oh.hold_source_id =:hold_source_id )';
12207: IF p_item_type is not null and p_activity_name is not null then
12208: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
12209: , wf_process_activities wpa
12210: where was.process_activity = wpa.instance_id
12211: and item_type = :p_item_type
12212: and item_key = to_char(ol.line_id)
12213: and activity_name = :l_activity_name
12276: and oh.line_id = ol.line_id
12277: and oh.hold_source_id =:hold_source_id )';
12278: IF p_item_type is not null and p_activity_name is not null then
12279: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
12280: , wf_process_activities wpa
12281: where was.process_activity = wpa.instance_id
12282: and item_type = :p_item_type
12283: and item_key = to_char(ol.line_id)
12284: and activity_name = :l_activity_name
12418: and oh.line_id = ol.line_id
12419: and oh.hold_source_id =:hold_source_id )';
12420: IF p_item_type is not null and p_activity_name is not null then
12421: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
12422: , wf_process_activities wpa
12423: where was.process_activity = wpa.instance_id
12424: and item_type = :p_item_type
12425: and item_key = to_char(ol.line_id)
12426: and activity_name = :l_activity_name
12488: and oh.line_id = ol.line_id
12489: and oh.hold_source_id =:hold_source_id )';
12490: IF p_item_type is not null and p_activity_name is not null then
12491: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
12492: , wf_process_activities wpa
12493: where was.process_activity = wpa.instance_id
12494: and item_type = :p_item_type
12495: and item_key = to_char(ol.line_id)
12496: and activity_name = :l_activity_name
12629: and oh.line_id = ol.line_id
12630: and oh.hold_source_id =:hold_source_id )';
12631: IF p_item_type is not null and p_activity_name is not null then
12632: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
12633: , wf_process_activities wpa
12634: where was.process_activity = wpa.instance_id
12635: and item_type = :p_item_type
12636: and item_key = to_char(ol.line_id)
12637: and activity_name = :l_activity_name
12699: and oh.line_id = ol.line_id
12700: and oh.hold_source_id =:hold_source_id )';
12701: IF p_item_type is not null and p_activity_name is not null then
12702: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
12703: , wf_process_activities wpa
12704: where was.process_activity = wpa.instance_id
12705: and item_type = :p_item_type
12706: and item_key = to_char(ol.line_id)
12707: and activity_name = :l_activity_name
12840: and oh.line_id = ol.line_id
12841: and oh.hold_source_id =:hold_source_id )';
12842: IF p_item_type is not null and p_activity_name is not null then
12843: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
12844: , wf_process_activities wpa
12845: where was.process_activity = wpa.instance_id
12846: and item_type = :p_item_type
12847: and item_key = to_char(ol.line_id)
12848: and activity_name = :l_activity_name
12911: and oh.line_id = ol.line_id
12912: and oh.hold_source_id =:hold_source_id )';
12913: IF p_item_type is not null and p_activity_name is not null then
12914: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
12915: , wf_process_activities wpa
12916: where was.process_activity = wpa.instance_id
12917: and item_type = :p_item_type
12918: and item_key = to_char(ol.line_id)
12919: and activity_name = :l_activity_name