[Home] [Help]
PACKAGE BODY: APPS.ZX_ON_FLY_TRX_UPGRADE_PKG
Source
1 PACKAGE BODY ZX_ON_FLY_TRX_UPGRADE_PKG AS
2 /* $Header: zxmigtrxflypkgb.pls 120.2.12010000.2 2008/12/31 13:35:20 rajessub ship $ */
3
4 g_current_runtime_level NUMBER;
5 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9
10 -------------------------------------------------------------------------------
11 -- PUBLIC PROCEDURE
12 -- upgrade_trx_on_fly
13 --
14 -- DESCRIPTION
15 -- on the fly migration for one transaction
16 --
17 -------------------------------------------------------------------------------
18
19 PROCEDURE upgrade_trx_on_fly(
20 p_application_id IN NUMBER,
21 p_entity_code IN VARCHAR2,
22 p_event_class_code IN VARCHAR2,
23 p_trx_id IN NUMBER,
24 x_return_status OUT NOCOPY VARCHAR2
25 ) IS
26 l_return_status VARCHAR2(100);
27 l_upg_trx_info_rec zx_upg_trx_info_rec_type;
28 BEGIN
29 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
30
31 IF (g_level_procedure >= g_current_runtime_level ) THEN
32 FND_LOG.STRING(g_level_procedure,
33 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly.BEGIN',
34 'ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly(+).wrapper');
35 END IF;
36
37 x_return_status := FND_API.G_RET_STS_SUCCESS;
38
39 l_upg_trx_info_rec.application_id := p_application_id;
40 l_upg_trx_info_rec.entity_code := p_entity_code;
41 l_upg_trx_info_rec.event_class_code := p_event_class_code;
42 l_upg_trx_info_rec.trx_id := p_trx_id;
43
44 upgrade_trx_on_fly( l_upg_trx_info_rec, x_return_status);
45
46 EXCEPTION
47 WHEN OTHERS THEN
48 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
49 IF (g_level_unexpected >= g_current_runtime_level ) THEN
50 FND_LOG.STRING(g_level_unexpected,
51 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly',
52 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
53 FND_LOG.STRING(g_level_unexpected,
54 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly.END',
55 'ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly(-)');
56 END IF;
57
58 END;
59
60
61
62 PROCEDURE upgrade_trx_on_fly(
63 p_upg_trx_info_rec IN zx_upg_trx_info_rec_type,
64 x_return_status OUT NOCOPY VARCHAR2
65 ) AS
66
67 BEGIN
68 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
69
70 IF (g_level_procedure >= g_current_runtime_level ) THEN
71 FND_LOG.STRING(g_level_procedure,
72 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly.BEGIN',
73 'ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly(+)');
74 END IF;
75
76 x_return_status := FND_API.G_RET_STS_SUCCESS;
77
78 IF (g_level_statement >= g_current_runtime_level ) THEN
79 FND_LOG.STRING(g_level_statement,
80 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly',
81 'p_upg_trx_info_rec.application_id: '||
82 p_upg_trx_info_rec.application_id );
83 END IF;
84
85 IF p_upg_trx_info_rec.application_id = 222 THEN
86 ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_ar(
87 p_upg_trx_info_rec => p_upg_trx_info_rec,
88 x_return_status => x_return_status
89 );
90 ELSIF p_upg_trx_info_rec.application_id = 200 THEN
91 ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_ap(
92 p_upg_trx_info_rec => p_upg_trx_info_rec,
93 x_return_status => x_return_status
94 );
95
96 ELSIF p_upg_trx_info_rec.application_id = 201 THEN
97 ZX_ON_FLY_TRX_UPGRADE_PO_PKG.upgrade_trx_on_fly_po(
98 p_upg_trx_info_rec => p_upg_trx_info_rec,
99 x_return_status => x_return_status
100 );
101
102 ELSE
103 IF (g_level_statement >= g_current_runtime_level ) THEN
104 FND_LOG.STRING(g_level_statement,
105 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly',
106 'On the fly upgrade currently not support product: '||
107 p_upg_trx_info_rec.application_id );
108 END IF;
109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
110 END IF;
111
112 IF (g_level_procedure >= g_current_runtime_level ) THEN
113 FND_LOG.STRING(g_level_procedure,
114 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly',
115 'x_return_status: '|| x_return_status);
116 FND_LOG.STRING(g_level_procedure,
117 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly.END',
118 'ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly(-)');
119 END IF;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124 IF (g_level_unexpected >= g_current_runtime_level ) THEN
125 FND_LOG.STRING(g_level_unexpected,
126 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly',
127 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
128 FND_LOG.STRING(g_level_unexpected,
129 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly.END',
130 'ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly(-)');
131 END IF;
132
133 END;
134
135 -------------------------------------------------------------------------------
136 -- PUBLIC PROCEDURE
137 -- upgrade_trx_on_fly_blk
138 --
139 -- DESCRIPTION
140 -- handle bulk on the fly migration, called from validate and default API
141 --
142 -- NOTE
143 -- in validation API, the first validation done is to check if any other
144 -- doc missing, if yes, this API will be called.
145 -------------------------------------------------------------------------------
146
147 PROCEDURE upgrade_trx_on_fly_blk(
148 x_return_status OUT NOCOPY VARCHAR2
149 ) AS
150 -- Bug 7637302 Added Union condition to fetch other doc application id for processing
151 CURSOR c_distinct_app_id IS
152 SELECT distinct application_id
153 FROM zx_validation_errors_gt
154 UNION
155 SELECT distinct other_doc_application_id
156 FROM zx_validation_errors_gt;
157
158 BEGIN
159
160 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
161
162 IF (g_level_procedure >= g_current_runtime_level ) THEN
163 FND_LOG.STRING(g_level_procedure,
164 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly_blk.BEGIN',
165 'ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly_blk(+)');
166 END IF;
167
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 FOR t IN c_distinct_app_id LOOP
171 IF t.application_id = 222 THEN
172 ZX_ON_FLY_TRX_UPGRADE_AR_PKG.upgrade_trx_on_fly_blk_ar(
173 x_return_status => x_return_status
174 );
175 ELSIF t.application_id = 200 THEN
176 ZX_ON_FLY_TRX_UPGRADE_AP_PKG.upgrade_trx_on_fly_blk_ap(
177 x_return_status => x_return_status
178 );
179
180 ELSIF t.application_id = 201 THEN
181 ZX_ON_FLY_TRX_UPGRADE_PO_PKG.upgrade_trx_on_fly_blk_po(
182 x_return_status => x_return_status
183 );
184
185 ELSE
186 IF (g_level_statement >= g_current_runtime_level ) THEN
187 FND_LOG.STRING(g_level_statement,
188 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly_blk',
189 'On the fly upgrade currently not support product: '||
190 t.application_id );
191 END IF;
192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
193 END IF;
194 END LOOP;
195
196 IF (g_level_procedure >= g_current_runtime_level ) THEN
197 FND_LOG.STRING(g_level_procedure,
198 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly_blk',
199 'x_return_status: '|| x_return_status);
200 FND_LOG.STRING(g_level_procedure,
201 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly_blk.END',
202 'ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly_blk(-)');
203 END IF;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
208 IF (g_level_unexpected >= g_current_runtime_level ) THEN
209 FND_LOG.STRING(g_level_unexpected,
210 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly_blk',
211 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
212 FND_LOG.STRING(g_level_unexpected,
213 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly_blk.END',
214 'ZX_ON_FLY_TRX_UPGRADE_PKG.upgrade_trx_on_fly_blk(-)');
215 END IF;
216
217 END upgrade_trx_on_fly_blk;
218
219 -------------------------------------------------------------------------------
220 -- PUBLIC PROCEDURE
221 -- is_trx_migrated
222 --
223 -- DESCRIPTION
224 -- This function is used to check if trx is already migrated
225 --
226 -- NOTE
227 -- case 1: called from ZX_TDS_APPLICABILITY_DETM_PKG.fetch_tax_lines(),
228 -- get_tax_lines_from_adjusted/applied():
229 -- trx_line_id in the rec is passed, need to check if the trx_line_id
230 -- exists in the zx_lines_det_factors table.
231 -- case 2: called from ZX_TRL_PUB_PKG.document_level_changes()
232 -- trx level update, if there is any trx line exist for the trx_id,
233 -- the trx is regarded as alreayd migrated.
234 --
235 -------------------------------------------------------------------------------
236
237 PROCEDURE is_trx_migrated(
238 p_upg_trx_info_rec IN zx_upg_trx_info_rec_type,
239 x_trx_migrated_b OUT NOCOPY BOOLEAN,
240 x_return_status OUT NOCOPY VARCHAR2
241 ) AS
242 l_count NUMBER;
243 BEGIN
244
245 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
246
247 IF (g_level_procedure >= g_current_runtime_level ) THEN
248 FND_LOG.STRING(g_level_procedure,
249 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.is_trx_migrated.BEGIN',
250 'ZX_ON_FLY_TRX_UPGRADE_PKG.is_trx_migrated(+)');
251 END IF;
252 x_return_status := FND_API.G_RET_STS_SUCCESS;
253
254 x_trx_migrated_b := TRUE;
255 IF p_upg_trx_info_rec.TRX_LINE_ID IS NOT NULL THEN
256 SELECT count(*) into l_count
257 FROM zx_lines_det_factors
258 WHERE application_id = p_upg_trx_info_rec.application_id
259 AND event_class_code = p_upg_trx_info_rec.event_class_code
260 AND entity_code = p_upg_trx_info_rec.entity_code
261 AND trx_id = p_upg_trx_info_rec.trx_id
262 AND trx_line_id = p_upg_trx_info_rec.trx_line_id
263 AND trx_level_type = p_upg_trx_info_rec.trx_level_type;
264 ELSE
265 SELECT count(*) into l_count
266 FROM zx_lines_det_factors
267 WHERE application_id = p_upg_trx_info_rec.application_id
268 AND event_class_code = p_upg_trx_info_rec.event_class_code
269 AND entity_code = p_upg_trx_info_rec.entity_code
270 AND trx_id = p_upg_trx_info_rec.trx_id;
271 END IF;
272
273 IF l_count = 0 THEN
274 x_trx_migrated_b := FALSE;
275 END IF;
276
277 IF (g_level_procedure >= g_current_runtime_level ) THEN
278 FND_LOG.STRING(g_level_procedure,
279 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.is_trx_migrated.END',
280 'ZX_ON_FLY_TRX_UPGRADE_PKG.is_trx_migrated(-)');
281 END IF;
282
283 EXCEPTION
284 WHEN OTHERS THEN
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286 IF (g_level_unexpected >= g_current_runtime_level ) THEN
287 FND_LOG.STRING(g_level_unexpected,
288 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.is_trx_migrated',
289 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
290 FND_LOG.STRING(g_level_unexpected,
291 'ZX.PLSQL.ZX_ON_FLY_TRX_UPGRADE_PKG.is_trx_migrated.END',
292 'ZX_ON_FLY_TRX_UPGRADE_PKG.is_trx_migrated(-)');
293 END IF;
294 END is_trx_migrated;
295
296 END ZX_ON_FLY_TRX_UPGRADE_PKG;
297