[Home] [Help]
PACKAGE BODY: APPS.WSH_SHIPPING_CONSTRAINTS_PKG
Source
1 PACKAGE BODY WSH_SHIPPING_CONSTRAINTS_PKG as
2 /* $Header: WSHCSCPB.pls 120.0 2005/05/26 18:33:38 appldev noship $ */
3
4 --Procedure: check_shipping_constraints
5 --Parameters: p_changed_attributes
6 -- x_return_status
7 -- x_action_allowed
8 --Description: This procedure will check if actions like Delete or cancel
9 -- are allowed on a source line considering the status
10 -- of the delivery detail, detail
11
12
13 --
14 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_SHIPPING_CONSTRAINTS_PKG';
15 --
16 PROCEDURE check_shipping_constraints
17 (
18 p_source_code IN VARCHAR2,
19 p_changed_attributes IN ChangedAttributeRecType,
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_action_allowed OUT NOCOPY VARCHAR2,
22 x_action_message OUT NOCOPY VARCHAR2,
23 x_ord_qty_allowed OUT NOCOPY NUMBER,
24 p_log_level IN NUMBER DEFAULT 0
25 ) IS
26
27 CURSOR c_del_details_cur IS
28 SELECT wdd.delivery_detail_id,
29 wdd.serial_number,
30 wdd.transaction_temp_id,
31 wdd.source_line_id,
32 wdd.pickable_flag,
33 wdd.move_order_line_id,
34 wdd.ship_from_location_id,
35 wdd.organization_id,
36 wdd.inventory_item_id,
37 wdd.subinventory,
38 wdd.revision,
39 wdd.locator_id,
40 wdd.lot_number,
41 wdd.released_status,
42 wdd.requested_quantity,
43 wdd.picked_quantity,
44 wdd.cancelled_quantity,
45 wdd.shipped_quantity,
46 wdd.requested_quantity2,
47 wdd.picked_quantity2,
48 wdd.cancelled_quantity2,
49 wdd.shipped_quantity2,
50 wda.parent_delivery_detail_id,
51 wda.delivery_assignment_id,
52 wnd.planned_flag,
53 wnd.delivery_id,
54 nvl(wnd.status_code,'NO') status_code
55 FROM wsh_delivery_details wdd,
56 wsh_new_deliveries wnd,
57 wsh_delivery_assignments_v wda
58 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
59 AND wda.delivery_id = wnd.delivery_id (+)
60 AND wdd.source_line_id = p_changed_attributes.source_line_id
61 AND wdd.source_code = p_source_code
62 AND nvl(wdd.line_direction, 'O') IN ('O','IO') -- J Inbound Logistics jckwok
63 AND wdd.delivery_detail_id = decode (p_changed_attributes.delivery_detail_id,
64 NULL , wdd.delivery_detail_id ,
65 p_changed_attributes.delivery_detail_id)
66 AND wdd.container_flag = 'N'
67 AND wdd.released_status <> 'D' -- New
68 ORDER BY decode(nvl(wnd.status_code,'NO'),'NO',1,'OP',2,'SA',3,10), -- sperera 940/945
69 decode(wda.parent_delivery_detail_id,NULL,1,10),
70 decode(wnd.planned_flag,'N',1,'Y',2,'F',3,10), --TP release
71 decode(wdd.released_status,'N',1,'R',2,'X',3,'B',4,'S',5,'Y',6,10),
72 nvl(wdd.requested_quantity,0) asc, -- This will make sure that maximum number of details are accounted for
73 wdd.delivery_detail_id;
74
75 l_delivery_details_rec c_del_details_cur%ROWTYPE;
76
77 --
78 l_debug_on BOOLEAN;
79 --
80 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_SHIPPING_CONSTRAINTS';
81 --
82 BEGIN
83
84 --
85 -- Debug Statements
86 --
87 WSH_UTIL_CORE.Set_Log_Level(p_log_level);
88 --
89 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
90 --
91 IF l_debug_on IS NULL
92 THEN
93 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
94 END IF;
95 --
96 IF l_debug_on THEN
97 WSH_DEBUG_SV.push(l_module_name);
98 --
99 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
100 WSH_DEBUG_SV.log(l_module_name,'action_flag',p_changed_attributes.action_flag);
101 WSH_DEBUG_SV.log(l_module_name,'delivery_detail_id',p_changed_attributes.delivery_detail_id);
102 WSH_DEBUG_SV.log(l_module_name,'original_source_line_id',p_changed_attributes.original_source_line_id);
103 WSH_DEBUG_SV.log(l_module_name,'source_header_id',p_changed_attributes.source_header_id);
104 WSH_DEBUG_SV.log(l_module_name,'source_line_id',p_changed_attributes.source_line_id);
105 WSH_DEBUG_SV.log(l_module_name,'sold_to_org_id',p_changed_attributes.sold_to_org_id);
106 WSH_DEBUG_SV.log(l_module_name,'ship_from_org_id',p_changed_attributes.ship_from_org_id);
107 WSH_DEBUG_SV.log(l_module_name,'ship_to_org_id',p_changed_attributes.ship_to_org_id);
108 WSH_DEBUG_SV.log(l_module_name,'deliver_to_org_id',p_changed_attributes.deliver_to_org_id);
109 WSH_DEBUG_SV.log(l_module_name,'intmed_ship_to_org_id',p_changed_attributes.intmed_ship_to_org_id);
110 WSH_DEBUG_SV.log(l_module_name,'ordered_quantity',p_changed_attributes.ordered_quantity);
111 WSH_DEBUG_SV.log(l_module_name,'released_status',p_changed_attributes.released_status);
112 WSH_DEBUG_SV.log(l_module_name,'shipped_quantity',p_changed_attributes.shipped_quantity);
113 WSH_DEBUG_SV.log(l_module_name,'customer_item_id',p_changed_attributes.customer_item_id);
114 WSH_DEBUG_SV.log(l_module_name,'P_LOG_LEVEL',P_LOG_LEVEL);
115 END IF;
116 --
117
118 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
119
120 x_action_allowed := 'N';
121
122 IF(p_changed_attributes.source_line_id IS NULL) THEN
123 --
124 -- Debug Statements
125 --
126 IF l_debug_on THEN
127 WSH_DEBUG_SV.logmsg(l_module_name, 'NULL PASSED FOR SOURCE LINE ID' );
128 END IF;
129 --
130 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
131 ELSE
132
133 -- if the source_line_id is not null
134
135 IF((p_changed_attributes.action_flag = 'D') OR (p_changed_attributes.action_flag ='C')) THEN
136
137 OPEN c_del_details_cur;
138
139 LOOP
140 FETCH c_del_details_cur INTO l_delivery_details_rec;
141
142 IF c_del_details_cur%ROWCOUNT = 0 THEN
143 --
144 -- Debug Statements
145 --
146 IF l_debug_on THEN
147 WSH_DEBUG_SV.logmsg(l_module_name, 'NO CORRESPONDING DATA FOUND FOR THE LINE ID' || P_CHANGED_ATTRIBUTES.SOURCE_LINE_ID );
148 END IF;
149 --
150 x_action_allowed := 'Y';
151 END IF;
152
153 EXIT WHEN c_del_details_cur%NOTFOUND;
154
155
156 IF (p_changed_attributes.action_flag = 'D') THEN
157
158 IF (l_delivery_details_rec.status_code = 'NO') AND -- sperera 940/945
159 (l_delivery_details_rec.parent_delivery_detail_id IS NULL) THEN
160
161 x_action_allowed := 'Y';
162
163 ELSE
164
165 -- action not allowed because of the
166 -- status of either delivery or delivery_detail
167 -- or because of packing of delivery detail, or delivery is 'SIC'
168
169
170 x_action_allowed := 'N';
171
172 --
173 -- Debug Statements
174 --
175 IF l_debug_on THEN
176 WSH_DEBUG_SV.logmsg(l_module_name, 'DELIVERY DETAIL RELEASED_STATUS = ' || L_DELIVERY_DETAILS_REC.RELEASED_STATUS );
177 END IF;
178 --
179 --
180 -- Debug Statements
181 --
182 IF l_debug_on THEN
183 WSH_DEBUG_SV.logmsg(l_module_name, 'PARENT DELIVERY DETAIL = ' || L_DELIVERY_DETAILS_REC.PARENT_DELIVERY_DETAIL_ID );
184 END IF;
185 --
186 --
187 -- Debug Statements
188 --
189 IF l_debug_on THEN
190 WSH_DEBUG_SV.logmsg(l_module_name, 'DELIVERY STATUS CODE = ' || L_DELIVERY_DETAILS_REC.STATUS_CODE );
191 END IF;
192 --
193 --
194 -- Debug Statements
195 --
196 IF l_debug_on THEN
197 WSH_DEBUG_SV.logmsg(l_module_name, 'DELIVERY PLANNED FLAG = ' || L_DELIVERY_DETAILS_REC.PLANNED_FLAG );
198 END IF;
199 --
200 --
201 -- Debug Statements
202 --
203 IF l_debug_on THEN
204 WSH_DEBUG_SV.logmsg(l_module_name, 'DELETE ACTION NOT ALLOWED FOR SOURCE LINE ID: ' || P_CHANGED_ATTRIBUTES.SOURCE_LINE_ID );
205 END IF;
206 --
207
208 exit;
209
210 END IF;
211
212 ELSIF (p_changed_attributes.action_flag = 'C') THEN
213
214 IF(l_delivery_details_rec.status_code IN ('CO', 'IT', 'CL', 'SR', 'SC')) THEN -- sperera 940/945
215
216 x_action_allowed := 'N';
217
218 x_ord_qty_allowed := 0;
219
220 ELSE
221
222 x_action_allowed := 'Y';
223
224 END IF;
225
226 END IF; -- if action_flag = D
227
228 END LOOP;
229
230 CLOSE c_del_details_cur;
231
232 ELSE
233
234 --
235 -- Debug Statements
236 --
237 IF l_debug_on THEN
238 WSH_DEBUG_SV.logmsg(l_module_name, 'INVALID ACTION FLAG' );
239 END IF;
240 --
241 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
242
243 END IF; -- if action_flag = D
244
245 END IF; -- if source_line_id is null
246
247
248 --
249 -- Debug Statements
250 --
251 IF l_debug_on THEN
252 WSH_DEBUG_SV.pop(l_module_name);
253 END IF;
254 --
255 EXCEPTION
256
257 WHEN OTHERS THEN
258 x_action_allowed := 'N';
259 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
260
261 IF c_del_details_cur%ISOPEN THEN
262 CLOSE c_del_details_cur;
263 END IF;
264 --
265 -- Debug Statements
266 --
267 IF l_debug_on THEN
268 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
269 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
270 END IF;
271 --
272 END check_shipping_constraints;
273
274 END WSH_SHIPPING_CONSTRAINTS_PKG;