[Home] [Help]
PACKAGE BODY: APPS.PA_INVOICE_TIEBACK
Source
1 PACKAGE BODY PA_INVOICE_TIEBACK as
2 /* $Header: PAXVINTB.pls 120.0.12000000.3 2007/10/18 06:18:47 srathi ship $ */
3
4 PROCEDURE Validate_inv_acct_amt
5 ( P_project_id IN num_arr,
6 P_draft_inv_num IN num_arr,
7 P_project_num IN var_arr_25,
8 P_cust_trx_id IN num_arr,
9 P_user_id IN NUMBER,
10 P_request_id IN NUMBER,
11 P_num_of_rec IN NUMBER,
12 P_out_error OUT NOCOPY var_arr_01 )
13 AS
14 Cursor cur_get_line_amt ( l_draft_inv_num number,
15 l_project_id number)
16 Is
17 SELECT dii.acct_amount acct_amt,
18 dii.line_num line_num
19 FROM pa_draft_invoice_items dii
20 WHERE dii.project_id = l_project_id
21 AND dii.draft_invoice_num = l_draft_inv_num;
22
23 Cursor cur_get_ar_amt ( l_project_num VARCHAR2,
24 l_cust_trx_id NUMBER,
25 l_draft_invoice_num NUMBER,
26 l_line_num NUMBER )
27 Is
28 /* Commented the code change done for bug 4022244 and modified the previous
29 query as below for this bug 6443623 */
30 Select sum(tgl.acctd_amount)
31 from ra_cust_trx_line_gl_dist_all tgl,
32 ra_customer_trx_lines trx
33 where trx.customer_trx_id = l_cust_trx_id
34 and trim(trx.interface_line_attribute6)
35 = to_char(l_line_num)
36 and rtrim(ltrim(trx.interface_line_attribute1)) = l_project_num
37 and tgl.customer_trx_line_id = trx.customer_trx_line_id
38 and tgl.account_class = 'REV';
39
40 /* Bug 4022244. Modified the cursor query to add a new join for interface_line_context */
41 /* Commenting the code for Bug 6443623
42 Select sum(tgl.acctd_amount)
43 from ra_cust_trx_line_gl_dist_all tgl,
44 ra_customer_trx_lines trx_lines,
45 ra_customer_trx trx, ra_batch_sources source
46 where trx_lines.customer_trx_id = trx.customer_trx_id
47 and trx.batch_source_id = source.batch_source_id
48 and trx_lines.interface_line_context = source.name
49 and trx_lines.customer_trx_id = l_cust_trx_id
50 and to_number(trunc(trx_lines.interface_line_attribute6))
51 = l_line_num
52 and rtrim(ltrim(trx_lines.interface_line_attribute1)) = l_project_num
53 and tgl.customer_trx_line_id = trx_lines.customer_trx_line_id
54 and tgl.account_class = 'REV';
55 */
56 /* Commenting the following for bug 4022244.
57 Select sum(tgl.acctd_amount)
58 from ra_cust_trx_line_gl_dist_all tgl,
59 ra_customer_trx_lines trx
60 where trx.customer_trx_id = l_cust_trx_id
61 and to_number(trunc(trx.interface_line_attribute6))
62 = l_line_num
63 and rtrim(ltrim(trx.interface_line_attribute1)) = l_project_num
64 and tgl.customer_trx_line_id = trx.customer_trx_line_id
65 and tgl.account_class = 'REV';
66 */
67
68 l_error_message VARCHAR2(80);
69 l_error_flag VARCHAR2(1);
70 l_acct_amount NUMBER;
71
72 BEGIN
73
74 /*** First fetch the error message ***/
75 begin
76 SELECT Meaning
77 INTO l_error_message
78 FROM PA_Lookups
79 WHERE Lookup_Type = 'TRANSFER REJECTION CODE'
80 AND Lookup_Code = 'EXCHANGE_RATE_CHANGE';
81
82 exception
83 when NO_DATA_FOUND then
84 l_error_message := 'EXCHANGE_RATE_CHANGE';
85 end;
86 /*** End fetching the error message ***/
87
88 For i in 1..P_num_of_rec
89 Loop
90 P_out_error(i) := NULL;
91 l_error_flag := 'N';
92
93 for cur_get_line_amt_rec in cur_get_line_amt ( P_draft_inv_num(i),
94 P_project_id(i))
95 loop
96 open cur_get_ar_amt ( P_project_num(i),
97 P_cust_trx_id(i),
98 P_draft_inv_num(i),
99 cur_get_line_amt_rec.line_num);
100 fetch cur_get_ar_amt into l_acct_amount;
101 close cur_get_ar_amt;
102
103 if l_acct_amount <> cur_get_line_amt_rec.acct_amt
104 then
105 l_error_flag := 'Y';
106 exit;
107 end if;
108
109 end loop;
110
111 if l_error_flag = 'Y'
112 then
113 P_out_error(i) := 'Y';
114
115 insert into pa_distribution_warnings
116 (
117 PROJECT_ID, DRAFT_INVOICE_NUM, LAST_UPDATE_DATE, LAST_UPDATED_BY,
118 CREATION_DATE, CREATED_BY, REQUEST_ID, WARNING_MESSAGE
119 )
120 VALUES
121 (
122 P_project_id(i), P_draft_inv_num(i), sysdate, P_user_id,
123 sysdate, P_user_id, P_request_id, l_error_message
124 );
125 End if;
126 End Loop;
127 EXCEPTION
128 When Others
129 Then
130 Raise;
131 END Validate_inv_acct_amt;
132
133 END PA_INVOICE_TIEBACK;