[Home] [Help]
PACKAGE BODY: APPS.RLM_INBOUND_SV
Source
1 PACKAGE BODY RLM_INBOUND_SV as
2 /* $Header: RLMEDINB.pls 120.1 2005/07/17 18:34:03 rlanka noship $*/
3 /*=========================RLM_INBOUND_SV ===========================*/
4
5
6 /*===========================================================================
7
8 PROCEDURE NAME: PROCESS_INBOUND
9
10 ===========================================================================*/
11 -- MOAC : Added p_org_id parameter.
12
13 PROCEDURE PROCESS_INBOUND
14 (
15 errbuf OUT NOCOPY VARCHAR2,
16 retcode OUT NOCOPY VARCHAR2,
17 p_org_id IN number,
18 p_file_path IN VARCHAR2,
19 p_file_name IN VARCHAR2,
20 p_transaction_type IN VARCHAR2,
21 p_map_id IN NUMBER,
22 p_debug_mode IN NUMBER,
23 p_run_import IN VARCHAR2,
24 p_enable_warn IN varchar2,
25 p_warn_replace_schedule IN VARCHAR2,
26 p_child_processes IN NUMBER DEFAULT 0,
27 p_data_file_char_set IN VARCHAR2
28 )
29 IS
30 --
31 i_submit_id NUMBER:=0;
32 i_run_id NUMBER;
33 i_header_id NUMBER;
34 i_count NUMBER:=0;
35 v_submit NUMBER:=0;
36 i_map_type VARCHAR2(40);
37 v_warn_replace VARCHAR2(1) := 'N';
38
39 cEnabled VARCHAR2(1):= 'Y';
40 ece_transaction_disabled EXCEPTION;
41
42 /* CURSOR c_header_cur IS
43 SELECT header_id
44 FROM rlm_interface_headers
45 WHERE request_id = i_run_id;
46 */
47
48 --
49 BEGIN
50 --
51 ec_debug.enable_debug(p_debug_mode);
52 ec_debug.pl(0,'EC','ECE_START_INBOUND','TRANSACTION_TYPE',p_transaction_type);
53 ec_debug.push('RLM_INBOUND_SV.PROCESS_INBOUND');
54 ec_debug.pl(3,'p_file_path',p_file_path);
55 ec_debug.pl(3,'p_file_name',p_file_name);
56 ec_debug.pl(3,'p_run_import',p_run_import);
57 ec_debug.pl(3,'p_map_id',p_map_id);
58 ec_debug.pl(3,'p_debug_mode',p_debug_mode);
59 ec_debug.pl(3,'p_transaction_type',p_transaction_type);
60 ec_debug.pl(3,'p_data_file_char_set',p_data_file_char_set);
61
62 /* Check to see if the transaction is enabled. If not, abort */
63 fnd_profile.get('ECE_' || p_transaction_type || '_ENABLED',cEnabled);
64 --
65 IF cEnabled = 'N' THEN
66 RAISE ece_transaction_disabled;
67 END IF;
68 --
69 ec_debug.pl(0,'EC','ECE_BEGIN_STAGING','TRANSACTION_TYPE',
70 p_transaction_type);
71 ec_inbound_stage.g_source_charset := p_data_file_char_set;
72
73 -- MOAC changes
74 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => p_org_id);
75
76 --
77 SELECT map_type
78 INTO i_map_type
79 FROM ece_mappings
80 WHERE map_id = p_map_id
81 AND enabled ='Y';
82 --
83 IF i_map_type = 'XML' THEN
84 --
85 ec_xml_utils.ec_xml_processor_in_generic
86 (
87 p_map_id,
88 i_run_id,
89 p_file_path,
90 p_file_name
91 );
92 --
93 ELSE
94 --
95 ec_inbound_stage.load_data
96 (
97 p_transaction_type,
98 p_file_name,
99 p_file_path,
100 p_map_id,
101 i_run_id
102 );
103 END IF;
104 --
105 /** Initialize the Stack Table **/
106 ec_debug.pl(0,'EC','ECE_END_STAGING','TRANSACTION_TYPE',p_transaction_type);
107 --
108 ec_utils.g_stack.DELETE;
109 --
110 ec_debug.pl(0,'EC','ECE_START_GENERIC_INBOUND',
111 'TRANSACTION_TYPE', p_transaction_type);
112 --
113 ece_inbound.process_run_inbound (
114 i_transaction_type => p_transaction_type,
115 i_run_id => i_run_id);
116 --
117 ec_debug.pl(0,'EC','ECE_FINISH_GENERIC_INBOUND',
118 'TRANSACTION_TYPE',p_transaction_type);
119 --
120 --4316744: Time zone uptake in RLM
121
122 UpdateHorizonDates(p_run_id => i_run_id);
123
124 IF (p_Run_Import = 'Y') THEN
125 --
126 i_count := GetCountInterfaceHeaderId(i_run_id);
127 --
128
129 ec_debug.pl(3,'i_count', i_count);
130
131 --
132 IF (i_count >0) THEN
133 --
134 ec_debug.pl(0,'EC','ECE_EXECUTE_OPEN_INTERFACE','TRANSACTION_TYPE',
135 p_transaction_type,'REQUEST_ID',i_run_id);
136
137 --bug 1873870
138
139 /*
140 --
141 i_Submit_ID := fnd_request.submit_request
142 (
143 'RLM', 'RLMDSP', NULL, NULL, FALSE,
144 NULL, NULL, NULL, NULL,
145 NULL, NULL, NULL, i_header_id,
146 p_warn_replace_schedule
147 );
148 --
149
150 */
151
152 --
153 -- MOAC: Added p_rog_id to the following API call.
154
155 i_Submit_ID := fnd_request.submit_request
156 (
157 'RLM', 'RLMDSP', NULL, NULL, FALSE,
158 p_org_id,NULL, NULL, NULL, NULL,
159 NULL, NULL, NULL, NULL,NULL,NULL,
160 p_warn_replace_schedule,NULL,p_child_processes,i_run_id
161 );
162 --
163
164
165 ec_debug.pl(0,'EC','ECE_EXECUTE_OPEN_INTERFACE','TRANSACTION_TYPE',
166 p_transaction_type,'REQUEST_ID',i_Submit_Id);
167 --
168 END IF;
169 --
170 END IF;
171 --
172 COMMIT;
173 --
174 retcode := ec_utils.i_ret_code;
175 ec_debug.pl(3,'retcode',retcode);
176 ec_debug.pl(3,'errbuf',errbuf);
177 ec_debug.pop('RLM_INBOUND_SV.PROCESS_INBOUND');
178 ec_debug.pl(0,'EC','ECE_END_INBOUND','TRANSACTION_TYPE',p_transaction_type);
179 ec_debug.disable_debug;
180 --
181 EXCEPTION
182 --
183 WHEN NO_DATA_FOUND THEN
184 --
185 ec_debug.pl(0,'EC','ECE_NO_MAP_TYPE',
186 'TRANSACTION',p_transaction_type);
187 retcode := 1;
188 ec_debug.disable_debug;
189 ROLLBACK WORK;
190 --
191 WHEN ece_transaction_disabled THEN
192 --
193 ec_debug.pl(0,'EC','ECE_TRANSACTION_DISABLED',
194 'TRANSACTION',p_transaction_type);
195 retcode := 1;
196 ec_debug.disable_debug;
197 ROLLBACK WORK;
198 --
199 WHEN EC_UTILS.PROGRAM_EXIT then
200 --
201 errbuf := ec_utils.i_errbuf;
202 retcode := ec_utils.i_ret_code;
203 ece_flatfile_pvt.print_attributes;
204 ROLLBACK WORK;
205 ec_debug.disable_debug;
206 --
207 WHEN OTHERS THEN
208 --
209 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',
210 'RLM_INBOUND_SV.PROCESS_INBOUND');
211 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
212 retcode := 2;
213 ROLLBACK WORK;
214 ece_flatfile_pvt.print_attributes;
215 ec_debug.disable_debug;
216 --
217 END PROCESS_INBOUND;
218
219 FUNCTION GetCountInterfaceHeaderId(x_request_id IN NUMBER)
220 RETURN NUMBER
221 IS
222 --
223 x_count NUMBER;
224 --
225 BEGIN
226 --
227 ec_debug.pl(0,'EC','ECE_START_GET_HEADER','REQUEST_ID',x_request_id);
228 ec_debug.push('RLM_INBOUND_SV.GetCountInterfaceHeaderId');
229 ec_debug.pl(3,'request_id',x_request_id);
230 --
231 SELECT count(*)
232 INTO x_count
233 FROM rlm_interface_headers
234 where request_id = x_request_id;
235 --
236 ec_debug.pl(3,'count',x_count);
237 ec_debug.pop('RLM_INBOUND_SV.GetCountInterfaceHeaderId');
238 ec_debug.pl(0,'EC','ECE_END_GET_HEADER','REQUEST_ID',x_request_id);
239 RETURN x_count;
240 --
241 EXCEPTION
242 --
243 WHEN NO_DATA_FOUND THEN
244 --
245 ec_debug.pl(0,'EC','ECE_NO_HEADER',
246 'REQUEST',x_request_id);
247 ec_debug.pop('RLM_INBOUND_SV.GETCOUNTINTERFACEHEADERID');
248 RETURN NULL;
249 --
250
251 WHEN OTHERS THEN
252 --
253 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',
254 'RLM_INBOUND_SV.GETCOUNTINTERFACEHEADERID');
255 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
256 ec_debug.pop('RLM_INBOUND_SV.GETCOUNTINTERFACEHEADERID');
257 raise;
258 --
259 END GetCountInterfaceHeaderId;
260
261 --4316744: Timezone uptake in RLM.Added the following new procedure.
262
263 /*===========================================================================
264
265 PROCEDURE NAME: UpdateHorizonDates
266
267 ===========================================================================*/
268
269 PROCEDURE UpdateHorizonDates(p_run_id IN NUMBER) IS
270 BEGIN
271 --
272 ec_debug.pl(0,'EC','ECE_START_UPDATE_HZ_DATES','RUN_ID',p_run_id);
273 ec_debug.push('RLM_INBOUND_SV.UpdateHorizonDates');
274 ec_debug.pl(3,'p_run_id', p_run_id);
275 --
276 UPDATE rlm_interface_headers_all
277 SET sched_horizon_start_date = TRUNC(sched_horizon_start_date),
278 sched_horizon_end_date = TRUNC(sched_horizon_end_date) + 0.99999
279 WHERE header_id IN
280 (SELECT header_id
281 FROM rlm_interface_headers
282 WHERE request_id = p_run_id);
283 --
284 ec_debug.pl(3, 'Number of headers updated', SQL%ROWCOUNT);
285 ec_debug.pop('RLM_INBOUND_SV.UpdateHorizonDates');
286 ec_debug.pl(0,'EC','ECE_END_UPDATE_HZ_DATES', 'p_run_id', p_run_id);
287 --
288 EXCEPTION
289 --
290 WHEN NO_DATA_FOUND THEN
291 --
292 ec_debug.pl(0,'EC','ECE_NO_HEADER',
293 'p_run_id', p_run_id);
294 ec_debug.pop('RLM_INBOUND_SV.UpdateHorizonDates');
295 --
296 WHEN OTHERS THEN
297 --
298 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',
299 'RLM_INBOUND_SV.UpdateHorizonDates');
300 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
301 ec_debug.pop('RLM_INBOUND_SV.UpdateHorizonDates');
302 raise;
303 --
304 END UpdateHorizonDates;
305
306
307 END RLM_INBOUND_SV;