oracle_package.sql

create or replace PACKAGE BODY oracle_package
AS
	------------ 1. AGENT table --------------
	FUNCTION f_agent (
   agent_id IN AGENT.AGENTID%TYPE,
    agency_id IN AGENT.AGENCYID%TYPE,
    fname IN AGENT.FIRSTNAME%TYPE,
    mname IN AGENT.MIDDLENAME%TYPE,
    lname IN AGENT.LASTNAME%TYPE,
    n_gender IN AGENT.GENDER%TYPE,
    n_dob IN AGENT.DOB%TYPE,
    tax_id IN AGENT.TAXID%TYPE,
    alt_tax_id IN AGENT.ALTTAXID%TYPE,
    business_from IN AGENT.BUSINESSFROM%TYPE,
    business_type IN AGENT.BUSINESSTYPE%TYPE,
    n_status IN AGENT.STATUS%TYPE,
    is1 IN AGENT.ISVESTEDPERSONAL%TYPE,
    is2 IN AGENT.ISVESTEDOVERRIDE%TYPE,
    email IN AGENT.EMAILADDRESS%TYPE,
    agent_level IN AGENT.AGENTLEVEL%TYPE,
		user IN varchar2,
    o_agency IN AGENT.ORIGINALAGENCY%TYPE,
    company_name IN AGENT.COMPANYNAME%TYPE
    )
			RETURN VARCHAR2
	IS
		v_today CONSTANT DATE := sysdate;
		v_status VARCHAR2(100);
		v_agent_id NUMBER := agent_id;
	BEGIN
		IF agent_id > 0 THEN
			v_status := 'UPDATE: ';
				UPDATE AGENT SET
					AGENCYID=agency_id, FIRSTNAME=fname, MIDDLENAME=mname, LASTNAME=lname, GENDER=n_gender, DOB=n_dob, TAXID=tax_id, ALTTAXID=alt_tax_id, BUSINESSFROM=business_from, BUSINESSTYPE=business_type, STATUS=n_status, ISVESTEDPERSONAL=is1, AGENTLEVEL=agent_level, ISVESTEDOVERRIDE=is2, EMAILADDRESS=email, MODIFY_DATE=v_today, COMPANYNAME=company_name,
					ORIGINALAGENCY=o_agency, MODIFY_USER=user WHERE AGENTID = agent_id;
		ELSE
			v_status := 'INSERT: ';
			insert into AGENT values(agency_id, SEQ_AGENT_AGENTID.NEXTVAL,
				fname, mname, lname, n_gender, n_dob, tax_id, alt_tax_id, business_from,
				business_type, n_status, is1, is2, email, v_today, NULL, user, NULL, agent_level, 0, o_agency, company_name, 'N'
				);
		END IF;
		COMMIT;
		IF v_agent_id = 0 then
			select agentid into v_agent_id from agent where agencyid=agency_id and firstname=fname and lastname=lname;
		END IF;
		RETURN v_status || to_char(v_agent_id);
	--exception handler code that should include a RETURN.
	EXCEPTION
		when OTHERS then
		rollback;
		-- Return status is: Oralce: bf:03-DEC-07,v_bf:03-DEC-07,dob:03-DEC-07,v_dob:03-DEC-077759
		-- v_status := 'Oralce: bf:'||business_from||',v_bf:'||v_bf||',dob:'||dob||',v_dob:'||v_dob;
	END f_agent;
	------------ 2. AGENTCONTACT table --------------
	FUNCTION f_agent_contact (
    agent_id IN AGENTCONTACT.AGENTID%TYPE,
    n_attn IN AGENTCONTACT.ATTN%TYPE,
    addr1 IN AGENTCONTACT.ADDRESS1%TYPE,
    addr2 IN AGENTCONTACT.ADDRESS2%TYPE,
    n_city IN AGENTCONTACT.CITY%TYPE,
    n_state IN AGENTCONTACT.STATE%TYPE,
    n_country IN AGENTCONTACT.COUNTRY%TYPE,
    maddr1 IN AGENTCONTACT.MADDRESS1%TYPE,
    maddr2 IN AGENTCONTACT.MADDRESS2%TYPE,
    n_mcity IN AGENTCONTACT.MCITY%TYPE,
    n_mstate IN AGENTCONTACT.MSTATE%TYPE,
    n_mcountry IN AGENTCONTACT.MCOUNTRY%TYPE,
    n_phone1 IN AGENTCONTACT.PHONE1%TYPE,
    n_phone2 IN AGENTCONTACT.PHONE2%TYPE,
    n_mobile IN AGENTCONTACT.MOBILE%TYPE,
    n_fax IN AGENTCONTACT.FAX%TYPE,
    n_comments IN AGENTCONTACT.COMMENTS%TYPE,
    user IN varchar2,
    n_zip IN AGENTCONTACT.ZIP%TYPE,
    n_mzip IN AGENTCONTACT.MZIP%TYPE,
    s_addr1 IN AGENTCONTACT.SADDRESS1%TYPE,
    s_addr2 IN AGENTCONTACT.SADDRESS2%TYPE,
    s_city IN AGENTCONTACT.SCITY%TYPE,
    s_state IN AGENTCONTACT.SSTATE%TYPE,
    s_country IN AGENTCONTACT.SCOUNTRY%TYPE,
    s_zip IN AGENTCONTACT.SZIP%TYPE)
		RETURN VARCHAR2
	IS
		v_count NUMBER;
		v_today CONSTANT DATE := sysdate;
		v_status varchar2(100);
	BEGIN
		select count(*) into v_count from AGENTCONTACT where AGENTID = agent_id;
		IF v_count > 0 THEN
			v_status := 'UPDATE: ';
			UPDATE AGENTCONTACT SET
					ATTN=n_attn, ADDRESS1=addr1, ADDRESS2=addr2, CITY=n_city, STATE=n_state, COUNTRY=n_country, ZIP=n_zip,
					MADDRESS1=maddr1, MADDRESS2=maddr2, MCITY=n_mcity, MSTATE=n_mstate, MCOUNTRY=n_mcountry, MZIP=n_mzip,
					PHONE1=n_phone1, PHONE2=n_phone2, MOBILE=n_mobile, FAX=n_fax, COMMENTS=n_comments, MODIFY_DATE=v_today, MODIFY_USER=user,
SADDRESS1=s_addr1, SADDRESS2=s_addr2, SCITY=s_city, SSTATE=s_state, SCOUNTRY=s_country, SZIP=s_zip
			WHERE AGENTID = agent_id;
		ELSE
			v_status := 'INSERT: ';
			-- record not exists, do 'insert'.
			INSERT into AGENTCONTACT values( agent_id,
				n_attn, addr1, addr2, n_city, n_state, n_country, n_zip,
				maddr1, maddr2, n_mcity, n_mstate, n_mcountry, n_mzip,
				n_phone1, n_phone2, n_mobile, n_fax, n_comments, v_today, '', user, '',
			s_addr1, s_addr2, s_city, s_state, s_country, s_zip);
		END IF;
		COMMIT;
		RETURN v_status || to_char(agent_id);
	EXCEPTION
		WHEN OTHERS THEN
			-- debugMsg(sqlerrm);
			ROLLBACK;
	END f_agent_contact;
	------------ 3. AGENTCRM table --------------
	FUNCTION f_agent_crm (
		agent_id IN AGENTCRM.AGENTID%TYPE,
		note_id IN AGENTCRM.NOTEID%TYPE,
		note_date IN AGENTCRM.NOTEDATE%TYPE,
		note_header IN AGENTCRM.NOTEHEADER%TYPE,
		n_note IN AGENTCRM.NOTE%TYPE,
		user IN varchar2)
		RETURN VARCHAR2
	IS
		v_count NUMBER;
		v_status VARCHAR2(100);
		v_today CONSTANT DATE := sysdate;
	BEGIN
		select count(*) into v_count from agentcrm where AGENTID = agent_id and NOTEID = note_id;
		IF v_count > 0 THEN
			v_status := 'UPDATE: ';
			UPDATE agentcrm SET
				NOTEDATE=note_date, NOTEHEADER=note_header, NOTE=n_note, MODIFY_DATE=v_today, MODIFY_USER=user
			WHERE AGENTID = agent_id and NOTEID = note_id;
		ELSE
			-- record not exists, do 'insert'.
			v_status := 'INSERT: ';
			INSERT into agentcrm values(
				agent_id, SEQ_AGENTCRM_NOTEID.nextval, note_date,
				note_header, n_note, v_today, '', user, '');
		END IF;
		COMMIT;
		RETURN v_status || to_char(agent_id) || ', ' || to_char(note_id);
	EXCEPTION
		WHEN OTHERS THEN
			-- debugMsg(sqlerrm);
			ROLLBACK;
	END f_agent_crm;
	------------ 4. AGENTDOCS table --------------
	FUNCTION f_agent_docs (
		agent_id IN AGENTDOCS.AGENTID%TYPE,
		doc_date IN AGENTDOCS.DOCDATE%TYPE,
		doc_header IN AGENTDOCS.DOCHEADER%TYPE,
		user IN varchar2,
		doc_id IN AGENTDOCS.DOCID%TYPE
		)
		RETURN VARCHAR2
	IS
		v_count NUMBER;
		v_status VARCHAR2(100);
		v_today CONSTANT DATE := sysdate;
	BEGIN
		select count(*) into v_count from agentdocs where AGENTID = agent_id and DOCID = doc_id;
		IF v_count > 0 THEN
			v_status := 'UPDATE: ';
			UPDATE agentdocs SET
				DOCDATE=doc_date,
				DOCHEADER=doc_header, MODIFY_DATE=v_today, MODIFY_USER=user
			WHERE AGENTID = agent_id and DOCID = doc_id;
		ELSE
			v_status := 'INSERT: dummy';
			--INSERT into agentdocs values( agent_id, SEQ_AGENTDOCS_DOCID.nextval, doc_date, doc_header, NULL, v_today, NULL, user, NULL, '');
		END IF;
		COMMIT;
		RETURN v_status || to_char(agent_id);
	EXCEPTION
		WHEN OTHERS THEN
			-- debugMsg(sqlerrm);
			ROLLBACK;
	END f_agent_docs;
	------------ 5. AGENTLICENSES table --------------
	FUNCTION f_agent_licenses (
		agent_id IN AGENTLICENSES.AGENTID%TYPE,
		n_ec_coverage IN AGENTLICENSES.EO_COVERAGE%TYPE,
		n_eo_expiration IN AGENTLICENSES.EO_EXPIRATION%TYPE,
		license_type IN AGENTLICENSES.LICENSETYPE%TYPE,
		n_state IN AGENTLICENSES.STATE%TYPE,
		n_resident IN AGENTLICENSES.RESIDENT%TYPE,
		license_no IN AGENTLICENSES.LICENSENO%TYPE,
		issue_date IN AGENTLICENSES.ISSUEDATE%TYPE,
		expiry_date IN AGENTLICENSES.EXPIRYDATE%TYPE,
		n_status IN AGENTLICENSES.STATUS%TYPE,
    user IN varchar2,
		license_id IN AGENTLICENSES.LICENSEID%TYPE)
		RETURN VARCHAR2
	IS
		v_count NUMBER default license_id;
		v_status VARCHAR2(100);
		v_today CONSTANT DATE := sysdate;
	BEGIN
		IF license_id > 0 THEN
			v_status := 'UPDATE: ';
			UPDATE AGENTLICENSES SET
				EO_COVERAGE=n_ec_coverage, EO_EXPIRATION=n_eo_expiration,
				LICENSETYPE=license_type, STATE=n_state,
				RESIDENT=n_resident, LICENSENO=license_no,
				ISSUEDATE=issue_date, EXPIRYDATE=expiry_date, STATUS=n_status,
				MODIFY_DATE=v_today, MODIFY_USER=user
			WHERE LICENSEID = license_id;
		ELSE
			v_status := 'INSERT: ';
			select seq_agentlicenses_licenseid.nextval into v_count from dual;
			INSERT into AGENTLICENSES values(
				agent_id, n_ec_coverage, n_eo_expiration,
				license_type, n_state, n_resident,
				license_no, issue_date, expiry_date, n_status, v_today, '', user, '',
				v_count);
		END IF;
		COMMIT;
		RETURN v_status || to_char(agent_id) || ', ' || to_char(v_count);
	EXCEPTION
		WHEN OTHERS THEN
			-- debugMsg(sqlerrm);
			ROLLBACK;
	END f_agent_licenses;

	------------ 6. APPOINTMENT table --------------
	FUNCTION f_appointment (
		agent_id IN APPOINTMENT.AGENTID%TYPE,
		carrier_id IN APPOINTMENT.CARRIERID%TYPE,
		n_state IN APPOINTMENT.STATE%TYPE,
		appt_type IN APPOINTMENT.APPTTYPE%TYPE,
		rece_date IN APPOINTMENT.RECEIVEDDATE%TYPE,
		to_carrier_date IN APPOINTMENT.TOCARRIERDATE%TYPE,
		confirm_date IN APPOINTMENT.CONFIRMEDDATE%TYPE,
		n_status IN APPOINTMENT.STATUS%TYPE,
		n_reason IN APPOINTMENT.REASON%TYPE,
		producer_code IN APPOINTMENT.PRODUCERCODE%TYPE,
		n_note IN APPOINTMENT.NOTE%TYPE,
		id_type IN APPOINTMENT.IDTYPE%TYPE,
    user IN varchar2)
		RETURN VARCHAR2
	IS
		v_count NUMBER;
		v_status VARCHAR2(100);
		v_today CONSTANT DATE := sysdate;

	BEGIN
		select count(*) into v_count from APPOINTMENT 
		where AGENTID = agent_id 
		and CARRIERID = carrier_id 
		and PRODUCERCODE = producer_code
		and STATE = n_state;
		-- and APPTTYPE=appt_type
		IF v_count > 0 THEN
			v_status := 'UPDATE: ';
			UPDATE APPOINTMENT SET
				APPTTYPE=appt_type, RECEIVEDDATE=rece_date,
				TOCARRIERDATE=to_carrier_date,
				CONFIRMEDDATE=confirm_date, STATUS=n_status,
				REASON=n_reason, 
				NOTE=n_note, IDTYPE=id_type, MODIFY_DATE=v_today, MODIFY_BY=user
			WHERE AGENTID = agent_id and CARRIERID = carrier_id and STATE = n_state
				and PRODUCERCODE = producer_code;
		ELSE
			v_status := 'INSERT: ';
			INSERT into APPOINTMENT values( agent_id,
				carrier_id, n_state, appt_type, rece_date,
				to_carrier_date, 	confirm_date,	n_status,
				n_reason, producer_code, n_note, id_type, v_today, NULL, user, '');
		END IF;
		COMMIT;
		RETURN v_status || to_char(agent_id) || ',' || to_char(carrier_id);
	EXCEPTION
		WHEN OTHERS THEN
			-- debugMsg(sqlerrm);
			ROLLBACK;
	END f_appointment;
	------------ 7. AGENTCONTRACTS table --------------
	FUNCTION f_agent_contracts (
    agent_id IN AGENTCONTRACTS.AGENTID%TYPE,
    natp_grad_date IN AGENTCONTRACTS.NATP_GRADDATE%TYPE,
    n_pay_on_issuance IN AGENTCONTRACTS.PAY_ON_ISSUANCE%TYPE,
    n_contract IN AGENTCONTRACTS.CONTRACT%TYPE,
    n_effective_date IN AGENTCONTRACTS.EFFECTIVE_DATE%TYPE,
    until_date IN AGENTCONTRACTS.UNTILDATE%TYPE,
    n_california_adv IN AGENTCONTRACTS.CALIFORNIA_ADV%TYPE,
    n_california_adv_amt IN AGENTCONTRACTS.CALIFORNIA_ADV_AMT%TYPE,
    n_dsmrsm_annuaised_adv IN AGENTCONTRACTS.DSMRSM_ANNUALISED_ADV%TYPE,
    n_dsmrsm_annuaised_adv_amt IN AGENTCONTRACTS.DSMRSM_ANNUALISED_ADV_AMT%TYPE,
    n_dbtblnc_with_renewal IN AGENTCONTRACTS.DBTBLNC_WITH_RENEWAL%TYPE,
    n_dbtblnc_with_renewal_amt IN AGENTCONTRACTS.DBTBLNC_WITH_RENEWAL_AMT%TYPE,
    n_dbtblnc_with_newbusiness IN AGENTCONTRACTS.DBTBLNC_WITH_NEWBUSINESS%TYPE,
    n_dbtblnc_with_newbusiness_amt IN AGENTCONTRACTS.DBTBLNC_WITH_NEWBUSINESS_AMT%TYPE,
    n_reserve_amnt_percent IN AGENTCONTRACTS.RESERVE_AMNT_PERCENT%TYPE,
    n_reserve_release_period IN AGENTCONTRACTS.RESERVE_RELEASE_PERIOD%TYPE,
    payment_method IN AGENTCONTRACTS.PAYMENTMETHOD%TYPE,
    n_bank IN AGENTCONTRACTS.BANK%TYPE,
    account_type IN AGENTCONTRACTS.ACCOUNTTYPE%TYPE,
    account_no IN AGENTCONTRACTS.ACCOUNTNO%TYPE,
    routing_no IN AGENTCONTRACTS.ROUTINGNO%TYPE,
    user IN varchar2,
	contract_id IN AGENTCONTRACTS.CONTRACTID%TYPE
	)
		RETURN VARCHAR2
	IS
		v_count NUMBER default contract_id;
		v_status VARCHAR2(100);
		v_today CONSTANT DATE := sysdate;
	BEGIN
		IF contract_id > 0 THEN
			v_status := 'UPDATE: ';
			UPDATE agentcontracts SET
					NATP_GRADDATE=natp_grad_date, PAY_ON_ISSUANCE=n_pay_on_issuance,
					CONTRACT=n_contract, EFFECTIVE_DATE=n_effective_date,
					UNTILDATE=until_date, CALIFORNIA_ADV=n_california_adv,
					CALIFORNIA_ADV_AMT=n_california_adv_amt, DSMRSM_ANNUALISED_ADV=n_dsmrsm_annuaised_adv,
					DSMRSM_ANNUALISED_ADV_AMT=n_dsmrsm_annuaised_adv_amt,
					DBTBLNC_WITH_RENEWAL=n_dbtblnc_with_renewal,
					DBTBLNC_WITH_RENEWAL_AMT=n_dbtblnc_with_renewal_amt,
					DBTBLNC_WITH_NEWBUSINESS=n_dbtblnc_with_newbusiness,
					DBTBLNC_WITH_NEWBUSINESS_AMT=n_dbtblnc_with_newbusiness_amt,
					RESERVE_AMNT_PERCENT=n_reserve_amnt_percent,
					RESERVE_RELEASE_PERIOD=n_reserve_release_period,
					PAYMENTMETHOD=payment_method, BANK=n_bank,
					ACCOUNTTYPE=account_type, ACCOUNTNO=account_no, ROUTINGNO=routing_no,
					MODIFY_DATE=v_today, MODIFY_USER=user
			WHERE CONTRACTID = contract_id;
		ELSE
			select seq_agentcontracts_contractid.nextval into v_count from dual;
			v_status := 'INSERT: ';
			INSERT into agentcontracts values(agent_id,
				natp_grad_date,
				n_pay_on_issuance,
				n_contract,
				n_effective_date,
				until_date,
				n_california_adv,
				n_california_adv_amt,
				n_dsmrsm_annuaised_adv,
				n_dsmrsm_annuaised_adv_amt,
				n_dbtblnc_with_renewal,
				n_dbtblnc_with_renewal_amt,
				n_dbtblnc_with_newbusiness,
				n_dbtblnc_with_newbusiness_amt,
				n_reserve_amnt_percent,
				n_reserve_release_period,
				payment_method,
				n_bank,
				account_type,
				account_no,
				routing_no,
				v_today,
				'',
				user,
				NULL,
				v_count
				);
		END IF;
		COMMIT;
		RETURN v_status || to_char(agent_id) || to_char(v_count);

	EXCEPTION
		WHEN OTHERS THEN
			-- debugMsg(sqlerrm);
			ROLLBACK;
	END f_agent_contracts;
	------------ 8. Others --------------
	PROCEDURE debugMsg(msg VARCHAR2)
	is
	BEGIN
		dbms_output.put_line('[' || to_char(sysdate, 'YYYYMMDD HH24:MI:SS') || '] - ' || msg);
	END debugMsg;

	procedure delete_agent(agent_id NUMBER)
	AS
	begin
		delete from agentcrm where agentid = agent_id;
		delete from agentdocs where agentid = agent_id;
		delete from agentcontact where agentid = agent_id;
		delete from agentcontracts where agentid = agent_id;
		delete from appointment where agentid = agent_id;
		delete from agentlicenses where agentid = agent_id;
		delete from AGENT where agentid = agent_id;
		commit;
	exception
		when others then
			debugMsg(sqlerrm);
			rollback;
	end delete_agent;

	---------------- 10. f_reminder ------------------
	FUNCTION f_reminder (
		reminder_id IN AGENTREMINDER.REMINDERID%TYPE,
		-- user_id IN AGENTREMINDER.USERID%TYPE,
		agent_id IN AGENTREMINDER.AGENTID%TYPE,
		r_subject IN AGENTREMINDER.SUBJECT%TYPE,
		r_message AGENTREMINDER.MESSAGE%TYPE,
		show_message  AGENTREMINDER.SHOWMESSAGE%TYPE,
		do_not_show AGENTREMINDER.DONOTSHOW%TYPE,
		r_warning IN AGENTREMINDER.WARNING%TYPE,
		user IN varchar2)
		RETURN VARCHAR2
	IS
		v_user_id AUTHUSERS.USERID%TYPE;
		v_status VARCHAR2(100);
		v_today CONSTANT DATE default sysdate;
		v_count AGENTREMINDER.REMINDERID%TYPE default reminder_id;
		--v_show_date DATE := to_date(show_message, 'DD-MON-YY HH24:MI');
		v_show_date DATE := show_message;
	BEGIN
		--return to_char(v_show_date,  'DD-MON-YY HH24:MI') || ', ' || to_char(show_message,  'DD-MON-YY HH24:MI');
		IF reminder_id > 0 THEN
			v_status := 'UPDATE: ';
			UPDATE agentreminder SET
				MESSAGE=r_message, SUBJECT=r_subject, SHOWMESSAGE=v_show_date, DONOTSHOW=do_not_show, WARNING=r_warning, MODIFY_DATE=v_today
			WHERE REMINDERID=reminder_id;
		ELSE
			v_status := 'INSERT: ';
			select userid into v_user_id from authusers where username=user;
			if v_user_id > 0 then
				select seq_agentreminder.nextval into v_count from dual;
				INSERT into agentreminder values(
					v_count, v_user_id, agent_id, r_subject, r_message,
					v_show_date, do_not_show, r_warning, v_today, '');
			end if;
		END IF;
		COMMIT;
		RETURN v_status || to_char(agent_id) || ', ' || to_char(v_count);
	EXCEPTION
		WHEN OTHERS THEN
			-- debugMsg(sqlerrm);
			ROLLBACK;

	END f_reminder;

	-------------  f_agenttraining ---------------
	FUNCTION f_agent_training (
		m0 IN AGENTTRAINING.TRAINID%TYPE,
		m1 IN AGENTTRAINING.AGENTID%TYPE,
		m2 IN AGENTTRAINING.COURSENAME%TYPE,
		m3 IN AGENTTRAINING.COURSEDATE%TYPE,
		m4 IN AGENTTRAINING.CARRIERID%TYPE,
		m5 IN AGENTTRAINING.NOTES%TYPE)
		RETURN VARCHAR2
	IS
		v_count NUMBER;
		v_status VARCHAR2(100);
	BEGIN
		IF m0 > 0 THEN
			v_status := 'UPDATE: ';
			UPDATE AGENTTRAINING SET
				COURSENAME=m2, COURSEDATE=m3,CARRIERID=m4,NOTES=m5
			WHERE TRAINID=m0;
		ELSE
			v_status := 'INSERT: ';
			select seq_agenttraining_trainid.nextval into v_count from dual;
			INSERT into AGENTTRAINING values (v_count, m1, m2, m3, m4, m5);
		END IF;
		COMMIT;
		RETURN v_status || to_char(m1) || ', ' || to_char(v_count);
	EXCEPTION
		WHEN OTHERS THEN
			ROLLBACK;
	END f_agent_training;

END agent_management;