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