Tuesday, November 29, 2011

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;

Saturday, October 15, 2011

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

Monday, October 3, 2011

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'
             

Saturday, October 1, 2011

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.

Monday, September 26, 2011

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.

Sunday, September 25, 2011

Oracle Financials GL AP AR etc & CRM documents

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