Upload Item Category Code Combinations Using API
Control File
LOAD DATA
INFILE 'Category_Codes.csv'
INSERT INTO TABLE mtl_categories_inst_cust
REPLACE FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
description "TRIM(:description)",
segment1 "TRIM(:segment1)",
segment2 "TRIM(:segment2)",
segment3 "TRIM(:segment3)",
segment4 "TRIM(:segment4)",
segment5 "TRIM(:segment5)",
segment6 "TRIM(:segment6)",
segment7 "TRIM(:segment7)",
segment8 "TRIM(:segment8)",
segment9 "TRIM(:segment9)",
segment10 "TRIM(:segment10)",
segment11 "TRIM(:segment11)",
segment12 "TRIM(:segment12)",
segment13 "TRIM(:segment13)",
segment14 "TRIM(:segment14)",
segment15 "TRIM(:segment15)",
segment16 "TRIM(:segment16)",
segment17 "TRIM(:segment17)",
segment18 "TRIM(:segment18)",
segment19 "TRIM(:segment19)",
segment20 "TRIM(:segment20)",
category_set_name "TRIM(:category_set_name)"
)
Custom Table
DROP TABLE APPS.MTL_CATEGORIES_INST_CUST CASCADE CONSTRAINTS;
CREATE TABLE APPS.MTL_CATEGORIES_INST_CUST
(
DESCRIPTION VARCHAR2(240 BYTE),
SEGMENT1 VARCHAR2(40 BYTE),
SEGMENT2 VARCHAR2(40 BYTE),
SEGMENT3 VARCHAR2(40 BYTE),
SEGMENT4 VARCHAR2(40 BYTE),
SEGMENT5 VARCHAR2(40 BYTE),
SEGMENT6 VARCHAR2(40 BYTE),
SEGMENT7 VARCHAR2(40 BYTE),
SEGMENT8 VARCHAR2(40 BYTE),
SEGMENT9 VARCHAR2(40 BYTE),
SEGMENT10 VARCHAR2(40 BYTE),
SEGMENT11 VARCHAR2(40 BYTE),
SEGMENT12 VARCHAR2(40 BYTE),
SEGMENT13 VARCHAR2(40 BYTE),
SEGMENT14 VARCHAR2(40 BYTE),
SEGMENT15 VARCHAR2(40 BYTE),
SEGMENT16 VARCHAR2(40 BYTE),
SEGMENT17 VARCHAR2(40 BYTE),
SEGMENT18 VARCHAR2(40 BYTE),
SEGMENT19 VARCHAR2(40 BYTE),
SEGMENT20 VARCHAR2(40 BYTE),
CATEGORY_SET_NAME VARCHAR2(240 BYTE)
);
Custom Procedure
l_row_id VARCHAR2 (50);
l_category_id NUMBER;
l_category_set_id NUMBER;
l_structure_id NUMBER;
CURSOR c_cat
IS
SELECT description, segment1, segment2, segment3, segment4, segment5,
segment6, segment7, segment8, segment9, segment10, segment11,
segment12, segment13, segment14, segment15, segment16,
segment17, segment18, segment19, segment20, category_set_name
FROM mtl_categories_inst_cust;
m_cat c_cat%ROWTYPE;
BEGIN
FOR m_cat IN c_cat
LOOP
SELECT MAX (category_id) + 1
INTO l_category_id
FROM mtl_categories;
SELECT category_set_id, structure_id
INTO l_category_set_id, l_structure_id
FROM mtl_category_sets_v
WHERE category_set_name = m_cat.category_set_name;
mtl_categories_pkg.insert_row (x_rowid => l_row_id,
x_category_id => l_category_id,
x_description => m_cat.description,
x_structure_id => l_structure_id,
x_disable_date => NULL,
x_web_status => NULL,
x_supplier_enabled_flag => NULL,
x_segment1 => m_cat.segment1,
x_segment2 => m_cat.segment2,
x_segment3 => m_cat.segment3,
x_segment4 => m_cat.segment4,
x_segment5 => m_cat.segment5,
x_segment6 => m_cat.segment6,
x_segment7 => m_cat.segment7,
x_segment8 => m_cat.segment8,
x_segment9 => m_cat.segment9,
x_segment10 => m_cat.segment10,
x_segment11 => m_cat.segment11,
x_segment12 => m_cat.segment12,
x_segment13 => m_cat.segment13,
x_segment14 => m_cat.segment14,
x_segment15 => m_cat.segment15,
x_segment16 => m_cat.segment16,
x_segment17 => m_cat.segment17,
x_segment18 => m_cat.segment18,
x_segment19 => m_cat.segment19,
x_segment20 => m_cat.segment20,
x_summary_flag => 'N',
x_enabled_flag => 'Y',
x_start_date_active => NULL,
x_end_date_active => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_last_update_date => SYSDATE,
x_last_updated_by => 1110,
x_creation_date => SYSDATE,
x_created_by => 1110,
x_last_update_login => 0
);
INSERT INTO mtl_category_set_valid_cats
(category_set_id, category_id, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login
)
VALUES (l_category_set_id, l_category_id, SYSDATE,
1110, SYSDATE, 1110,
0
);
END LOOP;
END;
Thanks
Srini
No comments:
Post a Comment
No one has ever become poor by giving