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.INV_LABEL_PVT7

Source


1 PACKAGE BODY INV_LABEL_PVT7 AS
2 /* $Header: INVLAP7B.pls 120.7.12010000.2 2008/07/29 13:41:02 ptkumar ship $ */
3 
4 LABEL_B     CONSTANT VARCHAR2(50) := '<label';
5 LABEL_E     CONSTANT VARCHAR2(50) := '</label>'||fnd_global.local_chr(10);
6 VARIABLE_B  CONSTANT VARCHAR2(50) := '<variable name= "';
7 VARIABLE_E  CONSTANT VARCHAR2(50) := '</variable>'||fnd_global.local_chr(10);
8 TAG_E    CONSTANT VARCHAR2(50)  := '>'||fnd_global.local_chr(10);
9 l_debug number;
10 
11 
12 PROCEDURE trace(p_message IN VARCHAR2) iS
13 BEGIN
14       inv_label.trace(p_message, 'LABEL_SHIPPING');
15 END trace;
16 
17 FUNCTION get_total_number_of_lpns(p_delivery_id IN NUMBER) RETURN NUMBER
18 IS
19    l_lpn_count    NUMBER := 0;
20 BEGIN
21    SELECT count(*)  INTO l_lpn_count
22    FROM wsh_delivery_assignments_v
23    WHERE delivery_id = p_delivery_id
24    AND  parent_delivery_detail_id is null;
25       RETURN l_lpn_count;
26 EXCEPTION
27    WHEN no_data_found THEN
28    RETURN  0;
29 
30 END get_total_number_of_lpns;
31 
32    --Bug# 5051977. Added the following function.
33    --This function will return 'Y' if there is a label aready printed for this LPN
34    --and delivery; otherwise it returns 'N'. This is used only for pick drop.
35    FUNCTION check_duplicate_label ( p_delivery_id IN NUMBER, p_transaction_id IN NUMBER)
36    RETURN VARCHAR2
37    IS
38       CURSOR Dup_Staging_txn_cur IS
39           SELECT 'Y'
40           FROM mtl_material_transactions mmt ,
41                mtl_material_transactions_temp mmtt,
42                wsh_delivery_assignments wda,
43                wsh_delivery_details wdd
44           WHERE mmtt.transaction_temp_id     = p_transaction_id
45           AND wda.delivery_id                = p_delivery_id
46           AND mmt.organization_id            = mmtt.organization_id
47           AND mmt.transaction_source_type_id IN (INV_GLOBALS.G_SOURCETYPE_SALESORDER,INV_GLOBALS.G_SOURCETYPE_INTORDER)
48           AND mmt.transaction_action_id      = INV_GLOBALS.G_ACTION_STGXFR
49           AND mmtt.transfer_lpn_id           = mmt.transfer_lpn_id
50           AND mmt.move_order_line_id         = wdd.move_order_line_id
51           AND wda.delivery_detail_id         = wdd.delivery_detail_id
52           AND ROWNUM<2;
53 
54       l_dup_label  VARCHAR2(1):= 'N';
55       l_debug       NUMBER  := INV_LABEL.l_debug;
56    BEGIN
57          IF (l_debug = 1) THEN
58              trace('In duplicate label check for del:'|| p_delivery_id||'tranxid:'||p_transaction_id);
59          End if;
60          OPEN  Dup_Staging_txn_cur ;
61          FETCH Dup_Staging_txn_cur INTO l_dup_label ;
62          CLOSE Dup_Staging_txn_cur;
63          IF ( l_dup_label = 'Y' AND l_debug = 1 ) THEN
64               trace('There is a label already printed for this LPN and delivery.');
65          END IF;
66          RETURN l_dup_label;
67    EXCEPTION
68    WHEN OTHERS THEN
69          RETURN 'N';
70    END check_duplicate_label;
71    --End of fix for Bug# 5051977
72 
73 PROCEDURE get_variable_data(
74    x_variable_content   OUT NOCOPY INV_LABEL.label_tbl_type
75 ,  x_msg_count    OUT NOCOPY NUMBER
76 ,  x_msg_data     OUT NOCOPY VARCHAR2
77 ,  x_return_status      OUT NOCOPY VARCHAR2
78 ,  p_label_type_info IN INV_LABEL.label_type_rec
79 ,  p_transaction_id  IN NUMBER
80 ,  p_input_param     IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
81 ,  p_transaction_identifier IN NUMBER
82 ) IS
83 
84    l_delivery_id     NUMBER;
85    l_delivery_detail_id NUMBER;
86 
87    l_move_order_line_id NUMBER;
88 
89 ---------------------------------------------------------------------------------------------
90 -- Project: 'Custom Labels' (A 11i10+ Project)                                               |
91 -- Author: Dinesh ([email protected])                                                      |
92 -- Change Description:                                                                       |
93 --   Following variables were added (as a part of 11i10+ 'Custom Labels' Project)            |
94 --   to retrieve and hold the SQL Statement and it's result.                                 |
95 ---------------------------------------------------------------------------------------------
96    l_sql_stmt  VARCHAR2(4000);
97    l_sql_stmt_result VARCHAR2(4000) := NULL;
98    TYPE sql_stmt IS REF CURSOR;
99    c_sql_stmt sql_stmt;
100    l_custom_sql_ret_status VARCHAR2(1);
101    l_custom_sql_ret_msg VARCHAR2(2000);
102 
103    -- Fix for bug: 4179593 Start
104    l_CustSqlWarnFlagSet BOOLEAN;
105    l_CustSqlErrFlagSet BOOLEAN;
106    l_CustSqlWarnMsg VARCHAR2(2000);
107    l_CustSqlErrMsg VARCHAR2(2000);
108    -- Fix for bug: 4179593 End
109 
110  ------------------------End of this change for Custom Labels project code--------------------
111 
112    CURSOR  c_wdd_shipping IS
113    SELECT wda.delivery_id, wdd.organization_id, wdd.subinventory
114    FROM   wsh_delivery_assignments_v   wda, wsh_new_deliveries wnd
115           , wsh_delivery_details wdd
116    WHERE  wda.delivery_detail_id = p_transaction_id
117    AND    wnd.delivery_id        = wda.delivery_id
118    AND    wdd.delivery_detail_id(+) = wda.delivery_detail_id;
119 
120    CURSOR   c_cart_shipping IS
121    SELECT   wda.delivery_id, mmtt.organization_id, mmtt.subinventory_code
122    FROM  wsh_delivery_assignments_v wda, wsh_new_deliveries wnd,
123       wsh_delivery_details wdd, mtl_material_transactions_temp mmtt
124    WHERE    mmtt.move_order_line_id    = wdd.move_order_line_id
125    AND      wda.delivery_detail_id     = wdd.delivery_detail_id
126    AND      wnd.delivery_id            = wda.delivery_id
127    AND      mmtt.transaction_temp_id   = p_transaction_id;
128 
129    -- Fix for Bug# 3786272. Removed the 'wms_packaging_hist' from the 'FROM' clause.
130 
131    CURSOR c_cart_shipping_pkg IS
132    SELECT   DISTINCT(wda.delivery_id)
133    FROM  wsh_delivery_assignments_v wda, wsh_new_deliveries wnd,
134       wsh_delivery_details wdd, mtl_material_transactions_temp mmtt
135       --wms_packaging_hist wph
136    WHERE    mmtt.move_order_line_id    = wdd.move_order_line_id (+)
137    AND      wda.delivery_detail_id (+)    = wdd.delivery_detail_id
138    AND      wnd.delivery_id (+)           = wda.delivery_id
139    AND      mmtt.cartonization_id      = p_transaction_id; -- Bug 2374644
140 
141 
142    /* Bug 2072560:
143       The solution to this (in consultation with  Tharian and Janet), we will derive
144            the move_order_line_id from the mmtt and then with the move_order_line_id
145            derive the delivery_detail_id from the WDD and then eventually the delivery_id.
146    */
147    /*CURSOR c_mmtt_temp_id IS
148    SELECT move_order_line_id
149    FROM   mtl_material_transactions_temp mmtt
150    WHERE  mmtt.transaction_temp_id = p_transaction_id;
151 
152 
153 
154       CURSOR c_wdd_delivery_dtl_id IS
155    SELECT delivery_detail_id
156    FROM   wsh_delivery_details wdd
157    WHERE  wdd.move_order_line_id = l_move_order_line_id;
158 
159 
160    CURSOR c_wda_delivery_id IS
161    SELECT delivery_id
162    FROM   wsh_delivery_assignments_v wda
163    WHERE  wda.delivery_detail_id = l_delivery_detail_id;*/
164 
165    /* Combined the above three cursors into one */
166    CURSOR c_mmtt_pick_drop IS
167    SELECT wda.delivery_id, mmtt.organization_id, mmtt.transfer_subinventory
168    FROM   wsh_delivery_assignments_v wda, wsh_delivery_details wdd
169           , mtl_material_transactions_temp mmtt
170    WHERE  wda.delivery_detail_id = wdd.delivery_detail_id
171    AND    wdd.move_order_line_id = mmtt.move_order_line_id
172    AND    mmtt.transaction_temp_id = p_transaction_id;
173 
174    /* Added for Patchset J
175     * When calling label printing from Packing Workbench
176     * LPN_ID is passed to label printing
177     * Delivery ID can be derived with the LPN_ID
178     * New cursor to derive delivery_id with LPN */
179    CURSOR c_lpn_delivery(p_lpn_id NUMBER) IS
180       SELECT wda.delivery_id
181       FROM wsh_delivery_assignments_v wda
182           ,wsh_delivery_details wdd
183       WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
184       AND   wda.delivery_id IS NOT NULL
185       AND   wdd.lpn_id IS NOT NULL
186       AND   wdd.released_status = 'X'  -- For LPN reuse ER : 6845650
187       AND   exists(
188              select lpn_id from wms_license_plate_numbers
189              where lpn_id = wdd.lpn_id
190              and   outermost_lpn_id = p_lpn_id)
191       AND rownum<2;
192 
193 
194 
195    /* Patchset J- Select attribute1..attribute15, tp_attribute1..tp_attribute15
196     *, global_attribute1..global_attribute15, attribute_category,
197     * tp_attribute_category, global_attribute_category from wsh_new_deliveries
198     */
199 
200    CURSOR  c_delivery(p_delivery_id NUMBER) IS
201    SELECT     wnd.delivery_id       delivery_number
202              -- Added by joabraha for Bug 3549300
203            , wnd.customer_id       customer_id
204            , wnd.organization_id   organization_id
205              --
206                 /*Bug 3969347 -Added two columns ship_from_addressee
207                   and ship_to_addressee to obtain data
208                   for these label fields.*/
209                 , hro.name              ship_from_addressee
210                 --    , rac.customer_name     ship_to_addressee  Commented for Bug#4445708
211                 --End of fix for Bug 3969347
212                 , addr.addressee        ship_to_addressee  --Added for fix of Bug#4445708
213       , rac.customer_name     customer_name
214       , rac.customer_name     customer
215       , hrl.address_line_1    ship_from_address1
216       , hrl.address_line_2    ship_from_address2
217       , hrl.address_line_3    ship_from_address3
218       , hrl.address_line_4    ship_from_address4
219       , hrl.city              ship_from_city
220       , hrl.postal_code       ship_from_postal_code
221       , hrl.state             ship_from_state
222       , hrl.county            ship_from_county
223       , hrl.country           ship_from_country
224       , hrl.province          ship_from_province
225       , hrl1.address_line_1   ship_to_address1
226       , hrl1.address_line_2   ship_to_address2
227       , hrl1.address_line_3   ship_to_address3
228       , hrl1.address_line_4   ship_to_address4
229       , hrl1.city       ship_to_city
230       , hrl1.postal_code      ship_to_postal_code
231       , hrl1.state            ship_to_state
232       , hrl1.county           ship_to_county
233       , hrl1.country          ship_to_country
234       , hrl1.province         ship_to_province
235       -- Bug 2878652, get carrier name from wsh_carriers_v
236       -- Bug 5121507, Get carrier in order of Trip->Delivery
237       -- Get carrier_id first, then query carrier_name later
238       --, wcv.carrier_name      carrier
239       , nvl(wt.carrier_id, wnd.carrier_id) carrier_id
240       , null carrier
241       , wnd.waybill           waybill
242       , wnd.waybill           airbill
243       , wdi.sequence_number   bill_of_lading
244       -- Bug 2878652, get ship_method_name with ship_method_code
245       , fcl.meaning           ship_method
246       , wnd.gross_weight      shipment_gross_weight
247       , wnd.weight_uom_code   shipment_gross_weight_uom
248       , (wnd.gross_weight  -  wnd.net_weight)   shipment_tare_weight
249       , wnd.weight_uom_code   shipment_tare_weight_uom
250       , wnd.volume            shipment_volume
251       , wnd.volume_uom_code   shipment_volume_uom
252       , get_total_number_of_lpns(wnd.delivery_id) total_number_of_lpns
253            , wnd.attribute_category new_del_attribute_category
254            , wnd.attribute1        new_del_attribute1
255            , wnd.attribute2        new_del_attribute2
256            , wnd.attribute3        new_del_attribute3
257            , wnd.attribute4        new_del_attribute4
258            , wnd.attribute5        new_del_attribute5
259            , wnd.attribute6        new_del_attribute6
260            , wnd.attribute7        new_del_attribute7
261            , wnd.attribute8        new_del_attribute8
262            , wnd.attribute9        new_del_attribute9
263            , wnd.attribute10        new_del_attribute10
264            , wnd.attribute11        new_del_attribute11
265            , wnd.attribute12        new_del_attribute12
266            , wnd.attribute13        new_del_attribute13
267            , wnd.attribute14        new_del_attribute14
268            , wnd.attribute15        new_del_attribute15
269            , wnd.tp_attribute_category new_del_tp_attr_category
270            , wnd.tp_attribute1        new_del_tp_attr1
271            , wnd.tp_attribute2        new_del_tp_attr2
272            , wnd.tp_attribute3        new_del_tp_attr3
273            , wnd.tp_attribute4        new_del_tp_attr4
274            , wnd.tp_attribute5        new_del_tp_attr5
275            , wnd.tp_attribute6        new_del_tp_attr6
276            , wnd.tp_attribute7        new_del_tp_attr7
277            , wnd.tp_attribute8        new_del_tp_attr8
278            , wnd.tp_attribute9        new_del_tp_attr9
279            , wnd.tp_attribute10        new_del_tp_attr10
280            , wnd.tp_attribute11        new_del_tp_attr11
281            , wnd.tp_attribute12        new_del_tp_attr12
282            , wnd.tp_attribute13        new_del_tp_attr13
283            , wnd.tp_attribute14        new_del_tp_attr14
284            , wnd.tp_attribute15        new_del_tp_attr15
285            , wnd.global_attribute_category new_del_global_attr_category
286            , wnd.global_attribute1        new_del_global_attr1
287            , wnd.global_attribute2        new_del_global_attr2
288            , wnd.global_attribute3        new_del_global_attr3
289            , wnd.global_attribute4        new_del_global_attr4
290            , wnd.global_attribute5        new_del_global_attr5
291            , wnd.global_attribute6        new_del_global_attr6
292            , wnd.global_attribute7        new_del_global_attr7
293            , wnd.global_attribute8        new_del_global_attr8
294            , wnd.global_attribute9        new_del_global_attr9
295            , wnd.global_attribute10        new_del_global_attr10
296            , wnd.global_attribute11        new_del_global_attr11
297            , wnd.global_attribute12        new_del_global_attr12
298            , wnd.global_attribute13        new_del_global_attr13
299            , wnd.global_attribute14        new_del_global_attr14
300            , wnd.global_attribute15        new_del_global_attr15
301    FROM
302         --
303         -- Modification Start for Bug # - 4418524
304         --
305         -- As part of TCA related changes ra_customers, ra_contacts views are
306         -- obsoleted in R12. The columns fetched from these views are fetched
307         -- from hz_parties and hz_cust_accounts.
308         --
309         -- Following table alias are commented
310         --  ra_customers                 rac
311         --
312         -- Following Queries are added to replace the above commented
313         -- views
314         --
315            ( SELECT CUST_ACCT.cust_account_id customer_id,
316                     SUBSTRB(PARTY.party_name,1,50) customer_name
317              FROM hz_parties PARTY
318                      , hz_cust_accounts CUST_ACCT
319                   WHERE CUST_ACCT.party_id = PARTY.party_id
320            ) rac,
321              --
322              -- Modification End for Bug # - 4418524
323              --
324       (select loc.location_id location_id,loc.address_line_1 address_line_1
325             ,loc.address_line_2 address_line_2,loc.address_line_3 address_line_3
326             ,loc.loc_information13 address_line_4,loc.town_or_city city
327             ,loc.postal_code postal_code,loc.region_2 state,loc.region_1 county
328             ,loc.country country,loc.region_3 province
329             from hr_locations_all loc
330       union all
331       select hz.location_id location_id,hz.address1   address_line_1
332             ,hz.address2   address_line_2,hz.address3 address_line_3
333             ,hz.address4   address_line_4,hz.city city,hz.postal_code postal_code
334             ,hz.state state,hz.county county,hz.country country,hz.province province
335       from hz_locations hz   ) hrl,
336         --Bug 3969347 -Adding the table hr_organization_units
337              hr_organization_units hro,
338         --End of fix for Bug 3969347.
339       (select loc.location_id location_id,loc.address_line_1 address_line_1
340             ,loc.address_line_2 address_line_2,loc.address_line_3 address_line_3
341             ,loc.loc_information13 address_line_4,loc.town_or_city city
342             ,loc.postal_code postal_code,loc.region_2 state,loc.region_1 county
343             ,loc.country country,loc.region_3 province
344             from hr_locations_all loc
345       union all
346       select hz.location_id location_id,hz.address1   address_line_1
347             ,hz.address2   address_line_2,hz.address3 address_line_3
348             ,hz.address4   address_line_4,hz.city city,hz.postal_code postal_code
349             ,hz.state state,hz.county county,hz.country country,hz.province province
350       from hz_locations hz   ) hrl1,
351       wsh_new_deliveries    wnd,
352       wsh_delivery_legs     wdl,
353       wsh_document_instances  wdi
354       -- Bug 2878652, get carrier name and ship method name
355       , fnd_common_lookups fcl
356       -- Bug 5121507 Getting Carrier in the order of Trip->Delivery
357       --, wsh_carriers_v wcv
358       ,  wsh_trip_stops  wts
359       ,  wsh_trips       wt
360                 --Added to fix the issue reported in the Bug#4445708
361                 , ( select party_site.addressee addressee
362                     from wsh_delivery_details wdd
363                        , wsh_delivery_assignments wda
364                        , hz_cust_site_uses_all hcsua
365                        , hz_party_sites party_site
366                        , hz_loc_assignments loc_assign
367                        , hz_locations loc
368                        , hz_cust_acct_sites_all acct_site
369                     where wdd.delivery_detail_id = wda.delivery_detail_id
370                       and wda.delivery_id = p_delivery_id
371                       and wdd.container_flag = 'N'
372                       and hcsua.site_use_id = wdd.ship_to_site_use_id
373                       and acct_site.cust_acct_site_id = hcsua.cust_acct_site_id
374                       AND acct_site.party_site_id = party_site.party_site_id
375                       AND loc.location_id = party_site.location_id
376                       AND loc.location_id = loc_assign.location_id
377                       AND NVL ( acct_site.org_id, -99 )  = NVL ( loc_assign.org_id, -99 )
378                       and rownum = 1
379                   ) addr
380 
381    WHERE wnd.delivery_id      =  p_delivery_id
382    AND   rac.customer_id(+)   =  wnd.customer_id
383    AND   hrl.location_id(+)   =  wnd.INITIAL_PICKUP_LOCATION_ID
384    AND   hrl1.location_id(+)  =  wnd.ULTIMATE_DROPOFF_LOCATION_ID
385    AND   wdl.delivery_id (+)  =  wnd.delivery_id
386    AND   wdi.entity_id  (+)   =  wdl.delivery_leg_id
387    AND     wdi.entity_name  (+)    =     'WSH_DELIVERY_LEGS' -- Bug 3905110
388    --Bug 3969347 --Added this condition to join the table hr_organization_units.
389    AND     hro.organization_id(+)  =       wnd.organization_id
390     --End of fix for Bug 3969347
391    AND wdi.document_type(+) = 'BOL'
392    AND fcl.lookup_type(+) = 'SHIP_METHOD'
393    AND fcl.lookup_code(+) = wnd.ship_method_code
394    --Bug 5121507 Getting Carrier in the order of Trip->Delivery
395    AND wnd.delivery_id      = wdl.delivery_id(+)
396    AND wdl.pick_up_stop_id  = wts.stop_id (+)
397    AND wts.trip_id          = wt.trip_id (+);
398    --AND wcv.carrier_id(+) = wnd.carrier_id;
399 
400    l_delivery_data   LONG;
401    l_sales_order_header_id NUMBER; -- Introduced for bug: 5740331
402 
403    l_selected_fields    INV_LABEL.label_field_variable_tbl_type;
404    l_selected_fields_count NUMBER;
405 
406    l_delivery_rec_index    NUMBER := 0;
407 
408    l_label_format_id       NUMBER := 0 ;
409    l_label_format       VARCHAR2(100);
410    l_printer      VARCHAR2(30);
411 
412    l_api_name        VARCHAR2(20) := 'get_variable_data';
413 
414    l_return_status      VARCHAR2(240);
415 
416    l_error_message   VARCHAR2(240);
417    l_msg_count       NUMBER;
418    l_api_status      VARCHAR2(240);
419    l_msg_data     VARCHAR2(240);
420 
421    i        NUMBER;
422 
423    l_organization_id NUMBER;
424    l_subinventory_code  VARCHAR2(30) :=null;
425 
426    l_label_index NUMBER;
427    l_label_request_id NUMBER;
428 
429    --I cleanup, use l_prev_format_id to record the previous label format
430    l_prev_format_id      NUMBER;
431    -- I cleanup, user l_prev_sub to record the previous subinventory
432    --so that get_printer is not called if the subinventory is the same
433    l_prev_sub VARCHAR2(30);
434 
435    -- a list of columns that are selected for format
436    l_column_name_list LONG;
437 
438 BEGIN
439    -- Initialize return status as success
440    x_return_status := FND_API.G_RET_STS_SUCCESS;
441    -- If the transactions_id is passed, and the business flow code passed is in 6(Cross dock),
442    -- or 19(Pick Drop), then the transactions_id passed is the Delivery_Detail_Id.
443    -- Since we are printing the "Shipping"  label here, we will have to derive the delivery_id
444    -- from the delivery_detail_id and print the details for the Delivery Id .
445    -- If the business flow code passed is 21(Shipping) then the transaction id passed is the
446    -- delivery ID itself and so this can be directly assigned to the l_delivery_id
447 
448    -- For Pick Load(18), transaction_header_id is passed as p_transaction_id,
449    -- Delivery ID is obtained from MMTT and joining mmtt.move_order_line_id
450    -- to wdd.move_order_line_id.. All the MMTT record with that header_id
451    -- has the same move_order_line_id
452 
453     l_debug := INV_LABEL.l_debug;
454    IF (l_debug = 1) THEN
455       trace('**In Shipping label**');
456       trace('  Business_flow: '||p_label_type_info.business_flow_code);
457       trace('  Transaction ID:'||p_transaction_id);
458    END IF;
459 
460    -- Get l_delivery_id
461    IF p_transaction_id IS NOT NULL then
462       -- txn mode
463       IF p_label_type_info.business_flow_code in (6) THEN
464       -- means that the delivery_detail_id has been passed
465          OPEN c_wdd_shipping;
466          FETCH c_wdd_shipping INTO l_delivery_id, l_organization_id, l_subinventory_code;
467          IF c_wdd_shipping%NOTFOUND THEN
468             IF (l_debug = 1) THEN
469                trace(' No delivery_id found from WDD, no label print');
470             END IF;
471             CLOSE c_wdd_shipping;
472             RETURN;
473          ELSE
474             CLOSE c_wdd_shipping;
475          END IF;
476       ELSIF p_label_type_info.business_flow_code in (19) THEN
477       -- Old Design.
478       -- when the transactions manager calls printing for business flow of 'Pick Drop' (19) and passes the
479       -- transactions_temp_id, we derive the the transfer_lpn_id and the content_lpn_id from the MMTT and using either
480       -- the transfer_lpn_id  or content_lpn_id (imp: only if transfer_lpn_id is null) derive the delivery_detail_id
481       -- from the wsh_delivery_details.
482       -- There may be no delivery at Pick Load, Pick Drop, and Cartonization is the users fails to create one.
483       -- But a delivery ID will be automatically created after ship confirm.
484       -- This means that of the business flows of Pick Load, Pick Drop, and Cartonization there may be no delivery
485       -- label printed on some ocassions mentioned above.
486 
487       -- New Design:
488       -- Derive the move_order_line_id from the mmtt and then with the move_order_line_id derive the delivery_detail_id
489       -- from the WDD and then eventually the delivery_id.
490 
491       /*combined cursor c_mmtt_temp_id, c_wdd_delivery_dtl_id
492       , c_wda_delivery_id into one c_mmtt_pick_drop cursor*/
493          OPEN c_mmtt_pick_drop;
494          FETCH c_mmtt_pick_drop INTO l_delivery_id, l_organization_id, l_subinventory_code;
495 
496          IF c_mmtt_pick_drop%NOTFOUND THEN
497             IF (l_debug = 1) THEN
498                trace(' No record found in MMTT for given ID:'||p_transaction_id);
499             END IF;
500             CLOSE c_mmtt_pick_drop;
501             RETURN;
502          ELSE
503             CLOSE c_mmtt_pick_drop;
504             IF (l_debug = 1) THEN
505                trace(' Found delivery ID for pick drop:'||l_delivery_id);
506             END IF;
507          END IF;
508 
509       ELSIF p_label_type_info.business_flow_code in (21) THEN
510       -- means that the delivery_id has been passed
511          l_delivery_id := p_transaction_id ;
512 
513 	 -- Bug 5740331 - Added the following block to fetch the sales order header.
514          BEGIN
515           SELECT DISTINCT(source_header_id)
516           INTO  l_sales_order_header_id
517           FROM  wsh_delivery_details wdd,
518                     wsh_delivery_assignments wda
519           WHERE wdd.delivery_detail_id = wda.delivery_detail_id
520           AND   wda.delivery_id = l_delivery_id
521           AND   wdd.container_flag = 'N' ;
522 
523           IF (l_debug = 1) THEN
524                trace('Value of l_sales_order_header_id:'|| l_sales_order_header_id);
525           END IF;
526 
527           EXCEPTION
528           WHEN TOO_MANY_ROWS THEN
529 	  IF (l_debug = 1) THEN
530 		trace('In the exception for too many rows for Sales Order Header');
531           END IF;
532                 l_sales_order_header_id := NULL;
533           WHEN OTHERS THEN
534           IF (l_debug = 1) THEN
535 		trace('In the exception for too many rows for Sales Order Header');
536           END IF;
537 		l_sales_order_header_id := NULL;
538           END;
539           --End of fix for Bug 5740331
540 
541       ELSIF p_label_type_info.business_flow_code in (22) THEN
542          OPEN c_cart_shipping_pkg;
543          FETCH c_cart_shipping_pkg INTO l_delivery_id;
544             IF c_cart_shipping_pkg%NOTFOUND
545             THEN
546                IF (l_debug = 1) THEN
547                   trace(' No delivery_id found for cartonizationt');
548                END IF;
549                CLOSE c_cart_shipping_pkg;
550                RETURN;
551             ELSE
552                IF (l_debug = 1) THEN
553                   Trace('Found delivery_id for cartonization:' || l_delivery_id);
554                END IF;
555             END IF;
556 
557       ELSIF p_label_type_info.business_flow_code in (18, 34) THEN
558          OPEN c_cart_shipping;
559          FETCH c_cart_shipping INTO l_delivery_id, l_organization_id, l_subinventory_code;
560          IF c_cart_shipping%NOTFOUND THEN
561             IF (l_debug = 1) THEN
562                trace(' No delivery_id found from MMTT, no label print');
563             END IF;
564             CLOSE c_cart_shipping;
565             RETURN;
566          ELSE
567             CLOSE c_cart_shipping;
568          END IF;
569 
570       -- 18th February 2002 : Commented out below for fix to bug 2219171 for Qualcomm. Hence forth the
571       -- WMSTASKB.pls will be calling label printing at Pick Load with the
572       -- transaction_temp_id as opposed to the transaction_header_id earlier. This business flows(18)
573       -- have been added to  the above call.
574       -- ELSIF p_label_type_info.business_flow_code in (18) THEN
575       -- OPEN c_pickload_shipping;
576       -- FETCH c_pickload_shipping INTO l_delivery_id;
577       -- IF c_pickload_shipping%NOTFOUND THEN
578       --    trace(' No delivery_id found from MMTT with the header_id, no label print');
579       --    CLOSE c_pickload_shipping;
580       --    RETURN;
581       -- ELSE
582       --    CLOSE c_pickload_shipping;
583       -- END IF;
584 
585       ELSE
586          IF (l_debug = 1) THEN
587             trace(' Invalid business flow code '|| p_label_type_info.business_flow_code || ' No label print');
588          END IF;
589          RETURN;
590       END IF;
591    ELSE
592       -- manual mode.Manual request is from Jason's page.
593       -- We have to have an agreement that when they call
594       -- this API, they have to pass the delivery_id
595       -- in place of the transactions_temp_id.
596       l_delivery_id := p_input_param.transaction_temp_id;
597 
598       -- After patchset J, as per request from Packing Workbench
599       -- If delivery_id is not passed in directly
600       --  but LPN_ID is available
601       --  will derive the delivery_id with the LPN_ID
602       IF l_delivery_id IS NULL AND p_input_param.lpn_id IS NOT NULL THEN
603          OPEN c_lpn_delivery(p_input_param.lpn_id);
604          FETCH c_lpn_delivery INTO l_delivery_id;
605          CLOSE c_lpn_delivery;
606       END IF;
607 
608    END IF;
609 
610    IF (l_debug = 1) THEN
611       trace(' Got Delivery_id = '|| l_delivery_id);
612    END IF;
613 
614    IF l_delivery_id IS NULL THEN
615       IF (l_debug = 1) THEN
616          trace(' Delivery ID IS NULL, can not process ');
617       END IF;
618       RETURN;
619    END IF;
620 
621    IF (l_debug = 1) THEN
622       trace(' Getting selected fields ');
623    END IF;
624 
625    INV_LABEL.GET_VARIABLES_FOR_FORMAT(
626       x_variables       => l_selected_fields
627    ,  x_variables_count => l_selected_fields_count
628    ,  p_format_id    => p_label_type_info.default_format_id);
629 
630    IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
631       IF (l_debug = 1) THEN
632          trace('no fields defined for this format: ' || p_label_type_info.default_format_id || ',' ||p_label_type_info.default_format_name);
633       END IF;
634       --return;
635    END IF;
636 
637    IF (l_debug = 1) THEN
638       trace(' Found variable defined for this format, cont = ' || l_selected_fields_count);
639    END IF;
640 
641    l_delivery_rec_index := 0;
642    IF (l_debug = 1) THEN
643       trace('** in PVT7.get_variable_data ** , start ');
644    END IF;
645    l_printer := p_label_type_info.default_printer;
646    l_prev_sub := '####';
647 
648    l_label_index := 1;
649    l_prev_format_id := p_label_type_info.default_format_id;
650 
651    WHILE l_delivery_id IS NOT NULL LOOP
652       l_delivery_data := '';
653       FOR v_delivery IN c_delivery(l_delivery_id) LOOP
654          l_delivery_rec_index := l_delivery_rec_index + 1;
655          IF (l_debug = 1) THEN
656             trace(' ** New Label  ' || l_delivery_rec_index );
657          END IF;
658 
659          -- Bug 5121507, get carrier name
660          IF v_delivery.carrier_id IS NOT NULL THEN
661             BEGIN
662                SELECT carrier_name
663                INTO v_delivery.carrier
664                FROM wsh_carriers_v
665                WHERE carrier_id = v_delivery.carrier_id
666                AND ROWNUM<2;
667             EXCEPTION
668                WHEN others THEN
669                   v_delivery.carrier := null;
670             END;
671          END IF;
672 
673          --R12 : RFID compliance project
674          --Calling rules engine before calling to get printer
675          IF (l_debug = 1) THEN
676             trace('Apply Rules engine for format'
677             ||',manual_format_id='||p_label_type_info.manual_format_id
678             ||',manual_format_name='||p_label_type_info.manual_format_name);
679          END IF;
680 
681          /* insert a record into wms_label_requests entity to
682          call the label rules engine to get appropriate label */
683          INV_LABEL.GET_FORMAT_WITH_RULE
684          (  p_document_id        =>p_label_type_info.label_type_id,
685             P_LABEL_FORMAT_ID    => p_label_type_info.manual_format_id,
686             p_delivery_id  =>l_delivery_id,
687             --p_printer_name  =>l_printer,-- Removed in R12: 4396558
688             P_BUSINESS_FLOW_CODE =>   p_label_type_info.business_flow_code,
689             P_LAST_UPDATE_DATE   =>sysdate,
690             P_LAST_UPDATED_BY    =>FND_GLOBAL.user_id,
691             P_CREATION_DATE      =>sysdate,
692             P_CREATED_BY         =>FND_GLOBAL.user_id,
693             x_return_status      =>l_return_status,
694             x_label_format_id   =>l_label_format_id,
695             x_label_format    =>l_label_format,
696             x_label_request_id  =>l_label_request_id,
697             -- Added by joabraha for Bug 3549300
698             p_customer_id     => v_delivery.customer_id,
699             p_organization_id   => v_delivery.organization_id,
700 	    -- Added by dchithir for bug 5740331
701 	    p_sales_order_header_id  => l_sales_order_header_id
702             );
703 
704          IF l_return_status <> 'S' THEN
705             FND_MESSAGE.SET_NAME('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
706             FND_MSG_PUB.ADD;
707             l_label_format:= p_label_type_info.default_format_id;
708             l_label_format_id:= p_label_type_info.default_format_name;
709          END IF;
710          IF (l_debug = 1) THEN
711             trace('did apply label ' || l_label_format || ',' || l_label_format_id||',req_id '||l_label_request_id);
712          END IF;
713 
714 
715 
716          IF (l_debug = 1) THEN
717             trace(' Getting printer, manual_printer='||p_label_type_info.manual_printer ||',sub='||l_subinventory_code ||',default printer='||p_label_type_info.default_printer);
718          END IF;
719 
720          -- IF clause Added for Add format/printer for manual request
721          IF p_label_type_info.manual_printer IS NULL THEN
722          -- The p_label_type_info.manual_printer is the one  passed from the manual page.
723          -- As per the design, if a printer is passed from the manual page, then we use that printer irrespective.
724 
725             IF (l_subinventory_code IS NOT NULL) AND (l_subinventory_code <> l_prev_sub) THEN
726                IF (l_debug = 1) THEN
727                   trace('getting printer with sub '||l_subinventory_code);
728                END IF;
729                BEGIN
730                   WSH_REPORT_PRINTERS_PVT.GET_PRINTER(
731                      p_concurrent_program_id=>p_label_type_info.label_type_id,
732                      p_user_id              =>fnd_global.user_id,
733                      p_responsibility_id    =>fnd_global.resp_id,
734                      p_application_id       =>fnd_global.resp_appl_id,
735                      p_organization_id      =>l_organization_id,
736                      p_zone                 =>l_subinventory_code,
737                      p_format_id         =>l_label_format_id, --added in r12 RFID 4396558
738                      x_printer              =>l_printer,
739                      x_api_status           =>l_api_status,
740                      x_error_message        =>l_error_message);
741                   IF l_api_status <> 'S' THEN
742                      IF (l_debug = 1) THEN
743                         trace('Error in calling get_printer, set printer as default printer, err_msg:'||l_error_message);
744                      END IF;
745                      l_printer := p_label_type_info.default_printer;
746                   END IF;
747 
748                EXCEPTION
749                   WHEN others THEN
750                      l_printer := p_label_type_info.default_printer;
751                END;
752                 l_prev_sub := l_subinventory_code;
753             END IF;
754          ELSE
755             IF (l_debug = 1) THEN
756                trace('Set printer as Manual Printer passed in:' || p_label_type_info.manual_printer );
757             END IF;
758             l_printer := p_label_type_info.manual_printer;
759          END IF;
760 
761 
762 
763 
764 
765          IF p_label_type_info.manual_format_id IS NOT NULL THEN
766             l_label_format_id := p_label_type_info.manual_format_id;
767             l_label_format := p_label_type_info.manual_format_name;
768             IF (l_debug = 1) THEN
769                trace('Manual format passed in:'||l_label_format_id||','||l_label_format);
770             END IF;
771          END IF;
772          IF (l_label_format_id IS NOT NULL) THEN
773             -- Derive the fields for the format either passed in or derived via the rules engine.
774             IF l_label_format_id <> nvl(l_prev_format_id, -999) THEN
775                IF (l_debug = 1) THEN
776                   trace(' Getting variables for new format ' || l_label_format);
777                END IF;
778                INV_LABEL.GET_VARIABLES_FOR_FORMAT(
779                   x_variables       => l_selected_fields
780                ,  x_variables_count => l_selected_fields_count
781                ,  p_format_id    => l_label_format_id);
782 
783                l_prev_format_id := l_label_format_id;
784 
785                IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
786                   IF (l_debug = 1) THEN
787                      trace('no fields defined for this format: ' || l_label_format|| ',' ||l_label_format_id);
788                   END IF;
789                   GOTO NextLabel;
790                END IF;
791                IF (l_debug = 1) THEN
792                   trace('   Found selected_fields for format ' || l_label_format ||', num='|| l_selected_fields_count);
793                END IF;
794             END IF;
795          ELSE
796             IF (l_debug = 1) THEN
797                trace('No format exists for this label, goto nextlabel');
798             END IF;
799             GOTO NextLabel;
800          END IF;
801 
802          /* variable header */
803          l_delivery_data := l_delivery_data || LABEL_B;
804          IF l_label_format <> nvl(p_label_type_info.default_format_name, '@@@') THEN
805             l_delivery_data := l_delivery_data || ' _FORMAT="' || nvl(p_label_type_info.manual_format_name, l_label_format) || '"';
806          END IF;
807          IF (l_printer IS NOT NULL) AND (l_printer <> nvl(p_label_type_info.default_printer,'###')) THEN
808             l_delivery_data := l_delivery_data || ' _PRINTERNAME="'||l_printer||'"';
809          END IF;
810 
811          l_delivery_data := l_delivery_data || TAG_E;
812 
813 
814          IF (l_debug = 1) THEN
815             trace('Starting assign variables, ');
816          END IF;
817 
818          l_column_name_list := 'Set variables for ';
819 
820          /* Modified for Bug 4072474 -start*/
821          l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
822          /* Modified for Bug 4072474 -End*/
823 
824          -- Fix for bug: 4179593 Start
825          l_CustSqlWarnFlagSet := FALSE;
826          l_CustSqlErrFlagSet := FALSE;
827          l_CustSqlWarnMsg := NULL;
828          l_CustSqlErrMsg := NULL;
829          -- Fix for bug: 4179593 End
830 
831          /* Loop for each selected fields, find the columns and write into the XML_content*/
832          FOR i IN 1..l_selected_fields.count LOOP
833 
834             IF (l_debug = 1) THEN
835                   l_column_name_list := l_column_name_list || ',' ||l_selected_fields(i).column_name;
836             END IF;
837 
838 ---------------------------------------------------------------------------------------------
839 -- Project: 'Custom Labels' (A 11i10+ Project)                                               |
840 -- Author: Dinesh ([email protected])                                                      |
841 -- Change Description:                                                                       |
842 --  The check (SQL_STMT <> NULL and COLUMN_NAME = NULL) implies that the field is a          |
843 --  Custom SQL based field. Handle it appropriately.                                         |
844 ---------------------------------------------------------------------------------------------
845            IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
846              IF (l_debug = 1) THEN
847               trace('Custom Labels Trace [INVLAP7B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
848               trace('Custom Labels Trace [INVLAP7B.pls]: LABEL_FIELD_ID       : ' || l_selected_fields(i).label_field_id);
849               trace('Custom Labels Trace [INVLAP7B.pls]: FIELD_VARIABLE_NAME  : ' || l_selected_fields(i).variable_name);
850               trace('Custom Labels Trace [INVLAP7B.pls]: COLUMN_NAME          : ' || l_selected_fields(i).column_name);
851               trace('Custom Labels Trace [INVLAP7B.pls]: SQL_STMT             : ' || l_selected_fields(i).sql_stmt);
852              END IF;
853              l_sql_stmt := l_selected_fields(i).sql_stmt;
854              IF (l_debug = 1) THEN
855               trace('Custom Labels Trace [INVLAP7B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
856              END IF;
857              l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
858              IF (l_debug = 1) THEN
859               trace('Custom Labels Trace [INVLAP7B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
860 
861              END IF;
862              BEGIN
863              IF (l_debug = 1) THEN
864               trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 1');
865               trace('Custom Labels Trace [INVLAP7B.pls]: LABEL_REQUEST_ID     : ' || l_label_request_id);
866              END IF;
867              OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
868              LOOP
869                 FETCH c_sql_stmt INTO l_sql_stmt_result;
870                 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
871              END LOOP;
872 
873              IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
874                 x_return_status := FND_API.G_RET_STS_SUCCESS;
875                 l_custom_sql_ret_status  := INV_LABEL.G_WARNING;
876                 fnd_message.set_name('WMS','WMS_CS_NULL_VALUE_RETURNED');
877                 fnd_msg_pub.ADD;
878                 -- Fix for bug: 4179593 Start
879                 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
880                 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
881                 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
882                 l_CustSqlWarnFlagSet := TRUE;
883                 -- Fix for bug: 4179593 End
884                IF (l_debug = 1) THEN
885                 trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 2');
886                 trace('Custom Labels Trace [INVLAP7B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
887                 trace('Custom Labels Trace [INVLAP7B.pls]: WARNING: NULL value returned by the custom SQL Query.');
888                 trace('Custom Labels Trace [INVLAP7B.pls]: l_custom_sql_ret_status  is set to : ' || l_custom_sql_ret_status );
889                END IF;
890              ELSIF c_sql_stmt%rowcount=0 THEN
891                IF (l_debug = 1) THEN
892                 trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 3');
893                 trace('Custom Labels Trace [INVLAP7B.pls]: WARNING: No row returned by the Custom SQL query');
894                END IF;
895                x_return_status := FND_API.G_RET_STS_SUCCESS;
896                l_custom_sql_ret_status  := INV_LABEL.G_WARNING;
897                fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
898                fnd_msg_pub.ADD;
899                /* Replaced following statement for Bug 4207625: Anupam Jain*/
900                /*fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_status);*/
901                -- Fix for bug: 4179593 Start
902                --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
903                l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
904                l_CustSqlWarnMsg := l_custom_sql_ret_msg;
905                l_CustSqlWarnFlagSet := TRUE;
906                -- Fix for bug: 4179593 End
907              ELSIF c_sql_stmt%rowcount>=2 THEN
908                IF (l_debug = 1) THEN
909                 trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 4');
910                 trace('Custom Labels Trace [INVLAP7B.pls]: ERROR: Multiple values returned by the Custom SQL query');
911                END IF;
912                l_sql_stmt_result := NULL;
913                x_return_status := FND_API.G_RET_STS_SUCCESS;
914                l_custom_sql_ret_status  := FND_API.G_RET_STS_ERROR;
915                fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
916                fnd_msg_pub.ADD;
917                /* Replaced following statement for Bug 4207625: Anupam Jain*/
918                /*fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_status);*/
919                -- Fix for bug: 4179593 Start
920                --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
921                l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
922                l_CustSqlErrMsg := l_custom_sql_ret_msg;
923                l_CustSqlErrFlagSet := TRUE;
924                -- Fix for bug: 4179593 End
925              END IF;
926              IF (c_sql_stmt%ISOPEN) THEN
927                CLOSE c_sql_stmt;
928              END IF;
929             EXCEPTION
930             WHEN OTHERS THEN
931             IF (c_sql_stmt%ISOPEN) THEN
932                CLOSE c_sql_stmt;
933             END IF;
934               IF (l_debug = 1) THEN
935                 trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 5');
936                 trace('Custom Labels Trace [INVLAP7B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
937               END IF;
938               x_return_status := FND_API.G_RET_STS_ERROR;
939               fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
940               fnd_msg_pub.ADD;
941               fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
942               RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
943             END;
944             IF (l_debug = 1) THEN
945               trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 6');
946               trace('Custom Labels Trace [INVLAP7B.pls]: Before assigning it to l_delivery_data');
947             END IF;
948             l_delivery_data  :=   l_delivery_data
949                            || variable_b
950                            || l_selected_fields(i).variable_name
951                            || '">'
952                            || l_sql_stmt_result
953                            || variable_e;
954             l_sql_stmt_result := NULL;
955             l_sql_stmt        := NULL;
956             IF (l_debug = 1) THEN
957               trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 7');
958               trace('Custom Labels Trace [INVLAP7B.pls]: After assigning it to l_delivery_data');
959               trace('Custom Labels Trace [INVLAP7B.pls]: --------------------------REPORT END-------------------------------------');
960             END IF;
961 ------------------------End of this change for Custom Labels project code--------------------
962             ELSIF LOWER(l_selected_fields(i).column_name) = 'current_date' THEN
963                l_delivery_data := l_delivery_data || VARIABLE_B ||
964                l_selected_fields(i).variable_name || '">' || INV_LABEL.G_DATE || VARIABLE_E;
965             ELSIF LOWER(l_selected_fields(i).column_name) = 'current_time' THEN
966                l_delivery_data := l_delivery_data || VARIABLE_B ||
967                l_selected_fields(i).variable_name || '">' || INV_LABEL.G_TIME || VARIABLE_E;
968             ELSIF LOWER(l_selected_fields(i).column_name) = 'request_user' THEN
969                l_delivery_data := l_delivery_data || VARIABLE_B ||
970                l_selected_fields(i).variable_name || '">' || INV_LABEL.G_USER || VARIABLE_E;
971             ELSIF LOWER(l_selected_fields(i).column_name) = 'airbill' THEN
972                l_delivery_data := l_delivery_data || VARIABLE_B ||
973                l_selected_fields(i).variable_name || '">' || v_delivery.airbill || VARIABLE_E;
974             ELSIF LOWER(l_selected_fields(i).column_name) = 'bill_of_lading' THEN
975                l_delivery_data := l_delivery_data || VARIABLE_B ||
976                l_selected_fields(i).variable_name || '">' || v_delivery.bill_of_lading || VARIABLE_E;
977             ELSIF LOWER(l_selected_fields(i).column_name) = 'carrier' THEN
978                l_delivery_data := l_delivery_data || VARIABLE_B ||
979                l_selected_fields(i).variable_name || '">' || v_delivery.carrier || VARIABLE_E;
980             ELSIF LOWER(l_selected_fields(i).column_name) = 'customer' THEN
981                l_delivery_data := l_delivery_data || VARIABLE_B ||
982                l_selected_fields(i).variable_name || '">' || v_delivery.customer || VARIABLE_E;
983             ELSIF LOWER(l_selected_fields(i).column_name) = 'delivery_number' THEN
984                l_delivery_data := l_delivery_data || VARIABLE_B ||
985                 l_selected_fields(i).variable_name || '">' || v_delivery.delivery_number || VARIABLE_E;
986             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_postal_code' THEN
987                l_delivery_data := l_delivery_data || VARIABLE_B ||
988                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_postal_code || VARIABLE_E;
989             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_state' THEN
990                l_delivery_data := l_delivery_data || VARIABLE_B ||
991                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_state || VARIABLE_E;
992             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_address1' THEN
993                l_delivery_data := l_delivery_data || VARIABLE_B ||
994                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_address1 || VARIABLE_E;
995             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_address2' THEN
996                l_delivery_data := l_delivery_data || VARIABLE_B ||
997                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_address2 || VARIABLE_E;
998             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_address3' THEN
999                l_delivery_data := l_delivery_data || VARIABLE_B ||
1000                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_address3 || VARIABLE_E;
1001             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_address4' THEN
1002                l_delivery_data := l_delivery_data || VARIABLE_B ||
1003                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_address4 || VARIABLE_E;
1004             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_city' THEN
1005                l_delivery_data := l_delivery_data || VARIABLE_B ||
1006                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_city || VARIABLE_E;
1007             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_country' THEN
1008                l_delivery_data := l_delivery_data || VARIABLE_B ||
1009                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_country || VARIABLE_E;
1010             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_county' THEN
1011                l_delivery_data := l_delivery_data || VARIABLE_B ||
1012                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_county || VARIABLE_E;
1013             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_province' THEN
1014                l_delivery_data := l_delivery_data || VARIABLE_B ||
1015                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_province || VARIABLE_E;
1016             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_state' THEN
1017                l_delivery_data := l_delivery_data || VARIABLE_B ||
1018                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_state || VARIABLE_E;
1019             ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_gross_weight' THEN
1020                l_delivery_data := l_delivery_data || VARIABLE_B ||
1021                l_selected_fields(i).variable_name || '">' || v_delivery.shipment_gross_weight || VARIABLE_E;
1022             ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_gross_weight_uom' THEN
1023                l_delivery_data := l_delivery_data || VARIABLE_B ||
1024                l_selected_fields(i).variable_name || '">' || v_delivery.shipment_gross_weight_uom || VARIABLE_E;
1025             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_method' THEN
1026                l_delivery_data := l_delivery_data || VARIABLE_B ||
1027                l_selected_fields(i).variable_name || '">' || v_delivery.ship_method || VARIABLE_E;
1028             ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_tare_weight' THEN
1029                l_delivery_data := l_delivery_data || VARIABLE_B ||
1030                l_selected_fields(i).variable_name || '">' || v_delivery.shipment_tare_weight || VARIABLE_E;
1031             ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_tare_weight_uom' THEN
1032                l_delivery_data := l_delivery_data || VARIABLE_B ||
1033                l_selected_fields(i).variable_name || '">' || v_delivery.shipment_tare_weight_uom || VARIABLE_E;
1034             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_address1' THEN
1035                l_delivery_data := l_delivery_data || VARIABLE_B ||
1036                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_address1 || VARIABLE_E;
1037             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_address2' THEN
1038                l_delivery_data := l_delivery_data || VARIABLE_B ||
1039                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_address2 || VARIABLE_E;
1040             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_address3' THEN
1041                l_delivery_data := l_delivery_data || VARIABLE_B ||
1042                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_address3 || VARIABLE_E;
1043             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_address4' THEN
1044                l_delivery_data := l_delivery_data || VARIABLE_B ||
1045                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_address4 || VARIABLE_E;
1046             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_city' THEN
1047                l_delivery_data := l_delivery_data || VARIABLE_B ||
1048                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_city || VARIABLE_E;
1049             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_country' THEN
1050                l_delivery_data := l_delivery_data || VARIABLE_B ||
1051                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_country || VARIABLE_E;
1052             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_county' THEN
1053                l_delivery_data := l_delivery_data || VARIABLE_B ||
1054                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_county || VARIABLE_E;
1055             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_postal_code' THEN
1056                l_delivery_data := l_delivery_data || VARIABLE_B ||
1057                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_postal_code || VARIABLE_E;
1058             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_province' THEN
1059                l_delivery_data := l_delivery_data || VARIABLE_B ||
1060                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_province || VARIABLE_E;
1061             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_state' THEN
1062                l_delivery_data := l_delivery_data || VARIABLE_B ||
1063                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_state || VARIABLE_E;
1064             ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_volume' THEN
1065                l_delivery_data := l_delivery_data || VARIABLE_B ||
1066                l_selected_fields(i).variable_name || '">' || v_delivery.shipment_volume || VARIABLE_E;
1067             ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_volume_uom' THEN
1068                l_delivery_data := l_delivery_data || VARIABLE_B ||
1069                l_selected_fields(i).variable_name || '">' || v_delivery.shipment_volume_uom || VARIABLE_E;
1070             ELSIF LOWER(l_selected_fields(i).column_name) = 'total_number_of_lpns' THEN
1071                l_delivery_data := l_delivery_data || VARIABLE_B ||
1072                l_selected_fields(i).variable_name || '">' || v_delivery.total_number_of_lpns || VARIABLE_E;
1073             ELSIF LOWER(l_selected_fields(i).column_name) = 'waybill' THEN
1074                l_delivery_data := l_delivery_data || VARIABLE_B ||
1075                l_selected_fields(i).variable_name || '">' || v_delivery.waybill || VARIABLE_E;
1076             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute1' THEN
1077                l_delivery_data := l_delivery_data || VARIABLE_B ||
1078                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute1 || VARIABLE_E;
1079             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute2' THEN
1080                l_delivery_data := l_delivery_data || VARIABLE_B ||
1081                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute2 || VARIABLE_E;
1082             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute3' THEN
1083                l_delivery_data := l_delivery_data || VARIABLE_B ||
1084                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute3 || VARIABLE_E;
1085             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute4' THEN
1086                l_delivery_data := l_delivery_data || VARIABLE_B ||
1087                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute4 || VARIABLE_E;
1088             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute5' THEN
1089                l_delivery_data := l_delivery_data || VARIABLE_B ||
1090                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute5 || VARIABLE_E;
1091             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute6' THEN
1092                l_delivery_data := l_delivery_data || VARIABLE_B ||
1093                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute6 || VARIABLE_E;
1094             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute7' THEN
1095                l_delivery_data := l_delivery_data || VARIABLE_B ||
1096                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute7 || VARIABLE_E;
1097             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute8' THEN
1098                l_delivery_data := l_delivery_data || VARIABLE_B ||
1099                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute8 || VARIABLE_E;
1100             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute9' THEN
1101                l_delivery_data := l_delivery_data || VARIABLE_B ||
1102                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute9 || VARIABLE_E;
1103             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute10' THEN
1104                l_delivery_data := l_delivery_data || VARIABLE_B ||
1105                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute10 || VARIABLE_E;
1106             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute11' THEN
1107                l_delivery_data := l_delivery_data || VARIABLE_B ||
1108                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute11 || VARIABLE_E;
1109             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute12' THEN
1110                l_delivery_data := l_delivery_data || VARIABLE_B ||
1111                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute12 || VARIABLE_E;
1112             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute13' THEN
1113                l_delivery_data := l_delivery_data || VARIABLE_B ||
1114                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute13 || VARIABLE_E;
1115             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute14' THEN
1116                l_delivery_data := l_delivery_data || VARIABLE_B ||
1117                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute14 || VARIABLE_E;
1118             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute15' THEN
1119                l_delivery_data := l_delivery_data || VARIABLE_B ||
1120                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute15 || VARIABLE_E;
1121             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute_category' THEN
1122                l_delivery_data := l_delivery_data || VARIABLE_B ||
1123                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute_category || VARIABLE_E;
1124 
1125              ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr1' THEN
1126                l_delivery_data := l_delivery_data || VARIABLE_B ||
1127                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr1 || VARIABLE_E;
1128             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr2' THEN
1129                l_delivery_data := l_delivery_data || VARIABLE_B ||
1130                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr2 || VARIABLE_E;
1131             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr3' THEN
1132                l_delivery_data := l_delivery_data || VARIABLE_B ||
1133                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr3 || VARIABLE_E;
1134             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr4' THEN
1135                l_delivery_data := l_delivery_data || VARIABLE_B ||
1136                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr4 || VARIABLE_E;
1137             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr5' THEN
1138                l_delivery_data := l_delivery_data || VARIABLE_B ||
1139                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr5 || VARIABLE_E;
1140             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr6' THEN
1141                l_delivery_data := l_delivery_data || VARIABLE_B ||
1142                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr6 || VARIABLE_E;
1143             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr7' THEN
1144                l_delivery_data := l_delivery_data || VARIABLE_B ||
1145                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr7 || VARIABLE_E;
1146             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr8' THEN
1147                l_delivery_data := l_delivery_data || VARIABLE_B ||
1148                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr8 || VARIABLE_E;
1149             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr9' THEN
1150                l_delivery_data := l_delivery_data || VARIABLE_B ||
1151                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr9 || VARIABLE_E;
1152             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr10' THEN
1153                l_delivery_data := l_delivery_data || VARIABLE_B ||
1154                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr10 || VARIABLE_E;
1155             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr11' THEN
1156                l_delivery_data := l_delivery_data || VARIABLE_B ||
1157                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr11 || VARIABLE_E;
1158             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr12' THEN
1159                l_delivery_data := l_delivery_data || VARIABLE_B ||
1160                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr12 || VARIABLE_E;
1161             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr13' THEN
1162                l_delivery_data := l_delivery_data || VARIABLE_B ||
1163                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr13 || VARIABLE_E;
1164             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr14' THEN
1165                l_delivery_data := l_delivery_data || VARIABLE_B ||
1166                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr14 || VARIABLE_E;
1167             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr15' THEN
1168                l_delivery_data := l_delivery_data || VARIABLE_B ||
1169                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr15 || VARIABLE_E;
1170             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr_category' THEN
1171                l_delivery_data := l_delivery_data || VARIABLE_B ||
1172                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr_category || VARIABLE_E;
1173 
1174              ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr1' THEN
1175                l_delivery_data := l_delivery_data || VARIABLE_B ||
1176                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr1 || VARIABLE_E;
1177             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr2' THEN
1178                l_delivery_data := l_delivery_data || VARIABLE_B ||
1179                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr2 || VARIABLE_E;
1180             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr3' THEN
1181                l_delivery_data := l_delivery_data || VARIABLE_B ||
1182                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr3 || VARIABLE_E;
1183             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr4' THEN
1184                l_delivery_data := l_delivery_data || VARIABLE_B ||
1185                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr4 || VARIABLE_E;
1186             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr5' THEN
1187                l_delivery_data := l_delivery_data || VARIABLE_B ||
1188                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr5 || VARIABLE_E;
1189             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr6' THEN
1190                l_delivery_data := l_delivery_data || VARIABLE_B ||
1191                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr6 || VARIABLE_E;
1192             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr7' THEN
1193                l_delivery_data := l_delivery_data || VARIABLE_B ||
1194                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr7 || VARIABLE_E;
1195             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr8' THEN
1196                l_delivery_data := l_delivery_data || VARIABLE_B ||
1197                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr8 || VARIABLE_E;
1198             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr9' THEN
1199                l_delivery_data := l_delivery_data || VARIABLE_B ||
1200                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr9 || VARIABLE_E;
1201             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr10' THEN
1202                l_delivery_data := l_delivery_data || VARIABLE_B ||
1203                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr10 || VARIABLE_E;
1204             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr11' THEN
1205                l_delivery_data := l_delivery_data || VARIABLE_B ||
1206                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr11 || VARIABLE_E;
1207             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr12' THEN
1208                l_delivery_data := l_delivery_data || VARIABLE_B ||
1209                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr12 || VARIABLE_E;
1210             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr13' THEN
1211                l_delivery_data := l_delivery_data || VARIABLE_B ||
1212                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr13 || VARIABLE_E;
1213             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr14' THEN
1214                l_delivery_data := l_delivery_data || VARIABLE_B ||
1215                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr14 || VARIABLE_E;
1216             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr15' THEN
1217                l_delivery_data := l_delivery_data || VARIABLE_B ||
1218                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr15 || VARIABLE_E;
1219             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr_category' THEN
1220                l_delivery_data := l_delivery_data || VARIABLE_B ||
1221                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr_category || VARIABLE_E;
1222             --Bug 3969347-Added the label fields ship_from_addressee and ship_to_addressee.
1223             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_addressee' THEN
1224                    l_delivery_data := l_delivery_data || VARIABLE_B ||
1225                     l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_addressee || VARIABLE_E;
1226             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_addressee' THEN
1227                    l_delivery_data := l_delivery_data || VARIABLE_B ||
1228                     l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_addressee || VARIABLE_E;
1229            --End of fix for Bug 3969347.
1230 
1231           END IF;
1232 
1233          END LOOP;
1234          l_delivery_data := l_delivery_data || LABEL_E;
1235          x_variable_content(l_label_index).label_content := l_delivery_data;
1236          x_variable_content(l_label_index).label_request_id := l_label_request_id;
1237 
1238 ------------------------Start of changes for Custom Labels project code------------------
1239 
1240         -- Fix for bug: 4179593 Start
1241         IF (l_CustSqlWarnFlagSet) THEN
1242          l_custom_sql_ret_status := INV_LABEL.G_WARNING;
1243          l_custom_sql_ret_msg := l_CustSqlWarnMsg;
1244         END IF;
1245 
1246         IF (l_CustSqlErrFlagSet) THEN
1247          l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
1248          l_custom_sql_ret_msg := l_CustSqlErrMsg;
1249         END IF;
1250         -- Fix for bug: 4179593 End
1251 
1252         x_variable_content(l_label_index).label_status      := l_custom_sql_ret_status ;
1253         x_variable_content(l_label_index).error_message     := l_custom_sql_ret_msg;
1254 ------------------------End of this changes for Custom Labels project code---------------
1255 
1256          l_label_index := l_label_index +1;
1257          <<NextLabel>>
1258          l_delivery_data := '';
1259          l_label_request_id := null;
1260 
1261 ------------------------Start of changes for Custom Labels project code------------------
1262         l_custom_sql_ret_status := NULL;
1263         l_custom_sql_ret_msg    := NULL;
1264 ------------------------End of this changes for Custom Labels project code---------------
1265 
1266          IF (l_debug = 1) THEN
1267             trace(l_column_name_list);
1268                trace('  Finished writing item variables ');
1269          END IF;
1270       END LOOP;
1271 
1272       IF p_label_type_info.business_flow_code in (22)
1273       THEN
1274          FETCH c_cart_shipping_pkg INTO l_delivery_id;
1275             IF c_cart_shipping_pkg%NOTFOUND
1276             THEN
1277                IF (l_debug = 1) THEN
1278                   trace(' No more delivery_id found for cartonization');
1279                END IF;
1280                CLOSE c_cart_shipping_pkg;
1281                l_delivery_id := null;
1282             ELSE
1283                IF (l_debug = 1) THEN
1284                   Trace('Found next delivery_id=' || l_delivery_id);
1285                END IF;
1286             END IF;
1287       ELSE
1288          l_delivery_id := null;
1289       END IF;
1290 
1291    END LOOP;
1292 END get_variable_data;
1293 
1294 PROCEDURE get_variable_data(
1295    x_variable_content   OUT NOCOPY LONG
1296 ,  x_msg_count    OUT NOCOPY NUMBER
1297 ,  x_msg_data           OUT NOCOPY VARCHAR2
1298 ,  x_return_status      OUT NOCOPY VARCHAR2
1299 ,  p_label_type_info IN INV_LABEL.label_type_rec
1300 ,  p_transaction_id  IN NUMBER
1301 ,  p_input_param     IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
1302 ,  p_transaction_identifier IN NUMBER
1303 ) IS
1304    l_variable_data_tbl INV_LABEL.label_tbl_type;
1305 BEGIN
1306    get_variable_data(
1307       x_variable_content   => l_variable_data_tbl
1308    ,  x_msg_count    => x_msg_count
1309    ,  x_msg_data           => x_msg_data
1310    ,  x_return_status      => x_return_status
1311    ,  p_label_type_info => p_label_type_info
1312    ,  p_transaction_id  => p_transaction_id
1313    ,  p_input_param     => p_input_param
1314    ,  p_transaction_identifier=> p_transaction_identifier
1315    );
1316 
1317    x_variable_content := '';
1318 
1319    FOR i IN 1..l_variable_data_tbl.count() LOOP
1320       x_variable_content := x_variable_content || l_variable_data_tbl(i).label_content;
1321    END LOOP;
1322 
1323 END get_variable_data;
1324 
1325 
1326 END INV_LABEL_PVT7;