[Home] [Help]
PACKAGE BODY: APPS.RCV_GAPLESS_NUMBERING
Source
1 PACKAGE BODY RCV_GAPLESS_NUMBERING AS
2 /* $Header: RCVSBGNB.pls 120.1.12010000.2 2010/01/25 23:26:52 vthevark ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(32) := 'RCV_GAPLESS_NUMBERING';
5 g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
6
7 PROCEDURE generate_invoice_number (
8 p_api_version IN NUMBER,
9 p_org_id IN NUMBER, -- BugFix 5192878
10 p_vendor_site_id IN NUMBER, -- BugFix 5192878
11 -- p_buying_company_code IN VARCHAR2, -- BugFix 5192878
12 -- p_selling_company_code IN VARCHAR2, -- BugFix 5192878
13 p_invoice_type IN VARCHAR2,
14 x_invoice_num OUT NOCOPY VARCHAR2,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_msg_count OUT NOCOPY NUMBER,
17 x_msg_data OUT NOCOPY VARCHAR2
18 )
19 IS
23 l_prefix VARCHAR2 (45);
20 l_api_version CONSTANT NUMBER := 1.0;
21 l_api_name CONSTANT VARCHAR2 (30) := 'get_invoice_numbering_options';
22 l_progress VARCHAR2 (3);
24 l_invoice_string VARCHAR2 (45);
25 l_next_sequence_number NUMBER;
26 l_rowid VARCHAR2 (250);
27
28 -- Following varibales are declared for BugFix 5197828
29 l_buying_company_identifier VARCHAR2 (10);
30 l_selling_company_identifier VARCHAR2 (10);
31 l_gapless_inv_num_flag_org VARCHAR2 (1);
32 l_gapless_inv_num_flag_sup VARCHAR2 (1);
33 l_return_status VARCHAR2 (1);
34 l_msg_data VARCHAR2 (2000);
35 l_msg_count NUMBER;
36 l_vendor_id NUMBER;
37 l_organization_name HR_ORGANIZATION_UNITS.NAME%TYPE;
38 l_organization_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
39 l_vendor_name PO_VENDORS.VENDOR_NAME%TYPE;
40 l_vendor_site_code PO_VENDOR_SITES_ALL.VENDOR_SITE_CODE%TYPE;
41 l_org_code_name VARCHAR2(300);
42 -- End of code bugfix 5197828
43
44 invoice_exception EXCEPTION;
45
46 cursor get_next_sequence_number is
47 SELECT ROWID,
48 next_sequence_number
49 FROM rcv_gapless_invoice_numbers
50 WHERE (prefix = l_prefix OR (prefix IS NULL AND l_prefix IS NULL))
51 AND buying_company_code = l_buying_company_identifier
52 AND selling_company_code = l_selling_company_identifier
53 AND invoice_type = p_invoice_type
54 FOR UPDATE OF next_sequence_number;
55
56 BEGIN
57
58 IF (g_asn_debug = 'Y')
59 THEN
60 asn_debug.put_line ( 'Following are input parameters to rcv_gapless_numbering.generate_invoice_number package.');
61 asn_debug.put_line ( 'p_org_id = ' || p_org_id );
62 asn_debug.put_line ( 'p_vendor_site_id = ' || p_vendor_site_id );
63 asn_debug.put_line ( 'p_invoice_type = ' || p_invoice_type );
64 END IF;
65
66 l_progress := '000';
67
68 x_return_status := fnd_api.G_RET_STS_ERROR;
69
70 l_progress := '010';
71
72 -- Standard call to check for call compatibility
73 IF NOT fnd_api.compatible_api_call (l_api_version,
74 p_api_version,
75 l_api_name,
76 g_pkg_name
77 ) THEN
78 RAISE fnd_api.g_exc_unexpected_error;
79 END IF;
80
81 -- BugFix 5197828
82 BEGIN
83 SELECT DISTINCT pv.vendor_id ,
84 pv.vendor_name,
85 pvs.vendor_site_code
86 INTO l_vendor_id,
87 l_vendor_name,
88 l_vendor_site_code
89 FROM po_vendor_sites_all pvs,
90 po_vendors pv
91 WHERE vendor_site_id = p_vendor_site_id
92 AND pvs.vendor_id = pv.vendor_id;
93
94 IF (g_asn_debug = 'Y')
95 THEN
96 asn_debug.put_line ( 'Vendor_id = ' || l_vendor_id );
97 END IF;
98
99 EXCEPTION
100 WHEN OTHERS
101 THEN
102 IF (g_asn_debug = 'Y')
103 THEN
104 asn_debug.put_line ( 'Error occured while selecting Vendor_id. Error message is =' || SQLERRM );
105 END IF;
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 END;
108
109 BEGIN
110 SELECT name
111 INTO l_organization_name
112 FROM hr_organization_units
113 WHERE organization_id = p_org_id;
114
115 IF (g_asn_debug = 'Y')
116 THEN
117 asn_debug.put_line ( 'Orgnization name = ' || l_organization_name );
118 END IF;
119 EXCEPTION
120 WHEN OTHERS
121 THEN
122 IF (g_asn_debug = 'Y')
123 THEN
124 asn_debug.put_line ( 'Error occured while selecting Organization Name. Error message is =' || SQLERRM );
125 END IF;
126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
127 END;
128
129
130 BEGIN
131 SELECT organization_code
132 INTO l_organization_code
133 FROM mtl_parameters
134 WHERE organization_id = p_org_id;
135
136 IF (g_asn_debug = 'Y')
137 THEN
138 asn_debug.put_line ( 'Orgnization Code = ' || l_organization_code );
139 END IF;
140
141 l_org_code_name := l_organization_code || ': ' || l_organization_name;
142
143 EXCEPTION
144 WHEN NO_DATA_FOUND
145 THEN
146 l_org_code_name := l_organization_name;
147 WHEN OTHERS
148 THEN
149 IF (g_asn_debug = 'Y')
150 THEN
151 asn_debug.put_line ( 'Error occured while selecting Organization Code. Error message is =' || SQLERRM );
152 END IF;
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 END;
155
156 -- End of code BugFix 5197828
157
158 -- Following code is moved from create_invoice_number procedure.
159 -- x_gapless_inv_num_flag_org is Y and x_gapless_inv_num_flag_sup is Y
160 -- then and then only we should generate the gapless numbers otherwise
161 -- we will raise exception from this procedure.
162
163 l_progress := '020';
164
165 po_ap_integration_grp.get_invoice_numbering_options (1,
166 p_org_id,
167 l_return_status,
168 l_msg_data,
169 l_buying_company_identifier,
170 l_gapless_inv_num_flag_org
171 );
172
173 IF (g_asn_debug = 'Y')
174 THEN
175 asn_debug.put_line ( 'po_ap_integration_grp.get_invoice_numbering_options returned with status ' || l_return_status);
176 asn_debug.put_line ( 'l_buying_company_identifier = ' || l_buying_company_identifier );
177 asn_debug.put_line ( 'l_gapless_inv_num_flag_org = ' || l_gapless_inv_num_flag_org);
178 END IF;
179
180 l_progress := '030';
181
182 AP_PO_GAPLESS_SBI_PKG.site_uses_gapless_num (p_vendor_site_id,
183 l_gapless_inv_num_flag_sup,
184 l_selling_company_identifier
185 );
186 l_progress := '040';
187
188 IF (g_asn_debug = 'Y')
189 THEN
190 asn_debug.put_line ( 'AP_PO_GAPLESS_SBI_PKG.site_uses_gapless_num returned with status ' || l_return_status);
191 asn_debug.put_line ( 'l_gapless_inv_num_flag_sup = ' || l_gapless_inv_num_flag_sup );
192 asn_debug.put_line ( 'l_selling_company_identifier = ' || l_selling_company_identifier);
193 END IF;
194
195 -- End of code BugFix 5197828
196
197 IF ( l_gapless_inv_num_flag_org = 'N' and l_gapless_inv_num_flag_sup = 'N' )
198 THEN
199 x_return_status := fnd_api.G_RET_STS_SUCCESS;
200 return;
201 END IF;
202
203 IF (l_buying_company_identifier is null) THEN
204
205 IF (g_asn_debug = 'Y')
206 THEN
207 asn_debug.put_line ( 'Buying company identofier not defined loggeing error. for organization ' || l_org_code_name );
208 END IF;
209
210 FND_MESSAGE.set_name('PO','RCV_NO_BUYING_COMPANY_ID');
211 FND_MESSAGE.SET_TOKEN('ORGCODENAME', l_org_code_name); -- Bugfix 5197828
212
213 RAISE invoice_exception;
214 END IF;
215
216 IF (l_selling_company_identifier is null) THEN
217 IF (g_asn_debug = 'Y')
218 THEN
219 asn_debug.put_line ( 'Selling company identofier not defined loggeing error. for vendor '
220 || l_vendor_name || ' and vendor site = ' || l_vendor_site_code );
221 END IF;
222
223 FND_MESSAGE.set_name('PO','RCV_NO_SELLING_COMPANY_ID');
224 FND_MESSAGE.SET_TOKEN('VENDORNAME', l_vendor_name); -- Bugfix 5197828
225 FND_MESSAGE.SET_TOKEN('VENDORSITE', l_vendor_site_code); -- Bugfix 5197828
226
227 RAISE invoice_exception;
228 END IF;
229
230 IF (p_invoice_type not in ('ERS','RTS','PPA')) THEN
231 RAISE fnd_api.g_exc_unexpected_error;
232 END IF;
233
234 l_progress := '020';
235
236 IF (p_invoice_type = 'ERS') THEN
237 fnd_profile.get ('ERS_PREFIX', l_prefix);
238 END IF;
239
240 IF (l_prefix = '') THEN
241 l_prefix := NULL;
242 END IF;
243
244 l_progress := '030';
245
246 open get_next_sequence_number;
247 fetch get_next_sequence_number into l_rowid,l_next_sequence_number;
248 close get_next_sequence_number;
249
250 l_progress := '040';
251
252 IF (l_next_sequence_number is null) THEN
253 INSERT INTO rcv_gapless_invoice_numbers (prefix,
254 buying_company_code,
255 selling_company_code,
256 invoice_type,
257 next_sequence_number
258 )
259 VALUES (l_prefix,
260 l_buying_company_identifier, -- Bugfix 5197828
261 l_selling_company_identifier, -- Bugfix 5197828
262 p_invoice_type,
263 1);
264
265 l_progress := '050';
266
267 open get_next_sequence_number;
268 fetch get_next_sequence_number into l_rowid,l_next_sequence_number;
269 close get_next_sequence_number;
270
271 l_progress := '060';
272
273 END IF;
274
275 l_progress := '070';
276
277 IF (l_prefix IS NULL) THEN
278 l_invoice_string := '';
279 ELSE
280 l_invoice_string := l_prefix || '-';
281 END IF;
282
283 l_invoice_string :=
284 l_invoice_string
285 || l_buying_company_identifier -- Bugfix 5197828
286 || '-'
287 || l_selling_company_identifier -- Bugfix 5197828
288 || '-'
289 || p_invoice_type
290 || '-';
291
292 WHILE (ap_po_gapless_sbi_pkg.this_is_dup_inv_num ( l_invoice_string
293 || TO_CHAR (l_next_sequence_number
294 ),
295 l_selling_company_identifier -- Bugfix 5197828
296 )
297 )
298 LOOP
299 l_next_sequence_number := l_next_sequence_number + 1;
300 END LOOP;
301
302 l_progress := '080';
303
304 IF (length(l_invoice_string || l_next_sequence_number)>45) THEN
305 FND_MESSAGE.set_name('PO','RCV_INVOICE_NUM_TOO_LONG');
306 RAISE invoice_exception;
307 END IF;
308
309 x_invoice_num := l_invoice_string || l_next_sequence_number;
310 l_next_sequence_number := l_next_sequence_number + 1;
311
312 UPDATE rcv_gapless_invoice_numbers
313 SET next_sequence_number = l_next_sequence_number
314 WHERE ROWID = l_rowid;
315
316 l_progress := '100';
317
318 x_return_status := fnd_api.G_RET_STS_SUCCESS;
319 FND_MSG_PUB.get(
320 p_msg_index => FND_MSG_PUB.G_LAST,
321 p_encoded => 'F',
322 p_msg_index_out => x_msg_count,
323 p_data => x_msg_data );
324
325 EXCEPTION
326 WHEN invoice_exception THEN
327 x_return_status := fnd_api.g_ret_sts_error;
328 FND_MSG_PUB.add;
329 FND_MSG_PUB.get(
330 p_msg_index => FND_MSG_PUB.G_LAST,
331 p_encoded => 'F',
332 p_msg_index_out => x_msg_count,
333 p_data => x_msg_data );
334 WHEN fnd_api.g_exc_unexpected_error THEN
335 x_return_status := fnd_api.g_ret_sts_unexp_error;
336 FND_MESSAGE.set_name('PO','RCV_SYSTEM_ERROR');
337 FND_MSG_PUB.add;
338 FND_MSG_PUB.get(
339 p_msg_index => FND_MSG_PUB.G_LAST,
340 p_encoded => 'F',
341 p_msg_index_out => x_msg_count,
342 p_data => x_msg_data );
343 WHEN OTHERS THEN
344 x_return_status := fnd_api.g_ret_sts_unexp_error;
345 FND_MESSAGE.set_name('PO','RCV_SYSTEM_ERROR');
346 FND_MSG_PUB.add;
347
348 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
349 fnd_msg_pub.add_exc_msg (p_pkg_name => g_pkg_name,
350 p_procedure_name => l_api_name,
351 p_error_text => SUBSTRB (SQLERRM,
352 1,
353 200
354 )
355 || ' : '
356 || l_progress
357 );
358 END IF;
359
360 FND_MSG_PUB.get(
361 p_msg_index => FND_MSG_PUB.G_LAST,
362 p_encoded => 'F',
363 p_msg_index_out => x_msg_count,
364 p_data => x_msg_data );
365
366 IF get_next_sequence_number%ISOPEN THEN
367 CLOSE get_next_sequence_number;
368 END IF;
369 END generate_invoice_number;
370
371
372 END RCV_GAPLESS_NUMBERING;