[Home] [Help]
PACKAGE BODY: APPS.PN_LOCATION_ALIAS_PKG
Source
1 PACKAGE BODY pn_location_alias_pkg AS
2 /* $Header: PNLCALSB.pls 120.2.12010000.2 2008/11/27 04:24:49 rthumma ship $ */
3
4 TYPE id_tbl IS TABLE OF pn_locations.location_id%TYPE INDEX BY BINARY_INTEGER;
5 TYPE code_tbl IS TABLE OF pn_locations.location_code%TYPE INDEX BY BINARY_INTEGER;
6 TYPE alias_tbl IS TABLE OF pn_locations.location_alias%TYPE INDEX BY BINARY_INTEGER;
7
8 ------------------------------------------------------------------------------+
9 -- FUNCTION : get_parent_location_code
10 --
11 -- DESCRIPTION:
12 -- 1. Finds location code from p_cd_tbl given a location id that matches.
13 --
14 -- HISTORY:
15 -- 18-JUN-03 ftanudja o created.
16 -- 20-JUN-03 ftanudja o rewrote to avoid loc separator impact.
17 ------------------------------------------------------------------------------+
18
19 FUNCTION get_parent_location_code(
20 p_cd_tbl IN code_tbl,
21 p_id_tbl IN id_tbl,
22 p_loc_id IN pn_locations.location_id%TYPE) RETURN VARCHAR2
23 IS
24 l_info VARCHAR2(300);
25 l_desc VARCHAR2(300) := 'pn_location_alias_pkg.get_parent_location_code';
26 l_result pn_locations.location_code%TYPE := '';
27 BEGIN
28 pnp_debug_pkg.debug(l_desc ||' (+)');
29
30 l_info := 'starting loop ';
31 FOR i IN REVERSE 0 .. p_id_tbl.COUNT - 1 LOOP
32 IF p_id_tbl(i) = p_loc_id THEN
33 l_result := p_cd_tbl(i);
34 exit;
35 END IF;
36 END LOOP;
37
38 RETURN l_result;
39
40 pnp_debug_pkg.debug(l_desc ||' (-)');
41
42 EXCEPTION
43 WHEN OTHERS THEN
44 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
45 raise;
46 END get_parent_location_code;
47
48 ------------------------------------------------------------------------------+
49 -- PROCEDURE : change_alias
50 -- DESCRIPTION: main extraction program for changing location code alias names
51 -- 1. Given : some location code and a new alias.
52 -- 2. Change alias and loc code for that location as well as its descendants.
53 --
54 -- HISTORY:
55 -- 18-JUN-03 ftanudja o created.
56 -- 20-JUN-03 ftanudja o rewrote to avoid loc separator impact.
57 -- 09-OCT-06 acprakas o Bug#5571818 - Changed Cursor definition for impacted_loc
58 -- to pick up the locations of specified type.
59 -- Added check to see whether any location already exists
60 -- having the specified location code.
61 -- 27-Nov-08 rthumma o Bug 6735518 : Modified cursor impacted_loc and 'Update'
62 -- statement. Changes are tagged with bug number.
63 ------------------------------------------------------------------------------+
64
65 PROCEDURE change_alias(
66 errbuf OUT NOCOPY VARCHAR2,
67 retcode OUT NOCOPY VARCHAR2,
68 p_location_type IN pn_locations.location_type_lookup_code%TYPE,
69 p_location_code IN pn_locations.location_code%TYPE,
70 p_new_alias IN pn_locations.location_alias%TYPE)
71 IS
72 CURSOR impacted_loc IS
73 SELECT location_id,
74 parent_location_id,
75 location_code,
76 location_alias
77 FROM pn_locations_all /*sdm14jul*/
78 WHERE NVL(TRUNC(ACTIVE_END_DATE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE) /* Bug 6735518 */
79 START WITH location_id = (SELECT location_id FROM
80 (SELECT location_id
81 FROM pn_locations_all
82 WHERE location_type_lookup_code = p_location_type
83 AND location_code = p_location_code
84 ORDER BY ACTIVE_START_DATE DESC)
85 WHERE ROWNUM = 1) /* Bug 6735518 */
86 CONNECT BY PRIOR location_id = parent_location_id
87 ORDER BY 3;
88
89 loc_id_tbl id_tbl;
90 als_nm_tbl alias_tbl;
91 old_cd_tbl code_tbl;
92 new_cd_tbl code_tbl;
93
94 l_info VARCHAR2(300);
95 l_desc VARCHAR2(300) := 'pn_location_alias_pkg.change_alias';
96 l_count NUMBER;
97 l_uniq_loc_count NUMBER := 0; --Bug#5571818
98 INVALID_LOC_CODE EXCEPTION; --Bug#5571818
99
100 BEGIN
101 pnp_debug_pkg.debug(l_desc ||' (+)');
102
103 l_info := 'initializing pl/sql tables ';
104 loc_id_tbl.delete;
105 als_nm_tbl.delete;
106 old_cd_tbl.delete;
107 new_cd_tbl.delete;
108
109 l_info := 'checking location code uniqueness';
110
111 --Bug#5571818
112 SELECT COUNT(1)
113 INTO l_uniq_loc_count
114 FROM pn_locations_all
115 WHERE location_code = p_new_alias;
116
117 IF l_uniq_loc_count <> 0 THEN
118 raise INVALID_LOC_CODE;
119 END IF;
120
121 l_info := 'fetching information ';
122 FOR init_tbl_cur IN impacted_loc LOOP
123
124 l_count := loc_id_tbl.COUNT;
125 loc_id_tbl(l_count) := init_tbl_cur.location_id;
126 old_cd_tbl(l_count) := init_tbl_cur.location_code;
127
128 IF init_tbl_cur.location_code = p_location_code THEN
129 als_nm_tbl(l_count) := p_new_alias;
130 new_cd_tbl(l_count) := SUBSTR(init_tbl_cur.location_code, 1,
131 LENGTH(init_tbl_cur.location_code) -
132 LENGTH(init_tbl_cur.location_alias))
133 || p_new_alias;
134
135 ELSE
136 als_nm_tbl(l_count) := init_tbl_cur.location_alias;
137 new_cd_tbl(l_count) := get_parent_location_code(
138 p_cd_tbl => new_cd_tbl,
139 p_id_tbl => loc_id_tbl,
140 p_loc_id => init_tbl_cur.parent_location_id) ||
141 SUBSTR(init_tbl_cur.location_code,
142 LENGTH(get_parent_location_code(
143 p_cd_tbl => old_cd_tbl,
144 p_id_tbl => loc_id_tbl,
145 p_loc_id => init_tbl_cur.parent_location_id)) + 1,
146 LENGTH(init_tbl_cur.location_code));
147 END IF;
148
149 END LOOP;
150
151 l_info := 'updating table with new values ';
152 FORALL i IN 0 .. loc_id_tbl.COUNT - 1
153 UPDATE pn_locations_all /*sdm14jul*/
154 SET location_code = new_cd_tbl(i),
155 location_alias = als_nm_tbl(i),
156 last_update_date = SYSDATE,
157 last_updated_by = nvl(fnd_profile.value('USER_ID'), -1),
158 last_update_login = nvl(fnd_profile.value('USER_ID'), -1)
159 WHERE location_id = loc_id_tbl(i)
160 AND NVL(TRUNC(ACTIVE_END_DATE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE); /* Bug 6735518 */
161
162 pnp_debug_pkg.debug(l_desc ||' (-)');
163
164 EXCEPTION
165 --Bug#5571818
166 WHEN INVALID_LOC_CODE THEN
167 fnd_message.set_name ('PN','PN_LOCN_TYPE_CODE_DUP');
168 pnp_debug_pkg.put_log_msg(fnd_message.get);
169 raise;
170 WHEN OTHERS THEN
171 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
172 raise;
173
174 END change_alias;
175
176 END pn_location_alias_pkg;