APPS.PA_ADVERTISEMENTS_PUB SQL Statements

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

Line: 72

  l_undeleted_action_code_tbl varchar_tbl;

Line: 81

  SELECT pa.start_date,
         ast.status_code,
         ast.actual_start_date
  FROM   pa_project_assignments pa,
         pa_action_sets ast
  WHERE  pa.assignment_id = ast.object_id
  AND    ast.action_set_id = p_action_set_id;

Line: 91

  SELECT action_code
  FROM pa_action_set_lines
  WHERE action_set_id = p_action_set_id
    AND (status_code = 'PENDING'
     OR status_code = 'UPDATE_PENDING'
     OR status_code = 'COMPLETE');

Line: 99

  CURSOR get_undeleted_action_lines IS
  SELECT action_code
  FROM pa_action_set_lines
  WHERE action_set_id = p_action_set_id
    AND line_deleted_flag = 'N';

Line: 107

  SELECT 'T'
  FROM pa_action_set_lines
  WHERE action_set_id = p_action_set_id
    AND action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
    AND (status_code = 'PENDING'
         OR status_code = 'UPDATE_PENDING'
         OR status_code = 'COMPLETE')
    AND rownum=1;

Line: 120

  SELECT action_set_line_id
  FROM pa_action_set_lines
  WHERE action_set_id = p_action_set_id
    AND rownum = 1
    AND action_code = 'ADVERTISEMENT_PUB_TO_START_ORG'
    AND (status_code = 'PENDING'
     OR status_code = 'UPDATE_PENDING'
     OR status_code = 'COMPLETE')
    AND action_set_line_number < (
       SELECT MIN(action_set_line_number)
       FROM pa_action_set_lines
       WHERE action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
         AND action_set_id = p_action_set_id
         AND (status_code = 'PENDING'
          OR status_code = 'UPDATE_PENDING'
          OR status_code = 'COMPLETE'));

Line: 140

  SELECT action_set_line_id
  FROM pa_action_set_lines
  WHERE action_set_id = p_action_set_id
    AND rownum = 1
    AND action_code = 'ADVERTISEMENT_PUB_TO_START_ORG'
    AND action_set_line_number < (
       SELECT MIN(action_set_line_number)
       FROM pa_action_set_lines
       WHERE action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
         AND action_set_id = p_action_set_id);

Line: 201

  OPEN get_undeleted_action_lines;

Line: 202

   FETCH get_undeleted_action_lines BULK COLLECT INTO l_undeleted_action_code_tbl;

Line: 203

  CLOSE get_undeleted_action_lines;

Line: 207

  IF l_undeleted_action_code_tbl.COUNT=0 THEN

    PA_UTILS.Add_Message ( p_app_short_name => 'PA'
                          ,p_msg_name => 'PA_NO_ACTION_LINE');

Line: 264

        SELECT aslc.condition_code, aslc.condition_attribute1, aslc.condition_attribute2
        BULK COLLECT INTO l_condition_code_tbl, l_condition_attribute1_tbl, l_condition_attribute2_tbl
        FROM pa_action_set_lines asl,
             pa_action_set_line_cond aslc
        WHERE asl.action_set_id = p_action_set_id
          AND asl.action_set_line_id = aslc.action_set_line_id
          AND (asl.action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
           OR asl.action_code = 'ADVERTISEMENT_PUB_TO_START_ORG')
          AND (asl.status_code = 'PENDING'
           OR asl.status_code = 'UPDATE_PENDING'
           OR asl.status_code = 'COMPLETE')
        ORDER BY asl.action_set_line_number;

Line: 356

, x_action_line_audit_tbl          OUT NOCOPY pa_action_set_utils.insert_audit_lines_tbl_type -- For 1159 mandate changes bug#2674619
, x_action_line_result_code        OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS

  TYPE varchar_tbl                    IS TABLE OF VARCHAR2(30)
   INDEX BY BINARY_INTEGER;

Line: 378

  SELECT pa.assignment_id,
         pa.project_id,
         pa.start_date,
         pa.record_version_number
  FROM   pa_project_assignments pa,
         pa_action_sets ast
  WHERE  pa.assignment_id = ast.object_id
  AND    ast.action_set_id = p_action_set_line_rec.action_set_id;

Line: 399

  g_action_line_audit_tbl.DELETE;

Line: 414

  IF p_action_line_conditions_tbl(p_action_line_conditions_tbl.COUNT).condition_date <= SYSDATE OR p_action_set_line_rec.status_code = 'UPDATE_PENDING' THEN

      -- CASE 1: Publish to All
      IF p_action_set_line_rec.action_code = 'ADVERTISEMENT_PUB_TO_ALL' THEN

        PA_ADVERTISEMENTS_PVT.Publish_To_all(
            p_action_set_line_id   => p_action_set_line_rec.action_set_line_id
          , p_object_id            => l_object_id
          , p_action_code          => p_action_set_line_rec.action_code
          , p_action_status_code   => p_action_set_line_rec.status_code
          , x_return_status        => l_return_status
        );

Line: 512

      ELSIF p_action_set_line_rec.action_code = 'ADVERTISEMENT_UPDATE_SP' THEN

        PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority(
              p_action_set_line_id     => p_action_set_line_rec.action_set_line_id
            , p_object_id              => l_object_id
            , p_action_code            => p_action_set_line_rec.action_code
            , p_action_status_code     => p_action_set_line_rec.status_code
            , p_staffing_priority_code => p_action_set_line_rec.action_attribute1
            , p_record_version_number  => l_record_version_number
            , x_return_status          => l_return_status
        );

Line: 548

           p_action_set_line_rec.action_code = 'ADVERTISEMENT_UPDATE_SP' THEN
          x_action_line_result_code := pa_action_set_utils.G_REVERSED_CUSTOM_AUDIT;

Line: 559

      ELSE -- update pending

        IF p_action_set_line_rec.action_code = 'ADVERTISEMENT_REMOVE_ADV' OR
           p_action_set_line_rec.action_code = 'ADVERTISEMENT_UPDATE_SP' THEN
          x_action_line_result_code := pa_action_set_utils.G_UPDATED_CUSTOM_AUDIT;

Line: 568

          x_action_line_result_code := pa_action_set_utils.G_UPDATED_CUSTOM_AUDIT;

Line: 570

          x_action_line_result_code := pa_action_set_utils.G_UPDATED_DEFAULT_AUDIT;

Line: 636

  SELECT pa.project_id,
         pa.start_date,
         ast.status_code,
         ast.actual_start_date,
         ast.action_set_id,
         ast.action_set_template_flag
  FROM   pa_project_assignments pa,
         pa_action_sets ast
  WHERE  pa.assignment_id = p_object_id
  AND    ast.object_id = p_object_id
  AND    ast.object_type = p_object_type
  AND    ast.action_set_type_code = 'ADVERTISEMENT'
  AND    ast.status_code <> 'DELETED';

Line: 693

    SELECT asl.action_set_line_id, 'UPDATE_PENDING', aslc.condition_date, aslc.action_set_line_condition_id
    BULK COLLECT INTO l_action_set_line_id_tbl, l_action_status_code_tbl, l_condition_date_tbl, l_action_line_cond_id_tbl
    FROM pa_action_set_lines asl,
         pa_action_set_line_cond aslc
    WHERE asl.action_set_id = l_action_set_id
      AND asl.status_code = 'COMPLETE'
      AND asl.action_set_line_id = aslc.action_set_line_id
      AND aslc.condition_date > SYSDATE;

Line: 703

    PA_ACTION_SETS_PVT.Bulk_Update_Line_Status(
        p_action_set_line_id_tbl => l_action_set_line_id_tbl
       ,p_line_status_tbl        => l_action_status_code_tbl
       ,x_return_status          => l_return_status
    );

Line: 712

    PA_ACTION_SETS_PVT.Bulk_Update_Condition_Date(
        p_action_line_condition_id_tbl  => l_action_line_cond_id_tbl
       ,p_condition_date_tbl            => l_condition_date_tbl
       ,x_return_status                 => l_return_status
    );

Line: 722

    l_action_set_line_id_tbl.DELETE;

Line: 723

    l_action_status_code_tbl.DELETE;

Line: 724

    l_condition_date_tbl.DELETE;

Line: 725

    l_action_line_cond_id_tbl.DELETE;

Line: 733

    SELECT asl.action_set_line_id, 'PENDING', aslc.condition_date, aslc.action_set_line_condition_id
    BULK COLLECT INTO l_action_set_line_id_tbl, l_action_status_code_tbl, l_condition_date_tbl, l_action_line_cond_id_tbl
    FROM pa_action_set_lines asl,
         pa_action_set_line_cond aslc
    WHERE asl.action_set_id = l_action_set_id
      AND asl.status_code = 'REVERSED'
      AND nvl(asl.line_deleted_flag, 'N') = 'N'
      AND asl.action_set_line_id = aslc.action_set_line_id
      AND aslc.condition_date > SYSDATE;

Line: 743

    PA_ACTION_SETS_PVT.Bulk_Update_Line_Status(
        p_action_set_line_id_tbl => l_action_set_line_id_tbl
       ,p_line_status_tbl        => l_action_status_code_tbl
       ,x_return_status          => l_return_status
    );

Line: 752

    PA_ACTION_SETS_PVT.Bulk_Update_Condition_Date(
        p_action_line_condition_id_tbl  => l_action_line_cond_id_tbl
       ,p_condition_date_tbl            => l_condition_date_tbl
       ,x_return_status                 => l_return_status
    );

Line: 828

    SELECT proj.start_adv_action_set_flag
    INTO l_action_set_start_flag
    FROM pa_project_assignments asgn,
         pa_projects_all proj
    WHERE asgn.assignment_id = p_object_id
      AND asgn.project_id = proj.project_id;