APPS.PON_EVAL_TEAM_UTIL_PVT SQL Statements

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

Line: 7

PROCEDURE send_eval_team_update_notif(p_auction_header_id IN NUMBER,
                                      p_member_user_id    IN NUMBER);

Line: 19

  SELECT person_party_id
  INTO l_party_id
  FROM fnd_user
  WHERE user_id = p_user_id;

Line: 34

  DELETE FROM pon_mng_eval_team_members
  WHERE auction_header_id = p_auction_header_id;

Line: 37

  DELETE FROM pon_mng_eval_team_sections
  WHERE auction_header_id = p_auction_header_id;

Line: 41

  INSERT INTO pon_mng_eval_team_members
  (
      auction_header_id,
      team_id,
      user_id,
      status_code,
      created_by,
      creation_date,
      last_updated_by,
      last_update_date,
      last_update_login
  )
  SELECT auction_header_id,
         team_id,
         user_id,
         'C',
         fnd_global.user_id,
         SYSDATE,
         fnd_global.user_id,
         SYSDATE,
         fnd_global.login_id
  FROM pon_evaluation_team_members
  WHERE auction_header_id = p_auction_header_id;

Line: 65

  INSERT INTO pon_mng_eval_team_sections
  (
      auction_header_id,
      team_id,
      team_name,
      section_id,
      status_code,
      created_by,
      creation_date,
      last_updated_by,
      last_update_date,
      last_update_login
  )
  SELECT pets.auction_header_id,
         pets.team_id,
         pet.team_name,
         pets.section_id,
         'C',
         fnd_global.user_id,
         SYSDATE,
         fnd_global.user_id,
         SYSDATE,
         fnd_global.login_id
  FROM pon_evaluation_team_sections pets,
       pon_evaluation_teams pet
  WHERE pets.auction_header_id = p_auction_header_id
    AND pet.auction_header_id = p_auction_header_id
    AND pet.team_id = pets.team_id;

Line: 105

  USING (SELECT pbh.bid_number,
                pmets.section_id
         FROM pon_mng_eval_team_members pmetm,
              pon_mng_eval_team_sections pmets,
              pon_bid_headers pbh,
              fnd_user fu
         WHERE pmetm.auction_header_id = p_auction_header_id
           AND pmets.auction_header_id = p_auction_header_id
           AND pmetm.team_id = pmets.team_id
           AND (pmetm.user_id, pmets.section_id) NOT IN
               (SELECT petm.user_id,
                       pets.section_id
                FROM pon_evaluation_team_members petm,
                     pon_evaluation_team_sections pets
                WHERE petm.auction_header_id = p_auction_header_id
                  AND pets.auction_header_id = p_auction_header_id
                  AND petm.team_id = pets.team_id
               )
           AND pbh.auction_header_id = p_auction_header_id
           AND pbh.bid_status IN ('ACTIVE', 'DRAFT')
           AND pbh.evaluation_flag = 'Y'
           AND pbh.evaluator_id = fu.person_party_id
           AND fu.user_id = pmetm.user_id
        ) bs_deleted
  ON (pmebs.auction_header_id = p_auction_header_id AND
      pmebs.bid_number = bs_deleted.bid_number AND
      pmebs.section_id = bs_deleted.section_id
     )
  WHEN MATCHED THEN
    UPDATE SET status_code = 'X' WHERE status_code = 'A'
    DELETE WHERE status_code = 'X'
  WHEN NOT MATCHED THEN
    INSERT (auction_header_id,
            bid_number,
            section_id,
            status_code,
            created_by,
            creation_date,
            last_updated_by,
            last_update_date,
            last_update_login
           )
    VALUES (p_auction_header_id,
            bs_deleted.bid_number,
            bs_deleted.section_id,
            'D',
            fnd_global.user_id,
            SYSDATE,
            fnd_global.user_id,
            SYSDATE,
            fnd_global.login_id
           );

Line: 162

  USING (SELECT pbh.bid_number,
                pets.section_id
         FROM pon_evaluation_team_members petm,
              pon_evaluation_team_sections pets,
              pon_bid_headers pbh,
              fnd_user fu
         WHERE petm.auction_header_id = p_auction_header_id
           AND pets.auction_header_id = p_auction_header_id
           AND petm.team_id = pets.team_id
           AND (petm.user_id, pets.section_id) NOT IN
               (SELECT pmetm.user_id,
                       pmets.section_id
                FROM pon_mng_eval_team_members pmetm,
                     pon_mng_eval_team_sections pmets
                WHERE pmetm.auction_header_id = p_auction_header_id
                  AND pmets.auction_header_id = p_auction_header_id
                  AND pmetm.team_id = pmets.team_id
               )
           AND pbh.auction_header_id = p_auction_header_id
           AND bid_status IN ('ACTIVE', 'DRAFT')
           AND pbh.evaluation_flag = 'Y'
           AND pbh.evaluator_id = fu.person_party_id
           AND fu.user_id = petm.user_id
        ) bs_added
  ON (pmebs.auction_header_id = p_auction_header_id AND
      pmebs.bid_number = bs_added.bid_number AND
      pmebs.section_id = bs_added.section_id
     )
  WHEN MATCHED THEN
    UPDATE SET status_code = 'A' WHERE status_code = 'D'
  WHEN NOT MATCHED THEN
    INSERT (auction_header_id,
            bid_number,
            section_id,
            status_code,
            created_by,
            creation_date,
            last_updated_by,
            last_update_date,
            last_update_login
           )
    VALUES (p_auction_header_id,
            bs_added.bid_number,
            bs_added.section_id,
            'A',
            fnd_global.user_id,
            SYSDATE,
            fnd_global.user_id,
            SYSDATE,
            fnd_global.login_id
           );

Line: 217

  UPDATE pon_mng_eval_team_members
  SET status_code = 'D'
  WHERE auction_header_id = p_auction_header_id
    AND (team_id, user_id) NOT IN
        (SELECT team_id,
                user_id
         FROM pon_evaluation_team_members
         WHERE auction_header_id = p_auction_header_id
        );

Line: 227

  INSERT INTO pon_mng_eval_team_members
  (
      auction_header_id,
      team_id,
      user_id,
      status_code,
      created_by,
      creation_date,
      last_updated_by,
      last_update_date,
      last_update_login
  )
  SELECT auction_header_id,
         team_id,
         user_id,
         'A',
         fnd_global.user_id,
         SYSDATE,
         fnd_global.user_id,
         SYSDATE,
         fnd_global.login_id
  FROM pon_evaluation_team_members
  WHERE auction_header_id = p_auction_header_id
    AND (team_id, user_id) NOT IN
        (SELECT team_id,
                user_id
         FROM pon_mng_eval_team_members
         WHERE auction_header_id = p_auction_header_id
        );

Line: 260

  UPDATE pon_mng_eval_team_sections
  SET status_code = 'D'
  WHERE auction_header_id = p_auction_header_id
    AND (team_id, section_id) NOT IN
        (SELECT team_id,
                section_id
         FROM pon_evaluation_team_sections
         WHERE auction_header_id = p_auction_header_id
        );

Line: 270

  INSERT INTO pon_mng_eval_team_sections
  (
      auction_header_id,
      team_id,
      team_name,
      section_id,
      status_code,
      created_by,
      creation_date,
      last_updated_by,
      last_update_date,
      last_update_login
  )
  SELECT pets.auction_header_id,
         pets.team_id,
         pet.team_name,
         pets.section_id,
         'A',
         fnd_global.user_id,
         SYSDATE,
         fnd_global.user_id,
         SYSDATE,
         fnd_global.login_id
  FROM pon_evaluation_team_sections pets,
       pon_evaluation_teams pet
  WHERE pets.auction_header_id = p_auction_header_id
    AND pet.auction_header_id = p_auction_header_id
    AND pet.team_id = pets.team_id
    AND (pets.team_id, pets.section_id) NOT IN
        (SELECT team_id,
                section_id
         FROM pon_mng_eval_team_sections
         WHERE auction_header_id = p_auction_header_id
        );

Line: 308

  UPDATE pon_team_member_attr_scores
  SET score = NULL
  WHERE auction_header_id = p_auction_header_id
    AND (bid_number, section_id) IN
        (SELECT bid_number,
                section_id
         FROM pon_mng_eval_bid_sections
         WHERE auction_header_id = p_auction_header_id
           AND status_code = 'D'
        );

Line: 319

  UPDATE pon_bid_attribute_values
  SET score = NULL,
      weighted_score = NULL
  WHERE auction_header_id = p_auction_header_id
    AND auction_line_number = -1
    AND (bid_number, attr_group_seq_number) IN
        (SELECT pmebs.bid_number,
                pas.attr_group_seq_number
         FROM pon_mng_eval_bid_sections pmebs,
              pon_auction_sections pas
         WHERE pmebs.auction_header_id = p_auction_header_id
           AND pmebs.status_code = 'D'
           AND pas.auction_header_id = p_auction_header_id
           AND pas.section_id = pmebs.section_id
        );

Line: 348

    SELECT user_id
    FROM pon_neg_team_members
    WHERE auction_header_id = p_auction_header_id
      AND (user_id IN (SELECT pmetm.user_id
                       FROM pon_mng_eval_team_members pmetm,
                            pon_mng_eval_team_sections pmets
                       WHERE pmetm.auction_header_id = p_auction_header_id
                         AND pmets.auction_header_id = p_auction_header_id
                         AND pmets.team_id = pmetm.team_id
                         AND ((pmetm.status_code = 'A' AND
                               pmets.status_code IN ('A', 'C'))
                              OR
                              (pmetm.status_code = 'D' AND
                               pmets.status_code IN ('C', 'D'))
                              OR
                              (pmetm.status_code = 'C' AND
                               pmets.status_code IN ('A', 'D'))
                             )
                      )
           OR
           menu_name = 'PON_SOURCING_EDITNEG'
          );

Line: 382

    send_eval_team_update_notif(p_auction_header_id,
                                l_notify_members_tbl(i).user_id);

Line: 389

PROCEDURE send_eval_team_update_notif(p_auction_header_id IN NUMBER,
                                      p_member_user_id    IN NUMBER)
IS

  CURSOR c_auction_details
  IS
    SELECT pah.document_number,
           pah.auction_title,
           hz.party_name preparer_tp_name,
           pad.message_suffix
    FROM pon_auction_headers_all pah,
         pon_auc_doctypes pad,
         hz_parties hz
    WHERE pah.auction_header_id = p_auction_header_id
      AND pad.doctype_id = pah.doctype_id
      AND hz.party_id = pah.trading_partner_id;

Line: 413

  l_et_update_subject  VARCHAR2(2000);

Line: 433

  SELECT user_name
  INTO l_member_user_name
  FROM fnd_user
  WHERE user_id = p_member_user_id;

Line: 438

  fnd_message.set_name('PON', 'PON_SM_ET_UPDATE_SUB');

Line: 442

  l_et_update_subject := fnd_message.get;

Line: 450

  SELECT pon_auction_wf_s.nextval
  INTO l_sequence
  FROM dual;

Line: 458

                          process  => 'MNG_EVAL_TEAM_UPDATE');

Line: 503

                            aname    => 'MNG_EVAL_TEAM_UPDATE_SUBJECT',
                            avalue   => l_et_update_subject);

Line: 508

                            aname    => 'MNG_EVAL_TEAM_UPDATE_BODY',
                            avalue   => 'plsqlclob:' ||
                                        'pon_eval_team_util_pvt.' ||
                                        'gen_eval_team_update_body/' ||
                                        l_itemtype || ':' ||
                                        l_itemkey);

Line: 531

END send_eval_team_update_notif;

Line: 534

PROCEDURE gen_eval_team_update_body(p_document_id   IN VARCHAR2,
                                    p_display_type  IN VARCHAR2,
                                    x_document      IN OUT NOCOPY CLOB,
                                    x_document_type IN OUT NOCOPY VARCHAR2)
IS

  CURSOR c_changed_team_members(p_auction_header_id IN NUMBER,
                                p_member_user_id    IN NUMBER)
  IS
    SELECT pmetm.user_id,
           pmets.team_name,
           pas.section_name,
           pmetm.status_code
    FROM pon_mng_eval_team_members pmetm,
         pon_mng_eval_team_sections pmets,
         pon_auction_sections pas
    WHERE pmetm.auction_header_id = p_auction_header_id
      AND pmets.auction_header_id = p_auction_header_id
      AND pas.auction_header_id = p_auction_header_id
      AND pmets.team_id = pmetm.team_id
      AND pas.section_id = pmets.section_id
      AND ((pmetm.status_code = 'A' AND
            pmets.status_code IN ('A', 'C'))
           OR
           (pmetm.status_code = 'D' AND
            pmets.status_code IN ('C', 'D'))
          )
      AND pmetm.user_id = NVL(p_member_user_id, pmetm.user_id)
    ORDER BY pmetm.status_code,
             pmetm.user_id,
             pmets.team_name,
             pas.attr_group_seq_number;

Line: 570

    SELECT pmets.team_name,
           pas.section_name,
           pmets.status_code
    FROM pon_mng_eval_team_members pmetm,
         pon_mng_eval_team_sections pmets,
         pon_auction_sections pas
    WHERE pmetm.auction_header_id = p_auction_header_id
      AND pmets.auction_header_id = p_auction_header_id
      AND pas.auction_header_id = p_auction_header_id
      AND pmets.team_id = pmetm.team_id
      AND pas.section_id = pmets.section_id
      AND pmetm.status_code = 'C'
      AND pmets.status_code IN ('A', 'D')
      AND pmetm.user_id = p_member_user_id
    ORDER BY pmets.status_code,
             pmets.team_name,
             pas.attr_group_seq_number;

Line: 590

    SELECT pmets.team_name,
           pas.section_name,
           pmets.status_code
    FROM pon_mng_eval_team_sections pmets,
         pon_auction_sections pas
    WHERE pmets.auction_header_id = p_auction_header_id
      AND pas.auction_header_id = p_auction_header_id
      AND pas.section_id = pmets.section_id
      AND pmets.status_code IN ('A', 'D')
    ORDER BY pmets.status_code,
             pmets.team_name,
             pas.attr_group_seq_number;

Line: 606

    SELECT notification_id
    FROM wf_item_activity_statuses
    WHERE item_type = p_itemtype
      AND item_key = p_itemkey
      AND assigned_user IS NOT NULL;

Line: 615

    SELECT 'Y'
    FROM pon_neg_team_members
    WHERE auction_header_id = p_auction_header_id
      AND user_id = p_member_user_id
      AND menu_name = 'PON_SOURCING_EDITNEG';

Line: 896

END gen_eval_team_update_body;

Line: 899

PROCEDURE send_eval_update_scorer_notif(p_bid_number IN NUMBER)
IS

  CURSOR c_auction_details
  IS
    SELECT pah.document_number,
           pah.auction_header_id,
           pah.auction_title,
           hz.party_name preparer_tp_name,
           pad.message_suffix
    FROM pon_bid_headers pbh,
         pon_auction_headers_all pah,
         pon_auc_doctypes pad,
         hz_parties hz
    WHERE pbh.bid_number = p_bid_number
      AND pah.auction_header_id = pbh.auction_header_id
      AND pad.doctype_id = pah.doctype_id
      AND hz.party_id = pah.trading_partner_id;

Line: 920

    SELECT DISTINCT
           fus.user_name scorer_user_name,
           pon_locale_pkg.get_party_display_name(pbh.evaluator_id)
             AS evaluator_name
    FROM pon_bid_headers pbh,
         fnd_user fue,
         pon_evaluation_team_members petm,
         pon_evaluation_team_sections pets,
         pon_scoring_team_members pstm,
         pon_scoring_team_sections psts,
         fnd_user fus
    WHERE pbh.bid_number = p_bid_number
      AND pbh.evaluation_flag = 'Y'
      AND pbh.evaluator_id = fue.person_party_id
      AND petm.user_id = fue.user_id
      AND petm.auction_header_id = pbh.auction_header_id
      AND pets.auction_header_id = pbh.auction_header_id
      AND pets.team_id = petm.team_id
      AND fus.user_id = pstm.user_id
      AND pstm.auction_header_id = pbh.auction_header_id
      AND psts.auction_header_id = pbh.auction_header_id
      AND psts.team_id = pstm.team_id
      AND psts.section_id = pets.section_id
      AND psts.section_id IN
          (SELECT pas.section_id
           FROM pon_auction_sections pas,
                pon_auction_attributes paa
           WHERE pas.auction_header_id = pbh.auction_header_id
             AND paa.auction_header_id = pbh.auction_header_id
             AND paa.attr_group_seq_number = pas.attr_group_seq_number
             AND paa.line_number= -1
             AND paa.internal_attr_flag = 'Y'
             AND paa.scoring_method = 'MANUAL'
          );

Line: 966

  l_eval_update_sub    VARCHAR2(2000);

Line: 1002

    fnd_message.set_name('PON', 'PON_SM_EVAL_UPDATE_SCORER_SUB');

Line: 1006

    l_eval_update_sub := fnd_message.get;

Line: 1014

    SELECT pon_auction_wf_s.nextval
    INTO l_sequence
    FROM dual;

Line: 1022

                            process  => 'NOTIFY_SCORER_EVAL_UPDATE');

Line: 1066

                            aname    => 'EVAL_UPDATE_SCORER_SUBJECT',
                            avalue   => l_eval_update_sub);

Line: 1071

                            aname    => 'EVAL_UPDATE_SCORER_BODY',
                            avalue   => 'plsqlclob:' ||
                                        'pon_eval_team_util_pvt.' ||
                                        'gen_eval_update_scorer_body/' ||
                                        l_itemtype || ':' ||
                                        l_itemkey);

Line: 1096

END send_eval_update_scorer_notif;

Line: 1099

PROCEDURE gen_eval_update_scorer_body(p_document_id   IN VARCHAR2,
                                      p_display_type  IN VARCHAR2,
                                      x_document      IN OUT NOCOPY CLOB,
                                      x_document_type IN OUT NOCOPY VARCHAR2)
IS

  CURSOR c_notification_id(p_itemtype IN VARCHAR2,
                           p_itemkey  IN VARCHAR2)
  IS
    SELECT notification_id
    FROM wf_item_activity_statuses
    WHERE item_type = p_itemtype
      AND item_key = p_itemkey
      AND assigned_user IS NOT NULL;

Line: 1176

    fnd_message.set_name('PON', 'PON_SM_EVAL_UPDATE_SCORER_HB');

Line: 1178

    fnd_message.set_name('PON', 'PON_SM_EVAL_UPDATE_SCORER_TB');

Line: 1192

END gen_eval_update_scorer_body;