PROCEDURE DETERMINE_ADDRESS ( p_tax_payer_no IN NUMBER , p_property_no in property.property_no%type, p_tax_type_no IN NUMBER , p_pay_agree_no IN NUMBER , p_tax_payer_name OUT VARCHAR2 , p_rep_name OUT VARCHAR2 , p_mailing_address OUT VARCHAR2 , p_city_name OUT varchar2 , p_post_code_code OUT varchar2 , p_country_name OUT varchar2, p_parish_name out varchar2 ) IS -- -- . CURSOR C_AGENT_NO( p_pay_agree_no IN NUMBER ) IS SELECT agent_no FROM payment_agreement WHERE pay_agree_no = p_pay_agree_no; -- -- . CURSOR C_TAX_PAYER_NAME( p_tax_payer_no IN NUMBER ) is SELECT GET_NAME_FN(P_TAX_PAYER_NO) from DUAL; -- -- . /* Put in commentary for the moment. conv Christiane Gélinas/Carole Longpré December 10, 2003 When ready to use, do not forget to change select for city_name, country_name, etc. CURSOR C_CHK_TAXAC_REP( p_tax_payer_no IN NUMBER , p_tax_type_no IN NUMBER ) IS SELECT rep_name, mailing_address, city_no, post_code_no, country_no FROM tax_account WHERE tax_payer_no = p_tax_payer_no AND tax_type_no = p_tax_type_no; */ -- -- . CURSOR C_CHK_TAX_P_REP( p_tax_payer_no IN NUMBER ) IS SELECT rep_tax_payer_no, rep_taxr_name FROM tax_payer WHERE tax_payer_no = p_tax_payer_no; -- -- . CURSOR C_ADDRESS_TAX_PAYER( p_tax_payer_no IN NUMBER ) IS SELECT tax_p.rep_taxr_name , tax_p.mailing_address , ci.city_name , pc.post_code_code , co.country_name , pa.parish_name FROM tax_payer tax_p , city ci , parish pa , country co , postal_code pc WHERE tax_p.tax_payer_no = p_tax_payer_no AND ci.city_no(+) = tax_p.city_no and ci.par_parish_no = pa.parish_no (+) and tax_p.country_no = co.country_no (+) and tax_p.post_code_no = pc.post_code_no (+); -- -- . /* Not used at the present. conversation Christiane Gélinas/Carole Longpré December 10, 2003 CURSOR C_AGENT_ADDRESS( p_pay_agree_no IN NUMBER ) IS SELECT pa.agent_no , NVL(corp.regist_name, --ind.last_name|| ', '||ind.first_name|| ' '||ind.middle_name) No column middle_name in table for Grenada ind.last_name|| ', '||ind.first_name) , tax_p.rep_tax_payer_no , tax_p.rep_taxr_name , tax_p.mailing_address , ci.city_name , pc.post_code_desc , co.country_name , pa.parish_name FROM payment_agreement pa , tax_payer tax_p , corporation corp , individual ind , city ci , parish pa , country co , postal_code pc WHERE pay_agree_no = p_pay_agree_no AND tax_p.tax_payer_no =pa.agent_no AND corp.tax_payer_no(+) = tax_p.tax_payer_no AND ind.tax_payer_no(+) = tax_p.tax_payer_no AND ci.city_no(+) = tax_p.city_no and ci.par_parish_no = pa.parish_no (+) and tax_p.country_no = co.country_no (+) and tax_p.post_code_no = pc.post_code_no (+); */ -- -- /* Property's representant's tax_payer_no */ CURSOR C_PROP_REPRESENT IS select TAX_PAYER_NO_REPRESENTED_BY from prop_represent pr where pr.PROPERTY_NO = p_property_no and pr.fl_care_of = 'Y' and (trunc(sysdate) BETWEEN pr.START_DATE AND pr.END_DATE OR (trunc(sysdate) >= pr.START_DATE AND pr.END_DATE IS NULL)); -- -- v_agent_no PAYMENT_AGREEMENT.AGENT_NO%TYPE; -- -- v_address_flag BOOLEAN; -- -- v_rep_tax_payer_no NUMBER; -- -- v_tax_payer_no_rep_by prop_represent.tax_payer_no_represented_by%type; -- -- v_rep_taxr_name VARCHAR2(45); -- -- v_dummy_name VARCHAR2(45); BEGIN v_address_flag := FALSE; /* Determine IF document to be created IS FOR a payment agreement. IF it IS FOR a payment agreement, THEN the document should be addressed to the agent, IF one exists FOR the agreement. */ /* Not used at the present. Conversation Christiane Gélinas / Carole Longpré December 10, 2003 IF p_pay_agree_no IS NOT NULL THEN OPEN c_agent_no(p_pay_agree_no); FETCH c_agent_no INTO v_agent_no; CLOSE c_agent_no; ELSE */ v_agent_no := NULL; --END IF; /* Determine name to be printed. */ IF v_agent_no IS NOT NULL THEN /* The name printed will be the agent's. */ OPEN c_tax_payer_name(v_agent_no); FETCH c_tax_payer_name INTO p_tax_payer_name; CLOSE c_tax_payer_name; ELSE /* The name printed will be the taxpayer's. */ OPEN c_tax_payer_name(p_tax_payer_no); FETCH c_tax_payer_name INTO p_tax_payer_name; CLOSE c_tax_payer_name; END IF; /* Determine IF there are any representative's, AND which address to use */ /* First, check at the account level. This is only done when the document IS not being sent to an agent. */ /* Not used at the present. Conversation Christiane Gélinas / Carole Longpré December 10, 2003 not to forget to check parameters when put back in use IF v_agent_no IS NULL THEN OPEN c_chk_taxac_rep(p_tax_payer_no, p_tax_type_no); FETCH c_chk_taxac_rep INTO p_rep_name , p_mailing_address , p_city_no , p_post_code_no , p_country_no; CLOSE c_chk_taxac_rep; IF p_rep_name IS NOT NULL THEN v_address_flag := TRUE; END IF; END IF; */ /* Determine if there IS a representative at the taxpayer level. */ /* Determine the address accordingly. */ IF v_address_flag = FALSE THEN IF v_agent_no IS NOT NULL THEN /* Document will be mailed to the agent */ OPEN c_chk_tax_p_rep(v_agent_no); FETCH c_chk_tax_p_rep INTO v_rep_tax_payer_no , v_rep_taxr_name; CLOSE c_chk_tax_p_rep; ELSE /* Document will be mailed to the taxpayer. */ OPEN c_chk_tax_p_rep(p_tax_payer_no); FETCH c_chk_tax_p_rep INTO v_rep_tax_payer_no , v_rep_taxr_name; CLOSE c_chk_tax_p_rep; END IF; IF v_rep_tax_payer_no IS NOT NULL THEN /* Lookup the rep.'s name, then use the address information, based on the rep.'s taxpayer record. */ OPEN c_tax_payer_name(v_rep_tax_payer_no); FETCH c_tax_payer_name INTO p_rep_name; CLOSE c_tax_payer_name; OPEN c_address_tax_payer(v_rep_tax_payer_no); FETCH c_address_tax_payer INTO v_dummy_name , p_mailing_address , p_city_name , p_post_code_code , p_country_name , p_parish_name; CLOSE c_address_tax_payer; v_address_flag := TRUE; ELSE IF v_rep_taxr_name IS NOT NULL THEN /* Use the rep.'s name AND the address based on the appropiate taxpayer record. */ IF v_agent_no IS NOT NULL THEN OPEN c_address_tax_payer(v_agent_no); FETCH c_address_tax_payer INTO p_rep_name , p_mailing_address , p_city_name , p_post_code_code , p_country_name , p_parish_name; CLOSE c_address_tax_payer; p_rep_name := v_rep_taxr_name; --Have to change rep name back!!! v_address_flag := TRUE; ELSE OPEN c_address_tax_payer(p_tax_payer_no); FETCH c_address_tax_payer INTO p_rep_name , p_mailing_address , p_city_name , p_post_code_code , p_country_name , p_parish_name; CLOSE c_address_tax_payer; p_rep_name := v_rep_taxr_name; --Have to change rep name back!!! v_address_flag := TRUE; END IF; ELSE /* Use the address based on the appropiate taxpayer record. */ IF v_agent_no IS NOT NULL THEN OPEN c_address_tax_payer(v_agent_no); FETCH c_address_tax_payer INTO p_rep_name , p_mailing_address , p_city_name , p_post_code_code , p_country_name , p_parish_name; CLOSE c_address_tax_payer; p_rep_name := NULL; v_address_flag := TRUE; ELSE IF p_property_no is not null THEN open c_prop_represent; fetch c_prop_represent into v_tax_payer_no_rep_by; close c_prop_represent; end IF; IF v_tax_payer_no_rep_by is not null THEN /* Lookup the property rep.'s name, then use the address information, based on the property rep.'s taxpayer record. */ OPEN c_tax_payer_name(v_tax_payer_no_rep_by); FETCH c_tax_payer_name INTO p_rep_name; CLOSE c_tax_payer_name; OPEN c_address_tax_payer(v_tax_payer_no_rep_by); FETCH c_address_tax_payer INTO v_dummy_name , p_mailing_address , p_city_name , p_post_code_code , p_country_name , p_parish_name; CLOSE c_address_tax_payer; ELSE OPEN c_address_tax_payer(p_tax_payer_no); FETCH c_address_tax_payer INTO p_rep_name , p_mailing_address , p_city_name , p_post_code_code , p_country_name , p_parish_name; CLOSE c_address_tax_payer; p_rep_name := NULL; v_address_flag := TRUE; end IF; END IF; END IF; END IF; END IF; END DETERMINE_ADDRESS;