[Home] [Help]
PACKAGE BODY: APPS.ARP_EBS_AUTOINV_PREPROC
Source
1 PACKAGE BODY ARP_EBS_AUTOINV_PREPROC AS
2 /* $Header: AREBSPPB.pls 120.5 2005/07/01 15:31:58 mraymond noship $ */
3
4 PROCEDURE update_trx ( p_request_id IN NUMBER,
5 p_func_curr_code IN VARCHAR2,
6 p_error_code IN OUT NOCOPY NUMBER ) IS
7
8 CURSOR curr_code IS
9 SELECT distinct l.currency_code, curr.end_date_active
10 FROM RA_INTERFACE_LINES_GT l,
11 FND_CURRENCIES curr
12 WHERE l.request_id = p_request_id
13 AND l.currency_code = curr.currency_code
14 AND curr.derive_type = 'EMU' ;
15
16 err_msg VARCHAR2(255);
17
18 BEGIN
19 err_msg := FND_MESSAGE.GET_STRING('AR','AR_RAXTRX_CONV_CM');
20
21 FOR rec IN curr_code LOOP
22
23 -- Flag credit memos with temporary error
24 INSERT INTO RA_INTERFACE_ERRORS
25 (INTERFACE_LINE_ID,
26 MESSAGE_TEXT,
27 INVALID_VALUE,
28 ORG_ID )
29 SELECT interface_line_id,
30 err_msg,
31 NULL,
32 org_id
33 FROM RA_INTERFACE_LINES
34 WHERE request_id = p_request_id
35 AND trx_date > NVL(rec.end_date_active,
36 to_date('31-12-2001','DD-MM-YYYY'))
37 AND currency_code = rec.currency_code
38 AND cust_trx_type_id in ( SELECT cust_trx_type_id
39 FROM ra_cust_trx_types
40 WHERE type = 'CM' ) ;
41
42 -- Updating dist rows
43 -- note use of sysdate - EMU currencies have fixed conversion
44 UPDATE ra_interface_distributions dist
45 SET amount = GL_CURRENCY_API.convert_amount( rec.currency_code,
46 p_func_curr_code,
47 sysdate,
48 'EMU FIXED',
49 dist.amount ),
50 acctd_amount = null
51 WHERE interface_line_id in (
52 select interface_line_id
53 from ra_interface_lines_gt
54 where request_id = p_request_id
55 and trx_date > NVL(rec.end_date_active,
56 to_date('31-12-2001','DD-MM-YYYY'))
57 and currency_code = rec.currency_code)
58 AND amount is not null;
59
60 -- Updating salescredit
61 UPDATE ra_interface_salescredits salescred
62 SET sales_credit_amount_split = GL_CURRENCY_API.convert_amount( rec.currency_code,
63 p_func_curr_code,
64 sysdate,
65 'EMU FIXED',
66 salescred.sales_credit_amount_split )
67 WHERE interface_line_id in (
68 select interface_line_id
69 from ra_interface_lines_gt
70 where request_id = p_request_id
71 and trx_date > NVL(rec.end_date_active,
72 to_date('31-12-2001','DD-MM-YYYY'))
73 and currency_code = rec.currency_code)
74 AND sales_credit_amount_split is not null;
75
76
77 -- Update credit memo headers
78 UPDATE ra_interface_lines_gt l
79 SET
80 reference_line_id = NULL,
81 reference_line_context = NULL,
82 reference_line_attribute1 = NULL,
83 reference_line_attribute2 = NULL,
84 reference_line_attribute3 = NULL,
85 reference_line_attribute4 = NULL,
86 reference_line_attribute5 = NULL,
87 reference_line_attribute6 = NULL,
88 reference_line_attribute7 = NULL,
89 reference_line_attribute8 = NULL,
90 reference_line_attribute9 = NULL,
91 reference_line_attribute10 = NULL,
92 reference_line_attribute11 = NULL,
93 reference_line_attribute12 = NULL,
94 reference_line_attribute13 = NULL,
95 reference_line_attribute14 = NULL,
96 reference_line_attribute15 = NULL,
97 previous_customer_trx_id = NULL
98 WHERE request_id = p_request_id
99 AND trx_date > NVL(rec.end_date_active,
100 to_date('31-12-2001','DD-MM-YYYY'))
101 AND currency_code = rec.currency_code
102 AND cust_trx_type_id in ( SELECT cust_trx_type_id
103 FROM ra_cust_trx_types
104 WHERE type = 'CM' ) ;
105
106 -- Update invoice and CM headers
107
108 /* 4448712 - Need to update tax lines with null trx_dates too */
109 UPDATE ra_interface_lines_gt l
110 SET currency_code = p_func_curr_code,
111 conversion_rate = 1,
112 conversion_type = 'User',
113 amount = GL_CURRENCY_API.convert_amount( l.currency_code,
114 p_func_curr_code,
115 l.trx_date,
116 'EMU FIXED',
117 l.amount ) ,
118 unit_selling_price = GL_CURRENCY_API.convert_amount( l.currency_code,
119 p_func_curr_code,
120 l.trx_date,
121 'EMU FIXED',
122 l.unit_selling_price ) ,
123 unit_standard_price = GL_CURRENCY_API.convert_amount( l.currency_code,
124 p_func_curr_code,
125 l.trx_date,
126 'EMU FIXED',
127 l.unit_standard_price )
128 WHERE request_id = p_request_id
129 AND currency_code = rec.currency_code
130 AND (trx_date > NVL(rec.end_date_active,
131 to_date('31-12-2001','DD-MM-YYYY'))
132 OR EXISTS (
133 SELECT 'child needs processing'
134 FROM ra_interface_lines PARENT
135 WHERE l.link_to_line_id = PARENT.interface_line_id
136 AND PARENT.trx_date > NVL(rec.end_date_active,
137 to_date('31-12-2001', 'DD-MM-YYYY'))
138 AND PARENT.request_id = p_request_id
139 )
140 );
141
142 END LOOP;
143
144
145 EXCEPTION
146 WHEN GL_CURRENCY_API.NO_RATE THEN
147 arp_file.write_log( p_text => 'Exception : No rates defined between these 2 currencies');
148 arp_file.write_log( p_text => SQLERRM(SQLCODE) );
149 WHEN OTHERS THEN
150 arp_file.write_log( p_text => SQLERRM(SQLCODE) );
151 END ;
152
153 END ARP_EBS_AUTOINV_PREPROC ;