APPS.MSC_X_UTIL SQL Statements

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

Line: 15

    SELECT party_name
    INTO   l_party_name
    FROM   hz_parties
    WHERE  party_id = p_party_id;

Line: 57

   SELECT category_name
   INTO l_category_code
   FROM   msc_item_categories mic,
          msc_trading_partners tp,
          msc_trading_partner_maps map
   WHERE  map.company_key = l_org_id
          and map.map_type = 2
          and map.tp_key = tp.partner_id
          and tp.sr_tp_id = mic.organization_id
          and mic.sr_instance_id = tp.sr_instance_id
          and mic.inventory_item_id = p_inventory_item_id
          and mic.category_set_id = FND_PROFILE.VALUE('MSCX_CP_HZ_CATEGORY_SET');

Line: 109

   SELECT buyer_name into l_buyer_code
   FROM   msc_system_items msi,
          msc_trading_partners tp,
          msc_trading_partner_maps map
   WHERE  map.company_key = l_org_id
          and map.map_type = 2
          and map.tp_key = tp.partner_id
          and tp.sr_tp_id = msi.organization_id
          and msi.sr_instance_id = tp.sr_instance_id
          and msi.inventory_item_id = p_inventory_item_id
	  and msi.plan_id = -1;

Line: 139

/*   select max(tpx.xref_ext_value)
  into l_xref_party_name
  from ect_xref_dtl tpx
  where tpx.xref_int_value = p_xref_party_id
  and tpx.party_id = p_party_id
  and tpx.direction = 'IN'
  and tpx.xref_category_id = 9 ;

Line: 215

   select uom_class
   into l_uom_class
   from msc_units_of_measure
   where uom_code = p_uom_code
   and rownum = 1;

Line: 227

    select uom_class
    into l_dest_uom_class
    from msc_units_of_measure
    where uom_code = p_dest_uom_code
   and rownum = 1;

Line: 241

      select muc1.conversion_rate/muc2.conversion_rate
      INTO
      p_conv_rate
      FROM
      msc_uom_conversions muc1,
       msc_uom_conversions muc2
      where muc1.inventory_item_id = 0
      and muc2.inventory_item_id = 0
      and muc1.uom_class = muc2.uom_class
      and muc1.uom_class = l_uom_class
      and muc1.uom_code = p_uom_code
      and muc2.uom_code = p_dest_uom_code
      and rownum = 1;

Line: 263

        select  muc.conversion_rate
        INTO
        p_conv_rate
        FROM
        msc_uom_conversions_view muc
        where muc.inventory_item_id = p_inventory_item_id
        and muc.primary_uom_code = p_uom_code
        and muc.uom_code = p_dest_uom_code
      and rownum = 1;

Line: 275

         select  muc.conversion_rate
         INTO
         p_conv_rate
         FROM
         msc_uom_conversions_view muc
         where muc.inventory_item_id = p_inventory_item_id
         and muc.primary_uom_code = p_dest_uom_code
         and muc.uom_code = p_uom_code
         and rownum = 1;

Line: 363

FUNCTION UPDATE_SHIP_RCPT_DATES (
                                  p_customer_id IN NUMBER,
                                  p_customer_site_id IN NUMBER,
                                  p_supplier_id IN NUMBER,
                                  p_supplier_site_id IN NUMBER,
                                  p_order_type IN NUMBER,
                          p_item_id IN NUMBER,
                                  p_ship_date IN  DATE,
                                  p_rcpt_date  IN DATE) RETURN DATE IS

l_org_id NUMBER NULL;

Line: 488

      SELECT tp_key
      INTO l_tp_org_partner_id
      FROM msc_trading_partner_maps map
      WHERE map.map_type = 2
      and map.company_key = l_org_id;

Line: 509

      SELECT sr_tp_id,
         sr_instance_id
      INTO l_sr_tp_id,
       l_sr_instance_id
      FROM msc_trading_partners
      WHERE partner_id = l_tp_org_partner_id;

Line: 532

      SELECT tpl.sr_tp_id INTO
      l_tp_customer_id
      FROM
      msc_tp_id_lid tpl,
      msc_trading_partner_maps map,
      msc_company_relationships rels
      WHERE tpl.tp_id = map.tp_key
      and tpl.partner_type = 2
      and tpl.sr_company_id = -1
      and tpl.sr_instance_id = l_sr_instance_id
       and map.map_type = 1
        and map.company_key = rels.relationship_id
      and rels.object_id = l_customer_id
        and rels.subject_id = l_company_id
        and rels.relationship_type = 1;

Line: 566

      SELECT tps.sr_tp_site_id into
      l_tp_customer_site_id
      FROM
      msc_tp_site_id_lid tps,
      msc_trading_partner_maps map
      WHERE tps.sr_company_id = -1
      and tps.tp_site_id = map.tp_key
      and tps.sr_instance_id = l_sr_instance_id
      and tps.partner_type = 2
      and map.map_type = 3
        and map.company_key = l_customer_site_id;

Line: 579

         SELECT tps.sr_tp_site_id into
         l_tp_customer_site_id
         FROM
         msc_tp_site_id_lid tps,
         msc_trading_partner_sites tp_sites,
         msc_trading_partner_maps map
         WHERE tps.sr_company_id = -1
         and tps.tp_site_id = tp_sites.partner_site_id
         and tps.sr_instance_id = l_sr_instance_id
         and tps.partner_type = 2
         and tp_sites.partner_site_id = map.tp_key
         and tp_sites.tp_site_code = 'SHIP_TO'
         and map.map_type = 3
         and map.company_key = l_customer_site_id;

Line: 622

       select mrp_atp_schedule_temp_s.nextval
       into   l_session_id
       from dual;

Line: 735

		SELECT tp_key
		INTO l_tp_org_partner_id
		FROM msc_trading_partner_maps map
		WHERE map.map_type = 2
		and map.company_key = l_org_id;

Line: 756

		SELECT sr_tp_id,
		   sr_instance_id
		INTO l_sr_tp_id,
		 l_sr_instance_id
		FROM msc_trading_partners
		WHERE partner_id = l_tp_org_partner_id;

Line: 778

        SELECT map.tp_key INTO
        l_tp_supplier_id
        FROM
        msc_trading_partner_maps map,
        msc_company_relationships rels
        WHERE map.map_type = 1
        and map.company_key = rels.relationship_id
        and rels.object_id = l_supplier_id
        and rels.subject_id = l_company_id
        and rels.relationship_type = 2;

Line: 800

        SELECT map.tp_key INTO
        l_tp_supplier_site_id
        FROM
        msc_trading_partner_maps map
        WHERE map.map_type = 3
        and map.company_key = l_supplier_site_id;

Line: 825

       select mrp_atp_schedule_temp_s.nextval
       into   l_session_id
       from dual;

Line: 883

END UPDATE_SHIP_RCPT_DATES;

Line: 898

   select to_number(to_char(sysdate,  'j'))
   INTO jul_ship_date
   FROM dual;

Line: 905

   ** It is built on top of update_ship_rcpt_dates fn. It passes
   ** in a ship date and gets back a recipt date. The difference
   ** between the 2 is returned as lead time.
   */


   BEGIN
    rcpt_date := UPDATE_SHIP_RCPT_DATES (
                                  p_customer_id,
                                  p_customer_site_id,
                          p_publisher_id,
                          p_publisher_site_id,
                                  14, /* Hard Coded to Sales Order */
                                  NULL,
                                  ship_date,
                                  NULL);

Line: 926

   select to_number(to_char(rcpt_date, 'j'))
   INTO jul_rcpt_date FROM dual;

Line: 944

    select meaning
    into   l_order_type_desc
    from   mfg_lookups
    where  lookup_type = p_lookup_type
    and    lookup_code = p_order_type_code;

Line: 977

	  SELECT tp.sr_tp_id,tp.sr_instance_id
	    INTO aps_partner_id, aps_sr_instance_id
	    FROM msc_trading_partner_maps map,
		 msc_trading_partners tp
	   WHERE map.map_type = G_ORGANIZATION_MAPPING
	     AND map.company_key = p_sce_company_site_id
	     AND map.tp_key = tp.partner_id;

Line: 988

	      SELECT map.tp_key
		INTO aps_partner_id
	        FROM msc_trading_partner_maps map,
		     msc_company_relationships cr
	       WHERE map.map_type = G_COMPANY_MAPPING
	         AND cr.object_id = p_sce_company_id
	         AND map.company_key = cr.relationship_id
	         AND cr.relationship_type = p_relationship_type
	         AND cr.subject_id = OEM_COMPANY_ID;

Line: 1002

              SELECT map.tp_key
		INTO aps_partner_site_id
	        FROM msc_trading_partner_maps map
	       WHERE map.map_type = G_COMPANY_SITE_MAPPING
	         AND map.company_key = p_sce_company_site_id;

Line: 1010

		     SELECT tp_sites.partner_site_id
		       INTO aps_partner_site_id
		       FROM msc_trading_partner_sites tp_sites,
			    msc_trading_partner_maps map
		      WHERE tp_sites.partner_site_id = map.tp_key
		        and tp_sites.tp_site_code = 'SHIP_TO'
		        and tp_sites.partner_type = 2
		        and map.map_type = G_COMPANY_SITE_MAPPING
		        and map.company_key = p_sce_company_site_id;

Line: 1127

	     select nvl(ca1.CALENDAR_CODE, ca.CALENDAR_CODE)
	       into lv_calendar_code
	       from msc_calendar_assignments  ca,
		    msc_calendar_assignments  ca1
	      where ca.sr_instance_id = aps_sr_instance_id
		and ca.CALENDAR_TYPE = 'RECEIVING'
		and ca.partner_type = 2
		and ca.partner_id = aps_cust_partner_id
		and ca.ORGANIZATION_ID is null
		and ca.ASSOCIATION_TYPE = G_CUSTOMER
		and ca1.sr_instance_id(+) = ca.sr_instance_id
		and ca1.CALENDAR_TYPE(+) = ca.CALENDAR_TYPE
		and ca1.partner_type(+) = ca.partner_type
		and ca1.ORGANIZATION_ID is null
		and ca1.ASSOCIATION_TYPE(+) = G_CUSTOMER_SITE
		and ca1.partner_id(+) = ca.partner_id
		and ca1.partner_site_id(+) = aps_cust_partner_site_id;

Line: 1150

			select nvl(ca.CALENDAR_CODE, tp.calendar_code)
	                  into lv_calendar_code
			  from msc_trading_partners tp,
			       msc_calendar_assignments    ca
			 where tp.sr_instance_id = aps_sr_instance_id
			   and tp.sr_tp_id = aps_org_partner_id
			   and tp.partner_type = 3
			   and ca.sr_instance_id(+) = tp.sr_instance_id
			   and ca.ORGANIZATION_ID(+) = tp.sr_tp_id
			   and ca.partner_type(+) = tp.partner_type
			   and ca.CALENDAR_TYPE(+) = 'SHIPPING'
			   and ca.ASSOCIATION_TYPE(+) = G_ORGANIZATION;

Line: 1191

        select nvl(ca.CALENDAR_CODE, tp.calendar_code)
	  into lv_calendar_code
	  from msc_calendar_assignments  ca,
	       msc_trading_partners tp
         where tp.sr_instance_id = aps_sr_instance_id
	   and tp.sr_tp_id = aps_org_partner_id
	   and tp.partner_type = 3
	   and ca.sr_instance_id(+) = tp.sr_instance_id
	   and ca.ORGANIZATION_ID(+) = tp.sr_tp_id
	   and ca.partner_type(+) = tp.partner_type
	   and ca.CALENDAR_TYPE(+) = 'RECEIVING'
	   and ca.ASSOCIATION_TYPE(+) = G_ORGANIZATION;

Line: 1211

	     select nvl(ca1.CALENDAR_CODE, ca.CALENDAR_CODE),
		    nvl(ca1.sr_instance_id,ca.sr_instance_id)
	       into lv_calendar_code,
		    aps_sr_instance_id
	       from msc_calendar_assignments  ca,
		    msc_calendar_assignments  ca1
	      where ca.CALENDAR_TYPE = 'RECEIVING'
		and ca.partner_type = 2
		and ca.partner_id = aps_cust_partner_id
		and ca.ORGANIZATION_ID is null
		and ca.ASSOCIATION_TYPE = G_CUSTOMER
		and ca1.sr_instance_id(+) = ca.sr_instance_id
		and ca1.CALENDAR_TYPE(+) = ca.CALENDAR_TYPE
		and ca1.partner_type(+) = ca.partner_type
		and ca1.ORGANIZATION_ID is null
		and ca1.ASSOCIATION_TYPE(+) = G_CUSTOMER_SITE
		and ca1.partner_id(+) = ca.partner_id
		and ca1.partner_site_id(+) = aps_cust_partner_site_id;

Line: 1239

	     select nvl(ca1.CALENDAR_CODE, ca.CALENDAR_CODE)
	       into lv_calendar_code
	       from msc_calendar_assignments  ca,
		    msc_calendar_assignments  ca1
	      where ca.sr_instance_id = aps_sr_instance_id
		and ca.CALENDAR_TYPE = 'RECEIVING'
		and ca.partner_type = 2
		and ca.partner_id = aps_cust_partner_id
		and ca.ORGANIZATION_ID is null
		and ca.ASSOCIATION_TYPE = G_CUSTOMER
		and ca1.sr_instance_id(+) = ca.sr_instance_id
		and ca1.CALENDAR_TYPE(+) = ca.CALENDAR_TYPE
		and ca1.partner_type(+) = ca.partner_type
		and ca1.ORGANIZATION_ID is null
		and ca1.ASSOCIATION_TYPE(+) = G_CUSTOMER_SITE
		and ca1.partner_id(+) = ca.partner_id
		and ca1.partner_site_id(+) = aps_cust_partner_site_id;

Line: 1261

			select nvl(ca.CALENDAR_CODE, tp.calendar_code)
	                  into lv_calendar_code
			  from msc_trading_partners tp,
			       msc_calendar_assignments    ca
			 where tp.sr_instance_id = aps_sr_instance_id
			   and tp.sr_tp_id = aps_org_partner_id
			   and tp.partner_type = 3
			   and ca.sr_instance_id(+) = tp.sr_instance_id
			   and ca.ORGANIZATION_ID(+) = tp.sr_tp_id
			   and ca.partner_type(+) = tp.partner_type
			   and ca.CALENDAR_TYPE(+) = 'SHIPPING'
			   and ca.ASSOCIATION_TYPE(+) = G_ORGANIZATION;

Line: 1288

	     select sr_instance_id
	       into p_sr_instance_id
	       from msc_calendar_dates
	      where calendar_code  = p_calendar_code
	        and rownum = 1;

Line: 1307

SELECT decode(upper(mtps.shipping_control), 'BUYER', 2,1)
from msc_trading_partner_maps mtpm,
     msc_trading_partner_sites mtps
where mtpm.company_key = a_site_id
and  mtpm.map_type = G_COMPANY_SITE
and  mtpm.tp_key = mtps.partner_site_id
and  mtps.partner_type = a_partner_type;

Line: 1317

SELECT decode(upper(mtps.shipping_control), 'BUYER', 2,1)
from msc_trading_partner_maps mtpm,
     msc_trading_partner_sites mtps
where mtpm.company_key = a_site_id
and  mtpm.map_type = G_COMPANY_SITE
and  mtpm.tp_key = mtps.partner_site_id
and  mtps.partner_type = a_partner_type
AND mtps.tp_site_code = 'SHIP_TO'
;

Line: 1404

     select mcs.company_id, mcs.company_site_id
      INTO l_customer_id, l_customer_site_id
      from msc_companies mc,
           msc_company_sites mcs
     where mc.company_id = mcs.company_id
     and   upper(mc.company_name) = upper(p_customer_name)
     and   upper(mcs.company_site_name) = upper(p_customer_site_name);

Line: 1412

     select mcs.company_id, mcs.company_site_id
      INTO l_supplier_id, l_supplier_site_id
      from msc_companies mc,
           msc_company_sites mcs
     where mc.company_id = mcs.company_id
     and   upper(mc.company_name) = upper(p_supplier_name)
     and   upper(mcs.company_site_name) = upper(p_supplier_site_name);

Line: 1444

     SELECT distinct buyer_name into l_buyer_code
   FROM   msc_system_items msi
   WHERE
	  msi.organization_id=p_organization_id
          and msi.sr_instance_id = p_sr_instance_id
          and msi.inventory_item_id = p_inventory_item_id
	  and   msi.plan_id = -1;