1 PACKAGE AP_INVOICE_LINES_PKG AUTHID CURRENT_USER AS
2 /* $Header: apinlins.pls 120.17 2011/01/19 11:42:31 sbonala ship $ */
3
4 TYPE r_dist_info IS RECORD
5 (
6 dist_line_num AP_INVOICE_DISTRIBUTIONS.distribution_line_number%TYPE
7 ,accounting_date AP_INVOICE_DISTRIBUTIONS.accounting_date%TYPE
8 ,period_name AP_INVOICE_DISTRIBUTIONS.period_name%TYPE
9 ,description AP_INVOICE_DISTRIBUTIONS.description%TYPE
10 ,dist_ccid AP_INVOICE_DISTRIBUTIONS.dist_code_combination_id%TYPE
11 ,charge_applicable_to_dist
12 AP_INVOICE_DISTRIBUTIONS.charge_applicable_to_dist_id%TYPE
13 ,project_id AP_INVOICE_DISTRIBUTIONS.project_id%TYPE
14 ,task_id AP_INVOICE_DISTRIBUTIONS.task_id%TYPE
15 ,expenditure_type AP_INVOICE_DISTRIBUTIONS.expenditure_type%TYPE
16 ,expenditure_organization_id
17 AP_INVOICE_DISTRIBUTIONS.expenditure_organization_id%TYPE
18 ,expenditure_item_date AP_INVOICE_DISTRIBUTIONS.expenditure_item_date%TYPE
19 ,project_accounting_context AP_INVOICE_DISTRIBUTIONS.project_accounting_context%TYPE
20 ,pa_quantity AP_INVOICE_DISTRIBUTIONS.pa_quantity%TYPE
21 ,pa_addition_flag AP_INVOICE_DISTRIBUTIONS.pa_addition_flag%TYPE
22 ,award_id AP_INVOICE_DISTRIBUTIONS.award_id%TYPE
23 ,attribute_category AP_INVOICE_DISTRIBUTIONS.attribute_category%TYPE
24 ,attribute1 AP_INVOICE_DISTRIBUTIONS.attribute1%TYPE
25 ,attribute2 AP_INVOICE_DISTRIBUTIONS.attribute2%TYPE
26 ,attribute3 AP_INVOICE_DISTRIBUTIONS.attribute3%TYPE
27 ,attribute4 AP_INVOICE_DISTRIBUTIONS.attribute4%TYPE
28 ,attribute5 AP_INVOICE_DISTRIBUTIONS.attribute5%TYPE
29 ,attribute6 AP_INVOICE_DISTRIBUTIONS.attribute6%TYPE
30 ,attribute7 AP_INVOICE_DISTRIBUTIONS.attribute7%TYPE
31 ,attribute8 AP_INVOICE_DISTRIBUTIONS.attribute8%TYPE
32 ,attribute9 AP_INVOICE_DISTRIBUTIONS.attribute9%TYPE
33 ,attribute10 AP_INVOICE_DISTRIBUTIONS.attribute10%TYPE
34 ,attribute11 AP_INVOICE_DISTRIBUTIONS.attribute11%TYPE
35 ,attribute12 AP_INVOICE_DISTRIBUTIONS.attribute12%TYPE
36 ,attribute13 AP_INVOICE_DISTRIBUTIONS.attribute13%TYPE
37 ,attribute14 AP_INVOICE_DISTRIBUTIONS.attribute14%TYPE
38 ,attribute15 AP_INVOICE_DISTRIBUTIONS.attribute15%TYPE
39 ,amount AP_INVOICE_DISTRIBUTIONS.amount%TYPE
40 ,base_amount AP_INVOICE_DISTRIBUTIONS.base_amount%TYPE
41 ,rounding_amt AP_INVOICE_DISTRIBUTIONS.rounding_amt%TYPE
42 ,type_1099 AP_INVOICE_DISTRIBUTIONS.type_1099%TYPE
43 ,income_tax_region AP_INVOICE_DISTRIBUTIONS.income_tax_region%TYPE
44 ,assets_tracking_flag AP_INVOICE_DISTRIBUTIONS.assets_tracking_flag%TYPE
45 ,asset_book_type_code AP_INVOICE_DISTRIBUTIONS.asset_book_type_code%TYPE
46 ,asset_category_id AP_INVOICE_DISTRIBUTIONS.asset_category_id%TYPE
47 ,awt_group_id AP_INVOICE_DISTRIBUTIONS.awt_group_id%TYPE
48 ,org_id AP_INVOICE_DISTRIBUTIONS.org_id%TYPE
49 ,set_of_books_id AP_INVOICE_DISTRIBUTIONS.set_of_books_id%TYPE
50 --ETAX: Invwkb
51 ,intended_use AP_INVOICE_DISTRIBUTIONS.intended_use%TYPE
52 --bugfix:4674194
53 ,global_attribute3 AP_INVOICE_DISTRIBUTIONS.global_attribute3%TYPE
54 --7022001
55 ,pay_awt_group_id AP_INVOICE_DISTRIBUTIONS.pay_awt_group_id%TYPE
56 --Bug9296445
57 ,reference_1 AP_INVOICE_DISTRIBUTIONS.reference_1%TYPE
58 ,reference_2 AP_INVOICE_DISTRIBUTIONS.reference_2%TYPE
59 --Bug10241241
60 ,stat_amount AP_INVOICE_DISTRIBUTIONS.stat_amount%TYPE
61 );
62
63
64 TYPE r_alloc_line IS RECORD
65 (
66 invoice_line_number AP_ALLOCATION_RULE_LINES.to_invoice_line_number%TYPE
67 ,percentage AP_ALLOCATION_RULE_LINES.percentage%TYPE
68 ,amount AP_ALLOCATION_RULE_LINES.amount%TYPE
69 ,sum_amount_dists AP_ALLOCATION_RULE_LINES.amount%TYPE);
70
71 TYPE dist_tab_type IS TABLE OF r_dist_info
72 INDEX BY BINARY_INTEGER;
73
74 TYPE alloc_line_tab_type IS TABLE of r_alloc_line
75 INDEX BY BINARY_INTEGER;
76
77
78 /*==========================================================================*/
79 /* */
80 /* This FUNCTION may be called from the insert_from_dist_set function of */
81 /* invoice lines, or from the import process. This FUNCTION takes a series */
82 /* of mandatory parameters. */
83 /* The mandatory parameters are: */
84 /* 1) X_vendor_id */
85 /* 2) X_invoice_date */
86 /* 3) X_invoice_lines_rec -- Lines record as defined in AP_INVOICES_PKG */
87 /* 4) X_dist_tab -- Variable to contain the plsql table of dists */
88 /* 5) X_dist_set_total_percent - 100 = Full Dist set, <> 100 = Skeleton */
89 /* 6) X_exchange_rate */
90 /* 7) X_exchange_rate_type */
91 /* 8) X_exchange_date */
92 /* 9) X_invoice_currency */
93 /* 10)X_base_currency */
94 /* 11)X_chart_of_accounts_id */
95 /* 12)X_Error_Code -- Variable RETURNing error code IF one is found */
96 /* Possible Codes are: */
97 /* 'INVALID ACCOUNT EXISTS' - Invalid account found */
98 /* 'CANNOT OVERLAY' - Cannot overlay with overlay info. */
99 /* 13)X_Debug_Info -- Variable RETURNing debug info for fatal error */
100 /* 14)X_Debug_Context -- Variable RETURNing context for fatal error */
101 /* 15)X_msg_application -- Variable to RETURN info from errors in PA Val */
102 /* 16)X_msg_data -- Variable to RETURN data from errors in PA Val */
103 /* 17)X_calling_sequence -- Calling sequence */
104 /* An optional parameter is: */
105 /* 1) X_line_source -- Should only be populated with the value 'IMPORT' */
106 /* -- IF calling from the Open Interface Import Program */
107 /* -- Should be null otherwise. */
108 /* */
109 /*==========================================================================*/
110 FUNCTION Generate_Dist_Tab_For_Dist_Set(
111 X_vendor_id IN AP_INVOICES.VENDOR_ID%TYPE,
112 X_invoice_date IN AP_INVOICES.INVOICE_DATE%TYPE,
113 X_invoice_lines_rec IN AP_INVOICES_PKG.r_invoice_line_rec,
114 X_line_source IN VARCHAR2,
115 X_dist_tab IN OUT NOCOPY AP_INVOICE_LINES_PKG.dist_tab_type,
116 X_dist_set_total_percent IN NUMBER,
117 X_exchange_rate IN AP_INVOICES.EXCHANGE_RATE%TYPE,
118 X_exchange_rate_type IN AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE,
119 X_exchange_date IN AP_INVOICES.EXCHANGE_DATE%TYPE,
120 X_invoice_currency IN AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE,
121 X_base_currency IN AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE,
122 X_chart_of_accounts_id IN GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE,
123 X_Error_Code OUT NOCOPY VARCHAR2,
124 X_Debug_Info OUT NOCOPY VARCHAR2,
125 X_Debug_Context OUT NOCOPY VARCHAR2,
126 X_msg_application OUT NOCOPY VARCHAR2,
127 X_msg_data OUT NOCOPY VARCHAR2,
128 X_calling_sequence IN VARCHAR2)
129 RETURN BOOLEAN;
130
131 /*==========================================================================*/
132 /* */
133 /* This FUNCTION may be called by any process requiring generation of */
134 /* an invoice line/distributions from a distribution set. */
135 /* It takes the following parameters: */
136 /* 1) X_invoice_id - ID of invoice requesting generation of line/dists */
137 /* 2) X_line_number - Line NUMBER IF line already exists, NULL otherwise */
138 /* - This process will error IF a line NUMBER is provided */
139 /* - and the line does not exist. */
140 /* 3) X_GL_Date - Accounting Date to use in insert of lines/dists. It is */
141 /* - assumed to have been validated to be in an open period. */
142 /* - You should pass this date IF generating distributions */
143 /* - for a line with a date in a closed period. This will */
144 /* - ensure the distributions are created in an open period. */
145 /* 4) X_period_name - Period corresponding to GL_Date parameter. */
146 /* 5) X_Skeleton_Allowed - Indicates whether a check must be performed to */
147 /* - ensure no skeleton dist set in use. */
148 /* 6) X_Generate_Dists - Indicates whether to Generate distributions as part*/
149 /* - of this function. */
150 /* 7) X_Generate_Permanent - Indicates whether to create distributions in */
151 /* - permanent or candidate mode. */
152 /* 8) X_Line_Source - 'HEADER DSET' IF called from the Invoice Workbench */
153 /* 'AUTO INVOICE CREATION' IF called from the */
154 /* AP_Recurring_Invoices_pkg.Ap_create_Recurring_Invoices*/
155 /* 9) X_Item_Description| Copied from the Recurring Invoices Template IF */
156 /* X_Manufacturer | the Invoice Lines/Dists are created via Recurring*/
157 /* X_Model_Number | Invoices */
158 /* 10) Out parameters to be used as stated below to handle errors. */
159 /* This FUNCTION will: */
160 /* 1) validate that IF Skeleton_Allowed is 'N', the given distribution set */
161 /* is not a skeleton distribution set. If skeleton, set error code to */
162 /* 'SKELETON_NOT_ALLOWED' and RETURN FALSE. */
163 /* 2) validate that distribution set is active. If inactive, set error code */
164 /* to 'DIST_SET_INACTIVE' and RETURN FALSE. */
165 /* 3) validate that all accounts in the distribution set are valid and/or if*/
166 /* overlay information provided, the overlayed */
167 /* accounts are valid. If an invalid account is found prior to overlay, */
168 /* set error code to 'INVALID_ACCOUNT_EXISTS' and RETURN FALSE. */
169 /* If overlay RETURNs an error set error code to 'CANNOT_OVERLAY' and */
170 /* RETURN FALSE. */
171 /* 4) IF no line already exists, create the line with the given parameters */
172 /* and information from the dist set. */
173 /* 5) call to create distributions */
174 /* 6) IF at any given time an error is encountered not covered in the error */
175 /* codes, populate debug_info with specific error and RETURN the debug */
176 /* info and the calling sequence as debug context with a FUNCTION output */
177 /* of FALSE. */
178 /* */
179 /*==========================================================================*/
180 FUNCTION Insert_From_Dist_Set(
181 X_invoice_id IN NUMBER,
182 X_line_number IN NUMBER DEFAULT NULL,
183 X_GL_Date IN DATE,
184 X_Period_Name IN VARCHAR2,
185 X_Skeleton_Allowed IN VARCHAR2 DEFAULT 'N',
186 X_Generate_Dists IN VARCHAR2 DEFAULT 'Y',
187 X_Generate_Permanent IN VARCHAR2 DEFAULT 'N',
188 X_Error_Code OUT NOCOPY VARCHAR2,
189 X_Debug_Info OUT NOCOPY VARCHAR2,
190 X_Debug_Context OUT NOCOPY VARCHAR2,
191 X_Msg_Application OUT NOCOPY VARCHAR2,
192 X_Msg_Data OUT NOCOPY VARCHAR2,
193 X_calling_sequence IN VARCHAR2) RETURN BOOLEAN;
194
195 /*==========================================================================*/
196 /* */
197 /* This FUNCTION RETURNs the maximum distribution line NUMBER for a given */
198 /* invoice and invoice line. If the line contains no distributions, this */
199 /* FUNCTION RETURNs 0. */
200 /* */
201 /*==========================================================================*/
202 FUNCTION Get_Max_Dist_Line_Num(
203 X_invoice_id IN NUMBER,
204 X_line_number IN NUMBER) RETURN NUMBER;
205
206 -- table to hols the dist ids that can be adjusted - bug 6892789
207 TYPE distribution_id_tab_type IS
208 TABLE OF AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID%TYPE
209 INDEX BY BINARY_INTEGER;
210
211 -- function modified to get the dists that can be adjusted - bug 6892789
212 FUNCTION round_base_amts(
213 X_INVOICE_ID IN NUMBER,
214 X_LINE_NUMBER IN NUMBER,
215 X_REPORTING_LEDGER_ID IN NUMBER DEFAULT NULL,
216 X_ROUND_DIST_ID_LIST OUT NOCOPY distribution_id_tab_type,
217 X_ROUNDED_AMT OUT NOCOPY NUMBER,
218 X_Debug_Info OUT NOCOPY VARCHAR2,
219 X_Debug_Context OUT NOCOPY VARCHAR2,
220 X_Calling_sequence IN VARCHAR2 )
221 RETURN BOOLEAN;
222
223 /*=============================================================================
224 | public PROCEDURE Discard_Inv_Line
225 |
226 | Discard or cancel the invoice line depending on calling mode. If error
227 | occurs, it return FALSE and error code will be populated. Otherwise,
228 | It return TRUE.
229 |
230 | Parameters
231 | P_line_rec - Invoice line record
232 | P_calling_mode - either from DISCARD, CANCEL or UNAPPLY_PREPAY
233 | p_inv_cancellable - 'Y' if invoice is canellable.
234 | P_last_updated_by
235 | P_last_update_login
236 | P_error_code - Error code indicates why it is not discardable
237 | P_calling_sequence - For debugging purpose
238 |
239 | PROGRAM FLOW
240 |
241 | 1. check if line is discardable
242 | 2. if line is discardable/cancellable and matched - reverse match
243 | 3. reset the encumberance flag, create account event
244 | 4. if there is an active distribution - reverse distribution
245 | 5. populate the out message and set the return value
246 |
247 | NOTES
248 |
249 | MODIFICATION HISTORY
250 | Date Author Description of Change
251 | 03/07/03 sfeng Created
252 |
253 *============================================================================*/
254
255 Function Discard_Inv_Line(
256 P_line_rec IN ap_invoice_lines%ROWTYPE,
257 P_calling_mode IN VARCHAR2,
258 p_inv_cancellable IN VARCHAR2 DEFAULT NULL,
259 P_last_updated_by IN NUMBER,
260 P_last_update_login IN NUMBER,
261 P_error_code OUT NOCOPY VARCHAR2,
262 P_token OUT NOCOPY VARCHAR2,
263 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN;
264
265 -- Bug 5114543
266 -- Allow discard of an item line with allocated charges. This utility is placed in the spec
267 -- so that it can be invoked from ap_invoice_lines_pkg and ap_invoice_distributions_pkg.
268
269 /*=============================================================================
270 | Public Function Reverse_Charge_distributions
271 |
272 | Reverse distributions for an invoice line. This will be called for
273 | a charge line (Freight, Miscellaneous). Returns false when an error
274 | occurs and error code will be populated. Otherwise, Returns False.
275 |
276 | Parameters
277 | P_inv_line_rec - Invoice line record.
278 | p_calling_mode - DISCARD, CANCEL
279 | x_error_code - For error handling
280 | x_debug_info - For debugging purposes
281 | p_calling_sequence - For debugging purposes
282 |
283 *============================================================================*/
284
285 FUNCTION Reverse_Charge_Distributions
286 (p_inv_line_rec IN AP_INVOICE_LINES_ALL%rowtype,
287 p_calling_mode IN VARCHAR2,
288 x_error_code OUT NOCOPY VARCHAR2,
289 x_debug_info OUT NOCOPY VARCHAR2,
290 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN;
291
292 END AP_INVOICE_LINES_PKG;
293