[Home] [Help]
PACKAGE BODY: APPS.PA_INVOICE_XFER
Source
1 PACKAGE BODY PA_INVOICE_XFER as
2 /* $Header: PAXITCAB.pls 120.4.12010000.2 2008/12/26 07:09:46 dlella 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
54 l_rate NUMBER;
55 l_func_line NUMBER;
56 l_mau NUMBER;
57 l_sp 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,
190 P_Interface_attr6 IN VARCHAR2,
191 P_Interface_attr7 IN VARCHAR2,
192 P_Func_currency_code IN VARCHAR2,
193 P_Inv_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,
320 P_Interface_attr2, P_Interface_attr3,
321 P_Interface_attr4, P_Interface_attr5,
322 P_Interface_attr6, P_Interface_attr7,
323 'UER', P_Batch_Src,
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
377 This procedure will compute the gl entries for an invoice line,and
374 END Create_RND_Entries;
375
376 /**
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
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)))
447 -- AND I.INTERFACE_LINE_CONTEXT = P_Batch_Src -- Performance Bug 2695303
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
487 l_inv_uer NUMBER;
484 AND DII.DRAFT_INVOICE_NUM = P_Inv_Num
485 AND DII.LINE_NUM = to_number(l_line_num);
486
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,
559 get_woff_line_rec.ATTR3,
560 get_woff_line_rec.ATTR4,
561 get_woff_line_rec.ATTR5,
562 get_woff_line_rec.ATTR6,
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,
604 P_Interface_attr7 => cur_get_acct_info.attr7,
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,
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)
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
670 )
671 VALUES ('REV',
672 cur_get_line_info.INV_UBR,
673 NULL,
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)
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 */
707 l_inv_uer,
704 ORG_ID
705 )
706 VALUES ('REV',
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,
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 */
785 ORG_ID
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
823 if called. -- bug 2615572*/
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
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, '',
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 */
903 INT_LINE.BATCH_SOURCE_NAME SOURCE, /* Added 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
926
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 */
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
1005 -- AND DII.DRAFT_INVOICE_NUM = cur_get_inv_info.cm_inv_num;
1006 --
1007 -- /* Calculate the prorated value of the crediting Invoice */
1008 -- CM_INV_CONV_AMT:= PA_CM_INV_AMT * (AR_ORIG_INV_AMT/PA_ORIG_INV_AMT);
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 */
1030 -- L_MAX_LINE
1027 -- SELECT SUM(L.AMOUNT),
1028 -- MAX(L.INTERFACE_LINE_ATTRIBUTE6)
1029 -- INTO PA_CM_INTERFACE_AMT,
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,
1123 cur_get_inv_info.REASON_CODE,
1124 cur_get_inv_info.SOURCE,
1125 cur_get_inv_info.CM_TRX_NUM,
1126 l_rate,
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 */
1156
1153
1154
1155 IF (P_ou_retn_acct_flag = 'Y') THEN
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
1294 for i in 1..P_num_rec
1295 loop
1296 X_reject_reason(i) := NULL;
1297 if (P_reject_code(i) is not 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|
1325 P_carrying_out_org_id IN NUMBER,
1322 | and Proj_Start_Org_id |
1323 +===============================================================*/
1324 PROCEDURE GET_TRX_CRMEMO_TYPES (P_business_group_id IN NUMBER,
1326 P_proj_org_struct_version_id IN NUMBER,
1327 p_basic_language IN VARCHAR2,
1328 P_trans_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1329 P_crmo_trx_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1330 P_error_status OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1331 P_error_message OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1332
1333 org_flag BOOLEAN :=false;
1334 pl_org_id NUMBER :=0;
1335
1336 -- Cursor is used to select organization sort by level
1337 -- Removed business group id check from CONNECT BY AND START WITH clause
1338
1339 /* Modifications have been made to the cursor cur_org for BUG#1493157
1340 SELECT p_carrying_out_org_id from dual
1341 union all has been added to select the start organization into the cursor
1342 along with the other organizations that are selected from the connect By query.
1343 The second part of select has been modified so as to select organization_id_parent inplace of organization_id_child */
1344
1345 CURSOR cur_org IS
1346 SELECT p_carrying_out_org_id from dual
1347 union all
1348 SELECT struct.organization_id_parent organization_id
1349 FROM per_org_structure_elements struct
1350 CONNECT BY PRIOR
1351 struct.organization_id_parent = struct.organization_id_child
1352 /* AND struct.business_group_id = p_business_group_id */
1353 AND struct.org_structure_version_id + 0 =
1354 p_proj_org_struct_version_id
1355 START WITH struct.organization_id_child
1356 = p_carrying_out_org_id+0
1357 /* AND struct.business_group_id = p_business_group_id */
1358 AND struct.org_structure_version_id + 0 = p_proj_org_struct_version_id;
1359
1360
1361 -- Cursor is used to select trx type, credit memo type from AR
1362 -- Removed Business Group id check from WHERE clause
1363
1364 CURSOR cur_trx_types(ip_org_id NUMBER) IS
1365 SELECT TO_CHAR (type.cust_trx_type_id) cust_trx_type_id,
1366 TO_CHAR (type.credit_memo_type_id) credit_memo_type
1367 FROM ra_cust_trx_types type,
1368 hr_all_organization_units org,
1369 hr_all_organization_units_tl org_tl
1370 WHERE org_tl.organization_id = org.organization_id
1371 AND org_tl.language = p_basic_language
1372 AND org.organization_id = ip_org_id
1373 /* AND org.business_group_id = p_business_group_id */
1374 AND type.type = 'INV'
1375 AND type.name = substrb(org_tl.organization_id ||org_tl.name,1,17) /*Modified for bug 6021078 */
1376 AND SYSDATE BETWEEN type.start_date AND NVL (type.end_date, SYSDATE+1)
1377 AND EXISTS (
1378 SELECT 'x'
1379 FROM hr_organization_information orginfo
1380 WHERE orginfo.organization_id = ip_org_id
1381 AND orginfo.org_information_context = 'CLASS'
1382 AND orginfo.org_information1 = 'PA_INVOICE_ORG'
1383 AND orginfo.org_information2 = 'Y' );
1384
1385 trx_types_rec cur_trx_types%ROWTYPE; /* record declare */
1386
1387 BEGIN
1388 OPEN cur_org; /* open cursor */
1389
1390 LOOP /* OUTER Loop starts */
1391
1392 FETCH cur_org INTO pl_org_id;
1393
1394 EXIT WHEN cur_org%NOTFOUND OR org_flag; /* exit if no data found or trx found */
1395
1396 OPEN cur_trx_types(pl_org_id);
1397
1398 LOOP /* Inner Loop */
1399
1400 FETCH cur_trx_types INTO trx_types_rec;
1401 EXIT WHEN cur_trx_types%NOTFOUND;
1402
1403 p_trans_type := trx_types_rec.cust_trx_type_id; /* assign trx type ID to OUT variable */
1404 p_crmo_trx_type := trx_types_rec.credit_memo_type; /* Assign credit-memo ID to OUT variable */
1405 org_flag := true; /* Flag set to exit from loop(s) */
1406 exit; /* Exit from inner Loop */
1407
1408 END LOOP; /* Inner Loop ends */
1409
1410
1411 CLOSE cur_trx_types;
1412
1413 IF org_flag THEN /* If flag is true exit from OUTER loop */
1414
1415 exit;
1416
1417 END IF;
1418
1419 END LOOP; /* Outer Loop Ends */
1420 CLOSE cur_org;
1421
1422 IF NOT org_flag THEN /* If the flag is false */
1423 -- assign pa_imp values
1424
1425 BEGIN
1426 SELECT description into p_error_message
1427 FROM pa_lookups
1428 WHERE lookup_type='AR TRANSACTION TYPE MISSING' AND
1432 WHEN NO_DATA_FOUND THEN
1429 lookup_code ='AR_TRX_TYPE_NOT_FOUND';
1430
1431 EXCEPTION
1433 p_error_message := 'AR transaction type or credit memo type not defined for this organization.';
1434 p_error_status := 1;
1435
1436 /* ATG CHANGES */
1437
1438 P_trans_type := null;
1439 P_crmo_trx_type := null;
1440
1441
1442 WHEN OTHERS THEN
1443 p_error_message := sqlerrm;
1444 p_error_status := 1;
1445
1446 /* ATG CHANGES */
1447
1448 P_trans_type := null;
1449 P_crmo_trx_type := null;
1450
1451
1452 END;
1453
1454 END IF;
1455
1456 END GET_TRX_CRMEMO_TYPES;
1457
1458 /*===============================================================+
1459 | This procedure checks for Internal customers. if exists, |
1460 | then checks whether the transaction type are defined. |
1461 +===============================================================*/
1462 PROCEDURE CHECK_TRXTYPE_INTERNAL (
1463 P_proj_id IN NUMBER,
1464 P_trans_type IN VARCHAR2,
1465 P_crmo_trx_type IN VARCHAR2,
1466 P_reject_mesg OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1467 P_error_status OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1468 P_error_message OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1469 int_ctr NUMBER :=0;
1470 BEGIN
1471 SELECT count(*) into int_ctr
1472 FROM PA_Project_Customers
1473 WHERE Project_ID = P_proj_id
1474 AND NVL(Bill_Another_Project_Flag,'N') = 'Y';
1475
1476 if (int_ctr > 0) THEN
1477 if (P_crmo_trx_type = '0' OR P_trans_type = '0') THEN
1478 P_reject_mesg := 'NO_INV_TYPE';
1479 else
1480 P_reject_mesg := NULL;
1481 end if;
1482 else
1483 P_reject_mesg := NULL;
1484 end if;
1485
1486 EXCEPTION
1487 WHEN OTHERS THEN
1488 P_reject_mesg := 'NO_INV_TYPE';
1489 P_error_message := sqlerrm;
1490 P_error_status := 1;
1491
1492 END CHECK_TRXTYPE_INTERNAL;
1493
1494 /* This procedure is added for bug 2958951 */
1495 PROCEDURE GET_GL_DATE_PERIOD (P_inv_date IN DATE DEFAULT SYSDATE,
1496 P_ar_install_flag IN VARCHAR2,
1497 P_gl_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
1498 P_gl_period_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1499 P_pa_date OUT NOCOPY DATE, /* Added for bug 4202647*/ --File.Sql.39 bug 4440895
1500 P_pa_period_name OUT NOCOPY VARCHAR2, /* Added for bug 4202647*/ --File.Sql.39 bug 4440895
1501 P_error_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1502 P_error_msg_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1503
1504 l_inv_date DATE ;
1505 l_pa_date pa_cost_distribution_lines_all.pa_date%TYPE ;
1506 l_pa_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE;
1507 l_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE := NULL;
1508 l_gl_date pa_cost_distribution_lines_all.pa_date%TYPE;
1509 l_calling_module VARCHAR2(30);
1510 l_return_status NUMBER ;
1511 l_error_stage NUMBER ;
1512 l_stage VARCHAR2(30) ;
1513 l_error_code VARCHAR2(30) := NULL;
1514 l_exception_msg VARCHAR2(80) := NULL ;
1515
1516 l_pa_gl_app_id NUMBER := 8721 ;
1517 l_ar_app_id NUMBER := 222;
1518 l_app_id NUMBER := NULL ;
1519 l_sob_id NUMBER;
1520
1521 BEGIN
1522 l_stage := 'Inside get_gl_date';
1523 IF g1_debug_mode = 'Y' THEN
1524 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1525 END IF;
1526
1527 IF P_ar_install_flag = 'I' THEN
1528 l_stage := 'AR is installed';
1529 l_calling_module := 'AR_INSTALLED_INVOICE';
1530 l_app_id := l_ar_app_id;
1531 IF g1_debug_mode = 'Y' THEN
1532 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1533 END IF;
1534 ELSE
1535 l_stage := 'AR is not installed';
1536 l_calling_module := 'AR_NOT_INSTALLED_INVOICE';
1537 l_app_id := l_pa_gl_app_id;
1538 IF g1_debug_mode = 'Y' THEN
1539 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1540 END IF;
1541 END IF;
1542
1543 l_inv_date := p_inv_date;
1544 l_stage := 'About to get ou period info';
1545 IF g1_debug_mode = 'Y' THEN
1546 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1547 END IF;
1548
1549 PA_UTILS2.get_OU_period_information
1550 (p_reference_date => l_inv_date,
1551 p_calling_module => l_calling_module,
1552 x_pa_date => l_pa_date,
1553 x_pa_period_name => l_pa_period_name,
1554 x_gl_date => l_gl_date,
1555 x_gl_period_name => l_gl_period_name,
1556 x_return_status => l_return_status,
1557 x_error_code => l_error_code,
1558 x_error_stage => l_error_stage
1559 );
1560
1561 l_stage := 'After get_ou_period';
1562 IF g1_debug_mode = 'Y' THEN
1563 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1564 END IF;
1565
1566 IF l_error_code IS NOT NULL THEN
1567
1568 l_stage := 'Error code of get OU period';
1569 IF g1_debug_mode = 'Y' THEN
1570 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1571 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: Error Code: '||l_error_code);
1572 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: Error Stage: '||l_error_stage);
1573 END IF;
1574
1575 /* Added if for bug 3183174 */
1576 IF l_error_code = 'NO_PRVDR_GL_DATE' AND P_ar_install_flag = 'I' THEN
1577 l_error_code := 'NO_AR_PERIOD';
1578 END IF;
1579 END IF;
1580
1581 l_stage := 'All Done successfully';
1582 IF g1_debug_mode = 'Y' THEN
1583 PA_MCB_INVOICE_PKG.log_message('GET_GL_DATE_PERIOD: '||l_stage);
1584 END IF;
1585
1586 p_gl_date := l_gl_date;
1587 p_gl_period_name := l_gl_period_name;
1588 p_pa_date := l_pa_date;
1589 p_pa_period_name := l_pa_period_name;
1590 p_error_msg_code := l_error_code;
1591 p_error_stage := l_stage;
1592
1593 EXCEPTION
1594 WHEN OTHERS THEN
1595 p_error_Stage := l_stage;
1596 p_error_msg_code := l_error_code;
1597
1598 /* ATG Changes */
1599
1600 P_gl_date := null;
1601 P_gl_period_name := null;
1602 P_pa_date := null;
1603 P_pa_period_name := null;
1604
1605 RAISE;
1606 END get_gl_date_period;
1607
1608 END PA_INVOICE_XFER;