Register as concurrent program to get the result. Trying from Back end is not advisable.
===============================================================
For the below custom table having itemcode(
mtl_system_items_interface.segment1 and new expense account)
CREATE TABLE APPS.XXAYTB_GLCODE_UPDATE
(
ITEMCODE VARCHAR2(240 BYTE),
EXPENSE_ACCOUNT_CODE VARCHAR2(240 BYTE),
STATUS VARCHAR2(240 BYTE),
ERROR_MESSAGE VARCHAR2(240 BYTE)
)
CREATE TABLE APPS.XXAYTB_GLCODE_CCID_CHECK
(
SEGMENT1 VARCHAR2(240 BYTE),
SEGMENT2 VARCHAR2(240 BYTE),
SEGMENT3 VARCHAR2(240 BYTE),
SEGMENT4 VARCHAR2(240 BYTE),
SEGMENT5 VARCHAR2(240 BYTE),
SEGMENT6 VARCHAR2(240 BYTE),
NEWCCID VARCHAR2(240 BYTE),
OLDCCID VARCHAR2(240 BYTE),
ITEMCODE VARCHAR2(240 BYTE),
ORGID VARCHAR2(240 BYTE),
EXPENSE_ACCOUNT VARCHAR2(240 BYTE)
)
CREATE TABLE APPS.XXAYTB_GLCODE_UPDATE_STATUS
(
INVENTORY_ITEM_ID VARCHAR2(240 BYTE),
ORGANIZATION_ID VARCHAR2(240 BYTE),
ITEMCODE VARCHAR2(240 BYTE),
MSIB_CURRVAL VARCHAR2(240 BYTE),
CODE_COMBINATION_ID VARCHAR2(240 BYTE),
STATUS VARCHAR2(240 BYTE)
)
/* Formatted on 2013/02/24 09:36 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE apps.xxinv_item_assignment_v1 (
errbuff OUT VARCHAR2,
retcode OUT NUMBER
)
---procedure created for updating expense account for an item senthil kalvarayapillai wipro 20-feb-2013
IS
v_error_msg VARCHAR2 (2000);
v_msg_cnt NUMBER;
v_category_cnt NUMBER;
v_set_process_id NUMBER;
v_code_combination_id NUMBER;
l_segment1 VARCHAR2 (100);
l_segment2 VARCHAR2 (100);
l_segment3 VARCHAR2 (100);
l_segment4 VARCHAR2 (100);
l_segment5 VARCHAR2 (100);
l_segment6 VARCHAR2 (100);
l_item VARCHAR2 (100);
l_ccid VARCHAR2 (100);
l_organization_id VARCHAR2 (100);
l_inventory_item_id VARCHAR2 (100);
v_charge_ccid_id NUMBER;
BEGIN
fnd_file.put_line
(fnd_file.output,
'============================INSERTED Records==============================='
);
FOR rec_c1 IN (SELECT *
FROM xxaytb_glcode_update)
LOOP
DBMS_OUTPUT.put_line (rec_c1.itemcode);
FOR rec_c2 IN (SELECT glcc.segment1, glcc.segment2, glcc.segment3,
glcc.segment4, glcc.segment5, glcc.segment6,
msib.segment1 item,
glcc.code_combination_id oldccid,
msib.organization_id, msib.inventory_item_id,
rec_c1.expense_account_code expacc,
rec_c1.itemcode
FROM mtl_system_items_b msib,
gl_code_combinations glcc
WHERE 1 = 1
AND msib.expense_account = glcc.code_combination_id
AND msib.segment1 = rec_c1.itemcode)
LOOP
DBMS_OUTPUT.put_line ( rec_c2.oldccid
|| ' - orgid is - '
|| rec_c2.organization_id
);
v_charge_ccid_id := NULL;
DBMS_OUTPUT.put_line ( rec_c2.inventory_item_id
|| ' - '
|| rec_c2.organization_id
|| ' - '
|| rec_c2.itemcode
|| ' - '
|| rec_c2.oldccid
);
fnd_file.put_line (fnd_file.output,
'inventory_item_id- '
|| rec_c2.inventory_item_id
|| 'organization_id- '
|| rec_c2.organization_id
|| 'itemcode- '
|| rec_c2.itemcode
|| 'oldccid-'
|| rec_c2.oldccid
);
INSERT INTO xxaytb_glcode_ccid_check
(segment1, segment2, segment3,
segment4, segment5, segment6,
oldccid, itemcode,
orgid, expense_account
)
VALUES (rec_c2.segment1, rec_c2.segment2, rec_c2.segment3,
rec_c2.segment4, rec_c2.segment5, rec_c2.segment6,
rec_c2.oldccid, rec_c2.itemcode,
rec_c2.organization_id, rec_c2.expacc
);
BEGIN
v_charge_ccid_id :=
fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => 50368,
validation_date => TO_CHAR
(SYSDATE,
'DD-MON-YYYY'
),
concatenated_segments => rec_c2.segment1
|| '.'
|| rec_c2.segment2
|| '.'
|| rec_c2.segment3
|| '.'
|| rec_c2.expacc
|| '.'
|| rec_c2.segment5
|| '.'
|| rec_c2.segment6
);
COMMIT;
DBMS_OUTPUT.put_line ('v_charge_ccid_id is:= ' || v_charge_ccid_id);
END;
IF v_charge_ccid_id > 0
THEN
INSERT INTO mtl_system_items_interface
(inventory_item_id, organization_id,
segment1, process_flag, transaction_type,
set_process_id,
expense_account
)
VALUES (rec_c2.inventory_item_id, rec_c2.organization_id,
rec_c2.itemcode, 1, 'UPDATE' --'CREATE'
,
mtl_system_items_intf_sets_s.NEXTVAL,
v_charge_ccid_id
);
COMMIT;
INSERT INTO xxaytb_glcode_update_status
(inventory_item_id, organization_id,
itemcode,
msib_currval,
code_combination_id, status
)
VALUES (rec_c2.inventory_item_id, rec_c2.organization_id,
rec_c2.itemcode,
mtl_system_items_intf_sets_s.CURRVAL,
v_charge_ccid_id, 'Success'
);
COMMIT;
ELSE
INSERT INTO xxaytb_glcode_update_status
(inventory_item_id, organization_id,
itemcode,
msib_currval,
code_combination_id, status
)
VALUES (rec_c2.inventory_item_id, rec_c2.organization_id,
rec_c2.itemcode,
mtl_system_items_intf_sets_s.CURRVAL,
v_charge_ccid_id, 'Error'
);
COMMIT;
END IF;
DBMS_OUTPUT.put_line ('---------------');
END LOOP;
END LOOP;
END;
/
Regisatered the concurrent program for the above procedure & submit the program.
Once completed Run the Import items program from inventory Responsibility.
