eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.ICX_POR_EXT_ITEM dependencies on ICX_CAT_ITEM_PRICES

Line 37: supplier_site_code icx_cat_item_prices.supplier_site_code%TYPE,

33: last_update_date DATE,
34: org_id NUMBER,
35: supplier_id NUMBER,
36: supplier icx_cat_items_b.supplier%TYPE,
37: supplier_site_code icx_cat_item_prices.supplier_site_code%TYPE,
38: supplier_part_num icx_cat_items_b.supplier_part_num%TYPE,
39: internal_item_id NUMBER,
40: internal_item_num icx_cat_items_b.internal_item_num%TYPE,
41: inventory_organization_id NUMBER,

Line 49: price_type icx_cat_item_prices.price_type%TYPE,

45: category_key icx_cat_categories_tl.key%TYPE,
46: description icx_cat_items_tlp.description%TYPE,
47: picture icx_cat_items_tlp.picture%TYPE,
48: picture_url icx_cat_items_tlp.picture_url%TYPE,
49: price_type icx_cat_item_prices.price_type%TYPE,
50: asl_id NUMBER,
51: supplier_site_id NUMBER,
52: contract_id NUMBER,
53: contract_line_id NUMBER,

Line 54: template_id icx_cat_item_prices.template_id%TYPE,

50: asl_id NUMBER,
51: supplier_site_id NUMBER,
52: contract_id NUMBER,
53: contract_line_id NUMBER,
54: template_id icx_cat_item_prices.template_id%TYPE,
55: template_line_id NUMBER,
56: price_search_type icx_cat_item_prices.search_type%TYPE,
57: --FPJ FPSL Extractor Changes
58: --unit_price column will hold amount for items with Fixed Price Services line_type

Line 56: price_search_type icx_cat_item_prices.search_type%TYPE,

52: contract_id NUMBER,
53: contract_line_id NUMBER,
54: template_id icx_cat_item_prices.template_id%TYPE,
55: template_line_id NUMBER,
56: price_search_type icx_cat_item_prices.search_type%TYPE,
57: --FPJ FPSL Extractor Changes
58: --unit_price column will hold amount for items with Fixed Price Services line_type
59: --For all other items it will hold price
60: unit_price NUMBER,

Line 62: value_basis icx_cat_item_prices.value_basis%TYPE,

58: --unit_price column will hold amount for items with Fixed Price Services line_type
59: --For all other items it will hold price
60: unit_price NUMBER,
61: --FPJ FPSL Extractor Changes
62: value_basis icx_cat_item_prices.value_basis%TYPE,
63: purchase_basis icx_cat_item_prices.purchase_basis%TYPE,
64: allow_price_override_flag icx_cat_item_prices.allow_price_override_flag%TYPE,
65: not_to_exceed_price NUMBER,
66: -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project

Line 63: purchase_basis icx_cat_item_prices.purchase_basis%TYPE,

59: --For all other items it will hold price
60: unit_price NUMBER,
61: --FPJ FPSL Extractor Changes
62: value_basis icx_cat_item_prices.value_basis%TYPE,
63: purchase_basis icx_cat_item_prices.purchase_basis%TYPE,
64: allow_price_override_flag icx_cat_item_prices.allow_price_override_flag%TYPE,
65: not_to_exceed_price NUMBER,
66: -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
67: suggested_quantity NUMBER,

Line 64: allow_price_override_flag icx_cat_item_prices.allow_price_override_flag%TYPE,

60: unit_price NUMBER,
61: --FPJ FPSL Extractor Changes
62: value_basis icx_cat_item_prices.value_basis%TYPE,
63: purchase_basis icx_cat_item_prices.purchase_basis%TYPE,
64: allow_price_override_flag icx_cat_item_prices.allow_price_override_flag%TYPE,
65: not_to_exceed_price NUMBER,
66: -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
67: suggested_quantity NUMBER,
68: -- FPJ Bug# 3110297 jingyu Add negotiated flag

Line 69: negotiated_by_preparer_flag icx_cat_item_prices.negotiated_by_preparer_flag%TYPE,

65: not_to_exceed_price NUMBER,
66: -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
67: suggested_quantity NUMBER,
68: -- FPJ Bug# 3110297 jingyu Add negotiated flag
69: negotiated_by_preparer_flag icx_cat_item_prices.negotiated_by_preparer_flag%TYPE,
70: currency icx_cat_item_prices.currency%TYPE,
71: unit_of_measure icx_cat_item_prices.unit_of_measure%TYPE,
72: functional_price NUMBER,
73: contract_num icx_cat_item_prices.contract_num%TYPE,

Line 70: currency icx_cat_item_prices.currency%TYPE,

66: -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
67: suggested_quantity NUMBER,
68: -- FPJ Bug# 3110297 jingyu Add negotiated flag
69: negotiated_by_preparer_flag icx_cat_item_prices.negotiated_by_preparer_flag%TYPE,
70: currency icx_cat_item_prices.currency%TYPE,
71: unit_of_measure icx_cat_item_prices.unit_of_measure%TYPE,
72: functional_price NUMBER,
73: contract_num icx_cat_item_prices.contract_num%TYPE,
74: contract_line_num NUMBER,

Line 71: unit_of_measure icx_cat_item_prices.unit_of_measure%TYPE,

67: suggested_quantity NUMBER,
68: -- FPJ Bug# 3110297 jingyu Add negotiated flag
69: negotiated_by_preparer_flag icx_cat_item_prices.negotiated_by_preparer_flag%TYPE,
70: currency icx_cat_item_prices.currency%TYPE,
71: unit_of_measure icx_cat_item_prices.unit_of_measure%TYPE,
72: functional_price NUMBER,
73: contract_num icx_cat_item_prices.contract_num%TYPE,
74: contract_line_num NUMBER,
75: manufacturer ICX_CAT_ITEMS_TLP.manufacturer%TYPE,

Line 73: contract_num icx_cat_item_prices.contract_num%TYPE,

69: negotiated_by_preparer_flag icx_cat_item_prices.negotiated_by_preparer_flag%TYPE,
70: currency icx_cat_item_prices.currency%TYPE,
71: unit_of_measure icx_cat_item_prices.unit_of_measure%TYPE,
72: functional_price NUMBER,
73: contract_num icx_cat_item_prices.contract_num%TYPE,
74: contract_line_num NUMBER,
75: manufacturer ICX_CAT_ITEMS_TLP.manufacturer%TYPE,
76: manufacturer_part_num ICX_CAT_ITEMS_TLP.manufacturer_part_num%TYPE,
77: rate_type ICX_CAT_ITEM_PRICES.rate_type%TYPE,

Line 77: rate_type ICX_CAT_ITEM_PRICES.rate_type%TYPE,

73: contract_num icx_cat_item_prices.contract_num%TYPE,
74: contract_line_num NUMBER,
75: manufacturer ICX_CAT_ITEMS_TLP.manufacturer%TYPE,
76: manufacturer_part_num ICX_CAT_ITEMS_TLP.manufacturer_part_num%TYPE,
77: rate_type ICX_CAT_ITEM_PRICES.rate_type%TYPE,
78: rate_date DATE,
79: rate NUMBER,
80: supplier_number ICX_CAT_ITEM_PRICES.supplier_number%TYPE,
81: supplier_contact_id NUMBER,

Line 80: supplier_number ICX_CAT_ITEM_PRICES.supplier_number%TYPE,

76: manufacturer_part_num ICX_CAT_ITEMS_TLP.manufacturer_part_num%TYPE,
77: rate_type ICX_CAT_ITEM_PRICES.rate_type%TYPE,
78: rate_date DATE,
79: rate NUMBER,
80: supplier_number ICX_CAT_ITEM_PRICES.supplier_number%TYPE,
81: supplier_contact_id NUMBER,
82: item_revision ICX_CAT_ITEM_PRICES.item_revision%TYPE,
83: line_type_id NUMBER,
84: buyer_id NUMBER,

Line 82: item_revision ICX_CAT_ITEM_PRICES.item_revision%TYPE,

78: rate_date DATE,
79: rate NUMBER,
80: supplier_number ICX_CAT_ITEM_PRICES.supplier_number%TYPE,
81: supplier_contact_id NUMBER,
82: item_revision ICX_CAT_ITEM_PRICES.item_revision%TYPE,
83: line_type_id NUMBER,
84: buyer_id NUMBER,
85: global_agreement_flag VARCHAR2(1),
86: status NUMBER,

Line 137: -- Update ICX_CAT_ITEM_PRICES

133:
134: --------------------------------------------------------------
135: -- Global PL/SQL Tables --
136: --------------------------------------------------------------
137: -- Update ICX_CAT_ITEM_PRICES
138: gUPRtItemIds DBMS_SQL.NUMBER_TABLE;
139: gUPPriceTypes DBMS_SQL.VARCHAR2_TABLE;
140: gUPAslIds DBMS_SQL.NUMBER_TABLE;
141: gUPSupplierSiteIds DBMS_SQL.NUMBER_TABLE;

Line 179: -- Update ICX_CAT_ITEM_PRICES for global agreements

175: gUPActiveFlags DBMS_SQL.VARCHAR2_TABLE;
176: gUPLastUpdateDates DBMS_SQL.DATE_TABLE;
177:
178:
179: -- Update ICX_CAT_ITEM_PRICES for global agreements
180: gUPGRtItemIds DBMS_SQL.NUMBER_TABLE;
181: gUPGContractIds DBMS_SQL.NUMBER_TABLE;
182: gUPGContractLineIds DBMS_SQL.NUMBER_TABLE;
183: gUPGInventoryItemIds DBMS_SQL.NUMBER_TABLE;

Line 199: -- Insert ICX_CAT_ITEM_PRICES

195: gUPGCurrencys DBMS_SQL.VARCHAR2_TABLE;
196: gUPGUnitOfMeasures DBMS_SQL.VARCHAR2_TABLE;
197: gUPGFunctionalPrices DBMS_SQL.NUMBER_TABLE;
198:
199: -- Insert ICX_CAT_ITEM_PRICES
200: gIPRtItemIds DBMS_SQL.NUMBER_TABLE;
201: gIPPriceTypes DBMS_SQL.VARCHAR2_TABLE;
202: gIPAslIds DBMS_SQL.NUMBER_TABLE;
203: gIPSupplierSiteIds DBMS_SQL.NUMBER_TABLE;

Line 355: -- Update ICX_CAT_ITEM_PRICES for local global agreements

351: -- Set item source type
352: gSIITRtItemIds DBMS_SQL.NUMBER_TABLE;
353: gSITRtItemIds DBMS_SQL.NUMBER_TABLE;
354:
355: -- Update ICX_CAT_ITEM_PRICES for local global agreements
356: gUPGASupplierSiteIds DBMS_SQL.NUMBER_TABLE;
357: gUPGAContractIds DBMS_SQL.NUMBER_TABLE;
358: gUPGAContractLineIds DBMS_SQL.NUMBER_TABLE;
359: gUPGAFunctionalPrices DBMS_SQL.NUMBER_TABLE;

Line 369: -- Insert ICX_CAT_ITEM_PRICES for local global agreements

365: gUPGARates DBMS_SQL.NUMBER_TABLE;
366: -- bug 3298502: Enabled Org Ids
367: gUPGAOrgIds DBMS_SQL.NUMBER_TABLE;
368:
369: -- Insert ICX_CAT_ITEM_PRICES for local global agreements
370: gIPGARtItemIds DBMS_SQL.NUMBER_TABLE;
371: gIPGALocalRtItemIds DBMS_SQL.NUMBER_TABLE;
372: gIPGASupplierSiteIds DBMS_SQL.NUMBER_TABLE;
373: gIPGAContractIds DBMS_SQL.NUMBER_TABLE;

Line 410: -- Update ICX_CAT_ITEM_PRICES

406: --------------------------------------------------------------
407: PROCEDURE clearTables(pMode IN VARCHAR2) IS
408: BEGIN
409: IF (pMode IN ('ALL', 'UPDATE_PRICES')) THEN
410: -- Update ICX_CAT_ITEM_PRICES
411: gUPRtItemIds.DELETE;
412: gUPPriceTypes.DELETE;
413: gUPAslIds.DELETE;
414: gUPSupplierSiteIds.DELETE;

Line 454: -- Update ICX_CAT_ITEM_PRICES for global agreements

450:
451: END IF;
452:
453: IF (pMode IN ('ALL', 'UPDATE_PRICES_G')) THEN
454: -- Update ICX_CAT_ITEM_PRICES for global agreements
455: gUPGRtItemIds.DELETE;
456: gUPGContractIds.DELETE;
457: gUPGContractLineIds.DELETE;
458: gUPGInventoryItemIds.DELETE;

Line 476: -- Insert ICX_CAT_ITEM_PRICES

472: gUPGFunctionalPrices.DELETE;
473: END IF;
474:
475: IF (pMode IN ('ALL', 'INSERT_PRICES')) THEN
476: -- Insert ICX_CAT_ITEM_PRICES
477: gIPRtItemIds.DELETE;
478: gIPPriceTypes.DELETE;
479: gIPAslIds.DELETE;
480: gIPSupplierSiteIds.DELETE;

Line 664: -- Set ICX_CAT_ITEM_PRICES.active_flag

660: gDIRtItemIds.DELETE;
661: END IF;
662:
663: IF (pMode IN ('ALL', 'SET_ACTIVE_FLAG')) THEN
664: -- Set ICX_CAT_ITEM_PRICES.active_flag
665: gSAPriceTypes.DELETE;
666: gSARtItemIds.DELETE;
667: gSARowIds.DELETE;
668: gSAActiveFlags.DELETE;

Line 672: -- Update ICX_CAT_ITEM_PRICES for local global agreements

668: gSAActiveFlags.DELETE;
669: END IF;
670:
671: IF (pMode IN ('ALL', 'UPDATE_PRICES_GA')) THEN
672: -- Update ICX_CAT_ITEM_PRICES for local global agreements
673: gUPGASupplierSiteIds.DELETE;
674: gUPGAContractIds.DELETE;
675: gUPGAContractLineIds.DELETE;
676: gUPGAFunctionalPrices.DELETE;

Line 688: -- Insert ICX_CAT_ITEM_PRICES for local global agreements

684: gUPGAOrgIds.DELETE;
685: END IF;
686:
687: IF (pMode IN ('ALL', 'INSERT_PRICES_GA')) THEN
688: -- Insert ICX_CAT_ITEM_PRICES for local global agreements
689: gIPGARtItemIds.DELETE;
690: gIPGALocalRtItemIds.DELETE;
691: gIPGASupplierSiteIds.DELETE;
692: gIPGAContractIds.DELETE;

Line 733: -- Update ICX_CAT_ITEM_PRICES

729: pMode IN VARCHAR2) RETURN varchar2 IS
730: xShot varchar2(4000) := 'SnapShot('||pMode||')['||pIndex||']--';
731: BEGIN
732: IF (pMode = 'UPDATE_PRICES') THEN
733: -- Update ICX_CAT_ITEM_PRICES
734: xShot := xShot || ' gUPRtItemIds: ' ||
735: ICX_POR_EXT_UTL.getTableElement(gUPRtItemIds, pIndex) || ', ';
736: xShot := xShot || ' gUPPriceTypes: ' ||
737: ICX_POR_EXT_UTL.getTableElement(gUPPriceTypes, pIndex) || ', ';

Line 808: -- Update ICX_CAT_ITEM_PRICES for global agreements

804: ICX_POR_EXT_UTL.getTableElement(gUPPriceRowIds, pIndex);
805: xShot := xShot || ' gUPLastUpdateDates: ' ||
806: ICX_POR_EXT_UTL.getTableElement(gUPLastUpdateDates, pIndex);
807: ELSIF (pMode = 'UPDATE_PRICES_G') THEN
808: -- Update ICX_CAT_ITEM_PRICES for global agreements
809: xShot := xShot || ' gUPGRtItemIds: ' ||
810: ICX_POR_EXT_UTL.getTableElement(gUPGRtItemIds, pIndex) || ', ';
811: xShot := xShot || ' gUPGContractIds: ' ||
812: ICX_POR_EXT_UTL.getTableElement(gUPGContractIds, pIndex) || ', ';

Line 844: -- Insert ICX_CAT_ITEM_PRICES

840: ICX_POR_EXT_UTL.getTableElement(gUPGUnitOfMeasures, pIndex) || ', ';
841: xShot := xShot || ' gUPGFunctionalPrices: ' ||
842: ICX_POR_EXT_UTL.getTableElement(gUPGFunctionalPrices, pIndex);
843: ELSIF (pMode = 'INSERT_PRICES') THEN
844: -- Insert ICX_CAT_ITEM_PRICES
845: xShot := xShot || ' gIPRtItemIds: ' ||
846: ICX_POR_EXT_UTL.getTableElement(gIPRtItemIds, pIndex) || ', ';
847: xShot := xShot || ' gIPPriceTypes: ' ||
848: ICX_POR_EXT_UTL.getTableElement(gIPPriceTypes, pIndex) || ', ';

Line 1097: -- Update ICX_CAT_ITEM_PRICES for local global agreements

1093: -- Delete Items without price
1094: xShot := xShot || ' gDIRtItemIds: ' ||
1095: ICX_POR_EXT_UTL.getTableElement(gDIRtItemIds, pIndex);
1096: ELSIF (pMode = 'UPDATE_PRICES_GA') THEN
1097: -- Update ICX_CAT_ITEM_PRICES for local global agreements
1098: xShot := xShot || ' gUPGASupplierSiteIds: ' ||
1099: ICX_POR_EXT_UTL.getTableElement(gUPGASupplierSiteIds, pIndex) || ', ';
1100: xShot := xShot || ' gUPGAContractIds: ' ||
1101: ICX_POR_EXT_UTL.getTableElement(gUPGAContractIds, pIndex) || ', ';

Line 1111: -- Insert ICX_CAT_ITEM_PRICES for local global agreements

1107: ICX_POR_EXT_UTL.getTableElement(gUPGASupplierSiteCodes, pIndex);
1108: xShot := xShot || ' gUPGAOrgIds: ' ||
1109: ICX_POR_EXT_UTL.getTableElement(gUPGAOrgIds, pIndex);
1110: ELSIF (pMode = 'INSERT_PRICES_GA') THEN
1111: -- Insert ICX_CAT_ITEM_PRICES for local global agreements
1112: xShot := xShot || ' gIPGARtItemIds: ' ||
1113: ICX_POR_EXT_UTL.getTableElement(gIPGARtItemIds, pIndex) || ', ';
1114: xShot := xShot || ' gIPGALocalRtItemIds: ' ||
1115: ICX_POR_EXT_UTL.getTableElement(gIPGALocalRtItemIds, pIndex) || ', ';

Line 1163: -- Set ICX_CAT_ITEM_PRICES.active_flag

1159: ICX_POR_EXT_UTL.getTableElement(gSLRRowIds, pIndex) || ', ';
1160: xShot := xShot || ' gSLRALocalRtItemIds: ' ||
1161: ICX_POR_EXT_UTL.getTableElement(gSLRALocalRtItemIds, pIndex);
1162: ELSIF (pMode = 'SET_ACTIVE_FLAG') THEN
1163: -- Set ICX_CAT_ITEM_PRICES.active_flag
1164: xShot := xShot || ' gSAPriceTypes: ' ||
1165: ICX_POR_EXT_UTL.getTableElement(gSAPriceTypes, pIndex) || ', ';
1166: xShot := xShot || ' gSARtItemIds: ' ||
1167: ICX_POR_EXT_UTL.getTableElement(gSARtItemIds, pIndex);

Line 1369: FROM icx_cat_item_prices p

1365: BEGIN
1366: IF p_price_type = 'PURCHASING_ITEM' THEN
1367: SELECT 'N'
1368: INTO xActiveFlag
1369: FROM icx_cat_item_prices p
1370: WHERE p.rowid = p_price_row_id
1371: AND EXISTS (SELECT 'documents'
1372: FROM icx_cat_item_prices p2
1373: WHERE p.org_id = p2.org_id

Line 1372: FROM icx_cat_item_prices p2

1368: INTO xActiveFlag
1369: FROM icx_cat_item_prices p
1370: WHERE p.rowid = p_price_row_id
1371: AND EXISTS (SELECT 'documents'
1372: FROM icx_cat_item_prices p2
1373: WHERE p.org_id = p2.org_id
1374: AND p.inventory_item_id = p2.inventory_item_id
1375: AND p2.price_type IN ('TEMPLATE', 'BLANKET',
1376: 'QUOTATION', 'GLOBAL_AGREEMENT',

Line 1381: FROM icx_cat_item_prices p

1377: 'ASL', 'BULKLOAD', 'CONTRACT'));
1378: ELSIF p_price_type = 'ASL' THEN
1379: SELECT 'N'
1380: INTO xActiveFlag
1381: FROM icx_cat_item_prices p
1382: WHERE p.rowid = p_price_row_id
1383: AND (EXISTS (SELECT 'documents'
1384: FROM icx_cat_item_prices p2
1385: WHERE p.rt_item_id = p2.rt_item_id

Line 1384: FROM icx_cat_item_prices p2

1380: INTO xActiveFlag
1381: FROM icx_cat_item_prices p
1382: WHERE p.rowid = p_price_row_id
1383: AND (EXISTS (SELECT 'documents'
1384: FROM icx_cat_item_prices p2
1385: WHERE p.rt_item_id = p2.rt_item_id
1386: AND p2.price_type IN ('TEMPLATE', 'BLANKET',
1387: 'QUOTATION', 'BULKLOAD',
1388: 'CONTRACT')) OR

Line 1390: FROM icx_cat_item_prices p2

1386: AND p2.price_type IN ('TEMPLATE', 'BLANKET',
1387: 'QUOTATION', 'BULKLOAD',
1388: 'CONTRACT')) OR
1389: EXISTS (SELECT 'global agreements'
1390: FROM icx_cat_item_prices p2
1391: WHERE p.rt_item_id = p2.local_rt_item_id
1392: AND p2.price_type = 'GLOBAL_AGREEMENT'));
1393: ELSIF p_price_type IN ('BULKLOAD', 'CONTRACT') THEN
1394: SELECT 'N'

Line 1396: FROM icx_cat_item_prices p

1392: AND p2.price_type = 'GLOBAL_AGREEMENT'));
1393: ELSIF p_price_type IN ('BULKLOAD', 'CONTRACT') THEN
1394: SELECT 'N'
1395: INTO xActiveFlag
1396: FROM icx_cat_item_prices p
1397: WHERE p.rowid = p_price_row_id
1398: AND (EXISTS (SELECT 'documents'
1399: FROM icx_cat_item_prices p2
1400: WHERE p.rt_item_id = p2.rt_item_id

Line 1399: FROM icx_cat_item_prices p2

1395: INTO xActiveFlag
1396: FROM icx_cat_item_prices p
1397: WHERE p.rowid = p_price_row_id
1398: AND (EXISTS (SELECT 'documents'
1399: FROM icx_cat_item_prices p2
1400: WHERE p.rt_item_id = p2.rt_item_id
1401: AND p2.price_type IN ('TEMPLATE', 'BLANKET',
1402: 'QUOTATION')) OR
1403: EXISTS (SELECT 'global agreements'

Line 1404: FROM icx_cat_item_prices p2

1400: WHERE p.rt_item_id = p2.rt_item_id
1401: AND p2.price_type IN ('TEMPLATE', 'BLANKET',
1402: 'QUOTATION')) OR
1403: EXISTS (SELECT 'global agreements'
1404: FROM icx_cat_item_prices p2
1405: WHERE p.rt_item_id = p2.local_rt_item_id
1406: AND p2.price_type = 'GLOBAL_AGREEMENT'));
1407: ELSIF p_price_type = 'TEMPLATE' THEN
1408: SELECT 'N'

Line 1410: FROM icx_cat_item_prices p

1406: AND p2.price_type = 'GLOBAL_AGREEMENT'));
1407: ELSIF p_price_type = 'TEMPLATE' THEN
1408: SELECT 'N'
1409: INTO xActiveFlag
1410: FROM icx_cat_item_prices p
1411: WHERE p.rowid = p_price_row_id
1412: AND ((p.contract_line_id <> -2 AND
1413: EXISTS (SELECT 'recently updated templates with same contract'
1414: FROM icx_cat_item_prices p2

Line 1414: FROM icx_cat_item_prices p2

1410: FROM icx_cat_item_prices p
1411: WHERE p.rowid = p_price_row_id
1412: AND ((p.contract_line_id <> -2 AND
1413: EXISTS (SELECT 'recently updated templates with same contract'
1414: FROM icx_cat_item_prices p2
1415: WHERE p.rt_item_id = p2.rt_item_id
1416: AND p.contract_line_id = p2.contract_line_id
1417: AND p.supplier_site_id = p2.supplier_site_id
1418: AND p2.price_type = 'TEMPLATE'

Line 1434: FROM icx_cat_item_prices p2

1430: AND nvl(r1.org_id, -2) = p.org_id
1431: AND r2.last_update_date > r1.last_update_date))))) OR
1432: (p.contract_line_id = -2 AND
1433: (EXISTS (SELECT 'contracts'
1434: FROM icx_cat_item_prices p2
1435: WHERE p.rt_item_id = p2.rt_item_id
1436: AND p2.contract_line_id <> -2) OR
1437: EXISTS (SELECT 'recently updated templates'
1438: FROM icx_cat_item_prices p2

Line 1438: FROM icx_cat_item_prices p2

1434: FROM icx_cat_item_prices p2
1435: WHERE p.rt_item_id = p2.rt_item_id
1436: AND p2.contract_line_id <> -2) OR
1437: EXISTS (SELECT 'recently updated templates'
1438: FROM icx_cat_item_prices p2
1439: WHERE p.rt_item_id = p2.rt_item_id
1440: AND p2.contract_line_id = -2
1441: AND p.supplier_site_id = p2.supplier_site_id
1442: AND p2.price_type = 'TEMPLATE'

Line 1457: FROM icx_cat_item_prices p2

1453: AND r1.sequence_num = p. template_line_id
1454: AND nvl(r1.org_id, -2) = p.org_id
1455: AND r2.last_update_date > r1.last_update_date)))))) OR
1456: EXISTS (SELECT 'global agreements'
1457: FROM icx_cat_item_prices p2
1458: WHERE p.rt_item_id = p2.local_rt_item_id
1459: AND p2.price_type = 'GLOBAL_AGREEMENT'));
1460: ELSIF p_price_type IN ('BLANKET', 'QUOTATION') THEN
1461: SELECT 'N'

Line 1463: FROM icx_cat_item_prices p

1459: AND p2.price_type = 'GLOBAL_AGREEMENT'));
1460: ELSIF p_price_type IN ('BLANKET', 'QUOTATION') THEN
1461: SELECT 'N'
1462: INTO xActiveFlag
1463: FROM icx_cat_item_prices p
1464: WHERE p.rowid = p_price_row_id
1465: AND EXISTS (SELECT 'template with same contract'
1466: FROM icx_cat_item_prices p2
1467: WHERE p.rt_item_id = p2.rt_item_id

Line 1466: FROM icx_cat_item_prices p2

1462: INTO xActiveFlag
1463: FROM icx_cat_item_prices p
1464: WHERE p.rowid = p_price_row_id
1465: AND EXISTS (SELECT 'template with same contract'
1466: FROM icx_cat_item_prices p2
1467: WHERE p.rt_item_id = p2.rt_item_id
1468: AND p.contract_line_id = p2.contract_line_id
1469: AND p2.price_type = 'TEMPLATE');
1470: ELSIF p_price_type = 'INTERNAL_ITEM' THEN

Line 1473: FROM icx_cat_item_prices p

1469: AND p2.price_type = 'TEMPLATE');
1470: ELSIF p_price_type = 'INTERNAL_ITEM' THEN
1471: SELECT 'N'
1472: INTO xActiveFlag
1473: FROM icx_cat_item_prices p
1474: WHERE p.rowid = p_price_row_id
1475: AND EXISTS (SELECT 'internal templates'
1476: FROM icx_cat_item_prices p2
1477: WHERE p.rt_item_id = p2.rt_item_id

Line 1476: FROM icx_cat_item_prices p2

1472: INTO xActiveFlag
1473: FROM icx_cat_item_prices p
1474: WHERE p.rowid = p_price_row_id
1475: AND EXISTS (SELECT 'internal templates'
1476: FROM icx_cat_item_prices p2
1477: WHERE p.rt_item_id = p2.rt_item_id
1478: AND p2.price_type = 'INTERNAL_TEMPLATE');
1479: ELSIF p_price_type = 'INTERNAL_TEMPLATE' THEN
1480: SELECT 'N'

Line 1482: FROM icx_cat_item_prices p

1478: AND p2.price_type = 'INTERNAL_TEMPLATE');
1479: ELSIF p_price_type = 'INTERNAL_TEMPLATE' THEN
1480: SELECT 'N'
1481: INTO xActiveFlag
1482: FROM icx_cat_item_prices p
1483: WHERE p.rowid = p_price_row_id
1484: AND EXISTS (SELECT 'recently updated internal templates'
1485: FROM icx_cat_item_prices p2
1486: WHERE p.rt_item_id = p2.rt_item_id

Line 1485: FROM icx_cat_item_prices p2

1481: INTO xActiveFlag
1482: FROM icx_cat_item_prices p
1483: WHERE p.rowid = p_price_row_id
1484: AND EXISTS (SELECT 'recently updated internal templates'
1485: FROM icx_cat_item_prices p2
1486: WHERE p.rt_item_id = p2.rt_item_id
1487: AND p2.price_type = 'INTERNAL_TEMPLATE'
1488: AND p2.rowid <> p.rowid
1489: --Bug 4349235

Line 1548: ' icx_cat_item_prices p, ' ||

1544: xString := xString ||
1545: 'FROM po_reqexpress_lines_all t, ' ;
1546: END IF;
1547: xString := xString ||
1548: ' icx_cat_item_prices p, ' ||
1549: ' icx_cat_extract_gt i ' ||
1550: 'WHERE NVL(t.org_id, -2) = p.org_id ' ||
1551: ' AND t.express_name = p.template_id ' ||
1552: ' AND t.sequence_num = p.template_line_id ' ||

Line 1568: ' icx_cat_item_prices p, ' ||

1564: xString := xString ||
1565: 'FROM po_lines_all t, ' ;
1566: END IF;
1567: xString := xString ||
1568: ' icx_cat_item_prices p, ' ||
1569: ' icx_cat_extract_gt i ' ||
1570: 'WHERE NVL(t.org_id, -2) = p.org_id ' ||
1571: ' AND t.po_line_id = p.contract_line_id ' ||
1572: ' AND i.type = ''ACTIVE_FLAG'' ' ||

Line 1592: ' icx_cat_item_prices p, ' ||

1588: ' financials_system_params_all fsp, ' ;
1589: END IF;
1590: xString := xString ||
1591: ' fnd_languages lang, ' ||
1592: ' icx_cat_item_prices p, ' ||
1593: ' icx_cat_extract_gt i ' ||
1594: 'WHERE NVL(fsp.org_id, -2) = p.org_id ' ||
1595: ' AND t.asl_id = p.asl_id ' ||
1596: ' AND t.owning_organization_id = fsp.inventory_organization_id ' ||

Line 1619: ' icx_cat_item_prices p, ' ||

1615: ' financials_system_params_all fsp, ' ;
1616: END IF;
1617: xString := xString ||
1618: ' fnd_languages lang, ' ||
1619: ' icx_cat_item_prices p, ' ||
1620: ' icx_cat_extract_gt i ' ||
1621: 'WHERE NVL(fsp.org_id, -2) = p.org_id ' ||
1622: ' AND t.inventory_item_id = p.inventory_item_id ' ||
1623: ' AND t.organization_id = fsp.inventory_organization_id ' ||

Line 1747: FROM icx_cat_item_prices p

1743: SELECT 'N'
1744: INTO xActiveFlag
1745: FROM dual
1746: WHERE EXISTS (SELECT 'documents'
1747: FROM icx_cat_item_prices p
1748: WHERE p.org_id = p_org_id
1749: AND p.inventory_item_id = p_inventory_item_id
1750: AND p.price_type IN ('TEMPLATE', 'BLANKET',
1751: 'QUOTATION', 'GLOBAL_AGREEMENT',

Line 1838: FROM icx_cat_item_prices

1834: BEGIN
1835: IF p_price_type IN ('TEMPLATE', 'INTERNAL_TEMPLATE') THEN
1836: SELECT 'Y'
1837: INTO xMatchTempalteFlag
1838: FROM icx_cat_item_prices
1839: WHERE rt_item_id = p_rt_item_id
1840: AND template_id = p_template_id
1841: AND rownum = 1;
1842: ELSE

Line 1861: xPriceType icx_cat_item_prices.price_type%TYPE;

1857: p_rt_item_id IN NUMBER)
1858: RETURN VARCHAR2
1859: IS
1860: xActiveFlag VARCHAR2(1) := 'N';
1861: xPriceType icx_cat_item_prices.price_type%TYPE;
1862: BEGIN
1863: IF p_action = 'DELETE' THEN
1864: -- If an active bulkload price is deleted, should set
1865: -- ASL back to active

Line 1870: FROM icx_cat_item_prices

1866: SELECT 'A'
1867: INTO xActiveFlag
1868: FROM dual
1869: WHERE EXISTS (SELECT 'ASL prices'
1870: FROM icx_cat_item_prices
1871: WHERE rt_item_id = p_rt_item_id
1872: AND price_type = 'ASL')
1873: AND NOT EXISTS (SELECT 'Contract/template prices'
1874: FROM icx_cat_item_prices

Line 1874: FROM icx_cat_item_prices

1870: FROM icx_cat_item_prices
1871: WHERE rt_item_id = p_rt_item_id
1872: AND price_type = 'ASL')
1873: AND NOT EXISTS (SELECT 'Contract/template prices'
1874: FROM icx_cat_item_prices
1875: WHERE rt_item_id = p_rt_item_id
1876: AND price_type IN ('TEMPLATE', 'BLANKET',
1877: 'QUOTATION',
1878: 'GLOBAL_AGREEMENT'));

Line 1882: FROM icx_cat_item_prices p

1878: 'GLOBAL_AGREEMENT'));
1879: ELSE
1880: SELECT price_type
1881: INTO xPriceType
1882: FROM icx_cat_item_prices p
1883: WHERE p.active_flag = 'Y'
1884: AND (p.rt_item_id = p_rt_item_id OR
1885: (p.local_rt_item_id = p_rt_item_id AND
1886: p.price_type = 'GLOBAL_AGREEMENT'))

Line 2086: -- Update ICX_CAT_ITEM_PRICES

2082: xErrLoc := 200;
2083: gTransactionCount := 0;
2084:
2085: xActionMode := 'UPDATE_PRICES';
2086: -- Update ICX_CAT_ITEM_PRICES
2087: IF (ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
2088: FOR i in 1..gUPRtItemIds.COUNT LOOP
2089: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
2090: snapShot(i, xActionMode));

Line 2094: UPDATE icx_cat_item_prices

2090: snapShot(i, xActionMode));
2091: END LOOP;
2092: END IF;
2093: FORALL i IN 1..gUPRtItemIds.COUNT
2094: UPDATE icx_cat_item_prices
2095: SET rt_item_id = gUPRtItemIds(i),
2096: price_type = gUPPriceTypes(i),
2097: active_flag = gUPActiveFlags(i),
2098: object_version_number = object_version_number + 1,

Line 2147: -- Update ICX_CAT_ITEM_PRICES for global agreements

2143: clearTables(xActionMode);
2144:
2145: xErrLoc := 210;
2146: xActionMode := 'UPDATE_PRICES_G';
2147: -- Update ICX_CAT_ITEM_PRICES for global agreements
2148: IF (ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
2149: FOR i in 1..gUPGRtItemIds.COUNT LOOP
2150: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
2151: snapShot(i, xActionMode));

Line 2155: UPDATE icx_cat_item_prices

2151: snapShot(i, xActionMode));
2152: END LOOP;
2153: END IF;
2154: FORALL i IN 1..gUPGRtItemIds.COUNT
2155: UPDATE icx_cat_item_prices
2156: SET rt_item_id = gUPGRtItemIds(i),
2157: object_version_number = object_version_number + 1,
2158: inventory_item_id = gUPGInventoryItemIds(i),
2159: mtl_category_id = gUPGMtlCategoryIds(i),

Line 2189: -- Insert ICX_CAT_ITEM_PRICES

2185: clearTables(xActionMode);
2186:
2187: xErrLoc := 220;
2188: xActionMode := 'INSERT_PRICES';
2189: -- Insert ICX_CAT_ITEM_PRICES
2190: IF (ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
2191: FOR i in 1..gIPRtItemIds.COUNT LOOP
2192: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
2193: snapShot(i, xActionMode));

Line 2197: INSERT INTO icx_cat_item_prices

2193: snapShot(i, xActionMode));
2194: END LOOP;
2195: END IF;
2196: FORALL i IN 1..gIPRtItemIds.COUNT
2197: INSERT INTO icx_cat_item_prices
2198: (rt_item_id, price_type,
2199: active_flag, object_version_number,
2200: asl_id, supplier_site_id,
2201: contract_id, contract_line_id,

Line 2554: DELETE FROM icx_cat_item_prices

2550: snapShot(i, xActionMode));
2551: END LOOP;
2552: END IF;
2553: FORALL i IN 1..gDPRowIds.COUNT
2554: DELETE FROM icx_cat_item_prices
2555: WHERE rowid = gDPRowIds(i);
2556:
2557: xErrLoc := 390;
2558: FORALL i IN 1..gDPTemplateCategoryIds.COUNT

Line 2575: DELETE FROM icx_cat_item_prices

2571: END LOOP;
2572: END IF;
2573: xRtItemIds.DELETE;
2574: FORALL i IN 1..gDPGContractLineIds.COUNT
2575: DELETE FROM icx_cat_item_prices
2576: WHERE contract_id = gDPGContractIds(i)
2577: AND contract_line_id = gDPGContractLineIds(i)
2578: AND price_type = 'GLOBAL_AGREEMENT'
2579: RETURNING local_rt_item_id BULK COLLECT INTO xRtItemIds;

Line 2591: -- Update ICX_CAT_ITEM_PRICES for local global agreements

2587: clearTables(xActionMode);
2588:
2589: xErrLoc := 400;
2590: xActionMode := 'UPDATE_PRICES_GA';
2591: -- Update ICX_CAT_ITEM_PRICES for local global agreements
2592: IF (ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
2593: FOR i in 1..gUPGAContractLineIds.COUNT LOOP
2594: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
2595: snapShot(i, xActionMode));

Line 2599: UPDATE icx_cat_item_prices

2595: snapShot(i, xActionMode));
2596: END LOOP;
2597: END IF;
2598: FORALL i IN 1..gUPGAContractLineIds.COUNT
2599: UPDATE icx_cat_item_prices
2600: SET object_version_number = object_version_number + 1,
2601: functional_price = gUPGAFunctionalPrices(i),
2602: supplier_site_id = gUPGASupplierSiteIds(i),
2603: supplier_site_code = gUPGASupplierSiteCodes(i),

Line 2626: -- Insert ICX_CAT_ITEM_PRICES for local global agreements

2622: clearTables(xActionMode);
2623:
2624: xErrLoc := 420;
2625: xActionMode := 'INSERT_PRICES_GA';
2626: -- Insert ICX_CAT_ITEM_PRICES for local global agreements
2627: IF (ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DETIL_LEVEL) THEN
2628: FOR i in 1..gIPGAContractLineIds.COUNT LOOP
2629: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
2630: snapShot(i, xActionMode));

Line 2634: INSERT INTO icx_cat_item_prices

2630: snapShot(i, xActionMode));
2631: END LOOP;
2632: END IF;
2633: FORALL i IN 1..gIPGAContractLineIds.COUNT
2634: INSERT INTO icx_cat_item_prices
2635: (rt_item_id, price_type,
2636: active_flag, object_version_number,
2637: asl_id, supplier_site_id,
2638: contract_id, contract_line_id,

Line 2708: UPDATE icx_cat_item_prices

2704: END LOOP;
2705: END IF;
2706: -- NOTE: we use local_rt_item_id to store local rt_item_id
2707: FORALL i IN 1..gSLRRowIds.COUNT
2708: UPDATE icx_cat_item_prices
2709: SET local_rt_item_id = gSLRALocalRtItemIds(i)
2710: WHERE rowid = gSLRRowIds(i);
2711: clearTables(xActionMode);
2712:

Line 2803: DELETE FROM icx_cat_item_prices

2799: xContinue := TRUE;
2800: WHILE xContinue LOOP
2801: xRtItemIds.DELETE;
2802: FORALL i IN 1..gDIPurchasingItemIds.COUNT
2803: DELETE FROM icx_cat_item_prices
2804: WHERE inventory_item_id = gDIPurchasingItemIds(i)
2805: AND org_id = gDIPurchasingOrgIds(i)
2806: AND (search_type = 'SUPPLIER' OR
2807: price_type = 'PURCHASING_ITEM')

Line 2825: UPDATE icx_cat_item_prices

2821:
2822: xErrLoc := 550;
2823: -- If there is any bulkloaded item, should set active_flag
2824: FORALL i IN 1..xRtItemIds.COUNT
2825: UPDATE icx_cat_item_prices
2826: SET active_flag = 'Y'
2827: WHERE rt_item_id = xRtItemIds(i)
2828: AND price_type IN ('BULKLOAD', 'CONTRACT');
2829:

Line 2864: DELETE FROM icx_cat_item_prices

2860: xContinue := TRUE;
2861: WHILE xContinue LOOP
2862: xRtItemIds.DELETE;
2863: FORALL i IN 1..gDINullPriceItemIds.COUNT
2864: DELETE FROM icx_cat_item_prices
2865: WHERE inventory_item_id = gDINullPriceItemIds(i)
2866: AND org_id = gDINullPriceOrgIds(i)
2867: AND price_type IN ('ASL', 'PURCHASING_ITEM')
2868: AND rownum <= ICX_POR_EXT_UTL.gCommitSize

Line 2907: DELETE FROM icx_cat_item_prices

2903: xContinue := TRUE;
2904: WHILE xContinue LOOP
2905: xRtItemIds.DELETE;
2906: FORALL i IN 1..gDIInternalItemIds.COUNT
2907: DELETE FROM icx_cat_item_prices
2908: WHERE inventory_item_id = gDIInternalItemIds(i)
2909: AND org_id = gDIInternalOrgIds(i)
2910: AND search_type = 'INTERNAL'
2911: AND rownum <= ICX_POR_EXT_UTL.gCommitSize

Line 3000: icx_cat_item_prices p

2996: SELECT p.local_rt_item_id,
2997: TO_NUMBER(NULL_NUMBER) primary_category_id, -- for Global Agreement match
2998: 'N' match_template_flag
2999: FROM icx_cat_items_b i,
3000: icx_cat_item_prices p
3001: WHERE i.internal_item_id = gCurrentPrice.internal_item_id
3002: AND (i.supplier IS NULL AND gCurrentPrice.supplier IS NULL OR
3003: i.supplier = gCurrentPrice.supplier)
3004: AND (i.supplier_part_num IS NULL AND

Line 3027: FROM icx_cat_item_prices p,

3023: t.primary_category_id,
3024: ICX_POR_EXT_ITEM.getMatchTempalteFlag(gCurrentPrice.price_type,
3025: p.rt_item_id,
3026: gCurrentPrice.template_id) match_template_flag
3027: FROM icx_cat_item_prices p,
3028: icx_cat_items_tlp t
3029: WHERE p.inventory_item_id = gCurrentPrice.internal_item_id
3030: AND p.org_id = gCurrentPrice.org_id
3031: AND p.price_type IN ('PURCHASING_ITEM',

Line 3066: icx_cat_item_prices p

3062: SELECT p.local_rt_item_id,
3063: TO_NUMBER(NULL_NUMBER) primary_category_id,
3064: 'N' match_template_flag
3065: FROM icx_cat_items_b i,
3066: icx_cat_item_prices p
3067: WHERE i.internal_item_id IS NULL
3068: AND i.supplier = gCurrentPrice.supplier
3069: AND i.supplier_part_num = gCurrentPrice.supplier_part_num
3070: AND i.supplier_part_auxid = '##NULL##'

Line 3091: FROM icx_cat_item_prices p,

3087: t.primary_category_id,
3088: ICX_POR_EXT_ITEM.getMatchTempalteFlag(gCurrentPrice.price_type,
3089: p.rt_item_id,
3090: gCurrentPrice.template_id) match_template_flag
3091: FROM icx_cat_item_prices p,
3092: icx_cat_items_tlp t
3093: WHERE p.inventory_item_id IS NULL
3094: AND p.org_id = gCurrentPrice.org_id
3095: AND p.contract_id = gCurrentPrice.contract_id

Line 3114: FROM icx_cat_item_prices p

3110: UNION ALL
3111: SELECT p.local_rt_item_id,
3112: TO_NUMBER(NULL_NUMBER) primary_category_id,
3113: 'N' match_template_flag
3114: FROM icx_cat_item_prices p
3115: WHERE p.inventory_item_id IS NULL
3116: AND p.org_id = gCurrentPrice.org_id
3117: AND p.contract_id = gCurrentPrice.contract_id
3118: AND p.contract_line_id = gCurrentPrice.contract_line_id

Line 3183: FROM icx_cat_item_prices p

3179: OPEN xFoundItemCur FOR
3180: SELECT p.rt_item_id,
3181: TO_NUMBER(NULL) primary_category_id,
3182: TO_CHAR(NULL) match_template_flag
3183: FROM icx_cat_item_prices p
3184: WHERE p.inventory_item_id IS NULL
3185: AND p.org_id = gCurrentPrice.org_id
3186: AND p.contract_id = gCurrentPrice.contract_id
3187: AND p.contract_line_id = gCurrentPrice.contract_line_id

Line 3235: -- ICX_CAT_ITEMS_B | ICX_CAT_ITEM_PRICES

3231: -- For instance: we have a global agreemnt defined in org 101,
3232: -- and enabled in org 102, 103; in org 102, there are two ASLs;
3233: -- in org 103, there is one ASL.
3234: --
3235: -- ICX_CAT_ITEMS_B | ICX_CAT_ITEM_PRICES
3236: -- -------------------------------+------------------------------
3237: -- ID | ITEM | SUP | SPN | ORG | ID | ORG | ACTIVE | LOCAL ID
3238: -- -------------------------------+------------------------------
3239: -- GA: 01 | I1 | s1 | spn1| 101 | 01 | 101 | 'Y' |

Line 4529: -- Delete all price rows from ICX_CAT_ITEM_PRICES for an inventory item.

4525: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL,
4526: 'Enter deleteItem()');
4527: END IF;
4528:
4529: -- Delete all price rows from ICX_CAT_ITEM_PRICES for an inventory item.
4530:
4531: IF gCurrentPrice.document_type = PURCHASING_ITEM_TYPE THEN
4532: IF (gCurrentPrice.status = ICX_POR_EXT_DIAG.UNPURCHASABLE_OUTSIDE) THEN
4533: xErrLoc := 200;

Line 4674: -- | |* update ICX_CAT_ITEM_PRICES |

4670: -- Process price row
4671: -- ___________________________________________________________
4672: -- | Match Price | Cache and Price Match |
4673: -- | | ---------------- |
4674: -- | |* update ICX_CAT_ITEM_PRICES |
4675: -- | |* Don't have to reset active_flag |
4676: -- | |_____________________________________________|
4677: -- | | Cache Match |
4678: -- | | ------------------- |

Line 4679: -- | |* update ICX_CAT_ITEM_PRICES |

4675: -- | |* Don't have to reset active_flag |
4676: -- | |_____________________________________________|
4677: -- | | Cache Match |
4678: -- | | ------------------- |
4679: -- | |* update ICX_CAT_ITEM_PRICES |
4680: -- | |* reset active_flag for both rt_item_id and |
4681: -- | | price_rt_item_id |
4682: -- | |* cleanup item for price_rt_item_id |
4683: -- | |* set item source for inventory_item_id |

Line 4687: -- | |* update ICX_CAT_ITEM_PRICES |

4683: -- | |* set item source for inventory_item_id |
4684: -- | |_____________________________________________|
4685: -- | | Price Match |
4686: -- | | ---------------- |
4687: -- | |* update ICX_CAT_ITEM_PRICES |
4688: -- | |* update ICX_CAT_ITEMS_B, ICX_CAT_ITEMS_TLP |
4689: -- | |* IF match_primary_category_id <> |
4690: -- | | primary_category_id |
4691: -- | | THEN update ICX_CAT_CATEGORY_ITEMS |

Line 4696: -- | |* update ICX_CAT_ITEM_PRICES |

4692: -- | |* don't have to reset active_flag |
4693: -- | |_____________________________________________|
4694: -- | | Item Match |
4695: -- | | ---------- |
4696: -- | |* update ICX_CAT_ITEM_PRICES |
4697: -- | |* update ICX_CAT_ITEMS_B, ICX_CAT_ITEMS_TLP |
4698: -- | |* IF match_primary_category_id <> |
4699: -- | | primary_category_id |
4700: -- | | THEN update ICX_CAT_CATEGORY_ITEMS |

Line 4710: -- | |* update ICX_CAT_ITEM_PRICES |

4706: -- | |* set item source for inventory_item_id |
4707: -- | |_____________________________________________|
4708: -- | | New Item |
4709: -- | | -------- |
4710: -- | |* update ICX_CAT_ITEM_PRICES |
4711: -- | |* create ICX_CAT_ITEMS_B, ICX_CAT_ITEMS_TLP |
4712: -- | |* create ICX_CAT_CATEGORY_ITEMS for |
4713: -- | | mtl_category_id |
4714: -- | |* IF template_id IS NOT NULL |

Line 4723: -- | |* create ICX_CAT_ITEM_PRICES |

4719: -- | |* set item source for inventory_item_id |
4720: -- |_____________|_____________________________________________|
4721: -- | New Price | Item Match |
4722: -- | | ---------- |
4723: -- | |* create ICX_CAT_ITEM_PRICES |
4724: -- | |* update ICX_CAT_ITEMS_B, ICX_CAT_ITEMS_TLP |
4725: -- | |* IF match_primary_category_id <> |
4726: -- | | primary_category_id |
4727: -- | | THEN update ICX_CAT_CATEGORY_ITEMS |

Line 4736: -- | |* create ICX_CAT_ITEM_PRICES |

4732: -- | |* set item source for inventory_item_id |
4733: -- | |_____________________________________________|
4734: -- | | New Item |
4735: -- | | -------- |
4736: -- | |* create ICX_CAT_ITEM_PRICES |
4737: -- | |* create ICX_CAT_ITEMS_B, ICX_CAT_ITEMS_TLP |
4738: -- | |* create ICX_CAT_CATEGORY_ITEMS for |
4739: -- | | mtl_category_id |
4740: -- | |* IF template_id IS NOT NULL |

Line 4745: -- | Delete Price|* delete ICX_CAT_ITEM_PRICES |

4741: -- | | THEN create ICX_CAT_CATEGORY_ITEMS |
4742: -- | |* set active_flag to 'Y |
4743: -- | |* set item source for inventory_item_id |
4744: -- |_____________|_____________________________________________|
4745: -- | Delete Price|* delete ICX_CAT_ITEM_PRICES |
4746: -- | |* reset active_flag for price_rt_item_id |
4747: -- | |* cleanup item for price_rt_item_id |
4748: -- | |* set item source for inventory_item_id |
4749: -- |_____________|_____________________________________________|

Line 6357: 'icx_cat_item_prices p, '||

6353: 'icx_cat_categories_tl ic2, '||
6354: 'icx_por_category_data_sources ds2, ';
6355: END IF;
6356: xFromStr := xFromStr ||
6357: 'icx_cat_item_prices p, '||
6358: 'icx_cat_categories_tl ic3, '||
6359: 'icx_por_category_data_sources ds3, '||
6360: 'icx_cat_items_b i ';
6361:

Line 6584: 'icx_cat_item_prices ip, '||

6580: 'financials_system_params_all fsp2, '|| -- Centralized Proc Impacts Enhancement
6581: 'po_system_parameters_all icx_psp, ';
6582: END IF;
6583: xViewStr2 := xViewStr2 ||
6584: 'icx_cat_item_prices ip, '||
6585: 'icx_cat_items_b i ';
6586:
6587: xErrLoc := 640;
6588: -- Considering global agreement reapproval, use ip.creation_date

Line 6610: 'icx_cat_item_prices p ';

6606: 'AND fsp2.inventory_organization_id = NVL(mi2.organization_id, '|| -- Centralized Proc Impacts
6607: 'fsp2.inventory_organization_id) ';
6608:
6609: xFromStr :=
6610: 'icx_cat_item_prices p ';
6611:
6612: xWhereStr :=
6613: 'WHERE doc.PoHeaderId = p.contract_id (+) '||
6614: 'AND doc.OrganizationId = p.org_id (+) '||

Line 6708: 'FROM icx_cat_item_prices p, '||

6704: 'p.local_rt_item_id, '||
6705: 'TO_CHAR(NULL) match_template_flag, '||
6706: 'TO_CHAR(NULL) active_flag, '||
6707: 'ROWIDTOCHAR(p.rowid) price_rowid '||
6708: 'FROM icx_cat_item_prices p, '||
6709: 'icx_cat_extract_ga_gt g, '||
6710: 'icx_cat_items_b i '||
6711: 'WHERE p.contract_id = g.contract_id '||
6712: 'AND p.contract_line_id = g.contract_line_id '||

Line 6950: ' FROM icx_cat_item_prices ip ' ||

6946: ' icx_por_loader_values l ' ||
6947: 'WHERE (l.vendor_last_run_date IS NULL OR ' ||
6948: ' vs.last_update_date > l.vendor_last_run_date) ' ||
6949: ' AND EXISTS ( SELECT ''Updated VendorSite In Catalog'' ' ||
6950: ' FROM icx_cat_item_prices ip ' ||
6951: ' WHERE ip.supplier_site_id = vs.vendor_site_id )';
6952:
6953:
6954: xErrLoc := 530;

Line 6999: UPDATE icx_cat_item_prices

6995: WHILE xContinue LOOP
6996:
6997: xErrLoc := 630;
6998: FORALL i in 1..xVendorSiteIds.COUNT
6999: UPDATE icx_cat_item_prices
7000: SET supplier_site_code = xVendorSiteNames(i),
7001: last_updated_by = ICX_POR_EXTRACTOR.gUserId,
7002: last_update_date = SYSDATE,
7003: last_update_login = ICX_POR_EXTRACTOR.gLoginId,

Line 7094: FROM icx_cat_item_prices p

7090: SELECT i.rt_item_id
7091: FROM icx_cat_extract_gt i
7092: WHERE i.type = 'CLEANUP_ITEM'
7093: AND NOT EXISTS (SELECT 'price rows'
7094: FROM icx_cat_item_prices p
7095: WHERE p.rt_item_id = i.rt_item_id);
7096: BEGIN
7097: xErrLoc := 100;
7098: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,

Line 7188: FROM icx_cat_item_prices p,

7184: SELECT p.price_type,
7185: p.rt_item_id,
7186: p.rowid price_rowid,
7187: ICX_POR_EXT_ITEM.getActiveFlag(p.price_type, p.rowid) active_flag
7188: FROM icx_cat_item_prices p,
7189: icx_cat_extract_gt i
7190: WHERE i.type = 'ACTIVE_FLAG'
7191: AND p.rt_item_id = i.rt_item_id
7192: AND p.price_type <> 'GLOBAL_AGREEMENT'

Line 7234: UPDATE icx_cat_item_prices

7230: END IF;
7231:
7232: xErrLoc := 240;
7233: FORALL i IN 1..gSARowIds.COUNT
7234: UPDATE icx_cat_item_prices
7235: SET active_flag = gSAActiveFlags(i),
7236: last_updated_by = ICX_POR_EXTRACTOR.gUserId,
7237: last_update_date = SYSDATE,
7238: last_update_login = ICX_POR_EXTRACTOR.gLoginId,

Line 7397: 'FROM icx_cat_item_prices p, '||

7393: 'ic.rt_category_id template_category_id, '||
7394: 'p.inventory_item_id, '||
7395: 'p.org_id, '||
7396: 'p.local_rt_item_id '||
7397: 'FROM icx_cat_item_prices p, '||
7398: 'icx_cat_categories_tl ic '||
7399: 'WHERE p.price_type IN (''TEMPLATE'', ''INTERNAL_TEMPLATE'', ' ||
7400: ' ''BLANKET'', ''QUOTATION'', ''GLOBAL_AGREEMENT'', ''ASL'', ' ||
7401: ' ''PURCHASING_ITEM'', ''INTERNAL_ITEM'') ' ||

Line 7457: DELETE icx_cat_item_prices

7453: END IF;
7454:
7455: xErrLoc := 240;
7456: FORALL i IN 1..gDPRowIds.COUNT
7457: DELETE icx_cat_item_prices
7458: WHERE rowid = gDPRowIds(i);
7459:
7460: xErrLoc := 260;
7461: FORALL i IN 1..gDPTemplateCategoryIds.COUNT

Line 7637: UPDATE ICX_CAT_ITEM_PRICES

7633: xErrLoc PLS_INTEGER := 100;
7634: BEGIN
7635: xErrLoc := 100;
7636:
7637: UPDATE ICX_CAT_ITEM_PRICES
7638: SET REQUEST_ID = ICX_POR_EXTRACTOR.gRequestId
7639: WHERE REQUEST_ID IN (
7640: TEMPLATE_TEMP_REQUEST_ID,
7641: CONTRACT_TEMP_REQUEST_ID,