ORACLE MASTERMINDS

Item Category Conversion

Categories are the method by which the items in inventory can be separated logically and functionally for planning, purchasing and other activities.You can use categories and category sets to group your items for various reports and programs. A category is a logical classification of items that have similar characteristics. A category set is a distinct grouping scheme and consists of categories. The flexibility of category sets allows you to report and inquire on items in a way that best suits your needs. This article will describe how to create categories and category set in oracle inventory.

Step1: Sample script to fetch the Item Category Conversion detail is given below.

PROCEDURE item_category_conv (errbut OUT VARCHAR2

,retcode OUT NUMBER

,p_org_id IN NUMBER

,p_log_level IN NUMBER

)

l_category_rec INV_ITEM_CATEGORY_PUB.category_rec_type;

l_api_version NUMBER;

o_return_status VARCHAR2(10);

o_msg_count NUMBER;

o_msg_data VARCHAR2(20000);

o_errorcode VARCHAR2(20000);

l_error_code VARCHAR2(20000);

l_error_desc VARCHAR2(20000);

l_conv_status VARCHAR2(20000);

v_category_id NUMBER;

l_count NUMBER;

l_err_count NUMBER;

ln_count NUMBER;

x_msg VARCHAR2(20000);

lc_status_flag VARCHAR2(1);

--l_msg_data VARCHAR2(20000);

CURSOR CAT_STRUCTURE_CUR

SELECT structure_id, category_set_name

FROM mtl_category_sets_vl

WHERE category_set_name IN (SELECT DISTINCT category_type

FROM xx_cnv_item_category

WHERE NVL(x_record_status,'X') != 'IAS'

)

ORDER BY 1;

CURSOR CAT_CUR(p_cat_type VARCHAR2)

SELECT DISTINCT ROWID,category_type,

(UPPER(TRIM(cat_segment1))) cat_segment1,

UPPER((TRIM(cat_segment2))) cat_segment2,

((TRIM(cat_segment3))) cat_segment3,

UPPER((TRIM(cat_segment4))) cat_segment4,

UPPER((TRIM(cat_segment5))) cat_segment5,

UPPER((TRIM(cat_segment6))) cat_segment6,

UPPER((TRIM(cat_segment7))) cat_segment7,

UPPER((TRIM(cat_segment8))) cat_segment8,

UPPER((TRIM(cat_segment9))) cat_segment9,

UPPER((TRIM(cat_segment10))) cat_segment10

FROM xx_CNV_ITEM_CATEGORY

WHERE category_type = p_cat_type

AND NVL(x_record_status,'X') != 'IAS'

ORDER BY 1;

BEGIN

fnd_global.apps_initialize ( user_id => gn_user_id ---1 -- ANONYMOUS

,resp_id => gn_resp_id --31175 -- W (BG)_Inventory -- 50791

,resp_appl_id => gn_resp_appl_id --401); -- Inventory

);

mo_global.init ('INV');

gn_inv_org_id := p_org_id;

BEGIN

SELECT operating_unit

INTO gn_org_id

FROM org_organization_definitions

WHERE organization_id = p_org_id;

EXCEPTION WHEN OTHERS

THEN

gn_org_id := 82;

END;

IF p_log_level IS NOT NULL THEN

g_log_level := p_log_level;

END IF;

--fnd_file.put_line (fnd_file.LOG,'g_log_level->'||g_log_level);

/* Setting the org context for the particular session */

mo_global.set_policy_context('S',gn_org_id);

--fnd_client_info.set_org_context (gn_org_id);

LOG(1,'Setting the Context');

fnd_file.put_line (fnd_file.LOG,'gn_org_id->'||gn_org_id);

l_count := 0;

l_err_count := 0;

FOR lcr_cat_structure_rec IN cat_structure_cur

LOOP

FOR lcr_cat_rec IN cat_cur (lcr_cat_structure_rec.category_set_name)

LOOP

x_msg := NULL;

lc_status_flag := 'S';

IF LENGTH(LCR_CAT_REC.cat_segment1)>40 OR LENGTH(LCR_CAT_REC.cat_segment2)>40 OR LENGTH(LCR_CAT_REC.cat_segment3)>40 OR LENGTH(LCR_CAT_REC.cat_segment4)>40

OR LENGTH(LCR_CAT_REC.cat_segment5)>40 OR LENGTH(LCR_CAT_REC.cat_segment6)>40 OR LENGTH(LCR_CAT_REC.cat_segment7)>40

THEN

lc_status_flag := 'E';

x_msg := 'Segment values should not exceed 40 Char';

END IF;

IF lc_status_flag != 'E'

THEN

ln_count := 0;

BEGIN

SELECT count(1)

INTO ln_count

FROM mtl_category_sets_vl MCS

,mtl_categories_v MC

WHERE MCS.structure_id = MC.structure_id

AND MCS.category_set_name = LCR_CAT_REC.category_type

AND UPPER(MC.segment1) = UPPER(LCR_CAT_REC.cat_segment1)

AND UPPER(MC.segment2) = UPPER(LCR_CAT_REC.cat_segment2)

AND UPPER(NVL(MC.segment3,'X')) = UPPER(NVL(LCR_CAT_REC.cat_segment3,'X'))

AND UPPER(NVL(MC.segment4,'X')) = UPPER(NVL(LCR_CAT_REC.cat_segment4,'X'))

AND UPPER(NVL(MC.segment5,'X')) = UPPER(NVL(LCR_CAT_REC.cat_segment5,'X'))

AND UPPER(NVL(MC.segment6,'X')) = UPPER(NVL(LCR_CAT_REC.cat_segment6,'X'))

AND UPPER(NVL(MC.segment7,'X')) = UPPER(NVL(LCR_CAT_REC.cat_segment7,'X'));

END;

IF ln_count = 0

THEN

l_category_rec := NULL;

l_category_rec.structure_id := lcr_cat_structure_rec.structure_id;

-- l_category_rec.structure_code := g_cat_flex_code;

l_category_rec.summary_flag := 'N';

l_category_rec.enabled_flag := 'Y';

l_category_rec.segment1 := lcr_cat_rec.cat_segment1;

l_category_rec.segment2 := lcr_cat_rec.cat_segment2;

l_category_rec.segment3 := lcr_cat_rec.cat_segment3;

l_category_rec.segment4 := lcr_cat_rec.cat_segment4;

l_category_rec.segment5 := lcr_cat_rec.cat_segment5;

l_category_rec.segment6 := lcr_cat_rec.cat_segment6;

l_category_rec.segment7 := lcr_cat_rec.cat_segment7;

l_category_rec.segment8 := lcr_cat_rec.cat_segment8;

l_category_rec.segment9 := lcr_cat_rec.cat_segment9;

l_category_rec.segment10 := lcr_cat_rec.cat_segment10;

INV_ITEM_CATEGORY_PUB.create_category (p_api_version => 1.0

, p_init_msg_list => fnd_api.g_TRUE

, p_commit => fnd_api.g_true

, x_return_status => o_return_status

, x_errorcode => o_errorcode

, x_msg_count => o_msg_count

, x_msg_data => o_msg_data

, p_category_rec => l_category_rec

, x_category_id => v_category_id);

IF o_return_status = 'S'

THEN

l_count := l_count + 1;

UPDATE xx_cnv_item_category

SET x_record_status = 'ICS'

,x_record_msg = 'Category Creation Successful'

WHERE rowid = lcr_cat_rec.rowid;

ELSE

l_err_count := l_err_count + 1;

FOR i IN 1 .. fnd_msg_pub.count_msg

LOOP

x_msg := x_msg || fnd_msg_pub.get (p_msg_index => i,

p_encoded => fnd_api.g_false

);

END LOOP;

fnd_file.put_line (fnd_file.LOG, 'API error :'||LCR_CAT_REC.category_type||'~~'||LCR_CAT_REC.CAT_SEGMENT1||'~~'||LCR_CAT_REC.CAT_SEGMENT2||'~~'

|| x_msg

);

UPDATE xx_cnv_item_category

SET x_record_status = 'ICE'

,x_record_msg = x_msg

WHERE rowid = lcr_cat_rec.rowid;

END IF;

ELSE

UPDATE xx_cnv_item_category

SET x_record_status = 'ICS'

,x_record_msg = 'Category Already Exists'

WHERE rowid = lcr_cat_rec.rowid;

END IF;

ELSE

UPDATE xx_cnv_item_category

SET x_record_status = 'ICE'

,x_record_msg = x_msg

WHERE rowid = lcr_cat_rec.rowid;

END IF;

END LOOP;

COMMIT;

LOG(1,'Valid Count: '||l_count);

LOG(1,'Error Count: '||l_err_count);

LOG(1,'*** Calling Assignment Procedure');

item_cat_assign_proc;

EXCEPTION

WHEN OTHERS

THEN

retcode := 1;

LOG(1,'Unexpected error @ item_category_conv -> '||SQLERRM);

END;

PROCEDURE ITEM_CAT_ASSIGN_PROC

l_template_id mtl_item_templates.template_id%TYPE;

l_locator_id mtl_item_locations_kfv.inventory_location_id%TYPE;

l_locator mtl_item_locations_kfv.concatenated_segments%TYPE;

l_asset_category fa_categories_b_kfv.category_id%TYPE;

l_item_type fnd_lookup_values.lookup_code%TYPE;

l_mrp_plan_code fnd_lookup_values.lookup_code%TYPE;

l_wip_type fnd_lookup_values.lookup_code%TYPE;

l_forecast_cntr fnd_lookup_values.lookup_code%TYPE;

l_inventory_plan fnd_lookup_values.lookup_code%TYPE;

l_assembly_pegg fnd_lookup_values.lookup_code%TYPE;

l_planning_time fnd_lookup_values.lookup_code%TYPE;

l_demand_time fnd_lookup_values.lookup_code%TYPE;

l_release_time fnd_lookup_values.lookup_code%TYPE;

l_bom_type fnd_lookup_values.lookup_code%TYPE;

l_reduce_mps fnd_lookup_values.lookup_code%TYPE;

l_overcomp_tol fnd_lookup_values.lookup_code%TYPE;

l_locator_cntr fnd_lookup_values.lookup_code%TYPE;

l_loc_cntr fnd_lookup_values.lookup_code%TYPE;

l_lot_shelf fnd_lookup_values.lookup_code%TYPE;

l_serv_code fnd_lookup_values.lookup_code%TYPE;

l_temp fnd_lookup_values.lookup_code%TYPE;

l_pln_time fnd_lookup_values.meaning%TYPE;

l_dem_time fnd_lookup_values.meaning%TYPE;

l_rel_time fnd_lookup_values.meaning%TYPE;

l_bom fnd_lookup_values.meaning%TYPE;

l_map_locator fnd_lookup_values.meaning%TYPE;

l_un_number po_un_numbers_tl.un_number_id%TYPE;

l_hazard_id po_hazard_classes_tl.hazard_class_id%TYPE;

l_wip_sub VARCHAR2 (1000);

l_so_sub VARCHAR2 (1000);

l_wip fnd_lookup_values.meaning%TYPE;

l_pick_rule mtl_picking_rules.picking_rule_id%TYPE;

l_pick_rl mtl_picking_rules.picking_rule_name%TYPE;

l_request_id fnd_concurrent_requests.request_id%TYPE;

l_atp_rule mtl_atp_rules.rule_id%TYPE;

l_atp_rl mtl_atp_rules.rule_name%TYPE;

l_payment_term ra_terms_tl.term_id%TYPE;

l_pay_tr ra_terms_tl.NAME%TYPE;

l_destination_org mtl_parameters.organization_code%TYPE;

l_inv_org mtl_parameters.organization_code%TYPE;

l_buyer_id po_agents_v.agent_id%TYPE;

l_forecast VARCHAR2 (1000);

l_asm_pegging VARCHAR2 (1000);

l_master_org mtl_parameters.organization_code%TYPE;

l_org_code mtl_parameters.organization_code%TYPE;

l_mast_org_id mtl_parameters.organization_id%TYPE;

l_org_id mtl_parameters.organization_id%TYPE;

l_buyer VARCHAR2 (100);

l_user_id fnd_user.user_id%TYPE;

l_temp_name mtl_item_templates.description%TYPE;

l_item_typ fnd_lookup_values.meaning%TYPE;

l_tax_code ap_tax_codes_all.NAME%TYPE;

l_tax_name ap_tax_codes_all.NAME%TYPE;

l_uom_code mtl_units_of_measure.unit_of_measure%TYPE;

l_uom_name mtl_units_of_measure.unit_of_measure%TYPE;

l_cover_temp oks_coverages_v.NAME%TYPE;

l_coverage_name oks_coverages_v.NAME%TYPE;

l_coverage_id oks_coverages_v.ID%TYPE;

l_revision mtl_item_revisions_interface.revision%TYPE;

l_volume_uom mtl_units_of_measure.uom_code%TYPE;

l_weight_uom mtl_units_of_measure.uom_code%TYPE;

l_planner mtl_planners.planner_code%TYPE;

l_exception_set mtl_system_items_interface.planning_exception_set%TYPE;

l_source_org mtl_parameters.organization_code%TYPE;

l_record_count NUMBER;

l_commit_count NUMBER;

l_error_count NUMBER;

l_prt_item_exist NUMBER;

l_duplicate_item NUMBER := 0;

l_item_exists NUMBER := 0;

l_err_msg VARCHAR2 (2000);

l_conv_id NUMBER;

l_val_count NUMBER := 0;

l_item_count NUMBER;

l_error_message VARCHAR2 (2000);

l_process_id mtl_system_items_interface.set_process_id%TYPE;

l_count NUMBER := 0;

l_org mtl_parameters.organization_id%TYPE;

l_source VARCHAR2 (2);

l_item_cnt NUMBER := 0;

l_valerr_count NUMBER := 0;

l_status VARCHAR2 (1);

------

l_inv_item_id NUMBER;

l_struc_id NUMBER;

l_cat_set_id NUMBER;

l_cat_id NUMBER;

l_trx_type VARCHAR2(30);

l_old_cat_id NUMBER;

CURSOR item_cat_cur

SELECT a.*, ROWID

FROM xx_cnv_item_category a

WHERE NVL(x_record_status,'X') = 'ICS';

BEGIN

LOG(1,'Start Item Category Assignment Valiation');

l_record_count := 0;

l_commit_count := 0;

l_error_count := 0;

l_error_message := NULL;

l_err_msg := NULL;

l_val_count := 0;

l_valerr_count := 0;

l_status := 'S';

DELETE FROM mtl_item_categories_interface WHERE set_process_id = 1;

COMMIT;

FOR lcr_cat_rec IN item_cat_cur

LOOP

l_err_msg := NULL;

l_struc_id := NULL;

l_cat_set_id := NULL;

l_cat_id := NULL;

l_inv_item_id := NULL;

l_old_cat_id := NULL;

l_item_count := NULL;

--- Validating Category set name

LOG(5,'Validating Category Name ');

IF (LCR_CAT_REC.category_type IS NULL)

THEN

l_err_msg := l_err_msg || 'Category Type For An Item Cant Be Null, ';

ELSE

BEGIN

SELECT structure_id,category_set_id

INTO l_struc_id,l_cat_set_id

FROM mtl_category_sets_vl

WHERE UPPER(category_set_name) = UPPER(lcr_cat_rec.category_type);

EXCEPTION

WHEN OTHERS

THEN

l_err_msg := l_err_msg || 'Could not find Category, ';

END;

END IF;

-- --- Validating Category Set

-- LOG(5,'Validating Category Set');

-- BEGIN

-- SELECT mcs.category_set_id

-- INTO l_cat_set_id

-- FROM mtl_category_sets mcs

-- ,mtl_category_sets_tl mcst

-- WHERE mcs.category_set_id = mcst.category_set_id

-- AND mcs.structure_id = l_struc_id;

-- EXCEPTION

-- WHEN OTHERS

-- THEN

-- l_err_msg := l_err_msg || 'Could not find Category Set ID, ';

-- END;

IF UPPER(LCR_CAT_REC.category_type) = 'FORECAST_CATEGORY'

THEN

BEGIN

SELECT category_id

INTO l_cat_id

FROM mtl_categories_b

WHERE structure_id = l_struc_id

AND UPPER(segment1) = UPPER(TRIM(LCR_CAT_REC.cat_segment1))

AND UPPER(segment2) = UPPER(TRIM(LCR_CAT_REC.cat_segment2))

AND UPPER(NVL(segment3,'X')) = UPPER(TRIM(NVL(LCR_CAT_REC.cat_segment3,'X')))

AND UPPER(NVL(segment4,'X')) = UPPER(TRIM(NVL(LCR_CAT_REC.cat_segment4,'X')))

AND UPPER(NVL(segment5,'X')) = UPPER(TRIM(NVL(LCR_CAT_REC.cat_segment5,'X')))

AND UPPER(NVL(segment6,'X')) = UPPER(TRIM(NVL(LCR_CAT_REC.cat_segment6,'X')))

AND UPPER(NVL(segment7,'X')) = UPPER(TRIM(NVL(LCR_CAT_REC.cat_segment7,'X')));

EXCEPTION

WHEN OTHERS

THEN

l_err_msg := l_err_msg || 'Cound not get Category ID, ';

END;

ELSIF UPPER(LCR_CAT_REC.category_type) = 'REPORTING_CATEGORY'

THEN

BEGIN

SELECT category_id

INTO l_cat_id

FROM mtl_categories_b

WHERE structure_id = l_struc_id

AND UPPER(segment1) = UPPER(TRIM(LCR_CAT_REC.cat_segment1))

AND UPPER(segment2) = UPPER(TRIM(LCR_CAT_REC.cat_segment2));

EXCEPTION

WHEN OTHERS

THEN

l_err_msg := l_err_msg || 'Cound not get Category ID, ';

END;

ELSE

BEGIN

SELECT category_id

INTO l_cat_id

FROM mtl_categories_b

WHERE structure_id = l_struc_id

AND UPPER(segment1) = UPPER(TRIM(LCR_CAT_REC.cat_segment1))

AND UPPER(segment2) = UPPER(TRIM(LCR_CAT_REC.cat_segment2))

AND UPPER(NVL(segment3,'X')) = UPPER(NVL(TRIM(LCR_CAT_REC.cat_segment3),'X'));

EXCEPTION

WHEN OTHERS

THEN

l_err_msg := l_err_msg || 'Cound not get Category ID, ';

END;

END IF;

IF UPPER(LCR_CAT_REC.category_type) = '_PURCHASING_CATEGORY'

THEN

l_trx_type := 'UPDATE';

BEGIN

SELECT category_id

INTO l_old_cat_id

FROM mtl_categories_b

WHERE structure_id = l_struc_id

AND UPPER(segment1) = UPPER('UNASSIGNED')

AND UPPER(segment2) = UPPER('UNASSIGNED')

AND UPPER(segment3) = UPPER('UNASSIGNED');

EXCEPTION

WHEN OTHERS

THEN

l_old_cat_id := NULL;

l_err_msg := l_err_msg || 'Old Category ID was not found, ';

END;

ELSE

l_trx_type := 'CREATE';

END IF;

--- Validating Item Number

LOG(5,'Validating Item Number');

BEGIN

SELECT inventory_item_id

INTO l_inv_item_id

FROM mtl_system_items_b

WHERE segment1 = TRIM(LCR_CAT_REC.item_number)

AND organization_id = gn_inv_org_id;

EXCEPTION WHEN OTHERS

THEN

l_err_msg := l_err_msg || ' Item does not exist In Oracle, ';

END;

--- Validating Item Number Duplication for a category

IF l_cat_id IS NOT NULL AND l_inv_item_id IS NOT NULL

THEN

LOG(5,'Validating Item Number Duplication for a category');

l_item_count:= 0;

BEGIN

SELECT COUNT(1)

INTO l_item_count

FROM mtl_item_categories_v

WHERE inventory_item_id = l_inv_item_id

AND organization_id = gn_inv_org_id

AND category_id = l_cat_id --NVL(l_cat_id,l_old_cat_id)

AND structure_id = l_struc_id;

IF (l_item_count > 0)

THEN

l_err_msg := l_err_msg || 'Item/Category combination already exists, ';

END IF;

END;

END IF;

IF l_err_msg IS NULL

THEN

fnd_file.put_line (fnd_file.LOG,( 'Item categories Interface Insert' ));

LOG(5,'Validation of Item categories Succedded');

----- Insert in to interface table starts

IF UPPER(LCR_CAT_REC.category_type) = 'PURCHASING_CATEGORY'

THEN

INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE (set_process_id

,process_flag

,organization_id

,inventory_item_id

,category_id

,category_set_id

,old_category_id

,transaction_type

,last_update_date

,last_updated_by

,creation_date

,created_by

,last_update_login

)

VALUES(1 -- set_process_id

,1 -- process_flag

,gn_inv_org_id -- organization_id

,l_inv_item_id --inventory_item_id

,l_cat_id -- category_id

,l_cat_set_id -- category_set_id

,l_old_cat_id --2321,

,l_trx_type --transaction_type,

,SYSDATE --last_update_date,

,gn_user_id --last_updated_by,

,SYSDATE --creation_date,

,gn_user_id --created_by,

,gn_user_id --last_update_login

);

ELSE

INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE

(set_process_id,

process_flag,

organization_id,

inventory_item_id,

category_id,

category_set_id,

-- old_category_id,

transaction_type,

last_update_date,

last_updated_by,

creation_date,

created_by,

last_update_login

)

VALUES (1, -- set_process_id

1, -- process_flag

gn_inv_org_id, -- organization_id

l_inv_item_id, --inventory_item_id

l_cat_id, -- category_id

l_cat_set_id, -- category_set_id

-- 2321,

l_trx_type, --'CREATE', --transaction_type,

Sysdate , --last_update_date,

gn_user_id, --last_updated_by,

Sysdate, --creation_date,

gn_user_id, --created_by,

gn_user_id --last_update_login

);

END IF;

----- Record Status Update

UPDATE xx_CNV_ITEM_CATEGORY

SET X_RECORD_STATUS = 'IAV'

,x_record_msg = 'Assignment validated'

WHERE ROWID = LCR_CAT_REC.ROWID;

ELSE

UPDATE xx_CNV_ITEM_CATEGORY

SET X_RECORD_STATUS = 'IAE'

,X_RECORD_MSG = 'Assignment Validation Error - '||l_err_msg

WHERE ROWID = LCR_CAT_REC.ROWID;

END IF;

END LOOP;

COMMIT;

EXCEPTION WHEN OTHERS

THEN

LOG (1,'Unexpected error @ item_cat_assign_proc ->' || SQLERRM);

END item_cat_assign_proc;

Step2: The sample table structure is given below

CREATE TABLE XX_CNV_ITEM_CATEGORY

(

ITEM_NUMBER VARCHAR2(240 BYTE),

CATEGORY_TYPE VARCHAR2(240 BYTE),

CAT_SEGMENT1 VARCHAR2(240 BYTE),

CAT_SEGMENT2 VARCHAR2(240 BYTE),

CAT_SEGMENT3 VARCHAR2(240 BYTE),

CAT_SEGMENT4 VARCHAR2(240 BYTE),

CAT_SEGMENT5 VARCHAR2(240 BYTE),

CAT_SEGMENT6 VARCHAR2(240 BYTE),

CAT_SEGMENT7 VARCHAR2(240 BYTE),

CAT_SEGMENT8 VARCHAR2(240 BYTE),

CAT_SEGMENT9 VARCHAR2(240 BYTE),

CAT_SEGMENT10 VARCHAR2(240 BYTE),

X_RECORD_STATUS VARCHAR2(3 BYTE),

X_RECORD_MSG VARCHAR2(2000 BYTE)

)

Deepak J

Monday, October 3, 2016

No comments:

Post a Comment