[Home] [Help]
PACKAGE BODY: APPS.AR_TRANSFER_DOCUMENT
Source
1 package body ar_transfer_document as
2 /*$Header: ARTRSDCB.pls 120.7 2011/06/23 07:18:16 chuansha ship $ */
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
87 select 'x'
88 from ecx_transactions trx,
89 ecx_ext_processes proc,
90 ecx_tp_headers tph,
91 ecx_tp_details tpd
92 where trx.transaction_type = p_doc_rec.ecx_trx_type and
93 trx.transaction_subtype = p_doc_rec.ecx_trx_subtype and
94 trx.party_type = p_doc_rec.ecx_party_type and
95 proc.direction = 'OUT' and
96 trx.transaction_id = proc.transaction_id and
97 tph.tp_header_id = tpd.tp_header_id and
98 tpd.ext_process_id = proc.ext_process_id and
99 tph.party_type = p_doc_rec.ecx_party_type and
100 ( tph.party_site_id,tph.party_id ) in
101 ( select /* cardinality( sites 1) */
102 sites.party_site_id,
103 sites.party_id
104 from hz_party_sites sites
105 where sites.party_site_id = p_doc_rec.tp_source_id
106 );
107
108
109 begin
110 if ar_doc_transfer_standard.isDebugOn then
111 ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validateTrx(+)');
112 ar_doc_transfer_standard.debug('p_doc_rec.ecx_trx_type:'||p_doc_rec.ecx_trx_type);
113 ar_doc_transfer_standard.debug('p_doc_rec.ecx_trx_subtype:'||p_doc_rec.ecx_trx_subtype);
114 ar_doc_transfer_standard.debug('p_doc_rec.ecx_party_type:'||p_doc_rec.ecx_party_type);
115 ar_doc_transfer_standard.debug('p_doc_rec.tp_source_id:'||p_doc_rec.tp_source_id);
116 end if;
117
118 open ecxTrx; fetch ecxTrx into l_exists; close ecxTrx;
119 if l_exists is null then l_retcode := false;
120 else l_retcode := true; end if;
121 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('l_exists:'||l_exists); end if;
122 if not l_retcode then
123 fnd_message.set_name('AR', 'AR_DOC_TRS_INV_TP_SETUP');
124 fnd_message.set_token('DOCUMENT_TRANSFER_ID', p_doc_rec.document_transfer_id);
125 p_doc_rec.status := 'FAILED';
126 p_doc_rec.exception_type := 'AR';
127 p_doc_rec.exception_message := fnd_message.get;
128 end if;
129 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('STATUS:'||p_doc_rec.status); end if;
130 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validateTrx(-)'); end if;
131 return l_retcode;
132 end;
133
134 procedure validate_document_record(p_doc_rec in out nocopy ar_document_transfers%rowtype) is
135 begin
136 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validate_document_record(+)'); end if;
137 if not validateSource(p_doc_rec) then return; end if;
138 if not validateTpSource(p_doc_rec) then return; end if;
139 if not validateEventName(p_doc_rec) then return; end if;
140 if not validateTrx(p_doc_rec) then return; end if;
141 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.validate_document_record(-)');end if;
142 end;
143
144 procedure notifyToAr(p_event_name in varchar2,
145 p_subject in varchar2,
146 p_doc_pkg in varchar2,
147 p_doc_proc in varchar2,
148 p_request_id in number,
149 p_exception_type in varchar2) is
150 l_url varchar2(200);
151
152 begin
153 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.notifyAr(+)'); end if;
154
155 l_url := 'JSP:/OA_HTML/OA.jsp?akRegionCode=ARDOCTRSPG'||'&'||
156 'akRegionApplicationId=222'||'&'||
157 'Query=Y'||'&'||
158 'ExceptionType=AR';
159 ar_notification_standard.raiseNotificationEvent(
160 p_event_name => p_event_name,
161 p_subject => p_subject,
162 p_doc_pkg => p_doc_pkg,
163 p_doc_proc => p_doc_proc,
164 p_role_name => 'FND_RESP222:'||to_char(fnd_global.resp_id),
165 p_url => l_url,
166 p_user_area1 => p_request_id,
167 p_user_area2 => p_exception_type);
168 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.notifyAr(-)'); end if;
169 end;
170
171 procedure notifyToSysadmin(p_event_name in varchar2,
172 p_subject in varchar2,
173 p_doc_pkg in varchar2,
174 p_doc_proc in varchar2,
175 p_request_id in number,
176 p_exception_type in varchar2) is
177 cursor role_csr is
178 select 'FND_RESP1:'||to_char(responsibility_id) role_name
179 from fnd_responsibility_tl
180 where application_id = 1;
181 l_url varchar2(200);
182 begin
183 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.notifyToSysadmin(+)'); end if;
184 l_url := 'JSP:/OA_HTML/OA.jsp?akRegionCode=ARDOCTRSPG'||'&'||
185 'akRegionApplicationId=222'||'&'||
186 'Query=Y'||'&'||
187 'ExceptionType=SYSTEM';
188 for l_role_rec in role_csr loop
189 ar_notification_standard.raiseNotificationEvent(
190 p_event_name => p_event_name,
191 p_subject => p_subject,
192 p_doc_pkg => p_doc_pkg,
193 p_doc_proc => p_doc_proc,
194 p_role_name => l_role_rec.role_name,
195 p_url => l_url,
196 p_user_area1 => p_request_id,
197 p_user_area2 => p_exception_type);
198 end loop;
199 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.notifyToSysadmin(-)'); end if;
200 end;
201
202 procedure notify(p_event_name in varchar2,
203 p_subject in varchar2,
204 p_doc_pkg in varchar2,
205 p_doc_proc in varchar2,
206 p_request_id in number,
207 p_exception_type in varchar2) is
208 begin
209 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.notify(+)'); end if;
210 if p_exception_type = 'SYSTEM' then
211 notifyToSysadmin(p_event_name,
212 p_subject,
213 p_doc_pkg,
214 p_doc_proc,
215 p_request_id,
216 p_exception_type);
217 elsif p_exception_type = 'AR' then
218 notifyToAr( p_event_name,
219 p_subject,
220 p_doc_pkg,
221 p_doc_proc,
222 p_request_id,
223 p_exception_type);
224 else
225 notifyToSysadmin(p_event_name,
226 p_subject,
227 p_doc_pkg,
228 p_doc_proc,
229 p_request_id,
230 p_exception_type);
231 end if;
232 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.notify(-)'); end if;
233 end;
234
235 procedure transfer_documents(errbuf out NOCOPY varchar2,
236 retcode out NOCOPY varchar2) is
237
238 cursor doc_trs is
239 select * from ar_document_transfers where status = 'WAITING';
240
241 cursor err_trs is
242 select distinct exception_type from ar_document_transfers
243 where status = 'FAILED'
244 --bug 6667346
245 and request_id = fnd_global.conc_request_id;
246
247 l_subject varchar2(100);
248 l_sqlerrm varchar2(1000);
249 l_ok_rec boolean := false;
250 l_trx_type varchar2(100);
251 l_trx_subtype varchar2(100);
252 l_msgid VARCHAR2(200);
253
254 begin
255 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.transfer_documents(+)'); end if;
256 for doc_rec in doc_trs loop
257 doc_rec.request_id := fnd_global.conc_request_id;
258 validate_document_record(doc_rec);
259 if doc_rec.status = 'WAITING' then
260 ar_doc_transfer_standard.raiseTransferEvent(
261 p_event_name => doc_rec.event_name,
262 p_trx_type => doc_rec.ecx_trx_type,
263 p_trx_sub_type => doc_rec.ecx_trx_subtype,
264 p_party_id => null,
265 p_party_site_id => doc_rec.tp_source_id,
266 p_party_type => doc_rec.ecx_party_type,
267 p_doc_transfer_id => doc_rec.document_transfer_id);
268 /* Bug 8320506 Updating to status STARTED in order to prevent duplication*/
269 doc_rec.status := 'STARTED';
270 doc_rec.document_msgid := l_msgid;
271 doc_rec.last_submission_date := sysdate;
272 doc_rec.exception_type := null;
273 doc_rec.exception_message := null;
274 ar_document_transfer_pkg.updateRow(doc_rec);
275 else
276 ar_document_transfer_pkg.updateRow(doc_rec);
277 end if;
278 end loop;
279 for err_rec in err_trs loop
280 if l_subject is null then
281 fnd_message.set_name('AR','AR_DOC_TRS_SBJ');
282 fnd_message.set_token('REQUEST_ID', fnd_global.conc_request_id);
283 l_subject := fnd_message.get;
284 end if;
285 notify(p_event_name => 'oracle.apps.ar.transmit.notification',
286 p_subject => l_subject,
287 p_doc_pkg => 'AR_TRANSFER_DOCUMENT',
288 p_doc_proc => 'BUILD_BATCH_ERROR_MESSAGE',
289 p_request_id => fnd_global.conc_request_id,
290 p_exception_type => err_rec.exception_type);
291 end loop;
292 retcode := 0;
293 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.transfer_documents(-)'); end if;
294
295 exception
296 when others then
297 l_sqlerrm := sqlerrm(sqlcode) || fnd_global.newline ||
298 'Location: AR_TRANSFER_DOCUMENT.transfer_documents()'||fnd_global.newline||
299 'Time: '||to_char(sysdate, 'DD-MON-RRRR HH:MI:SS');
300 retcode := 2;
301 fnd_message.set_name('AR','AR_DOC_TRS_SBJ');
302 fnd_message.set_token('REQUEST_ID', fnd_global.conc_request_id);
303 l_subject := fnd_message.get;
304 if ar_doc_transfer_standard.isDebugOn then
305 ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENTS.transfer_documents()EXCEPTION:'||l_sqlerrm);
306 end if;
307 ar_notification_standard.notifyToSysadmin(l_subject,
308 l_sqlerrm);
309 end;
310
311 procedure build_batch_error_message_clob( document_id in varchar2,
312 display_type in varchar2,
313 document in out NOCOPY CLOB,
314 document_type in out NOCOPY varchar2) is
315 l_buffer varchar2(1000);
316 l_item_type varchar2(30);
317 l_item_key varchar2(30);
318 l_request_id number;
319 l_exception_type varchar2(30);
320
321 cursor err_csr is
322 select exception_message from ar_document_transfers
323 where exception_type = l_exception_type;
324 --where request_id = l_request_id and exception_type = l_exception_type;
325
326 begin
327 if ar_doc_transfer_standard.isDebugOn then
328 ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.build_batch_error_message(+)');
329 end if;
330 fnd_message.set_name('AR', 'AR_DOC_TRS_BODY');
331 WF_NOTIFICATION.WriteToClob(document,fnd_message.get||fnd_global.newline||fnd_global.newline);
332 ar_notification_standard.parseDocumentId(document_id, l_item_type, l_item_key);
333 l_request_id := wf_engine.getItemAttrText(itemType => l_item_type,
334 itemKey => l_item_key,
335 aname => 'USER_AREA1');
336 l_exception_type := wf_engine.getItemAttrText(itemType => l_item_type,
337 itemKey => l_item_key,
338 aname => 'USER_AREA2');
339 for err_rec in err_csr loop
340 WF_NOTIFICATION.WriteToClob(document, err_rec.exception_message||fnd_global.newline);
341 end loop;
342 document_type := 'text/plain';
343 if ar_doc_transfer_standard.isDebugOn then
344 ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.build_batch_error_message(-)');
345 end if;
346 end;
347
348 procedure build_batch_error_message( document_id in varchar2,
349 display_type in varchar2,
350 document in out NOCOPY varchar2,
351 document_type in out NOCOPY varchar2) is
352 l_buffer varchar2(32000);
353 l_item_type varchar2(30);
354 l_item_key varchar2(30);
355 l_request_id number;
356 l_exception_type varchar2(30);
357
358 cursor err_csr is
359 select exception_message from ar_document_transfers
360 --where exception_type = l_exception_type;
361 --bug 6667346
362 where request_id = l_request_id and exception_type = l_exception_type;
363
364 begin
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 fnd_message.set_name('AR', 'AR_DOC_TRS_BODY');
369
370 ar_notification_standard.parseDocumentId(document_id, l_item_type, l_item_key);
371 l_request_id := wf_engine.getItemAttrText(itemType => l_item_type,
372 itemKey => l_item_key,
373 aname => 'USER_AREA1');
374 l_exception_type := wf_engine.getItemAttrText(itemType => l_item_type,
375 itemKey => l_item_key,
376 aname => 'USER_AREA2');
377 for err_rec in err_csr loop
378 l_buffer := l_buffer ||err_rec.exception_message||fnd_global.newline;
379 end loop;
380 document := fnd_message.get||fnd_global.newline||fnd_global.newline ||
381 l_buffer;
382 document_type := 'text/plain';
383
384 if ar_doc_transfer_standard.isDebugOn then
385 ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT.build_batch_error_message(-)');
386 end if;
387 end;
388
389
390 begin
391 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT(+)'); end if;
392 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_TRANSFER_DOCUMENT(-)'); end if;
393 end;