[Home] [Help]
PACKAGE BODY: APPS.WSH_DD_TXNS_PVT
Source
1 PACKAGE BODY WSH_DD_TXNS_PVT as
2 /* $Header: WSHDXTHB.pls 120.2 2006/06/07 08:19:06 alksharm noship $ */
3 --
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DD_TXN_PVT';
5 --
6 --
7 -- Procedure: Insert_DD_Txn
8 -- Parameters: All Attributes of a Delivery Detail Transaction Record,
9 -- Row_id out
10 -- DD_Txn_id out
11 -- Return_Status out
12 -- Description: This procedure will create a delivery detail transaction.
13 -- It will return to the user the dd_txn_id as a
14 -- parameter.
15
16 PROCEDURE Insert_DD_Txn(
17 p_dd_txn_info IN DD_Txn_Rec_Type,
18 x_rowid OUT NOCOPY VARCHAR2,
19 x_dd_txn_id OUT NOCOPY NUMBER,
20 x_return_status OUT NOCOPY VARCHAR2
21 ) IS
22
23 --
24 l_debug_on BOOLEAN;
25 --
26 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_DD_TXN';
27 --
28 BEGIN
29 --
30 --
31 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
32 --
33 IF l_debug_on IS NULL
34 THEN
35 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
36 END IF;
37 --
38 IF l_debug_on THEN
39 WSH_DEBUG_SV.push(l_module_name); -- bug 4992250
40 END IF;
41 --
42
43 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
44
45 INSERT INTO wsh_dd_txns
46 (DD_TXN_ID,
47 DD_TXN_DATE,
48 DELIVERY_DETAIL_ID,
49 RELEASED_STATUS,
50 REQUESTED_QUANTITY,
51 REQUESTED_QUANTITY_UOM,
52 REQUESTED_QUANTITY2,
53 REQUESTED_QUANTITY_UOM2,
54 PICKED_QUANTITY,
55 PICKED_QUANTITY2,
56 ATTRIBUTE_CATEGORY,
57 ATTRIBUTE1,
58 ATTRIBUTE2,
59 ATTRIBUTE3,
60 ATTRIBUTE4,
61 ATTRIBUTE5,
62 ATTRIBUTE6,
63 ATTRIBUTE7,
64 ATTRIBUTE8,
65 ATTRIBUTE9,
66 ATTRIBUTE10,
67 ATTRIBUTE11,
68 ATTRIBUTE12,
69 ATTRIBUTE13,
70 ATTRIBUTE14,
71 ATTRIBUTE15,
72 CREATION_DATE,
73 CREATED_BY,
74 LAST_UPDATE_DATE,
75 LAST_UPDATED_BY,
76 LAST_UPDATE_LOGIN)
77 VALUES
78 (wsh_dd_txns_s.nextval, -- bug 5257141
79 SYSDATE,
80 p_dd_txn_info.DELIVERY_DETAIL_ID,
81 p_dd_txn_info.RELEASED_STATUS,
82 p_dd_txn_info.REQUESTED_QUANTITY,
83 p_dd_txn_info.REQUESTED_QUANTITY_UOM,
84 p_dd_txn_info.REQUESTED_QUANTITY2,
85 p_dd_txn_info.REQUESTED_QUANTITY_UOM2,
86 p_dd_txn_info.PICKED_QUANTITY,
87 p_dd_txn_info.PICKED_QUANTITY2,
88 p_dd_txn_info.ATTRIBUTE_CATEGORY,
89 p_dd_txn_info.ATTRIBUTE1,
90 p_dd_txn_info.ATTRIBUTE2,
91 p_dd_txn_info.ATTRIBUTE3,
92 p_dd_txn_info.ATTRIBUTE4,
93 p_dd_txn_info.ATTRIBUTE5,
94 p_dd_txn_info.ATTRIBUTE6,
95 p_dd_txn_info.ATTRIBUTE7,
96 p_dd_txn_info.ATTRIBUTE8,
97 p_dd_txn_info.ATTRIBUTE9,
98 p_dd_txn_info.ATTRIBUTE10,
99 p_dd_txn_info.ATTRIBUTE11,
100 p_dd_txn_info.ATTRIBUTE12,
101 p_dd_txn_info.ATTRIBUTE13,
102 p_dd_txn_info.ATTRIBUTE14,
103 p_dd_txn_info.ATTRIBUTE15,
104 SYSDATE,
105 FND_GLOBAL.USER_ID,
106 SYSDATE,
107 FND_GLOBAL.USER_ID,
108 FND_GLOBAL.LOGIN_ID);
109
110 --
111 IF l_debug_on THEN
112 WSH_DEBUG_SV.log(l_module_name,'x_dd_txn_id',x_dd_txn_id);
113 WSH_DEBUG_SV.pop(l_module_name);
114 END IF;
115 --
116
117 EXCEPTION
118
119 WHEN others THEN
120 wsh_util_core.default_handler('WSH_DD_TXNS_PVT.INSERT_DD_TXN',l_module_name);
121 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
122 --
123 IF l_debug_on THEN
124 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
125 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
126 END IF;
127 --
128 END Insert_DD_Txn;
129
130
131 -- Procedure: Get_DD_Snapshot
132 -- Parameters: x_dd_txn_info: A record of all attributes of a DD Txn Record
133 -- p_delivery_detail_id : Delivery detail id for which the record to be populated.
134 -- Description: This procedure will copy the attributes of a delivery detail in wsh_delivery_details
135 -- and copy it to a dd transaction record.
136
137 PROCEDURE Get_DD_Snapshot (p_delivery_detail_id IN NUMBER,
138 x_dd_txn_info OUT NOCOPY DD_Txn_Rec_Type,
139 x_return_status OUT NOCOPY VARCHAR2) IS
140
141 CURSOR c_dd_rec (p_delivery_detail_id NUMBER) IS
142 SELECT DELIVERY_DETAIL_ID,
143 RELEASED_STATUS,
144 REQUESTED_QUANTITY,
145 REQUESTED_QUANTITY_UOM,
146 REQUESTED_QUANTITY2,
147 REQUESTED_QUANTITY_UOM2,
148 PICKED_QUANTITY,
149 PICKED_QUANTITY2
150 FROM
151 wsh_delivery_details
152 WHERE delivery_detail_id = p_delivery_detail_id;
153
154
155
156 ln_rec_info c_dd_rec%ROWTYPE;
157
158 --
159 l_debug_on BOOLEAN;
160 --
161 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DD_SNAPSHOT';
162 --
163
164 BEGIN
165
166 --
167 --
168 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
169 --
170 IF l_debug_on IS NULL
171 THEN
172 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
173 END IF;
174 --
175 IF l_debug_on THEN
176 WSH_DEBUG_SV.push(l_module_name);
177 WSH_DEBUG_SV.log(l_module_name,'p_delivery_detail_id',p_delivery_detail_id);
178 END IF;
179 --
180 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
181
182
183 OPEN c_dd_rec(p_delivery_detail_id);
184 FETCH c_dd_rec INTO ln_rec_info;
185 x_dd_txn_info.DELIVERY_DETAIL_ID := ln_rec_info.DELIVERY_DETAIL_ID;
186 x_dd_txn_info.RELEASED_STATUS := ln_rec_info.RELEASED_STATUS;
187 x_dd_txn_info.REQUESTED_QUANTITY := ln_rec_info.REQUESTED_QUANTITY;
188 x_dd_txn_info.REQUESTED_QUANTITY_UOM := ln_rec_info.REQUESTED_QUANTITY_UOM;
189 x_dd_txn_info.REQUESTED_QUANTITY2 := ln_rec_info.REQUESTED_QUANTITY2;
190 x_dd_txn_info.REQUESTED_QUANTITY_UOM2 := ln_rec_info.REQUESTED_QUANTITY_UOM2;
191 x_dd_txn_info.PICKED_QUANTITY := ln_rec_info.PICKED_QUANTITY;
192 x_dd_txn_info.PICKED_QUANTITY2 := ln_rec_info.PICKED_QUANTITY2;
193 CLOSE c_dd_rec;
194
195 --
196 IF l_debug_on THEN
197 WSH_DEBUG_SV.pop(l_module_name); -- bug 4992250
198 END IF;
199
200
201 EXCEPTION
202 WHEN others THEN
203 wsh_util_core.default_handler('WSH_DD_TXNS_PVT.Get_DD_Snapshot',l_module_name);
204 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
205 --
206 IF l_debug_on THEN
207 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
208 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
209 END IF;
210 --
211 END Get_DD_Snapshot;
212
213
214 PROCEDURE create_dd_txn_from_dd (p_delivery_detail_id IN NUMBER,
215 x_dd_txn_id OUT NOCOPY NUMBER,
216 x_return_status OUT NOCOPY VARCHAR2) IS
217
218 l_delivery_detail_id NUMBER;
219 l_dd_txn_id NUMBER := NULL;
220 l_dd_txn_info DD_Txn_Rec_Type;
221 l_return_status VARCHAR2(1);
222 l_rowid VARCHAR2(50);
223 others EXCEPTION;
224
225 --
226 l_debug_on BOOLEAN;
227 --
228 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DD_TXN_FROM_DD';
229 --
230
231 BEGIN
232
233 --
234 --
235 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
236 --
237 IF l_debug_on IS NULL
238 THEN
239 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
240 END IF;
241 --
242 IF l_debug_on THEN
243 WSH_DEBUG_SV.push(l_module_name);
244 WSH_DEBUG_SV.log(l_module_name,'p_delivery_detail_id',p_delivery_detail_id);
245 END IF;
246 --
247 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
248 --
249 -- Check if DBI is installed, possible values are Y or N only
250 -- If not installed, then do not proceed , return Success
251 IF (WSH_INTEGRATION.DBI_Installed = 'N')
252 THEN
253 IF l_debug_on THEN
254 WSH_DEBUG_SV.log(l_module_name,'DBI Installed flag-',WSH_INTEGRATION.DBI_Installed);
255 WSH_DEBUG_SV.pop(l_module_name);
256 END IF;
257 RETURN;
258 END IF;
259 --
260
261 l_delivery_detail_id := p_delivery_detail_id;
262
263 --
264 IF l_debug_on THEN
265 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Get_DD_Snapshot');
266 END IF;
267 --
268 Get_DD_Snapshot (p_delivery_detail_id => l_delivery_detail_id,
269 x_dd_txn_info => l_dd_txn_info,
270 x_return_status => l_return_status);
271
272 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
273 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
274 IF l_debug_on THEN
275 WSH_DEBUG_SV.logmsg(l_module_name,' Error in Get_DD_Snapshot');
276 WSH_DEBUG_SV.pop(l_module_name); -- bug 4992250
277 END IF;
278 RETURN;
279 END IF;
280
281 --
282 IF l_debug_on THEN
283 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Insert_DD_Txn');
284 END IF;
285 --
286
287 Insert_DD_Txn(p_dd_txn_info => l_dd_txn_info,
288 x_rowid => l_rowid,
289 x_dd_txn_id =>l_dd_txn_id,
290 x_return_status => l_return_status);
291
292 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
293 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
294 IF l_debug_on THEN
295 WSH_DEBUG_SV.logmsg(l_module_name,' Error in Insert_DD_Txn');
296 WSH_DEBUG_SV.pop(l_module_name); -- bug 4992250
297 END IF;
298 RETURN;
299 END IF;
300
301 IF l_debug_on THEN
302 WSH_DEBUG_SV.pop(l_module_name); -- bug 4992250
303 END IF;
304
305 EXCEPTION
306
307 WHEN others THEN
308 wsh_util_core.default_handler('WSH_DD_TXNS_PVT.create_dd_txn_from_dd',l_module_name);
309 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
310 --
311 IF l_debug_on THEN
312 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
313 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
314 END IF;
315 --
316
317 END create_dd_txn_from_dd;
318
319 END WSH_DD_TXNS_PVT;