1 PACKAGE BODY INV_ITEM_SUB_DEFAULT_PKG AS
2 /* $Header: INVISDPB.pls 120.1 2006/04/23 22:32:43 anmurali noship $ */
3 PROCEDURE INSERT_UPD_ITEM_SUB_DEFAULTS (
4 x_return_status OUT NOCOPY VARCHAR2
5 , x_msg_count OUT NOCOPY NUMBER
6 , x_msg_data OUT NOCOPY VARCHAR2
7 , p_organization_id IN NUMBER
8 , p_inventory_item_id IN NUMBER
9 , p_subinventory_code IN VARCHAR2
10 , p_default_type IN NUMBER
11 , p_creation_date IN DATE
12 , p_created_by IN NUMBER
13 , p_last_update_date IN DATE
14 , p_last_updated_by IN NUMBER
15 , p_process_code IN VARCHAR2
16 , p_commit IN VARCHAR2 ) IS
17
18 l_chk_rec_exists NUMBER;
19 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
20 l_subinv_code VARCHAR2(1);
21 l_msg_count NUMBER := 0;
22 BEGIN
23 IF (l_debug = 1) THEN
24 INV_TRX_UTIL_PUB.TRACE('Beginning of the program .The input parameters are :','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
25 INV_TRX_UTIL_PUB.TRACE('p_organization_id :'||p_organization_id,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
26 INV_TRX_UTIL_PUB.TRACE('p_inventory_item_id :'||p_inventory_item_id,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
27 INV_TRX_UTIL_PUB.TRACE('p_subinventory_code :'||p_subinventory_code,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
28 INV_TRX_UTIL_PUB.TRACE('p_default_type :'||p_default_type,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
29 INV_TRX_UTIL_PUB.TRACE('p_creation_date :'||p_creation_date,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
30 INV_TRX_UTIL_PUB.TRACE('p_created_by :'||p_created_by,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
31 INV_TRX_UTIL_PUB.TRACE('p_last_update_date :'||p_last_update_date,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
32 INV_TRX_UTIL_PUB.TRACE('p_last_updated_by :'||p_last_updated_by,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
33 INV_TRX_UTIL_PUB.TRACE('p_process_code :'||p_process_code,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
34 INV_TRX_UTIL_PUB.TRACE('p_commit :'||p_commit,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
35 END IF;
36 SAVEPOINT ins_upd_item_sub_dft;
37 x_return_status := fnd_api.g_ret_sts_success;
38
39 /*Check if all the input parameters are passed */
40 IF (p_organization_id IS NULL OR
41 p_inventory_item_id IS NULL OR
42 /*p_subinventory_code IS NULL OR Bug4013041--Now deleting default sub information when it is nulled.*/
43 p_default_type IS NULL OR
44 p_creation_date IS NULL OR
45 p_created_by IS NULL OR
46 p_last_update_date IS NULL OR
47 p_process_code IS NULL OR
48 p_last_updated_by IS NULL )
49 THEN
50 IF (l_debug = 1) THEN
51 INV_TRX_UTIL_PUB.TRACE('One or more input parameter/s is/are null :','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
52 END IF;
53 FND_MESSAGE.SET_NAME('WMS','WMS_NULL_INPUT_PARAMETER');
54 l_msg_count := l_msg_count + 1;
55 /* One of more input parameters provided are null */
56 RAISE fnd_api.g_exc_error;
57 END IF;
58
59 /*Check if the default is in 1,2,3. If not one among them, throw an exception */
60 IF p_default_type NOT IN (1,2,3) THEN
61 IF (l_debug = 1) THEN
62 INV_TRX_UTIL_PUB.TRACE('p_default_type is not in 1, 2,3 ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
63 END IF;
64 FND_MESSAGE.SET_NAME('WMS','WMS_INVALID_DEFAULT_TYPE');
65 l_msg_count := l_msg_count + 1;
66 /*Invalid value for the Default_Type.The value should be either 1 or 2 or 3*/
67 RAISE fnd_api.g_exc_error;
68 END IF;
69
70 IF p_process_code NOT IN ('INSERT','UPDATE','SYNC') THEN
71 IF (l_debug = 1) THEN
72 INV_TRX_UTIL_PUB.TRACE('Invalid value for p_process_code','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
73 END IF;
74 FND_MESSAGE.SET_NAME('WMS','WMS_INVALID_PROCESS_CODE');
75 l_msg_count := l_msg_count + 1;
76 RAISE fnd_api.g_exc_error;
77 /*Invalid value for the PROCESS CODE.The value should be either insert update or sync*/
78 END IF;
79
80 IF p_subinventory_code IS NOT NULL THEN
81 BEGIN
82 SELECT 'X' INTO l_subinv_code
83 FROM mtl_secondary_inventories
84 WHERE secondary_inventory_name = p_subinventory_code
85 AND nvl(disable_date,sysdate+1) > sysdate
86 AND organization_id = p_organization_id;
87 EXCEPTION
88 WHEN no_data_found THEN
89 FND_MESSAGE.SET_NAME('WMS','WMS_INVALID_SUBINVENTORY_CODE');
90 l_msg_count := l_msg_count + 1;
91 RAISE fnd_api.g_exc_error;
92 END;
93 END IF;
94
95 IF UPPER(p_process_code) = 'INSERT' THEN
96 IF (l_debug = 1) THEN
97 INV_TRX_UTIL_PUB.TRACE('p_process_code IS Insert ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
98 END IF;
99 INSERT
100 INTO mtl_item_sub_defaults (INVENTORY_ITEM_ID
101 ,ORGANIZATION_ID
102 ,SUBINVENTORY_CODE
103 ,DEFAULT_TYPE
104 ,LAST_UPDATE_DATE
105 ,LAST_UPDATED_BY
106 ,CREATION_DATE
107 ,CREATED_BY
108 )
109 VALUES (p_inventory_item_id
110 ,p_organization_id
111 ,p_subinventory_code
112 ,p_default_type
113 ,p_last_update_date
114 ,p_last_updated_by
115 ,p_creation_date
116 ,p_created_by
117 );
118 IF SQL%found THEN
119 IF (l_debug = 1) THEN
120 INV_TRX_UTIL_PUB.TRACE('Record inserted successfully ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
121 END IF;
122 END IF;
123 ELSIF UPPER(p_process_code) = 'UPDATE' THEN
124 IF (l_debug = 1) THEN
125 INV_TRX_UTIL_PUB.TRACE('p_process_code IS Update ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
126 END IF;
127
128 /*Bug4013041--Now deleting default sub information when it is nulled.*/
129 IF (p_subinventory_code IS NULL) THEN
130 delete from mtl_item_sub_defaults
131 where inventory_item_id = p_inventory_item_id
132 and organization_id = p_organization_id
133 and default_type = p_default_type;
134 ELSE
135 UPDATE mtl_item_sub_defaults
136 SET subinventory_code = p_subinventory_code
137 , LAST_UPDATE_DATE = p_last_update_date
138 , LAST_UPDATED_BY = p_last_updated_by
139 , CREATION_DATE = p_creation_date
140 , CREATED_BY = p_created_by
141 WHERE inventory_item_id = p_inventory_item_id
142 AND organization_id = p_organization_id
143 AND default_type = p_default_type;
144 IF SQL%FOUND THEN
145 IF (l_debug = 1) THEN
146 INV_TRX_UTIL_PUB.TRACE('Record Updated successfully','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
147 END IF;
148 END IF;
149 END IF; --p_subinventory_code IS NULL
150 ELSIF UPPER(p_process_code) = 'SYNC' THEN
151 /*
152 Check if the record exists in MTL_ITEM_SUB_DEFAULTS for the combination
153 Inventory_item_id,Organization_id,Default_type.If it exists, Update the
154 record with the new info provided. If the record does not exists, insert
155 a new record into MTL_ITEM_SUB_DEFAULTS
156 */
157 IF (l_debug = 1) THEN
158 INV_TRX_UTIL_PUB.TRACE('p_process_code IS Sync ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
159 END IF;
160 BEGIN
161 SELECT 1
162 INTO l_chk_rec_exists
163 FROM mtl_item_sub_defaults
164 WHERE inventory_item_id = p_inventory_item_id
165 AND organization_id = p_organization_id
166 AND default_type = p_default_type;
167
168 /*Bug4013041--Now deleting default sub information when it is nulled.*/
169 IF (p_subinventory_code IS NULL and l_chk_rec_exists = 1) THEN
170 delete from mtl_item_sub_defaults
171 where inventory_item_id = p_inventory_item_id
172 and organization_id = p_organization_id
173 and default_type = p_default_type;
174 ELSE
175 UPDATE mtl_item_sub_defaults
176 SET subinventory_code = p_subinventory_code
177 , LAST_UPDATE_DATE = p_last_update_date
178 , LAST_UPDATED_BY = p_last_updated_by
179 , CREATION_DATE = p_creation_date
180 , CREATED_BY = p_created_by
181 WHERE inventory_item_id = p_inventory_item_id
182 AND organization_id = p_organization_id
183 AND default_type = p_default_type;
184 IF SQL%FOUND THEN
185 IF (l_debug = 1) THEN
186 INV_TRX_UTIL_PUB.TRACE('Record Updated successfully','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
187 END IF;
188 END IF;
189 END IF;--p_subinventory_code IS NULL and l_chk_rec_exists = 1
190
191 EXCEPTION
192 WHEN no_data_found THEN
193 IF (l_debug = 1) THEN
194 INV_TRX_UTIL_PUB.TRACE('In no data found. Record does not exists in mtl_item_sub_defaults.Insert record.','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
195 END IF;
196 INSERT INTO mtl_item_sub_defaults (INVENTORY_ITEM_ID
197 ,ORGANIZATION_ID
198 ,SUBINVENTORY_CODE
199 ,DEFAULT_TYPE
200 ,LAST_UPDATE_DATE
201 ,LAST_UPDATED_BY
202 ,CREATION_DATE
203 ,CREATED_BY
204 )
205 VALUES (p_inventory_item_id
206 ,p_organization_id
207 ,p_subinventory_code
208 ,p_default_type
209 ,p_last_update_date
210 ,p_last_updated_by
211 ,p_creation_date
212 ,p_created_by
213 );
214 IF SQL%found THEN
215 IF (l_debug = 1) THEN
216 INV_TRX_UTIL_PUB.TRACE('Record inserted successfully ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
217 END IF;
218 END IF;
219 END;
220 END IF;
221
222 IF p_commit =fnd_api.g_true THEN
223 IF (l_debug = 1) THEN
224 INV_TRX_UTIL_PUB.TRACE('p_commit is true. Hence commiting the transaction ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
225 END IF;
226 COMMIT;
227 END IF;
228 IF (l_debug = 1) THEN
229 INV_TRX_UTIL_PUB.TRACE('Program completed successfully','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
230 END IF;
231 EXCEPTION
232 WHEN fnd_api.g_exc_error THEN
233 x_return_status := fnd_api.g_ret_sts_error;
234 IF (l_debug = 1) THEN
235 INV_TRX_UTIL_PUB.TRACE('In fnd_api.g_exc_error :'||SQLERRM,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
236 END IF;
237 ROLLBACK TO ins_upd_item_sub_dft;
238 x_msg_data := FND_MESSAGE.GET;
239 x_msg_count := l_msg_count;
240 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
241 x_return_status := fnd_api.g_ret_sts_unexp_error ;
242 IF (l_debug = 1) THEN
243 INV_TRX_UTIL_PUB.TRACE('In FND_API.G_EXC_UNEXPECTED_ERROR :'||SQLERRM,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
244 END IF;
245 ROLLBACK TO ins_upd_item_sub_dft;
246 x_msg_data := FND_MESSAGE.GET;
247 x_msg_count := l_msg_count;
248 WHEN OTHERS THEN
249 x_return_status := fnd_api.g_ret_sts_unexp_error ;
250 IF (l_debug = 1) THEN
251 INV_TRX_UTIL_PUB.TRACE('In when others :'||SQLERRM,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);
252 END IF;
253 ROLLBACK TO ins_upd_item_sub_dft;
254 x_msg_data := FND_MESSAGE.GET;
255 x_msg_count := l_msg_count;
256 END INSERT_UPD_ITEM_SUB_DEFAULTS;
257 END INV_ITEM_SUB_DEFAULT_PKG;
[Home] [Help]