1 PACKAGE BODY PQP_CLM_BAL AS
2 /* $Header: pqpvmbal.pkb 115.2 2003/06/25 11:48:50 jcpereir noship $*/
3 -- This Function return Element Level balances given the Balance Name, Element Entry Id
4 -- and Assignment Action Id
5 FUNCTION get_vehicletype_balance
6 (p_assignment_id IN number
7 ,p_business_group_id IN number
8 ,p_vehicle_type IN varchar2
9 ,p_ownership IN varchar2
10 ,p_usage_type IN varchar2
11 ,p_balance_name IN varchar2
12 ,p_element_entry_id IN NUMBER
13 ,p_assignment_action_id IN NUMBER
14 ) RETURN NUMBER IS
15 --
16 --
17 CURSOR csr_balance IS
18 select pet.element_name
19 ,pet.business_group_id
20 ,ppa.payroll_action_id
21 ,prr.source_type
22 ,prr.run_result_id
23 FROM pay_element_types_f pet
24 ,pay_element_type_extra_info pete
25 ,pay_run_results prr
26 ,pay_assignment_actions paas
27 ,pay_payroll_actions ppa
28 where
29 pete.EEI_INFORMATION_CATEGORY='PQP_VEHICLE_MILEAGE_INFO'
30 AND pet.element_type_id=pete.element_type_id
31 AND pete.EEI_INFORMATION1 in ('C','P','CM','CP','PP','PM')
32 AND pet.business_group_id=p_business_group_id
33 AND paas.assignment_id =p_assignment_id
34 AND paas.assignment_action_id=prr.assignment_action_id
35 AND pete.element_type_id=prr.element_type_id
36 AND paas.payroll_action_id=ppa.payroll_action_id
37 AND ppa.action_type in ('Q','R','V')
38 AND paas.action_status='C'
39 AND prr.source_id = p_element_entry_id
40 AND paas.assignment_action_id = p_assignment_action_id
41 AND pete.EEI_INFORMATION1 = nvl(p_vehicle_type,pete.EEI_INFORMATION1)
42 AND substr(pete.EEI_INFORMATION1,0,1) = nvl(p_ownership,substr(pete.EEI_INFORMATION1,0,1));
43
44 CURSOR c_src_ele_entry_id(cp_run_result_id NUMBER)
45 IS
46 Select rr2.source_id
47 FROM pay_run_results rr1,
48 pay_run_results rr2
49 WHERE
50 rr1.run_result_id = cp_run_result_id
51 AND rr1.source_id = rr2.run_result_id;
52
53 --
54 lsr_balance csr_balance%ROWTYPE;
55 l_sum number;
56 l_temp_element_entry_id number;
57 --
58 BEGIN
59
60 open csr_balance;
61 fetch csr_balance into lsr_balance;
62 close csr_balance;
63 hr_utility.set_location('SOURCE TYPE '||lsr_balance.source_type,10);
64 IF lsr_balance.source_type = 'R' or lsr_balance.source_type = 'V' THEN
65 open c_src_ele_entry_id(lsr_balance.run_result_id);
66 fetch c_src_ele_entry_id into l_temp_element_entry_id;
67 close c_src_ele_entry_id;
68 ELSE
69 l_temp_element_entry_id := p_element_entry_id;
70 END IF;
71 l_sum := pqp_clm_bal.get_balance_value ( lsr_balance.element_name
72 ,p_assignment_action_id
73 ,l_temp_element_entry_id
74 ,lsr_balance.business_group_id
75 ,lsr_balance.payroll_action_id
76 ,p_balance_name);
77 /*IF lsr_balance.source_type = 'R' or lsr_balance.source_type = 'V' THEN
78 l_sum := -l_sum;
79 END IF;*/
80 return nvl(l_sum,0);
81
82 END get_vehicletype_balance;
83
84 --
85 FUNCTION get_balance_value ( p_element_name IN VARCHAR2
86 ,p_assignment_action_id IN NUMBER
87 ,p_element_entry_id IN NUMBER
88 ,p_business_group_id IN NUMBER
89 ,p_payroll_action_id IN NUMBER
90 ,p_balance_name IN VARCHAR2
91 )
92 return NUMBER
93 IS
94 CURSOR c_get_balance_det
95 IS
96 SELECT pbt.balance_name,pbt.balance_type_id ,pbd.balance_dimension_id
97 FROM pay_balance_types pbt
98 ,pay_defined_balances pdb
99 ,pay_balance_dimensions pbd
100 WHERE balance_name like p_element_name||' '||p_balance_name
101 AND pbd.legislation_code='GB'
102 AND pbd.dimension_name='_ELEMENT_ITD'
103 AND pdb.balance_type_id=pbt.balance_type_id
104 AND pdb.balance_dimension_id=pbd.balance_dimension_id
105 AND pbt.balance_type_id=pdb.balance_type_id;
106
107 cursor c_get_balance_val(cp_balance_type_id NUMBER
108 ,cp_assignment_action_id NUMBER
109 ,cp_element_entry_id NUMBER
110 ,cp_payroll_action_id NUMBER)
111 is
112 SELECT nvl((fnd_number.canonical_to_number(TARGET.result_value)
113 * FEED.scale),0) tot
114 FROM pay_run_result_values TARGET
115 , pay_balance_feeds_f FEED
116 , pay_run_results RR
117 , pay_assignment_actions ASSACT
118 , pay_assignment_actions BAL_ASSACT
119 , pay_payroll_actions PACT
120 WHERE BAL_ASSACT.assignment_action_id = cp_assignment_action_id
121 AND FEED.balance_type_id = cp_balance_type_id
122 AND FEED.input_value_id = TARGET.input_value_id
123 AND TARGET.run_result_id = RR.run_result_id
124 AND RR.assignment_action_id = ASSACT.assignment_action_id
125 AND ASSACT.payroll_action_id = PACT.payroll_action_id
126 AND assact.payroll_action_id = cp_payroll_action_id
127 AND PACT.effective_date between FEED.effective_start_date
128 AND FEED.effective_end_date
129 AND RR.status in ('P','PA')
130 AND ASSACT.action_sequence <= BAL_ASSACT.action_sequence
131 AND ASSACT.assignment_id = BAL_ASSACT.assignment_id
132 AND (( RR.source_id = cp_element_entry_id and source_type in ( 'E','I'))
133 OR ( rr.source_type in ('R','V') /* reversal */
134 AND exists
135 ( SELECT null from pay_run_results rr1
136 WHERE rr1.source_id = cp_element_entry_id
137 AND rr1.run_result_id = rr.source_id
138 AND rr1.source_type in ( 'E','I'))));
139
140
141
142
143 l_get_balance_det c_get_balance_det%ROWTYPE;
144 l_get_balance_val c_get_balance_val%ROWTYPE;
145 l_balance_type_id NUMBER := NULL;
146 l_cache_count NUMBER;
147 BEGIN
148
149 --Check the Balance Cache for Balance Type Id
150 FOR i in 1..g_balance_cache.count
151 LOOP
152 IF g_balance_cache(i).balance_name = p_element_name||' '||p_balance_name THEN
153 l_balance_type_id := g_balance_cache(i).balance_type_id;
154 exit;
155 END IF;
156 END LOOP;
157
158 IF l_balance_type_id IS NULL THEN
159 OPEN c_get_balance_det;
160 FETCH c_get_balance_det INTO l_get_balance_det;
161 CLOSE c_get_balance_det;
162 l_balance_type_id := l_get_balance_det.balance_type_id;
163 -- Make An Entry in Balance Cache
164 l_cache_count := g_balance_cache.count+1;
165 g_balance_cache(l_cache_count).balance_name := p_element_name||' '||p_balance_name;
166 g_balance_cache(l_cache_count).balance_type_id := l_balance_type_id;
167 END IF;
168
169 OPEN c_get_balance_val(l_balance_type_id
170 ,p_assignment_action_id
171 ,p_element_entry_id
172 ,p_payroll_action_id);
173 FETCH c_get_balance_val INTO l_get_balance_val;
174 return(NVL(l_get_balance_val.tot,0));
175 CLOSE c_get_balance_val;
176
177
178 return(0);
179 END;
180 END PQP_CLM_BAL;
[Home] [Help]