[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_GRP
Source
1 PACKAGE body INV_DIAG_GRP AS
2 /* $Header: INVDGTPB.pls 120.1 2011/08/11 19:28:00 cbyrappa ship $ */
3 FUNCTION CHECK_AVAIL(p_Inventory_item_id Number
4 ,p_Organization_id Number
5 ,p_revision Varchar2
6 ,p_Subinventory_code Varchar2
7 ,p_locator_id Number
8 ) return NUMBER IS
9 L_api_return_status VARCHAR2(1);
10 l_qty_oh NUMBER;
11 l_qty_res_oh NUMBER;
12 l_qty_res NUMBER;
13 l_qty_sug NUMBER;
14 l_qty_att NUMBER;
15 l_qty_atr NUMBER;
16 l_msg_count NUMBER;
17 l_msg_data VARCHAR2(1000);
18 subinventory_code VARCHAR2(13);
19 revision varchar2(100);
20 locator_id Number;
21 is_revision_control boolean:=true;
22 QATT NUmber;
23
24 BEGIN
25 inv_quantity_tree_grp.clear_quantity_cache;
26
27 if p_revision is null then
28 revision:=null;
29 is_revision_control:=false;
30 else
31 revision := p_revision;
32 end if;
33
34 if p_subinventory_code is NULL then
35 subinventory_code:=null;
36 locator_id:=null;
37 else
38 subinventory_code:=p_subinventory_code;
39 locator_id:=p_locator_id;
40 end if;
41
42 if p_locator_id is NULL then
43 locator_id:=null;
44 else
45 locator_id:=p_locator_id;
46 end if;
47
48 /* BUG 12846683
49 * Removed the reference to "apps." to eliminate "INV_DIAG_GRP objects invalid"
50 * error during a POST upgrade from 11.5.10.2 to 12.1.3
51 */
52
53 INV_Quantity_Tree_PUB.Query_Quantities ( -- BUG 12846683
54 p_api_version_number => 1.0
55 , p_init_msg_lst => fnd_api.g_false -- BUG 12846683
56 , x_return_status => L_api_return_status
57 , x_msg_count => l_msg_count
58 , x_msg_data => l_msg_data
59 , p_organization_id => p_organization_id
60 , p_inventory_item_id => p_inventory_item_id
61 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode -- BUG 12846683
62 , p_onhand_source => NULL
63 , p_is_revision_control=> is_revision_control
64 , p_is_lot_control => FALSE
65 , p_is_serial_control => FALSE
66 , p_revision => revision
67 , p_lot_number => NULL
68 , p_subinventory_code => subinventory_code
69 , p_locator_id => locator_id
70 , x_qoh => l_qty_oh
71 , x_rqoh => l_qty_res_oh
72 , x_qr => l_qty_res
73 , x_qs => l_qty_sug
74 , x_att => l_qty_att
75 , x_atr => l_qty_atr );
76
77 if L_api_return_status <> fnd_api.g_ret_sts_success then
81 end if;
78 QATT:=0;
79 else
80 QATT:=l_qty_att;
82 return QATT;
83
84 end;
85
86 FUNCTION CHECK_ONHAND(p_Inventory_item_id Number
87 ,p_Organization_id Number
88 ,p_revision Varchar2
89 ,p_Subinventory_code Varchar2
90 ,p_locator_id Number
91 ) return NUMBER IS
92 L_api_return_status VARCHAR2(1);
93 l_qty_oh NUMBER;
94 l_qty_res_oh NUMBER;
95 l_qty_res NUMBER;
96 l_qty_sug NUMBER;
97 l_qty_att NUMBER;
98 l_qty_atr NUMBER;
99 l_msg_count NUMBER;
100 l_msg_data VARCHAR2(1000);
101 subinventory_code VARCHAR2(13);
102 revision varchar2(100);
103 locator_id Number;
104 is_revision_control boolean:=true;
105 QOH NUmber;
106
107 BEGIN
108 inv_quantity_tree_grp.clear_quantity_cache;
109
110 if p_revision is null then
111 revision:=null;
112 is_revision_control:=false;
113 else
114 revision := p_revision;
115 end if;
116
117 if p_subinventory_code is NULL then
118 subinventory_code:=null;
119 locator_id:=null;
120 else
121 subinventory_code:=p_subinventory_code;
122 locator_id:=p_locator_id;
123 end if;
124
125 if p_locator_id is NULL then
126 locator_id:=null;
127 else
128 locator_id:=p_locator_id;
129 end if;
130
131 INV_Quantity_Tree_PUB.Query_Quantities ( -- BUG 12846683
132 p_api_version_number => 1.0
133 , p_init_msg_lst => fnd_api.g_false -- BUG 12846683
134 , x_return_status => L_api_return_status
135 , x_msg_count => l_msg_count
136 , x_msg_data => l_msg_data
137 , p_organization_id => p_organization_id
138 , p_inventory_item_id => p_inventory_item_id
139 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode -- BUG 12846683
140 , p_onhand_source => NULL
141 , p_is_revision_control=> is_revision_control
142 , p_is_lot_control => FALSE
143 , p_is_serial_control => FALSE
144 , p_revision => revision
145 , p_lot_number => NULL
146 , p_subinventory_code => subinventory_code
147 , p_locator_id => locator_id
148 , x_qoh => l_qty_oh
149 , x_rqoh => l_qty_res_oh
150 , x_qr => l_qty_res
151 , x_qs => l_qty_sug
152 , x_att => l_qty_att
153 , x_atr => l_qty_atr );
154
155 if L_api_return_status <> fnd_api.g_ret_sts_success then
156 QOH:=0;
157 else
158 QOH:=l_qty_oh;
159 end if;
160 return QOH;
161
162 end;
163
164 -- to check if user has valid responsibility
165
166 FUNCTION check_responsibility(p_responsibility_name in FND_RESPONSIBILITY_TL.Responsibility_Name%type) return BOOLEAN is
167 l_dummy number;
168 begin
169
170 select count(*)
171 into l_dummy
172 from fnd_user_resp_groups fg, fnd_responsibility_vl fr
173 where fg.RESPONSIBILITY_ID=fr.RESPONSIBILITY_ID
174 and nvl(fr.START_DATE, sysdate) <= sysdate
175 and nvl(fr.END_DATE, sysdate) >= sysdate
176 and nvl(fg.START_DATE, sysdate) <=sysdate
177 and nvl(fg.END_DATE, sysdate) >=sysdate
178 and fr.RESPONSIBILITY_NAME = p_responsibility_name
179 and user_id=fnd_global.user_id;
180
181 if l_dummy >=1 then
182 return TRUE;
183 else
184 return FALSE;
185 end if;
186
187 exception when no_data_found then
188 return FALSE ;
189 end;
190
191 end;