[Home] [Help]
PACKAGE BODY: APPS.INV_INVLTATT_XMLP_PKG
Source
1 PACKAGE BODY INV_INVLTATT_XMLP_PKG AS
2 /* $Header: INVLTATTB.pls 120.1 2012/01/05 10:18:16 pdong noship $ */
3
4 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
5 BEGIN
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 EXCEPTION
9 WHEN OTHERS THEN
10 RAISE;
11 END;
12 --populate temp table
13 POPULATE_SYNC_REPORT_TMP;
14 RETURN (TRUE);
15 END BEFOREREPORT;
16
17 FUNCTION AFTERREPORT RETURN BOOLEAN IS
18 BEGIN
19 BEGIN
20 DELETE MTL_LOTATT_SYNC_REPORT_TEMP WHERE group_id=P_CONC_REQUEST_ID;
21 END;
22 RETURN (TRUE);
23 END AFTERREPORT;
24
25 FUNCTION AFTERFORM RETURN BOOLEAN IS
26 BEGIN
27 BEGIN
28 --get src organization code
29 SELECT organization_code
30 into P_SRC_ORG_CODE
31 FROM mtl_parameters
32 WHERE organization_id = P_SOURCE_ORG;
33
34 /*if the range of items are different, the range of lots will be ignored,
35 all available lot numbers will to be updated.*/
36 IF (nvl(P_ITEM_LO,'@@X@@') <> nvl(P_ITEM_HI,'@@X@@')) THEN
37 P_LOT_LO := null;
38 P_LOT_HI := null;
39 END IF;
40
41 --get dest organization code
42 IF(nvl(P_DEST_ORG_LO,-1) <> -1) THEN
43 select organization_code
44 into P_DEST_ORG_CODE_LO
45 from mtl_parameters
46 where organization_id = P_DEST_ORG_LO;
47 END IF;
48 IF(nvl(P_DEST_ORG_HI,-1) <> -1) THEN
49 select organization_code
50 into P_DEST_ORG_CODE_HI
51 from mtl_parameters
52 where organization_id = P_DEST_ORG_HI;
53 END IF;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 RETURN(FALSE);
58 END;
59 RETURN (TRUE);
60 END AFTERFORM;
61
62 PROCEDURE POPULATE_SYNC_REPORT_TMP IS
63 BEGIN
64 --starting insert into the synchornized tmp table
65 --populate the lot numbers from source organization
66 INSERT INTO MTL_LOTATT_SYNC_REPORT_TEMP
67 (organization_id,
68 inventory_item_id,
69 group_id,
70 lot_number,
71 grade_code,
72 origination_date,
73 expiration_date,
74 maturity_date,
75 expiration_action_date,
76 expiration_action_code,
77 hold_date,
78 retest_date,
79 flag,
80 attribute_category,
81 attribute1,
82 attribute2,
83 attribute3,
84 attribute4,
85 attribute5,
86 attribute6,
87 attribute7,
88 attribute8,
89 attribute9,
90 attribute10,
91 attribute11,
92 attribute12,
93 attribute13,
94 attribute14,
95 attribute15,
96 c_attribute1,
97 c_attribute2,
98 c_attribute3,
99 c_attribute4,
100 c_attribute5,
101 c_attribute6,
102 c_attribute7,
103 c_attribute8,
104 c_attribute9,
105 c_attribute10,
106 c_attribute11,
107 c_attribute12,
108 c_attribute13,
109 c_attribute14,
110 c_attribute15,
111 c_attribute16,
112 c_attribute17,
113 c_attribute18,
114 c_attribute19,
115 c_attribute20,
116 d_attribute1,
117 d_attribute2,
118 d_attribute3,
119 d_attribute4,
120 d_attribute5,
121 d_attribute6,
122 d_attribute7,
123 d_attribute8,
124 d_attribute9,
125 d_attribute10,
126 n_attribute1,
127 n_attribute2,
128 n_attribute3,
129 n_attribute4,
130 n_attribute5,
131 n_attribute6,
132 n_attribute7,
133 n_attribute8,
134 n_attribute9,
135 n_attribute10)
136 SELECT organization_id,
137 inventory_item_id,
138 P_CONC_REQUEST_ID,--using request id as groupid
139 lot_number,
140 grade_code,
141 origination_date,
142 expiration_date,
143 maturity_date,
144 expiration_action_date,
145 expiration_action_code,
146 hold_date,
147 retest_date,
148 1,
149 attribute_category,
150 attribute1,
151 attribute2,
152 attribute3,
153 attribute4,
154 attribute5,
155 attribute6,
156 attribute7,
157 attribute8,
158 attribute9,
159 attribute10,
160 attribute11,
161 attribute12,
162 attribute13,
163 attribute14,
164 attribute15,
165 c_attribute1,
166 c_attribute2,
167 c_attribute3,
168 c_attribute4,
169 c_attribute5,
170 c_attribute6,
171 c_attribute7,
172 c_attribute8,
173 c_attribute9,
174 c_attribute10,
175 c_attribute11,
176 c_attribute12,
177 c_attribute13,
178 c_attribute14,
179 c_attribute15,
180 c_attribute16,
181 c_attribute17,
182 c_attribute18,
183 c_attribute19,
184 c_attribute20,
185 d_attribute1,
186 d_attribute2,
187 d_attribute3,
188 d_attribute4,
189 d_attribute5,
190 d_attribute6,
191 d_attribute7,
192 d_attribute8,
193 d_attribute9,
194 d_attribute10,
195 n_attribute1,
196 n_attribute2,
197 n_attribute3,
198 n_attribute4,
199 n_attribute5,
200 n_attribute6,
201 n_attribute7,
202 n_attribute8,
203 n_attribute9,
204 n_attribute10
205 FROM mtl_lot_numbers mln
206 WHERE organization_id=P_SOURCE_ORG
207 AND mln.lot_number >= nvl(P_LOT_LO,mln.lot_number)
208 AND mln.lot_number <= nvl(P_LOT_HI,mln.lot_number)
209 /*Item should be lot controlled,
210 Lot should exists in source and destination */
211 AND inventory_item_id in(select inventory_item_id
212 from mtl_system_items_b
213 where (organization_id = P_SOURCE_ORG)
214 and (segment1 >= nvl(P_ITEM_LO,segment1) and segment1<=nvl(P_ITEM_HI,segment1))
215 and (lot_control_code = 2))
216 AND exists(select 'x' from mtl_lot_numbers mln1,mtl_parameters mp
217 where mln1.organization_id = mp.organization_id
218 and mp.organization_id <> P_SOURCE_ORG
219 and mp.organization_code >= P_DEST_ORG_CODE_LO
220 and mp.organization_code <= nvl(P_DEST_ORG_CODE_HI,mp.organization_code)
221 and mln1.lot_number = mln.lot_number
222 and mln1.inventory_item_id = mln.inventory_item_id)
223 /*Skip the lot if pending transaction exists */
224 AND not exists(select 'x'
225 from mtl_material_transactions_temp mmtt,
226 mtl_transaction_lots_temp mtlt
227 where mmtt.transaction_temp_id = mtlt.transaction_temp_id
228 and mmtt.organization_id = mln.organization_id
229 and mmtt.inventory_item_id = mln.inventory_item_id
230 and mtlt.lot_number = mln.lot_number)
231 AND not exists(select 'x'
232 from mtl_transactions_interface mti,
233 mtl_transaction_lots_interface mtli
234 where mti.transaction_interface_id = mtli.transaction_interface_id
235 and mti.organization_id = mln.organization_id
236 and mti.inventory_item_id = mln.inventory_item_id
237 and mtli.lot_number = mln.lot_number)
238 /*Skip the lot if reservation exists */
239 AND not exists(select 'x' from mtl_reservations mr
240 where mr.organization_id = mln.organization_id
241 and mr.inventory_item_id = mln.inventory_item_id
242 and mr.lot_number = mln.lot_number);
243 --populate the lot numbers from dest organization
244 INSERT INTO MTL_LOTATT_SYNC_REPORT_TEMP
245 (organization_id,
246 inventory_item_id,
247 group_id,
248 lot_number,
249 grade_code,
250 origination_date,
251 expiration_date,
252 maturity_date,
253 expiration_action_date,
254 expiration_action_code,
255 hold_date,
256 retest_date,
257 flag,
258 attribute_category,
259 attribute1,
260 attribute2,
261 attribute3,
262 attribute4,
263 attribute5,
264 attribute6,
265 attribute7,
266 attribute8,
267 attribute9,
268 attribute10,
269 attribute11,
270 attribute12,
271 attribute13,
272 attribute14,
273 attribute15,
274 c_attribute1,
275 c_attribute2,
276 c_attribute3,
277 c_attribute4,
278 c_attribute5,
279 c_attribute6,
280 c_attribute7,
281 c_attribute8,
282 c_attribute9,
283 c_attribute10,
284 c_attribute11,
285 c_attribute12,
286 c_attribute13,
287 c_attribute14,
288 c_attribute15,
289 c_attribute16,
290 c_attribute17,
291 c_attribute18,
292 c_attribute19,
293 c_attribute20,
294 d_attribute1,
295 d_attribute2,
296 d_attribute3,
297 d_attribute4,
298 d_attribute5,
299 d_attribute6,
300 d_attribute7,
301 d_attribute8,
302 d_attribute9,
303 d_attribute10,
304 n_attribute1,
305 n_attribute2,
306 n_attribute3,
307 n_attribute4,
308 n_attribute5,
309 n_attribute6,
310 n_attribute7,
311 n_attribute8,
312 n_attribute9,
313 n_attribute10)
314 SELECT mln.organization_id,
315 mln.inventory_item_id,
316 P_CONC_REQUEST_ID,--using request id as group id
317 mln.lot_number,
318 mln.grade_code,
319 mln.origination_date,
320 mln.expiration_date,
321 mln.maturity_date,
322 mln.expiration_action_date,
323 mln.expiration_action_code,
324 mln.hold_date,
325 mln.retest_date,
326 2,
327 mln.attribute_category,
328 mln.attribute1,
329 mln.attribute2,
330 mln.attribute3,
331 mln.attribute4,
332 mln.attribute5,
333 mln.attribute6,
334 mln.attribute7,
335 mln.attribute8,
336 mln.attribute9,
337 mln.attribute10,
338 mln.attribute11,
339 mln.attribute12,
340 mln.attribute13,
341 mln.attribute14,
342 mln.attribute15,
343 mln.c_attribute1,
344 mln.c_attribute2,
345 mln.c_attribute3,
346 mln.c_attribute4,
347 mln.c_attribute5,
348 mln.c_attribute6,
349 mln.c_attribute7,
350 mln.c_attribute8,
351 mln.c_attribute9,
352 mln.c_attribute10,
353 mln.c_attribute11,
354 mln.c_attribute12,
355 mln.c_attribute13,
356 mln.c_attribute14,
357 mln.c_attribute15,
358 mln.c_attribute16,
359 mln.c_attribute17,
360 mln.c_attribute18,
361 mln.c_attribute19,
362 mln.c_attribute20,
363 mln.d_attribute1,
364 mln.d_attribute2,
365 mln.d_attribute3,
366 mln.d_attribute4,
367 mln.d_attribute5,
368 mln.d_attribute6,
369 mln.d_attribute7,
370 mln.d_attribute8,
371 mln.d_attribute9,
372 mln.d_attribute10,
373 mln.n_attribute1,
374 mln.n_attribute2,
375 mln.n_attribute3,
376 mln.n_attribute4,
377 mln.n_attribute5,
378 mln.n_attribute6,
379 mln.n_attribute7,
380 mln.n_attribute8,
381 mln.n_attribute9,
382 mln.n_attribute10
383 FROM mtl_lot_numbers mln,mtl_parameters mp
384 WHERE mln.organization_id = mp.organization_id
385 AND mp.organization_id <> P_SOURCE_ORG
386 AND mp.organization_code >= P_DEST_ORG_CODE_LO
387 AND mp.organization_code <= nvl(P_DEST_ORG_CODE_HI,mp.organization_code)
388 AND mln.lot_number >= nvl(P_LOT_LO,mln.lot_number)
389 AND mln.lot_number <= nvl(P_LOT_HI,mln.lot_number)
390 /*Item should be lot controlled,
391 Lot should exists in source and destination */
392 AND inventory_item_id in(select inventory_item_id
393 from mtl_system_items_b msib,mtl_parameters mp
394 where msib.organization_id = mp.organization_id
395 and mp.organization_id <> P_SOURCE_ORG
396 and mp.organization_code >= P_DEST_ORG_CODE_LO
397 and mp.organization_code <= nvl(P_DEST_ORG_CODE_HI,mp.organization_code)
398 and (msib.segment1 >= nvl(P_ITEM_LO,msib.segment1) and msib.segment1<=nvl(P_ITEM_HI,msib.segment1))
399 and msib.lot_control_code = 2)
400 AND exists(select 'x' from mtl_lot_numbers mln1
401 where mln1.organization_id = P_SOURCE_ORG
402 and mln1.lot_number = mln.lot_number
403 and mln1.inventory_item_id = mln.inventory_item_id)
404 /*Skip the lot if pending transaction exists */
405 AND not exists(select 'x'
406 from mtl_material_transactions_temp mmtt,
407 mtl_transaction_lots_temp mtlt
408 where mmtt.transaction_temp_id = mtlt.transaction_temp_id
409 and mmtt.organization_id = mln.organization_id
410 and mmtt.inventory_item_id = mln.inventory_item_id
411 and mtlt.lot_number = mln.lot_number)
412 AND not exists(select 'x'
413 from mtl_transactions_interface mti,
414 mtl_transaction_lots_interface mtli
415 where mti.transaction_interface_id = mtli.transaction_interface_id
416 and mti.organization_id = mln.organization_id
417 and mti.inventory_item_id = mln.inventory_item_id
418 and mtli.lot_number = mln.lot_number)
419 /*Skip the lot if reservation exists */
420 AND not exists(select 'x' from mtl_reservations mr
421 where mr.organization_id = mln.organization_id
422 and mr.inventory_item_id = mln.inventory_item_id
423 and mr.lot_number = mln.lot_number);
424 --if commit Yes,Update MLN
425 IF(nvl(P_COMMIT_FLAG,2) = 1) THEN
426 INV_LOT_ATTRIBUTE_SYNC_PVT.SYNCHRONIZE_LOTATT(P_CONC_REQUEST_ID,P_SYNCDFF_FLAG);
427 END IF;
428
429 END POPULATE_SYNC_REPORT_TMP;
430
431 END INV_INVLTATT_XMLP_PKG;
432