[Home] [Help]
PACKAGE BODY: APPS.HXC_HTR_UPLOAD_PKG
Source
1 PACKAGE BODY hxc_htr_upload_pkg AS
2 /* $Header: hxchtrupl.pkb 120.0 2005/05/29 05:43:55 appldev noship $ */
3 PROCEDURE load_time_recipient_row (
4 p_name IN VARCHAR2
5 , p_owner IN VARCHAR2
6 , p_application_name IN VARCHAR2
7 , p_custom_mode IN VARCHAR2
8 , p_appl_retrieval_function IN VARCHAR2
9 , p_appl_update_process IN VARCHAR2
10 , p_appl_validation_process IN VARCHAR2
11 , p_appl_period_function IN VARCHAR2
12 , p_appl_dyn_template_process IN VARCHAR2
13 , p_extension_function1 IN VARCHAR2
14 , p_extension_function2 IN VARCHAR2
15 , p_last_update_date IN VARCHAR2 DEFAULT NULL) IS
16
17 l_time_recipient_id hxc_time_recipients.time_recipient_id%TYPE;
18 l_application_id hxc_time_recipients.application_id%TYPE;
19 l_ovn hxc_time_recipients.object_version_number%TYPE;
20 l_owner VARCHAR2(6);
21
22 l_appl_retrieval_function hxc_time_recipients.application_retrieval_function%TYPE := NULL;
23 l_appl_update_process hxc_time_recipients.application_update_process%TYPE := NULL;
24 l_appl_validation_process hxc_time_recipients.appl_validation_process%TYPE := NULL;
25 l_appl_period_function hxc_time_recipients.application_period_function%TYPE := NULL;
26 l_appl_dyn_template_process hxc_time_recipients.appl_dynamic_template_process%TYPE := NULL;
27 l_extension_function1 hxc_time_recipients.extension_function1%TYPE := NULL;
28 l_extension_function2 hxc_time_recipients.extension_function2%TYPE := NULL;
29 l_last_update_date_db hxc_time_recipients.last_update_date%TYPE;
30 l_last_updated_by_db hxc_time_recipients.last_updated_by%TYPE;
31 l_last_updated_by_f hxc_time_recipients.last_updated_by%TYPE;
32 l_last_update_date_f hxc_time_recipients.last_update_date%TYPE;
33 --
34
35 FUNCTION chk_schema_exists RETURN BOOLEAN IS
36
37 l_exists BOOLEAN := FALSE;
38 l_dummy NUMBER(1);
39
40 CURSOR csr_chk_tab (p_hxc_schema in varchar2) IS
41 SELECT 1
42 FROM dual
43 WHERE EXISTS (
44 SELECT 1
45 FROM all_tables
46 WHERE owner = p_hxc_schema
47 AND table_name = 'HXC_TIME_RECIPIENTS' );
48
49 l_result boolean;
50 l_prod_status varchar2(1);
51 l_industry varchar2(1);
52 l_hxc_schema varchar2(30);
53
54
55 BEGIN
56
57 -- get hxc schema name
58 l_result := fnd_installation.get_app_info ('HXC', l_prod_status, l_industry, l_hxc_schema );
59
60
61 OPEN csr_chk_tab(l_hxc_schema);
62 FETCH csr_chk_tab INTO l_dummy;
63
64 IF csr_chk_tab%FOUND
65 THEN
66 l_exists := TRUE;
67 END IF;
68
69 CLOSE csr_chk_tab;
70
71 RETURN l_exists;
72
73 END chk_Schema_exists;
74
75
76 FUNCTION chk_process_exists ( p_process_name VARCHAR2 ) RETURN BOOLEAN IS
77
78 l_exists BOOLEAN := FALSE;
79 l_dummy NUMBER(1);
80 l_process VARCHAR2(30);
81
82 CURSOR csr_chk_process ( p_process VARCHAR2 ) IS
83 SELECT 1
84 FROM dual
85 WHERE EXISTS (
86 SELECT 1
87 FROM user_objects
88 WHERE object_name = p_process
89 AND object_type = 'PACKAGE BODY'
90 );
91
92 BEGIN
93
94 l_process := SUBSTR(p_process_name, 1, (INSTR(p_process_name, '.', 1, 1)-1));
95
96 OPEN csr_chk_process ( l_process );
97 FETCH csr_chk_process INTO l_dummy;
98
99 IF csr_chk_process%FOUND
100 THEN
101 l_exists := TRUE;
102 END IF;
103
104 CLOSE csr_chk_process;
105
106 RETURN l_exists;
107
108 END chk_process_exists;
109
110 FUNCTION get_application_id ( p_application_name VARCHAR2 ) RETURN NUMBER IS
111
112 l_application_id hxc_time_recipients.application_id%TYPE;
113
114 CURSOR csr_get_app_id IS
115 SELECT app.application_id
116 FROM fnd_application app
117 WHERE app.application_short_name = p_application_name;
118
119 BEGIN
120
121 OPEN csr_get_app_id;
122 FETCH csr_get_app_id INTO l_application_id;
123 CLOSE csr_get_app_id;
124
125 RETURN l_application_id;
126
127 END get_application_id;
128
129
130 BEGIN -- load_time_recipient_row
131
132 IF ( chk_schema_exists )
133 THEN
134
135 l_application_id := get_application_id ( p_application_name );
136
137 -- make sure that the validation processes exist
138
139 IF ( ( p_appl_retrieval_function IS NOT NULL ) AND ( INSTR(p_appl_retrieval_function,'.',1,1) <> 0 ))
140 THEN
141 IF ( chk_process_exists ( p_appl_retrieval_function ) )
142 THEN
143 l_appl_retrieval_function := p_appl_retrieval_function;
144 END IF;
145
146 ELSIF ( p_appl_retrieval_function IS NOT NULL )
147 THEN
148
149 l_appl_retrieval_function := p_appl_retrieval_function;
150
151 END IF;
152
153 IF ( p_appl_update_process IS NOT NULL )
154 THEN
155 IF ( chk_process_exists ( p_appl_update_process ) )
156 THEN
157 l_appl_update_process := p_appl_update_process;
158 END IF;
159 END IF;
160
161 IF ( p_appl_validation_process IS NOT NULL )
162 THEN
163 IF ( chk_process_exists ( p_appl_validation_process ) )
164 THEN
165 l_appl_validation_process := p_appl_validation_process;
166 END IF;
167 END IF;
168
169 IF ( p_appl_period_function IS NOT NULL )
170 THEN
171 IF ( chk_process_exists ( p_appl_period_function ) )
172 THEN
173 l_appl_period_function := p_appl_period_function;
174 END IF;
175 END IF;
176
177 IF ( p_extension_function1 IS NOT NULL )
178 THEN
179 IF ( chk_process_exists ( p_extension_function1 ) )
180 THEN
181 l_extension_function1 := p_extension_function1;
182 END IF;
183 END IF;
184
185 IF ( p_extension_function2 IS NOT NULL )
186 THEN
187 IF ( chk_process_exists ( p_extension_function2 ) )
188 THEN
189 l_extension_function2 := p_extension_function2;
190 END IF;
191 END IF;
192
193 IF ( p_appl_dyn_template_process IS NOT NULL )
194 THEN
195 IF ( chk_process_exists ( p_appl_dyn_template_process ) )
196 THEN
197 l_appl_dyn_template_process := p_appl_dyn_template_process;
198 END IF;
199 END IF;
200
201 l_last_updated_by_f := fnd_load_util.owner_id(p_owner);
202 l_last_update_date_f := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
203 SELECT tr.time_recipient_id
204 , tr.object_version_number
205 ,tr.last_update_date
206 ,tr.last_updated_by
207 INTO l_time_recipient_id
208 , l_ovn
209 ,l_last_update_date_db
210 ,l_last_updated_by_db
211 FROM hxc_time_recipients tr
212 WHERE tr.name = P_NAME;
213
214 IF (fnd_load_util.upload_test( l_last_updated_by_f,
215 l_last_update_date_f,
216 l_last_updated_by_db,
217 l_last_update_date_db ,
218 p_custom_mode))
219 THEN
220 hxc_time_recipient_api.update_time_recipient (
221 p_time_recipient_id => l_time_recipient_id
222 , p_application_id => l_application_id
223 , p_object_version_number => l_ovn
224 , p_name => p_name
225 , p_appl_retrieval_function => l_appl_retrieval_function
226 , p_appl_update_process => l_appl_update_process
227 , p_appl_validation_process => l_appl_validation_process
228 , p_appl_period_function => l_appl_period_function
229 , p_appl_dyn_template_process => l_appl_dyn_template_process
230 , p_extension_function1 => l_extension_function1
231 , p_extension_function2 => l_extension_function2);
232
233 END IF;
234
235 END IF; -- chk_Schema_Exists
236
237
238 EXCEPTION WHEN NO_DATA_FOUND
239 THEN
240
241 hxc_time_recipient_api.create_time_recipient (
242 p_time_recipient_id => l_time_recipient_id
243 , p_application_id => l_application_id
244 , p_object_version_number => l_ovn
245 , p_name => p_name
246 , p_appl_retrieval_function => l_appl_retrieval_function
247 , p_appl_update_process => l_appl_update_process
248 , p_appl_validation_process => l_appl_validation_process
249 , p_appl_period_function => l_appl_period_function
250 , p_appl_dyn_template_process => l_appl_dyn_template_process
251 , p_extension_function1 => l_extension_function1
252 , p_extension_function2 => l_extension_function2);
253
254 END load_time_recipient_row;
255
256
257 END hxc_htr_upload_pkg;