APPS.ICX_CAT_FPI_UPGRADE SQL Statements

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

Line: 50

gIExtractorUpdatedFlags		DBMS_SQL.VARCHAR2_TABLE;

Line: 97

    gIRtItemIds.DELETE;

Line: 98

    gIOldRtItemIds.DELETE;

Line: 99

    gIOrgIds.DELETE;

Line: 100

    gISupplierPartNums.DELETE;

Line: 101

    gIRtCategoryIds.DELETE;

Line: 102

    gIExtractorUpdatedFlags.DELETE;

Line: 106

    gITRtItemIds.DELETE;

Line: 107

    gITItemDescriptions.DELETE;

Line: 111

    gCIRtItemIds.DELETE;

Line: 112

    gCITemplateIds.DELETE;

Line: 116

    gEPRtItemIds.DELETE;

Line: 117

    gEPActiveFlags.DELETE;

Line: 118

    gEPOrgIds.DELETE;

Line: 119

    gEPPriceTypes.DELETE;

Line: 120

    gEPRowIds.DELETE;

Line: 121

    gEPRateTypes.DELETE;

Line: 122

    gEPRateDates.DELETE;

Line: 123

    gEPRates.DELETE;

Line: 124

    gEPSupplierNumbers.DELETE;

Line: 125

    gEPSupplierContactIds.DELETE;

Line: 126

    gEPItemRevisions.DELETE;

Line: 127

    gEPLineTypeIds.DELETE;

Line: 128

    gEPBuyerIds.DELETE;

Line: 132

    gBPRtItemIds.DELETE;

Line: 133

    gBRActiveFlgs.DELETE;

Line: 134

    gBPOrgIds.DELETE;

Line: 135

    gBPSupplierSiteIds.DELETE;

Line: 136

    gBPPriceTypes.DELETE;

Line: 137

    gBPRowIds.DELETE;

Line: 158

    xShot := xShot || ' gIExtractorUpdatedFlags: ' ||
      ICX_POR_EXT_UTL.getTableElement(gIExtractorUpdatedFlags, pIndex);

Line: 220

  gTemplateItemCache.DELETE;

Line: 336

  SELECT ci.rt_category_id
  INTO	 xRtCategoryId
  FROM   icx_por_category_items ci
  WHERE  ci.rt_item_id = pRtItemId
  AND    EXISTS (SELECT 'primary category'
                 FROM   icx_por_categories_tl cat
                 WHERE  cat.rt_category_id = ci.rt_category_id
                 AND    cat.type = ICX_POR_EXT_CLASS.CATEGORY_TYPE)
  AND    ROWNUM = 1;

Line: 353

  SELECT ci.rt_category_id
  INTO	 xRtCategoryId
  FROM   icx_cat_category_items ci
  WHERE  ci.rt_item_id = pRtItemId
  AND    EXISTS (SELECT 'primary category'
                 FROM   icx_cat_categories_tl cat
                 WHERE  cat.rt_category_id = ci.rt_category_id
                 AND    cat.type = ICX_POR_EXT_CLASS.CATEGORY_TYPE)
  AND    ROWNUM = 1;

Line: 368

    SELECT rt_descriptor_id,
           key, type,
           stored_in_table,
           stored_in_column
    FROM   icx_cat_descriptors_tl
    WHERE  rt_category_id = cpRtCategoryId
    AND    language = (SELECT language_code
                       FROM   fnd_languages
                       WHERE  installed_flag = 'B');

Line: 384

  xUpdateColumns	VARCHAR2(2000) := NULL;

Line: 385

  xSelectColumns	VARCHAR2(2000) := NULL;

Line: 439

        IF (xUpdateColumns IS NOT NULL) THEN
          xUpdateColumns := xUpdateColumns || ',';

Line: 441

          xSelectColumns := xSelectColumns || ',';

Line: 444

        xUpdateColumns := xUpdateColumns || xStoredInColumns(i);

Line: 449

            xSelectColumns := xSelectColumns || 'i.A' ||
              xRtDescriptorIds(i);

Line: 452

            xSelectColumns := xSelectColumns || 'to_number(i.A' ||
              xRtDescriptorIds(i) || ')';

Line: 455

            xSelectColumns := xSelectColumns || 'tl.A' ||
              xRtDescriptorIds(i);

Line: 460

            xSelectColumns := xSelectColumns || 'to_number(c.A' ||
              xRtDescriptorIds(i) || ')';

Line: 463

            xSelectColumns := xSelectColumns || 'c.A' ||
              xRtDescriptorIds(i);

Line: 475

    IF (xUpdateColumns IS NOT NULL) THEN
      xErrLoc := 350;

Line: 478

        'UPDATE ICX_CAT_ITEMS_TLP tlp ' ||
        'SET    (' || xUpdateColumns || ') = ' ||
        '(SELECT ' || xSelectColumns ||
        ' FROM   ICX_POR_ITEMS i, ICX_POR_ITEMS_TL tl ' ||
        ' WHERE  i.rt_item_id = :old_rt_item_id ' ||
        ' AND    i.rt_item_id = tl.rt_item_id ' ||
        ' AND    tlp.language = tl.language) ' ||
        'WHERE  tlp.rt_item_id = :new_rt_item_id';

Line: 491

    IF (xUpdateColumns IS NOT NULL) THEN
      xErrLoc := 370;

Line: 494

        'UPDATE ICX_CAT_EXT_ITEMS_TLP tlp ' ||
        'SET    (' || xUpdateColumns || ') = ' ||
        '(SELECT ' || xSelectColumns ||
        ' FROM   ICX_POR_C' || pRtCategoryId || '_TL c' ||
        ' WHERE  c.rt_item_id = :old_rt_item_id ' ||
        ' AND    c.language = tlp.language) ' ||
        'WHERE  tlp.rt_item_id = :new_rt_item_id ' ||
        'AND    tlp.rt_category_id = ' || pRtCategoryId;

Line: 562

    SELECT NVL(sub.orc_operating_unit_id, ICX_POR_EXT_ITEM.NULL_NUMBER) org_id,
           NVL(ph.vendor_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
           pl.vendor_product_num supplier_part_num,
           NVL(sub.orc_supplier_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
           7 type, -- template_contracts
           greatest(pl.last_update_date,
                    ph.last_update_date) last_update_date,
           -- pcreddy : Bug # 3234875 : Price type should be TEMPLATE
           -- for template lines copied from blankets
           -- ph.type_lookup_code price_type, -- 'BLANKET' or 'QUOTATION'
           'TEMPLATE' as price_type,
           pl.item_description item_description,
           sub.orc_template_id template_id,
           ph.rate_type,
	   ph.rate_date,
	   ph.rate,
	   pv.segment1 supplier_number,
	   NVL(ph.vendor_contact_id, prl.suggested_vendor_contact_id) supplier_contact_id,
	   prl.item_revision,
	   prl.line_type_id,
           prl.suggested_buyer_id buyer_id,
           sub.rowid row_id
    FROM   icx_por_oracle_item_subtable sub,
           po_reqexpress_lines_all prl,
           po_headers_all ph,
           po_lines_all pl,
           po_vendors pv
    WHERE  sub.rt_item_id = p_rt_item_id
    AND    sub.orc_template_id is not null
    AND    sub.orc_contract_id is not null
    AND    sub.orc_template_id = prl.express_name
    AND    sub.orc_template_line_id = prl.sequence_num
    AND    (sub.orc_operating_unit_id is NULL AND
            prl.org_id is NULL OR
            prl.org_id = sub.orc_operating_unit_id)
    AND    sub.orc_contract_id = ph.po_header_id
    AND    sub.orc_contract_line_id = pl.po_line_id
    AND    prl.suggested_vendor_id = pv.vendor_id (+)
  UNION ALL
    SELECT NVL(sub.orc_operating_unit_id, ICX_POR_EXT_ITEM.NULL_NUMBER) org_id,
           NVL(ph.vendor_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
           pl.vendor_product_num supplier_part_num,
           NVL(sub.orc_supplier_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
           6 type, -- contracts
           greatest(pl.last_update_date,
                    ph.last_update_date) last_update_date,
           ph.type_lookup_code price_type, -- 'BLANKET' or 'QUOTATION'
           pl.item_description item_description,
           NVL(sub.orc_template_id, TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER)) template_id,
           ph.rate_type,
	   ph.rate_date,
	   ph.rate,
	   pv.segment1 supplier_number,
	   ph.vendor_contact_id supplier_contact_id,
	   pl.item_revision,
	   pl.line_type_id,
	   ph.agent_id buyer_id,
           sub.rowid row_id
    FROM   icx_por_oracle_item_subtable sub,
           po_headers_all ph,
           po_lines_all pl,
           po_vendors pv
    WHERE  sub.rt_item_id = p_rt_item_id
    AND    sub.orc_contract_id is not null
    AND    sub.orc_contract_id = ph.po_header_id
    AND    sub.orc_contract_line_id = pl.po_line_id
    AND    ph.vendor_id = pv.vendor_id (+)
  UNION ALL
    SELECT NVL(sub.orc_operating_unit_id, ICX_POR_EXT_ITEM.NULL_NUMBER) org_id,
           NVL(prl.suggested_vendor_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
           prl.suggested_vendor_product_code supplier_part_num,
           NVL(sub.orc_supplier_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
           5 type, -- templates
           greatest(prl.last_update_date,
                    prh.last_update_date) last_update_date,
           'TEMPLATE' price_type,
           prl.item_description item_description,
           sub.orc_template_id template_id,
           TO_CHAR(NULL) rate_type,
	   TO_DATE(NULL) rate_date,
	   TO_NUMBER(NULL) rate,
	   pv.segment1 supplier_number,
	   prl.suggested_vendor_contact_id supplier_contact_id,
	   prl.item_revision,
	   prl.line_type_id,
           prl.suggested_buyer_id buyer_id,
           sub.rowid row_id
    FROM   icx_por_oracle_item_subtable sub,
           po_reqexpress_headers_all prh,
           po_reqexpress_lines_all prl,
           po_vendors pv
    WHERE  sub.rt_item_id = p_rt_item_id
    AND    sub.orc_template_id is not null
    AND    sub.orc_contract_id is null
    AND    prh.express_name = sub.orc_template_id
    AND    (sub.orc_operating_unit_id is NULL AND
            prh.org_id is NULL OR
            prh.org_id = sub.orc_operating_unit_id)
    AND    prl.express_name = sub.orc_template_id
    AND    prl.sequence_num = sub.orc_template_line_id
    AND    (sub.orc_operating_unit_id is NULL AND
            prl.org_id is NULL OR
            prl.org_id = sub.orc_operating_unit_id)
    AND    prl.suggested_vendor_id = pv.vendor_id (+)
  UNION ALL
    SELECT NVL(sub.orc_operating_unit_id, ICX_POR_EXT_ITEM.NULL_NUMBER) org_id,
           NVL(prl.suggested_vendor_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
           prl.suggested_vendor_product_code supplier_part_num,
           NVL(sub.orc_supplier_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
           4 type, -- internal templates
           greatest(prl.last_update_date,
                    prh.last_update_date) last_update_date,
           'INTERNAL_TEMPLATE' price_type,
           prl.item_description item_description,
           sub.orc_template_id template_id,
           TO_CHAR(NULL) rate_type,
	   TO_DATE(NULL) rate_date,
	   TO_NUMBER(NULL) rate,
	   TO_CHAR(NULL) supplier_number,
	   TO_NUMBER(NULL) supplier_contact_id,
	   prl.item_revision,
	   prl.line_type_id,
           prl.suggested_buyer_id buyer_id,
           sub.rowid row_id
    FROM   icx_por_oracle_item_subtable sub,
           po_reqexpress_headers_all prh,
           po_reqexpress_lines_all prl
    WHERE  sub.rt_item_id = p_rt_item_id
    AND    sub.orc_template_id is not null
    AND    sub.orc_contract_id is null
    AND    sub.search_type = 'INTERNAL'
    AND    prh.express_name = sub.orc_template_id
    AND    (sub.orc_operating_unit_id is NULL AND
            prh.org_id is NULL OR
            prh.org_id = sub.orc_operating_unit_id)
    AND    prl.express_name = sub.orc_template_id
    AND    prl.sequence_num = sub.orc_template_line_id
    AND    (sub.orc_operating_unit_id is NULL AND
            prl.org_id is NULL OR
            prl.org_id = sub.orc_operating_unit_id)
  UNION ALL
    SELECT prl.buyer_id org_id,
           NVL(prl.supplier_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
           item.a3 supplier_part_num,
           NVL(pvs.vendor_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
           3 type, -- Bulk Loaded
           prl.last_update_date last_update_date,
           --Bug#3148018
           --For lines with contract_reference_num, should have a price_type
           --of CONTRACT
           decode(prl.contract_reference_num, null, 'BULKLOAD', 'CONTRACT') price_type,
           TO_CHAR(NULL) item_description,
           TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER) template_id,
           TO_CHAR(NULL) rate_type,
	   TO_DATE(NULL) rate_date,
	   TO_NUMBER(NULL) rate,
	   TO_CHAR(NULL) supplier_number,
	   TO_NUMBER(NULL) supplier_contact_id,
	   TO_CHAR(NULL) item_revision,
	   TO_NUMBER(NULL) line_type_id,
           TO_NUMBER(NULL) buyer_id,
           prl.rowid row_id
    FROM   icx_por_price_list_lines prl,
           icx_por_items item,
           po_vendor_sites_all pvs
    WHERE  prl.item_id = p_rt_item_id
    AND    prl.buyer_approval_status = 'APPROVED'
    AND    item.rt_item_id = p_rt_item_id
    AND    prl.supplier_site = pvs.vendor_site_code (+)
    AND    prl.supplier_id = pvs.vendor_id (+)
    AND    prl.buyer_id = pvs.org_id (+)
  UNION ALL
    SELECT NVL(sub.orc_operating_unit_id, ICX_POR_EXT_ITEM.NULL_NUMBER) org_id,
           NVL(pasl.vendor_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
           pasl.primary_vendor_item supplier_part_num,
           NVL(sub.orc_supplier_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
           2 type, -- ASLs
           pasl.last_update_date last_update_date,
           'ASL' price_type,
           TO_CHAR(NULL) item_description,
           TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER) template_id,
           TO_CHAR(NULL) rate_type,
	   TO_DATE(NULL) rate_date,
	   TO_NUMBER(NULL) rate,
	   TO_CHAR(NULL) supplier_number,
	   TO_NUMBER(NULL) supplier_contact_id,
	   TO_CHAR(NULL) item_revision,
	   TO_NUMBER(NULL) line_type_id,
           TO_NUMBER(NULL) buyer_id,
           sub.rowid row_id
    FROM   icx_por_oracle_item_subtable sub,
           po_approved_supplier_list pasl
    WHERE  sub.rt_item_id = p_rt_item_id
    AND    sub.orc_template_id is null
    AND    sub.orc_contract_id is null
    AND    sub.orc_asl_id is not null
    AND    pasl.asl_id = sub.orc_asl_id
    AND    (sub.orc_operating_unit_id is NULL AND
            pasl.owning_organization_id is NULL OR
            pasl.owning_organization_id =
              (SELECT fspa.inventory_organization_id
               FROM   financials_system_params_all fspa
               WHERE  fspa.org_id = sub.orc_operating_unit_id
               AND    rownum = 1))
  UNION ALL
    SELECT NVL(sub.orc_operating_unit_id, ICX_POR_EXT_ITEM.NULL_NUMBER) org_id,
           TO_NUMBER(ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_id,
           TO_CHAR(NULL) supplier_part_num,
           TO_NUMBER(ICX_POR_EXT_ITEM.NULL_NUMBER) supplier_site_id,
           1 type, -- Master Items
           msi.last_update_date last_update_date,
           DECODE(sub.search_type, 'SUPPLIER',
                  'PURCHASING_ITEM', 'INTERNAL_ITEM') price_type,
           TO_CHAR(NULL) item_description,
           TO_CHAR(NULL) template_id,
           TO_CHAR(NULL) rate_type,
	   TO_DATE(NULL) rate_date,
	   TO_NUMBER(NULL) rate,
	   TO_CHAR(NULL) supplier_number,
	   TO_NUMBER(NULL) supplier_contact_id,
	   TO_CHAR(NULL) item_revision,
	   TO_NUMBER(NULL) line_type_id,
           TO_NUMBER(NULL) buyer_id,
           sub.rowid row_id
    FROM   icx_por_oracle_item_subtable sub,
           icx_por_items item,
           mtl_system_items msi
    WHERE  sub.rt_item_id = p_rt_item_id
    AND    sub.orc_template_id is null
    AND    sub.orc_contract_id is null
    AND    sub.orc_asl_id is null
    AND    sub.rt_item_id = item.rt_item_id
    AND    item.orc_item_id is not null
    AND    msi.inventory_item_id = item.orc_item_id
    AND    (sub.orc_operating_unit_id is NULL AND
            msi.organization_id is NULL OR
            msi.organization_id =
              (SELECT fspa.inventory_organization_id
               FROM   financials_system_params_all fspa
               WHERE  fspa.org_id = sub.orc_operating_unit_id
               AND    rownum = 1))
  -- pcreddy : Bug # 3234875 : Order by type desc
  ORDER BY 1, 2, 3, 5 DESC, 6 DESC;

Line: 813

  xLastUpdateDates	DBMS_SQL.DATE_TABLE;

Line: 857

                     xTypes, xLastUpdateDates,
                     xPriceTypes, xItemDescriptions,
                     xTemplateIds, xRateTypes, xRateDates,
                     xRates, xSupplierNumbers,
                     xSupplierContactIds, xItemRevisions,
                     xLineTypeIds, xBuyerIds, xRowIds;

Line: 878

        ', last_update_date: ' || xLastUpdateDates(i) ||
        ', price_type: ' || xPriceTypes(i) ||
        ', item_description: ' || xItemDescriptions(i) ||
        ', template_id: ' || xTemplateIds(i) ||
        ', rate_type: ' || xRateTypes(i) ||
	', rate_date: ' || xRateDates(i) ||
	', rate: ' || xRates(i) ||
	', supplier_number: ' || xSupplierNumbers(i) ||
	', supplier_contact_id: ' || xSupplierContactIds(i) ||
	', item_revision: ' || xItemRevisions(i) ||
	', line_type_id: ' || xLineTypeIds(i) ||
	', buyer_id: ' || xBuyerIds(i) ||
        ', rowid: ' || xRowIds(i));

Line: 911

    gIExtractorUpdatedFlags(xCount) := 'N';

Line: 913

    gIExtractorUpdatedFlags(xCount) := 'Y';

Line: 933

        gIExtractorUpdatedFlags(gIExtractorUpdatedFlags.COUNT) := 'Y';

Line: 1008

      SELECT icx_por_itemid.nextval
      INTO   xRtItemId
      FROM   sys.dual;

Line: 1031

        gIExtractorUpdatedFlags(xCount) := 'N';

Line: 1033

        gIExtractorUpdatedFlags(xCount) := 'Y';

Line: 1145

    INSERT INTO ICX_CAT_ITEMS_B
    (rt_item_id, object_version_number, org_id,
     supplier_id,
     supplier, supplier_part_num, supplier_part_auxid,
     internal_item_id, internal_item_num,
     extractor_updated_flag, last_update_login, last_updated_by, last_update_date,
     created_by, creation_date, request_id,
     program_application_id, program_id, program_update_date)
    SELECT gIRtItemIds(i), gIOldRtItemIds(i), gIOrgIds(i),
           NVL(item.supplier_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
           item.A1, gISupplierPartNums(i), '##NULL##',
           item.orc_item_id, item.orc_item_num,
           gIExtractorUpdatedFlags(i),
           gUpgradeUserId, gUpgradeUserId, sysdate,
           gUpgradeUserId, item.creation_date, gUpgradeUserId,
           gUpgradeUserId, gUpgradeUserId, sysdate
    FROM   ICX_POR_ITEMS item
    WHERE  item.rt_item_id = gIOldRtItemIds(i);

Line: 1170

    INSERT INTO ICX_CAT_ITEMS_TLP
    (rt_item_id, language, org_id,
     supplier_id, item_source_type, search_type,
     primary_category_id, primary_category_name,
     internal_item_id, internal_item_num,
     supplier, supplier_part_num, supplier_part_auxid,
     manufacturer, manufacturer_part_num, description,
     comments, alias,
     picture, picture_url, thumbnail_image,
     attachment_url, long_description,
     unspsc_code, availability, lead_time, item_type,
     ctx_desc, last_update_login, last_updated_by, last_update_date,
     created_by, creation_date, request_id,
     program_application_id, program_id, program_update_date)
    SELECT gIRtItemIds(i), tl.language, gIOrgIds(i),
           NVL(item.supplier_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
           item.item_source_type, item.search_type,
           gIRtCategoryIds(i), cat.category_name,
           item.orc_item_id, item.orc_item_num,
           item.A1, gISupplierPartNums(i), '##NULL##',
           item.A4, item.A5, tl.A7,
           tl.A8, tl.A9,
           NVL(item.A13, item.A14), item.A14, NVL(item.A13, item.A14),
           item.A22, tl.A23,
           item.A24, item.A25, to_number(item.A26), item.A29,
           NULL, gUpgradeUserId, gUpgradeUserId, sysdate,
           gUpgradeUserId, tl.creation_date, gUpgradeUserId,
           gUpgradeUserId, gUpgradeUserId, sysdate
    FROM   ICX_POR_ITEMS item,
           ICX_POR_ITEMS_TL tl,
           ICX_POR_CATEGORY_ITEMS ci,
           ICX_POR_CATEGORIES_TL cat
    WHERE  item.rt_item_id = gIOldRtItemIds(i)
    AND    item.rt_item_id = tl.rt_item_id
    AND    ci.rt_item_id = item.rt_item_id
    AND    cat.rt_category_id = ci.rt_category_id
    AND    cat.rt_category_id = gIRtCategoryIds(i)
    AND    tl.language = cat.language;

Line: 1213

    'Dynamic SQL to update base attributes of ICX_CAT_ITEMS_TLP');

Line: 1238

    INSERT INTO ICX_CAT_CATEGORY_ITEMS
    (rt_item_id, rt_category_id,
     last_update_login, last_updated_by, last_update_date,
     created_by, creation_date, request_id,
     program_application_id, program_id, program_update_date)
    VALUES(gIRtItemIds(i), gIRtCategoryIds(i),
           gUpgradeUserId, gUpgradeUserId, sysdate,
           gUpgradeUserId, sysdate, gUpgradeUserId,
           gUpgradeUserId, gUpgradeUserId, sysdate);

Line: 1255

    INSERT INTO ICX_CAT_EXT_ITEMS_TLP
    (rt_item_id, language, org_id,
     rt_category_id, primary_flag,
     last_update_login, last_updated_by, last_update_date,
     created_by, creation_date, request_id,
     program_application_id, program_id, program_update_date)
    SELECT gIRtItemIds(i), tl.language, gIOrgIds(i),
           gIRtCategoryIds(i), NULL,
           gUpgradeUserId, gUpgradeUserId, sysdate,
           gUpgradeUserId, sysdate, gUpgradeUserId,
           gUpgradeUserId, gUpgradeUserId, sysdate
    FROM   ICX_POR_ITEMS_TL tl
    WHERE  tl.rt_item_id = gIOldRtItemIds(i);

Line: 1287

PROCEDURE updateItemsTLP IS
  xErrLoc	PLS_INTEGER;

Line: 1295

    'updateItemsTLP[Count: ' || gITRtItemIds.COUNT || ']');

Line: 1306

    'Update item_description of ICX_CAT_ITEMS_TLP');

Line: 1309

    UPDATE ICX_CAT_ITEMS_TLP
    SET description = gITItemDescriptions(i)
    WHERE  rt_item_id = gITRtItemIds(i)
    AND    language = (SELECT language_code
                       FROM   fnd_languages
                       WHERE  installed_flag = 'B');

Line: 1326

      'updateItemsTLP(' ||xErrLoc||'): '||sqlerrm;

Line: 1331

END updateItemsTLP;

Line: 1356

    INSERT INTO ICX_CAT_CATEGORY_ITEMS
    (rt_item_id, rt_category_id,
     last_update_login, last_updated_by, last_update_date,
     created_by, creation_date, request_id,
     program_application_id, program_id, program_update_date)
    SELECT gCIRtItemIds(i), cat.rt_category_id,
           gUpgradeUserId, gUpgradeUserId, sysdate,
           gUpgradeUserId, sysdate, gUpgradeUserId,
           gUpgradeUserId, gUpgradeUserId, sysdate
    FROM   ICX_POR_CATEGORIES_TL cat
    WHERE  cat.key = gCITemplateIds(i) || '_tmpl'
    AND    cat.type = 3
    AND    cat.language = (SELECT language_code
                           FROM   fnd_languages
                           WHERE  installed_flag = 'B');

Line: 1412

    INSERT INTO ICX_CAT_ITEM_PRICES
    (rt_item_id, price_type,
     active_flag, object_version_number,
     asl_id, supplier_site_id,
     contract_id, contract_line_id,
     template_id, template_line_id,
     inventory_item_id,
     mtl_category_id, org_id,
     search_type, unit_price,
     currency, unit_of_measure,
     functional_price, supplier_site_code,
     contract_num, contract_line_num,
     rate_type, rate_date, rate,
     supplier_number, supplier_contact_id,
     item_revision, line_type_id, buyer_id,
     price_list_id, last_update_login,
     last_updated_by, last_update_date,
     created_by, creation_date, request_id,
     program_application_id, program_id, program_update_date)
    SELECT gEPRtItemIds(i), gEPPriceTypes(i),
           gEPActiveFlags(i), 1,
           NVL(sub.orc_asl_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
           NVL(sub.orc_supplier_site_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
           NVL(sub.orc_contract_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
           NVL(sub.orc_contract_line_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
           -- PCREDDY: 3234875 : No template id for Contract lines
           DECODE(gEPPriceTypes(i), 'BLANKET', TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER),
             NVL(sub.orc_template_id, TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER))),
           DECODE(gEPPriceTypes(i), 'BLANKET', TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER),
             NVL(sub.orc_template_line_id, TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER))),
           NVL(item.orc_item_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
           sub.orc_category_id, gEPOrgIds(i),
           sub.search_type, sub.unit_price,
           sub.currency, sub.unit_of_measure,
           sub.functional_price, sub.orc_supplier_site_code,
           sub.orc_contract_num, sub.orc_contract_line_num,
           gEPRateTypes(i), gEPRateDates(i), gEPRates(i),
           gEPSupplierNumbers(i), gEPSupplierContactIds(i),
           gEPItemRevisions(i), gEPLineTypeIds(i), gEPBuyerIds(i),
           NULL, gUpgradeUserId, gUpgradeUserId, sysdate,
           gUpgradeUserId, sub.creation_date, gUpgradePhaseId,
           gUpgradeUserId, gUpgradeUserId, sysdate
    FROM   ICX_POR_ORACLE_ITEM_SUBTABLE sub,
           ICX_POR_ITEMS item
    WHERE  sub.rowid = gEPRowIds(i)
    AND    item.rt_item_id = sub.rt_item_id;

Line: 1500

    INSERT INTO ICX_CAT_ITEM_PRICES
    (rt_item_id, price_type,
     active_flag, object_version_number,
     asl_id, supplier_site_id,
     contract_id, contract_line_id,
     template_id, template_line_id,
     inventory_item_id,
     mtl_category_id, org_id,
     search_type, unit_price,
     currency, unit_of_measure,
     functional_price,
     supplier_site_code,
     contract_num, contract_line_num,
     price_list_id, last_update_login,
     last_updated_by, last_update_date,
     created_by, creation_date, request_id,
     program_application_id, program_id, program_update_date)
    SELECT gBPRtItemIds(i), gBPPriceTypes(i),
           gBRActiveFlgs(i), 1,
           ICX_POR_EXT_ITEM.NULL_NUMBER,
           NVL(gBPSupplierSiteIds(i), ICX_POR_EXT_ITEM.NULL_NUMBER),
           NVL(prl.contract_reference_id, ICX_POR_EXT_ITEM.NULL_NUMBER),
           ICX_POR_EXT_ITEM.NULL_NUMBER,
           TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER),
           ICX_POR_EXT_ITEM.NULL_NUMBER,
           ICX_POR_EXT_ITEM.NULL_NUMBER,
           ICX_POR_EXT_ITEM.NULL_NUMBER, gBPOrgIds(i),
           'SUPPLIER', prl.unit_price,
           prl.currency_code, prl.uom,
           NULL, -- Leave functional_price as NULL
           prl.supplier_site,
           prl.contract_reference_num, NULL,
           prl.header_id,
           gUpgradeUserId, gUpgradeUserId, sysdate,
           gUpgradeUserId, prl.creation_date, gUpgradePhaseId,
           gUpgradeUserId, gUpgradeUserId, sysdate
    FROM   ICX_POR_PRICE_LIST_LINES prl
    WHERE  prl.rowid = gBPRowIds(i);

Line: 1574

    updateItemsTLP;

Line: 1609

PROCEDURE updateExtItemsTLP IS
  CURSOR cAllCategories IS
    SELECT cat.rt_category_id
    FROM   icx_cat_categories_tl cat
    WHERE  cat.type = ICX_POR_EXT_CLASS.CATEGORY_TYPE
    AND    cat.language = (SELECT language_code
                           FROM   fnd_languages
                           WHERE  installed_flag = 'B')
    AND    EXISTS (SELECT 'category attributes'
                   FROM   icx_por_descriptors_tl des
                   WHERE  des.rt_category_id = cat.rt_category_id)
    AND    EXISTS (SELECT 'items belong to this category'
                   FROM   icx_cat_category_items ci,
                          icx_cat_items_b i
                   WHERE  cat.rt_category_id = ci.rt_category_id
                   AND    i.rt_item_id = ci.rt_item_id);

Line: 1628

    SELECT i.rt_item_id rt_item_id,
           decode(i.object_version_number, 1,
                  i.rt_item_id,
                  i.object_version_number) old_rt_item_id
    FROM   icx_cat_category_items ci,
           icx_cat_items_b i
    WHERE  ci.rt_category_id = pRtCategoryId
    AND    ci.rt_item_id = i.rt_item_id;

Line: 1650

    'Update category attributes of ICX_CAT_EXT_ITEMS_TLP');

Line: 1663

        xRtItemIds.DELETE;

Line: 1664

        xOldRtItemIds.DELETE;

Line: 1705

          UPDATE icx_cat_items_b
          SET    object_version_number = 1
          WHERE  rt_item_id = xRtItemIds(i);

Line: 1731

      'updateExtItemsTLP(' ||xErrLoc||'): '||sqlerrm;

Line: 1735

END updateExtItemsTLP;

Line: 1741

    SELECT p.rowid
    FROM   icx_cat_item_prices p
    WHERE  p.price_type = 'INTERNAL_TEMPLATE'
    AND    NOT EXISTS (SELECT 'already upgraded'
                       FROM   icx_cat_item_prices p2
                       WHERE  p2.rt_item_id = p.rt_item_id
                       AND    p2.price_type = 'INTERNAL_ITEM');

Line: 1759

    xRowIds.DELETE;

Line: 1768

      INSERT INTO ICX_CAT_ITEM_PRICES
      (rt_item_id, price_type,
       active_flag, object_version_number,
       asl_id, supplier_site_id,
       contract_id, contract_line_id,
       template_id, template_line_id,
       inventory_item_id,
       mtl_category_id, org_id,
       search_type, unit_price,
       currency, unit_of_measure,
       functional_price,
       supplier_site_code,
       contract_num, contract_line_num,
       price_list_id, last_update_login,
       last_updated_by, last_update_date,
       created_by, creation_date, request_id,
       program_application_id, program_id, program_update_date)
      SELECT p.rt_item_id, 'INTERNAL_ITEM',
             'N', 1,
             ICX_POR_EXT_ITEM.NULL_NUMBER, ICX_POR_EXT_ITEM.NULL_NUMBER,
             ICX_POR_EXT_ITEM.NULL_NUMBER, ICX_POR_EXT_ITEM.NULL_NUMBER,
             ICX_POR_EXT_ITEM.NULL_NUMBER, ICX_POR_EXT_ITEM.NULL_NUMBER,
             mi.inventory_item_id,
             p.mtl_category_id, p.org_id,
             'INTERNAL',
             mi.list_price_per_unit unit_price,
	     gsb.currency_code currency,
	     NVL(muom.uom_code, mi.primary_uom_code) unit_of_measure,
	     mi.list_price_per_unit functional_price,
             NULL, NULL, NULL, NULL,
             gUpgradeUserId, gUpgradeUserId, sysdate,
	     gUpgradeUserId, sysdate, gUpgradeUserId,
	     gUpgradeUserId, gUpgradeUserId, sysdate
      FROM   icx_cat_item_prices p,
             mtl_system_items_kfv mi,
	     gl_sets_of_books gsb,
	     financials_system_params_all fsp,
	     mtl_units_of_measure_tl muom
      WHERE  p.inventory_item_id = mi.inventory_item_id
      AND    p.org_id = fsp.org_id
      AND    mi.organization_id = fsp.inventory_organization_id
      AND    mi.unit_of_issue = muom.unit_of_measure(+)
      AND    muom.language(+) = ICX_POR_EXTRACTOR.gBaseLang
      AND    fsp.set_of_books_id = gsb.set_of_books_id
      AND    p.rowid = xRowIds(i);

Line: 1845

    SELECT NVL(p.rt_item_id, icx_por_itemid.nextval) rt_item_id,
           i.rt_item_id old_rt_item_id,
           p.rt_item_id internal_rt_item_id,
           i.internal_item_id inventory_item_id,
           i.org_id org_id,
           getPrimaryCategoryId(i.rt_item_id) rt_category_id
    FROM   icx_cat_items_b i,
           icx_cat_item_prices p
    WHERE  i.internal_item_id IS NOT NULL
    AND    p.request_id <> gUpgradePhaseId
    AND    EXISTS (SELECT 'supplier sourced documents'
                   FROM   icx_cat_item_prices p2
                   WHERE  p2.inventory_item_id = i.internal_item_id
                   AND    p2.org_id = i.org_id
                   AND    p2.price_type IN ('BLANKET', 'QUOTATION',
                                            'TEMPLATE', 'ASL'))
    AND    i.internal_item_id = p.inventory_item_id (+)
    AND    i.org_id = p.org_id (+)
    AND    p.price_type(+) = 'INTERNAL_ITEM'
    AND    NOT EXISTS (SELECT 'already upgraded'
                       FROM   icx_cat_item_prices p2
                       WHERE  p2.rt_item_id = i.internal_item_id
                       AND    p2.org_id = i.org_id
                       AND    p2.price_type = 'PURCHASING_ITEM');

Line: 1887

  SELECT language_code INTO xLanguage
  FROM   fnd_languages
  WHERE  installed_flag = 'B';

Line: 1899

    xRtItemIds.DELETE;

Line: 1900

    xOldRtItemIds.DELETE;

Line: 1901

    xInternalRtItemIds.DELETE;

Line: 1902

    xInventoryItemIds.DELETE;

Line: 1903

    xOrgIds.DELETE;

Line: 1904

    xRtCategoryIds.DELETE;

Line: 1919

      INSERT INTO ICX_CAT_ITEM_PRICES
      (rt_item_id, price_type,
       active_flag, object_version_number,
       asl_id, supplier_site_id,
       contract_id, contract_line_id,
       template_id, template_line_id,
       inventory_item_id,
       mtl_category_id, org_id,
       search_type, unit_price,
       currency, unit_of_measure,
       functional_price,
       supplier_site_code,
       contract_num, contract_line_num,
       price_list_id, last_update_login,
       last_updated_by, last_update_date,
       created_by, creation_date, request_id,
       program_application_id, program_id, program_update_date)
      SELECT xRtItemIds(i), 'PURCHASING_ITEM',
             'N', 1,
             ICX_POR_EXT_ITEM.NULL_NUMBER, ICX_POR_EXT_ITEM.NULL_NUMBER,
             ICX_POR_EXT_ITEM.NULL_NUMBER, ICX_POR_EXT_ITEM.NULL_NUMBER,
             ICX_POR_EXT_ITEM.NULL_NUMBER, ICX_POR_EXT_ITEM.NULL_NUMBER,
             mi.inventory_item_id,
             mic.category_id mtl_category_id, xOrgIds(i),
             'SUPPLIER',
             mi.list_price_per_unit unit_price,
	     gsb.currency_code currency,
	     mi.primary_uom_code unit_of_measure,
	     mi.list_price_per_unit functional_price,
             NULL, NULL, NULL, NULL,
             gUpgradeUserId, gUpgradeUserId, sysdate,
	     gUpgradeUserId, sysdate, gUpgradePhaseId,
	     gUpgradeUserId, gUpgradeUserId, sysdate
      FROM   mtl_system_items_kfv mi,
             mtl_item_categories mic,
	     gl_sets_of_books gsb,
	     financials_system_params_all fsp,
             --Bug#3581356
             --Since categories are already upgraded,
             --so join with icx_cat_categories_tl to get the valid category
             icx_cat_categories_tl ictl
      WHERE  mi.inventory_item_id = xInventoryItemIds(i)
      AND    fsp.org_id = xOrgIds(i)
      AND    mi.organization_id = fsp.inventory_organization_id
      AND    mi.inventory_item_id = mic.inventory_item_id
      AND    mic.organization_id = mi.organization_id
             --Bug#3581356
             --Join with icx_cat_categories_tl to get the valid category
             --Add the join between gl_sets_of_books and
             --financials_system_params_all
      AND    fsp.set_of_books_id = gsb.set_of_books_id
      AND    ictl.key = to_char(mic.category_id)
      AND    ictl.language = xLanguage
      AND    ictl.type = 2;

Line: 1986

	gIExtractorUpdatedFlags(xCount) := 'Y';

Line: 2040

PROCEDURE updateRequestId IS

  xErrLoc              PLS_INTEGER;

Line: 2047

    SELECT p.rt_item_id
    FROM  icx_cat_item_prices p
    WHERE p.request_id = CREATE_PURCHASING_PHASE;

Line: 2054

      'update request_id of item prices table');

Line: 2058

      xRtItemIds.DELETE;

Line: 2068

        UPDATE ICX_CAT_ITEM_PRICES
        SET request_id = gUpgradeUserId
        WHERE rt_item_id = xRtItemIds(i);

Line: 2085

        'updateRequestId(' ||xErrLoc||'): '||sqlerrm;

Line: 2089

END updateRequestId;

Line: 2102

    INSERT INTO ICX_CAT_PRICE_LISTS
    (price_list_id, name, supplier_id, buyer_id,
     description, currency, creation_date, created_by,
     last_update_date, last_updated_by, last_update_login,
     request_id, begindate, enddate, status,
     published_date, outdated_date, approval_date,
     rejected_date, deleted_date, buyercomments,
     action, type, parent_header_id)
    SELECT
     header_id, name, supplier_id, buyer_id,
     description, currency_code, creation_date, created_by,
     sysdate, gUpgradeUserId, gUpgradeUserId,
     job_number, begindate, enddate, status,
     published_date, outdated_date, approval_date,
     rejected_date, deleted_date, buyercomments,
     action, type, parent_header_id
    FROM  ICX_POR_PRICE_LISTS old_list
    WHERE NOT EXISTS (SELECT 'Already upgraded'
                      FROM   ICX_CAT_PRICE_LISTS new_list
                      WHERE  old_list.header_id = new_list.price_list_id)
    AND   ROWNUM <= gCommitSize;

Line: 2141

PROCEDURE updateFavoriteList IS
  xErrLoc		PLS_INTEGER;

Line: 2156

    UPDATE por_favorite_list_lines
    SET    rt_item_id = gUpFavRtItemIds(i)
    WHERE  rowid = gUpFavRowIds(i);

Line: 2160

  gUpFavRtItemIds.DELETE;

Line: 2161

  gUpFavRowIds.DELETE;

Line: 2168

      'updateFavoriteList(' ||xErrLoc||'): '||sqlerrm;

Line: 2172

END updateFavoriteList;

Line: 2175

PROCEDURE insertFavoriteList IS
  xErrLoc		PLS_INTEGER;

Line: 2190

    INSERT INTO por_favorite_list_lines
    (favorite_list_line_id,
     favorite_list_id,
     last_update_date,
     last_updated_by,
     creation_date,
     created_by,
     last_update_login,
     source_doc_header_id,
     source_doc_line_id,
     item_id,
     item_description,
     line_type_id,
     item_revision,
     category_id,
     unit_meas_lookup_code,
     unit_price,
     suggested_vendor_id,
     suggested_vendor_name,
     suggested_vendor_site_id,
     suggested_vendor_site,
     suggested_vendor_contact_id,
     suggested_vendor_contact,
     supplier_url,
     suggested_buyer_id,
     suggested_buyer,
     supplier_item_num,
     manufacturer_id,
     manufacturer_name,
     manufacturer_part_number,
     rfq_required_flag,
     attribute_category,
     attribute1,
     attribute2,
     attribute3,
     attribute4,
     attribute5,
     attribute6,
     attribute7,
     attribute8,
     attribute9,
     attribute10,
     attribute11,
     attribute12,
     attribute13,
     attribute14,
     attribute15,
     category,
     rt_item_id,
     rt_category_id,
     suggested_vendor_contact_phone,
     new_supplier,
     asl_id,
     template_name,
     template_line_num,
     price_list_id,
     currency,
     rate_type,
     rate)
    SELECT
     por_favorite_list_lines_s.nextval,
     favorite_list_id,
     sysdate,
     gUpgradeUserId,
     sysdate,
     gUpgradeUserId,
     gUpgradeUserId,
     source_doc_header_id,
     source_doc_line_id,
     item_id,
     item_description,
     line_type_id,
     item_revision,
     category_id,
     unit_meas_lookup_code,
     unit_price,
     suggested_vendor_id,
     suggested_vendor_name,
     suggested_vendor_site_id,
     suggested_vendor_site,
     suggested_vendor_contact_id,
     suggested_vendor_contact,
     supplier_url,
     suggested_buyer_id,
     suggested_buyer,
     supplier_item_num,
     manufacturer_id,
     manufacturer_name,
     manufacturer_part_number,
     rfq_required_flag,
     attribute_category,
     attribute1,
     attribute2,
     attribute3,
     attribute4,
     attribute5,
     attribute6,
     attribute7,
     attribute8,
     attribute9,
     attribute10,
     attribute11,
     attribute12,
     attribute13,
     attribute14,
     attribute15,
     category,
     gInFavRtItemIds(i),
     rt_category_id,
     suggested_vendor_contact_phone,
     new_supplier,
     asl_id,
     template_name,
     template_line_num,
     price_list_id,
     currency,
     rate_type,
     rate
    FROM  por_favorite_list_lines
    WHERE rowid = gInFavRowIds(i);

Line: 2311

  gInFavRowIds.DELETE;

Line: 2312

  gInFavRowIds.DELETE;

Line: 2319

      'insertFavoriteList(' ||xErrLoc||'): '||sqlerrm;

Line: 2323

END insertFavoriteList;

Line: 2328

    SELECT rowid,
           nvl(source_doc_header_id, ICX_POR_EXT_ITEM.NULL_NUMBER) contract_id,
           nvl(source_doc_line_id, ICX_POR_EXT_ITEM.NULL_NUMBER) contract_line_id,
           nvl(asl_id, ICX_POR_EXT_ITEM.NULL_NUMBER) asl_id,
           nvl(template_name, to_char(ICX_POR_EXT_ITEM.NULL_NUMBER)) template_id,
           nvl(template_line_num, ICX_POR_EXT_ITEM.NULL_NUMBER) template_line_id,
           nvl(item_id, ICX_POR_EXT_ITEM.NULL_NUMBER) inventory_item_id,
           price_list_id,
           rt_item_id
    FROM   por_favorite_list_lines
    WHERE  rt_item_id IS NOT NULL;

Line: 2358

        SELECT distinct rt_item_id
        FROM   icx_cat_item_prices
        WHERE  contract_id = favorite.contract_id
        AND    contract_line_id = favorite.contract_line_id
        AND    asl_id = favorite.asl_id
        AND    template_id = favorite.template_id
        AND    template_line_id = favorite.template_line_id
        AND    inventory_item_id = favorite.inventory_item_id
        AND    price_list_id IS NULL;

Line: 2369

        SELECT distinct p.rt_item_id
        FROM   icx_cat_item_prices p,
               icx_cat_items_b i,
               icx_por_items oi
        WHERE  p.price_list_id = favorite.price_list_id
        AND    p.rt_item_id = i.rt_item_id
        AND    oi.rt_item_id = favorite.rt_item_id
        AND    i.supplier = oi.a1
        AND    i.supplier_part_num = oi.a3;

Line: 2380

    xRtItemIds.DELETE;

Line: 2408

      updateFavoriteList;

Line: 2412

      insertFavoriteList;

Line: 2418

  updateFavoriteList;

Line: 2420

  insertFavoriteList;

Line: 2507

  SELECT 1
  INTO	 xResult
  FROM   dual
  WHERE  EXISTS (SELECT 'schema records'
                 FROM   icx_cat_categories_tl
                 WHERE  rt_category_id > 0)
  OR     EXISTS (SELECT 'data records'
                 FROM   icx_cat_items_b);

Line: 2575

    SELECT item.rt_item_id,
           getOldPrimaryCategoryId(item.rt_item_id) rt_category_id
    FROM   icx_por_items item
    WHERE  NOT EXISTS (SELECT 'already upgraded'
                       FROM   icx_cat_items_b new_item
                       WHERE  item.rt_item_id = new_item.rt_item_id)
    AND    (EXISTS (SELECT 'extracted price'
                    FROM   icx_por_oracle_item_subtable sub
                    WHERE  sub.rt_item_id = item.rt_item_id) OR
            EXISTS (SELECT 'bulkloaded price'
                    FROM   icx_por_price_list_lines pll
                    WHERE  pll.item_id = item.rt_item_id));

Line: 2633

  updateRequestId;

Line: 2637

    updateExtItemsTLP;