APPS.PON_THREAD_DISC_PKG SQL Statements

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

Line: 6

function insert_pon_discussions(
         p_entity_name             IN VARCHAR2,
         p_entity_pk1              IN VARCHAR2,
         p_entity_pk2              IN VARCHAR2,
         p_entity_pk3              IN VARCHAR2,
         p_entity_pk4              IN VARCHAR2,
         p_entity_pk5              IN VARCHAR2,
         p_subject                 IN VARCHAR2,
         p_language_code           IN VARCHAR2,
         p_party_id                IN NUMBER,
         p_validation_class        IN VARCHAR2)
return NUMBER
IS

    l_discussion_id NUMBER(15);

Line: 25

  SELECT discussion_id
  INTO   l_discussion_id
  FROM  pon_discussions
  WHERE  ENTITY_NAME = p_entity_name
  AND pk1_value =  p_entity_pk1;

Line: 34

    SELECT pon_discussions_s.nextval
    INTO   l_discussion_id
    FROM   dual;

Line: 38

    INSERT INTO pon_discussions(
          DISCUSSION_ID,
          ENTITY_NAME,
          PK1_VALUE,
          SUBJECT,
          LAST_UPDATE_DATE,
          VALIDATION_CLASS,
          PK2_VALUE,
          PK3_VALUE,
          PK4_VALUE,
          PK5_VALUE,
          LANGUAGE_CODE,
          OWNER_PARTY_ID
    )VALUES(
          l_discussion_id,
          p_entity_name,
          p_entity_pk1,
          nvl(p_subject,p_entity_pk1),
          sysdate,
          p_validation_class,
          p_entity_pk2,
          p_entity_pk3,
          p_entity_pk4,
          p_entity_pk5,
          p_language_code,
          p_party_id);

Line: 67

END insert_pon_discussions;

Line: 69

function insert_pon_threads(
         p_discussion_id           IN NUMBER,
         p_subject                 IN VARCHAR2,
         p_language_code           IN VARCHAR2,
         p_party_id                IN NUMBER)
return NUMBER
IS

    l_prev_thread_number NUMBER(15);

Line: 85

    SELECT nvl(max(thread_number), -1)
    INTO l_prev_thread_number
    FROM pon_threads
    WHERE discussion_id = p_discussion_id;

Line: 90

    SELECT nvl(p_language_code, language_code)
    INTO l_lang_code
    FROM pon_discussions
    WHERE discussion_id = p_discussion_id;

Line: 96

    INSERT INTO pon_threads(
          THREAD_NUMBER,
          OWNER_PARTY_ID,
          DISCUSSION_ID,
          SUBJECT,
          LANGUAGE_CODE,
          LAST_UPDATE_DATE
    )VALUES(
          l_prev_thread_number + 1,
          p_party_id,
          p_discussion_id,
          p_subject,
          l_lang_code,
          l_now_date);

Line: 111

    UPDATE pon_discussions
    SET last_update_date = l_now_date
    WHERE discussion_id = p_discussion_id;

Line: 117

END insert_pon_threads;

Line: 120

function insert_thread_entry(
         p_from_id in NUMBER,
         p_from_first_name in VARCHAR2,
         p_from_last_name in VARCHAR2,
         p_subject in VARCHAR2,
         p_discussion_id in VARCHAR2,
         p_thread_id in NUMBER,
         p_broadcast_flag in VARCHAR2,
         p_parent_id in NUMBER)
return NUMBER
IS

    l_entry_id   NUMBER(15);

Line: 137

    SELECT pon_thread_entries_s.nextval
    INTO l_entry_id FROM dual;

Line: 142

    INSERT INTO pon_thread_entries(
          ENTRY_ID,
          PARENT_ENTRY_ID,
          FROM_ID,
          FROM_FIRST_NAME,
          FROM_LAST_NAME,
          POSTED_DATE,
          SUBJECT,
          THREAD_NUMBER,
          DISCUSSION_ID,
          BROADCAST_FLAG,
          CONTENT
    )VALUES(
          l_entry_id,
          p_parent_id,
          p_from_id,
          p_from_first_name,
          p_from_last_name,
          l_now_date,
          p_subject,
          p_thread_id,
          p_discussion_id,
          p_broadcast_flag,
          empty_clob());

Line: 167

    UPDATE pon_threads
    SET last_update_date = l_now_date
    WHERE discussion_id = p_discussion_id
    AND thread_number = p_thread_id;

Line: 172

    UPDATE pon_discussions
    SET last_update_date = l_now_date
    WHERE discussion_id = p_discussion_id;

Line: 178

END insert_thread_entry;

Line: 208

procedure insert_or_update_recipient(
         p_entry_id in NUMBER,
         p_recipient_id in NUMBER,
         p_read_flag in VARCHAR2,
         p_replied_flag in VARCHAR2,
         p_to_company_id in NUMBER,
         p_to_first_name in VARCHAR2,
         p_to_last_name in VARCHAR2,
         p_to_company_name in VARCHAR2)
IS
  l_row_exists NUMBER;

Line: 219

  l_module_name VARCHAR2(40) := 'INSERT_OR_UPDATE_RECIPIENT';

Line: 223

    SELECT COUNT(1) INTO l_row_exists
    FROM pon_te_recipients
    WHERE to_id = p_recipient_id
    AND entry_id = p_entry_id;

Line: 232

          message  => 'Entering PON_THREAD_DISC_PKG.INSERT_OR_UPDATE_RECIPIENT'
                      || ', l_row_exists = ' || l_row_exists
                      || ', p_recipient_id = ' || p_recipient_id
                      || ', p_entry_id = ' || p_entry_id
                      || ', p_read_flag  = ' || p_read_flag
                      || ', p_replied_flag   = ' || p_replied_flag
                      || ', p_to_company_id = ' ||    p_to_company_id
                      || ', p_to_first_name = ' ||    p_to_first_name
                      || ', p_to_last_name   = ' || p_to_last_name
                      || ', p_to_company_name = '||   p_to_company_name);

Line: 246

        UPDATE pon_te_recipients
        SET read_flag = p_read_flag,
            replied_flag = p_replied_flag
        WHERE to_id = p_recipient_id
        AND entry_id = p_entry_id;

Line: 254

        INSERT INTO pon_te_recipients(
            TO_ID,
            READ_FLAG,
            ENTRY_ID,
            REPLIED_FLAG,
            TO_COMPANY_ID,
            TO_FIRST_NAME,
            TO_LAST_NAME,
            TO_COMPANY_NAME
        )VALUES(
            p_recipient_id,
            p_read_flag,
            p_entry_id,
            p_replied_flag,
            p_to_company_id,
            p_to_first_name,
            p_to_last_name ,
            p_to_company_name);

Line: 274

END insert_or_update_recipient;

Line: 300

PROCEDURE update_recipient_to_read(
         p_entry_id in NUMBER,
         p_recipient_id in NUMBER,
         p_to_company_id in NUMBER,
         p_to_first_name in VARCHAR2,
         p_to_last_name in VARCHAR2,
         p_to_company_name in VARCHAR2)
IS
    l_num_entries  NUMBER(1);

Line: 310

    l_module_name VARCHAR2(40) := 'UPDATE_RECIPIENT_TO_READ';

Line: 312

    SELECT count(1) INTO l_num_entries
    FROM pon_te_recipients
    WHERE entry_id = p_entry_id
    AND to_id = p_recipient_id;

Line: 320

          message  => 'Entering PON_THREAD_DISC_PKG.UPDATE_RECIPIENT_TO_READ'
                      || ', l_num_entries = ' || l_num_entries
                      || ', p_recipient_id = ' || p_recipient_id
                      || ', p_entry_id = ' || p_entry_id
                      || ', p_to_company_id = ' ||    p_to_company_id
                      || ', p_to_first_name = ' ||    p_to_first_name
                      || ', p_to_last_name   = ' ||   p_to_last_name
                      || ', p_to_company_name = '||   p_to_company_name);

Line: 331

        insert_or_update_recipient( p_entry_id, p_recipient_id, 'Y', 'N', p_to_company_id, p_to_first_name, p_to_last_name, p_to_company_name);

Line: 333

        SELECT replied_flag INTO l_replied_state
        FROM pon_te_recipients
        WHERE entry_id = p_entry_id
        AND to_id = p_recipient_id;

Line: 338

        insert_or_update_recipient( p_entry_id, p_recipient_id, 'Y', l_replied_state, null, null, null, null);

Line: 340

END update_recipient_to_read;

Line: 351

    INSERT INTO pon_te_view_audit(
            VIEW_DATE,
            ENTRY_ID,
            VIEWER_PARTY_ID
        )VALUES(
            sysdate,
            p_entry_id,
            p_reader
        );

Line: 397

        SELECT PON_LOCALE_PKG.get_party_display_name(PTR.TO_ID, PON_LOCALE_PKG.DEFAULT_NAME_DISPLAY_PATTERN, userenv('LANG')) as name
        FROM  PON_TE_RECIPIENTS PTR
        WHERE PTR.REPLIED_FLAG='Y'
            AND PTR.ENTRY_ID = x_entry_id
            AND PTR.TO_COMPANY_ID = x_auctioneer_tp_id      -- auctioneer's trading partner id
            AND PTR.TO_ID <> x_to_id                        -- Replied by Others
            AND 'EXTERNAL'=x_message_type;                  -- Replied to External Messages

Line: 459

        SELECT PON_LOCALE_PKG.get_party_display_name(PTR.TO_ID, PON_LOCALE_PKG.DEFAULT_NAME_DISPLAY_PATTERN, userenv('LANG')) || DECODE(x_message_type,'EXTERNAL',' - '|| PTR.TO_COMPANY_NAME) as name
        FROM  PON_TE_RECIPIENTS PTR
        WHERE PTR.ENTRY_ID = x_entry_id
            AND PTR.TO_ID <> x_from_id
	    AND ((x_message_type='EXTERNAL' AND PTR.TO_COMPANY_ID <> x_from_company_id)
                 OR x_message_type <> 'EXTERNAL');

Line: 480

    SELECT from_company_id
    INTO l_from_company_id
    FROM PON_THREAD_ENTRIES
    WHERE entry_id = p_entry_id;

Line: 540

        SELECT decode(replied_flag, 'Y', l_msg_replied, decode(read_flag, 'Y', l_msg_read, l_msg_unread))
        INTO l_message_status
        FROM pon_thread_entries pte, pon_te_recipients ptr
        WHERE pte.entry_id = p_entry_id
        AND ptr.entry_id = pte.entry_id
        AND ((pte.from_id = p_viewer_id AND pte.from_id = ptr.to_id)
        OR (ptr.to_id = p_viewer_id AND pte.from_id <> ptr.to_id));