APPS.PJM_UNIT_EFF SQL Statements

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

Line: 55

   SELECT serial_number_control_code
   INTO   L_ser_control
   FROM   mtl_system_items
   WHERE  inventory_item_id = X_item_id
   AND    organization_id = X_organization_id;

Line: 141

   SELECT nvl(allow_cross_unitnum_issues,'N')
   INTO   L_allowed
   FROM   pjm_org_parameters
   WHERE  organization_id = L_org_id;

Line: 207

      select nvl(eam_item_type, -2)
      into l_item
      from mtl_system_items_b
      where inventory_item_id =X_ITEM_ID
      and organization_id = X_organization_id;

Line: 217

         SELECT decode(effectivity_control , 2 , 'Y' , 'N')
         INTO   G_Unit_Eff_Item
         FROM   mtl_system_items
         WHERE  inventory_item_id = X_item_id
         AND    organization_id = X_organization_id;

Line: 298

  SELECT N2.unit_number
  FROM   pjm_unit_numbers N1
  ,      pjm_unit_numbers N2
  WHERE  N1.unit_number = X_unit_number
  AND    N2.end_item_id = N1.end_item_id
  AND    N2.master_organization_id = N1.master_organization_id
  AND    N2.unit_number < N1.unit_number
  ORDER BY N2.unit_number desc;

Line: 337

  SELECT N2.unit_number
  FROM   pjm_unit_numbers N1
  ,      pjm_unit_numbers N2
  WHERE  N1.unit_number = X_unit_number
  AND    N2.end_item_id = N1.end_item_id
  AND    N2.master_organization_id = N1.master_organization_id
  AND    N2.unit_number > N1.unit_number
  ORDER BY N2.unit_number asc;

Line: 380

   SELECT DECODE(e.entity_type, 1, dj.end_item_unit_number,
                                4, fs.end_item_unit_number)
   INTO   G_WIP_Unit_Num
   FROM   wip_flow_schedules fs
   ,      wip_discrete_jobs dj
   ,      wip_entities e
   WHERE  e.wip_entity_id = X_wip_entity_id
   AND    e.organization_id = X_organization_id
   AND    fs.organization_id (+) = e.organization_id
   AND    fs.wip_entity_id (+) = e.wip_entity_id
   AND    dj.organization_id (+) = e.organization_id
   AND    dj.wip_entity_id (+) = e.wip_entity_id;

Line: 416

      SELECT DECODE(e.entity_type, 1, dj.end_item_unit_number,
                                   4, fs.end_item_unit_number)
      INTO   G_WIP_Unit_Num
      FROM   wip_flow_schedules fs
      ,      wip_discrete_jobs dj
      ,      wip_entities e
      WHERE  e.wip_entity_id = X_wip_entity_id
      AND    e.organization_id = X_organization_id
      AND    fs.organization_id (+) = e.organization_id
      AND    fs.wip_entity_id (+) = e.wip_entity_id
      AND    dj.organization_id (+) = e.organization_id
      AND    dj.wip_entity_id (+) = e.wip_entity_id;

Line: 470

      SELECT po_distribution_id
      ,      requisition_line_id
      INTO   L_po_distribution_id
      ,      L_req_line_id
      FROM   rcv_transactions
      WHERE  transaction_id = X_rcv_transaction_id;

Line: 479

         SELECT end_item_unit_number
         INTO   G_RCV_Unit_Num
         FROM   po_distributions_all
         WHERE  po_distribution_id = L_po_distribution_id;

Line: 486

         SELECT end_item_unit_number
         INTO   G_RCV_Unit_Num
         FROM   po_req_distributions_all
         WHERE  requisition_line_id = L_req_line_id;

Line: 530

   SELECT end_item_unit_number
   INTO   G_OE_Unit_Num
   FROM   oe_order_lines_all
   WHERE  line_id = X_so_line_id;

Line: 555

      SELECT end_item_unit_number
      INTO   G_OE_Unit_Num
      FROM   oe_order_lines_all
      WHERE  line_id = X_so_line_id;

Line: 596

      SELECT oe_order_line_id
      INTO   L_oe_order_line_id
      FROM   rcv_transactions
      WHERE  transaction_id = X_rcv_transaction_id;

Line: 783

   SELECT end_item_unit_number
   INTO   L_serial_unitnum
   FROM   mtl_serial_numbers
   WHERE  serial_number = X_serial_number
   AND    inventory_item_id = X_item_id;

Line: 886

   SELECT organization_id
   ,      inventory_item_id
   ,      transaction_source_type_id
   ,      transaction_action_id
   ,      transaction_source_id
   ,      rcv_transaction_id
   ,      SIGN(primary_quantity)
   INTO   L_organization_id
   ,      L_item_id
   ,      L_src_type_id
   ,      L_trx_action_id
   ,      L_trx_src_id
   ,      L_rcv_trx_id
   ,      L_direction
   FROM   mtl_material_transactions
   WHERE  transaction_id = X_transaction_id;

Line: 948

   UPDATE mtl_serial_numbers
   SET    end_item_unit_number =
     DECODE(L_direction, 1, L_unit_number, NULL)
   WHERE  (inventory_item_id, serial_number) in (
     SELECT inventory_item_id, serial_number
     FROM   mtl_unit_transactions
     WHERE  transaction_id = X_transaction_id
    UNION ALL   /*Bug 7207502 (FP of 6391634): Added union part to select serial when assembly is lot serial controlled.*/
     SELECT mut.inventory_item_id, mut.serial_number
     FROM   mtl_transaction_lot_numbers mtln,   mtl_unit_transactions mut
     WHERE  mtln.transaction_id = X_transaction_id
     AND   mtln.serial_transaction_id =   mut.transaction_id
   );

Line: 1005

   INSERT INTO pjm_unit_serial_history
   ( serial_number
   , inventory_item_id
   , organization_id
   , old_unit_number
   , new_unit_number
   , creation_date
   , created_by
   , last_update_date
   , last_updated_by
   , last_update_login )
   SELECT X_serial_number
   ,      X_item_id
   ,      X_organization_id
   ,      X_old_unit_number
   ,      X_new_unit_number
   ,      sysdate
   ,      L_user_id
   ,      sysdate
   ,      L_user_id
   ,      L_login_id
   FROM   dual;