1 PACKAGE BODY INV_RMA_SERIAL_PVT AS
2 /* $Header: INVRMASB.pls 120.6 2006/06/02 05:59:02 sgumaste ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_RMA_SERIAL_PVT';
5
6 --
7 --Return values for API
8 --1) x_return_status = S, x_errorcode = 0
9 -- Serial numbers were populated in temp table
10 --2) x_return_status = E, x_errorcode = 1
11 -- No serials were found in oe_lot_serial_numbers
12 --3) x_return_status = E, x_errorcode > 100
13 -- There was some problem with the serial numbers entered
14 --4) x_return_status = U
15 -- Unexpected error
16
17 procedure populate_temp_table(
18 p_api_version IN NUMBER,
19 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
20 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
21 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2,
25 x_errorcode OUT NOCOPY NUMBER,
26
27 p_rma_line_id IN NUMBER,
28 p_org_id IN NUMBER,
29 p_item_id IN NUMBER) IS
30
31 l_api_version CONSTANT NUMBER := 0.9;
32 l_api_name CONSTANT VARCHAR2(30) := 'populate_temp_table';
33
34 l_orig_line_id NUMBER;
35 l_split_from_line_id NUMBER;
36 l_line_set_id NUMBER;
37 l_counter NUMBER;
38 l_qty NUMBER;
39 l_length NUMBER;
40 l_number_part NUMBER;
41 l_err NUMBER;
42 l_padded_length NUMBER;
43 l_count NUMBER;
44 l_temp_count NUMBER := 0;
45
46 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
47 l_lot_number VARCHAR2(80);
48 l_fm_serial_number VARCHAR2(30);
49 l_to_serial_number VARCHAR2(30);
50 l_prefix VARCHAR2(30); -- Increased the field width to 30 to match with the return value X_PREFIX
51 -- from mtl_serial_check.inv_serial_info for Bug 4312849
52 l_fm_num VARCHAR2(30); -- Increased the field width to 30
53 l_to_num VARCHAR2(30); -- Increased the field width to 30
54 l_serial_number VARCHAR2(30);
55
56 CURSOR oe_lot_serials(p_line_id IN NUMBER) IS
57 SELECT lot_number, from_serial_number, to_serial_number
58 FROM oe_lot_serial_numbers
59 WHERE line_id = p_line_id;
60
61 BEGIN
62
63 -- Standard Call to check for call compatibility
64 IF NOT FND_API.Compatible_API_Call(l_api_version
65 , p_api_version
66 , l_api_name
67 , G_PKG_NAME) THEN
68 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
69 END IF;
70 --
71 -- Initialize message list if p_init_msg_list is set to true
72 IF FND_API.to_Boolean(p_init_msg_list) THEN
73 FND_MSG_PUB.initialize;
74 END IF;
75 --
76 -- Initialisize API return status to success
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78 x_errorcode := 0;
79 INV_RMA_SERIAL_PVT.g_return_status := x_return_status; --3572112
80 INV_RMA_SERIAL_PVT.g_error_code := x_errorcode; --3572112
81 INV_RMA_SERIAL_PVT.g_rma_line_id := p_rma_line_id; --3572112
82 --Bug 3363907
83 --We will now check if serials already exist in the temp
84 --table. Only if the serials are not in temp table will
85 --we continue, else we will return success.
86
87 select count(*)
88 into l_temp_count
89 from mtl_rma_serial_temp
90 where line_id = p_rma_line_id;
91
92 IF l_temp_count = 0 THEN
93
94 --API BODY
95
96 --Get the line id from oe_order_lines_all
97 --Logic Used
98 --1) Normal Case
99 -- split_from_line_id is null in oe_order_lines_all
100 -- use line_id to join to oe_lot_serial_numbers.
101 --2) Split Case
102 -- split_from_line_id is not null in oe_order_lines_all
103 -- get line_set_id from oe_order_lines_all to join to
104 -- oe_lot_serial_numbers.
105
106 /* Commented the below code and added the new select statement to get
107 * correct value in l_orig_line_id i.e to get always line_id of parent RMA line
108 * from which subsequent lines got split. For bug 5144099 */
109 /*
110 SELECT split_from_line_id, line_set_id
111 INTO l_split_from_line_id, l_line_set_id
112 FROM oe_order_lines_all
113 WHERE line_id = p_rma_line_id;
114
115 IF l_split_from_line_id IS NULL THEN
116
117 --1) Normal Case
118 l_orig_line_id := p_rma_line_id;
119
120 ELSIF l_split_from_line_id IS NOT NULL THEN
121
122 --2) Split Case
123 l_orig_line_id := l_split_from_line_id;
124 END IF;
125 */
126
127 SELECT min(line_id)
128 INTO l_orig_line_id
129 FROM oe_order_lines_all
130 START WITH line_id = p_rma_line_id
131 CONNECT BY PRIOR split_from_line_id = line_id;
132
133 /* End of fix for bug 5144099*/
134
135 --Check if there are rows in the oe_lot_serial_numbers
136 --table before opening cursor
137 SELECT count(*)
138 INTO l_count
139 FROM oe_lot_serial_numbers
140 WHERE line_id = l_orig_line_id;
141
142 --IF count=0 then set x_errorcode=1
143 --and throw G_EXC_ERROR.
144
145 IF (l_count = 0) THEN
146 x_errorcode := 1;
147 INV_RMA_SERIAL_PVT.g_error_code := x_errorcode; --3572112
148 RAISE FND_API.G_EXC_ERROR;
149 ELSIF (l_count > 0) THEN
150
151 OPEN oe_lot_serials(l_orig_line_id);
152 LOOP
153 FETCH oe_lot_serials
154 INTO l_lot_number, l_fm_serial_number, l_to_serial_number;
155
156 EXIT WHEN oe_lot_serials%NOTFOUND;
157
158
159 IF NOT mtl_serial_check.inv_serial_info(
160 P_FROM_SERIAL_NUMBER => l_fm_serial_number,
161 P_TO_SERIAL_NUMBER => l_to_serial_number,
162 X_PREFIX => l_prefix,
163 X_QUANTITY => l_qty,
164 X_FROM_NUMBER => l_fm_num,
165 X_TO_NUMBER => l_to_num,
166 X_ERRORCODE => l_err) THEN
167
168 BEGIN
169 x_errorcode := to_number(l_err);
170 INV_RMA_SERIAL_PVT.g_error_code := x_errorcode; --3572112
171 RAISE FND_API.G_EXC_ERROR;
172 END;
173 END IF;
174
175 l_number_part := TO_NUMBER(l_fm_num);
176 l_counter := 1;
177 l_length := LENGTH(l_fm_serial_number);
178
179 --In the following loop we generat the serial
180 --one at a time and insert into temp table.
181 --Bug#4411411: l_number_part will be null if the serial has no numeric part.
182 -- Need to do NVL for the length(l_number_part). Prefix will
183 -- be null when serial has ONLY numbers or if serial ends in alphabet.
184 -- Need to do NVL for l_prefix for this case.
185 WHILE (l_counter <= l_qty) LOOP
186 l_padded_length := l_length - nvl(length(l_number_part),0);
187 l_serial_number := rpad(nvl(l_prefix,0),l_padded_length, '0')||l_number_part;
188 l_number_part := nvl(l_number_part,0) + 1;
189 l_counter := l_counter + 1;
190
191 --Insert serial into temp table
192
193 INSERT INTO mtl_rma_serial_temp
194 (organization_id,
195 inventory_item_id,
196 lot_number,
197 serial_number,
198 line_id) VALUES
199 (p_org_id,
200 p_item_id,
201 l_lot_number,
202 l_serial_number,
203 p_rma_line_id);
204
205 END LOOP;
206 END LOOP;
207 END IF;
208 x_return_status := FND_API.G_RET_STS_SUCCESS;
209 INV_RMA_SERIAL_PVT.g_return_status := x_return_status;
210
211 ELSE IF (l_temp_count > 0) THEN
212 --Serials are already populated in the temp table
213 --return error_code = 0 and success.
214 x_return_status := FND_API.G_RET_STS_SUCCESS;
215 x_errorcode := 0;
216 INV_RMA_SERIAL_PVT.g_return_status := x_return_status; --3572112
217 INV_RMA_SERIAL_PVT.g_error_code := x_errorcode; --3572112
218 END IF;
219
220 END IF;
221
222 EXCEPTION
223 WHEN FND_API.G_EXC_ERROR THEN
224 x_return_status := FND_API.G_RET_STS_ERROR;
225 INV_RMA_SERIAL_PVT.g_return_status := x_return_status; --3572112
226
227 WHEN OTHERS THEN
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 INV_RMA_SERIAL_PVT.g_return_status := x_return_status; --3572112
230
231 END populate_temp_table;
232 -- Added the below function for bug 3572112
233 function validate_serial_required(p_rma_line_id IN NUMBER) return NUMBER IS
234 BEGIN
235 IF ( INV_RMA_SERIAL_PVT.g_return_status = 'S' AND
236 INV_RMA_SERIAL_PVT.g_error_code = 0 AND
237 INV_RMA_SERIAL_PVT.g_rma_line_id = p_rma_line_id ) THEN
238 RETURN 0;
239 ELSE
240 RETURN 1;
241 END IF;
242 END validate_serial_required;
243
244 END INV_RMA_SERIAL_PVT;
[Home] [Help]