1 package body ar_transfer_document as
2 /*$Header: ARTRSDCB.pls 115.7 2002/12/23 22:45:11 tkoshio noship $ */
3
4 function validateSource(p_doc_rec in out nocopy ar_document_transfers%rowtype) return boolean is
5 cursor trx is
6 select 'x' from ra_customer_trx where customer_trx_id = p_doc_rec.source_id;
7 l_exists varchar2(1);
8 l_retcode boolean := false;
9 begin
10 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validateSource(+)'); end if;
11 if p_doc_rec.source_table = 'RA_CUSTOMER_TRX' then
12 open trx; fetch trx into l_exists; close trx;
13 if l_exists is null then l_retcode := false;
14 else l_retcode := true; end if;
15 else
16 l_retcode := false;
17 end if;
18
19 if not l_retcode then
20 fnd_message.set_name('AR', 'AR_DOC_TRS_INV_SRC_TBL_ID');
21 fnd_message.set_token('DOCUMENT_TRANSFER_ID', p_doc_rec.document_transfer_id);
22 p_doc_rec.status := 'FAILED';
23 p_doc_rec.exception_type := 'SYSTEM';
24 p_doc_rec.exception_message := fnd_message.get;
25 end if;
26 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('STATUS:'||p_doc_rec.status); end if;
27 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validateSource(-)'); end if;
28 return l_retcode;
29 end;
30
31 function validateTpSource(p_doc_rec in out nocopy ar_document_transfers%rowtype) return boolean is
32 cursor tp is
33 select 'x' from hz_party_sites where party_site_id = p_doc_rec.tp_source_id;
34 l_exists varchar2(1);
35 l_retcode boolean := false;
36 begin
37 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validateTpSource(+)'); end if;
38 if p_doc_rec.tp_source_table = 'HZ_PARTY_SITES' then
39 open tp; fetch tp into l_exists; close tp;
40 if l_exists is null then l_retcode := false;
41 else l_retcode := true; end if;
42 else
43 l_retcode := false;
44 end if;
45
46 if not l_retcode then
47 fnd_message.set_name('AR', 'AR_DOC_TRS_INV_TP_SRC_TBL_ID');
48 fnd_message.set_token('DOCUMENT_TRANSFER_ID', p_doc_rec.document_transfer_id);
49 p_doc_rec.status := 'FAILED';
50 p_doc_rec.exception_type := 'SYSTEM';
51 p_doc_rec.exception_message := fnd_message.get;
52 end if;
53 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('STATUS:'||p_doc_rec.status); end if;
54 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validateTpSource(-)'); end if;
55 return l_retcode;
56 end;
57
58 function validateEventName(p_doc_rec in out nocopy ar_document_transfers%rowtype) return boolean is
59 cursor event is
60 select 'x' from wf_events where name = p_doc_rec.event_name and status = 'ENABLED' and type = 'EVENT';
61 l_exists varchar2(1);
62 l_retcode boolean := false;
63 begin
64 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validateEventName(+)'); end if;
65 open event; fetch event into l_exists; close event;
66 if l_exists is null then l_retcode := false;
67 else l_retcode := true; end if;
68 if not l_retcode then
69 fnd_message.set_name('AR', 'AR_DOC_TRS_INV_BUS_EVENT');
70 fnd_message.set_token('EVENT_NAME', p_doc_rec.event_name);
71 fnd_message.set_token('DOCUMENT_TRANSFER_ID', p_doc_rec.document_transfer_id);
72 p_doc_rec.status := 'FAILED';
73 p_doc_rec.exception_type := 'AR';
74 p_doc_rec.exception_message := fnd_message.get;
75 end if;
76 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('STATUS:'||p_doc_rec.status); end if;
77 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validateEventName(-)'); end if;
78 return l_retcode;
79 end;
80
81 function validateTrx(p_doc_rec in out nocopy ar_document_transfers%rowtype) return boolean is
82
83 l_exists varchar2(1);
84 l_retcode boolean := false;
85
86 cursor ecxTrx is select 'x'
87 from ecx_transactions trx,
88 ecx_ext_processes proc,
89 ecx_tp_headers tph,
90 ecx_tp_details tpd
91 where trx.transaction_type = p_doc_rec.ecx_trx_type and
92 trx.transaction_subtype = p_doc_rec.ecx_trx_subtype and
93 trx.party_type = p_doc_rec.ecx_party_type and
94 proc.direction = 'OUT' and
95 trx.transaction_id = proc.transaction_id and
96 tph.party_type = p_doc_rec.ecx_party_type and
97 tph.party_site_id = p_doc_rec.tp_source_id and
98 tph.tp_header_id = tpd.tp_header_id and
99 tpd.ext_process_id = proc.ext_process_id;
100
101 begin
102 if ar_doc_transfer_standard.isDebugOn then
103 ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validateTrx(+)');
104 ar_doc_transfer_standard.debug('p_doc_rec.ecx_trx_type:'||p_doc_rec.ecx_trx_type);
105 ar_doc_transfer_standard.debug('p_doc_rec.ecx_trx_subtype:'||p_doc_rec.ecx_trx_subtype);
106 ar_doc_transfer_standard.debug('p_doc_rec.ecx_party_type:'||p_doc_rec.ecx_party_type);
107 ar_doc_transfer_standard.debug('p_doc_rec.tp_source_id:'||p_doc_rec.tp_source_id);
108 end if;
109
110 open ecxTrx; fetch ecxTrx into l_exists; close ecxTrx;
111 if l_exists is null then l_retcode := false;
112 else l_retcode := true; end if;
113 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('l_exists:'||l_exists); end if;
114 if not l_retcode then
115 fnd_message.set_name('AR', 'AR_DOC_TRS_INV_TP_SETUP');
116 fnd_message.set_token('DOCUMENT_TRANSFER_ID', p_doc_rec.document_transfer_id);
117 p_doc_rec.status := 'FAILED';
118 p_doc_rec.exception_type := 'AR';
119 p_doc_rec.exception_message := fnd_message.get;
120 end if;
121 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('STATUS:'||p_doc_rec.status); end if;
122 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validateTrx(-)'); end if;
123 return l_retcode;
124 end;
125
126 procedure validate_document_record(p_doc_rec in out nocopy ar_document_transfers%rowtype) is
127 begin
128 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validate_document_record(+)'); end if;
129 if not validateSource(p_doc_rec) then return; end if;
130 if not validateTpSource(p_doc_rec) then return; end if;
131 if not validateEventName(p_doc_rec) then return; end if;
132 if not validateTrx(p_doc_rec) then return; end if;
133 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validate_document_record(-)');end if;
134 end;
135
136 procedure notifyToAr(p_event_name in varchar2,
137 p_subject in varchar2,
138 p_doc_pkg in varchar2,
139 p_doc_proc in varchar2,
140 p_request_id in number,
141 p_exception_type in varchar2) is
142 l_url varchar2(200);
143
144 begin
145 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.notifyAr(+)'); end if;
146
147 l_url := 'JSP:/OA_HTML/OA.jsp?akRegionCode=ARDOCTRSPG'||'&'||
148 'akRegionApplicationId=222'||'&'||
149 'Query=Y'||'&'||
150 'ExceptionType=AR';
151 ar_notification_standard.raiseNotificationEvent(
152 p_event_name => p_event_name,
153 p_subject => p_subject,
154 p_doc_pkg => p_doc_pkg,
155 p_doc_proc => p_doc_proc,
156 p_role_name => 'FND_RESP222:'||to_char(fnd_global.resp_id),
157 p_url => l_url,
158 p_user_area1 => p_request_id,
159 p_user_area2 => p_exception_type);
160 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.notifyAr(-)'); end if;
161 end;
162
163 procedure notifyToSysadmin(p_event_name in varchar2,
164 p_subject in varchar2,
165 p_doc_pkg in varchar2,
166 p_doc_proc in varchar2,
167 p_request_id in number,
168 p_exception_type in varchar2) is
169 cursor role_csr is
170 select 'FND_RESP1:'||to_char(responsibility_id) role_name
171 from fnd_responsibility_tl
172 where application_id = 1;
173 l_url varchar2(200);
174 begin
175 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.notifyToSysadmin(+)'); end if;
176 l_url := 'JSP:/OA_HTML/OA.jsp?akRegionCode=ARDOCTRSPG'||'&'||
177 'akRegionApplicationId=222'||'&'||
178 'Query=Y'||'&'||
179 'ExceptionType=SYSTEM';
180 for l_role_rec in role_csr loop
181 ar_notification_standard.raiseNotificationEvent(
182 p_event_name => p_event_name,
183 p_subject => p_subject,
184 p_doc_pkg => p_doc_pkg,
185 p_doc_proc => p_doc_proc,
186 p_role_name => l_role_rec.role_name,
187 p_url => l_url,
188 p_user_area1 => p_request_id,
189 p_user_area2 => p_exception_type);
190 end loop;
191 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.notifyToSysadmin(-)'); end if;
192 end;
193
194 procedure notify(p_event_name in varchar2,
195 p_subject in varchar2,
196 p_doc_pkg in varchar2,
197 p_doc_proc in varchar2,
198 p_request_id in number,
199 p_exception_type in varchar2) is
200 begin
201 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.notify(+)'); end if;
202 if p_exception_type = 'SYSTEM' then
203 notifyToSysadmin(p_event_name,
204 p_subject,
205 p_doc_pkg,
206 p_doc_proc,
207 p_request_id,
208 p_exception_type);
209 elsif p_exception_type = 'AR' then
210 notifyToAr( p_event_name,
211 p_subject,
212 p_doc_pkg,
213 p_doc_proc,
214 p_request_id,
215 p_exception_type);
216 else
217 notifyToSysadmin(p_event_name,
218 p_subject,
219 p_doc_pkg,
220 p_doc_proc,
221 p_request_id,
222 p_exception_type);
223 end if;
224 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.notify(-)'); end if;
225 end;
226
227 procedure transfer_documents(errbuf out NOCOPY varchar2,
228 retcode out NOCOPY varchar2) is
229
230 cursor doc_trs is
231 select * from ar_document_transfers where status = 'WAITING';
232
233 cursor err_trs is
234 select distinct exception_type from ar_document_transfers
235 where status = 'FAILED';
236
237 l_subject varchar2(100);
238 l_sqlerrm varchar2(1000);
239 l_ok_rec boolean := false;
240 l_trx_type varchar2(100);
241 l_trx_subtype varchar2(100);
242
243 begin
244 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.transfer_documents(+)'); end if;
245 for doc_rec in doc_trs loop
246 doc_rec.request_id := fnd_global.conc_request_id;
247 validate_document_record(doc_rec);
248 if doc_rec.status = 'WAITING' then
249 ar_doc_transfer_standard.raiseTransferEvent(
250 p_event_name => doc_rec.event_name,
251 p_trx_type => doc_rec.ecx_trx_type,
252 p_trx_sub_type => doc_rec.ecx_trx_subtype,
253 p_party_id => null,
254 p_party_site_id => doc_rec.tp_source_id,
255 p_party_type => doc_rec.ecx_party_type,
256 p_doc_transfer_id => doc_rec.document_transfer_id);
257 else
258 ar_document_transfer_pkg.updateRow(doc_rec);
259 end if;
260 end loop;
261 for err_rec in err_trs loop
262 if l_subject is null then
263 fnd_message.set_name('AR','AR_DOC_TRS_SBJ');
264 fnd_message.set_token('REQUEST_ID', fnd_global.conc_request_id);
265 l_subject := fnd_message.get;
266 end if;
267 notify(p_event_name => 'oracle.apps.ar.transmit.notification',
268 p_subject => l_subject,
269 p_doc_pkg => 'AR_TRANSFER_DOCUMENT',
270 p_doc_proc => 'BUILD_BATCH_ERROR_MESSAGE',
271 p_request_id => fnd_global.conc_request_id,
272 p_exception_type => err_rec.exception_type);
273 end loop;
274 retcode := 0;
275 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.transfer_documents(-)'); end if;
276
277 exception
278 when others then
279 l_sqlerrm := sqlerrm(sqlcode) || fnd_global.newline ||
280 'Location: AR_TRANSFER_DOCUMENT.transfer_documents()'||fnd_global.newline||
281 'Time: '||to_char(sysdate, 'DD-MON-RRRR HH:MI:SS');
282 retcode := 2;
283 fnd_message.set_name('AR','AR_DOC_TRS_SBJ');
284 fnd_message.set_token('REQUEST_ID', fnd_global.conc_request_id);
285 l_subject := fnd_message.get;
286 if ar_doc_transfer_standard.isDebugOn then
287 ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENTS.transfer_documents()EXCEPTION:'||l_sqlerrm);
288 end if;
289 ar_notification_standard.notifyToSysadmin(l_subject,
290 l_sqlerrm);
291 end;
292
293 procedure build_batch_error_message_clob( document_id in varchar2,
294 display_type in varchar2,
295 document in out NOCOPY CLOB,
296 document_type in out NOCOPY varchar2) is
297 l_buffer varchar2(1000);
298 l_item_type varchar2(30);
299 l_item_key varchar2(30);
300 l_request_id number;
301 l_exception_type varchar2(30);
302
303 cursor err_csr is
304 select exception_message from ar_document_transfers
305 where exception_type = l_exception_type;
306 --where request_id = l_request_id and exception_type = l_exception_type;
307
308 begin
309 if ar_doc_transfer_standard.isDebugOn then
310 ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.build_batch_error_message(+)');
311 end if;
312 fnd_message.set_name('AR', 'AR_DOC_TRS_BODY');
313 WF_NOTIFICATION.WriteToClob(document,fnd_message.get||fnd_global.newline||fnd_global.newline);
314 ar_notification_standard.parseDocumentId(document_id, l_item_type, l_item_key);
315 l_request_id := wf_engine.getItemAttrText(itemType => l_item_type,
316 itemKey => l_item_key,
317 aname => 'USER_AREA1');
318 l_exception_type := wf_engine.getItemAttrText(itemType => l_item_type,
319 itemKey => l_item_key,
320 aname => 'USER_AREA2');
321 for err_rec in err_csr loop
322 WF_NOTIFICATION.WriteToClob(document, err_rec.exception_message||fnd_global.newline);
323 end loop;
324 document_type := 'text/plain';
325 if ar_doc_transfer_standard.isDebugOn then
326 ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.build_batch_error_message(-)');
327 end if;
328 end;
329
330 procedure build_batch_error_message( document_id in varchar2,
331 display_type in varchar2,
332 document in out NOCOPY varchar2,
333 document_type in out NOCOPY varchar2) is
334 l_buffer varchar2(32000);
335 l_item_type varchar2(30);
336 l_item_key varchar2(30);
337 l_request_id number;
338 l_exception_type varchar2(30);
339
340 cursor err_csr is
341 select exception_message from ar_document_transfers
342 where exception_type = l_exception_type;
343 --where request_id = l_request_id and exception_type = l_exception_type;
344
345 begin
346 if ar_doc_transfer_standard.isDebugOn then
347 ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.build_batch_error_message(+)');
348 end if;
349 fnd_message.set_name('AR', 'AR_DOC_TRS_BODY');
350
351 ar_notification_standard.parseDocumentId(document_id, l_item_type, l_item_key);
352 l_request_id := wf_engine.getItemAttrText(itemType => l_item_type,
353 itemKey => l_item_key,
354 aname => 'USER_AREA1');
355 l_exception_type := wf_engine.getItemAttrText(itemType => l_item_type,
356 itemKey => l_item_key,
357 aname => 'USER_AREA2');
358 for err_rec in err_csr loop
359 l_buffer := l_buffer ||err_rec.exception_message||fnd_global.newline;
360 end loop;
361 document := fnd_message.get||fnd_global.newline||fnd_global.newline ||
362 l_buffer;
363 document_type := 'text/plain';
364
365 if ar_doc_transfer_standard.isDebugOn then
366 ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.build_batch_error_message(-)');
367 end if;
368 end;
369
370
371 begin
372 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT(+)'); end if;
373 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT(-)'); end if;
374 end;
[Home] [Help]