1 PACKAGE csf_task_address_pvt AUTHID CURRENT_USER AS
2 /* $Header: CSFVTADS.pls 120.11.12020000.3 2013/03/14 10:25:51 srguntur ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'CSF_TASK_ADDRESS_PVT';
4 g_conc_program_name CONSTANT VARCHAR2 (30) := 'CSFFIA';
5 g_valid_true CONSTANT VARCHAR2 (1) := 'Y';
6 g_valid_false CONSTANT VARCHAR2 (1) := 'N';
7 g_debug_p CONSTANT VARCHAR2 (100)
8 := 'begin dbms_' || 'output' || '.put_line(:1); end;';
9 g_log CONSTANT NUMBER := fnd_file.LOG;
10 g_output CONSTANT NUMBER := fnd_file.output;
11 g_debug BOOLEAN;
12
13 TYPE task_rec_type IS RECORD (
14 ID csf_ext_locations.csf_ext_location_id%TYPE,
15 task_id jtf_tasks_b.task_id%TYPE,
16 task_number jtf_tasks_b.task_number%TYPE,
17 location_id hz_locations.location_id%TYPE,
18 address_style hz_locations.address_style%TYPE,
19 address1 hz_locations.address1%TYPE,
20 address2 hz_locations.address2%TYPE,
21 address3 hz_locations.address3%TYPE,
22 address4 hz_locations.address4%TYPE,
23 postal_code hz_locations.postal_code%TYPE,
24 city hz_locations.city%TYPE,
25 county hz_locations.county%TYPE,
26 state hz_locations.state%TYPE,
27 province hz_locations.province%TYPE,
28 country hz_locations.country%TYPE,
29 short_description hz_locations.short_description%TYPE,
30 description hz_locations.description%TYPE,
31 validated_flag csf_ext_locations.validated_flag%TYPE,
32 override_flag csf_ext_locations.override_flag%TYPE,
33 log_detail_short csf_ext_locations.log_detail_short%TYPE,
34 log_detail_long csf_ext_locations.log_detail_long%TYPE,
35 country_code VARCHAR2 (30),
36 timezone_id hz_locations.timezone_id%TYPE
37 );
38
39 TYPE task_rec_tbl_type IS TABLE OF task_rec_type
40 INDEX BY BINARY_INTEGER;
41
42 -- This procedure is called by the Find Invalid Address concurrent program.
43 PROCEDURE validate_address (
44 errbuf OUT NOCOPY VARCHAR2,
45 retcode OUT NOCOPY VARCHAR2,
46 p_start_date IN VARCHAR2 DEFAULT NULL,
47 p_end_date in varchar2 default null,
48 p_unstamped_only IN VARCHAR2 DEFAULT 'Yes'
49 );
50
51 -- This procedure is used to retrieve data from the hz_locations table.
52 PROCEDURE retrieve_data (
53 p_api_version IN NUMBER,
54 p_init_msg_list IN VARCHAR2,
55 p_commit IN VARCHAR2,
56 p_validation_level IN NUMBER,
57 p_start_date IN DATE DEFAULT NULL,
58 p_end_date IN DATE DEFAULT NULL,
59 x_task_rec_tbl OUT NOCOPY task_rec_tbl_type,
60 x_return_status OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER,
62 x_msg_data out nocopy varchar2,
63 p_unstamped_only IN VARCHAR2 DEFAULT 'Yes'
64 );
65
66 -- This procedure is used to get the geometry for a particular task.
67 PROCEDURE get_geometry (
68 p_api_version IN NUMBER,
69 p_init_msg_list IN VARCHAR2,
70 p_commit IN VARCHAR2,
71 p_validation_level IN NUMBER,
72 p_location_id IN hz_locations.location_id%TYPE,
73 x_locus OUT NOCOPY hz_locations.geometry%TYPE,
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_msg_count OUT NOCOPY NUMBER,
76 x_msg_data OUT NOCOPY VARCHAR2
77 );
78
79 -- Checks if the geometry is null.
80 FUNCTION is_geometry_null (p_locus IN MDSYS.SDO_GEOMETRY)
81 RETURN BOOLEAN;
82
83 -- Used to check if the geometry is valid
84 PROCEDURE is_geometry_valid (
85 p_api_version IN NUMBER,
86 p_init_msg_list IN VARCHAR2,
87 p_commit IN VARCHAR2,
88 p_validation_level IN NUMBER,
89 p_locus IN hz_locations.geometry%TYPE,
90 x_result OUT NOCOPY VARCHAR2,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2
94 );
95
96 -- Used to log the info generated by the "Find Invalid address" program
97 PROCEDURE log_info (
98 p_api_version IN NUMBER,
99 p_init_msg_list IN VARCHAR2,
100 p_commit IN VARCHAR2,
101 p_validation_level IN NUMBER,
102 p_task_rec IN task_rec_type,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2
106 );
107
108 PROCEDURE log_info_detail (
109 p_api_version IN NUMBER,
110 p_init_msg_list IN VARCHAR2,
111 p_commit IN VARCHAR2,
112 p_validation_level IN NUMBER,
113 p_task_rec IN task_rec_type,
114 x_return_status OUT NOCOPY VARCHAR2,
115 x_msg_count OUT NOCOPY NUMBER,
116 x_msg_data OUT NOCOPY VARCHAR2,
117 x_dup_msg_data IN OUT NOCOPY VARCHAR2
118 );
119
120 -- Get the error details for logging.
121 PROCEDURE get_error_detail (
122 p_api_version IN NUMBER,
123 p_init_msg_list IN VARCHAR2,
124 p_commit IN VARCHAR2,
125 p_validation_level IN NUMBER,
126 p_task_rec IN task_rec_type,
127 x_error OUT NOCOPY VARCHAR2,
128 x_error_detail OUT NOCOPY VARCHAR2,
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_msg_count OUT NOCOPY NUMBER,
131 x_msg_data OUT NOCOPY VARCHAR2
132 );
133
134 -- Used to validate the task data
135 PROCEDURE validate_task_data (
136 p_api_version IN NUMBER,
137 p_init_msg_list IN VARCHAR2,
138 p_commit IN VARCHAR2,
139 p_validation_level IN NUMBER,
140 p_invalid_task_id IN jtf_tasks_b.task_id%TYPE,
141 p_location_id IN hz_locations.location_id%TYPE,
142 p_address1 IN hz_locations.address1%TYPE,
143 p_address2 IN hz_locations.address2%TYPE,
144 p_address3 IN hz_locations.address3%TYPE,
145 p_address4 IN hz_locations.address4%TYPE,
146 p_city IN hz_locations.city%TYPE,
147 p_postal_code IN hz_locations.postal_code%TYPE,
148 p_state IN hz_locations.state%TYPE,
149 p_province IN hz_locations.province%TYPE,
150 p_county IN hz_locations.county%TYPE,
151 p_country IN hz_locations.country%TYPE,
152 p_timezone_id IN hz_locations.timezone_id%TYPE,
153 x_result OUT NOCOPY VARCHAR2,
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2
157 );
158
159 -- Checks if a task has valid address data
160 FUNCTION is_task_address_valid (p_task_id IN jtf_tasks_b.task_id%TYPE)
161 RETURN BOOLEAN;
162
163 -- Used to update the task data
164 PROCEDURE update_task_address (
165 p_api_version IN NUMBER,
166 p_init_msg_list IN VARCHAR2,
167 p_commit IN VARCHAR2,
168 p_validation_level IN NUMBER,
169 p_location_id IN hz_locations.location_id%TYPE,
170 p_address1 IN hz_locations.address1%TYPE,
171 p_address2 IN hz_locations.address2%TYPE,
172 p_address3 IN hz_locations.address3%TYPE,
173 p_address4 IN hz_locations.address4%TYPE,
174 p_city IN hz_locations.city%TYPE,
175 p_postal_code IN hz_locations.postal_code%TYPE,
176 p_state IN hz_locations.state%TYPE,
177 p_province IN hz_locations.province%TYPE,
178 p_county IN hz_locations.county%TYPE,
179 p_country IN hz_locations.country%TYPE,
180 p_validated_flag IN csf_ext_locations.validated_flag%TYPE,
181 p_override_flag IN csf_ext_locations.override_flag%TYPE,
182 p_timezone_id IN hz_locations.timezone_id%TYPE,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_msg_count OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2
186 );
187
188 -- This procedure used by Change Invalid address to get the counrty code and place id
189 PROCEDURE get_country_details(
190 p_country IN VARCHAR2,
191 place_id OUT NOCOPY VARCHAR2,
192 country_code OUT NOCOPY VARCHAR2);
193
194 -- This procedure used by Change Invalid address to get the counrty code for United Kingdom
195 procedure get_country_details_GBR(
196 p_city IN VARCHAR2,
197 p_country IN VARCHAR2,
198 place_id OUT NOCOPY VARCHAR2);
199
200 procedure odf_post_installation_steps(
201 errbuf OUT NOCOPY VARCHAR2,
202 retcode OUT NOCOPY VARCHAR2,
203 owner_name IN VARCHAR2,
204 data_set IN VARCHAR2);
205
206
207 PROCEDURE get_odf_country_details(
208 p_country IN VARCHAR2,
209 p_ctry_lang_code OUT NOCOPY VARCHAR2,
210 place_id OUT NOCOPY VARCHAR2,
211 country_code OUT NOCOPY VARCHAR2);
212
213 procedure get_odf_admin_level(
214 l_place_id IN VARCHAR2,
215 l_ctry_code IN VARCHAR2,
216 region_admin_level OUT NOCOPY NUMBER,
217 municipality_admin_level OUT NOCOPY NUMBER,
218 settlement_admin_level OUT NOCOPY NUMBER);
219
220 END csf_task_address_pvt;
[Home] [Help]