eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IBY_BANKACCXFR_PUB

Source


1 PACKAGE BODY IBY_BANKACCXFR_PUB AS
2 /*$Header: ibybareqb.pls 120.3 2005/09/15 01:33:03 rameshsh noship $ */
3 
4  -- *** Declaring global datatypes and variables ***
5  G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_BANKACCXFR_PUB';
6 
7  g_validation_level CONSTANT NUMBER  := FND_API.G_VALID_LEVEL_FULL;
8 
9 ----------------------------------------------------
10 -- *** Declaring global datatypes and variables ***
11 ----------------------------------------------------
12 
13 /* ============================================================================
14    --1. OraPmtBatchReq
15    --   -----------------------
16    --   Start of comments
17    --      API name  : OraPmtBankAccXfrBatchReq
18    --      Type      : Public
19    --      Pre-reqs  : None
20    --      Function  : Handles Batch Payment requests from Accounts Receivables
21    --      Parameters:
22    --      IN        : p_api_version        IN  NUMBER
23    --                  p_init_msg_list      IN  VARCHAR2
24    --                  p_commit             IN  VARCHAR2
25    --                  p_validation_level   IN  NUMBER
26    --                  p_ecapp_id           IN  NUMBER
27    --		       x_return_status      OUT VARCHAR2,
28    --                  x_msg_count          OUT NUMBER,
29    -- 	               x_msg_data           OUT VARCHAR2,
30    --                  p_pmt_batch_req_rec  IN  BankAccXfrReq_Rec_Type,
31    --                  p_pmt_batch_trxn_tbl IN  BankAccXfrTrxn_Tbl_Type,
32    --                  x_batch_resp_rec     IN  BankAccXfrResp_Rec_Type,
33    --                  x_batch_respdet_rec  IN  BankAccXfrRespDet_Tbl_Type
34    --   Version :
35    --   Current version      1.0
36    --   Previous version     1.0
37    --   Initial version      1.0
38    --   End of comments
39  ============================================================================ */
40 
41 PROCEDURE OraPmtBatchReq(
42          p_api_version        IN    NUMBER,
43          p_init_msg_list      IN    VARCHAR2  DEFAULT FND_API.G_FALSE,
44          p_commit             IN    VARCHAR2  DEFAULT FND_API.G_FALSE,
45          p_validation_level   IN    NUMBER  DEFAULT FND_API.G_VALID_LEVEL_FULL,
46 	 p_payee_id           IN    VARCHAR2,
47          p_ecapp_id           IN    NUMBER,
48          x_return_status      OUT   NOCOPY VARCHAR2,
49          x_msg_count          OUT   NOCOPY NUMBER,
50          x_msg_data           OUT   NOCOPY VARCHAR2,
51          p_batch_req_rec      IN    BankAccXfrReq_Rec_Type,
52          p_accxfr_trxn_tbl    IN    BankAccXfrTrxn_Tbl_Type,
53          x_batch_resp_rec     OUT   NOCOPY BankAccXfrResp_Rec_Type,
54 	 x_batch_respdet_tbl  OUT   NOCOPY BankAccXfrRespDet_Tbl_Type
55 	 ) IS
56 
57         l_get_baseurl   VARCHAR2(2000);
58         --The following 3 variables are meant for output of
59         --get_baseurl procedure.
60         l_status_url    VARCHAR2(2000);
61         l_msg_count_url NUMBER := 0;
62         l_msg_data_url  VARCHAR2(2000);
63         l_pmt_mode      VARCHAR2(80) := 'OFFLINE';
64 
65         l_api_name      CONSTANT  VARCHAR2(30) := 'OraPmtBatchReq';
66         l_oapf_action   CONSTANT  VARCHAR2(30) := 'oraPmtBatchReq';
67         l_api_version   CONSTANT  NUMBER := 1.0;
68 
69         l_url           VARCHAR2(30000) ;
70         l_html          VARCHAR2(32767) ;
71 
72         l_names         IBY_NETUTILS_PVT.v240_tbl_type;
73         l_values        IBY_NETUTILS_PVT.v240_tbl_type;
74 
75         --The following 3 variables are meant for output of
76         --unpack_results_url procedure.
77         l_status        NUMBER := 0;
78         l_errcode       NUMBER := 0;
79         l_curr_index   NUMBER :=1;
80         l_index        NUMBER := 1;
81         l_errmessage    VARCHAR2(2000) := 'Success';
82         l_ret_val      PLS_INTEGER;
83         l_conn         UTL_TCP.CONNECTION;  -- TCP/IP connection to the Web server
84 
85         l_content_len  NUMBER := 0;
86    	    l_pos          NUMBER := 0;
87      	l_post_body    VARCHAR2(30000);
88         l_tempclob     CLOB;
89 
90         -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
91 	--
92 	l_db_nls	p_batch_req_rec.NLS_LANG%TYPE := NULL;
93 	l_ecapp_nls	p_batch_req_rec.NLS_LANG%TYPE := NULL;
94 
95 	v_trxnTimestamp	DATE	:= NULL;
96 
97         --Defining a local variable to hold the payment instrument type.
98         l_pmtinstr_type VARCHAR2(200);
99         l_sec_cred NUMBER;
100 
101 BEGIN
102 
103       ---------------------------------------------------
104       -- Standard call to check for call compatibility.
105       ---------------------------------------------------
106       IF NOT FND_API.Compatible_API_Call ( l_api_version,
107 
108                                           p_api_version,
109                                           l_api_name,
110                                           G_PKG_NAME )
111       THEN
112         FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
113         FND_MSG_PUB.Add;
114         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115       END IF;
116 
117       --------------------------------------------------------------
118       -- Initialize message list if p_init_msg_list is set to TRUE.
119       --------------------------------------------------------------
120 
121       IF FND_API.to_Boolean( p_init_msg_list ) THEN
122         FND_MSG_PUB.initialize;
123       END IF;
124 
125 
126       -----------------------------------------------------------------------------
127       -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
128       -----------------------------------------------------------------------------
129       IF (p_validation_level <> g_validation_level) THEN
130         FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
131         FND_MSG_PUB.Add;
132         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
133       END IF;
134 
135      -----------------------
136      -- get iPayment URL
137      -----------------------
138      IBY_NETUTILS_PVT.get_baseurl(l_get_baseurl);
139      l_url := l_get_baseurl;
140      l_url := rtrim(l_url,'?');
141 
142      l_db_nls := IBY_NETUTILS_PVT.get_local_nls();
143      l_ecapp_nls := NULL; -- not passed in this api??
144 
145      -------------------------
146      -- create temporary clob
147      -------------------------
148      DBMS_LOB.CreateTemporary(l_tempclob, TRUE);
149 
150      -----------------------------
151      -- reading header level data
152      -----------------------------
153      IBY_NETUTILS_PVT.check_mandatory('OapfAction', l_oapf_action, l_post_body, l_db_nls, l_ecapp_nls);
154      IBY_NETUTILS_PVT.check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_post_body, l_db_nls, l_ecapp_nls);
155      -- Payee ID
156      IBY_NETUTILS_PVT.check_mandatory('OapfStoreId', p_payee_ID, l_post_body, l_db_nls, l_ecapp_nls);
157 
158      -- the mode has to be mandatory as per the specifications
159 
160      IBY_NETUTILS_PVT.check_mandatory('OapfMode', l_pmt_mode, l_post_body, l_db_nls, l_ecapp_nls);
161      IBY_NETUTILS_PVT.check_mandatory('OapfOrgId', p_batch_req_rec.Org_ID, l_post_body, l_db_nls, l_ecapp_nls);
162      IBY_NETUTILS_PVT.check_mandatory('OapfBatchId', p_batch_req_rec.ECBatch_ID, l_post_body, l_db_nls, l_ecapp_nls);
163 
164      IBY_NETUTILS_PVT.check_mandatory('OapfPmtInstrType', p_batch_req_rec.PmtInstrType, l_post_body, l_db_nls, l_ecapp_nls);
165      IBY_NETUTILS_PVT.check_mandatory('OapfBatchSize', p_accxfr_trxn_tbl.count, l_post_body, l_db_nls, l_ecapp_nls);
166 
167 
168      --reading trxn detail data
169      IBY_NETUTILS_PVT.check_mandatory('OapfPmtRegId', p_batch_req_rec.Payee_Instr_ID, l_post_body, l_db_nls, l_ecapp_nls);
170 
171 
172      -- set security token
173      --
174      iby_security_pkg.store_credential(l_post_body,l_sec_cred);
175      IBY_NETUTILS_PVT.check_mandatory('OapfSecurityToken', TO_CHAR(l_sec_cred),
176           l_post_body, l_db_nls, l_ecapp_nls);
177 
178 
179      -- write to CLOB
180      DBMS_LOB.WriteAppend(l_tempclob, length(l_post_body), l_post_body);
181 
182      --dbms_output.put_line('String :'||l_post_body);
183 
184      -- Writing Transaction Level Data
185      WHILE (l_curr_index <= p_accxfr_trxn_tbl.last) LOOP
186 
187          l_post_body := '';
188 
189          IBY_NETUTILS_PVT.check_mandatory('OapfPayerId-'|| l_index,p_accxfr_trxn_tbl(l_curr_index).Payer_Name, l_post_body, l_db_nls, l_ecapp_nls);
190          IBY_NETUTILS_PVT.check_mandatory('OapfPayerInstrId-'|| l_index,p_accxfr_trxn_tbl(l_curr_index).Payer_Instr_ID, l_post_body, l_db_nls, l_ecapp_nls);
191          IBY_NETUTILS_PVT.check_mandatory('OapfPmtInstrSubType-'|| l_index,p_accxfr_trxn_tbl(l_curr_index).PmtInstrSubType, l_post_body, l_db_nls, l_ecapp_nls);
192          IBY_NETUTILS_PVT.check_mandatory('OapfOrderId-'|| l_index,p_accxfr_trxn_tbl(l_curr_index).Tangible_ID, l_post_body, l_db_nls, l_ecapp_nls);
193          IBY_NETUTILS_PVT.check_mandatory('OapfPrice-'|| l_index,p_accxfr_trxn_tbl(l_curr_index).Tangible_Amount, l_post_body, l_db_nls, l_ecapp_nls);
194          IBY_NETUTILS_PVT.check_optional('OapfMemo-'|| l_index,p_accxfr_trxn_tbl(l_curr_index).Memo, l_post_body, l_db_nls, l_ecapp_nls);
195          IBY_NETUTILS_PVT.check_optional('OapfRefNumber-'|| l_index,p_accxfr_trxn_tbl(l_curr_index).RefInfo, l_post_body, l_db_nls, l_ecapp_nls);
196          IBY_NETUTILS_PVT.check_mandatory('OapfSchedDate-'|| l_index,to_char(p_accxfr_trxn_tbl(l_curr_index).Settlement_Date,'YYYY-MM-DD'),l_post_body, l_db_nls, l_ecapp_nls);
197          IBY_NETUTILS_PVT.check_mandatory('OapfIssueDate-'|| l_index,to_char(p_accxfr_trxn_tbl(l_curr_index).IssueDate,'YYYY-MM-DD'), l_post_body, l_db_nls, l_ecapp_nls);
198 	     IBY_NETUTILS_PVT.check_mandatory('OapfTrxnCurr-'|| l_index,p_accxfr_trxn_tbl(l_curr_index).Currency_Code, l_post_body, l_db_nls, l_ecapp_nls);
199 	     IBY_NETUTILS_PVT.check_optional('OapfNLSLang-'|| l_index,p_accxfr_trxn_tbl(l_curr_index).NLS_LANG, l_post_body, l_db_nls, l_ecapp_nls);
200 
201          --
202          -- Customer reference / debtors reference
203          --
204          IBY_NETUTILS_PVT.check_optional('OapfCustomerRef-'|| l_index,p_accxfr_trxn_tbl(l_curr_index).customerRef, l_post_body, l_db_nls, l_ecapp_nls);
205 
206          -- Adder for new fields ordermedium and EFTAuthMethod
207          IBY_NETUTILS_PVT.check_optional('OapfOrderMedium-'||l_index,p_accxfr_trxn_tbl(l_curr_index).OrderMedium, l_post_body, l_db_nls, l_ecapp_nls);
208          IBY_NETUTILS_PVT.check_optional('OapfEftAuthMethod-'||l_index,p_accxfr_trxn_tbl(l_curr_index).EFTAuthMethod, l_post_body, l_db_nls, l_ecapp_nls);
209 
210          l_index := l_index + 1;
211          l_curr_index := p_accxfr_trxn_tbl.next(l_curr_index);
212          --dbms_output.put_line('IBY_BANKACCXFR_PUB : '||l_pos);
213          --dbms_output.put_line('String :'||l_post_body);
214          -- write to CLOB
215          DBMS_LOB.WriteAppend(l_tempclob, length(l_post_body), l_post_body);
216 
217 
218 
219      END LOOP;
220 
221 
222      ---------------------------------------------------
223      -- sending POST request and unpacking the results
224      ---------------------------------------------------
225 
226      --dbms_output.put_line('Length : '||DBMS_LOB.GETLENGTH(l_tempclob));
227      IBY_NETUTILS_PVT.POST_REQUEST(l_url,l_tempclob,l_names,l_values, l_status, l_errcode, l_errmessage);
228 
229      -- Release temporary blobs
230      DBMS_LOB.FreeTemporary(l_tempclob);
231 
232 
233      FOR i IN l_names.FIRST..l_names.last LOOP
234           --dbms_output.put_line(l_names(i)||' : '||l_values(i));
235 	    -- Read Batch response from ECServlet
236 	    IF l_names(i) = 'OapfBatchStatus' THEN
237 	       x_batch_resp_rec.BatchStatus := TO_NUMBER(l_values(i));
238 	    ELSIF l_names(i) = 'OapfBatchId' THEN
239 	       x_batch_resp_rec.Batch_Id := l_values(i);
240 	    ELSIF l_names(i) = 'OapfCode' THEN
241 	       x_batch_resp_rec.ErrorCode := l_values(i);
242 	    ELSIF l_names(i) = 'OapfMsg' THEN
243 	       x_batch_resp_rec.ErrorMsg := l_values(i);
244 
245 	    -- Read Transaction Response
246             ELSIF substr(l_names(i),0,instr(l_names(i),'-')-1) = 'OapfTangibleId' THEN
247 	       l_index := TO_NUMBER(substr(l_names(i),instr(l_names(i),'-')+1,length(l_names(i))));
248 	       x_batch_respdet_tbl(l_index).TangibleID := l_values(i);
249 	    ELSIF substr(l_names(i),0,instr(l_names(i),'-')-1) = 'OapfTrxnId' THEN
250 	       l_index := TO_NUMBER(substr(l_names(i),instr(l_names(i),'-')+1,length(l_names(i))));
251 	       x_batch_respdet_tbl(l_index).TrxnId := l_values(i);
252 	    ELSIF substr(l_names(i),0,instr(l_names(i),'-')-1) = 'OapfTrxnRef' THEN
253 	       l_index := TO_NUMBER(substr(l_names(i),instr(l_names(i),'-')+1,length(l_names(i))));
254 	       x_batch_respdet_tbl(l_index).TrxnRef := l_values(i);
255 	    ELSIF substr(l_names(i),0,instr(l_names(i),'-')-1) = 'OapfTrxnStatus' THEN
256 	       l_index := TO_NUMBER(substr(l_names(i),instr(l_names(i),'-')+1,length(l_names(i))));
257 	       x_batch_respdet_tbl(l_index).TrxnStatus := l_values(i);
258             ELSIF substr(l_names(i),0,instr(l_names(i),'-')-1) = 'OapfTrxnErrCode' THEN
259 	       l_index := TO_NUMBER(substr(l_names(i),instr(l_names(i),'-')+1,length(l_names(i))));
260 	       x_batch_respdet_tbl(l_index).ErrorCode := l_values(i);
261             ELSIF substr(l_names(i),0,instr(l_names(i),'-')-1) = 'OapfTrxnErrMsg' THEN
262 	       l_index := TO_NUMBER(substr(l_names(i),instr(l_names(i),'-')+1,length(l_names(i))));
263 	       x_batch_respdet_tbl(l_index).ErrorMsg := l_values(i);
264 	    END IF;
265 
266 
267      END LOOP;
268 
269      -----------------------------------------------------------------------
270      --Raising Exception to handle errors in unpacking resulting html file.
271      -----------------------------------------------------------------------
272      IF (l_status = -1) THEN
273 	iby_debug_pub.add('Unpack status error');
274         FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
275         FND_MSG_PUB.Add;
276         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277      END IF;
278 
279      --------------------------------------------------------
280      --Raising Exception to handle Servlet related errors.
281      --------------------------------------------------------
282      IF (l_names.COUNT = 0) THEN
283 	iby_debug_pub.add('Names count=0');
284         FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
285         FND_MSG_PUB.Add;
286         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287      END IF;
288 
289      -------------------------------------------------------------------------
290      -- Set return status to success and return
291      -------------------------------------------------------------------------
292         x_return_status := FND_API.G_RET_STS_SUCCESS ;
293 
294      iby_debug_pub.add('Exit OraPmtBankAccXfrBatchReq');
295 
296    EXCEPTION
297 
298       WHEN FND_API.G_EXC_ERROR THEN
299 
300 	     iby_debug_pub.add('In G_EXC_ERROR Exception');
301          x_return_status := FND_API.G_RET_STS_ERROR ;
302          FND_MSG_PUB.Count_And_Get ( p_count  =>   x_msg_count,
303                                      p_data   =>   x_msg_data
304                                    );
305       WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
306 
307    	     iby_debug_pub.add('In G_EXC_UNEXPECTED_ERROR Exception');
308          x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
309          FND_MSG_PUB.Count_And_Get ( p_count  =>   x_msg_count,
310                                      p_data   =>   x_msg_data
311                                    );
312       WHEN OTHERS THEN
313 
314 	     --dbms_output.put_line('In OTHERS Exception');
315          x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
316          IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
317             FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
318          END IF;
319 
320          FND_MSG_PUB.Count_And_Get ( p_count  =>  x_msg_count,
321                                      p_data   =>  x_msg_data
322                                    );
323 
324          iby_debug_pub.add('x_return_status=' || x_return_status);
325          iby_debug_pub.add('Exit Exception');
326 
327 END OraPmtBatchReq;
328 
329 END IBY_BANKACCXFR_PUB;
330