, , , , , ,

Supplier , Supplier Bank and Supplier bank branch – Oracle Apps 11i

Select a.bank_account_name, a.bank_account_num, a.iban_number, a.bank_account_type,
a.currency_code, a.bank_account_name_alt, a.org_id, a.creation_date,
b.bank_name, b.bank_branch_name, b.bank_num , b.eft_swift_code,
b.address_line1, b.address_line2, b.city, b.county, b.state, b.zip,
b.country, c.vendor_id,d.vendor_name,d.segment1 AS SUPPLIER_NUM
from apps.ap_bank_accounts_all a,
apps.ap_bank_branches b,
apps.ap_bank_account_uses_all c,
apps. PO_VENDORS d
where a.org_id=xxxx
AND a.bank_branch_id = b.bank_branch_id
AND a.bank_account_id = c.external_bank_account_id
AND c.vendor_id=d.vendor_id
 AND c.end_date IS NULL
/by
, , , ,

Important Account Payables Tables -Part 2

AP_INTERFACE_REJECTIONS
Table Or View:  Table
Important columns: 

PARENT_TABLE : Reference to table in which the rejection occurred (AP_INVOICES_INTERFACE or AP_INVOICE_LINES_INTERFACE)
PARENT_ID NUMBER : Reference to invoice or invoice line identifier which was rejected (INVOICE_ID or INVOICE_LINE_ID)
REJECT_LOOKUP_CODE : Invoice rejection reason

Description: 

AP_INTERFACE_REJECTIONS stores information about invoice data from the AP_INVOICES_INTERFACE and
AP_INVOICE_LINES_INTERFACE tables which could not be processed by Payables Open Interface Import.
If you use Oracle e-Commerce Gateway, you can pass information from this table to your suppliers by submitting the Payables Open Interface Outbound Advice for rejected data.
You can purge data in this table by using the Payables Open Interface Purge.

AP_CHECKS_ALL

Table Or View: Table
Important columns: 

AMOUNT : Payment amount
BANK_ACCOUNT_ID : longer used
BANK_ACCOUNT_NAME : Bank account name
CHECK_ID NUMBER : Payment identifier
CHECK_NUMBER : Payment number
VENDOR_NAME :  Supplier name
VENDOR_SITE_CODE :  Supplier site code
PAYMENT_TYPE_FLAG : Type of payment
A – Payment Process Request
M – Manual
Q – Quick payment
R – Reunfund

Description: 

AP_CHECKS_ALL stores information about payments issued to suppliers or refunds received from suppliers.  You need one row for each payment you issue to a supplier or refund received from a supplier. Your Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers. Your Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment.
Your Oracle Payables application stores address information for all payments. If you allow changes to the supplier payment address on manual payments or Quick payments, your Oracle Payables application maintains the new address information in this table. Your Oracle Payables application uses BANK_ACCOUNT_NUM, BANK_NUM, and BANK_ACCOUNT_TYPE for the supplier’s bank information when you use the Electronic payment method. Your Oracle Payables application stores a dummy value for CHECK_STOCK_ID for refunds, thus, CHECK_STOCK_ID should not be treated as a foreign key to AP_CHECK_STOCKS_ALL in the case of refunds.
Payments are linked with invoices through AP_INVOICE_PAYMENTS_ALL

AP_SUPPLIERS

Table Or View: Table
Important columns: 

VENDOR_ID :  Yes Supplier unique identifier
SEGMENT1 : Supplier number

Description: 

AP_SUPPLIERS stores information about your supplier level attributes. Each row includes the purchasing, receiving, invoice, tax, classification, and general information. Oracle Purchasing uses this information to determine active suppliers.
This table replaces the old PO_VENDORS table. The supplier name, legal identifiers of the supplier will be stored in TCA and a reference to the party created in TCA will be stored in AP_SUPPLIERS.PARTY_ID, to link the party record in TCA.

 
PO_VENDORS
Table Or View: View
Important columns: 

VENDOR_ID
SEGMENT1

Description: 

PO_VENDORS is a view which contains selected columns of AP_SUPPLIERS

 

/by
, , , , ,

Shared Entities in Oracle EBS

Shared entities can be referenced by multiple products. These entities allow you to define broad-level structures that help you to include members when implementing the E-business suite. Which business unit will own specific data in a shared entity is at the discretion of the compnay. Employee data is always owned by human resources, provided the E-business suite is installed.

/by
,

What is Oracle Apps (ERP) : Basic Concepts

Lets take an example. Suppose you are running a small grocery shop, so the typical operation as a shop owner is you basically buy groceries from some big seller and stock it in your shop. Now people come to your shop for day-to-day needs and buy stuff from your shop at a slightly higher price than what you originally bought and stocked it in your shop.
Occasionally you may not be carrying items or run out of stock that people ask for so you make a note of it and promise the person to come back tomorrow and they will get their item. So far so good, now lets name some entities before we proceed and things get complicated. The big seller from whom you buy stock is called as Vendor, the people who come to your shop to buy things are known as customers, the stock in your shop is known as inventory.

So far we have identified few entities that play an active role in your day-to-day operations. As time goes by, your business expands and now you take orders over the phone and provide service to deliver the items to your customers, so you hire people to help you out in maintaining the inventory, do the delivery part and all the necessary stuff to keep the business running smoothly. The people you hire are known as employees.
So in this small shop, you typically manage the bookkeeping activities by hand using a notepad or something similar. Now imagine the same setup on a larger scale where you have more than 10,000 customers, have more than 1000 vendors, have more than 1000 employees and have a huge warehouse to maintain your inventory. Do you think you can manage all that information using pen and paper? Absolutely no way! Your business will come to a sudden stop sign.
To facilitate big businesses, companies like Oracle Corporation have created huge software known in the category of ERP (Enterprise Resource Planning) as Oracle Applications. Now coming to think of it, Oracle Apps is not one huge software, instead it is a collection of software known as modules that are integrated and talk to each other.
Now what is meant by integrated? First let us identify the modules by entities. For e.g Purchasing and Account Payables deal with the vendors since you typically purchase from vendors and eventually have to pay the dues. Oracle Purchasing handles all the requisitions and purchase orders to the vendors whereas Oracle Accounts Payables handles all the payments to the vendors.
Similarly Oracle Inventory deals with the items you maintain in stock, warehouse etc. Dealing with customers is handled collectively with the help of Oracle Receivables and Oracle Order Management. Order Management helps you collect all the information that your customer is ordering over the phone or webstore etc whereas Receivables help you collect the money for the orders that are delivered to the customers.
Now who maintains the paychecks, benefits of the 1000 employees? right! it is managed by Oracle Human Resources. So you get the idea by now that for each logical function there is a separate module that helps to execute and maintain that function.
So all the individual functions are being taken care but how do I know if I am making profit or loss? That’s where integration comes into play. There is another module known as Oracle General Ledger. This module receives information from all the different transaction modules and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc.
Just to simplify the explanation, when you pay your employees that payment is reported back to General Ledgers as cost i.e money going out, when you purchase inventory items the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items in your inventory it is transferred to GL as money coming in, when your customer sends payment it is transferred to GL as money coming in. So all the different transaction modules report to GL (General Ledger) as either money going in or money going out, the net result will tell you if you are making a profit or loss.
All the equipment, shops, warehouses, computers can be termed as Assets and they are managed by Oracle Fixed Assets. Initially Oracle Applications started as bunch of modules and as time passed by they added new modules for different and new functions growing out of the need for today’s internet world.
So if you come across a module that you are trying to learn and work on, first try to understand what business need is it trying to fulfill and then try to understand what the immediate modules that it interacts with. For e.g lets say you come across Oracle Cost Management module, you will learn that it helps to maintain the costs of items in your inventory and the immediate modules that it interacts with are Oracle Inventory (ofcourse), Oracle Bills of Material, Order Management and so on..
(Source: http://www.appsbi.com/what-is-oracle-apps-erp)
/by
, , , ,

Oracle Data & Migration

The sets of data required by Oracle Applications can be broadly classified in to 4 categories

  • Master Data : Item, Customer, Vendor, Bank Accounts, etc.
  • Opening Balances: On-Hand Quantity of Items, GL Balances, etc.
  • Open Transactions : Open Purchase Orders, Open Sales Order, Open Invoice etc.
  • Set-up Data :  Item Categories, Stock Locators

Similary source of data can be classified as

  • Existing Applications : DB2, Tally, SMS, etc.
  • Electronic Data : Comming through EDI in formats of Excel, Word, etc.
  • Hard Copy Data : In Registers
  • Non – Existent : Equipment Master, Collect and then migrate

The above diagram depicts the high level Data Migration approach from Legacy to ORACLE.
At a minimum, the data migration from Legacy to ORACLE consists of these tasks:

  • Extracting the data from Legacy data base.
  • Loading the extracted data into staging area.
  • Mapping and transformation of source data.
  • Migrating source data from staging area into target data base.
/by