1 package body pay_dyndbi_changes_pkg as
2 /* $Header: pydbichg.pkb 120.3 2008/04/25 10:05:03 ubhat noship $ */
3
4 ------------------------------- insert_row -------------------------------
5 procedure insert_row
6 (p_id in varchar2
7 ,p_type in varchar2
8 ,p_language in varchar2
9 ) is
10 begin
11 insert into pay_dyndbi_changes
12 (id
13 ,type
14 ,language
15 )
16 select p_id
17 , p_type
18 , p_language
19 from dual
20 where not exists
21 (
22 select null
23 from pay_dyndbi_changes pdc
24 where pdc.id = p_id
25 and pdc.type = p_type
26 and pdc.language = p_language
27 );
28 end insert_row;
29
30 ------------------------------ insert_rows -------------------------------
31 procedure insert_rows
32 (p_id in varchar2
33 ,p_type in varchar2
34 ,p_languages in dbms_sql.varchar2s
35 ) is
36 begin
37 for i in 1 .. p_languages.count loop
38 insert into pay_dyndbi_changes
39 (id
40 ,type
41 ,language
42 )
43 select p_id
44 , p_type
45 , p_languages(i)
46 from dual
47 where not exists
48 (
49 select null
50 from pay_dyndbi_changes pdc
51 where pdc.id = p_id
52 and pdc.type = p_type
53 and pdc.language = p_languages(i)
54 );
55 end loop;
56 end insert_rows;
57
58 -------------------------- element_type_change ---------------------------
59 procedure element_type_change
60 (p_element_type_id in number
61 ,p_languages in dbms_sql.varchar2s
62 ) is
63 --
64 -- Cursor for getting input values. Note: only need to fetch values
65 -- with GENERATE_DB_ITEMS_FLAG = 'Y'. Also, use the maximum effective
66 -- end date as this is what is done in HRDYNDBI.
67 --
68 cursor csr_input_values
69 (p_element_type_id in number
70 ) is
71 select iv.input_value_id
72 from pay_input_values_f iv
73 where iv.element_type_id = p_element_type_id
74 and iv.generate_db_items_flag = 'Y'
75 and iv.effective_end_date =
76 (
77 select max(iv1.effective_end_date)
78 from pay_input_values_f iv1
79 where iv1.input_value_id = iv.input_value_id
80 )
81 ;
82 begin
83 -- First handle the element type itself.
84 insert_rows
85 (p_id => p_element_type_id
86 ,p_type => C_ELEMENT_TYPE
87 ,p_languages => p_languages
88 );
89 -- Handle the input values for the element type.
90 for iv in csr_input_values(p_element_type_id) loop
91 insert_rows
92 (p_id => iv.input_value_id
93 ,p_type => C_INPUT_VALUE
94 ,p_languages => p_languages
95 );
96 end loop;
97 end element_type_change;
98
99 -------------------------- balance_type_change ---------------------------
100 procedure balance_type_change
101 (p_balance_type_id in number
102 ,p_languages in dbms_sql.varchar2s
103 ) is
104 cursor csr_defined_balances
105 (p_balance_type_id in number
106 ) is
107 select db.defined_balance_id
108 from pay_defined_balances db
109 where db.balance_type_id = p_balance_type_id
110 ;
111 begin
112 for db in csr_defined_balances(p_balance_type_id) loop
113 insert_rows
114 (p_id => db.defined_balance_id
115 ,p_type => C_DEFINED_BALANCE
116 ,p_languages => p_languages
117 );
118 end loop;
119 end balance_type_change;
120
121 --------------------------- input_value_change ---------------------------
122 procedure input_value_change
123 (p_input_value_id in number
124 ,p_languages in dbms_sql.varchar2s
125 ) is
126 cursor csr_input_value(p_input_value_id in number) is
127 select null
128 from pay_input_values_f iv
129 where iv.input_value_id = p_input_value_id
130 and iv.generate_db_items_flag = 'Y'
131 and iv.effective_end_date =
132 (
133 select max(iv1.effective_end_date)
134 from pay_input_values_f iv1
135 where iv1.input_value_id = p_input_value_id
136 )
137 ;
138 --
139 l_generate varchar2(10);
140 begin
141 open csr_input_value(p_input_value_id => p_input_value_id);
142 fetch csr_input_value
143 into l_generate;
144 if csr_input_value%found then
145 insert_rows
146 (p_id => p_input_value_id
147 ,p_type => C_INPUT_VALUE
148 ,p_languages => p_languages
149 );
150 end if;
151 close csr_input_value;
152 end input_value_change;
153
154 ------------------------ balance_dimension_change ------------------------
155 procedure balance_dimension_change
156 (p_balance_dimension_id in number
157 ,p_languages in dbms_sql.varchar2s
158 ) is
159 cursor csr_defined_balances
160 (p_balance_dimension_id in number
161 ) is
162 select db.defined_balance_id
163 from pay_defined_balances db
164 where db.balance_dimension_id = p_balance_dimension_id
165 ;
166 begin
167 for db in csr_defined_balances(p_balance_dimension_id) loop
168 insert_rows
169 (p_id => db.defined_balance_id
170 ,p_type => C_DEFINED_BALANCE
171 ,p_languages => p_languages
172 );
173 end loop;
174 end balance_dimension_change;
175
176 ------------------------------- delete_row -------------------------------
177 procedure delete_row
178 (p_id in varchar2
179 ,p_type in varchar2
180 ,p_language in varchar2
181 ) is
182 begin
183 delete from pay_dyndbi_changes pdc
184 where pdc.id = p_id
185 and pdc.type = p_type
186 and pdc.language = p_language
187 ;
188 end delete_row;
189
190 ------------------------------ delete_rows -------------------------------
191 procedure delete_rows
192 (p_id in number
193 ,p_type in varchar2
194 ) is
195 begin
196 delete from pay_dyndbi_changes pdc
197 where pdc.id = p_id
198 and pdc.type = p_type
199 ;
200 end delete_rows;
201
202 end pay_dyndbi_changes_pkg;
[Home] [Help]