APPS.HRI_OPL_PER_ORGCC SQL Statements

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

Line: 120

PROCEDURE recover_insert_rows(p_stored_rows_to_insert NUMBER) IS

BEGIN
  --
  -- loop through rows still to insert one at a time
  --
  FOR i IN 1..p_stored_rows_to_insert LOOP
    --
    -- Trap unique constraint errors
    --
    BEGIN
      --
      -- @@ Code specific to this view/table below
      -- @@ INTRUCTION TO DEVELOPER:
      -- @@ 1/ For each column in your view put a column in the insert
      -- @@ statement below.
      -- @@ 2/ Prefix each column in the VALUE clause with g_
      -- @@ 3/ make sure (i) is at the end of each column in the value clause
      --
      INSERT INTO hri_cs_per_orgcc_ct
        (organization_id
        ,cost_centre_code
        ,cc_mngr_person_id
        ,effective_start_date
        ,effective_end_date
        ,company_code
        ,reporting_name
        ,last_change_date)
      VALUES
        (g_organization_id(i)
        ,g_cost_centre_code(i)
        ,g_cc_mngr_person_id(i)
        ,g_effective_start_date(i)
        ,g_effective_end_date(i)
        ,g_company_code(i)
        ,g_reporting_name(i)
        ,g_last_change_date(i));

Line: 166

        output('Single insert error: ' || to_char(g_organization_id(i)) ||
               ' - ' || to_char(g_cc_mngr_person_id(i)));

Line: 179

END recover_insert_rows;

Line: 185

PROCEDURE bulk_insert_rows(p_stored_rows_to_insert NUMBER) IS
  --
BEGIN
  --
  -- insert chunk of rows
  --
  -- @@ Code specific to this view/table below
  -- @@ INTRUCTION TO DEVELOPER:
  -- @@ 1/ For each column in your view put a column in the insert statement
  --       below.
  -- @@ 2/ Prefix each column in the VALUE clause with g_
  -- @@ 3/ make sure (i) is at the end of each column in the value clause
  --
  FORALL i IN 1..p_stored_rows_to_insert
      INSERT INTO hri_cs_per_orgcc_ct
        (organization_id
        ,cost_centre_code
        ,cc_mngr_person_id
        ,effective_start_date
        ,effective_end_date
        ,company_code
        ,reporting_name
        ,last_change_date)
      VALUES
        (g_organization_id(i)
        ,g_cost_centre_code(i)
        ,g_cc_mngr_person_id(i)
        ,g_effective_start_date(i)
        ,g_effective_end_date(i)
        ,g_company_code(i)
        ,g_reporting_name(i)
        ,g_last_change_date(i));

Line: 232

    recover_insert_rows(p_stored_rows_to_insert);

Line: 235

END bulk_insert_rows;

Line: 241

PROCEDURE Incremental_Update IS
  --
BEGIN
  --
  -- @@ Code specific to this view/table below
  -- @@ INTRUCTION TO DEVELOPER:
  -- @@ 1/ Change the code below to reflect the columns in your view / table
  -- @@ 2/ Change the FROM, INSERT, DELETE statements to point at the relevant
  -- @@    source view / table
  --
  -- Insert completly new rows
  --
  -- log('Doing insert.');

Line: 254

  INSERT INTO hri_cs_per_orgcc_ct
  (organization_id
  ,cost_centre_code
  ,cc_mngr_person_id
  ,effective_start_date
  ,effective_end_date
  ,company_code
  ,reporting_name
  ,last_change_date)
  SELECT
   organization_id
  ,cost_centre_code
  ,cc_mngr_person_id
  ,effective_start_date
  ,effective_end_date
  ,company_code
  ,reporting_name
  ,last_change_date
  FROM hri_cs_per_orgcc_v svw
  --
  -- 4303724, Used TRUNC function
  --
  WHERE TRUNC(last_change_date) BETWEEN g_start_date
                                AND     g_end_date
  AND NOT EXISTS (SELECT 'x'
                  FROM   hri_cs_per_orgcc_ct tbl
                  WHERE  svw.organization_id    = tbl.organization_id
                  AND    svw.effective_start_date   = tbl.effective_start_date
                  AND    svw.effective_end_date     = tbl.effective_end_date);

Line: 289

  UPDATE hri_cs_per_orgcc_ct tbl
    SET (organization_id
        ,cost_centre_code
        ,cc_mngr_person_id
        ,effective_start_date
        ,effective_end_date
        ,company_code
        ,reporting_name
        ,last_change_date) =
          (SELECT svw.organization_id
                 ,svw.cost_centre_code
                 ,svw.cc_mngr_person_id
                 ,svw.effective_start_date
                 ,svw.effective_end_date
                 ,svw.company_code
                 ,svw.reporting_name
                 ,svw.last_change_date
           FROM hri_cs_per_orgcc_v     svw
	   --
	   -- 4303724, Used TRUNC function
	   --
           WHERE TRUNC(svw.last_change_date) BETWEEN g_start_date
                                             AND     g_end_date
           AND   svw.organization_id        = tbl.organization_id
           AND   svw.effective_start_date   = tbl.effective_start_date
           AND   svw.effective_end_date     = tbl.effective_end_date
           )
    WHERE (tbl.organization_id,
           tbl.effective_start_date,
           tbl.effective_end_date)
          IN
          (SELECT svw.organization_id,
                  svw.effective_start_date,
                  svw.effective_end_date
           FROM   hri_cs_per_orgcc_v     svw
	   --
	   -- 4303724, Used TRUNC function
	   --
           WHERE  TRUNC(svw.last_change_date) BETWEEN g_start_date
                                              AND     g_end_date);

Line: 335

  DELETE
  FROM hri_cs_per_orgcc_ct tbl
  WHERE NOT EXISTS (SELECT 'x'
                    FROM  hri_cs_per_orgcc_v svw
                    WHERE svw.organization_id      = tbl.organization_id
                    AND   svw.effective_start_date = tbl.effective_start_date
                    AND   svw.effective_end_date   = tbl.effective_end_date);

Line: 353

    Output('Failure in incremental update process.');

Line: 375

  SELECT
     organization_id
    ,cost_centre_code
    ,cc_mngr_person_id
    ,effective_start_date
    ,effective_end_date
    ,company_code
    ,reporting_name
    ,last_change_date
  FROM hri_cs_per_orgcc_v svw;

Line: 467

    bulk_insert_rows (l_rows_fetched);

Line: 545

    Incremental_Update;

Line: 563

  SELECT 'x'
  FROM   hri_cs_per_orgcc_ct;