[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_ATTRIBUTE_SYNC_PVT
Source
1 PACKAGE BODY INV_LOT_ATTRIBUTE_SYNC_PVT AS
2 /* $Header: INVLSATTB.pls 120.3 2011/12/30 02:32:25 pdong noship $ */
3
4 PROCEDURE SYNCHRONIZE_LOTATT(p_group_id IN NUMBER,p_syndff_flag IN NUMBER) IS
5 l_org_id NUMBER;
6 l_item_id NUMBER;
7 l_lot_number VARCHAR2(80);
8 l_grade_code VARCHAR2(150);
9 l_exp_date DATE;
10 l_mat_date DATE;
11 l_orig_date DATE;
12 l_exp_action_date DATE;
13 l_exp_action_code VARCHAR2(40);
14 l_hold_date DATE;
15 l_retest_date DATE;
16 l_dest_org_id NUMBER;
17 l_dest_item_id NUMBER;
18 l_dest_lot_number VARCHAR2(80);
19 l_attribute_category VARCHAR2(30);
20 l_attribute1 VARCHAR2(150);
21 l_attribute2 VARCHAR2(150);
22 l_attribute3 VARCHAR2(150);
23 l_attribute4 VARCHAR2(150);
24 l_attribute5 VARCHAR2(150);
25 l_attribute6 VARCHAR2(150);
26 l_attribute7 VARCHAR2(150);
27 l_attribute8 VARCHAR2(150);
28 l_attribute9 VARCHAR2(150);
29 l_attribute10 VARCHAR2(150);
30 l_attribute11 VARCHAR2(150);
31 l_attribute12 VARCHAR2(150);
32 l_attribute13 VARCHAR2(150);
33 l_attribute14 VARCHAR2(150);
34 l_attribute15 VARCHAR2(150);
35 l_c_attribute1 VARCHAR2(150);
36 l_c_attribute2 VARCHAR2(150);
37 l_c_attribute3 VARCHAR2(150);
38 l_c_attribute4 VARCHAR2(150);
39 l_c_attribute5 VARCHAR2(150);
40 l_c_attribute6 VARCHAR2(150);
41 l_c_attribute7 VARCHAR2(150);
42 l_c_attribute8 VARCHAR2(150);
43 l_c_attribute9 VARCHAR2(150);
44 l_c_attribute10 VARCHAR2(150);
45 l_c_attribute11 VARCHAR2(150);
46 l_c_attribute12 VARCHAR2(150);
47 l_c_attribute13 VARCHAR2(150);
48 l_c_attribute14 VARCHAR2(150);
49 l_c_attribute15 VARCHAR2(150);
50 l_c_attribute16 VARCHAR2(150);
51 l_c_attribute17 VARCHAR2(150);
52 l_c_attribute18 VARCHAR2(150);
53 l_c_attribute19 VARCHAR2(150);
54 l_c_attribute20 VARCHAR2(150);
55 l_d_attribute1 date;
56 l_d_attribute2 date;
57 l_d_attribute3 date;
58 l_d_attribute4 date;
59 l_d_attribute5 date;
60 l_d_attribute6 date;
61 l_d_attribute7 date;
62 l_d_attribute8 date;
63 l_d_attribute9 date;
64 l_d_attribute10 date;
65 l_n_attribute1 number;
66 l_n_attribute2 number;
67 l_n_attribute3 number;
68 l_n_attribute4 number;
69 l_n_attribute5 number;
70 l_n_attribute6 number;
71 l_n_attribute7 number;
72 l_n_attribute8 number;
73 l_n_attribute9 number;
74 l_n_attribute10 number;
75 cursor c_source_lotatt IS
76 select organization_id,
77 inventory_item_id,
78 lot_number,
79 grade_code,
80 origination_date,
81 expiration_date,
82 maturity_date,
83 expiration_action_date,
84 expiration_action_code,
85 hold_date,
86 retest_date,
87 attribute_category,
88 attribute1,
89 attribute2,
90 attribute3,
91 attribute4,
92 attribute5,
93 attribute6,
94 attribute7,
95 attribute8,
96 attribute9,
97 attribute10,
98 attribute11,
99 attribute12,
100 attribute13,
101 attribute14,
102 attribute15,
103 c_attribute1,
104 c_attribute2,
105 c_attribute3,
106 c_attribute4,
107 c_attribute5,
108 c_attribute6,
109 c_attribute7,
110 c_attribute8,
111 c_attribute9,
112 c_attribute10,
113 c_attribute11,
114 c_attribute12,
115 c_attribute13,
116 c_attribute14,
117 c_attribute15,
118 c_attribute16,
119 c_attribute17,
120 c_attribute18,
121 c_attribute19,
122 c_attribute20,
123 d_attribute1,
124 d_attribute2,
125 d_attribute3,
126 d_attribute4,
127 d_attribute5,
128 d_attribute6,
129 d_attribute7,
130 d_attribute8,
131 d_attribute9,
132 d_attribute10,
133 n_attribute1,
134 n_attribute2,
135 n_attribute3,
136 n_attribute4,
137 n_attribute5,
138 n_attribute6,
139 n_attribute7,
140 n_attribute8,
141 n_attribute9,
142 n_attribute10
143 from MTL_LOTATT_SYNC_REPORT_TEMP
144 where flag = 1
145 and group_id = p_group_id;
146
147 cursor c_dest_lotatt(p_item_id number,p_lot_number varchar) IS
148 select organization_id,
149 inventory_item_id,
150 lot_number
151 from MTL_LOTATT_SYNC_REPORT_TEMP
152 where lot_number = p_lot_number
153 and inventory_item_id = p_item_id
154 and flag = 2
155 and group_id = p_group_id;
156
157 BEGIN
158 --open source cursor
159 open c_source_lotatt;
160 loop
161 fetch c_source_lotatt into l_org_id,
162 l_item_id,
163 l_lot_number,
164 l_grade_code,
165 l_orig_date,
166 l_exp_date,
167 l_mat_date,
168 l_exp_action_date,
169 l_exp_action_code,
170 l_hold_date,
171 l_retest_date,
172 l_attribute_category,
173 l_attribute1,
174 l_attribute2,
175 l_attribute3,
176 l_attribute4,
177 l_attribute5,
178 l_attribute6,
179 l_attribute7,
180 l_attribute8,
181 l_attribute9,
182 l_attribute10,
183 l_attribute11,
184 l_attribute12,
185 l_attribute13,
186 l_attribute14,
187 l_attribute15,
188 l_c_attribute1,
189 l_c_attribute2,
190 l_c_attribute3,
191 l_c_attribute4,
192 l_c_attribute5,
193 l_c_attribute6,
194 l_c_attribute7,
195 l_c_attribute8,
196 l_c_attribute9,
197 l_c_attribute10,
198 l_c_attribute11,
199 l_c_attribute12,
200 l_c_attribute13,
201 l_c_attribute14,
202 l_c_attribute15,
203 l_c_attribute16,
204 l_c_attribute17,
205 l_c_attribute18,
206 l_c_attribute19,
207 l_c_attribute20,
208 l_d_attribute1,
209 l_d_attribute2,
210 l_d_attribute3,
211 l_d_attribute4,
212 l_d_attribute5,
213 l_d_attribute6,
214 l_d_attribute7,
215 l_d_attribute8,
216 l_d_attribute9,
217 l_d_attribute10,
218 l_n_attribute1,
219 l_n_attribute2,
220 l_n_attribute3,
221 l_n_attribute4,
222 l_n_attribute5,
223 l_n_attribute6,
224 l_n_attribute7,
225 l_n_attribute8,
226 l_n_attribute9,
227 l_n_attribute10;
228
229 EXIT WHEN c_source_lotatt%NOTFOUND;
230 open c_dest_lotatt(l_item_id,l_lot_number);
231 loop
232 fetch c_dest_lotatt into l_dest_org_id,
233 l_dest_item_id,
234 l_dest_lot_number;
235 EXIT WHEN c_dest_lotatt%NOTFOUND;
236 --update MLN against MTL_LOTATT_SYNC_REPORT_TMP from source to dest
237 IF(nvl(p_syndff_flag,2) = 2) THEN --syn lot dff attributes when the flag enabled
238 update mtl_lot_numbers
239 set grade_code = l_grade_code,
240 origination_date = l_orig_date,
241 expiration_date = l_exp_date,
242 maturity_date = l_mat_date,
243 expiration_action_date = l_exp_action_date,
244 expiration_action_code = l_exp_action_code,
245 hold_date = l_hold_date,
246 retest_date = l_retest_date
247 where organization_id = l_dest_org_id
248 and inventory_item_id = l_dest_item_id
249 and lot_number = l_dest_lot_number;
250 ELSE
251 update mtl_lot_numbers
252 set grade_code = l_grade_code,
253 origination_date = l_orig_date,
254 expiration_date = l_exp_date,
255 maturity_date = l_mat_date,
256 expiration_action_date = l_exp_action_date,
257 expiration_action_code = l_exp_action_code,
258 hold_date = l_hold_date,
259 retest_date = l_retest_date,
260 attribute_category = l_attribute_category,
261 attribute1 = l_attribute1,
262 attribute2 = l_attribute2,
263 attribute3 = l_attribute3,
264 attribute4 = l_attribute4,
265 attribute5 = l_attribute5,
266 attribute6 = l_attribute6,
267 attribute7 = l_attribute7,
268 attribute8 = l_attribute8,
269 attribute9 = l_attribute9,
270 attribute10 = l_attribute10,
271 attribute11 = l_attribute11,
272 attribute12 = l_attribute12,
273 attribute13 = l_attribute13,
274 attribute14 = l_attribute14,
275 attribute15 = l_attribute15,
276 c_attribute1 = l_c_attribute1,
277 c_attribute2 = l_c_attribute2,
278 c_attribute3 = l_c_attribute3,
279 c_attribute4 = l_c_attribute4,
280 c_attribute5 = l_c_attribute5,
281 c_attribute6 = l_c_attribute6,
282 c_attribute7 = l_c_attribute7,
283 c_attribute8 = l_c_attribute8,
284 c_attribute9 = l_c_attribute9,
285 c_attribute10 = l_c_attribute10,
286 c_attribute11 = l_c_attribute11,
287 c_attribute12 = l_c_attribute12,
288 c_attribute13 = l_c_attribute13,
289 c_attribute14 = l_c_attribute14,
290 c_attribute15 = l_c_attribute15,
291 c_attribute16 = l_c_attribute16,
292 c_attribute17 = l_c_attribute17,
293 c_attribute18 = l_c_attribute18,
294 c_attribute19 = l_c_attribute19,
295 c_attribute20 = l_c_attribute20,
296 d_attribute1 = l_d_attribute1,
297 d_attribute2 = l_d_attribute2,
298 d_attribute3 = l_d_attribute3,
299 d_attribute4 = l_d_attribute4,
300 d_attribute5 = l_d_attribute5,
301 d_attribute6 = l_d_attribute6,
302 d_attribute7 = l_d_attribute7,
303 d_attribute8 = l_d_attribute8,
304 d_attribute9 = l_d_attribute9,
305 d_attribute10 = l_d_attribute10,
306 n_attribute1 = l_n_attribute1,
307 n_attribute2 = l_n_attribute2,
308 n_attribute3 = l_n_attribute3,
309 n_attribute4 = l_n_attribute4,
310 n_attribute5 = l_n_attribute5,
311 n_attribute6 = l_n_attribute6,
312 n_attribute7 = l_n_attribute7,
313 n_attribute8 = l_n_attribute8,
314 n_attribute9 = l_n_attribute9,
315 n_attribute10 = l_n_attribute10
316 where organization_id = l_dest_org_id
317 and inventory_item_id = l_dest_item_id
318 and lot_number = l_dest_lot_number;
319
320 END IF;
321 end loop;
322 CLOSE c_dest_lotatt;
323 end loop;
324 CLOSE c_source_lotatt;
325 EXCEPTION
326 WHEN OTHERS THEN
327 IF c_dest_lotatt%ISOPEN THEN
328 CLOSE c_dest_lotatt;
329 END IF;
330 IF c_source_lotatt%ISOPEN THEN
331 CLOSE c_source_lotatt;
332 END IF;
333 END SYNCHRONIZE_LOTATT;
334 END INV_LOT_ATTRIBUTE_SYNC_PVT;
335