[Home] [Help]
PACKAGE BODY: APPS.AP_RECURRING_INVOICES_PKG
Source
1 PACKAGE BODY AP_RECURRING_INVOICES_PKG AS
2 /*$Header: aprecinb.pls 120.31.12020000.2 2012/10/05 11:58:49 rnimmaka ship $*/
3 --
4 -- Declare Local procedures
5 --
6 PROCEDURE ap_rec_inv_get_info(
7 P_invoice_date IN DATE,
8 P_invoice_amount IN NUMBER,
9 P_accounting_date IN DATE,
10 P_vendor_id IN NUMBER,
11 P_vendor_site_id IN NUMBER,
12 P_set_of_books_id IN NUMBER,
13 P_tax_id IN NUMBER,
14 P_invoice_currency_code IN VARCHAR2,
15 P_po_shipment_id IN NUMBER,
16 P_terms_date OUT NOCOPY DATE,
17 P_gl_period_name OUT NOCOPY VARCHAR2,
18 P_tax_amount OUT NOCOPY NUMBER,
19 P_type_1099 OUT NOCOPY VARCHAR2,
20 P_income_tax_region OUT NOCOPY VARCHAR2,
21 P_payment_priority OUT NOCOPY NUMBER,
22 P_min_unit OUT NOCOPY NUMBER,
23 P_precision OUT NOCOPY NUMBER,
24 P_overbilled OUT NOCOPY VARCHAR2,
25 P_transfer_desc_flex_flag OUT NOCOPY VARCHAR2,
26 P_approval_workflow_flag OUT NOCOPY VARCHAR2,
27 P_inventory_org_id OUT NOCOPY NUMBER,
28 P_asset_bt_code OUT NOCOPY VARCHAR2,
29 P_Price OUT NOCOPY NUMBER,
30 P_quantity OUT NOCOPY NUMBER, /* bug 5228301 */
31 P_retained_amount OUT NOCOPY NUMBER, /* bug 5228301 */
32 P_match_type OUT NOCOPY VARCHAR2, /* bug 5228301 */
33 P_Description OUT NOCOPY VARCHAR2,
34 P_unit_meas_lookup_code OUT NOCOPY VARCHAR2,
35 P_ship_to_location_id OUT NOCOPY NUMBER,
36 P_calling_sequence IN VARCHAR2);
37
38 PROCEDURE ap_rec_inv_insert_ap_batches(
39 P_batch_name IN VARCHAR2,
40 P_batch_control_flag IN VARCHAR2,
41 P_invoice_currency_code IN VARCHAR2,
42 P_payment_currency_code IN VARCHAR2,
43 P_last_update_date IN DATE,
44 P_last_updated_by IN NUMBER,
45 P_batch_id OUT NOCOPY NUMBER,
46 P_calling_sequence IN VARCHAR2,
47 P_Org_Id IN NUMBER);
48
49 PROCEDURE ap_rec_inv_insert_ap_invoices(
50 P_batch_id IN NUMBER,
51 P_last_update_date IN DATE,
52 P_last_updated_by IN NUMBER,
53 P_invoice_currency_code IN VARCHAR2,
54 P_payment_currency_code IN VARCHAR2,
55 P_base_currency_code IN VARCHAR2,
56 P_invoice_id IN OUT NOCOPY NUMBER,
57 P_invoice_num IN VARCHAR2,
58 P_invoice_amount IN NUMBER,
59 P_vendor_id IN NUMBER,
60 P_vendor_site_id IN NUMBER,
61 P_invoice_date IN DATE,
62 P_description IN VARCHAR2,
63 P_tax_name IN VARCHAR2,
64 P_tax_amount IN NUMBER,
65 P_terms_id IN NUMBER,
66 P_pay_group_lookup_code IN VARCHAR2,
67 P_set_of_books_id IN NUMBER,
68 P_accts_pay_ccid IN NUMBER,
69 P_payment_cross_rate IN NUMBER,
70 P_exchange_date IN DATE,
71 P_exchange_rate_type IN VARCHAR2,
72 P_exchange_rate IN NUMBER,
73 P_invoice_base_amount IN NUMBER,
74 P_recurring_payment_id IN NUMBER,
75 P_terms_date IN DATE,
76 P_doc_sequence_id IN NUMBER,
77 P_doc_sequence_value IN NUMBER,
78 P_doc_category_code IN VARCHAR2,
79 P_exclusive_payment_flag IN VARCHAR2,
80 P_awt_group_id IN NUMBER,
81 P_pay_awt_group_id IN NUMBER,--bug6639866
82 P_distribution_set_id IN NUMBER,
83 P_accounting_date IN DATE,
84 -- P_ussgl_txn_code IN VARCHAR2, - Bug 4277744
85 P_attribute1 IN VARCHAR2,
86 P_attribute2 IN VARCHAR2,
87 P_attribute3 IN VARCHAR2,
88 P_attribute4 IN VARCHAR2,
89 P_attribute5 IN VARCHAR2,
90 P_attribute6 IN VARCHAR2,
91 P_attribute7 IN VARCHAR2,
92 P_attribute8 IN VARCHAR2,
93 P_attribute9 IN VARCHAR2,
94 P_attribute10 IN VARCHAR2,
95 P_attribute11 IN VARCHAR2,
96 P_attribute12 IN VARCHAR2,
97 P_attribute13 IN VARCHAR2,
98 P_attribute14 IN VARCHAR2,
99 P_attribute15 IN VARCHAR2,
100 P_attribute_category IN VARCHAR2,
101 P_calling_sequence IN VARCHAR2,
102 P_Org_Id IN NUMBER,
103 P_Requester_Id IN NUMBER,
104 P_Tax_Control_Amount IN NUMBER,
105 P_Trx_Business_Category IN VARCHAR2,
106 P_User_Defined_Fisc_Class IN VARCHAR2,
107 P_Taxation_Country IN VARCHAR2,
108 P_Legal_Entity_Id IN NUMBER,
109 p_PAYMENT_METHOD_CODE in varchar2,
110 p_PAYMENT_REASON_CODE in varchar2,
111 p_remittance_message1 in varchar2,
112 p_remittance_message2 in varchar2,
113 p_remittance_message3 in varchar2,
114 p_bank_charge_bearer in varchar2,
115 p_settlement_priority in varchar2,
116 p_payment_reason_comments in varchar2,
117 p_delivery_channel_code in varchar2,
118 p_external_bank_account_id in number,
119 p_party_id in number,
120 p_party_site_id in number,
121 /* bug 4931755 */
122 p_disc_is_inv_less_tax_flag in varchar2,
123 p_exclude_freight_from_disc in varchar2,
124 P_REMIT_TO_SUPPLIER_NAME in VARCHAR2,
125 P_REMIT_TO_SUPPLIER_ID in NUMBER,
126 P_REMIT_TO_SUPPLIER_SITE in VARCHAR2,
127 P_REMIT_TO_SUPPLIER_SITE_ID in NUMBER,
128 P_RELATIONSHIP_ID in NUMBER);
129
130 Procedure Insert_Invoice_Line (
131 P_Invoice_Id IN NUMBER,
132 P_Invoice_line_number OUT NOCOPY NUMBER,
133 p_Invoice_Date IN DATE,
134 p_Line_Type_Lookup_Code IN VARCHAR2,
135 P_description IN VARCHAR2,
136 P_Po_Line_Location_Id IN NUMBER DEFAULT NULL,
137 P_Amount IN NUMBER,
138 P_Quantity_Invoiced IN NUMBER DEFAULT NULL,
139 P_Unit_Price IN NUMBER DEFAULT NULL,
140 P_set_of_books_id IN NUMBER,
141 P_exchange_rate IN NUMBER,
142 P_base_currency_code IN VARCHAR2,
143 P_accounting_date IN DATE,
144 P_awt_group_id IN NUMBER,
145 P_pay_awt_group_id IN NUMBER,--bug6639866
146 P_gl_period_name IN VARCHAR2,
147 P_income_tax_region IN VARCHAR2,
148 P_transfer_flag IN VARCHAR2,
149 P_approval_workflow_flag IN VARCHAR2,
150 P_Inventory_org_id IN NUMBER,
151 P_asset_bt_code IN VARCHAR2,
152 P_Tax_Control_Amount IN NUMBER DEFAULT NULL,
153 P_Primary_Intended_Use IN VARCHAR2 DEFAULT NULL,
154 P_Product_Fisc_Classification IN VARCHAR2 DEFAULT NULL,
155 P_User_Defined_Fisc_Class IN VARCHAR2 DEFAULT NULL,
156 P_Trx_Business_Category IN VARCHAR2 DEFAULT NULL,
157 P_retained_amount IN NUMBER DEFAULT NULL, /*bug 5228301 */
158 P_match_type IN VARCHAR2, /* bug 5228301 */
159 P_Tax_Classification_Code IN VARCHAR2,
160 P_PRODUCT_TYPE IN VARCHAR2, --Bug#8640313
161 P_PRODUCT_CATEGORY IN VARCHAR2, --Bug#8640313
162 P_Calling_Sequence IN VARCHAR2);
163
164
165 Procedure Insert_Invoice_Line_Dset(
166 P_invoice_id IN NUMBER,
167 P_line_amount IN NUMBER,
168 P_description IN VARCHAR2,
169 P_distribution_set_id IN NUMBER,
170 P_requester_id IN NUMBER,
171 P_set_of_books_id IN NUMBER,
172 P_exchange_rate IN NUMBER,
173 P_base_currency_code IN VARCHAR2,
174 P_accounting_date IN DATE,
175 P_gl_period_name IN VARCHAR2,
176 P_org_id IN NUMBER,
177 P_item_description IN VARCHAR2,
178 P_manufacturer IN VARCHAR2,
179 P_model_number IN VARCHAR2,
180 P_approval_workflow_flag IN VARCHAR2,
181 -- P_ussgl_txn_code IN VARCHAR2, - Bug 4277744
182 P_income_tax_region IN VARCHAR2,
183 P_type_1099 IN VARCHAR2,
184 P_asset_bt_code IN VARCHAR2,
185 P_awt_group_id IN NUMBER,
186 P_pay_awt_group_id IN NUMBER,--bug6639866
187 P_ship_to_location_id IN NUMBER,
188 P_primary_intended_use IN VARCHAR2,
189 P_product_fisc_classification IN VARCHAR2,
190 P_trx_business_category IN VARCHAR2,
191 P_user_defined_fisc_class IN VARCHAR2,
192 P_tax_classification_code IN VARCHAR2,
193 P_PRODUCT_TYPE IN VARCHAR2, --Bug#8640313
194 P_PRODUCT_CATEGORY IN VARCHAR2, --Bug#8640313
195 P_calling_sequence IN VARCHAR2
196 );
197
198 /*========================================================================
199 * Main Procedure: Create Recurring Invoices :ap_create_recurring_invoices
200 * Step 1. Call ap_rec_inv_get_info to get some required fields
201 * Step 2. Create ap_batches id it's a new batch
202 * Step 3. Create ap_invoices
203 * Step 4.1 Call ap_match to create ITEM invoice lines
204 * Step 4.2 Call AP_INVOICES_PKG.insert_children insert all the corrsponding
205 ap_invoice_lines
206 ap_payment_schedules, ap_holds
207
208 *========================================================================*/
209 PROCEDURE ap_create_recurring_invoices(
210 P_batch_name IN VARCHAR2 DEFAULT NULL,
211 P_last_update_date IN DATE,
212 P_last_updated_by IN NUMBER,
213 P_invoice_currency_code IN VARCHAR2,
214 P_payment_currency_code IN VARCHAR2,
215 P_invoice_num IN VARCHAR2,
216 P_invoice_amount IN OUT NOCOPY NUMBER,
217 P_vendor_id IN NUMBER,
218 P_vendor_site_id IN NUMBER Default NULL,
219 P_invoice_date IN DATE Default NULL,
220 P_description IN VARCHAR2 DEFAULT NULL,
221 P_terms_id IN NUMBER Default NULL,
222 P_pay_group_lookup_code IN VARCHAR2 DEFAULT NULL,
223 P_set_of_books_id IN NUMBER,
224 P_accts_pay_ccid IN NUMBER Default NULL,
225 P_payment_cross_rate IN NUMBER,
226 P_exchange_date IN DATE Default NULL,
227 P_exchange_rate_type IN VARCHAR2 DEFAULT NULL,
228 P_exchange_rate IN NUMBER Default NULL,
229 P_invoice_base_amount IN NUMBER Default NULL,
230 P_base_currency_code IN VARCHAR2 DEFAULT NULL,
231 P_recurring_payment_id IN NUMBER Default NULL,
232 P_doc_sequence_id IN NUMBER Default NULL,
233 P_doc_sequence_value IN NUMBER Default NULL,
234 P_doc_category_code IN VARCHAR2 DEFAULT NULL,
235 P_exclusive_payment_flag IN VARCHAR2 DEFAULT NULL,
236 P_awt_group_id IN NUMBER Default NULL,
237 P_pay_awt_group_id IN NUMBER Default NULL,--bug6639866
238 P_distribution_set_id IN NUMBER Default NULL,
239 P_accounting_date IN DATE,
240 P_po_shipment_id IN NUMBER Default NULL,
241 P_batch_control_flag IN VARCHAR2 DEFAULT NULL,
242 P_multi_currency_flag IN VARCHAR2 Default NULL,
243 P_po_match_flag IN VARCHAR2 Default NULL,
244 -- Removed for bug 4277744
245 -- P_ussgl_txn_code IN VARCHAR2 Default NULL,
246 P_attribute1 IN VARCHAR2 DEFAULT NULL,
247 P_attribute2 IN VARCHAR2 DEFAULT NULL,
248 P_attribute3 IN VARCHAR2 Default NULL,
249 P_attribute4 IN VARCHAR2 DEFAULT NULL,
250 P_attribute5 IN VARCHAR2 DEFAULT NULL,
251 P_attribute6 IN VARCHAR2 DEFAULT NULL,
252 P_attribute7 IN VARCHAR2 DEFAULT NULL,
253 P_attribute8 IN VARCHAR2 DEFAULT NULL,
254 P_attribute9 IN VARCHAR2 DEFAULT NULL,
255 P_attribute10 IN VARCHAR2 DEFAULT NULL,
256 P_attribute11 IN VARCHAR2 DEFAULT NULL,
257 P_attribute12 IN VARCHAR2 DEFAULT NULL,
258 P_attribute13 IN VARCHAR2 DEFAULT NULL,
259 P_attribute14 IN VARCHAR2 DEFAULT NULL,
260 P_attribute15 IN VARCHAR2 DEFAULT NULL,
261 P_attribute_category IN VARCHAR2 DEFAULT NULL,
262 P_calling_sequence IN VARCHAR2 DEFAULT NULL,
263 P_invoice_id OUT NOCOPY NUMBER,
264 P_Org_Id IN NUMBER Default mo_global.get_current_org_id,
265 P_Requester_Id IN NUMBER Default NULL,
266 P_Po_Release_Id IN NUMBER DEFAULT NULL,
267 P_Item_Description IN VARCHAR2 DEFAULT NULL,
268 P_Manufacturer IN VARCHAR2 DEFAULT NULL,
269 P_Model_Number IN VARCHAR2 DEFAULT NULL,
270 P_Tax_Control_Amount IN NUMBER DEFAULT NULL,
271 P_Trx_Business_Category IN VARCHAR2 DEFAULT NULL,
272 P_User_Defined_Fisc_Class IN VARCHAR2 DEFAULT NULL,
273 P_Taxation_Country IN VARCHAR2 DEFAULT NULL,
274 P_Primary_Intended_Use IN VARCHAR2 DEFAULT NULL,
275 P_Product_Fisc_Classification IN VARCHAR2 DEFAULT NULL,
276 P_Tax_Amount IN NUMBER DEFAULT NULL,
277 P_Tax_Amt_Exclusive IN VARCHAR2 DEFAULT NULL,
278 P_Legal_Entity_Id IN NUMBER DEFAULT NULL,
279 p_PAYMENT_METHOD_CODE in varchar2 default null,
280 p_PAYMENT_REASON_CODE in varchar2 default null,
281 p_remittance_message1 in varchar2 default null,
282 p_remittance_message2 in varchar2 default null,
283 p_remittance_message3 in varchar2 default null,
284 p_bank_charge_bearer in varchar2 default null,
285 p_settlement_priority in varchar2 default null,
286 p_payment_reason_comments in varchar2 default null,
287 p_delivery_channel_code in varchar2 default null,
288 p_external_bank_account_id in number default null,
289 p_party_id in number default null,
290 p_party_site_id in number default null,
291 /* bug 4931755. Exclude Tax From Discount */
292 p_disc_is_inv_less_tax_flag in varchar2 default null,
293 p_exclude_freight_from_disc in varchar2 default null,
294 p_tax_classification_code in varchar2 default null,
295 P_REMIT_TO_SUPPLIER_NAME VARCHAR2 DEFAULT NULL,
296 P_REMIT_TO_SUPPLIER_ID NUMBER DEFAULT NULL,
297 P_REMIT_TO_SUPPLIER_SITE VARCHAR2 DEFAULT NULL,
298 P_REMIT_TO_SUPPLIER_SITE_ID NUMBER DEFAULT NULL,
299 P_RELATIONSHIP_ID NUMBER DEFAULT NULL,
300 P_PRODUCT_TYPE VARCHAR2 DEFAULT NULL, --Bug#8640313
301 P_PRODUCT_CATEGORY VARCHAR2 DEFAULT NULL --Bug#8640313
302 ) IS
303
304 current_calling_sequence VARCHAR2(2000);
305 debug_info VARCHAR2(100);
306 C_terms_date DATE;
307 C_gl_period_name VARCHAR2(100);
308 C_tax_name AP_TAX_CODES.NAME%TYPE;
309 C_tax_id AP_TAX_CODES.TAX_ID%TYPE;
310 C_tax_type AP_TAX_CODES.TAX_TYPE%TYPE;
311 C_tax_description AP_TAX_CODES.DESCRIPTION%TYPE;
312 C_allow_tax_override GL_TAX_OPTION_ACCOUNTS.ALLOW_TAX_CODE_OVERRIDE_FLAG%TYPE;
313 C_tax_amount NUMBER;
314 C_type_1099 VARCHAR2(10);
315 C_income_tax_region VARCHAR2(10);
316 C_payment_priority NUMBER;
317 C_min_unit NUMBER;
318 C_precision NUMBER;
319 C_batch_id NUMBER;
320 C_invoice_id NUMBER;
321 C_create_item_dist_flag VARCHAR2(2) := 'Y';
322 C_Hold_count NUMBER;
323 C_Line_count NUMBER;
324 C_Line_Total NUMBER;
325 C_Distribution_Set_ID NUMBER;
326 C_quantity_outstanding NUMBER;
327 C_overbilled VARCHAR2(1);
328 C_Quantity NUMBER;
329 C_Transfer_desc_flex_Flag ap_system_parameters.transfer_desc_flex_flag%TYPE;
330 C_Approval_Workflow_Flag ap_system_parameters.approval_workflow_flag%TYPE;
331 C_Invoice_line_number ap_invoice_lines_all.line_number%TYPE;
332 C_Price NUMBER;
333 C_Description ap_lookup_codes.description%TYPE;
334 C_unit_meas_lookup_code po_line_locations_all.unit_meas_lookup_code%TYPE;
335 C_inventory_org_id financials_system_parameters.inventory_organization_id%TYPE;
336 C_asset_bt_code AP_INVOICE_LINES_ALL.ASSET_BOOK_TYPE_CODE%TYPE;
337 C_dist_tab AP_MATCHING_PKG.dist_tab_type;
338 C_ship_to_location_id PO_VENDOR_SITES.ship_to_location_id%TYPE;
339 C_retained_amount Number; /*bug 5228301 */
340 C_match_type AP_INVOICE_LINES_ALL.Match_Type%TYPE;
341 C_sched_holds_count NUMBER; --bug 5452979
342
343 -- eTax Uptake
344 l_inv_hdr_amount NUMBER;
345 l_inv_hdr_base_amount NUMBER; -- BUG 14621772
346 l_line_amount NUMBER;
347 l_base_line_amount NUMBER;
348 l_inv_line_tax_atts ap_invoice_lines%rowtype;
349
350 l_invoice_line_number AP_INVOICE_LINES.LINE_NUMBER%TYPE;
351
352 BEGIN
353 -- Update the calling sequence
354 --
355 current_calling_sequence :=
356 'AP_RECURRING_INVOICES_PKG.ap_create_recurring_invoices<-'
357 ||P_calling_sequence;
358
359 C_dist_tab.DELETE;
360
361 /*---------------------------------------------------------------------------
362 * Step 1:
363 * Call ap_rec_inv_get_info to get some parameters
364 *--------------------------------------------------------------------------*/
365 debug_info := 'Call ap_rec_inv_get_info';
366 AP_RECURRING_INVOICES_PKG.ap_rec_inv_get_info(
367 P_invoice_date,
368 P_invoice_amount,
369 P_accounting_date,
370 P_vendor_id,
371 P_vendor_site_id,
372 P_set_of_books_id,
373 c_tax_id,
374 P_invoice_currency_code,
375 P_po_shipment_id,
376 C_terms_date,
377 C_gl_period_name,
378 C_tax_amount,
379 C_type_1099,
380 C_income_tax_region,
381 C_payment_priority,
382 C_min_unit,
383 C_precision,
384 C_overbilled,
385 C_Transfer_desc_flex_Flag,
386 C_Approval_Workflow_Flag,
387 C_inventory_org_id,
388 C_asset_bt_code,
389 C_Price,
390 C_Quantity, /* bug 5228301 */
391 C_Retained_Amount, /* bug 5228301 */
392 C_Match_Type, /* bug 5228301 */
393 C_Description,
394 C_unit_meas_lookup_code,
395 C_Ship_to_location_id,
396 Current_calling_sequence);
397
398
399 /*---------------------------------------------------------------------------
400 * Step 2: Insert New AP_BATCHES IF it's a new batch
401 * Call ap_rec_inv_insert_ap_batches: Insert AP_BATCHES
402 *--------------------------------------------------------------------------*/
403 debug_info := 'ap_rec_inv_insert_ap_batches';
404 AP_RECURRING_INVOICES_PKG.ap_rec_inv_insert_ap_batches(
405 P_batch_name,
406 nvl(P_batch_control_flag,'N'),
407 P_invoice_currency_code,
408 P_payment_currency_code,
409 P_last_update_date,
410 P_last_updated_by,
411 C_batch_id,
412 Current_calling_sequence,
413 P_Org_Id);
414
415
416 /*---------------------------------------------------------------------------
417 * Step 3: Insert New AP_INVOICES
418 * Call ap_rec_inv_insert_ap_invoices: Insert AP_INVOICES
419 *--------------------------------------------------------------------------*/
420
421 IF (NVL(P_po_match_flag,'N') = 'Y') THEN
422 IF P_Tax_Amount IS NOT NULL THEN
423
424 l_inv_hdr_amount := P_Invoice_Amount + P_Tax_Amount;
425
426 --Added IF ELSE code for bug#14621772
427 IF(P_base_currency_code <> P_invoice_currency_code)THEN
428 l_inv_hdr_base_amount :=
429 AP_UTILITIES_PKG.Ap_Round_Currency(
430 NVL(l_inv_hdr_amount, 0)* P_EXCHANGE_RATE,
431 P_BASE_CURRENCY_CODE);
432 ELSE
433 l_inv_hdr_base_amount := l_inv_hdr_amount;
434
435 END IF;
436 --End of bug#14621772
437
438 END IF;
439 ELSE
440 l_inv_hdr_amount := NULL;
441 l_inv_hdr_base_amount := NULL; --Bug 14621772
442 END IF;
443
444 debug_info := 'ap_rec_inv_insert_ap_invoices';
445 AP_RECURRING_INVOICES_PKG.ap_rec_inv_insert_ap_invoices(
446 C_batch_id,
447 P_last_update_date,
448 P_last_updated_by,
449 P_invoice_currency_code,
450 P_payment_currency_code,
451 P_base_currency_code,
452 C_invoice_id,
453 P_invoice_num,
454 nvl(l_inv_hdr_amount, P_invoice_amount),
455 P_vendor_id,
456 P_vendor_site_id,
457 P_invoice_date,
458 P_description,
459 c_tax_name,
460 C_tax_amount,
461 P_terms_id,
462 P_pay_group_lookup_code,
463 P_set_of_books_id,
464 P_accts_pay_ccid,
465 P_payment_cross_rate,
466 P_exchange_date,
467 P_exchange_rate_type,
468 P_exchange_rate,
469 nvl(l_inv_hdr_base_amount, P_invoice_base_amount), -- Bug 14621772
470 P_recurring_payment_id,
471 C_terms_date,
472 P_doc_sequence_id,
473 P_doc_sequence_value,
474 P_doc_category_code,
475 P_exclusive_payment_flag,
476 P_awt_group_id,
477 P_pay_awt_group_id,--bug6639866
478 P_distribution_set_id,
479 P_accounting_date,
480 -- P_ussgl_txn_code, - Bug 4277744
481 P_attribute1,
482 P_attribute2,
483 P_attribute3,
484 P_attribute4,
485 P_attribute5,
486 P_attribute6,
487 P_attribute7,
488 P_attribute8,
489 P_attribute9,
490 P_attribute10,
491 P_attribute11,
492 P_attribute12,
493 P_attribute13,
494 P_attribute14,
495 P_attribute15,
496 P_attribute_category,
497 Current_calling_sequence,
498 P_Org_Id,
499 P_Requester_Id,
500 P_Tax_Control_Amount,
501 P_Trx_Business_Category,
502 P_User_Defined_Fisc_Class,
503 P_Taxation_Country,
504 P_Legal_Entity_Id,
505 p_PAYMENT_METHOD_CODE,
506 p_PAYMENT_REASON_CODE,
507 p_remittance_message1,
508 p_remittance_message2,
509 p_remittance_message3,
510 p_bank_charge_bearer,
511 p_settlement_priority,
512 p_payment_reason_comments,
513 p_delivery_channel_code,
514 p_external_bank_account_id,
515 p_party_id,
516 p_party_site_id,
517 /* bug 4931755 */
518 p_disc_is_inv_less_tax_flag,
519 p_exclude_freight_from_disc,
520 P_REMIT_TO_SUPPLIER_NAME,
521 P_REMIT_TO_SUPPLIER_ID,
522 P_REMIT_TO_SUPPLIER_SITE,
523 P_REMIT_TO_SUPPLIER_SITE_ID,
524 P_RELATIONSHIP_ID);
525
526 P_invoice_id := C_Invoice_Id;
527 C_Distribution_Set_ID := P_Distribution_Set_ID;
528
529 /*---------------------------------------------------------------------------
530 * Step 4: IF PO Matching
531 * * Call Insert_Invoice_line
532 * * Call AP_MATCHING_PKG.Base_Credit_PO_Match
533 *--------------------------------------------------------------------------*/
534
535 IF (NVL(P_po_match_flag,'N') = 'Y' AND P_invoice_amount >= 0) then
536
537 -------------------------------------------------------------
538 --Step 4.i : Insert the Invoice Line
539 -------------------------------------------------------------
540 debug_info := 'Create Invoice Line';
541 AP_RECURRING_INVOICES_PKG.Insert_Invoice_Line(
542 P_Invoice_ID => C_invoice_id,
543 P_Invoice_line_Number => C_Invoice_Line_Number,
544 P_Invoice_Date => P_Invoice_date,
545 P_Line_Type_Lookup_Code => 'ITEM',
546 P_description => C_description,
547 P_Po_Line_Location_id => P_po_shipment_id,
548 P_Amount => P_invoice_amount,
549 P_Quantity_Invoiced => C_quantity,
550 P_Unit_Price => C_price,
551 P_Set_of_Books_Id => P_set_of_books_Id,
552 P_exchange_rate => P_exchange_rate,
553 P_Base_currency_code => P_Base_currency_code,
554 P_Accounting_date => P_Accounting_date,
555 P_Awt_Group_Id => P_Awt_Group_Id,
556 P_Pay_Awt_Group_Id => P_Pay_Awt_Group_Id,--bug6639866
557 P_gl_period_name => C_gl_period_name,
558 P_income_tax_region => C_Income_tax_region,
559 P_Transfer_Flag => C_Transfer_desc_flex_Flag,
560 P_Approval_Workflow_Flag => C_Approval_Workflow_Flag,
561 P_inventory_org_id => C_inventory_org_id,
562 P_asset_bt_code => C_asset_bt_code,
563 P_Tax_Control_Amount => P_Tax_Control_Amount,
564 P_Primary_Intended_Use => P_Primary_Intended_Use,
565 P_Product_Fisc_Classification => P_Product_Fisc_Classification,
566 P_User_Defined_Fisc_Class => P_User_Defined_Fisc_Class,
567 P_Trx_Business_Category => P_Trx_Business_Category,
568 P_Retained_Amount => C_Retained_Amount, /* bug 5228301 */
569 P_Match_Type => C_Match_Type, /* bug 5228301 */
570 P_Tax_Classification_Code => P_Tax_Classification_Code,
571 P_PRODUCT_TYPE => P_PRODUCT_TYPE, --Bug#8640313
572 P_PRODUCT_CATEGORY => P_PRODUCT_CATEGORY, --Bug#8640313
573 P_Calling_Sequence => current_calling_sequence);
574
575 -------------------------------------------------------
576 -- Step 4.iv: Call PO_MATCH to create ITEM dist lines
577 -------------------------------------------------------
578 debug_info := 'Calling PO Matching to create Item Dist Lines';
579 /* bug 5228301 */
580 AP_MATCHING_UTILS_PKG.Match_Invoice_Line(
581 P_Invoice_Id => C_invoice_id,
582 P_Invoice_Line_Number => C_invoice_line_number,
583 P_Overbill_Flag => C_overbilled,
584 P_Calling_Sequence => Current_calling_sequence);
585 /*
586 AP_MATCHING_PKG.Base_Credit_PO_Match(
587 X_match_mode => 'STD-PS',
588 X_invoice_id => C_invoice_id,
589 X_invoice_line_number => C_invoice_line_number,
590 X_Po_Line_Location_id => P_po_shipment_id,
591 X_Dist_Tab => C_dist_tab,
592 X_amount => P_invoice_amount, --eTax - nvl(c_tax_amount,0),
593 X_quantity => C_quantity,
594 X_unit_price => C_price,
595 X_uom_lookup_code => C_unit_meas_lookup_code,
596 X_final_match_flag => 'N',
597 X_overbill_flag => C_overbilled,
598 X_freight_amount => NULL,
599 X_freight_description => NULL,
600 X_misc_amount => NULL,
601 X_misc_description => NULL,
602 X_calling_sequence => Current_calling_sequence);
603 */
604
605 --
606 -- Since ap_match has created the item dist lines. We need
607 -- to prevent insert_children to create item dist lines.
608 --
609 -- If we set C_Distribution_Set_ID is NULL, insert_children
610 -- won't create the ITEM lines.
611 --
612 C_Distribution_Set_ID := '';
613 END IF;
614 -------------------------------------------------------------------
615 -- Step 5: Call AP_INVOICES_PKG.insert_children()
616 -- Inserts child records into AP_HOLDS,
617 -- AP_PAYMENT_SCHEDULES
618 -- If C_Disttribution_set_ID is not NULL
619 -- Insert AP_INVOICE_LINES
620 -- * If PO_match has been executed. Don't create ITEM lines
621 -------------------------------------------------------------------
622
623 IF C_Distribution_Set_ID IS NOT NULL THEN
624
625 IF P_Tax_Amount IS NOT NULL AND
626 P_Tax_Amt_Exclusive = 'Y' THEN
627 l_line_amount := P_Invoice_Amount - P_Tax_Amount;
628 ELSE
629 l_line_amount := P_Invoice_Amount;
630 END IF;
631
632 AP_RECURRING_INVOICES_PKG.Insert_Invoice_Line_Dset(
633 P_invoice_id => p_invoice_id,
634 P_line_amount => l_line_amount,
635 P_description => c_description,
636 P_distribution_set_id => c_distribution_set_id,
637 P_requester_id => p_requester_id,
638 P_set_of_books_id => p_set_of_books_id,
639 P_exchange_rate => p_exchange_rate,
640 P_base_currency_code => p_base_currency_code,
641 P_accounting_date => p_accounting_date,
642 P_gl_period_name => c_gl_period_name,
643 P_org_id => p_org_id,
644 P_item_description => p_item_description,
645 P_manufacturer => p_manufacturer,
646 P_model_number => p_model_number,
647 P_approval_workflow_flag=> c_approval_workflow_flag,
648 -- P_ussgl_txn_code => p_ussgl_txn_code, - Bug 4277744
649 P_income_tax_region => c_income_tax_region,
650 P_type_1099 => c_type_1099,
651 P_asset_bt_code => c_asset_bt_code,
652 P_awt_group_id => p_awt_group_id,
653 P_pay_awt_group_id => p_pay_awt_group_id,--bug6639866
654 P_ship_to_location_id => c_ship_to_location_id,
655 P_primary_intended_use => p_primary_intended_use,
656 P_product_fisc_classification => p_product_fisc_classification,
657 P_trx_business_category => p_trx_business_category,
658 P_user_defined_fisc_class => p_user_defined_fisc_class,
659 P_tax_classification_code => p_tax_classification_code,
660 P_PRODUCT_TYPE => P_PRODUCT_TYPE, --Bug#8640313
661 P_PRODUCT_CATEGORY => P_PRODUCT_CATEGORY, --Bug#8640313
662 P_calling_sequence => current_calling_sequence);
663
664
665 END IF; /* if c_distribution_set is not null */
666
667
668
669
670 debug_info := 'Insert Holds, Payment Schedules ';
671 AP_INVOICES_PKG.insert_children (
672 X_invoice_id => C_invoice_id,
673 X_Payment_Priority => C_Payment_Priority,
674 X_Hold_count => C_Hold_count,
675 X_Line_count => C_Line_count,
676 X_Line_Total => C_Line_Total,
677 X_calling_sequence => Current_calling_sequence,
678 X_Sched_Hold_count => C_sched_holds_count ); --bug 5452979
679
680 EXCEPTION
681 WHEN OTHERS then
682 IF ((SQLCODE <> -20001) OR (SQLCODE <> -20002) ) then
683 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
684 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
685 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
686 FND_MESSAGE.SET_TOKEN('PARAMETERS',
687 'Invoice_date = '||TO_CHAR(P_invoice_date)
688 ||' Accounting_date = '||TO_CHAR(P_accounting_date)
689 ||' Currency_code = '||P_invoice_currency_code
690 ||' P_recurring_payment_id = '||TO_CHAR(P_recurring_payment_id)
691 ||' P_exclusive_payment_flag = '||P_exclusive_payment_flag
692 ||' P_awt_group_id = '||TO_CHAR(P_awt_group_id)
693 ||' P_pay_awt_group_id = '||TO_CHAR(P_pay_awt_group_id) --bug6639866
694 ||' Invoice_date = '||TO_CHAR(P_invoice_date)
695 ||' Vendor_id = '||TO_CHAR(P_vendor_id)
696 ||' Vendor_site_id = '||TO_CHAR(P_vendor_site_id)
697 ||' Invoice_num = '||P_invoice_num
698 ||' Invoice Amount = '||TO_CHAR(P_invoice_amount)
699 ||' P_doc_category_code = '||P_doc_category_code
700 ||' Doc_sequence_value = '||TO_CHAR(P_doc_sequence_value)
701 ||' Doc_sequence_id = '||TO_CHAR(P_doc_sequence_id)
702 ||' Pay_group_lookup_code = '||P_pay_group_lookup_code
703 ||' Invoice_currency_code = '||P_invoice_currency_code
704 ||' Payment_currency_code = '||P_payment_currency_code
705 ||' Terms_id = '||TO_CHAR(P_terms_id)
706 ||' Payment_cross_rate = '||TO_CHAR(P_payment_cross_rate)
707 ||' Exchange Rate = '||TO_CHAR(P_exchange_rate)
708 ||' Exchange Rate Type = '||P_exchange_rate_type
709 ||' Exchange Date = '||TO_CHAR(P_exchange_date)
710 ||' Set 0f books id = '||TO_CHAR(P_set_of_books_id)
711 ||' Last_updated_by = '||TO_CHAR(P_last_updated_by)
712 ||' Last_updated_date = '||TO_CHAR(P_last_update_date)
713 ||' P_po_match_flag = '||P_po_match_flag
714 ||' P_batch_control_flag = '||P_batch_control_flag);
715 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
716 END IF;
717
718 APP_EXCEPTION.RAISE_EXCEPTION;
719
720 END ap_create_recurring_invoices;
721
722
723
724 /*==========================================================================
725 Private Procedure: Ap_rec_inv_get_info
726
727 This PROCEDURE is responsible for getting values from several different
728 database column.
729 *=====================================================================*/
730 PROCEDURE ap_rec_inv_get_info(
731 P_invoice_date IN DATE,
732 P_invoice_amount IN NUMBER,
733 P_accounting_date IN DATE,
734 P_vendor_id IN NUMBER,
735 P_vendor_site_id IN NUMBER,
736 P_set_of_books_id IN NUMBER,
737 P_tax_id IN NUMBER,
738 P_invoice_currency_code IN VARCHAR2,
739 P_po_shipment_id IN NUMBER,
740 P_terms_date OUT NOCOPY DATE,
741 P_gl_period_name OUT NOCOPY VARCHAR2,
742 P_tax_amount OUT NOCOPY NUMBER,
743 P_type_1099 OUT NOCOPY VARCHAR2,
744 P_income_tax_region OUT NOCOPY VARCHAR2,
745 P_payment_priority OUT NOCOPY NUMBER,
746 P_min_unit OUT NOCOPY NUMBER,
747 P_precision OUT NOCOPY NUMBER,
748 P_overbilled OUT NOCOPY VARCHAR2,
749 P_transfer_desc_flex_flag OUT NOCOPY VARCHAR2,
750 P_approval_workflow_flag OUT NOCOPY VARCHAR2,
751 P_inventory_org_id OUT NOCOPY NUMBER,
752 P_asset_bt_code OUT NOCOPY VARCHAR2,
753 P_Price OUT NOCOPY NUMBER,
754 P_quantity OUT NOCOPY NUMBER, /* bug 5228301 */
755 P_retained_amount OUT NOCOPY NUMBER, /* bug 5228301 */
756 P_Match_Type OUT NOCOPY VARCHAR2, /* bug 5228301 */
757 P_Description OUT NOCOPY VARCHAR2,
758 P_unit_meas_lookup_code OUT NOCOPY VARCHAR2,
759 P_ship_to_location_id OUT NOCOPY NUMBER,
760 P_calling_sequence IN VARCHAR2) IS
761
762 debug_info VARCHAR2(100);
763 current_calling_sequence VARCHAR2(2000);
764 C_tax_amount NUMBER;
765 C_quantity_outstanding NUMBER;
766 C_quantity NUMBER;
767 C_tax_rate NUMBER;
768 C_asset_book_count NUMBER := 0;
769 l_matching_basis po_line_types.matching_basis%type; /* bug 5228301 */
770
771 BEGIN
772 -- Update the calling sequence
773 --
774 current_calling_sequence := 'ap_rec_inv_get_info<-'||P_calling_sequence;
775
776 -------------------------------------------
777 -- get terms_date
778 -------------------------------------------
779 debug_info := 'Get terms date and rounding rule';
780 SELECT decode(pvs.terms_date_basis, 'Current', SYSDATE,
781 P_invoice_date)
782 INTO P_terms_date
783 FROM po_vendor_sites pvs
784 WHERE vendor_site_id = P_vendor_site_id;
785
786
787 -------------------------------------------
788 -- get gl_period_name
789 -------------------------------------------
790 debug_info := 'Get period name';
791 SELECT period_name
792 INTO P_gl_period_name
793 FROM gl_period_statuses
794 WHERE application_id = 200
795 AND set_of_books_id = P_set_of_books_id
796 AND (closing_status = 'O'
797 OR
798 closing_status = 'F'
799 OR
800 closing_status = 'N') --1569550
801 AND NVL(P_accounting_date, P_invoice_date)
802 BETWEEN start_date AND end_date
803 AND NVL(adjustment_period_flag, 'N') = 'N';
804
805 ---------------------------------------
806 -- Get type_1099 and income_tax_region
807 ---------------------------------------
808 debug_info := 'Get type_1099 and income_tax_region';
809
810 /*
811 2645659 fbreslin: Remove the JOIN to AP_TAX_CODES in the following SQL.
812 */
813
814 BEGIN
815 SELECT v.type_1099,
816 decode(v.type_1099,
817 '','',
818 DECODE(sp.combined_filing_flag,
819 'N','',
820 DECODE(sp.income_tax_region_flag,
821 'Y',vs.state,
822 sp.income_tax_region))),
823 NVL(sp.transfer_desc_flex_flag, 'N'),
824 NVL(sp.approval_workflow_flag, 'N'),
825 fsp.inventory_organization_id,
826 vs.ship_to_location_id
827 INTO P_type_1099,
828 P_income_tax_region,
829 P_transfer_desc_flex_flag,
830 P_approval_workflow_flag,
831 P_inventory_org_id,
832 P_ship_to_location_id
833 FROM po_vendors v,
834 ap_system_parameters sp,
835 po_vendor_sites vs,
836 financials_system_parameters fsp
837 WHERE v.vendor_id = P_vendor_id
838 AND vs.org_id = sp.org_id
839 AND sp.org_id = fsp.org_id
840 AND vs.vendor_site_id = P_vendor_site_id
841 AND rownum < 2;
842
843 EXCEPTION
844 WHEN NO_DATA_FOUND THEN
845 NULL;
846 END;
847
848 ---------------------------------------------------------------------
849 -- Get the Min_unit and precision from fnd_currencies
850 ---------------------------------------------------------------------
851 debug_info := 'Get min_unit and precision';
852 SELECT minimum_accountable_unit, nvl(precision,0)
853 INTO P_min_unit, P_precision
854 FROM fnd_currencies
855 WHERE currency_code = P_invoice_currency_code;
856
857 ---------------------------------------------------------------------
858 -- Get the Payment Priority from po_vendor_site
859 ---------------------------------------------------------------------
860 debug_info := 'Get Payment Priority';
861 SELECT pvs.payment_priority
862 INTO P_payment_priority
863 FROM po_vendors pv, po_vendor_sites pvs
864 WHERE pv.vendor_id = P_vendor_id
865 AND pvs.vendor_site_id = P_vendor_site_id;
866
867 ----------------------------------------------------
868 -- Get quantity_outstanding
869 ----------------------------------------------------
870 debug_info := 'Get quantity_outstanding';
871
872 if(P_po_shipment_id is not null) then
873
874 debug_info := 'Get asset book and set asset category '||
875 'information IF possible';
876 BEGIN
877 SELECT count(*)
878 INTO c_asset_book_count
879 FROM fa_book_controls bc
880 WHERE bc.book_class = 'CORPORATE'
881 AND bc.set_of_books_id = P_set_of_books_id
882 AND bc.date_ineffective is null;
883
884 IF (C_asset_book_count = 1) then
885 SELECT bc.book_type_code
886 INTO P_asset_bt_code
887 FROM fa_book_controls bc
888 WHERE bc.set_of_books_id = P_set_of_books_id
889 AND bc.date_ineffective is null;
890
891 ELSE
892
893 P_asset_bt_code := NULL;
894
895 END IF;
896
897 EXCEPTION
898 -- No need to error handle IF FA information not available.
899 WHEN no_data_found THEN
900 NULL;
901 WHEN OTHERS THEN
902 NULL;
903 END;
904
905 ------------------------------------------------------
906 -- Retreive Unit Price and UOM Lookup Code
907 -- from PO Shipment and
908 -----------------------------------------------------
909 SELECT pll.price_override,
910 pl.unit_meas_lookup_code,
911 pll.matching_basis
912 INTO P_price,
913 P_unit_meas_lookup_code,
914 l_matching_basis /* bug 5228301 */
915 FROM po_line_locations pll, po_lines pl, po_line_types plt
916 WHERE pll.line_location_id = P_po_shipment_id
917 AND pl.po_line_id = pll.po_line_id
918 AND pl.line_type_id = plt.line_type_id;
919
920 IF l_matching_basis = 'QUANTITY' THEN
921 C_quantity := round((P_invoice_amount)/P_price, 15);
922 P_Quantity := C_quantity; /*bug 5228301 */
923 P_Match_Type := 'ITEM_TO_PO';
924 ELSE
925 P_Match_Type := 'ITEM_TO_SERVICE_PO'; /*bug 5228301 */
926 END IF;
927
928 -------------------------------------------------------------
929 -- Retreive description 'Created by Recurring Invoice'
930 -- from ap_lookup_codes to enable translation Problem
931 --------------------------------------------------------------
932 debug_info := 'get new description';
933 Begin
934 SELECT description INTO P_Description
935 FROM ap_lookup_codes
936 WHERE lookup_type = 'SOURCE' and
937 lookup_code = 'RECURRING INVOICE';
938 EXCEPTION
939 WHEN NO_DATA_FOUND Then
940 NULL;
941 End;
942
943 -- Bug 404997 has changed the following select Condition
944 /* bug 5228301, following statement has modified */
945 SELECT decode(l_matching_basis, 'QUANTITY',
946 (sum(nvl(pd.quantity_ordered,0) - nvl(pd.quantity_billed,0) -
947 nvl(pd.quantity_cancelled,0)) - ((p_invoice_amount - nvl(p_tax_amount,0))
948 /pll.price_override)),
949 (sum(nvl(pd.amount_ordered,0) - nvl(pd.amount_billed,0) -
950 nvl(pd.amount_cancelled,0)) - (p_invoice_amount - nvl(p_tax_amount,0)))
951 )
952 INTO C_quantity_outstanding
953 FROM po_distributions_ap_v pd,po_line_locations pll
954 WHERE pd.line_location_id=pll.line_location_id
955 AND pd.line_location_id = P_po_shipment_id
956 GROUP BY pll.line_location_id,pll.price_override;
957
958
959 ----------------------------------------------------
960 -- Decide IF overbilled
961 ----------------------------------------------------
962 IF l_matching_basis = 'QUANTITY' THEN
963 IF ( C_quantity > C_quantity_outstanding ) then
964 P_overbilled := 'Y';
965 ELSE
966 P_overbilled := 'N';
967 END IF;
968 ELSE /* bug 5228301 */
969 IF ( P_invoice_amount > C_quantity_outstanding ) then
970 P_overbilled := 'Y';
971 ELSE
972 P_overbilled := 'N';
973 END IF;
974 END IF;
975
976
977 /* bug 5228301. Retainage amount */
978 P_retained_amount := AP_INVOICE_LINES_UTILITY_PKG.Get_Retained_Amount
979 (p_po_shipment_id,
980 p_invoice_amount);
981
982 END IF;
983
984 EXCEPTION
985 WHEN NO_DATA_FOUND then
986
987 IF (debug_info = 'get offset tax amount and name') then
988 NULL;
989 ELSE
990 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
991 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
992 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
993 FND_MESSAGE.SET_TOKEN('PARAMETERS',
994 'Invoice_date = '||TO_CHAR(P_invoice_date)
995 ||' Invoice_amount = '||TO_CHAR(P_invoice_amount)
996 ||' Vendor_id = '||TO_CHAR(P_vendor_id)
997 ||' Vendor_site_id = '||TO_CHAR(P_vendor_site_id)
998 ||' Set_of_books_id = '||TO_CHAR(P_set_of_books_id)
999 ||' Accounting_date = '||TO_CHAR(P_accounting_date)
1000 ||' Tax_id = '||TO_CHAR(P_tax_id)
1001 ||' Currency_code = '||P_invoice_currency_code);
1002
1003 IF (debug_info = 'Get min_unit and precision') then
1004 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','No currency code for this invoice');
1005 APP_EXCEPTION.RAISE_EXCEPTION;
1006 elsif(debug_info ='Get period name') then
1007 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','the GL_date(sysdate) is not in an open period');
1008 APP_EXCEPTION.RAISE_EXCEPTION;
1009 else
1010 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1011 APP_EXCEPTION.RAISE_EXCEPTION;
1012 END IF;
1013 END IF;
1014
1015 WHEN OTHERS then
1016
1017 IF ((SQLCODE <> -20001) OR ((SQLCODE <> -20002))) then
1018 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1019 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1020 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1021
1022 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1023 END IF;
1024 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1025 'Invoice_date = '||TO_CHAR(P_invoice_date)
1026 ||' Invoice_amount = '||TO_CHAR(P_invoice_amount)
1027 ||' Vendor_id = '||TO_CHAR(P_vendor_id)
1028 ||' Vendor_site_id = '||TO_CHAR(P_vendor_site_id)
1029 ||' Set_of_books_id = '||TO_CHAR(P_set_of_books_id)
1030 ||' Accounting_date = '||TO_CHAR(P_accounting_date)
1031 ||' Tax_id = '||TO_CHAR(P_tax_id)
1032 ||' Currency_code = '||P_invoice_currency_code);
1033 APP_EXCEPTION.RAISE_EXCEPTION;
1034
1035 END ap_rec_inv_get_info;
1036
1037
1038 /*======================================================================
1039 Private Procedure: Insert new AP_BATCHES lines
1040
1041 Insert New Batch line IF the batch name is new
1042
1043 ========================================================================*/
1044 PROCEDURE ap_rec_inv_insert_ap_batches(
1045 P_batch_name IN VARCHAR2,
1046 P_batch_control_flag IN VARCHAR2,
1047 P_invoice_currency_code IN VARCHAR2,
1048 P_payment_currency_code IN VARCHAR2,
1049 P_last_update_date IN DATE,
1050 P_last_updated_by IN NUMBER,
1051 P_batch_id OUT NOCOPY NUMBER,
1052 P_calling_sequence IN VARCHAR2,
1053 P_org_id IN NUMBER) IS
1054
1055 current_calling_sequence VARCHAR2(2000);
1056 debug_info VARCHAR2(100);
1057 C_batch_id NUMBER;
1058 C_batch_date DATE;
1059 C_old_batch_flag VARCHAR2(20);
1060
1061 BEGIN
1062 -- Update the calling sequence
1063 --
1064 current_calling_sequence :=
1065 'ap_rec_inv_insert_ap_batches<-'
1066 ||P_calling_sequence;
1067
1068 ---------------------------------------------
1069 -- Return IF batch control flag is N
1070 ---------------------------------------------
1071 IF (nvl(P_batch_control_flag,'N') <> 'Y') then
1072 RETURN;
1073 END IF;
1074
1075 ---------------------------------------------
1076 -- Return IF batch_name was existed
1077 ---------------------------------------------
1078 debug_info := 'Check batch_name existance';
1079
1080 BEGIN
1081 SELECT 'OLD BATCH',batch_id
1082 INTO C_old_batch_flag, P_batch_id
1083 FROM ap_batches_all
1084 WHERE batch_name = P_batch_name;
1085
1086 IF (C_old_batch_flag = 'OLD BATCH') then
1087 RETURN;
1088 END IF;
1089
1090 EXCEPTION
1091 WHEN NO_DATA_FOUND THEN NULL;
1092 END;
1093
1094 ---------------------------------------------
1095 -- Get New batch_id and Batch_date
1096 ---------------------------------------------
1097 debug_info := 'Get New batch_id and batch_date';
1098
1099 --Modified sysdate as to_date(to_char(sysdate,'MM/DD/YYYY'), 'MM/DD/YYYY')
1100 --for the bug 7383201/7371814
1101
1102 SELECT ap_batches_s.nextval, to_date(to_char(sysdate,'MM/DD/YYYY'), 'MM/DD/YYYY')
1103 INTO C_batch_id, C_batch_date
1104 FROM sys.dual;
1105
1106 ---------------------------------------------
1107 -- Insert ap_batches
1108 ---------------------------------------------
1109 debug_info := 'Insert ap_batches';
1110
1111 INSERT INTO ap_batches_all
1112 (batch_id,
1113 batch_name,
1114 batch_date,
1115 last_update_date,
1116 last_updated_by,
1117 invoice_currency_code,
1118 payment_currency_code,
1119 creation_date,
1120 created_by,
1121 org_id)
1122 VALUES(C_batch_id,
1123 P_batch_name,
1124 C_batch_date,
1125 P_last_update_date,
1126 P_last_updated_by,
1127 P_invoice_currency_code,
1128 P_payment_currency_code,
1129 P_last_update_date,
1130 P_last_updated_by,
1131 P_Org_Id);
1132 --
1133 -- Transfer batch_id
1134 --
1135 P_batch_id := C_batch_id;
1136
1137
1138 EXCEPTION
1139 WHEN OTHERS then
1140
1141 IF ((SQLCODE <> -20001) AND ((SQLCODE <> -20002))) then
1142 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1143 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1144 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1145 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1146 'P_batch_name = '||P_batch_name
1147 ||'P_batch_control_flag = '|| P_batch_control_flag
1148 ||'P_invoice_currency_code = '||P_invoice_currency_code
1149 ||'P_payment_currency_code = '||P_payment_currency_code
1150 ||'P_last_update_date = '||TO_CHAR(P_last_update_date)
1151 ||'P_last_updated_by = '||TO_CHAR(P_last_updated_by));
1152 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1153 END IF;
1154
1155 APP_EXCEPTION.RAISE_EXCEPTION;
1156
1157 END ap_rec_inv_insert_ap_batches;
1158
1159
1160
1161 /*==========================================================================
1162 Private Procedure: ap_rec_inv_insert_ap_invoices
1163
1164 Insert AP_INVOICES for these recurring invoices
1165 *=====================================================================*/
1166 PROCEDURE ap_rec_inv_insert_ap_invoices(
1167 P_batch_id IN NUMBER,
1168 P_last_update_date IN DATE,
1169 P_last_updated_by IN NUMBER,
1170 P_invoice_currency_code IN VARCHAR2,
1171 P_payment_currency_code IN VARCHAR2,
1172 P_base_currency_code IN VARCHAR2,
1173 P_invoice_id IN OUT NOCOPY NUMBER,
1174 P_invoice_num IN VARCHAR2,
1175 P_invoice_amount IN NUMBER,
1176 P_vendor_id IN NUMBER,
1177 P_vendor_site_id IN NUMBER,
1178 P_invoice_date IN DATE,
1179 P_description IN VARCHAR2,
1180 P_tax_name IN VARCHAR2,
1181 P_tax_amount IN NUMBER,
1182 P_terms_id IN NUMBER,
1183 P_pay_group_lookup_code IN VARCHAR2,
1184 P_set_of_books_id IN NUMBER,
1185 P_accts_pay_ccid IN NUMBER,
1186 P_payment_cross_rate IN NUMBER,
1187 P_exchange_date IN DATE,
1188 P_exchange_rate_type IN VARCHAR2,
1189 P_exchange_rate IN NUMBER,
1190 P_invoice_base_amount IN NUMBER,
1191 P_recurring_payment_id IN NUMBER,
1192 P_terms_date IN DATE,
1193 P_doc_sequence_id IN NUMBER,
1194 P_doc_sequence_value IN NUMBER,
1195 P_doc_category_code IN VARCHAR2,
1196 P_exclusive_payment_flag IN VARCHAR2,
1197 P_awt_group_id IN NUMBER,
1198 P_pay_awt_group_id IN NUMBER,--bug6639866
1199 P_distribution_set_id IN NUMBER,
1200 P_accounting_date IN DATE,
1201 -- P_ussgl_txn_code IN VARCHAR2, - Bug 4277744
1202 P_attribute1 IN VARCHAR2,
1203 P_attribute2 IN VARCHAR2,
1204 P_attribute3 IN VARCHAR2,
1205 P_attribute4 IN VARCHAR2,
1206 P_attribute5 IN VARCHAR2,
1207 P_attribute6 IN VARCHAR2,
1208 P_attribute7 IN VARCHAR2,
1209 P_attribute8 IN VARCHAR2,
1210 P_attribute9 IN VARCHAR2,
1211 P_attribute10 IN VARCHAR2,
1212 P_attribute11 IN VARCHAR2,
1213 P_attribute12 IN VARCHAR2,
1214 P_attribute13 IN VARCHAR2,
1215 P_attribute14 IN VARCHAR2,
1216 P_attribute15 IN VARCHAR2,
1217 P_attribute_category IN VARCHAR2,
1218 P_calling_sequence IN VARCHAR2,
1219 P_Org_Id IN NUMBER,
1220 P_Requester_Id IN NUMBER,
1221 P_Tax_Control_Amount IN NUMBER,
1222 P_Trx_Business_Category IN VARCHAR2,
1223 P_User_Defined_Fisc_Class IN VARCHAR2,
1224 P_Taxation_Country IN VARCHAR2,
1225 P_Legal_Entity_Id IN NUMBER,
1226 p_PAYMENT_METHOD_CODE in varchar2,
1227 p_PAYMENT_REASON_CODE in varchar2,
1228 p_remittance_message1 in varchar2,
1229 p_remittance_message2 in varchar2,
1230 p_remittance_message3 in varchar2,
1231 p_bank_charge_bearer in varchar2,
1232 p_settlement_priority in varchar2,
1233 p_payment_reason_comments in varchar2,
1234 p_delivery_channel_code in varchar2,
1235 p_external_bank_account_id in number,
1236 p_party_id in number,
1237 p_party_site_id in number,
1238 /* bug 4931755. Exclude Tax From Discount */
1239 p_disc_is_inv_less_tax_flag in varchar2,
1240 p_exclude_freight_from_disc in varchar2,
1241 P_REMIT_TO_SUPPLIER_NAME in VARCHAR2,
1242 P_REMIT_TO_SUPPLIER_ID in NUMBER,
1243 P_REMIT_TO_SUPPLIER_SITE in VARCHAR2,
1244 P_REMIT_TO_SUPPLIER_SITE_ID in NUMBER,
1245 P_RELATIONSHIP_ID in NUMBER
1246 ) IS
1247
1248 C_invoice_id NUMBER;
1249 debug_info VARCHAR2(100);
1250 current_calling_sequence VARCHAR2(2000);
1251 C_source ap_lookup_codes.displayed_field%TYPE;
1252 l_ready_for_wf VARCHAR2(1);
1253 l_use_workflow_flag VARCHAR2(1);
1254 l_wfapproval_status VARCHAR2(50);
1255 l_approval_required_flag VARCHAR2(1);
1256 l_pay_curr_invoice_amount NUMBER; --4392543
1257
1258 BEGIN
1259 -- Update the calling sequence
1260 --
1261 current_calling_sequence :=
1262 'ap_rec_inv_insert_ap_invoices<-'||P_calling_sequence;
1263
1264
1265 -------------------------------------------
1266 -- get new invoice_id
1267 -------------------------------------------
1268 debug_info := 'get new invoice_id';
1269 SELECT ap_invoices_s.nextval
1270 INTO C_invoice_id
1271 FROM sys.dual;
1272
1273 ---------------------------------------
1274 -- Get WFApproval option -- not done in get_info
1275 --because need recurring_payment_id
1276 ---------------------------------------
1277 debug_info := 'Get WF Approval option';
1278 BEGIN
1279 SELECT approval_workflow_flag
1280 INTO l_use_workflow_flag
1281 FROM ap_system_parameters;
1282
1283 SELECT approval_required_flag
1284 INTO l_approval_required_flag
1285 FROM ap_recurring_payments_all
1286 WHERE recurring_payment_id = P_recurring_payment_id;
1287
1288 EXCEPTION
1289 WHEN NO_DATA_FOUND THEN
1290 NULL;
1291 END;
1292
1293 IF nvl(l_use_workflow_flag,'N') = 'Y' THEN
1294 IF nvl(l_approval_required_flag,'N') = 'Y'THEN
1295 l_wfapproval_status := 'REQUIRED';
1296 ELSE
1297 l_wfapproval_status := 'NOT REQUIRED';
1298 END IF;
1299 ELSE
1300 l_wfapproval_status := 'NOT REQUIRED';
1301 END IF;
1302
1303 l_ready_for_wf := 'Y'; --bug 2333796
1304
1305 --4392543
1306 l_pay_curr_invoice_amount:=
1307 ap_utilities_pkg.ap_round_currency(
1308 (P_invoice_amount * nvl( P_payment_cross_rate,1)),
1309 P_payment_currency_code);
1310
1311
1312
1313 -------------------------------------------
1314 -- Insert ap_invoices
1315 -------------------------------------------
1316 debug_info := 'Insert ap_invoices';
1317 INSERT INTO ap_invoices_all(
1318 invoice_id,
1319 last_update_date,
1320 last_updated_by,
1321 last_update_login, -- 2888897
1322 vendor_id,
1323 invoice_num,
1324 invoice_amount,
1325 vendor_site_id,
1326 amount_paid,
1327 discount_amount_taken,
1328 invoice_date,
1329 invoice_type_lookup_code,
1330 description,
1331 batch_id,
1332 amount_applicable_to_discount,
1333 tax_amount,
1334 terms_id,
1335 approved_amount,
1336 approval_status,
1337 approval_description,
1338 pay_group_lookup_code,
1339 set_of_books_id,
1340 accts_pay_code_combination_id,
1341 invoice_currency_code,
1342 payment_currency_code,
1343 payment_cross_rate,
1344 exchange_date,
1345 exchange_rate_type,
1346 exchange_rate,
1347 base_amount,
1348 payment_status_flag,
1349 posting_status,
1350 attribute_category,
1351 attribute1,
1352 attribute2,
1353 attribute3,
1354 attribute4,
1355 attribute5,
1356 attribute6,
1357 attribute7,
1358 attribute8,
1359 attribute9,
1360 attribute10,
1361 attribute11,
1362 attribute12,
1363 attribute13,
1364 attribute14,
1365 attribute15,
1366 creation_date,
1367 created_by,
1368 vendor_prepay_amount,
1369 prepay_flag,
1370 recurring_payment_id,
1371 vat_code,
1372 terms_date,
1373 source,
1374 doc_sequence_id,
1375 doc_sequence_value,
1376 doc_category_code,
1377 exclusive_payment_flag,
1378 awt_group_id,
1379 pay_awt_group_id,--bug6639866
1380 gl_date,
1381 wfapproval_status,
1382 approval_ready_flag,
1383 -- ussgl_transaction_code, - Bug 4277744
1384 org_Id,
1385 requester_id,
1386 distribution_set_id,
1387 control_amount,
1388 trx_business_category,
1389 user_defined_fisc_class,
1390 taxation_country,
1391 legal_entity_id,
1392 PAYMENT_METHOD_CODE,
1393 PAYMENT_REASON_CODE,
1394 remittance_message1,
1395 remittance_message2,
1396 remittance_message3,
1397 bank_charge_bearer,
1398 settlement_priority,
1399 payment_reason_comments,
1400 delivery_channel_code,
1401 external_bank_account_id,
1402 party_id,
1403 party_site_id,
1404 pay_curr_invoice_amount, -- 4992543
1405 disc_is_inv_less_tax_flag, -- 4931755
1406 exclude_freight_from_discount, -- 4931755
1407 REMIT_TO_SUPPLIER_NAME,
1408 REMIT_TO_SUPPLIER_ID,
1409 REMIT_TO_SUPPLIER_SITE,
1410 REMIT_TO_SUPPLIER_SITE_ID,
1411 RELATIONSHIP_ID
1412 )
1413 VALUES(
1414 C_invoice_id,
1415 P_last_update_date,
1416 P_last_updated_by,
1417 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')), -- 2888897
1418 P_vendor_id,
1419 P_invoice_num,
1420 P_invoice_amount,
1421 P_vendor_site_id,
1422 0,
1423 0,
1424 P_invoice_date,
1425 DECODE(SIGN(P_invoice_amount),
1426 -1, 'CREDIT', 'STANDARD'),
1427 P_description,
1428 P_batch_id,
1429 P_invoice_amount,
1430 P_tax_amount,
1431 P_terms_id,
1432 NULL,
1433 NULL,
1434 NULL,
1435 P_pay_group_lookup_code,
1436 P_set_of_books_id,
1437 P_accts_pay_ccid,
1438 P_invoice_currency_code,
1439 P_payment_currency_code,
1440 P_payment_cross_rate,
1441 Decode(P_exchange_date, NULL,
1442 decode(P_base_currency_code, P_invoice_currency_code,
1443 NULL,
1444 NVL(P_accounting_date,P_invoice_date)
1445 ), P_exchange_date),
1446 P_exchange_rate_type,
1447 P_exchange_rate,
1448 P_invoice_base_amount,
1449 'N',
1450 NULL,
1451 P_attribute_category,
1452 P_attribute1,
1453 P_attribute2,
1454 P_attribute3,
1455 P_attribute4,
1456 P_attribute5,
1457 P_attribute6,
1458 P_attribute7,
1459 P_attribute8,
1460 P_attribute9,
1461 P_attribute10,
1462 P_attribute11,
1463 P_attribute12,
1464 P_attribute13,
1465 P_attribute14,
1466 P_attribute15,
1467 P_last_update_date,
1468 P_last_updated_by,
1469 0,
1470 'N',
1471 P_recurring_payment_id,
1472 P_tax_name,
1473 P_terms_date,
1474 'RECURRING INVOICE', -- 1951771 Use RECURRING INVOICE
1475 P_doc_sequence_id,
1476 P_doc_sequence_value,
1477 P_doc_category_code,
1478 P_exclusive_payment_flag,
1479 P_awt_group_id,
1480 P_pay_awt_group_id,--bug6639866
1481 P_accounting_date,
1482 l_wfapproval_status,
1483 l_ready_for_wf,
1484 -- P_ussgl_txn_code, - Bug 4277744
1485 P_Org_Id,
1486 P_Requester_Id,
1487 P_Distribution_Set_Id,
1488 P_Tax_Control_Amount,
1489 P_Trx_Business_Category,
1490 P_User_Defined_Fisc_Class,
1491 P_Taxation_Country,
1492 P_Legal_Entity_Id,
1493 p_PAYMENT_METHOD_CODE,
1494 p_PAYMENT_REASON_CODE,
1495 p_remittance_message1,
1496 p_remittance_message2,
1497 p_remittance_message3,
1498 p_bank_charge_bearer,
1499 p_settlement_priority,
1500 p_payment_reason_comments,
1501 p_delivery_channel_code,
1502 p_external_bank_account_id,
1503 p_party_id,
1504 p_party_site_id,
1505 l_pay_curr_invoice_amount, --4392543
1506 /* bug 4931755. Exclude Tax From Discount */
1507 p_disc_is_inv_less_tax_flag,
1508 p_exclude_freight_from_disc,
1509 P_REMIT_TO_SUPPLIER_NAME,
1510 P_REMIT_TO_SUPPLIER_ID,
1511 P_REMIT_TO_SUPPLIER_SITE,
1512 P_REMIT_TO_SUPPLIER_SITE_ID,
1513 P_RELATIONSHIP_ID
1514 );
1515
1516 --Bug 4539462 DBI logging
1517 AP_DBI_PKG.Maintain_DBI_Summary
1518 (p_table_name => 'AP_INVOICES',
1519 p_operation => 'I',
1520 p_key_value1 => C_invoice_id,
1521 p_calling_sequence => current_calling_sequence);
1522
1523 P_invoice_id := C_invoice_id;
1524
1525 EXCEPTION
1526 WHEN OTHERS then
1527
1528 IF ((SQLCODE <> -20001) or ((SQLCODE <> -20002)) ) then
1529 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1530 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1531 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1532 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1533 ' batch_id = '||TO_CHAR(p_batch_id)
1534 ||' c_source = '||c_source
1535 ||' P_invoice_id = '||TO_CHAR(C_invoice_id)
1536 ||' P_tax_name = '||P_tax_name
1537 ||' P_tax_amount = '||TO_CHAR(P_tax_amount)
1538 ||' P_accts_pay_ccid = '||TO_CHAR(P_accts_pay_ccid)
1539 ||' P_invoice_base_amount = '||TO_CHAR(P_invoice_base_amount)
1540 ||' P_recurring_payment_id = '||TO_CHAR(P_recurring_payment_id)
1541 ||' P_exclusive_payment_flag = '||P_exclusive_payment_flag
1542 ||' P_awt_group_id = '||TO_CHAR(P_awt_group_id)
1543 ||' P_pay_awt_group_id = '||TO_CHAR(P_pay_awt_group_id) --bug6639866
1544 ||' Invoice_date = '||TO_CHAR(P_invoice_date)
1545 ||' accounting_date = '||TO_CHAR(P_accounting_date)
1546 ||' Vendor_id = '||TO_CHAR(P_vendor_id)
1547 ||' Vendor_site_id = '||TO_CHAR(P_vendor_site_id)
1548 ||' Invoice_num = '||P_invoice_num
1549 ||' Invoice Amount = '||TO_CHAR(P_invoice_amount)
1550 ||' P_doc_category_code = '||P_doc_category_code
1551 ||' Doc_sequence_value = '||TO_CHAR(P_doc_sequence_value)
1552 ||' Doc_sequence_id = '||TO_CHAR(P_doc_sequence_id)
1553 ||' Pay_group_lookup_code = '||P_pay_group_lookup_code
1554 ||' Invoice_currency_code = '||P_invoice_currency_code
1555 ||' Payment_currency_code = '||P_payment_currency_code
1556 ||' Base_currency_code = '||P_base_currency_code
1557 ||' Terms_date = '||TO_CHAR(P_terms_date)
1558 ||' Terms_id = '||TO_CHAR(P_terms_id)
1559 ||' Payment_cross_rate = '||TO_CHAR(P_payment_cross_rate)
1560 ||' Exchange Rate = '||TO_CHAR(P_exchange_rate)
1561 ||' Exchange Rate Type = '||P_exchange_rate_type
1562 ||' Exchange Date = '||TO_CHAR(P_exchange_date)
1563 ||' Set 0f books id = '||TO_CHAR(P_set_of_books_id)
1564 ||' Last_updated_by = '||TO_CHAR(P_last_updated_by)
1565 ||' Last_updated_date = '||TO_CHAR(P_last_update_date));
1566
1567 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1568 END IF;
1569
1570 APP_EXCEPTION.RAISE_EXCEPTION;
1571
1572 END ap_rec_inv_insert_ap_invoices;
1573
1574
1575
1576 /*======================================================================
1577 Pubilc Function: Get Next available period Name
1578
1579 The FUNCTION accept following parameter:
1580
1581 +---------------------------------------------------------------------+
1582 | Variable | NULL? | Type | Description |
1583 +=====================================================================+
1584 | P_period_type | No | VARCHAR2 | period_type |
1585 +---------------------------------------------------------------------+
1586 | P_current_period | | | |
1587 | _name | No | VARCHAR2 | Current Period_name |
1588 +---------------------------------------------------------------------+
1589
1590
1591 There are 3 output parameter:
1592 +---------------------------------------------------------------------+
1593 | Variable | NULL? | Type | Description |
1594 +=====================================================================+
1595 | P_next_period_name | No | VARCHAR2 | Next availbale period |
1596 | | | | name |
1597 +---------------------------------------------------------------------+
1598 | P_next_period_year | | NUMBER | Period year for the |
1599 | | No | | new period |
1600 +---------------------------------------------------------------------+
1601 | P_next_period_num | No | NUMBER | Next Period_num |
1602 +---------------------------------------------------------------------+
1603
1604
1605 ========================================================================*/
1606 PROCEDURE ap_get_next_period(
1607 P_period_type IN VARCHAR2,
1608 P_current_period_name IN VARCHAR2,
1609 P_next_period_name OUT NOCOPY VARCHAR2,
1610 P_next_period_num OUT NOCOPY NUMBER,
1611 P_next_period_year OUT NOCOPY NUMBER,
1612 P_calling_sequence IN VARCHAR2) IS
1613
1614 current_calling_sequence VARCHAR2(2000);
1615 debug_info VARCHAR2(100);
1616 C_current_period_num NUMBER;
1617 C_period_year NUMBER;
1618 C_next_period_num NUMBER;
1619
1620 BEGIN
1621 -- Update the calling sequence
1622 --
1623 current_calling_sequence := 'ap_get_next_period<-'
1624 ||P_calling_sequence;
1625
1626 ---------------------------------------------
1627 -- Get the current period_num and period_year
1628 ---------------------------------------------
1629 debug_info := 'Get the current period_num and period_year';
1630 SELECT period_num, period_year
1631 INTO C_current_period_num, C_period_year
1632 FROM ap_other_periods
1633 WHERE period_name = P_current_period_name
1634 AND module = 'RECURRING PAYMENTS'
1635 AND period_type = P_period_type;
1636
1637 ----------------------------------------------------------
1638 -- Get the Next period_num
1639 ----------------------------------------------------------
1640 BEGIN
1641 debug_info := 'Get next period_num';
1642 SELECT min(period_num)
1643 INTO C_next_period_num
1644 FROM ap_other_periods
1645 WHERE period_year = C_period_year
1646 AND to_number(period_num) > C_current_period_num
1647 AND module = 'RECURRING PAYMENTS'
1648 AND period_type = P_period_type;
1649 EXCEPTION
1650 WHEN NO_DATA_FOUND then
1651 NULL;
1652 END;
1653
1654 -----------------------------------------------------------------
1655 -- Get the Next period_num IF there's no more period in this year
1656 -----------------------------------------------------------------
1657 IF (C_next_period_num IS NULL) then
1658 BEGIN
1659 debug_info := 'Get next year period_num';
1660 SELECT min(period_num), C_period_year + 1
1661 INTO C_next_period_num, C_period_year
1662 FROM ap_other_periods
1663 WHERE period_year = C_period_year + 1
1664 AND module = 'RECURRING PAYMENTS'
1665 AND period_type = P_period_type;
1666 EXCEPTION
1667 WHEN NO_DATA_FOUND then
1668 NULL;
1669 END;
1670
1671 END IF;
1672
1673 ----------------------------------------------------------
1674 -- Get the Next period_name
1675 ----------------------------------------------------------
1676 IF (C_next_period_num IS NULL) THEN
1677 P_next_period_name := '';
1678 P_next_period_num := '';
1679 P_next_period_year := '';
1680 RETURN;
1681 END IF;
1682
1683 ----------------------------------------------------------
1684 -- Get the Next period_name
1685 ----------------------------------------------------------
1686 debug_info := 'Get next period name';
1687 SELECT period_name
1688 INTO P_next_period_name
1689 FROM ap_other_periods
1690 WHERE period_year = C_period_year
1691 AND period_num = C_next_period_num
1692 AND period_type = P_period_type
1693 AND module = 'RECURRING PAYMENTS';
1694
1695 ----------------------------------------------------------
1696 -- Get P_next_period_year P_next_period_num
1697 ----------------------------------------------------------
1698 debug_info := 'Get next period_num and period_year';
1699
1700 P_next_period_num := C_next_period_num;
1701 P_next_period_year := C_period_year;
1702
1703
1704 EXCEPTION
1705
1706 WHEN NO_DATA_FOUND then
1707 IF (debug_info = 'Get next period_num') then
1708 NULL;
1709
1710 ELSIF (debug_info = 'Get next year period_num') then
1711 NULL;
1712 else
1713
1714 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1715 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1716 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1717 FND_MESSAGE.SET_TOKEN('PARAMETERS','Period_type = '||P_period_type
1718 ||' Current period name = '||P_current_period_name);
1719 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1720 APP_EXCEPTION.RAISE_EXCEPTION;
1721 END IF;
1722
1723 WHEN OTHERS then
1724
1725 IF (SQLCODE <> -20001 ) then
1726 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1727 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1728 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1729 FND_MESSAGE.SET_TOKEN('PARAMETERS','Period_type = '||P_period_type
1730 ||' Current period name = '||P_current_period_name);
1731 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1732 END IF;
1733
1734 APP_EXCEPTION.RAISE_EXCEPTION;
1735
1736 END ap_get_next_period;
1737
1738
1739 /*======================================================================
1740 Pubilc Function: Calculate next payment amount
1741
1742 The FUNCTION accept following parameter:
1743
1744 +---------------------------------------------------------------------+
1745 | Variable | NULL? | Type | Description |
1746 +=====================================================================+
1747
1748
1749
1750 There are 2 output parameter:
1751 +---------------------------------------------------------------------+
1752 | Variable | NULL? | Type | Description |
1753 +=====================================================================+
1754 | P_next_amount | No | NUMBER | the next available |
1755 | | | | amount |
1756 +---------------------------------------------------------------------+
1757 | P_next_amount_ | No | NUMBER | Next amount remaining |
1758 | exclude_special | | | same IF in special |
1759 | | | | period |
1760 +---------------------------------------------------------------------+
1761
1762
1763 ========================================================================*/
1764
1765 PROCEDURE ap_get_next_payment(
1766 P_next_period_name IN VARCHAR2,
1767 P_special_period_name1 IN VARCHAR2,
1768 P_special_payment_amount1 IN NUMBER,
1769 P_special_period_name2 IN VARCHAR2,
1770 P_special_payment_amount2 IN NUMBER,
1771 P_increment_percent IN NUMBER,
1772 P_currency_code IN VARCHAR2,
1773 P_current_amount IN NUMBER,
1774 P_next_amount OUT NOCOPY NUMBER,
1775 P_next_amount_exclude_special OUT NOCOPY NUMBER,
1776 P_calling_sequence IN VARCHAR2) IS
1777
1778 current_calling_sequence VARCHAR2(2000);
1779 debug_info VARCHAR2(100);
1780 C_next_amount NUMBER;
1781
1782 BEGIN
1783 -- Update the calling sequence
1784 --
1785 current_calling_sequence := 'ap_get_next_payment<-'||P_calling_sequence;
1786
1787 ---------------------------------------------
1788 -- Get next payment
1789 ---------------------------------------------
1790 debug_info := 'Get Next payment';
1791
1792 IF (P_next_period_name = P_special_period_name1) then
1793 P_next_amount := P_special_payment_amount1;
1794 P_next_amount_exclude_special := P_current_amount;
1795
1796 ELSIF (P_next_period_name = P_special_period_name2) then
1797 P_next_amount := P_special_payment_amount2;
1798 P_next_amount_exclude_special := P_current_amount;
1799
1800 else
1801 C_next_amount := ap_utilities_pkg.ap_round_currency(
1802 P_current_amount * (1+ (NVL(P_increment_percent, 0)/100))
1803 , P_currency_code);
1804 P_next_amount := C_next_amount;
1805 P_next_amount_exclude_special := C_next_amount;
1806
1807 END IF;
1808
1809
1810 EXCEPTION
1811
1812 WHEN OTHERS then
1813
1814 IF (SQLCODE <> -20001 ) then
1815 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1816 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1817 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1818 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1819 'Increment_percent = '||TO_CHAR(P_increment_percent)
1820 ||' Next period name = '||P_next_period_name);
1821 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1822 END IF;
1823
1824 APP_EXCEPTION.RAISE_EXCEPTION;
1825
1826 END ap_get_next_payment;
1827
1828
1829
1830 /* ======================================================================
1831 Pubilc Function: Get the first_amount from control_total, or vice versa
1832
1833 The FUNCTION accept following parameter:
1834
1835 +---------------------------------------------------------------------+
1836 | Variable | NULL? | Type | Description |
1837 +=====================================================================+
1838 ...
1839 +---------------------------------------------------------------------+
1840 | P_amount_source_flag| No | VARCHAR2 | 'TOTAL' or "FIRST' |
1841 +---------------------------------------------------------------------+
1842 | P_number_of_regular | No | NUMBER | Number of periods |
1843 | _periods | | | exclude special period|
1844 +---------------------------------------------------------------------+
1845
1846
1847 There are 3 output parameter:
1848 +---------------------------------------------------------------------+
1849 | Variable | NULL? | Type | Description |
1850 +=====================================================================+
1851 | P_first_amount | Maybe | NUMBER | get first amount IF |
1852 | | | | P_amount_source_flag |
1853 | | | | = 'TOTAL'
1854 +---------------------------------------------------------------------+
1855 | P_control_total | Maybe | NUMBER | get first amount IF |
1856 | | | | P_amount_source_flag |
1857 | | | | = 'FIRST' |
1858 +---------------------------------------------------------------------+
1859
1860 ======================================================================== */
1861
1862 PROCEDURE ap_get_first_amount(
1863 P_first_period_name IN VARCHAR2,
1864 P_special_period_name1 IN VARCHAR2,
1865 P_special_payment_amount1 IN NUMBER,
1866 P_special_period_name2 IN VARCHAR2,
1867 P_special_payment_amount2 IN NUMBER,
1868 P_number_of_regular_periods IN NUMBER,
1869 P_amount_source_flag IN VARCHAR2,
1870 P_increment_percent IN NUMBER,
1871 P_currency_code IN VARCHAR2,
1872 P_first_amount IN OUT NOCOPY NUMBER,
1873 P_control_total IN OUT NOCOPY NUMBER,
1874 P_calling_sequence IN VARCHAR2) IS
1875
1876 current_calling_sequence VARCHAR2(2000);
1877 debug_info VARCHAR2(100);
1878 i INTEGER;
1879 C_total_percentage NUMBER := 1;
1880 C_percentage_factor NUMBER := 1;
1881 C_first_amount NUMBER;
1882 C_total NUMBER;
1883
1884 BEGIN
1885 -- Update the calling sequence
1886 --
1887 current_calling_sequence := 'ap_get_first_amount<-'||P_calling_sequence;
1888
1889 ---------------------------------------------
1890 -- Split case : calculate (i) first_amount
1891 -- (ii) control total
1892 ---------------------------------------------
1893
1894 IF (P_amount_source_flag = 'TOTAL') then
1895 ---------------------------------------------
1896 -- Case i: calculate first_amount
1897 ---------------------------------------------
1898 ---------------------------------------------
1899 -- Get total pencentage using NUMBER_of_regular_periods
1900 ---------------------------------------------
1901 debug_info := 'Get total_percentage';
1902 FOR i in 1..(P_number_of_regular_periods - 1)
1903 LOOP
1904
1905 C_percentage_factor := C_percentage_factor *
1906 (1+ (NVL(P_increment_percent, 0)/100));
1907 C_total_percentage := C_total_percentage + C_percentage_factor;
1908
1909 END LOOP;
1910 ---------------------------------------------
1911 -- Get first amount
1912 ---------------------------------------------
1913 debug_info := 'Get first amount';
1914 IF (P_first_period_name = P_special_period_name1) then
1915 C_first_amount := (P_control_total - NVL(P_special_payment_amount1,0)
1916 - NVL(P_special_payment_amount2,0))
1917 / C_total_percentage;
1918
1919 ELSIF (P_first_period_name = P_special_period_name2) then
1920 C_first_amount := P_special_payment_amount2;
1921
1922 else
1923 C_first_amount := (P_control_total - NVL(P_special_payment_amount1,0)
1924 - NVL(P_special_payment_amount2,0))
1925 / C_total_percentage;
1926
1927 END IF;
1928
1929 P_first_amount := ap_utilities_pkg.ap_round_currency(
1930 C_first_amount , P_currency_code);
1931
1932
1933 ELSIF (P_amount_source_flag = 'FIRST') then
1934 ---------------------------------------------
1935 -- Case ii: calculate control total
1936 ---------------------------------------------
1937 ---------------------------------------------
1938 -- Get total pencentage using NUMBER_of_regular_periods
1939 ---------------------------------------------
1940 debug_info := 'Get total_percentage';
1941 FOR i in 1..(P_number_of_regular_periods - 1) LOOP
1942
1943 C_percentage_factor := C_percentage_factor *
1944 (1+ (NVL(P_increment_percent, 0)/100));
1945 C_total_percentage := C_total_percentage + C_percentage_factor;
1946
1947 END LOOP;
1948
1949 ---------------------------------------------
1950 -- Get control total
1951 ---------------------------------------------
1952 debug_info := 'Get control total';
1953 C_total := ap_utilities_pkg.ap_round_currency(
1954 (P_first_amount * C_total_percentage),P_currency_code);
1955
1956 P_control_total := C_total + NVL(P_special_payment_amount1,0) +
1957 NVL(P_special_payment_amount2,0);
1958
1959 END IF;
1960
1961
1962 EXCEPTION
1963
1964 WHEN OTHERS then
1965
1966 IF (SQLCODE <> -20001 ) then
1967 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1968 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1969 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1970 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1971 'first_period_name = '||P_first_period_name
1972 ||' P_special_period_name1 = '||P_special_period_name1
1973 ||' P_special_payment_amount1 = '||TO_CHAR(P_special_payment_amount1)
1974 ||' P_special_period_name2 = '||P_special_period_name2
1975 ||' P_special_payment_amount2 = '||TO_CHAR(P_special_payment_amount2)
1976 ||' P_number_of_regular_periods = '||TO_CHAR(P_number_of_regular_periods)
1977 ||' P_amount_source_flag = '||P_amount_source_flag
1978 ||' P_increment_percent = '||TO_CHAR(P_increment_percent)
1979 ||' P_currency_code = '||P_currency_code);
1980 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1981 END IF;
1982
1983 APP_EXCEPTION.RAISE_EXCEPTION;
1984
1985 END ap_get_first_amount;
1986
1987
1988 Procedure Insert_Invoice_Line(
1989 P_Invoice_Id IN NUMBER,
1990 P_Invoice_line_number OUT NOCOPY NUMBER,
1991 P_Invoice_Date IN DATE,
1992 P_Line_Type_Lookup_Code IN VARCHAR2,
1993 P_description IN VARCHAR2,
1994 P_Po_Line_Location_Id IN NUMBER DEFAULT NULL,
1995 P_Amount IN NUMBER,
1996 P_Quantity_Invoiced IN NUMBER DEFAULT NULL,
1997 P_Unit_Price IN NUMBER DEFAULT NULL,
1998 P_set_of_books_id IN NUMBER,
1999 P_exchange_rate IN NUMBER,
2000 P_base_currency_code IN VARCHAR2,
2001 P_accounting_date IN DATE,
2002 P_awt_group_id IN NUMBER,
2003 P_pay_awt_group_id IN NUMBER,--bug6639866
2004 P_gl_period_name IN VARCHAR2,
2005 P_income_tax_region IN VARCHAR2,
2006 P_transfer_flag IN VARCHAR2,
2007 P_approval_workflow_flag IN VARCHAR2,
2008 P_inventory_org_id IN NUMBER,
2009 P_asset_bt_code IN VARCHAR2,
2010 P_Tax_Control_Amount IN NUMBER DEFAULT NULL,
2011 P_Primary_Intended_Use IN VARCHAR2 DEFAULT NULL,
2012 P_Product_Fisc_Classification IN VARCHAR2 DEFAULT NULL,
2013 P_User_Defined_Fisc_Class IN VARCHAR2 DEFAULT NULL,
2014 P_Trx_Business_Category IN VARCHAR2 DEFAULT NULL,
2015 P_retained_amount IN NUMBER DEFAULT NULL, /*bug 5228301 */
2016 P_match_type IN VARCHAR2, /*bug 5228301 */
2017 P_tax_classification_code IN VARCHAR2,
2018 P_PRODUCT_TYPE IN VARCHAR2, --Bug#8640313
2019 P_PRODUCT_CATEGORY IN VARCHAR2, --Bug#8640313
2020 P_Calling_Sequence IN VARCHAR2) IS
2021
2022 current_calling_sequence VARCHAR2(2000);
2023 debug_info VARCHAR2(100);
2024 l_User_Id number;
2025 l_Login_Id number;
2026 l_invoice_line_number AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
2027
2028 BEGIN
2029
2030 current_calling_sequence := 'Insert_Invoice_Line<-'||P_calling_sequence;
2031
2032 l_user_id := FND_PROFILE.VALUE('USER_ID');
2033 l_login_id := FND_PROFILE.VALUE('LOGIN_ID');
2034
2035 debug_info := 'Get line NUMBER for generation of line';
2036 l_invoice_line_number := AP_INVOICES_PKG.get_max_line_number(P_invoice_id) + 1;
2037
2038 debug_info := 'Inserting Item Line Matched to a PO';
2039 INSERT INTO AP_INVOICE_LINES (
2040 INVOICE_ID,
2041 LINE_NUMBER,
2042 LINE_TYPE_LOOKUP_CODE,
2043 REQUESTER_ID,
2044 DESCRIPTION,
2045 LINE_SOURCE,
2046 ORG_ID,
2047 INVENTORY_ITEM_ID,
2048 ITEM_DESCRIPTION,
2049 SERIAL_NUMBER,
2050 MANUFACTURER,
2051 MODEL_NUMBER,
2052 GENERATE_DISTS,
2053 MATCH_TYPE,
2054 DISTRIBUTION_SET_ID,
2055 ACCOUNT_SEGMENT,
2056 BALANCING_SEGMENT,
2057 COST_CENTER_SEGMENT,
2058 OVERLAY_DIST_CODE_CONCAT,
2059 DEFAULT_DIST_CCID,
2060 PRORATE_ACROSS_ALL_ITEMS,
2061 LINE_GROUP_NUMBER,
2062 ACCOUNTING_DATE,
2063 PERIOD_NAME,
2064 DEFERRED_ACCTG_FLAG,
2065 DEF_ACCTG_START_DATE,
2066 DEF_ACCTG_END_DATE,
2067 DEF_ACCTG_NUMBER_OF_PERIODS,
2068 DEF_ACCTG_PERIOD_TYPE,
2069 SET_OF_BOOKS_ID,
2070 AMOUNT,
2071 BASE_AMOUNT,
2072 ROUNDING_AMT,
2073 QUANTITY_INVOICED,
2074 UNIT_MEAS_LOOKUP_CODE,
2075 UNIT_PRICE,
2076 WFAPPROVAL_STATUS,
2077 -- USSGL_TRANSACTION_CODE, - Bug 4277744
2078 DISCARDED_FLAG,
2079 ORIGINAL_AMOUNT,
2080 ORIGINAL_BASE_AMOUNT,
2081 ORIGINAL_ROUNDING_AMT,
2082 CANCELLED_FLAG,
2083 INCOME_TAX_REGION,
2084 TYPE_1099,
2085 STAT_AMOUNT,
2086 PREPAY_INVOICE_ID,
2087 PREPAY_LINE_NUMBER,
2088 INVOICE_INCLUDES_PREPAY_FLAG,
2089 CORRECTED_INV_ID,
2090 CORRECTED_LINE_NUMBER,
2091 PO_HEADER_ID,
2092 PO_LINE_ID,
2093 PO_RELEASE_ID,
2094 PO_LINE_LOCATION_ID,
2095 PO_DISTRIBUTION_ID,
2096 RCV_TRANSACTION_ID,
2097 FINAL_MATCH_FLAG,
2098 ASSETS_TRACKING_FLAG,
2099 ASSET_BOOK_TYPE_CODE,
2100 ASSET_CATEGORY_ID,
2101 PROJECT_ID,
2102 TASK_ID,
2103 EXPENDITURE_TYPE,
2104 EXPENDITURE_ITEM_DATE,
2105 EXPENDITURE_ORGANIZATION_ID,
2106 PA_QUANTITY,
2107 PA_CC_AR_INVOICE_ID,
2108 PA_CC_AR_INVOICE_LINE_NUM,
2109 PA_CC_PROCESSED_CODE,
2110 AWARD_ID,
2111 AWT_GROUP_ID,
2112 PAY_AWT_GROUP_ID,--bug6639866
2113 REFERENCE_1,
2114 REFERENCE_2,
2115 RECEIPT_VERIFIED_FLAG,
2116 RECEIPT_REQUIRED_FLAG,
2117 RECEIPT_MISSING_FLAG,
2118 JUSTIFICATION,
2119 EXPENSE_GROUP,
2120 START_EXPENSE_DATE,
2121 END_EXPENSE_DATE,
2122 RECEIPT_CURRENCY_CODE,
2123 RECEIPT_CONVERSION_RATE,
2124 RECEIPT_CURRENCY_AMOUNT,
2125 DAILY_AMOUNT,
2126 WEB_PARAMETER_ID,
2127 ADJUSTMENT_REASON,
2128 MERCHANT_DOCUMENT_NUMBER,
2129 MERCHANT_NAME,
2130 MERCHANT_REFERENCE,
2131 MERCHANT_TAX_REG_NUMBER,
2132 MERCHANT_TAXPAYER_ID,
2133 COUNTRY_OF_SUPPLY,
2134 CREDIT_CARD_TRX_ID,
2135 COMPANY_PREPAID_INVOICE_ID,
2136 CC_REVERSAL_FLAG,
2137 ATTRIBUTE_CATEGORY,
2138 ATTRIBUTE1,
2139 ATTRIBUTE2,
2140 ATTRIBUTE3,
2141 ATTRIBUTE4,
2142 ATTRIBUTE5,
2143 ATTRIBUTE6,
2144 ATTRIBUTE7,
2145 ATTRIBUTE8,
2146 ATTRIBUTE9,
2147 ATTRIBUTE10,
2148 ATTRIBUTE11,
2149 ATTRIBUTE12,
2150 ATTRIBUTE13,
2151 ATTRIBUTE14,
2152 ATTRIBUTE15,
2153 /* GLOBAL_ATTRIBUTE_CATEGORY,
2154 GLOBAL_ATTRIBUTE1,
2155 GLOBAL_ATTRIBUTE2,
2156 GLOBAL_ATTRIBUTE3,
2157 GLOBAL_ATTRIBUTE4,
2158 GLOBAL_ATTRIBUTE5,
2159 GLOBAL_ATTRIBUTE6,
2160 GLOBAL_ATTRIBUTE7,
2161 GLOBAL_ATTRIBUTE8,
2162 GLOBAL_ATTRIBUTE9,
2163 GLOBAL_ATTRIBUTE10,
2164 GLOBAL_ATTRIBUTE11,
2165 GLOBAL_ATTRIBUTE12,
2166 GLOBAL_ATTRIBUTE13,
2167 GLOBAL_ATTRIBUTE14,
2168 GLOBAL_ATTRIBUTE15,
2169 GLOBAL_ATTRIBUTE16,
2170 GLOBAL_ATTRIBUTE17,
2171 GLOBAL_ATTRIBUTE18,
2172 GLOBAL_ATTRIBUTE19,
2173 GLOBAL_ATTRIBUTE20, */
2174 CREATION_DATE,
2175 CREATED_BY,
2176 LAST_UPDATED_BY,
2177 LAST_UPDATE_DATE,
2178 LAST_UPDATE_LOGIN,
2179 PROGRAM_APPLICATION_ID,
2180 PROGRAM_ID,
2181 PROGRAM_UPDATE_DATE,
2182 REQUEST_ID,
2183 CONTROL_AMOUNT,
2184 PRIMARY_INTENDED_USE,
2185 PRODUCT_FISC_CLASSIFICATION,
2186 USER_DEFINED_FISC_CLASS,
2187 TRX_BUSINESS_CATEGORY,
2188 SHIP_TO_LOCATION_ID,
2189 RETAINED_AMOUNT, /*bug 5228301 */
2190 RETAINED_AMOUNT_REMAINING,
2191 TAX_CLASSIFICATION_CODE,
2192 PRODUCT_TYPE, --Bug#8640313
2193 PRODUCT_CATEGORY) --Bug#8640313
2194 SELECT P_INVOICE_ID, --invoice_id
2195 L_INVOICE_LINE_NUMBER, --invoice_line_number
2196 P_LINE_TYPE_LOOKUP_CODE, --line_type_lookup_code
2197 NULL, --requester_id
2198 P_DESCRIPTION, --description
2199 'AUTO INVOICE CREATION', --line_source
2200 PLL.ORG_ID, --org_id
2201 PLL.ITEM_ID, --inventory_item_id
2202 PLL.ITEM_DESCRIPTION, --item_description
2203 NULL, --serial_number
2204 NULL, --manufacturer
2205 NULL, --model_number
2206 'D', --generate_dists
2207 P_Match_Type, --match_type /* bug 5228301 */
2208 NULL, --distribution_set_id
2209 NULL, --account_segment
2210 NULL, --balancing_segment
2211 NULL, --cost_center_segment
2212 NULL, --overlay_dist_code_concat
2213 --Bug6965650
2214 NULL, --default_dist_ccid
2215 'N', --prorate_across_all_items
2216 NULL, --line_group_number
2217 P_ACCOUNTING_DATE, --accounting_date
2218 P_GL_PERIOD_NAME, --period_name
2219 'N', --deferred_acctg_flag
2220 NULL, --def_acctg_start_date
2221 NULL, --def_acctg_end_date
2222 NULL, --def_acctg_number_of_periods
2223 NULL, --def_acctg_period_type
2224 P_SET_OF_BOOKS_ID, --set_of_books_id
2225 P_AMOUNT, --amount
2226 AP_UTILITIES_PKG.Ap_Round_Currency(
2227 NVL(P_AMOUNT, 0) * P_EXCHANGE_RATE,
2228 P_BASE_CURRENCY_CODE), --base_amount
2229 NULL, --rounding_amount
2230 P_QUANTITY_INVOICED, --quantity_invoiced
2231 PLL.UNIT_MEAS_LOOKUP_CODE, --unit_meas_lookup_code
2232 P_UNIT_PRICE, --unit_price
2233 decode(P_approval_workflow_flag,'Y'
2234 ,'REQUIRED','NOT REQUIRED'),--wf_approval_status
2235 -- Removed for bug 4277744
2236 -- PLL.USSGL_TRANSACTION_CODE, --ussgl_transaction_code
2237 'N', --discarded_flag
2238 NULL, --original_amount
2239 NULL, --original_base_amount
2240 NULL, --original_rounding_amt
2241 'N', --cancelled_flag
2242 P_INCOME_TAX_REGION, --income_tax_region
2243 PLL.TYPE_1099, --type_1099
2244 NULL, --stat_amount
2245 NULL, --prepay_invoice_id
2246 NULL, --prepay_line_number
2247 NULL, --invoice_includes_prepay_flag
2248 NULL, --corrected_inv_id
2249 NULL, --corrected_line_number
2250 PLL.PO_HEADER_ID, --po_header_id
2251 PLL.PO_LINE_ID, --po_line_id
2252 PLL.PO_RELEASE_ID, --po_release_id
2253 PLL.LINE_LOCATION_ID, --po_line_location_id
2254 NULL, --po_distribution_id
2255 NULL, --rcv_transaction_id
2256 'N', --final_match_flag
2257 'N', --assets_tracking_flag
2258 P_asset_bt_code, --asset_book_type_code
2259 MSI.ASSET_CATEGORY_ID, --asset_category_id
2260 NULL, --project_id
2261 NULL, --task_id
2262 NULL, --expenditure_type
2263 NULL, --expenditure_item_date
2264 NULL, --expenditure_organization_id
2265 NULL, --pa_quantity
2266 NULL, --pa_cc_ar_invoice_id
2267 NULL, --pa_cc_ar_invoice_line_num
2268 NULL, --pa_cc_processed_code
2269 NULL, --award_id
2270 P_AWT_GROUP_ID, --awt_group_id
2271 P_PAY_AWT_GROUP_ID, --pay_awt_group_id--bug6639866
2272 NULL, --reference_1
2273 NULL, --reference_2
2274 NULL, --receipt_verified_flag
2275 NULL, --receipt_required_flag
2276 NULL, --receipt_missing_flag
2277 NULL, --justification
2278 NULL, --expense_group
2279 NULL, --start_expense_date
2280 NULL, --end_expense_date
2281 NULL, --receipt_currency_code
2282 NULL, --receipt_conversion_rate
2283 NULL, --receipt_currency_amount
2284 NULL, --daily_amount
2285 NULL, --web_parameter_id
2286 NULL, --adjustment_reason
2287 NULL, --merchant_document_number
2288 NULL, --merchant_name
2289 NULL, --merchant_reference
2290 NULL, --merchant_tax_reg_number
2291 NULL, --merchant_taxpayer_id
2292 NULL, --country_of_supply
2293 NULL, --credit_card_trx_id
2294 NULL, --company_prepaid_invoice_id
2295 NULL, --cc_reversal_flag
2296 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute_category),''),--attribute_category
2297 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute1),''), --attribute1
2298 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute2),''), --attribute2
2299 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute3),''), --attribute3
2300 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute4),''), --attribute4
2301 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute5),''), --attribute5
2302 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute6),''), --attribute6
2303 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute7),''), --attribute7
2304 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute8),''), --attribute8
2305 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute9),''), --attribute9
2306 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute10),''), --attribute10
2307 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute11),''), --attribute11
2308 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute12),''), --attribute12
2309 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute13),''), --attribute13
2310 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute14),''), --attribute14
2311 NVL(DECODE(p_transfer_flag,'Y',PLL.attribute15),''), --attribute15
2312 /* p_GLOBAL_ATTRIBUTE_CATEGORY, --global_attribute_category
2313 p_GLOBAL_ATTRIBUTE1, --global_attribute1
2314 p_GLOBAL_ATTRIBUTE2, --global_attribute2
2315 p_GLOBAL_ATTRIBUTE3, --global_attribute3
2316 p_GLOBAL_ATTRIBUTE4, --global_attribute4
2317 p_GLOBAL_ATTRIBUTE5, --global_attribute5
2318 p_GLOBAL_ATTRIBUTE6, --global_attribute6
2319 p_GLOBAL_ATTRIBUTE7, --global_attribute7
2320 p_GLOBAL_ATTRIBUTE8, --global_attribute8
2321 p_GLOBAL_ATTRIBUTE9, --global_attribute9
2322 p_GLOBAL_ATTRIBUTE10, --global_attribute10
2323 p_GLOBAL_ATTRIBUTE11, --global_attribute11
2324 p_GLOBAL_ATTRIBUTE12, --global_attribute12
2325 p_GLOBAL_ATTRIBUTE13, --global_attribute13
2326 p_GLOBAL_ATTRIBUTE14, --global_attribute14
2327 p_GLOBAL_ATTRIBUTE15, --global_attribute15
2328 p_GLOBAL_ATTRIBUTE16, --global_attribute16
2329 p_GLOBAL_ATTRIBUTE17, --global_attribute17
2330 p_GLOBAL_ATTRIBUTE18, --global_attribute18
2331 p_GLOBAL_ATTRIBUTE19, --global_attribute19
2332 p_GLOBAL_ATTRIBUTE20, */ --global_attribute20
2333 SYSDATE, --creation_date
2334 l_USER_ID, --created_by
2335 l_USER_ID, --last_update_by
2336 SYSDATE, --last_update_date
2337 l_LOGIN_ID, --last_update_login
2338 NULL, --program_application_id
2339 NULL, --program_id
2340 NULL, --program_update_date
2341 NULL, --request_id
2342 P_TAX_CONTROL_AMOUNT, --control_amount,
2343 P_PRIMARY_INTENDED_USE, --primary_intended_use
2344 P_PRODUCT_FISC_CLASSIFICATION, --product_fisc_classification
2345 P_USER_DEFINED_FISC_CLASS, --user_defined_fisc_class
2346 P_TRX_BUSINESS_CATEGORY, --trx_business_category
2347 PLL.SHIP_TO_LOCATION_ID, --ship_to_location_id
2348 p_retained_amount, --retained_amount /* bug 5228301 */
2349 (-1)*p_retained_amount, --retained_amount_reamining
2350 p_tax_classification_code, --tax_classification_code
2351 --Added below 2 columns forBug#8640313
2352 P_PRODUCT_TYPE, --Product_type
2353 P_PRODUCT_CATEGORY --Product_category
2354 FROM PO_LINE_LOCATIONS_AP_V PLL,
2355 MTL_SYSTEM_ITEMS MSI
2356 WHERE PLL.LINE_LOCATION_ID = P_PO_LINE_LOCATION_ID
2357 AND MSI.INVENTORY_ITEM_ID(+) = PLL.ITEM_ID
2358 AND MSI.ORGANIZATION_ID(+) = P_INVENTORY_ORG_ID;
2359 --
2360 P_invoice_line_number := l_invoice_line_number;
2361 --
2362 EXCEPTION
2363
2364 WHEN OTHERS then
2365 IF ((SQLCODE <> -20001) OR ((SQLCODE <> -20002))) then
2366 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2367 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2368 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2369 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2370 END IF;
2371 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2372 ' Invoice_line_Number = '||TO_CHAR(l_invoice_line_number)
2373 ||' Asset_bt_code = '||P_asset_bt_code
2374 ||' Transfer_flag = '||p_transfer_flag
2375 ||' Approval_wf_flag = '||P_approval_workflow_flag
2376 ||' Amount = '||TO_CHAR(P_Amount)
2377 ||' Quantity_Invoiced = '||TO_CHAR(P_Quantity_Invoiced)
2378 ||' Unit_Price = '||to_char(P_unit_price));
2379 APP_EXCEPTION.RAISE_EXCEPTION;
2380 End Insert_Invoice_Line;
2381
2382
2383 Procedure Insert_Invoice_Line_Dset(
2384 P_invoice_id IN NUMBER,
2385 P_line_amount IN NUMBER,
2386 P_description IN VARCHAR2,
2387 P_distribution_set_id IN NUMBER,
2388 P_requester_id IN NUMBER,
2389 P_set_of_books_id IN NUMBER,
2390 P_exchange_rate IN NUMBER,
2391 P_base_currency_code IN VARCHAR2,
2392 P_accounting_date IN DATE,
2393 P_gl_period_name IN VARCHAR2,
2394 P_org_id IN NUMBER,
2395 P_item_description IN VARCHAR2,
2396 P_manufacturer IN VARCHAR2,
2397 P_model_number IN VARCHAR2,
2398 P_approval_workflow_flag IN VARCHAR2,
2399 -- P_ussgl_txn_code IN VARCHAR2, - Bug 4277744
2400 P_income_tax_region IN VARCHAR2,
2401 P_type_1099 IN VARCHAR2,
2402 P_asset_bt_code IN VARCHAR2,
2403 P_awt_group_id IN NUMBER,
2404 P_pay_awt_group_id IN NUMBER,--bug6639866
2405 P_ship_to_location_id IN NUMBER,
2406 P_primary_intended_use IN VARCHAR2,
2407 P_product_fisc_classification IN VARCHAR2,
2408 P_trx_business_category IN VARCHAR2,
2409 P_user_defined_fisc_class IN VARCHAR2,
2410 P_tax_classification_code IN VARCHAR2,
2411 P_PRODUCT_TYPE IN VARCHAR2, --Bug#8640313
2412 P_PRODUCT_CATEGORY IN VARCHAR2, --Bug#8640313
2413 P_calling_sequence IN VARCHAR2
2414 ) IS
2415
2416 l_asset_book_count NUMBER;
2417 l_inv_line_asset_bt_code NUMBER;
2418 l_inv_line_asset_category_id NUMBER;
2419 l_base_line_amount NUMBER;
2420 l_invoice_line_number AP_INVOICE_LINES.LINE_NUMBER%TYPE;
2421 l_dist_set_percent_number NUMBER := 0;
2422 l_dist_set_description AP_DISTRIBUTION_SETS.DESCRIPTION%TYPE;
2423 l_dist_set_attribute_category AP_DISTRIBUTION_SETS.ATTRIBUTE_CATEGORY%TYPE;
2424 l_dist_set_attribute1 AP_DISTRIBUTION_SETS.ATTRIBUTE1%TYPE;
2425 l_dist_set_attribute2 AP_DISTRIBUTION_SETS.ATTRIBUTE2%TYPE;
2426 l_dist_set_attribute3 AP_DISTRIBUTION_SETS.ATTRIBUTE3%TYPE;
2427 l_dist_set_attribute4 AP_DISTRIBUTION_SETS.ATTRIBUTE4%TYPE;
2428 l_dist_set_attribute5 AP_DISTRIBUTION_SETS.ATTRIBUTE5%TYPE;
2429 l_dist_set_attribute6 AP_DISTRIBUTION_SETS.ATTRIBUTE6%TYPE;
2430 l_dist_set_attribute7 AP_DISTRIBUTION_SETS.ATTRIBUTE7%TYPE;
2431 l_dist_set_attribute8 AP_DISTRIBUTION_SETS.ATTRIBUTE8%TYPE;
2432 l_dist_set_attribute9 AP_DISTRIBUTION_SETS.ATTRIBUTE9%TYPE;
2433 l_dist_set_attribute10 AP_DISTRIBUTION_SETS.ATTRIBUTE10%TYPE;
2434 l_dist_set_attribute11 AP_DISTRIBUTION_SETS.ATTRIBUTE11%TYPE;
2435 l_dist_set_attribute12 AP_DISTRIBUTION_SETS.ATTRIBUTE12%TYPE;
2436 l_dist_set_attribute13 AP_DISTRIBUTION_SETS.ATTRIBUTE13%TYPE;
2437 l_dist_set_attribute14 AP_DISTRIBUTION_SETS.ATTRIBUTE14%TYPE;
2438 l_dist_set_attribute15 AP_DISTRIBUTION_SETS.ATTRIBUTE15%TYPE;
2439 l_inactive_date DATE;
2440
2441 debug_info VARCHAR2(1000);
2442 current_calling_sequence VARCHAR2(2000);
2443
2444 BEGIN
2445
2446 current_calling_sequence := p_calling_sequence || ' -> Insert_Invoice_Line_Dset';
2447 --------------------------------------------------------------
2448 -- For the distribution set, obtain information required for
2449 -- validation and defaulting. Also verify that the distribution set
2450 -- is not inactive.
2451 --------------------------------------------------------------
2452 debug_info := 'Get total percent for distribution set';
2453 BEGIN
2454 SELECT total_percent_distribution,
2455 description,
2456 attribute_category,
2457 attribute1,
2458 attribute2,
2459 attribute3,
2460 attribute4,
2461 attribute5,
2462 attribute6,
2463 attribute7,
2464 attribute8,
2465 attribute9,
2466 attribute10,
2467 attribute11,
2468 attribute12,
2469 attribute13,
2470 attribute14,
2471 attribute15,
2472 inactive_date
2473 INTO
2474 l_dist_set_percent_number,
2475 l_dist_set_description,
2476 l_dist_set_attribute_category,
2477 l_dist_set_attribute1,
2478 l_dist_set_attribute2,
2479 l_dist_set_attribute3,
2480 l_dist_set_attribute4,
2481 l_dist_set_attribute5,
2482 l_dist_set_attribute6,
2483 l_dist_set_attribute7,
2484 l_dist_set_attribute8,
2485 l_dist_set_attribute9,
2486 l_dist_set_attribute10,
2487 l_dist_set_attribute11,
2488 l_dist_set_attribute12,
2489 l_dist_set_attribute13,
2490 l_dist_set_attribute14,
2491 l_dist_set_attribute15,
2492 l_inactive_date
2493 FROM ap_distribution_sets
2494 WHERE distribution_set_id = p_distribution_set_id;
2495
2496 IF (nvl(l_inactive_date, trunc(sysdate) + 1) <= trunc(sysdate)) THEN
2497 FND_MESSAGE.SET_NAME('SQLAP','AP_VEN_DIST_SET_INVALID');
2498 APP_EXCEPTION.RAISE_EXCEPTION;
2499 END IF;
2500
2501 IF (l_dist_set_percent_number <> 100) then
2502 FND_MESSAGE.SET_NAME('SQLAP','AP_CANT_USE_SKELETON_DIST_SET');
2503 APP_EXCEPTION.RAISE_EXCEPTION;
2504 END IF;
2505
2506 EXCEPTION
2507 WHEN NO_DATA_FOUND THEN
2508 Debug_info := debug_info || ': Cannot read Dist Set';
2509 IF (SQLCODE <> -20001) THEN
2510 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2511 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2512 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2513 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(p_invoice_id));
2514 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
2515 END IF;
2516 APP_EXCEPTION.RAISE_EXCEPTION;
2517 END;
2518
2519
2520 BEGIN
2521 debug_info := 'Get Asset Book count';
2522 SELECT count(*)
2523 INTO l_asset_book_count
2524 FROM fa_book_controls bc
2525 WHERE bc.book_class = 'CORPORATE'
2526 AND bc.set_of_books_id = p_set_of_books_id
2527 AND bc.date_ineffective is null;
2528
2529 IF (l_asset_book_count = 1) then
2530 SELECT bc.book_type_code
2531 INTO l_inv_line_asset_bt_code
2532 FROM fa_book_controls bc
2533 WHERE bc.set_of_books_id = p_set_of_books_id
2534 AND bc.date_ineffective is null;
2535
2536 ELSE
2537
2538 l_inv_line_asset_bt_code := NULL;
2539
2540 END IF;
2541
2542 l_inv_line_asset_category_id := NULL;
2543
2544 EXCEPTION
2545 -- No need to error handle IF FA information not available.
2546 WHEN OTHERS THEN
2547 NULL;
2548 END;
2549
2550
2551 debug_info := 'Calculate base_line_amount';
2552 l_base_line_amount := AP_UTILITIES_PKG.Ap_Round_Currency(
2553 NVL(p_line_amount,0) * p_exchange_rate ,
2554 p_base_currency_code);
2555
2556 l_invoice_line_number := ap_invoices_pkg.get_max_line_number(p_invoice_id)+1;
2557
2558 debug_info := 'Insert line into ap_invoice_lines';
2559 BEGIN
2560 INSERT INTO ap_invoice_lines(
2561 invoice_id,
2562 line_number,
2563 line_type_lookup_code,
2564 requester_id,
2565 description,
2566 line_source,
2567 org_id,
2568 inventory_item_id,
2569 item_description,
2570 serial_number,
2571 manufacturer,
2572 model_number,
2573 warranty_number,
2574 generate_dists,
2575 match_type,
2576 distribution_set_id,
2577 account_segment,
2578 balancing_segment,
2579 cost_center_segment,
2580 overlay_dist_code_concat,
2581 default_dist_ccid,
2582 prorate_across_all_items,
2583 line_group_number,
2584 accounting_date,
2585 period_name,
2586 deferred_acctg_flag,
2587 def_acctg_start_date,
2588 def_acctg_end_date,
2589 def_acctg_number_of_periods,
2590 def_acctg_period_type,
2591 set_of_books_id,
2592 amount,
2593 base_amount,
2594 rounding_amt,
2595 quantity_invoiced,
2596 unit_meas_lookup_code,
2597 unit_price,
2598 wfapproval_status,
2599 -- ussgl_transaction_code, - Bug 4277744
2600 discarded_flag,
2601 original_amount,
2602 original_base_amount,
2603 original_rounding_amt,
2604 cancelled_flag,
2605 income_tax_region,
2606 type_1099,
2607 stat_amount,
2608 prepay_invoice_id,
2609 prepay_line_number,
2610 invoice_includes_prepay_flag,
2611 corrected_inv_id,
2612 corrected_line_number,
2613 po_header_id,
2614 po_line_id,
2615 po_release_id,
2616 po_line_location_id,
2617 po_distribution_id,
2618 rcv_transaction_id,
2619 final_match_flag,
2620 assets_tracking_flag,
2621 asset_book_type_code,
2622 asset_category_id,
2623 project_id,
2624 task_id,
2625 expenditure_type,
2626 expenditure_item_date,
2627 expenditure_organization_id,
2628 pa_quantity,
2629 pa_cc_ar_invoice_id,
2630 pa_cc_ar_invoice_line_num,
2631 pa_cc_processed_code,
2632 award_id,
2633 awt_group_id,
2634 pay_awt_group_id,--bug6639866
2635 reference_1,
2636 reference_2,
2637 receipt_verified_flag,
2638 receipt_required_flag,
2639 receipt_missing_flag,
2640 justification,
2641 expense_group,
2642 start_expense_date,
2643 end_expense_date,
2644 receipt_currency_code,
2645 receipt_conversion_rate,
2646 receipt_currency_amount,
2647 daily_amount,
2648 web_parameter_id,
2649 adjustment_reason,
2650 merchant_document_number,
2651 merchant_name,
2652 merchant_reference,
2653 merchant_tax_reg_number,
2654 merchant_taxpayer_id,
2655 country_of_supply,
2656 credit_card_trx_id,
2657 company_prepaid_invoice_id,
2658 cc_reversal_flag,
2659 attribute_category,
2660 attribute1,
2661 attribute2,
2662 attribute3,
2663 attribute4,
2664 attribute5,
2665 attribute6,
2666 attribute7,
2667 attribute8,
2668 attribute9,
2669 attribute10,
2670 attribute11,
2671 attribute12,
2672 attribute13,
2673 attribute14,
2674 attribute15,
2675 global_attribute_category,
2676 global_attribute1,
2677 global_attribute2,
2678 global_attribute3,
2679 global_attribute4,
2680 global_attribute5,
2681 global_attribute6,
2682 global_attribute7,
2683 global_attribute8,
2684 global_attribute9,
2685 global_attribute10,
2686 global_attribute11,
2687 global_attribute12,
2688 global_attribute13,
2689 global_attribute14,
2690 global_attribute15,
2691 global_attribute16,
2692 global_attribute17,
2693 global_attribute18,
2694 global_attribute19,
2695 global_attribute20,
2696 creation_date,
2697 created_by,
2698 last_updated_by,
2699 last_update_date,
2700 last_update_login,
2701 program_application_id,
2702 program_id,
2703 program_update_date,
2704 request_id,
2705 ship_to_location_id,
2706 primary_intended_use,
2707 product_fisc_classification,
2708 trx_business_category,
2709 user_defined_fisc_class,
2710 product_type,
2711 product_category,
2712 tax_classification_code
2713 )
2714 VALUES ( p_invoice_id, -- invoice_id
2715 l_invoice_line_number, -- line_number
2716 'ITEM', -- line_type_lookup_code
2717 p_requester_id, -- requester_id
2718 nvl(l_dist_set_description,p_description), -- description
2719 'AUTO INVOICE CREATION', -- line_source
2720 p_org_id, -- org_id
2721 NULL, -- inventory_item_id
2722 P_Item_description, -- item_description
2723 NULL, -- serial_number
2724 P_Manufacturer, -- manufacturer
2725 P_Model_Number, -- model_number
2726 NULL, -- warranty_number
2727 'Y', -- generate_dists
2728 'NOT_MATCHED', -- match_type
2729 P_distribution_set_id, -- distribution_set_id
2730 NULL, -- account_segment
2731 NULL, -- balancing_segment
2732 NULL, -- cost_center_segment
2733 NULL, -- overlay_dist_code_concat
2734 NULL, -- default_dist_ccid
2735 'N', -- prorate_across_all_items
2736 NULL, -- line_group_number
2737 P_Accounting_Date, -- accounting_date
2738 P_gl_period_name, -- period_name
2739 'N', -- deferred_acctg_flag
2740 NULL, -- def_acctg_start_date
2741 NULL, -- def_acctg_end_date
2742 NULL, -- def_acctg_number_of_periods
2743 NULL, -- def_acctg_period_type
2744 p_set_of_books_id, -- set_of_books_id
2745 P_line_amount, -- amount
2746 l_base_line_amount, -- base_amount
2747 0, -- rounding_amt
2748 NULL, -- quantity_invoiced
2749 NULL, -- unit_meas_lookup_code
2750 NULL, -- unit_price
2751 Decode(P_Approval_Workflow_Flag,'Y','REQUIRED',
2752 'NOT REQUIRED'), -- wfapproval_status
2753 -- Removed for bug 4277744
2754 -- P_ussgl_txn_code, -- ussgl_transaction_code
2755 'N', -- discarded_flag
2756 NULL, -- original_amount
2757 NULL, -- original_base_amount
2758 NULL, -- original_rounding_amt
2759 'N', -- cancelled_flag
2760 P_income_tax_region, -- income_tax_region
2761 P_type_1099, -- type_1099
2762 NULL, -- stat_amount
2763 NULL, -- prepay_invoice_id
2764 NULL, -- prepay_line_number
2765 NULL, -- invoice_includes_prepay_flag
2766 NULL, -- corrected_inv_id
2767 NULL, -- corrected_line_number
2768 NULL, -- po_header_id
2769 NULL, -- po_line_id
2770 NULL, -- po_release_id
2771 NULL, -- po_line_location_id
2772 NULL, -- po_distribution_id
2773 NULL, -- rcv_transaction_id
2774 NULL, -- final_match_flag
2775 'N', -- assets_tracking_flag
2776 P_asset_bt_code, -- asset_book_type_code,
2777 l_inv_line_asset_category_id, -- asset_category_id
2778 NULL, -- project_id
2779 NULL, -- task_id
2780 NULL, -- expenditure_type
2781 NULL, -- expenditure_item_date
2782 NULL, -- expenditure_organization_id
2783 NULL, -- pa_quantity
2784 NULL, -- pa_cc_ar_invoice_id
2785 NULL, -- pa_cc_ar_invoice_line_num
2786 NULL, -- pa_cc_processed_code
2787 NULL, -- award_id
2788 P_awt_group_id, -- awt_group_id
2789 P_Pay_Awt_Group_Id, --pay_awt_group_id--bug6639866
2790 NULL, -- reference_1
2791 NULL, -- reference_2
2792 NULL, -- receipt_verified_flag
2793 NULL, -- receipt_required_flag
2794 NULL, -- receipt_missing_flag
2795 NULL, -- justification
2796 NULL, -- expense_group
2797 NULL, -- start_expense_date
2798 NULL, -- end_expense_date
2799 NULL, -- receipt_currency_code
2800 NULL, -- receipt_conversion_rate
2801 NULL, -- receipt_currency_amount
2802 NULL, -- daily_amount
2803 NULL, -- web_parameter_id
2804 NULL, -- adjustment_reason
2805 NULL, -- merchant_document_number
2806 NULL, -- merchant_name
2807 NULL, -- merchant_reference
2808 NULL, -- merchant_tax_reg_number
2809 NULL, -- merchant_taxpayer_id
2810 NULL, -- country_of_supply
2811 NULL, -- credit_card_trx_id
2812 NULL, -- company_prepaid_invoice_id
2813 NULL, -- cc_reversal_flag
2814 l_dist_set_attribute_category, -- attribute_category
2815 l_dist_set_attribute1, -- attribute1
2816 l_dist_set_attribute2, -- attribute2
2817 l_dist_set_attribute3, -- attribute3
2818 l_dist_set_attribute4, -- attribute4
2819 l_dist_set_attribute5, -- attribute5
2820 l_dist_set_attribute6, -- attribute6
2821 l_dist_set_attribute7, -- attribute7
2822 l_dist_set_attribute8, -- attribute8
2823 l_dist_set_attribute9, -- attribute9
2824 l_dist_set_attribute10, -- attribute10
2825 l_dist_set_attribute11, -- attribute11
2826 l_dist_set_attribute12, -- attribute12
2827 l_dist_set_attribute13, -- attribute13
2828 l_dist_set_attribute14, -- attribute14
2829 l_dist_set_attribute15, -- attribute15
2830 NULL, -- global_attribute_category
2831 NULL, -- global_attribute1
2832 NULL, -- global_attribute2
2833 NULL, -- global_attribute3
2834 NULL, -- global_attribute4
2835 NULL, -- global_attribute5
2836 NULL, -- global_attribute6
2837 NULL, -- global_attribute7
2838 NULL, -- global_attribute8
2839 NULL, -- global_attribute9
2840 NULL, -- global_attribute10
2841 NULL, -- global_attribute11
2842 NULL, -- global_attribute12
2843 NULL, -- global_attribute13
2844 NULL, -- global_attribute14
2845 NULL, -- global_attribute15
2846 NULL, -- global_attribute16
2847 NULL, -- global_attribute17
2848 NULL, -- global_attribute18
2849 NULL, -- global_attribute19
2850 NULL, -- global_attribute20
2851 sysdate, -- creation_date
2852 FND_GLOBAL.user_id, -- created_by
2853 FND_GLOBAL.user_id, -- last_updated_by
2854 sysdate, -- last_update_date
2855 FND_GLOBAL.login_id, -- last_update_login
2856 NULL, -- program_application_id
2857 NULL, -- program_id
2858 NULL, -- program_update_date
2859 NULL, -- request_id
2860 P_ship_to_location_id, -- ship_to_location_id
2861 P_primary_intended_use , -- primary_intended_use
2862 P_product_fisc_classification, -- product_fisc_classification
2863 P_trx_business_category, -- trx_business_category
2864 P_user_defined_fisc_class, -- user_defined_fisc_class
2865 P_PRODUCT_TYPE, -- NULL --product_type --Bug#8640313
2866 P_PRODUCT_CATEGORY, -- NULL --product_category --Bug#8640313
2867 P_tax_classification_code -- tax_classification_code
2868 );
2869
2870 END;
2871
2872 EXCEPTION
2873 WHEN OTHERS then
2874 IF ((SQLCODE <> -20001) OR (SQLCODE <> -20002) ) then
2875 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2876 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2877 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2878 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2879 'Invoice_id = '||TO_CHAR(P_invoice_id)
2880 ||' Distribution_Set_id = '||TO_CHAR(P_distribution_set_id)
2881 ||' Line_Amount = '||TO_CHAR(p_line_amount)
2882 ||' Accounting_date = '||TO_CHAR(P_accounting_date)
2883 ||' P_awt_group_id = '||TO_CHAR(P_awt_group_id)
2884 ||' Exchange Rate = '||TO_CHAR(P_exchange_rate)
2885 ||' Set 0f books id = '||TO_CHAR(P_set_of_books_id));
2886 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2887 END IF;
2888
2889 APP_EXCEPTION.RAISE_EXCEPTION;
2890
2891 END Insert_Invoice_Line_Dset;
2892
2893
2894 END AP_RECURRING_INVOICES_PKG;