1 PACKAGE csf_resource_address_pvt AS
2 /* $Header: CSFVADRS.pls 120.4.12010000.3 2008/09/23 10:30:57 rajukum ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_RESOURCE_ADDRESS_PVT';
5
6 g_st_party_fname CONSTANT VARCHAR2(30) := 'Dep/Arr Party';
7
8 /**
9 * Address Record Structure to store the Address Information when retrieved
10 * from HZ_LOCATIONS / PER_ADDRESSES tables.
11 *
12 * *** Filled when address is retrieved from HZ_LOCATIONS Table ***
13 * *** Associated API that fills the records is GET_PARTY_ADDRESSES ***
14 * PARTY_ID Primary Key to HZ_PARTIES Table
15 * PARTY_SITE_ID Primary Key to HZ_PARTY_SITES Table
16 * LOCATION Primary Key to HZ_LOCATIONS Table
17 * GEOMETRY Spatial Geometry of the Address
18
19 * *** Filled when address is retrieved from PER_ADDRESSES Table ***
20 * *** Associated API that fills the records is GET_HOME_ADDRESSES ***
21 * ADDRESS_ID Primray Key to PER_ADDRESS Table
22 *
23 * *** Common fields between HZ_LOCATIONS and PER_ADDRESSES Tables ***
24 * STREET Address Line 1 Information
25 * CITY City Information
26 * STATE State Information
27 * POSTAL_CODE Postal Code Information
28 * COUNTRY Country Information
29 * TERRITORY_SHORT_NAME Stores Country Code (FND_TERRITORIES_TL)
30 * START_DATE_ACTIVE Date from which the Party Site is active
31 * END_DATE_ACTIVE Date till which the Party Site is active
32 */
33 TYPE address_rec_type IS RECORD(
34 party_id hz_parties.party_id%TYPE
35 , party_site_id hz_party_sites.party_site_id%TYPE
36 , location_id hz_locations.location_id%TYPE
37 , address_id per_addresses.address_id%TYPE
38 , street hz_locations.address1%TYPE
39 , postal_code hz_locations.postal_code%TYPE
40 , city hz_locations.city%TYPE
41 , state hz_locations.state%TYPE
42 , country hz_locations.country%TYPE
43 , territory_short_name fnd_territories_vl.territory_short_name%TYPE
44 , geometry mdsys.sdo_geometry
45 , start_date_active DATE
46 , end_date_active DATE
47 );
48
49 /**
50 * Table of Addresses where each record is of type ADDRESS_REC_TYPE.
51 */
52 TYPE address_tbl_type IS TABLE OF address_rec_type;
53
54 /**
55 * Gets the Resource's Party Information and if the Resource is not
56 * tied to any Party, then creates a new set of Party, Site and Location
57 * Information.
58 *
59 * <b> Resource's Home Address </b>
60 * This is applicable only for Employee Resource (RS_EMPLOYEE).
61 * Employee Resource is an employee defined in HRMS with an address.
62 * Resource's Home Address is nothing but the address as defined in HRMS
63 * PER_ADDRESSES. There can be only one Primary Address for a particular
64 * period of time.
65 * <br>
66 * <b> Resource's Party Information </b>
67 * Any Party created by this API will have CREATED_BY_MODULE as CSFDEAR.
68 *
69 * RS_EMPLOYEE
70 * Party is already created by HRMS whenever an Employee is created.
71 * This API will take care of inserting Party Sites and copying
72 * the address in PER_ADDRESS in HRMS Module to HZ_LOCATIONS in TCA.
73 * Before, party 'Dep/Arr Party' were created even for Employee
74 * Resources. Now this API will reuse the Party Created by HRMS itself.
75 *
76 * RS_PARTY
77 * Party Resources are those which are created in TCA itself by making
78 * use of Contact Person Functionality. Nothing is done as everything
79 * is already in-order.
80 *
81 * Other Resources - RS_TEAM, RS_GROUP, Other Resource Types
82 * For all other resources, there exists no home address and no automatic
83 * Party Also. For these resources, a Party is created for the first time
84 * with the first name being 'Dep/Arr Party' and Last Name being the
85 * concantenation of Resource Type and Resource ID. A Dummy Address is
86 * inserted for its location.
87 * <br>
88 * <b> Sync between HRMS Address and CRM Location </b>
89 * Whenever the HRMS Address of the Resource is updated, the corresponding
90 * Location Record is updated to reflect the new changes.
91 * Whenever a HRMS Address is created by end-dating the existing HRMS Address,
92 * a new location is created and this location is returned to programs
93 * requesting the address of the Resource.
94 * <br>
95 * <b> Automatic Geocoding of the Address <b>
96 * If the Address corresponding to the determined Location is not yet Geocoded
97 * (GEOMETRY being NULL in HZ_LOCATIONS), the Address is Geocoded and stored
98 * in the table.
99 * <br>
100 *
101 * @param p_api_version API Version (1.0)
102 * @param p_init_msg_list Initialize Message List
103 * @param p_commit Commits the Work
104 * @param x_return_status Return Status of the Procedure.
105 * @param x_msg_count Number of Messages in the Stack.
106 * @param x_msg_data Stack of Error Messages.
107 * @param p_resource_id Resource Identifier
108 * @param p_resource_type Resource Type of the above Resource
109 * @param p_date Date for which the Address is desired
110 * @param x_address_rec Resource Complete Party Address Information
111 */
112 PROCEDURE get_resource_address(
113 p_api_version IN NUMBER
114 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
115 , p_commit IN VARCHAR2 DEFAULT NULL
116 , x_return_status OUT NOCOPY VARCHAR2
117 , x_msg_count OUT NOCOPY NUMBER
118 , x_msg_data OUT NOCOPY VARCHAR2
119 , p_resource_id IN NUMBER
120 , p_resource_type IN VARCHAR2
121 , p_date IN DATE
122 , x_address_rec OUT NOCOPY address_rec_type
123 );
124
125 /**
126 * This API is similar to that of GET_RESOURCE_ADDRESS but restricts
127 * itself in providing only the Party ID, Party Site ID and Location ID.
128 *
129 * @param p_api_version API Version (1.0)
130 * @param p_init_msg_list Initialize Message List
131 * @param p_commit Commits the Work
132 * @param x_return_status Return Status of the Procedure.
133 * @param x_msg_count Number of Messages in the Stack.
134 * @param x_msg_data Stack of Error Messages.
135 * @param p_resource_id Resource Identifier
136 * @param p_resource_type Resource Type of the above Resource
137 * @param p_date Date for which the Address is desired
138 * @param x_party_id Party Identifier of the Party tied to Resource
139 * @param x_party_id_site Party Site ID of the Active Party Site
140 * @param x_location_id Location ID of the Active Location
141 */
142 PROCEDURE get_resource_party_info(
143 p_api_version IN NUMBER
144 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
145 , p_commit IN VARCHAR2 DEFAULT NULL
146 , x_return_status OUT NOCOPY VARCHAR2
147 , x_msg_count OUT NOCOPY NUMBER
148 , x_msg_data OUT NOCOPY VARCHAR2
149 , p_resource_id IN NUMBER
150 , p_resource_type IN VARCHAR2
151 , p_date IN DATE
152 , x_party_id OUT NOCOPY NUMBER
153 , x_party_site_id OUT NOCOPY NUMBER
154 , x_location_id OUT NOCOPY NUMBER
155 );
156
157 /**
158 * Resolves the passed address and updates the corresponding address in
159 * HZ_LOCATIONS with the Geometry of the Address.
160 *
161 * The given address is resolved using Location Finder only when the
162 * profiles "CSR: Create Location" and "CSF: Location Finder Installed"
163 * are set to Yes. The geometry corresponding to the Location Specified
164 * is updated if the Geocoding was successful.
165 *
166 * The given address is updated corresponding to the Location Specified
167 * when the parameter P_UPDATE_ADDRESS is set to Yes.
168 *
169 * A single Address Line out of the four address lines is used depending
170 * on which Line contains a valid Street Info with Building Number.
171 * Preference is given to Address Line 4 to Address Line 1.
172 *
173 * @param p_api_version API Version (1.0)
174 * @param p_init_msg_list Initialize Message List
175 * @param p_commit Commits the work
176 * @param x_return_status Return Status of the Procedure.
177 * @param x_msg_count Number of Messages in the Stack.
178 * @param x_msg_data Stack of Error Messages.
179 * @param p_location_id Location ID of the Location to be updated.
180 * @param p_building_num Building Number of the address (Optional)
181 * @param p_address1 Address Line 1
182 * @param p_address2 Address Line 2 (Optional)
183 * @param p_address3 Address Line 3 (Optional)
184 * @param p_address4 Address Line 4 (Optional)
185 * @param p_city City
186 * @param p_state State
187 * @param p_postalcode Zip Code
188 * @param p_county County (Optional)
189 * @param p_province Province (Optional)
190 * @param p_country Country (United States)
191 * @param p_country_code Country Code (US)
192 * @param p_alternate NOT USED
193 * @param p_update_address Should the Location be updated with Address.
194 * @param x_geometry Geometry of the Address resolved.
195 */
196 PROCEDURE resolve_address(
197 p_api_version IN NUMBER
198 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
199 , p_commit IN VARCHAR2 DEFAULT NULL
200 , x_return_status OUT NOCOPY VARCHAR2
201 , x_msg_count OUT NOCOPY NUMBER
202 , x_msg_data OUT NOCOPY VARCHAR2
203 , p_location_id IN NUMBER
204 , p_building_num IN VARCHAR2 DEFAULT NULL
205 , p_address1 IN VARCHAR2
206 , p_address2 IN VARCHAR2 DEFAULT NULL
207 , p_address3 IN VARCHAR2 DEFAULT NULL
208 , p_address4 IN VARCHAR2 DEFAULT NULL
209 , p_city IN VARCHAR2
210 , p_state IN VARCHAR2
211 , p_postalcode IN VARCHAR2
212 , p_county IN VARCHAR2 DEFAULT NULL
213 , p_province IN VARCHAR2 DEFAULT NULL
214 , p_country IN VARCHAR2
215 , p_country_code IN VARCHAR2
216 , p_alternate IN VARCHAR2 DEFAULT NULL
217 , p_update_address IN VARCHAR2 DEFAULT NULL
218 , x_geometry OUT NOCOPY mdsys.sdo_geometry
219 );
220
221 /**
222 * Returns which address line to be considered among the four
223 * address lines.
224 *
225 * @param p_address1 Address Line 1
226 * @param p_address2 Address Line 2 (Optional)
227 * @param p_address3 Address Line 3 (Optional)
228 * @param p_address4 Address Line 4 (Optional)
229 * @param p_country_code Country Code (For Eg. US)
230 */
231 FUNCTION choose_address_line(
232 p_address1 IN VARCHAR2
233 , p_address2 IN VARCHAR2 DEFAULT NULL
234 , p_address3 IN VARCHAR2 DEFAULT NULL
235 , p_address4 IN VARCHAR2 DEFAULT NULL
236 , p_country_code IN VARCHAR2
237 ) RETURN VARCHAR2;
238
239 END csf_resource_address_pvt;
[Home] [Help]