[Home] [Help]
PACKAGE BODY: APPS.PQP_CONFIG_INFO_PKG
Source
1 PACKAGE BODY pqp_config_info_pkg AS
2 /* $Header: pqcfigcp.pkb 115.1 2003/03/06 23:41:51 sshetty noship $ */
3
4 FUNCTION get_user_table_name ( p_table_id IN NUMBER
5 ,p_business_group_id IN NUMBER
6 )
7 RETURN VARCHAR2
8 IS
9
10 CURSOR c_get_user_table_name
11 IS
12 SELECT put.user_table_name
13 FROM pay_user_tables put
14 WHERE put.business_group_id=p_business_group_id
15 AND put.user_table_id =p_table_id;
16
17 l_get_user_table_name c_get_user_table_name%ROWTYPE;
18
19 BEGIN
20
21 OPEN c_get_user_table_name;
22 FETCH c_get_user_table_name INTO l_get_user_table_name;
23
24 CLOSE c_get_user_table_name;
25
26 RETURN (NVL(l_get_user_table_name.user_table_name,'NONE'));
27
28 END;
29
30
31
32 FUNCTION get_element_name ( p_element_id IN NUMBER
33 ,p_business_group_id IN NUMBER
34 )
35 RETURN VARCHAR2
36 IS
37
38 CURSOR c_get_ele_name
39 IS
40 SELECT pet.element_name
41 FROM pay_element_types_f pet
42 WHERE pet.element_type_id =p_element_id
43 AND pet.business_group_id=p_business_group_id
44 AND sysdate BETWEEN pet.effective_start_date
45 AND pet.effective_end_date;
46
47 l_get_ele_name c_get_ele_name%ROWTYPE;
48
49
50
51
52 BEGIN
53 OPEN c_get_ele_name;
54 FETCH c_get_ele_name INTO l_get_ele_name;
55 CLOSE c_get_ele_name;
56
57 RETURN (NVL(l_get_ele_name.element_name,'NONE'));
58
59 END;
60
61 PROCEDURE pqp_veh_calc_info
62 ( errbuf OUT NOCOPY VARCHAR2,
63 retcode OUT NOCOPY NUMBER,
64 p_effective_date IN DATE default trunc(sysdate),
65 p_business_group_id IN NUMBER,
66 p_legislation_code IN VARCHAR2 default null,
67 p_ownership IN VARCHAR2 ,
68 p_usage_type IN VARCHAR2 default null ,
69 p_vehicle_type IN VARCHAR2 default null,
70 p_fuel_type IN VARCHAR2 default null,
71 p_user_rates_table IN VARCHAR2 default null,
72 p_element_entry_id IN VARCHAR2 default null,
73 p_mode IN VARCHAR2
74 )
75
76 IS
77
78 CURSOR c_get_info
79 IS
80 SELECT pcv.pcv_information_category
81 , pqp_gb_mileage_claim_pkg.get_lkp_meaning
82 (pcv.pcv_information1,'PQP_VEHICLE_OWNERSHIP_TYPE')
83 Ownership
84 ,RPAD(DECODE (pcv.pcv_information1,'C',
85 pqp_gb_mileage_claim_pkg.get_lkp_meaning
86 (pcv.pcv_information2,'PQP_COMPANY_VEHICLE_USER'),
87 'P',pqp_gb_mileage_claim_pkg.get_lkp_meaning
88 (pcv.pcv_information2,'PQP_PRIVATE_VEHICLE_USER')
89 ),10) Usage_type
90 , RPAD(pqp_gb_mileage_claim_pkg.get_lkp_meaning
91 (pcv.pcv_information3 ,'PQP_VEHICLE_TYPE'),10)
92 Vehicle_type
93 ,RPAD(pqp_gb_mileage_claim_pkg.get_lkp_meaning
94 (pcv.pcv_information4,'PQP_FUEL_TYPE'),21)
95 Fuel_type
96 ,RPAD(get_user_table_name(
97 TO_NUMBER(pcv.pcv_information5),
98 p_business_group_id),35) rates_type
99 ,RPAD(get_element_name(
100 TO_NUMBER(pcv.pcv_information6),
101 p_business_group_id),60) element_name
102 FROM pqp_configuration_values pcv
103 WHERE business_group_id=p_business_group_id;
104
105 l_configuration_value_id number;
106 l_object_version_number number;
107 l_pcv_information_category varchar2(80) := 'GB_VEHICLE_CALC_INFO';
108 l_count NUMBER;
109 l_mode VARCHAR2(1);
110 l_get_info c_get_info%ROWTYPE;
111 BEGIN
112
113 IF p_mode='I' THEN
114
115 SELECT count(*)
116 INTO l_count
117 FROM pqp_configuration_values pcv
118 WHERE pcv.pcv_information_category ='GB_VEHICLE_CALC_INFO'
119 AND pcv.pcv_information1 =p_ownership
120 AND pcv.pcv_information2 =p_usage_type
121 AND pcv.pcv_information3 =p_vehicle_type
122 AND pcv.pcv_information4 =p_fuel_type
123 AND pcv.business_group_id=p_business_group_id;
124
125 IF l_count >0 THEN
126 l_mode:='U';
127 ELSE
128 pqp_pcv_ins.ins
129 ( p_effective_date =>trunc(sysdate)
130 ,p_business_group_id =>p_business_group_id
131 ,p_pcv_information_category =>l_pcv_information_category
132 ,p_pcv_information1 =>p_ownership
133 ,p_pcv_information2 =>p_usage_type
134 ,p_pcv_information3 =>p_vehicle_type
135 ,p_pcv_information4 =>p_fuel_type
136 ,p_pcv_information5 =>(p_user_rates_table)
137 ,p_pcv_information6 =>(p_element_entry_id)
138 ,p_configuration_value_id =>l_configuration_value_id
139 ,p_object_version_number =>l_object_version_number
140 );
141 END IF;
142 END IF;
143
144 IF p_mode='U' or l_mode='U' THEN
145
146 SELECT configuration_value_id
147 ,object_version_number
148 INTO l_configuration_value_id
149 ,l_object_version_number
150 FROM pqp_configuration_values pcv
151 WHERE pcv.pcv_information_category ='GB_VEHICLE_CALC_INFO'
152 AND pcv.pcv_information1 =p_ownership
153 AND pcv.pcv_information2 =p_usage_type
154 AND pcv.pcv_information3 =p_vehicle_type
155 AND pcv.pcv_information4 =p_fuel_type
156 AND pcv.business_group_id=p_business_group_id;
157
158 pqp_pcv_upd.upd
159 (p_effective_date =>trunc(sysdate)
160 ,p_configuration_value_id =>l_configuration_value_id
161 ,p_object_version_number =>l_object_version_number
162 ,p_business_group_id =>p_business_group_id
163 ,p_legislation_code =>NULL
164 ,p_pcv_information_category =>l_pcv_information_category
165 ,p_pcv_information1 =>p_ownership
166 ,p_pcv_information2 =>p_usage_type
167 ,p_pcv_information3 =>p_vehicle_type
168 ,p_pcv_information4 =>p_fuel_type
169 ,p_pcv_information5 =>p_user_rates_table
170 ,p_pcv_information6 =>p_element_entry_id
171 );
172
173
174 END IF;
175
176 IF p_mode='D' THEN
177
178 SELECT configuration_value_id
179 ,object_version_number
180 INTO l_configuration_value_id
181 ,l_object_version_number
182 FROM pqp_configuration_values pcv
183 WHERE pcv.pcv_information_category ='GB_VEHICLE_CALC_INFO'
184 AND pcv.pcv_information1 =p_ownership
185 AND pcv.pcv_information2 =p_usage_type
186 AND pcv.pcv_information3 =p_vehicle_type
187 AND pcv.pcv_information4 =p_fuel_type
188 AND pcv.business_group_id=p_business_group_id;
189
190
191 pqp_pcv_del.del
192 (p_configuration_value_id =>l_configuration_value_id
193 ,p_object_version_number =>l_object_version_number
194 );
195
196 END IF;
197
198
199 fnd_file.put(fnd_file.output,'Ownership ');
200 fnd_file.put(fnd_file.output,'Usage type ');
201 fnd_file.put(fnd_file.output,'Vehicle Type ');
202 fnd_file.put(fnd_file.output,' ' );
203 fnd_file.put(fnd_file.output,'Fuel Type ');
204 fnd_file.put(fnd_file.output,' ' );
205 fnd_file.put(fnd_file.output,'Rates');
206 fnd_file.put(fnd_file.output,' ' );
207 fnd_file.put(fnd_file.output,'Element ');
208
209
210 fnd_file.put_line(fnd_file.output,' ');
211
212 OPEN c_get_info;
213 LOOP
214 FETCH c_get_info INTO l_get_info;
215 EXIT WHEN c_get_info%NOTFOUND;
216 fnd_file.put_line(fnd_file.output,' ');
217 fnd_file.put(fnd_file.output,l_get_info.Ownership );
218 fnd_file.put(fnd_file.output,' ' );
219 fnd_file.put(fnd_file.output,l_get_info.usage_type );
220 fnd_file.put(fnd_file.output,' ' );
221 fnd_file.put(fnd_file.output,l_get_info.vehicle_type );
222 fnd_file.put(fnd_file.output,' ' );
223 fnd_file.put(fnd_file.output,l_get_info.fuel_type );
224 fnd_file.put(fnd_file.output,' ' );
225 fnd_file.put(fnd_file.output,l_get_info.rates_type );
226 fnd_file.put(fnd_file.output,' ' );
227 fnd_file.put(fnd_file.output,l_get_info.element_name );
228
229
230 END LOOP;
231 CLOSE c_get_info;
232 END;
233
234 END;