[Home] [Help]
566: IF (NOT get_category_name%NOTFOUND) THEN
567: xErrLoc := 400;
568:
569: build_category_sql(v_current_catid, v_category_info,
570: 'ICX_CAT_ITEMS_GT');
571: gCategoryInfo(v_current_catid) := v_category_info;
572: END IF;
573:
574: CLOSE get_category_name;
796: it.supplier_site_code, it.price_list_id, gJobNumber, gUserId, sysdate, gUserId,
797: gUserLogin, sysdate , 1,
798: it.contract_num, it.contract_id, -- OEX_IP_PORTING
799: gNegotiatedPrice --Bug#3107596
800: FROM ICX_CAT_ITEMS_GT it
801: WHERE rowid = gRowIds(i)
802: AND gRowTypes(i) = p_batch_type
803: AND gSystemActions(i) = v_action;
804:
982: it.unit_price, it.currency, it.unit_of_measure,
983: it.supplier_site_code, it.price_list_id, gJobNumber, gUserId, sysdate, gUserId,
984: gUserLogin, sysdate, it.contract_num, it.contract_id , 1,
985: gNegotiatedPrice --Bug#3107596
986: FROM ICX_CAT_ITEMS_GT it
987: WHERE rowid = gRowIds(i)
988: AND gRowTypes(i) = p_batch_type
989: AND gSystemActions(i) = p_action
990: AND v_price_updated(i) = 0;
1002: it.supplier_site_code, gJobNumber, gUserId, gUserLogin, sysdate,
1003: it.contract_num, it.contract_id, 1,
1004: gPriceTypes(i), --Bug#3503280
1005: gNegotiatedPrice --Bug#3107596
1006: FROM ICX_CAT_ITEMS_GT it
1007: WHERE rowid = gRowIds(i)
1008: AND gRowTypes(i) = p_batch_type
1009: AND gSystemActions(i) = p_action
1010: AND v_price_updated(i) = 1)
1258: SELECT gItemIds(i), it.org_id, it.supplier_id, it.supplier,
1259: it.supplier_part_num, it.supplier_part_auxid, gCatalogName, --Bug#2611529
1260: 'N', gJobNumber, gUserId, sysdate, gUserId,
1261: gUserLogin, sysdate, 1
1262: FROM ICX_CAT_ITEMS_GT it
1263: WHERE it.rowid = gRowids(i)
1264: AND v_action = gSystemActions(i);
1265:
1266: xErrLoc := 200;
1490: it.tl_text_base_attribute94, it.tl_text_base_attribute95, it.tl_text_base_attribute96,
1491: it.tl_text_base_attribute97, it.tl_text_base_attribute98, it.tl_text_base_attribute99,
1492: it.tl_text_base_attribute100,
1493: null, gJobNumber, gUserId, sysdate, gUserId, gUserLogin, sysdate
1494: FROM ICX_CAT_ITEMS_GT it
1495: WHERE it.rowid = gRowids(i)
1496: AND v_action = gSystemActions(i);
1497:
1498: xErrLoc := 300;
1608: it.tl_text_cat_attribute43, it.tl_text_cat_attribute44, it.tl_text_cat_attribute45,
1609: it.tl_text_cat_attribute46, it.tl_text_cat_attribute47, it.tl_text_cat_attribute48,
1610: it.tl_text_cat_attribute49, it.tl_text_cat_attribute50,
1611: gUserId, sysdate, gUserId, gUserLogin, sysdate
1612: FROM ICX_CAT_ITEMS_GT it
1613: WHERE it.rowid = gRowids(i)
1614: AND v_action = gSystemActions(i);
1615:
1616: xErrLoc := 500;
2347: END fetch_local_descriptors;
2348:
2349: /* Saves a failed item */
2350: PROCEDURE save_failed_item(pJobNumber IN NUMBER,
2351: p_failed_item IN ICX_CAT_ITEMS_GT%ROWTYPE) IS
2352: v_rec tITRowRecord;
2353: i PLS_INTEGER;
2354: xErrLoc PLS_INTEGER;
2355: vDescKey ICX_POR_FAILED_LINES.DESCRIPTOR_KEY%TYPE;
3339: PROCEDURE process_batch_item_errors(pErrorRowids IN
3340: dbms_sql.urowid_table) IS
3341: i PLS_INTEGER;
3342: CURSOR get_failed_line(p_rowid IN VARCHAR2) IS
3343: SELECT * FROM ICX_CAT_ITEMS_GT
3344: WHERE rowid = p_rowid;
3345: v_rec ICX_CAT_ITEMS_GT%ROWTYPE;
3346: xErrLoc PLS_INTEGER := 100;
3347: BEGIN
3341: i PLS_INTEGER;
3342: CURSOR get_failed_line(p_rowid IN VARCHAR2) IS
3343: SELECT * FROM ICX_CAT_ITEMS_GT
3344: WHERE rowid = p_rowid;
3345: v_rec ICX_CAT_ITEMS_GT%ROWTYPE;
3346: xErrLoc PLS_INTEGER := 100;
3347: BEGIN
3348:
3349: FOR i IN 1..pErrorRowids.COUNT LOOP
3400: p_error_message IN VARCHAR2) IS
3401:
3402: BEGIN
3403: IF (p_row_type = 'ITEM') THEN
3404: UPDATE ICX_CAT_ITEMS_GT SET error_message = error_message ||
3405: p_error_message
3406: WHERE rowid = p_row_id;
3407: ELSE
3408: UPDATE ICX_CAT_PRICES_GT SET error_message = error_message ||
3764: -- Bug#2611529
3765: CURSOR it_table_csr(p_language IN VARCHAR2) IS
3766: -- Bug# 3366614 sosingha: include supplier_site_id also for Item Uniqueness check
3767: SELECT it.rowid, it.supplier, it.supplier_part_num, it.supplier_part_auxid, it.org_id, row_type, it.supplier_site_id, it.currency
3768: FROM ICX_CAT_ITEMS_GT it
3769: WHERE language = p_language
3770: ORDER BY supplier asc, supplier_part_num asc, supplier_part_auxid asc, org_id asc, supplier_site_id asc, currency asc, line_number desc;
3771:
3772: vRowids dbms_sql.urowid_table;
3867:
3868: xErrLoc := 400;
3869: -- Bug# 3366614 sosingha: Update rows with duplicates values for 4 primary item uniqueness attributes with processed flag as 'D'
3870: FOR i in 1..vDupeRowIds.COUNT LOOP
3871: UPDATE ICX_CAT_ITEMS_GT
3872: SET PROCESSED_FLAG = 'D'
3873: WHERE ROWID = vDupeRowIds(i);
3874: COMMIT;
3875: END LOOP;
3939: decode(it.required_tl_descriptors, NULL, 'N', 'Y'),
3940: it.unit_price,
3941: it.unit_of_measure
3942: ,it.system_action
3943: FROM ICX_CAT_ITEMS_GT it,
3944: icx_cat_items_b i, --Bug#2714487: dont join with category_items
3945: icx_cat_items_tlp tl
3946: WHERE it.supplier = i.supplier (+)
3947: AND it.supplier_part_num = i.supplier_part_num (+)
3998: p_succ_line := 0;
3999: p_failed_line := 0;
4000:
4001: xErrLoc := 200;
4002: build_root_sql('ICX_CAT_ITEMS_GT');
4003: xErrLoc := 300;
4004:
4005: /* Bug# 3366614 sosingha: move this call to move_data as we have to always call this before calling move_items
4006: -- Reject all the duplicates first
4180: IF (vErrorCount > 0) THEN
4181: xErrLoc := 1800;
4182: -- Need to first populate the system action column in the it table
4183: FORALL i IN 1..vRowids.COUNT
4184: UPDATE ICX_CAT_ITEMS_GT
4185: SET system_action = vSystemActions(i)
4186: WHERE rowid = vRowids(i)
4187: AND vHasErrors(i) = 'Y';
4188:
4199: -- Update the processed_flag to null
4200: ELSE
4201: xErrLoc := 2100;
4202: FORALL i IN 1..vRowids.COUNT
4203: UPDATE ICX_CAT_ITEMS_GT
4204: SET processed_flag = 'Y' --Bug#2958208: set to "Y" instead of null.
4205: WHERE rowid = vRowids(i);
4206:
4207: COMMIT;
4339:
4340: clear_all;
4341:
4342: IF (p_data_type = 'ITEM') THEN
4343: vTableName := 'ICX_CAT_ITEMS_GT';
4344: ELSIF (p_data_type = 'PRICE') THEN
4345: vTableName := 'ICX_CAT_PRICES_GT';
4346: ELSE
4347: p_failed_lines_saved_count := 0;