APPS.PO_CATALOG_INDEX_PVT SQL Statements

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

Line: 31

PROCEDURE insert_header_changes
(
  p_key IN NUMBER
, p_key_remaining_headers IN NUMBER
);

Line: 37

PROCEDURE insert_line_changes
(
  p_key IN NUMBER
, p_key_remaining_headers IN NUMBER
);

Line: 43

PROCEDURE insert_attr_changes
(
  p_key IN NUMBER
, p_key_remaining_headers IN NUMBER
);

Line: 49

PROCEDURE insert_tlp_changes
(
  p_key IN NUMBER
, p_key_remaining_headers IN NUMBER
);

Line: 55

PROCEDURE delete_processed_headers
(
  p_key IN NUMBER
, p_key_remaining_headers IN NUMBER
);

Line: 314

  SELECT PO_SESSION_GT_S.nextval
  INTO l_key
  FROM DUAL;

Line: 320

  INSERT INTO PO_SESSION_GT
             (
               key
             , index_num1 -- PO_LINE_ID (for Quotation Line): Required by iProc
             , index_num2 -- PO_HEADER_ID (for Quotation Header): Internal to PO
             , char5      -- DATA INFO: Internal to PO
             )
  SELECT l_key
       , po_line_id       -- PO_LINE_ID (for Quotation Line): Required by iProc
       , po_header_id     -- PO_HEADER_ID (for Quotation Header): Internal to PO
       , 'QUOTATION'      -- DATA INFO: Internal to PO
  FROM   PO_LINES_ALL
  WHERE  po_header_id = p_po_header_id;

Line: 337

  IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||SQL%rowcount); END IF;

Line: 349

      SELECT segment1
      INTO l_segment1
      FROM PO_HEADERS_ALL
      WHERE po_header_id = p_po_header_id;

Line: 439

  SELECT PO_SESSION_GT_S.nextval
  INTO l_key
  FROM DUAL;

Line: 445

  INSERT INTO PO_SESSION_GT
             (
               key
             , index_char1   -- ReqTemplate Name
             , index_num1    -- Reqtemplate Line Num
             , index_num2    -- Org Id
             , char5         -- DATA INFO: Internal to PO
             )
  SELECT l_key
       , p_reqexpress_name   -- ReqTemplate Name
       , sequence_num        -- Reqtemplate Line Num
       , org_id              -- Org Id
       , 'REQ_TEMPLATE'      -- DATA INFO: Internal to PO
    FROM PO_REQEXPRESS_LINES_ALL
   WHERE express_name = p_reqexpress_name
     AND org_id = p_org_id;

Line: 465

  IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||SQL%rowcount); END IF;

Line: 567

  SELECT PO_SESSION_GT_S.nextval
  INTO l_key
  FROM DUAL;

Line: 575

  SELECT PO_SESSION_GT_S.nextval
  INTO l_key_remaining_headers
  FROM DUAL;

Line: 582

    INSERT INTO PO_SESSION_GT
    (
      key                       -- Key: Internal to PO
    , index_num1                -- List of Input PO_HEADER_ID's
    , char5                     -- DATA INFO: Internal to PO
    )
    VALUES
    (
      l_key_remaining_headers   -- Key: Internal to PO
    , p_po_header_ids(i)        -- List of Input PO_HEADER_ID's
    , 'Remaining PO_HEADER_IDs' -- DATA INFO: Internal to PO
    );

Line: 595

  IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or PO_HEADER_IDs inserted into GT table='||SQL%rowcount); END IF;

Line: 600

  insert_header_changes
  (
    p_key => l_key
  , p_key_remaining_headers => l_key_remaining_headers
  );

Line: 612

  delete_processed_headers
  (
    p_key => l_key
  , p_key_remaining_headers => l_key_remaining_headers
  );

Line: 622

  insert_line_changes
  (
    p_key => l_key
  , p_key_remaining_headers => l_key_remaining_headers
  );

Line: 630

  delete_processed_headers
  (
    p_key => l_key
  , p_key_remaining_headers => l_key_remaining_headers
  );

Line: 641

  insert_attr_changes
  (
    p_key => l_key
  , p_key_remaining_headers => l_key_remaining_headers
  );

Line: 649

  delete_processed_headers
  (
    p_key => l_key
  , p_key_remaining_headers => l_key_remaining_headers
  );

Line: 659

  insert_tlp_changes
  (
    p_key => l_key
  , p_key_remaining_headers => l_key_remaining_headers
  );

Line: 668

  SELECT count(*)
  INTO l_num_rows_is_gt
  FROM PO_SESSION_GT
  WHERE key = l_key;

Line: 673

  IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||l_num_rows_is_gt); END IF;

Line: 757

PROCEDURE insert_header_changes
(
  p_key IN NUMBER
, p_key_remaining_headers IN NUMBER
)
IS
  l_api_name      CONSTANT VARCHAR2(30) := 'insert_header_changes';

Line: 775

  INSERT INTO PO_SESSION_GT
  (
    key
  , index_num1       -- PO_LINE_ID
  , index_char1      -- Line Changed Flag
  , index_char2      -- Attr Changed Flag
  , char1            -- TLP Changed Flag
  , char2            -- Language
  , char3            -- Global Agreement Flag
  , index_num2       -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
  , char5            -- DATA INFO: Internal to PO
  )
  SELECT
    p_key
  , POL.po_line_id   -- PO_LINE_ID
  , 'Y'              -- Line Changed Flag
  , NULL             -- Attr Changed Flag: n/a if line_changed_flag is Y
  , NULL             -- TLP Changed Flag: n/a if line_changed_flag is Y
  , NULL             -- Language: n/a if line_changed_flag is Y
  , POH.global_agreement_flag -- Global Agreement Flag
  , POH.po_header_id -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
  , 'BLANKET:HEADER' -- DATA INFO: Internal to PO
  FROM  PO_LINES_ALL POL
      , PO_HEADERS_ALL POH
      , PO_SESSION_GT GT_REMAINING_HDRS
  WHERE GT_REMAINING_HDRS.key = p_key_remaining_headers
    AND POH.po_header_id = GT_REMAINING_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
    AND POL.po_header_id = POH.po_header_id;

Line: 807

             (SELECT 'Headers were archived'
                FROM PO_HEADERS_ARCHIVE_ALL POHA
               WHERE POHA.po_header_id = POH.po_header_id)
          OR
            EXISTS
             (SELECT 'Some attribute is modified'
                FROM PO_HEADERS_ARCHIVE_ALL POHA
               WHERE POHA.po_header_id = POH.po_header_id
                 AND POHA.latest_external_flag = 'Y'
                 AND (POH.vendor_id <> POHA.vendor_id OR
                      (POH.vendor_id IS NULL AND POHA.vendor_id IS NOT NULL) OR
                      (POH.vendor_id IS NOT NULL AND POHA.vendor_id IS NULL) OR
                      POH.vendor_site_id <> POHA.vendor_site_id OR
                      (POH.vendor_site_id IS NULL AND POHA.vendor_site_id IS NOT NULL) OR
                      (POH.vendor_site_id IS NOT NULL AND POHA.vendor_site_id IS NULL))));

Line: 824

  IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||SQL%rowcount); END IF;

Line: 830

END insert_header_changes;

Line: 865

PROCEDURE insert_line_changes
(
  p_key IN NUMBER
, p_key_remaining_headers IN NUMBER
)
IS
  l_api_name      CONSTANT VARCHAR2(30) := 'insert_line_changes';

Line: 884

  INSERT INTO PO_SESSION_GT
  (
    key
  , index_num1       -- PO_LINE_ID
  , index_char1      -- Line Changed Flag
  , index_char2      -- Attr Changed Flag
  , char1            -- TLP Changed Flag
  , char2            -- Language
  , char3            -- Global Agreement Flag
  , index_num2       -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
  , char5            -- DATA INFO: Internal to PO
  )
  SELECT
    p_key
  , POL.po_line_id   -- PO_LINE_ID
  , 'Y'              -- Line Changed Flag
  , NULL             -- Attr Changed Flag: n/a if line_changed_flag is Y
  , NULL             -- TLP Changed Flag: n/a if line_changed_flag is Y
  , NULL             -- Language: n/a if line_changed_flag is Y
  , POH.global_agreement_flag -- Global Agreement Flag
  , POH.po_header_id -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
  , 'BLANKET:LINE'   -- DATA INFO: Internal to PO
  FROM  PO_LINES_ALL POL
      , PO_HEADERS_ALL POH
      , PO_SESSION_GT GT_REMAINING_HDRS
  WHERE GT_REMAINING_HDRS.key = p_key_remaining_headers
    AND POH.po_header_id = GT_REMAINING_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
    AND POL.po_header_id = POH.po_header_id;

Line: 916

             (SELECT 'Lines were archived'
                FROM PO_LINES_ARCHIVE_ALL POLA
               WHERE POLA.po_line_id = POL.po_line_id)
          OR
            EXISTS
             (SELECT 'Some attribute is modified'
                FROM PO_LINES_ARCHIVE_ALL POLA
               WHERE POLA.po_line_id = POL.po_line_id
                 AND POLA.latest_external_flag = 'Y'
                 AND (POL.ip_category_id <> POLA.ip_category_id OR
                      (POL.ip_category_id IS NULL AND POLA.ip_category_id IS NOT NULL) OR
                      (POL.ip_category_id IS NOT NULL AND POLA.ip_category_id IS NULL) OR
                      POL.category_id <> POLA.category_id OR
                      (POL.category_id IS NULL AND POLA.category_id IS NOT NULL) OR
                      (POL.category_id IS NOT NULL AND POLA.category_id IS NULL) OR
                      POL.supplier_part_auxid <> POLA.supplier_part_auxid OR
                      (POL.supplier_part_auxid IS NULL AND POLA.supplier_part_auxid IS NOT NULL) OR
                      (POL.supplier_part_auxid IS NOT NULL AND POLA.supplier_part_auxid IS NULL) OR
                      POL.item_id <> POLA.item_id OR
                      (POL.item_id IS NULL AND POLA.item_id IS NOT NULL) OR
                      (POL.item_id IS NOT NULL AND POLA.item_id IS NULL) OR
                      POL.item_revision <> POLA.item_revision OR
                      (POL.item_revision IS NULL AND POLA.item_revision IS NOT NULL) OR
                      (POL.item_revision IS NOT NULL AND POLA.item_revision IS NULL) OR
                      POL.item_description <> POLA.item_description OR
                      (POL.item_description IS NULL AND POLA.item_description IS NOT NULL) OR
                      (POL.item_description IS NOT NULL AND POLA.item_description IS NULL) OR
                      POL.vendor_product_num <> POLA.vendor_product_num OR
                      (POL.vendor_product_num IS NULL AND POLA.vendor_product_num IS NOT NULL) OR
                      (POL.vendor_product_num IS NOT NULL AND POLA.vendor_product_num IS NULL))));

Line: 948

  IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||SQL%rowcount); END IF;

Line: 954

END insert_line_changes;

Line: 985

PROCEDURE insert_attr_changes
(
  p_key IN NUMBER
, p_key_remaining_headers IN NUMBER
)
IS
  l_api_name      CONSTANT VARCHAR2(30) := 'insert_attr_changes';

Line: 1001

  INSERT INTO PO_SESSION_GT
  (
    key
  , index_num1       -- PO_LINE_ID
  , index_char1      -- Line Changed Flag
  , index_char2      -- Attr Changed Flag
  , char1            -- TLP Changed Flag
  , char2            -- Language
  , char3            -- Global Agreement Flag
  , index_num2       -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
  , char5            -- DATA INFO: Internal to PO
  )
  SELECT
    p_key
  , POL.po_line_id   -- PO_LINE_ID
  , 'N'              -- Line Changed Flag
  , 'Y'              -- Attr Changed Flag
  , NULL             -- TLP Changed Flag: n/a if line_changed_flag is Y
  , NULL             -- Language: n/a if line_changed_flag is Y
  , POH.global_agreement_flag -- Global Agreement Flag
  , POH.po_header_id -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
  , 'BLANKET:ATTR'   -- DATA INFO: Internal to PO
  FROM  PO_LINES_ALL POL
      , PO_HEADERS_ALL POH
      , PO_ATTRIBUTE_VALUES POATR
      , PO_SESSION_GT GT_REMAINING_HDRS
  WHERE GT_REMAINING_HDRS.key = p_key_remaining_headers
    AND POH.po_header_id = GT_REMAINING_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
    AND POL.po_header_id = POH.po_header_id
    AND POATR.po_line_id = POL.po_line_id
    AND POATR.rebuild_search_index_flag = 'Y';

Line: 1033

  IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||SQL%rowcount); END IF;

Line: 1039

END insert_attr_changes;

Line: 1070

PROCEDURE insert_tlp_changes
(
  p_key IN NUMBER
, p_key_remaining_headers IN NUMBER
)
IS
  l_api_name      CONSTANT VARCHAR2(30) := 'insert_tlp_changes';

Line: 1086

  INSERT INTO PO_SESSION_GT
  (
    key
  , index_num1       -- PO_LINE_ID
  , index_char1      -- Line Changed Flag
  , index_char2      -- Attr Changed Flag
  , char1            -- TLP Changed Flag
  , char2            -- Language
  , char3            -- Global Agreement Flag
  , index_num2       -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
  , char5            -- DATA INFO: Internal to PO
  )
  SELECT
    p_key
  , POL.po_line_id   -- PO_LINE_ID
  , 'N'              -- Line Changed Flag
  , 'N'              -- Attr Changed Flag
  , 'Y'              -- TLP Changed Flag
  , POTLP.language   -- Language
  , POH.global_agreement_flag -- Global Agreement Flag
  , POH.po_header_id -- PO_HEADER_ID (Internal to PO Dev, not required by iProc)
  , 'BLANKET:TLP'    -- DATA INFO: Internal to PO
  FROM  PO_LINES_ALL POL
      , PO_HEADERS_ALL POH
      , PO_ATTRIBUTE_VALUES_TLP POTLP
      , PO_SESSION_GT GT_REMAINING_HDRS
  WHERE GT_REMAINING_HDRS.key = p_key_remaining_headers
    AND POH.po_header_id = GT_REMAINING_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
    AND POL.po_header_id = POH.po_header_id
    AND POTLP.po_line_id = POL.po_line_id
    AND POTLP.rebuild_search_index_flag = 'Y';

Line: 1118

  IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or recs inserted into GT table='||SQL%rowcount); END IF;

Line: 1124

END insert_tlp_changes;

Line: 1155

PROCEDURE delete_processed_headers
(
  p_key IN NUMBER
, p_key_remaining_headers IN NUMBER
)
IS
  l_api_name      CONSTANT VARCHAR2(30) := 'delete_processed_headers';

Line: 1172

  DELETE FROM PO_SESSION_GT GT_REMAINING_HDRS
  WHERE GT_REMAINING_HDRS.key = p_key_remaining_headers
  AND EXISTS
       (SELECT 'Header is already present in the to-be-processed queue in GT table'
        FROM PO_SESSION_GT GT1
        WHERE GT1.key = p_key
          AND GT1.index_num2 = GT_REMAINING_HDRS.index_num1);

Line: 1180

  IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or PO_HEADER_IDs deleted='||SQL%rowcount); END IF;

Line: 1186

END delete_processed_headers;

Line: 1240

  SELECT PO_SESSION_GT_S.nextval
  INTO l_key_org_assignments
  FROM DUAL;

Line: 1245

  SELECT PO_SESSION_GT_S.nextval
  INTO l_key_input_headers
  FROM DUAL;

Line: 1251

    INSERT INTO PO_SESSION_GT
    (
      key
    , index_num1       -- PO_HEADER_IDs to be processed
    , char5            -- DATA INFO: Internal to PO
    )
    VALUES
    (
      l_key_input_headers
    , p_po_header_ids(i)                    -- PO_HEADER_IDs to be processed
    , 'ORG_ASSIGNMENT:Input PO_HEADER_IDs'  -- DATA INFO: Internal to PO
    );

Line: 1264

  IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or input PO_HEADER_IDs inserted into GT table='||SQL%rowcount); END IF;

Line: 1268

  INSERT INTO PO_SESSION_GT
  (
    key
  , index_num1               -- PO_HEADER_ID
  , index_num2               -- ORG_ASSIGNMENT_ID
  , index_char1              -- Enabled/Disabled changed flag
  , index_char2              -- Other fields Changed Flag (Purc Org, Purch Site)
  , char5                    -- DATA INFO: Internal to PO
  )
  SELECT
    l_key_org_assignments
  , POH.po_header_id         -- PO_HEADER_ID
  , POGA.org_assignment_id   -- ORG_ASSIGNMENT_ID
  , 'Y'                      -- Enabled/Disabled changed flag
  , 'Y'                      -- Other fields Changed Flag (Purc Org, Purch Site)
  , 'BLANKET:ORG_ASSIGNMENT' -- DATA INFO: Internal to PO
  FROM  PO_HEADERS_ALL POH
      , PO_GA_ORG_ASSIGNMENTS POGA
      , PO_SESSION_GT GT_INPUT_HDRS
  WHERE GT_INPUT_HDRS.key = l_key_input_headers
    AND POH.po_header_id = GT_INPUT_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
    AND POH.global_agreement_flag = 'Y'
    AND POGA.po_header_id = POH.po_header_id
    AND ( NOT EXISTS
             (SELECT 'Headers were archived'
                FROM PO_GA_ORG_ASSIGNMENTS_ARCHIVE ARCH
               WHERE ARCH.org_assignment_id = POGA.org_assignment_id))
  UNION ALL
  SELECT
    l_key_org_assignments
  , POH.po_header_id         -- PO_HEADER_ID
  , POGA.org_assignment_id   -- ORG_ASSIGNMENT_ID
  , 'Y'                      -- Enabled/Disabled changed flag
  , 'N'                      -- Other fields Changed Flag (Purc Org, Purch Site)
  , 'BLANKET:ORG_ASSIGNMENT' -- DATA INFO: Internal to PO
  FROM  PO_HEADERS_ALL POH
      , PO_GA_ORG_ASSIGNMENTS POGA
      , PO_SESSION_GT GT_INPUT_HDRS
  WHERE GT_INPUT_HDRS.key = l_key_input_headers
    AND POH.po_header_id = GT_INPUT_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
    AND POH.global_agreement_flag = 'Y'
    AND POGA.po_header_id = POH.po_header_id
    AND EXISTS
             (SELECT 'Only Enabled/disabled flag is modified'
                FROM PO_GA_ORG_ASSIGNMENTS_ARCHIVE ARCH
               WHERE ARCH.org_assignment_id = POGA.org_assignment_id
                 AND ARCH.latest_external_flag = 'Y'
                 AND ARCH.enabled_flag <> POGA.enabled_flag
                 AND ARCH.purchasing_org_id = POGA.purchasing_org_id
                 AND ARCH.vendor_site_id = POGA.vendor_site_id)
  UNION ALL
  SELECT
    l_key_org_assignments
  , POH.po_header_id         -- PO_HEADER_ID
  , POGA.org_assignment_id   -- ORG_ASSIGNMENT_ID
  , 'N'                      -- Enabled/Disabled changed flag
  , 'Y'                      -- Other fields Changed Flag (Purc Org, Purch Site)
  , 'BLANKET:ORG_ASSIGNMENT' -- DATA INFO: Internal to PO
  FROM  PO_HEADERS_ALL POH
      , PO_GA_ORG_ASSIGNMENTS POGA
      , PO_SESSION_GT GT_INPUT_HDRS
  WHERE GT_INPUT_HDRS.key = l_key_input_headers
    AND POH.po_header_id = GT_INPUT_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
    AND POH.global_agreement_flag = 'Y'
    AND POGA.po_header_id = POH.po_header_id
    AND EXISTS
             (SELECT 'Only Purch Org/Purch Site is modified'
                FROM PO_GA_ORG_ASSIGNMENTS_ARCHIVE ARCH
               WHERE ARCH.org_assignment_id = POGA.org_assignment_id
                 AND ARCH.latest_external_flag = 'Y'
                 AND (ARCH.purchasing_org_id <> POGA.purchasing_org_id OR
                      ARCH.vendor_site_id <> POGA.vendor_site_id)
                 AND ARCH.enabled_flag = POGA.enabled_flag)
  UNION ALL
  SELECT
    l_key_org_assignments
  , POH.po_header_id         -- PO_HEADER_ID
  , POGA.org_assignment_id   -- ORG_ASSIGNMENT_ID
  , 'Y'                      -- Enabled/Disabled changed flag
  , 'Y'                      -- Other fields Changed Flag (Purc Org, Purch Site)
  , 'BLANKET:ORG_ASSIGNMENT' -- DATA INFO: Internal to PO
  FROM  PO_HEADERS_ALL POH
      , PO_GA_ORG_ASSIGNMENTS POGA
      , PO_SESSION_GT GT_INPUT_HDRS
  WHERE GT_INPUT_HDRS.key = l_key_input_headers
    AND POH.po_header_id = GT_INPUT_HDRS.index_num1 -- index_num1 stores the PO_HEADER_ID
    AND POH.global_agreement_flag = 'Y'
    AND POGA.po_header_id = POH.po_header_id
    AND EXISTS
             (SELECT 'Both enable_flag AND Purch Org/Purch Site are modified'
                FROM PO_GA_ORG_ASSIGNMENTS_ARCHIVE ARCH
               WHERE ARCH.org_assignment_id = POGA.org_assignment_id
                 AND ARCH.latest_external_flag = 'Y'
                 AND (ARCH.purchasing_org_id <> POGA.purchasing_org_id OR
                      ARCH.vendor_site_id <> POGA.vendor_site_id)
                 AND ARCH.enabled_flag <> POGA.enabled_flag);

Line: 1367

  IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number of recs inserted into GT table='||l_num_rows_is_gt); END IF;

Line: 1478

    SELECT PO_SESSION_GT_S.nextval
    INTO l_key
    FROM DUAL;

Line: 1486

      INSERT INTO PO_SESSION_GT(key, index_num1) -- PO_HEADER_ID
      VALUES (l_key, p_po_header_id);

Line: 1494

        INSERT INTO PO_SESSION_GT(key, index_num1) -- PO_HEADER_ID
        VALUES (l_key, p_po_header_ids(i));

Line: 1501

    SELECT POL.po_line_id,
           POL.item_description,
           POH.created_language
    BULK COLLECT INTO
           l_po_line_id_list,
           l_item_description_list,
           l_created_lang_list
      FROM PO_LINES_ALL POL,
           PO_HEADERS_ALL POH,
           PO_SESSION_GT INPUT_HDRS
     WHERE POH.po_header_id = INPUT_HDRS.index_num1
       AND INPUT_HDRS.key = l_key /* Bug 6942699 - Added the condition to improve performance */
       AND POL.po_header_id = POH.po_header_id
       AND (NOT EXISTS
               (SELECT 'Lines were archived'
                  FROM PO_LINES_ARCHIVE_ALL POLA
                 WHERE POLA.po_line_id = POL.po_line_id)
           OR EXISTS
           (SELECT 'Item description has been modified'
              FROM PO_LINES_ARCHIVE_ALL POLA
             WHERE POLA.po_line_id = POL.po_line_id
               AND POLA.latest_external_flag = 'Y'
               AND (POL.item_description <> POLA.item_description OR
                    (POL.item_description IS NULL AND POLA.item_description IS NOT NULL) OR
                    (POL.item_description IS NOT NULL AND POLA.item_description IS NULL))));

Line: 1527

    IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or PO_LINE descriptions selected to synch='||SQL%rowcount); END IF;

Line: 1532

      UPDATE PO_ATTRIBUTE_VALUES_TLP POTLP
         SET description = l_item_description_list(i)
       WHERE POTLP.po_line_id = l_po_line_id_list(i)
         AND language = l_created_lang_list(i);

Line: 1543

    SELECT PORTL.express_name,
           PORTL.sequence_num,
           PORTL.org_id,
           PORTL.item_description
    BULK COLLECT INTO
           l_req_template_name_list,
           l_req_template_line_num_list,
           l_req_template_org_id_list,
           l_item_description_list
      FROM PO_REQEXPRESS_LINES_ALL PORTL
     WHERE PORTL.express_name = p_reqexpress_name
       AND PORTL.org_id = p_org_id;

Line: 1556

    IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or REQ_TEMPLATE line descriptions selected to synch='||SQL%rowcount); END IF;

Line: 1560

    SELECT language_code
    INTO l_base_lang
    FROM FND_LANGUAGES
    WHERE installed_flag='B';

Line: 1570

      UPDATE PO_ATTRIBUTE_VALUES_TLP POTLP
         SET description = l_item_description_list(i)
       WHERE POTLP.req_template_name = l_req_template_name_list(i)
         AND req_template_line_num = l_req_template_line_num_list(i)
         AND org_id = l_req_template_org_id_list(i)
         AND language = l_base_lang;

Line: 1656

    SELECT PO_SESSION_GT_S.nextval
    INTO l_key
    FROM DUAL;

Line: 1664

      INSERT INTO PO_SESSION_GT(key, index_num1) -- PO_HEADER_ID
      VALUES (l_key, p_po_header_id);

Line: 1672

        INSERT INTO PO_SESSION_GT(key, index_num1) -- PO_HEADER_ID
        VALUES (l_key, p_po_header_ids(i));

Line: 1679

    SELECT POL.po_line_id,
           POL.category_id,
           POATR.ip_category_id
    BULK COLLECT INTO
           l_po_line_id_list,
           l_category_id_list,
           l_old_ip_category_id_list
      FROM PO_LINES_ALL POL,
           PO_HEADERS_ALL POH,
           PO_ATTRIBUTE_VALUES POATR,
           PO_SESSION_GT INPUT_HDRS
     WHERE POH.po_header_id = INPUT_HDRS.index_num1
       AND INPUT_HDRS.key = l_key
       AND POL.po_header_id = POH.po_header_id
       AND POL.po_line_id = POATR.po_line_id
       AND POL.category_id IS NOT NULL;

Line: 1696

    IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or PO_LINE category ids selected to synch='||SQL%rowcount); END IF;

Line: 1706

          UPDATE PO_ATTRIBUTE_VALUES
             SET ip_category_id = l_new_ip_category_id
           WHERE po_line_id = l_po_line_id_list(i);

Line: 1710

          UPDATE PO_ATTRIBUTE_VALUES_TLP
             SET ip_category_id = l_new_ip_category_id
           WHERE po_line_id = l_po_line_id_list(i);

Line: 1722

    SELECT PORTL.express_name,
           PORTL.sequence_num,
           PORTL.org_id,
           PORTL.category_id,
           POATR.ip_category_id
    BULK COLLECT INTO
           l_req_template_name_list,
           l_req_template_line_num_list,
           l_req_template_org_id_list,
           l_category_id_list,
           l_old_ip_category_id_list
      FROM PO_REQEXPRESS_LINES_ALL PORTL,
           PO_ATTRIBUTE_VALUES POATR
     WHERE PORTL.express_name = p_reqexpress_name
       AND PORTL.po_line_id = POATR.po_line_id
       AND PORTL.org_id = p_org_id;

Line: 1739

    IF g_debug_stmt THEN PO_DEBUG.debug_stmt(l_log_head,l_progress,'Number or REQ_TEMPLATE line category ids selected to synch='||SQL%rowcount); END IF;

Line: 1750

          UPDATE PO_ATTRIBUTE_VALUES
             SET ip_category_id = l_new_ip_category_id
           WHERE req_template_name = l_req_template_name_list(i)
             AND req_template_line_num = l_req_template_line_num_list(i)
             AND org_id = l_req_template_org_id_list(i);

Line: 1756

          UPDATE PO_ATTRIBUTE_VALUES_TLP
             SET ip_category_id = l_new_ip_category_id
           WHERE req_template_name = l_req_template_name_list(i)
             AND req_template_line_num = l_req_template_line_num_list(i)
             AND org_id = l_req_template_org_id_list(i);