APPS.EAM_MATERIALISSUE_PVT SQL Statements

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

Line: 105

 	        select Inventory_item_id into l_inventory_item_id_wl
 	        from mtl_system_items
 	        where SEGMENT1 = p_inventory_item
 	        and ORGANIZATION_ID = p_organization_id;

Line: 113

 	        SELECT  inventory_location_id into l_source_locator_wl
 	        FROM mtl_item_locations_kfv
 	        WHERE organization_id = p_organization_id
 	        AND subInventory_code = p_source_subinventory
 	        AND NVL(disable_date,TRUNC(sysdate)+1) > TRUNC(sysdate)
 	        AND concatenated_segments = p_locator_name;

Line: 124

 	        select Inventory_item_id into l_rebuild_item_id_wl
 	        from mtl_system_items
 	        where SEGMENT1 = p_rebuild_item_name
 	        and ORGANIZATION_ID = p_organization_id;

Line: 171

  select nvl(material_issue_by_mo,'Y'), project_id, task_id
    into l_material_issue_by_mo, l_project_id, l_task_id
    from wip_discrete_jobs where
    wip_entity_id=p_wip_entity_id
    and organization_id=p_organization_id;

Line: 192

  select primary_uom_code,
    serial_number_control_code ,
    lot_control_code,
    concatenated_segments,
    REVISION_QTY_CONTROL_CODE
  into
    l_primary_uom_code ,
    l_serial_number_control_code,
    l_lot_control_code,
    l_material,
    l_revision_qty_control_code
  from mtl_system_items_b_kfv
  where inventory_item_id= nvl(p_inventory_item_id,l_inventory_item_id_wl) --bug 8661513
  and organization_id=p_organization_id;

Line: 295

 	   select NEGATIVE_INV_RECEIPT_CODE
 	   into l_neg_inv_receipt_code
 	   from mtl_parameters
 	   where organization_id = p_organization_id;

Line: 320

 	         select
 	         'Y'
 	         into l_within_open_period
 	         FROM
 	         org_acct_periods
 	         WHERE
 	         organization_id=p_organization_id
 	         and period_close_date is null
 	         and p_date between
 	         period_start_date and schedule_close_date + 1 - (1/(24*3600));

Line: 434

    SELECT COUNT(serial_number)
    INTO l_num_valid_serials
    FROM mtl_serial_numbers msn
    WHERE msn.inventory_item_id = l_inventory_item_id
    and msn.current_organization_id = l_organization_id
    and msn.current_subinventory_code = l_source_subinventory
    and (msn.group_mark_id is null or msn.group_mark_id = -1)
    and (msn.revision is null or msn.revision = p_revision)
    and (msn.lot_number is null or msn.lot_number = l_lot_number)
    and msn.current_status=3
    AND msn.serial_number between l_fm_serial_number and l_to_serial_number
    AND LENGTH(msn.serial_number) = LENGTH(l_fm_serial_number);

Line: 470

savepoint before_insert_mti;

Line: 520

   eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_CANNOT_DELETE_RESOURCE',
                                  p_token1=>'EAM_RET_MAT_PROCESS_MESSAGE', p_value1=>x_error_msg);

Line: 529

    rollback to before_insert_mti;

Line: 543

      select msi.inventory_item_id into l_rebuild_item_id
      from mtl_system_items_b_kfv msi, mtl_parameters mp
      where concatenated_segments = p_rebuild_item_name
      and msi.organization_id = mp.organization_id
	  and mp.maint_organization_id = p_organization_id
	  and eam_item_type = 3 --3 for rebuild
	  and rownum = 1;

Line: 552

      'Inserting rebuild item id: '|| l_rebuild_item_id);

Line: 554

    update mtl_transactions_interface
    set rebuild_item_id=l_rebuild_item_id
    where transaction_interface_id=  x_tmp_id;

Line: 563

  update mtl_transactions_interface
  set rebuild_serial_number=p_rebuild_serial_number
  where transaction_interface_id=  x_tmp_id;

Line: 573

      select inventory_item_id into l_rebuild_activity_id
      from mtl_system_items_b_kfv
      where concatenated_segments = p_rebuild_activity_name
      and organization_id = p_organization_id
      and eam_item_type = 2; --2 for activity

Line: 580

      'Inserting rebuild activity id: '|| l_rebuild_activity_id);

Line: 582

    update mtl_transactions_interface
    set rebuild_activity_id=l_rebuild_activity_id
    where transaction_interface_id=  x_tmp_id;

Line: 589

    'inserting rebuild job');

Line: 594

   SELECT
     wip_job_number_s.nextval INTO l_rebuild_job_temp
   FROM
     DUAL;

Line: 607

   update mtl_transactions_interface
   set rebuild_job_name=l_rebuild_job_name
   where transaction_interface_id=  x_tmp_id;

Line: 613

  update mtl_transactions_interface
  set transaction_reference=p_reference
  where transaction_interface_id=  x_tmp_id;

Line: 622

  update mtl_transactions_interface
  set reason_id=l_reason_id
  where transaction_interface_id=  x_tmp_id;

Line: 628

  select transaction_header_id into l_tx_hdr_id
  from mtl_transactions_interface
  where transaction_interface_id = x_tmp_id;

Line: 658

           SELECT error_explanation into  x_error_mssg1
           FROM mtl_transactions_interface
           WHERE TRANSACTION_header_id =  l_tx_hdr_id  ;

Line: 666

		  rollback to before_insert_mti;

Line: 676

             SELECT error_explanation into  x_error_mssg1
             FROM mtl_transactions_interface
             WHERE TRANSACTION_header_id =  l_tx_hdr_id  ;

Line: 683

		   rollback to before_insert_mti;

Line: 786

     SELECT NVL(max(oap.acct_period_id), -1)
     INTO l_acct_period_id
     FROM org_acct_periods oap,
          mtl_material_transactions_temp mmtt
     WHERE mmtt.transaction_temp_id =  p_trx_tmp_id
     AND oap.organization_id = mmtt.organization_id
     AND oap.open_flag = 'Y'
     AND trunc(SYSDATE)
     BETWEEN trunc(oap.period_start_date) AND
             trunc(oap.schedule_close_date);

Line: 801

     UPDATE mtl_material_transactions_temp
     SET transaction_date = SYSDATE,
         acct_period_id =  l_acct_period_id
     where  transaction_temp_id =  p_trx_tmp_id;

Line: 807

  select mtl_material_transactions_s.nextval into l_header_id from dual;

Line: 808

  update mtl_material_transactions_temp
  set transaction_header_id = l_header_id,
  transaction_status = null, --Added since WIP is no longer doing this in 11.5.10
  primary_quantity = -1* primary_quantity,
  transaction_quantity = -1* transaction_quantity
  where transaction_temp_id = p_trx_tmp_id;

Line: 876

PROCEDURE insert_ser_trx(p_trx_tmp_id 		IN	VARCHAR2,
			 p_serial_trx_tmp_id 	IN 	NUMBER,
			 p_trx_header_id	IN	NUMBER,
			 p_user_id 		IN	NUMBER,
			 p_fm_ser_num 		IN	VARCHAR2,
			 p_to_ser_num		IN	VARCHAR2,
			 p_item_id		IN      NUMBER,
			 p_org_id		IN 	NUMBER,
			 x_err_code		OUT NOCOPY	NUMBER,
		 	 x_err_message  	OUT NOCOPY	VARCHAR2) IS
BEGIN

    x_err_code := inv_trx_util_pub.insert_ser_trx(
               p_trx_tmp_id     => p_serial_trx_tmp_id,
               p_user_id        => p_user_id,
               p_fm_ser_num     => p_fm_ser_num,
               p_to_ser_num     => p_to_ser_num,
               x_proc_msg       => x_err_message);

Line: 907

END insert_ser_trx;

Line: 910

PROCEDURE INSERT_REASON_REF_INTO_MMTT(l_reason_id  IN Number,
p_reference  IN varchar2,
p_transaction_temp_id  In Number)  IS

begin

if ((l_reason_id is not null) and (p_reference is not null) )  then
update mtl_material_transactions_temp
set reason_id=l_reason_id,
transaction_reference=p_reference
where transaction_temp_id=p_transaction_temp_id;

Line: 924

update mtl_material_transactions_temp
set transaction_reference=p_reference
where transaction_temp_id=p_transaction_temp_id;

Line: 930

update mtl_material_transactions_temp
set reason_id=l_reason_id
where transaction_temp_id=p_transaction_temp_id;

Line: 935

 END INSERT_REASON_REF_INTO_MMTT;

Line: 957

   SELECT
     wip_job_number_s.nextval INTO l_rebuild_job_temp
   FROM
     DUAL;

Line: 972

update mtl_material_transactions_temp
set rebuild_item_id=p_rebuild_item_id,
rebuild_job_name =l_rebuild_job_name,
rebuild_activity_id=p_rebuild_activity_id,
rebuild_serial_number=p_rebuild_serial_number
where transaction_temp_id=p_transaction_temp_id;

Line: 981

update mtl_material_transactions_temp
set rebuild_item_id=p_rebuild_item_id,
rebuild_job_name =l_rebuild_job_name,
rebuild_serial_number=p_rebuild_serial_number
where transaction_temp_id=p_transaction_temp_id;

Line: 988

update mtl_material_transactions_temp
set rebuild_item_id=p_rebuild_item_id,
rebuild_job_name =l_rebuild_job_name,
rebuild_activity_id=p_rebuild_activity_id
where transaction_temp_id=p_transaction_temp_id;

Line: 995

update mtl_material_transactions_temp
set rebuild_item_id=p_rebuild_item_id,
rebuild_job_name =l_rebuild_job_name
where transaction_temp_id=p_transaction_temp_id;

Line: 1210

  select required_quantity,
         quantity_issued,
         eam_material_allocqty_pkg.allocated_quantity(wip_entity_id,operation_seq_num,organization_id,inventory_item_id),
         auto_request_material
  into   l_required_quantity,
         l_quantity_issued,
         l_quantity_allocated,
         l_auto_request_material
  from wip_requirement_operations
  where inventory_item_id = l_inventory_item_id
  and organization_id = l_organization_id
  and wip_entity_id = l_wip_entity_id
  and operation_seq_num = l_operation_seq_num;

Line: 1228

  select wdj.project_id,
         wdj.task_id,
         wdj.status_type
  into   l_project_id,
         l_task_id,
         l_status_type
  from  wip_discrete_jobs wdj, wip_entities we
  where wdj.wip_entity_id = we.wip_entity_id
  and wdj.organization_id = we.organization_id
  and wdj.organization_id = l_organization_id
  and wdj.wip_entity_id = l_wip_entity_id;

Line: 1443

  select required_quantity,
         auto_request_material
  into   l_required_quantity,
         l_auto_request_material
  from wip_requirement_operations
  where inventory_item_id = l_inventory_item_id
  and organization_id = l_organization_id
  and wip_entity_id = l_wip_entity_id
  and operation_seq_num = l_operation_seq_num;

Line: 1455

  select wdj.project_id,
         wdj.task_id,
         wdj.status_type
  into   l_project_id,
         l_task_id,
         l_status_type
  from  wip_discrete_jobs wdj, wip_entities we
  where wdj.wip_entity_id = we.wip_entity_id
  and wdj.organization_id = we.organization_id
  and wdj.organization_id = l_organization_id
  and wdj.wip_entity_id = l_wip_entity_id;

Line: 1624

       select project_id, task_id into l_project_id, l_task_id
       from wip_discrete_jobs
       where wip_entity_id = p_wip_entity_id
       and organization_id=p_organization_id;