query we can find the custom top path in oracle apps.

query we can find the custom top path.

select distinct variable_name, value

from fnd_env_context

where variable_name like '%CUS%_TOP' --escape '\'

order by variable_name;

Get concurrent request id in pl sql program in oracle

FND_PROFILE and FND_GLOBAL values

Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');
   fnd_profile.value('MFG_ORGANIZATION_ID');
   fnd_profile.value('ORG_ID');
   fnd_profile.value('LOGIN_ID');
   fnd_profile.value('USER_ID');
   fnd_profile.value('USERNAME');
   fnd_profile.value('CONCURRENT_REQUEST_ID');
   fnd_profile.value('GL_SET_OF_BKS_ID');
   fnd_profile.value('SO_ORGANIZATION_ID');
   fnd_profile.value('APPL_SHRT_NAME');
   fnd_profile.value('RESP_NAME');
   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;
   FND_GLOBAL.APPS_INTIALIZE;
   FND_GLOBAL.LOGIN_ID;
   FND_GLOBAL.CONC_LOGIN_ID;
   FND_GLOBAL.PROG_APPL_ID;
   FND_GLOBAL.CONC_PROGRAM_ID;
   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;
   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;
   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;
   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');
   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

   v_resp_appl_id  := fnd_global.resp_appl_id;
   v_resp_id       := fnd_global.resp_id;
   v_user_id       := fnd_global.user_id;
   
   FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);

link between order table,customer table and shipping tables in oracle apps r12

link between OE ,HZ AND WSH Tables in oracle apps r12

select DISTINCT wnd.name shipper_no,
       wnd.confirm_date,
       hp.party_name,
       hcsua.location "Customer address",
       hl.address1,
       hl.city,
       hl.state,
       hl.postal_code,
       hl.country,
       ool.header_id,
       msi.segment1 L_L_Item_No,
       wlp.license_plate_number,
       ool.ordered_item "Customer_Part_No",
       wda.delivery_id,
       msi.inventory_item_id,
     ool.ordered_item "CUST_1",
   msi.segment1 "ITEM_1"
  from wsh_new_deliveries wnd,
       hz_cust_accounts_all hca,
       hz_parties hp,
       hz_locations hl,
       wsh_delivery_assignments wda,
       wsh_delivery_details wdd,
       oe_order_lines_all ool,
       mtl_system_items_b msi,
       mtl_parameters mp,
       wsh_delivery_details wdd_p,
       wms_license_plate_numbers wlp,
        hz_cust_site_uses_all hcsua
 where wnd.customer_id = hca.cust_account_id
   and hca.party_id = hp.party_id
   and wnd.delivery_id = wda.delivery_id
   and wda.delivery_detail_id = wdd.delivery_detail_id
   and hl.location_id = wnd.ultimate_dropoff_location_id
   and wdd.source_line_id = ool.line_id
   and wdd.source_header_id = ool.header_id
   and ool.inventory_item_id = elss.inventory_item_id
   and ool.inventory_item_id = msi.inventory_item_id
   and ool.ship_from_org_id = msi.organization_id
   and mp.organization_code='000'
   and wda.parent_delivery_detail_id = wdd_p.delivery_detail_id
   and wdd_p.lpn_id = wlp.lpn_id
   and ool.ship_to_org_id = hcsua.site_use_id(+)
   and wnd.name='7453179';

Order Management Tables

Order Management Tables. Entered
oe_order_headers_all 1 record created in header table
oe_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_transactions
mtl_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 staged
mtl_material_transactions On the ship confirm form, check Ship all box
wsh_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 for
ra_customer_trx_lines_all specific batch transaction tables get populated
Price Details
qp_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 Phone
hz_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 Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments
Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_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 Orders
oe_order_lines_all Cancel Order Details