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 using FND_GLOBAL

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);

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

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';