[Home] [Help]
PACKAGE BODY: APPS.INV_UPDATE_ONHAND_STATUS
Source
1 PACKAGE BODY INV_UPDATE_ONHAND_STATUS AS
2 /* $Header: INVONSUB.pls 120.4 2008/03/18 06:55:47 aambulka noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_UPDATE_ONHAND_STATUS';
5 g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6
7 PROCEDURE print_debug(msg VARCHAR2) IS
8 BEGIN
9 IF (g_debug = 1) THEN
10 INV_LOG_UTIL.TRACE(msg,g_pkg_name);
11 END IF;
12 END print_debug;
13
14
15 PROCEDURE update_onhand_status(
16 x_errbuf OUT NOCOPY VARCHAR2
17 ,x_retcode OUT NOCOPY NUMBER
18 ,p_from_org_code IN VARCHAR2
19 ,p_to_org_code IN VARCHAR2
20 ,p_default_status IN VARCHAR2 ) IS
21
22 l_ret BOOLEAN ;
23
24 l_bulk_limit NUMBER := 5000;
25
26 TYPE rowidtab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
27 rowid_list rowidtab;
28
29 TYPE orgidtab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
30 orgid_list orgidtab;
31
32 l_default_status_id NUMBER := -1;
33 l_count NUMBER := 0;
34 l_record_count NUMBER := 0;
35
36 CURSOR c_moqd(org_id IN NUMBER, l_bulk_limit IN NUMBER) IS
37 SELECT rowid FROM mtl_onhand_quantities_detail moqd
38 WHERE moqd.organization_id = org_id
39 AND moqd.status_id is NULL
40 AND rownum < l_bulk_limit
41 AND EXISTS(
42 select 1 from mtl_system_items msi
43 WHERE moqd.inventory_item_id = msi.inventory_item_id
44 AND moqd.organization_id = msi.organization_id
45 AND msi.serial_number_control_code in (1,6)
46 )
47 ORDER BY subinventory_code
48 FOR UPDATE NOWAIT ;
49
50 CURSOR c_org_id (l_from_org_code IN VARCHAR2 ,l_to_org_code IN VARCHAR2) IS
51 SELECT organization_id FROM mtl_parameters
52 WHERE organization_code BETWEEN l_from_org_code AND l_to_org_code ;
53
54
55 BEGIN
56
57 print_debug('From Organization Code = '|| p_from_org_code );
58 print_debug('To Organization Code = '|| p_to_org_code );
59 print_debug('Default Status Code = '|| p_default_status );
60
61 BEGIN
62 orgid_list.DELETE;
63 OPEN c_org_id(p_from_org_code,p_to_org_code);
64 FETCH c_org_id bulk collect INTO orgid_list ;
65
66 --FETCH c_org_id bulk collect INTO orgid_list LIMIT l_bulk_limit;
67 IF (orgid_list.Count = 0) THEN
68 print_debug('No organization selected for given range. Please choose valid range');
69 l_ret := fnd_concurrent.set_completion_status('ERROR', 'Error');
70
71 x_retcode := 2;
72 x_errbuf := 'Error';
73 RETURN ;
74 END IF ;
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 print_debug('Error in getting organization ids');
79 print_debug('Error :'||substr(sqlerrm, 1, 200));
80
81 IF c_org_id%ISOPEN THEN
82 CLOSE c_org_id;
83 END IF;
84
85 l_ret := fnd_concurrent.set_completion_status('ERROR', 'Error');
86
87 x_retcode := 2;
88 x_errbuf := 'Error';
89
90 RETURN ;
91 END ;
92
93 -- Updating onhand_flag of individual material statuses
94 BEGIN
95 -- Initialize RETCODE
96 x_retcode := 0;
97
98 -- Updating onhand_flag of individual material statuses
99
100 UPDATE mtl_material_statuses_b
101 SET onhand_control = 1
102 WHERE Nvl(onhand_control,2) <> 1;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 print_debug('Error updating the material statues');
107 print_debug('Error :'||substr(sqlerrm, 1, 200));
108
109 l_ret := fnd_concurrent.set_completion_status('ERROR', 'Error');
110
111 x_retcode := 2;
112 x_errbuf := 'Error';
113 RETURN ;
114 END ;
115
116
117 FOR i IN orgid_list.first..orgid_list.last LOOP
118
119 print_debug('Running for OrganizationId =' || orgid_list(i));
120
121 SELECT NVL(default_status_id, -1)
122 INTO l_default_status_id
123 FROM mtl_parameters
124 WHERE organization_id = orgid_list(i);
125
126 IF (l_default_status_id = -1) THEN
127
128 l_record_count := 0;
129
130 LOOP
131 rowid_list.DELETE;
132 OPEN c_moqd(orgid_list(i),l_bulk_limit);
133 EXIT WHEN c_moqd%notfound;
134
135 FETCH c_moqd BULK COLLECT INTO rowid_list LIMIT l_bulk_limit;
136
137 IF rowid_list.first IS NULL THEN
138 print_debug('No more onhand records to be updated for orgid');
139 EXIT;
140 END IF;
141
142 FORALL j in rowid_list.first..rowid_list.last
143 UPDATE mtl_onhand_quantities_detail moqd
144 SET status_id = inv_material_status_grp.get_default_status_conc(moqd.organization_id,
145 moqd.inventory_item_id,
146 moqd.subinventory_code,
147 moqd.locator_id,
148 moqd.lot_number,
149 moqd.lpn_id)
150 WHERE rowid = rowid_list(j);
151
152 l_record_count := l_record_count + SQL%ROWCOUNT;
153 COMMIT;
154 CLOSE c_moqd;
155
156 END LOOP;
157 IF c_moqd%ISOPEN THEN
158 CLOSE c_moqd;
159 END IF;
160
161 print_debug('Updated :'||l_record_count||' rows in MOQD for orgid = ' || orgid_list(i));
162
163 --BEGIN
164 SELECT Count(1)
165 INTO l_count
166 FROM mtl_onhand_quantities_detail moqd, mtl_system_items_b msi
167 WHERE moqd.inventory_item_id = msi.inventory_item_id
168 AND moqd.organization_id = msi.organization_id
169 AND moqd.organization_id = orgid_list(i)
170 AND msi.serial_number_control_code in (1,6)
171 AND moqd.status_id is null
172 AND rownum = 1;
173
174 IF (l_count = 0) THEN
175
176 UPDATE mtl_parameters
177 SET default_status_id = (SELECT status_id
178 FROM mtl_material_statuses_vl
179 WHERE status_code = p_default_status)
180 WHERE organization_id = orgid_list(i);
181 print_debug('Updated Default Material Status at organization level for org: '|| orgid_list(i));
182 ELSE
183 print_debug('Some onhand records do not have status_id populated');
184 END IF ;
185
186 ELSE
187 print_debug('Organization is already onhand status enabled');
188 END IF;
189
190 END LOOP;
191
192 l_ret := fnd_concurrent.set_completion_status('NORMAL', 'Success');
193
194 x_retcode := 0;
195 x_errbuf := 'Success';
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 print_debug('Error :'||substr(sqlerrm, 1, 200));
200
201 IF c_moqd%ISOPEN THEN
202 CLOSE c_moqd;
203 END IF;
204
205 l_ret := fnd_concurrent.set_completion_status('ERROR', 'Error');
206
207 x_retcode := 2;
208 x_errbuf := 'Error';
209
210 END update_onhand_status;
211
212
213 END INV_UPDATE_ONHAND_STATUS ;