1 PACKAGE BODY PER_FI_POPULATE_COUNTRIES AS
2 /* $Header: perfipop.pkb 120.4 2008/04/04 09:32:25 rsengupt noship $ */
3 --
4 -- Purpose: to insert the countries into user table
5
6 PROCEDURE POPULATE_COUNTRIES
7 (p_errbuf OUT nocopy VARCHAR2
8 ,p_retcode OUT nocopy NUMBER
9 ,p_business_group_id IN NUMBER )
10 AS
11 -- Cursor for fetching territory_short_name from fnd_territories_vl.
12 --
13 CURSOR c_territory (p_bg NUMBER,p_table_id NUMBER) IS
14 SELECT TERRITORY_CODE||' - '||territory_short_name territory_short_name
15 FROM fnd_territories_vl
16 WHERE UPPER(TERRITORY_CODE||' - '||territory_short_name) NOT IN
17 ( SELECT UPPER(row_low_range_or_name)
18 FROM pay_user_rows_f
19 WHERE user_table_id=p_table_id
20 AND business_group_id = p_bg )
21 ORDER BY TERRITORY_CODE;
22
23 CURSOR c_update_territory(p_bg NUMBER,p_table_id NUMBER) IS
24 SELECT user_row_id
25 ,display_sequence
26 ,object_version_number
27 ,UPPER(row_low_range_or_name) row_low_range_or_name
28 ,row_high_range
29 FROM pay_user_rows_f
30 WHERE user_table_id=p_table_id
31 AND business_group_id = p_bg
32 AND UPPER(row_low_range_or_name) IN
33 (SELECT UPPER(territory_short_name)
34 FROM fnd_territories_vl );
35
36 CURSOR c_chk_territory(p_row_low_range_or_name VARCHAR2 ) IS
37 SELECT UPPER(TERRITORY_CODE||' - '||territory_short_name) territory_short_name
38 FROM fnd_territories_vl
39 WHERE UPPER(territory_short_name) = p_row_low_range_or_name ;
40
41
42
43 -- Local variables declared.
44 --
45 l_region_code FND_TERRITORIES_TL.TERRITORY_CODE%type;
46 l_region_name FND_TERRITORIES_TL.TERRITORY_SHORT_NAME%type;
47 l_user_table_id PAY_USER_TABLES.USER_TABLE_ID%type;
48 l_user_column_id PAY_USER_COLUMNS.USER_COLUMN_ID%type;
49 l_row_id NUMBER;
50 l_row_id1 VARCHAR2(4000);
51 l_user_row_id NUMBER;
52 l_user_column_instance_id NUMBER;
53 l_effective_start_date DATE;
54 l_effective_end_date DATE;
55 l_effective_date DATE;
56 l_object_version_number NUMBER;
57 l_display_sequence NUMBER;
58 l_row_low_range_or_name pay_user_rows_f.ROW_LOW_RANGE_OR_NAME%TYPE;
59 l_BASE_ROW_LOW_RANGE_OR_NAME pay_user_rows_f.ROW_LOW_RANGE_OR_NAME%TYPE;
60
61 BEGIN
62
63 -- Get the Table ID for the Table FI_REGIONAL_MEMBERSHIP
64 --
65 SELECT user_table_id
66 INTO l_user_table_id
67 FROM pay_user_tables
68 WHERE user_table_name='FI_REGIONAL_MEMBERSHIP'
69 AND legislation_code='FI';
70
71 -- Get the Column ID for the Table FI_REGIONAL_MEMBERSHIP
72 --
73 SELECT user_column_id
74 INTO l_user_column_id
75 FROM pay_user_columns
76 WHERE user_column_name='REGIONAL MEMBERSHIP'
77 AND legislation_code='FI';
78
79 FOR tc_rec IN c_update_territory(p_business_group_id,l_user_table_id)
80 LOOP
81 l_effective_date := TO_DATE('01010001','DDMMYYYY');
82 l_row_low_range_or_name :=' ';
83 l_BASE_ROW_LOW_RANGE_OR_NAME :=' ';
84
85 /*
86 ----------------------------------------------------------
87 These are the parmeters to call the PAY_USER_ROW_API.UPDATE_USER_ROW
88 P_VALIDATE BOOLEAN IN DEFAULT
89 P_EFFECTIVE_DATE DATE IN
90 P_DATETRACK_UPDATE_MODE VARCHAR2 IN
91 P_USER_ROW_ID NUMBER IN
92 P_DISPLAY_SEQUENCE NUMBER IN/OUT
93 P_OBJECT_VERSION_NUMBER NUMBER IN/OUT
94 P_ROW_LOW_RANGE_OR_NAME VARCHAR2 IN DEFAULT
95 P_BASE_ROW_LOW_RANGE_OR_NAME VARCHAR2 IN DEFAULT
96 P_DISABLE_RANGE_OVERLAP_CHECK BOOLEAN IN DEFAULT
97 P_DISABLE_UNITS_CHECK BOOLEAN IN DEFAULT
98 P_ROW_HIGH_RANGE VARCHAR2 IN DEFAULT
99 P_EFFECTIVE_START_DATE DATE OUT
100 P_EFFECTIVE_END_DATE DATE OUT
101 -------------------------------------------------------------
102 */
103
104 OPEN c_chk_territory(tc_rec.row_low_range_or_name);
105 FETCH c_chk_territory INTO l_row_low_range_or_name ;
106 CLOSE c_chk_territory;
107
108
109 PAY_USER_ROW_API.UPDATE_USER_ROW
110 ( FALSE
111 , l_effective_date
112 , 'CORRECTION'
113 , tc_rec.user_row_id
114 , tc_rec.display_sequence
115 , tc_rec.object_version_number
116 , l_row_low_range_or_name
117 , l_BASE_ROW_LOW_RANGE_OR_NAME
118 , FALSE
119 , FALSE
120 , tc_rec.row_high_range
121 , l_effective_start_date
122 , l_effective_end_date
123 );
124
125
126 END LOOP;
127
128
129 SELECT max(DISPLAY_SEQUENCE)
130 INTO l_display_sequence
131 FROM pay_user_rows_f
132 WHERE user_table_id=l_user_table_id
133 AND business_group_id = p_business_group_id;
134
135 IF l_display_sequence IS NULL THEN
136 /*if it is for the first time make the sequence as ZERO */
137 l_display_sequence := 0;
138 END IF;
139
140 -- Open cursor c_territory
141 --
142 FOR territory_rec In c_territory(p_business_group_id,l_user_table_id)
143 LOOP
144
145 --Initializing the Variables
146 --
147 l_row_id :=0;
148 l_user_row_id :=0;
149 l_user_column_instance_id := NULL;
150
151 /*
152 ----------------------------------------------------------
153 These are the parmeters to call the PAY_USER_ROW_API.CREATE_USER_ROW
154 p_validate in boolean default false
155 ,p_effective_date in date
156 ,p_user_table_id in number
157 ,p_row_low_range_or_name in varchar2
158 ,p_display_sequence in out nocopy number
159 ,p_business_group_id in number default null
160 ,p_legislation_code in varchar2 default null
161 ,p_disable_range_overlap_check in boolean default false
162 ,p_disable_units_check in boolean default false
163 ,p_row_high_range in varchar2 default null
164 ,p_user_row_id out nocopy number
165 ,p_object_version_number out nocopy number
166 ,p_effective_start_date out nocopy date
167 ,p_effective_end_date out nocopy date
168 ,P_BASE_ROW_LOW_RANGE_OR_NAME IN VARCHAR2 default -- Not included in the call (NULL by default)
169
170 --Please refer bug 6908057 as the argument P_BASE_ROW_LOW_RANGE_OR_NAME has been removed
171 -------------------------------------------------------------
172 */
173
174 -- Call the Procedure pay_user_row_api.create_user_row to insert
175 --rows into the Table
176 --
177 l_display_sequence := l_display_sequence + 1 ;
178 l_effective_date := TO_DATE('01010001','DDMMYYYY');
179
180 /* changed to named arguments w.r.t Bug 6908057 */
181
182 PAY_USER_ROW_API.CREATE_USER_ROW
183 (p_validate => FALSE
184 ,p_effective_date => l_effective_date
185 ,p_user_table_id => l_user_table_id
186 ,p_row_low_range_or_name => territory_rec.territory_short_name
187 ,p_display_sequence => l_display_sequence
188 ,p_business_group_id => p_business_group_id
189 ,p_legislation_code => NULL
190 ,p_disable_range_overlap_check => FALSE
191 ,p_disable_units_check => FALSE
192 ,p_row_high_range => NULL
193 ,p_user_row_id => l_user_row_id
194 ,p_object_version_number => l_object_version_number
195 ,p_effective_start_date => l_effective_start_date
196 ,p_effective_end_date => l_effective_end_date
197 ) ;
198 /*
199 ----------------------------------------------------------
200 These are the parmeters to call the
201 PAY_USER_COLUMN_INSTANCES_PKG.INSERT_ROW
202 p_rowid in out varchar2,
203 p_user_column_instance_id in out number,
204 p_effective_start_date in date,
205 p_effective_end_date in date,
206 p_user_row_id in number,
207 p_user_column_id in number,
208 p_business_group_id in number,
209 p_legislation_code in varchar2,
210 p_legislation_subgroup in varchar2,
211 p_value in varchar2 )
212 -------------------------------------------------------------
213 */
214
215 -- Call the Procedure PAY_USER_COLUMN_INSTANCES_PKG.INSERT_ROW to
216 -- insert rows into the Table
217 --
218 l_row_id1:=TO_CHAR( l_row_id);
219
220 PAY_USER_COLUMN_INSTANCES_PKG.insert_row
221 ( l_row_id1
222 ,l_user_column_instance_id
223 ,l_effective_start_date
224 ,l_effective_end_date
225 ,l_user_row_id
226 ,l_user_column_id
227 ,p_business_group_id
228 ,NULL
229 ,NULL
230 ,NULL
231 );
232
233 END LOOP;
234 EXCEPTION
235 WHEN OTHERS THEN
236
237 p_errbuf := NULL;
238 p_retcode := 2;
239 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
240 END POPULATE_COUNTRIES;
241
242 END PER_FI_POPULATE_COUNTRIES;
[Home] [Help]