eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PN_EXP_TO_EAM_PVT

Source


1 PACKAGE BODY PN_EXP_TO_EAM_PVT as
2 /* $Header: PNXPEAMB.pls 120.3 2006/05/04 03:08:08 hrodda noship $ */
3 
4 PROCEDURE export_location_to_eam (
5             errbuf                  OUT NOCOPY VARCHAR2,
6             retcode                 OUT NOCOPY VARCHAR2,
7             p_batch_name            IN VARCHAR2,
8             p_locn_code_from        IN pn_locations_all.location_code%TYPE,
9             p_locn_code_to          IN pn_locations_all.location_code%TYPE,
10             p_locn_type             IN pn_locations_all.location_type_lookup_code%TYPE,
11             p_organization_id       IN mtl_serial_numbers.current_organization_id%TYPE,
12             p_inventory_item_id     IN mtl_serial_numbers.inventory_item_id%TYPE,
13             p_owning_department_id  IN mtl_serial_numbers.owning_department_id%TYPE,
14             p_maintainable_flag     IN mtl_serial_numbers.maintainable_flag%TYPE)
15 IS
16 
17    location_rec                 pn_locations_all%ROWTYPE;
18    l_serial_num                 mtl_eam_asset_num_interface.serial_number%TYPE;
19    l_query                      VARCHAR2(1000);
20    l_where_clause               VARCHAR2(1000);
21    l_industry                   VARCHAR2(30);
22    l_installation_status        VARCHAR2(5);
23    l_insert                     NUMBER;
24    l_insert_mode                NUMBER;
25    l_insert_status              NUMBER;
26    l_msg_count                  NUMBER;
27    l_count_lines                NUMBER;
28    l_count_success              NUMBER;
29    l_count_failure              NUMBER;
30    l_process_flag               BOOLEAN;
31    l_return_status              VARCHAR2(250);
32    l_msg_data                   VARCHAR2(250);
33    l_info                       VARCHAR2(300);
34    l_message                    VARCHAR2(300);
35    l_cursor                     INTEGER;
36    l_rows                       INTEGER;
37    l_count                      INTEGER;
38    l_locn_type                  VARCHAR2(30);
39    l_locn_code_from             VARCHAR2(90);
40    l_locn_code_to               VARCHAR2(90);
41 
42 
43 BEGIN
44 
45    pnp_debug_pkg.log('PN_EXP_TO_EAM_PVT.EXPORT_LOCATION_TO_EAM (+)');
46 
47    l_info := 'Initializing counters ';
48    pnp_debug_pkg.log(l_info);
49 
50    l_count_lines   := 0;
51    l_count_success := 0;
52    l_count_failure := 0;
53 
54    l_info := 'Checking for EAM installation ';
55    pnp_debug_pkg.log(l_info);
56 
57    IF fnd_installation.get (
58          appl_id     => 426,
59          dep_appl_id => 426,
60          status      => l_installation_status,
61          industry    => l_industry)
62    THEN
63       null;
64    END IF;
65 
66    IF (l_installation_status not in ('I','S')) THEN
67       pnp_debug_pkg.log('EAM is not installed ...');
68       RETURN;
69    END IF;
70 
71    l_cursor := dbms_sql.open_cursor;
72    l_query :=
73       'SELECT location_id,'
74                || 'parent_location_id,'
75                || 'location_code,'
76                || 'active_start_date,'
77                || 'active_end_date'
78                || ' FROM pn_locations'
79                || ' WHERE SYSDATE BETWEEN active_start_date AND NVL(active_end_date,'
80                || ''''||TO_DATE('31/12/4712','DD/MM/YYYY') || ''''
81                ||')';
82 
83    l_info := 'Figuring location type lookup code ';
84    pnp_debug_pkg.log(l_info);
85 
86    IF p_locn_type IS NOT NULL THEN
87       l_locn_type := p_locn_type;
88       l_query :=
89       l_query || ' AND location_type_lookup_code =  :l_locn_type';
90 
91    END IF;
92 
93    l_info := 'Figuring location code ';
94    pnp_debug_pkg.log(l_info);
95 
96    IF p_locn_code_from IS NOT NULL AND p_locn_code_to IS NOT NULL THEN
97       l_locn_code_from := p_locn_code_from;
98       l_locn_code_to := p_locn_code_to;
99       l_where_clause :=
100       l_where_clause || ' AND location_code between :l_locn_code_from AND :l_locn_code_to ';
101 
102    ELSIF p_locn_code_from IS NOT NULL AND p_locn_code_to IS NULL THEN
103       l_locn_code_from := p_locn_code_from;
104       l_where_clause :=
105       l_where_clause || ' AND location_code >=  :l_locn_code_from ';
106 
107    ELSIF p_locn_code_from IS NULL AND p_locn_code_to IS NOT NULL THEN
108       l_locn_code_to := p_locn_code_to;
109       l_where_clause :=
110       l_where_clause || ' AND location_code <=  :l_locn_code_to ';
111 
112    END IF;
113 
114    /** Note: If a location_code_to is specified then all its descendants will be extracted **/
115 
116    IF p_locn_code_to IS NOT NULL THEN
117       l_locn_code_to := p_locn_code_to;
118       l_query := l_query || l_where_clause ||' UNION '|| l_query ||
119                  ' START WITH location_code = :l_locn_code_to
120                    CONNECT BY parent_location_id = PRIOR location_id';
121    ELSE
122       l_query := l_query || l_where_clause;
123    END IF;
124 
125    l_query := l_query || ' ORDER BY location_id ';
126    l_info := 'Figuring out the max id number in the mtl_eam_asset_num_interface ';
127    pnp_debug_pkg.log(l_info);
128 
129    dbms_sql.parse(l_cursor, l_query, dbms_sql.native);
130 
131    IF p_locn_type IS NOT NULL THEN
132       dbms_sql.bind_variable
133             (l_cursor,'l_locn_type',l_locn_type );
134    END IF;
135 
136    IF p_locn_code_from IS NOT NULL AND p_locn_code_to IS NOT NULL THEN
137       dbms_sql.bind_variable
138             (l_cursor,'l_locn_code_from',l_locn_code_from );
139       dbms_sql.bind_variable
140             (l_cursor,'l_locn_code_to',l_locn_code_to );
141    ELSIF p_locn_code_from IS NOT NULL AND p_locn_code_to IS NULL THEN
142       dbms_sql.bind_variable
143             (l_cursor,'l_locn_code_from',l_locn_code_from );
144    ELSIF p_locn_code_from IS NULL AND p_locn_code_to IS NOT NULL THEN
145       dbms_sql.bind_variable
146             (l_cursor,'l_locn_code_to',l_locn_code_to );
147    END IF;
148 
149    IF p_locn_code_to IS NOT NULL THEN
150       dbms_sql.bind_variable
151             (l_cursor,'l_locn_code_to',l_locn_code_to );
152    END IF;
153 
154    dbms_sql.define_column (l_cursor, 1,location_rec.location_id);
155    dbms_sql.define_column (l_cursor, 2,location_rec.parent_location_id);
156    dbms_sql.define_column (l_cursor, 3,location_rec.location_code,90);
157    dbms_sql.define_column (l_cursor, 4,location_rec.active_start_date);
158    dbms_sql.define_column (l_cursor, 5,location_rec.active_end_date);
159 
160    l_rows   := dbms_sql.execute(l_cursor);
161 
162    LOOP
163       l_count := dbms_sql.fetch_rows( l_cursor );
164       EXIT WHEN l_count <> 1;
165 
166       dbms_sql.column_value (l_cursor, 1,location_rec.location_id);
167       dbms_sql.column_value (l_cursor, 2,location_rec.parent_location_id);
168       dbms_sql.column_value (l_cursor, 3,location_rec.location_code);
169       dbms_sql.column_value (l_cursor, 4,location_rec.active_start_date);
170       dbms_sql.column_value (l_cursor, 5,location_rec.active_end_date);
171 
172       l_process_flag := TRUE;
173       l_count_lines  := l_count_lines + 1;
174 
175       pnp_debug_pkg.put_log_msg('*******************************************************************************');
176 
177       fnd_message.set_name ('PN','PN_XPEAM_SLNO');
178       fnd_message.set_token ('SL_NO', to_char(l_count_lines));
179       l_message := fnd_message.get;
180       pnp_debug_pkg.put_log_msg(l_message);
181 
182       fnd_message.set_name ('PN','PN_XPEAM_PROC');
183       fnd_message.set_token ('LOC_CODE',location_rec.location_code);
184       fnd_message.set_token ('ST_DATE',location_rec.active_start_date);
185       fnd_message.set_token ('END_DATE',NVL(location_rec.active_end_date,TO_DATE('31/12/4712','DD/MM/YYYY')));
186       l_message := fnd_message.get;
187       pnp_debug_pkg.put_log_msg(l_message);
188 
189       l_info := 'Calling EAM API to validate data ';
190       pnp_debug_pkg.log(l_info);
191 
192       BEGIN
193          pnp_debug_pkg.log('EAM_PN_EXTRACTION_PUB.PN_EAM_EXPORT_MODE (+)');
194 
195          eam_pn_extraction_pub.pn_eam_export_mode(
196             p_api_version        => 1.0,
197             p_pn_location_id     => location_rec.location_id,
198             p_parent_location_id => location_rec.parent_location_id,
199             p_active_start_date  => location_rec.active_start_date,
200             p_active_end_date    => location_rec.active_end_date,
201             x_insert             => l_insert,
202             x_insert_mode        => l_insert_mode,
203             x_insert_status      => l_insert_status,
204             x_return_status      => l_return_status,
205             x_msg_count          => l_msg_count,
206             x_msg_data           => l_msg_data);
207         pnp_debug_pkg.log('EAM_PN_EXTRACTION_PUB.PN_EAM_EXPORT_MODE (-)');
208       EXCEPTION
209          WHEN OTHERS THEN
210             l_process_flag := FALSE;
211             l_count_failure := l_count_failure + 1;
212             pnp_debug_pkg.put_log_msg('Failure Number: ('||to_char(l_count_failure) ||')'||
213                                       'Error while calling EAM API for location :'||location_rec.location_code);
214       END;
215 
216       IF l_insert = 1 AND l_process_flag THEN
217 
218          l_info := 'Figuring out how location code maps to serial number ';
219          pnp_debug_pkg.log(l_info);
220 
221          IF LENGTH(location_rec.location_code) > 30 THEN
222             l_serial_num := SUBSTR(location_rec.location_code, 1, 20) ||
223                                SUBSTR(TO_CHAR(location_rec.location_id),1,10);
224          ELSE
225             l_serial_num := location_rec.location_code;
226          END IF;
227 
228          l_info := 'Inserting data into mtl_eam_asset_num_interface table ';
229 
230          fnd_message.set_name ('PN','PN_XPEAM_INS');
231          fnd_message.set_token ('TBL', 'MTL_EAM_ASSET_NUM_INTERFACE');
232          l_message := fnd_message.get;
233          pnp_debug_pkg.put_log_msg(l_message);
234 
235 
236          BEGIN
237 
238             fnd_message.set_name ('PN','PN_XPEAM_INSERTING');
239             l_message := '*** '||fnd_message.get||' ...';
240             pnp_debug_pkg.put_log_msg(l_message);
241 
242 
243             INSERT INTO mtl_eam_asset_num_interface(
244                inventory_item_id,
245                serial_number,
246                interface_header_id,
247                import_mode,
248                import_scope,
249                current_status,
250                batch_id,
251                batch_name,
252                current_organization_id,
253                owning_department_id,
254                pn_location_id,
255                process_flag,
256                maintainable_flag,
257                creation_date,
258                last_update_date,
259                created_by,
260                last_updated_by,
261                last_update_login,
262                instance_number,
263                active_start_date,
264                active_end_date
265             ) VALUES (
266                p_inventory_item_id,
267                l_serial_num,
268                mtl_eam_asset_int_header_s.nextval,
269                l_insert_mode,
270                1,
271                l_insert_status,
272                p_inventory_item_id,
273                p_batch_name,
274                p_organization_id,
275                p_owning_department_id,
276                location_rec.location_id,
277                'P',
278                p_maintainable_flag,
279                SYSDATE,
280                SYSDATE,
281                fnd_global.user_id,
282                fnd_global.user_id,
283                fnd_global.login_id,
284                l_serial_num,
285                location_rec.active_start_date,
286                location_rec.active_end_date
287             );
288 
289             fnd_message.set_name ('PN','PN_XPEAM_LOC');
290             fnd_message.set_token ('LOC_CODE', TO_CHAR(l_serial_num));
291             l_message := fnd_message.get;
292             pnp_debug_pkg.put_log_msg(l_message);
293 
294             fnd_message.set_name ('PN','PN_XPEAM_INV_ID');
295             fnd_message.set_token ('INV_ID', TO_CHAR(p_inventory_item_id));
296             l_message := fnd_message.get;
297             pnp_debug_pkg.put_log_msg(l_message);
298 
299             fnd_message.set_name ('PN','PN_XPEAM_IMP_MODE');
300             fnd_message.set_token ('IMP_MODE', TO_CHAR(l_insert_mode));
301             l_message := fnd_message.get;
302             pnp_debug_pkg.put_log_msg(l_message);
303 
304             fnd_message.set_name ('PN','PN_XPEAM_CUR_STATUS');
305             fnd_message.set_token ('CUR_STATUS', TO_CHAR(l_insert_status));
306             l_message := fnd_message.get;
307             pnp_debug_pkg.put_log_msg(l_message);
308 
309             pnp_debug_pkg.log( 'Batch ID: ' || to_char(p_inventory_item_id));
310 
311             fnd_message.set_name ('PN','PN_XPEAM_BNAME');
312             fnd_message.set_token ('BNAME', TO_CHAR(p_batch_name));
313             l_message := fnd_message.get;
314             pnp_debug_pkg.put_log_msg(l_message);
315 
316             pnp_debug_pkg.log( 'Current Organization ID: ' || to_char(p_organization_id));
317             pnp_debug_pkg.log( 'Owning Department ID: ' || to_char(p_owning_department_id));
318             pnp_debug_pkg.log( 'PN Location ID: ' || to_char(location_rec.location_id));
319             pnp_debug_pkg.log( 'Process Flag: ' || to_char('P'));
320             pnp_debug_pkg.log( 'Maintainable Flag: ' || to_char('Y'));
321             l_count_success := l_count_success + 1;
322 
323             fnd_message.set_name ('PN','PN_XPEAM_PROC_LINE');
324             fnd_message.set_token ('LNO', TO_CHAR(l_count_success));
325             l_message := fnd_message.get;
326             pnp_debug_pkg.put_log_msg(l_message);
327 
328             fnd_message.set_name ('PN','PN_XPEAM_INSERTED');
329             l_message := fnd_message.get;
330             pnp_debug_pkg.put_log_msg('*** '||l_message||' ...');
331 
332             l_info := 'Doing batch commit after every 100 INSERT ';
333             IF MOD(l_count_success, 100) = 0 THEN
334                commit;
335                pnp_debug_pkg.log(l_info);
336             END IF;
337 
338          EXCEPTION
339             WHEN OTHERS THEN
340                l_count_failure := l_count_failure + 1;
341                fnd_message.set_name ('PN','PN_XPEAM_ERR_LINES');
342                fnd_message.set_token ('ER_LNO', TO_CHAR(l_count_failure));
343                l_message := fnd_message.get;
344                pnp_debug_pkg.put_log_msg(l_message);
345          END;
346 
347       ELSE
348          l_count_failure := l_count_failure + 1;
349          fnd_message.set_name ('PN','PN_XPEAM_ERR_LINES');
350          fnd_message.set_token ('PRO_LNO', TO_CHAR(l_count_failure));
351          l_message := fnd_message.get;
352          pnp_debug_pkg.put_log_msg(l_message);
353       END IF;
354 
355    END LOOP;
356 
357    IF dbms_sql.is_open (l_cursor) THEN
358       dbms_sql.close_cursor (l_cursor);
359    END IF;
360 
361 
362    pnp_debug_pkg.put_log_msg('===========================================================================');
363    fnd_message.set_name ('PN','PN_XPEAM_PROC_LN');
364    fnd_message.set_token ('PR_LNO', TO_CHAR(l_count_lines));
365    l_message := fnd_message.get;
366    pnp_debug_pkg.put_log_msg(l_message);
367 
368    fnd_message.set_name ('PN','PN_XPEAM_SUCS_LN');
369    fnd_message.set_token ('SUC_LNO', TO_CHAR(l_count_success));
370    l_message := fnd_message.get;
371    pnp_debug_pkg.put_log_msg(l_message);
372 
373    fnd_message.set_name ('PN','PN_XPEAM_FAIL_LN');
374    fnd_message.set_token ('FAIL_LNO', TO_CHAR(l_count_failure));
375    l_message := fnd_message.get;
376    pnp_debug_pkg.put_log_msg(l_message);
377    pnp_debug_pkg.put_log_msg('===========================================================================');
378 
379    pnp_debug_pkg.log('PN_EXP_TO_EAM_PVT.EXPORT_LOCATION_TO_EAM (-)');
380 
381 EXCEPTION
382    WHEN OTHERS THEN
383 
387 END export_location_to_eam;
384       pnp_debug_pkg.put_log_msg('PN_EXP_TO_EAM_PVT.EXPORT_LOCATION_TO_EAM : Error while ' || l_info);
385       raise;
386 
388 
389 END pn_exp_to_eam_pvt;