[Home] [Help]
PACKAGE BODY: APPS.PA_ACC_GEN_WF_PKG
Source
1 PACKAGE BODY pa_acc_gen_wf_pkg AS
2 /* $Header: PAXWFACB.pls 120.11.12010000.2 2008/08/22 16:20:06 mumohan ship $ */
3
4 /***Bug 3182416 :Moved the declaration of g_ variables to spec .
5 g_error_message VARCHAR2(1000) :='';
6 g_error_stack VARCHAR2(500) :='';
7 g_error_stage VARCHAR2(100) :='';
8 *************************************************************/
9
10 /* Bug 5233487 - g_error_stack_history will store all the messages in the error stack */
11 TYPE ErrorStack IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
12 g_error_stack_history ErrorStack;
13
14 ----------------------------------------------------------------------
15 -- Procedure pa_acc_gen_wf_pkg.wf_acc_derive_params
16 -- Definition of procedure in package specifications
17 ----------------------------------------------------------------------
18
19 PROCEDURE wf_acc_derive_params (
20 p_project_id IN pa_projects_all.project_id%TYPE,
21 p_task_id IN pa_tasks.task_id%TYPE,
22 p_expenditure_type IN pa_expenditure_types.expenditure_type%TYPE,
23 p_vendor_id IN po_vendors.vendor_id%type,
24 p_expenditure_organization_id IN hr_organization_units.organization_id%TYPE,
25 p_expenditure_item_date IN
26 pa_expenditure_items_all.expenditure_item_date%TYPE,
27 x_class_code OUT NOCOPY pa_class_codes.class_code%TYPE,
28 x_direct_flag OUT NOCOPY pa_project_types_all.direct_flag%TYPE,
29 x_expenditure_category OUT
30 NOCOPY pa_expenditure_categories.expenditure_category%TYPE,
31 x_expenditure_org_name OUT NOCOPY hr_organization_units.name%TYPE,
32 x_project_number OUT NOCOPY pa_projects_all.segment1%TYPE,
33 x_project_organization_name OUT NOCOPY hr_organization_units.name%TYPE,
34 x_project_organization_id OUT NOCOPY hr_organization_units.organization_id %TYPE,
35 x_project_type OUT NOCOPY pa_project_types_all.project_type%TYPE,
36 x_public_sector_flag OUT NOCOPY pa_projects_all.public_sector_flag%TYPE,
37 x_revenue_category OUT NOCOPY pa_expenditure_types.revenue_category_code%TYPE,
38 x_task_number OUT NOCOPY pa_tasks.task_number%TYPE,
39 x_task_organization_name OUT NOCOPY hr_organization_units.name%TYPE,
40 x_task_organization_id OUT NOCOPY hr_organization_units.organization_id %TYPE,
41 x_task_service_type OUT NOCOPY pa_tasks.service_type_code%TYPE,
42 x_top_task_id OUT NOCOPY pa_tasks.task_id%TYPE,
43 x_top_task_number OUT NOCOPY pa_tasks.task_number%TYPE,
44 x_vendor_employee_id OUT NOCOPY per_people_f.person_id%TYPE,
45 x_vendor_employee_number OUT NOCOPY per_people_f.employee_number%TYPE,
46 x_vendor_type OUT NOCOPY po_vendors.vendor_type_lookup_code%TYPE)
47 AS
48
49 l_person_effective_date DATE;
50
51 BEGIN
52
53 set_error_stack('-->wf_acc_derive_params'); /* Bug 5233487 */
54 g_encoded_error_message := NULL; /* Bug 5233487 */
55 g_error_message := '';
56 ---------------------------------------------------
57 -- If EI date is not passed, consider system date
58 ---------------------------------------------------
59 g_error_stage := '10';
60
61 IF p_expenditure_item_date is null
62 THEN
63 l_person_effective_date := sysdate;
64 ELSE
65 l_person_effective_date := p_expenditure_item_date;
66 END IF;
67
68 ----------------------------------------------------------
69 -- Derive vendor information if the vendor id is present
70 ----------------------------------------------------------
71 g_error_stage := '20';
72
73 IF p_vendor_id IS NOT NULL
74 THEN /* Commented for Bug# 4007983
75 SELECT
76 VEND.employee_id VENDOR_EMPLOYEE_ID,
77 EMP.employee_number VENDOR_EMPLOYEE_NUMBER,
78 VEND.vendor_type_lookup_code VENDOR_TYPE
79 INTO
80 x_vendor_employee_id,
81 x_vendor_employee_number,
82 x_vendor_type
83 FROM
84 po_vendors VEND,
85 per_people_f EMP
86 WHERE
87 VEND.vendor_id = p_vendor_id
88 AND VEND.employee_id = EMP.person_id (+)
89 AND l_person_effective_date
90 between EMP.effective_start_date(+)
91 and nvl(EMP.effective_end_date(+),sysdate); */
92
93 /* Start of Bug# 4007983 - Replaced the above query by these 2 queries */
94 SELECT
95 VEND.employee_id VENDOR_EMPLOYEE_ID,
96 VEND.vendor_type_lookup_code VENDOR_TYPE
97 INTO
98 x_vendor_employee_id,
99 x_vendor_type
100 FROM
101 po_vendors VEND
102 WHERE
103 VEND.vendor_id = p_vendor_id;
104
105 IF x_vendor_employee_id IS NOT NULL
106 THEN
107 BEGIN -- Bug 6053374
108 SELECT
109 EMP.employee_number VENDOR_EMPLOYEE_NUMBER
110 INTO
111 x_vendor_employee_number
112 FROM
113 per_people_f EMP
114 WHERE x_vendor_employee_id = EMP.person_id
115 AND l_person_effective_date between EMP.effective_start_date
116 and NVL (EMP.effective_end_date, sysdate);
117
118 /* Bug 6053374: Added the Exception block. Exception would be thrown only for a
119 Standard Invoice, as the case would be taken care in PAXTTXCB itself, for an Expense Report.
120 And for Standard Invoices, System should not throw an error even if the EI date does not fall
121 between effective_start_date and effective_end_date of the employee */
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 null;
125 END; -- Bug 6053374: End
126 END IF;
127 /* End of Bug# 4007983 */
128 END IF;
129
130 ---------------------------------------------------------------
131 -- Derive project information
132 ---------------------------------------------------------------
133 g_error_stage := '30';
134
135 pa_acc_gen_wf_pkg.wf_acc_derive_pa_params(
136 p_project_id ,
137 p_task_id ,
138 p_expenditure_type ,
139 p_expenditure_organization_id ,
140 p_expenditure_item_date ,
141 x_class_code ,
142 x_direct_flag ,
143 x_expenditure_category ,
144 x_expenditure_org_name ,
145 x_project_number ,
146 x_project_organization_name ,
147 x_project_organization_id ,
148 x_project_type ,
149 x_public_sector_flag ,
150 x_revenue_category ,
151 x_task_number ,
152 x_task_organization_name ,
153 x_task_organization_id ,
154 x_task_service_type ,
155 x_top_task_id ,
156 x_top_task_number );
157
158 reset_error_stack; /* Bug 5233487 */
159
160 EXCEPTION WHEN others THEN
161 /* Bug 5233487 - Start */
162 IF g_encoded_error_message IS NULL THEN
163 g_encoded_error_message := show_error(g_error_stack,g_error_stage,NULL);
164 END IF;
165 reset_error_stack;
166 /* Bug 5233487 - End */
167 g_error_message := SQLERRM;
168 raise;
169 END wf_acc_derive_params;
170
171 ------------------- End of procedure wf_acc_Derive_params ---------------------
172
173 ----------------------------------------------------------------------
174 -- Procedure pa_acc_gen_wf_pkg.wf_acc_derive_er_params
175 -- Definition of procedure in package specifications
176 ----------------------------------------------------------------------
177
178 PROCEDURE wf_acc_derive_er_params (
179 p_project_id IN pa_projects_all.project_id%TYPE,
180 p_task_id IN pa_tasks.task_id%TYPE,
181 p_expenditure_type IN pa_expenditure_types.expenditure_type%TYPE,
182 p_vendor_id IN po_vendors.vendor_id%type,
183 p_expenditure_organization_id IN hr_organization_units.organization_id%TYPE,
184 p_expenditure_item_date IN
185 pa_expenditure_items_all.expenditure_item_date%TYPE,
186 p_calling_module IN VARCHAR2,
187 p_employee_id IN per_people_f.person_id%TYPE,
188 p_employee_ccid IN OUT NOCOPY gl_code_combinations.code_combination_id%TYPE,
189 p_expense_type IN ap_expense_report_lines_all.web_parameter_id%TYPE,
190 p_expense_cc IN ap_expense_report_headers_all.flex_concatenated%TYPE,
191 x_class_code OUT NOCOPY pa_class_codes.class_code%TYPE,
192 x_direct_flag OUT NOCOPY pa_project_types_all.direct_flag%TYPE,
193 x_expenditure_category OUT NOCOPY pa_expenditure_categories.expenditure_category%TYPE,
194 x_expenditure_org_name OUT NOCOPY hr_organization_units.name%TYPE,
195 x_project_number OUT NOCOPY pa_projects_all.segment1%TYPE,
196 x_project_organization_name OUT NOCOPY hr_organization_units.name%TYPE,
197 x_project_organization_id OUT NOCOPY hr_organization_units.organization_id%TYPE,
198 x_project_type OUT NOCOPY pa_project_types_all.project_type%TYPE,
199 x_public_sector_flag OUT NOCOPY pa_projects_all.public_sector_flag%TYPE,
200 x_revenue_category OUT NOCOPY pa_expenditure_types.revenue_category_code%TYPE,
201 x_task_number OUT NOCOPY pa_tasks.task_number%TYPE,
202 x_task_organization_name OUT NOCOPY hr_organization_units.name%TYPE,
203 x_task_organization_id OUT NOCOPY hr_organization_units.organization_id%TYPE,
204 x_task_service_type OUT NOCOPY pa_tasks.service_type_code%TYPE,
205 x_top_task_id OUT NOCOPY pa_tasks.task_id%TYPE,
206 x_top_task_number OUT NOCOPY pa_tasks.task_number%TYPE,
207 x_employee_number OUT NOCOPY per_people_f.employee_number%TYPE,
208 x_vendor_type OUT NOCOPY po_vendors.vendor_type_lookup_code%TYPE,
209 x_person_type OUT NOCOPY VARCHAR2 )
210 AS
211
212 l_person_effective_date DATE;
213 l_employee_ccid Number;
214
215 BEGIN
216 set_error_stack('-->wf_acc_derive_er_params'); /* Bug 5233487 */
217 g_encoded_error_message := NULL; /* Bug 5233487 */
218 g_error_message := '';
219
220 ---------------------------------------------------
221 -- If EI date is not passed, consider system date
222 ---------------------------------------------------
223 g_error_stage := '10';
224
225 IF p_expenditure_item_date is null
226 THEN
227 l_person_effective_date := sysdate;
228 ELSE
229 l_person_effective_date := p_expenditure_item_date;
230 END IF;
231
232 ----------------------------------------------------------
233 -- Derive vendor information if the vendor id is present
234 ----------------------------------------------------------
235 g_error_stage := '20';
236
237 IF p_vendor_id IS NOT NULL
238 THEN
239 SELECT
240 VEND.vendor_type_lookup_code VENDOR_TYPE
241 INTO
242 x_vendor_type
243 FROM
244 po_vendors VEND
245 WHERE
246 VEND.vendor_id = p_vendor_id ;
247 END IF;
248
249 ---------------------------------------------------------------
250 -- Derive employee information if employee id is present
251 ---------------------------------------------------------------
252 g_error_stage := '30';
253
254 IF p_employee_id IS NOT NULL
255 THEN
256 BEGIN
257 /** Commented out below SQL to accommodate CWK changes **/
258
259 /* SELECT
260 EMP.employee_num, EMP.default_code_combination_id
261 INTO
262 x_employee_number, l_employee_ccid
263 FROM
264 hr_employees_current_v EMP
265 WHERE
266 EMP.employee_id = p_employee_id; */
267
268 SELECT DECODE(p.current_npw_flag,'Y',p.npw_number, p.employee_number) employee_number, a.default_code_comb_id,
269 DECODE(p.current_npw_flag,'Y','CWK','EMP') person_type
270 INTO x_employee_number, l_employee_ccid, x_person_type
271 FROM per_people_f p,
272 per_assignments_f a,
273 per_assignment_status_types past
274 WHERE p.person_id = p_employee_id
275 AND p.business_group_id + 0 = (SELECT nvl(max(fsp.business_group_id),0)
276 FROM financials_system_parameters fsp)
277 AND a.person_id = p.person_id
278 AND a.primary_flag = 'Y'
279 AND TRUNC(l_person_effective_date) BETWEEN p.effective_start_date and p.effective_end_date
280 AND TRUNC(l_person_effective_date) BETWEEN a.effective_start_date and a.effective_end_date
281 AND ((p.current_employee_flag = 'Y')
282 OR (p.current_npw_flag = 'Y') )
283 AND a.assignment_type in ('E','C')
284 AND a.assignment_status_type_id = past.assignment_status_type_id
285 AND past.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN','ACTIVE_CWK');
286
287 EXCEPTION WHEN no_data_found THEN /* added for bug 6954412 */
288 fnd_message.set_name('PA','PA_NO_ASSIGNMENT');
289 app_exception.raise_exception;
290
291 END;
292
293 IF p_employee_ccid is NULL then
294 p_employee_ccid := l_employee_ccid;
295 END IF;
296
297 END IF;
298
299
300 ---------------------------------------------------------------
301 -- Derive project information
302 ---------------------------------------------------------------
303 g_error_stage := '40';
304
305 pa_acc_gen_wf_pkg.wf_acc_derive_pa_params(
306 p_project_id ,
307 p_task_id ,
308 p_expenditure_type ,
309 p_expenditure_organization_id ,
310 p_expenditure_item_date ,
311 x_class_code ,
312 x_direct_flag ,
313 x_expenditure_category ,
314 x_expenditure_org_name ,
315 x_project_number ,
316 x_project_organization_name ,
317 x_project_organization_id ,
318 x_project_type ,
319 x_public_sector_flag ,
320 x_revenue_category ,
321 x_task_number ,
322 x_task_organization_name ,
323 x_task_organization_id ,
324 x_task_service_type ,
325 x_top_task_id ,
326 x_top_task_number );
327
328 reset_error_stack; /* Bug 5233487 */
329
330 EXCEPTION WHEN others THEN
331 /* Bug 5233487 - Start */
335 reset_error_stack;
332 IF g_encoded_error_message IS NULL THEN
333 g_encoded_error_message := show_error(g_error_stack,g_error_stage,NULL);
334 END IF;
336 /* Bug 5233487 - End */
337 g_error_message := SQLERRM;
338 raise;
339
340 END wf_acc_derive_er_params;
341
342 ------------------- End of procedure wf_acc_Derive_er_params ------------------
343
344 ----------------------------------------------------------------------
345 -- Procedure pa_acc_gen_wf_pkg.wf_acc_derive_pa_params
346 -- Definition of package body and function in package specifications
347 ----------------------------------------------------------------------
348
349
350 PROCEDURE wf_acc_derive_pa_params (
351 p_project_id IN pa_projects_all.project_id%TYPE,
352 p_task_id IN pa_tasks.task_id%TYPE,
353 p_expenditure_type IN pa_expenditure_types.expenditure_type%TYPE,
354 p_expenditure_organization_id IN hr_organization_units.organization_id%TYPE,
355 p_expenditure_item_date IN pa_expenditure_items_all.expenditure_item_date%TYPE,
356 x_class_code OUT NOCOPY pa_class_codes.class_code%TYPE,
357 x_direct_flag OUT NOCOPY pa_project_types_all.direct_flag%TYPE,
358 x_expenditure_category OUT NOCOPY pa_expenditure_categories.expenditure_category%TYPE,
359 x_expenditure_org_name OUT NOCOPY hr_organization_units.name%TYPE,
360 x_project_number OUT NOCOPY pa_projects_all.segment1%TYPE,
361 x_project_organization_name OUT NOCOPY hr_organization_units.name%TYPE,
362 x_project_organization_id OUT NOCOPY hr_organization_units.organization_id %TYPE,
363 x_project_type OUT NOCOPY pa_project_types_all.project_type%TYPE,
364 x_public_sector_flag OUT NOCOPY pa_projects_all.public_sector_flag%TYPE,
365 x_revenue_category OUT NOCOPY pa_expenditure_types.revenue_category_code%TYPE,
366 x_task_number OUT NOCOPY pa_tasks.task_number%TYPE,
367 x_task_organization_name OUT NOCOPY hr_organization_units.name%TYPE,
368 x_task_organization_id OUT NOCOPY hr_organization_units.organization_id %TYPE,
369 x_task_service_type OUT NOCOPY pa_tasks.service_type_code%TYPE,
370 x_top_task_id OUT NOCOPY pa_tasks.task_id%TYPE,
371 x_top_task_number OUT NOCOPY pa_tasks.task_number%TYPE)
372 AS
373
374 BEGIN
375 set_error_stack('-->wf_acc_derive_pa_params'); /* Bug 5233487 */
376 g_encoded_error_message := NULL; /* Bug 5233487 */
377 g_error_message := '';
378 g_error_stage := '10';
379 -----------------------------------------------------
380 -- Project id will always be there.
381 -- Get all project-related derived parameters
382
383
384 SELECT
385 PTYPE.direct_flag DIRECT_FLAG,
386 PROJ.segment1 PROJECT_NUMBER,
387 ORG.Name PROJECT_ORGANIZATION_NAME,
388 ORG.Organization_ID PROJECT_ORGANIZATION_ID,
389 PROJ.project_type PROJECT_TYPE,
390 PROJ.public_sector_flag PUBLIC_SECTOR_FLAG
391 INTO
392 x_direct_flag,
393 x_project_number,
394 x_project_organization_name,
395 x_project_organization_id,
396 x_project_type,
397 x_public_sector_flag
398 FROM
399 HR_Organization_Units ORG,
400 PA_Project_Types_all PTYPE,
401 PA_Projects_all PROJ
402 WHERE
403 PROJ.project_id = p_project_id
404 AND ORG.organization_id = PROJ.carrying_out_organization_id
405 AND nvl(PTYPE.org_id,-99) = nvl(PROJ.org_id,-99)
406 AND PTYPE.Project_Type = PROJ.Project_Type;
407
408 ----------------------------------------------------------------
409 -- Derive the expenditure category and revenue category if the
410 -- expenditure type is defined
411 ----------------------------------------------------------------
412 g_error_stage := '20';
413
414 IF p_expenditure_type is not null
415 THEN
416 SELECT
417 ETYPE.Expenditure_Category EXPENDITURE_CATEGORY,
418 ETYPE.revenue_category_code REVENUE_CATEGORY
419 INTO
420 x_expenditure_category,
421 x_revenue_category
422 FROM
423 PA_Expenditure_Types ETYPE
424 WHERE
425 ETYPE.expenditure_type = p_expenditure_type;
426 END IF;
427
428 ----------------------------------------------------------
429 -- Derive the project class code if it exists; otherwise
430 -- set the parameter to null
431 -- Bug 998553: Added clause to select class category row
432 -- valid for sysdate
433 ----------------------------------------------------------
434 g_error_stage := '30';
435
436 BEGIN
437 SELECT a.class_code
438 INTO x_class_code
439 FROM pa_project_classes a,
440 pa_class_categories b
444 AND sysdate BETWEEN b.start_date_active
441 WHERE a.project_id = p_project_id
442 AND a.class_category = b.class_category
443 AND b.autoaccounting_flag = 'Y'
445 AND nvl(b.end_date_active, sysdate);
446
447 EXCEPTION
448 WHEN no_data_found
449 THEN
450 x_class_code := null;
451 END;
452
453 ------------------------------------------------------------
454 -- Derive Expenditure organization name is Expenditure org
455 -- id has been passed
456 ------------------------------------------------------------
457 g_error_stage := '40';
458
459 IF p_expenditure_organization_id IS NOT NULL
460 THEN
461 SELECT ORG.name EXP_ORG_NAME
462 INTO x_expenditure_org_name
463 FROM hr_organization_units ORG
464 WHERE ORG.organization_id = p_expenditure_organization_id;
465 END IF;
466
467 --------------------------------------------------------
468 -- Derive parameters related to the task id if task id
469 -- has been passed
470 --------------------------------------------------------
471 g_error_stage := '50';
472
473 IF p_task_id IS NOT NULL
474 THEN
475 SELECT
476 TASK.task_number TASK_NUMBER,
477 ORG.Name TASK_ORGANIZATION_NAME,
478 ORG.Organization_id TASK_ORGANIZATION_ID,
479 TASK.Service_Type_Code TASK_SERVICE_TYPE,
480 TOP_TASK.Task_ID TOP_TASK_ID,
481 TOP_TASK.Task_Number TOP_TASK_NUMBER
482 INTO
483 x_task_number,
484 x_task_organization_name,
485 x_task_organization_id,
486 x_task_service_type,
487 x_top_task_id,
488 x_top_task_number
489 FROM
490 HR_Organization_Units ORG,
491 PA_Tasks TOP_TASK,
492 PA_Tasks TASK
493 WHERE
494 TASK.task_id = p_task_id
495 AND ORG.organization_id = TASK.carrying_out_organization_id
496 AND TASK.Top_Task_ID = TOP_TASK.Task_ID;
497 END IF;
498
499 reset_error_stack; /* Bug 5233487 */
500
501 EXCEPTION WHEN OTHERS THEN
502 /* Bug 5233487 - Start */
503 IF g_encoded_error_message IS NULL THEN
504 g_encoded_error_message := show_error(g_error_stack,g_error_stage,NULL);
505 END IF;
506 reset_error_stack;
507 /* Bug 5233487 - End */
508 g_error_message := SQLERRM;
509 raise;
510 END wf_acc_derive_pa_params;
511
512 ------------------- End of procedure wf_acc_Derive_pa_params -------------------
513
514 ----------------------------------------------------------------------
515 -- Procedure pa_acc_gen_wf_pkg.SetPa_Item_Attr
516 -- Definition of package body and procedure in package specifications
517 ----------------------------------------------------------------------
518 PROCEDURE Set_Pa_Item_Attr
519 (
520 p_itemtype IN VARCHAR2,
521 p_itemkey IN VARCHAR2,
522 p_project_id IN pa_projects_all.project_id%TYPE,
523 p_task_id IN pa_tasks.task_id%TYPE,
524 p_expenditure_type IN pa_expenditure_types.expenditure_type%TYPE,
525 p_expenditure_organization_id IN hr_organization_units.organization_id%TYPE,
526 p_expenditure_item_date IN DATE, /* Added For Bug 1629411 */
527 p_billable_flag IN pa_tasks.billable_flag%TYPE,
528 p_class_code IN pa_class_codes.class_code%TYPE,
529 p_direct_flag IN pa_project_types_all.direct_flag%TYPE,
530 p_expenditure_category IN pa_expenditure_categories.expenditure_category%TYPE,
531 p_expenditure_org_name IN hr_organization_units.name%TYPE,
532 p_project_number IN pa_projects_all.segment1%TYPE,
533 p_project_organization_name IN hr_organization_units.name%TYPE,
534 p_project_organization_id IN hr_organization_units.organization_id %TYPE,
535 p_project_type IN pa_project_types_all.project_type%TYPE,
536 p_public_sector_flag IN pa_projects_all.public_sector_flag%TYPE,
537 p_revenue_category IN pa_expenditure_types.revenue_category_code%TYPE,
538 p_task_number IN pa_tasks.task_number%TYPE,
539 p_task_organization_name IN hr_organization_units.name%TYPE,
540 p_task_organization_id IN hr_organization_units.organization_id %TYPE,
541 p_task_service_type IN pa_tasks.service_type_code%TYPE,
542 p_top_task_id IN pa_tasks.task_id%TYPE,
543 p_top_task_number IN pa_tasks.task_number%TYPE) AS
544
545
546
547 BEGIN
548 set_error_stack('-->Set_Pa_Item_Attr'); /* Bug 5233487 */
549 g_encoded_error_message := NULL; /* Bug 5233487 */
550 g_error_message := '';
551 g_error_stage := '10';
552
553 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
554 itemkey => p_itemkey,
555 aname => 'PROJECT_ID',
556 avalue => p_project_id);
557 g_error_stage := '20';
558
559
563 avalue => p_task_id);
560 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
561 itemkey => p_itemkey,
562 aname => 'TASK_ID',
564 g_error_stage := '30';
565
566 wf_engine.SetItemAttrText( itemtype => p_itemtype,
567 itemkey => p_itemkey,
568 aname => 'EXPENDITURE_TYPE',
569 avalue => p_expenditure_type);
570 g_error_stage := '40';
571
572 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
573 itemkey => p_itemkey,
574 aname => 'EXPENDITURE_ORGANIZATION_ID',
575 avalue => p_expenditure_organization_id);
576 g_error_stage := '50';
577
578 wf_engine.SetItemAttrText( itemtype => p_itemtype,
579 itemkey => p_itemkey,
580 aname => 'BILLABLE_FLAG',
581 avalue => p_billable_flag);
582 g_error_stage := '60';
583
584 wf_engine.SetItemAttrText( itemtype => p_itemtype,
585 itemkey => p_itemkey,
586 aname => 'CLASS_CODE',
587 avalue => p_class_code);
588 g_error_stage := '70';
589
590 wf_engine.SetItemAttrText( itemtype => p_itemtype,
591 itemkey => p_itemkey,
592 aname => 'DIRECT_FLAG',
593 avalue => p_direct_flag);
594 g_error_stage := '80';
595
596 wf_engine.SetItemAttrText( itemtype => p_itemtype,
597 itemkey => p_itemkey,
598 aname => 'EXPENDITURE_CATEGORY',
599 avalue => p_expenditure_category);
600 g_error_stage := '90';
601
602 wf_engine.SetItemAttrText( itemtype => p_itemtype,
603 itemkey => p_itemkey,
604 aname => 'EXPENDITURE_ORG_NAME',
605 avalue => p_expenditure_org_name);
606 g_error_stage := '100';
607
608 wf_engine.SetItemAttrText( itemtype => p_itemtype,
609 itemkey => p_itemkey,
610 aname => 'PROJECT_NUMBER',
611 avalue => p_project_number);
612 g_error_stage := '110';
613
614 wf_engine.SetItemAttrText( itemtype => p_itemtype,
615 itemkey => p_itemkey,
616 aname => 'PROJECT_ORGANIZATION_NAME',
617 avalue => p_project_organization_name);
618 g_error_stage := '120';
619
620 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
621 itemkey => p_itemkey,
622 aname => 'PROJECT_ORGANIZATION_ID',
623 avalue => p_project_organization_id);
624 g_error_stage := '130';
625
626 wf_engine.SetItemAttrText( itemtype => p_itemtype,
627 itemkey => p_itemkey,
628 aname => 'PROJECT_TYPE',
629 avalue => p_project_type);
630 g_error_stage := '140';
631
632 wf_engine.SetItemAttrText( itemtype => p_itemtype,
633 itemkey => p_itemkey,
634 aname => 'PUBLIC_SECTOR_FLAG',
635 avalue => p_public_sector_flag);
636 g_error_stage := '150';
637
638 wf_engine.SetItemAttrText( itemtype => p_itemtype,
639 itemkey => p_itemkey,
640 aname => 'REVENUE_CATEGORY',
641 avalue => p_revenue_category);
642 g_error_stage := '160';
643
644 wf_engine.SetItemAttrText( itemtype => p_itemtype,
645 itemkey => p_itemkey,
646 aname => 'TASK_NUMBER',
647 avalue => p_task_number);
648 g_error_stage := '170';
649
650 wf_engine.SetItemAttrText( itemtype => p_itemtype,
651 itemkey => p_itemkey,
652 aname => 'TASK_ORGANIZATION_NAME',
653 avalue => p_task_organization_name);
654 g_error_stage := '180';
655
656 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
657 itemkey => p_itemkey,
658 aname => 'TASK_ORGANIZATION_ID',
659 avalue => p_task_organization_id);
660 g_error_stage := '190';
661
662 wf_engine.SetItemAttrText( itemtype => p_itemtype,
663 itemkey => p_itemkey,
664 aname => 'TASK_SERVICE_TYPE',
665 avalue => p_task_service_type);
666 g_error_stage := '200';
667
668 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
669 itemkey => p_itemkey,
670 aname => 'TOP_TASK_ID',
671 avalue => p_top_task_id);
672 g_error_stage := '210';
673
674 wf_engine.SetItemAttrText( itemtype => p_itemtype,
675 itemkey => p_itemkey,
676 aname => 'TOP_TASK_NUMBER',
677 avalue => p_top_task_number);
678
679 /* Added for bug 1629411 */
680
681 g_error_stage := '220';
682
683 wf_engine.SetItemAttrText( itemtype => p_itemtype,
684 itemkey => p_itemkey,
685 aname => 'EXPENDITURE_ITEM_DATE',
686 avalue => to_char(p_expenditure_item_date));
687
688 reset_error_stack; /* Bug 5233487 */
689
690 EXCEPTION WHEN OTHERS THEN
691 /* Bug 5233487 - Start */
692 IF g_encoded_error_message IS NULL THEN
693 g_encoded_error_message := show_error(g_error_stack,g_error_stage,NULL);
694 END IF;
695 reset_error_stack;
696 /* Bug 5233487 - End */
697 g_error_message := SQLERRM;
698 RAISE;
699 END set_pa_item_attr;
700 ------------------- End pa_acc_gen_wf_pkg.Set_pa_item_attr --------------------
701
705 ----------------------------------------------------------------------
702 ----------------------------------------------------------------------
703 -- Procedure pa_acc_gen_wf_pkg.ap_er_generate_account
704 -- Definition of package body and function in package specifications
706
707 FUNCTION ap_er_generate_account
708 (
709 p_project_id IN pa_projects_all.project_id%TYPE,
710 p_task_id IN pa_tasks.task_id%TYPE,
711 p_expenditure_type IN pa_expenditure_types.expenditure_type%TYPE,
712 p_vendor_id IN po_vendors.vendor_id%type,
713 p_expenditure_organization_id IN hr_organization_units.organization_id%TYPE,
714 p_expenditure_item_date IN
715 pa_expenditure_items_all.expenditure_item_date%TYPE,
716 p_billable_flag IN pa_tasks.billable_flag%TYPE,
717 p_chart_of_accounts_id IN NUMBER,
718 p_calling_module IN VARCHAR2,
719 p_employee_id IN per_people_f.person_id%TYPE,
720 p_employee_ccid IN gl_code_combinations.code_combination_id%TYPE,
721 p_expense_type IN ap_expense_report_lines_all.web_parameter_id%TYPE,
722 p_expense_cc IN ap_expense_report_headers_all.flex_concatenated%TYPE,
723 p_attribute_category IN ap_expense_report_headers_all.attribute_category%TYPE,
724 p_attribute1 IN ap_expense_report_headers_all.attribute1%TYPE,
725 p_attribute2 IN ap_expense_report_headers_all.attribute2%TYPE,
726 p_attribute3 IN ap_expense_report_headers_all.attribute3%TYPE,
727 p_attribute4 IN ap_expense_report_headers_all.attribute4%TYPE,
728 p_attribute5 IN ap_expense_report_headers_all.attribute5%TYPE,
729 p_attribute6 IN ap_expense_report_headers_all.attribute6%TYPE,
730 p_attribute7 IN ap_expense_report_headers_all.attribute7%TYPE,
731 p_attribute8 IN ap_expense_report_headers_all.attribute8%TYPE,
732 p_attribute9 IN ap_expense_report_headers_all.attribute9%TYPE,
733 p_attribute10 IN ap_expense_report_headers_all.attribute10%TYPE,
734 p_attribute11 IN ap_expense_report_headers_all.attribute11%TYPE,
735 p_attribute12 IN ap_expense_report_headers_all.attribute12%TYPE,
736 p_attribute13 IN ap_expense_report_headers_all.attribute13%TYPE,
737 p_attribute14 IN ap_expense_report_headers_all.attribute14%TYPE,
738 p_attribute15 IN ap_expense_report_headers_all.attribute15%TYPE,
739 p_line_attribute_category IN ap_expense_report_lines_all.attribute_category%TYPE,
740 p_line_attribute1 IN ap_expense_report_lines_all.attribute1%TYPE,
741 p_line_attribute2 IN ap_expense_report_lines_all.attribute2%TYPE,
742 p_line_attribute3 IN ap_expense_report_lines_all.attribute3%TYPE,
743 p_line_attribute4 IN ap_expense_report_lines_all.attribute4%TYPE,
744 p_line_attribute5 IN ap_expense_report_lines_all.attribute5%TYPE,
745 p_line_attribute6 IN ap_expense_report_lines_all.attribute6%TYPE,
746 p_line_attribute7 IN ap_expense_report_lines_all.attribute7%TYPE,
747 p_line_attribute8 IN ap_expense_report_lines_all.attribute8%TYPE,
748 p_line_attribute9 IN ap_expense_report_lines_all.attribute9%TYPE,
749 p_line_attribute10 IN ap_expense_report_lines_all.attribute10%TYPE,
750 p_line_attribute11 IN ap_expense_report_lines_all.attribute11%TYPE,
751 p_line_attribute12 IN ap_expense_report_lines_all.attribute12%TYPE,
752 p_line_attribute13 IN ap_expense_report_lines_all.attribute13%TYPE,
753 p_line_attribute14 IN ap_expense_report_lines_all.attribute14%TYPE,
754 p_line_attribute15 IN ap_expense_report_lines_all.attribute15%TYPE,
755 p_input_ccid IN gl_code_combinations.code_combination_id%TYPE default null, /* Bug 5378579 */
756 x_return_ccid OUT NOCOPY gl_code_combinations.code_combination_id%TYPE,
757 x_concat_segs IN OUT NOCOPY VARCHAR2, -- Bug 5935019
758 x_concat_ids IN OUT NOCOPY VARCHAR2, -- Bug 5935019
759 x_concat_descrs IN OUT NOCOPY VARCHAR2, -- Bug 5935019
760 x_error_message OUT NOCOPY VARCHAR2,
761 /* R12 Changes Start - Added two new parameters Award_Id and Expenditure Item ID */
762 X_award_set_id IN NUMBER DEFAULT NULL,
763 p_award_id IN NUMBER DEFAULT NULL,
764 p_expenditure_item_id IN NUMBER DEFAULT NULL )
765 /* R12 Changes End */
766 RETURN BOOLEAN IS
767
768 l_itemtype CONSTANT VARCHAR2(30) := 'PAAPWEBX';
769 l_itemkey VARCHAR2(30);
770 l_result BOOLEAN;
771 l_concat_segs VARCHAR2(200);
772 l_concat_ids VARCHAR2(200);
773 l_concat_descrs VARCHAR2(500);
774 l_return_ccid gl_code_combinations.code_combination_id%TYPE;
775 l_employee_ccid gl_code_combinations.code_combination_id%TYPE;
776 l_class_code pa_class_codes.class_code%TYPE;
777 l_direct_flag pa_project_types_all.direct_flag%TYPE;
778 l_expenditure_category pa_expenditure_categories.expenditure_category%TYPE;
779 l_expenditure_organization_id hr_organization_units.organization_id%TYPE;
780 l_expenditure_org_name hr_organization_units.name%TYPE;
781 l_project_number pa_projects_all.segment1%TYPE;
785 l_public_sector_flag pa_projects_all.public_sector_flag%TYPE;
782 l_project_organization_name hr_organization_units.name%TYPE;
783 l_project_organization_id hr_organization_units.organization_id %TYPE;
784 l_project_type pa_project_types_all.project_type%TYPE;
786 l_revenue_category pa_expenditure_types.revenue_category_code%TYPE;
787 l_task_number pa_tasks.task_number%TYPE;
788 l_task_organization_name hr_organization_units.name%TYPE;
789 l_task_organization_id hr_organization_units.organization_id %TYPE;
790 l_task_service_type pa_tasks.service_type_code%TYPE;
791 l_top_task_id pa_tasks.task_id%TYPE;
792 l_top_task_number pa_tasks.task_number%TYPE;
793 l_employee_number per_people_f.employee_number%TYPE;
794 l_vendor_type po_vendors.vendor_type_lookup_code%TYPE;
795 l_error_message VARCHAR2(1000) := '';
796 l_org_id hr_organization_units.organization_id %TYPE; -- Workflow Enhancement
797
798 l_code_combination BOOLEAN;
799 l_person_type VARCHAR2(10);
800
801 /* R12 Changes Start */
802 /* Local variable used to set WF AWARD ID attribute */
803 l_award_id NUMBER;
804 /* R12 Changes End */
805
806 l_input_ccid gl_code_combinations.code_combination_id%TYPE; /* Bug 5378579 */
807
808 BEGIN
809 ---------------------------------------------------------------
810 -- Derive Organization id if employee id is present and
811 -- organization id is null
812 ---------------------------------------------------------------
813 set_error_stack('-->pa_acc_gen_wf_pkg.ap_er_generate_account'); /* Bug 5233487 */
814 g_encoded_error_message := NULL; /* Bug 5233487 */
815 g_error_stage := '10';
816 g_error_message := '';
817
818 IF p_expenditure_organization_id IS NULL
819 THEN
820 IF p_employee_id IS NOT NULL
821 THEN
822 l_expenditure_organization_id := pa_utils.GetEmpOrgId(p_employee_id,
823 nvl(p_expenditure_item_date,sysdate));
824 END IF;
825 ELSE
826 l_expenditure_organization_id := p_expenditure_organization_id;
827 END IF;
828 ---------------------------------------------------------------------
829 -- Call the procedure to obtain the derived parameters from the raw
830 -- parameters
831 ---------------------------------------------------------------------
832 g_error_stage := '20';
833 l_employee_ccid := p_employee_ccid;
834
835 pa_acc_gen_wf_pkg.wf_acc_derive_er_params
836 (
837 p_project_id => p_project_id,
838 p_task_id => p_task_id,
839 p_expenditure_type => p_expenditure_type,
840 p_vendor_id => p_vendor_id,
841 p_expenditure_organization_id => l_expenditure_organization_id,
842 p_expenditure_item_date => p_expenditure_item_date,
843 p_calling_module => p_calling_module,
844 p_employee_id => p_employee_id,
845 p_employee_ccid => l_employee_ccid,
846 p_expense_type => p_expense_type,
847 p_expense_cc => p_expense_cc,
848 x_class_code => l_class_code,
849 x_direct_flag => l_direct_flag,
850 x_expenditure_category => l_expenditure_category,
851 x_expenditure_org_name => l_expenditure_org_name,
852 x_project_number => l_project_number,
853 x_project_organization_name => l_project_organization_name,
854 x_project_organization_id => l_project_organization_id,
855 x_project_type => l_project_type,
856 x_public_sector_flag => l_public_sector_flag,
857 x_revenue_category => l_revenue_category,
858 x_task_number => l_task_number,
859 x_task_organization_name => l_task_organization_name,
860 x_task_organization_id => l_task_organization_id,
861 x_task_service_type => l_task_service_type,
862 x_top_task_id => l_top_task_id,
863 x_top_task_number => l_top_task_number,
864 x_employee_number => l_employee_number,
865 x_vendor_type => l_vendor_type,
866 x_person_type => l_person_type);
867
868 -------------------------------------
869 -- Call the FND initialize function
870 -------------------------------------
871 g_error_stage := '30';
872
873 l_itemkey := fnd_flex_workflow.initialize
874 (appl_short_name => 'SQLGL',
875 code => 'GL#',
876 num => p_chart_of_accounts_id,
877 itemtype =>l_itemtype);
878
879 ---------------------------------------------
880 -- Initialize the workflow item attributes
881 ---------------------------------------------
882 g_error_stage := '40';
883
884 pa_acc_gen_wf_pkg.Set_Pa_Item_Attr(
885 p_itemtype => l_itemtype,
886 p_itemkey => l_itemkey,
887 p_project_id => p_project_id,
888 p_task_id => p_task_id,
889 p_expenditure_type => p_expenditure_type,
890 p_expenditure_organization_id => l_expenditure_organization_id,
891 p_expenditure_item_date => p_expenditure_item_date,
892 p_billable_flag => p_billable_flag,
893 p_class_code => l_class_code,
894 p_direct_flag => l_direct_flag,
895 p_expenditure_category => l_expenditure_category,
899 p_project_organization_id => l_project_organization_id,
896 p_expenditure_org_name => l_expenditure_org_name,
897 p_project_number => l_project_number,
898 p_project_organization_name => l_project_organization_name,
900 p_project_type => l_project_type,
901 p_public_sector_flag => l_public_sector_flag,
902 p_revenue_category => l_revenue_category,
903 p_task_number => l_task_number,
904 p_task_organization_name => l_task_organization_name,
905 p_task_organization_id => l_task_organization_id,
906 p_task_service_type => l_task_service_type,
907 p_top_task_id => l_top_task_id,
908 p_top_task_number => l_top_task_number);
909
910 g_error_stage := '50';
911
912 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
913 itemkey => l_itemkey,
914 aname => 'VENDOR_ID',
915 avalue => p_vendor_id);
916
917 /* R12 Changes Start - Commented for R12; workflow will use award id instead
918 of award set id
919 g_error_stage := '55';
920 -- ---------------------------------------------------------------
921 -- OGM_0.0 : Vertical application OGM may use award_set_id to
922 -- derive award_id, which can be used to derive segments for
923 -- the account generator.
924 -- ---------------------------------------------------------------
925 IF x_award_set_id is not NULL THEN
926 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
927 itemkey => l_itemkey,
928 aname => 'AWARD_SET_ID',
929 avalue => x_award_set_id);
930 END IF ;
931 R12 Changes End */
932
933 g_error_stage := '60';
934
935 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
936 itemkey => l_itemkey,
937 aname => 'EMPLOYEE_ID',
938 avalue => p_employee_id);
939 g_error_stage := '70';
940
941 wf_engine.SetItemAttrText( itemtype => l_itemtype,
942 itemkey => l_itemkey,
943 aname => 'EMPLOYEE_NUMBER',
944 avalue => l_employee_number);
945
946 g_error_stage := '71';
947
948 wf_engine.SetItemAttrText( itemtype => l_itemtype,
949 itemkey => l_itemkey,
950 aname => 'PERSON_TYPE',
951 avalue => l_person_type);
952
953 g_error_stage := '80';
954
955 wf_engine.SetItemAttrText( itemtype => l_itemtype,
956 itemkey => l_itemkey,
957 aname => 'VENDOR_TYPE',
958 avalue => l_vendor_type);
959 g_error_stage := '90';
960
961 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
962 itemkey => l_itemkey,
963 aname => 'CHART_OF_ACCOUNTS_ID',
964 avalue => p_chart_of_accounts_id);
965 g_error_stage := '100';
966
967 wf_engine.SetItemAttrText( itemtype => l_itemtype,
968 itemkey => l_itemkey,
969 aname => 'CALLING_MODULE',
970 avalue => p_calling_module);
971 g_error_stage := '110';
972
973 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
974 itemkey => l_itemkey,
975 aname => 'EMPLOYEE_CCID',
976 avalue => l_employee_ccid);
977 g_error_stage := '120';
978
979 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
980 itemkey => l_itemkey,
981 aname => 'EXPENSE_TYPE',
982 avalue => p_expense_type);
983 g_error_stage := '130';
984
985 wf_engine.SetItemAttrText( itemtype => l_itemtype,
986 itemkey => l_itemkey,
987 aname => 'EXPENSE_CC',
988 avalue => p_expense_cc);
989 g_error_stage := '140';
990
991 IF p_attribute_category IS NOT NULL
992 THEN
993 wf_engine.SetItemAttrText( itemtype => l_itemtype,
994 itemkey => l_itemkey,
995 aname => 'ATTRIBUTE_CATEGORY',
996 avalue => p_attribute_category);
997 END IF;
998
999 g_error_stage := '150';
1000
1001 IF p_attribute1 IS NOT NULL
1002 THEN
1003 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1004 itemkey => l_itemkey,
1005 aname => 'ATTRIBUTE1',
1006 avalue => p_attribute1);
1007 END IF;
1008
1009 g_error_stage := '160';
1010
1011 IF p_attribute2 IS NOT NULL
1012 THEN
1013 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1014 itemkey => l_itemkey,
1015 aname => 'ATTRIBUTE2',
1016 avalue => p_attribute2);
1017 END IF;
1018
1019
1020 g_error_stage := '170';
1021
1022 IF p_attribute3 IS NOT NULL
1023 THEN
1024 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1025 itemkey => l_itemkey,
1026 aname => 'ATTRIBUTE3',
1027 avalue => p_attribute3);
1028 END IF;
1029
1030 g_error_stage := '180';
1031
1032 IF p_attribute4 IS NOT NULL
1036 aname => 'ATTRIBUTE4',
1033 THEN
1034 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1035 itemkey => l_itemkey,
1037 avalue => p_attribute4);
1038 END IF;
1039
1040 g_error_stage := '190';
1041
1042 IF p_attribute5 IS NOT NULL
1043 THEN
1044 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1045 itemkey => l_itemkey,
1046 aname => 'ATTRIBUTE5',
1047 avalue => p_attribute5);
1048 END IF;
1049
1050 g_error_stage := '200';
1051
1052 IF p_attribute6 IS NOT NULL
1053 THEN
1054 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1055 itemkey => l_itemkey,
1056 aname => 'ATTRIBUTE6',
1057 avalue => p_attribute6);
1058 END IF;
1059
1060 g_error_stage := '210';
1061
1062 IF p_attribute7 IS NOT NULL
1063 THEN
1064 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1065 itemkey => l_itemkey,
1066 aname => 'ATTRIBUTE7',
1067 avalue => p_attribute7);
1068 END IF;
1069 g_error_stage := '220';
1070
1071 IF p_attribute8 IS NOT NULL
1072 THEN
1073 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1074 itemkey => l_itemkey,
1075 aname => 'ATTRIBUTE8',
1076 avalue => p_attribute8);
1077 END IF;
1078 g_error_stage := '230';
1079
1080 IF p_attribute9 IS NOT NULL
1081 THEN
1082 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1083 itemkey => l_itemkey,
1084 aname => 'ATTRIBUTE9',
1085 avalue => p_attribute9);
1086 END IF;
1087 g_error_stage := '240';
1088
1089 IF p_attribute10 IS NOT NULL
1090 THEN
1091 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1092 itemkey => l_itemkey,
1093 aname => 'ATTRIBUTE10',
1094 avalue => p_attribute10);
1095 END IF;
1096 g_error_stage := '250';
1097
1098 IF p_attribute11 IS NOT NULL
1099 THEN
1100 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1101 itemkey => l_itemkey,
1102 aname => 'ATTRIBUTE11',
1103 avalue => p_attribute11);
1104 END IF;
1105 g_error_stage := '260';
1106
1107 IF p_attribute12 IS NOT NULL
1108 THEN
1109 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1110 itemkey => l_itemkey,
1111 aname => 'ATTRIBUTE12',
1112 avalue => p_attribute12);
1113 END IF;
1114 g_error_stage := '270';
1115
1116 IF p_attribute13 IS NOT NULL
1117 THEN
1118 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1119 itemkey => l_itemkey,
1120 aname => 'ATTRIBUTE13',
1121 avalue => p_attribute13);
1122 END IF;
1123 g_error_stage := '280';
1124
1125 IF p_attribute14 IS NOT NULL
1126 THEN
1127 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1128 itemkey => l_itemkey,
1129 aname => 'ATTRIBUTE14',
1130 avalue => p_attribute14);
1131 END IF;
1132 g_error_stage := '290';
1133
1134 IF p_attribute15 IS NOT NULL
1135 THEN
1136 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1137 itemkey => l_itemkey,
1138 aname => 'ATTRIBUTE15',
1139 avalue => p_attribute15);
1140 END IF;
1141 g_error_stage := '300';
1142
1143 IF p_line_attribute1 IS NOT NULL
1144 THEN
1145 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1146 itemkey => l_itemkey,
1147 aname => 'LINE_ATTRIBUTE1',
1148 avalue => p_line_attribute1);
1149
1150 END IF;
1151 g_error_stage := '310';
1152
1153 IF p_line_attribute2 IS NOT NULL
1154 THEN
1155 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1156 itemkey => l_itemkey,
1157 aname => 'LINE_ATTRIBUTE2',
1158 avalue => p_line_attribute2);
1159
1160 END IF;
1161 g_error_stage := '320';
1162
1163 IF p_line_attribute3 IS NOT NULL
1164 THEN
1165 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1166 itemkey => l_itemkey,
1167 aname => 'LINE_ATTRIBUTE3',
1168 avalue => p_line_attribute3);
1169
1170 END IF;
1171 g_error_stage := '330';
1172
1173 IF p_line_attribute4 IS NOT NULL
1174 THEN
1175 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1176 itemkey => l_itemkey,
1177 aname => 'LINE_ATTRIBUTE4',
1178 avalue => p_line_attribute4);
1179
1180 END IF;
1181 g_error_stage := '340';
1182
1183 IF p_line_attribute5 IS NOT NULL
1184 THEN
1185 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1186 itemkey => l_itemkey,
1187 aname => 'LINE_ATTRIBUTE5',
1188 avalue => p_line_attribute5);
1189
1190 END IF;
1191 g_error_stage := '350';
1192
1193 IF p_line_attribute6 IS NOT NULL
1194 THEN
1195 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1196 itemkey => l_itemkey,
1197 aname => 'LINE_ATTRIBUTE6',
1198 avalue => p_line_attribute6);
1199
1200 END IF;
1201 g_error_stage := '360';
1202
1203 IF p_line_attribute7 IS NOT NULL
1204 THEN
1208 avalue => p_line_attribute7);
1205 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1206 itemkey => l_itemkey,
1207 aname => 'LINE_ATTRIBUTE7',
1209
1210 END IF;
1211 g_error_stage := '370';
1212
1213 IF p_line_attribute8 IS NOT NULL
1214 THEN
1215 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1216 itemkey => l_itemkey,
1217 aname => 'LINE_ATTRIBUTE8',
1218 avalue => p_line_attribute8);
1219
1220 END IF;
1221 g_error_stage := '380';
1222
1223 IF p_line_attribute9 IS NOT NULL
1224 THEN
1225 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1226 itemkey => l_itemkey,
1227 aname => 'LINE_ATTRIBUTE9',
1228 avalue => p_line_attribute9);
1229
1230 END IF;
1231 g_error_stage := '390';
1232
1233 IF p_line_attribute10 IS NOT NULL
1234 THEN
1235 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1236 itemkey => l_itemkey,
1237 aname => 'LINE_ATTRIBUTE10',
1238 avalue => p_line_attribute10);
1239
1240 END IF;
1241 g_error_stage := '400';
1242
1243 IF p_line_attribute11 IS NOT NULL
1244 THEN
1245 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1246 itemkey => l_itemkey,
1247 aname => 'LINE_ATTRIBUTE11',
1248 avalue => p_line_attribute11);
1249
1250 END IF;
1251 g_error_stage := '410';
1252
1253 IF p_line_attribute12 IS NOT NULL
1254 THEN
1255 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1256 itemkey => l_itemkey,
1257 aname => 'LINE_ATTRIBUTE12',
1258 avalue => p_line_attribute12);
1259
1260 END IF;
1261 g_error_stage := '420';
1262
1263 IF p_line_attribute13 IS NOT NULL
1264 THEN
1265 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1266 itemkey => l_itemkey,
1267 aname => 'LINE_ATTRIBUTE13',
1268 avalue => p_line_attribute13);
1269
1270 END IF;
1271 g_error_stage := '430';
1272
1273 IF p_line_attribute14 IS NOT NULL
1274 THEN
1275 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1276 itemkey => l_itemkey,
1277 aname => 'LINE_ATTRIBUTE14',
1278 avalue => p_line_attribute14);
1279
1280 END IF;
1281 g_error_stage := '440';
1282
1283 IF p_line_attribute15 IS NOT NULL
1284 THEN
1285 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1286 itemkey => l_itemkey,
1287 aname => 'LINE_ATTRIBUTE15',
1288 avalue => p_line_attribute15);
1289
1290 END IF;
1291 g_error_stage := '450';
1292
1293 IF p_line_attribute_category IS NOT NULL
1294 THEN
1295 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1296 itemkey => l_itemkey,
1297 aname => 'LINE_ATTRIBUTE_CATEGORY',
1298 avalue => p_line_attribute_category);
1299 END IF;
1300
1301 --- Following section has been added as a part of enhancement request where
1302 --- Users can have workflow(PAAPINVW) setup accross orgs.
1303 --- Workflow Enhancement
1304
1305 BEGIN
1306 SELECT org_id
1307 INTO l_org_id
1308 FROM PA_IMPLEMENTATIONS;
1309 END;
1310
1311 g_error_stage := '451';
1312
1313 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
1314 itemkey => l_itemkey,
1315 aname => 'ORG_ID',
1316 avalue => l_org_id);
1317
1318 /* Bug 5935019 - Changes start */
1319 -- We cannot directly assign this to attribute
1320 -- 'FND_FLEX_SEGMENTS' as workflow will take care of this.
1321 IF x_concat_segs IS NOT NULL THEN
1322 g_error_stage := '382';
1323
1324 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1325 itemkey => l_itemkey,
1326 aname => 'CONCAT_SEGMENTS',
1327 avalue => x_concat_segs);
1328 END IF;
1329 /* Bug 5935019 - Changes end */
1330
1331 /* R12 Changes Start - Setting attributes Award ID and Expenditure Item ID
1332 for Workflow */
1333 g_error_stage := '452';
1334
1335 IF x_award_set_id IS NOT NULL THEN
1336
1337 l_award_id := PA_GMS_API.VERT_GET_AWARD_ID(x_award_set_id,NULL,NULL);
1338
1339 ELSE
1340
1341 l_award_id := p_award_id;
1342
1343 END IF;
1344
1345 IF l_award_id IS NOT NULL THEN
1346
1347 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
1348 itemkey => l_itemkey,
1349 aname => 'AWARD_ID',
1350 avalue => l_award_id);
1351
1352 END IF;
1353
1354 g_error_stage := '453';
1355
1356 IF p_expenditure_item_id IS NOT NULL THEN
1357
1358 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
1359 itemkey => l_itemkey,
1360 aname => 'EXPENDITURE_ITEM_ID',
1361 avalue => p_expenditure_item_id);
1362
1363 END IF;
1364 /* R12 Changes End */
1365
1366 /* Bug 5378579 - Start */
1367 g_error_stage := '454';
1368
1369 IF p_input_ccid IS NULL THEN
1370 l_input_ccid := 0;
1371 ELSE
1372 l_input_ccid := p_input_ccid;
1373 END IF;
1374
1375 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
1376 itemkey => l_itemkey,
1377 aname => 'DIST_CODE_COMBINATION_ID',
1381 -----------------------------------------------------------
1378 avalue => l_input_ccid);
1379 /* Bug 5378579 - End */
1380
1382 -- Call the workflow Generate function to trigger off the
1383 -- workflow account generation
1384 -----------------------------------------------------------
1385 g_error_stage := '460';
1386
1387 l_result := fnd_flex_workflow.generate( l_itemtype,
1388 l_itemkey,
1389 TRUE,
1390 l_return_ccid,
1391 x_concat_segs, -- Bug 5935019
1392 x_concat_ids, -- Bug 5935019
1393 x_concat_descrs, -- Bug 5935019
1394 l_error_message,
1395 l_code_combination);
1396
1397 ---Added this section for Workflow error handling.
1398 -- Workflow Enhancement
1399 /* IF (l_result and ( l_return_ccid is null or l_return_ccid = 0 or l_return_ccid = -1 )) OR
1400 (NOT l_result) THEN ** commenting this out for bug 2802847 */
1401
1402 /* Added the check for l_error_message for bug 2694601 */
1403 IF l_error_message is null THEN
1404 fnd_message.set_name('PA','PA_WF_SETUP_ERROR');
1405 x_error_message := fnd_message.get_encoded;
1406 ELSE
1407 x_error_message := l_error_message;
1408 END IF;
1409 /* 2694601 */
1410
1411 /* return(FALSE);
1412 END IF; ** bug 2802847 */
1413
1414 ------------------------------------------------------------------
1415 -- Copy the return values to the corresponding output parameters
1416 ------------------------------------------------------------------
1417 /* Bug 5935019 - Changes start -- commented 3 lines
1418 x_concat_segs := l_concat_segs;
1419 x_concat_ids := l_concat_ids;
1420 x_concat_descrs:= l_concat_descrs;
1421 Bug 5935019 - Changes start */
1422 -- x_error_message:= l_error_message; bug 2802847
1423 x_return_ccid := l_return_ccid;
1424
1425 -------------------------------------------------------------------
1426 -- Reset the error stack.
1427 -------------------------------------------------------------------
1428
1429 reset_error_stack;
1430
1431 --------------------------------------------------------------------
1432 -- Return the return value of the Generate function as the return
1433 -- value for the process
1434 --------------------------------------------------------------------
1435
1436 RETURN l_result;
1437
1438 EXCEPTION
1439
1440 WHEN OTHERS
1441 THEN
1442
1443 -----------------------------------------------------------------------
1444 -- Record error using generic error message routine for debugging and
1445 -- raise it
1446 -----------------------------------------------------------------------
1447 /* Bug 5233487 - Start */
1448 IF g_encoded_error_message IS NULL THEN
1449 g_encoded_error_message := show_error(g_error_stack,g_error_stage,NULL);
1450 END IF;
1451 x_error_message := g_encoded_error_message;
1452 reset_error_stack;
1453 /* Bug 5233487 - End */
1454
1455 wf_core.context( pkg_name => 'pa_acc_gen_wf_pkg',
1456 proc_name => 'ap_er_generate_account',
1457 arg1 => 'Project id: '||p_project_id,
1458 arg2 => 'Task id: '||p_task_id,
1459 arg3 => 'Vendor id: '||p_vendor_id,
1460 arg4 => 'Exp type: '||p_expenditure_type,
1461 arg5 => 'Exp Org id: '||p_expenditure_organization_id);
1462
1463 RETURN FALSE;
1464
1465 END ap_er_generate_account;
1466
1467 ----------------------------------------------------------------------
1468 -- Procedure pa_acc_gen_wf_pkg.ap_inv_generate_account
1469 -- Definition of package body and function in package specifications
1470 ----------------------------------------------------------------------
1471
1472 FUNCTION ap_inv_generate_account
1473 (
1474 p_project_id IN pa_projects_all.project_id%TYPE,
1475 p_task_id IN pa_tasks.task_id%TYPE,
1476 p_expenditure_type IN pa_expenditure_types.expenditure_type%TYPE,
1477 p_vendor_id IN po_vendors.vendor_id%type,
1478 p_expenditure_organization_id IN hr_organization_units.organization_id%TYPE,
1479 p_expenditure_item_date IN
1480 pa_expenditure_items_all.expenditure_item_date%TYPE,
1481 p_billable_flag IN pa_tasks.billable_flag%TYPE,
1482 p_chart_of_accounts_id IN NUMBER,
1483 p_attribute_category IN ap_invoices_all.attribute_category%TYPE,
1484 p_attribute1 IN ap_invoices_all.attribute1%TYPE,
1485 p_attribute2 IN ap_invoices_all.attribute2%TYPE,
1486 p_attribute3 IN ap_invoices_all.attribute3%TYPE,
1487 p_attribute4 IN ap_invoices_all.attribute4%TYPE,
1488 p_attribute5 IN ap_invoices_all.attribute5%TYPE,
1489 p_attribute6 IN ap_invoices_all.attribute6%TYPE,
1490 p_attribute7 IN ap_invoices_all.attribute7%TYPE,
1491 p_attribute8 IN ap_invoices_all.attribute8%TYPE,
1492 p_attribute9 IN ap_invoices_all.attribute9%TYPE,
1493 p_attribute10 IN ap_invoices_all.attribute10%TYPE,
1494 p_attribute11 IN ap_invoices_all.attribute11%TYPE,
1495 p_attribute12 IN ap_invoices_all.attribute12%TYPE,
1496 p_attribute13 IN ap_invoices_all.attribute13%TYPE,
1497 p_attribute14 IN ap_invoices_all.attribute14%TYPE,
1498 p_attribute15 IN ap_invoices_all.attribute15%TYPE,
1499 p_dist_attribute_category IN
1503 p_dist_attribute3 IN ap_invoice_distributions_all.attribute3%TYPE,
1500 ap_invoice_distributions_all.attribute_category%TYPE,
1501 p_dist_attribute1 IN ap_invoice_distributions_all.attribute1%TYPE,
1502 p_dist_attribute2 IN ap_invoice_distributions_all.attribute2%TYPE,
1504 p_dist_attribute4 IN ap_invoice_distributions_all.attribute4%TYPE,
1505 p_dist_attribute5 IN ap_invoice_distributions_all.attribute5%TYPE,
1506 p_dist_attribute6 IN ap_invoice_distributions_all.attribute6%TYPE,
1507 p_dist_attribute7 IN ap_invoice_distributions_all.attribute7%TYPE,
1508 p_dist_attribute8 IN ap_invoice_distributions_all.attribute8%TYPE,
1509 p_dist_attribute9 IN ap_invoice_distributions_all.attribute9%TYPE,
1510 p_dist_attribute10 IN ap_invoice_distributions_all.attribute10%TYPE,
1511 p_dist_attribute11 IN ap_invoice_distributions_all.attribute11%TYPE,
1512 p_dist_attribute12 IN ap_invoice_distributions_all.attribute12%TYPE,
1513 p_dist_attribute13 IN ap_invoice_distributions_all.attribute13%TYPE,
1514 p_dist_attribute14 IN ap_invoice_distributions_all.attribute14%TYPE,
1515 p_dist_attribute15 IN ap_invoice_distributions_all.attribute15%TYPE,
1516 /* Adding parameter p_input_ccid for bug 2348764 */
1517 p_input_ccid IN gl_code_combinations.code_combination_id%TYPE default NULL,
1518 x_return_ccid OUT NOCOPY gl_code_combinations.code_combination_id%TYPE,
1519 x_concat_segs IN OUT NOCOPY VARCHAR2, -- Bug 5935019
1520 x_concat_ids IN OUT NOCOPY VARCHAR2, -- Bug 5935019
1521 x_concat_descrs IN OUT NOCOPY VARCHAR2, -- Bug 5935019
1522 x_error_message OUT NOCOPY VARCHAR2,
1523 X_award_set_id IN NUMBER DEFAULT NULL,
1524 /* R12 Changes Start - Added two new parameters Award_Id and Expenditure Item ID */
1525 p_accounting_date IN ap_invoice_distributions_all.accounting_date%TYPE default NULL,
1526 p_award_id IN NUMBER DEFAULT NULL,
1527 p_expenditure_item_id IN NUMBER DEFAULT NULL )
1528 /* R12 Changes End */
1529
1530 RETURN BOOLEAN IS
1531 l_itemtype CONSTANT VARCHAR2(30) := 'PAAPINVW';
1532 l_itemkey VARCHAR2(30);
1533 l_result BOOLEAN;
1534 l_concat_segs VARCHAR2(200);
1535 l_concat_ids VARCHAR2(200);
1536 l_concat_descrs VARCHAR2(500);
1537 /* Adding parameter l_input_ccid for bug 2348764 */
1538 l_input_ccid gl_code_combinations.code_combination_id%TYPE;
1539 l_return_ccid gl_code_combinations.code_combination_id%TYPE;
1540 l_class_code pa_class_codes.class_code%TYPE;
1541 l_direct_flag pa_project_types_all.direct_flag%TYPE;
1542 l_expenditure_category pa_expenditure_categories.expenditure_category%TYPE;
1543 l_expenditure_org_name hr_organization_units.name%TYPE;
1544 l_project_number pa_projects_all.segment1%TYPE;
1545 l_project_organization_name hr_organization_units.name%TYPE;
1546 l_project_organization_id hr_organization_units.organization_id %TYPE;
1547 l_project_type pa_project_types_all.project_type%TYPE;
1548 l_public_sector_flag pa_projects_all.public_sector_flag%TYPE;
1549 l_revenue_category pa_expenditure_types.revenue_category_code%TYPE;
1550 l_task_number pa_tasks.task_number%TYPE;
1551 l_task_organization_name hr_organization_units.name%TYPE;
1552 l_task_organization_id hr_organization_units.organization_id %TYPE;
1553 l_task_service_type pa_tasks.service_type_code%TYPE;
1554 l_top_task_id pa_tasks.task_id%TYPE;
1555 l_top_task_number pa_tasks.task_number%TYPE;
1556 l_vendor_employee_id per_people_f.person_id%TYPE;
1557 l_vendor_employee_number per_people_f.employee_number%TYPE;
1558 l_vendor_type po_vendors.vendor_type_lookup_code%TYPE;
1559 l_error_message VARCHAR2(1000) :='';
1560 l_accounting_date DATE;
1561 l_org_id hr_organization_units.organization_id %TYPE; -- Workflow Enhancement
1562
1563 l_code_combination BOOLEAN;
1564
1565 /* R12 Changes Start */
1566 /* Local variable used to set WF AWARD ID attribute */
1567 l_award_id NUMBER;
1568 /* R12 Changes End */
1569
1570 BEGIN
1571 ---------------------------------------------------------------------
1572 -- Call the procedure to obtain the derived parameters from the raw
1573 -- parameters
1574 ---------------------------------------------------------------------
1575 set_error_stack('-->pa_acc_gen_wf_pkg.ap_inv_generate_account'); /* Bug 5233487 */
1576 g_encoded_error_message := NULL; /* Bug 5233487 */
1577 g_error_stage := '10';
1578 g_error_message := '';
1579
1580 pa_acc_gen_wf_pkg.wf_acc_derive_params
1581 (
1582 p_project_id => p_project_id,
1583 p_task_id => p_task_id,
1584 p_expenditure_type => p_expenditure_type,
1585 p_vendor_id => p_vendor_id,
1586 p_expenditure_organization_id => p_expenditure_organization_id,
1587 p_expenditure_item_date => p_expenditure_item_date,
1588 x_class_code => l_class_code,
1589 x_direct_flag => l_direct_flag,
1590 x_expenditure_category => l_expenditure_category,
1591 x_expenditure_org_name => l_expenditure_org_name,
1592 x_project_number => l_project_number,
1593 x_project_organization_name => l_project_organization_name,
1594 x_project_organization_id => l_project_organization_id,
1595 x_project_type => l_project_type,
1596 x_public_sector_flag => l_public_sector_flag,
1597 x_revenue_category => l_revenue_category,
1598 x_task_number => l_task_number,
1602 x_top_task_id => l_top_task_id,
1599 x_task_organization_name => l_task_organization_name,
1600 x_task_organization_id => l_task_organization_id,
1601 x_task_service_type => l_task_service_type,
1603 x_top_task_number => l_top_task_number,
1604 x_vendor_employee_id => l_vendor_employee_id,
1605 x_vendor_employee_number => l_vendor_employee_number,
1606 x_vendor_type => l_vendor_type);
1607
1608 -------------------------------------
1609 -- Call the FND initialize function
1610 -------------------------------------
1611 g_error_stage := '20';
1612
1613 l_itemkey := fnd_flex_workflow.initialize
1614 (appl_short_name => 'SQLGL',
1615 code => 'GL#',
1616 num => p_chart_of_accounts_id,
1617 itemtype =>l_itemtype);
1618
1619 ---------------------------------------------
1620 -- Initialize the workflow item attributes
1621 ---------------------------------------------
1622 g_error_stage := '30';
1623
1624 pa_acc_gen_wf_pkg.Set_Pa_Item_Attr(
1625 p_itemtype => l_itemtype,
1626 p_itemkey => l_itemkey,
1627 p_project_id => p_project_id,
1628 p_task_id => p_task_id,
1629 p_expenditure_type => p_expenditure_type,
1630 p_expenditure_organization_id => p_expenditure_organization_id,
1631 p_expenditure_item_date => p_expenditure_item_date,
1632 p_billable_flag => p_billable_flag,
1633 p_class_code => l_class_code,
1634 p_direct_flag => l_direct_flag,
1635 p_expenditure_category => l_expenditure_category,
1636 p_expenditure_org_name => l_expenditure_org_name,
1637 p_project_number => l_project_number,
1638 p_project_organization_name => l_project_organization_name,
1639 p_project_organization_id => l_project_organization_id,
1640 p_project_type => l_project_type,
1641 p_public_sector_flag => l_public_sector_flag,
1642 p_revenue_category => l_revenue_category,
1643 p_task_number => l_task_number,
1644 p_task_organization_name => l_task_organization_name,
1645 p_task_organization_id => l_task_organization_id,
1646 p_task_service_type => l_task_service_type,
1647 p_top_task_id => l_top_task_id,
1648 p_top_task_number => l_top_task_number);
1649
1650 g_error_stage := '40';
1651
1652 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
1653 itemkey => l_itemkey,
1654 aname => 'VENDOR_ID',
1655 avalue => p_vendor_id);
1656
1657 /*Added for bug2100489 */
1658
1659 g_error_stage :='45';
1660
1661 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1662 itemkey => l_itemkey,
1663 aname => 'ACCOUNTING_DATE',
1664 avalue => to_char(NVL(p_accounting_date,sysdate)));
1665
1666 g_error_stage := '50';
1667
1668 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
1669 itemkey => l_itemkey,
1670 aname => 'VENDOR_EMPLOYEE_ID',
1671 avalue => l_vendor_employee_id);
1672
1673 /* R12 changes Start - Commented for R12; workflow will use award id instead
1674 of award set id
1675 g_error_stage := '55';
1676 -- ---------------------------------------------------------------
1677 -- OGM_0.0 : Vertical application OGM may use award_set_id to
1678 -- derive award_id, which can be used to derive segments for
1679 -- the account generator.
1680 -- ---------------------------------------------------------------
1681 IF x_award_set_id is not NULL THEN
1682
1683 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
1684 itemkey => l_itemkey,
1685 aname => 'AWARD_SET_ID',
1686 avalue => x_award_set_id);
1687 END IF ;
1688 R12 Changes End */
1689
1690 g_error_stage := '60';
1691
1692 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1693 itemkey => l_itemkey,
1694 aname => 'VENDOR_EMPLOYEE_NUMBER',
1695 avalue => l_vendor_employee_number);
1696
1697 g_error_stage := '70';
1698
1699 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1700 itemkey => l_itemkey,
1701 aname => 'VENDOR_TYPE',
1702 avalue => l_vendor_type);
1703
1704 g_error_stage := '80';
1705
1706 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
1707 itemkey => l_itemkey,
1708 aname => 'CHART_OF_ACCOUNTS_ID',
1709 avalue => p_chart_of_accounts_id);
1710
1711 IF p_attribute_category IS NOT NULL
1712 THEN
1713 g_error_stage := '90';
1714
1715 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1716 itemkey => l_itemkey,
1717 aname => 'ATTRIBUTE_CATEGORY',
1718 avalue => p_attribute_category);
1719 END IF;
1720
1721 IF p_attribute1 IS NOT NULL
1722 THEN
1723 g_error_stage := '100';
1724
1725 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1726 itemkey => l_itemkey,
1727 aname => 'ATTRIBUTE1',
1728 avalue => p_attribute1);
1729 END IF;
1730
1731 IF p_attribute2 IS NOT NULL
1732 THEN
1736 itemkey => l_itemkey,
1733 g_error_stage := '110';
1734
1735 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1737 aname => 'ATTRIBUTE2',
1738 avalue => p_attribute2);
1739 END IF;
1740
1741 IF p_attribute3 IS NOT NULL
1742 THEN
1743 g_error_stage := '120';
1744
1745 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1746 itemkey => l_itemkey,
1747 aname => 'ATTRIBUTE3',
1748 avalue => p_attribute3);
1749 END IF;
1750
1751 IF p_attribute4 IS NOT NULL
1752 THEN
1753 g_error_stage := '130';
1754
1755 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1756 itemkey => l_itemkey,
1757 aname => 'ATTRIBUTE4',
1758 avalue => p_attribute4);
1759 END IF;
1760
1761 IF p_attribute5 IS NOT NULL
1762 THEN
1763 g_error_stage := '140';
1764
1765 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1766 itemkey => l_itemkey,
1767 aname => 'ATTRIBUTE5',
1768 avalue => p_attribute5);
1769 END IF;
1770
1771 IF p_attribute6 IS NOT NULL
1772 THEN
1773 g_error_stage := '150';
1774
1775 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1776 itemkey => l_itemkey,
1777 aname => 'ATTRIBUTE6',
1778 avalue => p_attribute6);
1779 END IF;
1780
1781 IF p_attribute7 IS NOT NULL
1782 THEN
1783 g_error_stage := '160';
1784
1785 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1786 itemkey => l_itemkey,
1787 aname => 'ATTRIBUTE7',
1788 avalue => p_attribute7);
1789 END IF;
1790
1791 IF p_attribute8 IS NOT NULL
1792 THEN
1793 g_error_stage := '170';
1794
1795 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1796 itemkey => l_itemkey,
1797 aname => 'ATTRIBUTE8',
1798 avalue => p_attribute8);
1799 END IF;
1800
1801 IF p_attribute9 IS NOT NULL
1802 THEN
1803 g_error_stage := '180';
1804
1805 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1806 itemkey => l_itemkey,
1807 aname => 'ATTRIBUTE9',
1808 avalue => p_attribute9);
1809 END IF;
1810
1811 IF p_attribute10 IS NOT NULL
1812 THEN
1813 g_error_stage := '190';
1814
1815 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1816 itemkey => l_itemkey,
1817 aname => 'ATTRIBUTE10',
1818 avalue => p_attribute10);
1819 END IF;
1820
1821 IF p_attribute11 IS NOT NULL
1822 THEN
1823 g_error_stage := '200';
1824
1825 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1826 itemkey => l_itemkey,
1827 aname => 'ATTRIBUTE11',
1828 avalue => p_attribute11);
1829 END IF;
1830
1831 IF p_attribute12 IS NOT NULL
1832 THEN
1833 g_error_stage := '210';
1834
1835 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1836 itemkey => l_itemkey,
1837 aname => 'ATTRIBUTE12',
1838 avalue => p_attribute12);
1839 END IF;
1840
1841 IF p_attribute13 IS NOT NULL
1842 THEN
1843 g_error_stage := '220';
1844
1845 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1846 itemkey => l_itemkey,
1847 aname => 'ATTRIBUTE13',
1848 avalue => p_attribute13);
1849 END IF;
1850
1851 IF p_attribute14 IS NOT NULL
1852 THEN
1853 g_error_stage := '230';
1854
1855 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1856 itemkey => l_itemkey,
1857 aname => 'ATTRIBUTE14',
1858 avalue => p_attribute14);
1859 END IF;
1860
1861 IF p_attribute15 IS NOT NULL
1862 THEN
1863 g_error_stage := '240';
1864
1865 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1866 itemkey => l_itemkey,
1867 aname => 'ATTRIBUTE15',
1868 avalue => p_attribute15);
1869 END IF;
1870
1871 IF p_dist_attribute1 IS NOT NULL
1872 THEN
1873 g_error_stage := '250';
1874
1875 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1876 itemkey => l_itemkey,
1877 aname => 'DIST_ATTRIBUTE1',
1878 avalue => p_dist_attribute1);
1879
1880 END IF;
1881
1882 IF p_dist_attribute2 IS NOT NULL
1883 THEN
1884 g_error_stage := '260';
1885
1886 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1887 itemkey => l_itemkey,
1888 aname => 'DIST_ATTRIBUTE2',
1889 avalue => p_dist_attribute2);
1890
1891 END IF;
1892
1893 IF p_dist_attribute3 IS NOT NULL
1894 THEN
1895 g_error_stage := '270';
1896
1897 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1898 itemkey => l_itemkey,
1899 aname => 'DIST_ATTRIBUTE3',
1900 avalue => p_dist_attribute3);
1901
1902 END IF;
1903
1904 IF p_dist_attribute4 IS NOT NULL
1905 THEN
1906 g_error_stage := '280';
1907
1908 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1909 itemkey => l_itemkey,
1910 aname => 'DIST_ATTRIBUTE4',
1911 avalue => p_dist_attribute4);
1912
1913 END IF;
1914
1915 IF p_dist_attribute5 IS NOT NULL
1916 THEN
1920 itemkey => l_itemkey,
1917 g_error_stage := '290';
1918
1919 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1921 aname => 'DIST_ATTRIBUTE5',
1922 avalue => p_dist_attribute5);
1923
1924 END IF;
1925
1926 IF p_dist_attribute6 IS NOT NULL
1927 THEN
1928 g_error_stage := '280';
1929
1930 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1931 itemkey => l_itemkey,
1932 aname => 'DIST_ATTRIBUTE6',
1933 avalue => p_dist_attribute6);
1934
1935 END IF;
1936
1937 IF p_dist_attribute7 IS NOT NULL
1938 THEN
1939 g_error_stage := '290';
1940
1941 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1942 itemkey => l_itemkey,
1943 aname => 'DIST_ATTRIBUTE7',
1944 avalue => p_dist_attribute7);
1945
1946 END IF;
1947
1948 IF p_dist_attribute8 IS NOT NULL
1949 THEN
1950 g_error_stage := '300';
1951
1952 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1953 itemkey => l_itemkey,
1954 aname => 'DIST_ATTRIBUTE8',
1955 avalue => p_dist_attribute8);
1956
1957 END IF;
1958
1959 IF p_dist_attribute9 IS NOT NULL
1960 THEN
1961 g_error_stage := '310';
1962
1963 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1964 itemkey => l_itemkey,
1965 aname => 'DIST_ATTRIBUTE9',
1966 avalue => p_dist_attribute9);
1967
1968 END IF;
1969
1970 IF p_dist_attribute10 IS NOT NULL
1971 THEN
1972 g_error_stage := '320';
1973
1974 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1975 itemkey => l_itemkey,
1976 aname => 'DIST_ATTRIBUTE10',
1977 avalue => p_dist_attribute10);
1978
1979 END IF;
1980
1981 IF p_dist_attribute11 IS NOT NULL
1982 THEN
1983 g_error_stage := '330';
1984
1985 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1986 itemkey => l_itemkey,
1987 aname => 'DIST_ATTRIBUTE11',
1988 avalue => p_dist_attribute11);
1989
1990 END IF;
1991
1992 IF p_dist_attribute12 IS NOT NULL
1993 THEN
1994 g_error_stage := '340';
1995
1996 wf_engine.SetItemAttrText( itemtype => l_itemtype,
1997 itemkey => l_itemkey,
1998 aname => 'DIST_ATTRIBUTE12',
1999 avalue => p_dist_attribute12);
2000
2001 END IF;
2002
2003 IF p_dist_attribute13 IS NOT NULL
2004 THEN
2005 g_error_stage := '350';
2006
2007 wf_engine.SetItemAttrText( itemtype => l_itemtype,
2008 itemkey => l_itemkey,
2009 aname => 'DIST_ATTRIBUTE13',
2010 avalue => p_dist_attribute13);
2011
2012 END IF;
2013
2014 IF p_dist_attribute14 IS NOT NULL
2015 THEN
2016 g_error_stage := '360';
2017
2018 wf_engine.SetItemAttrText( itemtype => l_itemtype,
2019 itemkey => l_itemkey,
2020 aname => 'DIST_ATTRIBUTE14',
2021 avalue => p_dist_attribute14);
2022
2023 END IF;
2024
2025 IF p_dist_attribute15 IS NOT NULL
2026 THEN
2027 g_error_stage := '370';
2028
2029 wf_engine.SetItemAttrText( itemtype => l_itemtype,
2030 itemkey => l_itemkey,
2031 aname => 'DIST_ATTRIBUTE15',
2032 avalue => p_dist_attribute15);
2033
2034 END IF;
2035
2036 IF p_dist_attribute_category IS NOT NULL
2037 THEN
2038 g_error_stage := '380';
2039
2040 wf_engine.SetItemAttrText( itemtype => l_itemtype,
2041 itemkey => l_itemkey,
2042 aname => 'DIST_ATTRIBUTE_CATEGORY',
2043 avalue => p_dist_attribute_category);
2044 END IF;
2045
2046 --- Following section has been added as a part of enhancement request where
2047 --- Users can have workflow(PAAPINVW) setup accross orgs.
2048 --- Workflow Enhancement
2049
2050 BEGIN
2051 SELECT org_id
2052 INTO l_org_id
2053 FROM PA_IMPLEMENTATIONS;
2054 END;
2055
2056 g_error_stage := '381';
2057
2058 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
2059 itemkey => l_itemkey,
2060 aname => 'ORG_ID',
2061 avalue => l_org_id);
2062
2063 /* Bug 5935019 - Changes start */
2064 -- We cannot directly assign this to attribute
2065 -- 'FND_FLEX_SEGMENTS' as workflow will take care of this.
2066 IF x_concat_segs IS NOT NULL THEN
2067 g_error_stage := '382';
2068
2069 wf_engine.SetItemAttrText( itemtype => l_itemtype,
2070 itemkey => l_itemkey,
2071 aname => 'CONCAT_SEGMENTS',
2072 avalue => x_concat_segs);
2073 END IF;
2074 /* Bug 5935019 - Changes end */
2075
2076 /* changes for bug 2348764 - passing the value of user entered ccid to
2077 the workflow */
2078
2079 g_error_stage := '383';
2080
2081 IF p_input_ccid IS NULL THEN
2082 l_input_ccid := 0;
2083 ELSE
2084 l_input_ccid := p_input_ccid;
2085 END IF;
2086 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
2087 itemkey => l_itemkey,
2088 aname => 'DIST_CODE_COMBINATION_ID',
2089 avalue => l_input_ccid);
2090 /* changes for bug 2348764 end */
2091
2092
2093 /* R12 Changes Start - Setting attributes Award ID and Expenditure Item ID
2097 IF x_award_set_id IS NOT NULL THEN
2094 for Workflow */
2095 g_error_stage := '384';
2096
2098
2099 l_award_id := PA_GMS_API.VERT_GET_AWARD_ID(x_award_set_id,NULL,NULL);
2100
2101 ELSE
2102
2103 l_award_id := p_award_id;
2104
2105 END IF;
2106
2107 IF l_award_id IS NOT NULL THEN
2108
2109 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
2110 itemkey => l_itemkey,
2111 aname => 'AWARD_ID',
2112 avalue => l_award_id);
2113
2114 END IF;
2115
2116 g_error_stage := '385';
2117
2118 IF p_expenditure_item_id IS NOT NULL THEN
2119
2120 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
2121 itemkey => l_itemkey,
2122 aname => 'EXPENDITURE_ITEM_ID',
2123 avalue => p_expenditure_item_id);
2124
2125 END IF;
2126 /* R12 Changes End */
2127
2128 -----------------------------------------------------------
2129 -- Call the workflow Generate function to trigger off the
2130 -- workflow account generation
2131 -----------------------------------------------------------
2132
2133 g_error_stage := '390';
2134
2135 l_result := fnd_flex_workflow.generate( l_itemtype,
2136 l_itemkey,
2137 TRUE,
2138 l_return_ccid,
2139 x_concat_segs, -- Bug 5935019
2140 x_concat_ids, -- Bug 5935019
2141 x_concat_descrs, -- Bug 5935019
2142 l_error_message,
2143 l_code_combination);
2144
2145 ---Added this section for Workflow error handling.
2146 -- Workflow Enhancement
2147 /* IF (l_result and ( l_return_ccid is null or l_return_ccid = 0 or l_return_ccid = -1 )) OR
2148 (NOT l_result) THEN ** Commenting this out for bug 2802847 */
2149
2150 /* Added the check for l_error_message for bug 2694601 */
2151 IF l_error_message is null THEN
2152 fnd_message.set_name('PA','PA_WF_SETUP_ERROR');
2153 x_error_message := fnd_message.get_encoded;
2154 ELSE
2155 x_error_message := l_error_message;
2156 END IF;
2157 /* 2694601*/
2158
2159 /* return(FALSE);
2160 END IF; ** bug 2802947 */
2161
2162 ------------------------------------------------------------------
2163 -- Copy the return values to the corresponding output parameters
2164 ------------------------------------------------------------------
2165 g_error_stage := '400';
2166
2167 /* Bug 5935019 - Changes start -- commented 3 lines
2168 x_concat_segs := l_concat_segs;
2169 x_concat_ids := l_concat_ids;
2170 x_concat_descrs:= l_concat_descrs;
2171 Bug 5935019 - Changes end */
2172 -- x_error_message:= l_error_message; bug 2802847
2173 x_return_ccid := l_return_ccid;
2174
2175 --------------------------------------------------------------------
2176 -- Reset the error stack because there were no errors
2177 --------------------------------------------------------------------
2178
2179 reset_error_stack;
2180
2181 --------------------------------------------------------------------
2182 -- Return the return value of the Generate function as the return
2183 -- value for the process
2184 --------------------------------------------------------------------
2185
2186 RETURN l_result;
2187
2188 EXCEPTION
2189
2190 WHEN OTHERS
2191 THEN
2192
2193 -----------------------------------------------------------------------
2194 -- Record error using generic error message routine for debugging and
2195 -- raise it
2196 -----------------------------------------------------------------------
2197
2198 /* Bug 5233487 - Start */
2199 IF g_encoded_error_message IS NULL THEN
2200 g_encoded_error_message := show_error(g_error_stack,g_error_stage,NULL);
2201 END IF;
2202 x_error_message := g_encoded_error_message;
2203 reset_error_stack;
2204 /* Bug 5233487 - End */
2205
2206
2207 wf_core.context( pkg_name => 'pa_acc_gen_wf_pkg',
2208 proc_name => 'ap_inv_generate_account',
2209 arg1 => 'Project id: '||p_project_id,
2210 arg2 => 'Task id: '||p_task_id,
2211 arg3 => 'Vendor id: '||p_vendor_id,
2212 arg4 => 'Exp type: '||p_expenditure_type,
2213 arg5 => 'Exp Org id: '||p_expenditure_organization_id);
2214
2215 RETURN FALSE;
2216
2217 END ap_inv_generate_account;
2218 ----------------------------------------------------------------------
2219 -- Start of procedure upgrade_flexbuilder_account. Procedure level
2220 -- comments with specifications
2221 ----------------------------------------------------------------------
2222
2223 PROCEDURE ap_inv_upgrade_flex_account (
2224 p_itemtype IN VARCHAR2,
2225 p_itemkey IN VARCHAR2,
2226 p_actid IN NUMBER,
2227 p_funcmode IN VARCHAR2,
2228 x_result OUT NOCOPY VARCHAR2)
2229 AS
2230
2231 l_project_id pa_projects_all.project_id%TYPE;
2232 l_task_id pa_tasks.task_id%TYPE;
2233 l_expenditure_type pa_expenditure_types.expenditure_type%TYPE;
2234 l_vendor_id po_vendors.vendor_id%type;
2235 l_expenditure_organization_id hr_organization_units.organization_id%TYPE;
2236 l_expenditure_item_date pa_expenditure_items_all.expenditure_item_date%TYPE;
2237 l_billable_flag pa_tasks.billable_flag%TYPE;
2238 l_chart_of_accounts_id NUMBER;
2239 l_fb_error_msg VARCHAR2(2000);
2243
2240 l_fb_flex_seg VARCHAR2(500);
2241 l_build_account_result BOOLEAN;
2242 l_award_set_id NUMBER; /* Added to fix bug 1612877 */
2244 BEGIN
2245
2246 -----------------------------------------------------------------------
2247 -- Check the Workflow mode in which this function has been called. If
2248 -- it is not in the RUN mode, then exit out of this function
2249 -----------------------------------------------------------------------
2250
2251 IF p_funcmode <> 'RUN'
2252 THEN
2253 x_result := null;
2254 return;
2255 END IF;
2256
2257 --------------------------------------------------------------
2258 -- Get the values of the attributes that were defined as raw
2259 -- parameters in Flexbuilder
2260 --------------------------------------------------------------
2261
2262 l_project_id := wf_engine.GetItemAttrNumber ( itemtype => p_itemtype,
2263 itemkey => p_itemkey,
2264 aname => 'PROJECT_ID');
2265
2266 l_task_id := wf_engine.GetItemAttrNumber ( itemtype => p_itemtype,
2267 itemkey => p_itemkey,
2268 aname => 'TASK_ID');
2269
2270 l_expenditure_type := wf_engine.GetItemAttrText ( itemtype => p_itemtype,
2271 itemkey => p_itemkey,
2272 aname => 'EXPENDITURE_TYPE');
2273
2274 l_expenditure_item_date:= wf_engine.GetItemAttrDate ( itemtype => p_itemtype,
2275 itemkey => p_itemkey,
2276 aname => 'EXPENDITURE_ITEM_DATE');
2277
2278 l_expenditure_organization_id :=
2279 wf_engine.GetItemAttrNumber ( itemtype => p_itemtype,
2280 itemkey => p_itemkey,
2281 aname => 'EXPENDITURE_ORGANIZATION_ID');
2282
2283
2284 /* l_vendor_id populated for bug 2037544 */
2285
2286 l_vendor_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
2287 itemkey => p_itemkey,
2288 aname => 'VENDOR_ID');
2289
2290 l_billable_flag := wf_engine.GetItemAttrText ( itemtype => p_itemtype,
2291 itemkey => p_itemkey,
2292 aname => 'BILLABLE_FLAG');
2293
2294 l_chart_of_accounts_id:=
2295 wf_engine.GetItemAttrNumber ( itemtype => p_itemtype,
2296 itemkey => p_itemkey,
2297 aname => 'CHART_OF_ACCOUNTS_ID');
2298
2299 /* Added to fix bug 1612877 */
2300 l_award_set_id:=
2301 wf_engine.GetItemAttrNumber ( itemtype => p_itemtype,
2302 itemkey => p_itemkey,
2303 aname => 'AWARD_SET_ID');
2304
2305
2306 -----------------------------------------------------------------------
2307 -- Call the build function to derive the account based on Flexbuilder
2308 -- rules
2309 -----------------------------------------------------------------------
2310 /* Added the call for award_set_id in this function to fix bug 1612877 */
2311
2312 l_build_account_result := pa_vend_inv_charge_account.build (
2313 fb_flex_num => l_chart_of_accounts_id,
2314 expenditure_organization_id => l_expenditure_organization_id,
2315 expenditure_type => l_expenditure_type,
2316 pa_billable_flag => l_billable_flag,
2317 project_id => l_project_id,
2318 task_id => l_task_id,
2319 vendor_id => l_vendor_id,
2320 fb_flex_seg => l_fb_flex_seg,
2321 fb_error_msg => l_fb_error_msg) ;
2322
2323 /* Removed to fix bug 1612877 */
2324 -- award_set_id => l_award_set_id );
2325 -- =====================================================
2326
2327 --------------------------------------------------------------------
2328 -- Call the FND procedure to load the values into the concatenated
2329 -- segments
2330 --------------------------------------------------------------------
2331
2332 fnd_flex_workflow.load_concatenated_segments ( p_itemtype,
2333 p_itemkey,
2334 l_fb_flex_seg );
2335
2336 -------------------------------------------------------------------------
2337 -- Check the result of the Build function and return success or failure
2338 -- accordingly
2339 -------------------------------------------------------------------------
2340
2341 IF l_build_account_result
2342 THEN
2343 x_result := 'COMPLETE:SUCCESS';
2344 RETURN;
2345 ELSE
2346 ---------------------------
2347 -- Set error message here
2348 ---------------------------
2349 wf_engine.SetItemAttrText( itemtype => p_itemtype,
2350 itemkey => p_itemkey,
2351 aname => 'ERROR_MESSAGE',
2352 avalue => l_fb_error_msg);
2353
2354 x_result := 'COMPLETE:FAILURE';
2355 RETURN;
2356 END IF;
2357
2358 EXCEPTION
2359
2360 WHEN OTHERS
2361 THEN
2362
2363 -----------------------------------------------------------------------
2364 -- Record error using generic error message routine for debugging and
2365 -- raise it
2366 -----------------------------------------------------------------------
2367
2368 wf_core.context( pkg_name => 'pa_acc_gen_wf_pkg',
2369 proc_name => 'ap_inv_upgrade_flex_account',
2370 arg1 => 'Project Id: ' ||l_project_id,
2371 arg2 => 'Task Id: ' ||l_task_id,
2372 arg3 => 'Vendor Id: ' ||l_vendor_id,
2376 raise;
2373 arg4 => 'Exp type: ' ||l_expenditure_type,
2374 arg5 => 'Exp Org Id: ' ||l_expenditure_organization_id);
2375
2377
2378
2379 END ap_inv_upgrade_flex_account;
2380
2381 ----------------------------------------------------------------------
2382 -- Start of procedure ap_inv_acc_undefined_rules. Function level
2383 -- comments with specifications
2384 ----------------------------------------------------------------------
2385
2386 PROCEDURE ap_inv_acc_undefined_rules (
2387 p_itemtype IN VARCHAR2,
2388 p_itemkey IN VARCHAR2,
2389 p_actid IN NUMBER,
2390 p_funcmode IN VARCHAR2,
2391 x_result OUT NOCOPY VARCHAR2)
2392 IS
2393 l_fb_error_msg VARCHAR2(400);
2394 BEGIN
2395
2396 -----------------------------------------------------------------------
2397 -- Check the Workflow mode in which this function has been called. If
2398 -- it is not in the RUN mode, then exit out of this function
2399 -----------------------------------------------------------------------
2400
2401 IF p_funcmode <> 'RUN'
2402 THEN
2403 x_result := null;
2404 return;
2405 END IF;
2406
2407 -----------------------------------------
2408 -- Set the appropriate message and exit
2409 -----------------------------------------
2410
2411 fnd_message.set_name('PA','FLEXWF-DEFAULT MISSING');
2412 l_fb_error_msg := fnd_message.get_encoded;
2413 wf_engine.SetItemAttrText( itemtype => p_itemtype,
2414 itemkey => p_itemkey,
2415 aname => 'ERROR_MESSAGE',
2416 avalue => l_fb_error_msg);
2417
2418 x_result := 'COMPLETE:FAILURE';
2419 RETURN;
2420
2421 EXCEPTION
2422
2423 WHEN OTHERS
2424 THEN
2425
2426 -----------------------------------------------------------------------
2427 -- Record error using generic error message routine for debugging and
2428 -- raise it
2429 -----------------------------------------------------------------------
2430
2431 wf_core.context( pkg_name => 'pa_acc_gen_wf_pkg ',
2432 proc_name => 'ap_inv_acc_undefined_rules',
2433 arg1 => 'Error: Default workflow not defined',
2434 arg2 => null,
2435 arg3 => null,
2436 arg4 => null,
2437 arg5 => null);
2438
2439 raise;
2440 END ap_inv_acc_undefined_rules;
2441
2442 ----------------------------------------------------------------------
2443
2444 ----------------------------------------------------------------------
2445 -- Start of procedure pa_seg_lookup_set_value. Procedure level
2446 -- comments with specifications
2447 ----------------------------------------------------------------------
2448
2449 PROCEDURE pa_seg_lookup_set_value (
2450 p_itemtype IN VARCHAR2,
2451 p_itemkey IN VARCHAR2,
2452 p_actid IN NUMBER,
2453 p_funcmode IN VARCHAR2,
2454 x_result OUT NOCOPY VARCHAR2)
2455 AS
2456
2457 l_seg_value_lookup_set_name
2458 pa_segment_value_lookup_sets.segment_value_lookup_set_name%TYPE;
2459
2460 l_intermediate_value
2461 pa_segment_value_lookups.segment_value_lookup%TYPE;
2462
2463 l_segment_value pa_segment_value_lookups.segment_value%TYPE;
2464
2465 no_lookup_type EXCEPTION;
2466 no_lookup_code EXCEPTION;
2467 l_error_message VARCHAR2(2000);
2468
2469 BEGIN
2470
2471 -----------------------------------------------------------------------
2472 -- Check the Workflow mode in which this function has been called. If
2473 -- it is not in the RUN mode, then exit out of this function
2474 -----------------------------------------------------------------------
2475
2476 set_error_stack('-->pa_seg_lookup_set_value'); /* Bug 5233487 */
2477 g_error_stage := '10';
2478
2479 IF p_funcmode <> 'RUN'
2480 THEN
2481 x_result := null;
2482 return;
2483 END IF;
2484
2485 ---------------------------------------------------
2486 -- Retrieve the current value for the lookup type
2487 ---------------------------------------------------
2488 g_error_stage := '20';
2489
2490 l_seg_value_lookup_set_name :=
2491 wf_engine.GetActivityAttrText
2492 ( itemtype => p_itemtype,
2493 itemkey => p_itemkey,
2494 actid => p_actid,
2495 aname => 'LOOKUP_TYPE' );
2496
2497 ------------------------------------------------------------------------
2498 -- Raise the appropriate exception if the lookup type has not been set
2499 ------------------------------------------------------------------------
2500
2501 IF l_seg_value_lookup_set_name IS NULL
2502 THEN
2503 RAISE no_lookup_type;
2504 END IF;
2505
2506 ---------------------------------------------------
2507 -- Retrieve the current value for the lookup code
2508 ---------------------------------------------------
2509 g_error_stage := '30';
2510
2511 l_intermediate_value :=
2512 wf_engine.GetActivityAttrText
2513 ( itemtype => p_itemtype,
2514 itemkey => p_itemkey,
2515 actid => p_actid,
2516 aname => 'LOOKUP_CODE' );
2517
2518 ------------------------------------------------------------------------
2519 -- Raise the appropriate exception if the lookup code has not been set
2520 ------------------------------------------------------------------------
2521
2522 IF l_intermediate_value IS NULL
2523 THEN
2524 RAISE no_lookup_code;
2525 END IF;
2526
2530 -------------------------------------------
2527
2528 -------------------------------------------
2529 -- Select the lookup value from the table
2531 g_error_stage := '40';
2532
2533 SELECT segment_value
2534 INTO l_segment_value
2535 FROM pa_segment_value_lookups valuex,
2536 pa_segment_value_lookup_sets sets
2537 WHERE sets.segment_value_lookup_set_id =
2538 valuex.segment_value_lookup_set_id
2539 AND sets.segment_value_lookup_set_name = l_seg_value_lookup_set_name
2540 AND valuex.segment_value_lookup = l_intermediate_value;
2541
2542
2543 -----------------------------------------------------------------------
2544 -- If the retrieval was successful, then set the appropriate item
2545 -- attribute to the value retrieved. Otherwise, raise the appropriate
2546 -- error message
2547 -----------------------------------------------------------------------
2548 g_error_stage := '50';
2549
2550 wf_engine.SetItemAttrText( itemtype => p_itemtype,
2551 itemkey => p_itemkey,
2552 aname => 'LOOKUP_SET_VALUE',
2553 avalue => l_segment_value);
2554
2555
2556 x_result := 'COMPLETE:SUCCESS';
2557
2558 -- If you are here then there were no errors. Reset the error stack.
2559
2560 reset_error_stack;
2561
2562 EXCEPTION
2563
2564 ------------------------------------------------------------------
2565 -- User defined exception raised when lookup type is not defined
2566 ------------------------------------------------------------------
2567
2568 WHEN no_lookup_type
2569 THEN
2570 reset_error_stack; /* Bug 5233487 */
2571 -- Record standard workflow debugging message
2572 wf_core.context( pkg_name => 'PA_ACC_GEN_WF_PKG ',
2573 proc_name => 'PA_SEG_LOOKUP_SET_VALUE',
2574 arg1 => 'Lookup Set:' || l_seg_value_lookup_set_name,
2575 arg2 => 'Intermediate Value: ' || l_intermediate_value,
2576 arg3 => 'Lookup type null',
2577 arg4 => null,
2578 arg5 => null);
2579
2580
2581 -- Error requires an error message to be set so that it can be
2582 -- displayed on the form.
2583
2584
2585 fnd_message.set_name('PA','WF_ACC_LOOKUP_TYPE_FAIL');
2586 l_error_message := fnd_message.get_encoded;
2587 wf_engine.SetItemAttrText( itemtype => p_itemtype,
2588 itemkey => p_itemkey,
2589 aname => 'ERROR_MESSAGE',
2590 avalue => l_error_message);
2591
2592
2593 -- Return a failure so that the abort generation End function is called
2594
2595 x_result := 'COMPLETE:FAILURE';
2596 RETURN;
2597
2598 ------------------------------------------------------------------
2599 -- User defined exception raised when lookup code is not defined
2600 ------------------------------------------------------------------
2601
2602 WHEN no_lookup_code
2603 THEN
2604 reset_error_stack; /* Bug 5233487 */
2605 -- Record standard workflow debugging message
2606 wf_core.context( pkg_name => 'PA_ACC_GEN_WF_PKG ',
2607 proc_name => 'PA_SEG_LOOKUP_SET_VALUE',
2608 arg1 => 'Lookup Set:' || l_seg_value_lookup_set_name,
2609 arg2 => 'Intermediate Value: ' || l_intermediate_value,
2610 arg3 => 'Lookup code null',
2611 arg4 => null,
2612 arg5 => null);
2613
2614
2615 -- Error requires an error message to be set so that it can be
2616 -- displayed on the form. The error message name is defined in
2617 -- Applications.
2618
2619 wf_engine.SetItemAttrText
2620 ( itemtype=> p_itemtype,
2621 itemkey => p_itemkey,
2622 aname => 'ERROR_MESSAGE',
2623 avalue => 'WF_ACC_LOOKUP_CODE_FAIL');
2624
2625 fnd_message.set_name('PA','WF_ACC_LOOKUP_CODE_FAIL');
2626 l_error_message := fnd_message.get_encoded;
2627 wf_engine.SetItemAttrText( itemtype => p_itemtype,
2628 itemkey => p_itemkey,
2629 aname => 'ERROR_MESSAGE',
2630 avalue => l_error_message);
2631
2632
2633 -- Return a failure so that the abort generation End function is called
2634
2635 x_result := 'COMPLETE:FAILURE';
2636 RETURN;
2637
2638 ------------------------------------------------------------------------
2639 -- If data is not found after the SELECT, it indicates that the
2640 -- combination of the lookup type and lookup code has not been defined
2641 ------------------------------------------------------------------------
2642
2643 WHEN no_data_found
2644 THEN
2645 reset_error_stack; /* Bug 5233487 */
2646 -- Record standard workflow debugging message
2647 wf_core.context( pkg_name => 'PA_ACC_GEN_WF_PKG ',
2648 proc_name => 'PA_SEG_LOOKUP_SET_VALUE',
2649 arg1 => 'Lookup Set:' || l_seg_value_lookup_set_name,
2650 arg2 => 'Intermediate Value: ' || l_intermediate_value,
2651 arg3 => 'Lookup code null',
2652 arg4 => null,
2653 arg5 => null);
2654
2655
2656 -- Error requires an error message to be set so that it can be
2657 -- displayed on the form.
2658
2659 wf_engine.SetItemAttrText
2660 ( itemtype=> p_itemtype,
2661 itemkey => p_itemkey,
2662 aname => 'ERROR_MESSAGE',
2663 avalue => 'WF_ACC_LOOKUP_NODATA_FAIL');
2664
2668 itemkey => p_itemkey,
2665 fnd_message.set_name('PA','WF_ACC_LOOKUP_NODATA_FAIL');
2666 l_error_message := fnd_message.get_encoded;
2667 wf_engine.SetItemAttrText( itemtype => p_itemtype,
2669 aname => 'ERROR_MESSAGE',
2670 avalue => l_error_message);
2671
2672
2673 -- Return a failure so that the abort generation End function is called
2674
2675 x_result := 'COMPLETE:FAILURE';
2676 RETURN;
2677
2678 -----------------------------------------------------------
2679 -- All other exceptions are raised to the calling program
2680 -----------------------------------------------------------
2681
2682 WHEN others
2683 THEN
2684 g_error_message := SQLERRM;
2685 --
2686 -- Call the show error function, this function should be used for handling
2687 -- fatal errors. It accepts 5 arguments, we have used this function to
2688 -- pin point the exact error section. Customers can user p_arg1, p_arg2 for
2689 -- more specific debugging, we have used these 2 arguments to identify the
2690 -- record in error( i.e. lookup_set_name and intermediate_value).
2691 --
2692
2693 l_error_message := pa_acc_gen_wf_pkg.show_error(p_error_stack => g_error_stack,
2694 p_error_stage => g_error_stage,
2695 p_error_message => g_error_message,
2696 p_arg1 => 'Lookup Set:'||l_seg_value_lookup_set_name,
2697 p_arg2 => 'Intermediate Value: ' || l_intermediate_value);
2698 reset_error_stack; /* Bug 5233487 */
2699
2700 -- populate the error message wf attribute and return failure.
2701
2702 wf_engine.SetItemAttrText
2703 ( itemtype=> p_itemtype,
2704 itemkey => p_itemkey,
2705 aname => 'ERROR_MESSAGE',
2706 avalue => l_error_message);
2707
2708 -- Return a failure so that the abort generation End function is called
2709
2710 x_result := 'COMPLETE:FAILURE';
2711
2712 -- Record standard workflow debugging message
2713 wf_core.context( pkg_name => 'PA_ACC_GEN_WF_PKG ',
2714 proc_name => 'PA_SEG_LOOKUP_SET_VALUE',
2715 arg1 => 'Lookup Set:' || l_seg_value_lookup_set_name,
2716 arg2 => 'Intermediate Value: ' || l_intermediate_value,
2717 arg3 => null,
2718 arg4 => null,
2719 arg5 => null);
2720
2721 RETURN;
2722
2723 END pa_seg_lookup_set_value;
2724
2725 ----------------------------------------------------------------------
2726 -- Start of procedure pa_aa_function_transaction. Procedure level
2727 -- comments with specifications
2728 ----------------------------------------------------------------------
2729 /*
2730 PROCEDURE pa_aa_function_transaction (
2731 p_itemtype IN VARCHAR2,
2732 p_itemkey IN VARCHAR2,
2733 p_actid IN NUMBER,
2734 p_funcmode IN VARCHAR2,
2735 x_result OUT VARCHAR2)
2736 AS
2737
2738 CURSOR c_ft_code(p_ft_code IN VARCHAR2,
2739 p_ptype_ft_code IN VARCHAR2)
2740 IS
2741 Select function_transaction_code ft_code,
2742 decode(function_transaction_code,'ALL',10,'CON',9,'CAP',8,'IND',7,5) ft_order
2743 from pa_function_transactions
2744 where function_transaction_code in
2745 ('ALL',p_ft_code,p_ptype_ft_code)
2746 and application_id = 275
2747 and function_code = 'BER'
2748 and enabled_flag = 'Y'
2749 order by 2;
2750
2751 ft_rec c_ft_code%ROWTYPE;
2752
2753 l_project_id
2754 pa_projects_all.project_id%TYPE;
2755
2756 l_ptype_class_code
2757 pa_project_types_all.project_type_class_code%TYPE;
2758
2759 l_public_sector_flag
2760 pa_projects_all.public_sector_flag%TYPE;
2761
2762 l_billable_flag pa_tasks.billable_flag%TYPE;
2763
2764 l_ft_code pa_function_transactions_all.function_transaction_code%TYPE;
2765
2766 l_ptype_ft_code pa_function_transactions_all.function_transaction_code%TYPE;
2767
2768
2769 l_old_error_stack VARCHAR2(500);
2770
2771 l_error_message VARCHAR2(2000);
2772
2773 BEGIN
2774
2775 -----------------------------------------------------------------------
2776 -- Check the Workflow mode in which this function has been called. If
2777 -- it is not in the RUN mode, then exit out of this function
2778 -----------------------------------------------------------------------
2779
2780 l_old_error_stack := g_error_stack;
2781 g_error_stack := g_error_stack||'-->'||'pa_aa_function_transaction';
2782 g_error_stage := '10';
2783
2784 IF p_funcmode <> 'RUN'
2785 THEN
2786 x_result := null;
2787 return;
2788 END IF;
2789
2790 ---------------------------------------------------
2791 -- Retrieve the current value for the lookup type
2792 ---------------------------------------------------
2793 g_error_stage := '20';
2794
2795 l_project_id :=
2796 wf_engine.GetItemAttrNumber
2797 ( itemtype => p_itemtype,
2798 itemkey => p_itemkey,
2799 aname => 'PROJECT_ID' );
2800
2801 select a.project_type_class_code
2802 into l_ptype_class_code
2803 from pa_project_types_all a,
2804 pa_projects_all b
2805 where a.project_type = b.project_type
2806 and nvl(a.org_id,-99) = nvl(b.org_id, -99)
2810 -- Retrieve the current value for the public sector flag
2807 and b.project_id = l_project_id;
2808
2809 ----------------------------------------------------------
2811 ----------------------------------------------------------
2812 g_error_stage := '30';
2813
2814 l_public_sector_flag :=
2815 wf_engine.GetItemAttrText
2816 ( itemtype => p_itemtype,
2817 itemkey => p_itemkey,
2818 aname => 'PUBLIC_SECTOR_FLAG' );
2819
2820 --------------------------------------------------------------
2821 -- Retrieve the current value for billable flag
2822 --------------------------------------------------------------
2823 g_error_stage := '40';
2824
2825 l_billable_flag :=
2826 wf_engine.GetItemAttrText
2827 ( itemtype => p_itemtype,
2828 itemkey => p_itemkey,
2829 aname => 'BILLABLE_FLAG' );
2830
2831 ---------------------------------------------------------
2832 -- derive the AutoAccounting function transaction code
2833 -- from project_type_class_code, public_sector_flag and
2834 -- billable_flag
2835 ---------------------------------------------------------
2836
2837 g_error_stage := '50';
2838
2839 IF ( l_ptype_class_code = 'CONTRACT' ) THEN
2840
2841 l_ptype_ft_code := 'CON';
2842
2843 IF ( l_public_sector_flag = 'Y' ) THEN
2844
2845 IF ( l_billable_flag = 'Y' ) THEN
2846
2847 l_ft_code := 'PUB-BILL';
2848 ELSE
2849
2850 l_ft_code := 'PUB-NOBIL';
2851 END IF;
2852 ELSE
2853
2854 IF ( l_billable_flag = 'Y' ) THEN
2855
2856 l_ft_code := 'PRV-BILL';
2857 ELSE
2858
2859 l_ft_code := 'PRV-NOBIL';
2860 END IF;
2861 END IF;
2862
2863 ELSIF ( l_ptype_class_code = 'CAPITAL' ) THEN
2864
2865 l_ptype_ft_code := 'CAP';
2866
2867 IF ( l_public_sector_flag = 'Y' ) THEN
2868
2869 IF ( l_billable_flag = 'Y' ) THEN
2870
2871 l_ft_code := 'PUB-CAP';
2872 ELSE
2873
2874 l_ft_code := 'PUB-NOCAP';
2875 END IF;
2876 ELSE
2877
2878 IF ( l_billable_flag = 'Y' ) THEN
2879
2880 l_ft_code := 'PRV-CAP';
2881 ELSE
2882
2883 l_ft_code := 'PRV-NOCAP';
2884 END IF;
2885 END IF;
2886
2887 ELSIF ( l_ptype_class_code = 'INDIRECT' ) THEN
2888
2889 l_ptype_ft_code := 'IND';
2890
2891 IF ( l_public_sector_flag = 'Y' ) THEN
2892
2893 l_ft_code := 'IND-PUB';
2894 ELSE
2895
2896 l_ft_code := 'IND-PRV';
2897 END IF;
2898
2899 END IF;
2900
2901 ----------------------------------------------------------------
2902 -- Now we have all the function transaction codes
2903 -- lookup the pa_function_transactions_all table and
2904 -- get the code that applies to this transaction
2905 ----------------------------------------------------------------
2906 g_error_stage := '60';
2907
2908 OPEN c_ft_code(l_ft_code,
2909 l_ptype_ft_code);
2910
2911 g_error_stage := '70';
2912
2913 FETCH c_ft_code INTO ft_rec;
2914
2915 CLOSE c_ft_code;
2916
2917 g_error_stage := '80';
2918
2919 wf_engine.SetItemAttrText( itemtype => p_itemtype,
2920 itemkey => p_itemkey,
2921 aname => 'TRANSACTION_CODE',
2922 avalue => ft_rec.ft_code);
2923
2924
2925 x_result := 'COMPLETE:SUCCESS';
2926
2927 -- If you are here then there were no errors. Reset the error stack.
2928
2929 g_error_stack := l_old_error_stack;
2930
2931 EXCEPTION
2932
2933 -----------------------------------------------------------
2934 -- All other exceptions are raised to the calling program
2935 -----------------------------------------------------------
2936
2937 WHEN others
2938 THEN
2939 g_error_message := SQLERRM;
2940 --
2941 -- Call the show error function, this function should be used for handling
2942 -- fatal errors. It accepts 5 arguments, we have used this function to
2943 -- pin point the exact error location. Customers can user p_arg1, p_arg2 for
2944 -- more specific debugging, we have used these 2 arguments to identify the
2945 -- record in error( i.e. l_function_transaction_c and l_ptype_class_code).
2946 --
2947
2948 l_error_message := pa_acc_gen_wf_pkg.show_error(p_error_stack => g_error_stack,
2949 p_error_stage => g_error_stage,
2950 p_error_message => g_error_message,
2951 p_arg1 => 'Function Transaction code:'||l_ft_code,
2952 p_arg2 => 'Project Type Class Code: ' || l_ptype_class_code);
2953
2954 -- populate the error message wf attribute and return failure.
2955
2956 wf_engine.SetItemAttrText
2957 ( itemtype=> p_itemtype,
2958 itemkey => p_itemkey,
2959 aname => 'ERROR_MESSAGE',
2960 avalue => l_error_message);
2961
2962 -- Return a failure so that the abort generation End function is called
2963
2964 x_result := 'COMPLETE:FAILURE';
2968 proc_name => 'PA_AA_FUNCTION_TRANSACTION',
2965
2966 -- Record standard workflow debugging message
2967 wf_core.context( pkg_name => 'PA_ACC_GEN_WF_PKG ',
2969 arg1 => 'Function Transaction Code' || l_ft_code,
2970 arg2 => 'Project Type Class Code: ' || l_ptype_class_code,
2971 arg3 => null,
2972 arg4 => null,
2973 arg5 => null);
2974
2975 RETURN;
2976
2977 END pa_aa_function_transaction;
2978
2979 */
2980 ----------------------------------------------------------------------
2981 -- Start of function show_error. Procedure level
2982 -- comments with specifications
2983 ----------------------------------------------------------------------
2984 FUNCTION show_error(p_error_stack IN VARCHAR2,
2985 p_error_stage IN VARCHAR2,
2986 p_error_message IN VARCHAR2,
2987 p_arg1 IN VARCHAR2 DEFAULT null,
2988 p_arg2 IN VARCHAR2 DEFAULT null) RETURN VARCHAR2
2989 IS
2990
2991 l_result FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2992
2993 BEGIN
2994 g_error_message := nvl(p_error_message,SUBSTRB(SQLERRM,1,1000));
2995
2996 fnd_message.set_name('PA','PA_WF_FATAL_ERROR');
2997 fnd_message.set_token('ERROR_STACK',p_error_stack);
2998 fnd_message.set_token('ERROR_STAGE',p_error_stage);
2999 fnd_message.set_token('ERROR_MESSAGE',g_error_message);
3000 fnd_message.set_token('ERROR_ARG1',p_arg1);
3001 fnd_message.set_token('ERROR_ARG2',p_arg2);
3002
3003 l_result := fnd_message.get_encoded;
3004
3005 g_error_message := NULL;
3006
3007 RETURN l_result;
3008 EXCEPTION WHEN OTHERS
3009 THEN
3010 raise;
3011 END show_error;
3012
3013 /* Bug 5233487 - Start */
3014 PROCEDURE set_error_stack(p_error_stack_msg IN VARCHAR2) IS
3015 BEGIN
3016 IF g_error_stack_history.COUNT = 0 THEN
3017 g_error_stack_history(0) := g_error_stack;
3018 ELSE
3019 g_error_stack_history(g_error_stack_history.LAST + 1) := g_error_stack;
3020 END IF;
3021 g_error_stack := SUBSTR(g_error_stack || p_error_stack_msg, 0, 500);
3022 END set_error_stack;
3023
3024 PROCEDURE reset_error_stack IS
3025 BEGIN
3026 IF g_error_stack_history.COUNT > 0 THEN
3027 g_error_stack := g_error_stack_history(g_error_stack_history.LAST);
3028 g_error_stack_history.DELETE(g_error_stack_history.LAST);
3029 END IF;
3030 END reset_error_stack;
3031 /* Bug 5233487 - End */
3032
3033 END pa_acc_gen_wf_pkg ;