[Home] [Help]
PACKAGE BODY: APPS.CST_PENDINGTXNSREPORT_PVT
Source
1 PACKAGE BODY CST_PendingTxnsReport_PVT AS
2 /* $Header: CSTVPTRB.pls 120.17 2010/12/12 17:28:37 vkatakam ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_PendingTxnsReport_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 Period Close Pending
9 -- transactions report.
10 -- Parameters :
11 -- p_org_id : Organization ID
12 -- p_period_id : Inventory accounting period for which XML data is generated
13 -- p_resolution_type:
14 -- 1 => All
15 -- 2 => Resolution Required
16 -- 3 => Resolution Recommended
17 -- p_transaction_type:
18 -- 1 => All
19 -- 2 => Unprocessed Material transactions
20 -- 3 => Uncosted Material transactions
21 -- 4 => Uncosted WIP transactions
22 -- 5 => Pending WSM interface transactions
23 -- 6 => Pending Receiving transactions
24 -- 7 => Pending Material Interface transactions
25 -- 8 => Pending Shop Floor Move transactions
26 -- 9 => Incomplete eAM Work Orders
27 -- 10 => Pending Shipping tranactions
28 PROCEDURE generateXML
29 (errcode OUT NOCOPY VARCHAR2,
30 errno OUT NOCOPY NUMBER,
31 p_org_id IN NUMBER,
32 p_period_id IN NUMBER,
33 p_resolution_type IN NUMBER,
34 p_transaction_type IN NUMBER)
35 IS
36 l_api_name CONSTANT VARCHAR2(30) := 'generateXML';
37 l_xml_doc CLOB;
38 l_period_start_date DATE;
39 l_period_end_date DATE;
40 l_amount NUMBER;
41 l_offset NUMBER;
42 l_length NUMBER;
43 l_buffer VARCHAR2(32767);
44 l_stmt_num NUMBER;
45
46 l_return_status VARCHAR2(1);
47 l_msg_count NUMBER;
48 l_msg_data VARCHAR2(2000);
49 l_success BOOLEAN;
50 l_record_count NUMBER;
51 l_temp_count NUMBER;
52 l_shipping_txn_hook NUMBER;
53
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_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
58 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
59 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
60 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
61 /*Bug 7305146*/
62 l_encoding VARCHAR2(20);
63 l_xml_header VARCHAR2(100);
64 l_legal_entity NUMBER := 0;
65
66 BEGIN
67
68 IF (l_pLog) THEN
69 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
70 l_module || '.begin',
71 '>>> ' || l_api_name || ':Parameters:' ||
72 'Org id: ' || p_org_id ||
73 '; period id: ' || p_period_id ||
74 '; resolution type: ' || p_resolution_type ||
75 '; transaction type: ' || p_transaction_type);
76 END IF;
77 /* Initialze variables */
78 DBMS_LOB.createtemporary(l_xml_doc, TRUE);
82 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
79
80 /*Bug 7305146: The following 3 lines of code ensures that XML data generated here uses the right encoding*/
81 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
83 DBMS_LOB.writeappend (l_xml_doc, length(l_xml_header), l_xml_header);
84
85 DBMS_LOB.writeappend (l_xml_doc, 8, '<REPORT>');
86 l_record_count := 0;
87
88 l_stmt_num := 10;
89 SELECT period_start_date, schedule_close_date
90 INTO l_period_start_date, l_period_end_date
91 FROM org_acct_periods
92 WHERE acct_period_id = p_period_id
93 AND organization_id = p_org_id;
94
95 l_stmt_num := 15;
96 SELECT legal_entity
97 INTO l_legal_entity
98 FROM cst_acct_info_v
99 WHERE organization_id = p_org_id;
100
101 l_stmt_num := 17;
102 l_period_end_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
103 l_period_end_date,
104 l_legal_entity);
105
106 l_period_end_date := l_period_end_date + 1 - (1/(24*3600));
107
108 /* Initialize message stack */
109 FND_MSG_PUB.initialize;
110
111 /* Add Parameters */
112
113 l_stmt_num := 20;
114 add_parameters (p_api_version => 1.0,
115 p_init_msg_list => FND_API.G_FALSE,
116 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
117 x_return_status => l_return_status,
118 x_msg_count => l_msg_count,
119 x_msg_data => l_msg_data,
120 i_org_id => p_org_id,
121 i_period_id => p_period_id,
122 i_resolution_type => p_resolution_type,
123 i_transaction_type => p_transaction_type,
124 x_xml_doc => l_xml_doc);
125
126 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128 END IF;
129
130 /* Validate which transactions need to be added for the parameter values */
131
132 IF (p_resolution_type = 1 OR ((p_resolution_type = 2) AND
133 (nvl(p_transaction_type,1) between 1 AND 5)))
134 /* Resolution Type: All or resolution required */
135 THEN
136 IF (nvl(p_transaction_type,1) = 1 OR nvl(p_transaction_type,1) = 2)
137 THEN
138 l_stmt_num := 30;
139 /* Transaction Type: All or Unprocessed MTL TRX */
140 unprocessed_mtl_trx (p_api_version => 1.0,
141 p_init_msg_list => FND_API.G_FALSE,
142 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
143 x_return_status => l_return_status,
144 x_msg_count => l_msg_count,
145 x_msg_data => l_msg_data,
146 i_period_end_date => l_period_end_date,
147 i_org_id => p_org_id,
148 x_record_count => l_temp_count,
149 x_xml_doc => l_xml_doc);
150
151 l_record_count := l_record_count + l_temp_count;
152
153 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 END IF;
156 END IF;
157
158 IF (nvl(p_transaction_type,1) = 1 OR nvl(p_transaction_type,1) = 3)
159 THEN
160 /* Transaction Type: All or Uncosted MTL TRX */
161 l_stmt_num := 40;
162 uncosted_mtl_trx(p_api_version => 1.0,
163 p_init_msg_list => FND_API.G_FALSE,
164 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
165 x_return_status => l_return_status,
166 x_msg_count => l_msg_count,
167 x_msg_data => l_msg_data,
168 i_period_end_date => l_period_end_date,
169 i_org_id => p_org_id,
170 x_record_count => l_temp_count,
171 x_xml_doc => l_xml_doc);
172
173 l_record_count := l_record_count + l_temp_count;
174
175 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
177 END IF;
178 END IF;
179
180 IF (nvl(p_transaction_type,1) = 1 OR nvl(p_transaction_type,1) = 4)
181 THEN
182 /* Transaction Type: All or Uncosted WIP TRX */
183 l_stmt_num := 50;
184 uncosted_wip_trx (p_api_version => 1.0,
185 p_init_msg_list => FND_API.G_FALSE,
186 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
187 x_return_status => l_return_status,
188 x_msg_count => l_msg_count,
189 x_msg_data => l_msg_data,
190 i_period_end_date => l_period_end_date,
191 i_org_id => p_org_id,
192 x_record_count => l_temp_count,
193 x_xml_doc => l_xml_doc);
194
195 l_record_count := l_record_count + l_temp_count;
196
197 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199 END IF;
200 END IF;
201
202 IF (nvl(p_transaction_type,1) = 1 or nvl(p_transaction_type,1) = 5)
203 THEN
204 /* Transaction Type: All or Pending WSM TRX */
205 l_stmt_num := 60;
206 pending_wsm_trx (p_api_version => 1.0,
210 x_msg_count => l_msg_count,
207 p_init_msg_list => FND_API.G_FALSE,
208 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
209 x_return_status => l_return_status,
211 x_msg_data => l_msg_data,
212 i_period_end_date => l_period_end_date,
213 i_org_id => p_org_id,
214 x_record_count => l_temp_count,
215 x_xml_doc => l_xml_doc);
216
217 l_record_count := l_record_count + l_temp_count;
218
219 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221 END IF;
222 END IF;
223
224 /*Support for LCM*/
225 IF (nvl(p_transaction_type,1) = 1 or nvl(p_transaction_type,1) = 11)
226 THEN
227 /* Transaction Type: All or Pending LCM TRX */
228 l_stmt_num := 60;
229 pending_lcm_trx (p_api_version => 1.0,
230 p_init_msg_list => FND_API.G_FALSE,
231 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
232 x_return_status => l_return_status,
233 x_msg_count => l_msg_count,
234 x_msg_data => l_msg_data,
235 i_period_end_date => l_period_end_date,
236 i_org_id => p_org_id,
237 x_record_count => l_temp_count,
238 x_xml_doc => l_xml_doc);
239
240 l_record_count := l_record_count + l_temp_count;
241
242 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 END IF;
245 END IF;
246 END IF;
247
248 IF (p_resolution_type = 1 OR
249 ((p_resolution_type = 3) AND (nvl(p_transaction_type,1) IN (1, 6, 7, 8, 9))))
250 /* Resolution Type: All or resolution recommended */
251 THEN
252 IF (nvl(p_transaction_type,1) = 1 OR nvl(p_transaction_type,1) = 6)
253 THEN
254 /* Transaction Type: All or Pending RCV TRX */
255 l_stmt_num := 70;
256 pending_rcv_trx (p_api_version => 1.0,
257 p_init_msg_list => FND_API.G_FALSE,
258 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
259 x_return_status => l_return_status,
260 x_msg_count => l_msg_count,
261 x_msg_data => l_msg_data,
262 i_period_end_date => l_period_end_date,
263 i_org_id => p_org_id,
264 x_record_count => l_temp_count,
265 x_xml_doc => l_xml_doc);
266
267 l_record_count := l_record_count + l_temp_count;
268
269 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
271 END IF;
272 END IF;
273
274 IF (nvl(p_transaction_type,1) = 1 OR nvl(p_transaction_type,1) = 7)
275 THEN
276 /* Transaction Type: All or Pending material interface */
277 l_stmt_num := 80;
278 pending_mtl_interface_trx (p_api_version => 1.0,
279 p_init_msg_list => FND_API.G_FALSE,
280 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
281 x_return_status => l_return_status,
282 x_msg_count => l_msg_count,
283 x_msg_data => l_msg_data,
284 i_period_end_date => l_period_end_date,
285 i_org_id => p_org_id,
286 x_record_count => l_temp_count,
287 x_xml_doc => l_xml_doc);
288
289 l_record_count := l_record_count + l_temp_count;
290
291 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
293 END IF;
294 END IF;
295
296 IF (nvl(p_transaction_type,1) = 1 OR nvl(p_transaction_type,1) = 8)
297 THEN
298 /* Transaction Type: All or Pending WIP Move trx */
299 l_stmt_num := 90;
300 pending_wip_move_trx (p_api_version => 1.0,
301 p_init_msg_list => FND_API.G_FALSE,
302 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
303 x_return_status => l_return_status,
304 x_msg_count => l_msg_count,
305 x_msg_data => l_msg_data,
306 i_period_end_date => l_period_end_date,
307 i_org_id => p_org_id,
308 x_record_count => l_temp_count,
309 x_xml_doc => l_xml_doc);
310
311 l_record_count := l_record_count + l_temp_count;
312
313 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
315 END IF;
316 END IF;
317
318 IF (nvl(p_transaction_type,1) = 1 OR nvl(p_transaction_type,1) = 9)
319 THEN
320 /* Transaction Type: All or Incomplete workorders */
321 l_stmt_num := 100;
322 incomplete_eam_wo (p_api_version => 1.0,
326 x_msg_count => l_msg_count,
323 p_init_msg_list => FND_API.G_FALSE,
324 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
325 x_return_status => l_return_status,
327 x_msg_data => l_msg_data,
328 i_period_end_date => l_period_end_date,
329 i_org_id => p_org_id,
330 x_record_count => l_temp_count,
331 x_xml_doc => l_xml_doc);
332
333 l_record_count := l_record_count + l_temp_count;
334
335 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
337 END IF;
338 END IF;
339 END IF;
340
341 -- Check if shipping transactions are resolution required or resolution recommended
342 l_stmt_num := 110;
343 CST_PERIODCLOSEOPTION_PUB.shipping_txn_hook (p_api_version => 1.0,
344 i_org_id => p_org_id,
345 i_acct_period_id => p_period_id,
346 x_close_option => l_shipping_txn_hook,
347 x_return_status => l_return_status,
348 x_msg_count => l_msg_count,
349 x_msg_data => l_msg_data);
350 IF (l_return_status <> 0) THEN
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352 END IF;
353
354 -- l_shipping_txn_hook = 0 if shipping transactions are resolution required
355 -- = 1 if shipping transactions are resolution recommended
356 IF ((p_resolution_type = 1 OR (p_resolution_type = 2 AND l_shipping_txn_hook = 0)
357 OR (p_resolution_type = 3 AND l_shipping_txn_hook = 1)) and
358 (nvl(p_transaction_type,1) = 1 OR nvl(p_transaction_type,1) = 10))
359 THEN
360 /* Transaction Type: All or Pending Shipping trx */
361 l_stmt_num := 120;
362 pending_shipping_trx (p_api_version => 1.0,
363 p_init_msg_list => FND_API.G_FALSE,
364 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
365 x_return_status => l_return_status,
366 x_msg_count => l_msg_count,
367 x_msg_data => l_msg_data,
368 i_period_start_date => l_period_start_date,
369 i_period_end_date => l_period_end_date,
370 i_org_id => p_org_id,
371 x_record_count => l_temp_count,
372 x_xml_doc => l_xml_doc);
373
374 l_record_count := l_record_count + l_temp_count;
375
376 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
378 END IF;
379 END IF;
380
381 IF (l_record_count = 0) THEN
382 DBMS_LOB.writeappend (l_xml_doc, 10, '<NO_DATA/>');
383 END IF;
384 DBMS_LOB.writeappend (l_xml_doc, 9, '</REPORT>');
385
386 /* write to output file */
387
388 l_length := nvl(DBMS_LOB.getlength(l_xml_doc), 0);
389 l_offset := 1;
390 l_amount := 16383;
391
392 l_stmt_num := 130;
393 LOOP
394 EXIT WHEN l_length <= 0;
395 DBMS_LOB.read (l_xml_doc, l_amount, l_offset, l_buffer);
396 FND_FILE.PUT (FND_FILE.OUTPUT, l_buffer);
397 l_length := l_length - l_amount;
398 l_offset := l_offset + l_amount;
399 END LOOP;
400
401 IF (l_eventLog) THEN
402 FND_LOG.STRING (FND_LOG.LEVEL_EVENT,
403 l_module || '.' || l_stmt_num,
404 'Completed writing to output file');
405 END IF;
406
407 /* free temporary memory */
408 DBMS_LOB.FREETEMPORARY (l_xml_doc);
409 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', 'Request Completed Successfully');
410
411 IF (l_pLog) THEN
412 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
413 l_module || '.end',
414 '<<< ' || l_api_name);
415 END IF;
416
417 EXCEPTION
418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
419 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count,
420 p_data => l_msg_data);
421
422 CST_UTILITY_PUB.writelogmessages (p_api_version => 1.0,
423 p_msg_count => l_msg_count,
424 p_msg_data => l_msg_data,
425 x_return_status=> l_return_status);
426 l_msg_data := SUBSTRB (SQLERRM,1,240);
427 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
428 IF (l_uLog) THEN
429 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
430 l_module || '.' || l_stmt_num,
431 l_msg_data);
432 END IF;
433 WHEN OTHERS THEN
434 IF (l_uLog) THEN
435 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
436 l_module || '.' || l_stmt_num,
437 SUBSTRB (SQLERRM , 1 , 240));
438 END IF;
439 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
440 THEN
441 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,l_api_name);
442 END IF;
443 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
447 p_msg_count => l_msg_count,
444 p_data => l_msg_data);
445
446 CST_UTILITY_PUB.writelogmessages (p_api_version => 1.0,
448 p_msg_data => l_msg_data,
449 x_return_status=> l_return_status);
450 l_msg_data := SUBSTRB (SQLERRM,1, 240);
451 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
452 END generateXML;
453
454
455 PROCEDURE add_parameters
456 (p_api_version IN NUMBER,
457 p_init_msg_list IN VARCHAR2,
458 p_validation_level IN NUMBER,
459 x_return_status OUT NOCOPY VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER,
461 x_msg_data OUT NOCOPY VARCHAR2,
462 i_org_id IN NUMBER,
463 i_period_id IN NUMBER,
464 i_resolution_type IN NUMBER,
465 i_transaction_type IN NUMBER,
466 x_xml_doc IN OUT NOCOPY CLOB)
467 IS
468 l_api_name CONSTANT VARCHAR2(30) := 'add_parameters';
469 l_api_version CONSTANT NUMBER := 1.0;
470 l_ref_cur SYS_REFCURSOR;
471 l_ctx NUMBER;
472 l_xml_temp CLOB;
473 l_offset PLS_INTEGER;
474 l_org_code CST_ORGANIZATION_DEFINITIONS.ORGANIZATION_CODE%TYPE;
475 l_period_name ORG_ACCT_PERIODS.PERIOD_NAME%TYPE;
476 l_resolution_type MFG_LOOKUPS.MEANING%TYPE;
477 l_temp VARCHAR2(240);
478 l_stmt_num NUMBER;
479
480 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
481 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
482
483 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
484 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
485 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
486 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
487
488 BEGIN
489
490 IF (l_pLog) THEN
491 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
492 l_module || '.begin',
493 '>>> ' || l_api_name);
494 END IF;
495
496 -- Standard call to check for call compatibility.
497 IF NOT FND_API.Compatible_API_Call (l_api_version,
498 p_api_version,
499 l_api_name,
500 G_PKG_NAME )
501 THEN
502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
503 END IF;
504
505 -- Initialize message list if p_init_msg_list is set to TRUE.
506 IF FND_API.to_Boolean( p_init_msg_list ) THEN
507 FND_MSG_PUB.initialize;
508 END IF;
509
510 -- Initialize API return status to success
511 x_return_status := FND_API.G_RET_STS_SUCCESS;
512
513 /* Initialize */
514 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
515
516 l_stmt_num := 10;
517 SELECT ORGANIZATION_CODE
518 INTO l_org_code
519 FROM mtl_parameters
520 WHERE organization_id = i_org_id;
521
522 l_stmt_num := 20;
523 SELECT PERIOD_NAME
524 INTO l_period_name
525 FROM org_acct_periods
526 WHERE acct_period_id = i_period_id
527 AND organization_id = i_org_id;
528
529 l_stmt_num := 25;
530 SELECT ML.MEANING
531 INTO l_resolution_type
532 FROM MFG_LOOKUPS ml
533 WHERE ml.lookup_type = 'CST_SRS_RESOLUTION_TYPES'
534 AND ml.lookup_code = i_resolution_type;
535
536 /* Open Ref Cursor */
537
538 l_stmt_num := 30;
539 OPEN l_ref_cur FOR
540 'SELECT :l_org_code ORG_CODE,
541 :l_period_name PERIOD_NAME,
542 :l_resolution_type RESOLUTION_TYPE,
543 ml.meaning TXN_TYPE
544 FROM MFG_LOOKUPS ml
545 WHERE ml.lookup_type = ''CST_SRS_TRANSACTION_TYPES''
546 AND ml.lookup_code = :i_transaction_type'
547 USING l_org_code, l_period_name, l_resolution_type, i_transaction_type;
548
549 /* create new context */
550 l_stmt_num := 40;
551 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
552 DBMS_XMLGEN.setRowSetTag (l_ctx,'PARAMETERS');
553 DBMS_XMLGEN.setRowTag (l_ctx, NULL);
554
555 /* get XML */
556 l_stmt_num := 50;
557 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
558
559 /* Add the XML header as the first line of output. add data to end */
560 IF (DBMS_XMLGEN.getNumRowsProcessed(l_ctx) > 0) THEN
561 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
562 pattern => '>',
563 offset => 1,
564 nth => 1);
565 /*Bug 7305146*/
566 /*DBMS_LOB.copy (x_xml_doc, l_xml_temp, l_offset + 1);
567 DBMS_LOB.writeappend (x_xml_doc, 8, '<REPORT>');*/
568 DBMS_LOB.erase(l_xml_temp, l_offset, 1);
569 DBMS_LOB.append (x_xml_doc, l_xml_temp);
570 END IF;
571
572 /* close context and free memory */
573 DBMS_XMLGEN.closeContext(l_ctx);
574 CLOSE l_ref_cur;
575 DBMS_LOB.FREETEMPORARY (l_xml_temp);
576
577 -- Standard call to get message count and if count is 1, get message info.
578 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
579 p_data => x_msg_data);
580
581 IF (l_pLog) THEN
582 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
583 l_module || '.end',
587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
584 '<<< ' || l_api_name);
585 END IF;
586 EXCEPTION
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
589 FND_MSG_PUB.Count_And_Get
590 ( p_count => x_msg_count,
591 p_data => x_msg_data
592 );
593 WHEN OTHERS THEN
594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
595 IF (l_uLog) THEN
596 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
597 l_module || '.' || l_stmt_num,
598 SUBSTRB (SQLERRM , 1 , 240));
599 END IF;
600
601 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
602 THEN
603 FND_MSG_PUB.Add_Exc_Msg
604 ( G_PKG_NAME,
605 l_api_name
606 );
607 END IF;
608 FND_MSG_PUB.Count_And_Get
609 (p_count => x_msg_count,
610 p_data => x_msg_data
611 );
612 END add_parameters;
613
614 PROCEDURE unprocessed_mtl_trx
615 (p_api_version IN NUMBER,
616 p_init_msg_list IN VARCHAR2,
617 p_validation_level IN NUMBER,
618 x_return_status OUT NOCOPY VARCHAR2,
619 x_msg_count OUT NOCOPY NUMBER,
620 x_msg_data OUT NOCOPY VARCHAR2,
621 i_period_end_date IN DATE,
622 i_org_id IN NUMBER,
623 x_record_count OUT NOCOPY NUMBER,
624 x_xml_doc IN OUT NOCOPY CLOB)
625 IS
626 l_api_name CONSTANT VARCHAR2(30) := 'unprocessed_mtl_trx';
627 l_api_version CONSTANT NUMBER := 1.0;
628 l_ref_cur SYS_REFCURSOR;
629 l_ctx NUMBER;
630 l_xml_temp CLOB;
631 l_offset PLS_INTEGER;
632 l_stmt_num NUMBER;
633
634 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
635 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
636
637 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
638 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
639 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
640 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
641 BEGIN
642
643 IF (l_pLog) THEN
644 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
645 l_module || '.begin',
646 '>>> ' || l_api_name);
647 END IF;
648
649 -- Standard call to check for call compatibility.
650 IF NOT FND_API.Compatible_API_Call (l_api_version,
651 p_api_version,
652 l_api_name,
653 G_PKG_NAME )
654 THEN
655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
656 END IF;
657 -- Initialize message list if p_init_msg_list is set to TRUE.
658 IF FND_API.to_Boolean( p_init_msg_list ) THEN
659 FND_MSG_PUB.initialize;
660 END IF;
661
662 -- Initialize API return status to success
663 x_return_status := FND_API.G_RET_STS_SUCCESS;
664
665 /* Initialize */
666 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
667
668 /* Open Ref Cursor */
669 l_stmt_num := 10;
670 OPEN l_ref_cur FOR
671 'SELECT mmtt.TRANSACTION_TEMP_ID,
672 mmtt.TRANSACTION_HEADER_ID,
673 mmtt.SOURCE_CODE,
674 mif.ITEM_NUMBER,
675 mmtt.INVENTORY_ITEM_ID,
676 mmtt.SUBINVENTORY_CODE,
677 mmtt.LOCATOR_ID,
678 mmtt.REVISION,
679 mtlt.LOT_NUMBER,
680 msnt.FM_SERIAL_NUMBER,
681 msnt.TO_SERIAL_NUMBER,
682 mmtt.TRANSACTION_DATE,
683 mmtt.TRANSACTION_QUANTITY,
684 mmtt.PRIMARY_QUANTITY,
685 mmtt.TRANSACTION_UOM,
686 mmtt.TRANSACTION_COST,
687 mtt.TRANSACTION_TYPE_NAME,
688 mmtt.TRANSACTION_TYPE_ID,
689 ml.MEANING TRANSACTION_ACTION, /*TXN Action meaning*/
690 mmtt.TRANSACTION_ACTION_ID,
691 mtst.TRANSACTION_SOURCE_TYPE_NAME,
692 mmtt.TRANSACTION_SOURCE_TYPE_ID,
693 mmtt.TRANSACTION_SOURCE_ID,
694 mmtt.RCV_TRANSACTION_ID,
695 mmtt.MOVE_ORDER_LINE_ID,
696 mmtt.COMPLETION_TRANSACTION_ID,
697 mmtt.PROCESS_FLAG,
698 mmtt.LOCK_FLAG,
699 mmtt.TRANSACTION_MODE,
700 ml1.MEANING TRANSACTION_MODE, /*TXN mode meaning*/
701 mmtt.REQUEST_ID,
702 mmtt.TRANSFER_SUBINVENTORY,
703 mmtt.TRANSFER_TO_LOCATION,
704 mmtt.PICK_SLIP_NUMBER,
705 mmtt.PICKING_LINE_ID,
706 mmtt.RESERVATION_ID,
707 mmtt.WMS_TASK_TYPE,
708 mmtt.STANDARD_OPERATION_ID,
709 mmtt.ERROR_CODE,
710 mmtt.ERROR_EXPLANATION
711 FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
712 MTL_ITEM_FLEXFIELDS mif,
713 MTL_TRANSACTION_TYPES mtt,
714 MTL_TXN_SOURCE_TYPES mtst,
715 MFG_LOOKUPS ml,
716 MFG_LOOKUPS ml1,
717 MTL_TRANSACTION_LOTS_TEMP mtlt,
718 MTL_SERIAL_NUMBERS_TEMP msnt
722 AND mmtt.inventory_item_id = mif.inventory_item_id(+)
719 WHERE mmtt.organization_id = :i_org_id
720 AND mmtt.transaction_date <= :i_period_end_date
721 AND NVL(mmtt.transaction_status,0) <> 2
723 AND mmtt.organization_id = mif.organization_id(+)
724 AND mmtt.transaction_type_id = mtt.transaction_type_id(+)
725 AND mmtt.transaction_source_type_id = mtst.transaction_source_type_id(+)
726 AND mmtt.transaction_action_id = ml.lookup_code
727 AND ml.lookup_type = ''MTL_TRANSACTION_ACTION''
728 AND (mtlt.transaction_temp_id (+) = mmtt.transaction_temp_id
729 AND msnt.transaction_temp_id (+) = mmtt.transaction_temp_id)
730 AND ml1.lookup_type = ''MTL_TRANSACTION_MODE''
731 AND ml1.lookup_code(+) = mmtt.transaction_mode
732 ORDER BY mmtt.TRANSACTION_DATE, TRANSACTION_TEMP_ID'
733 USING i_org_id, i_period_end_date;
734
735 /* create new context */
736 l_stmt_num := 20;
737 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
738 DBMS_XMLGEN.setRowSetTag (l_ctx,'UNPROCESSED_MTL_TRX');
739 DBMS_XMLGEN.setRowTag (l_ctx,'UNPROCESSED_MTL_TRANSACTION');
740
741 /* get XML */
742 l_stmt_num := 30;
743 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
744
745 x_record_count := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
746 /* remove the header and append the rest to xml output */
747 IF (x_record_count > 0) THEN
748 /* Find the number of characters in the header and delete
749 them. Header ends with '>'. Hence find first occurrence of
750 '>' in the CLOB */
751 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
752 pattern => '>',
753 offset => 1,
754 nth => 1);
755 DBMS_LOB.erase (l_xml_temp, l_offset, 1);
756 DBMS_LOB.append (x_xml_doc, l_xml_temp);
757 END IF;
758 /* close context and free memory */
759 DBMS_XMLGEN.closeContext(l_ctx);
760 CLOSE l_ref_cur;
761 DBMS_LOB.FREETEMPORARY (l_xml_temp);
762
763 -- Standard call to get message count and if count is 1, get message info.
764 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
765 p_data => x_msg_data);
766
767 IF (l_pLog) THEN
768 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
769 l_module || '.end',
770 '<<< ' || l_api_name);
771 END IF;
772
773 EXCEPTION
774 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
776 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
777 p_data => x_msg_data);
778 WHEN OTHERS THEN
779 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
780 IF (l_uLog) THEN
781 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
782 l_module || '.' || l_stmt_num,
783 SUBSTRB (SQLERRM , 1 , 240));
784 END IF;
785
786 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
787 THEN
788 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
789 END IF;
790 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
791 p_data => x_msg_data);
792 END unprocessed_mtl_trx;
793
794 PROCEDURE uncosted_mtl_trx
795 (p_api_version IN NUMBER,
796 p_init_msg_list IN VARCHAR2,
797 p_validation_level IN NUMBER,
798 x_return_status OUT NOCOPY VARCHAR2,
799 x_msg_count OUT NOCOPY NUMBER,
800 x_msg_data OUT NOCOPY VARCHAR2,
801 i_period_end_date IN DATE,
802 i_org_id IN NUMBER,
803 x_record_count OUT NOCOPY NUMBER,
804 x_xml_doc IN OUT NOCOPY CLOB)
805 IS
806 l_api_name CONSTANT VARCHAR2(30) := 'uncosted_mtl_trx';
807 l_api_version CONSTANT NUMBER := 1.0;
808 l_ref_cur SYS_REFCURSOR;
809 l_ctx NUMBER;
810 l_xml_temp CLOB;
811 l_offset PLS_INTEGER;
812 l_stmt_num NUMBER;
813
814 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
815 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
816
817 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
818 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
819 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
820 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
821 BEGIN
822 IF (l_pLog) THEN
823 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
824 l_module || '.begin',
825 '>>> ' || l_api_name);
826 END IF;
827
828 -- Standard call to check for call compatibility.
829 IF NOT FND_API.Compatible_API_Call (l_api_version,
830 p_api_version,
831 l_api_name,
832 G_PKG_NAME)
833 THEN
834 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
835 END IF;
836 -- Initialize message list if p_init_msg_list is set to TRUE.
837 IF FND_API.to_Boolean (p_init_msg_list) THEN
838 FND_MSG_PUB.initialize;
839 END IF;
840
841 -- Initialize API return status to success
842 x_return_status := FND_API.G_RET_STS_SUCCESS;
843
844 /* Initialize */
845 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
846
847 /* Open Ref Cursor */
851 mif.ITEM_NUMBER,
848 l_stmt_num := 10;
849 OPEN l_ref_cur FOR
850 'SELECT mmt.TRANSACTION_ID,
852 mmt.INVENTORY_ITEM_ID,
853 mmt.TRANSACTION_DATE,
854 mmt.TRANSACTION_QUANTITY,
855 mmt.PRIMARY_QUANTITY,
856 mmt.TRANSACTION_UOM,
857 mtt.TRANSACTION_TYPE_NAME,
858 mmt.TRANSACTION_TYPE_ID,
859 mmt.SUBINVENTORY_CODE,
860 mmt.LOCATOR_ID,
861 mmt.REVISION,
862 mmt.COSTED_FLAG,
863 mmt.COST_GROUP_ID,
864 mmt.TRANSACTION_GROUP_ID,
865 mmt.TRANSACTION_SET_ID,
866 mmt.LAST_UPDATE_DATE,
867 mmt.TRANSACTION_ACTION_ID,
868 mmt.COMPLETION_TRANSACTION_ID,
869 mtst.TRANSACTION_SOURCE_TYPE_NAME,
870 mmt.TRANSACTION_SOURCE_TYPE_ID,
871 mmt.TRANSACTION_SOURCE_ID,
872 mmt.TRANSACTION_SOURCE_NAME,
873 mmt.SOURCE_CODE,
874 mmt.SOURCE_LINE_ID,
875 mmt.REQUEST_ID,
876 mmt.TRANSFER_TRANSACTION_ID,
877 mmt.TRANSFER_ORGANIZATION_ID,
878 mp.ORGANIZATION_CODE TRANSFER_ORGANIZATION_CODE,
879 mmt.TRANSFER_SUBINVENTORY,
880 mmt.ERROR_CODE,
881 mmt.ERROR_EXPLANATION
882 FROM mtl_material_transactions mmt,
883 mtl_item_flexfields mif,
884 mtl_transaction_types mtt,
885 mtl_txn_source_types mtst,
886 mtl_parameters mp
887 WHERE mmt.organization_id = :i_org_id
888 AND mmt.transaction_date <= :i_period_end_date
889 AND mmt.costed_flag in (''N'',''E'')
890 AND mmt.inventory_item_id = mif.inventory_item_id (+)
891 AND mmt.organization_id = mif.organization_id (+)
892 AND mmt.transaction_type_id = mtt.transaction_type_id (+)
893 AND mmt.transaction_source_type_id = mtst.transaction_source_type_id(+)
894 AND mmt.transfer_organization_id = mp.organization_id (+)
895 ORDER BY mmt.TRANSACTION_DATE, mmt.TRANSACTION_ID'
896 USING i_org_id, i_period_end_date;
897
898 /* create new context */
899 l_stmt_num := 20;
900 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
901 DBMS_XMLGEN.setRowSetTag (l_ctx,'UNCOSTED_MTL_TRX');
902 DBMS_XMLGEN.setRowTag (l_ctx,'UNCOSTED_MTL_TRANSACTION');
903
904 /* get XML */
905 l_stmt_num := 30;
906 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
907
908 x_record_count := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
909 /* remove the header and append the rest to xml output */
910 IF (x_record_count > 0) THEN
911 /* Find the number of characters in the header and delete
912 them. Header ends with '>'. Hence find first occurrence of
913 '>' in the CLOB */
914 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
915 pattern => '>',
916 offset => 1,
917 nth => 1);
918 DBMS_LOB.erase (l_xml_temp, l_offset,1);
919 DBMS_LOB.append (x_xml_doc, l_xml_temp);
920 END IF;
921
922 /* close context and free memory */
923 DBMS_XMLGEN.closeContext(l_ctx);
924 CLOSE l_ref_cur;
925 DBMS_LOB.FREETEMPORARY (l_xml_temp);
926
927 -- Standard call to get message count and if count is 1, get message info.
928 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
929 p_data => x_msg_data);
930
931 IF (l_pLog) THEN
932 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
933 l_module || '.end',
934 '<<< ' || l_api_name);
935 END IF;
936 EXCEPTION
937 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
939 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
940 p_data => x_msg_data);
941 WHEN OTHERS THEN
942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
943 IF (l_uLog) THEN
944 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
945 l_module || '.' || l_stmt_num,
946 SUBSTRB (SQLERRM , 1 , 240));
947 END IF;
948
949 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
950 THEN
951 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
952 END IF;
953 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
954 p_data => x_msg_data);
955 END uncosted_mtl_trx;
956
957 PROCEDURE uncosted_wip_trx
958 (p_api_version IN NUMBER,
959 p_init_msg_list IN VARCHAR2,
960 p_validation_level IN NUMBER,
961 x_return_status OUT NOCOPY VARCHAR2,
962 x_msg_count OUT NOCOPY NUMBER,
963 x_msg_data OUT NOCOPY VARCHAR2,
964 i_period_end_date IN DATE,
965 i_org_id IN NUMBER,
966 x_record_count OUT NOCOPY NUMBER,
967 x_xml_doc IN OUT NOCOPY CLOB)
968 IS
969 l_api_name CONSTANT VARCHAR2(30) := 'uncosted_wip_trx';
970 l_api_version CONSTANT NUMBER := 1.0;
971 l_ref_cur SYS_REFCURSOR;
972 l_ctx NUMBER;
973 l_xml_temp CLOB;
974 l_offset PLS_INTEGER;
975 l_stmt_num NUMBER;
976
977 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
978 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
979
983 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
980 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
981 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
982 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
984 BEGIN
985 IF (l_pLog) THEN
986 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
987 l_module || '.begin',
988 '>>> ' || l_api_name);
989 END IF;
990
991 -- Standard call to check for call compatibility.
992 IF NOT FND_API.Compatible_API_Call (l_api_version,
993 p_api_version,
994 l_api_name,
995 G_PKG_NAME )
996 THEN
997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
998 END IF;
999 -- Initialize message list if p_init_msg_list is set to TRUE.
1000 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1001 FND_MSG_PUB.initialize;
1002 END IF;
1003
1004 -- Initialize API return status to success
1005 x_return_status := FND_API.G_RET_STS_SUCCESS;
1006
1007 /* Initialize */
1008 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
1009
1010 /* Open Ref Cursor */
1011 l_stmt_num := 10;
1012 OPEN l_ref_cur FOR
1013 'SELECT wcti.TRANSACTION_ID,
1014 mif.ITEM_NUMBER,
1015 wcti.PRIMARY_ITEM_ID,
1016 wcti.WIP_ENTITY_ID,
1017 wcti.WIP_ENTITY_NAME,
1018 wcti.ENTITY_TYPE,
1019 wcti.REPETITIVE_SCHEDULE_ID,
1020 wcti.TRANSACTION_DATE,
1021 wcti.TRANSACTION_QUANTITY,
1022 wcti.TRANSACTION_UOM,
1023 wcti.TRANSACTION_TYPE,
1024 ml.meaning TRANSACTION_TYPE_CODE,
1025 wcti.AUTOCHARGE_TYPE,
1026 wcti.BASIS_TYPE,
1027 ml1.meaning BASIS_TYPE_CODE,
1028 wcti.RESOURCE_TYPE,
1029 wcti.STANDARD_RATE_FLAG,
1030 wcti.REQUEST_ID,
1031 wcti.GROUP_ID,
1032 wcti.OPERATION_SEQ_NUM,
1033 wcti.RESOURCE_SEQ_NUM,
1034 wcti.RESOURCE_ID,
1035 br.RESOURCE_CODE,
1036 wcti.COMPLETION_TRANSACTION_ID,
1037 wcti.MOVE_TRANSACTION_ID,
1038 wcti.PROCESS_PHASE,
1039 wcti.PROCESS_STATUS,
1040 ml2.meaning PROCESS_STATUS_CODE,
1041 wcti.SOURCE_CODE,
1042 wcti.SOURCE_LINE_ID,
1043 wtie.ERROR_COLUMN,
1044 wtie.ERROR_MESSAGE
1045 FROM wip_cost_txn_interface wcti,
1046 wip_txn_interface_errors wtie,
1047 mtl_item_flexfields mif,
1048 bom_resources br,
1049 mfg_lookups ml,
1050 mfg_lookups ml1,
1051 mfg_lookups ml2
1052 WHERE wcti.organization_id = :i_org_id
1053 AND transaction_date <= :i_period_end_date
1054 AND wtie.transaction_id (+) = wcti.transaction_id
1055 AND wcti.organization_id = mif.organization_id (+)
1056 AND NVL( wcti.primary_item_id, -1) = mif.inventory_item_id(+)
1057 AND wcti.resource_id = br.resource_id (+)
1058 AND ml.lookup_type = ''WIP_TRANSACTION_TYPE''
1059 AND ml.lookup_code(+) = wcti.transaction_type
1060 AND ml1.lookup_type = ''CST_BASIS''
1061 AND ml1.lookup_code(+) = wcti.basis_type
1062 AND ml2.lookup_type = ''WIP_PROCESS_STATUS''
1063 AND ml2.lookup_code = wcti.process_status
1064 ORDER BY wcti.TRANSACTION_DATE, wcti.TRANSACTION_ID'
1065 USING i_org_id, i_period_end_date;
1066
1067 /* create new context */
1068 l_stmt_num := 20;
1069 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
1070 DBMS_XMLGEN.setRowSetTag (l_ctx,'UNCOSTED_WIP_TRX');
1071 DBMS_XMLGEN.setRowTag (l_ctx,'UNCOSTED_WIP_TRANSACTION');
1072
1073 /* get XML */
1074 l_stmt_num := 30;
1075 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
1076
1077 x_record_count := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
1078 /* remove the header and append the rest to xml output */
1079 IF (x_record_count > 0) THEN
1080 /* Find the number of characters in the header and delete
1081 them. Header ends with '>'. Hence find first occurrence of
1082 '>' in the CLOB */
1083 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
1084 pattern => '>',
1085 offset => 1,
1086 nth => 1);
1087 DBMS_LOB.erase (l_xml_temp, l_offset,1);
1088 DBMS_LOB.append (x_xml_doc, l_xml_temp);
1089 END IF;
1090 /* close context and free memory */
1091 DBMS_XMLGEN.closeContext(l_ctx);
1092 CLOSE l_ref_cur;
1093 DBMS_LOB.FREETEMPORARY (l_xml_temp);
1094
1095 -- Standard call to get message count and if count is 1, get message info.
1096 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1097 p_data => x_msg_data);
1098 IF (l_pLog) THEN
1099 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1100 l_module || '.end',
1101 '<<< ' || l_api_name);
1102 END IF;
1103 EXCEPTION
1104 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1106 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,
1107 p_data => x_msg_data);
1108 WHEN OTHERS THEN
1109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1110 IF (l_uLog) THEN
1111 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
1115
1112 l_module || '.' || l_stmt_num,
1113 SUBSTRB (SQLERRM , 1 , 240));
1114 END IF;
1116 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1117 THEN
1118 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,l_api_name);
1119 END IF;
1120 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1121 p_data => x_msg_data);
1122 END uncosted_wip_trx;
1123
1124 PROCEDURE pending_wsm_trx
1125 (p_api_version IN NUMBER,
1126 p_init_msg_list IN VARCHAR2,
1127 p_validation_level IN NUMBER,
1128 x_return_status OUT NOCOPY VARCHAR2,
1129 x_msg_count OUT NOCOPY NUMBER,
1130 x_msg_data OUT NOCOPY VARCHAR2,
1131 i_period_end_date IN DATE,
1132 i_org_id IN NUMBER,
1133 x_record_count OUT NOCOPY NUMBER,
1134 x_xml_doc IN OUT NOCOPY CLOB)
1135 IS
1136 l_api_name CONSTANT VARCHAR2(30) := 'pending_wsm_trx';
1137 l_api_version CONSTANT NUMBER := 1.0;
1138 l_ref_cur SYS_REFCURSOR;
1139 l_ctx NUMBER;
1140 l_xml_wsmti CLOB;
1141 l_xml_wlmti CLOB;
1142 l_xml_wlsmi CLOB;
1143 l_offset PLS_INTEGER;
1144 l_wsmti_flag VARCHAR2(1);
1145 l_wlmti_flag VARCHAR2(1);
1146 l_wlsmi_flag VARCHAR2(1);
1147 l_stmt_num NUMBER;
1148
1149 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1150 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
1151
1152 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
1153 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1154 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1155 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1156 BEGIN
1157 IF (l_pLog) THEN
1158 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1159 l_module || '.begin',
1160 '>>> ' || l_api_name);
1161 END IF;
1162
1163 -- Standard call to check for call compatibility.
1164 IF NOT FND_API.Compatible_API_Call (l_api_version,
1165 p_api_version,
1166 l_api_name,
1167 G_PKG_NAME )
1168 THEN
1169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1170 END IF;
1171 -- Initialize message list if p_init_msg_list is set to TRUE.
1172 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1173 FND_MSG_PUB.initialize;
1174 END IF;
1175
1176 -- Initialize API return status to success
1177 x_return_status := FND_API.G_RET_STS_SUCCESS;
1178
1179 /* Initialize */
1180 DBMS_LOB.createtemporary(l_xml_wsmti, TRUE);
1181 DBMS_LOB.createtemporary(l_xml_wlmti, TRUE);
1182 DBMS_LOB.createtemporary(l_xml_wlsmi, TRUE);
1183 l_wsmti_flag := FND_API.G_FALSE;
1184 l_wlmti_flag := FND_API.G_FALSE;
1185 l_wlsmi_flag := FND_API.G_FALSE;
1186
1187 /* Open Ref Cursor for WSM_SPLIT_MERGE_TXN_INTERFACE */
1188 l_stmt_num := 10;
1189 OPEN l_ref_cur FOR
1190 'SELECT wsmti.HEADER_ID,
1191 wsmti.TRANSACTION_TYPE_ID,
1192 ml.meaning TRANSACTION_TYPE_NAME,
1193 wsmti.TRANSACTION_DATE,
1194 wsmti.PROCESS_STATUS,
1195 ml1.meaning PROCESS_STATUS_CODE,
1196 wsmti.TRANSACTION_ID,
1197 /* Pick resulting lot as reference lot for merge and bonus
1198 Pick starting lot for other transactions */
1199 decode (wsmti.transaction_type_id,
1200 2, wrji.wip_entity_name,
1201 4, wrji.wip_entity_name,
1202 wsji.wip_entity_name) REFERENCE_LOT,
1203 wsmti.GROUP_ID,
1204 wsmti.REQUEST_ID,
1205 wsmti.ERROR_MESSAGE
1206 FROM wsm_split_merge_txn_interface wsmti,
1207 wsm_starting_jobs_interface wsji,
1208 wsm_resulting_jobs_interface wrji,
1209 mfg_lookups ml,
1210 mfg_lookups ml1
1211 WHERE wsmti.header_id = wsji.header_id(+)
1212 AND wsmti.header_id = wrji.header_id(+)
1213 AND ml.lookup_type = ''WSM_WIP_LOT_TXN_TYPE''
1214 AND ml.lookup_code = wsmti.transaction_type_id
1215 AND ml1.lookup_type = ''WIP_PROCESS_STATUS''
1216 AND ml1.lookup_code = wsmti.process_status
1217 AND wsmti.organization_id = :i_org_id
1218 AND wsmti.process_status <> 4
1219 AND wsmti.transaction_date <= :i_period_end_date
1220 ORDER BY TRANSACTION_DATE, HEADER_ID'
1221 USING i_org_id, i_period_end_date;
1222
1223 /* create new context */
1224 l_stmt_num := 20;
1225 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
1226 DBMS_XMLGEN.setRowSetTag (l_ctx,'PENDING_WSMTI_TRX');
1227 DBMS_XMLGEN.setRowTag (l_ctx,'PENDING_WSMTI_TRANSACTION');
1228
1229 /* get XML */
1230 l_stmt_num := 30;
1231 DBMS_XMLGEN.getXML (l_ctx, l_xml_wsmti, DBMS_XMLGEN.none);
1232
1233 x_record_count := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
1234 /* remove the header*/
1235 IF (x_record_count > 0) THEN
1236 l_wsmti_flag := FND_API.G_TRUE;
1237 l_offset := DBMS_LOB.instr (lob_loc => l_xml_wsmti,
1238 pattern => '>',
1239 offset => 1,
1240 nth => 1);
1241 DBMS_LOB.erase (l_xml_wsmti, l_offset,1);
1245 CLOSE l_ref_cur;
1242 END IF;
1243 /* close context */
1244 DBMS_XMLGEN.closeContext(l_ctx);
1246
1247 IF (l_eventLog) THEN
1248 FND_LOG.STRING (FND_LOG.LEVEL_EVENT,
1249 l_module || '.' || l_stmt_num,
1250 'Completed WSMTI transactions');
1251 END IF;
1252
1253 /* Open Ref Cursor for WSM_LOT_MOVE_TXN_INTERFACE */
1254 l_stmt_num := 40;
1255 OPEN l_ref_cur FOR
1256 'SELECT wlmti.TRANSACTION_ID,
1257 wlmti.REQUEST_ID,
1258 wlmti.GROUP_ID,
1259 wlmti.SOURCE_CODE,
1260 wlmti.SOURCE_LINE_ID,
1261 wlmti.STATUS,
1262 ml.MEANING STATUS_CODE,
1263 wlmti.TRANSACTION_TYPE,
1264 wlmti.ORGANIZATION_ID,
1265 wlmti.ORGANIZATION_CODE,
1266 wlmti.WIP_ENTITY_ID,
1267 wlmti.WIP_ENTITY_NAME,
1268 wlmti.ENTITY_TYPE,
1269 wlmti.PRIMARY_ITEM_ID,
1270 mif.ITEM_NUMBER,
1271 wlmti.REPETITIVE_SCHEDULE_ID,
1272 wlmti.TRANSACTION_DATE,
1273 wlmti.ACCT_PERIOD_ID,
1274 wlmti.FM_OPERATION_SEQ_NUM,
1275 wlmti.FM_OPERATION_CODE,
1276 wlmti.FM_DEPARTMENT_ID,
1277 wlmti.FM_DEPARTMENT_CODE,
1278 wlmti.TO_OPERATION_SEQ_NUM,
1279 wlmti.TO_OPERATION_CODE,
1280 wlmti.TO_DEPARTMENT_ID,
1281 wlmti.TO_DEPARTMENT_CODE,
1282 wlmti.TRANSACTION_QUANTITY,
1283 wlmti.PRIMARY_QUANTITY,
1284 wlmti.SCRAP_QUANTITY,
1285 wlmti.PRIMARY_SCRAP_QUANTITY,
1286 wlmti.ERROR,
1287 wlmti.HEADER_ID,
1288 wlmti.REASON_NAME
1289 FROM wsm_lot_move_txn_interface wlmti,
1290 mtl_item_flexfields mif,
1291 mfg_lookups ml
1292 WHERE wlmti.organization_id = :i_org_id
1293 AND wlmti.transaction_date <= :i_period_end_date
1294 AND wlmti.status <> 4
1295 AND NVL(wlmti.primary_item_id, -1) = mif.inventory_item_id(+)
1296 AND wlmti.organization_id = mif.organization_id (+)
1297 AND ml.lookup_type = ''WIP_PROCESS_STATUS''
1298 AND ml.lookup_code = wlmti.status
1299 ORDER BY TRANSACTION_DATE'
1300 USING i_org_id, i_period_end_date;
1301
1302 /* create new context */
1303 l_stmt_num := 50;
1304 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
1305 DBMS_XMLGEN.setRowSetTag (l_ctx,'PENDING_WLMTI_TRX');
1306 DBMS_XMLGEN.setRowTag (l_ctx,'PENDING_WLMTI_TRANSACTION');
1307
1308 /* get XML */
1309 l_stmt_num := 60;
1310 DBMS_XMLGEN.getXML (l_ctx, l_xml_wlmti, DBMS_XMLGEN.none);
1311
1312 x_record_count := x_record_count + DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
1313 /* remove the header */
1314 IF (DBMS_XMLGEN.getNumRowsProcessed(l_ctx) > 0) THEN
1315 l_wlmti_flag := FND_API.G_TRUE;
1316 l_offset := DBMS_LOB.instr (lob_loc => l_xml_wlmti,
1317 pattern => '>',
1318 offset => 1,
1319 nth => 1);
1320 DBMS_LOB.erase (l_xml_wlmti, l_offset,1);
1321 END IF;
1322 /* close context */
1323 DBMS_XMLGEN.closeContext(l_ctx);
1324 CLOSE l_ref_cur;
1325
1326 IF (l_eventLog) THEN
1327 FND_LOG.STRING (FND_LOG.LEVEL_EVENT,
1328 l_module || '.' || l_stmt_num,
1329 'Completed WLMTI transactions');
1330 END IF;
1331
1332 /* Open Ref Cursor for WSM_LOT_SPLIT_MERGES_INTERFACE */
1333 l_stmt_num := 70;
1334 OPEN l_ref_cur FOR
1335 'SELECT wlsmi.transaction_id,
1336 wlsmi.transaction_type_id,
1337 ml.meaning transaction_type_name,
1338 wlsmi.organization_id,
1339 wlsmi.wip_flag,
1340 wlsmi.split_flag,
1341 wlsmi.transaction_date,
1342 wlsmi.request_id,
1343 wlsmi.process_status,
1344 ml1.meaning process_status_code,
1345 wlsmi.error_message,
1346 wlsmi.group_id,
1347 wlsmi.transaction_reason,
1348 wlsmi.header_id,
1349 /* Pick resulting lot as reference lot for merge transactions
1350 Pick starting lot for split, transfer and translate transactions */
1351 decode (wlsmi.transaction_type_id,
1352 2, wrli.lot_number,
1353 wsli.lot_number) reference_lot
1354 FROM wsm_lot_split_merges_interface wlsmi,
1355 wsm_starting_lots_interface wsli,
1356 wsm_resulting_lots_interface wrli,
1357 mfg_lookups ml,
1358 mfg_lookups ml1
1359 WHERE wlsmi.organization_id = :i_org_id
1360 AND ml.lookup_type = ''WSM_INV_LOT_TXN_TYPE''
1361 AND ml.lookup_code = wlsmi.transaction_type_id
1362 AND ml1.lookup_type = ''WIP_PROCESS_STATUS''
1363 AND ml1.lookup_code(+) = wlsmi.process_status
1364 AND wlsmi.transaction_date <= :i_period_end_date
1365 AND wlsmi.process_status <> 4
1366 AND nvl(wlsmi.header_id, -1) = wsli.header_id(+)
1367 AND nvl(wlsmi.header_id, -1) = wrli.header_id(+)
1368 ORDER BY TRANSACTION_DATE, HEADER_ID'
1369 USING i_org_id, i_period_end_date;
1370
1371 /* create new context */
1372 l_stmt_num := 80;
1373 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
1374 DBMS_XMLGEN.setRowSetTag (l_ctx,'PENDING_WLSMI_TRX');
1375 DBMS_XMLGEN.setRowTag (l_ctx,'PENDING_WLSMI_TRANSACTION');
1376
1377 /* get XML */
1381 x_record_count := x_record_count + DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
1378 l_stmt_num := 90;
1379 DBMS_XMLGEN.getXML (l_ctx, l_xml_wlsmi, DBMS_XMLGEN.none);
1380
1382 /* remove the header */
1383 IF (DBMS_XMLGEN.getNumRowsProcessed(l_ctx) > 0) THEN
1384 l_wlsmi_flag := FND_API.G_TRUE;
1385 l_offset := DBMS_LOB.instr (lob_loc => l_xml_wlsmi,
1386 pattern => '>',
1387 offset => 1,
1388 nth => 1);
1389 DBMS_LOB.erase (l_xml_wlsmi, l_offset,1);
1390 END IF;
1391 /* close context */
1392 DBMS_XMLGEN.closeContext(l_ctx);
1393 CLOSE l_ref_cur;
1394
1395 IF (l_eventLog) THEN
1396 FND_LOG.STRING (FND_LOG.LEVEL_EVENT,
1397 l_module || '.' || l_stmt_num,
1398 'Completed WLSMI transactions');
1399 END IF;
1400
1401 IF (FND_API.to_Boolean (l_wsmti_flag) OR FND_API.to_Boolean (l_wlmti_flag)
1402 OR FND_API.to_Boolean (l_wlsmi_flag) ) THEN
1403 /* Atleast one transaction is present */
1404 l_stmt_num := 100;
1405 DBMS_LOB.writeappend (x_xml_doc, 17, '<PENDING_WSM_TRX>');
1406 IF (FND_API.to_Boolean (l_wsmti_flag)) THEN
1407 DBMS_LOB.append (x_xml_doc, l_xml_wsmti);
1408 END IF;
1409 IF (FND_API.to_Boolean (l_wlmti_flag)) THEN
1410 DBMS_LOB.append (x_xml_doc, l_xml_wlmti);
1411 END IF;
1412 IF (FND_API.to_Boolean (l_wlsmi_flag)) THEN
1413 DBMS_LOB.append (x_xml_doc, l_xml_wlsmi);
1414 END IF;
1415 DBMS_LOB.writeappend (x_xml_doc, 18, '</PENDING_WSM_TRX>');
1416 END IF;
1417
1418 DBMS_LOB.FREETEMPORARY (l_xml_wsmti);
1419 DBMS_LOB.FREETEMPORARY (l_xml_wlmti);
1420 DBMS_LOB.FREETEMPORARY (l_xml_wlsmi);
1421
1422 -- Standard call to get message count and if count is 1, get message info.
1423 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1424 p_data => x_msg_data);
1425
1426 IF (l_pLog) THEN
1427 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1428 l_module || '.end',
1429 '<<< ' || l_api_name);
1430 END IF;
1431 EXCEPTION
1432 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1434 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1435 p_data => x_msg_data);
1436 WHEN OTHERS THEN
1437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1438 IF (l_uLog) THEN
1439 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
1440 l_module || '.' || l_stmt_num,
1441 SUBSTRB (SQLERRM , 1 , 240));
1442 END IF;
1443
1444 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1445 THEN
1446 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1447 END IF;
1448 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1449 p_data => x_msg_data);
1450 END pending_wsm_trx;
1451
1452 PROCEDURE pending_mtl_interface_trx
1453 (p_api_version IN NUMBER,
1454 p_init_msg_list IN VARCHAR2,
1455 p_validation_level IN NUMBER,
1456 x_return_status OUT NOCOPY VARCHAR2,
1457 x_msg_count OUT NOCOPY NUMBER,
1458 x_msg_data OUT NOCOPY VARCHAR2,
1459 i_period_end_date IN DATE,
1460 i_org_id IN NUMBER,
1461 x_record_count OUT NOCOPY NUMBER,
1462 x_xml_doc IN OUT NOCOPY CLOB)
1463 IS
1464 l_api_name CONSTANT VARCHAR2(30) := 'pending_mtl_interface_trx';
1465 l_api_version CONSTANT NUMBER := 1.0;
1466 l_ref_cur SYS_REFCURSOR;
1467 l_ctx NUMBER;
1468 l_xml_temp CLOB;
1469 l_offset PLS_INTEGER;
1470 l_stmt_num NUMBER;
1471 l_min_txn_if_id MTL_TRANSACTIONS_INTERFACE.TRANSACTION_INTERFACE_ID%TYPE;
1472 l_max_txn_if_id MTL_TRANSACTIONS_INTERFACE.TRANSACTION_INTERFACE_ID%TYPE;
1473 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1474 l_module CONSTANT VARCHAR2(70) := 'cst.plsql.' || l_full_name;
1475
1476 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
1477 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1478 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1479 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1480 BEGIN
1481 IF (l_pLog) THEN
1482 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1483 l_module || '.begin',
1484 '>>> ' || l_api_name);
1485 END IF;
1486
1487 -- Standard call to check for call compatibility.
1488 IF NOT FND_API.Compatible_API_Call (l_api_version,
1489 p_api_version,
1490 l_api_name,
1491 G_PKG_NAME )
1492 THEN
1493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1494 END IF;
1495 -- Initialize message list if p_init_msg_list is set to TRUE.
1496 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1497 FND_MSG_PUB.initialize;
1498 END IF;
1499
1500 -- Initialize API return status to success
1501 x_return_status := FND_API.G_RET_STS_SUCCESS;
1502
1503 /* Initialize */
1504 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
1505
1506 SELECT min (TRANSACTION_INTERFACE_ID)
1507 INTO l_min_txn_if_id
1511 AND process_flag <> 9;
1508 FROM mtl_transactions_interface
1509 WHERE organization_id = i_org_id
1510 AND transaction_date <= i_period_end_date
1512
1513 SELECT max (TRANSACTION_INTERFACE_ID)
1514 INTO l_max_txn_if_id
1515 FROM mtl_transactions_interface
1516 WHERE organization_id = i_org_id
1517 AND transaction_date <= i_period_end_date
1518 AND process_flag <> 9;
1519
1520 /* Open Ref Cursor */
1521 l_stmt_num := 10;
1522 OPEN l_ref_cur FOR
1523 'SELECT mti.TRANSACTION_INTERFACE_ID,
1524 mti.TRANSACTION_HEADER_ID,
1525 mif.ITEM_NUMBER,
1526 mti.INVENTORY_ITEM_ID,
1527 mti.SUBINVENTORY_CODE,
1528 mti.LOCATOR_ID,
1529 mtli.LOT_NUMBER,
1530 mti.REVISION,
1531 msni.FM_SERIAL_NUMBER,
1532 msni.TO_SERIAL_NUMBER,
1533 mti.TRANSACTION_QUANTITY,
1534 mti.PRIMARY_QUANTITY,
1535 mti.TRANSACTION_UOM,
1536 mti.TRANSACTION_COST,
1537 mtt.TRANSACTION_TYPE_NAME,
1538 mti.TRANSACTION_TYPE_ID,
1539 ml4.meaning TRANSACTION_ACTION_NAME,
1540 mti.TRANSACTION_ACTION_ID,
1541 mtst.TRANSACTION_SOURCE_TYPE_NAME,
1542 mti.TRANSACTION_SOURCE_TYPE_ID,
1543 mti.TRANSACTION_SOURCE_NAME,
1544 mti.TRANSACTION_SOURCE_ID,
1545 mti.TRANSACTION_DATE,
1546 mti.TRANSFER_SUBINVENTORY,
1547 mp.ORGANIZATION_CODE TRANSFER_ORGANIZATION_CODE,
1548 mti.TRANSFER_ORGANIZATION,
1549 mti.REQUEST_ID,
1550 mti.SOURCE_CODE,
1551 mti.SOURCE_LINE_ID,
1552 mti.SOURCE_HEADER_ID,
1553 ml3.meaning PROCESS_FLAG_DESC,
1554 mti.PROCESS_FLAG,
1555 ml2.meaning TRANSACTION_MODE_DESC,
1556 mti.TRANSACTION_MODE,
1557 ml1.meaning LOCK_FLAG_DESC,
1558 mti.LOCK_FLAG,
1559 mti.ERROR_CODE,
1560 mti.ERROR_EXPLANATION
1561 FROM mtl_transactions_interface mti,
1562 mtl_item_flexfields mif,
1563 mtl_serial_numbers_interface msni,
1564 mtl_transaction_lots_interface mtli,
1565 mtl_parameters mp,
1566 mfg_lookups ml1,
1567 mfg_lookups ml2,
1568 mfg_lookups ml3,
1569 mfg_lookups ml4,
1570 mtl_txn_source_types mtst,
1571 mtl_transaction_types mtt
1572 WHERE mti.organization_id = :i_org_id
1573 AND mti.transaction_date <= :i_period_end_date
1574 AND mti.process_flag <> 9
1575 AND mti.transaction_interface_id
1576 between :l_min_txn_if_id AND :l_max_txn_if_id
1577 AND mti.organization_id = mif.organization_id (+)
1578 AND mti.inventory_item_id = mif.inventory_item_id (+)
1579 AND (mtli.transaction_interface_id (+) = mti.transaction_interface_id
1580 AND msni.transaction_interface_id (+) = mti.transaction_interface_id)
1581 AND ml1.lookup_type = ''SYS_YES_NO''
1582 AND ml1.lookup_code (+) = mti.lock_flag
1583 AND ml2.lookup_type = ''MTL_TRANSACTION_MODE''
1584 AND ml2.lookup_code (+) = mti.transaction_mode
1585 AND ml3.lookup_type = ''INV_YES_NO_ERROR''
1586 AND ml3.lookup_code (+) = mti.process_flag
1587 AND ml4.lookup_type = ''MTL_TRANSACTION_ACTION''
1588 AND ml4.lookup_code (+) = mti.transaction_action_id
1589 AND mp.organization_id (+) = mti.transfer_organization
1590 AND mtst.transaction_source_type_id (+) = mti.transaction_source_type_id
1591 AND mtt.transaction_type_id = mti.transaction_type_id
1592 ORDER BY mti.transaction_date, mti.transaction_interface_id'
1593 USING i_org_id, i_period_end_date, l_min_txn_if_id, l_max_txn_if_id;
1594
1595 /* create new context */
1596 l_stmt_num := 20;
1597 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
1598 DBMS_XMLGEN.setRowSetTag (l_ctx,'PENDING_MTL_INTERFACE_TRX');
1599 DBMS_XMLGEN.setRowTag (l_ctx,'PENDING_MTL_INTERFACE_TRANSACTION');
1600
1601 /* get XML */
1602 l_stmt_num := 30;
1603 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
1604
1605 x_record_count := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
1606 /* remove the header and append the rest to xml output */
1607 IF (x_record_count > 0) THEN
1608 /* Find the number of characters in the header and delete
1609 them. Header ends with '>'. Hence find first occurrence of
1610 '>' in the CLOB */
1611 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
1612 pattern => '>',
1613 offset => 1,
1614 nth => 1);
1615 DBMS_LOB.erase (l_xml_temp, l_offset,1);
1616 DBMS_LOB.append (x_xml_doc, l_xml_temp);
1617 END IF;
1618 /* close context and free memory */
1619 DBMS_XMLGEN.closeContext(l_ctx);
1620 CLOSE l_ref_cur;
1621 DBMS_LOB.FREETEMPORARY (l_xml_temp);
1622
1623 -- Standard call to get message count and if count is 1, get message info.
1624 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1625 p_data => x_msg_data);
1626
1627 IF (l_pLog) THEN
1628 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1629 l_module || '.end',
1630 '<<< ' || l_api_name);
1631 END IF;
1632
1633 EXCEPTION
1634 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1636 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1637 p_data => x_msg_data);
1638 WHEN OTHERS THEN
1642 l_module || '.' || l_stmt_num,
1639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1640 IF (l_uLog) THEN
1641 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
1643 SUBSTRB (SQLERRM , 1 , 240));
1644 END IF;
1645
1646 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1647 THEN
1648 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1649 END IF;
1650 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1651 p_data => x_msg_data);
1652 END pending_mtl_interface_trx;
1653
1654 PROCEDURE pending_rcv_trx
1655 (p_api_version IN NUMBER,
1656 p_init_msg_list IN VARCHAR2,
1657 p_validation_level IN NUMBER,
1658 x_return_status OUT NOCOPY VARCHAR2,
1659 x_msg_count OUT NOCOPY NUMBER,
1660 x_msg_data OUT NOCOPY VARCHAR2,
1661 i_period_end_date IN DATE,
1662 i_org_id IN NUMBER,
1663 x_record_count OUT NOCOPY NUMBER,
1664 x_xml_doc IN OUT NOCOPY CLOB)
1665 IS
1666 l_api_name CONSTANT VARCHAR2(30) := 'pending_rcv_trx';
1667 l_api_version CONSTANT NUMBER := 1.0;
1668 l_ref_cur SYS_REFCURSOR;
1669 l_ctx NUMBER;
1670 l_xml_temp CLOB;
1671 l_offset PLS_INTEGER;
1672 l_stmt_num NUMBER;
1673
1674 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1675 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
1676
1677 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
1678 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1679 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1680 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1681 BEGIN
1682 IF (l_pLog) THEN
1683 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1684 l_module || '.begin',
1685 '>>> ' || l_api_name);
1686 END IF;
1687
1688 -- Standard call to check for call compatibility.
1689 IF NOT FND_API.Compatible_API_Call (l_api_version,
1690 p_api_version,
1691 l_api_name,
1692 G_PKG_NAME )
1693 THEN
1694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1695 END IF;
1696 -- Initialize message list if p_init_msg_list is set to TRUE.
1697 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1698 FND_MSG_PUB.initialize;
1699 END IF;
1700
1701 -- Initialize API return status to success
1702 x_return_status := FND_API.G_RET_STS_SUCCESS;
1703
1704 /* Initialize */
1705 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
1706
1707 /* Open Ref Cursor */
1708 l_stmt_num := 10;
1709 OPEN l_ref_cur FOR
1710 'SELECT rti.INTERFACE_TRANSACTION_ID,
1711 rti.HEADER_INTERFACE_ID,
1712 mif.ITEM_NUMBER,
1713 rti.ITEM_ID,
1714 rti.GROUP_ID,
1715 rti.TRANSACTION_TYPE,
1716 rti.TRANSACTION_DATE,
1717 rti.PROCESSING_STATUS_CODE,
1718 rti.PROCESSING_MODE_CODE,
1719 rti.TRANSACTION_STATUS_CODE,
1720 rti.QUANTITY,
1721 rti.UNIT_OF_MEASURE,
1722 rti.AUTO_TRANSACT_CODE,
1723 rti.RECEIPT_SOURCE_CODE,
1724 rti.DESTINATION_TYPE_CODE,
1725 rti.SOURCE_DOCUMENT_CODE,
1726 rti.CURRENCY_CODE,
1727 rti.DOCUMENT_NUM,
1728 rti.SHIP_TO_LOCATION_ID,
1729 hl.LOCATION_CODE,
1730 rti.PARENT_TRANSACTION_ID,
1731 rti.PO_HEADER_ID,
1732 rti.PO_LINE_ID,
1733 rti.PO_RELEASE_ID,
1734 por.RELEASE_NUM,
1735 poh.SEGMENT1,
1736 rti.VENDOR_ID,
1737 rti.VENDOR_SITE_ID,
1738 rti.OE_ORDER_HEADER_ID,
1739 rti.OE_ORDER_LINE_ID,
1740 rti.VALIDATION_FLAG,
1741 rti.SUBINVENTORY,
1742 pol.LINE_NUM,
1743 pie.COLUMN_NAME,
1744 pie.ERROR_MESSAGE
1745 FROM rcv_transactions_interface rti,
1746 po_interface_errors pie,
1747 mtl_item_flexfields mif,
1748 po_headers_all poh,
1749 po_lines_all pol,
1750 po_releases_all por,
1751 hr_locations_all hl
1752 WHERE to_organization_id = :i_org_id
1753 AND transaction_date <= :i_period_end_date
1754 AND destination_type_code in (''INVENTORY'', ''SHOP FLOOR'')
1755 AND rti.po_header_id = poh.po_header_id(+)
1756 AND rti.po_line_id = pol.po_line_id(+)
1757 AND rti.po_release_id = por.po_release_id(+)
1758 AND rti.to_organization_id = mif.organization_id (+)
1759 AND rti.item_id = mif.inventory_item_id (+)
1760 AND rti.interface_transaction_id = pie.interface_transaction_id(+)
1761 AND rti.ship_to_location_id = hl.location_id (+)
1762 ORDER BY rti.TRANSACTION_DATE, rti.INTERFACE_TRANSACTION_ID'
1763 USING i_org_id, i_period_end_date;
1764
1765 /* create new context */
1766 l_stmt_num := 20;
1767 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
1768 DBMS_XMLGEN.setRowSetTag (l_ctx,'PENDING_RCV_TRX');
1769 DBMS_XMLGEN.setRowTag (l_ctx,'PENDING_RCV_TRANSACTION');
1770
1771 /* get XML */
1772 l_stmt_num := 30;
1773 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
1774
1778 /* Find the number of characters in the header and delete
1775 x_record_count := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
1776 /* remove the header and append the rest to xml output */
1777 IF (x_record_count > 0) THEN
1779 them. Header ends with '>'. Hence find first occurrence of
1780 '>' in the CLOB */
1781 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
1782 pattern => '>',
1783 offset => 1,
1784 nth => 1);
1785 DBMS_LOB.erase (l_xml_temp, l_offset,1);
1786 DBMS_LOB.append (x_xml_doc, l_xml_temp);
1787 END IF;
1788
1789 /* close context and free memory */
1790 DBMS_XMLGEN.closeContext(l_ctx);
1791 CLOSE l_ref_cur;
1792 DBMS_LOB.FREETEMPORARY (l_xml_temp);
1793
1794 -- Standard call to get message count and if count is 1, get message info.
1795 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1796 p_data => x_msg_data);
1797
1798 IF (l_pLog) THEN
1799 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1800 l_module || '.end',
1801 '<<< ' || l_api_name);
1802 END IF;
1803 EXCEPTION
1804 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1806 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1807 p_data => x_msg_data);
1808 WHEN OTHERS THEN
1809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1810 IF (l_uLog) THEN
1811 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
1812 l_module || '.' || l_stmt_num,
1813 SUBSTRB (SQLERRM , 1 , 240));
1814 END IF;
1815
1816 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1817 THEN
1818 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,l_api_name);
1819 END IF;
1820 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1821 p_data => x_msg_data);
1822 END pending_rcv_trx;
1823
1824 PROCEDURE pending_wip_move_trx
1825 (p_api_version IN NUMBER,
1826 p_init_msg_list IN VARCHAR2,
1827 p_validation_level IN NUMBER,
1828 x_return_status OUT NOCOPY VARCHAR2,
1829 x_msg_count OUT NOCOPY NUMBER,
1830 x_msg_data OUT NOCOPY VARCHAR2,
1831 i_period_end_date IN DATE,
1832 i_org_id IN NUMBER,
1833 x_record_count OUT NOCOPY NUMBER,
1834 x_xml_doc IN OUT NOCOPY CLOB)
1835 IS
1836 l_api_name CONSTANT VARCHAR2(30) := 'pending_wip_move_trx';
1837 l_api_version CONSTANT NUMBER := 1.0;
1838 l_ref_cur SYS_REFCURSOR;
1839 l_ctx NUMBER;
1840 l_xml_temp CLOB;
1841 l_offset PLS_INTEGER;
1842 l_stmt_num NUMBER;
1843
1844 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1845 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
1846
1847 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
1848 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1849 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1850 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1851 BEGIN
1852 IF (l_pLog) THEN
1853 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1854 l_module || '.begin',
1855 '>>> ' || l_api_name);
1856 END IF;
1857
1858 -- Standard call to check for call compatibility.
1859 IF NOT FND_API.Compatible_API_Call (l_api_version,
1860 p_api_version,
1861 l_api_name,
1862 G_PKG_NAME )
1863 THEN
1864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1865 END IF;
1866 -- Initialize message list if p_init_msg_list is set to TRUE.
1867 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1868 FND_MSG_PUB.initialize;
1869 END IF;
1870
1871 -- Initialize API return status to success
1872 x_return_status := FND_API.G_RET_STS_SUCCESS;
1873
1874 /* Initialize */
1875 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
1876
1877 /* Open Ref Cursor */
1878 l_stmt_num := 10;
1879 OPEN l_ref_cur FOR
1880 'SELECT wmti.TRANSACTION_ID,
1881 wmti.GROUP_ID,
1882 wmti.PROCESS_PHASE_MEANING,
1883 wmti.PROCESS_PHASE,
1884 wmti.PROCESS_STATUS_MEANING,
1885 wmti.PROCESS_STATUS,
1886 mif.ITEM_NUMBER,
1887 wmti.PRIMARY_ITEM_ID,
1888 wmti.ENTITY_TYPE,
1889 wmti.WIP_ENTITY_NAME,
1890 wmti.WIP_ENTITY_ID,
1891 wmti.TRANSACTION_TYPE_MEANING,
1892 wmti.TRANSACTION_TYPE,
1893 wmti.TRANSACTION_DATE,
1894 wmti.TRANSACTION_QUANTITY,
1895 wmti.TRANSACTION_UOM,
1896 wmti.PRIMARY_QUANTITY,
1897 wmti.PRIMARY_UOM,
1898 wmti.SOURCE_CODE,
1899 wmti.SOURCE_LINE_ID,
1900 wmti.REPETITIVE_SCHEDULE_ID,
1901 wmti.FM_OPERATION_SEQ_NUM,
1902 wmti.FM_INTRAOPERATION_STEP_TYPE,
1903 wmti.TO_OPERATION_SEQ_NUM,
1904 wmti.TO_INTRAOPERATION_STEP_TYPE,
1905 wmti.OVERCOMPLETION_TRANSACTION_QTY,
1906 wmti.SCRAP_ACCOUNT_ID,
1910 FROM wip_move_txn_interface_v wmti,
1907 wmti.REQUEST_ID,
1908 wtie.ERROR_COLUMN,
1909 wtie.ERROR_MESSAGE
1911 wip_txn_interface_errors wtie,
1912 mtl_item_flexfields mif
1913 WHERE wmti.organization_id = :i_org_id
1914 AND wmti.transaction_date <= :i_period_end_date
1915 AND wtie.transaction_id(+) = wmti.transaction_id
1916 AND wmti.organization_id = mif.organization_id (+)
1917 AND NVL( wmti.primary_item_id, -1) = mif.inventory_item_id(+)
1918 ORDER BY wmti.TRANSACTION_DATE, wmti.TRANSACTION_ID'
1919 USING i_org_id, i_period_end_date;
1920
1921 /* create new context */
1922 l_stmt_num := 20;
1923 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
1924 DBMS_XMLGEN.setRowSetTag (l_ctx,'PENDING_WIP_MOVE_TRX');
1925 DBMS_XMLGEN.setRowTag (l_ctx,'PENDING_WIP_MOVE_TRANSACTION');
1926
1927 /* get XML */
1928 l_stmt_num := 30;
1929 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
1930
1931 x_record_count := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
1932 /* remove the header and append the rest to xml output */
1933 IF (x_record_count > 0) THEN
1934 /* Find the number of characters in the header and delete
1935 them. Header ends with '>'. Hence find first occurrence of
1936 '>' in the CLOB */
1937 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
1938 pattern => '>',
1939 offset => 1,
1940 nth => 1);
1941 DBMS_LOB.erase (l_xml_temp, l_offset,1);
1942 DBMS_LOB.append (x_xml_doc, l_xml_temp);
1943 END IF;
1944 /* close context and free memory */
1945 DBMS_XMLGEN.closeContext(l_ctx);
1946 CLOSE l_ref_cur;
1947 DBMS_LOB.FREETEMPORARY (l_xml_temp);
1948
1949 -- Standard call to get message count and if count is 1, get message info.
1950 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1951 p_data => x_msg_data);
1952
1953 IF (l_pLog) THEN
1954 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
1955 l_module || '.end',
1956 '<<< ' || l_api_name);
1957 END IF;
1958 EXCEPTION
1959 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1961 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1962 p_data => x_msg_data);
1963
1964 WHEN OTHERS THEN
1965 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1966 IF (l_uLog) THEN
1967 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
1968 l_module || '.' || l_stmt_num,
1969 SUBSTRB (SQLERRM , 1 , 240));
1970 END IF;
1971
1972 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1973 THEN
1974 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1975 END IF;
1976 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1977 p_data => x_msg_data);
1978 END pending_wip_move_trx;
1979
1980 PROCEDURE pending_shipping_trx
1981 (p_api_version IN NUMBER,
1982 p_init_msg_list IN VARCHAR2,
1983 p_validation_level IN NUMBER,
1984 x_return_status OUT NOCOPY VARCHAR2,
1985 x_msg_count OUT NOCOPY NUMBER,
1986 x_msg_data OUT NOCOPY VARCHAR2,
1987 i_period_start_date IN DATE,
1988 i_period_end_date IN DATE,
1989 i_org_id IN NUMBER,
1990 x_record_count OUT NOCOPY NUMBER,
1991 x_xml_doc IN OUT NOCOPY CLOB)
1992 IS
1993 l_api_name CONSTANT VARCHAR2(30) := 'pending_shipping_trx';
1994 l_api_version CONSTANT NUMBER := 1.0;
1995 l_ref_cur SYS_REFCURSOR;
1996 l_ctx NUMBER;
1997 l_xml_temp CLOB;
1998 l_offset PLS_INTEGER;
1999 l_stmt_num NUMBER;
2000
2001 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2002 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
2003
2004 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
2005 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
2006 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
2007 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2008 BEGIN
2009 IF (l_pLog) THEN
2010 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2011 l_module || '.begin',
2012 '>>> ' || l_api_name);
2013 END IF;
2014
2015 -- Standard call to check for call compatibility.
2016 IF NOT FND_API.Compatible_API_Call (l_api_version ,
2017 p_api_version ,
2018 l_api_name ,
2019 G_PKG_NAME )
2020 THEN
2021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2022 END IF;
2023 -- Initialize message list if p_init_msg_list is set to TRUE.
2024 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2025 FND_MSG_PUB.initialize;
2026 END IF;
2027
2028 -- Initialize API return status to success
2029 x_return_status := FND_API.G_RET_STS_SUCCESS;
2030
2031 /* Initialize */
2032 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
2033
2034 /* Open Ref Cursor */
2035
2036 /* This cursor SQL should be dual maintained with the one used to
2037 get the pending shipping transaction count in WSH_INTEGRATION.
2041 OPEN l_ref_cur FOR
2038 GET_UNTRXD_SHPG_LINES_COUNT */
2039
2040 l_stmt_num := 10;
2042 'SELECT wdd.DELIVERY_DETAIL_ID,
2043 wnd.DELIVERY_ID,
2044 wdd.SOURCE_CODE,
2045 wdd.SOURCE_HEADER_ID,
2046 wdd.SOURCE_LINE_ID,
2047 wdd.SOURCE_HEADER_NUMBER,
2048 wdd.SOURCE_LINE_NUMBER,
2049 mif.ITEM_NUMBER,
2050 wdd.INVENTORY_ITEM_ID,
2051 wdd.ITEM_DESCRIPTION
2052 FROM wsh_delivery_details wdd,
2053 wsh_delivery_assignments wda,
2054 wsh_new_deliveries wnd,
2055 wsh_delivery_legs wdl,
2056 wsh_trip_stops wts,
2057 mtl_item_flexfields mif
2058 WHERE wdd.source_code = ''OE''
2059 AND wdd.released_status = ''C''
2060 AND wdd.inv_interfaced_flag in (''N'' ,''P'')
2061 AND wdd.organization_id = :i_org_id
2062 AND wda.delivery_detail_id = wdd.delivery_detail_id
2063 AND wnd.delivery_id = wda.delivery_id
2064 AND wnd.status_code in (''CL'',''IT'')
2065 AND wdl.delivery_id = wnd.delivery_id
2066 AND wts.pending_interface_flag in (''Y'', ''P'')
2067 AND trunc(wts.actual_departure_date) between :i_period_start_date
2068 AND :i_period_end_date
2069 AND wdl.pick_up_stop_id = wts.stop_id
2070 AND wdd.organization_id = mif.organization_id (+)
2071 AND wdd.inventory_item_id = mif.inventory_item_id (+)
2072 UNION ALL
2073 SELECT wdd.DELIVERY_DETAIL_ID,
2074 wnd.DELIVERY_ID,
2075 wdd.SOURCE_CODE,
2076 wdd.SOURCE_HEADER_ID,
2077 wdd.SOURCE_LINE_ID,
2078 wdd.SOURCE_HEADER_NUMBER,
2079 wdd.SOURCE_LINE_NUMBER,
2080 mif.ITEM_NUMBER,
2081 wdd.INVENTORY_ITEM_ID,
2082 wdd.ITEM_DESCRIPTION
2083 FROM wsh_delivery_details wdd,
2084 wsh_delivery_assignments wda,
2085 wsh_new_deliveries wnd,
2086 wsh_delivery_legs wdl,
2087 wsh_trip_stops wts,
2088 oe_order_lines_all oel,
2089 po_requisition_lines_all pl,
2090 mtl_item_flexfields mif
2091 WHERE wdd.source_code = ''OE''
2092 AND wdd.released_status = ''C''
2093 AND wdd.inv_interfaced_flag in (''N'' ,''P'')
2094 AND wda.delivery_detail_id = wdd.delivery_detail_id
2095 AND wnd.delivery_id = wda.delivery_id
2096 AND wnd.status_code in (''CL'',''IT'')
2097 AND wdl.delivery_id = wnd.delivery_id
2098 AND wts.pending_interface_flag in (''Y'', ''P'')
2099 AND trunc(wts.actual_departure_date) between :i_period_start_date
2100 AND :i_period_end_date
2101 AND wdd.source_line_id = oel.line_id
2102 AND wdd.source_document_type_id = 10
2103 AND oel.source_document_line_id = pl.requisition_line_id
2104 AND pl.destination_organization_id = :i_org_id
2105 AND pl.destination_organization_id <> pl.source_organization_id
2106 AND pl.destination_type_code = ''EXPENSE''
2107 AND wdl.pick_up_stop_id = wts.stop_id
2108 AND wts.stop_location_id = wnd.initial_pickup_location_id
2109 AND wdd.organization_id = mif.organization_id (+)
2110 AND wdd.inventory_item_id = mif.inventory_item_id (+)
2111 UNION ALL
2112 SELECT wdd.DELIVERY_DETAIL_ID,
2113 wnd.DELIVERY_ID,
2114 wdd.SOURCE_CODE,
2115 wdd.SOURCE_HEADER_ID,
2116 wdd.SOURCE_LINE_ID,
2117 wdd.SOURCE_HEADER_NUMBER,
2118 wdd.SOURCE_LINE_NUMBER,
2119 mif.ITEM_NUMBER,
2120 wdd.INVENTORY_ITEM_ID,
2121 wdd.ITEM_DESCRIPTION
2122 FROM wsh_delivery_details wdd,
2123 wsh_delivery_assignments wda,
2124 wsh_new_deliveries wnd,
2125 wsh_delivery_legs wdl,
2126 wsh_trip_stops wts,
2127 oe_order_lines_all oel,
2128 po_requisition_lines_all pl,
2129 mtl_interorg_parameters mip,
2130 mtl_item_flexfields mif
2131 WHERE wdd.source_code = ''OE''
2132 AND wdd.released_status = ''C''
2133 AND wdd.inv_interfaced_flag in (''N'' ,''P'')
2134 AND wda.delivery_detail_id = wdd.delivery_detail_id
2135 AND wnd.delivery_id = wda.delivery_id
2136 AND wnd.status_code in (''CL'',''IT'')
2137 AND wdl.delivery_id = wnd.delivery_id
2138 AND wts.pending_interface_flag in (''Y'', ''P'')
2139 AND trunc(wts.actual_departure_date) between :i_period_start_date
2140 AND :i_period_end_date
2141 AND wdd.source_line_id = oel.line_id
2142 AND wdd.source_document_type_id = 10
2143 AND oel.source_document_line_id = pl.requisition_line_id
2144 AND pl.destination_organization_id = :i_org_id
2145 AND pl.destination_organization_id <> pl.source_organization_id
2146 AND pl.destination_organization_id = mip.to_organization_id
2147 AND pl.source_organization_id = mip.from_organization_id
2148 AND mip.intransit_type = 1
2149 AND pl.destination_type_code <> ''EXPENSE''
2150 AND wdl.pick_up_stop_id = wts.stop_id
2151 AND wts.stop_location_id = wnd.initial_pickup_location_id
2152 AND wdd.organization_id = mif.organization_id (+)
2153 AND wdd.inventory_item_id = mif.inventory_item_id (+)
2154 ORDER BY 1'
2155 USING i_org_id, i_period_start_date, i_period_end_date,
2156 i_period_start_date, i_period_end_date, i_org_id,
2157 i_period_start_date, i_period_end_date, i_org_id;
2158
2159 /* create new context */
2160 l_stmt_num := 20;
2161 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
2165 /* get XML */
2162 DBMS_XMLGEN.setRowSetTag (l_ctx,'PENDING_SHIPPING_TRX');
2163 DBMS_XMLGEN.setRowTag (l_ctx,'PENDING_SHIPPING_TRANSACTION');
2164
2166 l_stmt_num := 30;
2167 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
2168
2169 x_record_count := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
2170 /* remove the header and append the rest to xml output */
2171 IF (x_record_count > 0) THEN
2172 /* Find the number of characters in the header and delete
2173 them. Header ends with '>'. Hence find first occurrence of
2174 '>' in the CLOB */
2175 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
2176 pattern => '>',
2177 offset => 1,
2178 nth => 1);
2179 DBMS_LOB.erase (l_xml_temp, l_offset,1);
2180 DBMS_LOB.append (x_xml_doc, l_xml_temp);
2181 END IF;
2182 /* close context and free memory */
2183 DBMS_XMLGEN.closeContext(l_ctx);
2184 CLOSE l_ref_cur;
2185 DBMS_LOB.FREETEMPORARY (l_xml_temp);
2186
2187 -- Standard call to get message count and if count is 1, get message info.
2188 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2189 p_data => x_msg_data);
2190
2191 IF (l_pLog) THEN
2192 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2193 l_module || '.end',
2194 '<<< ' || l_api_name);
2195 END IF;
2196 EXCEPTION
2197 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2199 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2200 p_data => x_msg_data);
2201 WHEN OTHERS THEN
2202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2203 IF (l_uLog) THEN
2204 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
2205 l_module || '.' || l_stmt_num,
2206 SUBSTRB (SQLERRM , 1 , 240));
2207 END IF;
2208
2209 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2210 THEN
2211 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2212 END IF;
2213 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2214 p_data => x_msg_data);
2215 END pending_shipping_trx;
2216
2217 PROCEDURE incomplete_eam_wo
2218 (p_api_version IN NUMBER,
2219 p_init_msg_list IN VARCHAR2,
2220 p_validation_level IN NUMBER,
2221 x_return_status OUT NOCOPY VARCHAR2,
2222 x_msg_count OUT NOCOPY NUMBER,
2223 x_msg_data OUT NOCOPY VARCHAR2,
2224 i_period_end_date IN DATE,
2225 i_org_id IN NUMBER,
2226 x_record_count OUT NOCOPY NUMBER,
2227 x_xml_doc IN OUT NOCOPY CLOB)
2228 IS
2229 l_api_name CONSTANT VARCHAR2(30) := 'incomplete_eam_wo';
2230 l_api_version CONSTANT NUMBER := 1.0;
2231 l_ref_cur SYS_REFCURSOR;
2232 l_ctx NUMBER;
2233 l_xml_temp CLOB;
2234 l_offset PLS_INTEGER;
2235 l_stmt_num NUMBER;
2236
2237 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2238 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
2239
2240 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
2241 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
2242 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
2243 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2244 BEGIN
2245 IF (l_pLog) THEN
2246 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2247 l_module || '.begin',
2248 '>>> ' || l_api_name);
2249 END IF;
2250
2251 -- Standard call to check for call compatibility.
2252 IF NOT FND_API.Compatible_API_Call (l_api_version ,
2253 p_api_version ,
2254 l_api_name ,
2255 G_PKG_NAME )
2256 THEN
2257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2258 END IF;
2259 -- Initialize message list if p_init_msg_list is set to TRUE.
2260 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2261 FND_MSG_PUB.initialize;
2262 END IF;
2263
2264 -- Initialize API return status to success
2265 x_return_status := FND_API.G_RET_STS_SUCCESS;
2266
2267 /* Initialize */
2268 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
2269
2270 /* Open Ref Cursor */
2271 l_stmt_num := 10;
2272 OPEN l_ref_cur FOR
2273 'SELECT wdj.WIP_ENTITY_ID,
2274 we.WIP_ENTITY_NAME,
2275 we.ENTITY_TYPE,
2276 wdj.ORGANIZATION_ID,
2277 wdj.DESCRIPTION,
2278 mif.CONCATENATED_SEGMENTS ACTIVITY,
2279 NVL (wdj.asset_number, NVL (wdj1.asset_number, wdj1.rebuild_serial_number)) ASSET_NUMBER,
2280 mif2.CONCATENATED_SEGMENTS ASSET_GROUP,
2281 decode(wdj.maintenance_object_type,
2282 3, cii.instance_description,
2283 2, ( SELECT description
2284 FROM mtl_system_items
2285 WHERE inventory_item_id = wdj.rebuild_item_id
2286 AND rownum = 1)) ASSET_DESCRIPTION,
2287 (SELECT department_code
2288 FROM bom_departments
2289 WHERE organization_id = wdj.organization_id
2293 ewodv.USER_DEFINED_STATUS_ID,
2290 AND department_id = wdj.owning_department) OWNING_DEPARTMENT_CODE,
2291 wdj.CLASS_CODE,
2292 wdj.STATUS_TYPE,
2294 ewodv.WORK_ORDER_STATUS,
2295 wdj.SCHEDULED_START_DATE,
2296 wdj.SCHEDULED_COMPLETION_DATE,
2297 pjm_project.all_proj_idtoname(wdj.project_id) PROJECT_NAME,
2298 pjm_project.all_task_idtoname(wdj.task_id) TASK_NAME,
2299 (SELECT meaning
2300 FROM mfg_lookups
2301 WHERE lookup_code = wdj.activity_type
2302 AND lookup_type = ''MTL_EAM_ACTIVITY_TYPE'') ACTIVITY_TYPE_DISP,
2303 (SELECT meaning
2304 FROM mfg_lookups
2305 WHERE lookup_code = wdj.activity_cause
2306 AND lookup_type = ''MTL_EAM_ACTIVITY_CAUSE'') ACTIVITY_CAUSE_DISP,
2307 (SELECT meaning
2308 FROM mfg_lookups
2309 WHERE lookup_code = wdj.activity_source
2310 AND lookup_type = ''MTL_EAM_ACTIVITY_SOURCE'') ACTIVITY_SOURCE_MEANING,
2311 cii.serial_number ASSET_SERIAL_NUMBER,
2312 (SELECT meaning
2313 FROM mfg_lookups
2314 WHERE lookup_code = wdj.work_order_type
2315 AND lookup_type = ''WIP_EAM_WORK_ORDER_TYPE'') WORK_ORDER_TYPE_DISP,
2316 wdj.DATE_RELEASED,
2317 wdj.DATE_COMPLETED,
2318 wdj.DATE_CLOSED,
2319 wdj.ESTIMATION_STATUS,
2320 (SELECT wip_entity_name
2321 FROM wip_entities
2322 WHERE wip_entity_id = wdj.parent_wip_entity_id
2323 AND organization_id = wdj.organization_id) PARENT_WIP_ENTITY_NAME,
2324 ewodv.WORK_ORDER_STATUS_PENDING,
2325 pjm_project.all_proj_idtonum(wdj.project_id) PROJECT_NUMBER,
2326 pjm_project.all_task_idtonum(wdj.task_id) TASK_NUMBER
2327 FROM wip_entities we,
2328 wip_discrete_jobs wdj1,
2329 wip_discrete_jobs wdj,
2330 mtl_system_items_kfv mif,
2331 mtl_system_items_kfv mif2,
2332 eam_work_order_details_v ewodv,
2333 csi_item_instances cii
2334 WHERE wdj.organization_id = :i_org_id
2335 AND we.entity_type = 6
2336 AND wdj.status_type = 3 /* Released */
2337 AND wdj.scheduled_completion_date <= :i_period_end_date
2338 AND wdj.organization_id = mif.organization_id (+)
2339 AND wdj.primary_item_id = mif.inventory_item_id (+)
2340 AND wdj.organization_id = mif2.organization_id (+)
2341 AND wdj.asset_group_id = mif2.inventory_item_id (+)
2342 AND wdj.wip_entity_id = we.wip_entity_id
2343 AND wdj.organization_id = we.organization_id
2344 AND wdj.parent_wip_entity_id = wdj1.wip_entity_id(+)
2345 AND wdj.organization_id = wdj1.organization_id(+)
2346 AND ewodv.wip_entity_id = wdj.wip_entity_id
2347 AND ewodv.organization_id = wdj.organization_id
2348 AND DECODE(wdj.maintenance_object_type,3,wdj.maintenance_object_id,NULL) = cii.instance_id(+)'
2349 USING i_org_id, i_period_end_date;
2350
2351 /* create new context */
2352 l_stmt_num := 20;
2353 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
2354 DBMS_XMLGEN.setRowSetTag (l_ctx,'INCOMPLETE_EAM_WO');
2355 DBMS_XMLGEN.setRowTag (l_ctx,'EAM_WORKORDER');
2356
2357 /* get XML */
2358 l_stmt_num := 30;
2359 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
2360
2361 x_record_count := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
2362
2363 /* remove the header and append the rest to xml output */
2364 IF (x_record_count > 0) THEN
2365 /* Find the number of characters in the header and delete
2366 them. Header ends with '>'. Hence find first occurrence of
2367 '>' in the CLOB */
2368 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
2369 pattern => '>',
2370 offset => 1,
2371 nth => 1);
2372 DBMS_LOB.erase (l_xml_temp, l_offset,1);
2373 DBMS_LOB.append (x_xml_doc, l_xml_temp);
2374 END IF;
2375 /* close context and free memory */
2376 DBMS_XMLGEN.closeContext(l_ctx);
2377 CLOSE l_ref_cur;
2378 DBMS_LOB.FREETEMPORARY (l_xml_temp);
2379
2380 -- Standard call to get message count and if count is 1, get message info.
2381 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2382 p_data => x_msg_data);
2383
2384 IF (l_pLog) THEN
2385 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2386 l_module || '.end',
2387 '<<< ' || l_api_name);
2388 END IF;
2389 EXCEPTION
2390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2392 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2393 p_data => x_msg_data);
2394 WHEN OTHERS THEN
2395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2396 IF (l_uLog) THEN
2397 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
2398 l_module || '.' || l_stmt_num,
2399 SUBSTRB (SQLERRM , 1 , 240));
2400 END IF;
2401
2402 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2403 THEN
2404 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2405 END IF;
2406 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2407 p_data => x_msg_data);
2408 END incomplete_eam_wo;
2409
2410 PROCEDURE pending_lcm_trx
2411 (p_api_version IN NUMBER,
2412 p_init_msg_list IN VARCHAR2,
2416 x_msg_data OUT NOCOPY VARCHAR2,
2413 p_validation_level IN NUMBER,
2414 x_return_status OUT NOCOPY VARCHAR2,
2415 x_msg_count OUT NOCOPY NUMBER,
2417 i_period_end_date IN DATE,
2418 i_org_id IN NUMBER,
2419 x_record_count OUT NOCOPY NUMBER,
2420 x_xml_doc IN OUT NOCOPY CLOB)
2421 IS
2422 l_api_name CONSTANT VARCHAR2(30) := 'pending_lcm_trx';
2423 l_api_version CONSTANT NUMBER := 1.0;
2424 l_ref_cur SYS_REFCURSOR;
2425 l_ctx NUMBER;
2426 l_xml_temp CLOB;
2427 l_offset PLS_INTEGER;
2428 l_stmt_num NUMBER;
2429
2430 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2431 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
2432
2433 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
2434 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
2435 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
2436 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2437 BEGIN
2438 IF (l_pLog) THEN
2439 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2440 l_module || '.begin',
2441 '>>> ' || l_api_name);
2442 END IF;
2443
2444 -- Standard call to check for call compatibility.
2445 IF NOT FND_API.Compatible_API_Call (l_api_version,
2446 p_api_version,
2447 l_api_name,
2448 G_PKG_NAME )
2449 THEN
2450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2451 END IF;
2452 -- Initialize message list if p_init_msg_list is set to TRUE.
2453 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2454 FND_MSG_PUB.initialize;
2455 END IF;
2456
2457 -- Initialize API return status to success
2458 x_return_status := FND_API.G_RET_STS_SUCCESS;
2459
2460 /* Initialize */
2461 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
2462
2463 /* Open Ref Cursor */
2464 l_stmt_num := 10;
2465 OPEN l_ref_cur FOR
2466 'SELECT clai.transaction_id,
2467 clai.rcv_transaction_id,
2468 clai.organization_id,
2469 clai.inventory_item_id,
2470 clai.transaction_date,
2471 clai.prior_landed_cost,
2472 clai.new_landed_cost,
2473 clai.group_id,
2474 clai.request_id,
2475 mif.item_number,
2476 clai.process_status,
2477 ml.meaning process_status_code,
2478 err.error_column,
2479 err.error_message
2480 FROM cst_lc_adj_interface clai,
2481 cst_lc_adj_interface_errors err,
2482 mtl_item_flexfields mif,
2483 mfg_lookups ml
2484 WHERE clai.organization_id = :i_org_id
2485 AND transaction_date <= :i_period_end_date
2486 AND err.transaction_id (+) = clai.transaction_id
2487 AND clai.organization_id = mif.organization_id (+)
2488 AND clai.inventory_item_id = mif.inventory_item_id(+)
2489 AND ml.lookup_type = ''LANDED_COST_ADJ_PROCESS_STATUS''
2490 AND ml.lookup_code = clai.process_status
2491 ORDER BY clai.transaction_date, clai.transaction_id'
2492 USING i_org_id, i_period_end_date;
2493
2494 /* create new context */
2495 l_stmt_num := 20;
2496 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
2497 DBMS_XMLGEN.setRowSetTag (l_ctx,'PENDING_LCM_TRX');
2498 DBMS_XMLGEN.setRowTag (l_ctx,'PENDING_LCM_TRANSACTION');
2499
2500 /* get XML */
2501 l_stmt_num := 30;
2502 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
2503
2504 x_record_count := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
2505 /* remove the header and append the rest to xml output */
2506 IF (x_record_count > 0) THEN
2507 /* Find the number of characters in the header and delete
2508 them. Header ends with '>'. Hence find first occurrence of
2509 '>' in the CLOB */
2510 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
2511 pattern => '>',
2512 offset => 1,
2513 nth => 1);
2514 DBMS_LOB.erase (l_xml_temp, l_offset,1);
2515 DBMS_LOB.append (x_xml_doc, l_xml_temp);
2516 END IF;
2517 /* close context and free memory */
2518 DBMS_XMLGEN.closeContext(l_ctx);
2519 CLOSE l_ref_cur;
2520 DBMS_LOB.FREETEMPORARY (l_xml_temp);
2521
2522 -- Standard call to get message count and if count is 1, get message info.
2523 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2524 p_data => x_msg_data);
2525 IF (l_pLog) THEN
2526 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
2527 l_module || '.end',
2528 '<<< ' || l_api_name);
2529 END IF;
2530 EXCEPTION
2531 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2533 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,
2534 p_data => x_msg_data);
2535 WHEN OTHERS THEN
2536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2537 IF (l_uLog) THEN
2538 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
2539 l_module || '.' || l_stmt_num,
2540 SUBSTRB (SQLERRM , 1 , 240));
2541 END IF;
2542
2543 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2544 THEN
2545 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,l_api_name);
2546 END IF;
2547 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2548 p_data => x_msg_data);
2549 END pending_lcm_trx;
2550
2551 END CST_PendingTxnsReport_PVT;