APPS.PO_R12_CAT_UPG_EXISTING_DOCS SQL Statements

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

Line: 25

gUpdatedAttribute             DBMS_SQL.VARCHAR2_TABLE;

Line: 35

g_R12_MIGRATION_PROGRAM PO_HEADERS_ALL.last_updated_program%TYPE
                 := PO_R12_CAT_UPG_PVT.g_R12_MIGRATION_PROGRAM;

Line: 95

    SELECT POL.po_line_id,  -- PoLineId
           TO_CHAR(NULL_ID), NULL_ID, -- TemplateName, TemplateId
           NVL(ICXM.SHOPPING_CATEGORY_ID, NULL_ID), -- iP Category Id
           NVL(POL.item_id, NULL_ID), POL.org_id, POL.item_description,
           POL.attribute13, -- Image
           POL.attribute14, -- Image URL
           DECODE( NVL(icxm.shopping_category_id, NULL_ID), TLP.ip_category_id,
                   decode(POL.item_description, TLP.description,
                   decode(POL.item_id, TLP.inventory_item_id,
                   NULL, 'ITEM_ID'), 'DESCRIPTION'), 'IP_CATEGORY_ID'), -- Attribute
           DECODE( NVL(icxm.shopping_category_id, NULL_ID), TLP.ip_category_id,
                   decode(POL.item_description, TLP.description,
                   decode(POL.item_id, TLP.inventory_item_id,
                   'N', 'Y'), 'Y'), 'Y'), -- Recreate Attribute
           DECODE( NVL(icxm.shopping_category_id, NULL_ID), TLP.ip_category_id,
                   decode(POL.item_description, TLP.description,
                   decode(POL.item_id, TLP.inventory_item_id,
                   'N', 'Y'), 'Y'), 'Y') -- Recreate Attribute TLP
    FROM PO_LINES_ALL POL, PO_ATTRIBUTE_VALUES_TLP TLP,
         ICX_CAT_PURCHASING_CAT_MAP_V ICXM
    WHERE POL.last_updated_program = g_R12_MIGRATION_PROGRAM
      AND POL.po_line_id = TLP.po_line_id
      AND POL.CATEGORY_ID = ICXM.po_category_id(+)
      AND TLP.language = p_base_lang
      AND (POL.item_description <> TLP.description
           OR NVL(POL.item_id, NULL_ID) <> TLP.inventory_item_id)
      AND POL.po_line_id between p_start_id and p_end_id --Bug#5156673
    UNION ALL
    SELECT POL.po_line_id,  -- PoLineId
           TO_CHAR(NULL_ID), NULL_ID, -- TemplateName, TemplateId
           POL.ip_category_id, NVL(POL.item_id, NULL_ID), POL.org_id, POL.item_description,
           POL.attribute13, -- Image
           POL.attribute14, -- Image URL
           'ITEM_TRANSLATION', 'N','Y' -- Attribute, Recreate Attrib, Recreate Attrib TLP
    FROM PO_LINES_ALL POL, MTL_SYSTEM_ITEMS_TL MTL,
         FINANCIALS_SYSTEM_PARAMS_ALL FSP
    WHERE POL.last_updated_program = g_R12_MIGRATION_PROGRAM
      AND POL.item_id IS NOT NULL
      AND POL.item_id = MTL.inventory_item_id
      AND POL.org_id  = FSP.org_id
      AND FSP.inventory_organization_id = MTL.organization_id
      AND MTL.language = MTL.source_lang
      AND NOT EXISTS
      (
          SELECT 'Upgraded Lines with newly added item master translations'
          FROM   PO_ATTRIBUTE_VALUES_TLP POATLP
          WHERE POATLP.po_line_id <> NULL_ID
            AND POATLP.po_line_id = POL.po_line_id
            AND POATLP.org_id = POL.org_id
            AND POATLP.language = MTL.language
      )
      AND POL.po_line_id between p_start_id and p_end_id --Bug#5156673
    UNION ALL
    SELECT po_line_id,  -- PoLineId
           TO_CHAR(NULL_ID), NULL_ID, -- TemplateName, TemplateId
           ip_category_id, nvl(inventory_item_id, NULL_ID), org_id, null, -- no need to get description(null)
           NULL, -- Image
           NULL, -- Image URL
           'LINE_DELETED', 'N', 'N' -- Attribute, Recreate Attrib, Recreate Attrib TLP
    FROM PO_ATTRIBUTE_VALUES POAT
    WHERE po_line_id <> NULL_ID --Bug#4865650
    AND NOT EXISTS ( SELECT 'PO Line deleted after pre-upgrade'
                       FROM PO_LINES_ALL POL
                       WHERE POL.po_line_id = POAT.po_line_id )
    AND POAT.po_line_id between p_start_id and p_end_id ;--Bug#5156673

Line: 177

  SELECT count(*)
  INTO l_was_R12_upg_ever_run_before
  FROM po_lines_all pol
  WHERE pol.last_updated_program = g_R12_MIGRATION_PROGRAM
    AND POL.po_line_id between p_start_id and p_end_id
    AND rownum=1;

Line: 199

      IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Modified Lines; gPoLineIds.DELETE'); END IF;

Line: 200

      gPoLineIds.DELETE;

Line: 201

      gIpCategoryIds.DELETE;

Line: 202

      gInvItemIds.DELETE;

Line: 203

      gPoOrgIds.DELETE;

Line: 204

      gItemDescriptions.DELETE;

Line: 205

      gImages.DELETE;

Line: 206

      gImageUrls.DELETE;

Line: 207

      gUpdatedAttribute.DELETE;

Line: 208

      gRecreateAttribRow.DELETE;

Line: 209

      gRecreateAttribTLPRow.DELETE;

Line: 210

      gPoReqTemplateNames.DELETE;

Line: 211

      gPoReqTemplateLineIds.DELETE;

Line: 230

                        gUpdatedAttribute,
                        gRecreateAttribRow, gRecreateAttribTLPRow;

Line: 253

      UPDATE PO_LINES_ALL  POL
      SET ip_category_id = gIpCategoryIds(i),
          last_updated_program = g_R12_MIGRATION_PROGRAM
      WHERE po_line_id = gPoLineIds(i)
        AND gUpdatedAttribute(i) = 'IP_CATEGORY_ID';

Line: 261

    IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_LINES_ALL rows updated='||x_rows_processed); END IF;

Line: 272

      DELETE FROM PO_ATTRIBUTE_VALUES POAT
      WHERE po_line_id = gPoLineIds(i)
        AND (gRecreateAttribRow(i) = 'Y' OR gUpdatedAttribute(i) = 'LINE_DELETED');

Line: 276

    IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES rows deleted='||SQL%rowcount); END IF;

Line: 287

      DELETE FROM PO_ATTRIBUTE_VALUES_TLP POATLP
      WHERE po_line_id = gPoLineIds(i)
        AND (gRecreateAttribTLPRow(i) = 'Y' OR  gUpdatedAttribute(i) = 'LINE_DELETED');

Line: 292

    IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows deleted='||SQL%rowcount); END IF;

Line: 301

      UPDATE PO_ATTRIBUTE_VALUES_TLP
      SET description = gItemDescriptions(i),
          last_updated_program = g_R12_MIGRATION_PROGRAM
      WHERE po_line_id = gPoLineIds(i)
        AND gUpdatedAttribute(i) = 'DESCRIPTION';

Line: 307

    IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows updated='||SQL%rowcount); END IF;

Line: 367

    SELECT po_line_id,       -- po_line_id
           to_char(NULL_ID), -- TemplateName
           NULL_ID,          -- TemplateId
           NVL(ICXM.SHOPPING_CATEGORY_ID, NULL_ID),
           NVL(item_id, NULL_ID),
           pol.org_id,
           item_description,
           POL.attribute13,  -- Image
           POL.attribute14,  -- ImageUrl
           NULL,             -- Attribute
           'Y',              -- Recreate Attrib Flag
           'Y'               -- Recreate Attrib TLP Flag
    FROM PO_LINES_ALL POL,
         PO_HEADERS_ALL POH,
         ICX_CAT_PURCHASING_CAT_MAP_V ICXM
    WHERE POL.last_updated_program IS NULL
      AND POL.po_header_id = POH.po_header_id
      AND POH.type_lookup_code IN ('BLANKET', 'QUOTATION')
      AND POL.CATEGORY_ID = ICXM.po_category_id(+)
      AND POL.po_line_id between p_start_id and p_end_id ;--Bug#5156673

Line: 403

      IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'New Lines; gPoLineIds.DELETE'); END IF;

Line: 404

      gPoLineIds.DELETE;

Line: 405

      gIpCategoryIds.DELETE;

Line: 406

      gInvItemIds.DELETE;

Line: 407

      gPoOrgIds.DELETE;

Line: 408

      gItemDescriptions.DELETE;

Line: 409

      gImages.DELETE;

Line: 410

      gImageUrls.DELETE;

Line: 411

      gUpdatedAttribute.DELETE;

Line: 412

      gRecreateAttribRow.DELETE;

Line: 413

      gRecreateAttribTLPRow.DELETE;

Line: 414

      gPoReqTemplateNames.DELETE;

Line: 415

      gPoReqTemplateLineIds.DELETE;

Line: 430

                      gUpdatedAttribute,
                      gRecreateAttribRow, gRecreateAttribTLPRow;

Line: 454

      UPDATE PO_LINES_ALL  POL
      SET ip_category_id = gIpCategoryIds(i),
          last_updated_program = g_R12_MIGRATION_PROGRAM
      WHERE po_line_id = gPoLineIds(i);

Line: 461

    IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_LINES_ALL rows updated='||x_rows_processed); END IF;

Line: 537

    INSERT INTO PO_ATTRIBUTE_VALUES
    (
      ATTRIBUTE_VALUES_ID,
      PO_LINE_ID,
      REQ_TEMPLATE_NAME,
      REQ_TEMPLATE_LINE_NUM,
      IP_CATEGORY_ID,
      INVENTORY_ITEM_ID,
      ORG_ID,
      PICTURE,
      LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN,
      last_updated_program,
      LAST_UPDATE_DATE,
      CREATED_BY,
      CREATION_DATE
    )
    SELECT PO_ATTRIBUTE_VALUES_S.nextval,
           gPoLineIds(i),
           gPoReqTemplateNames(i), -- req_template_name
           gPoReqTemplateLineIds(i), -- req_template_line_id
           gIpCategoryIds(i), -- ip_category_id
           gInvItemIds(i), -- inventory_item_id
           gPoOrgIds(i), -- org_id
           NVL(gImages(i), gImageUrls(i)), -- Image or URL
           g_R12_UPGRADE_USER, -- last_updated_by
           g_R12_UPGRADE_USER, -- last_update_login
           g_R12_MIGRATION_PROGRAM, -- last_update_program
           sysdate, -- last_update_date
           g_R12_UPGRADE_USER, -- created_by
           sysdate -- creation_date
    FROM DUAL
    WHERE gRecreateAttribRow(i) = 'Y'
      AND NOT EXISTS
       (SELECT /*+ INDEX(POAT, PO_ATTRIBUTE_VALUES_U2) */
              'Attr row already exists'
        FROM PO_ATTRIBUTE_VALUES POAT
        WHERE POAT.po_line_id = gPoLineIds(i)
          AND POAT.req_template_name = gPoReqTemplateNames(i)
          AND POAT.req_template_line_num = to_char(gPoReqTemplateLineIds(i))
          AND POAT.org_id = gPoOrgIds(i));

Line: 580

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES rows inserted='||SQL%rowcount); END IF;

Line: 621

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Inserting for description based items'); END IF;

Line: 634

    INSERT INTO PO_ATTRIBUTE_VALUES_TLP
    (
      ATTRIBUTE_VALUES_TLP_ID,
      PO_LINE_ID,
      REQ_TEMPLATE_NAME,
      REQ_TEMPLATE_LINE_NUM,
      IP_CATEGORY_ID,
      INVENTORY_ITEM_ID,
      ORG_ID,
      LANGUAGE,
      DESCRIPTION,
      LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN,
      last_updated_program,
      LAST_UPDATE_DATE,
      CREATED_BY,
      CREATION_DATE
    )
    SELECT PO_ATTRIBUTE_VALUES_TLP_S.nextval,
           gPoLineIds(i),
           gPoReqTemplateNames(i), -- req_template_name
           gPoReqTemplateLineIds(i), -- req_template_line_id
           gIpCategoryIds(i), -- ip_category_id
           gInvItemIds(i), -- inventory_item_id
           gPoOrgIds(i), -- org_id
           p_base_lang,
           gItemDescriptions(i),
           g_R12_UPGRADE_USER, -- last_updated_by
           g_R12_UPGRADE_USER, -- last_update_login
           g_R12_MIGRATION_PROGRAM, -- last_update_program
           sysdate, -- last_update_date
           g_R12_UPGRADE_USER, -- created_by
           sysdate -- creation_date
    FROM DUAL
    WHERE gRecreateAttribTLPRow(i) = 'Y'
      AND gInvItemIds(i) = NULL_ID -- Description based non catalog item
      AND NOT EXISTS
       (SELECT /*+ INDEX(POATLP, PO_ATTRIBUTE_VALUES_TLP_U2) */
               NULL
        FROM PO_ATTRIBUTE_VALUES_TLP POATLP
        WHERE POATLP.po_line_id = gPoLineIds(i)
          AND POATLP.req_template_name = gPoReqTemplateNames(i)
          AND POATLP.req_template_line_num = to_char(gPoReqTemplateLineIds(i))
          AND POATLP.org_id = gPoOrgIds(i));

Line: 679

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows inserted by default='||SQL%rowcount); END IF;

Line: 682

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Inserting for item master items (pulling translations from INV)'); END IF;

Line: 690

    INSERT INTO PO_ATTRIBUTE_VALUES_TLP
    (
      ATTRIBUTE_VALUES_TLP_ID,
      PO_LINE_ID,
      REQ_TEMPLATE_NAME,
      REQ_TEMPLATE_LINE_NUM,
      IP_CATEGORY_ID,
      INVENTORY_ITEM_ID,
      ORG_ID,
      LANGUAGE,
      DESCRIPTION,
      LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN,
      last_updated_program,
      LAST_UPDATE_DATE,
      CREATED_BY,
      CREATION_DATE
    )
    SELECT PO_ATTRIBUTE_VALUES_TLP_S.nextval,
           gPoLineIds(i),
           gPoReqTemplateNames(i), -- req_template_name
           gPoReqTemplateLineIds(i), -- req_template_line_id
           gIpCategoryIds(i), -- ip_category_id
           gInvItemIds(i), -- inventory_item_id
           gPoOrgIds(i), -- org_id
           mtl.language, -- Language
           -- For catalog language/base lang, the description is from PO Lines
           -- For the translations, the description is from items TL
           NVL(decode(mtl.language, p_base_lang, gItemDescriptions(i), mtl.description), mtl.description),  -- For null item_description, default from item master
           g_R12_UPGRADE_USER, -- last_updated_by
           g_R12_UPGRADE_USER, -- last_update_login
           g_R12_MIGRATION_PROGRAM, -- last_updated_program
           sysdate, -- last_update_date
           g_R12_UPGRADE_USER, -- created_by
           sysdate -- creation_date
    FROM MTL_SYSTEM_ITEMS_TL MTL, FINANCIALS_SYSTEM_PARAMS_ALL FSP
    WHERE gRecreateAttribTLPRow(i) = 'Y'
      AND gInvItemIds(i) <> NULL_ID -- Item master items
      AND gInvItemIds(i) = MTL.inventory_item_id
      AND gPoOrgIds(i)  = FSP.org_id
      AND FSP.inventory_organization_id = MTL.organization_id
      AND MTL.language = MTL.source_lang
      AND NOT EXISTS
      (SELECT /*+ INDEX(POATLP, PO_ATTRIBUTE_VALUES_TLP_U2) */
              NULL
        FROM PO_ATTRIBUTE_VALUES_TLP POATLP
        WHERE POATLP.language = MTL.language
          AND POATLP.po_line_id = gPoLineIds(i)
          AND POATLP.req_template_name = gPoReqTemplateNames(i)
          AND POATLP.req_template_line_num = to_char(gPoReqTemplateLineIds(i))
          AND POATLP.org_id = gPoOrgIds(i));

Line: 742

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows inserted due to item master translation='||SQL%rowcount); END IF;

Line: 745

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Inserting for item master items that have no translation available in base lang'); END IF;

Line: 762

    INSERT INTO PO_ATTRIBUTE_VALUES_TLP
    (
      ATTRIBUTE_VALUES_TLP_ID,
      PO_LINE_ID,
      REQ_TEMPLATE_NAME,
      REQ_TEMPLATE_LINE_NUM,
      IP_CATEGORY_ID,
      INVENTORY_ITEM_ID,
      ORG_ID,
      LANGUAGE,
      DESCRIPTION,
      LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN,
      last_updated_program,
      LAST_UPDATE_DATE,
      CREATED_BY,
      CREATION_DATE
    )
    SELECT PO_ATTRIBUTE_VALUES_TLP_S.nextval,
           gPoLineIds(i),
           NULL_ID, --gPoReqTemplateNames(i), -- req_template_name
           NULL_ID, --gPoReqTemplateLineIds(i), -- req_template_line_id
           gIpCategoryIds(i), -- ip_category_id
           gInvItemIds(i), -- inventory_item_id
           gPoOrgIds(i), -- org_id
           POH.created_language, -- Language
           POL.item_description, -- item_description
           g_R12_UPGRADE_USER, -- last_updated_by
           g_R12_UPGRADE_USER, -- last_update_login
           g_R12_MIGRATION_PROGRAM, -- last_update_program
           sysdate, -- last_update_date
           g_R12_UPGRADE_USER, -- created_by
           sysdate -- creation_date
    FROM PO_HEADERS_ALL POH,
         PO_LINES_ALL POL
         --, PO_ATTRIBUTE_VALUES POAT
    WHERE gPoLineIds(i) <> NULL_ID
      AND gInvItemids(i) <> NULL_ID
      AND POL.po_line_id = gPoLineIds(i)
      AND POH.po_header_id = POL.po_header_id
      --AND POH.created_language <> p_base_lang (ECO bug 4862164)
      --AND POAT.po_line_id = POL.po_line_id -- make sure that the Attr row exists
      AND NOT EXISTS
      (SELECT /*+ INDEX(POATLP, PO_ATTRIBUTE_VALUES_TLP_U2) */
             'TLP row for created_lang already exists for Blanket/Quotation line'
        FROM PO_ATTRIBUTE_VALUES_TLP POATLP
        WHERE POATLP.language = POH.created_language
          AND POATLP.po_line_id = gPoLineIds(i));

Line: 811

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows inserted due to nonexistence of item master in created_lang ='||SQL%rowcount); END IF;

Line: 856

    SELECT NULL_ID,  -- PoLineId
           express_name, sequence_num, -- TemplateName, TemplateId
           NVL(ICXM.shopping_category_id, NULL_ID),
           NVL(PORL.item_id, NULL_ID), PORL.org_id, PORL.item_description,
           NULL, -- Image
           NULL, -- ImageUrl
           DECODE(
              NVL(ICXM.shopping_category_id, NULL_ID), POATLP.ip_category_id,
              DECODE(PORL.item_id, POATLP.inventory_item_id,
              DECODE(PORL.item_description, POATLP.description,
                NULL, 'DESCRIPTION'), 'ITEM_ID'), 'IP_CATEGORY_ID'), -- Attribute Modified
           DECODE(
              NVL(ICXM.shopping_category_id, NULL_ID), POATLP.ip_category_id,
              DECODE(PORL.item_id, POATLP.inventory_item_id,
              DECODE(PORL.item_description, POATLP.description,
                'N', 'Y'), 'Y'), 'Y'), --  Recreate Attribute
           DECODE(
              NVL(ICXM.shopping_category_id, NULL_ID), POATLP.ip_category_id,
              DECODE(PORL.item_id, POATLP.inventory_item_id,
              DECODE(PORL.item_description, POATLP.description,
                'N', 'Y'), 'Y'), 'Y') --  Recreate Attribute TLP
    FROM PO_REQEXPRESS_LINES_ALL PORL,
         PO_ATTRIBUTE_VALUES_TLP POATLP,
         ICX_CAT_PURCHASING_CAT_MAP_V ICXM
    WHERE PORL.last_updated_program = g_R12_MIGRATION_PROGRAM
      AND PORL.express_name = POATLP.req_template_name
      AND PORL.sequence_num  = POATLP.req_template_line_num
      AND PORL.org_id = POATLP.org_id
      AND PORL.last_update_date > POATLP.last_update_date
      AND (   NVL(PORL.item_id, NULL_ID) <> POATLP.inventory_item_id
           OR PORL.item_description <> POATLP.description)
      AND POATLP.language = p_base_lang
      AND PORL.CATEGORY_ID = ICXM.po_category_id(+)
    UNION ALL
    SELECT NULL_ID,  -- PoLineId
           express_name, sequence_num, -- TemplateName, TemplateId
           PORL.ip_category_id, -- iP Category Id
           NVL(PORL.item_id, NULL_ID), PORL.org_id, PORL.item_description,
           NULL, -- Image
           NULL, -- ImageUrl
           'ITEM_TRANSLATION', 'N','Y' -- Attribute, Recreate Attrib, Recreate Attrib TLP
    FROM PO_REQEXPRESS_LINES_ALL PORL,
         MTL_SYSTEM_ITEMS_TL MTL,
         FINANCIALS_SYSTEM_PARAMS_ALL FSP
    WHERE PORL.last_updated_program = g_R12_MIGRATION_PROGRAM
      AND item_id IS NOT NULL
      AND PORL.item_id = MTL.inventory_item_id  -- If item had changed then it would have been taken care by 'ITEM_ID' attribute change portion of this sql(it recreates the attributes)
      AND PORL.org_id = FSP.org_id
      AND FSP.inventory_organization_id = MTL.organization_id
      AND MTL.language = MTL.source_lang
      AND NOT EXISTS
      (
          SELECT 'Upgraded Lines with newly added item master translations'
          FROM   PO_ATTRIBUTE_VALUES_TLP POATLP
          WHERE POATLP.req_template_name <> to_char(NULL_ID) -- Only look for Template records
            AND PORL.express_name = POATLP.req_template_name
            AND PORL.sequence_num = POATLP.req_template_line_num
            AND PORL.org_id = POATLP.org_id
            AND PORL.item_id = MTL.inventory_item_id
            AND POATLP.language = MTL.language
      )
    UNION ALL
    SELECT NULL_ID,  -- PoLineId
           req_template_name, req_template_line_num, -- TemplateName, TemplateId
           ip_category_id, NVL(inventory_item_id, NULL_ID), org_id, null, -- description notneeded(null)
           NULL, -- Image
           NULL, -- ImageUrl
           'LINE_DELETED', 'N','N' -- Attribute, Recreate Attrib, Recreate Attrib TLP
    FROM PO_ATTRIBUTE_VALUES POAT
    WHERE req_template_line_num <> NULL_ID --Bug#4865650
      AND NOT EXISTS
      -- Req Template lines that have been deleted but have attribute reference
      -- (Need to be purged)
      (
          SELECT 'Req Template lines deleted'
          FROM  PO_REQEXPRESS_LINES_ALL PORL
          WHERE PORL.express_name = POAT.req_template_name
            AND PORL.sequence_num = POAT.req_template_line_num
            AND PORL.org_id = POAT.org_id
      );

Line: 953

  SELECT count(*)
    INTO l_was_R12_upg_ever_run_before
    FROM PO_REQEXPRESS_LINES_ALL PORL
   WHERE PORL.last_updated_program = g_R12_MIGRATION_PROGRAM
     AND rownum=1;

Line: 973

      IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Modified RTs; gPoLineIds.DELETE'); END IF;

Line: 974

      gPoLineIds.DELETE;

Line: 975

      gIpCategoryIds.DELETE;

Line: 976

      gInvItemIds.DELETE;

Line: 977

      gPoOrgIds.DELETE;

Line: 978

      gItemDescriptions.DELETE;

Line: 979

      gImages.DELETE;

Line: 980

      gImageUrls.DELETE;

Line: 981

      gUpdatedAttribute.DELETE;

Line: 982

      gRecreateAttribRow.DELETE;

Line: 983

      gRecreateAttribTLPRow.DELETE;

Line: 984

      gPoReqTemplateNames.DELETE;

Line: 985

      gPoReqTemplateLineIds.DELETE;

Line: 1006

                        gUpdatedAttribute,
                        gRecreateAttribRow, gRecreateAttribTLPRow
      LIMIT p_batch_size;

Line: 1026

      UPDATE PO_REQEXPRESS_LINES_ALL PORL
      SET ip_category_id = gIpCategoryIds(i),
          last_updated_program = g_R12_MIGRATION_PROGRAM
      WHERE PORL.express_name = gPoReqTemplateNames(i)
        AND PORL.sequence_num = to_char(gPoReqTemplateLineIds(i))
        AND PORL.org_id = gPoOrgIds(i)
        AND gUpdatedAttribute(i) = 'IP_CATEGORY_ID';

Line: 1034

    IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_REQEXPRESS_LINES_ALL rows updated='||SQL%rowcount); END IF;

Line: 1044

      DELETE FROM PO_ATTRIBUTE_VALUES
      WHERE req_template_name = gPoReqTemplateNames(i)
        AND req_template_line_num = to_char(gPoReqTemplateLineIds(i))
        AND org_id = gPoOrgIds(i)
        AND (gRecreateAttribRow(i) = 'Y' OR gUpdatedAttribute(i) = 'LINE_DELETED');

Line: 1050

    IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES rows deleted='||SQL%rowcount); END IF;

Line: 1059

      DELETE FROM PO_ATTRIBUTE_VALUES_TLP
      WHERE req_template_name = gPoReqTemplateNames(i)
        AND req_template_line_num = to_char(gPoReqTemplateLineIds(i))
        AND org_id = gPoOrgIds(i)
        AND (gRecreateAttribTLPRow(i) = 'Y' OR gUpdatedAttribute(i) = 'LINE_DELETED');

Line: 1065

    IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows deleted='||SQL%rowcount); END IF;

Line: 1074

      UPDATE PO_ATTRIBUTE_VALUES_TLP
      SET description = gItemDescriptions(i),
          last_updated_program = g_R12_MIGRATION_PROGRAM
      WHERE req_template_name = gPoReqTemplateNames(i)
        AND req_template_line_num = to_char(gPoReqTemplateLineIds(i))
        AND org_id = gPoOrgIds(i)
        AND gUpdatedAttribute(i) = 'DESCRIPTION';

Line: 1082

    IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows updated='||SQL%rowcount); END IF;

Line: 1135

   SELECT NULL_ID,  -- PoLineId
           express_name, sequence_num, -- TemplateName, TemplateId
           NVL(ICXM.SHOPPING_CATEGORY_ID, NULL_ID), -- ip_category_id
           NVL(PORL.item_id, NULL_ID), PORL.org_id, PORL.item_description,
           NULL, -- Image
           NULL, -- ImageUrl
           NULL, 'Y','Y' -- Attribute, Recreate Attrib, Recreate Attrib TLP
    FROM PO_REQEXPRESS_LINES_ALL PORL,
         ICX_CAT_PURCHASING_CAT_MAP_V ICXM
    WHERE last_updated_program is null
      AND PORL.CATEGORY_ID = ICXM.po_category_id(+) ;

Line: 1163

      IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'New RTs; gPoLineIds.DELETE'); END IF;

Line: 1164

      gPoLineIds.DELETE;

Line: 1165

      gIpCategoryIds.DELETE;

Line: 1166

      gInvItemIds.DELETE;

Line: 1167

      gPoOrgIds.DELETE;

Line: 1168

      gItemDescriptions.DELETE;

Line: 1169

      gImages.DELETE;

Line: 1170

      gImageUrls.DELETE;

Line: 1171

      gUpdatedAttribute.DELETE;

Line: 1172

      gRecreateAttribRow.DELETE;

Line: 1173

      gRecreateAttribTLPRow.DELETE;

Line: 1174

      gPoReqTemplateNames.DELETE;

Line: 1175

      gPoReqTemplateLineIds.DELETE;

Line: 1196

                        gUpdatedAttribute,
                        gRecreateAttribRow, gRecreateAttribTLPRow
      LIMIT p_batch_size;

Line: 1216

      UPDATE PO_REQEXPRESS_LINES_ALL  PORL
      SET ip_category_id = gIpCategoryIds(i),
          last_updated_program = g_R12_MIGRATION_PROGRAM
      WHERE express_name = gPoReqTemplateNames(i)
        AND sequence_num = to_char(gPoReqTemplateLineIds(i))
        AND org_id = gPoOrgIds(i);

Line: 1223

    IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_REQEXPRESS_LINES_ALL rows updated='||SQL%rowcount); END IF;

Line: 1254

    SELECT NVL(MIN(job_number), 1)
    INTO   l_upgrade_job_number
    FROM   icx_cat_r12_upgrade_jobs;