reddy_mvg
asked on
Oracle PL/SQL PLS-00103 error in the import procedure i got the error
LINE/COL ERROR
-------- -------------------------- ---------- ---------- ---------- ---------
334/24 PLS-00103: Encountered the symbol "UPDATING" when expecting one
of the following:
. ( ) , * @ % & | = - + < / > at in is mod not range rem =>
.. <an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
here is my code
CREATE or REPLACE PACKAGE BODY LNHR_LOCATION_PKG
AS
Procedure validate (p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER
)
IS
v_error_message VARCHAR2(4000);
v_error_stage VARCHAR2(200);
cursor c_location IS
SELECT stg.rowid, stg.*
from LNHR_LOCATION_STG stg
where stg.status in ('U','E','I');
ro_location c_location%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('firs t');
v_error_message :=NULL;
OPEN c_location;
LOOP
FETCH c_location into ro_location;
EXIT WHEN c_job%NOTFOUND;
BEGIN
v_error_stage := 'Manadatory Column Validation';
SELECT DECODE (ro_location.location_code , NULL, 'location_code is null')||
DECODE (ro_location.address_line1 , NULL, 'address_line1 is null')||
DECODE (ro_location.town_or_city, NULL, 'city is null')||
DECODE (ro_location.country,NULL, 'country is null')||
DECODE (ro_location.postal_code,N ULL,'zip is null')
into v_error_message
FROM sys.dual;
v_error_stage := 'Updating Interim table with error messages';
UPDATE LNHR_LOCATION_STG LHLS
set LHLS.error_message = v_error_message
,LHLS.status = DECODE(v_error_message,NUL L,'D','I')
where LHLS.rowid = ro_location.rowid;
end;
END LOOP;
close c_location;
EXCEPTION
WHEN OTHERS THEN
p_retcode :=2;
p_errbuf := v_error_stage ||' - '|| SQLERRM;
END VALIDATE;
PROCEDURE import(p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER)
IS
v_object_version_number NUMBER;
v_error_stage VARCHAR2(4000);
v_error_message VARCHAR2(2000);
v_validate BOOLEAN:=FALSE;
v_location_id NUMBER;
v_job_id NUMBER;
v_ppc_object_version_numbe r NUMBER;
v_bg_grp_id NUMBER :=0;
v_esd_loc_extra_info_id NUMBER;
v_esd_ovn NUMBER;
v_egd_loc_extra_info_id NUMBER;
v_egd_ovn NUMBER;
v_eeo_information VARCHAR2(50):='EEO-1 Specific Information';
v_est_information VARCHAR2(50):='Establishme nt Information';
--v_job_information_catego ry VARCHAR2(10) :=trim('US');
cursor c_location IS
SELECT stg.rowid, stg.*
from LNHR_LOCATION_STG stg
where stg.status in ('D','E','I')
FOR UPDATE;
ro_location c_location%ROWTYPE;
BEGIN
for ro_location IN c_location
/* OPEN c_location;
LOOP
FETCH c_location into ro_location;
EXIT WHEN c_job%NOTFOUND;
END IF;
*/
LOOP
v_error_message :=NULL;
v_object_version_number:=N ULL;
v_location_id :=NULL;
BEGIN
DBMS_OUTPUT.PUT_LINE('HR_J OB_API Calling......');
HR_LOCATION_API.create_loc ation
( p_validate => FALSE --v_validate
,p_effective_date => TO_DATE('01-JAN-1950','DD- MON-YYYY)
,p_language_code => NULL --VARCHAR2 DEFAULT hr_api.userenv_lang
,p_location_code => ro_location.location_code
,p_description => ro_location.description
,p_timezone_code => NULL
,p_tp_header_id => NULL
,p_ece_tp_location_code => NULL
,p_address_line_1 => ro_location.address_line1
,p_address_line_2 => ro_location.address_line2
,p_address_line_3 => ro_location.address_line3
,p_bill_to_site_flag => NULL
,p_country => ro_location.country
,p_designated_receiver_id => NULL
,p_in_organization_flag => NULL
,p_inactive_date => NULL
,p_operating_unit_id => NULL
,p_inventory_organization_ id => NULL
,p_office_site_flag => NULL
,p_postal_code => ro_location.postal_code
,p_receiving_site_flag => NULL
,p_region_1 => NULL
,p_region_2 => ro_location.region2
,p_region_3 => NULL
,p_ship_to_location_id => NULL
,p_ship_to_site_flag => NULL
,p_style => ro_location.style
,p_tax_name => NULL
,p_telephone_number_1 => NULL
,p_telephone_number_2 => NULL
,p_telephone_number_3 => NULL
,p_town_or_city => ro_location.town_or_city
,p_loc_information13 => NULL
,p_loc_information14 => NULL
,p_loc_information15 => NULL
,p_loc_information16 => NULL
,p_loc_information17 => NULL
,p_loc_information18 => NULL
,p_loc_information19 => NULL
,p_loc_information20 => NULL
,p_attribute_category => NULL
,p_attribute1 => NULL
,p_attribute2 => NULL
,p_attribute3 => NULL
,p_attribute4 => NULL
,p_attribute5 => NULL
,p_attribute6 => NULL
,p_attribute7 => NULL
,p_attribute8 => NULL
,p_attribute9 => NULL
,p_attribute10 => NULL
,p_attribute11 => NULL
,p_attribute12 => NULL
,p_attribute13 => NULL
,p_attribute14 => NULL
,p_attribute15 => NULL
,p_attribute16 => NULL
,p_attribute17 => NULL
,p_attribute18 => NULL
,p_attribute19 => NULL
,p_attribute20 => NULL
,p_global_attribute_catego ry => NULL
,p_global_attribute1 => NULL
,p_global_attribute2 => NULL
,p_global_attribute3 => NULL
,p_global_attribute4 => NULL
,p_global_attribute5 => NULL
,p_global_attribute6 => NULL
,p_global_attribute7 => NULL
,p_global_attribute8 => NULL
,p_global_attribute9 => NULL
,p_global_attribute10 => NULL
,p_global_attribute11 => NULL
,p_global_attribute12 => NULL
,p_global_attribute13 => NULL
,p_global_attribute14 => NULL
,p_global_attribute15 => NULL
,p_global_attribute16 => NULL
,p_global_attribute17 => NULL
,p_global_attribute18 => NULL
,p_global_attribute19 => NULL
,p_global_attribute20 => NULL
,p_business_group_id => ro_location.business_group _id
,p_location_id => v_location_id
,p_object_version_number => v_object_version_number
);
/*Calling HR_LOCATION_EXTRA_INFO_API to Load EIT information for the type EEO-1 Specific Information*/
IF (ro_location.lei_informati on1 IS NOT NULL
OR ro_location.lei_informatio n9 IS NOT NULL)
THEN
v_esd_loc_extra_info_id := NULL;
v_esd_ovn := NULL;
HR_LOCATION_EXTRA_INFO_API .create_lo cation_ext ra_info
(p_validate => FALSE --v_validate
,p_location_id => v_location_id
,p_information_type => v_eeo_information
,p_lei_attribute_category => NULL
,p_lei_attribute1 => NULL
,p_lei_attribute2 => NULL
,p_lei_attribute3 => NULL
,p_lei_attribute4 => NULL
,p_lei_attribute5 => NULL
,p_lei_attribute6 => NULL
,p_lei_attribute7 => NULL
,p_lei_attribute8 => NULL
,p_lei_attribute9 => NULL
,p_lei_attribute10 => NULL
,p_lei_attribute11 => NULL
,p_lei_attribute12 => NULL
,p_lei_attribute13 => NULL
,p_lei_attribute14 => NULL
,p_lei_attribute15 => NULL
,p_lei_attribute16 => NULL
,p_lei_attribute17 => NULL
,p_lei_attribute18 => NULL
,p_lei_attribute19 => NULL
,p_lei_attribute20 => NULL
,p_lei_information_categor y => v_eeo_information
,p_lei_information1 => ro_location.lei_informatio n1
,p_lei_information2 => NULL
,p_lei_information3 => NULL
,p_lei_information4 => NULL
,p_lei_information5 => NULL
,p_lei_information6 => NULL
,p_lei_information7 => NULL
,p_lei_information8 => NULL
,p_lei_information9 => ro_location.lei_informatio n9
,p_lei_information10 => NULL
,p_lei_information11 => NULL
,p_lei_information12 => NULL
,p_lei_information13 => NULL
,p_lei_information14 => NULL
,p_lei_information15 => NULL
,p_lei_information16 => NULL
,p_lei_information17 => NULL
,p_lei_information18 => NULL
,p_lei_information19 => NULL
,p_lei_information20 => NULL
,p_lei_information21 => NULL
,p_lei_information22 => NULL
,p_lei_information23 => NULL
,p_lei_information24 => NULL
,p_lei_information25 => NULL
,p_lei_information26 => NULL
,p_lei_information27 => NULL
,p_lei_information28 => NULL
,p_lei_information29 => NULL
,p_lei_information30 => NULL
,p_location_extra_info_id => v_esd_loc_extra_info_id
,p_object_version_number => v_esd_ovn
);
END IF;
/*Calling HR_LOCATION_EXTRA_INFO_API to Load EIT information for the type EEO1 VETS Generic Data*/
IF (ro_location.lei_informati on19 IS NOT NULL)
THEN
v_egd_loc_extra_info_id := NULL;
v_egd_ovn := NULL;
HR_LOCATION_EXTRA_INFO_API .create_lo cation_ext ra_info
(p_validate => FALSE--v_validate
,p_location_id => v_location_id
,p_information_type => v_est_information
,p_lei_attribute_category => NULL
,p_lei_attribute1 => NULL
,p_lei_attribute2 => NULL
,p_lei_attribute3 => NULL
,p_lei_attribute4 => NULL
,p_lei_attribute5 => NULL
,p_lei_attribute6 => NULL
,p_lei_attribute7 => NULL
,p_lei_attribute8 => NULL
,p_lei_attribute9 => NULL
,p_lei_attribute10 => NULL
,p_lei_attribute11 => NULL
,p_lei_attribute12 => NULL
,p_lei_attribute13 => NULL
,p_lei_attribute14 => NULL
,p_lei_attribute15 => NULL
,p_lei_attribute16 => NULL
,p_lei_attribute17 => NULL
,p_lei_attribute18 => NULL
,p_lei_attribute19 => NULL
,p_lei_attribute20 => NULL
,p_lei_information_categor y => v_est_information
,p_lei_information1 => NULL
,p_lei_information2 => NULL
,p_lei_information3 => NULL
,p_lei_information4 => NULL
,p_lei_information5 => NULL
,p_lei_information6 => NULL
,p_lei_information7 => NULL
,p_lei_information8 => NULL
,p_lei_information9 => NULL
,p_lei_information10 => NULL
,p_lei_information11 => NULL
,p_lei_information12 => NULL
,p_lei_information13 => NULL
,p_lei_information14 => NULL
,p_lei_information15 => NULL
,p_lei_information16 => NULL
,p_lei_information17 => NULL
,p_lei_information18 => NULL
,p_lei_information19 => ro_location.lei_informatio n19
,p_lei_information20 => NULL
,p_lei_information21 => NULL
,p_lei_information22 => NULL
,p_lei_information23 => NULL
,p_lei_information24 => NULL
,p_lei_information25 => NULL
,p_lei_information26 => NULL
,p_lei_information27 => NULL
,p_lei_information28 => NULL
,p_lei_information29 => NULL
,p_lei_information30 => NULL
,p_location_extra_info_id => v_egd_loc_extra_info_id
,p_object_version_number => v_egd_ovn
);
END IF;
DBMS_OUTPUT.PUT_LINE('upda ting staging');
UPDATE LNHR_LOCATION_STG STG
set STG.status = 'P'
,STG.error_message = NULL
,STG.location_id = v_location_id
,STG.object_version_number = v_object_version_number
,STG.esd_loc_extra_info_id = v_esd_loc_extra_info_id
,STG.esd_ovn = v_esd_ovn
,STG.egd_loc_extra_info_id = v_egd_loc_extra_info_id
,STG.egd_ovn = v_egd_ovn
,STG.last_updated_by = 1111-- gv_user_id
,STG.LAST_UPDATE_DATE = SYSDATE
,STG.last_update_login = 1111-- gv_user_id
where CURRENT OF ro_location ; --STG.rowid = ro_location.rowid;
EXCEPTION
WHEN OTHERS THEN
v_error_message := SUBSTR(SQLERRM,1,500);
UPDATE LNHR_LOCATION_STG
set status = 'E'
,error_message = 'API Validation Error'||v_error_message
where rowid = ro_location.rowid;
END Loop;
END;
EXCEPTION
WHEN OTHERS THEN
p_errbuf := SUBSTR(SQLERRM,1,500);
p_retcode := 2;
END import;
procedure main(p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER)
IS
v_errbuf VARCHAR2(4000);
v_retcode NUMBER;
v_error_stage VARCHAR2(1000);
EX_USER_EXCEPTION EXCEPTION;
BEGIN
--gv_user_id := FND_PROFILE.VALUE('USER_ID ');
--FND_FILE.PUT_LINE(FND_FI LE.LOG, 'Request Id - '||gv_request_id);
v_error_stage := 'Updating the table with default values';
dbms_output.put_line ( 'mail starts..........');
UPDATE LNHR_LOCATION_STG LHLS
set LHLS.status = 'U'
,LHLS.business_group_id = gv_business_group_id
,LHLS.request_id = gv_request_id
,LHLS.created_by = 12345 --gv_user_id
,LHLS.creation_date = SYSDATE
,LHLS.last_updated_by = 12345 --gv_user_id
,LHLS.LAST_UPDATE_DATE = SYSDATE
,LHLS.last_update_login = 12345 --gv_user_id;
--WHERE LHLS.request_id =NULL;
commit;
v_error_stage := 'Calling Validate';
validate (p_errbuf =>v_errbuf,
p_retcode =>v_retcode
) ;
IF(v_retcode = 2) THEN
RAISE EX_USER_EXCEPTION;
END IF;
/*v_error_stage := 'Calling Import';
import(p_errbuf =>v_errbuf,
p_retcode =>v_retcode
) ;
IF(v_retcode = 2) THEN
RAISE EX_USER_EXCEPTION;
END IF;*/
EXCEPTION
WHEN EX_USER_EXCEPTION THEN
p_retcode :=2;
p_errbuf := v_errbuf;
--DBMS_OUTPUT.PUT_LINE('Fa iled During -'|| v_error_stage||'Error'|| p_errbuf);
--FND_FILE.PUT_LINE(FND_FI LE.LOG, 'Failed During -'|| v_error_stage||'Error'|| p_errbuf);
WHEN OTHERS THEN
p_retcode :=2;
p_errbuf := v_error_stage||' '||SUBSTR(SQLERRM,1,500);
DBMS_OUTPUT.PUT_LINE('Unkn ow error'|| p_errbuf);
--FND_FILE.PUT_LINE(FND_FI LE.LOG, 'Unknow error'|| p_errbuf);
END main;
END LNHR_LOCATION_PKG;
/
sho err;
-------- --------------------------
334/24 PLS-00103: Encountered the symbol "UPDATING" when expecting one
of the following:
. ( ) , * @ % & | = - + < / > at in is mod not range rem =>
.. <an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
here is my code
CREATE or REPLACE PACKAGE BODY LNHR_LOCATION_PKG
AS
Procedure validate (p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER
)
IS
v_error_message VARCHAR2(4000);
v_error_stage VARCHAR2(200);
cursor c_location IS
SELECT stg.rowid, stg.*
from LNHR_LOCATION_STG stg
where stg.status in ('U','E','I');
ro_location c_location%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('firs
v_error_message :=NULL;
OPEN c_location;
LOOP
FETCH c_location into ro_location;
EXIT WHEN c_job%NOTFOUND;
BEGIN
v_error_stage := 'Manadatory Column Validation';
SELECT DECODE (ro_location.location_code
DECODE (ro_location.address_line1
DECODE (ro_location.town_or_city,
DECODE (ro_location.country,NULL,
DECODE (ro_location.postal_code,N
into v_error_message
FROM sys.dual;
v_error_stage := 'Updating Interim table with error messages';
UPDATE LNHR_LOCATION_STG LHLS
set LHLS.error_message = v_error_message
,LHLS.status = DECODE(v_error_message,NUL
where LHLS.rowid = ro_location.rowid;
end;
END LOOP;
close c_location;
EXCEPTION
WHEN OTHERS THEN
p_retcode :=2;
p_errbuf := v_error_stage ||' - '|| SQLERRM;
END VALIDATE;
PROCEDURE import(p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER)
IS
v_object_version_number NUMBER;
v_error_stage VARCHAR2(4000);
v_error_message VARCHAR2(2000);
v_validate BOOLEAN:=FALSE;
v_location_id NUMBER;
v_job_id NUMBER;
v_ppc_object_version_numbe
v_bg_grp_id NUMBER :=0;
v_esd_loc_extra_info_id NUMBER;
v_esd_ovn NUMBER;
v_egd_loc_extra_info_id NUMBER;
v_egd_ovn NUMBER;
v_eeo_information VARCHAR2(50):='EEO-1 Specific Information';
v_est_information VARCHAR2(50):='Establishme
--v_job_information_catego
cursor c_location IS
SELECT stg.rowid, stg.*
from LNHR_LOCATION_STG stg
where stg.status in ('D','E','I')
FOR UPDATE;
ro_location c_location%ROWTYPE;
BEGIN
for ro_location IN c_location
/* OPEN c_location;
LOOP
FETCH c_location into ro_location;
EXIT WHEN c_job%NOTFOUND;
END IF;
*/
LOOP
v_error_message :=NULL;
v_object_version_number:=N
v_location_id :=NULL;
BEGIN
DBMS_OUTPUT.PUT_LINE('HR_J
HR_LOCATION_API.create_loc
( p_validate => FALSE --v_validate
,p_effective_date => TO_DATE('01-JAN-1950','DD-
,p_language_code => NULL --VARCHAR2 DEFAULT hr_api.userenv_lang
,p_location_code => ro_location.location_code
,p_description => ro_location.description
,p_timezone_code => NULL
,p_tp_header_id => NULL
,p_ece_tp_location_code => NULL
,p_address_line_1 => ro_location.address_line1
,p_address_line_2 => ro_location.address_line2
,p_address_line_3 => ro_location.address_line3
,p_bill_to_site_flag => NULL
,p_country => ro_location.country
,p_designated_receiver_id => NULL
,p_in_organization_flag => NULL
,p_inactive_date => NULL
,p_operating_unit_id => NULL
,p_inventory_organization_
,p_office_site_flag => NULL
,p_postal_code => ro_location.postal_code
,p_receiving_site_flag => NULL
,p_region_1 => NULL
,p_region_2 => ro_location.region2
,p_region_3 => NULL
,p_ship_to_location_id => NULL
,p_ship_to_site_flag => NULL
,p_style => ro_location.style
,p_tax_name => NULL
,p_telephone_number_1 => NULL
,p_telephone_number_2 => NULL
,p_telephone_number_3 => NULL
,p_town_or_city => ro_location.town_or_city
,p_loc_information13 => NULL
,p_loc_information14 => NULL
,p_loc_information15 => NULL
,p_loc_information16 => NULL
,p_loc_information17 => NULL
,p_loc_information18 => NULL
,p_loc_information19 => NULL
,p_loc_information20 => NULL
,p_attribute_category => NULL
,p_attribute1 => NULL
,p_attribute2 => NULL
,p_attribute3 => NULL
,p_attribute4 => NULL
,p_attribute5 => NULL
,p_attribute6 => NULL
,p_attribute7 => NULL
,p_attribute8 => NULL
,p_attribute9 => NULL
,p_attribute10 => NULL
,p_attribute11 => NULL
,p_attribute12 => NULL
,p_attribute13 => NULL
,p_attribute14 => NULL
,p_attribute15 => NULL
,p_attribute16 => NULL
,p_attribute17 => NULL
,p_attribute18 => NULL
,p_attribute19 => NULL
,p_attribute20 => NULL
,p_global_attribute_catego
,p_global_attribute1 => NULL
,p_global_attribute2 => NULL
,p_global_attribute3 => NULL
,p_global_attribute4 => NULL
,p_global_attribute5 => NULL
,p_global_attribute6 => NULL
,p_global_attribute7 => NULL
,p_global_attribute8 => NULL
,p_global_attribute9 => NULL
,p_global_attribute10 => NULL
,p_global_attribute11 => NULL
,p_global_attribute12 => NULL
,p_global_attribute13 => NULL
,p_global_attribute14 => NULL
,p_global_attribute15 => NULL
,p_global_attribute16 => NULL
,p_global_attribute17 => NULL
,p_global_attribute18 => NULL
,p_global_attribute19 => NULL
,p_global_attribute20 => NULL
,p_business_group_id => ro_location.business_group
,p_location_id => v_location_id
,p_object_version_number => v_object_version_number
);
/*Calling HR_LOCATION_EXTRA_INFO_API
IF (ro_location.lei_informati
OR ro_location.lei_informatio
THEN
v_esd_loc_extra_info_id := NULL;
v_esd_ovn := NULL;
HR_LOCATION_EXTRA_INFO_API
(p_validate => FALSE --v_validate
,p_location_id => v_location_id
,p_information_type => v_eeo_information
,p_lei_attribute_category => NULL
,p_lei_attribute1 => NULL
,p_lei_attribute2 => NULL
,p_lei_attribute3 => NULL
,p_lei_attribute4 => NULL
,p_lei_attribute5 => NULL
,p_lei_attribute6 => NULL
,p_lei_attribute7 => NULL
,p_lei_attribute8 => NULL
,p_lei_attribute9 => NULL
,p_lei_attribute10 => NULL
,p_lei_attribute11 => NULL
,p_lei_attribute12 => NULL
,p_lei_attribute13 => NULL
,p_lei_attribute14 => NULL
,p_lei_attribute15 => NULL
,p_lei_attribute16 => NULL
,p_lei_attribute17 => NULL
,p_lei_attribute18 => NULL
,p_lei_attribute19 => NULL
,p_lei_attribute20 => NULL
,p_lei_information_categor
,p_lei_information1 => ro_location.lei_informatio
,p_lei_information2 => NULL
,p_lei_information3 => NULL
,p_lei_information4 => NULL
,p_lei_information5 => NULL
,p_lei_information6 => NULL
,p_lei_information7 => NULL
,p_lei_information8 => NULL
,p_lei_information9 => ro_location.lei_informatio
,p_lei_information10 => NULL
,p_lei_information11 => NULL
,p_lei_information12 => NULL
,p_lei_information13 => NULL
,p_lei_information14 => NULL
,p_lei_information15 => NULL
,p_lei_information16 => NULL
,p_lei_information17 => NULL
,p_lei_information18 => NULL
,p_lei_information19 => NULL
,p_lei_information20 => NULL
,p_lei_information21 => NULL
,p_lei_information22 => NULL
,p_lei_information23 => NULL
,p_lei_information24 => NULL
,p_lei_information25 => NULL
,p_lei_information26 => NULL
,p_lei_information27 => NULL
,p_lei_information28 => NULL
,p_lei_information29 => NULL
,p_lei_information30 => NULL
,p_location_extra_info_id => v_esd_loc_extra_info_id
,p_object_version_number => v_esd_ovn
);
END IF;
/*Calling HR_LOCATION_EXTRA_INFO_API
IF (ro_location.lei_informati
THEN
v_egd_loc_extra_info_id := NULL;
v_egd_ovn := NULL;
HR_LOCATION_EXTRA_INFO_API
(p_validate => FALSE--v_validate
,p_location_id => v_location_id
,p_information_type => v_est_information
,p_lei_attribute_category => NULL
,p_lei_attribute1 => NULL
,p_lei_attribute2 => NULL
,p_lei_attribute3 => NULL
,p_lei_attribute4 => NULL
,p_lei_attribute5 => NULL
,p_lei_attribute6 => NULL
,p_lei_attribute7 => NULL
,p_lei_attribute8 => NULL
,p_lei_attribute9 => NULL
,p_lei_attribute10 => NULL
,p_lei_attribute11 => NULL
,p_lei_attribute12 => NULL
,p_lei_attribute13 => NULL
,p_lei_attribute14 => NULL
,p_lei_attribute15 => NULL
,p_lei_attribute16 => NULL
,p_lei_attribute17 => NULL
,p_lei_attribute18 => NULL
,p_lei_attribute19 => NULL
,p_lei_attribute20 => NULL
,p_lei_information_categor
,p_lei_information1 => NULL
,p_lei_information2 => NULL
,p_lei_information3 => NULL
,p_lei_information4 => NULL
,p_lei_information5 => NULL
,p_lei_information6 => NULL
,p_lei_information7 => NULL
,p_lei_information8 => NULL
,p_lei_information9 => NULL
,p_lei_information10 => NULL
,p_lei_information11 => NULL
,p_lei_information12 => NULL
,p_lei_information13 => NULL
,p_lei_information14 => NULL
,p_lei_information15 => NULL
,p_lei_information16 => NULL
,p_lei_information17 => NULL
,p_lei_information18 => NULL
,p_lei_information19 => ro_location.lei_informatio
,p_lei_information20 => NULL
,p_lei_information21 => NULL
,p_lei_information22 => NULL
,p_lei_information23 => NULL
,p_lei_information24 => NULL
,p_lei_information25 => NULL
,p_lei_information26 => NULL
,p_lei_information27 => NULL
,p_lei_information28 => NULL
,p_lei_information29 => NULL
,p_lei_information30 => NULL
,p_location_extra_info_id => v_egd_loc_extra_info_id
,p_object_version_number => v_egd_ovn
);
END IF;
DBMS_OUTPUT.PUT_LINE('upda
UPDATE LNHR_LOCATION_STG STG
set STG.status = 'P'
,STG.error_message = NULL
,STG.location_id = v_location_id
,STG.object_version_number
,STG.esd_loc_extra_info_id
,STG.esd_ovn = v_esd_ovn
,STG.egd_loc_extra_info_id
,STG.egd_ovn = v_egd_ovn
,STG.last_updated_by = 1111-- gv_user_id
,STG.LAST_UPDATE_DATE = SYSDATE
,STG.last_update_login = 1111-- gv_user_id
where CURRENT OF ro_location ; --STG.rowid = ro_location.rowid;
EXCEPTION
WHEN OTHERS THEN
v_error_message := SUBSTR(SQLERRM,1,500);
UPDATE LNHR_LOCATION_STG
set status = 'E'
,error_message = 'API Validation Error'||v_error_message
where rowid = ro_location.rowid;
END Loop;
END;
EXCEPTION
WHEN OTHERS THEN
p_errbuf := SUBSTR(SQLERRM,1,500);
p_retcode := 2;
END import;
procedure main(p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER)
IS
v_errbuf VARCHAR2(4000);
v_retcode NUMBER;
v_error_stage VARCHAR2(1000);
EX_USER_EXCEPTION EXCEPTION;
BEGIN
--gv_user_id := FND_PROFILE.VALUE('USER_ID
--FND_FILE.PUT_LINE(FND_FI
v_error_stage := 'Updating the table with default values';
dbms_output.put_line ( 'mail starts..........');
UPDATE LNHR_LOCATION_STG LHLS
set LHLS.status = 'U'
,LHLS.business_group_id = gv_business_group_id
,LHLS.request_id = gv_request_id
,LHLS.created_by = 12345 --gv_user_id
,LHLS.creation_date = SYSDATE
,LHLS.last_updated_by = 12345 --gv_user_id
,LHLS.LAST_UPDATE_DATE = SYSDATE
,LHLS.last_update_login = 12345 --gv_user_id;
--WHERE LHLS.request_id =NULL;
commit;
v_error_stage := 'Calling Validate';
validate (p_errbuf =>v_errbuf,
p_retcode =>v_retcode
) ;
IF(v_retcode = 2) THEN
RAISE EX_USER_EXCEPTION;
END IF;
/*v_error_stage := 'Calling Import';
import(p_errbuf =>v_errbuf,
p_retcode =>v_retcode
) ;
IF(v_retcode = 2) THEN
RAISE EX_USER_EXCEPTION;
END IF;*/
EXCEPTION
WHEN EX_USER_EXCEPTION THEN
p_retcode :=2;
p_errbuf := v_errbuf;
--DBMS_OUTPUT.PUT_LINE('Fa
--FND_FILE.PUT_LINE(FND_FI
WHEN OTHERS THEN
p_retcode :=2;
p_errbuf := v_error_stage||' '||SUBSTR(SQLERRM,1,500);
DBMS_OUTPUT.PUT_LINE('Unkn
--FND_FILE.PUT_LINE(FND_FI
END main;
END LNHR_LOCATION_PKG;
/
sho err;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.