Understanding ERP

Understanding ERP
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.
Ocassionally 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 1000employees and have a huge warehouse to maintain your inventory. Do you think you can manage all that information using pen and paper? Absolutely not possible! Agree?

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 Applications 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? It is managed by Oracle Human Resources. So by now you might have got an idea - 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.

To simplify, 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 (i.e. a form of money) coming in, when your customer sends payment it is transfered 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 to meet the needs of today's global business corporations

(Source:from internet)

Purchasing Setup Checklist

Purchasing Setup Checklist
1. Define set of books
2. Define descriptive flexfields
3. Define organizations
4. Define cross-reference types
5. Define profile options
6. Define financials options
7. Define accounting flexfield combinations
8. Define tax names
9. Define payment terms
10. Open and close General Ledger (GL) accounting periods
11. Open purchasing and inventory accounting periods
12. Define locations
13. Define location associations
14. Define job flexfield
15. Define jobs
16. Define position flexfield
17. Define positions
18. Define position hierarchies
19. Enter employee
20. Define item categories flexfield
21. Define category
22. Define category sets
23. Define default category set
24. Define control rules and groups
25. Define position controls
26. Fill employee hierarchy
27. Define usernames
28. Define purchasing lookup codes
29. Define freight carriers
30. Define inspection codes
31. Define hazard classes
32. Define United Nations (UN) numbers
33. Define standard notes
34. Define unit of measure classes
35. Define units of measure
36. Define unit of measure conversions
37. Define system items flexfield
38. Define item attribute controls
39. Define item templates
40. Define item catalog flexfield
41. Define item catalog groups
42. Define buyers
43. Define item
44. Define item relationships
45. Define line types
46. Define document controls
47. Start autosubmit
48. Define purchasing options
49. Define receiving options
50. Enter vendors
51. Define your transactions reasons
52. Set up Oracle Workflow
53. Request your receiving transaction processor
54. Define your concurrent process resubmission interval

Item upload thru Item interface

Item upload thru Item interface:

1. Gather all your data in excel sheet.

2. We need to enter data in the table mtl_system_items_interface. Below is the statement.(Here extra dta are entered but can be increase or decrease more data but some mandatory are to be needed.

insert into mtl_system_items_interface(item_number,description,transaction_type,organization_id,organization_code,unit_weight,weight_uom_code,
primary_unit_of_measure,attribute_category,process_flag,set_process_id,attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15)

3. To enter the data in the table best way is to first enter data in the customised table and then insert in the mtl_system_items_interface table.

Below is the example:

create table X

(ITEM VARCHAR2(200),
DESCRIPTION VARCHAR2(300),
TRANS VARCHAR2(200),
ORGID VARCHAR2(200),
ORGCODE VARCHAR2(200),
UNIT_WEIGHT VARCHAR2(200),
WEIGHT_UOM_CODE VARCHAR2(200),
PRIMARY_UNIT_OF_MEASURE VARCHAR2(200),
ATTCAT VARCHAR2(200),PROFLAG VARCHAR2(200),
SETPROID VARCHAR2(200),
ATTRIBUTE1 VARCHAR2(200),
ATTRIBUTE2 VARCHAR2(200),
ATTRIBUTE3 VARCHAR2(200),
ATTRIBUTE4 VARCHAR2(200),
ATTRIBUTE5 VARCHAR2(200),
ATTRIBUTE6 VARCHAR2(200),
ATTRIBUTE7 VARCHAR2(200),
ATTRIBUTE8 VARCHAR2(200),
ATTRIBUTE9 VARCHAR2(200),
ATTRIBUTE10 VARCHAR2(200),
ATTRIBUTE11 VARCHAR2(200),
ATTRIBUTE12 VARCHAR2(200),
ATTRIBUTE13 VARCHAR2(200),
ATTRIBUTE14 VARCHAR2(200),
ATTRIBUTE15 VARCHAR2(200))

Data can be enter in the table X through below command:@C:\Prod_All_length_items.txt;

where the Prod_All_length_items.txt is a text file stored in the c drive containg all the rows to be inserted in the table X in below format

Insert into Length_Items values('AL100-100-10-10*CA*R','ANGLE 100MM X 100MM X 10MM, ASTM A572 GR50 X MM','CREATE','230','CAN','CAN Length
Raw Material','15','KG','Millimeter','CAN','1','88888','15','L100X100X10','ASTM A572 GR50');

Then insert data as below statement:
insert into mtl_system_items_interface(item_number,description,transaction_type,organization_id,organization_code,unit_weight,weight_uom_code,
primary_unit_of_measure,attribute_category,process_flag,set_process_id,attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,attribute12,attribute13,attribute14,attribute15)select * from x

4. After data are entered in the mtl_system_items_interface check the data

select * from mtl_system_items_interface.

5. To import the items follow the below navigation

Under Inventory responsibility:

Items-->Import--> Import Items
In the parameter all will be default except Process set. Enter Set_process_id value which was entered in the mtl_system_items_interface.
The last parameter is by default 1 which is for creating new item. If you want to update existing one enter value 2 here.

6. After completion the request see the log if any error is there. Correct that fault and update the process flag as 1 in the mtl_system_items_interface table and again import the items.

Error can be seen in the below table also:
select * from MTL_INTERFACE_ERRORS

update mtl_system_items_interfaceset process_flag=1where organization_id=230

7. Before creating items in the child org first item must be created in master org then same process must be followed for other organization by simply updating the org_id in the mtl_system_items_interface table.

Category Updation for existing item thru interface:

1. Arrange data in the correct format.

2. We need to enter data in the table MTL_ITEM_CATEGORIES_INTERFACE. Below is the statement.(Here extra data are entered but can be increase
or decrease more data but some mandatory are to be needed.

insert into MTL_ITEM_CATEGORIES_INTERFACE(inventory_item_id,category_set_id, category_id,process_flag,organization_id,OLD_CATEGORY_ID,set_process_id, transaction_type)values('341980',1,1136,1,22,1135,88888,'UPDATE')
insert into MTL_ITEM_CATEGORIES_INTERFACE(item_number,category_set_id, category_id,process_flag,organization_id,OLD_CATEGORY_ID,set_process_id, transaction_type)values('20-01*16*PC',1,1135,1,22,1136,88888,'UPDATE')

3. To import the items follow the below navigation
Under Inventory responsibility:
Items-->Import--> Item Category Assignment Open Interface
In the parameter all will be default except Record set id. Enter Record set id value which was entered in the MTL_ITEM_CATEGORIES_INTERFACE as set_process_id.

create table CAT_INT
( item_number VARCHAR2(200),
CATEGORY_NAME VARCHAR2(200),
category_set_id VARCHAR2(300),
process_flag VARCHAR2(200),
organization_id VARCHAR2(200),
OLD_CATEGORY_ID VARCHAR2(200),
set_process_id VARCHAR2(200),
transaction_type VARCHAR2(200))

insert into CAT_INT values ('35-05*723*PC','20.MP FITTING.RF-3',1,1,22,1,88888,'UPDATE');