[Home] [Help]
PACKAGE BODY: APPS.OKL_ESG_TRANSPORT_PVT
Source
1 PACKAGE BODY OKL_ESG_TRANSPORT_PVT AS
2 /* $Header: OKLESTRB.pls 120.2.12020000.4 2012/10/18 12:55:40 vloomba ship $ */
3
4 ---------------------------------------------------------------------------
5 -- PRIVATE MEMBER VARIABLES
6 ---------------------------------------------------------------------------
7 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
8 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
9 G_IS_DEBUG_EXCEPTION_ON BOOLEAN;
10 G_IS_DEBUG_ERROR_ON BOOLEAN;
11 G_IS_DEBUG_PROCEDURE_ON BOOLEAN;
12
13 PROCEDURE transport(p_transaction_number IN NUMBER)
14 IS
15 request utl_http.req;
16 response utl_http.resp;
17
18 CURSOR c_tp IS
19 SELECT p.party_id, tp.tp_header_id, tp.party_site_id, tp.party_type
20 FROM ecx_tp_headers tp
21 , hz_parties p
22 WHERE tp.party_id = p.party_id
23 AND p.party_name = 'SuperTrump';
24
25 CURSOR c_tt (b_transaction_number NUMBER) is
26 SELECT t.ext_subtype, t.protocol_address, t.username, t.password,
27 t.protocol_type -- added bug8209104
28 FROM ecx_tp_details_v t
29 , okl_stream_interfaces si
30 WHERE t.transaction_type = 'OKL_ST'
31 AND t.transaction_subtype = si.deal_type
32 AND si.transaction_number = b_transaction_number;
33
34 rec c_tp%ROWTYPE;
35 rec2 c_tt%ROWTYPE;
36
37 l_url VARCHAR2(255);
38 l_path VARCHAR2(255);
39 l_password VARCHAR2(255);
40
41 l_max_timeout NUMBER := 3600; -- seconds
42 inbound_buffer VARCHAR2(32767);
43 l_parameter_data VARCHAR2(4096);
44
45 ctime DATE;
46
47 l_api_name CONSTANT VARCHAR2(30) := 'transport';
48 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||p_transaction_number;
49 BEGIN
50 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
51 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module, p_transaction_number||': begin okl_esg_transport_pvt.transport');
52 END IF;
53
54 OPEN c_tp;
55 FETCH c_tp INTO rec;
56 CLOSE c_tp;
57
58 OPEN c_tt(p_transaction_number);
59 FETCH c_tt INTO rec2;
60 CLOSE c_tt;
61
62 l_url := rec2.protocol_address;
63
64 l_parameter_data :=
65 'TRANSACTION_TYPE=OKL_ST'||
66 '&'||'TRANSACTION_SUBTYPE='||rec2.ext_subtype||
67 '&'||'DOCUMENT_NUMBER='||p_transaction_number||
68 '&'||'PARTYID='||rec.party_id||
69 '&'||'PARTY_SITE_ID='||rec.party_site_id||
70 '&'||'PARTY_TYPE='||rec.party_type||
71 '&'||'PROTOCOL_TYPE='||rec2.protocol_type||
72 '&'||'PROTOCOL_ADDRESS='||rec2.protocol_address||
73 '&'||'USERNAME='||rec2.username||
74 '&'||'PASSWORD=XXXX'||'&';
75
76 ----------------------------------------------------------------------------------
77 -- 1. Send the outbound xml to Proxy Server
78 ----------------------------------------------------------------------------------
79 utl_http.set_transfer_timeout(l_max_timeout);
80 -- bug8209104 start
81 IF upper(rec2.protocol_type) = 'HTTPS' THEN
82 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
83 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module, p_transaction_number||': calling UTL_HTTP.SET_WALLET');
84 END IF;
85
86 l_path := 'file:' || fnd_profile.value('FND_DB_WALLET_DIR');
87 l_password := fnd_preference.eget('#INTERNAL','WF_WEBSERVICES','EWALLETPWD', 'WFWS_PWD');
88
89 UTL_HTTP.SET_WALLET (l_path, l_password);
90
91 END IF;
92 -- bug8209104 end
93
94 -- request := utl_http.begin_request(l_url, 'POST', 'HTTP/1.0'); -- commented bug8209104
95 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
96 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module, p_transaction_number||': calling utl_http.begin_request');
97 END IF;
98 request := utl_http.begin_request(url=>l_url, method=>'POST'); -- added bug8209104
99 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
100 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module, p_transaction_number||': calling utl_http.set_header');
101 END IF;
102 utl_http.set_header(request, 'Content-Type', 'application/x-www-form-urlencoded');
103 utl_http.set_header(request, 'Content-Length', lengthb(l_parameter_data));
104 utl_http.write_text(request, l_parameter_data);
105
106 ----------------------------------------------------------------------------------
107 -- 2. Get response from Proxy Server
108 ----------------------------------------------------------------------------------
109 response := utl_http.get_response(request);
110 utl_http.read_text(response, inbound_buffer);
111 utl_http.end_response(response);
112
113 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
114 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module, p_transaction_number||': end okl_esg_transport_pvt.transport');
115 END IF;
116 END transport;
117
118 procedure attach_trx_state(p_trx_num number) is
119
120 pos1 integer(20);
121 trans_clob clob :=empty_clob;
122 length_3 integer(20);
123 pos2 integer(20);
124
125 out_xml_copy clob:=empty_clob;
126 length_from_tag_end integer(20);
127 l_tag_end_xml clob := empty_clob();
128
129
130 l_outbound_xml clob := empty_clob();
131
132 cursor get_clob(p_contract_id IN NUMBER,
133 p_purpose_code IN VARCHAR2) IS
134 select a.transaction_state
135 from okl_stream_trx_data a,
136 okl_stream_interfaces b
137 where a.orig_khr_id = p_contract_id
138 and a.last_trx_state = 'Y'
139 and a.transaction_number = b.transaction_number
140 and nvl(b.purpose_code, 'PRIMARY') = p_purpose_code;
141
142 cursor is_erd_enabled(p_trx_number IN NUMBER ) IS
143 select gts.deal_type
144 ,gts.interest_calc_meth_code
145 from okl_sys_acct_opts_all sao
146 ,okl_stream_interfaces osi
147 ,okc_k_headers_all_b chr
148 ,okl_k_headers khr
149 ,okl_products pdt
150 ,okl_ae_tmpt_sets_all aes
151 ,okl_st_gen_tmpt_sets_all gts
152 Where sao.org_id = chr.authoring_org_id
153 and chr.id = osi.khr_id
154 and osi.transaction_number = p_trx_number
155 and chr.id = khr.id
156 and khr.pdt_id = pdt.id
157 and pdt.aes_id = aes.id
158 and aes.gts_id = gts.id;
159 cursor get_trx_contract(p_trx_id IN NUMBER) IS
160 select khr_id, nvl(purpose_code, 'PRIMARY') purpose_code
161 from okl_stream_interfaces
162 where transaction_number = p_trx_num;
163
164 cursor get_orig_contract(p_trx_id in number) is
165 select orig_khr_id, out_xml
166 from okl_stream_trx_data
167 where transaction_number = p_trx_id
168 for update;
169
170 l_khr_id number;
171 l_purpose_code varchar2(30);
172 l_api_name varchar2(30) := 'attach_trx_state';
173 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||p_trx_num;
174 l_orig_chr_id number;
175
176 l_deal_type VARCHAR2(300);
177 l_interest_calc_meth_code VARCHAR2(300);
178
179 begin
180
181 open get_trx_contract(p_trx_num);
182 fetch get_trx_contract into l_khr_id, l_purpose_code;
183 close get_trx_contract;
184
185 open is_erd_enabled(p_trx_num);
186 fetch is_erd_enabled into l_deal_type, l_interest_calc_meth_code;
187 close is_erd_enabled;
188
189 open get_orig_contract(p_trx_num);
190 fetch get_orig_contract into l_orig_chr_id, l_outbound_xml;
191 close get_orig_contract;
192
193 if ( ( instr(l_outbound_xml, '<RebookType>Prospective</RebookType>') > 0 ) OR
194 (l_deal_type like 'LEASE%' and l_interest_calc_meth_code = 'REAMORT')
195 ) then
196
197 open get_clob(l_orig_chr_id, l_purpose_code);
198 fetch get_clob into trans_clob;
199 close get_clob;
200
201 IF (DBMS_LOB.getlength(trans_clob) > 0) then
202 pos1:= dbms_lob.instr(l_outbound_xml,'<FormerTransactionState/>') - 1;
203 pos2 := pos1 + 26; /* length of the tag <FormerTransactionState/> + 1 */
204
205 /* copy data from the tag till the end of the clob */
206 DBMS_LOB.createtemporary (l_tag_end_xml, TRUE);
207 length_from_tag_end := DBMS_LOB.getlength(l_outbound_xml) - pos2 + 1;
208 dbms_lob.copy (l_tag_end_xml, l_outbound_xml, length_from_tag_end, 1, pos2);
209
210 /* trim the clob from the tag till the end */
211 DBMS_LOB.TRIM(l_outbound_xml, pos1);
212
213 /* append the transaction state to the trimmed clob, and append the tail end from the tag to the end */
214 update okl_stream_trx_data
215 set out_xml = l_outbound_xml || '<FormerTransactionState>'||trans_clob||'</FormerTransactionState>'|| l_tag_end_xml
216 where transaction_number = p_trx_num;
217 end if;
218 end if;
219
220 end attach_trx_state;
221
222 PROCEDURE store_outxml(p_transaction_number IN NUMBER, p_xml IN CLOB)
223 IS
224 PRAGMA AUTONOMOUS_TRANSACTION;
225
226 l_api_name CONSTANT VARCHAR2(30) := 'store_outxml';
227 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||p_transaction_number;
228
229 -- temp clob to store corrected out_xml. remove the CDATA formating by ECX
230 l_temp_clob CLOB;
231 l_contract_id number;
232 l_orig_contract_id number;
233 l_trx_reference number;
234
235 CURSOR get_orig_contract_csr( p_khr_id IN NUMBER)
236 IS
237 SELECT rbk_chr.orig_system_id1 original_chr_id
238 FROM okc_k_headers_all_b rbk_chr,
239 okl_trx_contracts_all trx
240 WHERE trx.khr_id_new = rbk_chr.id
241 AND trx.tsu_code = 'ENTERED'
242 AND trx.tcn_type = 'TRBK'
243 AND rbk_chr.id = p_khr_id
244 AND rbk_chr.orig_system_source_code = 'OKL_REBOOK'
245 UNION
246 SELECT orig_chr.id original_chr_id
247 FROM okc_k_headers_all_b orig_chr,
248 okl_trx_contracts_all trx
249 WHERE orig_chr.id = p_khr_id
250 AND trx.khr_id = orig_chr.id
251 AND trx.tsu_code = 'ENTERED'
252 AND trx.tcn_type = 'TRBK'
253 AND EXISTS
254 (
255 SELECT '1'
256 FROM okl_rbk_selected_contract rbk_chr
257 WHERE rbk_chr.khr_id = orig_chr.id
258 AND rbk_chr.status <> 'PROCESSED'
259 );
260
261 cursor get_purpose_code(p_transaction_number number) IS
262 select NVL(purpose_code, 'PRIMARY') purpose_code
263 from okl_stream_interfaces
264 where transaction_number = p_transaction_number;
265
266 cursor get_trx_reference(p_khr_id number, p_purpose_code varchar2) IS
267 select a.transaction_number
268 from okl_stream_trx_data a, okl_stream_interfaces b
269 where a.orig_khr_id = p_khr_id
270 and a.last_trx_state = 'Y'
271 and a.transaction_number = b.transaction_number
272 and NVL(b.purpose_code, 'PRIMARY') = p_purpose_code;
273
274 l_purpose_code varchar2(20);
275
276 BEGIN
277 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
278 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module, p_transaction_number||': begin okl_esg_transport_pvt.store_outxml');
279 END IF;
280
281 -- get the current contract_id. this could be a copy_contract_id
282 select khr_id
283 into l_contract_id
284 from okl_stream_interfaces
285 where transaction_number = p_transaction_number;
286
287 -- based on the above contract_id, get the original contract_id.
288 -- this is applicable to online rebooks.
289 open get_orig_contract_csr(l_contract_id);
290 fetch get_orig_contract_csr into l_orig_contract_id;
291 close get_orig_contract_csr;
292
293 if l_orig_contract_id is null then
294 l_orig_contract_id := l_contract_id;
295 end if;
296
297 open get_purpose_code(p_transaction_number);
298 fetch get_purpose_code into l_purpose_code;
299 close get_purpose_code;
300
301 open get_trx_reference(l_orig_contract_id, l_purpose_code);
302 fetch get_trx_reference into l_trx_reference;
303 close get_trx_reference;
304
305 INSERT INTO OKL_STREAM_TRX_DATA
306 (id,
307 transaction_number,
308 out_xml,
309 khr_id,
310 orig_khr_id,
311 last_trx_state,
312 trx_reference)
313 VALUES
314 (p_transaction_number
315 ,p_transaction_number
316 ,p_xml
317 ,l_contract_id
318 ,l_orig_contract_id
319 ,NULL
320 ,l_trx_reference
321 );
322
323 -- enh: to workaround the 64K size limitation on the transaction state node imposed by ECX,
324 -- attaching the transaction state after ECX gives the outbound without the transaction state.
325 -- attach the transaction state to the outbound xml.
326
327 attach_trx_state(p_trx_num => p_transaction_number);
328
329 commit;
330
331 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
332 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module, p_transaction_number||': end okl_esg_transport_pvt.store_outxml');
333 END IF;
334
335 END store_outxml;
336
337 PROCEDURE process_esg(p_transaction_number IN NUMBER
338 ,x_return_status OUT NOCOPY VARCHAR2)
339 IS
340 l_api_name CONSTANT VARCHAR2(30) := 'process_esg';
341 l_outbound_xml CLOB;
342 l_inbound_xml CLOB;
343
344 l_resultout VARCHAR2(1);
345 amount Binary_integer := 0;
346
347 l_return_status VARCHAR2(1);
348 l_timeout NUMBER;
349
350 ctime DATE;
351
352 l_module VARCHAR2(255) := G_MODULE||'.'||l_api_name||'.'||p_transaction_number;
353 BEGIN
354 x_return_status := G_RET_STS_SUCCESS;
355
356 IF(G_DEBUG_ENABLED = 'Y') THEN
357 G_IS_DEBUG_EXCEPTION_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_EXCEPTION);
358 END IF;
359
360 IF(G_DEBUG_ENABLED = 'Y') THEN
361 G_IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_PROCEDURE);
362 END IF;
363
364 IF(G_DEBUG_ENABLED = 'Y') THEN
365 G_IS_DEBUG_ERROR_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_ERROR);
366 END IF;
367
368 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
369 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module, p_transaction_number||': begin okl_esg_transport_pvt.process_esg');
370 END IF;
371
372 ----------------------------------------------------------------------------------
373 -- 1. Generate an outbound xml document from XML Gateway
374 ----------------------------------------------------------------------------------
375 l_outbound_xml := okl_xmlgen_pvt.generate_xmldocument(p_transaction_number);
376
377 ----------------------------------------------------------------------------------
378 -- 2. Store the outbound xml into OKL_STREAM_TRX_DATA table
379 ----------------------------------------------------------------------------------
380 store_outxml(p_transaction_number, l_outbound_xml);
381
382 ----------------------------------------------------------------------------------
383 -- 3. Transporting outbound xml to Proxy Server
384 ----------------------------------------------------------------------------------
385 transport(p_transaction_number);
386
387 IF(G_IS_DEBUG_PROCEDURE_ON) THEN
388 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_PROCEDURE, l_module, p_transaction_number||': end okl_esg_transport_pvt.process_esg');
389 END IF;
390
391 EXCEPTION
392 WHEN OTHERS THEN
393 IF(G_IS_DEBUG_ERROR_ON) THEN
394 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_ERROR, l_module, p_transaction_number||': '||SQLERRM(SQLCODE));
395 END IF;
396
397 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
398 p_msg_name => G_UNEXPECTED_ERROR,
399 p_token1 => G_SQLCODE_TOKEN,
400 p_token1_value => SQLCODE,
401 p_token2 => G_SQLERRM_TOKEN,
402 p_token2_value => SQLERRM
403 );
404 x_return_status := G_RET_STS_UNEXP_ERROR;
405 END process_esg;
406
407 END OKL_ESG_TRANSPORT_PVT;