UMX/ Roles Security Queries

 -- Who all Users a particular Role/Responsibility Is Assigned

Select wlur.USER_NAME,

wfr.display_name,

wlur.ROLE_NAME,

wrh.super_name,

wfr.description,

wlur.ASSIGNMENT_REASON,

wrh.relationship_id,

wlur.START_DATE,

wlur.EXPIRATION_DATE,

wlur.EFFECTIVE_START_DATE,

wlur.EFFECTIVE_END_DATE

from APPS.WF_LOCAL_USER_ROLES wlur,

APPS.WF_ROLE_HIERARCHIES  wrh,

apps.UMX_ALL_ROLE_VL wfr

WHERE 1=1

AND wlur.role_name = wrh.sub_name (+)

and wfr.name =  wlur.role_name 

and wlur.USER_NAME = 'XXXXXX'  -- USER NAME

and sysdate between wlur.EFFECTIVE_START_DATE and wlur.EFFECTIVE_END_DATE

--and wfr.display_name like 'Receivables - Limited Access' ---- Responsibility Name

--and role_name ='FND_RESP|SQLGL|AMZ_GL_LOOK_CODE_MGR|STANDARD' -- Role Name

order by wlur.ROLE_NAME 

-- Responsibility and Role Name Join

SELECT --asn.LOOKUP_ASSIGNMENT_ID, 

uar.display_name "Responsbility Name",

uar.description "Responsiblity Description",

flv.MEANING "Role Category" ,

     fat.application_name,

     flv.description "Role Description",

asn.LOOKUP_TYPE CATEGORY_LOOKUP_TYPE, 

asn.LOOKUP_CODE CATEGORY_LOOKUP_CODE, 

asn.INSTANCE_PK1_VALUE WF_ROLE_NAME, 

uar.orig_system

FROM apps.fnd_lookup_assignments asn, 

apps.fnd_lookup_values_vl flv ,

apps.umx_all_role_vl uar,

apps.fnd_application fa,

apps.fnd_application_tl fat

WHERE flv.lookup_type = asn.LOOKUP_TYPE 

AND flv.lookup_code = asn.LOOKUP_CODE 

and uar.name = asn.INSTANCE_PK1_VALUE

and fa.application_short_name  = uar.owner_tag

and fa.application_id = fat.application_id

and fat.source_lang ='US'

AND flv.lookup_type = 'UMX_CATEGORY_LOOKUP' 

AND asn.obj_name = 'UMX_ACCESS_ROLE'

AND uar.status ='ACTIVE'

-- and asn.LOOKUP_CODE like '%AR%'

-- AND FA.APPLICATION_SHORT_NAME = 'SQLAP'

--and flv.MEANING = 'General Ledger Closer' ----- Role Description from Request Access Page

--and asn.INSTANCE_PK1_VALUE = 'FND_RESP|SQLAP|AMZ_AP_CN_ACCESS|STANDARD'

and uar.DISPLAY_NAME  = 'NZ - Receivables Inquiry' -- Responsibiity Name

--Assign a Security Admin Role to a User 

Begin

wf_local_synch.PropagateUserRole(

     p_user_name => 'XXXXXXX',

     p_role_name => 'UMX|SECURITY_ADMIN',

     p_start_date=> SYSDATE);

commit;

end;

User Preferences Values

Sometimes, we need to Update/retrieve User Preferences values Setup at User level in our code/Specifically in User specific application logic building.

So here are few pointers , one ought to know while working with User references.

 --> User Preferences are nothing but the System Profiles (Default from Site Level), starting with 'ICX%'%

 If some one Updates his/her user presences from EBS Home Page, updated values automatically gets reflects in System profiles as well  at the User Level (Not on Site level). 

 --> Function to get These values,

SELECT FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK',<USER_ID>) FROM DUAL;

This function retrieves the value from profiles for a specific User , if their is no value found at user value it further goes down to site value level and fetch the value.

Below query also can be used to get the values , those are updated at User level.

SELECT

    VAL.PROFILE_OPTION_VALUE, OPT.PROFILE_OPTION_NAME,val.PROFILE_OPTION_ID

FROM

    APPLSYS.FND_PROFILE_OPTION_VALUES VAL,

    APPLSYS.FND_PROFILE_OPTIONS OPT,

    APPLSYS.FND_USER FUSER

WHERE

    VAL.PROFILE_OPTION_ID = OPT.PROFILE_OPTION_ID

    AND VAL.LEVEL_VALUE = FUSER.USER_ID

  --  AND OPT.PROFILE_OPTION_NAME LIKE 'ICX%'

    AND VAL.LEVEL_ID = 10004 -- USER LEVEL

    AND FUSER.USER_NAME = 'DEMOUSER';

Convert Client Time zone date to Server Time Zone Date

Convert Client Time zone date to Server Time Zone Date



Select TO_CHAR (FND_TIMEZONES_PVT.ADJUST_DATETIME
                                (LAST_ACCPET_DATE, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE,
                                                                   FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE),
                                                                   'DD-MON-YYYY HH24:MI:SS')
 from PO_LINE_LOCATIONS_ALL where line_location_id =xxxxxxxx;

Query to get OTL Time sheet Data

  SELECT ppf.full_name Resource_Name,
         --  paaf.supervisor_id,
         ppf1.full_name Manager_Name,
         papa.name Project_Name,
         papa.segment1 project_num,
         pt.task_name,
         TO_CHAR (hts.start_time, 'DD-MON-YYYY') Week_Start,
         --hts.resource_id,
         htb2.measure Hours_Charge,
         hts.approval_status,
         --  hts.submission_date,
         htb1.start_time Individual_date,
         TO_CHAR (htb1.start_time, 'DAY') Individual_day
    FROM hxc.hxc_time_building_blocks htb,
         hxc.hxc_time_building_blocks htb1,
         hxc.hxc_time_building_blocks htb2,
         hxc.hxc_time_attribute_usages htau,
         hxc.hxc_time_attributes hta,
         apps.pa_projects_all papa,
         hxc.hxc_timecard_summary hts,
         apps.per_all_people_f ppf,
         apps.per_all_people_f ppf1,
         apps.pa_tasks pt,
         apps.per_all_assignments_f paaf
   WHERE     htb1.parent_building_block_id = htb.time_building_block_id
         AND htb1.parent_building_block_ovn = htb.object_version_number
         AND ppf.person_id = hts.resource_id
         --     AND htb.date_to = apps.hr_general.end_of_time
         AND htb.SCOPE = 'TIMECARD'
         AND htb1.SCOPE = 'DAY'
         --    AND htb1.date_to = apps.hr_general.end_of_time
         AND htb2.parent_building_block_id = htb1.time_building_block_id
         AND htb2.parent_building_block_ovn = htb1.object_version_number
         AND htb2.SCOPE = 'DETAIL'
         AND paaf.person_id = ppf.person_id
         AND paaf.organization_id = XXX
         --     AND htb2.date_to = apps.hr_general.end_of_time
         AND htau.time_building_block_id = htb2.time_building_block_id
         AND htau.time_building_block_ovn = htb2.object_version_number
         AND htau.time_attribute_id = hta.time_attribute_id
         AND papa.project_id = hta.attribute1
         AND hts.start_time = htb.start_time
         AND hts.resource_id = htb.resource_id
         AND htb2.approval_status = 'SUBMITTED'
         --AND htb.resource_id = p_resource_id
         --  AND hts.timecard_id = :p_timecard_id
         AND hta.attribute_category = 'PROJECTS'
         AND paaf.supervisor_id = ppf1.person_id
         AND hts.start_time >= SYSDATE -XXX(Days)
         --  AND hts.approval_status = 'WORKING'
         AND hta.attribute2 = pt.task_id
         AND hta.attribute1 = pt.project_id
         AND pt.project_id = pt.project_id
         --   and hts.resource_id=46787
         AND TO_CHAR (htb1.start_time, 'DAY') NOT IN ('SATURDAY ', 'SUNDAY   ')
         AND ppf.effective_end_date > SYSDATE
         AND ppf1.effective_end_date > SYSDATE
         AND paaf.effective_end_date > SYSDATE
ORDER BY ppf.full_name, htb1.start_time, htb1.start_time

Supplier Contact Information Queries

Hi Everyone,
Recently i had got a requirement to gather all the Email ids , those can be attached to any supplier.


Their are 4 places, we can store Email Addresses for any supplier in Oracle R12.

1. Address Book--> Communication Field.
2. Contact Directory
3. Remittance Email id at Site Level
4. Remittance Email id at Header Level

Queries : 

• Address Book --> Communication Field

select ass.vendor_name, ass.segment1 supplier_num,hcp.email_address

from hz_party_sites hps,

hz_contact_points hcp,

ap_suppliers ass,

ap_supplier_sites_all assa

where 1=1 

and ass.vendor_id=assa.vendor_id

and hcp.owner_table_name='HZ_PARTY_SITES'

and hps.party_id= ass.party_id

and hcp.owner_table_id= hps.party_site_id

and hcp.contact_point_type='EMAIL'

and assa.org_id = xxxx;

--AND ass.segment1='77439'

• Contact Directory

SELECT  asu.segment1 Supp_Num

,asu.vendor_name

,hpcp.email_address

FROM

 hz_relationships hr

,ap_suppliers asu

,ap_supplier_sites_all assa

--,ap_supplier_contacts asco

,hz_org_contacts hoc

,hz_parties hpc

,hz_parties hpr

,hz_contact_points hpcp

WHERE hoc.party_relationship_id = hr.relationship_id

AND hr.subject_id = asu.party_id

AND hr.relationship_code = 'CONTACT'

AND hr.object_table_name = 'HZ_PARTIES'

AND asu.vendor_id = assa.vendor_id

AND hr.object_id = hpc.party_id

AND hr.party_id = hpr.party_id

AND hpr.party_type='PARTY_RELATIONSHIP'
AND hpcp.contact_point_type='EMAIL'

AND assa.org_id = xxxx

AND hpr.party_id = hpcp.owner_table_id

AND hpcp.owner_table_name = 'HZ_PARTIES'

--AND asu.segment1='77439'

• Remittance Email id at Site Level

  SELECT

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

  -- and s.segment1='77439'

   and st.org_id= xxx

 ORDER BY s.vendor_name, st.vendor_site_code;

• Remittance Email id at Header Level

SELECT aps.vendor_name, aps.segment1 supplier_num,iepa.remit_advice_email 

       FROM  APPS.ap_suppliers aps,

          apps.ap_supplier_sites_all apss

       ,APPS.iby_external_payees_all iepa

       WHERE  iepa.payee_party_id = aps.party_id

       and aps.vendor_id= apss.vendor_id

         AND default_payment_method_code is null

        -- AND  aps.segment1='77439'

        and apss.org_id = xxx

         and iepa.party_site_id IS null

ORA-01861: literal does not match format string

Cause:  FDPSTP failed due to ORA-01861: literal does not match format string
ORA-06512: at line 1

Starting with Oracle Application Version 11.5.10.2, Oracle does not allow you to use FND_DATE4 parameters and instead recommends FND_STANDARD_DATE data type. Thus, concurrent program using date parameters would be assigned value set FND_STANDARD_DATE. 

The point to be noted here is, format mask for the FND_STANDARD_DATE data type is "YYYY/MM/DD HH24:MI:SS". However, in most of the date format in Oracle Database will have format mask "DD-MON-YYYY HH24:MI:SS". Because of difference in above two format masks, you get error "ORA-01861: literal does not match format string".

Following is the workaround to avoid such errors: 
1. Define the date parameter as VARCHAR2 in Oracle Procedure. 
2. Register Date Parameter in Concurrent Program with Value Set as "FND_STANDARD_DATE".
3. Use FND_DATE.CANONICAL_TO_DATE function to convert varchar2 value to oracle date format & then use this value in your oracle procedure. 

Example: 


CREATE PROCEDURE XX_TEMP (errbuf      OUT  VARCHAR2
                         ,retcode     OUT  VARCHAR2
                         ,i_from_date  IN  VARCHAR2
                         ,i_to_date    IN  VARCHAR2 
                         )
IS
   ld_from_date DATE := FND_DATE.CANONICAL_TO_DATE (i_from_date);
   ld_to_date   DATE := FND_DATE.CANONICAL_TO_DATE (i_to_date);

   /* Use  ld_from_date and ld_to_date instead of i_from_date and i_to_date in procedure */
END XX_TEMP;
/

Query to Get Onhand Stock at subinventory and locator level

             SELECT
            mp.operating_unit,  
            mp.organization_code,
             msib.segment1 item_number,
             msib.description,
             msib.inventory_item_status_code Item_Status,
             mms2.status_code Sub_Status,
             mil.segment1 Locator,
             mms3.status_code location_status,
             mln.lot_number,
             msib.primary_uom_code,
             SUM (mpoq.primary_transaction_quantity) onhand_qty,
             mln.organization_id,
             TO_CHAR(mln.expiration_date,'DD-MON-RRRR') expiration_date,
             mms1.status_code Lot_status,
             mln.inventory_item_id,
             mil.inventory_location_id
        FROM mtl_system_items_b msib,
               mtl_item_status mis,
               mtl_item_locations mil,
             org_organization_definitions mp,
             mtl_lot_numbers mln,
             mtl_onhand_quantities_detail mpoq,
             mtl_material_statuses_tl mms1,
             mtl_material_statuses_tl mms2,
             mtl_material_statuses_tl mms3,
             mtl_secondary_inventories msi
       WHERE     1 = 1
             AND mln.inventory_item_id = msib.inventory_item_id
             AND mln.organization_id = msib.organization_id
             AND mis.inventory_item_status_code=msib.inventory_item_status_code
             AND msib.organization_id = mp.organization_id
             AND mil.inventory_location_id = mpoq.locator_id
             --AND mil.inventory_item_id = mln.inventory_item_id
             AND mil.organization_id = mln.organization_id
             AND mln.inventory_item_id = mpoq.inventory_item_id(+)
             AND mln.organization_id = mpoq.organization_id(+)
             AND mln.lot_number = mpoq.lot_number(+)
             AND mpoq.organization_id = msi.organization_id
             AND mpoq.subinventory_code = msi.secondary_inventory_name
             AND mms1.status_id = mln.status_id
             AND mms2.status_id=  msi.status_id
             AND mms3.status_id=  mil.status_id
             AND mms1.language=USERENV('LANG')
             AND mms2.language=USERENV('LANG')
             AND mms3.language=USERENV('LANG')
      --     AND msib.lot_control_code = 2
      --     AND mp.operating_unit = :1--337
             AND mln.organization_id =:2--344
             AND msib.inventory_item_id = :3--1189116
         --  AND mln.lot_number='K09E276'
    GROUP BY mp.organization_code,
             msib.segment1,
             msib.description,
             mln.inventory_item_id,
             mln.organization_id,
             mln.lot_number,
             mil.inventory_location_id,
             msi.secondary_inventory_name,
             msib.inventory_item_status_code,
             msi.attribute6,
             mms1.status_code,
            mms2.status_code,
            mms3.status_code,
             msi.attribute4,
             mil.segment1,
             msib.primary_uom_code,
               (mln.expiration_date - sysdate) ,
             msib.shelf_life_days,
             TO_CHAR(mln.expiration_date,'DD-MON-RRRR'),
             mln.status_id,
             mpoq.locator_id,
             mp.operating_unit,
             mln.attribute14 ,
         msi.RESERVABLE_TYPE
     --ORDER BY mp.organization_code,msib.segment1, mln.lot_number, msi.secondary_inventory_name,mil.segment1
                   

Standard Function To Get Nextval for a sequence

Package Name : FND_SEQNUM
Function Name : GET_NEXT_AUTO_SEQ

So If Seq Name : XXX_ITEM_ID_S

Then next value can be fetched by simply using of

FND_SEQNUM.GET_NEXT_AUTO_SEQ ('XXX_ITEM_ID_S');

Know your Concurrent Program’s Performance

Know your Concurrent Program’s Performance

The below query will give you the time taken to execute the concurrent Programs with the latest concurrent programs with least execution time comes first.

select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      AND pt.language = USERENV('Lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc,
      f.actual_completion_date-f.actual_start_date ;

Oracle SQL Performance Tuning Common Tips

1. Do not use the set operator UNION if the objective can be achieved through an UNION ALL. UNION incurs an extra sort operation which can be avoided.

2. Select ONLY those columns in a query which are required. Extra columns which are not actually used, incur more I/O on the database and increase network traffic.

3. Do not use the keyword DISTINCT if the objective can be achieved otherwise. DISTINCT incurs an extra sort operation and therefore slows your queries down.

4. If it is required to use a composite index, try to use the “Leading” column in the “WHERE” clause. Though Index skip scan is possible, it incurs extra cost in creating virtual indexes and may not be always possible depending on the cardinality of the leading columns.

5. There should not be any Cartesian product in the query unless there is a definite requirement to do so. I know this is a silly point but we all have done this mistake at one point 

6. Wherever multiple tables are used, always refer to a column by either using an alias or using the fully qualified name. Do not leave the guess work for Oracle.

7. SQL statements should be formatted consistently (e.g the keywords should be in CAPS only) to aid readability. Now, this is not a performance tip really. However, it’s important and part of the practices.

8. If possible use bind variables instead of constant/literal values in the predicate filter conditions to reduce repeated parsing of the same statement.

9. Use meaningful aliases for tables/views

10. When writing sub-queries make use of the EXISTS operator where possible as Oracle knows that once a match has been found it can stop and avoid a full table scan (it does a SEMI JOIN).

11. If the selective predicate is in the sub query, then use IN.

12. If the selective predicate is in the parent query, then use EXISTS.

13. Do not modify indexed columns with functions such as RTRIM, TO_CHAR, UPPER, TRUNC as this will prevent the optimizer from identifying the index. If possible perform the modification on the constant side of the condition. If the indexed column is usually accessed through a function (e.g NVL), consider creating a function based index.

14. Try to use an index if less than 5% of the data needs to be accessed from a data set. The exception is a small table (a few hundred rows) which is usually best accessed through a FULL table scan irrespective of the percentage of data required.

15. Use equi-joins whenever possible, they improve SQL efficiency

16. Avoid the following kinds of complex expressions:

• NVL (col1,-999) = ….
• TO_DATE(), TO_NUMBER(), and so on

These expressions prevent the optimizer from assigning valid cardinality or selectivity estimates and can in turn affect the overall plan and the join method

17. It is always better to write separate SQL statements for different tasks, but if you must use one SQL statement, then you can make a very complex statement slightly less complex by using the UNION ALL operator

18. Joins to complex views are not recommended, particularly joins from one complex view to another. Often this results in the entire view being instantiated, and then the query is run against the view data

19. Querying from a view requires all tables from the view to be accessed for the data to be returned. If that is not required, then do not use the view. Instead, use the base table(s), or if necessary, define a new view.

20. While querying on a partitioned table try to use the partition key in the “WHERE” clause if possible. This will ensure partition pruning.

21. Consider using the PARALLEL hint (only when additional resources can be allocated) while accessing large data sets.

22. Avoid doing an ORDER BY on a large data set especially if the response time is important.

23. Consider changing the OPTIMIZER MODE to FIRST_ROWS(n) if the response time is important. The default is ALL_ROWS which gives better throughput.

24. Use CASE statements instead of DECODE (especially where nested DECODEs are involved) because they increase the readability of the query immensely.

25. Do not use HINTS unless the performance gains clear.

26. Check if the statistics for the objects used in the query are up to date. If not, use the DBMS_STATS package to collect the same.

27. It is always good to understand the data both functionally and it’s diversity and volume in order to tune the query. Selectivity (predicate) and Cardinality (skew) factors have a big impact on query plan. Use of Statistics and Histograms can drive the query towards a better plan.

28. Read explain plan and try to make largest restriction (filter) as the driving site for the query, followed by the next largest, this will minimize the time spent on I/O and execution in subsequent phases of the plan.

29. If Query requires quick response rather than good throughput is the objective, try to avoid sorts (group by, order by, etc.). For good throughput, optimizer mode should be set to ALL ROWS.

30. Queries tend to perform worse as they age due to volume increase, structural changes in the database and application, upgrades etc. Use Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM) to better understand change in execution plan and throughput of top queries over a period of time.

31. SQL Tuning Advisor and SQL Access Advisor can be used for system advice on tuning specific SQL and their join and access paths, however, advice generated by these tools may not be always applicable (point 28).

32. SQL Access paths for joins are an component determining query execution time. Hash Joins are preferable when 2 large tables need to be joined. Nested loops make work better when a large table is joined with a small table.

Disclaimer: Points listed above are only pointers and may not work under every circumstance. This check list can be used as a reference while fixing performance problems in the Oracle Database.

Suggested further readings

1. Materialized Views
2. Advanced Replication
3. Change Data Capture (Asynchronous)
4. Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM).
5. Partitioning strategies.

Now it’s turn if have any more tips which you have used then do add them in comment section… Your feedback is very valuable and it would be useful for other viewers too.

Query to get Set of Books, Operating Unit, Organization data

SELECT gl.set_of_books_id,
gl.name,
gl.short_name,
hou.name operatin_unit,
hou.organization_id operating_unit_id,
org.organization_name warehouse_name,
org.organization_id warehouse_id
 FROM org_organization_definitions org,
 hr_operating_units hou,
 gl_sets_of_books gl
 where org.operating_unit=hou.organization_id
 and hou.set_of_books_id = gl.set_of_books_id
order by 1,3,6

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 "Short Name",
        fcp.EXECUTION_METHOD_CODE,
        fdfcuv.column_seq_num "Column Seq Number",
        fdfcuv.end_user_column_name "Parameter Name",
        fdfcuv.form_left_prompt "Prompt",
        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    fcpl.user_concurrent_program_name = :conc_prg_name
        AND    fdfcuv.descriptive_flexfield_name = '$SRS$.'
                 || fcp.concurrent_program_name
                 and ffvs.flex_value_set_name = 'FND_STANDARD_DATE'
                 and fcp.concurrent_program_name like 'XXSYK%'
                 and fcp.EXECUTION_METHOD_CODE ='H'
        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')

Query to Find Org id based on Responsibility id

SELECT   pers.organization_id
    FROM apps.fnd_profile_options_vl fpo,
         apps.fnd_profile_option_values fpov,
         apps.fnd_responsibility_vl frv,
         apps.per_security_organizations_v pers
   WHERE frv.responsibility_id = :resp_id
     AND fpov.level_value = frv.responsibility_id
     AND fpo.profile_option_id = fpov.profile_option_id
     AND fpo.user_profile_option_name = 'MO: Security Profile'
     AND fpov.profile_option_id = fpo.profile_option_id
     AND fpov.profile_option_value = pers.security_profile_id
ORDER BY frv.responsibility_name;







SELECT frv.responsibility_name, fpov.profile_option_value org_id, hou.NAME
FROM apps.hr_organization_units hou,
apps.fnd_profile_options_vl fpo,
apps.fnd_profile_option_values fpov,
apps.fnd_responsibility_vl frv
WHERE frv.responsibility_name = :p_resp_name
AND fpov.level_value = frv.responsibility_id
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.user_profile_option_name = 'MO: Operating Unit'
AND fpov.profile_option_id = fpo.profile_option_id
AND hou.organization_id = TO_NUMBER (fpov.profile_option_value)
ORDER BY frv.responsibility_name

Difference between CHR(10) Line Feed (\n) and CHR(13) Carriage Return (\r)

 CHR(13) is carriage return. CHR(10) is line feed.

They are different. Most of the editors will treat them as new line, but not all

The new line is actually based on your OS - If I am not wrong for Unix it is CHR(10).
For MAc it is CHR(13). For Windows it is combination of both.

So it all depends on, what editor, what OS - how they will interpret these characters..

This difference is viewable during you do file transfer between different OS.

Example :---

SQL> select
       replace('a,b,c,d,e',',',CHR(10)) x1,
       replace('a,b,c,d,e',',',CHR(13)) x2,
       replace('a,b,c,d,e',',',CHR(13)||CHR(10)) x3,
       replace('a,b,c,d,e',',',CHR(10)||CHR(13)) x4
   from
   dual;

X1        X2        X3            X4                                                                                                        
--------- --------- ------------- -------------                                                                                             
a         a b
c
d
e a             a                                                                                                         
b                   b             
b                                                                                                        
c                   c             
c                                                                                                        
d                   d             
d                                                                                                        
e                   e              e       

Query to Find out RTV (Return to Vendor ) Transactions

Select
org.organization_code,
poh.segment1 po_number,
trunc(poh.creation_date) po_date,
rsh.receipt_num,
(rsh.creation_date) receipt_date,
mtl.segment1||'-'||mtl.segment2 item,
mtl.description,
rcv.quantity,
pol.unit_price
from rcv_transactions rcv
, po_lines_all pol,
po_headers_all poh,
 po_line_locations_all pll,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
mtl_system_items mtl,
org_organization_definitions org
where rcv.transaction_type = 'RETURN TO VENDOR'
and rcv.po_line_location_id = pll.line_location_id
and rcv.po_line_id = pol.po_line_id
and pll.SHIP_TO_ORGANIZATION_ID=org.ORGANIZATION_ID
and rcv.shipment_line_id=rsl.shipment_line_id
and org.ORGANIZATION_ID=mtl.ORGANIZATION_ID
and rcv.shipment_header_id = rsh.shipment_header_id
and mtl.inventory_item_id=pol.item_id
and pol.po_header_id=poh.po_header_id
and rsl.shipment_header_id=rsh.shipment_header_id
and org.operating_unit=1214
order by 1,2,4

Bank Details for Suppliers

Bank at Supplier Site level
-------------------------------

SELECT HZP.PARTY_NAME "VENDOR NAME"
, APS.SEGMENT1 "VENDOR NUMBER"
, ASS.VENDOR_SITE_CODE "SITE CODE"
, IEB.BANK_ACCOUNT_NUM "ACCOUNT NUMBER"
, IEB.BANK_ACCOUNT_NAME "ACCOUNT NAME"
, IEB.BANK_ACCOUNT_TYPE "Bank Account Type" 
, HZPBANK.PARTY_NAME "BANK NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BANK NUMBER"
, HZPBRANCH.PARTY_NAME "BRANCH NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BRANCH NUMBER" 
, HZPBANK.address1 "Bank Address"
, HZPBANK.country "Bank Country"
, HZPBANK.city "Bank City"
, HZPBANK.Postal_Code
, NVL(HZPBANK.state, HZPBANK.province) "State/Province"
FROM apps.HZ_PARTIES HZP
, apps.AP_SUPPLIERS APS
, apps.HZ_PARTY_SITES SITE_SUPP
, apps.AP_SUPPLIER_SITES_ALL ASS
, apps.IBY_EXTERNAL_PAYEES_ALL IEP
, apps.IBY_PMT_INSTR_USES_ALL IPI
, apps.IBY_EXT_BANK_ACCOUNTS IEB
, apps.HZ_PARTIES HZPBANK
, apps.HZ_PARTIES HZPBRANCH
, apps.HZ_ORGANIZATION_PROFILES HOPBANK
, apps.HZ_ORGANIZATION_PROFILES HOPBRANCH
WHERE HZP.PARTY_ID = APS.PARTY_ID
AND HZP.PARTY_ID = SITE_SUPP.PARTY_ID
AND SITE_SUPP.PARTY_SITE_ID = ASS.PARTY_SITE_ID
AND ASS.VENDOR_ID = APS.VENDOR_ID
AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID
AND IEP.PARTY_SITE_ID = SITE_SUPP.PARTY_SITE_ID
AND IEP.SUPPLIER_SITE_ID = ASS.VENDOR_SITE_ID
AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEB.BANK_ID = HZPBANK.PARTY_ID
AND IEB.BANK_ID = HZPBRANCH.PARTY_ID
AND HZPBRANCH.PARTY_ID = HOPBRANCH.PARTY_ID
AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
ORDER BY 1,3


Bank at Supplier level
------------------------

SELECT HZP.PARTY_NAME "VENDOR NAME"
, APS.SEGMENT1 "VENDOR NUMBER"
, IEB.BANK_ACCOUNT_NUM "ACCOUNT NUMBER"
, IEB.BANK_ACCOUNT_NAME "ACCOUNT NAME"
, IEB.BANK_ACCOUNT_TYPE "Bank Account Type" 
, HZPBANK.PARTY_NAME "BANK NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BANK NUMBER"
, HZPBRANCH.PARTY_NAME "BRANCH NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BRANCH NUMBER" 
, HZPBANK.address1 "Bank Address"
, HZPBANK.country "Bank Country"
, HZPBANK.city "Bank City"
, HZPBANK.Postal_Code
, NVL(HZPBANK.state, HZPBANK.province) "State/Province"
FROM apps.HZ_PARTIES HZP
, apps.AP_SUPPLIERS APS
, apps.IBY_EXTERNAL_PAYEES_ALL IEP
, apps.IBY_PMT_INSTR_USES_ALL IPI
, apps.IBY_EXT_BANK_ACCOUNTS IEB
, apps.HZ_PARTIES HZPBANK
, apps.HZ_PARTIES HZPBRANCH
, apps.HZ_ORGANIZATION_PROFILES HOPBANK
, apps.HZ_ORGANIZATION_PROFILES HOPBRANCH
WHERE HZP.PARTY_ID = APS.PARTY_ID
AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID
AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEB.BANK_ID = HZPBANK.PARTY_ID
AND IEB.BANK_ID = HZPBRANCH.PARTY_ID
AND HZPBRANCH.PARTY_ID = HOPBRANCH.PARTY_ID
AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
and IEP.SUPPLIER_SITE_ID is null
and aps.vendor_id = 2

Actual Usage of Date Functions

========== Extract Time Upto Miliseconds from Systimetsamp or current_timestamp===

select systimestamp ,
to_char(systimestamp,'HH24:MI:SS-FF') Systimestamp_ExtractMilisecond,
CURRENT_TIMESTAMP,
to_char(CURRENT_TIMESTAMP,'HH24:MI:SS-FF') Currtimestamp_ExtractMS from dual


=========financial year dates=============

SELECT TO_DATE ('01-APR-' || TO_CHAR (ADD_MONTHS (TRUNC (to_date(:p_from_date,'DD-MON-RRRR')),-12), 'YYYY'))  FY_START from dual

SELECT TO_DATE ('31-MAR-' || TO_CHAR (ADD_MONTHS (TRUNC (to_date(:p_from_date,'DD-MON-RRRR')),-1), 'YYYY') )  FY_end from dual  





How to get first day and last date of week, month, quarter, year in Oracle
--First day of current week(sunday)
select TRUNC(SYSDATE, 'Day') from dual;
--FIRST DAY OF CURRENT FINANCIAL YEAR
select ('01-APR-' || TO_CHAR (sysdate-1, 'YYYY'))  from dual
--First day of next week(sunday)
select TRUNC(SYSDATE+7 , 'Day') from dual;
--First day of previous week(sunday)
select TRUNC(SYSDATE-7 , 'Day') from dual;
--First day of current month
select TRUNC(SYSDATE , 'Month') from dual;
--First day of previous month
select TRUNC(TRUNC(SYSDATE , 'Month')-1 , 'Month') from dual;
--First day of next month
select TRUNC(LAST_DAY(SYSDATE)+1 , 'Month') from dual;
--First day of current year
select TRUNC(SYSDATE , 'Year') from dual;
--First day of previous year
select TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year') from dual;
--First day of next year
select ADD_MONTHS(TRUNC(SYSDATE , 'Year'),12) from dual;
-- First Day of Current quater
select TRUNC(SYSDATE , 'Q') from dual;
--  First Day of Previous Quarter
select ADD_MONTHS(TRUNC(SYSDATE , 'Q'),-3) from dual;
--  First Day of Next Quarter
select ADD_MONTHS(TRUNC(SYSDATE , 'Q'),3) from dual;


-------first date of previous 3 months

 select ADD_MONTHS(LAST_DAY(TRUNC(TRUNC(to_date(sysdate,'DD-MON-RRRR') , 'Month')-1 , 'Month')),-3)+1 from dual


--Last day of current week(sunday)
select TRUNC(SYSDATE, 'Day')+6 from dual;
--Last day of next week(sunday)
select TRUNC(SYSDATE+7 , 'Day')+6 from dual;
--Last day of previous week(sunday)
select TRUNC(SYSDATE-7 , 'Day')+6 from dual;
--Last day of current month
select LAST_DAY(TRUNC(SYSDATE , 'Month')) from dual;
--Last day of previous month
select LAST_DAY(TRUNC(TRUNC(SYSDATE , 'Month')-1 , 'Month')) from dual;
--Last day of next month
select LAST_DAY(TRUNC(LAST_DAY(SYSDATE)+1 , 'Month')) from dual;
--Last day of current year
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Year'),11)) from dual;
--Last day of previous year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),11)) from dual;
--Last day of next year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),-13)) from dual;
-- Last Day of Current quater
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),2)) from dual;
--  Last Day of Previous Quarter
select TRUNC(SYSDATE , 'Q')-1 from dual;
--  Last Day of Next Quarter
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),5)) from dual;