eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.WSH_SHIPMENT_BATCH_PKG

Source


1 PACKAGE BODY WSH_SHIPMENT_BATCH_PKG AS
2 /* $Header: WSHSBPKB.pls 120.1.12020000.2 2013/01/17 03:59:18 ueshanka ship $ */
3 
4    G_PKG_NAME      CONSTANT VARCHAR2(30) := 'WSH_SHIPMENT_BATCH_PKG';
5 
6 --FORWARD Declaration
7 
8 --
9 --=============================================================================
10 -- PUBLIC PROCEDURE :
11 --       Create_Shipment_Batch
12 --
13 -- PARAMETERS:
14 --       errbuf                 => Message returned to Concurrent Manager
15 --       retcode                => Code (0, 1, 2) returned to Concurrent Manager
16 --       p_organization_id      => Orgnaization
17 --       p_customer_id          => Consignee/Customer
18 --       p_ship_to_location_id  => Ship To Location
19 --       p_transaction_type_id  => Sales Order Type
20 --       p_from_order_number    => From Order Number
21 --       p_to_order_number      => To Order Number
22 --       p_from_request_date    => From Request Date
23 --       p_to_request_date      => To Request Date
24 --       p_from_schedule_date   => From Schedule Date
25 --       p_to_schedule_date     => To Schedule Date
26 --       p_shipment_priority    => Shipment Priority
27 --       p_include_internal_so  => Incude Internal Sales Order
28 --       Bug 14620571: Added parameter p_include_lines_with_hold
29 --       p_include_lines_with_hold => Include order lines with Hold
30 --       p_log_level            => Either 1(Debug), 0(No Debug)
31 --
32 -- COMMENT:
33 --       API will be invoked from Concurrent Manager whenever concurrent program
34 --       'Create Shipment Batches' is triggered.
35 --       Wrapper for 'Crete Shipment Batch' API
36 --=============================================================================
37 --
38 PROCEDURE Create_Shipment_Batch (
39           errbuf                 OUT NOCOPY   VARCHAR2,
40           retcode                OUT NOCOPY   NUMBER,
41           p_organization_id      IN  NUMBER,
42           p_customer_id          IN  NUMBER,
43           p_ship_to_location_id  IN  NUMBER,
44           p_transaction_type_id  IN  NUMBER,
45           p_from_order_number    IN  VARCHAR2,
46           p_to_order_number      IN  VARCHAR2,
47           p_from_request_date    IN  VARCHAR2,
48           p_to_request_date      IN  VARCHAR2,
49           p_from_schedule_date   IN  VARCHAR2,
50           p_to_schedule_date     IN  VARCHAR2,
51           p_shipment_priority    IN  VARCHAR,
52           p_include_internal_so  IN  VARCHAR,
53           p_include_lines_with_hold IN VARCHAR DEFAULT 'N',
54           p_log_level            IN  NUMBER)
55 IS
56    l_completion_status          VARCHAR2(30);
57    l_return_status              VARCHAR2(1);
58 
59    l_debug_on                 BOOLEAN;
60    l_module_name CONSTANT     VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_Shipment_Batch1';
61 BEGIN
62    --
63    WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
64    WSH_UTIL_CORE.Set_Log_Level(p_log_level);
65    --
66    l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
67    --
68    IF l_debug_on IS NULL
69    THEN
70        l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
71    END IF;
72    --
73    IF l_debug_on THEN
74       wsh_debug_sv.push(l_module_name);
75       wsh_debug_sv.log(l_module_name, 'p_organization_id', p_organization_id);
76       wsh_debug_sv.log(l_module_name, 'p_customer_id', p_customer_id);
77       wsh_debug_sv.log(l_module_name, 'p_ship_to_location_id', p_ship_to_location_id);
78       wsh_debug_sv.log(l_module_name, 'p_transaction_type_id', p_transaction_type_id);
79       wsh_debug_sv.log(l_module_name, 'p_from_order_number', p_from_order_number);
80       wsh_debug_sv.log(l_module_name, 'p_to_order_number', p_to_order_number);
81       wsh_debug_sv.log(l_module_name, 'p_from_request_date', p_from_request_date);
82       wsh_debug_sv.log(l_module_name, 'p_to_request_date', p_to_request_date);
83       wsh_debug_sv.log(l_module_name, 'p_from_schedule_date', p_from_schedule_date);
84       wsh_debug_sv.log(l_module_name, 'p_to_schedule_date', p_to_schedule_date);
85       wsh_debug_sv.log(l_module_name, 'p_shipment_priority', p_shipment_priority);
86       WSH_DEBUG_SV.log(l_module_name, 'p_include_internal_so', p_include_internal_so);
87       WSH_DEBUG_SV.log(l_module_name, 'p_include_lines_with_hold', p_include_lines_with_hold);
88       wsh_debug_sv.log(l_module_name, 'p_log_level', p_log_level);
89    END IF;
90    --
91 
92    --
93    IF l_debug_on THEN
94       WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Create_Shipment_Batch', WSH_DEBUG_SV.C_PROC_LEVEL);
95    END IF;
96    --
97 
98    Create_Shipment_Batch (
99              p_organization_id      => p_organization_id,
100              p_customer_id          => p_customer_id,
101              p_ship_to_location_id  => p_ship_to_location_id,
102              p_transaction_type_id  => p_transaction_type_id,
103              p_from_order_number    => p_from_order_number,
104              p_to_order_number      => p_to_order_number,
105              p_from_request_date    => p_from_request_date,
106              p_to_request_date      => p_to_request_date,
107              p_from_schedule_date   => p_from_schedule_date,
108              p_to_schedule_date     => p_to_schedule_date,
109              p_shipment_priority    => p_shipment_priority,
110              p_include_internal_so  => p_include_internal_so,
111              p_include_lines_with_hold => p_include_lines_with_hold,
112              x_return_status        => l_return_status );
113 
114    --
115    IF l_debug_on THEN
116       WSH_DEBUG_SV.log(l_module_name, 'Return Status of Create_Shipment_Batch', l_return_status);
117    END IF;
118    --
119 
120    IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
121       l_completion_status := 'SUCCESS';
122       errbuf := 'Create Shipment Batches Program has completed successfully';
123       retcode := '0';
124    ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
125       l_completion_status := 'WARNING';
126       errbuf := 'Create Shipment Batches Program has completed with warning';
127       retcode := '1';
128    ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
129       l_completion_status := 'ERROR';
130       errbuf := 'Create Shipment Batches Program has completed with error';
131       retcode := '2';
132    ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
133       l_completion_status := 'UNEXPECTED ERROR';
134       errbuf := 'Create Shipment Batches Program has completed with unexpected error';
135       retcode := '2';
136    END IF;
137 
138    IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING )
139    THEN
140       --
141       IF l_debug_on THEN
142          WSH_DEBUG_SV.logmsg(l_module_name, 'Commiting the transaction......');
143       END IF;
144       --
145       COMMIT;
146    ELSE
147       --
148       IF l_debug_on THEN
149          WSH_DEBUG_SV.logmsg(l_module_name, 'Rolling back the transaction......');
150       END IF;
151       --
152       ROLLBACK;
153    END IF;
154 
155    --
156    IF l_debug_on THEN
157       WSH_DEBUG_SV.log(l_module_name,'l_completion_status', l_completion_status);
158       WSH_DEBUG_SV.log(l_module_name,'errbuf', errbuf);
159       WSH_DEBUG_SV.log(l_module_name,'retcode', retcode);
160       WSH_DEBUG_SV.pop(l_module_name);
161    END IF;
162    --
163 EXCEPTION
164    WHEN OTHERS THEN
165       l_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
166       errbuf := 'Create Shipment Batches Program is completed with unexpected error - ' || SQLCODE;
167       retcode := '2';
168       --
169       IF l_debug_on THEN
170         WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
171         WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
172       END IF;
173       --
174 END Create_Shipment_Batch;
175 --
176 --=============================================================================
177 -- PUBLIC PROCEDURE :
178 --       Create_Shipment_Batch
179 --
180 -- PARAMETERS:
181 --       p_organization_id      => Orgnaization
182 --       p_customer_id          => Consignee/Customer
183 --       p_ship_to_location_id  => Ship To Location
184 --       p_transaction_type_id  => Sales Order Type
185 --       p_from_order_number    => From Order Number
186 --       p_to_order_number      => To Order Number
187 --       p_from_request_date    => From Request Date
188 --       p_to_request_date      => To Request Date
189 --       p_from_schedule_date   => From Schedule Date
190 --       p_to_schedule_date     => To Schedule Date
191 --       p_shipment_priority    => Shipment Priority
192 --       p_include_internal_so  => Incude Internal Sales Order
193 --       Bug 14620571: Added parameter p_include_lines_with_hold
194 --       p_include_lines_with_hold => Include order lines with Hold
195 --       x_return_status        => Return Status of API (S,W,E,U)
196 --
197 -- COMMENT:
198 --       Based on input parameter values, eligble records from WDD are fetced.
199 --       Records fetched are grouped into Shipment Batches based on grouping
200 --       criteria returned from WSH_CUSTOM_PUB.Shipment_Batch_Group_Criteria
201 --       Custom API. A record is inserted into Wsh_Shipment_Batches table for
202 --       each shipment Batch and corresponding batch name is stamped in WDD.
203 --
204 --       Mandatory grouping criteria for Shipment Batch is
205 --          a) Customer
206 --          b) Ship To Site
207 --          c) Organization
208 --          d) Org (Operating Unit)
209 --          e) Currency Code
210 --       Optional grouping criteria for Shipment Batch is
211 --          a) Invoice To Location
212 --          b) Deliver To Location
213 --          c) Ship To Contact
214 --          d) Invoice To Contact
215 --          e) Deliver To Contact
216 --          f) Ship Method
217 --          g) Freight Terms
218 --          h) FOB
219 --          i) Within/Across Orders
220 --=============================================================================
221 --
222 PROCEDURE Create_Shipment_Batch (
223           p_organization_id      IN  NUMBER,
224           p_customer_id          IN  NUMBER,
225           p_ship_to_location_id  IN  NUMBER,
226           p_transaction_type_id  IN  NUMBER,
227           p_from_order_number    IN  VARCHAR2,
228           p_to_order_number      IN  VARCHAR2,
229           p_from_request_date    IN  VARCHAR2,
230           p_to_request_date      IN  VARCHAR2,
231           p_from_schedule_date   IN  VARCHAR2,
232           p_to_schedule_date     IN  VARCHAR2,
233           p_shipment_priority    IN  VARCHAR,
234           p_include_internal_so  IN  VARCHAR,
235           p_include_lines_with_hold IN VARCHAR DEFAULT 'N',
236           x_return_status        OUT NOCOPY VARCHAR2 )
237 IS
238 
239    l_total                      NUMBER := 0;
240    l_success                    NUMBER := 0;
241    l_errors                     NUMBER := 0;
242    l_batch_id                   NUMBER;
243    l_prev_group_id              NUMBER;
244    l_cnt                        NUMBER;
245    l_bulk_count                 NUMBER := 1000;
246    l_line_number                NUMBER;
247    l_src_header_id              NUMBER;
248    l_upd_count                  NUMBER;
249 
250    l_from_request_date          DATE;
251    l_to_request_date            DATE;
252    l_from_schedule_date         DATE;
253    l_to_schedule_date           DATE;
254 
255    v_cursorid                   INTEGER;
256    v_ignore                     INTEGER;
257 
258    l_update_flag                BOOLEAN := FALSE;
259 
260    l_dyn_query                  VARCHAR2(15000);
261    l_order_query                VARCHAR2(1000);
262    l_grp_query                  VARCHAR2(2000);
263    l_dense_select               VARCHAR2(2000);
264    l_wh_type                    VARCHAR2(30);
265    l_return_status              VARCHAR2(1);
266    l_grp_by_inv_site            VARCHAR2(1);
267    l_grp_by_del_site            VARCHAR2(1);
268    l_grp_by_ship_contact        VARCHAR2(1);
269    l_grp_by_inv_contact         VARCHAR2(1);
270    l_grp_by_del_contact         VARCHAR2(1);
271    l_grp_by_ship_method         VARCHAR2(1);
272    l_grp_by_freight_terms       VARCHAR2(1);
273    l_grp_by_fob_code            VARCHAR2(1);
274    l_grp_by_within_order        VARCHAR2(1);
275 
276    l_rowid_tbl                  DBMS_SQL.URowid_Table;
277    l_del_detail_tbl             DBMS_SQL.Number_Table;
278    l_src_line_id_tbl            DBMS_SQL.Number_Table;
279    l_org_id_tbl                 DBMS_SQL.Number_Table;
280    l_customer_id_tbl            DBMS_SQL.Number_Table;
281    l_organization_id_tbl        DBMS_SQL.Number_Table;
282    l_ship_from_loc_tbl          DBMS_SQL.Number_Table;
283    l_ship_to_site_tbl           DBMS_SQL.Number_Table;
284    l_invoice_to_site_tbl        DBMS_SQL.Number_Table;
285    l_deliver_to_site_tbl        DBMS_SQL.Number_Table;
286    l_ship_to_con_tbl            DBMS_SQL.Number_Table;
287    l_invoice_to_con_tbl         DBMS_SQL.Number_Table;
288    l_deliver_to_con_tbl         DBMS_SQL.Number_Table;
289    l_curr_code_tbl              DBMS_SQL.Varchar2_Table;
290    l_ship_method_tbl            DBMS_SQL.Varchar2_Table;
291    l_freight_terms_tbl          DBMS_SQL.Varchar2_Table;
292    l_fob_code_tbl               DBMS_SQL.Varchar2_Table;
293    l_group_id_tbl               DBMS_SQL.Number_Table;
294 
295    l_shipment_batch_tbl         Shipment_Batch_Tbl;
296    l_dd_batch_id_tbl            WSH_UTIL_CORE.Id_Tab_Type;
297    l_dd_line_num_tbl            WSH_UTIL_CORE.Id_Tab_Type;
298    l_wdd_update_flag            WSH_UTIL_CORE.Column_Tab_Type;
299    l_batch_tbl                  WSH_UTIL_CORE.Id_Tab_Type;
300    --Fulfillment Batch XML Project --Start
301    l_raise_batch_tbl            WSH_UTIL_CORE.Id_Tab_Type;
302    l_be_return_status           VARCHAR2(1);
303    l_txns_history_rec           WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
304    wsh_raise_event_error        EXCEPTION;
305    l_dist_src_entity_profile    VARCHAR2 (10);
306    l_dist_flag                  VARCHAR2 (1);
307    --Fulfillment Batch XML Project --End
308 
309    Credit_or_Hold_Failed        EXCEPTION;
310    RECORD_LOCKED                EXCEPTION;
311    PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
312 
313    l_debug_on                 BOOLEAN;
314    l_module_name CONSTANT     VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_Shipment_Batch';
315 
316    CURSOR c_wh_flag_cur IS --Fulfillment Batch XML Project
317    SELECT distributed_organization_flag
318    FROM   mtl_parameters
319    WHERE  organization_id = p_organization_id;
320 
321 BEGIN
322    --
323    l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
324    --
325    IF l_debug_on IS NULL
326    THEN
327        l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
328    END IF;
329    --
330    IF l_debug_on THEN
331       wsh_debug_sv.push(l_module_name);
332       wsh_debug_sv.log(l_module_name, 'p_organization_id', p_organization_id);
333       wsh_debug_sv.log(l_module_name, 'p_customer_id', p_customer_id);
334       wsh_debug_sv.log(l_module_name, 'p_ship_to_location_id', p_ship_to_location_id);
335       wsh_debug_sv.log(l_module_name, 'p_transaction_type_id', p_transaction_type_id);
336       wsh_debug_sv.log(l_module_name, 'p_from_order_number', p_from_order_number);
337       wsh_debug_sv.log(l_module_name, 'p_to_order_number', p_to_order_number);
338       wsh_debug_sv.log(l_module_name, 'p_from_request_date', p_from_request_date);
339       wsh_debug_sv.log(l_module_name, 'p_to_request_date', p_to_request_date);
340       wsh_debug_sv.log(l_module_name, 'p_from_schedule_date', p_from_schedule_date);
341       wsh_debug_sv.log(l_module_name, 'p_to_schedule_date', p_to_schedule_date);
342       wsh_debug_sv.log(l_module_name, 'p_shipment_priority', p_shipment_priority);
343       WSH_DEBUG_SV.log(l_module_name, 'p_include_internal_so', p_include_internal_so);
344       WSH_DEBUG_SV.log(l_module_name, 'p_include_lines_with_hold', p_include_lines_with_hold);
345    END IF;
346    --
347 
348    x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
349 
350    l_dist_src_entity_profile :=  NVL(FND_PROFILE.VALUE('WSH_SR_SOURCE'),'D'); --Fulfillment Batch XML Project
351 
352    OPEN  c_wh_flag_cur;  --Fulfillment Batch XML Project
353    FETCH c_wh_flag_cur INTO l_dist_flag;
354    CLOSE c_wh_flag_cur;
355 
356    IF l_debug_on THEN --Fulfillment Batch XML Project
357       wsh_debug_sv.log (l_module_name,'WSH: Distributed Source Entity', l_dist_src_entity_profile);
358       wsh_debug_sv.log (l_module_name,'Distributed Flag', l_dist_flag);
359    END IF;
360    /*
361    --Check If organization is TW2 Enabled
362    --
363    IF l_debug_on THEN
364       WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type', WSH_DEBUG_SV.C_PROC_LEVEL);
365    END IF;
366    --
367    l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(
368                              p_organization_id  => p_organization_id,
369                              x_return_status    => l_return_status );
370    --
371    IF l_debug_on THEN
372       WSH_DEBUG_SV.log(l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
373    END IF;
374    --
375 
376    IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
377       --
378       IF l_debug_on THEN
379          WSH_DEBUG_SV.logmsg(l_module_name, 'Error: WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type returned error');
380       END IF;
381       --
382       x_return_status := l_return_status;
383       RAISE FND_API.G_EXC_ERROR;
384    END IF;
385 
386    IF nvl(l_wh_type, FND_API.G_MISS_CHAR) <> 'TW2' THEN
387       --
388       IF l_debug_on THEN
389          WSH_DEBUG_SV.logmsg(l_module_name, 'Error: Organization is not TW2 Enabled');
390       END IF;
391       --
392       RAISE FND_API.G_EXC_ERROR;
393    END IF;
394    */
395 
396    --
397    IF l_debug_on THEN
398       WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_CUSTOM_PUB.Shipment_Batch_Group_Criteria', WSH_DEBUG_SV.C_PROC_LEVEL);
399    END IF;
400    --
401    WSH_CUSTOM_PUB.Shipment_Batch_Group_Criteria(
402               x_grp_by_invoice_to_site     => l_grp_by_inv_site,
403               x_grp_by_deliver_to_site     => l_grp_by_del_site,
404               x_grp_by_ship_to_contact     => l_grp_by_ship_contact,
405               x_grp_by_invoice_to_contact  => l_grp_by_inv_contact,
406               x_grp_by_deliver_to_contact  => l_grp_by_del_contact,
407               x_grp_by_ship_method         => l_grp_by_ship_method,
408               x_grp_by_freight_terms       => l_grp_by_freight_terms,
409               x_grp_by_fob_code            => l_grp_by_fob_code,
410               x_grp_by_within_order        => l_grp_by_within_order );
411 
412    --
413    IF l_debug_on THEN
414       WSH_DEBUG_SV.log(l_module_name,'l_grp_by_invoice_to_site    ', l_grp_by_inv_site     );
415       WSH_DEBUG_SV.log(l_module_name,'l_grp_by_deliver_to_site    ', l_grp_by_del_site     );
416       WSH_DEBUG_SV.log(l_module_name,'l_grp_by_ship_to_contact    ', l_grp_by_ship_contact );
417       WSH_DEBUG_SV.log(l_module_name,'l_grp_by_invoice_to_contact ', l_grp_by_inv_contact  );
418       WSH_DEBUG_SV.log(l_module_name,'l_grp_by_deliver_to_contact ', l_grp_by_del_contact  );
419       WSH_DEBUG_SV.log(l_module_name,'l_grp_by_ship_method        ', l_grp_by_ship_method  );
420       WSH_DEBUG_SV.log(l_module_name,'l_grp_by_freight_terms      ', l_grp_by_freight_terms);
421       WSH_DEBUG_SV.log(l_module_name,'l_grp_by_fob_code           ', l_grp_by_fob_code     );
422       WSH_DEBUG_SV.log(l_module_name,'l_grp_by_within_order       ', l_grp_by_within_order );
423    END IF;
424    --
425 
426    l_from_request_date    := to_date(p_from_request_date,'YYYY/MM/DD HH24:MI:SS');
427    l_to_request_date      := to_date(p_to_request_date,'YYYY/MM/DD HH24:MI:SS');
428    l_from_schedule_date   := to_date(p_from_schedule_date,'YYYY/MM/DD HH24:MI:SS');
429    l_to_schedule_date     := to_date(p_to_schedule_date,'YYYY/MM/DD HH24:MI:SS');
430 
431    l_order_query := ' select to_char(order_number) from oe_order_headers_all where order_number ';
432 
433    -- SELECT CLAUSE
434    l_dyn_query := 'SELECT wdd.rowid, ';
435    l_dyn_query := l_dyn_query || ' wdd.delivery_detail_id, ';
436    l_dyn_query := l_dyn_query || ' wdd.source_line_id, ';
437    l_dyn_query := l_dyn_query || ' wdd.org_id, ';
438    l_dyn_query := l_dyn_query || ' wdd.currency_code, ';
439    l_dyn_query := l_dyn_query || ' wdd.customer_id, ';
440    l_dyn_query := l_dyn_query || ' wdd.organization_id, ';
441    l_dyn_query := l_dyn_query || ' wdd.ship_from_location_id, ';
442    l_dyn_query := l_dyn_query || ' wdd.ship_to_site_use_id, ';
443    l_dyn_query := l_dyn_query || ' oel.invoice_to_org_id, ';
444    l_dyn_query := l_dyn_query || ' wdd.deliver_to_site_use_id, ';
445    l_dyn_query := l_dyn_query || ' wdd.ship_to_contact_id, ';
446    l_dyn_query := l_dyn_query || ' oel.invoice_to_contact_id, ';
447    l_dyn_query := l_dyn_query || ' wdd.deliver_to_contact_id, ';
448    l_dyn_query := l_dyn_query || ' wdd.ship_method_code, ';
449    l_dyn_query := l_dyn_query || ' wdd.freight_terms_code, ';
450    l_dyn_query := l_dyn_query || ' wdd.fob_code, ';
451 
452    -- Mandatory Grouping Criteria
453    l_grp_query := '   wdd.customer_id ';
454    l_grp_query := l_grp_query || ' , wdd.organization_id ';
455    l_grp_query := l_grp_query || ' , wdd.ship_to_site_use_id ';
456    l_grp_query := l_grp_query || ' , wdd.org_id ';
457    l_grp_query := l_grp_query || ' , wdd.currency_code ';
458 
459    IF nvl(l_grp_by_inv_site, 'Y') = 'Y' THEN
460       l_grp_query := l_grp_query || ' , oel.invoice_to_org_id ';
461    END IF;
462 
463    IF nvl(l_grp_by_del_site, 'Y') = 'Y' THEN
464       l_grp_query := l_grp_query || ' , wdd.deliver_to_site_use_id ';
465    END IF;
466 
467    IF nvl(l_grp_by_ship_contact, 'Y') = 'Y' THEN
468       l_grp_query := l_grp_query || ' , wdd.ship_to_contact_id ';
469    END IF;
470 
471    IF nvl(l_grp_by_inv_contact, 'Y') = 'Y' THEN
472       l_grp_query := l_grp_query || ' , oel.invoice_to_contact_id ';
473    END IF;
474 
475    IF nvl(l_grp_by_del_contact, 'Y') = 'Y' THEN
476       l_grp_query := l_grp_query || ' , wdd.deliver_to_contact_id ';
477    END IF;
478 
479    IF nvl(l_grp_by_ship_method, 'Y') = 'Y' THEN
480       l_grp_query := l_grp_query || ' , wdd.ship_method_code ';
481    END IF;
482 
483    IF nvl(l_grp_by_freight_terms, 'Y') = 'Y' THEN
484       l_grp_query := l_grp_query || ' , wdd.freight_terms_code ';
485    END IF;
486 
487    IF nvl(l_grp_by_fob_code, 'Y') = 'Y' THEN
488       l_grp_query := l_grp_query || ' , wdd.fob_code ';
489    END IF;
490 
491    IF nvl(l_grp_by_within_order, 'Y') = 'Y' THEN
492       l_grp_query := l_grp_query || ' , wdd.source_header_id ';
493    END IF;
494 
495    -- Generating Group Id Using Analytic Function
496    l_dyn_query := l_dyn_query || ' DENSE_RANK() OVER (ORDER BY ';
497 
498    --Append Grouping Criteria to DENSE_RANK Analytic Function
499    l_dyn_query := l_dyn_query || l_grp_query;
500    l_dyn_query := l_dyn_query || ' ) Group_Id ';
501 
502    -- From Clause
503    l_dyn_query := l_dyn_query || ' FROM Wsh_Delivery_Details     wdd, ';
504    l_dyn_query := l_dyn_query || '      Wsh_Delivery_Assignments wda, ';
505    l_dyn_query := l_dyn_query || '      Oe_Order_Lines_All       oel  ';
506 
507    -- Where Clause
508    l_dyn_query := l_dyn_query || ' WHERE oel.line_id = wdd.source_line_id ';
509    -- To make sure delivery details are not assigned to delivery
510    l_dyn_query := l_dyn_query || ' AND   wda.delivery_id is null';
511    l_dyn_query := l_dyn_query || ' AND   wda.delivery_detail_id = wdd.delivery_detail_id';
512    l_dyn_query := l_dyn_query || ' AND   wdd.source_code = ''OE''';
513    l_dyn_query := l_dyn_query || ' AND   wdd.released_status in ( ''R'', ''B'', ''X'' )';
514    l_dyn_query := l_dyn_query || ' AND   wdd.shipment_batch_id is null';
515 
516    -- { Dynamic Where Clause Starts Here
517    IF p_include_internal_so = 'Y' THEN
518       l_dyn_query := l_dyn_query || ' AND wdd.line_direction in ( ''IO'', ''O'' ) ';
519    ELSE
520       l_dyn_query := l_dyn_query || ' AND wdd.line_direction in ( ''O'' ) ';
521    END IF;
522 
523    IF p_organization_id is not null THEN
524       l_dyn_query := l_dyn_query || ' AND wdd.organization_id = :x_organization_id ';
525    END IF;
526 
527    IF p_customer_id is not null THEN
528       l_dyn_query := l_dyn_query || ' AND wdd.customer_id = :x_customer_id ';
529    END IF;
530 
531    IF p_ship_to_location_id is not null THEN
532       l_dyn_query := l_dyn_query || ' AND wdd.ship_to_location_id = :x_ship_to_location_id ';
533    END IF;
534 
535    IF p_transaction_type_id is not null THEN
536       l_dyn_query := l_dyn_query || ' AND wdd.source_header_type_id = :x_transaction_type_id ';
537    END IF;
538 
539    IF p_from_order_number is not null and p_to_order_number is not null
540    THEN
541       l_dyn_query := l_dyn_query || '  AND wdd.source_header_number in ( ' || l_order_query || ' between :x_from_order_number ';
542       l_dyn_query := l_dyn_query || '           AND :x_to_order_number )';
543    ELSIF p_from_order_number is not null and p_to_order_number is null
544    THEN
545       l_dyn_query := l_dyn_query || '  AND wdd.source_header_number in ( ' || l_order_query || ' >= :x_from_order_number ) ';
546    ELSIF p_from_order_number is null and p_to_order_number is not null
547    THEN
548       l_dyn_query := l_dyn_query || '  AND wdd.source_header_number in ( ' || l_order_query || ' <= :x_to_order_number ) ';
549    END IF;
550 
551    IF l_from_request_date is not null and l_to_request_date is not null
552    THEN
553       l_dyn_query := l_dyn_query || '  AND    wdd.date_requested between :x_from_request_date ';
554       l_dyn_query := l_dyn_query || '         and :x_to_request_date ';
555    ELSIF l_from_request_date is not null and l_to_request_date is null
556    THEN
557       l_dyn_query := l_dyn_query || '  AND    wdd.date_requested >= :x_from_request_date ';
558    ELSIF l_from_request_date is null and l_to_request_date is not null
559    THEN
560       l_dyn_query := l_dyn_query || '  AND    wdd.date_requested <= :x_to_request_date ';
561    END IF;
562 
563    IF l_from_schedule_date is not null and l_to_schedule_date is not null
564    THEN
565       l_dyn_query := l_dyn_query || '  AND    wdd.date_scheduled between :x_from_schedule_date ';
566       l_dyn_query := l_dyn_query || '         and :x_to_schedule_date ';
567    ELSIF l_from_schedule_date is not null and l_to_schedule_date is null
568    THEN
569       l_dyn_query := l_dyn_query || '  AND    wdd.date_scheduled >= :x_from_schedule_date ';
570    ELSIF l_from_schedule_date is null and l_to_schedule_date is not null
571    THEN
572       l_dyn_query := l_dyn_query || '  AND    wdd.date_scheduled <= :x_to_schedule_date ';
573    END IF;
574 
575    IF p_shipment_priority is not null THEN
576       l_dyn_query := l_dyn_query || ' AND wdd.shipment_priority_code = :x_shipment_priority ';
577    END IF;
578    -- } Dynamic Where Clause Ends Here
579 
580    --
581    IF l_debug_on THEN
582       WSH_DEBUG_SV.log(l_module_name, 'Transaction Query', l_dyn_query);
583    END IF;
584    --
585 
586    v_cursorid := DBMS_SQL.Open_Cursor;
587    DBMS_SQL.Parse(v_cursorid, l_dyn_query, DBMS_SQL.v7 );
588    --
589    IF l_debug_on THEN
590       WSH_DEBUG_SV.log(l_module_name, 'Opened and Pasred cursor successfully', v_cursorid);
591    END IF;
592    --
593    --Using Define_Array to fetch in BULK based on l_bulk_count.
594    --Index of PL/SQL table will start from 1.
595    DBMS_SQL.Define_Array(v_cursorid,  1, l_rowid_tbl           , l_bulk_count, 1);
596    DBMS_SQL.Define_Array(v_cursorid,  2, l_del_detail_tbl      , l_bulk_count, 1);
597    DBMS_SQL.Define_Array(v_cursorid,  3, l_src_line_id_tbl     , l_bulk_count, 1);
598    DBMS_SQL.Define_Array(v_cursorid,  4, l_org_id_tbl          , l_bulk_count, 1);
599    DBMS_SQL.Define_Array(v_cursorid,  5, l_curr_code_tbl       , l_bulk_count, 1);
600    DBMS_SQL.Define_Array(v_cursorid,  6, l_customer_id_tbl     , l_bulk_count, 1);
601    DBMS_SQL.Define_Array(v_cursorid,  7, l_organization_id_tbl , l_bulk_count, 1);
602    DBMS_SQL.Define_Array(v_cursorid,  8, l_ship_from_loc_tbl   , l_bulk_count, 1);
603    DBMS_SQL.Define_Array(v_cursorid,  9, l_ship_to_site_tbl    , l_bulk_count, 1);
604    DBMS_SQL.Define_Array(v_cursorid, 10, l_invoice_to_site_tbl , l_bulk_count, 1);
605    DBMS_SQL.Define_Array(v_cursorid, 11, l_deliver_to_site_tbl , l_bulk_count, 1);
606    DBMS_SQL.Define_Array(v_cursorid, 12, l_ship_to_con_tbl     , l_bulk_count, 1);
607    DBMS_SQL.Define_Array(v_cursorid, 13, l_invoice_to_con_tbl  , l_bulk_count, 1);
608    DBMS_SQL.Define_Array(v_cursorid, 14, l_deliver_to_con_tbl  , l_bulk_count, 1);
609    DBMS_SQL.Define_Array(v_cursorid, 15, l_ship_method_tbl     , l_bulk_count, 1);
610    DBMS_SQL.Define_Array(v_cursorid, 16, l_freight_terms_tbl   , l_bulk_count, 1);
611    DBMS_SQL.Define_Array(v_cursorid, 17, l_fob_code_tbl        , l_bulk_count, 1);
612    DBMS_SQL.Define_Array(v_cursorid, 18, l_group_id_tbl        , l_bulk_count, 1);
613 
614    --Assigning Bind Values
615    -- { Binding Starts Here
616    IF p_organization_id is not null THEN
617       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_organization_id', p_organization_id);
618    END IF;
619 
620    IF p_customer_id is not null THEN
621       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_customer_id', p_customer_id);
622    END IF;
623 
624    IF p_ship_to_location_id is not null THEN
625       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_ship_to_location_id', p_ship_to_location_id);
626    END IF;
627 
628    IF p_transaction_type_id is not null THEN
629       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_transaction_type_id', p_transaction_type_id);
630    END IF;
631 
632    IF p_from_order_number is not null and p_to_order_number is not null
633    THEN
634       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_from_order_number', p_from_order_number);
635       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_to_order_number', p_to_order_number);
636    ELSIF p_from_order_number is not null and p_to_order_number is null
637    THEN
638       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_from_order_number', p_from_order_number);
639    ELSIF p_from_order_number is null and p_to_order_number is not null
640    THEN
641       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_to_order_number', p_to_order_number);
642    END IF;
643 
644    IF l_from_request_date is not null and l_to_request_date is not null
645    THEN
646       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_from_request_date', l_from_request_date);
647       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_to_request_date', l_to_request_date);
648    ELSIF l_from_request_date is not null and l_to_request_date is null
649    THEN
650       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_from_request_date', l_from_request_date);
651    ELSIF l_from_request_date is null and l_to_request_date is not null
652    THEN
653       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_to_request_date', l_to_request_date);
654    END IF;
655 
656    IF l_from_schedule_date is not null and l_to_schedule_date is not null
657    THEN
658       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_from_schedule_date', l_from_schedule_date);
659       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_to_schedule_date', l_to_schedule_date);
660    ELSIF l_from_schedule_date is not null and l_to_schedule_date is null
661    THEN
662       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_from_schedule_date', l_from_schedule_date);
663    ELSIF l_from_schedule_date is null and l_to_schedule_date is not null
664    THEN
665       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_to_schedule_date', l_to_schedule_date);
666    END IF;
667 
668    IF p_shipment_priority is not null THEN
669       DBMS_SQL.BIND_VARIABLE(v_cursorid, ':x_shipment_priority', p_shipment_priority);
670    END IF;
671    -- } Binding End Here
672 
673 
674    -- A current index into each array is maintained automatically. This index is initialized
675    -- to 1 at EXECUTE and keeps getting updated every time a COLUMN_VALUE call
676    -- is made. If you reexecute at any point, then the current index for each DEFINE is
677    -- re-initialized to 1.
678    v_ignore := DBMS_SQL.Execute(v_cursorid);
679 
680    l_prev_group_id := 0;
681    l_line_number   := 0;
682    --{ Loop for fetching records - Starts
683    LOOP
684       -- Each time when FETCH_ROWS is called, it fetches l_bulk_count(1000) rows and
685       -- are kept in DBMS_SQL buffers. When the COLUMN_VALUE call is run, those rows
686       -- move into the PL/SQL table specified (in this case l_rowid_tbl..l_group_id_tbl), at positions 1 to l_bulk_count(1000),
687       -- as specified in the DEFINE statements.
688       -- When the second batch is fetched in the loop, the rows go to positions 1001 to 2000; and so on.
689       v_ignore := DBMS_SQL.Fetch_Rows(v_cursorid);
690       IF v_ignore = 0 THEN
691          EXIT;
692       END IF;
693 
694       DBMS_SQL.Column_Value(v_cursorid,  1, l_rowid_tbl           );
695       DBMS_SQL.Column_Value(v_cursorid,  2, l_del_detail_tbl      );
696       DBMS_SQL.Column_Value(v_cursorid,  3, l_src_line_id_tbl     );
697       DBMS_SQL.Column_Value(v_cursorid,  4, l_org_id_tbl          );
698       DBMS_SQL.Column_Value(v_cursorid,  5, l_curr_code_tbl       );
699       DBMS_SQL.Column_Value(v_cursorid,  6, l_customer_id_tbl     );
700       DBMS_SQL.Column_Value(v_cursorid,  7, l_organization_id_tbl );
701       DBMS_SQL.Column_Value(v_cursorid,  8, l_ship_from_loc_tbl   );
702       DBMS_SQL.Column_Value(v_cursorid,  9, l_ship_to_site_tbl    );
703       DBMS_SQL.Column_Value(v_cursorid, 10, l_invoice_to_site_tbl );
704       DBMS_SQL.Column_Value(v_cursorid, 11, l_deliver_to_site_tbl );
705       DBMS_SQL.Column_Value(v_cursorid, 12, l_ship_to_con_tbl     );
706       DBMS_SQL.Column_Value(v_cursorid, 13, l_invoice_to_con_tbl  );
707       DBMS_SQL.Column_Value(v_cursorid, 14, l_deliver_to_con_tbl  );
708       DBMS_SQL.Column_Value(v_cursorid, 15, l_ship_method_tbl     );
709       DBMS_SQL.Column_Value(v_cursorid, 16, l_freight_terms_tbl   );
710       DBMS_SQL.Column_Value(v_cursorid, 17, l_fob_code_tbl        );
711       DBMS_SQL.Column_Value(v_cursorid, 18, l_group_id_tbl        );
712 
713       --
714       IF l_debug_on THEN
715          WSH_DEBUG_SV.log(l_module_name, 'Fetched successfully', v_ignore);
716          WSH_DEBUG_SV.log(l_module_name, 'Group Id Table Count', l_group_id_tbl.count);
717          WSH_DEBUG_SV.log(l_module_name, 'Group Id for first delivery detail# ' || l_del_detail_tbl(l_del_detail_tbl.first), l_group_id_tbl(l_group_id_tbl.first) );
718       END IF;
719       --
720 
721       --{ Loop through records fetched - Starts
722       FOR i IN l_group_id_tbl.FIRST..l_group_id_tbl.LAST
723       LOOP
724          --{ Locking Delivery Detail Starts
725          BEGIN
726             select source_header_id
727             into   l_src_header_id
728             from   wsh_delivery_details
729             where  rowid = l_rowid_tbl(i)
730             for update nowait;
731 
732             --Bug 14620571 - Start
733             IF p_include_lines_with_hold = 'N' THEN
734                IF l_debug_on THEN
735                         WSH_DEBUG_SV.logmsg(l_module_name,  'CHECKING FOR CREDIT CHECK/HOLDS'  );
736                         WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DETAILS_VALIDATIONS.CHECK_CREDIT_HOLDS',WSH_DEBUG_SV.C_PROC_LEVEL);
737                 END IF;
738                 --Check for Credit
739                 WSH_DETAILS_VALIDATIONS.Check_Credit_Holds(
740                           p_detail_id           => l_del_detail_tbl(i),
741                           p_activity_type       => 'SB_PICK',
742                           p_source_line_id      => l_src_line_id_tbl(i),
743                           p_source_header_id    => l_src_header_id,
744                           p_source_code         => 'OE',
745                           p_init_flag           => 'N',
746                           x_return_status       => l_return_status);
747 
748                IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
749                   RAISE Credit_or_Hold_Failed;
750                END IF;
751             END IF;
752             --Bug 14620571 - End
753 
754             --Delivery Detail can be assigned to batch.
755             l_wdd_update_flag(i) := 'Y';
756              --To check if atleast one WDD needs to be updated
757             IF NOT l_update_flag THEN
758                l_update_flag := TRUE;
759             END IF;
760 
761          EXCEPTION
762          WHEN Credit_or_Hold_Failed THEN
763             x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
764             --Delivery Detail cannot be assigned to batch.
765             l_wdd_update_flag(i)  := 'N';
766             l_dd_line_num_tbl(i) := 0;
767             l_dd_batch_id_tbl(i) := 0;
768             --
769             IF l_debug_on THEN
770                WSH_DEBUG_SV.log(l_module_name, 'Warning: Credit Check failed or Hold exists for detail', l_del_detail_tbl(i) );
771             END IF;
772             --
773             goto skip_record;
774          WHEN RECORD_LOCKED THEN
775             x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
776             --Delivery Detail cannot be assigned to batch.
777             l_wdd_update_flag(i)  := 'N';
778             l_dd_line_num_tbl(i) := 0;
779             l_dd_batch_id_tbl(i) := 0;
780             FND_MESSAGE.Set_Name('WSH', 'WSH_DLVB_LOCK_FAILED');
781             FND_MESSAGE.Set_Token('ENTITY_NAME', l_del_detail_tbl(i));
782             WSH_UTIL_CORE.Add_Message(x_return_status, l_module_name );
783             --
784             IF l_debug_on THEN
785                WSH_DEBUG_SV.log(l_module_name,'Warning: Not able to obtain lock, so skipping delivery detail', l_del_detail_tbl(i) );
786             END IF;
787             --
788             goto skip_record;
789          END;
790          --} Locking Delivery Detail Ends
791 
792          --{ Seperating Batches Starts Here
793          IF l_group_id_tbl(i) <> l_prev_group_id
794          THEN
795             l_prev_group_id := l_group_id_tbl(i);
796             l_line_number   := 0;
797 
798             l_cnt := l_shipment_batch_tbl.count + 1;
799             l_shipment_batch_tbl(l_cnt).customer_id           := l_customer_id_tbl(i);
800             l_shipment_batch_tbl(l_cnt).organization_id       := l_organization_id_tbl(i);
801             l_shipment_batch_tbl(l_cnt).ship_from_location_id := l_ship_from_loc_tbl(i);
802             l_shipment_batch_tbl(l_cnt).org_id                := l_org_id_tbl(i);
803             l_shipment_batch_tbl(l_cnt).currency_code         := l_curr_code_tbl(i);
804             l_shipment_batch_tbl(l_cnt).ship_to_site_use_id   := l_ship_to_site_tbl(i);
805             l_shipment_batch_tbl(l_cnt).group_id              := l_group_id_tbl(i);
806 
807             IF nvl(l_grp_by_inv_site, 'Y') = 'Y' THEN
808                l_shipment_batch_tbl(l_cnt).invoice_to_site_use_id := l_invoice_to_site_tbl(i);
809             END IF;
810 
811             IF nvl(l_grp_by_del_site, 'Y') = 'Y' THEN
812                l_shipment_batch_tbl(l_cnt).deliver_to_site_use_id := l_deliver_to_site_tbl(i);
813             END IF;
814 
815             IF nvl(l_grp_by_ship_contact, 'Y') = 'Y' THEN
816                l_shipment_batch_tbl(l_cnt).ship_to_contact_id := l_ship_to_con_tbl(i);
817             END IF;
818             IF nvl(l_grp_by_inv_contact, 'Y') = 'Y' THEN
819                l_shipment_batch_tbl(l_cnt).invoice_to_contact_id := l_invoice_to_con_tbl(i);
820             END IF;
821 
822             IF nvl(l_grp_by_del_contact, 'Y') = 'Y' THEN
823                l_shipment_batch_tbl(l_cnt).deliver_to_contact_id := l_deliver_to_con_tbl(i);
824             END IF;
825 
826             IF nvl(l_grp_by_ship_method, 'Y') = 'Y' THEN
827                l_shipment_batch_tbl(l_cnt).ship_method_code := l_ship_method_tbl(i);
828             END IF;
829 
830             IF nvl(l_grp_by_freight_terms, 'Y') = 'Y' THEN
831                l_shipment_batch_tbl(l_cnt).freight_terms_code := l_freight_terms_tbl(i);
832             END IF;
833 
834             IF nvl(l_grp_by_fob_code, 'Y') = 'Y' THEN
835                l_shipment_batch_tbl(l_cnt).fob_code := l_fob_code_tbl(i);
836             END IF;
837 
838             SELECT Wsh_Shipment_Batches_S.nextval
839             INTO   l_batch_id
840             FROM   DUAL;
841 
842             --Stores batch id of batches created.
843             l_batch_tbl(l_batch_tbl.count+1) := l_batch_id;
844             l_raise_batch_tbl(l_raise_batch_tbl.count+1) := l_batch_id; --Fulfillment Batch XML Project
845 
846             INSERT INTO Wsh_Shipment_Batches (
847                         batch_id,
848                         name,
849                         org_id,
850                         currency_code,
851                         customer_id,
852                         organization_id,
853                         ship_from_location_id,
854                         ship_to_site_use_id,
855                         invoice_to_site_use_id,
856                         deliver_to_site_use_id,
857                         ship_to_contact_id,
858                         invoice_to_contact_id,
859                         deliver_to_contact_id,
860                         ship_method_code,
861                         freight_terms_code,
862                         fob_code,
863                         pending_request_flag,
864                         creation_date,
865                         created_by,
866                         last_update_date,
867                         last_updated_by,
868                         last_update_login,
869                         request_id,
870                         program_application_id,
871                         program_id,
872                         program_update_date )
873             VALUES (
874                         l_batch_id,
875                         to_char(l_batch_id),
876                         l_shipment_batch_tbl(l_cnt).org_id,
877                         l_shipment_batch_tbl(l_cnt).currency_code,
878                         l_shipment_batch_tbl(l_cnt).customer_id,
879                         l_shipment_batch_tbl(l_cnt).organization_id,
880                         l_shipment_batch_tbl(l_cnt).ship_from_location_id,
881                         l_shipment_batch_tbl(l_cnt).ship_to_site_use_id,
882                         l_shipment_batch_tbl(l_cnt).invoice_to_site_use_id,
883                         l_shipment_batch_tbl(l_cnt).deliver_to_site_use_id,
884                         l_shipment_batch_tbl(l_cnt).ship_to_contact_id,
885                         l_shipment_batch_tbl(l_cnt).invoice_to_contact_id,
886                         l_shipment_batch_tbl(l_cnt).deliver_to_contact_id,
887                         l_shipment_batch_tbl(l_cnt).ship_method_code,
888                         l_shipment_batch_tbl(l_cnt).freight_terms_code,
889                         l_shipment_batch_tbl(l_cnt).fob_code,
890                         'Y',
891                         SYSDATE,
892                         FND_GLOBAL.user_id,
893                         SYSDATE,
894                         FND_GLOBAL.user_id,
895                         FND_GLOBAL.Login_Id,
896                         FND_GLOBAL.Conc_Request_Id,
897                         FND_GLOBAL.Prog_Appl_Id,
898                         FND_GLOBAL.Conc_Program_Id,
899                         SYSDATE );
900          END IF;
901          --} Seperating Batches Ends Here
902 
903          l_dd_batch_id_tbl(i) := l_batch_id;
904          l_line_number        := l_line_number + 1;
905          l_dd_line_num_tbl(i) := l_line_number;
906 
907          <<skip_record>>
908             null;
909       END LOOP;
910       --} Loop through records fetched - Ends
911 
912       --
913       IF l_debug_on THEN
914          WSH_DEBUG_SV.log(l_module_name, 'Group Id for last delivery detail# ' || l_del_detail_tbl(l_del_detail_tbl.last), l_group_id_tbl(l_group_id_tbl.last) );
915          WSH_DEBUG_SV.log(l_module_name, 'No. of lines fetched',   l_del_detail_tbl.count);
916          WSH_DEBUG_SV.log(l_module_name, 'No. of batches created', l_shipment_batch_tbl.count);
917       END IF;
918       --
919       IF l_update_flag THEN
920          FORALL i in l_rowid_tbl.first..l_rowid_tbl.last
921             update wsh_delivery_details
922             set    shipment_batch_id      = l_dd_batch_id_tbl(i),
923                    shipment_line_number   = l_dd_line_num_tbl(i),
924                    reference_line_id      = l_src_line_id_tbl(i),
925                    last_update_date       = SYSDATE,
926                    last_updated_by        = FND_GLOBAL.User_Id,
927                    last_update_login      = FND_GLOBAL.Login_Id,
928                    request_id             = FND_GLOBAL.Conc_Request_Id,
929                    program_application_id = FND_GLOBAL.Prog_Appl_Id,
930                    program_id             = FND_GLOBAL.Conc_Program_Id,
931                    program_update_date    = SYSDATE
932             where  rowid = l_rowid_tbl(i)
933             and    l_wdd_update_flag(i) = 'Y';
934 
935          l_upd_count := sql%rowcount;
936 
937          --
938          IF l_debug_on THEN
939             WSH_DEBUG_SV.log(l_module_name, 'No. of delivery details updated', l_upd_count);
940          END IF;
941          --
942       END IF;
943 
944       --Raise Business Event --Fulfillment Batch XML Project --Start
945       IF  (l_dist_src_entity_profile = 'B') AND (NVL(l_dist_flag, '!') = 'Y') THEN
946 
947         IF l_debug_on THEN
948            WSH_DEBUG_SV.logmsg(l_module_name, 'Raising Business Event for Batch');
949         END IF;
950 
951         FOR i IN 1..l_raise_batch_tbl.COUNT LOOP
952 
953          IF l_debug_on THEN
954             wsh_debug_sv.log (l_module_name, 'Batch Id ' , l_raise_batch_tbl(i));
955          END IF;
956 
957          -- Initializing the Transactions_History Record for BATCH.
958 
959          l_txns_history_rec.entity_number := l_raise_batch_tbl(i);
960          l_txns_history_rec.document_type := 'SR';
961          l_txns_history_rec.transaction_status := 'IP';
962          l_txns_history_rec.entity_type := 'BATCH';
963          l_txns_history_rec.action_type := 'A';
964          l_txns_history_rec.trading_partner_id := p_organization_id;
965          l_txns_history_rec.document_direction := 'O';
966          l_txns_history_rec.item_type := 'WSHBATO';
967          l_txns_history_rec.event_name := 'oracle.apps.wsh.batch.bsro';
968 
969          SELECT to_char(WSH_DOCUMENT_NUMBER_S.nextval) INTO l_txns_history_rec.document_number FROM dual;
970          IF l_debug_on THEN
971             wsh_debug_sv.log(l_module_name, 'DOCUMENT_NUMBER',l_txns_history_rec.document_number);
972          END IF;
973 
974          WSH_EXTERNAL_INTERFACE_SV.RAISE_EVENT(l_txns_history_rec, NULL, l_be_return_status);
975 
976          IF l_debug_on THEN
977             WSH_DEBUG_SV.log(l_module_name, 'Return Status of Raise Business Event', l_be_return_status);
978          END IF;
979          IF (l_be_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
980             raise wsh_raise_event_error;
981          END IF;
982         END LOOP;
983 
984       END IF; --Profile and Distributed Org Check
985 
986      --Fulfillment Batch XML Project --End
987 
988       l_rowid_tbl.delete;
989       l_del_detail_tbl.delete;
990       l_src_line_id_tbl.delete;
991       l_org_id_tbl.delete;
992       l_customer_id_tbl.delete;
993       l_organization_id_tbl.delete;
994       l_ship_from_loc_tbl.delete;
995       l_ship_to_site_tbl.delete;
996       l_invoice_to_site_tbl.delete;
997       l_deliver_to_site_tbl.delete;
998       l_ship_to_con_tbl.delete;
999       l_invoice_to_con_tbl.delete;
1000       l_deliver_to_con_tbl.delete;
1001       l_ship_method_tbl.delete;
1002       l_freight_terms_tbl.delete;
1003       l_fob_code_tbl.delete;
1004       l_group_id_tbl.delete;
1005       l_shipment_batch_tbl.delete;
1006       l_dd_line_num_tbl.delete;
1007       l_wdd_update_flag.delete;
1008       l_dd_batch_id_tbl.delete;
1009       l_raise_batch_tbl.delete; --Fulfillment Batch XML Project
1010 
1011       IF v_ignore < l_bulk_count THEN
1012          EXIT;
1013       END IF;
1014    END LOOP;
1015    --} Loop for fetching records - Ends
1016 
1017    -- Closing Dynamic Cursor
1018    IF DBMS_SQL.Is_Open(v_cursorid) THEN
1019       --
1020       IF l_debug_on THEN
1021          WSH_DEBUG_SV.logmsg(l_module_name, 'Closing cursor');
1022       END IF;
1023       --
1024       DBMS_SQL.Close_Cursor(v_cursorid);
1025       v_cursorid := null;
1026    END IF;
1027 
1028    --
1029    IF l_debug_on THEN
1030       WSH_DEBUG_SV.log(l_module_name, 'Total No. of shipment batches created', l_batch_tbl.count);
1031       WSH_DEBUG_SV.logmsg(l_module_name, '----------------------------------------------');
1032    ELSIF nvl(FND_GLOBAL.Conc_Request_Id, 0) > 0 THEN
1033       FND_FILE.put_line(FND_FILE.LOG, 'Total No. of shipment batches created => ' || l_batch_tbl.count);
1034       FND_FILE.put_line(FND_FILE.LOG, '----------------------------------------------');
1035    END IF;
1036    --
1037 
1038    --
1039    IF l_debug_on OR
1040       nvl(FND_GLOBAL.Conc_Request_Id, 0) > 0
1041    THEN
1042       IF l_batch_tbl.count > 0 THEN
1043          FOR i in l_batch_tbl.first..l_batch_tbl.last
1044          LOOP
1045             IF l_debug_on THEN
1046                WSH_DEBUG_SV.logmsg(l_module_name, i || '. Shipment Batch Id => ' || l_batch_tbl(i));
1047             ELSIF nvl(FND_GLOBAL.Conc_Request_Id, 0) > 0 THEN
1048                FND_FILE.put_line(FND_FILE.LOG, i || '. Shipment Batch Id => ' || l_batch_tbl(i));
1049             END IF;
1050          END LOOP;
1051       END IF;
1052    END IF;
1053    --
1054 
1055    --
1056    IF l_debug_on THEN
1057       WSH_DEBUG_SV.logmsg(l_module_name, '----------------------------------------------');
1058       WSH_DEBUG_SV.pop(l_module_name);
1059    ELSIF nvl(FND_GLOBAL.Conc_Request_Id, 0) > 0 THEN
1060       FND_FILE.put_line(FND_FILE.LOG, '----------------------------------------------');
1061    END IF;
1062    --
1063 
1064 EXCEPTION
1065    when FND_API.G_EXC_ERROR then
1066       x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1067       --
1068       IF l_debug_on THEN
1069          wsh_debug_sv.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1070          wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
1071       END IF;
1072       --
1073    WHEN wsh_raise_event_error THEN  --Fulfillment Batch XML Project
1074       x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1075       IF l_debug_on THEN
1076          wsh_debug_sv.logmsg(l_module_name,'wsh_raise_event_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1077          wsh_debug_sv.pop(l_module_name,'EXCEPTION:wsh_raise_event_error');
1078       END IF;
1079    WHEN OTHERS THEN
1080       x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1081       --
1082       IF l_debug_on THEN
1083         WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1084         WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1085       END IF;
1086       --
1087 END Create_Shipment_Batch;
1088 --
1089 --=============================================================================
1090 -- PUBLIC PROCEDURE :
1091 --       Cancel_Line
1092 --
1093 -- PARAMETERS:
1094 --       p_document_number      => Shipment Batch Document Number
1095 --       p_line_number          => Shipment Batch Line Number
1096 --       p_cancel_quantity      => quantity to unassign from Shipment batch
1097 --       x_return_status        => Return Status of API (S,E,U)
1098 --
1099 -- COMMENT:
1100 --       Delivery line(s) corresponding to document number and document line
1101 --       number will be unassigned from Shipment Batch till the cancel quantity
1102 --       is met.
1103 --
1104 --=============================================================================
1105 --
1106 PROCEDURE Cancel_Line(
1107           p_document_number      IN  VARCHAR2,
1108           p_line_number          IN  VARCHAR2,
1109           p_cancel_quantity      IN  NUMBER,
1110           x_return_status        OUT NOCOPY    VARCHAR2 )
1111 IS
1112    CURSOR c_del_details IS
1113       select src_requested_quantity,
1114              requested_quantity,
1115              delivery_detail_id
1116       from   wsh_delivery_details wdd,
1117              wsh_transactions_history wth,
1118              wsh_shipment_batches wsb
1119       where  source_code = 'OE'
1120       and    released_status in ( 'R', 'B', 'X' )
1121       and    wdd.shipment_batch_id = wsb.batch_id
1122       and    wsb.name = entity_number
1123       and    shipment_line_number = p_line_number
1124       and    entity_type = 'BATCH'
1125       and    document_direction = 'O'
1126       and    document_type = 'SR'
1127       and    document_number = p_document_number
1128       order  by requested_quantity desc
1129       for update nowait;
1130 
1131    l_new_detail_id          NUMBER;
1132    l_avlb_quantity          NUMBER := 0;
1133    l_pending_cancel_qty     NUMBER;
1134    l_cancel_quantity        NUMBER;
1135    l_cancel_quantity2       NUMBER;
1136 
1137    l_return_status          VARCHAR2(1);
1138 
1139    l_unassign_detail_tab    WSH_UTIL_CORE.Id_Tab_Type;
1140 
1141    RECORD_LOCKED                EXCEPTION;
1142    PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
1143 
1144    --
1145    l_debug_on               BOOLEAN;
1146    l_module_name CONSTANT   VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Cancel_Line';
1147    --
1148 BEGIN
1149    --
1150    l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1151    --
1152    IF l_debug_on IS NULL
1153    THEN
1154        l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1155    END IF;
1156    --
1157    IF l_debug_on THEN
1158       WSH_DEBUG_SV.push(l_module_name);
1159       --
1160       WSH_DEBUG_SV.log(l_module_name, 'p_document_number', p_document_number);
1161       WSH_DEBUG_SV.log(l_module_name, 'p_line_number', p_line_number);
1162       WSH_DEBUG_SV.log(l_module_name, 'p_cancel_quantity', p_cancel_quantity);
1163    END IF;
1164    --
1165    x_return_status      := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1166    l_pending_cancel_qty := p_cancel_quantity;
1167 
1168    --{ Cursor Loops Ends
1169    FOR i in c_del_details
1170    LOOP
1171       l_avlb_quantity := l_avlb_quantity + i.requested_quantity;
1172 
1173       IF i.requested_quantity > l_pending_cancel_qty
1174       THEN
1175       --{ Requested Quantity Greater than Cancel Quantity - Starts
1176          l_cancel_quantity := l_pending_cancel_qty;
1177          l_pending_cancel_qty := 0;
1178          --
1179          IF l_debug_on THEN
1180             WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details', WSH_DEBUG_SV.C_PROC_LEVEL);
1181          END IF;
1182          --
1183 
1184          WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details (
1185                   p_from_detail_id   =>  i.delivery_detail_id,
1186                   p_req_quantity     =>  l_cancel_quantity    ,
1187                   x_new_detail_id    =>  l_new_detail_id     ,
1188                   x_return_status    =>  l_return_status     );
1189 
1190          --
1191          IF l_debug_on THEN
1192             WSH_DEBUG_SV.log(l_module_name, 'Return Status of WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details', l_return_status);
1193          END IF;
1194          --
1195 
1196          IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1197             x_return_status := l_return_status;
1198             RAISE FND_API.G_EXC_ERROR;
1199          ELSE
1200             l_unassign_detail_tab(l_unassign_detail_tab.count+1) := l_new_detail_id;
1201          END IF;
1202       --} Requested Quantity Greater than Cancel Quantity - Ends
1203       ELSE
1204          l_unassign_detail_tab(l_unassign_detail_tab.count+1) := i.delivery_detail_id;
1205          l_pending_cancel_qty := l_pending_cancel_qty - i.requested_quantity;
1206       END IF;
1207 
1208       EXIT WHEN l_pending_cancel_qty = 0;
1209    END LOOP;
1210 
1211    IF l_pending_cancel_qty > 0 THEN
1212       --
1213       IF l_debug_on THEN
1214          WSH_DEBUG_SV.logmsg(l_module_name, 'Cancel Quantity ' || p_cancel_quantity || ' is greater than open quantity ' || l_avlb_quantity);
1215       END IF;
1216       --
1217       RAISE FND_API.G_EXC_ERROR;
1218    END IF;
1219 
1220    --
1221    IF l_debug_on THEN
1222       WSH_DEBUG_SV.log(l_module_name, 'Unassign delivery detail count', l_unassign_detail_tab.count);
1223    END IF;
1224    --
1225    IF l_unassign_detail_tab.count > 0 THEN
1226       FORALL i in l_unassign_detail_tab.first..l_unassign_detail_tab.last
1227          update wsh_delivery_details
1228          set    shipment_batch_id      = null,
1229                 shipment_line_number   = null,
1230                 reference_line_id      = null,
1231                 last_update_date       = SYSDATE,
1232                 last_updated_by        = FND_GLOBAL.User_Id,
1233                 last_update_login      = FND_GLOBAL.Login_Id,
1234                 request_id             = FND_GLOBAL.Conc_Request_Id,
1235                 program_application_id = FND_GLOBAL.Prog_Appl_Id,
1236                 program_id             = FND_GLOBAL.Conc_Program_Id,
1237                 program_update_date    = SYSDATE
1238          where  delivery_detail_id = l_unassign_detail_tab(i);
1239 
1240       --
1241       IF l_debug_on THEN
1242          WSH_DEBUG_SV.log(l_module_name, 'No. of delivery details Unassigned from Shipment Batch', sql%rowcount);
1243       END IF;
1244       --
1245    END IF;
1246 
1247    --
1248    IF l_debug_on THEN
1249       WSH_DEBUG_SV.log(l_module_name, 'Return Status', x_return_status);
1250       WSH_DEBUG_SV.pop(l_module_name);
1251    END IF;
1252    --
1253 EXCEPTION
1254    WHEN RECORD_LOCKED THEN
1255      x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1256      --
1257      IF l_debug_on THEN
1258         WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1259         WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
1260      END IF;
1261      --
1262 
1263    WHEN FND_API.G_EXC_ERROR THEN
1264       IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1265          x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1266       END IF;
1267       --
1268       IF l_debug_on THEN
1269           WSH_DEBUG_SV.logmsg(l_module_name,'Rolling back the transactions');
1270       END IF;
1271       --
1272       rollback;
1273       IF l_debug_on THEN
1274           WSH_DEBUG_SV.logmsg(l_module_name, 'Error occured while spliting line');
1275           WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1276       END IF;
1277    WHEN others THEN
1278       x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1279       --
1280       IF l_debug_on THEN
1281           WSH_DEBUG_SV.logmsg(l_module_name,'Rolling back the transactions');
1282       END IF;
1283       --
1284       rollback;
1285       IF l_debug_on THEN
1286          WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1287          WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1288       END IF;
1289 END Cancel_Line;
1290 
1291 END WSH_SHIPMENT_BATCH_PKG;