APPS.PON_EXHIBITS_PKG SQL Statements

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

Line: 8

 * This will be called only when multiple lines are inserted/Deleted
 */
PROCEDURE HANDLE_AUCTION_EXHIBIT_DETAILS(p_auction_header_id NUMBER)
                IS
                p_EXHIBIT_NUMBER     VARCHAR2(2);

Line: 14

                p_LAST_UPDATED_BY     NUMBER;

Line: 24

                                  DELETE FROM pon_auction_exhibit_details WHERE auction_header_id=p_auction_header_id
                                  AND IS_CDRL = 'N'
                                  AND exhibit_number in
                                  (SELECT exhibits.exhibit_number FROM pon_auction_exhibit_details exhibits WHERE exhibits.auction_header_id=p_auction_header_id
                                  AND IS_CDRL='N'
                                  AND exhibit_number NOT IN (SELECT exhibit_number FROM pon_auction_item_prices_ALL items
                                  WHERE items.auction_header_id=p_auction_header_id AND items.exhibit_number=exhibits.exhibit_number));

Line: 38

                        SELECT created_by,last_updated_by
                        INTO
                        p_CREATED_BY,p_LAST_UPDATED_BY
                        FROM pon_auction_headers_all
                        WHERE auction_header_id=p_auction_header_id;

Line: 46

                         /*     SELECT exhibit_number
                              INTO p_EXHIBIT_NUMBER
                              FROM pon_auction_item_prices_ALL items
                              WHERE items.auction_header_id=p_auction_header_id
                              AND items.exhibit_number IS NOT NULL
                              AND NOT EXISTS
                                    (SELECT exhibit_number FROM pon_auction_exhibit_details exhibits
                                     WHERE auction_header_id=p_auction_header_id AND IS_CDRL='N' AND items.exhibit_number=exhibits.exhibit_number)
                              AND ROWNUM=1;*/

Line: 56

                                 INSERT INTO pon_auction_exhibit_details
				 (AUCTION_HEADER_ID,
                                  EXHIBIT_NUMBER,
                                  IS_CDRL,
                                  ASSOCIATED_TO_LINE,
                                  EXHIBIT_DESCRIPTION,
                                  CREATION_DATE,
                                  CREATED_BY,
                                  LAST_UPDATE_DATE,
                                  LAST_UPDATED_BY)		(
                                         SELECT
                                         p_auction_header_id,
                                         exhibit_number,
                                         'N',
                                         NULL,
                                         NULL,
                                         SYSDATE,
                                         p_CREATED_BY,
                                         SYSDATE,
                                         p_LAST_UPDATED_BY
                                         FROM pon_auction_item_prices_ALL items
                                         WHERE items.auction_header_id=p_auction_header_id
                                         AND items.exhibit_number IS NOT NULL
                                         AND NOT EXISTS
					(SELECT exhibit_number FROM pon_auction_exhibit_details exhibits
                                         WHERE auction_header_id=p_auction_header_id AND IS_CDRL='N' AND items.exhibit_number=exhibits.exhibit_number)
					 GROUP BY exhibit_number);

Line: 93

/* This api will Insert data into Exhibit Details table
 * This will be called when single exhibit line is created/Modified
 */
PROCEDURE CREATE_EXHIBIT_DETAILS(p_auction_header_id NUMBER,p_old_exhibit_number varchar2,p_exhibit_number varchar2)
IS
number_of_exhibits NUMBER;

Line: 101

p_LAST_UPDATED_BY     NUMBER;

Line: 110

          SELECT Count(*)
          INTO number_of_exhibits
          FROM pon_auction_exhibit_details
          WHERE auction_header_id=p_auction_header_id
          AND exhibit_number=p_exhibit_number
          AND IS_CDRL='N';

Line: 117

          SELECT Count(*)
          INTO number_of_lines
          FROM pon_auction_item_prices_all
          WHERE auction_header_id=p_auction_header_id
          AND exhibit_number=p_exhibit_number
          AND ROWNUM=1;

Line: 126

                         INSERT INTO pon_auction_exhibit_details
                                 (AUCTION_HEADER_ID,
                                  EXHIBIT_NUMBER,
                                  IS_CDRL,
                                  CREATION_DATE,
                                  LAST_UPDATE_DATE
                                  )
                                  values (p_auction_header_id,p_exhibit_number,'N',SYSDATE,SYSDATE);

Line: 144

                DELETE_EXHIBIT_DETAILS(p_auction_header_id ,p_old_exhibit_number);

Line: 179

/* This api will Delete data from Exhibit Details table
 * This will be called when single exhibit line is deleted/Modified
 */
PROCEDURE DELETE_EXHIBIT_DETAILS(p_auction_header_id NUMBER,p_exhibit_number varchar2)
IS
number_of_lines NUMBER;

Line: 191

                SELECT Count(*)
                INTO number_of_lines
                FROM pon_auction_item_prices_all
                WHERE auction_header_id=p_auction_header_id
                AND exhibit_number=p_exhibit_number
                AND ROWNUM=1;

Line: 200

                    DELETE from pon_auction_exhibit_details
                    WHERE auction_header_id=p_auction_header_id
                    AND exhibit_number=p_exhibit_number
                    AND IS_CDRL='N';

Line: 213

/* This api is used when CLINs are deleted in a Solicitation
 * If there are exhibits linked to the deleted CLIN, those links will be removed by this procedure
 * p_line_number is used based on single clin delete or multiple clin delete
 */
PROCEDURE UPDATE_EXHIBIT_DETAILS(p_auction_header_id NUMBER,p_line_number NUMBER)
IS
BEGIN
 IF(IS_EXHIBITS_ENABLED_FOR_AUC(p_auction_header_id) = 'N') THEN
      RETURN;

Line: 224

          UPDATE pon_auction_exhibit_details
          SET ASSOCIATED_TO_LINE=NULL
          WHERE AUCTION_HEADER_ID=p_auction_header_id
          AND ASSOCIATED_TO_LINE = p_line_number;

Line: 230

          UPDATE pon_auction_exhibit_details
          SET ASSOCIATED_TO_LINE=NULL
          WHERE AUCTION_HEADER_ID=p_auction_header_id
          AND ASSOCIATED_TO_LINE
          NOT IN (SELECT line_number FROM pon_auction_item_prices_all WHERE AUCTION_HEADER_ID=p_auction_header_id);

Line: 269

             select count(1) INTO l_cdrl_count
             FROM  okc_deliverables
             where business_document_id = p_auction_header_id
             and  business_document_version = -99
             and  del_category_code = 'CDRL'
             and  exhibit_code = p_exhibit_number;

Line: 294

             INSERT_CDRL_EXHIBIT_DETAILS(p_auction_header_id,p_new_exhibit_number,p_return_status,p_msg_data);

Line: 302

/* This api is to Delete Exhibits
 */
PROCEDURE DELETE_EXHIBITS(
p_auction_header_id NUMBER,
p_doctype_id NUMBER,
p_user_id NUMBER,
p_exhibit_number VARCHAR2,
p_is_cdrl VARCHAR2)
IS

p_msg_data         VARCHAR2(1000);

Line: 322

             DELETE_ELINS(p_auction_header_id,p_doctype_id,p_user_id,p_exhibit_number);

Line: 327

             OKC_CDRL_PVT.delete_cdrl_for_exhibits
                        (p_api_version =>  1.0,
                         p_init_msg_list =>  FND_API.G_TRUE,
                         p_commit =>  FND_API.G_FALSE,
                         --p_doc_class => 'SOURCING',
                         p_doc_type =>  'SOLICITATION',
                         p_doc_id   =>  p_auction_header_id,
                         p_doc_version => NULL,
                         p_mode => NULL,
                         p_exhibit_tbl => l_exhibit_tbl,
                         x_msg_data => p_msg_data,
                         x_msg_count => p_msg_count,
                         x_return_status => p_return_status);

Line: 341

             DELETE_CDRL_EXHIBIT_DETAILS(p_auction_header_id,p_exhibit_number,p_return_status);

Line: 357

          SELECT lookup_code
          INTO p_new_exhibit_number
          FROM  (select lookup_code
                 from fnd_lookup_values
                 where lookup_type = 'PO_CLM_EXHIBIT_NUMBER'
                 AND Length(lookup_code) = 1
                 AND lookup_code NOT IN
                 (SELECT exhibit_number FROM pon_auction_exhibit_details WHERE auction_header_id=p_source_auction_header_id)
                 ORDER BY lookup_code)
          WHERE ROWNUM=1;

Line: 369

         SELECT lookup_code
         INTO p_new_exhibit_number
         FROM        (select lookup_code
                     from fnd_lookup_values
                     where lookup_type = 'PO_CLM_EXHIBIT_NUMBER'
                     AND lookup_code NOT IN
                       (SELECT exhibit_number FROM pon_auction_exhibit_details WHERE auction_header_id=p_source_auction_header_id)
                     ORDER BY length(lookup_code), lookup_code)
         WHERE ROWNUM=1;

Line: 419

         SELECT line_number
         BULK COLLECT INTO  p_lines
         FROM   pon_auction_item_prices_all
         WHERE  auction_header_id = p_auction_header_id
         AND    exhibit_number = p_exhibit_number;

Line: 431

         SELECT Max(line_number)
         INTO p_initial_line_number
         FROM pon_auction_item_prices_all WHERE
         auction_header_id = p_auction_header_id;

Line: 441

         SELECT Max(line_number)
         INTO p_final_line_number
         FROM pon_auction_item_prices_all WHERE
         auction_header_id = p_auction_header_id;

Line: 449

         SELECT X,p_new_exhibit_number||Decode(Length(p_new_exhibit_number)
                      +Length(pon_clo_renumber_pkg.DECIMAL_TO_ELIN(X)),4,'',3,'0',2,'00')
                      ||pon_clo_renumber_pkg.DECIMAL_TO_ELIN(X) AS NEXT_ELIN_SET
           BULK COLLECT INTO p_line_numbers,p_line_num_displays
           FROM
            (SELECT LEVEL AS X
              FROM dual
              CONNECT BY LEVEL <=p_final_line_number-p_initial_line_number+1);

Line: 460

         UPDATE pon_auction_item_prices_all
         SET exhibit_number = p_new_exhibit_number,
         line_num_display=p_line_num_displays(i)
         WHERE auction_header_id = p_auction_header_id
         AND line_number=p_line_numbers(i)+p_initial_line_number;

Line: 473

        SELECT NVL(MAX(line_number),0)
        INTO P_LINE_NUMBER
        FROM pon_auction_item_prices_all
        WHERE auction_header_id = p_source_auction_header_id;

Line: 478

        SELECT Count(*)
        INTO P_NO_OF_LINES
        FROM pon_auction_item_prices_all
        WHERE auction_header_id = p_source_auction_header_id;

Line: 483

        UPDATE PON_AUCTION_HEADERS_ALL
        SET NUMBER_OF_LINES     = P_NO_OF_LINES,
        LAST_LINE_NUMBER      = P_LINE_NUMBER
        WHERE AUCTION_HEADER_ID = p_source_auction_header_id;

Line: 495

PROCEDURE DELETE_ELINS(
p_auction_header_id NUMBER,
p_doctype_id NUMBER,
p_user_id NUMBER,
p_exhibit_number VARCHAR2)
IS
        p_source_auction_header_id NUMBER := p_auction_header_id;

Line: 509

        p_number_of_lines_deleted  NUMBER;

Line: 515

         LOG_MESSAGE('delete_lines','IS_EXHIBITS_ENABLED_FOR_AUC:'||'Y' );

Line: 517

         SELECT line_number
         BULK COLLECT INTO  p_lines
         FROM   pon_auction_item_prices_all
         WHERE  auction_header_id = p_auction_header_id
         AND    exhibit_number = p_exhibit_number;

Line: 523

        LOG_MESSAGE('delete_lines','After Collecting the line number of the exhibit Lines' );

Line: 527

         pon_clm_clo_util_pkg.delete_single_line
                               (p_auction_header_id=> p_auction_header_id,
                                p_line_number => p_lines(i),
                                x_result  =>  p_result,
                                x_error_code => p_error_code,
                                x_error_message => p_error_message,
                                x_number_of_lines_deleted => p_number_of_lines_deleted);

Line: 536

        LOG_MESSAGE('delete_lines','Number of Lines Deleted'||p_number_of_lines_deleted);

Line: 538

        LOG_MESSAGE('delete_lines','Remove Exhibit Details record' );

Line: 540

        DELETE_EXHIBIT_DETAILS(p_source_auction_header_id,p_exhibit_number);

Line: 542

        LOG_MESSAGE('delete_lines','Delete Lines Completed' );

Line: 544

        SELECT NVL(MAX(line_number),0)
        INTO P_LINE_NUMBER
        FROM pon_auction_item_prices_all
        WHERE auction_header_id = p_source_auction_header_id;

Line: 549

        SELECT Count(*)
        INTO P_NO_OF_LINES
        FROM pon_auction_item_prices_all
        WHERE auction_header_id = p_source_auction_header_id;

Line: 554

        UPDATE PON_AUCTION_HEADERS_ALL
        SET NUMBER_OF_LINES     = P_NO_OF_LINES,
        LAST_LINE_NUMBER      = P_LINE_NUMBER
        WHERE AUCTION_HEADER_ID = p_source_auction_header_id;

Line: 562

          LOG_MESSAGE('delete_lines','Exception in Delete Lines' );

Line: 586

   SELECT Count(*)
   INTO l_elin_count
   FROM pon_auction_exhibit_details
   WHERE auction_header_id = p_auction_header_id
   AND associated_to_line = p_line_number
   AND Nvl(is_cdrl, 'N') = 'N';

Line: 607

    SELECT Decode(Nvl(exhibit_number, '-1'), '-1' , 'N', 'Y')
    INTO l_is_elin
    FROM pon_auction_item_prices_all
    WHERE auction_header_id = p_auction_header_id
    AND   line_number = p_line_number;

Line: 624

   SELECT org_id, doctype_id
   INTO l_org_id,l_doctype_id
   FROM pon_auction_headers_all
   WHERE auction_header_id = p_auction_header_id;

Line: 657

/* This api will Insert data into Exhibit Details table for cdrls.
 * This will be called from okc code.
 * If cdrl record already exists : x_return_status = 'S', x_return_msg := 'CDRL_RECORD_EXISTS'
 * If p_exhibit_number is used by elins : x_return_status = 'U', x_return_msg := 'EXHIBIT_NUM_USED_BY_ELIN'
 */
PROCEDURE INSERT_CDRL_EXHIBIT_DETAILS(p_auction_header_id IN  NUMBER,
                                      p_exhibit_number    IN  VARCHAR2,
                                      x_return_status     OUT NOCOPY VARCHAR2,
                                      x_return_msg        OUT NOCOPY VARCHAR2)
IS
l_cdrl_exists VARCHAR2(1);

Line: 676

          SELECT Nvl(is_cdrl, 'E')
          INTO l_cdrl_exists
          FROM pon_auction_exhibit_details
          WHERE auction_header_id=p_auction_header_id
          AND   exhibit_number=p_exhibit_number;

Line: 698

          INSERT INTO pon_auction_exhibit_details
          (AUCTION_HEADER_ID,
           EXHIBIT_NUMBER,
           IS_CDRL,
           ASSOCIATED_TO_LINE,
           EXHIBIT_DESCRIPTION,
           CREATION_DATE,
           CREATED_BY,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY)
           VALUES
           (p_auction_header_id,p_exhibit_number,'Y', NULL, NULL, SYSDATE,l_user_id,SYSDATE,l_user_id);

Line: 713

END INSERT_CDRL_EXHIBIT_DETAILS;

Line: 715

/* This api will delete cdrl exhibit record from pon_auction_exhibit_details table.
 * This api should be called by okc code.
*/
PROCEDURE DELETE_CDRL_EXHIBIT_DETAILS(p_auction_header_id IN  NUMBER,
                                      p_exhibit_number    IN  VARCHAR2,
                                      x_return_status     OUT NOCOPY VARCHAR2)

IS

BEGIN

    DELETE FROM pon_auction_exhibit_details
    WHERE auction_header_id = p_auction_header_id
    AND exhibit_number =  p_exhibit_number
    AND is_cdrl = 'Y';

Line: 733

END DELETE_CDRL_EXHIBIT_DETAILS;