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.11.12020000.3 2013/01/19 13:25:24 blavu 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            , nvl(wnd.customer_id,addr.customer_id) customer_id  -- Modified for Bug# 14784146
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, DECODE(LOC.STYLE,'CA',(SELECT MEANING
329  	                               FROM   FND_COMMON_LOOKUPS
330  	                               WHERE  LOOKUP_TYPE = 'CA_PROVINCE'
331  	                                      AND LOOKUP_CODE = LOC.REGION_1
332  	                                      AND ROWNUM < 2),
333  	                         'CA_GLB',(SELECT MEANING
334  	                                   FROM   FND_COMMON_LOOKUPS
335  	                                   WHERE  LOOKUP_TYPE = 'CA_PROVINCE'
336  	                                          AND LOOKUP_CODE = LOC.REGION_1
337  	                                          AND ROWNUM < 2),
338  	                         LOC.REGION_3) PROVINCE  -- Modified for bug 7281160
339             from hr_locations_all loc
340       union all
341       select hz.location_id location_id,hz.address1   address_line_1
342             ,hz.address2   address_line_2,hz.address3 address_line_3
343             ,hz.address4   address_line_4,hz.city city,hz.postal_code postal_code
344             ,hz.state state,hz.county county,hz.country country,hz.province province
345       from hz_locations hz   ) hrl,
346         --Bug 3969347 -Adding the table hr_organization_units
347              hr_organization_units hro,
348         --End of fix for Bug 3969347.
349       (select loc.location_id location_id,loc.address_line_1 address_line_1
350             ,loc.address_line_2 address_line_2,loc.address_line_3 address_line_3
351             ,loc.loc_information13 address_line_4,loc.town_or_city city
352             ,loc.postal_code postal_code,loc.region_2 state,loc.region_1 county
353             ,loc.country country, DECODE(LOC.STYLE,'CA',(SELECT MEANING
354  	                               FROM   FND_COMMON_LOOKUPS
355  	                               WHERE  LOOKUP_TYPE = 'CA_PROVINCE'
356  	                                      AND LOOKUP_CODE = LOC.REGION_1
357  	                                      AND ROWNUM < 2),
358  	                         'CA_GLB',(SELECT MEANING
359  	                                   FROM   FND_COMMON_LOOKUPS
360  	                                   WHERE  LOOKUP_TYPE = 'CA_PROVINCE'
361  	                                          AND LOOKUP_CODE = LOC.REGION_1
362  	                                          AND ROWNUM < 2),
363  	                         LOC.REGION_3) PROVINCE  -- Modified for bug 7281160
364             from hr_locations_all loc
365       union all
366       select hz.location_id location_id,hz.address1   address_line_1
367             ,hz.address2   address_line_2,hz.address3 address_line_3
368             ,hz.address4   address_line_4,hz.city city,hz.postal_code postal_code
369             ,hz.state state,hz.county county,hz.country country,hz.province province
370       from hz_locations hz   ) hrl1,
371       wsh_new_deliveries    wnd,
372       wsh_delivery_legs     wdl,
373       wsh_document_instances  wdi
374       -- Bug 2878652, get carrier name and ship method name
375       , fnd_common_lookups fcl
376       -- Bug 5121507 Getting Carrier in the order of Trip->Delivery
377       --, wsh_carriers_v wcv
378       ,  wsh_trip_stops  wts
379       ,  wsh_trips       wt
380                 --Added to fix the issue reported in the Bug#4445708
381                 , (  select wda.delivery_id delivery_id, party_site.addressee addressee -- Added p_delivery_id 13108954
382 				            , wdd.customer_id customer_id
383                     from wsh_delivery_details wdd
384                        , wsh_delivery_assignments wda
385                        , hz_cust_site_uses_all hcsua
386                        , hz_party_sites party_site
387                        , hz_loc_assignments loc_assign
388                        , hz_locations loc
389                        , hz_cust_acct_sites_all acct_site
390                     where wdd.delivery_detail_id = wda.delivery_detail_id
391                       and wda.delivery_id = p_delivery_id
392                       and wdd.container_flag = 'N'
393                       and hcsua.site_use_id = wdd.ship_to_site_use_id
394                       and acct_site.cust_acct_site_id = hcsua.cust_acct_site_id
395                       AND acct_site.party_site_id = party_site.party_site_id
396                       AND loc.location_id = party_site.location_id
397                       AND loc.location_id = loc_assign.location_id
398                       AND NVL ( acct_site.org_id, -99 )  = NVL ( loc_assign.org_id, -99 )
399                       and rownum = 1
400                   ) addr
401    WHERE wnd.delivery_id      =  p_delivery_id
402    AND   rac.customer_id(+)   =  addr.customer_id  -- wnd.customer_id Modified for Bug# 14784146
403    and   addr.delivery_id(+)  =  wnd.delivery_id  -- 13108954
404    AND   hrl.location_id(+)   =  wnd.INITIAL_PICKUP_LOCATION_ID
405    AND   hrl1.location_id(+)  =  wnd.ULTIMATE_DROPOFF_LOCATION_ID
406    AND   wdl.delivery_id (+)  =  wnd.delivery_id
407    AND   wdi.entity_id  (+)   =  wdl.delivery_leg_id
408    AND     wdi.entity_name  (+)    =     'WSH_DELIVERY_LEGS' -- Bug 3905110
409    --Bug 3969347 --Added this condition to join the table hr_organization_units.
410    AND     hro.organization_id(+)  =       wnd.organization_id
411     --End of fix for Bug 3969347
412    AND wdi.document_type(+) = 'BOL'
413    AND fcl.lookup_type(+) = 'SHIP_METHOD'
414    AND fcl.lookup_code(+) = wnd.ship_method_code
415    --Bug 5121507 Getting Carrier in the order of Trip->Delivery
416    AND wnd.delivery_id      = wdl.delivery_id(+)
417    AND wdl.pick_up_stop_id  = wts.stop_id (+)
418    AND wts.trip_id          = wt.trip_id (+);
419 
420    l_delivery_data   LONG;
421    l_sales_order_header_id NUMBER; -- Introduced for bug: 5740331
422 
423    l_selected_fields    INV_LABEL.label_field_variable_tbl_type;
424    l_selected_fields_count NUMBER;
425 
426    l_delivery_rec_index    NUMBER := 0;
427 
428    l_label_format_id       NUMBER := 0 ;
429    l_label_format       VARCHAR2(100);
430    l_printer      VARCHAR2(30);
431 
432    l_api_name        VARCHAR2(20) := 'get_variable_data';
433 
434    l_return_status      VARCHAR2(240);
435 
436    l_error_message   VARCHAR2(240);
437    l_msg_count       NUMBER;
438    l_api_status      VARCHAR2(240);
439    l_msg_data     VARCHAR2(240);
440 
441    i        NUMBER;
442 
443    l_organization_id NUMBER;
444    l_subinventory_code  VARCHAR2(30) :=null;
445 
446    l_label_index NUMBER;
447    l_label_request_id NUMBER;
448 
449    --I cleanup, use l_prev_format_id to record the previous label format
450    l_prev_format_id      NUMBER;
451    -- I cleanup, user l_prev_sub to record the previous subinventory
452    --so that get_printer is not called if the subinventory is the same
453    l_prev_sub VARCHAR2(30);
454 
455    -- a list of columns that are selected for format
456    l_column_name_list LONG;
457 
458 BEGIN
459    -- Initialize return status as success
460    x_return_status := FND_API.G_RET_STS_SUCCESS;
461    -- If the transactions_id is passed, and the business flow code passed is in 6(Cross dock),
462    -- or 19(Pick Drop), then the transactions_id passed is the Delivery_Detail_Id.
463    -- Since we are printing the "Shipping"  label here, we will have to derive the delivery_id
464    -- from the delivery_detail_id and print the details for the Delivery Id .
465    -- If the business flow code passed is 21(Shipping) then the transaction id passed is the
466    -- delivery ID itself and so this can be directly assigned to the l_delivery_id
467 
468    -- For Pick Load(18), transaction_header_id is passed as p_transaction_id,
469    -- Delivery ID is obtained from MMTT and joining mmtt.move_order_line_id
470    -- to wdd.move_order_line_id.. All the MMTT record with that header_id
471    -- has the same move_order_line_id
472 
473     l_debug := INV_LABEL.l_debug;
474    IF (l_debug = 1) THEN
475       trace('**In Shipping label**');
476       trace('  Business_flow: '||p_label_type_info.business_flow_code);
477       trace('  Transaction ID:'||p_transaction_id);
478    END IF;
479 
480    -- Get l_delivery_id
481    IF p_transaction_id IS NOT NULL then
482       -- txn mode
483       IF p_label_type_info.business_flow_code in (6) THEN
484       -- means that the delivery_detail_id has been passed
485          OPEN c_wdd_shipping;
486          FETCH c_wdd_shipping INTO l_delivery_id, l_organization_id, l_subinventory_code;
487          IF c_wdd_shipping%NOTFOUND THEN
488             IF (l_debug = 1) THEN
489                trace(' No delivery_id found from WDD, no label print');
490             END IF;
491             CLOSE c_wdd_shipping;
492             RETURN;
493          ELSE
494             CLOSE c_wdd_shipping;
495          END IF;
496       ELSIF p_label_type_info.business_flow_code in (19) THEN
497       -- Old Design.
498       -- when the transactions manager calls printing for business flow of 'Pick Drop' (19) and passes the
499       -- transactions_temp_id, we derive the the transfer_lpn_id and the content_lpn_id from the MMTT and using either
500       -- the transfer_lpn_id  or content_lpn_id (imp: only if transfer_lpn_id is null) derive the delivery_detail_id
501       -- from the wsh_delivery_details.
502       -- There may be no delivery at Pick Load, Pick Drop, and Cartonization is the users fails to create one.
503       -- But a delivery ID will be automatically created after ship confirm.
504       -- This means that of the business flows of Pick Load, Pick Drop, and Cartonization there may be no delivery
505       -- label printed on some ocassions mentioned above.
506 
507       -- New Design:
508       -- Derive the move_order_line_id from the mmtt and then with the move_order_line_id derive the delivery_detail_id
509       -- from the WDD and then eventually the delivery_id.
510 
511       /*combined cursor c_mmtt_temp_id, c_wdd_delivery_dtl_id
512       , c_wda_delivery_id into one c_mmtt_pick_drop cursor*/
513          OPEN c_mmtt_pick_drop;
514          FETCH c_mmtt_pick_drop INTO l_delivery_id, l_organization_id, l_subinventory_code;
515 
516          IF c_mmtt_pick_drop%NOTFOUND THEN
517             IF (l_debug = 1) THEN
518                trace(' No record found in MMTT for given ID:'||p_transaction_id);
519             END IF;
520             CLOSE c_mmtt_pick_drop;
521             RETURN;
522          ELSE
523             CLOSE c_mmtt_pick_drop;
524             IF (l_debug = 1) THEN
525                trace(' Found delivery ID for pick drop:'||l_delivery_id);
526             END IF;
527          END IF;
528 
529       ELSIF p_label_type_info.business_flow_code in (21) THEN
530       -- means that the delivery_id has been passed
531          l_delivery_id := p_transaction_id ;
532 
533 	 -- Bug 5740331 - Added the following block to fetch the sales order header.
534          BEGIN
535           SELECT DISTINCT(source_header_id)
536           INTO  l_sales_order_header_id
537           FROM  wsh_delivery_details wdd,
538                     wsh_delivery_assignments wda
539           WHERE wdd.delivery_detail_id = wda.delivery_detail_id
540           AND   wda.delivery_id = l_delivery_id
541           AND   wdd.container_flag = 'N' ;
542 
543           IF (l_debug = 1) THEN
544                trace('Value of l_sales_order_header_id:'|| l_sales_order_header_id);
545           END IF;
546 
547           EXCEPTION
548           WHEN TOO_MANY_ROWS THEN
549 	  IF (l_debug = 1) THEN
550 		trace('In the exception for too many rows for Sales Order Header');
551           END IF;
552                 l_sales_order_header_id := NULL;
553           WHEN OTHERS THEN
554           IF (l_debug = 1) THEN
555 		trace('In the exception for too many rows for Sales Order Header');
556           END IF;
557 		l_sales_order_header_id := NULL;
558           END;
559           --End of fix for Bug 5740331
560 
561       ELSIF p_label_type_info.business_flow_code in (22) THEN
562          OPEN c_cart_shipping_pkg;
563          FETCH c_cart_shipping_pkg INTO l_delivery_id;
564             IF c_cart_shipping_pkg%NOTFOUND
565             THEN
566                IF (l_debug = 1) THEN
567                   trace(' No delivery_id found for cartonizationt');
568                END IF;
569                CLOSE c_cart_shipping_pkg;
570                RETURN;
571             ELSE
572                IF (l_debug = 1) THEN
573                   Trace('Found delivery_id for cartonization:' || l_delivery_id);
574                END IF;
575             END IF;
576 
577       ELSIF p_label_type_info.business_flow_code in (18, 34) THEN
578          OPEN c_cart_shipping;
579          FETCH c_cart_shipping INTO l_delivery_id, l_organization_id, l_subinventory_code;
580          IF c_cart_shipping%NOTFOUND THEN
581             IF (l_debug = 1) THEN
582                trace(' No delivery_id found from MMTT, no label print');
583             END IF;
584             CLOSE c_cart_shipping;
585             RETURN;
586          ELSE
587             CLOSE c_cart_shipping;
588          END IF;
589 
590       -- 18th February 2002 : Commented out below for fix to bug 2219171 for Qualcomm. Hence forth the
591       -- WMSTASKB.pls will be calling label printing at Pick Load with the
592       -- transaction_temp_id as opposed to the transaction_header_id earlier. This business flows(18)
593       -- have been added to  the above call.
594       -- ELSIF p_label_type_info.business_flow_code in (18) THEN
595       -- OPEN c_pickload_shipping;
596       -- FETCH c_pickload_shipping INTO l_delivery_id;
597       -- IF c_pickload_shipping%NOTFOUND THEN
598       --    trace(' No delivery_id found from MMTT with the header_id, no label print');
599       --    CLOSE c_pickload_shipping;
600       --    RETURN;
601       -- ELSE
602       --    CLOSE c_pickload_shipping;
603       -- END IF;
604 
605       ELSE
606          IF (l_debug = 1) THEN
607             trace(' Invalid business flow code '|| p_label_type_info.business_flow_code || ' No label print');
608          END IF;
609          RETURN;
610       END IF;
611    ELSE
612       -- manual mode.Manual request is from Jason's page.
613       -- We have to have an agreement that when they call
614       -- this API, they have to pass the delivery_id
615       -- in place of the transactions_temp_id.
616       l_delivery_id := p_input_param.transaction_temp_id;
617 
618       -- After patchset J, as per request from Packing Workbench
619       -- If delivery_id is not passed in directly
620       --  but LPN_ID is available
621       --  will derive the delivery_id with the LPN_ID
622       IF l_delivery_id IS NULL AND p_input_param.lpn_id IS NOT NULL THEN
623          OPEN c_lpn_delivery(p_input_param.lpn_id);
624          FETCH c_lpn_delivery INTO l_delivery_id;
625          CLOSE c_lpn_delivery;
626       END IF;
627 
628    END IF;
629 
630    IF (l_debug = 1) THEN
631       trace(' Got Delivery_id = '|| l_delivery_id);
632    END IF;
633 
634    IF l_delivery_id IS NULL THEN
635       IF (l_debug = 1) THEN
636          trace(' Delivery ID IS NULL, can not process ');
637       END IF;
638       RETURN;
639    END IF;
640 
641    IF (l_debug = 1) THEN
642       trace(' Getting selected fields ');
643    END IF;
644 
645    INV_LABEL.GET_VARIABLES_FOR_FORMAT(
646       x_variables       => l_selected_fields
647    ,  x_variables_count => l_selected_fields_count
648    ,  p_format_id    => p_label_type_info.default_format_id);
649 
650    IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
651       IF (l_debug = 1) THEN
652          trace('no fields defined for this format: ' || p_label_type_info.default_format_id || ',' ||p_label_type_info.default_format_name);
653       END IF;
654       --return;
655    END IF;
656 
657    IF (l_debug = 1) THEN
658       trace(' Found variable defined for this format, cont = ' || l_selected_fields_count);
659    END IF;
660 
661    l_delivery_rec_index := 0;
662    IF (l_debug = 1) THEN
663       trace('** in PVT7.get_variable_data ** , start ');
664    END IF;
665    l_printer := p_label_type_info.default_printer;
666    l_prev_sub := '####';
667 
668    l_label_index := 1;
669    l_prev_format_id := p_label_type_info.default_format_id;
670 
671    WHILE l_delivery_id IS NOT NULL LOOP
672       l_delivery_data := '';
673       FOR v_delivery IN c_delivery(l_delivery_id) LOOP
674          l_delivery_rec_index := l_delivery_rec_index + 1;
675          IF (l_debug = 1) THEN
676             trace(' ** New Label  ' || l_delivery_rec_index );
677          END IF;
678 
679          -- Bug 5121507, get carrier name
680          IF v_delivery.carrier_id IS NOT NULL THEN
681             BEGIN
682                SELECT carrier_name
683                INTO v_delivery.carrier
684                FROM wsh_carriers_v
685                WHERE carrier_id = v_delivery.carrier_id
686                AND ROWNUM<2;
687             EXCEPTION
688                WHEN others THEN
689                   v_delivery.carrier := null;
690             END;
691          END IF;
692 
693          --R12 : RFID compliance project
694          --Calling rules engine before calling to get printer
695          IF (l_debug = 1) THEN
696             trace('Apply Rules engine for format'
697             ||',manual_format_id='||p_label_type_info.manual_format_id
698             ||',manual_format_name='||p_label_type_info.manual_format_name);
699          END IF;
700 
701          /* insert a record into wms_label_requests entity to
702          call the label rules engine to get appropriate label */
703          INV_LABEL.GET_FORMAT_WITH_RULE
704          (  p_document_id        =>p_label_type_info.label_type_id,
705             P_LABEL_FORMAT_ID    => p_label_type_info.manual_format_id,
706             p_delivery_id  =>l_delivery_id,
707             --p_printer_name  =>l_printer,-- Removed in R12: 4396558
708             P_BUSINESS_FLOW_CODE =>   p_label_type_info.business_flow_code,
709             P_LAST_UPDATE_DATE   =>sysdate,
710             P_LAST_UPDATED_BY    =>FND_GLOBAL.user_id,
711             P_CREATION_DATE      =>sysdate,
712             P_CREATED_BY         =>FND_GLOBAL.user_id,
713             x_return_status      =>l_return_status,
714             x_label_format_id   =>l_label_format_id,
715             x_label_format    =>l_label_format,
716             x_label_request_id  =>l_label_request_id,
717             -- Added by joabraha for Bug 3549300
718             p_customer_id     => v_delivery.customer_id,
719             p_organization_id   => v_delivery.organization_id,
720 	    -- Added by dchithir for bug 5740331
721 	    p_sales_order_header_id  => l_sales_order_header_id,
722 	    p_lpn_id => p_input_param.lpn_id -- 12529483
723             );
724 
725          IF l_return_status <> 'S' THEN
726             FND_MESSAGE.SET_NAME('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
727             FND_MSG_PUB.ADD;
728             l_label_format:= p_label_type_info.default_format_id;
729             l_label_format_id:= p_label_type_info.default_format_name;
730          END IF;
731          IF (l_debug = 1) THEN
732             trace('did apply label ' || l_label_format || ',' || l_label_format_id||',req_id '||l_label_request_id);
733          END IF;
734 
735 
736 
737          IF (l_debug = 1) THEN
738             trace(' Getting printer, manual_printer='||p_label_type_info.manual_printer ||',sub='||l_subinventory_code ||',default printer='||p_label_type_info.default_printer);
739          END IF;
740 
741          -- IF clause Added for Add format/printer for manual request
742          IF p_label_type_info.manual_printer IS NULL THEN
743          -- The p_label_type_info.manual_printer is the one  passed from the manual page.
744          -- As per the design, if a printer is passed from the manual page, then we use that printer irrespective.
745 
746             IF (l_subinventory_code IS NOT NULL) AND (l_subinventory_code <> l_prev_sub) THEN
747                IF (l_debug = 1) THEN
748                   trace('getting printer with sub '||l_subinventory_code);
749                END IF;
750                BEGIN
751                   WSH_REPORT_PRINTERS_PVT.GET_PRINTER(
752                      p_concurrent_program_id=>p_label_type_info.label_type_id,
753                      p_user_id              =>fnd_global.user_id,
754                      p_responsibility_id    =>fnd_global.resp_id,
755                      p_application_id       =>fnd_global.resp_appl_id,
756                      p_organization_id      =>l_organization_id,
757                      p_zone                 =>l_subinventory_code,
758                      p_format_id         =>l_label_format_id, --added in r12 RFID 4396558
759                      x_printer              =>l_printer,
760                      x_api_status           =>l_api_status,
761                      x_error_message        =>l_error_message);
762                   IF l_api_status <> 'S' THEN
763                      IF (l_debug = 1) THEN
764                         trace('Error in calling get_printer, set printer as default printer, err_msg:'||l_error_message);
765                      END IF;
766                      l_printer := p_label_type_info.default_printer;
767                   END IF;
768 
769                EXCEPTION
770                   WHEN others THEN
771                      l_printer := p_label_type_info.default_printer;
772                END;
773                 l_prev_sub := l_subinventory_code;
774             END IF;
775          ELSE
776             IF (l_debug = 1) THEN
777                trace('Set printer as Manual Printer passed in:' || p_label_type_info.manual_printer );
778             END IF;
779             l_printer := p_label_type_info.manual_printer;
780          END IF;
781 
782 
783 
784 
785 
786          IF p_label_type_info.manual_format_id IS NOT NULL THEN
787             l_label_format_id := p_label_type_info.manual_format_id;
788             l_label_format := p_label_type_info.manual_format_name;
789             IF (l_debug = 1) THEN
790                trace('Manual format passed in:'||l_label_format_id||','||l_label_format);
791             END IF;
792          END IF;
793          IF (l_label_format_id IS NOT NULL) THEN
794             -- Derive the fields for the format either passed in or derived via the rules engine.
795             IF l_label_format_id <> nvl(l_prev_format_id, -999) THEN
796                IF (l_debug = 1) THEN
797                   trace(' Getting variables for new format ' || l_label_format);
798                END IF;
799                INV_LABEL.GET_VARIABLES_FOR_FORMAT(
800                   x_variables       => l_selected_fields
801                ,  x_variables_count => l_selected_fields_count
802                ,  p_format_id    => l_label_format_id);
803 
804                l_prev_format_id := l_label_format_id;
805 
806                IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
807                   IF (l_debug = 1) THEN
808                      trace('no fields defined for this format: ' || l_label_format|| ',' ||l_label_format_id);
809                   END IF;
810                   GOTO NextLabel;
811                END IF;
812                IF (l_debug = 1) THEN
813                   trace('   Found selected_fields for format ' || l_label_format ||', num='|| l_selected_fields_count);
814                END IF;
815             END IF;
816          ELSE
817             IF (l_debug = 1) THEN
818                trace('No format exists for this label, goto nextlabel');
819             END IF;
820             GOTO NextLabel;
821          END IF;
822 
823          /* variable header */
824          l_delivery_data := l_delivery_data || LABEL_B;
825          IF l_label_format <> nvl(p_label_type_info.default_format_name, '@@@') THEN
826             l_delivery_data := l_delivery_data || ' _FORMAT="' || nvl(p_label_type_info.manual_format_name, l_label_format) || '"';
827          END IF;
828          IF (l_printer IS NOT NULL) AND (l_printer <> nvl(p_label_type_info.default_printer,'###')) THEN
829             l_delivery_data := l_delivery_data || ' _PRINTERNAME="'||l_printer||'"';
830          END IF;
831 
832          l_delivery_data := l_delivery_data || TAG_E;
833 
834 
835          IF (l_debug = 1) THEN
836             trace('Starting assign variables, ');
837          END IF;
838 
839          l_column_name_list := 'Set variables for ';
840 
841          /* Modified for Bug 4072474 -start*/
842          l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
843          /* Modified for Bug 4072474 -End*/
844 
845          -- Fix for bug: 4179593 Start
846          l_CustSqlWarnFlagSet := FALSE;
847          l_CustSqlErrFlagSet := FALSE;
848          l_CustSqlWarnMsg := NULL;
849          l_CustSqlErrMsg := NULL;
850          -- Fix for bug: 4179593 End
851 
852          /* Loop for each selected fields, find the columns and write into the XML_content*/
853          FOR i IN 1..l_selected_fields.count LOOP
854 
855             IF (l_debug = 1) THEN
856                   l_column_name_list := l_column_name_list || ',' ||l_selected_fields(i).column_name;
857             END IF;
858 
859 ---------------------------------------------------------------------------------------------
860 -- Project: 'Custom Labels' (A 11i10+ Project)                                               |
861 -- Author: Dinesh ([email protected])                                                      |
862 -- Change Description:                                                                       |
863 --  The check (SQL_STMT <> NULL and COLUMN_NAME = NULL) implies that the field is a          |
864 --  Custom SQL based field. Handle it appropriately.                                         |
865 ---------------------------------------------------------------------------------------------
866            IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
867              IF (l_debug = 1) THEN
868               trace('Custom Labels Trace [INVLAP7B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
869               trace('Custom Labels Trace [INVLAP7B.pls]: LABEL_FIELD_ID       : ' || l_selected_fields(i).label_field_id);
870               trace('Custom Labels Trace [INVLAP7B.pls]: FIELD_VARIABLE_NAME  : ' || l_selected_fields(i).variable_name);
871               trace('Custom Labels Trace [INVLAP7B.pls]: COLUMN_NAME          : ' || l_selected_fields(i).column_name);
872               trace('Custom Labels Trace [INVLAP7B.pls]: SQL_STMT             : ' || l_selected_fields(i).sql_stmt);
873              END IF;
874              l_sql_stmt := l_selected_fields(i).sql_stmt;
875              IF (l_debug = 1) THEN
876               trace('Custom Labels Trace [INVLAP7B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
877              END IF;
878              l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
879              IF (l_debug = 1) THEN
880               trace('Custom Labels Trace [INVLAP7B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
881 
882              END IF;
883              BEGIN
884              IF (l_debug = 1) THEN
885               trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 1');
886               trace('Custom Labels Trace [INVLAP7B.pls]: LABEL_REQUEST_ID     : ' || l_label_request_id);
887              END IF;
888              OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
889              LOOP
890                 FETCH c_sql_stmt INTO l_sql_stmt_result;
891                 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
892              END LOOP;
893 
894              IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
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_NULL_VALUE_RETURNED');
898                 fnd_msg_pub.ADD;
899                 -- Fix for bug: 4179593 Start
900                 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
901                 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
902                 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
903                 l_CustSqlWarnFlagSet := TRUE;
904                 -- Fix for bug: 4179593 End
905                IF (l_debug = 1) THEN
906                 trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 2');
907                 trace('Custom Labels Trace [INVLAP7B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
908                 trace('Custom Labels Trace [INVLAP7B.pls]: WARNING: NULL value returned by the custom SQL Query.');
909                 trace('Custom Labels Trace [INVLAP7B.pls]: l_custom_sql_ret_status  is set to : ' || l_custom_sql_ret_status );
910                END IF;
911              ELSIF c_sql_stmt%rowcount=0 THEN
912                IF (l_debug = 1) THEN
913                 trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 3');
914                 trace('Custom Labels Trace [INVLAP7B.pls]: WARNING: No row returned by the Custom SQL query');
915                END IF;
916                x_return_status := FND_API.G_RET_STS_SUCCESS;
917                l_custom_sql_ret_status  := INV_LABEL.G_WARNING;
918                fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
919                fnd_msg_pub.ADD;
920                /* Replaced following statement for Bug 4207625: Anupam Jain*/
921                /*fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_status);*/
922                -- Fix for bug: 4179593 Start
923                --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
924                l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
925                l_CustSqlWarnMsg := l_custom_sql_ret_msg;
926                l_CustSqlWarnFlagSet := TRUE;
927                -- Fix for bug: 4179593 End
928              ELSIF c_sql_stmt%rowcount>=2 THEN
929                IF (l_debug = 1) THEN
930                 trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 4');
931                 trace('Custom Labels Trace [INVLAP7B.pls]: ERROR: Multiple values returned by the Custom SQL query');
932                END IF;
933                l_sql_stmt_result := NULL;
934                x_return_status := FND_API.G_RET_STS_SUCCESS;
935                l_custom_sql_ret_status  := FND_API.G_RET_STS_ERROR;
936                fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
937                fnd_msg_pub.ADD;
938                /* Replaced following statement for Bug 4207625: Anupam Jain*/
939                /*fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_status);*/
940                -- Fix for bug: 4179593 Start
941                --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
942                l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
943                l_CustSqlErrMsg := l_custom_sql_ret_msg;
944                l_CustSqlErrFlagSet := TRUE;
945                -- Fix for bug: 4179593 End
946              END IF;
947              IF (c_sql_stmt%ISOPEN) THEN
948                CLOSE c_sql_stmt;
949              END IF;
950             EXCEPTION
951             WHEN OTHERS THEN
952             IF (c_sql_stmt%ISOPEN) THEN
953                CLOSE c_sql_stmt;
954             END IF;
955               IF (l_debug = 1) THEN
956                 trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 5');
957                 trace('Custom Labels Trace [INVLAP7B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
958               END IF;
959               x_return_status := FND_API.G_RET_STS_ERROR;
960               fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
961               fnd_msg_pub.ADD;
962               fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
963               RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
964             END;
965             IF (l_debug = 1) THEN
966               trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 6');
967               trace('Custom Labels Trace [INVLAP7B.pls]: Before assigning it to l_delivery_data');
968             END IF;
969             l_delivery_data  :=   l_delivery_data
970                            || variable_b
971                            || l_selected_fields(i).variable_name
972                            || '">'
973                            || l_sql_stmt_result
974                            || variable_e;
975             l_sql_stmt_result := NULL;
976             l_sql_stmt        := NULL;
977             IF (l_debug = 1) THEN
978               trace('Custom Labels Trace [INVLAP7B.pls]: At Breadcrumb 7');
979               trace('Custom Labels Trace [INVLAP7B.pls]: After assigning it to l_delivery_data');
980               trace('Custom Labels Trace [INVLAP7B.pls]: --------------------------REPORT END-------------------------------------');
981             END IF;
982 ------------------------End of this change for Custom Labels project code--------------------
983             ELSIF LOWER(l_selected_fields(i).column_name) = 'current_date' THEN
984                l_delivery_data := l_delivery_data || VARIABLE_B ||
985                l_selected_fields(i).variable_name || '">' || INV_LABEL.G_DATE || VARIABLE_E;
986             ELSIF LOWER(l_selected_fields(i).column_name) = 'current_time' THEN
987                l_delivery_data := l_delivery_data || VARIABLE_B ||
988                l_selected_fields(i).variable_name || '">' || INV_LABEL.G_TIME || VARIABLE_E;
989             ELSIF LOWER(l_selected_fields(i).column_name) = 'request_user' THEN
990                l_delivery_data := l_delivery_data || VARIABLE_B ||
991                l_selected_fields(i).variable_name || '">' || INV_LABEL.G_USER || VARIABLE_E;
992             ELSIF LOWER(l_selected_fields(i).column_name) = 'airbill' THEN
993                l_delivery_data := l_delivery_data || VARIABLE_B ||
994                l_selected_fields(i).variable_name || '">' || v_delivery.airbill || VARIABLE_E;
995             ELSIF LOWER(l_selected_fields(i).column_name) = 'bill_of_lading' THEN
996                l_delivery_data := l_delivery_data || VARIABLE_B ||
997                l_selected_fields(i).variable_name || '">' || v_delivery.bill_of_lading || VARIABLE_E;
998             ELSIF LOWER(l_selected_fields(i).column_name) = 'carrier' THEN
999                l_delivery_data := l_delivery_data || VARIABLE_B ||
1000                l_selected_fields(i).variable_name || '">' || v_delivery.carrier || VARIABLE_E;
1001             ELSIF LOWER(l_selected_fields(i).column_name) = 'customer' THEN
1002                l_delivery_data := l_delivery_data || VARIABLE_B ||
1003                l_selected_fields(i).variable_name || '">' || v_delivery.customer || VARIABLE_E;
1004             ELSIF LOWER(l_selected_fields(i).column_name) = 'delivery_number' THEN
1005                l_delivery_data := l_delivery_data || VARIABLE_B ||
1006                 l_selected_fields(i).variable_name || '">' || v_delivery.delivery_number || VARIABLE_E;
1007             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_postal_code' THEN
1008                l_delivery_data := l_delivery_data || VARIABLE_B ||
1009                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_postal_code || VARIABLE_E;
1010             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_state' THEN
1011                l_delivery_data := l_delivery_data || VARIABLE_B ||
1012                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_state || VARIABLE_E;
1013             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_address1' THEN
1014                l_delivery_data := l_delivery_data || VARIABLE_B ||
1015                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_address1 || VARIABLE_E;
1016             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_address2' THEN
1017                l_delivery_data := l_delivery_data || VARIABLE_B ||
1018                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_address2 || VARIABLE_E;
1019             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_address3' THEN
1020                l_delivery_data := l_delivery_data || VARIABLE_B ||
1021                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_address3 || VARIABLE_E;
1022             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_address4' THEN
1023                l_delivery_data := l_delivery_data || VARIABLE_B ||
1024                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_address4 || VARIABLE_E;
1025             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_city' THEN
1026                l_delivery_data := l_delivery_data || VARIABLE_B ||
1027                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_city || VARIABLE_E;
1028             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_country' THEN
1029                l_delivery_data := l_delivery_data || VARIABLE_B ||
1030                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_country || VARIABLE_E;
1031             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_county' THEN
1032                l_delivery_data := l_delivery_data || VARIABLE_B ||
1033                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_county || VARIABLE_E;
1034             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_province' THEN
1035                l_delivery_data := l_delivery_data || VARIABLE_B ||
1036                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_province || VARIABLE_E;
1037             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_state' THEN
1038                l_delivery_data := l_delivery_data || VARIABLE_B ||
1039                l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_state || VARIABLE_E;
1040             ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_gross_weight' THEN
1041                l_delivery_data := l_delivery_data || VARIABLE_B ||
1042                l_selected_fields(i).variable_name || '">' || v_delivery.shipment_gross_weight || VARIABLE_E;
1043             ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_gross_weight_uom' THEN
1044                l_delivery_data := l_delivery_data || VARIABLE_B ||
1045                l_selected_fields(i).variable_name || '">' || v_delivery.shipment_gross_weight_uom || VARIABLE_E;
1046             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_method' THEN
1047                l_delivery_data := l_delivery_data || VARIABLE_B ||
1048                l_selected_fields(i).variable_name || '">' || v_delivery.ship_method || VARIABLE_E;
1049             ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_tare_weight' THEN
1050                l_delivery_data := l_delivery_data || VARIABLE_B ||
1051                l_selected_fields(i).variable_name || '">' || v_delivery.shipment_tare_weight || VARIABLE_E;
1052             ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_tare_weight_uom' THEN
1053                l_delivery_data := l_delivery_data || VARIABLE_B ||
1054                l_selected_fields(i).variable_name || '">' || v_delivery.shipment_tare_weight_uom || VARIABLE_E;
1055             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_address1' THEN
1056                l_delivery_data := l_delivery_data || VARIABLE_B ||
1057                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_address1 || VARIABLE_E;
1058             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_address2' THEN
1059                l_delivery_data := l_delivery_data || VARIABLE_B ||
1060                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_address2 || VARIABLE_E;
1061             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_address3' THEN
1062                l_delivery_data := l_delivery_data || VARIABLE_B ||
1063                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_address3 || VARIABLE_E;
1064             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_address4' THEN
1065                l_delivery_data := l_delivery_data || VARIABLE_B ||
1066                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_address4 || VARIABLE_E;
1067             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_city' THEN
1068                l_delivery_data := l_delivery_data || VARIABLE_B ||
1069                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_city || VARIABLE_E;
1070             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_country' THEN
1071                l_delivery_data := l_delivery_data || VARIABLE_B ||
1072                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_country || VARIABLE_E;
1073             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_county' THEN
1074                l_delivery_data := l_delivery_data || VARIABLE_B ||
1075                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_county || VARIABLE_E;
1076             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_postal_code' THEN
1077                l_delivery_data := l_delivery_data || VARIABLE_B ||
1078                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_postal_code || VARIABLE_E;
1079             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_province' THEN
1080                l_delivery_data := l_delivery_data || VARIABLE_B ||
1081                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_province || VARIABLE_E;
1082             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_state' THEN
1083                l_delivery_data := l_delivery_data || VARIABLE_B ||
1084                l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_state || VARIABLE_E;
1085             ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_volume' THEN
1086                l_delivery_data := l_delivery_data || VARIABLE_B ||
1087                l_selected_fields(i).variable_name || '">' || v_delivery.shipment_volume || VARIABLE_E;
1088             ELSIF LOWER(l_selected_fields(i).column_name) = 'shipment_volume_uom' THEN
1089                l_delivery_data := l_delivery_data || VARIABLE_B ||
1090                l_selected_fields(i).variable_name || '">' || v_delivery.shipment_volume_uom || VARIABLE_E;
1091             ELSIF LOWER(l_selected_fields(i).column_name) = 'total_number_of_lpns' THEN
1092                l_delivery_data := l_delivery_data || VARIABLE_B ||
1093                l_selected_fields(i).variable_name || '">' || v_delivery.total_number_of_lpns || VARIABLE_E;
1094             ELSIF LOWER(l_selected_fields(i).column_name) = 'waybill' THEN
1095                l_delivery_data := l_delivery_data || VARIABLE_B ||
1096                l_selected_fields(i).variable_name || '">' || v_delivery.waybill || VARIABLE_E;
1097             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute1' THEN
1098                l_delivery_data := l_delivery_data || VARIABLE_B ||
1099                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute1 || VARIABLE_E;
1100             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute2' THEN
1101                l_delivery_data := l_delivery_data || VARIABLE_B ||
1102                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute2 || VARIABLE_E;
1103             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute3' THEN
1104                l_delivery_data := l_delivery_data || VARIABLE_B ||
1105                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute3 || VARIABLE_E;
1106             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute4' THEN
1107                l_delivery_data := l_delivery_data || VARIABLE_B ||
1108                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute4 || VARIABLE_E;
1109             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute5' THEN
1110                l_delivery_data := l_delivery_data || VARIABLE_B ||
1111                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute5 || VARIABLE_E;
1112             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute6' THEN
1113                l_delivery_data := l_delivery_data || VARIABLE_B ||
1114                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute6 || VARIABLE_E;
1115             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute7' THEN
1116                l_delivery_data := l_delivery_data || VARIABLE_B ||
1117                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute7 || VARIABLE_E;
1118             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute8' THEN
1119                l_delivery_data := l_delivery_data || VARIABLE_B ||
1120                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute8 || VARIABLE_E;
1121             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute9' THEN
1122                l_delivery_data := l_delivery_data || VARIABLE_B ||
1123                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute9 || VARIABLE_E;
1124             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute10' THEN
1125                l_delivery_data := l_delivery_data || VARIABLE_B ||
1126                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute10 || VARIABLE_E;
1127             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute11' THEN
1128                l_delivery_data := l_delivery_data || VARIABLE_B ||
1129                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute11 || VARIABLE_E;
1130             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute12' THEN
1131                l_delivery_data := l_delivery_data || VARIABLE_B ||
1132                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute12 || VARIABLE_E;
1133             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute13' THEN
1134                l_delivery_data := l_delivery_data || VARIABLE_B ||
1135                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute13 || VARIABLE_E;
1136             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute14' THEN
1137                l_delivery_data := l_delivery_data || VARIABLE_B ||
1138                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute14 || VARIABLE_E;
1139             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute15' THEN
1140                l_delivery_data := l_delivery_data || VARIABLE_B ||
1141                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute15 || VARIABLE_E;
1142             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_attribute_category' THEN
1143                l_delivery_data := l_delivery_data || VARIABLE_B ||
1144                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_attribute_category || VARIABLE_E;
1145 
1146              ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr1' THEN
1147                l_delivery_data := l_delivery_data || VARIABLE_B ||
1148                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr1 || VARIABLE_E;
1149             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr2' THEN
1150                l_delivery_data := l_delivery_data || VARIABLE_B ||
1151                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr2 || VARIABLE_E;
1152             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr3' THEN
1153                l_delivery_data := l_delivery_data || VARIABLE_B ||
1154                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr3 || VARIABLE_E;
1155             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr4' THEN
1156                l_delivery_data := l_delivery_data || VARIABLE_B ||
1157                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr4 || VARIABLE_E;
1158             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr5' THEN
1159                l_delivery_data := l_delivery_data || VARIABLE_B ||
1160                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr5 || VARIABLE_E;
1161             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr6' THEN
1162                l_delivery_data := l_delivery_data || VARIABLE_B ||
1163                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr6 || VARIABLE_E;
1164             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr7' THEN
1165                l_delivery_data := l_delivery_data || VARIABLE_B ||
1166                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr7 || VARIABLE_E;
1167             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr8' THEN
1168                l_delivery_data := l_delivery_data || VARIABLE_B ||
1169                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr8 || VARIABLE_E;
1170             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr9' THEN
1171                l_delivery_data := l_delivery_data || VARIABLE_B ||
1172                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr9 || VARIABLE_E;
1173             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr10' THEN
1174                l_delivery_data := l_delivery_data || VARIABLE_B ||
1175                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr10 || VARIABLE_E;
1176             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr11' THEN
1177                l_delivery_data := l_delivery_data || VARIABLE_B ||
1178                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr11 || VARIABLE_E;
1179             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr12' THEN
1180                l_delivery_data := l_delivery_data || VARIABLE_B ||
1181                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr12 || VARIABLE_E;
1182             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr13' THEN
1183                l_delivery_data := l_delivery_data || VARIABLE_B ||
1184                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr13 || VARIABLE_E;
1185             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr14' THEN
1186                l_delivery_data := l_delivery_data || VARIABLE_B ||
1187                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr14 || VARIABLE_E;
1188             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr15' THEN
1189                l_delivery_data := l_delivery_data || VARIABLE_B ||
1190                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr15 || VARIABLE_E;
1191             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_tp_attr_category' THEN
1192                l_delivery_data := l_delivery_data || VARIABLE_B ||
1193                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_tp_attr_category || VARIABLE_E;
1194 
1195              ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr1' THEN
1196                l_delivery_data := l_delivery_data || VARIABLE_B ||
1197                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr1 || VARIABLE_E;
1198             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr2' THEN
1199                l_delivery_data := l_delivery_data || VARIABLE_B ||
1200                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr2 || VARIABLE_E;
1201             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr3' THEN
1202                l_delivery_data := l_delivery_data || VARIABLE_B ||
1203                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr3 || VARIABLE_E;
1204             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr4' THEN
1205                l_delivery_data := l_delivery_data || VARIABLE_B ||
1206                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr4 || VARIABLE_E;
1207             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr5' THEN
1208                l_delivery_data := l_delivery_data || VARIABLE_B ||
1209                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr5 || VARIABLE_E;
1210             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr6' THEN
1211                l_delivery_data := l_delivery_data || VARIABLE_B ||
1212                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr6 || VARIABLE_E;
1213             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr7' THEN
1214                l_delivery_data := l_delivery_data || VARIABLE_B ||
1215                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr7 || VARIABLE_E;
1216             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr8' THEN
1217                l_delivery_data := l_delivery_data || VARIABLE_B ||
1218                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr8 || VARIABLE_E;
1219             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr9' THEN
1220                l_delivery_data := l_delivery_data || VARIABLE_B ||
1221                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr9 || VARIABLE_E;
1222             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr10' THEN
1223                l_delivery_data := l_delivery_data || VARIABLE_B ||
1224                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr10 || VARIABLE_E;
1225             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr11' THEN
1226                l_delivery_data := l_delivery_data || VARIABLE_B ||
1227                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr11 || VARIABLE_E;
1228             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr12' THEN
1229                l_delivery_data := l_delivery_data || VARIABLE_B ||
1230                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr12 || VARIABLE_E;
1231             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr13' THEN
1232                l_delivery_data := l_delivery_data || VARIABLE_B ||
1233                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr13 || VARIABLE_E;
1234             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr14' THEN
1235                l_delivery_data := l_delivery_data || VARIABLE_B ||
1236                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr14 || VARIABLE_E;
1237             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr15' THEN
1238                l_delivery_data := l_delivery_data || VARIABLE_B ||
1239                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr15 || VARIABLE_E;
1240             ELSIF LOWER(l_selected_fields(i).column_name) = 'new_del_global_attr_category' THEN
1241                l_delivery_data := l_delivery_data || VARIABLE_B ||
1242                l_selected_fields(i).variable_name || '">' || v_delivery.new_del_global_attr_category || VARIABLE_E;
1243             --Bug 3969347-Added the label fields ship_from_addressee and ship_to_addressee.
1244             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_from_addressee' THEN
1245                    l_delivery_data := l_delivery_data || VARIABLE_B ||
1246                     l_selected_fields(i).variable_name || '">' || v_delivery.ship_from_addressee || VARIABLE_E;
1247             ELSIF LOWER(l_selected_fields(i).column_name) = 'ship_to_addressee' THEN
1248                    l_delivery_data := l_delivery_data || VARIABLE_B ||
1249                     l_selected_fields(i).variable_name || '">' || v_delivery.ship_to_addressee || VARIABLE_E;
1250            --End of fix for Bug 3969347.
1251 
1252           END IF;
1253 
1254          END LOOP;
1255          l_delivery_data := l_delivery_data || LABEL_E;
1256          x_variable_content(l_label_index).label_content := l_delivery_data;
1257          x_variable_content(l_label_index).label_request_id := l_label_request_id;
1258 
1259 ------------------------Start of changes for Custom Labels project code------------------
1260 
1261         -- Fix for bug: 4179593 Start
1262         IF (l_CustSqlWarnFlagSet) THEN
1263          l_custom_sql_ret_status := INV_LABEL.G_WARNING;
1264          l_custom_sql_ret_msg := l_CustSqlWarnMsg;
1265         END IF;
1266 
1267         IF (l_CustSqlErrFlagSet) THEN
1268          l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
1269          l_custom_sql_ret_msg := l_CustSqlErrMsg;
1270         END IF;
1271         -- Fix for bug: 4179593 End
1272 
1273         x_variable_content(l_label_index).label_status      := l_custom_sql_ret_status ;
1274         x_variable_content(l_label_index).error_message     := l_custom_sql_ret_msg;
1275 ------------------------End of this changes for Custom Labels project code---------------
1276 
1277          l_label_index := l_label_index +1;
1278          <<NextLabel>>
1279          l_delivery_data := '';
1280          l_label_request_id := null;
1281 
1282 ------------------------Start of changes for Custom Labels project code------------------
1283         l_custom_sql_ret_status := NULL;
1284         l_custom_sql_ret_msg    := NULL;
1285 ------------------------End of this changes for Custom Labels project code---------------
1286 
1287          IF (l_debug = 1) THEN
1288             trace(l_column_name_list);
1289                trace('  Finished writing item variables ');
1290          END IF;
1291       END LOOP;
1292 
1293       IF p_label_type_info.business_flow_code in (22)
1294       THEN
1295          FETCH c_cart_shipping_pkg INTO l_delivery_id;
1296             IF c_cart_shipping_pkg%NOTFOUND
1297             THEN
1298                IF (l_debug = 1) THEN
1299                   trace(' No more delivery_id found for cartonization');
1300                END IF;
1301                CLOSE c_cart_shipping_pkg;
1302                l_delivery_id := null;
1303             ELSE
1304                IF (l_debug = 1) THEN
1305                   Trace('Found next delivery_id=' || l_delivery_id);
1306                END IF;
1307             END IF;
1308       ELSE
1309          l_delivery_id := null;
1310       END IF;
1311 
1312    END LOOP;
1313 END get_variable_data;
1314 
1315 PROCEDURE get_variable_data(
1316    x_variable_content   OUT NOCOPY LONG
1317 ,  x_msg_count    OUT NOCOPY NUMBER
1318 ,  x_msg_data           OUT NOCOPY VARCHAR2
1319 ,  x_return_status      OUT NOCOPY VARCHAR2
1320 ,  p_label_type_info IN INV_LABEL.label_type_rec
1321 ,  p_transaction_id  IN NUMBER
1322 ,  p_input_param     IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
1323 ,  p_transaction_identifier IN NUMBER
1324 ) IS
1325    l_variable_data_tbl INV_LABEL.label_tbl_type;
1326 BEGIN
1327    get_variable_data(
1328       x_variable_content   => l_variable_data_tbl
1329    ,  x_msg_count    => x_msg_count
1330    ,  x_msg_data           => x_msg_data
1331    ,  x_return_status      => x_return_status
1332    ,  p_label_type_info => p_label_type_info
1333    ,  p_transaction_id  => p_transaction_id
1334    ,  p_input_param     => p_input_param
1335    ,  p_transaction_identifier=> p_transaction_identifier
1336    );
1337 
1338    x_variable_content := '';
1339 
1340    FOR i IN 1..l_variable_data_tbl.count() LOOP
1341       x_variable_content := x_variable_content || l_variable_data_tbl(i).label_content;
1342    END LOOP;
1343 
1344 END get_variable_data;
1345 
1346 
1347 END INV_LABEL_PVT7;