1 PACKAGE AP_Acctg_Data_Fix_PKG AUTHID CURRENT_USER AS
2 /* $Header: apgdfals.pls 120.14.12020000.4 2012/10/16 11:12:40 vinerao ship $ */
3
4 TYPE Event_ID
5 IS TABLE OF
6 AP_ACCOUNTING_EVENTS_ALL.accounting_event_id%TYPE
7 INDEX BY BINARY_INTEGER;
8
9 TYPE Source_ID
10 IS TABLE OF
11 AP_ACCOUNTING_EVENTS_ALL.Source_id%TYPE
12 INDEX BY BINARY_INTEGER;
13 --Bug5073523
14 TYPE Accounting_date
15 is TABLE OF
16 AP_ACCOUNTING_EVENTS_ALL.Accounting_date%TYPE
17 INDEX BY BINARY_INTEGER;
18
19 TYPE SOB_ID
20 is TABLE OF
21 AP_INVOICE_DISTRIBUTIONS_ALL.set_of_books_id%TYPE
22 INDEX BY BINARY_INTEGER;
23
24 TYPE Source_Table
25 IS TABLE OF
26 AP_ACCOUNTING_EVENTS_ALL.Source_Table%TYPE
27 INDEX BY BINARY_INTEGER;
28
29 TYPE Org_ID
30 IS TABLE OF
31 AP_ACCOUNTING_EVENTS_ALL.org_id%TYPE
32 INDEX BY BINARY_INTEGER;
33
34 TYPE Period_Name
35 IS TABLE OF
36 VARCHAR2(15)
37 INDEX BY BINARY_INTEGER;
38
39 TYPE Header_ID
40 IS TABLE OF
41 NUMBER(15)
42 INDEX BY BINARY_INTEGER;
43
44 TYPE Group_ID
45 IS TABLE OF
46 NUMBER(15)
47 INDEX BY BINARY_INTEGER;
48
49 TYPE Vendor_Name
50 IS TABLE OF
51 PO_VENDORS.vendor_name%TYPE
52 INDEX BY BINARY_INTEGER;
53
54 TYPE Rejection_List_Tab_Typ
55 IS TABLE OF
56 VARCHAR2(1000)
57 INDEX BY BINARY_INTEGER;
58
59 TYPE Pay_Dist_Rec IS RECORD
60 (payment_hist_dist_id AP_PAYMENT_HIST_DISTS.PAYMENT_HIST_DIST_ID%TYPE,
61 accounting_event_id AP_PAYMENT_HIST_DISTS.ACCOUNTING_EVENT_ID%TYPE,
62 pay_dist_lookup_code AP_PAYMENT_HIST_DISTS.PAY_DIST_LOOKUP_CODE%TYPE,
63 invoice_distribution_id AP_PAYMENT_HIST_DISTS.INVOICE_DISTRIBUTION_ID%TYPE,
64 amount AP_PAYMENT_HIST_DISTS.AMOUNT%TYPE,
65 payment_history_id AP_PAYMENT_HIST_DISTS.PAYMENT_HISTORY_ID%TYPE,
66 invoice_payment_id AP_PAYMENT_HIST_DISTS.INVOICE_PAYMENT_ID%TYPE,
67 bank_curr_amount AP_PAYMENT_HIST_DISTS.BANK_CURR_AMOUNT%TYPE,
68 cleared_base_amount AP_PAYMENT_HIST_DISTS.CLEARED_BASE_AMOUNT%TYPE,
69 historical_flag AP_PAYMENT_HIST_DISTS.HISTORICAL_FLAG%TYPE,
70 invoice_dist_amount AP_PAYMENT_HIST_DISTS.INVOICE_DIST_AMOUNT%TYPE,
71 invoice_dist_base_amount AP_PAYMENT_HIST_DISTS.INVOICE_DIST_BASE_AMOUNT%TYPE,
72 invoice_adjustment_event_id AP_PAYMENT_HIST_DISTS.INVOICE_ADJUSTMENT_EVENT_ID%TYPE,
73 matured_base_amount AP_PAYMENT_HIST_DISTS.MATURED_BASE_AMOUNT%TYPE,
74 paid_base_amount AP_PAYMENT_HIST_DISTS.PAID_BASE_AMOUNT%TYPE,
75 rounding_amt AP_PAYMENT_HIST_DISTS.ROUNDING_AMT%TYPE,
76 reversal_flag AP_PAYMENT_HIST_DISTS.REVERSAL_FLAG%TYPE,
77 reversed_pay_hist_dist_id AP_PAYMENT_HIST_DISTS.REVERSED_PAY_HIST_DIST_ID%TYPE,
78 created_by AP_PAYMENT_HIST_DISTS.CREATED_BY%TYPE,
79 creation_date AP_PAYMENT_HIST_DISTS.CREATION_DATE%TYPE,
80 last_update_date AP_PAYMENT_HIST_DISTS.LAST_UPDATE_DATE%TYPE,
81 last_updated_by AP_PAYMENT_HIST_DISTS.LAST_UPDATED_BY%TYPE,
82 last_update_login AP_PAYMENT_HIST_DISTS.LAST_UPDATE_LOGIN%TYPE,
83 program_application_id AP_PAYMENT_HIST_DISTS.PROGRAM_APPLICATION_ID%TYPE,
84 program_id AP_PAYMENT_HIST_DISTS.PROGRAM_ID%TYPE,
85 program_login_id AP_PAYMENT_HIST_DISTS.PROGRAM_LOGIN_ID%TYPE,
86 program_update_date AP_PAYMENT_HIST_DISTS.PROGRAM_UPDATE_DATE%TYPE,
87 request_id AP_PAYMENT_HIST_DISTS.REQUEST_ID%TYPE,
88 awt_related_id AP_PAYMENT_HIST_DISTS.AWT_RELATED_ID%TYPE,
89 release_inv_dist_derived_from AP_PAYMENT_HIST_DISTS.RELEASE_INV_DIST_DERIVED_FROM%TYPE,
90 pa_addition_flag AP_PAYMENT_HIST_DISTS.PA_ADDITION_FLAG%TYPE,
91 amount_variance AP_PAYMENT_HIST_DISTS.AMOUNT_VARIANCE%TYPE,
92 invoice_base_amt_variance AP_PAYMENT_HIST_DISTS.INVOICE_BASE_AMT_VARIANCE%TYPE,
93 quantity_variance AP_PAYMENT_HIST_DISTS.QUANTITY_VARIANCE%TYPE,
94 invoice_base_qty_variance AP_PAYMENT_HIST_DISTS.INVOICE_BASE_QTY_VARIANCE%TYPE,
95 write_off_code_combination GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE);
96
97
98 TYPE Pay_Dist_Tab_Typ IS TABLE OF Pay_Dist_Rec INDEX BY BINARY_INTEGER;
99
100 TYPE Prepay_Dist_Rec IS RECORD
101 (prepay_app_dist_id AP_PREPAY_APP_DISTS.PREPAY_APP_DIST_ID%TYPE,
102 prepay_dist_lookup_code AP_PREPAY_APP_DISTS.PREPAY_DIST_LOOKUP_CODE%TYPE,
103 invoice_distribution_id AP_PREPAY_APP_DISTS.INVOICE_DISTRIBUTION_ID%TYPE,
104 prepay_app_distribution_id AP_PREPAY_APP_DISTS.PREPAY_APP_DISTRIBUTION_ID%TYPE,
105 accounting_event_id AP_PREPAY_APP_DISTS.ACCOUNTING_EVENT_ID%TYPE,
106 prepay_history_id AP_PREPAY_APP_DISTS.PREPAY_HISTORY_ID%TYPE,
107 prepay_exchange_date AP_PREPAY_APP_DISTS.PREPAY_EXCHANGE_DATE%TYPE,
108 prepay_pay_exchange_date AP_PREPAY_APP_DISTS.PREPAY_PAY_EXCHANGE_DATE%TYPE,
109 prepay_clr_exchange_date AP_PREPAY_APP_DISTS.PREPAY_CLR_EXCHANGE_DATE%TYPE,
110 prepay_exchange_rate AP_PREPAY_APP_DISTS.PREPAY_EXCHANGE_RATE%TYPE,
111 prepay_pay_exchange_rate AP_PREPAY_APP_DISTS.PREPAY_PAY_EXCHANGE_RATE%TYPE,
112 prepay_clr_exchange_rate AP_PREPAY_APP_DISTS.PREPAY_CLR_EXCHANGE_RATE%TYPE,
113 prepay_exchange_rate_type AP_PREPAY_APP_DISTS.PREPAY_EXCHANGE_RATE_TYPE%TYPE,
114 prepay_pay_exchange_rate_type AP_PREPAY_APP_DISTS.PREPAY_PAY_EXCHANGE_RATE_TYPE%TYPE,
115 prepay_clr_exchange_rate_type AP_PREPAY_APP_DISTS.PREPAY_CLR_EXCHANGE_RATE_TYPE%TYPE,
116 reversed_prepay_app_dist_id AP_PREPAY_APP_DISTS.REVERSED_PREPAY_APP_DIST_ID%TYPE,
117 amount AP_PREPAY_APP_DISTS.AMOUNT%TYPE,
118 base_amt_at_prepay_xrate AP_PREPAY_APP_DISTS.BASE_AMT_AT_PREPAY_XRATE%TYPE,
119 base_amt_at_prepay_pay_xrate AP_PREPAY_APP_DISTS.BASE_AMT_AT_PREPAY_PAY_XRATE%TYPE,
120 base_amount AP_PREPAY_APP_DISTS.BASE_AMOUNT%TYPE,
121 base_amt_at_prepay_clr_xrate AP_PREPAY_APP_DISTS.BASE_AMT_AT_PREPAY_CLR_XRATE%TYPE,
122 rounding_amt AP_PREPAY_APP_DISTS.ROUNDING_AMT%TYPE,
123 round_amt_at_prepay_xrate AP_PREPAY_APP_DISTS.ROUND_AMT_AT_PREPAY_XRATE%TYPE,
124 round_amt_at_prepay_pay_xrate AP_PREPAY_APP_DISTS.ROUND_AMT_AT_PREPAY_PAY_XRATE%TYPE,
125 round_amt_at_prepay_clr_xrate AP_PREPAY_APP_DISTS.ROUND_AMT_AT_PREPAY_CLR_XRATE%TYPE,
126 last_updated_by AP_PREPAY_APP_DISTS.LAST_UPDATED_BY%TYPE,
127 last_update_date AP_PREPAY_APP_DISTS.LAST_UPDATE_DATE%TYPE,
128 last_update_login AP_PREPAY_APP_DISTS.LAST_UPDATE_LOGIN%TYPE,
129 created_by AP_PREPAY_APP_DISTS.CREATED_BY%TYPE,
130 creation_date AP_PREPAY_APP_DISTS.CREATION_DATE%TYPE,
131 program_application_id AP_PREPAY_APP_DISTS.PROGRAM_APPLICATION_ID%TYPE,
132 program_id AP_PREPAY_APP_DISTS.PROGRAM_ID%TYPE,
133 program_update_date AP_PREPAY_APP_DISTS.PROGRAM_UPDATE_DATE%TYPE,
134 request_id AP_PREPAY_APP_DISTS.REQUEST_ID%TYPE,
135 awt_related_id AP_PREPAY_APP_DISTS.AWT_RELATED_ID%TYPE,
136 release_inv_dist_derived_from AP_PREPAY_APP_DISTS.RELEASE_INV_DIST_DERIVED_FROM%TYPE,
137 pa_addition_flag AP_PREPAY_APP_DISTS.PA_ADDITION_FLAG%TYPE,
138 bc_event_id AP_PREPAY_APP_DISTS.BC_EVENT_ID%TYPE,
139 amount_variance AP_PREPAY_APP_DISTS.AMOUNT_VARIANCE%TYPE,
140 invoice_base_amt_variance AP_PREPAY_APP_DISTS.INVOICE_BASE_AMT_VARIANCE%TYPE,
141 quantity_variance AP_PREPAY_APP_DISTS.QUANTITY_VARIANCE%TYPE,
142 invoice_base_qty_variance AP_PREPAY_APP_DISTS.INVOICE_BASE_QTY_VARIANCE%TYPE,
143 write_off_code_combination GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE);
144
145 TYPE Prepay_Dist_Tab_Typ IS TABLE OF Prepay_Dist_Rec INDEX BY BINARY_INTEGER;
146
147
148 G_Group_ID Group_ID;
149
150 G_Bug_Number NUMBER;
151 NAMES_FILE UTL_FILE.FILE_TYPE ;
152
153 /* Procedure to open the log files on the instance where the datafix
154 script is being run. The log file contains the log messages
155 and the report outputs written by the data fix scripts.
156 The file location is the environment's 'utl_file_dir' parameter. */
157 PROCEDURE Open_Log_Out_Files
158 (p_bug_number IN VARCHAR2,
159 p_file_location OUT NOCOPY VARCHAR2);
160
161
162 /* Procedure to close the log files on the instance once all the log
163 messages are written to it. */
164 PROCEDURE Close_Log_Out_Files;
165
166
167 /* Procedure to create temproary backup tables for the accounting */
168 PROCEDURE Create_Temp_Acctg_Tables
169 (p_bug_number IN NUMBER);
170
171
172 /* Procedure to get all the columns for a particular table.
173 This procedure gets called from Back_Up_Acctg procedure. */
174 PROCEDURE get_cols
175 (tab_name IN VARCHAR2,
176 ret_str OUT NOCOPY VARCHAR2);
177
178 /* Overload get_cols to handle the case where a schema needs to
179 be designated because the table exists in more than one schema. */
180 PROCEDURE get_cols
181 (tab_name IN VARCHAR2,
182 schema_name IN VARCHAR2,
183 ret_str OUT NOCOPY VARCHAR2);
184
185 /* Procedure to get the backup of all the Accounting (XLA) tables. */
186 PROCEDURE Back_Up_Acctg
187 (p_bug_number IN NUMBER,
188 P_Driver_Table in VARCHAR2 DEFAULT NULL,
189 P_Calling_Sequence in VARCHAR2 DEFAULT NULL
190 );
191
192
193 /* Procedure to print messages in the Log file */
194 PROCEDURE Print
195 (p_message IN VARCHAR2,
196 p_calling_sequence IN VARCHAR2 DEFAULT NULL);
197
198
199 /* Procedure to print the values in the table and column list
200 passed as parameters, in HTML table format, into the Log file. */
201 /* GSI Bug 9490277 : Added parameter p_print_in_output
202 If this is passed as TRUE then table will be printed in output file instead of being printed in log file */
203 PROCEDURE Print_Html_Table
204 (p_select_list IN VARCHAR2,
205 p_table_in IN VARCHAR2,
206 p_where_in IN VARCHAR2 DEFAULT NULL,
207 p_calling_sequence IN VARCHAR2 DEFAULT NULL,
208 p_print_in_output IN BOOLEAN DEFAULT FALSE /* GSI Bug 9490277 */
209 );
210
211
212 /* Procedure to backup the data from the source table to destination
213 table. It also takes in as input SELECT LIST which determine
214 the list of columns which will be backed up. The additional
215 WHERE caluse can also be passed in as input. */
216 PROCEDURE Backup_data
217 (p_source_table IN VARCHAR2,
218 p_destination_table IN VARCHAR2,
219 p_select_list IN VARCHAR2,
220 p_where_clause IN VARCHAR2,
221 p_calling_sequence IN VARCHAR2 DEFAULT NULL);
222
223 PROCEDURE apps_initialize
224 (p_user_name IN FND_USER.USER_NAME%TYPE,
225 p_resp_name IN FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE,
226 p_calling_sequence IN VARCHAR2);
227
228 PROCEDURE check_period
229 (p_bug_no IN NUMBER,
230 p_driver_table IN VARCHAR2,
231 p_check_event_date IN VARCHAR2 DEFAULT 'Y',
232 p_check_sysdate IN VARCHAR2 DEFAULT 'N',
233 p_chk_proposed_undo_date IN VARCHAR2 DEFAULT 'N',
234 p_update_process_flag IN VARCHAR2,
235 P_calc_undo_date IN VARCHAR2,
236 p_commit_flag IN VARCHAR2 DEFAULT 'N',
237 p_calling_sequence IN VARCHAR2);
238
239 PROCEDURE check_ccid
240 (p_bug_no IN NUMBER,
241 p_driver_table IN VARCHAR2,
242 p_update_process_flag IN VARCHAR2,
243 p_commit_flag IN VARCHAR2 DEFAULT 'N',
244 p_calling_sequence IN VARCHAR2);
245
246
247 /* Procedure to undo Accounting for an invoice or payment
248 Parameters : p_source_table - Value is either AP_INVOICES or
249 AP_PAYMENTS
250 p_source_id - For AP_INVOICES it is invoice_id
251 for AP_PAYMENTS it is check_id
252 p_Event_id - It is a non-mandatory field
253 and is accounting event id
254 p_calling_sequence - Name of the package that
255 Calls Undo accounting
256 p_bug_id - Bug Id
257 */
258 PROCEDURE Undo_Accounting
259 (p_Source_Table IN VARCHAR2,
260 p_Source_Id IN NUMBER,
261 p_Event_Id IN NUMBER DEFAULT NULL,
262 p_skip_date_calc IN VARCHAR2 DEFAULT 'N',
263 p_undo_date IN DATE,
264 p_undo_period IN VARCHAR2,
265 p_bug_id IN NUMBER DEFAULT NULL,
266 p_Gl_Date IN DATE DEFAULT NULL, --Bug#8471406
267 p_rev_event_id OUT NOCOPY NUMBER,
268 p_new_event_id OUT NOCOPY NUMBER,
269 p_return_code OUT NOCOPY VARCHAR2,
270 p_calling_sequence IN VARCHAR2);
271
272
273 /* Procedure to undo Accounting for an invoice or payment
274 Parameters : p_source_table - Value is either AP_INVOICES or
275 AP_PAYMENTS
276 p_source_id - For AP_INVOICES it is invoice_id
277 for AP_PAYMENTS it is check_id
278 p_Event_id - It is a non-mandatory field
279 and is accounting event id
280 p_calling_sequence - Name of the package that
281 Calls Undo accounting
282 p_bug_id - Bug Id
283 */
284 PROCEDURE Undo_Accounting
285 (p_source_table IN VARCHAR2,
286 p_source_id IN NUMBER,
287 p_Event_id IN NUMBER DEFAULT NULL,
288 p_calling_sequence IN VARCHAR2 DEFAULT NULL,
289 p_bug_id IN NUMBER DEFAULT NULL,
290 p_GL_Date IN DATE DEFAULT NULL --Bug#8471406
291 );
292
293 PROCEDURE Del_Nonfinal_xla_entries
294 (p_event_id IN NUMBER,
295 p_delete_event IN VARCHAR2,
296 p_commit_flag IN VARCHAR2,
297 p_calling_sequence IN VARCHAR2);
298
299 PROCEDURE undo_acctg_entries
300 (p_bug_no IN NUMBER,
301 p_driver_table IN VARCHAR2,
302 p_calling_sequence IN VARCHAR2);
303
304 PROCEDURE push_error(p_error_code IN VARCHAR2,
305 p_error_stack IN OUT NOCOPY Rejection_List_Tab_Typ);
306
307 PROCEDURE final_pay_round_dfix
308 (p_invoice_id IN NUMBER,
309 p_op_event_id OUT NOCOPY NUMBER,
310 p_op_event_type OUT NOCOPY VARCHAR2,
311 p_return_status OUT NOCOPY BOOLEAN,
312 p_rejection_tab OUT NOCOPY Rejection_List_Tab_Typ,
313 p_rej_count OUT NOCOPY NUMBER,
314 p_error_msg OUT NOCOPY VARCHAR2,
315 p_pay_dist_tab OUT NOCOPY Pay_Dist_Tab_Typ,
316 p_prepay_dist_tab OUT NOCOPY Prepay_Dist_Tab_Typ,
317 p_commit_flag IN VARCHAR2,
318 p_calling_sequence IN VARCHAR2);
319
320
321 Function Is_period_open(P_Date IN date,
322 P_Org_Id IN number default mo_global.get_current_org_id) return varchar2;
323
324
325 Function get_open_period_start_date(P_Org_Id IN number) return date;
326
327 FUNCTION delete_cascade_adjustments
328 (p_source_type IN VARCHAR2,
329 p_source_id IN NUMBER,
330 p_related_event_id IN NUMBER DEFAULT NULL)
331 RETURN BOOLEAN;
332
333 PROCEDURE repop_prepay_dists
334 (p_bug_no IN NUMBER,
335 p_driver_table IN VARCHAR2,
336 p_calling_sequence IN VARCHAR2);
337
338 PROCEDURE del_cascade_adj_entries
339 (p_bug_no IN NUMBER,
340 p_driver_table IN VARCHAR2,
341 p_calling_sequence IN VARCHAR2);
342
343 /*Added Function UOM_CONVERT for Bug9756279*/
344 FUNCTION UOM_CONVERT
345 (from_unit in varchar2
346 ,to_unit in varchar2
347 ,item_id in number)
348 RETURN NUMBER;
349
350 -- Bug 10072990 start
351 PROCEDURE undo_inv_accounting(
352 p_source_table IN VARCHAR2,
353 p_source_id IN NUMBER,
354 p_event_id IN NUMBER DEFAULT NULL,
355 p_skip_date_calc IN VARCHAR2 DEFAULT 'N',
356 p_undo_date IN DATE,
357 p_undo_period IN VARCHAR2,
358 p_bug_id IN NUMBER DEFAULT NULL,
359 p_gl_date IN DATE DEFAULT NULL,
360 p_rev_event_id OUT NOCOPY NUMBER,
361 p_new_event_id OUT NOCOPY NUMBER,
362 p_return_code OUT NOCOPY VARCHAR2,
363 p_calling_sequence IN VARCHAR2 );
364
365 PROCEDURE undo_inv_accounting(
366 p_source_id IN NUMBER,
367 p_event_id IN NUMBER DEFAULT NULL,
368 p_calling_sequence IN VARCHAR2 DEFAULT NULL,
369 p_bug_id IN NUMBER DEFAULT NULL,
370 p_gl_date IN DATE DEFAULT NULL);
371 -- Bug 10072990 end
372
373 -- Bug11673966
374 FUNCTION get_net_prepay_app(p_event_id NUMBER)
375 RETURN NUMBER;
376
377 -- Bug11673966
378 FUNCTION get_net_prepay_acct(P_Event_ID NUMBER,
379 P_Ledger_ID NUMBER)
380 RETURN NUMBER;
381
382 -- Bug11673966
383 FUNCTION get_net_adj_acct(P_Event_ID NUMBER,
384 P_Ledger_ID NUMBER)
385 RETURN NUMBER;
386
387 -- Bug 10635312 start
388 FUNCTION po_vendor_contact_merge(new_vendor_contact_id NUMBER,
389 old_vendor_contact_id NUMBER)
390 RETURN NUMBER;
391 -- Bug 10635312 end
392
393 --Bug 13405297
394 FUNCTION po_vendor_contact_merge(p_driver_table IN VARCHAR2,
395 p_calling_sequence IN VARCHAR2)
396 RETURN NUMBER;
397
398 --Bug 14731725
399 FUNCTION send_mail_clob_impl(v_recipient VARCHAR2,
400 v_file_location VARCHAR2)
401 RETURN BOOLEAN;
402
403 END AP_Acctg_Data_Fix_PKG;
[Home] [Help]