[Home] [Help]
PACKAGE BODY: APPS.EDW_GEOGRAPHY_PKG
Source
1 PACKAGE BODY EDW_GEOGRAPHY_PKG AS
2 /* $Header: poafkge.pkb 120.1 2005/06/13 12:37:19 sriswami noship $ */
3
4 Function HR_Location_fk
5 (p_location_id in NUMBER,
6 p_instance_code in VARCHAR2 :=NULL) return VARCHAR2 IS
7
8 l_location VARCHAR2(240) := 'NA_EDW';
9 l_instance VARCHAR2(30) := NULL;
10
11 BEGIN
12 IF(p_location_id is NULL) then
13 return 'NA_EDW';
14 END IF;
15
16 IF (p_instance_code is NOT NULL) then
17 l_instance := p_instance_code;
18 ELSE
19 select instance_code into l_instance
20 from edw_local_instance;
21 END IF;
22
23 l_location := p_location_id || '-' || l_instance
24 || '-' || 'HR_LOC';
25
26 return l_location;
27
28 EXCEPTION
29 when others then
30
31 return 'NA_EDW';
32
33 END HR_Location_fk;
34
35
36 Function HZ_Postcode_City_fk
37 (p_location_id in NUMBER) return VARCHAR2 IS
38
39 l_postcode_city VARCHAR2(240) := 'NA_EDW';
40 l_city VARCHAR2(60);
41 l_postal_code VARCHAR2(60);
42 l_state VARCHAR2(60);
43 l_country VARCHAR2(60);
44
45 BEGIN
46 IF(p_location_id is NULL) then
47 return 'NA_EDW';
48 END IF;
49
50 select city, postal_code, decode(state, null, province, state), country
51 into l_city, l_postal_code, l_state, l_country
52 from hz_locations
53 where location_id = p_location_id;
54
55 l_postcode_city := l_city || '-' || l_postal_code || '-' ||
56 l_state || '-' || l_country;
57
58 return l_postcode_city;
59
60 EXCEPTION
61 when others then
62
63 return 'NA_EDW';
64
65 END HZ_Postcode_City_fk;
66
67 /* This API returns the PK in Party Site for this customer site use id
68 by joining to HZ_CUST_ACCT_SITES_ALL to get party_site_id */
69
70 Function Customer_Site_Location_fk
71 (p_site_use_id in NUMBER,
72 p_instance_code in VARCHAR2 :=NULL) return VARCHAR2 IS
73
74 l_location VARCHAR2(240) := 'NA_EDW';
75 l_instance VARCHAR2(30) := NULL;
76 l_party_site_id NUMBER := NULL;
77
78 BEGIN
79
80 IF(p_site_use_id is NULL) then
81 return 'NA_EDW';
82 END IF;
83
84 IF (p_instance_code is NOT NULL) then
85
86 l_instance := p_instance_code;
87
88 ELSE
89
90 select instance_code into l_instance
91 from edw_local_instance;
92
93 END IF;
94
95 select party_site_id into l_party_site_id
96 from HZ_CUST_ACCT_SITES_ALL hcas,
97 HZ_CUST_SITE_USES_ALL hcsu
98 where hcsu.site_use_id = p_site_use_id
99 and hcsu.cust_acct_site_id = hcas.cust_acct_site_id;
100
101
102 l_location := l_party_site_id || '-' || l_instance
103 || '-' || 'PARTY_SITE';
104
105 return l_location;
106
107
108 EXCEPTION
109 when others then
110
111 return 'NA_EDW';
112
113 END Customer_Site_Location_fk;
114
115
116
117 Function Supplier_Site_Location_fk
118 (p_vendor_site_id in NUMBER,
119 p_org_id in NUMBER,
120 p_instance_code in VARCHAR2 :=NULL) return VARCHAR2 IS
121
122 l_location VARCHAR2(240) := 'NA_EDW';
123 l_instance VARCHAR2(30) := NULL;
124
125 BEGIN
126
127 IF(p_vendor_site_id is NULL) then
128 return 'NA_EDW';
129 END IF;
130
131 IF (p_instance_code is NOT NULL) then
132 l_instance := p_instance_code;
133 ELSE
134 select instance_code into l_instance
135 from edw_local_instance;
136 END IF;
137
138 l_location := p_vendor_site_id || '-' || p_org_id
139 || '-' || l_instance
140 || '-' || 'SUPPLIER_SITE';
141
142 return l_location;
143
144 EXCEPTION
145 when others then
146
147 return 'NA_EDW';
148
149 END Supplier_Site_Location_fk;
150
151
152 /* For 11.5, new customer model, used by CRM */
153 Function Party_Site_Location_fk
154 (p_party_site_id in NUMBER,
155 p_instance_code in VARCHAR2 :=NULL) return VARCHAR2 IS
156
157 l_location VARCHAR2(240) := 'NA_EDW';
158 l_instance VARCHAR2(30) := NULL;
159
160 BEGIN
161
162 IF(p_party_site_id is NULL) then
163 return 'NA_EDW';
164 END IF;
165
166 IF (p_instance_code is NOT NULL) then
167 l_instance := p_instance_code;
168 ELSE
169 select instance_code into l_instance
170 from edw_local_instance;
171 END IF;
172
173 l_location := p_party_site_id || '-' || l_instance
174 || '-' || 'PARTY_SITE';
175
176 return l_location;
177
178 EXCEPTION
179 when others then
180
181 return 'NA_EDW';
182
183 END Party_Site_Location_fk;
184
185
186 END; --package body