My Oracle Applications Blog

QUERY to get the Value_set -------------------------------------------------------------------------------------------------------------------------------------- SELECT ffvs.flex_value_set_id , ffvs.flex_value_set_name , ffvs.description set_description , ffvs.validation_type, ffv.flex_value, ffvt.description value_description, ffv.enabled_flag, ffv.last_update_date, ffv.last_updated_by, ffv.attribute1, ffv.attribute2, ffv.attribute3--–Include attribute values based on DFF segments FROM fnd_flex_value_sets ffvs , fnd_flex_values ffv , fnd_flex_values_tl ffvt WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id and ffv.flex_value_id = ffvt.flex_value_id AND ffvt.language = USERENV('LANG') and flex_value_set_name like :p_value_set_name ---'XXXXXXXX' ORDER BY flex_value asc; --------------------------------------------------------------------------------------------------------------------------------------

Query To get the SMTP Server Details for a database server -------------------------------------------------------------------------------------------------------------------------------------- select fscpv.parameter_value "SMTP Host Name"       --SMTP protocol uses default port number 25 for outgoing emails       ,25                    "SMTP Port Number"       ,fscpt.description   from fnd_svc_comp_params_tl fscpt       ,fnd_svc_comp_param_vals fscpv  where fscpt.parameter_id = fscpv.parameter_id    and fscpt.display_name = 'Outbound Server Name' --'Inbound Server Name'    and fscpt.language = 'US'; --------------------------------------------------------------------------------------------------------------------------------------

Query to get the responsibility attached to a particular request group -------------------------------------------------------------------------------------------------------------------------------------- SELECT DISTINCT FRT.RESPONSIBILITY_NAME,                 FRG.REQUEST_GROUP_NAME                -- FRGU.REQUEST_UNIT_TYPE,                -- FRGU.REQUEST_UNIT_ID,               --  FCPT.USER_CONCURRENT_PROGRAM_NAME   FROM APPS.FND_RESPONSIBILITY         FR,        APPS.FND_RESPONSIBILITY_TL      FRT,        APPS.FND_REQUEST_GROUPS       ...

Query to get the Locks on database objects if any -------------------------------------------------------------------------------------------------------------------------------------- SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name  FROM V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID --------------------------------------------------------------------------------------------------------------------------------------

Query to get the directory of paricular top. -------------------------------------------------------------------------------------------------------------------------------------- Select distinct variable_name,value from fnd_env_context a , fnd_concurrent_processes b, fnd_concurrent_requests r where 1=1 and a.concurrent_process_id=b.concurrent_process_id and a.CONCURRENT_PROCESS_ID=r.CONTROLLING_MANAGER and a.variable_name IN ('AP_TOP','FND_TOP','JAVA_TOP'); --------------------------------------------------------------------------------------------------------------------------------------

Query to get the details of a submitted concurrent program -------------------------------------------------------------------------------------------------------------------------------------- /* Formatted on 2015/03/17 16:40 (Formatter Plus v4.8.8) */ SELECT   fcp.user_concurrent_program_name program_name,          fcp.concurrent_program_name short_name, fcr.request_id request_id,          frv.responsibility_name responsibility_name,          fcr.request_date request_date, fu.user_name request_submitted_by,          DECODE (fcr.status_code,                  'A', 'Waiting',                  'B', 'Resuming',         ...

Query to get details of concurent program along with parameter details -------------------------------------------------------------------------------------------------------------------------------------- SELECT   cp.user_concurrent_program_name "Program Name",          cp.concurrent_program_name "Short Name",          ap.application_name "Application", cp.description "Description",          cp.enabled_flag "Enabled", cp.output_file_type "Output Format",          cx.executable_name "Executable Short Name",          lv.meaning "Executable Method",          cx.user_executable_name "Executable Name",          df.column_seq_num "Sequence", df.end_user_column_name "Parameter",     ...

Query to get details of all active sessions. --------------------------------------------------------------------------------------------------------------------------------------  SELECT sid, serial#, username, a.* FROM v$session a; --------------------------------------------------------------------------------------------------------------------------------------

Query to find Sales order Associated with WIP JOB -------------------------------------------------------------------------------------------------------------------------------------- SELECT ooh.order_number ,   msib.segment1 ,   mr.reservation_quantity ,   we.wip_entity_name wip_job_name ,   wdj.scheduled_start_date wip_start_date ,   wdj.scheduled_completion_date wip_completion_date ,   wdj.attribute10 job_type , we.organization_id FROM oe_order_headers_all ooh ,   oe_order_lines_all ool ,   mtl_reservations mr ,   wip_discrete_jobs wdj ,   wip_entities we ,   mtl_system_items_b msib WHERE ooh.header_id = ool.header_id --AND ooh.org_id      = 160   -- AND ool.item_type_code IN ('CONFIG', 'STANDARD') AND mr.demand_source_line_id   = ool.line_id --AND mr.supply_source_type_id   = 5 AND mr.supply_source_header_id = we.wip_entity_id AND we.wip_entity_id    ...

Query to find responsibilities attached to the user -------------------------------------------------------------------------------------------------------------------------------------- SELECT frt.responsibility_name FROM apps.fnd_user_resp_groups furg,   fnd_user fu,   fnd_responsibility_tl frt WHERE fu.user_name         = '&user_name' AND fu.user_id             = furg.user_id AND furg.responsibility_id = frt.responsibility_id; select * from fnd_responsibility_tl where responsibility_name like 'Sys%Admin%' --------------------------------------------------------------------------------------------------------------------------------------

Query to find Organization Structure Oracle Apps -------------------------------------------------------------------------------------------------------------------------------------- SELECT   ood.operating_unit, hou.NAME, ood.organization_id,                 ood.organization_name,                 (SELECT organization_code                    FROM mtl_parameters mp                   WHERE mp.organization_id = ood.organization_id) inventory_org_code            FROM hr_operating_units hou, org_organization_definitions ood           WHERE hou.organization_id = ...

[PO] Matching Setting in Purchase Order Shipment Line Invoice matching can be set in five different areas of Oracle Purchasing: In the list below, a setting at any level will override the settings above it. 1. Oracle Purchasing Options a. Navigate to: Setup > Organizations > Purchasing Options b. Select Default Alternative Region 2. Supplier Information a. Navigate to: Supply Base > Suppliers b. Query on specific supplier c. Click on Open d. Select Receiving Alternative Region 3. Line Types a. Navigate to: Setup > Purchasing > Line Types b. In the Receipt Required field: Yes = 3-way, No = 2-way 4. Items a. Navigate to: Items > Master Items b. Query on specific item c. Select Purchasing Alternative Region d. In the Invoice Matching section: Yes = 3-way, No = 2-way 5. Purchase Order Shipments a. Navigate to: Purchase Orders > Purchase Orders b. Enter (header and) line information c. Click on Shipments button d. Select More Alternative Region Ramification of Invoice ...

Subledger Accounting(SLA): -------------------------------------------------------------------------------------------------------------------------------------- Oracle Subledger Accounting is a rule based engine for Generating accountingentries based on subledger transactions from all oracle applications. Subledger acounting is a set of services for R12 that significantly enhance accounting support across the E-Business suite. R12 introduces some of the changes that will be fully developed in Fusion, so close attention to the impact of these changes is worthwhile. Sub-ledger accounting may be one of these key changes. When Release 12 was first released, there was a lot of publicity about the move from the Set of Books concept to Ledgers being so fundamental, however experience suggests that the introduction of sub-ledger accounting (SLA) probably has even more impact. For those not familiar with SLA, it is a new product in Release 12 and a major change, (although it may be almost...

Organization Setup -------------------------------------------------------------------------------------------------------------------------------------- Organization Setup Steps Follow the below steps in the order listed. These are the MINIMUM steps necessary to successfully define an Organization for the Oracle Inventory module. Further information on these steps and other optional steps can be found in the Oracle Manufacturing Implementation Manual under Inventory Setup. 1. Define your set of books (GL function) 2. Define your Key Flexfields in the following order. a. Navigate to Setup /Flexfields / Key. b. Setup the System Items, Item Categories, Item Catalog Group, Stock Locators, Account Alias and Sales Order flexfields. 3. Define locations (used for a variety of functions including receiving and default delivery locations). Note: If you populate the organization field of this form it will only show on the LOV for that organization. 4. Define a workday calendar, also called t...

Create Request Group using oracle api -------------------------------------------------------------------------------------------------------------------------------------- DECLARE CURSOR c1 IS SELECT b.concurrent_program_name programname,c.application_name appname FROM fnd_request_group_units a ,fnd_concurrent_programs b ,fnd_application_tl c ,fnd_request_groups d WHERE a.request_Group_id=d.request_Group_id AND a.request_unit_id=b.concurrent_program_id AND b.application_id=c.application_id AND d.request_Group_name='GL Concurrent Program Group'; BEGIN FOR rec IN c1 LOOP fnd_program.add_to_group(program_short_name =>rec.programname ,program_application=>REC.APPNAME ,request_group=>'XX_GL_REQ_GRP' ,group_application=>'Custom Application'); END LOOP; END; --------------------------------------------------------------------------------------------------------------------------------------

Calculating Weekdays between two dates -------------------------------------------------------------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION totworkdays (fromdate DATE, todate DATE)    RETURN NUMBER IS    totalsundays     NUMBER;    totalsaturdays   NUMBER; begin    totalsundays         := NEXT_DAY (todate - 7, 'sunday')            - NEXT_DAY (fromdate - 1, 'sunday');    totalsaturdays       :=   NEXT_DAY (todate - 7, 'saturday')          - NEXT_DAY (fromdate - 1, 'saturday');    RETURN (todate - fromdate - (totalsundays + totalsaturdays) / 7 - 1); END totworkdays; Call this function as follows: declare lv_tot_work_days number; begin lv_tot_work_days := totworkdays ('01-jan-200...

Onhand Quantity at given date -------------------------------------------------------------------------------------------------------------------------------------- SELECT   SUM (target_qty)        , item_id FROM     (SELECT   moqv.subinventory_code subinv                  , moqv.inventory_item_id item_id                  , SUM (transaction_quantity) target_qty           FROM     mtl_onhand_qty_cost_v moqv           WHERE    moqv.organization_id = :org_id           AND      moqv.inventory_item_id = :item_id           GROU...

Database and Application Information -------------------------------------------------------------------------------------------------------------------------------------- Below are some of the queries that can be used to get the database and Application information. 1) Get Product Version SELECT product      , VERSION      , status FROM   product_component_version The other way to get this information is by using following query select * from v$version; 2) Get Applications Version and Patch Information SELECT   SUBSTR (a.application_name, 1, 60) Application_Name        , SUBSTR (i.product_version, 1, 4) Version        , i.patch_level        , i.application_id        , i.last_update_date FROM     apps.fnd_product_installations i        , apps.fnd_applicati...

List the name of user that is locking a table -------------------------------------------------------------------------------------------------------------------------------------- SELECT c.owner       ,c.object_name       ,c.object_type       ,fu.user_name locking_fnd_user_name       ,fl.start_time locking_fnd_user_login_time       ,vs.module       ,vs.machine       ,vs.osuser       ,vlocked.oracle_username       ,vs.sid       ,vp.pid       ,vp.spid AS os_process       ,vs.serial#       ,vs.status       ,vs.saddr       ,vs.audsid       ,vs.process FROM fnd_logins    ...

 To make the program to complete with warning. Through sql -------------------------------------------------------------------------------------------------------------------------------------- l_submit_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL); --------------------------------------------------------------------------------------------------------------------------------------

Query to find who and when update an Oracle Application user's profile -------------------------------------------------------------------------------------------------------------------------------------- SELECT t.user_profile_option_name, profile_option_value, v.creation_date, v.last_update_date, v.creation_date v. last_update_date "Change Date", (SELECT UNIQUE user_name FROM fnd_user WHERE user_id = v.created_by) "Created By", (SELECT user_name FROM fnd_user WHERE user_id = v.last_updated_by) "Last Update By" FROM fnd_profile_options o, fnd_profile_option_values v, fnd_profile_options_tl t WHERE o.profile_option_id = v.profile_option_id AND o.application_id = v.application_id AND start_date_active <= SYSDATE AND NVL (end_date_active, SYSDATE) >= SYSDATE AND o.profile_option_name = t.profile_option_name AND level_id = 10001 AND t.LANGUAGE IN (SELECT language_code FROM fnd_languages WHERE installed_flag = 'B' UNION SELECT nls_langua...

Query to get Request Group Details Responsibility wise -------------------------------------------------------------------------------------------------------------------------------------- SELECT   frg.request_group_name, fat1.application_name, frg.description,          DECODE (frgu.request_unit_type,                  'P', 'Program',                  'S', 'Set',                  'A', 'Application',                  frgu.request_unit_type                 ) TYPE,          DECODE (frgu.request_unit_type, ...

Query for Sales Order Details -------------------------------------------------------------------------------------------------------------------------------------- SELECT   ooha.header_id order_header_id, ottt.NAME order_type_name,          ooha.order_number, ooha.ordered_date,          ooha.transactional_curr_code order_currency, hp.party_id,          hp.party_number, hp.party_name customer_name,          hca.cust_account_id customer_id, hca.account_number customer_number,          oola.line_id order_line_id, oola.line_number, oola.inventory_item_id,          msib.segment1 item_number, msib.description item_desc,          oola.attribute15 superseded_item, oola.order_quantity_uom,   ...

Query To Find Concurrent Program Attached To a Responsibility -------------------------------------------------------------------------------------------------------------------------------------- SELECT frt.responsibility_name,   frg.request_group_name,   frgu.request_unit_type,   frgu.request_unit_id,   fcpt.user_concurrent_program_name FROM  fnd_Responsibility fr,   fnd_responsibility_tl frt,   fnd_request_groups frg,   fnd_request_group_units frgu,   fnd_concurrent_programs_tl fcpt WHERE  frt.responsibility_id = fr.responsibility_id   AND     frg.request_group_id = fr.request_group_id   AND     frgu.request_group_id = frg.request_group_id   AND     fcpt.concurrent_program_id = frgu.request_unit_id   AND     fcpt.user_concurrent_program_name = '&conc_program_name' ORDER BY 1,2,3,4 -------------------------------------------...

Query the Service Contracts Tables for Header, Line, Sub line and Billing Information. -------------------------------------------------------------------------------------------------------------------------------------- A. Contract Header Data select * from OKC_K_HEADERS_ALL_B where contract_number like :p_contract_number; B. Contract Line Data Note: in OKC_K_LINES_B the chr_id field is only populated with the contract header id for contract lines. For contract sublines, this value is NULL.  Dnz_chr_id is populated with the contract header id for both lines and sublines. B1. This SQL takes data from views rather than from actual contracts tables and is useful for reviewing data but not ideal for verifying if base tables hold correct data.   SELECT DISTINCT oal.line_number                 , oll.lse_name                 , o...

Query for Customer Address Details -------------------------------------------------------------------------------------------------------------------------------------- SELECT DISTINCT hca.account_number customer_number,                 hp.party_name customer_name,                 hps.party_site_number site_number, hl.address1 address1,                 hl.address2 address2, hl.address3 address3,                 hl.address4 address4, hl.city city,                 hl.postal_code postal_code, hl.state state,                 ftt.territory_sho...

Query for Supplier Bank Details -------------------------------------------------------------------------------------------------------------------------------------- SELECT DISTINCT pv.vendor_name vendor_name, pv.segment1 vendor_number,                 pvs.vendor_site_code vendor_site_code,                 aba.bank_account_name bank_account_name,                 aba.bank_account_num bank_account_num,                 aba.currency_code currency_code,                 abau.primary_flag primary_flag, abb.bank_name bank_name,                 ab...

Query for Customer Receipt Details -------------------------------------------------------------------------------------------------------------------------------------- SELECT   acra.cash_receipt_id,          DECODE (acra.TYPE,                  'cash', 'cash receipt receipt',                  'misc', 'miscellaneous',                  acra.TYPE                 ) receipt_type,          acra.currency_code, acra.doc_sequence_value receipt_number,          acra.receipt_number reference_number,          TRUNC (a...