[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
iv. Generate the average / layer cost update transactions.
- This update transactions would be created only for the
delivery transactions made against Asset items and subinventories.
- The average / layer cost update will be only done for the net quantity
(incoming quantity) that was delivered. The net quantity is
populated in the primary_quantity column of the transaction. Please
- The Average cost update transaction is created against the cost group
against which the original delivery was made. In case of wms-enabled
organizations, this could mean multiple cost groups for each parent receipt.
In such a case, multiple average cost update transactions will be created.
- The layer cost update is done against the layers that were hit by the
original delivery and the layer values are updated in proportion of the
delivered quantities against each layer.
- Create records into tables rcv_accounting_events, rcv_receiving_sub_ledger,
mtl_material_transactions, mtl_cst_txn_cost_details and XLA_EVENTS_INT_GT using
the global temporary tables.
------------------------------------------------------------------------------------------*/
G_PKG_NAME CONSTANT VARCHAR2(30) :='CST_LcmAdjustments_PVT';
SELECT Count(*)
INTO l_no_of_validated
FROM cst_lc_adj_interface
WHERE process_status = 2
AND organization_id = p_organization_id
AND group_id = p_group_id
AND ROWNUM = 1;
/* Insert the adjustment data from the GTTs into the actual tables */
Insert_Adjustment_Data
(p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_group_id => p_group_id,
p_organization_id => p_organization_id,
p_ledger_id => l_ledger_id,
x_return_status => l_return_status);
fnd_message.set_token('CALLED','Insert_Adjustment_Data');
select transaction_id, transaction_date, rcv_transaction_id
from cst_lc_adj_interface
where process_status = 2
and organization_id = p_organization_id
and group_id = p_group_id;
SELECT t.*, t.rowid
FROM cst_lc_accounts_gt t;
select rp.lcm_account_id,
mp.lcm_var_account,
rp.receiving_account_id,
mp.purchase_price_var_account,
mp.expense_account,
mp.primary_cost_method,
mp.wms_enabled_flag,
cai.legal_entity,
cai.ledger_id,
cai.operating_unit,
nvl(gsob.chart_of_accounts_id, 0),
fc.currency_code,
fc.minimum_accountable_unit,
fc.precision
into l_lcm_account,
l_lcm_var_account,
l_receiving_account,
l_purchase_price_var_account,
l_org_expense_account,
l_primary_cost_method,
l_wms_enabled_flag,
l_legal_entity_id,
l_ledger_id,
l_operating_unit,
l_chart_of_accounts_id,
l_currency_code,
l_minimum_accountable_unit,
l_precision
from rcv_parameters rp,
mtl_parameters mp,
cst_acct_info_v cai,
gl_sets_of_books gsob,
fnd_currencies fc
where rp.organization_id = p_organization_id
and mp.organization_id = p_organization_id
and cai.organization_id = p_organization_id
and gsob.set_of_books_id = cai.ledger_id
and fc.currency_code = gsob.currency_code;
/* Insert all the PO, accounting and Landed cost adjustment related information for
the lcm transaction. This will be used by all the events and accounting created
for this lcm transaction */
INSERT INTO CST_LC_ADJ_ACCTG_INFO_GT
(lcm_transaction_id,
rcv_transaction_id,
inventory_item_id,
prior_landed_cost,
new_landed_cost,
transaction_date,
accounting_date,
organization_id,
po_number,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
source_doc_unit_of_measure,
primary_unit_of_measure,
lcm_account,
lcm_var_account,
receiving_account,
purchase_price_var_account,
org_expense_account,
legal_entity_id,
ledger_id,
operating_unit,
chart_of_accounts_id,
func_currency_code,
func_minimum_accountable_unit,
func_precision,
period_name,
acct_period_id,
inventory_asset_flag
)
SELECT li.transaction_id,
li.rcv_transaction_id,
li.inventory_item_id,
li.prior_landed_cost,
li.new_landed_cost,
li.transaction_date,
INV_LE_TIMEZONE_PUB.Get_Le_Day_Time_For_Ou(
li.transaction_date,
l_operating_unit),
li.organization_id,
poh.segment1,
rt.po_header_id,
rt.po_release_id,
rt.po_line_id,
rt.po_line_location_id,
rt.source_doc_unit_of_measure,
msi.primary_unit_of_measure,
l_lcm_account,
l_lcm_var_account,
l_receiving_account,
l_purchase_price_var_account,
l_org_expense_account,
l_legal_entity_id,
l_ledger_id,
l_operating_unit,
l_chart_of_accounts_id,
l_currency_code,
l_minimum_accountable_unit,
l_precision,
gps.period_name,
oap.acct_period_id,
msi.inventory_asset_flag
FROM cst_lc_adj_interface li,
gl_period_statuses gps,
mtl_system_items_b msi,
po_headers_all poh,
org_acct_periods oap,
rcv_transactions rt
WHERE li.group_id = p_group_id
AND li.organization_id = p_organization_id
AND li.process_status = 2
AND gps.application_id = G_PO_APPLICATION_ID
AND gps.set_of_books_id = l_ledger_id
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(li.transaction_date, l_legal_entity_id) >= Trunc(gps.start_date)
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(li.transaction_date, l_legal_entity_id) <= Trunc(gps.end_date)
AND gps.adjustment_period_flag <> 'Y'
AND msi.inventory_item_id = li.inventory_item_id
AND msi.organization_id = li.organization_id
AND rt.transaction_id = li.rcv_transaction_id
AND poh.po_header_id = rt.po_header_id
AND oap.organization_id = li.organization_id
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(li.transaction_date, l_legal_entity_id) >= Trunc(oap.period_start_date)
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(li.transaction_date, l_legal_entity_id) <= Trunc(oap.schedule_close_date);
l_num_records || ' Rows inserted'
);
/* Insert all the receiving side transactions that have the receipt transaction
corresponding to the lcm adjustment as their root. */
for c_lt in c_lcm_txns (p_group_id) loop
l_stmt_num := 50;
INSERT INTO CST_LC_RCV_TXN_GT
(group_id, lcm_transaction_id,
rcv_transaction_id,
parent_transaction_id,
accounting_event_id,
transaction_type,
source_doc_quantity,
primary_quantity,
subinventory_code,
po_distribution_id
)
SELECT p_group_id, -1 * c_lt.transaction_id,
transaction_id,
parent_transaction_id,
NULL, /* accounting_event_id*/
transaction_type,
rt.source_doc_quantity,
rt.primary_quantity,
rt.subinventory,
rt.po_distribution_id
FROM rcv_transactions rt
WHERE rt.transaction_type IN ('RECEIVE', 'MATCH', 'RETURN TO VENDOR', 'CORRECT', 'DELIVER', 'RETURN TO RECEIVING')
AND rt.transaction_date < c_lt.transaction_date
START WITH rt.transaction_id = c_lt.rcv_transaction_id
CONNECT BY rt.parent_transaction_id = PRIOR rt.transaction_id;
l_num_records || ' Rows inserted'
);
INSERT INTO CST_LC_RCV_TXN_GT
(group_id,
lcm_transaction_id,
accounting_event_id,
rcv_transaction_id,
parent_transaction_id,
po_distribution_id,
source_doc_quantity,
primary_quantity,
transaction_type,
parent_transaction_type,
subinventory_code
)
SELECT rt.group_id,
-1 * rt.lcm_transaction_id,
rae.accounting_event_id,
rt.rcv_transaction_id,
NULL, /* parent_transaction_id */
nvl(rae.po_distribution_id, rt.po_distribution_id),
nvl(rae.source_doc_quantity, rt.source_doc_quantity),
nvl(rae.primary_quantity,rt.primary_quantity),
rt.transaction_type,
Decode(rt.transaction_type, 'CORRECT', rt_parent.transaction_type, rt.transaction_type) parent_transaction_type,
rt.subinventory_code
FROM rcv_transactions rt_parent,
rcv_accounting_events rae,
CST_LC_RCV_TXN_GT rt
WHERE rae.rcv_transaction_id (+) = rt.rcv_transaction_id
AND Nvl(rae.event_type_id,2) in (1,2,3,4,5,6)
AND rt_parent.transaction_id (+) = rt.parent_transaction_id;
l_num_records || ' Rows inserted'
);
DELETE FROM CST_LC_RCV_TXN_GT
WHERE lcm_transaction_id < 0;
l_num_records || ' Rows Deleted'
);
INSERT INTO CST_LC_ACCOUNTS_GT
(LCM_TRANSACTION_ID,
RCV_TRANSACTION_ID,
TRANSACTION_ID,
COST_GROUP_ID,
SUBINVENTORY_CODE,
ACCOUNT,
ACCOUNTING_LINE_TYPE_ID,
ACCOUNTING_LINE_TYPE,
DEBIT_CREDIT_FLAG,
EXP_FLAG
)
SELECT rt.lcm_transaction_id,
rt.rcv_transaction_id,
Max(mmt.transaction_id),
Decode(l_wms_enabled_flag, 'Y', mmt.cost_group_id, NULL) cost_group_id,
rt.subinventory_code,
Decode( /* Derive Scenarios */
Decode(l_primary_cost_method, 1,
Decode(l_wms_enabled_flag, 'N',
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
1,
2), 2),
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
3,
4), 4)),
Decode(l_wms_enabled_flag, 'N',
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
5,
6), 6),
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
7,
8), 8))),
1,
acc.purchase_price_var_account,
2,
mse.expense_account,
3,
Decode(mmt.cost_group_id, 1, acc.purchase_price_var_account, ccga.purchase_price_var_account),
4,
Decode(mmt.cost_group_id, 1, nvl(mse.expense_account, acc.org_expense_account), ccga.expense_account),
5,
acc.lcm_account,
6,
nvl(mse.expense_account, acc.org_expense_account),
7,
acc.lcm_account,
8,
Decode(mmt.cost_group_id, 1, nvl(mse.expense_account, acc.org_expense_account), ccga.expense_account)) account,
Decode( /* Derive Scenarios */
Decode(l_primary_cost_method, 1,
Decode(l_wms_enabled_flag, 'N',
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
1,
2), 2),
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
3,
4), 4)),
Decode(l_wms_enabled_flag, 'N',
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
5,
6), 6),
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
7,
8), 8))),
1,
6,
2,
2,
3,
6,
4,
2,
5,
38,
6,
2,
7,
38,
8,
2) account_line_type_id,
Decode( /* Derive Scenarios */
Decode(l_primary_cost_method, 1,
Decode(l_wms_enabled_flag, 'N',
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
1,
2), 2),
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
3,
4), 4)),
Decode(l_wms_enabled_flag, 'N',
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
5,
6), 6),
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
7,
8), 8))),
1,
'Purchase Price Variance',
2,
'Expense',
3,
'Purchase Price Variance',
4,
'Expense',
5,
'Landed Cost Absorption',
6,
'Expense',
7,
'Landed Cost Absorption',
8,
'Expense') account_line_type,
Decode(sign(acc.new_landed_cost-acc.prior_landed_cost), 1, 1, -1) debit_credit_flag,
Decode(acc.inventory_asset_flag, 'Y', Decode(mse.asset_inventory, 1,
0,
1), 1) exp_flag
FROM mtl_material_transactions mmt,
cst_lc_rcv_txn_gt rt,
cst_cost_group_accounts ccga,
cst_lc_adj_acctg_info_gt acc,
mtl_secondary_inventories mse
WHERE mmt.transaction_source_type_id = 1
AND mmt.organization_id = p_organization_id
AND mmt.rcv_transaction_id = rt.rcv_transaction_id
AND mse.organization_id = p_organization_id
AND mse.secondary_inventory_name = rt.subinventory_code
AND rt.transaction_type IN ('DELIVER', 'RETURN TO RECEIVING', 'CORRECT')
AND rt.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
AND ccga.cost_group_id (+) = mmt.cost_group_id
AND rt.lcm_transaction_id = acc.lcm_transaction_id
GROUP BY mmt.cost_group_id,
rt.subinventory_code,
rt.lcm_transaction_id,
rt.rcv_transaction_id,
acc.inventory_asset_flag,
mse.asset_inventory,
acc.purchase_price_var_account,
mmt.cost_group_id,
ccga.purchase_price_var_account,
mse.expense_account,
acc.org_expense_account,
ccga.expense_account,
acc.lcm_account,
acc.new_landed_cost,
acc.prior_landed_cost;
l_num_records || ' Rows updated'
);
INSERT INTO cst_lc_adj_interface_errors
(GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, c_account.lcm_transaction_id, 'ACCOUNT', l_error_msg,
fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id,
SYSDATE, SYSDATE, fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id);
l_num_records || ' Rows inserted'
);
UPDATE cst_lc_accounts_gt
SET account = l_acct_id
WHERE rowid = c_account.rowid;
UPDATE cst_lc_adj_interface i
SET process_status = 3,
group_id = NULL
WHERE group_id = p_group_id
AND process_status IN (1,2)
AND organization_id = p_organization_id
AND EXISTS (SELECT 1
FROM cst_lc_adj_interface_errors e
WHERE e.transaction_id = i.transaction_id
AND e.group_id = p_group_id
AND error_column = 'ACCOUNT');
l_num_records || ' Rows updated'
);
DELETE FROM cst_lc_accounts_gt t
WHERE EXISTS (SELECT 1
FROM cst_lc_adj_interface_errors e
WHERE e.transaction_id = t.lcm_transaction_id
AND e.group_id = p_group_id
AND error_column = 'ACCOUNT');
l_num_records || ' Rows deleted'
);
DELETE FROM cst_lc_rcv_txn_gt t
WHERE EXISTS (SELECT 1
FROM cst_lc_adj_interface_errors e
WHERE e.transaction_id = t.lcm_transaction_id
AND e.group_id = p_group_id
AND error_column = 'ACCOUNT');
l_num_records || ' Rows deleted'
);
DELETE FROM cst_lc_adj_acctg_info_gt t
WHERE EXISTS (SELECT 1
FROM cst_lc_adj_interface_errors e
WHERE e.transaction_id = t.lcm_transaction_id
AND e.group_id = p_group_id
AND error_column = 'ACCOUNT');
l_num_records || ' Rows deleted'
);
| and layer cost update data in global temporary tables. |
| |
| |
| |
| |
| |
| CALLED FROM : Process_LcmAdjustments Procedure |
| |
| Parameters : |
| IN : p_primary_cost_method IN NUMBER REQUIRED |
| p_wms_enabled_flag IN NUMBER REQUIRED |
| p_api_version IN NUMBER REQUIRED |
| p_init_msg_list IN VARCHAR2 REQUIRED |
| p_validation_level IN NUMBER REQUIRED |
| |
| OUT : x_return_status OUT NOCOPY NUMBER |
| |
| NOTES : None |
| |
| |
+===========================================================================*/
PROCEDURE Populate_Temp_Adjustment_Data
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_primary_cost_method IN NUMBER,
p_wms_enabled_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) :='Populate_Temp_Adjustment_Data';
INSERT INTO CST_LC_RAE_EVENTS_GT
(LCM_TRANSACTION_ID,
ACCOUNTING_EVENT_ID,
EVENT_SOURCE,
EVENT_SOURCE_ID,
EVENT_TYPE_ID,
RCV_TRANSACTION_ID,
INVENTORY_ITEM_ID,
PRIOR_UNIT_PRICE,
UNIT_PRICE,
transaction_date,
organization_id,
ORG_ID,
SET_OF_BOOKS_ID,
PO_HEADER_ID,
PO_RELEASE_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
CURRENCY_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE,
SOURCE_DOC_UNIT_OF_MEASURE,
TRANSACTION_UNIT_OF_MEASURE,
PRIMARY_UNIT_OF_MEASURE,
source_doc_quantity,
TRANSACTION_quantity,
primary_quantity,
CREDIT_ACCOUNT_ID,
DEBIT_ACCOUNT_ID,
CREDIT_ACCOUNTING_LINE_TYPE,
DEBIT_ACCOUNTING_LINE_TYPE,
/* who columns */
CREATED_BY,
CREATION_DATE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
REQUEST_ID,
ACCOUNTED_FLAG)
SELECT acc.lcm_transaction_id,
NULL,
'LC_ADJUSTMENTS',
acc.lcm_transaction_id,
15,
acc.rcv_transaction_id,
acc.inventory_item_id,
acc.prior_landed_cost,
acc.new_landed_cost,
acc.transaction_date,
acc.organization_id,
acc.operating_unit,
acc.ledger_id,
acc.po_header_id,
acc.po_release_id,
acc.po_line_id,
acc.po_line_location_id,
rt.po_distribution_id,
acc.func_currency_code,
NULL,
1,
acc.transaction_date,
acc.source_doc_unit_of_measure,
acc.primary_unit_of_measure,
acc.primary_unit_of_measure,
sum(Decode(rt.transaction_type,
'RECEIVE', 1,
'MATCH', 1,
'RETURN TO VENDOR', -1,
'CORRECT', Decode(rt.parent_transaction_type,
'RECEIVE', 1,
'MATCH', 1,
'RETURN TO VENDOR', -1,
0)) * rt.source_doc_quantity) source_doc_quantity,
sum(Decode(rt.transaction_type,
'RECEIVE', 1,
'MATCH', 1,
'RETURN TO VENDOR', -1,
'CORRECT', Decode(rt.parent_transaction_type,
'RECEIVE', 1,
'MATCH', 1,
'RETURN TO VENDOR', -1,
0)) * rt.primary_quantity) transaction_quantity,
sum(Decode(rt.transaction_type,
'RECEIVE', 1,
'MATCH', 1,
'RETURN TO VENDOR', -1,
'CORRECT', Decode(rt.parent_transaction_type,
'RECEIVE', 1,
'MATCH', 1,
'RETURN TO VENDOR', -1,
0)) * rt.primary_quantity) primary_quantity,
Decode(SIGN(acc.new_landed_cost-acc.prior_landed_cost), -1, acc.receiving_account, acc.lcm_account) CREDIT_ACCOUNT_ID,
Decode(SIGN(acc.new_landed_cost-acc.prior_landed_cost), 1, acc.receiving_account, acc.lcm_account) DEBIT_ACCOUNT_ID,
Decode(SIGN(acc.new_landed_cost-acc.prior_landed_cost), -1, 'Receiving Inspection', 'Landed Cost Absorption') CREDIT_ACCOUNTING_LINE_TYPE,
Decode(SIGN(acc.new_landed_cost-acc.prior_landed_cost), 1, 'Receiving Inspection', 'Landed Cost Absorption') DEBIT_ACCOUNTING_LINE_TYPE,
fnd_global.user_id,
SYSDATE,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
fnd_global.conc_request_id,
'N'
FROM CST_LC_ADJ_ACCTG_INFO_GT ACC,
CST_LC_RCV_TXN_GT RT
WHERE acc.lcm_transaction_id = RT.lcm_transaction_id
AND (rt.transaction_type IN ('RECEIVE', 'MATCH', 'RETURN TO VENDOR')
OR (rt.transaction_type = 'CORRECT'
AND rt.parent_transaction_type IN ('RECEIVE', 'MATCH', 'RETURN TO VENDOR')))
GROUP BY acc.lcm_transaction_id,
ACC.rcv_transaction_id,
inventory_item_id,
prior_landed_cost,
new_landed_cost,
transaction_date,
organization_id,
operating_unit,
ledger_id,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
func_currency_code,
source_doc_unit_of_measure,
primary_unit_of_measure,
receiving_account,
LCM_account
HAVING sum(Decode(rt.transaction_type,
'RECEIVE', 1,
'MATCH', 1,
'RETURN TO VENDOR', -1,
'CORRECT', Decode(rt.parent_transaction_type,
'RECEIVE', 1,
'MATCH', 1,
'RETURN TO VENDOR', -1,
0)) * rt.source_doc_quantity) <> 0;
l_num_records || ' Rows inserted'
);
INSERT INTO CST_LC_RAE_EVENTS_GT
(LCM_TRANSACTION_ID,
ACCOUNTING_EVENT_ID,
EVENT_SOURCE,
EVENT_SOURCE_ID,
EVENT_TYPE_ID,
RCV_TRANSACTION_ID,
INVENTORY_ITEM_ID,
PRIOR_UNIT_PRICE,
UNIT_PRICE,
transaction_date,
organization_id,
ORG_ID,
SET_OF_BOOKS_ID,
PO_HEADER_ID,
PO_RELEASE_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
CURRENCY_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE,
SOURCE_DOC_UNIT_OF_MEASURE,
TRANSACTION_UNIT_OF_MEASURE,
PRIMARY_UNIT_OF_MEASURE,
source_doc_quantity,
TRANSACTION_quantity,
primary_quantity,
CREDIT_ACCOUNT_ID,
DEBIT_ACCOUNT_ID,
CREDIT_ACCOUNTING_LINE_TYPE,
DEBIT_ACCOUNTING_LINE_TYPE,
/* who columns */
CREATED_BY,
CREATION_DATE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
REQUEST_ID,
ACCOUNTED_FLAG)
SELECT acc.lcm_transaction_id,
NULL,
'LC_ADJUSTMENTS',
acc.lcm_transaction_id,
Decode(adj_acc.exp_flag,
1, 17,
16),
acc.rcv_transaction_id,
acc.inventory_item_id,
acc.prior_landed_cost,
acc.new_landed_cost,
acc.transaction_date,
acc.organization_id,
acc.operating_unit,
acc.ledger_id,
acc.PO_HEADER_ID,
acc.PO_RELEASE_ID,
acc.PO_LINE_ID,
acc.PO_LINE_LOCATION_ID,
rt.PO_DISTRIBUTION_ID,
acc.func_currency_code,
NULL,
1,
acc.transaction_date,
acc.SOURCE_DOC_UNIT_OF_MEASURE,
acc.PRIMARY_UNIT_OF_MEASURE,
acc.PRIMARY_UNIT_OF_MEASURE,
sum(Decode(transaction_type,
'DELIVER', 1,
'RETURN TO RECEIVING', -1,
'CORRECT', Decode(parent_transaction_type,
'DELIVER', 1,
'RETURN TO RECEIVING', -1,
0)) * source_doc_quantity) source_doc_quantity,
sum(Decode(transaction_type,
'DELIVER', 1,
'RETURN TO RECEIVING', -1,
'CORRECT', Decode(parent_transaction_type,
'DELIVER', 1,
'RETURN TO RECEIVING', -1,
0)) * primary_quantity) transaction_quantity,
sum(Decode(transaction_type,
'DELIVER', 1,
'RETURN TO RECEIVING', -1,
'CORRECT', Decode(parent_transaction_type,
'DELIVER', 1,
'RETURN TO RECEIVING', -1,
0)) * primary_quantity) primary_quantity,
Decode(adj_acc.debit_credit_flag, 1, acc.receiving_account, adj_acc.account) CREDIT_ACCOUNT_ID,
Decode(adj_acc.debit_credit_flag, -1, acc.receiving_account, adj_acc.account) DEBIT_ACCOUNT_ID,
Decode(adj_acc.debit_credit_flag, 1, 'Receiving Inspection', adj_acc.accounting_line_type) CREDIT_ACCOUNTING_LINE_TYPE,
Decode(adj_acc.debit_credit_flag, -1, 'Receiving Inspection', adj_acc.accounting_line_type) DEBIT_ACCOUNTING_LINE_TYPE,
fnd_global.user_id,
SYSDATE,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
fnd_global.conc_request_id,
'N'
FROM cst_lc_adj_acctg_info_gt acc,
cst_lc_rcv_txn_gt rt,
cst_lc_accounts_gt adj_acc
WHERE (rt.transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
or (rt.transaction_type = 'CORRECT'
and rt.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')))
and rt.lcm_transaction_id = acc.lcm_transaction_id
and adj_acc.lcm_transaction_id = acc.lcm_transaction_id
GROUP BY acc.lcm_transaction_id,
acc.rcv_transaction_id,
inventory_item_id,
prior_landed_cost,
new_landed_cost,
transaction_date,
acc.organization_id,
operating_unit,
ledger_id,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
func_currency_code,
source_doc_unit_of_measure,
primary_unit_of_measure,
receiving_account,
adj_acc.account,
adj_acc.exp_flag,
adj_acc.debit_credit_flag,
adj_acc.accounting_line_type
HAVING sum(Decode(transaction_type,
'DELIVER', 1,
'RETURN TO RECEIVING', -1,
'CORRECT', Decode(parent_transaction_type,
'DELIVER', 1,
'RETURN TO RECEIVING', -1,
0)) * source_doc_quantity) <> 0;
l_num_records || ' Rows inserted'
);
UPDATE CST_LC_RAE_EVENTS_GT
SET accounting_event_id = rcv_accounting_event_s.NEXTVAL;
/* Insert Average cost update transactions for the deliveries of
asset items into asset organizations */
INSERT INTO CST_LC_MMT_GT
(lcm_transaction_id,
transaction_id,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
cost_group_id,
primary_quantity,
value_change,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inventory_item_id,
organization_id,
transaction_date,
acct_period_id,
transaction_source_name,
source_code,
transaction_reference,
trx_source_line_id,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
overhead_account,
expense_account_id,
costed_flag,
pm_cost_collected,
owning_organization_id,
owning_tp_type,
transaction_uom)
SELECT
acc.lcm_transaction_id,
NULL,
80,
24,
13,
mmt.cost_group_id,
sum(mmt.primary_quantity),
sum(mmt.primary_quantity) * (acc.new_landed_cost - acc.prior_landed_cost),
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE,
acc.inventory_item_id,
acc.organization_id,
acc.transaction_date,
acc.acct_period_id,
'LCM ADJUSTMENT',
'LCMADJ',
acc.lcm_transaction_id,
acc.rcv_transaction_id,
acc.lcm_account,
acc.lcm_account,
acc.lcm_account,
acc.lcm_account,
acc.lcm_account,
acc.lcm_var_account,
'N',
NULL,
acc.organization_id,
2,
msi.primary_uom_code
FROM cst_lc_adj_acctg_info_gt acc,
cst_lc_rcv_txn_gt rt,
mtl_secondary_inventories mse,
mtl_material_transactions mmt,
mtl_system_items_b msi
WHERE (rt.transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
or (rt.transaction_type = 'CORRECT'
and rt.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')))
and rt.lcm_transaction_id = acc.lcm_transaction_id
AND mse.secondary_inventory_name = rt.subinventory_code
and mse.organization_id = acc.organization_id
and mmt.rcv_transaction_id = rt.rcv_transaction_id
AND (acc.inventory_asset_flag = 'Y' AND Nvl(mse.asset_inventory,2) = 1)
AND msi.inventory_item_id = acc.inventory_item_id
AND msi.organization_id= acc.organization_id
GROUP BY
mmt.cost_group_id,
acc.inventory_item_id,
acc.organization_id,
acc.transaction_date,
acc.acct_period_id,
acc.rcv_transaction_id,
acc.lcm_account,
acc.lcm_transaction_id,
acc.lcm_var_account,
acc.new_landed_cost,
acc.prior_landed_cost,
msi.primary_uom_code
HAVING sum(mmt.primary_quantity) <> 0;
l_num_records || ' Rows inserted'
);
UPDATE CST_LC_MMT_GT
SET transaction_id = MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL;
l_num_records || ' Rows updated'
);
/* Insert Layer cost update transactions for the deliveries of
asset items into asset organizations */
INSERT INTO CST_LC_MMT_GT
(lcm_transaction_id,
transaction_id,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_source_id,
cost_group_id,
primary_quantity,
value_change,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inventory_item_id,
organization_id,
transaction_date,
acct_period_id,
transaction_source_name,
source_code,
transaction_reference,
trx_source_line_id,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
overhead_account,
expense_account_id,
costed_flag,
pm_cost_collected,
owning_organization_id,
owning_tp_type,
transaction_uom)
SELECT txn.lcm_transaction_id,
NULL,
28,
24,
15,
inv_layer_id,
txn.cost_group_id,
sum(txn.layer_quantity),
txn.value_change,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE,
txn.inventory_item_id,
txn.organization_id,
txn.transaction_date,
txn.acct_period_id,
'LCM ADJUSTMENT',
'LCMADJ',
txn.lcm_transaction_id,
txn.rcv_transaction_id,
txn.lcm_account,
txn.lcm_account,
txn.lcm_account,
txn.lcm_account,
txn.lcm_account,
txn.lcm_var_account,
'N',
NULL,
txn.organization_id,
2,
msi.primary_uom_code
FROM (SELECT DISTINCT acc.lcm_transaction_id,
mmt.transaction_id,
mclacd.inv_layer_id,
mclacd.layer_quantity,
mmt.cost_group_id,
acc.inventory_item_id,
acc.organization_id,
acc.transaction_date,
acc.acct_period_id,
acc.rcv_transaction_id,
acc.lcm_account,
acc.lcm_var_account,
(layer_quantity * (acc.new_landed_cost - acc.prior_landed_cost)) value_change
FROM mtl_material_transactions mmt,
mtl_cst_layer_act_cost_details mclacd,
cst_lc_adj_acctg_info_gt acc,
cst_lc_rcv_txn_gt rt,
mtl_secondary_inventories mse
WHERE (rt.transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
or (rt.transaction_type = 'CORRECT'
and rt.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')))
and rt.lcm_transaction_id = acc.lcm_transaction_id
AND mse.secondary_inventory_name = rt.subinventory_code
and mse.organization_id = acc.organization_id
and mmt.rcv_transaction_id = rt.rcv_transaction_id
and mmt.transaction_id = mclacd.transaction_id
AND (acc.inventory_asset_flag = 'Y' AND Nvl(mse.asset_inventory,2) = 1)) txn,
mtl_system_items_b msi
WHERE msi.inventory_item_id = txn.inventory_item_id
AND msi.organization_id= txn.organization_id
GROUP BY txn.lcm_transaction_id, txn.COST_GROUP_ID,
txn.INV_LAYER_ID,
txn.cost_group_id,
txn.inventory_item_id,
txn.organization_id,
txn.transaction_date,
txn.acct_period_id,
txn.rcv_transaction_id,
txn.value_change,
txn.lcm_account,
txn.lcm_var_account,
msi.primary_uom_code
HAVING sum(txn.layer_quantity) <> 0;
l_num_records || ' Rows inserted'
);
UPDATE CST_LC_MMT_GT
SET transaction_id = MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL;
l_num_records || ' Rows updated'
);
| PROCEDURE : Insert_Adjustment_Data |
| |
| DESCRIPTION : This procedure inserts the receiving events data and the |
| average and layer cost update data from temporary tables |
| into RCV_ACCOUNTING_EVENTS and MTL_MATERIAL_TRANSACTIONS. |
| Accounting and other entries are also calculated and |
| inserted into RCV_RECEIVING_SUBLEDGER, XLA_EVENTS_INT_GT |
| and MTL_CST_TXN_COST_DETAILS. |
| |
| CALLED FROM : Process_LcmAdjustments Procedure |
| |
| Parameters : |
| IN : p_group_id IN NUMBER REQUIRED |
| p_organization_id IN NUMBER REQUIRED |
| p_ledger_id IN NUMBER REQUIRED |
| p_api_version IN NUMBER REQUIRED |
| p_init_msg_list IN VARCHAR2 REQUIRED |
| p_validation_level IN NUMBER REQUIRED |
| |
| OUT : x_return_status OUT NOCOPY NUMBER |
| |
| NOTES : None |
| |
| |
+===========================================================================*/
PROCEDURE Insert_Adjustment_Data
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_group_id IN NUMBER,
p_organization_id IN NUMBER,
p_ledger_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) :='Insert_Adjustment_Data';
INSERT INTO rcv_accounting_events
(accounting_event_id,
event_source,
event_source_id,
event_type_id,
rcv_transaction_id,
inventory_item_id,
prior_unit_price,
unit_price,
transaction_date,
organization_id,
org_id,
set_of_books_id,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
source_doc_unit_of_measure,
transaction_unit_of_measure,
primary_unit_of_measure,
source_doc_quantity,
transaction_quantity,
primary_quantity,
credit_account_id,
debit_account_id,
/* who columns */
created_by,
creation_date,
program_application_id,
program_id,
program_udpate_date,
last_update_date,
last_update_login,
last_updated_by,
request_id,
accounted_flag)
SELECT accounting_event_id,
event_source,
event_source_id,
event_type_id,
rcv_transaction_id,
inventory_item_id,
prior_unit_price,
unit_price,
transaction_date,
organization_id,
org_id,
set_of_books_id,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
source_doc_unit_of_measure,
transaction_unit_of_measure,
primary_unit_of_measure,
source_doc_quantity,
transaction_quantity,
primary_quantity,
credit_account_id,
debit_account_id,
/* who columns */
created_by,
creation_date,
program_application_id,
program_id,
program_update_date,
last_update_date,
last_update_login,
last_updated_by,
request_id,
accounted_flag
FROM cst_lc_rae_events_gt;
l_num_records || ' Rows inserted'
);
INSERT INTO rcv_receiving_sub_ledger
(created_by,
creation_date,
program_application_id,
program_id,
program_update_date,
last_update_date,
last_update_login,
last_updated_by,
request_id,
rcv_sub_ledger_id,
accounting_event_id,
accounting_line_type,
rcv_transaction_id,
actual_flag,
je_source_name,
je_category_name,
set_of_books_id,
accounting_date,
code_combination_id,
entered_dr,
accounted_dr,
currency_code,
user_currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
transaction_date,
period_name,
chart_of_accounts_id,
functional_currency_code,
reference1,
reference2,
reference3,
reference4,
source_doc_quantity,
accrual_method_flag,
accounted_nr_tax,
accounted_rec_tax,
entered_nr_tax,
entered_rec_tax
)
SELECT
fnd_global.user_id,
SYSDATE,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
fnd_global.conc_request_id,
rcv_receiving_sub_ledger_s.nextval,
rae.accounting_event_id,
rae.debit_accounting_line_type,
rae.rcv_transaction_id,
'A',
'Purchasing',
'Receiving',
rae.set_of_books_id,
Trunc(acc.accounting_date),
rae.debit_account_id,
/* ENTERED */
Decode(func_minimum_accountable_unit
, NULL, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price)), func_precision)
, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price))/ func_minimum_accountable_unit) * func_minimum_accountable_unit),
/* ACCOUNTED */
Decode(func_minimum_accountable_unit
, NULL, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price)), func_precision)
, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price))/ func_minimum_accountable_unit) * func_minimum_accountable_unit),
acc.func_currency_code,
NULL,
1,
acc.transaction_date,
acc.transaction_date,
acc.period_name,
acc.chart_of_accounts_id,
acc.func_currency_code,
'PO',
rae.po_header_id,
rae.po_distribution_id,
acc.po_number,
rae.source_doc_quantity,
'O',
0,
0,
0,
0
FROM cst_lc_rae_events_gt rae,
cst_lc_adj_acctg_info_gt acc
WHERE rae.lcm_transaction_id = acc.lcm_transaction_id;
l_num_records || ' Rows inserted'
);
INSERT INTO rcv_receiving_sub_ledger
(created_by,
creation_date,
program_application_id,
program_id,
program_update_date,
last_update_date,
last_update_login,
last_updated_by,
request_id,
rcv_sub_ledger_id,
accounting_event_id,
accounting_line_type,
rcv_transaction_id,
actual_flag,
je_source_name,
je_category_name,
set_of_books_id,
accounting_date,
code_combination_id,
accounted_cr,
entered_cr,
currency_code,
user_currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
transaction_date,
period_name,
chart_of_accounts_id,
functional_currency_code,
reference1,
reference2,
reference3,
reference4,
source_doc_quantity,
accrual_method_flag,
accounted_nr_tax,
accounted_rec_tax,
entered_nr_tax,
entered_rec_tax)
SELECT
fnd_global.user_id,
SYSDATE,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
fnd_global.conc_request_id,
rcv_receiving_sub_ledger_s.NEXTVAL,
rae.accounting_event_id,
rae.credit_accounting_line_type,
rae.rcv_transaction_id,
'A',
'Purchasing',
'Receiving',
rae.set_of_books_id,
Trunc(acc.accounting_date),
rae.credit_account_id,
/* ENTERED */
Decode(func_minimum_accountable_unit
, NULL, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price)), func_precision)
, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price))/ func_minimum_accountable_unit) * func_minimum_accountable_unit),
/* ACCOUNTED */
Decode(func_minimum_accountable_unit
, NULL, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price)), func_precision)
, Round(ABS(rae.primary_quantity * (rae.unit_price - rae.prior_unit_price))/ func_minimum_accountable_unit) * func_minimum_accountable_unit),
acc.func_currency_code,
NULL,
1,
acc.transaction_date,
acc.transaction_date,
acc.period_name,
acc.chart_of_accounts_id,
acc.func_currency_code,
'PO',
rae.po_header_id,
rae.po_distribution_id,
acc.po_number,
rae.source_doc_quantity,
'O',
0,
0,
0,
0
FROM cst_lc_rae_events_gt rae,
cst_lc_adj_acctg_info_gt acc
WHERE rae.lcm_transaction_id = acc.lcm_transaction_id;
l_num_records || ' Rows inserted'
);
/* Create the transaction cost details for the average /layer cost update transactions */
INSERT INTO MTL_CST_TXN_COST_DETAILS
(transaction_id,
organization_id,
inventory_item_id,
cost_element_id,
level_type,
value_change,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT
mmt.transaction_id,
mmt.organization_id,
mmt.inventory_item_id,
1,
1,
mmt.primary_quantity * (acc.new_landed_cost - acc.prior_landed_cost),
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE
FROM cst_lc_mmt_gt mmt,
cst_lc_adj_acctg_info_gt acc
WHERE mmt.lcm_transaction_id = acc.lcm_transaction_id;
l_num_records || ' Rows inserted'
);
/* Create the the average / layer cost update transactions */
INSERT INTO MTL_MATERIAL_TRANSACTIONS
(transaction_id,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_source_id,
cost_group_id,
primary_quantity,
transaction_quantity,
value_change,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inventory_item_id,
organization_id,
transaction_date,
acct_period_id,
transaction_source_name,
source_code,
trx_source_line_id,
transaction_reference,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
overhead_account,
expense_account_id,
costed_flag,
pm_cost_collected,
owning_organization_id,
owning_tp_type,
transaction_uom)
SELECT
transaction_id,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_source_id,
cost_group_id,
primary_quantity,
primary_quantity,
value_change,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inventory_item_id,
organization_id,
transaction_date,
acct_period_id,
transaction_source_name,
source_code,
trx_source_line_id,
transaction_reference,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
overhead_account,
expense_account_id,
costed_flag,
pm_cost_collected,
owning_organization_id,
owning_tp_type,
transaction_uom
FROM CST_LC_MMT_GT;
l_num_records || ' Rows inserted'
);
INSERT INTO XLA_EVENTS_INT_GT
(application_id,
ledger_id,
entity_code,
source_id_int_1,
source_id_int_2,
source_id_int_3,
event_class_code,
event_type_code,
event_date,
event_status_code,
security_id_int_1,
security_id_int_2,
transaction_date,
reference_date_1,
transaction_number,
budgetary_control_flag)
SELECT 707,
acc.ledger_id,
'RCV_ACCOUNTING_EVENTS',
rae.rcv_transaction_id,
rae.accounting_event_id,
rae.organization_id,
cmap.event_class_code,
cmap.event_type_code,
rae.transaction_date,
XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
rae.organization_id,
rae.org_id,
rae.transaction_date,
acc.accounting_date,
rae.accounting_event_id,
NULL
FROM cst_lc_rae_events_gt rae,
cst_lc_adj_acctg_info_gt acc,
cst_xla_rcv_event_map cmap
WHERE rae.lcm_transaction_id = acc.lcm_transaction_id
AND cmap.event_class_code IN ('LDD_COST_ADJ_RCV', 'LDD_COST_ADJ_DEL')
AND cmap.transaction_type_id = rae.event_type_id;
l_num_records || ' Rows inserted'
);
/* Insert into the LCM transactions table to maintain history of this adjustment */
INSERT INTO cst_lc_adj_transactions
(transaction_id,
rcv_transaction_id,
organization_id,
inventory_item_id,
transaction_date,
prior_landed_cost,
new_landed_cost,
group_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT
transaction_id,
rcv_transaction_id,
organization_id,
inventory_item_id,
transaction_date,
prior_landed_cost,
new_landed_cost,
group_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE
FROM cst_lc_adj_interface
WHERE group_id = p_group_id
AND organization_id = p_organization_id
AND process_status = 2;
l_num_records || ' Rows inserted'
);
DELETE
FROM cst_lc_adj_interface e
WHERE e.group_id = p_group_id
AND e.organization_id = p_organization_id
AND e.process_status = 2
AND EXISTS (SELECT 1
FROM cst_lc_adj_transactions t
WHERE t.transaction_id = e.transaction_id);
l_num_records || ' Rows deleted'
);
END Insert_Adjustment_Data;
| CST_LC_ADJ_INTERFACE_ERRORS and updates the process_status|
| of interface records to validated = 2 or errored = 3. |
| |
| |
| |
| CALLED FROM : Process_LcmAdjustments Procedure |
| |
| Parameters : |
| IN : p_group_id IN NUMBER REQUIRED |
| p_organization_id IN NUMBER REQUIRED |
| p_api_version IN NUMBER REQUIRED |
| p_init_msg_list IN VARCHAR2 REQUIRED |
| p_validation_level IN NUMBER REQUIRED |
| |
| OUT : x_ledger_id OUT NOCOPY VARCHAR2 |
| x_primary_cost_method OUT NOCOPY NUMBER |
| x_primary_cost_method OUT NOCOPY VARCHAR2 |
| x_return_status OUT NOCOPY NUMBER |
| |
| NOTES : None |
| |
| |
+===========================================================================*/
PROCEDURE Validate_Lc_Interface
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_group_id IN NUMBER,
p_organization_id IN NUMBER,
x_no_of_errored OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) :='Validate_Lc_Interface';
INSERT ALL
WHEN new_landed_cost < 0 THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'NEW_LANDED_COST', 'The column cannot have negative value',
fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
WHEN prior_landed_cost < 0 THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'PRIOR_LANDED_COST', 'The column cannot have negative value',
fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
WHEN closing_status <> 'O' THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'TRANSACTION_DATE', 'Purchasing Period is Closed or in the Future',
fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
WHEN open_flag = 'N' THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'TRANSACTION_DATE', 'Inventory Period is Closed',
fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
WHEN rcv_transaction_id = -1 THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'RCV_TRANSACTION_ID', 'Invalid Rcv Transaction',
fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
WHEN destination_type_code = 'EXPENSE' OR destination_type_code = 'SHOP FLOOR' THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'RCV_TRANSACTION_ID',
'Destination type is set to ' || destination_type_code, fnd_global.user_id, SYSDATE,
fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id,
fnd_global.user_id, fnd_global.conc_request_id)
WHEN NOT (parent_transaction_id = -1 AND transaction_type IN ('RECEIVE', 'MATCH') ) THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'RCV_TRANSACTION_ID', 'The receipt transaction is not the parent receipt',
fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
WHEN lcm_flag = 'N' THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'RCV_TRANSACTION_ID', 'PO Shipment is not LCM Enabled',
fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
/* Bug 13960596 WHEN pol_item_id <> clai_inventory_item_id THEN */
WHEN rcv_item_id <> clai_inventory_item_id THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'INVENTORY_ITEM_ID',
'Item Id in adjustment transaction and original receipt donot match', fnd_global.user_id, SYSDATE,
fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id,
fnd_global.user_id, fnd_global.conc_request_id)
WHEN rt_organization_id <> clai_organization_id THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'ORGANIZATION_ID', 'Organization Id in adjustment transaction
and original receipt donot match', fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id,
fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id, fnd_global.user_id,
fnd_global.conc_request_id)
WHEN lcm_enabled_flag = 'N' THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'ORGANIZATION_ID', 'Organization is not LCM Enabled',
fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE,
fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id)
WHEN nvl(lcm_account_id, -1) = -1 THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'ORGANIZATION_ID',
'Landed cost absorption account is not defined for the organization', fnd_global.user_id, SYSDATE,
fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id,
fnd_global.user_id, fnd_global.conc_request_id)
WHEN Decode(primary_cost_method, 1, 0, Nvl(lcm_var_account, -1)) = -1 THEN
INTO cst_lc_adj_interface_errors (GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
VALUES (p_group_id, transaction_id, 'ORGANIZATION_ID',
'Landed cost variance account is not defined for the organization', fnd_global.user_id, SYSDATE,
fnd_global.prog_appl_id, fnd_global.conc_program_id, SYSDATE, SYSDATE, fnd_global.login_id,
fnd_global.user_id, fnd_global.conc_request_id)
SELECT distinct clai.transaction_id, clai.rcv_transaction_id, new_landed_cost, prior_landed_cost,
gps.closing_status, open_flag, Nvl(rt.transaction_id, -1) recv_transaction_id,
pod.destination_type_code, parent_transaction_id, transaction_type, Nvl(POLL.lcm_flag, 'N') lcm_flag,
/* Bug 13960596 pol.item_id pol_item_id, */
rsl.item_id rcv_item_id,
clai.inventory_item_id clai_inventory_item_id,
Nvl(rt.organization_id, -1) rt_organization_id, clai.organization_id clai_organization_id,
Nvl(lcm_enabled_flag, 'N') lcm_enabled_flag,
mp.lcm_var_account, rp.lcm_account_id, mp.primary_cost_method,
msi.inventory_item_id msi_item_id
FROM org_acct_periods oap,
cst_lc_adj_interface clai,
rcv_transactions rt,
rcv_accounting_events rae,
/* Bug 13960596 po_lines_all pol, */
rcv_shipment_lines rsl,
po_line_locations_all poll,
po_distributions_all pod,
gl_period_statuses gps,
cst_acct_info_v cai,
mtl_parameters mp,
mtl_system_items_b msi,
rcv_parameters rp
WHERE oap.organization_id (+) = clai.organization_id
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) >= Trunc(oap.period_start_date)
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) <= Trunc(oap.schedule_close_date)
AND clai.group_id = p_group_id
AND clai.process_status = 1
AND clai.organization_id = p_organization_id
AND rt.transaction_id (+) = clai.rcv_transaction_id
AND rae.rcv_transaction_id (+) = clai.rcv_transaction_id
AND rae.event_type_id in (1,4)
AND rae.po_line_location_id = poll.line_location_id (+)
AND rae.po_distribution_id = pod.po_distribution_id (+)
/* Bug 13960596 AND rae.po_line_id = pol.po_line_id (+) */
AND rt.shipment_line_id = rsl.shipment_line_id (+)
AND cai.organization_id (+) = clai.organizatIon_id
AND gps.ledger_id (+) = cai.ledger_id
AND gps.application_id = G_PO_APPLICATION_ID
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) >= Trunc(gps.start_date)
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) <= Trunc(gps.end_date)
AND gps.adjustment_period_flag <> 'Y'
AND mp.organization_id (+) = clai.organization_id
AND rp.organization_id (+) = clai.organization_id
AND msi.inventory_item_id (+) = clai.inventory_item_id
AND msi.organization_id (+) = clai.organization_id;
l_num_records || ' Rows inserted'
);
INSERT INTO cst_lc_adj_interface_errors
(GROUP_ID, transaction_id, error_column, error_message, CREATED_BY,
CREATION_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, LAST_UPDATED_BY, REQUEST_ID)
SELECT p_group_id, transaction_id, 'TRANSACTION_DATE', 'Purchasing Period is Closed or in the Future',
fnd_global.user_id, SYSDATE, fnd_global.prog_appl_id, fnd_global.conc_program_id,
SYSDATE, SYSDATE, fnd_global.login_id, fnd_global.user_id, fnd_global.conc_request_id
FROM cst_lc_adj_interface clai,
gl_period_statuses gps,
cst_acct_info_v cai
WHERE clai.group_id = p_group_id
AND clai.process_status = 2
AND clai.organization_id = p_organization_id
AND cai.organization_id = p_organization_id
AND gps.set_of_books_id = cai.ledger_id
AND gps.application_id = G_PO_APPLICATION_ID
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) >= Trunc(gps.start_date)
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(clai.transaction_date, cai.legal_entity) <= Trunc(gps.end_date)
AND gps.adjustment_period_flag <> 'Y'
and gps.closing_status <> 'O';
l_num_records || ' Rows inserted'
);
UPDATE cst_lc_adj_interface i
SET process_status = 3,
group_id = NULL
WHERE group_id = p_group_id
AND process_status IN (1,2)
AND organization_id = p_organization_id
AND EXISTS (SELECT 1
FROM cst_lc_adj_interface_errors e
WHERE e.transaction_id = i.transaction_id
AND e.group_id = p_group_id);
l_num_records || ' Rows updated'
);
UPDATE cst_lc_adj_interface
SET process_status = 2
WHERE group_id = p_group_id
AND organization_id = p_organization_id
AND process_status = 1;
l_num_records || ' Rows updated'
);