[Home] [Help]
PACKAGE: APPS.JAI_OPEN_API_PKG
Source
1 PACKAGE JAI_OPEN_API_PKG AUTHID CURRENT_USER AS
2 /* $Header: jai_open_api.pls 120.4 2011/06/30 03:34:25 zhhou noship $ */
3 --+=======================================================================+
4 --| Copyright (c) 2007 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| jai_open_api.pls
10 --|
11 --| DESCRIPTION
12 --|
13 --| This package contains the following PL/SQL tables/procedures/functions
14 --| to import taxes from legacy system to OFI.
15 --|
16 --|
17 --|
18 --|
19 --|
20 --| TYPE LIEST
21 --|
22 --|
23 --| PROCEDURE LIST
24 --| populate_jai_interface_lines
25 --| purge_jai_interface_lines
26 --| get_external_dist_account_ccid
27 --|
28 --|
29 --| HISTORY
30 --| 01-Mar-2011 Xiao Lv/Zhiwei Hou Created
31 --| 24-Mar-2011 Xiao for bug#11903425
32 --| Enhance feature of tax category preview in Interface tax lines UI.
33 --| 31-Mar-2011 Xiao for bug#11936390
34 --| For code review comments, add GL future status: GV_PERIOD_FUTURE_STATUS
35 --|
36 --+======================================================================*/
37 GV_MODULE_PREFIX VARCHAR2 (100) := 'JAI_OPEN_API_PKG';
38 GV_CONSTANT_MISCELLANEOUS VARCHAR2 (20) := 'MISCELLANEOUS';
39 GV_CONSTANT_ITEM VARCHAR2 (20) := 'ITEM';
40 GV_NOT_MATCH_TYPE VARCHAR2 (20) := 'NOT_MATCHED';
41 GV_JAI_AP_INVOICE_LINES VARCHAR2 (100) := 'JAI_AP_INVOICE_LINES';
42 GV_LINES_CREATEED VARCHAR2 (10) := 'NO';
43
44 GV_IMPORT_MODULE VARCHAR2(20);
45 GN_ORG_ID NUMBER;
46 GN_ORGANIZATIONID NUMBER;
47 GN_LOCATION_ID NUMBER;
48
49 GV_IMPORT_SUCCESS VARCHAR2 (20) := 'SUCCESS';
50 GV_IMPORT_FAILURE VARCHAR2 (20) := 'FAILURE';
51
52 GV_PROCESS_INITIALIZE VARCHAR2 (20) := 'INITIALIZE';
53 GV_PROCESS_VALIDATION VARCHAR2 (20) := 'VALIDATION';
54 GV_PROCESS_POPULATION VARCHAR2 (20) := 'POPULATION';
55 GV_PROCESS_CALCULATION VARCHAR2 (20) := 'CALCULATION';
56 GV_PROCESS_ACCOUNTING VARCHAR2 (20) := 'ACCOUNTING';
57 GV_PROCESS_UPDATING VARCHAR2 (20) := 'UPDATING';
58 GV_TAXABLE_EVENT_STANDALONE VARCHAR2 (20) := 'STANDARD';
59 GV_TAXABLE_EVENT_EXTERNAL VARCHAR2 (20) := 'EXTERNAL';
60 GV_TAXABLE_BASIS_LINEAMT VARCHAR2 (20) := 'LINE_AMOUNT';
61 GV_TAXABLE_BASIS_AV VARCHAR2 (20) := 'ASSESSABLE_VALUE';
62
63 GV_LINE_STATUS_ALL VARCHAR2(10) := 'ALL';
64 GV_LINE_STATUS_ERROR VARCHAR2(10) := 'ERROR';
65 GV_LINE_STATUS_SUCCESS VARCHAR2(10) := 'SUCCESS';
66
67
68 GV_VALIDATED VARCHAR2(30) := 'APPROVED';
69 GV_NEED_RE_VALIDATION VARCHAR2(30) := 'NEEDS REAPPROVAL';
70 GV_NEVER_VALIDATED VARCHAR2(30) := 'NEVER APPROVED';
71 GV_UNVALIDATED_PREPAYMENT VARCHAR2(30) := 'UNAPPROVED';
72 GV_UNPAID_PREPAYMENT VARCHAR2(30) := 'UNPAID';
73
74 GV_ERROR_TYPE_LINE VARCHAR2(30) := 'LINE';
75 GV_ERROR_TYPE_TAX VARCHAR2(30) := 'TAX';
76 GV_ERROR_TYPE_DISTRIBUTION VARCHAR2(30) := 'DISTRIBUTION';
77 GV_ORG VARCHAR2(20) := 'ORG';
78 GV_ORGANIZATION VARCHAR2(20) := 'ORGANIZATION';
79 GV_LOCATION VARCHAR2(20) := 'LOCATION';
80 GV_COMBINATION VARCHAR2(20) := 'COMBINATION';
81
82 GN_REQUEST_ID NUMBER;
83
84 GN_GL_APPLICATION_ID NUMBER := 101;
85 GN_AR_APPLICATION_ID NUMBER := 222;
86 GN_AP_APPLICATION_ID NUMBER := 200;
87
88 GV_PERIOD_CLOSE_STATUS VARCHAR2(10) := 'O';
89 GV_PERIOD_FUTURE_STATUS VARCHAR2(10) := 'F';
90
91 TYPE ref_cur_typ IS REF CURSOR;
92 TYPE tax_rec_typ IS RECORD(
93 tax_id JAI_CMN_TAXES_ALL.tax_id%TYPE
94 , lno JAI_CMN_TAX_CTG_LINES.line_no%TYPE
95 , p_1 JAI_CMN_TAX_CTG_LINES.precedence_1%TYPE
96 , p_2 JAI_CMN_TAX_CTG_LINES.precedence_2%TYPE
97 , p_3 JAI_CMN_TAX_CTG_LINES.precedence_3%TYPE
98 , p_4 JAI_CMN_TAX_CTG_LINES.precedence_4%TYPE
99 , p_5 JAI_CMN_TAX_CTG_LINES.precedence_5%TYPE
100 , p_6 JAI_CMN_TAX_CTG_LINES.precedence_6%TYPE
101 , p_7 JAI_CMN_TAX_CTG_LINES.precedence_7%TYPE
102 , p_8 JAI_CMN_TAX_CTG_LINES.precedence_8%TYPE
103 , p_9 JAI_CMN_TAX_CTG_LINES.precedence_9%TYPE
104 , p_10 JAI_CMN_TAX_CTG_LINES.precedence_10%TYPE
105 , tax_rate JAI_CMN_TAXES_ALL.tax_rate%TYPE
106 , tax_amount JAI_CMN_TAXES_ALL.tax_amount%TYPE
107 , uom_code JAI_CMN_TAXES_ALL.uom_code%TYPE
108 , valid_date JAI_CMN_TAXES_ALL.end_date%TYPE
109 , tax_type_val NUMBER
110 , mod_cr_percentage JAI_CMN_TAXES_ALL.mod_cr_percentage%TYPE
111 , vendor_id JAI_CMN_TAXES_ALL.vendor_id%TYPE
112 , tax_type JAI_CMN_TAXES_ALL.tax_type%TYPE
113 , rounding_factor JAI_CMN_TAXES_ALL.rounding_factor%TYPE
114 , adhoc_flag JAI_CMN_TAXES_ALL.adhoc_flag%TYPE
115 , tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%TYPE
116 , inclusive_tax_flag JAI_CMN_TAXES_ALL.inclusive_tax_flag%TYPE
117 , qty_rate number --Added by zhiwei for Bug#12604133 on 20110623
118 , vat_flag JAI_CMN_TAXES_ALL.vat_flag%type--Added by zhiwei for Bug#12604133 on 20110623
119 );
120
121
122 TYPE tax_typ IS RECORD
123 ( internal_trx_id jai_interface_lines_all.internal_trx_id%TYPE
124 , internal_trx_line_id jai_interface_lines_all.internal_trx_line_id%TYPE
125 , interface_line_id jai_interface_lines_all.interface_line_id%TYPE
126 , tax_id JAI_CMN_TAXES_ALL.tax_id%TYPE
127 , p_1 JAI_CMN_TAX_CTG_LINES.precedence_1%TYPE
128 , p_2 JAI_CMN_TAX_CTG_LINES.precedence_2%TYPE
129 , p_3 JAI_CMN_TAX_CTG_LINES.precedence_3%TYPE
130 , p_4 JAI_CMN_TAX_CTG_LINES.precedence_4%TYPE
131 , p_5 JAI_CMN_TAX_CTG_LINES.precedence_5%TYPE
132 , p_6 JAI_CMN_TAX_CTG_LINES.precedence_6%TYPE
133 , p_7 JAI_CMN_TAX_CTG_LINES.precedence_7%TYPE
134 , p_8 JAI_CMN_TAX_CTG_LINES.precedence_8%TYPE
135 , p_9 JAI_CMN_TAX_CTG_LINES.precedence_9%TYPE
136 , p_10 JAI_CMN_TAX_CTG_LINES.precedence_10%TYPE
137 , tax_type JAI_CMN_TAXES_ALL.tax_type%TYPE
138 , tax_rate JAI_CMN_TAXES_ALL.tax_rate%TYPE
139 , uom_code JAI_CMN_TAXES_ALL.uom_code%TYPE
140 , adhoc_flag JAI_CMN_TAXES_ALL.adhoc_flag%TYPE
141 , mod_cr_percentage JAI_CMN_TAXES_ALL.mod_cr_percentage%TYPE
142 , qty_rate number --Added by zhiwei for Bug#12604133 on 20110623
143 , lno JAI_CMN_TAX_CTG_LINES.line_no%TYPE
144 , tax_amount NUMBER
145 , func_tax_amount NUMBER
146 , base_tax_amount NUMBER
147 );
148
149 --==========================================================================
150 -- PROCEDURE NAME:
151 --
152 -- populate_jai_interface_lines Public
153 --
154 -- DESCRIPTION:
155 --
156 -- This procedure is main entrance procedure used by concurrent to import
157 -- OFI taxes from OFI Open Interface.
158 --
159 -- PARAMETERS:
160 -- In: errbuf OUT NOCOPY VARCHAR2
161 -- retcode OUT NOCOPY VARCHAR2
162 -- pv_import_module VARCHAR2
163 -- pn_org_id NUMBER DEFAULT NULL
164 -- pn_organization_id NUMBER DEFAULT NULL
165 -- pn_location_id NUMBER DEFAULT NULL
166 -- pn_party_id NUMBER DEFAULT NULL
167 -- pn_party_site_id NUMBER DEFAULT NULL
168 -- pv_transaction_num_from VARCHAR2 DEFAULT NULL
169 -- pv_transaction_num_to VARCHAR2 DEFAULT NULL
170 --
171 --
172 -- Out:
173 --
174 -- PRE-COND :
175 --
176 -- EXCEPTIONS: Taxes can be imported to AR/AP by OFI Open Interface.
177 --
178 --===========================================================================
179 PROCEDURE populate_jai_interface_lines(
180 errbuf OUT NOCOPY VARCHAR2
181 , retcode OUT NOCOPY VARCHAR2
182 , pv_import_module VARCHAR2
183 , pn_org_id NUMBER DEFAULT NULL
184 , pn_organization_id NUMBER DEFAULT NULL
185 , pn_location_id NUMBER DEFAULT NULL
186 , pn_party_id NUMBER DEFAULT NULL
187 , pn_party_site_id NUMBER DEFAULT NULL
188 , pv_transaction_num_from VARCHAR2 DEFAULT NULL
189 , pv_transaction_num_to VARCHAR2 DEFAULT NULL
190 );
191
192 --==========================================================================
193 -- PROCEDURE NAME:
194 --
195 -- populate_jai_interface_lines Public
196 --
197 -- DESCRIPTION:
198 --
199 -- This procedure is main entrance procedure used by concurrent to import
200 -- OFI taxes from OFI Open Interface.
201 --
202 -- PARAMETERS:
203 -- In: errbuf OUT NOCOPY VARCHAR2
204 -- retcode OUT NOCOPY VARCHAR2
205 -- pv_import_module VARCHAR2
206 -- pn_org_id NUMBER DEFAULT NULL
207 -- pn_organization_id NUMBER DEFAULT NULL
208 -- pn_location_id NUMBER DEFAULT NULL
209 -- pn_party_id NUMBER DEFAULT NULL
210 -- pn_party_site_id NUMBER DEFAULT NULL
211 -- pv_transaction_num_from VARCHAR2 DEFAULT NULL
212 -- pv_transaction_num_to VARCHAR2 DEFAULT NULL
213 --
214 --
215 -- Out:
216 --
217 -- PRE-COND :
218 --
219 -- EXCEPTIONS: Taxes can be imported to AR/AP by OFI Open Interface.
220 --
221 --===========================================================================
222 PROCEDURE purge_jai_interface_lines(
223 errbuf OUT NOCOPY VARCHAR2
224 , retcode OUT NOCOPY VARCHAR2
225 , pv_import_module VARCHAR2
226 , pn_org_id NUMBER
227 , pn_organization_id NUMBER
228 , pn_location_id NUMBER
229 , pn_party JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE
230 , pn_party_site JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE
231 , pv_transaction_num_from VARCHAR2
232 , pv_transaction_num_to VARCHAR2
233 , pv_line_status VARCHAR2
234 );
235 --==========================================================================
236 -- PROCEDURE NAME:
237 --
238 -- get_external_dist_account_ccid Public
239 --
240 -- DESCRIPTION:
241 --
242 -- This procedure is fetch tax account from JAI Interface tax table for
243 -- 'External' source.
244 --
245 -- PARAMETERS:
246 -- In: pn_invoice_id NUMBER
247 -- pn_line_number NUMBER
248 -- pn_tax_line_no NUMBER
249 --
250 --
251 -- Out:
252 --
253 -- PRE-COND :
254 --
255 -- EXCEPTIONS: Get tax account from JAI Interface tax table 'External' source.
256 --
257 --===========================================================================
258 FUNCTION get_external_dist_account_ccid(
259 pn_invoice_id IN NUMBER
260 , pn_line_number IN NUMBER
261 , pn_tax_line_no IN NUMBER
262 ) RETURN NUMBER;
263
264
265 END JAI_OPEN_API_PKG;