eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.INV_ONHAND_BALANCE

Source


1 package BODY inv_onhand_balance AS
2 /* $Header: INVEINVB.pls 120.0.12010000.6 2010/04/12 19:40:01 kdong noship $ */
3 
4 g_debug      NUMBER :=1;--   NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
8 |                                                                           |
5 
6 
7 /*===========================================================================
9 | PROCEDURE NAME   Raise_Event                                              |
10 |                                                                           |
11 | DESCRIPTION      This procedure raises an event in Work Flow.  It raises  |
12 |                  an appropriate procedure depending on the parameters     |
13 |                  passed.                                                  |
14 |                                                                           |                               |
15 |                                                                           |
16 ============================================================================*/
17 
18 PROCEDURE Raise_Event ( P_txn_hist_record  IN     INV_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type,
19                        P_xml_document_id   IN     NUMBER,
20                        x_return_status     IN OUT NOCOPY  VARCHAR2)
21 IS
22 
23   l_event_name VARCHAR2 (120);
24   l_Event_Key  VARCHAR2 (30);
25 
26   l_Return_Status    VARCHAR2 (1);
27   l_Transaction_Code VARCHAR2 (100);
28   l_Party_Site_ID    NUMBER;
29   l_txns_id          NUMBER;
30   l_xml_document_id  NUMBER;
31 
32   l_msg_parameter_list  WF_PARAMETER_LIST_T;
33   l_txn_hist_record INV_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
34 
35   l_wms_deployment_mode     varchar2(1);
36 
37   invalid_event_name  EXCEPTION;
38   update_history      EXCEPTION;
39 
40 BEGIN
41 
42   if (g_debug = 1) then
43     inv_trx_util_pub.TRACE('Entering Raise_Event', 'INV_ONHAND_BALANCE', 9);
44     inv_trx_util_pub.TRACE('transaction_id is ' || P_txn_hist_record.transaction_id, 'INV_ONHAND_BALANCE', 9);
45     inv_trx_util_pub.TRACE('transaction_status is ' || P_txn_hist_record.transaction_status, 'INV_ONHAND_BALANCE', 9);
46   end if;
47 
48   x_return_status := rcv_error_pkg.g_ret_sts_success;
49 
50   l_txn_hist_record := P_txn_hist_record;
51   l_xml_document_id := P_xml_document_id;
52 
53   -- Get the event name from the Transaction History Table.
54   l_event_name := l_txn_hist_record.Event_Name;
55 
56   -- Check if the event name is valid or not.
57   IF ( l_event_name NOT IN ('oracle.apps.inv.standalone.onhand') ) THEN
58      RAISE invalid_event_name;
59   END IF;
60 
61 
62   l_Transaction_Code := UPPER (SUBSTRB (l_event_name, INSTRB(l_Event_Name, '.', -1) + 1));
63 
64   if (g_debug = 1) then
65         inv_trx_util_pub.TRACE('l_transaction_code is '||l_transaction_code, 'INV_ONHAND_BALANCE', 9);
66   end if;
67 
68   l_Event_Key := l_txn_hist_record.Event_Key;
69   l_wms_deployment_mode := WMS_DEPLOY.WMS_DEPLOYMENT_MODE;
70 
71 
72   IF ( l_Transaction_Code in ('ONHAND') ) THEN --{
73      -- Generate the document number for outgoing documents.
74 
75 
76       if (g_debug = 1) then
77         inv_trx_util_pub.TRACE('trading_partner_id is '||P_txn_hist_record.trading_partner_id, 'INV_ONHAND_BALANCE', 9);
78       end if;
79 
80       IF (l_wms_deployment_mode = 'L') THEN
81 
82       SELECT trading_partner_site_id
83       INTO l_Party_Site_ID
84       FROM mtl_client_parameters
85       WHERE client_id IN (SELECT cust_account_id
86                           FROM hz_cust_accounts
87                           WHERE party_id = P_txn_hist_record.trading_partner_id);
88 
89 
90      ELSE
91 
92       l_Party_Site_ID  := P_txn_hist_record.trading_partner_id;
93 
94      END IF;
95 
96 
97       if (g_debug = 1) then
98         inv_trx_util_pub.TRACE('trading_partner_site_id is '||l_Party_Site_ID, 'INV_ONHAND_BALANCE', 9);
99       end if;
100 
101   END IF; --}
102 
103 
104 
105      WF_EVENT.AddParameterToList (p_name  => 'ECX_PARTY_ID',
106                                   p_value => l_txn_hist_record.Trading_Partner_ID,
107                                   p_parameterlist => l_msg_parameter_list);
108 
109      WF_EVENT.AddParameterToList (p_name  => 'ECX_PARTY_SITE_ID',
110                                   p_value => l_Party_Site_ID,
111                                   p_parameterlist => l_msg_parameter_list);
112 
113      IF ( l_wms_deployment_mode = 'L') then
114 
115        WF_EVENT.AddParameterToList (p_name  => 'ECX_PARTY_TYPE',
116                                     p_value => 'C',
117                                     p_parameterlist => l_msg_parameter_list);
118 
119        if (g_debug = 1) then
120          inv_trx_util_pub.TRACE('Party Type is C', 'INV_ONHAND_BALANCE', 9);
121        end if;
122 
123      ELSE
124 
125        WF_EVENT.AddParameterToList (p_name  => 'ECX_PARTY_TYPE',
126                                     p_value => 'I',
127                                     p_parameterlist => l_msg_parameter_list);
128 
129        if (g_debug = 1) then
130          inv_trx_util_pub.TRACE('Party Type is I', 'INV_ONHAND_BALANCE', 9);
131        end if;
132 
133      END IF;
134 
135      WF_EVENT.AddParameterToList (p_name  => 'ECX_DOCUMENT_ID',
136                                   p_value => l_xml_document_id, --l_txn_hist_record.Entity_Number, -- entity_id
137                                   p_parameterlist => l_msg_parameter_list);
138      if (g_debug = 1) then
139        inv_trx_util_pub.TRACE('Document ID is '|| l_txn_hist_record.Entity_Number, 'INV_ONHAND_BALANCE', 9);
140      end if;
141 
142 
143      WF_EVENT.AddParameterToList (p_name  => 'USER_ID',
144                            p_value => FND_GLOBAL.USER_ID,
145                            p_parameterlist => l_msg_parameter_list);
146 
147      if (g_debug = 1) then
148        inv_trx_util_pub.TRACE('User ID is '|| FND_GLOBAL.USER_ID, 'INV_ONHAND_BALANCE', 9);
149      end if;
150 
151 
152      WF_EVENT.AddParameterToList (p_name  => 'APPLICATION_ID',
153                            p_value => FND_GLOBAL.RESP_APPL_ID,
154                            p_parameterlist => l_msg_parameter_list);
155 
156      if (g_debug = 1) then
157        inv_trx_util_pub.TRACE('Responsibility Application ID is '|| FND_GLOBAL.RESP_APPL_ID, 'INV_ONHAND_BALANCE', 9);
158      end if;
159 
160 
161      WF_EVENT.AddParameterToList (p_name  => 'RESPONSIBILITY_ID',
162                            p_value => FND_GLOBAL.RESP_ID,
163                            p_parameterlist => l_msg_parameter_list);
164      if (g_debug = 1) then
165        inv_trx_util_pub.TRACE('Responsibility ID is '|| FND_GLOBAL.RESP_ID, 'INV_ONHAND_BALANCE', 9);
166      end if;
167 
168 
169      WF_EVENT.AddParameterToList (p_name  => 'ECX_TRANSACTION_TYPE',
170                                      p_value => 'INV',
171                                      p_parameterlist => l_msg_parameter_list);
172      if (g_debug = 1) then
173        inv_trx_util_pub.TRACE('Transaction Type is '|| 'INV', 'INV_ONHAND_BALANCE', 9);
174      end if;
175 
176 
177      IF ( l_wms_deployment_mode = 'L') then
178 
179         WF_EVENT.AddParameterToList (p_name  => 'ECX_TRANSACTION_SUBTYPE',
180                                      p_value => 'ONHAND',
181                                      p_parameterlist => l_msg_parameter_list);
182         if (g_debug = 1) then
183            inv_trx_util_pub.TRACE('Transaction SubType is '|| 'ONHAND', 'INV_ONHAND_BALANCE', 9);
184         end if;
185 
186      ELSE
187 
188         WF_EVENT.AddParameterToList (p_name  => 'ECX_TRANSACTION_SUBTYPE',
189                                      p_value => 'ONHAND-IN',
190                                      p_parameterlist => l_msg_parameter_list);
191         if (g_debug = 1) then
192            inv_trx_util_pub.TRACE('Transaction SubType is '|| 'ONHAND', 'INV_ONHAND_BALANCE', 9);
193         end if;
194 
195      END IF;
196 
197 
198      WF_EVENT.AddParameterToList (p_name  => 'USER',
199                                   p_value => FND_GLOBAL.user_name,
200                                   p_parameterlist => l_msg_parameter_list);
201      if (g_debug = 1) then
202        inv_trx_util_pub.TRACE('User_Name is '||FND_GLOBAL.user_name, 'INV_ONHAND_BALANCE', 9);
203      end if;
204 
205 
206      WF_EVENT.AddParameterToList (p_name  => 'ECX_PARAMETER1',
207                                   p_value => l_txn_hist_record.Entity_Number, --l_txn_hist_record.Action_Type,
208                                   p_parameterlist => l_msg_parameter_list);
209      if (g_debug = 1) then
210        inv_trx_util_pub.TRACE('ECX Parameter1 is '||l_txn_hist_record.Action_Type, 'INV_ONHAND_BALANCE', 9);
211      end if;
212 
213 
214      WF_EVENT.AddParameterToList (p_name  => 'ECX_PARAMETER2',
215                                   p_value => l_txn_hist_record.Client_Code,
216                                   p_parameterlist => l_msg_parameter_list);
217      if (g_debug = 1) then
218        inv_trx_util_pub.TRACE('ECX Parameter2 is '||l_txn_hist_record.Client_Code, 'INV_ONHAND_BALANCE', 9);
219      end if;
220 
221      INV_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
222                                                                 l_xml_document_id,
223                                                                 l_txns_id,
224                                                                 l_return_status );
225 
226 
227       if (g_debug = 1) then
228        inv_trx_util_pub.TRACE('l_txns_id is '||l_txns_id, 'INV_ONHAND_BALANCE', 9);
229        inv_trx_util_pub.TRACE('l_return_status is '||To_Char(l_Return_Status), 'INV_ONHAND_BALANCE', 9);
233    if (g_debug = 1) then
230       end if;
231 
232       IF ( l_Return_Status <> rcv_error_pkg.g_ret_sts_success ) THEN
234             inv_trx_util_pub.TRACE('Raise_Event.l_Return_Status is '|| l_Return_Status, 'INV_ONHAND_BALANCE', 9);
235           end if;
236           RAISE update_history;
237       END IF;
238 
239      -- Commit the data into the Transaction History table for the views.
240      COMMIT;
241 
242 
243   IF ( l_Transaction_Code IN ('ADJO', 'SHLO','ONHAND') ) THEN
244 
245       if (g_debug = 1) then
246        inv_trx_util_pub.TRACE('Raising Business Event', 'INV_ONHAND_BALANCE', 9);
247       end if;
248 
249       WF_EVENT.raise ( p_event_name => l_event_name,
250                        p_event_key  => l_Event_Key,
251                        p_parameters => l_msg_parameter_list );
252 
253       if (g_debug = 1) then
254        inv_trx_util_pub.TRACE('Completed the Business Event execution', 'INV_ONHAND_BALANCE', 9);
255       end if;
256 
257   END IF;
258 
259   if (g_debug = 1) then
260      inv_trx_util_pub.TRACE('Exiting Raise_Event', 'INV_ONHAND_BALANCE', 9);
261   end if;
262 
263 EXCEPTION
264   WHEN invalid_event_name THEN
265      x_return_status := rcv_error_pkg.g_ret_sts_error;
266      if (g_debug = 1) then
267        inv_trx_util_pub.TRACE('invalid_event_name exception has occured.', 'INV_ONHAND_BALANCE', 9);
268      end if;
269 
270   WHEN update_history THEN
271      x_return_status := rcv_error_pkg.g_ret_sts_error;
272      if (g_debug = 1) then
273        inv_trx_util_pub.TRACE('update_history exception has occured.', 'INV_ONHAND_BALANCE', 9);
274      end if;
275 
276   WHEN OTHERS THEN
277      x_return_status := rcv_error_pkg.g_ret_sts_unexp_error;
278 
279      if (g_debug = 1) then
280        inv_trx_util_pub.TRACE('Unexpected error has occured. Oracle error message is '|| SQLERRM, 'INV_ONHAND_BALANCE', 9);
281      end if;
282 
283 END Raise_Event;
284 
285 PROCEDURE Send_Onhand_Document( P_Entity_ID        IN  NUMBER,
286                                 P_Entity_Type      IN  VARCHAR2,
287                                 P_Action_Type      IN  VARCHAR2,
288                                 P_Document_Type    IN  VARCHAR2,
289                                 P_Org_ID           IN  NUMBER,
290                                 P_client_code      IN  VARCHAR2,
291                                 p_xml_document_id  IN  NUMBER,
292                                 X_Return_Status    OUT NOCOPY  VARCHAR2)
293    IS
294 
295       l_orig_Event_Key          VARCHAR2 (240);
296       l_curr_txn_hist_record    inv_transactions_history_pkg.Txns_History_Record_Type;
297       l_Return_Status           VARCHAR2 (1);
298       l_wms_deployment_mode     VARCHAR2(1);
299       l_party_id                NUMBER;
300       l_xml_document_id         NUMBER;
301 
302       invalid_entity_type        EXCEPTION;
303       invalid_action_type        EXCEPTION;
304       invalid_doc_type           EXCEPTION;
305       raise_event_error          EXCEPTION;
306 
307 BEGIN
308 
309 
310       IF (g_debug = 1) THEN
311          inv_trx_util_pub.TRACE('Entering Send_Onhand_Document', 'INV_ONHAND_BALANCE', 9);
312          inv_trx_util_pub.TRACE('Entity_ID is ' || P_Entity_ID, 'INV_ONHAND_BALANCE', 9);
313          inv_trx_util_pub.TRACE('Action Type is ' || P_Action_Type, 'INV_ONHAND_BALANCE', 9);
314          inv_trx_util_pub.TRACE('Doc Type is ' || P_Document_Type, 'INV_ONHAND_BALANCE', 9);
315          inv_trx_util_pub.TRACE('p_org_id is ' || P_Org_ID, 'INV_ONHAND_BALANCE', 9);
316          inv_trx_util_pub.TRACE('P_client_code is ' || P_client_code, 'INV_ONHAND_BALANCE', 9);
317          inv_trx_util_pub.TRACE('p_xml_document_id is ' || p_xml_document_id, 'INV_ONHAND_BALANCE', 9);
318 
319       END IF;
320 
321       X_Return_Status := rcv_error_pkg.g_ret_sts_success;
322       l_xml_document_id := P_xml_document_id;
323 
324       IF ( P_Entity_TYPE <> 'INVMOQD' ) THEN
325          RAISE invalid_entity_type;
326       ELSIF ( P_Action_TYPE <> 'A' ) THEN
327          RAISE invalid_action_type;
328       ELSIF ( P_Document_TYPE <> 'ONHAND' ) THEN
329          RAISE invalid_doc_type;
330       END IF;
331 
332       SELECT po_wf_itemkey_s.NEXTVAL
333       INTO   l_orig_Event_Key
334       FROM   DUAL;
335 
336       l_curr_txn_hist_record.Document_Type         := P_Document_Type;
337       l_curr_txn_hist_record.Document_Direction    := 'O';
338       l_curr_txn_hist_record.Entity_Number         := P_Entity_ID;
339       l_curr_txn_hist_record.Entity_Type           := P_Entity_TYPE;
340 
341       l_curr_txn_hist_record.Event_Name            := 'oracle.apps.inv.standalone.onhand';
342       l_curr_txn_hist_record.Item_Type             := 'INVMOQD';
343       l_curr_txn_hist_record.Event_Key             := l_orig_Event_Key;
344       l_curr_txn_hist_record.Action_Type           := P_Action_Type;
345       l_curr_txn_hist_record.Transaction_Status    := 'ST';
346       l_curr_txn_hist_record.Document_Number       := l_xml_document_id; --P_Entity_ID;
347 
348       l_wms_deployment_mode := wms_deploy.wms_deployment_mode;
349 
350       inv_trx_util_pub.TRACE('l_wms_deployment_mode ' || l_wms_deployment_mode, 'INV_ONHAND_BALANCE', 9);
351 
352       If (l_wms_deployment_mode = 'L') then
353 
354       l_curr_txn_hist_record.Client_Code           := P_client_code;
355 
356       SELECT party_id
357       INTO l_party_id
358       FROM hz_cust_accounts
359       WHERE cust_account_id IN (SELECT client_id
360                                 FROM mtl_client_parameters
361                                 WHERE client_code = P_client_code);
362       else
363 
367       where organization_id = P_Org_ID
364       select location_id
365       into l_party_id
366       from hr_organization_units_v
368       and rownum = 1;
369 
370       END If;
371 
372       l_curr_txn_hist_record.Trading_Partner_ID    := l_party_id;
373 
374 
375       IF (g_debug = 1) THEN
376          inv_trx_util_pub.TRACE('Item Type is ' || l_curr_txn_hist_record.Item_Type, 'INV_ONHAND_BALANCE', 9);
377          inv_trx_util_pub.TRACE('Event Name is ' || l_curr_txn_hist_record.Event_Name, 'INV_ONHAND_BALANCE', 9);
378          inv_trx_util_pub.TRACE('Event Key is ' || l_curr_txn_hist_record.Event_Key, 'INV_ONHAND_BALANCE', 9);
379          inv_trx_util_pub.TRACE('Trading Partner ID is ' || To_Char(l_curr_txn_hist_record.Trading_Partner_ID), 'INV_ONHAND_BALANCE', 9);
380          inv_trx_util_pub.TRACE('Document Type is ' || l_curr_txn_hist_record.Document_Type, 'INV_ONHAND_BALANCE', 9);
381          inv_trx_util_pub.TRACE('Document Direction is ' || l_curr_txn_hist_record.Document_Direction, 'INV_ONHAND_BALANCE', 9);
382          inv_trx_util_pub.TRACE('Document Number is ' || to_char(l_curr_txn_hist_record.Document_Number), 'INV_ONHAND_BALANCE', 9);
383       END IF;
384 
385 
386       /* Raise event will insert the record into the transaction history table
387          for the current transaction.
388       */
389 
390       Raise_Event ( l_curr_txn_hist_record,
391                     l_xml_document_id,
392                     l_Return_Status );
393 
394 
395       IF (g_debug = 1) THEN
396          inv_trx_util_pub.TRACE('Send_Receipt_Confirmation.l_Return_Status is '||l_Return_Status, 'INV_ONHAND_BALANCE', 9);
397          inv_trx_util_pub.TRACE('Exiting Send_Onhand_Document', 'INV_ONHAND_BALANCE', 9);
398 
399       END IF;
400 
401       IF (l_Return_Status <> rcv_error_pkg.g_ret_sts_success ) THEN
402          RAISE raise_event_error;
403       END IF;
404 
405    EXCEPTION
406 
407       WHEN invalid_entity_type THEN
408          X_Return_Status := rcv_error_pkg.g_ret_sts_error;
409          IF (g_debug = 1) THEN
410           inv_trx_util_pub.TRACE('invalid_entity_type exception has occured', 'INV_ONHAND_BALANCE', 9);
411          END IF;
412 
413       WHEN invalid_action_type THEN
414          X_Return_Status := rcv_error_pkg.g_ret_sts_error;
415          IF (g_debug = 1) THEN
416           inv_trx_util_pub.TRACE('invalid_action_type exception has occured', 'INV_ONHAND_BALANCE', 9);
417          END IF;
418 
419       WHEN invalid_doc_type THEN
420          X_Return_Status := rcv_error_pkg.g_ret_sts_error;
421          IF (g_debug = 1) THEN
422           inv_trx_util_pub.TRACE('invalid_doc_type exception has occured', 'INV_ONHAND_BALANCE', 9);
423          END IF;
424 
425       WHEN raise_event_error THEN
426          X_Return_Status := rcv_error_pkg.g_ret_sts_error;
427          IF (g_debug = 1) THEN
428           inv_trx_util_pub.TRACE('raise_event_error exception has occured, error message is '|| SQLERRM, 'INV_ONHAND_BALANCE', 9);
429          END IF;
430 
431       WHEN OTHERS THEN
432          X_Return_Status := rcv_error_pkg.g_ret_sts_error;
433          IF (g_debug = 1) THEN
434           inv_trx_util_pub.TRACE('Unexpected error has occured. Oracle error message is '|| SQLERRM, 'INV_ONHAND_BALANCE', 9);
435          END IF;
436 
437 END Send_Onhand_Document;
438 
439 
440 
441 PROCEDURE send_onhand (x_errbuf          OUT  NOCOPY VARCHAR2,
442                        x_retcode         OUT  NOCOPY NUMBER,
443                        p_org_id          IN NUMBER,
444                        p_deploy_mode     IN NUMBER DEFAULT null,
445                        p_client_code     IN VARCHAR2,
446                        p_warehouse_id    IN NUMBER,
447                        p_client          IN VARCHAR2,
448                        p_item_id         IN NUMBER,
449                        p_subinventory    IN VARCHAR2,
450                        p_locator         IN VARCHAR2,
451                        p_lot             IN VARCHAR2,
452                        p_grp             IN NUMBER DEFAULT  1,
453                        p_display_lot     IN NUMBER DEFAULT  2) IS
454 
455 l_return_status     VARCHAR2(1);
456 l_xml_doc_id        NUMBER;
457 l_entity_id         NUMBER;
458 l_org_id            NUMBER;
459 l_group_by          VARCHAR2(1000);
460 l_select_stmt       VARCHAR2(2000);
461 l_where_stmt        VARCHAR2(2000);
462 l_insert_stmt       VARCHAR2(2000);
463 l_stmt              varchar2(9000);
464 l_ret               BOOLEAN;
465 BEGIN
466 
467   if (g_debug = 1) then
468       inv_trx_util_pub.TRACE('Entering send_onhand', 'INV_ONHAND_BALANCE', 9);
469       inv_trx_util_pub.TRACE('p_org_id is '||p_org_id, 'INV_ONHAND_BALANCE', 9);
470       inv_trx_util_pub.TRACE('p_warehouse_id is '||p_warehouse_id, 'INV_ONHAND_BALANCE', 9);
471       inv_trx_util_pub.TRACE('p_deploy_mode is '||p_deploy_mode, 'INV_ONHAND_BALANCE', 9);
472       inv_trx_util_pub.TRACE('p_client_code is '||p_client_code, 'INV_ONHAND_BALANCE', 9);
473       inv_trx_util_pub.TRACE('p_item_id is ' ||p_item_id, 'INV_ONHAND_BALANCE', 9);
474       inv_trx_util_pub.TRACE('p_subinventory is ' ||p_subinventory, 'INV_ONHAND_BALANCE', 9);
475       inv_trx_util_pub.TRACE('p_locator is ' ||p_locator, 'INV_ONHAND_BALANCE', 9);
476       inv_trx_util_pub.TRACE('p_lot is ' ||p_lot, 'INV_ONHAND_BALANCE', 9);
477       inv_trx_util_pub.TRACE('p_grp is ' ||p_grp, 'INV_ONHAND_BALANCE', 9);
478       inv_trx_util_pub.TRACE('p_display_lot is ' ||p_display_lot, 'INV_ONHAND_BALANCE', 9);
479   end if;
480 
481 
482   x_errbuf := 'Success';
483   x_retcode := 0;
484 
485   --initialize
486   begin
487   delete from MTL_LSP_ONHAND_BALANCE_TMP;
491 
488   if (g_debug = 1) then
489      inv_trx_util_pub.TRACE('Initialize, rows deleted : '||sql%rowcount, 'INV_ONHAND_BALANCE', 9);
490   end if;
492   exception
493     when others then
494          if (g_debug = 1) then
495              inv_trx_util_pub.TRACE('Exception : '||sqlerrm||' occurred in delete_temp_table', 'INV_ONHAND_BALANCE', 9);
496          end if;
497          ROLLBACK;
498 
499   end;
500 
501   if p_warehouse_id is not null then
502      l_org_id := p_warehouse_id;
503   else
504      l_org_id := p_org_id;
505   end if;
506 
507   begin
508 
509     select  mtl_txns_history_s.nextval
510     into l_entity_id
511     from dual;
512 
513   exception
514     when others then
515       l_entity_id := -1;
516   end;
517 
518   l_xml_doc_id :=l_entity_id;
519 
520   if (g_debug = 1) then
521      inv_trx_util_pub.TRACE('l_entity_id is ' || l_entity_id, 'INV_ONHAND_BALANCE', 9);
522   end if;
523 
524   l_insert_stmt :='insert into MTL_LSP_ONHAND_BALANCE_TMP(ITEM_ID, ITEM, ITEM_DESCRIPTION, PRIMARY_UOM, PRIMARY_QUANTITY, SECONDARY_UOM, SECONDARY_QUANTITY, CATEGORY, CONTAINERIZED_FLAG, ONHAND_STATUS, SNAPSHOT_DATE, XML_DOCUMENT_ID ';
525   l_select_stmt :='select item_id, item, ITEM_DESCRIPTION, PRIMARY_UOM, sum(PRIMARY_QUANTITY), SECONDARY_UOM, sum(SECONDARY_QUANTITY), CATEGORY, CONTAINERIZED_FLAG, ONHAND_STATUS, SNAPSHOT_DATE, '||l_xml_doc_id;
526   l_where_stmt := 'where 1 = 1';
527   l_group_by :=' group by item_id, item, ITEM_DESCRIPTION, PRIMARY_UOM,SECONDARY_UOM, CATEGORY, CONTAINERIZED_FLAG, ONHAND_STATUS, SNAPSHOT_DATE  ';
528 
529   if p_item_id is not null then
530      l_where_stmt := l_where_stmt||' and item_id = '||p_item_id;
531   end if;
532 
533   if p_grp = 2 then
534    l_insert_stmt :=l_insert_stmt||',WAREHOUSE, WAREHOUSE_ID ';
535    l_select_stmt :=l_select_stmt||',WAREHOUSE, WAREHOUSE_ID ';
536    l_group_by :=l_group_by||',WAREHOUSE,  WAREHOUSE_ID ';
537   end if;
538 
539   if p_grp = 3 then
540    l_insert_stmt :=l_insert_stmt||',WAREHOUSE, WAREHOUSE_ID,SUBINVENTORY ';
541    l_select_stmt :=l_select_stmt||',WAREHOUSE, WAREHOUSE_ID,SUBINVENTORY ';
542    l_group_by :=l_group_by||',WAREHOUSE,  WAREHOUSE_ID, SUBINVENTORY ';
543   end if;
544 
545   if p_grp = 4 then
546    l_insert_stmt :=l_insert_stmt||',WAREHOUSE, WAREHOUSE_ID,SUBINVENTORY,LOCATOR ';
547    l_select_stmt :=l_select_stmt||',WAREHOUSE, WAREHOUSE_ID,SUBINVENTORY,LOCATOR ';
548    l_group_by :=l_group_by||',WAREHOUSE,  WAREHOUSE_ID,SUBINVENTORY,LOCATOR ';
549   end if;
550 
551   if p_display_lot = 1 then
552    l_insert_stmt :=l_insert_stmt||',LOT ';
553    l_select_stmt :=l_select_stmt||',LOT ';
554    l_group_by :=l_group_by||', LOT ';
555   end if;
556 
557   if p_warehouse_id is not null then
558     l_where_stmt :=l_where_stmt||' and WAREHOUSE_ID ='||p_warehouse_id;
559   end if;
560   if p_client_code is not null then
561     l_where_stmt :=l_where_stmt||' and wms_deploy.get_client_code(item_id) ='''||p_client_code||''' ';
562   end if;
563   if p_subinventory is not null then
564     l_where_stmt :=l_where_stmt||' and SUBINVENTORY = '''||p_subinventory||''' ';
565   end if;
566   if p_locator is not null then
567     l_where_stmt :=l_where_stmt||' and LOCATOR =(select concatenated_segments from mtl_item_locations_kfv where inventory_location_id='||p_locator||')';
568   end if;
569   if p_lot is not null then
570     l_where_stmt :=l_where_stmt||' and LOT = '''||p_lot||''' ';
571   end if;
572 
573   l_stmt := l_insert_stmt||') '||l_select_stmt||' from mtl_onhand_sync_v '||l_where_stmt||l_group_by;
574 
575   inv_trx_util_pub.TRACE('l_insert_stmt : '||l_insert_stmt, 'INV_ONHAND_BALANCE', 9);
576   inv_trx_util_pub.TRACE('l_select_stmt : '||l_select_stmt, 'INV_ONHAND_BALANCE', 9);
577   inv_trx_util_pub.TRACE('l_where_stmt : '||l_where_stmt, 'INV_ONHAND_BALANCE', 9);
578   inv_trx_util_pub.TRACE('l_group_by : '||l_group_by, 'INV_ONHAND_BALANCE', 9);
579 
580   EXECUTE IMMEDIATE l_stmt ;
581 
582 
583   if (g_debug = 1) then
584      inv_trx_util_pub.TRACE('Rows inserted : '||sql%rowcount, 'INV_ONHAND_BALANCE', 9);
585   end if;
586 
587   Send_Onhand_Document (P_Entity_ID        => l_entity_id,
588                         P_Entity_Type      => 'INVMOQD',
589                         P_Action_Type      => 'A',
590                         P_Document_Type    => 'ONHAND',
591                         P_Org_ID           => l_org_id,
592                         P_client_code      => p_client_code,
593                         p_xml_document_id  => l_xml_doc_id,
594                         X_Return_Status    => l_return_status );
595 
596   if (g_debug = 1) then
597      inv_trx_util_pub.TRACE('Send_Onhand_Document.l_return_status is ' || l_return_status, 'INV_ONHAND_BALANCE', 9);
598      inv_trx_util_pub.TRACE('Exiting Send_Document call', 'INV_ONHAND_BALANCE', 9);
599   end if;
600 
601   if l_return_status <> rcv_error_pkg.g_ret_sts_success then
602      l_ret :=fnd_concurrent.set_completion_status('ERROR', 'Error');
603   end if;
604 
605   COMMIT;
606 
607 EXCEPTION
608     WHEN OTHERS THEN
609 
610        if (g_debug = 1) then
611            inv_trx_util_pub.TRACE('Exception : '||sqlerrm||' occurred in Send_Onhand_Document', 'INV_ONHAND_BALANCE', 9);
612        end if;
613        ROLLBACK;
614 
615        l_ret :=fnd_concurrent.set_completion_status('ERROR', 'Error');
616 
617        x_errbuf := 'Error';
618        x_retcode := 2;
619 
620 END send_onhand;
621 
622 
623 
624 END inv_onhand_balance;