[Home] [Help]
PACKAGE BODY: APPS.AP_PAYMENT_EVENT_WF_PKG
Source
1 PACKAGE BODY AP_PAYMENT_EVENT_WF_PKG as
2 /* $Header: appewfpb.pls 120.6 2011/03/03 07:06:55 sbonala ship $ */
3
4 PROCEDURE get_check_info (p_item_type IN VARCHAR2,
5 p_item_key IN VARCHAR2,
6 p_actid IN NUMBER,
7 p_funmode IN VARCHAR2,
8 p_result OUT NOCOPY VARCHAR2) IS
9
10 l_check_id ap_checks_all.check_id%type;
11 l_org_id ap_checks_all.org_id%type;
15 l_payment_amount ap_checks_all.amount%type;
12 l_check_number ap_checks_all.check_number%type;
13 l_check_date varchar2(20);
14 l_currency_code ap_checks_all.currency_code%type;
16 l_email_address IBY_EXTERNAL_PAYEES_ALL.remit_advice_email%type;
17
18 l_message_lines1 varchar2(4000):='';
19 l_message_lines2 varchar2(4000):='';
20 l_message_lines3 varchar2(4000):='';
21 l_message_lines4 varchar2(4000):='';
22 l_message_lines5 varchar2(4000):='';
23 l_message_lines6 varchar2(4000):='';
24 l_message_lines7 varchar2(4000):='';
25 l_message_lines8 varchar2(4000):='';
26 l_message_lines9 varchar2(4000):='';
27 l_message_lines10 varchar2(4000):='';
28 l_message_line varchar2(200):='';
29
30 l_role varchar2(100);
31 l_display_role_name varchar2(100);
32
33 --Bug#11774150
34 l_calendar_aware_alt NUMBER :=2;
35 l_calendar_aware NUMBER := 1;
36 l_calendar_unaware NUMBER := 0;
37
38 --Introduced calendar_aware parameter in fnd_date.date_to_chardate
39 --procedure for bug#11774150
40 CURSOR c_message_lines IS
41 SELECT '<TR><TD>'||invoice_num||
42 '</TD><TD>'||fnd_date.date_to_chardate(dateval=>ai.invoice_date,
43 calendar_aware=>l_calendar_unaware)||
44 '</TD><TD ALIGN="RIGHT">'||to_char(aip.discount_taken)||
45 '</TD><TD ALIGN="RIGHT">'||to_char(aip.amount)||'</TD></TR>'
46 FROM ap_invoice_payments aip, ap_invoices ai
47 WHERE aip.check_id = l_check_id
48 AND ai.invoice_id = aip.invoice_id
49 ORDER BY invoice_num;
50
51 BEGIN
52
53 /* Get the check_id stored in the attribute check_id */
54
55 l_check_id := wf_engine.getitemattrnumber(p_item_type,
56 p_item_key,
57 'CHECK_ID');
58
59 /* Get the org_id stored in the attribute check_id */
60
61 l_org_id := wf_engine.getitemattrnumber(p_item_type,
62 p_item_key,
63 'ORG_ID');
64
65 /* Set the Org ID context */
66
67 if l_org_id is not null
68 then
69
70 fnd_client_info.set_org_context(l_org_id);
71
72 end if;
73
74 /* Get Basic Check Info */
75
76 --Introduced calendar_aware parameter in fnd_date.date_to_chardate
77 --procedure for bug#11774150
78 SELECT check_number,
79 fnd_date.date_to_chardate(dateval=>check_date,calendar_aware=>l_calendar_unaware),
80 currency_code,
81 amount
82 INTO l_check_number,
83 l_check_date,
84 l_currency_code,
85 l_payment_amount
86 FROM ap_checks
87 WHERE check_id = l_check_id;
88
89 OPEN c_message_lines;
90
91 LOOP
92
93 FETCH c_message_lines INTO l_message_line;
94 EXIT WHEN c_message_lines%NOTFOUND;
95
96 IF length(l_message_lines1||l_message_line) <= 4000 THEN
97 l_message_lines1 := l_message_lines1 || l_message_line;
98 ELSIF length(l_message_lines2||l_message_line) <= 4000 THEN
99 l_message_lines2 := l_message_lines2 || l_message_line;
100 ELSIF length(l_message_lines3||l_message_line) <= 4000 THEN
101 l_message_lines3 := l_message_lines3 || l_message_line;
102 ELSIF length(l_message_lines4||l_message_line) <= 4000 THEN
103 l_message_lines4 := l_message_lines4 || l_message_line;
104 ELSIF length(l_message_lines5||l_message_line) <= 4000 THEN
105 l_message_lines5 := l_message_lines5 || l_message_line;
106 ELSIF length(l_message_lines6||l_message_line) <= 4000 THEN
107 l_message_lines6 := l_message_lines6 || l_message_line;
108 ELSIF length(l_message_lines7||l_message_line) <= 4000 THEN
109 l_message_lines7 := l_message_lines7 || l_message_line;
110 ELSIF length(l_message_lines8||l_message_line) <= 4000 THEN
111 l_message_lines8 := l_message_lines8 || l_message_line;
112 ELSIF length(l_message_lines9||l_message_line) <= 4000 THEN
113 l_message_lines9 := l_message_lines9 || l_message_line;
114 ELSIF length(l_message_lines10||l_message_line) <= 4000 THEN
115 l_message_lines10 := l_message_lines10 || l_message_line;
116 END IF;
117
118 END LOOP;
119
120 CLOSE c_message_lines;
121
122 /* Get Supplier's Remittance Email Address */
123 get_remit_email_address(l_check_id,l_email_address);
124
125 l_role := null;
126 l_display_role_name := null;
127
128 -- BUG 4281586 changed from MAILHTML to MAILHTM2
129 WF_DIRECTORY.createAdhocRole(role_name => l_role,
130 role_display_name => l_display_role_name,
131 email_address => l_email_address,
132 notification_preference => 'MAILHTM2');
133
134
135 /* Set Check Number to WorkFlow Attribute */
136 wf_engine.setitemattrnumber(P_item_type,
137 p_item_key,
138 'CHECK_NUMBER',
139 l_check_number);
140
141 /* Set Check Date to the WorkFlow Attribute */
142 wf_engine.setitemattrtext(p_item_type,
143 p_item_key,
144 'CHECK_DATE',
145 l_check_date);
146
150 'PAYMENT_CURRENCY',
147 /* Set Payment Currency Code to Workflow Attribute */
148 wf_engine.setitemattrtext(p_item_type,
149 p_item_key,
151 l_currency_code);
152
153 /* Set Check Amount to the Workflow Attribute */
154 wf_engine.setitemattrnumber(p_item_type,
155 p_item_key,
156 'CHECK_AMOUNT',
157 l_payment_amount);
158
159 /* Set Email Address to Workflow Adhoc Role */
160 wf_engine.setitemattrtext(p_item_type,
161 p_item_key,
162 'EMAIL_ADDRESS',
163 l_role);
164
165 /* Set Invoices List Information to the Invoices List Attribute */
166 wf_engine.setitemattrtext(p_item_type,
167 p_item_key,
168 'INVOICES_LIST1',
169 l_message_lines1);
170 wf_engine.setitemattrtext(p_item_type,
171 p_item_key,
172 'INVOICES_LIST2',
173 l_message_lines2);
174 wf_engine.setitemattrtext(p_item_type,
175 p_item_key,
176 'INVOICES_LIST3',
177 l_message_lines3);
178 wf_engine.setitemattrtext(p_item_type,
179 p_item_key,
180 'INVOICES_LIST4',
181 l_message_lines4);
182 wf_engine.setitemattrtext(p_item_type,
183 p_item_key,
184 'INVOICES_LIST5',
185 l_message_lines5);
186 wf_engine.setitemattrtext(p_item_type,
187 p_item_key,
188 'INVOICES_LIST6',
189 l_message_lines6);
190 wf_engine.setitemattrtext(p_item_type,
191 p_item_key,
192 'INVOICES_LIST7',
193 l_message_lines7);
194 wf_engine.setitemattrtext(p_item_type,
195 p_item_key,
196 'INVOICES_LIST8',
197 l_message_lines8);
198 wf_engine.setitemattrtext(p_item_type,
199 p_item_key,
200 'INVOICES_LIST9',
201 l_message_lines9);
202 wf_engine.setitemattrtext(p_item_type,
203 p_item_key,
204 'INVOICES_LIST10',
205 l_message_lines10);
206
207 END get_check_info;
208
209 -------------------------------------------------------------------------------
210 ------- Procedure get_remit_email_address returns the Remittance Email Address
211 ------- of the Supplier. This procedure is called by get_check_info and rule_function
212 ------- procedures
213 -------------------------------------------------------------------------------
214
215 PROCEDURE get_remit_email_address (p_check_id in NUMBER,
216 p_email_address out NOCOPY VARCHAR2) is
217
218 l_vendor_id po_vendor_sites_all.vendor_id%type;
219 l_vendor_site_id po_vendor_sites_all.vendor_site_id%type;
220
221 BEGIN
222
223 SELECT vendor_id,
224 vendor_site_id
225 INTO l_vendor_id,
226 l_vendor_site_id
227 FROM ap_checks
228 WHERE check_id = p_check_id;
229
230 --bug6119080, changed the query to fetch the email address from the
231 --correct table.
232 SELECT remit_advice_email
233 INTO p_email_address
234 FROM IBY_EXTERNAL_PAYEES_ALL
235 WHERE supplier_site_id = l_vendor_site_id;
236
237 END get_remit_email_address;
238
239 -------------------------------------------------------------------------------
240 ------- Procedure get_remit_email_address returns the Remittance Email Address
241 ------- of the Supplier. This procedure is called by get_check_info and rule_function
242 ------- procedures
243 -------------------------------------------------------------------------------------------
244
245 PROCEDURE get_check_status (p_check_id in NUMBER,
246 p_check_status out NOCOPY VARCHAR2) is
247
248
249 BEGIN
250
251 SELECT status_lookup_code
252 INTO p_check_status
253 FROM ap_checks
254 WHERE check_id = p_check_id;
255
256 END get_check_status;
257
258 ------ Procedure rule_function is called by the Subscription program. Rule Function determines
259 ------ whether WorkFlow Program should be called.
260 ------ Rule Defined is that to call the Workflow program only if the Remittance Email Address
261 ------ is available for the Supplier.
262 -------------------------------------------------------------------------------------------
263
264 FUNCTION rule_function (p_subscription in RAW,
265 p_event in out NOCOPY WF_EVENT_T) return varchar2 is
266
267
268 l_rule VARCHAR2(20);
269 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
270 l_parameter_t wf_parameter_t:= wf_parameter_t(null, null);
271 i_parameter_name l_parameter_t.name%type;
272 i_parameter_value l_parameter_t.value%type;
273 i pls_integer;
274
275 l_check_id l_parameter_t.value%type;
276 l_org_id l_parameter_t.value%type;
277 l_email_address IBY_EXTERNAL_PAYEES_ALL.remit_advice_email%type;
278 l_check_status ap_checks_all.status_lookup_code%type;
279
280 BEGIN
281
282 l_parameter_list := p_event.getParameterList();
283 if l_parameter_list is not null
284 then
285 i := l_parameter_list.FIRST;
286 while ( i <= l_parameter_list.LAST )
287 loop
288 i_parameter_name := null;
289 i_parameter_value := null;
290
291 i_parameter_name := l_parameter_list(i).getName();
292 i_parameter_value := l_parameter_list(i).getValue();
293
294 if i_parameter_name is not null
295 then
296 if i_parameter_name = 'CHECK_ID'
297 then
298 l_check_id := i_parameter_value;
299 elsif i_parameter_name = 'ORG_ID'
300 then
301 l_org_id := i_parameter_value;
302 end if;
303 end if;
304 i := l_parameter_list.NEXT(i);
305 end loop;
306
307 end if;
308
309
310 /* Set the Org_id Context */
311
312 if l_org_id is not null
313 then
314
315 --bug6119080, commented the existing code and added the new the code
316 --to properly set the org context.
317
318 -- fnd_client_info.set_org_context(l_org_id);
319 mo_global.set_policy_context('S',l_org_id);
320
321 end if;
322
323
324 /* Convert check_id into number as function get_value returns
325 it as a varchar2 */
326
327 get_remit_email_address(to_number(l_check_id),l_email_address);
328
329 /* Get the status of the Check */
330
331 get_check_status(to_number(l_check_id),l_check_status);
332
333
334 /* if email address is missing, then do not execute WF program */
335
336 if l_email_address is not null
337 then
338
339 /* if check is voided, then do not execute WF program */
340
341 if l_check_status <> 'VOIDED'
342 then
343
344 l_rule := wf_rule.default_rule(p_subscription,p_event);
345
346 end if;
347
348 end if;
349
350 return ('SUCCESS');
351
352 END rule_function;
353
354 END AP_PAYMENT_EVENT_WF_PKG;