[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
inventory_item_id,
organization_id,
subinventory_code,
transaction_date,
transaction_action_id,
transaction_source_type_id,
primary_quantity,
transaction_source_id,
operation_seq_num,
nvl(final_completion_flag,'N'),
acct_period_id
into
l_inv_item_id,
l_org_id,
l_subinv_code,
l_txn_date,
l_action_id,
l_src_type_id,
l_txn_qty,
l_wip_entity_id,
l_op_seq_num,
l_final_comp_flag,
l_period_id
from mtl_material_transactions
where
transaction_id=l_trx_id;
UPDATE cst_comp_snapshot cocd1
SET
(cocd1.prior_completion_quantity, cocd1.prior_scrap_quantity) =
(SELECT
NVL
(SUM(
DECODE(mmt.transaction_action_id,
30,0,
cocd2.primary_quantity)
),
0),
NVL(SUM(
DECODE(mmt.transaction_action_id,
31,0,
32,0,
cocd2.primary_quantity)
),
0)
FROM
cst_comp_snapshot cocd2,
mtl_material_transactions mmt
WHERE cocd2.transaction_id = mmt.transaction_id
AND cocd2.wip_entity_id = cocd1.wip_entity_id
AND cocd2.operation_seq_num = cocd1.operation_seq_num
AND mmt.transaction_action_id in (30,31,32)
AND mmt.organization_id = l_org_id
AND mmt.transaction_source_id = l_wip_entity_id
AND mmt.transaction_source_type_id = 5
AND (mmt.transaction_date < l_txn_date
OR (mmt.transaction_date = l_txn_date
AND mmt.transaction_id < l_trx_id)
)
)
WHERE cocd1.transaction_id = l_trx_id
AND cocd1.wip_entity_id = l_wip_entity_id;
| therefore deliberately refrain from inserting a
| cost row. For completions, assembly returns and
| scrap transactions however, in the respective
| packages, we do not insert row if the cost is zero.
| To prevent such transactions from being processed at
| current average cost, we need to insert a dummy
| TL materil row into the cost table with zero cost.
|------------------------------------------------------*/
stmt_num :=75;
select count(*)
into l_mtl_txn_exists
from
mtl_cst_txn_cost_details
where
transaction_id = l_trx_id;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
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)
VALUES
(l_trx_id,
l_org_id,
l_inv_item_id,
1,
1,
0,
NULL,
NULL,
NULL,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
l_request_id,
l_prog_app_id,
l_prog_id,
SYSDATE);
| to update wip_period_balances.
|----------------------------------------------------------*/
stmt_num := 100;
select count(*) /*Modified for bug 9493692*/
into l_mcacd_count
from mtl_cst_actual_cost_details
where transaction_id = l_trx_id;
| in MTA to see if WPB needs to be updated or not otherwise |
| WPB will have cost incurred/relieved but MTA will have no |
| real distributions leading to discrepancy between MTA vs WPB|
|++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
stmt_num := 105;
select count(*)
into l_mta_count
from mtl_transaction_accounts
where transaction_id = l_trx_id;
SELECT COUNT(1)
INTO l_exp_job_cnt
FROM wip_discrete_jobs wdj,
wip_accounting_classes wac
WHERE wdj.wip_entity_id = l_wip_entity_id
AND wdj.organization_id = l_org_id
AND wac.organization_id = wdj.organization_id
AND wac.class_code = wdj.class_code
AND wac.class_type = 4
AND wdj.job_type = 3;
SELECT DECODE(asset_inventory,1,0,1)
INTO l_exp_sub
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_subinv_code
AND organization_id = l_org_id;
select entity_type
into l_entity_type
from wip_entities
where wip_entity_id = l_wip_entity_id;
/* Update Asset cost for eam jobs */
IF (l_entity_type in (6,7)) THEN
IF l_debug = 'Y' THEN
FND_FILE.PUT_LINE(FND_FILE.log, 'calling eamCost_pub.process_matCost ');