[Home] [Help]
PACKAGE BODY: APPS.INV_INVENTORY_ADJUSTMENT
Source
1 package BODY inv_inventory_adjustment AS
2 /* $Header: INVADJTB.pls 120.0.12010000.6 2010/02/26 20:09:52 musinha noship $ */
3
4 g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5
6
7 PROCEDURE send_adjustment (x_errbuf OUT NOCOPY VARCHAR2,
8 x_retcode OUT NOCOPY NUMBER,
9 p_deploy_mode IN NUMBER DEFAULT null,
10 p_client_code IN VARCHAR2,
11 p_client IN VARCHAR2,
12 p_org_id IN NUMBER,
13 p_trx_date_from IN VARCHAR2 DEFAULT null,
14 p_trx_date_to IN VARCHAR2 DEFAULT null,
15 p_trx_type IN VARCHAR2 DEFAULT null,
16 p_xml_doc_id IN NUMBER DEFAULT null) IS
17
18 l_wf_item_seq NUMBER;
19 l_event_name VARCHAR2(100);
20 l_event_key VARCHAR2(100);
21 l_parameter_list1 wf_parameter_list_t := wf_parameter_list_t();
22 l_return_status VARCHAR2(1);
23 temp_txn_num NUMBER := NULL;
24 xml_doc_id NUMBER := NULL;
25 temp_trx_date_from DATE;
26 trx_date_from DATE;
27 trx_date_to DATE;
28 l_txn_type_id NUMBER;
29 l_entity_id NUMBER;
30 l_dummy NUMBER := 0;
31
32
33 BEGIN
34
35 if (g_debug = 1) then
36 inv_trx_util_pub.TRACE('Entering send_adjustment', 'INV_INVENTORY_ADJUSTMENT', 9);
37 inv_trx_util_pub.TRACE('p_org_id is '||p_org_id, 'INV_INVENTORY_ADJUSTMENT', 9);
38 inv_trx_util_pub.TRACE('p_client_code is '||p_client_code, 'INV_INVENTORY_ADJUSTMENT', 9);
39 inv_trx_util_pub.TRACE('p_xml_doc_id is ' ||p_xml_doc_id, 'INV_INVENTORY_ADJUSTMENT', 9);
40 inv_trx_util_pub.TRACE('p_trx_type is ' ||p_trx_type, 'INV_INVENTORY_ADJUSTMENT', 9);
41 end if;
42
43
44 x_errbuf := 'Success';
45 x_retcode := 0;
46
47
48 IF p_xml_doc_id IS NOT NULL THEN
49 xml_doc_id := p_xml_doc_id;
50 end if;
51
52 -- Convert the transaction_type to transaction_type_id
53 IF p_trx_type IS NOT NULL THEN
54
55 begin
56 select transaction_type_id
57 into l_txn_type_id
58 from mtl_transaction_types
59 where transaction_type_name = p_trx_type;
60 exception
61 when others then
62 l_txn_type_id := null;
63 end;
64
65 END IF;
66
67 if (g_debug = 1) then
68 inv_trx_util_pub.TRACE('l_txn_type_id is ' || l_txn_type_id, 'INV_INVENTORY_ADJUSTMENT', 9);
69 end if;
70
71 -- trx_date_from := FND_DATE.Canonical_To_Date(p_trx_date_from);
72 -- trx_date_to := FND_DATE.Canonical_To_Date(p_trx_date_to);
73
74
75 IF (p_trx_date_from IS NULL) or (p_trx_date_to IS NULL) THEN
76 if (g_debug = 1) then
77 inv_trx_util_pub.TRACE('transaction date range is not provided.', 'INV_INVENTORY_ADJUSTMENT', 9);
78 end if;
79
80 x_errbuf := 'Transaction date range is not provided.';
81 x_retcode := 2;
82
83 return;
84 END IF;
85
86
87 trx_date_from := FND_DATE.Canonical_To_Date(p_trx_date_from);
88 trx_date_to := FND_DATE.Canonical_To_Date(p_trx_date_to);
89
90 trx_date_from := trunc(trx_date_from);
91 trx_date_to := trunc(trx_date_to);
92
93 if (g_debug = 1) then
94 inv_trx_util_pub.TRACE('From Trxn Date is ' ||trx_date_from, 'INV_INVENTORY_ADJUSTMENT', 9);
95 inv_trx_util_pub.TRACE('To Trxn Date ' || trx_date_to, 'INV_INVENTORY_ADJUSTMENT', 9);
96 inv_trx_util_pub.TRACE('xml_doc_id is '||xml_doc_id, 'INV_INVENTORY_ADJUSTMENT', 9);
97 end if;
98
99
100 l_event_name := 'oracle.apps.inv.standalone.adjo';
101
102 select mtl_txns_history_s.nextval
103 into l_entity_id
104 from dual;
105
106
107 if (g_debug = 1) then
108 inv_trx_util_pub.TRACE('l_entity_id is ' || l_entity_id, 'INV_INVENTORY_ADJUSTMENT', 9);
109 end if;
110
111 IF ( p_client_code is not null ) THEN
112
113 if (g_debug = 1) then
114 inv_trx_util_pub.TRACE('Client code is not null', 'INV_INVENTORY_ADJUSTMENT', 9);
115 end if;
116
117 IF (p_xml_doc_id is null) THEN
118
119 if (g_debug = 1) then
120 inv_trx_util_pub.TRACE('xml_doc_id is null', 'INV_INVENTORY_ADJUSTMENT', 9);
121 inv_trx_util_pub.TRACE('Inserting into mtl_adjustment_sync_temp', 'INV_INVENTORY_ADJUSTMENT', 9);
122 end if;
123
124 insert into mtl_adjustment_sync_temp
125 (TRANSACTION_NUMBER,
126 TRANSACTION_DATE,
127 CATEGORY,
128 CATEGORY_ID,
129 WAREHOUSE,
130 ORGANIZATION_ID,
131 ITEM,
132 ITEM_DESCRIPTION,
133 INVENTORY_ITEM_ID,
134 REVISION,
135 SUBINVENTORY,
136 LOCATOR,
137 TRANSFER_WAREHOUSE,
138 TRANSFER_SUBINVENTORY,
139 TRANSFER_LOCATOR,
140 LPN,
141 TRANSFER_LPN,
142 CONTENT_LPN,
143 TRANSACTION_TYPE,
144 TRANSACTION_TYPE_ID,
145 TRANSACTION_SOURCE_TYPE_ID,
146 TRANSACTION_ACTION_ID,
147 TRANSACTION_SOURCE,
148 CREATION_DATE,
149 TRANSACTION_EXTRACTED,
150 XML_DOCUMENT_ID,
151 TRANSACTION_QUANTITY,
152 TRANSACTION_UOM,
153 PRIMARY_QUANTITY,
154 PRIMARY_UOM,
155 SECONDARY_QUANTITY,
156 SECONDARY_UOM,
157 ENTITY_ID)
158 select
159 TRANSACTION_NUMBER,
160 TRANSACTION_DATE,
161 CATEGORY,
162 CATEGORY_ID,
163 WAREHOUSE,
164 ORGANIZATION_ID,
165 WMS_DEPLOY.GET_CLIENT_ITEM(ORGANIZATION_ID,INVENTORY_ITEM_ID) ITEM,
166 ITEM_DESCRIPTION,
167 INVENTORY_ITEM_ID,
168 REVISION,
169 SUBINVENTORY,
170 LOCATOR,
171 TRANSFER_WAREHOUSE,
172 TRANSFER_SUBINVENTORY,
173 TRANSFER_LOCATOR,
174 LPN,
175 TRANSFER_LPN,
176 CONTENT_LPN,
177 TRANSACTION_TYPE,
178 TRANSACTION_TYPE_ID,
179 TRANSACTION_SOURCE_TYPE_ID,
180 TRANSACTION_ACTION_ID,
181 TRANSACTION_SOURCE,
182 CREATION_DATE,
183 TRANSACTION_EXTRACTED,
184 XML_DOCUMENT_ID,
185 TRANSACTION_QUANTITY,
186 TRANSACTION_UOM,
187 PRIMARY_QUANTITY,
188 PRIMARY_UOM,
189 SECONDARY_QUANTITY,
190 SECONDARY_UOM,
191 l_entity_id
192 from mtl_adj_sync_wrapper_v
193 where organization_id = p_org_id
194 AND wms_deploy.get_client_code(inventory_item_id) = p_client_code
195 AND NVL(transaction_extracted, 'N') NOT IN ('Y','P')
196 AND transaction_type_id = nvl(l_txn_type_id, transaction_type_id)
197 AND transaction_date >= trx_date_from
198 AND transaction_date < trx_date_to + 1; -- Added 1 as the input dates are truncated.
199
200 if (g_debug = 1) then
201 inv_trx_util_pub.TRACE('no of rows inserted: '||SQL%ROWCOUNT, 'INV_INVENTORY_ADJUSTMENT', 9);
202 end if;
203
204 ELSE
205
206 if (g_debug = 1) then
207 inv_trx_util_pub.TRACE('xml_doc_id is not null', 'INV_INVENTORY_ADJUSTMENT', 9);
208 inv_trx_util_pub.TRACE('Inserting into mtl_adjustment_sync_temp', 'INV_INVENTORY_ADJUSTMENT', 9);
209 end if;
210
211 insert into mtl_adjustment_sync_temp
212 (TRANSACTION_NUMBER,
213 TRANSACTION_DATE,
214 CATEGORY,
215 CATEGORY_ID,
216 WAREHOUSE,
217 ORGANIZATION_ID,
218 ITEM,
219 ITEM_DESCRIPTION,
220 INVENTORY_ITEM_ID,
221 REVISION,
222 SUBINVENTORY,
223 LOCATOR,
224 TRANSFER_WAREHOUSE,
225 TRANSFER_SUBINVENTORY,
226 TRANSFER_LOCATOR,
227 LPN,
228 TRANSFER_LPN,
229 CONTENT_LPN,
230 TRANSACTION_TYPE,
231 TRANSACTION_TYPE_ID,
232 TRANSACTION_SOURCE_TYPE_ID,
233 TRANSACTION_ACTION_ID,
234 TRANSACTION_SOURCE,
235 CREATION_DATE,
236 TRANSACTION_EXTRACTED,
237 XML_DOCUMENT_ID,
238 TRANSACTION_QUANTITY,
239 TRANSACTION_UOM,
240 PRIMARY_QUANTITY,
241 PRIMARY_UOM,
242 SECONDARY_QUANTITY,
243 SECONDARY_UOM,
244 ENTITY_ID)
245 select
246 TRANSACTION_NUMBER,
247 TRANSACTION_DATE,
248 CATEGORY,
249 CATEGORY_ID,
250 WAREHOUSE,
251 ORGANIZATION_ID,
252 WMS_DEPLOY.GET_CLIENT_ITEM(ORGANIZATION_ID,INVENTORY_ITEM_ID) ITEM,
253 ITEM_DESCRIPTION,
254 INVENTORY_ITEM_ID,
255 REVISION,
256 SUBINVENTORY,
257 LOCATOR,
258 TRANSFER_WAREHOUSE,
259 TRANSFER_SUBINVENTORY,
260 TRANSFER_LOCATOR,
261 LPN,
262 TRANSFER_LPN,
263 CONTENT_LPN,
264 TRANSACTION_TYPE,
265 TRANSACTION_TYPE_ID,
266 TRANSACTION_SOURCE_TYPE_ID,
267 TRANSACTION_ACTION_ID,
268 TRANSACTION_SOURCE,
269 CREATION_DATE,
270 TRANSACTION_EXTRACTED,
271 XML_DOCUMENT_ID,
272 TRANSACTION_QUANTITY,
273 TRANSACTION_UOM,
274 PRIMARY_QUANTITY,
275 PRIMARY_UOM,
276 SECONDARY_QUANTITY,
277 SECONDARY_UOM,
278 l_entity_id
279 from mtl_adj_sync_wrapper_v
280 where organization_id = p_org_id
281 AND wms_deploy.get_client_code(inventory_item_id) = p_client_code
282 AND xml_document_id = p_xml_doc_id
283 AND NVL(transaction_extracted, 'N') IN ('Y');
284 --If xml_document_id is not null then other parameters are irrelevant
285 --AND transaction_type_id = nvl(l_txn_type_id, transaction_type_id);
286 --AND transaction_date >= trx_date_from
287 --AND transaction_date < trx_date_to + 1;
288
289 if (g_debug = 1) then
290 inv_trx_util_pub.TRACE('no of rows inserted: '||SQL%ROWCOUNT, 'INV_INVENTORY_ADJUSTMENT', 9);
291 end if;
292
293 END IF;
294
295 ELSE
296
297 if (g_debug = 1) then
298 inv_trx_util_pub.TRACE('Client code is null', 'INV_INVENTORY_ADJUSTMENT', 9);
299 end if;
300
301 IF (p_xml_doc_id is null) THEN
302
303 if (g_debug = 1) then
304 inv_trx_util_pub.TRACE('xml_doc_id is null', 'INV_INVENTORY_ADJUSTMENT', 9);
305 inv_trx_util_pub.TRACE('Inserting into mtl_adjustment_sync_temp', 'INV_INVENTORY_ADJUSTMENT', 9);
306 end if;
307
308 insert into mtl_adjustment_sync_temp
309 (TRANSACTION_NUMBER,
310 TRANSACTION_DATE,
311 CATEGORY,
312 CATEGORY_ID,
313 WAREHOUSE,
314 ORGANIZATION_ID,
315 ITEM,
316 ITEM_DESCRIPTION,
317 INVENTORY_ITEM_ID,
318 REVISION,
319 SUBINVENTORY,
320 LOCATOR,
321 TRANSFER_WAREHOUSE,
322 TRANSFER_SUBINVENTORY,
323 TRANSFER_LOCATOR,
324 LPN,
325 TRANSFER_LPN,
326 CONTENT_LPN,
327 TRANSACTION_TYPE,
328 TRANSACTION_TYPE_ID,
329 TRANSACTION_SOURCE_TYPE_ID,
330 TRANSACTION_ACTION_ID,
331 TRANSACTION_SOURCE,
332 CREATION_DATE,
333 TRANSACTION_EXTRACTED,
334 XML_DOCUMENT_ID,
335 TRANSACTION_QUANTITY,
336 TRANSACTION_UOM,
337 PRIMARY_QUANTITY,
338 PRIMARY_UOM,
339 SECONDARY_QUANTITY,
340 SECONDARY_UOM,
341 ENTITY_ID)
342 select
343 TRANSACTION_NUMBER,
344 TRANSACTION_DATE,
345 CATEGORY,
346 CATEGORY_ID,
347 WAREHOUSE,
348 ORGANIZATION_ID,
349 WMS_DEPLOY.GET_CLIENT_ITEM(ORGANIZATION_ID,INVENTORY_ITEM_ID) ITEM,
350 ITEM_DESCRIPTION,
351 INVENTORY_ITEM_ID,
352 REVISION,
353 SUBINVENTORY,
354 LOCATOR,
355 TRANSFER_WAREHOUSE,
356 TRANSFER_SUBINVENTORY,
357 TRANSFER_LOCATOR,
358 LPN,
359 TRANSFER_LPN,
360 CONTENT_LPN,
361 TRANSACTION_TYPE,
362 TRANSACTION_TYPE_ID,
363 TRANSACTION_SOURCE_TYPE_ID,
364 TRANSACTION_ACTION_ID,
365 TRANSACTION_SOURCE,
366 CREATION_DATE,
367 TRANSACTION_EXTRACTED,
368 XML_DOCUMENT_ID,
369 TRANSACTION_QUANTITY,
370 TRANSACTION_UOM,
371 PRIMARY_QUANTITY,
372 PRIMARY_UOM,
373 SECONDARY_QUANTITY,
374 SECONDARY_UOM,
375 l_entity_id
376 from mtl_adj_sync_wrapper_v
377 where organization_id = p_org_id
378 AND NVL(transaction_extracted, 'N') NOT IN ('Y','P')
379 AND transaction_type_id = nvl(l_txn_type_id, transaction_type_id)
380 AND transaction_date >= trx_date_from
381 AND transaction_date < trx_date_to + 1;
382
383 if (g_debug = 1) then
384 inv_trx_util_pub.TRACE('no of rows inserted: '||SQL%ROWCOUNT, 'INV_INVENTORY_ADJUSTMENT', 9);
385 end if;
386
387 ELSE
388
389 if (g_debug = 1) then
390 inv_trx_util_pub.TRACE('xml_doc_id is not null', 'INV_INVENTORY_ADJUSTMENT', 9);
391 inv_trx_util_pub.TRACE('Inserting into mtl_adjustment_sync_temp', 'INV_INVENTORY_ADJUSTMENT', 9);
392 end if;
393
394 insert into mtl_adjustment_sync_temp
395 (TRANSACTION_NUMBER,
396 TRANSACTION_DATE,
397 CATEGORY,
398 CATEGORY_ID,
399 WAREHOUSE,
400 ORGANIZATION_ID,
401 ITEM,
402 ITEM_DESCRIPTION,
403 INVENTORY_ITEM_ID,
404 REVISION,
405 SUBINVENTORY,
406 LOCATOR,
407 TRANSFER_WAREHOUSE,
408 TRANSFER_SUBINVENTORY,
409 TRANSFER_LOCATOR,
410 LPN,
411 TRANSFER_LPN,
412 CONTENT_LPN,
413 TRANSACTION_TYPE,
414 TRANSACTION_TYPE_ID,
415 TRANSACTION_SOURCE_TYPE_ID,
416 TRANSACTION_ACTION_ID,
417 TRANSACTION_SOURCE,
418 CREATION_DATE,
419 TRANSACTION_EXTRACTED,
420 XML_DOCUMENT_ID,
421 TRANSACTION_QUANTITY,
422 TRANSACTION_UOM,
423 PRIMARY_QUANTITY,
424 PRIMARY_UOM,
425 SECONDARY_QUANTITY,
426 SECONDARY_UOM,
427 ENTITY_ID)
428 select
429 TRANSACTION_NUMBER,
430 TRANSACTION_DATE,
431 CATEGORY,
432 CATEGORY_ID,
433 WAREHOUSE,
434 ORGANIZATION_ID,
435 WMS_DEPLOY.GET_CLIENT_ITEM(ORGANIZATION_ID,INVENTORY_ITEM_ID) ITEM,
436 ITEM_DESCRIPTION,
437 INVENTORY_ITEM_ID,
438 REVISION,
439 SUBINVENTORY,
440 LOCATOR,
441 TRANSFER_WAREHOUSE,
442 TRANSFER_SUBINVENTORY,
443 TRANSFER_LOCATOR,
444 LPN,
445 TRANSFER_LPN,
446 CONTENT_LPN,
447 TRANSACTION_TYPE,
448 TRANSACTION_TYPE_ID,
449 TRANSACTION_SOURCE_TYPE_ID,
450 TRANSACTION_ACTION_ID,
451 TRANSACTION_SOURCE,
452 CREATION_DATE,
453 TRANSACTION_EXTRACTED,
454 XML_DOCUMENT_ID,
455 TRANSACTION_QUANTITY,
456 TRANSACTION_UOM,
457 PRIMARY_QUANTITY,
458 PRIMARY_UOM,
459 SECONDARY_QUANTITY,
460 SECONDARY_UOM,
461 l_entity_id
462 from mtl_adj_sync_wrapper_v
463 where organization_id = p_org_id
464 AND xml_document_id = p_xml_doc_id
465 AND NVL(transaction_extracted, 'N') IN ('Y');
466 --If xml_document_id is not null then other parameters are irrelevant
467 --AND transaction_type_id = nvl(l_txn_type_id, transaction_type_id)
468 --AND transaction_date >= trx_date_from
469 --AND transaction_date < trx_date_to + 1;
470
471 if (g_debug = 1) then
472 inv_trx_util_pub.TRACE('no of rows inserted: '||SQL%ROWCOUNT, 'INV_INVENTORY_ADJUSTMENT', 9);
473 end if;
474
475 END IF;
476
477 END IF;
478
479 COMMIT;
480
481 IF (xml_doc_id is null) THEN
482
483
484 if (g_debug = 1) then
485 inv_trx_util_pub.TRACE('Updating mmt.transaction_extracted flag to P', 'INV_INVENTORY_ADJUSTMENT', 9);
486 end if;
487
488 UPDATE mtl_material_transactions
489 SET transaction_extracted = 'P'
490 WHERE organization_id = p_org_id
491 AND NVL(transaction_extracted, 'N') NOT IN ( 'Y', 'P')
492 AND xml_document_id IS NULL
493 AND transaction_id IN (select transaction_number
494 from mtl_adjustment_sync_temp
495 where entity_id = l_entity_id);
496
497
498 COMMIT;
499
500
501 END IF;
502
503
504 INV_TRANSACTIONS_UTIL2.Send_Document(
505 p_entity_id => l_entity_id,
506 p_entity_type => 'INVADJ',
507 p_action_type => 'A',
508 p_document_type => 'ADJ',
509 p_organization_id => p_org_id,
510 p_client_code => p_client_code,
511 p_xml_document_id => xml_doc_id,
512 x_return_status => l_return_status);
513
514 if (g_debug = 1) then
515 inv_trx_util_pub.TRACE('Send_Document.l_return_status is ' || l_return_status, 'INV_INVENTORY_ADJUSTMENT', 9);
516 inv_trx_util_pub.TRACE('Exiting Send_Document call', 'INV_INVENTORY_ADJUSTMENT', 9);
517 end if;
518
519 IF (l_return_status <> rcv_error_pkg.g_ret_sts_success) THEN
520
521 IF (xml_doc_id is null) THEN
522
523 if (g_debug = 1) then
524 inv_trx_util_pub.TRACE('send_document failed', 'INV_INVENTORY_ADJUSTMENT', 9);
525 inv_trx_util_pub.TRACE('Updating mmt.transaction_extracted flag to null', 'INV_INVENTORY_ADJUSTMENT', 9);
526 end if;
527
528 UPDATE mtl_material_transactions
529 SET transaction_extracted = null,
530 xml_document_id = null
531 WHERE organization_id = p_org_id
532 AND NVL(transaction_extracted, 'N') = 'P'
533 AND xml_document_id IS NULL
534 AND transaction_id IN (select transaction_number
535 from mtl_adjustment_sync_temp
536 where entity_id = l_entity_id);
537
538 END IF;
539
540 delete_temp_table(l_entity_id);
541
542 COMMIT;
543
544 END IF;
545
546
547 if (g_debug = 1) then
548 inv_trx_util_pub.TRACE('Exit Loop', 'INV_INVENTORY_ADJUSTMENT', 9);
549 inv_trx_util_pub.TRACE('Exiting send_adjustment', 'INV_INVENTORY_ADJUSTMENT', 9);
550 end if;
551
552 COMMIT;
553
554 EXCEPTION
555 WHEN OTHERS THEN
556
557 if (g_debug = 1) then
558 inv_trx_util_pub.TRACE('Exception : '||sqlerrm||' occurred in Send_Adjustment', 'INV_INVENTORY_ADJUSTMENT', 9);
559 end if;
560 ROLLBACK;
561
562 if(l_entity_id is not null) then
563 delete_temp_table(l_entity_id);
564 end if;
565
566 x_errbuf := 'Error';
567 x_retcode := 2;
568
569 END send_adjustment;
570
571 PROCEDURE delete_temp_table (p_entity_id NUMBER) IS
572
573 BEGIN
574
575 if (p_entity_id is not null) then
576
577 if (g_debug = 1) then
578 inv_trx_util_pub.TRACE('deleting the temp table for entity_id: '||p_entity_id, 'INV_INVENTORY_ADJUSTMENT', 9);
579 end if;
580
581 delete from mtl_adjustment_sync_temp
582 where entity_id = p_entity_id;
583
584 commit;
585
586 end if;
587
588 EXCEPTION
589
590 WHEN OTHERS THEN
591 if (g_debug = 1) then
592 inv_trx_util_pub.TRACE('Exception : '||sqlerrm||' occurred in delete_temp_table', 'INV_INVENTORY_ADJUSTMENT', 9);
593 end if;
594 ROLLBACK;
595
596 END delete_temp_table;
597
598 END inv_inventory_adjustment;
599