APPS.HRI_OLTP_DISC_TRAINING SQL Statements

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

Line: 20

  SELECT business_group_id
  FROM ota_events
  WHERE event_id = p_event_id;

Line: 25

  SELECT bg.currency_code, NVL(c.precision,2)
  FROM fnd_currencies c
  ,per_business_groups bg
  WHERE bg.currency_code = c.currency_code
  AND bg.business_group_id = g_business_group_id;

Line: 72

  SELECT
   evt.event_id
  ,evt.duration
  ,evt.duration_units
  ,evt.title
  ,tav.version_name
  FROM
   ota_activity_versions    tav
  ,ota_events               evt
  ,ota_program_memberships  pm
  WHERE	evt.activity_version_id = tav.activity_version_id
  AND evt.event_id = pm.event_id
  AND pm.program_event_id = p_program_event_id;

Line: 171

  SELECT
   nvl(budget_cost,0)	budget_cost
  ,budget_currency_code
  ,business_group_id
  FROM ota_events
  WHERE event_id = p_csr_event_id;

Line: 181

  SELECT program_event_id
  FROM ota_program_memberships
  WHERE event_id = p_event_id;

Line: 265

  SELECT
   sr.currency_code
  ,sr.cost_unit
  ,nvl(sr.cost,0) * nvl(rb.quantity,0) *
           nvl(trunc(rb.required_date_to - rb.required_date_FROM + 1),0) cost
  FROM
   ota_suppliable_resources  sr
  ,ota_resource_bookings     rb
  WHERE	rb.supplied_resource_id = sr.supplied_resource_id
  AND rb.event_id = p_csr_event_id
  AND rb.status = 'C';	-- Confirmed bookings only

Line: 280

  SELECT
   nvl(actual_cost,0)
  ,budget_currency_code
  ,business_group_id
  FROM ota_events
  WHERE event_id = p_event_id;

Line: 353

  SELECT
   price_basis
  ,nvl(stANDard_price,0)
  ,currency_code
  FROM ota_events
  WHERE event_id = p_event_id;

Line: 361

  SELECT NVL(SUM(NVL(tdb.number_of_places,1)),0)
  FROM
   ota_booking_status_types   bst
  ,ota_delegate_bookings      tdb
  WHERE tdb.booking_status_type_id = bst.booking_status_type_id
  AND bst.type = 'A'   -- Attended
  AND tdb.organization_id IS NOT NULL
  AND tdb.event_id = p_event_id;

Line: 421

  SELECT tdb.booking_id
  FROM ota_booking_status_types   bst
      ,ota_delegate_bookings    tdb
  WHERE	tdb.booking_status_type_id = bst.booking_status_type_id
  AND bst.type = 'A'     -- Attended
  AND tdb.customer_id is not null
  AND tdb.event_id = p_event_id;

Line: 432

  SELECT
   currency_code
  ,SUM(NVL(money_amount,0))	amount
  FROM	ota_finance_lines
  WHERE line_type = 'E'	   -- Enrollment
  AND cancelled_flag = 'N'
  AND booking_id = p_delegate_booking_id
  GROUP BY currency_code;

Line: 487

  SELECT
   price_basis
  ,nvl(stANDard_price,0)
  ,currency_code
  FROM ota_events
  WHERE event_id = p_event_id;

Line: 495

  SELECT  NVL(SUM(NVL(tdb.number_of_places,1)),0)
  FROM     ota_booking_status_types     bst
          ,ota_delegate_bookings        tdb
  WHERE tdb.booking_status_type_id = bst.booking_status_type_id
  AND bst.type = 'A'       -- AttENDed
  AND tdb.organization_id IS NOT NULL
  AND tdb.booking_id = p_booking_id
  AND tdb.event_id = p_event_id;

Line: 563

  SELECT tdb.booking_id
  FROM  ota_booking_status_types       bst
       ,ota_delegate_bookings          tdb
  WHERE tdb.booking_status_type_id = bst.booking_status_type_id
  AND bst.type = 'A'           -- Attended
  AND tdb.booking_id = p_booking_id
  AND tdb.organization_id IS NULL -- bug fix 1432057
  AND tdb.event_id = p_event_id;

Line: 575

  SELECT
   currency_code
  ,sum(nvl(money_amount,0))       amount
  FROM ota_finance_lines
  WHERE line_type = 'E'   -- Enrollment
  AND cancelled_flag = 'N'
  AND booking_id = p_delegate_booking_id
  GROUP BY currency_code;

Line: 634

  SELECT
   price_basis
  ,nvl(stANDard_price,0)
  ,currency_code
  FROM ota_events
  WHERE event_id = p_event_id;

Line: 642

  SELECT NVL(SUM(NVL(tdb.number_of_places,1)),0)
  FROM ota_booking_status_types       bst
      ,ota_delegate_bookings          tdb
  WHERE tdb.booking_status_type_id = bst.booking_status_type_id
  AND bst.type IN ('P','C')            -- Placed, Cancelled
  AND tdb.organization_id IS NOT NULL
  AND tdb.booking_id = p_booking_id
  AND tdb.event_id = p_event_id;

Line: 710

  SELECT tdb.booking_id
  FROM ota_booking_status_types       bst
      ,ota_delegate_bookings          tdb
  WHERE tdb.booking_status_type_id = bst.booking_status_type_id
  AND bst.type IN ('P','C')            -- Placed, Cancelled
  AND tdb.booking_id = p_booking_id
  AND tdb.organization_id IS NULL      -- bug fix 1432057
  AND tdb.event_id = p_event_id;

Line: 722

  SELECT
   currency_code
  ,SUM(NVL(money_amount,0))       amount
  FROM ota_finance_lines
  WHERE line_type = 'E'   -- Enrollment
  AND cancelled_flag = 'N'
  AND booking_id = p_delegate_booking_id
  GROUP BY currency_code;

Line: 779

  SELECT program_event_id
  FROM ota_program_memberships
  WHERE event_id = p_event_id;