APPS.PO_PDOI_MAINPROC_PVT SQL Statements

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

Line: 34

PROCEDURE process_update_lines_in_group
(
  p_group_num           IN NUMBER,
  x_lines               IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
);

Line: 218

  l_delete_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();

Line: 263

      l_headers.intf_id_index_tbl.DELETE;

Line: 295

      PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_headers
      (
        p_headers     => l_headers
      );

Line: 322

      l_rej_intf_header_id_tbl.DELETE;

Line: 401

      SELECT
        draft_id,
        po_header_id,
        'N'
      BULK COLLECT INTO
        l_headers.draft_id_tbl,
        l_headers.po_header_id_tbl,
        l_delete_tbl

      FROM   po_headers_interface
      WHERE  processing_id = PO_PDOI_PARAMS.g_processing_id
            AND    processing_round_num = PO_PDOI_PARAMS.g_current_round_num
            AND    interface_header_id > l_max_intf_header_id
            AND    action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE
      ORDER by interface_header_id;

Line: 445

        PO_LOG.stmt(d_module, d_position, 'Before Inserting into Po_Headers_Draft_All');

Line: 452

        p_delete_flag_tbl            => l_delete_tbl,
        x_record_already_exist_tbl   => l_record_already_exist_tbl
      );

Line: 457

        PO_LOG.stmt(d_module, d_position, 'After Inserting into Po_Headers_Draft_All');

Line: 598

  DELETE FROM po_line_locations_interface
  WHERE process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_OBSOLETE
  AND   processing_id = PO_PDOI_PARAMS.g_processing_id;

Line: 674

  select interface_header_id
  into PO_PDOI_PARAMS.g_request.interface_header_id
  from po_headers_interface
  where processing_id = PO_PDOI_PARAMS.g_processing_id
  and   processing_round_num = 1;

Line: 756

      l_lines.intf_id_index_tbl.DELETE;

Line: 827

      PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_lines
      (
        x_lines              => l_lines
      );

Line: 835

      PO_PDOI_LINE_PROCESS_PVT.update_line_intf_tbl
      (
        x_lines               => l_lines
      );

Line: 872

      l_rej_intf_line_id_tbl.DELETE;

Line: 932

  SELECT interface_line_id
  BULK COLLECT INTO l_rej_intf_line_id_tbl
  FROM   po_lines_interface intf_line1
  WHERE  price_break_flag = 'Y'
  AND    processing_id = PO_PDOI_PARAMS.g_processing_id
  AND    EXISTS(
           SELECT 'Y'
           FROM   po_lines_interface intf_line2
           WHERE  intf_line1.interface_header_id = intf_line2.interface_header_id
           AND    intf_line1.po_line_id = intf_line2.po_line_id
           AND    NVL(intf_line2.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
		            = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED
           AND    NVL(intf_line2.price_break_flag, 'N') = 'N');

Line: 956

  UPDATE po_lines_interface intf_line1
  SET    intf_line1.process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
  WHERE  intf_line1.processing_id = PO_PDOI_PARAMS.g_processing_id
  AND    intf_line1.price_break_flag = 'Y'
  AND    EXISTS(
           SELECT 'Y'
           FROM   po_lines_interface intf_line2
           WHERE  intf_line2.interface_header_id = intf_line1.interface_header_id
           AND    intf_line2.po_line_id = intf_line1.po_line_id
           AND    NVL(intf_line2.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
		            = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
           AND    NVL(intf_line2.price_break_flag, 'N') = 'N');

Line: 1029

  l_update_lines         PO_PDOI_TYPES.lines_rec_type;

Line: 1032

  l_create_lines and l_update_lines inside the procedure*/
  l_null_lines         PO_PDOI_TYPES.lines_rec_type;

Line: 1120

      /*  null the l_create_lines and l_update_lines
         before calling split lines procedure in the inner loop*/
          l_create_lines  := l_null_lines;

Line: 1123

          l_update_lines  := l_null_lines;

Line: 1126

      l_lines.intf_id_index_tbl.DELETE;

Line: 1205

          x_update_lines          => l_update_lines
        );

Line: 1221

        process_update_lines_in_group
        (
          p_group_num            => l_group_num,
          x_lines                => l_update_lines
        );

Line: 1272

  SELECT v.interface_line_id
  BULK COLLECT INTO l_rej_intf_line_id_tbl
  FROM   po_lines_interface intf_line1,
         (SELECT intf_line2.interface_line_id, max(intf_line3.interface_line_id) AS match_intf_line_id
          FROM   po_lines_interface intf_line2, po_lines_interface intf_line3,
                 po_headers_interface intf_headers
          WHERE  intf_line2.interface_header_id = intf_headers.interface_header_id
          AND    intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
          AND    intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
          AND    intf_line2.price_break_flag = 'Y'
          AND    intf_line2.processing_id = PO_PDOI_PARAMS.g_processing_id
          AND    intf_line2.interface_header_id = intf_line3.interface_header_id
          AND    intf_line2.po_line_id = intf_line3.po_line_id
          AND    NVL(intf_line3.price_break_flag, 'N') = 'N'
          AND    intf_line3.interface_line_id < intf_line2.interface_line_id
          GROUP BY intf_line2.interface_line_id) v
  WHERE   intf_line1.interface_line_id = v.match_intf_line_id
  AND     NVL(intf_line1.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
            = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED;

Line: 1304

  SELECT v.interface_line_id
  BULK COLLECT INTO l_notified_intf_line_id_tbl
  FROM   po_lines_interface intf_line1,
         (SELECT intf_line2.interface_line_id, max(intf_line3.interface_line_id) AS match_intf_line_id
          FROM   po_lines_interface intf_line2, po_lines_interface intf_line3,
                 po_headers_interface intf_headers
          WHERE  intf_line2.interface_header_id = intf_headers.interface_header_id
          AND    intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
          AND    intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
          AND    intf_line2.price_break_flag = 'Y'
          AND    intf_line2.processing_id = PO_PDOI_PARAMS.g_processing_id
          AND    intf_line2.interface_header_id = intf_line3.interface_header_id
          AND    intf_line2.po_line_id = intf_line3.po_line_id
          AND    NVL(intf_line3.price_break_flag, 'N') = 'N'
          AND    intf_line3.interface_line_id < intf_line2.interface_line_id
          GROUP BY intf_line2.interface_line_id) v
  WHERE   intf_line1.interface_line_id = v.match_intf_line_id
  AND     NVL(intf_line1.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
            = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED;

Line: 1327

    UPDATE po_lines_interface
    SET    process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
    WHERE  interface_line_id = l_notified_intf_line_id_tbl(i);

Line: 1448

  PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_lines
  (
    x_lines    => x_lines
  );

Line: 1456

  PO_PDOI_LINE_PROCESS_PVT.update_line_intf_tbl
  (
    x_lines    => x_lines
  );

Line: 1488

  l_rej_intf_line_id_tbl.DELETE;

Line: 1495

  UPDATE po_lines_all
  SET expiration_date = TRUNC(sysdate - 1)
  WHERE po_line_id = p_expire_line_id_tbl(i);

Line: 1531

PROCEDURE process_update_lines_in_group
(
  p_group_num           IN NUMBER,
  x_lines               IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
) IS

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

Line: 1564

  PO_PDOI_LINE_PROCESS_PVT.derive_lines_for_update
  (
    x_lines    => x_lines
  );

Line: 1570

  PO_PDOI_LINE_PROCESS_PVT.default_lines_for_update
  (
    x_lines    => x_lines
  );

Line: 1578

    p_action   => 'UPDATE',
    x_lines    => x_lines
  );

Line: 1593

  PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.update_lines
  (
    x_lines    => x_lines
  );

Line: 1601

  PO_PDOI_LINE_PROCESS_PVT.update_line_intf_tbl
  (
    x_lines    => x_lines
  );

Line: 1633

  l_rej_intf_line_id_tbl.DELETE;

Line: 1647

END process_update_lines_in_group;

Line: 1769

      l_line_locs.intf_id_index_tbl.DELETE;

Line: 1828

      PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_line_locs
      (
        p_line_locs              => l_line_locs
      );

Line: 1836

      PO_PDOI_LINE_LOC_PROCESS_PVT.update_line_loc_interface
      (
        p_intf_line_loc_id_tbl   => l_line_locs.intf_line_loc_id_tbl,
        p_line_loc_id_tbl        => l_line_locs.line_loc_id_tbl,
        p_error_flag_tbl         => l_line_locs.error_flag_tbl
      );

Line: 1879

      l_rej_intf_line_loc_id_tbl.DELETE;

Line: 1943

    PO_PDOI_LINE_LOC_PROCESS_PVT.update_amount_quantity_on_line
    (
      p_po_line_id_tbl      => l_processed_line_id_tbl,
      p_draft_id_tbl        => l_processed_draft_id_tbl
    );

Line: 1953

    PO_PDOI_LINE_LOC_PROCESS_PVT.delete_exist_price_breaks
    (
      p_po_line_id_tbl      => l_processed_line_id_tbl,
      p_draft_id_tbl        => l_processed_draft_id_tbl
    );

Line: 2108

      PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_dists
      (
        p_dists               => l_dists
      );

Line: 2136

      l_rej_intf_dist_id_tbl.DELETE;

Line: 2142

      UPDATE po_distributions_interface
      SET    process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED
      WHERE  interface_distribution_id = l_dists.intf_dist_id_tbl(i)
      AND    l_dists.error_flag_tbl(i) = FND_API.g_FALSE;

Line: 2332

      PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_price_diffs
      (
        p_price_diffs         => l_price_diffs
      );

Line: 2358

      l_rej_intf_price_diff_id_tbl.DELETE;

Line: 2364

      UPDATE po_price_diff_interface
      SET    process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED
      WHERE  price_diff_interface_id = l_price_diffs.intf_price_diff_id_tbl(i)
      AND    l_price_diffs.error_flag_tbl(i) = FND_API.g_FALSE;

Line: 2644

      UPDATE po_attr_values_interface
      SET    process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED
      WHERE  interface_attr_values_id = l_attr_values.intf_attr_values_id_tbl(i)
      AND    l_attr_values.error_flag_tbl(i) = FND_API.g_FALSE;

Line: 2841

      UPDATE po_attr_values_tlp_interface
      SET    process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED
      WHERE  interface_attr_values_tlp_id = l_attr_values_tlp.intf_attr_values_tlp_id_tbl(i)
      AND    l_attr_values_tlp.error_flag_tbl(i) = FND_API.g_FALSE;

Line: 2988

      l_lines.intf_id_index_tbl.DELETE;

Line: 2998

     x_last_update_date_tbl       => l_lines.last_update_date_tbl,
     x_last_updated_by_tbl        => l_lines.last_updated_by_tbl,
     x_last_update_login_tbl      => l_lines.last_update_login_tbl,
     x_creation_date_tbl          => l_lines.creation_date_tbl,
     x_created_by_tbl             => l_lines.created_by_tbl,
     x_request_id_tbl             => l_lines.request_id_tbl,
     x_program_application_id_tbl => l_lines.program_application_id_tbl,
     x_program_id_tbl             => l_lines.program_id_tbl,
     x_program_update_date_tbl    => l_lines.program_update_date_tbl
   );

Line: 3040

     SELECT po_lines_s.nextval INTO l_po_line_id FROM sys.dual;

Line: 3054

      PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_lines
      (
        x_lines              => l_lines
      );

Line: 3062

      PO_PDOI_LINE_PROCESS_PVT.update_line_intf_tbl
      (
        x_lines               => l_lines
      );

Line: 3099

      l_rej_intf_line_id_tbl.DELETE;

Line: 3172

         SELECT intf_lines.group_line_id, draft_lines.po_line_id,
                draft_lines.draft_id, draft_lines.po_header_id
           FROM po_lines_draft_all draft_lines, po_lines_interface intf_lines,
                po_headers_interface intf_headers
          WHERE intf_lines.group_line_id IS NOT NULL
            AND intf_lines.interface_header_id =
                                     intf_headers.interface_header_id
            AND intf_lines.line_num = draft_lines.line_num
            AND intf_headers.po_header_id = draft_lines.po_header_id
            AND intf_lines.processing_id = PO_PDOI_PARAMS.g_processing_id
            AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
            AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
            AND draft_lines.draft_id = intf_headers.draft_id;

Line: 3193

         UPDATE po_lines_draft_all
              SET group_line_id =
                     (SELECT pld.po_line_id
                        FROM po_lines_interface pli, po_lines_draft_all pld
                       WHERE 1 = 1
                         AND pld.po_header_id = l_rec.po_header_id
                         AND pld.group_line_id IS NULL
                         AND pld.line_num = pli.line_num
                         AND pli.interface_line_id = l_rec.group_line_id
                         AND Nvl(pld.draft_id,l_rec.draft_id) = l_rec.draft_id)
            WHERE po_line_id = l_rec.po_line_id
              AND Nvl(draft_id,l_rec.draft_id)   = l_rec.draft_id;

Line: 3238

  SELECT clm_base_line_num,
         po_line_id
  BULK COLLECT INTO l_clm_base_line_num_tbl, l_po_line_id_tbl
  FROM po_lines_interface
  WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
  AND   Nvl(clm_option_indicator,'*') = 'O';

Line: 3251

    UPDATE po_lines_draft_all
    SET  clm_base_line_num = ( SELECT po_line_id FROM po_lines_interface
                               WHERE  interface_line_id = l_clm_base_line_num_tbl(i)
                             )
    WHERE l_clm_base_line_num_tbl(i) IS NOT NULL
    AND   po_line_id = l_po_line_id_tbl(i);