APPS.WSMPGENE SQL Statements

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

Line: 17

      SELECT
	lpad(we.wip_entity_name,length(we.wip_entity_name)+(4*level_no)),
             we.wip_entity_id,
	     dj.start_quantity,
	     dj.organization_id
      FROM wip_discrete_jobs dj,
	   wip_entities we
      WHERE we.wip_entity_id = wip_ent_id
      AND we.wip_entity_id = dj.wip_entity_id;

Line: 61

    SELECT /*+ ORDERED */
	msi.concatenated_segments,
	   dj.organization_id
    FROM wip_discrete_jobs dj,
	mtl_system_items_kfv msi
    WHERE dj.wip_entity_id = wip_ent_id
    AND dj.primary_item_id = msi.inventory_item_id
    AND dj.organization_id = msi.organization_id;

Line: 91

    SELECT
	 transaction_source_id
    FROM mtl_material_transactions
    WHERE transaction_id = trans_id
    AND transaction_source_type_id = 5;

Line: 126

    SELECT
	max(transaction_id)
    FROM mtl_material_transactions
    WHERE source_line_id = trans_ref
    AND transaction_quantity < 0;

Line: 133

    SELECT
	MIN(transaction_id)
    FROM mtl_transaction_lot_numbers
    WHERE inventory_item_id = item_id
    AND lot_number = ltrim(lot_name)
    AND transaction_id > cur_trans_id
    AND transaction_quantity < 0;

Line: 142

    SELECT /*+ ORDERED */
	mmt.source_line_id,
	mmt.transaction_type_id,
	mmt.source_code
    FROM  mtl_material_transactions mmt
    WHERE  mmt.transaction_id = next_trans_id;

Line: 183

			 SELECT
			 wip_entity_id
			 INTO next_trans_id
			 FROM wsm_sm_resulting_lots
			 WHERE transaction_id = next_trans_ref;

Line: 216

    SELECT
	count(*)
    FROM mtl_transaction_lot_numbers mtln,
     	mtl_material_transactions mt
     WHERE mt.transaction_source_id = wip_ent_id
    AND mt.organization_id = org_id
    AND mt.transaction_action_id = 31
    AND mt.transaction_source_type_id = 5
    AND mtln.transaction_id = mt.transaction_id;

Line: 240

    SELECT
	count(*)
    FROM mtl_transaction_lot_numbers
    WHERE lot_number = lot_name
    AND inventory_item_id = item_id
    AND transaction_id > cur_trans_id
    AND transaction_quantity < 0;

Line: 264

    SELECT
	available_quantity
    FROM wsm_sm_starting_jobs
    WHERE wip_entity_id = wip_ent_id
    AND transaction_id = trans_id;

Line: 286

    SELECT
	quantity_completed
    FROM wip_discrete_jobs
    WHERE wip_entity_id = wip_ent_id;

Line: 309

**    SELECT
**	msi.segment1,
**      dj.organization_id
**    FROM wip_discrete_jobs dj,
**	 mtl_system_items msi
**    WHERE dj.wip_entity_id = wip_ent_id
**    AND dj.primary_item_id = msi.inventory_item_id
**    AND dj.organization_id = msi.organization_id;

Line: 322

    SELECT
	msi.concatenated_segments,
      dj.organization_id
    FROM wip_discrete_jobs dj,
	 mtl_system_items_kfv msi
    WHERE dj.wip_entity_id = wip_ent_id
    AND dj.primary_item_id = msi.inventory_item_id
    AND dj.organization_id = msi.organization_id;

Line: 373

      SELECT
	lpad(we.wip_entity_name,length(we.wip_entity_name)+(4*level_no)),
             we.wip_entity_id,
	     dj.start_quantity,
	     msi.concatenated_segments,
	     dj.organization_id
      FROM wip_entities we,
 	   wip_discrete_jobs dj,
	   mtl_system_items_kfv msi
      WHERE we.wip_entity_id = wip_ent_id
      AND we.wip_entity_id = dj.wip_entity_id
      AND dj.primary_item_id = msi.inventory_item_id
      AND dj.organization_id = msi.organization_id;

Line: 406

    SELECT
	starting.subinventory_code,
	   result.subinventory_code
    FROM wsm_sm_starting_lots starting,
	 wsm_sm_resulting_lots result
    WHERE starting.transaction_id = trans_id
    AND result.transaction_id = trans_id;

Line: 434

    SELECT
	transaction_source_id
    FROM mtl_material_transactions
    WHERE transaction_id = trans_id
    AND transaction_source_type_id = 5;

Line: 470

    SELECT
	MIN(mmt.transaction_id)
    FROM mtl_transaction_lot_numbers mtln,
	mtl_material_transactions mmt
    WHERE mtln.lot_number = lot_name
    AND mtln.transaction_id = mmt.transaction_id
    AND mmt.source_line_id = trans_ref;

Line: 478

    SELECT
	MAX(transaction_id)
    FROM mtl_transaction_lot_numbers
    WHERE lot_number = lot_name
    AND inventory_item_id = item_id
    AND transaction_id < cur_trans_id
    AND transaction_quantity > 0;

Line: 489

    SELECT mmt.source_line_id,
	   mmt.transaction_type_id,
           mmt.source_code,
	   mmt.transaction_source_id
    FROM  mtl_material_transactions mmt
    WHERE mmt.transaction_id = next_trans_id ;

Line: 527

	    THEN SELECT  transaction_type_name INTO txn_type
		 FROM mtl_transaction_types
                 WHERE transaction_type_id = 44 ;

Line: 563

**    SELECT 1
**    FROM sys.dual
**    WHERE exists (select 1
**                  from   mtl_transaction_lot_numbers mtln,
**                         mtl_material_transactions mt
**                  WHERE mtln.transaction_id = mt.transaction_id
**                  AND mt.transaction_source_id = wip_ent_id
**                  AND mt.organization_id = org_id
**                  AND mt.transaction_action_id = 1
**                  AND mt.transaction_source_type_id = 5);

Line: 586

    SELECT 1
    INTO no_trans
       FROM   mtl_transaction_lot_numbers mtln,
              mtl_material_transactions mt
       WHERE mtln.transaction_id = mt.transaction_id
       AND mt.transaction_source_id = wip_ent_id
       AND mt.organization_id = org_id
       AND mt.transaction_action_id = 1
       AND mt.transaction_source_type_id = 5;

Line: 623

       SELECT
	 max(rj.transaction_id)
	 FROM
	--bugfix 1796646, check status with error code '3', instead of with mfg_lookup
	   -- mfg_lookups lk,
	 wsm_split_merge_transactions tx,
	 wsm_sm_resulting_jobs rj
	 WHERE rj.wip_entity_id = wip_ent_id
	 AND rj.transaction_id < nvl(cur_trans_id,
				     rj.transaction_id + 1)
	 AND rj.transaction_id = tx.transaction_id
	 and tx.status <> 3;

Line: 664

**    SELECT 1
**    FROM   sys.dual
**    WHERE EXISTS (SELECT 1
**                  FROM   mtl_transaction_lot_numbers
**                  WHERE lot_number = lot_name
**                  AND inventory_item_id = item_id
**                  AND transaction_id < cur_trans_id
**                  AND transaction_quantity > 0);

Line: 684

    SELECT 1
    INTO no_of_trans
        FROM   mtl_transaction_lot_numbers
        WHERE lot_number = lot_name
        AND inventory_item_id = item_id
        AND transaction_id < nvl(cur_trans_id, transaction_id +1)     --bugfix1796646 added nvl.
        AND transaction_quantity > 0;

Line: 716

    SELECT
	start_quantity
    FROM wsm_sm_resulting_jobs
    WHERE wip_entity_id = wip_ent_id
    AND transaction_id = trans_id;

Line: 737

    SELECT
	start_quantity
    FROM wip_discrete_jobs
    WHERE wip_entity_id = wip_ent_id;

Line: 763

      SELECT ml.meaning,sm.transaction_type_id
      FROM mfg_lookups ml, wsm_lot_split_merges sm
      WHERE ml.lookup_type = 'WSM_INV_LOT_TXN_TYPE'
      AND ml.lookup_code  = sm.transaction_type_id
      AND sm.transaction_id = txn_id;

Line: 792

    SELECT
	from_org.organization_code, to_org.organization_code
    FROM mtl_parameters from_org,
	 mtl_parameters to_org
    WHERE from_org.organization_id = from_org_id
    AND to_org.organization_id = to_org_id;

Line: 814

      SELECT
	transaction_type_id
      FROM wsm_split_merge_transactions
      WHERE transaction_id = id;