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