[Home] [Help]
PACKAGE BODY: APPS.IEX_XML_PKG
Source
1 PACKAGE BODY IEX_XML_pkg AS
2 /* $Header: iextxmlb.pls 120.0 2005/05/10 19:54:12 acaraujo noship $ */
3
4 PG_DEBUG NUMBER(2) ;
5
6 PROCEDURE insert_row(
7 px_rowid IN OUT NOCOPY VARCHAR2
8 , px_xml_request_id IN OUT NOCOPY NUMBER
9 , p_query_temp_id NUMBER
10 , p_status VARCHAR2
11 , p_document BLOB
12 , p_xmldata CLOB
13 , p_method VARCHAR2
14 , p_destination VARCHAR2
15 , p_object_type VARCHAR2
16 , p_object_id NUMBER
17 , p_resource_id NUMBER
18 , p_view_by VARCHAR2
19 , p_party_id NUMBER
20 , p_cust_account_id NUMBER
21 , p_cust_site_use_id NUMBER
22 , p_delinquency_id NUMBER
23 , p_last_update_date DATE
24 , p_last_updated_by NUMBER
25 , p_creation_date DATE
26 , p_created_by NUMBER
27 , p_last_update_login NUMBER
28 , p_object_version_number NUMBER
29 ) IS
30
31 CURSOR get_rowid IS
32 SELECT ROWID
33 FROM iex_xml_request_histories
34 WHERE xml_request_id = px_xml_request_id;
35 --
36 CURSOR get_seq_csr is
37 SELECT IEX_XML_REQUEST_HISTORIES_s.nextval
38 FROM sys.dual;
39
40 BEGIN
41 --
42 If (px_xml_request_id IS NULL) OR (px_xml_request_id = FND_API.G_MISS_NUM) then
43 OPEN get_seq_csr;
44 FETCH get_seq_csr INTO px_xml_request_id;
45 CLOSE get_seq_csr;
46 End If;
47 --dbms_output.put_line('id=' || px_xml_request_id);
48 --dbms_output.put_line('insert');
49 --
50 INSERT INTO IEX_XML_REQUEST_HISTORIES (
51 XML_REQUEST_ID
52 , QUERY_TEMP_ID
53 , STATUS
54 , DOCUMENT
55 , XMLDATA
56 , METHOD
57 , DESTINATION
58 , OBJECT_TYPE
59 , OBJECT_ID
60 , RESOURCE_ID
61 , VIEW_BY
62 , PARTY_ID
63 , CUST_ACCOUNT_ID
64 , CUST_SITE_USE_ID
65 , DELINQUENCY_ID
66 , last_update_date
67 , last_updated_by
68 , creation_date
69 , created_by
70 , last_update_login
71 , object_version_number
72 ) VALUES (
73 px_xml_request_id
74 , p_query_temp_id
75 , p_status
76 , p_document
77 , p_xmldata
78 , p_method
79 , p_destination
80 , p_object_type
81 , p_object_id
82 , p_resource_id
83 , p_view_by
84 , p_party_id
85 , p_cust_account_id
86 , p_cust_site_use_id
87 , p_delinquency_id
88 , p_last_update_date
89 , p_last_updated_by
90 , p_creation_date
91 , p_created_by
92 , p_last_update_login
93 , p_object_version_number
94 );
95
96 OPEN get_rowid;
97 FETCH get_rowid INTO px_rowid;
98 IF (get_rowid%NOTFOUND) THEN
99 CLOSE get_rowid;
100 RAISE NO_DATA_FOUND;
101 END IF;
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 --dbms_output.put_line('error' || SQLERRM);
106 WriteLog('iextxmlb.pls:insert_row:Exception errmsg='||SQLERRM);
107 END insert_row;
108
109
110
111 PROCEDURE delete_row(
112 p_xml_request_id NUMBER
113 ) IS
114 BEGIN
115 DELETE FROM iex_xml_request_histories
116 WHERE xml_request_id = p_xml_request_id;
117 IF (SQL%NOTFOUND) THEN
118 RAISE NO_DATA_FOUND;
119 END IF;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 --dbms_output.put_line('error' || SQLERRM);
124 WriteLog('iextxmlb.pls:delete_row:Exception errmsg='||SQLERRM);
125 END delete_row;
126
127
128 PROCEDURE UPDATE_ROW (
129 p_xml_request_id NUMBER
130 , p_query_temp_id NUMBER
131 , p_status VARCHAR2
132 , p_document BLOB
133 , p_xmldata CLOB
134 , p_method VARCHAR2
135 , p_destination VARCHAR2
136 , p_object_type VARCHAR2
137 , p_object_id NUMBER
138 , p_resource_id NUMBER
139 , p_view_by VARCHAR2
140 , p_party_id NUMBER
141 , p_cust_account_id NUMBER
142 , p_cust_site_use_id NUMBER
143 , p_delinquency_id NUMBER
144 , p_last_update_date DATE
145 , p_last_updated_by NUMBER
146 , p_creation_date DATE
147 , p_created_by NUMBER
148 , p_last_update_login NUMBER
149 , p_object_version_number NUMBER
150 )
151 IS
152 cursor c_get_rec (in_request_id number) is
153 select rowid,
154 xml_request_id,
155 query_temp_id,
156 status,
157 document,
158 xmldata,
159 object_type,
160 object_id,
161 resource_id,
162 method,
163 destination,
164 view_by,
165 party_id,
166 cust_account_id,
167 cust_site_use_id,
168 delinquency_id,
169 creation_date,
170 created_by,
171 last_update_date,
172 last_updated_by,
173 last_update_login,
174 object_version_number
175 from iex_xml_request_histories
176 where xml_request_id = in_request_id
177 for update nowait;
178 --
179 l_xml_request_id NUMBER;
180 l_query_temp_id NUMBER;
181 l_status VARCHAR2(40);
182 l_document BLOB;
183 l_xmldata CLOB;
184 l_method VARCHAR2(10);
185 l_destination VARCHAR2(2000);
186 l_object_type VARCHAR2(100);
187 l_object_id NUMBER;
188 l_resource_id NUMBER;
189 l_view_by VARCHAR2(10);
190 l_party_id NUMBER;
191 l_cust_account_id NUMBER;
192 l_cust_site_use_id NUMBER;
193 l_delinquency_id NUMBER;
194 l_last_update_date DATE;
195 l_last_updated_by NUMBER;
196 l_creation_date DATE;
197 l_created_by NUMBER;
198 l_last_update_login NUMBER;
199 l_object_version_number NUMBER;
200 l_rowid varchar2(2000);
201
202 BEGIN
203
204 open c_get_rec (p_xml_request_id);
205 fetch c_get_rec into
206 l_rowid,
207 l_xml_request_id,
208 l_query_temp_id,
209 l_status,
210 l_document,
211 l_xmldata,
212 l_object_type,
213 l_object_id,
214 l_resource_id,
215 l_method,
216 l_destination,
217 l_view_by,
218 l_party_id,
219 l_cust_account_id,
220 l_cust_site_use_id,
221 l_delinquency_id,
222 l_creation_date,
223 l_created_by,
224 l_last_update_date,
225 l_last_updated_by,
226 l_last_update_login,
227 l_object_version_number;
228 CLOSE c_get_rec;
229
230 if (p_status is not null ) then
231 l_status := p_status;
232 end if;
233 if (p_document is not null ) then
234 l_document := p_document;
235 end if;
236 if (p_xmldata is not null ) then
237 l_xmldata := p_xmldata;
238 end if;
239 if (p_method is not null ) then
240 l_method := p_method;
241 end if;
242 if (p_destination is not null ) then
243 l_destination := p_destination;
244 end if;
245 if (p_resource_id is not null ) then
246 l_resource_id := p_resource_id;
247 end if;
248 if (p_last_update_date is not null ) then
249 l_last_update_date := p_last_update_date;
250 end if;
251 if (p_last_updated_by is not null ) then
252 l_last_updated_by := p_last_updated_by;
253 end if;
254
255 UPDATE iex_xml_request_histories
256 SET
257 query_temp_id = l_query_temp_id
258 , status = l_status
259 , document = l_document
260 , xmldata = l_xmldata
261 , method = l_method
262 , destination = l_destination
263 , object_type = l_object_type
264 , object_id = l_object_id
265 , resource_id = l_resource_id
266 , view_by = l_view_by
267 , party_id = l_party_id
268 , cust_account_id = l_cust_account_id
269 , cust_site_use_id = l_cust_site_use_id
270 , delinquency_id = l_delinquency_id
271 , last_update_date = l_last_update_date
272 , last_updated_by = l_last_updated_by
273 , last_update_login = l_last_update_login
274 , creation_date = l_creation_date
275 , created_by = l_created_by
276 , object_version_number = l_object_version_number
277 WHERE xml_request_id = p_xml_request_id;
278
279 IF (SQL%NOTFOUND) THEN
280 RAISE NO_DATA_FOUND;
281 END IF;
282
283 EXCEPTION
284 WHEN OTHERS THEN
285 --dbms_output.put_line('error' || SQLERRM);
286 WriteLog('iextxmlb.pls:update_row:Exception errmsg='||SQLERRM);
287
288 END update_row;
289
290
291
292 Procedure WriteLog ( p_msg IN VARCHAR2)
293 IS
294 BEGIN
295 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
296 iex_debug_pub.LogMessage (p_msg);
297 END IF;
298
299 --dbms_output.put_line(p_msg);
300
301 END WriteLog;
302
303
304 BEGIN
305 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
306
307
308 END iex_xml_pkg;