[Home] [Help]
PACKAGE BODY: APPS.PO_DOC_NUMBERING_PKG
Source
1 package body po_doc_numbering_pkg AS
2 /* $Header: po_doc_numbering_pkg.plb 120.35.12020000.8 2013/04/30 06:16:52 amalick ship $ */
3 d_pkg_name CONSTANT varchar2(50) := PO_LOG.get_package_base('PO_DOC_NUMBERING_PKG');
4
5 PROCEDURE DEFAULT_DOC_NUMBER_UDA( p_doc_header_id IN NUMBER,
6 p_draft_id IN NUMBER,
7 p_template_id IN NUMBER,
8 p_context_usage IN VARCHAR2,
9 p_source_org_owned IN VARCHAR2,
10 p_caller IN VARCHAR2,
11 x_doc_number OUT NOCOPY VARCHAR2,
12 x_return_status OUT NOCOPY VARCHAR2)
13 IS
14
15 d_api_name CONSTANT VARCHAR2(30) := 'DEFAULT_DOC_NUMBER_UDA';
16 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
17 d_progress NUMBER := 0;
18
19 l_po_uda_usage_object_array PO_UDA_USAGE_OBJECT_ARRAY;
20 l_template_id NUMBER;
21 l_po_uda_usage_object PO_UDA_USAGE_OBJECT;
22 l_context EGO_COL_NAME_VALUE_PAIR_ARRAY ;
23
24 l_return_status VARCHAR2(2);
25 l_errorcode NUMBER;
26 l_msg_count NUMBER;
27 l_msg_data VARCHAR2(4000);
28 l_attrb_grp_id NUMBER;
29
30 l_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
31 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
32 l_failed_row_id_list VARCHAR2(1000);
33 l_doc_number VARCHAR2(50);
34 l_attr_list VARCHAR2(2000);
35 l_attr_group_id NUMBER;
36 l_attr_group_name VARCHAR2(30);
37 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
38 l_src_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
39 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
40 l_external_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE;
41 l_src_object_name VARCHAR2(200);
42 l_src_data_level VARCHAR2(100) := 'PO_HEADER';
43 l_source_org_owned VARCHAR2(2) := p_source_org_owned;
44
45 l_ag_type VARCHAR2(50);
46
47 CURSOR c_attr_list(p_ag_type VARCHAR2, p_ag_name VARCHAR2) IS
48 SELECT END_USER_COLUMN_NAME
49 FROM FND_DESCR_FLEX_COLUMN_USAGES
50 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_ag_type
51 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_ag_name;
52
53 l_idv_number VARCHAR2(50);
54 l_org_id NUMBER := po_moac_utils_pvt.get_current_org_id; --Bug 13241358/12881115:
55 BEGIN
56
57 IF (PO_LOG.d_proc) THEN
58 PO_LOG.proc_begin(d_module);
59 PO_LOG.proc_begin(d_module, 'p_doc_header_id', p_doc_header_id);
60 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
61 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
62 PO_LOG.proc_begin(d_module, 'p_context_usage', p_context_usage);
63 PO_LOG.proc_begin(d_module, 'p_source_org_owned', p_source_org_owned);
64 PO_LOG.proc_begin(d_module, 'p_caller', p_caller);
65 END IF;
66
67 l_po_uda_usage_object := PO_UDA_USAGE_OBJECT();
68 l_po_uda_usage_object_array := PO_UDA_USAGE_OBJECT_ARRAY();
69
70 IF p_caller = 'PO' AND l_source_org_owned <> 'NA' THEN
71 --In this case we need to get the the Source IDV number from the DB
72 --and pass it as an external parameter
73 BEGIN
74 --This is an autocreate case with idv number populated.
75 SELECT pha.clm_document_number INTO l_idv_number
76 FROM po_headers_draft_all phda, po_headers_all pha
77 WHERE phda.po_header_id = p_doc_header_id
78 AND phda.clm_source_document_id = pha.po_header_id;
79 EXCEPTION
80 WHEN No_Data_Found THEN
81 BEGIN
82 --This is an copy case where idv number is populated
83 SELECT pha2.clm_document_number INTO l_idv_number
84 FROM po_headers_all pha1, po_headers_all pha2
85 WHERE pha1.po_header_id = p_doc_header_id
86 AND pha1.clm_source_document_id = pha2.po_header_id;
87 EXCEPTION
88 WHEN No_Data_Found THEN
89 l_idv_number := NULL;
90 l_source_org_owned :='NA';
91 END;
92 END;
93 PO_LOG.proc_begin(d_module, 'Source IDV number ', l_idv_number);
94 END IF;
95
96 IF p_caller = 'REQ' OR p_caller = 'SOL' OR p_caller = 'SOL_AMD' THEN
97 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY(
98 EGO_COL_NAME_VALUE_PAIR_OBJ('DOCUMENT_TYPE', p_context_usage),
99 EGO_COL_NAME_VALUE_PAIR_OBJ('ISSUING_ORG_OWNED', l_source_org_owned),
100 EGO_COL_NAME_VALUE_PAIR_OBJ('ORG_ID', To_Char(l_org_id))
101 /*Bug 13241358/12881115: instead of using fnd_global.org_id use
102 po_moac_utils_pvt.get_current_org_id*/
103 );
104 ELSIF p_caller = 'PO_MOD' OR p_caller = 'PO' THEN
105 l_context := EGO_COL_NAME_VALUE_PAIR_ARRAY(
106 EGO_COL_NAME_VALUE_PAIR_OBJ('DOCUMENT_TYPE', p_context_usage),
107 EGO_COL_NAME_VALUE_PAIR_OBJ('ISSUING_ORG_OWNED', l_source_org_owned),
108 EGO_COL_NAME_VALUE_PAIR_OBJ('ORG_ID', To_Char(l_org_id))
109 /*Bug 13241358/12881115: instead of using fnd_global.org_id use
110 po_moac_utils_pvt.get_current_org_id*/
111 );
112 END IF;
113
114 l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,'DOCUMENT_NUMBERING',l_context );
115 l_po_uda_usage_object_array.EXTEND(1);
116 l_po_uda_usage_object_array(l_po_uda_usage_object_array.COUNT) := l_po_uda_usage_object;
117
118 l_external_attr_value_pairs := NULL;
119
120 G_PO_VALIDATION_EVENT_TYPE := 'DEFAULT_DOC' ; --The XV has to skip the validation for 'XXXX' serial number first time.
121
122 IF p_caller = 'PO_MOD' OR p_caller = 'PO' THEN
123
124 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('p_source_doc_number', l_idv_number, l_external_attr_value_pairs);
125 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', FND_API.G_RET_STS_SUCCESS, l_external_attr_value_pairs);
126 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_errorcode', '0', l_external_attr_value_pairs);
127 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_count', '0', l_external_attr_value_pairs);
128 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_data', 'SUCCESS', l_external_attr_value_pairs);
129
130 PO_UDA_DEFAULTING_PKG.DEFAULT_AGS_FROM_USAGE_OBJECT
131 (
132 p_pk1_value => p_doc_header_id
133 ,p_pk2_value => p_draft_id
134 ,p_template_id => p_template_id
135 ,p_address_lookup_type => null
136 ,x_external_attr_value_pairs => l_external_attr_value_pairs
137 ,x_po_uda_usage_object_array => l_po_uda_usage_object_array
138 ,x_return_status => l_return_status
139 ,x_errorcode => l_errorcode
140 ,x_msg_count => l_msg_count
141 ,x_msg_data => l_msg_data
142 );
143
144 ELSIF p_caller = 'REQ' OR p_caller = 'SOL' THEN
145
146 PO_UDA_DEFAULTING_PKG.DEFAULT_AGS_FROM_USAGE_OBJECT
147 (
148 p_pk1_value => p_doc_header_id
149 ,p_template_id => p_template_id
150 ,p_address_lookup_type => null
151 ,x_external_attr_value_pairs => l_external_attr_value_pairs
152 ,x_po_uda_usage_object_array => l_po_uda_usage_object_array
153 ,x_return_status => l_return_status
154 ,x_errorcode => l_errorcode
155 ,x_msg_count => l_msg_count
156 ,x_msg_data => l_msg_data
157 );
158
159 ELSIF p_caller = 'SOL_AMD' THEN
160
161 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('BASE_AUCTION_HEADER_ID', p_draft_id, l_external_attr_value_pairs);
162 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', FND_API.G_RET_STS_SUCCESS, l_external_attr_value_pairs);
163 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_errorcode', '0', l_external_attr_value_pairs);
164 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_count', '0', l_external_attr_value_pairs);
165 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_data', 'SUCCESS', l_external_attr_value_pairs);
166
167 PO_UDA_DEFAULTING_PKG.DEFAULT_AGS_FROM_USAGE_OBJECT
168 (
169 p_pk1_value => p_doc_header_id
170 ,p_template_id => p_template_id
171 ,p_address_lookup_type => null
172 ,x_external_attr_value_pairs => l_external_attr_value_pairs
173 ,x_po_uda_usage_object_array => l_po_uda_usage_object_array
174 ,x_return_status => l_return_status
175 ,x_errorcode => l_errorcode
176 ,x_msg_count => l_msg_count
177 ,x_msg_data => l_msg_data
178 );
179 END IF;
180
181 /* Bug 13241358/12881115 get the attribute group id from
182 usage object, using the attr_group_id get attr group name*/
183 l_attrb_grp_id := l_po_uda_usage_object.Attr_group_Id(1);
184
185 IF PO_LOG.d_stmt THEN
186 PO_LOG.stmt(d_module, d_progress, 'l_attrb_grp_id : ', l_attrb_grp_id);
187 END IF;
188
189 select ags.ATTR_GROUP_NAME
190 into l_attr_group_name
191 from ego_attr_groups_v ags
192 where ags.attr_group_id = l_attrb_grp_id;
193
194 IF PO_LOG.d_stmt THEN
195 PO_LOG.stmt(d_module, d_progress, 'l_attr_group_name : ', l_attr_group_name);
196 END IF;
197
198 /*Bug 13241358/12881115 ends*/
199
200 G_PO_VALIDATION_EVENT_TYPE := 'SAVE' ; --reset to save again.
201
202 --Now we need to call the defauling again because first time it defaults the value
203 --with XXXX, we need to call it again to default the proper value for
204 --PO, SOL, PO_MOD and SOL_AMD cases
205
206 IF p_caller = 'PO_MOD' OR p_caller = 'PO' OR
207 p_caller = 'SOL' OR p_caller = 'SOL_AMD' THEN
208
209 IF p_caller = 'PO_MOD' OR p_caller = 'PO' THEN
210
211 l_ag_type := 'PO_HEADER_EXT_ATTRS';
212 l_src_data_level := 'PO_HEADER';
213
214 l_src_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
215 EGO_COL_NAME_VALUE_PAIR_OBJ(
216 'PO_HEADER_ID', p_doc_header_id
217 ),
218 EGO_COL_NAME_VALUE_PAIR_OBJ(
219 'DRAFT_ID', p_draft_id
220 )
221 );
222
223 ELSIF p_caller = 'SOL' OR p_caller = 'SOL_AMD' THEN
224
225 l_ag_type := 'PON_AUC_HDRS_EXT_ATTRS';
226 l_src_data_level := 'PON_AUC_HEADER';
227
228 l_src_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
229 EGO_COL_NAME_VALUE_PAIR_OBJ(
230 'AUCTION_HEADER_ID', p_doc_header_id
231 )
232 );
233 END IF;
234
235 l_attr_list := '';
236 FOR l_attr_list_rec IN c_attr_list(l_ag_type,l_attr_group_name)
237 LOOP
238 IF l_attr_list IS NULL OR l_attr_list = '' THEN
239 l_attr_list := l_attr_list_rec.END_USER_COLUMN_NAME;
240 ELSE
241 l_attr_list := l_attr_list || ',' || l_attr_list_rec.END_USER_COLUMN_NAME;
242 END IF;
243 END LOOP;
244
245 l_attr_group_request_table := EGO_ATTR_GROUP_REQUEST_TABLE();
246 l_attr_group_request_table.EXTEND(1);
247 l_attr_group_request_table(1) := ego_attr_group_request_obj
248 (
249 l_attrb_grp_id
250 ,201 -- application id is always 201
251 ,l_ag_type
252 ,l_attr_group_name
253 ,l_src_data_level
254 ,NULL
255 ,NULL
256 ,NULL
257 ,NULL
258 ,NULL
259 ,l_attr_list
260 );
261
262
263 l_src_object_name := PO_UDA_DATA_UTIL.g_object_dff_tl(l_ag_type).l_object_name;
264
265 ego_user_attrs_data_pvt.get_user_attrs_data(
266 p_api_version => 1.0
267 ,p_object_name => l_src_object_name --Get it from PO_UDA_AG_TEMPLATES
268 ,p_pk_column_name_value_pairs => l_src_pk_col_name_val_pairs--Source Primary Keys
269 ,p_attr_group_request_table => l_attr_group_request_table --Construct the Attribute Group Table
270 ,x_attributes_row_table => l_attributes_row_table
271 ,x_attributes_data_table => l_attributes_data_table
272 ,x_return_status => l_return_status
273 ,x_errorcode => l_errorcode
274 ,x_msg_count => l_msg_count
275 ,x_msg_data => l_msg_data
276 );
277
278 l_external_attr_value_pairs :=NULL;
279 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', FND_API.G_RET_STS_SUCCESS, l_external_attr_value_pairs);
280 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_errorcode', '0', l_external_attr_value_pairs);
281 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_count', '0', l_external_attr_value_pairs);
282 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_data', 'SUCCESS', l_external_attr_value_pairs);
283 IF p_caller = 'SOL_AMD' THEN
284 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('BASE_AUCTION_HEADER_ID', p_draft_id, l_external_attr_value_pairs);
285 ELSIF p_caller = 'PO' AND l_source_org_owned <> 'NA' THEN
286 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('p_source_doc_number', l_idv_number, l_external_attr_value_pairs);
287 END IF;
288
289 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
290 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
291 (
292 p_template_id => p_template_id
293 ,p_event => 'XD'
294 ,x_external_attr_value_pairs => l_external_attr_value_pairs
295 ,x_pk_column_name_value_pairs => l_src_pk_col_name_val_pairs
296 ,x_attr_name_value_pairs => l_attributes_data_table
297 ,x_attributes_row_table => l_attributes_row_table
298 ,x_return_status => l_return_status
299 ,x_errorcode => l_errorcode
300 ,x_msg_count => l_msg_count
301 ,x_msg_data => l_msg_data
302 );
303 END IF;
304
305
306 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
307 (
308 EGO_COL_NAME_VALUE_PAIR_OBJ
309 (
310 'UDA_TEMPLATE_ID',p_template_id || ''
311 )
312 );
313
314 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
315 EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data
316 (
317 p_api_version => 1.0
318 ,p_object_name => l_src_object_name
319 ,p_attributes_row_table => l_attributes_row_table
320 ,p_attributes_data_table => l_attributes_data_table
321 ,p_pk_column_name_value_pairs => l_src_pk_col_name_val_pairs
322 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
323 ,x_failed_row_id_list => l_failed_row_id_list
324 ,x_return_status => l_return_status
325 ,x_errorcode => l_errorcode
326 ,x_msg_count => l_msg_count
327 ,x_msg_data => l_msg_data
328 );
329
330 END IF;
331 END IF;
332
333 --Now get the document number generated
334 IF p_caller = 'REQ' THEN
335
336 SELECT c_ext_attr40 INTO x_doc_number
337 FROM po_req_headers_ext_b
338 WHERE requisition_header_id = p_doc_header_id
339 AND attr_group_id = l_attrb_grp_id;
340
341 ELSIF p_caller = 'PO' OR p_caller = 'PO_MOD' THEN
342
343 SELECT c_ext_attr40 INTO x_doc_number
344 FROM po_headers_all_ext_b
345 WHERE po_header_id = p_doc_header_id
346 AND draft_id = p_draft_id
347 AND attr_group_id = l_attrb_grp_id;
348
349 ELSIF p_caller = 'SOL' OR p_caller = 'SOL_AMD' THEN
350
351 SELECT c_ext_attr40 INTO x_doc_number
352 FROM pon_auction_headers_ext_b
353 WHERE auction_header_id = p_doc_header_id
354 AND attr_group_id = l_attrb_grp_id;
355 END IF;
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 d_progress := 40;
360 x_return_status := 'U';
361 IF PO_LOG.d_stmt THEN
362 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
363 END IF;
364 END DEFAULT_DOC_NUMBER_UDA;
365
366
367 FUNCTION validate_serial_number(p_serial_num IN VARCHAR2) RETURN varchar2
368 IS
369 l_isValid VARCHAR2(1):= 'Y';
370 l_val NUMBER;
371 l_char1 VARCHAR2(1);
372 l_char3 VARCHAR2(1);
373 l_char4 VARCHAR2(1);
374 BEGIN
375
376 --check whether serial number is of 4 digits or not.
377 IF p_serial_num IS NULL OR p_serial_num='' OR Length(p_serial_num) <> 4 THEN
378 l_isValid :='N';
379 RETURN l_isValid;
380 END IF;
381
382 --Serial number should not have I and O characters
383 IF InStr(p_serial_num,'I') > 0 OR InStr(p_serial_num,'O') > 0 THEN
384 l_isValid :='N';
385 RETURN l_isValid;
386 END IF;
387
388
389 l_char1 := SubStr(p_serial_num,1,1);
390 IF ( NOT(Ascii(l_char1) >=48 AND Ascii(l_char1) <=57)) THEN
391 l_isValid :='N';
392 RETURN l_isValid;
393 END IF;
394
395 --First 2 digits has to be number
396 BEGIN
397 l_val := To_Number(SubStr(p_serial_num,1,2));
398 EXCEPTION
399 WHEN OTHERS THEN
400 l_isValid :='N';
401 RETURN l_isValid;
402 END;
403
404 --Get the 3rd character and validate it,
405 --It should be from [0-9], [A-Z]
406 l_char3 := SubStr(p_serial_num,3,1);
407 IF ( NOT((Ascii(l_char3) >=48 AND Ascii(l_char3) <=57)
408 OR (Ascii(l_char3) >=65 AND Ascii(l_char3) <=90) )) THEN
409 l_isValid :='N';
410 RETURN l_isValid;
411 END IF;
412
413 --Get the 4rd character and validate it,
414 --It should be from [0-9], [A-Z]
415 l_char4 := SubStr(p_serial_num,4,1);
416 IF ( NOT((Ascii(l_char4) >=48 AND Ascii(l_char4) <=57)
417 OR (Ascii(l_char4) >=65 AND Ascii(l_char4) <=90) )) THEN
418 l_isValid :='N';
419 RETURN l_isValid;
420 END IF;
421
422 --if 3rd char is number then 4th has to be number
423 IF ( (Ascii(l_char3) >=48 AND Ascii(l_char3) <=57)
424 and (NOT (Ascii(l_char4) >=48 AND Ascii(l_char4) <=57)) ) THEN
425 l_isValid :='N';
426 RETURN l_isValid;
427 END IF;
428
429 --if 3rd char is alphabet then 4th has to be alphabet
430 IF ( (Ascii(l_char3) >=65 AND Ascii(l_char3) <=90)
431 and (NOT (Ascii(l_char4) >=65 AND Ascii(l_char4) <=90)) ) THEN
432 l_isValid :='N';
433 RETURN l_isValid;
434 END IF;
435
436 RETURN l_isValid;
437 END;
438
439
440 FUNCTION validate_serial_number_2digit(p_serial_num IN VARCHAR2) RETURN varchar2
441 IS
442 l_isValid VARCHAR2(1):= 'Y';
443 l_val NUMBER;
444 l_char1 VARCHAR2(1);
445 BEGIN
446
447 --check whether serial number is of 4 digits or not.
448 IF p_serial_num IS NULL OR p_serial_num='' OR Length(p_serial_num) <> 2 THEN
449 l_isValid :='N';
450 RETURN l_isValid;
451 END IF;
452
453 l_char1 := SubStr(p_serial_num,1,1);
454 IF ( NOT(Ascii(l_char1) >=48 AND Ascii(l_char1) <=57)) THEN
455 l_isValid :='N';
456 RETURN l_isValid;
457 END IF;
458
459
460 --First 2 digits has to be number
461 BEGIN
462 l_val := To_Number(p_serial_num);
463 EXCEPTION
464 WHEN OTHERS THEN
465 l_isValid :='N';
466 RETURN l_isValid;
467 END;
468
469 RETURN l_isValid;
470 END;
471
472
473
474 PROCEDURE req_numbering_XD
475 (x_prefix IN OUT NOCOPY VARCHAR2
476 ,x_fiscal_year IN OUT NOCOPY NUMBER
477 ,x_agency_identifier IN OUT NOCOPY VARCHAR2
478 ,x_agency_identifier_hidden IN OUT NOCOPY VARCHAR2
479 ,x_allowed_range IN OUT NOCOPY VARCHAR2
480 ,x_allowed_range_hidden IN OUT NOCOPY VARCHAR2
481 ,x_min_value IN OUT NOCOPY VARCHAR2
482 ,x_max_value IN OUT NOCOPY VARCHAR2
483 ,x_serial_number IN OUT NOCOPY VARCHAR2
484 ,x_delimiter IN OUT NOCOPY VARCHAR2
485 ,x_document_number IN OUT NOCOPY VARCHAR2
486 ,x_return_status OUT NOCOPY VARCHAR2
487 ,x_errorcode OUT NOCOPY VARCHAR2
488 ,x_msg_count OUT NOCOPY VARCHAR2
489 ,x_msg_data OUT NOCOPY VARCHAR2
490 )
491 IS
492
493
494 l_query VARCHAR2(4000);
495 l_col_name VARCHAR2(20);
496 l_attrb_grp_id NUMBER;
497
498 l_attr_grp_ids Dbms_Sql.number_table;
499 l_where_cls VARCHAR2(100):='';
500
501 min_code NUMBER;
502 max_code NUMBER;
503 l_lookup_code NUMBER;
504
505 CURSOR C IS
506 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
507 from PO_UDA_AG_TEMPLATE_USAGES puatu,
508 po_uda_ag_templates puat
509 WHERE puat.ENTITY_CODE='PO_REQ_HEADER_EXT_ATTRS'
510 AND puat.document_level='HEADER'
511 AND puat.template_id = puatu.template_id
512 AND puatu.attribute_category='DOCUMENT_NUMBERING';
513
514 CURSOR RANGES IS
515 SELECT lookup_code
516 FROM (
517 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_ALLOWED_RANGE'
518 and enabled_flag = 'Y'
519 and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code);
520
521 l_ranges_array Dbms_Sql.varchar2_table;
522 l_serial_num_fetched BOOLEAN := FALSE;
523
524 BEGIN
525 -- If document number is already generated, just return and don't redefault
526 x_return_status := FND_API.G_RET_STS_SUCCESS;
527 x_errorcode := '0';
528 x_msg_count := '1';
529 x_msg_data := 'SUCCESS';
530
531 IF(x_document_number is null) THEN
532
533 -- Do the defaulting for attributes and set the document number
534 x_prefix := 'PR';
535
536 SELECT To_Char(SYSDATE,'YY')
537 INTO x_fiscal_year
538 FROM dual;
539
540 BEGIN
541 SELECT lookup_code
542 INTO x_agency_identifier
543 FROM (
544 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_AGENCY_IDENTIFIER'
545 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
546 WHERE ROWNUM=1;
547 EXCEPTION
548 WHEN No_Data_Found THEN
549 x_return_status := FND_API.G_RET_STS_ERROR;
550 x_errorcode := '-1';
551 x_msg_count := '1';
552 x_msg_data := 'Agency Identifiers not defined';
553 fnd_message.set_name('PO', 'PO_CLM_AGENCY_IDENTIFIER_NVD');
554 fnd_msg_pub.ADD;
555 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
556 END;
557
558 x_agency_identifier_hidden := x_agency_identifier;
559 --x_agency_identifier := 'CDC';
560
561 x_delimiter := '-';
562
563 OPEN C;
564 FETCH C BULK COLLECT INTO l_attr_grp_ids;
565 CLOSE C;
566
567 FOR i IN 1..l_attr_grp_ids.Count LOOP
568 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
569 END LOOP;
570
571 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
572
573 IF l_serial_num_fetched = FALSE THEN
574
575 OPEN RANGES;
576 FETCH RANGES BULK COLLECT INTO l_ranges_array;
577 CLOSE RANGES;
578
579 FOR i IN 1..l_ranges_array.Count LOOP
580
581 x_min_value := SubStr(l_ranges_array(i),1,4);
582 x_max_value := SubStr(l_ranges_array(i),6);
583
584 -- Bug 16468041 Rewriting the below query due to performance issue.
585
586 /*l_query := ' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
587 ' ( select min(to_number(lookup_code)) from ( '||
588 ' SELECT lookup_code FROM po_lookup_codes '||
589 ' WHERE to_number(lookup_code) BETWEEN '||
590 ' (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
591 x_min_value||'''and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
592 ' and (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
593 x_max_value||''' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
594 ' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'' '||
595 ' minus '||
596 ' SELECT distinct plc.lookup_code FROM '||
597 ' PO_REQ_HEADERS_EXT_B prheb, po_lookup_codes plc ' ||
598 ' WHERE plc.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
599 ' and prheb.C_EXT_ATTR9 = plc.displayed_field ' ||
600 ' and prheb.C_EXT_ATTR1 = '''||x_prefix ||''''||
601 ' and prheb.C_EXT_ATTR2 = '''||x_fiscal_year ||''''||
602 ' and prheb.C_EXT_ATTR3 = '''||x_agency_identifier ||''''||
603 ' and prheb.attr_group_id in ('|| l_where_cls ||')))' ||
604 ' AND lookup_type = ''PO_CLM_SERIAL_NUM_CONSTANTS'' ';*/
605
606 BEGIN
607 SELECT to_number(lookup_code)
608 INTO min_code
609 FROM fnd_lookup_values
610 WHERE meaning = x_min_value
611 AND language = userenv ('LANG')
612 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
613
614 SELECT to_number(lookup_code)
615 INTO max_code
616 FROM fnd_lookup_values
617 WHERE meaning = x_max_value
618 AND language = userenv ('LANG')
619 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
620
621 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
622 ' FROM PO_REQ_HEADERS_EXT_B prheb, fnd_lookup_values flv ' ||
623 ' WHERE prheb.C_EXT_ATTR1 = '''||x_prefix ||''''||
624 ' AND prheb.C_EXT_ATTR2 = '''||x_fiscal_year ||''''||
625 ' AND prheb.C_EXT_ATTR3 = '''||x_agency_identifier ||''''||
626 ' AND flv.meaning = prheb.C_EXT_ATTR9 ' ||
627 ' AND flv.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
628 ' AND flv.language = userenv (''LANG'')' ||
629 ' AND to_number(flv.lookup_code) between ' || min_code || ' AND ' || max_code ||
630 ' AND prheb.attr_group_id in (' || l_where_cls || ')';
631
632 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
633
634 SELECT meaning
635 INTO x_serial_number
636 FROM fnd_lookup_values
637 WHERE lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS'
638 AND language = userenv ('LANG')
639 AND to_number(lookup_code) = Nvl(l_lookup_code, min_code) --<Bug 16699792>
640 AND to_number(lookup_code) <= max_code; --<Bug 16699792>
641
642 --EXECUTE IMMEDIATE l_query INTO x_serial_number ;
643 l_serial_num_fetched := TRUE;
644 x_allowed_range_hidden := l_ranges_array(i);
645 x_allowed_range := l_ranges_array(i);
646 EXIT; --exit the loop as number is fetched
647 EXCEPTION
648 WHEN No_Data_Found THEN
649 l_serial_num_fetched := FALSE;
650 WHEN OTHERS THEN
651 x_return_status := FND_API.G_RET_STS_ERROR;
652 x_errorcode := '-1';
653 x_msg_count := '1';
654 x_msg_data := 'Unable to generate serial number';
655 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
656 fnd_msg_pub.ADD;
657 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
658 END;
659
660 END LOOP;
661
662 END IF;--if l_serial_num_fetched = false
663
664 IF l_serial_num_fetched = TRUE THEN
665 x_document_number := x_prefix || x_delimiter
666 || x_fiscal_year || x_delimiter
667 || x_agency_identifier || x_delimiter
668 || x_serial_number ;
669 ELSE
670 x_return_status := FND_API.G_RET_STS_ERROR;
671 x_errorcode := '-1';
672 x_msg_count := '1';
673 x_msg_data := 'Unable to generate serial number';
674 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
675 fnd_msg_pub.ADD;
676 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
677 END IF;
678 END IF;
679
680 END req_numbering_XD;
681
682
683 PROCEDURE req_numbering_XDN
684 (x_requisition_header_id IN NUMBER
685 ,x_prefix IN OUT NOCOPY VARCHAR2
686 ,x_fiscal_year IN OUT NOCOPY NUMBER
687 ,x_agency_identifier IN OUT NOCOPY VARCHAR2
688 ,x_serial_number IN OUT NOCOPY VARCHAR2
689 ,x_delimiter IN OUT NOCOPY VARCHAR2
690 ,x_document_number IN OUT NOCOPY VARCHAR2
691 ,x_return_status OUT NOCOPY VARCHAR2
692 ,x_errorcode OUT NOCOPY VARCHAR2
693 ,x_msg_count OUT NOCOPY VARCHAR2
694 ,x_msg_data OUT NOCOPY VARCHAR2
695 )
696 IS
697
698 BEGIN
699
700 x_serial_number := Upper(x_serial_number);
701
702 x_document_number := x_prefix || x_delimiter
703 || x_fiscal_year || x_delimiter
704 || x_agency_identifier || x_delimiter
705 || x_serial_number;
706
707 req_numbering_XV(x_requisition_header_id
708 ,x_prefix
709 ,x_fiscal_year
710 ,x_agency_identifier
711 ,x_serial_number
712 ,x_document_number
713 ,x_return_status
714 ,x_errorcode
715 ,x_msg_count
716 ,x_msg_data
717 );
718
719 END req_numbering_XDN;
720
721
722 PROCEDURE req_numbering_XV
723 (x_requisition_header_id IN NUMBER
724 ,x_prefix IN VARCHAR2
725 ,x_fiscal_year IN NUMBER
726 ,x_agency_identifier IN VARCHAR2
727 ,x_serial_number IN VARCHAR2
728 ,x_document_number IN VARCHAR2
729 ,x_return_status OUT NOCOPY VARCHAR2
730 ,x_errorcode OUT NOCOPY VARCHAR2
731 ,x_msg_count OUT NOCOPY VARCHAR2
732 ,x_msg_data OUT NOCOPY VARCHAR2
733 )
734 IS
735
736
737 l_query VARCHAR2(4000);
738 l_col_name VARCHAR2(20);
739 l_attrb_grp_id NUMBER;
740 l_cnt NUMBER;
741
742 BEGIN
743 --If any of the value is null then we need to throw back the error
744 IF x_prefix IS NULL OR x_prefix = '' OR
745 x_fiscal_year IS NULL OR
746 x_agency_identifier IS NULL OR x_agency_identifier = '' OR
747 x_serial_number IS NULL OR x_serial_number = '' THEN
748
749 x_return_status := FND_API.G_RET_STS_ERROR;
750 x_errorcode := '-1';
751 x_msg_count := '1';
752 x_msg_data := 'All the fields are compulsory, please enter the value for all the Attributes.';
753
754 fnd_message.set_name('PO', 'PO_DOC_NUM_ALL_FIELDS_COMPUL');
755 fnd_msg_pub.ADD;
756 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
757
758 END IF;
759
760 IF 'N' = validate_serial_number(Upper(x_serial_number)) THEN
761 x_return_status := FND_API.G_RET_STS_ERROR;
762 x_errorcode := '-1';
763 x_msg_count := '1';
764 x_msg_data := 'Serial number is invalid.';
765 fnd_message.set_name('PO', 'PO_CLM_SERIAL_NUM_INVALID');
766 fnd_msg_pub.ADD;
767 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
768
769 ELSE
770 select count(*) INTO l_cnt
771 from po_req_headers_ext_b
772 WHERE C_EXT_ATTR40 = x_document_number
773 AND requisition_header_id <> x_requisition_header_id;
774
775 IF l_cnt > 0 THEN
776 x_return_status := FND_API.G_RET_STS_ERROR;
777 x_errorcode := '-1';
778 x_msg_count := '1';
779 x_msg_data := 'Document Number is not Unique';
780 fnd_message.set_name('PO', 'PO_DOC_NUM_NOT_UNIQUE');
781 fnd_msg_pub.ADD;
782 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
783 ELSE
784 x_return_status := FND_API.G_RET_STS_SUCCESS;
785 x_errorcode := '0';
786 x_msg_count := '1';
787 x_msg_data := 'SUCCESS';
788 END IF;
789 END IF;
790
791 END req_numbering_XV;
792
793
794 PROCEDURE req_numbering_XLE
795 (x_prefix IN OUT NOCOPY VARCHAR2
796 ,x_fiscal_year IN OUT NOCOPY NUMBER
797 ,x_agency_identifier IN OUT NOCOPY VARCHAR2
798 ,x_agency_identifier_hidden IN OUT NOCOPY VARCHAR2
799 ,x_allowed_range IN OUT NOCOPY VARCHAR2
800 ,x_allowed_range_hidden IN OUT NOCOPY VARCHAR2
801 ,x_min_value IN OUT NOCOPY VARCHAR2
802 ,x_max_value IN OUT NOCOPY VARCHAR2
803 ,x_serial_number IN OUT NOCOPY VARCHAR2
804 ,x_delimiter IN OUT NOCOPY VARCHAR2
805 ,x_document_number IN OUT NOCOPY VARCHAR2
806 ,x_return_status OUT NOCOPY VARCHAR2
807 ,x_errorcode OUT NOCOPY VARCHAR2
808 ,x_msg_count OUT NOCOPY VARCHAR2
809 ,x_msg_data OUT NOCOPY VARCHAR2
810 )
811 IS
812
813
814 l_query VARCHAR2(4000);
815 l_col_name VARCHAR2(20);
816 l_attrb_grp_id NUMBER;
817
818 l_attr_grp_ids Dbms_Sql.number_table;
819 l_where_cls VARCHAR2(100):='';
820
821 CURSOR C IS
822 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
823 from PO_UDA_AG_TEMPLATE_USAGES puatu,
824 po_uda_ag_templates puat
825 WHERE puat.ENTITY_CODE='PO_REQ_HEADER_EXT_ATTRS'
826 AND puat.document_level='HEADER'
827 AND puat.template_id = puatu.template_id
828 AND puatu.attribute_category='DOCUMENT_NUMBERING';
829
830 l_serial_num_fetched BOOLEAN := FALSE;
831
832 min_code NUMBER;
833 max_code NUMBER;
834 l_lookup_code NUMBER;
835
836 BEGIN
837
838 x_return_status := FND_API.G_RET_STS_SUCCESS;
839 x_errorcode := '0';
840 x_msg_count := '1';
841 x_msg_data := 'SUCCESS';
842
843 IF x_agency_identifier_hidden <> x_agency_identifier OR
844 x_allowed_range_hidden <> x_allowed_range THEN
845
846 x_agency_identifier_hidden := x_agency_identifier;
847 x_allowed_range_hidden := x_allowed_range;
848
849 x_min_value := SubStr(x_allowed_range,1,4);
850 x_max_value := SubStr(x_allowed_range,6);
851
852 OPEN C;
853 FETCH C BULK COLLECT INTO l_attr_grp_ids;
854 CLOSE C;
855
856 FOR i IN 1..l_attr_grp_ids.Count LOOP
857 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
858 END LOOP;
859
860 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
861
862 -- Bug 16468041 Rewriting the below query due to performance issue.
863
864 /*l_query := ' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
865 ' ( select min(to_number(lookup_code)) from ( '||
866 ' SELECT lookup_code FROM po_lookup_codes '||
867 ' WHERE to_number(lookup_code) BETWEEN '||
868 ' (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
869 x_min_value||'''and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
870 ' and (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
871 x_max_value||''' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
872 ' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'' '||
873 ' minus '||
874 ' SELECT distinct plc.lookup_code FROM '||
875 ' PO_REQ_HEADERS_EXT_B prheb, po_lookup_codes plc ' ||
876 ' WHERE plc.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
877 ' and prheb.C_EXT_ATTR9 = plc.displayed_field ' ||
878 ' and prheb.C_EXT_ATTR1 = '''||x_prefix ||''''||
879 ' and prheb.C_EXT_ATTR2 = '''||x_fiscal_year ||''''||
880 ' and prheb.C_EXT_ATTR3 = '''||x_agency_identifier ||''''||
881 ' and prheb.attr_group_id in ('|| l_where_cls ||')))' ||
882 ' AND lookup_type = ''PO_CLM_SERIAL_NUM_CONSTANTS'' ';*/
883
884
885 BEGIN
886
887 SELECT to_number(lookup_code)
888 INTO min_code
889 FROM fnd_lookup_values
890 WHERE meaning = x_min_value
891 AND language = userenv ('LANG')
892 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
893
894 SELECT to_number(lookup_code)
895 INTO max_code
896 FROM fnd_lookup_values
897 WHERE meaning = x_max_value
898 AND language = userenv ('LANG')
899 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
900
901 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
902 ' FROM PO_REQ_HEADERS_EXT_B prheb, fnd_lookup_values flv ' ||
903 ' WHERE prheb.C_EXT_ATTR1 = '''||x_prefix ||''''||
904 ' AND prheb.C_EXT_ATTR2 = '''||x_fiscal_year ||''''||
905 ' AND prheb.C_EXT_ATTR3 = '''||x_agency_identifier ||''''||
906 ' AND flv.meaning = prheb.C_EXT_ATTR9 ' ||
907 ' AND flv.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
908 ' AND flv.language = userenv (''LANG'')' ||
909 ' AND to_number(flv.lookup_code) between ' || min_code || ' AND ' || max_code ||
910 ' AND prheb.attr_group_id in (' || l_where_cls || ')';
911
912 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
913
914 SELECT meaning
915 INTO x_serial_number
916 FROM fnd_lookup_values
917 WHERE lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS'
918 AND language = userenv ('LANG')
919 AND to_number(lookup_code) = Nvl(l_lookup_code, min_code) --<Bug 16699792>
920 AND to_number(lookup_code) <= max_code; --<Bug 16699792>
921
922 --EXECUTE IMMEDIATE l_query INTO x_serial_number ;
923 l_serial_num_fetched := TRUE;
924 EXCEPTION
925 WHEN No_Data_Found THEN
926 l_serial_num_fetched := FALSE;
927 WHEN OTHERS THEN
928 x_return_status := FND_API.G_RET_STS_ERROR;
929 x_errorcode := '-1';
930 x_msg_count := '1';
931 x_msg_data := 'Unable to generate serial number';
932 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
933 fnd_msg_pub.ADD;
934 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
935 END;
936
937 IF l_serial_num_fetched = TRUE THEN
938 x_document_number := x_prefix || x_delimiter
939 || x_fiscal_year || x_delimiter
940 || x_agency_identifier || x_delimiter
941 || x_serial_number ;
942 ELSE
943 x_return_status := FND_API.G_RET_STS_ERROR;
944 x_errorcode := '-1';
945 x_msg_count := '1';
946 x_msg_data := 'Unable to generate serial number';
947 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
948 fnd_msg_pub.ADD;
949 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
950 END IF;
951 END IF;
952
953 END req_numbering_XLE;
954
955
956 PROCEDURE req_numbering_XSC
957 (x_requisition_header_id IN NUMBER
958 ,x_prefix IN OUT NOCOPY VARCHAR2
959 ,x_fiscal_year IN OUT NOCOPY NUMBER
960 ,x_agency_identifier IN OUT NOCOPY VARCHAR2
961 ,x_serial_number IN OUT NOCOPY VARCHAR2
962 ,x_delimiter IN OUT NOCOPY VARCHAR2
963 ,x_document_number IN OUT NOCOPY VARCHAR2
964 ,x_return_status OUT NOCOPY VARCHAR2
965 ,x_errorcode OUT NOCOPY VARCHAR2
966 ,x_msg_count OUT NOCOPY VARCHAR2
967 ,x_msg_data OUT NOCOPY VARCHAR2
968 )
969 IS
970
971 BEGIN
972
973 req_numbering_XV(x_requisition_header_id
974 ,x_prefix
975 ,x_fiscal_year
976 ,x_agency_identifier
977 ,x_serial_number
978 ,x_document_number
979 ,x_return_status
980 ,x_errorcode
981 ,x_msg_count
982 ,x_msg_data
983 );
984
985 END req_numbering_XSC;
986
987
988 PROCEDURE sol_numbering_XD
989 (x_prefix IN OUT NOCOPY VARCHAR2
990 ,x_fiscal_year IN OUT NOCOPY NUMBER
991 ,x_agency_identifier IN OUT NOCOPY VARCHAR2
992 ,x_agency_identifier_hidden IN OUT NOCOPY VARCHAR2
993 ,x_allowed_range IN OUT NOCOPY VARCHAR2
994 ,x_allowed_range_hidden IN OUT NOCOPY VARCHAR2
995 ,x_min_value IN OUT NOCOPY VARCHAR2
996 ,x_max_value IN OUT NOCOPY VARCHAR2
997 ,x_serial_number IN OUT NOCOPY VARCHAR2
998 ,x_delimiter IN OUT NOCOPY VARCHAR2
999 ,x_document_number IN OUT NOCOPY VARCHAR2
1000 ,x_return_status OUT NOCOPY VARCHAR2
1001 ,x_errorcode OUT NOCOPY VARCHAR2
1002 ,x_msg_count OUT NOCOPY VARCHAR2
1003 ,x_msg_data OUT NOCOPY VARCHAR2
1004 )
1005 IS
1006
1007
1008 l_query VARCHAR2(4000);
1009 l_col_name VARCHAR2(20);
1010 l_attrb_grp_id NUMBER;
1011
1012 l_attr_grp_ids Dbms_Sql.number_table;
1013 l_where_cls VARCHAR2(100):='';
1014
1015 CURSOR C IS
1016 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
1017 from PO_UDA_AG_TEMPLATE_USAGES puatu,
1018 po_uda_ag_templates puat
1019 WHERE puat.ENTITY_CODE='PON_AUC_HDRS_EXT_ATTRS'
1020 AND puat.document_level='HEADER'
1021 AND puat.template_id = puatu.template_id
1022 AND puatu.attribute_category='DOCUMENT_NUMBERING'
1023 AND puatu.attribute1='Base Document'
1024 AND puatu.attribute2='NA';
1025
1026 min_code NUMBER;
1027 max_code NUMBER;
1028 l_lookup_code NUMBER;
1029 BEGIN
1030 -- If document number is already generated, just return and don't redefault
1031
1032 IF(x_document_number is null) THEN
1033
1034 -- Do the defaulting for attributes and set the document number
1035 x_prefix := 'SOL';
1036
1037 SELECT To_Char(SYSDATE,'YY')
1038 INTO x_fiscal_year
1039 FROM dual;
1040
1041 BEGIN
1042 SELECT lookup_code
1043 INTO x_agency_identifier
1044 FROM (
1045 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_AGENCY_IDENTIFIER'
1046 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
1047 WHERE ROWNUM=1;
1048 EXCEPTION
1049 WHEN No_Data_Found THEN
1050 fnd_message.set_name('PO', 'PO_CLM_AGENCY_IDENTIFIER_NVD');
1051 fnd_msg_pub.ADD;
1052 END;
1053
1054
1055 x_agency_identifier_hidden := x_agency_identifier;
1056 --x_agency_identifier := 'CDC';
1057
1058 --For allowed_range, reserved_ranges and serial number generation
1059 --we need to check with CACI.
1060 BEGIN
1061 SELECT lookup_code
1062 INTO x_allowed_range
1063 FROM (
1064 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_ALLOWED_RANGE'
1065 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
1066 WHERE ROWNUM=1;
1067 EXCEPTION
1068 WHEN No_Data_Found THEN
1069 fnd_message.set_name('PO', 'PO_CLM_ALLOWED_RANGE_NVD');
1070 fnd_msg_pub.ADD;
1071 END;
1072
1073 x_allowed_range_hidden := x_allowed_range;
1074 --x_allowed_range := '0001_9999';
1075
1076 x_min_value := SubStr(x_allowed_range,1,4);
1077 x_max_value := SubStr(x_allowed_range,6);
1078 x_delimiter := '-';
1079 --x_serial_number := '0001';
1080
1081 OPEN C;
1082 FETCH C BULK COLLECT INTO l_attr_grp_ids;
1083 CLOSE C;
1084
1085 FOR i IN 1..l_attr_grp_ids.Count LOOP
1086 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
1087 END LOOP;
1088
1089 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
1090
1091 -- Bug 16468041 Rewriting the below query due to performance issue.
1092 /*l_query := ' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
1093 ' ( select min(to_number(lookup_code)) from ( '||
1094 ' SELECT lookup_code FROM po_lookup_codes '||
1095 ' WHERE to_number(lookup_code) BETWEEN '||
1096 ' (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
1097 x_min_value||'''and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
1098 ' and (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
1099 x_max_value||''' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
1100 ' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'' '||
1101 ' minus '||
1102 ' SELECT distinct plc.lookup_code FROM '||
1103 ' PON_AUCTION_HEADERS_EXT_B prheb, po_lookup_codes plc ' ||
1104 ' WHERE plc.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
1105 ' and prheb.C_EXT_ATTR9 = plc.displayed_field ' ||
1106 ' and prheb.C_EXT_ATTR1 = '''||x_prefix ||''''||
1107 ' and prheb.C_EXT_ATTR2 = '||x_fiscal_year ||
1108 ' and prheb.C_EXT_ATTR3 = '''||x_agency_identifier ||''''||
1109 ' and prheb.attr_group_id in ('|| l_where_cls ||')))' ||
1110 ' AND lookup_type = ''PO_CLM_SERIAL_NUM_CONSTANTS'' ';*/
1111
1112 BEGIN
1113
1114 SELECT to_number(lookup_code)
1115 INTO min_code
1116 FROM fnd_lookup_values
1117 WHERE meaning = x_min_value
1118 AND language = userenv ('LANG')
1119 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
1120
1121 SELECT to_number(lookup_code)
1122 INTO max_code
1123 FROM fnd_lookup_values
1124 WHERE meaning = x_max_value
1125 AND language = userenv ('LANG')
1126 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
1127
1128 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
1129 ' FROM PON_AUCTION_HEADERS_EXT_B prheb, fnd_lookup_values flv ' ||
1130 ' WHERE prheb.C_EXT_ATTR1 = '''||x_prefix ||''''||
1131 ' AND prheb.C_EXT_ATTR2 = '||x_fiscal_year ||
1132 ' AND prheb.C_EXT_ATTR3 = '''||x_agency_identifier ||''''||
1133 ' AND flv.meaning = prheb.C_EXT_ATTR9 ' ||
1134 ' AND flv.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
1135 ' AND flv.language = userenv (''LANG'')' ||
1136 ' AND to_number(flv.lookup_code) between ' || min_code || ' AND ' || max_code ||
1137 ' AND prheb.attr_group_id in (' || l_where_cls || ')';
1138
1139 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
1140
1141 SELECT meaning
1142 INTO x_serial_number
1143 FROM fnd_lookup_values
1144 WHERE lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS'
1145 AND language = userenv ('LANG')
1146 AND to_number(lookup_code) = Nvl(l_lookup_code, min_code) --<Bug 16699792>
1147 AND to_number(lookup_code) <= max_code; --<Bug 16699792>
1148 --EXECUTE IMMEDIATE l_query INTO x_serial_number ;
1149 END;
1150
1151
1152 x_document_number := x_prefix || x_delimiter
1153 || x_fiscal_year || x_delimiter
1154 || x_agency_identifier || x_delimiter
1155 || x_serial_number ;
1156
1157 END IF;
1158
1159 x_return_status := FND_API.G_RET_STS_SUCCESS;
1160 x_errorcode := '0';
1161 x_msg_count := '1';
1162 x_msg_data := 'SUCCESS';
1163
1164 END sol_numbering_XD;
1165
1166
1167
1168
1169 PROCEDURE sol_numbering_XDN
1170 (x_auction_header_id IN NUMBER
1171 ,x_prefix IN OUT NOCOPY VARCHAR2
1172 ,x_fiscal_year IN OUT NOCOPY NUMBER
1173 ,x_agency_identifier IN OUT NOCOPY VARCHAR2
1174 ,x_serial_number IN OUT NOCOPY VARCHAR2
1175 ,x_delimiter IN OUT NOCOPY VARCHAR2
1176 ,x_document_number IN OUT NOCOPY VARCHAR2
1177 ,x_return_status OUT NOCOPY VARCHAR2
1178 ,x_errorcode OUT NOCOPY VARCHAR2
1179 ,x_msg_count OUT NOCOPY VARCHAR2
1180 ,x_msg_data OUT NOCOPY VARCHAR2
1181 )
1182 IS
1183
1184 BEGIN
1185
1186 x_serial_number := Upper(x_serial_number);
1187
1188 x_document_number := x_prefix || x_delimiter
1189 || x_fiscal_year || x_delimiter
1190 || x_agency_identifier || x_delimiter
1191 || x_serial_number;
1192
1193 sol_numbering_XV(x_auction_header_id
1194 ,x_prefix
1195 ,x_fiscal_year
1196 ,x_agency_identifier
1197 ,x_serial_number
1198 ,x_document_number
1199 ,x_return_status
1200 ,x_errorcode
1201 ,x_msg_count
1202 ,x_msg_data
1203 );
1204
1205 END sol_numbering_XDN;
1206
1207
1208
1209 PROCEDURE sol_numbering_XV
1210 (x_auction_header_id IN NUMBER
1211 ,x_prefix IN VARCHAR2
1212 ,x_fiscal_year IN NUMBER
1213 ,x_agency_identifier IN VARCHAR2
1214 ,x_serial_number IN VARCHAR2
1215 ,x_document_number IN VARCHAR2
1216 ,x_return_status OUT NOCOPY VARCHAR2
1217 ,x_errorcode OUT NOCOPY VARCHAR2
1218 ,x_msg_count OUT NOCOPY VARCHAR2
1219 ,x_msg_data OUT NOCOPY VARCHAR2
1220 )
1221 IS
1222
1223
1224 l_query VARCHAR2(4000);
1225 l_col_name VARCHAR2(20);
1226 l_attrb_grp_id NUMBER;
1227 l_cnt NUMBER;
1228
1229 BEGIN
1230
1231 --If any of the value is null then we need to throw back the error
1232 IF x_prefix IS NULL OR x_prefix = '' OR
1233 x_fiscal_year IS NULL OR
1234 x_agency_identifier IS NULL OR x_agency_identifier = '' OR
1235 x_serial_number IS NULL OR x_serial_number = '' THEN
1236
1237 x_return_status := FND_API.G_RET_STS_ERROR;
1238 x_errorcode := '-1';
1239 x_msg_count := '1';
1240 x_msg_data := 'All the fields are compulsory, please enter the value for all the Attributes.';
1241 fnd_message.set_name('PO', 'PO_DOC_NUM_ALL_FIELDS_COMPUL');
1242 fnd_msg_pub.ADD;
1243 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
1244
1245 END IF;
1246
1247 IF 'N' = validate_serial_number(Upper(x_serial_number)) THEN
1248 x_return_status := FND_API.G_RET_STS_ERROR;
1249 x_errorcode := '-1';
1250 x_msg_count := '1';
1251 x_msg_data := 'Serial number is invalid.';
1252 fnd_message.set_name('PO', 'PO_CLM_SERIAL_NUM_INVALID');
1253 fnd_msg_pub.ADD;
1254 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
1255
1256 ELSE
1257
1258 select count(*) INTO l_cnt
1259 from PON_AUCTION_HEADERS_EXT_B
1260 WHERE C_EXT_ATTR40 = x_document_number
1261 AND auction_header_id <> x_auction_header_id;
1262
1263 IF l_cnt > 0 THEN
1264 x_return_status := FND_API.G_RET_STS_ERROR;
1265 x_errorcode := '-1';
1266 x_msg_count := '1';
1267 x_msg_data := 'Document Number is not Unique';
1268 fnd_message.set_name('PO', 'PO_DOC_NUM_NOT_UNIQUE');
1269 fnd_msg_pub.ADD;
1270 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
1271 ELSE
1272 x_return_status := FND_API.G_RET_STS_SUCCESS;
1273 x_errorcode := '0';
1274 x_msg_count := '1';
1275 x_msg_data := 'SUCCESS';
1276 END IF;
1277 END IF;
1278
1279 END sol_numbering_XV;
1280
1281
1282 PROCEDURE sol_numbering_XLE
1283 (x_prefix IN OUT NOCOPY VARCHAR2
1284 ,x_fiscal_year IN OUT NOCOPY NUMBER
1285 ,x_agency_identifier IN OUT NOCOPY VARCHAR2
1286 ,x_agency_identifier_hidden IN OUT NOCOPY VARCHAR2
1287 ,x_allowed_range IN OUT NOCOPY VARCHAR2
1288 ,x_allowed_range_hidden IN OUT NOCOPY VARCHAR2
1289 ,x_min_value IN OUT NOCOPY VARCHAR2
1290 ,x_max_value IN OUT NOCOPY VARCHAR2
1291 ,x_serial_number IN OUT NOCOPY VARCHAR2
1292 ,x_delimiter IN OUT NOCOPY VARCHAR2
1293 ,x_document_number IN OUT NOCOPY VARCHAR2
1294 ,x_return_status OUT NOCOPY VARCHAR2
1295 ,x_errorcode OUT NOCOPY VARCHAR2
1296 ,x_msg_count OUT NOCOPY VARCHAR2
1297 ,x_msg_data OUT NOCOPY VARCHAR2
1298 )
1299 IS
1300
1301
1302 l_query VARCHAR2(4000);
1303 l_col_name VARCHAR2(20);
1304 l_attrb_grp_id NUMBER;
1305
1306 l_attr_grp_ids Dbms_Sql.number_table;
1307 l_where_cls VARCHAR2(100):='';
1308
1309 CURSOR C IS
1310 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
1311 from PO_UDA_AG_TEMPLATE_USAGES puatu,
1312 po_uda_ag_templates puat
1313 WHERE puat.ENTITY_CODE='PON_AUC_HDRS_EXT_ATTRS'
1314 AND puat.document_level='HEADER'
1315 AND puat.template_id = puatu.template_id
1316 AND puatu.attribute_category='DOCUMENT_NUMBERING'
1317 AND puatu.attribute1='Base Document'
1318 AND puatu.attribute2='NA';
1319
1320 min_code NUMBER;
1321 max_code NUMBER;
1322 l_lookup_code NUMBER;
1323
1324 BEGIN
1325
1326 IF x_agency_identifier_hidden <> x_agency_identifier OR
1327 x_allowed_range_hidden <> x_allowed_range THEN
1328
1329 x_agency_identifier_hidden := x_agency_identifier;
1330 x_allowed_range_hidden := x_allowed_range;
1331
1332 x_min_value := SubStr(x_allowed_range,1,4);
1333 x_max_value := SubStr(x_allowed_range,6);
1334
1335 OPEN C;
1336 FETCH C BULK COLLECT INTO l_attr_grp_ids;
1337 CLOSE C;
1338
1339 FOR i IN 1..l_attr_grp_ids.Count LOOP
1340 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
1341 END LOOP;
1342
1343 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
1344
1345 -- Bug 16468041 Rewriting the below query due to performance issue.
1346 /*l_query := ' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
1347 ' ( select min(to_number(lookup_code)) from ( '||
1348 ' SELECT lookup_code FROM po_lookup_codes '||
1349 ' WHERE to_number(lookup_code) BETWEEN '||
1350 ' (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
1351 x_min_value||'''and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
1352 ' and (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
1353 x_max_value||''' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
1354 ' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'' '||
1355 ' minus '||
1356 ' SELECT distinct plc.lookup_code FROM '||
1357 ' PON_AUCTION_HEADERS_EXT_B prheb, po_lookup_codes plc ' ||
1358 ' WHERE plc.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
1359 ' and prheb.C_EXT_ATTR9 = plc.displayed_field ' ||
1360 ' and prheb.C_EXT_ATTR1 = '''||x_prefix ||''''||
1361 ' and prheb.C_EXT_ATTR2 = '||x_fiscal_year ||
1362 ' and prheb.C_EXT_ATTR3 = '''||x_agency_identifier ||''''||
1363 ' and prheb.attr_group_id in ('|| l_where_cls ||')))' ||
1364 ' AND lookup_type = ''PO_CLM_SERIAL_NUM_CONSTANTS'' ';*/
1365
1366
1367 BEGIN
1368 SELECT to_number(lookup_code)
1369 INTO min_code
1370 FROM fnd_lookup_values
1371 WHERE meaning = x_min_value
1372 AND language = userenv ('LANG')
1373 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
1374
1375 SELECT to_number(lookup_code)
1376 INTO max_code
1377 FROM fnd_lookup_values
1378 WHERE meaning = x_max_value
1379 AND language = userenv ('LANG')
1380 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
1381
1382 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
1383 ' FROM PON_AUCTION_HEADERS_EXT_B prheb, fnd_lookup_values flv ' ||
1384 ' WHERE prheb.C_EXT_ATTR9 = plc.displayed_field ' ||
1385 ' AND prheb.C_EXT_ATTR1 = '''||x_prefix ||''''||
1386 ' AND prheb.C_EXT_ATTR2 = '||x_fiscal_year ||
1387 ' AND prheb.C_EXT_ATTR3 = '''||x_agency_identifier ||''''||
1388 ' AND flv.meaning = prheb.C_EXT_ATTR9 ' ||
1389 ' AND flv.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
1390 ' AND flv.language = userenv (''LANG'')' ||
1391 ' AND to_number(flv.lookup_code) between ' || min_code || ' AND ' || max_code ||
1392 ' AND prheb.attr_group_id in (' || l_where_cls || ')';
1393
1394 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
1395
1396 SELECT meaning
1397 INTO x_serial_number
1398 FROM fnd_lookup_values
1399 WHERE lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS'
1400 AND language = userenv ('LANG')
1401 AND to_number(lookup_code) = Nvl(l_lookup_code, min_code) --<Bug 16699792>
1402 AND to_number(lookup_code) <= max_code; --<Bug 16699792>
1403
1404 --EXECUTE IMMEDIATE l_query INTO x_serial_number ;
1405 END;
1406
1407 x_document_number := x_prefix || x_delimiter
1408 || x_fiscal_year || x_delimiter
1409 || x_agency_identifier || x_delimiter
1410 || x_serial_number ;
1411
1412
1413 END IF;
1414
1415 x_return_status := FND_API.G_RET_STS_SUCCESS;
1416 x_errorcode := '0';
1417 x_msg_count := '1';
1418 x_msg_data := 'SUCCESS';
1419
1420 END sol_numbering_XLE;
1421
1422
1423 PROCEDURE sol_numbering_XSC
1424 (x_auction_header_id IN NUMBER
1425 ,x_prefix IN OUT NOCOPY VARCHAR2
1426 ,x_fiscal_year IN OUT NOCOPY NUMBER
1427 ,x_agency_identifier IN OUT NOCOPY VARCHAR2
1428 ,x_serial_number IN OUT NOCOPY VARCHAR2
1429 ,x_delimiter IN OUT NOCOPY VARCHAR2
1430 ,x_document_number IN OUT NOCOPY VARCHAR2
1431 ,x_return_status OUT NOCOPY VARCHAR2
1432 ,x_errorcode OUT NOCOPY VARCHAR2
1433 ,x_msg_count OUT NOCOPY VARCHAR2
1434 ,x_msg_data OUT NOCOPY VARCHAR2
1435 )
1436 IS
1437
1438 BEGIN
1439
1440 sol_numbering_XV(x_auction_header_id
1441 ,x_prefix
1442 ,x_fiscal_year
1443 ,x_agency_identifier
1444 ,x_serial_number
1445 ,x_document_number
1446 ,x_return_status
1447 ,x_errorcode
1448 ,x_msg_count
1449 ,x_msg_data
1450 );
1451
1452 END sol_numbering_XSC;
1453
1454
1455
1456 PROCEDURE order_numbering_XD
1457 (x_prefix IN OUT NOCOPY VARCHAR2
1458 ,x_fiscal_year IN OUT NOCOPY NUMBER
1459 ,x_agency_identifier IN OUT NOCOPY VARCHAR2
1460 ,x_agency_identifier_hidden IN OUT NOCOPY VARCHAR2
1461 ,x_allowed_range IN OUT NOCOPY VARCHAR2
1462 ,x_allowed_range_hidden IN OUT NOCOPY VARCHAR2
1463 ,x_min_value IN OUT NOCOPY VARCHAR2
1464 ,x_max_value IN OUT NOCOPY VARCHAR2
1465 ,x_serial_number IN OUT NOCOPY VARCHAR2
1466 ,x_delimiter IN OUT NOCOPY VARCHAR2
1467 ,x_document_number IN OUT NOCOPY VARCHAR2
1468 ,x_return_status OUT NOCOPY VARCHAR2
1469 ,x_errorcode OUT NOCOPY VARCHAR2
1470 ,x_msg_count OUT NOCOPY VARCHAR2
1471 ,x_msg_data OUT NOCOPY VARCHAR2
1472 )
1473 IS
1474
1475
1476 l_query VARCHAR2(4000);
1477 l_col_name VARCHAR2(20);
1478 l_attrb_grp_id NUMBER;
1479
1480 l_attr_grp_ids Dbms_Sql.number_table;
1481 l_where_cls VARCHAR2(100):='';
1482
1483 CURSOR C IS
1484 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
1485 from PO_UDA_AG_TEMPLATE_USAGES puatu,
1486 po_uda_ag_templates puat
1487 WHERE puat.ENTITY_CODE='PO_HEADER_EXT_ATTRS'
1488 AND puat.document_level='HEADER'
1489 AND puat.template_id = puatu.template_id
1490 AND puatu.attribute_category='DOCUMENT_NUMBERING'
1491 AND puatu.attribute1='Base Document'
1492 AND puatu.attribute2='NA';
1493
1494 min_code NUMBER;
1495 max_code NUMBER;
1496 l_lookup_code NUMBER;
1497
1498 BEGIN
1499 -- If document number is already generated, just return and don't redefault
1500
1501 IF(x_document_number is null) THEN
1502
1503 -- Do the defaulting for attributes and set the document number
1504 x_prefix := 'OR';
1505
1506 SELECT To_Char(SYSDATE,'YY')
1507 INTO x_fiscal_year
1508 FROM dual;
1509
1510 BEGIN
1511 SELECT lookup_code
1512 INTO x_agency_identifier
1513 FROM (
1514 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_AGENCY_IDENTIFIER'
1515 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
1516 WHERE ROWNUM=1;
1517
1518 EXCEPTION
1519 WHEN No_Data_Found THEN
1520 fnd_message.set_name('PO', 'PO_CLM_AGENCY_IDENTIFIER_NVD');
1521 fnd_msg_pub.ADD;
1522 END;
1523
1524 x_agency_identifier_hidden := x_agency_identifier;
1525 --x_agency_identifier := 'CDC';
1526
1527 --For allowed_range, reserved_ranges and serial number generation
1528 --we need to check with CACI.
1529 BEGIN
1530 SELECT lookup_code
1531 INTO x_allowed_range
1532 FROM (
1533 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_ALLOWED_RANGE'
1534 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
1535 WHERE ROWNUM=1;
1536 EXCEPTION
1537 WHEN No_Data_Found THEN
1538 fnd_message.set_name('PO', 'PO_CLM_ALLOWED_RANGE_NVD');
1539 fnd_msg_pub.ADD;
1540 END;
1541
1542 x_allowed_range_hidden := x_allowed_range;
1543 --x_allowed_range := '0001_9999';
1544
1545 x_min_value := SubStr(x_allowed_range,1,4);
1546 x_max_value := SubStr(x_allowed_range,6);
1547 x_delimiter := '-';
1548 --x_serial_number := '0001';
1549
1550 OPEN C;
1551 FETCH C BULK COLLECT INTO l_attr_grp_ids;
1552 CLOSE C;
1553
1554 FOR i IN 1..l_attr_grp_ids.Count LOOP
1555 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
1556 END LOOP;
1557
1558 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
1559
1560 -- Bug 16468041 Rewriting the below query due to performance issue.
1561 /*l_query := ' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
1562 ' ( select min(to_number(lookup_code)) from ( '||
1563 ' SELECT lookup_code FROM po_lookup_codes '||
1564 ' WHERE to_number(lookup_code) BETWEEN '||
1565 ' (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
1566 x_min_value||'''and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
1567 ' and (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
1568 x_max_value||''' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
1569 ' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'' '||
1570 ' minus '||
1571 ' SELECT distinct plc.lookup_code FROM '||
1572 ' PO_HEADERS_ALL_EXT_B prheb, po_lookup_codes plc ' ||
1573 ' WHERE plc.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
1574 ' and prheb.C_EXT_ATTR9 = plc.displayed_field ' ||
1575 ' and prheb.C_EXT_ATTR1 = '''||x_prefix ||''''||
1576 ' and prheb.C_EXT_ATTR2 = '||x_fiscal_year ||
1577 ' and prheb.C_EXT_ATTR3 = '''||x_agency_identifier ||''''||
1578 ' and prheb.attr_group_id in ('|| l_where_cls ||')))' ||
1579 ' AND lookup_type = ''PO_CLM_SERIAL_NUM_CONSTANTS'' ';*/
1580
1581 BEGIN
1582 SELECT to_number(lookup_code)
1583 INTO min_code
1584 FROM fnd_lookup_values
1585 WHERE meaning = x_min_value
1586 AND language = userenv ('LANG')
1587 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
1588
1589 SELECT to_number(lookup_code)
1590 INTO max_code
1591 FROM fnd_lookup_values
1592 WHERE meaning = x_max_value
1593 AND language = userenv ('LANG')
1594 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
1595
1596 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
1597 ' FROM PO_HEADERS_ALL_EXT_B prheb, fnd_lookup_values flv ' ||
1598 ' WHERE prheb..C_EXT_ATTR1 = '''||x_prefix ||''''||
1599 ' AND prheb.C_EXT_ATTR2 = '||x_fiscal_year ||
1600 ' AND prheb.C_EXT_ATTR3 = '''||x_agency_identifier ||''''||
1601 ' AND flv.meaning = prheb.C_EXT_ATTR9 ' ||
1602 ' AND flv.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
1603 ' AND flv.language = userenv (''LANG'')' ||
1604 ' AND to_number(flv.lookup_code) between ' || min_code || ' AND ' || max_code ||
1605 ' AND prheb.attr_group_id in (' || l_where_cls || ')';
1606
1607 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
1608
1609 SELECT meaning
1610 INTO x_serial_number
1611 FROM fnd_lookup_values
1612 WHERE lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS'
1613 AND language = userenv ('LANG')
1614 AND to_number(lookup_code) = Nvl(l_lookup_code, min_code) --<Bug 16699792>
1615 AND to_number(lookup_code) <= max_code; --<Bug 16699792>
1616
1617 --EXECUTE IMMEDIATE l_query INTO x_serial_number ;
1618 END;
1619
1620
1621 x_document_number := x_prefix || x_delimiter
1622 || x_fiscal_year || x_delimiter
1623 || x_agency_identifier || x_delimiter
1624 || x_serial_number ;
1625
1626 END IF;
1627
1628 x_return_status := FND_API.G_RET_STS_SUCCESS;
1629 x_errorcode := '0';
1630 x_msg_count := '1';
1631 x_msg_data := 'SUCCESS';
1632
1633 END order_numbering_XD;
1634
1635
1636
1637
1638 PROCEDURE order_numbering_XDN
1639 (x_order_header_id IN NUMBER
1640 ,x_prefix IN OUT NOCOPY VARCHAR2
1641 ,x_fiscal_year IN OUT NOCOPY NUMBER
1642 ,x_agency_identifier IN OUT NOCOPY VARCHAR2
1643 ,x_serial_number IN OUT NOCOPY VARCHAR2
1644 ,x_delimiter IN OUT NOCOPY VARCHAR2
1645 ,x_document_number IN OUT NOCOPY VARCHAR2
1646 ,x_return_status OUT NOCOPY VARCHAR2
1647 ,x_errorcode OUT NOCOPY VARCHAR2
1648 ,x_msg_count OUT NOCOPY VARCHAR2
1649 ,x_msg_data OUT NOCOPY VARCHAR2
1650 )
1651 IS
1652
1653 BEGIN
1654 x_serial_number := Upper(x_serial_number);
1655 x_document_number := x_prefix || x_delimiter
1656 || x_fiscal_year || x_delimiter
1657 || x_agency_identifier || x_delimiter
1658 || x_serial_number;
1659
1660 order_numbering_XV(x_order_header_id
1661 ,x_prefix
1662 ,x_fiscal_year
1663 ,x_agency_identifier
1664 ,x_serial_number
1665 ,x_document_number
1666 ,x_return_status
1667 ,x_errorcode
1668 ,x_msg_count
1669 ,x_msg_data
1670 );
1671
1672 END order_numbering_XDN;
1673
1674
1675
1676 PROCEDURE order_numbering_XV
1677 (x_order_header_id IN NUMBER
1678 ,x_prefix IN VARCHAR2
1679 ,x_fiscal_year IN NUMBER
1680 ,x_agency_identifier IN VARCHAR2
1681 ,x_serial_number IN VARCHAR2
1682 ,x_document_number IN VARCHAR2
1683 ,x_return_status OUT NOCOPY VARCHAR2
1684 ,x_errorcode OUT NOCOPY VARCHAR2
1685 ,x_msg_count OUT NOCOPY VARCHAR2
1686 ,x_msg_data OUT NOCOPY VARCHAR2
1687 )
1688 IS
1689
1690
1691 l_query VARCHAR2(4000);
1692 l_col_name VARCHAR2(20);
1693 l_attrb_grp_id NUMBER;
1694 l_cnt NUMBER;
1695
1696 BEGIN
1697
1698 --If any of the value is null then we need to throw back the error
1699 IF x_prefix IS NULL OR x_prefix = '' OR
1700 x_fiscal_year IS NULL OR
1701 x_agency_identifier IS NULL OR x_agency_identifier = '' OR
1702 x_serial_number IS NULL OR x_serial_number = '' THEN
1703
1704 x_return_status := FND_API.G_RET_STS_ERROR;
1705 x_errorcode := '-1';
1706 x_msg_count := '1';
1707 x_msg_data := 'All the fields are compulsory, please enter the value for all the Attributes.';
1708 fnd_message.set_name('PO', 'PO_DOC_NUM_ALL_FIELDS_COMPUL');
1709 fnd_msg_pub.ADD;
1710 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
1711
1712 END IF;
1713
1714 IF 'N' = validate_serial_number(Upper(x_serial_number)) THEN
1715 x_return_status := FND_API.G_RET_STS_ERROR;
1716 x_errorcode := '-1';
1717 x_msg_count := '1';
1718 x_msg_data := 'Serial number is invalid.';
1719 fnd_message.set_name('PO', 'PO_CLM_SERIAL_NUM_INVALID');
1720 fnd_msg_pub.ADD;
1721 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
1722
1723 ELSE
1724
1725 select count(*) INTO l_cnt
1726 from PO_HEADERS_ALL_EXT_B
1727 WHERE C_EXT_ATTR40 = x_document_number
1728 AND po_header_id <> x_order_header_id;
1729
1730 IF l_cnt > 0 THEN
1731 x_return_status := FND_API.G_RET_STS_ERROR;
1732 x_errorcode := '-1';
1733 x_msg_count := '1';
1734 x_msg_data := 'Document Number is not Unique';
1735 fnd_message.set_name('PO', 'PO_DOC_NUM_NOT_UNIQUE');
1736 fnd_msg_pub.ADD;
1737 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
1738
1739 ELSE
1740 x_return_status := FND_API.G_RET_STS_SUCCESS;
1741 x_errorcode := '0';
1742 x_msg_count := '1';
1743 x_msg_data := 'SUCCESS';
1744 END IF;
1745 END IF;
1746
1747 END order_numbering_XV;
1748
1749
1750 PROCEDURE order_numbering_XLE
1751 (x_prefix IN OUT NOCOPY VARCHAR2
1752 ,x_fiscal_year IN OUT NOCOPY NUMBER
1753 ,x_agency_identifier IN OUT NOCOPY VARCHAR2
1754 ,x_agency_identifier_hidden IN OUT NOCOPY VARCHAR2
1755 ,x_allowed_range IN OUT NOCOPY VARCHAR2
1756 ,x_allowed_range_hidden IN OUT NOCOPY VARCHAR2
1757 ,x_min_value IN OUT NOCOPY VARCHAR2
1758 ,x_max_value IN OUT NOCOPY VARCHAR2
1759 ,x_serial_number IN OUT NOCOPY VARCHAR2
1760 ,x_delimiter IN OUT NOCOPY VARCHAR2
1761 ,x_document_number IN OUT NOCOPY VARCHAR2
1762 ,x_return_status OUT NOCOPY VARCHAR2
1763 ,x_errorcode OUT NOCOPY VARCHAR2
1764 ,x_msg_count OUT NOCOPY VARCHAR2
1765 ,x_msg_data OUT NOCOPY VARCHAR2
1766 )
1767 IS
1768
1769
1770 l_query VARCHAR2(4000);
1771 l_col_name VARCHAR2(20);
1772 l_attrb_grp_id NUMBER;
1773
1774 l_attr_grp_ids Dbms_Sql.number_table;
1775 l_where_cls VARCHAR2(100):='';
1776
1777 CURSOR C IS
1778 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
1779 from PO_UDA_AG_TEMPLATE_USAGES puatu,
1780 po_uda_ag_templates puat
1781 WHERE puat.ENTITY_CODE='PO_HEADER_EXT_ATTRS'
1782 AND puat.document_level='HEADER'
1783 AND puat.template_id = puatu.template_id
1784 AND puatu.attribute_category='DOCUMENT_NUMBERING'
1785 AND puatu.attribute1='Base Document'
1786 AND puatu.attribute2='NA';
1787
1788 min_code NUMBER;
1789 max_code NUMBER;
1790 l_lookup_code NUMBER;
1791
1792 BEGIN
1793
1794 IF x_agency_identifier_hidden <> x_agency_identifier OR
1795 x_allowed_range_hidden <> x_allowed_range THEN
1796
1797 x_agency_identifier_hidden := x_agency_identifier;
1798 x_allowed_range_hidden := x_allowed_range;
1799
1800 x_min_value := SubStr(x_allowed_range,1,4);
1801 x_max_value := SubStr(x_allowed_range,6);
1802
1803 OPEN C;
1804 FETCH C BULK COLLECT INTO l_attr_grp_ids;
1805 CLOSE C;
1806
1807 FOR i IN 1..l_attr_grp_ids.Count LOOP
1808 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
1809 END LOOP;
1810
1811 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
1812
1813 -- Bug 16468041 Rewriting the below query due to performance issue.
1814 /*l_query := ' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
1815 ' ( select min(to_number(lookup_code)) from ( '||
1816 ' SELECT lookup_code FROM po_lookup_codes '||
1817 ' WHERE to_number(lookup_code) BETWEEN '||
1818 ' (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
1819 x_min_value||'''and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
1820 ' and (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
1821 x_max_value||''' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
1822 ' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'' '||
1823 ' minus '||
1824 ' SELECT distinct plc.lookup_code FROM '||
1825 ' PO_HEADERS_ALL_EXT_B prheb, po_lookup_codes plc ' ||
1826 ' WHERE plc.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
1827 ' and prheb.C_EXT_ATTR9 = plc.displayed_field ' ||
1828 ' and prheb.C_EXT_ATTR1 = '''||x_prefix ||''''||
1829 ' and prheb.C_EXT_ATTR2 = '||x_fiscal_year ||
1830 ' and prheb.C_EXT_ATTR3 = '''||x_agency_identifier ||''''||
1831 ' and prheb.attr_group_id in ('|| l_where_cls ||')))' ||
1832 ' AND lookup_type = ''PO_CLM_SERIAL_NUM_CONSTANTS'' ';*/
1833
1834 BEGIN
1835 SELECT to_number(lookup_code)
1836 INTO min_code
1837 FROM fnd_lookup_values
1838 WHERE meaning = x_min_value
1839 AND language = userenv ('LANG')
1840 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
1841
1842 SELECT to_number(lookup_code)
1843 INTO max_code
1844 FROM fnd_lookup_values
1845 WHERE meaning = x_max_value
1846 AND language = userenv ('LANG')
1847 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
1848
1849 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
1850 ' FROM PO_HEADERS_ALL_EXT_B prheb, fnd_lookup_values flv ' ||
1851 ' WHERE prheb.C_EXT_ATTR1 = '''||x_prefix ||''''||
1852 ' AND prheb.C_EXT_ATTR2 = '||x_fiscal_year ||
1853 ' AND prheb.C_EXT_ATTR3 = '''||x_agency_identifier ||''''||
1854 ' AND flv.meaning = prheb.C_EXT_ATTR9 ' ||
1855 ' AND flv.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
1856 ' AND flv.language = userenv (''LANG'')' ||
1857 ' AND to_number(flv.lookup_code) between ' || min_code || ' AND ' || max_code ||
1858 ' AND prheb.attr_group_id in (' || l_where_cls || ')';
1859
1860 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
1861
1862 SELECT meaning
1863 INTO x_serial_number
1864 FROM fnd_lookup_values
1865 WHERE lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS'
1866 AND language = userenv ('LANG')
1867 AND to_number(lookup_code) = Nvl(l_lookup_code, min_code) --<Bug 16699792>
1868 AND to_number(lookup_code) <= max_code; --<Bug 16699792>
1869 --EXECUTE IMMEDIATE l_query INTO x_serial_number ;
1870 END;
1871
1872 x_document_number := x_prefix || x_delimiter
1873 || x_fiscal_year || x_delimiter
1874 || x_agency_identifier || x_delimiter
1875 || x_serial_number ;
1876
1877
1878 END IF;
1879
1880 x_return_status := FND_API.G_RET_STS_SUCCESS;
1881 x_errorcode := '0';
1882 x_msg_count := '1';
1883 x_msg_data := 'SUCCESS';
1884
1885 END order_numbering_XLE;
1886
1887
1888 PROCEDURE order_numbering_XSC
1889 (x_order_header_id IN NUMBER
1890 ,x_prefix IN OUT NOCOPY VARCHAR2
1891 ,x_fiscal_year IN OUT NOCOPY NUMBER
1892 ,x_agency_identifier IN OUT NOCOPY VARCHAR2
1893 ,x_serial_number IN OUT NOCOPY VARCHAR2
1894 ,x_delimiter IN OUT NOCOPY VARCHAR2
1895 ,x_document_number IN OUT NOCOPY VARCHAR2
1896 ,x_return_status OUT NOCOPY VARCHAR2
1897 ,x_errorcode OUT NOCOPY VARCHAR2
1898 ,x_msg_count OUT NOCOPY VARCHAR2
1899 ,x_msg_data OUT NOCOPY VARCHAR2
1900 )
1901 IS
1902
1903 BEGIN
1904
1905 order_numbering_XV(x_order_header_id
1906 ,x_prefix
1907 ,x_fiscal_year
1908 ,x_agency_identifier
1909 ,x_serial_number
1910 ,x_document_number
1911 ,x_return_status
1912 ,x_errorcode
1913 ,x_msg_count
1914 ,x_msg_data
1915 );
1916
1917 END order_numbering_XSC;
1918
1919
1920
1921 PROCEDURE order_mod_numbering_XD
1922 (p_po_header_id IN NUMBER
1923 ,p_draft_id IN NUMBER
1924 ,x_base_doc_number IN OUT NOCOPY varchar2
1925 ,x_serial_number IN OUT NOCOPY VARCHAR2
1926 ,x_delimiter IN OUT NOCOPY VARCHAR2
1927 ,x_document_number IN OUT NOCOPY VARCHAR2
1928 ,x_return_status OUT NOCOPY VARCHAR2
1929 ,x_errorcode OUT NOCOPY VARCHAR2
1930 ,x_msg_count OUT NOCOPY VARCHAR2
1931 ,x_msg_data OUT NOCOPY VARCHAR2
1932 )
1933 IS
1934
1935 l_query VARCHAR2(4000);
1936 l_attr_grp_ids Dbms_Sql.number_table;
1937 l_where_cls VARCHAR2(100):='';
1938 l_num NUMBER;
1939
1940 CURSOR C IS
1941 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
1942 from PO_UDA_AG_TEMPLATE_USAGES puatu,
1943 po_uda_ag_templates puat
1944 WHERE puat.ENTITY_CODE='PO_HEADER_EXT_ATTRS'
1945 AND puat.document_level='HEADER'
1946 AND puat.template_id = puatu.template_id
1947 AND puatu.attribute_category='DOCUMENT_NUMBERING'
1948 AND puatu.attribute1='Modification'
1949 AND puatu.attribute2='NA';
1950
1951 BEGIN
1952
1953 IF x_document_number IS NULL THEN
1954
1955 SELECT CLM_DOCUMENT_NUMBER INTO x_base_doc_number
1956 FROM po_headers_all
1957 WHERE po_header_id = p_po_header_id;
1958
1959 OPEN C;
1960 FETCH C BULK COLLECT INTO l_attr_grp_ids;
1961 CLOSE C;
1962
1963 FOR i IN 1..l_attr_grp_ids.Count LOOP
1964 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
1965 END LOOP;
1966
1967 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
1968
1969 l_query := ' SELECT Max(To_Number(C_EXT_ATTR2)) '||
1970 ' FROM PO_HEADERS_ALL_EXT_B '||
1971 ' where C_EXT_ATTR1 ='''||x_base_doc_number||''' ' ||
1972 ' and ATTR_GROUP_ID in ('||l_where_cls||') '||
1973 ' and C_EXT_ATTR7 not like ''%X%'' ';
1974
1975 BEGIN
1976 EXECUTE IMMEDIATE l_query INTO l_num;
1977 IF l_num IS NULL THEN
1978 l_num :=0;
1979 END IF;
1980
1981 EXCEPTION
1982 WHEN No_Data_Found THEN
1983 l_num :=0;
1984 END;
1985
1986 x_serial_number := LPad(l_num+1,4,'0');
1987 x_delimiter := '-';
1988 x_document_number := x_base_doc_number ||x_delimiter|| x_serial_number;
1989 END IF;
1990
1991 x_return_status := FND_API.G_RET_STS_SUCCESS;
1992 x_errorcode := '0';
1993 x_msg_count := '1';
1994 x_msg_data := 'SUCCESS';
1995
1996
1997 END order_mod_numbering_XD;
1998
1999
2000
2001 PROCEDURE order_mod_numbering_XV
2002 (p_po_header_id IN NUMBER
2003 ,p_draft_id IN NUMBER
2004 ,x_base_doc_number IN OUT NOCOPY varchar2
2005 ,x_serial_number IN OUT NOCOPY VARCHAR2
2006 ,x_delimiter IN OUT NOCOPY VARCHAR2
2007 ,x_document_number IN OUT NOCOPY VARCHAR2
2008 ,x_return_status OUT NOCOPY VARCHAR2
2009 ,x_errorcode OUT NOCOPY VARCHAR2
2010 ,x_msg_count OUT NOCOPY VARCHAR2
2011 ,x_msg_data OUT NOCOPY VARCHAR2
2012 )
2013 IS
2014
2015 l_query VARCHAR2(4000);
2016 l_cnt NUMBER;
2017
2018 BEGIN
2019
2020 IF x_base_doc_number IS NULL OR x_base_doc_number='' OR
2021 x_serial_number IS NULL OR x_serial_number='' THEN
2022
2023 x_return_status := FND_API.G_RET_STS_ERROR;
2024 x_errorcode := '-1';
2025 x_msg_count := '1';
2026 x_msg_data := 'All the fields are compulsory, please enter the value for all the Attributes';
2027 fnd_message.set_name('PO', 'PO_DOC_NUM_ALL_FIELDS_COMPUL');
2028 fnd_msg_pub.ADD;
2029 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2030 END IF;
2031
2032 select count(*) INTO l_cnt
2033 from PO_HEADERS_ALL_EXT_B
2034 WHERE C_EXT_ATTR40 = x_document_number
2035 AND draft_id <> p_draft_id
2036 AND po_header_id <> p_po_header_id;
2037
2038 IF l_cnt > 0 THEN
2039 x_return_status := FND_API.G_RET_STS_ERROR;
2040 x_errorcode := '-1';
2041 x_msg_count := '1';
2042 x_msg_data := 'Document Number is not Unique';
2043 fnd_message.set_name('PO', 'PO_DOC_NUM_NOT_UNIQUE');
2044 fnd_msg_pub.ADD;
2045 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2046 ELSE
2047 x_return_status := FND_API.G_RET_STS_SUCCESS;
2048 x_errorcode := '0';
2049 x_msg_count := '1';
2050 x_msg_data := 'SUCCESS';
2051 END IF;
2052
2053 END order_mod_numbering_XV;
2054
2055
2056
2057 PROCEDURE sol_amend_numbering_XD
2058 (p_auction_header_id IN NUMBER
2059 ,p_base_auction_header_id IN NUMBER
2060 ,x_base_doc_number IN OUT NOCOPY varchar2
2061 ,x_serial_number IN OUT NOCOPY VARCHAR2
2062 ,x_delimiter IN OUT NOCOPY VARCHAR2
2063 ,x_document_number IN OUT NOCOPY VARCHAR2
2064 ,x_return_status OUT NOCOPY VARCHAR2
2065 ,x_errorcode OUT NOCOPY VARCHAR2
2066 ,x_msg_count OUT NOCOPY VARCHAR2
2067 ,x_msg_data OUT NOCOPY VARCHAR2
2068 )
2069 IS
2070
2071 l_query VARCHAR2(4000);
2072 l_attr_grp_ids Dbms_Sql.number_table;
2073 l_where_cls VARCHAR2(100):='';
2074 l_num NUMBER;
2075
2076 CURSOR C IS
2077 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
2078 from PO_UDA_AG_TEMPLATE_USAGES puatu,
2079 po_uda_ag_templates puat
2080 WHERE puat.ENTITY_CODE='PON_AUC_HDRS_EXT_ATTRS'
2081 AND puat.document_level='HEADER'
2082 AND puat.template_id = puatu.template_id
2083 AND puatu.attribute_category='DOCUMENT_NUMBERING'
2084 AND puatu.attribute1 ='Modification'
2085 AND puatu.attribute2='NA';
2086
2087 BEGIN
2088
2089 IF x_document_number IS NULL THEN
2090
2091 SELECT document_number INTO x_base_doc_number
2092 FROM pon_auction_headers_all
2093 WHERE auction_header_id = p_base_auction_header_id;
2094
2095
2096 OPEN C;
2097 FETCH C BULK COLLECT INTO l_attr_grp_ids;
2098 CLOSE C;
2099
2100 FOR i IN 1..l_attr_grp_ids.Count LOOP
2101 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
2102 END LOOP;
2103
2104 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
2105
2106 l_query := ' SELECT Max(To_Number(C_EXT_ATTR2)) '||
2107 ' FROM PON_AUCTION_HEADERS_EXT_B '||
2108 ' where C_EXT_ATTR1 ='''||x_base_doc_number||''' ' ||
2109 ' and ATTR_GROUP_ID in ('||l_where_cls||')';
2110
2111 BEGIN
2112 EXECUTE IMMEDIATE l_query INTO l_num;
2113 IF l_num IS NULL THEN
2114 l_num :=0;
2115 END IF;
2116
2117 EXCEPTION
2118 WHEN No_Data_Found THEN
2119 l_num :=0;
2120 END;
2121
2122 x_serial_number := LPad(l_num+1,4,'0');
2123 x_delimiter := '-';
2124 x_document_number := x_base_doc_number ||x_delimiter|| x_serial_number;
2125 END IF;
2126
2127 x_return_status := FND_API.G_RET_STS_SUCCESS;
2128 x_errorcode := '0';
2129 x_msg_count := '1';
2130 x_msg_data := 'SUCCESS';
2131
2132
2133 END sol_amend_numbering_XD;
2134
2135
2136 PROCEDURE sol_amend_numbering_XV
2137 (p_auction_header_id IN NUMBER
2138 ,x_base_doc_number IN OUT NOCOPY varchar2
2139 ,x_serial_number IN OUT NOCOPY VARCHAR2
2140 ,x_delimiter IN OUT NOCOPY VARCHAR2
2141 ,x_document_number IN OUT NOCOPY VARCHAR2
2142 ,x_return_status OUT NOCOPY VARCHAR2
2143 ,x_errorcode OUT NOCOPY VARCHAR2
2144 ,x_msg_count OUT NOCOPY VARCHAR2
2145 ,x_msg_data OUT NOCOPY VARCHAR2
2146 )
2147 IS
2148
2149 l_query VARCHAR2(4000);
2150 l_cnt NUMBER;
2151
2152 BEGIN
2153
2154 IF x_base_doc_number IS NULL OR x_base_doc_number='' OR
2155 x_serial_number IS NULL OR x_serial_number='' THEN
2156
2157 x_return_status := FND_API.G_RET_STS_ERROR;
2158 x_errorcode := '-1';
2159 x_msg_count := '1';
2160 x_msg_data := 'All the fields are compulsory, please enter the value for all the Attributes';
2161 fnd_message.set_name('PO', 'PO_DOC_NUM_ALL_FIELDS_COMPUL');
2162 fnd_msg_pub.ADD;
2163 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2164 END IF;
2165
2166 select count(*) INTO l_cnt
2167 from PON_AUCTION_HEADERS_EXT_B
2168 WHERE C_EXT_ATTR40 = x_document_number
2169 AND AUCTION_HEADER_ID <> p_auction_header_id;
2170
2171 IF l_cnt > 0 THEN
2172 x_return_status := FND_API.G_RET_STS_ERROR;
2173 x_errorcode := '-1';
2174 x_msg_count := '1';
2175 x_msg_data := 'Document Number is not Unique';
2176 fnd_message.set_name('PO', 'PO_DOC_NUM_NOT_UNIQUE');
2177 fnd_msg_pub.ADD;
2178 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2179 ELSE
2180 x_return_status := FND_API.G_RET_STS_SUCCESS;
2181 x_errorcode := '0';
2182 x_msg_count := '1';
2183 x_msg_data := 'SUCCESS';
2184 END IF;
2185
2186 END sol_amend_numbering_XV;
2187
2188
2189
2190 PROCEDURE dod_sol_numbering_XD
2191 (x_dodaac IN OUT NOCOPY VARCHAR2
2192 ,x_fiscal_year IN OUT NOCOPY VARCHAR2
2193 ,x_instrument_type IN OUT NOCOPY VARCHAR2
2194 ,x_allowed_range IN OUT NOCOPY VARCHAR2
2195 ,x_min_value IN OUT NOCOPY VARCHAR2
2196 ,x_max_value IN OUT NOCOPY VARCHAR2
2197 ,x_serial_number IN OUT NOCOPY VARCHAR2
2198 ,x_delimiter IN OUT NOCOPY VARCHAR2
2199 ,x_document_number IN OUT NOCOPY VARCHAR2
2200 ,x_dodaac_hidden IN OUT NOCOPY VARCHAR2
2201 ,x_instrument_type_hidden IN OUT NOCOPY VARCHAR2
2202 ,x_allowed_range_hidden IN OUT NOCOPY VARCHAR2
2203 ,x_return_status OUT NOCOPY VARCHAR2
2204 ,x_errorcode OUT NOCOPY VARCHAR2
2205 ,x_msg_count OUT NOCOPY VARCHAR2
2206 ,x_msg_data OUT NOCOPY VARCHAR2
2207 )
2208
2209 IS
2210
2211
2212 l_query VARCHAR2(4000);
2213 l_col_name VARCHAR2(20);
2214 l_attrb_grp_id NUMBER;
2215
2216 l_attr_grp_ids Dbms_Sql.number_table;
2217 l_where_cls VARCHAR2(100):='';
2218
2219 CURSOR C IS
2220 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
2221 from PO_UDA_AG_TEMPLATE_USAGES puatu,
2222 po_uda_ag_templates puat
2223 WHERE puat.ENTITY_CODE='PON_AUC_HDRS_EXT_ATTRS'
2224 AND puat.document_level='HEADER'
2225 AND puat.template_id = puatu.template_id
2226 AND puatu.attribute_category='DOCUMENT_NUMBERING'
2227 AND puatu.attribute1 ='Base Document'
2228 AND puatu.attribute2='NA';
2229
2230 CURSOR RANGES IS
2231 SELECT lookup_code
2232 FROM (
2233 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_ALLOWED_RANGE'
2234 and enabled_flag = 'Y'
2235 and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code);
2236
2237 l_ranges_array Dbms_Sql.varchar2_table;
2238 l_serial_num_fetched BOOLEAN := FALSE;
2239
2240 min_code NUMBER;
2241 max_code NUMBER;
2242 l_lookup_code NUMBER;
2243 BEGIN
2244
2245 x_return_status := FND_API.G_RET_STS_SUCCESS;
2246 x_errorcode := '0';
2247 x_msg_count := '1';
2248 x_msg_data := 'SUCCESS';
2249
2250 -- If document number is already generated, just return and don't redefault
2251 IF(x_dodaac IS NULL AND x_fiscal_year IS NULL AND
2252 x_instrument_type IS NULL AND x_allowed_range IS NULL AND
2253 x_serial_number IS NULL AND x_document_number is null) THEN
2254
2255 BEGIN
2256 SELECT PEI_INFORMATION2
2257 INTO x_dodaac
2258 FROM per_people_extra_info pei, fnd_user fu
2259 WHERE pei.information_type = 'CLM_CONTACT_TITLE'
2260 AND pei.person_id= fu.employee_id
2261 AND fu.user_id = fnd_global.user_id;
2262 EXCEPTION
2263 WHEN No_Data_Found THEN
2264 x_dodaac:='';
2265 fnd_message.set_name('PO', 'PO_CLM_DEFAULT_DODAAC_ND');
2266 fnd_msg_pub.ADD;
2267 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2268 END;
2269
2270 x_dodaac_hidden := x_dodaac;
2271
2272 SELECT To_Char(SYSDATE,'YY')
2273 INTO x_fiscal_year
2274 FROM dual;
2275
2276
2277 BEGIN
2278 SELECT lookup_code
2279 INTO x_instrument_type
2280 FROM (
2281 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_INSTRUMENT_TYPE'
2282 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
2283 WHERE ROWNUM=1;
2284 EXCEPTION
2285 WHEN No_Data_Found THEN
2286 x_return_status := FND_API.G_RET_STS_ERROR;
2287 x_errorcode := '-1';
2288 x_msg_count := '1';
2289 x_msg_data := 'Lookup PO_CLM_INSTRUMENT_TYPE not found';
2290 fnd_message.set_name('PO', 'PO_CLM_INSTRUMENT_TYPE_NVD');
2291 fnd_msg_pub.ADD;
2292 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2293 RETURN;
2294 END;
2295
2296 x_instrument_type_hidden := x_instrument_type;
2297
2298 BEGIN
2299 SELECT lookup_code
2300 INTO x_allowed_range
2301 FROM (
2302 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_ALLOWED_RANGE'
2303 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
2304 WHERE ROWNUM=1;
2305 EXCEPTION
2306 WHEN No_Data_Found THEN
2307 x_return_status := FND_API.G_RET_STS_ERROR;
2308 x_errorcode := '-1';
2309 x_msg_count := '1';
2310 x_msg_data := 'Lookup PO_CLM_ALLOWED_RANGE not found';
2311 fnd_message.set_name('PO', 'PO_CLM_ALLOWED_RANGE_NVD');
2312 fnd_msg_pub.ADD;
2313 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2314 RETURN;
2315 END;
2316
2317 x_allowed_range_hidden := x_allowed_range;
2318 x_delimiter := '-';
2319
2320 x_serial_number := 'XXXX';
2321
2322 x_document_number := x_dodaac || x_delimiter
2323 || x_fiscal_year || x_delimiter
2324 || x_instrument_type || x_delimiter
2325 || x_serial_number ;
2326
2327 ELSIF x_serial_number IS NULL OR x_serial_number = 'XXXX' THEN
2328
2329 OPEN C;
2330 FETCH C BULK COLLECT INTO l_attr_grp_ids;
2331 CLOSE C;
2332
2333 FOR i IN 1..l_attr_grp_ids.Count LOOP
2334 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
2335 END LOOP;
2336
2337 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
2338
2339 --First we need to default the value from the selected range
2340 --if its exausted then default it from the other ranges available.
2341 x_min_value := SubStr(x_allowed_range,1,4);
2342 x_max_value := SubStr(x_allowed_range,6);
2343
2344
2345 -- Bug 16468041 Rewriting the below query due to performance issue.
2346 /*l_query := ' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
2347 ' ( select min(to_number(lookup_code)) from ( '||
2348 ' SELECT lookup_code FROM po_lookup_codes '||
2349 ' WHERE to_number(lookup_code) BETWEEN '||
2350 ' (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
2351 x_min_value||'''and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
2352 ' and (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
2353 x_max_value||''' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
2354 ' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'' '||
2355 ' minus '||
2356 ' SELECT distinct plc.lookup_code FROM '||
2357 ' PON_AUCTION_HEADERS_EXT_B prheb, po_lookup_codes plc ' ||
2358 ' WHERE plc.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
2359 ' and prheb.C_EXT_ATTR7 = plc.displayed_field ' ||
2360 ' and prheb.C_EXT_ATTR1 = '''||x_dodaac ||''''||
2361 ' and prheb.C_EXT_ATTR2 = '''||x_fiscal_year ||''''||
2362 ' and prheb.C_EXT_ATTR3 = '''||x_instrument_type ||''''||
2363 ' and prheb.attr_group_id in ('|| l_where_cls ||')))' ||
2364 ' AND lookup_type = ''PO_CLM_SERIAL_NUM_CONSTANTS'' ';*/
2365
2366 BEGIN
2367 SELECT to_number(lookup_code)
2368 INTO min_code
2369 FROM fnd_lookup_values
2370 WHERE meaning = x_min_value
2371 AND language = userenv ('LANG')
2372 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
2373
2374 SELECT to_number(lookup_code)
2375 INTO max_code
2376 FROM fnd_lookup_values
2377 WHERE meaning = x_max_value
2378 AND language = userenv ('LANG')
2379 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
2380
2381 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
2382 ' FROM PON_AUCTION_HEADERS_EXT_B prheb, fnd_lookup_values flv ' ||
2383 ' WHERE prheb.C_EXT_ATTR1 = ''' || x_dodaac || '''' ||
2384 ' AND prheb.C_EXT_ATTR2 = ''' || x_fiscal_year || '''' ||
2385 ' AND prheb.C_EXT_ATTR3 = ''' || x_instrument_type || '''' ||
2386 ' AND flv.meaning = prheb.C_EXT_ATTR7 ' ||
2387 ' AND flv.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
2388 ' AND flv.language = userenv (''LANG'')' ||
2389 ' AND to_number(flv.lookup_code) between ' || min_code || ' AND ' || max_code ||
2390 ' AND prheb.attr_group_id in (' || l_where_cls || ')';
2391
2392 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
2393
2394 SELECT meaning
2395 INTO x_serial_number
2396 FROM fnd_lookup_values
2397 WHERE lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS'
2398 AND language = userenv ('LANG')
2399 AND to_number(lookup_code) = Nvl(l_lookup_code, min_code) --<Bug 16699792>
2400 AND to_number(lookup_code) <= max_code; --<Bug 16699792>
2401
2402 --EXECUTE IMMEDIATE l_query INTO x_serial_number ;
2403 l_serial_num_fetched := TRUE;
2404 EXCEPTION
2405 WHEN No_Data_Found THEN
2406 l_serial_num_fetched := FALSE;
2407 WHEN OTHERS THEN
2408 x_return_status := FND_API.G_RET_STS_ERROR;
2409 x_errorcode := '-1';
2410 x_msg_count := '1';
2411 x_msg_data := 'Unable to generate serial number';
2412 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
2413 fnd_msg_pub.ADD;
2414 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2415 END;
2416
2417 IF l_serial_num_fetched = FALSE THEN
2418
2419 OPEN RANGES;
2420 FETCH RANGES BULK COLLECT INTO l_ranges_array;
2421 CLOSE RANGES;
2422
2423 FOR i IN 1..l_ranges_array.Count LOOP
2424
2425 IF l_ranges_array(i) <> x_allowed_range THEN
2426
2427 x_min_value := SubStr(l_ranges_array(i),1,4);
2428 x_max_value := SubStr(l_ranges_array(i),6);
2429
2430 -- Bug 16468041 Rewriting the below query due to performance issue.
2431 /*l_query := ' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
2432 ' ( select min(to_number(lookup_code)) from ( '||
2433 ' SELECT lookup_code FROM po_lookup_codes '||
2434 ' WHERE to_number(lookup_code) BETWEEN '||
2435 ' (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
2436 x_min_value||'''and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
2437 ' and (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
2438 x_max_value||''' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
2439 ' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'' '||
2440 ' minus '||
2441 ' SELECT distinct plc.lookup_code FROM '||
2442 ' PON_AUCTION_HEADERS_EXT_B prheb, po_lookup_codes plc ' ||
2443 ' WHERE plc.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
2444 ' and prheb.C_EXT_ATTR7 = plc.displayed_field ' ||
2445 ' and prheb.C_EXT_ATTR1 = '''||x_dodaac ||''''||
2446 ' and prheb.C_EXT_ATTR2 = '''||x_fiscal_year ||''''||
2447 ' and prheb.C_EXT_ATTR3 = '''||x_instrument_type ||''''||
2448 ' and prheb.attr_group_id in ('|| l_where_cls ||')))' ||
2449 ' AND lookup_type = ''PO_CLM_SERIAL_NUM_CONSTANTS'' ';*/
2450
2451 BEGIN
2452 SELECT to_number(lookup_code)
2453 INTO min_code
2454 FROM fnd_lookup_values
2455 WHERE meaning = x_min_value
2456 AND language = userenv ('LANG')
2457 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
2458
2459 SELECT to_number(lookup_code)
2460 INTO max_code
2461 FROM fnd_lookup_values
2462 WHERE meaning = x_max_value
2463 AND language = userenv ('LANG')
2464 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
2465
2466 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
2467 ' FROM PON_AUCTION_HEADERS_EXT_B prheb, fnd_lookup_values flv ' ||
2468 ' WHERE prheb.C_EXT_ATTR1 = ''' || x_dodaac || '''' ||
2469 ' AND prheb.C_EXT_ATTR2 = ''' || x_fiscal_year || '''' ||
2470 ' AND prheb.C_EXT_ATTR3 = ''' || x_instrument_type || '''' ||
2471 ' AND flv.meaning = prheb.C_EXT_ATTR7 ' ||
2472 ' AND flv.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
2473 ' AND flv.language = userenv (''LANG'')' ||
2474 ' AND to_number(flv.lookup_code) between ' || min_code || ' AND ' || max_code ||
2475 ' AND prheb.attr_group_id in (' || l_where_cls || ')';
2476
2477 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
2478
2479 SELECT meaning
2480 INTO x_serial_number
2481 FROM fnd_lookup_values
2482 WHERE lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS'
2483 AND language = userenv ('LANG')
2484 AND to_number(lookup_code) = Nvl(l_lookup_code, min_code) --<Bug 16699792>
2485 AND to_number(lookup_code) <= max_code; --<Bug 16699792>
2486 --EXECUTE IMMEDIATE l_query INTO x_serial_number ;
2487 l_serial_num_fetched := TRUE;
2488 x_allowed_range_hidden := l_ranges_array(i);
2489 x_allowed_range := l_ranges_array(i);
2490 EXIT; --exit the loop as number is fetched
2491 EXCEPTION
2492 WHEN No_Data_Found THEN
2493 l_serial_num_fetched := FALSE;
2494 WHEN OTHERS THEN
2495 x_return_status := FND_API.G_RET_STS_ERROR;
2496 x_errorcode := '-1';
2497 x_msg_count := '1';
2498 x_msg_data := 'Unable to generate serial number';
2499 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
2500 fnd_msg_pub.ADD;
2501 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2502 END;
2503
2504 END IF;
2505 END LOOP;
2506
2507 END IF;--if l_serial_num_fetched = false
2508
2509 IF l_serial_num_fetched = TRUE THEN
2510 x_document_number := x_dodaac || x_delimiter
2511 || x_fiscal_year || x_delimiter
2512 || x_instrument_type || x_delimiter
2513 || x_serial_number ;
2514 ELSE
2515 x_return_status := FND_API.G_RET_STS_ERROR;
2516 x_errorcode := '-1';
2517 x_msg_count := '1';
2518 x_msg_data := 'Unable to generate serial number';
2519 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
2520 fnd_msg_pub.ADD;
2521 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2522 END IF;
2523 END IF;
2524
2525 END dod_sol_numbering_XD;
2526
2527
2528
2529 PROCEDURE dod_sol_numbering_XDN
2530 (x_auction_header_id IN NUMBER
2531 ,x_dodaac IN OUT NOCOPY VARCHAR2
2532 ,x_fiscal_year IN OUT NOCOPY VARCHAR2
2533 ,x_instrument_type IN OUT NOCOPY VARCHAR2
2534 ,x_serial_number IN OUT NOCOPY VARCHAR2
2535 ,x_delimiter IN OUT NOCOPY VARCHAR2
2536 ,x_document_number IN OUT NOCOPY VARCHAR2
2537 ,x_return_status OUT NOCOPY VARCHAR2
2538 ,x_errorcode OUT NOCOPY VARCHAR2
2539 ,x_msg_count OUT NOCOPY VARCHAR2
2540 ,x_msg_data OUT NOCOPY VARCHAR2
2541 )
2542 IS
2543
2544 BEGIN
2545 x_serial_number := Upper(x_serial_number);
2546 x_document_number := x_dodaac || x_delimiter
2547 || x_fiscal_year || x_delimiter
2548 || x_instrument_type || x_delimiter
2549 || x_serial_number;
2550
2551
2552 dod_sol_numbering_XV(x_auction_header_id
2553 ,x_dodaac
2554 ,x_fiscal_year
2555 ,x_instrument_type
2556 ,x_serial_number
2557 ,x_document_number
2558 ,x_return_status
2559 ,x_errorcode
2560 ,x_msg_count
2561 ,x_msg_data
2562 );
2563
2564 END dod_sol_numbering_XDN;
2565
2566
2567
2568 PROCEDURE dod_sol_numbering_XV
2569 (x_auction_header_id IN NUMBER
2570 ,x_dodaac IN VARCHAR2
2571 ,x_fiscal_year IN VARCHAR2
2572 ,x_instrument_type IN VARCHAR2
2573 ,x_serial_number IN VARCHAR2
2574 ,x_document_number IN VARCHAR2
2575 ,x_return_status OUT NOCOPY VARCHAR2
2576 ,x_errorcode OUT NOCOPY VARCHAR2
2577 ,x_msg_count OUT NOCOPY VARCHAR2
2578 ,x_msg_data OUT NOCOPY VARCHAR2
2579 )
2580 IS
2581
2582
2583 l_query VARCHAR2(4000);
2584 l_col_name VARCHAR2(20);
2585 l_attrb_grp_id NUMBER;
2586 l_cnt NUMBER;
2587
2588 BEGIN
2589 --First initialize the out variable to success
2590 x_return_status := FND_API.G_RET_STS_SUCCESS;
2591 x_errorcode := '0';
2592 x_msg_count := '1';
2593 x_msg_data := 'SUCCESS';
2594
2595 --If any of the value is null then we need to throw back the error
2596 IF x_dodaac IS NULL OR x_dodaac = '' OR
2597 x_fiscal_year IS NULL OR
2598 x_instrument_type IS NULL OR x_instrument_type = '' THEN
2599
2600 x_return_status := FND_API.G_RET_STS_ERROR;
2601 x_errorcode := '-1';
2602 x_msg_count := '1';
2603 x_msg_data := 'All the fields are compulsory, please enter the value for all the Attributes.';
2604 fnd_message.set_name('PO', 'PO_DOC_NUM_ALL_FIELDS_COMPUL');
2605 fnd_msg_pub.ADD;
2606 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2607
2608 END IF;
2609
2610 IF x_serial_number IS NOT NULL AND Upper(x_serial_number) <> 'XXXX' THEN
2611 IF 'N' = validate_serial_number(Upper(x_serial_number)) THEN
2612 x_return_status := FND_API.G_RET_STS_ERROR;
2613 x_errorcode := '-1';
2614 x_msg_count := '1';
2615 x_msg_data := 'Serial number is invalid.';
2616 fnd_message.set_name('PO', 'PO_CLM_SERIAL_NUM_INVALID');
2617 fnd_msg_pub.ADD;
2618 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2619 ELSE
2620
2621 IF(Length(x_document_number) > 240) THEN
2622 x_return_status := FND_API.G_RET_STS_ERROR;
2623 x_errorcode := '-1';
2624 x_msg_count := '1';
2625 x_msg_data := 'Document Number length exceeds the maximum allowed limit.';
2626 fnd_message.set_name('PO', 'PO_CLM_DOC_NUM_LENGTH_ERR');
2627 fnd_msg_pub.ADD;
2628 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2629 END IF;
2630
2631 select count(*) INTO l_cnt
2632 from PON_AUCTION_HEADERS_EXT_B
2633 WHERE C_EXT_ATTR40 = x_document_number
2634 AND auction_header_id <> x_auction_header_id;
2635
2636 IF l_cnt > 0 THEN
2637 x_return_status := FND_API.G_RET_STS_ERROR;
2638 x_errorcode := '-1';
2639 x_msg_count := '1';
2640 x_msg_data := 'Document Number is not Unique';
2641 fnd_message.set_name('PO', 'PO_DOC_NUM_NOT_UNIQUE');
2642 fnd_msg_pub.ADD;
2643 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2644 END IF;
2645 END IF;
2646 END IF;
2647
2648 END dod_sol_numbering_XV;
2649
2650
2651 PROCEDURE dod_sol_numbering_XLE
2652 (x_dodaac IN OUT NOCOPY VARCHAR2
2653 ,x_fiscal_year IN OUT NOCOPY VARCHAR2
2654 ,x_instrument_type IN OUT NOCOPY VARCHAR2
2655 ,x_allowed_range IN OUT NOCOPY VARCHAR2
2656 ,x_min_value IN OUT NOCOPY VARCHAR2
2657 ,x_max_value IN OUT NOCOPY VARCHAR2
2658 ,x_serial_number IN OUT NOCOPY VARCHAR2
2659 ,x_delimiter IN OUT NOCOPY VARCHAR2
2660 ,x_document_number IN OUT NOCOPY VARCHAR2
2661 ,x_dodaac_hidden IN OUT NOCOPY VARCHAR2
2662 ,x_instrument_type_hidden IN OUT NOCOPY VARCHAR2
2663 ,x_allowed_range_hidden IN OUT NOCOPY VARCHAR2
2664 ,x_return_status OUT NOCOPY VARCHAR2
2665 ,x_errorcode OUT NOCOPY VARCHAR2
2666 ,x_msg_count OUT NOCOPY VARCHAR2
2667 ,x_msg_data OUT NOCOPY VARCHAR2
2668 )
2669 IS
2670
2671 BEGIN
2672
2673 IF x_instrument_type_hidden <> x_instrument_type OR
2674 x_allowed_range_hidden <> x_allowed_range OR
2675 x_dodaac_hidden <> x_dodaac THEN
2676
2677 x_dodaac_hidden := x_dodaac;
2678 x_instrument_type_hidden := x_instrument_type;
2679 x_allowed_range_hidden := x_allowed_range;
2680
2681 x_min_value := SubStr(x_allowed_range,1,4);
2682 x_max_value := SubStr(x_allowed_range,6);
2683
2684 x_serial_number := 'XXXX';
2685
2686 x_document_number := x_dodaac || x_delimiter
2687 || x_fiscal_year || x_delimiter
2688 || x_instrument_type || x_delimiter
2689 || x_serial_number ;
2690
2691 END IF;
2692
2693 x_return_status := FND_API.G_RET_STS_SUCCESS;
2694 x_errorcode := '0';
2695 x_msg_count := '1';
2696 x_msg_data := 'SUCCESS';
2697
2698 END dod_sol_numbering_XLE;
2699
2700
2701 PROCEDURE dod_sol_numbering_XSC
2702 (x_auction_header_id IN NUMBER
2703 ,x_dodaac IN VARCHAR2
2704 ,x_fiscal_year IN VARCHAR2
2705 ,x_instrument_type IN VARCHAR2
2706 ,x_serial_number IN VARCHAR2
2707 ,x_document_number IN VARCHAR2
2708 ,x_return_status OUT NOCOPY VARCHAR2
2709 ,x_errorcode OUT NOCOPY VARCHAR2
2710 ,x_msg_count OUT NOCOPY VARCHAR2
2711 ,x_msg_data OUT NOCOPY VARCHAR2
2712 )
2713 IS
2714
2715 BEGIN
2716
2717 dod_sol_numbering_XV(x_auction_header_id
2718 ,x_dodaac
2719 ,x_fiscal_year
2720 ,x_instrument_type
2721 ,x_serial_number
2722 ,x_document_number
2723 ,x_return_status
2724 ,x_errorcode
2725 ,x_msg_count
2726 ,x_msg_data
2727 );
2728
2729 END dod_sol_numbering_XSC;
2730
2731
2732
2733 PROCEDURE dod_award_numbering_XD
2734 (x_dodaac IN OUT NOCOPY VARCHAR2
2735 ,x_fiscal_year IN OUT NOCOPY VARCHAR2
2736 ,x_instrument_type IN OUT NOCOPY VARCHAR2
2737 ,x_allowed_range IN OUT NOCOPY VARCHAR2
2738 ,x_min_value IN OUT NOCOPY VARCHAR2
2739 ,x_max_value IN OUT NOCOPY VARCHAR2
2740 ,x_serial_number IN OUT NOCOPY VARCHAR2
2741 ,x_delimiter IN OUT NOCOPY VARCHAR2
2742 ,x_document_number IN OUT NOCOPY VARCHAR2
2743 ,x_dodaac_hidden IN OUT NOCOPY VARCHAR2
2744 ,x_instrument_type_hidden IN OUT NOCOPY VARCHAR2
2745 ,x_allowed_range_hidden IN OUT NOCOPY VARCHAR2
2746 ,x_return_status OUT NOCOPY VARCHAR2
2747 ,x_errorcode OUT NOCOPY VARCHAR2
2748 ,x_msg_count OUT NOCOPY VARCHAR2
2749 ,x_msg_data OUT NOCOPY VARCHAR2
2750 )
2751
2752 IS
2753
2754
2755 l_query VARCHAR2(4000);
2756 l_col_name VARCHAR2(20);
2757 l_attrb_grp_id NUMBER;
2758
2759 l_attr_grp_ids Dbms_Sql.number_table;
2760 --l_where_cls VARCHAR2(100):='';
2761 l_cur_attr_group_id NUMBER ;
2762
2763 /*CURSOR C IS
2764 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
2765 from PO_UDA_AG_TEMPLATE_USAGES puatu,
2766 po_uda_ag_templates puat
2767 WHERE puat.ENTITY_CODE='PO_HEADER_EXT_ATTRS'
2768 AND puat.document_level='HEADER'
2769 AND puat.template_id = puatu.template_id
2770 AND puatu.attribute_category='DOCUMENT_NUMBERING'
2771 AND puatu.attribute1 ='Base Document'
2772 AND puatu.attribute2='NA'; */
2773
2774 CURSOR RANGES IS
2775 SELECT lookup_code
2776 FROM (
2777 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_ALLOWED_RANGE'
2778 and enabled_flag = 'Y'
2779 and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code);
2780
2781 l_ranges_array Dbms_Sql.varchar2_table;
2782 l_serial_num_fetched BOOLEAN := FALSE;
2783
2784 min_code NUMBER;
2785 max_code NUMBER;
2786 l_lookup_code NUMBER;
2787
2788 BEGIN
2789
2790 x_return_status := FND_API.G_RET_STS_SUCCESS;
2791 x_errorcode := '0';
2792 x_msg_count := '1';
2793 x_msg_data := 'SUCCESS';
2794
2795 --Get the current attribute group id .
2796
2797 SELECT attr_group_id
2798 INTO l_cur_attr_group_id
2799 FROM ego_attr_groups_v
2800 WHERE ATTR_GROUP_NAME = 'DOD_AWD'
2801 AND attr_group_type='PO_HEADER_EXT_ATTRS';
2802
2803 IF(x_dodaac IS NULL AND x_fiscal_year IS NULL AND
2804 x_instrument_type IS NULL AND x_allowed_range IS NULL AND
2805 x_serial_number IS NULL AND x_document_number is null) THEN
2806
2807 -- Do the defaulting for attributes and set the document number
2808 BEGIN
2809 SELECT PEI_INFORMATION2
2810 INTO x_dodaac
2811 FROM per_people_extra_info pei, fnd_user fu
2812 WHERE pei.information_type = 'CLM_CONTACT_TITLE'
2813 AND pei.person_id= fu.employee_id
2814 AND fu.user_id = fnd_global.user_id;
2815 EXCEPTION
2816 WHEN No_Data_Found THEN
2817 x_return_status := FND_API.G_RET_STS_ERROR;
2818 x_errorcode := '-1';
2819 x_msg_count := '1';
2820 x_msg_data := 'Users default DoDAAC not found';
2821 fnd_message.set_name('PO', 'PO_CLM_DEFAULT_DODAAC_ND');
2822 fnd_msg_pub.ADD;
2823 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2824 END;
2825
2826 x_dodaac_hidden := x_dodaac;
2827
2828 SELECT To_Char(SYSDATE,'YY')
2829 INTO x_fiscal_year
2830 FROM dual;
2831
2832
2833 BEGIN
2834 SELECT lookup_code
2835 INTO x_instrument_type
2836 FROM (
2837 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_INSTRUMENT_TYPE'
2838 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
2839 WHERE ROWNUM=1;
2840 EXCEPTION
2841 WHEN No_Data_Found THEN
2842 x_return_status := FND_API.G_RET_STS_ERROR;
2843 x_errorcode := '-1';
2844 x_msg_count := '1';
2845 x_msg_data := 'Lookup PO_CLM_INSTRUMENT_TYPE not found';
2846 fnd_message.set_name('PO', 'PO_CLM_INSTRUMENT_TYPE_NVD');
2847 fnd_msg_pub.ADD;
2848 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2849 RETURN;
2850 END;
2851
2852 x_instrument_type_hidden := x_instrument_type;
2853
2854 BEGIN
2855 SELECT lookup_code
2856 INTO x_allowed_range
2857 FROM (
2858 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_ALLOWED_RANGE'
2859 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
2860 WHERE ROWNUM=1;
2861 EXCEPTION
2862 WHEN No_Data_Found THEN
2863 x_return_status := FND_API.G_RET_STS_ERROR;
2864 x_errorcode := '-1';
2865 x_msg_count := '1';
2866 x_msg_data := 'Lookup PO_CLM_ALLOWED_RANGE not found';
2867 fnd_message.set_name('PO', 'PO_CLM_ALLOWED_RANGE_NVD');
2868 fnd_msg_pub.ADD;
2869 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2870 RETURN;
2871 END;
2872
2873 x_allowed_range_hidden := x_allowed_range;
2874 x_delimiter := '-';
2875
2876 --Serial number should be defaulted to XXXX
2877 x_serial_number :='XXXX';
2878
2879 x_document_number := x_dodaac || x_delimiter
2880 || x_fiscal_year || x_delimiter
2881 || x_instrument_type || x_delimiter
2882 || x_serial_number ;
2883
2884 ELSIF x_serial_number IS NULL OR x_serial_number = 'XXXX' THEN
2885
2886 /*OPEN C;
2887 FETCH C BULK COLLECT INTO l_attr_grp_ids;
2888 CLOSE C;
2889
2890 FOR i IN 1..l_attr_grp_ids.Count LOOP
2891 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
2892 END LOOP;
2893
2894 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1); */
2895
2896 x_min_value := SubStr(x_allowed_range,1,4);
2897 x_max_value := SubStr(x_allowed_range,6);
2898
2899 -- Bug 16468041 Rewriting the below query due to performance issue.
2900 /*l_query := ' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
2901 ' ( select min(to_number(lookup_code)) from ( '||
2902 ' SELECT lookup_code FROM po_lookup_codes '||
2903 ' WHERE to_number(lookup_code) BETWEEN '||
2904 ' (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
2905 x_min_value||'''and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
2906 ' and (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
2907 x_max_value||''' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
2908 ' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'' '||
2909 ' minus '||
2910 ' SELECT distinct plc.lookup_code FROM '||
2911 ' PO_HEADERS_ALL_EXT_B prheb, po_lookup_codes plc ' ||
2912 ' WHERE plc.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
2913 ' and ( prheb.C_EXT_ATTR7 = plc.displayed_field OR SubStr(prheb.C_EXT_ATTR40,-4) = plc.displayed_field)' ||
2914 ' and prheb.C_EXT_ATTR1 = '''||x_dodaac ||''''||
2915 ' and prheb.C_EXT_ATTR2 = '''||x_fiscal_year ||''''||
2916 ' and prheb.C_EXT_ATTR3 = '''||x_instrument_type ||''''||
2917 ' and prheb.attr_group_id = '|| l_cur_attr_group_id ||' ))' ||
2918 ' AND lookup_type = ''PO_CLM_SERIAL_NUM_CONSTANTS'' ';*/
2919
2920 BEGIN
2921 SELECT to_number(lookup_code)
2922 INTO min_code
2923 FROM fnd_lookup_values
2924 WHERE meaning = x_min_value
2925 AND language = userenv ('LANG')
2926 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
2927
2928 SELECT to_number(lookup_code)
2929 INTO max_code
2930 FROM fnd_lookup_values
2931 WHERE meaning = x_max_value
2932 AND language = userenv ('LANG')
2933 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
2934
2935 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
2936 ' FROM PO_HEADERS_ALL_EXT_B prheb, fnd_lookup_values flv ' ||
2937 ' WHERE prheb.C_EXT_ATTR1 = ''' || x_dodaac || '''' ||
2938 ' AND prheb.C_EXT_ATTR2 = ''' || x_fiscal_year || '''' ||
2939 ' AND prheb.C_EXT_ATTR3 = ''' || x_instrument_type || '''' ||
2940 ' AND flv.meaning = prheb.C_EXT_ATTR7 ' ||
2941 ' AND flv.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
2942 ' AND flv.language = userenv (''LANG'')' ||
2943 ' AND to_number(flv.lookup_code) between ' || min_code || ' AND ' || max_code ||
2944 ' AND prheb.attr_group_id in (' || l_cur_attr_group_id || ')';
2945
2946 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
2947
2948 SELECT meaning
2949 INTO x_serial_number
2950 FROM fnd_lookup_values
2951 WHERE lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS'
2952 AND language = userenv ('LANG')
2953 AND to_number(lookup_code) = Nvl(l_lookup_code, min_code) --<Bug 16699792>
2954 AND to_number(lookup_code) <= max_code; --<Bug 16699792>
2955
2956 -- Note : Changed the approach to take max no within the range and increment it by 1.
2957 -- Take the display value corresponding to this.
2958 -- But There is a loophole in this approach.
2959 -- If you delete a document, you will not be able to reuse the same serial number again.
2960 -- The next serial number generated would always be the max serial number that exists in the system + 1.
2961
2962 --EXECUTE IMMEDIATE l_query INTO x_serial_number ;
2963 l_serial_num_fetched := TRUE;
2964 EXCEPTION
2965 WHEN No_Data_Found THEN
2966 l_serial_num_fetched := FALSE;
2967 WHEN OTHERS THEN
2968 x_return_status := FND_API.G_RET_STS_ERROR;
2969 x_errorcode := '-1';
2970 x_msg_count := '1';
2971 x_msg_data := 'Unable to generate serial number';
2972 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
2973 fnd_msg_pub.ADD;
2974 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
2975 END;
2976
2977 IF l_serial_num_fetched = FALSE THEN
2978
2979 OPEN RANGES;
2980 FETCH RANGES BULK COLLECT INTO l_ranges_array;
2981 CLOSE RANGES;
2982
2983 FOR i IN 1..l_ranges_array.Count LOOP
2984
2985 IF l_ranges_array(i) <> x_allowed_range THEN
2986
2987 x_min_value := SubStr(l_ranges_array(i),1,4);
2988 x_max_value := SubStr(l_ranges_array(i),6);
2989
2990 -- Bug 16468041 Rewriting the below query due to performance issue.
2991 /*l_query := ' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
2992 ' ( select min(to_number(lookup_code)) from ( '||
2993 ' SELECT lookup_code FROM po_lookup_codes '||
2994 ' WHERE to_number(lookup_code) BETWEEN '||
2995 ' (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
2996 x_min_value||'''and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
2997 ' and (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
2998 x_max_value||''' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
2999 ' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'' '||
3000 ' minus '||
3001 ' SELECT distinct plc.lookup_code FROM '||
3002 ' PO_HEADERS_ALL_EXT_B prheb, po_lookup_codes plc ' ||
3003 ' WHERE plc.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
3004 ' and ( prheb.C_EXT_ATTR7 = plc.displayed_field OR SubStr(prheb.C_EXT_ATTR40,-4) = plc.displayed_field)' ||
3005 ' and prheb.C_EXT_ATTR1 = '''||x_dodaac ||''''||
3006 ' and prheb.C_EXT_ATTR2 = '''||x_fiscal_year ||''''||
3007 ' and prheb.C_EXT_ATTR3 = '''||x_instrument_type ||''''||
3008 ' and prheb.attr_group_id = '|| l_cur_attr_group_id ||' ))' ||
3009 ' AND lookup_type = ''PO_CLM_SERIAL_NUM_CONSTANTS'' ';*/
3010
3011 BEGIN
3012 SELECT to_number(lookup_code)
3013 INTO min_code
3014 FROM fnd_lookup_values
3015 WHERE meaning = x_min_value
3016 AND language = userenv ('LANG')
3017 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
3018
3019 SELECT to_number(lookup_code)
3020 INTO max_code
3021 FROM fnd_lookup_values
3022 WHERE meaning = x_max_value
3023 AND language = userenv ('LANG')
3024 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
3025
3026 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
3027 ' FROM PO_HEADERS_ALL_EXT_B prheb, fnd_lookup_values flv ' ||
3028 ' WHERE prheb.C_EXT_ATTR1 = ''' || x_dodaac || '''' ||
3029 ' AND prheb.C_EXT_ATTR2 = ''' || x_fiscal_year || '''' ||
3030 ' AND prheb.C_EXT_ATTR3 = ''' || x_instrument_type || '''' ||
3031 ' AND flv.meaning = prheb.C_EXT_ATTR7 ' ||
3032 ' AND flv.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
3033 ' AND flv.language = userenv (''LANG'')' ||
3034 ' AND to_number(flv.lookup_code) between ' || min_code || ' AND ' || max_code ||
3035 ' AND prheb.attr_group_id in (' || l_cur_attr_group_id || ')';
3036
3037 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
3038
3039 SELECT meaning
3040 INTO x_serial_number
3041 FROM fnd_lookup_values
3042 WHERE lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS'
3043 AND language = userenv ('LANG')
3044 AND to_number(lookup_code) = Nvl(l_lookup_code, min_code) --<Bug 16699792>
3045 AND to_number(lookup_code) <= max_code; --<Bug 16699792>
3046 --EXECUTE IMMEDIATE l_query INTO x_serial_number ;
3047 l_serial_num_fetched := TRUE;
3048 x_allowed_range_hidden := l_ranges_array(i);
3049 x_allowed_range := l_ranges_array(i);
3050 EXIT; --exit the loop as number is fetched
3051 EXCEPTION
3052 WHEN No_Data_Found THEN
3053 l_serial_num_fetched := FALSE;
3054 WHEN OTHERS THEN
3055 x_return_status := FND_API.G_RET_STS_ERROR;
3056 x_errorcode := '-1';
3057 x_msg_count := '1';
3058 x_msg_data := 'Unable to generate serial number';
3059 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
3060 fnd_msg_pub.ADD;
3061 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3062 END;
3063
3064 END IF;
3065 END LOOP;
3066
3067 END IF;--if l_serial_num_fetched = false
3068
3069 IF l_serial_num_fetched = TRUE THEN
3070 x_document_number := x_dodaac || x_delimiter
3071 || x_fiscal_year || x_delimiter
3072 || x_instrument_type || x_delimiter
3073 || x_serial_number ;
3074 ELSE
3075 x_return_status := FND_API.G_RET_STS_ERROR;
3076 x_errorcode := '-1';
3077 x_msg_count := '1';
3078 x_msg_data := 'Unable to generate serial number';
3079 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
3080 fnd_msg_pub.ADD;
3081 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3082 END IF;
3083 END IF;
3084
3085 END dod_award_numbering_XD;
3086
3087
3088
3089 PROCEDURE dod_award_numbering_XDN
3090 (x_order_header_id IN NUMBER
3091 ,x_dodaac IN OUT NOCOPY VARCHAR2
3092 ,x_fiscal_year IN OUT NOCOPY VARCHAR2
3093 ,x_instrument_type IN OUT NOCOPY VARCHAR2
3094 ,x_serial_number IN OUT NOCOPY VARCHAR2
3095 ,x_delimiter IN OUT NOCOPY VARCHAR2
3096 ,x_document_number IN OUT NOCOPY VARCHAR2
3097 ,x_return_status OUT NOCOPY VARCHAR2
3098 ,x_errorcode OUT NOCOPY VARCHAR2
3099 ,x_msg_count OUT NOCOPY VARCHAR2
3100 ,x_msg_data OUT NOCOPY VARCHAR2
3101 )
3102 IS
3103
3104 BEGIN
3105 x_serial_number := Upper(x_serial_number);
3106 x_document_number := x_dodaac || x_delimiter
3107 || x_fiscal_year || x_delimiter
3108 || x_instrument_type || x_delimiter
3109 || x_serial_number;
3110
3111
3112 G_PO_VALIDATION_EVENT_TYPE := 'XDN' ;
3113 dod_award_numbering_XV(x_order_header_id
3114 ,x_dodaac
3115 ,x_fiscal_year
3116 ,x_instrument_type
3117 ,x_serial_number
3118 ,x_document_number
3119 ,x_return_status
3120 ,x_errorcode
3121 ,x_msg_count
3122 ,x_msg_data
3123 );
3124 G_PO_VALIDATION_EVENT_TYPE := 'SAVE'; --RESET TO SAVE
3125
3126 END dod_award_numbering_XDN;
3127
3128
3129
3130 PROCEDURE dod_award_numbering_XV
3131 (x_order_header_id IN NUMBER
3132 ,x_dodaac IN VARCHAR2
3133 ,x_fiscal_year IN VARCHAR2
3134 ,x_instrument_type IN VARCHAR2
3135 ,x_serial_number IN VARCHAR2
3136 ,x_document_number IN VARCHAR2
3137 ,x_return_status OUT NOCOPY VARCHAR2
3138 ,x_errorcode OUT NOCOPY VARCHAR2
3139 ,x_msg_count OUT NOCOPY VARCHAR2
3140 ,x_msg_data OUT NOCOPY VARCHAR2
3141 )
3142 IS
3143
3144
3145 l_query VARCHAR2(4000);
3146 l_col_name VARCHAR2(20);
3147 l_attrb_grp_id NUMBER;
3148 l_cnt NUMBER;
3149
3150 BEGIN
3151 --Initialize the out variable to success
3152 x_return_status := FND_API.G_RET_STS_SUCCESS;
3153 x_errorcode := '0';
3154 x_msg_count := '1';
3155 x_msg_data := 'SUCCESS';
3156
3157 --If any of the value is null then we need to throw back the error
3158 IF x_dodaac IS NULL OR x_dodaac = '' OR
3159 x_fiscal_year IS NULL OR
3160 x_instrument_type IS NULL OR x_instrument_type = '' THEN
3161
3162 x_return_status := FND_API.G_RET_STS_ERROR;
3163 x_errorcode := '-1';
3164 x_msg_count := '1';
3165 x_msg_data := 'All the fields are compulsory, please enter the value for all the Attributes.';
3166 fnd_message.set_name('PO', 'PO_DOC_NUM_ALL_FIELDS_COMPUL');
3167 fnd_msg_pub.ADD;
3168 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3169
3170 END IF;
3171
3172
3173 IF x_serial_number IS NOT NULL AND (G_PO_VALIDATION_EVENT_TYPE LIKE 'SAVE' OR Upper(x_serial_number) <> 'XXXX') THEN
3174
3175 IF 'N' = validate_serial_number(Upper(x_serial_number)) THEN
3176 x_return_status := FND_API.G_RET_STS_ERROR;
3177 x_errorcode := '-1';
3178 x_msg_count := '1';
3179 x_msg_data := 'Serial number is invalid.';
3180 fnd_message.set_name('PO', 'PO_CLM_SERIAL_NUM_INVALID');
3181 fnd_msg_pub.ADD;
3182 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3183 ELSE
3184
3185 IF(Length(x_document_number) > 50) THEN
3186 x_return_status := FND_API.G_RET_STS_ERROR;
3187 x_errorcode := '-1';
3188 x_msg_count := '1';
3189 x_msg_data := 'Document Number exceeds the maximum allowed limit of 50.';
3190 fnd_message.set_name('PO', 'PO_CLM_DOC_NUM_LENGTH_ERR');
3191 fnd_msg_pub.ADD;
3192 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3193 END IF;
3194
3195 select count(*) INTO l_cnt
3196 from PO_HEADERS_ALL_EXT_B
3197 WHERE C_EXT_ATTR40 = x_document_number
3198 AND po_header_id <> x_order_header_id;
3199
3200 IF l_cnt > 0 THEN
3201 x_return_status := FND_API.G_RET_STS_ERROR;
3202 x_errorcode := '-1';
3203 x_msg_count := '1';
3204 x_msg_data := 'Document Number is not Unique';
3205 fnd_message.set_name('PO', 'PO_DOC_NUM_NOT_UNIQUE');
3206 fnd_msg_pub.ADD;
3207 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3208 END IF;
3209 END IF;
3210 END IF;
3211
3212 END dod_award_numbering_XV;
3213
3214
3215 PROCEDURE dod_award_numbering_XLE
3216 (x_dodaac IN OUT NOCOPY VARCHAR2
3217 ,x_fiscal_year IN OUT NOCOPY VARCHAR2
3218 ,x_instrument_type IN OUT NOCOPY VARCHAR2
3219 ,x_allowed_range IN OUT NOCOPY VARCHAR2
3220 ,x_min_value IN OUT NOCOPY VARCHAR2
3221 ,x_max_value IN OUT NOCOPY VARCHAR2
3222 ,x_serial_number IN OUT NOCOPY VARCHAR2
3223 ,x_delimiter IN OUT NOCOPY VARCHAR2
3224 ,x_document_number IN OUT NOCOPY VARCHAR2
3225 ,x_dodaac_hidden IN OUT NOCOPY VARCHAR2
3226 ,x_instrument_type_hidden IN OUT NOCOPY VARCHAR2
3227 ,x_allowed_range_hidden IN OUT NOCOPY VARCHAR2
3228 ,x_return_status OUT NOCOPY VARCHAR2
3229 ,x_errorcode OUT NOCOPY VARCHAR2
3230 ,x_msg_count OUT NOCOPY VARCHAR2
3231 ,x_msg_data OUT NOCOPY VARCHAR2
3232 )
3233 IS
3234
3235 BEGIN
3236
3237 IF x_instrument_type_hidden <> x_instrument_type OR
3238 x_allowed_range_hidden <> x_allowed_range OR
3239 x_dodaac_hidden <> x_dodaac THEN
3240
3241 x_dodaac_hidden := x_dodaac;
3242 x_instrument_type_hidden := x_instrument_type;
3243 x_allowed_range_hidden := x_allowed_range;
3244
3245 x_min_value := SubStr(x_allowed_range,1,4);
3246 x_max_value := SubStr(x_allowed_range,6);
3247
3248 x_serial_number := 'XXXX';
3249
3250 x_document_number := x_dodaac || x_delimiter
3251 || x_fiscal_year || x_delimiter
3252 || x_instrument_type || x_delimiter
3253 || x_serial_number ;
3254
3255 END IF;
3256
3257 x_return_status := FND_API.G_RET_STS_SUCCESS;
3258 x_errorcode := '0';
3259 x_msg_count := '1';
3260 x_msg_data := 'SUCCESS';
3261
3262 END dod_award_numbering_XLE;
3263
3264
3265 PROCEDURE dod_award_numbering_XSC
3266 (x_order_header_id IN NUMBER
3267 ,x_dodaac IN VARCHAR2
3268 ,x_fiscal_year IN VARCHAR2
3269 ,x_instrument_type IN VARCHAR2
3270 ,x_serial_number IN VARCHAR2
3271 ,x_document_number IN VARCHAR2
3272 ,x_return_status OUT NOCOPY VARCHAR2
3273 ,x_errorcode OUT NOCOPY VARCHAR2
3274 ,x_msg_count OUT NOCOPY VARCHAR2
3275 ,x_msg_data OUT NOCOPY VARCHAR2
3276 )
3277 IS
3278
3279 BEGIN
3280
3281 dod_award_numbering_XV(x_order_header_id
3282 ,x_dodaac
3283 ,x_fiscal_year
3284 ,x_instrument_type
3285 ,x_serial_number
3286 ,x_document_number
3287 ,x_return_status
3288 ,x_errorcode
3289 ,x_msg_count
3290 ,x_msg_data
3291 );
3292
3293 END dod_award_numbering_XSC;
3294
3295
3296 PROCEDURE dod_award_numbering_XDA
3297 (x_order_header_id IN NUMBER
3298 ,x_dodaac IN OUT NOCOPY VARCHAR2
3299 ,x_fiscal_year IN OUT NOCOPY VARCHAR2
3300 ,x_instrument_type IN OUT NOCOPY VARCHAR2
3301 ,x_serial_number IN OUT NOCOPY VARCHAR2
3302 ,x_delimiter IN OUT NOCOPY VARCHAR2
3303 ,x_document_number IN OUT NOCOPY VARCHAR2
3304 ,x_delimiter_ext OUT NOCOPY VARCHAR2
3305 ,x_return_status OUT NOCOPY VARCHAR2
3306 ,x_errorcode OUT NOCOPY VARCHAR2
3307 ,x_msg_count OUT NOCOPY VARCHAR2
3308 ,x_msg_data OUT NOCOPY VARCHAR2
3309 )
3310 IS
3311 BEGIN
3312
3313 x_return_status := FND_API.G_RET_STS_SUCCESS;
3314 x_errorcode := '0';
3315 x_msg_count := '1';
3316 x_msg_data := 'SUCCESS';
3317
3318 x_delimiter := '-';
3319 x_delimiter_ext := '-';
3320
3321 END dod_award_numbering_XDA;
3322
3323
3324 PROCEDURE dod_order_numbering_ow_XD
3325 (x_source_doc_number IN OUT NOCOPY VARCHAR2
3326 ,x_allowed_range IN OUT NOCOPY VARCHAR2
3327 ,x_min_value IN OUT NOCOPY VARCHAR2
3328 ,x_max_value IN OUT NOCOPY VARCHAR2
3329 ,x_serial_number IN OUT NOCOPY VARCHAR2
3330 ,x_delimiter IN OUT NOCOPY VARCHAR2
3331 ,x_document_number IN OUT NOCOPY VARCHAR2
3332 ,x_allowed_range_hidden IN OUT NOCOPY VARCHAR2
3333 ,p_source_doc_number IN VARCHAR2
3334 ,x_return_status OUT NOCOPY VARCHAR2
3335 ,x_errorcode OUT NOCOPY VARCHAR2
3336 ,x_msg_count OUT NOCOPY VARCHAR2
3337 ,x_msg_data OUT NOCOPY VARCHAR2
3338 )
3339
3340 IS
3341
3342
3343 l_query VARCHAR2(4000);
3344 l_col_name VARCHAR2(20);
3345 l_attrb_grp_id NUMBER;
3346
3347 l_attr_grp_ids Dbms_Sql.number_table;
3348 l_where_cls VARCHAR2(100):='';
3349
3350 CURSOR C IS
3351 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
3352 from PO_UDA_AG_TEMPLATE_USAGES puatu,
3353 po_uda_ag_templates puat
3354 WHERE puat.ENTITY_CODE='PO_HEADER_EXT_ATTRS'
3355 AND puat.document_level='HEADER'
3356 AND puat.template_id = puatu.template_id
3357 AND puatu.attribute_category='DOCUMENT_NUMBERING'
3358 AND puatu.attribute1='Base Document'
3359 AND puatu.attribute2='Y';
3360
3361 CURSOR RANGES IS
3362 SELECT lookup_code
3363 FROM (
3364 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_ALLOWED_RANGE'
3365 and enabled_flag = 'Y'
3366 and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code);
3367
3368 l_ranges_array Dbms_Sql.varchar2_table;
3369 l_serial_num_fetched BOOLEAN := FALSE;
3370
3371 min_code NUMBER;
3372 max_code NUMBER;
3373 l_lookup_code NUMBER;
3374
3375 BEGIN
3376 --First initialize the out variable to success
3377 x_return_status := FND_API.G_RET_STS_SUCCESS;
3378 x_errorcode := '0';
3379 x_msg_count := '1';
3380 x_msg_data := 'SUCCESS';
3381
3382 IF(x_source_doc_number IS NULL AND x_allowed_range IS NULL AND
3383 x_serial_number IS NULL AND x_document_number is null) THEN
3384
3385 -- Do the defaulting for attributes and set the document number
3386 x_source_doc_number := p_source_doc_number;
3387
3388 BEGIN
3389 SELECT lookup_code
3390 INTO x_allowed_range
3391 FROM (
3392 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_ALLOWED_RANGE'
3393 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
3394 WHERE ROWNUM=1;
3395 EXCEPTION
3396 WHEN No_Data_Found THEN
3397 x_return_status := FND_API.G_RET_STS_ERROR;
3398 x_errorcode := '-1';
3399 x_msg_count := '1';
3400 x_msg_data := 'Lookup PO_CLM_ALLOWED_RANGE not found';
3401 fnd_message.set_name('PO', 'PO_CLM_ALLOWED_RANGE_NVD');
3402 fnd_msg_pub.ADD;
3403 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3404 RETURN;
3405 END;
3406
3407 x_allowed_range_hidden := x_allowed_range;
3408 x_delimiter := '-';
3409
3410 --Serial number should be defaulted to XXXX
3411 x_serial_number :='XXXX';
3412
3413 x_document_number := x_source_doc_number || x_delimiter || x_serial_number;
3414
3415 ELSIF x_serial_number IS NULL OR x_serial_number = 'XXXX' THEN
3416
3417 OPEN C;
3418 FETCH C BULK COLLECT INTO l_attr_grp_ids;
3419 CLOSE C;
3420
3421 FOR i IN 1..l_attr_grp_ids.Count LOOP
3422 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
3423 END LOOP;
3424
3425 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
3426
3427 x_min_value := SubStr(x_allowed_range,1,4);
3428 x_max_value := SubStr(x_allowed_range,6);
3429
3430 -- Bug 16468041 Rewriting the below query due to performance issue.
3431 /*l_query := ' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
3432 ' ( select min(to_number(lookup_code)) from ( '||
3433 ' SELECT lookup_code FROM po_lookup_codes '||
3434 ' WHERE to_number(lookup_code) BETWEEN '||
3435 ' (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
3436 x_min_value||'''and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
3437 ' and (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
3438 x_max_value||''' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
3439 ' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'' '||
3440 ' minus '||
3441 ' SELECT distinct plc.lookup_code FROM '||
3442 ' PO_HEADERS_ALL_EXT_B prheb, po_lookup_codes plc ' ||
3443 ' WHERE plc.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
3444 ' and prheb.C_EXT_ATTR7 = plc.displayed_field ' ||
3445 ' and prheb.C_EXT_ATTR1 = '''||x_source_doc_number ||''''||
3446 ' and prheb.attr_group_id in ('|| l_where_cls ||')))' ||
3447 ' AND lookup_type = ''PO_CLM_SERIAL_NUM_CONSTANTS'' ';*/
3448
3449 BEGIN
3450 SELECT to_number(lookup_code)
3451 INTO min_code
3452 FROM fnd_lookup_values
3453 WHERE meaning = x_min_value
3454 AND language = userenv ('LANG')
3455 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
3456
3457 SELECT to_number(lookup_code)
3458 INTO max_code
3459 FROM fnd_lookup_values
3460 WHERE meaning = x_max_value
3461 AND language = userenv ('LANG')
3462 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
3463
3464 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
3465 ' FROM PO_HEADERS_ALL_EXT_B prheb, fnd_lookup_values flv ' ||
3466 ' WHERE prheb.C_EXT_ATTR1 = '''||x_source_doc_number ||''''||
3467 ' AND flv.meaning = prheb.C_EXT_ATTR7 ' ||
3468 ' AND flv.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
3469 ' AND flv.language = userenv (''LANG'')' ||
3470 ' AND to_number(flv.lookup_code) between ' || min_code || ' AND ' || max_code ||
3471 ' AND prheb.attr_group_id in (' || l_where_cls || ')';
3472
3473 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
3474
3475 SELECT meaning
3476 INTO x_serial_number
3477 FROM fnd_lookup_values
3478 WHERE lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS'
3479 AND language = userenv ('LANG')
3480 AND to_number(lookup_code) = Nvl(l_lookup_code, min_code) --<Bug 16699792>
3481 AND to_number(lookup_code) <= max_code; --<Bug 16699792>
3482 --EXECUTE IMMEDIATE l_query INTO x_serial_number ;
3483 l_serial_num_fetched := TRUE;
3484 EXCEPTION
3485 WHEN No_Data_Found THEN
3486 l_serial_num_fetched := FALSE;
3487 WHEN OTHERS THEN
3488 x_return_status := FND_API.G_RET_STS_ERROR;
3489 x_errorcode := '-1';
3490 x_msg_count := '1';
3491 x_msg_data := 'Unable to generate serial number';
3492 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
3493 fnd_msg_pub.ADD;
3494 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3495 END;
3496
3497 IF l_serial_num_fetched = FALSE THEN
3498
3499 OPEN RANGES;
3500 FETCH RANGES BULK COLLECT INTO l_ranges_array;
3501 CLOSE RANGES;
3502
3503 FOR i IN 1..l_ranges_array.Count LOOP
3504
3505 IF l_ranges_array(i) <> x_allowed_range THEN
3506
3507 x_min_value := SubStr(l_ranges_array(i),1,4);
3508 x_max_value := SubStr(l_ranges_array(i),6);
3509
3510 -- Bug 16468041 Rewriting the below query due to performance issue.
3511 /*l_query :=' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
3512 ' ( select min(to_number(lookup_code)) from ( '||
3513 ' SELECT lookup_code FROM po_lookup_codes '||
3514 ' WHERE to_number(lookup_code) BETWEEN '||
3515 ' (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
3516 x_min_value||'''and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
3517 ' and (SELECT to_number(lookup_code) FROM po_lookup_codes WHERE displayed_field='''||
3518 x_max_value||''' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'')' ||
3519 ' and lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'' '||
3520 ' minus '||
3521 ' SELECT distinct plc.lookup_code FROM '||
3522 ' PO_HEADERS_ALL_EXT_B prheb, po_lookup_codes plc ' ||
3523 ' WHERE plc.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
3524 ' and prheb.C_EXT_ATTR7 = plc.displayed_field ' ||
3525 ' and prheb.C_EXT_ATTR1 = '''||x_source_doc_number ||''''||
3526 ' and prheb.attr_group_id in ('|| l_where_cls ||')))' ||
3527 ' AND lookup_type = ''PO_CLM_SERIAL_NUM_CONSTANTS'' ';*/
3528
3529 BEGIN
3530 SELECT to_number(lookup_code)
3531 INTO min_code
3532 FROM fnd_lookup_values
3533 WHERE meaning = x_min_value
3534 AND language = userenv ('LANG')
3535 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
3536
3537 SELECT to_number(lookup_code)
3538 INTO max_code
3539 FROM fnd_lookup_values
3540 WHERE meaning = x_max_value
3541 AND language = userenv ('LANG')
3542 AND lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS';
3543
3544 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
3545 ' FROM PO_HEADERS_ALL_EXT_B prheb, fnd_lookup_values flv ' ||
3546 ' WHERE prheb.C_EXT_ATTR1 = '''||x_source_doc_number ||''''||
3547 ' AND flv.meaning = prheb.C_EXT_ATTR7 ' ||
3548 ' AND flv.lookup_type=''PO_CLM_SERIAL_NUM_CONSTANTS'''||
3549 ' AND flv.language = userenv (''LANG'')' ||
3550 ' AND to_number(flv.lookup_code) between ' || min_code || ' AND ' || max_code ||
3551 ' AND prheb.attr_group_id in (' || l_where_cls || ')';
3552
3553 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
3554
3555 SELECT meaning
3556 INTO x_serial_number
3557 FROM fnd_lookup_values
3558 WHERE lookup_type='PO_CLM_SERIAL_NUM_CONSTANTS'
3559 AND language = userenv ('LANG')
3560 AND to_number(lookup_code) = Nvl(l_lookup_code, min_code) --<Bug 16699792>
3561 AND to_number(lookup_code) <= max_code; --<Bug 16699792>
3562
3563 --EXECUTE IMMEDIATE l_query INTO x_serial_number ;
3564 l_serial_num_fetched := TRUE;
3565 x_allowed_range_hidden := l_ranges_array(i);
3566 x_allowed_range := l_ranges_array(i);
3567 EXIT; --exit the loop as number is fetched
3568 EXCEPTION
3569 WHEN No_Data_Found THEN
3570 l_serial_num_fetched := FALSE;
3571 WHEN OTHERS THEN
3572 x_return_status := FND_API.G_RET_STS_ERROR;
3573 x_errorcode := '-1';
3574 x_msg_count := '1';
3575 x_msg_data := 'Unable to generate serial number';
3576 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
3577 fnd_msg_pub.ADD;
3578 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3579 END;
3580
3581 END IF;
3582 END LOOP;
3583
3584 END IF;--if l_serial_num_fetched = false
3585
3586 IF l_serial_num_fetched = TRUE THEN
3587 x_document_number := x_source_doc_number || x_delimiter || x_serial_number;
3588 ELSE
3589 x_return_status := FND_API.G_RET_STS_ERROR;
3590 x_errorcode := '-1';
3591 x_msg_count := '1';
3592 x_msg_data := 'Unable to generate serial number';
3593 fnd_message.set_name('PO', 'PO_DOC_NUM_SER_NUM_ERROR');
3594 fnd_msg_pub.ADD;
3595 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3596 END IF;
3597
3598 END IF;
3599
3600 END dod_order_numbering_ow_XD;
3601
3602
3603
3604 PROCEDURE dod_order_numbering_ow_XV
3605 (x_order_header_id IN NUMBER
3606 ,x_source_doc_number IN VARCHAR2
3607 ,x_serial_number IN VARCHAR2
3608 ,x_document_number IN VARCHAR2
3609 ,x_return_status OUT NOCOPY VARCHAR2
3610 ,x_errorcode OUT NOCOPY VARCHAR2
3611 ,x_msg_count OUT NOCOPY VARCHAR2
3612 ,x_msg_data OUT NOCOPY VARCHAR2
3613 )
3614 IS
3615
3616
3617 l_query VARCHAR2(4000);
3618 l_col_name VARCHAR2(20);
3619 l_attrb_grp_id NUMBER;
3620 l_cnt NUMBER;
3621
3622 BEGIN
3623 --First initialize the out variable to success
3624 x_return_status := FND_API.G_RET_STS_SUCCESS;
3625 x_errorcode := '0';
3626 x_msg_count := '1';
3627 x_msg_data := 'SUCCESS';
3628
3629 --If any of the value is null then we need to throw back the error
3630 IF x_source_doc_number IS NULL OR x_source_doc_number = '' THEN
3631
3632 x_return_status := FND_API.G_RET_STS_ERROR;
3633 x_errorcode := '-1';
3634 x_msg_count := '1';
3635 x_msg_data := 'All the fields are compulsory, please enter the value for all the Attributes.';
3636 fnd_message.set_name('PO', 'PO_DOC_NUM_ALL_FIELDS_COMPUL');
3637 fnd_msg_pub.ADD;
3638 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3639
3640 END IF;
3641
3642
3643 IF x_serial_number IS NOT NULL AND (G_PO_VALIDATION_EVENT_TYPE LIKE 'SAVE' OR Upper(x_serial_number) <> 'XXXX') THEN
3644 IF 'N' = validate_serial_number(Upper(x_serial_number)) THEN
3645 x_return_status := FND_API.G_RET_STS_ERROR;
3646 x_errorcode := '-1';
3647 x_msg_count := '1';
3648 x_msg_data := 'Serial number is invalid.';
3649 fnd_message.set_name('PO', 'PO_CLM_SERIAL_NUM_INVALID');
3650 fnd_msg_pub.ADD;
3651 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3652 ELSE
3653
3654 select count(*) INTO l_cnt
3655 from PO_HEADERS_ALL_EXT_B
3656 WHERE C_EXT_ATTR40 = x_document_number
3657 AND po_header_id <> x_order_header_id;
3658
3659 IF l_cnt > 0 THEN
3660 x_return_status := FND_API.G_RET_STS_ERROR;
3661 x_errorcode := '-1';
3662 x_msg_count := '1';
3663 x_msg_data := 'Document Number is not Unique';
3664 fnd_message.set_name('PO', 'PO_DOC_NUM_NOT_UNIQUE');
3665 fnd_msg_pub.ADD;
3666 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3667 END IF;
3668 END IF;
3669 END IF;
3670
3671 END dod_order_numbering_ow_XV;
3672
3673
3674 PROCEDURE dod_order_numbering_ow_XDN
3675 (x_order_header_id IN NUMBER
3676 ,x_source_doc_number IN OUT NOCOPY VARCHAR2
3677 ,x_allowed_range IN OUT NOCOPY VARCHAR2
3678 ,x_min_value IN OUT NOCOPY VARCHAR2
3679 ,x_max_value IN OUT NOCOPY VARCHAR2
3680 ,x_serial_number IN OUT NOCOPY VARCHAR2
3681 ,x_delimiter IN OUT NOCOPY VARCHAR2
3682 ,x_document_number IN OUT NOCOPY VARCHAR2
3683 ,x_return_status OUT NOCOPY VARCHAR2
3684 ,x_errorcode OUT NOCOPY VARCHAR2
3685 ,x_msg_count OUT NOCOPY VARCHAR2
3686 ,x_msg_data OUT NOCOPY VARCHAR2
3687 )
3688 IS
3689
3690 BEGIN
3691 x_serial_number := Upper(x_serial_number);
3692 x_document_number := x_source_doc_number || x_delimiter || x_serial_number;
3693
3694 G_PO_VALIDATION_EVENT_TYPE := 'XDN';
3695
3696 dod_order_numbering_ow_XV(x_order_header_id
3697 ,x_source_doc_number
3698 ,x_serial_number
3699 ,x_document_number
3700 ,x_return_status
3701 ,x_errorcode
3702 ,x_msg_count
3703 ,x_msg_data
3704 );
3705
3706 G_PO_VALIDATION_EVENT_TYPE := 'SAVE'; --reset to save
3707
3708 END dod_order_numbering_ow_XDN;
3709
3710
3711 PROCEDURE dod_order_numbering_ow_XLE
3712 (x_source_doc_number IN OUT NOCOPY VARCHAR2
3713 ,x_allowed_range IN OUT NOCOPY VARCHAR2
3714 ,x_min_value IN OUT NOCOPY VARCHAR2
3715 ,x_max_value IN OUT NOCOPY VARCHAR2
3716 ,x_serial_number IN OUT NOCOPY VARCHAR2
3717 ,x_delimiter IN OUT NOCOPY VARCHAR2
3718 ,x_document_number IN OUT NOCOPY VARCHAR2
3719 ,x_allowed_range_hidden IN OUT NOCOPY VARCHAR2
3720 ,x_return_status OUT NOCOPY VARCHAR2
3721 ,x_errorcode OUT NOCOPY VARCHAR2
3722 ,x_msg_count OUT NOCOPY VARCHAR2
3723 ,x_msg_data OUT NOCOPY VARCHAR2
3724 )
3725 IS
3726
3727 BEGIN
3728
3729 IF x_allowed_range_hidden <> x_allowed_range THEN
3730
3731 x_allowed_range_hidden := x_allowed_range;
3732
3733 x_min_value := SubStr(x_allowed_range,1,4);
3734 x_max_value := SubStr(x_allowed_range,6);
3735
3736 x_serial_number :='XXXX';
3737
3738 x_document_number := x_source_doc_number || x_delimiter || x_serial_number;
3739
3740 END IF;
3741
3742 x_return_status := FND_API.G_RET_STS_SUCCESS;
3743 x_errorcode := '0';
3744 x_msg_count := '1';
3745 x_msg_data := 'SUCCESS';
3746
3747 END dod_order_numbering_ow_XLE;
3748
3749
3750 PROCEDURE dod_order_numbering_ow_XSC
3751 (x_order_header_id IN NUMBER
3752 ,x_source_doc_number IN VARCHAR2
3753 ,x_serial_number IN VARCHAR2
3754 ,x_document_number IN VARCHAR2
3755 ,x_return_status OUT NOCOPY VARCHAR2
3756 ,x_errorcode OUT NOCOPY VARCHAR2
3757 ,x_msg_count OUT NOCOPY VARCHAR2
3758 ,x_msg_data OUT NOCOPY VARCHAR2
3759 )
3760 IS
3761
3762 BEGIN
3763
3764 dod_order_numbering_ow_XV(x_order_header_id
3765 ,x_source_doc_number
3766 ,x_serial_number
3767 ,x_document_number
3768 ,x_return_status
3769 ,x_errorcode
3770 ,x_msg_count
3771 ,x_msg_data
3772 );
3773
3774
3775 END dod_order_numbering_ow_XSC;
3776
3777
3778 PROCEDURE dod_order_numbering_ow_XDA
3779 (x_order_header_id IN NUMBER
3780 ,x_source_doc_number IN OUT NOCOPY VARCHAR2
3781 ,x_serial_number IN OUT NOCOPY VARCHAR2
3782 ,x_delimiter IN OUT NOCOPY VARCHAR2
3783 ,x_document_number IN OUT NOCOPY VARCHAR2
3784 ,x_delimiter_ext OUT NOCOPY VARCHAR2
3785 ,x_return_status OUT NOCOPY VARCHAR2
3786 ,x_errorcode OUT NOCOPY VARCHAR2
3787 ,x_msg_count OUT NOCOPY VARCHAR2
3788 ,x_msg_data OUT NOCOPY VARCHAR2
3789 )
3790 IS
3791 BEGIN
3792
3793 x_return_status := FND_API.G_RET_STS_SUCCESS;
3794 x_errorcode := '0';
3795 x_msg_count := '1';
3796 x_msg_data := 'SUCCESS';
3797
3798 x_delimiter := '-';
3799 x_delimiter_ext := '-';
3800
3801 END dod_order_numbering_ow_XDA;
3802
3803
3804 PROCEDURE dod_order_numbering_now_XD
3805 (x_source_doc_number IN OUT NOCOPY VARCHAR2
3806 ,x_call_order_number IN OUT NOCOPY VARCHAR2
3807 ,x_serial_number IN OUT NOCOPY VARCHAR2
3808 ,x_delimiter IN OUT NOCOPY VARCHAR2
3809 ,x_document_number IN OUT NOCOPY VARCHAR2
3810 ,p_source_doc_number IN VARCHAR2
3811 ,x_return_status OUT NOCOPY VARCHAR2
3812 ,x_errorcode OUT NOCOPY VARCHAR2
3813 ,x_msg_count OUT NOCOPY VARCHAR2
3814 ,x_msg_data OUT NOCOPY VARCHAR2
3815 )
3816
3817 IS
3818
3819
3820 l_query VARCHAR2(4000);
3821 l_col_name VARCHAR2(20);
3822 l_attrb_grp_id NUMBER;
3823 l_num NUMBER;
3824 l_attr_grp_ids Dbms_Sql.number_table;
3825 l_where_cls VARCHAR2(100):='';
3826
3827 CURSOR C IS
3828 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
3829 from PO_UDA_AG_TEMPLATE_USAGES puatu,
3830 po_uda_ag_templates puat
3831 WHERE puat.ENTITY_CODE='PO_HEADER_EXT_ATTRS'
3832 AND puat.document_level='HEADER'
3833 AND puat.template_id = puatu.template_id
3834 AND puatu.attribute_category='DOCUMENT_NUMBERING'
3835 AND puatu.attribute1='Base Document'
3836 AND puatu.attribute2='N';
3837
3838 BEGIN
3839 --First initialize the out variable to success
3840 x_return_status := FND_API.G_RET_STS_SUCCESS;
3841 x_errorcode := '0';
3842 x_msg_count := '1';
3843 x_msg_data := 'SUCCESS';
3844
3845
3846 IF(x_source_doc_number IS NULL AND x_call_order_number IS NULL AND
3847 x_serial_number IS NULL AND x_document_number is null) THEN
3848
3849 -- Do the defaulting for attributes and set the document number
3850 x_source_doc_number := p_source_doc_number;
3851 BEGIN
3852 SELECT PEI_INFORMATION3
3853 INTO x_call_order_number
3854 FROM per_people_extra_info pei, fnd_user fu
3855 WHERE pei.information_type = 'CLM_CONTACT_TITLE'
3856 AND pei.person_id= fu.employee_id
3857 AND fu.user_id = fnd_global.user_id;
3858 EXCEPTION
3859 WHEN No_Data_Found THEN
3860 x_call_order_number:='';
3861 END;
3862
3863 x_delimiter := '-';
3864 x_serial_number := 'XX';
3865
3866 x_document_number := x_source_doc_number || x_delimiter || x_call_order_number|| x_serial_number;
3867
3868 ELSIF x_serial_number IS NULL OR x_serial_number = 'XX' THEN
3869
3870 OPEN C;
3871 FETCH C BULK COLLECT INTO l_attr_grp_ids;
3872 CLOSE C;
3873
3874 FOR i IN 1..l_attr_grp_ids.Count LOOP
3875 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
3876 END LOOP;
3877
3878 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
3879
3880 l_query := ' SELECT Max(To_Number(C_EXT_ATTR7)) '||
3881 ' FROM PO_HEADERS_ALL_EXT_B '||
3882 ' where C_EXT_ATTR1 ='''||x_source_doc_number||''' ' ||
3883 ' and C_EXT_ATTR2 ='''|| x_call_order_number ||''' ' ||
3884 ' and ATTR_GROUP_ID in ('||l_where_cls||') '||
3885 ' and C_EXT_ATTR7 not like ''%X%'' ';
3886
3887 BEGIN
3888 EXECUTE IMMEDIATE l_query INTO l_num;
3889 IF l_num IS NULL THEN
3890 l_num :=0;
3891 END IF;
3892
3893 EXCEPTION
3894 WHEN No_Data_Found THEN
3895 l_num :=0;
3896 END;
3897
3898 x_serial_number := LPad(l_num+1,2,'0');
3899
3900 x_document_number := x_source_doc_number || x_delimiter || x_call_order_number|| x_serial_number;
3901
3902 END IF;
3903
3904 END dod_order_numbering_now_XD;
3905
3906
3907
3908 PROCEDURE dod_order_numbering_now_XV
3909 (x_order_header_id IN NUMBER
3910 ,x_source_doc_number IN VARCHAR2
3911 ,x_call_order_number IN VARCHAR2
3912 ,x_serial_number IN VARCHAR2
3913 ,x_document_number IN VARCHAR2
3914 ,x_return_status OUT NOCOPY VARCHAR2
3915 ,x_errorcode OUT NOCOPY VARCHAR2
3916 ,x_msg_count OUT NOCOPY VARCHAR2
3917 ,x_msg_data OUT NOCOPY VARCHAR2
3918 )
3919 IS
3920
3921
3922 l_query VARCHAR2(4000);
3923 l_col_name VARCHAR2(20);
3924 l_attrb_grp_id NUMBER;
3925 l_cnt NUMBER;
3926
3927 BEGIN
3928 --First initialize the out variable to success
3929 x_return_status := FND_API.G_RET_STS_SUCCESS;
3930 x_errorcode := '0';
3931 x_msg_count := '1';
3932 x_msg_data := 'SUCCESS';
3933
3934 --If any of the value is null then we need to throw back the error
3935 IF x_source_doc_number IS NULL OR x_source_doc_number = '' OR
3936 x_call_order_number IS NULL OR x_call_order_number = '' THEN
3937
3938 x_return_status := FND_API.G_RET_STS_ERROR;
3939 x_errorcode := '-1';
3940 x_msg_count := '1';
3941 x_msg_data := 'All the fields are compulsory, please enter the value for all the Attributes.';
3942 fnd_message.set_name('PO', 'PO_DOC_NUM_ALL_FIELDS_COMPUL');
3943 fnd_msg_pub.ADD;
3944 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3945
3946 END IF;
3947
3948 IF x_serial_number IS NOT NULL AND (G_PO_VALIDATION_EVENT_TYPE LIKE 'SAVE' OR Upper(x_serial_number) <> 'XX') THEN
3949
3950 IF 'N' = validate_serial_number_2digit(Upper(x_serial_number)) THEN
3951 x_return_status := FND_API.G_RET_STS_ERROR;
3952 x_errorcode := '-1';
3953 x_msg_count := '1';
3954 x_msg_data := 'Serial number is invalid.';
3955 fnd_message.set_name('PO', 'PO_CLM_SERIAL_NUM_INVALID');
3956 fnd_msg_pub.ADD;
3957 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3958 ELSE
3959
3960 select count(*) INTO l_cnt
3961 from PO_HEADERS_ALL_EXT_B
3962 WHERE C_EXT_ATTR40 = x_document_number
3963 AND po_header_id <> x_order_header_id;
3964
3965 IF l_cnt > 0 THEN
3966 x_return_status := FND_API.G_RET_STS_ERROR;
3967 x_errorcode := '-1';
3968 x_msg_count := '1';
3969 x_msg_data := 'Document Number is not Unique';
3970 fnd_message.set_name('PO', 'PO_DOC_NUM_NOT_UNIQUE');
3971 fnd_msg_pub.ADD;
3972 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
3973 END IF;
3974 END IF;
3975 END IF;
3976
3977 END dod_order_numbering_now_XV;
3978
3979
3980 PROCEDURE dod_order_numbering_now_XDN
3981 (x_order_header_id IN NUMBER
3982 ,x_source_doc_number IN OUT NOCOPY VARCHAR2
3983 ,x_call_order_number IN OUT NOCOPY VARCHAR2
3984 ,x_serial_number IN OUT NOCOPY VARCHAR2
3985 ,x_delimiter IN OUT NOCOPY VARCHAR2
3986 ,x_document_number IN OUT NOCOPY VARCHAR2
3987 ,x_return_status OUT NOCOPY VARCHAR2
3988 ,x_errorcode OUT NOCOPY VARCHAR2
3989 ,x_msg_count OUT NOCOPY VARCHAR2
3990 ,x_msg_data OUT NOCOPY VARCHAR2
3991 )
3992 IS
3993
3994 BEGIN
3995 x_serial_number := Upper(x_serial_number);
3996 x_document_number := x_source_doc_number || x_delimiter || x_call_order_number
3997 || x_serial_number;
3998
3999 G_PO_VALIDATION_EVENT_TYPE := 'XDN';
4000 dod_order_numbering_now_XV(x_order_header_id
4001 ,x_source_doc_number
4002 ,x_call_order_number
4003 ,x_serial_number
4004 ,x_document_number
4005 ,x_return_status
4006 ,x_errorcode
4007 ,x_msg_count
4008 ,x_msg_data
4009 );
4010 G_PO_VALIDATION_EVENT_TYPE := 'SAVE'; --reset to save
4011 END dod_order_numbering_now_XDN;
4012
4013
4014 PROCEDURE dod_order_numbering_now_XSC
4015 (x_order_header_id IN NUMBER
4016 ,x_source_doc_number IN VARCHAR2
4017 ,x_call_order_number IN VARCHAR2
4018 ,x_serial_number IN VARCHAR2
4019 ,x_document_number IN VARCHAR2
4020 ,x_return_status OUT NOCOPY VARCHAR2
4021 ,x_errorcode OUT NOCOPY VARCHAR2
4022 ,x_msg_count OUT NOCOPY VARCHAR2
4023 ,x_msg_data OUT NOCOPY VARCHAR2
4024 )
4025 IS
4026
4027 BEGIN
4028
4029 dod_order_numbering_now_XV(x_order_header_id
4030 ,x_source_doc_number
4031 ,x_call_order_number
4032 ,x_serial_number
4033 ,x_document_number
4034 ,x_return_status
4035 ,x_errorcode
4036 ,x_msg_count
4037 ,x_msg_data
4038 );
4039
4040
4041 END dod_order_numbering_now_XSC;
4042
4043
4044 PROCEDURE dod_order_numbering_now_XDA
4045 (x_order_header_id IN NUMBER
4046 ,x_source_doc_number IN OUT NOCOPY VARCHAR2
4047 ,x_call_order_number IN OUT NOCOPY VARCHAR2
4048 ,x_serial_number IN OUT NOCOPY VARCHAR2
4049 ,x_delimiter IN OUT NOCOPY VARCHAR2
4050 ,x_document_number IN OUT NOCOPY VARCHAR2
4051 ,x_delimiter_ext OUT NOCOPY VARCHAR2
4052 ,x_return_status OUT NOCOPY VARCHAR2
4053 ,x_errorcode OUT NOCOPY VARCHAR2
4054 ,x_msg_count OUT NOCOPY VARCHAR2
4055 ,x_msg_data OUT NOCOPY VARCHAR2
4056 )
4057 IS
4058 BEGIN
4059
4060 x_return_status := FND_API.G_RET_STS_SUCCESS;
4061 x_errorcode := '0';
4062 x_msg_count := '1';
4063 x_msg_data := 'SUCCESS';
4064
4065 x_delimiter := '-';
4066 x_delimiter_ext := '-';
4067
4068 END dod_order_numbering_now_XDA;
4069
4070
4071
4072
4073
4074 PROCEDURE dod_sol_amd_XD
4075 (p_auction_header_id IN NUMBER
4076 ,p_base_auction_header_id IN NUMBER
4077 ,x_base_doc_number IN OUT NOCOPY VARCHAR2
4078 ,x_control_char IN OUT NOCOPY varchar2
4079 ,x_serial_number IN OUT NOCOPY VARCHAR2
4080 ,x_delimiter IN OUT NOCOPY VARCHAR2
4081 ,x_document_number IN OUT NOCOPY VARCHAR2
4082 ,x_return_status OUT NOCOPY VARCHAR2
4083 ,x_errorcode OUT NOCOPY VARCHAR2
4084 ,x_msg_count OUT NOCOPY VARCHAR2
4085 ,x_msg_data OUT NOCOPY VARCHAR2
4086 )
4087
4088 IS
4089
4090
4091 l_query VARCHAR2(4000);
4092 l_col_name VARCHAR2(20);
4093 l_attrb_grp_id NUMBER;
4094 l_num NUMBER;
4095 l_attr_grp_ids Dbms_Sql.number_table;
4096 l_where_cls VARCHAR2(100):='';
4097
4098 CURSOR C IS
4099 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
4100 from PO_UDA_AG_TEMPLATE_USAGES puatu,
4101 po_uda_ag_templates puat
4102 WHERE puat.ENTITY_CODE='PON_AUC_HDRS_EXT_ATTRS'
4103 AND puat.document_level='HEADER'
4104 AND puat.template_id = puatu.template_id
4105 AND puatu.attribute_category='DOCUMENT_NUMBERING'
4106 AND puatu.attribute1 ='Modification'
4107 AND puatu.attribute2='NA';
4108
4109 BEGIN
4110 --First initialize the out variable to success
4111 x_return_status := FND_API.G_RET_STS_SUCCESS;
4112 x_errorcode := '0';
4113 x_msg_count := '1';
4114 x_msg_data := 'SUCCESS';
4115
4116 IF(x_base_doc_number is NULL AND x_control_char IS NULL AND
4117 x_serial_number IS NULL AND x_document_number IS null) THEN
4118
4119 SELECT document_number INTO x_base_doc_number
4120 FROM pon_auction_headers_all
4121 WHERE auction_header_id = p_base_auction_header_id;
4122
4123 x_control_char := 'C';
4124 x_delimiter := '-';
4125 x_serial_number := 'XXXX';
4126
4127 x_document_number := x_base_doc_number || x_delimiter || x_control_char|| x_serial_number;
4128
4129 ELSIF x_serial_number IS NULL OR Trim(x_serial_number) = 'XXXX' THEN
4130
4131 OPEN C;
4132 FETCH C BULK COLLECT INTO l_attr_grp_ids;
4133 CLOSE C;
4134
4135 FOR i IN 1..l_attr_grp_ids.Count LOOP
4136 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
4137 END LOOP;
4138
4139 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
4140
4141 l_query := ' SELECT Max(To_Number(C_EXT_ATTR7)) '||
4142 ' FROM PON_AUCTION_HEADERS_EXT_B '||
4143 ' where C_EXT_ATTR1 ='''||x_base_doc_number||''' ' ||
4144 ' and C_EXT_ATTR2 ='''||x_control_char||''' '||
4145 ' and ATTR_GROUP_ID in ('||l_where_cls||') '||
4146 ' and C_EXT_ATTR7 not like ''%X%'' ';
4147
4148 BEGIN
4149 EXECUTE IMMEDIATE l_query INTO l_num;
4150 IF l_num IS NULL THEN
4151 l_num :=0;
4152 END IF;
4153
4154 EXCEPTION
4155 WHEN No_Data_Found THEN
4156 l_num :=0;
4157 END;
4158
4159 x_serial_number := LPad(l_num+1,4,'0');
4160
4161 x_document_number := x_base_doc_number || x_delimiter || x_control_char|| x_serial_number;
4162
4163 END IF;
4164
4165 END dod_sol_amd_XD;
4166
4167
4168
4169 PROCEDURE dod_sol_amd_XV
4170 (p_auction_header_id IN NUMBER
4171 ,x_base_doc_number IN OUT NOCOPY VARCHAR2
4172 ,x_control_char IN OUT NOCOPY VARCHAR2
4173 ,x_serial_number IN OUT NOCOPY VARCHAR2
4174 ,x_delimiter IN OUT NOCOPY VARCHAR2
4175 ,x_document_number IN OUT NOCOPY VARCHAR2
4176 ,x_return_status OUT NOCOPY VARCHAR2
4177 ,x_errorcode OUT NOCOPY VARCHAR2
4178 ,x_msg_count OUT NOCOPY VARCHAR2
4179 ,x_msg_data OUT NOCOPY VARCHAR2
4180 )
4181 IS
4182
4183
4184 l_query VARCHAR2(4000);
4185 l_col_name VARCHAR2(20);
4186 l_attrb_grp_id NUMBER;
4187 l_cnt NUMBER;
4188
4189 BEGIN
4190 --First initialize the out variable to success
4191 x_return_status := FND_API.G_RET_STS_SUCCESS;
4192 x_errorcode := '0';
4193 x_msg_count := '1';
4194 x_msg_data := 'SUCCESS';
4195
4196 --If any of the value is null then we need to throw back the error
4197 IF x_base_doc_number IS NULL OR x_base_doc_number='' OR
4198 x_control_char IS NULL OR x_control_char='' THEN
4199
4200 x_return_status := FND_API.G_RET_STS_ERROR;
4201 x_errorcode := '-1';
4202 x_msg_count := '1';
4203 x_msg_data := 'All the fields are compulsory, please enter the value for all the Attributes';
4204 fnd_message.set_name('PO', 'PO_DOC_NUM_ALL_FIELDS_COMPUL');
4205 fnd_msg_pub.ADD;
4206 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
4207 END IF;
4208
4209 IF x_serial_number IS NOT NULL AND Upper(x_serial_number) <> 'XXXX' THEN
4210 select count(*) INTO l_cnt
4211 from PON_AUCTION_HEADERS_EXT_B
4212 WHERE C_EXT_ATTR40 = x_document_number
4213 AND AUCTION_HEADER_ID <> p_auction_header_id;
4214
4215 IF l_cnt > 0 THEN
4216 x_return_status := FND_API.G_RET_STS_ERROR;
4217 x_errorcode := '-1';
4218 x_msg_count := '1';
4219 x_msg_data := 'Document Number is not Unique';
4220 fnd_message.set_name('PO', 'PO_DOC_NUM_NOT_UNIQUE');
4221 fnd_msg_pub.ADD;
4222 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
4223 END IF;
4224 END IF;
4225
4226 END dod_sol_amd_XV;
4227
4228
4229 PROCEDURE dod_sol_amd_XDN
4230 (p_auction_header_id IN NUMBER
4231 ,x_base_doc_number IN OUT NOCOPY VARCHAR2
4232 ,x_control_char IN OUT NOCOPY VARCHAR2
4233 ,x_serial_number IN OUT NOCOPY VARCHAR2
4234 ,x_delimiter IN OUT NOCOPY VARCHAR2
4235 ,x_document_number IN OUT NOCOPY VARCHAR2
4236 ,x_return_status OUT NOCOPY VARCHAR2
4237 ,x_errorcode OUT NOCOPY VARCHAR2
4238 ,x_msg_count OUT NOCOPY VARCHAR2
4239 ,x_msg_data OUT NOCOPY VARCHAR2
4240 )
4241 IS
4242
4243
4244 l_query VARCHAR2(4000);
4245 l_col_name VARCHAR2(20);
4246 l_attrb_grp_id NUMBER;
4247 l_cnt NUMBER;
4248
4249 BEGIN
4250 x_serial_number := Upper(x_serial_number);
4251 x_document_number := x_base_doc_number || x_delimiter || x_control_char||
4252 x_serial_number;
4253
4254 dod_sol_amd_XV
4255 (p_auction_header_id
4256 ,x_base_doc_number
4257 ,x_control_char
4258 ,x_serial_number
4259 ,x_delimiter
4260 ,x_document_number
4261 ,x_return_status
4262 ,x_errorcode
4263 ,x_msg_count
4264 ,x_msg_data
4265 );
4266
4267 END dod_sol_amd_XDN;
4268
4269
4270 PROCEDURE dod_ord_mod_XD
4271 (p_po_header_id IN NUMBER
4272 ,p_draft_id IN NUMBER
4273 ,x_base_doc_number IN OUT NOCOPY VARCHAR2
4274 ,x_control_char IN OUT NOCOPY VARCHAR2
4275 ,x_modif_type IN OUT NOCOPY VARCHAR2
4276 ,x_serial_number IN OUT NOCOPY VARCHAR2
4277 ,x_delimiter IN OUT NOCOPY VARCHAR2
4278 ,x_document_number IN OUT NOCOPY VARCHAR2
4279 ,x_modif_type_h IN OUT NOCOPY VARCHAR2
4280 ,x_return_status OUT NOCOPY VARCHAR2
4281 ,x_errorcode OUT NOCOPY VARCHAR2
4282 ,x_msg_count OUT NOCOPY VARCHAR2
4283 ,x_msg_data OUT NOCOPY VARCHAR2
4284 )
4285 IS
4286
4287 l_query VARCHAR2(4000);
4288 l_attr_grp_ids Dbms_Sql.number_table;
4289 l_where_cls VARCHAR2(100):='';
4290 l_num NUMBER;
4291 l_lookup_const VARCHAR2(50);
4292
4293 CURSOR C IS
4294 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
4295 from PO_UDA_AG_TEMPLATE_USAGES puatu,
4296 po_uda_ag_templates puat
4297 WHERE puat.ENTITY_CODE='PO_HEADER_EXT_ATTRS'
4298 AND puat.document_level='HEADER'
4299 AND puat.template_id = puatu.template_id
4300 AND puatu.attribute_category='DOCUMENT_NUMBERING'
4301 AND puatu.attribute1='Modification'
4302 AND puatu.attribute2='Y';
4303 l_lookup_code NUMBER;
4304 BEGIN
4305
4306 --First initialize the out variable to success
4307 x_return_status := FND_API.G_RET_STS_SUCCESS;
4308 x_errorcode := '0';
4309 x_msg_count := '1';
4310 x_msg_data := 'SUCCESS';
4311
4312 IF(x_base_doc_number is NULL AND x_control_char IS NULL AND
4313 x_serial_number IS NULL AND x_document_number IS null) THEN
4314
4315 SELECT CLM_DOCUMENT_NUMBER INTO x_base_doc_number
4316 FROM po_headers_all
4317 WHERE po_header_id = p_po_header_id;
4318
4319 x_control_char := 'C';
4320
4321 BEGIN
4322 SELECT lookup_code
4323 INTO x_modif_type
4324 FROM (
4325 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_MODIFICATION_TYPE'
4326 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
4327 WHERE ROWNUM=1;
4328 EXCEPTION
4329 WHEN No_Data_Found THEN
4330 x_return_status := FND_API.G_RET_STS_ERROR;
4331 x_errorcode := '-1';
4332 x_msg_count := '1';
4333 x_msg_data := 'Lookup PO_CLM_MODIFICATION_TYPE not found';
4334 fnd_message.set_name('PO', 'PO_CLM_MODIFICATION_TYPE_NVD');
4335 fnd_msg_pub.ADD;
4336 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
4337 RETURN;
4338 END;
4339
4340 x_modif_type_h := x_modif_type;
4341
4342 x_delimiter := '-';
4343 x_serial_number := 'XXXX';
4344
4345 x_document_number := x_base_doc_number ||x_delimiter|| x_control_char||x_serial_number;
4346
4347 ELSIF x_serial_number IS NULL OR x_serial_number = 'XXXX' THEN
4348
4349 IF x_modif_type = 'ACO' THEN
4350 l_lookup_const := 'PO_CLM_ACO_NUM_CONSTANTS';
4351 ELSIF x_modif_type = 'PCO' THEN
4352 l_lookup_const := 'PO_CLM_PCO_NUM_CONSTANTS';
4353 END IF;
4354
4355 OPEN C;
4356 FETCH C BULK COLLECT INTO l_attr_grp_ids;
4357 CLOSE C;
4358
4359 FOR i IN 1..l_attr_grp_ids.Count LOOP
4360 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
4361 END LOOP;
4362
4363 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
4364
4365
4366 -- Bug 16468041 Rewriting the below query due to performance issue.
4367 /*l_query := ' select displayed_field from po_lookup_codes where to_number(lookup_code) in'||
4368 ' ( select min(to_number(lookup_code)) from ( '||
4369 ' SELECT lookup_code FROM po_lookup_codes '||
4370 ' WHERE lookup_type='''||l_lookup_const||''' '||
4371 ' minus '||
4372 ' SELECT distinct plc.lookup_code FROM '||
4373 ' PO_HEADERS_ALL_EXT_B prheb, po_lookup_codes plc ' ||
4374 ' WHERE plc.lookup_type='''||l_lookup_const||''''||
4375 ' and prheb.C_EXT_ATTR7 = plc.displayed_field ' ||
4376 ' and prheb.C_EXT_ATTR1 = '''||x_base_doc_number ||''''||
4377 ' and prheb.C_EXT_ATTR2 = '''||x_control_char ||''''||
4378 ' and prheb.C_EXT_ATTR3 = '''||x_modif_type ||''''||
4379 ' and prheb.attr_group_id in ('|| l_where_cls ||')))' ||
4380 ' AND lookup_type = '''||l_lookup_const||''' ';*/
4381
4382 BEGIN
4383 l_query := ' SELECT max(to_number(lookup_code)) + 1 ' ||
4384 ' FROM PO_HEADERS_ALL_EXT_B prheb, fnd_lookup_values flv ' ||
4385 ' WHERE prheb.C_EXT_ATTR1 = '''||x_base_doc_number ||''''||
4386 ' AND prheb.C_EXT_ATTR2 = '''||x_control_char ||''''||
4387 ' AND prheb.C_EXT_ATTR3 = '''||x_modif_type ||''''||
4388 ' AND flv.meaning = prheb.C_EXT_ATTR7 ' ||
4389 ' AND flv.lookup_type='''||l_lookup_const||''''||
4390 ' AND flv.language = userenv (''LANG'')' ||
4391 ' AND prheb.attr_group_id in (' || l_where_cls || ')';
4392
4393 EXECUTE IMMEDIATE l_query INTO l_lookup_code ;
4394
4395 SELECT meaning
4396 INTO x_serial_number
4397 FROM fnd_lookup_values
4398 WHERE lookup_type = l_lookup_const
4399 AND language = userenv ('LANG')
4400 AND to_number(lookup_code) = Nvl(l_lookup_code, 1); --<Bug 16699792>
4401
4402 --EXECUTE IMMEDIATE l_query INTO x_serial_number;
4403 IF l_num IS NULL THEN
4404 l_num :=0;
4405 --TODO : Throw error
4406 END IF;
4407
4408 EXCEPTION
4409 WHEN No_Data_Found THEN
4410 l_num :=0;
4411 --TODO : Throw error
4412 END;
4413
4414 x_delimiter := '-';
4415 x_document_number := x_base_doc_number ||x_delimiter|| x_control_char||x_serial_number;
4416 END IF;
4417
4418 END dod_ord_mod_XD;
4419
4420
4421
4422 PROCEDURE dod_ord_mod_XV
4423 (p_po_header_id IN NUMBER
4424 ,p_draft_id IN NUMBER
4425 ,x_base_doc_number IN OUT NOCOPY varchar2
4426 ,x_control_char IN OUT NOCOPY VARCHAR2
4427 ,x_serial_number IN OUT NOCOPY VARCHAR2
4428 ,x_delimiter IN OUT NOCOPY VARCHAR2
4429 ,x_document_number IN OUT NOCOPY VARCHAR2
4430 ,x_return_status OUT NOCOPY VARCHAR2
4431 ,x_errorcode OUT NOCOPY VARCHAR2
4432 ,x_msg_count OUT NOCOPY VARCHAR2
4433 ,x_msg_data OUT NOCOPY VARCHAR2
4434 )
4435 IS
4436
4437 l_query VARCHAR2(4000);
4438 l_cnt NUMBER;
4439
4440 BEGIN
4441
4442 --First initialize the out variable to success
4443 x_return_status := FND_API.G_RET_STS_SUCCESS;
4444 x_errorcode := '0';
4445 x_msg_count := '1';
4446 x_msg_data := 'SUCCESS';
4447
4448 IF x_base_doc_number IS NULL OR x_base_doc_number='' OR
4449 x_control_char IS NULL OR x_control_char='' THEN
4450
4451 x_return_status := FND_API.G_RET_STS_ERROR;
4452 x_errorcode := '-1';
4453 x_msg_count := '1';
4454 x_msg_data := 'All the fields are compulsory, please enter the value for all the Attributes';
4455 fnd_message.set_name('PO', 'PO_DOC_NUM_ALL_FIELDS_COMPUL');
4456 fnd_msg_pub.ADD;
4457 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
4458 END IF;
4459
4460 IF x_serial_number IS NOT NULL AND Upper(x_serial_number) <> 'XXXX' THEN
4461
4462 select count(*) INTO l_cnt
4463 from PO_HEADERS_ALL_EXT_B
4464 WHERE C_EXT_ATTR40 = x_document_number
4465 AND draft_id <> p_draft_id
4466 AND po_header_id <> p_po_header_id;
4467
4468 IF l_cnt > 0 THEN
4469 x_return_status := FND_API.G_RET_STS_ERROR;
4470 x_errorcode := '-1';
4471 x_msg_count := '1';
4472 x_msg_data := 'Document Number is not Unique';
4473 fnd_message.set_name('PO', 'PO_DOC_NUM_NOT_UNIQUE');
4474 fnd_msg_pub.ADD;
4475 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
4476 END IF;
4477 END IF;
4478
4479 END dod_ord_mod_XV;
4480
4481
4482 PROCEDURE dod_ord_mod_XLE
4483 (p_po_header_id IN NUMBER
4484 ,p_draft_id IN NUMBER
4485 ,x_base_doc_number IN OUT NOCOPY VARCHAR2
4486 ,x_control_char IN OUT NOCOPY VARCHAR2
4487 ,x_modif_type IN OUT NOCOPY VARCHAR2
4488 ,x_serial_number IN OUT NOCOPY VARCHAR2
4489 ,x_delimiter IN OUT NOCOPY VARCHAR2
4490 ,x_document_number IN OUT NOCOPY VARCHAR2
4491 ,x_modif_type_h IN OUT NOCOPY VARCHAR2
4492 ,x_return_status OUT NOCOPY VARCHAR2
4493 ,x_errorcode OUT NOCOPY VARCHAR2
4494 ,x_msg_count OUT NOCOPY VARCHAR2
4495 ,x_msg_data OUT NOCOPY VARCHAR2
4496 )
4497 IS
4498
4499 BEGIN
4500
4501 IF x_modif_type_h <> x_modif_type THEN
4502
4503 x_modif_type_h := x_modif_type;
4504 x_serial_number := 'XXXX';
4505 x_document_number := x_base_doc_number ||x_delimiter|| x_control_char||x_serial_number;
4506
4507 END IF;
4508
4509 x_return_status := FND_API.G_RET_STS_SUCCESS;
4510 x_errorcode := '0';
4511 x_msg_count := '1';
4512 x_msg_data := 'SUCCESS';
4513
4514 END dod_ord_mod_XLE;
4515
4516
4517 PROCEDURE dod_ord_mod_XDN
4518 (p_po_header_id IN NUMBER
4519 ,p_draft_id IN NUMBER
4520 ,x_base_doc_number IN OUT NOCOPY varchar2
4521 ,x_control_char IN OUT NOCOPY VARCHAR2
4522 ,x_serial_number IN OUT NOCOPY VARCHAR2
4523 ,x_delimiter IN OUT NOCOPY VARCHAR2
4524 ,x_document_number IN OUT NOCOPY VARCHAR2
4525 ,x_return_status OUT NOCOPY VARCHAR2
4526 ,x_errorcode OUT NOCOPY VARCHAR2
4527 ,x_msg_count OUT NOCOPY VARCHAR2
4528 ,x_msg_data OUT NOCOPY VARCHAR2
4529 )
4530 IS
4531
4532 l_query VARCHAR2(4000);
4533 l_cnt NUMBER;
4534
4535 BEGIN
4536 x_serial_number := Upper(x_serial_number);
4537 x_document_number := x_base_doc_number ||x_delimiter|| x_control_char||
4538 x_serial_number;
4539
4540 dod_ord_mod_XV
4541 (p_po_header_id
4542 ,p_draft_id
4543 ,x_base_doc_number
4544 ,x_control_char
4545 ,x_serial_number
4546 ,x_delimiter
4547 ,x_document_number
4548 ,x_return_status
4549 ,x_errorcode
4550 ,x_msg_count
4551 ,x_msg_data
4552 );
4553
4554 END dod_ord_mod_XDN;
4555
4556
4557 PROCEDURE dod_ord_mod_XPA
4558 (p_po_header_id IN NUMBER
4559 ,p_draft_id IN NUMBER
4560 ,x_base_doc_number IN OUT NOCOPY VARCHAR2
4561 ,x_control_char IN OUT NOCOPY VARCHAR2
4562 ,x_modif_type IN OUT NOCOPY VARCHAR2
4563 ,x_serial_number IN OUT NOCOPY VARCHAR2
4564 ,x_delimiter IN OUT NOCOPY VARCHAR2
4565 ,x_document_number IN OUT NOCOPY VARCHAR2
4566 ,x_modif_type_h IN OUT NOCOPY VARCHAR2
4567 ,x_return_status OUT NOCOPY VARCHAR2
4568 ,x_errorcode OUT NOCOPY VARCHAR2
4569 ,x_msg_count OUT NOCOPY VARCHAR2
4570 ,x_msg_data OUT NOCOPY VARCHAR2
4571 )
4572 IS
4573 BEGIN
4574 --First initialize the out variable to success
4575 x_return_status := FND_API.G_RET_STS_SUCCESS;
4576 x_errorcode := '0';
4577 x_msg_count := '1';
4578 x_msg_data := 'SUCCESS';
4579
4580 x_document_number := x_base_doc_number ||x_delimiter|| x_serial_number;
4581 END dod_ord_mod_XPA;
4582
4583
4584 PROCEDURE dod_ord_mod_XDA
4585 (p_po_header_id IN NUMBER
4586 ,p_draft_id IN NUMBER
4587 ,x_base_doc_number IN OUT NOCOPY VARCHAR2
4588 ,x_control_char IN OUT NOCOPY VARCHAR2
4589 ,x_modif_type IN OUT NOCOPY VARCHAR2
4590 ,x_serial_number IN OUT NOCOPY VARCHAR2
4591 ,x_delimiter IN OUT NOCOPY VARCHAR2
4592 ,x_document_number IN OUT NOCOPY VARCHAR2
4593 ,x_delimiter_ext OUT NOCOPY VARCHAR2
4594 ,x_return_status OUT NOCOPY VARCHAR2
4595 ,x_errorcode OUT NOCOPY VARCHAR2
4596 ,x_msg_count OUT NOCOPY VARCHAR2
4597 ,x_msg_data OUT NOCOPY VARCHAR2
4598 )
4599 IS
4600 BEGIN
4601
4602 x_return_status := FND_API.G_RET_STS_SUCCESS;
4603 x_errorcode := '0';
4604 x_msg_count := '1';
4605 x_msg_data := 'SUCCESS';
4606
4607 x_delimiter := '-';
4608 x_delimiter_ext := '-';
4609
4610 END dod_ord_mod_XDA;
4611
4612 PROCEDURE dod_awd_mod_XD
4613 (p_po_header_id IN NUMBER
4614 ,p_draft_id IN NUMBER
4615 ,x_base_doc_number IN OUT NOCOPY VARCHAR2
4616 ,x_control_char IN OUT NOCOPY VARCHAR2
4617 ,x_modif_type IN OUT NOCOPY VARCHAR2
4618 ,x_modif_code IN OUT NOCOPY VARCHAR2
4619 ,x_serial_number IN OUT NOCOPY VARCHAR2
4620 ,x_delimiter IN OUT NOCOPY VARCHAR2
4621 ,x_document_number IN OUT NOCOPY VARCHAR2
4622 ,x_return_status OUT NOCOPY VARCHAR2
4623 ,x_errorcode OUT NOCOPY VARCHAR2
4624 ,x_msg_count OUT NOCOPY VARCHAR2
4625 ,x_msg_data OUT NOCOPY VARCHAR2
4626 )
4627 IS
4628
4629 l_query VARCHAR2(4000);
4630 l_attr_grp_ids Dbms_Sql.number_table;
4631 l_where_cls VARCHAR2(100):='';
4632 l_num NUMBER;
4633 l_lookup_const VARCHAR2(50);
4634
4635 CURSOR C IS
4636 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
4637 from PO_UDA_AG_TEMPLATE_USAGES puatu,
4638 po_uda_ag_templates puat
4639 WHERE puat.ENTITY_CODE='PO_HEADER_EXT_ATTRS'
4640 AND puat.document_level='HEADER'
4641 AND puat.template_id = puatu.template_id
4642 AND puatu.attribute_category='DOCUMENT_NUMBERING'
4643 AND puatu.attribute1='Modification'
4644 AND puatu.attribute2='NA';
4645
4646 BEGIN
4647
4648 --First initialize the out variable to success
4649 x_return_status := FND_API.G_RET_STS_SUCCESS;
4650 x_errorcode := '0';
4651 x_msg_count := '1';
4652 x_msg_data := 'SUCCESS';
4653
4654 IF(x_base_doc_number IS NULL AND x_control_char IS NULL AND
4655 x_modif_type IS NULL AND x_modif_code IS NULL AND
4656 x_serial_number IS NULL AND x_document_number IS null) THEN
4657
4658
4659 SELECT CLM_DOCUMENT_NUMBER INTO x_base_doc_number
4660 FROM po_headers_all
4661 WHERE po_header_id = p_po_header_id;
4662
4663 x_control_char := 'C';
4664
4665 BEGIN
4666 SELECT lookup_code
4667 INTO x_modif_type
4668 FROM (
4669 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_MODIFICATION_TYPE'
4670 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
4671 WHERE ROWNUM=1;
4672 EXCEPTION
4673 WHEN No_Data_Found THEN
4674 x_return_status := FND_API.G_RET_STS_ERROR;
4675 x_errorcode := '-1';
4676 x_msg_count := '1';
4677 x_msg_data := 'Lookup PO_CLM_MODIFICATION_TYPE not found';
4678 fnd_message.set_name('PO', 'PO_CLM_MODIFICATION_TYPE_NVD');
4679 fnd_msg_pub.ADD;
4680 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
4681 RETURN;
4682 END;
4683
4684 BEGIN
4685 SELECT lookup_code
4686 INTO x_modif_code
4687 FROM (
4688 SELECT * FROM po_lookup_codes where lookup_type = 'PO_CLM_MODIFICATION_CODE'
4689 and enabled_flag = 'Y' and trunc(nvl(inactive_date, sysdate)) >= trunc(sysdate) order by lookup_code)
4690 WHERE ROWNUM=1;
4691 EXCEPTION
4692 WHEN No_Data_Found THEN
4693 x_return_status := FND_API.G_RET_STS_ERROR;
4694 x_errorcode := '-1';
4695 x_msg_count := '1';
4696 x_msg_data := 'Lookup PO_CLM_MODIFICATION_CODE not found';
4697 fnd_message.set_name('PO', 'PO_CLM_MODIFICATION_CODE_NVD');
4698 fnd_msg_pub.ADD;
4699 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
4700 RETURN;
4701 END;
4702
4703 x_delimiter := '-';
4704 x_serial_number := 'XXXX';
4705 x_document_number := x_base_doc_number ||x_delimiter||
4706 x_control_char||x_modif_code||x_serial_number;
4707
4708 ELSIF x_serial_number IS NULL OR x_serial_number = 'XXXX' THEN
4709
4710 OPEN C;
4711 FETCH C BULK COLLECT INTO l_attr_grp_ids;
4712 CLOSE C;
4713
4714 FOR i IN 1..l_attr_grp_ids.Count LOOP
4715 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
4716 END LOOP;
4717
4718 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
4719
4720 l_query := ' SELECT Max(To_Number(C_EXT_ATTR7)) '||
4721 ' FROM PO_HEADERS_ALL_EXT_B '||
4722 ' where C_EXT_ATTR1 ='''||x_base_doc_number||''' ' ||
4723 ' and ATTR_GROUP_ID in ('||l_where_cls||') '||
4724 ' and C_EXT_ATTR7 not like ''%X%'' ';
4725
4726 BEGIN
4727 EXECUTE IMMEDIATE l_query INTO l_num;
4728 IF l_num IS NULL THEN
4729 l_num :=0;
4730 END IF;
4731
4732 EXCEPTION
4733 WHEN No_Data_Found THEN
4734 l_num :=0;
4735 END;
4736
4737 x_serial_number := LPad(l_num+1,4,'0');
4738
4739 x_delimiter := '-';
4740 x_document_number := x_base_doc_number ||x_delimiter||
4741 x_control_char||x_modif_code||x_serial_number;
4742 END IF;
4743
4744 END dod_awd_mod_XD;
4745
4746
4747
4748 PROCEDURE dod_awd_mod_XV
4749 (p_po_header_id IN NUMBER
4750 ,p_draft_id IN NUMBER
4751 ,x_base_doc_number IN OUT NOCOPY varchar2
4752 ,x_control_char IN OUT NOCOPY VARCHAR2
4753 ,x_serial_number IN OUT NOCOPY VARCHAR2
4754 ,x_delimiter IN OUT NOCOPY VARCHAR2
4755 ,x_document_number IN OUT NOCOPY VARCHAR2
4756 ,x_return_status OUT NOCOPY VARCHAR2
4757 ,x_errorcode OUT NOCOPY VARCHAR2
4758 ,x_msg_count OUT NOCOPY VARCHAR2
4759 ,x_msg_data OUT NOCOPY VARCHAR2
4760 )
4761 IS
4762
4763 l_query VARCHAR2(4000);
4764 l_cnt NUMBER;
4765
4766 BEGIN
4767 --First initialize the out variable to success
4768 x_return_status := FND_API.G_RET_STS_SUCCESS;
4769 x_errorcode := '0';
4770 x_msg_count := '1';
4771 x_msg_data := 'SUCCESS';
4772
4773 IF x_base_doc_number IS NULL OR x_base_doc_number='' OR
4774 x_control_char IS NULL OR x_control_char='' THEN
4775
4776 x_return_status := FND_API.G_RET_STS_ERROR;
4777 x_errorcode := '-1';
4778 x_msg_count := '1';
4779 x_msg_data := 'All the fields are compulsory, please enter the value for all the Attributes';
4780 fnd_message.set_name('PO', 'PO_DOC_NUM_ALL_FIELDS_COMPUL');
4781 fnd_msg_pub.ADD;
4782 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
4783 END IF;
4784
4785 IF x_serial_number IS NOT NULL AND Upper(x_serial_number) <> 'XXXX' THEN
4786 select count(*) INTO l_cnt
4787 from PO_HEADERS_ALL_EXT_B
4788 WHERE C_EXT_ATTR40 = x_document_number
4789 AND draft_id <> p_draft_id
4790 AND po_header_id <> p_po_header_id;
4791
4792 IF l_cnt > 0 THEN
4793 x_return_status := FND_API.G_RET_STS_ERROR;
4794 x_errorcode := '-1';
4795 x_msg_count := '1';
4796 x_msg_data := 'Document Number is not Unique';
4797 fnd_message.set_name('PO', 'PO_DOC_NUM_NOT_UNIQUE');
4798 fnd_msg_pub.ADD;
4799 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data);
4800 END IF;
4801 END IF;
4802
4803 END dod_awd_mod_XV;
4804
4805
4806 PROCEDURE dod_awd_mod_XDN
4807 (p_po_header_id IN NUMBER
4808 ,p_draft_id IN NUMBER
4809 ,x_base_doc_number IN OUT NOCOPY varchar2
4810 ,x_control_char IN OUT NOCOPY VARCHAR2
4811 ,x_modif_type IN OUT NOCOPY VARCHAR2
4812 ,x_modif_code IN OUT NOCOPY VARCHAR2
4813 ,x_serial_number IN OUT NOCOPY VARCHAR2
4814 ,x_delimiter IN OUT NOCOPY VARCHAR2
4815 ,x_document_number IN OUT NOCOPY VARCHAR2
4816 ,x_return_status OUT NOCOPY VARCHAR2
4817 ,x_errorcode OUT NOCOPY VARCHAR2
4818 ,x_msg_count OUT NOCOPY VARCHAR2
4819 ,x_msg_data OUT NOCOPY VARCHAR2
4820 )
4821 IS
4822
4823 l_query VARCHAR2(4000);
4824 l_cnt NUMBER;
4825
4826 BEGIN
4827 x_serial_number := Upper(x_serial_number);
4828 x_document_number := x_base_doc_number ||x_delimiter||
4829 x_control_char||x_modif_code||x_serial_number;
4830
4831 dod_awd_mod_XV
4832 (p_po_header_id
4833 ,p_draft_id
4834 ,x_base_doc_number
4835 ,x_control_char
4836 ,x_serial_number
4837 ,x_delimiter
4838 ,x_document_number
4839 ,x_return_status
4840 ,x_errorcode
4841 ,x_msg_count
4842 ,x_msg_data
4843 );
4844
4845 END dod_awd_mod_XDN;
4846
4847
4848 PROCEDURE dod_awd_mod_XPA
4849 (p_po_header_id IN NUMBER
4850 ,p_draft_id IN NUMBER
4851 ,x_base_doc_number IN OUT NOCOPY VARCHAR2
4852 ,x_control_char IN OUT NOCOPY VARCHAR2
4853 ,x_modif_type IN OUT NOCOPY VARCHAR2
4854 ,x_modif_code IN OUT NOCOPY VARCHAR2
4855 ,x_serial_number IN OUT NOCOPY VARCHAR2
4856 ,x_delimiter IN OUT NOCOPY VARCHAR2
4857 ,x_document_number IN OUT NOCOPY VARCHAR2
4858 ,x_return_status OUT NOCOPY VARCHAR2
4859 ,x_errorcode OUT NOCOPY VARCHAR2
4860 ,x_msg_count OUT NOCOPY VARCHAR2
4861 ,x_msg_data OUT NOCOPY VARCHAR2
4862 ,x_appr_ser_num IN OUT NOCOPY VARCHAR2
4863 )
4864 IS
4865
4866 l_query VARCHAR2(4000);
4867 l_attr_grp_ids Dbms_Sql.number_table;
4868 l_where_cls VARCHAR2(100):='';
4869 l_num NUMBER;
4870 l_uca_special_character VARCHAR2(1); -- UCA Changes - CLM R4
4871 l_isUCAEnabled VARCHAR2(1);
4872
4873 CURSOR C IS
4874 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
4875 from PO_UDA_AG_TEMPLATE_USAGES puatu,
4876 po_uda_ag_templates puat
4877 WHERE puat.ENTITY_CODE='PO_HEADER_EXT_ATTRS'
4878 AND puat.document_level='HEADER'
4879 AND puat.template_id = puatu.template_id
4880 AND puatu.attribute_category='DOCUMENT_NUMBERING'
4881 AND puatu.attribute1='Modification'
4882 AND puatu.attribute2='NA';
4883
4884 BEGIN
4885 --First initialize the out variable to success
4886 x_return_status := FND_API.G_RET_STS_SUCCESS;
4887 x_errorcode := '0';
4888 x_msg_count := '1';
4889 x_msg_data := 'SUCCESS';
4890
4891 OPEN C;
4892 FETCH C BULK COLLECT INTO l_attr_grp_ids;
4893 CLOSE C;
4894
4895 FOR i IN 1..l_attr_grp_ids.Count LOOP
4896 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
4897 END LOOP;
4898
4899 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
4900
4901 l_query :=' SELECT Max(To_Number(C_EXT_ATTR13)) '||
4902 ' FROM PO_HEADERS_ALL_EXT_B '||
4903 ' where C_EXT_ATTR1 ='''||x_base_doc_number||''' ' ||
4904 ' and C_EXT_ATTR10 = '''||x_modif_type||''' ' ||
4905 ' and ATTR_GROUP_ID in ('||l_where_cls||') ';
4906
4907 BEGIN
4908 EXECUTE IMMEDIATE l_query INTO l_num;
4909 IF l_num IS NULL THEN
4910 l_num :=0;
4911 END IF;
4912
4913 EXCEPTION
4914 WHEN No_Data_Found THEN
4915 l_num :=0;
4916 END;
4917
4918 x_appr_ser_num := LPad(l_num+1,5,'0'); --Bug 13906383 - approved modification document to have 5 digit serial number
4919 --UCA Project CLM R4 Changes Begin.
4920 SELECT
4921 Nvl(po_core_s.Retrieveoptionvalue(org_id,
4922 po_core_s.g_undef_cont_act_col),
4923 'N')
4924 INTO l_isUCAEnabled
4925 FROM po_headers_merge_v
4926 WHERE po_header_id = p_po_header_id
4927 AND draft_id = p_draft_id;
4928 IF l_isUCAEnabled = 'Y' THEN
4929 BEGIN
4930 SELECT 'Z'
4931 INTO l_uca_special_character
4932 FROM po_line_ucas
4933 WHERE po_header_id = p_po_header_id
4934 AND def_draft_id = p_draft_id
4935 AND ROWNUM = 1;
4936 EXCEPTION
4937 WHEN NO_DATA_FOUND THEN
4938 l_uca_special_character :='';
4939 END;
4940 END IF;
4941
4942 --UCA Project CLM R4 Changes End.
4943 IF x_modif_type = 'ACO' THEN
4944 x_document_number := x_base_doc_number ||x_delimiter||
4945 'A'||l_uca_special_character||x_appr_ser_num; --UCA Change
4946 ELSIF x_modif_type = 'PCO' THEN
4947 x_document_number := x_base_doc_number ||x_delimiter||
4948 'P'||l_uca_special_character||x_appr_ser_num; --UCA Change
4949 END IF;
4950
4951 END dod_awd_mod_XPA;
4952
4953
4954 PROCEDURE dod_awd_mod_XDA
4955 (p_po_header_id IN NUMBER
4956 ,p_draft_id IN NUMBER
4957 ,x_base_doc_number IN OUT NOCOPY VARCHAR2
4958 ,x_control_char IN OUT NOCOPY VARCHAR2
4959 ,x_modif_type IN OUT NOCOPY VARCHAR2
4960 ,x_modif_code IN OUT NOCOPY VARCHAR2
4961 ,x_serial_number IN OUT NOCOPY VARCHAR2
4962 ,x_delimiter IN OUT NOCOPY VARCHAR2
4963 ,x_document_number IN OUT NOCOPY VARCHAR2
4964 ,x_delimiter_ext OUT NOCOPY VARCHAR2
4965 ,x_return_status OUT NOCOPY VARCHAR2
4966 ,x_errorcode OUT NOCOPY VARCHAR2
4967 ,x_msg_count OUT NOCOPY VARCHAR2
4968 ,x_msg_data OUT NOCOPY VARCHAR2
4969 )
4970 IS
4971 BEGIN
4972
4973 x_return_status := FND_API.G_RET_STS_SUCCESS;
4974 x_errorcode := '0';
4975 x_msg_count := '1';
4976 x_msg_data := 'SUCCESS';
4977
4978 x_delimiter := '-';
4979 x_delimiter_ext := '-';
4980
4981 END dod_awd_mod_XDA;
4982
4983
4984 PROCEDURE process_dod_awd_mod_doc_num
4985 (
4986 p_po_header_id IN NUMBER,
4987 p_po_draft_id IN NUMBER,
4988 p_uda_template_id IN NUMBER,
4989 p_ag_type IN varchar2
4990 )
4991 IS
4992
4993 d_api_name CONSTANT VARCHAR2(30) := 'process_dod_awd_mod_doc_num';
4994 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
4995 d_progress NUMBER := 0;
4996
4997 l_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
4998 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
4999
5000 x_return_status VARCHAR2(1);
5001 x_errorcode NUMBER;
5002 x_msg_count NUMBER;
5003 x_msg_data VARCHAR2(100);
5004
5005 l_failed_row_id_list VARCHAR2(1000);
5006 l_doc_number VARCHAR2(50);
5007 l_attr_list VARCHAR2(2000);
5008 l_attr_group_id NUMBER;
5009 l_attr_group_name VARCHAR2(30);
5010 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
5011 l_src_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
5012 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
5013 l_external_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE;
5014 l_src_object_name VARCHAR2(200);
5015 l_src_data_level VARCHAR2(100) := 'PO_HEADER';
5016
5017 CURSOR c_attr_list(p_ag_type VARCHAR2, p_ag_name VARCHAR2) IS
5018 SELECT END_USER_COLUMN_NAME
5019 FROM FND_DESCR_FLEX_COLUMN_USAGES
5020 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_ag_type
5021 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_ag_name;
5022
5023 BEGIN
5024
5025 SELECT puatu.ATTRIBUTE_GROUP_ID, ags.ATTR_GROUP_NAME
5026 INTO l_attr_group_id, l_attr_group_name
5027 FROM PO_UDA_AG_TEMPLATE_USAGES puatu,
5028 po_uda_ag_templates puat,
5029 ego_attr_groups_v ags,
5030 po_headers_all_ext_b phaeb
5031 WHERE puat.ENTITY_CODE=p_ag_type
5032 AND phaeb.po_header_id = p_po_header_id
5033 AND phaeb.draft_id = p_po_draft_id
5034 AND phaeb.attr_group_id = puatu.attribute_group_id
5035 AND puat.document_level='HEADER'
5036 AND puat.template_id = p_uda_template_id
5037 AND puat.template_id = puatu.template_id
5038 AND puatu.attribute_category='DOCUMENT_NUMBERING'
5039 AND puatu.attribute1='Modification'
5040 AND puatu.attribute_group_id = ags.attr_group_id;
5041
5042 l_attr_list := '';
5043 FOR l_attr_list_rec IN c_attr_list(p_ag_type,l_attr_group_name)
5044 LOOP
5045 IF l_attr_list IS NULL OR l_attr_list = '' THEN
5046 l_attr_list := l_attr_list_rec.END_USER_COLUMN_NAME;
5047 ELSE
5048 l_attr_list := l_attr_list || ',' || l_attr_list_rec.END_USER_COLUMN_NAME;
5049 END IF;
5050 END LOOP;
5051
5052 l_src_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
5053 EGO_COL_NAME_VALUE_PAIR_OBJ(
5054 'PO_HEADER_ID', p_po_header_id
5055 ),
5056 EGO_COL_NAME_VALUE_PAIR_OBJ(
5057 'DRAFT_ID', p_po_draft_id
5058 )
5059 );
5060
5061 l_attr_group_request_table := EGO_ATTR_GROUP_REQUEST_TABLE();
5062 l_attr_group_request_table.EXTEND(1);
5063 l_attr_group_request_table(1) := ego_attr_group_request_obj
5064 (
5065 l_attr_group_id
5066 ,201 -- application id is always 201
5067 ,p_ag_type -- p_ag_type
5068 ,l_attr_group_name
5069 ,l_src_data_level
5070 ,NULL
5071 ,NULL
5072 ,NULL
5073 ,NULL
5074 ,NULL
5075 ,l_attr_list
5076 );
5077
5078 l_src_object_name := PO_UDA_DATA_UTIL.g_object_dff_tl(p_ag_type).l_object_name;
5079
5080 ego_user_attrs_data_pvt.get_user_attrs_data(
5081 p_api_version => 1.0
5082 ,p_object_name => l_src_object_name --Get it from PO_UDA_AG_TEMPLATES
5083 ,p_pk_column_name_value_pairs => l_src_pk_col_name_val_pairs--Source Primary Keys
5084 ,p_attr_group_request_table => l_attr_group_request_table --Construct the Attribute Group Table
5085 ,x_attributes_row_table => l_attributes_row_table
5086 ,x_attributes_data_table => l_attributes_data_table
5087 ,x_return_status => x_return_status
5088 ,x_errorcode => x_errorcode
5089 ,x_msg_count => x_msg_count
5090 ,x_msg_data => x_msg_data
5091 );
5092
5093 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', FND_API.G_RET_STS_SUCCESS, l_external_attr_value_pairs);
5094 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_errorcode', '0', l_external_attr_value_pairs);
5095 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_count', '0', l_external_attr_value_pairs);
5096 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_data', 'SUCCESS', l_external_attr_value_pairs);
5097
5098 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
5099 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
5100 (
5101 p_template_id => p_uda_template_id
5102 ,p_event => 'XPA'
5103 ,x_external_attr_value_pairs => l_external_attr_value_pairs
5104 ,x_pk_column_name_value_pairs => l_src_pk_col_name_val_pairs
5105 ,x_attr_name_value_pairs => l_attributes_data_table
5106 ,x_attributes_row_table => l_attributes_row_table
5107 ,x_return_status => x_return_status
5108 ,x_errorcode => x_errorcode
5109 ,x_msg_count => x_msg_count
5110 ,x_msg_data => x_msg_data
5111 );
5112 END IF;
5113
5114 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
5115 (
5116 EGO_COL_NAME_VALUE_PAIR_OBJ
5117 (
5118 'UDA_TEMPLATE_ID'
5119 ,p_uda_template_id || ''
5120 )
5121 );
5122
5123 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
5124 EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data
5125 (
5126 p_api_version => 1.0
5127 ,p_object_name => l_src_object_name
5128 ,p_attributes_row_table => l_attributes_row_table
5129 ,p_attributes_data_table => l_attributes_data_table
5130 ,p_pk_column_name_value_pairs => l_src_pk_col_name_val_pairs
5131 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
5132 ,x_failed_row_id_list => l_failed_row_id_list
5133 ,x_return_status => x_return_status
5134 ,x_errorcode => x_errorcode
5135 ,x_msg_count => x_msg_count
5136 ,x_msg_data => x_msg_data
5137 );
5138 END IF;
5139
5140 --Now get the c_ext_attr40 update the modification_number
5141 SELECT c_ext_attr40
5142 INTO l_doc_number
5143 FROM po_headers_all_ext_b
5144 WHERE po_header_id = p_po_header_id
5145 AND draft_id = p_po_draft_id
5146 AND attr_group_id = l_attr_group_id;
5147
5148 UPDATE po_drafts
5149 SET modification_number = l_doc_number
5150 WHERE draft_id = p_po_draft_id;
5151
5152 --UCA Changes CLMR 4 changes Begin.
5153 --Stamping uca mod number to the updated Mod number after approval
5154 UPDATE po_line_ucas
5155 SET undef_clm_document_number = l_doc_number
5156 WHERE po_header_id = p_po_header_id
5157 AND undef_draft_id = p_po_draft_id;
5158 --UCA Changes CLMR 4 Changes End.
5159
5160 COMMIT;
5161
5162 EXCEPTION
5163 WHEN OTHERS THEN
5164 x_return_status := 'U';
5165 IF PO_LOG.d_stmt THEN
5166 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
5167 PO_LOG.stmt(d_module, d_progress, 'error_message', SQLERRM);
5168 END IF;
5169
5170 END process_dod_awd_mod_doc_num;
5171
5172
5173 FUNCTION get_owned_by_issuing_org(p_idv_number IN NUMBER) RETURN VARCHAR2
5174 IS
5175 l_user_def_dodaac VARCHAR2(30);
5176 l_source_doc_number VARCHAR2(50);
5177 BEGIN
5178
5179 IF p_idv_number IS NULL THEN
5180 RETURN 'NA';
5181 END IF;
5182
5183 SELECT clm_document_number
5184 INTO l_source_doc_number
5185 FROM po_headers_all
5186 WHERE po_header_id = p_idv_number;
5187
5188 --Get user's default DoDAAC
5189 BEGIN
5190 SELECT PEI_INFORMATION2
5191 INTO l_user_def_dodaac
5192 FROM per_people_extra_info pei, fnd_user fu
5193 WHERE pei.information_type = 'CLM_CONTACT_TITLE'
5194 AND pei.person_id= fu.employee_id
5195 AND fu.user_id = fnd_global.user_id;
5196 EXCEPTION
5197 WHEN OTHERS THEN
5198 RETURN 'N';
5199 END;
5200
5201 IF SubStr(l_source_doc_number,1,6) = l_user_def_dodaac THEN
5202 RETURN 'Y';
5203 ELSE
5204 RETURN 'N';
5205 END IF;
5206
5207 END get_owned_by_issuing_org;
5208
5209 FUNCTION get_delimiter_for_doc(p_po_header_id IN NUMBER,
5210 p_draft_id IN NUMBER
5211 ) RETURN VARCHAR2
5212 IS
5213
5214 d_api_name CONSTANT VARCHAR2(30) := 'get_delimiter_for_doc';
5215 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
5216 d_progress NUMBER := 0;
5217
5218 l_return_status VARCHAR2(2);
5219 l_errorcode NUMBER;
5220 l_msg_count NUMBER;
5221 l_msg_data VARCHAR2(4000);
5222
5223 l_template_id NUMBER;
5224 l_clm_source_document_id NUMBER;
5225 l_context_usage VARCHAR2(25) := 'Base Document';
5226 l_source_org_owned VARCHAR2(2) :='NA';
5227 l_attr_group_id NUMBER;
5228 l_attr_group_name VARCHAR2(30);
5229 l_src_object_name VARCHAR2(200);
5230 l_src_data_level VARCHAR2(100) := 'PO_HEADER';
5231 l_ag_type VARCHAR2(50) := 'PO_HEADER_EXT_ATTRS';
5232
5233 l_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
5234 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
5235 l_attr_list VARCHAR2(2000);
5236 l_attrb_grp_id NUMBER;
5237
5238 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
5239 l_src_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
5240 l_external_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE;
5241 --bug 13054072
5242 l_org_id NUMBER;
5243
5244 CURSOR c_attr_list(p_ag_type VARCHAR2, p_ag_name VARCHAR2) IS
5245 SELECT END_USER_COLUMN_NAME
5246 FROM FND_DESCR_FLEX_COLUMN_USAGES
5247 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_ag_type
5248 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_ag_name;
5249
5250 l_ext_name_val_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
5251 l_delimiter VARCHAR2(2);
5252
5253 BEGIN
5254
5255 SELECT uda_template_id, clm_source_document_id, org_id
5256 INTO l_template_id, l_clm_source_document_id, l_org_id
5257 FROM po_headers_all
5258 WHERE po_header_id = p_po_header_id;
5259
5260 l_source_org_owned := get_owned_by_issuing_org(l_clm_source_document_id);
5261
5262 --If draft_id is populated then this is a modification case
5263 IF p_draft_id IS NOT NULL AND p_draft_id <> -1 THEN
5264 SELECT uda_template_id INTO l_template_id
5265 FROM po_headers_draft_all
5266 WHERE po_header_id = p_po_header_id
5267 AND draft_id = p_draft_id;
5268
5269 l_context_usage := 'Modification';
5270
5271 --For MOD, if clm_source_document_id is populated then
5272 --source_org_owned is always 'Y' irrespective of owned or not owned case.
5273 IF l_clm_source_document_id IS NOT NULL THEN
5274 l_source_org_owned :='Y';
5275 ELSE
5276 l_source_org_owned :='NA';
5277 END IF;
5278
5279 END IF;
5280
5281 -- bug 13054072
5282 BEGIN
5283
5284 SELECT usages.ATTRIBUTE_GROUP_ID , ags.ATTR_GROUP_NAME
5285 INTO l_attrb_grp_id, l_attr_group_name
5286 FROM PO_UDA_AG_TEMPLATE_USAGES usages, ego_attr_groups_v ags
5287 WHERE usages.template_id = l_template_id
5288 AND usages.ATTRIBUTE1=l_context_usage
5289 AND usages.ATTRIBUTE2=l_source_org_owned
5290 -- bug 13054072 Attribute3 needs to be added here as there may be records that have org_id as well as DEFAULT values in Attribute3 column
5291 AND usages.ATTRIBUTE3=To_Char(l_org_id)
5292 AND usages.ATTRIBUTE_CATEGORY='DOCUMENT_NUMBERING'
5293 AND usages.attribute_group_id = ags.attr_group_id;
5294
5295 -- bug 13054072 : No_data_Found exception needs to be handled if Attribute3 column does not have org_id i.e. Attribute3 has DEFAULT value
5296 EXCEPTION
5297 WHEN No_Data_Found THEN
5298 --If there is no org_id fetched from po_headers_all
5299
5300 SELECT usages.ATTRIBUTE_GROUP_ID , ags.ATTR_GROUP_NAME
5301 INTO l_attrb_grp_id, l_attr_group_name
5302 FROM PO_UDA_AG_TEMPLATE_USAGES usages, ego_attr_groups_v ags
5303 WHERE usages.template_id = l_template_id
5304 AND usages.ATTRIBUTE1=l_context_usage
5305 AND usages.ATTRIBUTE2=l_source_org_owned
5306 AND usages.ATTRIBUTE3='DEFAULT'
5307 AND usages.ATTRIBUTE_CATEGORY='DOCUMENT_NUMBERING'
5308 AND usages.attribute_group_id = ags.attr_group_id;
5309
5310 END;
5311 -- bug 13054072 ends
5312
5313 l_src_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
5314 EGO_COL_NAME_VALUE_PAIR_OBJ(
5315 'PO_HEADER_ID', p_po_header_id
5316 ),
5317 EGO_COL_NAME_VALUE_PAIR_OBJ(
5318 'DRAFT_ID', Nvl(p_draft_id,-1)
5319 )
5320 );
5321
5322 l_attr_list := '';
5323 FOR l_attr_list_rec IN c_attr_list(l_ag_type,l_attr_group_name)
5324 LOOP
5325 IF l_attr_list IS NULL OR l_attr_list = '' THEN
5326 l_attr_list := l_attr_list_rec.END_USER_COLUMN_NAME;
5327 ELSE
5328 l_attr_list := l_attr_list || ',' || l_attr_list_rec.END_USER_COLUMN_NAME;
5329 END IF;
5330 END LOOP;
5331
5332 l_attr_group_request_table := EGO_ATTR_GROUP_REQUEST_TABLE();
5333 l_attr_group_request_table.EXTEND(1);
5334 l_attr_group_request_table(1) := ego_attr_group_request_obj
5335 (
5336 l_attrb_grp_id
5337 ,201 -- application id is always 201
5338 ,l_ag_type
5339 ,l_attr_group_name
5340 ,l_src_data_level
5341 ,NULL
5342 ,NULL
5343 ,NULL
5344 ,NULL
5345 ,NULL
5346 ,l_attr_list
5347 );
5348
5349
5350 l_src_object_name := PO_UDA_DATA_UTIL.g_object_dff_tl(l_ag_type).l_object_name;
5351
5352 ego_user_attrs_data_pvt.get_user_attrs_data(
5353 p_api_version => 1.0
5354 ,p_object_name => l_src_object_name --Get it from PO_UDA_AG_TEMPLATES
5355 ,p_pk_column_name_value_pairs => l_src_pk_col_name_val_pairs--Source Primary Keys
5356 ,p_attr_group_request_table => l_attr_group_request_table --Construct the Attribute Group Table
5357 ,x_attributes_row_table => l_attributes_row_table
5358 ,x_attributes_data_table => l_attributes_data_table
5359 ,x_return_status => l_return_status
5360 ,x_errorcode => l_errorcode
5361 ,x_msg_count => l_msg_count
5362 ,x_msg_data => l_msg_data
5363 );
5364
5365
5366 l_external_attr_value_pairs :=NULL;
5367 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', FND_API.G_RET_STS_SUCCESS, l_external_attr_value_pairs);
5368 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_errorcode', '0', l_external_attr_value_pairs);
5369 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_count', '0', l_external_attr_value_pairs);
5370 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_data', 'SUCCESS', l_external_attr_value_pairs);
5371
5372 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
5373 PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION
5374 (
5375 p_template_id => l_template_id
5376 ,p_event => 'XDA'
5377 ,x_external_attr_value_pairs => l_external_attr_value_pairs
5378 ,x_pk_column_name_value_pairs => l_src_pk_col_name_val_pairs
5379 ,x_attr_name_value_pairs => l_attributes_data_table
5380 ,x_attributes_row_table => l_attributes_row_table
5381 ,x_return_status => l_return_status
5382 ,x_errorcode => l_errorcode
5383 ,x_msg_count => l_msg_count
5384 ,x_msg_data => l_msg_data
5385 );
5386 END IF;
5387
5388 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
5389 PO_UDA_DEFAULTING_PKG.GET_EXT_ATTR_NAME_VALUE_PAIR('DELIMITER', l_external_attr_value_pairs, l_ext_name_val_obj);
5390 l_delimiter := l_ext_name_val_obj.VALUE ;
5391 --ELSE
5392 --l_delimiter := '-';
5393 END IF;
5394
5395 --right now hard coded to return '-'
5396 RETURN l_delimiter;
5397
5398 END get_delimiter_for_doc;
5399
5400 --Mod Commercialization
5401 --This function is used to generate the Modification Number for a non-CLM Mod
5402 FUNCTION default_mod_number(p_document_id IN NUMBER)
5403 RETURN VARCHAR2
5404 IS
5405 l_latest_mod_number VARCHAR2(30);
5406 l_default_mod_number VARCHAR2(30);
5407 l_seperator_position NUMBER;
5408 l_base_po_number VARCHAR2(20);
5409 l_mod_sequence_number NUMBER;
5410 BEGIN
5411 SELECT MODIFICATION_NUMBER
5412 INTO l_latest_mod_number
5413 FROM po_drafts
5414 WHERE DRAFT_ID = (SELECT Max(DRAFT_ID)
5415 FROM po_drafts
5416 WHERE DOCUMENT_ID = p_document_id);
5417
5418 --If a Modification already exists for the document
5419 l_seperator_position := instr(l_latest_mod_number, '-');
5420 l_base_po_number := SubStr(l_latest_mod_number,1,l_seperator_position-1);
5421 l_mod_sequence_number := To_Number(SubStr(l_latest_mod_number, l_seperator_position+1));
5422 l_default_mod_number := l_base_po_number || '-' || (l_mod_sequence_number+1) ;
5423
5424 RETURN l_default_mod_number;
5425
5426 EXCEPTION
5427 WHEN No_Data_Found THEN
5428 --If this is the First Modification of a document
5429 SELECT SEGMENT1
5430 INTO l_base_po_number
5431 FROM PO_HEADERS_ALL
5432 WHERE PO_HEADER_ID = p_document_id;
5433
5434 l_default_mod_number := l_base_po_number || '-' || '1';
5435 RETURN l_default_mod_number;
5436 WHEN OTHERS THEN
5437 RAISE;
5438
5439 END default_mod_number;
5440
5441
5442 --<PAR Project>
5443
5444 PROCEDURE awd_par_XD
5445 (p_po_header_id IN NUMBER
5446 ,p_draft_id IN NUMBER
5447 ,x_base_doc_number IN OUT NOCOPY VARCHAR2
5448 ,x_serial_number IN OUT NOCOPY VARCHAR2
5449 ,x_delimiter IN OUT NOCOPY VARCHAR2
5450 ,x_document_number IN OUT NOCOPY VARCHAR2
5451 ,x_document_type IN OUT NOCOPY VARCHAR2
5452 ,x_owning_org IN OUT NOCOPY VARCHAR2
5453 ,x_doc_sub_type IN OUT NOCOPY VARCHAR2
5454 ,x_source_doc_number IN OUT NOCOPY VARCHAR2
5455 ,x_style_id IN OUT NOCOPY NUMBER
5456 ,x_org_id IN OUT NOCOPY NUMBER
5457 ,x_return_status OUT NOCOPY VARCHAR2
5458 ,x_errorcode OUT NOCOPY VARCHAR2
5459 ,x_msg_count OUT NOCOPY VARCHAR2
5460 ,x_msg_data OUT NOCOPY VARCHAR2
5461 )
5462 IS
5463
5464 d_api_name CONSTANT VARCHAR2(30) := 'awd_par_XD';
5465 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
5466 d_progress NUMBER := 0;
5467 l_query VARCHAR2(4000);
5468 l_attr_grp_ids Dbms_Sql.number_table;
5469 l_where_cls VARCHAR2(100):='';
5470 l_num NUMBER;
5471 l_lookup_const VARCHAR2(50);
5472 l_src_doc_id NUMBER := NULL;
5473 l_identifier VARCHAR2(3) := 'PAR';
5474
5475 CURSOR c_attr_grp_id IS
5476 SELECT DISTINCT puatu.ATTRIBUTE_GROUP_ID
5477 from PO_UDA_AG_TEMPLATE_USAGES puatu,
5478 po_uda_ag_templates puat
5479 WHERE puat.ENTITY_CODE='PO_HEADER_EXT_ATTRS'
5480 AND puat.document_level='HEADER'
5481 AND puat.template_id = puatu.template_id
5482 AND puatu.attribute_category='DOCUMENT_NUMBERING'
5483 AND puatu.attribute1='PAR'
5484 AND puatu.attribute2='NA';
5485
5486 BEGIN
5487
5488 IF (PO_LOG.d_proc) THEN
5489 PO_LOG.proc_begin(d_module);
5490 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
5491 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
5492 PO_LOG.proc_begin(d_module, 'x_base_doc_number', x_base_doc_number);
5493 PO_LOG.proc_begin(d_module, 'x_serial_number', x_serial_number);
5494 PO_LOG.proc_begin(d_module, 'x_delimiter', x_delimiter);
5495 PO_LOG.proc_begin(d_module, 'x_document_number', x_document_number);
5496 END IF;
5497 --First initialize the out variable to success
5498 x_return_status := FND_API.G_RET_STS_SUCCESS;
5499 x_errorcode := '0';
5500 x_msg_count := '1';
5501 x_msg_data := 'SUCCESS';
5502
5503 d_progress := 10;
5504 IF(x_base_doc_number is NULL AND x_serial_number IS NULL
5505 AND x_document_number IS null) THEN
5506
5507 d_progress := 20;
5508
5509 SELECT CLM_DOCUMENT_NUMBER, TYPE_LOOKUP_CODE, ORG_ID, CLM_SOURCE_DOCUMENT_ID, STYLE_ID
5510 INTO x_base_doc_number, x_doc_sub_type, x_org_id, l_src_doc_id, x_style_id
5511 FROM po_headers_all
5512 WHERE po_header_id = p_po_header_id;
5513
5514 IF l_src_doc_id IS NOT NULL
5515 THEN
5516 BEGIN
5517 SELECT CLM_DOCUMENT_NUMBER
5518 INTO x_source_doc_number
5519 FROM po_headers_all
5520 WHERE po_header_id = l_src_doc_id;
5521
5522 EXCEPTION
5523 WHEN No_Data_Found THEN
5524 x_source_doc_number := NULL;
5525 END;
5526 END IF;
5527
5528 IF PO_LOG.d_stmt THEN
5529 PO_LOG.stmt(d_module, d_progress, 'x_base_doc_number : ', x_base_doc_number);
5530 END IF;
5531
5532 d_progress := 30;
5533
5534 x_delimiter := '-';
5535 x_serial_number := 'XXXX';
5536 x_document_type := 'PAR';
5537 x_owning_org := 'NA';
5538
5539 x_document_number := x_base_doc_number ||x_delimiter||l_identifier||x_delimiter
5540 ||x_serial_number;
5541
5542 IF PO_LOG.d_stmt THEN
5543 PO_LOG.stmt(d_module, d_progress, 'x_document_number : ', x_document_number);
5544 END IF;
5545
5546 ELSIF x_serial_number IS NULL OR x_serial_number = 'XXXX' THEN
5547
5548 d_progress := 40;
5549
5550 OPEN c_attr_grp_id;
5551 FETCH c_attr_grp_id BULK COLLECT INTO l_attr_grp_ids;
5552 CLOSE c_attr_grp_id;
5553
5554 d_progress := 50;
5555
5556 FOR i IN 1..l_attr_grp_ids.Count LOOP
5557 l_where_cls := l_where_cls||l_attr_grp_ids(i)||',';
5558 END LOOP;
5559
5560 d_progress := 60;
5561
5562 l_where_cls := SubStr(l_where_cls,1, Length(l_where_cls)-1);
5563
5564 l_query := ' SELECT Max(To_Number(C_EXT_ATTR7)) '||
5565 ' FROM PO_HEADERS_ALL_EXT_B '||
5566 ' where C_EXT_ATTR1 ='''||x_base_doc_number||''' ' ||
5567 ' and ATTR_GROUP_ID in ('||l_where_cls||') '||
5568 ' and C_EXT_ATTR7 not like ''%X%'' ';
5569
5570 IF PO_LOG.d_stmt THEN
5571 PO_LOG.stmt(d_module, d_progress, 'l_query : ', l_query);
5572 END IF;
5573
5574 BEGIN
5575 d_progress := 70;
5576
5577 EXECUTE IMMEDIATE l_query INTO l_num;
5578
5579 IF PO_LOG.d_stmt THEN
5580 PO_LOG.stmt(d_module, d_progress, 'l_num : ', l_num);
5581 END IF;
5582
5583 IF l_num IS NULL THEN
5584 d_progress := 80;
5585 l_num :=0;
5586
5587 IF PO_LOG.d_stmt THEN
5588 PO_LOG.stmt(d_module, d_progress, 'l_num : ', l_num);
5589 END IF;
5590
5591 END IF;
5592
5593 EXCEPTION
5594 WHEN No_Data_Found THEN
5595 d_progress := 90;
5596 l_num :=0;
5597 IF PO_LOG.d_exc THEN
5598 PO_LOG.exc(d_module, d_progress);
5599 END IF;
5600 END;
5601
5602 d_progress := 100;
5603
5604 x_serial_number := LPad(l_num+1,4,'0');
5605 x_delimiter := '-';
5606 x_document_type := 'PAR';
5607 x_owning_org := 'NA';
5608
5609 x_document_number := x_base_doc_number ||x_delimiter||l_identifier||x_delimiter
5610 ||x_serial_number;
5611
5612 IF PO_LOG.d_stmt THEN
5613 PO_LOG.stmt(d_module, d_progress, 'x_document_number : ', x_document_number);
5614 END IF;
5615
5616 END IF;
5617
5618 IF (PO_LOG.d_proc) THEN
5619 PO_LOG.proc_end(d_module);
5620 END IF;
5621
5622 END awd_par_XD;
5623
5624
5625 --<Bug 15835524>
5626 /* ============================================================================
5627 NAME: populate_par_elins
5628 DESC: This procedure syncs back the document number change done through doc
5629 numbering popup to underlying bids.
5630 ARGS: p_clm_document_number New Doument Number
5631 p_document_id po_header_id for the doument
5632 ===============================================================================*/
5633 PROCEDURE sync_doc_num_change_to_bids
5634 ( p_document_id IN NUMBER,
5635 p_clm_document_number IN VARCHAR2
5636 )
5637 IS
5638
5639 d_api_name CONSTANT VARCHAR2(30) := 'sync_doc_num_change_to_bids';
5640 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
5641 d_progress NUMBER := 0;
5642
5643 BEGIN
5644
5645 IF PO_LOG.d_stmt THEN
5646 PO_LOG.stmt(d_module, d_progress, 'p_document_id ', p_document_id);
5647 PO_LOG.stmt(d_module, d_progress, 'p_clm_document_number ', p_clm_document_number);
5648 END IF;
5649
5650 d_progress := 10;
5651
5652 UPDATE PON_BID_HEADERS
5653 SET order_number = p_clm_document_number
5654 WHERE po_header_id = p_document_id;
5655
5656 IF PO_LOG.d_stmt THEN
5657 PO_LOG.stmt(d_module, d_progress, 'No. of records updated in PON_BID_HEADERS: ', SQL%ROWCOUNT);
5658 END IF;
5659
5660 d_progress := 20;
5661
5662 UPDATE PON_BID_ITEM_PRICES
5663 SET order_number = p_clm_document_number
5664 WHERE po_header_id = p_document_id;
5665
5666 IF PO_LOG.d_stmt THEN
5667 PO_LOG.stmt(d_module, d_progress, 'No. of records updated in PON_BID_ITEM_PRICES: ', SQL%ROWCOUNT);
5668 END IF;
5669
5670 EXCEPTION
5671 WHEN OTHERS THEN
5672 IF PO_LOG.d_stmt THEN
5673 PO_LOG.stmt(d_module, d_progress, 'error_message', SQLERRM);
5674 END IF;
5675 RAISE;
5676
5677 END sync_doc_num_change_to_bids;
5678
5679 END PO_DOC_NUMBERING_PKG;