-----------------------
ERROR: syntax error at or near "*"
LINE 35: SELECT * from logError(msg text) is
^
CONTEXT: invalid type name "* from logError(msg text) is
BEGIN
insert into SMERROR_LOG
(error_message, error_log_id, method_name, subscriber_id, company_id, creation_date, creation_user, update_date, update_user)
values(msg, nextval('smerror_log_sequence'), 'Gen_Budget_Periods_ALL_ForYear',subscriberID, companyID, LOCALTIMESTAMP, 'Admin', LOCALTIMESTAMP, 'Admin')"
********** Error **********
ERROR: syntax error at or near "*"
SQL state: 42601
Character: 1276
Context: invalid type name "* from logError(msg text) is
BEGIN
insert into SMERROR_LOG
(error_message, error_log_id, method_name, subscriber_id, company_id, creation_date, creation_user, update_date, update_user)
values(msg, nextval('smerror_log_sequence'), 'Gen_Budget_Periods_ALL_ForYear',subscriberID, companyID, LOCALTIMESTAMP, 'Admin', LOCALTIMESTAMP, 'Admin')"
--------------
Function Code as below:
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Function: cp_property_room_count_trans(bigint, bigint, text)
-- DROP FUNCTION cp_property_room_count_trans(bigint, bigint, text);
CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
subscriberid bigint,
incompanyid bigint,
loginname text)
RETURNS void AS
$BODY$
DECLARE
CRS_1 CURSOR FOR SELECT distinct company_id from CP_ROOM_COUNT_STAGING where subscriber_id=subscriberID and (process_flag is null or process_flag=0) order by company_id;
--Version:
-- 05/16/07 (Bea) transformed data from CP_ROOM_COUNT_STAGING
-- into CP_ROOM_TYPE_COUNT and CP_PROPERTY_PHASE tables
-- 06/08/07 (Bea) insert value for CP_ROOM_TYPE_COUNT.room_budget_home_amt
-- If phaseID is new, validate that these as required fields :CORP_PHASE_ID, PHASE_DESCRIPTION or PHASE_START_DATE
-- Will set process_flag=2 if fail the validation.
-- CP_PROPERTY_PHASE.PHASE_1_CODE field must be populated per business rule 15327
--
RW_1 RECORD; --CRS_1 %ROWTYPE;
err_msg_1 varchar(100);
v_errorMessage_1 varchar(4000);
sucessfulRecCount bigint :=0;
failedRecCount bigint :=0;
--************************************************************
SELECT logError(v_errorMesg text, procedureName text, subscriberID bigint, companyID bigint, supplierSku text, loginName text) is
BEGIN
insert into SMERROR_LOG
(error_message, method_name, system_message, error_log_id, subscriber_id, company_id, creation_date, creation_user, update_date, update_user)
values(v_errorMesg, procedureName, supplierSku, nextval('smerror_log_sequence'), subscriberID, companyID, LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin');
end;
--************************************************************
FUNCTION Invoke_InsertPropertyRCProcess(subID in number, compID in number, loginName in varchar2) is
CRS CURSOR FOR
SELECT st.*, st.rowid, ph.project_code from CP_ROOM_COUNT_STAGING st, CP_PROJECT_PHASE ph
where st.SUBSCRIBER_ID =subID and st.company_id=compID and (st.process_flag is null or st.process_flag=0)
and ph.SUBSCRIBER_ID(+)= st.SUBSCRIBER_ID AND ph.PHASE_ID(+)= st.CORP_PHASE_ID order by st.subscriber_id, st.company_id, st.phase_id;
RW CRS%ROWTYPE;
err_msg varchar2(100);
v_errorMessage varchar2(4000);
v_errorCountExp number :=0;
MAX_ERR_WRITTEN constant number :=10;
recCount number := 0;
checkCount number;
startRowCounter number :=0;
isValidated boolean :=true;
begin
logError('Begin Invoke_InsertPropertyRCProcess subID:'|| subID || ' and compID:'||compID,'Invoke_InsertPropertyRCProcess', subID, compID,null, loginName);
-- the row_number counter starting number
select max(Row_number) into startRowCounter from CP_ROOM_TYPE_COUNT
where subscriber_id=subID and company_id=compID;
if(startRowCounter is null) then
startRowCounter :=0;
end if;
open CRS;
loop
begin -- the begin inside the loop
fetch CRS into RW;
IF NOT FOUND THEN EXIT; END IF; -- apply on CRS
recCount := recCount+1;
startRowCounter := startRowCounter+1;
--check to if phase_id already existed in CP_PROPERTY_PHASE if not insert
select count(1) into checkCount from CP_PROPERTY_PHASE
where subscriber_id=subID and company_id=compID and PHASE_ID=RW.PHASE_ID;
isValidated := true;
--check to see if pass validation.
if(checkCount =0 and (RW.phase_start_date is null or RW.phase_description is null or RW.corp_phase_id is null)) then
isValidated:=false;
--update processing flag to 2 as fail.
update CP_ROOM_COUNT_STAGING set process_flag=2 where rowid=RW.rowid;
failedRecCount := failedRecCount+1;
end if;
if(isValidated = true) then
if(checkCount =0 ) then -- if phase_id not yet existed in the cp_property_phase table
insert into cp_property_phase
(subscriber_id, company_id, phase_id, phase_seq, phase_description,
corp_phase_id, phase_start_date, duration_type, phase_status, po_created_flag,
project_code, phase_level_path, is_deleted, phase_1_code,
creation_date, creation_user)
values
(subID, compID, RW.phase_id, RW.phase_id, RW.phase_description,
RW.corp_phase_id, RW.phase_start_date, 'W', 0, 0,
RW.project_code, RW.phase_id, 0,
lpad(to_char(RW.phase_id),5,'0')||' '||upper(substring(RW.phase_description from 1 for 40)),
LOCALTIMESTAMP, loginName);
end if;--if(checkCount =0 )
--insert into CP_ROOM_TYPE_COUNT table
insert into cp_room_type_count
(subscriber_id, company_id, phase_id, room_type_code,
room_count, room_measure_unit, room_length, room_width,
room_height, bath_length, bath_width, row_number, room_budget_home_amt,
creation_date, creation_user)
values
(subID, compID, RW.phase_id, RW.room_type_code,
RW.room_count, RW.room_measure_unit, RW.room_length, RW.room_width,
RW.room_height, RW.bath_length, RW.bath_width, startRowCounter, RW.room_budget_home_amt,
LOCALTIMESTAMP, loginName);
--update processing flag to 1 as successful.
update CP_ROOM_COUNT_STAGING set process_flag=1 where rowid=RW.rowid;
sucessfulRecCount := sucessfulRecCount +1;
if(mod(recCount, 500) = 0 ) then
commit;
end if;
end if; -- if(isValidated = true)
exception
when others then
if(v_errorCountExp < MAX_ERR_WRITTEN) then
err_msg := substring(SQLERRM from 1 for 100);
v_errorMessage:= 'Run into Exception in Invoke_InsertPropertyRCProcess: ' || err_msg;
logError(v_errorMessage, 'Invoke_InsertPropertyRCProcess', subID, compID,null, loginName);
end if;
v_errorCountExp := v_errorCountExp+1;
commit;
end; --for inside begin
end loop;
close CRS;
if( failedRecCount > 0) then
logError('Error: set cp_room_count_staging.process_flag=2 ( Failed to insert new records dued to one of these required fields are empty: Corp Phase ID, Phase Desc or Phase Start Date)','Invoke_InsertPropertyRCProcess', subID, compID,null, loginName);
end if;
logError('Ending Invoke_InsertPropertyRCProcess Total records: '||recCount|| '. There are ' || sucessfulRecCount || ' sucessfull and ' || failedRecCount ||' failed records.','Invoke_InsertPropertyRCProcess', subID, compID,null, loginName);
commit;
end Invoke_InsertPropertyRCProcess;
--************************************************************
begin
logError('Begin CP_Property_Room_Count_Trans ','CP_Property_Room_Count_Trans', subscriberID, inCompanyID,null, loginName);
open CRS_1;
loop
begin -- the begin inside the loop
fetch CRS_1 into RW_1;
IF NOT FOUND THEN EXIT; END IF; -- apply on CRS_1
sucessfulRecCount := 0;
failedRecCount := 0;
Invoke_InsertPropertyRCProcess(subscriberID, RW_1.company_ID, loginName);
exception
when others then
err_msg_1 := substring(SQLERRM from 1 for 100);
v_errorMessage_1:= 'Run into Exception in CP_Property_Room_Count_Trans: ' || err_msg_1;
logError(v_errorMessage_1, 'CP_Property_Room_Count_Trans', subscriberID, inCompanyID,null, loginName);
commit;
end; --for inside begin
end loop;
close CRS_1;
logError('Ending CP_Property_Room_Count_Trans.','CP_Property_Room_Count_Trans', subscriberID, inCompanyID,null, loginName);
commit;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION cp_property_room_count_trans(bigint, bigint, text)
OWNER TO postgres;
