[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.5 2010/10/27 07:45:50 amandali ship $ */
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 4. 14-JUL-2009 vkaranam for bug#8679068 120.3.12000000.2
39 Issue:
40 TCS Certificates are generated without the TCS invoice is been paid and settled.
41 Fix:
42 TCS certificates will be generated only if the TCS invoice is been settled.
43 Changes:
44 Added jai_rgm_settlements.status='SETTLED' condition in the cursor
45 c_group_for_certificate.
46
47 5. 27-Oct-2010 Bug 10201267
48 Description: A single certificate is generated for Traansactions with
49 difeerent Item Classification.
50 Fix: Added Item Classification in group by clause of cursor c_group_for_certificate
51 so that a separate certificate is generated if Item Classification changes.
52
53 Future Dependencies For the release Of this Object:-
54 (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/
55 A datamodel change )
56
57 ----------------------------------------------------------------------------------------------------------------------------------------------------
58 Current Version Current Bug Dependent Files Version Author Date Remarks
59 Of File On Bug/Patchset Dependent On
60 ----------------------------------------------------------------------------------------------------------------------------------------------------
61
62
63 ---------------------------------------------------------------------------- */
64
65
66 ---------------------------------------------------------------------------- */
67
68 /* ****************************** generate_tcs_certificates ***************************************** */
69 PROCEDURE generate_tcs_certificates
70 (
71 errbuf out nocopy varchar2,
72 retcode out nocopy varchar2,
73 pd_rgm_from_date in varchar2,
74 pd_rgm_to_date in varchar2,
75 pv_org_tan_num in varchar2,
76 pn_tax_authority_id in number ,
77 pn_tax_authority_site_id in number default null,
78 pn_customer_id in number default null,
79 pn_customer_site_id in number default null
80 )
81 is
82 /*Bug 10201267 - Separate Certificate to be generated if Item Classification Code is different
83 Added Item Classification to group by clause*/
84 cursor c_group_for_certificate
85 (
86 pd_rgm_from_date date,
87 pd_rgm_to_date date,
88 pv_org_tan_num varchar2,
89 pn_tax_authority_id number,
90 pn_tax_authority_site_id number,
91 pn_customer_id number,
92 pn_customer_site_id number
93 )
94 is
95 SELECT jrr.fin_year,
96 jrr.org_tan_no,
97 jrr.organization_id,
98 jrr.party_id,
99 jrr.party_site_id,
100 jrs.tax_authority_id,
101 jrs.tax_authority_site_id,
102 jrr.item_classification
103 FROM JAI_RGM_REFS_ALL jrr,
104 JAI_RGM_SETTLEMENTS jrs
105 WHERE jrr.settlement_id = jrs.settlement_id
106 AND jrr.source_document_date BETWEEN pd_rgm_from_date and pd_rgm_to_date
107 AND jrr.certificate_id IS NULL
108 AND jrs.status='SETTLED' --added for bug#8679068
109 AND jrs.tax_authority_id = pn_tax_authority_id
110 AND jrs.tax_authority_site_id = nvl(pn_tax_authority_site_id, tax_authority_site_id)
111 AND jrr.party_type = 'C'
112 AND jrr.party_id = nvl(pn_customer_id, party_id)
113 AND jrr.party_site_id = nvl(pn_customer_site_id, party_site_id)
114 AND jrr.org_tan_no = pv_org_tan_num
115 GROUP BY jrr.fin_year,
116 jrr.org_tan_no,
117 jrr.organization_id,
118 jrr.party_id,
119 jrr.party_site_id,
120 jrs.tax_authority_id,
121 jrs.tax_authority_site_id,
122 jrr.item_classification;
123
124 CURSOR c_jai_ap_tds_cert_nums(pv_org_tan_num VARCHAR2,
125 pn_fin_year NUMBER ,
126 pv_regime_code VARCHAR2)
127 IS
128 SELECT nvl(certificate_num, 0) + 1
129 FROM jai_ap_tds_cert_nums
130 WHERE org_tan_num = pv_org_tan_num
131 AND fin_yr = pn_fin_year
132 AND REGIME_CODE = pv_regime_code;
133
134 CURSOR c_get_certificate_id is
135 SELECT jai_rgm_certificates_s.nextval
136 FROM dual;
137
138 CURSOR cur_regime_id(cp_regime_code VARCHAR2) IS
139 SELECT regime_id
140 FROM JAI_RGM_DEFINITIONS
141 WHERE regime_code = cp_regime_code;
142
143 ln_certificate_num jai_ap_tds_cert_nums.certificate_num%type;
144 ln_certificate_id number;
145 ln_regime_id NUMBER;
146
147 ln_program_id number;
148 ln_program_login_id number;
149 ln_program_application_id number;
150 ln_request_id number;
151 ln_user_id number(15);
152 ln_last_update_login number(15);
153 ln_certificate_count number;
154 ld_from_date date;
155 ld_to_date date;
156
157 begin
158 /* */
159
160 /* Get the statis fnd values for populating into the table */
161 Fnd_File.put_line(Fnd_File.LOG, '** Start of procedure jai_ap_tcs_processing_pkg.generate_tcs_certificates **');
162
163 ln_user_id := fnd_global.user_id ;
164 ln_last_update_login := fnd_global.login_id ;
165 ln_program_id := fnd_global.conc_program_id ;
166 ln_program_login_id := fnd_global.conc_login_id ;
167 ln_program_application_id := fnd_global.prog_appl_id ;
168 ln_request_id := fnd_global.conc_request_id ;
169 ld_from_date := fnd_date.canonical_to_date(pd_rgm_from_date);
170 ld_to_date := fnd_date.canonical_to_date(pd_rgm_to_date) ;
171 ln_certificate_count := 0 ;
172
173 OPEN cur_regime_id(jai_constants.tcs_regime);
174 FETCH cur_regime_id INTO ln_regime_id;
175 CLOSE cur_regime_id;
176
177 /* Group for TCS Certificates */
178
179 Fnd_File.put_line(Fnd_File.LOG, ' Generating Certificates ' );
180
181 for cur_rec in
182 c_group_for_certificate
183 (
184 ld_from_date ,
185 ld_to_date ,
186 pv_org_tan_num ,
187 pn_tax_authority_id ,
188 pn_tax_authority_site_id ,
189 pn_customer_id ,
190 pn_customer_site_id
191 )
192 loop
193
194 /* Get certificate number */
195 ln_certificate_num := null;
196 OPEN c_jai_ap_tds_cert_nums(pv_org_tan_num, cur_rec.fin_year,jai_constants.tcs_regime);
197 FETCH c_jai_ap_tds_cert_nums into ln_certificate_num;
198 CLOSE c_jai_ap_tds_cert_nums;
199
200 IF ln_certificate_num IS NULL THEN
201 ln_certificate_num := 1;
202 END IF;
203
204 OPEN c_get_certificate_id;
205 FETCH c_get_certificate_id INTO ln_certificate_id;
206 CLOSE c_get_certificate_id;
207
208 UPDATE jai_rgm_refs_all
209 SET certificate_id = ln_certificate_id
210 , last_update_date = sysdate
211 , last_update_login = ln_last_update_login
212 where trx_ref_id in ( SELECT jrr.trx_ref_id
213 FROM jai_rgm_refs_all jrr,
214 jai_rgm_settlements jrs
215 WHERE jrr.settlement_id = jrs.settlement_id
216 AND jrr.source_document_date BETWEEN ld_from_date and ld_to_date
217 AND jrr.certificate_id IS NULL
218 AND jrs.tax_authority_id = cur_rec.tax_authority_id
219 AND jrs.tax_authority_site_id = cur_rec.tax_authority_site_id
220 AND jrr.party_type = jai_constants.party_type_customer
221 AND jrr.party_id = cur_rec.party_id
222 AND jrr.party_site_id = cur_rec.party_site_id
223 AND jrr.org_tan_no = cur_rec.org_tan_no
224 AND jrr.fin_year = cur_rec.fin_year
225 AND jrr.organization_id = cur_rec.organization_id
226 AND jrr.item_classification = cur_rec.item_classification);
227 /*Bug 10201267 - Separate Certificate to be generated if Item Classification Code is different*/
228
229 if sql%rowcount = 0 then
230 goto continue_with_next_certificate;
231 end if;
232
233 Fnd_File.put_line(Fnd_File.LOG, 'Certificate Number : ' || ln_certificate_num);
234 Fnd_File.put_line(Fnd_File.LOG, ' No of Records for the Certificate : ' || to_char(sql%rowcount) );
235 ln_certificate_count := ln_certificate_count + 1;
236
237 if ln_certificate_num = 1 then
238 Fnd_File.put_line(Fnd_File.LOG, 'Created a certificate record in jai_ap_tds_cert_nums');
239 insert into jai_ap_tds_cert_nums
240 (
241 FIN_YR_CERT_ID , -- Date 22/06/2007 by sacsethi for bug 6144923
242 regime_code ,
243 org_tan_num ,
244 fin_yr ,
245 certificate_num ,
246 created_by ,
247 creation_date ,
248 last_updated_by ,
249 last_update_date ,
250 last_update_login
251 )
252 values
253 (
254 JAI_AP_TDS_CERT_NUMS_S.NEXTVAL , -- Date 22/06/2007 by sacsethi for bug 6144923
255 jai_constants.tcs_regime,
256 pv_org_tan_num ,
257 cur_rec.fin_year ,
258 1 ,
259 ln_user_id ,
260 sysdate ,
261 ln_user_id ,
262 sysdate ,
263 ln_last_update_login
264 );
265
266 else
267
268
269 Fnd_File.put_line(Fnd_File.LOG, 'Updated certificate number in jai_ap_tds_cert_nums');
270 update jai_ap_tds_cert_nums
271 set certificate_num = ln_certificate_num
272 where org_tan_num = pv_org_tan_num
273 and fin_yr = cur_rec.fin_year
274 and regime_code = jai_constants.tcs_regime;
278 Fnd_File.put_line(Fnd_File.LOG, 'Inserting record in JAI_RGM_CERTIFICATES with certificate_id : ' || to_char(ln_certificate_id));
275 end if;
276
277 /* insert into JAI_RGM_CERTIFICATES */
279
280 INSERT INTO
281 jai_rgm_certificates( CERTIFICATE_ID ,
282 CERTIFICATE_NUM ,
283 CERTIFICATE_DATE ,
284 PARTY_TYPE ,
285 PARTY_ID ,
286 PARTY_SITE_ID ,
287 REGIME_ID ,
288 TAX_AUTHORITY_ID ,
289 TAX_AUTHORITY_SITE_ID ,
290 FROM_DATE ,
291 TO_DATE ,
292 PRINT_FLAG ,
293 organization_id ,
294 ISSUE_DATE ,
295 FIN_YEAR ,
296 ORG_TAN_NO ,
297 PROGRAM_ID ,
298 PROGRAM_LOGIN_ID ,
299 PROGRAM_APPLICATION_ID,
300 REQUEST_ID ,
301 OBJECT_VERSION_NUMBER ,
302 CREATION_DATE ,
303 CREATED_BY ,
304 LAST_UPDATE_DATE ,
305 LAST_UPDATED_BY ,
306 LAST_UPDATE_LOGIN )
307 VALUES( ln_certificate_id ,
308 ln_certificate_num ,
309 trunc(sysdate) ,
310 jai_constants.party_type_customer ,
311 cur_rec.party_id ,
312 cur_rec.party_site_id ,
313 ln_regime_id,
314 cur_rec.tax_authority_id ,
315 cur_rec.tax_authority_site_id,
316 ld_from_date ,
317 ld_to_date ,
318 NULL,
319 cur_rec.organization_id ,
320 NULL ,
321 cur_rec.fin_year ,
322 cur_rec.org_tan_no ,
323 ln_program_id ,
324 ln_program_login_id ,
325 ln_program_application_id ,
326 ln_request_id ,
327 NULL ,
328 SYSDATE ,
329 ln_user_id ,
330 SYSDATE ,
331 ln_user_id ,
332 ln_last_update_login );
333
334
335 << continue_with_next_certificate >>
336 null;
337
338 end loop; /* c_group_for_certificate */
339
340
341 <<exit_from_procedure>>
342 Fnd_File.put_line(Fnd_File.LOG, 'No of Certificates Generated : ' || to_char(ln_certificate_count));
343 Fnd_File.put_line(Fnd_File.LOG, '** Successful End of procedure jai_ap_tcs_processing_pkg.generate_tcs_certificates **');
344
345 return;
346
347 exception
348 when others then
349 retcode := 2;
350 errbuf := 'Error from jai_ap_tcs_processing_pkg.generate_tcs_certificates : ' || sqlerrm;
351 Fnd_File.put_line(Fnd_File.LOG, 'Error End of procedure jai_ap_tcs_processing_pkg.process_tds_payments : ' || sqlerrm);
352
353 end generate_tcs_certificates;
354
355 /* ****************************** generate_tcs_certificates ***************************************** */
356
357 END jai_ap_tcs_processing_pkg;