[Home] [Help]
PACKAGE BODY: APPS.GML_PO_CREATE_FLEXFIELDS
Source
1 PACKAGE BODY gml_po_create_flexfields AS
2 /* $Header: GMLFLRGB.pls 115.4 99/10/26 11:36:04 porting ship $ */
3 /* +========================================================================+
4 | |
5 | PROCEDURE |
6 | compute_duom_qty |
7 | |
8 | DESCRIPTION |
9 | This procedure is called from the procedure to create segments. |
10 | |
11 | This procedure computes the Dual Unit of Measure Quantity for |
12 | Item Second Unit of Measure. |
13 | |
14 | MODIFICATION HISTORY |
15 | 09-DEC-97 Ravi Dasani Created. |
16 | |
17 +========================================================================+ */
18
19 FUNCTION compute_duom_qty
20 ( v_item_no IN IC_ITEM_MST.ITEM_NO%TYPE,
21 v_um1 IN CHAR,
22 v_order1 IN NUMBER,
23 v_um2 IN CHAR)
24 return NUMBER
25 IS
26
27 CURSOR gm_item_id_cur IS
28 SELECT item_id
29 FROM ic_item_mst
30 WHERE item_no = v_item_no;
31
32 v_order2 number;
33 v_std_factor1 NUMBER;
34 v_std_factor2 NUMBER;
35 v_type1 VARCHAR2(4);
36 v_type2 VARCHAR2(4);
37 v_std_factor NUMBER;
38 v_type VARCHAR2(4);
39 v_type_factor02 NUMBER := 1;
40 v_type_factor01 NUMBER := 1;
41
42 v_um VARCHAR2(4);
43
44 err_num NUMBER;
45 err_msg VARCHAR2(100);
46 fhandle utl_file.file_type;
47
48 gm_item_id IC_ITEM_MST.ITEM_ID%TYPE;
49
50 BEGIN
51
52 /* 11/6/1998 T. Ricci added*/
53
54 OPEN gm_item_id_cur;
55 FETCH gm_item_id_cur INTO gm_item_id;
56 CLOSE gm_item_id_cur;
57
58 v_order2 :=GMICUOM.uom_conversion
59 (gm_item_id,0,
60 v_order1,
61 v_um1,
62 v_um2,0);
63
64 IF v_order2 < 0 THEN
65 v_order2 := 0;
66 END IF;
67
68 return (v_order2);
69
70 EXCEPTION
71
72 WHEN OTHERS THEN
73 err_num := SQLCODE;
74 err_msg := SUBSTRB(SQLERRM, 1, 100);
75 return (0);
76
77 END compute_duom_qty;
78
79 /*
80 +========================================================================+
81 | |
82 | PROCEDURE |
83 | create_val_sets |
84 | |
85 | DESCRIPTION |
86 | This procedure is called from the script to set up flexfields. |
87 | |
88 | This procedure creates new value sets which are used for the |
89 | following descriptive flexfields: Company Code, QC Grade, Base UOM, |
90 | and Secondary UOM. |
91 | |
92 | MODIFICATION HISTORY |
93 | 23-SEP-97 Kristie Chen Created. |
94 | 17-NOV-98 Tony Ricci removed IN VARCHAR2 from valueset_exists |
95 | and delete_valueset for R11 changes |
96 +========================================================================+ */
97
98 PROCEDURE create_val_sets IS
99 BEGIN
100
101 FND_FLEX_VAL_API.SET_SESSION_MODE('customer_data');
102
103 /* delete valuesets if they exist */
104
105 IF fnd_flex_val_api.valueset_exists('CPG_CO_CODE') THEN
106 fnd_flex_val_api.delete_valueset('CPG_CO_CODE');
107 End IF;
108
109 IF fnd_flex_val_api.valueset_exists('CPG_QC_GRADE') THEN
110 fnd_flex_val_api.delete_valueset('CPG_QC_GRADE');
111 End IF;
112
113 IF fnd_flex_val_api.valueset_exists('CPG_BASE_UOM') THEN
114 fnd_flex_val_api.delete_valueset('CPG_BASE_UOM');
115 End IF;
116
117 IF fnd_flex_val_api.valueset_exists('CPG_SEC_UOM') THEN
118 fnd_flex_val_api.delete_valueset('CPG_SEC_UOM');
119 End IF;
120
121 IF fnd_flex_val_api.valueset_exists('CPG_PO_NO') THEN
122 fnd_flex_val_api.delete_valueset('CPG_PO_NO');
123 End IF;
124
125 IF fnd_flex_val_api.valueset_exists('CPG_PO_QTY') THEN
126 fnd_flex_val_api.delete_valueset('CPG_PO_QTY');
127 End IF;
128
129 /* create value sets */
130
131 fnd_flex_val_api.create_valueset_table(
132 value_set_name => 'CPG_CO_CODE',
133 description => 'Displays co code',
134 security_available => 'N',
135 enable_longlist => 'Y',
136 format_type => 'Char',
137 maximum_size => 30,
138 numbers_only => 'N',
139 uppercase_only => 'N',
140 right_justify_zero_fill => 'N',
141 min_value => null,
142 max_value => null,
143 table_appl_short_name => 'PO',
144 table_name => 'SY_ORGN_MST',
145 allow_parent_values => 'N',
146 value_column_name => 'orgn_code',
147 value_column_type => 'Char',
148 value_column_size => 30,
149 meaning_column_name => 'orgn_name',
150 meaning_column_type => 'Char',
151 meaning_column_size => 40,
152 id_column_name => NULL, /* Column Name has been nullified*/
153 id_column_type => NULL,
154 id_column_size => NULL,
155 where_order_by => 'Where co_code in (select co_code from gl_plcy_mst where org_id=:$PROFILES$.ORG_ID) Order by ORGN_CODE');
156
157 fnd_flex_val_api.create_valueset_table(
158 value_set_name => 'CPG_PO_NO',
159 description => 'Displays Purchase Order Numbers',
160 security_available => 'N',
161 enable_longlist => 'Y',
162 format_type => 'Char',
163 maximum_size => 30,
164 numbers_only => 'N',
165 uppercase_only => 'N',
166 right_justify_zero_fill => 'N',
167 min_value => null,
168 max_value => null,
169 table_appl_short_name => 'PO',
170 table_name => 'PO_HEADERS_ALL POH',
171 allow_parent_values => 'N',
172 value_column_name => 'segment1',
173 value_column_type => 'Varchar2',
174 value_column_size => 20,
175 meaning_column_name => 'comments',
176 meaning_column_type => 'Char',
177 meaning_column_size => 40,
178 id_column_name => NULL, /* Column Name has been nullified*/
179 id_column_type => NULL,
180 id_column_size => NULL,
181 where_order_by => 'Where POH.TYPE_LOOKUP_CODE in
182 (''STANDARD'', ''PLANNED'', ''BLANKET'')
183 AND POH.APPROVED_FLAG = ''Y''');
184
185 fnd_flex_val_api.create_valueset_table(
186 value_set_name => 'CPG_QC_GRADE',
187 description => 'Displays qc grade',
188 security_available => 'N',
189 enable_longlist => 'Y',
190 format_type => 'Char',
191 maximum_size => 30,
192 numbers_only => 'N',
193 uppercase_only => 'N',
194 right_justify_zero_fill => 'N',
195 min_value => null,
196 max_value => null,
197 table_appl_short_name => 'PO',
198 table_name => 'GMS_GRAD_MST',
199 allow_parent_values => 'N',
200 value_column_name => 'qc_grade',
201 value_column_type => 'Char',
202 value_column_size => 30,
203 meaning_column_name => 'QC_GRADE_DESC',
204 meaning_column_type => 'Char',
205 meaning_column_size => 40,
206 id_column_name => NULL, /* Column Name has been nullified*/
207 id_column_type => NULL,
208 id_column_size => NULL,
209 where_order_by => 'Order by QC_GRADE');
210
211 fnd_flex_val_api.create_valueset_table(
212 value_set_name => 'CPG_BASE_UOM',
213 description => 'Displays base uom',
214 security_available => 'N',
215 enable_longlist => 'Y',
216 format_type => 'Char',
217 maximum_size => 30,
218 numbers_only => 'N',
219 uppercase_only => 'N',
220 right_justify_zero_fill => 'N',
221 min_value => null,
222 max_value => null,
223 table_appl_short_name => 'PO',
224 table_name => 'GMS_ITEM_MST',
225 allow_parent_values => 'N',
226 value_column_name => 'item_um',
227 value_column_type => 'Char',
228 value_column_size => 30,
229 id_column_name => NULL, /* Column Name has been nullified*/
230 id_column_type => NULL,
231 id_column_size => NULL,
232 where_order_by => 'where item_no=decode(:system.current_block, ''PO_LINES'', :po_lines.item_number, null)');
233
234 fnd_flex_val_api.create_valueset_table(
235 value_set_name => 'CPG_SEC_UOM',
236 description => 'Displays secondary uom',
237 security_available => 'N',
238 enable_longlist => 'Y',
239 format_type => 'Char',
240 maximum_size => 30,
241 numbers_only => 'N',
242 uppercase_only => 'N',
243 right_justify_zero_fill => 'N',
244 min_value => null,
245 max_value => null,
246 table_appl_short_name => 'PO',
247 table_name => 'GMS_ITEM_MST',
248 allow_parent_values => 'N',
249 value_column_name => 'item_um2',
250 value_column_type => 'Char',
251 value_column_size => 30,
252 id_column_name => NULL, /* Column Name has been nullified*/
253 id_column_type => NULL,
254 id_column_size => NULL,
255 where_order_by => 'where item_no=decode(:system.current_block, ''PO_LINES'', :po_lines.item_number, null)');
256
257 fnd_flex_val_api.create_valueset_none(
258 value_set_name => 'CPG_PO_QTY',
259 description => 'CPG Purchasing Base and Dual Qty',
260 security_available => 'N',
261 enable_longlist => 'N',
262 format_type => 'Number',
263 maximum_size => 10,
264 precision => 2,
265 numbers_only => 'Y',
266 uppercase_only => 'N',
267 right_justify_zero_fill => 'N',
268 min_value => null,
269 max_value => null);
270 END create_val_sets;
271
272 /*
273 +========================================================================+
274 | |
275 | PROCEDURE |
276 | delete_segments |
277 | |
278 | DESCRIPTION |
279 | This procedure is called from the script to set up flexfields. |
280 | |
281 | This procedure deletes the new descriptive flexfield segments, in |
282 | order to create new ones. |
283 | |
284 | MODIFICATION HISTORY |
285 | 23-SEP-97 Kristie Chen Created. |
286 | 17-NOV-98 Tony Ricci removed application parameter (was 1st) for |
287 | R11 changes to fnd_flex_dsc_api.delete_segment |
288 +========================================================================+
289 */
290
291 procedure delete_segments IS
292 BEGIN
293 fnd_flex_dsc_api.set_session_mode('customer_data');
294
295 fnd_flex_dsc_api.delete_segment('PO', 'PO_HEADERS', 'Global Data Elements', 'GEMMS Organization');
296
297 fnd_flex_dsc_api.delete_segment('PO', 'PO_LINES', 'Global Data Elements', 'QC Grade');
298
299 fnd_flex_dsc_api.delete_segment('PO', 'PO_LINES', 'Global Data Elements', 'Base UOM');
300
301 fnd_flex_dsc_api.delete_segment('PO', 'PO_LINES', 'Global Data Elements', 'Base Qty');
302
303 fnd_flex_dsc_api.delete_segment('PO', 'PO_LINES', 'Global Data Elements', 'Secondary UOM');
304
305 fnd_flex_dsc_api.delete_segment('PO', 'PO_LINES', 'Global Data Elements', 'Secondary Qty');
306
307 end delete_segments;
308
309
310 /*
311 +========================================================================+
312 | |
313 | PROCEDURE |
314 | create_segments |
315 | |
316 | DESCRIPTION |
317 | This procedure is called from the script to set up flexfields. |
318 | |
319 | This procedure creates the new descriptive flexfield segments. |
320 | |
321 | MODIFICATION HISTORY |
322 | 23-SEP-97 Kristie Chen Created. |
323 | 17-NOV-98 Tony Ricci removed application parameter (was 1st) for |
324 | R11 changes to fnd_flex_dsc_api.create_segment and |
325 | fnd_flex_dsc_api.freeze |
326 +========================================================================+ */
327
328 procedure create_segments IS
329 BEGIN
330
331 fnd_flex_dsc_api.set_session_mode('customer_data');
332
333 fnd_flex_dsc_api.create_segment(
334 appl_short_name => 'PO' ,
335 flexfield_name => 'PO_HEADERS',
336 context_name => 'Global Data Elements',
337 name => 'GEMMS Organization',
338 column => 'ATTRIBUTE15',
339 description => 'GEMMS Organization',
340 sequence_number => 1,
341 enabled => 'Y',
342 displayed => 'Y',
343 value_set => 'CPG_CO_CODE',
344 default_type => 'Profile',
345 default_value => 'GEMMS_DEFAULT_ORGN',
346 required => 'N',
347 security_enabled => 'N',
348 display_size => 8,
349 description_size => 8,
350 concatenated_description_size => 8,
351 list_of_values_prompt => 'GEMMS Organization',
352 window_prompt => 'GEMMS Organization',
353 range => NULL,
354 srw_parameter => NULL);
355
356
357 fnd_flex_dsc_api.create_segment(
358 appl_short_name => 'PO' ,
359 flexfield_name => 'PO_LINES',
360 context_name => 'Global Data Elements',
361 name => 'QC Grade',
362 column => 'ATTRIBUTE11',
363 description => 'QC Grade',
364 sequence_number => 1,
365 enabled => 'Y',
366 displayed => 'Y',
367 value_set => 'CPG_QC_GRADE',
368 default_type => 'SQL Statement',
369 default_value => 'select qc_grade from gms_item_mst where item_no=decode(:system.current_block, ''PO_LINES'', :po_lines.item_number, null)',
370 required => 'N',
371 security_enabled => 'N',
372 display_size => 8,
373 description_size => 8,
374 concatenated_description_size => 8,
375 list_of_values_prompt => 'QC Grade',
376 window_prompt => 'QC Grade',
377 range => NULL,
378 srw_parameter => NULL);
379
380
381
382 fnd_flex_dsc_api.create_segment(
383 appl_short_name => 'PO' ,
384 flexfield_name => 'PO_LINES',
385 context_name => 'Global Data Elements',
386 name => 'Base UOM',
387 column => 'ATTRIBUTE12',
388 description => 'Base UOM',
389 sequence_number => 2,
390 enabled => 'Y',
391 displayed => 'Y',
392 value_set => 'CPG_BASE_UOM',
393 default_type => 'SQL Statement',
394 default_value => 'select item_um from gms_item_mst where item_no=decode(:system.current_block, ''PO_LINES'', :po_lines.item_number, null)',
395 required => 'N',
396 security_enabled => 'N',
397 display_size => 8,
398 description_size => 8,
399 concatenated_description_size => 8,
400 list_of_values_prompt => 'Base UOM',
401 window_prompt => 'Base UOM',
402 range => NULL,
403 srw_parameter => NULL);
404
405
406
407 fnd_flex_dsc_api.create_segment(
408 appl_short_name => 'PO' ,
409 flexfield_name => 'PO_LINES',
410 context_name => 'Global Data Elements',
411 name => 'Base Qty',
412 column => 'ATTRIBUTE13',
413 description => 'Base Qty',
414 sequence_number => 3,
415 enabled => 'Y',
416 displayed => 'Y',
417 value_set => 'CPG_PO_QTY',
418 default_type => 'SQL Statement',
419 default_value => 'select gml_po_create_flexfields.compute_duom_qty(decode(:system.current_block, ''PO_LINES'', :po_lines.item_number, null),
420 decode(:system.current_block, ''PO_LINES'', :po_lines.unit_meas_lookup_code, null) ,
421 decode(:system.current_block, ''PO_LINES'', :po_lines.quantity, null), :$FLEX$.CPG_BASE_UOM) from dual',
422 required => 'N',
423 security_enabled => 'N',
424 display_size => 8,
425 description_size => 8,
426 concatenated_description_size => 8,
427 list_of_values_prompt => 'Base Qty',
428 window_prompt => 'Base Qty',
429 range => NULL,
430 srw_parameter => NULL);
431
432
433
434 fnd_flex_dsc_api.create_segment(
435 appl_short_name => 'PO' ,
436 flexfield_name => 'PO_LINES',
437 context_name => 'Global Data Elements',
438 name => 'Secondary UOM',
439 column => 'ATTRIBUTE14',
440 description => 'Secondary UOM',
441 sequence_number => 4,
442 enabled => 'Y',
443 displayed => 'Y',
444 value_set => 'CPG_SEC_UOM',
445 default_type => 'SQL Statement',
446 default_value => 'select item_um2 from gms_item_mst where item_no=decode(:system.current_block, ''PO_LINES'', :po_lines.item_number, null)',
447 required => 'N',
448 security_enabled => 'N',
449 display_size => 8,
450 description_size => 8,
451 concatenated_description_size => 8,
452 list_of_values_prompt => 'Secondary UOM',
453 window_prompt => 'Secondary UOM',
454 range => NULL,
455 srw_parameter => NULL);
456
457
458
459 fnd_flex_dsc_api.create_segment(
460 appl_short_name => 'PO' ,
461 flexfield_name => 'PO_LINES',
462 context_name => 'Global Data Elements',
463 name => 'Secondary Qty',
464 column => 'ATTRIBUTE15',
465 description => 'Secondary Qty',
466 sequence_number => 5,
467 enabled => 'Y',
468 displayed => 'Y',
469 value_set => 'CPG_PO_QTY',
470 default_type => 'SQL Statement',
471 default_value => 'select gml_po_create_flexfields.compute_duom_qty(decode(:system.current_block, ''PO_LINES'', :po_lines.item_number, null),:po_lines.unit_meas_lookup_code,
472 decode(:system.current_block, ''PO_LINES'', :po_lines.quantity, null), :$FLEX$.CPG_SEC_UOM) from dual',
473 required => 'N',
474 security_enabled => 'N',
475 display_size => 8,
476 description_size => 8,
477 concatenated_description_size => 8,
478 list_of_values_prompt => 'Secondary Qty',
479 window_prompt => 'Secondary Qty',
480 range => NULL,
481 srw_parameter => NULL);
482
483
484 fnd_flex_dsc_api.freeze(
485 appl_short_name => 'PO' ,
486 flexfield_name => 'PO_HEADERS');
487
488
489 fnd_flex_dsc_api.freeze(
490 appl_short_name => 'PO' ,
491 flexfield_name => 'PO_LINES');
492
493 end create_segments;
494
495 /* +========================================================================+
496 | |
497 | PROCEDURE |
498 | get_item_um2 |
499 | |
500 | DESCRIPTION |
501 | This procedure is called from CUSTOM.pll to get the item_um2 in |
502 | order to perform a unit of measure conversion |
503 | |
504 | |
505 | MODIFICATION HISTORY |
506 | 17-MAR-99 Tony Ricci Created for Bug 817680 |
507 | |
508 +========================================================================+ */
509
510 FUNCTION get_item_um2
511 ( v_item_no IN IC_ITEM_MST.ITEM_NO%TYPE)
512 return VARCHAR2
513 IS
514
515 CURSOR gm_item_um2_cur IS
516 SELECT item_um2
517 FROM ic_item_mst
518 WHERE item_no = v_item_no;
519
520 err_num NUMBER;
521 err_msg VARCHAR2(100);
522
523 gm_item_um2 IC_ITEM_MST.ITEM_UM2%TYPE;
524
525 BEGIN
526
527 OPEN gm_item_um2_cur;
528 FETCH gm_item_um2_cur INTO gm_item_um2;
529 CLOSE gm_item_um2_cur;
530
531 return (gm_item_um2);
532
533 EXCEPTION
534
535 WHEN OTHERS THEN
536 err_num := SQLCODE;
537 err_msg := SUBSTRB(SQLERRM, 1, 100);
538 return (0);
539
540 END get_item_um2;
541
542 END gml_po_create_flexfields;