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