1 PACKAGE BODY PA_CLIENT_EXTN_ASGMT_APPRVL AS
2 /*$Header: PARAAPCB.pls 120.1 2005/10/28 11:34:55 ssong noship $*/
3
4 --
5 -- Determine if the specified assignment's approval required items have been changed or not.
6 --
7 -- Currently the required items :
8 -- work type's resource utilization, org utilization, billable flag, assignment start date,
9 -- assignment end date, bill rates, transfer price rate override, transfer proce currency override,
10 -- transfer price basis override, transfer price applied % override
11 --
12 FUNCTION Is_Asgmt_Appr_Items_Changed
13 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE)
14 RETURN VARCHAR2
15 IS
16
17 --Row(s) returned if No changes made
18 CURSOR items_not_changed IS
19 SELECT 'N'
20 FROM pa_project_assignments ppa,
21 pa_assignments_history pah,
22 pa_work_types_b wtv1,
23 pa_work_types_b wtv2
24 WHERE ppa.assignment_id = p_assignment_id
25 AND pah.assignment_id = p_assignment_id
26 AND pah.last_approved_flag = 'Y'
27 AND ppa.start_date = pah.start_date
28 AND ppa.end_date = pah.end_date
29 AND (ppa.work_type_id = pah.work_type_id
30 OR (ppa.work_type_id <> pah.work_type_id
31 AND wtv1.work_type_id = ppa.work_type_id
32 AND wtv2.work_type_id = pah.work_type_id
33 AND wtv1.BILLABLE_CAPITALIZABLE_FLAG = wtv2.BILLABLE_CAPITALIZABLE_FLAG
34 AND wtv1.RES_UTILIZATION_PERCENTAGE = wtv2.RES_UTILIZATION_PERCENTAGE
35 AND wtv1.ORG_UTILIZATION_PERCENTAGE = wtv2.ORG_UTILIZATION_PERCENTAGE
36 )
37 )
38 -- Included NVL condition for the below four conditions for Bug#3960313
39 AND nvl(ppa.tp_rate_override, -99) = nvl(pah.tp_rate_override, -99)
40 AND nvl(ppa.tp_currency_override, '-99') = nvl(pah.tp_currency_override, '-99')
41 AND nvl(ppa.tp_calc_base_code_override, '-99') = nvl(pah.tp_calc_base_code_override, '-99')
42 AND nvl(ppa.tp_percent_applied_override, -99) = nvl(pah.tp_percent_applied_override, -99)
43 AND rownum = 1;
44
45 l_is_changed VARCHAR2(1);
46
47 BEGIN
48 -- Initialize the Error Stack
49 PA_DEBUG.init_err_stack('PA_CLIENT_EXTN_ASGMT_APPRVL.Is_Asgmt_Appr_Items_Changed');
50
51 OPEN items_not_changed;
52 FETCH items_not_changed INTO l_is_changed;
53
54 IF items_not_changed%NOTFOUND THEN
55 l_is_changed := 'Y';
56 END IF;
57
58 CLOSE items_not_changed;
59
60 RETURN l_is_changed;
61
62 EXCEPTION
63 WHEN OTHERS THEN
64 -- Set the excetption Message and the stack
65 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_CLIENT_EXTN_ASGMT_APPRVL.Is_Asgmt_Appr_Items_Changed'
66 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
67 --
68 RAISE; -- This is optional depending on the needs
69
70 END Is_Asgmt_Appr_Items_Changed;
71
72
73 END PA_CLIENT_EXTN_ASGMT_APPRVL;
[Home] [Help]