[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_TCS_PROCESSING_PKG
Source
1 PACKAGE BODY jai_ap_tcs_processing_pkg AS
2 /* $Header: jai_ap_tcs_prc.plb 120.3.12000000.1 2007/07/24 06:55:12 rallamse noship $ */
3 /* ----------------------------------------------------------------------------
4 FILENAME : jai_ap_tcs_prc.plb
5
6 Created By : Balaji
7
8 Created Date : 30-jan-2007
9
10 Bug : 5631784
11
12 Purpose : Solution for TCS
13
14 Called from : Concurrent,
15 JAINTCSC - India - Generate TCS Certificates
16
17 CHANGE HISTORY:
18 -------------------------------------------------------------------------------
19 S.No Date Author and Details
20 -------------------------------------------------------------------------------
21 1 30-JAN-2007 bgowrava for forward porting bug#5631784 (4742259).
22 Created this package for generating TCS Certificates. This is called from
23 JAINTCSC concurrent. A new column regime_code is added to table jai_ap_tds_cert_nums
24 to store the regime_code. This will allow us to use the table for both TDS and
25 TCS. A migration script is prepared to populate the regime_code as TDS for all
26 existing records. Changes in package jai_ap_tds_processing_pkg are made accordingly.
27
28 2 14-05-2007 ssawant for bug 5879769,
29 Objects was not compiling. so changes are done to make it compiling.
30
31 3. 22-06-2007 sacsethi for bug 6144923 File version - 120.2
32
33 Problem - R12RUP03-ST1:REPORT NOT AVAILABLE-INDIA - GENERATE TCS CERTIFICATES
34
35 Solution - Insert Statement is changed jai_ap_tds_cert_nums , Column FIN_YR_CERT_ID
36 is added According to R12 standard.
37
38 Future Dependencies For the release Of this Object:-
39 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
40 A datamodel change )
41
42 ----------------------------------------------------------------------------------------------------------------------------------------------------
43 Current Version Current Bug Dependent Files Version Author Date Remarks
44 Of File On Bug/Patchset Dependent On
45 ----------------------------------------------------------------------------------------------------------------------------------------------------
46
47
48 ---------------------------------------------------------------------------- */
49
50
51 ---------------------------------------------------------------------------- */
52
53 /* ****************************** generate_tcs_certificates ***************************************** */
54 PROCEDURE generate_tcs_certificates
55 (
56 errbuf out nocopy varchar2,
57 retcode out nocopy varchar2,
58 pd_rgm_from_date in varchar2,
59 pd_rgm_to_date in varchar2,
60 pv_org_tan_num in varchar2,
61 pn_tax_authority_id in number ,
62 pn_tax_authority_site_id in number default null,
63 pn_customer_id in number default null,
64 pn_customer_site_id in number default null
65 )
66 is
67 cursor c_group_for_certificate
68 (
69 pd_rgm_from_date date,
70 pd_rgm_to_date date,
71 pv_org_tan_num varchar2,
72 pn_tax_authority_id number,
73 pn_tax_authority_site_id number,
74 pn_customer_id number,
75 pn_customer_site_id number
76 )
77 is
78 SELECT jrr.fin_year,
79 jrr.org_tan_no,
80 jrr.organization_id,
81 jrr.party_id,
82 jrr.party_site_id,
83 jrs.tax_authority_id,
84 jrs.tax_authority_site_id
85 FROM JAI_RGM_REFS_ALL jrr,
86 JAI_RGM_SETTLEMENTS jrs
87 WHERE jrr.settlement_id = jrs.settlement_id
88 AND jrr.source_document_date BETWEEN pd_rgm_from_date and pd_rgm_to_date
89 AND jrr.certificate_id IS NULL
90 AND jrs.tax_authority_id = pn_tax_authority_id
91 AND jrs.tax_authority_site_id = nvl(pn_tax_authority_site_id, tax_authority_site_id)
92 AND jrr.party_type = 'C'
93 AND jrr.party_id = nvl(pn_customer_id, party_id)
94 AND jrr.party_site_id = nvl(pn_customer_site_id, party_site_id)
95 AND jrr.org_tan_no = pv_org_tan_num
96 GROUP BY jrr.fin_year,
97 jrr.org_tan_no,
98 jrr.organization_id,
99 jrr.party_id,
100 jrr.party_site_id,
101 jrs.tax_authority_id,
102 jrs.tax_authority_site_id;
103
104 CURSOR c_jai_ap_tds_cert_nums(pv_org_tan_num VARCHAR2,
105 pn_fin_year NUMBER ,
106 pv_regime_code VARCHAR2)
107 IS
108 SELECT nvl(certificate_num, 0) + 1
109 FROM jai_ap_tds_cert_nums
110 WHERE org_tan_num = pv_org_tan_num
111 AND fin_yr = pn_fin_year
112 AND REGIME_CODE = pv_regime_code;
113
114 CURSOR c_get_certificate_id is
115 SELECT jai_rgm_certificates_s.nextval
116 FROM dual;
117
118 CURSOR cur_regime_id(cp_regime_code VARCHAR2) IS
119 SELECT regime_id
120 FROM JAI_RGM_DEFINITIONS
121 WHERE regime_code = cp_regime_code;
122
123 ln_certificate_num jai_ap_tds_cert_nums.certificate_num%type;
124 ln_certificate_id number;
125 ln_regime_id NUMBER;
126
127 ln_program_id number;
128 ln_program_login_id number;
129 ln_program_application_id number;
130 ln_request_id number;
131 ln_user_id number(15);
132 ln_last_update_login number(15);
133 ln_certificate_count number;
134 ld_from_date date;
135 ld_to_date date;
136
137 begin
138 /* */
139
140 /* Get the statis fnd values for populating into the table */
141 Fnd_File.put_line(Fnd_File.LOG, '** Start of procedure jai_ap_tcs_processing_pkg.generate_tcs_certificates **');
142
143 ln_user_id := fnd_global.user_id ;
144 ln_last_update_login := fnd_global.login_id ;
145 ln_program_id := fnd_global.conc_program_id ;
146 ln_program_login_id := fnd_global.conc_login_id ;
147 ln_program_application_id := fnd_global.prog_appl_id ;
148 ln_request_id := fnd_global.conc_request_id ;
149 ld_from_date := fnd_date.canonical_to_date(pd_rgm_from_date);
150 ld_to_date := fnd_date.canonical_to_date(pd_rgm_to_date) ;
151 ln_certificate_count := 0 ;
152
153 OPEN cur_regime_id(jai_constants.tcs_regime);
154 FETCH cur_regime_id INTO ln_regime_id;
155 CLOSE cur_regime_id;
156
157 /* Group for TCS Certificates */
158
159 Fnd_File.put_line(Fnd_File.LOG, ' Generating Certificates ' );
160
161 for cur_rec in
162 c_group_for_certificate
163 (
164 ld_from_date ,
165 ld_to_date ,
166 pv_org_tan_num ,
167 pn_tax_authority_id ,
168 pn_tax_authority_site_id ,
169 pn_customer_id ,
170 pn_customer_site_id
171 )
172 loop
173
174 /* Get certificate number */
175 ln_certificate_num := null;
176 OPEN c_jai_ap_tds_cert_nums(pv_org_tan_num, cur_rec.fin_year,jai_constants.tcs_regime);
177 FETCH c_jai_ap_tds_cert_nums into ln_certificate_num;
178 CLOSE c_jai_ap_tds_cert_nums;
179
180 IF ln_certificate_num IS NULL THEN
181 ln_certificate_num := 1;
182 END IF;
183
184 OPEN c_get_certificate_id;
185 FETCH c_get_certificate_id INTO ln_certificate_id;
186 CLOSE c_get_certificate_id;
187
188 UPDATE jai_rgm_refs_all
189 SET certificate_id = ln_certificate_id
190 , last_update_date = sysdate
191 , last_update_login = ln_last_update_login
192 where trx_ref_id in ( SELECT jrr.trx_ref_id
193 FROM jai_rgm_refs_all jrr,
194 jai_rgm_settlements jrs
195 WHERE jrr.settlement_id = jrs.settlement_id
196 AND jrr.source_document_date BETWEEN ld_from_date and ld_to_date
197 AND jrr.certificate_id IS NULL
198 AND jrs.tax_authority_id = cur_rec.tax_authority_id
199 AND jrs.tax_authority_site_id = cur_rec.tax_authority_site_id
200 AND jrr.party_type = jai_constants.party_type_customer
201 AND jrr.party_id = cur_rec.party_id
202 AND jrr.party_site_id = cur_rec.party_site_id
203 AND jrr.org_tan_no = cur_rec.org_tan_no
204 AND jrr.fin_year = cur_rec.fin_year
205 AND jrr.organization_id = cur_rec.organization_id);
206
207 if sql%rowcount = 0 then
208 goto continue_with_next_certificate;
209 end if;
210
211 Fnd_File.put_line(Fnd_File.LOG, 'Certificate Number : ' || ln_certificate_num);
212 Fnd_File.put_line(Fnd_File.LOG, ' No of Records for the Certificate : ' || to_char(sql%rowcount) );
213 ln_certificate_count := ln_certificate_count + 1;
214
215 if ln_certificate_num = 1 then
216 Fnd_File.put_line(Fnd_File.LOG, 'Created a certificate record in jai_ap_tds_cert_nums');
217 insert into jai_ap_tds_cert_nums
218 (
219 FIN_YR_CERT_ID , -- Date 22/06/2007 by sacsethi for bug 6144923
220 regime_code ,
221 org_tan_num ,
222 fin_yr ,
223 certificate_num ,
224 created_by ,
225 creation_date ,
226 last_updated_by ,
227 last_update_date ,
228 last_update_login
229 )
230 values
231 (
232 JAI_AP_TDS_CERT_NUMS_S.NEXTVAL , -- Date 22/06/2007 by sacsethi for bug 6144923
233 jai_constants.tcs_regime,
234 pv_org_tan_num ,
235 cur_rec.fin_year ,
236 1 ,
237 ln_user_id ,
238 sysdate ,
239 ln_user_id ,
240 sysdate ,
241 ln_last_update_login
242 );
243
244 else
245
246
247 Fnd_File.put_line(Fnd_File.LOG, 'Updated certificate number in jai_ap_tds_cert_nums');
248 update jai_ap_tds_cert_nums
249 set certificate_num = ln_certificate_num
250 where org_tan_num = pv_org_tan_num
251 and fin_yr = cur_rec.fin_year
252 and regime_code = jai_constants.tcs_regime;
253 end if;
254
255 /* insert into JAI_RGM_CERTIFICATES */
256 Fnd_File.put_line(Fnd_File.LOG, 'Inserting record in JAI_RGM_CERTIFICATES with certificate_id : ' || to_char(ln_certificate_id));
257
258 INSERT INTO
259 jai_rgm_certificates( CERTIFICATE_ID ,
260 CERTIFICATE_NUM ,
261 CERTIFICATE_DATE ,
262 PARTY_TYPE ,
263 PARTY_ID ,
264 PARTY_SITE_ID ,
265 REGIME_ID ,
266 TAX_AUTHORITY_ID ,
267 TAX_AUTHORITY_SITE_ID ,
268 FROM_DATE ,
269 TO_DATE ,
270 PRINT_FLAG ,
271 organization_id ,
272 ISSUE_DATE ,
273 FIN_YEAR ,
274 ORG_TAN_NO ,
275 PROGRAM_ID ,
276 PROGRAM_LOGIN_ID ,
277 PROGRAM_APPLICATION_ID,
278 REQUEST_ID ,
279 OBJECT_VERSION_NUMBER ,
280 CREATION_DATE ,
281 CREATED_BY ,
282 LAST_UPDATE_DATE ,
283 LAST_UPDATED_BY ,
284 LAST_UPDATE_LOGIN )
285 VALUES( ln_certificate_id ,
286 ln_certificate_num ,
287 trunc(sysdate) ,
288 jai_constants.party_type_customer ,
289 cur_rec.party_id ,
290 cur_rec.party_site_id ,
291 ln_regime_id,
292 cur_rec.tax_authority_id ,
293 cur_rec.tax_authority_site_id,
294 ld_from_date ,
295 ld_to_date ,
296 NULL,
297 cur_rec.organization_id ,
298 NULL ,
299 cur_rec.fin_year ,
300 cur_rec.org_tan_no ,
301 ln_program_id ,
302 ln_program_login_id ,
303 ln_program_application_id ,
304 ln_request_id ,
305 NULL ,
306 SYSDATE ,
307 ln_user_id ,
308 SYSDATE ,
309 ln_user_id ,
310 ln_last_update_login );
311
312
313 << continue_with_next_certificate >>
314 null;
315
316 end loop; /* c_group_for_certificate */
317
318
319 <<exit_from_procedure>>
320 Fnd_File.put_line(Fnd_File.LOG, 'No of Certificates Generated : ' || to_char(ln_certificate_count));
321 Fnd_File.put_line(Fnd_File.LOG, '** Successful End of procedure jai_ap_tcs_processing_pkg.generate_tcs_certificates **');
322
323 return;
324
325 exception
326 when others then
327 retcode := 2;
328 errbuf := 'Error from jai_ap_tcs_processing_pkg.generate_tcs_certificates : ' || sqlerrm;
329 Fnd_File.put_line(Fnd_File.LOG, 'Error End of procedure jai_ap_tcs_processing_pkg.process_tds_payments : ' || sqlerrm);
330
331 end generate_tcs_certificates;
332
333 /* ****************************** generate_tcs_certificates ***************************************** */
334
335 END jai_ap_tcs_processing_pkg;