[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_VAT_REP_ENTITIES_PKG
Source
1 package body jg_zz_vat_rep_entities_pkg as
2 /*$Header: jgzzvreb.pls 120.3.12010000.2 2008/12/01 13:49:59 rshergil ship $*/
3 /* CHANGE HISTORY ------------------------------------------------------------------------------------------
4 DATE AUTHOR VERSION BUG NO. DESCRIPTION
5 (DD/MM/YYYY) (UID)
6 ------------------------------------------------------------------------------------------------------------
7 23/3/2006 BRATHOD 120.1 As per the change in approach new columns are
8 added to jg_zz_vat_rep_entities table. API is
9 modified to support these new columns
10
11 23/6/2006 BRATHOD 120.2 5166688 Modified the signature of INSERT_ROW procedure in
12 to return rowid to caller of API by adding out
13 parameter in the call. Refer bug# 5166688 for details
14
15 04/07/2006 KASBALAS 120.3 5398572 Modified the insert_row to insert the Driving
16 Date code as well.Refer Bug # 5398572 for details.
17 -----------------------------------------------------------------------------------------------------------*/
18
19 procedure insert_row
20 ( x_record in jg_zz_vat_rep_entities%rowtype
21 , x_vat_reporting_entity_id out nocopy jg_zz_vat_rep_entities.vat_reporting_entity_id%type
22 , x_row_id out nocopy rowid
23 )
24 is
25
26 cursor c_gen_vat_reporting_entity_id
27 is
28 select jg_zz_vat_rep_entities_s.nextval
29 from dual;
30
31 lv_entity_type_code jg_zz_vat_rep_entities.entity_type_code%type;
32
33 begin
34
35 if x_record.vat_reporting_entity_id is null then
36 /* Generate a new VAT_REPORTING_ENTITY_ID if not provied in procedure arguments */
37 open c_gen_vat_reporting_entity_id;
38 fetch c_gen_vat_reporting_entity_id into x_vat_reporting_entity_id;
39 close c_gen_vat_reporting_entity_id;
40 else
41 /* Use the allocation_rule_id given in the procedure arguments */
42 x_vat_reporting_entity_id := x_record.vat_reporting_entity_id;
43 end if;
44
45 if x_record.entity_type_code is null then
46 /*
47 Entity_type_code is not available, hence assign a default value using entity_level_code
48 Entity Level = LE implies type = LEGAL, otherwise it should be ACCOUNTING
49 */
50 if x_record.entity_level_code = 'LE' then
51 lv_entity_type_code := 'LEGAL';
52 else
53 lv_entity_type_code := 'ACCOUNTING';
54 end if;
55
56 else
57 /* Use entity_type_code available in API argument */
58 lv_entity_type_code := x_record.entity_type_code ;
59 end if;
60
61 insert into jg_zz_vat_rep_entities
62 ( vat_reporting_entity_id
63 , legal_entity_id
64 , party_id
65 , tax_regime_code
66 , tax_registration_number
67 , tax_calendar_name
68 , enable_allocations_flag
69 , enable_annual_allocation_flag
70 , enable_registers_flag
71 , enable_report_sequence_flag
72 , threshold_amount
73 , created_by
74 , creation_date
75 , last_updated_by
76 , last_update_date
77 , last_update_login
78 , entity_type_code
79 , entity_level_code
80 , ledger_id
81 , balancing_segment_value
82 , mapping_vat_rep_entity_id
83 , entity_identifier
84 , driving_date_code
85 )
86 values ( x_vat_reporting_entity_id
87 , x_record.legal_entity_id
88 , x_record.party_id
89 , x_record.tax_regime_code
90 , x_record.tax_registration_number
91 , x_record.tax_calendar_name
92 , x_record.enable_allocations_flag
93 , x_record.enable_annual_allocation_flag
94 , x_record.enable_registers_flag
95 , x_record.enable_report_sequence_flag
96 , x_record.threshold_amount
97 , x_record.created_by
98 , x_record.creation_date
99 , x_record.last_updated_by
100 , x_record.last_update_date
101 , x_record.last_update_login
102 , lv_entity_type_code
103 , x_record.entity_level_code
104 , x_record.ledger_id
105 , x_record.balancing_segment_value
106 , x_record.mapping_vat_rep_entity_id
107 , x_record.entity_identifier
108 , x_record.driving_date_code
109 ) returning rowid into x_row_id ;
110
111 if x_record.entity_identifier is null then
112 jg_zz_vat_rep_entities_pkg.update_entity_identifier
113 ( pn_vat_reporting_entity_id => x_vat_reporting_entity_id
114 , pv_entity_level_code => null
115 , pn_ledger_id => null
116 , pv_balancing_segment_value => null
117 , pv_called_from => 'TABLE_HANDLER'
118 );
119 end if;
120
121 exception
122 when others then
123 x_vat_reporting_entity_id := null;
124 x_row_id:= null;
125 raise;
126 end insert_row;
127
128 /*------------------------------------------------------------------------------------------------------------*/
129
130 procedure lock_row
131 ( x_row_id in rowid
132 , x_record in jg_zz_vat_rep_entities%rowtype
133 )
134 is
135
136 cursor c_locked_row is
137 select jzvrc.*
138 from jg_zz_vat_rep_entities jzvrc
139 where rowid = x_row_id
140 for update nowait;
141
142 lr_locked_row JG_ZZ_VAT_REP_ENTITIES%rowtype;
143
144 begin
145
146 open c_locked_row;
147 fetch c_locked_row into lr_locked_row;
148
149 if (c_locked_row%notfound) then
150 close c_locked_row;
151 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
152 app_exception.raise_exception;
153 end if;
154
155 close c_locked_row;
156 if ( lr_locked_row.vat_reporting_entity_id = x_record.vat_reporting_entity_id
157 and lr_locked_row.legal_entity_id = x_record.legal_entity_id
158 and lr_locked_row.party_id = x_record.party_id
159 and nvl(lr_locked_row.tax_regime_code,'X$')= nvl(x_record.tax_regime_code,'X$')
160 and lr_locked_row.tax_registration_number = x_record.tax_registration_number
161 and nvl(lr_locked_row.tax_calendar_name,'X$') = nvl(x_record.tax_calendar_name,'X$')
162 and nvl(lr_locked_row.enable_allocations_flag,'N') = nvl(x_record.enable_allocations_flag,'N')
163 and nvl(lr_locked_row.enable_annual_allocation_flag,'N') = nvl(x_record.enable_annual_allocation_flag,'N')
164 and nvl(lr_locked_row.enable_registers_flag,'N') = nvl(x_record.enable_registers_flag,'N')
165 and nvl(lr_locked_row.enable_report_sequence_flag,'N') = nvl(x_record.enable_report_sequence_flag,'N')
166 and nvl(lr_locked_row.threshold_amount,0) = nvl(x_record.threshold_amount,0)
167 and nvl(lr_locked_row.entity_type_code,'X$') = nvl(x_record.entity_type_code, 'X$')
168 and nvl(lr_locked_row.entity_level_code,'X$') = nvl(x_record.entity_level_code,'X$')
169 and nvl(lr_locked_row.ledger_id ,0) = nvl(x_record.ledger_id,0)
170 and nvl(lr_locked_row.balancing_segment_value,'X$') = nvl(x_record.balancing_segment_value,'X$')
171 and nvl(lr_locked_row.mapping_vat_rep_entity_id,0) = nvl(x_record.mapping_vat_rep_entity_id ,0)
172 and nvl(lr_locked_row.entity_identifier,'X$') = nvl(x_record.entity_identifier,'X$')
173 )
174 then
175 return;
176 else
177 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
178 app_exception.raise_exception;
179 end if;
180 end lock_row;
181
182 /*------------------------------------------------------------------------------------------------------------*/
183
184 procedure update_row( x_record in jg_zz_vat_rep_entities%rowtype
185 )
186 is
187
188 le_no_rows_updated exception;
189
190 begin
191
192 update jg_zz_vat_rep_entities
193 set legal_entity_id = x_record.legal_entity_id
194 , party_id = x_record.party_id
195 , tax_regime_code = x_record.tax_regime_code
196 , tax_registration_number = x_record.tax_registration_number
197 , tax_calendar_name = x_record.tax_calendar_name
198 , enable_allocations_flag = x_record.enable_allocations_flag
199 , enable_annual_allocation_flag = x_record.enable_annual_allocation_flag
200 , enable_registers_flag = x_record.enable_registers_flag
201 , enable_report_sequence_flag = x_record.enable_report_sequence_flag
202 , threshold_amount = x_record.threshold_amount
203 , created_by = x_record.created_by
204 , creation_date = x_record.creation_date
205 , last_updated_by = x_record.last_updated_by
206 , last_update_date = x_record.last_update_date
207 , last_update_login = x_record.last_update_login
208 , entity_type_code = x_record.entity_type_code
209 , entity_level_code = x_record.entity_level_code
210 , ledger_id = x_record.ledger_id
211 , balancing_segment_value = x_record.balancing_segment_value
212 , mapping_vat_rep_entity_id = x_record.mapping_vat_rep_entity_id
213 , entity_identifier = x_record.entity_identifier
214 , driving_date_code =
215 x_record.driving_date_code
216 where vat_reporting_entity_id = x_record.vat_reporting_entity_id;
217
218 end update_row;
219
220 /*------------------------------------------------------------------------------------------------------------*/
221
222 procedure delete_row(x_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type)
223 is
224 le_no_rows_deleted exception;
225 begin
226
227 delete from jg_zz_vat_rep_entities
228 where vat_reporting_entity_id = x_vat_reporting_entity_id;
229
230 end delete_row;
231
232 /*------------------------------------------------------------------------------------------------------------*/
233
234 procedure update_entity_identifier
235 ( pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type
236 , pv_entity_level_code in jg_zz_vat_rep_entities.entity_level_code%type default null
237 , pn_ledger_id in jg_zz_vat_rep_entities.ledger_id%type default null
238 , pv_balancing_segment_value in jg_zz_vat_rep_entities.balancing_segment_value%type default null
239 , pv_called_from in varchar2
240 )
241 is
242
243 lv_entity_identifier jg_zz_vat_rep_entities.entity_identifier%type;
244
245 begin
246 /* Calling utility package to generate entity identifier */
247 lv_entity_identifier := jg_zz_vat_rep_utility.get_reporting_identifier
248 ( pn_vat_reporting_entity_id => pn_vat_reporting_entity_id
249 , pv_entity_level_code => pv_entity_level_code
250 , pn_ledger_id => pn_ledger_id
251 , pv_balancing_segment_value => pv_balancing_segment_value
252 , pv_called_from => pv_called_from
253 );
254
255 update jg_zz_vat_rep_entities
256 set entity_identifier = lv_entity_identifier
257 where vat_reporting_entity_id = pn_vat_reporting_entity_id;
258
259 end update_entity_identifier;
260
261 end jg_zz_vat_rep_entities_pkg;
262