Mar 30, 2010

How to Set Organization? SET MO.

/*
select * from fnd_user where user_id=4080
select * from fnd_responsibility where responsibility_id=20707
select * from fnd_application where application_id=201
*/

begin
  fnd_profile.put('USER_ID', 0);--SYSADMIN
  fnd_profile.put('RESP_ID', 20432);--PA
  fnd_profile.put('RESP_APPL_ID', 275);--PA
  apps.fnd_client_info.set_org_context(to_char(183));
end;
/

begin
dbms_application_info.set_client_info('Org_id');
end;



To Retriew the records from Org dependent Views on Sql Developer/Toad/Sql * Plus

begin
fnd_client_info.set_org_context(Org_id);
end;

Mar 29, 2010

Data Structure of Oracle Receivables


What is Data Structure of Oracle Receivables

Ra_customers
: Customers > Standard
This table stores customer information.
Some columns of interest includes :
·         Customer_name
·         Customer_number
·         Status
·         Customer_prospect_code
·         Customer_type
·         Orig_system_reference (for imported customers from an external source)


Ra_addresses_all
: Customers > Standard
This table stores customer address information and your remit-to addresses.
Some columns of interest includes :
·         Status
·         Orig_system_reference  (for imported customer addresses from an external source)
·         Address1 to address4
·         City
·         State
·         Postal_code


Ra_site_uses_all
: Customers > Standard
This table stores the customer’s site and site purpose.  You must have one row for each address. A customer must have one bill to address for Receivables.  A customer must have one ship to address and one bill to address for Order Entry.
Some columns of interest includes :
·         Site_use_code (BILL_TO, SHIP_TO, STMTS, DUN/LEGAL)
·         Primary_flag
·         Status
·         Location


Ra_customer_trx_all
: Transactions > Transactions
This table stores invoice, debit memo, chargeback, commitment and credit memo header information.
Some columns of interest includes :
·         Cust_trx_type_id (link to ra_cust_trx_types_all)
·         Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
·         Terms_id (link to ra_terms)
·         Trx_number (invoice number)
·         Trx_date (invoice date)


Ra_customer_trx_lines_all
: Transactions > Transactions
This table stores the invoice, debit memo, chargeback, commitment and credit memo line information.
Some columns of interest includes :
·         Line_number
·         Description
·         Quantity_ordered
·         Quantity_credited
·         Quantity_invoiced
·         Unit_standard_price
·         Unit_selling_price
·         Line_type
·         Extended_amount
·         Revenue_amount


Ra_cust_trx_line_gl_dist_all
: Transactions > Transactions
This table stores the accounting information for revenue, unearned revenue, unbilled receivables, receivables, charges, freight and tax for each invoice or credit memo line.
Some columns of interest includes :
·         Amount_gl_date
·         Gl_posted_date
·         Account_class (CHARGES/FREIGHT/TAX/REC/REV/UNBILL/UNEARN)
·         Acctd_amount (functional currency)


Ar_cash_receipts
: Receipts > Receipts
This table stores the payment information.
Some columns of interest includes :
·         Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
·         Status (APP, UNAPP, UNID, NSF, STOP, REV)
·         Type (CASH, MISC)
·         Receipt_number
·         Amount
·         Currency_code
·         Pay_from_customer
·         Receipt_date


Ar_receivable_applications
: Receipts > Receipts
This table stores accounting entries for cash and credit memo applications.
Some columns of interest includes :
·         Amount_applied
·         Line_applied
·         Tax_applied
·         Application_type
·         Display
·         Gl_date
·         Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)


Ar_payment_schedules
: Transactions > Transactions and Receipts > Receipts
This table stores all transactions except adjustments and miscellaneous cash receipts.  This table is updated when a transaction occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt.
Some columns of interest includes :
·         Amount_due_original
·         Status
·         Class (DEP, DM, PMT, GUAR, CM, CB, INV)
·         Due_date
·         Amount_due_remaining
·         Invoice_currency_code
·         Amount_applied
·         Anmount_credited
·         Amount_adjusted

Data Structure of Oracle Order Entry

Data Structure of  Oracle Order Entry


So_headers_all
: Orders, Returns > Orders, Returns
This table stores the orders and returns information.
The s1 to s30 and s1_date to s30_date relates to the order cycle status.
Some columns of interest includes :
·         Order_category (I, P. S, R, RMA)
·         Order_number
·         Purchase_order_num
·         Original_system_source_code (for imported orders from an external source)
·         Original_system_reference (for imported orders from an external source)
·         Order_type_id (link to so_order_types_all for order type)
·         Date_ordered
·         S1 to s30
·         S1_date to s30_date


So_lines_all
: Orders, Returns > Orders, Returns
This table stores the orders and returns line information.
The s1 to s30 and s1_date to s30_date relates to the order line cycle status.
Some columns of interest includes :
·         Line_type_code (DETAIL, PARENT, REGULAR, RETURN)
·         Ordered_quantity
·         Cancelled_quantity
·         Selling_price
·         Price_list_id (links to so_price_lists for price list)
·         Schedule_date
·         Promise_date


So_line_details
: Orders, Returns > Orders, Returns
This table stores order scheduling information.
Some columns of interest includes :
·         Released_flag
·         Quantity
·         Schedule_date
·         Delivery (link to mtl_demand for reservation details)


So_picking_batches_all
: Shipping > Release Sales Orders or Shipping > Release Sales Orders, SRS
This table stores the batch of orders that have been pick released.
The header_count column indicates the number of picking headers are contained in a picking batch.

So_picking_headers_all
: Shipping > Release Sales Orders or Shipping > Release Sales Orders, SRS
This table stores the picking headers within a picking batch.
Some columns of interest includes :
·         Status_code (BACKORDERED, BACKORDER RELEASE, CLOSED, OPEN, PENDING, IN PROGRESS)
·         Pick_slip_number
·         Picked_by (link to per_people_f for picked by user)
·         Date_released
·         Date_confirmed
·         Date_shipped


So_picking_lines_all
: Shipping > Release Sales Orders or Shipping > Release Sales Orders, SRS
This table stores the picking lines for a picking header. 
Some columns of interest includes :
·         Requested_quantity
·         shipped_quantity
·         Date_requested
·         Cancelled_quantity


So_picking_line_details
: Shipping > Release Sales Orders or Shipping > Release Sales Orders, SRS
This table stores the location for the picking lines that have been reserved.
Some columns of interest includes :
·         Requested_quantity
·         Serial_number


Wsh_departure
: Shipping > Departure Planning> Departure Planning or Shipping > Departure Planning > New Departures
This table stores departure information for departure planning.
Some columns of interest includes :
·         Name
·         Planned_departure_date
·         Actual_depature_date


Wsh_delivery
: Shipping > Departure Planning> Departure Planning or Shipping > Departure Planning > New Deliveries
This table stores delivery information within a departure.
 Some columns of interest includes :
·         Name
·         Planned_departure_date
·         Actual_depature_date
·         Waybill

Data Structure of Oracle Inventory

Data Structure of  Oracle Inventory

 


Org_organization_definitions
: Setup > Organizations > Parameters
This view contains basic information on all inventory organisations.
Some columns of interest includes :
·         Organization_code
·         Organization_name
·         Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
·         Inventory_enabled_flag

Mtl_secondary_inventories
: Setup > Organizations > Subinventories
This table stores all subinventory information for an inventory organisation.
Some columns of interest includes :
·         Secondary_inventory_name
·         Description

Mtl_material_transactions
: Transactions > Material Transactions (Inquiry)
This table stores all inventory transactions including cost updates.
Some columns of interest includes :
·         Transaction_quantity
·         Transaction_type_id
·         Transaction_source_type_id
·         Transaction_source_name


Mtl_transaction_accounts
: Transactions > Material Distributions (Inquiry)
This table stores the inventory accounting information.  There are two rows in this table for each transaction in mtl_material_transactions.
Some columns of interest includes :
·         Transaction_date
·         Gl_batch_id
·         Accounting_line_type
·         Base_transaction_value


Mtl_system_items
: Items > Master Items or Items > Organization Items
This table stores the item definition. An item must exist in an inventory organisation.
Your item number is stored in the columns segment1 to segment20 depending on your application configuration.  If you have configured your items to have to segments then you may be using segment1 and segment2
Some columns of interest includes :
·         Segment1 to segment20
·         Description
·         Invetory_item_flag
·         Purchasing_item_flag
·         Inventory_asset_flag
·         Stock_enabled_flag
·         Invoiceable_item_flag
·         Shippable_item_flag
·         So_transaction_flag
·         Mtl_transactions_enabled_flag
·         Primary_unit_of_measure


Mtl_onhand_quantities
: On-hand, Availability > On-hand Quantities
This table stores quantity on hand in a location for each item.
Some columns of interest includes :
·         Date_received
·         Transaction_quantity
·         Subinventory_code


Cst_item_costs
: Costs > Item Costs
This table stores the item cost information.  Note that there can be multiple costs per item and the actual cost is where the cost type is Frozen.
Some columns of interest includes :
·         Cost_type_id (link to cst_cost_types)
·         Item_cost

Data Structure of Oracle Purchasing


Oracle Purchasing

 


Po_vendors
: Supply Base > Suppliers
This table stores supplier information. 
Some columns of interest includes :
·         Segment1 (supplier number)
·         Vendor_name
·         Terms_id
·         Vendor_type
·         Ship_to_location (link to hr_locations for location information)
·         Bill_to_location (link to hr_locations for location information)

Po_vendor_sites_all
: Supply Base > Suppliers
This table stores supplier sites information.
Some columns of interest includes :
·         Pay_site_flag
·         Purchasing_site_flag
·         Address_line1 to address_line3
·         City
·         State
·         Area_code
·         Zip


Po_headers_all
: Purchase Orders > Purchase Orders
This table stores the seven types of purchasing documents such as Purchase Order and Blanket Agreement.
Segment1 is the document number (i.e. purchase order number)
Some columns of interest includes :
·         Agent_id (link to per_people_f for the buyer)
·         Type_lookup_code


 Po_lines_all
: Purchase Orders > Purchase Orders
This table stores purchasing document lines. 
Some columns of interest includes :
·         Line_num
·         Item_description
·         Unit_price
·         Unit_meas_lookup_code (unit of measure)
·         Quantity
·         Item_id (link to mtl_system_items for the item number)
·         Category_id (link to mtl_categories for the category name)


Po_line_locations_all
: Purchase Orders > Purchase Orders
This table stores purchase order shipment schedules and blanket agreement price breaks.  A purchase order is closed when QUANTITY is equal to QUANTITY_RECEIVED.
Some columns of interest includes :
·         Quantity
·         Quantity_accepted
·         Quantity_received
·         Quantity_cancelled
·         Need_by_date
·         Ship_to_organization_id (link to org_organization_definitions for the organization code)


Po_distributions_all
: Purchase Orders > Purchase Orders
This table stores the accounting information on a purchase order shipment.  This table is used for Standard and Planned Purchase Orders and Planned and Blanket Purchase Order Release.
Some columns of interest includes :
·         Quantity_ordered
·         Quantity_billed
·         Amount_billed
·         Quantity_delivered
·         Quantity_cancelled
·         Destination_organization_id (link to org_organization_definitions for the organization code)
·         Destination_subinventory

Rcv_shipment_headers
: Receiving > Receipts
This table stores the receiving information. The three receipt sources are Supplier, Inventory and Internal Order.  There is one receipt header per receipt source.
Some columns of interest includes :
·         Receipt_num
·         Shipment_num
·         Receipt_source_code
·         Shipped_date
·         Ship_to_org_id


Rcv_shipment_lines
: Receiving > Receipts
This table stores information about items that have been shipped and/or received from a receipt source.
Some columns of interest includes :
·         Line_num
·         Quantity_shipped
·         Unit_of_measure
·         Item_id (link to mtl_system_items for item number)
·         To_organization_id (link to org_organization_definitions for organization code)
·         To_subinventory
·         Shipment_line_status_code (EXPECTED, FULLY RECEIVED, PARTIALLY RECEIVED)
·         Quantity_received
·         Quantity_shipped




OraApps Search

Custom Search

Search This Blog