Changing Keyboard Shortcuts in Oracle PLSQL Developer

Salary Change Query in Oracle HRMS/Payroll

select papf.employee_number,     

       papf.full_name,

       ppp_old.proposed_salary_n old_sal,

       ppp_new.proposed_salary_n new_sal,

       ppp_new.change_date

--,ppp_old.last_change_date,ppp_new.last_change_date

  from apps.per_all_people_f      papf,

       apps.per_all_assignments_f paaf,

       apps.per_pay_proposals     ppp_old,

       apps.per_pay_proposals     ppp_new

where 1 = 1

   and papf.person_id = paaf.person_id

   and paaf.assignment_type = 'E'

   and paaf.primary_flag = 'Y'

   and paaf.assignment_id = ppp_old.assignment_id

   and trunc(sysdate) between papf.effective_start_date and

       papf.effective_end_date

   and trunc(sysdate) between paaf.effective_start_date and

       paaf.effective_end_date

      --and ppp_new.change_date between trunc(sysdate) - 60 and trunc(sysdate)

      --and ppp_new.approved = 'Y'

   and ppp_new.assignment_id = paaf.assignment_id

   and ppp_new.last_change_date = ppp_old.change_date

   and ppp_new.change_date =

       (select max(x.change_date)

          from per_pay_proposals x

         where x.assignment_id = paaf.assignment_id)

   and paaf.assignment_id = 282416

Grade Change Query in Oracle HRMS

select papf.employee_number,papf.full_name,

pg_old.name old_grade, pg_new.name new_grade,paaf_new.effective_start_date change_date

from apps.per_all_people_f papf,

apps.per_all_assignments_f paaf_old,

apps.per_grades pg_old,

apps.per_all_assignments_f paaf_new,

apps.per_grades pg_new

where 1=1

and papf.person_id = paaf_old.person_id

and papf.person_id = paaf_new.person_id

and paaf_old.person_id = paaf_new.person_id

and paaf_old.assignment_type = 'E'

and paaf_old.primary_flag = 'Y'

and paaf_new.assignment_type = 'E'

and paaf_new.primary_flag = 'Y'

and pg_old.grade_id = paaf_old.grade_id

and pg_new.grade_id = paaf_new.grade_id

and paaf_old.grade_id <> paaf_new.grade_id

and trunc(paaf_old.effective_end_date)+1 =trunc(paaf_new.effective_start_date)

and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

and  trunc(sysdate) between paaf_new.effective_start_date and paaf_new.effective_end_date

Enable work list on Oracle EBS R12

For Applications release 12:

1. Please set the following profile options for the user who performs the Home Page personalization:

Personalize Self Service Definition - Yes
FND: Personalization Region Link - Yes

2. Login to Applications Home Page and select the 'Personalize Page' link.

3. Click on Personalize "Home Contenttable" link.

4. In the "Row Layout: Home Content Row" row, click on Personalize icon (pencil).

5. Change the rendered attribute to True in the "Applications Home Page" column.

6. Click on Apply.

7. Log off.

8. Log in.

9. The worklist should appear this time.

Adding Cost Center value to the OTL Timecard

The common requirement in any timecard configuration is to have a cost center field to charge the time against. By default in OTL Oracle provides the cost center field but for displaying the values in the Timecard you need to configure the ltd file and create a View to hold the cost center value

Following code can be used to create the view
CREATE OR REPLACE FORCE VIEW HXC_CUI_CUSTOM9_V ( display_value ,value ) AS select fv.flex_value'-'fv.DESCRIPTION display_value, to_char(flex_value_id) value from fnd_flex_values_vl fv, fnd_flex_value_sets fvs where fv.flex_value_set_id = fvs.flex_value_set_id and fvs.flex_value_set_name = 'GL_COA_COST_CENTER'

Following are the three layout needed to be modified

Payroll layout - hxczzhxclayt0000.ldt
Payroll Review page - hxczzhxclayt0005.ldt
Payroll Confirmation page - hxczzhxclayt0001.ldt

Replace the cost center segment with the below code

Modify the Payroll layout - hxczzhxclayt0000.ldt

BEGIN HXC_LAYOUT_COMPONENTS "Payroll Timecard Layout - Cost Center"OWNER = "CUSTOM"COMPONENT_VALUE = "COSTCENTER"REGION_CODE = "HXC_CUI_TIMECARD"REGION_CODE_APP_SHORT_NAME = "HXC"ATTRIBUTE_CODE = "HXC_TIMECARD_COST_CENTER"ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC"SEQUENCE = "190"COMPONENT_DEFINITION = "CHOICE_LIST"RENDER_TYPE = "WEB"PARENT_COMPONENT ="NEC Payroll Timecard Layout - Day Scope Building blocks for worker timecard matrix"LAST_UPDATE_DATE = "2004/05/24"
BEGIN HXC_LAYOUT_COMP_QUALIFIERS "NEC Payroll Timecard Layout - Cost Center"OWNER = "CUSTOM"QUALIFIER_ATTRIBUTE_CATEGORY = "CHOICE_LIST"QUALIFIER_ATTRIBUTE1 = "Custom9VO" ########Changed to Custm9VO ##############QUALIFIER_ATTRIBUTE4 = "N"QUALIFIER_ATTRIBUTE8 = "DisplayValue"QUALIFIER_ATTRIBUTE9 = "Value#NUMBER"
QUALIFIER_ATTRIBUTE10 ="oracle.apps.hxc.selfservice.timecard.server.Custom9VO" ########Changed to Custm9VO ##############
QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_END_DATETIMECARD_BIND_END_DATE"QUALIFIER_ATTRIBUTE17 = "OraTableCellText"QUALIFIER_ATTRIBUTE20 = "N"QUALIFIER_ATTRIBUTE21 = "Y"QUALIFIER_ATTRIBUTE22 = "L"QUALIFIER_ATTRIBUTE25 = "FLEX"QUALIFIER_ATTRIBUTE26 = "Dummy Cost Context"QUALIFIER_ATTRIBUTE27 = "Attribute2"LAST_UPDATE_DATE = "2004/05/24"END HXC_LAYOUT_COMP_QUALIFIERS
END HXC_LAYOUT_COMPONENTS

Modify the Payroll Review page - hxczzhxclayt0005.ldt

BEGIN HXC_LAYOUT_COMPONENTS "Payroll Review Layout - Cost Center" OWNER = "CUSTOM" COMPONENT_VALUE = "COSTCENTER" REGION_CODE = "HXC_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_TIMECARD_COST_CENTER" ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC" SEQUENCE = "160" COMPONENT_DEFINITION = "CHOICE_LIST" RENDER_TYPE = "WEB" PARENT_COMPONENT = "NEC Payroll Review Layout - Day building blocks - matrix layout" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "NEC NEC Payroll Review Layout - Cost Center" OWNER = "CUSTOM" QUALIFIER_ATTRIBUTE_CATEGORY = "CHOICE_LIST" QUALIFIER_ATTRIBUTE1 = "Custom9VO" #QUALIFIER_ATTRIBUTE8 = "DisplayValue" #QUALIFIER_ATTRIBUTE9 = "Value#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.Custom9VO" #QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_END_DATETIMECARD_BIND_END_DATE" #QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "Dummy Cost Context" QUALIFIER_ATTRIBUTE27 = "Attribute2" QUALIFIER_ATTRIBUTE30 = "Y" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS

Modify Payroll Confirmation page - hxczzhxclayt0001.ldt

BEGIN HXC_LAYOUT_COMPONENTS "Payroll Confirmation Layout - Cost Center" OWNER = "CUSTOM" COMPONENT_VALUE = "COSTCENTER" REGION_CODE = "HXC_CUI_TIMECARD" REGION_CODE_APP_SHORT_NAME = "HXC" ATTRIBUTE_CODE = "HXC_TIMECARD_COST_CENTER" ATTRIBUTE_CODE_APP_SHORT_NAME = "HXC" SEQUENCE = "210" COMPONENT_DEFINITION = "CHOICE_LIST" RENDER_TYPE = "WEB" PARENT_COMPONENT = "NEC Payroll Confirmation Layout - Day Scope Building blocks for worker timecard matrix" LAST_UPDATE_DATE = "2004/05/24" BEGIN HXC_LAYOUT_COMP_QUALIFIERS "NEC NEC Payroll Confirmation Layout - Cost Center" OWNER = "CUSTOM" QUALIFIER_ATTRIBUTE_CATEGORY = "CHOICE_LIST" QUALIFIER_ATTRIBUTE1 = "Custom9VO" #QUALIFIER_ATTRIBUTE8 = "DisplayValue" #QUALIFIER_ATTRIBUTE9 = "Value#NUMBER" QUALIFIER_ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.Custom9VO" #QUALIFIER_ATTRIBUTE11 = "TIMECARD_BIND_END_DATETIMECARD_BIND_END_DATE" #QUALIFIER_ATTRIBUTE17 = "OraTableCellText" QUALIFIER_ATTRIBUTE20 = "N" QUALIFIER_ATTRIBUTE21 = "Y" QUALIFIER_ATTRIBUTE22 = "L" QUALIFIER_ATTRIBUTE25 = "FLEX" QUALIFIER_ATTRIBUTE26 = "Dummy Cost Context" QUALIFIER_ATTRIBUTE27 = "Attribute2" QUALIFIER_ATTRIBUTE30 = "Y" LAST_UPDATE_DATE = "2004/05/24" END HXC_LAYOUT_COMP_QUALIFIERS END HXC_LAYOUT_COMPONENTS

Dashboards in OTL – Manager Self Service

 

Supervisor Timesheet Dashboard (Timecard Status Dashboard) (Doc ID 1504819.1)

Another Note - 1527178.1 

Running Oracle Fast Formula from PL/SQL Package Procedure

for c_formula_details_rec IN c_formula_details Loop

v_formula_id := c_formula_details_rec.formula_id;

end loop;

 

FF_EXEC.init_formula(p_formula_id => v_formula_id,

p_effective_date => v_eff_date,

p_inputs => l_f_inputs,

p_outputs => l_f_outputs);

 

IF l_f_inputs.count > 0 THEN

 

FOR i IN l_f_inputs.first .. l_f_inputs.last LOOP

IF l_f_inputs(i).name = 'BUSINESS_GROUP_ID' THEN

l_f_inputs(i).value := nBGId;

 

ELSIF l_f_inputs(i).name = 'ASSIGNMENT_ID' THEN

l_f_inputs(i).value := nAssignmentId;

 

ELSIF l_f_inputs(i).name = 'EFFECTIVE_DATE' THEN

l_f_inputs(i).value := v_eff_date;

ELSIF l_f_inputs(i).name = 'LV_ELEMENT_NAME' THEN

l_f_inputs(i).value := v_Row_Value;

 

END IF;

END LOOP;

END IF;

 

FF_EXEC.run_formula(p_inputs => l_f_inputs,

p_outputs => l_f_outputs,

p_use_dbi_cache => FALSE);

 

for i in l_f_outputs.first .. l_f_outputs.last loop

--

vActualValue:=lpad(l_f_outputs(i).value, 30);

end loop;