Requirement: Item
import for GST using API.
CREATE OR REPLACE
PROCEDURE apps.xxfil_item_import_prc (
c_segment1 IN VARCHAR2,
c_segment2 IN VARCHAR2,
c_segment3 IN VARCHAR2,
c_item_desc VARCHAR2,
c_long_desc VARCHAR2,
c_organization_code IN
VARCHAR2,
c_organization_id IN
NUMBER,
c_uom_code VARCHAR2,
c_template_name VARCHAR2,
c_template_id NUMBER,
c_attribute9 VARCHAR2,
c_purchaseable_flag VARCHAR2,
c_qa_req_flag VARCHAR2,
c_receipt_required_flag VARCHAR2,
c_receiving_routing_id NUMBER,
c_regime_name VARCHAR2,
c_reporting_type_name VARCHAR2,
c_item_classification VARCHAR2,
c_recoverable_flag VARCHAR2,
c_tariff_head VARCHAR2,
c_tariff_no VARCHAR2,
c_tax_status VARCHAR2,
c_reporting_usage VARCHAR2
)
IS
l_item_table ego_item_pub.item_tbl_type;
x_item_table ego_item_pub.item_tbl_type;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER (10);
x_msg_data VARCHAR2 (1000);
x_message_list error_handler.error_tbl_type;
v_cnt NUMBER := 1;
x_error_code NUMBER;
v_sub_inv_cnt NUMBER := 0;
v_item_code VARCHAR2 (200 BYTE);
xp_status VARCHAR2 (1 BYTE);
xuom_code VARCHAR2 (3 BYTE);
xcategory_id NUMBER;
xcategory_id_old NUMBER;
xorganization_id NUMBER;
l_msg VARCHAR2 (4000 BYTE);
xcategory_set_id NUMBER;
xregime_id NUMBER;
xreporting_type_id NUMBER;
xuser_id NUMBER;
x_cnt NUMBER := 0;
xreporting_type_code jai_reporting_types.reporting_type_code%TYPE;
xcogs_account NUMBER;
xexpense_account NUMBER;
xsale_account NUMBER;
localtemplatehdrid NUMBER;
localtemplatedtlid NUMBER;
localreportassid NUMBER;
BEGIN
-- Get User Id
BEGIN
SELECT NVL (fnd_profile.VALUE ('USER_ID'),
1110)
INTO xuser_id
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
xuser_id := 1110;
END;
-- Get Default Category Set Id
BEGIN
SELECT category_set_id
INTO xcategory_set_id
FROM mtl_default_category_sets_fk_v
WHERE 1 = 1 AND functional_area_id = 2;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT CATEGORY_ID
INTO XCATEGORY_ID
FROM MTL_CATEGORIES_KFV
WHERE 1 = 1
AND CONCATENATED_SEGMENTS = '000.000.00.0000.000';
EXCEPTION
WHEN OTHERS
THEN
BEGIN
ROLLBACK;
RETURN;
END;
END;
l_item_table (v_cnt).transaction_type := 'CREATE';
l_item_table (v_cnt).segment1 := c_segment1;
l_item_table (v_cnt).segment2 := c_segment2;
l_item_table (v_cnt).segment3 := c_segment3;
l_item_table (v_cnt).description :=
c_item_desc;
l_item_table (v_cnt).organization_code :=
c_organization_code;
l_item_table (v_cnt).template_name :=
c_template_name;
l_item_table (v_cnt).attribute9 :=
c_attribute9;
l_item_table (v_cnt).primary_uom_code :=
c_uom_code;
l_item_table (v_cnt).organization_id :=
c_organization_id;
l_item_table (v_cnt).template_id :=
c_template_id;
l_item_table (v_cnt).purchasing_item_flag :=
c_purchaseable_flag;
l_item_table (v_cnt).purchasing_enabled_flag
:= c_purchaseable_flag;
l_item_table (v_cnt).inspection_required_flag
:= c_qa_req_flag;
l_item_table (v_cnt).receipt_required_flag :=
c_receipt_required_flag;
l_item_table (v_cnt).receiving_routing_id :=
c_receiving_routing_id;
l_item_table (v_cnt).long_description :=
c_long_desc;
ego_item_pub.process_items (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_item_tbl => l_item_table,
x_item_tbl => x_item_table,
x_return_status =>
x_return_status,
x_msg_count => x_msg_count
);
IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
ROLLBACK;
error_handler.get_message_list (x_message_list);
FOR i IN 1 .. x_message_list.COUNT
LOOP
L_MSG := L_MSG|| x_message_list (i).MESSAGE_TEXT;
END LOOP;
COMMIT;
RETURN;
ELSE
COMMIT;
END IF;
-- Get Old Category Id
BEGIN
SELECT category_id
INTO xcategory_id_old
FROM inv.mtl_item_categories
WHERE 1 = 1
AND organization_id =
c_organization_id
AND category_set_id = xcategory_set_id
AND inventory_item_id = x_item_table (v_cnt).inventory_item_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
IF c_organization_CODE = 'IMO'
THEN
-- Category Assignment
inv_item_category_pub.create_category_assignment
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_errorcode => x_error_code,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_category_id => xcategory_id,
p_category_set_id => xcategory_set_id,
p_inventory_item_id => x_item_table (v_cnt).inventory_item_id,
p_organization_id => c_organization_id
);
END IF;
IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
ROLLBACK;
l_msg := NULL;
FOR i IN 1 .. fnd_msg_pub.count_msg
LOOP
l_msg :=
l_msg
|| fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false);
END LOOP;
COMMIT;
RETURN;
ELSE
COMMIT;
END IF;
------------------------------------------------------------
----- India Localization Data Insertion
------------------------------------------------------------
-- Get Regime Id
BEGIN
SELECT regime_id
INTO xregime_id
FROM ja.jai_regimes
WHERE 1 = 1 AND regime_name =
c_regime_name;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
ROLLBACK;
RETURN;
END;
END;
INSERT INTO xx_test
VALUES (1, 'XREGIME_ID -->' ||
xregime_id);
COMMIT;
-- Get Reporting Type Id
BEGIN
SELECT reporting_type_id,
reporting_type_code
INTO xreporting_type_id,
xreporting_type_code
FROM ja.jai_reporting_types#
WHERE 1 = 1
AND entity_code = 'ITEM'
AND reporting_type_name =
c_reporting_type_name;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
ROLLBACK;
RETURN;
END;
END;
COMMIT;
BEGIN
localtemplatehdrid :=
jai_item_templ_hdr_s.NEXTVAL;
-- Insert into JA.JAI_ITEM_TEMPL_HDR
Table
INSERT INTO ja.jai_item_templ_hdr
(template_hdr_id,
entity_id, entity_type_code,
item_classification,
inventory_item_id,
organization_id,
record_type_code, creation_date,
created_by, last_update_date,
last_updated_by
)
VALUES (localtemplatehdrid,
x_item_table (v_cnt).inventory_item_id,
'ITEM',
c_item_classification,
x_item_table (v_cnt).inventory_item_id,
c_organization_id, 'DEFINED',
SYSDATE,
xuser_id, SYSDATE, xuser_id
);
COMMIT;
localtemplatedtlid :=
jai_item_templ_dtls_s.NEXTVAL;
INSERT INTO ja.jai_item_templ_dtls
(template_hdr_id,
template_dtl_id, attribute_code,
attribute_value, regime_id,
applicable_flag,
record_type_code,
creation_date, created_by,
last_update_date,
last_updated_by
)
VALUES (localtemplatehdrid,
localtemplatedtlid, 'RECOVERABLE',
c_recoverable_flag,
xregime_id, 'Y',
'DEFINED', SYSDATE, xuser_id,
SYSDATE, xuser_id
);
localtemplatedtlid :=
jai_item_templ_dtls_s.NEXTVAL;
INSERT INTO ja.jai_item_templ_dtls
(template_hdr_id,
template_dtl_id, attribute_code,
attribute_value, regime_id,
applicable_flag,
record_type_code,
creation_date, created_by,
last_update_date,
last_updated_by
)
VALUES (localtemplatehdrid,
localtemplatedtlid, 'TARIFF_HEAD',
c_tariff_head, xregime_id, 'Y',
'DEFINED', SYSDATE, xuser_id,
SYSDATE, xuser_id
);
localtemplatedtlid :=
jai_item_templ_dtls_s.NEXTVAL;
INSERT INTO ja.jai_item_templ_dtls
(template_hdr_id,
template_dtl_id, attribute_code,
attribute_value, regime_id,
applicable_flag,
record_type_code,
creation_date, created_by,
last_update_date,
last_updated_by
)
VALUES (localtemplatehdrid,
localtemplatedtlid, 'TARIFF_NO',
c_tariff_no, xregime_id, 'Y',
'DEFINED', SYSDATE, xuser_id,
SYSDATE, xuser_id
);
localtemplatedtlid :=
jai_item_templ_dtls_s.NEXTVAL;
INSERT INTO ja.jai_item_templ_dtls
(template_hdr_id,
template_dtl_id, attribute_code,
attribute_value, regime_id,
applicable_flag,
record_type_code,
creation_date, created_by,
last_update_date,
last_updated_by
)
VALUES (localtemplatehdrid,
localtemplatedtlid, 'TAX_STATUS',
c_tax_status, xregime_id, 'Y',
'DEFINED', SYSDATE, xuser_id,
SYSDATE, xuser_id
);
localreportassid :=
jai_reporting_associations_s.NEXTVAL;
INSERT INTO ja.jai_reporting_associations
(reporting_association_id,
reporting_type_id,
reporting_type_name,
reporting_usage,
reporting_code, entity_code,
entity_id,
regime_id,
entity_source_table, effective_from,
record_type_code,
creation_date, created_by,
last_update_date,
last_updated_by
)
VALUES (localreportassid, xreporting_type_id,
c_reporting_type_name,
c_reporting_usage,
xreporting_type_code, 'ITEM',
localtemplatehdrid,
xregime_id, 'JAI_ITEM_TEMPL_HDR',
'01-JUL-2017',
'DEFINED', SYSDATE, xuser_id,
SYSDATE, xuser_id
);
END;
COMMIT;
x_cnt := x_cnt + 1;
END
xxfil_item_import_prc;
/
