4 /* --------------------------------------------------------------------------------------
1 PACKAGE BODY jai_cmn_rgm_setup_pkg
2 /* $Header: jai_cmn_rgm_setp.plb 120.6 2011/11/21 07:54:59 erma ship $ */
3 as
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.2 jai_cmn_rgn_setup -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12 as required for CASE COMPLAINCE.
13
14 14-Jun-2005 rchandan for bug#4428980, Version 116.3
15 Modified the object to remove literals from DML statements and CURSORS.
16 16-apr-2007 Vkaranam for bug #5989740,File version 120.2
17 Forward Porting the changes done in 115 bug 5907436(Budget Changes).
18 1.added the p_doc_class in the end of the decode statement in the c_default cursor
19
20 24-Apr-2007 ssawant for bug#5603661. File version 120.3
21 1. Cursors which are not closed have been closed
22 2. Added the following clause to the cursor c_get_prefix_current.
23 -- FOR UPDATE of current_number
24
25 16/5/2007 csahoo for bug#5233925, File Version 120.4
26 Forward Porting to R12
27 made code changes in the cursor c_rgm_dtl to make it work for unregistered RTV
28 09-jul-2009 vkaranam for bug#8667957 file version 120.1.12000000.3
29 Issue:
30 When attempting to complete the future date AR invoice with VAT taxes, the following error
31 occurs:
32 ERROR
33 -----------------------
34 APP-JA-460204: Document sequencing setup not done at Registration number
35 level
36 Even with the Document sequence setup exists
37 Reason:
38 Issue is with the following code in jai_cmn_rgm_setup_pkg.gen_invoice_num
39
40 SELECT RGM_DOCUMENT_SEQ_ID
41 FROM jai_rgm_doc_seq_hdrs rhdr
42 WHERE rhdr.regime_id = p_regime_id
43 AND rhdr.party_id = p_organization_id
44 AND rhdr.party_site_id = p_location_id
45 AND TRUNC(p_date) between TRUNC(rhdr.effective_from) and
46 NVL(TRUNC(rhdr.effective_to) ,sysdate) ;
47 In the VAT document sequencing setup,effective to date is given as null.
48
49 If the effective to date is given as null then the system date is the
50 effective to date.
51
52 Fix:
53 the default value for the effective to date has been changed as the transaction date instead of sysdate.
54
55
56
57 */
58 PROCEDURE Gen_Invoice_Number(
59 p_regime_id JAI_RGM_DEFINITIONS.regime_id%Type,
60 p_organization_id hr_all_organization_units.organization_id%Type,
61 p_location_id hr_locations.location_id%Type,
62 p_DATE DATE,
63 p_doc_class jai_rgm_doc_seq_dtls.document_class%Type ,
64 p_doc_type_id jai_rgm_doc_seq_dtls.document_class_type_id%Type,
65 p_invoice_number OUT NOCOPY VARCHAR2, /* caller should call with parameter of size VARCHAR2(100)*/
66 p_process_flag OUT NOCOPY VARCHAR2, /* caller should call with parameter of size VARCHAR2(2)*/
67 p_process_msg OUT NOCOPY VARCHAR2) /* caller should call with parameter of size VARCHAR2(1100) atleast*/
68 IS
69 lv_prefix jai_rgm_doc_seq_dtls.prefix%Type ;
70 ln_current jai_rgm_doc_seq_dtls.current_number%Type ;
71 ln_end jai_rgm_doc_seq_dtls.end_number%Type ;
72 ln_seq_id jai_rgm_doc_seq_dtls.rgm_document_seq_id%Type ;
73 ln_seq_dtl_id jai_rgm_doc_seq_dtls.rgm_document_seq_dtl_id%Type ;
74 ln_regnum JAI_RGM_REGISTRATIONS.attribute_value%Type ;
75
76 CURSOR c_rgm_hdr(p_regime_id JAI_RGM_DEFINITIONS.regime_id%Type,
77 p_organization_id hr_all_organization_units.organization_id%Type,
78 p_location_id hr_locations.location_id%Type ,
79 p_date DATE
80 ) IS
81 SELECT RGM_DOCUMENT_SEQ_ID
82 FROM jai_rgm_doc_seq_hdrs rhdr
83 WHERE rhdr.regime_id = p_regime_id
84 AND rhdr.party_id = p_organization_id
85 AND rhdr.party_site_id = p_location_id
86 -- AND TRUNC(p_date) between TRUNC(rhdr.effective_from) and NVL(TRUNC(rhdr.effective_to) ,sysdate) ;
87 AND TRUNC(p_date) between TRUNC(rhdr.effective_from) and NVL(TRUNC(rhdr.effective_to) ,TRUNC(p_date)) ; --changed the sysdate default value to TRUNC(p_date) for bug#8667957
88
92 ) IS
89 CURSOR c_rgm_hdr_regnum(p_regime_id JAI_RGM_DEFINITIONS.regime_id%Type,
90 ln_regnum JAI_RGM_REGISTRATIONS.attribute_value%Type,
91 p_date DATE
93 SELECT rgm_document_seq_id
94 FROM jai_rgm_doc_seq_hdrs rhdr
95 WHERE rhdr.regime_id = p_regime_id
96 AND rhdr.registration_num = ln_regnum
97 -- AND TRUNC(p_date) between TRUNC(rhdr.effective_from) and NVL(TRUNC(rhdr.effective_to) ,sysdate) ;
98 AND TRUNC(p_date) between TRUNC(rhdr.effective_from) and NVL(TRUNC(rhdr.effective_to) ,TRUNC(p_date)) ; --changed the sysdate default value to TRUNC(p_date) for bug#8667957
99
100 CURSOR c_rgm_regnum(p_regime_id JAI_RGM_DEFINITIONS.regime_id%Type,
101 p_organization_id hr_all_organization_units.organization_id%Type,
102 p_location_id hr_locations.location_id%Type,
103 p_att_type_code jai_rgm_registrations.attribute_type_Code%TYPE , --rchandan for bug#4428980
104 p_att_code jai_rgm_registrations.attribute_Code%TYPE --rchandan for bug#4428980
105 ) IS
106 select attribute_value
107 from JAI_RGM_ORG_REGNS_V
108 where regime_id = p_regime_id
109 and organization_id = p_organization_id
110 and location_id = p_location_id
111 and attribute_type_Code = p_att_type_code
112 and attribute_code = p_att_code ;
113
114
115 CURSOR c_rgm_dtl(ln_seq_id jai_rgm_doc_seq_hdrs.rgm_document_seq_id%Type,
116 p_doc_class jai_rgm_doc_seq_dtls.document_class%Type,
117 p_doc_type_id jai_rgm_doc_seq_dtls.document_class_Type_id%Type
118 ) IS
119 SELECT rgm_document_seq_dtl_id
120 FROM jai_rgm_doc_seq_dtls rdtl
121 WHERE rdtl.rgm_document_seq_id = ln_seq_id
122 AND rdtl.document_class = p_doc_class
123 AND rdtl.document_class_type_id = decode(p_doc_class, 'R', -8888, 'UR', -8888, --csahoo bug 5233925
127 IS
124 p_doc_type_id) ;
125
126 CURSOR c_default(ln_seq_id jai_rgm_doc_seq_dtls.rgm_document_seq_dtl_id%Type)
128 SELECT rgm_document_seq_dtl_id
129 FROM jai_rgm_doc_seq_dtls
130 WHERE rgm_document_seq_id = ln_seq_id
131 AND document_class = DECODE(p_doc_class,'O','D','UO','UD','UI','UD','I','D','R','D','UR','UD',p_doc_class);
132 /*added the p_doc_class in the end of the aboive decode statement for bug #5989740 */
133
134 CURSOR c_get_prefix_current(ln_seq_dtl_id jai_rgm_doc_seq_dtls.rgm_document_seq_dtl_id%Type)
135 IS
136 SELECT prefix,current_number,end_number
137 FROM jai_rgm_doc_seq_dtls
138 WHERE rgm_document_seq_dtl_id = ln_seq_dtl_id
139 FOR UPDATE of current_number ; /* Added 'FOR UPDATE OF current_number' by ssawant for bug#5603661 */
140
141 CURSOR c_get_regime_code(ln_regime_id jai_rgm_definitions.REGIME_ID%TYPE)
142 IS
143 SELECT regime_code
144 FROM jai_rgm_definitions
145 WHERE regime_id = ln_regime_id;
146 lv_regime_code jai_rgm_definitions.REGIMe_code%TYPE;
147
148 BEGIN
149 OPEN c_rgm_hdr(p_regime_id, p_organization_id, p_location_id, p_date) ;
150 FETCH c_rgm_hdr into ln_seq_id ;
151
152 IF c_rgm_hdr%FOUND THEN --header exists for regime,location,organization
153 close c_rgm_hdr ;-- added by ssawant for bug#5603661
154 OPEN c_rgm_dtl(ln_seq_id,p_doc_class,p_doc_Type_id );
155 FETCH c_rgm_dtl into ln_seq_dtl_id ;
156
157 IF c_rgm_dtl%FOUND THEN
158 CLOSE c_rgm_dtl;
159 OPEN c_get_prefix_current( ln_seq_dtl_id ) ;
163 IF (ln_current +1 ) > NVL(ln_end,ln_current+1)
160 FETCH c_get_prefix_current INTO lv_prefix,ln_current, ln_end ;
161 CLOSE c_get_prefix_current ;
162
164 THEN
165 /*
166 || Coding the ln_current > nvl(ln_end, ln_current+1) to make this check explicitly false in case the ln_end is NULL
167 || so it becomes infinitely applicable
168 */
169 p_process_flag := jai_constants.expected_error;
170 p_process_msg := 'Document Numbers are exhausted. Please set the End number to a larger value';
171 p_invoice_number := 0;
172 ELSIF ((ln_current +1 ) <= NVL(ln_end,ln_current+1) )
173 THEN
174 ln_current := ln_current + 1;
175 IF lv_prefix is null
176 THEN
177 p_invoice_number := ln_current ;
178 ELSE
179 p_invoice_number := lv_prefix || '/' || ln_current ;
180 END IF;
181
182 UPDATE jai_rgm_doc_seq_dtls
183 SET current_number = ln_current
184 WHERE rgm_document_seq_dtl_id = ln_seq_dtl_id ;
185
186 p_process_flag := jai_constants.successful ;
187 p_process_msg := 'VAT Invoice Number generated';
188
189 END IF;
190 ELSE /*
191 || This is the else to get the line level default in case the given order / invoice type is not setup
192 */
193 close c_rgm_dtl;-- added by ssawant for bug#5603661
194 OPEN c_default( ln_seq_id );
195 FETCH c_default INTO ln_seq_dtl_id ;
196 if c_default%FOUND
197 THEN
198 CLOSE c_default ;
199 OPEN c_get_prefix_current( ln_seq_dtl_id ) ;
200 FETCH c_get_prefix_current INTO lv_prefix,ln_current, ln_end ;
201 CLOSE c_get_prefix_current ;
202
203 IF ( (ln_current +1 ) > NVL(ln_end,ln_current + 1) )
204 THEN
205 p_process_flag := jai_constants.expected_error;
206 p_process_msg := 'Document Numbers are exhausted. Please set the End number to a larger value';
207 p_invoice_number := 0;
208 ELSIF ( (ln_current +1 ) <= NVL(ln_end,ln_current + 1) )
209 THEN
210 ln_current := ln_current + 1 ;
211 IF lv_prefix is null
212 THEN
213 p_invoice_number := ln_current ;
214 ELSE
215 p_invoice_number := lv_prefix || '/' || ln_current ;
216 END IF;
217
218 UPDATE jai_rgm_doc_seq_dtls
219 SET current_number = ln_current
220 WHERE rgm_document_seq_dtl_id = ln_seq_dtl_id ;
221
222 p_process_flag := jai_constants.successful ;
223 p_process_msg := 'VAT Invoice Number generated - default 1';
224
225 END IF;
226 ELSE
227 close c_default ; -- added by ssawant for bug#5603661
228
229 p_process_flag := jai_constants.expected_error;
230 p_process_msg := 'No Default document sequence setup exists for the regime / organization / location';
231 p_invoice_number := 0;
232 END IF;
233 END IF; /* END IF for detail found */
234
235 ELSIF c_rgm_hdr%NOTFOUND THEN
236 /*
237 || Header doesnt exists for such regime,location,organization combination
238 || Check the registration number level settings.
239 */
240 CLOSE c_rgm_hdr ;
241 OPEN c_get_regime_code(p_regime_id);
242 FETCH c_get_regime_code INTO lv_regime_code;
243
244 IF UPPER(NVL(lv_regime_code,'####')) = jai_constants.service_regime THEN
245 OPEN c_rgm_regnum(p_regime_id, p_organization_id, p_location_id,'PRIMARY','SERVICE_TAX_REGISTRATION_NO' ) ;--rchandan for bug#4428980
246 FETCH c_rgm_regnum into ln_regnum ;
247 --Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952
248
249 ELSIF UPPER(NVL(lv_regime_code,'####')) = jai_constants.vat_regime THEN
250 OPEN c_rgm_regnum(p_regime_id, p_organization_id, p_location_id,'PRIMARY','REGISTRATION_NO' ) ;--rchandan for bug#4428980
251 FETCH c_rgm_regnum into ln_regnum ;
252 END IF;
253 CLOSE c_get_regime_code;
254
255 IF c_rgm_regnum%FOUND THEN --regime registration num exists
256 CLOSE c_rgm_regnum ;
257 OPEN c_rgm_hdr_regnum(p_regime_id, ln_regnum, p_date );
258 FETCH c_rgm_hdr_regnum into ln_seq_id ;
259 IF c_rgm_hdr_regnum%FOUND THEN
260 CLOSE c_rgm_hdr_regnum;
261
262 OPEN c_rgm_dtl(ln_seq_id,p_doc_class,p_doc_type_id );
263 FETCH c_rgm_dtl into ln_seq_dtl_id ;
264
265 IF c_rgm_dtl%FOUND THEN
266 CLOSE c_rgm_dtl;
267 OPEN c_get_prefix_current( ln_seq_dtl_id ) ;
268 FETCH c_get_prefix_current INTO lv_prefix,ln_current, ln_end ;
269 CLOSE c_get_prefix_current ;
270
271 IF (ln_current +1 ) > NVL(ln_end,ln_current+1)
272 THEN
273 p_process_flag := jai_constants.expected_error;
274 p_process_msg := 'Document Numbers are exhausted. Please set the End number to a larger value';
275 p_invoice_number := 0;
276 ELSIF ((ln_current +1 ) <= NVL(ln_end,ln_current+1) )
277 THEN
278 ln_current := ln_current + 1;
279 IF lv_prefix is null
280 THEN
281 p_invoice_number := ln_current ;
282 ELSE
283 p_invoice_number := lv_prefix || '/' || ln_current ;
284 END IF;
285
286 UPDATE jai_rgm_doc_seq_dtls
287 SET current_number = ln_current
288 WHERE rgm_document_seq_dtl_id = ln_seq_dtl_id ;
289
290 p_process_flag := jai_constants.successful ;
291 p_process_msg := 'VAT Invoice Number generated';
292
293 END IF;
294 ELSE -- if given doc class doesnt exist for the registration number
295 close c_rgm_dtl; -- added by ssawant for bug#5603661
296 OPEN c_default( ln_seq_id );
297 FETCH c_default INTO ln_seq_dtl_id ;
298 if c_default%FOUND
299 THEN
300 CLOSE c_default ;
301 OPEN c_get_prefix_current( ln_seq_dtl_id ) ;
302 FETCH c_get_prefix_current INTO lv_prefix,ln_current, ln_end ;
303 CLOSE c_get_prefix_current ;
304
305 IF ( (ln_current +1 ) > NVL(ln_end,ln_current + 1) )
306 THEN
307 p_process_flag := jai_constants.expected_error;
308 p_process_msg := 'Document Numbers are exhausted. Please set the End number to a larger value';
309 p_invoice_number := 0;
310 ELSIF ( (ln_current +1 ) <= NVL(ln_end,ln_current + 1) )
311 THEN
312 ln_current := ln_current + 1 ;
313 IF lv_prefix is null
314 THEN
315 p_invoice_number := ln_current ;
316 ELSE
317 p_invoice_number := lv_prefix || '/' || ln_current ;
318 END IF;
319
320 UPDATE jai_rgm_doc_seq_dtls
321 SET current_number = ln_current
322 WHERE rgm_document_seq_dtl_id = ln_seq_dtl_id ;
323
324 p_process_flag := jai_constants.successful ;
325 p_process_msg := 'VAT Invoice Number generated - default 2';
326
327 END IF;
328 ELSE /* No Default exists for the registration number level also */
329 close c_default; -- added by ssawant for bug#5603661
330 p_process_flag := jai_constants.expected_error;
331 p_process_msg := 'No Default document sequence setup exists for the regime / registration number level';
332 p_invoice_number := 0;
333 END IF;
334 END IF; /* END IF for No detail exists for the registration number */
335
336 ELSE
337 /*
338 || No Setup found for the registration number , hence need to signal an error
339 */
340 close c_rgm_hdr_regnum ; -- added by ssawant for bug#5603661
341 p_process_flag := jai_constants.expected_error;
342 p_process_msg:= 'Document sequencing setup not done at Registration number level ' ;
343 --registration_num exist for such regime,organization, location combination
344 --Document sequencing not done though setup form
345 END IF;
346 ELSE
347 close c_rgm_regnum ; -- added by ssawant for bug#5603661
348 p_process_flag := jai_constants.expected_error;
349 p_process_msg:= 'Unable to get the registration number for regime + organization + location ' ;
350 --registration_num doesnt exist for such regime,organization, location combination
351 END IF ;
352 /*
353 Commented this code for testing purposes.
354 ELSE --header doesnt exists for such regime,location,organization combination (or) such registration_num
355
356 p_process_flag := jai_constants.expected_error;
357 p_process_msg := 'Document Sequencing Setup not done for this Regime, Location and Organization';
358 p_invoice_number := 0;
359 */
360 END IF;
361 EXCEPTION
362 WHEN OTHERS THEN
363 p_process_flag := jai_constants.unexpected_error ;
364 p_process_msg := 'Exception occurred: ' || SQLCODE || 'Exception Message: ' || substr(SQLERRM,1,1000) ;
365
366 END Gen_Invoice_Number;
367
368 end jai_cmn_rgm_setup_pkg;
[Home] [Help]