DBA Data[Home] [Help]

APPS.ICX_CAT_FPI_UPGRADE dependencies on ICX_CAT_ITEMS_B

Line 43: -- Global PL/SQL tables for ICX_CAT_ITEMS_B, ICX_CAT_ITEMS_TLP,

39:
40: --------------------------------------------------------------
41: -- Global PL/SQL Tables --
42: --------------------------------------------------------------
43: -- Global PL/SQL tables for ICX_CAT_ITEMS_B, ICX_CAT_ITEMS_TLP,
44: -- ICX_CAT_EXT_ITEMS_TLP, ICX_CAT_CATEGORY_ITEMS
45: gIRtItemIds DBMS_SQL.NUMBER_TABLE;
46: gIOldRtItemIds DBMS_SQL.NUMBER_TABLE;
47: gIOrgIds DBMS_SQL.NUMBER_TABLE;

Line 833: xSupplierPartNum ICX_CAT_ITEMS_B.supplier_part_num%TYPE;

829: xReturnErr VARCHAR2(2000);
830:
831: xOrgId NUMBER;
832: xSupplierId NUMBER;
833: xSupplierPartNum ICX_CAT_ITEMS_B.supplier_part_num%TYPE;
834: xSupplierSiteId NUMBER;
835: xRtItemId NUMBER;
836: xType PLS_INTEGER;
837: xActiveFlag VARCHAR2(1);

Line 1118: -- Move data into ICX_CAT_ITEMS_B, ICX_CAT_ITEMS_TLP, ICX_CAT_CATEGORY_ITEMS

1114: ICX_POR_EXT_UTL.pushError(xReturnErr);
1115: raise ICX_POR_EXT_UTL.gException;
1116: END processItems;
1117:
1118: -- Move data into ICX_CAT_ITEMS_B, ICX_CAT_ITEMS_TLP, ICX_CAT_CATEGORY_ITEMS
1119: PROCEDURE moveItems IS
1120: xErrLoc PLS_INTEGER;
1121: xReturnErr VARCHAR2(2000);
1122:

Line 1141: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'ICX_CAT_ITEMS_B');

1137: END LOOP;
1138: END IF;
1139:
1140: xErrLoc := 100;
1141: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'ICX_CAT_ITEMS_B');
1142:
1143: -- Let's use object_version_number to store old_rt_item_id for now
1144: FORALL i IN 1..gIRtItemIds.COUNT
1145: INSERT INTO ICX_CAT_ITEMS_B

Line 1145: INSERT INTO ICX_CAT_ITEMS_B

1141: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'ICX_CAT_ITEMS_B');
1142:
1143: -- Let's use object_version_number to store old_rt_item_id for now
1144: FORALL i IN 1..gIRtItemIds.COUNT
1145: INSERT INTO ICX_CAT_ITEMS_B
1146: (rt_item_id, object_version_number, org_id,
1147: supplier_id,
1148: supplier, supplier_part_num, supplier_part_auxid,
1149: internal_item_id, internal_item_num,

Line 1607: -- Note: We use icx_cat_items_b.object_version_number to

1603: raise ICX_POR_EXT_UTL.gException;
1604: END moveData;
1605:
1606: -- Update category attributes of ICX_CAT_EXT_ITEMS_TLP
1607: -- Note: We use icx_cat_items_b.object_version_number to
1608: -- store old_rt_item_id during the upgrade process
1609: PROCEDURE updateExtItemsTLP IS
1610: CURSOR cAllCategories IS
1611: SELECT cat.rt_category_id

Line 1622: icx_cat_items_b i

1618: FROM icx_por_descriptors_tl des
1619: WHERE des.rt_category_id = cat.rt_category_id)
1620: AND EXISTS (SELECT 'items belong to this category'
1621: FROM icx_cat_category_items ci,
1622: icx_cat_items_b i
1623: WHERE cat.rt_category_id = ci.rt_category_id
1624: AND i.rt_item_id = ci.rt_item_id);
1625:
1626: -- Cursor for all rt_item_ids with a rt_category_id

Line 1633: icx_cat_items_b i

1629: decode(i.object_version_number, 1,
1630: i.rt_item_id,
1631: i.object_version_number) old_rt_item_id
1632: FROM icx_cat_category_items ci,
1633: icx_cat_items_b i
1634: WHERE ci.rt_category_id = pRtCategoryId
1635: AND ci.rt_item_id = i.rt_item_id;
1636:
1637: xErrLoc PLS_INTEGER;

Line 1699: 'Restore ICX_CAT_ITEMS_B.object_version_number to 1');

1695:
1696: xErrLoc := 300;
1697: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL THEN
1698: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
1699: 'Restore ICX_CAT_ITEMS_B.object_version_number to 1');
1700: END IF;
1701:
1702: xErrLoc := 320;
1703: -- Let's restore object_version_number to 1

Line 1705: UPDATE icx_cat_items_b

1701:
1702: xErrLoc := 320;
1703: -- Let's restore object_version_number to 1
1704: FORALL i IN 1..xRtItemIds.COUNT
1705: UPDATE icx_cat_items_b
1706: SET object_version_number = 1
1707: WHERE rt_item_id = xRtItemIds(i);
1708:
1709: COMMIT;

Line 1851: FROM icx_cat_items_b i,

1847: p.rt_item_id internal_rt_item_id,
1848: i.internal_item_id inventory_item_id,
1849: i.org_id org_id,
1850: getPrimaryCategoryId(i.rt_item_id) rt_category_id
1851: FROM icx_cat_items_b i,
1852: icx_cat_item_prices p
1853: WHERE i.internal_item_id IS NOT NULL
1854: AND p.request_id <> gUpgradePhaseId
1855: AND EXISTS (SELECT 'supplier sourced documents'

Line 2371: icx_cat_items_b i,

2367: ELSE
2368: OPEN cMatchRtItemIds FOR
2369: SELECT distinct p.rt_item_id
2370: FROM icx_cat_item_prices p,
2371: icx_cat_items_b i,
2372: icx_por_items oi
2373: WHERE p.price_list_id = favorite.price_list_id
2374: AND p.rt_item_id = i.rt_item_id
2375: AND oi.rt_item_id = favorite.rt_item_id

Line 2514: FROM icx_cat_items_b);

2510: WHERE EXISTS (SELECT 'schema records'
2511: FROM icx_cat_categories_tl
2512: WHERE rt_category_id > 0)
2513: OR EXISTS (SELECT 'data records'
2514: FROM icx_cat_items_b);
2515: RETURN xResult;
2516: EXCEPTION
2517: WHEN NO_DATA_FOUND THEN
2518: xResult := 0;

Line 2532: -- ICX_CAT_ITEMS_B

2528: xErrLoc := 50;
2529: xIcxSchema := ICX_POR_EXT_UTL.getIcxSchema;
2530:
2531: xErrLoc := 100;
2532: -- ICX_CAT_ITEMS_B
2533: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
2534: xIcxSchema || '.icx_cat_items_b';
2535:
2536: xErrLoc := 200;

Line 2534: xIcxSchema || '.icx_cat_items_b';

2530:
2531: xErrLoc := 100;
2532: -- ICX_CAT_ITEMS_B
2533: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
2534: xIcxSchema || '.icx_cat_items_b';
2535:
2536: xErrLoc := 200;
2537: -- ICX_CAT_ITEMS_TLP
2538: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||

Line 2579: FROM icx_cat_items_b new_item

2575: SELECT item.rt_item_id,
2576: getOldPrimaryCategoryId(item.rt_item_id) rt_category_id
2577: FROM icx_por_items item
2578: WHERE NOT EXISTS (SELECT 'already upgraded'
2579: FROM icx_cat_items_b new_item
2580: WHERE item.rt_item_id = new_item.rt_item_id)
2581: AND (EXISTS (SELECT 'extracted price'
2582: FROM icx_por_oracle_item_subtable sub
2583: WHERE sub.rt_item_id = item.rt_item_id) OR