[Home] [Help]
PACKAGE BODY: APPS.AR_DOC_TRANSFER_STANDARD
Source
1 package body ar_doc_transfer_standard as
2 /*$Header: ARDOCTFB.pls 120.10.12000000.4 2007/08/17 14:24:12 nproddut ship $ */
3
4 PG_DEBUG varchar2(1);
5 PG_DEBUG_PATH varchar2(1000);
6
7
8 PROCEDURE CallbackFunction(p_item_type IN VARCHAR2,
9 p_item_key IN VARCHAR2,
10 p_actid IN NUMBER,
11 p_funmode IN VARCHAR2,
12 p_result OUT NOCOPY VARCHAR2) is
13 l_org_id NUMBER;
14
15 begin
16 l_org_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
17 p_item_key,
18 'ORG_ID');
19
20 if ( p_funmode = 'SET_CTX' ) then
21 if (l_org_id is not null) then
22 --replaced the call to fnd_client_info with mo_global[bug 5729562]
23 mo_global.set_policy_context('S',l_org_id);
24 end if;
25 p_result := 'TRUE';
26 return;
27 end if;
28
29 if ( p_funmode = 'TEST_CTX') then
30 if(l_org_id is not null) then
31 --replaced the call to fnd_client_info with mo_global[bug 5729562]
32 mo_global.set_policy_context('S',l_org_id);
33 end if;
34 IF (nvl(rtrim(substrb(USERENV('CLIENT_INFO'), 1, 10)),'NULL') = nvl(to_char(l_org_id),'NULL')) THEN
35 p_result := 'TRUE';
36 ELSE
37 p_result := 'FALSE';
38 END IF;
39 return;
40 end if;
41 end;
42
43 function otaCallBackRule(p_subscription_guid in raw,
44 p_event in out nocopy wf_event_t) return varchar2 is
45 l_msgid raw(16);
46 l_extType varchar2(80);
47 l_extSubType varchar2(80);
48 l_msg varchar2(4000);
49 l_returnCode varchar2(240);
50 l_errType varchar2(10);
51 begin
52 if isDebugOn then debug('AR_DOC_TRANSFER_STANDARD.otaCallBackRule(+)'); end if;
53
54 l_msgid := p_event.getValueForParameter('ECX_MSGID');
55 l_extType := p_event.getValueForParameter('ECX_TRANSACTION_TYPE');
56 l_extSubType := p_event.getValueForParameter('ECX_TRANSACTION_SUBTYPE');
57 l_returnCode := p_event.getValueForParameter('ECX_RETURN_CODE');
58 l_msg := p_event.getValueForParameter('ECX_ERROR_MSG');
59 fnd_message.set_name('ECX', l_msg);
60 l_msg := fnd_message.get;
61 if l_returnCode <> '0' and l_msg is not null then
62 l_errType := 'SYSTEM';
63 end if;
64 if l_extType = 'INVOICE' and l_extSubType = 'PROCESS' then
65 update ar_document_transfers
66 set status = decode(l_returnCode,'0','TRANSMITTED','FAILED'),
67 exception_message = decode(l_returnCode, '0', null, l_msg),
68 exception_type = l_errType
69 where document_msgid = l_msgid;
70 end if;
71 if isDebugOn then debug('AR_DOC_TRANSFER_STANDARD.otaCallBackRule(-)'); end if;
72 return 'SUCCESS';
73 exception
74 when others then
75 if isDebugOn then
76 debug('AR_DOC_TRANSFER_STANDARD.otaCallBackRule(Exception)');
77 debug(sqlerrm);
78 end if;
79 return 'ERROR';
80 end;
81
82 procedure debug(p_line in varchar2) is
83 begin
84 arp_standard.debug(p_line);
85 end;
86
87 function isDebugOn return boolean is
88 l_path varchar2(200);
89 begin
90 if pg_debug in ('Y', 'C') then
91 return true;
92 else
93 return false;
94 end if;
95 exception
96 when others then
97 return false;
98 end;
99
100
101 procedure updateStatus is
102 begin
103 if isDebugOn then debug('AR_DOC_TRANSFER_STANDARD.updateStatus(+)'); end if;
104 /*
105 update ar_document_transfers doc
106 set doc.status = 'TRANSMITTED'
107 where doc.status = 'STARTED' and
108 exists (select 'X'
109 from ecx_out_process_v ecx
110 where ecx.document_id = doc.document_transfer_id and
111 ecx.transaction_type = doc.ecx_trx_type and
112 ecx.transaction_subtype = doc.ecx_trx_subtype and
113 ecx.party_site_id = doc.tp_source_id and
114 ecx.out_msgid = doc.document_msgid and
115 ltrim(rtrim(ecx.delivery_status)) = '0');
116 update ar_document_transfers doc
117 set doc.status = 'FAILED',
118 doc.exception_type = 'SYSTEM',
119 doc.exception_message = 'Oracle Transport Agent: maximum transport attempts exceeded.'||
120 'Please contact System administrator'
121 where doc.status = 'STARTED' and
122 exists (select 'X'
123 from ecx_out_process_v ecx
124 where ecx.document_id = doc.document_transfer_id and
125 ecx.transaction_type = doc.ecx_trx_type and
126 ecx.transaction_subtype = doc.ecx_trx_subtype and
127 ecx.party_site_id = doc.tp_source_id and
128 ecx.out_msgid = doc.document_msgid and
129 ltrim(rtrim(ecx.delivery_status)) <> '0');
130 */
131 if isDebugOn then debug('AR_DOC_TRANSFER_STANDARD.updateStatus(-)');end if;
132 end;
133
134
135 procedure email_transfer( ITEMTYPE IN VARCHAR2,
136 ITEMKEY IN VARCHAR2,
137 ACTID IN NUMBER,
138 FUNCMODE IN VARCHAR2,
139 RESULTOUT IN OUT NOCOPY VARCHAR2) is
140 begin
141 if isDebugOn then debug('AR_DOC_TRANSFER_STANDARD.email_transfer(-)');end if;
142 if isDebugOn then debug('AR_DOC_TRANSFER_STANDARD.email_transfer(-)');end if;
143 end;
144
145 procedure edi_transfer( ITEMTYPE IN VARCHAR2,
146 ITEMKEY IN VARCHAR2,
147 ACTID IN NUMBER,
148 FUNCMODE IN VARCHAR2,
149 RESULTOUT IN OUT NOCOPY VARCHAR2) is
150 begin
151 if isDebugOn then debug('AR_DOC_TRANSFER_STANDARD.edi_transfer(-)');end if;
152 if isDebugOn then debug('AR_DOC_TRANSFER_STANDARD.edi_transfer(-)');end if;
153 end;
154
155
156 procedure xml_transfer( ITEMTYPE IN VARCHAR2,
157 ITEMKEY IN VARCHAR2,
158 ACTID IN NUMBER,
159 FUNCMODE IN VARCHAR2,
160 RESULTOUT IN OUT NOCOPY VARCHAR2) is
161
162 l_doc_transfer_id VARCHAR2(200);
163 l_doc_trx_type varchar2(200);
164 l_msgid VARCHAR2(200);
165 l_sqlerrm VARCHAR2(2000);
166 l_subject VARCHAR2(200);
167 cursor doc is select * from ar_document_transfers
168 where document_transfer_id = l_doc_transfer_id;
169
170 doc_rec doc%rowtype;
171 l_x varchar2(1);
172 l_org_id NUMBER;
173
174
175 begin
176 if isDebugOn then debug('AR_DOC_TRANSFER_STANDARD.xml_transfer(+)');end if;
177
178 l_doc_transfer_id := wf_engine.getItemAttrText(itemtype,
179 itemkey,
180 'ECX_DOCUMENT_ID');
181 l_doc_trx_type := wf_engine.getItemAttrText(itemtype,
182 itemkey,
183 'ECX_TRANSACTION_TYPE');
184 open doc; fetch doc into doc_rec; close doc;
185
186
187 begin
188
189 if fnd_global.org_id is not null then
190 if doc_rec.source_table = 'RA_CUSTOMER_TRX' then
191 select org_id into l_org_id from ra_customer_trx_all
192 where customer_trx_id = doc_rec.source_id;
193
194 if l_org_id is not null then
195 --replaced the call to fnd_client_info with mo_global[bug 5729562]
196 mo_global.set_policy_context('S',l_org_id);
197 end if;
198
199 end if;
200 end if;
201
202 if isDebugOn then debug('ECX_STANDARD.SEND(+)');end if;
203 ECX_STANDARD.SEND(ITEMTYPE,ITEMKEY,ACTID,FUNCMODE,RESULTOUT);
204 l_msgid := wf_engine.GetItemAttrText(itemtype,
205 itemkey,
206 'ECX_MSGID_ATTR');
207 update ecx_doclogs
208 set cb_event_name = 'oracle.apps.ar.transmit.otaCallback',
209 cb_event_key = to_char(sysdate, 'DD-MON-RRRR-HHMISS')
210 where msgid = l_msgid;
211
212
213 if isDebugOn then debug('ECX_STANDARD.SEND(-)');end if;
214
215 exception
216 when others then
217 resultout := 'COMPLETE';
218 doc_rec.status := 'FAILED';
219 doc_rec.exception_type := 'SYSTEM';
220 fnd_message.set_name('ECX','ECX_ERROR_EMAIL_HEADER');
221 fnd_message.set_token('TRANSACTION_TYPE',l_doc_trx_type);
222 fnd_message.set_token('DOCUMENT_NUMBER',l_doc_transfer_id);
223 doc_rec.exception_message := fnd_message.get ||
224 fnd_global.newline||
225 sqlerrm;
226 ar_document_transfer_pkg.updateRow(doc_rec);
227 return;
228 end;
229
230 l_msgid := wf_engine.GetItemAttrText(itemtype,
231 itemkey,
232 'ECX_MSGID_ATTR');
233 if isDebugOn then
234 debug('l_doc_transfer_id:'||l_doc_transfer_id);
235 debug('l_msgid:'||l_msgid);
236 debug('doc_rec.dcument_transfer_id:'||to_char(doc_rec.document_transfer_id));
237 end if;
238 doc_rec.status := 'STARTED';
239 doc_rec.document_msgid := l_msgid;
240 doc_rec.last_submission_date := sysdate;
241 doc_rec.exception_type := null;
242 doc_rec.exception_message := null;
243 if isDebugOn then
244 debug('doc_rec.status:'||doc_rec.status);
245 debug('doc_rec.document_msgid:'||doc_rec.document_msgid);
246 end if;
247 ar_document_transfer_pkg.updateRow(doc_rec);
248 resultout := 'COMPLETE';
249 if isDebugOn then
250 debug('AR_DOC_TRANSFER_STANDARD.xml_transfer(-)');
251 end if;
252 end;
253
254 procedure raiseTransferEvent(p_event_name in VARCHAR2,
255 p_trx_type in VARCHAR2,
256 p_trx_sub_type in VARCHAR2,
257 p_party_id in NUMBER,
258 p_party_site_id in NUMBER,
259 p_party_type in VARCHAR2,
260 p_doc_transfer_id in NUMBER) is
261
262 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
263 l_itemkey varchar2(100);
264 begin
265 if isDebugOn then
266 debug('AR_DOC_TRANSFER_STANDARD.raiseTransferEvent(+)');
267 debug('p_event_name:'||p_event_name);
268 debug('p_trx_type:'||p_trx_type);
269 debug('p_trx_sub_type:'||p_trx_sub_type);
270 debug('p_party_id:'||p_party_id);
271 debug('p_party_site_id:'||p_party_site_id);
272 debug('p_party_type:'||p_party_type);
273 debug('p_doc_transfer_id:'||p_doc_transfer_id);
274 end if;
275 l_itemkey := to_char(p_doc_transfer_id)||':'||to_char(sysdate, 'DD-MON-RRRR-HHMISS');
276 if isDebugOn then debug('l_itemkey:'||l_itemkey); end if;
277
278 wf_event.AddParameterToList (
279 p_name => 'ECX_TRANSACTION_TYPE',
280 p_value => p_trx_type,
281 p_parameterlist => l_parameter_list );
282
283 wf_event.AddParameterToList (
284 p_name => 'ECX_TRANSACTION_SUBTYPE',
285 p_value => p_trx_sub_type,
286 p_parameterlist => l_parameter_list );
287
288 wf_event.AddParameterToList (
289 p_name => 'ECX_PARTY_ID',
290 p_value => NULL,
291 p_parameterlist => l_parameter_list );
292
293 wf_event.AddParameterToList(
294 p_name => 'ECX_PARTY_SITE_ID',
295 p_value => p_party_site_id,
296 p_parameterlist => l_parameter_list );
297
298 wf_event.AddParameterToList(
299 p_name => 'ECX_PARTY_TYPE',
300 p_value => p_party_type,
301 p_parameterlist => l_parameter_list );
302
303 wf_event.AddParameterToList (
304 p_name => 'ECX_DOCUMENT_ID',
305 p_value => p_doc_transfer_id,
306 p_parameterlist => l_parameter_list );
307
308 wf_event.AddParameterToList (
309 p_name => 'ORG_ID',
310 p_value => fnd_global.org_id,
311 p_parameterlist => l_parameter_list );
312
313 wf_event.raise(
314 p_event_name => p_event_name,
315 p_event_key => l_itemkey,
316 p_parameters => l_parameter_list);
317
318 l_parameter_list.DELETE;
319 if isDebugOn then debug('AR_DOC_TRANSFER_STANDARD.raiseTransferEvent(-)'); end if;
320 end;
321
322 begin
323 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
324
325 select decode(instrb(value,',',1,1),0,value,substrb(value, 1, instrb(value, ',',1)-1))
326 into PG_DEBUG_PATH from v$parameter
327 where name = 'utl_file_dir';
328
329 if PG_DEBUG in ('Y','C') then
330 arp_standard.enable_file_debug(PG_DEBUG_PATH, 'AR_DOCUMENT_TRANSFER.log');
331 end if;
332 exception
333 when others then
334 PG_DEBUG := 'N';
335 end;