Author Archives: Syed Farhan Ashraf

About Syed Farhan Ashraf

This site is for sharing knowledge of Oracle HRMS. It mainly focuses on stuff for oracle apps starters and gives them a step by step guideline.

Programming Helping

By | September 4, 2017

While development there are lots of programming piece of codes that we use again and again. In this post, we shall be piling up such similar codes or tips or code structures which can help us during development. Structure of Creating Function create or replace function lsg_function return number is ln_temp number; begin ln_temp :=… Read More »

Update Bank Payment Method API

By | May 22, 2017

This API Updates the Bank Payment Method Detail.   DECLARE ln_method_id PAY_PERSONAL_PAYMENT_METHODS_F.PERSONAL_PAYMENT_METHOD_ID%TYPE; ln_ext_acc_id PAY_EXTERNAL_ACCOUNTS.EXTERNAL_ACCOUNT_ID%TYPE; ln_obj_ver_num PAY_PERSONAL_PAYMENT_METHODS_F.OBJECT_VERSION_NUMBER%TYPE; ld_eff_start_date DATE; ld_eff_end_date DATE; ln_comment_id NUMBER; cursor c1 is select pm.assignment_id,pm.object_version_number,pm.PERSONAL_PAYMENT_METHOD_ID,ac.segment2, ac.segment12,pm.effective_start_date,pm.effective_end_date ,’QNBS8′ stg_emp_Branch_code, stg_segment_12,pm.EXTERNAL_ACCOUNT_ID ,mv.employee_number pf from PAY_PERSONAL_PAYMENT_METHODS_F pm, PAY_EXTERNAL_ACCOUNTS ac, xxlsg_emp_master_v mv,XXLSG_PERSON_UPDATE_STG stg where 1=1 and pm.external_account_id = ac.external_account_id and pm.assignment_id = mv.assignment_id and mv.employee_number = stg.employee_number… Read More »

Calculate Leave Between Two Dates

By | December 5, 2016

Following function will help you to get the number of leaves between two dates. CREATE OR REPLACE function APPS.lsg_get_lv_btw_two_dates(p_person_id number,P_Period_Start_date date,P_Period_End_date date,p_leave_name varchar2) return number is ln_lv_curr_mnth_cnt number:=0; ln_lv_curr_mnth_cnt1 number:=0; ln_lv_curr_mnth_cnt2 number:=0; ln_lv_curr_mnth_cnt3 number:=0; ln_lv_curr_mnth_cnt4 number:=0; p_lv_st_dt date; p_lv_end_date date; date1 date; date2 date; –Created on December 5, 2016 by Syed Farhan Ashraf –This Function… Read More »

Steps to Implement Zoom Functionality through Custom.PLL

By | August 15, 2016

Table of Contents Business Requirenment Solution concept Find the form name and block name Copy Custom.PLL from Server to your Development Machine Modify Zoom function Identify the Executables Function Name Modify Event Procedure Migrate and Compile the CUSTOM.PLL Test the Form Business Requirnment It is a very common requirement to open a form from another… Read More »

Step By Step Guide to Create EIT in Oracle HRMS

By | March 31, 2016

Extra Information Types Oracle defines EIT in following words. Extra Information Types (EITs) is a descriptive flexfield that facilitates us to add an unlimited number of information types to six available important entities in Oracle HRMS. EITs do not appear automatically in any responsibility. You must set up responsibility-level access for EITs. Extra Information types… Read More »


By | February 14, 2016

API to Update People Group Field in Employee Assignment  declare l_effective_start_date DATE; l_effective_end_date DATE; l_people_group_id NUMBER := 62; — 2; –2067; l_group_name VARCHAR2 (500); l_org_now_no_manager_warning BOOLEAN; l_other_manager_warning BOOLEAN; l_spp_delete_warning BOOLEAN; l_entries_changed_warning VARCHAR2 (500); l_tax_district_changed_warning BOOLEAN; l_special_ceiling_step_id NUMBER; l_object_version_number NUMBER; l_soft_coding_keyflex_id NUMBER; ——————— — Local Variables — ——————— l_concatenated_segments VARCHAR2 (400); l_no_managers_warning BOOLEAN; l_comment_id NUMBER; l_batch_run_number… Read More »

Oracle Payroll Tables and Payroll Queries

By | December 9, 2015

Important Tables There is a big list of oracle payroll tables. But these are main tables which every technical consultant must know in detail. I have categorized these tables into following three categories. Element Entry Related Tables ————————— PAY_ELEMENT_TYPES_F PAY_ELEMENT_ENTRIES_F PAY_INPUT_VALUES_F PAY_ELEMENT_LINKS_F Payroll Run Related Tables ————————- PAY_ALL_PAYROLLS_F PAY_PAYROLL_ACTIONS PAY_ASSIGNMENT_ACTIONS PAY_RUN_RESULTS PAY_RUN_RESULT_VALUES Costing & Pre… Read More »

Get Columns Values in Row in SQL Query

By | December 8, 2015

Requirement: We have two columns in a table. One column having test name and other column having defect names against one test. We need to write a query to bring all defects against the test. Creating Scenario & its Solutions Tables Creation Script create table testcase(testname varchar2(100),defname varchar2(100)) Function to get the list in a… Read More »