[Home] [Help]
PACKAGE BODY: APPS.PON_EMD_VALIDATION_PKG
Source
1 PACKAGE BODY PON_EMD_VALIDATION_PKG AS
2 /* $Header: PONEMDVB.pls 120.4.12020000.3 2013/03/06 11:06:15 sgulkota ship $ */
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| PONEMDVB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to validation EMD transactions |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE validate_credit_card_num |
16 --| |
17 --| HISTORY |
18 --| 01/15/2009 Allen Yang Created |
19 --| 04/01/2009 Lion Li Add new PROCEDURE getReceiptInfoOfTrx |
20 --+======================================================================*/
21 --==========================================================================
22 -- PROCEDURE NAME:
23 --
24 -- validate_credit_card_num Public
25 --
26 -- DESCRIPTION:
27 --
28 -- This procedure is to validate if the credit card number is valid
29 --
30 -- PARAMETERS:
31 -- In: p_api_version API Version
32 -- p_init_msg_list Whether to initialize message list
33 -- p_card_number Credit Card Number
34 --
35 -- Out: x_return_status Returned flag to show if CCNumber is valid
36 --
37 --
38 -- DESIGN REFERENCES:
39 -- EMD_TECHNICAL_DESIGN_ALLEN.doc
40 --
41 -- CHANGE HISTORY:
42 --
43 -- 15-Jan-2009 Allen Yang created
44 --
45
46 PROCEDURE validate_credit_card_num(p_api_version IN NUMBER,
47 p_init_msg_list IN VARCHAR2,
48 p_card_number IN VARCHAR2,
49 x_return_status OUT NOCOPY VARCHAR2) IS
50
51 l_api_version NUMBER := 1.0;
52 l_api_name VARCHAR2(50) := 'validate_credit_card_num';
53 l_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
54 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
55 l_card_number iby_creditcard.ccnumber%TYPE := NULL;
56
57 lx_return_status VARCHAR2(1) := NULL;
58 lx_msg_count NUMBER := NULL;
59 lx_msg_data VARCHAR2(200) := NULL;
60 lx_cc_number iby_creditcard.ccnumber%TYPE := NULL;
61 lx_card_issuer iby_creditcard.card_issuer_code%TYPE := NULL;
62 lx_issuer_range iby_creditcard.cc_issuer_range_id%TYPE := NULL;
63 lx_card_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE := NULL;
64 lx_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE := NULL;
65
66 BEGIN
67 --logging for debug
68 IF (l_proc_level >= l_dbg_level) THEN
69 FND_LOG.STRING(l_proc_level,
70 g_module_prefix || '.' || l_api_name || '.begin',
71 'Enter procedure');
72 END IF; --l_proc_level>=l_dbg_level
73
74 -- initializilation of variables
75 l_card_number := p_card_number;
76 x_return_status := FND_API.G_RET_STS_SUCCESS; -- 'S'
77
78 -- Standard call to check for call compatibility.
79 IF NOT FND_API.Compatible_API_Call(l_api_version,
80 p_api_version,
81 l_api_name,
82 l_api_name) THEN
83 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
84 END IF;
85
86 -- Initialize message list if p_init_msg_list is set to TRUE.
87 IF FND_API.to_Boolean(p_init_msg_list) THEN
88 FND_MSG_PUB.initialize();
89 END IF;
90
91 -- Start credit card number validation
92 IF (l_card_number IS NULL) THEN
93 x_return_status := FND_API.G_RET_STS_ERROR; -- 'E'
94 RETURN;
95 END IF;
96
97 -- using the same logic as iby package to validate the format of CCNumber
98 iby_cc_validate.StripCC(p_api_version,
99 FND_API.G_FALSE,
100 l_card_number,
101 lx_return_status,
102 lx_msg_count,
103 lx_msg_data,
104 lx_cc_number);
105
106 IF ((lx_cc_number IS NULL) OR
107 (lx_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
108 x_return_status := FND_API.G_RET_STS_ERROR; -- 'E'
109 RETURN;
110 END IF;
111
112 iby_cc_validate.Get_CC_Issuer_Range(lx_cc_number,
113 lx_card_issuer,
114 lx_issuer_range,
115 lx_card_prefix,
116 lx_digit_check);
117
118 IF (lx_digit_check = 'Y') THEN
119 IF (MOD(iby_cc_validate.CheckCCDigits(lx_cc_number), 10) <> 0) THEN
120 x_return_status := FND_API.G_RET_STS_ERROR; -- 'E'
121 RETURN;
122 END IF; -- MOD(iby_cc_validate.CheckCCDigits(lx_cc_number),10) <> 0
123 END IF; -- lx_digit_check = 'Y'
124
125 --logging for debug
126 IF (l_proc_level >= l_dbg_level) THEN
127 FND_LOG.STRING(l_proc_level,
128 g_module_prefix || '.' || l_api_name || '.end',
129 'Exit procedure');
130 END IF; -- (l_proc_level>=l_dbg_level)
131
132 EXCEPTION
133 WHEN OTHERS THEN
134 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
135 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
136 g_module_prefix || '.' || l_api_name ||
137 '.Other_Exception ',
138 Sqlcode || Sqlerrm);
139 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
140 END validate_credit_card_num;
141 --==========================================================================
142 -- PROCEDURE NAME:
143 --
144 -- getReceiptInfoOfTrx Public
145 --
146 -- DESCRIPTION:
147 --
148 -- This procedure is to to get the receipt information of transaction
149 --
150 -- PARAMETERS:
151 -- In: p_trx_id Transaction id
152 -- p_trx_number Transaction number
153 -- p_org_id Org id
154 --
155 -- Out: x_return_status Returned flag to show if has receipt information of this transaction
156 -- x_receipt_num Returned receipt number of this transacton
157 -- x_cash_receipt_id Returned cash receipt id of this transacton
158 -- x_receivable_app_id Returned receivable app id of this transaction
159 -- x_receipt_status Returned receipt status of this transaction
160 -- DESIGN REFERENCES:
161 -- EMD_TECHNICAL_DESIGN_ALLEN.doc
162 --
163 -- CHANGE HISTORY:
164 --
165 -- 01-Apr-2009 Lion Li created
166
167 PROCEDURE getReceiptInfoOfTrx(p_trx_id IN NUMBER,
168 p_trx_number IN VARCHAR2,
169 p_org_id IN NUMBER,
170 x_receipt_num OUT NOCOPY VARCHAR2,
171 x_cash_receipt_id OUT NOCOPY NUMBER,
172 x_receivable_app_id OUT NOCOPY NUMBER,
173 x_receipt_status OUT NOCOPY VARCHAR2,
174 x_receipt_status_code OUT NOCOPY VARCHAR2,
175 x_return_status OUT NOCOPY VARCHAR2) IS
176 l_api_name VARCHAR2(50) := 'getReceiptInfoOfTrx';
177 l_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
178 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
179
180 BEGIN
181 --logging for debug
182 IF (l_proc_level >= l_dbg_level) THEN
183 FND_LOG.STRING(l_proc_level,
184 g_module_prefix || '.' || l_api_name || '.begin',
185 'Enter procedure');
186 END IF; -- (l_proc_level>=l_dbg_level)
187
188 x_return_status := FND_API.G_RET_STS_SUCCESS;
189
190 BEGIN
191 SELECT Max(acr.cash_receipt_id), Max(arp.receivable_application_id)
192 INTO x_cash_receipt_id, x_receivable_app_id
193 from ar_receivable_applications_all arp,
194 ra_customer_trx_all rct,
195 ra_cust_trx_types_all ctyp,
196 ar_cash_receipts_all acr
197 where arp.applied_customer_trx_id = p_trx_id
198 AND arp.org_id = p_org_id
199 AND arp.status = 'APP'
200 AND arp.applied_customer_trx_id = rct.customer_trx_id
201 AND arp.org_id = rct.org_id
202 AND rct.CUST_TRX_TYPE_ID = ctyp.CUST_TRX_TYPE_ID
203 AND rct.org_id = ctyp.org_id
204 AND ctyp.type = 'DEP'
205 AND arp.cash_receipt_id = acr.cash_receipt_id
206 AND arp.amount_applied > 0;
207 EXCEPTION
208 WHEN NO_DATA_FOUND THEN
209
210 x_return_status := FND_API.G_RET_STS_ERROR;
211 FND_MESSAGE.SET_NAME('PON', 'RECEIPT_NOT_CRE_FOR_DEPOSIT');
212 FND_MESSAGE.SET_TOKEN('DEPOSIT_TRX_NUM', p_trx_number);
213 FND_MSG_PUB.ADD;
214 x_receipt_num := NULL;
215 x_cash_receipt_id := NULL;
216 x_receipt_status := NULL;
217 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
218 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
219 g_module_prefix || '.' || l_api_name ||
220 '.NO_DATA_FOUND_Exception ',
221 Sqlcode || Sqlerrm);
222 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
223 RETURN;
224 WHEN TOO_MANY_ROWS THEN
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
227 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
228 g_module_prefix || '.' || l_api_name ||
229 '.TOO_MANY_ROWS_Exception ',
230 Sqlcode || Sqlerrm);
231 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
232 RETURN;
233 END;
234 IF (x_cash_receipt_id IS NOT NULL) THEN
235 BEGIN
236 SELECT acr.receipt_number, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('RECEIPT_CREATION_STATUS',
237 acrh.STATUS),acrh.STATUS
238 INTO x_receipt_num, x_receipt_status,x_receipt_status_code
239 FROM ar_cash_Receipt_history_all acrh, ar_cash_receipts_all acr
240 WHERE acrh.cash_receipt_id = acr.cash_receipt_id
241 AND acrh.current_record_flag = 'Y'
242 AND acr.cash_receipt_id = x_cash_receipt_id;
243 EXCEPTION
244 WHEN NO_DATA_FOUND THEN
245 x_return_status := FND_API.G_RET_STS_ERROR;
246 FND_MESSAGE.SET_NAME('PON', 'RECEIPT_NOT_CLEARED');
247 FND_MESSAGE.SET_TOKEN('DEPOSIT_TRX_NUM', p_trx_number);
248 FND_MESSAGE.SET_TOKEN('RECEIPT_NUM', x_receipt_num);
249 FND_MSG_PUB.ADD;
250 x_receipt_num := NULL;
251 x_cash_receipt_id := NULL;
252 x_receipt_status := NULL;
253 x_receipt_status_code := NULL;
254 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
255 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
256 g_module_prefix || '.' || l_api_name ||
257 '.NO_DATA_FOUND_Exception ',
258 Sqlcode || Sqlerrm);
259 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
260 RETURN;
261 WHEN TOO_MANY_ROWS THEN
262 x_return_status := FND_API.G_RET_STS_ERROR;
263 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
264 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
265 g_module_prefix || '.' || l_api_name ||
266 '.TOO_MANY_ROWS_Exception ',
267 Sqlcode || Sqlerrm);
268 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
269 RETURN;
270 END;
271 END IF;
272 --logging for debug
273 IF (l_proc_level >= l_dbg_level) THEN
274 FND_LOG.STRING(l_proc_level,
275 g_module_prefix || '.' || l_api_name || '.end',
276 'Exit procedure');
277 END IF; -- (l_proc_level>=l_dbg_level)
278 EXCEPTION
279 WHEN NO_DATA_FOUND THEN
280 x_return_status := FND_API.G_RET_STS_ERROR;
281 x_receipt_num := NULL;
282 x_cash_receipt_id := NULL;
283 x_receipt_status := NULL;
284 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
285 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
286 g_module_prefix || '.' || l_api_name ||
287 '.NO_DATA_FOUND_Exception ',
288 Sqlcode || Sqlerrm);
289 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
290 WHEN TOO_MANY_ROWS THEN
291 x_return_status := FND_API.G_RET_STS_ERROR;
292 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
293 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
294 g_module_prefix || '.' || l_api_name ||
295 '.TOO_MANY_ROWS_Exception ',
296 Sqlcode || Sqlerrm);
297 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
298 END getReceiptInfoOfTrx;
299
300 PROCEDURE ADD_EMD_BIDDING_PARTY
301 (
302 L_AUCTION_HEADER_ID IN NUMBER,
303 L_LIST_ID IN NUMBER,
304 L_SEQUENCE IN NUMBER,
305 L_TRADING_PARTNER_ID IN NUMBER,
306 L_TRADING_PARTNER_NAME IN VARCHAR2,
307 L_VENDOR_SITE_ID IN NUMBER
308 ) IS
309 L_USER_ID NUMBER;
310 BEGIN
311
312
313 L_USER_ID:=FND_GLOBAL.USER_ID;
314
315 INSERT INTO PON_BIDDING_PARTIES
316 (
317 AUCTION_HEADER_ID,
318 LIST_ID,
319 SEQUENCE,
320 TRADING_PARTNER_NAME,
321 TRADING_PARTNER_ID,
322 VENDOR_SITE_ID,
323 VENDOR_SITE_CODE,
324 CREATION_DATE,
325 CREATED_BY,
326 LAST_UPDATE_DATE,
327 LAST_UPDATED_BY,
328 ACCESS_TYPE,
329 FROM_EMD_FLAG
330 )
331 VALUES
332 (
333 L_AUCTION_HEADER_ID,
334 L_LIST_ID ,
335 L_SEQUENCE ,
336 L_TRADING_PARTNER_NAME,
337 L_TRADING_PARTNER_ID,
338 -1,
339 '-1',
340 SYSDATE,
341 L_USER_ID,
342 SYSDATE,
343 L_USER_ID,
344 'FULL',
345 'Y'
346 );
347 END;
348
349 FUNCTION get_user_name(P_USER_ID IN NUMBER ) RETURN VARCHAR2
350 IS
351
352 L_PERSON_FIRST_NAME HZ_PARTIES.PERSON_FIRST_NAME%TYPE;
353 L_PERSON_MIDDLE_NAME HZ_PARTIES.PERSON_MIDDLE_NAME%TYPE;
354 L_PERSON_LAST_NAME HZ_PARTIES.PERSON_LAST_NAME%TYPE;
355 L_PERSON_PRE_NAME_ADJUNCT HZ_PARTIES.PERSON_PRE_NAME_ADJUNCT%TYPE;
356 L_PERSON_NAME_SUFFIX HZ_PARTIES.PERSON_NAME_SUFFIX%TYPE;
357 L_PARTY_ID NUMBER;
358
359 X_NAME VARCHAR2(1000);
360 BEGIN
361
362 SELECT PERSON_PARTY_ID INTI INTO L_PARTY_ID FROM FND_USER WHERE USER_ID=P_USER_ID;
363
364 SELECT
365 PERSON_FIRST_NAME,
366 PERSON_MIDDLE_NAME,
367 PERSON_LAST_NAME,
368 PERSON_PRE_NAME_ADJUNCT,
369 PERSON_NAME_SUFFIX
370 INTO
371 L_PERSON_FIRST_NAME,
372 L_PERSON_MIDDLE_NAME,
373 L_PERSON_LAST_NAME,
374 L_PERSON_PRE_NAME_ADJUNCT,
375 L_PERSON_NAME_SUFFIX
376 FROM
377 HZ_PARTIES
378 WHERE
379 PARTY_ID=L_PARTY_ID;
380
381 X_NAME:=PON_LOCALE_PKG.party_display_name (
382 p_first_name => L_PERSON_FIRST_NAME
383 , p_last_name => L_PERSON_LAST_NAME
384 , p_middle_name => L_PERSON_MIDDLE_NAME
385 , p_prefix => L_PERSON_PRE_NAME_ADJUNCT
386 , p_suffix => L_PERSON_NAME_SUFFIX
387 , p_language => UserEnv('LANG')
388 , p_party_id => L_PARTY_ID);
389
390 RETURN X_NAME;
391
392 END;
393
394
395
396 END PON_EMD_VALIDATION_PKG;