[Home] [Help]
PACKAGE BODY: APPS.AP_AUTO_DM_CREATION_PKG
Source
1 PACKAGE BODY AP_AUTO_DM_CREATION_PKG AS
2 /* $Header: apcrtdmb.pls 120.16.12010000.2 2009/01/29 09:06:55 ssontine ship $ */
3
4 -- Logging Infra
5 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
11 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
12 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
13 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'AP.PLSQL.AP_AUTO_DM_CREATION_PKG.';
14 G_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
15 --1-STATEMENT, 2-PROCEDURE, 3-EVENT, 4-EXCEPTION, 5-ERROR, 6-UNEXPECTED
16
17 g_log_level NUMBER;
18 g_log_enabled BOOLEAN;
19
20
21 /* Private Procedures/functions */
22
23 Procedure Get_receipt_info(
24 p_rcv_txn_id IN NUMBER,
25 p_rts_txn_id IN NUMBER,
26 p_vendor_id IN OUT NOCOPY NUMBER,
27 p_vendor_site_id IN OUT NOCOPY NUMBER,
28 p_rcv_currency_code IN OUT NOCOPY VARCHAR2,
29 p_po_shipment_id IN OUT NOCOPY NUMBER,
30 p_po_dist_id IN NUMBER,
31 p_rcv_txn_date IN OUT NOCOPY DATE,
32 p_rcv_rate IN OUT NOCOPY NUMBER,
33 p_rcv_rate_date IN OUT NOCOPY DATE,
34 p_rcv_rate_type IN OUT NOCOPY VARCHAR2,
35 p_receipt_num IN OUT NOCOPY VARCHAR2,
36 p_receipt_uom IN OUT NOCOPY VARCHAR2,
37 p_po_pay_terms_id IN OUT NOCOPY NUMBER,
38 p_quantity_billed IN OUT NOCOPY NUMBER,
39 p_rts_txn_date IN OUT NOCOPY DATE,
40 p_item_description IN OUT NOCOPY VARCHAR2,
41 --Bug:2902340
42 p_match_option IN OUT NOCOPY VARCHAR2,
43 p_org_id IN OUT NOCOPY NUMBER,
44 p_po_uom IN OUT NOCOPY VARCHAR2,
45 p_po_ccid IN OUT NOCOPY NUMBER, --bugfix:5395955
46 p_calling_sequence IN VARCHAR2);
47
48 Procedure Get_vendor_info (
49 p_vendor_id IN NUMBER,
50 p_vendor_site_id IN NUMBER,
51 p_pay_group_lookup_code IN OUT NOCOPY VARCHAR2,
52 p_accts_pay_ccid IN OUT NOCOPY NUMBER,
53 p_payment_priority IN OUT NOCOPY NUMBER,
54 p_terms_date_basis IN OUT NOCOPY VARCHAR2,
55 p_vendor_income_tax_region IN OUT NOCOPY VARCHAR2,
56 p_type_1099 IN OUT NOCOPY VARCHAR2,
57 p_allow_awt_flag IN OUT NOCOPY VARCHAR2,
58 p_awt_group_id IN OUT NOCOPY NUMBER,
59 p_excl_freight_from_disc IN OUT NOCOPY VARCHAR2,
60 p_payment_currency IN OUT NOCOPY VARCHAR2,
61 p_auto_tax_calc_flag IN OUT NOCOPY VARCHAR2, -- Bug 1971188
62 p_calling_sequence IN VARCHAR2,
63 p_party_id IN OUT nocopy NUMBER, --4552701, added party info
64 p_party_site_id IN OUT nocopy NUMBER);
65
66 Procedure Create_Invoice_batch(
67 p_receipt_num IN VARCHAR2,
68 p_inv_curr IN VARCHAR2,
69 p_inv_payment_curr IN VARCHAR2,
70 p_rts_txn_date IN DATE,
71 p_user_id IN NUMBER,
72 p_login_id IN NUMBER,
73 p_batch_id IN OUT NOCOPY NUMBER,
74 p_calling_sequence IN VARCHAR2);
75
76 Procedure Create_Invoice_Header (
77 p_vendor_site_id IN NUMBER,
78 p_vendor_id IN NUMBER,
79 p_receipt_num IN VARCHAR2,
80 p_receipt_uom IN VARCHAR2,
81 p_invoice_curr IN VARCHAR2,
82 p_inv_pay_curr IN VARCHAR2,
83 p_base_curr IN VARCHAR2,
84 p_gl_date_from_rect_flag IN VARCHAR2,
85 p_set_of_books_id IN NUMBER,
86 p_quantity IN NUMBER,
87 p_quantity_uom IN VARCHAR2,
88 p_price IN NUMBER,
89 p_quantity_billed IN NUMBER,
90 p_batch_id IN NUMBER,
91 p_payment_method IN VARCHAR2,
92 p_pay_group IN VARCHAR2,
93 p_accts_pay_ccid IN NUMBER,
94 p_excl_pay_flag IN VARCHAR2,
95 p_transaction_date IN DATE,
96 p_rts_txn_date IN DATE,
97 p_rcv_rate IN NUMBER,
98 p_rcv_rate_date IN DATE,
99 p_rcv_rate_type IN VARCHAR2,
100 p_terms_date_basis IN VARCHAR2,
101 p_terms_id IN NUMBER,
102 p_awt_group_id IN NUMBER,
103 p_user_id IN NUMBER,
104 p_login_id IN NUMBER,
105 p_invoice_id IN OUT NOCOPY NUMBER,
106 p_amount IN OUT NOCOPY NUMBER,
107 p_terms_date IN OUT NOCOPY DATE,
108 p_pay_curr_invoice_amount IN OUT NOCOPY NUMBER,
109 p_pay_cross_rate IN OUT NOCOPY NUMBER,
110 p_auto_tax_calc_flag IN VARCHAR2,
111 p_calling_sequence IN VARCHAR2,
112 p_PAYMENT_REASON_CODE IN VARCHAR2,
113 p_BANK_CHARGE_BEARER IN VARCHAR2,
114 p_DELIVERY_CHANNEL_CODE IN VARCHAR2,
115 p_SETTLEMENT_PRIORITY IN VARCHAR2,
116 p_external_bank_account_id IN NUMBER,
117 p_le_id in number,
118 p_party_id in number,
119 p_party_site_id in number,
120 p_payment_reason_comments in varchar2,
121 /* bug 5227816 */
122 p_org_id IN NUMBER,
123 p_remit_to_supplier_name IN VARCHAR2, --Start 7758980
124 p_remit_to_supplier_id IN NUMBER ,
125 p_remit_to_supplier_site IN VARCHAR2,
126 p_remit_to_supplier_site_id IN NUMBER ,
127 p_relationship_id IN NUMBER --End 7758980
128 ) ;
129
130 FUNCTION create_dm_tax (p_invoice_id IN NUMBER,
131 p_invoice_amount IN NUMBER,
132 p_error_code OUT NOCOPY VARCHAR2,
133 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN;
134
135 /*-------------------------------------------------------------------------
136 Main Public Function : Create_DM
137 --------------------------------------------------------------------------
138
139 p_rcv_txn_id : The transaction_id for which the RTS is issued (should always
140 be the id of the RECEIVE transaction)
141 p_rts_txn_id : The transaction_id of the RETURN (RTS) Trasnaction itself
142 p_po_dist_id : If the Return is done against a delivery and the
143 po_distribution_id is known. If this is null, the quantity
144 will be prorated across the po distributions.
145 p_quantity : The quantity returned. Please note that the quantity should be
146 in the same UOM as the Receive Transaction, because we are
147 matching against that transaction. The quantity should be
148 negative.
149 p_qty_uom : The UOM the quantity is in.
150 p_unit_price : The price at which the goods are returned. This price will be
151 the same as the PO price but should be passed in terms of
152 x_qty_uom. The quantity and unit_price are used to get the
153 amount and these 2 should correspondto the same UOM. The unit
154 price should be positive.
155 p_user_id : AOL User Id from the Form
156 p_login_id : AOL Login Id from the form
157 p_calling_seq: The name of the module calling this function. Used for exception
158 handling
159
160
161 This procedure returns a Boolean value of TRUE when it completes sucessfully
162 and will return a value of FALSE when either a known exception or an unhandled
163 exception occurs. The Oracle error is stored on the message stack when an
164 unhandled exception occures. a meaningful error is stored when a known
165 exception occurs.
166 --------------------------------------------------------------------------*/
167
168 Function Create_DM (
169 p_rcv_txn_id IN NUMBER,
170 p_rts_txn_id IN NUMBER,
171 p_po_dist_id IN NUMBER,
172 p_quantity IN NUMBER,
173 p_qty_uom IN VARCHAR2,
174 p_unit_price IN NUMBER,
175 p_user_id IN NUMBER,
176 p_login_id IN NUMBER,
177 p_calling_sequence IN VARCHAR2)
178 RETURN BOOLEAN IS
179
180 l_vendor_id rcv_transactions.vendor_id%TYPE;
181 l_vendor_site_id rcv_transactions.vendor_site_id%TYPE;
182 l_rcv_currency_code rcv_transactions.currency_code%TYPE;
183 l_po_shipment_id rcv_transactions.po_line_location_id%TYPE;
184 l_rcv_txn_date rcv_transactions.transaction_date%TYPE;
185 l_rcv_rate rcv_transactions.currency_conversion_rate%TYPE;
186 l_rcv_rate_date rcv_transactions.currency_conversion_date%TYPE;
187 l_rcv_rate_type rcv_transactions.currency_conversion_type%TYPE;
188 l_receipt_num rcv_shipment_headers.receipt_num%TYPE;
189 l_receipt_uom rcv_shipment_lines.unit_of_measure%TYPE;
190 l_po_pay_terms_id po_line_locations.terms_id%TYPE;
191 l_set_of_books_id ap_system_parameters.set_of_books_id%TYPE;
192 l_base_currency_code ap_system_parameters.base_currency_code%TYPE;
193 l_batch_control_flag ap_system_parameters.batch_control_flag%TYPE;
194 l_gl_date_from_rect_flag ap_system_parameters.gl_date_from_receipt_flag%TYPE;
195 l_pay_group_lookup_code po_vendor_sites.pay_group_lookup_code%TYPE;
196 l_accts_pay_ccid po_vendor_sites.accts_pay_code_combination_id%TYPE;
197
198 --4552701, added the fields below
199 l_payment_method_code varchar2(30);
200 l_exclusive_payment_flag varchar2(1);
201 l_party_id number;
202 l_party_site_id number;
203 l_le_id number;
204 l_PAYMENT_REASON varchar2(80);
205 l_BANK_CHARGE_BEARER_DSP varchar2(80);
206 l_DELIVERY_CHANNEL varchar2(80);
207 l_SETTLEMENT_PRIORITY_DSP varchar2(80);
208 l_bank_account_num varchar2(100);
209 l_bank_account_name varchar2(80);
210 l_bank_branch_name varchar2(360);
211 l_bank_branch_num varchar2(30);
212 l_bank_name varchar2(360);
213 l_bank_number varchar2(30);
214 l_PAYMENT_REASON_CODE varchar2(30);
215 l_BANK_CHARGE_BEARER varchar2(30);
216 l_DELIVERY_CHANNEL_CODE varchar2(30);
217 l_SETTLEMENT_PRIORITY varchar2(30);
218 l_IBY_PAYMENT_METHOD varchar2(80);
219 l_external_bank_account_id number;
220
221 --4874927
222 l_payment_reason_comments varchar2(240);
223
224 l_payment_priority po_vendor_sites.payment_priority%TYPE;
225 l_terms_date_basis po_vendor_sites.terms_date_basis%TYPE;
226 l_vendor_income_tax_region po_vendor_sites.state%TYPE;
227 l_type_1099 po_vendors.type_1099%TYPE;
228 l_allow_awt_flag po_vendor_sites.allow_awt_flag%TYPE;
229 l_awt_group_id po_vendor_sites.awt_group_id%TYPE;
230 l_excl_freight_from_disc po_vendor_sites.exclude_freight_from_discount%TYPE;
231 l_payment_currency po_vendor_sites.payment_currency_code%TYPE;
232
233 l_inv_payment_curr po_vendor_sites.payment_currency_code%TYPE;
234 l_batch_id ap_batches.batch_id%TYPE;
235 l_rts_txn_date rcv_transactions.transaction_date%TYPE;
236 l_rcv_quantity_billed rcv_transactions.quantity_billed%TYPE;
237 l_invoice_id ap_invoices.invoice_id%TYPE;
238 l_amount ap_invoices.invoice_amount%TYPE;
239 l_terms_date DATE;
240 l_pay_curr_invoice_amount ap_invoices.pay_curr_invoice_amount%TYPE;
241 l_pay_cross_rate ap_invoices.payment_cross_rate%TYPE;
242 l_item_description po_lines.item_description%TYPE;
243
244 debug_info VARCHAR2(2000);
245 curr_calling_sequence VARCHAR2(2000);
246 l_po_dist_id NUMBER ; --Bug fix:1413339
247 l_derive_type_rcv_curr VARCHAR2(10); --Bug fix:1891850
248 l_derive_type_payment_curr VARCHAR2(10); --Bug fix:1891850
249 -- Bug fix : 1971188 - definition of variables
250 l_auto_tax_calc_flag_sys ap_system_parameters.auto_tax_calc_flag%TYPE;
251 l_auto_tax_calc_flag ap_supplier_sites_all.auto_tax_calc_flag%TYPE;
252
253 --Bug: 2902340
254 l_match_option po_line_locations.match_option%TYPE;
255 l_invoice_exists varchar2(1);
256
257 --bug:4537655
258 l_match_mode VARCHAR2(25);
259 l_org_id NUMBER;
260 l_po_uom PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE%TYPE;
261 l_dist_tab AP_MATCHING_PKG.DIST_TAB_TYPE;
262 l_error_code VARCHAR2(4000);
263 l_success BOOLEAN;
264 l_invoice_amount AP_INVOICES_ALL.INVOICE_AMOUNT%TYPE;
265 l_po_ccid NUMBER; --bugfix:5395955
266 l_remit_to_supplier_name ap_invoices_all.remit_to_supplier_name%TYPE; --Start 7758980
267 l_remit_to_supplier_id ap_invoices_all.remit_to_supplier_id%TYPE;
268 l_remit_to_supplier_site ap_invoices_all.remit_to_supplier_site%TYPE;
269 l_remit_to_supplier_site_id ap_invoices_all.remit_to_supplier_site_id%TYPE;
270 l_relationship_id ap_invoices_all.relationship_id%TYPE;
271 l_remit_party_id ap_invoices_all.party_id%TYPE;
272 l_remit_party_site_id ap_invoices_all.party_site_id%TYPE; --End 7758980
273 Begin
274
275 curr_calling_sequence := p_calling_sequence ||' <- Create_DM';
276 l_invoice_exists := 'N';
277
278 /* STEP 1:
279 Retreive all required information from rcv_transactions */
280
281 debug_info := 'Retreive receipt information';
282
283 --Bug fix:1413339
284 IF(p_po_dist_id = 0) THEN
285 l_po_dist_id := NULL;
286 ELSE
287 l_po_dist_id := p_po_dist_id;
288 END IF;
289
290 Get_receipt_info ( p_rcv_txn_id,
291 p_rts_txn_id,
292 l_vendor_id,
293 l_vendor_site_id,
294 l_rcv_currency_code,
295 l_po_shipment_id,
296 l_po_dist_id, /* Bug fix:1712542 */
297 l_rcv_txn_date,
298 l_rcv_rate,
299 l_rcv_rate_date,
300 l_rcv_rate_type,
301 l_receipt_num,
302 l_receipt_uom,
303 l_po_pay_terms_id,
304 l_rcv_quantity_billed,
305 l_rts_txn_date,
306 l_item_description,
307 l_match_option, --bug:2902340
308 l_org_id,
309 l_po_uom,
310 l_po_ccid, --bugfix:5395955
311 curr_calling_sequence);
312
313 If l_po_dist_id is NULL Then
314
315 Begin
316 Select 'Y'
317 Into l_invoice_exists
318 From ap_invoice_distributions aid,
319 po_distributions pd,
320 po_line_locations pll
321 where aid.po_distribution_id = pd.po_distribution_id
322 and pd.line_location_id = pll.line_location_id
323 and pll.line_location_id = l_po_shipment_id
324 and nvl(aid.rcv_transaction_id,-1) =
325 nvl(DECODE(l_match_option,'P',NULL,p_rcv_txn_id),-1)
326 and rownum=1;
327 EXCEPTION WHEN OTHERS THEN
331
328 l_invoice_exists := 'N';
329 return(FALSE);
330 END;
332
333 Else -- Perform this check only when l_po_dist_id is not NULL
334
335 BEGIN
336
337 SELECT 'Y'
338 INTO l_invoice_exists
339 FROM ap_invoice_distributions aid
340 WHERE aid.po_distribution_id = p_po_dist_id
341 AND nvl(aid.rcv_transaction_id,-1) =
342 nvl(DECODE(l_match_option,'P',NULL,p_rcv_txn_id),-1)
343 AND rownum = 1;
344
345 EXCEPTION WHEN OTHERS THEN
346 l_invoice_exists := 'N';
347 return(FALSE);
348 END;
349 End if; -- l_po_dist_id is NULL
350
351
352 --Bug: 2902340: added the IF condition
353 If l_invoice_exists ='Y' Then
354
355 /* STEP 2 :
356 Retreive information from ap_system_parameters */
357
358 debug_info := 'retrieve information from ap_system_parameters';
359
360 SELECT
361 set_of_books_id,
362 base_currency_code,
363 gl_date_from_receipt_flag,
364 auto_tax_calc_flag -- bug fix 1971188
365 INTO
366 l_set_of_books_id,
367 l_base_currency_code,
368 l_gl_date_from_rect_flag,
369 l_auto_tax_calc_flag_sys
370 FROM ap_system_parameters
371 WHERE org_id = l_org_id ;
372
373 --Bug :2024697
374 --Shared Services - Profile_Options : Added the following code so as get the
375 --value of invoice batch control flag from profile options instead
376 --of payables options.
377 BEGIN
378
379 FND_PROFILE.GET('AP_USE_INV_BATCH_CONTROLS',l_batch_control_flag);
380
381 EXCEPTION WHEN OTHERS THEN
382 l_batch_control_flag := 'N';
383 END ;
384
385
386 /*STEP 3:
387 Retreive vendor related information */
388
389 debug_info := 'Get vendor related information';
390
391
392 Get_vendor_info (
393 l_vendor_id,
394 l_vendor_site_id,
395 l_pay_group_lookup_code,
396 l_accts_pay_ccid,
397 l_payment_priority,
398 l_terms_date_basis,
399 l_vendor_income_tax_region,
400 l_type_1099,
401 l_allow_awt_flag,
402 l_awt_group_id,
403 l_excl_freight_from_disc,
404 l_payment_currency,
405 l_auto_tax_calc_flag, -- Bug 1971188
406 curr_calling_sequence,
407 l_party_id,
408 l_party_site_id);
409
410
411 /*STEP 4:
412 Obtain the invoice payment currency taking euro relations into
413 consideration */
414
415
416 --Bug fix:1891850
417 --If the payment currency and invoice currency on the
418 --supplier site is NULL, then we will create the debit memo
419 --in the receipt currency, which is same as PO currency.
420 IF( l_payment_currency IS NULL) THEN
421
422 l_payment_currency := l_rcv_currency_code ;
423
424 ELSE
425
426 SELECT NVL(fnd1.derive_type,'OTHER'),NVL(fnd2.derive_type,'OTHER')
427 INTO l_derive_type_rcv_curr,l_derive_type_payment_curr
428 FROM fnd_currencies fnd1,fnd_currencies fnd2
429 WHERE fnd1.currency_code = l_rcv_currency_code
430 AND fnd2.currency_code = l_payment_currency;
431
432 IF( l_payment_currency <>l_rcv_currency_code ) AND
433 l_derive_type_rcv_curr IN ('EMU','EURO') AND
434 l_derive_type_payment_curr IN ('EMU','EURO') THEN
435 NULL;
436 ELSE
437 l_payment_currency := l_rcv_currency_code ;
438 END IF;
439
440 END IF;
441
442
443 debug_info := 'get invoice payment currency';
444 --Bug 3492081
445 If (gl_currency_api.is_fixed_rate(l_payment_currency,
446 l_rcv_currency_code,
447 trunc(l_rts_txn_date)) = 'Y') then
448 l_inv_payment_curr := l_payment_currency;
449 Else
450 l_inv_payment_curr := l_rcv_currency_code;
451 End if;
452
453
454 --4552701, get le and payment attributes
455
456 ap_utilities_pkg.get_Invoice_LE(
457 p_vendor_site_id => l_vendor_site_id,
458 p_inv_liab_ccid => l_accts_pay_ccid,
459 p_org_id => l_org_id,
460 p_le_id => l_le_id);
461
462 -- Bug 7758980 Assinging values as part of Third Party Payments.
463 IBY_EXT_PAYEE_RELSHIPS_PKG.default_Ext_Payee_Relationship (
464 p_party_id => l_party_id,
465 p_supplier_site_id => l_vendor_site_id,
466 p_date => l_rts_txn_date,
467 x_remit_party_id => l_remit_party_id,
468 x_remit_supplier_site_id => l_remit_to_supplier_site_id,
469 x_relationship_id => l_relationship_id
470 );
471
472 if (l_remit_party_id is not null ) then
473 select vendor_id,vendor_name
474 into l_remit_to_supplier_id,l_remit_to_supplier_name
475 from ap_suppliers
476 where party_id = l_remit_party_id;
477 end if;
478
479 if (l_remit_to_supplier_site_id is not null) then
480 select party_site_id,vendor_site_code
481 into l_remit_party_site_id,l_remit_to_supplier_site
485
482 from ap_supplier_sites_all
483 where vendor_site_id = l_remit_to_supplier_site_id;
484 end if; --End 7758980
486 ap_invoices_pkg.get_payment_attributes(
487 p_le_id =>l_le_id,
488 p_org_id =>l_org_id,
489 p_payee_party_id =>l_remit_party_id, --Start 7758980
490 p_payee_party_site_id =>l_remit_party_site_id,
491 p_supplier_site_id =>l_remit_to_supplier_site_id,--End 7758980
492 p_payment_currency =>l_inv_payment_curr,
493 p_payment_amount =>1, --just a dummy value since this isn't known here
494 p_payment_function =>'PAYABLES_DISB',
495 p_pay_proc_trxn_type_code =>'PAYABLES_DOC',
496
497 p_PAYMENT_METHOD_CODE => l_payment_method_code,
498 p_PAYMENT_REASON_CODE => l_payment_reason_code,
499 p_BANK_CHARGE_BEARER => l_bank_charge_bearer,
500 p_DELIVERY_CHANNEL_CODE => l_delivery_channel_code,
501 p_SETTLEMENT_PRIORITY => l_settlement_priority,
502 p_PAY_ALONE => l_exclusive_payment_flag,
503 p_external_bank_account_id => l_external_bank_account_id,
504
505 p_IBY_PAYMENT_METHOD => l_IBY_PAYMENT_METHOD,
506 p_PAYMENT_REASON => l_PAYMENT_REASON,
507 p_BANK_CHARGE_BEARER_DSP => l_BANK_CHARGE_BEARER_DSP,
508 p_DELIVERY_CHANNEL => l_DELIVERY_CHANNEL,
509 p_SETTLEMENT_PRIORITY_DSP => l_SETTLEMENT_PRIORITY_DSP,
510 p_bank_account_num => l_bank_account_num,
511 p_bank_account_name => l_bank_account_name,
512 p_bank_branch_name => l_bank_branch_name,
513 p_bank_branch_num => l_bank_branch_num,
514 p_bank_name => l_bank_name,
515 p_bank_number => l_bank_number,
516 p_payment_reason_comments => l_payment_reason_comments); --4874927
517
518
519 /*STEP 4:
520 If batch control is on create an Invoice batch */
521 debug_info := 'Creating invoice batch';
522
523
524 If (l_batch_control_flag = 'Y') Then
525
526 debug_info := 'creating invoice batch';
527
528 Create_Invoice_batch(l_receipt_num,
529 l_rcv_currency_code,
530 l_inv_payment_curr,
531 l_rts_txn_date,
532 p_user_id,
533 p_login_id,
534 l_batch_id,
535 curr_calling_sequence);
536 End if;
537
538 /*STEP 4a:
539 Validate automatic calculation of tax flag. Bug fix 1971188
540 If automatic tax calculation is active <> 'N'(None)
541 must select value of the flag from vendor site info
542 Following standar behavior. */
543
544 IF l_auto_tax_calc_flag_sys = 'N' THEN
545 l_auto_tax_calc_flag := 'N';
546 END IF;
547
548
549 /*STEP 5:
550 Create the invoice header */
551
552 debug_info := 'Creating Invoice Header';
553
554 Create_Invoice_Header (
555 p_vendor_site_id =>l_vendor_site_id,
556 p_vendor_id =>l_vendor_id,
557 p_receipt_num =>l_receipt_num,
558 p_receipt_uom =>l_receipt_uom,
559 p_invoice_curr =>l_rcv_currency_code,
560 p_inv_pay_curr =>l_inv_payment_curr,
561 p_base_curr =>l_base_currency_code,
562 p_gl_date_from_rect_flag =>l_gl_date_from_rect_flag,
563 p_set_of_books_id =>l_set_of_books_id,
564 p_quantity =>p_quantity,
565 p_quantity_uom =>p_qty_uom,
566 p_price =>p_unit_price,
567 p_quantity_billed =>l_rcv_quantity_billed,
568 p_batch_id =>l_batch_id,
569 p_payment_method =>l_payment_method_code,
570 p_pay_group =>l_pay_group_lookup_code,
571 p_accts_pay_ccid =>l_accts_pay_ccid,
572 p_excl_pay_flag =>l_exclusive_payment_flag,
573 p_transaction_date =>l_rcv_txn_date,
574 p_rts_txn_date =>l_rts_txn_date,
575 p_rcv_rate =>l_rcv_rate,
576 p_rcv_rate_date =>l_rcv_rate_date,
577 p_rcv_rate_type =>l_rcv_rate_type,
578 p_terms_date_basis =>l_terms_date_basis,
579 p_terms_id =>l_po_pay_terms_id,
580 p_awt_group_id =>l_awt_group_id,
581 p_user_id =>p_user_id,
582 p_login_id =>p_login_id,
583 p_invoice_id =>l_invoice_id, --out param
584 p_amount =>l_amount, --out param
585 p_terms_date =>l_terms_date, --out param
586 p_pay_curr_invoice_amount => l_pay_curr_invoice_amount, --out
587 p_pay_cross_rate =>l_pay_cross_rate, --out
588 p_auto_tax_calc_flag =>l_auto_tax_calc_flag, -- Bug fix: 1971188
589 p_calling_sequence =>curr_calling_sequence,
590 p_PAYMENT_REASON_CODE => l_payment_reason_code, --4552701
591 p_BANK_CHARGE_BEARER => l_bank_charge_bearer,
592 p_DELIVERY_CHANNEL_CODE => l_delivery_channel_code,
593 p_SETTLEMENT_PRIORITY => l_settlement_priority,
594 p_external_bank_account_id => l_external_bank_account_id,
595 p_le_id => l_le_id,
596 p_party_id => l_party_id,
597 p_party_site_id => l_party_site_id,
598 p_payment_reason_comments => l_payment_reason_comments, --4874927
599 p_org_id => l_org_id, /*bug 5227816*/
603 p_remit_to_supplier_site_id => l_remit_to_supplier_site_id,
600 p_remit_to_supplier_name => l_remit_to_supplier_name, --Start 7758980
601 p_remit_to_supplier_id => l_remit_to_supplier_id,
602 p_remit_to_supplier_site => l_remit_to_supplier_site,
604 p_relationship_id => l_relationship_id); --End 7758980
605
606 /* STEP 6 :
607 Call the receipt matching package to create a matched distribution */
608
609 debug_info := 'Creating matched invoice distribution';
610
611 IF (l_po_dist_id IS NULL) THEN
612 l_match_mode := 'CR-PS';
613 ELSE
614 l_match_mode := 'CR-PD';
615 l_dist_tab(l_po_dist_id).po_distribution_id := l_po_dist_id;
616 l_dist_tab(l_po_dist_id).quantity_invoiced := p_quantity;
617 l_dist_tab(l_po_dist_id).unit_price := p_unit_price;
618 l_dist_tab(l_po_dist_id).amount := l_amount;
619 --bugfix:5395955
620 l_dist_tab(l_po_dist_id).dist_ccid := l_po_ccid;
621 END IF;
622
623 ap_rect_match_pkg.base_credit_rcv_match(
624 X_match_mode => l_match_mode,
625 X_invoice_id => l_invoice_id,
626 X_invoice_line_number => NULL,
627 X_Po_Line_Location_id => l_po_shipment_id,
628 X_rcv_transaction_id => p_rcv_txn_id,
629 X_Dist_Tab => l_dist_tab,
630 X_amount => l_amount,
631 X_quantity => p_quantity,
632 X_unit_price => p_unit_price,
633 X_uom_lookup_code => l_po_uom,
634 X_freight_cost_factor_id => NULL,
635 X_freight_amount => NULL,
636 X_freight_description => NULL,
637 X_misc_cost_factor_id => NULL,
638 X_misc_amount => NULL,
639 X_misc_description => NULL,
640 X_retained_amount => NULL,
641 X_calling_sequence => curr_calling_sequence);
642
643
644 /* STEP 7 :
645 Call the create_dm_tax procedure to calculate tax on Debit Memo */
646
647 debug_info := 'Calculating Tax on Debit Memo';
648
649 l_success := create_dm_tax(l_invoice_id,
650 l_amount,
651 l_error_code,
652 curr_calling_sequence);
653
654 --If Tax-Calculation Failed
655 IF NOT(l_success) THEN
656
657 debug_info := 'Call to EBTax api - Calculate Tax failed';
658
659 fnd_message.set_name('SQLAP',l_error_code);
660
661 Return(FALSE);
662
663 END IF;
664
665
666 --Bugfix:2845989 , moved the call here from step 6 to step8
667 --to be after the tax calculation.
668 /* STEP 8 :
669 Create the payment schedules for this invoice */
670
671 debug_info := 'Recalculate pay curr inv amount ';
672
673 SELECT invoice_amount
674 INTO l_invoice_amount
675 FROM ap_invoices
676 WHERE invoice_id = l_invoice_id;
677
678 /* Procced in re-calculation only when tax is calculated.
679 where in the invoice amount would have changed.
680 */
681
682 IF (l_invoice_amount <> l_amount ) THEN
683
684 IF (l_rcv_currency_code <> l_inv_payment_curr) THEN
685 l_pay_curr_invoice_amount := gl_currency_api.convert_amount(
686 l_rcv_currency_code,
687 l_inv_payment_curr,
688 l_terms_date,
689 'EMU FIXED',
690 l_invoice_amount);
691
692 END IF;
693
694 l_amount := l_invoice_amount;
695 END IF;
696
697
698 debug_info := 'Creating payment schedules';
699 ap_create_pay_scheds_pkg.ap_create_from_terms (
700 p_invoice_id =>l_invoice_id,
701 p_terms_id =>l_po_pay_terms_id,
702 p_last_updated_by =>p_user_id,
703 p_created_by =>p_user_id,
704 p_payment_priority =>l_payment_priority,
705 p_batch_id =>l_batch_id,
706 p_terms_date =>l_terms_date,
707 p_invoice_amount =>l_amount,
708 p_amount_for_discount =>l_amount,
709 p_payment_method =>l_payment_method_code, --4552701
710 p_invoice_currency =>l_rcv_currency_code,
711 p_payment_currency =>l_inv_payment_curr,
712 p_pay_curr_invoice_amount =>nvl(l_pay_curr_invoice_amount,l_amount),
713 p_payment_cross_rate => nvl(l_pay_cross_rate,1),
714 p_calling_sequence => curr_calling_sequence);
715
716 END IF; /*If l_invoice_exists */
717
721 Exception
718 -- no exceptions upto this point return true
719 Return(TRUE);
720
722 WHEN OTHERS THEN
723
724 If (SQLCODE <> -20001) Then
725 fnd_message.set_name('SQLAP','AP_DEBUG');
726 fnd_message.set_token('ERROR',SQLERRM);
727 fnd_message.set_token('CALLING_SEQUENCE',curr_calling_sequence);
728
729 fnd_message.set_token('PARAMETERS',
730 ' rcv_transaction_id = '||to_char(p_rcv_txn_id)
731 ||' po_dist_id = '||to_char(p_po_dist_id)
732 ||' quantity = '||to_char(p_quantity)
733 ||' unit price = '||to_char(p_unit_price)
734 ||' user_id = '||to_char(p_user_id)
735 ||' login_id = '||to_char(p_login_id));
736
737 fnd_message.set_token('DEBUG_INFO',debug_info);
738 End if;
739
740 Return(FALSE);
741
742 End Create_DM;
743
744 /*--------------End of Main Function------------------------------*/
745
746 /* Private Procedure/functions */
747
748 /*-------------------------------------------------------------------------
749 GET_RECEIPT_INFO : Gets relevant information from rcv_transactions and
750 po_shipments
751 --------------------------------------------------------------------------*/
752 Procedure Get_receipt_info(
753 p_rcv_txn_id IN NUMBER,
754 p_rts_txn_id IN NUMBER,
755 p_vendor_id IN OUT NOCOPY NUMBER,
756 p_vendor_site_id IN OUT NOCOPY NUMBER,
757 p_rcv_currency_code IN OUT NOCOPY VARCHAR2,
758 p_po_shipment_id IN OUT NOCOPY NUMBER,
759 p_po_dist_id IN NUMBER,/*Bug1712542*/
760 p_rcv_txn_date IN OUT NOCOPY DATE,
761 p_rcv_rate IN OUT NOCOPY NUMBER,
762 p_rcv_rate_date IN OUT NOCOPY DATE,
763 p_rcv_rate_type IN OUT NOCOPY VARCHAR2,
764 p_receipt_num IN OUT NOCOPY VARCHAR2,
765 p_receipt_uom IN OUT NOCOPY VARCHAR2,
766 p_po_pay_terms_id IN OUT NOCOPY NUMBER,
767 p_quantity_billed IN OUT NOCOPY NUMBER,
768 p_rts_txn_date IN OUT NOCOPY DATE,
769 p_item_description IN OUT NOCOPY VARCHAR2,
770 --Bugfix: 2902340
771 p_match_option IN OUT NOCOPY VARCHAR2,
772 p_org_id IN OUT NOCOPY NUMBER,
773 p_po_uom IN OUT NOCOPY VARCHAR2,
774 p_po_ccid IN OUT NOCOPY NUMBER, --BUGFIX:5395955
775 p_calling_sequence IN VARCHAR2) IS
776
777 debug_info VARCHAR2(2000);
778 curr_calling_sequence VARCHAR2(2000);
779 l_errm VARCHAR2(2000);
780
781 Begin
782
783 curr_calling_sequence := p_calling_sequence ||' <- Get_receipt_Info';
784
785
786 debug_info := 'get required information from RECEIVE transaction';
787
788 SELECT
789 rtxn.vendor_id,
790 rtxn.vendor_site_id,
791 rtxn.currency_code,
792 rtxn.po_line_location_id,
793 rtxn.transaction_date,
794 decode (rtxn.currency_conversion_type, null, null,
795 rtxn.currency_conversion_rate),
796 rtxn.currency_conversion_date,
797 rtxn.currency_conversion_type,
798 rsh.receipt_num,
799 rsl.unit_of_measure,
800 nvl( nvl(pll.terms_id,ph.terms_id),pvs.terms_id),
801 /* Bug fix: 1413309 added the pll.quantity_billed to the clause */
802 nvl(rtxn.quantity_billed,nvl(pll.quantity_billed,0)),
803 pl.item_description,
804 pll.match_option,
805 pll.org_id,
806 pl.unit_meas_lookup_code
807 INTO
808 p_vendor_id,
809 p_vendor_site_id,
810 p_rcv_currency_code,
811 p_po_shipment_id,
812 p_rcv_txn_date,
813 p_rcv_rate,
814 p_rcv_rate_date,
815 p_rcv_rate_type,
816 p_receipt_num,
817 p_receipt_uom,
818 p_po_pay_terms_id,
819 p_quantity_billed,
820 p_item_description,
821 p_match_option, --bug2902340
822 p_org_id,
823 p_po_uom
824 FROM
825 rcv_transactions rtxn,
826 rcv_shipment_headers rsh,
827 rcv_shipment_lines rsl,
828 po_headers ph,
829 po_line_locations pll,
830 po_lines pl,
831 po_vendor_sites pvs
832 WHERE rtxn.transaction_id = p_rcv_txn_id and
833 rtxn.shipment_line_id = rsl.shipment_line_id and
834 rsl.shipment_header_id = rsh.shipment_header_id and
835 rtxn.po_line_location_id = pll.line_location_id and
836 pll.po_line_id = pl.po_line_id and
837 pl.po_header_id = ph.po_header_id and
838 rtxn.vendor_site_id = pvs.vendor_site_id and
839 --Bug fix:2662505 Consigned Inventory for Supplier Project Impact
840 --Debit memo should not be created for RTS done on the receipt
841 --of a shipment,which has the consigned_flag set to Y.
842 nvl(pll.consigned_flag,'N') <> 'Y';
843
844 debug_info := 'Select information from RTS transaction';
845
846
847 SELECT transaction_date
848 INTO p_rts_txn_date
849 FROM rcv_transactions
850 WHERE transaction_id = p_rts_txn_id;
851
852 --Bugfix:5395955
853 IF (p_po_dist_id IS NOT NULL) THEN
854 SELECT code_combination_id
855 INTO p_po_ccid
856 FROM po_distributions_ap_v
857 WHERE po_distribution_id = p_po_dist_id;
858 END IF;
859
860 Exception
861 WHEN OTHERS THEN
862 If (SQLCODE <> -20001) Then
863 fnd_message.set_name('SQLAP','AP_DEBUG');
864 fnd_message.set_token('ERROR',SQLERRM);
865 fnd_message.set_token('CALLING_SEQUENCE',curr_calling_sequence);
869 End if;
866 fnd_message.set_token('PARAMETERS',
867 ' rcv_transaction_id = '||to_char(p_rcv_txn_id));
868 fnd_message.set_token('DEBUG_INFO',debug_info);
870 app_exception.raise_exception;
871
872 End Get_Receipt_Info;
873
874 /*-------------------------------------------------------------------------
875 GET_VENDOR_INFO : Get vendor and vendor site related information. Look for
876 value in the vendor site first and if null else get value from po_vendors
877 --------------------------------------------------------------------------*/
878 Procedure Get_vendor_info (
879 p_vendor_id IN NUMBER,
880 p_vendor_site_id IN NUMBER,
881 p_pay_group_lookup_code IN OUT NOCOPY VARCHAR2,
882 p_accts_pay_ccid IN OUT NOCOPY NUMBER,
883 p_payment_priority IN OUT NOCOPY NUMBER,
884 p_terms_date_basis IN OUT NOCOPY VARCHAR2,
885 p_vendor_income_tax_region IN OUT NOCOPY VARCHAR2,
886 p_type_1099 IN OUT NOCOPY VARCHAR2,
887 p_allow_awt_flag IN OUT NOCOPY VARCHAR2,
888 p_awt_group_id IN OUT NOCOPY NUMBER,
889 p_excl_freight_from_disc IN OUT NOCOPY VARCHAR2,
890 p_payment_currency IN OUT NOCOPY VARCHAR2,
891 p_auto_tax_calc_flag IN OUT NOCOPY VARCHAR2, -- Bug 1971188
892 p_calling_sequence VARCHAR2,
893 p_party_id IN OUT nocopy NUMBER, --4552701, added party info
894 p_party_site_id IN OUT nocopy NUMBER) IS
895
896 curr_calling_sequence VARCHAR2(2000);
897 debug_info VARCHAR2(2000);
898 l_default_pay_site_id NUMBER;
899
900 Begin
901
902 curr_calling_sequence:= 'p_calling_sequence '||' <- Get_vendor_Info';
903 debug_info := 'Get vendor information';
904
905 /* Bug 2226808 select the payment information based on the Alternative (default)
906 Payment site if it exists, otherwise, select the information based on the
907 Purchasing site.
908 Add the following SELECT and use l_default_pay_site_id in the WHERE for the
909 second SELECT instead of p_vendor_sit_id */
910
911 SELECT NVL(pvs.default_pay_site_id,pvs.vendor_site_id)
912 INTO l_default_pay_site_id
913 FROM po_vendor_sites pvs
914 WHERE vendor_site_id = p_vendor_site_id;
915
916 SELECT
917 nvl(pvs.pay_group_lookup_code, pv.pay_group_lookup_code),
918 pvs.accts_pay_code_combination_id,
919 pvs.payment_priority,
920 pvs.terms_date_basis,
921 pvs.state,
922 pv.type_1099,
923 pvs.allow_awt_flag,
924 pvs.awt_group_id,
925 pvs.exclude_freight_from_discount,
926 nvl(pvs.payment_currency_code, pvs.invoice_currency_code),
927 pvs.auto_tax_calc_flag, -- Bug fix 1971188
928 pv.party_id,
929 pvs.party_site_id
930 INTO
931 p_pay_group_lookup_code,
932 p_accts_pay_ccid,
933 p_payment_priority,
934 p_terms_date_basis,
935 p_vendor_income_tax_region,
936 p_type_1099,
937 p_allow_awt_flag,
938 p_awt_group_id,
939 p_excl_freight_from_disc,
940 p_payment_currency,
941 p_auto_tax_calc_flag,
942 p_party_id,
943 p_party_site_id
944 FROM po_vendors pv,
945 ap_supplier_sites pvs
946 WHERE pvs.vendor_site_id = l_default_pay_site_id and
947 pv.vendor_id = pvs.vendor_id;
948
949 Exception
950 WHEN OTHERS THEN
951 If (SQLCODE <> -20001) Then
952 fnd_message.set_name('SQLAP','AP_DEBUG');
953 fnd_message.set_token('ERROR',SQLERRM);
954 fnd_message.set_token('CALLING_SEQUENCE',curr_calling_sequence);
955 fnd_message.set_token('PARAMETERS',
956 ' vendor_site_id = '||to_char(p_vendor_site_id));
957 fnd_message.set_token('DEBUG_INFO',debug_info);
958 End if;
959 app_exception.raise_exception;
960
961 End Get_Vendor_info;
962
963 /*--------------------------------------------------------------------------
964 CREATE_INVOICE_BATCH : Creates an invoice batch . The batch_name is
965 derived from concatinating the receipt number with a database sequence
966 ---------------------------------------------------------------------------*/
967 Procedure Create_Invoice_batch(
968 p_receipt_num IN VARCHAR2,
969 p_inv_curr IN VARCHAR2,
970 p_inv_payment_curr IN VARCHAR2,
971 p_rts_txn_date IN DATE,
972 p_user_id IN NUMBER,
973 p_login_id IN NUMBER,
974 p_batch_id IN OUT NOCOPY NUMBER,
975 p_calling_sequence IN VARCHAR2) IS
976
977 debug_info VARCHAR2(2000);
978 curr_calling_sequence VARCHAR2(2000);
979 l_invoice_exists VARCHAR2(1) := 'N'; --Bug fix:1858452
980
981 Begin
982 curr_calling_sequence := p_calling_sequence ||' <- Create_Invoice_batch';
983
984 debug_info := 'Insert into ap_batches';
985
986 SELECT ap_batches_s.nextval INTO p_batch_id FROM dual;
987
988 --Bug fix:1858452
989 --Note: When matched to a PO , po_distribution_id is not populated
990 --in rcv_transactions, hence need to join using po_header_id...
991
992 /* For bug 2902340
993 Commented the following and added the same before calling all invoice
994 creation procedures.
995 BEGIN
996
997 SELECT 'Y'
998 INTO l_invoice_exists
999 FROM ap_invoice_distributions aid,
1000 rcv_shipment_headers rsh,
1001 rcv_transactions rct,
1005 AND rsh.shipment_header_id = rct.shipment_header_id
1002 po_distributions pod
1003 WHERE aid.po_distribution_id = pod.po_distribution_id
1004 AND pod.line_location_id = rct.po_line_location_id
1006 AND rsh.receipt_num = p_receipt_num
1007 AND rownum = 1;
1008
1009 EXCEPTION WHEN OTHERS THEN
1010
1011 l_invoice_exists := 'N';
1012
1013 END;
1014
1015
1016 IF(l_invoice_exists = 'Y') THEN */
1017
1018 INSERT INTO ap_batches_all (
1019 batch_id,
1020 batch_name,
1021 batch_date,
1022 invoice_currency_code,
1023 payment_currency_code,
1024 invoice_type_lookup_code,
1025 last_updated_by,
1026 last_update_date,
1027 created_by,
1028 creation_date,
1029 last_update_login)
1030 VALUES (
1031 p_batch_id,
1032 p_receipt_num||'-'||ap_batches_s1.nextval,
1033 trunc(p_rts_txn_date), --Bug 3492081
1034 p_inv_curr,
1035 p_inv_payment_curr,
1036 'DEBIT',
1037 p_user_id,
1038 sysdate,
1039 p_user_id,
1040 sysdate,
1041 p_login_id);
1042
1043 -- END IF;
1044
1045 Exception
1046 WHEN OTHERS THEN
1047 If (SQLCODE <> -20001) Then
1048 fnd_message.set_name('SQLAP','AP_DEBUG');
1049 fnd_message.set_token('ERROR',SQLERRM);
1050 fnd_message.set_token('CALLING_SEQUENCE',curr_calling_sequence);
1051 fnd_message.set_token('PARAMETERS',
1052 ' receipt_num = '||p_receipt_num
1053 ||'p_inv_curr = '||p_inv_curr
1054 ||'p_inv_payment_curr = '||p_inv_payment_curr);
1055 fnd_message.set_token('DEBUG_INFO',debug_info);
1056 End if;
1057 app_exception.raise_exception;
1058 End Create_Invoice_Batch;
1059
1060
1061 Procedure Create_Invoice_Header (
1062 p_vendor_site_id IN NUMBER,
1063 p_vendor_id IN NUMBER,
1064 p_receipt_num IN VARCHAR2,
1065 p_receipt_uom IN VARCHAR2,
1066 p_invoice_curr IN VARCHAR2,
1067 p_inv_pay_curr IN VARCHAR2,
1068 p_base_curr IN VARCHAR2,
1069 p_gl_date_from_rect_flag IN VARCHAR2,
1070 p_set_of_books_id IN NUMBER,
1071 p_quantity IN NUMBER,
1072 p_quantity_uom IN VARCHAR2,
1073 p_price IN NUMBER,
1074 p_quantity_billed IN NUMBER,
1075 p_batch_id IN NUMBER,
1076 p_payment_method IN VARCHAR2,
1077 p_pay_group IN VARCHAR2,
1078 p_accts_pay_ccid IN NUMBER,
1079 p_excl_pay_flag IN VARCHAR2,
1080 p_transaction_date IN DATE,
1081 p_rts_txn_date IN DATE,
1082 p_rcv_rate IN NUMBER,
1083 p_rcv_rate_date IN DATE,
1084 p_rcv_rate_type IN VARCHAR2,
1085 p_terms_date_basis IN VARCHAR2,
1086 p_terms_id IN NUMBER,
1087 p_awt_group_id IN NUMBER,
1088 p_user_id IN NUMBER,
1089 p_login_id IN NUMBER,
1090 p_invoice_id IN OUT NOCOPY NUMBER,
1091 p_amount IN OUT NOCOPY NUMBER,
1092 p_terms_date IN OUT NOCOPY DATE,
1093 p_pay_curr_invoice_amount IN OUT NOCOPY NUMBER,
1094 p_pay_cross_rate IN OUT NOCOPY NUMBER,
1095 p_auto_tax_calc_flag IN VARCHAR2, -- Bug fix: 1971188
1096 p_calling_sequence IN VARCHAR2,
1097 p_PAYMENT_REASON_CODE IN VARCHAR2,
1098 p_BANK_CHARGE_BEARER IN VARCHAR2,
1099 p_DELIVERY_CHANNEL_CODE IN VARCHAR2,
1100 p_SETTLEMENT_PRIORITY IN VARCHAR2,
1101 p_external_bank_account_id IN NUMBER,
1102 p_le_id IN NUMBER,
1103 p_party_id IN NUMBER,
1104 p_party_site_id IN NUMBER,
1105 p_payment_reason_comments IN VARCHAR2, --4874927
1106 /* bug 5227816 */
1107 p_org_id IN NUMBER,
1108 p_remit_to_supplier_name IN VARCHAR2, --Start 7758980
1109 p_remit_to_supplier_id IN NUMBER ,
1110 p_remit_to_supplier_site IN VARCHAR2,
1111 p_remit_to_supplier_site_id IN NUMBER ,
1112 p_relationship_id IN NUMBER --End 7758980
1113 ) IS
1114 l_gl_period VARCHAR2(15);
1115 l_gl_date DATE;
1116 l_inv_gl_date DATE;
1117 l_invoice_num ap_invoices.invoice_num%TYPE;
1118 l_terms_date DATE;
1119 l_inv_desc ap_invoices.description%TYPE;
1120 l_seq_num_profile VARCHAR2(25);
1121 l_db_seq_name fnd_document_sequences.db_sequence_name%TYPE;
1122 l_db_seq_id fnd_document_sequences.doc_sequence_id%TYPE;
1123 l_doc_seq_value NUMBER;
1124 l_invoice_amount ap_invoices.invoice_amount%TYPE;
1125 l_pay_cross_rate ap_invoices.payment_cross_rate%TYPE;
1126 l_pay_curr_invoice_amount ap_invoices.pay_curr_invoice_amount%TYPE;
1127 l_pay_cross_rate_type ap_invoices.payment_cross_rate_type%TYPE;
1128 l_pay_cross_rate_date DATE;
1129 l_valid_pay_site VARCHAR2(25);
1130 l_inv_base_amt ap_invoices.base_amount%TYPE;
1131 l_invoice_id ap_invoices.invoice_id%TYPE;
1132 l_alter_pay_site_id NUMBER;
1133 l_vendor_site_id NUMBER;
1134 l_rts_txn_le_date DATE; --Bug 3492081
1135 curr_calling_sequence VARCHAR2(2000);
1136 debug_info VARCHAR2(2000);
1137
1138 l_inv_le_date DATE;
1142 l_api_name CONSTANT VARCHAR2(200) := 'Create_Invoice_Header';
1139 l_sys_le_date DATE;
1140
1141 l_org_id ap_invoices.org_id%TYPE;
1143
1144
1145
1146 Begin
1147
1148 curr_calling_sequence := p_calling_sequence || '<- Create_Invoice_Header';
1149
1150 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1151 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1152 curr_calling_sequence);
1153 END IF;
1154
1155
1156 -- Step a1 :
1157 ------------
1158 debug_info := 'Check if there is an alternate_pay_site';
1159
1160 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1161 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1162 debug_info);
1163 END IF;
1164
1165
1166 SELECT default_pay_site_id
1167 INTO l_alter_pay_site_id
1168 FROM po_vendor_sites
1169 WHERE vendor_site_id = p_vendor_site_id;
1170
1171 if ( l_alter_pay_site_id is not NULL ) then
1172
1173 Begin
1174 select 'y'
1175 into l_valid_pay_site
1176 from po_vendor_sites
1177 where vendor_site_id = l_alter_pay_site_id
1178 and pay_site_flag = 'Y'
1179 and nvl(inactive_date, sysdate +1) > sysdate;
1180 Exception
1181 WHEN NO_DATA_FOUND Then
1182 l_valid_pay_site := 'n';
1183 END;
1184 end if;
1185
1186 if ( l_alter_pay_site_id is not NULL and l_valid_pay_site = 'y') then
1187 l_vendor_site_id := l_alter_pay_site_id;
1188 else
1189 -- Step a2 :
1190 ------------
1191 debug_info := 'no valid alternate paysite, check if valid pay site';
1192
1193 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1194 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1195 debug_info);
1196 END IF;
1197
1198
1199 Begin
1200
1201 SELECT 'y'
1202 INTO l_valid_pay_site
1203 FROM po_vendor_sites
1204 WHERE vendor_site_id = p_vendor_site_id
1205 AND pay_site_flag = 'Y'
1206 AND nvl(inactive_date, sysdate +1) > sysdate;
1207 Exception
1208 WHEN NO_DATA_FOUND Then
1209 -- It is not a valid pay site , a user freindly error message
1210 -- is set on the stack.
1211 fnd_message.set_name('SQLAP','AP_NOT_VALID_PAY_SITE');
1212 app_exception.raise_exception;
1213 End;
1214
1215 if ( l_valid_pay_site = 'y') then
1216 l_vendor_site_id := p_vendor_site_id;
1217 end if;
1218
1219 end if;
1220
1221 -- Step b :
1222 ------------
1223 debug_info := 'Check if quantity is negative';
1224
1225 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1226 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1227 debug_info);
1228 END IF;
1229
1230
1231 If (p_quantity > 0) Then
1232 fnd_message.set_name('SQLAP','AP_QUANTITY_POSITIVE');
1233 app_exception.raise_exception;
1234 End if;
1235
1236 -- step c :
1237 ------------
1238 debug_info := 'check if quantity_uom is same as receipt uom';
1239
1240 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1241 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1242 debug_info);
1243 END IF;
1244
1245
1246 If (p_receipt_uom <> p_quantity_uom) then
1247 fnd_message.set_name('SQLAP','AP_QUANTITY_UOM_INCORRECT');
1248 app_exception.raise_exception;
1249 End if;
1250
1251 -- Step d :
1252 -------------
1253 debug_info := 'check if quantity billed will be negative';
1254
1255 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1256 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1257 debug_info);
1258 END IF;
1259
1260 If (p_quantity_billed + p_quantity) < 0 Then
1261 fnd_message.set_name ('SQLAP','AP_QUANTITY_BILLED_NEGATIVE');
1262 app_exception.raise_exception;
1263 End if;
1264
1265 -- Step e :
1266 --------------
1267 debug_info := 'Determine Invoice number';
1268
1269 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1270 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1271 debug_info);
1272 END IF;
1273
1274 -- Bug 1330397
1275 -- Added a hyphen for the invoice number between receipt_num and the sequence
1276
1277 SELECT p_receipt_num||'-'||AP_INVOICES_S1.nextval
1278 INTO l_invoice_num
1279 FROM dual;
1280
1281 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1282 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1283 'Invoice Number := '||l_invoice_num);
1284 END IF;
1285
1286
1287 -- Step f :
1288 --------------
1289 /* Bug 5227816. Commented out the following line */
1290 --Bug 3492081 changed step to use l_rts_txn_le_date
1291 --l_org_id := nvl(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99);
1292
1293 --
1294 -- Bug 5233473: As discussed with Jayanta, we will not pass the truncated
1295 -- date to the LE Timezone conversion API. We will pass the date with the
1299
1296 -- time component to the LE API for invoice date case and the system date
1297 -- case.
1298 --
1300 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1301 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1302 'Operating Unit := '||to_char(p_org_id));
1303 END IF;
1304
1305
1306 --Call conversion api, setting l_ rts_txn_le_date
1307 l_rts_txn_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
1308 p_trxn_date => p_rts_txn_date
1309 ,p_ou_id => p_org_id);
1310
1311 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1312 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1313 'RTS GL Date := '||to_char(l_rts_txn_le_date,'DD-MON-RR HH:MI:SS'));
1314 END IF;
1315
1316 --Bug 3716946
1317 --Call conversion api, setting l_inv_le_date
1318 l_inv_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
1319 p_trxn_date => p_rts_txn_date
1320 ,p_ou_id => p_org_id);
1321
1322 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1323 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1324 'Invoice LE GL Date := '||to_char(l_inv_le_date,'DD-MON-RR HH:MI:SS'));
1325 END IF;
1326
1327
1328 --Call conversion api, setting l_inv_le_date
1329 l_sys_le_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Ou(
1330 p_trxn_date => sysdate
1331 ,p_ou_id => p_org_id);
1332
1333 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1334 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1335 'System LE GL Date := '||to_char(l_sys_le_date,'DD-MON-RR HH:MI:SS'));
1336 END IF;
1337
1338
1339 /* The gl_date id determined from the flag gl_date_from_receipt_flag
1340 If the flag = 'I' -- take Invoice_date
1341 = 'S' -- take System date
1342 = 'N' -- take nvl(receipt_date, invoice_date)
1343 = 'Y' -- take nvl(receipt_date, sysdate)
1344 Note here that the Invoice date is no longer the same as the receipt_date,
1345 i.e. the RETURN tranasaction_date , so case I and N are no longer the same */
1346
1347 debug_info := 'Determine invoice gl_date ';
1348
1349 If (p_gl_date_from_rect_flag = 'I') Then
1350 l_inv_gl_date := l_inv_le_date;
1351 Elsif (p_gl_date_from_rect_flag = 'N') Then
1352 l_inv_gl_date := nvl(l_rts_txn_le_date, l_inv_le_date);
1353 Elsif (p_gl_date_from_rect_flag = 'S') Then
1354 l_inv_gl_date := l_sys_le_date; --bug2213220
1355 Elsif (p_gl_date_from_rect_flag = 'Y') then
1356 l_inv_gl_date := nvl(l_rts_txn_le_date, l_sys_le_date); --Bug2213220
1357 End if;
1358
1359 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1360 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1361 'Invoice GL Date := '||to_char(l_inv_gl_date,'DD-MON-RR HH:MI:SS'));
1362 END IF;
1363
1364
1365 debug_info := 'Check if the date falls in a open or future period';
1366
1367 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1368 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1369 debug_info);
1370 END IF;
1371
1372
1373 l_gl_period := ap_utilities_pkg.get_current_gl_date(l_inv_gl_date);
1374
1375 If (l_gl_period is null) then
1376 ap_utilities_pkg.get_open_gl_date(
1377 l_inv_gl_date,
1378 l_gl_period,
1379 l_gl_date);
1380 l_inv_gl_date := l_gl_date;
1381 If (l_inv_gl_date is null) Then
1382 fnd_message.set_name('SQLAP','AP_NO_OPEN_PERIOD');
1383 app_exception.raise_exception;
1384 End if;
1385 End if;
1386
1387 -- Step g :
1388 --------------
1389 debug_info := 'Determine terms_date';
1390
1391 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1392 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1393 debug_info);
1394 END IF;
1395
1396
1400 l_terms_date := trunc(sysdate);
1397 --Bug 3492081 added trunc to date variables.
1398
1399 If (p_terms_date_basis = 'Current') Then
1401 Elsif (p_terms_date_basis = 'Goods Received') Then
1402 l_terms_date := trunc(p_rts_txn_date); -- coz good_received_date is rts_date
1403 Elsif (p_terms_date_basis = 'Invoice Received') Then
1404 l_terms_date := trunc(sysdate); -- coz invoice_received_date = sysdate
1405 -- Bug 1413331
1406 -- Added condition to check p_terms_date_basis = 'Invoice'
1407 Elsif (p_terms_date_basis = 'Invoice') Then
1408 l_terms_date := trunc(p_rts_txn_date); -- because invoice date is rts_date
1409 End if;
1410
1411 -- Step h :
1412 ---------------
1413 debug_info := 'Set Invoice description';
1414
1415 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1416 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1417 debug_info);
1418 END IF;
1419
1420
1421 fnd_message.set_name('SQLAP','AP_AUTO_DM_DESCRIPTION');
1422 l_inv_desc := fnd_message.get;
1423
1424 -- Step i :
1425 ---------------
1426 debug_info := 'Assigning document sequence ';
1427
1428 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1429 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1430 debug_info);
1431 END IF;
1432
1433
1434 -- check if sequential numbering is on
1435 fnd_profile.get('UNIQUE:SEQ_NUMBERS',l_seq_num_profile);
1436
1437 If (l_seq_num_profile IN ('P','A')) Then
1438 -- check if a sequence is assigned to DBM INV category
1439 debug_info := 'checking sequence for DBM INV';
1440
1441 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1442 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1443 debug_info);
1444 END IF;
1445
1446
1447 Begin
1448 --Bug 3492081
1449 SELECT SEQ.db_sequence_name,
1450 SEQ.doc_sequence_id
1451 INTO l_db_seq_name,
1452 l_db_seq_id
1453 FROM fnd_document_sequences SEQ,
1454 fnd_doc_sequence_assignments SA
1455 WHERE SEQ.doc_sequence_id = SA.doc_sequence_id
1456 AND SA.application_id = 200
1457 AND SA.category_code = 'DBM INV'
1458 AND SA.method_code = 'A'
1459 AND SA.set_of_books_id = p_set_of_books_id
1460 AND trunc(p_rts_txn_date) between
1461 SA.start_date and nvl(SA.end_date, trunc(p_rts_txn_date));
1462 Exception
1463 WHEN NO_DATA_FOUND Then
1464 l_db_seq_name := null;
1465 l_db_seq_id := null;
1466 End;
1467
1468 If (l_db_seq_id IS NOT NULL ) Then --Bug 5947765
1469 -- get sequence value
1470 debug_info := 'Obtain next sequence value';
1471 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1472 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1473 debug_info);
1474 END IF;
1475
1476 Begin
1477 --Bug 3492081
1478 l_doc_seq_value := fnd_seqnum.get_next_sequence (
1479 200,
1480 'DBM INV',
1481 p_set_of_books_id,
1482 'A',
1483 trunc(p_rts_txn_date),
1484 l_db_seq_name,
1485 l_db_seq_id);
1486 Exception
1487 WHEN OTHERS THEN
1488 l_doc_seq_value := null;
1489 End;
1490 Else
1491 l_doc_seq_value := null;
1492 End if;
1493
1494 If (l_seq_num_profile = 'A' and l_doc_seq_value IS null) then
1495 fnd_message.set_name('SQLAP','AP_CANNOT_ASSIGN_DOC_SEQ');
1496 app_exception.raise_exception;
1497 End if;
1498
1499 End if;
1500
1501 -- Step j :
1502 --------------
1503 debug_info := 'Calculate invoice_amount';
1504 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1505 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1506 debug_info);
1507 END IF;
1508
1509
1510 l_invoice_amount := ap_utilities_pkg.ap_round_currency ((p_quantity * p_price),
1511 p_invoice_curr);
1512
1513 l_inv_base_amt := ap_utilities_pkg.ap_round_currency(
1514 nvl((l_invoice_amount * p_rcv_rate),0),
1515 p_base_curr);
1516
1517 -- Step k :
1518 --------------
1519 debug_info := 'Assigning cross currency payment values';
1520 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1521 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1522 debug_info);
1523 END IF;
1524
1525
1526 -- Bug 3492081
1527
1528 -- invoice curr and pay curr will be different only if they are euro currencies
1529 If (p_invoice_curr <> p_inv_pay_curr) Then
1530 l_pay_cross_rate := gl_currency_api.get_rate(
1531 p_invoice_curr,
1532 p_inv_pay_curr,
1533 trunc(p_rts_txn_date),
1534 'EMU FIXED');
1535 l_pay_curr_invoice_amount := gl_currency_api.convert_amount(
1536 p_invoice_curr,
1537 p_inv_pay_curr,
1538 trunc(p_rts_txn_date),
1539 'EMU FIXED',
1540 l_invoice_amount);
1541 l_pay_cross_rate_type := 'EMU FIXED';
1542
1543 -- Bug fixed 1998904
1544 -- moving population of l_pay_cross_rate_date out of the condition
1545 -- populating allways
1549
1546 -- l_pay_cross_rate_date :=p_rts_txn_date;
1547
1548 End if;
1550 l_pay_cross_rate_date := trunc(p_rts_txn_date);
1551 -- Step l :
1552 -------------
1553 debug_info := 'Get invoice_id';
1554 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1555 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1556 debug_info);
1557 END IF;
1558
1559
1560 SELECT ap_invoices_s.nextval INTO l_invoice_id FROM dual;
1561
1562 debug_info := 'Inserting row in ap_invoices';
1563 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1564 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1565 debug_info);
1566 END IF;
1567
1568 INSERT INTO ap_invoices_all (
1569 org_id,
1570 invoice_id,
1571 last_update_date,
1572 last_updated_by,
1573 creation_date,
1574 created_by,
1575 last_update_login,
1576 vendor_id,
1577 invoice_num,
1578 set_of_books_id,
1579 invoice_currency_code,
1580 payment_currency_code,
1581 payment_cross_rate,
1582 invoice_amount,
1583 vendor_site_id,
1584 invoice_date,
1585 source,
1586 invoice_type_lookup_code,
1587 description,
1588 batch_id,
1589 amount_applicable_to_discount,
1590 terms_id,
1591 terms_date,
1592 payment_method_code,
1593 pay_group_lookup_code,
1594 accts_pay_code_combination_id,
1595 payment_status_flag,
1596 base_amount,
1597 exclusive_payment_flag,
1598 goods_received_date,
1599 invoice_received_date,
1600 approved_amount,
1601 exchange_rate,
1602 exchange_rate_type,
1603 exchange_date,
1604 doc_sequence_id,
1605 doc_sequence_value,
1606 doc_category_code,
1607 payment_cross_rate_type,
1608 payment_cross_rate_date,
1609 pay_curr_invoice_amount,
1610 awt_flag,
1611 awt_group_id,
1612 gl_date,
1613 approval_ready_flag, -- Bug 2345472
1614 wfapproval_status, -- Bug 2345472
1615 auto_tax_calc_flag, -- Bug fix : 1971188.
1616 PAYMENT_REASON_CODE,
1617 BANK_CHARGE_BEARER,
1618 DELIVERY_CHANNEL_CODE,
1619 SETTLEMENT_PRIORITY,
1620 external_bank_account_id,
1621 legal_entity_id,
1622 party_id,
1623 party_site_id,
1624 payment_reason_comments, --4874927
1625 remit_to_supplier_name, --Start 7758980
1626 remit_to_supplier_id,
1627 remit_to_supplier_site,
1628 remit_to_supplier_site_id,
1629 relationship_id ) --End 7758980
1630 VALUES (
1631 p_org_id,
1632 l_invoice_id,
1633 sysdate,
1634 p_user_id,
1635 sysdate,
1636 p_user_id,
1637 p_login_id,
1638 p_vendor_id,
1639 l_invoice_num,
1640 p_set_of_books_id,
1641 p_invoice_curr,
1642 p_inv_pay_curr,
1643 nvl(l_pay_cross_rate,1),
1644 l_invoice_amount,
1645 l_vendor_site_id,
1646 trunc(p_rts_txn_date), --Bug 3492081
1647 'RTS',
1648 'DEBIT',
1649 l_inv_desc,
1650 p_batch_id,
1651 l_invoice_amount,
1652 p_terms_id,
1653 l_terms_date,
1654 p_payment_method,
1655 p_pay_group,
1656 p_accts_pay_ccid,
1657 'N',
1658 l_inv_base_amt,
1659 --Bug 5583430. For a debit memo, the pay alone flag should be set to 'N'
1660 --and not being populated based on supplier site.
1661 --p_excl_pay_flag,
1662 'N',
1663 p_rts_txn_date,
1664 sysdate,
1665 0,
1666 p_rcv_rate,
1667 p_rcv_rate_type,
1668 p_rcv_rate_date,
1669 l_db_seq_id,
1670 l_doc_seq_value,
1671 'DBM INV',
1672 l_pay_cross_rate_type,
1673 l_pay_cross_rate_date,
1674 l_pay_curr_invoice_amount,
1675 'N',
1676 p_awt_group_id,
1677 l_inv_gl_date,
1678 'Y', --Bug 2345472
1679 'NOT REQUIRED', --Bug 2345472
1680 p_auto_tax_calc_flag,
1681 p_PAYMENT_REASON_CODE,
1682 p_BANK_CHARGE_BEARER,
1683 p_DELIVERY_CHANNEL_CODE,
1684 p_SETTLEMENT_PRIORITY,
1685 p_external_bank_account_id,
1686 p_le_id,
1687 p_party_id,
1688 p_party_site_id,
1689 p_payment_reason_comments, --4874927
1690 p_remit_to_supplier_name, --Start 7758980
1691 p_remit_to_supplier_id,
1692 p_remit_to_supplier_site,
1693 p_remit_to_supplier_site_id,
1694 p_relationship_id ); --End 7758980
1695
1696 --Step m :
1697 ------------
1698 debug_info := 'Set all OUT parameters';
1699
1700 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1701 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1702 debug_info);
1703 END IF;
1704
1705 p_invoice_id := l_invoice_id;
1706 p_amount := l_invoice_amount;
1707 p_terms_date := l_terms_date;
1708 p_pay_curr_invoice_amount := l_pay_curr_invoice_amount;
1709 p_pay_cross_rate := l_pay_cross_rate;
1710
1711 Exception
1712 WHEN OTHERS THEN
1713 If (SQLCODE <> -20001) Then
1714 fnd_message.set_name('SQLAP','AP_DEBUG');
1715 fnd_message.set_token('ERROR',SQLERRM);
1716 fnd_message.set_token('CALLING_SEQUENCE',curr_calling_sequence);
1717 fnd_message.set_token('PARAMETERS',
1718 ' p_vendor_site_id = '||to_char(p_vendor_site_id)
1719 ||' p_vendor_id = '||to_char(p_vendor_id)
1720 ||' p_invoice_curr = '||p_invoice_curr
1721 ||' p_inv_pay_curr = '||p_inv_pay_curr
1722 ||' p_base_curr = '||p_base_curr
1723 ||' p_gl_date_from_rect_flag = '||p_gl_date_from_rect_flag
1724 ||' p_set_of_books_id = '||to_char(p_set_of_books_id)
1725 ||' p_quantity = '||to_char(p_quantity)
1726 ||' p_price = '||to_char(p_price)
1727 ||' p_quantity_billed = '||to_char(p_quantity_billed));
1728 fnd_message.set_token('DEBUG_INFO',debug_info);
1729 End if;
1730 app_exception.raise_exception;
1731 End Create_Invoice_Header;
1732
1733 /*--------------------------------------------------------------------------
1734 CREATE_DM_TAX : Creates tax on debit memo. Procedure calls tax engine
1735 and if tax lines are created then updates invoice_amount
1736 appropriately.
1737 ---------------------------------------------------------------------------*/
1738 FUNCTION create_dm_tax (p_invoice_id IN NUMBER,
1739 p_invoice_amount IN NUMBER,
1740 p_error_code OUT NOCOPY VARCHAR2,
1741 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
1742
1743
1744 l_lines_total ap_invoice_lines_all.amount%type;
1745 l_lines_total_base_amount ap_invoice_lines_all.base_amount%type;
1746 l_calling_sequence varchar2(2000);
1747 l_success boolean;
1748
1749 BEGIN
1750
1751 l_calling_sequence := p_calling_sequence ||' <- Create_dm_Tax';
1752
1753 --Bug:4537655
1754 l_success := ap_etax_pkg.calling_etax(
1755 p_invoice_id => p_invoice_id,
1756 p_calling_mode => 'CALCULATE',
1757 p_all_error_messages => 'N',
1758 p_error_code => p_error_code,
1759 p_calling_sequence => l_calling_sequence);
1760
1761
1762 IF (l_success) THEN
1763
1764 SELECT nvl(sum(amount),0),nvl(sum(base_amount),0)
1765 INTO l_lines_total,l_lines_total_base_amount
1766 FROM ap_invoice_lines_all
1767 WHERE invoice_id = p_invoice_id;
1768
1769 IF (l_lines_total <> p_invoice_amount and nvl(l_lines_total,0) <> 0) THEN
1770
1771 UPDATE ap_invoices
1772 SET invoice_amount = l_lines_total,
1773 base_amount = l_lines_total_base_amount
1774 WHERE invoice_id = p_invoice_id;
1775
1776 END IF;
1777
1778 END IF;
1779
1780 RETURN(l_success);
1781 END create_dm_tax;
1782
1783 END AP_AUTO_DM_CREATION_PKG;
1784