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