1 PACKAGE BODY pa_wf_fb_sample_pkg
2 /* $Header: PAXTMPFB.pls 120.3 2005/08/08 12:24:54 sbharath ship $ */
3 AS
4
5 PROCEDURE pa_wf_sample_sql_fn
6 ( p_itemtype IN VARCHAR2,
7 p_itemkey IN VARCHAR2,
8 p_actid IN NUMBER,
9 p_funcmode IN VARCHAR2,
10 x_result OUT NOCOPY VARCHAR2)
11 AS
12
13 l_project_type pa_project_types_all.project_type%TYPE;
14 l_project_number pa_projects_all.segment1%TYPE;
15 l_expenditure_org_id hr_organization_units.organization_id%TYPE;
16 l_project_org_id hr_organization_units.organization_id%TYPE;
17 l_acc_org_id hr_organization_units.organization_id%TYPE;
18 l_org_map pa_lookups.meaning%TYPE;
19 l_expenditure_type pa_expenditure_types.expenditure_type%TYPE;
20 l_segment_value gl_code_combinations.segment1%TYPE;
21
22 BEGIN
23
24 ------------------------------------------------------------------------
25 -- This is a sample function that shows how the SQL statement of
26 -- autoaccounting can be implemented using Oracle Workflow. Assume
27 -- this mimicks a SQL statement in Autoaccounting which takes four
28 -- parameters: Project number, Expenditure Type, Expenditure
29 -- Organization id and Project Organization id
30 --
31 -- Assume that rules for obtaining the segment are as follows: If the
32 -- first two characters of the project number are 'AA' then the
33 -- project org should be used to get the segment otherwise the
34 -- expenditure org should be used to get the segment. Also assume that
35 -- the segment value is obtained from a lookup table, say PA_LOOKUPS
36 -- using the organization id and the expenditure type.
37 --
38 -- The equivalent SQL function in autoaccounting would have had the
39 -- Project number, Expenditure Organization Id, Project Organization
40 -- Id and Expenditure type as parameters. In the Workflow
41 -- implementation, these values have to be obtained from the
42 -- attributes of the Workflow item. After the values are obtained, the
43 -- required steps are performed to determine the final segment value.
44 -- An attribute in the Workflow item has to be defined to hold the
45 -- value of the final segment value. This function sets the value of
46 -- that attribute and the subsequent Workflow "Assign value to
47 -- segment" assigns the value of this attribute to the segment.
48 --
49 -- The return type of this function is "Flexfield Result" for which
50 -- the valid values are 'SUCCESS' and 'FAILURE'. The function must
51 -- return one of these values
52 --
53 ------------------------------------------------------------------------
54
55 -------------------------------------------------------------
56 -- First retrieve the values of these attributes into local
57 -- variables.
58 -------------------------------------------------------------
59
60 l_expenditure_org_id := wf_engine.GetItemAttrNumber
61 ( itemtype => p_itemtype,
62 itemkey => p_itemkey,
63 aname => 'EXPENDITURE_ORGANIZATION_ID');
64
65 l_project_org_id := wf_engine.GetItemAttrNumber
66 ( itemtype => p_itemtype,
67 itemkey => p_itemkey,
68 aname => 'PROJECT_ORGANIZATION_ID');
69
70 l_project_number := wf_engine.GetItemAttrText
71 ( itemtype => p_itemtype,
72 itemkey => p_itemkey,
73 aname => 'PROJECT_NUMBER');
74
75 l_expenditure_type := wf_engine.GetItemAttrText
76 ( itemtype => p_itemtype,
77 itemkey => p_itemkey,
78 aname => 'EXPENDITURE_TYPE');
79
80 ---------------------------------------------------
81 -- Now start determining the value of the segment
82 ---------------------------------------------------
83
84 -- First determine which of the organization ids are to be used The assumption
85 -- is that if the first two characters of the project number are 'AA',
86 -- then the Project Organization Id is to be used for account
87 -- generation. Otherwise, the Expenditure Organization Id is used for
88 -- account generation.
89
90 IF substr(l_project_number, 1, 2) = 'AA'
91 THEN
92 l_acc_org_id := l_project_org_id;
93 ELSE
94 l_acc_org_id := l_expenditure_org_id;
95 END IF;
96
97 -- Assume that the organization id is mapped to another field from a lookup
98 -- table. PA_LOOKUPS has been used in this example. Any table could
99 -- function as a lookup. Please note that the LOOKUP_TYPE used here
100 -- may not actually exist and is just used for illustration
101
102 BEGIN
103
104 SELECT meaning
105 INTO l_org_map
106 FROM pa_lookups
107 WHERE lookup_type = 'ORG_TO_ACC'
108 AND lookup_code = l_acc_org_id;
109
110 EXCEPTION
111
112 WHEN no_data_found
113 THEN
114 -- Set appropriate debugging information for workflow
115
116 wf_core.context( pkg_name => 'PA_WF_FB_SAMPLE_PKG',
117 proc_name => 'PA_WF_SAMPLE_SQL_FN',
118 arg1 => l_project_number,
119 arg2 => l_project_org_id,
120 arg3 => l_expenditure_org_id,
121 arg4 => l_expenditure_type,
122 arg5 => null);
123
124 -- Error requires an error message to be set so that it can be
125 -- displayed on the form. The error message name is defined in
126 -- Applications and the name is set here. The form should read this
127 -- error message and decode it to get the original message text.
128
129 wf_engine.SetItemAttrText
130 ( itemtype=> p_itemtype,
131 itemkey => p_itemkey,
132 aname => 'ERROR_MESSAGE',
133 avalue => 'SUP_INV_ACC_ORG_ID_LOOKUP FAIL');
134
135 -- Return a failure so that the abort generation End function is called
136
137 x_result := 'COMPLETE:FAILURE';
138 RETURN;
139 END;
140
141 -- Assume that the final segment value is again derived from
142 -- PA_LOOKUPS and is a combination of the variable l_org_map
143 -- determined above and the Expenditure type
144
145 BEGIN
146
147 SELECT meaning
148 INTO l_segment_value
149 FROM pa_lookups
150 WHERE lookup_type = 'ORG_EXP_TYPE'
151 AND lookup_code = l_org_map || l_expenditure_type;
152
153 EXCEPTION
154
155 WHEN no_data_found
156 THEN
157
158 -- Set appropriate debugging information
159
160 wf_core.context( pkg_name => 'PA_WF_FB_SAMPLE_PKG',
161 proc_name => 'PA_WF_SAMPLE_SQL_FN',
162 arg1 => l_project_number,
163 arg2 => l_project_org_id,
164 arg3 => l_expenditure_org_id,
165 arg4 => l_expenditure_type,
166 arg5 => null);
167
168 -- Error requires an error message to be set so that it can be displayed
169 -- on the form
170
171 wf_engine.SetItemAttrText
172 ( itemtype=> p_itemtype,
173 itemkey=> p_itemkey,
174 aname => 'ERROR_MSG',
175 avalue=> 'Org and Expenditure type lookup failed ' ||
176 'during account generation');
177 -- Return a failure so that the abort generation End function is called
178
179 x_result := 'COMPLETE:FAILURE';
180 RETURN;
181
182 END;
183
184
185 -- If control passes to this point, the segment value has been
186 -- determined correctly. Use this newly determined value to set the
187 -- value of the corresponding attribute in Workflow so that it is
188 -- available to subsequent functions
189 -- For the purpose of the sample and to avoid defining an extra item
190 -- attribute that will not be used later, the item attribute used is
191 -- the same that is used in the lookup set value (LOOKUP_SET_VALUE).
192 -- It is desirable that you define and use your own item attributes
193 -- e.g. SAMPLE_SEGMENT_1.
194
195 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
196 itemkey => p_itemkey,
197 aname => 'LOOKUP_SET_VALUE',
198 avalue => l_segment_value);
199
200 -- Return a success since the segment value has been determined
201 -- correctly. 'SUCCESS' is the expected result since the result of the
202 -- process has been defined as Flexfield Result
203
204 x_result := 'COMPLETE:SUCCESS';
205 RETURN;
206
207 EXCEPTION
208
209 WHEN OTHERS
210 THEN
211
212 -- Record error using generic error message routine for debugging and
213 -- raise it
214
215 wf_core.context( pkg_name => 'PA_WF_FB_SAMPLE_PKG',
216 proc_name => 'PA_WF_SAMPLE_SQL_FN',
217 arg1 => l_project_number,
218 arg2 => l_project_org_id,
219 arg3 => l_expenditure_org_id,
220 arg4 => l_expenditure_type,
221 arg5 => null);
222
223 raise;
224
225 END pa_wf_sample_sql_fn ;
226
227 --PA_TEST_AP_INV_ACCOUNT:
228 --
229 -- This procedure is used to test your workflow for the supplier
230 -- invoice charge account. After you design your workflow and save it
231 -- in the database, you can modify this procedure to test it. Before
232 -- beginning testing, make sure that the Account Generator window
233 -- shows the correct process that you want to run for your account
234 -- generation for your set of books.
235 --
236 -- Log into SQL*Plus into the database where you want to test your
237 -- workflow. Make sure that the dbms_application_info.set_client_info
238 -- package is run to select the correct organization id. Modify this
239 -- procedure or a copy of it giving values for each of the parameters
240 -- that will be used in account generation. The Project Id parameter
241 -- is mandatory. Replace the values below with your values for Project
242 -- Id, Task Id, Expenditure Type, etc. for the transaction for which
243 -- you want to test account generation. Also remember to "set
244 -- serveroutput on" before testing as the results of the Workflow are
245 -- displayed using dbms_output statements.
246 --
247 -- A typical test would be as follows:
248 --
249 -- SQL> set serveroutput on
250 -- SQL> begin
251 -- 2 pa_wf_fb_sample_pkg.pa_test_ap_inv_account;
252 -- 3 end;
253 -- 4 /
254 --
255 -- Alternatively, you can modify this procedure so that the parameters
256 -- that are used to vary the account are input to the procedure so
257 -- that you can run the procedure by passing the parameters instead of
258 -- modifying and recompiling each time
259 --
260
261 PROCEDURE pa_test_ap_inv_account
262 IS
263 p_return_ccid NUMBER;
264 p_concat_segs VARCHAR2(300);
265 p_concat_ids VARCHAR2(300);
266 p_concat_descrs VARCHAR2(300);
267 p_errmsg VARCHAR2(1300);
268 p_ret_value BOOLEAN;
269 x_ret_value_s VARCHAR2(20);
270
271 BEGIN
272
273 -- Set the profile option so that data is not purged from
274 -- the workflow tables
275
276 fnd_profile.put('ACCOUNT_GENERATOR:PURGE_DATA','N');
277 p_ret_value := pa_acc_gen_wf_pkg.ap_inv_generate_account (
278 p_project_id => 1000,
279 p_task_id => 1000,
280 p_expenditure_type => 'XXXXXX',
281 p_vendor_id => 1000,
282 p_expenditure_organization_id => 1000,
283 p_expenditure_item_date => to_date('01/01/1998','MM/DD/YYYY'),
284 p_billable_flag => 'Y',
285 p_chart_of_Accounts_id => 101,
286 p_accounting_date => to_date('01/01/1998','MM/DD/YYYY'),
287 p_attribute_category => null,
288 p_attribute1 => null,
289 p_attribute2 => null,
290 p_attribute3 => null,
291 p_attribute4 => null,
292 p_attribute5 => null,
293 p_attribute6 => null,
294 p_attribute7 => null,
295 p_attribute8 => null,
296 p_attribute9 => null,
297 p_attribute10 => null,
298 p_attribute11 => null,
299 p_attribute12 => null,
300 p_attribute13 => null,
301 p_attribute14 => null,
302 p_attribute15 => null,
303 p_dist_attribute_category => null,
304 p_dist_attribute1 => null,
305 p_dist_attribute2 => null,
306 p_dist_attribute3 => null,
307 p_dist_attribute4 => null,
308 p_dist_attribute5 => null,
309 p_dist_attribute6 => null,
310 p_dist_attribute7 => null,
311 p_dist_attribute8 => null,
312 p_dist_attribute9 => null,
313 p_dist_attribute10 => null,
314 p_dist_attribute11 => null,
315 p_dist_attribute12 => null,
316 p_dist_attribute13 => null,
317 p_dist_attribute14 => null,
318 p_dist_attribute15 => null,
319 x_return_ccid => p_return_ccid,
320 x_concat_segs => p_concat_segs,
321 x_concat_ids => p_concat_ids,
322 x_concat_descrs => p_concat_descrs,
323 x_error_message => p_errmsg);
324
325 -- Check the return value after calling
326
327 /*
328 In actual environment, for debugging, please put set serveroutput on
329 and uncomment the following dbms_output statements.
330 */
331
332 IF p_ret_value = True
333 THEN
334 x_ret_value_s := 'True';
335 /* dbms_output.put_line('Function was successful'); */
336 ELSE
337 x_ret_value_s := 'False';
338 /* dbms_output.put_line('Function was not successful'); */
339 END IF;
340
341 /* dbms_output.put_line('Return Value =' || x_ret_value_s); */
342 /* dbms_output.put_line('Dervied CCID =' || p_return_ccid); */
343 /* dbms_output.put_line('Segments =' || p_concat_segs); */
344 /* dbms_output.put_line('Segment ids =' || p_concat_ids); */
345 /* dbms_output.put_line('Description =' || p_concat_descrs); */
346 /* dbms_output.put_line ('Error message =' || p_errmsg ); */
347
348 END;
349
350 END pa_wf_fb_sample_pkg;
[Home] [Help]