[Home] [Help]
PACKAGE BODY: APPS.PA_INVOICE_XFER
Source
1 PACKAGE BODY PA_INVOICE_XFER as
2 /* $Header: PAXITCAB.pls 120.12 2011/11/11 08:54:32 cstatava ship $ */
3
4 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* bug 2958951 */
5 -- Private procedure
6
7 -- ==========================================================================
8 -- = PRIVATE PROCEDURE Get_Trans_Currency_Info
9 -- ==========================================================================
10 PROCEDURE Get_Trans_Currency_Info (l_curr_code IN varchar2, l_mau out NOCOPY number, --File.Sql.39 bug 4440895
11 l_sp out NOCOPY number, l_ep out NOCOPY number) IS --File.Sql.39 bug 4440895
12 BEGIN
13
14 SELECT FC.Minimum_Accountable_Unit,
15 FC.Precision,
16 FC.Extended_Precision
17 INTO l_mau,
18 l_sp,
19 l_ep
20 FROM FND_CURRENCIES FC
21 WHERE FC.Currency_Code = l_curr_code;
22
23 END Get_Trans_Currency_Info;
24
25 /**
26 Cr_Single_RND_Entries will apply the same logic of Receivable on Invoice
27 Line to reach the line amount,Write-Off/IC Receivable in accounting
28 currency and compute the possible rounding in accounting entries,and
29 insert proper distribution entries in RA interface distribution. Then
30 perform the update of invoice line with the computed line amount,Write-
31 off/Revenue Rounding amt in accounting currency.
32 **/
33
34 PROCEDURE Cr_Single_RND_Entries ( P_Batch_Src IN VARCHAR2,
35 P_Interface_attr1 IN VARCHAR2,
36 P_Interface_attr2 IN VARCHAR2,
37 P_Interface_attr3 IN VARCHAR2,
38 P_Interface_attr4 IN VARCHAR2,
39 P_Interface_attr5 IN VARCHAR2,
40 P_Interface_attr6 IN VARCHAR2,
41 P_Interface_attr7 IN VARCHAR2,
42 P_Interface_attr8 IN VARCHAR2,
43 P_Func_currency_code IN VARCHAR2,
44 P_Inv_currency_code IN VARCHAR2,
45 P_Single_Acct_Ccid IN NUMBER,
46 P_RND_ccid IN NUMBER,
47 P_Inv_line_amt IN NUMBER,
48 P_Proj_line_amt IN NUMBER,
49 P_Project_id IN NUMBER,
50 P_Conv_rate IN NUMBER,
51 P_Draft_inv_num IN NUMBER,
52 X_Acct_amt OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
53 AS
57 l_sp NUMBER;
54 l_rate NUMBER;
55 l_func_line NUMBER;
56 l_mau NUMBER;
58 l_ep NUMBER;
59 l_rnd_amt NUMBER;
60
61 /* Shared services changes: local variable to store org ID from org context */
62 l_org_id NUMBER;
63 BEGIN
64
65 /** If Project Functional Currency is same as invoice currency,
66 Then, no rounding issues will occur .
67 **/
68 If P_Func_currency_code = P_Inv_currency_code
69 Then
70
71 /** Update the lines Acct amount same as project currency amount **/
72
73 UPDATE PA_DRAFT_INVOICE_ITEMS
74 /* MCB2 change
75 SET ACCT_AMOUNT = AMOUNT,
76 */
77 SET ACCT_AMOUNT = PROJFUNC_BILL_AMOUNT,
78 ROUNDING_AMOUNT = 0
79 Where PROJECT_ID = P_PROJECT_ID
80 and DRAFT_INVOICE_NUM = P_DRAFT_INV_NUM
81 and LINE_NUM = TO_NUMBER(RTRIM(LTRIM(P_Interface_attr6)));
82
83 X_Acct_amt := 0;
84
85 /** Return to the calling program **/
86 Return;
87 End if;
88
89 l_rate := P_Conv_rate;
90
91 /** Get the currency info - minimum accountable unit,
92 standard precision,
93 extended precision for invoice currency.
94 **/
95 get_trans_currency_info(
96 L_CURR_CODE => P_Func_currency_code,
97 L_MAU => l_mau,
98 L_SP => l_sp,
99 L_EP => l_ep );
100
101 /** Compute Line Amount in accounting currency
102 from invoice currency.
103 **/
104
105 l_func_line := round(l_rate * P_Inv_line_amt,l_sp);
106 /* l_rnd_amt := P_Proj_line_amt - l_func_line; bug 4074354 */
107 l_rnd_amt := l_func_line - P_Proj_line_amt;
108
109 /** Shared services changes: get org id from org context, and
110 insert it into table RA_INTERFACE_DISTRIBUTIONS as ORG_ID.
111 **/
112 l_org_id := MO_GLOBAL.get_current_org_id;
113
114 /**
115 Insert Single Account rounding amount in Interface Distribution
116 **/
117 IF l_rnd_amt <> 0
118 THEN
119 INSERT INTO RA_INTERFACE_DISTRIBUTIONS
120 ( ACCOUNT_CLASS, ACCTD_AMOUNT,AMOUNT,PERCENT,
121 CODE_COMBINATION_ID, INTERFACE_LINE_ATTRIBUTE1,
122 INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3,
123 INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5,
124 INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7,
125 INTERFACE_LINE_ATTRIBUTE8,INTERFACE_LINE_CONTEXT,
126 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
127 ORG_ID)
128 VALUES ('REV', l_rnd_amt ,0,NULL,P_Single_Acct_Ccid, P_Interface_attr1,
129 P_Interface_attr2, P_Interface_attr3,
130 P_Interface_attr4, P_Interface_attr5,
131 P_Interface_attr6, P_Interface_attr7,
132 P_Interface_attr8, P_Batch_Src,
133 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID, /* Bug # 2244810 */
134 l_org_id);
135 INSERT INTO RA_INTERFACE_DISTRIBUTIONS
136 ( ACCOUNT_CLASS, ACCTD_AMOUNT,AMOUNT,PERCENT,
137 CODE_COMBINATION_ID, INTERFACE_LINE_ATTRIBUTE1,
138 INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3,
139 INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5,
140 INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7,
141 INTERFACE_LINE_ATTRIBUTE8,INTERFACE_LINE_CONTEXT,
142 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
143 ORG_ID)
144 VALUES ('REV',(-1)*( l_rnd_amt ),0,NULL,P_RND_ccid,
145 P_Interface_attr1,
146 P_Interface_attr2, P_Interface_attr3,
147 P_Interface_attr4, P_Interface_attr5,
148 P_Interface_attr6, P_Interface_attr7,
149 'RND', P_Batch_Src,
150 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID, /* Bug # 2244810 */
151 l_org_id);
152
153 END IF;
154
155 /** Update Invoice line with line amount,rounding amount
156 **/
157
158 UPDATE PA_DRAFT_INVOICE_ITEMS
159 SET ACCT_AMOUNT = l_func_line,
160 ROUNDING_AMOUNT = l_rnd_amt
161 Where PROJECT_ID = P_PROJECT_ID
162 and DRAFT_INVOICE_NUM = P_DRAFT_INV_NUM
163 and LINE_NUM = TO_NUMBER(RTRIM(LTRIM(P_Interface_attr6)));
164
165 X_Acct_amt := l_func_line;
166
167 EXCEPTION
168 When Others
169 Then
170 Raise;
171
172
173 END Cr_Single_RND_Entries;
174
175 /**
176 Create_RND_Entries will apply the same logic of Receivable on Invoice
177 Line to reach the line amount,UBR and UER in accounting currency and
178 compute the possible rounding in accounting entries,and insert proper
179 distribution entries in RA interface distribution. Then perform the
180 update of invoice line with the computed line amount,UBR,UER in acco
181 unting currency.
182 **/
183
184 PROCEDURE Create_RND_Entries ( P_Batch_Src IN VARCHAR2,
185 P_Interface_attr1 IN VARCHAR2,
186 P_Interface_attr2 IN VARCHAR2,
187 P_Interface_attr3 IN VARCHAR2,
188 P_Interface_attr4 IN VARCHAR2,
189 P_Interface_attr5 IN VARCHAR2,
193 P_Inv_currency_code IN VARCHAR2,
190 P_Interface_attr6 IN VARCHAR2,
191 P_Interface_attr7 IN VARCHAR2,
192 P_Func_currency_code IN VARCHAR2,
194 P_Inv_rate_type IN VARCHAR2,
195 P_Inv_rate_date IN DATE,
196 P_Inv_exchange_rate IN NUMBER,
197 P_UBR_ccid IN NUMBER,
198 P_UER_ccid IN NUMBER,
199 P_RND_ccid IN NUMBER,
200 P_Inv_line_amt IN NUMBER,
201 P_Prj_ubr_amt IN NUMBER,
202 P_Prj_uer_amt IN NUMBER,
203 P_Inv_ubr_amt IN NUMBER,
204 P_Inv_uer_amt IN NUMBER,
205 P_Project_id IN NUMBER,
206 P_Conv_rate IN NUMBER,
207 P_Draft_inv_num IN NUMBER )
208 AS
209
210 l_rate NUMBER;
211 l_func_UBR NUMBER;
212 l_func_UER NUMBER;
213 l_func_line NUMBER;
214 l_rnd_UBR NUMBER;
215 l_rnd_UER NUMBER;
216 l_mau NUMBER;
217 l_sp NUMBER;
218 l_ep NUMBER;
219
220 /* Shared services changes: local variable to store org ID from org context */
221 l_org_id NUMBER;
222 BEGIN
223 /** If Project Functional Currency is same as invoice currency,
224 Then, no rounding issues will occur .
225 **/
226
227 If P_Func_currency_code = P_Inv_currency_code
228 Then
229
230 /** Update the lines Acct amount same as project currency amount **/
231
232 UPDATE PA_DRAFT_INVOICE_ITEMS
233 /* MCB2 change
234 SET ACCT_AMOUNT = AMOUNT,
235 */
236 SET ACCT_AMOUNT = PROJFUNC_BILL_AMOUNT,
237 ROUNDING_AMOUNT = 0,
238 UNBILLED_ROUNDING_AMOUNT_DR = 0,
239 UNEARNED_ROUNDING_AMOUNT_CR = 0
240 Where PROJECT_ID = P_PROJECT_ID
241 and DRAFT_INVOICE_NUM = P_DRAFT_INV_NUM
242 and LINE_NUM = TO_NUMBER(RTRIM(LTRIM(P_Interface_attr6)));
243
244 /** Return to the calling program **/
245 Return;
246 End if;
247
248 l_rate := P_Conv_rate;
249
250 /** Get the currency info - minimum accountable unit,
251 standard precision,
252 extended precision for invoice currency.
253 **/
254 get_trans_currency_info(
255 L_CURR_CODE => P_Func_currency_code,
256 L_MAU => l_mau,
257 L_SP => l_sp,
258 L_EP => l_ep );
259
260 /** Compute UBR,Line Amount ,UER in accounting currency
261 from invoice currency.
262 **/
263
264 l_func_UBR := round(l_rate * P_Inv_ubr_amt,l_sp);
265 l_func_line := round(l_rate * P_Inv_line_amt,l_sp);
266 l_func_UER := l_func_line - l_func_UBR;
267 l_rnd_UBR := P_Prj_ubr_amt - l_func_UBR;
268 l_rnd_UER := P_Prj_uer_amt - l_func_UER;
269
270 /** Shared services changes: get org id from org context, and
271 insert it into table RA_INTERFACE_DISTRIBUTIONS as ORG_ID.
272 **/
273 l_org_id := MO_GLOBAL.get_current_org_id;
274
275 /**
276 Insert UBR rounding amount in Interface Distribution
277 **/
278 /**
279 Shared services changes: Insert the org ID
280 **/
281 IF l_rnd_UBR <> 0
282 THEN
283 INSERT INTO RA_INTERFACE_DISTRIBUTIONS
284 ( ACCOUNT_CLASS, ACCTD_AMOUNT,AMOUNT,PERCENT,
285 CODE_COMBINATION_ID, INTERFACE_LINE_ATTRIBUTE1,
286 INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3,
287 INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5,
288 INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7,
289 INTERFACE_LINE_ATTRIBUTE8,INTERFACE_LINE_CONTEXT,
290 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
291 ORG_ID)
292 VALUES ('REV', l_rnd_UBR,0,NULL,P_UBR_ccid, P_Interface_attr1,
293 P_Interface_attr2, P_Interface_attr3,
294 P_Interface_attr4, P_Interface_attr5,
295 P_Interface_attr6, P_Interface_attr7,
296 'UBR', P_Batch_Src,
297 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
298 l_org_id);
299 END IF;
300
301
302 /**
303 Insert UER rounding amount in Interface Distribution
304 **/
305 /**
306 Shared services changes: Insert the org ID
307 **/
308 IF l_rnd_UER <> 0
309 THEN
310 INSERT INTO RA_INTERFACE_DISTRIBUTIONS
311 ( ACCOUNT_CLASS, ACCTD_AMOUNT,AMOUNT,PERCENT,
312 CODE_COMBINATION_ID, INTERFACE_LINE_ATTRIBUTE1,
313 INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3,
314 INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5,
315 INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7,
316 INTERFACE_LINE_ATTRIBUTE8,INTERFACE_LINE_CONTEXT,
317 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
318 ORG_ID)
319 VALUES ('REV', l_rnd_UER,0,NULL,P_UER_ccid, P_Interface_attr1,
323 'UER', P_Batch_Src,
320 P_Interface_attr2, P_Interface_attr3,
321 P_Interface_attr4, P_Interface_attr5,
322 P_Interface_attr6, P_Interface_attr7,
324 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
325 l_org_id);
326 END IF;
327
328 /**
329 Shared services changes: Insert the org ID
330 **/
331 IF (l_rnd_UBR + l_rnd_UER) <> 0
332 THEN
333 INSERT INTO RA_INTERFACE_DISTRIBUTIONS
334 ( ACCOUNT_CLASS, ACCTD_AMOUNT,AMOUNT,PERCENT,
335 CODE_COMBINATION_ID, INTERFACE_LINE_ATTRIBUTE1,
336 INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3,
337 INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5,
338 INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7,
339 INTERFACE_LINE_ATTRIBUTE8,INTERFACE_LINE_CONTEXT,
340 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
341 ORG_ID)
342 VALUES ('REV',(-1)*( l_rnd_UBR + l_rnd_UER),0,NULL,P_RND_ccid,
343 P_Interface_attr1,
344 P_Interface_attr2, P_Interface_attr3,
345 P_Interface_attr4, P_Interface_attr5,
346 P_Interface_attr6, P_Interface_attr7,
347 'RND', P_Batch_Src,
348 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
349 l_org_id);
350 END IF;
351
352 /** Update Invoice line with line amount,rounding amount,UBR Rounding
353 ,UER rounding amount in accounting currency.
354 **/
355
356 UPDATE PA_DRAFT_INVOICE_ITEMS
357 SET ACCT_AMOUNT = l_func_line,
358 /* MCB2 change
359 ROUNDING_AMOUNT = l_func_line - AMOUNT,
360 */
361 ROUNDING_AMOUNT = l_func_line - PROJFUNC_BILL_AMOUNT,
362 UNBILLED_ROUNDING_AMOUNT_DR = (-1)*l_rnd_UBR,
363 UNEARNED_ROUNDING_AMOUNT_CR = l_rnd_UER
364 Where PROJECT_ID = P_PROJECT_ID
365 and DRAFT_INVOICE_NUM = P_DRAFT_INV_NUM
366 and LINE_NUM = TO_NUMBER(RTRIM(LTRIM(P_Interface_attr6)));
367
368 EXCEPTION
369 When Others
370 Then
371 Raise;
372
373
374 END Create_RND_Entries;
375
376 /**
377 This procedure will compute the gl entries for an invoice line,and
378 insert the entries in Receivable Interface distribution table.
379 **/
380
381 PROCEDURE Ins_Dist_Lines(P_Transfer_Mode IN VARCHAR2,
382 P_Project_Id IN NUMBER,
383 P_Project_Num IN VARCHAR2,
384 P_Inv_Num IN NUMBER,
385 P_Inv_Curr IN VARCHAR2,
386 P_Proj_Func_Cur IN VARCHAR2,
387 P_WO_Ccid IN NUMBER,
388 P_UBR_Ccid IN NUMBER,
389 P_UER_Ccid IN NUMBER,
390 P_REC_Ccid IN NUMBER,
391 P_RND_Ccid IN NUMBER,
392 P_UNB_ret_Ccid IN NUMBER,
393 P_Reason_Code IN VARCHAR2,
394 P_Batch_Src IN VARCHAR2,
395 P_Trx_Num IN VARCHAR2,
396 P_Conv_Rate IN NUMBER,
397 P_Retn_Acct_Flag IN VARCHAR2)
398 AS
399 CURSOR get_single_line
400 IS
401 SELECT I.AMOUNT AR_AMOUNT,
402 I.INTERFACE_LINE_ATTRIBUTE1 attr1,
403 I.INTERFACE_LINE_ATTRIBUTE2 attr2,
404 I.INTERFACE_LINE_ATTRIBUTE3 attr3,
405 I.INTERFACE_LINE_ATTRIBUTE4 attr4,
406 I.INTERFACE_LINE_ATTRIBUTE5 attr5,
407 I.INTERFACE_LINE_ATTRIBUTE6 attr6,
408 I.INTERFACE_LINE_ATTRIBUTE7 attr7,
409 decode(P_Transfer_Mode,'INTERCOMPANY','ICREV','WO') attr8,
410 /* MCB2 change
411 DII.AMOUNT Proj_line_amt,
412 */
413 DII.PROJFUNC_BILL_AMOUNT Proj_line_amt,
414 Decode(P_Transfer_Mode,'INTERCOMPANY',
415 DII.CC_REV_CODE_COMBINATION_ID,P_WO_Ccid) rev_ccid
416 FROM RA_INTERFACE_LINES I,
417 PA_DRAFT_INVOICE_ITEMS DII
418 WHERE I.INTERFACE_LINE_ATTRIBUTE1||'' = P_Project_Num
419 AND rtrim(ltrim(I.INTERFACE_LINE_ATTRIBUTE2)) =
420 rtrim(ltrim(to_char(P_Inv_Num)))
421 -- AND I.INTERFACE_LINE_CONTEXT = P_Batch_Src -- Performance Bug 2695303
422 AND I.BATCH_SOURCE_NAME = P_Batch_Src
423 AND I.TRX_NUMBER = P_Trx_Num
424 AND DII.PROJECT_ID = P_Project_Id
425 AND DII.DRAFT_INVOICE_NUM = P_Inv_Num
426 AND DII.LINE_NUM
427 = to_number(TRUNC(I.INTERFACE_LINE_ATTRIBUTE6));
428
429
430 CURSOR get_acct_info
431 IS
432 SELECT I.AMOUNT AR_AMOUNT,
433 I.INTERFACE_LINE_ATTRIBUTE1 ATTR1,
434 I.INTERFACE_LINE_ATTRIBUTE2 ATTR2,
435 I.INTERFACE_LINE_ATTRIBUTE3 ATTR3,
436 I.INTERFACE_LINE_ATTRIBUTE4 ATTR4,
437 I.INTERFACE_LINE_ATTRIBUTE5 ATTR5,
438 I.INTERFACE_LINE_ATTRIBUTE6 ATTR6,
439 I.INTERFACE_LINE_ATTRIBUTE7 ATTR7,
440 I.CONVERSION_RATE CRATE,
441 I.CONVERSION_DATE CDATE,
442 I.CONVERSION_TYPE CTYPE
443 FROM RA_INTERFACE_LINES I
447 -- AND I.INTERFACE_LINE_CONTEXT = P_Batch_Src -- Performance Bug 2695303
444 WHERE I.INTERFACE_LINE_ATTRIBUTE1||'' = P_Project_Num
445 AND rtrim(ltrim(I.INTERFACE_LINE_ATTRIBUTE2)) =
446 rtrim(ltrim(to_char(P_Inv_Num)))
448 AND I.BATCH_SOURCE_NAME = P_Batch_Src
449 AND I.TRX_NUMBER = P_Trx_Num ;
450
451 /*Commented for bug 1858443. Added for bug 1529404
452 AND I.BATCH_SOURCE_NAME = (SELECT RBS.NAME FROM
453 RA_BATCH_SOURCES RBS,PA_IMPLEMENTATIONS IMP
454 WHERE RBS.BATCH_SOURCE_ID
455 =IMP.INVOICE_BATCH_SOURCE_ID);
456 */
457
458 /* Added for bug 1633776. Removed abs from PRJ_UBR and PRJ_UER and multiplied
459 (-1) with PRJ_UBR. */
460
461
462 /* Retention Enahancement : Added the column invoice line type to identify the Regular line and
463 unbilled retention line */
464
465
466 Cursor get_line_info ( l_ar_amt NUMBER,
467 l_line_num VARCHAR2)
468 Is
469 SELECT DECODE(P_Proj_Func_Cur,P_Inv_Curr,(-1)*(DII.UNBILLED_RECEIVABLE_DR),
470 /* MCB2 change
471 DECODE((-1)*(DII.UNBILLED_RECEIVABLE_DR),DII.AMOUNT,l_ar_amt,
472 PA_CURRENCY.round_trans_currency_amt((l_ar_amt/DII.AMOUNT)*
473 */
474 DECODE((-1)*(DII.UNBILLED_RECEIVABLE_DR),DII.PROJFUNC_BILL_AMOUNT,l_ar_amt,
475 PA_CURRENCY.round_trans_currency_amt((l_ar_amt/DII.PROJFUNC_BILL_AMOUNT)*
476 (-1)*(DII.UNBILLED_RECEIVABLE_DR),P_Inv_Curr))) INV_UBR,
477 (-1)*(nvl(DII.UNBILLED_RECEIVABLE_DR,0)) PRJ_UBR,
478 (nvl(DII.UNEARNED_REVENUE_CR,0)) PRJ_UER,
479 dii.invoice_line_type, /* Retention Enhancement */
480 dii.projfunc_bill_amount,
481 dii.inv_amount
482 FROM PA_DRAFT_INVOICE_ITEMS DII
483 WHERE DII.PROJECT_ID = P_Project_Id
484 AND DII.DRAFT_INVOICE_NUM = P_Inv_Num
485 AND DII.LINE_NUM = to_number(l_line_num);
486
487 l_inv_uer NUMBER;
488 l_acct_amt NUMBER;
489
490
491 /* Retention Enhancement : Variable for store the AR_amount(inv_amount) */
492
493 l_ar_amount NUMBER;
494
495 l_min_line NUMBER; /*Added for bug 7665769 */
496
497 /* Shared services changes: local variable to store org ID from org context */
498 l_org_id NUMBER;
499
500 BEGIN
501
502 /** Shared services changes: get org id from org context, and
503 insert it into table RA_INTERFACE_DISTRIBUTIONS as ORG_ID.
504 **/
505 l_org_id := MO_GLOBAL.get_current_org_id;
506
507 /* For Write-off of Regular Invoice and InterCompany Invoice,the entry
508 type is same i.e. only two accounts are involved - Write-off/IC Revenue
509 account and Recivable/IC Receivable account . */
510
511 IF ( P_Reason_Code = 'PA_WRITE_OFF'
512 OR P_Transfer_Mode = 'INTERCOMPANY' )
513 THEN
514 FOR get_woff_line_rec IN get_single_line
515 LOOP
516
517 /* Create Rounding Entries for Write-Off Invoice */
518 Cr_Single_RND_Entries ( P_Batch_Src => P_Batch_Src,
519 P_Interface_attr1 =>get_woff_line_rec.attr1,
520 P_Interface_attr2 =>get_woff_line_rec.attr2,
521 P_Interface_attr3 =>get_woff_line_rec.attr3,
522 P_Interface_attr4 =>get_woff_line_rec.attr4,
523 P_Interface_attr5 =>get_woff_line_rec.attr5,
524 P_Interface_attr6 =>get_woff_line_rec.attr6,
525 P_Interface_attr7 =>get_woff_line_rec.attr7,
526 P_Interface_attr8 =>get_woff_line_rec.attr8,
527 P_Func_currency_code =>P_Proj_Func_Cur,
528 P_Inv_currency_code =>P_Inv_curr,
529 P_Single_Acct_Ccid
530 =>get_woff_line_rec.rev_ccid,
531 P_RND_ccid => P_RND_Ccid,
532 P_Inv_line_amt=> get_woff_line_rec.AR_AMOUNT,
533 P_Proj_line_amt
534 => get_woff_line_rec.Proj_Line_amt,
535 P_Project_id => P_Project_Id,
536 P_Conv_rate => P_Conv_rate,
537 P_Draft_inv_num => P_Inv_Num,
538 X_Acct_amt => l_acct_amt);
539
540 /* Insert the Write-off/Intercompany accounting */
541 /* Shared services changes: Insert the org ID */
542 INSERT INTO RA_Interface_Distributions
543 (
544 Account_Class, Amount, Percent, Code_Combination_ID,
545 Interface_Line_Attribute1, Interface_Line_Attribute2,
546 Interface_Line_Attribute3, Interface_Line_Attribute4,
547 Interface_Line_Attribute5, Interface_Line_Attribute6,
548 Interface_Line_Attribute7, Interface_Line_Attribute8,
549 Interface_Line_Context,Acctd_Amount,
550 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
551 ORG_ID
552 )
553 values( 'REV',
554 get_woff_line_rec.AR_AMOUNT,
555 NULL,
556 get_woff_line_rec.rev_ccid,
557 get_woff_line_rec.ATTR1,
558 get_woff_line_rec.ATTR2,
562 get_woff_line_rec.ATTR6,
559 get_woff_line_rec.ATTR3,
560 get_woff_line_rec.ATTR4,
561 get_woff_line_rec.ATTR5,
563 get_woff_line_rec.ATTR7,
564 get_woff_line_rec.ATTR8,
565 P_Batch_Src,
566 decode(l_acct_amt,0,get_woff_line_rec.Proj_Line_amt,l_acct_amt),
567 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
568 l_org_id);
569 END LOOP;
570
571 ELSE
572 FOR cur_get_acct_info IN get_acct_info
573 LOOP
574
575 /* Retention Enhancement : Storing the AR amount and line amount */
576
577
578
579 l_ar_amount := cur_get_acct_info.ar_amount;
580
581
582 FOR cur_get_line_info IN get_line_info( cur_get_acct_info.AR_AMOUNT,
583 cur_get_acct_info.ATTR6 )
584 LOOP
585
586
587 /* Retention Enhancement : If Retention accounting flag is 'Y' in the project setup
588 and invoice line type = 'RETENTION' then insert new line in ra_interface_distribution table
589 for the unbill retention account. */
590
591
592 IF ((P_Retn_Acct_Flag = 'Y') AND (cur_get_line_info.invoice_line_type = 'RETENTION')) THEN
593
594
595
596 /* Retention Enhnancement : Creating Rounding Entries for Retention */
597 Cr_Single_RND_Entries ( P_Batch_Src => P_Batch_Src,
598 P_Interface_attr1 => cur_get_acct_info.attr1,
599 P_Interface_attr2 => cur_get_acct_info.attr2,
600 P_Interface_attr3 => cur_get_acct_info.attr3,
601 P_Interface_attr4 => cur_get_acct_info.attr4,
602 P_Interface_attr5 => cur_get_acct_info.attr5,
603 P_Interface_attr6 => cur_get_acct_info.attr6,
604 P_Interface_attr7 => cur_get_acct_info.attr7,
605 P_Interface_attr8 => 'UNB-RET',
606 P_Func_currency_code => P_Proj_Func_Cur,
607 P_Inv_currency_code => P_Inv_curr,
608 P_Single_Acct_Ccid => P_UNB_ret_Ccid,
609 P_RND_ccid => P_RND_Ccid,
610 P_Inv_line_amt => l_ar_amount,
611 P_Proj_line_amt => cur_get_line_info.projfunc_bill_amount,
612 P_Project_id => P_Project_Id,
613 P_Conv_rate => P_Conv_rate,
614 P_Draft_inv_num => P_Inv_Num,
615 X_Acct_amt => l_acct_amt);
616
617 /* Shared services changes: Insert the org ID */
618 INSERT INTO RA_Interface_Distributions
619 (
620 Account_Class, Amount, Percent, Code_Combination_ID,
621 Interface_Line_Attribute1, Interface_Line_Attribute2,
622 Interface_Line_Attribute3, Interface_Line_Attribute4,
623 Interface_Line_Attribute5, Interface_Line_Attribute6,
624 Interface_Line_Attribute7, Interface_Line_Attribute8,
625 Interface_Line_Context,Acctd_amount,
626 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
627 ORG_ID
628 )
629 VALUES ('REV',
630 l_ar_amount,
631 NULL,
632 P_UNB_ret_Ccid,
633 cur_get_acct_info.ATTR1,
634 cur_get_acct_info.ATTR2,
635 cur_get_acct_info.ATTR3,
636 cur_get_acct_info.ATTR4,
637 cur_get_acct_info.ATTR5,
638 cur_get_acct_info.ATTR6,
639 cur_get_acct_info.ATTR7,
640 'UNB-RET',
641 P_Batch_Src,
642 decode(l_acct_amt,0, cur_get_line_info.projfunc_bill_amount, l_acct_amt),
643 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
644 l_org_id);
645
646
647 ELSE
648
649
650
651 l_inv_uer := cur_get_acct_info.AR_AMOUNT
652 -nvl(cur_get_line_info.INV_UBR,0);
653
654
655
656 /* Insert UBR entry for each lines */
657 /* Shared services changes: Insert the org ID */
658 IF (nvl(cur_get_line_info.INV_UBR,0) <> 0 or PA_BILLING.GETINVOICENZ = 'Y') /* Added Additonal condition for BUG 8666892 */
659 THEN
660 INSERT INTO RA_Interface_Distributions
661 (
662 Account_Class, Amount, Percent, Code_Combination_ID,
663 Interface_Line_Attribute1, Interface_Line_Attribute2,
664 Interface_Line_Attribute3, Interface_Line_Attribute4,
665 Interface_Line_Attribute5, Interface_Line_Attribute6,
666 Interface_Line_Attribute7, Interface_Line_Attribute8,
667 Interface_Line_Context,Acctd_amount,
668 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG #2244810 */
669 ORG_ID
673 NULL,
670 )
671 VALUES ('REV',
672 cur_get_line_info.INV_UBR,
674 P_UBR_Ccid,
675 cur_get_acct_info.ATTR1,
676 cur_get_acct_info.ATTR2,
677 cur_get_acct_info.ATTR3,
678 cur_get_acct_info.ATTR4,
679 cur_get_acct_info.ATTR5,
680 cur_get_acct_info.ATTR6,
681 cur_get_acct_info.ATTR7,
682 'UBR',
683 P_Batch_Src,
684 cur_get_line_info.PRJ_UBR,
685 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
686 l_org_id);
687 END IF;
688
689
690
691 /* Insert UER entry for each lines */
692 /* Shared services changes: Insert the org ID */
693 IF (l_inv_uer <> 0 or PA_BILLING.GETINVOICENZ = 'Y') /* Added Additonal condition for BUG 8666892 */
694 THEN
695 INSERT INTO RA_Interface_Distributions
696 (
697 Account_Class, Amount, Percent, Code_Combination_ID,
698 Interface_Line_Attribute1, Interface_Line_Attribute2,
699 Interface_Line_Attribute3, Interface_Line_Attribute4,
700 Interface_Line_Attribute5, Interface_Line_Attribute6,
701 Interface_Line_Attribute7, Interface_Line_Attribute8,
702 Interface_Line_Context,Acctd_Amount,
703 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG #2244810 */
704 ORG_ID
705 )
706 VALUES ('REV',
707 l_inv_uer,
708 NULL,
709 P_UER_Ccid,
710 cur_get_acct_info.ATTR1,
711 cur_get_acct_info.ATTR2,
712 cur_get_acct_info.ATTR3,
713 cur_get_acct_info.ATTR4,
714 cur_get_acct_info.ATTR5,
715 cur_get_acct_info.ATTR6,
716 cur_get_acct_info.ATTR7,
717 'UER',
718 P_Batch_Src,
719 cur_get_line_info.PRJ_UER,
720 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
721 l_org_id);
722
723 END IF;
724
725 Create_RND_Entries
726 (
727 P_Batch_Src => P_Batch_Src,
728 P_Interface_attr1 => cur_get_acct_info.ATTR1,
729 P_Interface_attr2 => cur_get_acct_info.ATTR2,
730 P_Interface_attr3 => cur_get_acct_info.ATTR3,
731 P_Interface_attr4 => cur_get_acct_info.ATTR4,
732 P_Interface_attr5 => cur_get_acct_info.ATTR5,
733 P_Interface_attr6 => cur_get_acct_info.ATTR6,
734 P_Interface_attr7 => cur_get_acct_info.ATTR7,
735 P_Func_currency_code => P_Proj_Func_Cur,
736 P_Inv_currency_code => P_Inv_Curr,
737 P_Inv_rate_type => cur_get_acct_info.CTYPE,
738 P_Inv_rate_date => cur_get_acct_info.CDATE,
739 P_Inv_exchange_rate => cur_get_acct_info.CRATE,
740 P_UBR_ccid => P_UBR_Ccid,
741 P_UER_ccid => P_UER_Ccid,
742 P_RND_ccid => P_RND_Ccid,
743 P_Inv_line_amt => cur_get_acct_info.AR_AMOUNT,
744 /* P_Prj_ubr_amt => abs(cur_get_line_info.PRJ_UBR),
745 P_Prj_uer_amt => abs(cur_get_line_info.PRJ_UER),
746 Removed abs added in bug 1633776 as fix for bug 2032231 */
747 P_Prj_ubr_amt => cur_get_line_info.PRJ_UBR,
748 P_Prj_uer_amt => cur_get_line_info.PRJ_UER,
749 P_Inv_ubr_amt => cur_get_line_info.INV_UBR,
750 P_Inv_uer_amt => l_inv_uer,
751 P_Project_id => P_Project_Id,
752 P_Conv_rate => P_Conv_Rate,
753 P_Draft_inv_num =>
754 to_number(rtrim(ltrim(cur_get_acct_info.ATTR2))));
755
756
757 END IF ; /* P_Retn_Acct_Flag = 'Y and invoice_line_type = 'RETENTION' */
758
759
760 END LOOP;
761 END LOOP;
762 END IF;
763
764
765 /*Added the select statement for bug 7665769 */
766 SELECT min(to_number(rtrim(ltrim(I.Interface_Line_Attribute6))))
767 INTO l_min_line
768 FROM ra_interface_lines I
769 WHERE rtrim(ltrim(I.Interface_Line_Attribute2)) = rtrim(ltrim(to_char(P_Inv_Num)))
770 AND I.Interface_Line_Attribute1 = P_Project_Num
771 AND I.BATCH_SOURCE_NAME = P_Batch_Src
772 AND I.TRX_NUMBER = P_Trx_Num;
773
774 /* Insert the Receivable Accounting for the Invoice */
775 /* Shared services changes: Insert the org ID */
776 INSERT INTO RA_Interface_Distributions
777 (
778 Account_Class, Amount, Percent, Code_Combination_ID,
779 Interface_Line_Attribute1, Interface_Line_Attribute2,
780 Interface_Line_Attribute3, Interface_Line_Attribute4,
781 Interface_Line_Attribute5, Interface_Line_Attribute6,
785 ORG_ID
782 Interface_Line_Attribute7, Interface_Line_Attribute8,
783 Interface_Line_Context,
784 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
786 )
787 SELECT 'REC',
788 NULL,
789 100,
790 P_REC_Ccid,
791 I.Interface_Line_Attribute1,
792 I.Interface_Line_Attribute2,
793 I.Interface_Line_Attribute3,
794 I.Interface_Line_Attribute4,
795 I.Interface_Line_Attribute5,
796 I.Interface_Line_Attribute6,
797 I.Interface_Line_Attribute7,
798 NULL,
799 P_Batch_Src,
800 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
801 l_org_id
802 FROM RA_Interface_lines I
803 WHERE rtrim(ltrim(I.Interface_Line_Attribute2)) =
804 rtrim(ltrim(to_char(P_Inv_Num)))
805 AND to_number(rtrim(ltrim(I.Interface_Line_Attribute6))) = l_min_line --Modified the condition for bug 7665769
806 AND I.Interface_Line_Attribute1 = P_Project_Num
807 -- AND I.INTERFACE_LINE_CONTEXT = P_Batch_Src -- Performance Bug 2695303
808 AND I.BATCH_SOURCE_NAME = P_Batch_Src
809 AND I.TRX_NUMBER = P_Trx_Num;
810
811 EXCEPTION
812 WHEN OTHERS
813 THEN
814 RAISE;
815
816 END Ins_Dist_Lines;
817
818
819 /* This overloaded function was added to provide for compilation of older
820 version of files like patopt.lpc. In these older versions, call to procedure
821 Convert_Amt is made with older, different signature. This procedure is not
822 supposed to be called, hence the body consists of code to raise and exception
823 if called. -- bug 2615572*/
824
825 PROCEDURE Convert_Amt ( P_Project_Id IN NUMBER,
826 P_Project_Num IN VARCHAR2,
827 P_Request_Id IN NUMBER,
828 P_Proj_Func_Cur IN VARCHAR2,
829 P_Batch_Src IN VARCHAR2,
830 P_WO_Ccid IN NUMBER,
831 P_UBR_Ccid IN NUMBER,
832 P_UER_Ccid IN NUMBER,
833 P_REC_Ccid IN NUMBER,
834 P_RND_Ccid IN NUMBER,
835 P_Transfer_Mode IN VARCHAR2)
836 AS
837 BEGIN
838 RAISE NO_DATA_FOUND;
839
840 EXCEPTION
841 WHEN OTHERS THEN
842 RAISE;
843 END Convert_Amt;
844
845
846
847 PROCEDURE Convert_Amt ( P_Project_Id IN NUMBER,
848 P_Project_Num IN VARCHAR2,
849 P_Request_Id IN NUMBER,
850 P_Proj_Func_Cur IN VARCHAR2,
851 P_Batch_Src IN VARCHAR2,
852 P_WO_Ccid IN NUMBER,
853 P_UBR_Ccid IN NUMBER,
854 P_UER_Ccid IN NUMBER,
855 P_REC_Ccid IN NUMBER,
856 P_RND_Ccid IN NUMBER,
857 P_UNB_ret_Ccid IN NUMBER,
858 P_Transfer_Mode IN VARCHAR2,
859 P_Retn_Acct_Flag IN VARCHAR2)
860 AS
861 CURSOR get_invoice_info
862 IS
863 SELECT ORG_DI.DRAFT_INVOICE_NUM cm_inv_num,
864 INT_LINE.currency_code invoice_currency_code,
865 ORG_DI.DRAFT_INVOICE_NUM_CREDITED orig_inv_num,
866 0 CUST_TRX_ID,
867 'N' CM_CAN_FLAG,
868 ORG_DI.RA_INVOICE_NUMBER CM_TRX_NUM,
869 /* INT_LINE.reason_code REASON_CODE, Changed for
870 credit memo reason*/
871 DECODE(ORG_DI.Draft_Invoice_Num_Credited, NULL, '',
872 DECODE(NVL(ORG_DI.Write_Off_Flag, 'N'), 'N',
873 'PA_CREDIT_MEMO', 'PA_WRITE_OFF')) REASON_CODE,
874 /* INT_LINE.Interface_Line_Context SOURCE, Commented for bug 3502647 */
875 INT_LINE.BATCH_SOURCE_NAME SOURCE, /* Added for bug 3502647 */
876 ORG_DI.PROJFUNC_INVTRANS_RATE_DATE exchg_date,
877 ORG_DI.PROJFUNC_INVTRANS_RATE_TYPE exchg_type,
878 ORG_DI.INV_CURRENCY_CODE inv_curr_code,
879 INT_LINE.CONVERSION_RATE exchg_rate
880 FROM ra_interface_lines INT_LINE,
881 pa_draft_invoices ORG_DI
882 WHERE INT_LINE.interface_line_attribute1||'' = P_Project_Num
883 AND ORG_DI.request_id = P_Request_Id
884 AND ltrim(rtrim(INT_LINE.interface_line_attribute2))
885 = ltrim(rtrim(to_char(ORG_DI.Draft_invoice_num)))
886 AND ORG_DI.project_id = P_Project_Id
887 AND ORG_DI.DRAFT_INVOICE_NUM_CREDITED is NULL
888 AND INT_LINE.BATCH_SOURCE_NAME = P_Batch_Src
889 AND INT_LINE.TRX_NUMBER = ORG_DI.RA_INVOICE_NUMBER
890 UNION
891 SELECT CM_DI.DRAFT_INVOICE_NUM cm_inv_num,
892 INT_LINE.currency_code invoice_currency_code,
893 CM_DI.DRAFT_INVOICE_NUM_CREDITED orig_inv_num,
894 nvl(ORG_DI.SYSTEM_REFERENCE,0) CUST_TRX_ID,
895 nvl(ORG_DI.CANCELED_FLAG,'N') CM_CAN_FLAG,
896 CM_DI.RA_INVOICE_NUMBER CM_TRX_NUM,
897 /* INT_LINE.reason_code REASON_CODE, changed for credit memo
898 reason*/
899 DECODE(CM_DI.Draft_Invoice_Num_Credited, NULL, '',
903 INT_LINE.BATCH_SOURCE_NAME SOURCE, /* Added for bug 3502647 */
900 DECODE(NVL(CM_DI.Write_Off_Flag, 'N'), 'N',
901 'PA_CREDIT_MEMO', 'PA_WRITE_OFF')) REASON_CODE,
902 /* INT_LINE.Interface_Line_Context SOURCE, Commented for bug 3502647 */
904 CM_DI.PROJFUNC_INVTRANS_RATE_DATE exchg_date,
905 CM_DI.PROJFUNC_INVTRANS_RATE_TYPE exchg_type,
906 CM_DI.INV_CURRENCY_CODE inv_curr_code,
907 INT_LINE.CONVERSION_RATE exchg_rate
908 FROM ra_interface_lines INT_LINE,
909 pa_draft_invoices CM_DI,
910 pa_draft_invoices ORG_DI,
911 ra_customer_trx TRX,
912 ra_batch_sources SOURCE
913 WHERE INT_LINE.interface_line_attribute1 = P_Project_Num
914 AND CM_DI.request_id = P_Request_Id
915 AND ltrim(rtrim(INT_LINE.interface_line_attribute2))
916 = ltrim(rtrim(to_char(CM_DI.Draft_invoice_num)))
917 AND CM_DI.project_id = P_Project_Id
918 AND CM_DI.PROJECT_ID = ORG_DI.PROJECT_ID
919 AND CM_DI.DRAFT_INVOICE_NUM_CREDITED = ORG_DI.DRAFT_INVOICE_NUM
920 AND CM_DI.DRAFT_INVOICE_NUM_CREDITED is NOT NULL
921 AND ORG_DI.SYSTEM_REFERENCE = TRX.CUSTOMER_TRX_ID
922 AND TRX.BATCH_SOURCE_ID = SOURCE.BATCH_SOURCE_ID
923 AND INT_LINE.Batch_Source_Name = SOURCE.NAME
924 AND INT_LINE.TRX_NUMBER = CM_DI.RA_INVOICE_NUMBER
925 AND INT_LINE.BATCH_SOURCE_NAME = P_Batch_Src; /* 2366742 */
926
927
928 CM_CAN_FLAG PA_DRAFT_INVOICES.CANCELED_FLAG%TYPE;
929 PA_ORIG_INV_AMT NUMBER;
930 PA_CM_INV_AMT NUMBER;
931 AR_ORIG_INV_AMT NUMBER;
932 CM_INV_CONV_AMT NUMBER;
933 PA_CM_INTERFACE_AMT RA_INTERFACE_LINES.AMOUNT%TYPE;
934 ROUND_OFF_AMT NUMBER;
935 L_MAX_LINE RA_INTERFACE_LINES.INTERFACE_LINE_ATTRIBUTE6%TYPE;
936 l_rate NUMBER;
937 PA_EXCHG_TYPE PA_DRAFT_INVOICES.INV_RATE_TYPE%TYPE;/* bug 2142736*/
938
939
940 BEGIN
941 FOR cur_get_inv_info IN get_invoice_info
942 LOOP
943 -- -----------------------------------------------------------------------
944 -- This Has been Commented out for R11.5 Bill in any Currency Project
945 -- For Bill in any Currency We assume that The User Would not be Changing
946 -- Currency and Conversion Attributes in AR
947 -- -----------------------------------------------------------------------
948 -- IF ((cur_get_inv_info.orig_inv_num is not null)
949 -- AND (cur_get_inv_info.invoice_currency_code <> P_Proj_Func_Cur))
950 -- THEN
951 --
952 -- IF cur_get_inv_info.CM_CAN_FLAG = 'Y'
953 -- -- Only Cancelled Case
954 -- THEN
955 --
956 -- /* In case of Cancellation, Update the line amount of the invoice line
957 -- with the line amount of the original invoice line amount in AR */
958 --
959 -- UPDATE RA_INTERFACE_LINES L
960 -- SET (L.AMOUNT,L.UNIT_SELLING_PRICE)
961 -- = ( SELECT sign(L.AMOUNT)*TRX.EXTENDED_AMOUNT,
962 -- sign(L.UNIT_SELLING_PRICE) *TRX.EXTENDED_AMOUNT
963 -- from RA_CUSTOMER_TRX_LINES TRX
964 -- Where TRX.CUSTOMER_TRX_ID
965 -- = cur_get_inv_info.CUST_TRX_ID
966 -- and ltrim(rtrim(TRX.INTERFACE_LINE_ATTRIBUTE6))
967 -- = ltrim(rtrim(L.INTERFACE_LINE_ATTRIBUTE6)))
968 -- WHERE L.INTERFACE_LINE_ATTRIBUTE1 = P_Project_Num
969 -- AND rtrim(ltrim(L.INTERFACE_LINE_ATTRIBUTE2))
970 -- = ltrim(rtrim(to_char(cur_get_inv_info.cm_inv_num)))
971 -- AND L.INTERFACE_LINE_CONTEXT = cur_get_inv_info.SOURCE
972 -- AND L.TRX_NUMBER = cur_get_inv_info.CM_TRX_NUM;
973 --
974 -- ELSE
975 -- -- Only Credit memo
976 --
977 -- /* Calculate the Original Invoice Amount in PA */
978 -- SELECT SUM(DII.AMOUNT)
979 -- INTO PA_ORIG_INV_AMT
980 -- FROM PA_DRAFT_INVOICE_ITEMS DII
981 -- WHERE DII.PROJECT_ID = P_Project_Id
982 -- AND DII.DRAFT_INVOICE_NUM = cur_get_inv_info.orig_inv_num;
983 --
984 -- /* Calculate the original Invoice amount in AR */
985 -- SELECT SUM(TRX_LINES.EXTENDED_AMOUNT)
986 -- INTO AR_ORIG_INV_AMT
987 -- FROM RA_CUSTOMER_TRX_LINES_ALL TRX_LINES
988 -- WHERE TRX_LINES.CUSTOMER_TRX_ID
989 -- = cur_get_inv_info.CUST_TRX_ID
990 -- AND TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 = P_Project_Num
991 -- AND rtrim(ltrim(TRX_LINES.INTERFACE_LINE_ATTRIBUTE2))
992 -- = to_char(cur_get_inv_info.orig_inv_num)
993 -- AND TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NOT NULL
994 -- AND TRX_LINES.INTERFACE_LINE_ATTRIBUTE2 IS NOT NULL
995 -- AND TRX_LINES.INTERFACE_LINE_ATTRIBUTE3 IS NOT NULL
996 -- AND TRX_LINES.INTERFACE_LINE_ATTRIBUTE4 IS NOT NULL
997 -- AND TRX_LINES.INTERFACE_LINE_ATTRIBUTE5 IS NOT NULL
998 -- AND TRX_LINES.INTERFACE_LINE_ATTRIBUTE6 IS NOT NULL;
999 --
1000 -- /* Calculate the Invoice Amount of the Crediting Invoice */
1001 -- SELECT SUM(DII.AMOUNT)
1002 -- INTO PA_CM_INV_AMT
1003 -- FROM PA_DRAFT_INVOICE_ITEMS DII
1004 -- WHERE DII.PROJECT_ID = P_Project_Id
1008 -- CM_INV_CONV_AMT:= PA_CM_INV_AMT * (AR_ORIG_INV_AMT/PA_ORIG_INV_AMT);
1005 -- AND DII.DRAFT_INVOICE_NUM = cur_get_inv_info.cm_inv_num;
1006 --
1007 -- /* Calculate the prorated value of the crediting Invoice */
1009 --
1010 -- /* Update the amount of the crediting Invoice in AR interface
1011 -- table */
1012 -- UPDATE RA_INTERFACE_LINES
1013 -- SET AMOUNT = PA_CURRENCY.round_trans_currency_amt(AMOUNT
1014 -- *(AR_ORIG_INV_AMT/PA_ORIG_INV_AMT) ,
1015 -- cur_get_inv_info.invoice_currency_code),
1016 -- UNIT_SELLING_PRICE = PA_CURRENCY.round_trans_currency_amt(
1017 -- UNIT_SELLING_PRICE *(AR_ORIG_INV_AMT/PA_ORIG_INV_AMT)
1018 -- ,cur_get_inv_info.invoice_currency_code)
1019 -- WHERE INTERFACE_LINE_ATTRIBUTE1 = P_Project_Num
1020 -- AND rtrim(ltrim(INTERFACE_LINE_ATTRIBUTE2))
1021 -- = to_char(cur_get_inv_info.cm_inv_num)
1022 -- AND BATCH_SOURCE_NAME = cur_get_inv_info.SOURCE
1023 -- AND TRX_NUMBER = cur_get_inv_info.CM_TRX_NUM;
1024 --
1025 --
1026 -- /* Calculate the converted invoice amount */
1027 -- SELECT SUM(L.AMOUNT),
1028 -- MAX(L.INTERFACE_LINE_ATTRIBUTE6)
1029 -- INTO PA_CM_INTERFACE_AMT,
1030 -- L_MAX_LINE
1031 -- FROM RA_INTERFACE_LINES L
1032 -- WHERE L.INTERFACE_LINE_ATTRIBUTE1 = P_Project_Num
1033 -- AND ltrim(rtrim(L.INTERFACE_LINE_ATTRIBUTE2))
1034 -- = to_char(cur_get_inv_info.cm_inv_num)
1035 -- AND L.BATCH_SOURCE_NAME = cur_get_inv_info.SOURCE
1036 -- AND L.TRX_NUMBER = cur_get_inv_info.CM_TRX_NUM;
1037 --
1038 --
1039 -- /* Calculate the round off error amount */
1040 -- ROUND_OFF_AMT := CM_INV_CONV_AMT- PA_CM_INTERFACE_AMT;
1041 --
1042 -- /* Adjust the round off error amount with the maximum invoice
1043 -- line */
1044 -- UPDATE RA_INTERFACE_LINES A
1045 -- SET A.AMOUNT = A.AMOUNT + ROUND_OFF_AMT,
1046 -- A.UNIT_SELLING_PRICE = A.UNIT_SELLING_PRICE + ROUND_OFF_AMT
1047 -- WHERE A.INTERFACE_LINE_ATTRIBUTE1 = P_Project_Num
1048 -- AND ltrim(rtrim(A.INTERFACE_LINE_ATTRIBUTE2))
1049 -- = to_char(cur_get_inv_info.cm_inv_num)
1050 -- AND A.INTERFACE_LINE_ATTRIBUTE6 = L_MAX_LINE
1051 -- AND A.BATCH_SOURCE_NAME = cur_get_inv_info.SOURCE
1052 -- AND A.TRX_NUMBER = cur_get_inv_info.CM_TRX_NUM;
1053 --
1054 -- END IF; /* Cancellation */
1055 -- END IF; /* Currency Check */
1056 /* Get the rate from invoice currency to functional currency
1057 If Invoice rate type is not 'User'then get the conversion rate
1058 else use the user rate.
1059 */
1060
1061 l_rate := NULL;
1062 PA_EXCHG_TYPE := cur_get_inv_info.exchg_type;/*Added for bug 2142736*/
1063 If cur_get_inv_info.inv_curr_code <> P_Proj_Func_Cur
1064 Then
1065 If cur_get_inv_info.exchg_type <> 'User'
1066 Then
1067 l_rate := GL_CURRENCY_API.get_rate (
1068 X_FROM_CURRENCY => cur_get_inv_info.inv_curr_code,
1069 X_TO_CURRENCY => P_Proj_Func_Cur,
1070 X_CONVERSION_TYPE => cur_get_inv_info.exchg_type,
1071 X_CONVERSION_DATE => cur_get_inv_info.exchg_date );
1072 Else
1073 /*Added for bug 2142736*/
1074 IF cur_get_inv_info.exchg_rate is not null
1075 then
1076 l_rate := cur_get_inv_info.exchg_rate;
1077 ELSE
1078 IF cur_get_inv_info.CM_CAN_FLAG = 'Y'
1079 THEN
1080 SELECT SUM(DII.AMOUNT) /SUM(DII.INV_AMOUNT)
1081 INTO l_rate
1082 FROM PA_DRAFT_INVOICE_ITEMS DII
1083 WHERE DII.PROJECT_ID = P_Project_Id
1084 AND DII.DRAFT_INVOICE_NUM = cur_get_inv_info.cm_inv_num;
1085
1086 PA_EXCHG_TYPE :='User';
1087 END IF;
1088 END IF;
1089 /*End of bug 2142736*/
1090 End If;
1091 End if;
1092
1093 /*Changed cur_get_inv_info.exchg_type to PA_EXCHG_TYPE*/
1094
1095 Update PA_DRAFT_INVOICES
1096 set ACCTD_CURR_CODE = P_Proj_Func_Cur,
1097 ACCTD_RATE_TYPE = decode(cur_get_inv_info.inv_curr_code,
1098 P_Proj_Func_Cur,NULL,PA_EXCHG_TYPE),
1099 ACCTD_RATE_DATE = decode(cur_get_inv_info.inv_curr_code,
1100 P_Proj_Func_Cur,NULL,cur_get_inv_info.exchg_date),
1101 ACCTD_EXCHG_RATE = decode(cur_get_inv_info.inv_curr_code,
1102 P_Proj_Func_Cur,NULL,l_rate)
1103 Where project_id = P_Project_Id
1104 And draft_invoice_num= cur_get_inv_info.cm_inv_num;
1105
1106
1107 /* Retention Changes : Adding the unbilled retention code combination Id */
1108
1109
1110 /* Insert the accounting entry for UBR/UER/WO/IC */
1111 Ins_Dist_lines(P_Transfer_Mode,
1112 P_Project_Id,
1113 P_Project_Num,
1114 cur_get_inv_info.cm_inv_num,
1115 cur_get_inv_info.invoice_currency_code,
1116 P_Proj_Func_Cur,
1117 P_WO_Ccid,
1118 P_UBR_Ccid,
1119 P_UER_Ccid,
1120 P_REC_Ccid,
1121 P_RND_Ccid,
1122 P_UNB_ret_Ccid,
1126 l_rate,
1123 cur_get_inv_info.REASON_CODE,
1124 cur_get_inv_info.SOURCE,
1125 cur_get_inv_info.CM_TRX_NUM,
1127 P_Retn_Acct_Flag);
1128
1129 END LOOP;
1130
1131 EXCEPTION
1132 WHEN OTHERS THEN
1133 RAISE;
1134 END Convert_Amt;
1135
1136 PROCEDURE Check_Invoice_acct_setup ( P_Func_code IN VARCHAR2,
1137 P_ou_retn_acct_flag IN VARCHAR2,
1138 X_Status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1139 AS
1140 l_dummy Varchar2(1);
1141 BEGIN
1142
1143 /* Retention Enhancement :
1144 Ou level Retention Flag enabled : If any auto accounting function transaction es disabled then
1145 error out.
1146 Ou level Retention Flag disabled : If any auto accounting function transaction is disabled other
1147 than the Unbilled Retention account then error out.
1148 */
1149
1150 /* This setups are valid only for invoice accounts
1151 It is not valid for revenue accounts like Realized Gains and Losses
1152 added where clause for patchset K */
1153
1154
1155 IF (P_ou_retn_acct_flag = 'Y') THEN
1156
1157
1158 SELECT 'x'
1159 INTO l_dummy
1160 FROM PA_FUNCTION_TRANSACTIONS
1161 WHERE FUNCTION_CODE = P_Func_code
1162 AND function_transaction_code NOT IN ('RLZD-GAIN', 'RLZD-LOSS')
1163 AND nvl(ENABLED_FLAG,'N') = 'N'
1164 AND rownum = 1;
1165
1166 ELSIF (P_ou_retn_acct_flag = 'N') THEN
1167
1168
1169 SELECT 'x'
1170 INTO l_dummy
1171 FROM PA_FUNCTION_TRANSACTIONS
1172 WHERE FUNCTION_CODE = P_Func_code
1173 /* AND function_transaction_code NOT IN ('UNB-RET','RLZD_GAIN', 'RLZD_LOSS') */
1174 AND function_transaction_code NOT IN ('UNB-RET','RLZD-GAIN', 'RLZD-LOSS')
1175 AND nvl(ENABLED_FLAG,'N') = 'N'
1176 AND rownum = 1;
1177
1178 --AND FUNCTION_TRANSACTION_CODE <> 'UNB-RET'
1179 END IF;
1180
1181
1182 X_Status := 'Y';
1183
1184 EXCEPTION
1185 When NO_DATA_FOUND
1186 Then
1187 X_Status := 'N';
1188 When Others
1189 Then
1190 raise;
1191
1192 END Check_Invoice_acct_setup;
1193
1194
1195 /* Retention Enhancement : Added the new param P_ou_retn_acct_flag and P_UNB_ret_ccid */
1196
1197
1198 PROCEDURE Check_ccid ( P_Rec_ccid IN NUMBER,
1199 P_UBR_ccid IN NUMBER,
1200 P_UER_ccid IN NUMBER,
1201 P_WO_ccid IN NUMBER,
1202 P_RND_ccid IN NUMBER,
1203 P_ou_retn_acct_flag IN VARCHAR2,
1204 P_UNB_ret_ccid IN NUMBER,
1205 X_Status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1206 AS
1207 l_dummy VARCHAR2(1);
1208 BEGIN
1209
1210 SELECT 'x'
1211 INTO l_dummy
1212 FROM gl_code_combinations
1213 WHERE code_combination_id = P_Rec_ccid;
1214
1215
1216 /****
1217 P_UBR_ccid, P_UER_ccid, P_WO_ccid will return null for Intercompany.
1218
1219 ***/
1220
1221 if (P_UBR_ccid is not null) then
1222 SELECT 'x'
1223 INTO l_dummy
1224 FROM gl_code_combinations
1225 WHERE code_combination_id = P_UBR_ccid;
1226 end if;
1227
1228 if (P_UER_ccid is not null) then
1229 SELECT 'x'
1230 INTO l_dummy
1231 FROM gl_code_combinations
1232 WHERE code_combination_id = P_UER_ccid;
1233 end if;
1234
1235
1236 if (P_WO_ccid is not null) then
1237 SELECT 'x'
1238 INTO l_dummy
1239 FROM gl_code_combinations
1240 WHERE code_combination_id = P_WO_ccid;
1241 end if;
1242
1243
1244 SELECT 'x'
1245 INTO l_dummy
1246 FROM gl_code_combinations
1247 WHERE code_combination_id = P_RND_ccid;
1248
1249
1250 /* Retention Enhancement : Validating the unbilled retention cc id */
1251
1252
1253 IF (P_ou_retn_acct_flag = 'Y') and (P_UNB_ret_ccid IS NOT NULL) THEN
1254
1255 SELECT 'x'
1256 INTO l_dummy
1257 FROM gl_code_combinations
1258 WHERE code_combination_id = P_UNB_ret_ccid;
1259
1260 END IF;
1261
1262
1263 X_Status := 'N';
1264
1265 EXCEPTION
1266
1267 When NO_DATA_FOUND
1268 Then
1269 X_Status := 'Y';
1270
1271 When Others
1272 Then
1273 Raise;
1274
1275 END Check_ccid;
1276
1277 PROCEDURE get_reject_reason ( P_reject_code IN var_arr_30,
1278 P_num_rec IN NUMBER,
1279 X_reject_reason OUT NOCOPY var_arr_80) --File.Sql.39 bug 4440895
1280 AS
1281 cursor get_reason ( l_rej_code in varchar2)
1282 is
1283 select nvl(meaning ,l_rej_code)
1284 from pa_lookups
1285 where lookup_type = 'TRANSFER REJECTION CODE'
1286 and lookup_code = l_rej_code;
1287
1288 i NUMBER;
1289 l_reason VARCHAR2(80);
1290 l_reject_code VARCHAR2(30):= NULL;
1291 l_reject_res VARCHAR2(80):= NULL;
1292
1293 BEGIN
1297 if (P_reject_code(i) is not null)
1294 for i in 1..P_num_rec
1295 loop
1296 X_reject_reason(i) := NULL;
1298 then
1299 if ((P_reject_code(i) <> l_reject_code)
1300 or (l_reject_code is NULL))
1301 then
1302 open get_reason ( P_reject_code(i));
1303 fetch get_reason into l_reason;
1304 close get_reason;
1305 X_reject_reason(i) := l_reason;
1306 l_reject_code := P_reject_code(i);
1307 l_reject_res := l_reason;
1308 else
1309 X_reject_reason(i) := l_reject_res;
1310 end if;
1311 end if;
1312 end loop;
1313
1314 END get_reject_reason;
1315
1316 /*===============================================================+
1317 | To get the AR Trx Type, instead of using the Invoice_Org_Type |
1318 | from PA_Implementations, use HR_Organization_Information |
1319 | table. There should be a rec for the org in this table |
1320 | satisfying the conditions detailed below. Org-Reorg changes. |
1321 | Also, use Proj_Org_Structure_version_id, Proj_Org_Structure_id|
1322 | and Proj_Start_Org_id |
1323 +===============================================================*/
1324 /* Added P_trans_type for bug 8687883*/
1325 PROCEDURE GET_TRX_CRMEMO_TYPES (P_business_group_id IN NUMBER,
1326 P_carrying_out_org_id IN NUMBER,
1327 P_proj_org_struct_version_id IN NUMBER,
1328 p_basic_language IN VARCHAR2,
1329 p_trans_date IN DATE,
1330 P_trans_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1331 P_crmo_trx_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1332 P_error_status OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1333 P_error_message OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1334
1335 org_flag BOOLEAN :=false;
1336 pl_org_id NUMBER :=0;
1337
1338 -- Cursor is used to select organization sort by level
1339 -- Removed business group id check from CONNECT BY AND START WITH clause
1340
1341 /* Modifications have been made to the cursor cur_org for BUG#1493157
1342 SELECT p_carrying_out_org_id from dual
1343 union all has been added to select the start organization into the cursor
1344 along with the other organizations that are selected from the connect By query.
1345 The second part of select has been modified so as to select organization_id_parent inplace of organization_id_child */
1346
1347 CURSOR cur_org IS
1348 SELECT p_carrying_out_org_id from dual
1349 union all
1350 SELECT struct.organization_id_parent organization_id
1351 FROM per_org_structure_elements struct
1352 CONNECT BY PRIOR
1353 struct.organization_id_parent = struct.organization_id_child
1354 /* AND struct.business_group_id = p_business_group_id */
1355 AND struct.org_structure_version_id + 0 =
1356 p_proj_org_struct_version_id
1357 START WITH struct.organization_id_child
1358 = p_carrying_out_org_id+0
1359 /* AND struct.business_group_id = p_business_group_id */
1360 AND struct.org_structure_version_id + 0 = p_proj_org_struct_version_id;
1361
1362
1363 -- Cursor is used to select trx type, credit memo type from AR
1364 -- Removed Business Group id check from WHERE clause
1365
1366 CURSOR cur_trx_types(ip_org_id NUMBER) IS
1367 SELECT TO_CHAR (type.cust_trx_type_id) cust_trx_type_id,
1368 TO_CHAR (type.credit_memo_type_id) credit_memo_type
1369 FROM ra_cust_trx_types type,
1370 hr_all_organization_units org,
1371 hr_all_organization_units_tl org_tl
1372 WHERE org_tl.organization_id = org.organization_id
1373 AND org_tl.language = p_basic_language
1374 AND org.organization_id = ip_org_id
1375 /* AND org.business_group_id = p_business_group_id */
1376 AND type.type = 'INV'
1377 AND trim(type.name) = trim(substrb(org_tl.name,1,17)) /*Modified for bug 6021078 */ /*Modified for bug 9213496*/
1378 /*Modified trim(substrb(org_tl.organization_id||org_tl.name,1,17) to trim(substrb(org_tl.name,1,17) for the bug 12607920*/
1379 AND type.attribute1=to_char(org.organization_id)/*Added for Bug 12607920 as FP*/
1380 AND NVL(p_trans_date,SYSDATE) BETWEEN type.start_date AND NVL (type.end_date, NVL(p_trans_date,SYSDATE)) /* Modified for bug 8687883*/
1381 AND EXISTS (
1382 SELECT 'x'
1383 FROM hr_organization_information orginfo
1384 WHERE orginfo.organization_id = ip_org_id
1385 AND orginfo.org_information_context = 'CLASS'
1386 AND orginfo.org_information1 = 'PA_INVOICE_ORG'
1387 AND orginfo.org_information2 = 'Y' );
1388
1389 trx_types_rec cur_trx_types%ROWTYPE; /* record declare */
1390
1391 BEGIN
1392 OPEN cur_org; /* open cursor */
1393
1394 LOOP /* OUTER Loop starts */
1395
1396 FETCH cur_org INTO pl_org_id;
1397
1398 EXIT WHEN cur_org%NOTFOUND OR org_flag; /* exit if no data found or trx found */
1402 LOOP /* Inner Loop */
1399
1400 OPEN cur_trx_types(pl_org_id);
1401
1403
1404 FETCH cur_trx_types INTO trx_types_rec;
1405 EXIT WHEN cur_trx_types%NOTFOUND;
1406
1407 p_trans_type := trx_types_rec.cust_trx_type_id; /* assign trx type ID to OUT variable */
1408 p_crmo_trx_type := trx_types_rec.credit_memo_type; /* Assign credit-memo ID to OUT variable */
1409 org_flag := true; /* Flag set to exit from loop(s) */
1410 exit; /* Exit from inner Loop */
1411
1412 END LOOP; /* Inner Loop ends */
1413
1414
1415 CLOSE cur_trx_types;
1416
1417 IF org_flag THEN /* If flag is true exit from OUTER loop */
1418
1419 exit;
1420
1421 END IF;
1422
1423 END LOOP; /* Outer Loop Ends */
1424 CLOSE cur_org;
1425
1426 IF NOT org_flag THEN /* If the flag is false */
1427 -- assign pa_imp values
1428
1429 BEGIN
1430 SELECT description into p_error_message
1431 FROM pa_lookups
1432 WHERE lookup_type='AR TRANSACTION TYPE MISSING' AND
1433 lookup_code ='AR_TRX_TYPE_NOT_FOUND';
1434
1435 EXCEPTION
1436 WHEN NO_DATA_FOUND THEN
1437 p_error_message := 'AR transaction type or credit memo type not defined for this organization.';
1438 p_error_status := 1;
1439
1440 /* ATG CHANGES */
1441
1442 P_trans_type := null;
1443 P_crmo_trx_type := null;
1444
1445
1446 WHEN OTHERS THEN
1447 p_error_message := sqlerrm;
1448 p_error_status := 1;
1449
1450 /* ATG CHANGES */
1451
1452 P_trans_type := null;
1453 P_crmo_trx_type := null;
1454
1455
1456 END;
1457
1458 END IF;
1459
1460 END GET_TRX_CRMEMO_TYPES;
1461
1462 /*===============================================================+
1463 | This procedure checks for Internal customers. if exists, |
1464 | then checks whether the transaction type are defined. |
1465 +===============================================================*/
1466 PROCEDURE CHECK_TRXTYPE_INTERNAL (
1467 P_proj_id IN NUMBER,
1468 P_trans_type IN VARCHAR2,
1469 P_crmo_trx_type IN VARCHAR2,
1470 P_reject_mesg OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1471 P_error_status OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1472 P_error_message OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1473 int_ctr NUMBER :=0;
1474 BEGIN
1475 SELECT count(*) into int_ctr
1476 FROM PA_Project_Customers
1477 WHERE Project_ID = P_proj_id
1478 AND NVL(Bill_Another_Project_Flag,'N') = 'Y';
1479
1480 if (int_ctr > 0) THEN
1481 if (P_crmo_trx_type = '0' OR P_trans_type = '0') THEN
1482 P_reject_mesg := 'NO_INV_TYPE';
1483 else
1484 P_reject_mesg := NULL;
1485 end if;
1486 else
1487 P_reject_mesg := NULL;
1488 end if;
1489
1490 EXCEPTION
1491 WHEN OTHERS THEN
1492 P_reject_mesg := 'NO_INV_TYPE';
1493 P_error_message := sqlerrm;
1494 P_error_status := 1;
1495
1496 END CHECK_TRXTYPE_INTERNAL;
1497
1498 /* This procedure is added for bug 2958951 */
1499 PROCEDURE GET_GL_DATE_PERIOD (P_inv_date IN DATE DEFAULT SYSDATE,
1500 P_ar_install_flag IN VARCHAR2,
1501 P_gl_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
1502 P_gl_period_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1503 P_pa_date OUT NOCOPY DATE, /* Added for bug 4202647*/ --File.Sql.39 bug 4440895
1504 P_pa_period_name OUT NOCOPY VARCHAR2, /* Added for bug 4202647*/ --File.Sql.39 bug 4440895
1505 P_error_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1506 P_error_msg_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1507
1508 l_inv_date DATE ;
1509 l_pa_date pa_cost_distribution_lines_all.pa_date%TYPE ;
1510 l_pa_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE;
1511 l_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE := NULL;
1512 l_gl_date pa_cost_distribution_lines_all.pa_date%TYPE;
1513 l_calling_module VARCHAR2(30);
1514 l_return_status NUMBER ;
1515 l_error_stage NUMBER ;
1516 l_stage VARCHAR2(30) ;
1517 l_error_code VARCHAR2(30) := NULL;
1518 l_exception_msg VARCHAR2(80) := NULL ;
1519
1520 l_pa_gl_app_id NUMBER := 8721 ;
1521 l_ar_app_id NUMBER := 222;
1522 l_app_id NUMBER := NULL ;
1523 l_sob_id NUMBER;
1524
1525 BEGIN
1526 l_stage := 'Inside get_gl_date';
1527 IF g1_debug_mode = 'Y' THEN
1528 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1529 END IF;
1530
1531 IF P_ar_install_flag = 'I' THEN
1532 l_stage := 'AR is installed';
1533 l_calling_module := 'AR_INSTALLED_INVOICE';
1534 l_app_id := l_ar_app_id;
1535 IF g1_debug_mode = 'Y' THEN
1536 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1537 END IF;
1538 ELSE
1539 l_stage := 'AR is not installed';
1540 l_calling_module := 'AR_NOT_INSTALLED_INVOICE';
1541 l_app_id := l_pa_gl_app_id;
1542 IF g1_debug_mode = 'Y' THEN
1543 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1544 END IF;
1545 END IF;
1546
1547 l_inv_date := p_inv_date;
1548 l_stage := 'About to get ou period info';
1549 IF g1_debug_mode = 'Y' THEN
1550 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1551 END IF;
1552
1553 PA_UTILS2.get_OU_period_information
1554 (p_reference_date => l_inv_date,
1555 p_calling_module => l_calling_module,
1556 x_pa_date => l_pa_date,
1557 x_pa_period_name => l_pa_period_name,
1558 x_gl_date => l_gl_date,
1559 x_gl_period_name => l_gl_period_name,
1560 x_return_status => l_return_status,
1561 x_error_code => l_error_code,
1562 x_error_stage => l_error_stage
1563 );
1564
1565 l_stage := 'After get_ou_period';
1566 IF g1_debug_mode = 'Y' THEN
1567 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1568 END IF;
1569
1570 IF l_error_code IS NOT NULL THEN
1571
1572 l_stage := 'Error code of get OU period';
1573 IF g1_debug_mode = 'Y' THEN
1574 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1575 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: Error Code: '||l_error_code);
1576 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: Error Stage: '||l_error_stage);
1577 END IF;
1578
1579 /* Added if for bug 3183174 */
1580 IF l_error_code = 'NO_PRVDR_GL_DATE' AND P_ar_install_flag = 'I' THEN
1581 l_error_code := 'NO_AR_PERIOD';
1582 END IF;
1583 END IF;
1584
1585 l_stage := 'All Done successfully';
1586 IF g1_debug_mode = 'Y' THEN
1587 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1588 END IF;
1589
1590 p_gl_date := l_gl_date;
1591 p_gl_period_name := l_gl_period_name;
1592 p_pa_date := l_pa_date;
1593 p_pa_period_name := l_pa_period_name;
1594 p_error_msg_code := l_error_code;
1595 p_error_stage := l_stage;
1596
1597 EXCEPTION
1598 WHEN OTHERS THEN
1599 p_error_Stage := l_stage;
1600 p_error_msg_code := l_error_code;
1601
1602 /* ATG Changes */
1603
1604 P_gl_date := null;
1605 P_gl_period_name := null;
1606 P_pa_date := null;
1607 P_pa_period_name := null;
1608
1609 RAISE;
1610 END get_gl_date_period;
1611
1612 END PA_INVOICE_XFER;