APPS.CTO_WORKBENCH_UTIL_PK SQL Statements

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

Line: 45

      Select Line_number,
             shipment_number,
             option_number,
             Component_number,
             Service_number
      into   line_number,
             shipment_number,
             option_number,
             component_number,
             service_number
      from   oe_order_lines_all
      where  ato_line_id = p_ato_line_id
      and    item_type_code = 'CONFIG';

Line: 103

           select line_id,
                  flow_status_code
           into   l_line_id,
                  l_flow_status_code
           from  oe_order_lines_all oel
           where oel.ato_line_id = p_ato_line_id
           and   oel.item_type_code = 'CONFIG';

Line: 153

      	select line_id
      	into   l_demand_line_id
      	from   oe_order_lines_all
      	where  ato_line_id = p_ato_line_id
      	and    item_type_code = 'CONFIG';

Line: 171

	select count(*)
	into v_ds_po_quantity
	from oe_drop_ship_sources ods
	where ods.line_id = l_demand_line_id
	--and ods.drop_ship_source_id = 2
  	and ods.po_header_id is not null;

Line: 182

	select count(*)
	into v_ds_req_quantity
	from oe_drop_ship_sources ods
	where ods.line_id = l_demand_line_id
	--and ods.drop_ship_source_id = 2
	and ods.po_header_id is null;

Line: 192

			select meaning
			into x_result
			from mfg_lookups
			where lookup_type = 'CTO_WB_SUPPLY_TYPE'
			and lookup_code = l_result;

Line: 204

	select meaning
	into x_result
	from mfg_lookups
	where lookup_type = 'CTO_WB_SUPPLY_TYPE'
	and lookup_code = l_result;

Line: 221

	select nvl(sum(reservation_quantity),0)
        into v_wip_quantity
        from mtl_reservations
        where  demand_source_type_id = decode (l_source_document_type_id, 10,
						   inv_reservation_global.g_source_type_internal_ord,
						   inv_reservation_global.g_source_type_oe )
        and    demand_source_line_id = l_demand_line_id
        and    supply_source_type_id = inv_reservation_global.g_source_type_wip;

Line: 234

	select count(*)
        into v_flow_count
        from wip_flow_schedules
        where demand_source_type = inv_reservation_global.g_source_type_oe
        and   demand_source_line =to_char(l_demand_line_id)
        and   status = 1;

Line: 244

			select meaning
			into x_result
			from mfg_lookups
			where lookup_type = 'CTO_WB_SUPPLY_TYPE'
			and lookup_code = l_result;

Line: 256

	select nvl(sum(reservation_quantity),0)
        into   v_po_quantity
        from   mtl_reservations
        where  demand_source_type_id = decode (l_source_document_type_id,
                                    		10, inv_reservation_global.g_source_type_internal_ord,
         					    inv_reservation_global.g_source_type_oe )
        and    demand_source_line_id = l_demand_line_id
        and    supply_source_type_id = inv_reservation_global.g_source_type_po;

Line: 268

			select meaning
			into x_result
			from mfg_lookups
			where lookup_type = 'CTO_WB_SUPPLY_TYPE'
			and lookup_code = l_result;

Line: 280

	select nvl(sum(reservation_quantity), 0)
           into   v_req_quantity
           from   mtl_reservations
           where  demand_source_type_id = decode (l_source_document_type_id,
                                                  10, inv_reservation_global.g_source_type_internal_ord,
         		   		          inv_reservation_global.g_source_type_oe )
           and    demand_source_line_id = l_demand_line_id
           and    supply_source_type_id = inv_reservation_global.g_source_type_req;

Line: 292

			select meaning
			into x_result
			from mfg_lookups
			where lookup_type = 'CTO_WB_SUPPLY_TYPE'
			and lookup_code = l_result;

Line: 305

	select nvl(sum(reservation_quantity), 0)
           into   v_ireq_quantity
           from   mtl_reservations
           where  demand_source_type_id = decode (l_source_document_type_id,
                                                  10, inv_reservation_global.g_source_type_internal_ord,
         		   		          inv_reservation_global.g_source_type_oe )
           and    demand_source_line_id = l_demand_line_id
           and    supply_source_type_id = inv_reservation_global.g_source_type_internal_req;

Line: 317

			select meaning
			into x_result
			from mfg_lookups
			where lookup_type = 'CTO_WB_SUPPLY_TYPE'
			and lookup_code = l_result;

Line: 330

	select nvl(sum(reservation_quantity), 0)
           into   v_asn_quantity
           from   mtl_reservations
           where  demand_source_type_id = decode (l_source_document_type_id,
                                                  10, inv_reservation_global.g_source_type_internal_ord,
         		   		          inv_reservation_global.g_source_type_oe )
           and    demand_source_line_id = l_demand_line_id
           and    supply_source_type_id = inv_reservation_global.g_source_type_asn;

Line: 342

			select meaning
			into x_result
			from mfg_lookups
			where lookup_type = 'CTO_WB_SUPPLY_TYPE'
			and lookup_code = l_result;

Line: 354

	select meaning
	into x_result
	from mfg_lookups
	where lookup_type = 'CTO_WB_SUPPLY_TYPE'
	and lookup_code = l_result;

Line: 382

          select line_id into   l_config_line_id
          from   oe_order_lines_all
          where  ato_line_id = p_ato_line_id
          and    item_type_code = 'CONFIG';

Line: 414

      select concatenated_segments
      into   l_item_name
      from   mtl_system_items_kfv
      where  inventory_item_id = p_config_item
      and    rownum =1;

Line: 422

      select concatenated_segments
      into  l_item_name
      from  mtl_system_items_kfv mtl,
            oe_order_lines_all oel
      where oel.ato_line_id = p_ato_line_id
      and   oel.item_type_code = 'CONFIG'
      and   oel.inventory_item_id = mtl.inventory_item_id
      and   mtl.organization_id = p_ship_org_id;

Line: 450

      select description
      into   l_item_desc
      from   mtl_system_items_kfv
      where  inventory_item_id = p_config_item
      and    rownum = 1;

Line: 458

      select description
      into   l_item_desc
      from   mtl_system_items_kfv mtl,
             oe_order_lines_all oel
      where  mtl.inventory_item_id = oel.inventory_item_id
      and    mtl.organization_id   = p_ship_org_id
      and    oel.ato_line_id = p_ato_line_id
      and    oel.item_type_code = 'CONFIG';

Line: 478

	  select h.source_document_type_id
	  into   l_source_document_type_id
	  from   oe_order_headers_all h, oe_order_lines_all l
	  where  h.header_id =  l.header_id
	  and    l.line_id = pLineId
	  and    rownum = 1;

Line: 533

      	select full_name
      	into   l_buyer_name
      	from   per_people_f
      	where  person_id = P_suggested_buyer_id;

Line: 618

            select line_id
	    into   l_line_id
	    from   oe_order_lines_all
	    where  ato_line_id = p_ato_line_id
	    and    item_type_code = 'CONFIG';

Line: 629

      select sum(primary_reservation_quantity),primary_uom_code
      into   l_prim_rsv_qty, l_prim_uom_code
      from   mtl_reservations
      where  demand_source_line_id  = l_line_id
      and    supply_source_TYpe_id  = 13
      group by primary_uom_code;

Line: 637

      select CTO_WORKBENCH_UTIL_PK.convert_uom(l_prim_uom_code,
                                               oel.order_quantity_uom,
                                               nvl(l_prim_rsv_qty,0),
                                               oel.inventory_item_id)
      into l_rsv_qty
      from oe_order_lines_all oel
      where oel.line_id = l_line_id;

Line: 691

Select max(exp_comp_date) into l_date
from
   (SELECT wdj.scheduled_completion_date exp_comp_date
    FROM mtl_reservations mr, wip_discrete_jobs wdj
    WHERE mr.demand_source_type_id = decode(CTO_WORKBENCH_UTIL_PK.get_source_document_id(l_config_line_id), 10,8,2)
    AND mr.demand_source_line_id = l_config_line_id
    AND mr.supply_source_type_id = 5
    AND wdj.wip_entity_id = mr.supply_source_header_id
    AND wdj.organization_id = mr.organization_id

    UNION

    SELECT wfs.scheduled_completion_date exp_comp_date
    FROM wip_flow_schedules wfs
    WHERE wfs.demand_source_line = to_char(l_config_line_id)
    AND wfs.status = 1

    UNION

    select nvl(poll.promised_date,poll.need_by_date) exp_comp_date
    from mtl_reservations mr, po_line_locations_all poll
    where mr.demand_source_type_id =  2
    and mr.demand_source_line_id = l_config_line_id
    and mr.supply_source_type_id =  1
    and mr.supply_source_header_id = poll.po_header_id
    and mr.supply_source_line_id = poll.line_location_id

    UNION

    select porl.need_by_date exp_comp_date
    from mtl_reservations mr, po_requisition_lines_all porl
    where mr.demand_source_type_id = 2
    and mr.demand_source_line_id = l_config_line_id
    and mr.supply_source_type_id in (7, 17)
    and mr.supply_source_header_id = porl.requisition_header_id
    and mr.supply_source_line_id = porl.requisition_line_id

    UNION

    select nvl(poll.promised_date,poll.need_by_date) exp_comp_date
    from oe_drop_ship_sources ods, po_line_locations_all poll
    where ods.line_id = l_config_line_id
    and ods.po_header_id = poll.po_header_id
    and ods.line_location_id = poll.line_location_id

    UNION

    select porl.need_by_date exp_comp_date
    from oe_drop_ship_sources ods, po_requisition_lines_all porl
    where ods.line_id = l_config_line_id
    and ods.po_header_id is null
    and ods.requisition_header_id = porl.requisition_header_id
    and ods.requisition_line_id = porl.requisition_line_id

    UNION

    select asn_headers.expected_receipt_date exp_comp_date
    from mtl_reservations mr,
         rcv_shipment_lines ASN_LINES,
         rcv_shipment_headers asn_headers
    where mr.demand_source_type_id = 2
    and mr.demand_source_line_id = l_config_line_id
    and mr.supply_source_type_id = 25
    and mr.supply_source_line_detail = ASN_LINES.shipment_line_id
    and asn_headers.shipment_header_id = asn_lines.shipment_header_id
    and ASN_LINES.asn_line_flag = 'Y');

Line: 799

            select line_id
	    into   l_line_id
	    from   oe_order_lines_all
	    where  ato_line_id = p_ato_line_id
	    and    item_type_code = 'CONFIG';

Line: 810

      select sum(primary_reservation_quantity),primary_uom_code
      into   l_prim_rsv_qty, l_prim_uom_code
      from   mtl_reservations
      where  demand_source_line_id  = l_line_id
      and    supply_source_TYpe_id  = 27
      group by primary_uom_code;

Line: 818

      select CTO_WORKBENCH_UTIL_PK.convert_uom(l_prim_uom_code,
                                               oel.order_quantity_uom,
                                               nvl(l_prim_rsv_qty,0),
                                               oel.inventory_item_id)
      into l_rsv_qty
      from oe_order_lines_all oel
      where oel.line_id = l_line_id;

Line: 866

      select line_id into x_config_line_id
      from   oe_order_lines_all
      where  ato_line_id = p_ato_line_id
      and    item_type_code = 'CONFIG';