[Home] [Help]
PACKAGE BODY: APPS.PQP_CANDM_UPG
Source
1 package body PQP_CANDM_UPG AS
2 /* $Header: pqpcnmupg.pkb 120.1.12010000.7 2009/07/30 09:52:35 nchinnam ship $ */
3 /*
4 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
5 --
6 --
7
8 --
9 */
10
11 function get_version(p_formula_text pay_shadow_formulas.formula_text%TYPE) RETURN VARCHAR2
12 as
13 l_ver_start_idx INTEGER;
14 l_ver_end_idx INTEGER;
15 l_version VARCHAR2(100);
16
17 begin
18 l_ver_start_idx:=INSTR(p_formula_text,'pqgbtcam.sql')+13;
19 l_ver_end_idx:=INSTR(p_formula_text,' ',l_ver_start_idx);
20 l_version:=substr(p_formula_text,l_ver_start_idx,l_ver_end_idx - l_ver_start_idx);
21 return l_version;
22 end get_version;
23
24 function get_token (p_text varchar2,p_token_num number,p_delim varchar2 :='.')return varchar2
25 as
26 l_start_pos number;
27 l_end_pos number;
28 begin
29 if p_token_num <= 0 then
30 return null;
31 end if;
32
33 if p_token_num = 1 then
34 l_start_pos := 1;
35 else
36 l_start_pos := instr(p_text, p_delim, 1, p_token_num - 1);
37 if l_start_pos = 0 then
38 return null;
39 else
40 l_start_pos := l_start_pos + length(p_delim);
41 end if;
42 end if;
43
44 l_end_pos := instr(p_text, p_delim, l_start_pos, 1);
45 if l_end_pos = 0 then
46 return substr(p_text, l_start_pos);
47 else
48 return substr(p_text, l_start_pos, l_end_pos - l_start_pos);
49 end if;
50 end get_token;
51
52 function version_compare(p_low_version VARCHAR2,p_high_version VARCHAR2) RETURN BOOLEAN
53 as
54 l_count INTEGER:=1;
55 l_low_cur VARCHAR2(100);
56 l_high_cur VARCHAR2(100);
57 l_ret_value BOOLEAN;
58
59 begin
60
61 LOOP
62 l_low_cur:=get_token(p_low_version,l_count);
63 l_high_cur:=get_token(p_high_version,l_count);
64 if (l_low_cur is null) AND (l_high_cur is not null) then
65 return true;
66 elsif (l_low_cur is not null) AND (l_high_cur is null) then
67 return true;
68 elsif (l_low_cur is null) AND (l_high_cur is null) then
69 return false;
70 end if;
71
72 if (to_number(l_low_cur) < to_number(l_high_cur)) then
73 return true;
74 elsif to_number(l_low_cur) > to_number(l_high_cur) then
75 return false;
76 end if;
77 l_count:=l_count+1;
78 if l_count > 100 then
79 return false;
80 END IF;
81 END LOOP;
82
83 end version_compare;
84
85 procedure upgrade_formula (errbuf OUT NOCOPY VARCHAR2
86 ,retcode OUT NOCOPY NUMBER
87 ,p_formula_cat IN VARCHAR2
88 ,p_formula_name IN VARCHAR2
89 )
90 AS
91 -- Get the shadow formula details
92 CURSOR c_sh_formula_details IS
93 SELECT formula_name
94 ,formula_text
95 FROM pay_shadow_formulas
96 WHERE template_type='T'
97 AND formula_name like p_formula_cat;
98
99 -- Get the Actual formula details
100 CURSOR c_formula_details IS
101 SELECT fff.formula_id
102 ,fff.effective_start_date
103 ,fff.effective_end_date
104 ,fff.formula_type_id
105 ,fff.formula_name
106 ,fff.business_group_id
107 ,fff.description
108 ,fff.sticky_flag
109 ,fff.compile_flag
110 ,paf.status_processing_rule_id status_processing_rule_id
111 ,fff.formula_text
112 ,fff.legislation_code
113 FROM ff_formulas_f fff
114 ,PAY_STATUS_PROCESSING_RULES_F paf
115 WHERE fff.formula_name like NVL(p_formula_name,p_formula_cat)
116 AND fff.formula_id=paf.formula_id
117 AND paf.assignment_status_type_id IS NULL
118 AND fff.business_group_id=paf.business_group_id
119 AND fff.business_group_id=fnd_global.per_business_group_id
120 AND SYSDATE BETWEEN fff.effective_start_date and fff.effective_end_date
121 ORDER BY fff.formula_id,fff.effective_start_date ;
122
123
124 l_sh_formula_name pay_shadow_formulas.formula_name%TYPE;
125 l_sh_formula_text pay_shadow_formulas.formula_text%TYPE;
126 l_sh_formula_ver VARCHAR2(50);
127
128 l_formula c_formula_details%ROWTYPE;
129 l_formula_ver VARCHAR2(50);
130 l_formula_prefix VARCHAR2(50);
131 l_formula_text_buffer ff_formulas_f.formula_text%TYPE;
132
133 l_year VARCHAR2(30);
134
135
136 BEGIN
137
138 fnd_file.put_line(fnd_file.log,'p_formula_cat:'||p_formula_cat);
139 fnd_file.put_line(fnd_file.log,'p_formula_name:'||p_formula_name);
140
141
142 OPEN c_sh_formula_details;
143 LOOP
144 FETCH c_sh_formula_details INTO l_sh_formula_name,l_sh_formula_text;
145 EXIT WHEN c_sh_formula_details%NOTFOUND;
146 --l_sh_formula_ver:=substr(l_sh_formula_text,INSTR(l_sh_formula_text,'pqgbtcam.sql')+13,6);
147 l_sh_formula_ver:=get_version(l_sh_formula_text);
148 END LOOP;
149 CLOSE c_sh_formula_details;
150
151 fnd_file.put_line(fnd_file.log,'Shadow Formula Ver:'||l_sh_formula_ver);
152
153 OPEN c_formula_details;
154 LOOP
155 FETCH c_formula_details INTO l_formula;
156 EXIT WHEN c_formula_details%NOTFOUND;
157 l_formula_prefix := substr(l_formula.formula_name,0,INSTR(l_formula.formula_name,substr(p_formula_cat,2))-1);
158 --l_formula_ver := substr(l_formula.formula_text,INSTR(l_formula.formula_text,'pqgbtcam.sql')+13,6);
159 l_formula_ver := get_version(l_formula.formula_text);
160
161 fnd_file.put_line(fnd_file.log,'Formula Prefix:'||l_formula_prefix);
162 fnd_file.put_line(fnd_file.log,'Formula Ver:'||l_formula_ver);
163
164 IF version_compare(l_formula_ver,l_sh_formula_ver) THEN
165 fnd_file.put_line(fnd_file.log,'Upgrading Formula:'||l_formula.formula_name);
166 l_formula_text_buffer := replace(l_sh_formula_text,'<BASE NAME>',UPPER(REPLACE(l_formula_prefix,' ','_')));
167
168 SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
169 INTO l_year
170 FROM DUAL;
171
172 UPDATE ff_formulas_f
173 SET formula_name = l_formula.formula_name||'_'||l_year||'_BAK'
174 WHERE formula_id = l_formula.formula_id;
175
176 UPDATE ff_formulas_f_tl
177 SET formula_name = l_formula.formula_name||'_'||l_year||'_BAK'
178 WHERE formula_id = l_formula.formula_id;
179
180 INSERT INTO ff_formulas_f(
181 FORMULA_ID
182 ,EFFECTIVE_START_DATE
183 ,EFFECTIVE_END_DATE
184 ,BUSINESS_GROUP_ID
185 ,LEGISLATION_CODE
186 ,FORMULA_TYPE_ID
187 ,FORMULA_NAME
188 ,DESCRIPTION
189 ,FORMULA_TEXT
190 ,STICKY_FLAG
191 ,COMPILE_FLAG
192 ,LAST_UPDATE_DATE
193 ,LAST_UPDATED_BY
194 ,LAST_UPDATE_LOGIN
195 ,CREATED_BY
196 ,CREATION_DATE )
197 VALUES(
198 ff_formulas_s.nextval
199 ,l_formula.EFFECTIVE_START_DATE
200 ,l_formula.EFFECTIVE_end_DATE
201 ,l_formula.business_group_id
202 ,l_formula.legislation_code
203 ,l_formula.formula_type_id
204 ,l_formula.formula_name
205 ,l_formula.description
206 ,l_formula_text_buffer
207 ,l_formula.sticky_flag
208 ,l_formula.compile_flag
209 ,sysdate
210 ,1
211 ,1
212 ,1
213 ,sysdate
214 );
215
216 UPDATE pay_status_processing_rules_f
217 SET formula_id=ff_formulas_s.currval
218 WHERE status_processing_rule_id=l_formula.status_processing_rule_id
219 AND SYSDATE BETWEEN effective_start_date and effective_end_date;
220
221 fnd_file.put_line(fnd_file.output,'The Fast Formula '||l_formula.formula_name||' is successfully upgraded');
222 fnd_file.put_line(fnd_file.output,'and the backup of old formula is in ' || l_formula.formula_name||'_'||l_year||'_BAK');
223 retcode:=0;
224
225 ELSE
226 fnd_file.put_line(fnd_file.output,'Latest version of the formula '||l_formula.formula_name||' already exists.');
227 retcode:=1;
228
229 END IF;
230 END LOOP;
231 CLOSE c_formula_details;
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 fnd_file.put_line(fnd_file.output,'Formula Upgrade Failed');
236 fnd_file.put_line(fnd_file.log,substr(SQLERRM,1,200));
237 errbuf:='Formula Upgrade Failed';
238 retcode:=2;
239 END UPGRADE_FORMULA;
240
241
242 end PQP_CANDM_UPG;