1 PACKAGE jai_ap_utils_pkg AS
2 /* $Header: jai_ap_utils.pls 120.13.12020000.3 2013/03/25 01:55:08 cholei ship $ */
3
4 /*----------------------------------------------------------------------------------------------------------------
5
6 Filename:
7
8 Change History:
9
10 Date Remarks
11 ---------------------------------------------------------------------------------------------------------
12 08-Jun-2005 File Version 116.2 Object is Modified to refer to New DB Entity names in place of
13 Old DB Entity Names as required for CASE COMPLAINCE.
14
15 14-Jun-2005 rchandan for bug#4428980, Version 116.3
16 Modified the object to remove literals from DML statements and CURSORS.
17 As part OF R12 Initiative Inventory conversion the OPM code is commented
18
19 23-Jun-2005 Brathod , File Version 112.0 , Bug# 4445989
20 Signature for procedure get_aportion_factor is modified to use invoice_id and invoice_line_number
21
22 02-Sep-2005 Ramananda for Bug#4418550, File Version 120.2
23 Added the new function get_tds_invoice_batch
24
25 Dependency Due to this Bug (Functional)
26 --------------------------
27 jai_ap_utils.plb (120.2)
28 jai_ap_tds_old.plb (120.3)
29 jai_ap_tds_gen.plb (120.8)
30 jai_constants.pls (120.3)
31 jaiorgdffsetup.sql (120.2)
32 ja12alu.ldt, jaivmlu.ldt
33 16-apr-2007 Vkaranam for bug #5989740,File version 120.3
34 Forward porting the changes done in 11i bug#5583832
35 Signature for procedure get_aportion_factor is modified.
36
37 11-Mar-2011 Wenqiong for bug #1168411,File version 120.11
38 Updated create_boe_invoice, and added new procedure/function ap_void_check,
39 get_boe_applied_status,check_boe_payment.
40
41 ---------------------------------------------------------------------------------------------------------*/
42
43 PROCEDURE create_pla_invoice(
44 P_PLA_ID IN NUMBER,
45 P_SET_OF_BOOK_ID IN NUMBER,
46 P_ORG_ID IN NUMBER
47 );
48
49 PROCEDURE create_boe_invoice(
50 P_BOE_ID IN NUMBER,
51 P_SET_OF_BOOK_ID IN NUMBER,
52 P_ORG_ID IN NUMBER
53 );
54
55
56 PROCEDURE insert_ap_inv_interface(
57 p_jai_source IN VARCHAR2,
58 p_invoice_id OUT NOCOPY ap_invoices_interface.INVOICE_ID%TYPE,
59 p_invoice_num IN ap_invoices_interface.INVOICE_NUM%TYPE DEFAULT NULL,
60 p_invoice_type_lookup_code IN ap_invoices_interface.INVOICE_TYPE_LOOKUP_CODE%TYPE DEFAULT NULL,
61 p_invoice_date IN ap_invoices_interface.INVOICE_DATE%TYPE DEFAULT NULL,
62 p_po_number IN ap_invoices_interface.PO_NUMBER%TYPE DEFAULT NULL,
63 p_vendor_id IN ap_invoices_interface.VENDOR_ID%TYPE DEFAULT NULL,
64 p_vendor_num IN ap_invoices_interface.VENDOR_NUM%TYPE DEFAULT NULL,
65 p_vendor_name IN ap_invoices_interface.VENDOR_NAME%TYPE DEFAULT NULL,
66 p_vendor_site_id IN ap_invoices_interface.VENDOR_SITE_ID%TYPE DEFAULT NULL,
67 p_vendor_site_code IN ap_invoices_interface.VENDOR_SITE_CODE%TYPE DEFAULT NULL,
68 p_invoice_amount IN ap_invoices_interface.INVOICE_AMOUNT%TYPE DEFAULT NULL,
69 p_invoice_currency_code IN ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE DEFAULT NULL,
70 p_exchange_rate IN ap_invoices_interface.EXCHANGE_RATE%TYPE DEFAULT NULL,
71 p_exchange_rate_type IN ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE DEFAULT NULL,
72 p_exchange_date IN ap_invoices_interface.EXCHANGE_DATE%TYPE DEFAULT NULL,
73 p_terms_id IN ap_invoices_interface.TERMS_ID%TYPE DEFAULT NULL,
74 p_terms_name IN ap_invoices_interface.TERMS_NAME%TYPE DEFAULT NULL,
75 p_description IN ap_invoices_interface.DESCRIPTION%TYPE DEFAULT NULL,
76 p_awt_group_id IN ap_invoices_interface.AWT_GROUP_ID%TYPE DEFAULT NULL,
77 p_awt_group_name IN ap_invoices_interface.AWT_GROUP_NAME%TYPE DEFAULT NULL,
78 p_last_update_date IN ap_invoices_interface.LAST_UPDATE_DATE%TYPE DEFAULT NULL,
79 p_last_updated_by IN ap_invoices_interface.LAST_UPDATED_BY%TYPE DEFAULT NULL,
80 p_last_update_login IN ap_invoices_interface.LAST_UPDATE_LOGIN%TYPE DEFAULT NULL,
81 p_creation_date IN ap_invoices_interface.CREATION_DATE%TYPE DEFAULT NULL,
82 p_created_by IN ap_invoices_interface.CREATED_BY%TYPE DEFAULT NULL,
83 --Added below the attribute category and attribute parameters for Bug #3841637
84 p_attribute_category IN ap_invoices_interface.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
85 p_attribute1 IN ap_invoices_interface.ATTRIBUTE1%TYPE DEFAULT NULL,
86 p_attribute2 IN ap_invoices_interface.ATTRIBUTE2%TYPE DEFAULT NULL,
87 p_attribute3 IN ap_invoices_interface.ATTRIBUTE3%TYPE DEFAULT NULL,
88 p_attribute4 IN ap_invoices_interface.ATTRIBUTE4%TYPE DEFAULT NULL,
89 p_attribute5 IN ap_invoices_interface.ATTRIBUTE5%TYPE DEFAULT NULL,
90 p_attribute6 IN ap_invoices_interface.ATTRIBUTE6%TYPE DEFAULT NULL,
91 p_attribute7 IN ap_invoices_interface.ATTRIBUTE7%TYPE DEFAULT NULL,
92 p_attribute8 IN ap_invoices_interface.ATTRIBUTE8%TYPE DEFAULT NULL,
93 p_attribute9 IN ap_invoices_interface.ATTRIBUTE9%TYPE DEFAULT NULL,
94 p_attribute10 IN ap_invoices_interface.ATTRIBUTE10%TYPE DEFAULT NULL,
95 p_attribute11 IN ap_invoices_interface.ATTRIBUTE11%TYPE DEFAULT NULL,
96 p_attribute12 IN ap_invoices_interface.ATTRIBUTE12%TYPE DEFAULT NULL,
97 p_attribute13 IN ap_invoices_interface.ATTRIBUTE13%TYPE DEFAULT NULL,
98 p_attribute14 IN ap_invoices_interface.ATTRIBUTE14%TYPE DEFAULT NULL,
99 p_attribute15 IN ap_invoices_interface.ATTRIBUTE15%TYPE DEFAULT NULL,
100 p_status IN ap_invoices_interface.STATUS%TYPE DEFAULT NULL,
101 p_source IN ap_invoices_interface.SOURCE%TYPE DEFAULT NULL,
102 p_group_id IN ap_invoices_interface.GROUP_ID%TYPE DEFAULT NULL,
103 p_request_id IN ap_invoices_interface.REQUEST_ID%TYPE DEFAULT NULL,
104 p_payment_cross_rate_type IN ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE DEFAULT NULL,
105 p_payment_cross_rate_date IN ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE DEFAULT NULL,
106 p_payment_cross_rate IN ap_invoices_interface.PAYMENT_CROSS_RATE%TYPE DEFAULT NULL,
107 p_payment_currency_code IN ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE DEFAULT NULL,
108 p_workflow_flag IN ap_invoices_interface.WORKFLOW_FLAG%TYPE DEFAULT NULL,
109 p_doc_category_code IN ap_invoices_interface.DOC_CATEGORY_CODE%TYPE DEFAULT NULL,
110 p_voucher_num IN ap_invoices_interface.VOUCHER_NUM%TYPE DEFAULT NULL,
111 p_payment_method_lookup_code IN ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE DEFAULT NULL,
112 p_pay_group_lookup_code IN ap_invoices_interface.PAY_GROUP_LOOKUP_CODE%TYPE DEFAULT NULL,
113 p_goods_received_date IN ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE DEFAULT NULL,
114 p_invoice_received_date IN ap_invoices_interface.INVOICE_RECEIVED_DATE%TYPE DEFAULT NULL,
115 p_gl_date IN ap_invoices_interface.GL_DATE%TYPE DEFAULT NULL,
116 p_accts_pay_ccid IN ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE DEFAULT NULL,
117 p_ussgl_transaction_code IN ap_invoices_interface.USSGL_TRANSACTION_CODE%TYPE DEFAULT NULL,
118 p_exclusive_payment_flag IN ap_invoices_interface.EXCLUSIVE_PAYMENT_FLAG%TYPE DEFAULT NULL,
119 p_org_id IN ap_invoices_interface.ORG_ID%TYPE DEFAULT NULL,
120 p_amount_applicable_to_dis IN ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE DEFAULT NULL,
121 p_prepay_num IN ap_invoices_interface.PREPAY_NUM%TYPE DEFAULT NULL,
122 p_prepay_dist_num IN ap_invoices_interface.PREPAY_DIST_NUM%TYPE DEFAULT NULL,
123 p_prepay_apply_amount IN ap_invoices_interface.PREPAY_APPLY_AMOUNT%TYPE DEFAULT NULL,
124 p_prepay_gl_date IN ap_invoices_interface.PREPAY_GL_DATE%TYPE DEFAULT NULL,
125 -- Bug4240179. Added by LGOPALSA. Changed the data type
126 -- for the following 4 fields.
127 p_invoice_includes_prepay_flag IN VARCHAR2 DEFAULT NULL,
128 p_no_xrate_base_amount IN NUMBER DEFAULT NULL,
129 p_vendor_email_address IN VARCHAR2 DEFAULT NULL,
130 p_terms_date IN DATE DEFAULT NULL,
131 p_requester_id IN NUMBER DEFAULT NULL,
132 p_ship_to_location IN VARCHAR2 DEFAULT NULL,
133 p_external_doc_ref IN VARCHAR2 DEFAULT NULL,
134 -- bug 7109056. Added by Lakshmi Gopalsami
135 p_payment_method_code IN VARCHAR2 DEFAULT NULL,
136 --Added by Qiong for AP Open Interface of ZX intergration
137 p_Calc_Tax_During_Import_Flag IN VARCHAR2 DEFAULT NULL
138 );
139
140 PROCEDURE insert_ap_inv_lines_interface(
141 p_jai_source IN VARCHAR2,
142 p_invoice_id IN ap_invoice_lines_interface.INVOICE_ID%TYPE,
143 p_invoice_line_id OUT NOCOPY ap_invoice_lines_interface.INVOICE_LINE_ID%TYPE,
144 p_line_number IN ap_invoice_lines_interface.LINE_NUMBER%TYPE DEFAULT NULL,
145 p_line_type_lookup_code IN ap_invoice_lines_interface.LINE_TYPE_LOOKUP_CODE%TYPE DEFAULT NULL,
146 p_line_group_number IN ap_invoice_lines_interface.LINE_GROUP_NUMBER%TYPE DEFAULT NULL,
147 p_amount IN ap_invoice_lines_interface.AMOUNT%TYPE DEFAULT NULL,
148 p_accounting_date IN ap_invoice_lines_interface.ACCOUNTING_DATE%TYPE DEFAULT NULL,
149 p_description IN ap_invoice_lines_interface.DESCRIPTION%TYPE DEFAULT NULL,
150 p_amount_includes_tax_flag IN ap_invoice_lines_interface.AMOUNT_INCLUDES_TAX_FLAG%TYPE DEFAULT NULL,
151 p_prorate_across_flag IN ap_invoice_lines_interface.PRORATE_ACROSS_FLAG%TYPE DEFAULT NULL,
152 p_tax_code IN ap_invoice_lines_interface.TAX_CODE%TYPE DEFAULT NULL,
153 p_final_match_flag IN ap_invoice_lines_interface.FINAL_MATCH_FLAG%TYPE DEFAULT NULL,
154 p_po_header_id IN ap_invoice_lines_interface.PO_HEADER_ID%TYPE DEFAULT NULL,
155 p_po_number IN ap_invoice_lines_interface.PO_NUMBER%TYPE DEFAULT NULL,
156 p_po_line_id IN ap_invoice_lines_interface.PO_LINE_ID%TYPE DEFAULT NULL,
157 p_po_line_number IN ap_invoice_lines_interface.PO_LINE_NUMBER%TYPE DEFAULT NULL,
158 p_po_line_location_id IN ap_invoice_lines_interface.PO_LINE_LOCATION_ID%TYPE DEFAULT NULL,
159 p_po_shipment_num IN ap_invoice_lines_interface.PO_SHIPMENT_NUM%TYPE DEFAULT NULL,
160 p_po_distribution_id IN ap_invoice_lines_interface.PO_DISTRIBUTION_ID%TYPE DEFAULT NULL,
161 p_po_distribution_num IN ap_invoice_lines_interface.PO_DISTRIBUTION_NUM%TYPE DEFAULT NULL,
162 p_po_unit_of_measure IN ap_invoice_lines_interface.PO_UNIT_OF_MEASURE%TYPE DEFAULT NULL,
163 p_inventory_item_id IN ap_invoice_lines_interface.INVENTORY_ITEM_ID%TYPE DEFAULT NULL,
164 p_item_description IN ap_invoice_lines_interface.ITEM_DESCRIPTION%TYPE DEFAULT NULL,
165 p_quantity_invoiced IN ap_invoice_lines_interface.QUANTITY_INVOICED%TYPE DEFAULT NULL,
166 p_ship_to_location_code IN ap_invoice_lines_interface.SHIP_TO_LOCATION_CODE%TYPE DEFAULT NULL,
167 p_unit_price IN ap_invoice_lines_interface.UNIT_PRICE%TYPE DEFAULT NULL,
168 p_distribution_set_id IN ap_invoice_lines_interface.DISTRIBUTION_SET_ID%TYPE DEFAULT NULL,
169 p_distribution_set_name IN ap_invoice_lines_interface.DISTRIBUTION_SET_NAME%TYPE DEFAULT NULL,
170 p_dist_code_concatenated IN ap_invoice_lines_interface.DIST_CODE_CONCATENATED%TYPE DEFAULT NULL,
171 p_dist_code_combination_id IN ap_invoice_lines_interface.DIST_CODE_COMBINATION_ID%TYPE DEFAULT NULL,
172 p_awt_group_id IN ap_invoice_lines_interface.AWT_GROUP_ID%TYPE DEFAULT NULL,
173 p_awt_group_name IN ap_invoice_lines_interface.AWT_GROUP_NAME%TYPE DEFAULT NULL,
174 p_last_updated_by IN ap_invoice_lines_interface.LAST_UPDATED_BY%TYPE DEFAULT NULL,
175 p_last_update_date IN ap_invoice_lines_interface.LAST_UPDATE_DATE%TYPE DEFAULT NULL,
176 p_last_update_login IN ap_invoice_lines_interface.LAST_UPDATE_LOGIN%TYPE DEFAULT NULL,
177 p_created_by IN ap_invoice_lines_interface.CREATED_BY%TYPE DEFAULT NULL,
178 p_creation_date IN ap_invoice_lines_interface.CREATION_DATE%TYPE DEFAULT NULL,
179 --Added below the attribute category and attribute parameters for Bug #3841637
180 p_attribute_category IN ap_invoices_interface.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
181 p_attribute1 IN ap_invoices_interface.ATTRIBUTE1%TYPE DEFAULT NULL,
182 p_attribute2 IN ap_invoices_interface.ATTRIBUTE2%TYPE DEFAULT NULL,
183 p_attribute3 IN ap_invoices_interface.ATTRIBUTE3%TYPE DEFAULT NULL,
184 p_attribute4 IN ap_invoices_interface.ATTRIBUTE4%TYPE DEFAULT NULL,
185 p_attribute5 IN ap_invoices_interface.ATTRIBUTE5%TYPE DEFAULT NULL,
186 p_attribute6 IN ap_invoices_interface.ATTRIBUTE6%TYPE DEFAULT NULL,
187 p_attribute7 IN ap_invoices_interface.ATTRIBUTE7%TYPE DEFAULT NULL,
188 p_attribute8 IN ap_invoices_interface.ATTRIBUTE8%TYPE DEFAULT NULL,
189 p_attribute9 IN ap_invoices_interface.ATTRIBUTE9%TYPE DEFAULT NULL,
190 p_attribute10 IN ap_invoices_interface.ATTRIBUTE10%TYPE DEFAULT NULL,
191 p_attribute11 IN ap_invoices_interface.ATTRIBUTE11%TYPE DEFAULT NULL,
192 p_attribute12 IN ap_invoices_interface.ATTRIBUTE12%TYPE DEFAULT NULL,
193 p_attribute13 IN ap_invoices_interface.ATTRIBUTE13%TYPE DEFAULT NULL,
194 p_attribute14 IN ap_invoices_interface.ATTRIBUTE14%TYPE DEFAULT NULL,
195 p_attribute15 IN ap_invoices_interface.ATTRIBUTE15%TYPE DEFAULT NULL,
196 p_po_release_id IN ap_invoice_lines_interface.PO_RELEASE_ID%TYPE DEFAULT NULL,
197 p_release_num IN ap_invoice_lines_interface.RELEASE_NUM%TYPE DEFAULT NULL,
198 p_account_segment IN ap_invoice_lines_interface.ACCOUNT_SEGMENT%TYPE DEFAULT NULL,
199 p_balancing_segment IN ap_invoice_lines_interface.BALANCING_SEGMENT%TYPE DEFAULT NULL,
200 p_cost_center_segment IN ap_invoice_lines_interface.COST_CENTER_SEGMENT%TYPE DEFAULT NULL,
201 p_project_id IN ap_invoice_lines_interface.PROJECT_ID%TYPE DEFAULT NULL,
202 p_task_id IN ap_invoice_lines_interface.TASK_ID%TYPE DEFAULT NULL,
203 p_expenditure_type IN ap_invoice_lines_interface.EXPENDITURE_TYPE%TYPE DEFAULT NULL,
204 p_expenditure_item_date IN ap_invoice_lines_interface.EXPENDITURE_ITEM_DATE%TYPE DEFAULT NULL,
205 p_expenditure_organization_id IN ap_invoice_lines_interface.EXPENDITURE_ORGANIZATION_ID%TYPE DEFAULT NULL,
206 p_project_accounting_context IN ap_invoice_lines_interface.PROJECT_ACCOUNTING_CONTEXT%TYPE DEFAULT NULL,
207 p_pa_addition_flag IN ap_invoice_lines_interface.PA_ADDITION_FLAG%TYPE DEFAULT NULL,
208 p_pa_quantity IN ap_invoice_lines_interface.PA_QUANTITY%TYPE DEFAULT NULL,
209 p_ussgl_transaction_code IN ap_invoice_lines_interface.USSGL_TRANSACTION_CODE%TYPE DEFAULT NULL,
210 p_stat_amount IN ap_invoice_lines_interface.STAT_AMOUNT%TYPE DEFAULT NULL,
211 p_type_1099 IN ap_invoice_lines_interface.TYPE_1099%TYPE DEFAULT NULL,
212 p_income_tax_region IN ap_invoice_lines_interface.INCOME_TAX_REGION%TYPE DEFAULT NULL,
213 p_assets_tracking_flag IN ap_invoice_lines_interface.ASSETS_TRACKING_FLAG%TYPE DEFAULT NULL,
214 p_price_correction_flag IN ap_invoice_lines_interface.PRICE_CORRECTION_FLAG%TYPE DEFAULT NULL,
215 p_org_id IN ap_invoice_lines_interface.ORG_ID%TYPE DEFAULT NULL,
216 p_receipt_number IN ap_invoice_lines_interface.RECEIPT_NUMBER%TYPE DEFAULT NULL,
217 p_receipt_line_number IN ap_invoice_lines_interface.RECEIPT_LINE_NUMBER%TYPE DEFAULT NULL,
218 p_match_option IN ap_invoice_lines_interface.MATCH_OPTION%TYPE DEFAULT NULL,
219 p_packing_slip IN ap_invoice_lines_interface.PACKING_SLIP%TYPE DEFAULT NULL,
220 p_rcv_transaction_id IN ap_invoice_lines_interface.RCV_TRANSACTION_ID%TYPE DEFAULT NULL,
221 p_pa_cc_ar_invoice_id IN ap_invoice_lines_interface.PA_CC_AR_INVOICE_ID%TYPE DEFAULT NULL,
222 p_pa_cc_ar_invoice_line_num IN ap_invoice_lines_interface.PA_CC_AR_INVOICE_LINE_NUM%TYPE DEFAULT NULL,
223 p_reference_1 IN ap_invoice_lines_interface.REFERENCE_1%TYPE DEFAULT NULL,
224 p_reference_2 IN ap_invoice_lines_interface.REFERENCE_2%TYPE DEFAULT NULL,
225 p_pa_cc_processed_code IN ap_invoice_lines_interface.PA_CC_PROCESSED_CODE%TYPE DEFAULT NULL,
226 p_tax_recovery_rate IN ap_invoice_lines_interface.TAX_RECOVERY_RATE%TYPE DEFAULT NULL,
227 p_tax_recovery_override_flag IN ap_invoice_lines_interface.TAX_RECOVERY_OVERRIDE_FLAG%TYPE DEFAULT NULL,
228 p_tax_recoverable_flag IN ap_invoice_lines_interface.TAX_RECOVERABLE_FLAG%TYPE DEFAULT NULL,
229 p_tax_code_override_flag IN ap_invoice_lines_interface.TAX_CODE_OVERRIDE_FLAG%TYPE DEFAULT NULL,
230 p_tax_code_id IN ap_invoice_lines_interface.TAX_CODE_ID%TYPE DEFAULT NULL,
231 p_credit_card_trx_id IN ap_invoice_lines_interface.CREDIT_CARD_TRX_ID%TYPE DEFAULT NULL,
232 -- Bug 4240179. Changed data for vendor_item_num and award_id
233 -- Added by LGOPALSA
234 p_award_id IN NUMBER DEFAULT NULL,
235 p_vendor_item_num IN VARCHAR2 DEFAULT NULL,
236 p_taxable_flag IN VARCHAR2 DEFAULT NULL,
237 p_price_correct_inv_num IN VARCHAR2 DEFAULT NULL,
238 p_external_doc_line_ref IN VARCHAR2 DEFAULT NULL);
239
240 /* Brathod, For Bug# 4445989, get_apportion_factor signature is modified to use invoice_id and line_number*/
241 FUNCTION get_apportion_factor(
242 -- p_invoice_distribution_id in number
243 pn_invoice_id AP_INVOICE_LINES_ALL.INVOICE_ID%TYPE
244 , pn_invoice_line_number AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE
245 --added the foll parameter by vkarnam for bug #5989740
246 , p_factor_type varchar2 default null
247 ) return number ;
248
249 PROCEDURE submit_pla_boe_for_approval(
250 ERRBUF OUT NOCOPY VARCHAR2,
251 RETCODE OUT NOCOPY VARCHAR2,
252 p_boe_id In VARCHAR2,
253 p_set_of_books_id In Number,
254 p_prv_req_id In Number,
255 p_vendor_id In Number
256 ) ;
257
258 --As part OF R12 Inititive Inventory conversion the following code IS commented BY Ravi
259 --FUNCTION get_opm_assessable_value(p_item_id number,p_qty number,p_exted_price number,P_Cust_Id Number Default 0 ) RETURN NUMBER ;
260
261
262 PROCEDURE pan_update( P_errbuf OUT NOCOPY varchar2,
263 P_return_code OUT NOCOPY varchar2,
264 P_vendor_id in PO_VENDORS.vendor_id%TYPE ,
265 P_old_pan_num IN JAI_AP_TDS_VENDOR_HDRS.pan_no%TYPE,
266 P_new_pan_num IN JAI_AP_TDS_VENDOR_HDRS.pan_no%TYPE,
267 P_debug_flag IN varchar2
268 );
269
270 /*
271 || Added by Ramananda for bug#4584221
272 */
273
274 /* Begin 4579729 */
275
276 FUNCTION get_tds_invoice_batch(p_invoice_id IN NUMBER,
277 p_org_id number default null) --added org_id parameter for bug#9149941
278 RETURN VARCHAR2;
279
280 procedure jai_calc_ipv_erv ( P_errmsg OUT NOCOPY VARCHAR2,
281 P_retcode OUT NOCOPY Number,
282 P_invoice_id in number,
283 P_po_dist_id in number,
284 P_invoice_distribution_id IN NUMBER,
285 P_amount IN NUMBER,
286 P_base_amount IN NUMBER,
287 P_rcv_transaction_id IN NUMBER,
288 P_invoice_price_variance IN NUMBER,
289 P_base_invoice_price_variance IN NUMBER,
290 P_price_var_ccid IN NUMBER,
291 P_Exchange_rate_variance IN NUMBER,
292 P_rate_var_ccid IN NUMBER
293 );
294
295 FUNCTION fetch_tax_target_amt
296 ( p_invoice_id IN NUMBER ,
297 p_line_location_id IN NUMBER ,
298 p_transaction_id IN NUMBER ,
299 p_parent_dist_id IN NUMBER,
300 p_tax_id IN NUMBER
301 )
302 RETURN NUMBER ;
303
304 /* End 4579729 */
305 -- Added by Jia Li for Tax inclusive computation on 2007/12/17, Begin
306 --==========================================================================
307 -- PROCEDURE NAME:
308 --
309 -- acct_inclu_taxes Public
310 --
311 -- DESCRIPTION:
312 --
313 -- This procedure is written that would pass GL entries for inclusive taxes in GL interface
314 --
315 -- PARAMETERS:
316 -- In: pn_invoice_id pass the invoice id for which the accounting needs to done
317 -- pn_invoice_distribution_id pass the invoice distribution id for the item line which the accounting needs to done
318 -- OUt: xv_process_flag Indicates the process flag, 'SS' for success
319 -- 'EE' for expected error
320 -- 'UE' for unexpected error
321 -- xv_process_message Indicates the process message
322 --
323 --
324 -- DESIGN REFERENCES:
325 -- Inclusive Tax Technical Design V1.4.doc
326 --
327 -- CHANGE HISTORY:
328 --
329 -- 17-DEC-2007 Jia Li created
330 --==========================================================================
331 PROCEDURE acct_inclu_taxes
332 ( pn_invoice_id IN NUMBER
333 , pn_invoice_distribution_id IN NUMBER
334 , xv_process_flag OUT NOCOPY VARCHAR2
335 , xv_process_message OUT NOCOPY VARCHAR2
336 );
337
338 -- code ported from BL12.1.3 by zhiwei.xin on 18-MAR-2013 begin
339 --Added by Chong.Lei for DTC bug#13359892 on 20111205 begin
340 ------------------------------------------------------------------------------------------------
341 FUNCTION get_natural_account(pn_sob_id NUMBER, pn_legal_entity_id NUMBER) RETURN VARCHAR2;
342 FUNCTION get_section_code(PN_DISTRIBUTION_CCID NUMBER, PV_SEGMENT_NAME VARCHAR2, Pn_LEGAL_ENTITY_ID NUMBER
343 ,p_accounting_date Date default NULL --Added by Chong for DTC bug#15939571 20121210
344 ) RETURN VARCHAR2;
345 FUNCTION GET_NATURAL_ACCOUNT_VALUE (PN_DISTRIBUTION_CCID NUMBER, PV_SEGMENT_NAME VARCHAR2) RETURN VARCHAR2 ;
346 FUNCTION enable_tds_menu(pn_vendor_id NUMBER,pv_invoice_type VARCHAR2, pn_invoice_id NUMBER) RETURN VARCHAR2;
347 FUNCTION is_tds_calendar_missing RETURN VARCHAR2;
348 FUNCTION is_tds_vendor_setup_missing(pn_vendor_id NUMBER) RETURN VARCHAR2;
349 FUNCTION is_invoice_misc_missing(pn_invoice_id NUMBER) RETURN VARCHAR2;
350 ------------------------------------------------------------------------------------------------
351 --Added by Chong.Lei for DTC bug#13359892 on 20111205 end
352 --Added by Chong.Lei for DTC bug#13359892 on 20111205 begin
353 ------------------------------------------------------------------------------------------------
354 PROCEDURE auto_create_category(p_tax_category_name IN JAI_CMN_TAX_CTGS_ALL.TAX_CATEGORY_NAME%TYPE DEFAULT NULL
355 ,p_tax_category_desc IN JAI_CMN_TAX_CTGS_ALL.TAX_CATEGORY_DESC%TYPE DEFAULT NULL
356 ,p_item_class_cd IN JAI_CMN_TAX_CTGS_ALL.ITEM_CLASS_CD%TYPE DEFAULT NULL
357 ,p_org_id IN JAI_CMN_TAX_CTGS_ALL.ORG_ID%TYPE DEFAULT NULL
358 ,p_object_version_number IN JAI_CMN_TAX_CTGS_ALL.OBJECT_VERSION_NUMBER%TYPE DEFAULT NULL
359 ,p_creation_date IN JAI_CMN_TAX_CTGS_ALL.CREATION_DATE%TYPE DEFAULT NULL
360 ,p_created_by IN JAI_CMN_TAX_CTGS_ALL.CREATED_BY%TYPE DEFAULT NULL
361 ,p_last_update_date IN JAI_CMN_TAX_CTGS_ALL.LAST_UPDATE_DATE%TYPE DEFAULT NULL
362 ,p_last_updated_by IN JAI_CMN_TAX_CTGS_ALL.LAST_UPDATED_BY%TYPE DEFAULT NULL
363 ,p_last_update_login IN JAI_CMN_TAX_CTGS_ALL.LAST_UPDATE_LOGIN%TYPE DEFAULT NULL
364 ,p_tax_id IN JAI_CMN_TAX_CTG_LINES.TAX_ID%TYPE DEFAULT NULL
365 );
366
367 PROCEDURE auto_create_dtc_category(p_tax_category_name IN JAI_CMN_TAX_CTGS_ALL.TAX_CATEGORY_NAME%TYPE
368 ,p_tax_category_desc IN JAI_CMN_TAX_CTGS_ALL.TAX_CATEGORY_DESC%TYPE
369 ,p_item_class_cd IN JAI_CMN_TAX_CTGS_ALL.ITEM_CLASS_CD%TYPE
370 ,p_org_id IN JAI_CMN_TAX_CTGS_ALL.ORG_ID%TYPE
371 ,p_object_version_number IN JAI_CMN_TAX_CTGS_ALL.OBJECT_VERSION_NUMBER%TYPE
372 ,p_tax_id IN JAI_CMN_TAX_CTG_LINES.TAX_ID%TYPE
373 );
374
375 PROCEDURE insert_jai_cmn_taxes_all (
376 p_jai_cmn_taxes_all_rec jai_cmn_taxes_all%ROWTYPE
377 );
378
379 PROCEDURE insert_jai_cmn_tax_ctg_lines (
380 p_jai_cmn_tax_ctg_lines_rec jai_cmn_tax_ctg_lines%ROWTYPE
381 );
382 ------------------------------------------------------------------------------------------------
383 --Added by Chong.Lei for DTC bug#13359892 on 20111205 end
384
385 --Added by Zhiwei Hou for DTC migration Bug#13359892 on 20120228 begin
386 ------------------------------------------------------------------------
387 function get_sec_code_mapping(pv_tds_section_code varchar2) return varchar2;
388 ------------------------------------------------------------------------
389 --Added by Zhiwei Hou for DTC migration Bug#13359892 on 20120228 end
390
391 --Added by Zhiwei Hou for DTC migration bug#13359892 on 20120309 begin
392 -------------------------------------------------------------------------------
393 PROCEDURE dtc_batch_migration
394 (
395 errbuf OUT NOCOPY VARCHAR2
396 ,retcode OUT NOCOPY VARCHAR2
397 ,pv_file_path IN VARCHAR2
398 ,pv_file_old_name IN VARCHAR2
399 ,pv_file_new_name in varchar2
400 );
401 -------------------------------------------------------------------------------
402 --Added by Zhiwei Hou for DTC migration bug#13359892 on 20120309 end
403
404 -- code ported from BL12.1.3 by zhiwei.xin on 18-MAR-2013 end.
405
406
407 END jai_ap_utils_pkg ;