OTL Metalink Imp Notes

OIT to OTL Migration (Metalink, Note: 289674.1)
Oracle OTL HXC Time Store Deposit API (Metalink Note: 223987.1)
Oracle Time & Labor Implementation and User Guide (Metalink Note: 207333.1)
Oracle Time & Labor Mini-Pack About Documents
• HXT.G (Metalink Note: 258309.1)
• HXT.H (Metalink Note: 282087.1)
• HXT.I (Metalink Note: 303755.1)
• HXT.J (Metalink Note: 344022.1)
Oracle Time & Labor Timecard Configuration 11.5.10+ (Metalink Note: 304340.1; customers whose systems are not yet at the 11.5.10

OTL Imp Metalink note which will have all the setup details and API details of OTL

223987.1

OTL queries

Query to check if for a person a Timecard has been submitted or not for the start and stop time


SELECT time_building_block_id, object_version_number, start_time,
comment_text, created_by, creation_date, last_updated_by,
last_update_date, last_update_login
FROM hxc_time_building_blocks
WHERE resource_id = <employee id>
AND SCOPE = 'TIMECARD'
AND start_time = <Start Time>
AND stop_time = <stop Time>
AND date_to = hr_general.end_of_time;


Query to get the Complete Timecard Data:


SELECT *
FROM HXC_TIME_BUILDING_BLOCKS
WHERE date_to=hr_general.end_of_time
CONNECT BY prior time_building_block_id=parent_building_block_id
AND object_version_number=parent_building_block_ovn
START WITH time_building_block_id=< Timecard Id>
ORDER BY 1




Query to get all Attributes Information for a complete Timecard


SELECT htb.resource_id Person_id,
htb.start_time Start_time, htb.stop_time Stop_time,
htb2.time_building_block_id, hta.time_attribute_id,
htb2.measure, htb2.start_time, htb2.stop_time,
hta.Attribute_category
FROM hxc_time_building_blocks htb,
hxc_time_building_blocks htb1,
hxc_time_building_blocks htb2,
hxc_time_attribute_usages htau,
hxc_time_attributes hta
WHERE htb1.parent_building_block_id = htb.time_building_block_id
AND htb1.parent_building_block_ovn = htb.object_version_number
AND htb.date_to = hr_general.end_of_time
AND htb.SCOPE = 'TIMECARD'
AND htb1.SCOPE = 'DAY'
AND htb1.date_to = 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 htb2.date_to = 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 TRUNC(htb.start_time) = <start Date>
AND TRUNC(htb.stop_time) = <End Date>
AND htb.resource_id = <resource_id>




Use the preference to get the setup for a particular resource id


hxc_preference_evaluation.resource_preferences
(<.Resource_id>,'TC_W_TCRD_LAYOUT',1,SYSDATE);


Table : hxc_pref_definitions
l_pref_table hxc_preference_evaluation.t_pref_table;


hxc_preference_evaluation.resource_preferences(<resource_id>,l_pref_table,sysdate);
l_index := l_pref_table.first;
LOOP
EXIT WHEN
(NOT l_pref_table.exists(l_index));
IF (l_pref_table(l_index).preference_code = 'TC_W_TCRD_LAYOUT') THEN ....
l_index := l_pref_table.next(l_index);
END LOOP;

To Find the executable name and package name and application name of a concurrent program

 select fa.application_name, fe.executable_name,fe.execution_file_name package_name,

fcp.concurrent_program_id,fcpt.user_concurrent_program_name,fcp.concurrent_program_name

from fnd_executables fe,fnd_concurrent_programs fcp, fnd_concurrent_programs_tl fcpt, fnd_application_tl fa

where fe.executable_id = fcp.executable_id

and  fcp.concurrent_program_id = fcpt.concurrent_program_id

and fcp.concurrent_program_name in ( Concurrent program short name)

and fcp.application_id = fa.application_id

To Find the executable name and package name of a concurrent program

SELECT a.user_concurrent_program_name, b.executable_name,
      b.execution_file_name package_name
 FROM fnd_concurrent_programs_vl a, fnd_executables_vl b
WHERE a.executable_id = b.executable_id
  AND user_concurrent_program_name like    'Concurrent program name'
             

Oracle Timecard (OTL) Automation

Oracle Timecard (OTL) Automation
--------------------------------------

Many companies have announced furlough for employees- meaning the offices and manufacturing units will be shutdonw for a specified amount of time and it will be mandatory for employees to either take that time off from their vacation time, flex time, may be borrow it from next year or simply be unpaid for that time period. Whatever be the case, it saves company a lot of money that it was otherwise obligated to pay in vacation time or paid days. Now because its mandatory, it has to be in the system for the payroll to pick it up.

If the organization uses time entry system – like oracle – to enter vacation/flex time, and processes payroll then following needs to happen in order for a succesfull furlough automation.

- Employees time must be entered on the online timecard.

- The timecard must be approved by manager or Auto-approval process.

- This OTL timecard must be transferred to Batch Element Entries for Payroll.

To automate this process we will use HXC Timecard APIs provided by Oracle. These APIs help us in creating a timecard for a day, week, and also attach elements/ projects to the timecard. Also, the APIs submit the timecard with a workflow process type so it can either be picked for AUTO processing or Manual approval.

Before we go into the details, we have to see how a typical timecard is built. For the sake of simplicity, we will consider Monthly Paid (exempt) employees.

A timecard is a combination of DAYS. Each DAY will be one Row in a table. For Every day that you need to enter time (e.g. Saturday Sunday will not need a time entry being a holiday) you need a DETAIL type row also. And, for each DETAIL record that has to go into an element or project for costing or payroll purpose, we need an ATTRIBUTE also.

These are nothing but the TIME BUILDING BLOCKS. To make things simple, lets go and see these records:

select * from hxc_time_building_blocks

TYPE: MEASURE ( Number of hours to be put in that particular day) or RANGE (For period like week or day).
SCOPE: TIMECARD, DAY, DETAIL or APPLICATION_PERIOD

Now, if you see a typical timecard, this is how it looks (Use the hierarchical Query below):

TIMECARD (12/1/2008  - 12/7/2008) TYPE: RANGE
|_  DAY (12/1/2008 – 12/1/2008) TYPE: RANGE
|_  DETAIL (12/1/2008):  8 HRS   (may have attributes)  TYPE:MEASURE
|_  DAY (12/2/2008 – 12/2/2008)
|_  DETAIL (12/2/2008):  8 HRS
|_  DAY (12/3/2008 – 12/3/2008)
|_  DETAIL (12/3/2008):  8 HRS
|_  DAY (12/4/2008 – 12/4/2008)
|_  DETAIL (12/4/2008):  8 HRS
|_  DAY (12/5/2008 – 12/5/2008)
|_  DETAIL (12/5/2008):  8 HRS
|_  DAY (12/6/2008 – 12/6/2008)
|_  DAY (12/7/2008 – 12/7/2008)

SELECT htbb.time_building_block_id, htbb.TYPE, htbb.measure, htbb.unit_of_measure, htbb.start_time, htbb.stop_time,htbb.parent_building_block_id, ‘N’ parent_is_new, htbb.SCOPE,htbb.object_version_number, htbb.approval_status,htbb.resource_id, htbb.resource_type, htbb.approval_style_id,htbb.date_from, htbb.date_to, htbb.comment_text,htbb.parent_building_block_ovn, ‘N’ NEW, ‘N’ changed,htbb.application_set_id, htbb.translation_display_key
FROM apps.hxc_time_building_blocks htbb START WITH ( htbb.time_building_block_id in (6848088) AND htbb.object_version_number in (1 ) )CONNECT BY PRIOR htbb.time_building_block_id = htbb. parent_building_block_id AND PRIOR htbb.object_version_number = htbb.parent_building_block_ovn ORDER BY htbb.time_building_block_id ASC

NOW Over to APIs….


hxc_timestore_deposit.create_timecard_bb
– Create a Timecard building block (only timecard, no days, or details)
– Here are the parameters it takes
(
p_start_time => to_date(’01-DEC-2008 00:00:00′,’DD-MON-YYYY HH24:MI:SS’) ,
p_stop_time =>  to_date(’07-DEC-2008 23:59:59′,’DD-MON-YYYY HH24:MI:SS’),
p_resource_id => emp.person_id ,
p_comment_text => ‘Automated TimeCard: DEC08′,
p_approval_style_id => 41, –This is your workflow approval style, default to AUTO APPROVE
p_app_blocks => l_tbl_timecard_info,
p_time_building_block_id => l_tc_bb_id –returns the id of  TC Building block
);

hxc_timestore_deposit.create_day_bb
– Creates a DAY building block (only DAY no details)
– Here are the parameters it takes
(
p_day => l_start_date,
p_parent_building_block_id => l_tc_bb_id,
p_comment_text => ‘Automated TimeCard: DEC08′,
p_app_blocks => l_tbl_timecard_info,
p_time_building_block_id => l_day_bb_id
);

hxc_timestore_deposit.create_detail_bb
– Creates a DETAIL building block, in next step we have to attach the attribute to this DETAIL
– Here are the parameters it takes
(
p_type => ‘MEASURE’,
p_measure => 8, –Number of Hours
p_parent_building_block_id => l_day_bb_id,
p_comment_text => ‘Automated TimeCard: DEC08′,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info,
p_time_building_block_id => l_detail_bb_st_id
);
hxc_timestore_deposit.create_attribute (
p_building_block_id=> l_detail_bb_st_id,
p_attribute_name=> ‘Dummy Element Context’,
p_attribute_value=> ‘ELEMENT – 60110′, –This is the Accrual PTO Element we want to update through this API.
p_app_attributes=> l_tbl_attributes_info);

HXC_TIMESTORE_DEPOSIT.EXECUTE_DEPOSIT_PROCESS
–This is the Submission Call. This process will submit the timecard, days and details with attributes. Timecard will stay in SUBMITTED State until approved via Manual or AUTO Approve process.
(
p_validate => FALSE,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info,
p_messages => l_tbl_messages,
p_mode => ‘SUBMIT’,
p_deposit_process => ‘OTL Deposit Process’,
p_retrieval_process => ‘BEE Retrieval Process’,
p_timecard_id => l_new_timecard_id,
p_timecard_ovn => l_new_timecard_ovn
);

Hope this article helped you in understanding the basics of the Time Entry APIs. Please note that this is  the initial and basic knowledge. You will need some more knowledge – like DELETE timecards API for rollback in case of any issues. UPDATE timecard APIs etc for a full fledge capability on OTL timecard automation.

How To Delete the Approve, Reject, and Request More Information Buttons from a Workflow E-mail Notificatin That Requires Response [ID 302214.1]

Applies to:

Oracle Workflow Cartridge - Version: 11.5.9 to 11.5.10.2 - Release: 11.5 to 11.5
Information in this document applies to any platform.
Checked for relevance on 06-JUL-2011

Goal

How to Delete the Approve, Reject, and Request More Information Buttons from wokflow e-mail notifications.
Need only 'Click Here to Respond' link in order to sign into applications and access the Notifications Detail web page.
Client e-mail software does not interpret correctly the mailto tag.

Solution

1. To change the message template for ALL the notification messages requesting a response, this is the solution.
Change the  Open Notification Template parameter value for Workflow Notification Mailer.
Change the Open Notification Template from Workflow Open Mail (Templated) to Workflow Open
Mail for Outlook Express.

a. Go to Oracle Applications Manager (OAM).
b. Navigate to WF Manager.
c. Select Service Components Link.
d. Select Workflow Notification Mailer Link.
e. EDIT
f. Advance until step #4.
g. Change the the Open Notification Template from Workflow Open Mail (Templated) to Workflow Open
Mail for Outlook Express.
h. Save the changes and re-start the workflow mailer.

OR
2. To hide the 'Request More Information' link in the workflow notifications (generally in the approval workflows), do the following:

- Open the workflow builder.
- Get connected to the database.
- Open your workflow.
- Open the workflow process in which we want remove this link.
- Expand: Messages in this process.
- Goto appropriate message of the notification.
- Do right click and select 'New Attribute'.
- Enter the following data:
1. Internal Name: #HIDE_MOREINFO
2. Display Name: Request Information
3. Description: Request Information
4. Value: Y.
- Click APPLY.
- Save the workflow process definition into the database.

Next time a requisition is sent for approval, the Request More Information button will be hidden.

NOTE: This is considered a customization, if any patch overwrite this workflow, the
customization above will be lost.

Please check the PART NO: B10284-02 (Oracle Workflow Developer's Guide) for more information.

Oracle Financials GL AP AR etc & CRM documents

http://apps2fusion.com/apps-training/apps-functional-documents/gl-ap-ar-iproc-po-fa-iexpenses

Oracle workflow builder install site

http://www.oracle.com/technetwork/database/options/winclient-101059.html

Query to find the responsibilities assigned to the user

SELECT UNIQUE u.description, u.user_name user_name,
r.responsibility_name responsiblity,
a.application_name application
FROM fnd_user u,
   fnd_user_resp_groups g,
   fnd_application_tl a,
   fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND g.end_date IS NULL
AND u.end_date IS NULL
AND a.application_id in (275,809)
ORDER BY u.user_name

In Oracle, I want to know if a string value is numeric only. How can I do this?

To test a string for numeric characters, you could use a combination of the LENGTH, TRIM, AND TRANSLATE functions built into Oracle.
You can use the following command:

LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' ')))

string1 is the string value that you are testing
This function will return a null value if string1 is numeric. It will return a value "greater than 0" if string1 contains any non-numeric characters.

For example,

LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' ')));	would return 1
LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' ')));	would return 2
LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' ')));	would return null
LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' ')));

query to find the duplicate active employees in per_all_people_f

   SELECT email_address
--    INTO l_person_id
    FROM per_all_people_f
    WHERE 1=1
    AND SYSDATE Between effective_start_date and effective_end_date
    and person_type_id=6
    group by email_address
    having count(email_address) >1

Function to check if the mail address and attach to a value set

---------------------------------------------------------------------------------------
/*Function to check if the mail address is valid. it checks for any valid email address.*/
--------------------------------------------------------------------------------------
   FUNCTION check_valid_email_address (
      p_email_address_i    IN   VARCHAR2,
   )
      RETURN VARCHAR2
   AS
      l_email_result   VARCHAR2 (200);
      l_cnt            NUMBER (10)    := 0;
      l_count          NUMBER (10)    := 0;
   BEGIN
IF (INSTR (p_email_address_i, '..')) > 0
      THEN
         l_email_result := g_bad_email;
      ELSIF (INSTR (p_email_address_i, ' ')) > 0
      THEN
         l_email_result := g_bad_email;
      ELSIF (INSTR (p_email_address_i, '.')) = 0
      THEN
         l_email_result := g_bad_email;
      ELSIF (INSTR (p_email_address_i, '@')) = 0
      THEN
         l_email_result := g_bad_email;
      ELSIF INSTR (SUBSTR (p_email_address_i,
                           INSTR (p_email_address_i, '@') + 1,
                           LENGTH (p_email_address_i)
                          ),
                   '@'
                  ) > 0
      THEN
         l_email_result := g_bad_email;
      ELSIF INSTR (SUBSTR (p_email_address_i,
                           INSTR (p_email_address_i, '@') - 1,
                           LENGTH (p_email_address_i)
                          ),
                   '_'
                  ) = 1
      THEN
         l_email_result := g_bad_email;
      ELSIF SUBSTR (p_email_address_i, 1, 1) = '@'
      THEN
         l_email_result := g_bad_email;
      ELSIF INSTR (UPPER (p_email_address_i), '@_') > 0
      THEN
         l_email_result := g_bad_email;
      ELSIF INSTR (UPPER (p_email_address_i), '@-') > 0
      THEN
         l_email_result := g_bad_email;
      ELSIF SUBSTR (p_email_address_i,
                    LENGTH (p_email_address_i),
                    LENGTH (p_email_address_i)
                   ) = '.'
      THEN
         l_email_result := g_bad_email;
      ELSE
         WHILE (l_cnt < LENGTH (p_email_address_i) - 1)
         LOOP
            BEGIN
               SELECT COUNT (*)
                 INTO l_count
                 FROM DUAL
                WHERE ASCII (SUBSTR (p_email_address_i, l_cnt, l_cnt + 1))
                         BETWEEN ASCII ('A')
                             AND ASCII ('Z')
                   OR ASCII (SUBSTR (p_email_address_i, l_cnt, l_cnt + 1))
                         BETWEEN ASCII ('a')
                             AND ASCII ('z')
                   OR ASCII (SUBSTR (p_email_address_i, l_cnt, l_cnt + 1))
                         BETWEEN ASCII (0)
                             AND ASCII (9)
                   OR ASCII (SUBSTR (p_email_address_i, l_cnt, l_cnt + 1)) IN
                         (ASCII ('_'),
                          ASCII ('-'),
                          ASCII ('.'),
                          ASCII ('@'),
                          ASCII ('''')
                         );
            END;
            --Increment the counter
            l_cnt := l_cnt + 1;
         END LOOP;

         IF (l_count >= 1)
         THEN
            l_email_result := p_email_address_i;
         END IF;
      END IF;

      RETURN l_email_result;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END check_valid_email_address;

---------------------------------
Value set for valid email address
---------------------------------

FND PLSQL  "declare
l_local VARCHAR2(200) := 'Invalid';
CURSOR validate_email_id
is
select 'Valid'
from dual
where check_valid_email_address(:!VALUE,'Y') = :!VALUE
and :!VALUE <> 'BAD_EMAIL_ADDRESS';
BEGIN
open validate_email_id;
fetch validate_email_id into l_local;
if validate_email_id%NOTFOUND then
fnd_message.set_name('XXCAS_PRJ','XXCAS_PRJ_INVALID_EMAIL_ID');
fnd_message.raise_error;
end if;
close validate_email_id;
end;"

HOW TO DEFINE TRANSACTION TYPES IN ORACLE RECEIVABLES

Oracle Receivables use transaction types to default payment term, account, tax, freight, creation sign, posting, and receivables information.
Oracle Receivable provides two predefined transaction types:

1. Invoice
2. Credit Memo.

We can create receivable transaction type by using following steps

1. Sign on to the application using Receivables Super user Responsibility.

1. Navigate to the Transaction Types Form

Setup / Transactions / Transaction Types

1. In operating Unit field by default current operating unit is populated Vision Operations

1. Enter a Name and Description for this transaction type.

Name: = XX Credit Memo

Description:-This is a Sample Credit Memo

1. Enter a Class for this transaction type.

Following are the seeded classes provided by oracle

1. Invoice
2. Chargeback
3. Credit Memo
4. Debit Memo
5. Deposit
6. Guarantee

In credit memo case I select Credit Memo

1. Open Receivable is set to Yes,

Receivables updates customer balances each time we create a complete debit memo, credit memo, chargeback,
or on-account credit with this transaction type. Receivables also include these transactions in the standard aging and collection processes.

1. Check the Post to GL box

To be able to post transactions with this type to general ledger.
The default is the value specified for the Open Receivables option. This box must be checked if the class is Deposit or Guarantee.

1. Choose Print. We can override this value when entering transactions in transaction window.

1. Choose Transaction Status of Open from open, Closed, Pending, or Void.

Use these statuses to implement our own invoice approval system. Enter 'Void' to void debit memos, on-account
credits or invoices to which we want to assign this transaction type.

1. Check the Allow Freight box.

Allow freight to be entered for transactions with this transaction type

1. Check the Tax Calculation box.

Receivables calculate tax for transactions with this transaction type.
If we do not check this box, the Tax Code field in the Lines window will not be required and Receivables
will not perform tax calculations or create tax accounting entries for transactions with this
transaction type (this is also applicable for transactions in Oracle Order Management).

1. Choose Creation Sign Negative.

The default is Positive Sign for transaction types with a class of either Guarantee or Deposit.
If we are using the Cash Basis accounting method, Transaction's creation sign must be Positive Sign, Negative Sign, or Any Sign.
We cannot update this field after entering transactions with this type.

1. Select the Natural Application Only check box. We cannot update this option after save this transaction type.

1. Enter the Receivable Account for transactions with this transaction type.
   Receivables use this information, along with Auto Accounting definition, to determine the receivable accounts
   for transactions with these types.
   Receivables creates a receivables transaction record using this account so we can transfer to general ledger and
   create a journal entry if Post To GL is Yes for this transaction type.
   

1. Enter receivable account “Accounts Receivable” .
   Receivables does not require entering a Receivable account for Credit Memo transaction types if the profile option Use
   Invoice Accounting for Credit Memos is set to YES otherwise, must enter a Receivable Account.

1. Enter a Freight Account for transactions with this transaction type.
   Receivable uses this information, along with Auto Accounting definition, to determine the freight account for transactions with this transaction type.

1. Enter Revenue Account Receivables does not require to enter a Revenue Account for Credit Memo transaction
   types if the profile option Use Invoice Accounting for Credit Memos is set to YES. Otherwise, must enter a Revenue Account.

1. As this is a credit memo so we need to define UNBILLED RECEIVABLE ACCOUNT.
   Receivable uses this information, along with Auto Accounting definition, to determine the Unbilled Receivable
   account for transactions with this transaction type.
   Receivable only uses the Unbilled Account field to determine the Unbilled Receivable account for invoices with the rule Bill in Arrears.

1. Enter an Unearned Revenue Account. Receivable uses this information, along with Auto Accounting
   definition, to determine the unearned revenue account for transactions with this transaction type.
   Receivable only uses this account when transaction's invoicing rule is Bill In Advance.

1. Enter a Tax Account. Receivable uses this information along with Auto Accounting
   definition to determine the tax account for transactions with this transaction type.

1. Save this credit memo transaction type now.

1. Navigate to the Transaction Form

Transaction  Transactions

Look in above screen shot I have select XX Credit Memo Type

All Oracle apps exam papers

http://www.peteprobinson.com/Exams/Test%20in%20.pdf/

To find the parameters of the concurrent program from back end

      SELECT  *
  FROM  fnd_descr_flex_col_usage_vl ds,
        fnd_flex_value_sets         vs,
        (SELECT  meaning,
                 lookup_code
          FROM   fnd_lookup_values
         WHERE   lookup_type  =  'FLEX_DEFAULT_TYPE') lookup      
 WHERE  ds.descriptive_flexfield_name  LIKE '%'||'XXCAS_PRJ_PROJ%' ---Package name
   AND  ds.flex_value_set_id           =     vs.flex_value_set_id
   AND  ds.DEFAULT_TYPE                =     lookup.lookup_code (+)
ORDER BY ds.column_seq_num;

Create Oracle FND_USER with System Administrator

f you have the Apps Password, its quite easy to create a FND_USER for yourself by using the API.
I find this script very useful when development environment gets cloned from Production(that is when i do not have FND_USER in Production.
Please note that:-
1. You will be allocated System Administrator by this script. Hence you can assign whatever responsibilities that you desire latter, after logging in.
2. The password will be set to oracle
3. You need apps password to run this script. Alternately you need execute permission on fnd_user_pkg from the user where this script will be run. If using some other user, please use apps.fnd_user_pkg.createuser
4. You need to do a COMMIT after this script has run. I have not included the commit within this script.
5. When running this script, you will be prompted to enter a user name.

--------Beging of script--------------
DECLARE
--By: Anil Passi
--When Jun-2001
  v_session_id INTEGER := userenv('sessionid');
  v_user_name  VARCHAR2(30) := upper('&Enter_User_Name');
BEGIN
  --Note, can be executed only when you have apps password.
  -- Call the procedure to Creaet FND User
  fnd_user_pkg.createuser(x_user_name                  => v_user_name
    ,x_owner                      => ''
    ,x_unencrypted_password       => 'oracle'
    ,x_session_number             => v_session_id
    ,x_start_date                 => SYSDATE - 10
    ,x_end_date                   => SYSDATE + 100
    ,x_last_logon_date            => SYSDATE - 10
    ,x_description                => 'appstechnical.blogspot.com'
    ,x_password_date              => SYSDATE - 10
    ,x_password_accesses_left     => 10000
    ,x_password_lifespan_accesses => 10000
    ,x_password_lifespan_days     => 10000
    ,x_employee_id                => 30 /*Change this id by running below SQL*/
     /*  
     SELECT person_id
           ,full_name
     FROM   per_all_people_f
     WHERE  upper(full_name) LIKE '%' || upper('<ampersand>full_name') || '%'
     GROUP  BY person_id
              ,full_name
     */
    ,x_email_address => 'appstechnical.blogspot@gmail.com'
    ,x_fax           => ''
    ,x_customer_id   => ''
    ,x_supplier_id   => '');
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'SYSADMIN'
                      ,resp_key       => 'SYSTEM_ADMINISTRATOR'
                      ,security_group => 'STANDARD'
                      ,description    => 'Auto Assignment'
                      ,start_date     => SYSDATE - 10
                      ,end_date       => SYSDATE + 1000);
END;
/

Find Request Group for Concurrent Programs

I have created a concurrent program, and I now wish to know which request group must this program be added to?

Step 1. Ensure that profile option "Utilities:Diagnostics" is set to Yes at your user level.

Note:- This step can be skipped in case you have the apps password.

Step 2. Navigate to the responsibility from where you will submit the concurrent program.

Step 3. Navigate to the "Submit New Request" screen, where you will enter the name of the concurrent program to run.

Step 4. Now you must do examine using the help menu as shown below.

Step 5. In the block field, enter PARAMETER, and in the field name enter REQUEST_GROUP_CODE

Note down this value
Step 6. Now in the value field enter REQUEST_GROUP_APPL_SHORT_NAME

Note down the two values from Step 5 and Step 6. 

These will indicate the request group to which you must add your concurrent program.

Question: In the submit request screen, REQUEST_GROUP_CODE is blank??

In this case use the Request group specified in "Define Responsibility Screen"

The rule is :-

If request group code is being passed in as parameter to Submit Request form function, then use that request group. Or else use the request group at responsibility level.

Context Sensitive Descriptive Flexfields

A training article on Context Sensitive Descriptive flexfields.
IMPORTANT:- You must read Basics Flexfield article . This article is an extension to the work that we did for a simple flexfields. 

First some basic Question and answers, and then we will do screenshots detailing how flexfields are configured.


Question: I want these Flex fields to appear in screen only when certain conditions are met. Is it possible?
Answer: Yes, you can. Lets take an Example. You have a "Bank Branch" screen where you enter the Bank names and their branches . There is a field named Branch Type in that screen. You wish to show & capture following new fields:-
a.    Banks Country of Origin Field ( regardless of bank branch type, we must show this new field). As we configured in earlier training chapter.
b.    If user entered a value of SWIFT in Branch type, then display a new DFF segment "SWIFT Partner field".
c.    If Branch type CHIPS is selected by the user, then display a DFF segment "Chip ID" field.


In Order to do this, we will follow the below steps(screenshots will follow) :-
1.    Navigate to the DFF Registration screen in Oracle Apps and query on Table AP_BANK_BRANCES. Now click on Reference Field, and ensure that “BANK_BRANCH_TYPE” field can be used as a context switch. In our case we add this field as a reference field.
2.    Navigate to DFF Segments screen and query on the Title of the “Bank Branch” and Unfreeze the Flexfield and add segments as in screenshot below. Register the BANK_BRANCH_TYPE field as a reference item.
3. Create the contexts for each possible value of Bank Branch Type field(for which you want conditional display of fields)....

Hmmmm not clear yet, see the the screenshots and you will surely understand......

Find out the table name for Bank Branch screen, this will enable us to find the DFF that is available for Bank Branch 

The Bank Branch can have one of the following values.

Given that our DFF will be sensitive to what we enter in Bank Branch, we must find out name of the field(not the display name). This can be done by using examine as shown

We can see that internal name of the Branch Type is "BANK_BRANCH_TYPE". We need to ensure that a DFF can be made sensitive to the value in BANK_BRANCH_TYPE

Lets navigate to DFF registration screen, and using the table name, we query DFF for AP_BANK_BRANCHES.

Alternately you can query using DFF title too.

Now lets add BANK_BRANCH_TYPE as a REFERENCE Field, by clicking on Button Reference

Now we need to define the new fields(segments). This screen is accessed via menu /Descriptive/Segments . In this screen, lets make BANK_BRANCH_TYPE as the context/reference. This means that DFF will become sensitive to values in Branch Type field

IMPORTANT: If your requirement is not to have any conditiional logic, then no need for all the "Reference Field" blaaa. All you need to do is to add your segment to Global Data Elements

When the user selects Type=SWIFT, we see the relevant SWIFT field appear in Flexfield window


When user selects Type=CHIPS, we see CHIP Id field appearing in Flexfield window.

Here we now see that value entered in DFF field gets stored in the database column,
 

Descriptive Flexfield Basics in Oracle Apps

A training article on Descriptive flexfields, also refered as DFF

First some basic Question and answers, and then we will do screenshots detailing how flexfields are configured.

Question: What does DFF mean?
Answer: DFF is a mechanism that lets us create new fields in screens that are delivered by Oracle.

Question: Oh good, but can these new fields be added without modifying/customization of the screen?.
Answer: Yes, certainly. Only some setup is needed, but no programmatic change is needed to setup DFF.

Question: Why the word Descriptive in Name DFF?
Answer: I think Oracle used this terminology because by means of setup...you are describing the structure of these new fields. Or may be Oracle simply used a silly word to distinguish DFF from KFF(discussed in latter training lesson).

Question: Are these DFF's flexible?
Answer: A little flexible, for example, depending upon the value in a field, we can make  either Field1 or Field2  to appear in DFF.

Question: So we create new fields in existing screen, but why the need of doing so?
Answer: Oracle delivers a standard set of fields for each screen, but different customers have different needs, hence Oracle lets us create new fields to the screen.

Question: Are these new fields that get created as a result of DFF free text?
I mean, can end user enter any junk into the new fields that are added via DFF?
Answer: If you attach a value set to the field(at time of setup of dff), then field will no longer be free text. The entered value in the field will be validated, also a list of valid values will be provided in LOV.

Question : Will the values that get entered by the user in dff fields be updated to database?
Answer: Indeed, this happens because for each field that you create using DFF will be mapped to  a column in Oracle Applications.

Question: Can I create a DFF on any database column?
Answer: Not really. Oracle delivers a predefined list of columns for each table that are meant for DFF usage. Only those columns can be mapped to DFF segments. These columns are named similar to ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3 ETC. Usually Oracle provides upto 15 columns, but this number can vary. 

Question: Can I add hundreds of fields to a given screen?
Answer: This depends on the number of attribute columns in the table that screen uses. Also, those columns must be flagged as DFF enabled in DFF Registration screen. Don't need to worry much about this because all the ATTRIBUTE columns are by default flagged for their DFF usage.

Question: Hmmm, I can see that DFFs are related to table and columns...
Answer: Yes correct. Each DFF is mapped to one table. And also each segment(or call it field) is mapped to one of the attribute columns in that table.

Question: I want these fields to appear in screen only when certain conditions are met. Is it possible?
Answer: Yes, we have something known as Context Sensitive Descriptive Flexfields.

In Order to do this, we will follow the below steps(screenshots will follow) :-
1.    Navigate to the DFF Registration screen in Oracle Apps and query on Table AP_BANK_BRANCES. Now click on Reference Field
2.    Navigate to DFF Segments screen and query on the Title of the “Bank Branch” and Unfreeze the Flexfield and add segments as to Section "GLOBAL Data Elements" as shown in screenshots.

Here are the screenshots......The descriptions are embedded within the screenshots.

We are in "Bank Branches screen" below, that is available in Payables responsibility. We need to add a new field as below.


Once having noted down the table, we try to find the Title of the DFF for that Table. We go to Flexfield/Register


Here we pick the Title of the respective DFF


Query on that DFF Title from Descriptive Flexfield Segment Screen


Add a new segment under "Global Data Elements"


The options for making mandatory or enabling validations for the new field.

Once you finalize the changes, you will be prompted to Freeze the DFF definition. Click on OK


Now, we see the fruits of our configuration

Difference between Lookups and Value Sets

Difference between Lookups and Value Sets

I hope you have read the previous articles on Value Sets and also on Lookups. It is important for the learners to read things in Sequence. Hence you may decide to browse through the Training Index Page. Difference 1 Value sets can be attached to parameters of a concurrent program, whereas Lookups can't. Difference 2 Certain types of Lookups are maintainable by the users too, for example HR Users will maintain "Ethnic Minority" lookups. Value Sets are almost never maintained by end users, with the exception of GL Flexfield codes. Value sets are usually maintained by System Administrators. Difference 3 Value sets can contain values that are a result of an SQL Statement. Hence it is possible to make Value Set list of values dynamic. On the contrary, Lookup Codes are Static list of values.

Value sets in oracle apps

Value Sets

Value Set is a collection of values. It validates the values entered by the user for a flex-field, report parameters or a concurrent.

The navigation path: Application :- Validation :- Sets

Value Set is associated with the following validations which holds the key to the value sets.

Types of Validations:

1. None :- Validation is minimal.

2. Independent :- The data input is based on previously defined list of values.

3. Dependent :- The data input is checked against a defined set of values.

4. Table :- The data input is checked against values in a table.

5. Special :- The data input is checked against a flex-field.

6. Pair :- The data input is checked against two flex-field specified range of values.

7. Translatable independent :- The data input is checked against previously defined list of values.

8. Translatable dependent :- The data input is checked against defined set of values.

Significance of $FLEX$

$FLEX$: enables to match the prior segment with either value set name or segment name.

Let v2 be the value set definition of 2nd parameter and v1 be the value set definition for the first parameter then

In the value set definition of v2 = value $FLEX$.v1

Note:

Changes You Should Never Make

You should never make these types of changes (old value set to new value set) because you will corrupt your existing key flex-field combinations data:

. Independent to Dependent

. Dependent to Independent

. None to Dependent

. Dependent to Table

. Table to Dependent

. Translatable Independent to Translatable Dependent

. Translatable Dependent to Translatable Independent

. None to Translatable Dependent

. Translatable Dependent to Table

. Table to Translatable Dependent