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