[Home] [Help]
PACKAGE BODY: APPS.JL_BR_SPED_PKG
Source
1 PACKAGE BODY JL_BR_SPED_PKG AS
2 /* $Header: jlspedab.pls 120.0.12010000.4 2008/11/28 11:26:05 pakumare noship $ */
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
19 -- Declaration part
20
21 CURSOR C_BATCH_SOURCE IS
22 SELECT NVL(B.GLOBAL_ATTRIBUTE5, 'N') GLOBAL_ATTRIBUTE5
23 FROM RA_BATCH_SOURCES B, RA_CUSTOMER_TRX T
24 WHERE T.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
25 AND B.BATCH_SOURCE_ID = T.BATCH_SOURCE_ID;
26
27 CURSOR C_CUST_EXT IS
28 SELECT COUNT(*) as CTR
29 FROM JL_BR_CUSTOMER_TRX_EXTS
30 WHERE ELECTRONIC_INV_STATUS IN ('2','7')
31 AND CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
32 AND NOT EXISTS ( SELECT 'X' FROM AR_PAYMENT_SCHEDULES
33 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
34 AND SELECTED_FOR_RECEIPT_BATCH_ID = -999);
35
36 l_proceed_flag VARCHAR2(1) := 'F';
37 l_country_code VARCHAR2(30);
38
39 BEGIN
40
41 FND_PROFILE.GET('JGZZ_COUNTRY_CODE', l_country_code);
42
43 IF (l_country_code = 'BR') THEN
44
45 FOR batch_source_rec IN C_BATCH_SOURCE LOOP
46
47 IF batch_source_rec.GLOBAL_ATTRIBUTE5 = 'Y' THEN
48
49 FOR cust_ext_rec IN C_CUST_EXT LOOP
50 IF cust_ext_rec.CTR = 0 THEN
51 l_proceed_flag := 'F';
52 ELSE
53 l_proceed_flag := 'T';
54 END IF;
55 END LOOP;
56 ELSE
57 l_proceed_flag := 'T';
58 END IF;
59 END LOOP;
60
61 ELSE
62 l_proceed_flag := 'T';
63 END IF;
64
65 RETURN l_proceed_flag;
66
67 EXCEPTION
68 WHEN OTHERS THEN
69 l_proceed_flag := 'F';
70 RETURN l_proceed_flag;
71 END IS_INVOICE_FINAL;
72
73
74 PROCEDURE Set_Trx_Lock_Status(p_customer_trx_id IN NUMBER) IS
75
76 CURSOR C_BATCH_SOURCE IS
77 SELECT NVL(B.GLOBAL_ATTRIBUTE5, 'N') GLOBAL_ATTRIBUTE5
78 FROM RA_BATCH_SOURCES B, RA_CUSTOMER_TRX T
79 WHERE T.CUSTOMER_TRX_ID = p_customer_trx_id
80 AND B.BATCH_SOURCE_ID = T.BATCH_SOURCE_ID;
81 l_country_code varchar2(30);
82 BEGIN
83 FND_PROFILE.GET('JGZZ_COUNTRY_CODE', l_country_code);
84
85 IF (l_country_code = 'BR') THEN
86
87 FOR BATCH_SOURCE_REC IN C_BATCH_SOURCE LOOP
88 IF BATCH_SOURCE_REC.GLOBAL_ATTRIBUTE5 = 'Y' THEN
89 UPDATE AR_PAYMENT_SCHEDULES_ALL
90 SET SELECTED_FOR_RECEIPT_BATCH_ID = -999
91 WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
92 END IF;
93 END LOOP;
94 END IF;
95 END Set_Trx_Lock_Status;
96
97 /* Function Name : Copy_GDF_Attributes
98 Description : This function will be called from the core AR Autoinvoice program and the Copy Transactions program
99 for inserting the Electronic Invoice attributes in the JL_BR_CUSTOMER_TRX_EXTS extension table*/
100 FUNCTION COPY_GDF_ATTRIBUTES (P_REQUEST_ID IN NUMBER, P_CALLED_FROM IN VARCHAR2) RETURN NUMBER IS
101
102 TYPE HEADER_GDF_ATTR_TYPE IS TABLE OF RA_INTERFACE_LINES_ALL.HEADER_GDF_ATTRIBUTE1%TYPE;
103
104 TYPE CUSTOMER_TRX_ID_TYPE IS TABLE OF RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE;
105
106 TYPE LEGAL_PROCESS_CODE_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.LEGAL_PROCESS_CODE%TYPE;
107 TYPE LEGAL_PROCESS_SOURCE_IND_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.LEGAL_PROCESS_SOURCE_IND%TYPE;
108 TYPE VEHICLE_PLATE_STATE_CODE_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.VEHICLE_PLATE_STATE_CODE%TYPE;
109 TYPE VEHICLE_ANTT_INSCRIPTION_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.VEHICLE_ANTT_INSCRIPTION%TYPE;
110 TYPE TOWING_VEH_PLATE_NUMBER_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_PLATE_NUMBER%TYPE;
111 TYPE TOWING_VEH_PLATE_STATE_CODE_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_PLATE_STATE_CODE%TYPE;
112 TYPE TOWING_VEH_ANTT_INSCRIPTION_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_ANTT_INSCRIPTION%TYPE;
113 TYPE SEAL_NUMBER_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.SEAL_NUMBER%TYPE;
114 TYPE ELECTRONIC_INV_WEB_ADDRESS_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.ELECTRONIC_INV_WEB_ADDRESS%TYPE;
115 TYPE ELECTRONIC_INV_ACCESS_KEY_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.ELECTRONIC_INV_ACCESS_KEY%TYPE;
116 TYPE ELECTRONIC_INV_PROTOCOL_T IS TABLE OF JL_BR_CUSTOMER_TRX_EXTS.ELECTRONIC_INV_PROTOCOL%TYPE;
117
118 AUTO_CUSTOMER_TRX_ID CUSTOMER_TRX_ID_TYPE;
119 COPY_CUSTOMER_TRX_ID CUSTOMER_TRX_ID_TYPE;
120
121 HEADER_GDF_ATTRIBUTE19 HEADER_GDF_ATTR_TYPE;
122 HEADER_GDF_ATTRIBUTE20 HEADER_GDF_ATTR_TYPE;
123 HEADER_GDF_ATTRIBUTE21 HEADER_GDF_ATTR_TYPE;
124 HEADER_GDF_ATTRIBUTE22 HEADER_GDF_ATTR_TYPE;
125 HEADER_GDF_ATTRIBUTE23 HEADER_GDF_ATTR_TYPE;
126 HEADER_GDF_ATTRIBUTE24 HEADER_GDF_ATTR_TYPE;
127 HEADER_GDF_ATTRIBUTE25 HEADER_GDF_ATTR_TYPE;
128 HEADER_GDF_ATTRIBUTE26 HEADER_GDF_ATTR_TYPE;
129 HEADER_GDF_ATTRIBUTE27 HEADER_GDF_ATTR_TYPE;
130 HEADER_GDF_ATTRIBUTE29 HEADER_GDF_ATTR_TYPE;
131 HEADER_GDF_ATTRIBUTE30 HEADER_GDF_ATTR_TYPE;
132
133 LEGAL_PROCESS_CODE LEGAL_PROCESS_CODE_T;
134 LEGAL_PROCESS_SOURCE_INDICATOR LEGAL_PROCESS_SOURCE_IND_T;
135 VEHICLE_PLATE_STATE_CODE VEHICLE_PLATE_STATE_CODE_T;
136 VEHICLE_ANTT_INSCRIPTION VEHICLE_ANTT_INSCRIPTION_T;
137 TOWING_VEH_PLATE_NUMBER TOWING_VEH_PLATE_NUMBER_T;
138 TOWING_VEH_PLATE_STATE_CODE TOWING_VEH_PLATE_STATE_CODE_T;
139 TOWING_VEH_ANTT_INSCRIPTION TOWING_VEH_ANTT_INSCRIPTION_T;
140 SEAL_NUMBER SEAL_NUMBER_T;
141 ELECTRONIC_INV_WEB_ADDRESS ELECTRONIC_INV_WEB_ADDRESS_T;
142 ELECTRONIC_INV_ACCESS_KEY ELECTRONIC_INV_ACCESS_KEY_T;
143 ELECTRONIC_INV_PROTOCOL ELECTRONIC_INV_PROTOCOL_T;
144
145 CURSOR C_AUTOINV_ATTR IS SELECT
146 GT.CUSTOMER_TRX_ID,
147 GT.HEADER_GDF_ATTRIBUTE19,
148 GT.HEADER_GDF_ATTRIBUTE20,
149 GT.HEADER_GDF_ATTRIBUTE21,
150 GT.HEADER_GDF_ATTRIBUTE22,
151 GT.HEADER_GDF_ATTRIBUTE23,
152 GT.HEADER_GDF_ATTRIBUTE24,
153 GT.HEADER_GDF_ATTRIBUTE25,
154 GT.HEADER_GDF_ATTRIBUTE26,
155 GT.HEADER_GDF_ATTRIBUTE27,
156 GT.HEADER_GDF_ATTRIBUTE29,
157 GT.HEADER_GDF_ATTRIBUTE30
158 FROM RA_INTERFACE_LINES_GT GT,
159 RA_CUSTOMER_TRX TRX
160 WHERE TRX.REQUEST_ID = P_REQUEST_ID
161 AND GT.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
162 AND GT.INTERFACE_LINE_ID = (SELECT MIN(GT.INTERFACE_LINE_ID)
163 FROM RA_INTERFACE_LINES_GT GT_2
164 WHERE GT_2.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID) ;
165
166
167 CURSOR C_COPYINV_ATTR IS SELECT
168 NEW_TRX.CUSTOMER_TRX_ID
169 ,OLD_TRX.LEGAL_PROCESS_CODE
170 ,OLD_TRX.LEGAL_PROCESS_SOURCE_IND
171 ,OLD_TRX.VEHICLE_PLATE_STATE_CODE
172 ,OLD_TRX.VEHICLE_ANTT_INSCRIPTION
173 ,OLD_TRX.TOWING_VEH_PLATE_NUMBER
174 ,OLD_TRX.TOWING_VEH_PLATE_STATE_CODE
175 ,OLD_TRX.TOWING_VEH_ANTT_INSCRIPTION
176 ,OLD_TRX.SEAL_NUMBER
177 ,OLD_TRX.ELECTRONIC_INV_WEB_ADDRESS
178 ,OLD_TRX.ELECTRONIC_INV_ACCESS_KEY
179 ,OLD_TRX.ELECTRONIC_INV_PROTOCOL
180 FROM JL_BR_CUSTOMER_TRX_EXTS OLD_TRX,
181 (SELECT TRX_NUMBER, CUSTOMER_TRX_ID,RECURRED_FROM_TRX_NUMBER,BATCH_SOURCE_ID
182 FROM RA_CUSTOMER_TRX
183 WHERE REQUEST_ID = P_REQUEST_ID) NEW_TRX
184 WHERE OLD_TRX.CUSTOMER_TRX_ID = (SELECT CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX
185 WHERE TRX_NUMBER = NEW_TRX.RECURRED_FROM_TRX_NUMBER
186 AND BATCH_SOURCE_ID = NEW_TRX.BATCH_SOURCE_ID);
187
188 L_COUNTRY_CODE VARCHAR2(30);
189
190 BEGIN
191 FND_PROFILE.GET('JGZZ_COUNTRY_CODE', L_COUNTRY_CODE);
192
193 IF L_COUNTRY_CODE = 'BR' THEN
194 IF P_CALLED_FROM = 'RAXTRX' THEN
195
196 OPEN C_AUTOINV_ATTR;
197 FETCH C_AUTOINV_ATTR BULK COLLECT INTO
198 AUTO_CUSTOMER_TRX_ID,
199 HEADER_GDF_ATTRIBUTE19,
200 HEADER_GDF_ATTRIBUTE20,
201 HEADER_GDF_ATTRIBUTE21,
202 HEADER_GDF_ATTRIBUTE22,
203 HEADER_GDF_ATTRIBUTE23,
204 HEADER_GDF_ATTRIBUTE24,
205 HEADER_GDF_ATTRIBUTE25,
206 HEADER_GDF_ATTRIBUTE26,
207 HEADER_GDF_ATTRIBUTE27,
208 HEADER_GDF_ATTRIBUTE29,
209 HEADER_GDF_ATTRIBUTE30;
210 CLOSE C_AUTOINV_ATTR;
211
212 IF AUTO_CUSTOMER_TRX_ID.COUNT > 0 THEN
213
214 FOR I IN 1..AUTO_CUSTOMER_TRX_ID.COUNT
215 LOOP
216 INSERT INTO JL_BR_CUSTOMER_TRX_EXTS
217 (CUSTOMER_TRX_ID
218 ,LEGAL_PROCESS_CODE
219 ,LEGAL_PROCESS_SOURCE_IND
220 ,VEHICLE_PLATE_STATE_CODE
221 ,VEHICLE_ANTT_INSCRIPTION
222 ,TOWING_VEH_PLATE_NUMBER
223 ,TOWING_VEH_PLATE_STATE_CODE
224 ,TOWING_VEH_ANTT_INSCRIPTION
225 ,SEAL_NUMBER
226 ,ELECTRONIC_INV_WEB_ADDRESS
227 ,ELECTRONIC_INV_ACCESS_KEY
228 ,ELECTRONIC_INV_PROTOCOL
229 ,LAST_UPDATE_DATE
230 ,LAST_UPDATED_BY
231 ,LAST_UPDATE_LOGIN
232 ,CREATION_DATE
233 ,CREATED_BY)
234 VALUES(
235 AUTO_CUSTOMER_TRX_ID(I),
236 HEADER_GDF_ATTRIBUTE19(I),
237 HEADER_GDF_ATTRIBUTE20(I),
238 HEADER_GDF_ATTRIBUTE21(I),
239 HEADER_GDF_ATTRIBUTE22(I),
240 HEADER_GDF_ATTRIBUTE23(I),
241 HEADER_GDF_ATTRIBUTE24(I),
242 HEADER_GDF_ATTRIBUTE25(I),
243 HEADER_GDF_ATTRIBUTE26(I),
244 HEADER_GDF_ATTRIBUTE27(I),
245 HEADER_GDF_ATTRIBUTE29(I),
246 HEADER_GDF_ATTRIBUTE30(I),
247 SYSDATE,
248 FND_GLOBAL.USER_ID,
249 FND_GLOBAL.LOGIN_ID,
250 SYSDATE,
251 FND_GLOBAL.USER_ID);
252 Set_Trx_Lock_Status(AUTO_CUSTOMER_TRX_ID(I));
253 END LOOP;
254 END IF;
255
256 ELSIF P_CALLED_FROM = 'ARXREC' THEN
257
258 OPEN C_COPYINV_ATTR;
259 FETCH C_COPYINV_ATTR BULK COLLECT INTO
260 COPY_CUSTOMER_TRX_ID,
261 LEGAL_PROCESS_CODE,
262 LEGAL_PROCESS_SOURCE_INDICATOR,
263 VEHICLE_PLATE_STATE_CODE,
264 VEHICLE_ANTT_INSCRIPTION,
265 TOWING_VEH_PLATE_NUMBER ,
266 TOWING_VEH_PLATE_STATE_CODE,
267 TOWING_VEH_ANTT_INSCRIPTION,
268 SEAL_NUMBER,
269 ELECTRONIC_INV_WEB_ADDRESS,
270 ELECTRONIC_INV_ACCESS_KEY,
271 ELECTRONIC_INV_PROTOCOL;
272 CLOSE C_COPYINV_ATTR;
273
274 IF COPY_CUSTOMER_TRX_ID.COUNT > 0 THEN
275
276 FOR I IN 1..COPY_CUSTOMER_TRX_ID.COUNT
277 LOOP
278 INSERT INTO JL_BR_CUSTOMER_TRX_EXTS
279 (CUSTOMER_TRX_ID
280 ,LEGAL_PROCESS_CODE
281 ,LEGAL_PROCESS_SOURCE_IND
282 ,VEHICLE_PLATE_STATE_CODE
283 ,VEHICLE_ANTT_INSCRIPTION
284 ,TOWING_VEH_PLATE_NUMBER
285 ,TOWING_VEH_PLATE_STATE_CODE
286 ,TOWING_VEH_ANTT_INSCRIPTION
287 ,SEAL_NUMBER
288 --,ELECTRONIC_INV_WEB_ADDRESS
289 --,ELECTRONIC_INV_ACCESS_KEY
290 --,ELECTRONIC_INV_PROTOCOL
291 ,LAST_UPDATE_DATE
292 ,LAST_UPDATED_BY
293 ,LAST_UPDATE_LOGIN
294 ,CREATION_DATE
295 ,CREATED_BY)
296 VALUES(
297 COPY_CUSTOMER_TRX_ID(I)
298 ,LEGAL_PROCESS_CODE(I)
299 ,LEGAL_PROCESS_SOURCE_INDICATOR(I)
300 ,VEHICLE_PLATE_STATE_CODE(I)
301 ,VEHICLE_ANTT_INSCRIPTION(I)
302 ,TOWING_VEH_PLATE_NUMBER(I)
303 ,TOWING_VEH_PLATE_STATE_CODE(I)
304 ,TOWING_VEH_ANTT_INSCRIPTION(I)
305 ,SEAL_NUMBER(I)
306 --,ELECTRONIC_INV_WEB_ADDRESS(I)
307 --,ELECTRONIC_INV_ACCESS_KEY(I)
308 --,ELECTRONIC_INV_PROTOCOL(I)
309 ,SYSDATE
310 ,FND_GLOBAL.USER_ID
311 ,FND_GLOBAL.LOGIN_ID
312 ,SYSDATE
313 ,FND_GLOBAL.USER_ID);
314 Set_Trx_Lock_Status(COPY_CUSTOMER_TRX_ID(I));
315 END LOOP;
316 END IF;
317 END IF;
318 END IF;
319 RETURN 1;
320
321 EXCEPTION
322 WHEN OTHERS THEN
323 RETURN 0;
324 END COPY_GDF_ATTRIBUTES;
325
326
327 /*Function name : Copy_GDF_Attributes_API
328 Description : This function will be called from the core AR Invoice API
329 This function will insert the Electronic Invoice attributes IN THE JL_BR_CUSTOMER_TRX_EXTS
330 the the extension table */
331 FUNCTION COPY_GDF_ATTRIBUTES_API (P_CUSTOMER_TRX_ID IN NUMBER) RETURN NUMBER IS
332
333 TYPE HEADER_GDF_ATTR_TYPE IS TABLE OF RA_INTERFACE_LINES_ALL.HEADER_GDF_ATTRIBUTE1%TYPE;
334 TYPE CUSTOMER_TRX_ID_TYPE IS TABLE OF RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE;
338 HEADER_GDF_ATTRIBUTE21 HEADER_GDF_ATTR_TYPE;
335 CUSTOMER_TRX_ID CUSTOMER_TRX_ID_TYPE;
336 HEADER_GDF_ATTRIBUTE19 HEADER_GDF_ATTR_TYPE;
337 HEADER_GDF_ATTRIBUTE20 HEADER_GDF_ATTR_TYPE;
339 HEADER_GDF_ATTRIBUTE22 HEADER_GDF_ATTR_TYPE;
340 HEADER_GDF_ATTRIBUTE23 HEADER_GDF_ATTR_TYPE;
341 HEADER_GDF_ATTRIBUTE24 HEADER_GDF_ATTR_TYPE;
342 HEADER_GDF_ATTRIBUTE25 HEADER_GDF_ATTR_TYPE;
343 HEADER_GDF_ATTRIBUTE26 HEADER_GDF_ATTR_TYPE;
344 HEADER_GDF_ATTRIBUTE27 HEADER_GDF_ATTR_TYPE;
345 HEADER_GDF_ATTRIBUTE29 HEADER_GDF_ATTR_TYPE;
346 HEADER_GDF_ATTRIBUTE30 HEADER_GDF_ATTR_TYPE;
347
348 CURSOR C_APIINV_ATTR IS SELECT
349 GT.CUSTOMER_TRX_ID,
350 GT.GLOBAL_ATTRIBUTE19,
351 GT.GLOBAL_ATTRIBUTE20,
352 GT.GLOBAL_ATTRIBUTE21,
353 GT.GLOBAL_ATTRIBUTE22,
354 GT.GLOBAL_ATTRIBUTE23,
355 GT.GLOBAL_ATTRIBUTE24,
356 GT.GLOBAL_ATTRIBUTE25,
357 GT.GLOBAL_ATTRIBUTE26,
358 GT.GLOBAL_ATTRIBUTE27,
359 GT.GLOBAL_ATTRIBUTE29,
360 GT.GLOBAL_ATTRIBUTE30
361 FROM AR_TRX_HEADER_GT GT
362 WHERE GT.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
363 L_COUNTRY_CODE VARCHAR2 (30);
364 BEGIN
365
366 FND_PROFILE.GET ('JGZZ_COUNTRY_CODE', L_COUNTRY_CODE);
367 IF L_COUNTRY_CODE = 'BR' THEN
368 OPEN C_APIINV_ATTR;
369 FETCH C_APIINV_ATTR BULK COLLECT INTO
370 CUSTOMER_TRX_ID,
371 HEADER_GDF_ATTRIBUTE19,
372 HEADER_GDF_ATTRIBUTE20,
373 HEADER_GDF_ATTRIBUTE21,
374 HEADER_GDF_ATTRIBUTE22,
375 HEADER_GDF_ATTRIBUTE23,
376 HEADER_GDF_ATTRIBUTE24,
377 HEADER_GDF_ATTRIBUTE25,
378 HEADER_GDF_ATTRIBUTE26,
379 HEADER_GDF_ATTRIBUTE27,
380 HEADER_GDF_ATTRIBUTE29,
381 HEADER_GDF_ATTRIBUTE30;
382 CLOSE C_APIINV_ATTR;
383
384 FOR I IN 1..CUSTOMER_TRX_ID.COUNT
385 LOOP
386 INSERT INTO JL_BR_CUSTOMER_TRX_EXTS
387 (CUSTOMER_TRX_ID
388 ,LEGAL_PROCESS_CODE
389 ,LEGAL_PROCESS_SOURCE_IND
390 ,VEHICLE_PLATE_STATE_CODE
391 ,VEHICLE_ANTT_INSCRIPTION
392 ,TOWING_VEH_PLATE_NUMBER
393 ,TOWING_VEH_PLATE_STATE_CODE
394 ,TOWING_VEH_ANTT_INSCRIPTION
395 ,SEAL_NUMBER
396 ,ELECTRONIC_INV_WEB_ADDRESS
397 ,ELECTRONIC_INV_ACCESS_KEY
398 ,ELECTRONIC_INV_PROTOCOL
399 ,LAST_UPDATE_DATE
400 ,LAST_UPDATED_BY
401 ,LAST_UPDATE_LOGIN
402 ,CREATION_DATE
403 ,CREATED_BY)
404 VALUES(
405 CUSTOMER_TRX_ID(I),
406 HEADER_GDF_ATTRIBUTE19(I),
407 HEADER_GDF_ATTRIBUTE20(I),
408 HEADER_GDF_ATTRIBUTE21(I),
409 HEADER_GDF_ATTRIBUTE22(I),
410 HEADER_GDF_ATTRIBUTE23(I),
411 HEADER_GDF_ATTRIBUTE24(I),
412 HEADER_GDF_ATTRIBUTE25(I),
413 HEADER_GDF_ATTRIBUTE26(I),
414 HEADER_GDF_ATTRIBUTE27(I),
415 HEADER_GDF_ATTRIBUTE29(I),
416 HEADER_GDF_ATTRIBUTE30(I),
417 SYSDATE,
418 FND_GLOBAL.USER_ID,
419 FND_GLOBAL.LOGIN_ID,
420 SYSDATE,
421 FND_GLOBAL.USER_ID);
422 Set_Trx_Lock_Status(CUSTOMER_TRX_ID(I));
423 END LOOP;
424 END IF;
425
426 RETURN 1;
427
428 EXCEPTION
429 WHEN OTHERS THEN
430 RETURN 0;
431 END COPY_GDF_ATTRIBUTES_API;
432
433 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
434
435 l_trx_status NUMBER(15);
436 l_trx_amount NUMBER;
437 l_CM_type VARCHAR2(20);
438 l_CM_status VARCHAR2(20);
439 CURSOR C_trx_details( p_trx_id IN NUMBER ) IS
440 SELECT selected_for_receipt_batch_id, sum(amount_due_remaining) FROM ar_payment_schedules WHERE customer_trx_id = p_trx_id
441 GROUP BY customer_trx_id,selected_for_receipt_batch_id;
442 CURSOR C_CM_Details( p_type_id IN NUMBER ) IS
443 SELECT TYPE, DEFAULT_STATUS FROM ra_cust_trx_types WHERE cust_trx_type_id = p_type_id;
444 L_COUNTRY_CODE varchar2(30);
445 BEGIN
446 FND_PROFILE.GET ('JGZZ_COUNTRY_CODE', L_COUNTRY_CODE);
447 IF L_COUNTRY_CODE = 'BR' THEN
448
449 OPEN C_trx_details(p_inv_customer_trx_id);
450 FETCH C_trx_details INTO l_trx_status,l_trx_amount;
451 CLOSE C_trx_details;
452
453 OPEN C_CM_Details(p_trx_type_id);
454 FETCH C_CM_Details INTO l_CM_type, l_CM_status;
455 CLOSE C_CM_Details;
456
457 IF l_trx_status = -999 THEN
458 IF l_CM_type = 'CM' AND l_CM_status = 'VD' AND (l_trx_amount + p_CM_amount) = 0 THEN
459 UPDATE ar_payment_schedules
460 SET selected_for_receipt_batch_id = NULL
461 WHERE selected_for_receipt_batch_id = -999 AND
462 customer_trx_id = p_inv_customer_trx_id;
463 RETURN 1;
464 ELSE
465 RETURN 0;
466 END IF;
467 ELSE
468 RETURN 1;
469 END IF;
470
471 ELSE
472 RETURN 1;
473 END IF;
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 RETURN 0;
478
479 END Create_Void_CM;
480
481 END JL_BR_SPED_PKG;
482
483
484