[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_UPD_FMT
Source
1 PACKAGE BODY fnd_flex_upd_fmt AS
2 /* $Header: AFFFUPFB.pls 115.0 99/07/16 23:20:04 porting ship $ */
3
4 /* START_PUBLIC */
5 bad_parameter EXCEPTION;
6 PRAGMA EXCEPTION_INIT(bad_parameter, -06501);
7 /* END_PUBLIC */
8
9
10 debug_mode_on BOOLEAN := FALSE;
11
12 PROCEDURE debug_on IS
13 BEGIN
14 debug_mode_on := TRUE;
15 END;
16
17 PROCEDURE debug_off IS
18 BEGIN
19 debug_mode_on := FALSE;
20 END;
21
22 PROCEDURE println(msg IN VARCHAR2) IS
23 BEGIN
24 IF(debug_mode_on) THEN
25 dbms_output.enable;
26 dbms_output.put_line(msg);
27 END IF;
28 END;
29
30
31 /* START_PUBLIC */
32
33 /* Change the date format in the specified column of the
34 specified table to conform to the new date and time standards.
35 The new_format_type is one of Date, DateTime, or Time.
36 If the old format is not specified (as a format string
37 in the to_char format), then the old standard conrresponding
38 to the new format type is used.
39 */
40 /* old 'standard' formats
41 old_date_fmt := 'DD-MON-YY HH24:MI:SS';
42 old_datetime_fmt := 'DD-MON-YY HH24:MI:SS';
43 old_time_fmt := 'HH24:MI:SS';
44 */
45
46 PROCEDURE convert_date(table_name IN VARCHAR2,
47 column_name IN VARCHAR2,
48 new_format_type IN VARCHAR2,
49 old_format IN VARCHAR2 DEFAULT null)
50 IS
51 /* new standard formats */
52 std_date_fmt VARCHAR2(100) := 'YYYY/MM/DD HH24:MI:SS';
53 std_datetime_fmt VARCHAR2(100) := 'YYYY/MM/DD HH24:MI:SS';
54 std_time_fmt VARCHAR2(100) := 'HH24:MI:SS';
55 /* old 'standard' formats */
56 old_date_fmt VARCHAR2(100) := 'DD-MON-YY HH24:MI:SS';
57 old_datetime_fmt VARCHAR2(100) := 'DD-MON-YY HH24:MI:SS';
58 old_time_fmt VARCHAR2(100) := 'HH24:MI:SS';
59 cursor_handle INTEGER;
60 format_type VARCHAR2(100);
61 sqlstmt VARCHAR2(1000);
62 newfmt VARCHAR2(100);
63 oldfmt VARCHAR2(100);
64 num_lines NUMBER;
65 BEGIN
66 format_type := Upper(new_format_type);
67 IF(format_type = 'DATE') THEN
68 oldfmt := old_date_fmt;
69 newfmt := std_date_fmt;
70 ELSIF(format_type = 'DATETIME') THEN
71 oldfmt := old_datetime_fmt;
72 newfmt := std_datetime_fmt;
73 ELSIF(format_type = 'TIME') THEN
74 oldfmt := old_time_fmt;
75 newfmt := std_time_fmt;
76 ELSE
77 println('bad format type:'||format_type);
78 RAISE bad_parameter;
79 END IF;
80
81 IF(old_format IS NOT NULL) THEN
82 oldfmt := old_format;
83 END IF;
84
85 -- make sure there are no quotes to mess things up
86 IF((Instr(oldfmt, '''') > 0) OR
87 (Instr(newfmt, '''') > 0)) THEN
88 println('detected single quote in format string');
89 RAISE bad_parameter;
90 END IF;
91
92
93 -- sqlstmt := 'UPDATE :table_name ' ||
94 -- 'SET :column_name = To_char(To_date(:column_name, :oldfmt), :newfmt)';
95
96 sqlstmt := 'UPDATE ' || table_name ||
97 ' SET ' || column_name ||
98 ' = To_char(To_date('||column_name|| ',''' || oldfmt ||
99 '''), ''' || newfmt || ''')';
100
101 println('table name:'||table_name);
102 println('sql:'||sqlstmt);
103
104 cursor_handle := dbms_sql.open_cursor;
105 dbms_sql.parse(cursor_handle, sqlstmt, dbms_sql.v7);
106 -- dbms_sql.bind_variable(cursor_handle, 'table_name', table_name);
107 -- dbms_sql.bind_variable(cursor_handle, 'column_name', column_name);
108 -- dbms_sql.bind_variable(cursor_handle, 'oldfmt', oldfmt);
109 -- dbms_sql.bind_variable(cursor_handle, 'newfmt', newfmt);
110 num_lines := dbms_sql.execute(cursor_handle);
111 dbms_sql.close_cursor(cursor_handle);
112 println('updated:' || To_char(num_lines));
113 EXCEPTION
114 WHEN OTHERS THEN
115 dbms_sql.close_cursor(cursor_handle);
116 println(Sqlerrm);
117 RAISE bad_parameter;
118 END;
119
120
121 /* END_PUBLIC */
122
123 END fnd_flex_upd_fmt;