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