1 PACKAGE AP_APPROVAL_PKG AUTHID CURRENT_USER AS
2 /* $Header: apaprvls.pls 120.27.12020000.2 2012/07/15 08:56:36 mkmeda ship $ */
3
4 /*=============================================================================
5 | Global variable Spec
6 *===========================================================================*/
7
8 g_debug_mode VARCHAR2(1):= 'N';
9
10 ---
11 TYPE g_org_holds_rec IS RECORD(
12 hold_lookup_code ap_holds_all.hold_lookup_code%type,
13 holds_placed NUMBER,
14 holds_released NUMBER,
15 org_id ap_invoices_all.org_id%type);
16
17 TYPE g_org_holds_tab IS TABLE OF g_org_holds_rec
18 INDEX BY BINARY_INTEGER;
19
20 g_org_holds g_org_holds_tab;
21 ---
22
23 TYPE HoldsArray IS TABLE OF ap_holds.hold_lookup_code%TYPE
24 INDEX BY BINARY_INTEGER;
25
26 TYPE CountArray IS TABLE OF NUMBER
27 INDEX BY BINARY_INTEGER;
28 ---
29
30 TYPE Goods_Tolerance_Rec Is RECORD(
31 price_tolerance ap_tolerance_templates.price_tolerance%type,
32 quantity_tolerance ap_tolerance_templates.quantity_tolerance%type,
33 qty_received_tolerance ap_tolerance_templates.qty_received_tolerance%type,
34 max_qty_ord_tolerance ap_tolerance_templates.max_qty_ord_tolerance%type,
35 max_qty_rec_tolerance ap_tolerance_templates.max_qty_rec_tolerance%type,
36 ship_amt_tolerance ap_tolerance_templates.ship_amt_tolerance%type,
37 rate_amt_tolerance ap_tolerance_templates.rate_amt_tolerance%type,
38 total_amt_tolerance ap_tolerance_templates.total_amt_tolerance%type);
39
40 TYPE GOODS_TOLERANCES_TAB IS TABLE OF Goods_Tolerance_Rec
41 INDEX BY PLS_INTEGER;
42
43 G_GOODS_TOLERANCES GOODS_TOLERANCES_TAB;
44 ---
45
46 TYPE Services_Tolerances_Rec IS RECORD(
47 amount_tolerance ap_tolerance_templates.price_tolerance%type,
48 amt_received_tolerance ap_tolerance_templates.quantity_tolerance%type,
49 max_amt_ord_tolerance ap_tolerance_templates.max_qty_ord_tolerance%type,
50 max_amt_rec_tolerance ap_tolerance_templates.max_qty_rec_tolerance%type,
51 ser_ship_amt_tolerance ap_tolerance_templates.ship_amt_tolerance%type,
52 ser_rate_amt_tolerance ap_tolerance_templates.rate_amt_tolerance%type,
53 ser_total_amt_tolerance ap_tolerance_templates.total_amt_tolerance%type);
54
55 TYPE SERVICES_TOLERANCES_TAB IS TABLE OF Services_Tolerances_Rec
56 INDEX BY PLS_INTEGER;
57
58 G_SERVICES_TOLERANCES SERVICES_TOLERANCES_TAB;
59 ---
60
61 TYPE Options_Record Is RECORD(
62 chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%type,
63 set_of_books_id gl_sets_of_books.set_of_books_id%type,
64 automatic_offsets_flag ap_system_parameters.automatic_offsets_flag%type,
65 recalc_pay_schedule_flag ap_system_parameters.recalc_pay_schedule_flag%type,
66 liability_post_lookup_code ap_system_parameters.liability_post_lookup_code%type,
67 rate_var_gain_ccid ap_system_parameters.rate_var_gain_ccid%type,
68 rate_var_loss_ccid ap_system_parameters.rate_var_loss_ccid%type,
69 base_currency_code ap_system_parameters.base_currency_code%type,
70 match_on_tax_flag ap_system_parameters.match_on_tax_flag%type,
71 enforce_tax_from_account ap_system_parameters.enforce_tax_from_account%type,
72 inv_encumbrance_type_id financials_system_parameters.inv_encumbrance_type_id%type,
73 purch_encumbrance_type_id financials_system_parameters.purch_encumbrance_type_id%type,
74 receipt_acceptance_days financials_system_parameters.receipt_acceptance_days%type,
75 gl_date_from_receipt_flag ap_system_parameters.gl_date_from_receipt_flag%type,
76 accounting_method_option ap_system_parameters.accounting_method_option%type,
77 secondary_accounting_method ap_system_parameters.secondary_accounting_method%type,
78 cash_basis_enc_nr_tax financials_system_parameters.cash_basis_enc_nr_tax%type,
79 non_recoverable_tax_flag financials_system_parameters.non_recoverable_tax_flag%type,
80 disc_is_inv_less_tax_flag ap_system_parameters.disc_is_inv_less_tax_flag%type,
81 org_id financials_system_parameters.org_id%type,
82 System_User NUMBER,
83 User_ID NUMBER);
84
85 TYPE Options_Table IS TABLE OF Options_Record
86 INDEX BY PLS_INTEGER;
87
88 G_OPTIONS_TABLE Options_Table;
89 ---
90
91 TYPE Invoice_Rec IS RECORD(
92 invoice_id ap_invoices_all.invoice_id%type,
93 invoice_num ap_invoices_all.invoice_num%type,
94 org_id ap_invoices_all.org_id%type,
95 invoice_amount ap_invoices_all.invoice_amount%type,
96 base_amount ap_invoices_all.base_amount%type,
97 exchange_rate ap_invoices_all.exchange_rate%type,
98 invoice_currency_code ap_invoices_all.invoice_currency_code%type,
99 invoice_amount_limit ap_supplier_sites_all.invoice_amount_limit%type,
100 hold_future_payments_flag ap_supplier_sites_all.hold_future_payments_flag%type,
101 invoice_type_lookup_code ap_invoices_all.invoice_type_lookup_code%type,
102 exchange_date ap_invoices_all.exchange_date%type,
103 exchange_rate_type ap_invoices_all.exchange_rate_type%type,
104 vendor_id ap_invoices_all.vendor_id%type,
105 invoice_date ap_invoices_all.invoice_date%type,
106 disc_is_inv_less_tax_flag ap_invoices_all.disc_is_inv_less_tax_flag%type,
107 exclude_freight_from_discount ap_invoices_all.exclude_freight_from_discount%type,
108 tolerance_id ap_supplier_sites_all.tolerance_id%type,
109 services_tolerance_id ap_supplier_sites_all.services_tolerance_id%type);
110
111 TYPE Invoices_Table IS TABLE OF Invoice_Rec
112 INDEX BY PLS_INTEGER;
113
114 G_INVOICES_TABLE Invoices_Table;
115 G_SELECTED_INVOICES Invoices_Table;
116 G_VALIDATION_REQUEST_ID NUMBER;
117 ---
118
119 /*=============================================================================
120 | Public Procedure Specification
121 *============================================================================*/
122
123 PROCEDURE Cache_Options
124 (p_calling_sequence IN VARCHAR2);
125
126 PROCEDURE Cache_Tolerance_Templates
127 (p_tolerance_id IN NUMBER,
128 p_services_tolerance_id IN NUMBER,
129 p_calling_sequence IN VARCHAR2);
130
131 PROCEDURE Generate_Distributions
132 (p_invoice_rec IN AP_APPROVAL_PKG.Invoice_Rec,
133 p_base_currency_code IN VARCHAR2,
134 p_inv_batch_id IN NUMBER,
135 p_run_option IN VARCHAR2,
136 p_calling_sequence IN VARCHAR2,
137 x_error_code IN VARCHAR2,
138 p_calling_mode IN VARCHAR2 DEFAULT NULL ); --bug6833543
139 --Bug 8346277
140 PROCEDURE Generate_Manual_Awt_Dist
141 (p_invoice_rec IN AP_APPROVAL_PKG.Invoice_Rec,
142 p_base_currency_code IN VARCHAR2,
143 p_inv_batch_id IN NUMBER,
144 p_run_option IN VARCHAR2,
145 p_calling_sequence IN VARCHAR2,
146 x_error_code IN VARCHAR2,
147 p_calling_mode IN VARCHAR2 DEFAULT NULL );
148
149 PROCEDURE Process_Inv_Hold_Status(
150 p_invoice_id IN NUMBER,
151 p_line_location_id IN NUMBER,
152 p_rcv_transaction_id IN NUMBER,
153 p_hold_lookup_code IN VARCHAR2,
154 p_should_have_hold IN VARCHAR2,
155 p_hold_reason IN VARCHAR2,
156 p_system_user IN NUMBER,
157 p_holds IN OUT NOCOPY HOLDSARRAY,
158 p_holds_count IN OUT NOCOPY COUNTARRAY,
159 p_release_count IN OUT NOCOPY COUNTARRAY,
160 p_calling_sequence IN VARCHAR2);
161
162 PROCEDURE Get_Hold_Status(
163 p_invoice_id IN NUMBER,
164 p_line_location_id IN NUMBER,
165 p_rcv_transaction_id IN NUMBER,
166 p_hold_lookup_code IN VARCHAR2,
167 p_system_user IN NUMBER,
168 p_status IN OUT NOCOPY VARCHAR2,
169 p_return_hold_reason IN OUT NOCOPY VARCHAR2,
170 p_user_id IN OUT NOCOPY VARCHAR2,
171 p_resp_id IN OUT NOCOPY VARCHAR2,
172 p_calling_sequence IN VARCHAR2);
173
174 -- added new procedure for the bug9553683/11659334
175 PROCEDURE Release_fund_holds
176 (P_Invoice_ID IN NUMBER,
177 p_system_user IN NUMBER,
178 p_calling_mode IN VARCHAR2,
179 p_holds IN OUT NOCOPY HOLDSARRAY,
180 p_holds_count IN OUT NOCOPY COUNTARRAY,
181 p_release_count IN OUT NOCOPY COUNTARRAY,
182 p_calling_sequence IN VARCHAR2);
183
184 PROCEDURE Approve(
185 p_run_option IN VARCHAR2,
186 p_invoice_batch_id IN NUMBER,
187 p_begin_invoice_date IN DATE DEFAULT NULL,
188 p_end_invoice_date IN DATE DEFAULT NULL,
189 p_vendor_id IN NUMBER,
190 p_pay_group IN VARCHAR2,
191 p_invoice_id IN NUMBER,
192 p_entered_by IN NUMBER,
193 p_set_of_books_id IN NUMBER,
194 p_trace_option IN VARCHAR2,
195 p_conc_flag IN VARCHAR2,
196 p_holds_count IN OUT NOCOPY NUMBER,
197 p_approval_status IN OUT NOCOPY VARCHAR2,
198 p_funds_return_code OUT NOCOPY VARCHAR2, -- 4276409 (3462325)
199 p_calling_mode IN VARCHAR2 DEFAULT 'APPROVE',
200 p_calling_sequence IN VARCHAR2,
201 p_debug_switch IN VARCHAR2 DEFAULT 'N',
202 p_budget_control IN VARCHAR2 DEFAULT 'Y',
203 p_commit IN VARCHAR2 DEFAULT 'Y');
204
205 PROCEDURE Release_Hold(
206 p_invoice_id IN NUMBER,
207 p_line_location_id IN NUMBER,
208 p_rcv_transaction_id IN NUMBER,
209 p_hold_lookup_code IN VARCHAR2,
210 p_holds IN OUT NOCOPY HOLDSARRAY,
211 p_release_count IN OUT NOCOPY COUNTARRAY,
212 p_calling_sequence IN VARCHAR2);
213
214 PROCEDURE Check_Insufficient_Line_Data(
215 p_inv_line_rec IN AP_INVOICES_PKG.r_invoice_line_rec,
216 p_system_user IN NUMBER,
217 p_holds IN OUT NOCOPY HOLDSARRAY,
218 p_holds_count IN OUT NOCOPY COUNTARRAY,
219 p_release_count IN OUT NOCOPY COUNTARRAY,
220 p_insufficient_data_exist OUT NOCOPY BOOLEAN,
221 --ETAX: Validation
222 p_calling_mode IN VARCHAR2,
223 p_calling_sequence IN VARCHAR2);
224
225 FUNCTION Execute_Dist_Generation_Check(
226 p_batch_id IN NUMBER,
227 p_invoice_date IN DATE,
228 p_vendor_id IN NUMBER,
229 p_invoice_currency IN VARCHAR2,
230 p_exchange_rate IN NUMBER,
231 p_exchange_rate_type IN VARCHAR2,
232 p_exchange_date IN DATE,
233 p_inv_line_rec IN AP_INVOICES_PKG.r_invoice_line_rec,
234 p_system_user IN NUMBER,
235 p_holds IN OUT NOCOPY HOLDSARRAY,
236 p_holds_count IN OUT NOCOPY COUNTARRAY,
237 p_release_count IN OUT NOCOPY COUNTARRAY,
238 p_generate_permanent IN VARCHAR2,
239 --ETAX: Validation
240 p_calling_mode IN VARCHAR2 DEFAULT 'VALIDATION',
241 p_error_code OUT NOCOPY VARCHAR2,
242 p_curr_calling_sequence IN VARCHAR2) RETURN BOOLEAN;
243
244 --Bugfix:4673607
245 FUNCTION Is_Product_Registered(P_Application_Id IN NUMBER,
246 X_Registration_Api OUT NOCOPY VARCHAR2,
247 X_Registration_View OUT NOCOPY VARCHAR2,
248 P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN;
249
250 --Bugfix:4673607
251 FUNCTION Gen_Dists_From_Registration(
252 P_Batch_Id IN NUMBER,
253 P_Invoice_Line_Rec IN AP_INVOICES_PKG.r_invoice_line_rec,
254 P_Registration_Api IN VARCHAR2,
255 P_Registration_View IN VARCHAR2,
256 P_Generate_Permanent IN VARCHAR2,
257 X_Error_Code OUT NOCOPY VARCHAR2,
258 P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN;
259
260 --BugFix: 3489536
261 FUNCTION Batch_Approval
262 (p_run_option IN VARCHAR2,
263 p_sob_id IN NUMBER,
264 p_inv_start_date IN DATE,
265 p_inv_end_date IN DATE,
266 p_inv_batch_id IN NUMBER,
267 p_vendor_id IN NUMBER,
268 p_pay_group IN VARCHAR2,
269 p_invoice_id IN NUMBER,
270 p_entered_by IN NUMBER,
271 p_debug_switch IN VARCHAR2,
272 p_conc_request_id IN NUMBER,
273 p_commit_size IN NUMBER,
274 p_org_id IN NUMBER,
275 p_report_holds_count OUT NOCOPY NUMBER,
276 p_transaction_num IN NUMBER DEFAULT NULL) RETURN BOOLEAN; -- Bug 8234569
277 -- Bug 8647857
278 -- Added DEFAULT value as NULL to parameter p_transaction_num
279
280 -- added for bug 6892789
281 --This function returns base amount after rouding.
282 --Also calculates the rounding amount for the next line
283 FUNCTION get_adjusted_base_amount(p_base_amount IN NUMBER,
284 p_rounding_amt OUT NOCOPY NUMBER,
285 p_next_line_rounding_amt IN OUT NOCOPY NUMBER,
286 p_amount IN NUMBER DEFAULT NULL) -- Bug 12686348
287 RETURN NUMBER;
288
289 --Start 8691645
290 PROCEDURE CHECK_CCR_VENDOR(
291 P_INVOICE_ID IN AP_INVOICES.INVOICE_ID%TYPE,
292 P_VENDOR_ID IN AP_INVOICES.VENDOR_ID%TYPE,
293 P_VENDOR_SITE_ID IN AP_INVOICES.VENDOR_SITE_ID%TYPE,
294 P_REMIT_TO_SUPPLIER_SITE_ID IN AP_INVOICES.REMIT_TO_SUPPLIER_SITE_ID%TYPE,
295 P_SYSTEM_USER IN NUMBER,
296 P_HOLDS IN OUT NOCOPY HOLDSARRAY,
297 P_HOLDS_COUNT IN OUT NOCOPY COUNTARRAY,
298 P_RELEASE_COUNT IN OUT NOCOPY COUNTARRAY,
299 P_VENDOR_SITE_REG_EXPIRED OUT NOCOPY VARCHAR2,
300 P_CALLING_SEQUENCE IN VARCHAR2);
301
302
303 PROCEDURE UPDATE_SCHEDULES(P_INVOICE_ID IN AP_INVOICES.INVOICE_ID%TYPE,
304 P_CALLING_SEQUENCE IN VARCHAR2);
305
306 PROCEDURE BATCH_APPROVAL_FOR_VENDOR(P_VENDOR_ID IN AP_INVOICES.VENDOR_ID%TYPE,
307 P_CALLING_SEQUENCE IN VARCHAR2);
308 --End 8691645
309
310 --GTAS Project Bug#13464635 Begin
311
312 PROCEDURE POPULATE_GDF_FOR_FV(
313 P_invoice_id IN NUMBER,
314 P_curr_calling_sequence IN VARCHAR2);
315
316 PROCEDURE CHECK_GDF_VALID(
317 P_INVOICE_ID IN NUMBER,
318 P_SYSTEM_USER IN NUMBER,
319 P_HOLDS IN OUT NOCOPY HOLDSARRAY,
320 P_HOLDS_COUNT IN OUT NOCOPY COUNTARRAY,
321 P_RELEASE_COUNT IN OUT NOCOPY COUNTARRAY,
322 P_CALLING_SEQUENCE IN VARCHAR2);
323
324 --End Bug#13464635
325
326 END AP_APPROVAL_PKG;