[Home] [Help]
PACKAGE BODY: APPS.WF_SYSTEMS_PKG
Source
1 package body WF_SYSTEMS_PKG as
2 /* $Header: WFEVSYSB.pls 120.3 2005/09/02 16:07:55 vshanmug ship $ */
3
4 m_table_name varchar2(255) := 'WF_SYSTEMS';
5 m_package_version varchar2(30) := '1.0';
6
7 -----------------------------------------------------------------------------
8 procedure INSERT_ROW (
9 X_ROWID in out nocopy varchar2,
10 X_GUID in raw,
11 X_NAME in varchar2,
12 X_MASTER_GUID in raw,
13 X_DISPLAY_NAME in varchar2,
14 X_DESCRIPTION in varchar2
15 ) is
16 cursor C is select rowid
17 from wf_systems
18 where guid = X_GUID;
19 begin
20 insert into wf_systems (
21 guid,
22 name,
23 master_guid,
24 display_name,
25 description
26 ) values (
27 X_GUID,
28 X_NAME,
29 X_MASTER_GUID,
30 X_DISPLAY_NAME,
31 X_DESCRIPTION
32 );
33
34 open c;
35 fetch c into X_ROWID;
36 if (c%notfound) then
37 close c;
38 raise no_data_found;
39 else
40 wf_event.raise('oracle.apps.wf.event.system.create',x_guid);
41 end if;
42 close c;
43 exception
44 when others then
45 wf_core.context('Wf_Systems_Pkg', 'Insert_row', x_guid, x_name);
46 raise;
47
48 end INSERT_ROW;
49 -----------------------------------------------------------------------------
50 procedure UPDATE_ROW (
51 X_GUID in raw,
52 X_NAME in varchar2,
53 X_MASTER_GUID in raw,
54 X_DISPLAY_NAME in varchar2,
55 X_DESCRIPTION in varchar2
56 ) is
57 begin
58 update wf_systems
59 set name = X_NAME,
60 master_guid = X_MASTER_GUID,
61 display_name = X_DISPLAY_NAME,
62 description = X_DESCRIPTION
63 where guid = X_GUID;
64
65 if (sql%notfound) then
66 raise no_data_found;
67 else
68 wf_event.raise('oracle.apps.wf.event.system.update',x_guid);
69 end if;
70 exception
71 when others then
72 wf_core.context('Wf_Systems_Pkg', 'Update_row', x_guid, x_name);
73 raise;
74 end UPDATE_ROW;
75 -----------------------------------------------------------------------------
76 procedure DELETE_ROW (
77 X_GUID in raw
78 ) is
79 begin
80 wf_event.raise('oracle.apps.wf.event.system.delete',x_guid);
81
82 delete from wf_systems
83 where guid = X_GUID;
84
85 if (sql%notfound) then
86 raise no_data_found;
87 end if;
88
89 -- Invalidate cache
90 wf_bes_cache.SetMetaDataUploaded();
91 exception
92 when others then
93 wf_core.context('Wf_Systems_Pkg', 'Delete_row', x_guid);
94 raise;
95 end DELETE_ROW;
96 -----------------------------------------------------------------------------
97 procedure LOAD_ROW (
98 X_GUID in raw,
99 X_NAME in varchar2,
100 X_MASTER_GUID in raw,
101 X_DISPLAY_NAME in varchar2,
102 X_DESCRIPTION in varchar2
103 ) is
104 row_id varchar2(64);
105 begin
106 begin
107 WF_SYSTEMS_PKG.UPDATE_ROW (
108 X_GUID => X_GUID,
109 X_NAME => X_NAME,
110 X_MASTER_GUID => X_MASTER_GUID,
111 X_DISPLAY_NAME => X_DISPLAY_NAME,
112 X_DESCRIPTION => X_DESCRIPTION);
113
114 -- Invalidate cache
115 wf_bes_cache.SetMetaDataUploaded();
116 exception
117 when no_data_found then
118 WF_SYSTEMS_PKG.INSERT_ROW(
119 X_ROWID => row_id,
120 X_GUID => X_GUID,
121 X_NAME => X_NAME,
122 X_MASTER_GUID => X_MASTER_GUID,
123 X_DISPLAY_NAME => X_DISPLAY_NAME,
124 X_DESCRIPTION => X_DESCRIPTION);
125 end;
126
127 exception
128 when others then
129 wf_core.context('Wf_Systems_Pkg', 'Load_row', x_guid, x_name);
130 raise;
131 end LOAD_ROW;
132 -----------------------------------------------------------------------------
133 function GENERATE (
134 X_GUID in raw
135 ) return varchar2 is
136 buf varchar2(32000);
137 l_doc xmldom.DOMDocument;
138 l_element xmldom.DOMElement;
139 l_root xmldom.DOMNode;
140 l_node xmldom.DOMNode;
141 l_header xmldom.DOMNode;
142 l_name varchar2(80);
143 l_master_guid raw(16);
144 l_display_name varchar2(80);
145 l_description varchar2(240);
146
147 begin
148 select name, master_guid, display_name, description
149 into l_name, l_master_guid, l_display_name, l_description
150 from wf_systems
151 where guid = x_guid;
152
153 l_doc := xmldom.newDOMDocument;
154 l_root := xmldom.makeNode(l_doc);
155 l_root := wf_event_xml.newtag (l_doc, l_root, wf_event_xml.masterTagName);
156 l_header := wf_event_xml.newtag(l_doc, l_root, m_table_name);
157 l_node := wf_event_xml.newtag(l_doc, l_header, wf_event_xml.versionTagName,
158 m_package_version);
159 l_node := wf_event_xml.newtag(l_doc, l_header, 'GUID',
160 rawtohex(x_guid));
161 l_node := wf_event_xml.newtag(l_doc, l_header, 'NAME', l_name);
162 l_node := wf_event_xml.newtag(l_doc, l_header, 'MASTER_GUID',
163 rawtohex(l_master_guid));
164 l_node := wf_event_xml.newtag(l_doc, l_header, 'DISPLAY_NAME',
165 l_display_name);
166 l_node := wf_event_xml.newtag(l_doc, l_header, 'DESCRIPTION', l_description);
167
168 xmldom.writeToBuffer(l_root, buf);
169
170 return buf;
171 exception
172 when others then
173 wf_core.context('Wf_Systems_Pkg', 'Generate', x_guid);
174 raise;
175 end GENERATE;
176 -----------------------------------------------------------------------------
177 procedure RECEIVE (
178 X_MESSAGE in varchar2
179 ) is
180 l_guid varchar2(32);
181 l_name varchar2(80);
182 l_master_guid varchar2(32);
183 l_display_name varchar2(80);
184 l_description varchar2(240);
185 l_version varchar2(80);
186 l_message varchar2(32000);
187
188 l_node_name varchar2(255);
189 l_node xmldom.DOMNode;
190 l_child xmldom.DOMNode;
191 l_value varchar2(32000);
192 l_length integer;
193 l_node_list xmldom.DOMNodeList;
194 begin
195
196 l_message := x_message;
197 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetGUID(l_message); -- update #NEW
198 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetSYSTEMGUID(l_message); -- update #LOCAL
199 l_message := WF_EVENT_SYNCHRONIZE_PKG.SetSID(l_message); -- update #SID
200
201 l_node_list := wf_event_xml.findTable(l_message, m_table_name);
202 l_length := xmldom.getLength(l_node_list);
203
204 -- loop through elements that we received.
205 for i in 0..l_length-1 loop
206 l_node := xmldom.item(l_node_list, i);
207 l_node_name := xmldom.getNodeName(l_node);
208 if xmldom.hasChildNodes(l_node) then
209 l_child := xmldom.GetFirstChild(l_node);
210 l_value := xmldom.getNodevalue(l_child);
211 else
212 l_value := NULL;
213 end if;
214
215 if(l_node_name = 'GUID') then
216 l_guid := l_value;
217 elsif(l_node_name = 'NAME') then
218 l_name := l_value;
219 elsif(l_node_name = 'MASTER_GUID') then
220 l_master_guid := l_value;
221 elsif(l_node_name = 'DISPLAY_NAME') then
222 l_display_name := l_value;
223 elsif(l_node_name = 'DESCRIPTION') then
224 l_description := l_value;
225 elsif(l_node_name = wf_event_xml.versionTagName) then
226 l_version := l_value;
227 else
228 Wf_Core.Token('REASON', 'Invalid column name found:' ||
229 l_node_name || ' with value:'||l_value);
230 Wf_Core.Raise('WFSQL_INTERNAL');
231 end if;
232 end loop;
233
234 load_row(l_guid, l_name, l_master_guid, l_display_name, l_description);
235 exception
236 when others then
237 wf_core.context('Wf_Systems_Pkg', 'Receive', x_message);
238 raise;
239 end RECEIVE;
240 -----------------------------------------------------------------------------
241 end WF_SYSTEMS_PKG;