eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AHL_RM_ROUTE_UTIL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 20


SELECT DISTINCT operation_id
FROM    AHL_OPERATIONS_B_KFV
WHERE   concatenated_segments = c_concatenated_segments;

Line: 26


SELECT DISTINCT operation_id
FROM    AHL_OPERATIONS_B
WHERE   operation_id = c_operation_id;

Line: 113


SELECT DISTINCT lookup_code
FROM    FND_LOOKUP_VALUES_VL
WHERE   lookup_type = c_lookup_type
AND   meaning = c_lookup_meaning
AND   SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
        NVL( end_date_active, SYSDATE );

Line: 123


SELECT DISTINCT lookup_code
FROM    FND_LOOKUP_VALUES_VL
WHERE   lookup_type = c_lookup_type
AND   lookup_code = c_lookup_code
AND   SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
        NVL( end_date_active, SYSDATE );

Line: 218


SELECT DISTINCT party_id
FROM    HZ_PARTIES
WHERE   party_name = c_operator_name;

Line: 224


SELECT DISTINCT party_id
FROM    HZ_PARTIES
WHERE   party_id = c_operator_party_id;

Line: 314


SELECT
mtl.INVENTORY_ITEM_ID ,
mtl.organization_id ,
mtl.inventory_item_flag
from
AHL_MTL_ITEMS_EAM_V mtl
where
MTL.inventory_item_flag = 'Y'
AND mtl.enabled_flag = 'Y'
and mtl.concatenated_segments like c_item_number
and mtl.organization_id = c_inventory_org_id
order by 1;

Line: 327


SELECT DISTINCT MI.inventory_item_id,
    MI.organization_id,
    NVL(MI.inventory_item_flag,'X')
FROM    MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
WHERE   MP.organization_id = MI.organization_id
AND   MI.concatenated_segments = c_item_number
AND   MI.organization_id = c_inventory_org_id
AND   MI.enabled_flag = 'Y'
AND   SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
      AND NVL( MI.end_date_active, SYSDATE )
AND DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y';

Line: 339


SELECT DISTINCT
mtl.INVENTORY_ITEM_ID ,
mtl.organization_id ,
NVL(mtl.inventory_item_flag,'X')
from
MTL_SYSTEM_ITEMS_KFV MTL
, fnd_lookup_values_vl IT
, MTL_PARAMETERS MP
where
DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',mp.eam_enabled_flag )='Y'
AND MTL.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
and MTL.inventory_item_flag = 'Y'
AND IT.lookup_code (+) = MTL.item_type
AND IT.lookup_type (+) = 'ITEM_TYPE'
AND mtl.enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( mtl.start_date_active, SYSDATE )
AND NVL( mtl.end_date_active, SYSDATE )
and upper(mtl.concatenated_segments) like upper(c_item_number)
and mtl.organization_id = c_inventory_org_id
order by 1;

Line: 363


SELECT
mtl.INVENTORY_ITEM_ID ,
mtl.organization_id ,
mtl.inventory_item_flag
from AHL_MTL_ITEMS_EAM_V mtl
where
MTL.inventory_item_flag = 'Y'
AND mtl.enabled_flag = 'Y'
and mtl.inventory_item_id = c_inventory_item_id
and mtl.organization_id = c_inventory_org_id
order by 1;

Line: 375


SELECT DISTINCT MI.inventory_item_id,
    MI.organization_id,
    NVL(MI.inventory_item_flag,'X')
FROM    MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
WHERE DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y'
AND   MP.organization_id = MI.organization_id
AND   MI.inventory_item_id = c_inventory_item_id
AND   MI.organization_id = c_inventory_org_id
AND   MI.enabled_flag = 'Y'
AND   SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
      AND NVL( MI.end_date_active, SYSDATE );

Line: 387


SELECT DISTINCT
mtl.INVENTORY_ITEM_ID ,
mtl.organization_id ,
NVL(mtl.inventory_item_flag,'X')
from MTL_SYSTEM_ITEMS_KFV MTL
, MTL_PARAMETERS MP
, fnd_lookup_values_vl IT
where
DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',mp.eam_enabled_flag )='Y'
AND MTL.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
and MTL.inventory_item_flag = 'Y'
AND IT.lookup_code (+) = MTL.item_type
AND IT.lookup_type (+) = 'ITEM_TYPE'
AND mtl.enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL( mtl.start_date_active, SYSDATE )
AND NVL( mtl.end_date_active, SYSDATE )
and mtl.inventory_item_id = c_inventory_item_id
and mtl.organization_id = c_inventory_org_id
order by 1;

Line: 519


SELECT MTL.inventory_item_id,
    MTL.organization_id,
    MTL.inventory_item_flag,
    MTL.mtl_transactions_enabled_flag,
    MTL.stock_enabled_flag,
    NVL(MTL.wip_supply_type,0)
FROM    AHL_MTL_ITEMS_EAM_V MTL
WHERE
MTL.concatenated_segments = c_item_number
AND   MTL.organization_id = c_inventory_org_id
AND   MTL.enabled_flag = 'Y';

Line: 531


SELECT DISTINCT MI.inventory_item_id,
--      MI.organization_id,
    MP.master_organization_id,
    NVL(MI.inventory_item_flag,'X'),
    NVL(MI.mtl_transactions_enabled_flag,'X'),
    NVL(MI.stock_enabled_flag,'X'),
    NVL(MI.wip_supply_type,0)
FROM    MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
WHERE DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y'
AND   MP.master_organization_id = MI.organization_id
AND   MI.concatenated_segments = c_item_number
--AND     MI.organization_id = c_inventory_org_id
AND   MP.master_organization_id = c_inventory_org_id
AND   MI.enabled_flag = 'Y'
AND   SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
      AND NVL( MI.end_date_active, SYSDATE );

Line: 549


SELECT DISTINCT
mtl.inventory_item_id ,
mtl.inventory_org_id ,
NVL(mtl.inventory_item_flag,'X'),
NVL(mtl.mtl_transactions_enabled_flag,'X'),
NVL(mtl.stock_enabled_flag,'X'),
NVL(mtl.wip_supply_type,0)
from ahl_mtl_items_non_ou_v mtl
where
DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',mtl.eam_enabled_flag )='Y'
AND   mtl.enabled_flag = 'Y'
AND   SYSDATE BETWEEN NVL( mtl.start_date_active, SYSDATE )
      AND NVL( mtl.end_date_active, SYSDATE )
and upper(mtl.concatenated_segments) like upper(c_item_number)
and mtl.inventory_org_id = c_inventory_org_id
order by 1;

Line: 570


SELECT MTL.inventory_item_id,
    MTL.organization_id,
    MTL.inventory_item_flag,
    MTL.mtl_transactions_enabled_flag,
    MTL.stock_enabled_flag,
    NVL(MTL.wip_supply_type,0)
FROM    AHL_MTL_ITEMS_EAM_V MTL
WHERE
    MTL.inventory_item_id = c_inventory_item_id
AND             MTL.organization_id = c_inventory_org_id
AND   MTL.enabled_flag = 'Y';

Line: 582


SELECT DISTINCT MI.inventory_item_id,
--      MI.organization_id,
    MP.master_organization_id,
    NVL(MI.inventory_item_flag,'X'),
    NVL(MI.mtl_transactions_enabled_flag,'X'),
    NVL(MI.stock_enabled_flag,'X'),
    NVL(MI.wip_supply_type,0)
FROM    MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
WHERE DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y'
AND   MP.master_organization_id = MI.organization_id
AND   MI.inventory_item_id = c_inventory_item_id
--AND     MI.organization_id = c_inventory_org_id
AND   MP.master_organization_id = c_inventory_org_id
AND   MI.enabled_flag = 'Y'
AND   SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
      AND NVL( MI.end_date_active, SYSDATE );

Line: 600


SELECT DISTINCT
mtl.inventory_item_id ,
mtl.inventory_org_id ,
NVL(mtl.inventory_item_flag,'X'),
NVL(mtl.mtl_transactions_enabled_flag,'X'),
NVL(mtl.stock_enabled_flag,'X'),
NVL(mtl.wip_supply_type,0)
from ahl_mtl_items_non_ou_v mtl
where
DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',mtl.eam_enabled_flag )='Y'
AND   mtl.enabled_flag = 'Y'
AND   SYSDATE BETWEEN NVL( mtl.start_date_active, SYSDATE )
      AND NVL( mtl.end_date_active, SYSDATE )
and mtl.inventory_item_id = c_inventory_item_id
and mtl.inventory_org_id = c_inventory_org_id
order by 1;

Line: 740


SELECT DISTINCT MI.inventory_item_id,
    MI.organization_id,
    MI.outside_operation_flag,
    MI.purchasing_item_flag,
    MI.purchasing_enabled_flag
FROM    MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
WHERE DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y'
AND   MP.organization_id = MI.organization_id
AND   MI.concatenated_segments = c_item_number
AND   MI.enabled_flag = 'Y'
AND   SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
      AND NVL( MI.end_date_active, SYSDATE );

Line: 756


SELECT DISTINCT MI.inventory_item_id,
    MI.organization_id,
    MI.outside_operation_flag,
    MI.purchasing_item_flag,
    MI.purchasing_enabled_flag
FROM    MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
WHERE DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y'
AND   MP.organization_id = MI.organization_id
AND   MI.inventory_item_id = c_inventory_item_id
AND   MI.organization_id = c_inventory_org_id
AND   MI.enabled_flag = 'Y'
AND   SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
      AND NVL( MI.end_date_active, SYSDATE );

Line: 887


SELECT
MTL.INVENTORY_ITEM_ID ,
MTL.organization_id ,
MTL.comms_nl_trackable_flag
from   AHL_MTL_ITEMS_EAM_V MTL
where
upper(nvl(MTL.comms_nl_trackable_flag,'N')) = 'Y'
and MTL.enabled_flag = 'Y'
and upper(MTL.concatenated_segments) like upper(c_item_number)
and upper(MTL.organization_code) like upper(c_org_code)
order by 1;

Line: 899


SELECT DISTINCT MI.inventory_item_id,
    MP.master_organization_id,
    MI.comms_nl_trackable_flag
FROM    MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
WHERE   MP.master_organization_id = MI.organization_id
AND   upper(MI.concatenated_segments) = upper(c_item_number)
AND   MI.enabled_flag = 'Y'
AND   upper(MP.ORGANIZATION_CODE)=upper(c_org_code)
AND   SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
      AND NVL( MI.end_date_active, SYSDATE )
AND DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y';

Line: 912


SELECT DISTINCT
mtl.INVENTORY_ITEM_ID ,
mtl.organization_id ,
mtl.comms_nl_trackable_flag
from MTL_SYSTEM_ITEMS_KFV MTL
, MTL_PARAMETERS MP
, MTL_PARAMETERS MP1
, fnd_lookup_values_vl IT
where
DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',mp.eam_enabled_flag )='Y'
AND MTL.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
and MP.MASTER_ORGANIZATION_ID = MP1.ORGANIZATION_ID
and sysdate between nvl( MTL.start_date_active, sysdate )
and nvl( MTL.end_date_active, sysdate )
and IT.lookup_code (+) = MTL.item_type
and IT.lookup_type (+) = 'ITEM_TYPE'
and MTL.enabled_flag = 'Y'
and upper(mtl.concatenated_segments) like upper(c_item_number)
and upper(mp1.organization_code) like upper(c_org_code)
order by 1;

Line: 937


SELECT
mtl.INVENTORY_ITEM_ID ,
mtl.organization_id ,
mtl.comms_nl_trackable_flag
from   AHL_MTL_ITEMS_EAM_V MTL
where
upper(nvl(MTL.comms_nl_trackable_flag,'N')) = 'Y'
and MTL.enabled_flag = 'Y'
and mtl.inventory_item_id = c_inventory_item_id
and mtl.organization_id = c_inventory_org_id
order by 1;

Line: 949


SELECT DISTINCT MI.inventory_item_id,
    MP.master_organization_id,
    MI.comms_nl_trackable_flag
FROM    MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
WHERE   MP.master_organization_id = MI.organization_id
AND   MI.inventory_item_id = c_inventory_item_id
AND   MI.organization_id = c_inventory_org_id
AND   MI.enabled_flag = 'Y'
AND   SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
      AND NVL( MI.end_date_active, SYSDATE )
AND DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y';

Line: 961


/*SELECT DISTINCT
mtl.INVENTORY_ITEM_ID ,
mtl.organization_id ,
mtl.comms_nl_trackable_flag
from MTL_SYSTEM_ITEMS_KFV MTL
, MTL_PARAMETERS MP
, fnd_lookup_values_vl IT
where
DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',mp.eam_enabled_flag )='Y'
AND MTL.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
and sysdate between nvl( MTL.start_date_active, sysdate )
and nvl( MTL.end_date_active, sysdate )
and IT.lookup_code (+) = MTL.item_type
and IT.lookup_type (+) = 'ITEM_TYPE'
and MTL.enabled_flag = 'Y'
and mtl.inventory_item_id = c_inventory_item_id
and mtl.organization_id = c_inventory_org_id
order by 1;

Line: 1091


SELECT DISTINCT class_code,
    organization_id
FROM    WIP_ACCOUNTING_CLASSES
WHERE   description = c_accounting_class
AND   class_type = 6;

Line: 1100


SELECT DISTINCT class_code,
    organization_id
FROM    WIP_ACCOUNTING_CLASSES
WHERE   class_code = c_accounting_class_code
AND   organization_id = c_accounting_class_org_id
AND   class_type = 6;

Line: 1195


SELECT DISTINCT task_template_group_id
FROM    JTF_TASK_TEMP_GROUPS_VL
WHERE    trunc(sysdate) >= trunc(nvl(start_date_active, sysdate)) and
           trunc(sysdate) < trunc(nvl(end_date_active, sysdate+1)) and
           template_group_name = c_task_template_group;

Line: 1203


SELECT DISTINCT task_template_group_id
FROM    JTF_TASK_TEMP_GROUPS_VL
WHERE   trunc(sysdate) >= trunc(nvl(start_date_active, sysdate)) and
           trunc(sysdate) < trunc(nvl(end_date_active, sysdate+1)) and
           task_template_group_id = c_task_template_group_id;

Line: 1290


SELECT DISTINCT short_code
FROM    QA_CHAR_VALUE_LOOKUPS_V
WHERE   description = c_qa_inspection_type_desc;

Line: 1296


SELECT DISTINCT short_code
FROM    QA_CHAR_VALUE_LOOKUPS_V
WHERE   short_code = c_qa_inspection_type;

Line: 1381


SELECT DISTINCT plan_id
FROM    QA_PLANS_VAL_V
WHERE   name = c_qa_plan;

Line: 1387


SELECT DISTINCT plan_id
FROM    QA_PLANS_VAL_V
WHERE   plan_id = c_qa_plan_id;

Line: 1473


SELECT DISTINCT item_group_id
FROM    AHL_ITEM_GROUPS_VL
WHERE   name = c_item_group_name
AND   DECODE(c_association_type,'DISPOSITION',TYPE_CODE,'NON-TRACKED')='NON-TRACKED';

Line: 1480


SELECT DISTINCT item_group_id
FROM    AHL_ITEM_GROUPS_VL
WHERE   item_group_id = c_item_group_id
AND   DECODE(c_association_type,'DISPOSITION',TYPE_CODE,'NON-TRACKED')='NON-TRACKED';

Line: 1565


SELECT DISTINCT ICD.item_comp_detail_id
FROM    AHL_ITEM_COMP_DETAILS ICD
    , AHL_ITEM_COMPOSITIONS CD
WHERE   ICD.item_comp_detail_id = c_item_comp_detail_id
AND   CD.APPROVAL_STATUS_CODE ='COMPLETE'
AND   CD.item_composition_id = ICD.item_composition_id
AND   nvl(trunc(CD.EFFECTIVE_END_DATE),trunc(sysdate+1)) > trunc(sysdate)
AND   nvl(trunc(ICD.EFFECTIVE_END_DATE),trunc(sysdate+1)) > trunc(sysdate);

Line: 1620


SELECT DISTINCT path_position_id
FROM    AHL_MC_PATH_POSITIONS
WHERE   position_ref_code = c_position_path;

Line: 1627


SELECT DISTINCT
mcp.path_position_id
FROM
  ahl_mc_relationships mcr,
  ahl_mc_headers_b mch,
  ahl_mc_path_position_nodes mcp,
  ahl_route_effectivities re
WHERE
  re.mc_id IS NOT NULL AND
  re.mc_id = mch.mc_id AND
  mch.mc_header_id =mcr.mc_header_id AND
  mch.mc_id = mcp.mc_id AND
  mch.version_number = NVL(mcp.version_number, mch.version_number) AND
  mcr.position_key =  mcp.position_key AND
  mcp.SEQUENCE = ( SELECT MAX(SEQUENCE) FROM  ahl_mc_path_position_nodes WHERE path_position_id = NVL(c_position_path_id,-1) )  AND
  mcp.path_position_id =  NVL(c_position_path_id,-1);

Line: 1644


/*SELECT DISTINCT
mcp.path_position_id
FROM
ahl_mc_relationships mcr
, ahl_mc_headers_b mch
,   ahl_mc_path_position_nodes mcp
, AHL_ROUTE_EFFECTIVITIES_V re
WHERE
mch.mc_header_id = mcr.mc_header_id
and mch.mc_id = mcp.mc_id
and re.MC_ID = mch.MC_ID
and mch.version_number = nvl(mcp.version_number, mch.version_number)
and mcr.position_key = mcp.position_key
and mcp.sequence = (select max(sequence) from ahl_mc_path_position_nodes where path_position_id = nvl(c_position_path_id,-1))
and mcp.path_position_id = nvl(c_position_path_id,-1);*/

Line: 1747


SELECT DISTINCT mc_id
FROM    AHL_MC_HEADERS_B
WHERE   upper(name) = upper(c_mc_name)
AND   CONFIG_STATUS_CODE='COMPLETE';

Line: 1752


/*SELECT DISTINCT mc_id
FROM    AHL_MC_HEADERS_V
WHERE   upper(name) = upper(c_mc_name)
AND   CONFIG_STATUS_CODE='COMPLETE';*/

Line: 1760


SELECT DISTINCT mc_header_id, mc_id
FROM    AHL_MC_HEADERS_B
WHERE   upper(name) = upper(c_mc_name)
AND   upper(revision)=upper(c_mc_revision_number)
AND   CONFIG_STATUS_CODE='COMPLETE';

Line: 1766


SELECT DISTINCT mc_header_id,
    mc_id
FROM    AHL_MC_HEADERS_V
WHERE   upper(name) = upper(c_mc_name)
AND   upper(revision)=upper(c_mc_revision_number)
AND   CONFIG_STATUS_CODE='COMPLETE';*/

Line: 1776


SELECT DISTINCT mc_header_id
FROM    AHL_MC_HEADERS_V
WHERE   mc_header_id = c_mc_header_id
AND   CONFIG_STATUS_CODE='COMPLETE'
;

Line: 1784


SELECT DISTINCT mc_id
FROM    AHL_MC_HEADERS_V
WHERE   mc_id = c_mc_id
AND   CONFIG_STATUS_CODE='COMPLETE'
;

Line: 1793


SELECT DISTINCT mc_id
FROM    AHL_MC_HEADERS_V
WHERE   mc_id = c_mc_id
AND   CONFIG_STATUS_CODE='COMPLETE'
;

Line: 1801


SELECT DISTINCT mc_header_id
FROM    AHL_MC_HEADERS_V
WHERE   mc_header_id = c_mc_header_id
AND   CONFIG_STATUS_CODE='COMPLETE'
;

Line: 1957


SELECT DISTINCT uom_code
FROM    MTL_UNITS_OF_MEASURE_VL
WHERE   upper(unit_of_measure) = upper(c_uom);

Line: 1963


SELECT DISTINCT uom_code
FROM    MTL_UNITS_OF_MEASURE_VL
WHERE   upper(uom_code) = upper(c_uom_code);

Line: 2052


SELECT    'X'
FROM    AHL_ITEM_CLASS_UOM_V
WHERE   uom_code = c_uom_code
AND   inventory_item_id = c_inventory_item_id
AND   inventory_org_id = c_inventory_org_id;

Line: 2061


SELECT    'X'
FROM    AHL_ITEM_CLASS_UOM_V UOM, AHL_ITEM_ASSOCIATIONS_B ASSOC
WHERE   UOM.uom_code = c_uom_code
AND   UOM.inventory_item_id = ASSOC.inventory_item_id
AND   UOM.inventory_org_id = ASSOC.inventory_org_id
AND   ASSOC.item_group_id = c_item_group_id;

Line: 2141


SELECT 'X'
FROM   AHL_PRODTYPE_ZONES
WHERE  product_type_code = c_product_type_code
AND    zone_code = c_zone_code
AND    sub_zone_code IS NULL;

Line: 2189


SELECT 'X'
FROM   AHL_PRODTYPE_ZONES
WHERE  product_type_code = c_product_type_code
AND    zone_code = c_zone_code
AND    sub_zone_code = c_sub_zone_code;

Line: 2245


SELECT DISTINCT lookup_code
FROM    MFG_LOOKUPS
WHERE   lookup_type = c_lookup_type
AND   UPPER(meaning) = UPPER(c_lookup_meaning)
AND   SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
        NVL( end_date_active, SYSDATE )
AND   LOOKUP_CODE IN (c_lookup_code_1, c_lookup_code_2);

Line: 2258


SELECT DISTINCT lookup_code
FROM    MFG_LOOKUPS
WHERE   lookup_type = c_lookup_type
AND   lookup_code = c_lookup_code
AND   SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
        NVL( end_date_active, SYSDATE )
AND   LOOKUP_CODE IN (c_lookup_code_1, c_lookup_code_2);

Line: 2269


SELECT DISTINCT lookup_code
FROM    MFG_LOOKUPS
WHERE   lookup_type = c_lookup_type
AND   UPPER(meaning) = UPPER(c_lookup_meaning)
AND   SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
        NVL( end_date_active, SYSDATE );

Line: 2279


SELECT DISTINCT lookup_code
FROM    MFG_LOOKUPS
WHERE   lookup_type = c_lookup_type
AND   lookup_code = c_lookup_code
AND   SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
        NVL( end_date_active, SYSDATE );

Line: 2438


SELECT DISTINCT resource_id
FROM    AHL_RESOURCES
WHERE   UPPER(TRIM(name)) = UPPER(TRIM(c_aso_resource_name));

Line: 2444


SELECT DISTINCT resource_id
FROM    AHL_RESOURCES
WHERE   resource_id = c_aso_resource_id;

Line: 2531


SELECT DISTINCT resource_id,
    organization_id
FROM    BOM_RESOURCES
WHERE   resource_code = c_bom_resource_code;

Line: 2539


SELECT DISTINCT resource_id,
    organization_id
FROM    BOM_RESOURCES
WHERE   resource_id = c_bom_resource_id
AND   organization_id = c_bom_org_id;

Line: 2637


select distinct DEPT.department_id
from bom_departments DEPT, BOM_DEPARTMENT_RESOURCES DEPT_RES, BOM_RESOURCES RES
where DEPT_RES.department_id = DEPT.department_id
and DEPT.description = c_bom_department_name
and DEPT_RES.resource_id = c_bom_resource_id
and DEPT.organization_id = c_bom_org_id;

Line: 2704


SELECT DISTINCT activity_id
FROM    CST_ACTIVITIES
WHERE   activity = c_activity;

Line: 2710


SELECT DISTINCT activity_id
FROM    CST_ACTIVITIES
WHERE   activity_id = c_activity_id;

Line: 2797


SELECT DISTINCT competence_id
FROM    PER_COMPETENCES
WHERE   name = c_skill_name
AND   business_group_id = c_business_group_id;

Line: 2805


SELECT DISTINCT competence_id
FROM    PER_COMPETENCES
WHERE   competence_id = c_skill_competence_id
AND   business_group_id = c_business_group_id;

Line: 2902


SELECT DISTINCT rating_level_id
FROM    PER_RATING_LEVELS
WHERE   TO_CHAR( step_value ) || '-' || name = c_skill_level_desc
AND   competence_id = c_skill_competence_id
AND   business_group_id = c_business_group_id;

Line: 2912


SELECT DISTINCT rating_level_id
FROM    PER_RATING_LEVELS
WHERE   rating_level_id = c_rating_level_id
AND   competence_id = c_skill_competence_id
AND   business_group_id = c_business_group_id;

Line: 3008


SELECT DISTINCT qualification_type_id
FROM    PER_QUALIFICATION_TYPES
WHERE   name = c_qualification_type;

Line: 3014


SELECT DISTINCT qualification_type_id
FROM    PER_QUALIFICATION_TYPES
WHERE   qualification_type_id = c_qualification_type_id;

Line: 3095


    SELECT revision_status_code
      FROM  ahl_routes_app_v
     WHERE route_id = c_route_id;

Line: 3145


   SELECT ROUTE_ID
        FROM AHL_ROUTE_EFFECTIVITIES
        WHERE ROUTE_EFFECTIVITY_ID = c_efct_id;

Line: 3148


    /*SELECT ROUTE_ID
      FROM  AHL_ROUTE_EFFECTIVITIES_V
     WHERE ROUTE_EFFECTIVITY_ID = c_efct_id;*/

Line: 3154


    SELECT ROUTE_NO
        FROM AHL_ROUTES_B
        WHERE ROUTE_ID = c_route_id AND
        APPLICATION_USG_CODE=rtrim(ltrim(fnd_profile.value('AHL_APPLN_USAGE')));

Line: 3158


   /* SELECT ROUTE_NO
      FROM  AHL_ROUTES_V
     WHERE ROUTE_ID = c_route_id;*/

Line: 3212


    SELECT revision_status_code
      FROM ahl_operations_b
     WHERE operation_id = c_operation_id;

Line: 3265


SELECT MAX( duration )
FROM   AHL_RT_OPER_RESOURCES
WHERE  association_type_code = 'ROUTE'
AND    object_id = c_route_id
AND    scheduled_type_id = 1;

Line: 3273


SELECT SUM(MAX(duration))
FROM   AHL_RT_OPER_RESOURCES
WHERE  association_type_code = 'ROUTE'
AND object_id = c_route_id
GROUP BY schedule_seq ;

Line: 3285


SELECT MAX( RES.duration )
FROM   AHL_RT_OPER_RESOURCES RES, AHL_OPERATIONS_B OPER, AHL_ROUTE_OPERATIONS ASS
WHERE  RES.association_type_code = 'OPERATION'
AND    RES.object_id = ASS.operation_id
AND    NVL( OPER.end_date_active , TRUNC( SYSDATE ) + 1 ) > TRUNC( SYSDATE )
AND    OPER.operation_id = ASS.operation_id
AND    RES.scheduled_type_id = 1
AND    ASS.route_id = c_route_id;

Line: 3296


SELECT SUM(OPR_DURATION)
FROM
  (SELECT RES.object_id,RES.schedule_seq, MAX(duration) "OPR_DURATION"
   FROM   AHL_RT_OPER_RESOURCES RES, AHL_OPERATIONS_B OPER, AHL_ROUTE_OPERATIONS ASS
   WHERE  RES.association_type_code = 'OPERATION'
   AND    RES.object_id = ASS.operation_id
   AND    NVL( OPER.end_date_active , TRUNC( SYSDATE ) + 1 ) > TRUNC( SYSDATE )
   AND    OPER.operation_id = ASS.operation_id
   AND    ASS.route_id = c_route_id
   GROUP BY RES.object_id,RES.schedule_seq ) ;

Line: 3364


SELECT time_span
FROM   AHL_ROUTES_APP_V
WHERE  route_id = c_route_id;

Line: 3370


SELECT MIN( RT.time_span )
FROM   AHL_ROUTES_APP_V RT, AHL_ROUTE_OPERATIONS ASS
WHERE  TRUNC ( NVL ( RT.end_date_active ,  SYSDATE  + 1 ) )  > TRUNC( SYSDATE )
AND    RT.route_id = ASS.route_id
AND    ASS.operation_id = c_operation_id;

Line: 3437


SELECT time_span
FROM   AHL_ROUTES_APP_V
WHERE  route_id = c_route_id;

Line: 3444


SELECT MAX( duration )
FROM   AHL_RT_OPER_RESOURCES
WHERE  association_type_code = 'OPERATION'
AND    scheduled_type_id = 1
AND    object_id = c_operation_id;

Line: 3495


SELECT MAX( A.start_date_active )
FROM   AHL_ROUTES_APP_V A, AHL_ROUTES_APP_V B
WHERE  A.route_no = B.route_no
AND    A.route_id <> c_route_id
AND    B.route_id = c_route_id;

Line: 3503


SELECT MAX( A.start_date_active )
FROM   AHL_OPERATIONS_B_KFV A, AHL_OPERATIONS_B_KFV B
WHERE  A.concatenated_segments = B.concatenated_segments
AND    A.operation_id <> c_operation_id
AND    B.operation_id = c_operation_id;

Line: 3569


SELECT r.Application_usg_code
FROM   AHL_ROUTES_B r
WHERE  r.route_id = c_route_id
;

Line: 3644


SELECT DISTINCT
fnd.MEANING
FROM
ahl_mc_relationships mcr
, ahl_mc_headers_b mch
, ahl_mc_path_position_nodes mcp
, AHL_ROUTE_EFFECTIVITIES_V re
, fnd_lookup_values_vl fnd
, AHL_RT_OPER_MATERIALS AOB
WHERE
mch.mc_header_id = mcr.mc_header_id
and mch.mc_id = mcp.mc_id
and re.MC_ID = mch.MC_ID
and mch.version_number = nvl(mcp.version_number, mch.version_number)
and mcr.position_key = mcp.position_key
and mcp.sequence = (select max(sequence) from ahl_mc_path_position_nodes where path_position_id = nvl(c_position_path_id,-1))
and mcp.path_position_id = nvl(c_position_path_id,-1)
and fnd.LOOKUP_TYPE = 'AHL_POSITION_REFERENCE'
and fnd.LOOKUP_CODE = mcr.POSITION_REF_CODE
;

Line: 3693


SELECT DISTINCT MTL.CONCATENATED_SEGMENTS
    , MTL.ORGANIZATION_CODE
FROM    AHL_ITEM_COMP_DETAILS ICD
    , AHL_ITEM_COMPOSITIONS CD
        , AHL_MTL_ITEMS_NON_OU_V MTL
WHERE   ICD.item_comp_detail_id = c_item_comp_detail_id
AND   CD.APPROVAL_STATUS_CODE ='COMPLETE'
AND   CD.item_composition_id = ICD.item_composition_id
AND   CD.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND   CD.INVENTORY_MASTER_ORG_ID =  MTL.INVENTORY_ORG_ID
AND   nvl(trunc(CD.EFFECTIVE_END_DATE),trunc(sysdate+1)) > trunc(sysdate)
AND   nvl(trunc(ICD.EFFECTIVE_END_DATE),trunc(sysdate+1)) > trunc(sysdate)
;

Line: 3712


select distinct mtl.concatenated_segments,
                mtl.organization_code
from   (select kfv.inventory_item_id,
               mp.master_organization_id inventory_org_id,
               mp.organization_code,
               kfv.concatenated_segments
        from   mtl_system_items_kfv kfv,
               mtl_parameters mp
        where  kfv.organization_id = mp.organization_id
               and exists (select 'X'
                           from   mtl_parameters mp1
                           where  mp1.master_organization_id = kfv.organization_id
                                  and mp1.eam_enabled_flag = 'Y')) mtl,
       (select inventory_item_id,
               inventory_org_id
        from   ahl_position_alternates_v
        where  relationship_id = c_position_path_id) pal,
       (select item_composition_id,
               item_comp_detail_id
        from   ahl_item_comp_details
        where  nvl(trunc(effective_end_date),trunc(sysdate + 1)) > trunc(sysdate)
               and item_comp_detail_id = c_item_comp_detail_id) icd,
       (select icb.item_composition_id,
               icb.inventory_item_id,
               icb.inventory_master_org_id,
               decode(sign(trunc(nvl(icb.effective_end_date,sysdate + 1)) - trunc(sysdate)),1,icb.approval_status_code,'EXPIRED') approval_status_code,
               icb.effective_end_date effective_end_date
        from   ahl_item_compositions icb
        where  approval_status_code = 'COMPLETE'
               and nvl(trunc(effective_end_date),trunc(sysdate + 1)) > trunc(sysdate)) cd
where  pal.inventory_item_id = mtl.inventory_item_id
       and pal.inventory_org_id = mtl.inventory_org_id
       and pal.inventory_item_id = cd.inventory_item_id
       and pal.inventory_org_id = cd.inventory_master_org_id
       and cd.item_composition_id = icd.item_composition_id;

Line: 3749


/*select distinct
mtl.concatenated_segments ,
mtl.organization_code
from
ahl_mtl_items_non_ou_v mtl,
ahl_position_alternates_v pal,
AHL_ITEM_COMP_V CD ,
AHL_ITEM_COMP_DETAILS ICD
where
pal.relationship_id = nvl(c_position_path_id,'-1')
and pal.inventory_item_id = mtl.INVENTORY_ITEM_ID
and pal.inventory_org_id = mtl.INVENTORY_ORG_ID
and pal.inventory_item_id = CD.inventory_item_id
and pal.inventory_org_id = CD.inventory_master_org_id
AND CD.APPROVAL_STATUS_CODE = 'COMPLETE'
AND CD.item_composition_id = ICD.item_composition_id
AND nvl(trunc(CD.EFFECTIVE_END_DATE),trunc(sysdate+1)) > trunc(sysdate)
AND nvl(trunc(ICD.EFFECTIVE_END_DATE),trunc(sysdate+1)) > trunc(sysdate)
AND ICD.ITEM_COMP_DETAIL_ID=c_item_comp_detail_id;*/

Line: 3771


SELECT DISTINCT
  CD.concatenated_segments ,
  CD.organization_code
FROM
  ahl_mc_relationships mcr,
  ahl_mc_headers_b mch,
  ahl_mc_path_position_nodes mcp,
  ahl_item_associations_v igass ,
  AHL_ROUTE_EFFECTIVITIES re ,
--  ahl_mtl_items_non_ou_v mtl,
  AHL_ITEM_COMP_V CD ,
  AHL_ITEM_COMP_DETAILS ICD
WHERE
  mch.mc_header_id = mcr.mc_header_id
  and mch.mc_id = mcp.mc_id
  and re.MC_ID = mch.MC_ID
  and mch.version_number = nvl(mcp.version_number, mch.version_number)
  and mcr.position_key = mcp.position_key
  and mcp.sequence = (select max(sequence) from ahl_mc_path_position_nodes where path_position_id = nvl(p_position_path_id,'-1'))
  and mcp.path_position_id = nvl(p_position_path_id,'-1')
  and mcr.item_group_id = igass.item_group_id
  and igass.INVENTORY_ITEM_ID = CD.inventory_item_id
  and igass.INVENTORY_ORG_ID = CD.inventory_master_org_id
  AND CD.APPROVAL_STATUS_CODE = 'COMPLETE'
  AND CD.item_composition_id = ICD.item_composition_id
  AND nvl(trunc(CD.EFFECTIVE_END_DATE),trunc(sysdate-1)) < trunc(sysdate)
  AND ICD.ITEM_COMP_DETAIL_ID=c_item_comp_detail_id
  order by 1
 ;

Line: 3883


SELECT operation_id
FROM ahl_operations_vl
WHERE concatenated_segments = p_operation_number
  AND
  revision_number = p_operation_revision;

Line: 3933


SELECT route_id
FROM ahl_routes_app_v
WHERE UPPER( TRIM(route_no)) = UPPER(TRIM(p_route_number))
  AND
  revision_number = p_route_revision;