[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_AR_AUTO_INVOICE
Source
1 PACKAGE BODY JG_ZZ_AR_AUTO_INVOICE AS
2 /* $Header: jgzztnub.pls 120.0.12010000.3 2008/12/31 13:21:55 rsaini noship $ */
3
4 Function Is_context_enabled (l_country_code In Varchar2) Return Boolean IS
5 l_exist Varchar2(30);
6 Begin
7
8 /* Checks whether the context is enabled for the country. It checks the contexts
9 only for the JG_RA_CUSTOMER_TRX_LINES gdf. */
10 SELECT 'YES' INTO l_exist
11 FROM fnd_descr_flex_contexts
12 WHERE application_id = 7003
13 AND descriptive_flexfield_name like 'JG_RA_CUSTOMER_TRX_LINES'
14 AND descriptive_flex_context_code like '%ARXTWMAI.REGISTER_INFO%'
15 AND substr(descriptive_flex_context_code, 4, 2) = l_country_code;
16 IF l_exist = 'YES' THEN
17 Return TRUE;
18 ELSE
19 Return FALSE;
20 END IF;
21 Exception
22 When Others Then
23 Return FALSE;
24 End;
25
26
27 Function Is_Reg_Loc_Enabled Return Boolean IS
28 l_country_code Varchar2(30);
29 Begin
30 fnd_profile.get('JGZZ_COUNTRY_CODE', l_country_code);
31 If (l_country_code = 'TW' or l_country_code = 'AR') THEN
32 Return TRUE;
33 Elsif Is_context_enabled(l_country_code) THEN
34 Return TRUE;
35 Else
36 Return FALSE;
37 End If;
38 Exception
39 When Others Then
40 Return FALSE;
41 End;
42
43 Procedure Trx_Num_Upd (p_request_id In Number) Is
44 Cursor C_Trx_Lines (x_request_id Number) Is
45 Select l.trx_number
46 ,l.customer_trx_id
47 From ra_customer_trx_all l
48 Where l.request_id = x_request_id
49 And l.complete_flag = 'Y'
50 And l.customer_trx_id Is Not Null;
51
52 CURSOR C_AR_Batch_Details (l_batch_source_id Number) IS
53 SELECT substr(global_attribute2,1,4),
54 substr(global_attribute3,1,1),
55 auto_trx_numbering_flag,
56 global_attribute8,
57 global_attribute9
58 FROM ra_batch_sources_all
59 WHERE batch_source_id = l_batch_source_id;
60
61 TYPE trx_id is Table of ra_customer_trx_all.customer_trx_id%Type;
62 TYPE trx_num is Table of ra_customer_trx_all.trx_number%Type;
63
64 customer_trx_id trx_id;
65 trx_number trx_num;
66
67 l_batch_source_id Number;
68 l_auto_trx_num_flag Varchar2(1);
69 l_inv_word Varchar2(2);
70 l_init_trx_num Varchar2(8);
71 l_fin_trx_num Varchar2(8);
72 l_last_trx_date Varchar2(30);
73 l_adv_days Number;
74 l_org_id Number;
75 l_seq_name Varchar2(30);
76 l_seq_number Number;
77 l_err_code Number;
78 l_trx_number Varchar2(30);
79 l_debug_loc Varchar2(100);
80 l_country_code Varchar2(30);
81 FATAL_ERROR Exception;
82 l_branch_number Varchar2(4);
83 l_document_letter Varchar2(1);
84 l_cai_num Varchar2(15);
85 l_cai_due_date Varchar2(20);
86 f_org_id Varchar2(15);
87 temp1 Number;
88 temp2 Number;
89 Begin
90 fnd_profile.get('JGZZ_COUNTRY_CODE', l_country_code);
91 fnd_profile.get('ORG_ID',l_org_id);
92
93 If l_country_code = 'TW' Then
94 l_debug_loc := 'jg_zz_ar_auto_invoice.trx_num_upd, country code TW';
95 Open C_Trx_lines(p_request_id);
96 Fetch C_Trx_lines Bulk Collect Into trx_number, customer_trx_id;
97 Close C_Trx_lines;
98
99 Select To_number(cr.argument3)
100 Into l_batch_source_id
101 From fnd_concurrent_requests cr
102 Where request_id = p_request_id;
103
104 l_debug_loc := 'ja_tw_sh_gui_utils.get_trx_src_info';
105 ja_tw_sh_gui_utils.get_trx_src_info(
106 l_batch_source_id
107 ,l_auto_trx_num_flag
108 ,l_inv_word
109 ,l_init_trx_num
110 ,l_fin_trx_num
111 ,l_last_trx_date
112 ,l_adv_days
113 ,l_org_id);
114
115 l_seq_name := ja_tw_sh_gui_utils.get_seq_name(l_batch_source_id);
116
117 If trx_number.count > 0 Then
118 For i in customer_trx_id.FIRST .. customer_trx_id.LAST Loop
119 l_debug_loc := 'ja_tw_sh_gui_utils.val_trx_num';
120 If ja_tw_sh_gui_utils.val_trx_num(
121 NULL
122 ,l_batch_source_id
123 ,l_fin_trx_num
124 ,'RAXTRX') = 'FATAL' Then
125 Exit;
126 Else
127 ja_tw_sh_gui_utils.get_next_seq_num(
128 l_seq_name
129 ,l_seq_number
130 ,l_err_code);
131 l_trx_number := l_inv_word || LPAD(l_seq_number,8,'0');
132 trx_number(i) := l_trx_number;
133 End If;
134 End Loop;
135
136 Forall j in customer_trx_id.FIRST .. customer_trx_id.LAST
137 Update ra_customer_trx_all
138 Set trx_number = trx_number(j)
139 Where customer_trx_id = customer_trx_id(j)
140 and request_id = p_request_id;
141 End If;
142
143 Elsif l_country_code = 'AR' Then
144 fnd_profile.get('ORG_ID',f_org_id);
145 l_debug_loc := 'jg_zz_ar_auto_invoice.trx_num_upd, country code AR';
146 Open C_Trx_lines(p_request_id);
147 Fetch C_Trx_lines Bulk Collect Into trx_number, customer_trx_id;
148 Close C_Trx_lines;
149
150 SELECT to_number(cr.argument3)
151 INTO l_batch_source_id
152 FROM fnd_concurrent_requests cr
153 WHERE request_id = p_request_id;
154
155
156 /* Code added for Transaction created by CopyTo Operation */
157 --Start
158 IF l_batch_source_id IS NULL THEN
159
160 SELECT B.batch_source_id INTO temp1
161 FROM ra_customer_trx_all A, ra_customer_trx_all B
162 WHERE A.RECURRED_FROM_TRX_NUMBER = B.trx_number
163 AND A.trx_number = trx_number(1) AND B.ORG_ID = f_org_id and rownum =1;
164 BEGIN
165
166 SELECT GLOBAL_ATTRIBUTE1 INTO temp2
167 FROM RA_BATCH_SOURCES_ALL WHERE BATCH_SOURCE_ID = temp1;
168 EXCEPTION
169 WHEN NO_DATA_FOUND THEN
170 null;
171 END;
172 IF temp2 IS NULL THEN
173 l_batch_source_id := temp1;
174 ELSE
175 l_batch_source_id := temp2;
176 END IF;
177
178 END IF;
179 --End
180
181 IF l_batch_source_id IS NOT NULL THEN
182 l_debug_loc := 'Getting Transaction Source Information';
183
184 OPEN C_AR_Batch_Details(l_batch_source_id);
185 FETCH C_AR_Batch_Details INTO l_branch_number,l_document_letter,l_auto_trx_num_flag,
186 l_cai_num,l_cai_due_date;
187 CLOSE C_AR_Batch_Details;
188
189 l_seq_name := 'JL_ZZ_TRX_NUM_'
190 || to_char(l_batch_source_id)
191 || '_'
192 || f_org_id
193 || '_S';
194
195 END IF;
196
197 IF trx_number.count > 0 AND l_auto_trx_num_flag = 'Y' THEN
198 FOR i IN customer_trx_id.FIRST .. customer_trx_id.LAST LOOP
199 l_debug_loc := 'Getting Next Sequence number';
200 JL_ZZ_AR_LIBRARY_1_PKG.get_next_seq_number (l_seq_name, l_seq_number,1,l_err_code);
201 IF l_err_code = 0 THEN
202 l_trx_number := l_document_letter || '-' || l_branch_number || '-'
203 || lpad(l_seq_number,8,'0');
204 trx_number(i) := l_trx_number;
205 END IF;
206 END LOOP;
207
208 FORALL j IN customer_trx_id.FIRST .. customer_trx_id.LAST
209 UPDATE ra_customer_trx_all
210 SET trx_number = trx_number(j),
211 global_attribute17 = l_cai_num,
212 global_attribute18 = l_cai_due_date
213 WHERE customer_trx_id = customer_trx_id(j)
214 AND request_id = p_request_id;
215 END IF;
216
217 End If;
218 Exception
219 When Others Then
220 arp_standard.debug('-- Found an exception at ' || l_debug_loc||'.');
221 arp_standard.debug('-- ' || SQLERRM);
222 End;
223
224 PROCEDURE val_trx_range (p_request_id IN Number, p_flag OUT NOCOPY Number) IS
225 CURSOR C_Trx_Lines IS
226 SELECT l.customer_trx_id
227 FROM ra_customer_trx_all l
228 WHERE l.request_id = p_request_id
229 --And l.complete_flag = 'Y'
230 AND l.customer_trx_id IS NOT NULL;
231
232 CURSOR C_Reject_Entry (p_trx_id NUMBER) IS
233 SELECT A.customer_trx_line_id line_id, B.cust_trx_line_gl_dist_id dist_id
234 FROM ra_customer_trx_lines_all A ,ra_cust_trx_line_gl_dist_all B
235 WHERE A.customer_trx_id = p_trx_id AND
236 A.customer_trx_line_id = B.customer_trx_line_id
237 AND ROWNUM = 1;
238
239 TYPE trx_id IS TABLE OF ra_customer_trx_all.customer_trx_id%Type;
240
241 customer_trx_id trx_id;
242
243 l_batch_source_id Number;
244 l_auto_trx_num_flag Varchar2(1);
245 l_inv_word Varchar2(2);
246 l_init_trx_num Varchar2(8);
247 l_fin_trx_num Varchar2(8);
248 l_last_trx_date Varchar2(30);
249 l_adv_days Number;
250 l_seq_name Varchar2(30);
251 l_seq_number Number;
252 l_err_code Number;
253 l_trx_number Varchar2(30);
254 l_debug_loc Varchar2(100);
255 l_country_code Varchar2(30);
256 l_org_id Varchar2(15);
257 l_count Number DEFAULT 0;
258 l_line_id Number;
259 l_dist_id Number;
260 l_last_trx_num Varchar2(8);
261 l_message_text Varchar2(240);
262 l_batch_source_name Varchar2(50);
263 BEGIN
264 fnd_profile.get('JGZZ_COUNTRY_CODE', l_country_code);
265 fnd_profile.get('ORG_ID',l_org_id);
266 IF l_country_code = 'TW' THEN
267 p_flag := 0;
268 l_debug_loc := 'Country code TW';
269
270 OPEN C_Trx_lines;
271 FETCH C_Trx_lines BULK COLLECT INTO customer_trx_id;
272 CLOSE C_Trx_lines;
273
274 SELECT TO_NUMBER(cr.argument3)
275 INTO l_batch_source_id
276 FROM fnd_concurrent_requests cr
277 WHERE request_id = p_request_id;
278
279 SELECT name INTO l_batch_source_name
280 FROM RA_BATCH_SOURCES_ALL
281 WHERE batch_source_id = l_batch_source_id;
282
283 l_debug_loc := 'Getting Transaction Source details';
284 ja_tw_sh_gui_utils.get_trx_src_info(
285 l_batch_source_id
286 ,l_auto_trx_num_flag
287 ,l_inv_word
288 ,l_init_trx_num
289 ,l_fin_trx_num
290 ,l_last_trx_date
291 ,l_adv_days
292 ,l_org_id);
293
294 l_seq_name := ja_tw_sh_gui_utils.get_seq_name(l_batch_source_id);
295 l_last_trx_num := ja_tw_sh_gui_utils.get_last_trx_num(l_seq_name);
296
297 --fnd_message.set_name( 'JA','JA_TW_GUI_NUM_OVERLIMIT_CHQ');
298 fnd_message.set_name( 'JA','JA_TW_AR_GUI_NUM_OUT_OF_RANGE');
299 fnd_message.set_token('BATCH_SOURCE_NAME',l_batch_source_name);
300 l_message_text := fnd_message.GET;
301
302 IF customer_trx_id.count > 0 THEN
303 l_debug_loc := 'Checking for the Sequence maximum limit';
304 FOR i IN customer_trx_id.FIRST .. customer_trx_id.LAST LOOP
305 IF to_number(l_last_trx_num) < to_number(l_fin_trx_num) THEN
306 null;
307 ELSE
308 l_count := i;
309 EXIT;
310 END IF;
311 l_last_trx_num := to_number(l_last_trx_num + 1);
312 END LOOP;
313 END IF;
314
315 IF l_count > 0 THEN
316 l_debug_loc := 'Inserting into Interface Error over limit transactions';
317 FOR i IN l_count .. customer_trx_id.LAST LOOP
318 FOR C_Reject_Entry_Rec IN C_Reject_Entry(customer_trx_id(i)) LOOP
319 INSERT INTO ra_interface_errors(
320 interface_line_id,
321 interface_distribution_id,
322 message_text,
323 org_id)
324 VALUES(
325 C_Reject_Entry_Rec.line_id,
326 C_Reject_Entry_Rec.dist_id,
327 l_message_text,
328 l_org_id);
329 p_flag := p_flag + 1;
330 END LOOP;
331 END LOOP;
332
333 END IF;
334
335 END IF;
336
337 /* To nullify the global attribute columns. This is to avoid populating
338 Global columns while Copying transaction by "CopyTo" function */
339 --IF Is_context_enabled(l_country_code) THEN
340 -- arp_standard.debug('-- Start JE_GLOBAL_PKG.nullify_globalcolumns ');
341 -- JE_COMMON_PKG.nullify_globalcolumns(p_request_id);
342 -- arp_standard.debug('-- End JE_GLOBAL_PKG.nullify_globalcolumns ');
343 --END IF;
344
345 EXCEPTION
346 WHEN OTHERS THEN
347 p_flag := 0;
348 arp_standard.debug('-- Found an exception at :' || l_debug_loc||'.');
349 arp_standard.debug('-- ' || SQLERRM);
350 END;
351
352 End JG_ZZ_AR_AUTO_INVOICE;
353