[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_POPULATE_COUNTRIES
Source
1 PACKAGE BODY PAY_SE_POPULATE_COUNTRIES AS
2 /* $Header: pysepop.pkb 120.0 2005/05/29 02:17:14 appldev 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
60 BEGIN
61
62 -- Get the Table ID for the Table PER DIEM OVERSEAS NORMAL AMOUNT
63 --
64 SELECT user_table_id
65 INTO l_user_table_id
66 FROM pay_user_tables
67 WHERE user_table_name='PER DIEM OVERSEAS NORMAL AMOUNT'
68 AND legislation_code='SE';
69
70 -- Get the Column ID for the Table PER DIEM OVERSEAS NORMAL AMOUNT
71 --
72 SELECT user_column_id
73 INTO l_user_column_id
74 FROM pay_user_columns
75 WHERE user_column_name='NORMAL_AMOUNT'
76 AND legislation_code='SE';
77
78 FOR tc_rec IN c_update_territory(p_business_group_id,l_user_table_id)
79 LOOP
80 l_effective_date := TO_DATE('01010001','DDMMYYYY');
81 l_row_low_range_or_name :=' ';
82
83 /*
84 ----------------------------------------------------------
85 These are the parameters to call the PAY_USER_ROW_API.UPDATE_USER_ROW
86 P_VALIDATE BOOLEAN IN DEFAULT
87 P_EFFECTIVE_DATE DATE IN
88 P_DATETRACK_UPDATE_MODE VARCHAR2 IN
89 P_USER_ROW_ID NUMBER IN
90 P_DISPLAY_SEQUENCE NUMBER IN/OUT
91 P_OBJECT_VERSION_NUMBER NUMBER IN/OUT
92 P_ROW_LOW_RANGE_OR_NAME VARCHAR2 IN DEFAULT
93 P_DISABLE_RANGE_OVERLAP_CHECK BOOLEAN IN DEFAULT
94 P_DISABLE_UNITS_CHECK BOOLEAN IN DEFAULT
95 P_ROW_HIGH_RANGE VARCHAR2 IN DEFAULT
96 P_EFFECTIVE_START_DATE DATE OUT
97 P_EFFECTIVE_END_DATE DATE OUT
98 -------------------------------------------------------------
99 */
100
101 OPEN c_chk_territory(tc_rec.row_low_range_or_name);
102 FETCH c_chk_territory INTO l_row_low_range_or_name ;
103 CLOSE c_chk_territory;
104
105
106 PAY_USER_ROW_API.UPDATE_USER_ROW
107 ( P_VALIDATE => FALSE
108 , P_EFFECTIVE_DATE => l_effective_date
109 , P_DATETRACK_UPDATE_MODE => 'CORRECTION'
110 , P_USER_ROW_ID => tc_rec.user_row_id
111 , P_DISPLAY_SEQUENCE => tc_rec.display_sequence
112 , P_OBJECT_VERSION_NUMBER => tc_rec.object_version_number
113 , P_ROW_LOW_RANGE_OR_NAME => l_row_low_range_or_name
114 , P_DISABLE_RANGE_OVERLAP_CHECK => FALSE
115 , P_DISABLE_UNITS_CHECK => FALSE
116 , P_ROW_HIGH_RANGE => tc_rec.row_high_range
117 , P_EFFECTIVE_START_DATE => l_effective_start_date
118 , P_EFFECTIVE_END_DATE => l_effective_end_date
119 );
120
121
122 END LOOP;
123
124
125 SELECT max(DISPLAY_SEQUENCE)
126 INTO l_display_sequence
127 FROM pay_user_rows_f
128 WHERE user_table_id=l_user_table_id
129 AND business_group_id = p_business_group_id;
130
131 IF l_display_sequence IS NULL THEN
132 /*if it is for the first time make the sequence as ZERO */
133 l_display_sequence := 0;
134 END IF;
135
136 -- Open cursor c_territory
137 --
138 FOR territory_rec In c_territory(p_business_group_id,l_user_table_id)
139 LOOP
140
141 --Initializing the Variables
142 --
143 l_row_id :=0;
144 l_user_row_id :=0;
145 l_user_column_instance_id := NULL;
146
147 /*
148 ----------------------------------------------------------
149 These are the parmeters to call the PAY_USER_ROW_API.CREATE_USER_ROW
150 p_validate in boolean default false
151 ,p_effective_date in date
152 ,p_user_table_id in number
153 ,p_row_low_range_or_name in varchar2
154 ,p_display_sequence in out nocopy number
155 ,p_business_group_id in number default null
156 ,p_legislation_code in varchar2 default null
157 ,p_disable_range_overlap_check in boolean default false
158 ,p_disable_units_check in boolean default false
159 ,p_row_high_range in varchar2 default null
160 ,p_user_row_id out nocopy number
161 ,p_object_version_number out nocopy number
162 ,p_effective_start_date out nocopy date
163 ,p_effective_end_date out nocopy date
164 -------------------------------------------------------------
165 */
166
167 -- Call the Procedure pay_user_row_api.create_user_row to insert
168 --rows into the Table
169 --
170 l_display_sequence := l_display_sequence + 1 ;
171 l_effective_date := TO_DATE('01010001','DDMMYYYY');
172
173 PAY_USER_ROW_API.CREATE_USER_ROW
174 (p_validate => FALSE
175 ,p_effective_date => l_effective_date
176 ,p_user_table_id => l_user_table_id
177 ,p_row_low_range_or_name => territory_rec.territory_short_name
178 ,p_display_sequence => l_display_sequence
179 ,p_business_group_id => p_business_group_id
180 ,p_legislation_code => NULL
181 ,p_disable_range_overlap_check => FALSE
182 ,p_disable_units_check => FALSE
183 ,p_row_high_range => NULL
184 ,p_user_row_id => l_user_row_id
185 ,p_object_version_number => l_object_version_number
186 ,p_effective_start_date => l_effective_start_date
187 ,p_effective_end_date => l_effective_end_date
188 ) ;
189 /*
190 ----------------------------------------------------------
191 These are the parmeters to call the
192 PAY_USER_COLUMN_INSTANCES_PKG.INSERT_ROW
193 p_rowid in out varchar2,
194 p_user_column_instance_id in out number,
195 p_effective_start_date in date,
196 p_effective_end_date in date,
197 p_user_row_id in number,
198 p_user_column_id in number,
199 p_business_group_id in number,
200 p_legislation_code in varchar2,
201 p_legislation_subgroup in varchar2,
202 p_value in varchar2 )
203 -------------------------------------------------------------
204 */
205
206 -- Call the Procedure PAY_USER_COLUMN_INSTANCES_PKG.INSERT_ROW to
207 -- insert rows into the Table
208 --
209 l_row_id1:=TO_CHAR( l_row_id);
210
211 PAY_USER_COLUMN_INSTANCES_PKG.insert_row
212 ( p_rowid => l_row_id1
213 ,p_user_column_instance_id => l_user_column_instance_id
214 ,p_effective_start_date => l_effective_start_date
215 ,p_effective_end_date => l_effective_end_date
216 ,p_user_row_id => l_user_row_id
217 ,p_user_column_id => l_user_column_id
218 ,p_business_group_id => p_business_group_id
219 ,p_legislation_code => NULL
220 ,p_legislation_subgroup => NULL
221 ,p_value => NULL
222 );
223
224 END LOOP;
225 EXCEPTION
226 WHEN OTHERS THEN
227
228 p_errbuf := NULL;
229 p_retcode := 2;
230 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
231 END POPULATE_COUNTRIES;
232
233 END PAY_SE_POPULATE_COUNTRIES;