DBA Data[Home] [Help]

APPS.MSD_SR_UTIL SQL Statements

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

Line: 59

 select name
 into l_org
 from HR_ORGANIZATION_UNITS
 where organization_id = p_org_id;
Line: 76

  select concatenated_segments
  into l_item
  from mtl_system_items_kfv
  where inventory_item_id = p_item_id AND organization_id is NULL;
Line: 81

  select concatenated_segments
  into l_item
  from mtl_system_items_kfv
  where inventory_item_id = p_item_id AND organization_id = p_org_id;
Line: 98

   select substrb(PARTY.PARTY_NAME,1,50) customer_name
   into l_cust
   from HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
   WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
   and CUST_ACCT.CUST_ACCOUNT_ID = p_cust_id;
Line: 122

 select name
 into l_ret
 from ra_salesreps_all
 where (salesrep_id = p_srep_id) AND
   ((org_id is NULL) OR (org_id = p_org_id));
Line: 143

        select  t.conversion_rate      std_conv_rate
        from  mtl_uom_conversions t
        where t.inventory_item_id in (p_item_id, 0)
        and   t.uom_code= p_uom_code
        and   nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
	and   t.conversion_rate is not null
        order by t.inventory_item_id desc;
Line: 168

      select to_number(parameter_value)
      into l_master_org
      from msd_setup_parameters
      where parameter_name = 'MSD_MASTER_ORG';
Line: 173

     select NVL(primary_uom_code,'Ea')
     into   l_master_uom
     from mtl_system_items
     where inventory_item_id = item_id
     and   organization_id = l_master_org;
Line: 200

 select list_price_per_unit
 into l_ret
 from mtl_system_items
 where (p_item_id = inventory_item_id) and
   ((organization_id = p_org_id) OR (organization_id is null));
Line: 217

select parameter_value
into c_global_currency_code
from msd_setup_parameters
where parameter_name = 'MSD_CURRENCY_CODE';
Line: 222

select parameter_value
into c_global_rate_type
from msd_setup_parameters
where parameter_name = 'MSD_CONVERSION_TYPE';
Line: 248

 select actual_departure_date
 into l_ret
 from wsh_departures
 where departure_id = p_departure_id;
Line: 276

select booked_date
into l_ret
from oe_order_headers_all
where header_id = p_header_id;
Line: 293

   select substrb(hp.PARTY_NAME,1,50) || '-' || csu.location
   into  l_ret
   from HZ_CUST_SITE_USES_ALL csu,
        HZ_CUST_ACCOUNTS ca,
	HZ_PARTIES hp,
	HZ_CUST_ACCT_SITES_ALL cas
   where csu.site_use_id = p_loc_id
   and csu.cust_acct_site_id = cas.cust_acct_site_id
   and cas.cust_account_id = ca.cust_account_id
   and ca.cust_account_id = hp.party_id;
Line: 318

  select fpg.MULTI_ORG_FLAG into x_product_group_type
  from fnd_product_groups fpg
  where fpg.product_group_type='Standard' ;
Line: 335

        select organization_id into x_master_org
        from mtl_parameters
        where organization_id = master_organization_id
        and   rownum < 2 ;
Line: 343

        select organization_id into x_master_org
        from mtl_parameters
        where organization_id = master_organization_id
        and   rownum < 2 ;
Line: 502

select meaning from fnd_lookup_values
where lookup_type = l_type
and language = userenv('LANG')
and lookup_code = l_code;
Line: 528

select 1
from
   msd_app_instance_orgs     morg,
   bom_bill_of_materials     bbm,
   mtl_system_items          msi,  -- Parent
   bom_inventory_components  bic
where
   bic.bill_sequence_id = bbm.bill_sequence_id
   and bbm.organization_id = morg.organization_id
   and msi.organization_id = bbm.organization_id
   and msi.inventory_item_id = bbm.assembly_item_id
   and msi.bom_item_type not in (C_PLANNING, C_STANDARD, C_PRODUCT_FAMILY)
   and bic.optional = C_YES
   and ( msi.bom_item_type = 2 or
         ( msi.bom_item_type = 1 and msi.ato_forecast_control in (1, 2) )
       )
   and bic.component_item_id = p_component_item_id;
Line: 570

select 1
from
   msd_app_instance_orgs     morg,
   bom_bill_of_materials     bbm,
   mtl_system_items          msi,  -- Parent
   bom_inventory_components  bic
where
   bic.bill_sequence_id = bbm.bill_sequence_id
   and bbm.organization_id = morg.organization_id
   and msi.organization_id = bbm.organization_id
   and msi.inventory_item_id = bbm.assembly_item_id
   and msi.bom_item_type not in (C_PLANNING, C_STANDARD, C_PRODUCT_FAMILY)
   and bic.optional = C_YES
   and ( msi.bom_item_type = 2 or
         ( msi.bom_item_type = 1 and msi.ato_forecast_control in (1, 2) )
       )
   and bic.component_item_id = p_component_item_id
   and bbm.organization_id = p_org_id;
Line: 630

select 1
from
   msd_app_instance_orgs     morg,
   bom_bill_of_materials     bbm,
   mtl_system_items          msi,   -- Parent
   bom_inventory_components  bic
where
   bic.component_sequence_id = l_component_seq_id
   and bic.bill_sequence_id = bbm.bill_sequence_id
   and bbm.organization_id = morg.organization_id
   and msi.organization_id = bbm.organization_id
   and msi.inventory_item_id = bbm.assembly_item_id
   and msi.bom_item_type not in (C_PLANNING, C_STANDARD, C_PRODUCT_FAMILY)
   and bic.optional = C_YES;
Line: 648

select component_sequence_id
from   oe_order_lines_all
where  line_id = p_parent_line_id;
Line: 658

select 1
from
   msd_app_instance_orgs     morg,
   bom_bill_of_materials     bbm,
   mtl_system_items          msi,
   bom_inventory_components  bic
where
   bic.bill_sequence_id = bbm.bill_sequence_id
   and bbm.organization_id = morg.organization_id
   and msi.organization_id = bbm.organization_id
   and msi.inventory_item_id = bic.component_item_id
   and
   ( (msi.ato_forecast_control = 3 and   bic.optional = C_YES)
      or
      msi.ato_forecast_control in (1, 2)
   )
   and bic.component_sequence_id = l_component_seq_id;
Line: 721

SELECT assemb.assembly_item_id, msi.bom_item_type
FROM bom_bill_of_materials assemb,
     bom_inventory_components  comp,
     mtl_system_items          msi
WHERE
     assemb.bill_sequence_id = comp.bill_sequence_id
     and comp.component_sequence_id = p_comp_seq_id
     and assemb.assembly_item_id = msi.inventory_item_id
     and assemb.organization_id = msi.organization_id;
Line: 772

select
inventory_item_id, item_type_code, link_to_line_id, ato_line_id
from oe_order_lines_all
where line_id = l_line_id;
Line: 819

SELECT
count(1)
FROM
mtl_system_items parent,
bom_inventory_components bic,
bom_bill_of_materials bom
WHERE
parent.bom_item_type = 5 and
parent.organization_id = bom.organization_id and
bom.ASSEMBLY_ITEM_ID = parent.inventory_item_id and
bom.bill_sequence_id = bic.bill_sequence_id and
bic.component_item_id = p_inventory_item_id and
bom.organization_id = p_org_id and
nvl(parent.ato_forecast_control, 3) <> 3;
Line: 873

select count(1)
from mtl_system_items itm
where inventory_item_id = (select inventory_item_id
    from oe_order_lines_all l
    where l.item_type_code = 'CONFIG'
    and l.header_id = p_header_id
    and l.org_id = p_org_id
    and l.ato_line_id = p_ato_line_id )
and itm.organization_id = p_org_id
and nvl(itm.ato_forecast_control, 3) <> 3
and itm.base_item_id is not null;
Line: 918

select decode(min(nvl(released_flag,'Y')),'N','Y','N')
from   oe_order_holds_all
where  header_id = p_header_id
AND    nvl(line_id,p_line_id) = p_line_id
group by header_id,nvl(line_id,p_line_id);
Line: 946

select application_column_name
from fnd_descr_flex_column_usages
where end_user_column_name  = x_end_user_column_name
and descriptive_flexfield_name = x_des_fname
and application_id = x_wsh_application_id;
Line: 982

select wrv.region_id
from wsh_region_locations wrl,
wsh_zone_regions wzr,
wsh_regions_v wrv
where wrl.location_id = p_location_id
and wrl.region_id = wzr.region_id
and wzr.parent_region_id = wrv.region_id
and wrv.region_type = 10
and decode(nvl(lower(p_zone_attr), '2'), 'attribute1', wrv.attribute1,
'attribute2', wrv.attribute2, 'attribute3',wrv.attribute3, 'attribute4',
wrv.attribute4, 'attribute5', wrv.attribute5, 'attribute6', wrv.attribute6,
'attribute7', wrv.attribute7, 'attribute8', wrv.attribute8, 'attribute9',
wrv.attribute9, 'attribute10', wrv.attribute10, 'attribute11', wrv.attribute11,
'attribute12', wrv.attribute12, 'attribute13', wrv.attribute13, 'attribute14',
wrv.attribute14, 'attribute15', wrv.attribute15, '2') = '1'
order by wrv.region_id;
Line: 1031

select organization_id
from csd_repair_job_xref crjx
where wip_entity_id = p_txn_source_id
order by repair_job_xref_id desc;
Line: 1070

select repair_line_id
  from csd_repair_job_xref
 where wip_entity_id = p_txn_source_id
order by repair_job_xref_id desc;
Line: 1077

  select decode(decode(nvl(lower(p_cust_filter), '1'), '1', '1', 'attribute1',
       cust.attribute1, 'attribute2', cust.attribute2, 'attribute3', cust.attribute3,
       'attribute4', cust.attribute4, 'attribute5', cust.attribute5, 'attribute6',
       cust.attribute6, 'attribute7', cust.attribute7, 'attribute8', cust.attribute8,
       'attribute9', cust.attribute9, 'attribute10', cust.attribute10, 'attribute11',
       cust.attribute11, 'attribute12', cust.attribute12, 'attribute13',cust.attribute13,
       'attribute14', cust.attribute14, 'attribute15', cust.attribute15, '2'), '1',
       nvl(cia.account_id, msd_sr_util.get_null_pk), msd_sr_util.get_null_pk)
  from cs_incidents_all_b cia,
       hz_cust_accounts cust,
       csd_repairs crp
  where crp.repair_line_id = p_repair_line_id
  and cia.incident_id = crp.incident_id
  and cust.cust_account_id = cia.account_id;
Line: 1134

select repair_line_id
  from csd_repair_job_xref
 where wip_entity_id = p_txn_source_id
order by repair_job_xref_id desc;
Line: 1140

select get_sr_zone_pk(hps.location_id, p_zone_filter)
from cs_incidents_all_b cia,
     hz_party_sites hps,
     csd_repairs crp
where crp.repair_line_id = p_repair_line_id
  and cia.incident_id = crp.incident_id
  and cia.install_site_id = hps.party_site_id (+);
Line: 1179

select 1
from csd_repair_job_xref crjx
where wip_entity_id = p_txn_source_id
order by repair_job_xref_id;
Line: 1214

select  decode( decode(nvl(lower(filtercust.parameter_value), '1'), '1', '1',
                                                                   'attribute1', cust.attribute1,
                                                                   'attribute2', cust.attribute2,
                                                                   'attribute3', cust.attribute3,
                                                                   'attribute4', cust.attribute4,
                                                                   'attribute5', cust.attribute5,
                                                                   'attribute6', cust.attribute6,
                                                                   'attribute7', cust.attribute7,
                                                                   'attribute8', cust.attribute8,
                                                                   'attribute9', cust.attribute9,
                                                                   'attribute10', cust.attribute10,
                                                                   'attribute11', cust.attribute11,
                                                                   'attribute12', cust.attribute12,
                                                                   'attribute13',cust.attribute13,
                                                                   'attribute14', cust.attribute14,
                                                                   'attribute15', cust.attribute15, '2')
                                                                 ,  '1',  nvl(cust.cust_account_id,msd_sr_util.get_null_pk), msd_sr_util.get_null_pk)
from
hz_cust_accounts cust,
(select parameter_value from msd_setup_parameters where parameter_name = 'MSD_CUSTOMER_ATTRIBUTE')  filtercust
where cust.party_id = p_party_id
order by cust.cust_account_id ASC;
Line: 1265

  select mrp_planning_code,pick_components_flag
  into lv_mrp_planning_code,lv_pick_components_flag
  from mtl_system_items_kfv
  where inventory_item_id = p_inventory_item_id
  and     organization_id = p_organization_id;
Line: 1322

SELECT c_onhand_qty
FROM (select fact.item_org_id,
             sum(decode(cal.report_date, p_transaction_date, onhand_qty, null)) c_onhand_qty
      from opi_inv_val_sum_mv fact,
           fii_time_rpt_struct_v cal
      where fact.time_id = cal.time_id
      and fact.item_org_id = (p_inventory_item_id||'-'||p_organization_id)
      and fact.organization_id = p_organization_id
      and fact.aggregation_level_flag = 0
      and cal.report_date = p_transaction_date
      and bitand(cal.record_type_id, 1143) = cal.record_type_id
      group by  fact.item_org_id );
Line: 1397

    l_sql_stmt := ' select wrv.region_id ' ||
		  ' from wsh_region_locations wrl, ' ||
                  ' wsh_zone_regions wzr, ' ||
		  ' wsh_regions_v wrv ' ||
		  ' where wrl.location_id = ''' || p_location_id  || '''' ||
		  ' and wrl.region_id = wzr.region_id  ' ||
		  ' and wzr.parent_region_id = wrv.region_id ' ||
		  ' and wrv.region_type = 10  ' ||
		  ' and decode(nvl(lower( ''' || p_zone_attr || ''' ), ''2''), ''attribute1'', wrv.attribute1,  ' ||
		  ' ''attribute2'', wrv.attribute2, ''attribute3'',wrv.attribute3, ''attribute4'',  ' ||
		  ' wrv.attribute4, ''attribute5'', wrv.attribute5, ''attribute6'', wrv.attribute6,  ' ||
		  ' ''attribute7'', wrv.attribute7, ''attribute8'', wrv.attribute8, ''attribute9'',  ' ||
		  ' wrv.attribute9, ''attribute10'', wrv.attribute10, ''attribute11'', wrv.attribute11,  ' ||
		  ' ''attribute12'', wrv.attribute12, ''attribute13'', wrv.attribute13, ''attribute14'',  ' ||
		  ' wrv.attribute14, ''attribute15'', wrv.attribute15, ''2'') = ''1'' ' ||
		  ' order by wrv.region_id';
Line: 1444

    l_sql_stmt := ' select wrv.zone ' ||
		  ' from wsh_region_locations wrl, ' ||
                  ' wsh_zone_regions wzr, ' ||
		  ' wsh_regions_v wrv ' ||
		  ' where wrl.location_id = ''' || p_location_id  || '''' ||
		  ' and wrl.region_id = wzr.region_id  ' ||
		  ' and wzr.parent_region_id = wrv.region_id ' ||
		  ' and wrv.region_type = 10  ' ||
		  ' and decode(nvl(lower( ''' || p_zone_attr || ''' ), ''2''), ''attribute1'', wrv.attribute1,  ' ||
		  ' ''attribute2'', wrv.attribute2, ''attribute3'',wrv.attribute3, ''attribute4'',  ' ||
		  ' wrv.attribute4, ''attribute5'', wrv.attribute5, ''attribute6'', wrv.attribute6,  ' ||
		  ' ''attribute7'', wrv.attribute7, ''attribute8'', wrv.attribute8, ''attribute9'',  ' ||
		  ' wrv.attribute9, ''attribute10'', wrv.attribute10, ''attribute11'', wrv.attribute11,  ' ||
		  ' ''attribute12'', wrv.attribute12, ''attribute13'', wrv.attribute13, ''attribute14'',  ' ||
		  ' wrv.attribute14, ''attribute15'', wrv.attribute15, ''2'') = ''1'' ' ||
		  ' order by wrv.region_id';
Line: 1491

    l_sql_stmt := ' select wrv.region_id ' ||
		  ' from wsh_region_locations wrl, ' ||
                  ' wsh_zone_regions wzr, ' ||
		  ' wsh_regions_v wrv ' ||
		  ' where wrl.location_id = ''' || p_location_id  || '''' ||
		  ' and wrl.region_id = wzr.region_id  ' ||
		  ' and wzr.parent_region_id = wrv.region_id ' ||
		  ' and wrv.region_type = 10  ' ||
		  ' and decode(nvl(lower( ''' || p_zone_attr || ''' ), ''2''), ''attribute1'', wrv.attribute1,  ' ||
		  ' ''attribute2'', wrv.attribute2, ''attribute3'',wrv.attribute3, ''attribute4'',  ' ||
		  ' wrv.attribute4, ''attribute5'', wrv.attribute5, ''attribute6'', wrv.attribute6,  ' ||
		  ' ''attribute7'', wrv.attribute7, ''attribute8'', wrv.attribute8, ''attribute9'',  ' ||
		  ' wrv.attribute9, ''attribute10'', wrv.attribute10, ''attribute11'', wrv.attribute11,  ' ||
		  ' ''attribute12'', wrv.attribute12, ''attribute13'', wrv.attribute13, ''attribute14'',  ' ||
		  ' wrv.attribute14, ''attribute15'', wrv.attribute15, ''2'') = ''1'' ' ||
		  ' order by wrv.region_id';
Line: 1538

    l_sql_stmt := ' select wrv.zone ' ||
		  ' from wsh_region_locations wrl, ' ||
                  ' wsh_zone_regions wzr, ' ||
		  ' wsh_regions_v wrv ' ||
		  ' where wrl.location_id = ''' || p_location_id  || '''' ||
		  ' and wrl.region_id = wzr.region_id  ' ||
		  ' and wzr.parent_region_id = wrv.region_id ' ||
		  ' and wrv.region_type = 10  ' ||
		  ' and decode(nvl(lower(''' || p_zone_attr || ''' ), ''2''), ''attribute1'', wrv.attribute1,  ' ||
		  ' ''attribute2'', wrv.attribute2, ''attribute3'',wrv.attribute3, ''attribute4'',  ' ||
		  ' wrv.attribute4, ''attribute5'', wrv.attribute5, ''attribute6'', wrv.attribute6,  ' ||
		  ' ''attribute7'', wrv.attribute7, ''attribute8'', wrv.attribute8, ''attribute9'',  ' ||
		  ' wrv.attribute9, ''attribute10'', wrv.attribute10, ''attribute11'', wrv.attribute11,  ' ||
		  ' ''attribute12'', wrv.attribute12, ''attribute13'', wrv.attribute13, ''attribute14'',  ' ||
		  ' wrv.attribute14, ''attribute15'', wrv.attribute15, ''2'') = ''1'' ' ||
		  ' order by wrv.region_id';