Value Sets

It is list of values (LOV) with validations, it will be used to restrict the end user not to enter invalid data.

None: Used to give format conditions like maximum size, only numbers, only characters etc.

Independent: Create the value set name by giving the validation type as ‘independent’. Then enter the values for this set.

Dependent: The list of values will be depends on the independent value set.

Table: If we want to get the values from database table as LOV then we will use this value set.

Translation Independent & Translation Dependent:

Just like Independent and Dependent but values are visible in other than American English language which was mentioned.

Special and Pair: To display values of flex fields.

Executable: It is user defined window where we will mention execution filename, execution method and application name.

Concurrent Program: The combination of application name plus program name uniquely identifies your concurrent program.

Request Group: It is collection of concurrent programs and reports.

Responsibility: It is level of an authority where we will combine request group, menu and data group.

SRS(Standard Request Submission) Window: By using this window we can submit the Concurrent Programs.

SELECT * FROM FND_EXECUTABLES WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

SELECT * FROM FND_CONCURRENT_PROGRAMS WHERE

TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

SELECT * FROM FND_REQUEST_GROUPS WHERE

TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

SELECT * FROM FND_USER WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

Standards for Report Registration in Oracle Application:

Define one mandatory user parameter ‘P_CONC_REQUEST_ID’

Call the user exit from Before Report Trigger

SRW.USER_EXIT(‘FND SRWINIT’)

Call the another user exit from After Report Trigger

SRW.USER_EXIT(‘FND SRWEXIT’)

P_CONC_REQUEST_ID: We have to define this bind variable in reports where we are using user exits.

When we submit any Concurrent Program from SRS window request id will be generated, this will be transferred to this variable.

Based on this request id  USER_EXITS will be working in reports.

Value Sets:

It is list of values (LOV) with validations, it will be used to restrict the end user not to enter invalid data.

None: Used to give format conditions like maximum size, only numbers, only characters etc.

Independent: Create the value set name by giving the validation type as ‘independent’. Then enter the values for this set.

Dependent: The list of values will be depends on the independent value set.

Table: If we want to get the values from database table as LOV then we will use this value set.

Translation Independent & Translation Dependent:

Just like Independent and Dependent but values are visible in other than American English language which was mentioned.

Special and Pair: To display values of flex fields.

Executable: It is user defined window where we will mention execution filename, execution method and application name.

Concurrent Program: The combination of application name plus program name uniquely identifies your concurrent program.

Request Group: It is collection of concurrent programs and reports.

Responsibility: It is level of an authority where we will combine request group, menu and data group.

SRS(Standard Request Submission) Window: By using this window we can submit the Concurrent Programs.

SELECT * FROM FND_EXECUTABLES WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

SELECT * FROM FND_CONCURRENT_PROGRAMS WHERE

TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

SELECT * FROM FND_REQUEST_GROUPS WHERE

TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

SELECT * FROM FND_USER WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

Standards for Report Registration in Oracle Application:

Define one mandatory user parameter ‘P_CONC_REQUEST_ID’

Call the user exit from Before Report Trigger

SRW.USER_EXIT(‘FND SRWINIT’)

Call the another user exit from After Report Trigger

SRW.USER_EXIT(‘FND SRWEXIT’)

P_CONC_REQUEST_ID: We have to define this bind variable in reports where we are using user exits.

When we submit any Concurrent Program from SRS window request id will be generated, this will be transferred to this variable.

Based on this request id  USER_EXITS will be working in reports.

Value Sets:

It is list of values (LOV) with validations, it will be used to restrict the end user not to enter invalid data.

None: Used to give format conditions like maximum size, only numbers, only characters etc.

Independent: Create the value set name by giving the validation type as ‘independent’. Then enter the values for this set.

Dependent: The list of values will be depends on the independent value set.

Table: If we want to get the values from database table as LOV then we will use this value set.

Translation Independent & Translation Dependent:

Just like Independent and Dependent but values are visible in other than American English language which was mentioned.

Special and Pair: To display values of flex fields.

Executable: It is user defined window where we will mention execution filename, execution method and application name.

Concurrent Program: The combination of application name plus program name uniquely identifies your concurrent program.

Request Group: It is collection of concurrent programs and reports.

Responsibility: It is level of an authority where we will combine request group, menu and data group.

SRS(Standard Request Submission) Window: By using this window we can submit the Concurrent Programs.

SELECT * FROM FND_EXECUTABLES WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

SELECT * FROM FND_CONCURRENT_PROGRAMS WHERE

TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

SELECT * FROM FND_REQUEST_GROUPS WHERE

TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

SELECT * FROM FND_USER WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE)

Standards for Report Registration in Oracle Application:

Define one mandatory user parameter ‘P_CONC_REQUEST_ID’

Call the user exit from Before Report Trigger

SRW.USER_EXIT(‘FND SRWINIT’)

Call the another user exit from After Report Trigger

SRW.USER_EXIT(‘FND SRWEXIT’)

P_CONC_REQUEST_ID: We have to define this bind variable in reports where we are using user exits.

When we submit any Concurrent Program from SRS window request id will be generated, this will be transferred to this variable.

Based on this request id  USER_EXITS will be working in reports.

USER EXISTS:

User Exit is one of the program which will transfer the control from report builder executable to 3^rd generation language to get information and completes remaining process.

Total we have five user exits:

FND SRWINIT: We will call this user exist from before report trigger to retrieve user profile values like user id, Responsibility name etc.

FND SRWEXIT: We will call this user exist from after report trigger to freeze the memory which is occupied by user profile values.

FND FLEXSQL: Used to display the flex fields information.

FND FLEXIDVAL: Used to display the flex fields information.

FND FORMATCURRENCY: Used for multiple Currency Reporting.

REPORT PARAMETERS:

If report is having bind parameters we have to define those parameters while registering concurrent program.

 Select ‘parameters’ Button ,enter the seq no and give the parameter name.

VALUESET: It is nothing but list of values which will validate the data.

TOKEN: Token is one of the field which will be used to map concurrent program parameter with report builder bind variable. we have to mention the bind variable name in this token field.

Default Types:

When we want to pass default values for concurrent program parameter then we will use default types. We have 4 default types:

Constant: We can pass constant number or string default value.

Sql Statement: We can define valid SQL settlement (select statement), the result of this query will be pass as default value. This query should retrieve only one value.

Segment: When we want to get any other parameter value as default then we will use this one.

Profile: We will use this one to pass the profile values like org_id, user_id etc as default value.

Out parameters in procedure:

Errbuf: Will be used to send error messages into logfile if any error occurs inside of procedure.

Retcode: Will be used to find out concurrent program status whether it has been completed successfully.

Warnings or errors:

Successfulà0

Warningà1

Errorà2

Fnd File APIs:

We can’t use dbms_output.put_line in oracle applications we will use fnd_file APIs to write the message into output files as well as log files.

Fnd_file.put_line(fnd_file.output,’message’):   Writes the messages into output file.

Fnd_file.put_line(fnd_file.log,’message’):    Writes the messages into log file..

Value Set Definition

-- Setup > Financials > Flexfields > Validation > Sets


SELECT   ffvs.flex_value_set_name name,
         DECODE (ffvs.longlist_flag,
                 'N', 'List of Values',
                 'X', 'Poplist',
                 'Y', 'Long List of Values',
                 'Other')
            list_type,
         DECODE (ffvs.security_enabled_flag,
                 'N', 'No Security',
                 'Y', 'Non-Hierarchical Security',
                 'H', 'Hierarchical Security',
                 'Other')
            security_type,
         DECODE (ffvs.format_type,
                 'C', 'Char',
                 'D', 'Date',
                 'T', 'DateTime',
                 'N', 'Number',
                 'X', 'Standard Date',
                 'Y', 'Standard DateTime',
                 'I', 'Time',
                 'NULL')
            format_type,
         DECODE (ffvs.validation_type,
                 'Y',
                 'Translatable Dependent',
                 'X',
                 'Translatable Independent',
                 'F',
                 'Table',
                 'U',
                 'Special',
                 'D',
                 'Dependent',
                 'I',
                 'Independent',
                 'N',
                 'None',
                 'P',
                 'Pair')
            validation_type,
         ffvs.maximum_size max_size,
         ffvs.number_precision precision,
         CASE WHEN ffvs.format_type = 'N' THEN 'Y' ELSE 'N' END numbers_only,
         ffvs.numeric_mode_enabled_flag right_justify,
         ffvs.uppercase_only_flag uppercase,
         ffvs.protected_flag,
         ffvs.security_enabled_flag,
         ffvs.uppercase_only_flag,
         ffvs.dependant_default_value,
         ffvs.dependant_default_meaning,
         ffvs2.flex_value_set_name independent_value_set,
         ffvs.description
  FROM   applsys.fnd_flex_value_sets ffvs, applsys.fnd_flex_value_sets ffvs2
 WHERE       ffvs.parent_flex_value_set_id = ffvs2.flex_value_set_id(+)
         AND ffvs.flex_value_set_name IN ('POA_EUL_LIST')
         AND 1 = 1

Value Set not available for 'Value Set' Field Query

SELECT flex_value_set_name,
       vt.meaning validation_type_meaning,
       ft.meaning format_type_meaning,
       v.description,
       v.flex_value_set_id
FROM fnd_flex_value_sets v,
     fnd_lookups vt,
     fnd_lookups ft,
     fnd_flex_validation_tables t
WHERE (UPPER (flex_value_set_name) LIKE :1
       AND (   flex_value_set_name LIKE :2
            OR flex_value_set_name LIKE :3
            OR flex_value_set_name LIKE :4
            OR flex_value_set_name LIKE :5))
      AND (    vt.lookup_type = 'SEG_VAL_TYPES'
           AND vt.lookup_code = v.validation_type
           AND ft.lookup_type = 'FIELD_TYPE'
           AND ft.lookup_code = v.format_type
           AND v.flex_value_set_id = t.flex_value_set_id(+)
           AND v.flex_value_set_name NOT LIKE '$FLEX$.%'
           AND v.validation_type IN ('I', 'F')
           AND v.format_type = 'C'
           AND v.alphanumeric_allowed_flag = 'Y'
           AND v.uppercase_only_flag = 'N'
           AND v.numeric_mode_enabled_flag = 'N'
           AND ( (v.validation_type = 'I' AND v.maximum_size <= 30)
                OR (    v.validation_type = 'F'
                    AND v.maximum_size <= 80
                    AND t.id_column_type IN ('C', 'V')
                    AND t.id_column_size <= 30
                    AND t.value_column_type IN ('C', 'V')
                    AND t.value_column_size <= 80)))
ORDER BY v.flex_value_set_name;

Independent Value set Details Query

SELECT ffvs.flex_value_set_id,
       ffvs.flex_value_set_name,
       ffvs.description set_description,
       ffvs.validation_type ,
       ffv.flex_value_id,
       ffv.flex_value,
       ffvt.flex_value_meaning,
       ffvt.description value_description
FROM fnd_flex_value_sets ffvs, fnd_flex_values ffv, fnd_flex_values_tl ffvt
WHERE     ffvs.flex_value_set_name = 'XXX_YES_NO'
      AND ffvs.flex_value_set_id = ffv.flex_value_set_id
      AND ffv.flex_value_id = ffvt.flex_value_id
      AND ffvt.language = USERENV ('LANG');

Different type of xml tags in bi publisher

Not null:

<?if@column:G_MON1_QTY_MON1!=''?>

If not null

<?xdoxslt:ifelse(UNIT_PRICE!='',UNIT_PRICE,0)?>

If null then

<?xdoxslt:ifelse(UNIT_PRICE ='',UNIT_PRICE,0)?>

<?xdofx:if TOTAL_COST = '' then 0 end if?>

<?xdofx:if TOTAL_COST = '' then TOTAL_COST= 0 end if?>

If with < or > process

<?xdofx:if ITEM_COST <10 then 'John' else 'Smith' end if?>

write decode condition in xml tag

<?xdofx:Decode(tag_name ,'',0,tag_name?>

tamplate with matrix format

<?for-each-group:G_SUPPLIER;ITEM?>// Group name;column name //

cross table xml tag Row act as column process in xml tag
<?for-each-group@column:G_SUPPLIER;PO_CREATION_MONTH?> // group name;column name //

<?for-each-group@cell://G_BUDGET;./TASK_NAME?>

cross table xml tag Row act as column with other details

<?for-each-group@cell:current-group();PO_CREATION_MONTH?>


section command in xml tag:
<?for-each-group@section:G_1;./FK_REF_REGION_CODE?>

<?if@section:count(//G_1)=0?>No Data Found<?end if?>

<?end for-each-group?>


Detail Solution

 I am giving this solution for Standard Check Printing Report. Tree structure of data (Sample XML data is as follow).

<LIST_G_CHECKS>

    <G_CHECKS>  -- Top Most root -- Header

      <C_CHECK_NUMBER>21897</C_CHECK_NUMBER>

      <C_VENDOR_NUMBER>2205</C_VENDOR_NUMBER>

      <LIST_G_INVOICES>

        <G_INVOICES> -- Inner loop - Line Section

          <C_PAYMENT_NUMBER>1</C_PAYMENT_NUMBER>

          <C_INVOICE_NUMBER>ERS-20-SEP-06-243</C_INVOICE_NUMBER>

        </G_INVOICES>

        <G_INVOICES> -- Inner loop - Line Section

          <C_PAYMENT_NUMBER>2</C_PAYMENT_NUMBER>

          <C_INVOICE_NUMBER>ERS-20-SEP-06-244</C_INVOICE_NUMBER>

        </G_INVOICES>

      </LIST_G_INVOICES>

    </G_CHECKS>

</LIST_G_CHECKS>



Below is the step-step guide which I follow.



1) Open the Outermost for loop --  G_CHECKS

<?for-each@section:G_CHECKS?>



2) Declare Global Variable called ‘no_of_lines_per_page’  -- In this case I have fixed 40 lines per page.

<xsl:variable name="no_of_lines_per_page" select="number(40)"/>



3) Declare incontext variable for inner group (G_INVOICES), variable is called ‘inner_group’

<xsl:variable xdofo:ctx="incontext" name="inner_group” select=".//G_INVOICES"/>



4) Open the Inner Loop

   <?for-each:$inner_group?>



5) Before putting any elements with the help of current record pointer 'position()’, I am checking if the current position is modulizing with the no_of_lines_per_page equals zero or not. If it reaches the first record after modulizing then I will create local variable 'first_rec' and initialize it with '0'.

<?if:(position()-1) mod $no_of_lines_per_page=0?><xsl:variable name="first_rec" xdofo:ctx="incontext" select="position()"/>



Note : -- Above 3 steps ( 3,4,5) are created under ‘V_inner_group_And_V_First_rec’ form-field. Here there is limitation of Microsoft-word. We can enter upto 138 characters only in ‘Status’ field of ‘Add help text’ button.If you want to add more , you can do this by clicking on ‘Help Key’  which is adjacent to ‘Status’ tab.



6) If above condition holds true then we will iterate the inner loop.

<?for-each:$inner_group?>



7) I will check with the help of current record pointer 'position()' that the current record position is either greater than 'first_rec' i.e. the first record or less the 'no_of_lines_per_page' value set up earlier. If it is then show the record otherwise not otherwise it will not go in loop.

   <?if:position()>=$first_rec and position()<$first_rec+$no_of_lines_per_page?>



8) Here I am closing the inner for loop and if condition

   <?end if?><?end for-each?>



9) Here I am checking if no_of_lines of invoice is modulizing with the no_of_lines_per_page equals to zero or not ,and the same time I am checking if $first_rec+$no_of_lines_per_page is greater than no_of_lines of invoice or not. This is important step for filling the blank rows.

<?if:not(count($inner_group) mod $no_of_lines_per_page=0) and ($first_rec+$no_of_lines_per_page>count($inner_group))?>





10) Now I am calling sub-template recursively for filling the blank rows. While calling this template I am passing one parameter which is having value of no_of_rows to fill. Sub-template will have just one row table.

<xsl:call-template xdofo:ctx="inline" name="countdown"><xsl:with-param name="countdown" select="$no_of_lines_per_page - (count($inner_group) mod $no_of_lines_per_page)"/></xsl:call-template>



11) Sub-template declaration

   <xsl:template name="countdown">

   <xsl:param name="countdown"/><xsl:if test="$countdown"><xsl:call-template  

   name="countdown"><xsl:with-param name="countdown" select="$countdown - 1"/> 

   </xsl:call-template></xsl:if>

   </xsl:template>



12) I have created page break after the fixed number of rows have been displayed.

   <xsl:if xdofo:ctx="inblock" test="$first_rec+$no_of_lines_per_page<=count($inner_group)">

      <xsl:attribute name="break-before">page</xsl:attribute>

   </xsl:if>



13) Finally closing outer if and inner for loop and outer for loop.

   <?end if?><?end for-each?><?end for-each?>

You can use IF condition directly or using xdofx in RTF Template. Below are few examples

(?xdofx:if LENGTH(/XXBRPRPOP/LIST_G_INIT_INFO/G_INIT_INFO/LIST_G_HEADERS/G_HEADERS/POH_CUSTOMER)=0 THEN 'DELIVER TO:'POD_REQUESTOR_NAME'('POD_QUANTITY_ORDERED')' END IF?)


(?xdofx:if LENGTH(/XXBRPRPOP/LIST_G_INIT_INFO/G_INIT_INFO/LIST_G_HEADERS/G_HEADERS/POH_CUSTOMER) !=0 AND(/XXBRPRPOP/LIST_G_INIT_INFO/G_INIT_INFO/LIST_G_HEADERS/G_HEADERS/POH_CUSTOMER)!=POD_REQUESTOR_NAME THEN 'DELIVER TO:'POD_REQUESTOR_NAME'('POD_QUANTITY_ORDERED')' END IF?)


(?if:string-length(/XXBRPRPOP/LIST_G_INIT_INFO/G_INIT_INFO/LIST_G_HEADERS/G_HEADERS/LIST_G_CANCEL_RELEASE/CANCEL_RELEASE_DATE)!=0?)


(?xdofx:if AMOUNT > 1000 then ’Higher’
else
if AMOUNT < 1000 then ’Lower’
else
’Equal’
end if?>

Status code and Phase code from fnd_concurrent_requests

FND_CONCURRENT_REQUESTS

STATUS_CODE Column:
A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting.

PHASE_CODE column.
C Completed
I Inactive
P Pending
R Running

Query to list concurrent program details with its parameter, values set and default value/type AND Attached Responsibilities

Query to list concurrent program details with its parameter, values set and default value/type AND Attached Responsibilities

1. query to list all the responsibilities attached to a user

select fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('lang')


2.to find from which responsibility a concurrent program can be run

select distinct
  a.user_concurrent_program_name,
  a.description,
  request_group_name,
  e.responsibility_name
from
  fnd_concurrent_programs_tl a,
  fnd_request_groups b,
  fnd_request_group_units c,
  fnd_responsibility d,
  fnd_responsibility_tl e
where
  a.concurrent_program_id = c.request_unit_id   
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
and a.concurrent_program_id = :p_conc_program_id

3.provide concurrent program name to the following query.

it lists all the request sets which are created with the concurrent program given.

select distinct user_request_set_name
  from fnd_request_sets_tl
 where request_set_id in
          (select request_set_id
             from fnd_request_set_programs
            where concurrent_program_id =
                     (select concurrent_program_id
                        from fnd_concurrent_programs_tl
                       where upper(user_concurrent_program_name) = upper( '&enter_prog_name')));

4. provide the request set name to the following query.
it lists all concurrent programs of this request set.

select user_concurrent_program_name
  from fnd_concurrent_programs_tl
 where concurrent_program_id in
          (select concurrent_program_id
             from fnd_request_set_programs
            where request_set_id =
                     (select request_set_id
                        from fnd_request_sets_tl
                       where upper(user_request_set_name) = upper('&request_set_name')));

5. query to list concurrent program details with its parameter, values set and default value/type:

  select fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , fav.application_short_name
      , fav.application_name
      , fav.application_id
      , fdfcuv.end_user_column_name
      , fdfcuv.form_left_prompt prompt
      , fdfcuv.enabled_flag
      , fdfcuv.required_flag
      , fdfcuv.display_flag
      , fdfcuv.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , fdfcuv.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
      , fnd_application_vl fav
 where  fcp.concurrent_program_id = fcpl.concurrent_program_id
 and    fcpl.user_concurrent_program_name = :conc_prg_name
 and    fcpl.language = 'us'
 and    fav.application_id=fcp.application_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    flv.language(+) = userenv ('lang');


 6. query to find out concurrent program details and its parameters

select fcpl.user_concurrent_program_name
     , fcp.concurrent_program_name
     , fcp.concurrent_program_id
     , fav.application_short_name
     , fav.application_name
     , fav.application_id
     , fdfcuv.end_user_column_name
     , fdfcuv.form_left_prompt prompt
     , fdfcuv.enabled_flag
     , fdfcuv.required_flag
     , fdfcuv.display_flag
from   fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpl
     , fnd_descr_flex_col_usage_vl fdfcuv
     , fnd_application_vl fav
where  fcp.concurrent_program_id = fcpl.concurrent_program_id
and    fcpl.user_concurrent_program_name = :conc_prg_name
and    fav.application_id=fcp.application_id
and    fcpl.language = 'US'
and    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;


7 :for checking the locks in concurrent jobs

select decode(request,0,'holder: ','waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type from gv$lock
where (id1, id2, type) in (select id1, id2, type from gv$lock where request>0) order by id1,request;

8 :for checking the concurrent programs running currently with details of processed time-- and start date


 select distinct c.user_concurrent_program_name,round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
 a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
 (a.actual_start_date-a.request_date)*24*60*60 as lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from   apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.fnd_concurrent_programs_tl c,apps.fnd_user d
where  a.concurrent_program_id=b.concurrent_program_id and b.concurrent_program_id=c.concurrent_program_id and
a.requested_by=d.user_id and status_code='r' order by process_time desc;
  
9 :for checking last run of a concurrent program along with processed time

-- useful to find the details of concurrent programs which run daily and comparison purpose

select distinct c.user_concurrent_program_name,
            round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) as process_time,
            a.request_id,a.parent_request_id,to_char(a.request_date,'dd-mon-yy hh24:mi:ss'),to_char(a.actual_start_date,'dd-mon-yy hh24:mi:ss'),
  to_char(a.actual_completion_date,'dd-mon-yy hh24:mi:ss'), (a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
            (a.actual_start_date-a.request_date)*24*60*60 as lag_time,
            d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from   apps.fnd_concurrent_requests a,
            apps.fnd_concurrent_programs b ,
            apps.fnd_concurrent_programs_tl c,
            apps.fnd_user d
where       a.concurrent_program_id= b.concurrent_program_id and
            b.concurrent_program_id=c.concurrent_program_id and
            a.requested_by =d.user_id and
--          trunc(a.actual_completion_date) = '24-aug-2005'
c.user_concurrent_program_name='incentive compensation analytics - odi' --  and argument_text like  '%, , , , ,%';
--          and status_code!='c'

10 :for checking the last run of concurrent program.

- use below query to check all the concurrent request running which may refer given package

-- this is very useful check before compiling any package on given instance.

-- the query can be modified as per requirement.

-- remove fnd_concurrent_requests table and joins to check all program dependent on given package.


select
 fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');

11 :the following query will display the time taken to execute the concurrent programs

--for a particular user with the latest concurrent programs sorted in least time taken

-- to complete the request.


 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;


12 : by using the below query we can get sid,serial#,spid of the concurrent request..

    select a.request_id, d.sid, d.serial# , c.spid
    from apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_processes b,
    v$process c,
    v$session d
    where a.controlling_manager = b.concurrent_process_id
    and c.pid = b.oracle_process_id
    and b.session_id=d.audsid
    and a.request_id = &request_id
    and a.phase_code = 'r';


13 : by using below concurrent manager and program rules...

--gives detail of the concurrent_queue_name and user_concurrent_program_name

select b.concurrent_queue_name, c.user_concurrent_program_name
from fnd_concurrent_queue_content a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
where a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(include_flag, 'i', 1, 2), type_code;
  

14 : gives details of running concurrent jobs


select distinct c.user_concurrent_program_name,
      round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
    a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
      (a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
      (a.actual_start_date-a.request_date)*24*60*60 as lag_time,
      d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from     apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_programs b ,
    apps.fnd_concurrent_programs_tl c,
    apps.fnd_user d
where   a.concurrent_program_id=b.concurrent_program_id and
    b.concurrent_program_id=c.concurrent_program_id and
    a.requested_by=d.user_id and
    status_code='r' order by process_time desc;
  

15 : gives detail of concurrent job completed and pending


select
 fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');

16 :gives detail of running and completed concurrent jobs with start date and end date

-- latest one at the top

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;

17 : wait events details related with concurrent programs


select s.saddr, s.sid, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
upper (s.program) program, s.type, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
from v$session s
where ( (s.username is not null)
and (nvl (s.osuser, 'x') <> 'system')
and (s.type <> 'background') and status='active'
)
order by "program";

18 : to find the pid of the concurrent job and kill it.


select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_id ='31689665'
and a.inst_id = b.inst_id and c.os_process_id = a.process;

19 :to find the database sid of the concurrent job

-- we need our concurrent request id as an input.

-- c.spid= is the operating system process id

-- d.sid= is the oracle process id


sql> column process heading "fndlibr pid"

select a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.spid
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
where a.controlling_manager = b.concurrent_process_id
and c.pid = b.oracle_process_id
and b.session_id=d.audsid
and a.request_id = &request_id
and a.phase_code = 'r';