[Home] [Help]
PACKAGE BODY: APPS.AZ_R12_UPD_DET_LOG
Source
1 PACKAGE BODY az_r12_upd_det_log AS
2 /* $Header: azr12detlog.plb 120.12 2008/05/30 11:34:36 gnamasiv noship $ */ -- Author : LMATHUR
3 -- Created : 12/2/2007 2:22:06 PM
4 -- Purpose : Update the status for the detailed logging records
5 -- Function and procedure implementations
6
7 /*********************Procedure declarations*************************************/
8
9 PROCEDURE raise_error_msg(errcode IN NUMBER, errmsg IN VARCHAR2, procedurename IN VARCHAR2, statement IN VARCHAR2);
10
11 PROCEDURE update_master_status(p_request_id IN NUMBER, p_source IN VARCHAR2, p_log_status IN VARCHAR2, p_status_check_clause IN VARCHAR2, p_id_list IN typ_nest_tab_number);
12
13 c_log_head constant VARCHAR2(30) := 'az.plsql.az_r12_upd_det_log.';
14
15 /**********************************************************/
16
17 PROCEDURE update_status(p_request_id IN NUMBER, p_source IN VARCHAR2) IS
18
19 v_current_id NUMBER := 2;
20 --starting ID
21 v_commit_marker_id_list typ_nest_tab_number;
22 v_last_commit_marker_id NUMBER;
23 v_error_id NUMBER;
24 v_commit_marker_name VARCHAR2(255) := 'AZ_CommitMarker';
25 l_api_name constant VARCHAR2(70) := 'update_status : ';
26 l_log_query VARCHAR2(32000);
27 BEGIN
28
29 EXECUTE IMMEDIATE 'ALTER SESSION SET CURSOR_SHARING=''SIMILAR''';
30
31 BEGIN
32
33 SELECT MAX(id)
34 INTO v_last_commit_marker_id
35 FROM az_diff_results
36 WHERE request_id = p_request_id
37 AND source = p_source
38 AND name = v_commit_marker_name
39 ORDER BY id;
40
41 EXCEPTION
42 WHEN no_data_found THEN
43 v_last_commit_marker_id := v_current_id;
44 END;
45
46 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
47 fnd_log.string(FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name
48 || to_char(systimestamp), 'Getting Max Commit marker id for p_request_id: ' || p_request_id
49 || ' p_source: ' || p_source || ' v_last_commit_marker_id: ' || v_last_commit_marker_id);
50 end if;
51
52 DELETE FROM az_diff_results
53 WHERE request_id = p_request_id
54 AND source = p_source
55 AND name = v_commit_marker_name;
56
57 -- there are no commit markers, hence update the status as SP for all records
58
59 UPDATE az_diff_results
60 SET detail_log_status = 'SP'
61 WHERE request_id = p_request_id
62 AND source = p_source
63 AND id > v_last_commit_marker_id
64 AND detail_log_status IN('I', 'U', 'IW', 'UW')
65 AND detail_log_status <> 'SP';
66
67 COMMIT;
68
69 -- Now update the entire tree for records which are Skipped with Warning
70 SELECT id bulk collect
71 INTO v_commit_marker_id_list
72 FROM az_diff_results
73 WHERE request_id = p_request_id
74 AND source = p_source
75 AND detail_log_status = 'SW'
76 ORDER BY id;
77
78 IF v_commit_marker_id_list.COUNT <> 0 THEN
79 update_master_status(p_request_id, p_source, 'concat(g.detail_log_status,''W'')', ' not in (''IW'',''UW'',''SW'')', v_commit_marker_id_list);
80 END IF;
81
82 ------------------------------------------------------------------------------
83 -- Now update the entire tree for records which are Skipped with Warning
84 SELECT id bulk collect
85 INTO v_commit_marker_id_list
86 FROM az_diff_results
87 WHERE request_id = p_request_id
88 AND source = p_source
89 AND detail_log_status = 'E'
90 ORDER BY id;
91
92 IF v_commit_marker_id_list.COUNT <> 0 THEN
93 update_master_status(p_request_id, p_source, '''SE''', ' not in (''E'',''SE'')', v_commit_marker_id_list);
94 END IF;
95
96 --CALLED EXTERNALLY---update_det_log_counts(p_request_id, p_source);
97 COMMIT;
98
99 EXCEPTION
100 WHEN no_data_found THEN
101 NULL;
102 WHEN application_exception THEN
103 RAISE;
104 WHEN others THEN
105 raise_error_msg(SQLCODE, sqlerrm, 'update_status', 'procedure end');
106
107 END update_status;
108
109 PROCEDURE update_master_status(p_request_id IN NUMBER, p_source IN VARCHAR2, p_log_status IN VARCHAR2, p_status_check_clause IN VARCHAR2, p_id_list IN typ_nest_tab_number) IS
110 l_api_name constant VARCHAR2(30) := 'update_master_status : ';
111 l_log_query VARCHAR2(4000);
112 BEGIN
113
114 FOR i IN 1 .. p_id_list.COUNT
115 LOOP
116 l_log_query := 'UPDATE az_diff_results g
117 SET g.detail_log_status = ' || p_log_status || '
118 WHERE g.id in
119 (SELECT k.id
120 FROM
121 (SELECT parent_id, id, detail_log_status
122 FROM
123 (SELECT d.parent_id, d.id, d.detail_log_status
124 FROM az_diff_results d
125 WHERE d.request_id = ' || p_request_id || '
126 AND d.source = ''' || p_source || '''
127 AND d.parent_id >0) f
128 CONNECT BY PRIOR f.parent_id = f.id START WITH f.id = ' || p_id_list(i) || ') k
129
130 WHERE (k.detail_log_status ' || p_status_check_clause || '))
131 AND g.request_id = ' || p_request_id || '
132 AND g.source = ''' || p_source || '''';
133
134 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
135 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
136 || to_char(systimestamp), 'Executing update master status query: ' || l_log_query);
137 END IF;
138
139 EXECUTE IMMEDIATE l_log_query;
140 END LOOP;
141 END update_master_status;
142
143 PROCEDURE raise_error_msg (ErrCode IN NUMBER,
144 ErrMsg IN VARCHAR2,
145 ProcedureName IN VARCHAR2,
146 Statement IN VARCHAR2) IS
147
148 v_message VARCHAR2(2048);
149
150 BEGIN
151
152 if( FND_LOG.LEVEL_UNEXPECTED >=
153 FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
154 FND_MESSAGE.SET_NAME('AZ', 'AZ_R12_PLSQL_EXCEPTION'); -- Seeded Message
155 -- Runtime Information
156 FND_MESSAGE.SET_TOKEN('ERROR_CODE', ErrCode);
157 FND_MESSAGE.SET_TOKEN('ERROR_MESG', ErrMsg);
158 FND_MESSAGE.SET_TOKEN('ERROR_PROC', 'az_comp_reporter.' || ProcedureName);
159 IF (Statement IS NOT NULL) THEN
160 FND_MESSAGE.SET_TOKEN('ERROR_STMT', Statement);
161 ELSE
162 FND_MESSAGE.SET_TOKEN('ERROR_STMT', 'none');
163 END IF;
164 raise_application_error(-20001, FND_MESSAGE.GET);
165 end if;
166 END raise_error_msg;
167
168
169 PROCEDURE update_det_log_counts(p_request_id IN NUMBER, p_source IN VARCHAR2, p_update_xsl OUT nocopy VARCHAR2) IS
170
171 v_diff_type_list typ_nest_tab_varchar;
172 v_diff_count_list typ_nest_tab_varchar;
173
174 v_rows_count NUMBER;
175 v_update_xsl VARCHAR2(32767);
176 l_api_name constant VARCHAR2(50) := 'update_det_log_counts : ';
177 BEGIN
178
179 v_update_xsl := '';
180 v_update_xsl := v_update_xsl || '<?xml version="1.0" ?> ';
181 v_update_xsl := v_update_xsl || '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> ';
182 v_update_xsl := v_update_xsl || '<xsl:template match="EXT"> ';
183 v_update_xsl := v_update_xsl || '<EXT> ';
184 v_update_xsl := v_update_xsl || '<xsl:copy-of select="@*"/> ';
185 v_update_xsl := v_update_xsl || '<xsl:apply-templates/> ';
186 v_update_xsl := v_update_xsl || '</EXT> ';
187 v_update_xsl := v_update_xsl || '</xsl:template> ';
188 v_update_xsl := v_update_xsl || '<xsl:template match="H"> ';
189 v_update_xsl := v_update_xsl || '<H> ';
190 v_update_xsl := v_update_xsl || '<xsl:copy-of select="@*"/> ';
191 v_update_xsl := v_update_xsl || '<xsl:copy-of select="*[not(name()=''V'')]"/> ';
192 v_update_xsl := v_update_xsl || '<xsl:for-each select="V"> ';
193 v_update_xsl := v_update_xsl || '<V> ';
194 v_update_xsl := v_update_xsl || '<xsl:copy-of select="@*"/> ';
195 v_update_xsl := v_update_xsl || '<xsl:choose> ';
196
197 ---Rows Inserted
198 SELECT COUNT(1)
199 INTO v_rows_count
200 FROM az_diff_results d
201 WHERE request_id = p_request_id
202 AND source = p_source
203 AND parent_id = 1
204 AND detail_log_status = 'I';
205
206 v_update_xsl := v_update_xsl || '<xsl:when test="@N=''RowsInserted'' and ../V[@N=''EntityOccuranceCode'' and .=''';
207 v_update_xsl := v_update_xsl || p_source || ''']">';
208 v_update_xsl := v_update_xsl || v_rows_count || '</xsl:when>';
209
210 ---Rows Updated
211 SELECT COUNT(1)
212 INTO v_rows_count
213 FROM az_diff_results d
214 WHERE request_id = p_request_id
215 AND source = p_source
216 AND parent_id = 1
217 AND detail_log_status = 'U';
218
219 v_update_xsl := v_update_xsl || '<xsl:when test="@N=''RowsUpdated'' and ../V[@N=''EntityOccuranceCode'' and .=''';
220 v_update_xsl := v_update_xsl || p_source || ''']">';
221 v_update_xsl := v_update_xsl || v_rows_count || '</xsl:when>';
222
223 SELECT COUNT(1)
224 INTO v_rows_count
225 FROM az_diff_results d
226 WHERE request_id = p_request_id
227 AND source = p_source
228 AND parent_id = 1
229 AND(detail_log_status LIKE 'S%' OR detail_log_status = 'E');
230
231 v_update_xsl := v_update_xsl || '<xsl:when test="@N=''RowsSkipped'' and ../V[@N=''EntityOccuranceCode'' and .=''';
232 v_update_xsl := v_update_xsl || p_source || ''']">';
233 v_update_xsl := v_update_xsl || v_rows_count || '</xsl:when>';
234
235 ---Rows RowsPartiallyInserted
236 SELECT COUNT(1)
237 INTO v_rows_count
238 FROM az_diff_results d
239 WHERE request_id = p_request_id
240 AND source = p_source
241 AND parent_id = 1
242 AND detail_log_status = 'IW';
243
244 v_update_xsl := v_update_xsl || '<xsl:when test="@N=''RowsPartiallyInserted'' and ../V[@N=''EntityOccuranceCode'' and .=''';
245 v_update_xsl := v_update_xsl || p_source || ''']">';
246 v_update_xsl := v_update_xsl || v_rows_count || '</xsl:when>';
247
248 ---Rows RowsPartiallyUpdated
249 SELECT COUNT(1)
250 INTO v_rows_count
251 FROM az_diff_results d
252 WHERE request_id = p_request_id
253 AND source = p_source
254 AND parent_id = 1
255 AND detail_log_status = 'UW';
256
257 v_update_xsl := v_update_xsl || '<xsl:when test="@N=''RowsPartiallyUpdated'' and ../V[@N=''EntityOccuranceCode'' and .=''';
258 v_update_xsl := v_update_xsl || p_source || ''']">';
259 v_update_xsl := v_update_xsl || v_rows_count || '</xsl:when>';
260
261 v_update_xsl := v_update_xsl || '<xsl:otherwise> ';
262 v_update_xsl := v_update_xsl || '<xsl:value-of select ="."/> ';
263 v_update_xsl := v_update_xsl || '</xsl:otherwise> ';
264 v_update_xsl := v_update_xsl || '</xsl:choose> ';
265 v_update_xsl := v_update_xsl || '</V> ';
266 v_update_xsl := v_update_xsl || '</xsl:for-each> ';
267 v_update_xsl := v_update_xsl || '</H> ';
268 v_update_xsl := v_update_xsl || '</xsl:template> ';
269 v_update_xsl := v_update_xsl || '</xsl:stylesheet> ';
270
271 p_update_xsl := v_update_xsl;
272
273 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
274 fnd_log.string(fnd_log.level_statement, c_log_head || l_api_name
275 || to_char(systimestamp) , 'Generated v_update_xsl: ' || v_update_xsl);
276 end if;
277
278 EXCEPTION
279 WHEN no_data_found THEN
280
281 NULL;
282 WHEN application_exception THEN
283 RAISE;
284 WHEN others THEN
285 raise_error_msg(SQLCODE, sqlerrm, 'update_det_log_counts', 'Error while updating the count based on type of status');
286
287 END update_det_log_counts;
288
289 END az_r12_upd_det_log;
290
291