1 PACKAGE csf_task_address_pvt AS
2 /* $Header: CSFVTADS.pls 120.5.12010000.6 2008/09/26 09:59:13 vpalle 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 );
49
50 -- This procedure is used to retrieve data from the hz_locations table.
51 PROCEDURE retrieve_data (
52 p_api_version IN NUMBER,
53 p_init_msg_list IN VARCHAR2,
54 p_commit IN VARCHAR2,
55 p_validation_level IN NUMBER,
56 p_start_date IN DATE DEFAULT NULL,
57 p_end_date IN DATE DEFAULT NULL,
58 x_task_rec_tbl OUT NOCOPY task_rec_tbl_type,
59 x_return_status OUT NOCOPY VARCHAR2,
60 x_msg_count OUT NOCOPY NUMBER,
61 x_msg_data OUT NOCOPY VARCHAR2
62 );
63
64 -- This procedure is used to get the geometry for a particular task.
65 PROCEDURE get_geometry (
66 p_api_version IN NUMBER,
67 p_init_msg_list IN VARCHAR2,
68 p_commit IN VARCHAR2,
69 p_validation_level IN NUMBER,
70 p_location_id IN hz_locations.location_id%TYPE,
71 x_locus OUT NOCOPY hz_locations.geometry%TYPE,
72 x_return_status OUT NOCOPY VARCHAR2,
73 x_msg_count OUT NOCOPY NUMBER,
74 x_msg_data OUT NOCOPY VARCHAR2
75 );
76
77 -- Checks if the geometry is null.
78 FUNCTION is_geometry_null (p_locus IN MDSYS.SDO_GEOMETRY)
79 RETURN BOOLEAN;
80
81 -- Used to check if the geometry is valid
82 PROCEDURE is_geometry_valid (
83 p_api_version IN NUMBER,
84 p_init_msg_list IN VARCHAR2,
85 p_commit IN VARCHAR2,
86 p_validation_level IN NUMBER,
87 p_locus IN hz_locations.geometry%TYPE,
88 x_result OUT NOCOPY VARCHAR2,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_msg_count OUT NOCOPY NUMBER,
91 x_msg_data OUT NOCOPY VARCHAR2
92 );
93
94 -- Used to log the info generated by the "Find Invalid address" program
95 PROCEDURE log_info (
96 p_api_version IN NUMBER,
97 p_init_msg_list IN VARCHAR2,
98 p_commit IN VARCHAR2,
99 p_validation_level IN NUMBER,
100 p_task_rec IN task_rec_type,
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_msg_count OUT NOCOPY NUMBER,
103 x_msg_data OUT NOCOPY VARCHAR2
104 );
105
106 -- Get the error details for logging.
107 PROCEDURE get_error_detail (
108 p_api_version IN NUMBER,
109 p_init_msg_list IN VARCHAR2,
110 p_commit IN VARCHAR2,
111 p_validation_level IN NUMBER,
112 p_task_rec IN task_rec_type,
113 x_error OUT NOCOPY VARCHAR2,
114 x_error_detail OUT NOCOPY VARCHAR2,
115 x_return_status OUT NOCOPY VARCHAR2,
116 x_msg_count OUT NOCOPY NUMBER,
117 x_msg_data OUT NOCOPY VARCHAR2
118 );
119
120 -- Used to validate the task data
121 PROCEDURE validate_task_data (
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_invalid_task_id IN jtf_tasks_b.task_id%TYPE,
127 p_location_id IN hz_locations.location_id%TYPE,
128 p_address1 IN hz_locations.address1%TYPE,
129 p_address2 IN hz_locations.address2%TYPE,
130 p_address3 IN hz_locations.address3%TYPE,
131 p_address4 IN hz_locations.address4%TYPE,
132 p_city IN hz_locations.city%TYPE,
133 p_postal_code IN hz_locations.postal_code%TYPE,
134 p_state IN hz_locations.state%TYPE,
135 p_province IN hz_locations.province%TYPE,
136 p_county IN hz_locations.county%TYPE,
137 p_country IN hz_locations.country%TYPE,
138 p_timezone_id IN hz_locations.timezone_id%TYPE,
139 x_result OUT NOCOPY VARCHAR2,
140 x_return_status OUT NOCOPY VARCHAR2,
141 x_msg_count OUT NOCOPY NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2
143 );
144
145 -- Checks if a task has valid address data
146 FUNCTION is_task_address_valid (p_task_id IN jtf_tasks_b.task_id%TYPE)
147 RETURN BOOLEAN;
148
149 -- Used to update the task data
150 PROCEDURE update_task_address (
151 p_api_version IN NUMBER,
152 p_init_msg_list IN VARCHAR2,
153 p_commit IN VARCHAR2,
154 p_validation_level IN NUMBER,
155 p_location_id IN hz_locations.location_id%TYPE,
156 p_address1 IN hz_locations.address1%TYPE,
157 p_address2 IN hz_locations.address2%TYPE,
158 p_address3 IN hz_locations.address3%TYPE,
159 p_address4 IN hz_locations.address4%TYPE,
160 p_city IN hz_locations.city%TYPE,
161 p_postal_code IN hz_locations.postal_code%TYPE,
162 p_state IN hz_locations.state%TYPE,
163 p_province IN hz_locations.province%TYPE,
164 p_county IN hz_locations.county%TYPE,
165 p_country IN hz_locations.country%TYPE,
166 p_validated_flag IN csf_ext_locations.validated_flag%TYPE,
167 p_override_flag IN csf_ext_locations.override_flag%TYPE,
168 p_timezone_id IN hz_locations.timezone_id%TYPE,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_count OUT NOCOPY NUMBER,
171 x_msg_data OUT NOCOPY VARCHAR2
172 );
173
174 -- This procedure used by Change Invalid address to get the counrty code and place id
175 PROCEDURE get_country_details(
176 p_country IN VARCHAR2,
177 place_id OUT NOCOPY VARCHAR2,
178 country_code OUT NOCOPY VARCHAR2);
179
180 -- This procedure used by Change Invalid address to get the counrty code for United Kingdom
181 procedure get_country_details_GBR(
182 p_city IN VARCHAR2,
183 place_id OUT NOCOPY VARCHAR2);
184
185 END csf_task_address_pvt;
[Home] [Help]