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