Bank, Bank Account, Cheque Book PLSQL Query - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/12/bank-bank-account-cheque-book-plsql.html

select a.bank_name,b.BANK_ACCOUNT_NAME, b.BANK_ACCOUNT_NUM, b.CURRENCY_CODE,  b.IBAN_NUMBER, b.EFT_USER_NUM,
c.SEGMENT1, c.SEGMENT9 , c.SEGMENT10, c.SEGMENT2, c.SEGMENT3, c.SEGMENT4, c.SEGMENT5, c.SEGMENT6,c.SEGMENT7,c.SEGMENT8, c.SEGMENT11
,
(SELECT gl_flexfields_pkg.get_description_sql

(
50232388,--- chart of account id
5,----- Position of segment
segment3 ---- Segment value
)

FROM gl_code_combinations g where g.CODE_COMBINATION_ID = c.CODE_COMBINATION_ID ) Description
,d.PAYMENT_DOCUMENT_NAME , d.PAPER_STOCK_TYPE , d.FIRST_AVAILABLE_DOCUMENT_NUM
from ce_banks_v a,   ce_bank_accounts b , gl_code_combinations c  ,ce_payment_documents d
where  a.bank_party_id = b.BANK_ID
and b.ASSET_CODE_COMBINATION_ID = c.CODE_COMBINATION_ID
and b.BANK_ACCOUNT_ID = d.INTERNAL_BANK_ACCOUNT_ID
--and trunc(b.CREATION_DATE) =  '13-DEC-2019'

--order by c.SEGMENT3 asc

Work around for Cross Currency Payments - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/10/work-around-for-cross-currency-payments.html

1. Make Invoice in EUR.

2. Create dummy EUR Payment Clearing Account and do Payment.

3. Do a Fund Transfer from PKR to EUR.

4. Source Bank Account will be PKR and Destination Bank Account will be EUR.

Below are the Entries generated after above scenario:

Warning control in Payables Module if the invoice date is 3 months older than GL date or vice versa - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/08/warning-control-in-payables-module-if.html

When New Block Instance
LINE_SUM_FOLDER

Condition:
abs(Months_between(:INV_SUM_FOLDER.INVOICE_DATE,:INV_SUM_FOLDER.GL_DATE)) > 3

Warning
Message: Number of Months between Invoice & GL Date Exceeds 3 Months.


OR, You can put Error on Header. So user cannot proceed with the Invoice.


When Validate Record
INV_SUM_FOLDER

Condition:
abs(Months_between(:INV_SUM_FOLDER.INVOICE_DATE,:INV_SUM_FOLDER.GL_DATE)) > 3

Error
Message: Number of Months between Invoice & GL Date Exceeds 3 Months.

Inconsistent Quantity Info Error on Invoice (Inconsistent quantity, price, amount combination) - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/inconsistent-quantity-info-error-on.html

Inconsistent Quantity Info Error on Invoice (Inconsistent quantity, price, amount combination) - Oracle EBS R12


 Unit Price * Quantity = Amount  ( Rounding Issue)

Invoice Stucked on Needs Revalidation status. Not allowing us to Validate or Cancel the same - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/invoice-stucked-on-needs-revalidation.html

Match status flag on posted invoice and self assessed tax distributions is not 'A'. Such invoices will be picked by Invoice Validation Program though invoices are shown as 'Validated' / 'Needs Revalidation' on invoice Workbench. Further if match status flag is 'N' on posted distributions, then cancellation might fail with "AP_UNSUCCESSFUL_CANCELLATION" message.

You can update the match_status_flag as 'A'

How to make Bank Account Transfers from Oracle AP - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/bank-account-transfers-from-ap-oracle.html


How you can make Bank Account Transfers through Oracle AP if Cash Management is not Implemented.

USD TO PKR

PKR TO USD

Expense of Foreign Currency and Payment will be done on Exchange of Currency (No Foreign Currency Bank Account) - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/foreign-currency-expense-on-functional.html

If you don't have Foreign Currency Bank Account and you want to book Expense of Foreign Currency and Payment will be done on Exchange of Currency. Then the Payment Process will be as follows:

Invoice and Payment Exchange Rates are same in my case.
Exchange Rate = 150

		Entered DR	Entered CR	Accounted DR	Accounted CR
Foreign Currency Invoice	Expense	5000		750000
	Liability		5000		750000
Foreign Currency Payment	Liability	5000		750000
	Foreign Currency Clearing Bank Account		5000		750000
Functional Currency JV	Foreign Currency Clearing Bank Account			750000
	Functional Currency Bank Account				750000

Difference in Credit and Debit Memo - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/credit-and-debit-memo-concept-oracle.html

Credit Memo. Negative amount invoice created by a supplier and sent to you to notify you of a credit.

Debit Memo. Negative amount invoice created by you and sent to a supplier to notify the supplier of a credit you are recording. 

Types of Prepayments - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/types-of-prepayments-oracle-ebs-r12.html

There are two types of prepayments: Temporary and Permanent.

Temporary Prepayments

Temporary prepayments can be applied to invoices or expense reports you receive. For example, you use a Temporary prepayment to pay a hotel a catering deposit. When the hotel’s invoice arrives, apply the prepayment to the invoice to reduce the invoice amount you pay.

Or;

It’s a Deposit with the supplier, But it will be adjusted against future Invoices.

Permanent Prepayments

Permanent prepayments cannot be applied to invoices. For example, you use a Permanent prepayment to pay a lease deposit for which you do not expect to be invoiced.

Or;

It’s a deposit with the supplier, But it will not be adjusted against future Invoices. It’s nothing but a security deposit.

Customization for Gain & Loss Account at Payment time - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/customization-for-gain-loss-account-at.html

User wants Gain and Loss Amount to be charged in Expense Accounts used on Invoice Distribution and for Asset Clearing Account it must charge Gain & Loss in Realized Gain and Loss Account.

Please Copy Paste the Below URL in your Browser

https://drive.google.com/open?id=1eh9n02fItVNFTyY6G9G-fvaBbkkqYhOn

Document Sequencing in AP Payments Operating Unit Wise - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/document-sequencing-in-ap-payments.html

Please Copy Paste the Below URL in your Browser

https://drive.google.com/open?id=1lsvD8NUoAzgPk7kWrHjSMS2mTL0Er9Ti

Recording a Refund of a Prepayment or Advance - Oracle EBS R12

https://aporaclepayables.blogspot.com/2019/07/recording-refund-of-prepayment-or.html

Go through the section of "Recording a Refund of a Prepayment or Advance" in Payables user guide.

Note: We recommend that to record a refund, you enter a standard invoice and apply the prepayment to it. Then enter a debit memo and pay it with a Refund payment in the Payments window.

See: Refunds for Prepayments: page 6 – 10. 

Alternatively, record the refund by using the following method. Recording Refunds by Paying a Standard Invoice You can record a refund from a supplier that pays back an unused prepayment or part of a prepayment. When you do this, Payables reverses the prepayment so that it is no longer outstanding and so that you can no longer apply the prepayment to an invoice. The net effect is a debit to your cash (asset) account in the amount of the refund and a credit to the prepayment account in the amount of the refund.

You can use exactly the same method to record a refund from an employee for an advance. 

Invoices 4 – 245 

Prerequisites 

1. You have entered and paid a prepayment or advance. 

2. The supplier or employee has refunded the unused portion of the prepayment. 

3. If the supplier or employee has not refunded the entire prepayment, apply the appropriate amount of the prepayment to an invoice or expense report. 

To record a refund of a prepayment: 

1. In the Invoices window, enter an invoice in the amount of the refund from the supplier. Enter Standard for the type. Enter a description on the invoice to indicate that you are retiring a prepayment. Create an Invoice distribution for the amount of the refund to debit your cash account. 

2. Choose the Actions button to navigate to the Invoice Actions window. Select the Apply/Unapply Prepayment check box and choose OK. 

3. Select the prepayment you want to apply. Enter the refund amount in the Amount To Apply field. Optionally change the GL Date. 

4. Choose the Apply/Unapply button to apply the prepayment amount to the invoice you entered for the refund and to save your work. 

This fully retires the prepayment. This credit balances the debit you entered on the invoice (Step 1) to the Cash account. 

4 – 246 Oracle Payables User Guide

Suppose, you have given an Advance of Rs. 1000 to a contractor and the Total Expenses were made of Rs. 500. Now the remaining Rs. 500 have to be refunded from Supplier.

Prepayment Invoice	01-001-000-248111100-000-00-0000-0000	Liability		1000
	01-001-000-112121501-000-00-0000-0000	Prepaid expense	1000
Prepayment Payment	01-001-000-248311403-000-00-0000-0000	Cash		1000
	01-001-000-248111100-000-00-0000-0000	Liability	1000
Standard Invoice	01-001-000-581139210-000-00-0000-0000	Item expense	500
	01-001-000-248111100-000-00-0000-0000	Liability		500
Apply Prepayment	01-001-000-248111100-000-00-0000-0000	Liability	500
	01-001-000-112121501-000-00-0000-0000	Prepaid Expense		500
	Refund Recommended Method
Standard Invoice	01-001-000-101990105-000-00-0000-0000	Item expense	500		Invoice Distribution Clearing Account
	01-001-000-248111100-000-00-0000-0000	Liability		500
Apply Prepayment	01-001-000-248111100-000-00-0000-0000	Liability	500
	01-001-000-112121501-000-00-0000-0000	Prepaid Expense		500
Debit Memo	01-001-000-101990105-000-00-0000-0000	Item expense		500	Invoice Distribution Clearing Account
	01-001-000-248111100-000-00-0000-0000	Liability	500
Debit Memo Payment	01-001-000-248311403-000-00-0000-0000	Cash	500
	01-001-000-248111100-000-00-0000-0000	Liability		500
	Refund Direct Method
Standard Invoice	01-001-000-248311403-000-00-0000-0000	Cash	500		Invoice Distribution Clearing Account
	01-001-000-248111100-000-00-0000-0000	Liability		500
Apply Prepayment	01-001-000-248111100-000-00-0000-0000	Liability	500
	01-001-000-112121501-000-00-0000-0000	Prepaid Expense		500