APPS.PO_EXHIBITS_PVT SQL Statements

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

Line: 127

  SELECT DISTINCT line_num_display
  BULK COLLECT INTO l_oth_line_num_arr
  FROM   po_lines_merge_v
  WHERE  po_header_id = p_header_id
         AND clm_exhibit_name =  p_exhibit_name
         AND change_status='NEW'
         AND status in ('DRAFT','REJECTED','IN PROCESS','PRE-APPROVED');

Line: 151

  SELECT Nvl(Max(ROWNUM),0)+1
  INTO lineNumber
  FROM
      (SELECT PO_EXHIBITS_PVT.ELIN_TO_DECIMAL(SubStr(a.column_value,2+1,4-2)) elin_decimal
      FROM Table(l_merged_line_num_arr) a
      order by elin_decimal)
  WHERE elin_decimal=ROWNUM ;

Line: 218

  SELECT lookup_code
  INTO l_next_exhibit
  FROM
  (SELECT lookup_code
    FROM fnd_lookup_values lk
    WHERE lookup_type = 'PO_CLM_EXHIBIT_NUMBER'
    AND NOT EXISTS (SELECT 1 FROM po_exhibit_details_merge_v pex
                            WHERE pex.po_header_id = p_header_id
                            AND pex.draft_id = p_draft_id
                            AND pex.exhibit_name = lk.lookup_code)
    ORDER BY LENGTH(lookup_code),lookup_code
  ) WHERE ROWNUM = 1;

Line: 267

  SELECT 1
  INTO l_dummy
  FROM po_exhibit_details_draft
  WHERE po_exhibit_details_id = p_po_exhibit_details_id
  AND draft_id = p_draft_id
  FOR UPDATE NOWAIT;

Line: 311

  SELECT 1
  INTO l_dummy
  FROM po_exhibit_details
  WHERE po_exhibit_details_id = p_po_exhibit_details_id
  FOR UPDATE NOWAIT;

Line: 359

  p_delete_flag_tbl          IN PO_TBL_VARCHAR1,
  x_record_already_exist_tbl OUT NOCOPY PO_TBL_VARCHAR1
) IS

d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';

Line: 414

  INSERT INTO po_exhibit_details_draft
        (
          po_exhibit_details_id,
          po_header_id,
          draft_id,
          delete_flag,
          change_accepted_flag,
          exhibit_name,
          exhibit_description,
          is_cdrl,
          reference_line_id,
	  revision_num,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_LOGIN
          )
        SELECT
          po_exhibit_details_id,
          po_header_id,
          p_draft_id_tbl(i),
          p_delete_flag_tbl(i),
          NULL,
          exhibit_name,
          exhibit_description,
          is_cdrl,
          reference_line_id,
	  revision_num,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_LOGIN
          FROM po_exhibit_details
        WHERE po_exhibit_details_id = p_po_exhibit_details_id_tbl(i)
        AND x_record_already_exist_tbl(i) = FND_API.G_FALSE
        AND l_duplicate_flag_tbl(i) = FND_API.G_FALSE;

Line: 459

    UPDATE po_exhibit_details_draft
    SET    delete_flag = p_delete_flag_tbl(i)
    WHERE  po_exhibit_details_id = p_po_exhibit_details_id_tbl(i)
    AND    draft_id = p_draft_id_tbl(i)
    AND    NVL(delete_flag, 'N') <> 'Y'  -- bug5570989
    AND    x_record_already_exist_tbl(i) = FND_API.G_TRUE;

Line: 469

    PO_LOG.stmt(d_module, d_position, 'update draft records that are already' ||
                ' in draft table. Count = ' || SQL%ROWCOUNT);

Line: 518

  p_delete_flag IN VARCHAR2,
  x_record_already_exist OUT NOCOPY VARCHAR2
) IS

d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';

Line: 538

    p_delete_flag_tbl          => PO_TBL_VARCHAR1(p_delete_flag),
    x_record_already_exist_tbl => l_record_already_exist_tbl
  );

Line: 626

    INSERT INTO po_session_gt
    ( key,
      num1
    )
    SELECT l_key,
           l_index_tbl(i)
    FROM DUAL
    WHERE EXISTS (SELECT 1
                  FROM   po_exhibit_details_draft PDD
                  WHERE  PDD.draft_id = p_draft_id_tbl(i)
                  AND    PDD.po_exhibit_details_id =
                           NVL(p_po_exhibit_details_id_tbl(i),
                               PDD.po_exhibit_details_id)
                  AND    NVL(PDD.change_accepted_flag, 'Y') = 'Y');

Line: 646

  DELETE FROM po_session_gt
  WHERE key = l_key
  RETURNING num1
  BULK COLLECT INTO l_dft_exists_index_tbl;

Line: 782

  DELETE FROM po_exhibit_details pe
  WHERE pe.po_exhibit_details_id IN
         ( SELECT ped.po_exhibit_details_id -- Bug 5292573
           FROM   po_exhibit_details_draft ped
           WHERE  ped.draft_id = p_draft_id
           AND    ped.delete_flag = 'Y'
           AND    NVL(ped.change_accepted_flag, 'Y') = 'Y' );

Line: 792

    SELECT
      PED.draft_id,
      PED.delete_flag,
      PED.change_accepted_flag,
      PED.po_exhibit_details_id,
      PED.po_header_id,
      PED.exhibit_name,
      PED.exhibit_description,
      PED.is_cdrl,
      PED.reference_line_id,
      PED.revision_num,
      PED.last_update_date,
      PED.last_updated_by,
      PED.creation_date,
      PED.created_by,
      PED.last_update_login
    FROM po_exhibit_details_draft PED
    WHERE PED.draft_id = p_draft_id
    AND NVL(PED.change_accepted_flag, 'Y') = 'Y'
    ) PEDV
  ON (PE.po_exhibit_details_id = PEDV.po_exhibit_details_id)
  WHEN MATCHED THEN
    UPDATE
    SET
      PE.last_update_date = PEDV.last_update_date,
      PE.last_updated_by = PEDV.last_updated_by,
      PE.po_header_id = PEDV.po_header_id,
      PE.last_update_login = PEDV.last_update_login,
      PE.exhibit_name = PEDV.exhibit_name,
      PE.exhibit_description = PEDV.exhibit_description,
      PE.is_cdrl = PEDV.is_cdrl,
      PE.reference_line_id = PEDV.reference_line_id,
      PE.revision_num = PEDV.revision_num
  --  DELETE WHERE PDDV.delete_flag = 'Y'
  WHEN NOT MATCHED THEN
    INSERT
    (
      PE.po_exhibit_details_id,
      PE.exhibit_name,
      PE.exhibit_description,
      PE.is_cdrl,
      PE.revision_num,
      PE.reference_line_id, --16626594
      PE.last_update_date,
      PE.last_updated_by,
      PE.po_header_id,
      PE.last_update_login,
      PE.creation_date,
      PE.created_by
    )
    VALUES
    (
      PEDV.po_exhibit_details_id,
      PEDV.exhibit_name,
      PEDV.exhibit_description,
      PEDV.is_cdrl,
      PEDV.revision_num,
      PEDV.reference_line_id, -- 16626594
      PEDV.last_update_date,
      PEDV.last_updated_by,
      PEDV.po_header_id,
      PEDV.last_update_login,
      PEDV.creation_date,
      PEDV.created_by
    ) WHERE NVL(PEDV.delete_flag, 'N') <> 'Y';

Line: 956

PROCEDURE delete_rows
( p_draft_id IN NUMBER,
  p_po_exhibit_details_id IN NUMBER
) IS

d_api_name CONSTANT VARCHAR2(30) := 'delete_rows';

Line: 972

  DELETE FROM po_exhibit_details_draft
  WHERE draft_id = p_draft_id
  AND po_exhibit_details_id = NVL(p_po_exhibit_details_id, po_exhibit_details_id);

Line: 984

END delete_rows;

Line: 1009

PROCEDURE insert_exhibits
(
  p_document_type_tbl IN PO_TBL_VARCHAR30,
  p_document_id_tbl   IN PO_TBL_NUMBER,
  p_exhibit_name_tbl  IN PO_TBL_VARCHAR30,
  p_exhibit_description_tbl IN PO_TBL_VARCHAR240,
  p_is_cdrl_tbl       IN PO_TBL_VARCHAR1,
  p_revision_num_tbl  IN PO_TBL_NUMBER,
  x_return_status OUT NOCOPY VARCHAR2,
  x_return_msg   OUT NOCOPY VARCHAR2
) IS

d_api_name CONSTANT VARCHAR2(30) := 'insert_cdrl_exhibits';

Line: 1044

            SELECT Nvl(is_cdrl,'N')
            INTO l_is_valid_exhibit
            FROM po_exhibit_details ex
            WHERE  ex.exhibit_name = p_exhibit_name_tbl(i)
            AND ex.po_header_id = p_document_id_tbl(i);

Line: 1056

            INSERT INTO po_exhibit_details
              (
                po_exhibit_details_id,
                po_header_id,
                exhibit_name,
                exhibit_description,
                is_cdrl,
		revision_num,
                LAST_UPDATE_DATE,
                LAST_UPDATED_BY,
                CREATION_DATE,
                CREATED_BY,
                LAST_UPDATE_LOGIN
              )
            SELECT
                po_exhibit_details_s.nextval,
                p_document_id_tbl(i),
                p_exhibit_name_tbl(i),
                p_exhibit_description_tbl(i),
                p_is_cdrl_tbl(i),
		p_revision_num_tbl(i),
                SYSDATE ,
                fnd_global.user_id,
                SYSDATE,
                fnd_global.user_id,
                fnd_global.login_id
                FROM dual
                WHERE NOT EXISTS (SELECT 1 FROM po_exhibit_details ex
                                      WHERE ex.exhibit_name = p_exhibit_name_tbl(i)
                                      AND ex.po_header_id = p_document_id_tbl(i));

Line: 1098

              SELECT Nvl(is_cdrl, 'N')
              INTO l_is_valid_exhibit
              FROM po_exhibit_details_merge_v ex
              WHERE  ex.exhibit_name = p_exhibit_name_tbl(i)
              AND ex.draft_id = p_document_id_tbl(i) ;

Line: 1111

              INSERT INTO po_exhibit_details_draft
                (
                  po_exhibit_details_id,
                  po_header_id,
                  draft_id,
                  exhibit_name,
                  exhibit_description,
                  is_cdrl,
                  change_status,
		  revision_num,
                  LAST_UPDATE_DATE,
                  LAST_UPDATED_BY,
                  CREATION_DATE,
                  CREATED_BY,
                  LAST_UPDATE_LOGIN
                )
              SELECT
                  po_exhibit_details_s.nextval,
                  dft.DOCUMENT_ID,
                  p_document_id_tbl(i),
                  p_exhibit_name_tbl(i),
                  p_exhibit_description_tbl(i),
                  p_is_cdrl_tbl(i),
                  'NEW',
		  p_revision_num_tbl(i),
                  SYSDATE ,
                  fnd_global.user_id,
                  SYSDATE,
                  fnd_global.user_id,
                  fnd_global.login_id
                  FROM po_drafts dft
                  WHERE dft.draft_id = p_document_id_tbl(i)
                  AND NOT EXISTS (SELECT 1 FROM po_exhibit_details_merge_v mex
                                          WHERE mex.exhibit_name = p_exhibit_name_tbl(i)
                                          AND mex.draft_id = p_document_id_tbl(i));

Line: 1174

END insert_exhibits;

Line: 1200

PROCEDURE delete_exhibits
(
  p_document_type_tbl IN PO_TBL_VARCHAR30,
  p_document_id_tbl   IN PO_TBL_NUMBER,
  p_exhibit_name_tbl  IN PO_TBL_VARCHAR30,
  p_is_cdrl_tbl       IN PO_TBL_VARCHAR1,
  x_return_status OUT NOCOPY VARCHAR2,
  x_return_msg   OUT NOCOPY VARCHAR2
) IS

d_api_name CONSTANT VARCHAR2(30) := 'delete_exhibits';

Line: 1232

            DELETE po_exhibit_details ex
            WHERE ex.exhibit_name = p_exhibit_name_tbl(i)
            AND ex.po_header_id = p_document_id_tbl(i)
            AND ex.is_cdrl = p_is_cdrl_tbl(i);

Line: 1239

            DELETE po_exhibit_details_draft mex
            WHERE mex.exhibit_name = p_exhibit_name_tbl(i)
            AND mex.draft_id = p_document_id_tbl(i)
            AND mex.is_cdrl = p_is_cdrl_tbl(i);

Line: 1262

END delete_exhibits;

Line: 1340

      INSERT INTO po_exhibit_details
      (
        po_exhibit_details_id,
        po_header_id,
        exhibit_name,
        exhibit_description,
        is_cdrl,
	      revision_num,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN
      )
      SELECT
          po_exhibit_details_s.nextval,
          p_po_header_id,
          l_new_exhibit_name,
          exhibit_description,
          is_cdrl,
	        revision_num,
          SYSDATE ,
          fnd_global.user_id,
          SYSDATE,
          fnd_global.user_id,
          fnd_global.login_id
          FROM po_exhibit_details
          WHERE po_header_id = p_po_header_id
          AND exhibit_name = p_exhibit_name;

Line: 1382

      INSERT INTO po_exhibit_details_draft
      (
          po_exhibit_details_id,
          po_header_id,
          draft_id,
          exhibit_name,
          exhibit_description,
          is_cdrl,
          change_status,
		      revision_num,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_LOGIN
        )
      SELECT
          po_exhibit_details_s.nextval,
          po_header_id,
          draft_id,
          l_new_exhibit_name,
          exhibit_description,
          is_cdrl,
          'NEW',
		      revision_num,
          SYSDATE ,
          fnd_global.user_id,
          SYSDATE,
          fnd_global.user_id,
          fnd_global.login_id
          FROM po_exhibit_details_draft dft
          WHERE po_header_id = p_po_header_id
          AND draft_id = p_po_draft_id
          AND exhibit_name = p_exhibit_name;

Line: 1491

PROCEDURE delete_cdrls_for_exhibit
(
  p_po_header_id IN NUMBER,
  p_po_draft_id  IN NUMBER,
  p_exhibit_name IN VARCHAR2,
  p_doc_sub_type IN VARCHAR2,
  x_return_status OUT NOCOPY VARCHAR2,
  x_return_msg    OUT NOCOPY VARCHAR2
) IS

d_api_name CONSTANT VARCHAR2(30) := 'delete_cdrls_for_exhibit';

Line: 1557

  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_type       => l_document_type,
                              p_doc_id         => l_docid,
                              p_doc_version    => NULL,
                              p_mode           => NULL,
                              p_exhibit_tbl    => l_exhibit_tbl,
                              x_msg_data       => l_msg_data,
                              x_msg_count      => l_msg_count,
                              x_return_status  => l_return_status
                              );

Line: 1593

END delete_cdrls_for_exhibit;