[Home] [Help]
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;
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
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;
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
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
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
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);
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
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;
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,
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'||'&'||
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,
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';
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,
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,
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
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
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;
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 ||
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);
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;
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);
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,
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);
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:
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;
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;