[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX(primary_item_id)
INTO l_item_id
FROM wip_entities we
WHERE we.wip_entity_id = p_txn_source_id;
SELECT category_set_id
INTO l_category_set_id
FROM mtl_default_category_sets mdcs
WHERE functional_area_id = 5;
SELECT MAX(category_id)
INTO l_category_id
FROM mtl_item_categories mic
WHERE mic.inventory_item_id = l_item_id
AND mic.organization_id = p_organization_id
AND mic.category_set_id = l_category_set_id;
select primary_cost_method,nvl(cost_group_accounting,0)
into l_cost_method,l_cg_acct_flag
from mtl_parameters
where organization_code = p_organization_code;
select primary_cost_method,nvl(cost_group_accounting,0)
into l_cost_method,l_cg_acct_flag
from mtl_parameters
where organization_id = p_organization_id;
procedure insert_MTA (
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
P_ORG_ID IN NUMBER,
P_TXN_ID IN NUMBER,
P_USER_ID IN NUMBER,
P_LOGIN_ID IN NUMBER,
P_REQ_ID IN NUMBER,
P_PRG_APPL_ID IN NUMBER,
P_PRG_ID IN NUMBER,
P_ACCOUNT IN NUMBER,
P_DBT_CRDT IN NUMBER,
P_LINE_TYP IN NUMBER,
P_BS_TXN_VAL IN NUMBER,
P_CST_ELEMENT IN NUMBER,
P_RESOURCE_ID IN NUMBER,
P_ENCUMBR_ID IN NUMBER
) IS
/* local control variables */
l_api_name CONSTANT VARCHAR2(30) := 'insert_MTA';
SAVEPOINT Insert_MTA_PUB;
l_api_message := 'insert_MTA API: Txn ID = '||to_char(P_TXN_ID);
select 1
into l_num
from gl_code_combinations
where code_combination_id = P_ACCOUNT;
select 1
into l_num
from mfg_lookups
where lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
and lookup_code = P_LINE_TYP;
select 1
into l_num
from cst_cost_elements
where cost_element_id = P_CST_ELEMENT;
select 1
into l_num
from bom_resources
where resource_id = P_RESOURCE_ID;
select 1
into l_num
from gl_encumbrance_types
where encumbrance_type_id = P_ENCUMBR_ID;
select ledger_id
into l_sob_id
from cst_acct_info_v
where organization_id = P_ORG_ID;
select currency_code
into l_pri_curr
from gl_sets_of_books
where set_of_books_id = l_sob_id;
select precision, minimum_accountable_unit
into l_precision, l_min_acct_unit
from fnd_currencies
where currency_code = l_pri_curr;
insert into mtl_transaction_accounts -- line 95
(ORGANIZATION_ID,
TRANSACTION_ID,
REFERENCE_ACCOUNT,
INVENTORY_ITEM_ID,
BASE_TRANSACTION_VALUE,
PRIMARY_QUANTITY,
ACCOUNTING_LINE_TYPE,
COST_ELEMENT_ID,
TRANSACTION_DATE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_VALUE,
RATE_OR_AMOUNT,
BASIS_TYPE,
RESOURCE_ID,
ACTIVITY_ID,
CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
ENCUMBRANCE_TYPE_ID,
GL_BATCH_ID,
CONTRA_SET_ID,
REPETITIVE_SCHEDULE_ID,
GL_SL_LINK_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
select P_ORG_ID,
P_TXN_ID,
P_ACCOUNT,
mmt.inventory_item_id,
decode(l_min_acct_unit, NULL, decode(l_precision, NULL, ABS(P_BS_TXN_VAL) * sign(P_DBT_CRDT),
ROUND(ABS(P_BS_TXN_VAL) * sign(P_DBT_CRDT), l_precision)),
ROUND(ABS(P_BS_TXN_VAL) * sign(P_DBT_CRDT) / l_min_acct_unit) * l_min_acct_unit),
ABS(
DECODE(
mmt.transaction_action_id,
24,
mmt.quantity_adjusted,
mmt.primary_quantity
)
) * sign(P_DBT_CRDT),
P_LINE_TYP,
P_CST_ELEMENT,
mmt.transaction_date,
decode(mmt.transaction_source_type_id, 16, -1, nvl(mmt.transaction_source_id, -1)),
mmt.transaction_source_type_id,
decode(mmt.currency_code, NULL, NULL, l_pri_curr, NULL,
decode(mmt.currency_conversion_rate, NULL, NULL, 0, NULL,
decode(fc.minimum_accountable_unit, NULL,
decode(fc.precision, NULL, sign(P_DBT_CRDT) * ABS(P_BS_TXN_VAL) / mmt.currency_conversion_rate,
ROUND(sign(P_DBT_CRDT) * ABS(P_BS_TXN_VAL) / mmt.currency_conversion_rate, fc.precision)),
ROUND(sign(P_DBT_CRDT) * ABS(P_BS_TXN_VAL) / mmt.currency_conversion_rate / fc.minimum_accountable_unit) * fc.minimum_accountable_unit))),
decode(mmt.primary_quantity, 0, 0, sign(P_DBT_CRDT) * ABS(P_BS_TXN_VAL) / mmt.primary_quantity),
1,
P_RESOURCE_ID,
NULL,
decode(mmt.currency_code, l_pri_curr, NULL, mmt.currency_code),
decode(mmt.currency_code, l_pri_curr, NULL, NULL, NULL, nvl(mmt.currency_conversion_date, mmt.transaction_date)),
decode(mmt.currency_code, l_pri_curr, NULL, NULL, NULL, mmt.currency_conversion_type),
decode(mmt.currency_code, l_pri_curr, NULL, NULL, NULL, nvl(mmt.currency_conversion_rate, -1)),
P_ENCUMBR_ID,
-1,
1,
NULL,
NULL,
P_REQ_ID,
P_PRG_APPL_ID,
-1*P_PRG_ID,
sysdate,
sysdate,
P_USER_ID,
sysdate,
P_USER_ID,
P_LOGIN_ID
from mtl_material_transactions mmt, fnd_currencies fc
where mmt.transaction_id = P_TXN_ID
and (mmt.organization_id = P_ORG_ID or
mmt.transfer_organization_id = P_ORG_ID)
and fc.currency_code = nvl(mmt.currency_code, l_pri_curr);
if SQL%FOUND then -- insert succeeded
l_api_message := 'INSERT succeeded';
l_api_message := 'Insert Failed for txn_id '||to_char(P_TXN_ID)||'. Check that it exists in MMT and that P_ORG_ID is correct.';
END insert_MTA;
select mmt.transaction_action_id,
mmt.transaction_source_type_id,
nvl(mmt.transaction_source_id, -1),
mmt.inventory_item_id,
mmt.subinventory_code,
mmt.organization_id
into l_txn_act_id,
l_txn_src_type_id,
l_wip_entity_id,
l_item_id,
l_sub_inventory,
l_org_id
from mtl_material_transactions mmt
where mmt.transaction_id = p_txn_id;
select entity_type
into l_entity_type
from wip_entities
where wip_entity_id = l_wip_entity_id;
select decode(inventory_asset_flag,'Y', 0, 1), nvl(eam_item_type,-1)
into l_exp_item, l_rebuild_item
from mtl_system_items_b
where inventory_item_id = l_item_id
and organization_id = l_org_id;
select decode(asset_inventory, 1, 0, 1)
into l_exp_sub
from mtl_secondary_inventories
where secondary_inventory_name = l_sub_inventory
and organization_id = l_org_id;
select decode(nvl(issue_zero_cost_flag, 'N'), 'Y', 1, 0)
into l_zero_cost_flag
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id;
select entity_type
into l_entity_type
from wip_entities
where wip_entity_id = p_wip_entity_id;
select cceea.mfg_cost_element_id
into l_cst_element_id
from cst_cat_ele_exp_assocs cceea
where cceea.category_id = p_category_id
and sysdate >= cceea.start_date
and sysdate <= (nvl(cceea.end_date, sysdate) + 1);
select decode(l_cst_element_id, 1, nvl(material_account,-1),
3, nvl(resource_account, -1),
4, nvl(outside_processing_account, -1))
into l_account
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
SELECT replace(substr(version,1,instr(version,'.',1,2)-1),'.')
INTO l_db_version
FROM v$instance;
SELECT
ledger_id,
legal_entity,
operating_unit
INTO
p_ledger_id,
p_le_id,
p_ou_id
FROM
cst_acct_info_v
WHERE
organization_id = p_org_id;
SELECT EVENT_CLASS_CODE
INTO p_event_class_code
FROM CST_XLA_RCV_EVENT_MAP
WHERE TRANSACTION_TYPE_ID = 1;