CREATE OR REPLACE FORCE VIEW APPS.XXAP_INVLINE_INTERFACE_V

AS

   SELECT aii.org_id,

--##############################################################################--

--#                                                                            #--

--#   View Name : XXAP_INVLINE_INTERFACE_V                                     #--

--#                                                                            #--

--#  Description    :                                                         #--

--#   OFFICE MANDATORY AND REQUIED AP INVOICE AND LINES INTERFACE COLUMNS      #--

--#  Initial Verion       Created By                  Last_Update_Date         #--

--#  --------------       ----------                  ----------------         #--

--#      1.0              Santosh                      05-JAN-2016             #--

--#                                                                            #--

--##############################################################################--

          aii.request_id,

          aii.status,

          aii.invoice_type_lookup_code,

          aii.source,

          aii.invoice_id,

          aii.invoice_num,

          aii.invoice_date,

          aii.vendor_id,

          aii.vendor_site_id,

          aii.invoice_currency_code,

          aii.invoice_amount,

          aii.description,

          aii.exchange_rate,

          aii.terms_name,

          aii.attribute_category,

          aii.attribute2 dff_cpr_number,

          aii.attribute3 dff_manual_payment,

          aii.attribute4 dff_service_name,

          aii.attribute5 dff_number_of_instalments,

          aii.attribute6 dff_reason_for_refund,

          aii.attribute7 dff_اسم_الوكيل,

          aii.attribute8 dff_pensioner_cpr,

          aii.attribute9 dff_jv_reference,

          aii.creation_date,

          aii.created_by,

          aii.last_update_date,

          aii.last_updated_by,

          ali.invoice_id line_invoice_id,

          ali.invoice_line_id line_invoice_line_id,

          ali.line_number line_line_number,

          ali.line_type_lookup_code line_line_type_lookup_code,

          ali.amount line_amount,

          ali.description line_description,

          ali.dist_code_combination_id line_dist_code_combination_id,

          ali.distribution_set_id line_distribution_set_id,

          ali.distribution_set_name line_distribution_set_name,

          ali.dist_code_concatenated line_dist_code_concatenated,

          ali.attribute_category line_attribute_category,

          ali.attribute1 line_attribute1,

          ali.attribute2 line_attribute2,

          ali.attribute3 line_attribute3,

          ali.attribute4 line_attribute4,

          ali.attribute5 line_attribute5,

          ali.assets_tracking_flag line_assets_tracking_flag,

          ali.po_header_id line_po_header_id,

          ali.po_number line_po_number,

          ali.po_line_id line_po_line_id,

          ali.po_line_number line_po_line_number,

          ali.po_line_location_id line_po_line_location_id,

          ali.po_shipment_num line_po_shipment_num,

          ali.po_distribution_id line_po_distribution_id,

          ali.po_distribution_num line_po_distribution_num,

          ali.po_unit_of_measure line_po_unit_of_measure,

          ali.inventory_item_id line_inventory_item_id,

          ali.item_description line_item_description,

          ali.quantity_invoiced line_quantity_invoiced,

          ali.ship_to_location_code line_ship_to_location_code,

          ali.unit_price line_unit_price,

          ali.match_option line_match_option,

          ali.final_match_flag line_final_match_flag

     FROM ap_invoices_interface aii, ap_invoice_lines_interface ali

    WHERE aii.invoice_id = ali.invoice_id;

View for Purchase order print depedent tables relations as APPS.XXPOPRINTING_REPORT

CREATE OR REPLACE FORCE VIEW APPS.XXPOPRINTING_REPORT

AS

SELECT pha.po_header_id,

--##############################################################################--

--#                                                                            #--

--#   View Name : XXPOPRINTING_REPORT                                          #--

--#                                                                            #--

--#  Description    :                                                          #--

--#  Office  Standard Purchase Order Printing  By Joining Dependent Tables     #--

--#  Initial Verion       Created By                  Last_Update_Date         #--

--#  --------------       ----------                  ----------------         #--

--#      1.0              Santosh                      05-JAN-2016             #--

--#                                                                            #--

--##############################################################################--

       pha.segment1 po_number,

       pha.creation_date po_date,

       pha.vendor_id,

       pha.comments,

       pv.vendor_name,

       pha.vendor_site_id,

          pvsa.address_line1

       || ' '

       || pvsa.address_line2

       || ' '

       || pvsa.address_line3

       || ' '

       || pvsa.city

       || ' '

       || ftt.territory_short_name

          address,

       pvsa.address_line1,

       pvsa.address_line2,

       pvsa.address_line3,

       pvsa.city,

       ftt.territory_short_name,

       pvsa.fax,

       pha.terms_id,

       apt.description pay_terms,

       pha.currency_code,

       pha.rate,

       pla.po_line_id,

       pla.line_num,

       pla.item_description,

       pla.unit_meas_lookup_code,

       pla.unit_price,

       LTRIM (pla.quantity) quantity,

       pv.segment1,

       pla.quantity * pla.unit_price * NVL (pha.rate, 1) amount,

       mtl_categories_b.segment1 catdesc

  FROM po_headers_all pha,

       po_lines_all pla,

       ap_terms apt,

       po_vendors pv,

       po_vendor_sites_all pvsa,

       fnd_territories_tl ftt,

       mtl_categories_b,

       po_line_locations_all poll,

       po_distributions_all pod,

       ap_invoice_distributions_all apid

 WHERE     pha.org_id = 81  ---  Pass the ORG_ID

       AND pha.segment1 = pha.segment1  --pass the PO Number

       AND pha.po_header_id = pla.po_header_id

       AND pha.terms_id = apt.term_id

       AND pha.vendor_id = pv.vendor_id

       AND pha.vendor_site_id = pvsa.vendor_site_id

       AND pvsa.country = ftt.territory_code(+)

       AND ftt.language(+) = 'AR'

       AND mtl_categories_b.category_id = pla.category_id

       AND pla.po_line_id = poll.po_line_id

       AND poll.line_location_id = pod.line_location_id

       AND pod.po_distribution_id = apid.po_distribution_id(+);

Oracle Cash Management

Oracle Cash Management R12 Reconciliation, AutoReconciliation and Matching Criteria

(1). Matching Bank Statement Lines with Transactions
Reconciling bank statements in Cash Management has two major steps: matching and clearing.
- Matching: Match a bank statement line against a Payables payment, Receivables receipt, Cash Management cashflow, Payroll payment, miscellaneous transaction, or open interface transaction using a transaction number (such as the payment or
deposit number), bank account, amount, and currency. You can match a statement line against a Treasury settlement using date and amount. You can also match a statement line against a General Ledger journal entry. Whenever localizations has
reconciliation function enabled in payments, you can match a bank statement against a Payroll EFT payment by using transaction number and date. If matching criteria are not met, the statement line remains unmatched. For manual reconciliation, there are no restrictions for matching statement lines and transactions.
- Clearing: Clear the transactions that are matched to the bank statement line, if the transactions have not been cleared previously. Only Payables payments, Receivables receipts, Cash Management cashflows, miscellaneous transactions, and open interface transactions can be cleared. Reconciled Payroll payments and General Ledger journal entries are matched but not cleared. You can report on reconciliation exceptions, variances, and items that have been matched.

1). Matching Criteria for Payables Transactions
The Cash Management AutoReconciliation program fully matches Payables
transactions against bank statement lines if the transactions meet the following criteria:
- For payment batches (such as EFT batches), the Payment Instruction ID matches the statement line Transaction Number, and the transaction amount is within the reconciliation tolerance, and
- For the detail payment transactions stopped transactions, the matching sequence searches in the following order:
1. Payment Number
2. Invoice Number and Supplier Bank Account
3. Invoice Number and Supplier Name
Also, the transaction amount is within the reconciliation tolerance.

Note: If the bank statement does not provide a check number for a payment, then matching to the invoice number and supplier bank account number is particularly useful.
- For stopped transactions, the payment must first be stopped or voided in Payables.

2). Matching Criteria for Receivables Transactions
The Cash Management AutoReconciliation program fully matches Receivables
transactions against bank statement lines if the transactions meet the following criteria:
- For remittance batches (such as Direct Debit batches), the Remittance Batch Deposit Number or Receipt Batch Name matches the statement line Transaction Number and the transaction amount is within the reconciliation tolerance.
- For detail remittance transactions, NSF (Non-Sufficient Funds), and Rejected transactions, the matching sequence searches in the following order:
1. Receipt Number
2. Invoice Number and Customer Bank Account Number
3. Invoice Number and Customer Name
Note: Also, the transaction amount is within the reconciliation tolerance.
If the bank statement does not provide a receipt number for a receipt, then matching to the invoice number and customer bank account number is particularly useful.
- For NSF (Non-Sufficient Funds) or Rejected transactions the receipt must first be reversed in Receivables.

3). Matching Criteria for Oracle Payroll Transactions
The Cash Management AutoReconciliation program matches Oracle Payroll manual
transactions against bank statement lines if the transactions meet the following criteria:
- For manual Payroll payments, the payment number matches the statement line transactions number and the payment amount, and the payment amount matches the statement line amount.
- For Oracle Payroll EFT transactions, the matching sequence searches in the following order:
1. Transaction Number and Transaction Date
2. Transaction Number, Batch Amount, and Transaction Date
- Reconciliation tolerances do not apply to Payroll payments.

4). Matching Criteria for Miscellaneous Transactions
The Cash Management AutoReconciliation program matches miscellaneous transactions if the transactions meet the following criteria:
- If the transaction type matches to regular miscellaneous transactions:
- The transaction amount is within the reconciliation tolerance.
- The Receipt Number matches the statement line Transaction Number.
- If the transaction type matches to correction statement lines:
- For reversal corrections, the statement line has the same transaction number, same amount, and opposite transaction type (for example, Miscellaneous Receipt and Miscellaneous Payment), or
- For adjustment corrections, the statement line has the same transaction number, and has a transaction type of payment, receipt, miscellaneous payment, or miscellaneous receipt, and the net amount is within the reconciliation tolerance.

5). Matching Criteria for Open Interface Transactions
The Cash Management AutoReconciliation program matches open interface
transactions if the transactions meet one of the following criteria:
- The Transaction Number matches the statement line Transaction Number, and the transaction amount is within the reconciliation tolerance.
- The transaction date and amount are the same as the bank statement line date and amount. If you use the Reconciliation Open Interface to reconcile Treasury settlements, then you must choose date and amount as the matching criteria.

6). Matching Criteria for General Ledger Journals
The Cash Management AutoReconciliation program matches General Ledger journal transactions if the transactions meet the following criteria:
- The journal line is posted.
- The journal line Accounting Flexfield matches the bank account's Cash Accounting Flexfield.
- The journal line Description matches the statement line Transaction Number.

7). Matching Criteria for Cash Management Cashflows
The Cash Management AutoReconciliation program fully matches Cash Management cashflow transactions in the following sequence:
1. Statement Line ID (for transfers created through the sweep transactions program or
transactions created from bank statement lines)
2. Bank Reference Number, Date, and Amount
3. Agent Bank Account, Date, and Amount
Note: The transaction amount must be within the reconciliation tolerance.

9). Run Program: Autoreconciliation

From Concurrent Request, Parameters needed:

Bank Branch Name:  -- Provide / Select
Bank Account Number:   -- Provide / Select
Statement Number From:  -- Provide / Select
Statement Number To:  -- Provide / Select
Statement Date From:  -- Provide / Select
Statement Date To:  -- Provide / Select
GL Date:  -- Provide
Organization:  -- Provide / Select
Receivable Activity:
Payment Merthod:
NSF Handling:  -- Options: No Action, Debit Memo Reversal, Reverse

To Insure that all the important parameters become mandatory, Please do as follows:

Responsibiity System Administrator
Navigate
Concurrent - Program - Define

Query
Short Name = ARPLABRC
Click Parameters

Find the Parameters

15
Bank Branch Name
20
Bank Account Number
30
Statement Number from
40
Statement Number to

For Each Parameter, in the Validation Section, Check the Required Check box to make each Parameter Mandatory when running the Program - AutoReconciliation Reconciliation concurrent process.

Click Save

(2). Question / Answers:

Q1: To mark a bank statement as complete:
1. Navigate to the Find Bank Statements window.
2. Enter the query criteria for the statement you want to mark as complete, or leave
the criteria blank to query all existing statements.
3. Choose Find to see the Reconcile Bank Statements folder window.
4. Select the statement you want to complete, then choose Review.
5. In the Bank Statement window, check the Complete check box.

Q2: To unmark a completed bank statement:
1. Navigate to the Bank Statement window, as above.
2. Uncheck the Complete check box.

(3). Reconciliation Controls
1. Optionally, you can select Payables Matching Order 1, Payables Matching Order 2, Float Handling, and Receivables Matching Order.

Foreign Currency Bank
1. Optionally, you can select Exchange Rate Type and Exchange Rate Date.

Open Interface Controls
1. Optionally, you can select Use Reconciliation Open Interfaces and Open Interface

Matching Criteria.
2. Optionally, you can enter Float Status and Clear Status.

Manual Reconciliation Tolerances
1. Optionally, you can enter Tolerance Amount and Tolerance Percentage.

Auto Reconciliation Tolerances
Payments
1. Optionally, you can enter Tolerance Amount, Tolerance Percentage, Foreign Tolerance Differences, and Tolerance Differences.
Receipts
1. Optionally, you can enter Tolerance Amount, Tolerance Percentage, and Foreign Tolerance Differences.
Cashflows
1. Optionally, you can enter Tolerance Amount, Tolerance Percentage, Foreign Tolerance Differences, and Tolerance Differences.
Open Interface
1. Optionally, you can enter Tolerance Amount and Tolerance Percentage, Foreign Tolerance Differences, and Tolerance Differences.
2. Click Save and Next.

Oracle Interface Frequently Asked Questions

Oracle Interface Frequently Asked Questions

1)What process you have folllowed to develop Inbound Interface?

 

2)What are  the Interface tables?

 

3)What are the Base tables?

 

4)What is the Standard program has used to import data?

 

5)How to track the errors in Interfae?

 

6)What are the Validations you have done?

 

7)Tell me some of teh errors which you have faced?

 

8)Have you used "Autonomous Transaction" in Interface?

 

9)What is the diff between Inbound and Outbound?

 

10)Wether you have used UTL_FILE or SQL*loader?

 

11)What is format of Data file ? (.csv)

 

12)Wether your Interface is scheduled or manuvally running ? (Scheduled)

 

14)what is the Differenc between Converison and Interface?