[Home] [Help]
PACKAGE BODY: APPS.OKL_IN_TPP_PVT
Source
1 PACKAGE BODY OKL_IN_TPP_PVT AS
2 /* $Header: OKLRTPPB.pls 115.3 2002/12/18 12:51:41 kjinger noship $ */
3 FUNCTION agency_address( p_isu_id IN NUMBER,
4 p_agency_site_id IN NUMBER,
5 x_agency_name OUT NOCOPY VARCHAR2,
6 x_agency_addrss1 OUT NOCOPY VARCHAR2,
7 x_agency_addrss2 OUT NOCOPY VARCHAR2,
8 x_agency_addrss3 OUT NOCOPY VARCHAR2,
9 x_agency_addrss4 OUT NOCOPY VARCHAR2,
10 x_agency_city OUT NOCOPY VARCHAR2,
11 x_agency_county OUT NOCOPY VARCHAR2,
12 x_agency_province OUT NOCOPY VARCHAR2,
13 x_agency_state OUT NOCOPY VARCHAR2,
14 x_agency_postalcode OUT NOCOPY VARCHAR2,
15 x_agency_country OUT NOCOPY VARCHAR2
16 ) RETURN VARCHAR2 IS
17 cursor agency_addrss is
18 SELECT prt.PARTY_NAME,
19 prt.ADDRESS1,
20 prt.ADDRESS2,
21 prt.ADDRESS3,
22 prt.ADDRESS4,
23 prt.CITY,
24 prt.COUNTY,
25 prt.PROVINCE,
26 prt.STATE,
27 prt.POSTAL_CODE,
28 prt.COUNTRY
29 FROM HZ_PARTIES prt ,HZ_PARTY_SITES hps
30 WHERE prt.CATEGORY_CODE = 'INSURER' AND
31 prt.PARTY_ID = hps.PARTY_ID AND
32 --prt.THIRD_PARTY_FLAG ='Y' AND
33 prt.PARTY_ID = p_isu_id AND
34 hps.party_site_id = p_agency_site_id;
35 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
36 BEGIN
37 open agency_addrss;
38 fetch agency_addrss into x_agency_name,x_agency_addrss1,x_agency_addrss2,x_agency_addrss3,
39 x_agency_addrss4,x_agency_city,x_agency_county,x_agency_province,
40 x_agency_state,x_agency_postalcode,x_agency_country;
41 close agency_addrss;
42 return (l_return_status);
43 EXCEPTION
44 WHEN OTHERS THEN
45 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
46 ,p_msg_name => G_UNEXPECTED_ERROR
47 ,p_token1 => G_SQLCODE_TOKEN
48 ,p_token1_value => SQLCODE
49 ,p_token2 => G_SQLERRM_TOKEN
50 ,p_token2_value => SQLERRM);
51 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
52 return (l_return_status);
53 END agency_address;
54 FUNCTION agent_address(p_int_id IN NUMBER,
55 p_agent_site_id IN NUMBER,
56 x_agent_name OUT NOCOPY VARCHAR2,
57 x_agent_addrss1 OUT NOCOPY VARCHAR2,
58 x_agent_addrss2 OUT NOCOPY VARCHAR2,
59 x_agent_addrss3 OUT NOCOPY VARCHAR2,
60 x_agent_addrss4 OUT NOCOPY VARCHAR2,
61 x_agent_city OUT NOCOPY VARCHAR2,
62 x_agent_county OUT NOCOPY VARCHAR2,
63 x_agent_province OUT NOCOPY VARCHAR2,
64 x_agent_state OUT NOCOPY VARCHAR2,
65 x_agent_postalcode OUT NOCOPY VARCHAR2,
66 x_agent_country OUT NOCOPY VARCHAR2
67 ) RETURN VARCHAR2 IS
68 cursor agent_addrss is
69 SELECT prt.PARTY_NAME,
70 prt.ADDRESS1,
71 prt.ADDRESS2,
72 prt.ADDRESS3,
73 prt.ADDRESS4,
74 prt.CITY,
75 prt.COUNTY,
76 prt.PROVINCE,
77 prt.STATE,
78 prt.POSTAL_CODE,
79 prt.COUNTRY
80 FROM HZ_PARTIES prt ,HZ_PARTY_SITES hps
81 WHERE prt.CATEGORY_CODE = 'INSURANCE_AGENT' AND
82 prt.PARTY_ID = hps.PARTY_ID AND
83 --prt.THIRD_PARTY_FLAG ='Y' AND
84 prt.PARTY_ID = p_int_id AND
85 hps.party_site_id = p_agent_site_id;
86 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
87 BEGIN
88 open agent_addrss;
89 fetch agent_addrss into x_agent_name,x_agent_addrss1,x_agent_addrss2,x_agent_addrss3,
90 x_agent_addrss4,x_agent_city,x_agent_county,x_agent_province,
91 x_agent_state,x_agent_postalcode,x_agent_country;
92 close agent_addrss;
93 return (l_return_status);
94 EXCEPTION
95 WHEN OTHERS THEN
96 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
97 ,p_msg_name => G_UNEXPECTED_ERROR
98 ,p_token1 => G_SQLCODE_TOKEN
99 ,p_token1_value => SQLCODE
100 ,p_token2 => G_SQLERRM_TOKEN
101 ,p_token2_value => SQLERRM);
102 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
103 return (l_return_status);
104 END agent_address;
105 END OKL_IN_TPP_PVT;