1 PACKAGE BODY jai_cmn_rgm_setup_pkg
2 /* $Header: jai_cmn_rgm_setp.plb 120.4 2007/05/17 05:53:03 csahoo ship $ */
3 as
4 /* --------------------------------------------------------------------------------------
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
29 */
30 PROCEDURE Gen_Invoice_Number(
31 p_regime_id JAI_RGM_DEFINITIONS.regime_id%Type,
32 p_organization_id hr_all_organization_units.organization_id%Type,
33 p_location_id hr_locations.location_id%Type,
34 p_DATE DATE,
35 p_doc_class jai_rgm_doc_seq_dtls.document_class%Type ,
36 p_doc_type_id jai_rgm_doc_seq_dtls.document_class_type_id%Type,
37 p_invoice_number OUT NOCOPY VARCHAR2, /* caller should call with parameter of size VARCHAR2(100)*/
38 p_process_flag OUT NOCOPY VARCHAR2, /* caller should call with parameter of size VARCHAR2(2)*/
39 p_process_msg OUT NOCOPY VARCHAR2) /* caller should call with parameter of size VARCHAR2(1100) atleast*/
40 IS
41 lv_prefix jai_rgm_doc_seq_dtls.prefix%Type ;
42 ln_current jai_rgm_doc_seq_dtls.current_number%Type ;
43 ln_end jai_rgm_doc_seq_dtls.end_number%Type ;
44 ln_seq_id jai_rgm_doc_seq_dtls.rgm_document_seq_id%Type ;
45 ln_seq_dtl_id jai_rgm_doc_seq_dtls.rgm_document_seq_dtl_id%Type ;
46 ln_regnum JAI_RGM_REGISTRATIONS.attribute_value%Type ;
47
48 CURSOR c_rgm_hdr(p_regime_id JAI_RGM_DEFINITIONS.regime_id%Type,
49 p_organization_id hr_all_organization_units.organization_id%Type,
50 p_location_id hr_locations.location_id%Type ,
51 p_date DATE
52 ) IS
53 SELECT RGM_DOCUMENT_SEQ_ID
54 FROM jai_rgm_doc_seq_hdrs rhdr
55 WHERE rhdr.regime_id = p_regime_id
56 AND rhdr.party_id = p_organization_id
57 AND rhdr.party_site_id = p_location_id
58 AND TRUNC(p_date) between TRUNC(rhdr.effective_from) and NVL(TRUNC(rhdr.effective_to) ,sysdate) ;
59
60 CURSOR c_rgm_hdr_regnum(p_regime_id JAI_RGM_DEFINITIONS.regime_id%Type,
61 ln_regnum JAI_RGM_REGISTRATIONS.attribute_value%Type,
62 p_date DATE
63 ) IS
64 SELECT rgm_document_seq_id
65 FROM jai_rgm_doc_seq_hdrs rhdr
66 WHERE rhdr.regime_id = p_regime_id
67 AND rhdr.registration_num = ln_regnum
68 AND TRUNC(p_date) between TRUNC(rhdr.effective_from) and NVL(TRUNC(rhdr.effective_to) ,sysdate) ;
69
70 CURSOR c_rgm_regnum(p_regime_id JAI_RGM_DEFINITIONS.regime_id%Type,
71 p_organization_id hr_all_organization_units.organization_id%Type,
72 p_location_id hr_locations.location_id%Type,
73 p_att_type_code jai_rgm_registrations.attribute_type_Code%TYPE , --rchandan for bug#4428980
74 p_att_code jai_rgm_registrations.attribute_Code%TYPE --rchandan for bug#4428980
75 ) IS
76 select attribute_value
77 from JAI_RGM_ORG_REGNS_V
78 where regime_id = p_regime_id
79 and organization_id = p_organization_id
80 and location_id = p_location_id
81 and attribute_type_Code = p_att_type_code
82 and attribute_code = p_att_code ;
83
84
85 CURSOR c_rgm_dtl(ln_seq_id jai_rgm_doc_seq_hdrs.rgm_document_seq_id%Type,
86 p_doc_class jai_rgm_doc_seq_dtls.document_class%Type,
87 p_doc_type_id jai_rgm_doc_seq_dtls.document_class_Type_id%Type
88 ) IS
89 SELECT rgm_document_seq_dtl_id
90 FROM jai_rgm_doc_seq_dtls rdtl
91 WHERE rdtl.rgm_document_seq_id = ln_seq_id
92 AND rdtl.document_class = p_doc_class
93 AND rdtl.document_class_type_id = decode(p_doc_class, 'R', -8888, 'UR', -8888, --csahoo bug 5233925
94 p_doc_type_id) ;
95
96 CURSOR c_default(ln_seq_id jai_rgm_doc_seq_dtls.rgm_document_seq_dtl_id%Type)
97 IS
98 SELECT rgm_document_seq_dtl_id
99 FROM jai_rgm_doc_seq_dtls
100 WHERE rgm_document_seq_id = ln_seq_id
101 AND document_class = DECODE(p_doc_class,'O','D','UO','UD','UI','UD','I','D','R','D','UR','UD',p_doc_class);
102 /*added the p_doc_class in the end of the aboive decode statement for bug #5989740 */
103
104 CURSOR c_get_prefix_current(ln_seq_dtl_id jai_rgm_doc_seq_dtls.rgm_document_seq_dtl_id%Type)
105 IS
106 SELECT prefix,current_number,end_number
107 FROM jai_rgm_doc_seq_dtls
108 WHERE rgm_document_seq_dtl_id = ln_seq_dtl_id
109 FOR UPDATE of current_number ; /* Added 'FOR UPDATE OF current_number' by ssawant for bug#5603661 */
110
111 BEGIN
112 OPEN c_rgm_hdr(p_regime_id, p_organization_id, p_location_id, p_date) ;
113 FETCH c_rgm_hdr into ln_seq_id ;
114
115 IF c_rgm_hdr%FOUND THEN --header exists for regime,location,organization
116 close c_rgm_hdr ;-- added by ssawant for bug#5603661
117 OPEN c_rgm_dtl(ln_seq_id,p_doc_class,p_doc_Type_id );
118 FETCH c_rgm_dtl into ln_seq_dtl_id ;
119
120 IF c_rgm_dtl%FOUND THEN
121 CLOSE c_rgm_dtl;
122 OPEN c_get_prefix_current( ln_seq_dtl_id ) ;
123 FETCH c_get_prefix_current INTO lv_prefix,ln_current, ln_end ;
124 CLOSE c_get_prefix_current ;
125
126 IF (ln_current +1 ) > NVL(ln_end,ln_current+1)
127 THEN
128 /*
129 || Coding the ln_current > nvl(ln_end, ln_current+1) to make this check explicitly false in case the ln_end is NULL
130 || so it becomes infinitely applicable
131 */
132 p_process_flag := jai_constants.expected_error;
133 p_process_msg := 'Document Numbers are exhausted. Please set the End number to a larger value';
134 p_invoice_number := 0;
135 ELSIF ((ln_current +1 ) <= NVL(ln_end,ln_current+1) )
136 THEN
137 ln_current := ln_current + 1;
138 IF lv_prefix is null
139 THEN
140 p_invoice_number := ln_current ;
141 ELSE
142 p_invoice_number := lv_prefix || '/' || ln_current ;
143 END IF;
144
145 UPDATE jai_rgm_doc_seq_dtls
146 SET current_number = ln_current
147 WHERE rgm_document_seq_dtl_id = ln_seq_dtl_id ;
148
149 p_process_flag := jai_constants.successful ;
150 p_process_msg := 'VAT Invoice Number generated';
151
152 END IF;
153 ELSE /*
154 || This is the else to get the line level default in case the given order / invoice type is not setup
155 */
156 close c_rgm_dtl;-- added by ssawant for bug#5603661
157 OPEN c_default( ln_seq_id );
158 FETCH c_default INTO ln_seq_dtl_id ;
159 if c_default%FOUND
160 THEN
161 CLOSE c_default ;
162 OPEN c_get_prefix_current( ln_seq_dtl_id ) ;
163 FETCH c_get_prefix_current INTO lv_prefix,ln_current, ln_end ;
164 CLOSE c_get_prefix_current ;
165
166 IF ( (ln_current +1 ) > NVL(ln_end,ln_current + 1) )
167 THEN
168 p_process_flag := jai_constants.expected_error;
169 p_process_msg := 'Document Numbers are exhausted. Please set the End number to a larger value';
170 p_invoice_number := 0;
171 ELSIF ( (ln_current +1 ) <= NVL(ln_end,ln_current + 1) )
172 THEN
173 ln_current := ln_current + 1 ;
174 IF lv_prefix is null
175 THEN
176 p_invoice_number := ln_current ;
177 ELSE
178 p_invoice_number := lv_prefix || '/' || ln_current ;
179 END IF;
180
181 UPDATE jai_rgm_doc_seq_dtls
182 SET current_number = ln_current
183 WHERE rgm_document_seq_dtl_id = ln_seq_dtl_id ;
184
185 p_process_flag := jai_constants.successful ;
186 p_process_msg := 'VAT Invoice Number generated - default 1';
187
188 END IF;
189 ELSE
190 close c_default ; -- added by ssawant for bug#5603661
191 p_process_flag := jai_constants.expected_error;
192 p_process_msg := 'No Default document sequence setup exists for the regime / organization / location';
193 p_invoice_number := 0;
194 END IF;
195 END IF; /* END IF for detail found */
196
197 ELSIF c_rgm_hdr%NOTFOUND THEN
198 /*
199 || Header doesnt exists for such regime,location,organization combination
200 || Check the registration number level settings.
201 */
202 CLOSE c_rgm_hdr ;
203 OPEN c_rgm_regnum(p_regime_id, p_organization_id, p_location_id,'PRIMARY','REGISTRATION_NO' ) ;--rchandan for bug#4428980
204 FETCH c_rgm_regnum into ln_regnum ;
205
206 IF c_rgm_regnum%FOUND THEN --regime registration num exists
207 CLOSE c_rgm_regnum ;
208 OPEN c_rgm_hdr_regnum(p_regime_id, ln_regnum, p_date );
209 FETCH c_rgm_hdr_regnum into ln_seq_id ;
210
211 IF c_rgm_hdr_regnum%FOUND THEN
212 CLOSE c_rgm_hdr_regnum;
213
214 OPEN c_rgm_dtl(ln_seq_id,p_doc_class,p_doc_type_id );
215 FETCH c_rgm_dtl into ln_seq_dtl_id ;
216
217 IF c_rgm_dtl%FOUND THEN
218 CLOSE c_rgm_dtl;
219 OPEN c_get_prefix_current( ln_seq_dtl_id ) ;
220 FETCH c_get_prefix_current INTO lv_prefix,ln_current, ln_end ;
221 CLOSE c_get_prefix_current ;
222
223 IF (ln_current +1 ) > NVL(ln_end,ln_current+1)
224 THEN
225 p_process_flag := jai_constants.expected_error;
226 p_process_msg := 'Document Numbers are exhausted. Please set the End number to a larger value';
227 p_invoice_number := 0;
228 ELSIF ((ln_current +1 ) <= NVL(ln_end,ln_current+1) )
229 THEN
230 ln_current := ln_current + 1;
231 IF lv_prefix is null
232 THEN
233 p_invoice_number := ln_current ;
234 ELSE
235 p_invoice_number := lv_prefix || '/' || ln_current ;
236 END IF;
237
238 UPDATE jai_rgm_doc_seq_dtls
239 SET current_number = ln_current
240 WHERE rgm_document_seq_dtl_id = ln_seq_dtl_id ;
241
242 p_process_flag := jai_constants.successful ;
243 p_process_msg := 'VAT Invoice Number generated';
244
245 END IF;
246 ELSE -- if given doc class doesnt exist for the registration number
247 close c_rgm_dtl; -- added by ssawant for bug#5603661
248 OPEN c_default( ln_seq_id );
249 FETCH c_default INTO ln_seq_dtl_id ;
250 if c_default%FOUND
251 THEN
252 CLOSE c_default ;
253 OPEN c_get_prefix_current( ln_seq_dtl_id ) ;
254 FETCH c_get_prefix_current INTO lv_prefix,ln_current, ln_end ;
255 CLOSE c_get_prefix_current ;
256
257 IF ( (ln_current +1 ) > NVL(ln_end,ln_current + 1) )
258 THEN
259 p_process_flag := jai_constants.expected_error;
260 p_process_msg := 'Document Numbers are exhausted. Please set the End number to a larger value';
261 p_invoice_number := 0;
262 ELSIF ( (ln_current +1 ) <= NVL(ln_end,ln_current + 1) )
263 THEN
264 ln_current := ln_current + 1 ;
265 IF lv_prefix is null
266 THEN
267 p_invoice_number := ln_current ;
268 ELSE
269 p_invoice_number := lv_prefix || '/' || ln_current ;
270 END IF;
271
272 UPDATE jai_rgm_doc_seq_dtls
273 SET current_number = ln_current
274 WHERE rgm_document_seq_dtl_id = ln_seq_dtl_id ;
275
276 p_process_flag := jai_constants.successful ;
277 p_process_msg := 'VAT Invoice Number generated - default 2';
278
279 END IF;
280 ELSE /* No Default exists for the registration number level also */
281 close c_default; -- added by ssawant for bug#5603661
282 p_process_flag := jai_constants.expected_error;
283 p_process_msg := 'No Default document sequence setup exists for the regime / registration number level';
284 p_invoice_number := 0;
285 END IF;
286 END IF; /* END IF for No detail exists for the registration number */
287
288 ELSE
289 /*
290 || No Setup found for the registration number , hence need to signal an error
291 */
292 close c_rgm_hdr_regnum ; -- added by ssawant for bug#5603661
293 p_process_flag := jai_constants.expected_error;
294 p_process_msg:= 'Document sequencing setup not done at Registration number level ' ;
295 --registration_num exist for such regime,organization, location combination
296 --Document sequencing not done though setup form
297 END IF;
298 ELSE
299 close c_rgm_regnum ; -- added by ssawant for bug#5603661
300 p_process_flag := jai_constants.expected_error;
301 p_process_msg:= 'Unable to get the registration number for regime + organization + location ' ;
302 --registration_num doesnt exist for such regime,organization, location combination
303 END IF ;
304 /*
305 Commented this code for testing purposes.
306 ELSE --header doesnt exists for such regime,location,organization combination (or) such registration_num
307
308 p_process_flag := jai_constants.expected_error;
309 p_process_msg := 'Document Sequencing Setup not done for this Regime, Location and Organization';
310 p_invoice_number := 0;
311 */
312 END IF;
313 EXCEPTION
314 WHEN OTHERS THEN
315 p_process_flag := jai_constants.unexpected_error ;
316 p_process_msg := 'Exception occurred: ' || SQLCODE || 'Exception Message: ' || substr(SQLERRM,1,1000) ;
317
318 END Gen_Invoice_Number;
319
320 end jai_cmn_rgm_setup_pkg;
[Home] [Help]