[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_VAT_REP_FINAL_REPORTING
Source
1 package body jg_zz_vat_rep_final_reporting as
2 /* $Header: jgzzvatfinalprcb.pls 120.2.12010000.4 2008/08/04 13:59:43 vgadde ship $*/
3
4 /* --------------------------------------------------------------------------
5 CHANGE HISTORY:
6 S.No Date Author and Details
7
8 1. 25-jan-2006 Aparajita. Created Version#120.0.
9
10 29-Mar-2006 Aparajita. Modified for revised approach.Version#120.1
11
12 28-Apr-2006 Aparajita. Version#120.2
13 Modified xv_errbuf to be of varchar2 type.
14 2. 16-Apr-2008 Spasupun 120.3
15 Bug 6835573. Done the required changes to final reporting
16 process to stop doing final reporting for periods before
17 last reported period in R11i. The process allows final
18 reporting only for periods after last reported period in r11i.
19 The last reported period will be available in rep entities
20 table for each accouting reporting entity.
21
22 -------------------------------------------------------------------------- */
23
24
25 /* ================================== Start of execute_final_reporting =============================== */
26
27 procedure execute_final_reporting
28 (
29 xv_errbuf out nocopy varchar2, /* out parameter for concurrent program */
30 xv_retcode out nocopy varchar2, /* out parameter for concurrent program */
31 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
32 pv_tax_calendar_period in gl_periods.period_name%type,
33 pv_source in varchar2
34 )
35 is
36
37 ln_reporting_status_id_ap jg_zz_vat_rep_status.reporting_status_id%type;
38 ln_reporting_status_id_ar jg_zz_vat_rep_status.reporting_status_id%type;
39 ln_reporting_status_id_gl jg_zz_vat_rep_status.reporting_status_id%type;
40 lv_return_status varchar2(1);
41 lv_return_message varchar2(254);
42 ln_final_reporting_id number;
43 lv_enable_allocations_flag jg_zz_vat_rep_entities.enable_allocations_flag%type;
44
45 /* Revised approach change */
46 ltn_application_id zx_extract_pkg.application_id_tbl;
47 ltv_entity_code zx_extract_pkg.entity_code_tbl;
48 ltv_event_class_code zx_extract_pkg.event_class_code_tbl;
49 ltn_trx_id zx_extract_pkg.trx_id_tbl;
50 ltn_trx_line_id zx_extract_pkg.trx_line_id_tbl;
51 ltn_internal_organization_id zx_extract_pkg.internal_organization_id_tbl;
52 ltn_tax_line_id zx_extract_pkg.tax_line_id_tbl;
53
54 ln_msg_count number;
55 lv_msg_data varchar2(254);
56
57 cursor c_jg_zz_vat_trx_details
58 (cpn_vat_reporting_entity_id number, cpv_tax_calendar_period varchar2, cpv_source varchar2) is
59 select
60 application_id ,
61 entity_code ,
62 event_class_code ,
63 trx_id ,
64 trx_line_id ,
65 internal_organization_id ,
66 tax_line_id
67 from
68 jg_zz_vat_trx_details
69 where
70 reporting_status_id in
71 (
72 select reporting_status_id
73 from jg_zz_vat_rep_status
74 Where vat_reporting_entity_id = cpn_vat_reporting_entity_id
75 and tax_calendar_period = cpv_tax_calendar_period
76 and ( (cpv_source = 'ALL') OR (cpv_source <> 'ALL' and source = cpv_source) )
77 );
78
79 /* Revised approach change */
80
81 cursor c_jg_zz_vat_rep_entities (pn_vat_reporting_entity_id number) is
82 select enable_allocations_flag
83 from jg_zz_vat_rep_entities
84 where vat_reporting_entity_id = pn_vat_reporting_entity_id;
85
86 cursor c_get_final_reporting_id is
87 Select jg_zz_vat_rep_status_s3.nextval
88 From dual;
89
90
91 cur_rec_jg_zz_vat_rep_entities c_jg_zz_vat_rep_entities%rowtype;
92
93 CURSOR c_get_last_rep_period_status (pn_vat_reporting_entity_id number,pn_period varchar2) is
94 SELECT 'Y'
95 FROM JG_ZZ_VAT_REP_STATUS RPS
96 WHERE RPS.VAT_REPORTING_ENTITY_ID= pn_vat_reporting_entity_id
97 AND RPS.TAX_CALENDAR_PERIOD = pn_period
98 AND RPS.period_start_date >
99 NVL((SELECT glp.end_date
100 FROM jg_zz_vat_rep_entities legal
101 ,jg_zz_vat_rep_entities acct
102 ,gl_periods glp
103 WHERE acct.entity_type_code='ACCOUNTING'
104 AND acct.vat_reporting_entity_id = pn_vat_reporting_entity_id
105 AND acct.mapping_vat_rep_entity_id= legal.vat_reporting_entity_id
106 AND glp.period_set_name = legal.tax_calendar_name
107 AND glp.period_name = acct.last_reported_period),RPS.period_start_date-1)
108 AND rownum=1;
109
110 l_last_reoprted_status VARCHAR2(100);
111 l_entity_level_code JG_ZZ_VAT_REP_ENTITIES.entity_level_code%TYPE;
112
113
114 begin
115
116 SELECT entity_level_code
117 INTO l_entity_level_code
118 FROM jg_zz_vat_rep_entities
119 WHERE vat_reporting_entity_id =pn_vat_reporting_entity_id;
120
121 IF l_entity_level_code='LEDGER' or l_entity_level_code='BSV' THEN
122
123 /* Check if the period is before last reported period in R111i. If yes
124 don't allow the final reporting process.
125 l_last_reoprted_status will be NULL, if the period is before
126 last reported period in R111i
127 */
128
129 OPEN c_get_last_rep_period_status( pn_vat_reporting_entity_id,
130 pv_tax_calendar_period);
131 FETCH c_get_last_rep_period_status INTO l_last_reoprted_status;
132 CLOSE c_get_last_rep_period_status;
133
134 IF l_last_reoprted_status IS NULL THEN
135
136 FND_MESSAGE.SET_NAME('JG','JG_ZZ_VAT_FINAL_NOT_ALLOWED');
137 xv_errbuf := FND_MESSAGE.GET;
138 xv_retcode := 2;
139 goto exit_from_procedure;
140
141 END IF;
142
143 END IF;
144
145 /* Invoke the utility API to checck if final reporting can take place */
146
147 jg_zz_vat_rep_utility.validate_process_initiation
148 (
149 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id,
150 pv_tax_calendar_period => pv_tax_calendar_period,
151 pv_source => pv_source,
152 pv_process_name => 'FINAL REPORTING',
153 xn_reporting_status_id_ap => ln_reporting_status_id_ap,
154 xn_reporting_status_id_ar => ln_reporting_status_id_ar,
155 xn_reporting_status_id_gl => ln_reporting_status_id_gl,
156 xv_return_status => lv_return_status,
157 xv_return_message => lv_return_message
158 );
159
160 if lv_return_status <> fnd_api.g_ret_sts_success then
161 xv_errbuf := lv_return_message;
162 xv_retcode := 2;
163 goto exit_from_procedure;
164 end if;
165
166 /* Check if Allocation is disabled, if disabled this needs to be updated at final reporting */
167 open c_jg_zz_vat_rep_entities(pn_vat_reporting_entity_id );
168 fetch c_jg_zz_vat_rep_entities into cur_rec_jg_zz_vat_rep_entities;
169 close c_jg_zz_vat_rep_entities;
170
171 if cur_rec_jg_zz_vat_rep_entities.enable_allocations_flag = 'X' then
172 lv_enable_allocations_flag := 'X';
173 end if;
174
175 /* Revised approach change */
176 open c_jg_zz_vat_trx_details(pn_vat_reporting_entity_id, pv_tax_calendar_period, pv_source);
177 fetch c_jg_zz_vat_trx_details bulk collect into
178 ltn_application_id ,
179 ltv_entity_code ,
180 ltv_event_class_code ,
181 ltn_trx_id ,
182 ltn_trx_line_id ,
183 ltn_internal_organization_id ,
184 ltn_tax_line_id ;
185 close c_jg_zz_vat_trx_details;
186
187
188 /* Call the eBtax API to update transactions in eBtax as finally reported */
189 zx_extract_pkg.zx_upd_legal_reporting_status
190 (
191 p_api_version => jg_zz_vat_rep_final_reporting.gn_api_version ,
192 p_init_msg_list => fnd_api.g_false ,
193 p_commit => fnd_api.g_false ,
194 p_validation_level => null ,
195 p_application_id_tbl => ltn_application_id ,
196 p_entity_code_tbl => ltv_entity_code ,
197 p_event_class_code_tbl => ltv_event_class_code ,
198 p_trx_id_tbl => ltn_trx_id ,
199 p_trx_line_id_tbl => ltn_trx_line_id ,
200 p_internal_organization_id_tbl => ltn_internal_organization_id ,
201 p_tax_line_id_tbl => ltn_tax_line_id ,
202 p_legal_reporting_status_val => jg_zz_vat_rep_final_reporting.gv_legal_reporting_status ,
203 x_return_status => lv_return_status ,
204 x_msg_count => ln_msg_count ,
205 x_msg_data => lv_msg_data
206 );
207
208 if lv_return_status <> fnd_api.g_ret_sts_success then
209 xv_retcode := 2;
210 goto exit_from_procedure;
211 end if;
212
213 /* Revised approach change */
214 open c_get_final_reporting_id;
215 fetch c_get_final_reporting_id into ln_final_reporting_id;
216 close c_get_final_reporting_id;
217
218 update jg_zz_vat_trx_details
219 set final_reporting_id = ln_final_reporting_id,
220 last_updated_by = fnd_global.user_id,
221 last_update_date = trunc(sysdate),
222 last_update_login = fnd_global.login_id
223 where reporting_status_id in
224 (
225 select reporting_status_id
226 from jg_zz_vat_rep_status
227 Where vat_reporting_entity_id = pn_vat_reporting_entity_id
228 and tax_calendar_period = pv_tax_calendar_period
229 and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) )
230 );
231
232 jg_zz_vat_rep_utility.post_process_update
233 (
234 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
235 pv_tax_calendar_period => pv_tax_calendar_period ,
236 pv_source => pv_source ,
237 pv_process_name => 'FINAL REPORTING' ,
238 pn_process_id => ln_final_reporting_id ,
239 pv_process_flag => fnd_api.g_ret_sts_success ,
240 pv_enable_allocations_flag => lv_enable_allocations_flag ,
241 xv_return_status => lv_return_status ,
242 xv_return_message => lv_return_message
243 );
244
245 if lv_return_status <> fnd_api.g_ret_sts_success then
246 xv_errbuf := lv_return_message;
247 xv_retcode := 2;
248 goto exit_from_procedure;
249 end if;
250
251 << exit_from_procedure >>
252 return;
253 exception
254 when others then
255 xv_errbuf := 'Unexpected Error - ' || SQLERRM ;
256 xv_retcode := 2;
257
258 end execute_final_reporting;
259
260 end jg_zz_vat_rep_final_reporting;