[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE_PRICE PLS_INTEGER := 6; -- Delete price row
last_update_date DATE,
org_id NUMBER,
supplier_id NUMBER,
supplier icx_cat_items_b.supplier%TYPE,
supplier_site_code icx_cat_item_prices.supplier_site_code%TYPE,
supplier_part_num icx_cat_items_b.supplier_part_num%TYPE,
internal_item_id NUMBER,
internal_item_num icx_cat_items_b.internal_item_num%TYPE,
inventory_organization_id NUMBER,
item_source_type icx_cat_items_tlp.item_source_type%TYPE,
item_search_type icx_cat_items_tlp.search_type%TYPE,
mtl_category_id NUMBER,
category_key icx_cat_categories_tl.key%TYPE,
description icx_cat_items_tlp.description%TYPE,
picture icx_cat_items_tlp.picture%TYPE,
picture_url icx_cat_items_tlp.picture_url%TYPE,
price_type icx_cat_item_prices.price_type%TYPE,
asl_id NUMBER,
supplier_site_id NUMBER,
contract_id NUMBER,
contract_line_id NUMBER,
template_id icx_cat_item_prices.template_id%TYPE,
template_line_id NUMBER,
price_search_type icx_cat_item_prices.search_type%TYPE,
--FPJ FPSL Extractor Changes
--unit_price column will hold amount for items with Fixed Price Services line_type
--For all other items it will hold price
unit_price NUMBER,
--FPJ FPSL Extractor Changes
value_basis icx_cat_item_prices.value_basis%TYPE,
purchase_basis icx_cat_item_prices.purchase_basis%TYPE,
allow_price_override_flag icx_cat_item_prices.allow_price_override_flag%TYPE,
not_to_exceed_price NUMBER,
-- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
suggested_quantity NUMBER,
-- FPJ Bug# 3110297 jingyu Add negotiated flag
negotiated_by_preparer_flag icx_cat_item_prices.negotiated_by_preparer_flag%TYPE,
currency icx_cat_item_prices.currency%TYPE,
unit_of_measure icx_cat_item_prices.unit_of_measure%TYPE,
functional_price NUMBER,
contract_num icx_cat_item_prices.contract_num%TYPE,
contract_line_num NUMBER,
manufacturer ICX_CAT_ITEMS_TLP.manufacturer%TYPE,
manufacturer_part_num ICX_CAT_ITEMS_TLP.manufacturer_part_num%TYPE,
rate_type ICX_CAT_ITEM_PRICES.rate_type%TYPE,
rate_date DATE,
rate NUMBER,
supplier_number ICX_CAT_ITEM_PRICES.supplier_number%TYPE,
supplier_contact_id NUMBER,
item_revision ICX_CAT_ITEM_PRICES.item_revision%TYPE,
line_type_id NUMBER,
buyer_id NUMBER,
global_agreement_flag VARCHAR2(1),
status NUMBER,
primary_category_id NUMBER,
primary_category_name icx_cat_categories_tl.category_name%TYPE,
template_category_id NUMBER,
price_rt_item_id NUMBER,
price_internal_item_id NUMBER,
price_supplier_id NUMBER,
price_supplier_part_num icx_cat_items_b.supplier_part_num%TYPE,
price_contract_line_id NUMBER,
price_mtl_category_id NUMBER,
match_primary_category_id NUMBER,
rt_item_id NUMBER,
local_rt_item_id NUMBER,
match_template_flag VARCHAR2(1),
active_flag VARCHAR2(1),
price_rowid VARCHAR2(30) );
gUPLastUpdateDates DBMS_SQL.DATE_TABLE;
gIPLastUpdateDates DBMS_SQL.DATE_TABLE;
gUBExtractorUpdatedFlags DBMS_SQL.VARCHAR2_TABLE;
IF (pMode IN ('ALL', 'UPDATE_PRICES')) THEN
-- Update ICX_CAT_ITEM_PRICES
gUPRtItemIds.DELETE;
gUPPriceTypes.DELETE;
gUPAslIds.DELETE;
gUPSupplierSiteIds.DELETE;
gUPContractIds.DELETE;
gUPContractLineIds.DELETE;
gUPTemplateIds.DELETE;
gUPTemplateLineIds.DELETE;
gUPInventoryItemIds.DELETE;
gUPMtlCategoryIds.DELETE;
gUPOrgIds.DELETE;
gUPSearchTypes.DELETE;
gUPUnitPrices.DELETE;
gUPValueBasis.DELETE;
gUPPurchaseBasis.DELETE;
gUPAllowPriceOverrideFlag.DELETE;
gUPNotToExceedPrice.DELETE;
gUPSuggestedQuantities.DELETE;
gUPNegotiatedFlag.DELETE;
gUPCurrencys.DELETE;
gUPUnitOfMeasures.DELETE;
gUPFunctionalPrices.DELETE;
gUPSupplierSiteCodes.DELETE;
gUPContractNums.DELETE;
gUPContractLineNums.DELETE;
gUpRateTypes.DELETE;
gUpRateDates.DELETE;
gUpRates.DELETE;
gUpSupplierNumbers.DELETE;
gUpSupplierContactIds.DELETE;
gUpItemRevisions.DELETE;
gUpLineTypeIds.DELETE;
gUpBuyerIds.DELETE;
gUPPriceRowIds.DELETE;
gUPActiveFlags.DELETE;
gUPLastUpdateDates.DELETE;
IF (pMode IN ('ALL', 'UPDATE_PRICES_G')) THEN
-- Update ICX_CAT_ITEM_PRICES for global agreements
gUPGRtItemIds.DELETE;
gUPGContractIds.DELETE;
gUPGContractLineIds.DELETE;
gUPGInventoryItemIds.DELETE;
gUPGMtlCategoryIds.DELETE;
gUPGSearchTypes.DELETE;
gUPGUnitPrices.DELETE;
gUPGValueBasis.DELETE;
gUPGPurchaseBasis.DELETE;
gUPGAllowPriceOverrideFlag.DELETE;
gUPGNotToExceedPrice.DELETE;
gUPGNegotiatedFlag.DELETE;
gUPGLineTypeIds.DELETE;
gUPGCurrencys.DELETE;
gUPGUnitOfMeasures.DELETE;
gUPGFunctionalPrices.DELETE;
IF (pMode IN ('ALL', 'INSERT_PRICES')) THEN
-- Insert ICX_CAT_ITEM_PRICES
gIPRtItemIds.DELETE;
gIPPriceTypes.DELETE;
gIPAslIds.DELETE;
gIPSupplierSiteIds.DELETE;
gIPContractIds.DELETE;
gIPContractLineIds.DELETE;
gIPTemplateIds.DELETE;
gIPTemplateLineIds.DELETE;
gIPInventoryItemIds.DELETE;
gIPMtlCategoryIds.DELETE;
gIPOrgIds.DELETE;
gIPSearchTypes.DELETE;
gIPUnitPrices.DELETE;
gIPValueBasis.DELETE;
gIPPurchaseBasis.DELETE;
gIPAllowPriceOverrideFlag.DELETE;
gIPNotToExceedPrice.DELETE;
gIPSuggestedQuantities.DELETE;
gIPNegotiatedFlag.DELETE;
gIPCurrencys.DELETE;
gIPUnitOfMeasures.DELETE;
gIPFunctionalPrices.DELETE;
gIPSupplierSiteCodes.DELETE;
gIPContractNums.DELETE;
gIPContractLineNums.DELETE;
gIpRateTypes.DELETE;
gIpRateDates.DELETE;
gIpRates.DELETE;
gIpSupplierNumbers.DELETE;
gIpSupplierContactIds.DELETE;
gIpItemRevisions.DELETE;
gIpLineTypeIds.DELETE;
gIpBuyerIds.DELETE;
gIPActiveFlags.DELETE;
gIPLastUpdateDates.DELETE;
IF (pMode IN ('ALL', 'INSERT_ITEMS_B')) THEN
-- Insert ICX_CAT_ITEMS_B
gIBRtItemIds.DELETE;
gIBOrgIds.DELETE;
gIBSupplierIds.DELETE;
gIBSuppliers.DELETE;
gIBSupplierPartNums.DELETE;
gIBInternalItemIds.DELETE;
gIBInternalItemNums.DELETE;
IF (pMode IN ('ALL', 'UPDATE_ITEMS_B')) THEN
-- Update ICX_CAT_ITEMS_B
gUBRtItemIds.DELETE;
gUBInternalItemNums.DELETE;
gUBExtractorUpdatedFlags.DELETE;
gUBJobNumbers.DELETE;
IF (pMode IN ('ALL', 'INSERT_ITEMS_TLP')) THEN
-- Insert ICX_CAT_ITEMS_TLP
gITRtItemIds.DELETE;
gITLanguages.DELETE;
gITOrgIds.DELETE;
gITSupplierIds.DELETE;
gITItemSourceTypes.DELETE;
gITSearchTypes.DELETE;
gITPrimaryCategoryIds.DELETE;
gITPrimaryCategoryNames.DELETE;
gITInternalItemIds.DELETE;
gITInternalItemNums.DELETE;
gITSuppliers.DELETE;
gITSupplierPartNums.DELETE;
gITDescriptions.DELETE;
gITPictures.DELETE;
gITPictureURLs.DELETE;
gITManufacturers.DELETE;
gITManufacturerPartNums.DELETE;
IF (pMode IN ('ALL', 'UPDATE_ITEMS_TLP')) THEN
-- Update ICX_CAT_ITEMS_TLP
gUTRtItemIds.DELETE;
gUTLanguages.DELETE;
gUTItemSourceTypes.DELETE;
gUTSearchTypes.DELETE;
gUTPrimaryCategoryIds.DELETE;
gUTPrimaryCategoryNames.DELETE;
gUTInternalItemNums.DELETE;
gUTDescriptions.DELETE;
gUTPictures.DELETE;
gUTPictureURLs.DELETE;
gUTManufacturers.DELETE;
gUTManufacturerPartNums.DELETE;
IF (pMode IN ('ALL', 'INSERT_CATEGORY_ITEMS')) THEN
-- Insert ICX_CAT_CATEGORY_ITEMS
gICRtItemIds.DELETE;
gICRtCategoryIds.DELETE;
IF (pMode IN ('ALL', 'UPDATE_CATEGORY_ITEMS')) THEN
-- Update ICX_CAT_CATEGORY_ITEMS
gUCRtItemIds.DELETE;
gUCRtCategoryIds.DELETE;
gUCOldRtCategoryIds.DELETE;
IF (pMode IN ('ALL', 'INSERT_EXT_ITEMS')) THEN
-- Insert ICX_CAT_EXT_ITEMS_TLP
gIERtItemIds.DELETE;
gIELanguages.DELETE;
gIEOrgIds.DELETE;
gIERtCategoryIds.DELETE;
IF (pMode IN ('ALL', 'UPDATE_EXT_ITEMS')) THEN
-- Update ICX_CAT_EXT_ITEMS_TLP
gUERtItemIds.DELETE;
gUELanguages.DELETE;
gUERtCategoryIds.DELETE;
gUEOldRtCategoryIds.DELETE;
IF (pMode IN ('ALL', 'DELETE_ITEM_PRICE')) THEN
-- Delete Item Price
gDPRowIds.DELETE;
gDPTemplateCategoryIds.DELETE;
gDPRtItemIds.DELETE;
gDPInventoryItemIds.DELETE;
gDPOrgIds.DELETE;
gDPLocalRtItemIds.DELETE;
IF (pMode IN ('ALL', 'DELETE_ITEM_PRICE_GA')) THEN
-- Delete Item Price for global agreement
gDPGContractIds.DELETE;
gDPGContractLineIds.DELETE;
IF (pMode IN ('ALL', 'DELETE_PURCHASING_ITEM')) THEN
-- Delete Purchasing Item
gDIPurchasingItemIds.DELETE;
gDIPurchasingOrgIds.DELETE;
IF (pMode IN ('ALL', 'DELETE_NULL_PRICE_ITEM')) THEN
-- Delete Null Price Item
gDINullPriceItemIds.DELETE;
gDINullPriceOrgIds.DELETE;
IF (pMode IN ('ALL', 'DELETE_INTERNAL_ITEM')) THEN
-- Delete Internal Item
gDIInternalItemIds.DELETE;
gDIInternalOrgIds.DELETE;
gCIRtItemIds.DELETE;
IF (pMode IN ('ALL', 'TOUCH_UPDATED_GA')) THEN
-- Insert temporary table to update global agreement
gUGAContractIds.DELETE;
gUGAContractLineIds.DELETE;
gTARtItemIds.DELETE;
gTAInvItemIds.DELETE;
gTAInvOrgIds.DELETE;
IF (pMode IN ('ALL', 'DELETE_ITEM_NOPRICE')) THEN
-- Delete Items without price
gDIRtItemIds.DELETE;
gSAPriceTypes.DELETE;
gSARtItemIds.DELETE;
gSARowIds.DELETE;
gSAActiveFlags.DELETE;
IF (pMode IN ('ALL', 'UPDATE_PRICES_GA')) THEN
-- Update ICX_CAT_ITEM_PRICES for local global agreements
gUPGASupplierSiteIds.DELETE;
gUPGAContractIds.DELETE;
gUPGAContractLineIds.DELETE;
gUPGAFunctionalPrices.DELETE;
gUPGASupplierSiteCodes.DELETE;
gUPGALineTypeIds.DELETE;
gUPGARateTypes.DELETE;
gUPGARateDates.DELETE;
gUPGARates.DELETE;
gUPGAOrgIds.DELETE;
IF (pMode IN ('ALL', 'INSERT_PRICES_GA')) THEN
-- Insert ICX_CAT_ITEM_PRICES for local global agreements
gIPGARtItemIds.DELETE;
gIPGALocalRtItemIds.DELETE;
gIPGASupplierSiteIds.DELETE;
gIPGAContractIds.DELETE;
gIPGAContractLineIds.DELETE;
gIPGAInventoryItemIds.DELETE;
gIPGAMtlCategoryIds.DELETE;
gIPGAOrgIds.DELETE;
gIPGAUnitPrices.DELETE;
gIPGAValueBasis.DELETE;
gIPGAPurchaseBasis.DELETE;
gIPGAAllowPriceOverrideFlag.DELETE;
gIPGANotToExceedPrice.DELETE;
gIPGANegotiatedFlag.DELETE;
gIPGACurrencys.DELETE;
gIPGAUnitOfMeasures.DELETE;
gIPGAFunctionalPrices.DELETE;
gIPGASupplierSiteCodes.DELETE;
gIPGAContractNums.DELETE;
gIPGAContractLineNums.DELETE;
gIPGALineTypeIds.DELETE;
gIPGARateTypes.DELETE;
gIPGARateDates.DELETE;
gIPGARates.DELETE;
gSLRRowIds.DELETE;
gSLRALocalRtItemIds.DELETE;
IF (pMode = 'UPDATE_PRICES') THEN
-- Update ICX_CAT_ITEM_PRICES
xShot := xShot || ' gUPRtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gUPRtItemIds, pIndex) || ', ';
xShot := xShot || ' gUPLastUpdateDates: ' ||
ICX_POR_EXT_UTL.getTableElement(gUPLastUpdateDates, pIndex);
ELSIF (pMode = 'UPDATE_PRICES_G') THEN
-- Update ICX_CAT_ITEM_PRICES for global agreements
xShot := xShot || ' gUPGRtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gUPGRtItemIds, pIndex) || ', ';
ELSIF (pMode = 'INSERT_PRICES') THEN
-- Insert ICX_CAT_ITEM_PRICES
xShot := xShot || ' gIPRtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gIPRtItemIds, pIndex) || ', ';
xShot := xShot || ' gIPLastUpdateDates: ' ||
ICX_POR_EXT_UTL.getTableElement(gIPLastUpdateDates, pIndex);
ELSIF (pMode = 'INSERT_ITEMS_B') THEN
-- Insert ICX_CAT_ITEMS_B
xShot := xShot || ' gIBRtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gIBRtItemIds, pIndex) || ', ';
ELSIF (pMode = 'UPDATE_ITEMS_B') THEN
-- Update ICX_CAT_ITEMS_B
xShot := xShot || ' gUBRtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gUBRtItemIds, pIndex) || ', ';
xShot := xShot || ' gUBExtractorUpdatedFlags: ' ||
ICX_POR_EXT_UTL.getTableElement(gUBExtractorUpdatedFlags, pIndex) || ', ';
ELSIF (pMode = 'INSERT_ITEMS_TLP') THEN
-- Insert ICX_CAT_ITEMS_TLP
xShot := xShot || ' gITRtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gITRtItemIds, pIndex) || ', ';
ELSIF (pMode = 'UPDATE_ITEMS_TLP') THEN
-- Update ICX_CAT_ITEMS_TLP
xShot := xShot || ' gUTRtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gUTRtItemIds, pIndex) || ', ';
ELSIF (pMode = 'INSERT_CATEGORY_ITEMS') THEN
-- Insert ICX_CAT_CATEGORY_ITEMS
xShot := xShot || ' gICRtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gICRtItemIds, pIndex) || ', ';
ELSIF (pMode = 'UPDATE_CATEGORY_ITEMS') THEN
-- Update ICX_CAT_CATEGORY_ITEMS
xShot := xShot || ' gUCRtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gUCRtItemIds, pIndex) || ', ';
ELSIF (pMode = 'INSERT_EXT_ITEMS') THEN
-- Insert ICX_CAT_EXT_ITEMS_TLP
xShot := xShot || ' gIERtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gIERtItemIds, pIndex) || ', ';
ELSIF (pMode = 'UPDATE_EXT_ITEMS') THEN
-- Update ICX_CAT_EXT_ITEMS_TLP
xShot := xShot || ' gUERtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gUERtItemIds, pIndex) || ', ';
ELSIF (pMode = 'DELETE_ITEM_PRICE') THEN
-- Delete Item Price
xShot := xShot || ' gDPRowIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gDPRowIds, pIndex) || ', ';
ELSIF (pMode = 'DELETE_ITEM_PRICE_GA') THEN
-- Delete Item Price fro global agreement
xShot := xShot || ' gDPGContractIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gDPGContractIds, pIndex)||', ';
ELSIF (pMode = 'DELETE_PURCHASING_ITEM') THEN
-- Delete Item
xShot := xShot || ' gDIPurchasingItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gDIPurchasingItemIds, pIndex) || ', ';
ELSIF (pMode = 'DELETE_NULL_PRICE_ITEM') THEN
xShot := xShot || ' gDINullPriceItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gDINullPriceItemIds, pIndex) || ', ';
ELSIF (pMode = 'DELETE_INTERNAL_ITEM') THEN
xShot := xShot || ' gDIInternalItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gDIInternalItemIds, pIndex) || ', ';
ELSIF (pMode = 'TOUCH_UPDATED_GA') THEN
-- Insert temporary table to update global agreement
xShot := xShot || ' gUGAContractIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gUGAContractIds, pIndex)||', ';
ELSIF (pMode = 'DELETE_ITEM_NOPRICE') THEN
-- Delete Items without price
xShot := xShot || ' gDIRtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gDIRtItemIds, pIndex);
ELSIF (pMode = 'UPDATE_PRICES_GA') THEN
-- Update ICX_CAT_ITEM_PRICES for local global agreements
xShot := xShot || ' gUPGASupplierSiteIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gUPGASupplierSiteIds, pIndex) || ', ';
ELSIF (pMode = 'INSERT_PRICES_GA') THEN
-- Insert ICX_CAT_ITEM_PRICES for local global agreements
xShot := xShot || ' gIPGARtItemIds: ' ||
ICX_POR_EXT_UTL.getTableElement(gIPGARtItemIds, pIndex) || ', ';
ELSIF pItemStatus = DELETE_PRICE THEN
xItemStatusStr := 'DELETE_PRICE';
', last_update_date: ' || gCurrentPrice.last_update_date ||
', org_id: ' || gCurrentPrice.org_id ||
', supplier_id: ' || gCurrentPrice.supplier_id ||
', supplier: ' || gCurrentPrice.supplier ||
', supplier_site_code: ' || gCurrentPrice.supplier_site_code ||
', supplier_part_num: ' || gCurrentPrice.supplier_part_num ||
', internal_item_id: ' || gCurrentPrice.internal_item_id ||
', internal_item_num: ' || gCurrentPrice.internal_item_num ||
', inventory_organization_id: ' || gCurrentPrice.inventory_organization_id ||
', item_source_type: ' || gCurrentPrice.item_source_type ||
', item_search_type: ' || gCurrentPrice.item_search_type ||
', mtl_category_id: ' || gCurrentPrice.mtl_category_id ||
', category_key: ' || gCurrentPrice.category_key ||
', description: ' || gCurrentPrice.description ||
', picture: ' || gCurrentPrice.picture ||
', picture_url: ' || gCurrentPrice.picture_url ||
', price_type: ' || gCurrentPrice.price_type ||
', asl_id: ' || gCurrentPrice.asl_id ||
', supplier_site_id: ' || gCurrentPrice.supplier_site_id ||
', contract_id: ' || gCurrentPrice.contract_id ||
', contract_line_id: ' || gCurrentPrice.contract_line_id ||
', template_id: ' || gCurrentPrice.template_id ||
', template_line_id: ' || gCurrentPrice.template_line_id ||
', price_search_type: ' || gCurrentPrice.price_search_type ||
', unit_price: ' || gCurrentPrice.unit_price ||
--FPJ FPSL Extractor Changes
', value_basis: ' || gCurrentPrice.value_basis ||
', purchase_basis: ' || gCurrentPrice.purchase_basis ||
', allow_price_override_flag: ' || gCurrentPrice.allow_price_override_flag ||
', not_to_exceed_price: ' || gCurrentPrice.not_to_exceed_price ||
', line_type_id: ' || gCurrentPrice.line_type_id ||
-- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
', suggested_quantity: ' || gCurrentPrice.suggested_quantity ||
-- FPJ Bug# 3110297 jingyu Add negotiated flag
', negotiated_by_preparer_flag: ' || gCurrentPrice.negotiated_by_preparer_flag ||
', currency: ' || gCurrentPrice.currency ||
', unit_of_measure: ' || gCurrentPrice.unit_of_measure ||
', functional_price: ' || gCurrentPrice.functional_price ||
', contract_num: ' || gCurrentPrice.contract_num ||
', contract_line_num: ' || gCurrentPrice.contract_line_num ||
', global_agreement_flag: ' || gCurrentPrice.global_agreement_flag ||
', status: ' || ICX_POR_EXT_DIAG.getStatusString(gCurrentPrice.status) ||
', primary_category_id: ' || gCurrentPrice.primary_category_id ||
', primary_category_name: ' || gCurrentPrice.primary_category_name ||
', template_category_id: ' || gCurrentPrice.template_category_id ||
', price_rt_item_id: ' || gCurrentPrice.price_rt_item_id ||
', price_internal_item_id: ' || gCurrentPrice.price_internal_item_id ||
', price_supplier_id: ' || gCurrentPrice.price_supplier_id ||
', price_supplier_part_num: ' || gCurrentPrice.price_supplier_part_num ||
', price_contract_line_id: ' || gCurrentPrice.price_contract_line_id ||
', price_mtl_category_id: ' || gCurrentPrice.price_mtl_category_id ||
', match_primary_category_id: '||gCurrentPrice.match_primary_category_id||
', rt_item_id: ' || gCurrentPrice.rt_item_id ||
', local_rt_item_id: ' || gCurrentPrice.local_rt_item_id ||
', match_template_flag: ' || gCurrentPrice.match_template_flag ||
', active_flag: ' || gCurrentPrice.active_flag ||
', price_rowid: ' || gCurrentPrice.price_rowid || ']';
SELECT 'N'
INTO xActiveFlag
FROM icx_cat_item_prices p
WHERE p.rowid = p_price_row_id
AND EXISTS (SELECT 'documents'
FROM icx_cat_item_prices p2
WHERE p.org_id = p2.org_id
AND p.inventory_item_id = p2.inventory_item_id
AND p2.price_type IN ('TEMPLATE', 'BLANKET',
'QUOTATION', 'GLOBAL_AGREEMENT',
'ASL', 'BULKLOAD', 'CONTRACT'));
SELECT 'N'
INTO xActiveFlag
FROM icx_cat_item_prices p
WHERE p.rowid = p_price_row_id
AND (EXISTS (SELECT 'documents'
FROM icx_cat_item_prices p2
WHERE p.rt_item_id = p2.rt_item_id
AND p2.price_type IN ('TEMPLATE', 'BLANKET',
'QUOTATION', 'BULKLOAD',
'CONTRACT')) OR
EXISTS (SELECT 'global agreements'
FROM icx_cat_item_prices p2
WHERE p.rt_item_id = p2.local_rt_item_id
AND p2.price_type = 'GLOBAL_AGREEMENT'));
SELECT 'N'
INTO xActiveFlag
FROM icx_cat_item_prices p
WHERE p.rowid = p_price_row_id
AND (EXISTS (SELECT 'documents'
FROM icx_cat_item_prices p2
WHERE p.rt_item_id = p2.rt_item_id
AND p2.price_type IN ('TEMPLATE', 'BLANKET',
'QUOTATION')) OR
EXISTS (SELECT 'global agreements'
FROM icx_cat_item_prices p2
WHERE p.rt_item_id = p2.local_rt_item_id
AND p2.price_type = 'GLOBAL_AGREEMENT'));
SELECT 'N'
INTO xActiveFlag
FROM icx_cat_item_prices p
WHERE p.rowid = p_price_row_id
AND ((p.contract_line_id <> -2 AND
EXISTS (SELECT 'recently updated templates with same contract'
FROM icx_cat_item_prices p2
WHERE p.rt_item_id = p2.rt_item_id
AND p.contract_line_id = p2.contract_line_id
AND p.supplier_site_id = p2.supplier_site_id
AND p2.price_type = 'TEMPLATE'
AND p2.rowid <> p.rowid
AND ((p2.last_update_date > p.last_update_date) OR
(p2.last_update_date = p.last_update_date AND
EXISTS (SELECT 'exists'
FROM po_reqexpress_lines_all r1,
po_reqexpress_lines_all r2
WHERE r2.express_name = p2.template_id
AND r2.sequence_num = p2. template_line_id
AND nvl(r2.org_id, -2) = p2.org_id
AND r1.express_name = p.template_id
AND r1.sequence_num = p. template_line_id
AND nvl(r1.org_id, -2) = p.org_id
AND r2.last_update_date > r1.last_update_date))))) OR
(p.contract_line_id = -2 AND
(EXISTS (SELECT 'contracts'
FROM icx_cat_item_prices p2
WHERE p.rt_item_id = p2.rt_item_id
AND p2.contract_line_id <> -2) OR
EXISTS (SELECT 'recently updated templates'
FROM icx_cat_item_prices p2
WHERE p.rt_item_id = p2.rt_item_id
AND p2.contract_line_id = -2
AND p.supplier_site_id = p2.supplier_site_id
AND p2.price_type = 'TEMPLATE'
AND p2.rowid <> p.rowid
AND ((p2.last_update_date > p.last_update_date) OR
(p2.last_update_date = p.last_update_date AND
EXISTS (SELECT 'exists'
FROM po_reqexpress_lines_all r1,
po_reqexpress_lines_all r2
WHERE r2.express_name = p2.template_id
AND r2.sequence_num = p2. template_line_id
AND nvl(r2.org_id, -2) = p2.org_id
AND r1.express_name = p.template_id
AND r1.sequence_num = p. template_line_id
AND nvl(r1.org_id, -2) = p.org_id
AND r2.last_update_date > r1.last_update_date)))))) OR
EXISTS (SELECT 'global agreements'
FROM icx_cat_item_prices p2
WHERE p.rt_item_id = p2.local_rt_item_id
AND p2.price_type = 'GLOBAL_AGREEMENT'));
SELECT 'N'
INTO xActiveFlag
FROM icx_cat_item_prices p
WHERE p.rowid = p_price_row_id
AND EXISTS (SELECT 'template with same contract'
FROM icx_cat_item_prices p2
WHERE p.rt_item_id = p2.rt_item_id
AND p.contract_line_id = p2.contract_line_id
AND p2.price_type = 'TEMPLATE');
SELECT 'N'
INTO xActiveFlag
FROM icx_cat_item_prices p
WHERE p.rowid = p_price_row_id
AND EXISTS (SELECT 'internal templates'
FROM icx_cat_item_prices p2
WHERE p.rt_item_id = p2.rt_item_id
AND p2.price_type = 'INTERNAL_TEMPLATE');
SELECT 'N'
INTO xActiveFlag
FROM icx_cat_item_prices p
WHERE p.rowid = p_price_row_id
AND EXISTS (SELECT 'recently updated internal templates'
FROM icx_cat_item_prices p2
WHERE p.rt_item_id = p2.rt_item_id
AND p2.price_type = 'INTERNAL_TEMPLATE'
AND p2.rowid <> p.rowid
--Bug 4349235
AND ((p2.last_update_date > p.last_update_date) OR
(p2.last_update_date = p.last_update_date AND
EXISTS (SELECT 'exists'
FROM po_reqexpress_lines_all r1,
po_reqexpress_lines_all r2
WHERE r2.express_name = p2.template_id
AND r2.sequence_num = p2. template_line_id
AND nvl(r2.org_id, -2) = p2.org_id
AND r1.express_name = p.template_id
AND r1.sequence_num = p. template_line_id
AND nvl(r1.org_id, -2) = p.org_id
AND ((r2.last_update_date > r1.last_update_date)
OR
(r2.last_update_date = r1.last_update_date AND p2.rowid > p.rowid))))));
'SELECT p.rt_item_id, t.item_description, ' ||
' null, null ' ;
'SELECT p.rt_item_id, t.item_description, ' ||
' NVL(t.attribute13, t.attribute14), t.Attribute14 ' ;
'SELECT p.rt_item_id, mitl.description, ' ||
' null, null ' ;
'SELECT p.rt_item_id, t.description, ' ||
' null, null ' ;
UPDATE icx_cat_items_tlp
SET description = xUPItemDescriptions(i),
picture = xUPItemPictures(i),
picture_url = xUPItemPictureUrls(i),
thumbnail_image = xUPItemPictures(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
request_id = ICX_POR_EXTRACTOR.gRequestId,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE rt_item_id = xUPItemRtItemIds(i)
AND language = ICX_POR_EXTRACTOR.gBaseLang;
UPDATE icx_cat_items_tlp
SET description = xUPItemDescriptions(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
request_id = ICX_POR_EXTRACTOR.gRequestId,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE rt_item_id = xUPItemRtItemIds(i)
AND language = ICX_POR_EXTRACTOR.gBaseLang;
SELECT 'N'
INTO xActiveFlag
FROM dual
WHERE EXISTS (SELECT 'documents'
FROM icx_cat_item_prices p
WHERE p.org_id = p_org_id
AND p.inventory_item_id = p_inventory_item_id
AND p.price_type IN ('TEMPLATE', 'BLANKET',
'QUOTATION', 'GLOBAL_AGREEMENT',
'ASL', 'BULKLOAD'));
SELECT 'Y'
INTO xMatchTempalteFlag
FROM icx_cat_item_prices
WHERE rt_item_id = p_rt_item_id
AND template_id = p_template_id
AND rownum = 1;
IF p_action = 'DELETE' THEN
-- If an active bulkload price is deleted, should set
-- ASL back to active
SELECT 'A'
INTO xActiveFlag
FROM dual
WHERE EXISTS (SELECT 'ASL prices'
FROM icx_cat_item_prices
WHERE rt_item_id = p_rt_item_id
AND price_type = 'ASL')
AND NOT EXISTS (SELECT 'Contract/template prices'
FROM icx_cat_item_prices
WHERE rt_item_id = p_rt_item_id
AND price_type IN ('TEMPLATE', 'BLANKET',
'QUOTATION',
'GLOBAL_AGREEMENT'));
SELECT price_type
INTO xPriceType
FROM icx_cat_item_prices p
WHERE p.active_flag = 'Y'
AND (p.rt_item_id = p_rt_item_id OR
(p.local_rt_item_id = p_rt_item_id AND
p.price_type = 'GLOBAL_AGREEMENT'))
AND rownum = 1;
gItemCache.DELETE;
xActionMode := 'UPDATE_PRICES';
UPDATE icx_cat_item_prices
SET rt_item_id = gUPRtItemIds(i),
price_type = gUPPriceTypes(i),
active_flag = gUPActiveFlags(i),
object_version_number = object_version_number + 1,
asl_id = gUPAslIds(i),
supplier_site_id = gUPSupplierSiteIds(i),
contract_id = gUPContractIds(i),
contract_line_id = gUPContractLineIds(i),
template_id = gUPTemplateIds(i),
template_line_id = gUPTemplateLineIds(i),
inventory_item_id = gUPInventoryItemIds(i),
mtl_category_id = gUPMtlCategoryIds(i),
org_id = gUPOrgIds(i),
search_type = gUPSearchTypes(i),
unit_price = gUPUnitPrices(i),
--FPJ FPSL Extractor Changes
value_basis = gUPValueBasis(i),
purchase_basis = gUPPurchaseBasis(i),
allow_price_override_flag = gUPAllowPriceOverrideFlag(i),
not_to_exceed_price = gUPNotToExceedPrice(i),
-- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
suggested_quantity = gUPSuggestedQuantities(i),
-- FPJ Bug# 3110297 jingyu Add negotiated flag
negotiated_by_preparer_flag = gUPNegotiatedFlag(i),
currency = gUPCurrencys(i),
unit_of_measure = gUPUnitOfMeasures(i),
functional_price = gUPFunctionalPrices(i),
supplier_site_code = gUPSupplierSiteCodes(i),
contract_num = gUPContractNums(i),
contract_line_num = gUPContractLineNums(i),
rate_type = gUpRateTypes(i),
rate_date = gUpRateDates(i),
rate = gUpRates(i),
supplier_number = gUpSupplierNumbers(i),
supplier_contact_id = gUpSupplierContactIds(i),
item_revision = gUpItemRevisions(i),
line_type_id = gUpLineTypeIds(i),
buyer_id = gUpBuyerIds(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = gUPLastUpdateDates(i),
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
-- Bug#3352834
request_id = ICX_POR_EXT_ITEM.CURRENT_REQUEST_ID,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE rowid = gUPPriceRowIds(i);
xActionMode := 'UPDATE_PRICES_G';
UPDATE icx_cat_item_prices
SET rt_item_id = gUPGRtItemIds(i),
object_version_number = object_version_number + 1,
inventory_item_id = gUPGInventoryItemIds(i),
mtl_category_id = gUPGMtlCategoryIds(i),
search_type = gUPGSearchTypes(i),
unit_price = gUPGUnitPrices(i),
--FPJ FPSL Extractor Changes
value_basis = gUPGValueBasis(i),
purchase_basis = gUPGPurchaseBasis(i),
allow_price_override_flag = gUPGAllowPriceOverrideFlag(i),
not_to_exceed_price = gUPGNotToExceedPrice(i),
-- FPJ Bug# 3110297 jingyu Add negotiated flag
negotiated_by_preparer_flag = gUPGNegotiatedFlag(i),
line_type_id = gUPGLineTypeIds(i),
currency = gUPGCurrencys(i),
unit_of_measure = gUPGUnitOfMeasures(i),
functional_price = gUPGFunctionalPrices(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
-- Bug#3352834
request_id = ICX_POR_EXT_ITEM.CURRENT_REQUEST_ID,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE contract_id = gUPGContractIds(i)
AND contract_line_id = gUPGContractLineIds(i)
AND price_type = 'GLOBAL_AGREEMENT';
xActionMode := 'INSERT_PRICES';
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,
--FPJ FPSL Extractor Changes
value_basis, purchase_basis,
allow_price_override_flag, not_to_exceed_price,
-- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
suggested_quantity,
-- FPJ Bug# 3110297 jingyu Add negotiated flag
negotiated_by_preparer_flag,
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)
VALUES
(gIPRtItemIds(i), gIPPriceTypes(i),
gIPActiveFlags(i), 1,
gIPAslIds(i), gIPSupplierSiteIds(i),
gIPContractIds(i), gIPContractLineIds(i),
gIPTemplateIds(i), gIPTemplateLineIds(i),
gIPInventoryItemIds(i), gIPMtlCategoryIds(i),
gIPOrgIds(i), gIPSearchTypes(i), gIPUnitPrices(i),
--FPJ FPSL Extractor Changes
gIPValueBasis(i), gIPPurchaseBasis(i),
gIPAllowPriceOverrideFlag(i), gIPNotToExceedPrice(i),
-- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
gIPSuggestedQuantities(i),
-- FPJ Bug# 3110297 jingyu Add negotiated flag
gIPNegotiatedFlag(i),
gIPCurrencys(i), gIPUnitOfMeasures(i),
gIPFunctionalPrices(i), gIPSupplierSiteCodes(i),
gIPContractNums(i), gIPContractLineNums(i),
gIpRateTypes(i), gIpRateDates(i), gIpRates(i),
gIpSupplierNumbers(i), gIpSupplierContactIds(i),
gIpItemRevisions(i), gIpLineTypeIds(i), gIpBuyerIds(i),
NULL, ICX_POR_EXTRACTOR.gLoginId,
ICX_POR_EXTRACTOR.gUserId, gIPLastUpdateDates(i),
ICX_POR_EXTRACTOR.gUserId, SYSDATE,
-- Bug#3352834
ICX_POR_EXT_ITEM.CURRENT_REQUEST_ID,
ICX_POR_EXTRACTOR.gProgramApplicationId,
ICX_POR_EXTRACTOR.gProgramId, SYSDATE);
xActionMode := 'INSERT_ITEMS_B';
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, catalog_name,
last_update_login, last_updated_by, last_update_date,
created_by, creation_date, request_id,
program_application_id, program_id, program_update_date)
VALUES
(gIBRtItemIds(i), 1, gIBOrgIds(i),
gIBSupplierIds(i), gIBSuppliers(i), gIBSupplierPartNums(i),
'##NULL##', gIBInternalItemIds(i), gIBInternalItemNums(i),
'Y', NULL,
ICX_POR_EXTRACTOR.gLoginId, ICX_POR_EXTRACTOR.gUserId, SYSDATE,
ICX_POR_EXTRACTOR.gUserId, SYSDATE, ICX_POR_EXTRACTOR.gRequestId,
ICX_POR_EXTRACTOR.gProgramApplicationId,
ICX_POR_EXTRACTOR.gProgramId, SYSDATE);
xActionMode := 'UPDATE_ITEMS_B';
UPDATE icx_cat_items_b
SET object_version_number = object_version_number + 1,
extractor_updated_flag = gUBExtractorUpdatedFlags(i),
supplier_part_auxid = '##NULL##',
internal_item_num = gUBInternalItemNums(i),
catalog_name = NULL,
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
request_id = ICX_POR_EXTRACTOR.gRequestId,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE rt_item_id = gUBRtItemIds(i);
xActionMode := 'INSERT_ITEMS_TLP';
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)
VALUES
(gITRtItemIds(i), gITLanguages(i), gITOrgIds(i),
gITSupplierIds(i), gITItemSourceTypes(i), gITSearchTypes(i),
gITPrimaryCategoryIds(i), gITPrimaryCategoryNames(i),
gITInternalItemIds(i), gITInternalItemNums(i),
gITSuppliers(i), gITSupplierPartNums(i), '##NULL##', gITManufacturers(i),
gITManufacturerPartNums(i), gITDescriptions(i), NULL, NULL,
gITPictures(i), gITPictureURLs(i), gITPictures(i), NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, ICX_POR_EXTRACTOR.gLoginId,
ICX_POR_EXTRACTOR.gUserId, SYSDATE,
ICX_POR_EXTRACTOR.gUserId, SYSDATE, ICX_POR_EXTRACTOR.gRequestId,
ICX_POR_EXTRACTOR.gProgramApplicationId,
ICX_POR_EXTRACTOR.gProgramId, SYSDATE);
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,
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)
VALUES
(gITRtItemIds(i), gITLanguages(i), gITOrgIds(i),
gITSupplierIds(i), gITItemSourceTypes(i), gITSearchTypes(i),
gITPrimaryCategoryIds(i), gITPrimaryCategoryNames(i),
gITInternalItemIds(i), gITInternalItemNums(i),
gITSuppliers(i), gITSupplierPartNums(i), '##NULL##', gITManufacturers(i),
gITManufacturerPartNums(i), gITDescriptions(i), NULL, NULL,
NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, ICX_POR_EXTRACTOR.gLoginId,
ICX_POR_EXTRACTOR.gUserId, SYSDATE,
ICX_POR_EXTRACTOR.gUserId, SYSDATE, ICX_POR_EXTRACTOR.gRequestId,
ICX_POR_EXTRACTOR.gProgramApplicationId,
ICX_POR_EXTRACTOR.gProgramId, SYSDATE);
xActionMode := 'UPDATE_ITEMS_TLP';
UPDATE icx_cat_items_tlp
SET item_source_type = gUTItemSourceTypes(i),
search_type = gUTSearchTypes(i),
primary_category_id = gUTPrimaryCategoryIds(i),
primary_category_name = gUTPrimaryCategoryNames(i),
internal_item_num = gUTInternalItemNums(i),
description = gUTDescriptions(i),
picture = gUTPictures(i),
picture_url = gUTPictureURLs(i),
supplier_part_auxid = '##NULL##',
--manufacturer = gUTManufacturers(i),
--manufacturer_part_num = gUTManufacturerPartNums(i),
thumbnail_image = gUTPictures(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
request_id = ICX_POR_EXTRACTOR.gRequestId,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE rt_item_id = gUTRtItemIds(i)
AND language = gUTLanguages(i);
UPDATE icx_cat_items_tlp
SET item_source_type = gUTItemSourceTypes(i),
search_type = gUTSearchTypes(i),
primary_category_id = gUTPrimaryCategoryIds(i),
primary_category_name = gUTPrimaryCategoryNames(i),
internal_item_num = gUTInternalItemNums(i),
description = gUTDescriptions(i),
supplier_part_auxid = '##NULL##',
--manufacturer = gUTManufacturers(i),
--manufacturer_part_num = gUTManufacturerPartNums(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
request_id = ICX_POR_EXTRACTOR.gRequestId,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE rt_item_id = gUTRtItemIds(i)
AND language = gUTLanguages(i);
xActionMode := 'INSERT_CATEGORY_ITEMS';
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
(gICRtItemIds(i), gICRtCategoryIds(i),
ICX_POR_EXTRACTOR.gLoginId, ICX_POR_EXTRACTOR.gUserId, SYSDATE,
ICX_POR_EXTRACTOR.gUserId, SYSDATE, ICX_POR_EXTRACTOR.gRequestId,
ICX_POR_EXTRACTOR.gProgramApplicationId,
ICX_POR_EXTRACTOR.gProgramId, SYSDATE);
xActionMode := 'UPDATE_CATEGORY_ITEMS';
UPDATE icx_cat_category_items
SET rt_category_id = gUCRtCategoryIds(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
request_id = ICX_POR_EXTRACTOR.gRequestId,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE rt_item_id = gUCRtItemIds(i)
AND rt_category_id = gUCOldRtCategoryIds(i);
xActionMode := 'INSERT_EXT_ITEMS';
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)
-- bug 2925403
-- SELECT
VALUES
-- gIERtItemIds(i), language_code, gIEOrgIds(i),
(gIERtItemIds(i), gIELanguages(i), gIEOrgIds(i),
gIERtCategoryIds(i), 'Y',
ICX_POR_EXTRACTOR.gLoginId, ICX_POR_EXTRACTOR.gUserId, SYSDATE,
ICX_POR_EXTRACTOR.gUserId, SYSDATE, ICX_POR_EXTRACTOR.gRequestId,
ICX_POR_EXTRACTOR.gProgramApplicationId,
ICX_POR_EXTRACTOR.gProgramId, SYSDATE);
xActionMode := 'UPDATE_EXT_ITEMS';
UPDATE icx_cat_ext_items_tlp
SET rt_category_id = gUERtCategoryIds(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
request_id = ICX_POR_EXTRACTOR.gRequestId,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE rt_item_id = gUERtItemIds(i)
-- bug 2925403
AND language = gUELanguages(i)
AND rt_category_id = gUEOldRtCategoryIds(i);
xActionMode := 'DELETE_ITEM_PRICE';
DELETE FROM icx_cat_item_prices
WHERE rowid = gDPRowIds(i);
DELETE FROM icx_cat_category_items
WHERE rt_category_id = gDPTemplateCategoryIds(i)
AND rt_item_id = gDPRtItemIds(i);
xActionMode := 'DELETE_ITEM_PRICE_GA';
xRtItemIds.DELETE;
DELETE FROM icx_cat_item_prices
WHERE contract_id = gDPGContractIds(i)
AND contract_line_id = gDPGContractLineIds(i)
AND price_type = 'GLOBAL_AGREEMENT'
RETURNING local_rt_item_id BULK COLLECT INTO xRtItemIds;
INSERT INTO icx_cat_extract_gt
(rt_item_id, type)
VALUES (xRtItemIds(i), 'ACTIVE_FLAG');
xActionMode := 'UPDATE_PRICES_GA';
UPDATE icx_cat_item_prices
SET object_version_number = object_version_number + 1,
functional_price = gUPGAFunctionalPrices(i),
supplier_site_id = gUPGASupplierSiteIds(i),
supplier_site_code = gUPGASupplierSiteCodes(i),
-- bug 2912717: populate line_type, rate info. for GA
line_type_id = gUPGALineTypeIds(i),
rate_type = gUPGARateTypes(i),
rate_date = gUPGARateDates(i),
rate = gUPGARates(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
-- Bug#3352834
request_id = ICX_POR_EXT_ITEM.CURRENT_REQUEST_ID,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE contract_id = gUPGAContractIds(i)
AND contract_line_id = gUPGAContractLineIds(i)
-- bug 3298502 : Enabled Org Ids
AND org_id = gUPGAOrgIds(i)
AND price_type = 'GLOBAL_AGREEMENT';
xActionMode := 'INSERT_PRICES_GA';
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,
--FPJ FPSL Extractor Changes
value_basis, purchase_basis,
allow_price_override_flag, not_to_exceed_price,
-- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
suggested_quantity,
-- FPJ Bug# 3110297 jingyu Add negotiated flag
negotiated_by_preparer_flag,
currency, unit_of_measure,
functional_price, supplier_site_code,
contract_num, contract_line_num,
-- bug 2912717: populate line_type, rate info. for GA
line_type_id, rate_type, rate_date, rate,
local_rt_item_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)
VALUES
(gIPGARtItemIds(i), 'GLOBAL_AGREEMENT',
'Y', 1,
NULL_NUMBER, gIPGASupplierSiteIds(i),
gIPGAContractIds(i), gIPGAContractLineIds(i),
NULL_NUMBER, NULL_NUMBER,
gIPGAInventoryItemIds(i), gIPGAMtlCategoryIds(i),
gIPGAOrgIds(i), 'SUPPLIER', gIPGAUnitPrices(i),
--FPJ FPSL Extractor Changes
gIPGAValueBasis(i), gIPGAPurchaseBasis(i),
gIPGAAllowPriceOverrideFlag(i), gIPGANotToExceedPrice(i),
--FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
NULL,
-- FPJ Bug# 3110297 jingyu Add negotiated flag
gIPGANegotiatedFlag(i),
gIPGACurrencys(i), gIPGAUnitOfMeasures(i),
gIPGAFunctionalPrices(i), gIPGASupplierSiteCodes(i),
gIPGAContractNums(i), gIPGAContractLineNums(i),
-- bug 2912717: populate line_type_id for GA
gIPGALineTypeIds(i),
gIPGARateTypes(i), gIPGARateDates(i), gIPGARates(i),
-- Use local_rt_item_id to store local rt_item_id
gIPGALocalRtItemIds(i),
NULL, ICX_POR_EXTRACTOR.gLoginId,
ICX_POR_EXTRACTOR.gUserId, SYSDATE,
ICX_POR_EXTRACTOR.gUserId, SYSDATE,
-- Bug#3352834
ICX_POR_EXT_ITEM.CURRENT_REQUEST_ID,
ICX_POR_EXTRACTOR.gProgramApplicationId,
ICX_POR_EXTRACTOR.gProgramId, SYSDATE);
UPDATE icx_cat_items_tlp
SET request_id = ICX_POR_EXTRACTOR.gRequestId
WHERE rt_item_id = gIPGARtItemIds(i);
UPDATE icx_cat_item_prices
SET local_rt_item_id = gSLRALocalRtItemIds(i)
WHERE rowid = gSLRRowIds(i);
INSERT INTO icx_cat_extract_gt
(rt_item_id, type)
VALUES (gCIRtItemIds(i), 'CLEANUP_ITEM');
xActionMode := 'TOUCH_UPDATED_GA';
INSERT INTO icx_cat_extract_ga_gt
(contract_id, contract_line_id)
VALUES (gUGAContractIds(i), gUGAContractLineIds(i));
INSERT INTO icx_cat_extract_gt
(rt_item_id, type)
VALUES (gTARtItemIds(i), 'ACTIVE_FLAG');
INSERT INTO icx_cat_extract_gt
(rt_item_id, type)
SELECT rt_item_id, 'ACTIVE_FLAG'
FROM icx_cat_items_b
WHERE internal_item_id = gTAInvItemIds(i)
AND org_id = NVL(gTAInvOrgIds(i), org_id)
AND supplier IS NULL;
xActionMode := 'DELETE_PURCHASING_ITEM';
xRtItemIds.DELETE;
DELETE FROM icx_cat_item_prices
WHERE inventory_item_id = gDIPurchasingItemIds(i)
AND org_id = gDIPurchasingOrgIds(i)
AND (search_type = 'SUPPLIER' OR
price_type = 'PURCHASING_ITEM')
AND rownum <= ICX_POR_EXT_UTL.gCommitSize
RETURNING rt_item_id BULK COLLECT INTO xRtItemIds;
INSERT INTO icx_cat_extract_gt
(rt_item_id, type)
VALUES (xRtItemIds(i), 'CLEANUP_ITEM');
UPDATE icx_cat_item_prices
SET active_flag = 'Y'
WHERE rt_item_id = xRtItemIds(i)
AND price_type IN ('BULKLOAD', 'CONTRACT');
DELETE FROM icx_cat_category_items ci
WHERE rt_item_id = xRtItemIds(i)
AND EXISTS (SELECT 'template header'
FROM icx_cat_categories_tl c
WHERE c.rt_category_id = ci.rt_category_id
AND c.type = ICX_POR_EXT_CLASS.TEMPLATE_HEADER_TYPE);
xActionMode := 'DELETE_NULL_PRICE_ITEM';
xRtItemIds.DELETE;
DELETE FROM icx_cat_item_prices
WHERE inventory_item_id = gDINullPriceItemIds(i)
AND org_id = gDINullPriceOrgIds(i)
AND price_type IN ('ASL', 'PURCHASING_ITEM')
AND rownum <= ICX_POR_EXT_UTL.gCommitSize
RETURNING rt_item_id BULK COLLECT INTO xRtItemIds;
INSERT INTO icx_cat_extract_gt
(rt_item_id, type)
VALUES (xRtItemIds(i), 'CLEANUP_ITEM');
xActionMode := 'DELETE_INTERNAL_ITEM';
xRtItemIds.DELETE;
DELETE FROM icx_cat_item_prices
WHERE inventory_item_id = gDIInternalItemIds(i)
AND org_id = gDIInternalOrgIds(i)
AND search_type = 'INTERNAL'
AND rownum <= ICX_POR_EXT_UTL.gCommitSize
RETURNING rt_item_id BULK COLLECT INTO xRtItemIds;
INSERT INTO icx_cat_extract_gt
(rt_item_id, type)
VALUES (xRtItemIds(i), 'CLEANUP_ITEM');
SELECT i.rt_item_id,
t.primary_category_id,
ICX_POR_EXT_ITEM.getMatchTempalteFlag(gCurrentPrice.price_type,
i.rt_item_id,
gCurrentPrice.template_id) match_template_flag
FROM icx_cat_items_b i,
icx_cat_items_tlp t
WHERE i.internal_item_id = gCurrentPrice.internal_item_id
AND i.org_id = gCurrentPrice.org_id
AND (i.supplier IS NULL AND gCurrentPrice.supplier IS NULL OR
i.supplier = gCurrentPrice.supplier)
AND (i.supplier_part_num IS NULL AND
gCurrentPrice.supplier_part_num = TO_CHAR(NULL_NUMBER) OR
i.supplier_part_num = gCurrentPrice.supplier_part_num)
AND t.rt_item_id = i.rt_item_id
AND rownum = 1
UNION ALL
SELECT p.local_rt_item_id,
TO_NUMBER(NULL_NUMBER) primary_category_id, -- for Global Agreement match
'N' match_template_flag
FROM icx_cat_items_b i,
icx_cat_item_prices p
WHERE i.internal_item_id = gCurrentPrice.internal_item_id
AND (i.supplier IS NULL AND gCurrentPrice.supplier IS NULL OR
i.supplier = gCurrentPrice.supplier)
AND (i.supplier_part_num IS NULL AND
gCurrentPrice.supplier_part_num = TO_CHAR(NULL_NUMBER) OR
i.supplier_part_num = gCurrentPrice.supplier_part_num)
AND p.rt_item_id = i.rt_item_id
AND p.price_type = 'GLOBAL_AGREEMENT'
AND p.org_id = gCurrentPrice.org_id
AND rownum = 1;
SELECT p.rt_item_id,
t.primary_category_id,
ICX_POR_EXT_ITEM.getMatchTempalteFlag(gCurrentPrice.price_type,
p.rt_item_id,
gCurrentPrice.template_id) match_template_flag
FROM icx_cat_item_prices p,
icx_cat_items_tlp t
WHERE p.inventory_item_id = gCurrentPrice.internal_item_id
AND p.org_id = gCurrentPrice.org_id
AND p.price_type IN ('PURCHASING_ITEM',
'INTERNAL_TEMPLATE',
'INTERNAL_ITEM')
AND t.rt_item_id = p.rt_item_id
AND rownum = 1;
SELECT i.rt_item_id,
t.primary_category_id,
ICX_POR_EXT_ITEM.getMatchTempalteFlag(gCurrentPrice.price_type,
i.rt_item_id,
gCurrentPrice.template_id) match_template_flag
FROM icx_cat_items_b i,
icx_cat_items_tlp t
WHERE i.internal_item_id IS NULL
AND i.org_id = gCurrentPrice.org_id
AND i.supplier = gCurrentPrice.supplier
AND i.supplier_part_num = gCurrentPrice.supplier_part_num
AND i.supplier_part_auxid = '##NULL##'
AND t.rt_item_id = i.rt_item_id
AND rownum = 1
UNION ALL
SELECT p.local_rt_item_id,
TO_NUMBER(NULL_NUMBER) primary_category_id,
'N' match_template_flag
FROM icx_cat_items_b i,
icx_cat_item_prices p
WHERE i.internal_item_id IS NULL
AND i.supplier = gCurrentPrice.supplier
AND i.supplier_part_num = gCurrentPrice.supplier_part_num
AND i.supplier_part_auxid = '##NULL##'
AND p.rt_item_id = i.rt_item_id
AND p.price_type = 'GLOBAL_AGREEMENT'
AND p.org_id = gCurrentPrice.org_id
AND rownum = 1;
SELECT p.rt_item_id,
t.primary_category_id,
ICX_POR_EXT_ITEM.getMatchTempalteFlag(gCurrentPrice.price_type,
p.rt_item_id,
gCurrentPrice.template_id) match_template_flag
FROM icx_cat_item_prices p,
icx_cat_items_tlp t
WHERE p.inventory_item_id IS NULL
AND p.org_id = gCurrentPrice.org_id
AND p.contract_id = gCurrentPrice.contract_id
AND p.contract_line_id = gCurrentPrice.contract_line_id
AND EXISTS (SELECT 'item with same supplier/supplier_part_num'
FROM icx_cat_items_b i
WHERE i.rt_item_id = p.rt_item_id
AND i.org_id = p.org_id
AND (i.supplier IS NULL AND
gCurrentPrice.supplier IS NULL OR
i.supplier = gCurrentPrice.supplier)
AND (i.supplier_part_num IS NULL AND
gCurrentPrice.supplier_part_num =
TO_CHAR(NULL_NUMBER) OR
i.supplier_part_num = gCurrentPrice.supplier_part_num))
AND t.rt_item_id = p.rt_item_id
AND rownum = 1
UNION ALL
SELECT p.local_rt_item_id,
TO_NUMBER(NULL_NUMBER) primary_category_id,
'N' match_template_flag
FROM icx_cat_item_prices p
WHERE p.inventory_item_id IS NULL
AND p.org_id = gCurrentPrice.org_id
AND p.contract_id = gCurrentPrice.contract_id
AND p.contract_line_id = gCurrentPrice.contract_line_id
AND p.price_type = 'GLOBAL_AGREEMENT'
AND EXISTS (SELECT 'item with same supplier/supplier_part_num'
FROM icx_cat_items_b i
WHERE i.rt_item_id = p.rt_item_id
AND (i.supplier IS NULL AND
gCurrentPrice.supplier IS NULL OR
i.supplier = gCurrentPrice.supplier)
AND (i.supplier_part_num IS NULL AND
gCurrentPrice.supplier_part_num =
TO_CHAR(NULL_NUMBER) OR
i.supplier_part_num = gCurrentPrice.supplier_part_num))
AND rownum = 1;
SELECT i.rt_item_id,
TO_NUMBER(NULL) primary_category_id,
TO_CHAR(NULL) match_template_flag
FROM icx_cat_items_b i
WHERE i.internal_item_id = gCurrentPrice.internal_item_id
AND i.org_id = gCurrentPrice.org_id
AND (i.supplier IS NULL AND gCurrentPrice.supplier IS NULL OR
i.supplier = gCurrentPrice.supplier)
AND (i.supplier_part_num IS NULL AND
gCurrentPrice.supplier_part_num = TO_CHAR(NULL_NUMBER) OR
i.supplier_part_num = gCurrentPrice.supplier_part_num)
AND rownum = 1;
SELECT i.rt_item_id,
TO_NUMBER(NULL) primary_category_id,
TO_CHAR(NULL) match_template_flag
FROM icx_cat_items_b i
WHERE i.internal_item_id IS NULL
AND i.org_id = gCurrentPrice.org_id
AND i.supplier = gCurrentPrice.supplier
AND i.supplier_part_num = gCurrentPrice.supplier_part_num
AND i.supplier_part_auxid = '##NULL##'
AND rownum = 1;
SELECT p.rt_item_id,
TO_NUMBER(NULL) primary_category_id,
TO_CHAR(NULL) match_template_flag
FROM icx_cat_item_prices p
WHERE p.inventory_item_id IS NULL
AND p.org_id = gCurrentPrice.org_id
AND p.contract_id = gCurrentPrice.contract_id
AND p.contract_line_id = gCurrentPrice.contract_line_id
AND EXISTS (SELECT 'item with same supplier/supplier_part_num'
FROM icx_cat_items_b i
WHERE i.rt_item_id = p.rt_item_id
AND i.org_id = p.org_id
AND (i.supplier IS NULL AND
gCurrentPrice.supplier IS NULL OR
i.supplier = gCurrentPrice.supplier)
AND (i.supplier_part_num IS NULL AND
gCurrentPrice.supplier_part_num =
TO_CHAR(NULL_NUMBER) OR
i.supplier_part_num = gCurrentPrice.supplier_part_num))
AND rownum = 1;
RETURN DELETE_PRICE;
SELECT icx_por_itemid.nextval
INTO xRtItemId
FROM dual;
SELECT icx_por_itemid.nextval
INTO xRtItemId
FROM dual;
PROCEDURE updateItemPrices IS
xErrLoc PLS_INTEGER := 100;
gUPLastUpdateDates(xIndex) := gCurrentPrice.last_update_date;
ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.updateItemPrices-'||
xErrLoc||' '||SQLERRM);
END updateItemPrices;
PROCEDURE insertItemPrices IS
xErrLoc PLS_INTEGER := 100;
gIPLastUpdateDates(xIndex) := gCurrentPrice.last_update_date;
ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.insertItemPrices-'||
xErrLoc||' '||SQLERRM);
END insertItemPrices;
PROCEDURE insertItemsB IS
xErrLoc PLS_INTEGER := 100;
ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.insertItemsB-'||
xErrLoc||' '||SQLERRM);
END insertItemsB;
PROCEDURE updateItemsB IS
xErrLoc PLS_INTEGER := 100;
gUBExtractorUpdatedFlags(xIndex) := 'Y';
ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.updateItemsB-'||
xErrLoc||' '||SQLERRM);
END updateItemsB;
PROCEDURE insertItemsTLP IS
xString VARCHAR2(2000);
'SELECT m.description, ' ||
'm.language, ' ||
'ctl.category_name ';
'AND m.language IN (SELECT language_code ' ||
'FROM fnd_languages ' ||
'WHERE installed_flag IN (''B'', ''I'')) ';
xString := 'SELECT language_code ' || 'FROM fnd_languages ' || 'WHERE installed_flag IN (''B''';
ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.insertItemsTLP-'||
xErrLoc||' '||SQLERRM);
END insertItemsTLP;
PROCEDURE updateItemsTLP IS
xString VARCHAR2(2000);
'SELECT m.description, ' ||
'm.language, ' ||
'ctl.category_name, ' ||
't.rt_item_id ';
'AND m.language IN (SELECT language_code ' ||
'FROM fnd_languages ' ||
'WHERE installed_flag IN (''B'', ''I'')) ' ||
'AND m.language = t.language (+) ' ||
'AND t.rt_item_id (+) = :rt_item_id';
xString := 'SELECT f.language_code, i.rt_item_id ' ||
'FROM fnd_languages f, icx_cat_items_tlp i ' ||
'WHERE ' ||
'f.language_code = i.language(+) AND ' ||
'i.rt_item_id(+) = :rt_item_id AND ' ||
'f.installed_flag IN (''B''';
ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.updateItemsTLP-'||
xErrLoc||' '||SQLERRM);
END updateItemsTLP;
PROCEDURE insertExtItemsTLP IS
xString VARCHAR2(2000);
'SELECT m.language ';
'AND m.language IN (SELECT language_code ' ||
'FROM fnd_languages ' ||
'WHERE installed_flag IN (''B'', ''I'')) ';
'SELECT language_code ' ||
'FROM fnd_languages ' ||
'WHERE installed_flag IN (''B''';
ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.insertExtItemsTLP-'||
xErrLoc||' '||SQLERRM);
END insertExtItemsTLP;
PROCEDURE updateExtItemsTLP IS
xString VARCHAR2(2000);
'SELECT m.language, ' ||
't.rt_item_id ';
'AND m.language IN (SELECT language_code ' ||
'FROM fnd_languages ' ||
'WHERE installed_flag IN (''B'', ''I'')) ' ||
'AND m.language = t.language (+) ' ||
'AND t.rt_item_id (+) = :rt_item_id';
'SELECT f.language_code, i.rt_item_id ' ||
'FROM fnd_languages f, icx_cat_ext_items_tlp i ' ||
'WHERE ' ||
'f.language_code = i.language(+) AND ' ||
'i.rt_item_id(+) = :rt_item_id AND ' ||
'f.installed_flag IN (''B''';
ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.updateExtItemsTLP-'||
xErrLoc||' '||SQLERRM);
END updateExtItemsTLP;
PROCEDURE insertPrimaryCategoryItems IS
xErrLoc PLS_INTEGER := 100;
'ICX_POR_EXT_ITEM.insertPrimaryCategoryItems-'||
xErrLoc||' '||SQLERRM);
END insertPrimaryCategoryItems;
PROCEDURE updatePrimaryCategoryItems IS
xErrLoc PLS_INTEGER := 100;
'ICX_POR_EXT_ITEM.updatePrimaryCategoryItems-'||
xErrLoc||' '||SQLERRM);
END updatePrimaryCategoryItems;
PROCEDURE insertTemplateCategoryItems IS
xErrLoc PLS_INTEGER := 100;
'ICX_POR_EXT_ITEM.insertTemplateCategoryItems-'||
xErrLoc||' '||SQLERRM);
END insertTemplateCategoryItems;
PROCEDURE touchUpdatedGA(pContractId IN NUMBER,
pContractLineId IN NUMBER)
IS
xErrLoc PLS_INTEGER := 100;
'ICX_POR_EXT_ITEM.touchUpdatedGA-'||
xErrLoc||' '||SQLERRM);
END touchUpdatedGA;
PROCEDURE deleteItemPrices IS
xErrLoc PLS_INTEGER := 100;
'Enter deleteItemPrices()');
'ICX_POR_EXT_ITEM.deleteItemPrices-'||
xErrLoc||' '||SQLERRM);
END deleteItemPrices;
PROCEDURE deleteItem IS
xErrLoc PLS_INTEGER := 100;
'Enter deleteItem()');
'ICX_POR_EXT_ITEM.deleteItem-'||
xErrLoc||' '||SQLERRM);
END deleteItem;
PROCEDURE updateItemPricesGA IS
xErrLoc PLS_INTEGER := 100;
'Enter updateItemPricesGA()');
ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.updateItemPricesGA-'||
xErrLoc||' '||SQLERRM);
END updateItemPricesGA;
PROCEDURE insertItemPricesGA IS
xErrLoc PLS_INTEGER := 100;
'Enter insertItemPricesGA()');
ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.insertItemPricesGA-'||
xErrLoc||' '||SQLERRM);
END insertItemPricesGA;
updateItemPrices;
insertItemPrices;
updateItemPrices;
updateItemPrices;
updateItemsB;
updateItemsTLP;
updateExtItemsTLP;
updatePrimaryCategoryItems;
insertItemPrices;
updateItemPrices;
updateItemsB;
updateItemsTLP;
updateExtItemsTLP;
updatePrimaryCategoryItems;
insertTemplateCategoryItems;
insertItemPrices;
updateItemPrices;
insertItemsB;
insertItemsTLP;
insertExtItemsTLP;
insertPrimaryCategoryItems;
insertTemplateCategoryItems;
ELSIF (xItemStatus = DELETE_PRICE) THEN
xErrLoc := 600;
deleteItemPrices;
deleteItem;
touchUpdatedGA(gCurrentPrice.contract_id,
gCurrentPrice.contract_line_id);
ITEM_MATCH, DELETE_PRICE))
THEN
touchCleanupItem(gCurrentPrice.price_rt_item_id);
ELSIF xItemStatus IN (NEW_ITEM, NEW_GA_ITEM, DELETE_PRICE) THEN
-- We need to reset actice_flag for price_rt_item_id
IF gCurrentPrice.price_rt_item_id IS NOT NULL THEN
touchRtItemActiveFlag(gCurrentPrice.price_rt_item_id);
IF (xItemStatus = DELETE_PRICE) THEN
IF (gCurrentPrice.price_rowid IS NOT NULL) THEN
xErrLoc := 900;
deleteItemPrices;
updateItemPricesGA;
insertItemPricesGA;
IF xItemStatus IN (ITEM_MATCH, NEW_ITEM, DELETE_PRICE) THEN
-- We need to reset actice_flag for rt_item_id
IF gCurrentPrice.rt_item_id IS NOT NULL THEN
touchRtItemActiveFlag(gCurrentPrice.rt_item_id);
cUpdatedPriceRows tCursorType;
l_last_update_date DBMS_SQL.DATE_TABLE;
open cUpdatedPriceRows for cSqlString;
l_document_type.DELETE;
l_last_update_date.DELETE;
l_org_id.DELETE;
l_supplier_id.DELETE;
l_supplier_part_num.DELETE;
l_internal_item_id.DELETE;
l_mtl_category_id.DELETE;
l_category_key.DELETE;
l_description.DELETE;
l_picture.DELETE;
l_picture_url.DELETE;
l_price_type.DELETE;
l_asl_id.DELETE;
l_supplier_site_id.DELETE;
l_contract_id.DELETE;
l_contract_line_id.DELETE;
l_template_id.DELETE;
l_template_line_id.DELETE;
l_price_search_type.DELETE;
l_unit_price.DELETE;
l_value_basis.DELETE;
l_purchase_basis.DELETE;
l_allow_price_override_flag.DELETE;
l_not_to_exceed_price.DELETE;
l_suggested_quantity.DELETE;
l_negotiated_by_preparer_flag.DELETE;
l_currency.DELETE;
l_unit_of_measure.DELETE;
l_functional_price.DELETE;
l_contract_num.DELETE;
l_contract_line_num.DELETE;
l_manufacturer.DELETE;
l_manufacturer_part_num.DELETE;
l_rate_type.DELETE;
l_rate_date.DELETE;
l_rate.DELETE;
l_supplier_number.DELETE;
l_supplier_contact_id.DELETE;
l_item_revision.DELETE;
l_line_type_id.DELETE;
l_buyer_id.DELETE;
l_global_agreement_flag.DELETE;
l_status.DELETE;
l_supplier.DELETE;
l_supplier_site_code.DELETE;
l_internal_item_num.DELETE;
l_inventory_organization_id.DELETE;
l_item_source_type.DELETE;
l_item_search_type.DELETE;
l_primary_category_id.DELETE;
l_primary_category_name.DELETE;
l_template_category_id.DELETE;
l_price_rt_item_id.DELETE;
l_price_internal_item_id.DELETE;
l_price_supplier_id.DELETE;
l_price_supplier_part_num.DELETE;
l_price_contract_line_id.DELETE;
l_price_mtl_category_id.DELETE;
l_match_primary_category_id.DELETE;
l_rt_item_id.DELETE;
l_local_rt_item_id.DELETE;
l_match_template_flag.DELETE;
l_active_flag.DELETE;
l_price_rowid.DELETE;
EXIT WHEN cUpdatedPriceRows%NOTFOUND;
FETCH cUpdatedPriceRows INTO
l_document_type(i), l_last_update_date(i), l_org_id(i),
l_supplier_id(i), l_supplier(i), l_supplier_site_code(i),
l_supplier_part_num(i), l_internal_item_id(i), l_internal_item_num(i),
l_inventory_organization_id(i), l_item_source_type(i), l_item_search_type(i),
l_mtl_category_id(i), l_category_key(i), l_description(i),
l_picture(i), l_picture_url(i), l_price_type(i),
l_asl_id(i), l_supplier_site_id(i), l_contract_id(i),
l_contract_line_id(i), l_template_id(i), l_template_line_id(i),
l_price_search_type(i), l_unit_price(i),
--FPJ FPSL Extractor Changes
l_value_basis(i), l_purchase_basis(i),
l_allow_price_override_flag(i), l_not_to_exceed_price(i),
-- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
l_suggested_quantity(i),
-- FPJ Bug# 3110297 jingyu Add negotiated flag
l_negotiated_by_preparer_flag(i),
l_currency(i),
l_unit_of_measure(i), l_functional_price(i), l_contract_num(i),
l_contract_line_num(i), l_manufacturer(i), l_manufacturer_part_num(i),
l_rate_type(i), l_rate_date(i), l_rate(i), l_supplier_number(i),
l_supplier_contact_id(i), l_item_revision(i), l_line_type_id(i),
l_buyer_id(i), l_global_agreement_flag(i), l_status(i),
l_primary_category_id(i), l_primary_category_name(i), l_template_category_id(i),
l_price_rt_item_id(i), l_price_internal_item_id(i), l_price_supplier_id(i),
l_price_supplier_part_num(i), l_price_contract_line_id(i),
l_price_mtl_category_id(i), l_match_primary_category_id(i), l_rt_item_id(i),
l_local_rt_item_id(i), l_match_template_flag(i), l_active_flag(i), l_price_rowid(i);
EXIT WHEN cUpdatedPriceRows%NOTFOUND;
FETCH cUpdatedPriceRows
BULK COLLECT INTO
l_document_type, l_last_update_date, l_org_id,
l_supplier_id, l_supplier, l_supplier_site_code,
l_supplier_part_num, l_internal_item_id, l_internal_item_num,
l_inventory_organization_id, l_item_source_type, l_item_search_type,
l_mtl_category_id, l_category_key, l_description,
l_picture, l_picture_url, l_price_type,
l_asl_id, l_supplier_site_id, l_contract_id,
l_contract_line_id, l_template_id, l_template_line_id,
l_price_search_type, l_unit_price,
--FPJ FPSL Extractor Changes
l_value_basis, l_purchase_basis,
l_allow_price_override_flag, l_not_to_exceed_price,
-- new FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
l_suggested_quantity,
-- FPJ Bug# 3110297 jingyu Add negotiated flag
l_negotiated_by_preparer_flag,
l_currency,
l_unit_of_measure, l_functional_price, l_contract_num,
l_contract_line_num, l_manufacturer, l_manufacturer_part_num,
l_rate_type, l_rate_date, l_rate, l_supplier_number,
l_supplier_contact_id, l_item_revision, l_line_type_id,
l_buyer_id, l_global_agreement_flag, l_status,
l_primary_category_id, l_primary_category_name, l_template_category_id,
l_price_rt_item_id, l_price_internal_item_id, l_price_supplier_id,
l_price_supplier_part_num, l_price_contract_line_id,
l_price_mtl_category_id, l_match_primary_category_id, l_rt_item_id,
l_local_rt_item_id, l_match_template_flag, l_active_flag, l_price_rowid
LIMIT ICX_POR_EXT_UTL.gCommitSize;
gCurrentPrice.last_update_date := l_last_update_date(i);
IF (cUpdatedPriceRows%ISOPEN) THEN
xErrLoc := 284;
CLOSE cUpdatedPriceRows;
OPEN cUpdatedPriceRows for cSqlString;
IF (cUpdatedPriceRows%ISOPEN) THEN
xErrLoc := 410;
CLOSE cUpdatedPriceRows;
IF (cUpdatedPriceRows%ISOPEN) THEN
CLOSE cUpdatedPriceRows;
IF (cUpdatedPriceRows%ISOPEN) THEN
CLOSE cUpdatedPriceRows;
SELECT name
INTO l_owning_ou_name
FROM hr_all_organization_units
WHERE organization_id = x_owning_org_id;
SELECT currency_code
INTO l_to_currency
FROM GL_SETS_OF_BOOKS
WHERE set_of_books_id = x_set_of_books_id;
SELECT USER_CONVERSION_TYPE
INTO l_user_conv_type
FROM GL_DAILY_CONVERSION_TYPES
WHERE CONVERSION_TYPE = x_conversion_type;
xSelectStr VARCHAR2(4000) := NULL;
SELECT unit_of_measure
INTO xOneValidUomCode
FROM mtl_units_of_measure_tl
WHERE rownum = 1;
xSelectStr :=
'SELECT /*+ LEADING(doc) */ ' ||
'doc.*, ' ||
'ic1.rt_category_id primary_category_id, ' ||
'ic1.category_name primary_category_name, ';
xSelectStr := xSelectStr ||
'ic2.rt_category_id template_category_id, ';
xSelectStr := xSelectStr ||
'TO_NUMBER(NULL) template_category_id, ';
xSelectStr := xSelectStr ||
'p.rt_item_id price_rt_item_id, ' ||
'NVL(i.internal_item_id, '||NULL_NUMBER||
') price_internal_item_id, ' ||
'NVL(i.supplier_id, '||NULL_NUMBER||') price_supplier_id, ' ||
'NVL(i.supplier_part_num, TO_CHAR('||NULL_NUMBER||
')) price_supplier_part_num, ' ||
'p.contract_line_id price_contract_line_id, ' ||
'p.mtl_category_id price_mtl_category_id, ' ||
'ic3.rt_category_id match_primary_category_id, ' ||
'TO_NUMBER(NULL) rt_item_id, ' ||
'TO_NUMBER(NULL) local_rt_item_id, '||
'''N'' match_template_flag, ';
xSelectStr := xSelectStr ||
'DECODE(doc.status, ' || ICX_POR_EXT_DIAG.VALID_FOR_EXTRACT ||
', ICX_POR_EXT_ITEM.getItemActiveFlag(doc.internal_item_id, doc.org_id), ' ||
'NULL) active_flag, ';
xSelectStr := xSelectStr ||
'p.active_flag active_flag, ';
xSelectStr := xSelectStr ||
'ROWIDTOCHAR(p.rowid) price_rowid ';
'SELECT DECODE(prl.source_type_code, ''VENDOR'', '||
TEMPLATE_TYPE||', '||
INTERNAL_TEMPLATE_TYPE||') document_type, '||
'greatest(prl.last_update_date, prh.last_update_date) ' ||
'last_update_date, '||
'NVL(prl.org_id, '||NULL_NUMBER||') org_id, '||
'NVL(nvl(ph.vendor_id, prl.suggested_vendor_id), '||
NULL_NUMBER||') supplier_id, '||
'icx_pv.vendor_name supplier, '||
'pvs.vendor_site_code supplier_site_code, '||
'NVL(nvl(pl.vendor_product_num, prl.suggested_vendor_product_code), '||
'TO_CHAR('||NULL_NUMBER||')) supplier_part_num, '||
'NVL(prl.item_id, '||NULL_NUMBER||') internal_item_id, '||
'mi.concatenated_segments internal_item_num, '||
'mi.organization_id inventory_organization_id, '||
'ICX_POR_EXT_ITEM.getItemSourceType(DECODE(prl.source_type_code, ''VENDOR'', '||
'''TEMPLATE'', ''INTERNAL_TEMPLATE''), prl.item_id, ' ||
'mi.purchasing_enabled_flag, mi.outside_operation_flag, ' ||
'mi.list_price_per_unit, l.load_item_master, ' ||
'mi.internal_order_enabled_flag, l.load_internal_item) item_source_type, ' ||
'ICX_POR_EXT_ITEM.getSearchType(DECODE(prl.source_type_code, ''VENDOR'', '||
'''TEMPLATE'', ''INTERNAL_TEMPLATE''), prl.item_id, ' ||
'mi.purchasing_enabled_flag, mi.outside_operation_flag, ' ||
'mi.list_price_per_unit, l.load_item_master, ' ||
'mi.internal_order_enabled_flag, l.load_internal_item) item_search_type, ' ||
'nvl(pl.category_id, prl.category_id) mtl_category_id, '||
'TO_CHAR(nvl(pl.category_id, prl.category_id)) category_key, '||
'prl.item_description description, '||
'TO_CHAR(NULL) picture, '||
'TO_CHAR(NULL) picture_url, '||
'DECODE(prl.source_type_code, ''VENDOR'', '||
'''TEMPLATE'', ''INTERNAL_TEMPLATE'') price_type, '||
'TO_NUMBER('||NULL_NUMBER||') asl_id, '||
'NVL(nvl(ph.vendor_site_id, prl.suggested_vendor_site_id), '||
''||NULL_NUMBER||') supplier_site_id, '||
'NVL(prl.po_header_id, '||NULL_NUMBER||') contract_id, '||
'NVL(prl.po_line_id, '||NULL_NUMBER||') contract_line_id, '||
'prl.express_name template_id, '||
'prl.sequence_num template_line_id, '||
'DECODE(prl.source_type_code, ''VENDOR'', '||
'''SUPPLIER'', ''INTERNAL'') price_search_type, '||
--FPJ FPSL Extractor Changes
-- If value_basis i.e. order_type_lookup_code is 'FIXED PRICE'
-- Then extractor will store amount in unit_price
'DECODE(prl.source_type_code, ''VENDOR'', '||
'DECODE(pltb.order_type_lookup_code, ''FIXED PRICE'', '||
'nvl(pl.amount, prl.amount), '||
'nvl(pl.unit_price, prl.unit_price)), NULL) unit_price, '||
--FPJ FPSL Extractor Changes
'pltb.order_type_lookup_code value_basis, '||
'pltb.purchase_basis purchase_basis, '||
--FPJ FPSL Extractor Changes
--allow_price_override_flag and not_to_exceed_price are not
--supported in req templates, so we will get it directly
--from po lines if the req template is sourced from a po.
'pl.allow_price_override_flag allow_price_override_flag, '||
'pl.not_to_exceed_price not_to_exceed_price, '||
-- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
'prl.suggested_quantity suggested_quantity, '||
-- FPJ Bug# 3110297 jingyu Add negotiated flag
'nvl(pl.negotiated_by_preparer_flag, prl.negotiated_by_preparer_flag) negotiated_by_preparer_flag, '||
'DECODE(prl.source_type_code, ''VENDOR'', '||
'nvl(ph.currency_code, gsb.currency_code), NULL) currency, '||
/* Bug#3693294 : srmani
* An Outer Join is required with UOMTL Table, as the Fixed Price Line Type Items do not have
* a UOM (is null).
* But as we need to pick up the uom from the Po Lines table (when a template is sourced from a
* blanket line), we can't have this outer join in the uomtl table.
* As a hack to eliminate the outer join, we're equating the FPLT Item with a valid UOM , that is
* retrieved at the start of this procedure for the equi join condition.
* Here in the select we want to put back the UOM as null for the FPLT Item Prices.
*/
'DECODE(pltb.order_type_lookup_code, ''FIXED PRICE'', NULL, uomtl.uom_code) unit_of_measure, '||
--FPJ FPSL Extractor Changes
'DECODE(prl.source_type_code, ''VENDOR'', '||
'nvl(decode(gc.minimum_accountable_unit, null, '||
'round(DECODE(pltb.order_type_lookup_code, ''FIXED PRICE'', '||
'pl.amount, pl.unit_price)*nvl(ph.rate, 1),gc.extended_precision), '||
'round(DECODE(pltb.order_type_lookup_code, ''FIXED PRICE'', '||
'pl.amount, pl.unit_price)*nvl(ph.rate, 1)/gc.minimum_accountable_unit)* '||
'gc.minimum_accountable_unit), '||
'DECODE(pltb.order_type_lookup_code, ''FIXED PRICE'', '||
'prl.amount, prl.unit_price)), NULL) functional_price, '||
'ph.segment1 contract_num, '||
'pl.line_num contract_line_num, '||
'TO_CHAR(NULL) manufacturer, '||
'TO_CHAR(NULL) manufacturer_part_num, '||
'ph.rate_type, '||
'ph.rate_date, '||
'ph.rate, '||
'icx_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, '||
'TO_CHAR(NULL) global_agreement_flag, '||
'ICX_POR_EXT_DIAG.getTemplateLineStatus(prl.express_name, '||
'prl.sequence_num, prl.org_id, prh.inactive_date, '||
'prl.po_line_id, '''||ICX_POR_EXT_TEST.gTestMode||''') status ';
'greatest(NVL(mi.last_update_date, l.template_lines_last_run_date-1), ' ||
'prl.last_update_date, prh.last_update_date) > '||
'l.template_lines_last_run_date OR '||
'prh.inactive_date BETWEEN l.template_lines_last_run_date AND '||
'SYSDATE) '||
'AND prl.express_name = prh.express_name '||
/* Bug#3693294 : srmani. Pick up the UOM from the
* - BPA Line (when sourced from a BPA),
* - Template Line (otherwise).
* To do this we'll have to equate the UOM Code in the Po Lines table with UOMTL
* As, there already exists an outer join on Po Lines, we can't have the outer
* join on UOMTL (introduced for FPLT Items. ). To still have this work functionally
* without an outerjoin, we're doing an equi-join on Template Line (with line type
* as fixed price) and UOMTL using a valid UOM (retrieved earlier in the procedure.
*/
'AND DECODE(pltb.order_type_lookup_code, ' ||
' ''FIXED PRICE'', ''' || xOneValidUomCode || ''' , ' ||
' NVL(pl.unit_meas_lookup_code, prl.unit_meas_lookup_code) ) = ' ||
' uomtl.unit_of_measure ' ||
'AND uomtl.language = ''' || ICX_POR_EXTRACTOR.gBaseLang || ''' ' ||
--Bug#2998604 'AND uomtl.source_lang = uomtl.language '||
'AND (prl.org_id is null and prh.org_id is null or '||
'prl.org_id = prh.org_id) '||
'AND prl.po_header_id = ph.po_header_id(+) '||
'AND prl.po_line_id = pl.po_line_id(+) '||
'AND (prh.org_id is null and fsp.org_id is null or '||
'prh.org_id = fsp.org_id) '||
'AND gsb.set_of_books_id = fsp.set_of_books_id '||
'AND nvl(ph.currency_code, gsb.currency_code) = gc.currency_code '||
'AND fsp.inventory_organization_id = NVL(mi.organization_id, '||
'fsp.inventory_organization_id) '||
'AND prl.item_id = mi.inventory_item_id (+) '||
'AND prl.suggested_vendor_id = icx_pv.vendor_id (+) '||
'AND prl.suggested_vendor_site_id = pvs.vendor_site_id (+) '||
--FPJ FPSL Extractor Changes
'AND prl.line_type_id = pltb.line_type_id '||
'AND NVL(pltb.purchase_basis, ''NULL'') <> ''TEMP LABOR'' ';
'SELECT '||CONTRACT_TYPE||' document_type, '||
'greatest(pl.last_update_date, ph.last_update_date) '||
'last_update_date, '||
'NVL(pl.org_id, '||NULL_NUMBER||') org_id, '||
'NVL(ph.vendor_id, '||NULL_NUMBER||') supplier_id, '||
'icx_pv.vendor_name supplier, '||
'';
'greatest(NVL(mi.last_update_date, l.contracts_last_run_date-1), '||
'pl.last_update_date, ph.last_update_date ';
', icx_psp.last_update_date ';
'mck.last_update_date > l.contracts_last_run_date OR ' ||
'ph.end_date BETWEEN l.contracts_last_run_date AND SYSDATE OR '||
-- pcreddy # 3122831
'trunc(ph.start_date) between trunc(l.contracts_last_run_date) and trunc(sysdate) OR '||
'pl.expiration_date BETWEEN l.contracts_last_run_date AND '||
'SYSDATE OR '||
'(ph.type_lookup_code = ''QUOTATION'' AND '||
'EXISTS (SELECT ''updated quotaion line location'' ';
'AND GREATEST(pll.last_update_date, pqa.last_update_date) > '||
'l.contracts_last_run_date))) '||
'AND ph.po_header_id = pl.po_header_id '||
--Bug #4474307
'AND mck.category_id = pl.category_id ' ||
--Bug #4474307 - end
'';
'SELECT /*+ LEADING(pasl) */'||ASL_TYPE||' document_type, '||
'pasl.last_update_date, '||
'NVL(fsp.org_id, '||NULL_NUMBER||') org_id, '||
'NVL(pasl.vendor_id, '||NULL_NUMBER||') supplier_id, '||
'icx_pv.vendor_name supplier, '||
'Decode( fsp.org_id, pvs.org_id , pvs.vendor_site_code,TO_CHAR(NULL)) supplier_site_code ,' ||
'NVL(pasl.primary_vendor_item, TO_CHAR('||NULL_NUMBER||
')) supplier_part_num, '||
'pasl.item_id internal_item_id, '||
'mi.concatenated_segments internal_item_num, '||
'mi.organization_id inventory_organization_id, '||
'ICX_POR_EXT_ITEM.getItemSourceType(''ASL'', pasl.item_id, ' ||
'mi.purchasing_enabled_flag, mi.outside_operation_flag, ' ||
'mi.list_price_per_unit, l.load_item_master, ' ||
'mi.internal_order_enabled_flag, l.load_internal_item) item_source_type, ' ||
'''SUPPLIER'' item_search_type, '||
'NVL(pasl.category_id, mic.category_id) mtl_category_id, '||
'TO_CHAR(NVL(pasl.category_id, mic.category_id)) category_key, '||
'mitl.description description, '||
'TO_CHAR(NULL) picture, '||
'TO_CHAR(NULL) picture_url, '||
'''ASL'' price_type, '||
'pasl.asl_id, '||
'Decode( fsp.org_id, pvs.org_id ,pasl.vendor_site_id,'||NULL_NUMBER||') supplier_site_id ,' ||
'TO_NUMBER('||NULL_NUMBER||') contract_id, '||
'TO_NUMBER('||NULL_NUMBER||') contract_line_id, '||
'TO_CHAR('||NULL_NUMBER||') template_id, '||
'TO_NUMBER('||NULL_NUMBER||') template_line_id, '||
'''SUPPLIER'' price_search_type, '||
'mi.list_price_per_unit unit_price, '||
--FPJ FPSL Extractor Changes
'TO_CHAR(NULL) value_basis, '||
'TO_CHAR(NULL) purchase_basis, '||
'TO_CHAR(NULL) allow_price_override_flag, '||
'TO_NUMBER(NULL) not_to_exceed_price, '||
-- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
'TO_NUMBER('||NULL_NUMBER||') suggested_quantity, '||
-- FPJ Bug# 3110297 jingyu Add negotiated flag
'''N'' negotiated_by_preparer_flag, ' ||
'gsb.currency_code currency, '||
'mi.primary_uom_code unit_of_measure, '||
'mi.list_price_per_unit functional_price, '||
'TO_CHAR(NULL) contract_num, '||
'TO_NUMBER(NULL) contract_line_num, '||
'TO_CHAR(NULL) manufacturer, '||
'TO_CHAR(NULL) manufacturer_part_num, '||
'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, '||
'TO_CHAR(NULL) global_agreement_flag, '||
'ICX_POR_EXT_DIAG.getASLStatus(pasl.asl_id, '||
'pasl.disable_flag, pasl.asl_status_id, '||
'mi.list_price_per_unit, '''||ICX_POR_EXT_TEST.gTestMode||
''') status ';
'GREATEST(NVL(mi.last_update_date, l.item_master_last_run_date-1), '||
'pasl.last_update_date) > l.item_master_last_run_date) '||
'AND mic.category_set_id = '||gCategorySetId||' '||
'AND fsp.inventory_organization_id = pasl.owning_organization_id '||
'AND mic.inventory_item_id = pasl.item_id '||
'AND mic.organization_id = pasl.owning_organization_id '||
'AND pasl.item_id = mi.inventory_item_id '||
'AND pasl.owning_organization_id = mi.organization_id '||
'AND fsp.set_of_books_id = gsb.set_of_books_id '||
'AND mi.inventory_item_id = mitl.inventory_item_id '||
'AND mi.organization_id = mitl.organization_id '||
'AND mitl.language = '''||ICX_POR_EXTRACTOR.gBaseLang||''' '||
'AND pasl.vendor_id = icx_pv.vendor_id '||
'AND pasl.vendor_site_id = pvs.vendor_site_id (+) ';
'SELECT /*+ cardinality(type 2) first_rows use_nl(type l fsp) */ type.document_type, '||
'mi.last_update_date, '||
'NVL(fsp.org_id, '||NULL_NUMBER||') org_id, '||
'TO_NUMBER('||NULL_NUMBER||') supplier_id, '||
'TO_CHAR(NULL) supplier, '||
'TO_CHAR(NULL) supplier_site_code, '||
'TO_CHAR('||NULL_NUMBER||') supplier_part_num, '||
'mi.inventory_item_id internal_item_id, '||
'mi.concatenated_segments internal_item_num, '||
'mi.organization_id inventory_organization_id, '||
'ICX_POR_EXT_ITEM.getItemSourceType(type.price_type, mi.inventory_item_id, ' ||
'mi.purchasing_enabled_flag, mi.outside_operation_flag, ' ||
'mi.list_price_per_unit, l.load_item_master, ' ||
'mi.internal_order_enabled_flag, l.load_internal_item) item_source_type, ' ||
'ICX_POR_EXT_ITEM.getSearchType(type.price_type, mi.inventory_item_id, ' ||
'mi.purchasing_enabled_flag, mi.outside_operation_flag, ' ||
'mi.list_price_per_unit, l.load_item_master, ' ||
'mi.internal_order_enabled_flag, l.load_internal_item) item_search_type, ' ||
'mic.category_id mtl_category_id, '||
'TO_CHAR(mic.category_id) category_key, '||
'TO_CHAR(NULL) description, '||
'TO_CHAR(NULL) picture, '||
'TO_CHAR(NULL) picture_url, '||
'type.price_type, '||
'TO_NUMBER('||NULL_NUMBER||') asl_id, '||
'TO_NUMBER('||NULL_NUMBER||') supplier_site_id, '||
'TO_NUMBER('||NULL_NUMBER||') contract_id, '||
'TO_NUMBER('||NULL_NUMBER||') contract_line_id, '||
'TO_CHAR('||NULL_NUMBER||') template_id, '||
'TO_NUMBER('||NULL_NUMBER||') template_line_id, '||
'type.price_search_type, '||
'DECODE(type.document_type, '||PURCHASING_ITEM_TYPE||', '||
'mi.list_price_per_unit, NULL) unit_price, '||
--FPJ FPSL Extractor Changes
'TO_CHAR(NULL) value_basis, '||
'TO_CHAR(NULL) purchase_basis, '||
'TO_CHAR(NULL) allow_price_override_flag, '||
'TO_NUMBER(NULL) not_to_exceed_price, '||
-- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
'TO_NUMBER('||NULL_NUMBER||') suggested_quantity, '||
-- FPJ Bug# 3110297 jingyu Add negotiated flag
'''N'' negotiated_by_preparer_flag, ' ||
'DECODE(type.document_type, '||PURCHASING_ITEM_TYPE||', '||
'gsb.currency_code, NULL) currency, '||
'DECODE(type.document_type, '||PURCHASING_ITEM_TYPE||', '||
'mi.primary_uom_code, '||
'NVL(muom.uom_code, mi.primary_uom_code)) unit_of_measure, '||
'DECODE(type.document_type, '||PURCHASING_ITEM_TYPE||', '||
'mi.list_price_per_unit, NULL) functional_price, '||
'TO_CHAR(NULL) contract_num, '||
'TO_NUMBER(NULL) contract_line_num, '||
'TO_CHAR(NULL) manufacturer, '||
'TO_CHAR(NULL) manufacturer_part_num, '||
'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, '||
'TO_CHAR(NULL) global_agreement_flag, '||
'DECODE(type.document_type, '||PURCHASING_ITEM_TYPE||', '||
'ICX_POR_EXT_DIAG.getPurchasingItemStatus(mi.purchasing_enabled_flag, '||
'mi.outside_operation_flag, '||
'mi.list_price_per_unit, '''||ICX_POR_EXT_TEST.gTestMode||'''), '||
'ICX_POR_EXT_DIAG.getInternalItemStatus(mi.internal_order_enabled_flag, '''||
ICX_POR_EXT_TEST.gTestMode||''')) status ';
'(SELECT '||PURCHASING_ITEM_TYPE||' document_type, '||
'''PURCHASING_ITEM'' price_type, '||
'''SUPPLIER'' price_search_type '||
'FROM dual '||
'UNION ALL '||
'SELECT '||INTERNAL_ITEM_TYPE||' document_type, '||
'''INTERNAL_ITEM'' price_type, '||
'''INTERNAL'' price_search_type '||
'FROM dual) type '||
-- Bug#3213218/3163334 : pcreddy - Check for the load flag to be 'Y'.
'WHERE ( (l.load_item_master = ''Y'' AND ' ||
' l.item_master_last_run_date IS NULL) OR ' ||
' (l.load_internal_item = ''Y'' AND ' ||
' l.internal_item_last_run_date IS NULL) OR ' ||
-- 'WHERE (l.item_master_last_run_date IS NULL OR '||
-- 'l.internal_item_last_run_date IS NULL OR '||
-- Bug # 3529303
'mi.last_update_date > LEAST(nvl(l.item_master_last_run_date, '||
'sysdate), nvl(l.internal_item_last_run_date, sysdate)) OR '||
'EXISTS (SELECT ''updated description'' ';
'AND mitl.last_update_date > GREATEST(l.item_master_last_run_date, '||
'l.internal_item_last_run_date)) OR '||
'mic.last_update_date > GREATEST(l.item_master_last_run_date, '||
'l.internal_item_last_run_date) OR '||
'mc.last_update_date > GREATEST(l.item_master_last_run_date, '||
'l.internal_item_last_run_date)) '||
'AND mi.inventory_item_id = mic.inventory_item_id '||
'AND mic.organization_id = mi.organization_id '||
'AND mic.category_id = mc.category_id '||
'AND mic.category_set_id = '||gCategorySetId||' '||
'AND mc.web_status = ''Y'' '||
'AND NOT (mi.replenish_to_order_flag = ''Y'' AND '||
'mi.base_item_id IS NOT NULL AND '||
'mi.auto_created_config_flag = ''Y'') '||
'AND mi.unit_of_issue = muom.unit_of_measure(+) '||
'AND muom.language(+) = '''||ICX_POR_EXTRACTOR.gBaseLang||''' '||
'AND mi.organization_id = fsp.inventory_organization_id '||
'AND fsp.set_of_books_id = gsb.set_of_books_id ';
pSqlString := xSelectStr||
'FROM ('||xViewStr||xViewStr2||') doc, '||
xFromStr||xWhereStr;
xSelectStr :=
'SELECT /*+ LEADING(doc) */ '||
GLOBAL_AGREEMENT_TYPE||' document_type, '||
'doc.last_update_date, '||
'doc.org_id, '||
'doc.supplier_id, '||
'doc.supplier, '||
'doc.supplier_site_code, '||
'doc.supplier_part_num, '||
'doc.internal_item_id, '||
'doc.internal_item_num, '||
'doc.inventory_organization_id, '||
'TO_CHAR(NULL) item_source_type, '||
'TO_CHAR(NULL) item_search_type, '||
'doc.mtl_category_id, '||
'TO_CHAR(NULL) category_key, '||
'TO_CHAR(NULL) description, '||
'TO_CHAR(NULL) picture, '||
'TO_CHAR(NULL) picture_url, '||
'''GLOBAL_AGREEMENT'' price_type, '||
'TO_NUMBER(-2) asl_id, '||
'doc.supplier_site_id, '||
'doc.contract_id, '||
'doc.contract_line_id, '||
'TO_CHAR(-2) template_id, '||
'TO_NUMBER(-2) template_line_id, '||
'''SUPPLIER'' price_search_type, '||
'doc.unit_price, '||
'doc.value_basis, '||
'doc.purchase_basis, '||
'doc.allow_price_override_flag, '||
'doc.not_to_exceed_price, '||
'TO_NUMBER(-2) suggested_quantity, '||
'doc.negotiated_by_preparer_flag, '||
'doc.currency, '||
'doc.unit_of_measure, '||
'doc.functional_price, '||
'doc.contract_num, '||
'doc.contract_line_num, '||
'TO_CHAR(NULL) manufacturer, '||
'TO_CHAR(NULL) manufacturer_part_num, '||
'doc.rate_type, '||
'doc.rate_date, '||
'doc.rate, '||
'TO_CHAR(NULL) supplier_number, '||
'TO_NUMBER(NULL) supplier_contact_id, '||
'TO_CHAR(NULL) item_revision, '||
'doc.line_type_id, '||
'TO_NUMBER(NULL) buyer_id, '||
'''N'' global_agreement_flag, '||
'doc.status, '||
'TO_NUMBER(NULL) primary_category_id, '||
'TO_CHAR(NULL) primary_category_name, '||
'TO_NUMBER(NULL) template_category_id, '||
'doc.price_rt_item_id, '||
'TO_NUMBER(NULL) price_internal_item_id, '||
'TO_NUMBER(NULL) price_supplier_id, '||
'TO_CHAR(NULL) price_supplier_part_num, '||
'TO_NUMBER(NULL) price_contract_line_id, '||
'TO_NUMBER(NULL) price_mtl_category_id, '||
'TO_NUMBER(NULL) match_primary_category_id, '||
'TO_NUMBER(NULL) rt_item_id, '||
'TO_NUMBER(NULL) local_rt_item_id, '||
'''N'' match_template_flag, '||
'p.active_flag active_flag, '||
'ROWIDTOCHAR(p.rowid) price_rowid ';
'SELECT /*+ LEADING(t) */ '||
't.po_header_id as PoHeaderId, '||
't.organization_id as OrganizationId, '||
'ip.contract_line_id as ContractLineId, '||
't.last_update_date, '||
'NVL(t.organization_id, '||NULL_NUMBER||') org_id, '||
'NVL(i.supplier_id, '||NULL_NUMBER||') supplier_id, '||
'NVL(i.supplier, TO_CHAR('||NULL_NUMBER||')) supplier, '||
'pvs.vendor_site_code supplier_site_code, '||
'NVL(i.supplier_part_num, TO_CHAR('||NULL_NUMBER||
')) supplier_part_num, '||
'NVL(i.internal_item_id, '||NULL_NUMBER||') internal_item_id, '||
'i.internal_item_num, '||
'mi.organization_id inventory_organization_id, '||
'ip.mtl_category_id, '||
't.vendor_site_id supplier_site_id, '||
't.po_header_id contract_id, '||
'ip.contract_line_id, '||
'ip.unit_price, '||
--FPJ FPSL Extractor Changes
'ip.value_basis, '||
'ip.purchase_basis, '||
'ip.allow_price_override_flag, '||
'ip.not_to_exceed_price, '||
-- FPJ Bug# 3110297 jingyu Add negotiated flag
'ip.negotiated_by_preparer_flag negotiated_by_preparer_flag, '||
'ip.currency, '||
'ip.unit_of_measure, '||
'ICX_CAT_UTIL_PKG.convert_amount_sql(ip.currency, '||
'gsb.currency_code, SYSDATE, icx_psp.default_rate_type, '||
'ip.unit_price) functional_price, '||
'ip.contract_num, '||
'ip.contract_line_num, '||
/* Retrieve and use the Default Rate Type form the Purchasing Options of the
* Enabled Org for calculation of Rate and Functional Price.
*/
'icx_psp.default_rate_type rate_type, '||
'sysdate rate_date, '||
'ICX_POR_EXT_ITEM.getRate(fsp.set_of_books_id, '||
'ip.currency, '||
'sysdate, '||
'icx_psp.default_rate_type, ' ||
't.purchasing_org_id, '||
'ip.org_id, '||
'ip.contract_num ) rate, '||
-- bug 2912717: populate line_type, rate info. for GA
'ip.line_type_id line_type_id, '||
'ICX_POR_EXT_DIAG.getGlobalAgreementStatus(t.enabled_flag, '||
'pvs.purchasing_site_flag, '||
'pvs.inactive_date, '||
'mi.purchasing_enabled_flag, '||
'mi.outside_operation_flag, '||
'mi.primary_uom_code, '||
'mi2.purchasing_enabled_flag, '||
'mi2.outside_operation_flag, '||
'ip.unit_of_measure, '||
'mi2.primary_uom_code, '''||
ICX_POR_EXT_TEST.gTestMode||''') status, '||
'ip.rt_item_id price_rt_item_id ';
'GREATEST(NVL(mi.last_update_date, l.contracts_last_run_date-1), '||
'ip.creation_date, t.last_update_date) > l.contracts_last_run_date) '||
'AND t.vendor_site_id = pvs.vendor_site_id (+) '||
'AND icx_psp.org_id = t.organization_id '||
'AND ip.contract_id = t.po_header_id '||
'AND ip.price_type = ''BLANKET'' '||
'AND t.organization_id <> ip.org_id '||
'AND t.purchasing_org_id = fsp2.org_id '|| -- Centralized Proc Impacts Enhancement
'AND i.rt_item_id = ip.rt_item_id '||
'AND t.organization_id = fsp.org_id '||
'AND fsp.set_of_books_id = gsb.set_of_books_id '||
'AND ip.inventory_item_id = mi.inventory_item_id (+) '||
'AND ip.inventory_item_id = mi2.inventory_item_id (+) '|| -- Centralized Proc Impacts
'AND fsp.inventory_organization_id = NVL(mi.organization_id, '||
'fsp.inventory_organization_id) '||
'AND fsp2.inventory_organization_id = NVL(mi2.organization_id, '|| -- Centralized Proc Impacts
'fsp2.inventory_organization_id) ';
'SELECT /*+ LEADING(g) */ '||GLOBAL_AGREEMENT_TYPE||' document_type, '||
'p.last_update_date, '||
'NVL(p.org_id, '||NULL_NUMBER||') org_id, '||
'NVL(i.supplier_id, '||NULL_NUMBER||') supplier_id, '||
'NVL(i.supplier, TO_CHAR('||NULL_NUMBER||')) supplier, '||
'TO_CHAR(NULL) supplier_site_code, '||
'NVL(i.supplier_part_num, TO_CHAR('||NULL_NUMBER||
')) supplier_part_num, '||
'NVL(i.internal_item_id, '||NULL_NUMBER||') internal_item_id, '||
'i.internal_item_num, '||
'TO_NUMBER(NULL) inventory_organization_id, '||
'TO_CHAR(NULL) item_source_type, '||
'TO_CHAR(NULL) item_search_type, '||
'TO_NUMBER(NULL) mtl_category_id, '||
'TO_CHAR(NULL) category_key, '||
'TO_CHAR(NULL) description, '||
'TO_CHAR(NULL) picture, '||
'TO_CHAR(NULL) picture_url, '||
'''SET_ACTIVE_FLAG'' price_type, '||
'TO_NUMBER(NULL) asl_id, '||
'TO_NUMBER(NULL) supplier_site_id, '||
'TO_NUMBER(NULL) contract_id, '||
'p.contract_line_id, '||
'TO_CHAR(NULL) template_id, '||
'TO_NUMBER(NULL) template_line_id, '||
'TO_CHAR(NULL) price_search_type, '||
'TO_NUMBER(NULL) unit_price, '||
--FPJ FPSL Extractor Changes
'TO_CHAR(NULL) value_basis, '||
'TO_CHAR(NULL) purchase_basis, '||
'TO_CHAR(NULL) allow_price_override_flag, '||
'TO_NUMBER(NULL) not_to_exceed_price, '||
-- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
'TO_NUMBER(NULL) suggested_quantity, '||
-- FPJ Bug# 3110297 jingyu Add negotiated flag
'TO_CHAR(NULL) negotiated_by_preparer_flag, '||
'TO_CHAR(NULL) currency, '||
'TO_CHAR(NULL) unit_of_measure, '||
'TO_NUMBER(NULL) functional_price, '||
'TO_CHAR(NULL) contract_num, '||
'TO_NUMBER(NULL) contract_line_num, '||
'TO_CHAR(NULL) manufacturer, '||
'TO_CHAR(NULL) manufacturer_part_num, '||
'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, '||
'TO_CHAR(NULL) global_agreement_flag, '||
ICX_POR_EXT_DIAG.VALID_FOR_EXTRACT||' status, '||
'TO_NUMBER(NULL) primary_category_id, '||
'TO_CHAR(NULL) primary_category_name, '||
'TO_NUMBER(NULL) template_category_id, '||
'TO_NUMBER(NULL) price_rt_item_id, '||
'TO_NUMBER(NULL) price_internal_item_id, '||
'TO_NUMBER(NULL) price_supplier_id, '||
'TO_CHAR(NULL) price_supplier_part_num, '||
'TO_NUMBER(NULL) price_contract_line_id, '||
'TO_NUMBER(NULL) price_mtl_category_id, '||
'TO_NUMBER(NULL) match_primary_category_id, '||
'TO_NUMBER(NULL) rt_item_id, '||
'p.local_rt_item_id, '||
'TO_CHAR(NULL) match_template_flag, '||
'TO_CHAR(NULL) active_flag, '||
'ROWIDTOCHAR(p.rowid) price_rowid '||
'FROM icx_cat_item_prices p, '||
'icx_cat_extract_ga_gt g, '||
'icx_cat_items_b i '||
'WHERE p.contract_id = g.contract_id '||
'AND p.contract_line_id = g.contract_line_id '||
'AND p.price_type = ''GLOBAL_AGREEMENT'' '||
'AND i.rt_item_id = p.rt_item_id ';
xSelectStr ||
'FROM ( '|| xViewStr || xViewStr2 || ') doc, '||
xFromStr || xWhereStr ||
' UNION ALL ' || xString;
cUpdatedVendorNames tCursorType;
cUpdatedVendorSites tCursorType;
'SELECT /*+ LEADING(v) */ v.vendor_id, '||
'v.vendor_name ';
'v.last_update_date > l.vendor_last_run_date) ' ||
'AND NOT EXISTS (SELECT ''updated vendor name'' ' ||
'FROM icx_cat_items_b i ' ||
'WHERE i.supplier_id = v.vendor_id ' ||
'AND i.supplier = v.vendor_name)';
'Query for updated vendor names: ' || xString);
OPEN cUpdatedVendorNames FOR xString;
/* Changing the fetch for cUpdatedVendorNames to bulk fetch into plsql tables
and moving the fetch outside of the loop for the following reasons:
1. Dont expect a huge number of vendor_names that needs changes in catalog items table
Even if there are huge number of vendor_names that will be returned by the cursor cUpdatedVendorNames
doing a bulk fetch will only increase the size of plsql tables
2. We can take the advantage of doing bulk fetch for cUpdatedVendorNames
3. While updating icx_cat_items_tlp we can utilize forall instead of processing one vendor at a time
anyway we will be processing only 2500 rows at a time, since we have the rownum constraint.
*/
xErrLoc := 160;
FETCH cUpdatedVendorNames INTO xVendorId(xIndex), xVendorName(xIndex);
EXIT WHEN cUpdatedVendorNames%NOTFOUND;
FETCH cUpdatedVendorNames BULK COLLECT
INTO xVendorId, xVendorName;
xRtItemIds.DELETE;
xNewVendorNames.DELETE;
UPDATE icx_cat_items_b
SET supplier = xVendorName(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
request_id = ICX_POR_EXTRACTOR.gRequestId,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE supplier_id = xVendorId(i)
AND supplier <> xVendorName(i)
AND rownum <= xCommitSize
RETURNING RT_ITEM_ID, SUPPLIER BULK COLLECT INTO xRtItemIds, xNewVendorNames;
UPDATE icx_cat_items_tlp
SET supplier = xNewVendorNames(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
request_id = ICX_POR_EXTRACTOR.gRequestId,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE rt_item_id = xRtItemIds(i);
IF (cUpdatedVendorNames%ISOPEN) THEN
xErrLoc := 410;
CLOSE cUpdatedVendorNames;
'SELECT /*+ LEADING(vs) */ ' ||
' vs.vendor_site_id, ' ||
' vs.vendor_site_code ';
' vs.last_update_date > l.vendor_last_run_date) ' ||
' AND EXISTS ( SELECT ''Updated VendorSite In Catalog'' ' ||
' FROM icx_cat_item_prices ip ' ||
' WHERE ip.supplier_site_id = vs.vendor_site_id )';
'Query for updated vendor site names: ' || xString);
OPEN cUpdatedVendorSites FOR xString;
FETCH cUpdatedVendorSites INTO xVendorSiteIds(xIndex), xVendorSiteNames(xIndex);
EXIT WHEN cUpdatedVendorSites%NOTFOUND;
FETCH cUpdatedVendorSites BULK COLLECT
INTO xVendorSiteIds, xVendorSiteNames;
UPDATE icx_cat_item_prices
SET supplier_site_code = xVendorSiteNames(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
request_id = ICX_POR_EXTRACTOR.gRequestId,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE supplier_site_id = xVendorSiteIds(i)
AND supplier_site_code <> xVendorSiteNames(i)
AND rownum <= xCommitSize;
IF (cUpdatedVendorSites%ISOPEN) THEN
xErrLoc := 680;
CLOSE cUpdatedVendorSites;
IF (cUpdatedVendorNames%ISOPEN) THEN
CLOSE cUpdatedVendorNames;
SELECT i.rt_item_id
FROM icx_cat_extract_gt i
WHERE i.type = 'CLEANUP_ITEM'
AND NOT EXISTS (SELECT 'price rows'
FROM icx_cat_item_prices p
WHERE p.rt_item_id = i.rt_item_id);
xActionMode := 'DELETE_ITEM_NOPRICE';
ICX_POR_DELETE_CATALOG.setCommitSize(ICX_POR_EXT_UTL.gCommitSize);
ICX_POR_DELETE_CATALOG.deleteCommonTables(gDIRtItemIds,
ICX_POR_DELETE_CATALOG.ITEM_TABLE_LAST);
'Total deleted items without price : ' || xRowCount);
SELECT p.price_type,
p.rt_item_id,
p.rowid price_rowid,
ICX_POR_EXT_ITEM.getActiveFlag(p.price_type, p.rowid) active_flag
FROM icx_cat_item_prices p,
icx_cat_extract_gt i
WHERE i.type = 'ACTIVE_FLAG'
AND p.rt_item_id = i.rt_item_id
AND p.price_type <> 'GLOBAL_AGREEMENT'
AND nvl(p.request_id, ICX_POR_EXT_ITEM.AF_NEW_PRICE_TEMP_REQUEST_ID) <>
ICX_POR_EXT_ITEM.AF_CURRENT_REQUEST_ID; -- Bug # 3542291
UPDATE icx_cat_item_prices
SET active_flag = gSAActiveFlags(i),
last_updated_by = ICX_POR_EXTRACTOR.gUserId,
last_update_date = SYSDATE,
last_update_login = ICX_POR_EXTRACTOR.gLoginId,
-- Bug # 3542291
request_id = ICX_POR_EXT_ITEM.AF_CURRENT_REQUEST_ID,
program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
program_id = ICX_POR_EXTRACTOR.gProgramId,
program_update_date = SYSDATE
WHERE rowid = gSARowIds(i);
xActionMode := 'DELETE_ITEM_PRICE';
'SELECT p.rowid, '||
'p.rt_item_id, '||
'ic.rt_category_id template_category_id, '||
'p.inventory_item_id, '||
'p.org_id, '||
'p.local_rt_item_id '||
'FROM icx_cat_item_prices p, '||
'icx_cat_categories_tl ic '||
'WHERE p.price_type IN (''TEMPLATE'', ''INTERNAL_TEMPLATE'', ' ||
' ''BLANKET'', ''QUOTATION'', ''GLOBAL_AGREEMENT'', ''ASL'', ' ||
' ''PURCHASING_ITEM'', ''INTERNAL_ITEM'') ' ||
'AND ICX_POR_EXT_DIAG.getPriceStatus(p.price_type, p.rowid, '''||
ICX_POR_EXT_TEST.gTestMode||''') <> '||
ICX_POR_EXT_DIAG.VALID_FOR_EXTRACT||' '||
'AND p.template_id||''_tmpl'' = ic.key (+) '||
'AND ic.type (+) = '||ICX_POR_EXT_CLASS.TEMPLATE_HEADER_TYPE||' '||
'AND ic.language (+) = '''||ICX_POR_EXTRACTOR.gBaseLang||''' ';
'AND p.last_updated_by = '||ICX_POR_EXT_TEST.TEST_USER_ID||' ';
DELETE icx_cat_item_prices
WHERE rowid = gDPRowIds(i);
DELETE icx_cat_category_items
WHERE rt_category_id = gDPTemplateCategoryIds(i)
AND rt_item_id = gDPRtItemIds(i);
INSERT INTO icx_cat_extract_gt
(rt_item_id, type)
VALUES (gDPRtItemIds(i), 'CLEANUP_ITEM');
INSERT INTO icx_cat_extract_gt
(rt_item_id, type)
VALUES (gDPRtItemIds(i), 'ACTIVE_FLAG');
INSERT INTO icx_cat_extract_gt
(rt_item_id, type)
SELECT rt_item_id, 'ACTIVE_FLAG'
FROM icx_cat_items_b
WHERE internal_item_id = gDPInventoryItemIds(i)
AND org_id = NVL(gDPOrgIds(i), org_id)
AND supplier IS NULL;
INSERT INTO icx_cat_extract_gt
(rt_item_id, type)
SELECT gDPLocalRtItemIds(i), 'ACTIVE_FLAG'
FROM dual
WHERE gDPLocalRtItemIds(i) IS NOT NULL;
'Total deleted invalid price rows : ' || xRowCount);
select category_set_id,
validate_flag,
structure_id
into gCategorySetId,
gValidateFlag,
gStructureId
from mtl_default_sets_view
where functional_area_id = 2;
select nvl(multi_org_flag, 'N')
into gMultiOrgFlag
from fnd_product_groups;
'All updated price rows processing done: ' || gTotalCount);
updatePriceRequestIds;
updatePriceRequestIds;
PROCEDURE updatePriceRequestIds IS
xErrLoc PLS_INTEGER := 100;
UPDATE ICX_CAT_ITEM_PRICES
SET REQUEST_ID = ICX_POR_EXTRACTOR.gRequestId
WHERE REQUEST_ID IN (
TEMPLATE_TEMP_REQUEST_ID,
CONTRACT_TEMP_REQUEST_ID,
GA_TEMP_REQUEST_ID,
ASL_TEMP_REQUEST_ID,
ITEM_TEMP_REQUEST_ID,
-- Bug # 3542291
AF_TEMPLATE_TEMP_REQUEST_ID,
AF_CONTRACT_TEMP_REQUEST_ID,
AF_GA_TEMP_REQUEST_ID,
AF_ASL_TEMP_REQUEST_ID,
AF_ITEM_TEMP_REQUEST_ID,
AF_CLEANUP_TEMP_REQUEST_ID);
ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_ITEM.updatePriceRequestIds -'||
xErrLoc||' '||SQLERRM);
END updatePriceRequestIds;