[Home] [Help]
PACKAGE BODY: APPS.ZX_NEW_SERVICES_PKG
Source
1 PACKAGE BODY ZX_NEW_SERVICES_PKG AS
2 /* $Header: zxifnewsrvcspubb.pls 120.0.12010000.1 2009/01/09 12:10:46 smuthusa noship $ */
3
4 /* ======================================================================*
5 | Global Data Types |
6 * ======================================================================*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZX_NEW_SERVICES_PKG';
9 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
10 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
11 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
12 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
13 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
14 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
15 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
16
17 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
18 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
19 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
20 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
21 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
22 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
23 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
24 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'ZX.PLSQL.ZX_NEW_APIS_PUB.';
25
26 /* =======================================================================*
27 | PROCEDURE freeze_tax_distributions : |
28 * =======================================================================*/
29
30 PROCEDURE freeze_tax_dists_for_items
31 (p_api_version IN NUMBER,
32 p_init_msg_list IN VARCHAR2,
33 p_commit IN VARCHAR2,
34 p_validation_level IN NUMBER,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2,
38 p_transaction_rec IN OUT NOCOPY ZX_API_PUB.transaction_rec_type,
39 p_trx_line_dist_id_tbl IN ZX_API_PUB.number_tbl_type
40 ) IS
41
42 l_api_name CONSTANT VARCHAR2(30) := 'FREEZE_TAX_DISTS_FOR_ITEMS';
43 l_api_version CONSTANT NUMBER := 1.0;
44 l_return_status VARCHAR2(30);
45 l_event_class_rec ZX_API_PUB.event_class_rec_type;
46 l_init_msg_list VARCHAR2(1);
47
48 CURSOR get_event_class_info
49 IS
50 SELECT evntcls.application_id,
51 evntcls.entity_code,
52 evntcls.event_class_code,
53 evnttyp.event_type_code,
54 null, --dist.tax_event_class_code,
55 'UPDATE' tax_event_type_code,
56 'UPDATED' doc_status_code,
57 evntcls.summarization_flag,
58 evntcls.retain_summ_tax_line_id_flag
59 FROM zx_evnt_cls_mappings evntcls,
60 zx_evnt_typ_mappings evnttyp
61 WHERE p_transaction_rec.application_id = evntcls.application_id
62 AND p_transaction_rec.entity_code = evntcls.entity_code
63 AND p_transaction_rec.event_class_code = evntcls.event_class_code
64 AND evnttyp.application_id = evntcls.application_id
65 AND evnttyp.entity_code = evntcls.entity_code
66 AND evnttyp.event_class_code = evntcls.event_class_code
67 AND evnttyp.tax_event_type_code = 'UPDATE';
68
69
70 BEGIN
71
72 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
73 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||
74 '.BEGIN','ZX_NEW_SERVICES_PKG: '||l_api_name||'()+');
75 END IF;
76
77 /*--------------------------------------------------+
78 | Standard start of API savepoint |
79 +--------------------------------------------------*/
80 SAVEPOINT freeze_tax_dists_for_itms_PVT;
81
82 /*--------------------------------------------------+
83 | Standard call to check for call compatibility |
84 +--------------------------------------------------*/
85 IF NOT FND_API.Compatible_API_Call( l_api_version,
86 p_api_version,
87 l_api_name,
88 G_PKG_NAME
89 ) THEN
90 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
91 END IF;
92
93 /*--------------------------------------------------------------+
94 | Initialize message list if p_init_msg_list is set to TRUE |
95 +--------------------------------------------------------------*/
96 IF p_init_msg_list is null THEN
97 l_init_msg_list := FND_API.G_FALSE;
98 ELSE
99 l_init_msg_list := p_init_msg_list;
100 END IF;
101
102 IF FND_API.to_Boolean(l_init_msg_list) THEN
103 FND_MSG_PUB.initialize;
104 END IF;
105
106 /*-----------------------------------------+
107 | Initialize return status to SUCCESS |
108 +-----------------------------------------*/
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110
111 /*-----------------------------------------+
112 | Populate Global Variable |
113 +-----------------------------------------*/
114 ZX_API_PUB.G_PUB_SRVC := l_api_name;
115 ZX_API_PUB.G_DATA_TRANSFER_MODE := 'PLS';
116 ZX_API_PUB.G_EXTERNAL_API_CALL := 'N';
117
118 -- Get Event Class Info.
119 --
120 OPEN get_event_class_info;
121 FETCH get_event_class_info INTO
122 l_event_class_rec.APPLICATION_ID,
123 l_event_class_rec.ENTITY_CODE,
124 l_event_class_rec.EVENT_CLASS_CODE,
125 l_event_class_rec.EVENT_TYPE_CODE,
126 l_event_class_rec.TAX_EVENT_CLASS_CODE,
127 l_event_class_rec.TAX_EVENT_TYPE_CODE,
128 l_event_class_rec.DOC_STATUS_CODE,
129 l_event_class_rec.summarization_flag,
130 l_event_class_rec.retain_summ_tax_line_id_flag;
131
132 IF get_event_class_info%notfound THEN
133 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
134 FND_LOG.STRING(G_LEVEL_STATEMENT,
135 G_MODULE_NAME || l_api_name, G_PKG_NAME||':'||
136 l_api_name ||': Event Class Info not retreived');
137 END IF;
138 END IF;
139
140 CLOSE get_event_class_info;
141
142 /*-----------------------------------------+
143 | Get the event id for the whole document |
144 +-----------------------------------------*/
145 SELECT ZX_LINES_DET_FACTORS_S.NEXTVAL
146 INTO l_event_class_rec.event_id
147 FROM dual;
148
149 /*------------------------------------------------+
150 | Update zx_lines_det_factors |
151 +------------------------------------------------*/
152 FORALL i IN NVL(p_trx_line_dist_id_tbl.FIRST,0) ..NVL(p_trx_line_dist_id_tbl.LAST, -1)
153 UPDATE ZX_LINES_DET_FACTORS
154 SET EVENT_TYPE_CODE = p_transaction_rec.event_type_code,
155 TAX_EVENT_TYPE_CODE = p_transaction_rec.tax_event_type_code,
156 EVENT_ID = l_event_class_rec.event_id,
157 DOC_EVENT_STATUS = l_event_class_rec.doc_status_code
158 WHERE APPLICATION_ID = p_transaction_rec.APPLICATION_ID
159 AND ENTITY_CODE = p_transaction_rec.ENTITY_CODE
160 AND EVENT_CLASS_CODE = p_transaction_rec.EVENT_CLASS_CODE
161 AND TRX_ID = p_transaction_rec.TRX_ID
162 AND (TRX_ID, TRX_LINE_ID, TRX_LEVEL_TYPE) IN
163 (SELECT dist.trx_id, dist.trx_line_id, dist.trx_level_type
164 FROM zx_rec_nrec_dist dist
165 WHERE application_id = p_transaction_rec.application_id
166 AND entity_code = p_transaction_rec.entity_code
167 AND event_class_code = p_transaction_rec.event_class_code
168 AND trx_id = p_transaction_rec.trx_id
169 AND trx_line_dist_id = p_trx_line_dist_id_tbl(i)
170 );
171
172 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
173 FND_LOG.STRING(G_LEVEL_PROCEDURE,
174 G_MODULE_NAME||l_api_name,
175 'ZX_NEW_SERVICES_PKG: '||l_api_name||'()+');
176 END IF;
177
178 -- Initialize API return status to success
179 x_return_status := FND_API.G_RET_STS_SUCCESS;
180
181 FORALL i IN NVL(p_trx_line_dist_id_tbl.FIRST,0) ..NVL(p_trx_line_dist_id_tbl.LAST, -1)
182 UPDATE ZX_REC_NREC_DIST
183 SET freeze_flag = 'Y',
184 event_type_code = l_event_class_rec.event_type_code,
185 tax_event_type_code = l_event_class_rec.tax_event_type_code
186 WHERE application_id = p_transaction_rec.application_id
187 AND entity_code = p_transaction_rec.entity_code
188 AND event_class_code = p_transaction_rec.event_class_code
189 AND trx_id = p_transaction_rec.trx_id
190 AND trx_line_dist_id = p_trx_line_dist_id_tbl(i);
191
192 FORALL i IN NVL(p_trx_line_dist_id_tbl.FIRST,0) ..NVL(p_trx_line_dist_id_tbl.LAST, -1)
193 UPDATE ZX_LINES ZL
194 SET associated_child_frozen_flag ='Y',
195 event_type_code = l_event_class_rec.event_type_code,
196 tax_event_type_code = l_event_class_rec.tax_event_type_code,
197 doc_event_status = l_event_class_rec.doc_status_code
198 WHERE TAX_LINE_ID IN (SELECT ZD.TAX_LINE_ID
199 FROM ZX_REC_NREC_DIST ZD
200 WHERE application_id = p_transaction_rec.application_id
201 AND entity_code = p_transaction_rec.entity_code
202 AND event_class_code = p_transaction_rec.event_class_code
203 AND trx_id = p_transaction_rec.trx_id
204 AND trx_line_dist_id = p_trx_line_dist_id_tbl(i)
205 );
206
207 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
208 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
209 FND_LOG.STRING(G_LEVEL_PROCEDURE,
210 G_MODULE_NAME||l_api_name,
211 'Exception:' ||SQLCODE||';'||SQLERRM);
212 FND_LOG.STRING(G_LEVEL_PROCEDURE,
213 G_MODULE_NAME||l_api_name,
214 'Return Status = '||l_return_status);
215 END IF;
216 RAISE FND_API.G_EXC_ERROR;
217 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
218 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
219 FND_LOG.STRING(G_LEVEL_PROCEDURE,
220 G_MODULE_NAME||l_api_name,
221 'Exception:' ||SQLCODE||';'||SQLERRM);
222
223 FND_LOG.STRING(G_LEVEL_PROCEDURE,
224 G_MODULE_NAME||l_api_name,
225 'Return Status = '||l_return_status);
226 END IF;
227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
228 END IF;
229
230 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
231 FND_LOG.STRING(G_LEVEL_PROCEDURE,
232 G_MODULE_NAME||l_api_name,
233 'ZX_NEW_SERVICES_PKG: '||l_api_name||'()-');
234 END IF;
235
236 EXCEPTION
237 WHEN FND_API.G_EXC_ERROR THEN
238 ROLLBACK TO freeze_tax_dists_for_itms_PVT;
239 x_return_status := FND_API.G_RET_STS_ERROR ;
240 /*---------------------------------------------------------+
241 | FND_MSG_PUB.Count_And_Get used to get the count of mesg.|
242 | in the message stack. If there is only one message in |
243 | the stack it retrieves this message |
244 +---------------------------------------------------------*/
245 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
246 p_count => x_msg_count,
247 p_data => x_msg_data
248 );
249
250 IF ( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
251 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name,'');
252 END IF;
253
254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
255 ROLLBACK TO freeze_tax_dists_for_itms_PVT;
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
257 FND_MESSAGE.SET_NAME ('ZX','ZX_UNEXPECTED_ERROR');
258 FND_MSG_PUB.Add;
259 /*---------------------------------------------------------+
260 | FND_MSG_PUB.Count_And_Get used to get the count of mesg.|
261 | in the message stack. If there is only one message in |
262 | the stack it retrieves this message |
263 +---------------------------------------------------------*/
264 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
265 p_count => x_msg_count,
266 p_data => x_msg_data
267 );
268 IF ( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
269 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name,'');
270 END IF;
271
272 WHEN OTHERS THEN
273 ROLLBACK TO freeze_tax_dists_for_itms_PVT;
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
275 FND_MESSAGE.SET_NAME ('ZX','ZX_UNEXPECTED_ERROR');
276 FND_MSG_PUB.Add;
277 /*---------------------------------------------------------+
278 | FND_MSG_PUB.Count_And_Get used to get the count of mesg.|
279 | in the message stack. If there is only one message in |
280 | the stack it retrieves this message |
281 +---------------------------------------------------------*/
282 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
283 p_count => x_msg_count,
284 p_data => x_msg_data
285 );
286 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
287 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
288 END IF;
289 END freeze_tax_dists_for_items;
290
291 END ZX_NEW_SERVICES_PKG;