[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
check_desc_update VARCHAR2(25),
rt_item_id NUMBER
);
gIAVTCheckDescUpdateTbl DBMS_SQL.VARCHAR2_TABLE;
IF (p_action_mode IN ('ALL', 'INSERT_INT_HDRS')) THEN
--INSERT po_headers_interface
gIHInterfaceHeaderIdTbl.DELETE;
gIHActionTbl.DELETE;
gIHOrgIdTbl.DELETE;
gIHDocumentTypeCodeTbl.DELETE;
gIHBudgetAccountSegment1Tbl.DELETE;
gIHPoHeaderIdTbl.DELETE;
gIHApprovalStatusTbl.DELETE;
gIHVendorIdTbl.DELETE;
gIHVendorSiteIdTbl.DELETE;
gIHCurrencyCodeTbl.DELETE;
gIHCpaReferenceTbl.DELETE;
gIHCreatedLanguageTbl.DELETE;
gIHCommentsTbl.DELETE;
IF (p_action_mode IN ('ALL', 'INSERT_INT_LINES')) THEN
--INSERT po_lines_interface
gILInterfaceLineIdTbl.DELETE;
gILInterfaceHeaderIdTbl.DELETE;
gILActionTbl.DELETE;
gILPoLineIdTbl.DELETE;
gILPoHeaderIdTbl.DELETE;
gILUnitPriceTbl.DELETE;
gILUomCodeTbl.DELETE;
gILNegByPrepFlagTbl.DELETE;
gILIpCategoryIdTbl.DELETE;
gILCategoryIdTbl.DELETE;
gILCategoryNameTbl.DELETE;
gILVendorProductNumTbl.DELETE;
gILSupplierPartAuxidTbl.DELETE;
gILItemDescripionTbl.DELETE;
gILCatalogNameTbl.DELETE;
IF (p_action_mode IN ('ALL', 'INSERT_INT_ATTR_VALS')) THEN
--INSERT po_attr_values_interface
gIAVInterfaceHeaderIdTbl.DELETE;
gIAVInterfaceLineIdTbl.DELETE;
gIAVActionTbl.DELETE;
gIAVPoLineIdTbl.DELETE;
gIAVReqTemplateNameTbl.DELETE;
gIAVReqTemplateLineNumTbl.DELETE;
gIAVInventoryItemIdTbl.DELETE;
gIAVOrgIdTbl.DELETE;
gIAVRtItemIdTbl.DELETE;
gIAVLanguageTbl.DELETE;
IF (p_action_mode IN ('ALL', 'INSERT_INT_ATTR_VALS_TLP')) THEN
--INSERT po_attr_values_tlp_interface
gIAVTInterfaceHeaderIdTbl.DELETE;
gIAVTInterfaceLineIdTbl.DELETE;
gIAVTActionTbl.DELETE;
gIAVTPoLineIdTbl.DELETE;
gIAVTReqTemplateNameTbl.DELETE;
gIAVTReqTemplateLineNumTbl.DELETE;
gIAVTInventoryItemIdTbl.DELETE;
gIAVTOrgIdTbl.DELETE;
gIAVTRtItemIdTbl.DELETE;
gIAVTLanguageTbl.DELETE;
gIAVTCheckDescUpdateTbl.DELETE;
IF (p_action_mode IN ('ALL', 'INSERT_R12_UPGRADE')) THEN
--INSERT icx_cat_r12_upgrade
gIRURtItemIdTbl.DELETE;
gIRUSupplierSiteIdTbl.DELETE;
gIRUCurrencyTbl.DELETE;
gIRUCpaReferenceTbl.DELETE;
gIRUPriceContractIdTbl.DELETE;
gIRUSrcContractIdTbl.DELETE;
gIRUPoCategoryIdTbl.DELETE;
gIRUPoInterfaceHeaderIdTbl.DELETE;
gIRUPoInterfaceLineIdTbl.DELETE;
gIRUCreatedLanguageTbl.DELETE;
IF (p_action_mode IN ('ALL', 'UPDATE_R12_UPGRADE')) THEN
--UPDATE icx_cat_r12_upgrade
gURURtItemIdTbl.DELETE;
gURUPoCategoryIdTbl.DELETE;
gURUCpaReferenceTbl.DELETE;
gURUPriceContractIdTbl.DELETE;
gURUSrcContractIdTbl.DELETE;
gURUOldPoInterfaceLineIdTbl.DELETE;
gURUPoInterfaceHeaderIdTbl.DELETE;
gURUPoInterfaceLineIdTbl.DELETE;
gURUPoHeaderIdTbl.DELETE;
gURUPoLineIdTbl.DELETE;
gURUCreatedLanguageTbl.DELETE;
IF (p_action_mode IN ('ALL', 'DELETE_R12_UPGRADE')) THEN
--DELETE icx_cat_r12_upgrade
gDRURtItemIdTbl.DELETE;
gDRUPoInterfaceHeaderIdTbl.DELETE;
gDRUPoInterfaceLineIdTbl.DELETE;
IF (p_action_mode IN ('ALL', 'INSERT_FAV_LINES_CATALOG')) THEN
--INSERT icx_cat_fav_list_lines_tlp for catalog items
gIFLCFavoriteListIdTbl.DELETE;
gIFLCNewFavoriteListLineIdTbl.DELETE;
gIFLCOldFavoriteListLineIdTbl.DELETE;
gIFLCRtItemIdTbl.DELETE;
gIFLCSourceTypeTbl.DELETE;
gIFLCOrgIdTbl.DELETE;
gIFLCLanguageTbl.DELETE;
IF (p_action_mode IN ('ALL', 'INSERT_FAV_LINES_OTHER')) THEN
--INSERT icx_cat_fav_list_lines_tlp for other items
gIFLOFavoriteListIdTbl.DELETE;
gIFLONewFavoriteListLineIdTbl.DELETE;
gIFLOOldFavoriteListLineIdTbl.DELETE;
gIFLOOrgIdTbl.DELETE;
gIFLOLanguageTbl.DELETE;
IF (p_action_mode = 'INSERT_INT_HDRS') THEN
--INSERT po_headers_interface
l_string := l_string || ' gIHInterfaceHeaderIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gIHInterfaceHeaderIdTbl, p_index) || ', ';
IF (p_action_mode = 'INSERT_INT_LINES') THEN
--INSERT po_lines_interface
l_string := l_string || ' gILInterfaceLineIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gILInterfaceLineIdTbl, p_index) || ', ';
IF (p_action_mode = 'INSERT_INT_ATTR_VALS') THEN
--INSERT po_attr_values_interface
l_string := l_string || ' gIAVInterfaceHeaderIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gIAVInterfaceHeaderIdTbl, p_index) || ', ';
IF (p_action_mode = 'INSERT_INT_ATTR_VALS_TLP') THEN
--INSERT po_attr_values_tlp_interface
l_string := l_string || ' gIAVTInterfaceHeaderIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gIAVTInterfaceHeaderIdTbl, p_index) || ', ';
l_string := l_string || ' gIAVTCheckDescUpdateTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gIAVTCheckDescUpdateTbl, p_index) || ', ';
IF (p_action_mode = 'INSERT_R12_UPGRADE') THEN
--INSERT icx_cat_r12_upgrade
l_string := l_string || ' gIRURtItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gIRURtItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'UPDATE_R12_UPGRADE') THEN
--UPDATE icx_cat_r12_upgrade
l_string := l_string || ' gURURtItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gURURtItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'DELETE_R12_UPGRADE') THEN
--DELETE icx_cat_r12_upgrade
l_string := l_string || ' gDRURtItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gDRURtItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'INSERT_FAV_LINES_CATALOG') THEN
--INSERT icx_cat_fav_list_lines_tlp for catalog items
l_string := l_string || ' gIFLCFavoriteListIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gIFLCFavoriteListIdTbl, p_index) || ', ';
IF (p_action_mode = 'INSERT_FAV_LINES_OTHER') THEN
--INSERT icx_cat_fav_list_lines_tlp for other items
l_string := l_string || ' gIFLOFavoriteListIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gIFLOFavoriteListIdTbl, p_index) || ', ';
IF (p_action_mode = 'INSERT_INT_HDRS') THEN
l_err_loc := 500;
IF (p_action_mode = 'INSERT_INT_LINES') THEN
l_err_loc := 700;
IF (p_action_mode = 'INSERT_INT_ATTR_VALS') THEN
l_err_loc := 900;
IF (p_action_mode = 'INSERT_INT_ATTR_VALS_TLP') THEN
l_err_loc := 1100;
l_log_string := ' gIAVTCheckDescUpdateTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gIAVTCheckDescUpdateTbl, p_index) || '; ';
IF (p_action_mode = 'INSERT_R12_UPGRADE') THEN
l_err_loc := 1300;
IF (p_action_mode = 'UPDATE_R12_UPGRADE') THEN
l_err_loc := 1500;
IF (p_action_mode = 'DELETE_R12_UPGRADE') THEN
l_err_loc := 1700;
IF (p_action_mode = 'INSERT_FAV_LINES_CATALOG') THEN
l_err_loc := 1900;
IF (p_action_mode = 'INSERT_FAV_LINES_OTHER') THEN
l_err_loc := 2100;
SELECT po_headers_interface_s.NEXTVAL
INTO l_interface_header_id
FROM DUAL;
SELECT po_lines_interface_s.NEXTVAL
INTO l_interface_line_id
FROM DUAL;
SELECT por_favorite_list_lines_s.NEXTVAL
INTO l_favorite_list_line_id
FROM DUAL;
SELECT 'UPDATE'
INTO l_action
FROM po_attribute_values_tlp
WHERE po_line_id = p_po_line_id
AND req_template_name = p_req_template_name
AND req_template_line_num = p_req_template_line_num
AND org_id = p_org_id
AND language = p_language;
PROCEDURE insertPOHeadersInterface
IS
l_api_name CONSTANT VARCHAR2(30) := 'insertPOHeadersInterface';
END insertPOHeadersInterface;
PROCEDURE insertPOLinesInterface
IS
l_api_name CONSTANT VARCHAR2(30) := 'insertPOLinesInterface';
END insertPOLinesInterface;
PROCEDURE insertPOAttrValsInterface
IS
l_api_name CONSTANT VARCHAR2(30) := 'insertPOAttrValsInterface';
END insertPOAttrValsInterface;
PROCEDURE insertPOAttrValsTLPInterface
IS
l_api_name CONSTANT VARCHAR2(30) := 'insertPOAttrValsTLPInterface';
gIAVTCheckDescUpdateTbl(l_index) := g_po_attrvalstlp_int_rec.check_desc_update;
END insertPOAttrValsTLPInterface;
PROCEDURE insertR12Upgrade
IS
l_api_name CONSTANT VARCHAR2(30) := 'insertR12Upgrade';
END insertR12Upgrade;
PROCEDURE updateR12Upgrade
IS
l_api_name CONSTANT VARCHAR2(30) := 'updateR12Upgrade';
END updateR12Upgrade;
PROCEDURE deleteR12Upgrade
IS
l_api_name CONSTANT VARCHAR2(30) := 'deleteR12Upgrade';
END deleteR12Upgrade;
l_action_mode := 'INSERT_INT_HDRS';
INSERT INTO po_headers_interface
(interface_header_id, batch_id, interface_source_code, process_code,
action, org_id, document_type_code, budget_account_segment1, po_header_id,
approval_status, vendor_id, vendor_site_id, currency_code,
cpa_reference, created_language, comments, vendor_name, vendor_site_code)
SELECT gIHInterfaceHeaderIdTbl(i), g_PDOI_batch_id, g_interface_source_code, g_process_code,
gIHActionTbl(i), gIHOrgIdTbl(i), gIHDocumentTypeCodeTbl(i), gIHBudgetAccountSegment1Tbl(i), gIHPoHeaderIdTbl(i),
gIHApprovalStatusTbl(i), doc.vendor_id, doc.vendor_site_id, gIHCurrencyCodeTbl(i),
gIHCpaReferenceTbl(i), gIHCreatedLanguageTbl(i), gIHCommentsTbl(i),
supp.vendor_name, site.vendor_site_code
FROM (
SELECT gIHVendorIdTbl(i) vendor_id, gIHVendorSiteIdTbl(i) vendor_site_id
FROM DUAL
) doc,
po_vendors supp, po_vendor_sites_all site
WHERE supp.vendor_id (+) = doc.vendor_id
AND site.vendor_site_id (+) = doc.vendor_site_id;
'No: of rows inserted into po_headers_interface:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_INT_LINES';
INSERT INTO po_lines_interface
(interface_line_id, interface_header_id, process_code, action,
po_line_id, po_header_id, unit_price,
uom_code, negotiated_by_preparer_flag, ip_category_id, category_id, category,
vendor_product_num, supplier_part_auxid, item_description, catalog_name)
VALUES(gILInterfaceLineIdTbl(i), gILInterfaceHeaderIdTbl(i), g_process_code, gILActionTbl(i),
gILPoLineIdTbl(i), gILPoHeaderIdTbl(i), gILUnitPriceTbl(i),
gILUomCodeTbl(i), gILNegByPrepFlagTbl(i), gILIpCategoryIdTbl(i), gILCategoryIdTbl(i), gILCategoryNameTbl(i),
gILVendorProductNumTbl(i), gILSupplierPartAuxidTbl(i), gILItemDescripionTbl(i), gILCatalogNameTbl(i));
'No: of rows inserted into po_lines_interface:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_INT_ATTR_VALS';
INSERT INTO po_attr_values_interface
(interface_attr_values_id, interface_header_id,
interface_line_id, process_code, action,
po_line_id, req_template_name, req_template_line_num,
inventory_item_id, org_id,
ip_category_id, manufacturer_part_num, thumbnail_image,
supplier_url,manufacturer_url, attachment_url,
unspsc, availability, lead_time, picture,
text_base_attribute1, text_base_attribute2, text_base_attribute3,
text_base_attribute4, text_base_attribute5, text_base_attribute6,
text_base_attribute7, text_base_attribute8, text_base_attribute9,
text_base_attribute10, text_base_attribute11, text_base_attribute12,
text_base_attribute13, text_base_attribute14, text_base_attribute15,
text_base_attribute16, text_base_attribute17, text_base_attribute18,
text_base_attribute19, text_base_attribute20, text_base_attribute21,
text_base_attribute22, text_base_attribute23, text_base_attribute24,
text_base_attribute25, text_base_attribute26, text_base_attribute27,
text_base_attribute28, text_base_attribute29, text_base_attribute30,
text_base_attribute31, text_base_attribute32, text_base_attribute33,
text_base_attribute34, text_base_attribute35, text_base_attribute36,
text_base_attribute37, text_base_attribute38, text_base_attribute39,
text_base_attribute40, text_base_attribute41, text_base_attribute42,
text_base_attribute43, text_base_attribute44, text_base_attribute45,
text_base_attribute46, text_base_attribute47, text_base_attribute48,
text_base_attribute49, text_base_attribute50, text_base_attribute51,
text_base_attribute52, text_base_attribute53, text_base_attribute54,
text_base_attribute55, text_base_attribute56, text_base_attribute57,
text_base_attribute58, text_base_attribute59, text_base_attribute60,
text_base_attribute61, text_base_attribute62, text_base_attribute63,
text_base_attribute64, text_base_attribute65, text_base_attribute66,
text_base_attribute67, text_base_attribute68, text_base_attribute69,
text_base_attribute70, text_base_attribute71, text_base_attribute72,
text_base_attribute73, text_base_attribute74, text_base_attribute75,
text_base_attribute76, text_base_attribute77, text_base_attribute78,
text_base_attribute79, text_base_attribute80, text_base_attribute81,
text_base_attribute82, text_base_attribute83, text_base_attribute84,
text_base_attribute85, text_base_attribute86, text_base_attribute87,
text_base_attribute88, text_base_attribute89, text_base_attribute90,
text_base_attribute91, text_base_attribute92, text_base_attribute93,
text_base_attribute94, text_base_attribute95, text_base_attribute96,
text_base_attribute97, text_base_attribute98, text_base_attribute99,
text_base_attribute100, num_base_attribute1, num_base_attribute2,
num_base_attribute3, num_base_attribute4, num_base_attribute5,
num_base_attribute6, num_base_attribute7, num_base_attribute8,
num_base_attribute9, num_base_attribute10, num_base_attribute11,
num_base_attribute12, num_base_attribute13, num_base_attribute14,
num_base_attribute15, num_base_attribute16, num_base_attribute17,
num_base_attribute18, num_base_attribute19, num_base_attribute20,
num_base_attribute21, num_base_attribute22, num_base_attribute23,
num_base_attribute24, num_base_attribute25, num_base_attribute26,
num_base_attribute27, num_base_attribute28, num_base_attribute29,
num_base_attribute30, num_base_attribute31, num_base_attribute32,
num_base_attribute33, num_base_attribute34, num_base_attribute35,
num_base_attribute36, num_base_attribute37, num_base_attribute38,
num_base_attribute39, num_base_attribute40, num_base_attribute41,
num_base_attribute42, num_base_attribute43, num_base_attribute44,
num_base_attribute45, num_base_attribute46, num_base_attribute47,
num_base_attribute48, num_base_attribute49, num_base_attribute50,
num_base_attribute51, num_base_attribute52, num_base_attribute53,
num_base_attribute54, num_base_attribute55, num_base_attribute56,
num_base_attribute57, num_base_attribute58, num_base_attribute59,
num_base_attribute60, num_base_attribute61, num_base_attribute62,
num_base_attribute63, num_base_attribute64, num_base_attribute65,
num_base_attribute66, num_base_attribute67, num_base_attribute68,
num_base_attribute69, num_base_attribute70, num_base_attribute71,
num_base_attribute72, num_base_attribute73, num_base_attribute74,
num_base_attribute75, num_base_attribute76, num_base_attribute77,
num_base_attribute78, num_base_attribute79, num_base_attribute80,
num_base_attribute81, num_base_attribute82, num_base_attribute83,
num_base_attribute84, num_base_attribute85, num_base_attribute86,
num_base_attribute87, num_base_attribute88, num_base_attribute89,
num_base_attribute90, num_base_attribute91, num_base_attribute92,
num_base_attribute93, num_base_attribute94, num_base_attribute95,
num_base_attribute96, num_base_attribute97, num_base_attribute98,
num_base_attribute99, num_base_attribute100, text_cat_attribute1,
text_cat_attribute2, text_cat_attribute3, text_cat_attribute4,
text_cat_attribute5, text_cat_attribute6, text_cat_attribute7,
text_cat_attribute8, text_cat_attribute9, text_cat_attribute10,
text_cat_attribute11, text_cat_attribute12, text_cat_attribute13,
text_cat_attribute14, text_cat_attribute15, text_cat_attribute16,
text_cat_attribute17, text_cat_attribute18, text_cat_attribute19,
text_cat_attribute20, text_cat_attribute21, text_cat_attribute22,
text_cat_attribute23, text_cat_attribute24, text_cat_attribute25,
text_cat_attribute26, text_cat_attribute27, text_cat_attribute28,
text_cat_attribute29, text_cat_attribute30, text_cat_attribute31,
text_cat_attribute32, text_cat_attribute33, text_cat_attribute34,
text_cat_attribute35, text_cat_attribute36, text_cat_attribute37,
text_cat_attribute38, text_cat_attribute39, text_cat_attribute40,
text_cat_attribute41, text_cat_attribute42, text_cat_attribute43,
text_cat_attribute44, text_cat_attribute45, text_cat_attribute46,
text_cat_attribute47, text_cat_attribute48, text_cat_attribute49,
text_cat_attribute50, num_cat_attribute1, num_cat_attribute2,
num_cat_attribute3, num_cat_attribute4, num_cat_attribute5,
num_cat_attribute6, num_cat_attribute7, num_cat_attribute8,
num_cat_attribute9, num_cat_attribute10, num_cat_attribute11,
num_cat_attribute12, num_cat_attribute13, num_cat_attribute14,
num_cat_attribute15, num_cat_attribute16, num_cat_attribute17,
num_cat_attribute18, num_cat_attribute19, num_cat_attribute20,
num_cat_attribute21, num_cat_attribute22, num_cat_attribute23,
num_cat_attribute24, num_cat_attribute25, num_cat_attribute26,
num_cat_attribute27, num_cat_attribute28, num_cat_attribute29,
num_cat_attribute30, num_cat_attribute31, num_cat_attribute32,
num_cat_attribute33, num_cat_attribute34, num_cat_attribute35,
num_cat_attribute36, num_cat_attribute37, num_cat_attribute38,
num_cat_attribute39, num_cat_attribute40, num_cat_attribute41,
num_cat_attribute42, num_cat_attribute43, num_cat_attribute44,
num_cat_attribute45, num_cat_attribute46, num_cat_attribute47,
num_cat_attribute48, num_cat_attribute49, num_cat_attribute50)
SELECT po_attr_values_interface_s.NEXTVAL, gIAVInterfaceHeaderIdTbl(i),
gIAVInterfaceLineIdTbl(i), g_process_code, gIAVActionTbl(i),
gIAVPoLineIdTbl(i), gIAVReqTemplateNameTbl(i), gIAVReqTemplateLineNumTbl(i),
gIAVInventoryItemIdTbl(i), gIAVOrgIdTbl(i),
baseAtt.primary_category_id, baseAtt.manufacturer_part_num, baseAtt.thumbnail_image,
baseAtt.supplier_url, baseAtt.manufacturer_url, baseAtt.attachment_url,
baseAtt.unspsc_code, baseAtt.availability, baseAtt.lead_time, baseAtt.picture,
baseAtt.text_base_attribute1, baseAtt.text_base_attribute2, baseAtt.text_base_attribute3,
baseAtt.text_base_attribute4, baseAtt.text_base_attribute5, baseAtt.text_base_attribute6,
baseAtt.text_base_attribute7, baseAtt.text_base_attribute8, baseAtt.text_base_attribute9,
baseAtt.text_base_attribute10, baseAtt.text_base_attribute11, baseAtt.text_base_attribute12,
baseAtt.text_base_attribute13, baseAtt.text_base_attribute14, baseAtt.text_base_attribute15,
baseAtt.text_base_attribute16, baseAtt.text_base_attribute17, baseAtt.text_base_attribute18,
baseAtt.text_base_attribute19, baseAtt.text_base_attribute20, baseAtt.text_base_attribute21,
baseAtt.text_base_attribute22, baseAtt.text_base_attribute23, baseAtt.text_base_attribute24,
baseAtt.text_base_attribute25, baseAtt.text_base_attribute26, baseAtt.text_base_attribute27,
baseAtt.text_base_attribute28, baseAtt.text_base_attribute29, baseAtt.text_base_attribute30,
baseAtt.text_base_attribute31, baseAtt.text_base_attribute32, baseAtt.text_base_attribute33,
baseAtt.text_base_attribute34, baseAtt.text_base_attribute35, baseAtt.text_base_attribute36,
baseAtt.text_base_attribute37, baseAtt.text_base_attribute38, baseAtt.text_base_attribute39,
baseAtt.text_base_attribute40, baseAtt.text_base_attribute41, baseAtt.text_base_attribute42,
baseAtt.text_base_attribute43, baseAtt.text_base_attribute44, baseAtt.text_base_attribute45,
baseAtt.text_base_attribute46, baseAtt.text_base_attribute47, baseAtt.text_base_attribute48,
baseAtt.text_base_attribute49, baseAtt.text_base_attribute50, baseAtt.text_base_attribute51,
baseAtt.text_base_attribute52, baseAtt.text_base_attribute53, baseAtt.text_base_attribute54,
baseAtt.text_base_attribute55, baseAtt.text_base_attribute56, baseAtt.text_base_attribute57,
baseAtt.text_base_attribute58, baseAtt.text_base_attribute59, baseAtt.text_base_attribute60,
baseAtt.text_base_attribute61, baseAtt.text_base_attribute62, baseAtt.text_base_attribute63,
baseAtt.text_base_attribute64, baseAtt.text_base_attribute65, baseAtt.text_base_attribute66,
baseAtt.text_base_attribute67, baseAtt.text_base_attribute68, baseAtt.text_base_attribute69,
baseAtt.text_base_attribute70, baseAtt.text_base_attribute71, baseAtt.text_base_attribute72,
baseAtt.text_base_attribute73, baseAtt.text_base_attribute74, baseAtt.text_base_attribute75,
baseAtt.text_base_attribute76, baseAtt.text_base_attribute77, baseAtt.text_base_attribute78,
baseAtt.text_base_attribute79, baseAtt.text_base_attribute80, baseAtt.text_base_attribute81,
baseAtt.text_base_attribute82, baseAtt.text_base_attribute83, baseAtt.text_base_attribute84,
baseAtt.text_base_attribute85, baseAtt.text_base_attribute86, baseAtt.text_base_attribute87,
baseAtt.text_base_attribute88, baseAtt.text_base_attribute89, baseAtt.text_base_attribute90,
baseAtt.text_base_attribute91, baseAtt.text_base_attribute92, baseAtt.text_base_attribute93,
baseAtt.text_base_attribute94, baseAtt.text_base_attribute95, baseAtt.text_base_attribute96,
baseAtt.text_base_attribute97, baseAtt.text_base_attribute98, baseAtt.text_base_attribute99,
baseAtt.text_base_attribute100, baseAtt.num_base_attribute1, baseAtt.num_base_attribute2,
baseAtt.num_base_attribute3, baseAtt.num_base_attribute4, baseAtt.num_base_attribute5,
baseAtt.num_base_attribute6, baseAtt.num_base_attribute7, baseAtt.num_base_attribute8,
baseAtt.num_base_attribute9, baseAtt.num_base_attribute10, baseAtt.num_base_attribute11,
baseAtt.num_base_attribute12, baseAtt.num_base_attribute13, baseAtt.num_base_attribute14,
baseAtt.num_base_attribute15, baseAtt.num_base_attribute16, baseAtt.num_base_attribute17,
baseAtt.num_base_attribute18, baseAtt.num_base_attribute19, baseAtt.num_base_attribute20,
baseAtt.num_base_attribute21, baseAtt.num_base_attribute22, baseAtt.num_base_attribute23,
baseAtt.num_base_attribute24, baseAtt.num_base_attribute25, baseAtt.num_base_attribute26,
baseAtt.num_base_attribute27, baseAtt.num_base_attribute28, baseAtt.num_base_attribute29,
baseAtt.num_base_attribute30, baseAtt.num_base_attribute31, baseAtt.num_base_attribute32,
baseAtt.num_base_attribute33, baseAtt.num_base_attribute34, baseAtt.num_base_attribute35,
baseAtt.num_base_attribute36, baseAtt.num_base_attribute37, baseAtt.num_base_attribute38,
baseAtt.num_base_attribute39, baseAtt.num_base_attribute40, baseAtt.num_base_attribute41,
baseAtt.num_base_attribute42, baseAtt.num_base_attribute43, baseAtt.num_base_attribute44,
baseAtt.num_base_attribute45, baseAtt.num_base_attribute46, baseAtt.num_base_attribute47,
baseAtt.num_base_attribute48, baseAtt.num_base_attribute49, baseAtt.num_base_attribute50,
baseAtt.num_base_attribute51, baseAtt.num_base_attribute52, baseAtt.num_base_attribute53,
baseAtt.num_base_attribute54, baseAtt.num_base_attribute55, baseAtt.num_base_attribute56,
baseAtt.num_base_attribute57, baseAtt.num_base_attribute58, baseAtt.num_base_attribute59,
baseAtt.num_base_attribute60, baseAtt.num_base_attribute61, baseAtt.num_base_attribute62,
baseAtt.num_base_attribute63, baseAtt.num_base_attribute64, baseAtt.num_base_attribute65,
baseAtt.num_base_attribute66, baseAtt.num_base_attribute67, baseAtt.num_base_attribute68,
baseAtt.num_base_attribute69, baseAtt.num_base_attribute70, baseAtt.num_base_attribute71,
baseAtt.num_base_attribute72, baseAtt.num_base_attribute73, baseAtt.num_base_attribute74,
baseAtt.num_base_attribute75, baseAtt.num_base_attribute76, baseAtt.num_base_attribute77,
baseAtt.num_base_attribute78, baseAtt.num_base_attribute79, baseAtt.num_base_attribute80,
baseAtt.num_base_attribute81, baseAtt.num_base_attribute82, baseAtt.num_base_attribute83,
baseAtt.num_base_attribute84, baseAtt.num_base_attribute85, baseAtt.num_base_attribute86,
baseAtt.num_base_attribute87, baseAtt.num_base_attribute88, baseAtt.num_base_attribute89,
baseAtt.num_base_attribute90, baseAtt.num_base_attribute91, baseAtt.num_base_attribute92,
baseAtt.num_base_attribute93, baseAtt.num_base_attribute94, baseAtt.num_base_attribute95,
baseAtt.num_base_attribute96, baseAtt.num_base_attribute97, baseAtt.num_base_attribute98,
baseAtt.num_base_attribute99, baseAtt.num_base_attribute100, catAtt.text_cat_attribute1,
catAtt.text_cat_attribute2, catAtt.text_cat_attribute3, catAtt.text_cat_attribute4,
catAtt.text_cat_attribute5, catAtt.text_cat_attribute6, catAtt.text_cat_attribute7,
catAtt.text_cat_attribute8, catAtt.text_cat_attribute9, catAtt.text_cat_attribute10,
catAtt.text_cat_attribute11, catAtt.text_cat_attribute12, catAtt.text_cat_attribute13,
catAtt.text_cat_attribute14, catAtt.text_cat_attribute15, catAtt.text_cat_attribute16,
catAtt.text_cat_attribute17, catAtt.text_cat_attribute18, catAtt.text_cat_attribute19,
catAtt.text_cat_attribute20, catAtt.text_cat_attribute21, catAtt.text_cat_attribute22,
catAtt.text_cat_attribute23, catAtt.text_cat_attribute24, catAtt.text_cat_attribute25,
catAtt.text_cat_attribute26, catAtt.text_cat_attribute27, catAtt.text_cat_attribute28,
catAtt.text_cat_attribute29, catAtt.text_cat_attribute30, catAtt.text_cat_attribute31,
catAtt.text_cat_attribute32, catAtt.text_cat_attribute33, catAtt.text_cat_attribute34,
catAtt.text_cat_attribute35, catAtt.text_cat_attribute36, catAtt.text_cat_attribute37,
catAtt.text_cat_attribute38, catAtt.text_cat_attribute39, catAtt.text_cat_attribute40,
catAtt.text_cat_attribute41, catAtt.text_cat_attribute42, catAtt.text_cat_attribute43,
catAtt.text_cat_attribute44, catAtt.text_cat_attribute45, catAtt.text_cat_attribute46,
catAtt.text_cat_attribute47, catAtt.text_cat_attribute48, catAtt.text_cat_attribute49,
catAtt.text_cat_attribute50, catAtt.num_cat_attribute1, catAtt.num_cat_attribute2,
catAtt.num_cat_attribute3, catAtt.num_cat_attribute4, catAtt.num_cat_attribute5,
catAtt.num_cat_attribute6, catAtt.num_cat_attribute7, catAtt.num_cat_attribute8,
catAtt.num_cat_attribute9, catAtt.num_cat_attribute10, catAtt.num_cat_attribute11,
catAtt.num_cat_attribute12, catAtt.num_cat_attribute13, catAtt.num_cat_attribute14,
catAtt.num_cat_attribute15, catAtt.num_cat_attribute16, catAtt.num_cat_attribute17,
catAtt.num_cat_attribute18, catAtt.num_cat_attribute19, catAtt.num_cat_attribute20,
catAtt.num_cat_attribute21, catAtt.num_cat_attribute22, catAtt.num_cat_attribute23,
catAtt.num_cat_attribute24, catAtt.num_cat_attribute25, catAtt.num_cat_attribute26,
catAtt.num_cat_attribute27, catAtt.num_cat_attribute28, catAtt.num_cat_attribute29,
catAtt.num_cat_attribute30, catAtt.num_cat_attribute31, catAtt.num_cat_attribute32,
catAtt.num_cat_attribute33, catAtt.num_cat_attribute34, catAtt.num_cat_attribute35,
catAtt.num_cat_attribute36, catAtt.num_cat_attribute37, catAtt.num_cat_attribute38,
catAtt.num_cat_attribute39, catAtt.num_cat_attribute40, catAtt.num_cat_attribute41,
catAtt.num_cat_attribute42, catAtt.num_cat_attribute43, catAtt.num_cat_attribute44,
catAtt.num_cat_attribute45, catAtt.num_cat_attribute46, catAtt.num_cat_attribute47,
catAtt.num_cat_attribute48, catAtt.num_cat_attribute49, catAtt.num_cat_attribute50
FROM icx_cat_items_tlp baseAtt, icx_cat_ext_items_tlp catAtt
WHERE baseAtt.rt_item_id = catAtt.rt_item_id
AND baseAtt.language = catAtt.language
AND baseAtt.rt_item_id = gIAVRtItemIdTbl(i)
AND baseAtt.language = gIAVLanguageTbl(i);
'No: of rows inserted into po_attr_values_interface:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_INT_ATTR_VALS_TLP';
INSERT INTO po_attr_values_tlp_interface
(interface_attr_values_tlp_id, interface_header_id,
interface_line_id, action, process_code,
po_line_id, req_template_name, req_template_line_num,
inventory_item_id, org_id, language,
ip_category_id, description, manufacturer,
comments, alias, long_description,
tl_text_base_attribute1, tl_text_base_attribute2, tl_text_base_attribute3,
tl_text_base_attribute4, tl_text_base_attribute5, tl_text_base_attribute6,
tl_text_base_attribute7, tl_text_base_attribute8, tl_text_base_attribute9,
tl_text_base_attribute10, tl_text_base_attribute11, tl_text_base_attribute12,
tl_text_base_attribute13, tl_text_base_attribute14, tl_text_base_attribute15,
tl_text_base_attribute16, tl_text_base_attribute17, tl_text_base_attribute18,
tl_text_base_attribute19, tl_text_base_attribute20, tl_text_base_attribute21,
tl_text_base_attribute22, tl_text_base_attribute23, tl_text_base_attribute24,
tl_text_base_attribute25, tl_text_base_attribute26, tl_text_base_attribute27,
tl_text_base_attribute28, tl_text_base_attribute29, tl_text_base_attribute30,
tl_text_base_attribute31, tl_text_base_attribute32, tl_text_base_attribute33,
tl_text_base_attribute34, tl_text_base_attribute35, tl_text_base_attribute36,
tl_text_base_attribute37, tl_text_base_attribute38, tl_text_base_attribute39,
tl_text_base_attribute40, tl_text_base_attribute41, tl_text_base_attribute42,
tl_text_base_attribute43, tl_text_base_attribute44, tl_text_base_attribute45,
tl_text_base_attribute46, tl_text_base_attribute47, tl_text_base_attribute48,
tl_text_base_attribute49, tl_text_base_attribute50, tl_text_base_attribute51,
tl_text_base_attribute52, tl_text_base_attribute53, tl_text_base_attribute54,
tl_text_base_attribute55, tl_text_base_attribute56, tl_text_base_attribute57,
tl_text_base_attribute58, tl_text_base_attribute59, tl_text_base_attribute60,
tl_text_base_attribute61, tl_text_base_attribute62, tl_text_base_attribute63,
tl_text_base_attribute64, tl_text_base_attribute65, tl_text_base_attribute66,
tl_text_base_attribute67, tl_text_base_attribute68, tl_text_base_attribute69,
tl_text_base_attribute70, tl_text_base_attribute71, tl_text_base_attribute72,
tl_text_base_attribute73, tl_text_base_attribute74, tl_text_base_attribute75,
tl_text_base_attribute76, tl_text_base_attribute77, tl_text_base_attribute78,
tl_text_base_attribute79, tl_text_base_attribute80, tl_text_base_attribute81,
tl_text_base_attribute82, tl_text_base_attribute83, tl_text_base_attribute84,
tl_text_base_attribute85, tl_text_base_attribute86, tl_text_base_attribute87,
tl_text_base_attribute88, tl_text_base_attribute89, tl_text_base_attribute90,
tl_text_base_attribute91, tl_text_base_attribute92, tl_text_base_attribute93,
tl_text_base_attribute94, tl_text_base_attribute95, tl_text_base_attribute96,
tl_text_base_attribute97, tl_text_base_attribute98, tl_text_base_attribute99,
tl_text_base_attribute100, tl_text_cat_attribute1, tl_text_cat_attribute2,
tl_text_cat_attribute3, tl_text_cat_attribute4, tl_text_cat_attribute5,
tl_text_cat_attribute6, tl_text_cat_attribute7, tl_text_cat_attribute8,
tl_text_cat_attribute9, tl_text_cat_attribute10, tl_text_cat_attribute11,
tl_text_cat_attribute12, tl_text_cat_attribute13, tl_text_cat_attribute14,
tl_text_cat_attribute15, tl_text_cat_attribute16, tl_text_cat_attribute17,
tl_text_cat_attribute18, tl_text_cat_attribute19, tl_text_cat_attribute20,
tl_text_cat_attribute21, tl_text_cat_attribute22, tl_text_cat_attribute23,
tl_text_cat_attribute24, tl_text_cat_attribute25, tl_text_cat_attribute26,
tl_text_cat_attribute27, tl_text_cat_attribute28, tl_text_cat_attribute29,
tl_text_cat_attribute30, tl_text_cat_attribute31, tl_text_cat_attribute32,
tl_text_cat_attribute33, tl_text_cat_attribute34, tl_text_cat_attribute35,
tl_text_cat_attribute36, tl_text_cat_attribute37, tl_text_cat_attribute38,
tl_text_cat_attribute39, tl_text_cat_attribute40, tl_text_cat_attribute41,
tl_text_cat_attribute42, tl_text_cat_attribute43, tl_text_cat_attribute44,
tl_text_cat_attribute45, tl_text_cat_attribute46, tl_text_cat_attribute47,
tl_text_cat_attribute48, tl_text_cat_attribute49, tl_text_cat_attribute50)
SELECT po_attr_values_tlp_interface_s.NEXTVAL, gIAVTInterfaceHeaderIdTbl(i),
gIAVTInterfaceLineIdTbl(i), gIAVTActionTbl(i), g_process_code,
gIAVTPoLineIdTbl(i), gIAVTReqTemplateNameTbl(i), gIAVTReqTemplateLineNumTbl(i),
gIAVTInventoryItemIdTbl(i), gIAVTOrgIdTbl(i), gIAVTLanguageTbl(i), baseAtt.primary_category_id,
-- DECODE(gIAVTCheckDescUpdateTbl(i), ICX_CAT_UTIL_PVT.g_update_description, SUBSTRB(baseAtt.description, 1, 240), NULL),
SUBSTRB(baseAtt.description, 1, 240),
baseAtt.manufacturer, baseAtt.comments, baseAtt.alias, baseAtt.long_description,
baseAtt.tl_text_base_attribute1, baseAtt.tl_text_base_attribute2, baseAtt.tl_text_base_attribute3,
baseAtt.tl_text_base_attribute4, baseAtt.tl_text_base_attribute5, baseAtt.tl_text_base_attribute6,
baseAtt.tl_text_base_attribute7, baseAtt.tl_text_base_attribute8, baseAtt.tl_text_base_attribute9,
baseAtt.tl_text_base_attribute10, baseAtt.tl_text_base_attribute11, baseAtt.tl_text_base_attribute12,
baseAtt.tl_text_base_attribute13, baseAtt.tl_text_base_attribute14, baseAtt.tl_text_base_attribute15,
baseAtt.tl_text_base_attribute16, baseAtt.tl_text_base_attribute17, baseAtt.tl_text_base_attribute18,
baseAtt.tl_text_base_attribute19, baseAtt.tl_text_base_attribute20, baseAtt.tl_text_base_attribute21,
baseAtt.tl_text_base_attribute22, baseAtt.tl_text_base_attribute23, baseAtt.tl_text_base_attribute24,
baseAtt.tl_text_base_attribute25, baseAtt.tl_text_base_attribute26, baseAtt.tl_text_base_attribute27,
baseAtt.tl_text_base_attribute28, baseAtt.tl_text_base_attribute29, baseAtt.tl_text_base_attribute30,
baseAtt.tl_text_base_attribute31, baseAtt.tl_text_base_attribute32, baseAtt.tl_text_base_attribute33,
baseAtt.tl_text_base_attribute34, baseAtt.tl_text_base_attribute35, baseAtt.tl_text_base_attribute36,
baseAtt.tl_text_base_attribute37, baseAtt.tl_text_base_attribute38, baseAtt.tl_text_base_attribute39,
baseAtt.tl_text_base_attribute40, baseAtt.tl_text_base_attribute41, baseAtt.tl_text_base_attribute42,
baseAtt.tl_text_base_attribute43, baseAtt.tl_text_base_attribute44, baseAtt.tl_text_base_attribute45,
baseAtt.tl_text_base_attribute46, baseAtt.tl_text_base_attribute47, baseAtt.tl_text_base_attribute48,
baseAtt.tl_text_base_attribute49, baseAtt.tl_text_base_attribute50, baseAtt.tl_text_base_attribute51,
baseAtt.tl_text_base_attribute52, baseAtt.tl_text_base_attribute53, baseAtt.tl_text_base_attribute54,
baseAtt.tl_text_base_attribute55, baseAtt.tl_text_base_attribute56, baseAtt.tl_text_base_attribute57,
baseAtt.tl_text_base_attribute58, baseAtt.tl_text_base_attribute59, baseAtt.tl_text_base_attribute60,
baseAtt.tl_text_base_attribute61, baseAtt.tl_text_base_attribute62, baseAtt.tl_text_base_attribute63,
baseAtt.tl_text_base_attribute64, baseAtt.tl_text_base_attribute65, baseAtt.tl_text_base_attribute66,
baseAtt.tl_text_base_attribute67, baseAtt.tl_text_base_attribute68, baseAtt.tl_text_base_attribute69,
baseAtt.tl_text_base_attribute70, baseAtt.tl_text_base_attribute71, baseAtt.tl_text_base_attribute72,
baseAtt.tl_text_base_attribute73, baseAtt.tl_text_base_attribute74, baseAtt.tl_text_base_attribute75,
baseAtt.tl_text_base_attribute76, baseAtt.tl_text_base_attribute77, baseAtt.tl_text_base_attribute78,
baseAtt.tl_text_base_attribute79, baseAtt.tl_text_base_attribute80, baseAtt.tl_text_base_attribute81,
baseAtt.tl_text_base_attribute82, baseAtt.tl_text_base_attribute83, baseAtt.tl_text_base_attribute84,
baseAtt.tl_text_base_attribute85, baseAtt.tl_text_base_attribute86, baseAtt.tl_text_base_attribute87,
baseAtt.tl_text_base_attribute88, baseAtt.tl_text_base_attribute89, baseAtt.tl_text_base_attribute90,
baseAtt.tl_text_base_attribute91, baseAtt.tl_text_base_attribute92, baseAtt.tl_text_base_attribute93,
baseAtt.tl_text_base_attribute94, baseAtt.tl_text_base_attribute95, baseAtt.tl_text_base_attribute96,
baseAtt.tl_text_base_attribute97, baseAtt.tl_text_base_attribute98, baseAtt.tl_text_base_attribute99,
baseAtt.tl_text_base_attribute100, catAtt.tl_text_cat_attribute1, catAtt.tl_text_cat_attribute2,
catAtt.tl_text_cat_attribute3, catAtt.tl_text_cat_attribute4, catAtt.tl_text_cat_attribute5,
catAtt.tl_text_cat_attribute6, catAtt.tl_text_cat_attribute7, catAtt.tl_text_cat_attribute8,
catAtt.tl_text_cat_attribute9, catAtt.tl_text_cat_attribute10, catAtt.tl_text_cat_attribute11,
catAtt.tl_text_cat_attribute12, catAtt.tl_text_cat_attribute13, catAtt.tl_text_cat_attribute14,
catAtt.tl_text_cat_attribute15, catAtt.tl_text_cat_attribute16, catAtt.tl_text_cat_attribute17,
catAtt.tl_text_cat_attribute18, catAtt.tl_text_cat_attribute19, catAtt.tl_text_cat_attribute20,
catAtt.tl_text_cat_attribute21, catAtt.tl_text_cat_attribute22, catAtt.tl_text_cat_attribute23,
catAtt.tl_text_cat_attribute24, catAtt.tl_text_cat_attribute25, catAtt.tl_text_cat_attribute26,
catAtt.tl_text_cat_attribute27, catAtt.tl_text_cat_attribute28, catAtt.tl_text_cat_attribute29,
catAtt.tl_text_cat_attribute30, catAtt.tl_text_cat_attribute31, catAtt.tl_text_cat_attribute32,
catAtt.tl_text_cat_attribute33, catAtt.tl_text_cat_attribute34, catAtt.tl_text_cat_attribute35,
catAtt.tl_text_cat_attribute36, catAtt.tl_text_cat_attribute37, catAtt.tl_text_cat_attribute38,
catAtt.tl_text_cat_attribute39, catAtt.tl_text_cat_attribute40, catAtt.tl_text_cat_attribute41,
catAtt.tl_text_cat_attribute42, catAtt.tl_text_cat_attribute43, catAtt.tl_text_cat_attribute44,
catAtt.tl_text_cat_attribute45, catAtt.tl_text_cat_attribute46, catAtt.tl_text_cat_attribute47,
catAtt.tl_text_cat_attribute48, catAtt.tl_text_cat_attribute49, catAtt.tl_text_cat_attribute50
FROM icx_cat_items_tlp baseAtt, icx_cat_ext_items_tlp catAtt
WHERE baseAtt.rt_item_id = catAtt.rt_item_id
AND baseAtt.language = catAtt.language
AND baseAtt.rt_item_id = gIAVTRtItemIdTbl(i)
AND baseAtt.language = gIAVTLanguageTbl(i);
'No: of rows inserted into po_attr_values_tlp_interface:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_R12_UPGRADE';
INSERT INTO icx_cat_r12_upgrade
(supplier_site_id, currency, cpa_reference,
price_contract_id, src_contract_id, po_category_id,
rt_item_id, po_interface_header_id, po_interface_line_id,
created_language,
last_update_login, last_updated_by, last_update_date,
created_by, creation_date, internal_request_id, request_id,
program_application_id, program_id, program_login_id)
VALUES
(gIRUSupplierSiteIdTbl(i), gIRUCurrencyTbl(i), gIRUCpaReferenceTbl(i),
gIRUPriceContractIdTbl(i), gIRUSrcContractIdTbl(i), gIRUPoCategoryIdTbl(i),
gIRURtItemIdTbl(i), gIRUPoInterfaceHeaderIdTbl(i), gIRUPoInterfaceLineIdTbl(i),
gIRUCreatedLanguageTbl(i),
ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, sysdate,
ICX_CAT_UTIL_PVT.g_who_columns_rec.user_id, sysdate,
ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id);
'No: of rows inserted into icx_cat_r12_upgrade:' ||SQL%ROWCOUNT);
l_action_mode := 'UPDATE_R12_UPGRADE';
UPDATE icx_cat_r12_upgrade
SET po_interface_header_id = gURUPoInterfaceHeaderIdTbl(i),
po_interface_line_id = gURUPoInterfaceLineIdTbl(i),
po_category_id = gURUPoCategoryIdTbl(i),
cpa_reference = gURUCpaReferenceTbl(i),
price_contract_id = gURUPriceContractIdTbl(i),
src_contract_id = gURUSrcContractIdTbl(i),
po_header_id = gURUPoHeaderIdTbl(i),
po_line_id = gURUPoLineIdTbl(i),
created_language = gURUCreatedLanguageTbl(i),
last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
last_updated_by = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
last_update_date = sysdate,
internal_request_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id,
request_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
program_application_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
program_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
program_login_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id
WHERE po_interface_line_id = gURUOldPoInterfaceLineIdTbl(i)
AND rt_item_id = gURURtItemIdTbl(i);
'No: of rows updated in icx_cat_r12_upgrade:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_R12_UPGRADE';
DELETE FROM icx_cat_r12_upgrade
WHERE rt_item_id = gDRURtItemIdTbl(i)
AND po_interface_header_id = gDRUPoInterfaceHeaderIdTbl(i)
AND po_interface_line_id = gDRUPoInterfaceLineIdTbl(i);
'No: of rows deleted from icx_cat_r12_upgrade:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_FAV_LINES_CATALOG';
INSERT INTO icx_cat_fav_list_lines_tlp
(
favorite_list_line_id, favorite_list_id,
last_update_date, last_updated_by, last_update_login,
creation_date, created_by,
po_line_id, inventory_item_id,
item_description, rfq_required_flag,
req_template_name, req_template_line_num,
org_id, language, source_type
)
SELECT
gIFLCNewFavoriteListLineIdTbl(i), gIFLCFavoriteListIdTbl(i),
fav.last_update_date, fav.last_updated_by, fav.last_update_login,
NVL(fav.creation_date, fav.last_update_date),
NVL(fav.created_by, fav.last_updated_by),
NVL(fav.source_doc_line_id, -2) po_line_id,
NVL(fav.item_id, -2) inventory_item_id,
fav.item_description, fav.rfq_required_flag,
NVL(fav.template_name, '-2') req_template_name,
NVL(fav.template_line_num, -2) req_template_line_num,
gIFLCOrgIdTbl(i), gIFLCLanguageTbl(i),
gIFLCSourceTypeTbl(i)
FROM por_favorite_list_lines fav
WHERE favorite_list_line_id = gIFLCOldFavoriteListLineIdTbl(i)
AND favorite_list_id = gIFLCFavoriteListIdTbl(i);
'No: of rows inserted into icx_cat_fav_list_lines_tlp for catalog items:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_FAV_LINES_OTHER';
INSERT INTO icx_cat_fav_list_lines_tlp
(
favorite_list_line_id, favorite_list_id,
last_update_date, last_updated_by, last_update_login,
creation_date, created_by,
po_header_id, po_line_id, inventory_item_id,
item_description, line_type_id,
item_revision, po_category_id, unit_meas_lookup_code,
unit_price, suggested_vendor_id, suggested_vendor_name,
suggested_vendor_site_id, suggested_vendor_site,
suggested_vendor_contact_id, suggested_vendor_contact,
supplier_url, suggested_buyer_id,
suggested_buyer, supplier_item_num,
manufacturer_id, manufacturer_name, manufacturer_part_number,
rfq_required_flag, attribute_category,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
po_category_name, suggested_vendor_contact_phone, new_supplier,
req_template_name, req_template_line_num,
currency, rate_type, rate, rate_date,
noncat_template_id, suggested_vendor_contact_fax,
suggested_vendor_contact_email,
negotiated_by_preparer_flag, thumbnail_image,
org_id, language, source_type, amount
)
SELECT
gIFLONewFavoriteListLineIdTbl(i), gIFLOFavoriteListIdTbl(i),
fav.last_update_date, fav.last_updated_by, fav.last_update_login,
NVL(fav.creation_date, fav.last_update_date),
NVL(fav.created_by, fav.last_updated_by),
fav.source_doc_header_id po_header_id,
fav.source_doc_line_id po_line_id,
fav.item_id inventory_item_id,
fav.item_description,
NVL(fav.line_type_id, 1) line_type_id,
fav.item_revision, fav.category_id, fav.unit_meas_lookup_code,
DECODE(NVL(line_types.order_type_lookup_code, 'QUANTITY'),
'QUANTITY', fav.unit_price, NULL) unit_price,
fav.suggested_vendor_id, fav.suggested_vendor_name,
fav.suggested_vendor_site_id, fav.suggested_vendor_site,
fav.suggested_vendor_contact_id, fav.suggested_vendor_contact,
fav.supplier_url, fav.suggested_buyer_id,
fav.suggested_buyer, fav.supplier_item_num,
fav.manufacturer_id, fav.manufacturer_name, fav.manufacturer_part_number,
fav.rfq_required_flag, fav.attribute_category,
fav.attribute1, fav.attribute2, fav.attribute3, fav.attribute4, fav.attribute5,
fav.attribute6, fav.attribute7, fav.attribute8, fav.attribute9, fav.attribute10,
fav.attribute11, fav.attribute12, fav.attribute13, fav.attribute14, fav.attribute15,
fav.category, fav.suggested_vendor_contact_phone, fav.new_supplier,
fav.template_name req_template_name,
fav.template_line_num req_template_line_num,
NVL(fav.currency, gsob.currency_code) currency,
fav.rate_type, fav.rate, fav.rate_date,
fav.noncat_template_id, fav.suggested_vendor_contact_fax,
fav.suggested_vendor_contact_email,
fav.negotiated_by_preparer_flag, fav.thumbnail_image,
gIFLOOrgIdTbl(i), gIFLOLanguageTbl(i),
NVL(fav.item_type, 'NONCATALOG') source_type,
DECODE(NVL(line_types.order_type_lookup_code, 'QUANTITY'),
'QUANTITY', NULL, fav.unit_price) amount
FROM por_favorite_list_lines fav, po_line_types_b line_types,
financials_system_params_all fsp, gl_sets_of_books gsob
WHERE favorite_list_line_id = gIFLOOldFavoriteListLineIdTbl(i)
AND favorite_list_id = gIFLOFavoriteListIdTbl(i)
AND fav.line_type_id = line_types.line_type_id (+)
AND fsp.org_id = gIFLOOrgIdTbl(i)
AND fsp.set_of_books_id = gsob.set_of_books_id;
'No: of rows inserted into icx_cat_fav_list_lines_tlp for other items:' ||SQL%ROWCOUNT);
SELECT distinct p.contract_id po_header_id, p.price_type, p.org_id
FROM icx_cat_item_prices p
WHERE p.price_type IN ('BLANKET', 'QUOTATION')
AND p.contract_id >= p_start_po_header_id
ORDER BY p.contract_id;
SELECT distinct p.contract_id po_header_id, p.price_type, p.org_id
FROM icx_cat_item_prices p, icx_cat_items_tlp itemtlp,
icx_cat_items_b itemsb, icx_cat_ext_items_tlp exttlp
WHERE p.price_type IN ('BLANKET', 'QUOTATION')
AND p.contract_id >= p_start_po_header_id
AND itemtlp.rt_item_id = p.rt_item_id
AND itemsb.rt_item_id = p.rt_item_id
AND itemtlp.rt_item_id = exttlp.rt_item_id
AND itemtlp.language = exttlp.language
AND (itemsb.last_update_date > g_extract_last_run_date
OR itemtlp.last_update_date > g_extract_last_run_date
OR exttlp.last_update_date > g_extract_last_run_date
OR p.last_update_date > g_extract_last_run_date)
ORDER BY p.contract_id;
SELECT p.rt_item_id, itemtlp.language, p.price_type,
phi.interface_header_id, p.contract_id po_header_id, p.contract_line_id po_line_id,
p.inventory_item_id, p.org_id,
DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
itemsb.catalog_name, itemtlp.primary_category_id,
getPOAttrValuesTLPAction(p.contract_line_id, '-2' ,-2, p.org_id, itemtlp.language)
FROM icx_cat_item_prices p, icx_cat_items_tlp itemtlp,
icx_cat_items_b itemsb, po_headers_interface phi
WHERE p.price_type IN ('BLANKET', 'QUOTATION')
AND p.contract_line_id >= p_start_po_line_id
AND itemtlp.rt_item_id = p.rt_item_id
AND itemsb.rt_item_id = p.rt_item_id
AND p.contract_id = phi.po_header_id
AND phi.batch_id = g_PDOI_batch_id
ORDER BY p.contract_line_id, p.rt_item_id, itemtlp.language;
SELECT p.rt_item_id, itemtlp.language, p.price_type,
phi.interface_header_id, p.contract_id po_header_id, p.contract_line_id po_line_id,
p.inventory_item_id, p.org_id,
DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
itemsb.catalog_name, itemtlp.primary_category_id,
getPOAttrValuesTLPAction(p.contract_line_id, '-2' ,-2, p.org_id, itemtlp.language)
FROM icx_cat_item_prices p, icx_cat_items_tlp itemtlp,
icx_cat_items_b itemsb, icx_cat_ext_items_tlp exttlp, po_headers_interface phi
WHERE p.price_type IN ('BLANKET', 'QUOTATION')
AND p.contract_line_id >= p_start_po_line_id
AND itemtlp.rt_item_id = p.rt_item_id
AND itemsb.rt_item_id = p.rt_item_id
AND itemtlp.rt_item_id = exttlp.rt_item_id
AND itemtlp.language = exttlp.language
AND p.contract_id = phi.po_header_id
AND phi.batch_id = g_PDOI_batch_id
AND (itemsb.last_update_date > g_extract_last_run_date
OR itemtlp.last_update_date > g_extract_last_run_date
OR exttlp.last_update_date > g_extract_last_run_date
OR p.last_update_date > g_extract_last_run_date)
ORDER BY contract_line_id, p.rt_item_id, itemtlp.language;
l_po_header_id_tbl.DELETE;
l_price_type_tbl.DELETE;
l_org_id_tbl.DELETE;
g_po_hdrs_int_rec.action := 'UPDATE';
insertPOHeadersInterface;
l_rt_item_id_tbl.DELETE;
l_language_tbl.DELETE;
l_price_type_tbl.DELETE;
l_interface_header_id_tbl.DELETE;
l_po_header_id_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_inventory_item_id_tbl.DELETE;
l_org_id_tbl.DELETE;
l_supplier_part_auxid_tbl.DELETE;
l_catalog_name_tbl.DELETE;
l_primary_category_id_tbl.DELETE;
l_attr_val_tlp_action_tbl.DELETE;
g_po_line_attrval_int_rec.action := 'UPDATE';
insertPOLinesInterface;
insertPOAttrValsInterface;
g_po_attrvalstlp_int_rec.check_desc_update := ICX_CAT_UTIL_PVT.g_donot_update_description;
g_po_attrvalstlp_int_rec.check_desc_update := ICX_CAT_UTIL_PVT.g_update_description;
insertPOAttrValsTLPInterface;
SELECT distinct p.template_id, p.org_id
FROM icx_cat_item_prices p
WHERE p.price_type IN ('TEMPLATE', 'INTERNAL_TEMPLATE')
AND p.template_id >= p_start_template_id
ORDER BY p.template_id;
SELECT distinct p.template_id, p.org_id
FROM icx_cat_item_prices p, icx_cat_items_tlp itemtlp,
icx_cat_items_b itemsb, icx_cat_ext_items_tlp exttlp
WHERE p.price_type IN ('TEMPLATE', 'INTERNAL_TEMPLATE')
AND p.template_id >= p_start_template_id
AND itemtlp.rt_item_id = p.rt_item_id
AND itemsb.rt_item_id = p.rt_item_id
AND itemtlp.rt_item_id = exttlp.rt_item_id
AND itemtlp.language = exttlp.language
AND (itemsb.last_update_date > g_extract_last_run_date
OR itemtlp.last_update_date > g_extract_last_run_date
OR exttlp.last_update_date > g_extract_last_run_date
OR p.last_update_date > g_extract_last_run_date)
ORDER BY p.template_id;
SELECT p.rt_item_id, itemtlp.language, phi.interface_header_id,
p.template_id req_template_name, p.template_line_id req_template_line_num,
p.inventory_item_id, p.org_id,
DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
itemsb.catalog_name, itemtlp.primary_category_id,
getPOAttrValuesTLPAction(-2, p.template_id, p.template_line_id, p.org_id, itemtlp.language)
FROM icx_cat_item_prices p, icx_cat_items_tlp itemtlp,
icx_cat_items_b itemsb, po_headers_interface phi
WHERE p.price_type IN ('TEMPLATE', 'INTERNAL_TEMPLATE')
AND p.rt_item_id >= p_start_rt_item_id
AND itemtlp.rt_item_id = p.rt_item_id
AND itemsb.rt_item_id = p.rt_item_id
AND p.template_id = phi.budget_account_segment1
AND p.org_id = phi.org_id
AND phi.batch_id = g_PDOI_batch_id
ORDER BY p.rt_item_id, itemtlp.language;
SELECT p.rt_item_id, itemtlp.language, phi.interface_header_id,
p.template_id req_template_name, p.template_line_id req_template_line_num,
p.inventory_item_id, p.org_id,
DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
itemsb.catalog_name, itemtlp.primary_category_id,
getPOAttrValuesTLPAction(-2, p.template_id, p.template_line_id, p.org_id, itemtlp.language)
FROM icx_cat_item_prices p, icx_cat_items_tlp itemtlp,
icx_cat_items_b itemsb, icx_cat_ext_items_tlp exttlp, po_headers_interface phi
WHERE p.price_type IN ('TEMPLATE', 'INTERNAL_TEMPLATE')
AND p.rt_item_id >= p_start_rt_item_id
AND itemtlp.rt_item_id = p.rt_item_id
AND itemsb.rt_item_id = p.rt_item_id
AND itemtlp.rt_item_id = exttlp.rt_item_id
AND itemtlp.language = exttlp.language
AND p.template_id = phi.budget_account_segment1
AND p.org_id = phi.org_id
AND phi.batch_id = g_PDOI_batch_id
AND (itemsb.last_update_date > g_extract_last_run_date
OR itemtlp.last_update_date > g_extract_last_run_date
OR exttlp.last_update_date > g_extract_last_run_date
OR p.last_update_date > g_extract_last_run_date)
ORDER BY p.rt_item_id, itemtlp.language;
l_req_template_name_tbl.DELETE;
l_org_id_tbl.DELETE;
insertPOHeadersInterface;
l_rt_item_id_tbl.DELETE;
l_language_tbl.DELETE;
l_interface_header_id_tbl.DELETE;
l_req_template_name_tbl.DELETE;
l_req_template_line_num_tbl.DELETE;
l_inventory_item_id_tbl.DELETE;
l_org_id_tbl.DELETE;
l_supplier_part_auxid_tbl.DELETE;
l_catalog_name_tbl.DELETE;
l_primary_category_id_tbl.DELETE;
l_attr_val_tlp_action_tbl.DELETE;
insertPOLinesInterface;
g_po_line_attrval_int_rec.action := 'UPDATE';
insertPOAttrValsInterface;
g_po_attrvalstlp_int_rec.check_desc_update := ICX_CAT_UTIL_PVT.g_donot_update_description;
g_po_attrvalstlp_int_rec.check_desc_update := ICX_CAT_UTIL_PVT.g_update_description;
insertPOAttrValsTLPInterface;
SELECT NVL(price.org_id, -2), NVL(itemtlp.supplier_id, -2), NVL(price.supplier_site_id, -2),
NVL(price.currency, '-2'), NVL(price.contract_id, -2) contract_id,
NVL(map.external_source_key, '-2') po_category_id
FROM icx_cat_item_prices price,
icx_cat_items_tlp itemtlp,
icx_por_category_order_map map
WHERE price.price_type = 'BULKLOAD'
AND price.rt_item_id = itemtlp.rt_item_id
AND NOT EXISTS (SELECT 'extracted price'
FROM icx_cat_item_prices priceIn
WHERE priceIn.rt_item_id = price.rt_item_id
AND priceIn.price_type IN ('BLANKET', 'GLOBAL_AGREEMENT', 'QUOTATION',
'INTERNAL_TEMPLATE', 'TEMPLATE', 'ASL'))
AND itemtlp.primary_category_id = map.rt_category_id (+)
GROUP BY price.org_id, itemtlp.supplier_id, price.supplier_site_id,
price.currency, price.contract_id, map.external_source_key
ORDER BY price.org_id;
SELECT NVL(price.org_id, -2), NVL(itemtlp.supplier_id, -2), NVL(price.supplier_site_id, -2),
NVL(price.currency, '-2'), NVL(price.contract_id, -2) contract_id,
NVL(map.external_source_key, '-2') po_category_id
FROM icx_cat_item_prices price,
icx_cat_items_tlp itemtlp,
icx_por_category_order_map map,
icx_cat_items_b itemb,
icx_cat_ext_items_tlp extitemtlp,
icx_cat_r12_upgrade upg
WHERE price.price_type = 'BULKLOAD'
AND price.rt_item_id = itemtlp.rt_item_id
AND NOT EXISTS (SELECT 'extracted price'
FROM icx_cat_item_prices priceIn
WHERE priceIn.rt_item_id = price.rt_item_id
AND priceIn.price_type IN ('BLANKET', 'GLOBAL_AGREEMENT', 'QUOTATION',
'INTERNAL_TEMPLATE', 'TEMPLATE', 'ASL'))
AND itemtlp.primary_category_id = map.rt_category_id (+)
AND price.rt_item_id = itemb.rt_item_id
AND price.rt_item_id = extitemtlp.rt_item_id
AND itemtlp.language = extitemtlp.language
AND price.rt_item_id = upg.rt_item_id (+)
AND price.supplier_site_id = upg.supplier_site_id (+)
AND price.currency = upg.currency (+)
AND price.contract_id = upg.price_contract_id (+)
AND ( -- Last update changes in items / price tables
(itemb.last_update_date > g_bulk_last_run_date
OR itemtlp.last_update_date > g_bulk_last_run_date
OR extitemtlp.last_update_date > g_bulk_last_run_date
OR price.last_update_date > g_bulk_last_run_date)
OR -- The items that errored out in the previous run
(upg.po_header_id is null
OR upg.po_line_id is null))
GROUP BY price.org_id, itemtlp.supplier_id, price.supplier_site_id,
price.currency, price.contract_id, map.external_source_key
ORDER BY price.org_id;
INSERT INTO icx_cat_r12_upg_autosource
(org_id, supplier_id, supplier_site_id, currency,
contract_id, po_category_id,
last_update_login, last_updated_by, last_update_date,
created_by, creation_date, internal_request_id, request_id,
program_application_id, program_id, program_login_id)
VALUES
(l_as_org_id_tbl(i), l_as_supplier_id_tbl(i),
l_as_supplier_site_id_tbl(i), l_as_currency_tbl(i),
l_as_contract_id_tbl(i), l_as_po_category_id_tbl(i),
ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, sysdate,
ICX_CAT_UTIL_PVT.g_who_columns_rec.user_id, sysdate,
ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id);
'No: of rows inserted in autosource:' || SQL%ROWCOUNT);
l_as_org_id_tbl.DELETE;
l_as_supplier_id_tbl.DELETE;
l_as_supplier_site_id_tbl.DELETE;
l_as_currency_tbl.DELETE;
l_as_contract_id_tbl.DELETE;
l_as_po_category_id_tbl.DELETE;
SELECT itemtlp.rt_item_id, price.unit_price, price.unit_of_measure,
DECODE(NVL(price.negotiated_by_preparer_flag, '1'), '0', 'N', 'Y'),
itemtlp.primary_category_id, NVL(map.external_source_key, '-2') po_category_id,
SUBSTRB(itemtlp.supplier_part_num, 1, 25) supplier_part_num,
DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
SUBSTRB(itemtlp.description, 1, 240) description, itemsb.catalog_name,
upg.po_interface_header_id, upg.created_language, upg.po_interface_line_id,
price.price_type, itemtlp.primary_category_name
FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp, icx_cat_items_b itemsb,
icx_por_category_order_map map, icx_cat_r12_upg_autosource src,
icx_cat_r12_upgrade upg
WHERE price.price_type = 'BULKLOAD'
AND price.rt_item_id = itemtlp.rt_item_id
AND price.rt_item_id = itemsb.rt_item_id
AND NOT EXISTS (SELECT 'extracted price'
FROM icx_cat_item_prices priceIn
WHERE priceIn.rt_item_id = price.rt_item_id
AND priceIn.price_type IN ('BLANKET', 'GLOBAL_AGREEMENT', 'QUOTATION',
'INTERNAL_TEMPLATE', 'TEMPLATE', 'ASL'))
AND itemtlp.primary_category_id = map.rt_category_id (+)
AND itemtlp.org_id = src.org_id
AND itemtlp.supplier_id = src.supplier_id
AND price.supplier_site_id = src.supplier_site_id
AND price.currency = src.currency
AND NVL(map.external_source_key, '-2') = src.po_category_id
AND price.rt_item_id = upg.rt_item_id (+)
AND price.supplier_site_id = upg.supplier_site_id (+)
AND price.currency = upg.currency (+)
AND price.contract_id = upg.price_contract_id (+)
AND itemtlp.rt_item_id >= p_start_rt_item_id
AND itemtlp.language = p_language
AND src.org_id = p_org_id
AND src.supplier_id = p_supplier_id
AND src.supplier_site_id = p_supplier_site_id
AND src.currency = p_currency
AND src.contract_id = p_cpa_reference
UNION ALL
SELECT itemtlp.rt_item_id, price.unit_price, price.unit_of_measure,
DECODE(NVL(price.negotiated_by_preparer_flag, '1'), '0', 'N', 'Y'),
itemtlp.primary_category_id, NVL(map.external_source_key, '-2') po_category_id,
SUBSTRB(itemtlp.supplier_part_num, 1, 25) supplier_part_num,
DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
SUBSTRB(itemtlp.description, 1, 240) description, itemsb.catalog_name,
upg.po_interface_header_id, upg.created_language, upg.po_interface_line_id,
price.price_type, itemtlp.primary_category_name
FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp, icx_cat_items_b itemsb,
icx_por_category_order_map map, icx_cat_r12_upgrade upg
WHERE price.price_type = 'CONTRACT'
AND price.rt_item_id = itemtlp.rt_item_id
AND price.rt_item_id = itemsb.rt_item_id
AND NOT EXISTS (SELECT 'extracted price'
FROM icx_cat_item_prices priceIn
WHERE priceIn.rt_item_id = price.rt_item_id
AND priceIn.price_type IN ('BLANKET', 'GLOBAL_AGREEMENT', 'QUOTATION',
'INTERNAL_TEMPLATE', 'TEMPLATE', 'ASL'))
AND itemtlp.primary_category_id = map.rt_category_id (+)
AND price.rt_item_id = upg.rt_item_id (+)
AND price.supplier_site_id = upg.supplier_site_id (+)
AND price.currency = upg.currency (+)
AND price.contract_id = upg.price_contract_id (+)
AND itemtlp.rt_item_id >= p_start_rt_item_id
AND itemtlp.language = p_language
AND itemtlp.org_id = p_org_id
AND itemtlp.supplier_id = p_supplier_id
AND price.supplier_site_id = p_supplier_site_id
AND price.currency = p_currency
AND price.contract_id = p_cpa_reference
ORDER BY 1;
l_rt_item_id_tbl.DELETE;
l_unit_price_tbl.DELETE;
l_unit_of_measure_tbl.DELETE;
l_neg_by_prep_flag_tbl.DELETE;
l_primary_category_id_tbl.DELETE;
l_po_category_id_tbl.DELETE;
l_supp_part_num_tbl.DELETE;
l_supp_part_auxid_tbl.DELETE;
l_description_tbl.DELETE;
l_catalog_name_tbl.DELETE;
l_po_interface_header_id_tbl.DELETE;
l_upg_created_language_tbl.DELETE;
l_po_interface_line_id_tbl.DELETE;
l_price_type_tbl.DELETE;
l_primary_category_name_tbl.DELETE;
insertPOHeadersInterface;
'inserting into lines, attr_values and r12 upgrade');
insertPOLinesInterface;
insertPOAttrValsInterface;
insertR12Upgrade;
g_po_attrvalstlp_int_rec.check_desc_update := ICX_CAT_UTIL_PVT.g_update_description;
'inserting only into attr_values_tlp');
insertPOAttrValsTLPInterface;
SELECT itemtlp.rt_item_id, price.unit_price, price.unit_of_measure,
DECODE(NVL(price.negotiated_by_preparer_flag, '1'), '0', 'N', 'Y') negotiated_by_preparer_flag,
itemtlp.primary_category_id, NVL(map.external_source_key, '-2') po_category_id,
SUBSTRB(itemtlp.supplier_part_num, 1, 25) supplier_part_num,
DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
SUBSTRB(itemtlp.description, 1, 240) description,
itemsb.catalog_name, price.price_type, itemtlp.primary_category_name
FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp, icx_cat_items_b itemsb,
icx_por_category_order_map map, icx_cat_r12_upg_autosource src
WHERE price.price_type = 'BULKLOAD'
AND price.rt_item_id = itemtlp.rt_item_id
AND price.rt_item_id = itemsb.rt_item_id
AND NOT EXISTS (SELECT 'extracted price'
FROM icx_cat_item_prices priceIn
WHERE priceIn.rt_item_id = price.rt_item_id
AND priceIn.price_type IN ('BLANKET', 'GLOBAL_AGREEMENT', 'QUOTATION',
'INTERNAL_TEMPLATE', 'TEMPLATE', 'ASL'))
AND itemtlp.primary_category_id = map.rt_category_id (+)
AND itemtlp.org_id = src.org_id
AND itemtlp.supplier_id = src.supplier_id
AND price.supplier_site_id = src.supplier_site_id
AND price.currency = src.currency
AND NVL(map.external_source_key, '-2') = src.po_category_id
AND itemtlp.rt_item_id >= p_start_rt_item_id
AND itemtlp.language = p_language
AND src.org_id = p_org_id
AND src.supplier_id = p_supplier_id
AND src.supplier_site_id = p_supplier_site_id
AND src.currency = p_currency
AND src.contract_id = p_cpa_reference
UNION ALL
SELECT itemtlp.rt_item_id, price.unit_price, price.unit_of_measure,
DECODE(NVL(price.negotiated_by_preparer_flag, '1'), '0', 'N', 'Y') negotiated_by_preparer_flag,
itemtlp.primary_category_id, NVL(map.external_source_key, '-2') po_category_id,
SUBSTRB(itemtlp.supplier_part_num, 1, 25) supplier_part_num,
DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
SUBSTRB(itemtlp.description, 1, 240) description,
itemsb.catalog_name, price.price_type, itemtlp.primary_category_name
FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp, icx_cat_items_b itemsb,
icx_por_category_order_map map
WHERE price.price_type = 'CONTRACT'
AND price.rt_item_id = itemtlp.rt_item_id
AND price.rt_item_id = itemsb.rt_item_id
AND NOT EXISTS (SELECT 'extracted price'
FROM icx_cat_item_prices priceIn
WHERE priceIn.rt_item_id = price.rt_item_id
AND priceIn.price_type IN ('BLANKET', 'GLOBAL_AGREEMENT', 'QUOTATION',
'INTERNAL_TEMPLATE', 'TEMPLATE', 'ASL'))
AND itemtlp.primary_category_id = map.rt_category_id (+)
AND itemtlp.rt_item_id >= p_start_rt_item_id
AND itemtlp.language = p_language
AND itemtlp.org_id = p_org_id
AND itemtlp.supplier_id = p_supplier_id
AND price.supplier_site_id = p_supplier_site_id
AND price.currency = p_currency
AND price.contract_id = p_cpa_reference
ORDER BY 1;
l_rt_item_id_tbl.DELETE;
l_unit_price_tbl.DELETE;
l_unit_of_measure_tbl.DELETE;
l_neg_by_prep_flag_tbl.DELETE;
l_primary_category_id_tbl.DELETE;
l_po_category_id_tbl.DELETE;
l_supp_part_num_tbl.DELETE;
l_supp_part_auxid_tbl.DELETE;
l_description_tbl.DELETE;
l_catalog_name_tbl.DELETE;
l_price_type_tbl.DELETE;
l_primary_category_name_tbl.DELETE;
g_po_attrvalstlp_int_rec.check_desc_update := ICX_CAT_UTIL_PVT.g_update_description;
'inserting into lines, attr_values, attr_values_tlp and r12 upgrade');
insertPOLinesInterface;
insertPOAttrValsInterface;
insertPOAttrValsTLPInterface;
insertR12Upgrade;
SELECT doc.*,
COUNT(*) count
FROM (
SELECT src.org_id org_id, src.supplier_id supplier_id,
src.supplier_site_id supplier_site_id, src.currency currency,
src.contract_id gbpa_cpa_reference,
itemtlp.language language
FROM icx_cat_r12_upg_autosource src, icx_cat_item_prices price,
icx_cat_items_tlp itemtlp, icx_por_category_order_map map
WHERE price.price_type = 'BULKLOAD'
AND price.rt_item_id = itemtlp.rt_item_id
AND NOT EXISTS (SELECT 'extracted price'
FROM icx_cat_item_prices priceIn
WHERE priceIn.rt_item_id = price.rt_item_id
AND priceIn.price_type IN ('BLANKET', 'GLOBAL_AGREEMENT', 'QUOTATION',
'INTERNAL_TEMPLATE', 'TEMPLATE', 'ASL'))
AND itemtlp.primary_category_id = map.rt_category_id (+)
AND itemtlp.org_id = src.org_id
AND itemtlp.supplier_id = src.supplier_id
AND price.supplier_site_id = src.supplier_site_id
AND price.currency = src.currency
AND NVL(map.external_source_key, '-2') = src.po_category_id
UNION ALL
-- Added NVL to org_id, supplier, site and currency
SELECT NVL(itemtlp.org_id, -2) org_id, NVL(itemtlp.supplier_id, -2) supplier_id,
NVL(price.supplier_site_id, -2) supplier_site_id, NVL(price.currency, '-2') currency,
price.contract_id gbpa_cpa_reference,
itemtlp.language language
FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp
WHERE price.price_type = 'CONTRACT'
AND price.rt_item_id = itemtlp.rt_item_id
AND NOT EXISTS (SELECT 'extracted price'
FROM icx_cat_item_prices priceIn
WHERE priceIn.rt_item_id = price.rt_item_id
AND priceIn.price_type IN ('BLANKET', 'GLOBAL_AGREEMENT', 'QUOTATION',
'INTERNAL_TEMPLATE', 'TEMPLATE', 'ASL'))
) doc
GROUP BY doc.org_id, doc.supplier_id, doc.supplier_site_id,
doc.currency, doc.gbpa_cpa_reference,
doc.language
ORDER BY doc.org_id, doc.supplier_id, doc.supplier_site_id,
doc.currency, doc.gbpa_cpa_reference,
count DESC, doc.language;
insertPOHeadersInterface;
SELECT po_interface_header_id, po_header_id
INTO l_po_interface_header_id, l_po_header_id
FROM icx_cat_r12_upgrade upg, icx_cat_items_b b
WHERE b.rt_item_id = upg.rt_item_id
AND b.org_id = p_org_id
AND b.supplier_id = p_supplier_id
AND upg.supplier_site_id = p_supplier_site_id
AND upg.currency = p_currency
AND upg.cpa_reference = p_cpa_reference
AND upg.created_language = p_created_language
AND rownum < 2;
SELECT itemtlp.rt_item_id, price.unit_price, price.unit_of_measure,
DECODE(NVL(price.negotiated_by_preparer_flag, '1'), '0', 'N', 'Y'),
itemtlp.primary_category_id, NVL(map.external_source_key, '-2') po_category_id,
SUBSTRB(itemtlp.supplier_part_num, 1, 25) supplier_part_num,
DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
SUBSTRB(itemtlp.description, 1, 240) description, itemb.catalog_name,
upg.po_interface_header_id, upg.po_interface_line_id,
upg.po_header_id, upg.po_line_id, upg.created_language,
upg.po_category_id old_po_catgegory_id,
DECODE(attr.ATTRIBUTE_VALUES_TLP_ID, NULL, 'ADD', 'UPDATE') attr_val_tlp_action,
price.price_type, itemtlp.primary_category_name
FROM icx_cat_r12_upg_autosource src, icx_cat_item_prices price,
icx_cat_items_tlp itemtlp, icx_por_category_order_map map,
icx_cat_items_b itemb, icx_cat_ext_items_tlp extitemtlp,
icx_cat_r12_upgrade upg, po_attribute_values_tlp attr
WHERE price.price_type = 'BULKLOAD'
AND price.rt_item_id = itemtlp.rt_item_id
AND NOT EXISTS (SELECT 'extracted price'
FROM icx_cat_item_prices priceIn
WHERE priceIn.rt_item_id = price.rt_item_id
AND priceIn.price_type IN ('BLANKET', 'GLOBAL_AGREEMENT', 'QUOTATION',
'INTERNAL_TEMPLATE', 'TEMPLATE', 'ASL'))
AND itemtlp.primary_category_id = map.rt_category_id (+)
AND itemtlp.org_id = src.org_id
AND itemtlp.supplier_id = src.supplier_id
AND price.supplier_site_id = src.supplier_site_id
AND price.currency = src.currency
AND NVL(map.external_source_key, '-2') = src.po_category_id
AND price.rt_item_id = itemb.rt_item_id
AND price.rt_item_id = extitemtlp.rt_item_id
AND itemtlp.language = extitemtlp.language
AND price.rt_item_id = upg.rt_item_id (+)
AND price.supplier_site_id = upg.supplier_site_id (+)
AND price.currency = upg.currency (+)
AND price.contract_id = upg.price_contract_id (+)
AND (upg.po_header_id IS NULL AND p_po_header_id IS NULL OR upg.po_header_id = p_po_header_id)
AND ( -- Last update changes in items / price tables
(itemb.last_update_date > g_bulk_last_run_date
OR itemtlp.last_update_date > g_bulk_last_run_date
OR extitemtlp.last_update_date > g_bulk_last_run_date
OR price.last_update_date > g_bulk_last_run_date)
OR -- The items that errored out in the previous run
(upg.po_header_id is null
OR upg.po_line_id is null))
AND itemtlp.rt_item_id >= p_start_rt_item_id
AND itemtlp.language = p_language
AND src.org_id = p_org_id
AND src.supplier_id = p_supplier_id
AND src.supplier_site_id = p_supplier_site_id
AND src.currency = p_currency
AND src.contract_id = p_cpa_reference
AND attr.po_line_id (+) = upg.po_line_id
AND attr.req_template_name (+) = '-2'
AND attr.req_template_line_num (+) = -2
AND attr.org_id (+) = p_org_id
AND attr.language (+) = p_language
UNION ALL
SELECT itemtlp.rt_item_id, price.unit_price, price.unit_of_measure,
DECODE(NVL(price.negotiated_by_preparer_flag, '1'), '0', 'N', 'Y'),
itemtlp.primary_category_id, NVL(map.external_source_key, '-2') po_category_id,
SUBSTRB(itemtlp.supplier_part_num, 1, 25) supplier_part_num,
DECODE(itemtlp.supplier_part_auxid, '##NULL##', null, itemtlp.supplier_part_auxid),
SUBSTRB(itemtlp.description, 1, 240) description, itemb.catalog_name,
upg.po_interface_header_id, upg.po_interface_line_id,
upg.po_header_id, upg.po_line_id, upg.created_language,
upg.po_category_id old_po_catgegory_id,
DECODE(attr.ATTRIBUTE_VALUES_TLP_ID, NULL, 'ADD', 'UPDATE') attr_val_tlp_action,
price.price_type, itemtlp.primary_category_name
FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp,
icx_por_category_order_map map,
icx_cat_items_b itemb, icx_cat_ext_items_tlp extitemtlp,
icx_cat_r12_upgrade upg, po_attribute_values_tlp attr
WHERE price.price_type = 'CONTRACT'
AND price.rt_item_id = itemtlp.rt_item_id
AND NOT EXISTS (SELECT 'extracted price'
FROM icx_cat_item_prices priceIn
WHERE priceIn.rt_item_id = price.rt_item_id
AND priceIn.price_type IN ('BLANKET', 'GLOBAL_AGREEMENT', 'QUOTATION',
'INTERNAL_TEMPLATE', 'TEMPLATE', 'ASL'))
AND itemtlp.primary_category_id = map.rt_category_id (+)
AND price.rt_item_id = itemb.rt_item_id
AND price.rt_item_id = extitemtlp.rt_item_id
AND itemtlp.language = extitemtlp.language
AND price.rt_item_id = upg.rt_item_id (+)
AND price.supplier_site_id = upg.supplier_site_id (+)
AND price.currency = upg.currency (+)
AND price.contract_id = upg.price_contract_id (+)
AND (upg.po_header_id IS NULL AND p_po_header_id IS NULL OR upg.po_header_id = p_po_header_id)
AND ( -- Last update changes in items / price tables
(itemb.last_update_date > g_bulk_last_run_date
OR itemtlp.last_update_date > g_bulk_last_run_date
OR extitemtlp.last_update_date > g_bulk_last_run_date
OR price.last_update_date > g_bulk_last_run_date)
OR -- The items that errored out in the previous run
(upg.po_header_id is null
OR upg.po_line_id is null))
AND itemtlp.rt_item_id >= p_start_rt_item_id
AND itemtlp.language = p_language
AND itemtlp.org_id = p_org_id
AND itemtlp.supplier_id = p_supplier_id
AND price.supplier_site_id = p_supplier_site_id
AND price.currency = p_currency
AND price.contract_id = p_cpa_reference
AND attr.po_line_id (+) = upg.po_line_id
AND attr.req_template_name (+) = '-2'
AND attr.req_template_line_num (+) = -2
AND attr.org_id (+) = p_org_id
AND attr.language (+) = p_language
ORDER BY 1;
l_line_deleted BOOLEAN := FALSE;
l_rt_item_id_tbl.DELETE;
l_unit_price_tbl.DELETE;
l_unit_of_measure_tbl.DELETE;
l_neg_by_prep_flag_tbl.DELETE;
l_primary_category_id_tbl.DELETE;
l_po_category_id_tbl.DELETE;
l_supp_part_num_tbl.DELETE;
l_supp_part_auxid_tbl.DELETE;
l_description_tbl.DELETE;
l_catalog_name_tbl.DELETE;
l_po_interface_header_id_tbl.DELETE;
l_po_interface_line_id_tbl.DELETE;
l_po_header_id_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_created_language_tbl.DELETE;
l_old_po_category_id_tbl.DELETE;
l_attr_val_tlp_action_tbl.DELETE;
l_price_type_tbl.DELETE;
l_primary_category_name_tbl.DELETE;
g_po_hdrs_int_rec.action := 'UPDATE';
g_po_hdrs_int_rec.action := 'UPDATE';
'inserting into headers_interface; total_hdr_count:' ||g_GBPA_hdr_count ||
insertPOHeadersInterface;
l_line_deleted := FALSE;
g_po_hdrs_int_rec.action := 'UPDATE';
'inserting into headers interface for delete of lines'||
', l_del_interface_header_id:' || l_del_interface_header_id ||
', g_po_hdrs_int_rec.action:' || g_po_hdrs_int_rec.action ||
', g_po_hdrs_int_rec.po_header_id:' || g_po_hdrs_int_rec.po_header_id ||
', created_language:' || g_po_hdrs_int_rec.created_language ||
', comments:' || g_po_hdrs_int_rec.comments ||
', g_po_hdrs_int_rec.cpa_reference:' || g_po_hdrs_int_rec.cpa_reference ||
', g_current_gbpa_hdr_rec.upg_cpa_reference:' || g_current_gbpa_hdr_rec.upg_cpa_reference ||
', g_current_gbpa_hdr_rec.cpa_reference:' || g_current_gbpa_hdr_rec.cpa_reference ||
', g_current_gbpa_hdr_rec.po_header_id:' ||g_current_gbpa_hdr_rec.po_header_id ||
', l_po_header_id_tbl:' || l_po_header_id_tbl(i) );
insertPOHeadersInterface;
g_po_line_attrval_int_rec.action := 'DELETE';
'inserting into lines interface with DELETE action' ||
', l_del_interface_line_id:' || l_del_interface_line_id ||
', po_line_id: ' || g_po_line_attrval_int_rec.po_line_id ||
', rt_item_id: ' || l_rt_item_id_tbl(i) ||
', l_po_category_id_tbl:' || l_po_category_id_tbl(i) ||
', l_old_po_category_id_tbl:' || l_old_po_category_id_tbl(i) );
insertPOLinesInterface;
l_line_deleted := TRUE;
l_line_deleted)
THEN
l_dml_reqd_in_lines := TRUE;
l_line_deleted := FALSE;
'inserting into r12 upgrade');
insertR12Upgrade;
IF (l_line_deleted) THEN
-- So that the po_line_id is null in po_attr_values_tlp_interface
-- when translation is inserted for a deleted line
-- (Note: the line was deleted due to source / category change)
g_r12_upg_rec.po_line_id := null;
updateR12Upgrade;
IF (l_po_line_id_tbl(i) IS NULL OR l_line_deleted) THEN
g_po_line_attrval_int_rec.action := 'ADD';
g_po_line_attrval_int_rec.action := 'UPDATE';
'inserting into lines and attr_values with action:' ||
g_po_line_attrval_int_rec.action ||
', l_rt_item_id_tbl(i): ' || l_rt_item_id_tbl(i) ||
', interface_line_id: ' || g_po_line_attrval_int_rec.interface_line_id ||
', interface_header_id: ' || g_po_line_attrval_int_rec.interface_header_id );
insertPOLinesInterface;
insertPOAttrValsInterface;
IF (l_po_line_id_tbl(i) IS NULL OR l_line_deleted) THEN
g_po_attrvalstlp_int_rec.action := 'ADD';
g_po_attrvalstlp_int_rec.check_desc_update := ICX_CAT_UTIL_PVT.g_update_description;
'inserting into attr_values_tlp with action:' || g_po_attrvalstlp_int_rec.action ||
', g_po_attrvalstlp_int_rec.po_line_id: ' || g_po_attrvalstlp_int_rec.po_line_id ||
', g_current_gbpa_hdr_rec.language: ' || g_current_gbpa_hdr_rec.language ||
', g_current_gbpa_hdr_rec.upg_created_language: ' || g_current_gbpa_hdr_rec.upg_created_language ||
', l_rt_item_id_tbl(i): ' || l_rt_item_id_tbl(i) ||
', interface_line_id: ' || g_po_attrvalstlp_int_rec.interface_line_id ||
', interface_header_id: ' || g_po_attrvalstlp_int_rec.interface_header_id );
insertPOAttrValsTLPInterface;
PROCEDURE checkUpdateInGBPAForDelta
IS
-- Added the decode for supplier and supplier_site_code, because of some corrupt data
-- that exists on the internal envs (also on gevt11i).
-- The details: supplier_site_id = -2 but supplier_site_code is not null
-- supplier_id = -2 but supplier is not null
-- note that here we need to do nvl for upg.cpa_reference
-- because for new lines the outer join to upg will not return any rows and hence upg.cpa_reference will be null
-- for these guys. but in the table (upg) the cpa_reference is not null (it is actually -2) and since we are
-- grouping these rows on upg.cpa_reference we need both to be -2
CURSOR checkUpdateInGBPAForDeltaCsr IS
SELECT doc.*,
COUNT(*) count
FROM (
SELECT src.org_id org_id, src.supplier_id supplier_id,
src.supplier_site_id supplier_site_id, src.currency currency,
src.contract_id gbpa_cpa_reference,
itemtlp.language language,
upg.po_interface_header_id,
upg.po_header_id, upg.created_language,
nvl(upg.cpa_reference, -2) upg_cpa_reference
FROM icx_cat_r12_upg_autosource src, icx_cat_item_prices price,
icx_cat_items_tlp itemtlp, icx_por_category_order_map map,
icx_cat_items_b itemb, icx_cat_ext_items_tlp extitemtlp,
icx_cat_r12_upgrade upg
WHERE price.price_type = 'BULKLOAD'
AND price.rt_item_id = itemtlp.rt_item_id
AND NOT EXISTS (SELECT 'extracted price'
FROM icx_cat_item_prices priceIn
WHERE priceIn.rt_item_id = price.rt_item_id
AND priceIn.price_type IN ('BLANKET', 'GLOBAL_AGREEMENT', 'QUOTATION',
'INTERNAL_TEMPLATE', 'TEMPLATE', 'ASL'))
AND itemtlp.primary_category_id = map.rt_category_id (+)
AND itemtlp.org_id = src.org_id
AND itemtlp.supplier_id = src.supplier_id
AND price.supplier_site_id = src.supplier_site_id
AND price.currency = src.currency
AND NVL(map.external_source_key, '-2') = src.po_category_id
AND price.rt_item_id = itemb.rt_item_id
AND price.rt_item_id = extitemtlp.rt_item_id
AND itemtlp.language = extitemtlp.language
AND price.rt_item_id = upg.rt_item_id (+)
AND price.supplier_site_id = upg.supplier_site_id (+)
AND price.currency = upg.currency (+)
AND price.contract_id = upg.price_contract_id (+)
AND ( -- Last update changes in items / price tables
(itemb.last_update_date > g_bulk_last_run_date
OR itemtlp.last_update_date > g_bulk_last_run_date
OR extitemtlp.last_update_date > g_bulk_last_run_date
OR price.last_update_date > g_bulk_last_run_date)
OR -- The items that errored out in the previous run
(upg.po_header_id is null
OR upg.po_line_id is null))
UNION ALL
-- Added NVL to org_id, supplier, site and currency
SELECT NVL(itemtlp.org_id, -2) org_id, NVL(itemtlp.supplier_id, -2) supplier_id,
NVL(price.supplier_site_id, -2) supplier_site_id, NVL(price.currency, '-2') currency,
price.contract_id gbpa_cpa_reference,
itemtlp.language language,
upg.po_interface_header_id,
upg.po_header_id, upg.created_language,
nvl(upg.cpa_reference, -2) upg_cpa_reference
FROM icx_cat_item_prices price, icx_cat_items_tlp itemtlp,
icx_cat_items_b itemb, icx_cat_ext_items_tlp extitemtlp,
icx_cat_r12_upgrade upg
WHERE price.price_type = 'CONTRACT'
AND price.rt_item_id = itemtlp.rt_item_id
AND NOT EXISTS (SELECT 'extracted price'
FROM icx_cat_item_prices priceIn
WHERE priceIn.rt_item_id = price.rt_item_id
AND priceIn.price_type IN ('BLANKET', 'GLOBAL_AGREEMENT', 'QUOTATION',
'INTERNAL_TEMPLATE', 'TEMPLATE', 'ASL'))
AND price.rt_item_id = itemb.rt_item_id
AND price.rt_item_id = extitemtlp.rt_item_id
AND itemtlp.language = extitemtlp.language
AND price.rt_item_id = upg.rt_item_id (+)
AND price.supplier_site_id = upg.supplier_site_id (+)
AND price.currency = upg.currency (+)
AND price.contract_id = upg.price_contract_id (+)
AND ( -- Last update changes in items / price tables
(itemb.last_update_date > g_bulk_last_run_date
OR itemtlp.last_update_date > g_bulk_last_run_date
OR extitemtlp.last_update_date > g_bulk_last_run_date
OR price.last_update_date > g_bulk_last_run_date)
OR -- The items that errored out in the previous run
(upg.po_header_id is null
OR upg.po_line_id is null))
) doc
GROUP BY doc.org_id, doc.supplier_id, doc.supplier_site_id,
doc.currency, doc.gbpa_cpa_reference,
doc.language,
doc.po_interface_header_id, doc.po_header_id,
doc.created_language, doc.upg_cpa_reference
ORDER BY doc.org_id, doc.supplier_id, doc.supplier_site_id,
doc.currency, doc.gbpa_cpa_reference,
doc.po_interface_header_id, count DESC, doc.language;
l_api_name CONSTANT VARCHAR2(30) := 'checkUpdateInGBPAForDelta';
OPEN checkUpdateInGBPAForDeltaCsr;
FETCH checkUpdateInGBPAForDeltaCsr BULK COLLECT INTO
l_org_id_tbl, l_supplier_id_tbl, l_supplier_site_id_tbl, l_currency_tbl,
l_gbpa_cpa_reference_tbl, l_language_tbl,
l_po_interface_header_id_tbl, l_po_header_id_tbl, l_upg_created_language_tbl,
l_upg_cpa_reference_tbl, l_count_tbl;
CLOSE checkUpdateInGBPAForDeltaCsr;
END checkUpdateInGBPAForDelta;
PROCEDURE checkDeleteInGBPAForDelta
IS
-- Reason for joining back to po_headers_all is:
-- pomigratecatalog code is dependant on the org_id being
-- populated in po_headers_interface for its processing
-- Reason for outer join with po_headers_all, because there may be items in
-- catalog that was never migrated due to errors, so they will have po_header_id as null
CURSOR getDeletedItemPricesInCatlgCsr IS
SELECT rt_item_id, po_interface_header_id, po_interface_line_id,
upg.po_header_id, po_line_id, ph.org_id
FROM icx_cat_r12_upgrade upg, po_headers_all ph
WHERE NOT EXISTS (SELECT 'x'
FROM icx_cat_items_b itemsB, icx_cat_item_prices prices
WHERE itemsB.rt_item_id = prices.rt_item_id
AND upg.rt_item_id = prices.rt_item_id
AND upg.supplier_site_id = prices.supplier_site_id
AND upg.currency = prices.currency
AND upg.price_contract_id = prices.contract_id)
/* NOT NEEDED IF WE UPDATE the prices table with contract_id = -2 for bulkload items.
(upg.contract_id IS NULL OR
prices.contract_id IS NULL OR
upg.contract_id = prices.contract_id) */
AND upg.po_header_id = ph.po_header_id (+)
-- Order by is done for inserting only one header into po_interface_headers
-- for all the lines to be deleted in a particular header.
ORDER BY upg.po_header_id;
l_api_name CONSTANT VARCHAR2(30) := 'checkDeleteInGBPAForDelta';
OPEN getDeletedItemPricesInCatlgCsr;
l_rt_item_id_tbl.DELETE;
l_po_interface_header_id_tbl.DELETE;
l_po_interface_line_id_tbl.DELETE;
l_po_header_id_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_org_id_tbl.DELETE;
FETCH getDeletedItemPricesInCatlgCsr BULK COLLECT INTO
l_rt_item_id_tbl, l_po_interface_header_id_tbl,
l_po_interface_line_id_tbl, l_po_header_id_tbl,
l_po_line_id_tbl, l_org_id_tbl
LIMIT ICX_CAT_UTIL_PVT.g_batch_size ;
g_po_hdrs_int_rec.action := 'UPDATE';
' --> inserting into headers interface' ||
', g_po_hdrs_int_rec.po_header_id: ' || g_po_hdrs_int_rec.po_header_id ||
', l_interface_header_id: ' || l_interface_header_id);
insertPOHeadersInterface;
g_po_line_attrval_int_rec.action := 'DELETE';
' --> inserting into lines interface' ||
', g_po_line_attrval_int_rec.po_header_id: ' || g_po_line_attrval_int_rec.po_header_id ||
', g_po_line_attrval_int_rec.po_line_id: ' || g_po_line_attrval_int_rec.po_line_id ||
', l_interface_header_id: ' || l_interface_header_id ||
', l_interface_line_id: ' || l_interface_line_id);
insertPOLinesInterface;
deleteR12Upgrade;
IF (getDeletedItemPricesInCatlgCsr%ISOPEN) THEN
CLOSE getDeletedItemPricesInCatlgCsr;
OPEN getDeletedItemPricesInCatlgCsr;
IF (getDeletedItemPricesInCatlgCsr%ISOPEN) THEN
CLOSE getDeletedItemPricesInCatlgCsr;
END checkDeleteInGBPAForDelta;
SELECT doc.po_header_id, COUNT(*)
FROM (SELECT po_interface_header_id, po_header_id
FROM icx_cat_r12_upgrade
WHERE po_header_id IS NOT NULL
GROUP BY po_interface_header_id, po_header_id) doc
GROUP BY po_header_id
HAVING COUNT(*) > 1;
SELECT rowid
FROM icx_cat_r12_upgrade
WHERE po_header_id IS NULL
AND po_interface_header_id IS NOT NULL;
UPDATE icx_cat_r12_upgrade
SET po_interface_header_id = l_upo_int_hdr_id_tbl(i)
WHERE po_header_id = l_upo_hdr_id_tbl(i);
'Number of rows updated in r12 upgrade with new interface_header_id ' || SQL%ROWCOUNT);
l_rowid_tbl.DELETE;
UPDATE icx_cat_r12_upgrade
SET po_interface_header_id = NULL,
created_language = NULL
WHERE rowid = l_rowid_tbl(i);
'No: of rows updated in icx_cat_r12_upgrade:' ||SQL%ROWCOUNT);
3. Check for item updated/translation added
Note: Steps 1, 2 and 3 will be done together using the following approach:
a) Outer join with icx_cat_r12_upgrade
b) Check for last_update_date > g_bulk_last_run_date in icx_cat_items_b, icx_cat_items_tlp,
icx_cat_ext_items_tlp and icx_cat_item_prices
4. Check for items deleted i.e. rt_item_id exists in icx_cat_r12_upgrade but not in icx_cat_items_b
5. Check for price deleted i.e. price_rowid exists in icx_cat_r12_upgrade but not in icx_cat_items_b
6. Check for any pricing hdr attribute (i.e. supplier_site_id, currency, contract_id) update after running pre-upgrade.
Note: Steps 4, 5 and 6 will be done together using the following approach:
a) Get all the records from icx_cat_r12_upgrade that don't exist in icx_cat_items_b and icx_cat_item_prices
based on rt_item_id, supplier_site_id, currency, contract_id
7. Check for items that have errors i.e. the ones that were not migrated into po tables due to validation errors,
These will have po_interface_header_id and po_interface_line_id populated but will have null po_header_id and po_line_id
Assumptions: Translations cannot be deleted.
*/
-- First we will sync up all the po_interface_header_ids
-- in icx_cat_r12_upgrade which belongs to the same po_header_id but exists with
-- different po_interface_header_id in icx_cat_r12_upgrade
-- Reason being, lines belonging to the same po_header where processed at two different upgrade delta jobs.
syncPOIntHdrIdInR12UpgTbl;
checkDeleteInGBPAForDelta; --Checks for Steps 4, 5 and 6.
checkUpdateInGBPAForDelta; --Checks for Steps 1, 2, 3 and 7.
SELECT rowid, rt_item_id
FROM icx_cat_item_prices
WHERE rt_item_id >= p_rt_item_id
AND price_type in ('BULKLOAD', 'CONTRACT')
AND contract_id IS NULL;
l_rowid_tbl.DELETE;
l_rt_item_id_tbl.DELETE;
UPDATE icx_cat_item_prices
SET contract_id = -2
WHERE rowid = l_rowid_tbl(i);
'No: of rows updated in icx_cat_item_prices:' ||SQL%ROWCOUNT);
ICX_CAT_UTIL_PVT.g_job_pdoi_update_date := NULL;
PROCEDURE updatePOHeaderId
( p_interface_header_id IN DBMS_SQL.NUMBER_TABLE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'updatePOHeaderId';
UPDATE icx_cat_r12_upgrade upg
SET (po_header_id, created_language) =
(SELECT po_header_id, created_language
FROM po_headers_interface phi
WHERE phi.interface_header_id = upg.po_interface_header_id)
WHERE upg.po_interface_header_id = p_interface_header_id(i);
'No: of header rows updated in icx_cat_r12_upgrade table:' || l_row_count ||
', g_job_pdoi_update_date:' || ICX_CAT_UTIL_PVT.g_job_pdoi_update_date);
ICX_CAT_UTIL_PVT.g_job_pdoi_update_date IS NULL)
THEN
ICX_CAT_UTIL_PVT.g_job_pdoi_update_date := sysdate;
updateR12UpgradeJob(ICX_CAT_UTIL_PVT.g_job_paused_status);
', g_job_pdoi_update_date set to:' || ICX_CAT_UTIL_PVT.g_job_pdoi_update_date);
END updatePOHeaderId;
PROCEDURE updatePOLineId
( p_interface_line_id IN DBMS_SQL.NUMBER_TABLE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'updatePOLineId';
UPDATE icx_cat_r12_upgrade upg
SET po_line_id =
(SELECT po_line_id
FROM po_lines_interface pli
WHERE pli.interface_line_id = upg.po_interface_line_id)
WHERE upg.po_interface_line_id = p_interface_line_id(i);
'No: of line rows updated in icx_cat_r12_upgrade table:' || l_row_count ||
', g_job_pdoi_update_date:' || ICX_CAT_UTIL_PVT.g_job_pdoi_update_date);
ICX_CAT_UTIL_PVT.g_job_pdoi_update_date IS NULL)
THEN
ICX_CAT_UTIL_PVT.g_job_pdoi_update_date := sysdate;
updateR12UpgradeJob(ICX_CAT_UTIL_PVT.g_job_paused_status);
', g_job_pdoi_update_date set to:' || ICX_CAT_UTIL_PVT.g_job_pdoi_update_date);
END updatePOLineId;
INSERT INTO icx_cat_fav_list_headers
(
employee_id, favorite_list_id, favorite_list_name,
last_update_date, last_updated_by, last_update_login,
creation_date, created_by,
description, inactive_date, attribute_category,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
primary_flag
)
SELECT
employee_id, favorite_list_id, favorite_list_name,
last_update_date, last_updated_by, last_update_login,
NVL(creation_date, last_update_date), NVL(created_by, last_updated_by),
description, inactive_date, attribute_category,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
primary_flag
FROM por_favorite_list_headers old_fav_hdrs
WHERE NOT EXISTS (SELECT NULL
FROM icx_cat_fav_list_headers new_fav_hdrs
WHERE new_fav_hdrs.employee_id = old_fav_hdrs.employee_id
AND new_fav_hdrs.favorite_list_id = old_fav_hdrs.favorite_list_id);
'No: of rows inserted into icx_cat_fav_list_headers during upgrade:' || SQL%ROWCOUNT);
UPDATE icx_cat_fav_list_headers favout
SET primary_flag = 'Y'
WHERE primary_flag is null
AND favorite_list_name = 'POR_FAVORITE_LIST'
AND NOT EXISTS (SELECT 'x' FROM icx_cat_fav_list_headers favin
WHERE favin.employee_id = favout.employee_id
AND favin.primary_flag = 'Y')
AND ROWNUM <= ICX_CAT_UTIL_PVT.g_batch_size;
'No: of rows updated in icx_cat_fav_list_headers for primary_flag:' || l_row_count);
SELECT fav.rowid, upg.po_line_id
FROM por_favorite_list_lines fav,
icx_cat_item_prices price,
icx_cat_r12_upgrade upg
WHERE fav.rt_item_id IS NOT NULL
AND fav.price_list_id IS NOT NULL
AND fav.source_doc_line_id IS NULL
AND fav.rt_item_id = price.rt_item_id
AND fav.price_list_id = price.price_list_id
AND fav.suggested_vendor_site_id = price.supplier_site_id
AND price.rt_item_id = upg.rt_item_id
AND price.supplier_site_id = upg.supplier_site_id
AND price.currency = upg.currency
AND price.contract_id = upg.price_contract_id
AND upg.po_line_id IS NOT NULL;
SELECT favorite_list_line_id, source_doc_line_id, template_name,
template_line_num, item_id, asl_id, rt_item_id
FROM por_favorite_list_lines
WHERE duplicate_in_r12 IS NULL
AND item_type IS NULL
OR (item_type = 'CATALOG'
AND (source_doc_line_id IS NULL
OR template_name IS NULL
OR item_id IS NULL
OR asl_id IS NULL));
SELECT fav.favorite_list_id, fav.item_id, fav.favorite_list_line_id
FROM
( SELECT favorite_list_id, item_id, duplicate_in_r12, COUNT(*)
FROM
( SELECT favorite_list_id, item_id, duplicate_in_r12
FROM por_favorite_list_lines fav_out
WHERE fav_out.item_type = 'CATALOG'
AND asl_id <> -2
UNION ALL
SELECT favorite_list_id, item_id, duplicate_in_r12
FROM por_favorite_list_lines fav_out
WHERE item_type = 'CATALOG'
AND asl_id = -2 AND source_doc_line_id = -2 AND template_name = '-2'
AND EXISTS (SELECT 'x' FROM por_favorite_list_lines fav_in
WHERE asl_id <> -2 AND item_type = 'CATALOG'
AND fav_in.item_id = fav_out.item_id
AND fav_in.favorite_list_id = fav_out.favorite_list_id)
)
GROUP BY favorite_list_id, item_id, duplicate_in_r12
HAVING COUNT(*) > 1
) dupe, por_favorite_list_lines fav
WHERE fav.favorite_list_id = dupe.favorite_list_id
AND fav.item_id = dupe.item_id
ORDER BY fav.favorite_list_id, fav.item_id;
SELECT favorite_list_id, favorite_list_line_id,
fav.source_doc_line_id, fav.template_name,
fav.template_line_num, fav.item_id,
items.source_type, items.language, items.org_id
FROM por_favorite_list_lines fav, icx_cat_items_ctx_hdrs_tlp items
WHERE fav.item_type = 'CATALOG'
AND fav.duplicate_in_r12 IS NULL
AND fav.source_doc_line_id = items.po_line_id
AND fav.template_name = items.req_template_name
AND fav.template_line_num = items.req_template_line_num
AND fav.item_id = items.inventory_item_id
AND NOT EXISTS ( SELECT NULL
FROM icx_cat_fav_list_lines_tlp new_fav
WHERE new_fav.favorite_list_id = fav.favorite_list_id
AND new_fav.favorite_list_line_id = fav.favorite_list_line_id)
ORDER BY favorite_list_id, favorite_list_line_id, fav.source_doc_line_id,
fav.template_name, fav.template_line_num, fav.item_id,
items.source_type;
SELECT fav_hdrs.favorite_list_id, fav_hdrs.employee_id, users.user_id,
prf_vals.profile_option_value, COUNT(*)
FROM por_favorite_list_headers fav_hdrs, fnd_user users,
fnd_profile_option_values prf_vals, por_favorite_list_lines fav_lines
WHERE fav_hdrs.favorite_list_id = fav_lines.favorite_list_id
AND fav_lines.item_type <> 'CATALOG'
AND fav_hdrs.employee_id = users.employee_id (+)
AND users.user_id = prf_vals.level_value (+)
AND prf_vals.profile_option_id (+) = p_profile_option_id
AND prf_vals.level_id (+) = 10004
AND NOT EXISTS ( SELECT NULL
FROM icx_cat_fav_list_lines_tlp new_fav
WHERE new_fav.favorite_list_id = fav_lines.favorite_list_id
AND new_fav.favorite_list_line_id = fav_lines.favorite_list_line_id )
GROUP BY fav_hdrs.favorite_list_id, fav_hdrs.employee_id, users.user_id,
prf_vals.profile_option_value
ORDER BY fav_hdrs.favorite_list_id, fav_hdrs.employee_id, users.user_id;
SELECT DISTINCT NVL(resp_profile.profile_option_value,
NVL(app_profile.profile_option_value, -2)) org_id
FROM fnd_responsibility resp,
fnd_profile_option_values resp_profile,
fnd_profile_option_values app_profile,
fnd_user_resp_groups_all user_resp
WHERE user_resp.user_id = p_user_id
AND user_resp.responsibility_application_id IN (177, 178, 201, 396, 426)
AND user_resp.responsibility_id = resp.responsibility_id
AND user_resp.responsibility_application_id = resp.application_id
AND app_profile.profile_option_id(+) = p_profile_option_id
AND app_profile.level_id(+) = 10002
AND app_profile.level_value(+) = resp.application_id
AND resp_profile.profile_option_id(+) = p_profile_option_id
AND resp_profile.level_id(+) = 10003
AND resp_profile.level_value(+) = resp.responsibility_id
ORDER BY 1;
SELECT favorite_list_line_id
FROM por_favorite_list_lines fav_lines
WHERE fav_lines.favorite_list_id = p_favorite_list_id
AND fav_lines.item_type <> 'CATALOG';
l_rowid_tbl.DELETE;
l_po_line_id_tbl.DELETE;
UPDATE por_favorite_list_lines
SET source_doc_line_id = l_po_line_id_tbl(i)
WHERE rowid = l_rowid_tbl(i);
'No: of rows updated in por_favorite_list_lines for bulk loaded items:' ||SQL%ROWCOUNT);
l_favorite_list_line_id_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_req_template_name_tbl.DELETE;
l_req_template_line_num_tbl.DELETE;
l_inventory_item_id_tbl.DELETE;
l_asl_id_tbl.DELETE;
l_rt_item_id_tbl.DELETE;
l_item_type_tbl.DELETE;
UPDATE por_favorite_list_lines
SET item_type = l_item_type_tbl(i),
source_doc_line_id = l_po_line_id_tbl(i),
template_name = l_req_template_name_tbl(i),
template_line_num = l_req_template_line_num_tbl(i),
item_id = l_inventory_item_id_tbl(i),
asl_id = l_asl_id_tbl(i)
WHERE favorite_list_line_id = l_favorite_list_line_id_tbl(i);
'No: of rows updated in por_favorite_list_lines for item_type:' ||SQL%ROWCOUNT);
l_favorite_list_line_id_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_req_template_name_tbl.DELETE;
l_req_template_line_num_tbl.DELETE;
l_inventory_item_id_tbl.DELETE;
l_asl_id_tbl.DELETE;
l_rt_item_id_tbl.DELETE;
l_item_type_tbl.DELETE;
l_favorite_list_id_tbl.DELETE;
l_inventory_item_id_tbl.DELETE;
l_favorite_list_line_id_tbl.DELETE;
UPDATE por_favorite_list_lines
SET duplicate_in_r12 = 'Y'
WHERE favorite_list_line_id = l_dupe_fav_list_line_id_tbl(i);
'No: of rows updated in por_favorite_list_lines for duplicates:' ||SQL%ROWCOUNT);
l_dupe_fav_list_line_id_tbl.DELETE;
l_favorite_list_id_tbl.DELETE;
l_inventory_item_id_tbl.DELETE;
l_favorite_list_line_id_tbl.DELETE;
l_favorite_list_id_tbl.DELETE;
l_favorite_list_line_id_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_req_template_name_tbl.DELETE;
l_req_template_line_num_tbl.DELETE;
l_inventory_item_id_tbl.DELETE;
l_source_type_tbl.DELETE;
l_language_tbl.DELETE;
l_org_id_tbl.DELETE;
l_favorite_list_id_tbl.DELETE;
l_favorite_list_line_id_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_req_template_name_tbl.DELETE;
l_req_template_line_num_tbl.DELETE;
l_inventory_item_id_tbl.DELETE;
l_source_type_tbl.DELETE;
l_language_tbl.DELETE;
l_org_id_tbl.DELETE;
l_language_tbl.DELETE;
SELECT language_code
BULK COLLECT INTO l_language_tbl
FROM fnd_languages
WHERE installed_flag IN ('B', 'I')
ORDER BY installed_flag;
SELECT profile_option_id
INTO l_profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = 'ORG_ID';
l_favorite_list_id_tbl.DELETE;
l_employee_id_tbl.DELETE;
l_user_id_tbl.DELETE;
l_profile_option_value_tbl.DELETE;
l_count_tbl.DELETE;
l_org_id_tbl.DELETE;
SELECT profile_option_value
INTO l_site_level_prf_opt_val
FROM fnd_profile_option_values
WHERE profile_option_id = l_profile_option_id
AND level_id = 10001;
l_org_id_tbl.DELETE;
l_favorite_list_id_tbl.DELETE;
l_employee_id_tbl.DELETE;
l_user_id_tbl.DELETE;
l_profile_option_value_tbl.DELETE;
l_count_tbl.DELETE;
l_favorite_list_id_tbl.DELETE;
l_employee_id_tbl.DELETE;
DELETE FROM icx_cat_fav_list_headers hdrs
WHERE NOT EXISTS ( SELECT 'x' FROM icx_cat_fav_list_lines_tlp lines
WHERE lines.favorite_list_id = hdrs.favorite_list_id)
AND ROWNUM <= ICX_CAT_UTIL_PVT.g_batch_size
RETURNING favorite_list_id, employee_id
BULK COLLECT INTO l_favorite_list_id_tbl, l_employee_id_tbl;
'No: of rows deleted from icx_cat_fav_list_headers for which there are no lines:' || l_row_count);
SELECT 0, status, domidx_status, domidx_opstatus
INTO l_index_exists, l_status, l_domidx_status, l_domidx_opstatus
FROM all_indexes
WHERE index_name = 'ICX_CAT_ITEMSCTXDESC_HDRS'
AND owner = l_icx_schema_name;
updateR12UpgradeJob(ICX_CAT_UTIL_PVT.g_job_current_status);
updateR12UpgradeJob(ICX_CAT_UTIL_PVT.g_job_failed_status);
SELECT 1
INTO l_temp
FROM icx_cat_descriptors_tl
WHERE UPPER(key) = l_newKey;
SELECT rt_descriptor_id, UPPER(key)
FROM icx_cat_descriptors_tl
WHERE UPPER(key) IN ('SOURCE',
'SHOPPING_CATEGORY',
'PURCHASING_CATEGORY',
'ITEM_REVISION')
AND language = source_lang
AND rt_descriptor_id > 100;
SELECT attribute_id, key
FROM icx_cat_attributes_tl
WHERE key IN ('PURCHASING_CATEGORY',
'THUMBNAIL_IMAGE',
'SUPPLIER_SITE',
'PICTURE',
'UOM',
'PRICE',
'CURRENCY',
'FUNCTIONAL_PRICE',
'FUNCTIONAL_CURRENCY',
'ATTACHMENT_URL',
'SUPPLIER_URL',
'MANUFACTURER_URL')
AND searchable = 1
AND rt_category_id = 0
AND language = ICX_CAT_UTIL_PVT.g_base_language;
SELECT 1
INTO l_is_attr_already_upgraded
FROM dual
WHERE EXISTS (SELECT 'attribute records'
FROM icx_cat_attributes_tl
WHERE attribute_id > 100);
l_rt_descriptor_id_tbl.DELETE;
l_key_tbl.DELETE;
UPDATE icx_cat_descriptors_tl
SET key = l_upd_key_tbl(i)
WHERE rt_descriptor_id = l_upd_rt_descriptor_id_tbl(i);
INSERT INTO icx_cat_attributes_tl
(
attribute_id, language, source_lang,
attribute_name, description, rt_category_id, type,
sequence, key,
searchable, search_results_visible, item_detail_visible,
created_by, creation_date,
last_updated_by, last_update_login, last_update_date,
request_id, program_application_id, program_id,
rebuild_flag, section_tag,
stored_in_table, stored_in_column
)
(
SELECT
des1.rt_descriptor_id, des1.language, des1.source_lang,
des1.descriptor_name, des1.description, des1.rt_category_id, des1.type,
des1.sequence, des1.key,
des1.searchable, des1.search_results_visible, des1.item_detail_visible,
des1.created_by, des1.creation_date,
des1.last_updated_by, des1.last_update_login, des1.last_update_date,
des1.batch_job_num, des1.program_application_id, des1.program_id,
des1.rebuild_flag, des1.section_tag,
DECODE(des1.type, 2, 'PO_ATTRIBUTE_VALUES_TLP', 'PO_ATTRIBUTE_VALUES'),
des1.stored_in_column
FROM
icx_cat_descriptors_tl des1
WHERE des1.rt_descriptor_id > 100
AND NOT EXISTS (SELECT NULL FROM icx_cat_attributes_tl des2
WHERE des1.rt_descriptor_id = des2.attribute_id
AND des1.language = des2.language)
);
'No: of rows inserted into icx_cat_attributes_tl during upgrade:' || SQL%ROWCOUNT);
l_attribute_id_tbl.DELETE;
l_key_tbl.DELETE;
UPDATE icx_cat_attributes_tl
SET searchable = 0
WHERE attribute_id = l_attribute_id_tbl(i);
'No: of rows updated with searchable=0 in icx_cat_attributes_tl during upgrade:' || SQL%ROWCOUNT);
SELECT NVL(MAX(batch_id), 0) + 1
INTO g_PDOI_batch_id
FROM po_headers_interface;
SELECT NVL(MAX(PREUPG_PDOI_COMPLETE_DATE), NULL) extract_last_run_date,
NVL(MAX(preupg_pdoi_update_date), NULL) bulk_last_run_date,
NVL(MAX(preupg_bpa_complete_date), NULL) bpa_last_run_date,
NVL(MAX(preupg_quote_complete_date), NULL) quote_last_run_date,
NVL(MAX(preupg_reqtmplt_complete_date), NULL) reqtmplt_last_run_date,
NVL(MAX(preupg_mi_complete_date), NULL) mi_last_run_date
INTO g_extract_last_run_date,
g_bulk_last_run_date,
g_bpa_last_run_date,
g_quote_last_run_date,
g_reqtmplt_last_run_date,
g_mi_last_run_date
FROM icx_cat_r12_upgrade_jobs
-- Check within jobs that are not data exception
-- OR child data exception process ( which will be submitted from pre-upgrade program)
WHERE job_type NOT IN (ICX_CAT_UTIL_PVT.g_data_exception_program,
ICX_CAT_UTIL_PVT.g_child_data_excptn_program);
updateR12UpgradeJob(ICX_CAT_UTIL_PVT.g_job_running_status);
SELECT fpov.profile_option_value
INTO l_relevance_profile_value
FROM fnd_profile_options fpo, fnd_profile_option_values fpov
WHERE fpo.profile_option_name = 'POR_SORT_BY_RELEVANCE'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.level_id = 10001;
SELECT application_id, profile_option_id
INTO l_application_id, l_profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = 'POR_DEFAULT_SHOPPING_SORT';
INSERT INTO fnd_profile_option_values
(application_id, profile_option_id, level_id, level_value, last_update_date,
last_updated_by, creation_date, created_by, last_update_login, profile_option_value)
SELECT l_application_id, l_profile_option_id, 10001, 0, sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id, fnd_global.login_id, 'Relevance'
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM fnd_profile_option_values
WHERE application_id = l_application_id
AND profile_option_id = l_profile_option_id
AND level_id = 10001
AND level_value = 0);
SELECT application_id, profile_option_id
INTO l_application_id, l_profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = 'POR_DEFAULT_SHOPPING_SORT_ORDER';
INSERT INTO fnd_profile_option_values
(application_id, profile_option_id, level_id, level_value, last_update_date,
last_updated_by, creation_date, created_by, last_update_login, profile_option_value)
SELECT l_application_id, l_profile_option_id, 10001, 0, sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id, fnd_global.login_id, 'DESC'
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM fnd_profile_option_values
WHERE application_id = l_application_id
AND profile_option_id = l_profile_option_id
AND level_id = 10001
AND level_value = 0);
updateR12UpgradeJob(ICX_CAT_UTIL_PVT.g_job_current_status);
updateR12UpgradeJob(ICX_CAT_UTIL_PVT.g_job_failed_status);
ICX_CAT_R12_UPGRADE_PVT.updateR12UpgradeJob(ICX_CAT_UTIL_PVT.g_job_complete_status);
updateR12UpgradeJob(ICX_CAT_UTIL_PVT.g_job_failed_status);
INSERT INTO icx_cat_r12_upgrade_jobs
(job_type, job_number, status, run_date, audsid, pdoi_batch_id,
last_update_login, last_updated_by, last_update_date,
created_by, creation_date, internal_request_id, request_id,
program_application_id, program_id, program_login_id)
SELECT ICX_CAT_UTIL_PVT.g_job_type, ICX_CAT_UTIL_PVT.g_job_number,
ICX_CAT_UTIL_PVT.g_job_running_status, sysdate, p_audsid, p_pdoi_batch_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id, sysdate,
ICX_CAT_UTIL_PVT.g_who_columns_rec.user_id, sysdate,
ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id
FROM DUAL
WHERE NOT EXISTS (SELECT 'x' FROM icx_cat_r12_upgrade_jobs
WHERE job_type = ICX_CAT_UTIL_PVT.g_job_type
AND job_number = ICX_CAT_UTIL_PVT.g_job_number );
PROCEDURE updateR12UpgradeJob
( p_job_status IN VARCHAR2 ,
p_audsid2 IN NUMBER DEFAULT NULL
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'updateR12UpgradeJob';
UPDATE icx_cat_r12_upgrade_jobs
SET status = p_job_status,
audsid = g_audsid,
pdoi_batch_id = g_PDOI_batch_id,
last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
last_updated_by = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
last_update_date = sysdate,
internal_request_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id,
request_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id,
program_application_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
program_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
program_login_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id
WHERE job_type = ICX_CAT_UTIL_PVT.g_job_type
AND job_number = ICX_CAT_UTIL_PVT.g_job_number;
UPDATE icx_cat_r12_upgrade_jobs
SET preupg_pdoi_update_date = ICX_CAT_UTIL_PVT.g_job_pdoi_update_date,
preupg_pdoi_complete_date = ICX_CAT_UTIL_PVT.g_job_pdoi_complete_date
WHERE job_type = ICX_CAT_UTIL_PVT.g_job_type
AND job_number = ICX_CAT_UTIL_PVT.g_job_number;
UPDATE icx_cat_r12_upgrade_jobs
SET status = p_job_status,
audsid2 = p_audsid2,
preupg_complete_date = ICX_CAT_UTIL_PVT.g_job_complete_date,
preupg_pdoi_update_date = NVL(ICX_CAT_UTIL_PVT.g_job_pdoi_update_date, preupg_pdoi_update_date),
preupg_pdoi_complete_date = NVL(ICX_CAT_UTIL_PVT.g_job_pdoi_complete_date, preupg_pdoi_complete_date),
preupg_bpa_complete_date = ICX_CAT_UTIL_PVT.g_job_bpa_complete_date,
preupg_quote_complete_date = ICX_CAT_UTIL_PVT.g_job_quote_complete_date,
preupg_reqtmplt_complete_date = ICX_CAT_UTIL_PVT.g_job_reqtmplt_complete_date,
preupg_mi_complete_date = ICX_CAT_UTIL_PVT.g_job_mi_complete_date
WHERE job_type = ICX_CAT_UTIL_PVT.g_job_type
AND job_number = ICX_CAT_UTIL_PVT.g_job_number;
'; No: of rows updated in icx_cat_r12_upgrade_jobs:' || l_row_count);
END updateR12UpgradeJob;