[Home] [Help]
PACKAGE BODY: APPS.PN_LOCATION_ALIAS_PKG
Source
1 PACKAGE BODY pn_location_alias_pkg AS
2 /* $Header: PNLCALSB.pls 120.4.12020000.3 2013/02/07 09:35:08 ppenumar 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 -- 21-Nov-11 ppenumar o Bug 9767441- Fwd port for Bug 9674450
64 -- 01-Feb-12 ppenumar o Bug 9767441- Modified the select query in the CURSOR 'impacted_loc'
65 -- as a part of Projects 12.1.3 Delta 4 CU8
66 -- 07-Feb-13 ppenumar o Bug16098151- Made a small change to the fix Bug#5571818.
67 -- For checking the 'location code uniqueness' the earlier code was
68 -- comparing the new alias with the location code. Modified it to compare
69 -- the new location code with the existing location codes.
70 ------------------------------------------------------------------------------+
71
72 PROCEDURE change_alias(
73 errbuf OUT NOCOPY VARCHAR2,
74 retcode OUT NOCOPY VARCHAR2,
75 p_location_type IN pn_locations.location_type_lookup_code%TYPE,
76 p_location_code IN pn_locations.location_code%TYPE,
77 p_new_alias IN pn_locations.location_alias%TYPE)
78 IS
79 CURSOR impacted_loc IS
80 SELECT location_id,
81 parent_location_id,
82 location_code,
83 location_alias
84 FROM pn_locations_all /*sdm14jul*/
85 START WITH location_id = (SELECT location_id FROM
86 (SELECT location_id
87 FROM pn_locations_all
88 WHERE location_type_lookup_code = p_location_type
89 AND location_code = p_location_code
90 ORDER BY ACTIVE_START_DATE DESC)
91 WHERE ROWNUM = 1)
92 CONNECT BY PRIOR location_id = parent_location_id
93 ORDER BY 3;
94
95 loc_id_tbl id_tbl;
96 als_nm_tbl alias_tbl;
97 old_cd_tbl code_tbl;
98 new_cd_tbl code_tbl;
99
100 l_info VARCHAR2(300);
101 l_desc VARCHAR2(300) := 'pn_location_alias_pkg.change_alias';
102 l_count NUMBER;
103 l_uniq_loc_count NUMBER := 0; --Bug#5571818
104 INVALID_LOC_CODE EXCEPTION; --Bug#5571818
105 l_new_loc_code VARCHAR2(300); -- Bug#16098151
106
107 BEGIN
108 pnp_debug_pkg.debug(l_desc ||' (+)');
109
110 l_info := 'initializing pl/sql tables ';
111 loc_id_tbl.delete;
112 als_nm_tbl.delete;
113 old_cd_tbl.delete;
114 new_cd_tbl.delete;
115
116 l_info := 'fetching information ';
117 FOR init_tbl_cur IN impacted_loc LOOP
118
119 l_count := loc_id_tbl.COUNT;
120 loc_id_tbl(l_count) := init_tbl_cur.location_id;
121 old_cd_tbl(l_count) := init_tbl_cur.location_code;
122
123 IF init_tbl_cur.location_code = p_location_code THEN
124 als_nm_tbl(l_count) := p_new_alias;
125 new_cd_tbl(l_count) := SUBSTR(init_tbl_cur.location_code, 1,
126 LENGTH(init_tbl_cur.location_code) -
127 LENGTH(init_tbl_cur.location_alias))
128 || p_new_alias;
129
130 ELSE
131 als_nm_tbl(l_count) := init_tbl_cur.location_alias;
132 new_cd_tbl(l_count) := get_parent_location_code(
133 p_cd_tbl => new_cd_tbl,
134 p_id_tbl => loc_id_tbl,
135 p_loc_id => init_tbl_cur.parent_location_id) ||
136 SUBSTR(init_tbl_cur.location_code,
137 LENGTH(get_parent_location_code(
138 p_cd_tbl => old_cd_tbl,
139 p_id_tbl => loc_id_tbl,
140 p_loc_id => init_tbl_cur.parent_location_id)) + 1,
141 LENGTH(init_tbl_cur.location_code));
142 END IF;
143
144 END LOOP;
145
146 l_new_loc_code := new_cd_tbl(0);
147
148 l_info := 'checking location code uniqueness';
149
150 --Bug#5571818
151 SELECT COUNT(1)
152 INTO l_uniq_loc_count
153 FROM pn_locations_all
154 WHERE location_code = l_new_loc_code
155 and location_code <> p_location_code; --Bug#16098151
156
157 IF l_uniq_loc_count <> 0 THEN
158 raise INVALID_LOC_CODE;
159 END IF;
160
161
162
163 l_info := 'updating table with new values ';
164 FORALL i IN 0 .. loc_id_tbl.COUNT - 1
165 UPDATE pn_locations_all /*Bug 9767441 */
166 SET location_code = new_cd_tbl(i),
167 location_alias = als_nm_tbl(i),
168 last_update_date = SYSDATE,
169 last_updated_by = nvl(fnd_profile.value('USER_ID'), -1),
170 last_update_login = nvl(fnd_profile.value('USER_ID'), -1)
171 WHERE location_id = loc_id_tbl(i);
172
173 pnp_debug_pkg.debug(l_desc ||' (-)');
174
175 EXCEPTION
176 --Bug#5571818
177 WHEN INVALID_LOC_CODE THEN
178 fnd_message.set_name ('PN','PN_LOCN_TYPE_CODE_DUP');
179 pnp_debug_pkg.put_log_msg(fnd_message.get);
180 raise;
181 WHEN OTHERS THEN
182 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
183 raise;
184
185 END change_alias;
186
187 END pn_location_alias_pkg;