Tuesday, 3 May 2011

Purchase Order tables


Purchase Order

Purchase Order Tables

1.PO_REQUISITION_HEADESR_ALL
2.PO_REQUISITION_LINES_ALL

When you raise the Requisition these tables effected(PO_REQUISITION_HEADER_ID) is the join between the tables
4.PO_REQ_DISTRIBUTION_ALL
It distribute the Requisition Account information.
5.PO_HEADERS_ALL (PO_HEADER_ID)
6.PO_LINES_ALL
When PO Created PO# stores in tables(PO_HEADER_ID) is the join for tables
7.PO_DISTRIBUTIONS_ALL
It will distribute the PO# Account information
8.PO_ACTION_HISTORY
Here you can get Approvals notification Status
9.PO_VENDORS_ALL
(VENDOR_ID)10.PO_VENDOR_SITES_ALL(VENDOR_SITE_ID)11.PO_VENDOR_CONTACTS_ALL (VENDOR_CONTACT_ID)
Contacts Vendor ,Vendor site and conatct information(Vendor_id,Vendor_contact_id,VEDNOR_SITE_ID)
12.RCV_SHIPMENT_HEADERS_ALL(SHIPMENT_HEADER_ID)
common information about the source of your receipts or expected receipts
13.RCV_SHPMENT_LINES_ALLwhen you issue the recept The recepent number , Shipment location stores in the table.
14.RCV_TRANSACTIONS(TRANSACTION_ID)
15.MTL_MATERIAL_TRANSACTIONS 
(TRANSACTION_ID)Stores material transaction information (Transaction_id)
16.PO_Release_allTO see the which purchase order is released.
17.PO_AGENTScontains information about buyers and purchasing managers.
18.PO_NOTIFICATION_CONTROLScontains information about the notification control rules for blanket, planned, and contract purchase orders.
19.PO_APPROVAL_LIST_HEADERS
list of approvers for the purchasing document used for requisition approvals only
20.PO_APPROVAL_LIST_LINESapproval list lines for the requisition approval list.
21.PO_POSITION_CONTROLS_ALLassignment of control groups to jobs and/or positions
22.PO_DOCUMENT_TYPES_ALL_Bdefault, control, and option information you provide to customize
23.PO_CONTROL_GROUPS_ALLcontrol groups you use in your business

24.PO_RFQ_VENDORS
Information about the set of suppliers assigned to a request for quotation (RFQ)
25.PO_VENDOR_LIST_HEADERS(VENDOR_LIST_HEADER_ID)
stores information about supplier quotation lists you create.
26.FINANCIALS_SYSTEM_PARAMS_ALL
This Tables stores common and default information between AP and PO

OM Tables


OM Tables

Order Management Tables.

Entered
oe_order_headers_all 1 record created in header tableoe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.

Booked
oe_order_headers_all Booked_flag=Y Order booked.wsh_delivery_details Released_status Ready to release

Pick Released 
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release. 
mtl_reservations This is only soft reservations. No physical movement of stock

Full Transaction
mtl_material_transactions No records in mtl_material_transactionsmtl_txn_request_headers
mtl_txn_request_lines

wsh_delivery_details Released to warehouse.wsh_new_deliveries if Auto-Create is Yes then data populated.wsh_delivery_assignments deliveries get assigned

Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock


Ship Confirmed

wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from stagedmtl_material_transactions On the ship confirm form, check Ship all boxwsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped

oe_order_lines_all Shipped_quantity get populated.wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N


Autoinvoice

wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program forra_customer_trx_lines_all specific batch transaction tables get populated

Price Detailsqp_list_headers_b To Get Item Price Details.
qp_list_lines

Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.

Payment Terms
ra_terms Payment terms

AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.

Customer Information
hz_parties Get Customer information include name,contacts,Address and Phonehz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers

Document Sequence
fnd_document_sequences Document Sequence Numbersfnd_doc_sequence_categories
fnd_doc_sequence_assignments

Default rules for Price List
oe_def_attr_def_rules Price List Default Rulesoe_def_attr_condns
ak_object_attributes

End User Details 
csi_t_party_details To capture End user Details

Sales Credit Sales Credit Information(How much credit can get)oe_sales_credits

Attaching Documents 
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text

Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all

Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions

Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all

Hold Relaese
oe_hold_releases_all Hold released Sales Order.

Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.

Cancel Ordersoe_order_lines_all Cancel Order Details.

HELP-MENU


HELP-MENU
  • How to see application version, Form shortname and version of form
    (Help) About Oracle application
  • (Help)Diagnostics ->Examine
    • Pass the apps password and investigate the value for some variable.
  • (Help)Diagnostics ->Examine ….Chose $DESCRIPTIVE_FLEXFIELD$block
  • (Help)Diagnostics ->Examine ….Chose $ENVIRONMENT$block
    • This has all primary environment variables.
  • (Help)Diagnostics ->Examine ….Chose $PROFILE$block
    • this shows all profile options given to user who is accessing the application.
  • (Help)Diagnostics ->Examine ….Chose system block…choose LAST_QUERY field
    • This will shows last query used by the form.
  • (Help) Diagnostics -> Display Database error
    • This shows any database error message.
  • (Help) Diagnostics -> Trace(checkbox)
    • The options are on and off for forms tracing. Tracing file (.trc) is put into user dump folder.

Oracle Applications Video Clips

10 things Oracle Developers should never do


10 things Oracle Developers should never do

Oracle developer tips: Ten things you should never do

For the Oracle developer, customizing Oracle E-Business Suite applications can be very dangerous, especially if that Oracle developer is prone to making common rookie errors.

1. Don't run non-select commands in production without testing first 

Occasionally developers need to run non-select commands on standard tables, such as an update on an insert. The consultant said developers should never do this without testing the commands first on DEV or Test Instance.

"Make sure it works, and then run it in production.

2. Never change the definition of a standard database object

"You never should have a need to change a standard table," . "If you change a standard table, it's definite that the next relevant Patch you apply is going to break the system."

Developers may need to change a standard package to customize an application. Folks that need to do that should begin by creating a new package.

"But if you do change a standard package, I would say that in a 1,000-line standard package if you add five lines, make sure you add clear comments before the five lines you have added".

3. Don't leave open update commands in code editors
When developers have an open update command sitting in the Toad editor, there is a risk of running that update unintentionally.

"Have only your Select commands open in an editor". "When you have an update, just comment it out. When you need to run it, just uncomment for that time only."

All data manipulation commands will have to be built as scripts and tested thoroughly by the time they are run in production.

4. Don't execute scripts in production by yourself

When developers are faced with the task of running something in a production instance, it's always good to ask a colleague to sit next to you and watch out for any mistakes. Two pairs of eyes are better than one.

5. Don't forget how to deal with commands that take a long time to execute, that any command expected to take more than five minutes to execute will need to be built as a concurrent program or run under the VNC editor.
"Don't run [time-consuming commands] on a Toad or a SQL*Plus session". "It's good to run it in the background."

6. Don't forget to protect passwords

It's important not to give out production passwords to anyone unauthorized in the company or outside the company.

7. Do not leave your desk when you have a connection open on production
Before leaving the desk, execute the script needed and close the connection immediately.
In addition to the obvious security concerns, leaving a connection open and unattended can cause developers to forget the context of the session when they return.

"You might come back and start typing in commands thinking that it's a development instance [when it's not]" .

8. Don't give out customer data
Never give the data (financial numbers, credit cards, customer list, etc.) to anyone other than the authorized client personnel, especially via e-mail.

9. Don't forget to maintain version control

Maintain a good version control for all your code and document at least the essential details about your programs.

"Don't let the production instance be the only source of version control".

10. Develop Prod-sensitive connections to third party interfaces 
Oracle systems have live connectivity with third party systems like POS, 3PLs, payment systems etc.

"Make sure that these interfaces get inactivated when production databases are copied to other instances". "One way to do is to check for the SID name from the v$session table and inactivate the connection if it is not a production one."

Oracle Implemenation Stages not taught in training


Oracle Implemenation Stages not taught in training

Today we will be seeing the different stages that are available in an implementation project. A Project is not a short term activity to be completed in matter of days, it requires intensive analysis, thoughtful thinking and serious decision making in core and critical business activities. There decisions taken at every stage is critical and irreversible, there is no going back after
performing final step. We have to understand how a long term project is broken up in to phases and completed smoothly. We will see what are the different stages in an Oracle Application Implementation project in real time. i have taken Application Implementation Methodology (AIM) standards as the base for further explanations.

Application Implementation Methodology (AIM) has laid out various stages available in a project.

Stage 1 - Definition of Project Management
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Description
===========
The success to any project is a well thought of PROJECT PLAN at the very beginning considering the requirements and available resources. A Normal Contention of many people is that Project Plan should normally stage the Start Date and End Date and the Consideration, but in real time it is actually more than that, the whole project is broken in to different phases and for each phase a detailed plan is laid out which in turns account for the overall project plan. Project Plan must be revised whenever there is a Change in the contract or requirement of the client.

Documentation to be Prepared
============================
Project Plan (Finalized or Draft)
Write up about the Client and the Consultant.


Stage 2 - Operational Analysis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Description
========
This is the very very Critical stage of the Project. One must devote the maximum possible time to this stage. Any compromise on this stage may prove critical at later stage of the project. In this stage a Consultant, who is a total stranger to the client so far is going to make an effort to become an expert in understanding the client business every inch. Only when he masters the clients business, he will be in a position to think beyond it and provide solutions considering the impact it can have on overall business. In this stage a consultant must carryout following activities:

--> Clearly understand the contractual obligations made between the client and the consultant with respect to the work to be performed.
--> Detailed study on the Client business and Organisation Structure
--> Capture the current business process as they are following ( Also known as AS IS documentation)
--> Collect the requirements from the client, managers, end users as to what they expect from the Oracle Product
--> Requirements with respect to Functional Side as well as Technical Side is collected
--> Document the bottlenecks faced with the existing system in place
--> Most important point is that get the document signed from the client
( Note: Normally speaking once a requirement is expressed at this stage , it will be taken care by the consultant, however in real time, requirements gathering becomes a day to day activity in many clients place, which is highly unprofessional way of working)

Documentation to be Prepared
============================

RD 10 --> Current Financial and Operating Structure
RD 20 --> Current Business Baseline
RD 50 --> Business Requirements Scenario

Stage 3 - Solution Design
~~~~~~~~~~~~~~~~~~~~~~~~~


Description
===========

After making serious understanding and analysis on clients current business practices and future requirements, consultant must begin the process of providing solutions to each and every aspect. If an straight forward solution is not possible , think about the possibility of workaround. Enough analysis has to be one in drafting the new business process of the client which is going to be followed here after, it is also known as To Be Process.Once the solution is decided on, the same must be communicated to the client seeking acceptance. when the client is satisfied with the solution, consultant can put an end to his research work on providing alternate solutions.

Documentation to be Prepared
============================
BP 80 --> Business Process Design ( To be process )
BR 10 --> High Level Gap Analysis
BR 30 --> Business Requirements Mapping
MD 50 --> Application Extension Functional Design
MD 70 --> Application Extension Technical Design

Stage 4 - Building Stage
~~~~~~~~~~~~~~~~~~~~~~~~


Description
===========

Only at this stage the work of performing setups and access to the instance begins. Based on the Solutions and new business process ( To Be) designed at the previous stage, the consultant starts performing setting up of the Application. In case of a functional requirement, it will be setups from the front end, in case of technical requirements, sql scripts, customizations will be performed. Document all the setups that has been carried out at this stage, since it will be useful when the production Instance is configured or when there is a system crash or corruption of data or any hardware failure. After performing all required setups, the system has to be tested for its readiness to carry out operations

Documentation to be Prepared
=============================

BR 100 --> Application Setup
TE 20 --> Unit Test Script
TE 40 --> System Test Script
TE 50 --> System Integration Test Script
TE 120 --> System Integration Test Plan


Stage 5 - Transition Stage
~~~~~~~~~~~~~~~~~~~~~~~~~~

Description
===========

In this stage, client is exposed to the Applications for the first time to get the comfort level. In order to make the client to get a feel of the system, user guides, training manuals and training may be given in understanding how thier business is completly taken care by the product. Once the client is exposed to the Applications, they normally tend to show some interest in learning
new things, which can be used for testing the system. Once the client is satisfied with the Solutions and the way in which the system performs, he can express his willings to accept the system. In real time, acceptance will be taken up at two levels,
First Level - CRP - Conferance Room Piloting - Where the consultant gives a preview of the new system to the selected members of the client and get thier feedback.
Second Level - UAT - User Acceptance Testing - Here the consultant gives detailed demonstration on the new system to the users ( Super and End ), and get the acceptance from the users that the new system meet thier expectations.

Documentation to be Prepared
======================

TE 130 - User Acceptance Test Report


Stage 6 - Production Stage
~~~~~~~~~~~~~~~~~~~~~~~~~~

Description
===========

Once CRP and UAT is approved, it is the duty of the consultant to replicate the same in a new environment which will be used solely and exclusively for the Production purposes only. After making the production server ready with all setups, it is advised to clone the production server to an development or test server, so that any value addition or future testing can be carried out
separately in future. Once the production server is ready, it is given to the client to begin thier operations, which is termed as GO LIVE . In order to assist the client in adopting to the new system, consultant will provide them with User Manuals and also a support consultant for a short period of time if needed.
Once the production server is ready and used, the implementation gets over, and the client should give it in writing that he is satisfied with the implementation taken place, which is termed as sign off ( A Big Good Bye ) !!

Documentation to be Prepared
============================

User Manuals
Client's sign off on the Acceptance Certificate

NOTE:
=====
Documentations suggested in this post is recommended when AIM is adopted. Instead of the recommended documents, users can prepare documents based on thier methodology.
Nothing stated above is mandatory, every aspect is recommendatory in nature.

**********************************************

GENERALISED
===========

The Implementation Phase in a Oracle implementation project as per Oracle AIM.
1.Definition
2.Operation Analysis
3.Solution Design
4.Build
5.Transition
6.Production Process Overviews - Tasks & Deliverable

[BP] Business Process Architecture
[RD] Business Requirements Definition
[BR] Business Requirements Mapping
[TA] Application and Technical Architecture
[MD] Module Design and Build
[CV] Data Conversion
[DO] Documentation
[TE] Business System Testing
[PT] Performance Testing
[AP] Adoption and Learning
[PM] Production Migration

There are several deliverable from Oracle to deliver at the time of Implementation, But to provide all those documents is not required for Sensible Project.

To my understanding required documents needed to be delivered are

RD - 20 -Requirement Gathering document
BP - 80 or 90 - Future Business Mapping Document
MD - 50 - Functional Specification for Application Customization.
MD - 70 - Technical Specification for Customization.
CV-40-CV-60 - Data Collection Template
UAT - Training Scripts TA - Training Documents
BR - 100 Application Setup Documents
 


Its better if you have the AIM document generator with you. So that you can have all the document Templates handy. To download the AIM oracle documentation software go tohttp://www.ziddu.com/download/1682474/AIMtoolaset.EXE.html

Oracle Instances: Vision, Development, Test Production


Oracle Instances: Vision, Development, Test Production

Let us discuss something on the instances which a consultant normally works on...

Instance is an address for a location where Applications database is stored.
We might have heard of many instances such as Vision, Test, Development,CRP I, CRP II, UAT, Production etc.... but apart from the name there is no change in the behaviour of any of the Instances.

All instances carry the same technical features and functional setups, no difference what so ever in any of them, but why we call it by different names then ?
The different instance names are given based on the purpose for which it is used. Every instance is created with the same features and specifications, only naming is done based on the usage or the purpose.

We will see the standard or common purposes for which different instances are used in
an implementation.

Test Instance
It is a fresh instance which is used by the Implementation team to try out new setups and work arounds as per the clients business specifications and arriving at final solutions. Apart from that test instance is used for testing the performance of installation as well as other factors. The life of this instance starts from the first day of kick off and ends when the production begins or system has gone live.

Development InstanceIt is also a fresh instance when installed, but more or less it replicates the test or Production instance (normally cloned). The purpose is to make constant development in the current business process and the system performance and working out new methods in solving critical bottlenecks with the current system. The life of this instance is infinity, since there is no end to development, it is an ongoing process, it continues even after the system has gone live.

Production Instance
It is a fresh instance installed with an intention to share it with all the users of the client with fine tunning. This instance is different from Test and Development with respect to the number of users, size of the database, security features etc. The life of the production instance begins when the User Acceptance testing is done, and it continues till the company is in existence.

Vision Instance
It is different from all other instances because the installation comes with the data, whereas all other instances are Fresh. Even though there is no particular necessity to have this instance in an real time scenario, but it is better to have a vision instance always for the following reasons:

1) It serves as a practice for the DBA to find out the installation problems that occurs due to hardware failures.
2) Functional consultant often try out a small business scenarios to find out solutions, in order to try out these scenarios, they need to do all the mandatory setups in an instance then only they can perform it. If they had vision instance, they can use the existing setups to try out thier business scenarios
3) Vision instance also acts as a tutor to the Functional Consultants in case they are lost in performing critical setups.