[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT transaction_source_id,
operation_seq_num,
acct_period_id,
organization_id,
to_char(transaction_date,'YYYY/MM/DD HH24:MI:SS')
INTO l_wip_entity_id,
l_opseq_num,
l_period_id,
l_org_id,
l_txn_date
FROM mtl_material_transactions
WHERE transaction_id = p_txn_id;
SELECT SUM(NVL(base_transaction_value,0))
INTO l_value
FROM mtl_transaction_accounts
WHERE transaction_id = p_txn_id
AND accounting_line_type = 7 -- WIP valuation
GROUP BY transaction_id;
fnd_file.put_line(fnd_file.log, 'calling Update_eamCost');
Update_eamCost (
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_mode => l_txn_mode,
p_period_id => l_period_id,
p_org_id => l_org_id,
p_wip_entity_id => l_wip_entity_id,
p_opseq_num => l_opseq_num,
p_value_type => l_value_type,
p_value => l_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id,
p_txn_date => l_txn_date);
l_api_message := 'Update_eamCost returned error';
SELECT wt.transaction_id,
wt.organization_id,
wt.wip_entity_id,
wt.acct_period_id,
DECODE(wt.resource_id,NULL, null,
wt.resource_id) resource_id,
wt.operation_seq_num,
wt.resource_seq_num,
wt.charge_department_id,
to_char(wt.transaction_date,'YYYY/MM/DD HH24:MI:SS') txn_date
FROM wip_transactions wt
WHERE wt.group_id = p_group_id
AND EXISTS
(SELECT 'eam jobs'
FROM wip_entities we
WHERE we.wip_entity_id = wt.wip_entity_id
AND we.entity_type in (6,7));
SELECT SUM(NVL(wta.base_transaction_value,0))
INTO l_value
FROM wip_transaction_accounts wta
WHERE transaction_id = l_resourcetxn_rec.transaction_id
AND accounting_line_type = 7;
to update_eamcost is for Get_MaintCostCat to determine the owning
dept. Since we have the charge dept, there is no need for
Update_EamCost to call Get_MaintCostCat later on. So it is safe to
use p_res_seq_num for charge dept id
*/
IF l_resourcetxn_rec.charge_department_id <> 0 THEN
l_txn_mode := 3; -- resource txn w/ specified charge dept
Update_eamCost (
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_mode => l_txn_mode,
p_period_id => l_resourcetxn_rec.acct_period_id,
p_org_id => l_resourcetxn_rec.organization_id,
p_wip_entity_id => l_resourcetxn_rec.wip_entity_id,
p_opseq_num => l_resourcetxn_rec.operation_seq_num,
p_resource_id => l_resourcetxn_rec.resource_id,
p_res_seq_num => l_res_seq_num,
p_value_type => l_value_type,
p_value => l_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id,
p_txn_date => l_resourcetxn_rec.txn_date);
l_api_message := 'Update_eamCost returned error';
PROCEDURE Update_eamCost (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_txn_mode IN NUMBER, -- 1=material, 2=resource, 4=direct item
p_period_id IN NUMBER := null,
p_period_set_name IN VARCHAR2 := null,
p_period_name IN VARCHAR2 := null,
p_org_id IN NUMBER,
p_wip_entity_id IN NUMBER,
p_opseq_num IN NUMBER, -- routing operation sequence
p_resource_id IN NUMBER := null,
p_res_seq_num IN NUMBER := null,
p_value_type IN NUMBER, -- 1=actual, 2=estimated
p_value IN NUMBER,
p_user_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_id IN NUMBER,
p_prog_app_id IN NUMBER,
p_login_id IN NUMBER,
p_txn_date IN VARCHAR2,
p_txn_id IN NUMBER DEFAULT -1 -- Direct Item Acct Enh (Patchset J)
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_eamCost';
SAVEPOINT Update_eamCost_PUB;
fnd_file.put_line(fnd_file.log, 'In Update_eamCost');
SELECT we.entity_type
INTO l_wip_entity_type
FROM wip_entities we
WHERE we.wip_entity_id = p_wip_entity_id;
SELECT maint_cost_category
INTO l_maint_cost_category
FROM bom_departments
WHERE department_id = l_owning_dept_id;
SELECT department_id
INTO l_dept_id
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_opseq_num
AND organization_id = p_org_id;
fnd_file.put_line(fnd_file.log, 'Calling insertUpdate_EamPerBal');
InsertUpdate_eamPerBal(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => p_period_id,
p_period_set_name => p_period_set_name,
p_period_name => p_period_name,
p_org_id => p_org_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => p_opseq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_id,
p_asset_number => l_asset_number,
p_value_type => p_value_type,
p_value => p_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id,
p_txn_date => p_txn_date);
l_api_message := 'InsertUpdate_eamPerBal returned error';
ROLLBACK TO Update_eamCost_PUB;
ROLLBACK TO Update_eamCost_PUB;
fnd_file.put_line(fnd_file.log, 'Exception in Update_eamcost'|| SQLERRM);
ROLLBACK TO Update_eamCost_PUB;
, 'Update_eamCost : Statement -'||to_char(l_stmt_num)
);
END Update_eamCost;
PROCEDURE InsertUpdate_eamPerBal (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_period_id IN NUMBER := null,
p_period_set_name IN VARCHAR2 := null,
p_period_name IN VARCHAR2 := null,
p_org_id IN NUMBER,
p_wip_entity_id IN NUMBER,
p_owning_dept_id IN NUMBER,
p_dept_id IN NUMBER,
p_maint_cost_cat IN NUMBER,
p_opseq_num IN NUMBER,
p_eam_cost_element IN NUMBER,
p_asset_group_id IN NUMBER,
p_asset_number IN VARCHAR2,
p_value_type IN NUMBER,
p_value IN NUMBER,
p_user_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_id IN NUMBER,
p_prog_app_id IN NUMBER,
p_login_id IN NUMBER,
p_txn_date IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'InsertUpdate_eamPerBal';
select cii.instance_id,cii.inventory_item_id,cii.serial_number
from csi_item_instances cii,
eam_work_order_route ewor
where ewor.wip_entity_id=p_wip_entity_id
and cii.instance_id=ewor.instance_id
union
select mena.maintenance_object_id,cii.inventory_item_id,cii2.serial_number
from csi_item_instances cii,
mtl_eam_network_assets mena,
mtl_parameters mp,
csi_item_instances cii2
where cii.instance_number = p_asset_number
and mena.network_object_id = cii.instance_id
and cii2.instance_id = mena.maintenance_object_id
and cii.inventory_item_id = p_asset_group_id
and mp.maint_organization_id = p_org_id
and cii.last_vld_organization_id = mp.organization_id
and nvl(mena.start_date_active, sysdate) <= sysdate
and nvl(mena.end_date_active, sysdate) >= sysdate
and maintenance_object_type =3;
SAVEPOINT InsertUpdate_eamPerBal_PUB;
FND_FILE.PUT_LINE(fnd_file.log,'In InsertUpdate_eamPerBal');
SELECT acct_period_id,
period_set_name,
period_name,
period_start_date
INTO
l_period_id,
l_period_set_name,
l_period_name,
l_period_start_date
FROM org_acct_periods
WHERE organization_id = p_org_id AND
(acct_period_id = p_period_id OR
(period_set_name = p_period_set_name AND
period_name = p_period_name));
SELECT 0,
period_set_name,
period_name,
start_date
INTO
l_period_id,
l_period_set_name,
l_period_name,
l_period_start_date
FROM gl_periods
WHERE period_set_name = l_period_set_name AND
period_name = l_period_name;
Insert/update WIP_EAM_PERIOD_BALANCES
------------------------------------------------------------- */
l_stmt_num := 140;
SELECT count(*)
INTO l_count
FROM wip_eam_period_balances
WHERE period_set_name = l_period_set_name AND
period_name = l_period_name AND
/* Bug 2113001 */
acct_period_id = l_period_id AND
organization_id = p_org_id AND
wip_entity_id = p_wip_entity_id AND
maint_cost_category = p_maint_cost_cat AND
owning_dept_id = p_owning_dept_id AND
nvl(operations_dept_id,-99) = nvl(p_dept_id,-99) AND
operation_seq_num = p_opseq_num;
l_statement := 'UPDATE wip_eam_period_balances SET '
|| l_column || '='
|| 'nvl('|| l_column || ',0) + nvl(:p_value,0)'
|| ', last_update_date = sysdate'
|| ', last_updated_by = :p_user_id'
|| ', last_update_login = :p_login_id'
|| ' WHERE period_set_name = :l_period_set_name'
|| ' AND period_name = :l_period_name'
|| ' AND organization_id = :p_org_id'
|| ' AND wip_entity_id = :p_wip_entity_id'
|| ' AND maint_cost_category = :p_maint_cost_cat'
|| ' AND owning_dept_id = :p_owning_dept_id'
|| ' AND nvl(operations_dept_id,-99) = nvl(:p_dept_id,-99)'
|| ' AND operation_seq_num = :p_opseq_num';
FND_FILE.PUT_LINE(fnd_file.log,'Inserting wip_eam_period_balances....');
INSERT INTO wip_eam_period_balances (
period_set_name,
period_name,
acct_period_id,
wip_entity_id,
organization_id,
owning_dept_id,
operations_dept_id,
operation_seq_num,
maint_cost_category,
actual_mat_cost,
actual_lab_cost,
actual_eqp_cost,
system_estimated_mat_cost,
system_estimated_lab_cost,
system_estimated_eqp_cost,
manual_estimated_mat_cost,
manual_estimated_lab_cost,
manual_estimated_eqp_cost,
period_start_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id
)
VALUES (
l_period_set_name,
l_period_name,
l_period_id,
p_wip_entity_id,
p_org_id,
p_owning_dept_id,
p_dept_id,
p_opseq_num,
p_maint_cost_cat,
DECODE(l_col_type, 13, NVL(p_value,0),0), -- actual mat
DECODE(l_col_type, 12, NVL(p_value,0),0), -- actual lab
DECODE(l_col_type, 11, NVL(p_value,0),0), -- actual eqp
DECODE(l_col_type, 23, NVL(p_value,0),0), -- sys est
DECODE(l_col_type, 22, NVL(p_value,0),0), -- sys est
DECODE(l_col_type, 21, NVL(p_value,0),0), -- sys est
0,
0,
0,
l_period_start_date,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id
);
fnd_file.put_line(fnd_file.log, 'Inserted into wepb');
SELECT maintenance_object_id, maintenance_object_type
INTO l_maint_obj_id, l_maint_obj_type
FROM WIP_DISCRETE_JOBS
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_org_id;
select network_asset_flag
into l_route_asset
from CSI_Item_Instances
where instance_id = l_maint_obj_id;
select count(*)
into l_asset_count
from EAM_WORK_ORDER_ROUTE
where wip_entity_id=p_wip_entity_id;
select count(*)
into l_asset_count
from mtl_eam_network_assets mena,
csi_item_instances cii,
mtl_parameters mp
where cii.instance_number = p_asset_number
and mena.network_object_id = cii.instance_id
and cii.inventory_item_id = p_asset_group_id
and mp.maint_organization_id = p_org_id
and cii.last_vld_organization_id = mp.organization_id
and nvl(mena.start_date_active, sysdate) <= sysdate
and nvl(mena.end_date_active, sysdate) >= sysdate
and maintenance_object_type =3;
InsertUpdate_assetPerBal (
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => p_org_id,
p_maint_cost_cat => p_maint_cost_cat,
p_asset_group_id => route_assets.inventory_item_id,
p_asset_number => route_assets.serial_number,
p_value => l_alloc_amount,
p_column => l_column,
p_col_type => l_col_type,
p_period_start_date => l_period_start_date,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id,
p_maint_obj_type => 3.0,
p_maint_obj_id => route_assets.instance_id
);
l_api_message := 'InsertUpdate_assetPerBal error';
'InsertUpdate_eamPerBal('||to_char(l_stmt_num) || ')', l_api_message);
InsertUpdate_assetPerBal (
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => p_org_id,
p_maint_cost_cat => p_maint_cost_cat,
p_asset_group_id => p_asset_group_id,
p_asset_number => p_asset_number,
p_value => p_value,
p_column => l_column,
p_col_type => l_col_type,
p_period_start_date => l_period_start_date,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id,
p_maint_obj_id => l_maint_obj_id,
p_maint_obj_type => l_maint_obj_type
);
l_api_message := 'InsertUpdate_assetPerBal error';
'InsertUpdate_eamPerBal('||to_char(l_stmt_num) || ')', l_api_message);
FND_FILE.PUT_LINE(fnd_file.log,'inserted into cst_eam_asset_per_balances' );
ROLLBACK TO InsertUpdate_eamPerBal_PUB;
ROLLBACK TO InsertUpdate_eamPerBal_PUB;
ROLLBACK TO InsertUpdate_eamPerBal_PUB;
'InsertUpdate_eamPerBal : Statement -'||to_char(l_stmt_num)
);
END InsertUpdate_eamPerBal;
PROCEDURE InsertUpdate_assetPerBal (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_period_id IN NUMBER := null,
p_period_set_name IN VARCHAR2 := null,
p_period_name IN VARCHAR2 := null,
p_org_id IN NUMBER,
p_maint_cost_cat IN NUMBER,
p_asset_group_id IN NUMBER,
p_asset_number IN VARCHAR2,
p_value IN NUMBER,
p_column IN VARCHAR2,
p_col_type IN NUMBER,
p_period_start_date IN DATE,
p_maint_obj_id IN NUMBER,
p_maint_obj_type IN NUMBER,
p_user_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_id IN NUMBER,
p_prog_app_id IN NUMBER,
p_login_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'InsertUpdate_assetPerBal';
SAVEPOINT InsertUpdate_assetPerBal_PUB;
FND_FILE.PUT_LINE(fnd_file.log,'In InsertUpdate_assetPerBal');
SELECT count(*)
INTO l_count
FROM cst_eam_asset_per_balances
WHERE period_set_name = p_period_set_name AND
period_name = p_period_name AND
organization_id = p_org_id AND
inventory_item_id = p_asset_group_id AND
serial_number = p_asset_number AND
maint_cost_category = p_maint_cost_cat;
l_statement := 'UPDATE cst_eam_asset_per_balances SET '
|| p_column || '='
|| 'nvl('|| p_column || ',0) + nvl(:p_value,0)'
|| ', last_update_date = sysdate'
|| ', last_updated_by = :p_user_id'
|| ' WHERE period_set_name = :p_period_set_name'
|| ' AND period_name = :p_period_name'
|| ' AND organization_id = :p_org_id'
|| ' AND inventory_item_id = :p_asset_group_id'
|| ' AND serial_number = :p_asset_number'
|| ' AND maint_cost_category = :p_maint_cost_cat';
INSERT INTO cst_eam_asset_per_balances (
period_set_name,
period_name,
acct_period_id,
organization_id,
inventory_item_id,
serial_number,
maint_cost_category,
actual_mat_cost,
actual_lab_cost,
actual_eqp_cost,
system_estimated_mat_cost,
system_estimated_lab_cost,
system_estimated_eqp_cost,
manual_estimated_mat_cost,
manual_estimated_lab_cost,
manual_estimated_eqp_cost,
period_start_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
maintenance_object_type,
maintenance_object_id
)
VALUES (
p_period_set_name,
p_period_name,
p_period_id,
p_org_id,
p_asset_group_id,
p_asset_number,
p_maint_cost_cat,
DECODE(p_col_type, 13, NVL(p_value,0),0), -- actual mat
DECODE(p_col_type, 12, NVL(p_value,0),0), -- actual lab
DECODE(p_col_type, 11, NVL(p_value,0),0), -- actual eqp
DECODE(p_col_type, 23, NVL(p_value,0),0), -- sys est
DECODE(p_col_type, 22, NVL(p_value,0),0), -- sys est
DECODE(p_col_type, 21, NVL(p_value,0),0), -- sys est
0, -- manual estimated (not implemented yet)
0,
0,
p_period_start_date,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_request_id,
p_prog_app_id,
p_maint_obj_type,
p_maint_obj_id
);
ROLLBACK TO InsertUpdate_assetPerBal_PUB;
ROLLBACK TO InsertUpdate_assetPerBal_PUB;
ROLLBACK TO InsertUpdate_assetPerBal_PUB;
'InsertUpdate_assetPerBal : Statement -'||to_char(l_stmt_num)
);
END InsertUpdate_assetPerBal;
SELECT resource_type
INTO l_resource_type
FROM bom_resources
WHERE organization_id = p_org_id
AND resource_id = p_resource_id;
SELECT def_eam_cost_element_id
into l_eam_cost_element
FROM wip_eam_parameters
WHERE organization_id = p_org_id;
SELECT entity_type,
organization_id
INTO l_entity_type,
l_organization_id
FROM wip_entities we
WHERE we.wip_entity_id = p_wip_entity_id;
SELECT count(*)
INTO l_ops_exists
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_opseq_num;
SELECT bd.department_id
INTO l_dept_id
FROM bom_departments bd,
wip_operations wo
WHERE bd.department_id = wo.department_id
AND wo.wip_entity_id = p_wip_entity_id
AND wo.operation_seq_num = p_opseq_num;
SELECT bdr.department_id,
DECODE(bdr.share_from_dept_id,null,bdr.department_id,
bdr.share_from_dept_id)
INTO l_dept_id, l_owning_dept_id
FROM wip_operation_resources wor,
wip_operations wo,
bom_department_resources bdr
WHERE bdr.department_id =
decode(wor.department_id,null,
wo.department_id, wor.department_id)
AND bdr.resource_id = wor.resource_id
AND wor.wip_entity_id = p_wip_entity_id
AND wor.operation_seq_num = p_opseq_num
AND wor.resource_seq_num = p_res_seq_num
AND wo.wip_entity_id = wor.wip_entity_id
AND wo.operation_seq_num = wor.operation_seq_num;
SELECT owning_department
INTO l_owning_dept_id
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id;
SELECT maint_cost_category
INTO l_maint_cost_category
FROM bom_departments
WHERE department_id = l_owning_dept_id;
SELECT def_maint_cost_category
INTO l_maint_cost_category
FROM wip_eam_parameters
WHERE organization_id = l_organization_id;
PROCEDURE Delete_eamPerBal (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_entity_id_tab IN CSTPECEP.wip_entity_id_type,
p_org_id IN NUMBER,
p_type IN NUMBER :=1
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_eamPerBal';
SELECT period_set_name,
period_name,
maint_cost_category,
sum(NVL(system_estimated_mat_cost,0)) sys_mat,
sum(NVL(system_estimated_lab_cost,0)) sys_lab,
sum(NVL(system_estimated_eqp_cost,0)) sys_eqp,
sum(NVL(manual_estimated_mat_cost,0)) man_mat,
sum(NVL(manual_estimated_lab_cost,0)) man_lab,
sum(NVL(manual_estimated_eqp_cost,0)) man_eqp
FROM wip_eam_period_balances
WHERE wip_entity_id = c_wip_entity_id AND
organization_id = c_org_id
GROUP BY period_set_name,
period_name,
maint_cost_category;
SAVEPOINT Delete_eamPerBal_PUB;
SELECT maintenance_object_id,
maintenance_object_type,
asset_group_id,
asset_number
INTO l_maint_obj_id,
l_maint_obj_type,
l_inventory_item_id,
l_asset_number
FROM wip_discrete_jobs
WHERE organization_id = p_org_id AND
wip_entity_id = p_entity_id_tab(l_index);
select network_asset_flag
into l_route_asset
from CSI_Item_Instances
where instance_id = l_maint_obj_id;
select count(*)
into l_asset_count
from EAM_WORK_ORDER_ROUTE ewor
where ewor.wip_entity_id = p_entity_id_tab(l_index);
select count(*)
into l_asset_count
from mtl_eam_network_assets mena,
csi_item_instances cii,
mtl_parameters mp
where cii.instance_number = l_asset_number
and mena.network_object_id = cii.instance_id
and cii.inventory_item_id = l_inventory_item_id
and mp.maint_organization_id = p_org_id
and cii.last_vld_organization_id = mp.organization_id
and nvl(mena.start_date_active, sysdate) <= sysdate
and nvl(mena.end_date_active, sysdate) >= sysdate
and maintenance_object_type =3;
IF (p_type = 1) AND -- update sys est
( v_est_rec.sys_mat <> 0 OR
v_est_rec.sys_lab <> 0 OR
v_est_rec.sys_eqp <> 0) THEN
l_stmt_num := 312;
UPDATE cst_eam_asset_per_balances
SET system_estimated_mat_cost =
system_estimated_mat_cost
- (v_est_rec.sys_mat/l_asset_count),
system_estimated_lab_cost =
system_estimated_lab_cost
- (v_est_rec.sys_lab/l_asset_count),
system_estimated_eqp_cost =
system_estimated_eqp_cost
- (v_est_rec.sys_eqp/l_asset_count)
WHERE period_set_name = v_est_rec.period_set_name AND
period_name = v_est_rec.period_name AND
maintenance_object_id in
(select ewor.instance_id
from eam_work_order_route ewor
where ewor.wip_entity_id = p_entity_id_tab(l_index)
union /* Added the union clause for Bug 5315176 */
select mena.maintenance_object_id
from mtl_eam_network_assets mena,
csi_item_instances cii,
mtl_parameters mp
where cii.instance_number = l_asset_number
and mena.network_object_id = cii.instance_id
and cii.inventory_item_id = l_inventory_item_id
and mp.maint_organization_id = p_org_id
and cii.last_vld_organization_id = mp.organization_id
and nvl(mena.start_date_active, sysdate) <= sysdate
and nvl(mena.end_date_active, sysdate) >= sysdate
and maintenance_object_type =3
)
AND organization_id = p_org_id
AND maint_cost_category = v_est_rec.maint_cost_category;
DELETE from cst_eam_asset_per_balances
WHERE actual_mat_cost = 0 AND
NVL(actual_lab_cost,0) = 0 AND
NVL(actual_eqp_cost,0) = 0 AND
NVL(system_estimated_mat_cost,0) = 0 AND
NVL(system_estimated_lab_cost,0) = 0 AND
NVL(system_estimated_eqp_cost,0) = 0 AND
NVL(manual_estimated_mat_cost,0) = 0 AND
NVL(manual_estimated_lab_cost,0) = 0 AND
NVL(manual_estimated_eqp_cost,0) = 0 AND
period_set_name = v_est_rec.period_set_name AND
period_name = v_est_rec.period_name AND
maintenance_object_id in
(select ewor.instance_id
from eam_work_order_route ewor
where ewor.wip_entity_id = p_entity_id_tab(l_index)
union /* Added the union clause for Bug 5315176 */
select mena.maintenance_object_id
from mtl_eam_network_assets mena,
csi_item_instances cii,
mtl_parameters mp
where cii.instance_number = l_asset_number
and mena.network_object_id = cii.instance_id
and cii.inventory_item_id = l_inventory_item_id
and mp.maint_organization_id = p_org_id
and cii.last_vld_organization_id = mp.organization_id
and nvl(mena.start_date_active, sysdate) <= sysdate
and nvl(mena.end_date_active, sysdate) >= sysdate
and maintenance_object_type =3
)
AND organization_id = p_org_id
AND maint_cost_category = v_est_rec.maint_cost_category;
ELSIF (p_type = 2) AND -- update manual est
(v_est_rec.man_mat <> 0 OR
v_est_rec.man_lab <> 0 OR
v_est_rec.man_eqp <> 0) THEN
l_stmt_num := 314;
UPDATE cst_eam_asset_per_balances
SET manual_estimated_mat_cost =
manual_estimated_mat_cost
- (v_est_rec.man_mat/l_asset_count),
manual_estimated_lab_cost =
manual_estimated_lab_cost
- (v_est_rec.man_lab/l_asset_count),
manual_estimated_eqp_cost =
manual_estimated_eqp_cost
- (v_est_rec.man_eqp/l_asset_count)
WHERE period_set_name = v_est_rec.period_set_name AND
period_name = v_est_rec.period_name AND
maintenance_object_id in
(select ewor.instance_id
from eam_work_order_route ewor
where ewor.wip_entity_id = p_entity_id_tab(l_index)
union /* Added the union clause for Bug 5315176 */
select mena.maintenance_object_id
from mtl_eam_network_assets mena,
csi_item_instances cii,
mtl_parameters mp
where cii.instance_number = l_asset_number
and mena.network_object_id = cii.instance_id
and cii.inventory_item_id = l_inventory_item_id
and mp.maint_organization_id = p_org_id
and cii.last_vld_organization_id = mp.organization_id
and nvl(mena.start_date_active, sysdate) <= sysdate
and nvl(mena.end_date_active, sysdate) >= sysdate
and maintenance_object_type =3
)
AND organization_id = p_org_id AND
maint_cost_category = v_est_rec.maint_cost_category;
DELETE from cst_eam_asset_per_balances
WHERE actual_mat_cost = 0 AND
NVL(actual_lab_cost,0) = 0 AND
NVL(actual_eqp_cost,0) = 0 AND
NVL(system_estimated_mat_cost,0) = 0 AND
NVL(system_estimated_lab_cost,0) = 0 AND
NVL(system_estimated_eqp_cost,0) = 0 AND
NVL(manual_estimated_mat_cost,0) = 0 AND
NVL(manual_estimated_lab_cost,0) = 0 AND
NVL(manual_estimated_eqp_cost,0) = 0 AND
period_set_name = v_est_rec.period_set_name AND
period_name = v_est_rec.period_name AND
maintenance_object_id in
(select ewor.instance_id
from eam_work_order_route ewor
where ewor.wip_entity_id = p_entity_id_tab(l_index)
union /* Added the union clause for Bug 5315176 */
select mena.maintenance_object_id
from mtl_eam_network_assets mena,
csi_item_instances cii,
mtl_parameters mp
where cii.instance_number = l_asset_number
and mena.network_object_id = cii.instance_id
and cii.inventory_item_id = l_inventory_item_id
and mp.maint_organization_id = p_org_id
and cii.last_vld_organization_id = mp.organization_id
and nvl(mena.start_date_active, sysdate) <= sysdate
and nvl(mena.end_date_active, sysdate) >= sysdate
and maintenance_object_type =3
)
AND organization_id = p_org_id AND
maint_cost_category = v_est_rec.maint_cost_category;
IF (p_type = 1) AND -- update sys est
( v_est_rec.sys_mat <> 0 OR
v_est_rec.sys_lab <> 0 OR
v_est_rec.sys_eqp <> 0) THEN
l_stmt_num := 316;
UPDATE cst_eam_asset_per_balances
SET system_estimated_mat_cost =
system_estimated_mat_cost
- v_est_rec.sys_mat,
system_estimated_lab_cost =
system_estimated_lab_cost
- v_est_rec.sys_lab,
system_estimated_eqp_cost =
system_estimated_eqp_cost
- v_est_rec.sys_eqp
WHERE period_set_name = v_est_rec.period_set_name AND
period_name = v_est_rec.period_name AND
maintenance_object_id = l_maint_obj_id AND
maint_cost_category = v_est_rec.maint_cost_category;
ELSIF (p_type = 2) AND -- update manual est
(v_est_rec.man_mat <> 0 OR
v_est_rec.man_lab <> 0 OR
v_est_rec.man_eqp <> 0) THEN
l_stmt_num := 320;
UPDATE cst_eam_asset_per_balances
SET manual_estimated_mat_cost =
manual_estimated_mat_cost
- v_est_rec.man_mat,
manual_estimated_lab_cost =
manual_estimated_lab_cost
- v_est_rec.man_lab,
manual_estimated_eqp_cost =
manual_estimated_eqp_cost
- v_est_rec.man_eqp
WHERE period_set_name = v_est_rec.period_set_name AND
period_name = v_est_rec.period_name AND
maintenance_object_id = l_maint_obj_id AND
organization_id = p_org_id AND
maint_cost_category = v_est_rec.maint_cost_category;
DELETE from cst_eam_asset_per_balances
WHERE actual_mat_cost = 0 AND
NVL(actual_lab_cost,0) = 0 AND
NVL(actual_eqp_cost,0) = 0 AND
NVL(system_estimated_mat_cost,0) = 0 AND
NVL(system_estimated_lab_cost,0) = 0 AND
NVL(system_estimated_eqp_cost,0) = 0 AND
NVL(manual_estimated_mat_cost,0) = 0 AND
NVL(manual_estimated_lab_cost,0) = 0 AND
NVL(manual_estimated_eqp_cost,0) = 0 AND
maintenance_object_id = l_maint_obj_id AND
organization_id = p_org_id;
UPDATE wip_eam_period_balances
SET system_estimated_mat_cost =
decode(p_type,1,0,system_estimated_mat_cost),
system_estimated_lab_cost =
decode(p_type,1,0,system_estimated_lab_cost),
system_estimated_eqp_cost =
decode(p_type,1,0,system_estimated_eqp_cost),
manual_estimated_mat_cost =
decode(p_type,2,0,manual_estimated_mat_cost),
manual_estimated_lab_cost =
decode(p_type,2,0,manual_estimated_lab_cost),
manual_estimated_eqp_cost =
decode(p_type,2,0,manual_estimated_eqp_cost)
WHERE wip_entity_id = p_entity_id_tab(l_index) AND
organization_id = p_org_id;
DELETE from wip_eam_period_balances
WHERE actual_mat_cost = 0 AND
NVL(actual_lab_cost,0) = 0 AND
NVL(actual_eqp_cost,0) = 0 AND
NVL(system_estimated_mat_cost,0) = 0 AND
NVL(system_estimated_lab_cost,0) = 0 AND
NVL(system_estimated_eqp_cost,0) = 0 AND
NVL(manual_estimated_mat_cost,0) = 0 AND
NVL(manual_estimated_lab_cost,0) = 0 AND
NVL(manual_estimated_eqp_cost,0) = 0 AND
wip_entity_id = p_entity_id_tab(l_index) AND
organization_id = p_org_id;
ROLLBACK TO Delete_eamPerBal_PUB;
ROLLBACK TO Delete_eamPerBal_PUB;
ROLLBACK TO Delete_eamPerBal_PUB;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Delete_eamPerBal - statement '
|| l_stmt_num || ': '
|| substr(SQLERRM,1,200));
, '.Delete_eamPerBal : Statement -'||to_char(l_stmt_num)
);
END Delete_eamPerBal;
SELECT wor.operation_seq_num operation_seq_num,
crc.resource_rate resource_rate,
wor.uom_code uom,
wor.usage_rate_or_amount resource_usage,
decode(br.functional_currency_flag,
1, 1,
NVL(crc.resource_rate,0))
* wor.usage_rate_or_amount
* decode(wor.basis_type,
1, l_lot_size,
2, 1,
1) raw_resource_value,
ROUND(decode(br.functional_currency_flag,
1, 1,
NVL(crc.resource_rate,0))
* wor.usage_rate_or_amount
* decode(wor.basis_type,
1, l_lot_size,
2, 1,
1) ,l_ext_precision) resource_value,
wor.resource_id resource_id,
wor.resource_seq_num resource_seq_num,
wor.basis_type basis_type,
wor.usage_rate_or_amount
* decode(wor.basis_type,
1, l_lot_size,
2, 1,
1) usage_rate_or_amount,
wor.standard_rate_flag standard_flag,
wor.department_id department_id,
br.functional_currency_flag functional_currency_flag,
br.cost_element_id cost_element_id,
br.resource_type resource_type
FROM wip_operation_resources wor,
bom_resources br,
cst_resource_costs crc
WHERE wor.wip_entity_id = p_wip_entity_id
AND br.resource_id = wor.resource_id
AND br.organization_id = wor.organization_id
AND crc.resource_id = wor.resource_id
AND crc.cost_type_id = l_rates_ct;
SELECT cdo.overhead_id ovhd_id,
cdo.rate_or_amount actual_cost,
cdo.basis_type basis_type,
ROUND(cdo.rate_or_amount *
decode(cdo.basis_type,
3, p_res_units,
p_res_value), l_ext_precision) rbo_value,
cdo.department_id
FROM cst_resource_overheads cro,
cst_department_overheads cdo
WHERE cdo.department_id = p_dept_id
AND cdo.organization_id = p_org_id
AND cdo.cost_type_id = l_rates_ct
AND cdo.basis_type IN (3,4)
AND cro.cost_type_id = cdo.cost_type_id
AND cro.resource_id = p_resource_id
AND cro.overhead_id = cdo.overhead_id
AND cro.organization_id = cdo.organization_id;
/* Select the costs corresponding to each cost element. The non-zero value for each
cost element will be used to estimate charges for WAC Accounts - eAM Enhancements
Project R12 */
CURSOR c_wro IS
SELECT wro.operation_seq_num operation_seq_num,
wro.department_id department_id,
ROUND(SUM(NVL(wro.required_quantity,0) * -- l_lot_size *
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,'Y',0,
nvl(ccicv.item_cost,0)),
NVL(ccicv.item_cost,0))), l_ext_precision) mat_value,
ROUND(SUM(NVL(wro.required_quantity,0) *
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,'Y',0,
nvl(ccicv.material_cost,0)),
NVL(ccicv.material_cost,0))), l_ext_precision) material_cost,
ROUND(SUM(NVL(wro.required_quantity,0) *
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,'Y',0,
nvl(ccicv.material_overhead_cost,0)),
NVL(ccicv.material_overhead_cost,0))), l_ext_precision) material_overhead_cost,
ROUND(SUM(NVL(wro.required_quantity,0) *
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,'Y',0,
nvl(ccicv.resource_cost,0)),
NVL(ccicv.resource_cost,0))), l_ext_precision) resource_cost,
ROUND(SUM(NVL(wro.required_quantity,0) *
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,'Y',0,
nvl(ccicv.outside_processing_cost,0)),
NVL(ccicv.outside_processing_cost,0))), l_ext_precision) outside_processing_cost,
ROUND(SUM(NVL(wro.required_quantity,0) *
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,'Y',0,
nvl(ccicv.overhead_cost,0)),
NVL(ccicv.overhead_cost,0))), l_ext_precision) overhead_cost
FROM wip_requirement_operations wro,
cst_cg_item_costs_view ccicv,
mtl_system_items_b msi,
wip_discrete_jobs wdj
WHERE wro.wip_entity_id = p_wip_entity_id
AND wdj.wip_entity_id = wro.wip_entity_id
AND ccicv.inventory_item_id = wro.inventory_item_id
AND ccicv.organization_id = wro.organization_id
AND ccicv.cost_group_id = decode(l_primary_cost_method,1,1,
l_cost_group_id)
AND wro.wip_supply_type IN (1,4)
AND nvl(wro.released_quantity,-1) <> 0
/* Non stockable items will be included in c_wrodi */
AND msi.organization_id = wro.organization_id
AND msi.inventory_item_id = wro.inventory_item_id
AND msi.stock_enabled_flag = 'Y'
AND wro.wip_entity_id = wdj.wip_entity_id /* Bug 5230287 */
AND wro.organization_id = wdj.organization_id /* Bug 5230287 */
GROUP BY wro.operation_seq_num,
wro.department_id;
SELECT
wro.operation_seq_num operation_seq_num,
wro.department_id department_id,
ROUND(SUM(
DECODE(
SIGN(NVL(wro.required_quantity,0) - NVL(wediv.quantity_ordered,0)),
1,
NVL(wro.required_quantity,0) - NVL(wediv.quantity_ordered,0),
0
) *
NVL(wro.unit_price,0)), l_ext_precision) mat_value,
msi.inventory_item_id item_id,
mic.category_id category_id
FROM wip_requirement_operations wro,
(SELECT cedi.work_order_number,
cedi.organization_id,
cedi.task_number,
cedi.item_id,
SUM(
inv_convert.inv_um_convert(
cedi.item_id, NULL, cedi.quantity_ordered,
cedi.uom_code, msi.primary_uom_code, NULL, NULL
)
/* We convert to primary_uom because the required_quantity in
WRO is always in the primary unit of measure */
) quantity_ordered
/* Sum is needed because there could be multiple POs/Reqs
for the same non-stockable item */
FROM cst_eam_direct_items_temp cedi,
mtl_system_items_b msi
WHERE cedi.item_id = msi.inventory_item_id
AND cedi.organization_id = msi.organization_id
AND cedi.work_order_number = p_wip_entity_id
GROUP
BY cedi.work_order_number,
cedi.organization_id,
cedi.task_number,
cedi.item_id
) wediv,
mtl_system_items_b msi,
mtl_item_categories mic,
mtl_default_category_sets mdcs
WHERE wro.wip_entity_id = p_wip_entity_id
AND wediv.work_order_number(+) = wro.wip_entity_id
AND wediv.item_id (+)= wro.inventory_item_id
AND wediv.organization_id(+) = wro.organization_id
AND wediv.task_number(+) = wro.operation_seq_num
AND wro.wip_supply_type IN (1,4)
AND msi.organization_id = wro.organization_id
AND msi.inventory_item_id = wro.inventory_item_id
AND msi.stock_enabled_flag = 'N'
AND msi.inventory_item_id = mic.inventory_item_id
AND mic.category_set_id = mdcs.category_set_id
AND mic.organization_id = wro.organization_id
AND mdcs.functional_area_id = 2
GROUP BY
wro.operation_seq_num,
wro.department_id,
msi.inventory_item_id,
mic.category_id;
SELECT
wedi.operation_seq_num operation_seq_num,
wedi.department_id department_id,
wedi.purchasing_category_id category_id,
wedi.direct_item_sequence_id direct_item_id,
ROUND(
DECODE(wediv.order_type_lookup_code,
'FIXED PRICE', NVL(wedi.amount,0) * NVL(wediv.currency_rate,1) - sum( NVL(wediv.amount_delivered ,0)),
'RATE', NVL(wedi.amount,0) * NVL(wediv.currency_rate,1) - sum(NVL(wediv.amount_delivered ,0)),
DECODE(
SIGN(
NVL(wedi.required_quantity,0) -
SUM(
/* Sum is needed because there could be multiple
POs/Reqs for the same description item */
inv_convert.inv_um_convert(
NULL, NULL, NVL(wediv.quantity_ordered,0),
NVL(wediv.uom_code, wedi.uom), wedi.uom, NULL, NULL
)
)
),
1,
(
NVL(wedi.required_quantity,0) -
SUM(
inv_convert.inv_um_convert(
NULL, NULL, NVL(wediv.quantity_ordered,0),
NVL(wediv.uom_code, wedi.uom), wedi.uom, NULL, NULL
)
)
),
0
) * NVL(wedi.unit_price, 0) * NVL(wediv.currency_rate,1)),
l_ext_precision
) wedi_value
FROM wip_eam_direct_items wedi,
cst_eam_direct_items_temp wediv
WHERE wedi.wip_entity_id = p_wip_entity_id
AND wediv.work_order_number(+) = wedi.wip_entity_id
AND wediv.organization_id(+) = wedi.organization_id
AND wediv.direct_item_sequence_id(+) = wedi.direct_item_sequence_id
AND wediv.task_number(+) = wedi.operation_seq_num
/* AND wediv.category_id(+) = wedi.purchasing_category_id - commented for Bug 5403190 */
GROUP
BY wedi.operation_seq_num,
wedi.department_id,
wedi.purchasing_category_id,
wedi.direct_item_sequence_id,
NVL(wedi.required_quantity,0),
NVL(wedi.unit_price,0),
NVL(wedi.amount,0),
wediv.order_type_lookup_code,
wediv.currency_rate;
SELECT
ROUND(SUM(
decode
(
NVL(pla.order_type_lookup_code,'QUANTITY'),
'RATE',(
(NVL(wediv.amount,0) - NVL(pda.amount_cancelled,0))
+ PO_TAX_SV.get_tax('PO',pda.po_distribution_id)
)
* NVL(wediv.currency_rate,1) ,
'FIXED PRICE',(
(NVL(wediv.amount,0) - NVL(pda.amount_cancelled,0))
+ PO_TAX_SV.get_tax('PO',pda.po_distribution_id)
)
* NVL(wediv.currency_rate,1),
(
NVL(plla.price_override,0) *
(NVL(pda.quantity_ordered,0) - NVL(pda.quantity_cancelled,0))
+ PO_TAX_SV.get_tax('PO',pda.po_distribution_id)
)
* NVL(wediv.currency_rate,1)
)), l_ext_precision
) pda_value,
pda.wip_operation_seq_num operation_seq_num,
pla.category_id category_id,
nvl(pha.approved_date, pha.last_update_date) category_date,
pha.type_lookup_code, /* Bug 5201970 */
wediv.po_release_id /* Bug 5201970 */
FROM po_distributions_all pda,
po_line_locations_all plla,
po_headers_all pha,
po_lines_all pla,
cst_eam_direct_items_temp wediv
WHERE wediv.work_order_number = p_wip_entity_id
AND wediv.organization_id = l_organization_id
AND wediv.task_number = pda.wip_operation_seq_num
AND wediv.category_id = pla.category_id
AND pha.po_header_id = wediv.po_header_id
AND pla.po_line_id = wediv.po_line_id
AND pda.wip_entity_id = wediv.work_order_number
AND pda.po_header_id = wediv.po_header_id
AND pda.destination_organization_id = wediv.organization_id
AND pda.po_line_id = pla.po_line_id
AND plla.line_location_id = pda.line_location_id
GROUP BY pda.wip_operation_seq_num,
pla.category_id,
pha.approved_date,
pha.last_update_date,
wediv.currency_rate,
pha.last_update_date,
pha.type_lookup_code,
wediv.po_release_id
UNION
SELECT
ROUND(SUM(
DECODE(NVL(prla.order_type_lookup_code,'QUANTITY'),
'RATE', NVL(wediv.amount,NVL(prla.amount * nvl(wediv.currency_rate,1),0)),
'FIXED PRICE', NVL(wediv.amount,NVL(prla.amount * nvl(wediv.currency_rate,1),0)),
NVL(prla.unit_price,0) * NVL(prla.quantity,0))
* NVL(wediv.currency_rate,1)), 6) pda_value,
prla.wip_operation_seq_num operation_seq_num,
prla.category_id category_id,
prha.last_update_date category_date,
null, /* Bug 5201970 */
null /* Bug 5201970 */
FROM po_requisition_lines_all prla,
po_requisition_headers_all prha,
cst_eam_direct_items_temp wediv
WHERE wediv.work_order_number = p_wip_entity_id
AND wediv.organization_id = l_organization_id
AND wediv.task_number = prla.wip_operation_seq_num
AND wediv.category_id = prla.category_id
AND wediv.po_header_id IS NULL -- to ensure that we do not double count
AND prha.requisition_header_id = wediv.requisition_header_id
AND prla.destination_organization_id = wediv.organization_id
AND prla.wip_entity_id = wediv.work_order_number
AND prla.requisition_line_id = wediv.requisition_line_id
GROUP BY prla.wip_operation_seq_num,
prla.category_id,
prha.last_update_date,
wediv.currency_rate;
SELECT SUM(ROUND(NVL(cdo.rate_or_amount,0) *
decode(cdo.basis_type,
1, l_lot_size,
2, 1,
1) ,l_ext_precision)) dbo_value,
cdo.department_id department_id ,
wo.operation_seq_num operation_seq_num
FROM wip_operations wo,
cst_department_overheads cdo
WHERE cdo.cost_type_id = l_rates_ct
AND cdo.organization_id = l_organization_id
AND cdo.department_id = wo.department_id
AND wo.wip_entity_id = p_wip_entity_id
AND cdo.rate_or_amount <> 0
AND cdo.basis_type IN (1,2)
GROUP BY wo.operation_seq_num,
cdo.department_id;
select material_account,
material_overhead_account,
resource_account,
outside_processing_account,
overhead_account,
class_code wip_acct_class
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
SELECT entity_type,
organization_id
INTO l_entity_type,
l_organization_id
FROM wip_entities we
WHERE we.wip_entity_id = p_wip_entity_id;
SELECT start_quantity,
NVL(project_id, -1),
scheduled_completion_date
INTO l_lot_size,
l_wip_project_id,
l_scheduled_completion_date
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = p_wip_entity_id;
SELECT NVL(default_cost_group_id,-1)
INTO l_cost_group_id
FROM mtl_parameters
WHERE organization_id = l_organization_id;
SELECT NVL(costing_group_id,-1)
INTO l_cost_group_id
FROM pjm_project_parameters ppp
WHERE ppp.project_id = l_wip_project_id
AND ppp.organization_id = l_organization_id;
SELECT count(*)
INTO l_dummy
FROM org_acct_periods oap
WHERE oap.organization_id = l_organization_id
AND l_trunc_le_sched_comp_date BETWEEN oap.period_start_date
AND oap.schedule_close_date;
SELECT oap.acct_period_id,
oap.period_set_name,
oap.period_name,
oap.period_start_date
INTO l_acct_period_id,
l_period_set_name,
l_period_name,
l_period_start_date
FROM org_acct_periods oap
WHERE oap.organization_id = l_organization_id
AND l_trunc_le_sched_comp_date BETWEEN oap.period_start_date
AND oap.schedule_close_date;
SELECT gp.period_set_name,
gp.period_name,
gp.start_date
INTO l_period_set_name,
l_period_name,
l_period_start_date
FROM gl_periods gp,
gl_sets_of_books gsob,
/*org_organization_definitions ood */
cst_organization_definitions ood
WHERE ood.organization_id = l_organization_id
AND gsob.set_of_books_id = ood.set_of_books_id
AND gp.period_set_name = gsob.period_set_name
AND gp.adjustment_period_flag = 'N'
AND gp.period_type = gsob.accounted_period_type
AND l_trunc_le_sched_comp_date BETWEEN gp.start_date
AND gp.end_date;
SELECT decode (mp.primary_cost_method,
1, mp.primary_cost_method,
NVL(mp.avg_rates_cost_type_id,-1)),
mp.primary_cost_method
INTO l_rates_ct,
l_primary_cost_method
FROM mtl_parameters mp
WHERE mp.organization_id = l_organization_id;
InsertUpdate_eamPerBal(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wor_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_wor_rec.resource_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_eamperbal error';
l_api_message :=' Calling Insert_eamBalAcct... WOR... ';
Insert_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wor_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_acct_id,
p_value => c_wor_rec.resource_value,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => c_wor_rec.cost_element_id,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
InsertUpdate_eamPerBal(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wor_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_rbo_rec.rbo_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_eamperbal error';
/* Insert Resource based overheads only if the value is greater than 0 */
IF ( l_sum_rbo <> 0 ) THEN
IF (p_debug = 'Y') THEN
l_api_message :=' Calling Insert_eamBalAcct... RBO... ';
Insert_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wor_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_overhead_account,
p_value => l_sum_rbo,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => 5, /* Overhead Cost Element */
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
resource and the resource value and insert into CST_EAM_WO_ESTIMATE_DETAILS */
l_sum_rbo := l_sum_rbo + c_wor_rec.resource_value;
Insert into CST_EAM_WO_ESTIMATE_DETAILS(
wip_entity_id,
organization_id,
operations_dept_id,
operations_seq_num,
maint_cost_category,
owning_dept_id,
estimated_cost,
resource_id,
resource_rate,
uom,
resource_usage,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES(
p_wip_entity_id,
l_organization_id,
l_operation_dept_id,
c_wor_rec.operation_seq_num,
l_maint_cost_category,
l_owning_dept_id,
l_sum_rbo,
c_wor_rec.resource_id,
c_wor_rec.resource_rate,
c_wor_rec.uom,
c_wor_rec.resource_usage,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE);
InsertUpdate_eamPerBal(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_dbo_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_dbo_rec.dbo_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'INSERTUPDATE_EAMPERBAL ERROR';
l_api_message :=' Calling Insert_eamBalAcct... DBO... ';
Insert_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_dbo_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_overhead_account,
p_value => c_dbo_rec.dbo_value,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => 5, -- Overhead Cost Element
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
InsertUpdate_eamPerBal(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => c_wro_rec.department_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wro_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_wro_rec.mat_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_eamperbal error';
l_api_message :=' Calling Insert_eamBalAcct... WRO... ';
Insert_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wro_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_account,
p_value => l_value,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => l_mfg_cost_element_id,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
/* Now start inserting the Estimation details into CST_EAM_WO_ESTIMATE_DETAILS */
l_stmt_num := 125;
Insert into CST_EAM_WO_ESTIMATE_DETAILS(
wip_entity_id,
organization_id,
operations_dept_id,
operations_seq_num,
maint_cost_category,
owning_dept_id,
estimated_cost,
inventory_item_id,
item_cost,
required_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT p_wip_entity_id,
wro.organization_id,
l_operation_dept_id,
wro.operation_seq_num,
l_maint_cost_category,
l_owning_dept_id,
NVL(wro.required_quantity,0) * -- lot_size * Commented for bug 5398315
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,'Y',0,nvl(ccicv.item_cost,0)),
NVL(ccicv.item_cost,0)),
wro.inventory_item_id,
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,'Y',0,ccicv.item_cost),
ccicv.item_cost),
wro.required_quantity,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE
FROM wip_requirement_operations wro,
cst_cg_item_costs_view ccicv,
wip_discrete_jobs wdj,
mtl_system_items_b msi
WHERE wro.wip_entity_id = p_wip_entity_id
AND ccicv.inventory_item_id = wro.inventory_item_id
AND ccicv.organization_id = wro.organization_id
AND ccicv.cost_group_id = decode(l_primary_cost_method,1,1,
l_cost_group_id)
AND wro.wip_supply_type IN (1,4)
AND nvl(wro.released_quantity,-1) <> 0
AND wdj.wip_entity_id = wro.wip_entity_id
AND msi.inventory_item_id = wro.inventory_item_id
AND msi.organization_id = wro.organization_id
AND msi.stock_enabled_flag = 'Y'
AND wro.department_id = c_wro_rec.department_id
AND wro.operation_seq_num = c_wro_rec.operation_seq_num
AND wdj.organization_id = wro.organization_id ;/* Bug 5230287 */
select cceea.mnt_cost_element_id,cceea.mfg_cost_element_id
into l_eam_cost_element,l_mfg_cost_element_id
from cst_cat_ele_exp_assocs cceea
where cceea.category_id = c_wrodi_rec.category_id
and NVL(cceea.end_date, SYSDATE) + 1 > SYSDATE
and cceea.start_date <= sysdate;
InsertUpdate_eamPerBal(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => c_wrodi_rec.department_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wrodi_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_wrodi_rec.mat_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_eamperbal error';
l_api_message :=' Calling Insert_eamBalAcct... WRODI... ';
Insert_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wrodi_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_acct_id,
p_value => c_wrodi_rec.mat_value,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => l_mfg_cost_element_id,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
/* Now start inserting the Estimation details into CST_EAM_WO_ESTIMATE_DETAILS */
l_stmt_num := 155;
Insert into CST_EAM_WO_ESTIMATE_DETAILS(
wip_entity_id,
organization_id,
operations_dept_id,
operations_seq_num,
maint_cost_category,
owning_dept_id,
estimated_cost,
inventory_item_id,
direct_item,
item_cost,
required_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT p_wip_entity_id,
wro.organization_id,
l_operation_dept_id,
wro.operation_seq_num,
l_maint_cost_category,
l_owning_dept_id,
(NVL(wro.required_quantity,0) - NVL(wediv.quantity_ordered,0))
* NVL(wro.unit_price,0),
wro.inventory_item_id,
'Y',
NVL(wro.unit_price,0),
NVL(wro.required_quantity,0) - NVL(wediv.quantity_ordered,0),
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE
FROM wip_requirement_operations wro,
(SELECT
cedi.work_order_number,
cedi.organization_id,
cedi.task_number,
cedi.item_id,
SUM(
inv_convert.inv_um_convert(
cedi.item_id, NULL, cedi.quantity_ordered,
cedi.uom_code, msi.primary_uom_code, NULL, NULL
)
/* We convert to primary_uom because the required_quantity in
WRO is always in the primary unit of measure */
) quantity_ordered
/* Sum is needed because there could be multiple POs/Reqs
for the same non-stockable item */
FROM cst_eam_direct_items_temp cedi,
mtl_system_items_b msi
WHERE cedi.item_id = msi.inventory_item_id
AND cedi.organization_id = msi.organization_id
AND cedi.work_order_number = p_wip_entity_id
GROUP
BY cedi.work_order_number,
cedi.organization_id,
cedi.task_number,
cedi.item_id
) wediv,
mtl_system_items_b msi
WHERE wro.wip_entity_id = p_wip_entity_id
AND wediv.work_order_number(+) = wro.wip_entity_id
AND wediv.item_id(+) = wro.inventory_item_id
AND wediv.organization_id(+) = wro.organization_id
AND wediv.task_number(+) = wro.operation_seq_num
AND wro.wip_supply_type IN (1,4)
AND msi.organization_id = wro.organization_id
AND msi.inventory_item_id = wro.inventory_item_id
AND msi.stock_enabled_flag = 'N'
AND wro.department_id = c_wrodi_rec.department_id
AND wro.operation_seq_num = c_wrodi_rec.operation_seq_num
AND wro.inventory_item_id = c_wrodi_rec.item_id
AND NVL(wro.required_quantity,0) > NVL(wediv.quantity_ordered,0);
select cceea.mnt_cost_element_id,cceea.mfg_cost_element_id
into l_eam_cost_element,l_mfg_cost_element_id
from cst_cat_ele_exp_assocs cceea
where cceea.category_id = c_wedi_rec.category_id
and NVL(cceea.end_date, SYSDATE) + 1 > SYSDATE
and cceea.start_date <= sysdate;
InsertUpdate_eamPerBal(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => c_wedi_rec.department_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wedi_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_wedi_rec.wedi_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_eamperbal error';
l_api_message :=' Calling Insert_eamBalAcct... WEDI';
Insert_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wedi_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_acct_id,
p_value => c_wedi_rec.wedi_value,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => l_mfg_cost_element_id,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
/* Now start inserting the Estimation details into CST_EAM_WO_ESTIMATE_DETAILS */
l_stmt_num := 175;
Insert into CST_EAM_WO_ESTIMATE_DETAILS(
wip_entity_id,
organization_id,
operations_dept_id,
operations_seq_num,
maint_cost_category,
owning_dept_id,
estimated_cost,
item_description,
direct_item,
item_cost,
required_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT
p_wip_entity_id,
wedi.organization_id,
l_operation_dept_id,
wedi.operation_seq_num,
l_maint_cost_category,
l_owning_dept_id,
DECODE(cedi.order_type_lookup_code,
'FIXED PRICE',NVL(wedi.amount,0) * NVL(cedi.currency_rate,1) - sum(NVL(cedi.amount_delivered,0)),
'RATE',NVL(wedi.amount,0) * NVL(cedi.currency_rate,1) - sum(NVL(cedi.amount_delivered,0)),
(NVL(wedi.required_quantity,0) -
SUM(
/* Sum is needed because there could be multiple
POs/Reqs for the same description item */
inv_convert.inv_um_convert(
NULL, NULL, NVL(cedi.quantity_ordered,0),
NVL(cedi.uom_code, wedi.uom), wedi.uom, NULL, NULL
)
)
) * NVL(wedi.unit_price, 0) * NVL(cedi.currency_rate,1)),
wedi.description,
'Y',
DECODE(cedi.order_type_lookup_code,
'FIXED PRICE',NVL(wedi.amount,0) * NVL(cedi.currency_rate,1),
'RATE',NVL(wedi.amount,0) * NVL(cedi.currency_rate,1),
NVL(wedi.unit_price, 0) * NVL(cedi.currency_rate,1) ),
DECODE(cedi.order_type_lookup_code,
'FIXED PRICE',NVL(wedi.amount,0) * NVL(cedi.currency_rate,1) - sum(NVL(cedi.amount_delivered,0)),
'RATE',NVL(wedi.amount,0) * NVL(cedi.currency_rate,1) - sum(NVL(cedi.amount_delivered,0)),
NVL(wedi.required_quantity,0) -
SUM(
/* Sum is needed because there could be multiple
POs/Reqs for the same description item */
inv_convert.inv_um_convert(
NULL, NULL, NVL(cedi.quantity_ordered,0),
NVL(cedi.uom_code, wedi.uom), wedi.uom, NULL, NULL
)
)),
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE
FROM wip_eam_direct_items wedi,
cst_eam_direct_items_temp cedi
WHERE wedi.wip_entity_id = p_wip_entity_id
AND cedi.work_order_number(+) = wedi.wip_entity_id
AND cedi.direct_item_sequence_id(+) = wedi.direct_item_sequence_id
AND cedi.organization_id(+) = wedi.organization_id
AND cedi.task_number(+) = wedi.operation_seq_num
/* AND cedi.category_id(+) = wedi.purchasing_category_id Commented for Bug 5403190 */
AND wedi.department_id = c_wedi_rec.department_id
AND wedi.operation_seq_num = c_wedi_rec.operation_seq_num
AND wedi.purchasing_category_id = c_wedi_rec.category_id
AND wedi.direct_item_sequence_id = c_wedi_rec.direct_item_id
GROUP
BY wedi.operation_seq_num,
wedi.organization_id,
NVL(wedi.required_quantity,0),
NVL(wedi.unit_price, 0),
NVL(wedi.amount,0),
wedi.description,
cedi.order_type_lookup_code,
cedi.currency_rate
HAVING
DECODE(cedi.order_type_lookup_code,
'FIXED PRICE',NVL(wedi.amount,0) - sum(NVL(cedi.amount_delivered,0)),
'RATE',NVL(wedi.amount,0) - sum(NVL(cedi.amount_delivered,0)),
NVL(wedi.required_quantity,0) - SUM(
inv_convert.inv_um_convert(
NULL, NULL, NVL(cedi.quantity_ordered,0),
NVL(cedi.uom_code, wedi.uom), wedi.uom, NULL, NULL
)
)) > 0;
SELECT department_id
INTO l_dept_id
FROM wip_operations wo
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.operation_seq_num = c_pda_rec.operation_seq_num;
select approved_date
into l_approved_date
from po_releases_all
where po_release_id = c_pda_rec.po_release_id;
select cceea.mnt_cost_element_id, cceea.mfg_cost_element_id
into l_eam_cost_element, l_mfg_cost_element_id
from cst_cat_ele_exp_assocs cceea
where cceea.category_id = c_pda_rec.category_id
and l_approved_date >= cceea.start_date
and l_approved_date < (nvl(cceea.end_date, sysdate) + 1);
InsertUpdate_eamPerBal(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_pda_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_pda_rec.pda_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_eamperbal error';
l_api_message :=' Calling Insert_eamBalAcct... PDA...';
Insert_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_pda_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_acct_id,
p_value => c_pda_rec.pda_value,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => l_mfg_cost_element_id,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
/* Insert quantity as NULL for Service Line types */
Insert into CST_EAM_WO_ESTIMATE_DETAILS(
wip_entity_id,
organization_id,
operations_dept_id,
operations_seq_num,
maint_cost_category,
owning_dept_id,
direct_item,
estimated_cost,
required_quantity,
item_cost,
rate,
requisition_header_id,
po_header_id,
requisition_line_id,
po_distribution_id,
line_location_id,
item_description,
inventory_item_id,
req_auth_status,
po_line_cancel_flag,
req_line_cancel_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT p_wip_entity_id,
l_organization_id,
l_operation_dept_id,
c_pda_rec.operation_seq_num,
l_maint_cost_category,
l_owning_dept_id,
'Y',
CST_TEMP.estimated_cost,
CST_TEMP.required_quantity,
CST_TEMP.unit_price,
CST_TEMP.rate,
CST_TEMP.requisition_header_id,
CST_TEMP.po_header_id,
CST_TEMP.requisition_line_id,
CST_TEMP.po_distribution_id,
CST_TEMP.line_location_id,
CST_TEMP.item_description,
CST_TEMP.item_id,
CST_TEMP.req_auth_status,
'N', -- enforced in the view WEDIV
'N', -- enforced in the view WEDIV
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE
FROM (
SELECT decode(NVL(pla.order_type_lookup_code,'QUANTITY'),
'RATE',(
(NVL(cedi.amount,0) - NVL(pda.amount_cancelled,0))
+ PO_TAX_SV.get_tax('PO',pda.po_distribution_id)
)
* NVL(cedi.currency_rate,1),
'FIXED PRICE',(
(NVL(cedi.amount,0) - NVL(pda.amount_cancelled,0))
+ PO_TAX_SV.get_tax('PO',pda.po_distribution_id)
)
* NVL(cedi.currency_rate,1),
(NVL(plla.price_override,0) *
(NVL(pda.quantity_ordered,0) - NVL(pda.quantity_cancelled,0))
+ /* Tax */ PO_TAX_SV.get_tax('PO',pda.po_distribution_id)
)
* NVL(cedi.currency_rate,1)) estimated_cost,
decode(NVL(pla.order_type_lookup_code,'QUANTITY'),
'RATE',NVL(cedi.amount,0) ,
'FIXED PRICE',NVL(cedi.amount,0),
NVL(cedi.unit_price,0)) unit_price,
DECODE(pla.order_type_lookup_code,'RATE',NULL,'FIXED PRICE',NULL,
NVL(pda.quantity_ordered,0) - NVL(pda.quantity_cancelled,0)
) required_quantity,
pda.rate rate,
cedi.po_header_id po_header_id,
cedi.requisition_header_id requisition_header_id,
cedi.requisition_line_id requisition_line_id,
pda.po_distribution_id po_distribution_id,
plla.line_location_id line_location_id,
pla.item_description item_description,
pla.item_id item_id,
cedi.req_authorization_status req_auth_status
FROM po_distributions_all pda,
po_line_locations_all plla,
po_headers_all pha,
po_lines_all pla,
cst_eam_direct_items_temp cedi
WHERE cedi.work_order_number = p_wip_entity_id
AND cedi.organization_id = l_organization_id
AND cedi.task_number = pda.wip_operation_seq_num
AND cedi.category_id = pla.category_id
AND pha.po_header_id = cedi.po_header_id
AND pla.po_line_id = cedi.po_line_id
AND pda.wip_entity_id = cedi.work_order_number
AND pda.po_header_id = cedi.po_header_id
AND pda.po_line_id = cedi.po_line_id
AND pda.destination_organization_id = cedi.organization_id
AND plla.line_location_id = pda.line_location_id
AND pda.wip_operation_seq_num = c_pda_rec.operation_seq_num
AND pla.category_id = c_pda_rec.category_id
AND NVL(pha.approved_date, pha.last_update_date) = c_pda_rec.category_date
UNION ALL
SELECT
decode(NVL(prla.order_type_lookup_code,'QUANTITY'),
'RATE',NVL(cedi.amount,NVL(prla.amount,0) * NVL(cedi.currency_rate,1)),
'FIXED PRICE',NVL(cedi.amount, NVL(prla.amount,0)* NVL(cedi.currency_rate,1)),
NVL(prla.unit_price,0) * NVL(prla.quantity,0) )
* NVL(cedi.currency_rate,1) estimated_cost,
decode(NVL(prla.order_type_lookup_code,'QUANTITY'),
'RATE',NVL(cedi.amount,0),
'FIXED PRICE',NVL(cedi.amount,0),
NVL(cedi.unit_price,0)) unit_price,
decode(NVL(prla.order_type_lookup_code,'QUANTITY'),
'RATE',NULL, 'FIXED PRICE',NULL,
prla.quantity) required_quantity,
prla.rate rate,
TO_NUMBER(NULL) po_header_id,
cedi.requisition_header_id requisition_header_id,
cedi.requisition_line_id requisition_line_id,
TO_NUMBER(NULL) po_distribution_id,
TO_NUMBER(NULL) line_location_id,
prla.item_description item_description,
prla.item_id item_id,
cedi.req_authorization_status req_auth_status
FROM po_requisition_lines_all prla,
po_requisition_headers_all prha,
cst_eam_direct_items_temp cedi
WHERE cedi.work_order_number = p_wip_entity_id
AND cedi.organization_id = l_organization_id
AND cedi.task_number = prla.wip_operation_seq_num
AND cedi.category_id = prla.category_id
AND cedi.po_header_id IS NULL -- to ensure that we do not double count
AND prha.requisition_header_id = cedi.requisition_header_id
AND prla.destination_organization_id = cedi.organization_id
AND prla.wip_entity_id = cedi.work_order_number
AND prla.requisition_line_id = cedi.requisition_line_id
AND prla.wip_operation_seq_num = c_pda_rec.operation_seq_num
AND prla.category_id = c_pda_rec.category_id
AND prha.last_update_date = c_pda_rec.category_date
) CST_TEMP;
SELECT gen_object_id
INTO l_gen_object_id
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number;
SELECT mtl_eam_asset_activities_s.nextval
INTO x_group_id
FROM dual;
INSERT INTO cst_eam_hierarchy_snapshot
(group_id,
object_type,
object_id,
parent_object_type,
parent_object_id,
level_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
last_update_login
)
SELECT
x_group_id,
1, -- Asset
object_id,
1, -- Asset
parent_object_id,
level,
sysdate,
1,
sysdate,
1,
NULL,
NULL,
NULL
FROM mtl_object_genealogy
START WITH object_id = l_gen_object_id
AND sysdate between start_date_active and nvl(end_date_active, sysdate)
CONNECT BY parent_object_id = PRIOR object_id
AND sysdate between start_date_active and nvl(end_date_active, sysdate);
SELECT count(*)
INTO l_count
FROM cst_eam_hierarchy_snapshot
WHERE group_id = x_group_id;
/* AMONDAL's fix, updated by DLE */
INSERT INTO cst_eam_hierarchy_snapshot
(group_id,
object_type,
object_id,
parent_object_type,
parent_object_id,
level_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
last_update_login
)
SELECT
x_group_id,
1, -- Asset
object_id,
1, -- Asset
parent_object_id,
level,
sysdate,
1,
sysdate,
1,
NULL,
NULL,
NULL
FROM mtl_object_genealogy
START WITH parent_object_id = l_gen_object_id
AND sysdate between start_date_active and nvl(end_date_active, sysdate)
CONNECT BY parent_object_id = PRIOR object_id
AND sysdate between start_date_active and nvl(end_date_active, sysdate);
INSERT INTO cst_eam_hierarchy_snapshot
(group_id,
object_type,
object_id,
parent_object_type,
parent_object_id,
level_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
last_update_login
)
VALUES
(x_group_id,
1, -- Asset
l_gen_object_id,
1, -- Asset
-1,
1,
sysdate,
1,
sysdate,
1,
NULL,
NULL,
NULL );
select
y.start_date
into
l_period_start_date
from gl_periods y,
mfg_lookups x
where
y.adjustment_period_flag = 'N' and
x.lookup_type(+) = 'MTL_ACCT_PERIOD_STATUS' and
x.enabled_flag(+) = 'Y' and
x.lookup_code (+)= 67 and
y.period_name = p_beginning_period_name and
y.period_set_name = p_period_set_name;
select
y.end_date
into
l_period_end_date
from gl_periods y,
mfg_lookups x
where
y.adjustment_period_flag = 'N' and
x.lookup_type(+) = 'MTL_ACCT_PERIOD_STATUS' and
x.enabled_flag(+) = 'Y' and
x.lookup_code (+)= 67 and
y.period_name = p_ending_period_name and
y.period_set_name = p_period_set_name;
select cii.instance_id
into l_maintenance_object_id
from csi_item_instances cii
where cii.serial_number = p_serial_number
and cii.inventory_item_id = p_inventory_item_id;
/* Inserted maintenance_object_id and maintenance_object_type from CEAPB as
part of eAM Requirements Project - R12. */
INSERT INTO cst_eam_rollup_temp
(group_id,
period_set_name,
period_name,
inventory_item_id,
serial_number,
organization_id,
acct_period_id,
maint_cost_category,
actual_mat_cost,
actual_lab_cost,
actual_eqp_cost,
system_estimated_mat_cost,
system_estimated_lab_cost,
system_estimated_eqp_cost,
manual_estimated_mat_cost,
manual_estimated_lab_cost,
manual_estimated_eqp_cost,
period_start_date,
maintenance_object_type,
maintenance_object_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id
)
SELECT
x_group_id,
p_period_set_name,
ceapb.period_name,
p_inventory_item_id,
p_serial_number,
ceapb.organization_id,
ceapb.acct_period_id,
ceapb.maint_cost_category,
sum(ceapb.actual_mat_cost),
sum(ceapb.actual_lab_cost),
sum(ceapb.actual_eqp_cost),
sum(ceapb.system_estimated_mat_cost),
sum(ceapb.system_estimated_lab_cost),
sum(ceapb.system_estimated_eqp_cost),
sum(ceapb.manual_estimated_mat_cost),
sum(ceapb.manual_estimated_lab_cost),
sum(ceapb.manual_estimated_eqp_cost),
ceapb.period_start_date,
3,
l_maintenance_object_id,
sysdate,
1,
sysdate,
1,
NULL,
NULL
FROM cst_eam_asset_per_balances ceapb,
mtl_serial_numbers msn,
cst_eam_hierarchy_snapshot cehs
WHERE
ceapb.inventory_item_id = msn.inventory_item_id
AND ceapb.serial_number = msn.serial_number
AND msn.gen_object_id = cehs.object_id
AND cehs.group_id = x_group_id
AND ceapb.period_set_name = p_period_set_name
AND ceapb.period_start_date >= DECODE(l_period_start_date, NULL, ceapb.period_start_date, l_period_start_date)
AND ceapb.period_start_date <= DECODE(l_period_end_date, NULL, ceapb.period_start_date, l_period_end_date)
GROUP BY
ceapb.period_name,
ceapb.organization_id,
ceapb.acct_period_id,
ceapb.maint_cost_category,
ceapb.period_start_date;
SELECT count(*) INTO l_count
FROM cst_eam_rollup_temp
WHERE group_id = x_group_id;
l_api_message := 'No row is inserted into CST_EAM_ROLLUP_TEMP';
DELETE cst_eam_hierarchy_snapshot
WHERE group_id= p_group_id;
DELETE cst_eam_rollup_temp
WHERE group_id= p_group_id;
SELECT interface_transaction_id
INTO l_interface_txn_id
FROM rcv_transactions
WHERE transaction_id = p_interface_txn_id;
SELECT wip_entity_id,
item_id,
to_organization_id
INTO l_wip_entity_id,
l_item_id,
l_org_id
FROM rcv_transactions_interface
WHERE interface_transaction_id = l_interface_txn_id;
SELECT nvl(eam_enabled_flag,'N')
INTO l_eam_flag
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT nvl(entity_type,-1)
INTO l_entity_type
FROM wip_entities
WHERE wip_entity_id = l_wip_entity_id;
SELECT nvl(outside_operation_flag,'N')
INTO l_osp_item
FROM mtl_system_items_b
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id;
SELECT interface_transaction_id, source_doc_quantity /*, primary_quantity*/
INTO l_interface_txn_id, l_quantity /*, l_primary_quantity*/
FROM rcv_transactions
WHERE transaction_id = l_txn_id;
/* SELECT decode(pol.order_type_lookup_code,
'RATE', rti.amount + rti.amount *
PO_TAX_SV.get_tax('PO',pod.po_distribution_id)/pod.amount_ordered,
'FIXED PRICE', rti.amount + rti.amount *
PO_TAX_SV.get_tax('PO',pod.po_distribution_id)/pod.amount_ordered,
(rti.po_unit_price +
PO_TAX_SV.get_tax('PO',pod.po_distribution_id)/pod.quantity_ordered)),
nvl(l_directItem_rec.currency_conversion_rate,nvl(rti.currency_conversion_rate,1)),
rti.currency_code,
pol.order_type_lookup_code
into l_actual_res_rate,
l_curr_rate,
l_curr_code,
l_po_order_type_lookup_code
from po_distributions_all pod,
rcv_transactions_interface rti,
po_lines_all pol
where rti.interface_transaction_id = l_interface_txn_id
and pod.po_distribution_id = rti.po_distribution_id
and pol.po_header_id=pod.po_header_id
and pol.po_line_id=pod.po_line_id; */
select decode(pol.order_type_lookup_code,
'RATE', rti.amount + rti.amount *
PO_TAX_SV.get_tax('PO',pod.po_distribution_id)/pod.amount_ordered,
'FIXED PRICE', rti.amount + rti.amount *
PO_TAX_SV.get_tax('PO',pod.po_distribution_id)/pod.amount_ordered,
(rti.po_unit_price +
PO_TAX_SV.get_tax('PO',pod.po_distribution_id)/pod.quantity_ordered)),
nvl(l_directItem_rec.currency_conversion_rate,nvl(rti.currency_conversion_rate,1)),
rti.currency_code,
pol.order_type_lookup_code,
1,
rti.last_updated_by,
sysdate,
l_actual_res_rate,
rti.currency_code,
nvl(l_directItem_rec.currency_conversion_date,rti.currency_conversion_date),
l_curr_rate,
nvl(l_directItem_rec.currency_conversion_type,rti.currency_conversion_type),
rti.last_updated_by,
sysdate,
rti.last_update_login,
rti.wip_operation_seq_num,
rti.organization_id,
rti.po_header_id,
rti.po_line_id,
2,
1,
sysdate,
pod.project_id,
rti.reason_id,
rti.comments,
2,
pod.task_id,
rti.transaction_date,
decode(pol.order_type_lookup_code, 'RATE',NULL,
'FIXED PRICE', NULL,
rti.quantity),
17,
rti.uom_code,
rti.wip_entity_id,
pol.item_id,
rti.source_doc_unit_of_measure
into l_actual_res_rate,
l_curr_rate,
l_curr_code,
l_po_order_type_lookup_code,
l_directItem_rec.basis_type,
l_directItem_rec.created_by,
l_directItem_rec.creation_date,
l_directItem_rec.currency_actual_rsc_rate,
l_directItem_rec.currency_code,
l_directItem_rec.currency_conversion_date,
l_directItem_rec.currency_conversion_rate,
l_directItem_rec.currency_conversion_type,
l_directItem_rec.last_updated_by,
l_directItem_rec.last_update_date,
l_directItem_rec.last_update_login,
l_directItem_rec.operation_seq_num,
l_directItem_rec.organization_id,
l_directItem_rec.po_header_id,
l_directItem_rec.po_line_id,
l_directItem_rec.process_phase,
l_directItem_rec.process_status,
l_directItem_rec.program_update_date,
l_directItem_rec.project_id,
l_directItem_rec.reason_id,
l_directItem_rec.reference,
l_directItem_rec.standard_rate_flag,
l_directItem_rec.task_id,
l_directItem_rec.transaction_date,
l_directItem_rec.transaction_quantity,
l_directItem_rec.transaction_type,
l_directItem_rec.transaction_uom,
l_directItem_rec.wip_entity_id,
l_item_id,
l_source_doc_unit_of_measure
from rcv_transactions rti,
po_distributions_all pod,
po_lines_all pol
where rti.transaction_id = l_txn_id
and rti.po_distribution_id = pod.po_distribution_id
and pol.po_line_id = pod.po_line_id;
SELECT uom_code
INTO l_source_doc_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_source_doc_unit_of_measure;
SELECT msi.primary_uom_code
INTO l_directItem_rec.primary_uom
from mtl_system_items_b msi
where msi.inventory_item_id = l_item_id
and msi.organization_id = l_directItem_rec.organization_id;
/* Bug 4683371 : Removed the select statement added for Bug #1795350.
The currency conversion rate should be multiplied by the product of Unit Price * Quantity
to obtain the correct transaction value. As we are not using the primary quantity here,
we can defer the actual resource rate calculation till Cost Processing. */
/* Bug 2595198 - adjust actual resource rate against the primary uom quantity */
/* No need to adjust quantity in case of Service Line Types
- eAM Requirements Project R12 */
IF ( l_po_order_type_lookup_code <> 'RATE'
AND l_po_order_type_lookup_code <> 'FIXED PRICE') THEN
l_actual_res_rate := l_actual_res_rate * (l_quantity/l_directItem_rec.primary_quantity);
/* select (l_actual_res_rate * l_curr_rate),
1,
rti.last_updated_by,
sysdate,
l_actual_res_rate,
rti.currency_code,
nvl(l_directItem_rec.currency_conversion_date,rti.currency_conversion_date),
l_curr_rate,
nvl(l_directItem_rec.currency_conversion_type,rti.currency_conversion_type),
rti.last_updated_by,
sysdate,
rti.last_update_login,
rti.wip_operation_seq_num,
rti.to_organization_id,
rti.po_header_id,
rti.po_line_id,
decode(l_po_order_type_lookup_code, 'RATE',NULL,
'FIXED PRICE', NULL,
rti.primary_quantity),
muom.uom_code,
2,
1,
sysdate,
pod.project_id,
rti.reason_id,
rti.comments,
2,
pod.task_id,
rti.transaction_date,
decode(l_po_order_type_lookup_code, 'RATE',NULL,
'FIXED PRICE', NULL,
rti.quantity),
17,
rti.uom_code,
l_actual_res_rate * l_curr_rate,
rti.wip_entity_id
into l_directItem_rec.actual_resource_rate,
l_directItem_rec.basis_type,
l_directItem_rec.created_by,
l_directItem_rec.creation_date,
l_directItem_rec.currency_actual_rsc_rate,
l_directItem_rec.currency_code,
l_directItem_rec.currency_conversion_date,
l_directItem_rec.currency_conversion_rate,
l_directItem_rec.currency_conversion_type,
l_directItem_rec.last_updated_by,
l_directItem_rec.last_update_date,
l_directItem_rec.last_update_login,
l_directItem_rec.operation_seq_num,
l_directItem_rec.organization_id,
l_directItem_rec.po_header_id,
l_directItem_rec.po_line_id,
l_directItem_rec.primary_quantity,
l_directItem_rec.primary_uom,
l_directItem_rec.process_phase,
l_directItem_rec.process_status,
l_directItem_rec.program_update_date,
l_directItem_rec.project_id,
l_directItem_rec.reason_id,
l_directItem_rec.reference,
l_directItem_rec.standard_rate_flag,
l_directItem_rec.task_id,
l_directItem_rec.transaction_date,
l_directItem_rec.transaction_quantity,
l_directItem_rec.transaction_type,
l_directItem_rec.transaction_uom,
l_directItem_rec.usage_rate_or_amount,
l_directItem_rec.wip_entity_id
from rcv_transactions_interface rti,
po_distributions_all pod,
mtl_units_of_measure muom,
mtl_units_of_measure puom
where rti.interface_transaction_id = l_interface_txn_id
and rti.po_distribution_id = pod.po_distribution_id
and puom.unit_of_measure(+) = rti.unit_of_measure
and muom.uom_class(+) = puom.uom_class
and muom.base_uom_flag(+) = 'Y'
and muom.language(+) = userenv('LANG');*/
select transaction_type
into l_po_txn_type
from rcv_transactions_interface
where interface_transaction_id = l_interface_txn_id;
select primary_item_id,
wip_entity_name,
entity_type
into l_directItem_rec.primary_item_id,
l_directItem_rec.wip_entity_name,
l_directItem_rec.entity_type
from wip_entities
where wip_entity_id = l_directItem_rec.wip_entity_id;
select wo.department_id,
bd.department_code
into l_directItem_rec.department_id,
l_directitem_rec.department_code
from wip_operations wo,
bom_departments bd
where wo.wip_entity_id = l_directItem_rec.wip_entity_id
and wo.operation_seq_num = l_directItem_rec.operation_seq_num
and wo.organization_id = l_directItem_rec.organization_id
and bd.department_id = wo.department_id;
select user_name
into l_directItem_rec.created_by_name
from fnd_user
where user_id = l_directItem_rec.created_by;
select user_name
into l_directItem_rec.last_updated_by_name
from fnd_user
where user_id = l_directItem_rec.last_updated_by;
fnd_file.put_line(fnd_file.log,'updated by ' || l_directItem_rec.last_updated_by);
select organization_code
into l_directItem_rec.organization_code
from mtl_parameters
where organization_id = l_directItem_rec.organization_id;
select reason_name
into l_directItem_rec.reason_name
from mtl_transaction_reasons
where reason_id = l_directItem_rec.reason_id;
select transaction_id,
organization_id,
nvl(acct_period_id,-1) acct_period_id,
nvl(receiving_account_id,-1) rcv_acct_id,
nvl(actual_resource_rate,0) act_res_rate,
nvl(currency_actual_resource_rate, 0) curr_act_res_rate,
wip_entity_id,
operation_seq_num opseq_num,
primary_quantity qty,
source_code src_code,
to_char(transaction_date,'YYYY/MM/DD HH24:MI:SS') txn_date,
rcv_transaction_id,
currency_code, /* bug 4683371 */
currency_conversion_rate /* bug 4683371 */
from wip_cost_txn_interface
where group_id = p_group_id
and process_status = 2;
/* Added the select statements in the IF clause for bug 4683371. The transaction value is
first calculated and then rounded with standard precision of the functional currency*/
/* Removed the select statement that fetched actual resource rate in functional
currency for IPV xrf txns as actual resource rate is correctly populated in WCTI,
no need to convert the value. Bug 5360723 */
if (direct_item_txn_rec.src_code = 'IPV') then
l_txn_value := direct_item_txn_rec.curr_act_res_rate;
select decode(nvl(fc.minimum_accountable_unit,0),0,
round(direct_item_txn_rec.act_res_rate,fc.precision),
round(direct_item_txn_rec.act_res_rate/fc.minimum_accountable_unit)
*fc.minimum_accountable_unit) ,
(decode(nvl(fc.minimum_accountable_unit,0),0,
round(direct_item_txn_rec.act_res_rate,fc.precision),
round(direct_item_txn_rec.act_res_rate/fc.minimum_accountable_unit)
*fc.minimum_accountable_unit)) / nvl(direct_item_txn_rec.currency_conversion_rate,1)
into l_base_txn_value,
l_txn_value
from fnd_currencies fc
where currency_code = direct_item_txn_rec.currency_code;
select decode(nvl(fc.minimum_accountable_unit,0),0,
round(direct_item_txn_rec.act_res_rate * direct_item_txn_rec.qty ,fc.precision),
round(direct_item_txn_rec.act_res_rate * direct_item_txn_rec.qty /fc.minimum_accountable_unit)
*fc.minimum_accountable_unit),
( decode(nvl(fc.minimum_accountable_unit,0),0,
round(direct_item_txn_rec.act_res_rate * direct_item_txn_rec.qty ,fc.precision),
round(direct_item_txn_rec.act_res_rate * direct_item_txn_rec.qty /fc.minimum_accountable_unit)
*fc.minimum_accountable_unit)) / nvl(direct_item_txn_rec.currency_conversion_rate,1)
into l_base_txn_value,
l_txn_value
from fnd_currencies fc
where currency_code = direct_item_txn_rec.currency_code;
fnd_file.put_line(fnd_file.log,'Insert RI account ' || to_char(direct_item_txn_rec.rcv_acct_id));
insert_direct_item_distr (
p_api_version => 1.0,
p_txn_id => direct_item_txn_rec.transaction_id,
p_ref_acct => direct_item_txn_rec.rcv_acct_id,
p_txn_value => -1 * l_txn_value,
p_base_txn_value => -1 * l_base_txn_value,
p_wip_entity_id => direct_item_txn_rec.wip_entity_id,
p_acct_line_type => l_acct_line_type,
p_prg_appl_id => p_prg_appl_id,
p_prg_id => p_prg_id,
p_request_id => p_request_id,
p_user_id => p_user_id,
p_login_id => p_login_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
select decode(l_cost_element, 1, nvl(material_account,-1),
3, nvl(resource_account, -1),
4, nvl(outside_processing_account, -1), -1)
into l_wip_acct
from wip_discrete_jobs
where wip_entity_id = direct_item_txn_rec.wip_entity_id;
fnd_file.put_line(fnd_file.log,'Insert WIP material acct ' || to_char(l_wip_acct));
insert_direct_item_distr (
p_api_version => 1.0,
p_txn_id => direct_item_txn_rec.transaction_id,
p_ref_acct => l_wip_acct,
p_txn_value => l_txn_value,
p_base_txn_value => l_base_txn_value,
p_wip_entity_id => direct_item_txn_rec.wip_entity_id,
p_acct_line_type => 7,
p_prg_appl_id => p_prg_appl_id,
p_prg_id => p_prg_id,
p_request_id => p_request_id,
p_user_id => p_user_id,
p_login_id => p_login_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
fnd_file.put_line(fnd_file.log,'Update wip_period_balances');
update_wip_period_balances (
p_api_version => 1.0,
p_wip_entity_id => direct_item_txn_rec.wip_entity_id,
p_acct_period_id => direct_item_txn_rec.acct_period_id,
p_txn_id => direct_item_txn_rec.transaction_id,
p_prg_appl_id => p_prg_appl_id,
p_prg_id => p_prg_id,
p_request_id => p_request_id,
p_user_id => p_user_id,
p_login_id => p_login_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
fnd_file.put_line(fnd_file.log,'Update eamcost');
update_eamCost (
p_api_version => 1.0,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_txn_mode => 4, /* Direct Item */
p_period_id => direct_item_txn_rec.acct_period_id,
p_org_id => direct_item_txn_rec.organization_id,
p_wip_entity_id => direct_item_txn_rec.wip_entity_id,
p_opseq_num => direct_item_txn_rec.opseq_num,
p_value_type => 1,
/* Bug 2924311: the following parameter should contain the base transaction value */
p_value => l_base_txn_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prg_id,
p_prog_app_id => p_prg_appl_id,
p_login_id => p_login_id,
p_txn_date => direct_item_txn_rec.txn_date,
p_txn_id => direct_item_txn_rec.transaction_id
);
/* insert_direct_item_txn */
l_stmt_num := 130;
fnd_file.put_line(fnd_file.log,'Insert direct item transaction');
insert_direct_item_txn (
p_api_version => 1.0,
p_group_id => p_group_id,
p_prg_appl_id => p_prg_appl_id,
p_prg_id => p_prg_id,
p_request_id => p_request_id,
p_user_id => p_user_id,
p_login_id => p_login_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
fnd_file.put_line(fnd_file.log,'Delete from wcti');
delete from wip_cost_txn_interface
where group_id = p_group_id
and process_status = 2;
PROCEDURE insert_direct_item_distr (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
p_txn_id IN NUMBER,
p_ref_acct IN NUMBER,
p_txn_value IN NUMBER,
p_base_txn_value IN NUMBER,
p_wip_entity_id IN NUMBER,
p_acct_line_type IN NUMBER,
p_prg_appl_id IN NUMBER,
p_prg_id IN NUMBER,
p_request_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'insert_direct_item_distr';
SAVEPOINT insert_direct_item_distr_PUB;
select organization_id, currency_code
into l_org_id,l_currency
from wip_cost_txn_interface
where transaction_id = p_txn_id;
/* The following select statement will be modified to refer to
cst_organization_definitions as an impact of the HR-PROFILE option. */
select ood.currency_code
into l_func_currency
from cst_organization_definitions ood
where ood.organization_id = l_org_id;
select decode(minimum_accountable_unit,null,
decode(precision, null, p_base_txn_value, round(p_base_txn_value,precision)),
0, decode(precision, null, p_base_txn_value, round(p_base_txn_value,precision)),
round(p_base_txn_value/minimum_accountable_unit) * minimum_accountable_unit)
into l_base_txn_value
from fnd_currencies
where currency_code = l_func_currency;
select decode(minimum_accountable_unit,null,
decode(precision, null, p_txn_value, round(p_txn_value,precision)),
0, decode(precision, null, p_txn_value, round(p_txn_value,precision)),
round(p_txn_value/minimum_accountable_unit) * minimum_accountable_unit)
into l_txn_value
from fnd_currencies
where currency_code = l_currency;
select decode(minimum_accountable_unit,null,
decode(precision, null, p_base_txn_value, round(p_base_txn_value,precision)),
0, decode(precision, null, p_base_txn_value, round(p_base_txn_value,precision)),
round((p_base_txn_value)/minimum_accountable_unit) * minimum_accountable_unit)
into l_base_txn_value
from fnd_currencies
where currency_code = l_func_currency;
Insert into wip_transaction_accounts (
wip_sub_ledger_id,
transaction_id,
reference_account,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
transaction_date,
wip_entity_id,
accounting_line_type,
transaction_value,
base_transaction_value,
primary_quantity,
rate_or_amount,
basis_type,
cost_element_id,
currency_code,
currency_conversion_date,
currency_conversion_type,
currency_conversion_rate,
request_id,
program_application_id,
program_id,
program_update_date)
select CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
p_txn_id,
p_ref_acct,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
wcti.organization_id,
wcti.transaction_date,
p_wip_entity_id,
p_acct_line_type,
l_txn_value,
l_base_txn_value,
decode(wcti.source_code,'IPV',NULL,wcti.primary_quantity),
wcti.actual_resource_rate,
1,
l_cost_element,
wcti.currency_code,
wcti.currency_conversion_date,
wcti.currency_conversion_type,
wcti.currency_conversion_rate,
p_request_id,
p_prg_appl_id,
p_prg_id,
sysdate
FROM wip_cost_txn_interface wcti
WHERE wcti.transaction_id = p_txn_id;
ROLLBACK TO insert_direct_item_distr_PUB;
ROLLBACK TO insert_direct_item_distr_PUB;
ROLLBACK TO insert_direct_item_distr_PUB;
fnd_file.put_line(fnd_file.log,'CST_eamCost_PUB.insert_direct_item_distr(' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,240));
, 'insert_direct_item_distr : Statement - '|| to_char(l_stmt_num)
);
END insert_direct_item_distr;
PROCEDURE update_wip_period_balances (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_wip_entity_id IN NUMBER,
p_acct_period_id IN NUMBER,
p_txn_id IN NUMBER,
p_prg_appl_id IN NUMBER,
p_prg_id IN NUMBER,
p_request_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_wip_period_balances';
l_update_stmt VARCHAR2(2000) := NULL;
SAVEPOINT update_wip_period_balances_PUB;
select sum(nvl(base_transaction_value,0))
into l_txn_value
from wip_transaction_accounts
where transaction_id = p_txn_id
and accounting_line_type = 7;
/* Bug 4321505 - Modified the dynamic update query to remove literals
in the SQL and use bind variables instead. This is to make the SQL
comply with PL/SQL Standards */
if l_cost_element = 1 then
l_update_stmt := 'UPDATE wip_period_balances ' ||
'SET pl_material_in = nvl( pl_material_in, 0) + :l_txn_value , ' ||
'last_update_date = sysdate, ' ||
'last_updated_by = :p_user_id, ' ||
'last_update_login = :p_login_id, ' ||
'request_id = :p_request_id, ' ||
'program_application_id = :p_prg_appl_id, ' ||
'program_id =:p_prg_id, ' ||
'program_update_date = sysdate ' ||
'WHERE wip_entity_id = :p_wip_entity_id ' ||
' AND acct_period_id = :p_acct_period_id ';
l_update_stmt := 'UPDATE wip_period_balances ' ||
'SET tl_resource_in = nvl( tl_resource_in, 0) + :l_txn_value , ' ||
'last_update_date = sysdate, ' ||
'last_updated_by = :p_user_id, ' ||
'last_update_login = :p_login_id, ' ||
'request_id = :p_request_id, ' ||
'program_application_id = :p_prg_appl_id, ' ||
'program_id =:p_prg_id, ' ||
'program_update_date = sysdate ' ||
'WHERE wip_entity_id = :p_wip_entity_id ' ||
' AND acct_period_id = :p_acct_period_id ';
l_update_stmt := 'UPDATE wip_period_balances ' ||
'SET tl_outside_processing_in = ' ||
'nvl( tl_outside_processing_in, 0) + :l_txn_value , ' ||
'last_update_date = sysdate, ' ||
'last_updated_by = :p_user_id, ' ||
'last_update_login = :p_login_id, ' ||
'request_id = :p_request_id, ' ||
'program_application_id = :p_prg_appl_id, ' ||
'program_id =:p_prg_id, ' ||
'program_update_date = sysdate ' ||
'WHERE wip_entity_id = :p_wip_entity_id ' ||
' AND acct_period_id = :p_acct_period_id ';
EXECUTE IMMEDIATE l_update_stmt USING
l_txn_value, p_user_id, p_login_id, p_request_id, p_prg_appl_id, p_prg_id,
p_wip_entity_id, p_acct_period_id;
ROLLBACK TO update_wip_period_balances_PUB;
ROLLBACK TO update_wip_period_balances_PUB;
ROLLBACK TO update_wip_period_balances_PUB;
( 'CST_eamCost_PUB',' update_wip_period_balances : Statement - ' || to_char(l_statement));
END update_wip_period_balances;
PROCEDURE insert_direct_item_txn (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
p_group_id IN NUMBER,
p_prg_appl_id IN NUMBER,
p_prg_id IN NUMBER,
p_request_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'insert_direct_item_txn';
SAVEPOINT insert_direct_item_txn_PUB;
fnd_file.put_line(fnd_file.log,'Insert into WT');
/* Insert Currency_Actual_Resource_Rate also - Bug 2719622 */
insert into wip_transactions (
transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
wip_entity_id,
primary_item_id,
acct_period_id,
department_id,
transaction_type,
transaction_date,
line_id,
source_code,
source_line_id,
operation_seq_num,
standard_rate_flag,
usage_rate_or_amount,
basis_type,
transaction_quantity,
transaction_uom,
primary_quantity,
primary_uom,
actual_resource_rate,
currency_actual_resource_rate,
currency_code,
currency_conversion_date,
currency_conversion_type,
currency_conversion_rate,
reason_id,
reference,
po_header_id,
po_line_id,
rcv_transaction_id,
request_id,
program_application_id,
program_id,
pm_cost_collected,
project_id,
task_id )
select wcti.transaction_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
wcti.organization_id,
wcti.wip_entity_id,
wcti.primary_item_id,
wcti.acct_period_id,
wcti.department_id,
17,
wcti.transaction_date,
wcti.line_id,
wcti.source_code,
wcti.source_line_id,
wcti.operation_seq_num,
wcti.standard_rate_flag,
wcti.usage_rate_or_amount,
wcti.basis_type,
decode(wcti.source_code,'IPV',NULL,wcti.transaction_quantity),
wcti.transaction_uom,
decode(wcti.source_code,'IPV',NULL,wcti.primary_quantity),
wcti.primary_uom,
wcti.actual_resource_rate,
wcti.currency_actual_resource_rate,
wcti.currency_code,
wcti.currency_conversion_date,
wcti.currency_conversion_type,
wcti.currency_conversion_rate,
wcti.reason_id,
wcti.reference,
wcti.po_header_id,
wcti.po_line_id,
wcti.rcv_transaction_id,
p_request_id,
p_prg_appl_id,
p_prg_id,
'N',
wcti.project_id,
wcti.task_id
from wip_cost_txn_interface wcti
where group_id = p_group_id
and process_status = 2;
ROLLBACK TO insert_direct_item_txn_PUB;
ROLLBACK TO insert_direct_item_txn_PUB;
ROLLBACK TO insert_direct_item_txn_PUB;
fnd_file.put_line(fnd_file.log,'CST_eamCost_PUB.insert_direct_item_txn: Statement(' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,240));
, 'insert_direct_item_txn : Statement - '|| to_char(l_stmt_num)
);
END insert_direct_item_txn;
select entity_type
into l_entity_type
from wip_entities
where wip_entity_id = p_wip_entity_id;
select nvl(material_account,-1)
into l_material_account
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
select wcti.transaction_id,
wcti.organization_id,
wcti.wip_entity_id,
wcti.acct_period_id,
to_char(wcti.transaction_date,'YYYY/MM/DD HH24:MI:SS') txn_date,
cii.inventory_item_id,
cii.serial_number,
wdj.class_code,
wdj.primary_item_id,
wdj.project_id,
wdj.task_id,
wdj.maintenance_object_id
from wip_cost_txn_interface wcti,
wip_discrete_jobs wdj,
wip_entities we,
csi_item_instances cii
where wcti.group_id = p_wcti_group_id
and wdj.wip_entity_id = wcti.wip_entity_id
and we.wip_entity_id = wcti.wip_entity_id
and we.entity_type in (6,7)
and cii.instance_id = wdj.maintenance_object_id
and wdj.maintenance_object_type = 3
and cii.network_asset_flag = 'Y';
select cii.inventory_item_id,
cii.serial_number,
msn.gen_object_id
from csi_item_instances cii,
eam_work_order_route ewor,
mtl_serial_numbers msn,
wip_discrete_jobs wdj
where wdj.organization_id = p_org_id
and ewor.wip_entity_id = p_wip_entity_id
and ewor.wip_entity_id = wdj.wip_entity_id
and cii.instance_id = ewor.instance_id
and msn.inventory_item_id = cii.inventory_item_id
and msn.serial_number = cii.serial_number;
SELECT DISTINCT COD.currency_code
INTO l_currency_code
FROM cst_organization_definitions COD,
wip_cost_txn_interface WCTI
WHERE WCTI.group_id = p_wcti_group_id
AND WCTI.organization_id = COD.organization_id;
select count(*)
into l_number_members
from EAM_WORK_ORDER_ROUTE ewor
where ewor.wip_entity_id = c_route_rec.wip_entity_id;
select
-1* SUM(NVL(wpb.pl_material_out,0)
- NVL(wpb.pl_material_in,0)
+ NVL(wpb.pl_material_var,0)
+ NVL(wpb.pl_material_overhead_out,0)
- NVL(wpb.pl_material_overhead_in,0)
+ NVL(wpb.pl_material_overhead_var,0)
+ NVL(wpb.pl_resource_out,0)
- NVL(wpb.pl_resource_in,0)
+ NVL(wpb.pl_resource_var,0)
+ NVL(wpb.pl_overhead_out,0)
- NVL(wpb.pl_overhead_in,0)
+ NVL(wpb.pl_overhead_var,0)
+ NVL(wpb.pl_outside_processing_out,0)
- NVL(wpb.pl_outside_processing_in,0)
+ NVL(wpb.pl_outside_processing_var,0)
+ NVL(wpb.tl_material_out,0)
- 0
+ NVL(wpb.tl_material_var,0)
+ NVL(wpb.tl_material_overhead_out,0)
- 0
+ NVL(wpb.tl_material_overhead_var,0)),
SUM(NVL(wpb.tl_resource_in,0)
- NVL(wpb.tl_resource_out,0)
- NVL(wpb.tl_resource_var,0)),
SUM(NVL(wpb.tl_outside_processing_in,0)
- NVL(wpb.tl_outside_processing_out,0)
- NVL(wpb.tl_outside_processing_var,0)),
SUM(NVL(wpb.tl_overhead_in,0)
- NVL(wpb.tl_overhead_out,0)
- NVL(wpb.tl_overhead_var,0))
INTO l_pl_var,
l_res_var,
l_osp_var,
l_ovh_var
from wip_period_balances wpb
where wpb.wip_entity_id = c_route_rec.wip_entity_id
and wpb.acct_period_id <= c_route_rec.acct_period_id;
select material_variance_account,
resource_variance_account,
outside_proc_variance_account,
overhead_variance_account
into l_mtl_var_acct,
l_res_var_acct,
l_osp_var_acct,
l_ovh_var_acct
from wip_accounting_classes
where class_code = l_class_code
and organization_id = c_route_rec.organization_id;
INSERT INTO wip_transaction_accounts
(TRANSACTION_ID, REFERENCE_ACCOUNT,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, ORGANIZATION_ID,
TRANSACTION_DATE, WIP_ENTITY_ID,
ACCOUNTING_LINE_TYPE, BASE_TRANSACTION_VALUE,
CONTRA_SET_ID, COST_ELEMENT_ID,
REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE)
SELECT
c_route_rec.transaction_id,
decode(cce.cost_element_id,
1,l_mtl_var_acct,
3,l_res_var_acct,
4,l_osp_var_acct,
5,l_ovh_var_acct),
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
c_route_rec.organization_id,
to_date(c_route_rec.txn_date,'YYYY/MM/DD HH24:MI:SS'),
c_route_rec.wip_entity_id,
8, -- accounting_line_type is WIP variance,
ROUND((decode(cce.cost_element_id,
1, l_pl_var,
3, l_res_var,
4, l_osp_var,
5, l_ovh_var) * l_weightage_factor)/l_min_acct_unit) * l_min_acct_unit,
c_ewor_rec.gen_object_id,
cce.cost_element_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE
FROM cst_cost_elements cce
WHERE cce.cost_element_id <> 2
GROUP BY cce.cost_element_id
HAVING decode(cce.cost_element_id,
1, l_pl_var,
3, l_res_var,
4, l_osp_var,
5, l_ovh_var) * l_weightage_factor <> 0;
INSERT INTO wip_transaction_accounts
(TRANSACTION_ID, REFERENCE_ACCOUNT,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, ORGANIZATION_ID,
TRANSACTION_DATE, WIP_ENTITY_ID,
ACCOUNTING_LINE_TYPE,
BASE_TRANSACTION_VALUE,
CONTRA_SET_ID, COST_ELEMENT_ID,
REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE)
SELECT
c_route_rec.transaction_id,
decode(cce.cost_element_id,
1, wdj.material_variance_account,
3, wdj.resource_variance_account,
4, wdj.outside_proc_variance_account,
5, wdj.overhead_variance_account),
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
c_route_rec.organization_id,
to_date(c_route_rec.txn_date,'YYYY/MM/DD HH24:MI:SS'),
c_route_rec.wip_entity_id,
8,
decode(cce.cost_element_id,
1,l_pl_var_total,
3,l_res_var_total,
4,l_osp_var_total,
5,l_ovh_var_total) * -1,
c_route_rec.maintenance_object_id,
cce.cost_element_id, -- CE
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE
FROM cst_cost_elements cce,
wip_discrete_jobs wdj
where cce.cost_element_id <> 2
and wdj.wip_entity_id = c_route_rec.wip_entity_id
group by cce.cost_element_id,
decode(cce.cost_element_id,
1, wdj.material_variance_account,
3, wdj.resource_variance_account,
4, wdj.outside_proc_variance_account,
5, wdj.overhead_variance_account)
having decode(cce.cost_element_id,
1,l_pl_var_total,
3,l_res_var_total,
4,l_osp_var_total,
5,l_ovh_var_total) <> 0;
UPDATE WIP_TRANSACTION_ACCOUNTS
SET WIP_SUB_LEDGER_ID = CST_WIP_SUB_LEDGER_ID_S.NEXTVAL
WHERE TRANSACTION_ID = c_route_rec.transaction_id;
SELECT wdj.parent_wip_entity_id,
pwdj.maintenance_object_type,
pwdj.maintenance_object_id,
pcii.network_asset_flag,
pcii.inventory_item_id,
pcii.serial_number,
wdj.maintenance_object_type,
wdj.maintenance_object_id,
cii.inventory_item_id,
cii.serial_number
INTO l_parent_wip_entity_id,
l_parent_maint_object_type,
l_parent_maint_object_id,
l_parent_network_asset_flag,
l_parent_inventory_item_id,
l_parent_serial_number,
l_maint_object_type,
l_maint_object_id,
l_inventory_item_id,
l_serial_number
FROM wip_discrete_jobs wdj,
wip_discrete_jobs pwdj,
csi_item_instances cii,
csi_item_instances pcii
WHERE wdj.wip_entity_id = p_wip_entity_id
AND cii.instance_id (+) = wdj.maintenance_object_id
AND pwdj.wip_entity_id (+) = wdj.parent_wip_entity_id
AND pcii.instance_id (+) = pwdj.maintenance_object_id;
rows storing the value of 1 will be updated to 3 as part of the project */
-- Set the output depending on the work order type and the network asset flag
l_stmt_num := 20;
select nvl(wcti.po_header_id, -1),
nvl(wcti.po_line_id, -1),
nvl(wcti.rcv_transaction_id, -1)
into l_po_header_id,
l_po_line_id,
l_rcv_txn_id
from wip_cost_txn_interface wcti
where wcti.transaction_id = p_txn_id;
select nvl(wt.po_header_id, -1),
nvl(wt.po_line_id, -1),
nvl(wt.rcv_transaction_id, -1)
into l_po_header_id,
l_po_line_id,
l_rcv_txn_id
from wip_transactions wt
where wt.transaction_id = p_txn_id;
select pha.approved_date,
type_lookup_code
into l_approved_date,
l_type_lookup_code
from po_headers_all pha
where pha.po_header_id = l_po_header_id;
select nvl(pla.category_id, -1)
into l_category_id
from po_lines_all pla
where pla.po_line_id = l_po_line_id;
select po_release_id
into l_po_release_id
from rcv_transactions
where transaction_id = l_rcv_txn_id;
select approved_date
into l_approved_date
from po_releases_all
where po_release_id = l_po_release_id;
select cceea.mnt_cost_element_id
into l_cost_element_id
from cst_cat_ele_exp_assocs cceea
where cceea.category_id = l_category_id
and l_approved_date >= cceea.start_date
and l_approved_date < (nvl(cceea.end_date,sysdate) + 1);
select cceea.mfg_cost_element_id
into l_cost_element_id
from cst_cat_ele_exp_assocs cceea
where cceea.category_id = l_category_id
and l_approved_date >= cceea.start_date
and l_approved_date < (nvl(cceea.end_date, sysdate) + 1);
select nvl(wt.po_header_id, -1),
nvl(wt.po_line_id, -1)
into l_po_header_id,
l_po_line_id
from wip_transactions wt
where wt.transaction_id = p_txn_id;
select pha.approved_date
into l_approved_date
from po_headers_all pha
where pha.po_header_id = l_po_header_id;
select nvl(pla.category_id, -1)
into l_category_id
from po_lines_all pla
where pla.po_line_id = l_po_line_id;
select pet.expenditure_type
into l_expenditure_type
from cst_cat_ele_exp_assocs cceea,
pa_expenditure_types pet
where cceea.category_id = l_category_id
and l_approved_date >= cceea.start_date
and l_approved_date < (nvl(cceea.end_date, sysdate) + 1)
and cceea.expenditure_type_id = pet.expenditure_type_id;
SELECT count(*)
INTO l_object_type_count
FROM cst_eam_hierarchy_snapshot
WHERE group_id = p_group_id
AND (object_type IS NULL OR parent_object_type IS NULL);
SELECT MAX(level_num)
INTO l_max_level
FROM cst_eam_hierarchy_snapshot
WHERE group_id = p_group_id;
DELETE cst_eam_rollup_costs
WHERE group_id = p_group_id;
p_error_text => 'Deleted ' || SQL%ROWCOUNT || ' existing calculation for ' ||
' group id '|| p_group_id
);
INSERT
INTO cst_eam_rollup_costs(
group_id,
object_type,
object_id,
period_set_name,
period_name,
maint_cost_category,
actual_mat_cost,
actual_lab_cost,
actual_eqp_cost,
estimated_mat_cost,
estimated_lab_cost,
estimated_eqp_cost,
last_update_date,
last_updated_by,
creation_date,
creation_by,
program_application_id
)
SELECT TEMP.group_id,
TEMP.object_type,
TEMP.object_id,
TEMP.period_set_name,
TEMP.period_name,
TEMP.maint_cost_category,
SUM(TEMP.actual_mat_cost),
SUM(TEMP.actual_lab_cost),
SUM(TEMP.actual_eqp_cost),
SUM(TEMP.estimated_mat_cost),
SUM(TEMP.estimated_lab_cost),
SUM(TEMP.estimated_eqp_cost),
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_prog_appl_id
FROM (
SELECT CURR.group_id group_id,
CURR.object_type object_type,
CURR.object_id object_id,
WEPB.period_set_name period_set_name,
WEPB.period_name period_name,
WEPB.maint_cost_category maint_cost_category,
SUM(NVL(WEPB.actual_mat_cost,0)) actual_mat_cost,
SUM(NVL(WEPB.actual_lab_cost,0)) actual_lab_cost,
SUM(NVL(WEPB.actual_eqp_cost,0)) actual_eqp_cost,
SUM(NVL(WEPB.system_estimated_mat_cost,0)) estimated_mat_cost,
SUM(NVL(WEPB.system_estimated_lab_cost,0)) estimated_lab_cost,
SUM(NVL(WEPB.system_estimated_eqp_cost,0)) estimated_eqp_cost
FROM cst_eam_hierarchy_snapshot CURR,
wip_eam_period_balances WEPB
WHERE CURR.group_id = p_group_id
AND CURR.level_num = l_level
AND CURR.object_type = 2 -- WIP job
AND WEPB.organization_id = p_organization_id
AND WEPB.wip_entity_id = CURR.object_id
GROUP
BY CURR.group_id,
CURR.object_type,
CURR.object_id,
WEPB.period_set_name,
WEPB.period_name,
WEPB.maint_cost_category
UNION ALL
SELECT CURR.group_id,
CURR.object_type,
CURR.object_id,
CERC.period_set_name,
CERC.period_name,
CERC.maint_cost_category,
SUM(NVL(CERC.actual_mat_cost,0)),
SUM(NVL(CERC.actual_lab_cost,0)),
SUM(NVL(CERC.actual_eqp_cost,0)),
SUM(NVL(CERC.estimated_mat_cost,0)),
SUM(NVL(CERC.estimated_lab_cost,0)),
SUM(NVL(CERC.estimated_eqp_cost,0))
FROM cst_eam_hierarchy_snapshot CURR,
cst_eam_hierarchy_snapshot CHILDREN,
cst_eam_rollup_costs CERC
WHERE CURR.group_id = p_group_id
AND CURR.level_num = l_level
AND CHILDREN.group_id = p_group_id
AND CHILDREN.parent_object_type = CURR.object_type
AND CHILDREN.parent_object_id = CURR.object_id
AND CERC.group_id = p_group_id
AND CERC.object_type = CHILDREN.object_type
AND CERC.object_id = CHILDREN.object_id
GROUP
BY CURR.group_id,
CURR.object_type,
CURR.object_id,
CERC.period_set_name,
CERC.period_name,
CERC.maint_cost_category
) TEMP
GROUP
BY TEMP.group_id,
TEMP.object_type,
TEMP.object_id,
TEMP.period_set_name,
TEMP.period_name,
TEMP.maint_cost_category;
p_error_text => 'Object type must be inserted. Use 0 instead of '||
'NULL for entities that are not a WIP entity'
);
p_last_update_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Purge_RollupCost';
DELETE cst_eam_hierarchy_snapshot
WHERE group_id = NVL(p_group_id,group_id)
AND program_application_id =
NVL(p_prog_appl_id,program_application_id)
AND last_update_date < NVL(p_last_update_date,last_update_date+1);
': Successfully deleted '||
SQL%ROWCOUNT||
' from CST_EAM_HIERARHCY_SNAPSHOT'
);
DELETE cst_eam_rollup_costs
WHERE group_id = NVL(p_group_id,group_id)
AND program_application_id =
NVL(p_prog_appl_id,program_application_id)
AND last_update_date < NVL(p_last_update_date,last_update_date+1);
': Successfully deleted '||
SQL%ROWCOUNT||
' from CST_EAM_ROLLUP_COSTS'
);
PROCEDURE Insert_eamBalAcct
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_period_id IN NUMBER,
p_period_set_name IN VARCHAR2,
p_period_name IN VARCHAR2,
p_org_id IN NUMBER,
p_wip_entity_id IN NUMBER,
p_owning_dept_id IN NUMBER,
p_dept_id IN NUMBER,
p_maint_cost_cat IN NUMBER,
p_opseq_num IN NUMBER,
p_period_start_date IN DATE,
p_account_ccid IN NUMBER,
p_value IN NUMBER,
p_txn_type IN NUMBER,
p_wip_acct_class IN VARCHAR2,
p_mfg_cost_element_id IN NUMBER,
p_user_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_id IN NUMBER,
p_prog_app_id IN NUMBER,
p_login_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_eamBalAcct';
SAVEPOINT Insert_eamBalAcct_PUB;
/* Update the record if already exists else insert a new one */
MERGE INTO CST_EAM_BALANCE_BY_ACCOUNTS cebba
USING
(
SELECT NULL FROM DUAL
)
ON
(
cebba.period_set_name = period_set_name AND
cebba.period_name = p_period_name AND
cebba.acct_period_id = p_period_id AND
cebba.wip_entity_id = p_wip_entity_id AND
cebba.organization_id = p_org_id AND
cebba.maint_cost_category = p_maint_cost_cat AND
cebba.owning_dept_id = p_owning_dept_id AND
cebba.period_start_date = p_period_start_date AND
cebba.account_id = p_account_ccid AND
cebba.txn_type = p_txn_type AND
cebba.wip_acct_class_code = p_wip_acct_class AND
cebba.mfg_cost_element_id = p_mfg_cost_element_id
)
WHEN MATCHED THEN
UPDATE
SET cebba.acct_value = cebba.acct_value + p_value,
cebba.LAST_UPDATE_DATE = sysdate,
cebba.LAST_UPDATED_BY = p_user_id,
cebba.LAST_UPDATE_LOGIN = p_login_id
WHEN NOT MATCHED THEN
Insert
(
PERIOD_SET_NAME,
PERIOD_NAME,
ACCT_PERIOD_ID,
WIP_ENTITY_ID,
ORGANIZATION_ID,
OPERATIONS_DEPT_ID,
OPERATIONS_SEQ_NUM,
MAINT_COST_CATEGORY,
OWNING_DEPT_ID,
PERIOD_START_DATE,
ACCOUNT_ID,
ACCT_VALUE,
TXN_TYPE,
WIP_ACCT_CLASS_CODE,
MFG_COST_ELEMENT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)VALUES
(
p_period_set_name,
p_period_name ,
p_period_id ,
p_wip_entity_id,
p_org_id ,
p_dept_id,
p_opseq_num ,
p_maint_cost_cat,
p_owning_dept_id,
p_period_start_date,
p_account_ccid,
p_value ,
p_txn_type,
p_wip_acct_class,
p_mfg_cost_element_id,
sysdate,
p_user_id ,
sysdate,
p_prog_app_id ,
p_login_id
);
ROLLBACK TO Insert_eamBalAcct_PUB;
ROLLBACK TO Insert_eamBalAcct_PUB;
END Insert_eamBalAcct;
PROCEDURE Delete_eamBalAcct
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
p_org_id IN NUMBER,
p_entity_id_tab IN CSTPECEP.wip_entity_id_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_eamBalAcct';
SAVEPOINT Delete_eamBalAcct_PUB;
/* Delete data from CST_EAM_BALANCE_BY_ACCOUNTS */
FORALL l_index IN p_entity_id_tab.FIRST..p_entity_id_tab.LAST
Delete from CST_EAM_BALANCE_BY_ACCOUNTS
where wip_entity_id = p_entity_id_tab(l_index)
and organization_id=p_org_id;
ROLLBACK TO Delete_eamBalAcct_PUB;
ROLLBACK TO Delete_eamBalAcct_PUB;
END Delete_eamBalAcct;
PROCEDURE Insert_tempEstimateDetails
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_entity_id_tab IN CSTPECEP.wip_entity_id_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_eamBalAcct';
select project_id,
purchasing_ou_id,
receiving_ou_id,
organization_id,
document_type,
currency_code,
currency_rate,
currency_date,
currency_type,
txn_flow_header_id,
unit_price,
set_of_books_id,
order_type_lookup_code,
amount,
amount_delivered
from cst_eam_direct_items_temp
where purchasing_ou_id <> receiving_ou_id
FOR UPDATE;
SAVEPOINT Insert_tempEstimateDetails_PUB;
/* Insert rows for POs for Direct Items */
FORALL l_index in p_entity_id_tab.FIRST..p_entity_id_tab.LAST
INSERT INTO cst_eam_direct_items_temp
SELECT
pd.wip_entity_id,
pd.wip_operation_seq_num,
pd.destination_organization_id,
wo.department_id,
to_number(null),
poh.segment1 ,
pd.item_description,
uom.uom_code,
pd.unit_price,
to_number(null),
sum(pd.quantity_ordered) ,
sum(pd.quantity_delivered),
to_date(null),
sum(pd.quantity_ordered),
sum(pd.quantity_cancelled),
to_char(null),
to_number(null),
pd.line_location_id,
pd.cancel_flag,
pd.item_id,
null, -- rql.closed_code,
pd.closed_code,
null, -- rqh.authorization_status,
poh.authorization_status,
pd.po_line_id,
poh.po_header_id,
to_number(null), -- rqh.requisition_header_id,
wed.direct_item_sequence_id,
pd.category_id,
pd.po_release_id,
to_number(null), -- rql.requisition_line_id,
pd.order_type_lookup_code,
pd.amount_ordered,
pd.amount_delivered,
pd.req_distribution_id,
poh.approved_date,
pd.project_id, -- PROJECT_ID
pd.org_id, -- PURCHASING_OU_ID
to_number(org_information1), -- SET_OF_BOOKS_ID
to_number(NULL), -- TXN_FLOW_HEADER_ID
to_number(hoi.org_information3), -- RECEIVING_OU_ID
poh.currency_code, -- CURRENCY_CODE
poh.rate_date, -- CURRENCY_DATE
poh.rate_type, -- CURRENCY_TYPE
pd.rate, -- CURRENCY_RATE ,
poh.type_lookup_code, -- DOCUMENT_TYPE
to_char(null) -- IS_SHARED_PROC
FROM
po_line_types plt,
mtl_units_of_measure uom,
po_headers_all poh,
wip_eam_direct_items wed,
hr_organization_information hoi,
wip_operations wo,
(SELECT
pd1.wip_entity_id,
pd1.wip_operation_seq_num,
pd1.destination_organization_id,
pd1.wip_line_id,
pol.item_description,
pol.unit_price,
pd1.quantity_ordered,
pd1.quantity_cancelled,
pd1.quantity_delivered,
pd1.line_location_id,
pol.cancel_flag,
pol.item_id,
pol.closed_code,
pol.po_line_id,
pol.category_id,
pd1.po_release_id,
pol.order_type_lookup_code,
pd1.amount_ordered,
pd1.amount_delivered,
pd1.req_distribution_id,
pd1.rate,
pol.unit_meas_lookup_code,
pd1.destination_type_code,
pol.line_type_id,
pd1.po_header_id,
pd1.project_id,
pol.org_id
FROM po_lines_all pol,
po_distributions_all pd1
WHERE pol.po_line_id = pd1.po_line_id AND
pd1.wip_entity_id = p_entity_id_tab(l_index)
) pd
WHERE
pd.po_line_id = pd.po_line_id AND
pd.wip_entity_id = p_entity_id_tab(l_index) AND
poh.po_header_id = pd.po_header_id AND
pd.line_type_id = plt.line_type_id AND
upper(nvl(plt.outside_operation_flag, 'N')) = 'N' AND
pd.destination_type_code = 'SHOP FLOOR' AND
pd.unit_meas_lookup_code = uom.unit_of_measure (+) AND
upper(nvl(pd.cancel_flag, 'N')) <> 'Y' AND
pd.wip_entity_id IS NOT NULL AND
pd.item_description = wed.description(+) AND
pd.wip_entity_id = wed.wip_entity_id (+) AND
pd.wip_operation_seq_num = wed. operation_seq_num (+) AND
pd.destination_organization_id = wed.organization_id (+) AND
hoi.organization_id = pd.destination_organization_id AND
hoi.org_information_context = 'Accounting Information' AND
wo.wip_entity_id(+) = p_entity_id_tab(l_index) AND
wo.organization_id(+) = pd.destination_organization_id AND
wo.operation_seq_num(+) = pd.wip_operation_seq_num
GROUP BY pd.wip_entity_id,
pd.wip_operation_seq_num,
pd.destination_organization_id,
wo.department_id,
poh.segment1,
pd.item_description,
uom.uom_code,
pd.order_type_lookup_code,
pd.unit_price,
pd.amount_ordered,
pd.amount_delivered,
poh.currency_code,
pd.cancel_flag,
pd.item_id,
pd.closed_code,
poh.authorization_status,
pd.po_line_id,
poh.po_header_id,
wed.direct_item_sequence_id,
pd.category_id,
pd.po_release_id,
pd.req_distribution_id,
pd.rate,
poh.approved_date,
pd.wip_line_id,
pd.line_location_id,
pd.project_id,
pd.org_id, -- PURCHASING_OU_ID
to_number(org_information1), -- SET_OF_BOOKS_ID
to_number(hoi.org_information3), -- RECEIVING_OU_ID
poh.currency_code, -- CURRENCY_CODE
poh.rate_date, -- CURRENCY_DATE
poh.rate_type, -- CURRENCY_TYPE
pd.rate, -- CURRENCY_RATE ,
poh.type_lookup_code; -- DOCUMENT_TYPE
UPDATE cst_eam_direct_items_temp cedi
SET TXN_FLOW_HEADER_ID =(SELECT transaction_flow_header_id
FROM po_line_locations_all poll
WHERE poll.line_location_id = cedi.line_location_id)
WHERE cedi.line_location_id is not null;
/* Will insert for Reqs after updation to avoid extra rows that need not participate in updation */
UPDATE cst_eam_direct_items_temp cedi
SET (REQUISITION_NUMBER,
REQ_AUTHORIZATION_STATUS,
REQUISITION_HEADER_ID,
REQUISITION_LINE_ID,
CLOSED_CODE
) = (SELECT rqh.segment1,
rqh.authorization_status,
rqh.requisition_header_id,
rql.requisition_line_id,
rql.closed_code
FROM po_requisition_headers_all rqh,
po_requisition_lines_all rql,
po_req_distributions_all rqd
WHERE rql.requisition_header_id = rqh.requisition_header_id AND
rqd.requisition_line_id = rql.requisition_line_id AND
rqd.distribution_id(+) = cedi.req_distribution_id
)
WHERE cedi.req_distribution_id IS NOT NULL;
/* Insert rows for Reqs for Direct Items */
FORALL l_index in p_entity_id_tab.FIRST..p_entity_id_tab.LAST
INSERT INTO cst_eam_direct_items_temp
SELECT
rql.wip_entity_id,
rql.wip_operation_seq_num,
rql.destination_organization_id,
wo.department_id,
rqh.segment1,
null,
rql.item_description,
uom.uom_code,
rql.unit_price,
rql.quantity,
rql.quantity,
to_number(null),
to_date(null),
to_number(null),
to_number(null),
to_char(null),
to_number(null),
to_number(null),
to_char(null),
rql.item_id,
rql.closed_code,
to_char(null),
rqh.authorization_status,
to_char(null),
to_number(null),
to_number(null),
rqh.requisition_header_id,
wed.direct_item_sequence_id,
rql.category_id,
to_number(null),
rql.requisition_line_id,
rql.order_type_lookup_code,
rql.amount,
to_number(null) ,
to_number(null),
rqh.last_update_date,
to_number(NULL), -- PROJECT_ID
rql.org_id, -- PURCHASING_OU_ID
to_number(hoi.org_information1), -- SET_OF_BOOKS_ID
to_number(NULL), -- TXN_FLOW_HEADER_ID
to_number(hoi.org_information3), -- RECEIVING_OU_ID
rql.currency_code, -- CURRENCY_CODE
rql.rate_date, -- CURRENCY_DATE
rql.rate_type, -- CURRENCY_TYPE
rql.rate, -- CURRENCY_RATE ,
rqh.type_lookup_code, -- DOCUMENT_TYPE
to_char(null) -- IS_SHARED_PROC
FROM
po_requisition_lines_all rql,
po_requisition_headers_all rqh,
po_line_types plt,
mtl_units_of_measure uom,
wip_eam_direct_items wed,
hr_organization_information hoi,
wip_operations wo
WHERE
rql.requisition_header_id = rqh.requisition_header_id AND
rql.line_type_id = plt.line_type_id AND
rql.unit_meas_lookup_code = uom.unit_of_measure (+) AND
upper(rqh.authorization_status) NOT IN ('CANCELLED', 'REJECTED','SYSTEM_SAVED') AND
rql.line_location_id IS NULL AND
upper(nvl(rql.cancel_flag, 'N')) <> 'Y' AND
upper(nvl(plt.outside_operation_flag, 'N')) = 'N' AND
rql.destination_type_code = 'SHOP FLOOR' AND
rql.wip_entity_id IS NOT NULL AND
rql.item_description = wed.description (+) AND
rql.wip_entity_id = wed.wip_entity_id (+) AND
RQL.WIP_OPERATION_SEQ_NUM = WED.OPERATION_SEQ_NUM (+) AND
rql.destination_organization_id = wed.organization_id (+) AND
rql.wip_entity_id = p_entity_id_tab(l_index) AND
hoi.organization_id = rql.destination_organization_id AND
hoi.org_information_context = 'Accounting Information' AND
wo.wip_entity_id(+) = p_entity_id_tab(l_index) AND
wo.organization_id(+) = rql.destination_organization_id AND
wo.operation_seq_num(+) = RQL.WIP_OPERATION_SEQ_NUM ;
SELECT currency_code
INTO l_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = c_cedi_rec.set_of_books_id;
update cst_eam_direct_items_temp
set unit_price = l_unit_price,
currency_rate = l_currency_rate,
currency_code = l_currency_code,
currency_date = l_currency_date,
currency_type = l_currency_type,
is_shared_proc = l_is_shared_proc,
amount = l_amount,
amount_delivered = l_amount_delivered
where current of c_cedi;
ROLLBACK TO Insert_tempEstimateDetails_PUB;
ROLLBACK TO Insert_tempEstimateDetails_PUB;
END Insert_tempEstimateDetails;