[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF (p_action_mode IN ('ALL', 'INSERT_CTX_HDRS', 'INSERT_ATTR_VALUES', 'INSERT_ATTR_VALUES_TLP')) THEN
l_err_loc := 200;
gIHInventoryItemIdTbl.DELETE;
gIHPoLineIdTbl.DELETE;
gIHReqTemplateNameTbl.DELETE;
gIHReqTemplateLineNumTbl.DELETE;
gIHOrgIdTbl.DELETE;
gIHLanguageTbl.DELETE;
gIHSourceTypeTbl.DELETE;
gIHItemTypeTbl.DELETE;
gIHPurchasingOrgIdTbl.DELETE;
gIHOwningOrgIdTbl.DELETE;
gIHIpCategoryIdTbl.DELETE;
gIHIpCategoryNameTbl.DELETE;
gIHPoCategoryIdTbl.DELETE;
gIHSupplierIdTbl.DELETE;
gIHSupplierPartNumTbl.DELETE;
gIHSupplierPartAuxidTbl.DELETE;
gIHSupplierSiteIdTbl.DELETE;
gIHReqTemplatePoLineIdTbl.DELETE;
gIHItemRevisionTbl.DELETE;
gIHPoHeaderIdTbl.DELETE;
gIHDocumentNumberTbl.DELETE;
gIHLineNumTbl.DELETE;
gIHAllowPriceOverrideFlagTbl.DELETE;
gIHNotToExceedPriceTbl.DELETE;
gIHLineTypeIdTbl.DELETE;
gIHUnitMeasLookupCodeTbl.DELETE;
gIHSuggestedQuantityTbl.DELETE;
gIHUnitPriceTbl.DELETE;
gIHAmountTbl.DELETE;
gIHCurrencyCodeTbl.DELETE;
gIHRateTypeTbl.DELETE;
gIHRateDateTbl.DELETE;
gIHRateTbl.DELETE;
gIHBuyerIdTbl.DELETE;
gIHSupplierContactIdTbl.DELETE;
gIHRfqRequiredFlagTbl.DELETE;
gIHNegotiatedByPreparerFlagTbl.DELETE;
gIHDescriptionTbl.DELETE;
gIHLongDescriptionTbl.DELETE;
gIHOrganizationIdTbl.DELETE;
gIHMasterOrganizationIdTbl.DELETE;
gIHOrderTypeLookupCodeTbl.DELETE;
gIHSupplierTbl.DELETE;
gIHGlobalAgreementFlagTbl.DELETE;
gIHMergedSourceTypeTbl.DELETE;
IF (p_action_mode IN ('ALL', 'INSERT_CTX_DTLS')) THEN
l_err_loc := 400;
gIDInventoryItemIdTbl.DELETE;
gIDPoLineIdTbl.DELETE;
gIDReqTemplateNameTbl.DELETE;
gIDReqTemplateLineNumTbl.DELETE;
gIDOrgIdTbl.DELETE;
gIDLanguageTbl.DELETE;
gIDPurchasingOrgIdTbl.DELETE;
gIDOwningOrgIdTbl.DELETE;
IF (p_action_mode IN ('ALL', 'UPDATE_CTX_HDRS', 'INSERT_TO_UPDATE_ATTR_VALUES', 'INSERT_TO_UPDATE_ATTR_VALUES_TLP')) THEN
l_err_loc := 600;
gUHInventoryItemIdTbl.DELETE;
gUHPoLineIdTbl.DELETE;
gUHReqTemplateNameTbl.DELETE;
gUHReqTemplateLineNumTbl.DELETE;
gUHOrgIdTbl.DELETE;
gUHLanguageTbl.DELETE;
gUHSourceTypeTbl.DELETE;
gUHItemTypeTbl.DELETE;
gUHPurchasingOrgIdTbl.DELETE;
gUHIpCategoryIdTbl.DELETE;
gUHIpCategoryNameTbl.DELETE;
gUHPoCategoryIdTbl.DELETE;
gUHSupplierIdTbl.DELETE;
gUHSupplierPartNumTbl.DELETE;
gUHSupplierPartAuxidTbl.DELETE;
gUHSupplierSiteIdTbl.DELETE;
gUHReqTemplatePoLineIdTbl.DELETE;
gUHItemRevisionTbl.DELETE;
gUHPoHeaderIdTbl.DELETE;
gUHDocumentNumberTbl.DELETE;
gUHLineNumTbl.DELETE;
gUHAllowPriceOverrideFlagTbl.DELETE;
gUHNotToExceedPriceTbl.DELETE;
gUHLineTypeIdTbl.DELETE;
gUHUnitMeasLookupCodeTbl.DELETE;
gUHSuggestedQuantityTbl.DELETE;
gUHUnitPriceTbl.DELETE;
gUHAmountTbl.DELETE;
gUHCurrencyCodeTbl.DELETE;
gUHRateTypeTbl.DELETE;
gUHRateDateTbl.DELETE;
gUHRateTbl.DELETE;
gUHBuyerIdTbl.DELETE;
gUHSupplierContactIdTbl.DELETE;
gUHRfqRequiredFlagTbl.DELETE;
gUHNegotiatedByPreparerFlagTbl.DELETE;
gUHDescriptionTbl.DELETE;
gUHLongDescriptionTbl.DELETE;
gUHOrganizationIdTbl.DELETE;
gUHMasterOrganizationIdTbl.DELETE;
gUHOrderTypeLookupCodeTbl.DELETE;
gUHSupplierTbl.DELETE;
gUHGlobalAgreementFlagTbl.DELETE;
gUHMergedSourceTypeTbl.DELETE;
IF (p_action_mode IN ('ALL', 'DELETE_CTX_HDRS', 'DELETE_ATTR_VALUES', 'DELETE_ATTR_VALUES_TLP')) THEN
l_err_loc := 800;
gDHInventoryItemIdTbl.DELETE;
gDHPoLineIdTbl.DELETE;
gDHReqTemplateNameTbl.DELETE;
gDHReqTemplateLineNumTbl.DELETE;
gDHOrgIdTbl.DELETE;
gDHLanguageTbl.DELETE;
IF (p_action_mode IN ('ALL', 'DELETE_CTX_DTLS')) THEN
l_err_loc := 1000;
gDDInventoryItemIdTbl.DELETE;
gDDPoLineIdTbl.DELETE;
gDDReqTemplateNameTbl.DELETE;
gDDReqTemplateLineNumTbl.DELETE;
gDDOrgIdTbl.DELETE;
gDDLanguageTbl.DELETE;
IF (p_action_mode IN ('ALL', 'DELETE_SPECIFIC_CTX_DTLS')) THEN
l_err_loc := 1200;
gDBLDInventoryItemIdTbl.DELETE;
gDBLDPoLineIdTbl.DELETE;
gDBLDReqTemplateNameTbl.DELETE;
gDBLDReqTemplateLineNumTbl.DELETE;
gDBLDOrgIdTbl.DELETE;
gDBLDOwningOrgIdTbl.DELETE;
gDBLDLanguageTbl.DELETE;
IF (p_action_mode IN ('ALL', 'DELETE_MANDATORY_ROW_CTX_DTLS')) THEN
l_err_loc := 1400;
gDMDInventoryItemIdTbl.DELETE;
gDMDPoLineIdTbl.DELETE;
gDMDReqTemplateNameTbl.DELETE;
gDMDReqTemplateLineNumTbl.DELETE;
gDMDOrgIdTbl.DELETE;
gDMDOwningOrgIdTbl.DELETE;
gDMDLanguageTbl.DELETE;
IF (p_action_mode IN ('ALL', 'DELETE_SUPPLIER_ROW_CTX_DTLS')) THEN
l_err_loc := 1600;
gDSDInventoryItemIdTbl.DELETE;
gDSDPoLineIdTbl.DELETE;
gDSDReqTemplateNameTbl.DELETE;
gDSDReqTemplateLineNumTbl.DELETE;
gDSDOrgIdTbl.DELETE;
gDSDOwningOrgIdTbl.DELETE;
gDSDLanguageTbl.DELETE;
IF (p_action_mode IN ('ALL', 'DELETE_ITEMREV_ROW_CTX_DTLS')) THEN
l_err_loc := 1800;
gDIRDInventoryItemIdTbl.DELETE;
gDIRDPoLineIdTbl.DELETE;
gDIRDReqTemplateNameTbl.DELETE;
gDIRDReqTemplateLineNumTbl.DELETE;
gDIRDOrgIdTbl.DELETE;
gDIRDOwningOrgIdTbl.DELETE;
gDIRDLanguageTbl.DELETE;
IF (p_action_mode IN ('ALL', 'DELETE_SHOPCATG_ROW_CTX_DTLS')) THEN
l_err_loc := 2000;
gDSCDInventoryItemIdTbl.DELETE;
gDSCDPoLineIdTbl.DELETE;
gDSCDReqTemplateNameTbl.DELETE;
gDSCDReqTemplateLineNumTbl.DELETE;
gDSCDOrgIdTbl.DELETE;
gDSCDOwningOrgIdTbl.DELETE;
gDSCDLanguageTbl.DELETE;
IF (p_action_mode IN ('ALL', 'DELETE_PURCHORG_ROW_CTX_DTLS')) THEN
l_err_loc := 2200;
gDPODInventoryItemIdTbl.DELETE;
gDPODPoLineIdTbl.DELETE;
gDPODReqTemplateNameTbl.DELETE;
gDPODReqTemplateLineNumTbl.DELETE;
gDPODOrgIdTbl.DELETE;
gDPODOwningOrgIdTbl.DELETE;
gDPODPurchasingOrgIdTbl.DELETE;
gDPODLanguageTbl.DELETE;
IF (p_action_mode = 'INSERT_CTX_HDRS') THEN
--INSERT icx_cat_items_ctx_hdrs_tlp
l_string := l_string || ' gIHInventoryItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gIHInventoryItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'INSERT_CTX_DTLS') THEN
--INSERT icx_cat_items_ctx_dtl_tlp
l_string := l_string || ' gIDInventoryItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gIDInventoryItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'UPDATE_CTX_HDRS') THEN
--UPDATE icx_cat_items_ctx_hdrs_tlp
l_string := l_string || ' gUHInventoryItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gUHInventoryItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'DELETE_CTX_HDRS') THEN
--DELETE icx_cat_items_ctx_hdrs_tlp
l_string := l_string || ' gDHInventoryItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gDHInventoryItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'DELETE_CTX_DTLS') THEN
--DELETE icx_cat_items_ctx_dtl_tlp
l_string := l_string || ' gDDInventoryItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gDDInventoryItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'DELETE_MANDATORY_ROW_CTX_DTLS') THEN
--DELETE rows with sequence = 1 in icx_cat_items_ctx_dtl_tlp
l_string := l_string || ' gDMDInventoryItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gDMDInventoryItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'DELETE_SUPPLIER_ROW_CTX_DTLS') THEN
--DELETE rows with sequence = 2 in icx_cat_items_ctx_dtl_tlp
l_string := l_string || ' gDSDInventoryItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gDSDInventoryItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'DELETE_ITEMREV_ROW_CTX_DTLS') THEN
--DELETE rows with sequence = 5 in icx_cat_items_ctx_dtl_tlp
l_string := l_string || ' gDIRDInventoryItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gDIRDInventoryItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'DELETE_SHOPCATG_ROW_CTX_DTLS') THEN
--DELETE rows with sequence = 6 in icx_cat_items_ctx_dtl_tlp
l_string := l_string || ' gDSCDInventoryItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gDSCDInventoryItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'DELETE_PURCHORG_ROW_CTX_DTLS') THEN
--DELETE rows with sequence =15001 in icx_cat_items_ctx_dtl_tlp
l_string := l_string || ' gDPODInventoryItemIdTbl: ' ||
ICX_CAT_UTIL_PVT.getTableElement(gDPODInventoryItemIdTbl, p_index) || ', ';
IF (p_action_mode = 'INSERT_CTX_HDRS') THEN
l_err_loc := 300;
IF (p_action_mode = 'INSERT_CTX_DTLS') THEN
l_err_loc := 500;
IF (p_action_mode = 'UPDATE_CTX_HDRS') THEN
l_err_loc := 700;
IF (p_action_mode = 'DELETE_CTX_HDRS') THEN
l_err_loc := 900;
IF (p_action_mode = 'DELETE_CTX_DTLS') THEN
l_err_loc := 1100;
IF (p_action_mode = 'DELETE_MANDATORY_ROW_CTX_DTLS') THEN
l_err_loc := 1300;
IF (p_action_mode = 'DELETE_SUPPLIER_ROW_CTX_DTLS') THEN
l_err_loc := 1500;
IF (p_action_mode = 'DELETE_ITEMREV_ROW_CTX_DTLS') THEN
l_err_loc := 1700;
IF (p_action_mode = 'DELETE_SHOPCATG_ROW_CTX_DTLS') THEN
l_err_loc := 1900;
IF (p_action_mode = 'DELETE_PURCHORG_ROW_CTX_DTLS') THEN
l_err_loc := 2100;
PROCEDURE deleteItemCtxHdrsTLP
( p_current_ctx_item_rec IN g_ctx_item_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'deleteItemCtxHdrsTLP';
END deleteItemCtxHdrsTLP;
PROCEDURE deleteItemCtxDtlsTLP
( p_current_ctx_item_rec IN g_ctx_item_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'deleteItemCtxDtlsTLP';
END deleteItemCtxDtlsTLP;
PROCEDURE insertItemCtxHdrsTLP
( p_current_ctx_item_rec IN g_ctx_item_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'insertItemCtxHdrsTLP';
END insertItemCtxHdrsTLP;
PROCEDURE insertItemCtxDtlsTLP
( p_current_ctx_item_rec IN g_ctx_item_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'insertItemCtxDtlsTLP';
END insertItemCtxDtlsTLP;
PROCEDURE updateItemCtxHdrsTLP
( p_current_ctx_item_rec IN g_ctx_item_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'updateItemCtxHdrsTLP';
END updateItemCtxHdrsTLP;
l_update_item_ctx_hdr_row BOOLEAN := FALSE;
insertItemCtxHdrsTLP(p_current_ctx_item_rec);
insertItemCtxDtlsTLP(p_current_ctx_item_rec);
'Update row. p_current_cursor:' || p_current_cursor ||
', p_mode:' || p_mode);
deleteItemCtxHdrsTLP(p_current_ctx_item_rec);
deleteItemCtxDtlsTLP(p_current_ctx_item_rec);
insertItemCtxHdrsTLP(p_current_ctx_item_rec);
insertItemCtxDtlsTLP(p_current_ctx_item_rec);
l_update_item_ctx_hdr_row := TRUE;
l_update_item_ctx_hdr_row := TRUE;
l_update_item_ctx_hdr_row := TRUE;
IF (l_update_item_ctx_hdr_row) THEN
l_err_loc := 2600;
updateItemCtxHdrsTLP(p_current_ctx_item_rec);
', l_update_item_ctx_hdr_row is false; so will not call updateItemCtxHdrsTLP');
'Delete row');
deleteItemCtxHdrsTLP(p_current_ctx_item_rec);
deleteItemCtxDtlsTLP(p_current_ctx_item_rec);
'; Num. of rows to be inserted into hdrs:' || gIHInventoryItemIdTbl.COUNT ||
'; Num. of rows to be inserted into dtls:' || gIDInventoryItemIdTbl.COUNT ||
', Total num. of rows to be updated:' || gUHInventoryItemIdTbl.COUNT ||
', Mandatory rows to be re-populated for:' || gDMDInventoryItemIdTbl.COUNT ||
', Supplier rows to be re-populated for:' || gDSDInventoryItemIdTbl.COUNT ||
', Item Revision rows to be re-populated for:' || gDIRDInventoryItemIdTbl.COUNT ||
', Shopping Category rows to be re-populated for:' || gDSCDInventoryItemIdTbl.COUNT ||
', Base and Local attribute rows to be re-populated for:' || gDBLDInventoryItemIdTbl.COUNT ||
', Num. of rows to be deleted:' || gDHInventoryItemIdTbl.COUNT);
l_action_mode := 'INSERT_CTX_HDRS';
INSERT INTO icx_cat_items_ctx_hdrs_tlp
(inventory_item_id, po_line_id,
req_template_name, req_template_line_num,
org_id, language,
source_type, item_type, purchasing_org_id, owning_org_id,
ip_category_id, ip_category_name, po_category_id,
supplier_id, supplier_part_num,
supplier_part_auxid, supplier_site_id,
req_template_po_line_id, item_revision, po_header_id,
document_number, line_num, allow_price_override_flag,
not_to_exceed_price, line_type_id, unit_meas_lookup_code,
suggested_quantity, unit_price, amount, currency_code, rate_type,
rate_date, rate, buyer_id, supplier_contact_id,
rfq_required_flag, negotiated_by_preparer_flag,
description, order_type_lookup_code,
supplier, global_agreement_flag, merged_source_type,
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(gIHInventoryItemIdTbl(i), gIHPoLineIdTbl(i),
gIHReqTemplateNameTbl(i), gIHReqTemplateLineNumTbl(i),
gIHOrgIdTbl(i), gIHLanguageTbl(i),
gIHSourceTypeTbl(i), gIHItemTypeTbl(i), gIHPurchasingOrgIdTbl(i), gIHOwningOrgIdTbl(i),
gIHIpCategoryIdTbl(i), gIHIpCategoryNameTbl(i), gIHPoCategoryIdTbl(i),
gIHSupplierIdTbl(i), gIHSupplierPartNumTbl(i),
gIHSupplierPartAuxidTbl(i), gIHSupplierSiteIdTbl(i), gIHReqTemplatePoLineIdTbl(i),
gIHItemRevisionTbl(i), gIHPoHeaderIdTbl(i), gIHDocumentNumberTbl(i),
gIHLineNumTbl(i), gIHAllowPriceOverrideFlagTbl(i), gIHNotToExceedPriceTbl(i),
gIHLineTypeIdTbl(i), gIHUnitMeasLookupCodeTbl(i), gIHSuggestedQuantityTbl(i),
gIHUnitPriceTbl(i), gIHAmountTbl(i), gIHCurrencyCodeTbl(i), gIHRateTypeTbl(i),
gIHRateDateTbl(i), gIHRateTbl(i), gIHBuyerIdTbl(i), gIHSupplierContactIdTbl(i),
gIHRfqRequiredFlagTbl(i), gIHNegotiatedByPreparerFlagTbl(i), gIHDescriptionTbl(i),
gIHOrderTypeLookupCodeTbl(i), gIHSupplierTbl(i),
gIHGlobalAgreementFlagTbl(i), gIHMergedSourceTypeTbl(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);
'Num. of rows inserted into ctx_hdrs:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_PO_ATTR_VALUES';
l_action_mode := 'INSERT_ATTR_VALUES';
USING (SELECT *
FROM po_attribute_values
WHERE inventory_item_id = gIHInventoryItemIdTbl(i)
AND po_line_id = gIHPoLineIdTbl(i)
AND req_template_name = gIHReqTemplateNameTbl(i)
AND req_template_line_num = gIHReqTemplateLineNumTbl(i)
AND org_id = gIHOrgIdTbl(i)) temp
ON (icav.inventory_item_id = temp.inventory_item_id AND
icav.po_line_id = temp.po_line_id AND
icav.req_template_name = temp.req_template_name AND
icav.req_template_line_num = temp.req_template_line_num AND
icav.org_id = temp.org_id)
WHEN NOT MATCHED THEN INSERT VALUES (
temp.attribute_values_id, temp.po_line_id, temp.req_template_name,
temp.req_template_line_num, temp.ip_category_id, temp.inventory_item_id,
temp.org_id, temp.manufacturer_part_num, temp.picture, temp.thumbnail_image,
temp.supplier_url, temp.manufacturer_url, temp.attachment_url, temp.unspsc,
temp.availability, temp.lead_time,
temp.text_base_attribute1, temp.text_base_attribute2, temp.text_base_attribute3,
temp.text_base_attribute4, temp.text_base_attribute5, temp.text_base_attribute6,
temp.text_base_attribute7, temp.text_base_attribute8, temp.text_base_attribute9,
temp.text_base_attribute10, temp.text_base_attribute11, temp.text_base_attribute12,
temp.text_base_attribute13, temp.text_base_attribute14, temp.text_base_attribute15,
temp.text_base_attribute16, temp.text_base_attribute17, temp.text_base_attribute18,
temp.text_base_attribute19, temp.text_base_attribute20, temp.text_base_attribute21,
temp.text_base_attribute22, temp.text_base_attribute23, temp.text_base_attribute24,
temp.text_base_attribute25, temp.text_base_attribute26, temp.text_base_attribute27,
temp.text_base_attribute28, temp.text_base_attribute29, temp.text_base_attribute30,
temp.text_base_attribute31, temp.text_base_attribute32, temp.text_base_attribute33,
temp.text_base_attribute34, temp.text_base_attribute35, temp.text_base_attribute36,
temp.text_base_attribute37, temp.text_base_attribute38, temp.text_base_attribute39,
temp.text_base_attribute40, temp.text_base_attribute41, temp.text_base_attribute42,
temp.text_base_attribute43, temp.text_base_attribute44, temp.text_base_attribute45,
temp.text_base_attribute46, temp.text_base_attribute47, temp.text_base_attribute48,
temp.text_base_attribute49, temp.text_base_attribute50, temp.text_base_attribute51,
temp.text_base_attribute52, temp.text_base_attribute53, temp.text_base_attribute54,
temp.text_base_attribute55, temp.text_base_attribute56, temp.text_base_attribute57,
temp.text_base_attribute58, temp.text_base_attribute59, temp.text_base_attribute60,
temp.text_base_attribute61, temp.text_base_attribute62, temp.text_base_attribute63,
temp.text_base_attribute64, temp.text_base_attribute65, temp.text_base_attribute66,
temp.text_base_attribute67, temp.text_base_attribute68, temp.text_base_attribute69,
temp.text_base_attribute70, temp.text_base_attribute71, temp.text_base_attribute72,
temp.text_base_attribute73, temp.text_base_attribute74, temp.text_base_attribute75,
temp.text_base_attribute76, temp.text_base_attribute77, temp.text_base_attribute78,
temp.text_base_attribute79, temp.text_base_attribute80, temp.text_base_attribute81,
temp.text_base_attribute82, temp.text_base_attribute83, temp.text_base_attribute84,
temp.text_base_attribute85, temp.text_base_attribute86, temp.text_base_attribute87,
temp.text_base_attribute88, temp.text_base_attribute89, temp.text_base_attribute90,
temp.text_base_attribute91, temp.text_base_attribute92, temp.text_base_attribute93,
temp.text_base_attribute94, temp.text_base_attribute95, temp.text_base_attribute96,
temp.text_base_attribute97, temp.text_base_attribute98, temp.text_base_attribute99,
temp.text_base_attribute100,
temp.num_base_attribute1, temp.num_base_attribute2, temp.num_base_attribute3,
temp.num_base_attribute4, temp.num_base_attribute5, temp.num_base_attribute6,
temp.num_base_attribute7, temp.num_base_attribute8, temp.num_base_attribute9,
temp.num_base_attribute10, temp.num_base_attribute11, temp.num_base_attribute12,
temp.num_base_attribute13, temp.num_base_attribute14, temp.num_base_attribute15,
temp.num_base_attribute16, temp.num_base_attribute17, temp.num_base_attribute18,
temp.num_base_attribute19, temp.num_base_attribute20, temp.num_base_attribute21,
temp.num_base_attribute22, temp.num_base_attribute23, temp.num_base_attribute24,
temp.num_base_attribute25, temp.num_base_attribute26, temp.num_base_attribute27,
temp.num_base_attribute28, temp.num_base_attribute29, temp.num_base_attribute30,
temp.num_base_attribute31, temp.num_base_attribute32, temp.num_base_attribute33,
temp.num_base_attribute34, temp.num_base_attribute35, temp.num_base_attribute36,
temp.num_base_attribute37, temp.num_base_attribute38, temp.num_base_attribute39,
temp.num_base_attribute40, temp.num_base_attribute41, temp.num_base_attribute42,
temp.num_base_attribute43, temp.num_base_attribute44, temp.num_base_attribute45,
temp.num_base_attribute46, temp.num_base_attribute47, temp.num_base_attribute48,
temp.num_base_attribute49, temp.num_base_attribute50, temp.num_base_attribute51,
temp.num_base_attribute52, temp.num_base_attribute53, temp.num_base_attribute54,
temp.num_base_attribute55, temp.num_base_attribute56, temp.num_base_attribute57,
temp.num_base_attribute58, temp.num_base_attribute59, temp.num_base_attribute60,
temp.num_base_attribute61, temp.num_base_attribute62, temp.num_base_attribute63,
temp.num_base_attribute64, temp.num_base_attribute65, temp.num_base_attribute66,
temp.num_base_attribute67, temp.num_base_attribute68, temp.num_base_attribute69,
temp.num_base_attribute70, temp.num_base_attribute71, temp.num_base_attribute72,
temp.num_base_attribute73, temp.num_base_attribute74, temp.num_base_attribute75,
temp.num_base_attribute76, temp.num_base_attribute77, temp.num_base_attribute78,
temp.num_base_attribute79, temp.num_base_attribute80, temp.num_base_attribute81,
temp.num_base_attribute82, temp.num_base_attribute83, temp.num_base_attribute84,
temp.num_base_attribute85, temp.num_base_attribute86, temp.num_base_attribute87,
temp.num_base_attribute88, temp.num_base_attribute89, temp.num_base_attribute90,
temp.num_base_attribute91, temp.num_base_attribute92, temp.num_base_attribute93,
temp.num_base_attribute94, temp.num_base_attribute95, temp.num_base_attribute96,
temp.num_base_attribute97, temp.num_base_attribute98, temp.num_base_attribute99,
temp.num_base_attribute100,
temp.text_cat_attribute1, temp.text_cat_attribute2, temp.text_cat_attribute3,
temp.text_cat_attribute4, temp.text_cat_attribute5, temp.text_cat_attribute6,
temp.text_cat_attribute7, temp.text_cat_attribute8, temp.text_cat_attribute9,
temp.text_cat_attribute10, temp.text_cat_attribute11, temp.text_cat_attribute12,
temp.text_cat_attribute13, temp.text_cat_attribute14, temp.text_cat_attribute15,
temp.text_cat_attribute16, temp.text_cat_attribute17, temp.text_cat_attribute18,
temp.text_cat_attribute19, temp.text_cat_attribute20, temp.text_cat_attribute21,
temp.text_cat_attribute22, temp.text_cat_attribute23, temp.text_cat_attribute24,
temp.text_cat_attribute25, temp.text_cat_attribute26, temp.text_cat_attribute27,
temp.text_cat_attribute28, temp.text_cat_attribute29, temp.text_cat_attribute30,
temp.text_cat_attribute31, temp.text_cat_attribute32, temp.text_cat_attribute33,
temp.text_cat_attribute34, temp.text_cat_attribute35, temp.text_cat_attribute36,
temp.text_cat_attribute37, temp.text_cat_attribute38, temp.text_cat_attribute39,
temp.text_cat_attribute40, temp.text_cat_attribute41, temp.text_cat_attribute42,
temp.text_cat_attribute43, temp.text_cat_attribute44, temp.text_cat_attribute45,
temp.text_cat_attribute46, temp.text_cat_attribute47, temp.text_cat_attribute48,
temp.text_cat_attribute49, temp.text_cat_attribute50,
temp.num_cat_attribute1, temp.num_cat_attribute2, temp.num_cat_attribute3,
temp.num_cat_attribute4, temp.num_cat_attribute5, temp.num_cat_attribute6,
temp.num_cat_attribute7, temp.num_cat_attribute8, temp.num_cat_attribute9,
temp.num_cat_attribute10, temp.num_cat_attribute11, temp.num_cat_attribute12,
temp.num_cat_attribute13, temp.num_cat_attribute14, temp.num_cat_attribute15,
temp.num_cat_attribute16, temp.num_cat_attribute17, temp.num_cat_attribute18,
temp.num_cat_attribute19, temp.num_cat_attribute20, temp.num_cat_attribute21,
temp.num_cat_attribute22, temp.num_cat_attribute23, temp.num_cat_attribute24,
temp.num_cat_attribute25, temp.num_cat_attribute26, temp.num_cat_attribute27,
temp.num_cat_attribute28, temp.num_cat_attribute29, temp.num_cat_attribute30,
temp.num_cat_attribute31, temp.num_cat_attribute32, temp.num_cat_attribute33,
temp.num_cat_attribute34, temp.num_cat_attribute35, temp.num_cat_attribute36,
temp.num_cat_attribute37, temp.num_cat_attribute38, temp.num_cat_attribute39,
temp.num_cat_attribute40, temp.num_cat_attribute41, temp.num_cat_attribute42,
temp.num_cat_attribute43, temp.num_cat_attribute44, temp.num_cat_attribute45,
temp.num_cat_attribute46, temp.num_cat_attribute47, temp.num_cat_attribute48,
temp.num_cat_attribute49, temp.num_cat_attribute50,
temp.last_update_login, temp.last_updated_by, temp.last_update_date, temp.created_by,
temp.creation_date, temp.request_id, temp.program_application_id, temp.program_id,
temp.program_update_date, temp.last_updated_program, temp.rebuild_search_index_flag);
'Num. of rows inserted into icx_cat_attribute_values:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_PO_ATTR_VALUES_TLP';
l_action_mode := 'INSERT_ATTR_VALUES_TLP';
USING (SELECT *
FROM po_attribute_values_tlp
WHERE inventory_item_id = gIHInventoryItemIdTbl(i)
AND po_line_id = gIHPoLineIdTbl(i)
AND req_template_name = gIHReqTemplateNameTbl(i)
AND req_template_line_num = gIHReqTemplateLineNumTbl(i)
AND org_id = gIHOrgIdTbl(i)
AND language = gIHLanguageTbl(i)) temp
ON (icavt.inventory_item_id = temp.inventory_item_id AND
icavt.po_line_id = temp.po_line_id AND
icavt.req_template_name = temp.req_template_name AND
icavt.req_template_line_num = temp.req_template_line_num AND
icavt.org_id = temp.org_id AND
icavt.language = temp.language)
WHEN NOT MATCHED THEN INSERT VALUES (
temp.attribute_values_tlp_id, temp.po_line_id, temp.req_template_name,
temp.req_template_line_num, temp.ip_category_id, temp.inventory_item_id,
temp.org_id, temp.language, temp.description, temp.manufacturer,
temp.comments, temp.alias, temp.long_description,
temp.tl_text_base_attribute1, temp.tl_text_base_attribute2, temp.tl_text_base_attribute3,
temp.tl_text_base_attribute4, temp.tl_text_base_attribute5, temp.tl_text_base_attribute6,
temp.tl_text_base_attribute7, temp.tl_text_base_attribute8, temp.tl_text_base_attribute9,
temp.tl_text_base_attribute10, temp.tl_text_base_attribute11, temp.tl_text_base_attribute12,
temp.tl_text_base_attribute13, temp.tl_text_base_attribute14, temp.tl_text_base_attribute15,
temp.tl_text_base_attribute16, temp.tl_text_base_attribute17, temp.tl_text_base_attribute18,
temp.tl_text_base_attribute19, temp.tl_text_base_attribute20, temp.tl_text_base_attribute21,
temp.tl_text_base_attribute22, temp.tl_text_base_attribute23, temp.tl_text_base_attribute24,
temp.tl_text_base_attribute25, temp.tl_text_base_attribute26, temp.tl_text_base_attribute27,
temp.tl_text_base_attribute28, temp.tl_text_base_attribute29, temp.tl_text_base_attribute30,
temp.tl_text_base_attribute31, temp.tl_text_base_attribute32, temp.tl_text_base_attribute33,
temp.tl_text_base_attribute34, temp.tl_text_base_attribute35, temp.tl_text_base_attribute36,
temp.tl_text_base_attribute37, temp.tl_text_base_attribute38, temp.tl_text_base_attribute39,
temp.tl_text_base_attribute40, temp.tl_text_base_attribute41, temp.tl_text_base_attribute42,
temp.tl_text_base_attribute43, temp.tl_text_base_attribute44, temp.tl_text_base_attribute45,
temp.tl_text_base_attribute46, temp.tl_text_base_attribute47, temp.tl_text_base_attribute48,
temp.tl_text_base_attribute49, temp.tl_text_base_attribute50, temp.tl_text_base_attribute51,
temp.tl_text_base_attribute52, temp.tl_text_base_attribute53, temp.tl_text_base_attribute54,
temp.tl_text_base_attribute55, temp.tl_text_base_attribute56, temp.tl_text_base_attribute57,
temp.tl_text_base_attribute58, temp.tl_text_base_attribute59, temp.tl_text_base_attribute60,
temp.tl_text_base_attribute61, temp.tl_text_base_attribute62, temp.tl_text_base_attribute63,
temp.tl_text_base_attribute64, temp.tl_text_base_attribute65, temp.tl_text_base_attribute66,
temp.tl_text_base_attribute67, temp.tl_text_base_attribute68, temp.tl_text_base_attribute69,
temp.tl_text_base_attribute70, temp.tl_text_base_attribute71, temp.tl_text_base_attribute72,
temp.tl_text_base_attribute73, temp.tl_text_base_attribute74, temp.tl_text_base_attribute75,
temp.tl_text_base_attribute76, temp.tl_text_base_attribute77, temp.tl_text_base_attribute78,
temp.tl_text_base_attribute79, temp.tl_text_base_attribute80, temp.tl_text_base_attribute81,
temp.tl_text_base_attribute82, temp.tl_text_base_attribute83, temp.tl_text_base_attribute84,
temp.tl_text_base_attribute85, temp.tl_text_base_attribute86, temp.tl_text_base_attribute87,
temp.tl_text_base_attribute88, temp.tl_text_base_attribute89, temp.tl_text_base_attribute90,
temp.tl_text_base_attribute91, temp.tl_text_base_attribute92, temp.tl_text_base_attribute93,
temp.tl_text_base_attribute94, temp.tl_text_base_attribute95, temp.tl_text_base_attribute96,
temp.tl_text_base_attribute97, temp.tl_text_base_attribute98, temp.tl_text_base_attribute99,
temp.tl_text_base_attribute100,
temp.tl_text_cat_attribute1, temp.tl_text_cat_attribute2, temp.tl_text_cat_attribute3,
temp.tl_text_cat_attribute4, temp.tl_text_cat_attribute5, temp.tl_text_cat_attribute6,
temp.tl_text_cat_attribute7, temp.tl_text_cat_attribute8, temp.tl_text_cat_attribute9,
temp.tl_text_cat_attribute10, temp.tl_text_cat_attribute11, temp.tl_text_cat_attribute12,
temp.tl_text_cat_attribute13, temp.tl_text_cat_attribute14, temp.tl_text_cat_attribute15,
temp.tl_text_cat_attribute16, temp.tl_text_cat_attribute17, temp.tl_text_cat_attribute18,
temp.tl_text_cat_attribute19, temp.tl_text_cat_attribute20, temp.tl_text_cat_attribute21,
temp.tl_text_cat_attribute22, temp.tl_text_cat_attribute23, temp.tl_text_cat_attribute24,
temp.tl_text_cat_attribute25, temp.tl_text_cat_attribute26, temp.tl_text_cat_attribute27,
temp.tl_text_cat_attribute28, temp.tl_text_cat_attribute29, temp.tl_text_cat_attribute30,
temp.tl_text_cat_attribute31, temp.tl_text_cat_attribute32, temp.tl_text_cat_attribute33,
temp.tl_text_cat_attribute34, temp.tl_text_cat_attribute35, temp.tl_text_cat_attribute36,
temp.tl_text_cat_attribute37, temp.tl_text_cat_attribute38, temp.tl_text_cat_attribute39,
temp.tl_text_cat_attribute40, temp.tl_text_cat_attribute41, temp.tl_text_cat_attribute42,
temp.tl_text_cat_attribute43, temp.tl_text_cat_attribute44, temp.tl_text_cat_attribute45,
temp.tl_text_cat_attribute46, temp.tl_text_cat_attribute47, temp.tl_text_cat_attribute48,
temp.tl_text_cat_attribute49, temp.tl_text_cat_attribute50,
temp.last_update_login, temp.last_updated_by, temp.last_update_date, temp.created_by,
temp.creation_date, temp.request_id, temp.program_application_id, temp.program_id,
temp.program_update_date, temp.last_updated_program, temp.rebuild_search_index_flag);
'Num. of rows inserted into icx_cat_attribute_values_tlp:' ||SQL%ROWCOUNT);
l_action_mode := 'UPDATE_CTX_HDRS';
UPDATE icx_cat_items_ctx_hdrs_tlp
SET ctx_desc = null,
purchasing_org_id = gUHPurchasingOrgIdTbl(i),
ip_category_id = gUHIpCategoryIdTbl(i),
ip_category_name = gUHIpCategoryNameTbl(i),
po_category_id = gUHPoCategoryIdTbl(i),
supplier_id = gUHSupplierIdTbl(i),
supplier_part_num = gUHSupplierPartNumTbl(i),
supplier_part_auxid = gUHSupplierPartAuxidTbl(i),
supplier_site_id = gUHSupplierSiteIdTbl(i),
req_template_po_line_id = gUHReqTemplatePoLineIdTbl(i),
item_revision = gUHItemRevisionTbl(i),
po_header_id = gUHPoHeaderIdTbl(i),
document_number = gUHDocumentNumberTbl(i),
line_num = gUHLineNumTbl(i),
allow_price_override_flag = gUHAllowPriceOverrideFlagTbl(i),
not_to_exceed_price = gUHNotToExceedPriceTbl(i),
line_type_id = gUHLineTypeIdTbl(i),
unit_meas_lookup_code = gUHUnitMeasLookupCodeTbl(i),
suggested_quantity = gUHSuggestedQuantityTbl(i),
unit_price = gUHUnitPriceTbl(i),
amount = gUHAmountTbl(i),
currency_code = gUHCurrencyCodeTbl(i),
rate_type = gUHRateTypeTbl(i),
rate_date = gUHRateDateTbl(i),
rate = gUHRateTbl(i),
buyer_id = gUHBuyerIdTbl(i),
supplier_contact_id = gUHSupplierContactIdTbl(i),
rfq_required_flag = gUHRfqRequiredFlagTbl(i),
negotiated_by_preparer_flag = gUHNegotiatedByPreparerFlagTbl(i),
description = gUHDescriptionTbl(i),
order_type_lookup_code = gUHOrderTypeLookupCodeTbl(i),
supplier = gUHSupplierTbl(i),
global_agreement_flag = gUHGlobalAgreementFlagTbl(i),
merged_source_type = gUHMergedSourceTypeTbl(i),
item_type = gUHItemTypeTbl(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 inventory_item_id = gUHInventoryItemIdTbl(i)
AND po_line_id = gUHPoLineIdTbl(i)
AND req_template_name = gUHReqTemplateNameTbl(i)
AND req_template_line_num = gUHReqTemplateLineNumTbl(i)
AND org_id = gUHOrgIdTbl(i)
AND language = gUHLanguageTbl(i)
AND source_type = gUHSourceTypeTbl(i);
'Num. of rows updated in ctx_hdrs:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_TO_UPDATE_ATTR_VALUES';
po_attribute_values_pvt.update_attributes_MI
(
p_org_id => gUHOrgIdTbl(i),
p_ip_category_id => gUHIpCategoryIdTbl(i),
p_inventory_item_id => gUHInventoryItemIdTbl(i),
p_language => gUHLanguageTbl(i),
p_item_description => gUHDescriptionTbl(i),
p_long_description => gUHLongDescriptionTbl(i),
p_organization_id => gUHOrganizationIdTbl(i),
p_master_organization_id => gUHMasterOrganizationIdTbl(i)
);
DELETE FROM icx_cat_attribute_values
WHERE inventory_item_id = gUHInventoryItemIdTbl(i)
AND po_line_id = gUHPoLineIdTbl(i)
AND req_template_name = gUHReqTemplateNameTbl(i)
AND req_template_line_num = gUHReqTemplateLineNumTbl(i)
AND org_id = gUHOrgIdTbl(i);
'Num. of rows deleted for update from icx_cat_attribute_values:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_TO_UPDATE_ATTR_VALUES';
USING (SELECT *
FROM po_attribute_values
WHERE inventory_item_id = gUHInventoryItemIdTbl(i)
AND po_line_id = gUHPoLineIdTbl(i)
AND req_template_name = gUHReqTemplateNameTbl(i)
AND req_template_line_num = gUHReqTemplateLineNumTbl(i)
AND org_id = gUHOrgIdTbl(i)) temp
ON (icav.inventory_item_id = temp.inventory_item_id AND
icav.po_line_id = temp.po_line_id AND
icav.req_template_name = temp.req_template_name AND
icav.req_template_line_num = temp.req_template_line_num AND
icav.org_id = temp.org_id)
WHEN NOT MATCHED THEN INSERT VALUES (
temp.attribute_values_id, temp.po_line_id, temp.req_template_name,
temp.req_template_line_num, temp.ip_category_id, temp.inventory_item_id,
temp.org_id, temp.manufacturer_part_num, temp.picture, temp.thumbnail_image,
temp.supplier_url, temp.manufacturer_url, temp.attachment_url, temp.unspsc,
temp.availability, temp.lead_time,
temp.text_base_attribute1, temp.text_base_attribute2, temp.text_base_attribute3,
temp.text_base_attribute4, temp.text_base_attribute5, temp.text_base_attribute6,
temp.text_base_attribute7, temp.text_base_attribute8, temp.text_base_attribute9,
temp.text_base_attribute10, temp.text_base_attribute11, temp.text_base_attribute12,
temp.text_base_attribute13, temp.text_base_attribute14, temp.text_base_attribute15,
temp.text_base_attribute16, temp.text_base_attribute17, temp.text_base_attribute18,
temp.text_base_attribute19, temp.text_base_attribute20, temp.text_base_attribute21,
temp.text_base_attribute22, temp.text_base_attribute23, temp.text_base_attribute24,
temp.text_base_attribute25, temp.text_base_attribute26, temp.text_base_attribute27,
temp.text_base_attribute28, temp.text_base_attribute29, temp.text_base_attribute30,
temp.text_base_attribute31, temp.text_base_attribute32, temp.text_base_attribute33,
temp.text_base_attribute34, temp.text_base_attribute35, temp.text_base_attribute36,
temp.text_base_attribute37, temp.text_base_attribute38, temp.text_base_attribute39,
temp.text_base_attribute40, temp.text_base_attribute41, temp.text_base_attribute42,
temp.text_base_attribute43, temp.text_base_attribute44, temp.text_base_attribute45,
temp.text_base_attribute46, temp.text_base_attribute47, temp.text_base_attribute48,
temp.text_base_attribute49, temp.text_base_attribute50, temp.text_base_attribute51,
temp.text_base_attribute52, temp.text_base_attribute53, temp.text_base_attribute54,
temp.text_base_attribute55, temp.text_base_attribute56, temp.text_base_attribute57,
temp.text_base_attribute58, temp.text_base_attribute59, temp.text_base_attribute60,
temp.text_base_attribute61, temp.text_base_attribute62, temp.text_base_attribute63,
temp.text_base_attribute64, temp.text_base_attribute65, temp.text_base_attribute66,
temp.text_base_attribute67, temp.text_base_attribute68, temp.text_base_attribute69,
temp.text_base_attribute70, temp.text_base_attribute71, temp.text_base_attribute72,
temp.text_base_attribute73, temp.text_base_attribute74, temp.text_base_attribute75,
temp.text_base_attribute76, temp.text_base_attribute77, temp.text_base_attribute78,
temp.text_base_attribute79, temp.text_base_attribute80, temp.text_base_attribute81,
temp.text_base_attribute82, temp.text_base_attribute83, temp.text_base_attribute84,
temp.text_base_attribute85, temp.text_base_attribute86, temp.text_base_attribute87,
temp.text_base_attribute88, temp.text_base_attribute89, temp.text_base_attribute90,
temp.text_base_attribute91, temp.text_base_attribute92, temp.text_base_attribute93,
temp.text_base_attribute94, temp.text_base_attribute95, temp.text_base_attribute96,
temp.text_base_attribute97, temp.text_base_attribute98, temp.text_base_attribute99,
temp.text_base_attribute100,
temp.num_base_attribute1, temp.num_base_attribute2, temp.num_base_attribute3,
temp.num_base_attribute4, temp.num_base_attribute5, temp.num_base_attribute6,
temp.num_base_attribute7, temp.num_base_attribute8, temp.num_base_attribute9,
temp.num_base_attribute10, temp.num_base_attribute11, temp.num_base_attribute12,
temp.num_base_attribute13, temp.num_base_attribute14, temp.num_base_attribute15,
temp.num_base_attribute16, temp.num_base_attribute17, temp.num_base_attribute18,
temp.num_base_attribute19, temp.num_base_attribute20, temp.num_base_attribute21,
temp.num_base_attribute22, temp.num_base_attribute23, temp.num_base_attribute24,
temp.num_base_attribute25, temp.num_base_attribute26, temp.num_base_attribute27,
temp.num_base_attribute28, temp.num_base_attribute29, temp.num_base_attribute30,
temp.num_base_attribute31, temp.num_base_attribute32, temp.num_base_attribute33,
temp.num_base_attribute34, temp.num_base_attribute35, temp.num_base_attribute36,
temp.num_base_attribute37, temp.num_base_attribute38, temp.num_base_attribute39,
temp.num_base_attribute40, temp.num_base_attribute41, temp.num_base_attribute42,
temp.num_base_attribute43, temp.num_base_attribute44, temp.num_base_attribute45,
temp.num_base_attribute46, temp.num_base_attribute47, temp.num_base_attribute48,
temp.num_base_attribute49, temp.num_base_attribute50, temp.num_base_attribute51,
temp.num_base_attribute52, temp.num_base_attribute53, temp.num_base_attribute54,
temp.num_base_attribute55, temp.num_base_attribute56, temp.num_base_attribute57,
temp.num_base_attribute58, temp.num_base_attribute59, temp.num_base_attribute60,
temp.num_base_attribute61, temp.num_base_attribute62, temp.num_base_attribute63,
temp.num_base_attribute64, temp.num_base_attribute65, temp.num_base_attribute66,
temp.num_base_attribute67, temp.num_base_attribute68, temp.num_base_attribute69,
temp.num_base_attribute70, temp.num_base_attribute71, temp.num_base_attribute72,
temp.num_base_attribute73, temp.num_base_attribute74, temp.num_base_attribute75,
temp.num_base_attribute76, temp.num_base_attribute77, temp.num_base_attribute78,
temp.num_base_attribute79, temp.num_base_attribute80, temp.num_base_attribute81,
temp.num_base_attribute82, temp.num_base_attribute83, temp.num_base_attribute84,
temp.num_base_attribute85, temp.num_base_attribute86, temp.num_base_attribute87,
temp.num_base_attribute88, temp.num_base_attribute89, temp.num_base_attribute90,
temp.num_base_attribute91, temp.num_base_attribute92, temp.num_base_attribute93,
temp.num_base_attribute94, temp.num_base_attribute95, temp.num_base_attribute96,
temp.num_base_attribute97, temp.num_base_attribute98, temp.num_base_attribute99,
temp.num_base_attribute100,
temp.text_cat_attribute1, temp.text_cat_attribute2, temp.text_cat_attribute3,
temp.text_cat_attribute4, temp.text_cat_attribute5, temp.text_cat_attribute6,
temp.text_cat_attribute7, temp.text_cat_attribute8, temp.text_cat_attribute9,
temp.text_cat_attribute10, temp.text_cat_attribute11, temp.text_cat_attribute12,
temp.text_cat_attribute13, temp.text_cat_attribute14, temp.text_cat_attribute15,
temp.text_cat_attribute16, temp.text_cat_attribute17, temp.text_cat_attribute18,
temp.text_cat_attribute19, temp.text_cat_attribute20, temp.text_cat_attribute21,
temp.text_cat_attribute22, temp.text_cat_attribute23, temp.text_cat_attribute24,
temp.text_cat_attribute25, temp.text_cat_attribute26, temp.text_cat_attribute27,
temp.text_cat_attribute28, temp.text_cat_attribute29, temp.text_cat_attribute30,
temp.text_cat_attribute31, temp.text_cat_attribute32, temp.text_cat_attribute33,
temp.text_cat_attribute34, temp.text_cat_attribute35, temp.text_cat_attribute36,
temp.text_cat_attribute37, temp.text_cat_attribute38, temp.text_cat_attribute39,
temp.text_cat_attribute40, temp.text_cat_attribute41, temp.text_cat_attribute42,
temp.text_cat_attribute43, temp.text_cat_attribute44, temp.text_cat_attribute45,
temp.text_cat_attribute46, temp.text_cat_attribute47, temp.text_cat_attribute48,
temp.text_cat_attribute49, temp.text_cat_attribute50,
temp.num_cat_attribute1, temp.num_cat_attribute2, temp.num_cat_attribute3,
temp.num_cat_attribute4, temp.num_cat_attribute5, temp.num_cat_attribute6,
temp.num_cat_attribute7, temp.num_cat_attribute8, temp.num_cat_attribute9,
temp.num_cat_attribute10, temp.num_cat_attribute11, temp.num_cat_attribute12,
temp.num_cat_attribute13, temp.num_cat_attribute14, temp.num_cat_attribute15,
temp.num_cat_attribute16, temp.num_cat_attribute17, temp.num_cat_attribute18,
temp.num_cat_attribute19, temp.num_cat_attribute20, temp.num_cat_attribute21,
temp.num_cat_attribute22, temp.num_cat_attribute23, temp.num_cat_attribute24,
temp.num_cat_attribute25, temp.num_cat_attribute26, temp.num_cat_attribute27,
temp.num_cat_attribute28, temp.num_cat_attribute29, temp.num_cat_attribute30,
temp.num_cat_attribute31, temp.num_cat_attribute32, temp.num_cat_attribute33,
temp.num_cat_attribute34, temp.num_cat_attribute35, temp.num_cat_attribute36,
temp.num_cat_attribute37, temp.num_cat_attribute38, temp.num_cat_attribute39,
temp.num_cat_attribute40, temp.num_cat_attribute41, temp.num_cat_attribute42,
temp.num_cat_attribute43, temp.num_cat_attribute44, temp.num_cat_attribute45,
temp.num_cat_attribute46, temp.num_cat_attribute47, temp.num_cat_attribute48,
temp.num_cat_attribute49, temp.num_cat_attribute50,
temp.last_update_login, temp.last_updated_by, temp.last_update_date, temp.created_by,
temp.creation_date, temp.request_id, temp.program_application_id, temp.program_id,
temp.program_update_date, temp.last_updated_program, temp.rebuild_search_index_flag);
'Num. of rows updated into icx_cat_attribute_values:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_TO_UPDATE_ATTR_VALUES_TLP';
DELETE FROM icx_cat_attribute_values_tlp
WHERE inventory_item_id = gUHInventoryItemIdTbl(i)
AND po_line_id = gUHPoLineIdTbl(i)
AND req_template_name = gUHReqTemplateNameTbl(i)
AND req_template_line_num = gUHReqTemplateLineNumTbl(i)
AND org_id = gUHOrgIdTbl(i)
AND language = gUHLanguageTbl(i);
'Num. of rows deleted for update from icx_cat_attribute_values_tlp:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_TO_UPDATE_ATTR_VALUES_TLP';
USING (SELECT *
FROM po_attribute_values_tlp
WHERE inventory_item_id = gUHInventoryItemIdTbl(i)
AND po_line_id = gUHPoLineIdTbl(i)
AND req_template_name = gUHReqTemplateNameTbl(i)
AND req_template_line_num = gUHReqTemplateLineNumTbl(i)
AND org_id = gUHOrgIdTbl(i)
AND language = gUHLanguageTbl(i)) temp
ON (icavt.inventory_item_id = temp.inventory_item_id AND
icavt.po_line_id = temp.po_line_id AND
icavt.req_template_name = temp.req_template_name AND
icavt.req_template_line_num = temp.req_template_line_num AND
icavt.org_id = temp.org_id AND
icavt.language = temp.language)
WHEN NOT MATCHED THEN INSERT VALUES (
temp.attribute_values_tlp_id, temp.po_line_id, temp.req_template_name,
temp.req_template_line_num, temp.ip_category_id, temp.inventory_item_id,
temp.org_id, temp.language, temp.description, temp.manufacturer,
temp.comments, temp.alias, temp.long_description,
temp.tl_text_base_attribute1, temp.tl_text_base_attribute2, temp.tl_text_base_attribute3,
temp.tl_text_base_attribute4, temp.tl_text_base_attribute5, temp.tl_text_base_attribute6,
temp.tl_text_base_attribute7, temp.tl_text_base_attribute8, temp.tl_text_base_attribute9,
temp.tl_text_base_attribute10, temp.tl_text_base_attribute11, temp.tl_text_base_attribute12,
temp.tl_text_base_attribute13, temp.tl_text_base_attribute14, temp.tl_text_base_attribute15,
temp.tl_text_base_attribute16, temp.tl_text_base_attribute17, temp.tl_text_base_attribute18,
temp.tl_text_base_attribute19, temp.tl_text_base_attribute20, temp.tl_text_base_attribute21,
temp.tl_text_base_attribute22, temp.tl_text_base_attribute23, temp.tl_text_base_attribute24,
temp.tl_text_base_attribute25, temp.tl_text_base_attribute26, temp.tl_text_base_attribute27,
temp.tl_text_base_attribute28, temp.tl_text_base_attribute29, temp.tl_text_base_attribute30,
temp.tl_text_base_attribute31, temp.tl_text_base_attribute32, temp.tl_text_base_attribute33,
temp.tl_text_base_attribute34, temp.tl_text_base_attribute35, temp.tl_text_base_attribute36,
temp.tl_text_base_attribute37, temp.tl_text_base_attribute38, temp.tl_text_base_attribute39,
temp.tl_text_base_attribute40, temp.tl_text_base_attribute41, temp.tl_text_base_attribute42,
temp.tl_text_base_attribute43, temp.tl_text_base_attribute44, temp.tl_text_base_attribute45,
temp.tl_text_base_attribute46, temp.tl_text_base_attribute47, temp.tl_text_base_attribute48,
temp.tl_text_base_attribute49, temp.tl_text_base_attribute50, temp.tl_text_base_attribute51,
temp.tl_text_base_attribute52, temp.tl_text_base_attribute53, temp.tl_text_base_attribute54,
temp.tl_text_base_attribute55, temp.tl_text_base_attribute56, temp.tl_text_base_attribute57,
temp.tl_text_base_attribute58, temp.tl_text_base_attribute59, temp.tl_text_base_attribute60,
temp.tl_text_base_attribute61, temp.tl_text_base_attribute62, temp.tl_text_base_attribute63,
temp.tl_text_base_attribute64, temp.tl_text_base_attribute65, temp.tl_text_base_attribute66,
temp.tl_text_base_attribute67, temp.tl_text_base_attribute68, temp.tl_text_base_attribute69,
temp.tl_text_base_attribute70, temp.tl_text_base_attribute71, temp.tl_text_base_attribute72,
temp.tl_text_base_attribute73, temp.tl_text_base_attribute74, temp.tl_text_base_attribute75,
temp.tl_text_base_attribute76, temp.tl_text_base_attribute77, temp.tl_text_base_attribute78,
temp.tl_text_base_attribute79, temp.tl_text_base_attribute80, temp.tl_text_base_attribute81,
temp.tl_text_base_attribute82, temp.tl_text_base_attribute83, temp.tl_text_base_attribute84,
temp.tl_text_base_attribute85, temp.tl_text_base_attribute86, temp.tl_text_base_attribute87,
temp.tl_text_base_attribute88, temp.tl_text_base_attribute89, temp.tl_text_base_attribute90,
temp.tl_text_base_attribute91, temp.tl_text_base_attribute92, temp.tl_text_base_attribute93,
temp.tl_text_base_attribute94, temp.tl_text_base_attribute95, temp.tl_text_base_attribute96,
temp.tl_text_base_attribute97, temp.tl_text_base_attribute98, temp.tl_text_base_attribute99,
temp.tl_text_base_attribute100,
temp.tl_text_cat_attribute1, temp.tl_text_cat_attribute2, temp.tl_text_cat_attribute3,
temp.tl_text_cat_attribute4, temp.tl_text_cat_attribute5, temp.tl_text_cat_attribute6,
temp.tl_text_cat_attribute7, temp.tl_text_cat_attribute8, temp.tl_text_cat_attribute9,
temp.tl_text_cat_attribute10, temp.tl_text_cat_attribute11, temp.tl_text_cat_attribute12,
temp.tl_text_cat_attribute13, temp.tl_text_cat_attribute14, temp.tl_text_cat_attribute15,
temp.tl_text_cat_attribute16, temp.tl_text_cat_attribute17, temp.tl_text_cat_attribute18,
temp.tl_text_cat_attribute19, temp.tl_text_cat_attribute20, temp.tl_text_cat_attribute21,
temp.tl_text_cat_attribute22, temp.tl_text_cat_attribute23, temp.tl_text_cat_attribute24,
temp.tl_text_cat_attribute25, temp.tl_text_cat_attribute26, temp.tl_text_cat_attribute27,
temp.tl_text_cat_attribute28, temp.tl_text_cat_attribute29, temp.tl_text_cat_attribute30,
temp.tl_text_cat_attribute31, temp.tl_text_cat_attribute32, temp.tl_text_cat_attribute33,
temp.tl_text_cat_attribute34, temp.tl_text_cat_attribute35, temp.tl_text_cat_attribute36,
temp.tl_text_cat_attribute37, temp.tl_text_cat_attribute38, temp.tl_text_cat_attribute39,
temp.tl_text_cat_attribute40, temp.tl_text_cat_attribute41, temp.tl_text_cat_attribute42,
temp.tl_text_cat_attribute43, temp.tl_text_cat_attribute44, temp.tl_text_cat_attribute45,
temp.tl_text_cat_attribute46, temp.tl_text_cat_attribute47, temp.tl_text_cat_attribute48,
temp.tl_text_cat_attribute49, temp.tl_text_cat_attribute50,
temp.last_update_login, temp.last_updated_by, temp.last_update_date, temp.created_by,
temp.creation_date, temp.request_id, temp.program_application_id, temp.program_id,
temp.program_update_date, temp.last_updated_program, temp.rebuild_search_index_flag);
'Num. of rows updated into icx_cat_attribute_values_tlp:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_CTX_DTLS';
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id = gDDInventoryItemIdTbl(i)
AND po_line_id = gDDPoLineIdTbl(i)
AND req_template_name = gDDReqTemplateNameTbl(i)
AND req_template_line_num = gDDReqTemplateLineNumTbl(i)
AND org_id = gDDOrgIdTbl(i)
AND language = gDDLanguageTbl(i);
'Num. of rows deleted from ctx_dtls:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_CTX_HDRS';
DELETE FROM icx_cat_items_ctx_hdrs_tlp
WHERE inventory_item_id = gDHInventoryItemIdTbl(i)
AND po_line_id = gDHPoLineIdTbl(i)
AND req_template_name = gDHReqTemplateNameTbl(i)
AND req_template_line_num = gDHReqTemplateLineNumTbl(i)
AND org_id = gDHOrgIdTbl(i)
AND language = gDHLanguageTbl(i);
'Num. of rows deleted from ctx_hdrs:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_ATTR_VALUES';
DELETE FROM icx_cat_attribute_values
WHERE inventory_item_id = gDHInventoryItemIdTbl(i)
AND po_line_id = gDHPoLineIdTbl(i)
AND req_template_name = gDHReqTemplateNameTbl(i)
AND req_template_line_num = gDHReqTemplateLineNumTbl(i)
AND org_id = gDHOrgIdTbl(i);
'Num. of rows deleted from icx_Cat_attribute_values:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_ATTR_VALUES_TLP';
DELETE FROM icx_cat_attribute_values_tlp
WHERE inventory_item_id = gDHInventoryItemIdTbl(i)
AND po_line_id = gDHPoLineIdTbl(i)
AND req_template_name = gDHReqTemplateNameTbl(i)
AND req_template_line_num = gDHReqTemplateLineNumTbl(i)
AND org_id = gDHOrgIdTbl(i)
AND language = gDHLanguageTbl(i);
'Num. of rows deleted from icx_Cat_attribute_values_tlp:' ||SQL%ROWCOUNT);
l_special_ctx_sql_tbl.DELETE;
l_regular_ctx_sql_tbl.DELETE;
l_action_mode := 'DELETE_MANDATORY_ROW_CTX_DTLS';
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id = gDMDInventoryItemIdTbl(i)
AND po_line_id = gDMDPoLineIdTbl(i)
AND req_template_name = gDMDReqTemplateNameTbl(i)
AND req_template_line_num = gDMDReqTemplateLineNumTbl(i)
AND org_id = gDMDOrgIdTbl(i)
AND language = gDMDLanguageTbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqMandatoryBaseRow;
'Num. of rows deleted from ctx_dtls for mandatory row changes:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_SUPPLIER_ROW_CTX_DTLS';
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id = gDSDInventoryItemIdTbl(i)
AND po_line_id = gDSDPoLineIdTbl(i)
AND req_template_name = gDSDReqTemplateNameTbl(i)
AND req_template_line_num = gDSDReqTemplateLineNumTbl(i)
AND org_id = gDSDOrgIdTbl(i)
AND language = gDSDLanguageTbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSupplierRow;
'Num. of rows deleted from ctx_dtls for supplier row changes:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_ITEMREV_ROW_CTX_DTLS';
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id = gDIRDInventoryItemIdTbl(i)
AND po_line_id = gDIRDPoLineIdTbl(i)
AND req_template_name = gDIRDReqTemplateNameTbl(i)
AND req_template_line_num = gDIRDReqTemplateLineNumTbl(i)
AND org_id = gDIRDOrgIdTbl(i)
AND language = gDIRDLanguageTbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForItemRevisionRow;
'Num. of rows deleted from ctx_dtls for item revision row changes:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_SHOPCATG_ROW_CTX_DTLS';
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id = gDSCDInventoryItemIdTbl(i)
AND po_line_id = gDSCDPoLineIdTbl(i)
AND req_template_name = gDSCDReqTemplateNameTbl(i)
AND req_template_line_num = gDSCDReqTemplateLineNumTbl(i)
AND org_id = gDSCDOrgIdTbl(i)
AND language = gDSCDLanguageTbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForShoppingCategoryRow;
'Num. of rows deleted from ctx_dtls for shop category row changes:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_PURCHORG_ROW_CTX_DTLS';
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id = gDPODInventoryItemIdTbl(i)
AND po_line_id = gDPODPoLineIdTbl(i)
AND req_template_name = gDPODReqTemplateNameTbl(i)
AND req_template_line_num = gDPODReqTemplateLineNumTbl(i)
AND org_id = gDPODOrgIdTbl(i)
AND language = gDPODLanguageTbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForPurchasingOrgIdRow;
'Num. of rows deleted from ctx_dtls for purch org row changes:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_SPECIFIC_CTX_DTLS';
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id = gDBLDInventoryItemIdTbl(i)
AND po_line_id = gDBLDPoLineIdTbl(i)
AND req_template_name = gDBLDReqTemplateNameTbl(i)
AND req_template_line_num = gDBLDReqTemplateLineNumTbl(i)
AND org_id = gDBLDOrgIdTbl(i)
AND language = gDBLDLanguageTbl(i)
AND ((sequence BETWEEN l_start_sequence AND l_end_sequence) OR SEQUENCE = 3);
'Num. of rows deleted from ctx_dtls for base and local row changes:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_CTX_DTLS';
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name, req_template_line_num,
org_id, language, sequence, ctx_desc,
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(gIDInventoryItemIdTbl(i), gIDPoLineIdTbl(i), gIDReqTemplateNameTbl(i), gIDReqTemplateLineNumTbl(i),
gIDOrgIdTbl(i), gIDLanguageTbl(i), 10000, '',
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);
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name, req_template_line_num,
org_id, language, sequence, ctx_desc,
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(gIDInventoryItemIdTbl(i), gIDPoLineIdTbl(i), gIDReqTemplateNameTbl(i), gIDReqTemplateLineNumTbl(i),
gIDOrgIdTbl(i), gIDLanguageTbl(i), 10001, to_char(gIDOrgIdTbl(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);
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name, req_template_line_num,
org_id, language, sequence, ctx_desc,
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(gIDInventoryItemIdTbl(i), gIDPoLineIdTbl(i), gIDReqTemplateNameTbl(i), gIDReqTemplateLineNumTbl(i),
gIDOrgIdTbl(i), gIDLanguageTbl(i), 15000, '',
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);
'Num. of rows inserted into ctx_dtls for org row:' ||SQL%ROWCOUNT);
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name, req_template_line_num,
org_id, language, sequence, ctx_desc,
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(gIDInventoryItemIdTbl(i), gIDPoLineIdTbl(i), gIDReqTemplateNameTbl(i), gIDReqTemplateLineNumTbl(i),
gIDOrgIdTbl(i), gIDLanguageTbl(i), 15001, '' || to_char(gIDPurchasingOrgIdTbl(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);
'Num. of rows inserted into ctx_dtls for purch_org row:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_PURCHORG_ROW_CTX_DTLS';
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name, req_template_line_num,
org_id, language, sequence, ctx_desc,
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(gDPODInventoryItemIdTbl(i), gDPODPoLineIdTbl(i), gDPODReqTemplateNameTbl(i), gDPODReqTemplateLineNumTbl(i),
gDPODOrgIdTbl(i), gDPODLanguageTbl(i), 15001, '' || to_char(gDPODPurchasingOrgIdTbl(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);
'Num. of rows inserted into ctx_dtls for purch_org row:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_CTX_DTLS';
', for seq:' || l_sequence || ', Num. of rows inserted into dtls:' || l_status);
l_action_mode := 'DELETE_MANDATORY_ROW_CTX_DTLS';
', for seq:' || l_sequence || ', Num. of rows inserted into dtls:' || l_status);
l_action_mode := 'DELETE_SUPPLIER_ROW_CTX_DTLS';
', for seq:' || l_sequence || ', Num. of rows inserted into dtls:' || l_status);
l_action_mode := 'DELETE_SPECIFIC_CTX_DTLS';
', for seq:' || l_sequence || ', Num. of rows inserted into dtls:' || l_status);
l_action_mode := 'DELETE_ITEMREV_ROW_CTX_DTLS';
', for seq:' || l_sequence || ', Num. of rows inserted into dtls:' || l_status);
l_action_mode := 'DELETE_SHOPCATG_ROW_CTX_DTLS';
', for seq:' || l_sequence || ', Num. of rows inserted into dtls:' || l_status);
l_action_mode := 'INSERT_CTX_DTLS';
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name,
req_template_line_num, org_id, 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,
sequence, ctx_desc)
SELECT inventory_item_id, po_line_id, req_template_name,
req_template_line_num, gIDOrgIdTbl(i), language,
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,
sequence, ctx_desc
FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id = gIDInventoryItemIdTbl(i)
AND po_line_id = gIDPoLineIdTbl(i)
AND req_template_name = gIDReqTemplateNameTbl(i)
AND req_template_line_num = gIDReqTemplateLineNumTbl(i)
AND org_id = gIDOwningOrgIdTbl(i)
AND language = gIDLanguageTbl(i)
AND sequence BETWEEN l_start_sequence AND l_end_sequence;
'Num. of rows inserted into ctx_dtls for GBPA with seq between 2 - 5000 row:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_SPECIFIC_CTX_DTLS';
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name,
req_template_line_num, org_id, 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,
sequence, ctx_desc)
SELECT inventory_item_id, po_line_id, req_template_name,
req_template_line_num, gDBLDOrgIdTbl(i), language,
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,
sequence, ctx_desc
FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id = gDBLDInventoryItemIdTbl(i)
AND po_line_id = gDBLDPoLineIdTbl(i)
AND req_template_name = gDBLDReqTemplateNameTbl(i)
AND req_template_line_num = gDBLDReqTemplateLineNumTbl(i)
AND org_id = gDBLDOwningOrgIdTbl(i)
AND language = gDBLDLanguageTbl(i)
AND ((sequence BETWEEN l_start_sequence AND l_end_sequence) or
sequence =3);
'Num. of rows inserted into ctx_dtls for 101 - 5000 row:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_SUPPLIER_ROW_CTX_DTLS';
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name,
req_template_line_num, org_id, 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,
sequence, ctx_desc)
SELECT inventory_item_id, po_line_id, req_template_name,
req_template_line_num, gDSDOrgIdTbl(i), language,
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,
sequence, ctx_desc
FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id = gDSDInventoryItemIdTbl(i)
AND po_line_id = gDSDPoLineIdTbl(i)
AND req_template_name = gDSDReqTemplateNameTbl(i)
AND req_template_line_num = gDSDReqTemplateLineNumTbl(i)
AND org_id = gDSDOwningOrgIdTbl(i)
AND language = gDSDLanguageTbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSupplierRow;
'Num. of rows inserted into ctx_dtls for seq 2 row:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_ITEMREV_ROW_CTX_DTLS';
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name,
req_template_line_num, org_id, 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,
sequence, ctx_desc)
SELECT inventory_item_id, po_line_id, req_template_name,
req_template_line_num, gDIRDOrgIdTbl(i), language,
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,
sequence, ctx_desc
FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id = gDIRDInventoryItemIdTbl(i)
AND po_line_id = gDIRDPoLineIdTbl(i)
AND req_template_name = gDIRDReqTemplateNameTbl(i)
AND req_template_line_num = gDIRDReqTemplateLineNumTbl(i)
AND org_id = gDIRDOwningOrgIdTbl(i)
AND language = gDIRDLanguageTbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForItemRevisionRow;
'Num. of rows inserted into ctx_dtls for seq 5 row:' ||SQL%ROWCOUNT);
l_action_mode := 'DELETE_SHOPCATG_ROW_CTX_DTLS';
INSERT INTO icx_cat_items_ctx_dtls_tlp
(inventory_item_id, po_line_id, req_template_name,
req_template_line_num, org_id, 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,
sequence, ctx_desc)
SELECT inventory_item_id, po_line_id, req_template_name,
req_template_line_num, gDSCDOrgIdTbl(i), language,
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,
sequence, ctx_desc
FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id = gDSCDInventoryItemIdTbl(i)
AND po_line_id = gDSCDPoLineIdTbl(i)
AND req_template_name = gDSCDReqTemplateNameTbl(i)
AND req_template_line_num = gDSCDReqTemplateLineNumTbl(i)
AND org_id = gDSCDOwningOrgIdTbl(i)
AND language = gDSCDLanguageTbl(i)
AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForShoppingCategoryRow;
'Num. of rows inserted into ctx_dtls for seq 6 row:' ||SQL%ROWCOUNT);
l_action_mode := 'INSERT_CTX_DTLS';
', Num. of rows inserted into dtls:' || l_status);
l_action_mode := 'DELETE_SPECIFIC_CTX_DTLS';
', Num. of rows inserted into dtls:' || l_status);
l_rowid_tbl.DELETE;
UPDATE icx_cat_items_ctx_hdrs_tlp
SET ctx_desc = NULL,
supplier = p_vendor_name,
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 supplier_id IN (SELECT vendor_id
FROM po_vendors
WHERE party_id = p_vendor_party_id)
AND internal_request_id <> ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id
AND ROWNUM <= ICX_CAT_UTIL_PVT.g_batch_size
RETURNING ROWID BULK COLLECT INTO l_rowid_tbl;
'Num of rows updated in ctx_hdrs for supplier name change:' || l_row_count);
UPDATE icx_cat_items_ctx_dtls_tlp dtls
SET ctx_desc = '<' || l_section_tag || '>' || replace(replace(p_vendor_name, '<', ' '), '>', ' ') || '',
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 sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSupplierRow
AND EXISTS ( SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.po_line_id = dtls.po_line_id
AND hdrs.req_template_name = dtls.req_template_name
AND hdrs.req_template_line_num = dtls.req_template_line_num
AND hdrs.inventory_item_id = dtls.inventory_item_id
AND hdrs.org_id = dtls.org_id
AND hdrs.language = dtls.language
AND hdrs.rowid = l_rowid_tbl(i) );
'Num of rows updated in ctx_dtls for supplier name change:' ||SQL%ROWCOUNT);
l_rowid_tbl.DELETE;
UPDATE icx_cat_items_ctx_hdrs_tlp
SET supplier = p_vendor_name,
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 supplier_id IN (SELECT vendor_id
FROM po_vendors
WHERE party_id = p_vendor_party_id)
AND internal_request_id <> ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id
AND ROWNUM <= ICX_CAT_UTIL_PVT.g_batch_size;
'Num of rows updated in ctx_hdrs for supplier name change:' || l_row_count);
'Only update the supplier name on the header; Supplier is not searchable l_searchable:' || l_searchable );
1. update supplier_id, supplier_site_id and ctx_desc in icx_cat_items_ctx_hdrs_tlp
2. delete and insert into icx_cat_items_ctx_dtls_tlp : sequence 1 for supid and siteid
3. Check if supplier is searchable, if yes then update icx_cat_items_ctx_dtls_tlp : sequence 2 for supplier
4. call rebuild_index.
*/
PROCEDURE populateVendorMerge
( p_from_vendor_id IN NUMBER ,
p_from_site_id IN NUMBER ,
p_to_vendor_id IN NUMBER ,
p_to_site_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'populateVendorMerge';
l_rows_updated BOOLEAN := TRUE;
SELECT vendor_name
INTO l_to_vendor_name
FROM po_vendors
WHERE vendor_id = p_to_vendor_id;
l_rowid_tbl.DELETE;
UPDATE icx_cat_items_ctx_hdrs_tlp
SET ctx_desc = NULL,
supplier_id = p_to_vendor_id,
supplier_site_id = p_to_site_id,
supplier = l_to_vendor_name,
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 supplier_id = p_from_vendor_id
AND supplier_site_id = p_from_site_id
AND internal_request_id <> ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id
AND ROWNUM <= ICX_CAT_UTIL_PVT.g_batch_size
RETURNING ROWID BULK COLLECT INTO l_rowid_tbl;
'Num of rows updated in ctx_hdrs for vendor merge:' || l_row_count);
DELETE FROM icx_cat_items_ctx_dtls_tlp dtls
WHERE sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqMandatoryBaseRow
AND EXISTS ( SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.po_line_id = dtls.po_line_id
AND hdrs.req_template_name = dtls.req_template_name
AND hdrs.req_template_line_num = dtls.req_template_line_num
AND hdrs.inventory_item_id = dtls.inventory_item_id
AND hdrs.org_id = dtls.org_id
AND hdrs.language = dtls.language
AND hdrs.rowid = l_rowid_tbl(i) );
'Num. of rows deleted from dtls for seq=1:' || SQL%ROWCOUNT);
'Num. of rows inserted into dtls for seq=1:' || l_status);
UPDATE icx_cat_items_ctx_dtls_tlp dtls
SET ctx_desc = '<' || l_metadata_rec.section_tag || '>' ||
replace(replace(l_to_vendor_name, '<', ' '), '>', ' ') ||
'',
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 sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSupplierRow
AND EXISTS ( SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.po_line_id = dtls.po_line_id
AND hdrs.req_template_name = dtls.req_template_name
AND hdrs.req_template_line_num = dtls.req_template_line_num
AND hdrs.inventory_item_id = dtls.inventory_item_id
AND hdrs.org_id = dtls.org_id
AND hdrs.language = dtls.language
AND hdrs.rowid = l_rowid_tbl(i) );
SELECT mtlb.category_id
FROM mtl_categories_b mtlb
WHERE mtlb.structure_id = ICX_CAT_UTIL_PVT.g_structure_id
AND (NVL(mtlb.end_date_active, SYSDATE + 1) < SYSDATE
OR NVL(mtlb.disable_date, SYSDATE + 1) < SYSDATE)
AND EXISTS (SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.po_category_id = mtlb.category_id
AND hdrs.source_type = 'MASTER_ITEM');
SELECT mtlb.category_id
FROM mtl_categories_b mtlb,
mtl_category_set_valid_cats mcsvc
WHERE mtlb.structure_id = ICX_CAT_UTIL_PVT.g_structure_id
AND (NVL(mtlb.end_date_active, SYSDATE + 1) < SYSDATE
OR NVL(mtlb.disable_date, SYSDATE + 1) < SYSDATE)
AND mcsvc.category_set_id = ICX_CAT_UTIL_PVT.g_category_set_id
AND mcsvc.category_id = mtlb.category_id
AND EXISTS (SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.po_category_id = mtlb.category_id
AND hdrs.source_type = 'MASTER_ITEM');
l_po_category_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_org_id_tbl.DELETE;
l_language_tbl.DELETE;
DELETE FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.source_type = 'MASTER_ITEM'
AND po_category_id = l_po_category_id_tbl(i)
AND ROWNUM <= ICX_CAT_UTIL_PVT.g_batch_size
RETURNING po_line_id, req_template_name, req_template_line_num,
inventory_item_id, org_id, language
BULK COLLECT INTO l_po_line_id_tbl, l_req_template_name_tbl, l_req_template_line_num_tbl,
l_inventory_item_id_tbl, l_org_id_tbl, l_language_tbl;
'No rows deleted from ctx_hdrs for invalid category, so exit out of the loop;');
'Num. of rows deleted from ctx_hdrs for invalid category:' || l_row_count);
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(j)
AND req_template_name = l_req_template_name_tbl(j)
AND req_template_line_num = l_req_template_line_num_tbl(j)
AND inventory_item_id = l_inventory_item_id_tbl(j)
AND org_id = l_org_id_tbl(j)
AND language = l_language_tbl(j);
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_org_id_tbl.DELETE;
l_language_tbl.DELETE;
DELETE FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.source_type IN ('TEMPLATE', 'INTERNAL_TEMPLATE')
AND (
-- Req template lines that are invalid.
EXISTS ( SELECT 'x'
FROM po_reqexpress_lines_all prl, po_reqexpress_headers_all prh,
po_lines_all pl, po_headers_all ph
WHERE hdrs.po_line_id = -2
AND hdrs.inventory_item_id = nvl(prl.item_id, -2)
AND hdrs.req_template_name = prl.express_name
AND hdrs.req_template_line_num = prl.sequence_num
AND hdrs.org_id = prl.org_id
AND prl.express_name = prh.express_name
AND prl.org_id = prh.org_id
AND prl.po_line_id = pl.po_line_id (+)
AND prl.po_header_id = pl.po_header_id (+)
AND pl.po_header_id = ph.po_header_id (+)
AND (NVL(prh.inactive_date, SYSDATE + 1) <= SYSDATE
OR (prl.po_line_id IS NOT NULL AND
(ph.approved_date IS NULL
OR NVL(ph.authorization_status, 'INCOMPLETE') IN ('REJECTED', 'INCOMPLETE')
OR NVL(ph.cancel_flag, 'N') = 'Y'
OR NVL(ph.frozen_flag, 'N') = 'Y'
OR NVL(ph.closed_code, 'OPEN') IN ('CLOSED', 'FINALLY CLOSED')
OR NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1)) <= TRUNC(SYSDATE)
OR NVL(pl.cancel_flag, 'N') = 'Y'
OR NVL(pl.closed_code, 'OPEN') IN ('CLOSED', 'FINALLY CLOSED')
OR NVL(TRUNC(pl.expiration_date), TRUNC(SYSDATE + 1)) <= TRUNC(SYSDATE)))))
OR
-- Req template lines that are deleted.
NOT EXISTS ( SELECT 'x'
FROM po_reqexpress_lines_all prl
WHERE hdrs.po_line_id = -2
AND hdrs.inventory_item_id = nvl(prl.item_id, -2)
AND hdrs.req_template_name = prl.express_name
AND hdrs.req_template_line_num = prl.sequence_num
AND hdrs.org_id = prl.org_id)
)
AND ROWNUM <= ICX_CAT_UTIL_PVT.g_batch_size
RETURNING po_line_id, req_template_name, req_template_line_num,
inventory_item_id, org_id, language
BULK COLLECT INTO l_po_line_id_tbl, l_req_template_name_tbl, l_req_template_line_num_tbl,
l_inventory_item_id_tbl, l_org_id_tbl, l_language_tbl;
'no rows deleted from ctx_hdrs for invalid req tmplts, so exit from the loop');
'Num. of rows deleted from ctx_hdrs for invalid req templates:' || l_row_count);
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(j)
AND req_template_name = l_req_template_name_tbl(j)
AND req_template_line_num = l_req_template_line_num_tbl(j)
AND inventory_item_id = l_inventory_item_id_tbl(j)
AND org_id = l_org_id_tbl(j)
AND language = l_language_tbl(j);
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_org_id_tbl.DELETE;
l_language_tbl.DELETE;
DELETE FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.source_type = 'QUOTATION'
AND (
-- Quote lines that are invalid.
ICX_CAT_POPULATE_STATUS_PVT.getQuoteLineStatus(hdrs.po_line_id) = -1
OR
-- Quote lines that are deleted.
NOT EXISTS ( SELECT 'x' FROM po_lines_all pl
WHERE hdrs.po_line_id = pl.po_line_id)
)
AND ROWNUM <= ICX_CAT_UTIL_PVT.g_batch_size
RETURNING po_line_id, req_template_name, req_template_line_num,
inventory_item_id, org_id, language
BULK COLLECT INTO l_po_line_id_tbl, l_req_template_name_tbl, l_req_template_line_num_tbl,
l_inventory_item_id_tbl, l_org_id_tbl, l_language_tbl;
'no rows deleted from ctx_hdrs for invalid Quote lines, so exit from the loop');
'Num. of rows deleted from ctx_hdrs for invalid Quote lines:' || l_row_count);
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(j)
AND req_template_name = l_req_template_name_tbl(j)
AND req_template_line_num = l_req_template_line_num_tbl(j)
AND inventory_item_id = l_inventory_item_id_tbl(j)
AND org_id = l_org_id_tbl(j)
AND language = l_language_tbl(j);
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_org_id_tbl.DELETE;
l_language_tbl.DELETE;
DELETE FROM icx_cat_items_ctx_hdrs_tlp hdrs
WHERE hdrs.source_type IN ('BLANKET', 'GLOBAL_BLANKET')
AND EXISTS ( SELECT 'x' FROM po_lines_all pl, po_headers_all ph
WHERE hdrs.po_line_id = pl.po_line_id
AND pl.po_header_id = ph.po_header_id
AND (ph.approved_date IS NULL
OR NVL(ph.authorization_status, 'INCOMPLETE') IN ('REJECTED', 'INCOMPLETE')
OR NVL(ph.cancel_flag, 'N') = 'Y'
OR NVL(ph.frozen_flag, 'N') = 'Y'
OR NVL(ph.closed_code, 'OPEN') IN ('CLOSED', 'FINALLY CLOSED')
OR NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1)) <= TRUNC(SYSDATE)
OR NVL(pl.cancel_flag, 'N') = 'Y'
OR NVL(pl.closed_code, 'OPEN') IN ('CLOSED', 'FINALLY CLOSED')
OR NVL(TRUNC(pl.expiration_date), TRUNC(SYSDATE + 1)) <= TRUNC(SYSDATE)))
AND ROWNUM <= ICX_CAT_UTIL_PVT.g_batch_size
RETURNING po_line_id, req_template_name, req_template_line_num,
inventory_item_id, org_id, language
BULK COLLECT INTO l_po_line_id_tbl, l_req_template_name_tbl, l_req_template_line_num_tbl,
l_inventory_item_id_tbl, l_org_id_tbl, l_language_tbl;
'no rows deleted from ctx_hdrs for invalid blanket lines, so exit from the loop');
'Num. of rows deleted from ctx_hdrs for invalid blanket lines:' || l_row_count);
DELETE FROM icx_cat_items_ctx_dtls_tlp
WHERE po_line_id = l_po_line_id_tbl(j)
AND req_template_name = l_req_template_name_tbl(j)
AND req_template_line_num = l_req_template_line_num_tbl(j)
AND inventory_item_id = l_inventory_item_id_tbl(j)
AND org_id = l_org_id_tbl(j)
AND language = l_language_tbl(j);