[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wpb.pl_material_in
FROM wip_period_balances wpb
WHERE wpb.acct_period_id = i_period_id
AND wpb.organization_id = i_org_id
AND wpb.wip_entity_id = i_entity_id
AND wpb.repetitive_schedule_id in
(SELECT mmta.repetitive_schedule_id
FROM mtl_material_txn_allocations mmta
WHERE mmta.transaction_id = i_txn_id)
AND EXISTS
(SELECT 'Check if the item has cost'
FROM cst_item_costs cic, mtl_parameters mp
WHERE cic.inventory_item_id = i_item_id
AND mp.organization_id = i_org_id
AND cic.organization_id = mp.cost_organization_id
AND cic.cost_type_id = i_cost_type_id)
FOR UPDATE OF pl_material_in;
/* Update WIP_PERIOD_BALANCES */
IF i_entity_type <> 2 THEN
/* EAM Acct Enh Project */
CST_Utility_PUB.get_zeroCostIssue_flag (
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_txn_id => i_txn_id,
x_zero_cost_flag => l_zero_cost_flag
);
/* update wip_period_balances WHO columns */
UPDATE wip_period_balances b
SET LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = i_user_id,
LAST_UPDATE_LOGIN = i_login_id,
REQUEST_ID = DECODE(i_req_id, -1, NULL, i_req_id),
PROGRAM_APPLICATION_ID = DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
PROGRAM_ID = DECODE(i_prg_id, -1, NULL, i_prg_id),
PROGRAM_UPDATE_DATE = DECODE(i_req_id, -1, NULL, SYSDATE)
WHERE acct_period_id = i_period_id
AND organization_id = i_org_id
AND wip_entity_id = i_entity_id;
UPDATE wip_period_balances b
SET (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
pl_material_in,
pl_material_overhead_in,
pl_resource_in,
pl_outside_processing_in,
pl_overhead_in )=
(SELECT
SYSDATE, i_user_id, i_login_id,
DECODE(i_req_id, -1, NULL, i_req_id),
DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
DECODE(i_prg_id, -1, NULL, i_prg_id),
DECODE(i_req_id, -1, NULL, SYSDATE),
nvl(b.pl_material_in,0) +
(ROUND((NVL(material_cost,0) * -1 * i_txn_qty) /
l_round_unit) * l_round_unit),
nvl(b.pl_material_overhead_in,0) +
(ROUND((NVL(material_overhead_cost,0) * -1 * i_txn_qty) /
l_round_unit) * l_round_unit),
nvl(b.pl_resource_in,0) +
(ROUND((NVL(resource_cost,0) * -1 * i_txn_qty) /
l_round_unit) * l_round_unit),
nvl(b.pl_outside_processing_in,0)+
(ROUND((NVL(outside_processing_cost,0) * -1 * i_txn_qty) /
l_round_unit) * l_round_unit),
nvl(b.pl_overhead_in,0) +
(ROUND((NVL(overhead_cost,0) * -1 * i_txn_qty) /
l_round_unit) * l_round_unit)
FROM cst_item_costs cic, mtl_parameters mp
WHERE cic.inventory_item_id = i_item_id
AND cic.organization_id = mp.cost_organization_id
AND mp.organization_id = i_org_id
AND cic.cost_type_id = i_cost_type_id
)
WHERE acct_period_id = i_period_id
AND organization_id = i_org_id
AND wip_entity_id = i_entity_id
AND EXISTS
(SELECT 'Check if the item has cost'
FROM CST_ITEM_COSTS CIC, MTL_PARAMETERS MP
WHERE CIC.INVENTORY_ITEM_ID = i_item_id
AND CIC.ORGANIZATION_ID = MP.COST_ORGANIZATION_ID
AND MP.ORGANIZATION_ID = i_org_id
AND CIC.COST_TYPE_ID = i_cost_type_id);
UPDATE wip_period_balances b
SET (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
pl_material_in,
pl_material_overhead_in,
pl_resource_in,
pl_outside_processing_in,
pl_overhead_in) =
(SELECT
SYSDATE, i_user_id, i_login_id,
DECODE(i_req_id, -1, NULL, i_req_id),
DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
DECODE(i_prg_id, -1, NULL, i_prg_id),
DECODE(i_req_id, -1, NULL, SYSDATE),
nvl(b.pl_material_in,0) +
ROUND((NVL(material_cost,0) * -1 * alloc.primary_quantity)/
l_round_unit) * l_round_unit,
nvl(b.pl_material_overhead_in,0) +
ROUND((NVL(material_overhead_cost,0) * -1 *
alloc.primary_quantity)/l_round_unit) * l_round_unit,
nvl(b.pl_resource_in,0) +
ROUND((NVL(resource_cost,0) * -1 * alloc.primary_quantity)/
l_round_unit) * l_round_unit,
nvl(b.pl_outside_processing_in,0)+
ROUND((NVL(outside_processing_cost,0) * -1 *
alloc.primary_quantity)/l_round_unit)* l_round_unit,
nvl(b.pl_overhead_in,0) +
ROUND((NVL(overhead_cost,0) * -1 * alloc.primary_quantity)/
l_round_unit) * l_round_unit
FROM cst_item_costs cic,
mtl_parameters mp,
mtl_material_txn_allocations alloc
WHERE NVL(alloc.repetitive_schedule_id, -99) =
NVL(b.repetitive_schedule_id, -99)
AND alloc.transaction_id = i_txn_id
AND cic.inventory_item_id = i_item_id
AND cic.organization_id = mp.cost_organization_id
AND mp.organization_id = i_org_id
AND cic.cost_type_id = 1
)
WHERE CURRENT OF wpb_rep_sch;
(SELECT repetitive_schedule_id
FROM mtl_material_txn_allocations
WHERE transaction_id = i_txn_id)
AND EXISTS
(SELECT 'Check if the item has cost'
FROM CST_ITEM_COSTS CIC, MTL_PARAMETERS MP
WHERE INVENTORY_ITEM_ID = i_item_id
AND MP.ORGANIZATION_ID = i_org_id
AND CIC.ORGANIZATION_ID = MP.COST_ORGANIZATION_ID
AND COST_TYPE_ID = i_cost_type_id);*/
SELECT SUM(NVL(yielded_cost, 0)) yielded_cost,
cost_element_id,
level_type
FROM cst_item_cost_details
WHERE inventory_item_id = i_item_id
AND organization_id = i_org_id
AND cost_type_id = 1
GROUP BY cost_element_id, level_type;
SELECT wpb.pl_material_out
FROM wip_period_balances wpb
WHERE wpb.acct_period_id = i_period_id
AND wpb.organization_id = i_org_id
AND wpb.wip_entity_id = i_entity_id
AND wpb.repetitive_schedule_id in
(SELECT mmta.repetitive_schedule_id
FROM mtl_material_txn_allocations mmta
WHERE mmta.transaction_id = i_txn_id)
AND EXISTS
(SELECT 'Check if the item has cost'
FROM cst_item_costs cic, mtl_parameters mp
WHERE cic.inventory_item_id = i_item_id
AND mp.organization_id = i_org_id
AND cic.organization_id = mp.cost_organization_id
AND cic.cost_type_id = i_cost_type_id)
FOR UPDATE OF pl_material_out;
/* Update TL, PL (OUT) costs to WIP_PERIOD_BALANCES for the assembly */
/* Separte SQL statement for job and schedule for faster performance */
/* Complete into INV take positive quantity */
IF i_entity_type <> 2 THEN /* discrete job */
/*----------------------------------------------
| Check if reallocation of operation yield cost |
| is to be done. This was added by Sujit Dalai |
------------------------------------------------- */
SELECT DECODE (entity_type, 5, 1, 0)
INTO x_realoc_yld_cost
FROM wip_entities
WHERE wip_entity_id = i_entity_id
AND organization_id = i_org_id ;
UPDATE wip_period_balances b
SET (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
pl_material_out,
pl_material_overhead_out,
pl_resource_out,
pl_outside_processing_out,
pl_overhead_out,
tl_material_out,
tl_material_overhead_out,
tl_resource_out,
tl_outside_processing_out,
tl_overhead_out,
tl_scrap_out) =
(SELECT
SYSDATE, i_user_id, i_login_id,
DECODE(i_req_id, -1, NULL, i_req_id),
DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
DECODE(i_prg_id, -1, NULL, i_prg_id),
DECODE(i_req_id, -1, NULL, SYSDATE),
nvl(b.pl_material_out,0) +
ROUND(((NVL(pl_material,0) - x_pl_mat_yld_cost) * i_txn_qty)/l_round_unit)*
l_round_unit,
nvl(b.pl_material_overhead_out,0) +
ROUND(((NVL(pl_material_overhead,0) - x_pl_mat_ovhd_yld_cost) * i_txn_qty)/l_round_unit)*
l_round_unit,
nvl(b.pl_resource_out,0) +
ROUND(((NVL(pl_resource,0) - x_pl_res_yld_cost)* i_txn_qty)/l_round_unit)*
l_round_unit,
nvl(b.pl_outside_processing_out,0) +
ROUND(((NVL(pl_outside_processing,0) - x_pl_osp_yld_cost) * i_txn_qty)/l_round_unit)*
l_round_unit,
nvl(b.pl_overhead_out,0) +
ROUND(((NVL(pl_overhead,0) - x_pl_ovhd_yld_cost) * i_txn_qty)/l_round_unit)*
l_round_unit,
nvl(b.tl_material_out,0)+
ROUND(((NVL(tl_material,0) - x_tl_mat_yld_cost) * i_txn_qty)/l_round_unit)*
l_round_unit,
nvl(b.tl_material_overhead_out,0)+
/* code change for bug 2090740 - decode modified for osfm jobs(class_type = 5) */
/* if standard job or repetitive or osfm job, do not credit the job
tl matl ovhd ;
(SELECT 'Check if the item has cost'
FROM CST_ITEM_COSTS CIC, MTL_PARAMETERS MP
WHERE INVENTORY_ITEM_ID = i_item_id
AND MP.ORGANIZATION_ID = i_org_id
AND CIC.ORGANIZATION_ID = MP.COST_ORGANIZATION_ID
AND COST_TYPE_ID = i_cost_type_id);
UPDATE wip_period_balances b
SET (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
pl_material_out,
pl_material_overhead_out,
pl_resource_out,
pl_outside_processing_out,
pl_overhead_out,
tl_material_out,
tl_resource_out,
tl_outside_processing_out,
tl_overhead_out) =
(SELECT
SYSDATE, i_user_id, i_login_id,
DECODE(i_req_id, -1, NULL, i_req_id),
DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
DECODE(i_prg_id, -1, NULL, i_prg_id),
DECODE(i_req_id, -1, NULL, SYSDATE),
nvl(b.pl_material_out,0) +
ROUND((NVL(pl_material,0) * alloc.primary_quantity)/
l_round_unit)* l_round_unit,
nvl(b.pl_material_overhead_out,0) +
ROUND((NVL(pl_material_overhead,0) *alloc.primary_quantity)
/l_round_unit)* l_round_unit,
nvl(b.pl_resource_out,0) +
ROUND((NVL(pl_resource,0) *alloc.primary_quantity)
/l_round_unit)* l_round_unit,
nvl(b.pl_outside_processing_out,0) +
ROUND((NVL(pl_outside_processing,0) *alloc.primary_quantity)
/l_round_unit)* l_round_unit,
nvl(b.pl_overhead_out,0) +
ROUND((NVL(pl_overhead,0) * alloc.primary_quantity)/
l_round_unit)* l_round_unit,
nvl(b.tl_material_out,0)+
ROUND((NVL(tl_material,0) * alloc.primary_quantity)/
l_round_unit)* l_round_unit,
nvl(b.tl_resource_out,0) +
ROUND((NVL(tl_resource,0) * alloc.primary_quantity)/
l_round_unit)* l_round_unit,
nvl(b.tl_outside_processing_out,0) +
ROUND((NVL(tl_outside_processing,0)*alloc.primary_quantity)
/l_round_unit)* l_round_unit,
nvl(b.tl_overhead_out,0) +
ROUND((NVL(tl_overhead,0) * alloc.primary_quantity)/
l_round_unit)* l_round_unit
FROM cst_item_costs cic,
mtl_parameters mp,
mtl_material_txn_allocations alloc
WHERE cic.inventory_item_id = i_item_id
AND cic.organization_id = mp.cost_organization_id
AND mp.organization_id = i_org_id
AND cic.cost_type_id = 1
AND alloc.transaction_id = i_txn_id
AND NVL(alloc.repetitive_schedule_id, -99) =
NVL(b.repetitive_schedule_id, -99)
)
WHERE CURRENT OF wpb_rep_sch;
(SELECT repetitive_schedule_id
FROM mtl_material_txn_allocations
WHERE transaction_id = i_txn_id)
AND EXISTS
(SELECT 'Check if the item has cost'
FROM CST_ITEM_COSTS CIC, MTL_PARAMETERS MP
WHERE INVENTORY_ITEM_ID = i_item_id
AND MP.ORGANIZATION_ID = i_org_id
AND CIC.ORGANIZATION_ID = MP.COST_ORGANIZATION_ID
AND COST_TYPE_ID = i_cost_type_id);*/