ITEM INTERFACE PROCEDURE & DATA &CONTORL FILE

DATA FILE:


AVK,204,VIMAL,CREATE,1,1001,@Finished Good,FG,V1

BVK,204,VINOT,CREATE,1,1001,@Finished Good,FG,V1

MRB,204,RAJES,CREATE,1,1001,@Finished Good,FG,V1

RAK,204,ARAVI,CREATE,1,1001,@Finished Good,FG,V1

BVP,204,VIVIN,CREATE,1,1001,@Finished Good,FG,V1

ARK,204,RAJES,CREATE,1,1001,@Finished Good,FG,V1

MBM,204,BALAS,CREATE,1,1001,@Finished Good,FG,V1

MMS,204,SAMIS,CREATE,1,1001,@Finished Good,FG,V1

SJM,204,JOTHI,CREATE,1,1001,@Finished Good,FG,V1

RKN,204,KANNA,CREATE,1,1001,@Finished Good,FG,V1



CONTROL FILE:


LOAD DATA

INFILE '/oracle2/VIS1211/apps/apps_st/appl/inv/12.0.0/bin/oit.txt'

INSERT INTO TABLE iteminterface

FIELDS TERMINATED BY ','

(

SEGMENT1 ,

ORGANIZATION_ID,

DESCRIPTION,

TRANSACTION_TYPE,

PROCESS_FLAG,

SET_PROCESS_ID,

template_name,

item_type,

organization_code

)


Procedure Program:


CREATE OR REPLACE

PROCEDURE APPS.vimal_interface

(

ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2)

IS

LN_REC_CNT NUMBER := 0;

v_template_name NUMBER;

v_organization NUMBER;

l_error_details VARCHAR2 (4000) := NULL;

l_error_flag VARCHAR2 (2000) := NULL;

l_failure_count NUMBER := 0;

l_success_count NUMBER := 0;

l_user_id NUMBER := fnd_global.user_id;

l_resp_id NUMBER := fnd_global.resp_id;

l_appl_id NUMBER := fnd_global.resp_appl_id;

CURSOR DATA_LOAD

IS

SELECT * FROM iteminterface;

BEGIN

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR I IN DATA_LOAD

LOOP

l_error_flag := NULL;

l_error_details := NULL;

LN_REC_CNT := LN_REC_CNT + 1;

BEGIN

SELECT COUNT (*)

INTO v_organization

FROM org_organization_definitions

WHERE organization_code = i.organization_code;

IF v_organization = 0 THEN

l_error_flag := 'Y';

l_error_details := l_error_details || '\' || 'Organization Error' || '\' || SUBSTR (SQLERRM, 1, 100);

END IF;

END;

BEGIN

SELECT COUNT (*)

INTO v_template_name

FROM mtl_item_templates

WHERE template_name = i.template_name;

IF v_template_name = 0 THEN

l_error_flag := 'Y';

l_error_details := l_error_details || '\' || 'Template_Name Error' || '\' || SUBSTR (SQLERRM, 1, 100);

END IF;

END;

IF (l_error_flag = 'Y') THEN

l_failure_count := l_failure_count + 1;

ELSE

INSERT

INTO MTL_SYSTEM_ITEMS_INTERFACE

(

SEGMENT1 ,

ORGANIZATION_ID ,

DESCRIPTION ,

TRANSACTION_TYPE,

PROCESS_FLAG ,

SET_PROCESS_ID ,

template_name ,

item_type ,

organization_code

)

VALUES

(

i.SEGMENT1 ,

i.ORGANIZATION_ID ,

i.DESCRIPTION ,

i.TRANSACTION_TYPE,

i.PROCESS_FLAG ,

i.SET_PROCESS_ID ,

i.template_name ,

i.item_type ,

i.organization_code

);

l_success_count := l_success_count + 1;

END IF;

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

FND_FILE.PUT_LINE

(

FND_FILE.LOG,'Records not loaded into the interface table'

)

;

END;