SQL & PL/SQL

Hi ,
Please code giving complition error.Please any idea which one i missed

CREATE OR REPLACE PROCEDURE company
v_org_xid varchar2(50);
v_par_org_id varchar2(50);
v_time_stamp varchar2(50);
xclass_id CHAR(20);
v_xsplit varchar2(50);

xseq_nextval NUMBER(38);
xcmd_msg VARCHAR2(2168);
xrowcount NUMBER(38);
xvalid_rec_ind CHAR(1);
xmsg_type CHAR(1); /* W (Warning); E (Error)*/
xnew_record NUMBER(38);
xrecord_no NUMBER(38);
xprocess_ind CHAR(1);
xtot_records NUMBER(38);
xerr_records NUMBER(38);
xins_records NUMBER(38);
xupd_records NUMBER(38);
xwrn_records NUMBER(38);
xerror NUMBER(38);
xstp_status NUMBER(38);
xrec_status CHAR(1); /* I (Inserted); U (Updated)*/
xmsg_num NUMBER(38);
/********************************************************
DECLARE CURSOR FROM MIGRATION TABLE
********************************************************/
CURSOR c_get_record IS
SELECT xid, xtime_stamp, xname, xcreated_by, xcreated_on,
xupdated_by, xupdated_on, xphone1, xphone2, xfax, xbusiness_typ, xcustom0,
xcustom1, xcustom2, xcustom3, xcustom4, xaddr1, xaddr2, xaddr3, xcity,
xstate, xzip, xcountry, xsplit, xcontact_id, xemail, xweb_server,
xmax_discount, xname2, xsec_key, xparent_id, xcustom5, xcustom6, xcustom7,
xcustom8, xcustom9, xcurrency_id, xaccount_no, xdescription, xis_active,xnewts,
xref_thinq_pk
FROM MIGT_TPT_COMPANY start with trim(xparent_id) is null
connect by prior trim(xparent_id)=trim(xref_thinq_pk)
and process_ind IS NULL;
begin
FOR each_rec IN c_get_record
LOOP
v_tot_records := v_tot_records + 1;
v_valid_rec_ind := 'Y';
v_rec_status := NULL;
v_msg_type := NULL;
v_msg_num := NULL;
v_cmd_msg := NULL;

/*******************************************************************
CHECK TO SEE IF RECORD EXISTS ALREADY IN SABA FROM A PREVIOUS IMPORT
********************************************************************/

begin
IF (each_rec.xsplit = 'world') THEN
v_xsplit := 'domin000000000000001';
ELSE
SELECT id INTO v_xsplit
FROM FGT_DOMAIN
WHERE id = each_rec.xsplit;
end if;
exception when no_data_found
then
xcmd_msg := 'LMS_ERROR:: TPT_COMPANY ('||LTRIM(RTRIM(xref_thinq_pk))||') Domain reference '||LTRIM(RTRIM(xsplit))||' does not exist';
dbms_output.put_line(xcmd_msg);
xerr_records := xerr_records + 1;
xvalid_rec_ind := 'N';
xmsg_type := 'E';

when to_many_rows THEN
xcmd_msg := 'LMS_ERROR:: TPT_COMPANY ('||LTRIM(RTRIM(xref_thinq_pk))||') too many rows returned for Domain reference';
dbms_output.put_line(xcmd_msg);
xerr_records := xerr_records + 1;
xvalid_rec_ind := 'N';
xmsg_type := 'E';

END ;

begin

SELECT id,time_stamp INTO v_org_xid,v_time_stamp
FROM TPT_COMPANY
WHERE NAME = upper(each_rec.xname);


Begin
If (each_rec.xparent_id) is null then v_org_xid:='bisut000000000000001'
else

select id into v_par_org_id from TPT_COMPANY
WHERE NAME = upper(each_rec.xparent_id);
end if;
exception when no_data_found
then
xcmd_msg := 'LMS_ERROR:: TPT_COMPANY ('||LTRIM(RTRIM(xref_thinq_pk))||') Domain reference '||LTRIM(RTRIM(xsplit))||' does not exist';
dbms_output.put_line(xcmd_msg);
xerr_records := xerr_records + 1;
xvalid_rec_ind := 'N';
xmsg_type := 'E';

when to_many_rows THEN
xcmd_msg := 'LMS_ERROR:: TPT_COMPANY ('||LTRIM(RTRIM(xref_thinq_pk))||') too many rows returned for Domain reference';
dbms_output.put_line(xcmd_msg);
xerr_records := xerr_records + 1;
xvalid_rec_ind := 'N';
xmsg_type := 'E';

END ;




begin
tpp_business_unit_upd(
xid => v_org_xid,
xtime_stamp=>v_time_stamp,
xname => each_rec.xname,
xcreated_by => each_rec.xcreated_by,
xcreated_on =>each_rec.xcreated_on,
xupdated_by => each_rec.xupdated_by,
xupdated_on => each_rec.xupdated_on,
xphone1 => each_rec.xphone1,
xphone2 => each_rec.xphone2,
xfax => each_rec.xfax ,
xcustom0 => each_rec.xcustom0 ,
xcustom1 => each_rec.xcustom1,
xcustom2 => each_rec.xcustom2,
xcustom3 => each_rec.xcustom3,
xcustom4 => xcustom4 ,
xaddr1 => each_rec.xaddr1,
xaddr2 => each_rec.xaddr2,
xaddr3 => each_rec.xaddr3,
xcity => each_rec.xcity,
xstate => each_rec.xstate,
xzip => each_rec.xzip ,
xcountry => xcountry,
xsplit => each_rec.xsplit,
xcontact_id => xcontact_id,
xparent_id => v_par_org_id,
xemail => each_rec.xemail ,
xweb_server => each_rec.xweb_server,
xaa_id => null,
xname2 => each_rec.xname2 ,
xcustom5 => each_rec.xcustom5,
xcustom6 => each_rec.xcustom6,
xcustom7 => each_rec.xcustom7,
xcustom8 => each_rec.xcustom8,
xcustom9 =>each_rec.xcustom9,
xcurrency_id =>each_rec.xcurrency_id,
xaccount_no =>each_rec.xaccount_no,
xdescription =>each_rec.xdescription,
xcost_center => null,
xnewts => each_rec.xnewts
);

v_cmd_msg :=
'UPDATE:: TPT_COMPANY('
|| LTRIM (RTRIM (each_rec.xname))
|| ') updated successfully.';
v_upd_records := v_upd_records + 1;
v_valid_rec_ind := 'Y';
v_msg_type := 'U';
v_rec_status := 'U';
EXCEPTION
WHEN OTHERS
THEN
v_cmd_msg :=
'LMS_ERROR:: TPT_COMPANY('
|| TRIM (each_rec.xname)
|| ') update failed';
v_err_records := v_err_records + 1;
v_valid_rec_ind := 'N';
v_msg_type := 'E';
END;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
/*Generate id for new record*/
xclass_id := 'ddcls000000000001012';
fgp_id_gen(xclass_id, v_org_xid);

BEGIN
tpp_business_unit_ins(
xid => v_org_xid,
xtime_stamp=>v_time_stamp,
xname => each_rec.xname,
xcreated_by => each_rec.xcreated_by,
xcreated_on =>each_rec.xcreated_on,
xupdated_by => each_rec.xupdated_by,
xupdated_on => each_rec.xupdated_on,
xphone1 => each_rec.xphone1,
xphone2 => each_rec.xphone2,
xfax => each_rec.xfax ,
xcustom0 => each_rec.xcustom0 ,
xcustom1 => each_rec.xcustom1,
xcustom2 => each_rec.xcustom2,
xcustom3 => each_rec.xcustom3,
xcustom4 => xcustom4 ,
xaddr1 => each_rec.xaddr1,
xaddr2 => each_rec.xaddr2,
xaddr3 => each_rec.xaddr3,
xcity => each_rec.xcity,
xstate => each_rec.xstate,
xzip => each_rec.xzip ,
xcountry => xcountry,
xsplit => each_rec.xsplit,
xcontact_id => xcontact_id,
xparent_id => v_par_org_id,
xemail => each_rec.xemail ,
xweb_server => each_rec.xweb_server,
xaa_id => null,
xname2 => each_rec.xname2 ,
xcustom5 => each_rec.xcustom5,
xcustom6 => each_rec.xcustom6,
xcustom7 => each_rec.xcustom7,
xcustom8 => each_rec.xcustom8,
xcustom9 =>each_rec.xcustom9,
xcurrency_id =>each_rec.xcurrency_id,
xaccount_no =>each_rec.xaccount_no,
xdescription =>each_rec.xdescription,
xcost_center => null,
xnewts => each_rec.xnewts
);

v_valid_rec_ind := 'Y';
v_msg_type := 'I';
v_ins_records := v_ins_records + 1;
v_rec_status := 'I';
END;
WHEN TOO_MANY_ROWS
THEN
v_cmd_msg :=
'LMS_ERROR:: TPT_COMPANY ('
|| LTRIM (RTRIM (each_rec.xname))
|| ') too many rows returned';
--DBMS_OUTPUT.PUT_LINE(xcmd_msg);
v_err_records := v_err_records + 1;
v_valid_rec_ind := 'N';
v_msg_type := 'E';
END;

/**************************************************
UPDATE RECORD IN MIGRATION TABLE
**************************************************/

UPDATE MIGT_TPT_COMPANY
SET
valid_rec_ind = xvalid_rec_ind,
process_ind = xprocess_ind,
process_date = SYSDATE,
rec_status = xrec_status,
msg_type = xmsg_type,
msg_num = xmsg_num,
msg = xcmd_msg,
xid=v_org_xid
WHERE xref_thinq_pk = xref_thinq_pk;


IF MOD (v_tot_records, 500) = 0
THEN
COMMIT;
END IF;
END LOOP;

COMMIT;
/***********************************************************************************
PRINT RESULTS
***********************************************************************************/
xcmd_msg := '***********************************************';
dbms_output.put_line(xcmd_msg);

xcmd_msg := 'Total MIGT_TPT_COMPANY Records: '|| SUBSTR(LPAD(TO_CHAR(ROUND(xtot_records)), 10), 1, 10);
dbms_output.put_line(xcmd_msg);

xcmd_msg := 'Total TPT_COMPANY Records Inserted: ' || SUBSTR(LPAD(TO_CHAR(ROUND(xins_records)), 10), 1, 10);
dbms_output.put_line(xcmd_msg);

xcmd_msg := 'Total TPT_COMPANY Records Updated: ' || SUBSTR(LPAD(TO_CHAR(ROUND(xupd_records)), 10), 1, 10);
dbms_output.put_line(xcmd_msg);

xcmd_msg := 'Total Invalid MIGT_TPT_COMPANY Records: ' || SUBSTR(LPAD(TO_CHAR(ROUND(xerr_records)), 10), 1, 10);
dbms_output.put_line(xcmd_msg);

xcmd_msg := 'Completed On: ' || TO_CHAR((SYSDATE), 'Mon DD YYYY HH:MI:SSAM');
dbms_output.put_line(xcmd_msg||xid);

xcmd_msg := '***********************************************';
dbms_output.put_line(xcmd_msg);
end;