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;