[Home] [Help]
Skip to content
PACKAGE BODY: APPS.LNS_LOAN_HISTORY_PUB
Source
1 PACKAGE BODY LNS_LOAN_HISTORY_PUB AS
2 /* $Header: LNS_LNHIS_PUBP_B.pls 120.0 2005/05/31 17:58:27 appldev noship $ */
3 procedure get_record_snapshot(p_id NUMBER, p_primary_key_name VARCHAR2, p_table_name VARCHAR2, p_mode VARCHAR2) AS
4
5 l_select_col_stmt VARCHAR2(4000);
6 l_select_val_stmt VARCHAR2(4000);
7 l_cursorID INTEGER;
8 l_cursorID2 INTEGER;
9 l_column_name DBA_TAB_COLUMNS.column_name%TYPE;
10 l_data_type DBA_TAB_COLUMNS.data_type%TYPE;
11 l_dummy INTEGER;
12 l_index NUMBER;
13
14 BEGIN
15 l_cursorID := DBMS_SQL.OPEN_CURSOR;
16
17 l_select_col_stmt := 'select column_name, data_type from dba_tab_columns' || ' where table_name = :tab_name and owner = ''LNS'' order by column_id';
18
19 l_select_val_stmt := 'select';
20
21 DBMS_SQL.PARSE(l_cursorID, l_select_col_stmt, DBMS_SQL.V7);
22 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':tab_name', p_table_name);
23
24 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 1, l_column_name, 30);
25 DBMS_SQL.DEFINE_COLUMN(l_cursorID, 2, l_data_type, 106);
26
27 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
28
29 l_index := 1;
30 LOOP
31 IF DBMS_SQL.FETCH_ROWS(l_cursorID) = 0 THEN
32 EXIT;
33 END IF;
34
35 DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_column_name);
36 DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_data_type);
37
38 IF (l_column_name NOT IN ('CREATED_BY', 'CREATION_DATE',
39 'LAST_UPDATED_BY', 'LAST_UPDATE_DATE', 'LAST_UPDATE_LOGIN'
40 , 'OBJECT_VERSION_NUMBER')) THEN
41 IF (p_mode = 'PRE') THEN
42 G_VALUE_LIST(l_index).column_name := l_column_name;
43 G_VALUE_LIST(l_index).data_type := l_data_type;
44 ELSE
45 -- Validate in POST mode,
46 -- the snapshot of the SAME record is getting retrieved
47 IF (G_VALUE_LIST(l_index).column_name <> l_column_name)
48 THEN
49 RAISE FND_API.G_EXC_ERROR;
50 END IF;
51 END IF;
52
53 -- handle special first case
54 IF (length(l_select_val_stmt) = 6) THEN
55 l_select_val_stmt := l_select_val_stmt || ' ';
56 ELSE
57 l_select_val_stmt := l_select_val_stmt || ', ';
58 END IF;
59
60 IF (l_data_type = 'VARCHAR2') THEN
61 l_select_val_stmt := l_select_val_stmt || l_column_name;
62 ELSIF (l_data_type = 'NUMBER') THEN
63 l_select_val_stmt := l_select_val_stmt || 'to_char(' || l_column_name || ')';
64 ELSIF (l_data_Type = 'DATE') THEN
65 l_select_val_stmt := l_select_val_stmt || 'to_char(' || l_column_Name || ', ''DD-MON-YYYY HH24:MI:SS'')';
66 ELSE
67 l_select_val_stmt := l_select_val_stmt || l_column_name;
68 END IF;
69 l_index := l_index + 1;
70
71 END IF;
72
73 END LOOP;
74
75 l_index := G_VALUE_LIST.count;
76
77 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
78
79 IF (length(l_select_val_stmt) > 6) THEN
80
81 l_select_val_stmt := l_select_val_stmt || ' from ' || p_table_name || ' where ';
82 l_select_val_stmt := l_select_val_stmt || p_primary_key_name || ' = :p_id';
83
84 l_cursorID := DBMS_SQL.OPEN_CURSOR;
85 DBMS_SQL.PARSE(l_cursorID, l_select_val_stmt, DBMS_SQL.V7);
86
87 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':p_id', p_id);
88
89 FOR i in 1..l_index LOOP
90
91 IF (p_mode = 'PRE') THEN
92 DBMS_SQL.DEFINE_COLUMN(l_cursorID, i, G_VALUE_LIST(i).old_value, 2000);
93 ELSE
94 DBMS_SQL.DEFINE_COLUMN(l_cursorID, i, G_VALUE_LIST(i).new_value, 2000);
95 END IF;
96 END LOOP;
97
98 l_dummy := DBMS_SQL.EXECUTE(l_cursorID);
99
100 IF DBMS_SQL.FETCH_ROWS(l_cursorID) = 0 THEN
101 RAISE NO_DATA_FOUND;
102 END IF;
103
104 FOR i in 1..l_index LOOP
105 IF (p_mode = 'PRE') THEN
106 DBMS_SQL.COLUMN_VALUE(l_cursorID, i, G_VALUE_LIST(i).old_value);
107 ELSE
108 DBMS_SQL.COLUMN_VALUE(l_cursorID, i, G_VALUE_LIST(i).new_value);
109 END IF;
110 END LOOP;
111
112 DBMS_SQL.CLOSE_CURSOR(l_cursorID);
113 ELSE
114 raise FND_API.G_EXC_ERROR;
115 -- NO TABLE INFORMATION FOUND. RAISE ERROR HERE
116 END IF;
117 /*
118 for i in 1..l_index LOOP
119 dbms_output.put_line('COLUMN: ' || G_VALUE_LIST(i).column_name);
120 dbms_output.put_line('DATATYPE: ' ||G_VALUE_LIST(i).data_type);
121 dbms_output.put_line('OLD VALUE: ' ||G_VALUE_LIST(i).old_value);
122 dbms_output.put_line('NEW VALUE: ' ||G_VALUE_LIST(i).new_value);
123 END LOOP;
124 */
125 exception
126 when others then
127 dbms_sql.close_cursor(l_cursorID);
128 raise;
129 end get_record_snapshot;
130
131 procedure log_changes(p_loan_id NUMBER) AS
132 x_loan_history_id NUMBER := NULL;
133 BEGIN
134 for i in 1..G_VALUE_LIST.count LOOP
135 if (G_VALUE_LIST(i).old_value <> G_VALUE_LIST(i).new_value OR
136 (G_VALUE_LIST(i).old_value is NULL AND G_VALUE_LIST(i).new_value is NOT NULL) OR
137 (G_VALUE_LIST(i).old_value is NOT NULL AND G_VALUE_LIST(i).new_value is NULL)) then
138 LNS_LOAN_HISTORIES_H_PKG.Insert_Row(
139 x_loan_history_id => x_loan_history_id,
140 p_loan_id => p_loan_id,
141 p_table_Name => G_TABLE_NAME,
142 p_column_name => G_VALUE_LIST(i).column_name,
143 p_data_type => G_VALUE_LIST(i).data_type,
144 p_old_value => G_VALUE_LIST(i).old_value,
145 p_new_value => G_VALUE_LIST(i).new_value,
146 p_object_version_number => 1,
147 p_primary_key_id => G_PRIMARY_KEY_ID);
148 end if;
149 END LOOP;
150
151 EXCEPTION
152 WHEN OTHERS THEN
153 RAISE;
154 END;
155
156 procedure log_record_pre(p_id NUMBER, p_primary_key_name VARCHAR2, p_table_name VARCHAR2) AS
157 BEGIN
158 -- reset the table
159 G_VALUE_LIST.delete;
160 G_PRIMARY_KEY_NAME := p_primary_key_name;
161 G_PRIMARY_KEY_ID := p_id;
162 G_TABLE_NAME := p_table_name;
163 get_record_snapshot(p_id, p_primary_key_name, p_table_name, 'PRE');
164
165 END log_record_pre;
166
167 procedure log_record_post(p_id NUMBER, p_primary_key_name VARCHAR2, p_table_name VARCHAR2, p_loan_id NUMBER) AS
168 BEGIN
169
170 IF (G_VALUE_LIST.count = 0) THEN
171 -- NO PRE UPDATE SNAPSHOT, RAISE ERROR
172 raise FND_API.G_EXC_ERROR;
173 END IF;
174
175 IF (G_TABLE_NAME <> p_table_name) THEN
176 -- RAISE ERROR -- invalid table name
177 raise FND_API.G_EXC_ERROR;
178 END IF;
179
180 IF (G_PRIMARY_KEY_NAME <> p_primary_key_name OR
181 G_PRIMARY_KEY_ID <> p_id) THEN
182 raise FND_API.G_EXC_ERROR;
183 END IF;
184
185 get_record_snapshot(p_id, p_primary_key_name, p_table_name, 'POST');
186 log_changes(p_loan_id);
187
188 EXCEPTION
189 WHEN OTHERS THEN
190 RAISE;
191 END log_record_post;
192
193 END lns_loan_history_pub;