[Home] [Help]
PACKAGE BODY: APPS.PA_INVOICE_TRANSFER
Source
1 PACKAGE BODY PA_Invoice_Transfer AS
2 -- $Header: PAXVTRXB.pls 120.4.12010000.2 2008/10/01 03:46:37 arbandyo ship $
3
4 Procedure Validate_Tax_Id ( P_Project_ID IN Number,
5 P_Draft_Invoice_Num IN Number,
6 P_Trx_Date IN Date,
7 X_Reject_Code OUT NOCOPY Varchar2) IS --File.Sql.39 bug 4440895
8
9 pl_dummy varchar2(1);
10
11 BEGIN
12
13 X_Reject_Code := NULL;
14
15 -- Check any invoice line has invalid tax id
16
17 Select 'x'
18 Into pl_dummy
19 from pa_draft_invoice_items dii
20 where draft_invoice_num = P_Draft_Invoice_Num
21 and project_id = P_Project_ID
22 -- and output_vat_tax_id is not null
23 and output_tax_classification_code is not null
24 and not exists ( select 'x'
25 from pa_output_tax_code_txn_v tax
26 where tax.tax_code = dii.output_tax_classification_code)
27 /* where P_Trx_Date >= start_date_active
28 and P_Trx_Date <= nvl(end_date_active,P_Trx_Date) */ /* commented for bug 5484859 */
29 -- and vat_tax_id = output_vat_tax_id)
30 and rownum = 1;
31
32 X_Reject_Code := 'INVALID_TAX_CODE';
33
34 Exception
35
36 When NO_DATA_FOUND
37 Then
38 Null;
39
40 When Others
41 Then
42 Raise;
43
44 END Validate_Tax_Id;
45
46 Procedure Client_Extn_Driver( P_Project_ID IN Number,
47 P_Draft_Invoice_Num IN Number,
48 P_Draft_Invoice_Type IN Varchar2,
49 P_AR_Trx_Type IN Varchar2,
50 X_AR_Trx_Type OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
51 X_Reject_Code OUT NOCOPY Varchar2 ) IS --File.Sql.39 bug 4440895
52
53 Invoice_Class Varchar2(15);
54 Invoice_Amount Number;
55 Project_Amount Number;
56 Invoice_Date Date;
57 Inv_Currency_Code Varchar2(15);
58 Project_Currency_Code Varchar2(15);
59 Err_Status Number;
60 Reject_Code Varchar2(30);
61 P_AR_Trx_Type_ID Number := to_number(P_AR_Trx_Type);
62 -- Trx type ID is passed as character string.
63 AR_Trx_Type Varchar2(30);
64 AR_Trx_Type_ID Number;
65
66 BEGIN
67
68 X_Reject_Code := NULL;
69 X_AR_Trx_Type := NULL;
70 Reject_Code := NULL;
71 AR_Trx_Type := NULL;
72
73 -- Determine the Invoice Date
74
75 Select I.Invoice_Date, I.Inv_Currency_Code, P.Project_Currency_Code
76 Into Invoice_date, Inv_Currency_Code, Project_Currency_Code
77 From PA_Draft_Invoices I, PA_Projects P
78 WHERE P.Project_id = P_Project_ID
79 AND I.Project_id = P_Project_ID
80 AND Draft_Invoice_Num = P_Draft_Invoice_Num;
81
82 -- Determine the invoice amount
83
84 Select Sum(Item.Amount), Sum(Item.Inv_Amount)
85 Into Project_Amount, Invoice_Amount
86 From PA_Draft_Invoice_Items Item
87 Where Item.Project_ID = P_Project_ID
88 And Item.Draft_Invoice_Num = P_Draft_Invoice_Num
89 And Item.Invoice_Line_Type <> 'NET ZERO ADJUSTMENT';
90
91 -- Determine the Invoice Class
92
93 IF P_Draft_Invoice_Type = 'P' then
94 Invoice_Class := 'INVOICE';
95 ELSIF P_Draft_Invoice_Type = 'WO' then
96 Invoice_Class := 'WRITE_OFF';
97 ELSE
98 Select decode(Orig.Canceled_Flag,'Y','CANCEL','CREDIT_MEMO')
99 Into Invoice_Class
100 From PA_Draft_Invoices Orig,
101 PA_Draft_Invoices Curr
102 Where Curr.Project_ID = P_Project_ID
103 AND Curr.Draft_Invoice_Num = P_Draft_Invoice_Num
104 AND Orig.Project_ID = Curr.Project_ID
105 AND Orig.Draft_Invoice_Num = Curr.Draft_Invoice_Num_Credited;
106 END IF;
107
108 -- Call the Client Extn
109
110 PA_Client_Extn_Inv_Transfer.Get_AR_Trx_Type( P_Project_ID,
111 P_Draft_Invoice_Num,
112 Invoice_Class,
113 Project_Amount,
114 Project_Currency_Code,
115 Inv_Currency_Code,
116 Invoice_Amount,
117 P_AR_Trx_Type_ID,
118 AR_Trx_Type_ID,
119 Err_Status );
120
121 -- Validate the Returned AR Trx Type and Null Tax id in Invoice Line
122
123 IF (Err_Status = 0) AND (AR_Trx_Type_ID IS NULL) then
124 -- Validate the orig AR Trx Type,Null Tax id in Invoice Line and return
125 Validate_AR_Trx_Type( P_AR_Trx_Type_ID,P_Project_ID,P_Draft_Invoice_Num,
126 Invoice_Date, AR_Trx_Type, Reject_Code );
127 X_Reject_Code := Reject_Code;
128 X_AR_Trx_Type := P_AR_Trx_Type;
129 Elsif (Err_Status < 0) then
130 X_Reject_Code := 'PA_CLIENT_EXTN_ORACLE_ERROR';
131 X_AR_Trx_Type := NULL;
132 Elsif (Err_Status > 0) then
133 X_Reject_Code := 'PA_CLIENT_EXTN_APP_ERROR';
134 X_AR_Trx_Type := NULL;
135 Else
136 -- Validate the client AR Trx Type,Null Tax id in Invoice Line and return
137 Validate_AR_Trx_Type( AR_Trx_Type_ID,P_Project_ID,P_Draft_Invoice_Num,
138 Invoice_Date, AR_Trx_Type, Reject_Code );
139 X_Reject_Code := Reject_Code;
140 X_AR_Trx_Type := to_char(AR_Trx_Type_ID); -- return ID as character string.
141 END IF;
142
143 -- If Invoice line has invalid tax code , error out the invoice
144
145 Validate_Tax_Id ( P_Project_ID,
146 P_Draft_Invoice_Num,
147 Invoice_Date,
148 Reject_Code);
149 If Reject_Code Is not Null
150 Then
151 X_Reject_Code := Reject_Code;
152 End If;
153
154 EXCEPTION
155
156 When OTHERS then
157
158 /* ATG Changes */
159 X_AR_Trx_Type := null;
160
161 RAISE;
162
163 END Client_Extn_Driver;
164
165 -------------------------------
166
167 Procedure Validate_AR_Trx_Type ( P_AR_Trx_Type_ID IN Number,
168 P_Project_Id IN Number,
169 P_Draft_Inv_Num IN Number,
170 P_Invoice_Date IN Date,
171 X_AR_Trx_Type OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
172 X_Reject_Code OUT NOCOPY Varchar2 ) IS --File.Sql.39 bug 4440895
173
174 AR_Trx_Type Varchar2(30) := NULL;
175 /* AR_Tax_Flag Varchar2(1) := NULL; Commented for bug 7348841 */
176 l_dummy Varchar2(1) ;
177 l_step Number := 0;
178
179 BEGIN
180
181 X_Reject_Code := NULL;
182 X_AR_Trx_Type := NULL;
183
184 /* Included for Bug#2423626 to check creation_sign */
185 -- Validate AR Trx Sign
186 l_step := 5;
187
188 Select Name
189 /* Tax_Calculation_Flag Commented for bug 7348841 */
190 Into AR_Trx_Type
191 /* AR_Tax_Flag commented for bug 7348841 */
192 From RA_Cust_Trx_Types
193 Where Cust_Trx_Type_ID = P_AR_Trx_Type_ID
194 AND Type IN ('INV','CM')
195 AND Creation_Sign = 'A'
196 AND Start_Date <= P_Invoice_Date
197 AND NVL(End_Date, P_Invoice_Date+1) >= P_Invoice_Date
198 AND Rownum = 1;
199 /* end of code fix for Bug#2423626 */
200
201 -- Validate AR Trx Type
202 l_step := 10;
203
204 Select Name
205 /* Tax_Calculation_Flag commented for bug 7348841 */
206 Into AR_Trx_Type
207 /* AR_Tax_Flag coomented for bug 7348841 */
208 From RA_Cust_Trx_Types
209 Where Cust_Trx_Type_ID = P_AR_Trx_Type_ID
210 AND Type IN ('INV','CM')
211 AND Accounting_Affect_Flag = 'Y'
212 AND Creation_Sign = 'A'
213 AND POST_TO_GL = 'Y'
214 AND Start_Date <= P_Invoice_Date
215 AND NVL(End_Date, P_Invoice_Date+1) >= P_Invoice_Date
216 AND Rownum = 1;
217
218 -- Validation OK for AR Transaction type
219
220 -- Validate whether any line with null tax id is to be transferred
221
222 l_step := 20;
223
224 /* Added the check draft_inv_line_num_credited is null as the
225 following check is necessary only for invoices and not for credit memos - For bug 2973011 */
226
227 /* If AR_Tax_Flag = 'Y'
228 Then
229 Select 'x'
230 Into l_dummy
231 From PA_Draft_Invoice_Items
232 Where Project_id = P_Project_Id
233 And Draft_Invoice_num = P_Draft_Inv_Num
234 AND output_tax_classification_code IS NULL
235 -- And Output_vat_tax_id is Null
236 AND draft_inv_line_num_credited is null /* For bug 2973011
237 And Invoice_Line_type <> 'NET ZERO ADJUSTMENT'
238 And rownum = 1;
239
240 X_Reject_Code := 'NO_OUTPUT_TAX_CODE';
241
242 End if; commented for bug 7348841 */
243
244 EXCEPTION
245
246 -- Error
247
248 When NO_DATA_FOUND then
249 If l_step = 5 /* Bug#2423626 */
250 Then /* Bug#2423626 */
251 X_Reject_Code := 'INVALID_AR_TRX_SIGN'; /* Bug#2423626 */
252 X_AR_Trx_Type := NULL; /* Bug#2423626 */
253 Elsif l_step = 10
254 Then
255 X_Reject_Code := 'INVALID_AR_TRX_TYPE';
256 X_AR_Trx_Type := NULL;
257 Elsif l_step = 20
258 Then
259 X_Reject_Code := NULL;
260 End If;
261
262 /* ATG Changes */
263 X_AR_Trx_Type := null;
264
265
266 When OTHERS then
267
268 /* ATG Changes */
269 X_AR_Trx_Type := null;
270
271 RAISE;
272
273 END Validate_AR_Trx_Type;
274
275 -------------------------------
276
277 END PA_Invoice_Transfer;