[Home] [Help]
PACKAGE BODY: APPS.CSP_PP_UTIL
Source
1 PACKAGE BODY CSP_PP_UTIL AS
2 /* $Header: cspgtppb.pls 115.4 2002/11/26 06:43:30 hhaugeru noship $ */
3 --
4 -- File : cspgtppb.pls
5 -- Content :
6 -- Description :
7 -- Notes :
8 -- Modified : 07/31/99 bitang created
9 --
10 g_pkg_name VARCHAR2(30) := 'CSP_PP_UTIL';
11 g_file_name VARCHAR2(30) := 'cspgtppb.pls';
12 TYPE g_number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
13
14 --
15 -- insert record into mtl_transaction_lots_temp
16 PROCEDURE insert_mtlt
17 (
18 x_return_status OUT NOCOPY VARCHAR2
19 ,p_mtlt_tbl IN g_mtlt_tbl_type
20 ,p_mtlt_tbl_size IN INTEGER
21 )
22 IS
23 l_api_name CONSTANT VARCHAR2(30) := 'insert_mtlt';
24 l_today DATE;
25 l_user_id NUMBER;
26 l_login_id NUMBER;
27 l_rowid VARCHAR2(20);
28 BEGIN
29
30 -- Initialisize API return status to access
31 x_return_status := fnd_api.g_ret_sts_success;
32 IF p_mtlt_tbl_size IS NULL OR p_mtlt_tbl_size < 1 THEN
33 RETURN;
34 END IF;
35 --
36 SELECT Sysdate INTO l_today FROM dual;
37 l_user_id := fnd_global.user_id;
38 l_login_id := fnd_global.login_id;
39 FOR l_counter IN 1..p_mtlt_tbl_size LOOP
40 INSERT INTO mtl_transaction_lots_temp
41 (
42 transaction_temp_id
43 ,last_update_date
44 ,last_updated_by
45 ,creation_date
46 ,created_by
47 ,last_update_login
48 ,request_id
49 ,program_application_id
50 ,program_id
51 ,program_update_date
52 ,transaction_quantity
53 ,primary_quantity
54 ,lot_number
55 ,lot_expiration_date
56 ,error_code
57 ,serial_transaction_temp_id
58 ,group_header_id
59 ,put_away_rule_id
60 ,pick_rule_id
61 )
62 VALUES
63 (
64 p_mtlt_tbl(l_counter).transaction_temp_id
65 ,l_today
66 ,l_user_id
67 ,l_today
68 ,l_user_id
69 ,l_login_id
70 ,p_mtlt_tbl(l_counter).request_id
71 ,p_mtlt_tbl(l_counter).program_application_id
72 ,p_mtlt_tbl(l_counter).program_id
73 ,p_mtlt_tbl(l_counter).program_update_date
74 ,p_mtlt_tbl(l_counter).transaction_quantity
75 ,p_mtlt_tbl(l_counter).primary_quantity
76 ,p_mtlt_tbl(l_counter).lot_number
77 ,p_mtlt_tbl(l_counter).lot_expiration_date
78 ,p_mtlt_tbl(l_counter).error_code
79 ,p_mtlt_tbl(l_counter).serial_transaction_temp_id
80 ,p_mtlt_tbl(l_counter).group_header_id
81 ,p_mtlt_tbl(l_counter).put_away_rule_id
82 ,p_mtlt_tbl(l_counter).pick_rule_id
83 );
84 END LOOP;
85 --
86 EXCEPTION
87 when fnd_api.g_exc_error then
88 x_return_status := fnd_api.g_ret_sts_error;
89 --
90 when fnd_api.g_exc_unexpected_error then
91 x_return_status := fnd_api.g_ret_sts_unexp_error;
92 --
93 when others then
94 --
95 x_return_status := fnd_api.g_ret_sts_unexp_error;
96 /*if fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.g_msg_lvl_unexp_error) then
97 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
98 end if;*/
99 --
100 END insert_mtlt;
101 --
102 -- insert record into mtl_serial_numbers_temp
103 PROCEDURE insert_msnt
104 (
105 x_return_status OUT NOCOPY VARCHAR2
106 ,p_msnt_tbl IN g_msnt_tbl_type
107 ,p_msnt_tbl_size IN INTEGER
108 )
109 IS
110 l_api_name CONSTANT VARCHAR2(30) := 'Insert_MSNT';
111 l_today DATE;
112 l_user_id NUMBER;
113 l_login_id NUMBER;
114 l_rowid VARCHAR2(20);
115 BEGIN
116 --
117 -- Initialisize API return status to access
118 x_return_status := fnd_api.g_ret_sts_success;
119 IF p_msnt_tbl_size IS NULL OR p_msnt_tbl_size < 1 THEN
120 RETURN;
121 END IF;
122 --
123 SELECT Sysdate INTO l_today FROM dual;
124 l_user_id := fnd_global.user_id;
125 l_login_id := fnd_global.login_id;
126 FOR l_counter IN 1..p_msnt_tbl_size LOOP
127 INSERT INTO mtl_serial_numbers_temp
128 (
129 transaction_temp_id
130 ,last_update_date
131 ,last_updated_by
132 ,creation_date
133 ,created_by
134 ,last_update_login
135 ,request_id
136 ,program_application_id
137 ,program_id
138 ,program_update_date
139 ,vendor_serial_number
140 ,vendor_lot_number
141 ,fm_serial_number
142 ,to_serial_number
143 ,serial_prefix
144 ,error_code
145 ,group_header_id
146 ,parent_serial_number
147 ,end_item_unit_number
148 )
149 VALUES
150 (
151 p_msnt_tbl(l_counter).transaction_temp_id
152 ,l_today
153 ,l_user_id
154 ,l_today
155 ,l_user_id
156 ,l_login_id
157 ,p_msnt_tbl(l_counter).request_id
158 ,p_msnt_tbl(l_counter).program_application_id
159 ,p_msnt_tbl(l_counter).program_id
160 ,p_msnt_tbl(l_counter).program_update_date
161 ,p_msnt_tbl(l_counter).vendor_serial_number
162 ,p_msnt_tbl(l_counter).vendor_lot_number
163 ,p_msnt_tbl(l_counter).fm_serial_number
164 ,p_msnt_tbl(l_counter).to_serial_number
165 ,p_msnt_tbl(l_counter).serial_prefix
166 ,p_msnt_tbl(l_counter).error_code
167 ,p_msnt_tbl(l_counter).group_header_id
168 ,p_msnt_tbl(l_counter).parent_serial_number
169 ,p_msnt_tbl(l_counter).end_item_unit_number
170 );
171
172 END LOOP;
173
174 EXCEPTION
175 when fnd_api.g_exc_error then
176 x_return_status := fnd_api.g_ret_sts_error;
177 --
178 when fnd_api.g_exc_unexpected_error then
179 x_return_status := fnd_api.g_ret_sts_unexp_error;
180 --
181 when others then
182 x_return_status := fnd_api.g_ret_sts_unexp_error;
183
184 END insert_msnt;
185 --
186 -- Start of comments
187 -- Name : split_prefix_num
188 -- Function : Separates prefix and numeric part of a serial number
189 -- Pre-reqs : none
190 -- Parameters :
191 -- p_serial_number in varchar2
192 -- p_prefix in/out varchar2 the prefix
193 -- x_num out varchar2(30) the numeric portion
194 -- Notes : privat procedure for internal use only
195 -- needed only once serial numbers are supported
196 -- End of comments
197 --
198 PROCEDURE split_prefix_num
199 (
200 p_serial_number IN VARCHAR2
201 ,p_prefix IN OUT NOCOPY VARCHAR2
202 ,x_num OUT NOCOPY VARCHAR2
203 ) is
204 l_counter number;
205 BEGIN
206 IF p_prefix IS NOT NULL THEN
207 x_num := SUBSTR(p_serial_number,length(p_prefix)+1);
208 ELSE
209 l_counter := length(p_serial_number);
210 WHILE l_counter >= 0 AND SUBSTR(p_serial_number,l_counter,1) >= '0' AND
211 SUBSTR(p_serial_number,l_counter,1) <= '9'
212 LOOP
213 l_counter := l_counter - 1;
214 END LOOP;
215 IF l_counter = 0 THEN
216 p_prefix := NULL;
217 ELSE
218 p_prefix := SUBSTR(p_serial_number,1,l_counter);
219 END IF;
220 x_num := SUBSTR(p_serial_number,l_counter+1);
221 END IF;
222 END split_prefix_num;
223 --
224 -- For serial number support
225 FUNCTION subtract_serials
226 (
227 p_operand1 IN VARCHAR2,
228 p_operand2 IN VARCHAR2
229 ) RETURN NUMBER IS
230 l_prefix1 VARCHAR2(30);
231 l_prefix2 VARCHAR2(30);
232 l_num1 NUMBER;
233 l_num2 NUMBER;
234 l_return NUMBER;
235 BEGIN
236 split_prefix_num(p_operand1,l_prefix1,l_num1);
237 split_prefix_num(p_operand2,l_prefix2,l_num2);
238 IF l_prefix1 = l_prefix2
239 OR l_prefix1 IS NULL AND l_prefix2 IS NULL THEN
240 l_return := NVL(l_num2,0) - NVL(l_num1,0);
241 ELSE
242 l_return := 0;
243 END IF;
244 RETURN(l_return);
245 END subtract_serials;
246
247 FUNCTION get_item_name (p_item_id NUMBER)
248 RETURN VARCHAR2
249 IS
250 l_item_name VARCHAR2(50);
251
252 BEGIN
253 SELECT distinct concatenated_segments INTO l_item_name
254 FROM mtl_system_items_kfv
255 WHERE inventory_item_id = p_item_id;
256
257 RETURN l_item_name;
258 EXCEPTION
259 WHEN OTHERS THEN
260 RETURN NULL;
261 END get_item_name;
262
263
264
265 END csp_pp_util;