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 ( (lookup_type <> 'ITEM_TYPE') OR (lookup_type = 'ITEM_TYPE' AND view_application_id in (0,3)))
AND   SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
        NVL( end_date_active, SYSDATE );

Line: 124


SELECT DISTINCT lookup_code
FROM    FND_LOOKUP_VALUES_VL
WHERE   lookup_type = c_lookup_type
AND   lookup_code = c_lookup_code
AND ( (lookup_type <> 'ITEM_TYPE') OR (lookup_type = 'ITEM_TYPE' AND view_application_id in (0,3)))
AND   SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
        NVL( end_date_active, SYSDATE );

Line: 220


SELECT DISTINCT party_id
FROM    HZ_PARTIES
WHERE   party_name = c_operator_name;

Line: 226


SELECT DISTINCT party_id
FROM    HZ_PARTIES
WHERE   party_id = c_operator_party_id;

Line: 316


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: 329


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: 341


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: 365


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: 377


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: 389


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: 521


SELECT DISTINCT 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) --pdoki commented for Bug 8589785
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: 533


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: 551


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: 572


SELECT DISTINCT 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) --pdoki commented for Bug 8589785
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: 584


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: 602


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: 744


SELECT DISTINCT MI.inventory_item_id,
    mast.organization_id,
    MI.outside_operation_flag,
    MI.purchasing_item_flag,
    MI.purchasing_enabled_flag
FROM    MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI,mtl_system_items_b mast
WHERE DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y'
AND   MP.organization_id = MI.organization_id
AND     MP.master_organization_id = mast.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: 762


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: 893


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: 905


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: 918


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: 943


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: 955


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: 967


/*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: 1097


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

Line: 1106


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: 1201


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: 1209


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: 1294


  SELECT warranty_template_id
  FROM   AHL_WARRANTY_TEMPLATES_B
  WHERE  name = c_warranty_template_name
         AND enabled_flag = 'Y';

Line: 1300


  SELECT warranty_template_id
  FROM   AHL_WARRANTY_TEMPLATES_B
  WHERE  warranty_template_id = c_warranty_template_id
         AND enabled_flag = 'Y';

Line: 1368


SELECT DISTINCT short_code
FROM    QA_CHAR_VALUE_LOOKUPS_V
--WHERE   description = c_qa_inspection_type_desc; fix for the bug 9397756

Line: 1375


SELECT DISTINCT short_code
FROM    QA_CHAR_VALUE_LOOKUPS_V
WHERE   short_code = c_qa_inspection_type;

Line: 1460


SELECT DISTINCT plan_id
FROM    QA_PLANS_VAL_V
WHERE   name = c_qa_plan;

Line: 1466


SELECT DISTINCT plan_id
FROM    QA_PLANS_VAL_V
WHERE   plan_id = c_qa_plan_id;

Line: 1552


SELECT DISTINCT item_group_id
FROM    AHL_ITEM_GROUPS_VL
WHERE   name = c_item_group_name
-- bug # 11833328 - sthilak - added the status=complete condition; sorao for backporting project

Line: 1561


SELECT DISTINCT item_group_id
FROM    AHL_ITEM_GROUPS_VL
WHERE   item_group_id = c_item_group_id
-- bug # 11833328 - sthilak - added the status=complete condition; sorao for backporting project

Line: 1648


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: 1704


SELECT DISTINCT path_position_id
FROM    AHL_MC_PATH_POSITIONS
WHERE   position_ref_code = c_position_path;

Line: 1713


select 'X'
  FROM ahl_mc_relationships mcr,
    ahl_mc_headers_b mch,
    ahl_mc_path_position_nodes mcp
  WHERE mcp.mc_id             = mch.mc_id
  AND mcp.SEQUENCE = ( SELECT MAX(SEQUENCE) FROM  ahl_mc_path_position_nodes WHERE path_position_id = NVL(c_position_path_id,-1) )
  AND mch.mc_header_id      = mcr.mc_header_id
  AND mch.version_number    = NVL(mcp.version_number, mch.version_number)
  AND mcp.position_key= mcr.position_key;

Line: 1725


SELECT 'X'
FROM ahl_mc_path_position_nodes mcp ,
  ahl_route_effectivities re
WHERE mcp.mc_id         =re.mc_id
AND mcp.path_position_id=NVL(c_position_path_id,-1) and mcp.sequence=0;

Line: 1735


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: 1752


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: 1879


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

Line: 1884


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

Line: 1892


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: 1898


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: 1908


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

Line: 1916


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

Line: 1925


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

Line: 1933


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

Line: 2089


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

Line: 2095


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

Line: 2184


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: 2193


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: 2273


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: 2321


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: 2377


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: 2390


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: 2401


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: 2411


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: 2570


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

Line: 2576


SELECT DISTINCT resource_id
FROM    AHL_RESOURCES
WHERE   resource_id = c_aso_resource_id;

Line: 2663


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

Line: 2671


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

Line: 2769


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: 2836


SELECT DISTINCT activity_id
FROM    CST_ACTIVITIES
WHERE   activity = c_activity;

Line: 2842


SELECT DISTINCT activity_id
FROM    CST_ACTIVITIES
WHERE   activity_id = c_activity_id;

Line: 2929


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

Line: 2937


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

Line: 3034


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: 3044


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: 3140


SELECT DISTINCT qualification_type_id
FROM    PER_QUALIFICATION_TYPES
WHERE   name = c_qualification_type;

Line: 3146


SELECT DISTINCT qualification_type_id
FROM    PER_QUALIFICATION_TYPES
WHERE   qualification_type_id = c_qualification_type_id;

Line: 3227


    SELECT revision_status_code
      FROM  ahl_routes_app_v
     WHERE route_id = c_route_id;

Line: 3277


   SELECT ROUTE_ID
        FROM AHL_ROUTE_EFFECTIVITIES
        WHERE ROUTE_EFFECTIVITY_ID = c_efct_id;

Line: 3280


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

Line: 3286


    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: 3290


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

Line: 3344


    SELECT revision_status_code
      FROM ahl_operations_b
     WHERE operation_id = c_operation_id;

Line: 3398


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: 3406


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: 3418


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: 3430


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: 3447


SELECT MAX(OPER_DURATION)
FROM   ( SELECT  SUM(RES_DURATION) OPER_DURATION,
                 object_id
       FROM     (SELECT  MAX(res.duration) RES_DURATION,
                         res.object_id
                FROM     ahl_rt_oper_resources res
                WHERE    res.association_type_code = 'OPERATION'
                AND      res.scheduled_type_id = 1
                AND      res.object_id IN
                         (SELECT oper.operation_id
                         FROM    ahl_operations_b oper    ,
                                 ahl_route_operations ass ,
                                 ahl_routes_app_v rou
                         WHERE
                                 (
                                   TRUNC(nvl(c_rou_start_date,rou.start_date_active)) >= TRUNC(oper.start_date_active)
                                   AND
                                   TRUNC(NVL(oper.end_date_active, nvl(c_rou_start_date,rou.start_date_active) + 1)) > TRUNC(nvl(c_rou_start_date,rou.start_date_active))
                                 )
                         AND     rou.route_id      = c_route_id
                         AND     oper.operation_id = ass.operation_id
                         AND     ass.route_id      = c_route_id
                         )
                GROUP BY res.object_id,
                         res.schedule_seq
                )
       GROUP BY object_id
       );*/

Line: 3478


SELECT route_operation_id, oper_duration
FROM ( SELECT  SUM(RES_DURATION) OPER_DURATION, object_id
           FROM (SELECT  MAX(res.duration) RES_DURATION, res.object_id
                     FROM     ahl_rt_oper_resources res
                     WHERE    res.association_type_code = 'OPERATION'
                     AND      res.scheduled_type_id = 1
                     AND      res.object_id IN
                     (SELECT oper.operation_id
                       FROM    ahl_operations_b oper, ahl_route_operations ass , ahl_routes_app_v rou
                       WHERE (
                                   TRUNC(nvl(c_rou_start_date,rou.start_date_active)) >= TRUNC(oper.start_date_active)
                                   AND
                                   TRUNC(NVL(oper.end_date_active, nvl(c_rou_start_date,rou.start_date_active) + 1)) > TRUNC(nvl(c_rou_start_date,rou.start_date_active))
                                 )
                         AND     rou.route_id      = c_route_id
                         AND     oper.operation_id = ass.operation_id
                         AND     ass.route_id      = c_route_id
                         ) GROUP BY res.object_id, res.schedule_seq
                       ) GROUP BY object_id
       ) res, ahl_route_operations ro
WHERE res.object_id = ro.operation_id
AND ro.route_id      = c_route_id ;

Line: 3504


SELECT SYS_CONNECT_BY_PATH(from_rt_op_id,'|')||'|'||to_rt_op_id Path
FROM  ahl_rt_oper_dependencies od
WHERE dependency_code = 'STARTSAFTEREND' AND from_rt_op_id
IN ( SELECT route_operation_id
       FROM ahl_route_operations
       WHERE ROUTE_ID =c_route_id
       AND route_operation_id =od.from_rt_op_id
    )
START WITH from_rt_op_id = c_from_rt_op_id
CONNECT BY PRIOR to_rt_op_id = from_rt_op_id;

Line: 3631


SELECT time_span
FROM   AHL_ROUTES_APP_V
WHERE  route_id = c_route_id;

Line: 3638


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

Line: 3712


SELECT time_span
FROM   AHL_ROUTES_APP_V
WHERE  route_id = c_route_id;

Line: 3720


SELECT rou.time_span
FROM   AHL_ROUTES_APP_V rou,
       AHL_OPERATIONS_B oper
WHERE  (
         TRUNC(rou.start_date_active)                               >= TRUNC(oper.start_date_active)
         AND
         TRUNC(NVL(oper.end_date_active, rou.start_date_active + 1)) > TRUNC(rou.start_date_active)
       )
AND    oper.operation_id = c_operation_id
AND    rou.route_id      = c_route_id;

Line: 3738


SELECT NVL(SUM(RES_DURATION),0)
FROM   ( SELECT  MAX( duration ) RES_DURATION
       FROM     AHL_RT_OPER_RESOURCES
       WHERE    association_type_code = 'OPERATION'
       AND      scheduled_type_id     = 1
       AND      object_id             = c_operation_id
       GROUP BY schedule_seq
       );

Line: 3794


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: 3802


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: 3868


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

Line: 3943


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: 3992


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: 4011


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: 4048


/*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: 4070


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: 4182


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

Line: 4232


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

Line: 4281


SELECT mc_id
FROM AHL_MC_HEADERS_VL mc_header1
where
mc_header1.name = c_mc_name and
mc_header1.config_status_code = 'COMPLETE'
AND rowid = (SELECT MIN(rowid) FROM AHL_MC_HEADERS_VL where config_status_code = 'COMPLETE' and name = mc_header1.name);

Line: 4289


SELECT mc_id
FROM AHL_MC_HEADERS_VL mc_header1
where
mc_header1.mc_id = c_mc_id and
mc_header1.config_status_code = 'COMPLETE'
AND rowid = (SELECT MIN(rowid) FROM AHL_MC_HEADERS_VL where config_status_code = 'COMPLETE' and mc_id = mc_header1.mc_id);

Line: 4358


SELECT DISTINCT amr.position_key
FROM
AHL_MC_RELATIONSHIPS amr,
FND_LOOKUP_VALUES_VL flv,
AHL_MC_HEADERS_VL amh
WHERE
amr.mc_header_id = amh.mc_header_id AND
amh.mc_id = NVL(c_mc_id,-1) AND
amh.config_status_code = 'COMPLETE' AND
nvl(amr.active_end_date, SYSDATE + 1) > SYSDATE AND
flv.lookup_type = 'AHL_POSITION_REFERENCE' AND
NVL(flv.ENABLED_FLAG,'N') = 'Y' AND
NVL(flv.END_DATE_ACTIVE, SYSDATE +1 ) > SYSDATE AND
flv.lookup_code = amr.position_ref_code AND
flv.meaning = c_mc_position and
nvl(amr.parent_relationship_id, -1) not in (
select rel.relationship_id
from AHL_MC_PATH_POSITION_NODES ampp, AHL_MC_RULE_STATEMENTS amrs, ahl_mc_headers_b amh2,ahl_mc_rules_b mcrule,ahl_mc_relationships rel
where ampp.mc_id = c_mc_id and
ampp.path_position_node_id = amrs.subject_id and
amrs.subject_type = 'POSITION' and
amrs.operator = 'MUST_HAVE' and
amrs.object_type = 'TOT_CHILD_QUANTITY' and
amh2.mc_id = ampp.mc_id and
amh2.mc_header_id = mcrule.mc_header_id and
mcrule.rule_id = amrs.rule_id  and
rel.position_key = ampp.position_key and
nvl(mcrule.active_end_date,SYSDATE +1) > SYSDATE and
amh2.config_status_code = 'COMPLETE' and
nvl(rel.active_end_date, SYSDATE + 1) > SYSDATE);

Line: 4391


SELECT DISTINCT amr.position_key
FROM
AHL_MC_RELATIONSHIPS amr,
AHL_MC_HEADERS_VL amh
WHERE
amr.mc_header_id = amh.mc_header_id and
amh.mc_id = NVL(c_mc_id,-1) and
amh.config_status_code = 'COMPLETE' AND
nvl(amr.active_end_date, SYSDATE + 1) > SYSDATE AND
amr.position_key = c_mc_position_key and
nvl(amr.parent_relationship_id, -1) not in (
select rel.relationship_id
from AHL_MC_PATH_POSITION_NODES ampp, AHL_MC_RULE_STATEMENTS amrs, ahl_mc_headers_b amh2,ahl_mc_rules_b mcrule,ahl_mc_relationships rel
where ampp.mc_id = c_mc_id and
ampp.path_position_node_id = amrs.subject_id and
amrs.subject_type = 'POSITION' and
amrs.operator = 'MUST_HAVE' and
amrs.object_type = 'TOT_CHILD_QUANTITY' and
amh2.mc_id = ampp.mc_id and
amh2.mc_header_id = mcrule.mc_header_id and
mcrule.rule_id = amrs.rule_id  and
rel.position_key = ampp.position_key and
nvl(mcrule.active_end_date,SYSDATE +1) > SYSDATE and
amh2.config_status_code = 'COMPLETE' and
nvl(rel.active_end_date, SYSDATE + 1) > SYSDATE);