[Home] [Help]
PACKAGE BODY: APPS.CST_LCMADJUSTMENTS_PVT
Source
1 PACKAGE BODY CST_LcmAdjustments_PVT AS
2 /* $Header: CSTLCADB.pls 120.0.12010000.8 2008/12/22 07:00:54 mpuranik noship $ */
3
4 /*------------------------------------------------------------------------------------------
5 Landed Cost Adjustment algorithm:
6
7 The worker runs for a subset of adjustment transactions in an organization.
8 Each set is stamped by a unique group id.
9
10 Phase 1: Validation
11 -------------------
12 1. Validate all the unvalidated interface records i.e. Process_status = 1
13 (Pending).
14 2. Transactions that fail validation are set to process_status=3 amd are
15 not picked up for processing until they are corrected and resubmitted
16 by users. Validated records are set to process_status=2 (Validated).
17
18 Phase 2: Adjustment Processing
19 ------------------------------
20 1. Collect all the organization level information, such as accounts,
21 ledger information etc.
22 2. For each lcm adjustment transaction -
23 a. Retrieve all the data related to the receipt transaction and
24 its child transactions (Correct, Return, Deliver).
25 Only transactions that have transaction date prior to the
26 transaction date of the landed cost adjustment transaction
27 are considered for adjustment.
28 b. Generate the RCV_ACCOUNTING_EVENTS for the receipts and
29 delivery adjustments.
30 For each LCM transaction following receiving events are created:-
31 i. Adjustments against the net received quantity.
32 Event Type Id 15 i.e. Landed Cost Adjustments for Receipts.
33 ii. Adjustments against the net delivered quantity of Asset items
34 to Asset Subinventories. Event Type Id 16 - Landed Cost Adjustments
35 for Deliveries to Asset.
36 iii. Adjustments against the net delivered quantity of Expense items or
37 to Expense Subinventories. Event Type Id 17 - Landed Cost Adjustments
38 for Deliveries to Expense. For deliveries to Expense the accounting
39 is created against Receiving Inspection and Expense Account.
40 For wms-enabled subinventories, this account should be derived from the
41 cost group against which the delivery was made.
42 iv. Generate the average / layer cost update transactions.
43 - This update transactions would be created only for the
44 delivery transactions made against Asset items and subinventories.
45 - The average / layer cost update will be only done for the net quantity
46 (incoming quantity) that was delivered. The net quantity is
47 populated in the primary_quantity column of the transaction. Please
48 - The Average cost update transaction is created against the cost group
49 against which the original delivery was made. In case of wms-enabled
50 organizations, this could mean multiple cost groups for each parent receipt.
51 In such a case, multiple average cost update transactions will be created.
52 - The layer cost update is done against the layers that were hit by the
53 original delivery and the layer values are updated in proportion of the
54 delivered quantities against each layer.
55 - Create records into tables rcv_accounting_events, rcv_receiving_sub_ledger,
56 mtl_material_transactions, mtl_cst_txn_cost_details and XLA_EVENTS_INT_GT using
57 the global temporary tables.
58 ------------------------------------------------------------------------------------------*/
59
60 G_PKG_NAME CONSTANT VARCHAR2(30) :='CST_LcmAdjustments_PVT';
61 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
62 G_DEBUG CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
63
64 G_PO_APPLICATION_ID CONSTANT NUMBER := 201;
65
66 -- Accounting Line Types
67 RECEIVING_INSPECTION CONSTANT VARCHAR2(30) := 'Receiving Inspection';
68 LC_ABSORPTION CONSTANT VARCHAR2(30) := 'Landed Cost Absorption';
69 PURCHASE_PRICE_VARIANCE VARCHAR2(30) := 'Purchase Price Variance';
70 INVENTORY_EXPENSE VARCHAR2(30) := 'Expense';
71
72
73 /*===========================================================================+
74 | |
75 | PROCEDURE : Process_LcmAdjustments |
76 | |
77 | DESCRIPTION : This Procedure is the main procedure for the landed |
78 | cost adjustment worker. This procedure makes calls |
79 | to other procedures in the package which validate, |
80 | calculate and create landed cost adjustments. |
81 | |
82 | CALLED FROM : Launch_Workers (CST_LcmAdjustments_PUB) |
83 | |
84 | Parameters : |
85 | IN : p_group_id IN NUMBER REQUIRED |
86 | p_organization_id IN NUMBER REQUIRED |
87 | |
88 | OUT : errbuf OUT NOCOPY VARCHAR2 |
89 | retcode OUT NOCOPY NUMBER |
90 | |
91 | NOTES : None |
92 | |
93 | |
94 +===========================================================================*/
95
96 PROCEDURE Process_LcmAdjustments
97 (
98 errbuf OUT NOCOPY VARCHAR2,
99 retcode OUT NOCOPY NUMBER,
100 p_group_id IN NUMBER,
101 p_organization_id IN NUMBER
102 )
103
104 IS
105 l_api_name CONSTANT VARCHAR2(30) :='Process_LcmAdjustments';
106 l_api_version CONSTANT NUMBER := 1.0;
107 l_return_status VARCHAR2(1);
108 l_module CONSTANT VARCHAR2(100) := 'cst.plsql.'|| G_PKG_NAME || '.' || l_api_name;
109
110 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
111 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
112 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
113 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
114 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
115 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
116
117 l_stmt_num NUMBER;
118 l_num_records NUMBER;
119 l_no_of_errored NUMBER;
120 l_no_of_validated NUMBER := 0;
121 l_conc_status BOOLEAN;
122 l_wms_enabled_flag VARCHAR2(1);
123 l_primary_cost_method NUMBER;
124 l_ledger_id NUMBER;
125 INVALID_ADJUSTMENT_TXNS EXCEPTION;
126
127 BEGIN
128
129 l_stmt_num := 0;
130
131 /* Procedure level log message for Entry point */
132 IF (l_pLog) THEN
133 FND_LOG.STRING(
134 FND_LOG.LEVEL_PROCEDURE,
135 l_module || '.begin',
136 '>> ' || l_api_name || ': Parameters:' ||
137 ' Group id ' || p_group_id ||
138 ', Organization id ' || p_organization_id
139 );
140 END IF;
141
142 -- Initialize message list
143 FND_MSG_PUB.initialize;
144
145 /* Initialize API return status to success */
146 l_return_status := FND_API.G_RET_STS_SUCCESS;
147
148 l_stmt_num := 10;
149
150 /* Call the landed cost interface records validation proceduere */
151 Validate_Lc_Interface
152 (p_api_version => l_api_version,
153 p_init_msg_list => FND_API.G_FALSE,
154 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
155 p_group_id => p_group_id,
156 p_organization_id => p_organization_id,
157 x_no_of_errored => l_no_of_errored,
158 x_return_status => l_return_status);
159
160 IF l_return_status <> FND_API.g_ret_sts_success THEN
161 IF l_exceptionLog THEN
162 fnd_message.set_name('BOM','CST_LOG_EXCEPTION');
163 fnd_message.set_token('CALLED','Validate_Lc_Interface');
164
165 END IF;
166
167 RAISE FND_API.g_exc_unexpected_error;
168 END IF;
169
170 l_stmt_num := 15;
171
172 SELECT Count(*)
173 INTO l_no_of_validated
174 FROM cst_lc_adj_interface
175 WHERE process_status = 2
176 AND organization_id = p_organization_id
177 AND group_id = p_group_id
178 AND ROWNUM = 1;
179
180 l_stmt_num := 20;
181
182 /* Call the remaining procedures only if there are any validated records */
183 IF l_no_of_validated > 0 THEN
184
185 l_stmt_num := 30;
186
187 /* Populate common information related to the lcm adjustment transaction */
188 Populate_Lcm_Adjustment_Info
189 (p_api_version => l_api_version,
190 p_init_msg_list => FND_API.G_FALSE,
191 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
192 p_group_id => p_group_id,
193 p_organization_id => p_organization_id,
194 x_ledger_id => l_ledger_id,
195 x_primary_cost_method => l_primary_cost_method,
196 x_wms_enabled_flag => l_wms_enabled_flag,
197 x_return_status => l_return_status);
198
199 IF l_return_status <> FND_API.g_ret_sts_success THEN
200 IF l_exceptionLog THEN
201 fnd_message.set_name('BOM','CST_LOG_EXCEPTION');
202 fnd_message.set_token('CALLED','Populate_Temp_Adjustment_Data');
203
204 END IF;
205 RAISE FND_API.g_exc_unexpected_error;
206 END IF;
207
208 l_stmt_num := 40;
209
210 /* Populate the adjustment data into the global temporary tables */
211 Populate_Temp_Adjustment_Data
212 (p_api_version => l_api_version,
213 p_init_msg_list => FND_API.G_FALSE,
214 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
215 p_primary_cost_method => l_primary_cost_method,
216 p_wms_enabled_flag => l_wms_enabled_flag,
217 x_return_status => l_return_status);
218
219 IF l_return_status <> FND_API.g_ret_sts_success THEN
220 IF l_exceptionLog THEN
221 fnd_message.set_name('BOM','CST_LOG_EXCEPTION');
222 fnd_message.set_token('CALLED','Populate_Temp_Adjustment_Data');
223
224 END IF;
225
226 RAISE FND_API.g_exc_unexpected_error;
227 END IF;
228
229 l_stmt_num := 50;
230
231 /* Insert the adjustment data from the GTTs into the actual tables */
232 Insert_Adjustment_Data
233 (p_api_version => l_api_version,
234 p_init_msg_list => FND_API.G_FALSE,
235 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
236 p_group_id => p_group_id,
237 p_organization_id => p_organization_id,
238 p_ledger_id => l_ledger_id,
239 x_return_status => l_return_status);
240
241 IF l_return_status <> FND_API.g_ret_sts_success THEN
242 IF l_exceptionLog THEN
243 fnd_message.set_name('BOM','CST_LOG_EXCEPTION');
244 fnd_message.set_token('CALLED','Insert_Adjustment_Data');
245
246 END IF;
247 RAISE FND_API.g_exc_unexpected_error;
248 END IF;
249
250 l_stmt_num := 60;
251
252 COMMIT WORK;
253
254 END IF; /* l_no_of_validated > 0 */
255
256 l_stmt_num := 70;
257 /* Set Status to warning if some transactions have failed validation */
258 IF l_no_of_errored > 0 THEN
259 l_stmt_num := 80;
260 RAISE INVALID_ADJUSTMENT_TXNS;
261
262 END IF;
263
264 l_stmt_num := 90;
265 IF (l_pLog) THEN
266 FND_LOG.STRING(
267 FND_LOG.LEVEL_PROCEDURE,
268 l_module || '.end',
272
269 '<< ' || l_api_name
270 );
271 END IF;
273 EXCEPTION
274 WHEN INVALID_ADJUSTMENT_TXNS THEN
275
276 FND_MESSAGE.SET_NAME('BOM', 'CST_INVALID_LCM_TRANSACTIONS');
277 FND_MESSAGE.SET_TOKEN('NUM', l_no_of_errored);
278 FND_MESSAGE.SET_TOKEN('ORG', p_organization_id);
279 FND_MESSAGE.SET_TOKEN('GROUP', p_group_id);
280 FND_MESSAGE.SET_MODULE(l_module);
281 fnd_file.put_line( FND_FILE.LOG, FND_MESSAGE.GET);
282
283 IF l_ulog THEN
284 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
285 END IF;
286
287 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',FND_MESSAGE.GET);
288
289 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
290 ROLLBACK;
291
292 IF (l_ulog) THEN
293 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
294 END IF;
295
296 /* Set concurrent program status to error */
297 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',FND_MESSAGE.GET);
298
299 WHEN FND_API.G_EXC_ERROR THEN
300 ROLLBACK;
301
302 IF (l_exceptionLog) THEN
303 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,l_module, TRUE);
304 END IF;
305
306 /* Set concurrent program status to error */
307 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',FND_MESSAGE.GET);
308
309 WHEN OTHERS THEN
310 ROLLBACK;
311
312 FND_MESSAGE.set_name('BOM', 'CST_UNEXP_ERROR');
313 FND_MESSAGE.set_token('PACKAGE', G_PKG_NAME);
314 FND_MESSAGE.set_token('PROCEDURE',l_api_name);
315 FND_MESSAGE.set_token('STATEMENT',to_char(l_stmt_num));
316 IF (l_uLog) THEN
317 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
318 FND_MSG_PUB.ADD;
319
320 END IF;
321
322 FND_MESSAGE.SET_NAME('BOM','CST_LOG_UNEXPECTED');
323 FND_MESSAGE.SET_TOKEN('SQLERRM',SQLERRM);
324 IF (l_uLog) THEN
325 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
326 FND_MSG_PUB.ADD;
327
328 END IF;
329
330 /* Set concurrent program status to error */
331 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
332 FND_MESSAGE.GET);
333 fnd_file.put_line( FND_FILE.LOG, FND_MESSAGE.GET);
334
335 END Process_LcmAdjustments;
336
337 /*===========================================================================+
338 | |
339 | PROCEDURE : Populate_Lcm_Adjustment_Info |
340 | |
341 | DESCRIPTION : This procedure populates CST_LC_ADJ_ACCTG_INFO_GT with |
342 | the organization level information and CST_LC_RCV_TXN_GT |
343 | with the entire hierarchy of transaction within the |
344 | cutoff date under the parent receipt transaction of the |
345 | lcm adjustment transaction. |
346 | |
347 | |
348 | CALLED FROM : Process_LcmAdjustments Procedure |
349 | |
350 | Parameters : |
351 | IN : p_group_id IN NUMBER REQUIRED |
352 | p_organization_id IN NUMBER REQUIRED |
353 | p_api_version IN NUMBER REQUIRED |
354 | p_init_msg_list IN VARCHAR2 REQUIRED |
355 | p_validation_level IN NUMBER REQUIRED |
356 | |
357 | OUT : x_ledger_id OUT NOCOPY VARCHAR2 |
358 | x_primary_cost_method OUT NOCOPY NUMBER |
359 | x_primary_cost_method OUT NOCOPY VARCHAR2 |
360 | x_return_status OUT NOCOPY NUMBER |
361 | |
362 | NOTES : None |
363 | |
364 | |
365 +===========================================================================*/
366
367 PROCEDURE Populate_Lcm_Adjustment_Info
368 (
369 p_api_version IN NUMBER,
370 p_init_msg_list IN VARCHAR2,
371 p_validation_level IN NUMBER,
372 p_group_id IN NUMBER,
373 p_organization_id IN NUMBER,
374 x_ledger_id OUT NOCOPY NUMBER,
375 x_primary_cost_method OUT NOCOPY NUMBER,
376 x_wms_enabled_flag OUT NOCOPY VARCHAR2,
377 x_return_status OUT NOCOPY VARCHAR2
378 )
379
380 IS
381 l_api_name CONSTANT VARCHAR2(30) :='Populate_Lcm_Adjustment_Info';
382 l_api_version CONSTANT NUMBER := 1.0;
383 l_return_status VARCHAR2(1);
384 l_module CONSTANT VARCHAR2(100) := 'cst.plsql.'|| G_PKG_NAME || '.' || l_api_name;
385
386 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
387 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
391 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
388 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
389 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
390 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
392
393 l_stmt_num NUMBER;
394 l_num_records NUMBER;
395 l_msg_data VARCHAR2(240);
396
397 l_err_num NUMBER;
398 l_error_code VARCHAR2(240);
399 l_error_msg VARCHAR2(240);
400 l_acct_id NUMBER;
401 l_lcm_account NUMBER;
402 l_lcm_var_account NUMBER;
403 l_receiving_account NUMBER;
404 l_purchase_price_var_account NUMBER;
405 l_org_expense_account NUMBER;
406 l_primary_cost_method NUMBER;
407 l_wms_enabled_flag varchar2(1);
408 l_legal_entity_id NUMBER;
409 l_ledger_id NUMBER;
410 l_operating_unit NUMBER;
411 l_chart_of_accounts_id NUMBER;
412 l_currency_code varchar2(15);
413 l_user_je_category_name NUMBER;
414 l_user_je_source_name NUMBER;
415 l_minimum_accountable_unit NUMBER;
416 l_precision NUMBER;
417
418 cursor c_lcm_txns (p_group_id number) is
419 select transaction_id, transaction_date, rcv_transaction_id
420 from cst_lc_adj_interface
421 where process_status = 2
422 and organization_id = p_organization_id
423 and group_id = p_group_id;
424
425 CURSOR c_adj_offset_account IS
426 SELECT t.*, t.rowid
427 FROM cst_lc_accounts_gt t;
428
429 BEGIN
430
431 l_stmt_num := 0;
432
433 /* Procedure level log message for Entry point */
434 IF (l_pLog) THEN
435 FND_LOG.STRING(
436 FND_LOG.LEVEL_PROCEDURE,
437 l_module || '.begin',
438 '>> ' || l_api_name || ': Parameters:' ||
439 ' Api version ' || p_api_version ||
440 ', Init msg list ' || p_init_msg_list ||
441 ', Validation level ' || p_validation_level ||
442 ', Group id ' || p_group_id ||
443 ', Organization id ' || p_organization_id
444 );
445 END IF;
446
447 /* Standard call to check for call compatibility */
448 IF NOT FND_API.Compatible_API_Call ( l_api_version,
449 p_api_version,
450 l_api_name,
451 G_PKG_NAME )
452 THEN
453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
454 END IF;
455
456 /* Initialize message list if p_init_msg_list is set to TRUE */
457 IF FND_API.to_Boolean( p_init_msg_list ) THEN
458 FND_MSG_PUB.initialize;
459 END IF;
460
461 /* Initialize API return status to success */
462 x_return_status := FND_API.G_RET_STS_SUCCESS;
463 l_return_status := FND_API.G_RET_STS_SUCCESS;
464
465 l_stmt_num := 10;
466
467 /* Retrieve the organization / ledger / OU related information for the organization */
468 select rp.lcm_account_id,
469 mp.lcm_var_account,
470 rp.receiving_account_id,
471 mp.purchase_price_var_account,
472 mp.expense_account,
473 mp.primary_cost_method,
474 mp.wms_enabled_flag,
475 cai.legal_entity,
476 cai.ledger_id,
477 cai.operating_unit,
478 nvl(gsob.chart_of_accounts_id, 0),
479 fc.currency_code,
480 fc.minimum_accountable_unit,
481 fc.precision
482 into l_lcm_account,
483 l_lcm_var_account,
484 l_receiving_account,
485 l_purchase_price_var_account,
486 l_org_expense_account,
487 l_primary_cost_method,
488 l_wms_enabled_flag,
489 l_legal_entity_id,
490 l_ledger_id,
491 l_operating_unit,
492 l_chart_of_accounts_id,
493 l_currency_code,
494 l_minimum_accountable_unit,
495 l_precision
496 from rcv_parameters rp,
497 mtl_parameters mp,
498 cst_acct_info_v cai,
499 gl_sets_of_books gsob,
500 fnd_currencies fc
501 where rp.organization_id = p_organization_id
502 and mp.organization_id = p_organization_id
503 and cai.organization_id = p_organization_id
504 and gsob.set_of_books_id = cai.ledger_id
505 and fc.currency_code = gsob.currency_code;
506
507 l_stmt_num := 20;
508
509 /* Return the primary cost method, wms flag and ledger id */
510 x_primary_cost_method := l_primary_cost_method;
511 x_wms_enabled_flag := l_wms_enabled_flag;
512 x_ledger_id := l_ledger_id;
513
514 l_stmt_num := 30;
515
516 /* Insert all the PO, accounting and Landed cost adjustment related information for
517 the lcm transaction. This will be used by all the events and accounting created
518 for this lcm transaction */
519 INSERT INTO CST_LC_ADJ_ACCTG_INFO_GT
520 (lcm_transaction_id,
521 rcv_transaction_id,
522 inventory_item_id,
523 prior_landed_cost,
524 new_landed_cost,
525 transaction_date,
526 accounting_date,
527 organization_id,
528 po_number,
529 po_header_id,
530 po_release_id,
531 po_line_id,
532 po_line_location_id,
533 source_doc_unit_of_measure,
534 primary_unit_of_measure,
535 lcm_account,
536 lcm_var_account,
540 legal_entity_id,
537 receiving_account,
538 purchase_price_var_account,
539 org_expense_account,
541 ledger_id,
542 operating_unit,
543 chart_of_accounts_id,
544 func_currency_code,
545 func_minimum_accountable_unit,
546 func_precision,
547 period_name,
548 acct_period_id,
549 inventory_asset_flag
550 )
551 SELECT li.transaction_id,
552 li.rcv_transaction_id,
553 li.inventory_item_id,
554 li.prior_landed_cost,
555 li.new_landed_cost,
556 li.transaction_date,
557 INV_LE_TIMEZONE_PUB.Get_Le_Day_Time_For_Ou(
558 li.transaction_date,
559 l_operating_unit),
560 li.organization_id,
561 poh.segment1,
562 rt.po_header_id,
563 rt.po_release_id,
564 rt.po_line_id,
565 rt.po_line_location_id,
566 rt.source_doc_unit_of_measure,
567 msi.primary_unit_of_measure,
568 l_lcm_account,
569 l_lcm_var_account,
570 l_receiving_account,
571 l_purchase_price_var_account,
572 l_org_expense_account,
573 l_legal_entity_id,
574 l_ledger_id,
575 l_operating_unit,
576 l_chart_of_accounts_id,
577 l_currency_code,
578 l_minimum_accountable_unit,
579 l_precision,
580 gps.period_name,
581 oap.acct_period_id,
582 msi.inventory_asset_flag
583 FROM cst_lc_adj_interface li,
584 gl_period_statuses gps,
585 mtl_system_items_b msi,
586 po_headers_all poh,
587 org_acct_periods oap,
588 rcv_transactions rt
589 WHERE li.group_id = p_group_id
590 AND li.organization_id = p_organization_id
591 AND li.process_status = 2
592 AND gps.application_id = G_PO_APPLICATION_ID
593 AND gps.set_of_books_id = l_ledger_id
594 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(li.transaction_date, l_legal_entity_id) >= Trunc(gps.start_date)
595 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(li.transaction_date, l_legal_entity_id) <= Trunc(gps.end_date)
596 AND gps.adjustment_period_flag <> 'Y'
597 AND msi.inventory_item_id = li.inventory_item_id
598 AND msi.organization_id = li.organization_id
599 AND rt.transaction_id = li.rcv_transaction_id
600 AND poh.po_header_id = rt.po_header_id
601 AND oap.organization_id = li.organization_id
602 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(li.transaction_date, l_legal_entity_id) >= Trunc(oap.period_start_date)
603 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(li.transaction_date, l_legal_entity_id) <= Trunc(oap.schedule_close_date);
604
605 IF (l_sLog AND l_pLog) THEN
606 l_num_records := SQL%ROWCOUNT;
607 FND_LOG.STRING(
608 FND_LOG.LEVEL_PROCEDURE,
609 l_module || '.query(' || l_stmt_num || ')',
610 l_num_records || ' Rows inserted'
611 );
612
613 END IF;
614
615 l_stmt_num := 40;
616
617 /* Insert all the receiving side transactions that have the receipt transaction
618 corresponding to the lcm adjustment as their root. */
619 for c_lt in c_lcm_txns (p_group_id) loop
620
621 l_stmt_num := 50;
622 INSERT INTO CST_LC_RCV_TXN_GT
623 (group_id, lcm_transaction_id,
624 rcv_transaction_id,
625 parent_transaction_id,
626 accounting_event_id,
627 transaction_type,
628 source_doc_quantity,
629 primary_quantity,
630 subinventory_code,
631 po_distribution_id
632 )
633 SELECT p_group_id, -1 * c_lt.transaction_id,
634 transaction_id,
635 parent_transaction_id,
636 NULL, /* accounting_event_id*/
637 transaction_type,
638 rt.source_doc_quantity,
639 rt.primary_quantity,
640 rt.subinventory,
641 rt.po_distribution_id
642 FROM rcv_transactions rt
643 WHERE rt.transaction_type IN ('RECEIVE', 'MATCH', 'RETURN TO VENDOR', 'CORRECT', 'DELIVER', 'RETURN TO RECEIVING')
644 AND rt.transaction_date < c_lt.transaction_date
645 START WITH rt.transaction_id = c_lt.rcv_transaction_id
646 CONNECT BY rt.parent_transaction_id = PRIOR rt.transaction_id;
647
648 IF (l_sLog AND l_pLog) THEN
649 l_num_records := SQL%ROWCOUNT;
650 FND_LOG.STRING(
651 FND_LOG.LEVEL_PROCEDURE,
652 l_module || '.query(' || l_stmt_num || ')',
653 l_num_records || ' Rows inserted'
654 );
655
656 END IF;
657
658 end loop;
659
660 l_stmt_num := 60;
661 INSERT INTO CST_LC_RCV_TXN_GT
662 (group_id,
663 lcm_transaction_id,
664 accounting_event_id,
665 rcv_transaction_id,
666 parent_transaction_id,
667 po_distribution_id,
668 source_doc_quantity,
669 primary_quantity,
670 transaction_type,
671 parent_transaction_type,
672 subinventory_code
673 )
674 SELECT rt.group_id,
675 -1 * rt.lcm_transaction_id,
676 rae.accounting_event_id,
677 rt.rcv_transaction_id,
678 NULL, /* parent_transaction_id */
679 nvl(rae.po_distribution_id, rt.po_distribution_id),
680 nvl(rae.source_doc_quantity, rt.source_doc_quantity),
681 nvl(rae.primary_quantity,rt.primary_quantity),
682 rt.transaction_type,
683 Decode(rt.transaction_type, 'CORRECT', rt_parent.transaction_type, rt.transaction_type) parent_transaction_type,
687 CST_LC_RCV_TXN_GT rt
684 rt.subinventory_code
685 FROM rcv_transactions rt_parent,
686 rcv_accounting_events rae,
688 WHERE rae.rcv_transaction_id (+) = rt.rcv_transaction_id
689 AND Nvl(rae.event_type_id,2) in (1,2,3,4,5,6)
690 AND rt_parent.transaction_id (+) = rt.parent_transaction_id;
691
692 IF (l_sLog AND l_pLog) THEN
693 l_num_records := SQL%ROWCOUNT;
694 FND_LOG.STRING(
695 FND_LOG.LEVEL_PROCEDURE,
696 l_module || '.query(' || l_stmt_num || ')',
697 l_num_records || ' Rows inserted'
698 );
699
700 END IF;
701
702 l_stmt_num := 65;
703 DELETE FROM CST_LC_RCV_TXN_GT
704 WHERE lcm_transaction_id < 0;
705
706 IF (l_sLog AND l_pLog) THEN
707 l_num_records := SQL%ROWCOUNT;
708 FND_LOG.STRING(
709 FND_LOG.LEVEL_PROCEDURE,
710 l_module || '.query(' || l_stmt_num || ')',
711 l_num_records || ' Rows Deleted'
712 );
713
714 END IF;
715
716 l_stmt_num := 70;
717 /*--------------------------------------------------------------------------------------------------
718 COST METHOD: STANDARD |
719 ---------------------------------------------------------------------------------------------------|
720 SCENARIO NO. | WMS | ASSET | ACCOUNTING LINE TYPE | ID || ACCOUNT |
721 ---------------------------------------------------------------------------------------------------|
722 1. | Y | Y | Purchase price variance | 6 || ORG PPV ACCOUNT |
723 2. | Y | N | Expense | 2 || SUBINVENTORY EXPENSE ACCOUNT |
724 3. | N | Y | Purchase price variance | 6 || CG PPV ACCOUNT |
725 4. | N | N | Expense | 2 || CG EXPENSE ACCOUNT |
726 ---------------------------------------------------------------------------------------------------|
727 COST METHOD: ACTUAL |
728 ---------------------------------------------------------------------------------------------------|
729 SCENARIO NO. | WMS | ASSET | ACCOUNTING LINE TYPE | ID || ACCOUNT |
730 ---------------------------------------------------------------------------------------------------|
731 5. | Y | Y | Landed Cost absorption | 38 || ORG LANDED COST ABSORPTION ACCOUNT|
732 6. | Y | N | Expense | 2 || SUBINVENTORY EXPENSE ACCOUNT |
733 7. | N | Y | Landed Cost Absorption | 38 || ORG LANDED COST ABSORPTION ACCOUNT|
734 8. | N | N | Expense | 2 || CG EXPENSE ACCOUNT |
735 --------------------------------------------------------------------------------------------------*/
736 INSERT INTO CST_LC_ACCOUNTS_GT
737 (LCM_TRANSACTION_ID,
738 RCV_TRANSACTION_ID,
739 TRANSACTION_ID,
740 COST_GROUP_ID,
741 SUBINVENTORY_CODE,
742 ACCOUNT,
743 ACCOUNTING_LINE_TYPE_ID,
744 ACCOUNTING_LINE_TYPE,
745 DEBIT_CREDIT_FLAG,
746 EXP_FLAG
747 )
748 SELECT rt.lcm_transaction_id,
749 rt.rcv_transaction_id,
750 Max(mmt.transaction_id),
751 Decode(l_wms_enabled_flag, 'Y', mmt.cost_group_id, NULL) cost_group_id,
752 rt.subinventory_code,
753 Decode( /* Derive Scenarios */
754 Decode(l_primary_cost_method, 1,
755 Decode(l_wms_enabled_flag, 'N',
756 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
757 1,
758 2), 2),
759 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
760 3,
761 4), 4)),
762 Decode(l_wms_enabled_flag, 'N',
763 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
764 5,
765 6), 6),
766 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
767 7,
768 8), 8))),
769 1,
770 acc.purchase_price_var_account,
771 2,
772 mse.expense_account,
773 3,
774 Decode(mmt.cost_group_id, 1, acc.purchase_price_var_account, ccga.purchase_price_var_account),
775 4,
776 Decode(mmt.cost_group_id, 1, nvl(mse.expense_account, acc.org_expense_account), ccga.expense_account),
777 5,
778 acc.lcm_account,
779 6,
780 nvl(mse.expense_account, acc.org_expense_account),
781 7,
782 acc.lcm_account,
783 8,
784 Decode(mmt.cost_group_id, 1, nvl(mse.expense_account, acc.org_expense_account), ccga.expense_account)) account,
785 Decode( /* Derive Scenarios */
786 Decode(l_primary_cost_method, 1,
787 Decode(l_wms_enabled_flag, 'N',
788 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
789 1,
790 2), 2),
791 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
792 3,
793 4), 4)),
794 Decode(l_wms_enabled_flag, 'N',
795 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
796 5,
797 6), 6),
798 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
799 7,
800 8), 8))),
801 1,
802 6,
803 2,
804 2,
805 3,
809 5,
806 6,
807 4,
808 2,
810 38,
811 6,
812 2,
813 7,
814 38,
815 8,
816 2) account_line_type_id,
817 Decode( /* Derive Scenarios */
818 Decode(l_primary_cost_method, 1,
819 Decode(l_wms_enabled_flag, 'N',
820 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
821 1,
822 2), 2),
823 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
824 3,
825 4), 4)),
826 Decode(l_wms_enabled_flag, 'N',
827 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
828 5,
829 6), 6),
830 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
831 7,
832 8), 8))),
833 1,
834 'Purchase Price Variance',
835 2,
836 'Expense',
837 3,
838 'Purchase Price Variance',
839 4,
840 'Expense',
841 5,
842 'Landed Cost Absorption',
843 6,
844 'Expense',
845 7,
846 'Landed Cost Absorption',
847 8,
848 'Expense') account_line_type,
849 Decode(sign(acc.new_landed_cost-acc.prior_landed_cost), 1, 1, -1) debit_credit_flag,
850 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
851 0,
852 1), 1) exp_flag
853 FROM mtl_material_transactions mmt,
854 cst_lc_rcv_txn_gt rt,
855 cst_cost_group_accounts ccga,
856 cst_lc_adj_acctg_info_gt acc,
857 mtl_secondary_inventories mse
858 WHERE mmt.transaction_source_type_id = 1
859 AND mmt.organization_id = p_organization_id
860 AND mmt.rcv_transaction_id = rt.rcv_transaction_id
861 AND mse.organization_id = p_organization_id
862 AND mse.secondary_inventory_name = rt.subinventory_code
863 AND rt.transaction_type IN ('DELIVER', 'RETURN TO RECEIVING', 'CORRECT')
864 AND rt.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
865 AND ccga.cost_group_id (+) = mmt.cost_group_id
866 AND rt.lcm_transaction_id = acc.lcm_transaction_id
867 GROUP BY mmt.cost_group_id,
868 rt.subinventory_code,
869 rt.lcm_transaction_id,
870 rt.rcv_transaction_id,
871 acc.inventory_asset_flag,
872 mse.asset_inventory,
873 acc.purchase_price_var_account,
874 mmt.cost_group_id,
875 ccga.purchase_price_var_account,
876 mse.expense_account,
877 acc.org_expense_account,
878 ccga.expense_account,
879 acc.lcm_account,
880 acc.new_landed_cost,
881 acc.prior_landed_cost;
882
883 IF (l_sLog AND l_pLog) THEN
884 l_num_records := SQL%ROWCOUNT;
885 FND_LOG.STRING(
886 FND_LOG.LEVEL_PROCEDURE,
887 l_module || '.query(' || l_stmt_num || ')',
888 l_num_records || ' Rows updated'
889 );
890
891 END IF;
892
893 l_stmt_num := 80;
894 IF l_primary_cost_method = 1 THEN
895
896 l_stmt_num := 90;
897 FOR c_account IN c_adj_offset_account LOOP
898
899 l_stmt_num := 100;
900 l_acct_id := CSTPSCHK.std_get_account_id( p_organization_id, c_account.transaction_id,
901 -1 * c_account.debit_credit_flag, c_account.accounting_line_type_id, 1,
902 0, c_account.subinventory_code, c_account.cost_group_id, c_account.exp_flag, 0,
903 l_err_num, l_error_code, l_error_msg);
904
905 IF NVL(l_err_num, 0) <> 0 THEN
906
907 l_error_msg := 'Retrieval of ' || c_account.accounting_line_type || ' account' ||
908 ' from client extension errored out with: ' || '(' || substr(l_error_code, 10) || ') - ' || substr(Nvl(l_error_msg, 'Unknown Error'), 100);
909
910 l_stmt_num := 110;
911 INSERT INTO cst_lc_adj_interface_errors
912 (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
913 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
914 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
915 VALUES (p_group_id, c_account.lcm_transaction_id, 'ACCOUNT', l_error_msg,
916 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id,
917 SYSDATE, SYSDATE, fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id);
918
919 IF (l_sLog AND l_pLog) THEN
920 l_num_records := SQL%ROWCOUNT;
921 FND_LOG.STRING(
922 FND_LOG.LEVEL_PROCEDURE,
923 l_module || '.query(' || l_stmt_num || ')',
924 l_num_records || ' Rows inserted'
925 );
926
927 END IF;
928
929 END IF;
930
931 l_stmt_num := 120;
932 IF l_acct_id <> -1 THEN
933
934 l_stmt_num := 130;
935 UPDATE cst_lc_accounts_gt
936 SET account = l_acct_id
937 WHERE rowid = c_account.rowid;
938
939 END IF;
940
941 END LOOP;
942
943 l_stmt_num := 140;
944 UPDATE cst_lc_adj_interface i
945 SET process_status = 3,
946 group_id = NULL
947 WHERE group_id = p_group_id
948 AND process_status IN (1,2)
952 WHERE e.transaction_id = i.transaction_id
949 AND organization_id = p_organization_id
950 AND EXISTS (SELECT 1
951 FROM cst_lc_adj_interface_errors e
953 AND e.group_id = p_group_id
954 AND error_column = 'ACCOUNT');
955
956 IF (l_sLog AND l_pLog) THEN
957 l_num_records := SQL%ROWCOUNT;
958 FND_LOG.STRING(
959 FND_LOG.LEVEL_PROCEDURE,
960 l_module || '.query(' || l_stmt_num || ')',
961 l_num_records || ' Rows updated'
962 );
963
964 END IF;
965
966 l_stmt_num := 150;
967 DELETE FROM cst_lc_accounts_gt t
968 WHERE EXISTS (SELECT 1
969 FROM cst_lc_adj_interface_errors e
970 WHERE e.transaction_id = t.lcm_transaction_id
971 AND e.group_id = p_group_id
972 AND error_column = 'ACCOUNT');
973
974 IF (l_sLog AND l_pLog) THEN
975 l_num_records := SQL%ROWCOUNT;
976 FND_LOG.STRING(
977 FND_LOG.LEVEL_PROCEDURE,
978 l_module || '.query(' || l_stmt_num || ')',
979 l_num_records || ' Rows deleted'
980 );
981
982 END IF;
983
984 l_stmt_num := 160;
985 DELETE FROM cst_lc_rcv_txn_gt t
986 WHERE EXISTS (SELECT 1
987 FROM cst_lc_adj_interface_errors e
988 WHERE e.transaction_id = t.lcm_transaction_id
989 AND e.group_id = p_group_id
990 AND error_column = 'ACCOUNT');
991
992 IF (l_sLog AND l_pLog) THEN
993 l_num_records := SQL%ROWCOUNT;
994 FND_LOG.STRING(
995 FND_LOG.LEVEL_PROCEDURE,
996 l_module || '.query(' || l_stmt_num || ')',
997 l_num_records || ' Rows deleted'
998 );
999
1000 END IF;
1001
1002 l_stmt_num := 170;
1003 DELETE FROM cst_lc_adj_acctg_info_gt t
1004 WHERE EXISTS (SELECT 1
1005 FROM cst_lc_adj_interface_errors e
1006 WHERE e.transaction_id = t.lcm_transaction_id
1007 AND e.group_id = p_group_id
1008 AND error_column = 'ACCOUNT');
1009
1010 IF (l_sLog AND l_pLog) THEN
1011 l_num_records := SQL%ROWCOUNT;
1012 FND_LOG.STRING(
1013 FND_LOG.LEVEL_PROCEDURE,
1014 l_module || '.query(' || l_stmt_num || ')',
1015 l_num_records || ' Rows deleted'
1016 );
1017
1018 END IF;
1019
1020 END IF; -- l_primary_cost_method = 1
1021
1022 l_stmt_num := 180;
1023
1024 IF (l_pLog) THEN
1025 FND_LOG.STRING(
1026 FND_LOG.LEVEL_PROCEDURE,
1027 l_module || '.end',
1028 '<< ' || l_api_name || ': Out Parameters:' ||
1029 ' x_ledger_id ' || x_ledger_id ||
1030 ', x_primary_cost_method ' || x_primary_cost_method ||
1031 ', x_wms_enabled_flag ' || x_wms_enabled_flag
1032 );
1033 END IF;
1034
1035 EXCEPTION
1036
1037 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1038 x_return_status := FND_API.g_ret_sts_error;
1039 IF (l_ulog) THEN
1040 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
1041 END IF;
1042
1043 WHEN FND_API.G_EXC_ERROR THEN
1044 x_return_status := FND_API.g_ret_sts_error;
1045 IF (l_exceptionLog) THEN
1046 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,l_module, TRUE);
1047 END IF;
1048
1049 WHEN OTHERS THEN
1050 x_return_status := FND_API.g_ret_sts_error;
1051 IF (l_uLog) THEN
1052 FND_MESSAGE.set_name('BOM', 'CST_UNEXP_ERROR');
1053 FND_MESSAGE.set_token('PACKAGE', G_PKG_NAME);
1054 FND_MESSAGE.set_token('PROCEDURE',l_api_name);
1055 FND_MESSAGE.set_token('STATEMENT',to_char(l_stmt_num));
1056 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
1057 FND_MSG_PUB.ADD;
1058
1059 FND_MESSAGE.SET_NAME('BOM','CST_LOG_UNEXPECTED');
1060 FND_MESSAGE.SET_TOKEN('SQLERRM',SQLERRM);
1061 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
1062 FND_MSG_PUB.ADD;
1063
1064 END IF;
1065
1066 END Populate_Lcm_Adjustment_Info;
1067
1068 /*===========================================================================+
1069 | |
1070 | PROCEDURE : Populate_Temp_Adjustment_Data |
1071 | |
1072 | DESCRIPTION : This procedure creates the receiveing events and average |
1073 | and layer cost update data in global temporary tables. |
1074 | |
1075 | |
1076 | |
1077 | |
1078 | |
1079 | CALLED FROM : Process_LcmAdjustments Procedure |
1080 | |
1081 | Parameters : |
1082 | IN : p_primary_cost_method IN NUMBER REQUIRED |
1083 | p_wms_enabled_flag IN NUMBER REQUIRED |
1084 | p_api_version IN NUMBER REQUIRED |
1085 | p_init_msg_list IN VARCHAR2 REQUIRED |
1086 | p_validation_level IN NUMBER REQUIRED |
1090 | NOTES : None |
1087 | |
1088 | OUT : x_return_status OUT NOCOPY NUMBER |
1089 | |
1091 | |
1092 | |
1093 +===========================================================================*/
1094
1095 PROCEDURE Populate_Temp_Adjustment_Data
1096 (
1097 p_api_version IN NUMBER,
1098 p_init_msg_list IN VARCHAR2,
1099 p_validation_level IN NUMBER,
1100 p_primary_cost_method IN NUMBER,
1101 p_wms_enabled_flag IN VARCHAR2,
1102 x_return_status OUT NOCOPY VARCHAR2
1103 )
1104
1105 IS
1106 l_api_name CONSTANT VARCHAR2(30) :='Populate_Temp_Adjustment_Data';
1107 l_api_version CONSTANT NUMBER := 1.0;
1108 l_return_status VARCHAR2(1);
1109 l_module CONSTANT VARCHAR2(100) := 'cst.plsql.'|| G_PKG_NAME || '.' || l_api_name;
1110
1111 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
1112 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1113 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1114 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1115 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1116 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1117
1118 l_stmt_num NUMBER;
1119 l_num_records NUMBER;
1120 l_msg_data VARCHAR2(240);
1121
1122 BEGIN
1123
1124 l_stmt_num := 0;
1125
1126 /* Procedure level log message for Entry point */
1127 IF (l_pLog) THEN
1128 FND_LOG.STRING(
1129 FND_LOG.LEVEL_PROCEDURE,
1130 l_module || '.begin',
1131 '>> ' || l_api_name || ': Parameters:' ||
1132 ' Api version ' || p_api_version ||
1133 ', Init msg list ' || p_init_msg_list ||
1134 ', Validation level ' || p_validation_level ||
1135 ', Primary cost method ' || p_primary_cost_method ||
1136 ', Wms enabled flag ' || p_wms_enabled_flag
1137 );
1138 END IF;
1139
1140 /* Standard call to check for call compatibility */
1141 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1142 p_api_version,
1143 l_api_name,
1144 G_PKG_NAME )
1145 THEN
1146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1147 END IF;
1148
1149 /* Initialize message list if p_init_msg_list is set to TRUE */
1150 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1151 FND_MSG_PUB.initialize;
1152 END IF;
1153
1154 /* Initialize API return status to success */
1155 x_return_status := FND_API.G_RET_STS_SUCCESS;
1156 l_return_status := FND_API.G_RET_STS_SUCCESS;
1157
1158 l_stmt_num := 10;
1159
1160 /* Create the Receipt Adjustment Events - Event type id = 15 */
1161 INSERT INTO CST_LC_RAE_EVENTS_GT
1162 (LCM_TRANSACTION_ID,
1163 ACCOUNTING_EVENT_ID,
1164 EVENT_SOURCE,
1165 EVENT_SOURCE_ID,
1166 EVENT_TYPE_ID,
1167 RCV_TRANSACTION_ID,
1168 INVENTORY_ITEM_ID,
1169 PRIOR_UNIT_PRICE,
1170 UNIT_PRICE,
1171 transaction_date,
1172 organization_id,
1173 ORG_ID,
1174 SET_OF_BOOKS_ID,
1175 PO_HEADER_ID,
1176 PO_RELEASE_ID,
1177 PO_LINE_ID,
1178 PO_LINE_LOCATION_ID,
1179 PO_DISTRIBUTION_ID,
1180 CURRENCY_CODE,
1181 CURRENCY_CONVERSION_TYPE,
1182 CURRENCY_CONVERSION_RATE,
1183 CURRENCY_CONVERSION_DATE,
1184 SOURCE_DOC_UNIT_OF_MEASURE,
1185 TRANSACTION_UNIT_OF_MEASURE,
1186 PRIMARY_UNIT_OF_MEASURE,
1187 source_doc_quantity,
1188 TRANSACTION_quantity,
1189 primary_quantity,
1190 CREDIT_ACCOUNT_ID,
1191 DEBIT_ACCOUNT_ID,
1192 CREDIT_ACCOUNTING_LINE_TYPE,
1193 DEBIT_ACCOUNTING_LINE_TYPE,
1194 /* who columns */
1195 CREATED_BY,
1196 CREATION_DATE,
1197 PROGRAM_APPLICATION_ID,
1198 PROGRAM_ID,
1199 PROGRAM_UPDATE_DATE,
1200 LAST_UPDATE_DATE,
1201 LAST_UPDATE_LOGIN,
1202 LAST_UPDATED_BY,
1203 REQUEST_ID,
1204 ACCOUNTED_FLAG)
1205 SELECT acc.lcm_transaction_id,
1206 NULL,
1207 'LC_ADJUSTMENTS',
1208 acc.lcm_transaction_id,
1209 15,
1210 acc.rcv_transaction_id,
1211 acc.inventory_item_id,
1212 acc.prior_landed_cost,
1213 acc.new_landed_cost,
1214 acc.transaction_date,
1215 acc.organization_id,
1216 acc.operating_unit,
1217 acc.ledger_id,
1218 acc.po_header_id,
1219 acc.po_release_id,
1220 acc.po_line_id,
1221 acc.po_line_location_id,
1222 rt.po_distribution_id,
1223 acc.func_currency_code,
1224 NULL,
1225 1,
1226 acc.transaction_date,
1227 acc.source_doc_unit_of_measure,
1228 acc.primary_unit_of_measure,
1229 acc.primary_unit_of_measure,
1230 sum(Decode(rt.transaction_type,
1231 'RECEIVE', 1,
1232 'MATCH', 1,
1233 'RETURN TO VENDOR', -1,
1234 'CORRECT', Decode(rt.parent_transaction_type,
1235 'RECEIVE', 1,
1236 'MATCH', 1,
1240 'RECEIVE', 1,
1237 'RETURN TO VENDOR', -1,
1238 0)) * rt.source_doc_quantity) source_doc_quantity,
1239 sum(Decode(rt.transaction_type,
1241 'MATCH', 1,
1242 'RETURN TO VENDOR', -1,
1243 'CORRECT', Decode(rt.parent_transaction_type,
1244 'RECEIVE', 1,
1245 'MATCH', 1,
1246 'RETURN TO VENDOR', -1,
1247 0)) * rt.primary_quantity) transaction_quantity,
1248 sum(Decode(rt.transaction_type,
1249 'RECEIVE', 1,
1250 'MATCH', 1,
1251 'RETURN TO VENDOR', -1,
1252 'CORRECT', Decode(rt.parent_transaction_type,
1253 'RECEIVE', 1,
1254 'MATCH', 1,
1255 'RETURN TO VENDOR', -1,
1256 0)) * rt.primary_quantity) primary_quantity,
1257 Decode(SIGN(acc.new_landed_cost-acc.prior_landed_cost), -1, acc.receiving_account, acc.lcm_account) CREDIT_ACCOUNT_ID,
1258 Decode(SIGN(acc.new_landed_cost-acc.prior_landed_cost), 1, acc.receiving_account, acc.lcm_account) DEBIT_ACCOUNT_ID,
1259 Decode(SIGN(acc.new_landed_cost-acc.prior_landed_cost), -1, 'Receiving Inspection', 'Landed Cost Absorption') CREDIT_ACCOUNTING_LINE_TYPE,
1260 Decode(SIGN(acc.new_landed_cost-acc.prior_landed_cost), 1, 'Receiving Inspection', 'Landed Cost Absorption') DEBIT_ACCOUNTING_LINE_TYPE,
1261 fnd_global.user_id,
1262 SYSDATE,
1263 fnd_global.prog_appl_id,
1264 fnd_global.conc_program_id,
1265 SYSDATE,
1266 SYSDATE,
1267 fnd_global.login_id,
1268 fnd_global.user_id,
1269 fnd_global.conc_request_id,
1270 'N'
1271 FROM CST_LC_ADJ_ACCTG_INFO_GT ACC,
1272 CST_LC_RCV_TXN_GT RT
1273 WHERE acc.lcm_transaction_id = RT.lcm_transaction_id
1274 AND (rt.transaction_type IN ('RECEIVE', 'MATCH', 'RETURN TO VENDOR')
1275 OR (rt.transaction_type = 'CORRECT'
1276 AND rt.parent_transaction_type IN ('RECEIVE', 'MATCH', 'RETURN TO VENDOR')))
1277 GROUP BY acc.lcm_transaction_id,
1278 ACC.rcv_transaction_id,
1279 inventory_item_id,
1280 prior_landed_cost,
1281 new_landed_cost,
1282 transaction_date,
1283 organization_id,
1284 operating_unit,
1285 ledger_id,
1286 po_header_id,
1287 po_release_id,
1288 po_line_id,
1289 po_line_location_id,
1290 po_distribution_id,
1291 func_currency_code,
1292 source_doc_unit_of_measure,
1293 primary_unit_of_measure,
1294 receiving_account,
1295 LCM_account ;
1296
1297 IF (l_sLog AND l_pLog) THEN
1298 l_num_records := SQL%ROWCOUNT;
1299 FND_LOG.STRING(
1300 FND_LOG.LEVEL_PROCEDURE,
1301 l_module || '.query(' || l_stmt_num || ')',
1302 l_num_records || ' Rows inserted'
1303 );
1304
1305 END IF;
1306
1307 l_stmt_num := 20;
1308
1309 /* Create the Delivery Adjustment Events - Event type id = 16,17 */
1310 l_stmt_num := 30;
1311 INSERT INTO CST_LC_RAE_EVENTS_GT
1312 (LCM_TRANSACTION_ID,
1313 ACCOUNTING_EVENT_ID,
1314 EVENT_SOURCE,
1315 EVENT_SOURCE_ID,
1316 EVENT_TYPE_ID,
1317 RCV_TRANSACTION_ID,
1318 INVENTORY_ITEM_ID,
1319 PRIOR_UNIT_PRICE,
1320 UNIT_PRICE,
1321 transaction_date,
1322 organization_id,
1323 ORG_ID,
1324 SET_OF_BOOKS_ID,
1325 PO_HEADER_ID,
1326 PO_RELEASE_ID,
1327 PO_LINE_ID,
1328 PO_LINE_LOCATION_ID,
1329 PO_DISTRIBUTION_ID,
1330 CURRENCY_CODE,
1331 CURRENCY_CONVERSION_TYPE,
1332 CURRENCY_CONVERSION_RATE,
1333 CURRENCY_CONVERSION_DATE,
1334 SOURCE_DOC_UNIT_OF_MEASURE,
1335 TRANSACTION_UNIT_OF_MEASURE,
1336 PRIMARY_UNIT_OF_MEASURE,
1337 source_doc_quantity,
1338 TRANSACTION_quantity,
1339 primary_quantity,
1340 CREDIT_ACCOUNT_ID,
1341 DEBIT_ACCOUNT_ID,
1342 CREDIT_ACCOUNTING_LINE_TYPE,
1343 DEBIT_ACCOUNTING_LINE_TYPE,
1344 /* who columns */
1345 CREATED_BY,
1346 CREATION_DATE,
1347 PROGRAM_APPLICATION_ID,
1348 PROGRAM_ID,
1349 PROGRAM_UPDATE_DATE,
1350 LAST_UPDATE_DATE,
1351 LAST_UPDATE_LOGIN,
1352 LAST_UPDATED_BY,
1353 REQUEST_ID,
1354 ACCOUNTED_FLAG)
1355 SELECT acc.lcm_transaction_id,
1356 NULL,
1357 'LC_ADJUSTMENTS',
1358 acc.lcm_transaction_id,
1359 Decode(adj_acc.exp_flag,
1360 1, 17,
1361 16),
1362 acc.rcv_transaction_id,
1363 acc.inventory_item_id,
1364 acc.prior_landed_cost,
1365 acc.new_landed_cost,
1366 acc.transaction_date,
1367 acc.organization_id,
1368 acc.operating_unit,
1369 acc.ledger_id,
1370 acc.PO_HEADER_ID,
1371 acc.PO_RELEASE_ID,
1372 acc.PO_LINE_ID,
1373 acc.PO_LINE_LOCATION_ID,
1374 rt.PO_DISTRIBUTION_ID,
1375 acc.func_currency_code,
1376 NULL,
1377 1,
1378 acc.transaction_date,
1379 acc.SOURCE_DOC_UNIT_OF_MEASURE,
1380 acc.PRIMARY_UNIT_OF_MEASURE,
1381 acc.PRIMARY_UNIT_OF_MEASURE,
1382 sum(Decode(transaction_type,
1383 'DELIVER', 1,
1384 'RETURN TO RECEIVING', -1,
1385 'CORRECT', Decode(parent_transaction_type,
1386 'DELIVER', 1,
1387 'RETURN TO RECEIVING', -1,
1388 0)) * source_doc_quantity) source_doc_quantity,
1389 sum(Decode(transaction_type,
1390 'DELIVER', 1,
1391 'RETURN TO RECEIVING', -1,
1392 'CORRECT', Decode(parent_transaction_type,
1393 'DELIVER', 1,
1394 'RETURN TO RECEIVING', -1,
1395 0)) * primary_quantity) transaction_quantity,
1396 sum(Decode(transaction_type,
1397 'DELIVER', 1,
1398 'RETURN TO RECEIVING', -1,
1402 0)) * primary_quantity) primary_quantity,
1399 'CORRECT', Decode(parent_transaction_type,
1400 'DELIVER', 1,
1401 'RETURN TO RECEIVING', -1,
1403 Decode(adj_acc.debit_credit_flag, 1, acc.receiving_account, adj_acc.account) CREDIT_ACCOUNT_ID,
1404 Decode(adj_acc.debit_credit_flag, -1, acc.receiving_account, adj_acc.account) DEBIT_ACCOUNT_ID,
1405 Decode(adj_acc.debit_credit_flag, 1, 'Receiving Inspection', adj_acc.accounting_line_type) CREDIT_ACCOUNTING_LINE_TYPE,
1406 Decode(adj_acc.debit_credit_flag, -1, 'Receiving Inspection', adj_acc.accounting_line_type) DEBIT_ACCOUNTING_LINE_TYPE,
1407 fnd_global.user_id,
1408 SYSDATE,
1409 fnd_global.prog_appl_id,
1410 fnd_global.conc_program_id,
1411 SYSDATE,
1412 SYSDATE,
1413 fnd_global.login_id,
1414 fnd_global.user_id,
1415 fnd_global.conc_request_id,
1416 'N'
1417 FROM cst_lc_adj_acctg_info_gt acc,
1418 cst_lc_rcv_txn_gt rt,
1419 cst_lc_accounts_gt adj_acc
1420 WHERE (rt.transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
1421 or (rt.transaction_type = 'CORRECT'
1422 and rt.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')))
1423 and rt.lcm_transaction_id = acc.lcm_transaction_id
1424 and adj_acc.lcm_transaction_id = acc.lcm_transaction_id
1425 GROUP BY acc.lcm_transaction_id,
1426 acc.rcv_transaction_id,
1427 inventory_item_id,
1428 prior_landed_cost,
1429 new_landed_cost,
1430 transaction_date,
1431 acc.organization_id,
1432 operating_unit,
1433 ledger_id,
1434 po_header_id,
1435 po_release_id,
1436 po_line_id,
1437 po_line_location_id,
1438 po_distribution_id,
1439 func_currency_code,
1440 source_doc_unit_of_measure,
1441 primary_unit_of_measure,
1442 receiving_account,
1443 adj_acc.account,
1444 adj_acc.exp_flag,
1445 adj_acc.debit_credit_flag,
1446 adj_acc.accounting_line_type;
1447
1448 IF (l_sLog AND l_pLog) THEN
1449 l_num_records := SQL%ROWCOUNT;
1450 FND_LOG.STRING(
1451 FND_LOG.LEVEL_PROCEDURE,
1452 l_module || '.query(' || l_stmt_num || ')',
1453 l_num_records || ' Rows inserted'
1454 );
1455
1456 END IF;
1457
1458 l_stmt_num := 50;
1459 UPDATE CST_LC_RAE_EVENTS_GT
1460 SET accounting_event_id = rcv_accounting_event_s.NEXTVAL;
1461
1462 l_stmt_num := 60;
1463 IF p_primary_cost_method = 2 THEN
1464 l_stmt_num := 70;
1465
1466 /* Insert Average cost update transactions for the deliveries of
1467 asset items into asset organizations */
1468 INSERT INTO CST_LC_MMT_GT
1469 (lcm_transaction_id,
1470 transaction_id,
1471 transaction_type_id,
1472 transaction_action_id,
1473 transaction_source_type_id,
1474 cost_group_id,
1475 primary_quantity,
1476 value_change,
1477 last_update_date,
1478 last_updated_by,
1479 creation_date,
1480 created_by,
1481 last_update_login,
1482 request_id,
1483 program_application_id,
1484 program_id,
1485 program_update_date,
1486 inventory_item_id,
1487 organization_id,
1488 transaction_date,
1489 acct_period_id,
1490 transaction_source_name,
1491 source_code,
1492 transaction_reference,
1493 trx_source_line_id,
1494 material_account,
1495 material_overhead_account,
1496 resource_account,
1497 outside_processing_account,
1498 overhead_account,
1499 expense_account_id,
1500 costed_flag,
1501 pm_cost_collected,
1502 owning_organization_id,
1503 owning_tp_type,
1504 transaction_uom)
1505 SELECT
1506 acc.lcm_transaction_id,
1507 NULL,
1508 80,
1509 24,
1510 13,
1511 mmt.cost_group_id,
1512 sum(mmt.primary_quantity),
1513 sum(mmt.primary_quantity) * (acc.new_landed_cost - acc.prior_landed_cost),
1514 SYSDATE,
1515 fnd_global.user_id,
1516 SYSDATE,
1517 fnd_global.user_id,
1518 fnd_global.login_id,
1519 fnd_global.conc_request_id,
1520 fnd_global.prog_appl_id,
1521 fnd_global.conc_program_id,
1522 SYSDATE,
1523 acc.inventory_item_id,
1524 acc.organization_id,
1525 acc.transaction_date,
1526 acc.acct_period_id,
1527 'LCM ADJUSTMENT',
1528 'LCMADJ',
1529 acc.lcm_transaction_id,
1530 acc.rcv_transaction_id,
1531 acc.lcm_account,
1532 acc.lcm_account,
1533 acc.lcm_account,
1534 acc.lcm_account,
1535 acc.lcm_account,
1536 acc.lcm_var_account,
1537 'N',
1538 NULL,
1539 acc.organization_id,
1540 2,
1541 msi.primary_uom_code
1542 FROM cst_lc_adj_acctg_info_gt acc,
1543 cst_lc_rcv_txn_gt rt,
1544 mtl_secondary_inventories mse,
1545 mtl_material_transactions mmt,
1546 mtl_system_items_b msi
1547 WHERE (rt.transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
1548 or (rt.transaction_type = 'CORRECT'
1549 and rt.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')))
1550 and rt.lcm_transaction_id = acc.lcm_transaction_id
1551 AND mse.secondary_inventory_name = rt.subinventory_code
1552 and mse.organization_id = acc.organization_id
1553 and mmt.rcv_transaction_id = rt.rcv_transaction_id
1554 AND (acc.inventory_asset_flag = 'Y' AND Nvl(mse.asset_inventory,2) = 1)
1555 AND msi.inventory_item_id = acc.inventory_item_id
1559 acc.inventory_item_id,
1556 AND msi.organization_id= acc.organization_id
1557 GROUP BY
1558 mmt.cost_group_id,
1560 acc.organization_id,
1561 acc.transaction_date,
1562 acc.acct_period_id,
1563 acc.rcv_transaction_id,
1564 acc.lcm_account,
1565 acc.lcm_transaction_id,
1566 acc.lcm_var_account,
1567 acc.new_landed_cost,
1568 acc.prior_landed_cost,
1569 msi.primary_uom_code;
1570
1571 IF (l_sLog AND l_pLog) THEN
1572 l_num_records := SQL%ROWCOUNT;
1573 FND_LOG.STRING(
1574 FND_LOG.LEVEL_PROCEDURE,
1575 l_module || '.query(' || l_stmt_num || ')',
1576 l_num_records || ' Rows inserted'
1577 );
1578
1579 END IF;
1580
1581 l_stmt_num := 75;
1582 UPDATE CST_LC_MMT_GT
1583 SET transaction_id = MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL;
1584
1585 IF (l_sLog AND l_pLog) THEN
1586 l_num_records := SQL%ROWCOUNT;
1587 FND_LOG.STRING(
1588 FND_LOG.LEVEL_PROCEDURE,
1589 l_module || '.query(' || l_stmt_num || ')',
1590 l_num_records || ' Rows updated'
1591 );
1592
1593 END IF;
1594
1595 END IF; --p_primary_cost_method = 2 THEN
1596
1597 l_stmt_num := 80;
1598 IF p_primary_cost_method IN (5,6) THEN
1599
1600 l_stmt_num := 90;
1601 /* Insert Layer cost update transactions for the deliveries of
1602 asset items into asset organizations */
1603 INSERT INTO CST_LC_MMT_GT
1604 (lcm_transaction_id,
1605 transaction_id,
1606 transaction_type_id,
1607 transaction_action_id,
1608 transaction_source_type_id,
1609 transaction_source_id,
1610 cost_group_id,
1611 primary_quantity,
1612 value_change,
1613 last_update_date,
1614 last_updated_by,
1615 creation_date,
1616 created_by,
1617 last_update_login,
1618 request_id,
1619 program_application_id,
1620 program_id,
1621 program_update_date,
1622 inventory_item_id,
1623 organization_id,
1624 transaction_date,
1625 acct_period_id,
1626 transaction_source_name,
1627 source_code,
1628 transaction_reference,
1629 trx_source_line_id,
1630 material_account,
1631 material_overhead_account,
1632 resource_account,
1633 outside_processing_account,
1634 overhead_account,
1635 expense_account_id,
1636 costed_flag,
1637 pm_cost_collected,
1638 owning_organization_id,
1639 owning_tp_type,
1640 transaction_uom)
1641 SELECT txn.lcm_transaction_id,
1642 NULL,
1643 28,
1644 24,
1645 15,
1646 inv_layer_id,
1647 txn.cost_group_id,
1648 sum(txn.layer_quantity),
1649 txn.value_change,
1650 SYSDATE,
1651 fnd_global.user_id,
1652 SYSDATE,
1653 fnd_global.user_id,
1654 fnd_global.login_id,
1655 fnd_global.conc_request_id,
1656 fnd_global.prog_appl_id,
1657 fnd_global.conc_program_id,
1658 SYSDATE,
1659 txn.inventory_item_id,
1660 txn.organization_id,
1661 txn.transaction_date,
1662 txn.acct_period_id,
1663 'LCM ADJUSTMENT',
1664 'LCMADJ',
1665 txn.lcm_transaction_id,
1666 txn.rcv_transaction_id,
1667 txn.lcm_account,
1668 txn.lcm_account,
1669 txn.lcm_account,
1670 txn.lcm_account,
1671 txn.lcm_account,
1672 txn.lcm_var_account,
1673 'N',
1674 NULL,
1675 txn.organization_id,
1676 2,
1677 msi.primary_uom_code
1678 FROM (SELECT DISTINCT acc.lcm_transaction_id,
1679 mmt.transaction_id,
1680 mclacd.inv_layer_id,
1681 mclacd.layer_quantity,
1682 mmt.cost_group_id,
1683 acc.inventory_item_id,
1684 acc.organization_id,
1685 acc.transaction_date,
1686 acc.acct_period_id,
1687 acc.rcv_transaction_id,
1688 acc.lcm_account,
1689 acc.lcm_var_account,
1690 (layer_quantity * (acc.new_landed_cost - acc.prior_landed_cost)) value_change
1691 FROM mtl_material_transactions mmt,
1692 mtl_cst_layer_act_cost_details mclacd,
1693 cst_lc_adj_acctg_info_gt acc,
1694 cst_lc_rcv_txn_gt rt,
1695 mtl_secondary_inventories mse
1696 WHERE (rt.transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
1697 or (rt.transaction_type = 'CORRECT'
1698 and rt.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')))
1699 and rt.lcm_transaction_id = acc.lcm_transaction_id
1700 AND mse.secondary_inventory_name = rt.subinventory_code
1701 and mse.organization_id = acc.organization_id
1702 and mmt.rcv_transaction_id = rt.rcv_transaction_id
1703 and mmt.transaction_id = mclacd.transaction_id
1704 AND (acc.inventory_asset_flag = 'Y' AND Nvl(mse.asset_inventory,2) = 1)) txn,
1705 mtl_system_items_b msi
1706 WHERE msi.inventory_item_id = txn.inventory_item_id
1707 AND msi.organization_id= txn.organization_id
1708 GROUP BY txn.lcm_transaction_id, txn.COST_GROUP_ID,
1709 txn.INV_LAYER_ID,
1710 txn.cost_group_id,
1711 txn.inventory_item_id,
1712 txn.organization_id,
1716 txn.value_change,
1713 txn.transaction_date,
1714 txn.acct_period_id,
1715 txn.rcv_transaction_id,
1717 txn.lcm_account,
1718 txn.lcm_var_account,
1719 msi.primary_uom_code;
1720
1721 IF (l_sLog AND l_pLog) THEN
1722 l_num_records := SQL%ROWCOUNT;
1723 FND_LOG.STRING(
1724 FND_LOG.LEVEL_PROCEDURE,
1725 l_module || '.query(' || l_stmt_num || ')',
1726 l_num_records || ' Rows inserted'
1727 );
1728
1729 END IF;
1730
1731 l_stmt_num := 100;
1732 UPDATE CST_LC_MMT_GT
1733 SET transaction_id = MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL;
1734
1735 IF (l_sLog AND l_pLog) THEN
1736 l_num_records := SQL%ROWCOUNT;
1737 FND_LOG.STRING(
1738 FND_LOG.LEVEL_PROCEDURE,
1739 l_module || '.query(' || l_stmt_num || ')',
1740 l_num_records || ' Rows updated'
1741 );
1742
1743 END IF;
1744
1745 END IF; -- p_primary_cost_method IN (5,6) THEN
1746
1747
1748 l_stmt_num := 110;
1749 IF (l_pLog) THEN
1750 FND_LOG.STRING(
1751 FND_LOG.LEVEL_PROCEDURE,
1752 l_module || '.end',
1753 '<< ' || l_api_name || ': Out Parameters:' ||
1754 ', x_return_status ' || x_return_status
1755 );
1756 END IF;
1757
1758 EXCEPTION
1759
1760 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1761 x_return_status := FND_API.g_ret_sts_error;
1762 IF (l_uLog) THEN
1763 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
1764 END IF;
1765
1766 WHEN FND_API.G_EXC_ERROR THEN
1767 x_return_status := FND_API.g_ret_sts_error;
1768 IF (l_exceptionLog) THEN
1769 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,l_module, TRUE);
1770 END IF;
1771
1772 WHEN OTHERS THEN
1773 x_return_status := FND_API.g_ret_sts_error;
1774 IF (l_uLog) THEN
1775 FND_MESSAGE.set_name('BOM', 'CST_UNEXP_ERROR');
1776 FND_MESSAGE.set_token('PACKAGE', G_PKG_NAME);
1777 FND_MESSAGE.set_token('PROCEDURE',l_api_name);
1778 FND_MESSAGE.set_token('STATEMENT',to_char(l_stmt_num));
1779 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
1780 FND_MSG_PUB.ADD;
1781
1782 FND_MESSAGE.SET_NAME('BOM','CST_LOG_UNEXPECTED');
1783 FND_MESSAGE.SET_TOKEN('SQLERRM',SQLERRM);
1784 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
1785 FND_MSG_PUB.ADD;
1786
1787 END IF;
1788
1789 END Populate_Temp_Adjustment_Data;
1790
1791 /*===========================================================================+
1792 | |
1793 | PROCEDURE : Insert_Adjustment_Data |
1794 | |
1795 | DESCRIPTION : This procedure inserts the receiving events data and the |
1796 | average and layer cost update data from temporary tables |
1797 | into RCV_ACCOUNTING_EVENTS and MTL_MATERIAL_TRANSACTIONS. |
1798 | Accounting and other entries are also calculated and |
1799 | inserted into RCV_RECEIVING_SUBLEDGER, XLA_EVENTS_INT_GT |
1800 | and MTL_CST_TXN_COST_DETAILS. |
1801 | |
1802 | CALLED FROM : Process_LcmAdjustments Procedure |
1803 | |
1804 | Parameters : |
1805 | IN : p_group_id IN NUMBER REQUIRED |
1806 | p_organization_id IN NUMBER REQUIRED |
1807 | p_ledger_id IN NUMBER REQUIRED |
1808 | p_api_version IN NUMBER REQUIRED |
1809 | p_init_msg_list IN VARCHAR2 REQUIRED |
1810 | p_validation_level IN NUMBER REQUIRED |
1811 | |
1812 | OUT : x_return_status OUT NOCOPY NUMBER |
1813 | |
1814 | NOTES : None |
1815 | |
1816 | |
1817 +===========================================================================*/
1818
1819 PROCEDURE Insert_Adjustment_Data
1820 (
1821 p_api_version IN NUMBER,
1822 p_init_msg_list IN VARCHAR2,
1823 p_validation_level IN NUMBER,
1824 p_group_id IN NUMBER,
1825 p_organization_id IN NUMBER,
1826 p_ledger_id IN NUMBER,
1827 x_return_status OUT NOCOPY VARCHAR2
1828 )
1829
1830 IS
1831 l_api_name CONSTANT VARCHAR2(30) :='Insert_Adjustment_Data';
1832 l_api_version CONSTANT NUMBER := 1.0;
1833 l_return_status VARCHAR2(1);
1834 l_module CONSTANT VARCHAR2(100) := 'cst.plsql.'|| G_PKG_NAME || '.' || l_api_name;
1835
1836 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
1837 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1838 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1842
1839 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1840 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1841 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1843 l_stmt_num NUMBER;
1844 l_num_records NUMBER;
1845 l_msg_data VARCHAR2(240);
1846
1847
1848 BEGIN
1849
1850 l_stmt_num := 0;
1851
1852 /* Procedure level log message for Entry point */
1853 IF (l_pLog) THEN
1854 FND_LOG.STRING(
1855 FND_LOG.LEVEL_PROCEDURE,
1856 l_module || '.begin',
1857 '>> ' || l_api_name || ': Parameters:' ||
1858 ' Api version ' || p_api_version ||
1859 ', Init msg list ' || p_init_msg_list ||
1860 ', Validation level ' || p_validation_level ||
1861 ', Group id ' || p_group_id ||
1862 ', Ledger id ' || p_ledger_id
1863 );
1864 END IF;
1865
1866 /* Standard call to check for call compatibility */
1867 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1868 p_api_version,
1869 l_api_name,
1870 G_PKG_NAME )
1871 THEN
1872 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1873 END IF;
1874
1875 /* Initialize message list if p_init_msg_list is set to TRUE */
1876 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1877 FND_MSG_PUB.initialize;
1878 END IF;
1879
1880 /* Initialize API return status to success */
1881 x_return_status := FND_API.G_RET_STS_SUCCESS;
1882 l_return_status := FND_API.G_RET_STS_SUCCESS;
1883
1884 l_stmt_num := 10;
1885
1886 /* Create RCV accounting events data from the GTT */
1887 INSERT INTO rcv_accounting_events
1888 (accounting_event_id,
1889 event_source,
1890 event_source_id,
1891 event_type_id,
1892 rcv_transaction_id,
1893 inventory_item_id,
1894 prior_unit_price,
1895 unit_price,
1896 transaction_date,
1897 organization_id,
1898 org_id,
1899 set_of_books_id,
1900 po_header_id,
1901 po_release_id,
1902 po_line_id,
1903 po_line_location_id,
1904 po_distribution_id,
1905 currency_code,
1906 currency_conversion_type,
1907 currency_conversion_rate,
1908 currency_conversion_date,
1909 source_doc_unit_of_measure,
1910 transaction_unit_of_measure,
1911 primary_unit_of_measure,
1912 source_doc_quantity,
1913 transaction_quantity,
1914 primary_quantity,
1915 credit_account_id,
1916 debit_account_id,
1917 /* who columns */
1918 created_by,
1919 creation_date,
1920 program_application_id,
1921 program_id,
1922 program_udpate_date,
1923 last_update_date,
1924 last_update_login,
1925 last_updated_by,
1926 request_id,
1927 accounted_flag)
1928 SELECT accounting_event_id,
1929 event_source,
1930 event_source_id,
1931 event_type_id,
1932 rcv_transaction_id,
1933 inventory_item_id,
1934 prior_unit_price,
1935 unit_price,
1936 transaction_date,
1937 organization_id,
1938 org_id,
1939 set_of_books_id,
1940 po_header_id,
1941 po_release_id,
1942 po_line_id,
1943 po_line_location_id,
1944 po_distribution_id,
1945 currency_code,
1946 currency_conversion_type,
1947 currency_conversion_rate,
1948 currency_conversion_date,
1949 source_doc_unit_of_measure,
1950 transaction_unit_of_measure,
1951 primary_unit_of_measure,
1952 source_doc_quantity,
1953 transaction_quantity,
1954 primary_quantity,
1955 credit_account_id,
1956 debit_account_id,
1957 /* who columns */
1958 created_by,
1959 creation_date,
1960 program_application_id,
1961 program_id,
1962 program_update_date,
1963 last_update_date,
1964 last_update_login,
1965 last_updated_by,
1966 request_id,
1967 accounted_flag
1968 FROM cst_lc_rae_events_gt;
1969
1970 IF (l_sLog AND l_pLog) THEN
1971 l_num_records := SQL%ROWCOUNT;
1972 FND_LOG.STRING(
1973 FND_LOG.LEVEL_PROCEDURE,
1974 l_module || '.query(' || l_stmt_num || ')',
1975 l_num_records || ' Rows inserted'
1976 );
1977
1978 END IF;
1979
1980 l_stmt_num := 20;
1981
1982 /* Create the debit lines in the subledger for the rcv accounting events */
1983 INSERT INTO rcv_receiving_sub_ledger
1984 (created_by,
1985 creation_date,
1986 program_application_id,
1987 program_id,
1988 program_update_date,
1989 last_update_date,
1990 last_update_login,
1991 last_updated_by,
1992 request_id,
1993 rcv_sub_ledger_id,
1994 accounting_event_id,
1995 accounting_line_type,
1996 rcv_transaction_id,
1997 actual_flag,
1998 je_source_name,
1999 je_category_name,
2000 set_of_books_id,
2001 accounting_date,
2002 code_combination_id,
2003 entered_dr,
2004 accounted_dr,
2005 currency_code,
2006 user_currency_conversion_type,
2007 currency_conversion_rate,
2008 currency_conversion_date,
2009 transaction_date,
2010 period_name,
2011 chart_of_accounts_id,
2012 functional_currency_code,
2013 reference1,
2014 reference2,
2015 reference3,
2019 accounted_nr_tax,
2016 reference4,
2017 source_doc_quantity,
2018 accrual_method_flag,
2020 accounted_rec_tax,
2021 entered_nr_tax,
2022 entered_rec_tax
2023 )
2024 SELECT
2025 fnd_global.user_id,
2026 SYSDATE,
2027 fnd_global.prog_appl_id,
2028 fnd_global.conc_program_id,
2029 SYSDATE,
2030 SYSDATE,
2031 fnd_global.login_id,
2032 fnd_global.user_id,
2033 fnd_global.conc_request_id,
2034 rcv_receiving_sub_ledger_s.nextval,
2035 rae.accounting_event_id,
2036 rae.debit_accounting_line_type,
2037 rae.rcv_transaction_id,
2038 'A',
2039 'Purchasing',
2040 'Receiving',
2041 rae.set_of_books_id,
2042 Trunc(acc.accounting_date),
2043 rae.debit_account_id,
2044 /* ENTERED */
2045 Decode(func_minimum_accountable_unit
2046 , NULL, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price)), func_precision)
2047 , Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price))/ func_minimum_accountable_unit) * func_minimum_accountable_unit),
2048 /* ACCOUNTED */
2049 Decode(func_minimum_accountable_unit
2050 , NULL, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price)), func_precision)
2051 , Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price))/ func_minimum_accountable_unit) * func_minimum_accountable_unit),
2052 acc.func_currency_code,
2053 NULL,
2054 1,
2055 acc.transaction_date,
2056 acc.transaction_date,
2057 acc.period_name,
2058 acc.chart_of_accounts_id,
2059 acc.func_currency_code,
2060 'PO',
2061 rae.po_header_id,
2062 rae.po_distribution_id,
2063 acc.po_number,
2064 rae.source_doc_quantity,
2065 'O',
2066 0,
2067 0,
2068 0,
2069 0
2070 FROM cst_lc_rae_events_gt rae,
2071 cst_lc_adj_acctg_info_gt acc
2072 WHERE rae.lcm_transaction_id = acc.lcm_transaction_id;
2073
2074 IF (l_sLog AND l_pLog) THEN
2075 l_num_records := SQL%ROWCOUNT;
2076 FND_LOG.STRING(
2077 FND_LOG.LEVEL_PROCEDURE,
2078 l_module || '.query(' || l_stmt_num || ')',
2079 l_num_records || ' Rows inserted'
2080 );
2081
2082 END IF;
2083
2084 l_stmt_num := 30;
2085 /* Create the credit lines in the subledger for the rcv accounting events */
2086 INSERT INTO rcv_receiving_sub_ledger
2087 (created_by,
2088 creation_date,
2089 program_application_id,
2090 program_id,
2091 program_update_date,
2092 last_update_date,
2093 last_update_login,
2094 last_updated_by,
2095 request_id,
2096 rcv_sub_ledger_id,
2097 accounting_event_id,
2098 accounting_line_type,
2099 rcv_transaction_id,
2100 actual_flag,
2101 je_source_name,
2102 je_category_name,
2103 set_of_books_id,
2104 accounting_date,
2105 code_combination_id,
2106 accounted_cr,
2107 entered_cr,
2108 currency_code,
2109 user_currency_conversion_type,
2110 currency_conversion_rate,
2111 currency_conversion_date,
2112 transaction_date,
2113 period_name,
2114 chart_of_accounts_id,
2115 functional_currency_code,
2116 reference1,
2117 reference2,
2118 reference3,
2119 reference4,
2120 source_doc_quantity,
2121 accrual_method_flag,
2122 accounted_nr_tax,
2123 accounted_rec_tax,
2124 entered_nr_tax,
2125 entered_rec_tax)
2126 SELECT
2127 fnd_global.user_id,
2128 SYSDATE,
2129 fnd_global.prog_appl_id,
2130 fnd_global.conc_program_id,
2131 SYSDATE,
2132 SYSDATE,
2133 fnd_global.login_id,
2134 fnd_global.user_id,
2135 fnd_global.conc_request_id,
2136 rcv_receiving_sub_ledger_s.NEXTVAL,
2137 rae.accounting_event_id,
2138 rae.credit_accounting_line_type,
2139 rae.rcv_transaction_id,
2140 'A',
2141 'Purchasing',
2142 'Receiving',
2143 rae.set_of_books_id,
2144 Trunc(acc.accounting_date),
2145 rae.credit_account_id,
2146 /* ENTERED */
2147 Decode(func_minimum_accountable_unit
2148 , NULL, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price)), func_precision)
2149 , Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price))/ func_minimum_accountable_unit) * func_minimum_accountable_unit),
2150 /* ACCOUNTED */
2151 Decode(func_minimum_accountable_unit
2152 , NULL, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price)), func_precision)
2153 , Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price))/ func_minimum_accountable_unit) * func_minimum_accountable_unit),
2154 acc.func_currency_code,
2155 NULL,
2156 1,
2157 acc.transaction_date,
2158 acc.transaction_date,
2159 acc.period_name,
2160 acc.chart_of_accounts_id,
2161 acc.func_currency_code,
2162 'PO',
2163 rae.po_header_id,
2164 rae.po_distribution_id,
2165 acc.po_number,
2166 rae.source_doc_quantity,
2167 'O',
2168 0,
2169 0,
2170 0,
2171 0
2172 FROM cst_lc_rae_events_gt rae,
2173 cst_lc_adj_acctg_info_gt acc
2174 WHERE rae.lcm_transaction_id = acc.lcm_transaction_id;
2175
2176 IF (l_sLog AND l_pLog) THEN
2177 l_num_records := SQL%ROWCOUNT;
2178 FND_LOG.STRING(
2179 FND_LOG.LEVEL_PROCEDURE,
2180 l_module || '.query(' || l_stmt_num || ')',
2184 END IF;
2181 l_num_records || ' Rows inserted'
2182 );
2183
2185
2186 l_stmt_num := 40;
2187 /* Create the transaction cost details for the average /layer cost update transactions */
2188 INSERT INTO MTL_CST_TXN_COST_DETAILS
2189 (transaction_id,
2190 organization_id,
2191 inventory_item_id,
2192 cost_element_id,
2193 level_type,
2194 value_change,
2195 last_update_date,
2196 last_updated_by,
2197 creation_date,
2198 created_by,
2199 last_update_login,
2200 request_id,
2201 program_application_id,
2202 program_id,
2203 program_update_date)
2204 SELECT
2205 mmt.transaction_id,
2206 mmt.organization_id,
2207 mmt.inventory_item_id,
2208 1,
2209 1,
2210 mmt.primary_quantity * (acc.new_landed_cost - acc.prior_landed_cost),
2211 SYSDATE,
2212 fnd_global.user_id,
2213 SYSDATE,
2214 fnd_global.user_id,
2215 fnd_global.login_id,
2216 fnd_global.conc_request_id,
2217 fnd_global.prog_appl_id,
2218 fnd_global.conc_program_id,
2219 SYSDATE
2220 FROM cst_lc_mmt_gt mmt,
2221 cst_lc_adj_acctg_info_gt acc
2222 WHERE mmt.lcm_transaction_id = acc.lcm_transaction_id;
2223
2224 IF (l_sLog AND l_pLog) THEN
2225 l_num_records := SQL%ROWCOUNT;
2226 FND_LOG.STRING(
2227 FND_LOG.LEVEL_PROCEDURE,
2228 l_module || '.query(' || l_stmt_num || ')',
2229 l_num_records || ' Rows inserted'
2230 );
2231
2232 END IF;
2233
2234 l_stmt_num := 50;
2235
2236 /* Create the the average / layer cost update transactions */
2237 INSERT INTO MTL_MATERIAL_TRANSACTIONS
2238 (transaction_id,
2239 transaction_type_id,
2240 transaction_action_id,
2241 transaction_source_type_id,
2242 transaction_source_id,
2243 cost_group_id,
2244 primary_quantity,
2245 transaction_quantity,
2246 value_change,
2247 last_update_date,
2248 last_updated_by,
2249 creation_date,
2250 created_by,
2251 last_update_login,
2252 request_id,
2253 program_application_id,
2254 program_id,
2255 program_update_date,
2256 inventory_item_id,
2257 organization_id,
2258 transaction_date,
2259 acct_period_id,
2260 transaction_source_name,
2261 source_code,
2262 trx_source_line_id,
2263 transaction_reference,
2264 material_account,
2265 material_overhead_account,
2266 resource_account,
2267 outside_processing_account,
2268 overhead_account,
2269 expense_account_id,
2270 costed_flag,
2271 pm_cost_collected,
2272 owning_organization_id,
2273 owning_tp_type,
2274 transaction_uom)
2275 SELECT
2276 transaction_id,
2277 transaction_type_id,
2278 transaction_action_id,
2279 transaction_source_type_id,
2280 transaction_source_id,
2281 cost_group_id,
2282 primary_quantity,
2283 primary_quantity,
2284 value_change,
2285 last_update_date,
2286 last_updated_by,
2287 creation_date,
2288 created_by,
2289 last_update_login,
2290 request_id,
2291 program_application_id,
2292 program_id,
2293 program_update_date,
2294 inventory_item_id,
2295 organization_id,
2296 transaction_date,
2297 acct_period_id,
2298 transaction_source_name,
2299 source_code,
2300 trx_source_line_id,
2301 transaction_reference,
2302 material_account,
2303 material_overhead_account,
2304 resource_account,
2305 outside_processing_account,
2306 overhead_account,
2307 expense_account_id,
2308 costed_flag,
2309 pm_cost_collected,
2310 owning_organization_id,
2311 owning_tp_type,
2312 transaction_uom
2313 FROM CST_LC_MMT_GT;
2314
2315 IF (l_sLog AND l_pLog) THEN
2316 l_num_records := SQL%ROWCOUNT;
2317 FND_LOG.STRING(
2318 FND_LOG.LEVEL_PROCEDURE,
2319 l_module || '.query(' || l_stmt_num || ')',
2320 l_num_records || ' Rows inserted'
2321 );
2322
2323 END IF;
2324
2325 l_stmt_num := 60;
2326 /* Create the xla events */
2327 INSERT INTO XLA_EVENTS_INT_GT
2328 (application_id,
2329 ledger_id,
2330 entity_code,
2331 source_id_int_1,
2332 source_id_int_2,
2333 source_id_int_3,
2334 event_class_code,
2335 event_type_code,
2336 event_date,
2337 event_status_code,
2338 security_id_int_1,
2339 security_id_int_2,
2340 transaction_date,
2341 reference_date_1,
2342 transaction_number,
2343 budgetary_control_flag)
2344 SELECT 707,
2345 acc.ledger_id,
2346 'RCV_ACCOUNTING_EVENTS',
2347 rae.rcv_transaction_id,
2348 rae.accounting_event_id,
2349 rae.organization_id,
2350 cmap.event_class_code,
2351 cmap.event_type_code,
2352 rae.transaction_date,
2353 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
2354 rae.organization_id,
2355 rae.org_id,
2356 rae.transaction_date,
2357 acc.accounting_date,
2358 rae.accounting_event_id,
2359 NULL
2360 FROM cst_lc_rae_events_gt rae,
2361 cst_lc_adj_acctg_info_gt acc,
2362 cst_xla_rcv_event_map cmap
2363 WHERE rae.lcm_transaction_id = acc.lcm_transaction_id
2364 AND cmap.event_class_code IN ('LDD_COST_ADJ_RCV', 'LDD_COST_ADJ_DEL')
2368 l_num_records := SQL%ROWCOUNT;
2365 AND cmap.transaction_type_id = rae.event_type_id;
2366
2367 IF (l_sLog AND l_pLog) THEN
2369 FND_LOG.STRING(
2370 FND_LOG.LEVEL_PROCEDURE,
2371 l_module || '.query(' || l_stmt_num || ')',
2372 l_num_records || ' Rows inserted'
2373 );
2374
2375 END IF;
2376
2377 l_stmt_num := 70;
2378
2379 /* Call the xla package for creating xla events for the receiving events in bulk */
2380 xla_events_pub_pkg.create_bulk_events(p_application_id => 707,
2381 p_ledger_id => p_ledger_id,
2382 p_entity_type_code => 'RCV_ACCOUNTING_EVENTS',
2383 p_source_application_id => 201);
2384
2385 l_stmt_num := 80;
2386 /* Insert into the LCM transactions table to maintain history of this adjustment */
2387 INSERT INTO cst_lc_adj_transactions
2388 (transaction_id,
2389 rcv_transaction_id,
2390 organization_id,
2391 inventory_item_id,
2392 transaction_date,
2393 prior_landed_cost,
2394 new_landed_cost,
2395 group_id,
2396 creation_date,
2397 created_by,
2398 last_update_date,
2399 last_updated_by,
2400 last_update_login,
2401 request_id,
2402 program_application_id,
2403 program_id,
2404 program_update_date)
2405 SELECT
2406 transaction_id,
2407 rcv_transaction_id,
2408 organization_id,
2409 inventory_item_id,
2410 transaction_date,
2411 prior_landed_cost,
2412 new_landed_cost,
2413 group_id,
2414 SYSDATE,
2415 fnd_global.user_id,
2416 SYSDATE,
2417 fnd_global.user_id,
2418 fnd_global.login_id,
2419 fnd_global.conc_request_id,
2420 fnd_global.prog_appl_id,
2421 fnd_global.conc_program_id,
2422 SYSDATE
2423 FROM cst_lc_adj_interface
2424 WHERE group_id = p_group_id
2425 AND organization_id = p_organization_id
2426 AND process_status = 2;
2427
2428 IF (l_sLog AND l_pLog) THEN
2429 l_num_records := SQL%ROWCOUNT;
2430 FND_LOG.STRING(
2431 FND_LOG.LEVEL_PROCEDURE,
2432 l_module || '.query(' || l_stmt_num || ')',
2433 l_num_records || ' Rows inserted'
2434 );
2435
2436 END IF;
2437
2438 l_stmt_num := 90;
2439 DELETE
2440 FROM cst_lc_adj_interface e
2441 WHERE e.group_id = p_group_id
2442 AND e.organization_id = p_organization_id
2443 AND e.process_status = 2
2444 AND EXISTS (SELECT 1
2445 FROM cst_lc_adj_transactions t
2446 WHERE t.transaction_id = e.transaction_id);
2447
2448 IF (l_sLog AND l_pLog) THEN
2449 l_num_records := SQL%ROWCOUNT;
2450 FND_LOG.STRING(
2451 FND_LOG.LEVEL_PROCEDURE,
2452 l_module || '.query(' || l_stmt_num || ')',
2453 l_num_records || ' Rows deleted'
2454 );
2455
2456 END IF;
2457
2458 l_stmt_num := 100;
2459 IF (l_pLog) THEN
2460 FND_LOG.STRING(
2461 FND_LOG.LEVEL_PROCEDURE,
2462 l_module || '.end',
2463 '<< ' || l_api_name || ': Out Parameters:' ||
2464 ', x_return_status ' || x_return_status
2465 );
2466 END IF;
2467
2468 EXCEPTION
2469
2470 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2471 x_return_status := FND_API.g_ret_sts_error;
2472 IF (l_uLog) THEN
2473 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
2474 END IF;
2475
2476 WHEN FND_API.G_EXC_ERROR THEN
2477 x_return_status := FND_API.g_ret_sts_error;
2478 IF (l_exceptionLog) THEN
2479 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,l_module, TRUE);
2480 END IF;
2481
2482 WHEN OTHERS THEN
2483 x_return_status := FND_API.g_ret_sts_error;
2484 IF (l_uLog) THEN
2485 FND_MESSAGE.set_name('BOM', 'CST_UNEXP_ERROR');
2486 FND_MESSAGE.set_token('PACKAGE', G_PKG_NAME);
2487 FND_MESSAGE.set_token('PROCEDURE',l_api_name);
2488 FND_MESSAGE.set_token('STATEMENT',to_char(l_stmt_num));
2489 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
2490 FND_MSG_PUB.ADD;
2491
2492 FND_MESSAGE.SET_NAME('BOM','CST_LOG_UNEXPECTED');
2493 FND_MESSAGE.SET_TOKEN('SQLERRM',SQLERRM);
2494 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
2495 FND_MSG_PUB.ADD;
2496
2497 END IF;
2498
2499 END Insert_Adjustment_Data;
2500
2501 /*===========================================================================+
2502 | |
2503 | PROCEDURE : Validate_Lc_Interface |
2504 | |
2505 | DESCRIPTION : This procedure validates unvalidated interface records, |
2506 | populates details of failed validation into table |
2507 | CST_LC_ADJ_INTERFACE_ERRORS and updates the process_status|
2508 | of interface records to validated = 2 or errored = 3. |
2509 | |
2510 | |
2511 | |
2512 | CALLED FROM : Process_LcmAdjustments Procedure |
2513 | |
2514 | Parameters : |
2515 | IN : p_group_id IN NUMBER REQUIRED |
2516 | p_organization_id IN NUMBER REQUIRED |
2520 | |
2517 | p_api_version IN NUMBER REQUIRED |
2518 | p_init_msg_list IN VARCHAR2 REQUIRED |
2519 | p_validation_level IN NUMBER REQUIRED |
2521 | OUT : x_ledger_id OUT NOCOPY VARCHAR2 |
2522 | x_primary_cost_method OUT NOCOPY NUMBER |
2523 | x_primary_cost_method OUT NOCOPY VARCHAR2 |
2524 | x_return_status OUT NOCOPY NUMBER |
2525 | |
2526 | NOTES : None |
2527 | |
2528 | |
2529 +===========================================================================*/
2530
2531 PROCEDURE Validate_Lc_Interface
2532 (
2533 p_api_version IN NUMBER,
2534 p_init_msg_list IN VARCHAR2,
2535 p_validation_level IN NUMBER,
2536 p_group_id IN NUMBER,
2537 p_organization_id IN NUMBER,
2538 x_no_of_errored OUT NOCOPY NUMBER,
2539 x_return_status OUT NOCOPY VARCHAR2
2540 )
2541
2542 IS
2543 l_api_name CONSTANT VARCHAR2(30) :='Validate_Lc_Interface';
2544 l_api_version CONSTANT NUMBER := 1.0;
2545 l_return_status VARCHAR2(1);
2546 l_module CONSTANT VARCHAR2(100) := 'cst.plsql.'|| G_PKG_NAME || '.' || l_api_name;
2547
2548 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
2549 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
2550 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
2551 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
2552 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2553 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
2554
2555 l_stmt_num NUMBER;
2556 l_num_records NUMBER;
2557 l_msg_data VARCHAR2(240);
2558
2559 l_gl_info RCV_CreateAccounting_PVT.RCV_AE_GLINFO_REC_TYPE;
2560
2561 BEGIN
2562
2563 l_stmt_num := 0;
2564
2565 /* Procedure level log message for Entry point */
2566 IF (l_pLog) THEN
2567 FND_LOG.STRING(
2568 FND_LOG.LEVEL_PROCEDURE,
2569 l_module || '.begin',
2570 '>> ' || l_api_name || ': Parameters:' ||
2571 ' Api version ' || p_api_version ||
2572 ', Init msg list ' || p_init_msg_list ||
2573 ', Validation level ' || p_validation_level ||
2574 ', Group id ' || p_group_id
2575 );
2576 END IF;
2577
2578 /* Initialize API return status to success */
2579 x_return_status := FND_API.G_RET_STS_SUCCESS;
2580 l_return_status := FND_API.G_RET_STS_SUCCESS;
2581
2582 /* Standard call to check for call compatibility */
2583 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2584 p_api_version,
2585 l_api_name,
2586 G_PKG_NAME )
2587 THEN
2588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2589 END IF;
2590
2591 /* Initialize message list if p_init_msg_list is set to TRUE */
2592 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2593 FND_MSG_PUB.initialize;
2594 END IF;
2595
2596 l_stmt_num := 10;
2597
2598 /* Validating the lcm adjustments.
2599 A record will be created for each, lcm adjustment transaction and error combination in
2600 the cst_lc_adj_interface_errors table and the adjustment interface record will be
2601 set to error status */
2602 INSERT ALL
2603 WHEN new_landed_cost < 0 THEN
2604 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2605 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2606 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2607 VALUES (p_group_id, transaction_id, 'NEW_LANDED_COST', 'The column cannot have negative value',
2608 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2609 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2610 WHEN prior_landed_cost < 0 THEN
2611 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2612 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2613 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2614 VALUES (p_group_id, transaction_id, 'PRIOR_LANDED_COST', 'The column cannot have negative value',
2615 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2616 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2617 WHEN closing_status <> 'O' THEN
2618 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2619 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2620 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2621 VALUES (p_group_id, transaction_id, 'TRANSACTION_DATE', 'Purchasing Period is Closed or in the Future',
2622 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2623 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2624 WHEN open_flag = 'N' THEN
2628 VALUES (p_group_id, transaction_id, 'TRANSACTION_DATE', 'Inventory Period is Closed',
2625 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2626 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2627 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2629 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2630 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2631 WHEN rcv_transaction_id = -1 THEN
2632 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2633 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2634 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2635 VALUES (p_group_id, transaction_id, 'RCV_TRANSACTION_ID', 'Invalid Rcv Transaction',
2636 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2637 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2638 WHEN destination_type_code = 'EXPENSE' OR destination_type_code = 'SHOP FLOOR' THEN
2639 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2640 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2641 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2642 VALUES (p_group_id, transaction_id, 'RCV_TRANSACTION_ID',
2643 'Destination type is set to ' || destination_type_code, fnd_global.user_id, SYSDATE,
2644 fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id,
2645 fnd_global.user_id, fnd_global.conc_request_id)
2646 WHEN NOT (parent_transaction_id = -1 AND transaction_type IN ('RECEIVE', 'MATCH') ) THEN
2647 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2648 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2649 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2650 VALUES (p_group_id, transaction_id, 'RCV_TRANSACTION_ID', 'The receipt transaction is not the parent receipt',
2651 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2652 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2653 WHEN lcm_flag = 'N' THEN
2654 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2655 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2656 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2657 VALUES (p_group_id, transaction_id, 'RCV_TRANSACTION_ID', 'PO Shipment is not LCM Enabled',
2658 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2659 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2660 WHEN pol_item_id <> clai_inventory_item_id THEN
2661 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2662 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2663 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2664 VALUES (p_group_id, transaction_id, 'INVENTORY_ITEM_ID',
2665 'Item Id in adjustment transaction and original receipt donot match', fnd_global.user_id, SYSDATE,
2666 fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id,
2667 fnd_global.user_id, fnd_global.conc_request_id)
2668 WHEN rt_organization_id <> clai_organization_id THEN
2669 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2670 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2671 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2672 VALUES (p_group_id, transaction_id, 'ORGANIZATION_ID', 'Organization Id in adjustment transaction
2673 and original receipt donot match', fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id,
2674 fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id, fnd_global.user_id,
2675 fnd_global.conc_request_id)
2676 WHEN lcm_enabled_flag = 'N' THEN
2677 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2678 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2679 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2680 VALUES (p_group_id, transaction_id, 'ORGANIZATION_ID', 'Organization is not LCM Enabled',
2681 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2682 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2683 WHEN nvl(lcm_account_id, -1) = -1 THEN
2684 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2685 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2686 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2687 VALUES (p_group_id, transaction_id, 'ORGANIZATION_ID',
2688 'Landed cost absorption account is not defined for the organization', fnd_global.user_id, SYSDATE,
2689 fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id,
2690 fnd_global.user_id, fnd_global.conc_request_id)
2691 WHEN Decode(primary_cost_method, 1, 0, Nvl(lcm_var_account, -1)) = -1 THEN
2692 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2693 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2694 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2695 VALUES (p_group_id, transaction_id, 'ORGANIZATION_ID',
2696 'Landed cost variance account is not defined for the organization', fnd_global.user_id, SYSDATE,
2697 fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id,
2698 fnd_global.user_id, fnd_global.conc_request_id)
2699 SELECT distinct clai.transaction_id, clai.rcv_transaction_id, new_landed_cost, prior_landed_cost,
2700 gps.closing_status, open_flag, Nvl(rt.transaction_id, -1) recv_transaction_id,
2704 Nvl(rt.organization_id, -1) rt_organization_id, clai.organization_id clai_organization_id,
2701 pod.destination_type_code, parent_transaction_id, transaction_type, Nvl(POLL.lcm_flag, 'N') lcm_flag,
2702 pol.item_id pol_item_id,
2703 clai.inventory_item_id clai_inventory_item_id,
2705 Nvl(lcm_enabled_flag, 'N') lcm_enabled_flag,
2706 mp.lcm_var_account, rp.lcm_account_id, mp.primary_cost_method,
2707 msi.inventory_item_id msi_item_id
2708 FROM org_acct_periods oap,
2709 cst_lc_adj_interface clai,
2710 rcv_transactions rt,
2711 rcv_accounting_events rae,
2712 po_lines_all pol,
2713 po_line_locations_all poll,
2714 po_distributions_all pod,
2715 gl_period_statuses gps,
2716 cst_acct_info_v cai,
2717 mtl_parameters mp,
2718 mtl_system_items_b msi,
2719 rcv_parameters rp
2720 WHERE oap.organization_id (+) = clai.organization_id
2721 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) >= Trunc(oap.period_start_date)
2722 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) <= Trunc(oap.schedule_close_date)
2723 AND clai.group_id = p_group_id
2724 AND clai.process_status = 1
2725 AND clai.organization_id = p_organization_id
2726 AND rt.transaction_id (+) = clai.rcv_transaction_id
2727 AND rae.rcv_transaction_id = clai.rcv_transaction_id
2728 AND rae.event_type_id in (1,4)
2729 AND rae.po_line_location_id = poll.line_location_id
2730 AND rae.po_distribution_id = pod.po_distribution_id
2731 AND rae.po_line_id = pol.po_line_id
2732 AND cai.organization_id (+) = clai.organizatIon_id
2733 AND gps.set_of_books_id = rae.set_of_books_id
2734 AND gps.application_id = G_PO_APPLICATION_ID
2735 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) >= Trunc(gps.start_date)
2736 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) <= Trunc(gps.end_date)
2737 AND gps.adjustment_period_flag <> 'Y'
2738 AND mp.organization_id (+) = clai.organization_id
2739 AND rp.organization_id (+) = clai.organization_id
2740 AND msi.inventory_item_id (+) = clai.inventory_item_id
2741 AND msi.organization_id (+) = clai.organization_id;
2742
2743 IF (l_sLog AND l_pLog) THEN
2744 l_num_records := SQL%ROWCOUNT;
2745 FND_LOG.STRING(
2746 FND_LOG.LEVEL_PROCEDURE,
2747 l_module || '.query(' || l_stmt_num || ')',
2748 l_num_records || ' Rows inserted'
2749 );
2750
2751 END IF;
2752
2753 l_stmt_num := 20;
2754 /* For already validated transactions only check if PO period is still open */
2755 INSERT INTO cst_lc_adj_interface_errors
2756 (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2757 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2758 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2759 SELECT p_group_id, transaction_id, 'TRANSACTION_DATE', 'Purchasing Period is Closed or in the Future',
2760 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id,
2761 SYSDATE, SYSDATE, fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id
2762 FROM cst_lc_adj_interface clai,
2763 gl_period_statuses gps,
2764 cst_acct_info_v cai
2765 WHERE clai.group_id = p_group_id
2766 AND clai.process_status = 2
2767 AND clai.organization_id = p_organization_id
2768 AND cai.organization_id = p_organization_id
2769 AND gps.set_of_books_id = cai.ledger_id
2770 AND gps.application_id = G_PO_APPLICATION_ID
2771 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) >= Trunc(gps.start_date)
2772 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) <= Trunc(gps.end_date)
2773 AND gps.adjustment_period_flag <> 'Y'
2774 and gps.closing_status <> 'O';
2775
2776 IF (l_sLog AND l_pLog) THEN
2777 l_num_records := SQL%ROWCOUNT;
2778 FND_LOG.STRING(
2779 FND_LOG.LEVEL_PROCEDURE,
2780 l_module || '.query(' || l_stmt_num || ')',
2781 l_num_records || ' Rows inserted'
2782 );
2783
2784 END IF;
2785
2786 l_stmt_num := 30;
2787 /* Set the errored adjustment interface records for the group to ERROR status */
2788 UPDATE cst_lc_adj_interface i
2789 SET process_status = 3,
2790 group_id = NULL
2791 WHERE group_id = p_group_id
2792 AND process_status IN (1,2)
2793 AND organization_id = p_organization_id
2794 AND EXISTS (SELECT 1
2795 FROM cst_lc_adj_interface_errors e
2796 WHERE e.transaction_id = i.transaction_id
2797 AND e.group_id = p_group_id);
2798
2799 IF (l_sLog AND l_pLog) THEN
2800 l_num_records := SQL%ROWCOUNT;
2801 FND_LOG.STRING(
2802 FND_LOG.LEVEL_PROCEDURE,
2803 l_module || '.query(' || l_stmt_num || ')',
2804 l_num_records || ' Rows updated'
2805 );
2806
2807 END IF;
2808
2809 x_no_of_errored := SQL%ROWCOUNT;
2810
2811 l_stmt_num := 40;
2812 /* Set all unerrored adjustment interface records for the group to VALIDATED status */
2813 UPDATE cst_lc_adj_interface
2814 SET process_status = 2
2815 WHERE group_id = p_group_id
2816 AND organization_id = p_organization_id
2817 AND process_status = 1;
2818
2819 IF (l_sLog AND l_pLog) THEN
2820 l_num_records := SQL%ROWCOUNT;
2821 FND_LOG.STRING(
2822 FND_LOG.LEVEL_PROCEDURE,
2823 l_module || '.query(' || l_stmt_num || ')',
2827 END IF;
2824 l_num_records || ' Rows updated'
2825 );
2826
2828
2829 l_stmt_num := 50;
2830 IF (
2831 l_pLog) THEN
2832 FND_LOG.STRING(
2833 FND_LOG.LEVEL_PROCEDURE,
2834 l_module || '.end',
2835 '<< ' || l_api_name || ': Out Parameters:' ||
2836 ' x_no_of_errored ' || x_no_of_errored ||
2837 ', x_return_status ' || x_return_status
2838 );
2839 END IF;
2840
2841 EXCEPTION
2842
2843 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2844 x_return_status := FND_API.g_ret_sts_error;
2845 IF (l_uLog) THEN
2846 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
2847 END IF;
2848
2849 WHEN FND_API.G_EXC_ERROR THEN
2850 x_return_status := FND_API.g_ret_sts_error;
2851 IF (l_exceptionLog) THEN
2852 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,l_module, TRUE);
2853 END IF;
2854
2855 WHEN OTHERS THEN
2856 x_return_status := FND_API.g_ret_sts_error;
2857 IF (l_uLog) THEN
2858 FND_MESSAGE.set_name('BOM', 'CST_UNEXP_ERROR');
2859 FND_MESSAGE.set_token('PACKAGE', G_PKG_NAME);
2860 FND_MESSAGE.set_token('PROCEDURE',l_api_name);
2861 FND_MESSAGE.set_token('STATEMENT',to_char(l_stmt_num));
2862 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
2863 FND_MSG_PUB.ADD;
2864
2865 FND_MESSAGE.SET_NAME('BOM','CST_LOG_UNEXPECTED');
2866 FND_MESSAGE.SET_TOKEN('SQLERRM',SQLERRM);
2867 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
2868 FND_MSG_PUB.ADD;
2869
2870 END IF;
2871
2872 END Validate_Lc_Interface;
2873
2874 END CST_LcmAdjustments_PVT; -- end package body