[Home] [Help]
PACKAGE: APPS.AP_IMPORT_INVOICES_PKG
Source
1 PACKAGE AP_IMPORT_INVOICES_PKG AUTHID CURRENT_USER AS
2 /* $Header: apiimpts.pls 120.43.12020000.5 2013/03/07 13:57:20 harsanan ship $ */
3
4 -- Declaring the global variables
5 g_pa_allows_overrides VARCHAR2(30) := 'N';
6 g_source VARCHAR2(80);
7 g_debug_switch VARCHAR2(1) := 'N';
8 g_inv_sysdate DATE;
9 g_program_application_id NUMBER;
10 g_program_id NUMBER;
11 g_conc_request_id NUMBER;
12 g_invoices_table VARCHAR2(30);
13 g_invoice_lines_table VARCHAR2(30);
14 g_instructions_table VARCHAR2(30);
15 g_segment_delimiter VARCHAR2(10); -- 6349739 - for RETEK
16
17 -- Added for Payment Requests
18 g_invoice_id NUMBER;
19
20 -- Added for tax call if invoice has tax line BUG 10158760
21 --g_inv_has_tax_line VARCHAR(5) := 'N'; --bug 15862708
22
23
24 -- For Period_name in Get_item_line_info/
25 -- Get_non_item_line_info
26 lg_many_controller fnd_plsql_cache.cache_1tom_controller_type;
27 lg_generic_storage fnd_plsql_cache.generic_cache_values_type;
28
29 -- For fnd_flex.validate_segs in v_check_line_project_info/
30 -- v_check_line_account_info
31 lg_many_controller1 fnd_plsql_cache.cache_1tom_controller_type;
32 lg_generic_storage1 fnd_plsql_cache.generic_cache_values_type;
33
34 -- For gl_code_combinations in get_item_line_info/
35 -- insert_from_dis_sets/
36 -- overlay_for_po_matched/
37 -- get_non_item_line_info/
38 -- prorate_dists
39 lg_many_controller2 fnd_plsql_cache.cache_1tom_controller_type;
40 lg_generic_storage2 fnd_plsql_cache.generic_cache_values_type;
41
42 -- Bug 5572876
43 lg_incometax_controller fnd_plsql_cache.cache_1tom_controller_type;
44 lg_incometax_storage fnd_plsql_cache.generic_cache_values_type;
45
46 -- Bug 5572876
47 lg_incometaxr_controller fnd_plsql_cache.cache_1tom_controller_type;
48 lg_incometaxr_storage fnd_plsql_cache.generic_cache_values_type;
49
50 TYPE r_invoice_info_rec IS RECORD (
51 invoice_id AP_INVOICES_INTERFACE.invoice_id%TYPE,
52 invoice_num AP_INVOICES_INTERFACE.invoice_num%TYPE,
53 invoice_type_lookup_code
54 AP_INVOICES_INTERFACE.invoice_type_lookup_code%TYPE,
55 invoice_date AP_INVOICES_INTERFACE.invoice_date%TYPE,
56 po_number AP_INVOICES_INTERFACE.po_number%TYPE,
57 vendor_id AP_INVOICES_INTERFACE.vendor_id%TYPE,
58 vendor_num AP_INVOICES_INTERFACE.vendor_num%TYPE,
59 vendor_name AP_INVOICES_INTERFACE.vendor_name%TYPE,
60 vendor_site_id AP_INVOICES_INTERFACE.vendor_site_id%TYPE,
61 vendor_site_code AP_INVOICES_INTERFACE.vendor_site_code%TYPE,
62 invoice_amount AP_INVOICES_INTERFACE.invoice_amount%TYPE,
63 invoice_currency_code AP_INVOICES_INTERFACE.invoice_currency_code%TYPE,
64 exchange_rate AP_INVOICES_INTERFACE.exchange_rate%TYPE,
65 exchange_rate_type AP_INVOICES_INTERFACE.exchange_rate_type%TYPE,
66 exchange_date AP_INVOICES_INTERFACE.exchange_date%TYPE,
67 terms_id AP_INVOICES_INTERFACE.terms_id%TYPE,
68 terms_name AP_INVOICES_INTERFACE.terms_name%TYPE,
69 terms_date AP_INVOICES_INTERFACE.terms_date%TYPE,
70 description AP_INVOICES_INTERFACE.description%TYPE,
71 awt_group_id AP_INVOICES_INTERFACE.awt_group_id%TYPE,
72 awt_group_name AP_INVOICES_INTERFACE.awt_group_name%TYPE,
73 pay_awt_group_id AP_INVOICES_INTERFACE.pay_awt_group_id%TYPE,--bug6639866
74 pay_awt_group_name AP_INVOICES_INTERFACE.pay_awt_group_name%TYPE,--bug6639866
75 amount_applicable_to_discount
76 AP_INVOICES_INTERFACE.amount_applicable_to_discount%TYPE,
77 last_update_date AP_INVOICES_INTERFACE.last_update_date%TYPE,
78 last_updated_by AP_INVOICES_INTERFACE.last_updated_by%TYPE,
79 last_update_login AP_INVOICES_INTERFACE.last_update_login%TYPE,
80 creation_date AP_INVOICES_INTERFACE.creation_date%TYPE,
81 created_by AP_INVOICES_INTERFACE.created_by%TYPE,
82 status AP_INVOICES_INTERFACE.status%TYPE,
83 attribute_category AP_INVOICES_INTERFACE.attribute_category%TYPE,
84 attribute1 AP_INVOICES_INTERFACE.attribute1%TYPE,
85 attribute2 AP_INVOICES_INTERFACE.attribute2%TYPE,
86 attribute3 AP_INVOICES_INTERFACE.attribute3%TYPE,
87 attribute4 AP_INVOICES_INTERFACE.attribute4%TYPE,
88 attribute5 AP_INVOICES_INTERFACE.attribute5%TYPE,
89 attribute6 AP_INVOICES_INTERFACE.attribute6%TYPE,
90 attribute7 AP_INVOICES_INTERFACE.attribute7%TYPE,
91 attribute8 AP_INVOICES_INTERFACE.attribute8%TYPE,
92 attribute9 AP_INVOICES_INTERFACE.attribute9%TYPE,
93 attribute10 AP_INVOICES_INTERFACE.attribute10%TYPE,
94 attribute11 AP_INVOICES_INTERFACE.attribute11%TYPE,
95 attribute12 AP_INVOICES_INTERFACE.attribute12%TYPE,
96 attribute13 AP_INVOICES_INTERFACE.attribute13%TYPE,
97 attribute14 AP_INVOICES_INTERFACE.attribute14%TYPE,
98 attribute15 AP_INVOICES_INTERFACE.attribute15%TYPE,
99 global_attribute_category
100 AP_INVOICES_INTERFACE.global_attribute_category%TYPE,
101 global_attribute1 AP_INVOICES_INTERFACE.global_attribute1%TYPE,
102 global_attribute2 AP_INVOICES_INTERFACE.global_attribute2%TYPE,
103 global_attribute3 AP_INVOICES_INTERFACE.global_attribute3%TYPE,
104 global_attribute4 AP_INVOICES_INTERFACE.global_attribute4%TYPE,
105 global_attribute5 AP_INVOICES_INTERFACE.global_attribute5%TYPE,
106 global_attribute6 AP_INVOICES_INTERFACE.global_attribute6%TYPE,
107 global_attribute7 AP_INVOICES_INTERFACE.global_attribute7%TYPE,
108 global_attribute8 AP_INVOICES_INTERFACE.global_attribute8%TYPE,
109 global_attribute9 AP_INVOICES_INTERFACE.global_attribute9%TYPE,
110 global_attribute10 AP_INVOICES_INTERFACE.global_attribute10%TYPE,
111 global_attribute11 AP_INVOICES_INTERFACE.global_attribute11%TYPE,
112 global_attribute12 AP_INVOICES_INTERFACE.global_attribute12%TYPE,
113 global_attribute13 AP_INVOICES_INTERFACE.global_attribute13%TYPE,
114 global_attribute14 AP_INVOICES_INTERFACE.global_attribute14%TYPE,
115 global_attribute15 AP_INVOICES_INTERFACE.global_attribute15%TYPE,
116 global_attribute16 AP_INVOICES_INTERFACE.global_attribute16%TYPE,
117 global_attribute17 AP_INVOICES_INTERFACE.global_attribute17%TYPE,
118 global_attribute18 AP_INVOICES_INTERFACE.global_attribute18%TYPE,
119 global_attribute19 AP_INVOICES_INTERFACE.global_attribute19%TYPE,
120 global_attribute20 AP_INVOICES_INTERFACE.global_attribute20%TYPE,
121 payment_currency_code AP_INVOICES_INTERFACE.payment_currency_code%TYPE,
122 payment_cross_rate AP_INVOICES_INTERFACE.payment_cross_rate%TYPE,
123 payment_cross_rate_type AP_INVOICES_INTERFACE.payment_cross_rate_type%TYPE,
124 payment_cross_rate_date AP_INVOICES_INTERFACE.payment_cross_rate_date%TYPE,
125 doc_category_code AP_INVOICES_INTERFACE.doc_category_code%TYPE,
126 voucher_num AP_INVOICES_INTERFACE.voucher_num%TYPE,
127 payment_method_code
128 AP_INVOICES_INTERFACE.payment_method_code%TYPE,
129 pay_group_lookup_code AP_INVOICES_INTERFACE.pay_group_lookup_code%TYPE,
130 goods_received_date AP_INVOICES_INTERFACE.goods_received_date%TYPE,
131 invoice_received_date AP_INVOICES_INTERFACE.invoice_received_date%TYPE,
132 gl_date AP_INVOICES_INTERFACE.gl_date%TYPE,
133 accts_pay_code_combination_id
134 AP_INVOICES_INTERFACE.accts_pay_code_combination_id%TYPE,
135 -- bug 6509776 - added field
136 accts_pay_code_concatenated
137 AP_INVOICES_INTERFACE.accts_pay_code_concatenated%TYPE,
138
139 --Removed for bug 4277744
140 --ussgl_transaction_code AP_INVOICES_INTERFACE.ussgl_transaction_code%TYPE,
141 exclusive_payment_flag AP_INVOICES_INTERFACE.exclusive_payment_flag%TYPE,
142 prepay_num AP_INVOICES_INTERFACE.prepay_num%TYPE,
143 prepay_line_num AP_INVOICES_INTERFACE.prepay_line_num%TYPE,
144 prepay_apply_amount AP_INVOICES_INTERFACE.prepay_apply_amount%TYPE,
145 prepay_gl_date AP_INVOICES_INTERFACE.prepay_gl_date%TYPE,
146 invoice_includes_prepay_flag
147 AP_INVOICES_INTERFACE.invoice_includes_prepay_flag%TYPE,
148 no_xrate_base_amount AP_INVOICES_INTERFACE.no_xrate_base_amount%TYPE,
149 requester_id AP_INVOICES_INTERFACE.requester_id%TYPE,
150 org_id AP_INVOICES_INTERFACE.org_id%TYPE,
151 operating_unit AP_INVOICES_INTERFACE.operating_unit%TYPE,
152 source AP_INVOICES_INTERFACE.source%TYPE,
153 group_id AP_INVOICES_INTERFACE.group_id%TYPE,
154 request_id AP_INVOICES_INTERFACE.request_id%TYPE,
155 workflow_flag AP_INVOICES_INTERFACE.workflow_flag%TYPE,
156 vendor_email_address AP_INVOICES_INTERFACE.vendor_email_address%TYPE,
157 calc_tax_during_import_flag
158 AP_INVOICES_INTERFACE.calc_tax_during_import_flag%TYPE,
159 control_amount AP_INVOICES_INTERFACE.control_amount%TYPE,
160 add_tax_to_inv_amt_flag AP_INVOICES_INTERFACE.add_tax_to_inv_amt_flag%TYPE,
161 tax_related_invoice_id AP_INVOICES_INTERFACE.tax_related_invoice_id%TYPE,
162 taxation_country AP_INVOICES_INTERFACE.taxation_country%TYPE,
163 document_sub_type AP_INVOICES_INTERFACE.document_sub_type%TYPE,
164 supplier_tax_invoice_number
165 AP_INVOICES_INTERFACE.supplier_tax_invoice_number%TYPE,
166 supplier_tax_invoice_date
167 AP_INVOICES_INTERFACE.supplier_tax_invoice_date%TYPE,
168 supplier_tax_exchange_rate
169 AP_INVOICES_INTERFACE.supplier_tax_exchange_rate%TYPE,
170 tax_invoice_recording_date
171 AP_INVOICES_INTERFACE.tax_invoice_recording_date%TYPE,
172 tax_invoice_internal_seq
173 AP_INVOICES_INTERFACE.tax_invoice_internal_seq%TYPE,
174 legal_entity_id AP_INVOICES_INTERFACE.legal_entity_id%TYPE,
175 set_of_books_id AP_SYSTEM_PARAMETERS_ALL.set_of_books_id%TYPE,
176 tax_only_rcv_matched_flag VARCHAR2(1),
177 tax_only_flag VARCHAR2(1),
178 apply_advances_flag AP_INVOICES_INTERFACE.apply_advances_flag%TYPE,
179 application_id AP_INVOICES_INTERFACE.application_id%TYPE,
180 product_table AP_INVOICES_INTERFACE.product_table%TYPE,
181 reference_key1 AP_INVOICES_INTERFACE.reference_key1%TYPE,
182 reference_key2 AP_INVOICES_INTERFACE.reference_key2%TYPE,
183 reference_key3 AP_INVOICES_INTERFACE.reference_key3%TYPE,
184 reference_key4 AP_INVOICES_INTERFACE.reference_key4%TYPE,
185 reference_key5 AP_INVOICES_INTERFACE.reference_key5%TYPE,
186 reference_1 AP_INVOICES_INTERFACE.reference_1%TYPE,
187 reference_2 AP_INVOICES_INTERFACE.reference_2%TYPE,
188 net_of_retainage_flag AP_INVOICES_INTERFACE.net_of_retainage_flag%TYPE,
189 cust_registration_code AP_INVOICES_INTERFACE.cust_registration_code%TYPE,
190 cust_registration_number AP_INVOICES_INTERFACE.cust_registration_number%TYPE,
191 paid_on_behalf_employee_id AP_INVOICES_INTERFACE.paid_on_behalf_employee_id%TYPE,
192 party_id AP_INVOICES_INTERFACE.party_id%TYPE,
193 party_site_id AP_INVOICES_INTERFACE.party_site_id%TYPE,
194 pay_proc_trxn_type_code AP_INVOICES_INTERFACE.pay_proc_trxn_type_code%TYPE,
195 payment_function AP_INVOICES_INTERFACE.payment_function%TYPE,
196 payment_priority AP_INVOICES_INTERFACE.payment_priority%TYPE,
197 BANK_CHARGE_BEARER AP_INVOICES_INTERFACE.BANK_CHARGE_BEARER%TYPE,
198 REMITTANCE_MESSAGE1 AP_INVOICES_INTERFACE.REMITTANCE_MESSAGE1%TYPE,
199 REMITTANCE_MESSAGE2 AP_INVOICES_INTERFACE.REMITTANCE_MESSAGE2%TYPE,
200 REMITTANCE_MESSAGE3 AP_INVOICES_INTERFACE.REMITTANCE_MESSAGE3%TYPE,
201 UNIQUE_REMITTANCE_IDENTIFIER
202 AP_INVOICES_INTERFACE.UNIQUE_REMITTANCE_IDENTIFIER%TYPE,
203 URI_CHECK_DIGIT AP_INVOICES_INTERFACE.URI_CHECK_DIGIT%TYPE,
204 SETTLEMENT_PRIORITY AP_INVOICES_INTERFACE.SETTLEMENT_PRIORITY%TYPE,
205 PAYMENT_REASON_CODE AP_INVOICES_INTERFACE.PAYMENT_REASON_CODE%TYPE,
206 PAYMENT_REASON_COMMENTS AP_INVOICES_INTERFACE.PAYMENT_REASON_COMMENTS%TYPE,
207 DELIVERY_CHANNEL_CODE AP_INVOICES_INTERFACE.DELIVERY_CHANNEL_CODE%TYPE,
208 EXTERNAL_BANK_ACCOUNT_ID AP_INVOICES_INTERFACE.EXTERNAL_BANK_ACCOUNT_ID %TYPE,
209 --Bug 7357218 Quick Pay and Dispute Resolution Project
210 ORIGINAL_INVOICE_AMOUNT AP_INVOICES_INTERFACE.ORIGINAL_INVOICE_AMOUNT%TYPE,
211 DISPUTE_REASON AP_INVOICES_INTERFACE.DISPUTE_REASON%TYPE,
212 --Third party payments
213 REMIT_TO_SUPPLIER_NAME AP_INVOICES_INTERFACE.REMIT_TO_SUPPLIER_NAME%TYPE,
214 REMIT_TO_SUPPLIER_ID AP_INVOICES_INTERFACE.REMIT_TO_SUPPLIER_ID%TYPE,
215 REMIT_TO_SUPPLIER_SITE AP_INVOICES_INTERFACE.REMIT_TO_SUPPLIER_SITE%TYPE,
216 REMIT_TO_SUPPLIER_SITE_ID AP_INVOICES_INTERFACE.REMIT_TO_SUPPLIER_SITE_ID%TYPE,
217 RELATIONSHIP_ID AP_INVOICES_INTERFACE.RELATIONSHIP_ID%TYPE,
218 REMIT_TO_SUPPLIER_NUM AP_INVOICES_INTERFACE.REMIT_TO_SUPPLIER_NUM%TYPE
219 /* Added for bug 10226070 */
220 ,REQUESTER_LAST_NAME AP_INVOICES_INTERFACE.REQUESTER_LAST_NAME%TYPE
221 ,REQUESTER_FIRST_NAME AP_INVOICES_INTERFACE.REQUESTER_FIRST_NAME%TYPE
222 /* Added for bug 13074325 */
223 ,REQUESTER_EMPLOYEE_NUM AP_INVOICES_INTERFACE.REQUESTER_EMPLOYEE_NUM%TYPE
224 --bug 15862708 starts
225 ,positive_price_tolerance NUMBER
226 ,qty_tolerance NUMBER
227 ,max_qty_ord_tolerance NUMBER
228 ,import_status VARCHAR2(2)
229 ,inv_has_tax_line VARCHAR2(2)
230 --bug 15862708 ends
231 --BUG 16092065 starts
232 ,PREPAY_PERIOD_NAME VARCHAR(25)
233 ,PREPAY_INV_ID AP_INVOICES_ALL.INVOICE_ID%TYPE
234 ,PREPAY_CASE_NAME VARCHAR2(100)
235 --bug 16092065 ends
236 );
237
238 -- Retropricing
239 TYPE t_invoice_table is TABLE of r_invoice_info_rec
240 index by BINARY_INTEGER;
241 -- Retropricing
242 TYPE r_line_info_rec IS RECORD (
243 row_id ROWID,
244 invoice_line_id AP_INVOICE_LINES_INTERFACE.invoice_line_id%TYPE,
245 line_type_lookup_code AP_INVOICE_LINES_INTERFACE.line_type_lookup_code%TYPE,
246 line_number AP_INVOICE_LINES_INTERFACE.line_number%TYPE,
247 line_group_number AP_INVOICE_LINES_INTERFACE.line_group_number%TYPE,
248 amount AP_INVOICE_LINES_INTERFACE.amount%TYPE,
249 base_amount AP_INVOICE_LINES_INTERFACE.amount%TYPE,
250 accounting_date AP_INVOICE_LINES_INTERFACE.accounting_date%TYPE,
251 period_name AP_INVOICE_LINES.period_name%TYPE,
252 deferred_acctg_flag AP_INVOICE_LINES_INTERFACE.deferred_acctg_flag%TYPE,
253 def_acctg_start_date AP_INVOICE_LINES_INTERFACE.def_acctg_start_date%TYPE,
254 def_acctg_end_date AP_INVOICE_LINES_INTERFACE.def_acctg_end_date%TYPE,
255 def_acctg_number_of_periods
256 AP_INVOICE_LINES_INTERFACE.def_acctg_number_of_periods%TYPE,
257 def_acctg_period_type AP_INVOICE_LINES_INTERFACE.def_acctg_period_type%TYPE,
258 description AP_INVOICE_LINES_INTERFACE.description%TYPE,
259 prorate_across_flag AP_INVOICE_LINES_INTERFACE.prorate_across_flag%TYPE,
260 match_type AP_INVOICE_LINES.MATCH_TYPE%TYPE,
261 po_header_id AP_INVOICE_LINES_INTERFACE.po_header_id%TYPE,
262 po_number AP_INVOICE_LINES_INTERFACE.po_number%TYPE,
263 po_line_id AP_INVOICE_LINES_INTERFACE.po_line_id%TYPE,
264 po_line_number AP_INVOICE_LINES_INTERFACE.po_line_number%TYPE,
265 po_release_id AP_INVOICE_LINES_INTERFACE.po_release_id%TYPE,
266 release_num AP_INVOICE_LINES_INTERFACE.release_num%TYPE,
267 po_line_location_id AP_INVOICE_LINES_INTERFACE.po_line_location_id%TYPE,
268 po_shipment_num AP_INVOICE_LINES_INTERFACE.po_shipment_num%TYPE,
269 po_distribution_id AP_INVOICE_LINES_INTERFACE.po_distribution_id%TYPE,
270 po_distribution_num AP_INVOICE_LINES_INTERFACE.po_distribution_num%TYPE,
271 unit_of_meas_lookup_code
272 AP_INVOICE_LINES_INTERFACE.unit_of_meas_lookup_code%TYPE,
273 inventory_item_id AP_INVOICE_LINES_INTERFACE.inventory_item_id%TYPE,
274 item_description AP_INVOICE_LINES_INTERFACE.item_description%TYPE,
275 quantity_invoiced AP_INVOICE_LINES_INTERFACE.quantity_invoiced%TYPE,
276 ship_to_location_code AP_INVOICE_LINES_INTERFACE.ship_to_location_code%TYPE,
277 unit_price AP_INVOICE_LINES_INTERFACE.unit_price%TYPE,
278 final_match_flag AP_INVOICE_LINES_INTERFACE.final_match_flag%TYPE,
279 distribution_set_id AP_INVOICE_LINES_INTERFACE.distribution_set_id%TYPE,
280 distribution_set_name AP_INVOICE_LINES_INTERFACE.distribution_set_name%TYPE,
281 partial_segments VARCHAR2(1),
282 dist_code_concatenated AP_INVOICE_LINES_INTERFACE.dist_code_concatenated%TYPE,
283 dist_code_combination_id
284 AP_INVOICE_LINES_INTERFACE.dist_code_combination_id%TYPE,
285 awt_group_id AP_INVOICE_LINES_INTERFACE.awt_group_id%TYPE,
286 awt_group_name AP_INVOICE_LINES_INTERFACE.awt_group_name%TYPE,
287 pay_awt_group_id AP_INVOICE_LINES_INTERFACE.pay_awt_group_id%TYPE,--bug6639866
288 pay_awt_group_name AP_INVOICE_LINES_INTERFACE.pay_awt_group_name%TYPE,--bug6639866
289 balancing_segment AP_INVOICE_LINES_INTERFACE.balancing_segment%TYPE,
290 cost_center_segment AP_INVOICE_LINES_INTERFACE.cost_center_segment%TYPE,
291 account_segment AP_INVOICE_LINES_INTERFACE.account_segment%TYPE,
292 attribute_category AP_INVOICE_LINES_INTERFACE.attribute_category%TYPE,
293 attribute1 AP_INVOICE_LINES_INTERFACE.attribute1%TYPE,
294 attribute2 AP_INVOICE_LINES_INTERFACE.attribute2%TYPE,
295 attribute3 AP_INVOICE_LINES_INTERFACE.attribute3%TYPE,
296 attribute4 AP_INVOICE_LINES_INTERFACE.attribute4%TYPE,
297 attribute5 AP_INVOICE_LINES_INTERFACE.attribute5%TYPE,
298 attribute6 AP_INVOICE_LINES_INTERFACE.attribute6%TYPE,
299 attribute7 AP_INVOICE_LINES_INTERFACE.attribute7%TYPE,
300 attribute8 AP_INVOICE_LINES_INTERFACE.attribute8%TYPE,
301 attribute9 AP_INVOICE_LINES_INTERFACE.attribute9%TYPE,
302 attribute10 AP_INVOICE_LINES_INTERFACE.attribute10%TYPE,
303 attribute11 AP_INVOICE_LINES_INTERFACE.attribute11%TYPE,
304 attribute12 AP_INVOICE_LINES_INTERFACE.attribute12%TYPE,
305 attribute13 AP_INVOICE_LINES_INTERFACE.attribute13%TYPE,
306 attribute14 AP_INVOICE_LINES_INTERFACE.attribute14%TYPE,
307 attribute15 AP_INVOICE_LINES_INTERFACE.attribute15%TYPE,
308 global_attribute_category
309 AP_INVOICE_LINES_INTERFACE.global_attribute_category%TYPE,
310 global_attribute1 AP_INVOICE_LINES_INTERFACE.global_attribute1%TYPE,
311 global_attribute2 AP_INVOICE_LINES_INTERFACE.global_attribute2%TYPE,
312 global_attribute3 AP_INVOICE_LINES_INTERFACE.global_attribute3%TYPE,
313 global_attribute4 AP_INVOICE_LINES_INTERFACE.global_attribute4%TYPE,
314 global_attribute5 AP_INVOICE_LINES_INTERFACE.global_attribute5%TYPE,
315 global_attribute6 AP_INVOICE_LINES_INTERFACE.global_attribute6%TYPE,
316 global_attribute7 AP_INVOICE_LINES_INTERFACE.global_attribute7%TYPE,
317 global_attribute8 AP_INVOICE_LINES_INTERFACE.global_attribute8%TYPE,
318 global_attribute9 AP_INVOICE_LINES_INTERFACE.global_attribute9%TYPE,
319 global_attribute10 AP_INVOICE_LINES_INTERFACE.global_attribute10%TYPE,
320 global_attribute11 AP_INVOICE_LINES_INTERFACE.global_attribute11%TYPE,
321 global_attribute12 AP_INVOICE_LINES_INTERFACE.global_attribute12%TYPE,
322 global_attribute13 AP_INVOICE_LINES_INTERFACE.global_attribute13%TYPE,
323 global_attribute14 AP_INVOICE_LINES_INTERFACE.global_attribute14%TYPE,
324 global_attribute15 AP_INVOICE_LINES_INTERFACE.global_attribute15%TYPE,
325 global_attribute16 AP_INVOICE_LINES_INTERFACE.global_attribute16%TYPE,
326 global_attribute17 AP_INVOICE_LINES_INTERFACE.global_attribute17%TYPE,
327 global_attribute18 AP_INVOICE_LINES_INTERFACE.global_attribute18%TYPE,
328 global_attribute19 AP_INVOICE_LINES_INTERFACE.global_attribute19%TYPE,
329 global_attribute20 AP_INVOICE_LINES_INTERFACE.global_attribute20%TYPE,
330 project_id AP_INVOICE_LINES_INTERFACE.project_id%TYPE,
331 task_id AP_INVOICE_LINES_INTERFACE.task_id%TYPE,
332 award_id AP_INVOICE_LINES_INTERFACE.award_id%TYPE,
333 expenditure_type AP_INVOICE_LINES_INTERFACE.expenditure_type%TYPE,
334 expenditure_item_date AP_INVOICE_LINES_INTERFACE.expenditure_item_date%TYPE,
335 expenditure_organization_id
336 AP_INVOICE_LINES_INTERFACE.expenditure_organization_id%TYPE,
337 pa_addition_flag AP_INVOICE_LINES_INTERFACE.pa_addition_flag%TYPE,
338 pa_quantity AP_INVOICE_LINES_INTERFACE.pa_quantity%TYPE,
339 --Removed for bug 4277744
340 --ussgl_transaction_code AP_INVOICE_LINES_INTERFACE.ussgl_transaction_code%TYPE,
341 stat_amount AP_INVOICE_LINES_INTERFACE.stat_amount%TYPE,
342 type_1099 AP_INVOICE_LINES_INTERFACE.type_1099%TYPE,
343 income_tax_region AP_INVOICE_LINES_INTERFACE.income_tax_region%TYPE,
344 assets_tracking_flag AP_INVOICE_LINES_INTERFACE.assets_tracking_flag%TYPE,
345 asset_book_type_code AP_INVOICE_LINES_INTERFACE.asset_book_type_code%TYPE,
346 asset_category_id AP_INVOICE_LINES_INTERFACE.asset_category_id%TYPE,
347 serial_number AP_INVOICE_LINES_INTERFACE.serial_number%TYPE,
348 manufacturer AP_INVOICE_LINES_INTERFACE.manufacturer%TYPE,
349 model_number AP_INVOICE_LINES_INTERFACE.model_number%TYPE,
350 warranty_number AP_INVOICE_LINES_INTERFACE.warranty_number%TYPE,
351 price_correction_flag AP_INVOICE_LINES_INTERFACE.price_correction_flag%TYPE,
352 price_correct_inv_num AP_INVOICE_LINES_INTERFACE.price_correct_inv_num%TYPE,
353 corrected_inv_id AP_INVOICE_LINES_ALL.corrected_inv_id%TYPE,
354 price_correct_inv_line_num
355 AP_INVOICE_LINES_INTERFACE.price_correct_inv_line_num%TYPE,
356 receipt_number AP_INVOICE_LINES_INTERFACE.receipt_number%TYPE,
357 receipt_line_number AP_INVOICE_LINES_INTERFACE.receipt_line_number%TYPE,
358 rcv_transaction_id AP_INVOICE_LINES_INTERFACE.rcv_transaction_id%TYPE,
359 -- bug 7344899 - added field
360 rcv_shipment_line_id AP_INVOICE_LINES.RCV_SHIPMENT_LINE_ID%TYPE,
361 match_option AP_INVOICE_LINES_INTERFACE.match_option%TYPE,
362 packing_slip AP_INVOICE_LINES_INTERFACE.packing_slip%TYPE,
363 vendor_item_num AP_INVOICE_LINES_INTERFACE.vendor_item_num%TYPE,
364 taxable_flag AP_INVOICE_LINES_INTERFACE.taxable_flag%TYPE,
365 pa_cc_ar_invoice_id AP_INVOICE_LINES_INTERFACE.pa_cc_ar_invoice_id%TYPE,
366 pa_cc_ar_invoice_line_num
367 AP_INVOICE_LINES_INTERFACE.pa_cc_ar_invoice_line_num%TYPE,
368 pa_cc_processed_code AP_INVOICE_LINES_INTERFACE.pa_cc_processed_code%TYPE,
369 reference_1 AP_INVOICE_LINES_INTERFACE.reference_1%TYPE,
370 reference_2 AP_INVOICE_LINES_INTERFACE.reference_2%TYPE,
371 credit_card_trx_id AP_INVOICE_LINES_INTERFACE.credit_card_trx_id%TYPE,
372 requester_id AP_INVOICE_LINES_INTERFACE.requester_id%TYPE,
373 org_id AP_INVOICE_LINES_INTERFACE.org_id%TYPE,
374 program_application_id AP_INVOICE_LINES.PROGRAM_APPLICATION_ID%TYPE,
375 program_id AP_INVOICE_LINES.PROGRAM_ID%TYPE,
376 request_id AP_INVOICE_LINES.REQUEST_ID%TYPE,
377 program_update_date AP_INVOICE_LINES.PROGRAM_UPDATE_DATE%TYPE,
378 control_amount AP_INVOICE_LINES_INTERFACE.control_amount%TYPE,
379 assessable_value AP_INVOICE_LINES_INTERFACE.assessable_value%TYPE,
380 default_dist_ccid AP_INVOICE_LINES_INTERFACE.default_dist_ccid%TYPE,
381 primary_intended_use AP_INVOICE_LINES_INTERFACE.primary_intended_use%TYPE,
382 ship_to_location_id AP_INVOICE_LINES_INTERFACE.ship_to_location_id%TYPE,
383 product_type AP_INVOICE_LINES_INTERFACE.product_type%TYPE,
384 product_category AP_INVOICE_LINES_INTERFACE.product_category%TYPE,
385 product_fisc_classification
386 AP_INVOICE_LINES_INTERFACE.product_fisc_classification%TYPE,
387 user_defined_fisc_class AP_INVOICE_LINES_INTERFACE.user_defined_fisc_class%TYPE,
388 trx_business_category AP_INVOICE_LINES_INTERFACE.trx_business_category%TYPE,
389 tax_regime_code AP_INVOICE_LINES_INTERFACE.tax_regime_code%TYPE,
390 tax AP_INVOICE_LINES_INTERFACE.tax%TYPE,
391 tax_jurisdiction_code AP_INVOICE_LINES_INTERFACE.tax_jurisdiction_code%TYPE,
392 tax_status_code AP_INVOICE_LINES_INTERFACE.tax_status_code%TYPE,
393 tax_rate_id AP_INVOICE_LINES_INTERFACE.tax_rate_id%TYPE,
394 tax_rate_code AP_INVOICE_LINES_INTERFACE.tax_rate_code%TYPE,
395 tax_rate AP_INVOICE_LINES_INTERFACE.tax_rate%TYPE,
396 incl_in_taxable_line_flag
397 AP_INVOICE_LINES_INTERFACE.incl_in_taxable_line_flag%TYPE,
398 application_id AP_INVOICE_LINES_INTERFACE.application_id%TYPE,
399 product_table AP_INVOICE_LINES_INTERFACE.product_table%TYPE,
400 reference_key1 AP_INVOICE_LINES_INTERFACE.reference_key1%TYPE,
401 reference_key2 AP_INVOICE_LINES_INTERFACE.reference_key2%TYPE,
402 reference_key3 AP_INVOICE_LINES_INTERFACE.reference_key3%TYPE,
403 reference_key4 AP_INVOICE_LINES_INTERFACE.reference_key4%TYPE,
404 reference_key5 AP_INVOICE_LINES_INTERFACE.reference_key5%TYPE,
405 purchasing_category_id AP_INVOICE_LINES_INTERFACE.purchasing_category_id%TYPE,
406 purchasing_category VARCHAR2(2000),
407 cost_factor_id AP_INVOICE_LINES_INTERFACE.cost_factor_id%TYPE,
408 cost_factor_name AP_INVOICE_LINES_INTERFACE.cost_factor_name%TYPE,
409 source_application_id AP_INVOICE_LINES_INTERFACE.source_application_id%TYPE,
410 source_entity_code AP_INVOICE_LINES_INTERFACE.source_entity_code%TYPE,
411 source_event_class_code AP_INVOICE_LINES_INTERFACE.source_event_class_code%TYPE,
412 source_trx_id AP_INVOICE_LINES_INTERFACE.source_trx_id%TYPE,
413 source_line_id AP_INVOICE_LINES_INTERFACE.source_line_id%TYPE,
414 source_trx_level_type AP_INVOICE_LINES_INTERFACE.source_trx_level_type%TYPE,
415 tax_classification_code AP_INVOICE_LINES_INTERFACE.tax_classification_code%TYPE,
416 retained_amount AP_INVOICE_LINES_ALL.retained_amount%TYPE,
417 amount_includes_tax_flag AP_INVOICE_LINES_INTERFACE.amount_includes_tax_flag%TYPE,
418 --Bug6167068 starts Added the following columns to record
419 CC_REVERSAL_FLAG AP_INVOICE_LINES_INTERFACE.CC_REVERSAL_FLAG%TYPE,
420 COMPANY_PREPAID_INVOICE_ID AP_INVOICE_LINES_INTERFACE.COMPANY_PREPAID_INVOICE_ID%TYPE,
421 EXPENSE_GROUP AP_INVOICE_LINES_INTERFACE.EXPENSE_GROUP%TYPE,
422 JUSTIFICATION AP_INVOICE_LINES_INTERFACE.JUSTIFICATION%TYPE,
423 MERCHANT_DOCUMENT_NUMBER AP_INVOICE_LINES_INTERFACE.MERCHANT_DOCUMENT_NUMBER%TYPE,
424 MERCHANT_NAME AP_INVOICE_LINES_INTERFACE.MERCHANT_NAME%TYPE,
425 MERCHANT_REFERENCE AP_INVOICE_LINES_INTERFACE.MERCHANT_REFERENCE%TYPE,
426 MERCHANT_TAXPAYER_ID AP_INVOICE_LINES_INTERFACE.MERCHANT_TAXPAYER_ID%TYPE,
427 MERCHANT_TAX_REG_NUMBER AP_INVOICE_LINES_INTERFACE.MERCHANT_TAX_REG_NUMBER%TYPE,
428 RECEIPT_CONVERSION_RATE AP_INVOICE_LINES_INTERFACE.RECEIPT_CONVERSION_RATE%TYPE,
429 RECEIPT_CURRENCY_AMOUNT AP_INVOICE_LINES_INTERFACE.RECEIPT_CURRENCY_AMOUNT%TYPE,
430 RECEIPT_CURRENCY_CODE AP_INVOICE_LINES_INTERFACE.RECEIPT_CURRENCY_CODE%TYPE,
431 COUNTRY_OF_SUPPLY AP_INVOICE_LINES_INTERFACE.COUNTRY_OF_SUPPLY%TYPE
432 --Bug6167068 ends
433
434 --Bug 8658097 starts
435 ,EXPENSE_START_DATE AP_INVOICE_LINES_INTERFACE.EXPENSE_START_DATE%TYPE
436 ,EXPENSE_END_DATE AP_INVOICE_LINES_INTERFACE.EXPENSE_END_DATE%TYPE
437 --Bug 8658097 ends
438 /* Added for bug 10226070 */
439 ,REQUESTER_LAST_NAME AP_INVOICE_LINES_INTERFACE.REQUESTER_LAST_NAME%TYPE
440 ,REQUESTER_FIRST_NAME AP_INVOICE_LINES_INTERFACE.REQUESTER_FIRST_NAME%TYPE
441 ,CASCADE_RECEIPTS_FLAG VARCHAR2(1) /* Added for bug#10175718 This flag will make sure whether the
442 line needs to cascaded or not across receipts of the shipment */
443 /* Added for bug 13074325 */
444 ,REQUESTER_EMPLOYEE_NUM AP_INVOICE_LINES_INTERFACE.REQUESTER_EMPLOYEE_NUM%TYPE
445 --bug 15862708 starts
446 ,INVOICE_ID AP_INVOICE_LINES_INTERFACE.INVOICE_ID%TYPE
447 ,WFAPPROVAL_STATUS AP_INVOICE_LINES_ALL.WFAPPROVAL_STATUS%TYPE
448 ,GENERATE_DISTS AP_INVOICE_LINES_ALL.GENERATE_DISTS%TYPE
449 --bug 15862708 ends
450 /*Bug 14271140 Start*/
451 ,LAST_UPDATED_BY AP_INVOICE_LINES_INTERFACE.LAST_UPDATED_BY%TYPE
452 ,LAST_UPDATE_LOGIN AP_INVOICE_LINES_INTERFACE.LAST_UPDATE_LOGIN%TYPE
453 ,CREATED_BY AP_INVOICE_LINES_INTERFACE.CREATED_BY%TYPE
454 ,CREATION_DATE AP_INVOICE_LINES_INTERFACE.CREATION_DATE%TYPE
455 ,LAST_UPDATE_DATE AP_INVOICE_LINES_INTERFACE.LAST_UPDATE_DATE%TYPE
456 /*Bug 14271140 End*/
457 );
458
459
460 TYPE t_lines_table is TABLE of r_line_info_rec
461 index by BINARY_INTEGER;
462
463
464 TYPE rejection_rec_type IS RECORD
465 (parent_table AP_INTERFACE_REJECTIONS.parent_table%TYPE,
466 parent_id AP_INTERFACE_REJECTIONS.parent_id%TYPE,
467 reject_lookup_code AP_INTERFACE_REJECTIONS.reject_lookup_code%TYPE);
468
469 TYPE rejection_tab_type IS TABLE OF rejection_rec_type
470 index by BINARY_INTEGER;
471
472 -- Bug 5448579. Following new variables are added
473 TYPE moac_ou_rec_type IS RECORD
474 (org_id HR_OPERATING_UNITS.Organization_Id%TYPE,
475 org_name HR_OPERATING_UNITS.Name%TYPE);
476
477 TYPE moac_ou_tab_type IS TABLE OF moac_ou_rec_type
478 index by BINARY_INTEGER;
479
480 TYPE fsp_org_rec_type IS RECORD
481 (org_id FINANCIALS_SYSTEM_PARAMS_ALL.Org_Id%TYPE);
482
483 TYPE fsp_org_tab_type IS TABLE OF fsp_org_rec_type
484 index by BINARY_INTEGER;
485
486 TYPE pay_group_rec_type IS RECORD
487 (pay_group PO_LOOKUP_CODES.Lookup_Code%Type);
488
489 TYPE pay_group_tab_type IS TABLE OF pay_group_rec_type
490 index by BINARY_INTEGER;
491
492 TYPE payment_method_rec_type IS RECORD
493 (payment_method IBY_PAYMENT_METHODS_VL.Payment_Method_Code%Type);
494
495 TYPE payment_method_tab_type IS TABLE OF payment_method_rec_type
496 index by BINARY_INTEGER;
497
498 TYPE fnd_currency_rec_type IS RECORD
499 (currency_code FND_CURRENCIES.currency_code%TYPE,
500 start_date_active FND_CURRENCIES.start_date_active%TYPE,
501 end_date_active FND_CURRENCIES.end_date_active%TYPE,
502 minimum_accountable_unit FND_CURRENCIES.minimum_accountable_unit%TYPE,
503 precision FND_CURRENCIES.precision%TYPE,
504 enabled_flag FND_CURRENCIES.enabled_flag%TYPE);
505
506 TYPE fnd_currency_tab_type IS TABLE OF fnd_currency_rec_type
507 index by BINARY_INTEGER;
508
509 g_moac_ou_tab moac_ou_tab_type;
510 g_fsp_ou_tab fsp_org_tab_type;
511 g_pay_group_tab pay_group_tab_type;
512 g_payment_method_tab payment_method_tab_type;
513 g_fnd_currency_tab fnd_currency_tab_type;
514 g_structure_id mtl_default_sets_view.structure_id%TYPE;
515
516 FUNCTION IMPORT_INVOICES(
517 p_batch_name IN VARCHAR2,
518 p_gl_date IN DATE,
519 p_hold_code IN VARCHAR2,
520 p_hold_reason IN VARCHAR2,
521 p_commit_cycles IN NUMBER,
522 p_source IN VARCHAR2,
523 p_group_id IN VARCHAR2,
524 p_conc_request_id IN NUMBER,
525 p_debug_switch IN VARCHAR2,
526 p_org_id IN NUMBER,
527 p_batch_error_flag OUT NOCOPY VARCHAR2,
528 p_invoices_fetched OUT NOCOPY NUMBER,
529 p_invoices_created OUT NOCOPY NUMBER,
530 p_total_invoice_amount OUT NOCOPY NUMBER,
531 p_print_batch OUT NOCOPY VARCHAR2,
532 p_calling_sequence IN VARCHAR2,
533 p_invoice_interface_id IN NUMBER DEFAULT NULL,
534 p_needs_invoice_approval IN VARCHAR2 DEFAULT 'N',
535 p_commit IN VARCHAR2 DEFAULT 'Y') RETURN BOOLEAN;
536
537
538 FUNCTION IMPORT_PURGE(
539 p_source IN VARCHAR2,
540 p_group_id IN VARCHAR2,
541 p_org_id IN NUMBER,
542 p_commit_cycles IN NUMBER,
543 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN;
544
545 FUNCTION XML_IMPORT_PURGE(
546 p_group_id IN VARCHAR2,
547 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN;
548
549
550 PROCEDURE SUBMIT_PAYMENT_REQUEST(
551 p_api_version IN VARCHAR2 DEFAULT '1.0',
552 p_invoice_interface_id IN NUMBER,
553 p_budget_control IN VARCHAR2 DEFAULT 'Y',
554 p_needs_invoice_approval IN VARCHAR2 DEFAULT 'N',
555 p_invoice_id OUT NOCOPY NUMBER,
556 x_return_status OUT NOCOPY VARCHAR2,
557 x_msg_count OUT NOCOPY NUMBER,
558 x_msg_data OUT NOCOPY VARCHAR2,
559 x_rejection_list OUT NOCOPY rejection_tab_type,
560 p_calling_sequence IN VARCHAR2,
561 p_commit IN VARCHAR2 DEFAULT 'Y',
562 p_batch_name IN VARCHAR2 DEFAULT NULL, --Bug 8361660
563 p_conc_request_id IN NUMBER DEFAULT NULL --Bug 8492591
564 );
565
566 --bug 15862708
567 TYPE get_info_rec IS RECORD (
568 --p_org_id ap_system_parameters_all.org_id%TYPE,
569 p_set_of_books_id ap_system_parameters_all.set_of_books_id%TYPE,
570 p_multi_currency_flag ap_system_parameters_all.multi_currency_flag%TYPE,
571 p_make_rate_mandatory_flag ap_system_parameters_all.make_rate_mandatory_flag%TYPE,
572 p_default_exchange_rate_type ap_system_parameters_all.default_exchange_rate_type%TYPE,
573 p_base_currency_code ap_system_parameters_all.base_currency_code%TYPE,
574 p_batch_control_flag varchar2(2),
575 p_invoice_currency_code ap_system_parameters_all.invoice_currency_code%TYPE,
576 p_base_min_acct_unit number,
577 p_base_precision number,
578 p_sequence_numbering varchar2(30),
579 p_awt_include_tax_amt ap_system_parameters_all.awt_include_tax_amt%TYPE,
580 p_gl_date date,
581 p_transfer_desc_flex_flag ap_system_parameters_all.transfer_desc_flex_flag%TYPE,
582 p_gl_date_from_receipt_flag ap_system_parameters_all.gl_date_from_receipt_flag%TYPE,
583 p_purch_encumbrance_flag financials_system_params_all.purch_encumbrance_flag%TYPE,
584 p_retainage_ccid financials_system_params_all.retainage_code_combination_id%TYPE,
585 P_pa_installed varchar2(2),
586 p_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%type,
587 p_inv_doc_cat_override ap_system_parameters_all.inv_doc_category_override%TYPE,
588 p_calc_user_xrate ap_system_parameters_all.calc_user_xrate%TYPE,
589 --p_calling_sequence varchar2(200),
590 p_approval_workflow_flag ap_system_parameters_all.approval_workflow_flag%TYPE,
591 p_freight_code_combination_id ap_system_parameters_all.freight_code_combination_id%TYPE,
592 p_allow_interest_invoices ap_system_parameters_all.auto_calculate_interest_flag%TYPE,
593 p_add_days_settlement_date ap_system_parameters_all.add_days_settlement_date%TYPE,
594 p_disc_is_inv_less_tax_flag ap_system_parameters_all.disc_is_inv_less_tax_flag%TYPE,
595 p_asset_book_type varchar2(100)
596 );
597
598 TYPE table_get_info_rec is TABLE of get_info_rec
599 index by varchar2(20);
600
601 FUNCTION get_info_1 (
602 p_org_id IN NUMBER,
603 p_gl_date IN OUT NOCOPY DATE,
604 p_calling_sequence IN VARCHAR2,
605 p_source IN VARCHAR2, --bug 5382889. LE TimeZone
606 p_invoice_date IN DATE, -- bug 5382889. LE TimeZone
607 p_goods_received_date IN DATE, -- bug 5382889. LE TimeZone
608 p_get_info_rec OUT NOCOPY AP_IMPORT_INVOICES_PKG.get_info_rec
609 )
610 RETURN BOOLEAN;
611
612 tab_get_info_rec AP_IMPORT_INVOICES_PKG.table_get_info_rec;
613
614 TYPE LINES_TABLE IS TABLE OF AP_INVOICE_LINES_ALL%ROWTYPE;
615 --bug 15862708
616
617 END AP_IMPORT_INVOICES_PKG;