APPS.EAM_MATERIALISSUE_PVT SQL Statements

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

Line: 122

  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: 143

  select primary_uom_code,
    serial_number_control_code ,
    lot_control_code,
    concatenated_segments
  into
    l_primary_uom_code ,
    l_serial_number_control_code,
    l_lot_control_code,
    l_material
  from mtl_system_items_b_kfv
  where inventory_item_id=p_inventory_item_id
  and organization_id=p_organization_id;

Line: 225

    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: 261

savepoint before_insert_mti;

Line: 311

   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: 320

    rollback to before_insert_mti;

Line: 334

      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: 343

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

Line: 345

    update mtl_transactions_interface
    set rebuild_item_id=l_rebuild_item_id
    where transaction_interface_id=  x_tmp_id;

Line: 354

  update mtl_transactions_interface
  set rebuild_serial_number=p_rebuild_serial_number
  where transaction_interface_id=  x_tmp_id;

Line: 364

      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: 371

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

Line: 373

    update mtl_transactions_interface
    set rebuild_activity_id=l_rebuild_activity_id
    where transaction_interface_id=  x_tmp_id;

Line: 380

    'inserting rebuild job');

Line: 385

   SELECT
     wip_job_number_s.nextval INTO l_rebuild_job_temp
   FROM
     DUAL;

Line: 398

   update mtl_transactions_interface
   set rebuild_job_name=l_rebuild_job_name
   where transaction_interface_id=  x_tmp_id;

Line: 404

  update mtl_transactions_interface
  set transaction_reference=p_reference
  where transaction_interface_id=  x_tmp_id;

Line: 413

  update mtl_transactions_interface
  set reason_id=l_reason_id
  where transaction_interface_id=  x_tmp_id;

Line: 419

  select transaction_header_id into l_tx_hdr_id
  from mtl_transactions_interface
  where transaction_interface_id = x_tmp_id;

Line: 447

		  rollback to before_insert_mti;

Line: 456

		   rollback to before_insert_mti;

Line: 559

     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: 574

     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: 580

  select mtl_material_transactions_s.nextval into l_header_id from dual;

Line: 581

  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: 649

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: 680

END insert_ser_trx;

Line: 683

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: 697

update mtl_material_transactions_temp
set transaction_reference=p_reference
where transaction_temp_id=p_transaction_temp_id;

Line: 703

update mtl_material_transactions_temp
set reason_id=l_reason_id
where transaction_temp_id=p_transaction_temp_id;

Line: 708

 END INSERT_REASON_REF_INTO_MMTT;

Line: 730

   SELECT
     wip_job_number_s.nextval INTO l_rebuild_job_temp
   FROM
     DUAL;

Line: 745

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: 754

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: 761

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: 768

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: 983

  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: 1001

  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: 1216

  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: 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: 1397

       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;