[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_UTIL_PKG
Source
1 PACKAGE BODY AP_WEB_UTIL_PKG as
2 /* $Header: apwutilb.pls 120.5 2011/12/07 10:57:11 meesubra ship $ */
3
4 --
5 -- Encryption routine
6 --
7 function encrypt(msg in varchar2) return varchar2
8 is
9 r varchar2(5);
10 begin
11 r := to_char(dbms_random.value(0, 99999), 'FM00000');
12 return icx_call.encrypt(r || msg);
13 end encrypt;
14
15 --
16 -- Decryption routine
17 --
18 function decrypt(msg in varchar2) return varchar2
19 is
20 begin
21 return substr(icx_call.decrypt(msg), 6);
22 end decrypt;
23
24 --
25 -- Gets the proxy server
26 --
27 PROCEDURE GET_PROXY(p_host_name IN VARCHAR2, p_proxy_host OUT nocopy VARCHAR2, p_proxy_port OUT nocopy NUMBER)
28 IS
29 l_proxy VARCHAR2(255);
30 l_proxy_port VARCHAR2(255);
31 BEGIN
32 -- First, attempt to get proxy value from FND. If the proxy name is not
33 -- found, try the TCA values regardless of whether the port is found.
34 fnd_profile.get('WEB_PROXY_HOST', l_proxy);
35 fnd_profile.get('WEB_PROXY_PORT', l_proxy_port);
36
37 IF l_proxy IS NULL AND l_proxy_port IS NULL THEN
38 fnd_profile.get('HZ_WEBPROXY_NAME', l_proxy);
39 fnd_profile.get('HZ_WEBPROXY_PORT', l_proxy_port);
40 END IF;
41
42 p_proxy_host := l_proxy;
43 p_proxy_port := to_number(l_proxy_port);
44 END GET_PROXY;
45
46
47 /*
48 * Updates the download columns in AP_CARD_PROGRAMS_ALL
49 */
50 PROCEDURE UPDATE_DOWNLOAD_SIZES(p_card_program_id in NUMBER, p_file_size in NUMBER)
51 IS
52 l_curr_average_size number;
53 l_curr_download_count number;
54 BEGIN
55 BEGIN
56 SELECT nvl(average_download_size, 0), nvl(download_count, 0)
57 INTO l_curr_average_size, l_curr_download_count
58 FROM ap_card_programs_all
59 WHERE card_program_id = p_card_program_id;
60 EXCEPTION
61 WHEN No_Data_Found THEN
62 RETURN;
63 END;
64 UPDATE ap_card_programs_all
65 SET last_download_date = SYSDATE,
66 last_download_size = p_file_size,
67 average_download_size = (l_curr_average_size * l_curr_download_count + p_file_size) / (l_curr_download_count + 1),
68 download_count = l_curr_download_count + 1
69 WHERE card_program_id = p_card_program_id;
70 END UPDATE_DOWNLOAD_SIZES;
71
72 ------------------------------------------------------------------------
73 -- PROCEDURE NotifyEmployeeOnPayment
74 --
75 -- This API notifies the employee, when payments are made to employee
76 -- or credit card company in behalf of the employee, based on the
77 -- sse_cc_payment_notify profile option. This replaces the AFTER INSERT
78 -- trigger AP_SSE_NOTIFY_EMPLOYEE on table AP_INVOICE_PAYMENTS_ALL.
79 -- Introduced as part of the bugfix 9557247
80 ------------------------------------------------------------------------
81 PROCEDURE NotifyEmployeeOnPayment (P_Event_Type IN VARCHAR2,
82 P_Check_ID IN NUMBER,
83 P_Return_Status OUT NOCOPY VARCHAR2,
84 P_Msg_Count OUT NOCOPY NUMBER,
85 P_Msg_Data OUT NOCOPY VARCHAR2) IS
86
87 l_debug_info VARCHAR2(1000);
88
89 l_sse_cc_payment_notify VARCHAR2(1);
90
91 l_checkNumber AP_CHECKS_ALL.CHECK_NUMBER%TYPE;
92 l_paymentCurrency AP_CHECKS_ALL.CURRENCY_CODE%TYPE;
93 l_paymentDate AP_CHECKS_ALL.CHECK_DATE%TYPE;
94 l_paymentMethod AP_CHECKS_ALL.PAYMENT_METHOD_LOOKUP_CODE%TYPE := NULL;
95 l_paymentProfileId AP_CHECKS_ALL.PAYMENT_PROFILE_ID%TYPE := NULL;
96 l_ext_bank_account_id AP_CHECKS_ALL.EXTERNAL_BANK_ACCOUNT_ID%TYPE; -- bug 2426077
97 l_processing_type IBY_PAYMENT_PROFILES.PROCESSING_TYPE%TYPE;
98
99 l_invoice_type_lookup_code AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE%TYPE;
100 l_employeeID PO_VENDORS.EMPLOYEE_ID%TYPE := NULL;
101 l_paid_on_behalf_employee_id AP_INVOICES_ALL.PAID_ON_BEHALF_EMPLOYEE_ID%TYPE := NULL;
102 l_invoiceNumber AP_INVOICES_ALL.INVOICE_NUM%TYPE;
103 l_account IBY_EXT_BANK_ACCOUNTS_V.BANK_ACCOUNT_NUMBER%TYPE := NULL; --1684954
104 l_bankName IBY_EXT_BANK_ACCOUNTS_V.BANK_NAME%TYPE := NULL; --1758619
105 l_cardIssuer PO_VENDORS.VENDOR_NAME%TYPE;
106 l_paidAmount AP_INVOICES_ALL.PAY_CURR_INVOICE_AMOUNT%TYPE;
107
108 l_flag NUMBER := 0;
109 l_source AP_INVOICES_ALL.SOURCE%TYPE;
110
111 l_invoiceId AP_INVOICE_PAYMENTS_ALL.INVOICE_ID%TYPE;
112 l_reversalFlag AP_INVOICE_PAYMENTS_ALL.REVERSAL_FLAG%TYPE;
113 l_count NUMBER := 0;
114 l_employee_id PO_VENDORS.EMPLOYEE_ID%TYPE := NULL;
115 l_notify VARCHAR2(1);
116
117 -- getting invoice by check id
118 CURSOR get_invoice_cur(P_CHECK_ID number) IS
119 select invoice_id,amount,reversal_flag
120 from AP_INVOICE_PAYMENTS_ALL
121 where check_id = P_CHECK_ID;
122
123 BEGIN
124
125 LogStatement('AP_WEB_UTILITIES_PKG', 'NotifyEmployeeOnPayment');
126
127 -- Standard start of API savepoint
128 SAVEPOINT NotifyEmployeeOnPayment;
129 LogStatement('NotifyEmployeeOnPayment', 'Savepoint is established');
130
131 -- Initialize API return status to success
132 P_Return_Status := FND_API.G_RET_STS_SUCCESS;
133
134 -- START OF BODY OF API
135 LogStatement('NotifyEmployeeOnPayment', 'Processing to send Employee Notifications...');
136 LogStatement('NotifyEmployeeOnPayment', 'Input Data: ');
137 LogStatement('NotifyEmployeeOnPayment', 'P_Event_Type: ' || P_Event_Type);
138 LogStatement('NotifyEmployeeOnPayment', 'P_Check_ID: ' || P_Check_ID);
139
140 LogStatement('NotifyEmployeeOnPayment', 'Getting check informations');
141 SELECT check_number,
142 currency_code,
143 check_date,
144 payment_profile_id,
145 external_bank_account_id -- bug 2426077
146 INTO l_checkNumber,
147 l_paymentCurrency,
148 l_paymentDate,
149 l_paymentProfileId,
150 l_ext_bank_account_id -- bug 2426077
151 FROM AP_CHECKS_ALL
152 WHERE check_id = P_Check_ID;
153
154 LogStatement('NotifyEmployeeOnPayment', 'Getting Payment Processing Type for Profile ' || l_paymentProfileId);
155 if (l_paymentProfileId is not null) then -- Bug 7118709(sodash)
156 SELECT processing_type
157 INTO l_processing_type
158 FROM IBY_PAYMENT_PROFILES
159 WHERE payment_profile_id = l_paymentProfileId
160 AND rownum = 1;
161 end if;
162 LogStatement('NotifyEmployeeOnPayment', 'Payment Processing Type ' || l_processing_type);
163
164 LogStatement('NotifyEmployeeOnPayment', 'Getting Bank Information for the Invoice and Vendor ' || l_ext_bank_account_id);
165 if (l_ext_bank_account_id is not null) then
166 SELECT bank_account_number,
167 bank_name||','||bank_branch_name
168 INTO l_account,
169 l_bankName
170 FROM IBY_EXT_BANK_ACCOUNTS_V
171 WHERE ext_bank_account_id = l_ext_bank_account_id
172 AND rownum = 1;
173 end if;
174 LogStatement('NotifyEmployeeOnPayment', 'Bank Information ' || l_account || ' ' || l_bankName );
175
176 LogStatement('NotifyEmployeeOnPayment', 'Setting Payment Method ');
177 -- The value for l_paymentMethod is set to a constant recognized by the
178 -- ap_web_credit_card_wf.sendPaymentNotification procedure
179 if ((l_processing_type = 'ELECTRONIC')) then -- AND (l_ext_bank_account_id IS NOT NULL)) then
180 l_paymentMethod := 'DIRECT_DEPOSIT';
181 else
182 l_paymentMethod := 'CHECK';
183 end if;
184 LogStatement('NotifyEmployeeOnPayment', 'Payment Method ' || l_paymentMethod);
185
186 -- getting invoice by check id
187 l_count := 0;
188 open get_invoice_cur(P_Check_ID);
189 LOOP
190
191 fetch get_invoice_cur into l_invoiceId,l_paidAmount,l_reversalFlag;
192 exit when get_invoice_cur%NOTFOUND;
193
194 l_count := l_count + 1;
195 LogStatement('NotifyEmployeeOnPayment', 'Processing invoice # ' || l_count || ' ' || l_invoiceId);
196
197 BEGIN
198 SELECT 1, source
199 INTO l_flag, l_source
200 FROM ap_invoices_all
201 WHERE
202 invoice_id = l_invoiceId
203 AND SOURCE IN ( 'SelfService', 'Both Pay', 'CREDIT CARD','XpenseXpress');
204 EXCEPTION
205 WHEN NO_DATA_FOUND THEN
206 l_flag := 0;
207 END;
208
209 LogStatement('NotifyEmployeeOnPayment', 'Processing flag ' || l_flag);
210
211 if ( l_flag = 1 ) THEN
212 -- IF(l_source <> 'XpenseXpress') THEN (sodash) Changed for Bug 9025829: EXPENSE_STATUS_CODE OF EXPENSE REPORTS NOT UPDATED
213 AP_WEB_UTILITIES_PKG.UpdateExpenseStatusCode( p_invoice_id => l_invoiceId);
214 -- END IF;
215
216 -- The notification should be sent only if the profile option for
217 -- credit card payment notification is set to 'Y'.
218 fnd_profile.get('SSE_CC_PAYMENT_NOTIFY', l_sse_cc_payment_notify);
219 LogStatement('NotifyEmployeeOnPayment', 'SSE_CC_PAYMENT_NOTIFY ' || l_sse_cc_payment_notify);
220
221 SELECT nvl(PV.employee_id,AIA.paid_on_behalf_employee_id) INTO l_employee_id FROM PO_VENDORS PV, AP_INVOICES_ALL AIA
222 WHERE PV.vendor_id = AIA.vendor_id AND AIA.invoice_id = l_invoiceId;
223
224 LogStatement('NotifyEmployeeOnPayment', 'Fetching profile from resp for ' || l_employee_id);
225
226 IF (l_employee_id IS NOT NULL) THEN
227 BEGIN
228 select PROFILE_OPTION_VALUE INTO l_notify FROM FND_PROFILE_OPTION_VALUES FPOV, FND_PROFILE_OPTIONS FPO,
229 FND_USER FU, FND_USER_RESP_GROUPS FURG WHERE
230 fu.employee_id = l_employee_id
231 and fu.user_id = furg.user_id
232 and fpo.profile_option_name = 'SSE_CC_PAYMENT_NOTIFY'
233 and fpov.profile_option_id = fpo.profile_option_id
234 and furg.responsibility_id = fpov.level_value
235 and fpov.level_value_application_id = 200
236 and fpov.application_id = 200
237 and sysdate between furg.start_date and nvl(furg.end_date, sysdate)
238 and rownum = 1
239 order by furg.responsibility_id;
240 l_sse_cc_payment_notify := l_notify;
241 LogStatement('NotifyEmployeeOnPayment', 'Profile Value from resp ' || l_sse_cc_payment_notify);
242 EXCEPTION
243 WHEN OTHERS THEN
244 l_notify := 'N';
245 LogStatement('NotifyEmployeeOnPayment', 'Exception when trying to fetch profile ' || l_notify);
246 END;
247 END IF;
248 LogStatement('NotifyEmployeeOnPayment', 'Final Profile Value ' || l_sse_cc_payment_notify);
249
250 if (nvl(l_sse_cc_payment_notify,'N') = 'Y') then
251
252 LogStatement('NotifyEmployeeOnPayment', 'Get invoice and vendor info for invoice ' || l_invoiceId);
253 SELECT
254 AIA.invoice_type_lookup_code,
255 PV.employee_id,
256 AIA.paid_on_behalf_employee_id,
257 AIA.invoice_num,
258 PV.vendor_name
259 INTO
260 l_invoice_type_lookup_code,
261 l_employeeID,
262 l_paid_on_behalf_employee_id,
263 l_invoiceNumber,
264 l_cardIssuer
265 FROM
266 PO_VENDORS PV,
267 AP_INVOICES_ALL AIA
268 WHERE
269 PV.vendor_id = AIA.vendor_id
270 AND AIA.invoice_id = l_invoiceId;
271
272 LogStatement('NotifyEmployeeOnPayment', 'Paid on behalf of Employee Id ' || l_paid_on_behalf_employee_id);
273 LogStatement('NotifyEmployeeOnPayment', 'Invoice Type Lookup Code ' || l_invoice_type_lookup_code);
274
275 if (nvl(l_paid_on_behalf_employee_id, -1) <> -1
276 or (l_invoice_type_lookup_code = 'EXPENSE REPORT')) then
277
278 LogStatement('NotifyEmployeeOnPayment', 'Start Processing Notifications ');
279
280 if (nvl(l_paid_on_behalf_employee_id, -1) <> -1) then
281
282 LogStatement('NotifyEmployeeOnPayment', 'Notify Employee on Payment to credit card company on behalf of the employee ');
283 LogStatement('NotifyEmployeeOnPayment', 'Calling SSE workflow API...(paid_on_behalf_employee_id not null)');
284
285 ap_web_credit_card_wf.sendPaymentNotification(
286 p_checkNumber => l_checkNumber,
287 p_employeeId => l_paid_on_behalf_employee_id,
288 p_paymentCurrency => l_paymentCurrency,
289 p_invoiceNumber => l_invoiceNumber,
290 p_paidAmount => l_paidAmount,
291 p_paymentTo => ap_web_credit_card_wf.c_paymentToCardIssuer,
292 p_paymentMethod => NULL,
293 p_account => NULL,
294 p_bankName => NULL,
295 p_cardIssuer => l_cardIssuer,
296 p_paymentDate => fnd_date.date_to_canonical(l_paymentDate));
297
298 elsif (l_invoice_type_lookup_code = 'EXPENSE REPORT') then
299
300 LogStatement('NotifyEmployeeOnPayment', 'Notify Employee on all expense payments made to the employee ');
301
302 if(l_reversalFlag = 'Y') then
303 LogStatement('NotifyEmployeeOnPayment', 'Notification for Reversal Flag = Y');
304 ap_web_credit_card_wf.sendPaymentNotification(
305 p_checkNumber => l_checkNumber,
306 p_employeeId => l_employeeId,
307 p_paymentCurrency => l_paymentCurrency,
308 p_invoiceNumber => l_invoiceNumber,
309 p_paidAmount => l_paidAmount,
310 p_paymentTo => ap_web_credit_card_wf.c_voidPayment,
311 p_paymentMethod => l_paymentMethod,
312 p_account => l_account,
313 p_bankName => l_bankName,
314 p_cardIssuer => NULL,
315 p_paymentDate => fnd_date.date_to_canonical(l_paymentDate) );
316 else
317 LogStatement('NotifyEmployeeOnPayment', 'Notification for Reversal Flag <> Y');
318 ap_web_credit_card_wf.sendPaymentNotification(
319 p_checkNumber => l_checkNumber,
320 p_employeeId => l_employeeId,
321 p_paymentCurrency => l_paymentCurrency,
322 p_invoiceNumber => l_invoiceNumber,
323 p_paidAmount => l_paidAmount,
324 p_paymentTo => ap_web_credit_card_wf.c_paymentToEmployee,
325 p_paymentMethod => l_paymentMethod,
326 p_account => l_account,
327 p_bankName => l_bankName,
328 p_cardIssuer => NULL,
329 p_paymentDate => fnd_date.date_to_canonical(l_paymentDate) );
330 end if;
331 end if;
332 end if;
333 end if;
334 end if;
335 END LOOP;
336 close get_invoice_cur;
337
338 EXCEPTION
339 WHEN FND_API.G_EXC_ERROR THEN
340 ROLLBACK TO NotifyEmployeeOnPayment;
341 p_return_status := FND_API.G_RET_STS_ERROR;
342 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count, p_data => p_msg_data);
343 LogStatement('FND_LOG.LEVEL_UNEXPECTED', 'Rollbacked due FND_API.G_EXC_ERROR' );
344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
345 ROLLBACK TO NotifyEmployeeOnPayment;
346 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count, p_data => p_msg_data);
348 LogStatement('FND_LOG.LEVEL_UNEXPECTED', 'Rollbacked due FND_API.G_EXC_UNEXPECTED_ERROR');
349 WHEN OTHERS THEN
350 ROLLBACK TO NotifyEmployeeOnPayment;
351 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
352 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count, p_data => p_msg_data);
353 LogStatement('FND_LOG.LEVEL_UNEXPECTED', 'Rollbacked due others Exception');
354
355 END NotifyEmployeeOnPayment;
356
357 PROCEDURE LogStatement(p_pkgname IN VARCHAR2,
358 p_message IN VARCHAR2) IS
359 BEGIN
360 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
361 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_pkgname, p_message);
362 end if;
363 END LogStatement;
364
365 end AP_WEB_UTIL_PKG;