[Home] [Help]
PACKAGE BODY: APPS.WF_REPLACE_MODPLSQL_UTILITY
Source
1 package body wf_replace_modplsql_utility as
2 /* $Header: WFMPLRMB.pls 120.7 2006/04/30 22:19:33 sramani noship $ */
3
4 p_array_size pls_integer := 2000;
5
6 procedure update_item_attr_vals(p_matches t_matches)
7 is
8 begin
9 forall k in 1..p_matches.id.COUNT
10 update wf_item_attribute_values SAVE_EXCEPTIONS
11 set text_value = p_matches.url(k)
12 where rowid = p_matches.id(k);
13 exception
14 when others then
15 raise;
16 end;
17
18 procedure update_ntf_attrs(p_matches t_matches)
19 is
20 begin
21 --use SAVE_EXCEPTIONS here
22 forall k in 1..p_matches.id.COUNT
23 update wf_notification_attributes SAVE_EXCEPTIONS
24 set text_value = p_matches.url(k)
25 where rowid = p_matches.id(k);
26 exception
27 when others then
28 raise;
29 end;
30
31 function getUpdatedUrl(
32 p_oldUrl in varchar2) return varchar2
33 is
34 l_newUrl varchar2(4000);
35 l_retcode pls_integer := 0;
36 begin
37 if (p_oldUrl like 'HTTP%PLS%WF_MONITOR.BUILDMONITOR%') then
38 wf_monitor.updateToFwkMonitorUrl(
39 oldUrl => p_oldUrl,
40 newUrl => l_newUrl,
41 errorCode => l_retcode);
42 elsif (p_oldUrl like 'HTTP%PLS%WF_EVENT_HTML.EVENTDATA%') then
43 wf_event_html.updateToFwkEvtDataUrl(
44 oldUrl => p_oldUrl,
45 newUrl => l_newUrl,
46 errorCode => l_retcode);
47 elsif (p_oldUrl like 'HTTP%PLS%WF_EVENT_HTML.EVENTSUBS%') then
48 wf_event_html.updateToFwkEvtSubscriptionUrl(
49 oldUrl => p_oldUrl,
50 newUrl => l_newUrl,
51 errorCode => l_retcode);
52 end if;
53
54 if (l_retcode <> 0) then
55 l_newUrl := p_oldUrl;
56 end if;
57
58 return l_newUrl;
59 end;
60
61 procedure update_ntf_attr
62 is
63 --using FOR UPDATE OF, as there is some pl/sql processing before the update
64 cursor url_c is
65 select /*+ PARALLEL(wf_notifications, wf_notification_attributes) */
66 wfna.rowid,
67 wfna.text_value
68 from wf_notifications wfn,
69 wf_notification_attributes wfna
70 where wfn.notification_id = wfna.notification_id
71 and wfn.status = 'OPEN'
72 and (UPPER(wfna.text_value) like 'HTTP%PLS%WF_MONITOR.BUILDMONITOR%'
73 or UPPER(wfna.text_value) like 'HTTP%PLS%WF_EVENT_HTML.EVENTDATA%'
74 or UPPER(wfna.text_value) like 'HTTP%PLS%WF_EVENT_HTML.EVENTSUBS%');
75
76 l_nta_matches t_matches;
77
78 begin
79
80 open url_c;
81 loop
82 fetch url_c bulk collect into l_nta_matches.id, l_nta_matches.url limit p_array_size;
83
84 for i in 1 .. l_nta_matches.id.count
85 loop
86 l_nta_matches.url(i) := getUpdatedUrl(l_nta_matches.url(i));
87 end loop;
88
89 update_ntf_attrs(l_nta_matches);
90
91 commit;
92
93 exit when url_c%notfound;
94 end loop;
95 close url_c;
96 exception
97 when others then
98 if url_c%ISOPEN then
99 close url_c;
100 end if;
101 raise;
102 end;
103
104
105 procedure update_item_attr_val
106 is
107 cursor url_c is
108 select /*+ PARALLEL(wf_items, wf_item_attribute_values) */
109 wfiav.rowid,
110 wfiav.text_value
111 from wf_items wfi,
112 wf_item_attribute_values wfiav
113 where wfi.item_type = wfiav.item_type
114 and wfi.item_key = wfiav.item_key
115 and wfi.end_date is null
116 and (UPPER(wfiav.text_value) like 'HTTP%PLS%WF_MONITOR.BUILDMONITOR%'
117 or UPPER(wfiav.text_value) like 'HTTP%PLS%WF_EVENT_HTML.EVENTDATA%'
118 or UPPER(wfiav.text_value) like 'HTTP%PLS%WF_EVENT_HTML.EVENTSUBS%');
119
120 l_iav_matches t_matches;
121
122 begin
123 open url_c;
124 loop
125 fetch url_c bulk collect into l_iav_matches.id, l_iav_matches.url limit p_array_size;
126
127 for i in 1 .. l_iav_matches.id.count
128 loop
129 l_iav_matches.url(i) := getUpdatedUrl(l_iav_matches.url(i));
130 end loop;
131
132 update_item_attr_vals(l_iav_matches);
133
134 commit;
135
136 exit when url_c%notfound;
137 end loop;
138 close url_c;
139
140 exception
141 when others then
142 if url_c%ISOPEN then
143 close url_c;
144 end if;
145 raise;
146 end;
147
148
149 procedure update_wf_attrs
150 (
151 errbuf out NOCOPY varchar2,
152 retcode out NOCOPY varchar2)
153 is
154 begin
155 update_ntf_attr();
156 update_item_attr_val();
157 errbuf := '';
158 retcode := 0;
159 exception
160 when others then
161 errbuf := to_char(sqlcode) || ':'|| sqlerrm;
162 retcode := '2';
163 end;
164
165 end;