APPS.AHL_FMP_COMMON_PVT SQL Statements

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

Line: 23

SELECT  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: 33

SELECT 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: 126

SELECT DISTINCT MI.inventory_item_id,
                MI.inventory_item_flag,
                MI.eng_item_flag,
                MI.build_in_wip_flag,
                MI.wip_supply_type,
                MI.eam_item_type,
                MI.comms_nl_trackable_flag,
                MI.serv_req_enabled_code
FROM            MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
WHERE DECODE(G_APPLN_USAGE,'PM','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: 146

SELECT DISTINCT MI.inventory_item_id,
                MI.inventory_item_flag,
                MI.eng_item_flag,
                MI.build_in_wip_flag,
                MI.wip_supply_type,
                MI.eam_item_type,
                MI.comms_nl_trackable_flag,
                MI.serv_req_enabled_code
FROM            MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
WHERE DECODE(G_APPLN_USAGE,'PM','Y',MP.eam_enabled_flag )='Y'
AND             MP.organization_id = MI.organization_id
AND             MI.inventory_item_id = c_inventory_item_id
AND             MI.enabled_flag = 'Y'
AND             SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
                        AND     NVL( MI.end_date_active, SYSDATE );

Line: 295

SELECT  pc_node_id
FROM            AHL_PC_NODES_B
WHERE           name = c_pc_node_name;

Line: 301

SELECT  pc_node_id
FROM            AHL_PC_NODES_B
WHERE           pc_node_id = c_pc_node_id;

Line: 390

SELECT DISTINCT relationship_id,
                NVL( active_start_date, SYSDATE ),
                NVL( active_end_date, SYSDATE + 1 )
FROM            AHL_MASTER_CONFIG_DETAILS_V
WHERE           position_ref_meaning = c_position_ref_meaning;

Line: 398

SELECT DISTINCT relationship_id,
                NVL( active_start_date, SYSDATE ),
                NVL( active_end_date, SYSDATE + 1 )
FROM            AHL_RELATIONSHIPS_VL
WHERE           relationship_id = c_relationship_id;

Line: 409

SELECT relationship_id
FROM  ahl_mc_relationships mcr,
ahl_mc_headers_b mch,   ahl_mc_path_position_nodes mcp
where 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_relationship_id,'-1'))
and mcp.path_position_id = nvl(c_relationship_id,'-1');

Line: 423

SELECT 'x'
FROM ahl_mc_relationships
WHERE parent_relationship_id IS NULL
START WITH relationship_id = c_relationship_id
           AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
CONNECT BY PRIOR parent_relationship_id = relationship_id
           AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1));

Line: 517

SELECT mch.mc_header_id
FROM ahl_mc_headers_b mch, ahl_mc_path_position_nodes mcp
WHERE
mch.mc_id = mcp.mc_id and
mch.version_number = nvl(mcp.version_number, mch.version_number) and
mcp.sequence = (select max(sequence)
                from ahl_mc_path_position_nodes
                where path_position_id = nvl(c_relationship_id,'-1'))
and mcp.path_position_id = nvl(c_relationship_id,'-1');

Line: 529

SELECT 'X'
FROM ahl_mc_headers_b
WHERE mc_header_id = c_mc_header_id AND
config_status_code = 'COMPLETE';

Line: 611

SELECT 'X'
FROM ahl_mc_relationships mcr,
     ahl_mc_headers_b mch,
     ahl_mc_path_position_nodes mcp,
     --ahl_item_associations_v igass ,
   ahl_item_associations_vl igass, --priyan changes due to performance reasons , Refer Bug # 5078530
     mtl_system_items_kfv MTL,
     mtl_item_status STAT,
     fnd_lookup_values_vl IT
WHERE 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_relationship_id,-1)
                          and path_position_id=mcp.path_position_id)
      and mcp.path_position_id = nvl(c_relationship_id,-1)
      and mcr.item_group_id = igass.item_group_id
      and igass.INVENTORY_ITEM_ID = mtl.INVENTORY_ITEM_ID
      and igass.INVENTORY_ORG_ID = mtl.ORGANIZATION_ID
      and mtl.inventory_item_id=c_inventory_item_id
      and MTL.service_item_flag = 'N'
      and STAT.inventory_item_status_code = MTL.inventory_item_status_code
      and IT.lookup_code (+) = MTL.item_type
      and IT.lookup_type (+) = 'ITEM_TYPE'
      and trunc(sysdate) between trunc(nvl(it.start_date_active,sysdate))
      and trunc(nvl(IT.end_date_active,sysdate+1));

Line: 689

SELECT 'X'
FROM   csi_counter_template_vl C,
       --CS_COUNTER_GROUPS CG,
       CS_CSI_COUNTER_GROUPS CG,
       CS_CTR_ASSOCIATIONS CA,
       AHL_POSITION_ALTERNATES_V PA
WHERE  C.DEFAULTED_GROUP_ID = CG.COUNTER_GROUP_ID
       AND CG.TEMPLATE_FLAG = 'Y'
       AND C.COUNTER_ID = c_counter_id
       AND C.DIRECTION IN ('A',
                           'B')
       AND CG.COUNTER_GROUP_ID = CA.COUNTER_GROUP_ID
       AND CA.SOURCE_OBJECT_ID = PA.INVENTORY_ITEM_ID
       AND PA.RELATIONSHIP_ID = c_relationship_id;

Line: 708

SELECT DISTINCT C.counter_id,
                C.counter_group_id
FROM            CS_COUNTERS C, CS_COUNTER_GROUPS CG
WHERE           CG.template_flag = 'Y'
AND             C.counter_group_id = CG.counter_group_id
AND             C.name = c_counter_name
AND             C.DIRECTION in ('A','B'); */

Line: 720

SELECT DISTINCT
  C.COUNTER_ID,
  C.DEFAULTED_GROUP_ID  COUNTER_GROUP_ID
FROM
  CSI_COUNTER_TEMPLATE_VL C,
  --CS_COUNTER_GROUPS CG
  --Priyan
  --Perf changes . Refer Bug # 4913671
  CS_CSI_COUNTER_GROUPS CG
WHERE
    CG.TEMPLATE_FLAG = 'Y'
  AND C.DEFAULTED_GROUP_ID   = CG.COUNTER_GROUP_ID
  AND C.NAME = c_counter_name
  AND C.DIRECTION in ('A','B');

Line: 740

SELECT DISTINCT C.counter_id,
                C.counter_group_id
FROM            CS_COUNTERS C, CS_COUNTER_GROUPS CG
WHERE           CG.template_flag = 'Y'
AND             C.counter_group_id = CG.counter_group_id
AND             C.counter_id = c_counter_id;

Line: 752

 SELECT DISTINCT
  C.COUNTER_ID,
  C.DEFAULTED_GROUP_ID  COUNTER_GROUP_ID
 FROM
  CSI_COUNTER_TEMPLATE_VL C,
  --CS_COUNTER_GROUPS CG
  --Priyan
  --Perf changes . Refer Bug # 4913671
  CS_CSI_COUNTER_GROUPS CG
 WHERE
    CG.TEMPLATE_FLAG = 'Y'
  AND C.DEFAULTED_GROUP_ID = CG.COUNTER_GROUP_ID
  AND C.COUNTER_ID = c_counter_id ;

Line: 769

SELECT 'X'
FROM --cs_counters c,
csi_counter_template_vl C,
--cs_counter_groups CG,
CS_CSI_COUNTER_GROUPS CG,
cs_ctr_associations CA
where --C.counter_group_id = CG.counter_group_id
C.defaulted_group_id = CG.counter_group_id
and CG.template_flag = 'Y'
and C.COUNTER_ID = c_counter_id
and C.direction in ('A','B')
and CG.counter_group_id = CA.counter_group_id
and CA.source_object_id = c_inventory_item_id;

Line: 785

SELECT DISTINCT source_object_id
FROM            CS_CTR_ASSOCIATIONS
WHERE           counter_group_id = c_counter_group_id;

Line: 792

SELECT DISTINCT source_object_id
FROM            CS_CTR_ASSOCIATIONS
WHERE           counter_group_id = c_counter_group_id;

Line: 916

SELECT DISTINCT territory_code
FROM            FND_TERRITORIES_VL
WHERE           territory_short_name = c_country_name;

Line: 922

SELECT DISTINCT territory_code
FROM            FND_TERRITORIES_VL
WHERE           territory_code = c_country_code;

Line: 1011

/*SELECT DISTINCT manufacturer_id,
                inventory_item_id
FROM            MTL_MFG_PART_NUMBERS_ALL_V
WHERE           manufacturer_name = c_manufacturer_name;*/

Line: 1020

select distinct     b.manufacturer_id,
                    a.inventory_item_id
from                mtl_manufacturers b,
                    mtl_mfg_part_numbers a
where               a.manufacturer_id = b.manufacturer_id
and                 b.manufacturer_name = c_manufacturer_name
and                 a.organization_id in ( select distinct
                        m.master_organization_id
                      from inv_organization_info_v org,
                         mtl_parameters m
                      where org.organization_id = m.organization_id
                        and nvl(org.operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id() );

Line: 1036

/*SELECT DISTINCT manufacturer_id,
                inventory_item_id
FROM            MTL_MFG_PART_NUMBERS_ALL_V
WHERE           manufacturer_id = c_manufacturer_id;*/

Line: 1045

select distinct     manufacturer_id,
                    inventory_item_id
from                mtl_mfg_part_numbers
where               manufacturer_id = c_manufacturer_id
and                 organization_id in ( select distinct
                        m.master_organization_id
                      from inv_organization_info_v org,
                        mtl_parameters m
                      where org.organization_id = m.organization_id
                        and nvl(org.operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id() );

Line: 1222

SELECT          'X'
FROM            DUAL
WHERE           TO_NUMBER( c_serial_number_to ) >=
                TO_NUMBER( c_serial_number_from );

Line: 1229

SELECT          'X'
FROM            DUAL
WHERE           c_serial_number_to >= c_serial_number_from;

Line: 1290

SELECT mr_status_code
FROM   AHL_MR_HEADERS_APP_V
WHERE  mr_header_id = c_mr_header_id;

Line: 1348

SELECT object_version_number
FROM   AHL_MR_EFFECTIVITIES_APP_V
WHERE  mr_effectivity_id = c_mr_effectivity_id;

Line: 1404

SELECT   'X'
FROM     AHL_MR_EFFECTIVITIES_APP_V
WHERE    mr_header_id = c_mr_header_id
AND      threshold_date IS NOT NULL;

Line: 1411

SELECT   A.mr_effectivity_id,
         A.counter_id
FROM     AHL_MR_INTERVALS_APP_V A, AHL_MR_EFFECTIVITIES_APP_V B
WHERE    A.mr_effectivity_id = B.mr_effectivity_id
AND      B.mr_header_id = c_mr_header_id
GROUP BY A.mr_effectivity_id,
         A.counter_id
HAVING   count(*) > 1;

Line: 1422

SELECT   'X'
FROM     AHL_MR_INTERVALS_APP_V A, AHL_MR_EFFECTIVITIES_APP_V B
WHERE    ( A.start_date IS NOT NULL OR A.start_value IS NOT NULL )
AND      A.mr_effectivity_id = B.mr_effectivity_id
AND      B.mr_header_id = c_mr_header_id;

Line: 1534

  DELETE FROM AHL_APPLICABLE_MRS;

Line: 1563

           INSERT INTO AHL_APPLICABLE_MRS (
          CSI_ITEM_INSTANCE_ID,
          MR_HEADER_ID,
          MR_EFFECTIVITY_ID,
          REPETITIVE_FLAG   ,
          SHOW_REPETITIVE_CODE,
          COPY_ACCOMPLISHMENT_CODE,
          PRECEDING_MR_HEADER_ID,
            IMPLEMENT_STATUS_CODE,
          DESCENDENT_COUNT
           ) values
          ( l_appl_mrs_tbl(i).item_instance_id,
            l_appl_mrs_tbl(i).mr_header_id,
            l_appl_mrs_tbl(i).mr_effectivity_id,
            l_appl_mrs_tbl(i).repetitive_flag,
            l_appl_mrs_tbl(i).show_repetitive_code,
            l_appl_mrs_tbl(i).copy_accomplishment_flag,
            l_appl_mrs_tbl(i).preceding_mr_header_id,
            l_appl_mrs_tbl(i).implement_status_code,
            l_appl_mrs_tbl(i).descendent_count
        );

Line: 1604

SELECT mr_header_id
FROM ahl_mr_headers_app_v
WHERE title = p_mr_title
AND version_number = p_mr_version_number
AND  mr_status_code<>'TERMINATED'
AND TRUNC(NVL(effective_to,SYSDATE+1))> TRUNC(SYSDATE);

Line: 1666

SELECT mr_effectivity_id
FROM ahl_mr_effectivities_app_v
WHERE name = p_mr_effectivity_name
      AND
      mr_header_id = p_mr_header_id;

Line: 1716

SELECT TYPE_CODE
FROM   AHL_MR_HEADERS_B
WHERE  mr_header_id = c_mr_header_id;

Line: 1737

SELECT mr_status_code
FROM   AHL_MR_HEADERS_B
WHERE  mr_header_id = c_mr_header_id
AND trunc(nvl( effective_to, sysdate+1 ))>=trunc(sysdate);

Line: 1765

SELECT mr_status_code
FROM   AHL_MR_HEADERS_B
WHERE  mr_header_id = c_mr_header_id;

Line: 1821

SELECT 1
FROM DUAL
WHERE
EXISTS (
        SELECT 'x'
        FROM AHL_UNIT_EFFECTIVITIES_B unit,
             CSI_ITEM_INSTANCES csi,
             AHL_MR_EFFECTIVITIES eff
        where
            eff.inventory_item_id         = csi.INVENTORY_ITEM_ID
        and unit.CSI_ITEM_INSTANCE_ID     = csi.instance_id
        and unit.program_mr_header_id     = c_mr_header_id
        and eff.mr_effectivity_id         =c_effectivity_id
        and eff.object_version_number     =c_eff_obj ) ;

Line: 1873

SELECT 1
FROM DUAL
WHERE EXISTS ( SELECT 'x'
               FROM AHL_UNIT_EFFECTIVITIES_B unit,
                    CSI_ITEM_INSTANCES csi,
                    AHL_MR_EFFECTIVITIES eff
               where
                   eff.inventory_item_id         = csi.INVENTORY_ITEM_ID
               and unit.CSI_ITEM_INSTANCE_ID     = csi.instance_id
               and unit.mr_header_id   =eff.mr_header_id
               and eff.mr_effectivity_id =c_effectivity_id
               and eff.object_version_number=c_eff_obj) ;