[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wip_entity_name
INTO l_wip_entity_name
FROM wip_entities
WHERE wip_entity_id = p_specific_workorder;
SELECT xle.name
INTO l_legal_entity
FROM xle_firstparty_information_v xle
WHERE xle.legal_entity_id = p_legal_entity_id;
SELECT cct.COST_TYPE
INTO l_cost_type
FROM cst_cost_types cct
WHERE cct.cost_type_id = p_cost_type_id;
SELECT ccg.cost_group
INTO l_cost_group
FROM cst_cost_groups ccg
WHERE ccg.cost_group_id = p_cost_group_id
AND NVL(ccg.cost_group_type,1) = 2;
'SELECT
:l_legal_entity NAME,
:l_cost_type COST_TYPE,
:l_cost_group COST_GROUP,
LU.meaning RANGE,
:p_from_workorder FROM_WO,
:p_to_workorder TO_WO,
:l_wip_entity_name ENTITY
FROM mfg_lookups LU
WHERE LU.lookup_type = ''CST_PAC_EAM_JOB_OPTION''
AND LU.lookup_code = :p_range'
USING l_legal_entity, l_cost_type, l_cost_group,
p_from_workorder, p_to_workorder, l_wip_entity_name, p_range;
select we.WIP_ENTITY_NAME ENTITY_NAME,
mp.organization_code ORGANIZATION,
lu1.meaning CATEGORY,
bd.department_code DEPARTMENT,
wpepb.operation_seq_num OPERATION_SEQ,
sum(nvl(wpepb.system_estimated_mat_cost,0)) EST_MATL_COST,
sum(nvl(wpepb.system_estimated_lab_cost,0)) EST_LABR_COST,
sum(nvl(wpepb.system_estimated_eqp_cost,0)) EST_EQUIP_COST,
sum(nvl(wpepb.actual_mat_cost,0)) ACT_MATL_COST,
sum(nvl(wpepb.actual_lab_cost,0)) ACT_LABR_COST,
sum(nvl(wpepb.actual_eqp_cost,0)) ACT_EQUIP_COST,
sum(nvl(wpepb.system_estimated_mat_cost,0)) - sum(nvl(wpepb.actual_mat_cost,0)) VAR_MATL_COST,
sum(nvl(wpepb.system_estimated_lab_cost,0)) - sum(nvl(wpepb.actual_lab_cost,0)) VAR_LABR_COST,
sum(nvl(wpepb.system_estimated_eqp_cost,0)) - sum(nvl(wpepb.actual_eqp_cost,0)) VAR_EQUIP_COST
FROM CST_PAC_EAM_PERIOD_BALANCES WPEPB,
MTL_PARAMETERS mp,
WIP_ENTITIES we,
BOM_DEPARTMENTS bd,
MFG_LOOKUPS lu1
Where wpepb.legal_entity_id = :p_legal_entity_id
and wpepb.cost_group_id = :p_cost_group_id
and wpepb.Cost_type_id = :p_cost_type_id
and wpepb.wip_entity_id = we.wip_entity_id
and wpepb.organization_id = mp.organization_id
and we.organization_id = mp.organization_id
and we.ENTITY_TYPE in (6,7)
and bd.department_id = wpepb.owning_dept_id
and bd.organization_id = wpepb.organization_id
and lu1.lookup_type = ''BOM_EAM_COST_CATEGORY''
and lu1.lookup_code = wpepb.maint_cost_category
group by we.WIP_ENTITY_NAME,
mp.organization_code,
lu1.meaning,
bd.department_code,
wpepb.operation_seq_num' using p_legal_entity_id, p_cost_group_id, p_cost_type_id ;
select we.WIP_ENTITY_NAME ENTITY_NAME,
mp.organization_code ORGANIZATION,
lu1.meaning CATEGORY,
bd.department_code DEPARTMENT,
wpepb.operation_seq_num OPERATION_SEQ,
sum(nvl(wpepb.system_estimated_mat_cost,0)) EST_MATL_COST,
sum(nvl(wpepb.system_estimated_lab_cost,0)) EST_LABR_COST,
sum(nvl(wpepb.system_estimated_eqp_cost,0)) EST_EQUIP_COST,
sum(nvl(wpepb.actual_mat_cost,0)) ACT_MATL_COST,
sum(nvl(wpepb.actual_lab_cost,0)) ACT_LABR_COST,
sum(nvl(wpepb.actual_eqp_cost,0)) ACT_EQUIP_COST,
sum(nvl(wpepb.system_estimated_mat_cost,0)) - sum(nvl(wpepb.actual_mat_cost,0)) VAR_MATL_COST,
sum(nvl(wpepb.system_estimated_lab_cost,0)) - sum(nvl(wpepb.actual_lab_cost,0)) VAR_LABR_COST,
sum(nvl(wpepb.system_estimated_eqp_cost,0)) - sum(nvl(wpepb.actual_eqp_cost,0)) VAR_EQUIP_COST
FROM CST_PAC_EAM_PERIOD_BALANCES WPEPB,
MTL_PARAMETERS mp,
WIP_ENTITIES we,
BOM_DEPARTMENTS bd,
MFG_LOOKUPS lu1
Where wpepb.legal_entity_id = :p_legal_entity_id
and wpepb.cost_group_id = :p_cost_group_id
and wpepb.Cost_type_id = :p_cost_type_id
and wpepb.wip_entity_id = we.wip_entity_id
and we.wip_entity_id = :p_specific_workorder
/* 1 - ALL , 2 - Specific 3 - Range */
and wpepb.organization_id = mp.organization_id
and we.organization_id = mp.organization_id
and we.ENTITY_TYPE in (6,7)
and bd.department_id = wpepb.owning_dept_id
and bd.organization_id = wpepb.organization_id
and lu1.lookup_type = ''BOM_EAM_COST_CATEGORY''
and lu1.lookup_code = wpepb.maint_cost_category
group by we.WIP_ENTITY_NAME,
mp.organization_code,
lu1.meaning,
bd.department_code,
wpepb.operation_seq_num' using p_legal_entity_id, p_cost_group_id, p_cost_type_id, p_specific_workorder ;
SELECT we.WIP_ENTITY_NAME ENTITY_NAME,
mp.organization_code ORGANIZATION,
lu1.meaning CATEGORY,
bd.department_code DEPARTMENT,
wpepb.operation_seq_num OPERATION_SEQ,
sum(nvl(wpepb.system_estimated_mat_cost,0)) EST_MATL_COST,
sum(nvl(wpepb.system_estimated_lab_cost,0)) EST_LABR_COST,
sum(nvl(wpepb.system_estimated_eqp_cost,0)) EST_EQUIP_COST,
sum(nvl(wpepb.actual_mat_cost,0)) ACT_MATL_COST,
sum(nvl(wpepb.actual_lab_cost,0)) ACT_LABR_COST,
sum(nvl(wpepb.actual_eqp_cost,0)) ACT_EQUIP_COST,
sum(nvl(wpepb.system_estimated_mat_cost,0)) - sum(nvl(wpepb.actual_mat_cost,0)) VAR_MATL_COST,
sum(nvl(wpepb.system_estimated_lab_cost,0)) - sum(nvl(wpepb.actual_lab_cost,0)) VAR_LABR_COST,
sum(nvl(wpepb.system_estimated_eqp_cost,0)) - sum(nvl(wpepb.actual_eqp_cost,0)) VAR_EQUIP_COST
FROM CST_PAC_EAM_PERIOD_BALANCES WPEPB,
MTL_PARAMETERS mp,
WIP_ENTITIES we,
BOM_DEPARTMENTS bd,
MFG_LOOKUPS lu1
Where wpepb.legal_entity_id = :p_legal_entity_id
and wpepb.cost_group_id = :p_cost_group_id
and wpepb.Cost_type_id = :p_cost_type_id
and wpepb.wip_entity_id = we.wip_entity_id
and we.wip_entity_name between :p_from_workorder
AND :p_to_workorder
and wpepb.organization_id = mp.organization_id
and we.organization_id = mp.organization_id
and we.ENTITY_TYPE in (6,7)
and bd.department_id = wpepb.owning_dept_id
and bd.organization_id = wpepb.organization_id
and lu1.lookup_type = ''BOM_EAM_COST_CATEGORY''
and lu1.lookup_code = wpepb.maint_cost_category
group by we.WIP_ENTITY_NAME,
mp.organization_code,
lu1.meaning,
bd.department_code,
wpepb.operation_seq_num' using p_legal_entity_id, p_cost_group_id, p_cost_type_id, p_from_workorder, p_to_workorder ;