DBA Data[Home] [Help]

PACKAGE BODY: APPS.JL_BR_SPED_PKG

Source


1 PACKAGE BODY JL_BR_SPED_PKG AS
2 /* $Header: jlspedab.pls 120.12 2011/03/09 12:12:55 rsaini ship $ */
3 
4 
5 /*
6 	Function Name: IS_INVOICE_FINAL.
7 	Description  : This function will return T - True if the country code is not Brazil.
8 	If the country code is Brazil the function will return T - True if the electronic invoice
9         issuing source is not enabled for the invoice, or if the electronic invoicing issuing
10         source for the invoice is enabled and the electronic invoice status is either
11         Finalized or Contingency.
12 
13 */
14      FUNCTION IS_INVOICE_FINAL
15                   (P_CUSTOMER_TRX_ID  IN NUMBER) RETURN VARCHAR2
16 
17      IS
18       -- Declaration part
19 
20         CURSOR C_BATCH_SOURCE IS
21                 SELECT NVL(B.GLOBAL_ATTRIBUTE5, 'N') GLOBAL_ATTRIBUTE5
22                 FROM RA_BATCH_SOURCES B, RA_CUSTOMER_TRX T
23                 WHERE T.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
24                 AND B.BATCH_SOURCE_ID = T.BATCH_SOURCE_ID;
25 
26         CURSOR C_CUST_EXT IS
27                 SELECT COUNT(*) as CTR
28                 FROM JL_BR_CUSTOMER_TRX_EXTS
29                 WHERE ELECTRONIC_INV_STATUS IN ('2','7')
30                 AND CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
31                 AND NOT EXISTS ( SELECT 'X' FROM AR_PAYMENT_SCHEDULES
32                 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
33                 AND SELECTED_FOR_RECEIPT_BATCH_ID = -999);
34 
35         l_proceed_flag VARCHAR2(1) := 'F';
36         l_country_code VARCHAR2(30);
37 
38       BEGIN
39 
40             FND_PROFILE.GET('JGZZ_COUNTRY_CODE', l_country_code);
41 
42             IF (l_country_code = 'BR') THEN
43 
44               FOR batch_source_rec IN C_BATCH_SOURCE LOOP
45 
46                   IF  batch_source_rec.GLOBAL_ATTRIBUTE5 = 'Y' THEN
47 
48                       FOR cust_ext_rec IN C_CUST_EXT LOOP
49                           IF cust_ext_rec.CTR = 0 THEN
50                               l_proceed_flag := 'F';
51                           ELSE
52                               l_proceed_flag := 'T';
53                           END IF;
54                       END LOOP;
55                   ELSE
56                       l_proceed_flag := 'T';
57                   END IF;
58               END LOOP;
59 
60             ELSE
61                         l_proceed_flag := 'T';
62             END IF;
63 
64       RETURN l_proceed_flag;
65 
66     EXCEPTION
67           WHEN OTHERS THEN
68                 l_proceed_flag := 'F';
69                 RETURN l_proceed_flag;
70     END IS_INVOICE_FINAL;
71 
72 
73     PROCEDURE Set_Trx_Lock_Status(p_customer_trx_id IN NUMBER) IS
74 
75     CURSOR C_BATCH_SOURCE IS
76           SELECT NVL(B.GLOBAL_ATTRIBUTE5, 'N') GLOBAL_ATTRIBUTE5
77                    FROM RA_BATCH_SOURCES B, RA_CUSTOMER_TRX T
78                    WHERE T.CUSTOMER_TRX_ID = p_customer_trx_id
79                          AND B.BATCH_SOURCE_ID = T.BATCH_SOURCE_ID;
80     l_country_code varchar2(30);
81     BEGIN
82          FND_PROFILE.GET('JGZZ_COUNTRY_CODE', l_country_code);
83 
84          IF (l_country_code = 'BR') THEN
85 
86           FOR BATCH_SOURCE_REC IN C_BATCH_SOURCE LOOP
87             IF  BATCH_SOURCE_REC.GLOBAL_ATTRIBUTE5 = 'Y' THEN
88                 UPDATE AR_PAYMENT_SCHEDULES_ALL
89                       SET SELECTED_FOR_RECEIPT_BATCH_ID = -999
90                       WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
91             END IF;
92           END LOOP;
93          END IF;
94     END Set_Trx_Lock_Status;
95 
96     /* Function Name : Copy_GDF_Attributes
97        Description   : This function will be called from the core AR Autoinvoice program and the Copy Transactions program
98                        for inserting the Electronic Invoice attributes in the  JL_BR_CUSTOMER_TRX_EXTS extension table*/
99     FUNCTION COPY_GDF_ATTRIBUTES (P_REQUEST_ID IN NUMBER, P_CALLED_FROM IN VARCHAR2) RETURN NUMBER IS
100 
101             TYPE HEADER_GDF_ATTR_TYPE  IS TABLE OF RA_INTERFACE_LINES_ALL.HEADER_GDF_ATTRIBUTE1%TYPE;
102             TYPE LINE_GDF_ATTR_TYPE IS TABLE OF RA_INTERFACE_LINES_ALL.LINE_GDF_ATTRIBUTE1%TYPE;  --ER 9974789
103 
104             TYPE CUSTOMER_TRX_ID_TYPE IS TABLE OF RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE;
105             TYPE CUSTOMER_TRX_LINE_ID_TYPE IS TABLE OF RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
106 
107             TYPE LEGAL_PROCESS_CODE_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.LEGAL_PROCESS_CODE%TYPE;
108             TYPE LEGAL_PROCESS_SOURCE_IND_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.LEGAL_PROCESS_SOURCE_IND%TYPE;
109             TYPE VEHICLE_PLATE_STATE_CODE_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.VEHICLE_PLATE_STATE_CODE%TYPE;
110             TYPE VEHICLE_ANTT_INSCRIPTION_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.VEHICLE_ANTT_INSCRIPTION%TYPE;
111             TYPE TOWING_VEH_PLATE_NUMBER_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_PLATE_NUMBER%TYPE;
112             TYPE TOWING_VEH_PLATE_STATE_CODE_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_PLATE_STATE_CODE%TYPE;
113             TYPE TOWING_VEH_ANTT_INSCRIPTION_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_ANTT_INSCRIPTION%TYPE;
114             TYPE SEAL_NUMBER_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.SEAL_NUMBER%TYPE;
115             TYPE ELECTRONIC_INV_WEB_ADDRESS_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.ELECTRONIC_INV_WEB_ADDRESS%TYPE;
116             TYPE ELECTRONIC_INV_ACCESS_KEY_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.ELECTRONIC_INV_ACCESS_KEY%TYPE;
117             TYPE ELECTRONIC_INV_PROTOCOL_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.ELECTRONIC_INV_PROTOCOL%TYPE;
118             TYPE WAGON_CODE_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.WAGON_CODE%TYPE;
119             TYPE FERRY_CODE_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.FERRY_CODE%TYPE;
120             TYPE SERVICE_EXEC_DATE_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.SRV_EXEC_DATE%TYPE; -- BUG 11825724
121 
122             /*Added for ER 9974789*/
123             TYPE INDUSTRY_ATTRIBUTE_T IS TABLE OF JL_BR_CUST_TRX_LINES_EXTS.INDUSTRY_ATTRIBUTE1%TYPE;
124 
125             AUTO_CUSTOMER_TRX_ID CUSTOMER_TRX_ID_TYPE;
126             AUTO_CUST_TRX_ID CUSTOMER_TRX_ID_TYPE;
127             AUTO_CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID_TYPE;
128             COPY_CUSTOMER_TRX_ID CUSTOMER_TRX_ID_TYPE;
129             COPY_CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID_TYPE;
130 
131             HEADER_GDF_ATTRIBUTE19 HEADER_GDF_ATTR_TYPE;
132             HEADER_GDF_ATTRIBUTE20 HEADER_GDF_ATTR_TYPE;
133             HEADER_GDF_ATTRIBUTE21 HEADER_GDF_ATTR_TYPE;
134             HEADER_GDF_ATTRIBUTE22 HEADER_GDF_ATTR_TYPE;
135             HEADER_GDF_ATTRIBUTE23 HEADER_GDF_ATTR_TYPE;
136             HEADER_GDF_ATTRIBUTE24 HEADER_GDF_ATTR_TYPE;
137             HEADER_GDF_ATTRIBUTE25 HEADER_GDF_ATTR_TYPE;
138             HEADER_GDF_ATTRIBUTE26 HEADER_GDF_ATTR_TYPE;
139             HEADER_GDF_ATTRIBUTE27 HEADER_GDF_ATTR_TYPE;
140             HEADER_GDF_ATTRIBUTE29 HEADER_GDF_ATTR_TYPE;
141             HEADER_GDF_ATTRIBUTE30 HEADER_GDF_ATTR_TYPE;
142             HEADER_GDF_ATTRIBUTE18 HEADER_GDF_ATTR_TYPE;
143             HEADER_GDF_ATTRIBUTE28 HEADER_GDF_ATTR_TYPE;
144 
145             --Added for additional Towing Attributes
146             HEADER_GDF_ATTRIBUTE31 HEADER_GDF_ATTR_TYPE;
147             HEADER_GDF_ATTRIBUTE32 HEADER_GDF_ATTR_TYPE;
148             HEADER_GDF_ATTRIBUTE33 HEADER_GDF_ATTR_TYPE;
149             HEADER_GDF_ATTRIBUTE34 HEADER_GDF_ATTR_TYPE;
150             HEADER_GDF_ATTRIBUTE35 HEADER_GDF_ATTR_TYPE;
151             HEADER_GDF_ATTRIBUTE36 HEADER_GDF_ATTR_TYPE;
152             HEADER_GDF_ATTRIBUTE37 HEADER_GDF_ATTR_TYPE;
153             HEADER_GDF_ATTRIBUTE38 HEADER_GDF_ATTR_TYPE;
154             HEADER_GDF_ATTRIBUTE39 HEADER_GDF_ATTR_TYPE; --BUG 11825724
155 
156             /*Added for ER 9974789*/
157             LINE_GDF_ATTRIBUTE21   LINE_GDF_ATTR_TYPE;
158             LINE_GDF_ATTRIBUTE22   LINE_GDF_ATTR_TYPE;
159             LINE_GDF_ATTRIBUTE23   LINE_GDF_ATTR_TYPE;
160             LINE_GDF_ATTRIBUTE24   LINE_GDF_ATTR_TYPE;
161             LINE_GDF_ATTRIBUTE25   LINE_GDF_ATTR_TYPE;
162             LINE_GDF_ATTRIBUTE26   LINE_GDF_ATTR_TYPE;
163             LINE_GDF_ATTRIBUTE27   LINE_GDF_ATTR_TYPE;
164             LINE_GDF_ATTRIBUTE28   LINE_GDF_ATTR_TYPE;
165             LINE_GDF_ATTRIBUTE29   LINE_GDF_ATTR_TYPE;
166             LINE_GDF_ATTRIBUTE30   LINE_GDF_ATTR_TYPE;
167             LINE_GDF_ATTRIBUTE31   LINE_GDF_ATTR_TYPE;
168             LINE_GDF_ATTRIBUTE32   LINE_GDF_ATTR_TYPE;
169             LINE_GDF_ATTRIBUTE33   LINE_GDF_ATTR_TYPE;
170             LINE_GDF_ATTRIBUTE34   LINE_GDF_ATTR_TYPE;
171             LINE_GDF_ATTRIBUTE35   LINE_GDF_ATTR_TYPE;
172             LINE_GDF_ATTRIBUTE36   LINE_GDF_ATTR_TYPE;
173             LINE_GDF_ATTRIBUTE37   LINE_GDF_ATTR_TYPE;
174             LINE_GDF_ATTRIBUTE38   LINE_GDF_ATTR_TYPE;
175             LINE_GDF_ATTRIBUTE39   LINE_GDF_ATTR_TYPE;
176             LINE_GDF_ATTRIBUTE40   LINE_GDF_ATTR_TYPE;
177             LINE_GDF_ATTRIBUTE41   LINE_GDF_ATTR_TYPE;
178             LINE_GDF_ATTRIBUTE42   LINE_GDF_ATTR_TYPE;
179             LINE_GDF_ATTRIBUTE43   LINE_GDF_ATTR_TYPE;
180             LINE_GDF_ATTRIBUTE44   LINE_GDF_ATTR_TYPE;
181             LINE_GDF_ATTRIBUTE45   LINE_GDF_ATTR_TYPE;
182             LINE_GDF_ATTRIBUTE46   LINE_GDF_ATTR_TYPE;
183             LINE_GDF_ATTRIBUTE47   LINE_GDF_ATTR_TYPE;
184             LINE_GDF_ATTRIBUTE48   LINE_GDF_ATTR_TYPE;
185             LINE_GDF_ATTRIBUTE49   LINE_GDF_ATTR_TYPE;
186             LINE_GDF_ATTRIBUTE50   LINE_GDF_ATTR_TYPE;
187 
188 
189             LEGAL_PROCESS_CODE LEGAL_PROCESS_CODE_T;
190             LEGAL_PROCESS_SOURCE_INDICATOR LEGAL_PROCESS_SOURCE_IND_T;
191             VEHICLE_PLATE_STATE_CODE VEHICLE_PLATE_STATE_CODE_T;
192             VEHICLE_ANTT_INSCRIPTION VEHICLE_ANTT_INSCRIPTION_T;
193             TOWING_VEH_PLATE_NUMBER TOWING_VEH_PLATE_NUMBER_T;
194             TOWING_VEH_PLATE_STATE_CODE TOWING_VEH_PLATE_STATE_CODE_T;
195             -- BUG 10184720 Added for additional 4 sets of Towing Vehicle Attributes
196             -- Start
197             TOWING_VEH_PLATE_NUMBER2 TOWING_VEH_PLATE_NUMBER_T;
198             TOWING_VEH_PLATE_STATE_CODE2 TOWING_VEH_PLATE_STATE_CODE_T;
199             TOWING_VEH_PLATE_NUMBER3 TOWING_VEH_PLATE_NUMBER_T;
200             TOWING_VEH_PLATE_STATE_CODE3 TOWING_VEH_PLATE_STATE_CODE_T;
201             TOWING_VEH_PLATE_NUMBER4 TOWING_VEH_PLATE_NUMBER_T;
202             TOWING_VEH_PLATE_STATE_CODE4 TOWING_VEH_PLATE_STATE_CODE_T;
203             TOWING_VEH_PLATE_NUMBER5 TOWING_VEH_PLATE_NUMBER_T;
204             TOWING_VEH_PLATE_STATE_CODE5 TOWING_VEH_PLATE_STATE_CODE_T;
205             -- End
206             TOWING_VEH_ANTT_INSCRIPTION TOWING_VEH_ANTT_INSCRIPTION_T;
207             SEAL_NUMBER SEAL_NUMBER_T;
208             ELECTRONIC_INV_WEB_ADDRESS ELECTRONIC_INV_WEB_ADDRESS_T;
209             ELECTRONIC_INV_ACCESS_KEY ELECTRONIC_INV_ACCESS_KEY_T;
210             ELECTRONIC_INV_PROTOCOL ELECTRONIC_INV_PROTOCOL_T;
211             WAGON_CODE WAGON_CODE_T;
212             FERRY_CODE FERRY_CODE_T;
213             SERVICE_EXEC_DATE SERVICE_EXEC_DATE_T; --BUG 11825724
214 
215             /*Added for ER 9974789*/
216             INDUSTRY_ATTRIBUTE1 INDUSTRY_ATTRIBUTE_T;
217             INDUSTRY_ATTRIBUTE2 INDUSTRY_ATTRIBUTE_T;
218             INDUSTRY_ATTRIBUTE3 INDUSTRY_ATTRIBUTE_T;
219             INDUSTRY_ATTRIBUTE4 INDUSTRY_ATTRIBUTE_T;
220             INDUSTRY_ATTRIBUTE5 INDUSTRY_ATTRIBUTE_T;
221             INDUSTRY_ATTRIBUTE6 INDUSTRY_ATTRIBUTE_T;
222             INDUSTRY_ATTRIBUTE7 INDUSTRY_ATTRIBUTE_T;
223             INDUSTRY_ATTRIBUTE8 INDUSTRY_ATTRIBUTE_T;
224             INDUSTRY_ATTRIBUTE9 INDUSTRY_ATTRIBUTE_T;
225             INDUSTRY_ATTRIBUTE10 INDUSTRY_ATTRIBUTE_T;
226             INDUSTRY_ATTRIBUTE11 INDUSTRY_ATTRIBUTE_T;
227             INDUSTRY_ATTRIBUTE12 INDUSTRY_ATTRIBUTE_T;
228             INDUSTRY_ATTRIBUTE13 INDUSTRY_ATTRIBUTE_T;
229             INDUSTRY_ATTRIBUTE14 INDUSTRY_ATTRIBUTE_T;
230             INDUSTRY_ATTRIBUTE15 INDUSTRY_ATTRIBUTE_T;
231             INDUSTRY_ATTRIBUTE16 INDUSTRY_ATTRIBUTE_T;
232             INDUSTRY_ATTRIBUTE17 INDUSTRY_ATTRIBUTE_T;
233             INDUSTRY_ATTRIBUTE18 INDUSTRY_ATTRIBUTE_T;
234             INDUSTRY_ATTRIBUTE19 INDUSTRY_ATTRIBUTE_T;
235             INDUSTRY_ATTRIBUTE20 INDUSTRY_ATTRIBUTE_T;
236             INDUSTRY_ATTRIBUTE21 INDUSTRY_ATTRIBUTE_T;
237             INDUSTRY_ATTRIBUTE22 INDUSTRY_ATTRIBUTE_T;
238             INDUSTRY_ATTRIBUTE23 INDUSTRY_ATTRIBUTE_T;
239             INDUSTRY_ATTRIBUTE24 INDUSTRY_ATTRIBUTE_T;
240             INDUSTRY_ATTRIBUTE25 INDUSTRY_ATTRIBUTE_T;
241             INDUSTRY_ATTRIBUTE26 INDUSTRY_ATTRIBUTE_T;
242             INDUSTRY_ATTRIBUTE27 INDUSTRY_ATTRIBUTE_T;
243             INDUSTRY_ATTRIBUTE28 INDUSTRY_ATTRIBUTE_T;
244             INDUSTRY_ATTRIBUTE29 INDUSTRY_ATTRIBUTE_T;
245             INDUSTRY_ATTRIBUTE30 INDUSTRY_ATTRIBUTE_T;
246 
247              CURSOR C_AUTOINV_ATTR IS SELECT
248                     GT.CUSTOMER_TRX_ID,
249                     GT.HEADER_GDF_ATTRIBUTE19,
250                     GT.HEADER_GDF_ATTRIBUTE20,
251                     GT.HEADER_GDF_ATTRIBUTE21,
252                     GT.HEADER_GDF_ATTRIBUTE22,
253                     GT.HEADER_GDF_ATTRIBUTE23,
254                     GT.HEADER_GDF_ATTRIBUTE24,
255                     GT.HEADER_GDF_ATTRIBUTE25,
256                     GT.HEADER_GDF_ATTRIBUTE26,
257                     GT.HEADER_GDF_ATTRIBUTE27,
258                     GT.HEADER_GDF_ATTRIBUTE29,
259                     GT.HEADER_GDF_ATTRIBUTE30,
260                     GT.HEADER_GDF_ATTRIBUTE18,
261                     GT.HEADER_GDF_ATTRIBUTE28
262                     FROM RA_INTERFACE_LINES_GT GT,
263                          RA_CUSTOMER_TRX TRX
264                     WHERE TRX.REQUEST_ID = P_REQUEST_ID
265                           AND GT.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
266                           AND GT.INTERFACE_LINE_ID = (SELECT MIN(GT_2.INTERFACE_LINE_ID)
267                                                       FROM RA_INTERFACE_LINES_GT GT_2
268                                                       WHERE  GT_2.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID);
269 
270              -- BUG 10184720
271              -- Cursor Added for Additional Towing Vehicle Attributes
272              CURSOR C_AUTOINV_TOW_ATTR IS SELECT
273                     GT.CUSTOMER_TRX_ID,
274                     JLEXT.HEADER_GDF_ATTRIBUTE31,
275                     JLEXT.HEADER_GDF_ATTRIBUTE32,
276                     JLEXT.HEADER_GDF_ATTRIBUTE33,
277                     JLEXT.HEADER_GDF_ATTRIBUTE34,
278                     JLEXT.HEADER_GDF_ATTRIBUTE35,
279                     JLEXT.HEADER_GDF_ATTRIBUTE36,
280                     JLEXT.HEADER_GDF_ATTRIBUTE37,
281                     JLEXT.HEADER_GDF_ATTRIBUTE38,
282                     JLEXT.HEADER_GDF_ATTRIBUTE39 --BUG 11825724
283                     FROM RA_INTERFACE_LINES_ALL GT,
284                          RA_CUSTOMER_TRX TRX,
285                          JL_BR_INTERFACE_LINES_EXTS JLEXT
286                     WHERE TRX.REQUEST_ID = P_REQUEST_ID
287                       AND GT.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
288                       AND GT.ROWID = JLEXT.JL_BR_INTERFACE_LINK_ID
289                       AND GT.INTERFACE_LINE_ID = (SELECT MIN(GT_2.INTERFACE_LINE_ID)
290                                                       FROM RA_INTERFACE_LINES_GT GT_2
291                                                       WHERE  GT_2.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID);
292 
293             /*Added for ER 9974789*/
294               CURSOR C_AUTOINV_LINE_ATTR IS SELECT
295                       ARINT.CUSTOMER_TRX_ID,
296                       ARINT.INTERFACE_LINE_ID,
297                       JLEXT.LINE_GDF_ATTRIBUTE21,
298                       JLEXT.LINE_GDF_ATTRIBUTE22,
299                       JLEXT.LINE_GDF_ATTRIBUTE23,
300                       JLEXT.LINE_GDF_ATTRIBUTE24,
301                       JLEXT.LINE_GDF_ATTRIBUTE25,
302                       JLEXT.LINE_GDF_ATTRIBUTE26,
303                       JLEXT.LINE_GDF_ATTRIBUTE27,
304                       JLEXT.LINE_GDF_ATTRIBUTE28,
305                       JLEXT.LINE_GDF_ATTRIBUTE29,
306                       JLEXT.LINE_GDF_ATTRIBUTE30,
307                       JLEXT.LINE_GDF_ATTRIBUTE31,
308                       JLEXT.LINE_GDF_ATTRIBUTE32,
309                       JLEXT.LINE_GDF_ATTRIBUTE33,
310                       JLEXT.LINE_GDF_ATTRIBUTE34,
311                       JLEXT.LINE_GDF_ATTRIBUTE35,
312                       JLEXT.LINE_GDF_ATTRIBUTE36,
313                       JLEXT.LINE_GDF_ATTRIBUTE37,
314                       JLEXT.LINE_GDF_ATTRIBUTE38,
315                       JLEXT.LINE_GDF_ATTRIBUTE39,
316                       JLEXT.LINE_GDF_ATTRIBUTE40,
317                       JLEXT.LINE_GDF_ATTRIBUTE41,
318                       JLEXT.LINE_GDF_ATTRIBUTE42,
319                       JLEXT.LINE_GDF_ATTRIBUTE43,
320                       JLEXT.LINE_GDF_ATTRIBUTE44,
321                       JLEXT.LINE_GDF_ATTRIBUTE45,
322                       JLEXT.LINE_GDF_ATTRIBUTE46,
323                       JLEXT.LINE_GDF_ATTRIBUTE47,
324                       JLEXT.LINE_GDF_ATTRIBUTE48,
325                       JLEXT.LINE_GDF_ATTRIBUTE49,
326                       JLEXT.LINE_GDF_ATTRIBUTE50
327                     FROM JL_BR_INTERFACE_LINES_EXTS JLEXT,
328                          RA_INTERFACE_LINES_ALL ARINT,
329 		         RA_CUSTOMER_TRX TRX
330                     WHERE TRX.REQUEST_ID = P_REQUEST_ID
331                           AND ARINT.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
332                           AND ARINT.ROWID = JLEXT.JL_BR_INTERFACE_LINK_ID;
333 
334 
335               CURSOR C_COPYINV_ATTR IS SELECT
336                      NEW_TRX.CUSTOMER_TRX_ID
337                     ,OLD_TRX.LEGAL_PROCESS_CODE
338                     ,OLD_TRX.LEGAL_PROCESS_SOURCE_IND
339                     ,OLD_TRX.VEHICLE_PLATE_STATE_CODE
340                     ,OLD_TRX.VEHICLE_ANTT_INSCRIPTION
341                     ,OLD_TRX.TOWING_VEH_PLATE_NUMBER
342                     ,OLD_TRX.TOWING_VEH_PLATE_STATE_CODE
343                     ,OLD_TRX.TOWING_VEH_ANTT_INSCRIPTION
344                     ,OLD_TRX.SEAL_NUMBER
345                     ,OLD_TRX.ELECTRONIC_INV_WEB_ADDRESS
346                     ,OLD_TRX.ELECTRONIC_INV_ACCESS_KEY
347                     ,OLD_TRX.ELECTRONIC_INV_PROTOCOL
348                     ,OLD_TRX.WAGON_CODE
349                     ,OLD_TRX.FERRY_CODE
350                     -- BUG 10184720 Added for 4 new set of Towing Vehicle attributes
351                     ,OLD_TRX.TOWING_VEH_PLATE_NUMBER2
352                     ,OLD_TRX.TOWING_VEH_PLATE_STATE_CODE2
353                     ,OLD_TRX.TOWING_VEH_PLATE_NUMBER3
354                     ,OLD_TRX.TOWING_VEH_PLATE_STATE_CODE3
355                     ,OLD_TRX.TOWING_VEH_PLATE_NUMBER4
356                     ,OLD_TRX.TOWING_VEH_PLATE_STATE_CODE4
357                     ,OLD_TRX.TOWING_VEH_PLATE_NUMBER5
358                     ,OLD_TRX.TOWING_VEH_PLATE_STATE_CODE5
359                     ,OLD_TRX.SRV_EXEC_DATE --BUG 11825724 Service Execution Date
360                     FROM JL_BR_CUSTOMER_TRX_EXTS OLD_TRX,
361                         (SELECT TRX_NUMBER, CUSTOMER_TRX_ID,RECURRED_FROM_TRX_NUMBER,BATCH_SOURCE_ID
362                             FROM RA_CUSTOMER_TRX WHERE REQUEST_ID = P_REQUEST_ID) NEW_TRX
363                     WHERE OLD_TRX.CUSTOMER_TRX_ID = (SELECT CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX
364                                                           WHERE TRX_NUMBER = NEW_TRX.RECURRED_FROM_TRX_NUMBER
365                                                             AND BATCH_SOURCE_ID = NEW_TRX.BATCH_SOURCE_ID);
366 
367 	    /*Added for ER 9974789*/
368             CURSOR C_COPYINV_LINE_ATTR IS SELECT
369                     NEW.CUSTOMER_TRX_ID,
370                     NEW.CUSTOMER_TRX_LINE_ID,
371                     OLD.INDUSTRY_ATTRIBUTE1,
372                     OLD.INDUSTRY_ATTRIBUTE2,
373                     OLD.INDUSTRY_ATTRIBUTE3,
374                     OLD.INDUSTRY_ATTRIBUTE4,
375                     OLD.INDUSTRY_ATTRIBUTE5,
376                     OLD.INDUSTRY_ATTRIBUTE6,
377                     OLD.INDUSTRY_ATTRIBUTE7,
378                     OLD.INDUSTRY_ATTRIBUTE8,
379                     OLD.INDUSTRY_ATTRIBUTE9,
380                     OLD.INDUSTRY_ATTRIBUTE10,
381                     OLD.INDUSTRY_ATTRIBUTE11,
382                     OLD.INDUSTRY_ATTRIBUTE12,
383                     OLD.INDUSTRY_ATTRIBUTE13,
384                     OLD.INDUSTRY_ATTRIBUTE14,
385                     OLD.INDUSTRY_ATTRIBUTE15,
386                     OLD.INDUSTRY_ATTRIBUTE16,
387                     OLD.INDUSTRY_ATTRIBUTE17,
388                     OLD.INDUSTRY_ATTRIBUTE18,
389                     OLD.INDUSTRY_ATTRIBUTE19,
390                     OLD.INDUSTRY_ATTRIBUTE20,
391                     OLD.INDUSTRY_ATTRIBUTE21,
392                     OLD.INDUSTRY_ATTRIBUTE22,
393                     OLD.INDUSTRY_ATTRIBUTE23,
394                     OLD.INDUSTRY_ATTRIBUTE24,
395                     OLD.INDUSTRY_ATTRIBUTE25,
396                     OLD.INDUSTRY_ATTRIBUTE26,
397                     OLD.INDUSTRY_ATTRIBUTE27,
398                     OLD.INDUSTRY_ATTRIBUTE28,
399                     OLD.INDUSTRY_ATTRIBUTE29,
400                     OLD.INDUSTRY_ATTRIBUTE30
401 	       FROM JL_BR_CUST_TRX_LINES_EXTS OLD,
402 	            RA_CUSTOMER_TRX TRX,
403                	    RA_CUSTOMER_TRX_LINES TRXL,
404                     (SELECT RATRX.TRX_NUMBER, RATRX.CUSTOMER_TRX_ID, RATRX.RECURRED_FROM_TRX_NUMBER, RATRX.BATCH_SOURCE_ID,
405 			          RATXL.CUSTOMER_TRX_LINE_ID, RATXL.LINE_NUMBER, RATXL.LINE_TYPE
406 			        FROM  RA_CUSTOMER_TRX RATRX, RA_CUSTOMER_TRX_LINES RATXL
407 					WHERE RATRX.REQUEST_ID = P_REQUEST_ID
408 					  AND RATRX.CUSTOMER_TRX_ID = RATXL.CUSTOMER_TRX_ID) NEW
409                     WHERE TRX.CUSTOMER_TRX_ID = TRXL.CUSTOMER_TRX_ID
410                       AND TRX.CUSTOMER_TRX_ID = OLD.CUSTOMER_TRX_ID
411                       AND TRXL.CUSTOMER_TRX_LINE_ID = OLD.CUSTOMER_TRX_LINE_ID
412                       AND NEW.RECURRED_FROM_TRX_NUMBER = TRX.TRX_NUMBER
413                       AND NEW.BATCH_SOURCE_ID = TRX.BATCH_SOURCE_ID --Bug10354228
414                       AND TRXL.LINE_NUMBER = NEW.LINE_NUMBER
415                       AND TRXL.LINE_TYPE NOT IN ('TAX','FREIGHT')
416                       AND TRXL.LINE_TYPE = NEW.LINE_TYPE;
417 
418 
419 
420             L_COUNTRY_CODE      VARCHAR2(30);
421             L_ACCESS_CODE       NUMBER;
422             L_ISSUE_AUTHORITY   VARCHAR2(1);
423     BEGIN
424             FND_PROFILE.GET('JGZZ_COUNTRY_CODE', L_COUNTRY_CODE);
425 
426             BEGIN
427             SELECT NVL(B.GLOBAL_ATTRIBUTE5, 'N') INTO L_ISSUE_AUTHORITY
428                    FROM RA_BATCH_SOURCES B, RA_CUSTOMER_TRX T
429                    WHERE T.REQUEST_ID = P_REQUEST_ID
430                      AND B.BATCH_SOURCE_ID = T.BATCH_SOURCE_ID
431                      AND ROWNUM = 1;
432             EXCEPTION
433               WHEN OTHERS THEN
434               RETURN 0;
435             END;
436 
437 
438             IF L_COUNTRY_CODE = 'BR' THEN
439 
440 	    -- Logic for Receivables AUTOInvoice program
441                     IF P_CALLED_FROM = 'RAXTRX' THEN
442 
443 	               -- Processing for Transaction header Level Attributes
444                             OPEN C_AUTOINV_ATTR;
445                             FETCH C_AUTOINV_ATTR BULK COLLECT INTO
446                                     AUTO_CUSTOMER_TRX_ID,
447                                     HEADER_GDF_ATTRIBUTE19,
448                                     HEADER_GDF_ATTRIBUTE20,
449                                     HEADER_GDF_ATTRIBUTE21,
450                                     HEADER_GDF_ATTRIBUTE22,
451                                     HEADER_GDF_ATTRIBUTE23,
452                                     HEADER_GDF_ATTRIBUTE24,
453                                     HEADER_GDF_ATTRIBUTE25,
454                                     HEADER_GDF_ATTRIBUTE26,
455                                     HEADER_GDF_ATTRIBUTE27,
456                                     HEADER_GDF_ATTRIBUTE29,
457                                     HEADER_GDF_ATTRIBUTE30,
458                                     HEADER_GDF_ATTRIBUTE18,
459                                     HEADER_GDF_ATTRIBUTE28;
460                             CLOSE C_AUTOINV_ATTR;
461 
462                             IF AUTO_CUSTOMER_TRX_ID.COUNT > 0 THEN
463 
464                             FOR I IN 1..AUTO_CUSTOMER_TRX_ID.COUNT
465                             LOOP
466                                 BEGIN
467                                     IF L_ISSUE_AUTHORITY = 'Y' THEN
468                                        SELECT JL_BR_EI_NUM_CODE_S.nextval INTO L_ACCESS_CODE FROM DUAL;
469                                     ELSE
470                                        L_ACCESS_CODE := NULL;
471                                     END IF;
472                                 EXCEPTION
473                                     WHEN OTHERS THEN
474                                     RETURN 0;
475                                 END;
476 
477                             INSERT INTO JL_BR_CUSTOMER_TRX_EXTS
478                                     (ELECTRONIC_INV_ACCESS_KEY_ID
479                                     ,CUSTOMER_TRX_ID
480                                     ,LEGAL_PROCESS_CODE
481                                     ,LEGAL_PROCESS_SOURCE_IND
482                                     ,VEHICLE_PLATE_STATE_CODE
483                                     ,VEHICLE_ANTT_INSCRIPTION
484                                     ,TOWING_VEH_PLATE_NUMBER
485                                     ,TOWING_VEH_PLATE_STATE_CODE
486                                     ,TOWING_VEH_ANTT_INSCRIPTION
487                                     ,SEAL_NUMBER
488                                     ,ELECTRONIC_INV_WEB_ADDRESS
489                                     ,ELECTRONIC_INV_ACCESS_KEY
490                                     ,ELECTRONIC_INV_PROTOCOL
491                                     ,WAGON_CODE
492                                     ,FERRY_CODE
493                                     ,LAST_UPDATE_DATE
494                                     ,LAST_UPDATED_BY
495                                     ,LAST_UPDATE_LOGIN
496                                     ,CREATION_DATE
497                                     ,CREATED_BY)
498                                     VALUES(
499                                     L_ACCESS_CODE,
500                                     AUTO_CUSTOMER_TRX_ID(I),
501                                     HEADER_GDF_ATTRIBUTE19(I),
502                                     HEADER_GDF_ATTRIBUTE20(I),
503                                     HEADER_GDF_ATTRIBUTE21(I),
504                                     HEADER_GDF_ATTRIBUTE22(I),
505                                     HEADER_GDF_ATTRIBUTE23(I),
506                                     HEADER_GDF_ATTRIBUTE24(I),
507                                     HEADER_GDF_ATTRIBUTE25(I),
508                                     HEADER_GDF_ATTRIBUTE26(I),
509                                     HEADER_GDF_ATTRIBUTE27(I),
510                                     HEADER_GDF_ATTRIBUTE29(I),
511                                     HEADER_GDF_ATTRIBUTE30(I),
512                                     HEADER_GDF_ATTRIBUTE18(I),
513                                     HEADER_GDF_ATTRIBUTE28(I),
514                                     SYSDATE,
515                                     FND_GLOBAL.USER_ID,
516                                     FND_GLOBAL.LOGIN_ID,
517                                     SYSDATE,
518                                     FND_GLOBAL.USER_ID);
519                             Set_Trx_Lock_Status(AUTO_CUSTOMER_TRX_ID(I));
520                             END LOOP;
521                             END IF;
522 
523                             -- BUG 10184720
524                             -- Processing for Additional Towing Vehicle Attributes
525                             -- Start
526                             OPEN C_AUTOINV_TOW_ATTR;
527                             FETCH C_AUTOINV_TOW_ATTR BULK COLLECT INTO
528                                     AUTO_CUST_TRX_ID,
529                                     HEADER_GDF_ATTRIBUTE31,
530                                     HEADER_GDF_ATTRIBUTE32,
531                                     HEADER_GDF_ATTRIBUTE33,
532                                     HEADER_GDF_ATTRIBUTE34,
533                                     HEADER_GDF_ATTRIBUTE35,
534                                     HEADER_GDF_ATTRIBUTE36,
535                                     HEADER_GDF_ATTRIBUTE37,
536                                     HEADER_GDF_ATTRIBUTE38,
537                                     HEADER_GDF_ATTRIBUTE39; --BUG 11825724 Service Execution Date
538                             CLOSE C_AUTOINV_TOW_ATTR;
539 
540                             IF AUTO_CUST_TRX_ID.COUNT > 0 THEN
541 
542                             FOR I IN 1..AUTO_CUST_TRX_ID.COUNT
543                             LOOP
544                             UPDATE JL_BR_CUSTOMER_TRX_EXTS
545                                 SET TOWING_VEH_PLATE_NUMBER2 = HEADER_GDF_ATTRIBUTE31(I),
546                                     TOWING_VEH_PLATE_STATE_CODE2 = HEADER_GDF_ATTRIBUTE32(I),
547                                     TOWING_VEH_PLATE_NUMBER3 = HEADER_GDF_ATTRIBUTE33(I),
548                                     TOWING_VEH_PLATE_STATE_CODE3 = HEADER_GDF_ATTRIBUTE34(I),
549                                     TOWING_VEH_PLATE_NUMBER4 = HEADER_GDF_ATTRIBUTE35(I),
550                                     TOWING_VEH_PLATE_STATE_CODE4 =  HEADER_GDF_ATTRIBUTE36(I),
551                                     TOWING_VEH_PLATE_NUMBER5 = HEADER_GDF_ATTRIBUTE37(I),
552                                     TOWING_VEH_PLATE_STATE_CODE5 = HEADER_GDF_ATTRIBUTE38(I),
553                                     SRV_EXEC_DATE = to_date(HEADER_GDF_ATTRIBUTE39(I))
554                                 WHERE CUSTOMER_TRX_ID = AUTO_CUST_TRX_ID(I);
555                              END LOOP;
556                              END IF;
557                              -- End
558 
559                             -- Processing for Transaction Line Level Attributes
560                             /*Added for ER 9974789*/
561                             --START
562 	                    OPEN C_AUTOINV_LINE_ATTR;
563                             FETCH C_AUTOINV_LINE_ATTR BULK COLLECT INTO
564                                     AUTO_CUSTOMER_TRX_ID,
565                                     AUTO_CUSTOMER_TRX_LINE_ID,
566                                     LINE_GDF_ATTRIBUTE21,
567                                     LINE_GDF_ATTRIBUTE22,
568                                     LINE_GDF_ATTRIBUTE23,
569                                     LINE_GDF_ATTRIBUTE24,
570                                     LINE_GDF_ATTRIBUTE25,
571                                     LINE_GDF_ATTRIBUTE26,
572                                     LINE_GDF_ATTRIBUTE27,
573                                     LINE_GDF_ATTRIBUTE28,
574                                     LINE_GDF_ATTRIBUTE29,
575                                     LINE_GDF_ATTRIBUTE30,
576                                     LINE_GDF_ATTRIBUTE31,
577                                     LINE_GDF_ATTRIBUTE32,
578                                     LINE_GDF_ATTRIBUTE33,
579                                     LINE_GDF_ATTRIBUTE34,
580                                     LINE_GDF_ATTRIBUTE35,
581                                     LINE_GDF_ATTRIBUTE36,
582                                     LINE_GDF_ATTRIBUTE37,
583                                     LINE_GDF_ATTRIBUTE38,
584                                     LINE_GDF_ATTRIBUTE39,
585                                     LINE_GDF_ATTRIBUTE40,
586                                     LINE_GDF_ATTRIBUTE41,
587                                     LINE_GDF_ATTRIBUTE42,
588                                     LINE_GDF_ATTRIBUTE43,
589                                     LINE_GDF_ATTRIBUTE44,
590                                     LINE_GDF_ATTRIBUTE45,
591                                     LINE_GDF_ATTRIBUTE46,
592                                     LINE_GDF_ATTRIBUTE47,
593                                     LINE_GDF_ATTRIBUTE48,
594                                     LINE_GDF_ATTRIBUTE49,
595                                     LINE_GDF_ATTRIBUTE50;
596                             CLOSE C_AUTOINV_LINE_ATTR;
597 
598 		  IF AUTO_CUSTOMER_TRX_LINE_ID.COUNT > 0 THEN
599 
600                   FOR I IN 1..AUTO_CUSTOMER_TRX_LINE_ID.COUNT
601                   LOOP
602 
603 		  INSERT INTO JL_BR_CUST_TRX_LINES_EXTS
604                             (CUSTOMER_TRX_ID
605                             ,CUSTOMER_TRX_LINE_ID
606                             ,INDUSTRY_ATTRIBUTE1
607                             ,INDUSTRY_ATTRIBUTE2
608                             ,INDUSTRY_ATTRIBUTE3
609                             ,INDUSTRY_ATTRIBUTE4
610                             ,INDUSTRY_ATTRIBUTE5
611                             ,INDUSTRY_ATTRIBUTE6
612                             ,INDUSTRY_ATTRIBUTE7
613                             ,INDUSTRY_ATTRIBUTE8
614                             ,INDUSTRY_ATTRIBUTE9
615                             ,INDUSTRY_ATTRIBUTE10
616                             ,INDUSTRY_ATTRIBUTE11
617                             ,INDUSTRY_ATTRIBUTE12
618                             ,INDUSTRY_ATTRIBUTE13
619                             ,INDUSTRY_ATTRIBUTE14
620                             ,INDUSTRY_ATTRIBUTE15
621                             ,INDUSTRY_ATTRIBUTE16
622                             ,INDUSTRY_ATTRIBUTE17
623                             ,INDUSTRY_ATTRIBUTE18
624                             ,INDUSTRY_ATTRIBUTE19
625                             ,INDUSTRY_ATTRIBUTE20
626                             ,INDUSTRY_ATTRIBUTE21
627                             ,INDUSTRY_ATTRIBUTE22
628                             ,INDUSTRY_ATTRIBUTE23
629                             ,INDUSTRY_ATTRIBUTE24
630                             ,INDUSTRY_ATTRIBUTE25
631                             ,INDUSTRY_ATTRIBUTE26
632                             ,INDUSTRY_ATTRIBUTE27
633                             ,INDUSTRY_ATTRIBUTE28
634                             ,INDUSTRY_ATTRIBUTE29
635                             ,INDUSTRY_ATTRIBUTE30
636                             ,LAST_UPDATE_DATE
637                             ,LAST_UPDATED_BY
638                             ,LAST_UPDATE_LOGIN
639                             ,CREATION_DATE
640                             ,CREATED_BY)
641                             VALUES(
642                             AUTO_CUSTOMER_TRX_ID(I),
643                             AUTO_CUSTOMER_TRX_LINE_ID(I),
644                             LINE_GDF_ATTRIBUTE21(I),
645                             LINE_GDF_ATTRIBUTE22(I),
646                             LINE_GDF_ATTRIBUTE23(I),
647                             LINE_GDF_ATTRIBUTE24(I),
648                             LINE_GDF_ATTRIBUTE25(I),
649                             LINE_GDF_ATTRIBUTE26(I),
650                             LINE_GDF_ATTRIBUTE27(I),
651                             LINE_GDF_ATTRIBUTE28(I),
652                             LINE_GDF_ATTRIBUTE29(I),
653                             LINE_GDF_ATTRIBUTE30(I),
654                             LINE_GDF_ATTRIBUTE31(I),
655                             LINE_GDF_ATTRIBUTE32(I),
656                             LINE_GDF_ATTRIBUTE33(I),
657                             LINE_GDF_ATTRIBUTE34(I),
658                             LINE_GDF_ATTRIBUTE35(I),
659                             LINE_GDF_ATTRIBUTE36(I),
660                             LINE_GDF_ATTRIBUTE37(I),
661                             LINE_GDF_ATTRIBUTE38(I),
662                             LINE_GDF_ATTRIBUTE39(I),
663                             LINE_GDF_ATTRIBUTE40(I),
664                             LINE_GDF_ATTRIBUTE41(I),
665                             LINE_GDF_ATTRIBUTE42(I),
666                             LINE_GDF_ATTRIBUTE43(I),
667                             LINE_GDF_ATTRIBUTE44(I),
668                             LINE_GDF_ATTRIBUTE45(I),
669                             LINE_GDF_ATTRIBUTE46(I),
670                             LINE_GDF_ATTRIBUTE47(I),
671                             LINE_GDF_ATTRIBUTE48(I),
672                             LINE_GDF_ATTRIBUTE49(I),
673                             LINE_GDF_ATTRIBUTE50(I),
674                             SYSDATE,
675                             FND_GLOBAL.USER_ID,
676                             FND_GLOBAL.LOGIN_ID,
677                             SYSDATE,
678                             FND_GLOBAL.USER_ID);
679                             END LOOP;
680                         END IF;
681                         --END
682 
683                     -- Logic for Receivables COPY TO functionality
684                     ELSIF P_CALLED_FROM = 'ARXREC' THEN
685 
686 						        -- Processing for Transaction Header Level Attributes
687                             OPEN C_COPYINV_ATTR;
688                             FETCH C_COPYINV_ATTR BULK COLLECT INTO
689                                     COPY_CUSTOMER_TRX_ID,
690                                     LEGAL_PROCESS_CODE,
691                                     LEGAL_PROCESS_SOURCE_INDICATOR,
692                                     VEHICLE_PLATE_STATE_CODE,
693                                     VEHICLE_ANTT_INSCRIPTION,
694                                     TOWING_VEH_PLATE_NUMBER ,
695                                     TOWING_VEH_PLATE_STATE_CODE,
696                                     TOWING_VEH_ANTT_INSCRIPTION,
697                                     SEAL_NUMBER,
698                                     ELECTRONIC_INV_WEB_ADDRESS,
699                                     ELECTRONIC_INV_ACCESS_KEY,
700                                     ELECTRONIC_INV_PROTOCOL,
701                                     WAGON_CODE,
702                                     FERRY_CODE,
703                                     -- BUG 10184720 Added for additional 4 sets of Towing Vehicle Attribute
704                                     TOWING_VEH_PLATE_NUMBER2,
705                                     TOWING_VEH_PLATE_STATE_CODE2,
706                                     TOWING_VEH_PLATE_NUMBER3,
707                                     TOWING_VEH_PLATE_STATE_CODE3,
708                   	       	        TOWING_VEH_PLATE_NUMBER4,
709                                     TOWING_VEH_PLATE_STATE_CODE4,
710                                     TOWING_VEH_PLATE_NUMBER5,
711                                     TOWING_VEH_PLATE_STATE_CODE5,
712                                     SERVICE_EXEC_DATE; --BUG 11825724
713                             CLOSE C_COPYINV_ATTR;
714 
715 
716                             IF COPY_CUSTOMER_TRX_ID.COUNT > 0 THEN
717 
718                             FOR I IN 1..COPY_CUSTOMER_TRX_ID.COUNT
719                             LOOP
720                                 BEGIN
721                                     IF L_ISSUE_AUTHORITY = 'Y' THEN
722                                         SELECT JL_BR_EI_NUM_CODE_S.nextval INTO L_ACCESS_CODE FROM DUAL;
723                                     ELSE
724                                         L_ACCESS_CODE := NULL;
725                                     END IF;
726                                 EXCEPTION
727                                     WHEN OTHERS THEN
728                                     RETURN 0;
729                                 END;
730                             INSERT INTO JL_BR_CUSTOMER_TRX_EXTS
731                                     (ELECTRONIC_INV_ACCESS_KEY_ID
732                                     ,CUSTOMER_TRX_ID
733                                     ,LEGAL_PROCESS_CODE
734                                     ,LEGAL_PROCESS_SOURCE_IND
735                                     ,VEHICLE_PLATE_STATE_CODE
736                                     ,VEHICLE_ANTT_INSCRIPTION
737                                     ,TOWING_VEH_PLATE_NUMBER
738                                     ,TOWING_VEH_PLATE_STATE_CODE
739                                     --BUG 10184720 Added for additional 4 sets of Towing Vehicle Attribute
740                                     ,TOWING_VEH_PLATE_NUMBER2
741                                     ,TOWING_VEH_PLATE_STATE_CODE2
742                                     ,TOWING_VEH_PLATE_NUMBER3
743                                     ,TOWING_VEH_PLATE_STATE_CODE3
744                                     ,TOWING_VEH_PLATE_NUMBER4
745                                     ,TOWING_VEH_PLATE_STATE_CODE4
746                                     ,TOWING_VEH_PLATE_NUMBER5
747                                     ,TOWING_VEH_PLATE_STATE_CODE5
748                                     ,TOWING_VEH_ANTT_INSCRIPTION
749                                     ,SEAL_NUMBER
750                                     ,WAGON_CODE
751                                     ,FERRY_CODE
752                                     ,SRV_EXEC_DATE --BUG 11825724
753                                     --,ELECTRONIC_INV_WEB_ADDRESS
754                                     --,ELECTRONIC_INV_ACCESS_KEY
755                                     --,ELECTRONIC_INV_PROTOCOL
756                                     ,LAST_UPDATE_DATE
757                                     ,LAST_UPDATED_BY
758                                     ,LAST_UPDATE_LOGIN
759                                     ,CREATION_DATE
760                                     ,CREATED_BY)
761                                     VALUES(
762                                      L_ACCESS_CODE
763                                     ,COPY_CUSTOMER_TRX_ID(I)
764                                     ,LEGAL_PROCESS_CODE(I)
765                                     ,LEGAL_PROCESS_SOURCE_INDICATOR(I)
766                                     ,VEHICLE_PLATE_STATE_CODE(I)
767                                     ,VEHICLE_ANTT_INSCRIPTION(I)
768                                     ,TOWING_VEH_PLATE_NUMBER(I)
769                                     ,TOWING_VEH_PLATE_STATE_CODE(I)
770                                     -- BUG 10184720 Added for additional 4 sets of Towing Vehicle Attribute
771                                     ,TOWING_VEH_PLATE_NUMBER2(I)
772                                     ,TOWING_VEH_PLATE_STATE_CODE2(I)
773                                     ,TOWING_VEH_PLATE_NUMBER3(I)
774                                     ,TOWING_VEH_PLATE_STATE_CODE3(I)
775                                     ,TOWING_VEH_PLATE_NUMBER4(I)
776                                     ,TOWING_VEH_PLATE_STATE_CODE4(I)
777                                     ,TOWING_VEH_PLATE_NUMBER5(I)
778                                     ,TOWING_VEH_PLATE_STATE_CODE5(I)
779                                     ,TOWING_VEH_ANTT_INSCRIPTION(I)
780                                     ,SEAL_NUMBER(I)
781                                     ,WAGON_CODE(I)
782                                     ,FERRY_CODE(I)
783                                     ,SERVICE_EXEC_DATE(I) --BUG 11825724
784                                     --,ELECTRONIC_INV_WEB_ADDRESS(I)
785                                     --,ELECTRONIC_INV_ACCESS_KEY(I)
786                                     --,ELECTRONIC_INV_PROTOCOL(I)
787                                     ,SYSDATE
788                                     ,FND_GLOBAL.USER_ID
789                                     ,FND_GLOBAL.LOGIN_ID
790                                     ,SYSDATE
791                                     ,FND_GLOBAL.USER_ID);
792                             Set_Trx_Lock_Status(COPY_CUSTOMER_TRX_ID(I));
793                             END LOOP;
794                             END IF;
795 
796 			-- Processing for Transaction Line level Attributes
797 			/*Added for ER 9974789*/
798 			--START
799 			   OPEN C_COPYINV_LINE_ATTR;
800                            FETCH C_COPYINV_LINE_ATTR BULK COLLECT INTO
801 									 COPY_CUSTOMER_TRX_ID
802 									,COPY_CUSTOMER_TRX_LINE_ID
803 									,INDUSTRY_ATTRIBUTE1
804 									,INDUSTRY_ATTRIBUTE2
805 									,INDUSTRY_ATTRIBUTE3
806 									,INDUSTRY_ATTRIBUTE4
807 									,INDUSTRY_ATTRIBUTE5
808 									,INDUSTRY_ATTRIBUTE6
809 									,INDUSTRY_ATTRIBUTE7
810 									,INDUSTRY_ATTRIBUTE8
811 									,INDUSTRY_ATTRIBUTE9
812 									,INDUSTRY_ATTRIBUTE10
813 									,INDUSTRY_ATTRIBUTE11
814 									,INDUSTRY_ATTRIBUTE12
815 									,INDUSTRY_ATTRIBUTE13
816 									,INDUSTRY_ATTRIBUTE14
817 									,INDUSTRY_ATTRIBUTE15
818 									,INDUSTRY_ATTRIBUTE16
819 									,INDUSTRY_ATTRIBUTE17
820 									,INDUSTRY_ATTRIBUTE18
821 									,INDUSTRY_ATTRIBUTE19
822 									,INDUSTRY_ATTRIBUTE20
823 									,INDUSTRY_ATTRIBUTE21
824 									,INDUSTRY_ATTRIBUTE22
825 									,INDUSTRY_ATTRIBUTE23
826 									,INDUSTRY_ATTRIBUTE24
827 									,INDUSTRY_ATTRIBUTE25
828 									,INDUSTRY_ATTRIBUTE26
829 									,INDUSTRY_ATTRIBUTE27
830 									,INDUSTRY_ATTRIBUTE28
831 									,INDUSTRY_ATTRIBUTE29
832 									,INDUSTRY_ATTRIBUTE30;
833 			  CLOSE C_COPYINV_LINE_ATTR;
834 
835               IF COPY_CUSTOMER_TRX_LINE_ID.COUNT > 0 THEN
836 
837               FOR I IN 1..COPY_CUSTOMER_TRX_LINE_ID.COUNT
838               LOOP
839 
840               INSERT INTO JL_BR_CUST_TRX_LINES_EXTS
841                          (CUSTOMER_TRX_ID
842                          ,CUSTOMER_TRX_LINE_ID
843                          ,INDUSTRY_ATTRIBUTE1
844                           ,INDUSTRY_ATTRIBUTE2
845                           ,INDUSTRY_ATTRIBUTE3
846                           ,INDUSTRY_ATTRIBUTE4
847                           ,INDUSTRY_ATTRIBUTE5
848                           ,INDUSTRY_ATTRIBUTE6
849                           ,INDUSTRY_ATTRIBUTE7
850                           ,INDUSTRY_ATTRIBUTE8
851                           ,INDUSTRY_ATTRIBUTE9
852                           ,INDUSTRY_ATTRIBUTE10
853                           ,INDUSTRY_ATTRIBUTE11
854                           ,INDUSTRY_ATTRIBUTE12
855                           ,INDUSTRY_ATTRIBUTE13
856                           ,INDUSTRY_ATTRIBUTE14
857                           ,INDUSTRY_ATTRIBUTE15
858                           ,INDUSTRY_ATTRIBUTE16
859                           ,INDUSTRY_ATTRIBUTE17
860                           ,INDUSTRY_ATTRIBUTE18
861                           ,INDUSTRY_ATTRIBUTE19
862                           ,INDUSTRY_ATTRIBUTE20
863                           ,INDUSTRY_ATTRIBUTE21
864                           ,INDUSTRY_ATTRIBUTE22
865                           ,INDUSTRY_ATTRIBUTE23
866                           ,INDUSTRY_ATTRIBUTE24
867                           ,INDUSTRY_ATTRIBUTE25
868                           ,INDUSTRY_ATTRIBUTE26
869                           ,INDUSTRY_ATTRIBUTE27
870                           ,INDUSTRY_ATTRIBUTE28
871                           ,INDUSTRY_ATTRIBUTE29
872                           ,INDUSTRY_ATTRIBUTE30
873                           ,LAST_UPDATE_DATE
874                           ,LAST_UPDATED_BY
875                           ,LAST_UPDATE_LOGIN
876                           ,CREATION_DATE
877                           ,CREATED_BY)
878                            VALUES(
879                            COPY_CUSTOMER_TRX_ID(I)
880                           ,COPY_CUSTOMER_TRX_LINE_ID(I)
881                           ,INDUSTRY_ATTRIBUTE1(I)
882                           ,INDUSTRY_ATTRIBUTE2(I)
883                           ,INDUSTRY_ATTRIBUTE3(I)
884                           ,INDUSTRY_ATTRIBUTE4(I)
885                           ,INDUSTRY_ATTRIBUTE5(I)
886                           ,INDUSTRY_ATTRIBUTE6(I)
887                           ,INDUSTRY_ATTRIBUTE7(I)
888                           ,INDUSTRY_ATTRIBUTE8(I)
889                           ,INDUSTRY_ATTRIBUTE9(I)
890                           ,INDUSTRY_ATTRIBUTE10(I)
891                           ,INDUSTRY_ATTRIBUTE11(I)
892                           ,INDUSTRY_ATTRIBUTE12(I)
893                           ,INDUSTRY_ATTRIBUTE13(I)
894                           ,INDUSTRY_ATTRIBUTE14(I)
895                           ,INDUSTRY_ATTRIBUTE15(I)
896                           ,INDUSTRY_ATTRIBUTE16(I)
897                           ,INDUSTRY_ATTRIBUTE17(I)
898                           ,INDUSTRY_ATTRIBUTE18(I)
899                           ,INDUSTRY_ATTRIBUTE19(I)
900                           ,INDUSTRY_ATTRIBUTE20(I)
901                           ,INDUSTRY_ATTRIBUTE21(I)
902                           ,INDUSTRY_ATTRIBUTE22(I)
903                           ,INDUSTRY_ATTRIBUTE23(I)
904                           ,INDUSTRY_ATTRIBUTE24(I)
905                           ,INDUSTRY_ATTRIBUTE25(I)
906                           ,INDUSTRY_ATTRIBUTE26(I)
907                           ,INDUSTRY_ATTRIBUTE27(I)
908                           ,INDUSTRY_ATTRIBUTE28(I)
909                           ,INDUSTRY_ATTRIBUTE29(I)
910                           ,INDUSTRY_ATTRIBUTE30(I)
911                           ,SYSDATE
912                           ,FND_GLOBAL.USER_ID
913                           ,FND_GLOBAL.LOGIN_ID
914                           ,SYSDATE
915                           ,FND_GLOBAL.USER_ID);
916 			END LOOP;
917 			END IF;
918                     END IF;
919             END IF;
920             RETURN 1;
921 
922     EXCEPTION
923           WHEN OTHERS THEN
924             RETURN 0;
925     END COPY_GDF_ATTRIBUTES;
926 
927 
928     /*Function name : Copy_GDF_Attributes_API
929       Description   : This function will be called from the core AR Invoice API
930                       This function will insert the Electronic Invoice attributes IN THE JL_BR_CUSTOMER_TRX_EXTS
931                       the the extension table */
932     FUNCTION COPY_GDF_ATTRIBUTES_API (P_CUSTOMER_TRX_ID IN NUMBER) RETURN NUMBER IS
933 
934             TYPE HEADER_GDF_ATTR_TYPE  IS TABLE OF RA_INTERFACE_LINES_ALL.HEADER_GDF_ATTRIBUTE1%TYPE;
935             TYPE CUSTOMER_TRX_ID_TYPE IS TABLE OF RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE;
936             TYPE CUSTOMER_TRX_LINE_ID_TYPE IS TABLE OF RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE;
937             TYPE INDUSTRY_ATTRIBUTE_TYPE IS TABLE OF JL_BR_CUST_TRX_LINES_EXTS.INDUSTRY_ATTRIBUTE1%TYPE;
938             TYPE SERVICE_EXEC_DATE_TYPE IS TABLE OF JL_BR_INV_API_TRX_LINES_T.SRV_EXEC_DATE%TYPE; --BUG 11825724
939 
940             CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID_TYPE;
941             CUSTOMER_TRX_ID CUSTOMER_TRX_ID_TYPE;
942             CUST_TRX_ID CUSTOMER_TRX_ID_TYPE; --BUG 10184720
943             HEADER_GDF_ATTRIBUTE19 HEADER_GDF_ATTR_TYPE;
944             HEADER_GDF_ATTRIBUTE20 HEADER_GDF_ATTR_TYPE;
945             HEADER_GDF_ATTRIBUTE21 HEADER_GDF_ATTR_TYPE;
946             HEADER_GDF_ATTRIBUTE22 HEADER_GDF_ATTR_TYPE;
947             HEADER_GDF_ATTRIBUTE23 HEADER_GDF_ATTR_TYPE;
948             HEADER_GDF_ATTRIBUTE24 HEADER_GDF_ATTR_TYPE;
949             HEADER_GDF_ATTRIBUTE25 HEADER_GDF_ATTR_TYPE;
950             HEADER_GDF_ATTRIBUTE26 HEADER_GDF_ATTR_TYPE;
951             HEADER_GDF_ATTRIBUTE27 HEADER_GDF_ATTR_TYPE;
952             HEADER_GDF_ATTRIBUTE29 HEADER_GDF_ATTR_TYPE;
953             HEADER_GDF_ATTRIBUTE30 HEADER_GDF_ATTR_TYPE;
954             HEADER_GDF_ATTRIBUTE18 HEADER_GDF_ATTR_TYPE;
955             HEADER_GDF_ATTRIBUTE28 HEADER_GDF_ATTR_TYPE;
956 
957             SERVICE_EXEC_DATE SERVICE_EXEC_DATE_TYPE; --BUG 11825724
958 
959             -- BUG 10184720 Added for additional Towing Vehicle Attributes
960             TOWING_VEH_PLATE_NUMBER2 HEADER_GDF_ATTR_TYPE;
961             TOWING_VEH_PLATE_STATE_CODE2 HEADER_GDF_ATTR_TYPE;
962             TOWING_VEH_PLATE_NUMBER3 HEADER_GDF_ATTR_TYPE;
963             TOWING_VEH_PLATE_STATE_CODE3 HEADER_GDF_ATTR_TYPE;
964             TOWING_VEH_PLATE_NUMBER4 HEADER_GDF_ATTR_TYPE;
965             TOWING_VEH_PLATE_STATE_CODE4 HEADER_GDF_ATTR_TYPE;
966             TOWING_VEH_PLATE_NUMBER5 HEADER_GDF_ATTR_TYPE;
967             TOWING_VEH_PLATE_STATE_CODE5 HEADER_GDF_ATTR_TYPE;
968 
969             /*Added for ER 9974789*/
970             INDUSTRY_ATTRIBUTE1 INDUSTRY_ATTRIBUTE_TYPE;
971             INDUSTRY_ATTRIBUTE2 INDUSTRY_ATTRIBUTE_TYPE;
972             INDUSTRY_ATTRIBUTE3 INDUSTRY_ATTRIBUTE_TYPE;
973             INDUSTRY_ATTRIBUTE4 INDUSTRY_ATTRIBUTE_TYPE;
974             INDUSTRY_ATTRIBUTE5 INDUSTRY_ATTRIBUTE_TYPE;
975             INDUSTRY_ATTRIBUTE6 INDUSTRY_ATTRIBUTE_TYPE;
976             INDUSTRY_ATTRIBUTE7 INDUSTRY_ATTRIBUTE_TYPE;
977             INDUSTRY_ATTRIBUTE8 INDUSTRY_ATTRIBUTE_TYPE;
978             INDUSTRY_ATTRIBUTE9 INDUSTRY_ATTRIBUTE_TYPE;
979             INDUSTRY_ATTRIBUTE10 INDUSTRY_ATTRIBUTE_TYPE;
980             INDUSTRY_ATTRIBUTE11 INDUSTRY_ATTRIBUTE_TYPE;
981             INDUSTRY_ATTRIBUTE12 INDUSTRY_ATTRIBUTE_TYPE;
982             INDUSTRY_ATTRIBUTE13 INDUSTRY_ATTRIBUTE_TYPE;
983             INDUSTRY_ATTRIBUTE14 INDUSTRY_ATTRIBUTE_TYPE;
984             INDUSTRY_ATTRIBUTE15 INDUSTRY_ATTRIBUTE_TYPE;
985             INDUSTRY_ATTRIBUTE16 INDUSTRY_ATTRIBUTE_TYPE;
986             INDUSTRY_ATTRIBUTE17 INDUSTRY_ATTRIBUTE_TYPE;
987             INDUSTRY_ATTRIBUTE18 INDUSTRY_ATTRIBUTE_TYPE;
988             INDUSTRY_ATTRIBUTE19 INDUSTRY_ATTRIBUTE_TYPE;
989             INDUSTRY_ATTRIBUTE20 INDUSTRY_ATTRIBUTE_TYPE;
990             INDUSTRY_ATTRIBUTE21 INDUSTRY_ATTRIBUTE_TYPE;
991             INDUSTRY_ATTRIBUTE22 INDUSTRY_ATTRIBUTE_TYPE;
992             INDUSTRY_ATTRIBUTE23 INDUSTRY_ATTRIBUTE_TYPE;
993             INDUSTRY_ATTRIBUTE24 INDUSTRY_ATTRIBUTE_TYPE;
994             INDUSTRY_ATTRIBUTE25 INDUSTRY_ATTRIBUTE_TYPE;
995             INDUSTRY_ATTRIBUTE26 INDUSTRY_ATTRIBUTE_TYPE;
996             INDUSTRY_ATTRIBUTE27 INDUSTRY_ATTRIBUTE_TYPE;
997             INDUSTRY_ATTRIBUTE28 INDUSTRY_ATTRIBUTE_TYPE;
998             INDUSTRY_ATTRIBUTE29 INDUSTRY_ATTRIBUTE_TYPE;
999             INDUSTRY_ATTRIBUTE30 INDUSTRY_ATTRIBUTE_TYPE;
1000 
1001 
1002             CURSOR C_APIINV_ATTR IS SELECT
1003                     GT.CUSTOMER_TRX_ID,
1004                     GT.GLOBAL_ATTRIBUTE19,
1005                     GT.GLOBAL_ATTRIBUTE20,
1006                     GT.GLOBAL_ATTRIBUTE21,
1007                     GT.GLOBAL_ATTRIBUTE22,
1008                     GT.GLOBAL_ATTRIBUTE23,
1009                     GT.GLOBAL_ATTRIBUTE24,
1010                     GT.GLOBAL_ATTRIBUTE25,
1011                     GT.GLOBAL_ATTRIBUTE26,
1012                     GT.GLOBAL_ATTRIBUTE27,
1013                     GT.GLOBAL_ATTRIBUTE29,
1014                     GT.GLOBAL_ATTRIBUTE30,
1015                     GT.GLOBAL_ATTRIBUTE18,
1016                     GT.GLOBAL_ATTRIBUTE28
1017                     FROM AR_TRX_HEADER_GT GT
1018                       WHERE GT.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
1019 
1020             -- BUG 10184720
1021             -- Cursor Added for capturing additional Towing Attributes
1022             CURSOR C_APIINV_TOW_ATTR IS SELECT
1023                     GT.CUSTOMER_TRX_ID,
1024                     T.TOWING_VEH_PLATE_NUMBER2,
1025                     T.TOWING_VEH_PLATE_STATE_CODE2,
1026                     T.TOWING_VEH_PLATE_NUMBER3,
1027                     T.TOWING_VEH_PLATE_STATE_CODE3,
1028                     T.TOWING_VEH_PLATE_NUMBER4,
1029                     T.TOWING_VEH_PLATE_STATE_CODE4,
1030                     T.TOWING_VEH_PLATE_NUMBER5,
1031                     T.TOWING_VEH_PLATE_STATE_CODE5,
1032                     T.SRV_EXEC_DATE --BUG 11825724
1033                     FROM AR_TRX_HEADER_GT GT,
1034                          JL_BR_INV_API_TRX_LINES_T T
1035                     WHERE GT.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
1036                       AND GT.TRX_HEADER_ID = T.TRX_HEADER_ID
1037                       AND T.TRX_LINE_ID IS NULL; --bug 10265440
1038 
1039             CURSOR C_APIINV_LINE_ATTR IS SELECT
1040                     ARGT.CUSTOMER_TRX_ID,
1041                     ARGT.CUSTOMER_TRX_LINE_ID,
1042                     T.INDUSTRY_ATTRIBUTE1,
1043                     T.INDUSTRY_ATTRIBUTE2,
1044                     T.INDUSTRY_ATTRIBUTE3,
1045           	    T.INDUSTRY_ATTRIBUTE4,
1046                     T.INDUSTRY_ATTRIBUTE5,
1047                     T.INDUSTRY_ATTRIBUTE6,
1048                     T.INDUSTRY_ATTRIBUTE7,
1049                     T.INDUSTRY_ATTRIBUTE8,
1050                     T.INDUSTRY_ATTRIBUTE9,
1051                     T.INDUSTRY_ATTRIBUTE10,
1052                     T.INDUSTRY_ATTRIBUTE11,
1053                     T.INDUSTRY_ATTRIBUTE12,
1054                     T.INDUSTRY_ATTRIBUTE13,
1055                     T.INDUSTRY_ATTRIBUTE14,
1056                     T.INDUSTRY_ATTRIBUTE15,
1057                     T.INDUSTRY_ATTRIBUTE16,
1058                     T.INDUSTRY_ATTRIBUTE17,
1059                     T.INDUSTRY_ATTRIBUTE18,
1060                     T.INDUSTRY_ATTRIBUTE19,
1061                     T.INDUSTRY_ATTRIBUTE20,
1062                     T.INDUSTRY_ATTRIBUTE21,
1063                     T.INDUSTRY_ATTRIBUTE22,
1064                     T.INDUSTRY_ATTRIBUTE23,
1065                     T.INDUSTRY_ATTRIBUTE24,
1066                     T.INDUSTRY_ATTRIBUTE25,
1067                     T.INDUSTRY_ATTRIBUTE26,
1068                     T.INDUSTRY_ATTRIBUTE27,
1069                     T.INDUSTRY_ATTRIBUTE28,
1070                     T.INDUSTRY_ATTRIBUTE29,
1071                     T.INDUSTRY_ATTRIBUTE30
1072                     FROM JL_BR_INV_API_TRX_LINES_T T,
1073 	                       AR_TRX_LINES_GT ARGT
1074                     WHERE T.TRX_LINE_ID = ARGT.TRX_LINE_ID
1075                       AND T.TRX_HEADER_ID = ARGT.TRX_HEADER_ID
1076                       AND ARGT.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
1077 
1078 
1079             L_COUNTRY_CODE      VARCHAR2 (30);
1080             L_ACCESS_CODE       NUMBER;
1081             L_ISSUE_AUTHORITY   VARCHAR2(1);
1082     BEGIN
1083 
1084             FND_PROFILE.GET ('JGZZ_COUNTRY_CODE', L_COUNTRY_CODE);
1085             IF L_COUNTRY_CODE = 'BR' THEN
1086 
1087            -- Processing for Transaction Header Attributes
1088             OPEN C_APIINV_ATTR;
1089             FETCH C_APIINV_ATTR BULK COLLECT INTO
1090                     CUSTOMER_TRX_ID,
1091                     HEADER_GDF_ATTRIBUTE19,
1092                     HEADER_GDF_ATTRIBUTE20,
1093                     HEADER_GDF_ATTRIBUTE21,
1094                     HEADER_GDF_ATTRIBUTE22,
1095                     HEADER_GDF_ATTRIBUTE23,
1096                     HEADER_GDF_ATTRIBUTE24,
1097                     HEADER_GDF_ATTRIBUTE25,
1098                     HEADER_GDF_ATTRIBUTE26,
1099                     HEADER_GDF_ATTRIBUTE27,
1100                     HEADER_GDF_ATTRIBUTE29,
1101                     HEADER_GDF_ATTRIBUTE30,
1102                     HEADER_GDF_ATTRIBUTE18,
1103                     HEADER_GDF_ATTRIBUTE28;
1104             CLOSE C_APIINV_ATTR;
1105 
1106             IF CUSTOMER_TRX_ID.COUNT > 0 THEN
1107 
1108             FOR I IN 1..CUSTOMER_TRX_ID.COUNT
1109             LOOP
1110                     BEGIN
1111                         SELECT NVL(B.GLOBAL_ATTRIBUTE5, 'N') INTO L_ISSUE_AUTHORITY
1112                           FROM RA_BATCH_SOURCES B, RA_CUSTOMER_TRX T
1113                          WHERE T.CUSTOMER_TRX_ID = CUSTOMER_TRX_ID(I)
1114                            AND B.BATCH_SOURCE_ID = T.BATCH_SOURCE_ID;
1115 
1116                         IF L_ISSUE_AUTHORITY = 'Y' THEN
1117                           SELECT JL_BR_EI_NUM_CODE_S.nextval INTO L_ACCESS_CODE FROM DUAL;
1118                         ELSE
1119                           L_ACCESS_CODE := NULL;
1120                         END IF;
1121                     EXCEPTION
1122                       WHEN OTHERS THEN
1123                       RETURN 0;
1124                     END;
1125 
1126                     INSERT INTO JL_BR_CUSTOMER_TRX_EXTS
1127                     (ELECTRONIC_INV_ACCESS_KEY_ID
1128                     ,CUSTOMER_TRX_ID
1129                     ,LEGAL_PROCESS_CODE
1130                     ,LEGAL_PROCESS_SOURCE_IND
1131                     ,VEHICLE_PLATE_STATE_CODE
1132                     ,VEHICLE_ANTT_INSCRIPTION
1133                     ,TOWING_VEH_PLATE_NUMBER
1134                     ,TOWING_VEH_PLATE_STATE_CODE
1135                     ,TOWING_VEH_ANTT_INSCRIPTION
1136                     ,SEAL_NUMBER
1137                     ,ELECTRONIC_INV_WEB_ADDRESS
1138                     ,ELECTRONIC_INV_ACCESS_KEY
1139                     ,ELECTRONIC_INV_PROTOCOL
1140                     ,WAGON_CODE
1141                     ,FERRY_CODE
1142                     ,LAST_UPDATE_DATE
1143                     ,LAST_UPDATED_BY
1144                     ,LAST_UPDATE_LOGIN
1145                     ,CREATION_DATE
1146                     ,CREATED_BY)
1147                     VALUES(
1148                     L_ACCESS_CODE,
1149                     CUSTOMER_TRX_ID(I),
1150                     HEADER_GDF_ATTRIBUTE19(I),
1151                     HEADER_GDF_ATTRIBUTE20(I),
1152                     HEADER_GDF_ATTRIBUTE21(I),
1153                     HEADER_GDF_ATTRIBUTE22(I),
1154                     HEADER_GDF_ATTRIBUTE23(I),
1155                     HEADER_GDF_ATTRIBUTE24(I),
1156                     HEADER_GDF_ATTRIBUTE25(I),
1157                     HEADER_GDF_ATTRIBUTE26(I),
1158                     HEADER_GDF_ATTRIBUTE27(I),
1159                     HEADER_GDF_ATTRIBUTE29(I),
1160                     HEADER_GDF_ATTRIBUTE30(I),
1161                     HEADER_GDF_ATTRIBUTE18(I),
1162                     HEADER_GDF_ATTRIBUTE28(I),
1163                     SYSDATE,
1164                     FND_GLOBAL.USER_ID,
1165                     FND_GLOBAL.LOGIN_ID,
1166                     SYSDATE,
1167                     FND_GLOBAL.USER_ID);
1168             Set_Trx_Lock_Status(CUSTOMER_TRX_ID(I));
1169             END LOOP;
1170             END IF;
1171 
1172             -- BUG 10184720
1173             -- Processing for Additional Towing Vehicle Attributes
1174             -- Start
1175             OPEN C_APIINV_TOW_ATTR;
1176             FETCH C_APIINV_TOW_ATTR BULK COLLECT INTO
1177                     CUST_TRX_ID,
1178                     TOWING_VEH_PLATE_NUMBER2,
1179                     TOWING_VEH_PLATE_STATE_CODE2,
1180                     TOWING_VEH_PLATE_NUMBER3,
1181                     TOWING_VEH_PLATE_STATE_CODE3,
1182                     TOWING_VEH_PLATE_NUMBER4,
1183                     TOWING_VEH_PLATE_STATE_CODE4,
1184                     TOWING_VEH_PLATE_NUMBER5,
1185                     TOWING_VEH_PLATE_STATE_CODE5,
1186                     SERVICE_EXEC_DATE; --BUG 11825724
1187              CLOSE C_APIINV_TOW_ATTR;
1188 
1189 
1190              IF CUST_TRX_ID.COUNT > 0 THEN
1191 
1192              FOR I IN 1..CUST_TRX_ID.COUNT
1193              LOOP
1194              UPDATE JL_BR_CUSTOMER_TRX_EXTS
1195                   SET TOWING_VEH_PLATE_NUMBER2 = TOWING_VEH_PLATE_NUMBER2(I),
1196                       TOWING_VEH_PLATE_STATE_CODE2 = TOWING_VEH_PLATE_STATE_CODE2(I),
1197                       TOWING_VEH_PLATE_NUMBER3 = TOWING_VEH_PLATE_NUMBER3(I),
1198                       TOWING_VEH_PLATE_STATE_CODE3 = TOWING_VEH_PLATE_STATE_CODE3(I),
1199                       TOWING_VEH_PLATE_NUMBER4 = TOWING_VEH_PLATE_NUMBER4(I),
1200                       TOWING_VEH_PLATE_STATE_CODE4 =  TOWING_VEH_PLATE_STATE_CODE4(I),
1201                       TOWING_VEH_PLATE_NUMBER5 = TOWING_VEH_PLATE_NUMBER5(I),
1202                       TOWING_VEH_PLATE_STATE_CODE5 = TOWING_VEH_PLATE_STATE_CODE5(I),
1203                       SRV_EXEC_DATE = SERVICE_EXEC_DATE(I) --BUG 11825724
1204                   WHERE CUSTOMER_TRX_ID = CUST_TRX_ID(I);
1205 
1206              END LOOP;
1207              END IF;
1208              -- End
1209                     -- Processing for Transaction Line Attributes
1210                     OPEN C_APIINV_LINE_ATTR;
1211                     FETCH C_APIINV_LINE_ATTR BULK COLLECT INTO
1212                         CUSTOMER_TRX_ID,
1213                         CUSTOMER_TRX_LINE_ID,
1214                         INDUSTRY_ATTRIBUTE1,
1215                         INDUSTRY_ATTRIBUTE2,
1216                         INDUSTRY_ATTRIBUTE3,
1217                         INDUSTRY_ATTRIBUTE4,
1218                         INDUSTRY_ATTRIBUTE5,
1219                         INDUSTRY_ATTRIBUTE6,
1220                         INDUSTRY_ATTRIBUTE7,
1221                         INDUSTRY_ATTRIBUTE8,
1222                         INDUSTRY_ATTRIBUTE9,
1223                         INDUSTRY_ATTRIBUTE10,
1224                         INDUSTRY_ATTRIBUTE11,
1225                         INDUSTRY_ATTRIBUTE12,
1226                         INDUSTRY_ATTRIBUTE13,
1227                         INDUSTRY_ATTRIBUTE14,
1228                         INDUSTRY_ATTRIBUTE15,
1229                         INDUSTRY_ATTRIBUTE16,
1230                         INDUSTRY_ATTRIBUTE17,
1231                         INDUSTRY_ATTRIBUTE18,
1232                         INDUSTRY_ATTRIBUTE19,
1233                         INDUSTRY_ATTRIBUTE20,
1234                         INDUSTRY_ATTRIBUTE21,
1235                         INDUSTRY_ATTRIBUTE22,
1236                         INDUSTRY_ATTRIBUTE23,
1237                         INDUSTRY_ATTRIBUTE24,
1238                         INDUSTRY_ATTRIBUTE25,
1239                         INDUSTRY_ATTRIBUTE26,
1240                         INDUSTRY_ATTRIBUTE27,
1241                         INDUSTRY_ATTRIBUTE28,
1242                         INDUSTRY_ATTRIBUTE29,
1243                         INDUSTRY_ATTRIBUTE30;
1244                 	CLOSE C_APIINV_LINE_ATTR;
1245 
1246             IF CUSTOMER_TRX_LINE_ID.COUNT > 0 THEN
1247 
1248             FOR I IN 1..CUSTOMER_TRX_LINE_ID.COUNT
1249                 LOOP
1250 
1251                 INSERT INTO JL_BR_CUST_TRX_LINES_EXTS
1252                         (CUSTOMER_TRX_ID
1253                         ,CUSTOMER_TRX_LINE_ID
1254                         ,INDUSTRY_ATTRIBUTE1
1255                         ,INDUSTRY_ATTRIBUTE2
1256                         ,INDUSTRY_ATTRIBUTE3
1257                         ,INDUSTRY_ATTRIBUTE4
1258                         ,INDUSTRY_ATTRIBUTE5
1259                         ,INDUSTRY_ATTRIBUTE6
1260                         ,INDUSTRY_ATTRIBUTE7
1261                         ,INDUSTRY_ATTRIBUTE8
1262                         ,INDUSTRY_ATTRIBUTE9
1263                         ,INDUSTRY_ATTRIBUTE10
1264                         ,INDUSTRY_ATTRIBUTE11
1265                         ,INDUSTRY_ATTRIBUTE12
1266                         ,INDUSTRY_ATTRIBUTE13
1267                         ,INDUSTRY_ATTRIBUTE14
1268                         ,INDUSTRY_ATTRIBUTE15
1269                         ,INDUSTRY_ATTRIBUTE16
1270                         ,INDUSTRY_ATTRIBUTE17
1271                         ,INDUSTRY_ATTRIBUTE18
1272                         ,INDUSTRY_ATTRIBUTE19
1273                         ,INDUSTRY_ATTRIBUTE20
1274                         ,INDUSTRY_ATTRIBUTE21
1275                         ,INDUSTRY_ATTRIBUTE22
1276                         ,INDUSTRY_ATTRIBUTE23
1277                         ,INDUSTRY_ATTRIBUTE24
1278                         ,INDUSTRY_ATTRIBUTE25
1279                         ,INDUSTRY_ATTRIBUTE26
1280                         ,INDUSTRY_ATTRIBUTE27
1281                         ,INDUSTRY_ATTRIBUTE28
1282                         ,INDUSTRY_ATTRIBUTE29
1283                         ,INDUSTRY_ATTRIBUTE30
1284                         ,LAST_UPDATE_DATE
1285                         ,LAST_UPDATED_BY
1286                         ,LAST_UPDATE_LOGIN
1287                         ,CREATION_DATE
1288                         ,CREATED_BY
1289                         )
1290                         VALUES(
1291                         CUSTOMER_TRX_ID(I),
1292                         CUSTOMER_TRX_LINE_ID(I),
1293                         INDUSTRY_ATTRIBUTE1(I),
1294                         INDUSTRY_ATTRIBUTE2(I),
1295                         INDUSTRY_ATTRIBUTE3(I),
1296                         INDUSTRY_ATTRIBUTE4(I),
1297                         INDUSTRY_ATTRIBUTE5(I),
1298                         INDUSTRY_ATTRIBUTE6(I),
1299                         INDUSTRY_ATTRIBUTE7(I),
1300                         INDUSTRY_ATTRIBUTE8(I),
1301                         INDUSTRY_ATTRIBUTE9(I),
1302                         INDUSTRY_ATTRIBUTE10(I),
1303                         INDUSTRY_ATTRIBUTE11(I),
1304                         INDUSTRY_ATTRIBUTE12(I),
1305                         INDUSTRY_ATTRIBUTE13(I),
1306                         INDUSTRY_ATTRIBUTE14(I),
1307                         INDUSTRY_ATTRIBUTE15(I),
1308                         INDUSTRY_ATTRIBUTE16(I),
1309                         INDUSTRY_ATTRIBUTE17(I),
1310                         INDUSTRY_ATTRIBUTE18(I),
1311                         INDUSTRY_ATTRIBUTE19(I),
1312                         INDUSTRY_ATTRIBUTE20(I),
1313                         INDUSTRY_ATTRIBUTE21(I),
1314                         INDUSTRY_ATTRIBUTE22(I),
1315                         INDUSTRY_ATTRIBUTE23(I),
1316                         INDUSTRY_ATTRIBUTE24(I),
1317                         INDUSTRY_ATTRIBUTE25(I),
1318                         INDUSTRY_ATTRIBUTE26(I),
1319                         INDUSTRY_ATTRIBUTE27(I),
1320                         INDUSTRY_ATTRIBUTE28(I),
1321                         INDUSTRY_ATTRIBUTE29(I),
1322                         INDUSTRY_ATTRIBUTE30(I),
1323                         SYSDATE,
1324                         FND_GLOBAL.USER_ID,
1325                         FND_GLOBAL.LOGIN_ID,
1326                         SYSDATE,
1327                         FND_GLOBAL.USER_ID);
1328 
1329 	    END LOOP;
1330 
1331           END IF;
1332             -- Deleting Data from Temporary table once it has been copied to core JL table
1333             -- BUG 10184720
1334             DELETE FROM JL_BR_INV_API_TRX_LINES_T
1335                   WHERE TRX_HEADER_ID IN (SELECT TRX_HEADER_ID FROM AR_TRX_HEADER_GT
1336 	                                          WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID);
1337          END IF; -- main IF country code = 'BR'
1338 
1339          RETURN 1;
1340 
1341     EXCEPTION
1342             WHEN OTHERS THEN
1343             RETURN 0;
1344     END COPY_GDF_ATTRIBUTES_API;
1345 
1346     FUNCTION Create_Void_CM (p_inv_customer_trx_id IN NUMBER,p_trx_type_id IN NUMBER,p_CM_amount IN NUMBER) RETURN NUMBER IS
1347 
1348     l_trx_status NUMBER(15);
1349     l_trx_amount NUMBER;
1350     l_CM_type    VARCHAR2(20);
1351     l_CM_status  VARCHAR2(20);
1352     CURSOR C_trx_details( p_trx_id IN NUMBER ) IS
1353            SELECT selected_for_receipt_batch_id, sum(amount_due_remaining) FROM ar_payment_schedules WHERE customer_trx_id = p_trx_id
1354                                                  GROUP BY customer_trx_id,selected_for_receipt_batch_id;
1355     CURSOR C_CM_Details( p_type_id IN NUMBER ) IS
1356            SELECT TYPE, DEFAULT_STATUS FROM ra_cust_trx_types WHERE cust_trx_type_id = p_type_id;
1357     L_COUNTRY_CODE varchar2(30);
1358     BEGIN
1359     FND_PROFILE.GET ('JGZZ_COUNTRY_CODE', L_COUNTRY_CODE);
1360     IF L_COUNTRY_CODE = 'BR' THEN
1361 
1362             OPEN C_trx_details(p_inv_customer_trx_id);
1363             FETCH C_trx_details INTO l_trx_status,l_trx_amount;
1364             CLOSE C_trx_details;
1365 
1366             OPEN C_CM_Details(p_trx_type_id);
1367             FETCH C_CM_Details INTO l_CM_type, l_CM_status;
1368             CLOSE C_CM_Details;
1369 
1370         IF l_trx_status = -999 THEN
1371               IF l_CM_type = 'CM' AND l_CM_status = 'VD' AND (l_trx_amount + p_CM_amount) = 0 THEN
1372                   UPDATE ar_payment_schedules
1373                       SET selected_for_receipt_batch_id = NULL
1374                       WHERE selected_for_receipt_batch_id = -999 AND
1375                       customer_trx_id = p_inv_customer_trx_id;
1376                       RETURN 1;
1377               ELSE
1378                       RETURN 0;
1379               END IF;
1380         ELSE
1381               RETURN 1;
1382         END IF;
1383 
1384     ELSE
1385        RETURN 1;
1386     END IF;
1387 
1388     EXCEPTION
1389          WHEN OTHERS THEN
1390              RETURN 0;
1391 
1392     END Create_Void_CM;
1393 
1394 /*This procedure purges data from JL AutoInvoice Interface table whenever data from
1395   core AR Interface table is purged*/
1396 PROCEDURE DELETE_JL_INT_EXTN_TAB (p_request_id IN NUMBER) IS
1397 
1398 TYPE rowid_tab_T IS TABLE OF ROWID;
1399 rowid_tab rowid_tab_T;
1400 
1401 BEGIN
1402 
1403 IF p_request_id IS NOT NULL THEN
1404    SELECT rowid BULK COLLECT INTO rowid_tab FROM RA_INTERFACE_LINES
1405         WHERE REQUEST_ID = p_request_id
1406           AND INTERFACE_STATUS = 'P';
1407 
1408    FOR I IN 1..rowid_tab.COUNT
1409      LOOP
1410      DELETE FROM JL_BR_INTERFACE_LINES_EXTS
1411            WHERE JL_BR_INTERFACE_LINK_ID = rowid_tab(I);
1412      END LOOP;
1413 
1414 ELSE
1415    DELETE FROM JL_BR_INTERFACE_LINES_EXTS
1416      WHERE JL_BR_INTERFACE_LINK_ID IN (SELECT rowid FROM RA_INTERFACE_LINES
1417                                            WHERE INTERFACE_STATUS = 'P');
1418 END IF;
1419 
1420 EXCEPTION
1421     WHEN OTHERS THEN
1422     null;
1423 END DELETE_JL_INT_EXTN_TAB;
1424 
1425 END JL_BR_SPED_PKG;