Daily Archives: December 22, 2010

Oracle EBS – TABLES USED BY FNDSCSGN – TOP TEN LIST feature


By default, the Top Ten List appears on the right half side of the FNDSCSGN form.  You can access this list by first clicking on the toolbar ‘Special’ button and then clicking on the Top Ten List on the popup.
You may want to access the table and perhaps update the columns for the Top Ten List.
You can obtain information using the Oracle user name:

SQL>
select fnd_RESPONSIBILITY.RESPONSIBILITY_NAME,function1,        function2,function3,function4,function5,        function6,function7,function8,function9,function10        from fnd_user_RESPONSIBILITY,fnd_RESPONSIBILITY,fnd_user        where fnd_user.user_name ='SPUROHIT'        and fnd_user_RESPONSIBILITY.responsibility_id =        fnd_RESPONSIBILITY.responsibility_id        and fnd_RESPONSIBILITY.application_id =        fnd_user_RESPONSIBILITY.application_id        and fnd_user_RESPONSIBILITY.user_id = fnd_user.user_id;

Oracle EBS – Using FNDLOAD – Tips


Generic Loader (FNDLOAD)
The Generic Loader (FNDLOAD) is a concurrent program that can move Oracle Applications data between database and text file representations. The loader reads a configuration file to determine what data to access. For information on specific configuration files consult the Open Interfaces Guide for your product group.

Overview
The Generic Loader can download data from an application entity into a portable, editable text file. This file can then be uploaded into any other database to copy the data. Conversion between database store and file format is specified by a configuration file that is read by the loader.

The Generic Loader downloads data from a database according to a configuration (.lct) file, and converts the data into a data file (.ldt file). The Generic Loader can then upload this data to another database using a configuration file.

The loader operates in one of two modes: download or upload. In the download mode, data is downloaded from the database to a text file; in the upload mode, data is uploaded from a text file to the database.

Data structures supported by the loader include master-detail relationships and foreign key reference relationships.

In both downloading and uploading, the structure of the data involved is described by a configuration file. The configuration file describes the structure of the data and also the access methods to use to copy the data into or out of the database. The same configuration file may be used for both uploading and downloading.

When downloading, the Generic Loader creates a second file, called the data file that contains the structured data selected for downloading. The data file has a standard syntax for representing the data that has been downloaded. When uploading, the Generic Loader reads a data file to get the data that it is to upload. In most cases, the data file was produced by a previous download, but may have come from another source. The data file cannot be interpreted without the corresponding configuration file available.

What can be done?
These are the extensive list which can be done through FNDLOAD
· Concurrent Programs, Executables
· Request Groups, Request Sets
· Profile Options
· Key and Descriptive Flexfields
· Menus and Responsibilities
· Forms and Form Functions
· Attachments
· Messages
· Value Sets and Values
· Lookup Types
· User Responsibilities
· Printer Definitions
· FND Dictionary
· Help Configuration
· Document Sequences
· Alerts
· Concurrent Manager Schedules

The FNDLOAD: Concurrent Program – FNDLOAD can be executed as a concurrent program.

Some advantages when using FNDLOAD
1. Because downloaded data is stored in a text file, version administration is possible
2. There is nothing to worry to go to purchase because Investment = 0$
3. No learning curve. this is relief for developer and dba
4. Fully supported and recommended by Oracle
5. Capture the migrations in a file and use it during installations, clones etc. to migrate in batch
6. Pin-point when something happened and where (database) easily
7. Your AOL data migration process is now simplified and streamlined goal attained

Some disadvantages when using FNDLOAD
1. Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
2. UPLOAD_MODE=REPLACE only for menus
3. No validation against migrating database/instance sensitive data

Syntax
The Generic Loader is a concurrent program named FNDLOAD. The concurrent executable takes the following parameters:

FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ...]
where

The APPS schema and password in the form username/password[@connect_string]. If
connect_string is omitted, it is taken in a platform-specific manner from the environment
using the name TWO_TASK.

Concurrent program flags.

mode
UPLOAD or DOWNLOAD. UPLOAD causes the datafile to be uploaded to the database. DOWNLOAD causes the loader to fetch rows and write them to the datafile.

The configuration file to use (usually with a suffix of .lct, but not enforced or supplied by the loader).

The data file to write (usually with a suffix of .ldt, but not enforced or supplied by the loader). If the data file already exists, it will be overwritten.

The entity(ies) to upload or download. When uploading, you should always upload all entities, so specify a “-” to upload all entities.

Zero or more additional parameters are used to provide bind values in the access SQL (both UPLOAD and DOWNLOAD). Each parameter is in the form NAME=VALUE. NAME should not conflict with an attribute name for the entities being loaded.

Modes of Operation
This is important because it would drive the whole flow, and it always be either Upload or Download.

Example of download
FNDLOAD apps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=

Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt – CUSTOM_MODE=FORCE undocumented parameter

What are FNDLOAD Options?
· Application level OR single entity level download
o (Example) Download all the profile options of Inventory or just the INV: Default Primary UOM
· Entire OR Partial upload of a data file
o (Example) Upload the entire myfile.ldt or just a single entity indicated by – and mode UPLOAD or UPLOAD_PARTIAL
o Entity name required for UPLOAD_PARTIAL mode
· Custom mode force update
o To override Oracle’s upload algorithm and update the custom AOL data regardless, use CUSTOM_MODE= FORCE
o UPLOAD_MODE= REPLACE (only for menus)
· Support for NLS uploads
o NLS data uploads for translated columns are supported, use UPLOAD_MODE= NLS

Where is Configuration File Located
By default Oracle delivers most of configuration files you can use to download certain entities.
· Configuration files with extension .lct
o On Unix – all the configuration files are in $FND_TOP/patch/115/import directory
o On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory
· Data files with extension .ldt
FNDLOAD File Structure
· The configuration files (.lct) are delivered and maintained by Oracle
· It has entity definitions, parent-child relationships and user input parameters identified by :NAME
· Downloading a parent automatically downloads all children – (Example) Concurrent Program download
· The data files (.ldt) have both entity definition and the data
· It also shows the version and the location of the configuration file (.lct) that was used
· Without the configuration file, data file is useless
· Without the data file, configuration file is meaningless

FNDLOAD Files
· Key files: .lct and .ldt
· You must run the FNDLOAD as apps user not as applsys or any other user, otherwise you will receive Ora-6550…error
· Both are easily readable, editable and portable
· Do not modify Oracle .lct files
· Use your favourite editor to manipulate only the .ldt files but be cautious about data type, length, delimiter placements etc.
· Use the log file outputs or .ldt file contents creatively for quick file comparisons and answer questions faster (Why can I access that? What is that profile option name, value and level? What is the value set used for that DFF segment attribute10 etc.)
· Partial string searches (which value set has Priority something in its where clause, etc)

Some sample examples
1 – Printer Styles
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=”printer style name”

2 – Lookups
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”prod” LOOKUP_TYPE=”lookup name”

3 – Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME=”prod” DESCRIPTIVE_FLEXFIELD_NAME=”desc flex name” P_CONTEXT_CODE=”context name”

4 – Key Flexfield Structures
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME=”prod” ID_FLEX_CODE=”key flex code” P_STRUCTURE_CODE=”structure name”

5 – Concurrent Programs
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”prod” CONCURRENT_PROGRAM_NAME=”concurrent name”

6 – Value Sets
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=”value set name”

7 – Value Sets with values
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=”value set name”

8 – Profile Options
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”prod”

9 – Request Group
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group” APPLICATION_SHORT_NAME=”prod”

10 – Request Sets
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=”prod” REQUEST_SET_NAME=”request set”

11 – Responsibilities
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility

12 – Menus
FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=”menu_name”

13 – Forms/Functions/Personalizations: Refer to the System Administrator’s Guide on dependencies
FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct
FND_FORM_CUSTOM_RULES form_name=

OR

FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FUNCTION FUNCTION_NAME=

OR

FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FORM FORM_NAME=
14 – User/Responsibilities
FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_USER

15 – Alert
FNDLOAD apps/pwd 0 Y DOWNLOAD
$ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS
APPLICATION_SHORT_NAME=FND ALERT_NAME=Alert name to download

References:
· Oracle Applications Systems Administrator Guide – Configuration

Notes:
1. Give special attention when downloading Menus or Responsibilities.
In case you have several developers modifying Responsibilities and Menus, then be ultra careful. Not being careful will mean that untested Forms, Functions and Menus will become available in your clients Production environment besides your tested Forms, Functions and Menus.

2. Be very careful when downloading flexfields that reference value sets with independent values for GL Segment Codes.
By doing so, you will download and extract all the test data in GL Codes that might not be applicable for production.

3. There are several variations possible for FNDLOAD, for example you can restrict the download and upload to specific segments within Descriptive Flex Fields.

4. FNDLOAD is very reliable and stable, if used properly.

5. Please test your FNDLOAD properly, so as to ensure that you do not get any unexpected data.

6. As the name suggests, FNDLOAD is useful for FND Related objects. However in any implementation, you will be required to migrate the Setups in Financials and Oracle HRMS from one environment to another. For this you can use iSetup “Oracle iSetup”.
Some of the things that can be migrated using Oracle iSetup are GL Set of Books, HR Organization Structures, HRMS Employees, Profile Options Setup, Suppliers, Customers, Tax Codes & Tax Rates, Financials Setup, Accounting Calendars, Chart of Accounts, GL Currencies.

Oracle Purchasing – Receiving – Payables – What is Pay On Receipt?


POXPOIV, Pay on Receipt, also known as ERS (Evaluated Receipt Settlement), or Self-Billing, is an Oracle Purchasing concurrent program, which automatically creates invoices in Payables and matches them with Purchase Orders for the received amount.

When Pay on Receipt Autoinvoice (PRA) program is ran, it in turn launches Payables Interface Import (PII) program. PRA updates invoice_status_code of rcv_transactions to INVOICED, then populates records in ap_invoices_interface and ap_invoice_lines_interface.  Then the interface data is committed and PII is invoked to create the invoices.
If PII program errors out for some reason during the validation of AP interface data then the records stay in interface tables with REJECTED status and the associated error information will be stored in AP_INTERAFACE_REJECTIONS table. PRA sets invoice_status_code of rcv_transactions, for transaction_type RECEIVE,  to INVOICED for both successfully imported invoices and for invoices rejected by PII.
If Pay on Receipt AutoInvoice is ran again later then it will pick only those records which have invoice_status_code as ‘PENDING. It will not pick/process the records which have invoice_status_code = INVOICED.

The Pay on Receipt program populates data in ap_invoices_interface and ap_invoice_lines_interface.  Then Payables Import program is kicked off, which in turn processes these records and creates an invoice by populating the final tables of ap_invoices_all and ap_invoice_distributions_all
At the time invoice_status_code is inserted into rcv_transactions, the system checks for the value of the Vendor Site’s Pay On Code.  If Pay On Code is set to ‘Receipt’, then invoice_status_code is inserted into rcv_transactions as ‘PENDING’, otherwise it will be inserted as null

PRA launches PII, and PII completes with error.  This will cause records to be stuck in AP Interface tables, and an Invoice is not created.  To determine if records are stuck in status of NULL or REJECTED in ap_invoices_interface, to determine those records:

select AP.invoice_id, AP.invoice_num,AP.vendor_id,AP.vendor_site_id,        AP.po_number, AP.status,AP.source,AP.group_id, APL.invoice_id,        APL.po_header_id,APL.po_line_id,APL.po_line_location_id,        APL.po_distribution_id,APL.po_release_id, APL.release_num,        APL.org_id,APL.rcv_transaction_id,APL.receipt_number,       RT.transaction_type,RT.transaction_id,RT.parent_transaction_id,       RT.quantity,RT.invoice_status_code,RT.quantity_billed   from ap_invoices_interface AP, ap_invoice_lines_interface APL,        rcv_transactions RT  where AP.source = 'ERS'    and AP.invoice_id = APL.invoice_id    and rt.po_header_id = APL.po_header_id    and APL.rcv_transaction_id is not null    and APL.rcv_transaction_id = rt.transaction_id    and (rt.vendor_id is null or rt.vendor_site_id is null);

To verify if Vendor is enabled to Pay on Receipt, retrieve the following:
select pv.vendor_name, pvs.last_update_date, pvs.creation_date, pvs.purchasing_site flag, pvs.pay_site_flag, pvs.default_pay_site_id, pvs.pay_on_code, pvs.inactive_date, pvs.hold_all_payments_flag
from po_vendor_sites_all pvs, po_vendors_all pv
where pv.vendor_name in <‘Enter the vendor name’>
and pv.vendor_id = pvs.vendor_id;

Verify the receipt is available for Pay on Receipt.  If the invoice_status_code is NULL in RCV_TRANSACTIONS, the record is not eligible for Pay on Receipt.

select rt.quantity, rt.source_document_code, rt.invoice_status_code, rt.match_option, rt.match_flag, rt.shipment_header_id,
rt.po_header_id, rsh.invoice_status_code, rsh.last_update_date
from rcv_transactions rt, rcv_shipment_headers.rsh
where rt.shipment_header_id = rsh.shipment_header_id
and rsh.receipt_num = <’enter receipt number’>
and rsh.ship_to_or_id = <enter the ship_to_org_id>;

 

Oracle EBS – Purchasing / Inventory – Receiving Reports and Processes


The following are the reports and processes within the Receiving application. An explanation of each report or process follows.

REPORTS

POXDLPDT – Receipt Traveler
POXRVRUR – Unordered Receipts Report
POXRVRSR – Substitute Receipts Report
RCVIERR – Receiving Interface Errors Report
POXRVRTN – Receipt Adjustments Report
RCVTXRTR – Receiving Transactions Register
POXRVXRV – Expected Receipts Report
POXRVRER – Receiving Exceptions Report
POXRVOVS – Overshipments Report
POXPORRA – Uninvoiced Receipt report
POXRRVDR – Receiving Account Distribution report
POXACREC – Accrual Reconciliation report
POXACWRO – Accrual Write-off form and report
POXRRCVV – Receiving Value Report
POXRVRVD – Receiving Value Report by Destination Account

PROCESSES

PORCPTWF – Confirm Receipts Workflow Select Orders Process
POXPOIV – Payment on Receipt
RCVDLPDT – Receipt Traveler
RVCTP or RTP – Receiving Transaction Processor
RVCACR – Receipt Accruals – Period-End Process

 

POXDLPDT – Receipt Traveler
The Receipt Traveler facilitates receiving inspection and delivery of goods you receive within your organization. After you receive the goods, you can print receipt travelers and attach these tickets to the goods. You can enter selection criteria to specify the receipt travelers you want to print. One receipt traveler prints per distribution, and each traveler has space for you to record delivery comments.

If the RCV: Print Receipt Traveler profile option is set to Yes, the receipt traveler automatically prints when you perform a receipt or a receiving transaction and using Online or Immediate processing mode, and when you match unordered receipts

POXRVRUR – Unordered Receipts Report
The Unordered Receipts Report lists all or selected unordered receipts. Unordered receipts refer to received items that receivers could not match to purchase orders or return material authorizations (RMAs). You can enter unordered receipt transactions only if you set up your system options accordingly. Purchasing lets you specify which unordered receipts you want to review.

POXRVRSR – Substitute Receipts Report
The Substitute Receipts Report lists all or specific substitute receipts. To prevent disruption in your receiving process, Purchasing lets you receive items which a supplier has delivered as a substitute for the ordered item. You need to predefine the items you accept as substitute for other items.

The report includes sourcing information appropriate for the source type. For the Inventory source type, this is the organization and subinventory. For the Supplier source type, this is the supplier and supplier site.

RCVIERR – Receiving Interface Errors Report
This report shows you what warnings or errors occurred while the Receiving Transaction Processor was processing rows in the Receiving Open Interface tables. Rows processed in the Receiving Open Interface include Advance Shipment Notices (ASNs), receipts, and deliveries. Any errors that occur during this process are displayed in the Receiving Interface Errors report when you run the report. At the same time, the errors are also sent to the Oracle e–Commerce Gateway process responsible for generating Application Advices. (For example, Application Advices are sent back to suppliers detailing errors that occurred when the suppliers sent ASNs.)

POXRVRTN – Receipt Adjustments Report
The Receipt Adjustments Report lists purchase order shipments, internal requisition lines, or return material authorization (RMA) lines with corrections or returns to supplier (or customer).

RCVTXRTR – Receiving Transactions Register
The Receiving Transactions Register lists detail information about your receiving transactions. The register is sorted by item, category, receipt number, document number, and transaction date. The three–part document number represents purchase order number, purchase order line number, and purchase order shipment number. For receiving transactions created against customer returns, the two–part document number represents the return material authorization (RMA) number and RMA line number.

POXRVXRV – Expected Receipts Report
The Expected Receipts Report can be used to review all or specific supplier sourced expected receipts or expected customer returns that have not yet been received for a particular date or a range of dates. The report excludes purchase orders that are cancelled, closed, or closed for receiving at the header, release, line, or shipment level. You can specify the expected receipts you want to review.

POXRVRER – Receiving Exceptions Report
Use the Receiving Exceptions Report to review receipts for which there is a receipt exception. These receipts cannot be automatically closed for receiving or invoicing. You can enter a receipt exception when you receive the item.

POXRVOVS – Overshipments Report
The Overshipments Report lists purchase order receipts with a quantity received greater than the quantity ordered. You can also use the Overshipments Report to identify service overcharges or suppliers who deliver more than the requested quantity.

POXPORRA – Uninvoiced Receipt report
The Uninvoiced Receipts Report should be run before the Receipt Accrual – Period–End process. With this report, you can review all or specific uninvoiced receipts for both period end and online accruals. Uninvoiced receipts are goods and services you have received that your supplier did not invoice yet. This report indicates exactly what you have to accrue and for what amount, and helps you analyze your receipt accrual entries. The accrual amount is the difference between the quantity received and the quantity billed multiplied by the unit price of the item

POXRRVDR – Receiving Account Distribution report
The Receiving Account Distribution Report lists the accounting distributions for your receiving transactions. This report supports the distributions created for the following transactions:
– Purchase Order Receipts
– Purchase Order Receipt Adjustments
– Purchase Order Returns to Supplier
– Deliver to Expense Destinations
– Return to Receiving from Expense Destinations
– Match Unordered Receipts
This feature helps you reconcile your receiving accounting to your general ledger.

POXACREC – Accrual Reconciliation report
The Accrual Reconciliation Report can be used to analyze the balance of the Accounts Payable (A/P) accrual accounts. You can accrue both expense and inventory purchases as you receive them. When this happens, you temporarily record an accounts payable liability to your Expense or Inventory A/P accrual accounts. When Payables creates the accounting for the matched and approved invoice, Payables clears the A/P accrual accounts and records the liability from the supplier site.

This report is available in two versions: Accrual Reconciliation Report and Accrual Rebuild Reconciliation Report. When you specify Accrual Rebuild Reconciliation Report, the report selects the accounting entries from the appropriate source (sub ledgers). This accounting information resides in a temporary table and remains until you rebuild this information again. When you specify Accrual Reconciliation Report, the report does not reselect this information. Instead, it merely reports using the pre–existing information. This feature saves you time, because you do not have to recreate the accrual information every time you submit a report. Typically, you specify the Accrual Rebuild Reconciliation Report at month end and use the Accrual Reconciliation Report for interim reports. Note that the title of the report remains the Accrual Reconciliation Report even if you specify the rebuild option.

After you have entered your receipt transactions and matched your invoices, you can run the report for any transaction date range and identify any differences between your PO Receipts and A/P Invoices. This report also displays any miscellaneous transactions recorded in error to your accrual accounts. These miscellaneous transactions or transactions unrelated to purchase order receipts may be from Payables, Inventory, or Work in Process (depending on your installation). After you have researched the reported accrual balances, you can use the Accrual Write–Offs window to indicate which entries you wish to remove and write off from this report. And, after you have written off these entries, you can use the Accrual Write–Off Report as supporting detail for your manual journal entry.

The Accrual Reconciliation Report helps you monitor potential problems with purchasing and receiving activities that can affect the accuracy of your A/P accrual accounts. You can review this report to identify the following problems in receiving, purchasing, inventory, work in process, or accounts payable transactions:
• Quantities differ between receipts and invoices
• Discrepancies in supplier billing
• Accounts payable matched invoice to wrong purchase order or purchase order line, or the wrong receipt or receipt line
• Receiving clerk received against the wrong purchase order or wrong purchase order line
• Miscellaneous inventory or work in process transactions that do not belong to the accrual accounts
• Payables entries for sales tax and freight that do not belong to the accrual accounts.

If you use Inventory, this report automatically reconciles your activity for all inventory organizations. If you use Work in Process for outside processing receipts, this report also reconciles all work in process activity for all organizations.

In addition, the Accrual Reconciliation Report supports multiple A/P accrual accounts. In this case, the report groups information by each account. When you define your purchase order requisitions or orders, the Account Generator processes create your A/P accrual accounts. Typically, you use only one A/P accrual account for all of your inventory organizations. However, for certain situations you may want to use more.

POXACWRO – Accrual Write-off form and report
The Accrual Write–Off Report to provide supporting detail for your write–off journal entries. The process is as follows. First, you analyze the Accrual Reconciliation Report for transactions that you should expense out of the accrual accounts. After you have researched the reported accrual balances, you then use the Accrual Write–Off window to indicate which entries you wish to remove and write off from this report. And, after you have written off these entries, you use the Accrual Write–Off Report as supporting detail for your manual journal entry.

POXRRCVV – Receiving Value Report
The Receiving Value Report shows item quantity, valuation, and detailed receipt information for your receiving inspection location.

POXRVRVD – Receiving Value Report by Destination Account
The Receiving Value Report by Destination Account lists received items by purchase order destination and distribution account. If you accrue both inventory and expense at the time of receipt, you may have both inventory and expense as part of your receiving valuation account. The Receiving Value Report by Destination Account allocates your receiving balances by the purchase order line inventory, expense accounts, and quantities. You can then use this report to manually re class the receiving valuation account to the appropriate expense accounts.

 

PROCESS DEFINITIONS

PORCPTWF – Confirm Receipts Workflow Select Orders Process
The Self–Service Purchasing Confirm Receipts Workflow Select Orders process in Purchasing must be running in order to use the Confirm Receipts workflow. The Confirm Receipts workflow sends notifications through the Web, e–mail, or Notification Details Web page (accessible through the Notifications Summary menu in Purchasing) to requester or buyers who create requisitions in Purchasing or iProcurement. It lets people know they should have received an item.

The Confirm Receipts workflow sends notifications for items with a Destination or Deliver–To Type of Expense, a Routing of Direct Delivery, and a Need–By date that is equal to or later than today’s date.

POXPOIV – Payment on Receipt
Payment on Receipt enables you to automatically create standard, unapproved invoices for payment of goods based on receipt transactions. Invoices are created using a combination of receipt and
purchase order information, eliminating duplicate manual data entry and ensuring accurate and timely data processing. Payment on Receipt is also known as Evaluated Receipt Settlement (ERS) and Self Billing. You can automatically create invoices with multiple items and distribution lines, and include tax. You define which supplier sites participate in Payment on Receipt and enforce matching rules to ensure the proper payments are made to the suppliers.

The Pay on Receipt Auto Invoice program automatically creates an invoice batch depending on the options in the Payables Options window. Invoice count and invoice total are calculated automatically.

RVCTP or RTP – Receiving Transaction Processor
Use the Receiving Transaction Processor to process your pending or unprocessed receiving transactions. How the Receiving Transaction Processor handles these transactions depends on the processing mode, which is a profile option that you can set at the site, application, responsibility, and user levels.

You can set Standard Report Submission parameters to run the Receiving Transaction Processor at specified intervals so that your pending transactions are processed as often as required.

RVCACR – Receipt Accruals – Period-End Process
Use the Receipt Accruals – Period–End process to create period–end accruals for your uninvoiced receipts for Expense distributions. Purchasing creates an accrual journal entry in your general ledger for each uninvoiced receipt you choose using this form

Your Technical or Functional Questions ?


Need Help to understand anything ? Put your question here , I receive your comment in my email and I will reply on that email directly to you.





Oracle R12 – Some good Question on Supplier – Bank Setups


Where is Supplier Bank Information Stored in Release 12?

The supplier bank account information is in the table: IBY_EXT_BANK_ACCOUNTS, the bank and bank branches information is in the table: HZ_PARTIES.

The bank branch number can be found in the table: HZ_ORGANIZATION_PROFILES

The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created. This table also contains data retrieved from Dun & Bradstreet using either the Dun & Bradstreet online or batch download methods. Historical data for the organization can also be stored in this table. Each time organization information is changed, the effective end date column for the original record is updated and a new record that contains the updated information is created.

The column name is BANK_OR_BRANCH_NUMBER VARCHAR2 (30) Stores bank number for banks and branch number for bank branches, primarily ABA number for US bank branches.

Where are Supplier (External) Bank Accounts Created?

In R12, Internal bank accounts are now created in Cash Management (Setup -> Banks).  Where are Supplier (or External) bank accounts created?

Supplier (or External) bank accounts are created in Payables, in the Supplier Entry forms.  In the Payables Manager responsibility:

1. Navigate to Suppliers -> Entry.
2. Query or create your supplier.
3. Click on Banking Details and then choose Create.

After you have created the bank account, you can assign the bank account to the supplier site.

How do you import Supplier Bank Accounts during Supplier and Supplier Site Open Interface?

After the Supplier or Supplier Site is validated and a rows entered in the various AP and HZ tables, a Payee is created in IBY (the new Payments application) for the Supplier or Supplier Site.

If the Payee is successfully created, we then check to see if there are any corresponding rows in
IBY_TEMP_EXT_BANK_ACCTS. If there are, we call an IBY API to create the Bank Account and associate it with the Payee.

So to import supplier bank accounts during Supplier and Supplier Site Open Interface, you can populate the IBY_TEMP_EXT_BANK_ACCTS table.

A row in IBY_TEMP_EXT_BANK_ACCTS is said to be associated with the Supplier or Supplier Site if the column IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref1 is equal to either AP_SUPPLIERS_INT.vendor_interface_id for Suppliers or  IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref2 is equal to AP_SUPPLIER_SITES_INT.vendor_site_interface_id for Supplier Sites.

The bank and bank branch referenced in IBY_TEMP_EXT_BANK_ACCTS must already exist in the system.  There is no functionality in the Bank Account Import to create the bank and/or bank branch. This functionality associates the new supplier to an existing bank and/or bank branch.

How to enable the Supplier bank Account default to the Payment Schedules tab

A review of the current code, underlying in ibydiscb.pls ie IBY_DISBURSEMENT_COMP_PUB, depicts that the Supplier bank Account defaults to the Payment Schedules tab in the remit to bank Account only if any of the below mentioned two conditions are satisfied;
1. The Supplier bank Account has no currency associated to it
Or
2. The currency code associated to the Supplier Bank Account is the same as the Payment Currency
at the Invoice level

How to Query the Bank Account at the Supplier Site Level in SQL

R12 a Supplier Site is stored, in TCA, as a Party_Site. The Party Site has the Party ID of the Party that represents the Supplier record.
SELECT BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM
FROM IBY_EXT_BANK_ACCOUNTS
WHERE EXT_BANK_ACCOUNT_ID IN
(SELECT EXT_BANK_ACCOUNT_ID
FROM IBY_ACCOUNT_OWNERS
WHERE ACCOUNT_OWNER_PARTY_ID IN
(SELECT party_id
FROM hz_party_sites
WHERE party_site_name = '<site name>'))