[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;