[Home] [Help]
PACKAGE BODY: APPS.PQP_CANDM_UPG
Source
1 package body PQP_CANDM_UPG AS
2 /* $Header: pqpcnmupg.pkb 120.1 2006/04/24 20:12:14 hgattu noship $ */
3 /*
4 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
5 --
6 --
7
8 --
9 */
10 procedure upgrade_formula (errbuf OUT NOCOPY VARCHAR2
11 ,retcode OUT NOCOPY NUMBER
12 ,p_formula_name IN VARCHAR2
13 )
14 AS
15
16 l_temp LONG;--varchar2(32000);
17 l_temp_addl LONG;
18 l_result_name VARCHAR2(80);
19 l_flag Varchar2(1):='N';
20 l_sh_formula_name pay_shadow_formulas.formula_name%TYPE;
21 l_sh_formula_text varchar2(32727);
22 l_object_version_number NUMBER;
23 l_suffix VARCHAR2(80);
24 l_error NUMBER:=0;
25 l_version VARCHAR2(300);
26 l_template_version_pvt VARCHAR2(10);
27 l_chk_version NUMBER;
28
29 l_cursor integer;
30 l_n number;
31 l_long_val varchar2(250);
32 l_long_len number;
33 l_buflen number := 250;
34 l_curpos number := 0;
35
36
37
38 /*Gets Existing car mileage formula text*/
39 CURSOR c_sh_formula_text IS
40 SELECT formula_name,formula_text
41 FROM pay_shadow_formulas
42 where template_type='T'
43 AND formula_name
44 IN ( '_MILEAGE_CLAIM_PVT'
45 );
46
47
48 l_temp_text LONG;
49 l_temp_pvt LONG;
50
51 CURSOR c_formula IS
52 SELECT fff.formula_id
53 ,fff.effective_start_date
54 ,fff.effective_end_date
55 ,fff.formula_type_id
56 ,fff.formula_name
57 ,fff.business_group_id
58 ,fff.description
59 ,fff.sticky_flag
60 ,fff.compile_flag
61 ,paf.status_processing_rule_id status_processing_rule_id
62 FROM ff_formulas_f fff
63 ,PAY_STATUS_PROCESSING_RULES_F paf
64 --,pay_element_types_f petf
65 WHERE (formula_name
66 LIKE NVL(p_formula_name,'%_MILEAGE_CLAIM_PVT')
67 )
68 AND fff.formula_id=paf.formula_id
69 AND paf.assignment_status_type_id IS NULL
70 AND fff.business_group_id=paf.business_group_id
71 ORDER BY fff.formula_id,fff.effective_start_date ;
72
73 CURSOR c_actual_formula (cp_formula_name VARCHAR2
74 ,cp_business_group_id NUMBER
75 )
76 IS
77 SELECT fff.formula_id
78 ,fff.effective_start_date
79 ,fff.effective_end_date
80 ,fff.formula_type_id
81 ,fff.formula_name
82 ,substr(fff.formula_name,0,(length(fff.formula_name)-12)) fname
83 ,fff.business_group_id
84 ,paf.status_processing_rule_id
85 FROM ff_formulas_f fff
86 ,PAY_STATUS_PROCESSING_RULES_F paf
87 WHERE formula_name=cp_formula_name
88 AND fff.business_group_id=cp_business_group_id
89 AND fff.business_group_id=paf.business_group_id
90 AND fff.formula_id=paf.formula_id
91 AND paf.assignment_status_type_id IS NULL
92 ORDER BY fff.formula_id,fff.effective_start_date ;
93
94 l_formula c_formula%ROWTYPE;
95 l_actual_formula c_actual_formula%ROWTYPE;
96 l_formula_name VARCHAR2(80);
97 l_short_formula_name VARCHAR2(80);
98
99 CURSOR c_elename(cformula_id number,cbusiness_group_id number) IS
100 SELECT element_name
101 FROM pay_element_types_f petf
102 WHERE element_type_id = (SELECT distinct(pspr.element_type_id)
103 FROM pay_status_processing_rules_f pspr
104 ,pay_element_type_extra_info pete
105 WHERE pspr.formula_id =cformula_id
106 AND pspr.business_group_id=cbusiness_group_id
107 AND pspr.business_group_id=petf.business_group_id
108 AND pspr.element_type_id=pete.element_type_id
109 AND pete.INFORMATION_TYPE =
110 'PQP_VEHICLE_MILEAGE_INFO'
111 AND pete.EEI_INFORMATION1 in ('P','PP',
112 'PM'
113 ));
114 l_elename c_elename%ROWTYPE;
115 l_dt VARCHAR2(5);
116 l_query VARCHAR2(3000);
117
118 l_seq NUMBER;
119 l_st_date DATE;
120 l_end_date DATE;
121 l_bg_id NUMBER;
122 l_formula_type_id ff_formulas_f.formula_type_id%TYPE;
123
124 l_ctr NUMBER;
125
126 /********BEGIN************/
127 Begin
128
129 SELECT TO_CHAR(SYSDATE,'YYYY')
130 INTO l_dt
131 FROM DUAL;
132
133 l_dt:='_'||l_dt;
134
135 IF l_pvt_formula IS NULL THEN
136 OPEN c_sh_formula_text;
137 LOOP
138 FETCH c_sh_formula_text INTO l_sh_formula_name,l_sh_formula_text;
139 EXIT WHEN c_sh_formula_text%NOTFOUND;
140 l_pvt_formula := l_sh_formula_text;
141 l_template_version_pvt:=substr(l_pvt_formula,
142 INSTR(l_pvt_formula,'pqgbtcam.sql')+13,6);
143
144 END LOOP;
145 CLOSE c_sh_formula_text;
146
147 END IF;
148
149
150
151
152 OPEN c_formula;
153 LOOP
154 BEGIN
155 FETCH c_formula INTO l_formula;
156 EXIT WHEN c_formula%NOTFOUND;
157 l_formula_name:=l_formula.formula_name;
158 l_short_formula_name := substr(l_formula.formula_name,0,(length(l_formula.formula_name)-18));
159 l_query:=
160 'SELECT formula_text FROM ff_formulas_f WHERE formula_name= :x AND sysdate BETWEEN
161 effective_start_date and effective_end_date';
162
163 BEGIN
164 l_cursor := dbms_sql.open_cursor;
165 dbms_sql.parse( l_cursor, l_query, dbms_sql.native );
166 dbms_sql.bind_variable( l_cursor, ':x', l_formula_name );
167
168 dbms_sql.define_column_long(l_cursor, 1);
169 l_n := dbms_sql.execute(l_cursor);
170 IF (dbms_sql.fetch_rows(l_cursor)>0)
171 THEN
172
173 dbms_sql.column_value_long(l_cursor, 1, l_buflen,
174 l_curpos , l_long_val,
175 l_long_len );
176 END IF;
177
178 BEGIN
179 l_version:=substr(l_long_val,
180 INSTR(l_long_val,'pqgbtcam.sql')+13,6);
181 l_chk_version :=l_version;
182
183 EXCEPTION
184 ---------
185 WHEN OTHERS THEN
186 l_version:='115.39';
187
188 END;
189
190
191 dbms_sql.close_cursor(l_cursor);
192
193 end;
194 IF l_chk_version < l_template_version_pvt THEN
195 l_temp_text:=l_pvt_formula;
196
197
198 l_temp_text:= replace (l_temp_text,'<BASE NAME>',UPPER(REPLACE(l_short_formula_name,' ','_')));
199 UPDATE ff_formulas_f
200 SET formula_name = l_formula_name||l_dt||'BAK'
201 WHERE formula_name = l_formula_name
202 AND sysdate between effective_start_date and effective_end_date;
203
204
205 UPDATE ff_formulas_f_tl
206 SET formula_name = l_formula_name||l_dt||'BAK'
207 WHERE formula_name = l_formula_name;
208
209 INSERT INTO ff_formulas_f(
210 FORMULA_ID
211 ,EFFECTIVE_START_DATE
212 ,EFFECTIVE_END_DATE
213 ,BUSINESS_GROUP_ID
214 ,LEGISLATION_CODE
215 ,FORMULA_TYPE_ID
216 ,FORMULA_NAME
217 ,DESCRIPTION
218 ,FORMULA_TEXT
219 ,STICKY_FLAG
220 ,COMPILE_FLAG
221 ,LAST_UPDATE_DATE
222 ,LAST_UPDATED_BY
223 ,LAST_UPDATE_LOGIN
224 ,CREATED_BY
225 ,CREATION_DATE )
226
227 VALUES(
228 ff_formulas_s.nextval
229 ,l_formula.EFFECTIVE_START_DATE
230 ,l_formula.EFFECTIVE_end_DATE
231 ,l_formula.business_group_id
232 ,NULL
233 ,l_formula.formula_type_id
234 ,l_formula.formula_name
235 ,l_formula.description
236 ,l_temp_text --l_formula.formula_text
237 ,l_formula.sticky_flag
238 ,l_formula.compile_flag
239 ,sysdate
240 ,1
241 ,1
242 ,1
243 ,sysdate
244 );
245
246 /* INSERT INTO ff_formulas_f_tl (
247 FORMULA_ID
248 ,LANGUAGE
249 ,SOURCE_LANG
250 ,FORMULA_NAME
251 ,DESCRIPTION
252 ,LAST_UPDATE_DATE
253 ,LAST_UPDATED_BY
254 ,LAST_UPDATE_LOGIN
255 ,CREATED_BY
256 ,CREATION_DATE )
257 VALUES
258 (ff_formulas_s.currval
259 ,'US'
260 ,'US'
261 ,l_formula.formula_name
262 ,l_formula.description
263 ,sysdate
264 ,1
265 ,1
266 ,1
267 ,sysdate
268 );*/
269
270 UPDATE pay_status_processing_rules_f
271 SET formula_id=ff_formulas_s.currval
272 WHERE status_processing_rule_id=l_formula.status_processing_rule_id
273 AND sysdate BETWEEN effective_start_date and effective_end_date;
274
275
276 fnd_file.new_line (fnd_file.output,1);
277 fnd_file.put(fnd_file.output,'The Fast Formula '|| l_formula.formula_name||
278 ' is successfully upgraded and the backup of old formula is in ' || l_formula_name||l_dt||'BAK'||' .');
279 END IF;
280
281
282 EXCEPTION
283 ---------
284
285 WHEN OTHERS THEN
286 fnd_file.put(fnd_file.output,l_formula_name||' Formula Upgrade Failed');
287 END;
288 END LOOP;
289 CLOSE C_FORMULA;
290 EXCEPTION
291 ---------
292
293 WHEN OTHERS THEN
294 fnd_file.put(fnd_file.output,'Formula Upgrade Failed');
295 END UPGRADE_FORMULA;
296
297
298 end PQP_CANDM_UPG;