[Home] [Help]
PACKAGE BODY: APPS.CST_PERITEMCOSTCHANGERPT_PVT
Source
1 PACKAGE BODY CST_PerItemCostChangeRpt_PVT AS
2 /* $Header: CSTVPICB.pls 120.2.12010000.3 2008/12/11 02:26:45 anjha ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_PerItemCostChangeRpt_PVT';
5 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 -- API name : generateXML
8 -- Description : The API is directly called by the Periodic Item Cost
9 -- change report.
10 -- Parameters :
11 -- p_legal_entity_id : Legal Entity ID
12 -- p_cost_type_id : Cost Type ID
13 -- p_pac_period_id : PAC Period ID
14 -- p_cost_group_id : Cost Group ID
15 -- p_category_set_id : Category Set ID
16 -- p_item_master_org_id : Item Master Organization ID
17 -- p_category_number : Dummy Parameter
18 -- p_category_from : From Category Name
19 -- p_category_to : To Category Name
20 -- p_item_from : From Item Name
21 -- p_item_to : To Item Name
22 -- p_qty_precision : Precision
23
24 PROCEDURE generateXML
25 (errcode OUT NOCOPY VARCHAR2,
26 errno OUT NOCOPY NUMBER,
27 p_legal_entity_id IN NUMBER,
28 p_cost_type_id IN NUMBER,
29 p_pac_period_id IN NUMBER,
30 p_cost_group_id IN NUMBER,
31 p_category_set_id IN NUMBER,
32 p_item_master_org_id IN NUMBER,
33 p_category_number IN NUMBER, /* Dummy */
34 p_category_from IN VARCHAR2,
35 p_category_to IN VARCHAR2,
36 p_item_from IN VARCHAR2,
37 p_item_to IN VARCHAR2,
38 p_qty_precision IN NUMBER)
39 IS
40 l_api_name CONSTANT VARCHAR2(30) := 'generateXML';
41 l_xml_doc CLOB;
42 l_xml_temp CLOB;
43 l_amount NUMBER;
44 l_offset NUMBER;
45 l_length NUMBER;
46 l_buffer VARCHAR2(32767);
47 l_stmt_num NUMBER;
48 l_ref_cur SYS_REFCURSOR;
49 l_ctx NUMBER;
50 l_return_status VARCHAR2(1);
51 l_msg_count NUMBER;
52 l_msg_data VARCHAR2(2000);
53 l_success BOOLEAN;
54 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
55 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
56
57 l_item_where_clause VARCHAR2 (2400);
58 l_category_where_clause VARCHAR2 (2400);
59
60 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
61 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
62 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
63 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
64 /*Bug 7346254*/
65 l_encoding VARCHAR2(20);
66 l_xml_header VARCHAR2(100);
67 BEGIN
68 IF (l_pLog) THEN
69 FND_LOG.STRING ( FND_LOG.LEVEL_PROCEDURE,
70 l_module || '.begin',
71 '>>> ' || l_api_name || ':Parameters:
72 Legal Entity id: ' || p_legal_entity_id || '
73 Cost Type ID: ' || p_cost_type_id || '
74 Cost Group ID: ' || p_cost_group_id || '
75 PAC Period ID: ' || p_pac_period_id || '
76 Item Master Org ID: ' || p_item_master_org_id || '
77 Category Set ID: ' || p_category_set_id || '
78 Category Number: ' || p_category_number || '
79 Category From: ' || p_category_from || '
80 Category To: ' || p_category_to || '
81 From Item: ' || p_item_from || '
82 To Item: ' || p_item_to || '
83 Quantity Precision: ' || p_qty_precision);
84 END IF;
85
86 l_stmt_num := 10;
87 /* Initialze variables */
88 DBMS_LOB.createtemporary(l_xml_doc, TRUE);
89
90 /*Bug 7346254: The following 3 lines of code ensures that XML data generated here uses the right encoding*/
91 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
92 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
93 DBMS_LOB.writeappend (l_xml_doc, length(l_xml_header), l_xml_header);
94
95 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
96 DBMS_LOB.writeappend (l_xml_doc, 8, '<REPORT>');
97 FND_MSG_PUB.initialize;
98
99 /* Add Parameters */
100 l_stmt_num := 20;
101 add_parameters (p_api_version => 1.0,
102 p_init_msg_list => FND_API.G_FALSE,
103 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
104 x_return_status => l_return_status,
105 x_msg_count => l_msg_count,
106 x_msg_data => l_msg_data,
107 i_legal_entity_id => p_legal_entity_id,
108 i_cost_type_id => p_cost_type_id,
109 i_cost_group_id => p_cost_group_id,
110 i_pac_period_id => p_pac_period_id,
111 i_category_set_id => p_category_set_id,
112 i_item_master_org_id => p_item_master_org_id,
113 i_category_from => p_category_from,
114 i_category_to => p_category_to,
115 i_item_from => p_item_from,
116 i_item_to => p_item_to,
117 x_xml_doc => l_xml_doc);
118
119 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
121 END IF;
122
123 l_stmt_num := 30;
124 OPEN l_ref_cur FOR
125 'SELECT mc.concatenated_segments CATEGORY,
126 msi.concatenated_segments ITEM,
127 cppb.inventory_item_id INVENTORY_ITEM_ID,
128 cppb.txn_category,
129 ml.meaning STEP,
130 round (nvl(cppb.txn_category_qty,0), :p_qty_precision) CATEGORY_QUANTITY,
131 round (nvl(cppb.period_quantity, 0), :p_qty_precision) CUMULATIVE_QUANTITY,
132 round (sum (nvl(cppb.periodic_cost,0)), 2) PERIODIC_COST,
133 round (sum (nvl(cppb.txn_category_value,0)), 2) AMOUNT,
134 round (sum (nvl(cppb.period_balance,0)), 2) CAV,
135 round (sum (nvl(cppb.variance_amount,0)), 2) VARIANCE_AMOUNT
136 FROM cst_pac_period_balances cppb
137 , mfg_lookups ml
138 , mtl_item_categories mic
139 , mtl_categories_kfv mc
140 , mtl_system_items_kfv msi
141 WHERE CPPB.cost_group_id = :p_cost_group_id
142 AND CPPB.pac_period_id = :p_pac_period_id
143 AND CPPB.inventory_item_id = MSI.inventory_item_id
144 AND ml.lookup_type = ''CST_PAC_TXN_CATEGORY''
145 /* The MFG LOOKUP exhibits inconsistent behavior with fractional number
146 as lookup code. To ensure that the txn category is between 2 and 3 but
147 also use the mfg lookup for meaning mfg lookup has been seeded with 11
148 for txn_category 2.5 */
149 AND ml.lookup_code = decode(cppb.txn_category,2.5,11,cppb.txn_category)
150 AND mic.inventory_item_id = cppb.inventory_item_id
151 AND mic.organization_id = :p_item_master_org_id
152 AND mic.category_set_id = :p_category_set_id
153 AND mc.category_id = mic.category_id
154 AND msi.organization_id = mic.organization_id
155 AND msi.inventory_item_id = cppb.inventory_item_id
156 AND msi.concatenated_segments between
157 nvl(:p_item_from, msi.concatenated_segments)
158 AND nvl(:p_item_to, msi.concatenated_segments)
159 AND mc.concatenated_segments between
160 nvl(:p_category_from, mc.concatenated_segments)
161 AND nvl(:p_category_to, mc.concatenated_segments)
162 GROUP BY mc.concatenated_segments,
163 msi.concatenated_segments,
164 cppb.inventory_item_id,
165 CPPB.txn_category,
166 ml.meaning,
167 cppb.txn_category_qty,
168 cppb.period_quantity
169 ORDER BY mc.concatenated_segments, msi.concatenated_segments, cppb.txn_category'
170 USING p_qty_precision, p_qty_precision, p_cost_group_id, p_pac_period_id,
171 p_item_master_org_id, p_category_set_id, p_item_from, p_item_to,
172 p_category_from, p_category_to;
173
174
175 /* create new context */
176 l_stmt_num := 40;
177 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
178 DBMS_XMLGEN.setRowSetTag (l_ctx,'ROWSET');
179 DBMS_XMLGEN.setRowTag (l_ctx, 'ROW');
180
181 /* get XML */
182 l_stmt_num := 50;
183 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
184
185 IF (DBMS_XMLGEN.getNumRowsProcessed(l_ctx) > 0) THEN
186 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
187 pattern => '>',
188 offset => 1,
189 nth => 1);
190 DBMS_LOB.erase (l_xml_temp, l_offset, 1);
191 DBMS_LOB.append (l_xml_doc, l_xml_temp);
192 ELSE
193 DBMS_LOB.writeappend (l_xml_doc, 10, '<NO_DATA/>');
194 END IF;
195
196
197 DBMS_LOB.writeappend (l_xml_doc, 9, '</REPORT>');
198
199 /* write to output file */
200 l_stmt_num := 60;
201 l_length := nvl(DBMS_LOB.getlength(l_xml_doc), 0);
202 l_offset := 1;
203 l_amount := 16383; /*Bug 7346238*/
204
205 LOOP
206 EXIT WHEN l_length <= 0;
207 DBMS_LOB.read (l_xml_doc, l_amount, l_offset, l_buffer);
208 FND_FILE.PUT (FND_FILE.OUTPUT, l_buffer);
209 l_length := l_length - l_amount;
210 l_offset := l_offset + l_amount;
211 END LOOP;
212
213 IF (l_eventLog) THEN
214 FND_LOG.STRING (FND_LOG.LEVEL_EVENT,
215 l_module || '.' || l_stmt_num,
216 'Completed writing to output file');
217 END IF;
218
219 l_stmt_num := 70;
220 DBMS_LOB.FREETEMPORARY (l_xml_doc);
221 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', 'Request Completed Successfully');
222
223 IF (l_pLog) THEN
224 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
225 l_module || '.end',
226 '<<< ' || l_api_name);
227 END IF;
228
229 EXCEPTION
230 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
231 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count,
232 p_data => l_msg_data);
233
234 CST_UTILITY_PUB.writelogmessages (p_api_version => 1.0,
235 p_msg_count => l_msg_count,
236 p_msg_data => l_msg_data,
237 x_return_status=> l_return_status);
238 l_msg_data := SUBSTRB (SQLERRM,1,240);
239 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
240 IF (l_uLog) THEN
241 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
242 l_module || '.' || l_stmt_num,
243 l_msg_data);
244 END IF;
245 WHEN OTHERS THEN
246 IF (l_uLog) THEN
247 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
248 l_module || '.' || l_stmt_num,
249 SUBSTRB (SQLERRM , 1 , 240));
250 END IF;
251 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
252 THEN
253 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,l_api_name);
254 END IF;
255 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
256 p_data => l_msg_data);
257
258 CST_UTILITY_PUB.writelogmessages (p_api_version => 1.0,
259 p_msg_count => l_msg_count,
260 p_msg_data => l_msg_data,
261 x_return_status=> l_return_status);
262 l_msg_data := SUBSTRB (SQLERRM,1, 240);
263 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
264 END generateXML;
265
266 PROCEDURE add_parameters (
267 p_api_version IN NUMBER,
268 p_init_msg_list IN VARCHAR2,
269 p_validation_level IN NUMBER,
270 x_return_status OUT NOCOPY VARCHAR2,
271 x_msg_count OUT NOCOPY NUMBER,
272 x_msg_data OUT NOCOPY VARCHAR2,
273 i_legal_entity_id IN NUMBER,
274 i_cost_type_id IN NUMBER,
275 i_pac_period_id IN NUMBER,
276 i_cost_group_id IN NUMBER,
277 i_category_set_id IN NUMBER,
278 i_item_master_org_id IN NUMBER,
279 i_category_from IN VARCHAR2,
280 i_category_to IN VARCHAR2,
281 i_item_from IN VARCHAR2,
282 i_item_to IN VARCHAR2,
283 x_xml_doc IN OUT NOCOPY CLOB)
284 IS
285 l_api_name CONSTANT VARCHAR2(30) := 'add_parameters';
286 l_api_version CONSTANT NUMBER := 1.0;
287 l_ref_cur SYS_REFCURSOR;
288 l_ctx NUMBER;
289 l_xml_temp CLOB;
290 l_offset PLS_INTEGER;
291 l_stmt_num NUMBER;
292 l_legal_entity HR_LEGAL_ENTITIES.NAME%TYPE;
293 l_cost_type CST_COST_TYPES.COST_TYPE%TYPE;
294 l_cost_group CST_COST_GROUPS.COST_GROUP%TYPE;
295 l_pac_period CST_PAC_PERIODS.PERIOD_NAME%TYPE;
296 l_category_set MTL_CATEGORY_SETS.CATEGORY_SET_NAME%TYPE;
297 l_item VARCHAR2 (2000);
298 l_category VARCHAR2 (2000);
299 l_item_master_org MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
300 l_currency_code VARCHAR2(15);
301
302 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
303 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
304
305 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
306 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
307 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
308 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
309
310 BEGIN
311
312 IF (l_pLog) THEN
313 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
314 l_module || '.begin',
315 '>>> ' || l_api_name);
316 END IF;
317
318 -- Standard call to check for call compatibility.
319 IF NOT FND_API.Compatible_API_Call (l_api_version,
320 p_api_version,
321 l_api_name,
322 G_PKG_NAME )
323 THEN
324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
325 END IF;
326
327 -- Initialize message list if p_init_msg_list is set to TRUE.
328 IF FND_API.to_Boolean( p_init_msg_list ) THEN
329 FND_MSG_PUB.initialize;
330 END IF;
331
332 -- Initialize API return status to success
333 x_return_status := FND_API.G_RET_STS_SUCCESS;
334
335 /* Initialize */
336 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
337
338 /* Get Legal Entity Name */
339 l_stmt_num := 10;
340 SELECT name
341 INTO l_legal_entity
342 FROM xle_entity_profiles
343 WHERE legal_entity_id = i_legal_entity_id;
344
345 /* Get Cost Type Name */
346 l_stmt_num := 20;
347 SELECT cost_type
348 INTO l_cost_type
349 FROM cst_cost_types
350 WHERE cost_type_id = i_cost_type_id;
351
352 /* Get Cost Group Name */
353 l_stmt_num := 30;
354 SELECT cost_group
355 INTO l_cost_group
356 FROM cst_cost_groups
357 WHERE cost_group_id = i_cost_group_id
358 AND cost_group_type = 2;
359
360 /* Get PAC Period Name */
361 l_stmt_num := 40;
362 SELECT period_name
363 INTO l_pac_period
364 FROM cst_pac_periods
365 WHERE pac_period_id = i_pac_period_id
366 AND legal_entity = i_legal_entity_id
367 AND cost_type_id = i_cost_type_id;
368
369 /* Get Category Set Name */
370 l_stmt_num := 50;
371 SELECT category_set_name
372 INTO l_category_set
373 FROM mtl_category_sets
374 WHERE category_set_id = i_category_set_id;
375
376 /* Get Item Master Organization Name */
377 l_stmt_num := 60;
378 SELECT organization_code
379 INTO l_item_master_org
380 FROM mtl_parameters
381 WHERE organization_id = i_item_master_org_id;
382
383 /* Get Currency Code */
384 l_stmt_num := 65;
385 SELECT currency_code
386 INTO l_currency_code
387 FROM gl_ledger_le_v
388 WHERE legal_entity_id = i_legal_entity_id
389 AND ledger_category_code = 'PRIMARY';
390
391 l_stmt_num := 70;
392 OPEN l_ref_cur FOR
393 'SELECT :l_legal_entity LEGAL_ENTITY,
394 :l_cost_type COST_TYPE,
395 :l_pac_period PAC_PERIOD,
396 :l_cost_group COST_GROUP,
397 :l_category_set CATEGORY_SET,
398 :l_item_master_org ITEM_MASTER_ORG,
399 :i_category_from CATEGORY_FROM,
400 :i_category_to CATEGORY_TO,
401 :i_item_from ITEM_FROM,
402 :i_item_to ITEM_TO,
403 :l_currency_code CURRENCY_CODE
404 FROM dual'
405 USING l_legal_entity, l_cost_type, l_pac_period, l_cost_group,
406 l_category_set, l_item_master_org, i_category_from, i_category_to,
407 i_item_from, i_item_to, l_currency_code;
408
409 /* create new context */
410 l_stmt_num := 80;
411 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
412 DBMS_XMLGEN.setRowSetTag (l_ctx,'PARAMETERS');
413 DBMS_XMLGEN.setRowTag (l_ctx, NULL);
414
415 /* get XML */
416 l_stmt_num := 90;
417 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
418
419 l_stmt_num := 100;
420 /* Add the XML header as the first line of output. add data to end */
421 IF (DBMS_XMLGEN.getNumRowsProcessed(l_ctx) > 0) THEN
422 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
423 pattern => '>',
424 offset => 1,
425 nth => 1);
426 /*Bug 7346254*/
427 /*DBMS_LOB.copy (x_xml_doc, l_xml_temp, l_offset + 1);
428 DBMS_LOB.writeappend (x_xml_doc, 8, '<REPORT>');*/
429 DBMS_LOB.erase(l_xml_temp, l_offset, 1);
430 DBMS_LOB.append (x_xml_doc, l_xml_temp);
431 END IF;
432
433 l_stmt_num := 110;
434 /* close context and free memory */
435 DBMS_XMLGEN.closeContext(l_ctx);
436 CLOSE l_ref_cur;
437 DBMS_LOB.FREETEMPORARY (l_xml_temp);
438
439 -- Standard call to get message count and if count is 1, get message info.
440 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
441 p_data => x_msg_data);
442
443 IF (l_pLog) THEN
444 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
445 l_module || '.end',
446 '<<< ' || l_api_name);
447 END IF;
448 EXCEPTION
449 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
451 FND_MSG_PUB.Count_And_Get
452 ( p_count => x_msg_count,
453 p_data => x_msg_data
454 );
455 WHEN OTHERS THEN
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
457 IF (l_uLog) THEN
458 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
459 l_module || '.' || l_stmt_num,
460 SUBSTRB (SQLERRM , 1 , 240));
461 END IF;
462
463 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
464 THEN
465 FND_MSG_PUB.Add_Exc_Msg
466 ( G_PKG_NAME,
467 l_api_name
468 );
469 END IF;
470 FND_MSG_PUB.Count_And_Get
471 (p_count => x_msg_count,
472 p_data => x_msg_data
473 );
474 END add_parameters;
475 END CST_PerItemCostChangeRpt_PVT;