ORACLE TECH CENTER

Wednesday, 8 June 2016

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

Monday, 6 June 2016

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

Thursday, 26 May 2016

Query to find DFF

Let's say, we need to find Descriptive Flexfield (DFF) called, "Further Job Information".
In the following example, I am trying to get all the information for "US" context code.

The following query will display the DFF related information. You can try changing "fdfv.title" value too see different DFF.

-----------------------------------------------------------------------------

-- Query to find DFF information

-----------------------------------------------------------------------------

SELECT

       fdf.title                             "DFF Title",

       fdf.application_table_name            "Application Table",

       fdf.context_column_name               "Context Column Name",

       --

       fdfcu.descriptive_flex_context_code   "DFF Context Code",

       fdfcu.column_seq_num                  "Sequence",

       fdfcu.end_user_column_name            "Segment Name",

       fdfcu.application_column_name         "Column Name",

       --

       ffv.flex_value_set_name               "Value Set Name"

  FROM

       fnd_descr_flex_col_usage_vl   fdfcu,

     fnd_descriptive_flexs_vl      fdf,

       fnd_flex_value_sets           ffv

WHERE

       1 = 1

   --

   AND fdf.title = 'Further Job Information'        -- <change it>

   AND fdfcu.descriptive_flex_context_code = 'US'   -- <change it>

   AND fdfcu.enabled_flag = 'Y'

   --

   AND fdfcu.flex_value_set_id = ffv.flex_value_set_id

   AND fdfcu.descriptive_flexfield_name = fdf.descriptive_flexfield_name

   AND fdfcu.application_id = fdf.application_id

   --

ORDER BY

       fdfcu.descriptive_flexfield_name,

       fdfcu.descriptive_flex_context_code,

       fdfcu.column_seq_num;

Query to find AP Supplier Remittance Email Addresses

This following query will display all the active vendors' email addresses by their associated sites. This will work only in Oracle R12.

SELECT

-- s.vendor_id,

-- st.vendor_site_id,

-- s.party_id,

-- st.party_site_id,

s.vendor_name "Vendor Name",

s.segment1 "Vendor Number",

s.vendor_type_lookup_code "Vendor Type",

st.vendor_site_code "Vendor Site Code",

ou.name "Operating Unit",

iepa.remit_advice_delivery_method "Remittance Delivery Method",

iepa.remit_advice_email "Remittance Advice Email"

FROM

ap.ap_suppliers s,

ap.ap_supplier_sites_all st,

hr_operating_units ou,

iby.iby_external_payees_all iepa

WHERE

1=1

-- AND s.vendor_type_lookup_code = 'EMPLOYEE'

AND TRUNC (SYSDATE) BETWEEN TRUNC (s.start_date_active) AND TRUNC (NVL(s.end_date_active, SYSDATE+1))

AND s.enabled_flag = 'Y'

AND iepa.supplier_site_id = st.vendor_site_id

AND iepa.payee_party_id = s.party_id

AND st.org_id = ou.organization_id

AND st.vendor_id = s.vendor_id

ORDER BY s.vendor_name, st.vendor_site_code;

Delete concurrent program from backend

If you create an Executable without creating a concurrent program, the system will allow to delete the Executable. But once you create the Concurrent Program for that Executable, the system never allows you to delete the program -- it only gives the option to disable the Concurrent Program.

At that point, your only option is to delete the Concurrent Program and its Executable from the back-end. Following is a simple straight-forward query that you can use for deleting a Concurrent Program. This query first checks if the concurrent program and its executable exist in the system. If found, it will delete the program; if not found, it will just display a message.

In this example, 'XX_TEST' is my Concurrent Program's Short Name and 'XX' is the Application Short Name. You will have to use appropriate program name and application short name according to your need.

-------------------------------------------------------------------------------

-- delete concurrent program definition and executable from back-end

-------------------------------------------------------------------------------

-- syntax:

-- delete_program (program_short_name, application_short_name)

-- delete_executable (program_short_name, application_short_name)

-------------------------------------------------------------------------------

DECLARE

lv_prog_short_name VARCHAR2(240);

lv_appl_short_name VARCHAR2(240);

BEGIN

-- set the variables first

lv_prog_short_name := 'XX_TEST'; -- concurrent program short name

lv_appl_short_name := 'XX'; -- application short name

-- see if the program exists. if found, delete the program

IF fnd_program.program_exists (lv_prog_short_name, lv_appl_short_name) AND

fnd_program.executable_exists (lv_prog_short_name, lv_appl_short_name)

THEN

fnd_program.delete_program(lv_prog_short_name, lv_appl_short_name);

fnd_program.delete_executable(lv_prog_short_name, lv_appl_short_name);

COMMIT;

DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' deleted successfully');

-- if the program does not exist in the system

ELSE

DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' not found');

END IF;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);

END;

Query to find Custom Oracle Alert

The following query finds all enabled custom alerts. You can comment out the very last two lines (alr.enabled_flag and alr.created_by) to display all both enabled and disabled alerts.

SELECT alr.application_id,

alr.alert_id,

alr.alert_name,

alr.start_date_active,

alr.description,

alr.sql_statement_text

FROM alr.alr_alerts alr

WHERE 1=1

AND alr.created_by <> 1 -- show only custom alerts

AND alr.enabled_flag = 'Y'; -- show only enabled alerts

Query to find Parameters and Value Sets associated with a Concurrent Program

SELECT fcpl.user_concurrent_program_name "Concurrent Program Name",

fcp.concurrent_program_name "Program Short Name",

fdfcuv.column_seq_num "Column Seq #",

fdfcuv.end_user_column_name "Parameter Name",

fdfcuv.form_left_prompt "Prompt Name",

fdfcuv.enabled_flag "Enabled Flag",

fdfcuv.required_flag "Required Flag",

fdfcuv.display_flag "Display Flag",

fdfcuv.flex_value_set_id "Value Set ID",

ffvs.flex_value_set_name "Value Set Name",

flv.meaning "Default Type",

fdfcuv.default_value "Default Value"

FROM fnd_concurrent_programs fcp,

fnd_concurrent_programs_tl fcpl,

fnd_descr_flex_col_usage_vl fdfcuv,

fnd_flex_value_sets ffvs,

fnd_lookup_values flv

WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id

AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name

AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id

AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'

AND flv.lookup_code(+) = fdfcuv.default_type

AND fcpl.LANGUAGE = USERENV('LANG')

AND flv.LANGUAGE(+) = USERENV('LANG')

AND fdfcuv.enabled_flag = 'Y'

AND fcpl.user_concurrent_program_name = 'XX AR Conversion Program' -- <change it>

ORDER BY fdfcuv.column_seq_num;