[Home] [Help]
PACKAGE BODY: APPS.JAI_FBT_PROCESS_P
Source
1 PACKAGE BODY JAI_FBT_PROCESS_P AS
2 --$Header: jainfbtprc.plb 120.10 2011/03/03 11:44:45 abezgam ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| jai_fbt_process_p.plb |
10 --| |
11 --| DESCRIPTION |
12 --| To fetch eligible ap invoices and gl journals for FBT assessment |
13 --| and calculate the tax and insert data into jai_fbt_repository |
14 --| table |
15 --| |
16 --| PROCEDURE LIST |
17 --| PROCEDURE Fbt_Inv_Process |
18 --| PROCEDURE Insert_Fbt_Repository |
19 --| PROCEDURE Calculate_Fbt_Amount |
20 --| FUNCTION Check_Inv_Validation |
21 --| FUNCTION Get_Natural_Acc_Seg |
22 --| FUNCTION Get_Balance_Acc_Seg |
23 --| FUNCTION currency_conversion |
24 --| |
25 --| HISTORY |
26 --| 2007/10/11 Kevin Cheng Created |
27 --| 2008/03/21 Kevin Cheng bug#6908012 |
28 --| Add legal entity criteria for illegible invoice |
29 --| fetching. |
30 --| 2008/07/22 Eric Ma Code change for 11.5 backport |
31 --| 2008/07/29 Eric Ma Code change for 11.5 backport |
32 --| 2008/08/11 Xiao Lv Code change for 11i new changes |
33 --| 2008/08/25 Xiao Lv Fixing bug#7347306, bug#7347401 |
34 --| Commented this piece of code in Fbt_Inv_process |
35 --| UPDATE jai_fbt_repository |
36 --| SET settlement_id = NULL |
37 --| WHERE legal_entity_id = pn_legal_entity_id |
38 --| AND period_start_date >= ld_start_date |
39 --| AND period_end_date <= ld_end_date |
40 --| AND settlement_id IS NOT NULL; |
41 --| Commented the benifit_type_code query condition of |
42 --| judging settlement flag |
43 --| 2008/11/06 Xiao Lv Code change for FBT new changes in R12 |
44 --| 2008/12/23 Xiao Lv Fixing bug#7661991 |
45 --| 2008/12/26 Xiao Lv Fixing bug#7670949 |
46 --| 2008/12/30 Jia Li Fixing bug#7675638 |
47 --| 2009/04/22 Jia Li Fixing Bug#8417642 |
48 --+======================================================================*/
49
50 --==========================================================================
51 -- FUNCTION NAME:
52 --
53 -- currency_conversion Private
54 --
55 -- DESCRIPTION:
56 --
57 -- As jai_cmn_utils_pkg.currency_conversion is not available in the IL 11.5
58 -- This function is added for compalibity with R12 FBT code.
59 --
60 -- PARAMETERS:
61 -- In: c_set_of_books_id sob id
62 -- In: c_from_currency_code currency code
63 -- In: c_conversion_date currency conversion date
64 -- In: c_conversion_type currency conversion type
65 -- In: c_conversion_rate currency conversion rate
66 --
67 -- DESIGN REFERENCES:
68 --
69 --
70 -- CHANGE HISTORY:
71 --
72 -- 17-JUL-2008 Eric Ma created
73
74
75 FUNCTION currency_conversion
76 (
77 c_set_of_books_id IN NUMBER
78 ,c_from_currency_code IN VARCHAR2
79 ,c_conversion_date IN DATE
80 ,c_conversion_type IN VARCHAR2
81 ,c_conversion_rate IN NUMBER
82 ) RETURN NUMBER IS
83 dummy NUMBER;
84 Begin
85 dummy :=0;
86 return dummy;
87 END currency_conversion;
88
89 --==========================================================================
90 -- FUNCTION NAME:
91 --
92 -- Check_Inv_Validation Public
93 --
94 -- DESCRIPTION:
95 --
96 -- This function checks whether the invoice is validate or not
97 --
98 -- PARAMETERS:
99 -- In: pn_invoice_id Identifier of ap invoices
100 --
101 -- DESIGN REFERENCES:
102 -- FBT Technical Design Document 1.1.doc
103 --
104 -- CHANGE HISTORY:
105 --
106 -- 11-OCT-2007 Kevin Cheng created
107
108 FUNCTION Check_Inv_Validation
109 ( pn_invoice_id IN NUMBER
110 )
111 RETURN VARCHAR2
112 IS
113 dummy varchar2(10);
114 Begin
115 dummy:='NULL';
116 return NULL;
117 END Check_Inv_Validation;
118
119 --==========================================================================
120 -- FUNCTION NAME:
121 --
122 -- Get_Natural_Acc_Seg Public
123 --
124 -- DESCRIPTION:
125 --
126 -- This function is used to get the natural account segment value
127 --
128 -- PARAMETERS:
129 -- In: pv_col_name Identifier of natural account name
130 -- pn_ccid Identifier of code combination id
131 --
132 -- DESIGN REFERENCES:
133 -- FBT Technical Design Document 1.1.doc
134 --
135 -- CHANGE HISTORY:
136 --
137 -- 11-OCT-2007 Kevin Cheng created
138 -- 14-NOV-2008 Xiao Lv modified
139 -- Return value type changes to VARCHAR2 from NUMBER
140
141 FUNCTION Get_Natural_Acc_Seg
142 ( pv_col_name IN VARCHAR2
143 , pn_ccid IN NUMBER
144 )
145 RETURN VARCHAR2
146 IS
147 dummy varchar2(10);
148 Begin
149 dummy:='NULL';
150 return NULL;
151 END Get_Natural_Acc_Seg;
152
153 --==========================================================================
154 -- FUNCTION NAME:
155 --
156 -- Get_Balance_Acc_Seg Public
157 --
158 -- DESCRIPTION:
159 --
160 -- This function is used to get the balance account segment value
161 --
162 -- PARAMETERS:
163 -- In: pv_col_name Identifier of natural account name
164 -- pn_ccid Identifier of code combination id
165 --
166 -- DESIGN REFERENCES:
167 -- FBT Technical Design Document 1.1.doc
168 --
169 -- CHANGE HISTORY:
170 --
171 -- 11-AUG-2008 Xiao Lv created
172
173 FUNCTION Get_Balance_Acc_Seg
174 ( pv_col_name IN VARCHAR2
175 , pn_ccid IN NUMBER
176 )
177 RETURN VARCHAR2
178 IS
179 dummy varchar2(10);
180 Begin
181 dummy:='NULL';
182 return NULL;
183 END Get_Balance_Acc_Seg;
184
185
186 --==========================================================================
187 -- PROCEDURE NAME:
188 --
189 -- Calculate_Fbt_Amount Private
190 --
191 -- DESCRIPTION:
192 --
193 -- This procedure calculates the various tax amounts which need to be
194 -- populated in jai_fbt_repository table
195 --
196 -- PARAMETERS:
197 -- In: pn_legal_entity_id Identifier of legal entity
198 -- pv_fringe_benefit_type_code Identifier of FB type code
199 -- pn_inv_dist_id Identifier of invoice dist id
200 -- pn_inv_dist_amt Identifier of invoice dist amount
201 -- pv_currency Identifier of invoice currency
202 -- pv_exchange_rate_type Identifier of exchange rate type
203 -- pd_exchange_date Identifier of exchange date
204 -- pn_source Identifier of Source, 'Others' or 'Payables'
205 -- pn_je_header_id Identifier of GL JE Header
206 -- pn_fbt_year Identifier of FBT Year
207 --
208 -- Out: x_fbt_repository_type Returns the repository record
209 --
210 -- DESIGN REFERENCES:
211 -- FBT Technical Design Document 1.1.doc
212 --
213 -- CHANGE HISTORY:
214 --
215 -- 11-OCT-2007 Kevin Cheng created
216 -- 11-AUG-2008 Xiao Lv modified for 11i new changes
217 -- 06-NOV-2008 Xiao Lv modified for R12 new changes
218 -- 23-DEV-2008 Xiao Lv modified for bug#7661991
219 -- 26-DEV-2008 Xiao Lv modified for bug#7670949
220
221 PROCEDURE Calculate_Fbt_Amount
222 ( pn_legal_entity_id IN NUMBER
223 , pv_fringe_benefit_type_code IN VARCHAR2
224 , pn_inv_dist_id IN NUMBER
225 , pn_inv_dist_amt IN NUMBER
226 , pv_currency IN VARCHAR2
227 , pv_exchange_rate_type IN VARCHAR2
228 , pd_exchange_date IN DATE
229 , pn_source IN VARCHAR2
230 , pn_je_header_id IN NUMBER
231 , pn_fbt_year IN NUMBER
232 , x_fbt_repository_type OUT NOCOPY JAI_FBT_REPOSITORY%ROWTYPE
233 )
234 IS
235 dummy varchar2(10);
236 Begin
237 dummy:='NULL';
238 END Calculate_Fbt_Amount;
239
240 --==========================================================================
241 -- PROCEDURE NAME:
242 --
243 -- Insert_Fbt_Repository Private
244 --
245 -- DESCRIPTION:
246 --
247 -- This procedure insert one record into table jai_fbt_repository
248 --
249 -- PARAMETERS:
250 -- In: p_fbt_repository Identifier of record containing
251 -- fbt repository information
252 --
253 -- DESIGN REFERENCES:
254 -- FBT Technical Design Document 1.1.doc
255 --
256 -- CHANGE HISTORY:
257 --
258 -- 11-OCT-2007 Kevin Cheng created
259 -- 11-AUG-2008 Xiao Lv modified for 11i new changes
260 -- 14-NOV-2008 Xiao Lv modified for R12 new changes
261
262 PROCEDURE Insert_Fbt_Repository
263 ( p_fbt_repository IN JAI_FBT_REPOSITORY%ROWTYPE
264 )
265 IS
266 dummy varchar2(10);
267 Begin
268 dummy:='NULL';
269 END Insert_Fbt_Repository;
270
271 --==========================================================================
272 -- PROCEDURE NAME:
273 --
274 -- Fbt_Inv_Process Public
275 --
276 -- DESCRIPTION:
277 --
278 -- This is the main procedure which will be called by the concurrent
279 -- program to check eligible invoices, calculate FBT taxes and insert
280 -- data into jai_fbt_repository table
281 --
282 -- PARAMETERS:
283 -- In: pn_legal_entity_id Identifier of legal entity
284 -- pv_start_date Identifier of period start date
285 -- pv_end_date Identifier of period end date
286 -- pv_fringe_benefit_type_code Identifier of FB type code
287 -- pv_generate_return Identifier of supplier id
288 --
289 -- Out: pv_errbuf Returns the error if concurrent program
290 -- does not execute completely
291 -- pv_retcode Returns success or failure
292 --
293 -- DESIGN REFERENCES:
294 -- FBT Technical Design Document 1.1.doc
295 --
296 -- CHANGE HISTORY:
297 --
298 -- 11-OCT-2007 Kevin Cheng created
299 -- 21-MAR-2008 Kevin Cheng bug#6908012
300 -- Add legal entity criteria for illegible invoice
301 -- fetching.
302 -- 29-Jul-2008 Eric commented out code in the cursor get_eligible_invoices_cur
303 -- to leave the invoice case of accrue_on_receipt_flag ='Y' to GL module
304 -- 11-Aug-2008 Lv Xiao modified for 11i new changes.
305 -- 25-Aug-2008 Lv Xiao fix bug#7347306 and bug#7347401.
306 -- 06-Nov-2008 Lv Xiao modified for FBT R12 new changes.
307 -- 30-Dec-2008 Li Jia fix bug#7675638
308 -- 22-Apr-2009 Jia Li Modified for Bug#8417642.
309 -- Issue: FBT Processing program run with error message:
310 -- ORA-06502: PL/SQL: numeric or value error: character to number conversion error
311 -- Fixed: Used varchar2 type to replace number type for dist_natural_account_value column.
312
313 PROCEDURE Fbt_Inv_Process
314 ( pv_errbuf OUT NOCOPY VARCHAR2
315 , pv_retcode OUT NOCOPY VARCHAR2
316 , pn_legal_entity_id IN NUMBER
317 , pn_fbt_year IN NUMBER
318 , pv_start_date IN VARCHAR2
319 , pv_end_date IN VARCHAR2
320 , pv_fringe_benefit_type_code IN VARCHAR2
321 )
322 IS
323 dummy varchar2(10);
324 Begin
325 dummy:='NULL';
326 END Fbt_Inv_Process;
327
328 END JAI_FBT_PROCESS_P; --END OF PACKAGE