[Home] [Help]
PACKAGE BODY: APPS.FUN_TRX_PUB
Source
1 PACKAGE BODY FUN_TRX_PUB AS
2 /* $Header: funtrxvalinsb.pls 120.20.12010000.6 2009/01/12 04:40:29 srampure ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FUN_TRX_VAL_AND_INS';
4 G_DEBUG VARCHAR2(5);
5
6
7 PROCEDURE Print
8 (
9 P_string IN VARCHAR2
10 ) IS
11
12
13 BEGIN
14
15 IF G_DEBUG = 'Y' THEN
16 fnd_file.put_line(FND_FILE.LOG, p_string);
17
18 END IF;
19
20 EXCEPTION
21 WHEN OTHERS THEN
22 APP_EXCEPTION.RAISE_EXCEPTION;
23 END Print;
24
25 PROCEDURE Set_Return_Status
26 ( x_orig_status IN OUT NOCOPY VARCHAR2,
27 p_new_status IN VARCHAR2
28 ) IS
29 BEGIN
30 -- API body
31 IF (x_orig_status = FND_API.G_RET_STS_SUCCESS
32 AND p_new_status <> FND_API.G_RET_STS_SUCCESS) THEN
33 x_orig_status := p_new_status;
34 ELSIF (x_orig_status = FND_API.G_RET_STS_ERROR
35 AND p_new_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
36 x_orig_status := p_new_status;
37 END IF;
38
39 Print ( 'Val and Insert >>>> '|| 'Setting the status '|| x_orig_status);
40 -- End of API body.
41 EXCEPTION
42 WHEN OTHERS THEN
43 Print('Set return status - Unexpected error ');
44 APP_EXCEPTION.RAISE_EXCEPTION;
45 END Set_Return_Status;
46
47
48
49 FUNCTION insert_rejections(
50 p_batch_id IN NUMBER,
51 p_trx_id IN NUMBER default null,
52 p_dist_id IN NUMBER default null,
53 p_batch_dist_id IN NUMBER default null,
54 p_reject_code IN VARCHAR2 default null,
55 p_reject_reason IN VARCHAR2
56 ) RETURN BOOLEAN IS
57
58 debug_info VARCHAR2(500);
59
60 BEGIN
61
62 debug_info := '(Insert Rejections 1) Insert into FUN_INTERFACE_REJECTIONS, REJECT REASON: '||p_reject_reason;
63 Print('Insert Rejections >>'||debug_info);
64
65 Print('Insert Rejections >>'||'Btc id:' || to_char(p_batch_id));
66 Print('Insert Rejections >>'||'gl_date:' || to_char(p_reject_code));
67 INSERT INTO FUN_INTERFACE_REJECTIONS (
68 batch_id,
69 trx_id,
70 dist_id,
71 batch_dist_id,
72 reject_code,
73 reject_reason
74 )
75 VALUES(
76 p_batch_id,
77 nvl(p_trx_id,null),
78 nvl(p_dist_id,null),
79 nvl(p_batch_dist_id,null),
80 p_reject_code,
81 p_reject_reason
82 );
83 Print('Insert Rejections >>'||'Sucessfully inserted into Rejections');
84 RETURN(TRUE);
85
86 EXCEPTION
87 WHEN OTHERS then
88 Print('Insert Rejections >>'||'Insert into Rejections Table Failed');
89 IF (SQLCODE < 0) then
90 Print(SQLERRM);
91 END IF;
92 RETURN (FALSE);
93
94 END insert_rejections;
95
96
97 Procedure CREATE_BATCH(
98 p_api_version IN NUMBER,
99 p_init_msg_list IN VARCHAR2 ,
100 p_commit IN VARCHAR2 ,
101 p_validation_level IN NUMBER ,
102 p_debug IN VARCHAR2,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2,
106 p_sent IN VARCHAR2,
107 p_calling_sequence IN VARCHAR2,
108 p_insert IN VARCHAR2 ,
109 p_batch_rec IN OUT NOCOPY FULL_BATCH_REC_TYPE,
110 p_trx_tbl IN OUT NOCOPY FULL_TRX_TBL_TYPE,
111 p_init_dist_tbl IN OUT NOCOPY FULL_INIT_DIST_TBL_TYPE,
112 p_dist_lines_tbl IN OUT NOCOPY FULL_DIST_LINE_TBL_TYPE
113 ) IS
114
115 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_BATCH';
116 l_api_version CONSTANT NUMBER := 1.0;
117 l_batch_rec FUN_TRX_PVT.BATCH_REC_TYPE;
118 l_trx_tbl FUN_TRX_PVT.TRX_TBL_TYPE;
119 l_init_dist_tbl FUN_TRX_PVT.INIT_DIST_TBL_TYPE;
120 l_dist_lines_tbl FUN_TRX_PVT.DIST_LINE_TBL_TYPE;
121 l_trx_rec_type FUN_TRX_PVT.TRX_REC_TYPE;
122
123 l_init_dist_rec_type FUN_TRX_PVT.INIT_DIST_REC_TYPE;
124
125 l_dist_lines_rec_type FUN_TRX_PVT.DIST_LINE_REC_TYPE;
126
127 l_count NUMBER; -- Index for trx_tbl
128 l_count_lines NUMBER; -- Index for dist_lines_tbl
129
130 l_return_status varchar2(1);
131 -- Bug 7340636 Increased the size of l_msg_data from 80 to 2000.
132 l_msg_data varchar2(2000);
133 l_msg_count number;
134 l_msg varchar2(2000);
135 l_app varchar2(10);
136
137 l_old_batch_rec FULL_BATCH_REC_TYPE;
138 l_old_trx_tbl FULL_TRX_TBL_TYPE;
139 l_old_init_dist_tbl FULL_INIT_DIST_TBL_TYPE;
140 l_old_dist_lines_tbl FULL_DIST_LINE_TBL_TYPE;
141
142 l_init_msg_list VARCHAR2(1);
143
144 l_seq_version_id number;
145 l_sequence_number number;
146 l_assignment_id number;
147 l_error_code varchar2(15);
148
149 l_user number;
150 l_login number;
151 l_wfkey varchar2(1000);
152 l_batch_id number;
153 l_trx_id number;
154 l_unique_batch_id NUMBER;
155 l_to_ledger_id NUMBER; -- Bug: 7695801
156
157 -- 25-10-2007 MAKANSAL
158 -- For Bug # 6527666 Introduced to keep the recipient party id and recipient legal entity id
159 l_le_party_id Xle_Firstparty_Information_V.party_id%type;
160 l_to_le_id GL_LEDGER_LE_BSV_SPECIFIC_V.LEGAL_ENTITY_ID%type;
161
162 -- 25-10-2007 MAKANSAL
163 -- For Bug # 6527666 Introduced the Cursor to fetch the Recipient Legal Entity Id
164 Cursor C_Le_Id(cp_le_party_id In Xle_Entity_Profiles.party_id%type) Is
165 Select legal_entity_id
166 From Xle_Firstparty_Information_V
167 Where party_id = cp_le_party_id;
168
169 BEGIN
170
171 -- Set the debug flag
172 G_DEBUG := p_debug;
173
174 Print('Val and Insert >>>>'||'Start of the API');
175 -- Storing Initial Values
176 l_old_batch_rec := p_batch_rec;
177 l_old_trx_tbl := p_trx_tbl;
178 l_old_init_dist_tbl := p_init_dist_tbl;
179 l_old_dist_lines_tbl := p_dist_lines_tbl;
180 -- Standard Start of API savepoint
181
182 SAVEPOINT Fun_Trx_Val_And_Insert_PUB;
183
184 Print('Val and Insert >>>>'||'API Compatibilty Check');
185 -- Standard Call to check for API compatibility
186 IF NOT FND_API.Compatible_API_Call (l_api_version,
187 p_api_version,
188 l_api_name,
189 G_PKG_NAME)
190 THEN
191 Print('Val and Insert >>>>'||'Non compatible API call');
192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
193 END IF;
194 -- Initialize API return status to success
195 x_return_status := FND_API.G_RET_STS_SUCCESS;
196
197 -- Initialize message list if p_init_msg_list is set to TRUE.
198 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE) ) THEN
199 FND_MSG_PUB.initialize;
200 END IF;
201
202 /*Initialize the Stack of the Validation API's when calling_sequence is Intercompany Imort Programs*/
203 If (p_calling_sequence = 'Intercompany Import Program') then
204 l_init_msg_list := FND_API.G_TRUE;
205 else
206 l_init_msg_list := FND_API.G_FALSE;
207 End If;
208
209 Print('Val and Insert >>>>'||'Populating Parameters to be sent to Batch Validation API');
210 Print('Val and Insert >>>>'||'Value of message list '||l_init_msg_list);
211 --Populate the Parameters to be sent to Init_Batch_Validate
212 l_batch_rec.batch_id := p_batch_rec.batch_id;
213 l_batch_rec.batch_number := p_batch_rec.batch_number;
214 l_batch_rec.initiator_id := p_batch_rec.initiator_id;
215 l_batch_rec.from_le_id := p_batch_rec.from_le_id;
216 l_batch_rec.from_ledger_id := p_batch_rec.from_ledger_id;
217 l_batch_rec.control_total := p_batch_rec.control_total;
218 l_batch_rec.currency_code := p_batch_rec.currency_code;
219 l_batch_rec.exchange_rate_type := p_batch_rec.exchange_rate_type;
220 l_batch_rec.status := p_batch_rec.status;
221 l_batch_rec.description := p_batch_rec.description;
222 l_batch_rec.trx_type_id := p_batch_rec.trx_type_id;
223 l_batch_rec.trx_type_code := p_batch_rec.trx_type_code;
224 l_batch_rec.gl_date := p_batch_rec.gl_date;
225 l_batch_rec.batch_date := p_batch_rec.batch_date;
226 l_batch_rec.reject_allowed := p_batch_rec.reject_allow_flag;
227 l_batch_rec.from_recurring_batch := p_batch_rec.from_recurring_batch_id;
228 l_batch_rec.automatic_proration_flag := 'N';
229
230
231 Print('Val and Insert >>>>'||'Population of Trx_Tbl');
232
233 l_count := 1;
234
235 -- Populate l_trx_tbl, l_init_dist_tbl, l_dist_lines_tbl;
236 for l_head_count in 1..p_trx_tbl.count
237 LOOP
238 l_trx_tbl(l_count).trx_id := p_trx_tbl(l_head_count).trx_id;
239 l_trx_tbl(l_count).trx_number := p_trx_tbl(l_head_count).trx_number;
240 l_trx_tbl(l_count).initiator_id := p_trx_tbl(l_head_count).initiator_id;
241 l_trx_tbl(l_count).recipient_id := p_trx_tbl(l_head_count).recipient_id;
242 l_trx_tbl(l_count).to_le_id := p_trx_tbl(l_head_count).to_le_id;
243 l_trx_tbl(l_count).to_ledger_id := p_trx_tbl(l_head_count).to_ledger_id;
244 l_trx_tbl(l_count).batch_id := p_trx_tbl(l_head_count).batch_id;
245 l_trx_tbl(l_count).status := p_trx_tbl(l_head_count).status;
246 l_trx_tbl(l_count).init_amount_cr := p_trx_tbl(l_head_count).init_amount_cr;
247 l_trx_tbl(l_count).init_amount_dr := p_trx_tbl(l_head_count).init_amount_dr;
248 l_trx_tbl(l_count).reci_amount_cr := p_trx_tbl(l_head_count).reci_amount_cr;
249 l_trx_tbl(l_count).reci_amount_dr := p_trx_tbl(l_head_count).reci_amount_dr;
250 l_trx_tbl(l_count).invoicing_rule := p_trx_tbl(l_head_count).invoice_flag;
251 l_trx_tbl(l_count).approver_id := p_trx_tbl(l_head_count).approver_id;
252 l_trx_tbl(l_count).approval_date := p_trx_tbl(l_head_count).approval_date;
253 l_trx_tbl(l_count).original_trx_id := p_trx_tbl(l_head_count).original_trx_id;
254 l_trx_tbl(l_count).reversed_trx_id := p_trx_tbl(l_head_count).reversed_trx_id;
255 l_trx_tbl(l_count).from_recurring_trx_id :=
256 p_trx_tbl(l_head_count).from_recurring_trx_id;
257 l_trx_tbl(l_count).initiator_instance := p_trx_tbl(l_head_count).initiator_instance_flag;
258 l_trx_tbl(l_count).recipient_instance := p_trx_tbl(l_head_count).recipient_instance_flag;
259
260 l_count := l_count+1;
261
262
263 END LOOP;
264
265 l_count_lines := 1;
266
267 Print('Val and Insert >>>>'||'Populate Batch Dist');
268
269 If p_init_dist_tbl is not null then
270 Print('Val and Insert >>>>'||'BATCH DIST TBL NOT NULL '||P_INIT_dist_tbl.COUNT);
271 for l_line_count in 1..p_init_dist_tbl.count
272 LOOP
273 Print('Val and Insert >>>>'||p_init_dist_tbl(l_line_count).batch_dist_id);
274 Print('Val and Insert >>>>'||p_init_dist_tbl(l_line_count).line_number);
275 Print('Val and Insert >>>>'||p_init_dist_tbl(l_line_count).batch_id);
276 Print('Val and Insert >>>>'||p_init_dist_tbl(l_line_count).ccid);
277 Print('Val and Insert >>>>'||p_init_dist_tbl(l_line_count).amount_cr);
278 Print('Val and Insert >>>>'||p_init_dist_tbl(l_line_count).amount_Dr);
279 l_init_dist_tbl(l_count_lines).batch_dist_id :=
280 p_init_dist_tbl(l_line_count).batch_dist_id;
281 l_init_dist_tbl(l_count_lines).line_number :=
282 p_init_dist_tbl(l_line_count).line_number;
283 l_init_dist_tbl(l_count_lines).batch_id :=
284 p_init_dist_tbl(l_line_count).batch_id;
285 l_init_dist_tbl(l_count_lines).ccid :=
286 p_init_dist_tbl(l_line_count).ccid;
287 l_init_dist_tbl(l_count_lines).amount_cr :=
288 p_init_dist_tbl(l_line_count).amount_cr;
289 l_init_dist_tbl(l_count_lines).amount_dr :=
290 p_init_dist_tbl(l_line_count).amount_dr;
291
292 l_count_lines := l_count_lines + 1;
293 END LOOP;
294 End If;
295
296 Print('Val and Insert >>>>'||'Dist Lines Tbl');
297
298 l_count_lines := 1;
299
300 If p_dist_lines_tbl is not null then
301 Print('Val and Insert >>>>'||'Dist lines not null');
302 for l_line_count in 1..p_dist_lines_tbl.count
303 LOOP
304 Print('Val and Insert >>>>'||'Record Details for '||l_line_count);
305 Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).trx_id);
306 Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).dist_id);
307 Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).line_id);
308 Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).party_id);
309 Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).party_type_flag);
310 Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).dist_type_flag);
311 Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).batch_dist_id);
312 Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).amount_cr);
313 Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).amount_dr);
314 Print('Val and Insert >>>>'||p_dist_lines_tbl(l_line_count).ccid); --Bug 3603338
315 Print('Val and Insert >>>>'|| 'End of Record Details');
316
317 l_dist_lines_tbl(l_count_lines).trx_id :=
318 p_dist_lines_tbl(l_line_count).trx_id;
319 l_dist_lines_tbl(l_count_lines).dist_id :=
320 p_dist_lines_tbl(l_line_count).dist_id;
321 l_dist_lines_tbl(l_count_lines).line_id :=
322 p_dist_lines_tbl(l_line_count).line_id;
323 l_dist_lines_tbl(l_count_lines).party_id :=
324 p_dist_lines_tbl(l_line_count).party_id;
325 l_dist_lines_tbl(l_count_lines).party_type :=
326 p_dist_lines_tbl(l_line_count).party_type_flag;
327 l_dist_lines_tbl(l_count_lines).dist_type :=
328 p_dist_lines_tbl(l_line_count).dist_type_flag;
329 l_dist_lines_tbl(l_count_lines).batch_dist_id :=
330 p_dist_lines_tbl(l_line_count).batch_dist_id;
331 l_dist_lines_tbl(l_count_lines).amount_cr :=
332 p_dist_lines_tbl(l_line_count).amount_cr;
333 l_dist_lines_tbl(l_count_lines).amount_dr :=
334 p_dist_lines_tbl(l_line_count).amount_dr;
335 l_dist_lines_tbl(l_count_lines).ccid :=
336 p_dist_lines_tbl(l_line_count).ccid;--Bug 3603338
337
338 l_count_lines := l_count_lines + 1;
339
340 END LOOP;
341 End if;
342
343 Print('Val and Insert >>>>'||'Validating the Batch');
344
345 IF (p_calling_sequence = 'Intercompany Import Program') then
346 Print('Val and Insert >>>>'||'Validating Batch Id');
347 BEGIN
348 SELECT batch_id INTO l_unique_batch_id
349 FROM fun_trx_batches
350 WHERE batch_id=l_batch_rec.batch_id;
351 -- IF batch_id exist
352 l_return_status :=FND_API.G_RET_STS_ERROR;
353 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
354 FND_MESSAGE.SET_NAME('FUN', 'FUN_DUPLICATE_BATCH_ID');
355 FND_MESSAGE.SET_TOKEN('BATCH_ID',l_batch_rec.batch_id);
356 l_msg := FND_Message.Get;
357 IF (insert_rejections(p_batch_id => l_batch_rec.batch_id,
358 p_reject_reason => l_msg) <> TRUE) THEN
359 Print('Val and Insert >>>>'||'insert_rejections of invalid batches failure');
360 RAISE fnd_api.g_exc_unexpected_error;
361 END IF;
362 END IF;
363 EXCEPTION
364 WHEN no_data_found THEN
365 Print('Val and Insert >>>>'||'Batch Id is unique');
366 l_return_status:=FND_API.G_RET_STS_SUCCESS;
367 END;
368 END IF;
369
370 IF (l_return_status = FND_API.G_RET_STS_SUCCESS ) THEN --batch validation
371 FUN_TRX_PVT.Init_Batch_Validate(
372 p_api_version => 1.0,
373 p_init_msg_list => l_init_msg_list,
374 p_validation_level => nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL),
375 x_return_status => l_return_status,
376 x_msg_count => l_msg_count,
377 x_msg_data => l_msg,
378 p_insert => nvl(p_insert,FND_API.G_TRUE),
379 p_batch_rec => l_batch_rec,
380 p_trx_tbl => l_trx_tbl,
381 p_init_dist_tbl => l_init_dist_tbl,
382 p_dist_lines_tbl => l_dist_lines_tbl
383 );
384 Print('Val and Insert >>>>'||'Batch Validation Complete'|| l_return_status|| 'message'||l_msg);
385
386 /* Insert into Rejections Table with all the reason of error */
387 If (l_return_status = FND_API.G_RET_STS_ERROR) then
388 If (p_calling_sequence = 'Intercompany Import Program') then
389 IF l_msg_count >= 1 THEN
390 FOR i IN 1..l_msg_count
391 LOOP
392 l_msg := FND_MSG_PUB.Get( p_msg_index => l_msg_count,
393 p_encoded => FND_API.G_FALSE);
394 If (insert_rejections(
395 p_batch_id => l_batch_rec.batch_id,
396 p_reject_reason => l_msg
397 ) <> TRUE)
398 then
399 Print('Val and Insert >>>>'||'insert_rejections of invalid batches failure');
400 raise fnd_api.g_exc_unexpected_error;
401 End if; -- Insert Rejections
402 END LOOP; --msg count
403 END IF; -- l_msg_count > 1
404 End if; -- calling sequence
405 End if; -- l_return_status
406
407 END IF; --batch validaion
408
409 /* Set x_return_status according to l_return_status */
410 Set_Return_Status(x_orig_status => x_return_status,
411 p_new_status => l_return_status
412 );
413
414
415 /* If l_return_status is Unexpected - Raise Unexpected Error*/
416
417 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
418 Print ('Val and Insert >>>> '|| 'Unexpected error after batch val');
419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420 END IF;
421
422 -- Flush the l_trx_tbl, l_init_dist_tbl, l_dist_lines_tbl tables
423
424 l_trx_tbl.delete;
425 l_init_dist_tbl.delete;
426 l_dist_lines_tbl.delete;
427
428 Print('Val and Insert >>>>'||'Populating Parameters to be sent to Trx Header Validation API');
429 -- Population the Parameters to be sent with Init_Trx_Validate
430 Print('Val and Insert >>>>'||p_trx_tbl.count);
431 for l_head_count in 1..p_trx_tbl.count
432 LOOP
433 Print('Val and Insert >>>>'||'Populating trx_rec_type');
434 l_trx_rec_type.trx_id := p_trx_tbl(l_head_count).trx_id;
435 l_trx_rec_type.trx_number := p_trx_tbl(l_head_count).trx_number;
436 l_trx_rec_type.Initiator_id := p_trx_tbl(l_head_count).Initiator_id;
437 l_trx_rec_type.recipient_id := p_trx_tbl(l_head_count).recipient_id;
438 l_trx_rec_type.to_le_id := p_trx_tbl(l_head_count).to_le_id;
439 l_trx_rec_type.to_ledger_id := p_trx_tbl(l_head_count).to_ledger_id;
440 l_trx_rec_type.batch_id := p_trx_tbl(l_head_count).batch_id;
441 l_trx_rec_type.status := p_trx_tbl(l_head_count).status;
442 l_trx_rec_type.init_amount_cr := p_trx_tbl(l_head_count).init_amount_cr;
443 l_trx_rec_type.init_amount_dr := p_trx_tbl(l_head_count).init_amount_dr;
444 l_trx_rec_type.reci_amount_cr := p_trx_tbl(l_head_count).reci_amount_cr;
445 l_trx_rec_type.reci_amount_dr := p_trx_tbl(l_head_count).reci_amount_dr;
446 l_trx_rec_type.invoicing_rule := p_trx_tbl(l_head_count).invoice_flag;
447 l_trx_rec_type.approver_id := p_trx_tbl(l_head_count).approver_id;
448 l_trx_rec_type.approval_date := p_trx_tbl(l_head_count).approval_date;
449 l_trx_rec_type.original_trx_id := p_trx_tbl(l_head_count).original_trx_id;
450 l_trx_rec_type.reversed_trx_id := p_trx_tbl(l_head_count).reversed_trx_id;
451 l_trx_rec_type.from_recurring_trx_id := p_trx_tbl(l_head_count).from_recurring_trx_id;
452 l_trx_rec_type.initiator_instance := p_trx_tbl(l_head_count).initiator_instance_flag;
453 l_trx_rec_type.recipient_instance := p_trx_tbl(l_head_count).recipient_instance_flag;
454
455 Print('Val and Insert >>>>'||'Populating Dist_Lines Tbl');
456 If p_dist_lines_tbl is not null then
457 l_count_lines := 1;
458 for l_line_count in 1..p_dist_lines_tbl.count
459 LOOP
460 If (l_trx_rec_type.trx_id = p_dist_lines_tbl(l_line_count).trx_id) then
461
462 l_dist_lines_tbl(l_count_lines).trx_id :=
463 p_dist_lines_tbl(l_line_count).trx_id;
464 l_dist_lines_tbl(l_count_lines).dist_id :=
465 p_dist_lines_tbl(l_line_count).dist_id;
466 l_dist_lines_tbl(l_count_lines).line_id :=
467 p_dist_lines_tbl(l_line_count).line_id;
468 l_dist_lines_tbl(l_count_lines).party_id :=
469 p_dist_lines_tbl(l_line_count).party_id;
470 l_dist_lines_tbl(l_count_lines).party_type :=
471 p_dist_lines_tbl(l_line_count).party_type_flag;
472 l_dist_lines_tbl(l_count_lines).dist_type :=
473 p_dist_lines_tbl(l_line_count).dist_type_flag;
474 l_dist_lines_tbl(l_count_lines).batch_dist_id :=
475 p_dist_lines_tbl(l_line_count).batch_dist_id;
476 l_dist_lines_tbl(l_count_lines).amount_cr :=
477 p_dist_lines_tbl(l_line_count).amount_cr;
478 l_dist_lines_tbl(l_count_lines).amount_dr :=
479 p_dist_lines_tbl(l_line_count).amount_dr;
480 l_dist_lines_tbl(l_count_lines).ccid :=
481 p_dist_lines_tbl(l_line_count).ccid;--Bug 3603338
482 l_count_lines := l_count_lines + 1;
483 End If; -- l_trx_rec_type.trx_id = p_dist_lines_tbl.trx_id
484 END LOOP;
485 End if; -- p_dist_lines_tbl is not null
486
487
488 Print('Val and Insert >>>>'||'Validating the Trx Header');
489 FUN_TRX_PVT.Init_Trx_Validate(
490 p_api_version => 1.0,
491 p_init_msg_list => l_init_msg_list,
492 p_validation_level => nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL),
493 x_return_status => l_return_status,
494 x_msg_count => l_msg_count,
495 x_msg_data => l_msg_data,
496 p_trx_rec => l_trx_rec_type,
497 p_dist_lines_tbl => l_dist_lines_tbl,
498 p_currency_code => l_batch_rec.currency_code,
499 p_gl_date => l_batch_rec.gl_date,
500 p_trx_date => l_batch_rec.batch_date
501 );
502
503 Print('Val and Insert >>>>'||'Transaction Validation Complete');
504 /* Set x_return_status according to l_return_status */
505 Set_Return_Status(x_orig_status => x_return_status,
506 p_new_status => l_return_status);
507
508 Print('Val and Insert >>>>'||'Return Staus from Txn validate '||l_return_status);
509
510 /* If l_return_status is Unexpected - Raise Unexpected Error*/
511
512 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
513
514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
515 END IF;
516
517 If (l_return_status = FND_API.G_RET_STS_ERROR) then
518 If (p_calling_sequence = 'Intercompany Import Program') then
519 Print('Val and Insert >>>>'||'Inserting Trx Header Reject Reasons');
520 UPDATE fun_interface_headers set import_status_code = 'R' Where trx_id = p_trx_tbl(l_head_count).trx_id;
521 -- Insert into Rejections Table
522 IF l_msg_count >= 1 THEN
523 FOR i IN 1..l_msg_count
524 LOOP
525 l_msg := FND_MSG_PUB.Get( p_msg_index => l_msg_count,
526 p_encoded => FND_API.G_FALSE );
527 If (insert_rejections(
528 p_batch_id => p_trx_tbl(l_head_count).batch_id,
529 p_trx_id => p_trx_tbl(l_head_count).trx_id,
530 p_reject_reason => l_msg
531 )<> TRUE)
532 then
533 Print('Val and Insert >>>>'||'insert_rejections of invalid transactions failure');
534 raise fnd_api.g_exc_unexpected_error;
535 End if; -- Insert Rejections
536 END LOOP; --msg count
537 END IF; -- l_msg_count > 1
538 End if; -- Calling Sequence
539
540 ELSE
541 If (P_calling_sequence = 'Intercompany Import Program') then
542 UPDATE fun_interface_headers set import_status_code = 'A'
543 Where trx_id = p_trx_tbl(l_head_count).trx_id;
544 End if; -- Calling Sequence
545
546 END IF; -- return_status
547
548 -- Flush the l_dist_lines_tbl table
549
550 l_dist_lines_tbl.delete;
551
552 END LOOP; --- Next Transaction Record - For l_head_count
553 Print('Val and Insert >>>>'||'Populating Parameters to be sent to Batch Dist Validation API');
554 ---- Populate the Parameters to be sent with Init_Dist_Validate
555
556 If p_init_dist_tbl is not null then
557
558 for l_line_count in 1..p_init_dist_tbl.count
559 LOOP
560 l_init_dist_rec_type.batch_dist_id := p_init_dist_tbl(l_line_count).batch_dist_id;
561 l_init_dist_rec_type.line_number := p_init_dist_tbl(l_line_count).line_number;
562 l_init_dist_rec_type.batch_id := p_init_dist_tbl(l_line_count).batch_id;
563 l_init_dist_rec_type.ccid := p_init_dist_tbl(l_line_count).ccid;
564 l_init_dist_rec_type.amount_cr := p_init_dist_tbl(l_line_count).amount_cr;
565 l_init_dist_rec_type.amount_dr := p_init_dist_tbl(l_line_count).amount_dr;
566
567 --Validation Transaction API's
568 Print('Val and Insert >>>>'||'Validating the Batch Distributions');
569 FUN_TRX_PVT.Init_Dist_Validate(
570 p_api_version => 1.0,
571 p_init_msg_list => l_init_msg_list,
572 p_validation_level => nvl( p_validation_level,FND_API.G_VALID_LEVEL_FULL),
573 x_return_status => l_return_status,
574 p_le_id => p_batch_rec.from_le_id,
575 p_ledger_id => p_batch_rec.from_ledger_id,
576
577 x_msg_count => l_msg_count,
578 x_msg_data => l_msg_data,
579 p_init_dist_rec => l_init_dist_rec_type
580 );
581
582
583 /* Set x_return_status according to l_return_status */
584 Set_Return_Status(x_orig_status => x_return_status,
585 p_new_status => l_return_status);
586 Print('Val and Insert >>>> '||' Status after Init_dist_validate 2'|| l_return_status);
587
588 /* If l_return_status is Unexpected - Raise Unexpected Error*/
589 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
591 END IF;
592
593 Print('Val and Insert >>>> '||' Status after Init_dist_validate 3'|| l_return_status);
594 If (l_return_status = FND_API.G_RET_STS_ERROR) then
595
596 If (P_calling_sequence = 'Intercompany Import Program') then
597 Print('Inserting Batch Distributions Reject Reasons');
598 UPDATE fun_interface_batchdists set import_status_code = 'R'
599 Where batch_dist_id = p_init_dist_tbl(l_line_count).batch_dist_id;
600
601 IF l_msg_count >= 1 THEN
602 FOR i IN 1..l_msg_count
603 LOOP
604 l_msg := FND_MSG_PUB.Get( p_msg_index => l_msg_count,
605 p_encoded => FND_API.G_FALSE );
606 If (insert_rejections(
607 p_batch_id => p_init_dist_tbl(l_line_count).batch_id,
608 p_batch_dist_id => p_init_dist_tbl(l_line_count).batch_dist_id,
609 p_reject_reason => l_msg
610 )<> TRUE) Then
611 Print('insert_rejections of invalid batch dist records failure');
612 raise fnd_api.g_exc_unexpected_error;
613 End if;
614 END LOOP;
615 END IF; -- l_msg_count > 1
616 End If;
617
618 ELSE
619 If (P_calling_sequence = 'Intercompany Import Program') then
620 UPDATE fun_interface_batchdists set import_status_code = 'A'
621 Where batch_dist_id = p_init_dist_tbl(l_line_count).batch_dist_id;
622 End if;
623 END IF;
624
625 END LOOP; -- Next Batch_Dist recor
626 End If; -- p_init_dist_tbl is not null
627
628
629 --- Populate the Parameters to be sent with Init_IC_Dist_Validate
630 Print('Val and Insert >>>>'||'Populating Parameters to be sent to Dist Lines API');
631 If p_dist_lines_tbl is not null then
632
633 for l_line_count in 1..p_dist_lines_tbl.count
634 LOOP
635 l_dist_lines_rec_type.dist_id := p_dist_lines_tbl(l_line_count).dist_id;
636 l_dist_lines_rec_type.line_id := p_dist_lines_tbl(l_line_count).line_id;
637 l_dist_lines_rec_type.party_id := p_dist_lines_tbl(l_line_count).party_id;
638 l_dist_lines_rec_type.party_type := p_dist_lines_tbl(l_line_count).party_type_flag;
639 l_dist_lines_rec_type.dist_type := p_dist_lines_tbl(l_line_count).dist_type_flag;
640 l_dist_lines_rec_type.batch_dist_id := p_dist_lines_tbl(l_line_count).batch_dist_id;
641 l_dist_lines_rec_type.amount_cr := p_dist_lines_tbl(l_line_count).amount_cr;
642 l_dist_lines_rec_type.amount_dr := p_dist_lines_tbl(l_line_count).amount_dr;
643 l_dist_lines_rec_type.ccid := p_dist_lines_tbl(l_line_count).ccid;
644
645 -- 25-10-2007 Changes made by MAKANSAl for Bug # 6527666
646 -- If the distribution line has the party type as 'R' then the recipient
647 -- legal entity id is passed so that the validation for BSV linkage
648 -- is successfully.
649
650 If l_dist_lines_rec_type.party_type = 'R' Then
651
652 --Fectch the recipient Legal Entity Id
653 l_le_party_id := null;
654 l_le_party_id := Fun_Tca_Pkg.Get_Le_Id(l_dist_lines_rec_type.party_id, sysdate);
655
656 For C_Le_Id_Rec In C_Le_Id(l_le_party_id) Loop
657 l_to_le_id := C_Le_Id_Rec.legal_entity_id;
658 End Loop;
659 -- Bug: 7695801
660 select trxh.to_ledger_id
661 into l_to_ledger_id
662 from fun_interface_headers trxH,
663 fun_interface_dist_lines dist
664 where dist.trx_id = trxH.trx_id
665 and dist.dist_id = l_dist_lines_rec_type.dist_id;
666
667 -- Pass Recipient Legal entity Id
668
669 /* Added for Debugging of Bug # 6670702 */
670 Print('Val And Insert Debug >>> ' || l_init_msg_list );
671 Print('Val And Insert Debug >>> ' || nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL));
672 Print('Val And Insert Debug >>> ' || l_to_le_id);
673 Print('Val And Insert Debug >>> ' || l_to_ledger_id);
674 Print('Val And Insert Debug >>> ' || p_batch_rec.from_ledger_id);
675 Print('Val And Insert Debug >>> ' || l_return_status);
676 Print('Val And Insert Debug >>> ' || l_msg_count);
677 Print('Val And Insert Debug >>> ' || l_msg_data);
678 Print('Val And Insert Debug >>> ' || p_batch_rec.from_le_id);
679
680 Fun_Trx_Pvt.Init_IC_Dist_Validate (
681 p_api_version => 1.0,
682 p_init_msg_list => l_init_msg_list,
683 p_validation_level => nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL),
684 p_le_id => l_to_le_id,
685 p_ledger_id => l_to_ledger_id,
686 x_return_status => l_return_status,
687 x_msg_count => l_msg_count,
688 x_msg_data => l_msg_data,
689 p_dist_line_rec => l_dist_lines_rec_type);
690 Else
691
692 -- Changes complete for Bug # 6527666
693
694
695 --Validation Transaction API's
696 Print('Val and Insert >>>>'||'Validating the Dist Lines');
697 FUN_TRX_PVT.Init_IC_Dist_Validate(
698 p_api_version => 1.0,
699 p_init_msg_list => l_init_msg_list,
700 p_validation_level => nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL),
701 p_le_id => p_batch_rec.from_le_id,
702 p_ledger_id => p_batch_rec.from_ledger_id,
703 x_return_status => l_return_status,
704 x_msg_count => l_msg_count,
705 x_msg_data => l_msg_data,
706 p_dist_line_rec => l_dist_lines_rec_type
707 );
708 End If;
709
710 Print('Val and Insert >>>> '||' Status after Init_dist_validate '|| l_return_status);
711
712 /* Set x_return_status according to l_return_status */
713 Set_Return_Status(x_orig_status => x_return_status,
714 p_new_status => l_return_status);
715
716
717 /* If l_return_status is Unexpected - Raise Unexpected Error*/
718 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
720 END IF;
721
722 If (l_return_status = FND_API.G_RET_STS_ERROR) then
723
724 If (P_calling_sequence = 'Intercompany Import Program') then
725 Print('Val and Insert >>>>'||'Inserting Dist Lines Reject Reasons');
726 UPDATE fun_interface_dist_lines set import_status_code = 'R'
727 Where dist_id = p_dist_lines_tbl(l_line_count).dist_id;
728
729 IF l_msg_count >= 1 THEN
730 FOR i IN 1..l_msg_count
731 LOOP
732 l_msg := FND_MSG_PUB.Get( p_msg_index => l_msg_count,
733 p_encoded => FND_API.G_FALSE );
734 If (insert_rejections(
735 p_batch_id =>l_batch_rec.batch_id,
736 p_dist_id => p_dist_lines_tbl(l_line_count).dist_id,
737 p_reject_reason => l_msg
738 )<> TRUE) Then
739 Print('Val and Insert >>>>'||'insert_rejections of invalid dist lines failure');
740 raise fnd_api.g_exc_unexpected_error;
741 End if;
742
743 END LOOP;
744 END IF; -- l_msg_count > 1
745
746 End if; -- p_calling_sequence
747 ELSE
748 If (P_calling_sequence = 'Intercompany Import Program') then
749
750 UPDATE fun_interface_dist_lines set import_status_code = 'A'
751 Where dist_id = p_dist_lines_tbl(l_line_count).dist_id;
752 End If;
753
754 End if; -- l_return_status
755
756 END LOOP; -- Next Disttibution
757
758 End If; -- dist_line_tbl is not null
759
760
761 COMMIT WORK;
762
763 IF p_batch_rec.batch_id is not null then
764 l_batch_id :=p_batch_rec.batch_id;
765 Else
766 Select fun_trx_batches_s.nextval INTO l_batch_id from dual;
767 END IF;
768
769 /* If all Validations pass then the record should be inserted into Fun Tables with status as New */
770 If x_return_Status = FND_API.G_RET_STS_SUCCESS then
771
772 If (nvl(p_insert, FND_API.G_TRUE) = FND_API.G_TRUE) THEN
773 Print ('Populating User Id');
774 l_user := fnd_global.user_id;
775 l_login := fnd_global.login_id;
776 Print('Val and Insert >>>>'||'Inserting into fun_trx_batches Table');
777
778 /* To be uncommented - when doc seq is ready
779
780 If NOT(fun_system_options_pkg.is_manual_numbering ) then
781 -- Generating batch Number
782 Print('Generating Batch Number');
783 fun_seq.get_sequence_number(
784 P_CONTEXT_TYPE => 'DB_INSTANCE',
785 p_context_value => null,
786 P_APPLICATION_ID => 435,
787 P_TABLE_NAME => 'FUN_TRX_BATCHES',
788 P_EVENT_CODE => 'CREATION',
789 p_control_attribute_rec => null,
790 p_control_date_tbl => null,
791 p_suppress_error => null,
792 x_seq_version_id => l_seq_version_id,
793 x_sequence_number => l_sequence_number,
794 x_assignment_id => l_assignment_id,
795 x_error_code => l_error_code
796 );
797
798 If (l_error_code <> 'SUCCESS') then
799 Print('Batch Number Generation errored out');
800 Raise FND_API.G_EXC_ERROR;
801 else
802 l_batch_rec.batch_number := l_sequence_number;
803 End If;
804 End If;
805 To be uncommented - when doc seq is ready
806 */
807
808 -- Insertion into FUN_TRX_BATCHES TABLE
809
810 INSERT into fun_trx_batches(
811 batch_id,
812 batch_number,
813 initiator_id,
814 from_le_id,
815 from_ledger_id,
816 control_total,
817 running_total_cr,
818 running_total_dr,
819 currency_code,
820 exchange_rate_type,
821 status,
822 description,
823 note,
824 trx_type_id,
825 trx_type_code,
826 gl_date,
827 batch_date,
828 reject_allow_flag, -- changed
829 original_batch_id,
830 reversed_batch_id,
831 from_recurring_batch_id,
832 attribute1,
833 attribute2,
834 attribute3,
835 attribute4,
836 attribute5,
837 attribute6,
838 attribute7,
839 attribute8,
840 attribute9,
841 attribute10,
842 attribute11,
843 attribute12,
844 attribute13,
845 attribute14,
846 attribute15,
847 attribute_category,
848 created_by,
849 creation_date,
850 last_updated_by,
851 last_update_date,
852 last_update_login,
853 auto_proration_flag )
854
855 VALUES(
856 l_batch_id,
857 p_batch_rec.batch_number,
858 p_batch_rec.initiator_id,
859 p_batch_rec.from_le_id,
860 p_batch_rec.from_ledger_id,
861 p_batch_rec.control_total,
862 p_batch_rec.running_total_cr,
863 p_batch_rec.running_total_dr,
864 p_batch_rec.currency_code,
865 p_batch_rec. exchange_rate_type,
866 p_batch_rec.status,
867 p_batch_rec.description,
868 p_batch_rec.note,
869 p_batch_rec.trx_type_id,
870 p_batch_rec. trx_type_code,
871 p_batch_rec.gl_date,
872 p_batch_rec.batch_date,
873 p_batch_rec.reject_allow_flag,
874 p_batch_rec.original_batch_id,
875 p_batch_rec.reversed_batch_id,
876 p_batch_rec.from_recurring_batch_id,
877 p_batch_rec.attribute1,
878 p_batch_rec.attribute2,
879 p_batch_rec.attribute3,
880 p_batch_rec.attribute4,
881 p_batch_rec.attribute5,
882 p_batch_rec.attribute6,
883 p_batch_rec.attribute7,
884 p_batch_rec.attribute8,
885 p_batch_rec.attribute9,
886 p_batch_rec.attribute10,
887 p_batch_rec.attribute11,
888 p_batch_rec.attribute12,
889 p_batch_rec.attribute13,
890 p_batch_rec.attribute14,
891 p_batch_rec.attribute15,
892 p_batch_rec.attribute_category,
893 l_user,
894 sysdate,
895 l_user,
896 sysdate,
897 l_login,
898 'N'
899 );
900
901
902 Print('Val and Insert >>>>'||'Inserting into fun_trx_headers Table');
903
904
905 -- Insertion into FUN_TRX_HEADERS and FUN_TRX_LINES TABLE
906 for l_head_count in 1..p_trx_tbl.count
907 LOOP
908
909 /* To be uncommented - when doc seq is ready
910
911 If NOT (fun_system_options_pkg.is_manual_numbering ) then
912 -- Generating Trx Number
913 Print('Val and Insert >>>>>' ||'Generating Trx Number');
914 fun_seq.get_sequence_number(
915 P_CONTEXT_TYPE => 'DB_INSTANCE',
916 p_context_value => null,
917 P_APPLICATION_ID => 435,
918 P_TABLE_NAME => 'FUN_TRX_HEADERS',
919 P_EVENT_CODE => 'CREATION',
920 p_control_attribute_rec => null,
921 p_control_date_tbl => null,
922 p_suppress_error => null,
923 x_seq_version_id => l_seq_version_id,
924 x_sequence_number => l_sequence_number,
925 x_assignment_id => l_assignment_id,
926 x_error_code => l_error_code
927 );
928
929 If (l_error_code <> 'SUCCESS') then
930 Print('Trx Number Generation errored out');
931 Raise FND_API.G_EXC_ERROR;
932 else
933 p_trx_tbl(l_head_count).trx_number := l_sequence_number;
934 End If;
935 End If;
936
937 To be uncommented - when doc seq is ready
938
939 */
940
941 INSERT into fun_trx_headers (
942 trx_id,
943 trx_number,
944 initiator_id,
945 recipient_id,
946 to_le_id,
947 to_ledger_id,
948 batch_id,
949 status,
950 init_amount_cr,
951 init_amount_dr,
952 reci_amount_cr,
953 reci_amount_dr,
954 ar_invoice_number,
955 invoice_flag,
956 approver_id,
957 approval_date,
958 original_trx_id,
959 reversed_trx_id,
960 from_recurring_trx_id,
961 initiator_instance_flag,
962 recipient_instance_flag,
963 description,
964 reject_reason,
965 init_wf_key,
966 reci_wf_key,
967 attribute1,
968 attribute2,
969 attribute3,
970 attribute4,
971 attribute5,
972 attribute6,
973 attribute7,
974 attribute8,
975 attribute9,
976 attribute10,
977 attribute11,
978 attribute12,
979 attribute13,
980 attribute14,
981 attribute15,
982 attribute_category,
983 created_by,
984 creation_date,
985 last_updated_by,
986 last_update_date,
987 last_update_login
988 )
989 VALUES(
990 Fun_trx_headers_s.nextval,
991 p_trx_tbl(l_head_count).trx_number,
992 p_batch_rec.initiator_id,
993 p_trx_tbl(l_head_count).recipient_id,
994 p_trx_tbl(l_head_count).to_le_id,
995 p_trx_tbl(l_head_count).to_ledger_id,
996 l_batch_id,
997 p_trx_tbl(l_head_count).status,
998 p_trx_tbl(l_head_count).init_amount_cr,
999 p_trx_tbl(l_head_count).init_amount_dr,
1000 p_trx_tbl(l_head_count).reci_amount_cr,
1001 p_trx_tbl(l_head_count).reci_amount_dr,
1002 p_trx_tbl(l_head_count).ar_invoice_number,
1003 p_trx_tbl(l_head_count).invoice_flag,
1004 p_trx_tbl(l_head_count).approver_id,
1005 p_trx_tbl(l_head_count).approval_date,
1006 p_trx_tbl(l_head_count).original_trx_id,
1007 p_trx_tbl(l_head_count).reversed_trx_id,
1008 p_trx_tbl(l_head_count).from_recurring_trx_id,
1009 p_trx_tbl(l_head_count).initiator_instance_flag,
1010 p_trx_tbl(l_head_count).recipient_instance_flag,
1011 p_trx_tbl(l_head_count).description,
1012 p_trx_tbl(l_head_count).reject_reason,
1013 p_trx_tbl(l_head_count).init_wf_key,
1014 p_trx_tbl(l_head_count).reci_wf_key,
1015 p_trx_tbl(l_head_count).attribute1,
1016 p_trx_tbl(l_head_count).attribute2,
1017 p_trx_tbl(l_head_count).attribute3,
1018 p_trx_tbl(l_head_count).attribute4,
1019 p_trx_tbl(l_head_count).attribute5,
1020 p_trx_tbl(l_head_count).attribute6,
1021 p_trx_tbl(l_head_count).attribute7,
1022 p_trx_tbl(l_head_count).attribute8,
1023 p_trx_tbl(l_head_count).attribute9,
1024 p_trx_tbl(l_head_count).attribute10,
1025 p_trx_tbl(l_head_count).attribute11,
1026 p_trx_tbl(l_head_count).attribute12,
1027 p_trx_tbl(l_head_count).attribute13,
1028 p_trx_tbl(l_head_count).attribute14,
1029 p_trx_tbl(l_head_count).attribute15,
1030 p_trx_tbl(l_head_count).attribute_category,
1031 l_user,
1032 sysdate,
1033 l_user,
1034 sysdate,
1035 l_login
1036 );
1037
1038 Print('Val and Insert >>>>'||'Inserting into fun_trx_lines Table');
1039
1040 /* To be uncommented - when doc seq is ready
1041
1042 If NOT (fun_system_options_pkg.is_manual_numbering ) then
1043
1044 Print('Val and Insert >>>>>' ||'Generating Trx Lines');
1045 fun_seq.get_sequence_number(
1046 P_CONTEXT_TYPE => 'DB_INSTANCE',
1047 p_context_value => null,
1048 P_APPLICATION_ID => 435,
1049 P_TABLE_NAME => 'FUN_TRX_LINES',
1050 P_EVENT_CODE => 'CREATION',
1051 p_control_attribute_rec => null,
1052 p_control_date_tbl => null,
1053 p_suppress_error => null,
1054 x_seq_version_id => l_seq_version_id,
1055 x_sequence_number => l_sequence_number,
1056 x_assignment_id => l_assignment_id,
1057 x_error_code => l_error_code
1058 );
1059
1060 If (l_error_code <> 'SUCCESS') then
1061 Print('Val and Insert >>>>>' ||'Generating trx line number errored out ');
1062 Raise FND_API.G_EXC_ERROR;
1063
1064 End If;
1065 End If;
1066 To be uncommented - when doc seq is ready
1067 */
1068
1069
1070 INSERT into fun_trx_lines (
1071 line_id,
1072 trx_id,
1073 line_number,
1074 line_type_flag,
1075 init_amount_cr,
1076 init_amount_dr,
1077 reci_amount_cr,
1078 reci_amount_dr,
1079 description,
1080 created_by,
1081 creation_date,
1082 last_updated_by,
1083 last_update_date,
1084 last_update_login
1085 )
1086 VALUES(
1087 Fun_trx_lines_s.nextval,
1088 Fun_trx_headers_s.currval,
1089 --l_sequence_number, commented out untill generation of doc seq
1090 --1234, Taking line_id as line_number instead of hardcoding it. Bug 3603338
1091 Fun_trx_lines_s.currval,
1092 'I',
1093 p_trx_tbl(l_head_count).init_amount_cr,
1094 p_trx_tbl(l_head_count).init_amount_dr,
1095 p_trx_tbl(l_head_count).reci_amount_cr,
1096 p_trx_tbl(l_head_count).reci_amount_dr,
1097 p_trx_tbl(l_head_count).description,
1098 l_user,
1099 sysdate,
1100 l_user,
1101 sysdate,
1102 l_login
1103 );
1104
1105
1106 -- Insertion into FUN_DIST_LINES
1107 Print('Val and Insert >>>>'||'Inserting into fun_trx_dist_lines Table');
1108 If p_dist_lines_tbl is not null then
1109
1110 for l_line_count in 1..p_dist_lines_tbl.count
1111 LOOP
1112 IF p_dist_lines_tbl(l_line_count).trx_id = p_trx_tbl(l_head_count).trx_id THEN
1113 /* To be uncommented - when doc seq is ready
1114
1115 If NOT (fun_system_options_pkg.is_manual_numbering ) then
1116
1117 Print('Val and Insert >>>>>' ||'Generating Trx dist Lines');
1118 fun_seq.get_sequence_number(
1119 P_CONTEXT_TYPE => 'DB_INSTANCE',
1120 p_context_value => null,
1121 P_APPLICATION_ID => 435,
1122 P_TABLE_NAME => 'FUN_DIST_LINES',
1123 P_EVENT_CODE => 'CREATION',
1124 p_control_attribute_rec => null,
1125 p_control_date_tbl => null,
1126 p_suppress_error => null,
1127 x_seq_version_id => l_seq_version_id,
1128 x_sequence_number => l_sequence_number,
1129 x_assignment_id => l_assignment_id,
1130 x_error_code => l_error_code
1131 );
1132
1133 If (l_error_code <> 'SUCCESS') then
1134 Print('Val and Insert >>>>>' ||'Generating trx dist line number errored out ');
1135 Raise FND_API.G_EXC_ERROR;
1136 else
1137 p_dist_lines_tbl(l_line_count).dist_number := l_sequence_number;
1138 End If;
1139 End If;
1140
1141 To be uncommented - when doc seq is ready
1142 */
1143 /*p_dist_lines_tbl(l_line_count).dist_number := 1234; --Bug 3603338 Hardcoded becoz seq num is not ready
1144 Taking dist_id as dist_number instead of hardcoding it.
1145 */
1146 INSERT into fun_dist_lines
1147 (
1148 dist_id,
1149 line_id,
1150 dist_number,
1151 party_id,
1152 party_type_flag,
1153 dist_type_flag,
1154 batch_dist_id,
1155 amount_cr,
1156 amount_dr,
1157 ccid,
1158 description,
1159 auto_generate_flag,
1160 attribute1,
1161 attribute2,
1162 attribute3,
1163 attribute4,
1164 attribute5,
1165 attribute6,
1166 attribute7,
1167 attribute8,
1168 attribute9,
1169 attribute10,
1170 attribute11,
1171 attribute12,
1172 attribute13,
1173 attribute14,
1174 attribute15,
1175 attribute_category,
1176 created_by,
1177 creation_date,
1178 last_updated_by,
1179 last_update_date,
1180 last_update_login,
1181 trx_id
1182 )
1183 VALUES(
1184 Fun_dist_lines_s.nextval,
1185 Fun_trx_lines_s.CURRVAL,
1186 Fun_dist_lines_s.currval, --Bug 3603338
1187 p_dist_lines_tbl(l_line_count).party_id,
1188 p_dist_lines_tbl(l_line_count).party_type_flag,
1189 p_dist_lines_tbl(l_line_count).dist_type_flag,
1190 p_dist_lines_tbl(l_line_count).batch_dist_id,
1191 p_dist_lines_tbl(l_line_count).amount_cr,
1192 p_dist_lines_tbl(l_line_count).amount_dr,
1193 p_dist_lines_tbl(l_line_count).ccid,
1194 p_dist_lines_tbl(l_line_count).description,
1195 --p_dist_lines_tbl(l_line_count).auto_generate_flag, is not avl anywhere for now hardcoding to N Bug 3603338
1196 'N',
1197 p_dist_lines_tbl(l_line_count).attribute1,
1198 p_dist_lines_tbl(l_line_count).attribute2,
1199 p_dist_lines_tbl(l_line_count).attribute3,
1200 p_dist_lines_tbl(l_line_count).attribute4,
1201 p_dist_lines_tbl(l_line_count).attribute5,
1202 p_dist_lines_tbl(l_line_count).attribute6,
1203 p_dist_lines_tbl(l_line_count).attribute7,
1204 p_dist_lines_tbl(l_line_count).attribute8,
1205 p_dist_lines_tbl(l_line_count).attribute9,
1206 p_dist_lines_tbl(l_line_count).attribute10,
1207 p_dist_lines_tbl(l_line_count).attribute11,
1208 p_dist_lines_tbl(l_line_count).attribute12,
1209 p_dist_lines_tbl(l_line_count).attribute13,
1210 p_dist_lines_tbl(l_line_count).attribute14,
1211 p_dist_lines_tbl(l_line_count).attribute15,
1212 p_dist_lines_tbl(l_line_count).attribute_category,
1213 l_user,
1214 sysdate,
1215 l_user,
1216 sysdate,
1217 l_login,
1218 Fun_trx_headers_s.currval
1219 );
1220 END IF; --p_dist_lines_tbl.trx_id = p_trx_tbl.trx_id
1221
1222 END LOOP; -- l_line_count
1223 End If; -- p_ dist_line_tbl not null
1224
1225 /* -- Send Batch not individual transactions
1226 --raise Workflow event if p_sent='Y'
1227 IF p_sent = 'Y' THEN
1228 Print('Val and Insert >>>>'||'Raise Business Event');
1229 select FUN_TRX_BATCHES_S.CURRVAL into l_batch_id from dual;
1230 select FUN_TRX_HEADERS_S.CURRVAL into l_trx_id from dual;
1231 l_wfkey := fun_initiator_wf_pkg.generate_key(l_batch_id, l_trx_id);
1232 fun_wf_common.raise_wf_bus_event(l_batch_id, l_trx_id, l_wfkey, 'oracle.apps.fun.manualtrx.batch.send');
1233 END IF;
1234 */
1235
1236 END LOOP; --l_head_count
1237
1238 Print('Val and Insert >>>>'||'Inserting into fun_trx_batch_dist Table');
1239 -- Insertion into FUN_BATCH_DISTS
1240 If p_init_dist_tbl is not null then
1241 for l_line_count in 1..p_init_dist_tbl.count
1242 LOOP
1243
1244 INSERT into fun_batch_dists (
1245 batch_dist_id,
1246 line_number,
1247 batch_id,
1248 ccid,
1249 amount_cr,
1250 amount_dr,
1251 description,
1252 created_by,
1253 creation_date,
1254 last_updated_by,
1255 last_update_date,
1256 last_update_login
1257 )
1258 VALUES
1259 (
1260 fun_batch_dist_s.nextval,
1261 p_init_dist_tbl(l_line_count).line_number,
1262 l_batch_id,
1263 p_init_dist_tbl(l_line_count).ccid,
1264 p_init_dist_tbl(l_line_count).amount_cr,
1265 p_init_dist_tbl(l_line_count).amount_dr,
1266 p_init_dist_tbl(l_line_count).description,
1267 l_user,
1268 sysdate,
1269 l_user,
1270 sysdate,
1271 l_login
1272 );
1273 END LOOP; -- l_line_count
1274 End If; -- p_init_dist_tbl not null
1275
1276 IF p_sent = 'Y' THEN
1277 Print('Val and Insert >>>>'||'Raise Business Event');
1278
1279 UPDATE fun_trx_batches
1280 SET status = 'SENT'
1281 WHERE batch_id = l_batch_id;
1282
1283 UPDATE fun_trx_headers
1284 SET status = 'SENT'
1285 WHERE batch_id = l_batch_id;
1286
1287 l_wfkey := fun_initiator_wf_pkg.generate_key(l_batch_id, NULL);
1288
1289 fun_wf_common.raise_wf_bus_event(l_batch_id,
1290 NULL,
1291 l_wfkey,
1292 'oracle.apps.fun.manualtrx.batch.send');
1293 END IF;-- p_sent ='Y'
1294
1295 End if; -- p_insert true
1296 End If; -- Overall Status
1297
1298 IF FND_API.To_Boolean( nvl(p_commit,FND_API.G_FALSE) ) THEN
1299 COMMIT WORK;
1300 END IF;
1301
1302 -- Standard call to get message count and if count is 1, get message info.
1303 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1304 p_data => x_msg_data);
1305 Print('Val and Insert >>>>'||'End of the API');
1306
1307 EXCEPTION
1308
1309 WHEN FND_API.G_EXC_ERROR THEN
1310 Print('g_exc_error');
1311 p_batch_rec := l_old_batch_rec;
1312 p_trx_tbl := l_old_trx_tbl;
1313 p_init_dist_tbl := l_old_init_dist_tbl;
1314 p_dist_lines_tbl := l_old_dist_lines_tbl;
1315 ROLLBACK TO Fun_Trx_Val_And_Insert_PUB;
1316 x_return_status := FND_API.G_RET_STS_ERROR ;
1317 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1318 p_data => x_msg_data);
1319 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1320 Print('Val and Insert >>>>'||'Unexpected error occurred -'||SQLERRM);
1321 p_batch_rec := l_old_batch_rec;
1322 p_trx_tbl := l_old_trx_tbl;
1323 p_init_dist_tbl := l_old_init_dist_tbl;
1324 p_dist_lines_tbl := l_old_dist_lines_tbl;
1325
1326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1327 Print('****************************************');
1328 Print('Val and Insert >>>>'||'Details of Error');
1329 Print('****************************************');
1330 IF x_msg_count > 1 THEN
1331 FOR i IN 1..x_msg_count
1332 LOOP
1333 Print(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ));
1334 END LOOP;
1335 ELSE
1336 Print(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ));
1337 END IF;
1338 ROLLBACK TO Fun_Trx_Val_And_Insert_PUB;
1339
1340 WHEN OTHERS THEN
1341 Print('Val and Insert >>>>'||'When Other');
1342 p_batch_rec := l_old_batch_rec;
1343 p_trx_tbl := l_old_trx_tbl;
1344 p_init_dist_tbl := l_old_init_dist_tbl;
1345 p_dist_lines_tbl := l_old_dist_lines_tbl;
1346 ROLLBACK TO Fun_Trx_Val_And_Insert_PUB;
1347 Print('Val and Insert >>>>'||'Exception others- '||SQLERRM);
1348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1349 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1350 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1351 END IF;
1352 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1353 p_data => x_msg_data);
1354
1355
1356 END CREATE_BATCH; -- Procedure
1357
1358
1359
1360 END FUN_TRX_PUB; -- Package Body
1361