[Home] [Help]
PACKAGE BODY: APPS.CST_LCMADJUSTMENTS_PVT
Source
4 /*------------------------------------------------------------------------------------------
1 PACKAGE BODY CST_LcmAdjustments_PVT AS
2 /* $Header: CSTLCADB.pls 120.8.12020000.2 2012/07/11 12:56:30 vkatakam ship $ */
3
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 | |
97 (
94 +===========================================================================*/
95
96 PROCEDURE Process_LcmAdjustments
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,
237 p_organization_id => p_organization_id,
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,
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',
269 '<< ' || l_api_name
270 );
271 END IF;
272
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 |
366
363 | |
364 | |
365 +===========================================================================*/
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);
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);
391 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= 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,
500 fnd_currencies fc
497 mtl_parameters mp,
498 cst_acct_info_v cai,
499 gl_sets_of_books gsob,
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,
537 receiving_account,
538 purchase_price_var_account,
539 org_expense_account,
540 legal_entity_id,
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
646 CONNECT BY rt.parent_transaction_id = PRIOR rt.transaction_id;
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
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,
684 rt.subinventory_code
685 FROM rcv_transactions rt_parent,
686 rcv_accounting_events rae,
687 CST_LC_RCV_TXN_GT rt
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',
766 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
763 Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
764 5,
765 6), 6),
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,
806 6,
807 4,
808 2,
809 5,
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)
949 AND organization_id = p_organization_id
950 AND EXISTS (SELECT 1
951 FROM cst_lc_adj_interface_errors e
952 WHERE e.transaction_id = i.transaction_id
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 |
1087 | |
1088 | OUT : x_return_status OUT NOCOPY NUMBER |
1089 | |
1090 | NOTES : None |
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,
1237 'RETURN TO VENDOR', -1,
1238 0)) * rt.source_doc_quantity) source_doc_quantity,
1239 sum(Decode(rt.transaction_type,
1240 'RECEIVE', 1,
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 HAVING sum(Decode(rt.transaction_type,
1297 'RECEIVE', 1,
1298 'MATCH', 1,
1299 'RETURN TO VENDOR', -1,
1300 'CORRECT', Decode(rt.parent_transaction_type,
1301 'RECEIVE', 1,
1302 'MATCH', 1,
1303 'RETURN TO VENDOR', -1,
1304 0)) * rt.source_doc_quantity) <> 0;
1305
1306 IF (l_sLog AND l_pLog) THEN
1307 l_num_records := SQL%ROWCOUNT;
1308 FND_LOG.STRING(
1309 FND_LOG.LEVEL_PROCEDURE,
1310 l_module || '.query(' || l_stmt_num || ')',
1311 l_num_records || ' Rows inserted'
1312 );
1313
1314 END IF;
1315
1316 l_stmt_num := 20;
1317
1318 /* Create the Delivery Adjustment Events - Event type id = 16,17 */
1319 l_stmt_num := 30;
1320 INSERT INTO CST_LC_RAE_EVENTS_GT
1321 (LCM_TRANSACTION_ID,
1322 ACCOUNTING_EVENT_ID,
1323 EVENT_SOURCE,
1324 EVENT_SOURCE_ID,
1325 EVENT_TYPE_ID,
1326 RCV_TRANSACTION_ID,
1327 INVENTORY_ITEM_ID,
1328 PRIOR_UNIT_PRICE,
1329 UNIT_PRICE,
1330 transaction_date,
1331 organization_id,
1332 ORG_ID,
1333 SET_OF_BOOKS_ID,
1334 PO_HEADER_ID,
1335 PO_RELEASE_ID,
1336 PO_LINE_ID,
1337 PO_LINE_LOCATION_ID,
1338 PO_DISTRIBUTION_ID,
1339 CURRENCY_CODE,
1343 SOURCE_DOC_UNIT_OF_MEASURE,
1340 CURRENCY_CONVERSION_TYPE,
1341 CURRENCY_CONVERSION_RATE,
1342 CURRENCY_CONVERSION_DATE,
1344 TRANSACTION_UNIT_OF_MEASURE,
1345 PRIMARY_UNIT_OF_MEASURE,
1346 source_doc_quantity,
1347 TRANSACTION_quantity,
1348 primary_quantity,
1349 CREDIT_ACCOUNT_ID,
1350 DEBIT_ACCOUNT_ID,
1351 CREDIT_ACCOUNTING_LINE_TYPE,
1352 DEBIT_ACCOUNTING_LINE_TYPE,
1353 /* who columns */
1354 CREATED_BY,
1355 CREATION_DATE,
1356 PROGRAM_APPLICATION_ID,
1357 PROGRAM_ID,
1358 PROGRAM_UPDATE_DATE,
1359 LAST_UPDATE_DATE,
1360 LAST_UPDATE_LOGIN,
1361 LAST_UPDATED_BY,
1362 REQUEST_ID,
1363 ACCOUNTED_FLAG)
1364 SELECT acc.lcm_transaction_id,
1365 NULL,
1366 'LC_ADJUSTMENTS',
1367 acc.lcm_transaction_id,
1368 Decode(adj_acc.exp_flag,
1369 1, 17,
1370 16),
1371 acc.rcv_transaction_id,
1372 acc.inventory_item_id,
1373 acc.prior_landed_cost,
1374 acc.new_landed_cost,
1375 acc.transaction_date,
1376 acc.organization_id,
1377 acc.operating_unit,
1378 acc.ledger_id,
1379 acc.PO_HEADER_ID,
1380 acc.PO_RELEASE_ID,
1381 acc.PO_LINE_ID,
1382 acc.PO_LINE_LOCATION_ID,
1383 rt.PO_DISTRIBUTION_ID,
1384 acc.func_currency_code,
1385 NULL,
1386 1,
1387 acc.transaction_date,
1388 acc.SOURCE_DOC_UNIT_OF_MEASURE,
1389 acc.PRIMARY_UNIT_OF_MEASURE,
1390 acc.PRIMARY_UNIT_OF_MEASURE,
1391 sum(Decode(transaction_type,
1392 'DELIVER', 1,
1393 'RETURN TO RECEIVING', -1,
1394 'CORRECT', Decode(parent_transaction_type,
1395 'DELIVER', 1,
1396 'RETURN TO RECEIVING', -1,
1397 0)) * source_doc_quantity) source_doc_quantity,
1398 sum(Decode(transaction_type,
1399 'DELIVER', 1,
1400 'RETURN TO RECEIVING', -1,
1401 'CORRECT', Decode(parent_transaction_type,
1402 'DELIVER', 1,
1403 'RETURN TO RECEIVING', -1,
1404 0)) * primary_quantity) transaction_quantity,
1405 sum(Decode(transaction_type,
1406 'DELIVER', 1,
1407 'RETURN TO RECEIVING', -1,
1408 'CORRECT', Decode(parent_transaction_type,
1409 'DELIVER', 1,
1410 'RETURN TO RECEIVING', -1,
1411 0)) * primary_quantity) primary_quantity,
1412 Decode(adj_acc.debit_credit_flag, 1, acc.receiving_account, adj_acc.account) CREDIT_ACCOUNT_ID,
1413 Decode(adj_acc.debit_credit_flag, -1, acc.receiving_account, adj_acc.account) DEBIT_ACCOUNT_ID,
1414 Decode(adj_acc.debit_credit_flag, 1, 'Receiving Inspection', adj_acc.accounting_line_type) CREDIT_ACCOUNTING_LINE_TYPE,
1415 Decode(adj_acc.debit_credit_flag, -1, 'Receiving Inspection', adj_acc.accounting_line_type) DEBIT_ACCOUNTING_LINE_TYPE,
1416 fnd_global.user_id,
1417 SYSDATE,
1418 fnd_global.prog_appl_id,
1419 fnd_global.conc_program_id,
1420 SYSDATE,
1421 SYSDATE,
1422 fnd_global.login_id,
1423 fnd_global.user_id,
1424 fnd_global.conc_request_id,
1425 'N'
1426 FROM cst_lc_adj_acctg_info_gt acc,
1427 cst_lc_rcv_txn_gt rt,
1428 cst_lc_accounts_gt adj_acc
1429 WHERE (rt.transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
1430 or (rt.transaction_type = 'CORRECT'
1431 and rt.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')))
1432 and rt.lcm_transaction_id = acc.lcm_transaction_id
1433 and adj_acc.lcm_transaction_id = acc.lcm_transaction_id
1434 GROUP BY acc.lcm_transaction_id,
1435 acc.rcv_transaction_id,
1436 inventory_item_id,
1437 prior_landed_cost,
1438 new_landed_cost,
1439 transaction_date,
1440 acc.organization_id,
1441 operating_unit,
1442 ledger_id,
1443 po_header_id,
1444 po_release_id,
1445 po_line_id,
1446 po_line_location_id,
1447 po_distribution_id,
1448 func_currency_code,
1449 source_doc_unit_of_measure,
1450 primary_unit_of_measure,
1451 receiving_account,
1452 adj_acc.account,
1453 adj_acc.exp_flag,
1454 adj_acc.debit_credit_flag,
1455 adj_acc.accounting_line_type
1456 HAVING sum(Decode(transaction_type,
1457 'DELIVER', 1,
1458 'RETURN TO RECEIVING', -1,
1459 'CORRECT', Decode(parent_transaction_type,
1460 'DELIVER', 1,
1461 'RETURN TO RECEIVING', -1,
1462 0)) * source_doc_quantity) <> 0;
1463
1464 IF (l_sLog AND l_pLog) THEN
1465 l_num_records := SQL%ROWCOUNT;
1466 FND_LOG.STRING(
1467 FND_LOG.LEVEL_PROCEDURE,
1468 l_module || '.query(' || l_stmt_num || ')',
1469 l_num_records || ' Rows inserted'
1470 );
1471
1472 END IF;
1473
1474 l_stmt_num := 50;
1475 UPDATE CST_LC_RAE_EVENTS_GT
1476 SET accounting_event_id = rcv_accounting_event_s.NEXTVAL;
1477
1478 l_stmt_num := 60;
1479 IF p_primary_cost_method = 2 THEN
1480 l_stmt_num := 70;
1481
1482 /* Insert Average cost update transactions for the deliveries of
1483 asset items into asset organizations */
1484 INSERT INTO CST_LC_MMT_GT
1485 (lcm_transaction_id,
1486 transaction_id,
1487 transaction_type_id,
1488 transaction_action_id,
1489 transaction_source_type_id,
1490 cost_group_id,
1491 primary_quantity,
1492 value_change,
1493 last_update_date,
1494 last_updated_by,
1495 creation_date,
1496 created_by,
1497 last_update_login,
1501 program_update_date,
1498 request_id,
1499 program_application_id,
1500 program_id,
1502 inventory_item_id,
1503 organization_id,
1504 transaction_date,
1505 acct_period_id,
1506 transaction_source_name,
1507 source_code,
1508 transaction_reference,
1509 trx_source_line_id,
1510 material_account,
1511 material_overhead_account,
1512 resource_account,
1513 outside_processing_account,
1514 overhead_account,
1515 expense_account_id,
1516 costed_flag,
1517 pm_cost_collected,
1518 owning_organization_id,
1519 owning_tp_type,
1520 transaction_uom)
1521 SELECT
1522 acc.lcm_transaction_id,
1523 NULL,
1524 80,
1525 24,
1526 13,
1527 mmt.cost_group_id,
1528 sum(mmt.primary_quantity),
1529 sum(mmt.primary_quantity) * (acc.new_landed_cost - acc.prior_landed_cost),
1530 SYSDATE,
1531 fnd_global.user_id,
1532 SYSDATE,
1533 fnd_global.user_id,
1534 fnd_global.login_id,
1535 fnd_global.conc_request_id,
1536 fnd_global.prog_appl_id,
1537 fnd_global.conc_program_id,
1538 SYSDATE,
1539 acc.inventory_item_id,
1540 acc.organization_id,
1541 acc.transaction_date,
1542 acc.acct_period_id,
1543 'LCM ADJUSTMENT',
1544 'LCMADJ',
1545 acc.lcm_transaction_id,
1546 acc.rcv_transaction_id,
1547 acc.lcm_account,
1548 acc.lcm_account,
1549 acc.lcm_account,
1550 acc.lcm_account,
1551 acc.lcm_account,
1552 acc.lcm_var_account,
1553 'N',
1554 NULL,
1555 acc.organization_id,
1556 2,
1557 msi.primary_uom_code
1558 FROM cst_lc_adj_acctg_info_gt acc,
1559 cst_lc_rcv_txn_gt rt,
1560 mtl_secondary_inventories mse,
1561 mtl_material_transactions mmt,
1562 mtl_system_items_b msi
1563 WHERE (rt.transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
1564 or (rt.transaction_type = 'CORRECT'
1565 and rt.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')))
1566 and rt.lcm_transaction_id = acc.lcm_transaction_id
1567 AND mse.secondary_inventory_name = rt.subinventory_code
1568 and mse.organization_id = acc.organization_id
1569 and mmt.rcv_transaction_id = rt.rcv_transaction_id
1570 AND (acc.inventory_asset_flag = 'Y' AND Nvl(mse.asset_inventory,2) = 1)
1571 AND msi.inventory_item_id = acc.inventory_item_id
1572 AND msi.organization_id= acc.organization_id
1573 GROUP BY
1574 mmt.cost_group_id,
1575 acc.inventory_item_id,
1576 acc.organization_id,
1577 acc.transaction_date,
1578 acc.acct_period_id,
1579 acc.rcv_transaction_id,
1580 acc.lcm_account,
1581 acc.lcm_transaction_id,
1582 acc.lcm_var_account,
1583 acc.new_landed_cost,
1584 acc.prior_landed_cost,
1585 msi.primary_uom_code
1586 HAVING sum(mmt.primary_quantity) <> 0;
1587
1588 IF (l_sLog AND l_pLog) THEN
1589 l_num_records := SQL%ROWCOUNT;
1590 FND_LOG.STRING(
1591 FND_LOG.LEVEL_PROCEDURE,
1592 l_module || '.query(' || l_stmt_num || ')',
1593 l_num_records || ' Rows inserted'
1594 );
1595
1596 END IF;
1597
1598 l_stmt_num := 75;
1599 UPDATE CST_LC_MMT_GT
1600 SET transaction_id = MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL;
1601
1602 IF (l_sLog AND l_pLog) THEN
1603 l_num_records := SQL%ROWCOUNT;
1604 FND_LOG.STRING(
1605 FND_LOG.LEVEL_PROCEDURE,
1606 l_module || '.query(' || l_stmt_num || ')',
1607 l_num_records || ' Rows updated'
1608 );
1609
1610 END IF;
1611
1612 END IF; --p_primary_cost_method = 2 THEN
1613
1614 l_stmt_num := 80;
1615 IF p_primary_cost_method IN (5,6) THEN
1616
1617 l_stmt_num := 90;
1618 /* Insert Layer cost update transactions for the deliveries of
1619 asset items into asset organizations */
1620 INSERT INTO CST_LC_MMT_GT
1621 (lcm_transaction_id,
1622 transaction_id,
1623 transaction_type_id,
1624 transaction_action_id,
1625 transaction_source_type_id,
1626 transaction_source_id,
1627 cost_group_id,
1628 primary_quantity,
1629 value_change,
1630 last_update_date,
1631 last_updated_by,
1632 creation_date,
1633 created_by,
1634 last_update_login,
1635 request_id,
1636 program_application_id,
1637 program_id,
1638 program_update_date,
1639 inventory_item_id,
1640 organization_id,
1641 transaction_date,
1642 acct_period_id,
1643 transaction_source_name,
1644 source_code,
1645 transaction_reference,
1646 trx_source_line_id,
1647 material_account,
1648 material_overhead_account,
1649 resource_account,
1650 outside_processing_account,
1651 overhead_account,
1652 expense_account_id,
1653 costed_flag,
1654 pm_cost_collected,
1655 owning_organization_id,
1656 owning_tp_type,
1657 transaction_uom)
1658 SELECT txn.lcm_transaction_id,
1659 NULL,
1660 28,
1664 txn.cost_group_id,
1661 24,
1662 15,
1663 inv_layer_id,
1665 sum(txn.layer_quantity),
1666 txn.value_change,
1667 SYSDATE,
1668 fnd_global.user_id,
1669 SYSDATE,
1670 fnd_global.user_id,
1671 fnd_global.login_id,
1672 fnd_global.conc_request_id,
1673 fnd_global.prog_appl_id,
1674 fnd_global.conc_program_id,
1675 SYSDATE,
1676 txn.inventory_item_id,
1677 txn.organization_id,
1678 txn.transaction_date,
1679 txn.acct_period_id,
1680 'LCM ADJUSTMENT',
1681 'LCMADJ',
1682 txn.lcm_transaction_id,
1683 txn.rcv_transaction_id,
1684 txn.lcm_account,
1685 txn.lcm_account,
1686 txn.lcm_account,
1687 txn.lcm_account,
1688 txn.lcm_account,
1689 txn.lcm_var_account,
1690 'N',
1691 NULL,
1692 txn.organization_id,
1693 2,
1694 msi.primary_uom_code
1695 FROM (SELECT DISTINCT acc.lcm_transaction_id,
1696 mmt.transaction_id,
1697 mclacd.inv_layer_id,
1698 mclacd.layer_quantity,
1699 mmt.cost_group_id,
1700 acc.inventory_item_id,
1701 acc.organization_id,
1702 acc.transaction_date,
1703 acc.acct_period_id,
1704 acc.rcv_transaction_id,
1705 acc.lcm_account,
1706 acc.lcm_var_account,
1707 (layer_quantity * (acc.new_landed_cost - acc.prior_landed_cost)) value_change
1708 FROM mtl_material_transactions mmt,
1709 mtl_cst_layer_act_cost_details mclacd,
1710 cst_lc_adj_acctg_info_gt acc,
1711 cst_lc_rcv_txn_gt rt,
1712 mtl_secondary_inventories mse
1713 WHERE (rt.transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
1714 or (rt.transaction_type = 'CORRECT'
1715 and rt.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')))
1716 and rt.lcm_transaction_id = acc.lcm_transaction_id
1717 AND mse.secondary_inventory_name = rt.subinventory_code
1718 and mse.organization_id = acc.organization_id
1719 and mmt.rcv_transaction_id = rt.rcv_transaction_id
1720 and mmt.transaction_id = mclacd.transaction_id
1721 AND (acc.inventory_asset_flag = 'Y' AND Nvl(mse.asset_inventory,2) = 1)) txn,
1722 mtl_system_items_b msi
1723 WHERE msi.inventory_item_id = txn.inventory_item_id
1724 AND msi.organization_id= txn.organization_id
1725 GROUP BY txn.lcm_transaction_id, txn.COST_GROUP_ID,
1726 txn.INV_LAYER_ID,
1727 txn.cost_group_id,
1728 txn.inventory_item_id,
1729 txn.organization_id,
1730 txn.transaction_date,
1731 txn.acct_period_id,
1732 txn.rcv_transaction_id,
1733 txn.value_change,
1734 txn.lcm_account,
1735 txn.lcm_var_account,
1736 msi.primary_uom_code
1737 HAVING sum(txn.layer_quantity) <> 0;
1738
1739 IF (l_sLog AND l_pLog) THEN
1740 l_num_records := SQL%ROWCOUNT;
1741 FND_LOG.STRING(
1742 FND_LOG.LEVEL_PROCEDURE,
1743 l_module || '.query(' || l_stmt_num || ')',
1744 l_num_records || ' Rows inserted'
1745 );
1746
1747 END IF;
1748
1749 l_stmt_num := 100;
1750 UPDATE CST_LC_MMT_GT
1751 SET transaction_id = MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL;
1752
1753 IF (l_sLog AND l_pLog) THEN
1754 l_num_records := SQL%ROWCOUNT;
1755 FND_LOG.STRING(
1756 FND_LOG.LEVEL_PROCEDURE,
1757 l_module || '.query(' || l_stmt_num || ')',
1758 l_num_records || ' Rows updated'
1759 );
1760
1761 END IF;
1762
1763 END IF; -- p_primary_cost_method IN (5,6) THEN
1764
1765
1766 l_stmt_num := 110;
1767 IF (l_pLog) THEN
1768 FND_LOG.STRING(
1769 FND_LOG.LEVEL_PROCEDURE,
1770 l_module || '.end',
1771 '<< ' || l_api_name || ': Out Parameters:' ||
1772 ', x_return_status ' || x_return_status
1773 );
1774 END IF;
1775
1776 EXCEPTION
1777
1778 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1779 x_return_status := FND_API.g_ret_sts_error;
1780 IF (l_uLog) THEN
1781 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
1782 END IF;
1783
1784 WHEN FND_API.G_EXC_ERROR THEN
1785 x_return_status := FND_API.g_ret_sts_error;
1786 IF (l_exceptionLog) THEN
1787 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,l_module, TRUE);
1788 END IF;
1789
1790 WHEN OTHERS THEN
1791 x_return_status := FND_API.g_ret_sts_error;
1792 IF (l_uLog) THEN
1793 FND_MESSAGE.set_name('BOM', 'CST_UNEXP_ERROR');
1794 FND_MESSAGE.set_token('PACKAGE', G_PKG_NAME);
1795 FND_MESSAGE.set_token('PROCEDURE',l_api_name);
1796 FND_MESSAGE.set_token('STATEMENT',to_char(l_stmt_num));
1797 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
1798 FND_MSG_PUB.ADD;
1799
1800 FND_MESSAGE.SET_NAME('BOM','CST_LOG_UNEXPECTED');
1801 FND_MESSAGE.SET_TOKEN('SQLERRM',SQLERRM);
1802 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
1803 FND_MSG_PUB.ADD;
1804
1805 END IF;
1806
1807 END Populate_Temp_Adjustment_Data;
1808
1809 /*===========================================================================+
1810 | |
1811 | PROCEDURE : Insert_Adjustment_Data |
1812 | |
1813 | DESCRIPTION : This procedure inserts the receiving events data and the |
1814 | average and layer cost update data from temporary tables |
1815 | into RCV_ACCOUNTING_EVENTS and MTL_MATERIAL_TRANSACTIONS. |
1816 | Accounting and other entries are also calculated and |
1817 | inserted into RCV_RECEIVING_SUBLEDGER, XLA_EVENTS_INT_GT |
1818 | and MTL_CST_TXN_COST_DETAILS. |
1819 | |
1820 | CALLED FROM : Process_LcmAdjustments Procedure |
1821 | |
1822 | Parameters : |
1823 | IN : p_group_id IN NUMBER REQUIRED |
1824 | p_organization_id IN NUMBER REQUIRED |
1825 | p_ledger_id IN NUMBER REQUIRED |
1826 | p_api_version IN NUMBER REQUIRED |
1827 | p_init_msg_list IN VARCHAR2 REQUIRED |
1828 | p_validation_level IN NUMBER REQUIRED |
1829 | |
1830 | OUT : x_return_status OUT NOCOPY NUMBER |
1831 | |
1832 | NOTES : None |
1833 | |
1834 | |
1835 +===========================================================================*/
1836
1837 PROCEDURE Insert_Adjustment_Data
1838 (
1839 p_api_version IN NUMBER,
1840 p_init_msg_list IN VARCHAR2,
1841 p_validation_level IN NUMBER,
1842 p_group_id IN NUMBER,
1843 p_organization_id IN NUMBER,
1844 p_ledger_id IN NUMBER,
1845 x_return_status OUT NOCOPY VARCHAR2
1846 )
1847
1848 IS
1849 l_api_name CONSTANT VARCHAR2(30) :='Insert_Adjustment_Data';
1850 l_api_version CONSTANT NUMBER := 1.0;
1851 l_return_status VARCHAR2(1);
1852 l_module CONSTANT VARCHAR2(100) := 'cst.plsql.'|| G_PKG_NAME || '.' || l_api_name;
1853
1854 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
1855 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1856 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1857 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1858 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1859 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1860
1861 l_stmt_num NUMBER;
1862 l_num_records NUMBER;
1863 l_msg_data VARCHAR2(240);
1864
1865
1866 BEGIN
1867
1868 l_stmt_num := 0;
1869
1870 /* Procedure level log message for Entry point */
1871 IF (l_pLog) THEN
1872 FND_LOG.STRING(
1873 FND_LOG.LEVEL_PROCEDURE,
1874 l_module || '.begin',
1875 '>> ' || l_api_name || ': Parameters:' ||
1876 ' Api version ' || p_api_version ||
1877 ', Init msg list ' || p_init_msg_list ||
1878 ', Validation level ' || p_validation_level ||
1879 ', Group id ' || p_group_id ||
1880 ', Ledger id ' || p_ledger_id
1881 );
1882 END IF;
1883
1884 /* Standard call to check for call compatibility */
1885 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1886 p_api_version,
1887 l_api_name,
1888 G_PKG_NAME )
1889 THEN
1890 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1891 END IF;
1892
1893 /* Initialize message list if p_init_msg_list is set to TRUE */
1894 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1895 FND_MSG_PUB.initialize;
1896 END IF;
1897
1898 /* Initialize API return status to success */
1899 x_return_status := FND_API.G_RET_STS_SUCCESS;
1900 l_return_status := FND_API.G_RET_STS_SUCCESS;
1901
1902 l_stmt_num := 10;
1903
1904 /* Create RCV accounting events data from the GTT */
1905 INSERT INTO rcv_accounting_events
1906 (accounting_event_id,
1907 event_source,
1908 event_source_id,
1909 event_type_id,
1910 rcv_transaction_id,
1911 inventory_item_id,
1912 prior_unit_price,
1913 unit_price,
1914 transaction_date,
1915 organization_id,
1916 org_id,
1917 set_of_books_id,
1918 po_header_id,
1919 po_release_id,
1920 po_line_id,
1921 po_line_location_id,
1922 po_distribution_id,
1923 currency_code,
1924 currency_conversion_type,
1925 currency_conversion_rate,
1926 currency_conversion_date,
1927 source_doc_unit_of_measure,
1928 transaction_unit_of_measure,
1929 primary_unit_of_measure,
1930 source_doc_quantity,
1931 transaction_quantity,
1932 primary_quantity,
1933 credit_account_id,
1934 debit_account_id,
1935 /* who columns */
1936 created_by,
1937 creation_date,
1938 program_application_id,
1939 program_id,
1940 program_udpate_date,
1941 last_update_date,
1942 last_update_login,
1943 last_updated_by,
1944 request_id,
1945 accounted_flag)
1946 SELECT accounting_event_id,
1947 event_source,
1948 event_source_id,
1949 event_type_id,
1950 rcv_transaction_id,
1951 inventory_item_id,
1952 prior_unit_price,
1953 unit_price,
1954 transaction_date,
1955 organization_id,
1956 org_id,
1957 set_of_books_id,
1958 po_header_id,
1959 po_release_id,
1960 po_line_id,
1961 po_line_location_id,
1962 po_distribution_id,
1963 currency_code,
1964 currency_conversion_type,
1965 currency_conversion_rate,
1966 currency_conversion_date,
1967 source_doc_unit_of_measure,
1968 transaction_unit_of_measure,
1969 primary_unit_of_measure,
1970 source_doc_quantity,
1971 transaction_quantity,
1972 primary_quantity,
1973 credit_account_id,
1974 debit_account_id,
1975 /* who columns */
1976 created_by,
1977 creation_date,
1978 program_application_id,
1979 program_id,
1980 program_update_date,
1981 last_update_date,
1982 last_update_login,
1983 last_updated_by,
1984 request_id,
1985 accounted_flag
1986 FROM cst_lc_rae_events_gt;
1987
1988 IF (l_sLog AND l_pLog) THEN
1989 l_num_records := SQL%ROWCOUNT;
1990 FND_LOG.STRING(
1991 FND_LOG.LEVEL_PROCEDURE,
1992 l_module || '.query(' || l_stmt_num || ')',
1993 l_num_records || ' Rows inserted'
1994 );
1995
1996 END IF;
1997
1998 l_stmt_num := 20;
1999
2000 /* Create the debit lines in the subledger for the rcv accounting events */
2001 INSERT INTO rcv_receiving_sub_ledger
2002 (created_by,
2003 creation_date,
2004 program_application_id,
2005 program_id,
2006 program_update_date,
2007 last_update_date,
2008 last_update_login,
2009 last_updated_by,
2010 request_id,
2011 rcv_sub_ledger_id,
2012 accounting_event_id,
2013 accounting_line_type,
2014 rcv_transaction_id,
2015 actual_flag,
2016 je_source_name,
2017 je_category_name,
2018 set_of_books_id,
2019 accounting_date,
2020 code_combination_id,
2021 entered_dr,
2022 accounted_dr,
2023 currency_code,
2024 user_currency_conversion_type,
2025 currency_conversion_rate,
2026 currency_conversion_date,
2027 transaction_date,
2028 period_name,
2029 chart_of_accounts_id,
2030 functional_currency_code,
2031 reference1,
2032 reference2,
2033 reference3,
2034 reference4,
2035 source_doc_quantity,
2036 accrual_method_flag,
2037 accounted_nr_tax,
2038 accounted_rec_tax,
2039 entered_nr_tax,
2040 entered_rec_tax
2041 )
2042 SELECT
2043 fnd_global.user_id,
2044 SYSDATE,
2045 fnd_global.prog_appl_id,
2046 fnd_global.conc_program_id,
2047 SYSDATE,
2048 SYSDATE,
2049 fnd_global.login_id,
2050 fnd_global.user_id,
2051 fnd_global.conc_request_id,
2052 rcv_receiving_sub_ledger_s.nextval,
2053 rae.accounting_event_id,
2054 rae.debit_accounting_line_type,
2055 rae.rcv_transaction_id,
2056 'A',
2057 'Purchasing',
2058 'Receiving',
2059 rae.set_of_books_id,
2060 Trunc(acc.accounting_date),
2061 rae.debit_account_id,
2062 /* ENTERED */
2063 Decode(func_minimum_accountable_unit
2064 , NULL, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price)), func_precision)
2065 , Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price))/ func_minimum_accountable_unit) * func_minimum_accountable_unit),
2066 /* ACCOUNTED */
2067 Decode(func_minimum_accountable_unit
2068 , NULL, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price)), func_precision)
2069 , Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price))/ func_minimum_accountable_unit) * func_minimum_accountable_unit),
2070 acc.func_currency_code,
2071 NULL,
2072 1,
2073 acc.transaction_date,
2074 acc.transaction_date,
2075 acc.period_name,
2076 acc.chart_of_accounts_id,
2077 acc.func_currency_code,
2078 'PO',
2079 rae.po_header_id,
2080 rae.po_distribution_id,
2081 acc.po_number,
2082 rae.source_doc_quantity,
2083 'O',
2084 0,
2085 0,
2086 0,
2087 0
2088 FROM cst_lc_rae_events_gt rae,
2089 cst_lc_adj_acctg_info_gt acc
2090 WHERE rae.lcm_transaction_id = acc.lcm_transaction_id;
2091
2092 IF (l_sLog AND l_pLog) THEN
2093 l_num_records := SQL%ROWCOUNT;
2094 FND_LOG.STRING(
2095 FND_LOG.LEVEL_PROCEDURE,
2096 l_module || '.query(' || l_stmt_num || ')',
2097 l_num_records || ' Rows inserted'
2098 );
2099
2100 END IF;
2101
2102 l_stmt_num := 30;
2103 /* Create the credit lines in the subledger for the rcv accounting events */
2104 INSERT INTO rcv_receiving_sub_ledger
2105 (created_by,
2106 creation_date,
2107 program_application_id,
2108 program_id,
2109 program_update_date,
2110 last_update_date,
2111 last_update_login,
2112 last_updated_by,
2113 request_id,
2114 rcv_sub_ledger_id,
2115 accounting_event_id,
2116 accounting_line_type,
2117 rcv_transaction_id,
2118 actual_flag,
2119 je_source_name,
2120 je_category_name,
2121 set_of_books_id,
2122 accounting_date,
2123 code_combination_id,
2124 accounted_cr,
2125 entered_cr,
2126 currency_code,
2127 user_currency_conversion_type,
2128 currency_conversion_rate,
2129 currency_conversion_date,
2130 transaction_date,
2131 period_name,
2132 chart_of_accounts_id,
2133 functional_currency_code,
2134 reference1,
2135 reference2,
2136 reference3,
2137 reference4,
2138 source_doc_quantity,
2139 accrual_method_flag,
2140 accounted_nr_tax,
2141 accounted_rec_tax,
2142 entered_nr_tax,
2143 entered_rec_tax)
2144 SELECT
2145 fnd_global.user_id,
2146 SYSDATE,
2147 fnd_global.prog_appl_id,
2148 fnd_global.conc_program_id,
2149 SYSDATE,
2150 SYSDATE,
2151 fnd_global.login_id,
2152 fnd_global.user_id,
2153 fnd_global.conc_request_id,
2154 rcv_receiving_sub_ledger_s.NEXTVAL,
2155 rae.accounting_event_id,
2156 rae.credit_accounting_line_type,
2157 rae.rcv_transaction_id,
2158 'A',
2159 'Purchasing',
2160 'Receiving',
2161 rae.set_of_books_id,
2162 Trunc(acc.accounting_date),
2163 rae.credit_account_id,
2164 /* ENTERED */
2165 Decode(func_minimum_accountable_unit
2166 , NULL, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price)), func_precision)
2167 , Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price))/ func_minimum_accountable_unit) * func_minimum_accountable_unit),
2168 /* ACCOUNTED */
2169 Decode(func_minimum_accountable_unit
2170 , NULL, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price)), func_precision)
2171 , Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price))/ func_minimum_accountable_unit) * func_minimum_accountable_unit),
2172 acc.func_currency_code,
2173 NULL,
2174 1,
2175 acc.transaction_date,
2176 acc.transaction_date,
2177 acc.period_name,
2178 acc.chart_of_accounts_id,
2179 acc.func_currency_code,
2180 'PO',
2181 rae.po_header_id,
2182 rae.po_distribution_id,
2183 acc.po_number,
2184 rae.source_doc_quantity,
2185 'O',
2186 0,
2187 0,
2188 0,
2189 0
2190 FROM cst_lc_rae_events_gt rae,
2191 cst_lc_adj_acctg_info_gt acc
2192 WHERE rae.lcm_transaction_id = acc.lcm_transaction_id;
2193
2194 IF (l_sLog AND l_pLog) THEN
2195 l_num_records := SQL%ROWCOUNT;
2196 FND_LOG.STRING(
2197 FND_LOG.LEVEL_PROCEDURE,
2198 l_module || '.query(' || l_stmt_num || ')',
2199 l_num_records || ' Rows inserted'
2200 );
2201
2202 END IF;
2203
2204 l_stmt_num := 40;
2205 /* Create the transaction cost details for the average /layer cost update transactions */
2206 INSERT INTO MTL_CST_TXN_COST_DETAILS
2207 (transaction_id,
2208 organization_id,
2209 inventory_item_id,
2210 cost_element_id,
2211 level_type,
2212 value_change,
2213 last_update_date,
2214 last_updated_by,
2215 creation_date,
2216 created_by,
2217 last_update_login,
2218 request_id,
2219 program_application_id,
2220 program_id,
2221 program_update_date)
2222 SELECT
2223 mmt.transaction_id,
2224 mmt.organization_id,
2225 mmt.inventory_item_id,
2226 1,
2227 1,
2228 mmt.primary_quantity * (acc.new_landed_cost - acc.prior_landed_cost),
2229 SYSDATE,
2230 fnd_global.user_id,
2231 SYSDATE,
2232 fnd_global.user_id,
2233 fnd_global.login_id,
2234 fnd_global.conc_request_id,
2235 fnd_global.prog_appl_id,
2236 fnd_global.conc_program_id,
2237 SYSDATE
2238 FROM cst_lc_mmt_gt mmt,
2239 cst_lc_adj_acctg_info_gt acc
2240 WHERE mmt.lcm_transaction_id = acc.lcm_transaction_id;
2241
2242 IF (l_sLog AND l_pLog) THEN
2243 l_num_records := SQL%ROWCOUNT;
2244 FND_LOG.STRING(
2245 FND_LOG.LEVEL_PROCEDURE,
2246 l_module || '.query(' || l_stmt_num || ')',
2247 l_num_records || ' Rows inserted'
2248 );
2249
2250 END IF;
2251
2252 l_stmt_num := 50;
2253
2254 /* Create the the average / layer cost update transactions */
2255 INSERT INTO MTL_MATERIAL_TRANSACTIONS
2256 (transaction_id,
2257 transaction_type_id,
2258 transaction_action_id,
2259 transaction_source_type_id,
2260 transaction_source_id,
2261 cost_group_id,
2262 primary_quantity,
2263 transaction_quantity,
2264 value_change,
2265 last_update_date,
2266 last_updated_by,
2267 creation_date,
2268 created_by,
2269 last_update_login,
2270 request_id,
2271 program_application_id,
2272 program_id,
2273 program_update_date,
2274 inventory_item_id,
2275 organization_id,
2276 transaction_date,
2277 acct_period_id,
2278 transaction_source_name,
2279 source_code,
2280 trx_source_line_id,
2281 transaction_reference,
2282 material_account,
2283 material_overhead_account,
2284 resource_account,
2285 outside_processing_account,
2286 overhead_account,
2287 expense_account_id,
2288 costed_flag,
2292 transaction_uom)
2289 pm_cost_collected,
2290 owning_organization_id,
2291 owning_tp_type,
2293 SELECT
2294 transaction_id,
2295 transaction_type_id,
2296 transaction_action_id,
2297 transaction_source_type_id,
2298 transaction_source_id,
2299 cost_group_id,
2300 primary_quantity,
2301 primary_quantity,
2302 value_change,
2303 last_update_date,
2304 last_updated_by,
2305 creation_date,
2306 created_by,
2307 last_update_login,
2308 request_id,
2309 program_application_id,
2310 program_id,
2311 program_update_date,
2312 inventory_item_id,
2313 organization_id,
2314 transaction_date,
2315 acct_period_id,
2316 transaction_source_name,
2317 source_code,
2318 trx_source_line_id,
2319 transaction_reference,
2320 material_account,
2321 material_overhead_account,
2322 resource_account,
2323 outside_processing_account,
2324 overhead_account,
2325 expense_account_id,
2326 costed_flag,
2327 pm_cost_collected,
2328 owning_organization_id,
2329 owning_tp_type,
2330 transaction_uom
2331 FROM CST_LC_MMT_GT;
2332
2333 IF (l_sLog AND l_pLog) THEN
2334 l_num_records := SQL%ROWCOUNT;
2335 FND_LOG.STRING(
2336 FND_LOG.LEVEL_PROCEDURE,
2337 l_module || '.query(' || l_stmt_num || ')',
2338 l_num_records || ' Rows inserted'
2339 );
2340
2341 END IF;
2342
2343 l_stmt_num := 60;
2344 /* Create the xla events */
2345 INSERT INTO XLA_EVENTS_INT_GT
2346 (application_id,
2347 ledger_id,
2348 entity_code,
2349 source_id_int_1,
2350 source_id_int_2,
2351 source_id_int_3,
2352 event_class_code,
2353 event_type_code,
2354 event_date,
2355 event_status_code,
2356 security_id_int_1,
2357 security_id_int_2,
2358 transaction_date,
2359 reference_date_1,
2360 transaction_number,
2361 budgetary_control_flag)
2362 SELECT 707,
2363 acc.ledger_id,
2364 'RCV_ACCOUNTING_EVENTS',
2365 rae.rcv_transaction_id,
2366 rae.accounting_event_id,
2367 rae.organization_id,
2368 cmap.event_class_code,
2369 cmap.event_type_code,
2370 rae.transaction_date,
2371 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
2372 rae.organization_id,
2373 rae.org_id,
2374 rae.transaction_date,
2375 acc.accounting_date,
2376 rae.accounting_event_id,
2377 NULL
2378 FROM cst_lc_rae_events_gt rae,
2379 cst_lc_adj_acctg_info_gt acc,
2380 cst_xla_rcv_event_map cmap
2381 WHERE rae.lcm_transaction_id = acc.lcm_transaction_id
2382 AND cmap.event_class_code IN ('LDD_COST_ADJ_RCV', 'LDD_COST_ADJ_DEL')
2383 AND cmap.transaction_type_id = rae.event_type_id;
2384
2385 IF (l_sLog AND l_pLog) THEN
2386 l_num_records := SQL%ROWCOUNT;
2387 FND_LOG.STRING(
2388 FND_LOG.LEVEL_PROCEDURE,
2389 l_module || '.query(' || l_stmt_num || ')',
2390 l_num_records || ' Rows inserted'
2391 );
2392
2393 END IF;
2394
2395 l_stmt_num := 70;
2396
2397 /* Call the xla package for creating xla events for the receiving events in bulk */
2398 xla_events_pub_pkg.create_bulk_events(p_application_id => 707,
2399 p_ledger_id => p_ledger_id,
2400 p_entity_type_code => 'RCV_ACCOUNTING_EVENTS',
2401 p_source_application_id => 201);
2402
2403 l_stmt_num := 80;
2404 /* Insert into the LCM transactions table to maintain history of this adjustment */
2405 INSERT INTO cst_lc_adj_transactions
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 creation_date,
2415 created_by,
2416 last_update_date,
2417 last_updated_by,
2418 last_update_login,
2419 request_id,
2420 program_application_id,
2421 program_id,
2422 program_update_date)
2423 SELECT
2424 transaction_id,
2425 rcv_transaction_id,
2426 organization_id,
2427 inventory_item_id,
2428 transaction_date,
2429 prior_landed_cost,
2430 new_landed_cost,
2431 group_id,
2432 SYSDATE,
2433 fnd_global.user_id,
2434 SYSDATE,
2435 fnd_global.user_id,
2436 fnd_global.login_id,
2437 fnd_global.conc_request_id,
2438 fnd_global.prog_appl_id,
2439 fnd_global.conc_program_id,
2440 SYSDATE
2441 FROM cst_lc_adj_interface
2442 WHERE group_id = p_group_id
2443 AND organization_id = p_organization_id
2444 AND process_status = 2;
2445
2446 IF (l_sLog AND l_pLog) THEN
2447 l_num_records := SQL%ROWCOUNT;
2448 FND_LOG.STRING(
2449 FND_LOG.LEVEL_PROCEDURE,
2450 l_module || '.query(' || l_stmt_num || ')',
2451 l_num_records || ' Rows inserted'
2452 );
2453
2454 END IF;
2455
2456 l_stmt_num := 90;
2457 DELETE
2458 FROM cst_lc_adj_interface e
2459 WHERE e.group_id = p_group_id
2460 AND e.organization_id = p_organization_id
2461 AND e.process_status = 2
2462 AND EXISTS (SELECT 1
2463 FROM cst_lc_adj_transactions t
2464 WHERE t.transaction_id = e.transaction_id);
2465
2466 IF (l_sLog AND l_pLog) THEN
2467 l_num_records := SQL%ROWCOUNT;
2471 l_num_records || ' Rows deleted'
2468 FND_LOG.STRING(
2469 FND_LOG.LEVEL_PROCEDURE,
2470 l_module || '.query(' || l_stmt_num || ')',
2472 );
2473
2474 END IF;
2475
2476 l_stmt_num := 100;
2477 IF (l_pLog) THEN
2478 FND_LOG.STRING(
2479 FND_LOG.LEVEL_PROCEDURE,
2480 l_module || '.end',
2481 '<< ' || l_api_name || ': Out Parameters:' ||
2482 ', x_return_status ' || x_return_status
2483 );
2484 END IF;
2485
2486 EXCEPTION
2487
2488 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2489 x_return_status := FND_API.g_ret_sts_error;
2490 IF (l_uLog) THEN
2491 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
2492 END IF;
2493
2494 WHEN FND_API.G_EXC_ERROR THEN
2495 x_return_status := FND_API.g_ret_sts_error;
2496 IF (l_exceptionLog) THEN
2497 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,l_module, TRUE);
2498 END IF;
2499
2500 WHEN OTHERS THEN
2501 x_return_status := FND_API.g_ret_sts_error;
2502 IF (l_uLog) THEN
2503 FND_MESSAGE.set_name('BOM', 'CST_UNEXP_ERROR');
2504 FND_MESSAGE.set_token('PACKAGE', G_PKG_NAME);
2505 FND_MESSAGE.set_token('PROCEDURE',l_api_name);
2506 FND_MESSAGE.set_token('STATEMENT',to_char(l_stmt_num));
2507 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
2508 FND_MSG_PUB.ADD;
2509
2510 FND_MESSAGE.SET_NAME('BOM','CST_LOG_UNEXPECTED');
2511 FND_MESSAGE.SET_TOKEN('SQLERRM',SQLERRM);
2512 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
2513 FND_MSG_PUB.ADD;
2514
2515 END IF;
2516
2517 END Insert_Adjustment_Data;
2518
2519 /*===========================================================================+
2520 | |
2521 | PROCEDURE : Validate_Lc_Interface |
2522 | |
2523 | DESCRIPTION : This procedure validates unvalidated interface records, |
2524 | populates details of failed validation into table |
2525 | CST_LC_ADJ_INTERFACE_ERRORS and updates the process_status|
2526 | of interface records to validated = 2 or errored = 3. |
2527 | |
2528 | |
2529 | |
2530 | CALLED FROM : Process_LcmAdjustments Procedure |
2531 | |
2532 | Parameters : |
2533 | IN : p_group_id IN NUMBER REQUIRED |
2534 | p_organization_id IN NUMBER REQUIRED |
2535 | p_api_version IN NUMBER REQUIRED |
2536 | p_init_msg_list IN VARCHAR2 REQUIRED |
2537 | p_validation_level IN NUMBER REQUIRED |
2538 | |
2539 | OUT : x_ledger_id OUT NOCOPY VARCHAR2 |
2540 | x_primary_cost_method OUT NOCOPY NUMBER |
2541 | x_primary_cost_method OUT NOCOPY VARCHAR2 |
2542 | x_return_status OUT NOCOPY NUMBER |
2543 | |
2544 | NOTES : None |
2545 | |
2546 | |
2547 +===========================================================================*/
2548
2549 PROCEDURE Validate_Lc_Interface
2550 (
2551 p_api_version IN NUMBER,
2552 p_init_msg_list IN VARCHAR2,
2553 p_validation_level IN NUMBER,
2554 p_group_id IN NUMBER,
2555 p_organization_id IN NUMBER,
2556 x_no_of_errored OUT NOCOPY NUMBER,
2557 x_return_status OUT NOCOPY VARCHAR2
2558 )
2559
2560 IS
2561 l_api_name CONSTANT VARCHAR2(30) :='Validate_Lc_Interface';
2562 l_api_version CONSTANT NUMBER := 1.0;
2563 l_return_status VARCHAR2(1);
2564 l_module CONSTANT VARCHAR2(100) := 'cst.plsql.'|| G_PKG_NAME || '.' || l_api_name;
2565
2566 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
2567 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
2568 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
2569 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
2570 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2571 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
2572
2573 l_stmt_num NUMBER;
2574 l_num_records NUMBER;
2575 l_msg_data VARCHAR2(240);
2576
2577 l_gl_info RCV_CreateAccounting_PVT.RCV_AE_GLINFO_REC_TYPE;
2578
2579 BEGIN
2580
2581 l_stmt_num := 0;
2582
2586 FND_LOG.LEVEL_PROCEDURE,
2583 /* Procedure level log message for Entry point */
2584 IF (l_pLog) THEN
2585 FND_LOG.STRING(
2587 l_module || '.begin',
2588 '>> ' || l_api_name || ': Parameters:' ||
2589 ' Api version ' || p_api_version ||
2590 ', Init msg list ' || p_init_msg_list ||
2591 ', Validation level ' || p_validation_level ||
2592 ', Group id ' || p_group_id
2593 );
2594 END IF;
2595
2596 /* Initialize API return status to success */
2597 x_return_status := FND_API.G_RET_STS_SUCCESS;
2598 l_return_status := FND_API.G_RET_STS_SUCCESS;
2599
2600 /* Standard call to check for call compatibility */
2601 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2602 p_api_version,
2603 l_api_name,
2604 G_PKG_NAME )
2605 THEN
2606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2607 END IF;
2608
2609 /* Initialize message list if p_init_msg_list is set to TRUE */
2610 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2611 FND_MSG_PUB.initialize;
2612 END IF;
2613
2614 l_stmt_num := 10;
2615
2616 /* Validating the lcm adjustments.
2617 A record will be created for each, lcm adjustment transaction and error combination in
2618 the cst_lc_adj_interface_errors table and the adjustment interface record will be
2619 set to error status */
2620 INSERT ALL
2621 WHEN new_landed_cost < 0 THEN
2622 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2623 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2624 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2625 VALUES (p_group_id, transaction_id, 'NEW_LANDED_COST', 'The column cannot have negative value',
2626 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2627 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2628 WHEN prior_landed_cost < 0 THEN
2629 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2630 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2631 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2632 VALUES (p_group_id, transaction_id, 'PRIOR_LANDED_COST', 'The column cannot have negative value',
2633 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2634 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2635 WHEN closing_status <> 'O' THEN
2636 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2637 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2638 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2639 VALUES (p_group_id, transaction_id, 'TRANSACTION_DATE', 'Purchasing Period is Closed or in the Future',
2640 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2641 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2642 WHEN open_flag = 'N' THEN
2643 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2644 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2645 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2646 VALUES (p_group_id, transaction_id, 'TRANSACTION_DATE', 'Inventory Period is Closed',
2647 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2648 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2649 WHEN rcv_transaction_id = -1 THEN
2650 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2651 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2652 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2653 VALUES (p_group_id, transaction_id, 'RCV_TRANSACTION_ID', 'Invalid Rcv Transaction',
2654 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2655 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2656 WHEN destination_type_code = 'EXPENSE' OR destination_type_code = 'SHOP FLOOR' THEN
2657 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2658 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2659 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2660 VALUES (p_group_id, transaction_id, 'RCV_TRANSACTION_ID',
2661 'Destination type is set to ' || destination_type_code, fnd_global.user_id, SYSDATE,
2662 fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id,
2663 fnd_global.user_id, fnd_global.conc_request_id)
2664 WHEN NOT (parent_transaction_id = -1 AND transaction_type IN ('RECEIVE', 'MATCH') ) THEN
2665 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2666 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2667 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2668 VALUES (p_group_id, transaction_id, 'RCV_TRANSACTION_ID', 'The receipt transaction is not the parent receipt',
2669 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2670 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2671 WHEN lcm_flag = 'N' THEN
2672 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2673 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2677 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2674 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2675 VALUES (p_group_id, transaction_id, 'RCV_TRANSACTION_ID', 'PO Shipment is not LCM Enabled',
2676 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2678 /* Bug 13960596 WHEN pol_item_id <> clai_inventory_item_id THEN */
2679 WHEN rcv_item_id <> clai_inventory_item_id THEN
2680 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2681 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2682 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2683 VALUES (p_group_id, transaction_id, 'INVENTORY_ITEM_ID',
2684 'Item Id in adjustment transaction and original receipt donot match', fnd_global.user_id, SYSDATE,
2685 fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id,
2686 fnd_global.user_id, fnd_global.conc_request_id)
2687 WHEN rt_organization_id <> clai_organization_id THEN
2688 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2689 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2690 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2691 VALUES (p_group_id, transaction_id, 'ORGANIZATION_ID', 'Organization Id in adjustment transaction
2692 and original receipt donot match', fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id,
2693 fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id, fnd_global.user_id,
2694 fnd_global.conc_request_id)
2695 WHEN lcm_enabled_flag = 'N' THEN
2696 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2697 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2698 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2699 VALUES (p_group_id, transaction_id, 'ORGANIZATION_ID', 'Organization is not LCM Enabled',
2700 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
2701 fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
2702 WHEN nvl(lcm_account_id, -1) = -1 THEN
2703 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2704 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2705 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2706 VALUES (p_group_id, transaction_id, 'ORGANIZATION_ID',
2707 'Landed cost absorption account is not defined for the organization', fnd_global.user_id, SYSDATE,
2708 fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id,
2709 fnd_global.user_id, fnd_global.conc_request_id)
2710 WHEN Decode(primary_cost_method, 1, 0, Nvl(lcm_var_account, -1)) = -1 THEN
2711 INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2712 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2713 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2714 VALUES (p_group_id, transaction_id, 'ORGANIZATION_ID',
2715 'Landed cost variance account is not defined for the organization', fnd_global.user_id, SYSDATE,
2716 fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id,
2717 fnd_global.user_id, fnd_global.conc_request_id)
2718 SELECT distinct clai.transaction_id, clai.rcv_transaction_id, new_landed_cost, prior_landed_cost,
2719 gps.closing_status, open_flag, Nvl(rt.transaction_id, -1) recv_transaction_id,
2720 pod.destination_type_code, parent_transaction_id, transaction_type, Nvl(POLL.lcm_flag, 'N') lcm_flag,
2721 /* Bug 13960596 pol.item_id pol_item_id, */
2722 rsl.item_id rcv_item_id,
2723 clai.inventory_item_id clai_inventory_item_id,
2724 Nvl(rt.organization_id, -1) rt_organization_id, clai.organization_id clai_organization_id,
2725 Nvl(lcm_enabled_flag, 'N') lcm_enabled_flag,
2726 mp.lcm_var_account, rp.lcm_account_id, mp.primary_cost_method,
2727 msi.inventory_item_id msi_item_id
2728 FROM org_acct_periods oap,
2729 cst_lc_adj_interface clai,
2730 rcv_transactions rt,
2731 rcv_accounting_events rae,
2732 /* Bug 13960596 po_lines_all pol, */
2733 rcv_shipment_lines rsl,
2734 po_line_locations_all poll,
2735 po_distributions_all pod,
2736 gl_period_statuses gps,
2737 cst_acct_info_v cai,
2738 mtl_parameters mp,
2739 mtl_system_items_b msi,
2740 rcv_parameters rp
2741 WHERE oap.organization_id (+) = clai.organization_id
2742 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) >= Trunc(oap.period_start_date)
2743 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) <= Trunc(oap.schedule_close_date)
2744 AND clai.group_id = p_group_id
2745 AND clai.process_status = 1
2746 AND clai.organization_id = p_organization_id
2747 AND rt.transaction_id (+) = clai.rcv_transaction_id
2748 AND rae.rcv_transaction_id (+) = clai.rcv_transaction_id
2749 AND rae.event_type_id in (1,4)
2750 AND rae.po_line_location_id = poll.line_location_id (+)
2751 AND rae.po_distribution_id = pod.po_distribution_id (+)
2752 /* Bug 13960596 AND rae.po_line_id = pol.po_line_id (+) */
2753 AND rt.shipment_line_id = rsl.shipment_line_id (+)
2754 AND cai.organization_id (+) = clai.organizatIon_id
2755 AND gps.ledger_id (+) = cai.ledger_id
2756 AND gps.application_id = G_PO_APPLICATION_ID
2757 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) >= Trunc(gps.start_date)
2758 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) <= Trunc(gps.end_date)
2759 AND gps.adjustment_period_flag <> 'Y'
2760 AND mp.organization_id (+) = clai.organization_id
2761 AND rp.organization_id (+) = clai.organization_id
2762 AND msi.inventory_item_id (+) = clai.inventory_item_id
2763 AND msi.organization_id (+) = clai.organization_id;
2764
2765 IF (l_sLog AND l_pLog) THEN
2766 l_num_records := SQL%ROWCOUNT;
2767 FND_LOG.STRING(
2768 FND_LOG.LEVEL_PROCEDURE,
2769 l_module || '.query(' || l_stmt_num || ')',
2770 l_num_records || ' Rows inserted'
2771 );
2772
2773 END IF;
2774
2775 l_stmt_num := 20;
2776 /* For already validated transactions only check if PO period is still open */
2777 INSERT INTO cst_lc_adj_interface_errors
2778 (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
2779 CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
2780 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
2781 SELECT p_group_id, transaction_id, 'TRANSACTION_DATE', 'Purchasing Period is Closed or in the Future',
2782 fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id,
2783 SYSDATE, SYSDATE, fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id
2784 FROM cst_lc_adj_interface clai,
2785 gl_period_statuses gps,
2786 cst_acct_info_v cai
2787 WHERE clai.group_id = p_group_id
2788 AND clai.process_status = 2
2789 AND clai.organization_id = p_organization_id
2790 AND cai.organization_id = p_organization_id
2791 AND gps.set_of_books_id = cai.ledger_id
2792 AND gps.application_id = G_PO_APPLICATION_ID
2793 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) >= Trunc(gps.start_date)
2794 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) <= Trunc(gps.end_date)
2795 AND gps.adjustment_period_flag <> 'Y'
2796 and gps.closing_status <> 'O';
2797
2798 IF (l_sLog AND l_pLog) THEN
2799 l_num_records := SQL%ROWCOUNT;
2800 FND_LOG.STRING(
2801 FND_LOG.LEVEL_PROCEDURE,
2802 l_module || '.query(' || l_stmt_num || ')',
2803 l_num_records || ' Rows inserted'
2804 );
2805
2806 END IF;
2807
2808 l_stmt_num := 30;
2809 /* Set the errored adjustment interface records for the group to ERROR status */
2810 UPDATE cst_lc_adj_interface i
2811 SET process_status = 3,
2812 group_id = NULL
2813 WHERE group_id = p_group_id
2814 AND process_status IN (1,2)
2815 AND organization_id = p_organization_id
2816 AND EXISTS (SELECT 1
2817 FROM cst_lc_adj_interface_errors e
2818 WHERE e.transaction_id = i.transaction_id
2819 AND e.group_id = p_group_id);
2820
2821 IF (l_sLog AND l_pLog) THEN
2822 l_num_records := SQL%ROWCOUNT;
2823 FND_LOG.STRING(
2824 FND_LOG.LEVEL_PROCEDURE,
2825 l_module || '.query(' || l_stmt_num || ')',
2826 l_num_records || ' Rows updated'
2827 );
2828
2829 END IF;
2830
2831 x_no_of_errored := SQL%ROWCOUNT;
2832
2833 l_stmt_num := 40;
2834 /* Set all unerrored adjustment interface records for the group to VALIDATED status */
2835 UPDATE cst_lc_adj_interface
2836 SET process_status = 2
2837 WHERE group_id = p_group_id
2838 AND organization_id = p_organization_id
2839 AND process_status = 1;
2840
2841 IF (l_sLog AND l_pLog) THEN
2842 l_num_records := SQL%ROWCOUNT;
2843 FND_LOG.STRING(
2844 FND_LOG.LEVEL_PROCEDURE,
2845 l_module || '.query(' || l_stmt_num || ')',
2846 l_num_records || ' Rows updated'
2847 );
2848
2849 END IF;
2850
2851 l_stmt_num := 50;
2852 IF (
2853 l_pLog) THEN
2854 FND_LOG.STRING(
2855 FND_LOG.LEVEL_PROCEDURE,
2856 l_module || '.end',
2857 '<< ' || l_api_name || ': Out Parameters:' ||
2858 ' x_no_of_errored ' || x_no_of_errored ||
2859 ', x_return_status ' || x_return_status
2860 );
2861 END IF;
2862
2863 EXCEPTION
2864
2865 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2866 x_return_status := FND_API.g_ret_sts_error;
2867 IF (l_uLog) THEN
2868 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
2869 END IF;
2870
2871 WHEN FND_API.G_EXC_ERROR THEN
2872 x_return_status := FND_API.g_ret_sts_error;
2873 IF (l_exceptionLog) THEN
2874 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,l_module, TRUE);
2875 END IF;
2876
2877 WHEN OTHERS THEN
2878 x_return_status := FND_API.g_ret_sts_error;
2879 IF (l_uLog) THEN
2880 FND_MESSAGE.set_name('BOM', 'CST_UNEXP_ERROR');
2881 FND_MESSAGE.set_token('PACKAGE', G_PKG_NAME);
2882 FND_MESSAGE.set_token('PROCEDURE',l_api_name);
2883 FND_MESSAGE.set_token('STATEMENT',to_char(l_stmt_num));
2884 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
2885 FND_MSG_PUB.ADD;
2886
2887 FND_MESSAGE.SET_NAME('BOM','CST_LOG_UNEXPECTED');
2888 FND_MESSAGE.SET_TOKEN('SQLERRM',SQLERRM);
2889 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
2890 FND_MSG_PUB.ADD;
2891
2892 END IF;
2893
2894 END Validate_Lc_Interface;
2895
2896 END CST_LcmAdjustments_PVT; -- end package body