[Home] [Help]
PACKAGE BODY: APPS.FUN_TRX_PUB
Source
1 PACKAGE BODY FUN_TRX_PUB AS
2 /* $Header: funtrxvalinsb.pls 120.26 2011/12/16 02:20:57 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 p_exchange_rate_type => l_batch_rec.exchange_rate_type
502 );
503
504 Print('Val and Insert >>>>'||'Transaction Validation Complete');
505 /* Set x_return_status according to l_return_status */
506 Set_Return_Status(x_orig_status => x_return_status,
507 p_new_status => l_return_status);
508
509 Print('Val and Insert >>>>'||'Return Staus from Txn validate '||l_return_status);
510
511 /* If l_return_status is Unexpected - Raise Unexpected Error*/
512
513 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
514
515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
516 END IF;
517
518 If (l_return_status = FND_API.G_RET_STS_ERROR) then
519 If (p_calling_sequence = 'Intercompany Import Program') then
520 Print('Val and Insert >>>>'||'Inserting Trx Header Reject Reasons');
521 UPDATE fun_interface_headers set import_status_code = 'R' Where trx_id = p_trx_tbl(l_head_count).trx_id;
522 -- Insert into Rejections Table
523 IF l_msg_count >= 1 THEN
524 FOR i IN 1..l_msg_count
525 LOOP
526 l_msg := FND_MSG_PUB.Get( p_msg_index => l_msg_count,
527 p_encoded => FND_API.G_FALSE );
528 If (insert_rejections(
529 p_batch_id => p_trx_tbl(l_head_count).batch_id,
530 p_trx_id => p_trx_tbl(l_head_count).trx_id,
531 p_reject_reason => l_msg
532 )<> TRUE)
533 then
534 Print('Val and Insert >>>>'||'insert_rejections of invalid transactions failure');
535 raise fnd_api.g_exc_unexpected_error;
536 End if; -- Insert Rejections
537 END LOOP; --msg count
538 END IF; -- l_msg_count > 1
539 End if; -- Calling Sequence
540
541 ELSE
542 If (P_calling_sequence = 'Intercompany Import Program') then
543 UPDATE fun_interface_headers set import_status_code = 'A'
544 Where trx_id = p_trx_tbl(l_head_count).trx_id;
545 End if; -- Calling Sequence
546
547 END IF; -- return_status
548
549 -- Flush the l_dist_lines_tbl table
550
551 l_dist_lines_tbl.delete;
552
553 END LOOP; --- Next Transaction Record - For l_head_count
554 Print('Val and Insert >>>>'||'Populating Parameters to be sent to Batch Dist Validation API');
555 ---- Populate the Parameters to be sent with Init_Dist_Validate
556
557 If p_init_dist_tbl is not null then
558
559 for l_line_count in 1..p_init_dist_tbl.count
560 LOOP
561 l_init_dist_rec_type.batch_dist_id := p_init_dist_tbl(l_line_count).batch_dist_id;
562 l_init_dist_rec_type.line_number := p_init_dist_tbl(l_line_count).line_number;
563 l_init_dist_rec_type.batch_id := p_init_dist_tbl(l_line_count).batch_id;
564 l_init_dist_rec_type.ccid := p_init_dist_tbl(l_line_count).ccid;
565 l_init_dist_rec_type.amount_cr := p_init_dist_tbl(l_line_count).amount_cr;
566 l_init_dist_rec_type.amount_dr := p_init_dist_tbl(l_line_count).amount_dr;
567
568 --Validation Transaction API's
569 Print('Val and Insert >>>>'||'Validating the Batch Distributions');
570 FUN_TRX_PVT.Init_Dist_Validate(
571 p_api_version => 1.0,
572 p_init_msg_list => l_init_msg_list,
573 p_validation_level => nvl( p_validation_level,FND_API.G_VALID_LEVEL_FULL),
574 x_return_status => l_return_status,
575 p_le_id => p_batch_rec.from_le_id,
576 p_ledger_id => p_batch_rec.from_ledger_id,
577
578 x_msg_count => l_msg_count,
579 x_msg_data => l_msg_data,
580 p_init_dist_rec => l_init_dist_rec_type
581 );
582
583
584 /* Set x_return_status according to l_return_status */
585 Set_Return_Status(x_orig_status => x_return_status,
586 p_new_status => l_return_status);
587 Print('Val and Insert >>>> '||' Status after Init_dist_validate 2'|| l_return_status);
588
589 /* If l_return_status is Unexpected - Raise Unexpected Error*/
590 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
592 END IF;
593
594 Print('Val and Insert >>>> '||' Status after Init_dist_validate 3'|| l_return_status);
595 If (l_return_status = FND_API.G_RET_STS_ERROR) then
596
597 If (P_calling_sequence = 'Intercompany Import Program') then
598 Print('Inserting Batch Distributions Reject Reasons');
599 UPDATE fun_interface_batchdists set import_status_code = 'R'
600 Where batch_dist_id = p_init_dist_tbl(l_line_count).batch_dist_id;
601
602 IF l_msg_count >= 1 THEN
603 FOR i IN 1..l_msg_count
604 LOOP
605 l_msg := FND_MSG_PUB.Get( p_msg_index => l_msg_count,
606 p_encoded => FND_API.G_FALSE );
607 If (insert_rejections(
608 p_batch_id => p_init_dist_tbl(l_line_count).batch_id,
609 p_batch_dist_id => p_init_dist_tbl(l_line_count).batch_dist_id,
610 p_reject_reason => l_msg
611 )<> TRUE) Then
612 Print('insert_rejections of invalid batch dist records failure');
613 raise fnd_api.g_exc_unexpected_error;
614 End if;
615 END LOOP;
616 END IF; -- l_msg_count > 1
617 End If;
618
619 ELSE
620 If (P_calling_sequence = 'Intercompany Import Program') then
621 UPDATE fun_interface_batchdists set import_status_code = 'A'
622 Where batch_dist_id = p_init_dist_tbl(l_line_count).batch_dist_id;
623 End if;
624 END IF;
625
626 END LOOP; -- Next Batch_Dist recor
627 End If; -- p_init_dist_tbl is not null
628
629
630 --- Populate the Parameters to be sent with Init_IC_Dist_Validate
631 Print('Val and Insert >>>>'||'Populating Parameters to be sent to Dist Lines API');
632 If p_dist_lines_tbl is not null then
633
634 for l_line_count in 1..p_dist_lines_tbl.count
635 LOOP
636 l_dist_lines_rec_type.dist_id := p_dist_lines_tbl(l_line_count).dist_id;
637 l_dist_lines_rec_type.line_id := p_dist_lines_tbl(l_line_count).line_id;
638 l_dist_lines_rec_type.party_id := p_dist_lines_tbl(l_line_count).party_id;
639 l_dist_lines_rec_type.party_type := p_dist_lines_tbl(l_line_count).party_type_flag;
640 l_dist_lines_rec_type.dist_type := p_dist_lines_tbl(l_line_count).dist_type_flag;
641 l_dist_lines_rec_type.batch_dist_id := p_dist_lines_tbl(l_line_count).batch_dist_id;
642 l_dist_lines_rec_type.amount_cr := p_dist_lines_tbl(l_line_count).amount_cr;
643 l_dist_lines_rec_type.amount_dr := p_dist_lines_tbl(l_line_count).amount_dr;
644 l_dist_lines_rec_type.ccid := p_dist_lines_tbl(l_line_count).ccid;
645
646 -- 25-10-2007 Changes made by MAKANSAl for Bug # 6527666
647 -- If the distribution line has the party type as 'R' then the recipient
648 -- legal entity id is passed so that the validation for BSV linkage
649 -- is successfully.
650
651 If l_dist_lines_rec_type.party_type = 'R' Then
652
653 --Fectch the recipient Legal Entity Id
654 l_le_party_id := null;
655 l_le_party_id := Fun_Tca_Pkg.Get_Le_Id(l_dist_lines_rec_type.party_id, sysdate);
656
657 For C_Le_Id_Rec In C_Le_Id(l_le_party_id) Loop
658 l_to_le_id := C_Le_Id_Rec.legal_entity_id;
659 End Loop;
660 -- Bug: 7695801
661 select trxh.to_ledger_id
662 into l_to_ledger_id
663 from fun_interface_headers trxH,
664 fun_interface_dist_lines dist
665 where dist.trx_id = trxH.trx_id
666 and dist.dist_id = l_dist_lines_rec_type.dist_id;
667
668 -- Pass Recipient Legal entity Id
669
670 /* Added for Debugging of Bug # 6670702 */
671 Print('Val And Insert Debug >>> ' || l_init_msg_list );
672 Print('Val And Insert Debug >>> ' || nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL));
673 Print('Val And Insert Debug >>> ' || l_to_le_id);
674 Print('Val And Insert Debug >>> ' || l_to_ledger_id);
675 Print('Val And Insert Debug >>> ' || p_batch_rec.from_ledger_id);
676 Print('Val And Insert Debug >>> ' || l_return_status);
677 Print('Val And Insert Debug >>> ' || l_msg_count);
678 Print('Val And Insert Debug >>> ' || l_msg_data);
679 Print('Val And Insert Debug >>> ' || p_batch_rec.from_le_id);
680
681 Fun_Trx_Pvt.Init_IC_Dist_Validate (
682 p_api_version => 1.0,
683 p_init_msg_list => l_init_msg_list,
684 p_validation_level => nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL),
685 p_le_id => l_to_le_id,
686 p_ledger_id => l_to_ledger_id,
687 x_return_status => l_return_status,
688 x_msg_count => l_msg_count,
689 x_msg_data => l_msg_data,
690 p_dist_line_rec => l_dist_lines_rec_type);
691 Else
692
693 -- Changes complete for Bug # 6527666
694
695
696 --Validation Transaction API's
697 Print('Val and Insert >>>>'||'Validating the Dist Lines');
698 FUN_TRX_PVT.Init_IC_Dist_Validate(
699 p_api_version => 1.0,
700 p_init_msg_list => l_init_msg_list,
701 p_validation_level => nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL),
702 p_le_id => p_batch_rec.from_le_id,
703 p_ledger_id => p_batch_rec.from_ledger_id,
704 x_return_status => l_return_status,
705 x_msg_count => l_msg_count,
706 x_msg_data => l_msg_data,
707 p_dist_line_rec => l_dist_lines_rec_type
708 );
709 End If;
710
711 Print('Val and Insert >>>> '||' Status after Init_dist_validate '|| l_return_status);
712
713 /* Set x_return_status according to l_return_status */
714 Set_Return_Status(x_orig_status => x_return_status,
715 p_new_status => l_return_status);
716
717
718 /* If l_return_status is Unexpected - Raise Unexpected Error*/
719 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
720 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
721 END IF;
722
723 If (l_return_status = FND_API.G_RET_STS_ERROR) then
724
725 If (P_calling_sequence = 'Intercompany Import Program') then
726 Print('Val and Insert >>>>'||'Inserting Dist Lines Reject Reasons');
727 UPDATE fun_interface_dist_lines set import_status_code = 'R'
728 Where dist_id = p_dist_lines_tbl(l_line_count).dist_id;
729
730 IF l_msg_count >= 1 THEN
731 FOR i IN 1..l_msg_count
732 LOOP
733 l_msg := FND_MSG_PUB.Get( p_msg_index => l_msg_count,
734 p_encoded => FND_API.G_FALSE );
735 If (insert_rejections(
736 p_batch_id =>l_batch_rec.batch_id,
737 p_dist_id => p_dist_lines_tbl(l_line_count).dist_id,
738 p_reject_reason => l_msg
739 )<> TRUE) Then
740 Print('Val and Insert >>>>'||'insert_rejections of invalid dist lines failure');
741 raise fnd_api.g_exc_unexpected_error;
742 End if;
743
744 END LOOP;
745 END IF; -- l_msg_count > 1
746
747 End if; -- p_calling_sequence
748 ELSE
749 If (P_calling_sequence = 'Intercompany Import Program') then
750
751 UPDATE fun_interface_dist_lines set import_status_code = 'A'
752 Where dist_id = p_dist_lines_tbl(l_line_count).dist_id;
753 End If;
754
755 End if; -- l_return_status
756
757 END LOOP; -- Next Disttibution
758
759 End If; -- dist_line_tbl is not null
760
761
762 COMMIT WORK;
763
764 IF p_batch_rec.batch_id is not null then
765 l_batch_id :=p_batch_rec.batch_id;
766 Else
767 Select fun_trx_batches_s.nextval INTO l_batch_id from dual;
768 END IF;
769
770 /* If all Validations pass then the record should be inserted into Fun Tables with status as New */
771 If x_return_Status = FND_API.G_RET_STS_SUCCESS then
772
773 If (nvl(p_insert, FND_API.G_TRUE) = FND_API.G_TRUE) THEN
774 Print ('Populating User Id');
775 l_user := fnd_global.user_id;
776 l_login := fnd_global.login_id;
777 Print('Val and Insert >>>>'||'Inserting into fun_trx_batches Table');
778
779 /* To be uncommented - when doc seq is ready
780
781 If NOT(fun_system_options_pkg.is_manual_numbering ) then
782 -- Generating batch Number
783 Print('Generating Batch Number');
784 fun_seq.get_sequence_number(
785 P_CONTEXT_TYPE => 'DB_INSTANCE',
786 p_context_value => null,
787 P_APPLICATION_ID => 435,
788 P_TABLE_NAME => 'FUN_TRX_BATCHES',
789 P_EVENT_CODE => 'CREATION',
790 p_control_attribute_rec => null,
791 p_control_date_tbl => null,
792 p_suppress_error => null,
793 x_seq_version_id => l_seq_version_id,
794 x_sequence_number => l_sequence_number,
795 x_assignment_id => l_assignment_id,
796 x_error_code => l_error_code
797 );
798
799 If (l_error_code <> 'SUCCESS') then
800 Print('Batch Number Generation errored out');
801 Raise FND_API.G_EXC_ERROR;
802 else
803 l_batch_rec.batch_number := l_sequence_number;
804 End If;
805 End If;
806 To be uncommented - when doc seq is ready
807 */
808
809 -- Insertion into FUN_TRX_BATCHES TABLE
810
811 INSERT into fun_trx_batches(
812 batch_id,
813 batch_number,
814 initiator_id,
815 from_le_id,
816 from_ledger_id,
817 control_total,
818 running_total_cr,
819 running_total_dr,
820 currency_code,
821 exchange_rate_type,
822 status,
823 description,
824 note,
825 trx_type_id,
826 trx_type_code,
827 gl_date,
828 batch_date,
829 reject_allow_flag, -- changed
830 original_batch_id,
831 reversed_batch_id,
832 from_recurring_batch_id,
833 attribute1,
834 attribute2,
835 attribute3,
836 attribute4,
837 attribute5,
838 attribute6,
839 attribute7,
840 attribute8,
841 attribute9,
842 attribute10,
843 attribute11,
844 attribute12,
845 attribute13,
846 attribute14,
847 attribute15,
848 attribute_category,
849 created_by,
850 creation_date,
851 last_updated_by,
852 last_update_date,
853 last_update_login,
854 auto_proration_flag )
855
856 VALUES(
857 l_batch_id,
858 p_batch_rec.batch_number,
859 p_batch_rec.initiator_id,
860 p_batch_rec.from_le_id,
861 p_batch_rec.from_ledger_id,
862 p_batch_rec.control_total,
863 p_batch_rec.running_total_cr,
864 p_batch_rec.running_total_dr,
865 p_batch_rec.currency_code,
866 p_batch_rec. exchange_rate_type,
867 p_batch_rec.status,
868 p_batch_rec.description,
869 p_batch_rec.note,
870 p_batch_rec.trx_type_id,
871 p_batch_rec. trx_type_code,
872 p_batch_rec.gl_date,
873 p_batch_rec.batch_date,
874 p_batch_rec.reject_allow_flag,
875 p_batch_rec.original_batch_id,
876 p_batch_rec.reversed_batch_id,
877 p_batch_rec.from_recurring_batch_id,
878 p_batch_rec.attribute1,
879 p_batch_rec.attribute2,
880 p_batch_rec.attribute3,
881 p_batch_rec.attribute4,
882 p_batch_rec.attribute5,
883 p_batch_rec.attribute6,
884 p_batch_rec.attribute7,
885 p_batch_rec.attribute8,
886 p_batch_rec.attribute9,
887 p_batch_rec.attribute10,
888 p_batch_rec.attribute11,
889 p_batch_rec.attribute12,
890 p_batch_rec.attribute13,
891 p_batch_rec.attribute14,
892 p_batch_rec.attribute15,
893 p_batch_rec.attribute_category,
894 l_user,
895 sysdate,
896 l_user,
897 sysdate,
898 l_login,
899 'N'
900 );
901
902
903 Print('Val and Insert >>>>'||'Inserting into fun_trx_headers Table');
904
905
906 -- Insertion into FUN_TRX_HEADERS and FUN_TRX_LINES TABLE
907 for l_head_count in 1..p_trx_tbl.count
908 LOOP
909
910 /* To be uncommented - when doc seq is ready
911
912 If NOT (fun_system_options_pkg.is_manual_numbering ) then
913 -- Generating Trx Number
914 Print('Val and Insert >>>>>' ||'Generating Trx Number');
915 fun_seq.get_sequence_number(
916 P_CONTEXT_TYPE => 'DB_INSTANCE',
917 p_context_value => null,
918 P_APPLICATION_ID => 435,
919 P_TABLE_NAME => 'FUN_TRX_HEADERS',
920 P_EVENT_CODE => 'CREATION',
921 p_control_attribute_rec => null,
922 p_control_date_tbl => null,
923 p_suppress_error => null,
924 x_seq_version_id => l_seq_version_id,
925 x_sequence_number => l_sequence_number,
926 x_assignment_id => l_assignment_id,
927 x_error_code => l_error_code
928 );
929
930 If (l_error_code <> 'SUCCESS') then
931 Print('Trx Number Generation errored out');
932 Raise FND_API.G_EXC_ERROR;
933 else
934 p_trx_tbl(l_head_count).trx_number := l_sequence_number;
935 End If;
936 End If;
937
938 To be uncommented - when doc seq is ready
939
940 */
941
942 INSERT into fun_trx_headers (
943 trx_id,
944 trx_number,
945 initiator_id,
946 recipient_id,
947 to_le_id,
948 to_ledger_id,
949 batch_id,
950 status,
951 init_amount_cr,
952 init_amount_dr,
953 reci_amount_cr,
954 reci_amount_dr,
955 ar_invoice_number,
956 invoice_flag,
957 approver_id,
958 approval_date,
959 original_trx_id,
960 reversed_trx_id,
961 from_recurring_trx_id,
962 initiator_instance_flag,
963 recipient_instance_flag,
964 description,
965 reject_reason,
966 init_wf_key,
967 reci_wf_key,
968 attribute1,
969 attribute2,
970 attribute3,
971 attribute4,
972 attribute5,
973 attribute6,
974 attribute7,
975 attribute8,
976 attribute9,
977 attribute10,
978 attribute11,
979 attribute12,
980 attribute13,
981 attribute14,
982 attribute15,
983 attribute_category,
984 created_by,
985 creation_date,
986 last_updated_by,
987 last_update_date,
988 last_update_login
989 )
990 VALUES(
991 Fun_trx_headers_s.nextval,
992 p_trx_tbl(l_head_count).trx_number,
993 p_batch_rec.initiator_id,
994 p_trx_tbl(l_head_count).recipient_id,
995 p_trx_tbl(l_head_count).to_le_id,
996 p_trx_tbl(l_head_count).to_ledger_id,
997 l_batch_id,
998 p_trx_tbl(l_head_count).status,
999 p_trx_tbl(l_head_count).init_amount_cr,
1000 p_trx_tbl(l_head_count).init_amount_dr,
1001 p_trx_tbl(l_head_count).reci_amount_cr,
1002 p_trx_tbl(l_head_count).reci_amount_dr,
1003 p_trx_tbl(l_head_count).ar_invoice_number,
1004 p_trx_tbl(l_head_count).invoice_flag,
1005 p_trx_tbl(l_head_count).approver_id,
1006 p_trx_tbl(l_head_count).approval_date,
1007 p_trx_tbl(l_head_count).original_trx_id,
1008 p_trx_tbl(l_head_count).reversed_trx_id,
1009 p_trx_tbl(l_head_count).from_recurring_trx_id,
1010 p_trx_tbl(l_head_count).initiator_instance_flag,
1011 p_trx_tbl(l_head_count).recipient_instance_flag,
1012 p_trx_tbl(l_head_count).description,
1013 p_trx_tbl(l_head_count).reject_reason,
1014 p_trx_tbl(l_head_count).init_wf_key,
1015 p_trx_tbl(l_head_count).reci_wf_key,
1016 p_trx_tbl(l_head_count).attribute1,
1017 p_trx_tbl(l_head_count).attribute2,
1018 p_trx_tbl(l_head_count).attribute3,
1019 p_trx_tbl(l_head_count).attribute4,
1020 p_trx_tbl(l_head_count).attribute5,
1021 p_trx_tbl(l_head_count).attribute6,
1022 p_trx_tbl(l_head_count).attribute7,
1023 p_trx_tbl(l_head_count).attribute8,
1024 p_trx_tbl(l_head_count).attribute9,
1025 p_trx_tbl(l_head_count).attribute10,
1026 p_trx_tbl(l_head_count).attribute11,
1027 p_trx_tbl(l_head_count).attribute12,
1028 p_trx_tbl(l_head_count).attribute13,
1029 p_trx_tbl(l_head_count).attribute14,
1030 p_trx_tbl(l_head_count).attribute15,
1031 p_trx_tbl(l_head_count).attribute_category,
1032 l_user,
1033 sysdate,
1034 l_user,
1035 sysdate,
1036 l_login
1037 );
1038
1039 Print('Val and Insert >>>>'||'Inserting into fun_trx_lines Table');
1040
1041 /* To be uncommented - when doc seq is ready
1042
1043 If NOT (fun_system_options_pkg.is_manual_numbering ) then
1044
1045 Print('Val and Insert >>>>>' ||'Generating Trx Lines');
1046 fun_seq.get_sequence_number(
1047 P_CONTEXT_TYPE => 'DB_INSTANCE',
1048 p_context_value => null,
1049 P_APPLICATION_ID => 435,
1050 P_TABLE_NAME => 'FUN_TRX_LINES',
1051 P_EVENT_CODE => 'CREATION',
1052 p_control_attribute_rec => null,
1053 p_control_date_tbl => null,
1054 p_suppress_error => null,
1055 x_seq_version_id => l_seq_version_id,
1056 x_sequence_number => l_sequence_number,
1057 x_assignment_id => l_assignment_id,
1058 x_error_code => l_error_code
1059 );
1060
1061 If (l_error_code <> 'SUCCESS') then
1062 Print('Val and Insert >>>>>' ||'Generating trx line number errored out ');
1063 Raise FND_API.G_EXC_ERROR;
1064
1065 End If;
1066 End If;
1067 To be uncommented - when doc seq is ready
1068 */
1069
1070
1071 INSERT into fun_trx_lines (
1072 line_id,
1073 trx_id,
1074 line_number,
1075 line_type_flag,
1076 init_amount_cr,
1077 init_amount_dr,
1078 reci_amount_cr,
1079 reci_amount_dr,
1080 description,
1081 created_by,
1082 creation_date,
1083 last_updated_by,
1084 last_update_date,
1085 last_update_login
1086 )
1087 VALUES(
1088 Fun_trx_lines_s.nextval,
1089 Fun_trx_headers_s.currval,
1090 --l_sequence_number, commented out untill generation of doc seq
1091 --1234, Taking line_id as line_number instead of hardcoding it. Bug 3603338
1092 Fun_trx_lines_s.currval,
1093 'I',
1094 p_trx_tbl(l_head_count).init_amount_cr,
1095 p_trx_tbl(l_head_count).init_amount_dr,
1096 p_trx_tbl(l_head_count).reci_amount_cr,
1097 p_trx_tbl(l_head_count).reci_amount_dr,
1098 p_trx_tbl(l_head_count).description,
1099 l_user,
1100 sysdate,
1101 l_user,
1102 sysdate,
1103 l_login
1104 );
1105
1106
1107 -- Insertion into FUN_DIST_LINES
1108 Print('Val and Insert >>>>'||'Inserting into fun_trx_dist_lines Table');
1109 If p_dist_lines_tbl is not null then
1110
1111 for l_line_count in 1..p_dist_lines_tbl.count
1112 LOOP
1113 IF p_dist_lines_tbl(l_line_count).trx_id = p_trx_tbl(l_head_count).trx_id THEN
1114 /* To be uncommented - when doc seq is ready
1115
1116 If NOT (fun_system_options_pkg.is_manual_numbering ) then
1117
1118 Print('Val and Insert >>>>>' ||'Generating Trx dist Lines');
1119 fun_seq.get_sequence_number(
1120 P_CONTEXT_TYPE => 'DB_INSTANCE',
1121 p_context_value => null,
1122 P_APPLICATION_ID => 435,
1123 P_TABLE_NAME => 'FUN_DIST_LINES',
1124 P_EVENT_CODE => 'CREATION',
1125 p_control_attribute_rec => null,
1126 p_control_date_tbl => null,
1127 p_suppress_error => null,
1128 x_seq_version_id => l_seq_version_id,
1129 x_sequence_number => l_sequence_number,
1130 x_assignment_id => l_assignment_id,
1131 x_error_code => l_error_code
1132 );
1133
1134 If (l_error_code <> 'SUCCESS') then
1135 Print('Val and Insert >>>>>' ||'Generating trx dist line number errored out ');
1136 Raise FND_API.G_EXC_ERROR;
1137 else
1138 p_dist_lines_tbl(l_line_count).dist_number := l_sequence_number;
1139 End If;
1140 End If;
1141
1142 To be uncommented - when doc seq is ready
1143 */
1144 /*p_dist_lines_tbl(l_line_count).dist_number := 1234; --Bug 3603338 Hardcoded becoz seq num is not ready
1145 Taking dist_id as dist_number instead of hardcoding it.
1146 */
1147 INSERT into fun_dist_lines
1148 (
1149 dist_id,
1150 line_id,
1151 dist_number,
1152 party_id,
1153 party_type_flag,
1154 dist_type_flag,
1155 batch_dist_id,
1156 amount_cr,
1157 amount_dr,
1158 ccid,
1159 description,
1160 auto_generate_flag,
1161 attribute1,
1162 attribute2,
1163 attribute3,
1164 attribute4,
1165 attribute5,
1166 attribute6,
1167 attribute7,
1168 attribute8,
1169 attribute9,
1170 attribute10,
1171 attribute11,
1172 attribute12,
1173 attribute13,
1174 attribute14,
1175 attribute15,
1176 attribute_category,
1177 created_by,
1178 creation_date,
1179 last_updated_by,
1180 last_update_date,
1181 last_update_login,
1182 trx_id
1183 )
1184 VALUES(
1185 Fun_dist_lines_s.nextval,
1186 Fun_trx_lines_s.CURRVAL,
1187 Fun_dist_lines_s.currval, --Bug 3603338
1188 p_dist_lines_tbl(l_line_count).party_id,
1189 p_dist_lines_tbl(l_line_count).party_type_flag,
1190 p_dist_lines_tbl(l_line_count).dist_type_flag,
1191 p_dist_lines_tbl(l_line_count).batch_dist_id,
1192 p_dist_lines_tbl(l_line_count).amount_cr,
1193 p_dist_lines_tbl(l_line_count).amount_dr,
1194 p_dist_lines_tbl(l_line_count).ccid,
1195 p_dist_lines_tbl(l_line_count).description,
1196 --p_dist_lines_tbl(l_line_count).auto_generate_flag, is not avl anywhere for now hardcoding to N Bug 3603338
1197 'N',
1198 p_dist_lines_tbl(l_line_count).attribute1,
1199 p_dist_lines_tbl(l_line_count).attribute2,
1200 p_dist_lines_tbl(l_line_count).attribute3,
1201 p_dist_lines_tbl(l_line_count).attribute4,
1202 p_dist_lines_tbl(l_line_count).attribute5,
1203 p_dist_lines_tbl(l_line_count).attribute6,
1204 p_dist_lines_tbl(l_line_count).attribute7,
1205 p_dist_lines_tbl(l_line_count).attribute8,
1206 p_dist_lines_tbl(l_line_count).attribute9,
1207 p_dist_lines_tbl(l_line_count).attribute10,
1208 p_dist_lines_tbl(l_line_count).attribute11,
1209 p_dist_lines_tbl(l_line_count).attribute12,
1210 p_dist_lines_tbl(l_line_count).attribute13,
1211 p_dist_lines_tbl(l_line_count).attribute14,
1212 p_dist_lines_tbl(l_line_count).attribute15,
1213 p_dist_lines_tbl(l_line_count).attribute_category,
1214 l_user,
1215 sysdate,
1216 l_user,
1217 sysdate,
1218 l_login,
1219 Fun_trx_headers_s.currval
1220 );
1221 END IF; --p_dist_lines_tbl.trx_id = p_trx_tbl.trx_id
1222
1223 END LOOP; -- l_line_count
1224 End If; -- p_ dist_line_tbl not null
1225
1226 /* -- Send Batch not individual transactions
1227 --raise Workflow event if p_sent='Y'
1228 IF p_sent = 'Y' THEN
1229 Print('Val and Insert >>>>'||'Raise Business Event');
1230 select FUN_TRX_BATCHES_S.CURRVAL into l_batch_id from dual;
1231 select FUN_TRX_HEADERS_S.CURRVAL into l_trx_id from dual;
1232 l_wfkey := fun_initiator_wf_pkg.generate_key(l_batch_id, l_trx_id);
1233 fun_wf_common.raise_wf_bus_event(l_batch_id, l_trx_id, l_wfkey, 'oracle.apps.fun.manualtrx.batch.send');
1234 END IF;
1235 */
1236
1237 END LOOP; --l_head_count
1238
1239 Print('Val and Insert >>>>'||'Inserting into fun_trx_batch_dist Table');
1240 -- Insertion into FUN_BATCH_DISTS
1241 If p_init_dist_tbl is not null then
1242 for l_line_count in 1..p_init_dist_tbl.count
1243 LOOP
1244
1245 INSERT into fun_batch_dists (
1246 batch_dist_id,
1247 line_number,
1248 batch_id,
1249 ccid,
1250 amount_cr,
1251 amount_dr,
1252 description,
1253 created_by,
1254 creation_date,
1255 last_updated_by,
1256 last_update_date,
1257 last_update_login
1258 )
1259 VALUES
1260 (
1261 fun_batch_dist_s.nextval,
1262 p_init_dist_tbl(l_line_count).line_number,
1263 l_batch_id,
1264 p_init_dist_tbl(l_line_count).ccid,
1265 p_init_dist_tbl(l_line_count).amount_cr,
1266 p_init_dist_tbl(l_line_count).amount_dr,
1267 p_init_dist_tbl(l_line_count).description,
1268 l_user,
1269 sysdate,
1270 l_user,
1271 sysdate,
1272 l_login
1273 );
1274 END LOOP; -- l_line_count
1275 End If; -- p_init_dist_tbl not null
1276
1277 IF p_sent = 'Y' THEN
1278 Print('Val and Insert >>>>'||'Raise Business Event');
1279
1280 UPDATE fun_trx_batches
1281 SET status = 'SENT'
1282 WHERE batch_id = l_batch_id;
1283
1284 UPDATE fun_trx_headers
1285 SET status = 'SENT'
1286 WHERE batch_id = l_batch_id;
1287
1288 l_wfkey := fun_initiator_wf_pkg.generate_key(l_batch_id, NULL);
1289
1290 fun_wf_common.raise_wf_bus_event(l_batch_id,
1291 NULL,
1292 l_wfkey,
1293 'oracle.apps.fun.manualtrx.batch.send');
1294 END IF;-- p_sent ='Y'
1295
1296 End if; -- p_insert true
1297 End If; -- Overall Status
1298
1299 IF FND_API.To_Boolean( nvl(p_commit,FND_API.G_FALSE) ) THEN
1300 COMMIT WORK;
1301 END IF;
1302
1303 -- Standard call to get message count and if count is 1, get message info.
1304 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1305 p_data => x_msg_data);
1306 Print('Val and Insert >>>>'||'End of the API');
1307
1308 EXCEPTION
1309
1310 WHEN FND_API.G_EXC_ERROR THEN
1311 Print('g_exc_error');
1312 p_batch_rec := l_old_batch_rec;
1313 p_trx_tbl := l_old_trx_tbl;
1314 p_init_dist_tbl := l_old_init_dist_tbl;
1315 p_dist_lines_tbl := l_old_dist_lines_tbl;
1316 ROLLBACK TO Fun_Trx_Val_And_Insert_PUB;
1317 x_return_status := FND_API.G_RET_STS_ERROR ;
1318 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1319 p_data => x_msg_data);
1320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1321 Print('Val and Insert >>>>'||'Unexpected error occurred -'||SQLERRM);
1322 p_batch_rec := l_old_batch_rec;
1323 p_trx_tbl := l_old_trx_tbl;
1324 p_init_dist_tbl := l_old_init_dist_tbl;
1325 p_dist_lines_tbl := l_old_dist_lines_tbl;
1326
1327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1328 Print('****************************************');
1329 Print('Val and Insert >>>>'||'Details of Error');
1330 Print('****************************************');
1331 IF x_msg_count > 1 THEN
1332 FOR i IN 1..x_msg_count
1333 LOOP
1334 Print(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ));
1335 END LOOP;
1336 ELSE
1337 Print(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ));
1338 END IF;
1339 ROLLBACK TO Fun_Trx_Val_And_Insert_PUB;
1340
1341 WHEN OTHERS THEN
1342 Print('Val and Insert >>>>'||'When Other');
1343 p_batch_rec := l_old_batch_rec;
1344 p_trx_tbl := l_old_trx_tbl;
1345 p_init_dist_tbl := l_old_init_dist_tbl;
1346 p_dist_lines_tbl := l_old_dist_lines_tbl;
1347 ROLLBACK TO Fun_Trx_Val_And_Insert_PUB;
1348 Print('Val and Insert >>>>'||'Exception others- '||SQLERRM);
1349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1350 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1351 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1352 END IF;
1353 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1354 p_data => x_msg_data);
1355
1356
1357 END CREATE_BATCH; -- Procedure
1358
1359
1360
1361 END FUN_TRX_PUB; -- Package Body
1362