0% found this document useful (0 votes)

358 views134 pages

Payroll Package Functions

The document defines a package called APPS.XXLAM_HR_PAYROLL_ALL that contains functions for retrieving employee payroll information such as salary, benefits, and leave balances. The functions accept employee and date parameters and return values like payroll names, element entry IDs, balances, and calculated values for items like basic salary, transportation, and housing allowances. The package will be used to access and calculate employee compensation and benefits data for payroll processing.

Uploaded by

Zafar Iqbal

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as TXT, PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

358 views134 pages

Payroll Package Functions

Uploaded by

Zafar Iqbal

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as TXT, PDF, TXT or read online on Scribd

You are on page 1/ 134

APPS.

XXLAM_HR_PAYROLL_ALL
APPS.XXLAM_HR_ABSENCE_DURATION

========================================================

CREATE OR REPLACE PACKAGE APPS.XXLAM_HR_PAYROLL_ALL

AS
-- ----------------------------------------------------------------------------
-- |-------------------------< get_action_id >--------------------------|
-- ----------------------------------------------------------------------------
FUNCTION get_action_id (p_assignment_id IN NUMBER,
p_effective_date IN DATE)
RETURN NUMBER;

----------------------------------------------------------------------------
-- |-------------------------< get_Basic_id >--------------------------|
-- ----------------------------------------------------------------------------
FUNCTION get_basic_at (p_assignment_id NUMBER, p_date DATE)
RETURN NUMBER;

-- ----------------------------------------------------------------------------
-- |-------------------------< get_balance >--------------------------|
-- ----------------------------------------------------------------------------
FUNCTION get_balance (p_assignment_id IN NUMBER,
p_bal_name IN VARCHAR2,
p_bal_dim IN VARCHAR2,
p_effective_date IN DATE)
RETURN NUMBER;

FUNCTION get_balance (p_assignment_id IN NUMBER,

p_business_group_id IN NUMBER,
p_bal_name IN VARCHAR2,
p_bal_dim IN VARCHAR2,
p_effective_date IN DATE)
RETURN NUMBER;

FUNCTION get_balance (p_assignment_id IN number,

p_item_name IN varchar2,
p_effective_date IN date DEFAULT SYSDATE )
RETURN number;

-- ----------------------------------------------------------------------------
-- |-------------------------< get_salary_at >--------------------------|
-- ----------------------------------------------------------------------------
FUNCTION get_gross_at (p_assignment_id NUMBER, p_date DATE)
RETURN NUMBER;

FUNCTION calculate_basic_salary (p_assignment_id IN NUMBER,

p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_month_value OUT NUMBER,
p_week_holi_pay OUT NUMBER,
p_maternity_pay OUT NUMBER,
p_maternity_days OUT NUMBER,
p_sick_pay OUT NUMBER,
p_sick_ded OUT NUMBER,
p_sick_days OUT NUMBER)
RETURN NUMBER;

FUNCTION calculate_trans_all (p_assignment_id IN NUMBER,

p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_month_value OUT NUMBER)
RETURN NUMBER;

FUNCTION calculate_housing_all (p_assignment_id IN NUMBER,

p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_month_value OUT NUMBER)
RETURN NUMBER;

FUNCTION calculate_other_all (p_assignment_id IN NUMBER,

p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_month_value OUT NUMBER)
RETURN NUMBER;

-- ----------------------------------------------------------------------------
-- |-------------------------< calculate_gratuity >--------------------------|
-- ----------------------------------------------------------------------------
--
FUNCTION calculate_gratuity (p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_initiator IN VARCHAR2,
p_contract IN VARCHAR2,
p_service_period OUT NOCOPY VARCHAR2)
RETURN NUMBER;

-- ----------------------------------------------------------------------------
-- |-------------------------< get_payroll_name >-----------------------------|
-- ----------------------------------------------------------------------------
FUNCTION get_payroll_name (p_emp_no varchar2, p_date DATE)
RETURN VARCHAR2;

-- ----------------------------------------------------------------------------
-- Get Person Type
-- ----------------------------------------------------------------------------
FUNCTION get_person_type (p_name_or_id VARCHAR2,
p_person_id NUMBER,
p_effective_date DATE DEFAULT SYSDATE )
RETURN VARCHAR2;

-- ----------------------------------------------------------------------------
-- Get Insurance Type
-- ----------------------------------------------------------------------------
FUNCTION GET_INSURANCE_TYPE (P_BUSINESS_GROUP_ID NUMBER,
P_ASSIGNMENT_ID NUMBER,
P_DATE DATE)
RETURN Varchar2;

-- ----------------------------------------------------------------------------
-- Get GL Conversion Rate
-- ----------------------------------------------------------------------------

FUNCTION GET_GL_DAILY_RATE (p_from_curr Varchar2,

p_to_curr Varchar2,
P_DATE DATE)
RETURN Number;

-- ----------------------------------------------------------------------------
-- Get Advance Salary Flag
-- ----------------------------------------------------------------------------
FUNCTION get_adv_sal_flag (p_element_entry_id NUMBER,
p_effective_date DATE)
RETURN VARCHAR2;

--------------------------------------------------------------------------------
-- Get Weekoff Pay
--------------------------------------------------------------------------------

FUNCTION xxotl_get_week_off_pay (p_assignment_id IN Number,

p_period_start_date IN DATE,
p_period_end_date IN DATE)
RETURN NUMBER;

--------------------------------------------------------------------------------
-- Get Hours Per Day
--------------------------------------------------------------------------------

FUNCTION xxotl_hours_day (p_assignment_id IN number, p_date IN date)

RETURN number;

--------------------------------------------------------------------------------
-- Get Month Days
--------------------------------------------------------------------------------

FUNCTION get_month_days (p_assignment_id IN NUMBER, p_date IN DATE)

RETURN number;

FUNCTION GET_TICKET_ALLOWANCE (p_business_group_id NUMBER,

p_assignment_id NUMBER,
p_start_date DATE,
p_end_date DATE,
p_accrued_ticket_a OUT NUMBER,
p_accrued_ticket_c OUT NUMBER,
p_accrued_ticket_a_dep OUT NUMBER)
RETURN NUMBER;

FUNCTION get_vacation_pay (p_employee_number IN NUMBER,

p_start_date IN DATE,
p_end_date IN DATE,
p_monthly_value OUT number)
RETURN number;

FUNCTION get_los_sal_chg ( -- p_business_group_id IN number,

p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN date,
p_length_of_service OUT number,
p_basic_salary OUT number)
RETURN number;

FUNCTION get_flag_adj_grat_accruals (p_assignment_id IN number,

p_period_start_date IN date,
p_period_end_date IN date,
p_amount IN varchar2)
RETURN varchar2;

FUNCTION get_month_unpaid_days (p_assignment_id IN NUMBER,

p_start_date IN DATE,
p_end_date IN date)
RETURN number;

FUNCTION get_grat_accrual_adj ( --p_business_group_id IN number,

p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN date)
RETURN number;

FUNCTION get_final_stlmt_adj_amt (p_assignment_id IN number)

RETURN NUMBER;

-- ----------------------------------------------------------------------------
-- |-------------------------< calculate_food_all >-------------------------|
-- ----------------------------------------------------------------------------

FUNCTION calculate_food_all (p_assignment_id IN NUMBER,

p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_month_value OUT NUMBER)
RETURN NUMBER;

FUNCTION get_all_unpaid_days (p_assignment_id IN NUMBER,

p_effective_date IN date)
RETURN NUMBER;

FUNCTION get_Staff_Work_Location(p_assignment_id IN NUMBER)

RETURN VARCHAR2;

FUNCTION CALCULATE_ADMIN_SALARY (p_assignment_id IN NUMBER,

p_element_entry_id IN Number,
p_start_date IN DATE,
p_end_date IN DATE
-- p_month_value OUT NUMBER
--- p_week_holi_pay OUT NUMBER,
--p_maternity_pay OUT NUMBER,
---p_maternity_days OUT NUMBER,
--- p_sick_pay OUT NUMBER,
--- p_sick_ded OUT NUMBER,
--- p_sick_days OUT NUMBER
)
RETURN NUMBER;

FUNCTION CALCULATE_OVERTIME (p_assignment_id IN NUMBER,

P_ot_hours IN NUMBER,
p_ot_date IN DATE
)
RETURN NUMBER;
FUNCTION GET_OTL_REGULAR_HOURS_YARD (
p_assignment_id NUMBER,
p_effective_date DATE
)
RETURN NUMBER;

FUNCTION CALCULATE_SALARY_YARD (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
--- p_month_value OUT NUMBER

)
RETURN NUMBER;

FUNCTION LEL_CALCULATE_GRATUITY (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
)
RETURN NUMBER;
FUNCTION CALCULATE_SICK_LEAVE_SALARY (
p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_sick_pay OUT NUMBER,
p_sick_ded OUT NUMBER,
p_sick_days OUT NUMBER
)
RETURN NUMBER;

FUNCTION CALCULATE_LEAVE_SALARY(
p_assignment_id IN NUMBER,
p_leave_name IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_leave_days OUT NUMBER
)
RETURN NUMBER;
FUNCTION CALCULATE_OVERTIME_HOLIDAY (p_assignment_id IN NUMBER,
p_ot_hours IN NUMBER,
p_ot_date IN DATE
)
RETURN NUMBER;
FUNCTION GET_NET_ENTITLEMENT_ANNUAL(
P_ASSG_ID IN NUMBER,
P_PAYROLL_ID IN NUMBER,
P_CALCULATION_DATE IN DATE
)
RETURN NUMBER;
FUNCTION LEL_CALCULATE_LEAVE_ENCASHMENT (
p_assignment_id IN NUMBER,
p_end_date IN DATE,
p_start_date IN DATE,
p_net_entitle IN NUMBER
)
RETURN NUMBER;
FUNCTION GET_ASSG_DFF_VAL (
P_ELEMENT_ENTRY_ID IN NUMBER,
P_ASSG_ID IN NUMBER,
P_END_DATE IN DATE
)
RETURN VARCHAR;
FUNCTION GET_DAY_STATUS (
p_assignment_id IN NUMBER,
p_plan_date IN DATE
)
RETURN VARCHAR2;
FUNCTION get_total_off_days (
p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
)
RETURN NUMBER;

FUNCTION GET_ANNUAL_ACCURAL_AMT (l_assignment_id IN NUMBER,

l_start_date IN DATE,
l_end_date IN DATE
)
RETURN NUMBER;

FUNCTION get_emp_work_plan (
p_assignment_id IN NUMBER,
p_plan_date IN DATE
)
RETURN NUMBER;
FUNCTION GET_OT_VALUE (P_ASSG_ID IN NUMBER,
P_END_DATE IN DATE
)
RETURN VARCHAR;

FUNCTION GET_TICKET_FLAG ( p_assg_id in NUMBER,

p_date_start in DATE,
p_date_end in DATE

)
RETURN VARCHAR2;
FUNCTION get_ticket_accrual_payment (
p_assignment_id NUMBER,
p_type VARCHAR2,
p_start_date DATE,
p_end_date DATE
)
RETURN NUMBER ;

FUNCTION CAL_ADV_LEAVE_SALARY(
p_assignment_id IN NUMBER,
p_leave_name IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_leave_days OUT NUMBER
)
RETURN NUMBER;
FUNCTION calculate_food_allowance (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_food_all IN NUMBER
)
RETURN NUMBER;
FUNCTION GET_OT_CAP(
p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
)
RETURN NUMBER;
FUNCTION calculate_sra_monthly_value (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
)
RETURN NUMBER;
FUNCTION GET_FOOD_ALLOANCE_DAYS ( p_assg_id in NUMBER,
p_date_start in DATE,
p_date_end in DATE

)
RETURN Number;
FUNCTION GET_PAYSLIP_MISC_REMARK(
p_element_type_id IN NUMBER,
p_ELEMENT_ENTRY_ID IN NUMBER,
p_assignment_action_id IN NUMBER
)
RETURN CHAR;
FUNCTION GET_HSBC_PAYMENT_CODE ( p_assignment_id IN NUMBER,
P_PER_PAY_ID IN NUMBER
)
RETURN CHAR;
FUNCTION GET_CURRENCY_EXCHANGE_RATE ( P_CURRENCY IN CHAR,
P_DATE IN DATE)
RETURN NUMBER;

FUNCTION calculate_food_allo_OM_POOL (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_food_all IN NUMBER
)
RETURN NUMBER;

END XXLAM_HR_PAYROLL_ALL;
/

===================================================================================
==

Body

===================================================================================
==
CREATE OR REPLACE PACKAGE BODY APPS.xxlam_hr_payroll_all
AS
v_business_group_id number := hr_general.get_business_group_id();
--- v_business_group_id NUMBER := 81;

-- ----------------------------------------------------------------------------
-- |----------------------------< get_action_id >----------------------------|
-- ----------------------------------------------------------------------------
FUNCTION get_action_id (p_assignment_id IN NUMBER, p_effective_date IN DATE)
RETURN NUMBER
IS
l_action_id NUMBER;
BEGIN
SELECT NVL (MAX (assignment_action_id), 0)
INTO l_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
per_all_assignments_f ass
WHERE ass.assignment_id = p_assignment_id
AND p_effective_date BETWEEN ass.effective_start_date
AND ass.effective_end_date
AND ptp.time_period_id = ppa.time_period_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND ppa.payroll_id = ass.payroll_id
AND ass.primary_flag = 'Y'
AND action_type IN ('R', 'Q')
--and ptp.end_date <= p_effective_date -- commented this line as the
system is picking the balance based on the previous month payroll periods if quick
pay is proceed in b/w
AND ppa.date_earned <=
p_effective_date
-- added this line to take the latest assignment action id based on date_erned
;

-- AND p_effective_date BETWEEN ptp.start_date AND ptp.end_date;

/*;and*/
RETURN l_action_id;
END get_action_id;

-- ----------------------------------------------------------------------------
-- |-------------------------< get_element_entry_id >--------------------------|
-- ----------------------------------------------------------------------------
FUNCTION get_element_entry_id (
p_assignment_id IN NUMBER,
p_element_name IN VARCHAR2,
p_effective_date IN DATE
)
RETURN NUMBER
AS
l_element_entry_id NUMBER;
BEGIN
SELECT element_entry_id
INTO l_element_entry_id
FROM pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
WHERE 1 = 1
AND p_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND p_effective_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pee.element_link_id = pel.element_link_id
AND pet.element_type_id = pee.element_type_id
AND pet.element_name = p_element_name
AND pee.assignment_id = p_assignment_id;

RETURN l_element_entry_id;
EXCEPTION
WHEN OTHERS
THEN
l_element_entry_id := 0;
RETURN l_element_entry_id;
END;

----
FUNCTION get_basic_at (p_assignment_id NUMBER, p_date DATE)
RETURN NUMBER
AS
l_basic_salary NUMBER := 0;
l_gross NUMBER := 0;
l_value NUMBER := 0;
l_person_id NUMBER := 0;
l_person_type VARCHAR2 (100) := NULL;
BEGIN
SELECT MAX (NVL (fnd_number.canonical_to_number (pev.screen_entry_value),
0
)
)
INTO l_gross
FROM pay_element_types_f_vl pet,
pay_input_values_f piv,
pay_element_entry_values_f pev,
pay_element_entries_f peef
WHERE 1 = 1
AND pet.element_type_id = piv.element_type_id
AND UPPER (pet.element_name) = UPPER ('Gross Salary')
AND UPPER (piv.NAME) = UPPER ('Monthly Value')
AND piv.input_value_id = pev.input_value_id
AND peef.element_entry_id = pev.element_entry_id
AND peef.assignment_id = p_assignment_id
AND p_date BETWEEN pev.effective_start_date AND pev.effective_end_date
AND p_date BETWEEN peef.effective_start_date AND peef.effective_end_date
AND p_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND p_date BETWEEN pet.effective_start_date AND pet.effective_end_date;

SELECT MAX (NVL (fnd_number.canonical_to_number (pev.screen_entry_value),

0
)
)
INTO l_basic_salary
FROM pay_element_types_f_vl pet,
pay_input_values_f piv,
pay_element_entry_values_f pev,
pay_element_entries_f peef
WHERE 1 = 1
AND pet.element_type_id = piv.element_type_id
AND UPPER (pet.element_name) = UPPER ('Basic Salary')
AND UPPER (piv.NAME) = UPPER ('Monthly Value')
AND piv.input_value_id = pev.input_value_id
AND peef.element_entry_id = pev.element_entry_id
AND peef.assignment_id = p_assignment_id
AND p_date BETWEEN pev.effective_start_date AND pev.effective_end_date
AND p_date BETWEEN peef.effective_start_date AND peef.effective_end_date
AND p_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND p_date BETWEEN pet.effective_start_date AND pet.effective_end_date;

IF NVL (l_basic_salary, 0) <> 0

THEN
l_value := l_basic_salary;
ELSE
BEGIN
SELECT person_id
INTO l_person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_date BETWEEN effective_start_date AND effective_end_date;

-- l_person_type :=
XXLAM_HR_UTILITIES_PKG.GET_PERSON_TYPE('NAME',p_assignment_id ,p_date );
l_person_type :=
xxlam_hr_utilities_pkg.get_person_type ('NAME',
l_person_id,
p_date
);
EXCEPTION
WHEN OTHERS
THEN
l_person_type := 'Worker';
END;

IF UPPER (l_person_type) = 'STAFF'

THEN
l_value := (l_gross * 0.6);
ELSIF UPPER (l_person_type) = 'WORKER'
THEN
l_value := l_gross;
ELSE
l_value := l_gross;
END IF;
END IF;

RETURN l_value;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END get_basic_at;

--
-- ----------------------------------------------------------------------------
-- |----------------------------< get_balance >-------------------------------|
-- ----------------------------------------------------------------------------
--
FUNCTION get_balance (
p_assignment_id IN NUMBER,
p_item_name IN VARCHAR2,
p_effective_date IN DATE DEFAULT SYSDATE
)
RETURN NUMBER
IS
CURSOR csr_defined_balance
IS
SELECT TO_NUMBER (ue.creator_id)
FROM ff_database_items di, ff_user_entities ue
WHERE di.user_name = p_item_name
AND ue.user_entity_id = di.user_entity_id
AND ue.creator_type = 'B'
AND ue.business_group_id = v_business_group_id;

--
l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
g_message_text VARCHAR2 (240); -- make global
l_balance NUMBER;
l_balance_char VARCHAR2 (12);
l_effective_date DATE;
--
BEGIN
--
OPEN csr_defined_balance;

FETCH csr_defined_balance
INTO l_defined_balance_id;

IF csr_defined_balance%NOTFOUND
THEN
CLOSE csr_defined_balance;

g_message_text := 'Balance DB item does not exist';

RAISE hr_utility.hr_error;
ELSE
CLOSE csr_defined_balance;
END IF;

--
SELECT NVL (p_effective_date, effective_date)
INTO l_effective_date
FROM fnd_sessions
WHERE session_id = USERENV ('sessionid');

--
BEGIN
l_balance :=
pay_balance_pkg.get_value (l_defined_balance_id,
p_assignment_id,
l_effective_date
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_balance := 0;
END;
RETURN l_balance;
END get_balance;

FUNCTION get_balance (
p_assignment_id IN NUMBER,
p_bal_name IN VARCHAR2,
p_bal_dim IN VARCHAR2,
p_effective_date IN DATE
)
RETURN NUMBER
IS
l_defined_balance_id NUMBER := 0;
balance_id NUMBER := 0;
balance_value NUMBER := 0;
p_action_id NUMBER;
l_business_group_id NUMBER := 0;
l_legislation_code VARCHAR2 (10);

CURSOR dimension_relevant (
c_balance_type_id IN NUMBER,
c_db_item_suffix IN VARCHAR2
)
IS
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb, pay_balance_dimensions pbd
WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbd.database_item_suffix = c_db_item_suffix
AND pdb.balance_type_id = c_balance_type_id;
BEGIN
l_business_group_id := hr_general.get_business_group_id ();

-- l_business_group_id := 81;
/*

SELECT
balance_type_id
INTO
balance_id
FROM
pay_balance_types
WHERE
balance_name = p_bal_name;
*/
BEGIN
SELECT legislation_code
INTO l_legislation_code
FROM per_business_groups
WHERE 1 = 1 AND business_group_id = l_business_group_id;
EXCEPTION
WHEN OTHERS
THEN
l_legislation_code := NULL;
END;

BEGIN
SELECT balance_type_id
INTO balance_id
FROM pay_balance_types
WHERE 1 = 1
AND balance_name = p_bal_name
AND NVL (business_group_id, l_business_group_id) =
l_business_group_id
AND NVL (legislation_code, l_legislation_code) =
l_legislation_code;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

p_action_id := get_action_id (p_assignment_id, p_effective_date);

IF p_action_id > 0
THEN
IF p_bal_dim = 'ITD'
THEN
balance_value :=
hr_gbbal.calc_asg_itd_action (p_action_id, balance_id);
ELSIF p_bal_dim = 'YTD'
THEN
balance_value :=
hr_gbbal.calc_asg_ytd_action (p_action_id, balance_id);
ELSIF p_bal_dim = 'RUN'
THEN
balance_value :=
hr_gbbal.calc_asg_run_action (p_action_id, balance_id);
ELSIF p_bal_dim = 'PTD'
THEN
OPEN dimension_relevant (balance_id, '_ASG_PTD');

FETCH dimension_relevant
INTO l_defined_balance_id;

CLOSE dimension_relevant;

IF l_defined_balance_id IS NOT NULL

THEN
balance_value :=
pay_balance_pkg.get_value (l_defined_balance_id,
p_action_id
);
END IF;
END IF;
END IF;

RETURN balance_value;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END get_balance;

--
-- ----------------------------------------------------------------------------
-- |----------------------------< get_balance - Overriding function >---------|
-- ----------------------------------------------------------------------------
--
FUNCTION get_balance (
p_assignment_id IN NUMBER,
p_business_group_id IN NUMBER,
p_bal_name IN VARCHAR2,
p_bal_dim IN VARCHAR2,
p_effective_date IN DATE
)
RETURN NUMBER
IS
l_defined_balance_id NUMBER := 0;
balance_id NUMBER := 0;
balance_value NUMBER := 0;
p_action_id NUMBER;
l_legislation_code VARCHAR2 (20);

CURSOR dimension_relevant (
c_balance_type_id IN NUMBER,
c_db_item_suffix IN VARCHAR2
)
IS
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb, pay_balance_dimensions pbd
WHERE 1 = 1
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbd.database_item_suffix = c_db_item_suffix
AND pdb.balance_type_id = c_balance_type_id;
BEGIN
BEGIN
SELECT legislation_code
INTO l_legislation_code
FROM per_business_groups
WHERE 1 = 1 AND business_group_id = p_business_group_id;
EXCEPTION
WHEN OTHERS
THEN
l_legislation_code := NULL;
END;

BEGIN
SELECT balance_type_id
INTObalance_id
FROMpay_balance_types
WHERE 1 = 1
ANDbalance_name = p_bal_name
ANDNVL (business_group_id, p_business_group_id) =
p_business_group_id
AND NVL (legislation_code, l_legislation_code) =
l_legislation_code;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

p_action_id := get_action_id (p_assignment_id, p_effective_date);

FETCH dimension_relevant
INTO l_defined_balance_id;

CLOSE dimension_relevant;

IF l_defined_balance_id IS NOT NULL

THEN
balance_value :=
pay_balance_pkg.get_value (l_defined_balance_id,
p_action_id
);
END IF;
ELSE
OPEN dimension_relevant (balance_id, p_bal_dim);

FETCH dimension_relevant
INTO l_defined_balance_id;

CLOSE dimension_relevant;

IF l_defined_balance_id IS NOT NULL

THEN
balance_value :=
pay_balance_pkg.get_value (l_defined_balance_id,
p_action_id
);
END IF;
END IF;
END IF;

RETURN balance_value;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END get_balance;

-- ----------------------------------------------------------------------------
-- |----------------------------< get_salary_at>------------------------------|
-- ----------------------------------------------------------------------------
--
FUNCTION get_gross_at (p_assignment_id NUMBER, p_date DATE)
RETURN NUMBER
AS
l_salary NUMBER;
BEGIN
SELECT MAX (NVL (fnd_number.canonical_to_number (pev.screen_entry_value),
0
)
)
INTO l_salary
FROM pay_element_types_f_vl pet,
pay_input_values_f piv,
pay_element_entry_values_f pev,
pay_element_entries_f peef
WHERE 1 = 1
AND pet.element_type_id = piv.element_type_id
AND UPPER (pet.element_name) = UPPER ('Gross Salary')
AND UPPER (piv.NAME) IN ('Monthly Value', 'PAY VALUE')
AND piv.input_value_id = pev.input_value_id
AND peef.element_entry_id = pev.element_entry_id
AND peef.assignment_id = p_assignment_id
AND p_date BETWEEN pev.effective_start_date AND pev.effective_end_date
AND p_date BETWEEN peef.effective_start_date AND peef.effective_end_date
AND p_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND p_date BETWEEN pet.effective_start_date AND pet.effective_end_date;

-- SELECT
-- ppp.proposed_salary_n
-- INTO
-- l_salary
-- FROM
-- per_pay_proposals ppp
-- WHERE
-- ppp.assignment_id = p_assignment_id
-- AND ppp.approved = 'Y'
-- AND change_date =
-- (SELECT
-- MAX (change_date)
-- FROM
-- per_pay_proposals ppp
-- WHERE
-- ppp.assignment_id = p_assignment_id
-- AND ppp.approved = 'Y'

-- AND ppp.change_date <= p_date

-- );
RETURN l_salary;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;

-- ----------------------------------------------------------------------------
-- |-------------------------< get_screen_entry_value_at >---------------------|
-- ----------------------------------------------------------------------------
FUNCTION get_screen_entry_value_at (
p_assignment_id NUMBER,
p_element_entry_id NUMBER,
p_element_input_name VARCHAR2,
p_effective_date DATE
)
RETURN NUMBER
AS
l_value NUMBER;
BEGIN
SELECT NVL (fnd_number.canonical_to_number (pev.screen_entry_value), 0)
INTO l_value
FROM pay_element_types_f_vl pet,
pay_input_values_f piv,
pay_element_entry_values_f pev,
pay_element_entries_f peef
WHERE 1 = 1
-- AND Upper(pet.element_name) = Upper(p_element_name)
AND pet.element_type_id = piv.element_type_id
AND UPPER (piv.NAME) = UPPER (p_element_input_name)
AND piv.input_value_id = pev.input_value_id
AND peef.element_entry_id = pev.element_entry_id
AND peef.element_entry_id = p_element_entry_id
AND p_effective_date BETWEEN pev.effective_start_date
AND pev.effective_end_date
AND p_effective_date BETWEEN peef.effective_start_date
AND peef.effective_end_date
AND p_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;

RETURN l_value;
EXCEPTION
WHEN OTHERS
THEN
l_value := 0;
RETURN l_value;
END;

-- ----------------------------------------------------------------------------
-- |-------------------------< get_otl_regular_hours >-------------------------|
-- ----------------------------------------------------------------------------
FUNCTION get_otl_regular_hours (
p_assignment_id NUMBER,
p_effective_date DATE
)
RETURN NUMBER
IS
l_value NUMBER := 0;
v_week_off NUMBER := 0;
v_holiday NUMBER := 0;
v_person_type VARCHAR2 (30);
-----Add the below Variables
v_element_entry_id NUMBER := 0;
v_date DATE;
v_skip_y_n VARCHAR2 (100) := 'N';
v_term_person_type VARCHAR2 (100) := NULL;
BEGIN
v_person_type :=
get_person_type_ass (p_assignment_id, p_effective_date);

BEGIN
SELECT TO_DATE ( 20
|| '-'
|| TO_CHAR (p_effective_date, 'MON')
|| '-'
|| TO_CHAR (p_effective_date, 'RRRR')
)
INTO v_date
FROM DUAL;
END;

-- added the below if condition to handle the cross months payroll end date
startes here
IF p_effective_date > v_date
THEN
v_date := ADD_MONTHS (v_date, 1);
END IF;

-- added the below if condition to handle the cross months payroll end date
ends here
v_term_person_type :=
get_person_type_ass (p_assignment_id, p_effective_date);
DBMS_OUTPUT.put_line ( 'Regular hours v_term_person_type = '
|| v_term_person_type
);

----Added the below code to getting the payroll period end_date for staff for
that employee ends here
IF v_person_type = 'Worker'
THEN
--SELECT
nvl(sum(fnd_number.canonical_to_number(pev.screen_entry_value)),0)
--INTO l_value
SELECT NVL
(SUM
(fnd_number.canonical_to_number (pev.screen_entry_value)
),
0
),
MAX (peef.element_entry_id)
INTO l_value,
v_element_entry_id
FROM pay_element_types_f_vl pet,
pay_input_values_f piv,
pay_element_entry_values_f pev,
pay_element_entries_f peef
WHERE 1 = 1
AND peef.assignment_id = p_assignment_id
AND piv.NAME = 'Hours'
AND pet.element_name IN ('OTL Regular Hours', 'OTL Holiday Pay')
AND piv.element_type_id = pet.element_type_id
AND peef.element_type_id = pet.element_type_id
AND piv.input_value_id = pev.input_value_id
AND peef.element_entry_id = pev.element_entry_id
AND peef.date_earned = p_effective_date;
ELSE
--SELECT
nvl(sum(fnd_number.canonical_to_number(pev.screen_entry_value)),0)
--INTO l_value
SELECT NVL
(SUM
(fnd_number.canonical_to_number (pev.screen_entry_value)
),
0
),
MAX (peef.element_entry_id)
INTO l_value,
v_element_entry_id
FROM pay_element_types_f_vl pet,
pay_input_values_f piv,
pay_element_entry_values_f pev,
pay_element_entries_f peef
WHERE 1 = 1
AND peef.assignment_id = p_assignment_id
AND piv.NAME = 'Hours'
AND pet.element_name IN ('OTL Regular Hours')
AND piv.element_type_id = pet.element_type_id
AND peef.element_type_id = pet.element_type_id
AND piv.input_value_id = pev.input_value_id
AND peef.element_entry_id = pev.element_entry_id
AND peef.date_earned = p_effective_date;
END IF;

--------Added the below code Starts here

IF v_term_person_type IN ('Worker', 'Staff')
THEN
BEGIN
v_skip_y_n :=
xxpay_skip_reg_hours_y_n (v_element_entry_id, v_date);
END;
ELSE
BEGIN
v_skip_y_n :=
xxpay_skip_reg_hours_y_n (v_element_entry_id,
p_effective_date);
END;
END IF;

--------Added the below code ENDSs here

BEGIN
v_week_off :=
xxotl_get_week_off_pay (p_assignment_id,
p_effective_date,
p_effective_date
);
END;

v_person_type := get_person_type_ass (p_assignment_id, p_effective_date);

BEGIN
v_holiday :=
xxlam_hr_absence_duration.holidays (p_assignment_id,
p_effective_date,
p_effective_date
);
END;

IF v_person_type = 'Worker'
THEN
IF (v_week_off <> 0 OR v_holiday <> 0)
THEN
l_value := 0;
END IF;
--------Added the below code Starts here
ELSIF v_person_type = 'Staff'
THEN
IF v_skip_y_n = 'Y'
THEN
l_value := 0;
END IF;
--------Added the below code ENDs here
END IF;

DBMS_OUTPUT.put_line ('Regular hours v_skip_y_n = ' || v_skip_y_n);

DBMS_OUTPUT.put_line (l_value);
RETURN l_value;
EXCEPTION
WHEN OTHERS
THEN
l_value := 0;
RETURN l_value;
END;

/*FUNCTION get_otl_regular_hours (p_assignment_id Number

,p_effective_date DATE)
RETURN NUMBER IS
l_value Number :=0;
v_week_off number := 0;
v_holiday Number := 0;
v_person_type varchar2(30);
BEGIN
v_person_type := get_person_type_ass(p_assignment_id,p_effective_date);
if v_person_type ='Worker' Then
SELECT nvl(sum(fnd_number.canonical_to_number(pev.screen_entry_value)),0)
INTO l_value
FROM
pay_element_types_f_vl pet,
pay_input_values_f piv,
pay_element_entry_values_f pev,
pay_element_entries_f peef
WHERE 1 = 1
AND peef.assignment_id = p_assignment_id
and piv.name = 'Hours'
and pet.element_name IN ('OTL Regular Hours','OTL Holiday Pay')
and piv.element_type_id = pet.element_type_id
and peef.element_type_id = pet.element_type_id
and piv.input_value_id = pev.input_value_id
and peef.element_entry_id = pev.element_entry_id
and peef.date_earned = p_effective_date;
Else
SELECT nvl(sum(fnd_number.canonical_to_number(pev.screen_entry_value)),0)
INTO l_value
FROM
pay_element_types_f_vl pet,
pay_input_values_f piv,
pay_element_entry_values_f pev,
pay_element_entries_f peef
WHERE 1 = 1
AND peef.assignment_id = p_assignment_id
and piv.name = 'Hours'
and pet.element_name IN ('OTL Regular Hours')
and piv.element_type_id = pet.element_type_id
and peef.element_type_id = pet.element_type_id
and piv.input_value_id = pev.input_value_id
and peef.element_entry_id = pev.element_entry_id
and peef.date_earned = p_effective_date;
End if;
Begin
v_week_off := xxotl_get_week_off_pay(p_assignment_id, p_effective_date,
p_effective_date);
End;
v_person_type := get_person_type_ass(p_assignment_id,p_effective_date);
Begin
v_holiday :=
XXLAM_HR_ABSENCE_DURATION.holidays(p_assignment_id,p_effective_date,p_effective_dat
e);
end;
if v_person_type ='Worker' Then
if (v_week_off <> 0 or v_holiday <> 0) then
l_value := 0;
End if;
End if;
dbms_output.put_line(l_value);
RETURN l_value;
EXCEPTION
WHEN OTHERS THEN
l_value := 0;
return l_value;
END;
*/

-- ----------------------------------------------------------------------------
-- |-------------------------< calculate_Basic_Salary >------------------------|
-- ----------------------------------------------------------------------------
FUNCTION calculate_basic_salary (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_month_value OUT NUMBER,
p_week_holi_pay OUT NUMBER,
p_maternity_pay OUT NUMBER,
p_maternity_days OUT NUMBER,
p_sick_pay OUT NUMBER,
p_sick_ded OUT NUMBER,
p_sick_days OUT NUMBER
)
RETURN NUMBER
IS
v_salary NUMBER := 0;
v_gross NUMBER := 0;
v_basic NUMBER := 0;
v_days NUMBER := (p_end_date - p_start_date);
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_hrs_per_day NUMBER := 0;
v_otl_hrs NUMBER (5, 2) := 0;
v_curr_otl_hrs NUMBER (5, 2) := 0;
v_week_hrs NUMBER := 0;
v_curr_week_hrs NUMBER := 0;
v_holiday NUMBER := 0;
v_week_pay NUMBER := 0;
v_holi_pay NUMBER := 0;
v_curr_holiday NUMBER := 0;
v_curr_maternity NUMBER := 0;
v_curr_maternity_pay NUMBER := 0;
v_curr_sick NUMBER := 0;
v_curr_sick_pay NUMBER := 0;
v_curr_sick_ded NUMBER := 0;
v_total_sick_days NUMBER := 0;
v_business_group_id NUMBER := 0;
l_period_service NUMBER := 0;
v_person_type VARCHAR2 (100);
v_skip_holiday NUMBER := 0;
v_employee_number VARCHAR2 (250);
v_curr_sick_pay_p NUMBER;
v_curr_sick_ded_p NUMBER;
BEGIN
v_basic := 0;
v_mth_days := get_month_days (p_assignment_id, p_start_date);
v_person_type := get_person_type_ass (p_assignment_id, p_end_date);
DBMS_OUTPUT.put_line ('v_mth_days:' || v_mth_days);
DBMS_OUTPUT.put_line ('v_person_type:' || v_person_type);
fnd_file.put_line (fnd_file.LOG, 'v_mth_days:' || v_mth_days);
fnd_file.put_line (fnd_file.LOG, 'v_person_type:' || v_person_type);
p_month_value := 0;
p_week_holi_pay := 0;
p_month_value := 0;
p_maternity_pay := 0;
p_maternity_days := 0;
p_sick_pay := 0;
p_sick_ded := 0;
p_sick_days := 0;
v_business_group_id := hr_general.get_business_group_id ();

--v_business_group_id := 81;
FOR i IN 0 .. v_days
LOOP
v_date := p_start_date + i;
v_curr_otl_hrs := 0;
v_curr_week_hrs := 0;
v_curr_holiday := 0;
v_curr_maternity_pay := 0;
v_curr_sick_pay := 0;
v_curr_sick_ded := 0;
v_curr_sick_pay_p := 0;
v_curr_sick_ded_p := 0;

BEGIN
v_mth_value :=
get_screen_entry_value_at (p_assignment_id,
p_element_entry_id,
'Monthly Value',
v_date
);
END;
BEGIN
v_gross := get_gross_at (p_assignment_id, v_date);
END;

IF v_mth_value = 0
THEN
IF v_person_type = 'Worker'
THEN
p_month_value := v_gross;
ELSE
p_month_value := ROUND ((v_gross * .6), 2);
END IF;

IF p_month_value > 10000

THEN
p_month_value := 10000;
END IF;
ELSE
p_month_value := v_mth_value;
END IF;

BEGIN
v_hrs_per_day := xxotl_hours_day (p_assignment_id, v_date);
END;

BEGIN
v_curr_otl_hrs := get_otl_regular_hours (p_assignment_id, v_date);
END;

v_otl_hrs := v_otl_hrs + v_curr_otl_hrs;

-- v_sal_per_day :=round( v_sal_per_day + (p_month_value / v_mth_days/
v_hrs_per_day) * v_curr_otl_hrs,2);
v_sal_per_day :=
v_sal_per_day
+ (p_month_value / v_mth_days / v_hrs_per_day) * v_curr_otl_hrs;
DBMS_OUTPUT.put_line ( 'v_sal_per_day:......'
|| v_sal_per_day
|| '~'
|| 'Date:....'
|| v_date
);

BEGIN
v_curr_week_hrs :=
xxotl_get_week_off_pay (p_assignment_id, v_date, v_date);
END;

IF v_curr_week_hrs <> 0
THEN
v_week_hrs := v_week_hrs + v_curr_week_hrs;
v_week_pay :=
v_week_pay
+ (p_month_value / v_mth_days) * v_curr_week_hrs;
END IF;

DBMS_OUTPUT.put_line ('Week Pay:' || v_week_pay || 'Date:' || v_date);

-- for unpaid and vacation with Advance Salary

BEGIN
SELECT employee_number
INTOv_employee_number
FROMper_all_people_f papf, per_all_assignments_f paaf
WHERE papf.person_id = paaf.person_id
ANDpaaf.assignment_id = p_assignment_id
ANDv_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND v_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.primary_flag = 'Y'
AND papf.current_employee_flag = 'Y';

v_skip_holiday :=
xxlam_hr_absence_duration.get_holiday_unpaid_leaves
(v_employee_number,
v_business_group_id,
v_date
);
EXCEPTION
WHEN OTHERS
THEN
v_skip_holiday := 0;
END;

IF v_skip_holiday <> 1
THEN
BEGIN
v_curr_holiday :=
xxlam_hr_absence_duration.holidays (p_assignment_id,
v_date,
v_date
);
END;

IF v_curr_holiday <> 0
THEN
v_holiday := v_holiday + v_curr_holiday;
v_holi_pay :=
v_holi_pay
+ (p_month_value / v_mth_days) * v_curr_holiday;
END IF;
END IF;

DBMS_OUTPUT.put_line ('Holiday Pay:' || v_holi_pay || 'Date:'

|| v_date
);
DBMS_OUTPUT.put_line ( v_sal_per_day
|| 'Week:'
|| v_week_hrs
|| ' OTL Hours:'
|| v_otl_hrs
|| ' Holiday:'
|| v_holiday
);
/************************ Maternity Leave Calculation Starts
****************/
l_period_service :=
xxlam_hr_absence_duration.get_period_of_service (p_assignment_id,
v_date
);
v_curr_maternity :=
xxlam_hr_absence_duration.get_no_of_leaves_taken_ass_id
(v_business_group_id,
p_assignment_id,
'Maternity Leave',
v_date,
v_date
);

IF v_curr_maternity > 0
THEN
IF l_period_service < 365
THEN
v_curr_maternity_pay :=
v_curr_maternity * (v_gross / v_mth_days)
* .5;
ELSE
v_curr_maternity_pay :=
v_curr_maternity
* (v_gross / v_mth_days);
END IF;
END IF;

p_maternity_pay := p_maternity_pay + v_curr_maternity_pay;

p_maternity_days := p_maternity_days + v_curr_maternity;
DBMS_OUTPUT.put_line ( 'maternity days:'
|| p_maternity_days
|| ' maternity pay:'
|| p_maternity_pay
|| ' Service Period:'
|| l_period_service
|| ' Date:'
|| v_date
);
/************************ Maternity Leave Calculation Ends
****************/

/******** Sick Leave Calculation Starts /

v_total_sick_days :=
xxlam_hr_absence_duration.get_sick_leave_taken (p_assignment_id,
v_date
)
+ xxlam_hr_payroll_all.get_balance (p_assignment_id,
v_business_group_id,
'Initial_Sick_Leave_Days',
'ITD',
v_date
);
v_curr_sick :=
xxlam_hr_absence_duration.get_no_of_leaves_taken_ass_id
(v_business_group_id,
p_assignment_id,
'Sick Leave',
v_date,
v_date
);
IF v_curr_sick = 0
THEN
-- v_curr_sick :=
XXLAM_HR_ABSENCE_DURATION.get_parttime_absence (p_assignment_id,'Parttime Sick
Leave',v_date)/v_hrs_per_day;
v_curr_sick :=
xxlam_hr_absence_duration.get_parttime_absence
(p_assignment_id,
'Parttime Sick Leave',
v_date
);
END IF;

--v_curr_sick := v_curr_sick + v_curr_sick_pay_P;

IF v_curr_sick > 0
THEN
IF v_total_sick_days <= 15
THEN
v_curr_sick_pay := v_curr_sick * (v_gross / v_mth_days);
DBMS_OUTPUT.put_line ('Less than 15 days: Gross' || v_gross);
ELSE
IF v_total_sick_days > 15 AND v_total_sick_days <= 45
THEN
v_curr_sick_pay := v_curr_sick * (v_gross / v_mth_days);
v_curr_sick_ded := v_curr_sick * (v_gross / v_mth_days)
* .5;
DBMS_OUTPUT.put_line
( 'between than 16 and 45 days: Gross'
|| v_gross
);
ELSIF v_total_sick_days > 45 AND v_total_sick_days <= 90
THEN
v_curr_sick_pay := v_curr_sick * (v_gross / v_mth_days);
v_curr_sick_ded := v_curr_sick * (v_gross / v_mth_days);
DBMS_OUTPUT.put_line
( 'between than 49 and 90 days: Gross'
|| v_gross
);
END IF;
END IF;
END IF;

p_sick_pay := p_sick_pay + v_curr_sick_pay;

p_sick_ded := p_sick_ded + v_curr_sick_ded;
p_sick_days := p_sick_days + v_curr_sick;
DBMS_OUTPUT.put_line ( 'Curr Sick:'
|| v_curr_sick
|| 'sick days:'
|| p_sick_days
|| ' sick pay:'
|| p_sick_pay
|| ' sick ded:'
|| p_sick_ded
|| ' Total Sick:'
|| v_total_sick_days
|| ' Date:'
|| v_date
);
/************************ Maternity Leave Calculation Ends ****************/
END LOOP;

DBMS_OUTPUT.put_line ( 'Week off:'

|| v_week_pay
|| 'Holi:'
|| v_holi_pay
|| 'Reg:'
|| v_sal_per_day
);
-- p_week_holi_pay := Round ((v_week_pay + v_holi_pay)/.1,0)*.1;
p_week_holi_pay := ROUND (v_week_pay + v_holi_pay, 2);
---- v_sal_per_day := v_sal_per_day + p_week_holi_pay;
v_sal_per_day := NVL (v_sal_per_day, 0) + p_week_holi_pay;
DBMS_OUTPUT.put_line ('p_week_holi_pay = ' || p_week_holi_pay);
v_basic := ROUND (v_sal_per_day, 2);
/* Commented on 20-12-2009 based on requirements from users

p_sick_pay := round(p_sick_pay,2);
p_sick_ded := round(p_sick_ded,2);
*/
DBMS_OUTPUT.put_line ( 'V_basic~~'
|| v_basic
|| ' v_sal_per_day~~'
|| v_sal_per_day
|| '~~p_week_holi_pay'
|| p_week_holi_pay
);
p_sick_pay := ROUND (p_sick_pay, 2) - ROUND (p_sick_ded, 2);
p_sick_ded := 0;
p_maternity_pay := ROUND (p_maternity_pay, 2);
DBMS_OUTPUT.put_line ( v_days
|| v_mth_days
|| (v_week_hrs + v_holiday)
|| v_otl_hrs
);
RETURN v_basic;
END;

-- ----------------------------------------------------------------------------
-- |-------------------------< calculate_trans_all >---------------------------|
-- ----------------------------------------------------------------------------
FUNCTION calculate_trans_all (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_month_value OUT NUMBER
)
RETURN NUMBER
IS
v_salary NUMBER := 0;
v_gross NUMBER := 0;
v_value NUMBER := 0;
v_days NUMBER := (p_end_date - p_start_date);
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_hrs_per_day NUMBER := 0;
v_otl_hrs NUMBER := 0;
v_curr_otl_hrs NUMBER := 0;
v_week_hrs NUMBER := 0;
v_curr_week_hrs NUMBER := 0;
v_holiday NUMBER := 0;
v_curr_holiday NUMBER := 0;
v_week_holi_pay NUMBER := 0;
BEGIN
-- v_mth_days := to_number(to_char(last_day(p_start_date),'DD'));
v_mth_days := get_month_days (p_assignment_id, p_start_date);
p_month_value := 0;

FOR i IN 0 .. v_days
LOOP
v_date := p_start_date + i;
v_curr_otl_hrs := 0;
v_curr_week_hrs := 0;
v_curr_holiday := 0;

BEGIN
v_mth_value :=
get_screen_entry_value_at (p_assignment_id,
p_element_entry_id,
'Monthly Value',
v_date
);
END;

BEGIN
v_gross := get_gross_at (p_assignment_id, v_date);
END;

IF v_mth_value = 0
THEN
p_month_value := ROUND ((v_gross * .15), 2);
ELSE
p_month_value := v_mth_value;
END IF;

BEGIN
v_hrs_per_day := xxotl_hours_day (p_assignment_id, v_date);
END;

BEGIN
v_curr_otl_hrs := get_otl_regular_hours (p_assignment_id, v_date);
END;

v_otl_hrs := v_otl_hrs + v_curr_otl_hrs;

v_sal_per_day :=
v_sal_per_day
+ (p_month_value / v_mth_days / v_hrs_per_day) * v_curr_otl_hrs;

BEGIN
v_curr_week_hrs :=
xxotl_get_week_off_pay (p_assignment_id, v_date, v_date);
END;

IF v_curr_week_hrs <> 0
THEN
v_week_hrs := v_week_hrs + v_curr_week_hrs;
v_week_holi_pay :=
v_week_holi_pay
+ (p_month_value / v_mth_days) * v_curr_week_hrs;
END IF;

BEGIN
v_curr_holiday :=
xxlam_hr_absence_duration.holidays (p_assignment_id,
v_date,
v_date
);
END;

IF v_curr_holiday <> 0
THEN
v_holiday := v_holiday + v_curr_holiday;
v_week_holi_pay :=
v_week_holi_pay
+ (p_month_value / v_mth_days) * v_curr_holiday;
END IF;

DBMS_OUTPUT.put_line ( v_sal_per_day
|| 'Week:'
|| v_week_hrs
|| 'OTL:'
|| v_otl_hrs
|| 'Holiday:'
|| v_holiday
);
END LOOP;

v_sal_per_day := v_sal_per_day + v_week_holi_pay;

v_value := ROUND (v_sal_per_day, 2);
DBMS_OUTPUT.put_line ( v_days
|| v_mth_days
|| (v_week_hrs + v_holiday)
|| v_otl_hrs
);
RETURN v_value;
END;

-- ----------------------------------------------------------------------------
-- |-------------------------< calculate_housing_all >-------------------------|
-- ----------------------------------------------------------------------------
FUNCTION calculate_housing_all (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_month_value OUT NUMBER
)
RETURN NUMBER
IS
v_salary NUMBER := 0;
v_gross NUMBER := 0;
v_value NUMBER := 0;
v_days NUMBER := (p_end_date - p_start_date);
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_hrs_per_day NUMBER := 0;
v_otl_hrs NUMBER := 0;
v_curr_otl_hrs NUMBER := 0;
v_week_hrs NUMBER := 0;
v_curr_week_hrs NUMBER := 0;
v_holiday NUMBER := 0;
v_curr_holiday NUMBER := 0;
v_week_holi_pay NUMBER := 0;
BEGIN
v_mth_days := get_month_days (p_assignment_id, p_start_date);
p_month_value := 0;

FOR i IN 0 .. v_days
LOOP
v_date := p_start_date + i;
v_curr_otl_hrs := 0;
v_curr_week_hrs := 0;
v_curr_holiday := 0;

BEGIN
v_mth_value :=
get_screen_entry_value_at (p_assignment_id,
p_element_entry_id,
'Monthly Value',
v_date
);
END;

BEGIN
v_gross := get_gross_at (p_assignment_id, v_date);
END;

IF v_mth_value = 0
THEN
p_month_value := ROUND ((v_gross * .25), 2);
ELSE
p_month_value := v_mth_value;
END IF;

BEGIN
v_hrs_per_day := xxotl_hours_day (p_assignment_id, v_date);
END;

BEGIN
v_curr_otl_hrs := get_otl_regular_hours (p_assignment_id, v_date);
END;

v_otl_hrs := v_otl_hrs + v_curr_otl_hrs;

v_sal_per_day :=
v_sal_per_day
+ (p_month_value / v_mth_days / v_hrs_per_day) * v_curr_otl_hrs;

BEGIN
v_curr_week_hrs :=
xxotl_get_week_off_pay (p_assignment_id, v_date, v_date);
END;
IF v_curr_week_hrs <> 0
THEN
v_week_hrs := v_week_hrs + v_curr_week_hrs;
v_week_holi_pay :=
v_week_holi_pay
+ (p_month_value / v_mth_days) * v_curr_week_hrs;
END IF;

BEGIN
v_curr_holiday :=
xxlam_hr_absence_duration.holidays (p_assignment_id,
v_date,
v_date
);
END;

IF v_curr_holiday <> 0
THEN
v_holiday := v_holiday + v_curr_holiday;
v_week_holi_pay :=
v_week_holi_pay
+ (p_month_value / v_mth_days) * v_curr_holiday;
END IF;

DBMS_OUTPUT.put_line ( v_sal_per_day
|| 'Week:'
|| v_week_hrs
|| 'OTL:'
|| v_otl_hrs
|| 'Holiday:'
|| v_holiday
);
END LOOP;

v_sal_per_day := v_sal_per_day + v_week_holi_pay;

v_value := ROUND (v_sal_per_day, 2);
DBMS_OUTPUT.put_line ( v_days
|| v_mth_days
|| (v_week_hrs + v_holiday)
|| v_otl_hrs
);
RETURN v_value;
END;

-- ----------------------------------------------------------------------------
-- |-------------------------< calculate_other_all >-------------------------|
-- ----------------------------------------------------------------------------
FUNCTION calculate_other_all (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_month_value OUT NUMBER
)
RETURN NUMBER
IS
v_salary NUMBER := 0;
v_gross NUMBER := 0;
v_value NUMBER := 0;
v_basic NUMBER := 0;
v_trans_all NUMBER := 0;
v_housing_all NUMBER := 0;
v_days NUMBER := (p_end_date - p_start_date);
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_hrs_per_day NUMBER := 0;
v_otl_hrs NUMBER := 0;
v_curr_otl_hrs NUMBER := 0;
v_week_hrs NUMBER := 0;
v_curr_week_hrs NUMBER := 0;
v_holiday NUMBER := 0;
v_curr_holiday NUMBER := 0;
v_payroll_name VARCHAR2 (100);
v_element_entry_id NUMBER := 0;
l_dummy NUMBER := 0;
v_week_holi_pay NUMBER := 0;
e NUMBER := 0;
v_basic_holiday NUMBER := 0;
v_skip_holiday NUMBER := 0;
v_business_group_id NUMBER := 0;
v_employee_number VARCHAR2 (250);

FUNCTION get_element_entry_id (
p_assignment_id NUMBER,
p_element_name VARCHAR2,
p_date DATE
)
RETURN NUMBER
IS
l_element_entry_id NUMBER := 0;
BEGIN
SELECT DISTINCT peef.element_entry_id
INTO l_element_entry_id
FROM pay_element_types_f_vl pet,
pay_element_entries_f peef
WHERE 1 = 1
AND pet.element_name = p_element_name
AND peef.element_type_id = pet.element_type_id
AND peef.assignment_id = p_assignment_id
AND TRUNC (p_date) BETWEEN peef.effective_start_date
AND peef.effective_end_date;

RETURN l_element_entry_id;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
BEGIN
v_mth_days := get_month_days (p_assignment_id, p_start_date);

FOR i IN 0 .. v_days
LOOP
v_date := p_start_date + i;
v_curr_otl_hrs := 0;
v_curr_week_hrs := 0;
v_curr_holiday := 0;
v_basic_holiday := 0;
p_month_value := 0;

BEGIN
DBMS_OUTPUT.put_line ('Entry ID:' || v_element_entry_id);

SELECT DISTINCT peef.element_entry_id

INTO v_element_entry_id
FROM pay_element_types_f_vl pet,
pay_element_entries_f peef
WHERE 1 = 1
AND pet.element_name = 'Basic Salary'
AND peef.element_type_id = pet.element_type_id
AND peef.assignment_id = p_assignment_id
AND TRUNC (v_date) BETWEEN peef.effective_start_date
AND peef.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
v_element_entry_id := 0;
END;

BEGIN
e :=
calculate_basic_salary (p_assignment_id,
v_element_entry_id,
TRUNC (v_date),
TRUNC (v_date),
v_basic,
v_basic_holiday,
l_dummy,
l_dummy,
l_dummy,
l_dummy,
l_dummy
);
END;

BEGIN
v_mth_value :=
get_screen_entry_value_at (p_assignment_id,
p_element_entry_id,
'Monthly Value',
v_date
);
END;

BEGIN
v_gross := get_gross_at (p_assignment_id, v_date);
END;

BEGIN
v_payroll_name :=
get_payroll_name (p_assignment_id, TRUNC (v_date));
END;

IF v_mth_value = 0
THEN
IF v_payroll_name IN
(' Admin Payroll')
THEN
/* Calculation of Transportation Allowance */
BEGIN
v_element_entry_id := 0;

SELECT DISTINCT peef.element_entry_id

INTO v_element_entry_id
FROM pay_element_types_f_vl pet,
pay_element_entries_f peef
WHERE 1 = 1
AND pet.element_name =
'Transportation Allowance'
AND peef.element_type_id = pet.element_type_id
AND peef.assignment_id = p_assignment_id
AND TRUNC (v_date)
BETWEEN peef.effective_start_date
AND peef.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
v_element_entry_id := 0;
END;

BEGIN
e :=
calculate_trans_all (p_assignment_id,
v_element_entry_id,
TRUNC (v_date),
TRUNC (v_date),
v_trans_all
);
END;

/* Calculation of Housing Allowance */

BEGIN
v_element_entry_id := 0;

-- v_element_entry_id :=
get_element_entry_id(p_assignment_id,'Housing Allowance',trunc(v_date));
SELECT DISTINCT peef.element_entry_id
INTO v_element_entry_id
FROM pay_element_types_f_vl pet,
pay_element_entries_f peef
WHERE 1 = 1
AND pet.element_name = 'Housing Allowance'
AND peef.element_type_id = pet.element_type_id
AND peef.assignment_id = p_assignment_id
AND TRUNC (v_date)
BETWEEN peef.effective_start_date
AND peef.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
v_element_entry_id := 0;
END;
BEGIN
e :=
calculate_housing_all (p_assignment_id,
v_element_entry_id,
TRUNC (v_date),
TRUNC (v_date),
v_housing_all
);
END;

p_month_value :=
ROUND (v_gross - (v_basic + v_trans_all + v_housing_all), 2);
ELSE
IF v_mth_value != 0
AND v_payroll_name IN ('Payroll')
THEN
p_month_value := v_gross - v_basic;
ELSIF v_payroll_name IN ('Payroll')
THEN
p_month_value := v_mth_value;
ELSE
p_month_value := v_gross - v_basic;
END IF;
END IF;
ELSE
p_month_value := v_mth_value;
END IF;

DBMS_OUTPUT.put_line ('Month_Value:' || p_month_value);

BEGIN
v_hrs_per_day := xxotl_hours_day (p_assignment_id, v_date);
END;

BEGIN
v_curr_otl_hrs := get_otl_regular_hours (p_assignment_id, v_date);
END;

v_otl_hrs := v_otl_hrs + v_curr_otl_hrs;

v_sal_per_day :=
v_sal_per_day
+ (p_month_value / v_mth_days / v_hrs_per_day) * v_curr_otl_hrs;

BEGIN
v_curr_week_hrs :=
xxotl_get_week_off_pay (p_assignment_id, v_date, v_date);
END;

DBMS_OUTPUT.put_line ('Week off - OTher :' || v_curr_week_hrs);

IF v_curr_week_hrs <> 0
THEN
v_week_hrs := v_week_hrs + v_curr_week_hrs;
v_week_holi_pay :=
v_week_holi_pay
+ (p_month_value / v_mth_days) * v_curr_week_hrs;
END IF;

-- Unpaid and vacation with advance to be ignored

BEGIN
SELECT employee_number, paaf.business_group_id
INTOv_employee_number, v_business_group_id
FROMper_all_people_f papf, per_all_assignments_f paaf
WHERE papf.person_id = paaf.person_id
ANDpaaf.assignment_id = p_assignment_id
ANDv_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND v_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.primary_flag = 'Y'
AND papf.current_employee_flag = 'Y';

v_skip_holiday :=
xxlam_hr_absence_duration.get_holiday_unpaid_leaves
(v_employee_number,
v_business_group_id,
v_date
);
EXCEPTION
WHEN OTHERS
THEN
v_skip_holiday := 0;
END;

IF v_skip_holiday <> 1
THEN
BEGIN
v_curr_holiday :=
xxlam_hr_absence_duration.holidays (p_assignment_id,
v_date,
v_date
);
END;

IF v_curr_holiday <> 0
THEN
v_holiday := v_holiday + v_curr_holiday;
v_week_holi_pay :=
v_week_holi_pay
+ (p_month_value / v_mth_days) * v_curr_holiday;
END IF;
END IF;

DBMS_OUTPUT.put_line ( v_sal_per_day
|| 'Week:'
|| v_week_hrs
|| 'OTL:'
|| v_otl_hrs
|| 'Holiday:'
|| v_holiday
);
END LOOP;

v_sal_per_day := v_sal_per_day + v_week_holi_pay;

v_value := ROUND (v_sal_per_day, 2);
DBMS_OUTPUT.put_line ( v_days
|| v_mth_days
|| (v_week_hrs + v_holiday)
|| v_otl_hrs
);
RETURN v_value;
END;

-- ----------------------------------------------------------------------------
-- |-------------------------< get_contract_duration >--------------------------|
-- ----------------------------------------------------------------------------
FUNCTION get_contract_duration (p_assignment_id NUMBER, p_date DATE)
RETURN NUMBER
IS
l_person_id NUMBER := 0;
l_duration NUMBER := 0;
BEGIN
SELECT DISTINCT person_id
INTO l_person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;

BEGIN
SELECT DURATION
INTO l_duration
FROM per_contracts pc
WHERE person_id = l_person_id
AND p_date BETWEEN pc.effective_start_date AND pc.effective_end_date;

RETURN l_duration;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
END;

--
-- ----------------------------------------------------------------------------
-- |----------------------------< calculate_gratuity >------------------------|
-- ----------------------------------------------------------------------------
--
FUNCTION calculate_gratuity (
p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_initiator IN VARCHAR2,
p_contract IN VARCHAR2,
p_service_period OUT NOCOPY VARCHAR2
)
RETURN NUMBER
AS
l_years NUMBER := 0;
l_months NUMBER := 0;
l_days NUMBER := 0;
l_value NUMBER := 0;
l_sal_day NUMBER;
-- := (get_salary_at (p_assignment_id, p_end_date) * .6) / 365;
l_service_days NUMBER := 0;
l_salary NUMBER := 0;
l_unpaid_leave NUMBER := 0;
l_service_years NUMBER := 0;
p_month_factor NUMBER;
l_contract_duration NUMBER;
l_salary_tmp NUMBER;
l_bg_id NUMBER;
BEGIN
SELECT business_group_id
INTO l_bg_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_end_date BETWEEN effective_start_date AND effective_end_date;

l_salary_tmp :=
xxlam_hr_payroll_all.get_balance (p_assignment_id,
l_bg_id,
'Monthly Basic',
'RUN',
p_end_date
);
l_sal_day := (l_salary_tmp * 12) / 365;

--l_unpaid_leave := nvl(XXLAM_HR_PAYROLL_ALL.get_balance (p_assignment_id,

'Unpaid Leave', 'ITD', p_end_date ),0);
-- l_unpaid_leave :=
-- XXLAM_HR_ABSENCE_DURATION.get_no_of_leaves_taken_ass_id (
-- l_bg_id,
-- p_assignment_id,
-- 'Unpaid Leave',
-- p_start_date,
-- p_end_date
-- )
-- + NVL (XXLAM_HR_PAYROLL_ALL.get_balance (p_assignment_id,
-- 'Initial Unpaid Adjustment',
-- 'ITD',
-- p_end_date), 0);
l_unpaid_leave :=
xxlam_hr_payroll_all.get_all_unpaid_days (p_assignment_id,
p_end_date);

p_month_factor :=
MONTHS_BETWEEN ((p_end_date - l_unpaid_leave), (p_start_date - 1));
l_service_days := ((p_end_date - p_start_date) + 1) - l_unpaid_leave;

SELECT TRUNC (p_month_factor / 12) YEAR,

TRUNC (p_month_factor - (12 * TRUNC (p_month_factor / 12)))
months,
ROUND (30 * (p_month_factor - TRUNC (p_month_factor))) days
INTO l_years,
l_months,
l_days
FROM DUAL;

DBMS_OUTPUT.put_line ( 'l_sal_day = '

|| l_sal_day
|| ' p_month_factor = '
|| p_month_factor
|| 'l_salary_tmp = '
|| l_salary_tmp
);
l_service_years := (p_month_factor / 12);
l_contract_duration :=
xxlam_hr_payroll_all.get_contract_duration (p_assignment_id,
p_end_date
);
DBMS_OUTPUT.put_line ( 'l_service_years= '
|| l_service_years
|| ' l_contract_duration = '
|| l_contract_duration
);
fnd_file.put_line (fnd_file.LOG,
'Service:'
|| l_service_years
|| 'Contract:'
|| l_contract_duration
|| 'Contract Type:'
|| p_contract
|| 'Unpaid Leave: '
|| l_unpaid_leave
|| 'Service Days: '
|| l_service_days
);

/******************** Gratuity . Resignation Unlimited Contract

******************/
IF p_initiator = 'EE'
AND (p_contract = 'UNLIMITED_CONTRACT' OR p_contract = 'N')
THEN
IF l_service_years < 1
THEN
l_value := 0;
ELSIF l_service_years >= 1 AND l_service_years < 3
THEN
l_value := ROUND (l_service_days * (l_sal_day) * 7 / 365, 0);
ELSIF l_service_years >= 3 AND l_service_years < 5
THEN
l_value := ROUND (l_service_days * (l_sal_day) * 14 / 365, 0);
ELSIF l_service_years = 5
THEN
l_value := ROUND (l_service_days * (l_sal_day) * 21 / 365, 0);
ELSIF l_service_years > 5
THEN
l_value :=
ROUND ( (1825 * (l_sal_day) * 21 / 365)
+ (l_service_days - 1825) * ((l_sal_day) * 30 / 365),
2
);
END IF;
/******************** Gratuity . Resignation Fixed Contract
******************/
ELSIF p_initiator = 'EE'
AND (p_contract = 'FIXED_CONTRACT' OR p_contract = 'N')
THEN
IF l_service_years < l_contract_duration
THEN
l_value := 0;
END IF;
/******************** Gratuity - Termination Unlimited Contract AND
Termination Fixed Contract ******************/
ELSIF p_initiator = 'ER'
AND ( p_contract = 'UNLIMITED_CONTRACT'
OR p_contract = 'FIXED_CONTRACT'
OR p_contract = 'N'
)
THEN
IF l_service_years < 5
THEN
l_value := ROUND (l_service_days * (l_sal_day) * 21 / 365, 0);
ELSIF l_service_years >= 5
THEN
l_value :=
ROUND ( (1825 * (l_sal_day) * 21 / 365)
+ (l_service_days - 1825) * ((l_sal_day) * 30 / 365),
2
);
END IF;
ELSE
l_value := 0;
END IF;

DBMS_OUTPUT.put_line ( 'l_service_years = '

|| l_service_years
|| ' l_service_days = '
|| l_service_days
|| ' l_value = '
|| l_value
|| ' l_sal_day= '
|| l_sal_day
);
p_service_period :=
'Total Days '
|| l_service_days
|| ' or '
|| l_years
|| 'Years '
|| l_months
|| 'Months '
|| l_days
|| 'Days ';
RETURN l_value;
EXCEPTION
WHEN OTHERS
THEN
l_value := 0;
p_service_period := NULL;
RETURN l_value;
END;

-- ----------------------------------------------------------------------------
-- |-------------------------< get_ticket_value >--------------------------|
-- ----------------------------------------------------------------------------
FUNCTION get_ticket_value_at (
p_business_group_id NUMBER,
p_assignment_id NUMBER,
p_date DATE,
p_ticket_adult OUT NUMBER,
p_ticket_child OUT NUMBER
)
RETURN NUMBER
AS
l_ticket_class VARCHAR2 (100);
l_ticket_to VARCHAR2 (200);
l_no_of_tickets_a NUMBER;
l_no_of_tickets_c NUMBER;
BEGIN
BEGIN
SELECT pac.segment2 ticket_class, pac.segment3 ticket_to,
pac.segment4 tickets_adult, pac.segment6 tickets_child
INTO l_ticket_class, l_ticket_to,
l_no_of_tickets_a, l_no_of_tickets_c
FROM per_analysis_criteria pac,
fnd_id_flex_structures_vl fflex,
per_person_analyses ppa,
per_all_assignments_f paf
WHERE 1 = 1
AND UPPER (fflex.id_flex_structure_code) IN UPPER
('XXHR_TICKET_DETAILS'
)
AND fflex.id_flex_num = pac.id_flex_num
AND ppa.analysis_criteria_id = pac.analysis_criteria_id
AND paf.person_id = ppa.person_id
AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND p_date BETWEEN NVL (ppa.date_from, TRUNC (p_date))
AND NVL (date_to, TRUNC (p_date))
AND paf.assignment_id = p_assignment_id;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

BEGIN
p_ticket_adult :=
NVL (hruserdt.get_table_value (p_business_group_id,
' HR Ticket Sector',
'ADULT-' || l_ticket_class,
l_ticket_to,
p_date
),
0
);
EXCEPTION
WHEN OTHERS
THEN
p_ticket_adult := 0;
END;

BEGIN
p_ticket_child :=
NVL (hruserdt.get_table_value (p_business_group_id,
' HR Ticket Sector',
'CHILD-' || l_ticket_class,
l_ticket_to,
p_date
),
0
);
EXCEPTION
WHEN OTHERS
THEN
p_ticket_child := 0;
END;

RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
RETURN 1;
END;

-- ----------------------------------------------------------------------------
-- |-------------------------< get_accrued_ticket >--------------------------|
-- ----------------------------------------------------------------------------
FUNCTION get_accrued_ticket (
p_business_group_id NUMBER,
p_assignment_id NUMBER,
p_start_date DATE,
p_end_date DATE,
p_accrued_ticket_a OUT NUMBER,
p_accrued_ticket_c OUT NUMBER,
p_accrued_ticket_a_dep OUT NUMBER
)
RETURN NUMBER
AS
l_person_id NUMBER;
l_nod NUMBER := (p_end_date)
- (p_start_date);
l_date DATE;
l_days NUMBER;
l_accrual_date DATE;
l_hire_date DATE;
l_age NUMBER;
l_person_type VARCHAR2 (30);
l_family_status VARCHAR2 (10);
l_accrued_amount NUMBER := 0;
l_ticket_class VARCHAR2 (30);
l_dep_ticket_class VARCHAR2 (30);
l_ticket_to VARCHAR2 (30);
l_ticket_adult NUMBER := 0;
l_ticket_child NUMBER := 0;
l_ticket_spouse NUMBER := 0;
l_ticket_a_balance NUMBER := 0;
l_ticket_c_balance NUMBER := 0;
l_no_of_tickets_a NUMBER := 0;
l_no_of_tickets_c NUMBER := 0;
l_absence NUMBER := 0;
l_accrued_ticket_s NUMBER := 0;
l_start_date DATE;
l_end_date DATE;
l_curr_month_amt NUMBER := 0;
l_curr_mon_a NUMBER := 0;
l_curr_mon_c NUMBER := 0;
l_accrued_ticket_ca NUMBER := 0;
l_bg_id NUMBER;
l_ticket_a_dep_balance NUMBER := 0;
ln_payroll_id NUMBER := 0;
CURSOR c_rel (p_person_id IN NUMBER, p_date IN DATE)
IS
SELECT pcr.person_id, pcr.contact_person_id, pcr.contact_type,
pcr.date_start, pap.date_of_birth,
pcr.cont_attribute2 ticket_eligibility
FROM per_contact_relationships pcr, per_all_people_f pap
WHERE 1 = 1
AND pap.person_id = pcr.contact_person_id
AND pcr.person_id = p_person_id
AND pcr.contact_type IN ('S', 'C')
AND p_date BETWEEN pap.effective_start_date AND pap.effective_end_date
AND p_date BETWEEN NVL (pcr.date_start, pap.start_date)
AND NVL
(pcr.date_end, TO_DATE ('31-DEC-4712'))
--AND pcr.cont_attribute2 = 'Y'
;
BEGIN
l_date := p_end_date;
l_start_date := p_start_date;
l_end_date := p_end_date;
DBMS_OUTPUT.put_line (p_start_date);
DBMS_OUTPUT.put_line (p_end_date);

SELECT DISTINCT person_id, business_group_id

INTO l_person_id, l_bg_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND l_date BETWEEN effective_start_date AND effective_end_date;

SELECT start_date
INTO l_hire_date
FROM per_all_people_f
WHERE person_id = l_person_id
AND l_date BETWEEN effective_start_date AND effective_end_date;

BEGIN
l_person_type :=
xxlam_hr_utilities_pkg.get_person_type ('NAME',
l_person_id,
l_date
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

BEGIN
l_ticket_a_balance :=
get_balance (p_assignment_id,
l_bg_id,
'TICKET_NO_OF_TKTS_ADULT',
'ITD',
l_start_date
);
EXCEPTION
WHEN OTHERS
THEN
l_ticket_a_balance := 0;
END;

DBMS_OUTPUT.put_line ('L_TICKET_A_BALANCE' || l_ticket_a_balance);

fnd_file.put_line (fnd_file.LOG,
'L_TICKET_A_BALANCE'
|| l_ticket_a_balance
|| 'Person Type'
|| l_person_type
);

BEGIN
l_ticket_a_dep_balance :=
get_balance (p_assignment_id,
l_bg_id,
'TICKET_NO_OF_TKTS_ADULT_DEP',
'ITD',
l_start_date
);
EXCEPTION
WHEN OTHERS
THEN
l_ticket_a_balance := 0;
END;

DBMS_OUTPUT.put_line ('L_TICKET_A_DEP_BALANCE' || l_ticket_a_balance);

fnd_file.put_line (fnd_file.LOG,
'L_TICKET_A_DEP_BALANCE'
|| l_ticket_a_dep_balance
|| 'Person Type'
|| l_person_type
);

BEGIN
l_ticket_c_balance :=
get_balance (p_assignment_id,
l_bg_id,
'TICKET_NO_OF_TKTS_CHILD',
'ITD',
l_start_date
);
EXCEPTION
WHEN OTHERS
THEN
l_ticket_c_balance := 0;
END;

DBMS_OUTPUT.put_line ('L_TICKET_C_BALANCE' || l_ticket_c_balance);

p_accrued_ticket_a := 0;
p_accrued_ticket_a_dep := 0;
p_accrued_ticket_c := 0;
l_accrued_ticket_s := 0; -- Changed
l_accrued_ticket_ca := 0; -- Changed

FOR i IN 0 .. l_nod
LOOP
l_date := (l_start_date) + i;
l_days := TO_NUMBER (TO_CHAR (LAST_DAY (l_date), 'DD'));
DBMS_OUTPUT.put_line ('DAYS :' || l_days);
BEGIN
-- l_no_of_tickets_a, l_no_of_tickets_c
SELECT pac.segment2 ticket_class, pac.segment3 ticket_to,
pac.segment5 dep_tickets_class
INTO l_ticket_class, l_ticket_to,
l_dep_ticket_class
FROM per_analysis_criteria pac,
fnd_id_flex_structures_vl fflex,
per_person_analyses ppa,
per_all_assignments_f paf
WHERE 1 = 1
AND UPPER (fflex.id_flex_structure_code) IN UPPER
('XXHR_TICKET_DETAILS'
)
AND fflex.id_flex_num = pac.id_flex_num
AND ppa.analysis_criteria_id = pac.analysis_criteria_id
AND paf.person_id = ppa.person_id
AND l_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND l_date BETWEEN NVL (ppa.date_from, TRUNC (l_date))
AND NVL (date_to, TRUNC (l_date))
AND paf.assignment_id = p_assignment_id;

fnd_file.put_line (fnd_file.LOG,
'Date:'
|| p_end_date
|| ' Class:'
|| l_ticket_class
|| ' Dest:'
|| l_ticket_to
|| 'No of Ticket'
|| l_no_of_tickets_a
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

BEGIN
l_ticket_adult :=
NVL (hruserdt.get_table_value (p_business_group_id,
' HR Ticket Sector',
'ADULT-' || l_ticket_class,
l_ticket_to,
l_date
),
0
);
fnd_file.put_line (fnd_file.LOG,
'Ticket Adult:' || l_ticket_adult);
EXCEPTION
WHEN OTHERS
THEN
l_ticket_adult := 0;
DBMS_OUTPUT.put_line ('Ticket Adult:' || SQLERRM);
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END;
DBMS_OUTPUT.put_line ('L_TICKET_ADULT' || l_ticket_adult);

BEGIN
l_ticket_spouse :=
NVL (hruserdt.get_table_value (p_business_group_id,
' HR Ticket Sector',
'ADULT-' || l_dep_ticket_class,
l_ticket_to,
l_date
),
0
);
fnd_file.put_line (fnd_file.LOG,
'Ticket Spouse:' || l_ticket_spouse
);
EXCEPTION
WHEN OTHERS
THEN
l_ticket_spouse := 0;
DBMS_OUTPUT.put_line ('Spouse Adult:' || SQLERRM);
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END;

DBMS_OUTPUT.put_line ('L_TICKET_CHILD' || l_ticket_child);

-- Checking Family Status

BEGIN
SELECT ass_attribute1
INTO l_family_status
FROM per_all_assignments_f paf
WHERE 1 = 1
AND assignment_id = p_assignment_id
AND l_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_family_status := 'N';
END;

-- IF Family Status is no then only one ticket for adult

IF l_family_status = 'N'
THEN
p_accrued_ticket_a := p_accrued_ticket_a + NVL (1, 0);
ELSE
-- For Employee
p_accrued_ticket_a := p_accrued_ticket_a + 1;

FOR rec_rel IN c_rel (l_person_id, l_date)

LOOP
BEGIN
-- For Spouse
IF rec_rel.contact_type = 'S'
AND rec_rel.ticket_eligibility = 'Y'
THEN
--p_accrued_ticket_a := p_accrued_ticket_a + 1;
l_accrued_ticket_s := l_accrued_ticket_s + 1; -- Changed
--l_accrued_ticket_s := l_accrued_ticket_s + NVL (1, 0);
END IF;

-- For Child
IF rec_rel.contact_type = 'C'
AND rec_rel.ticket_eligibility = 'Y'
THEN
SELECT MONTHS_BETWEEN (l_date, rec_rel.date_of_birth)
/ 12
INTO l_age
FROM DUAL;

IF l_age > 0
AND l_age <= 12
AND rec_rel.ticket_eligibility = 'Y'
THEN
p_accrued_ticket_c := p_accrued_ticket_c + NVL (1, 0);
ELSIF l_age > 12 AND rec_rel.ticket_eligibility = 'Y'
THEN
l_accrued_ticket_ca := l_accrued_ticket_ca + 1;
-- Changed
--p_accrued_ticket_a := p_accrued_ticket_a + NVL (1, 0);
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No Data Found');
NULL;
END;
END LOOP;
END IF;
END LOOP;

-- Changed
IF l_ticket_spouse = 0
THEN
l_accrued_ticket_s := 0;
l_accrued_ticket_ca := 0;
END IF;

IF l_ticket_child = 0
THEN
p_accrued_ticket_c := 0;
END IF;

IF l_ticket_adult = 0
THEN
p_accrued_ticket_a := 0;
END IF;

p_accrued_ticket_a_dep := l_accrued_ticket_s + l_accrued_ticket_ca;

IF UPPER (l_person_type) = 'STAFF'

THEN
p_accrued_ticket_a := p_accrued_ticket_a / 365;
--(p_accrued_ticket_a + l_accrued_ticket_s) / 365;
p_accrued_ticket_c := p_accrued_ticket_c / 365;
p_accrued_ticket_a_dep := p_accrued_ticket_a_dep / 365;
--l_accrued_ticket_s := l_accrued_ticket_s / 365; -- Changed
--l_accrued_ticket_ca := l_accrued_ticket_ca / 365; -- Changed
DBMS_OUTPUT.put_line ('P_ACCRUED_TICKET_A' || p_accrued_ticket_a);
DBMS_OUTPUT.put_line ('P_ACCRUED_TICKET_C' || p_accrued_ticket_c);
ELSE
p_accrued_ticket_a := p_accrued_ticket_a / 730;
--p_accrued_ticket_c := p_accrued_ticket_c / 730;
l_accrued_ticket_s := 0; -- Changed
p_accrued_ticket_c := 0;
l_accrued_ticket_ca := 0; -- Changed
p_accrued_ticket_a_dep := 0;

/*
Changed for PMYI-DIG workers
*/
BEGIN
SELECT payroll_id
INTO ln_payroll_id
FROM per_all_assignments_f
WHERE 1 = 1
AND assignment_id = p_assignment_id
AND primary_flag = 'Y'
AND TO_DATE ('25-MAY-2012') BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
ln_payroll_id := 0;
END;

IF ln_payroll_id = 67
THEN
l_ticket_a_balance := 0;
END IF;
/*
Changed for PMYI-DIG workers
*/
END IF;

DBMS_OUTPUT.put_line ('P_ACCRUED_TICKET_A' || p_accrued_ticket_a);

DBMS_OUTPUT.put_line ('P_ACCRUED_TICKET_C' || p_accrued_ticket_c);
fnd_file.put_line (fnd_file.LOG,
'P_ACCRUED_TICKET_A' || p_accrued_ticket_a
);
-- Calcualting Amount based on class
fnd_file.put_line (fnd_file.LOG,
'L_ACCRUED_AMOUNT: Adult: Before:'
|| l_ticket_a_balance
);
fnd_file.put_line (fnd_file.LOG,
'L_ACCRUED_AMOUNT: Child: Before:'
|| l_ticket_c_balance
);
--if p_assignment_id in (3098,2234,14903) then
-- l_ticket_a_balance := 0;
-- end if;
l_curr_month_amt :=
(l_ticket_adult * (l_ticket_a_balance + p_accrued_ticket_a)
)
+ ( l_ticket_spouse
* (l_ticket_a_dep_balance + p_accrued_ticket_a_dep)
)
+ -- Changed
(l_ticket_child * (l_ticket_c_balance + p_accrued_ticket_c)
);
l_accrued_amount := l_accrued_amount + l_curr_month_amt;
fnd_file.put_line (fnd_file.LOG,
'L_ACCRUED_AMOUNT: Current: ' || l_curr_month_amt
);
DBMS_OUTPUT.put_line ('L_ACCRUED_AMOUNT' || l_accrued_amount);
fnd_file.put_line (fnd_file.LOG,
'L_ACCRUED_AMOUNT: Total: ' || l_accrued_amount
);
-- L_ACCRUED_AMOUNT := NVL((L_TICKET_ADULT *
L_TICKET_A_BALANCE),0)+NVL((L_TICKET_CHILD * L_TICKET_C_BALANCE),0);
l_accrued_amount := ROUND (NVL (l_accrued_amount, 0), 2);
RETURN l_accrued_amount;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;

-- ----------------------------------------------------------------------------
-- Get Payroll Name
-- ----------------------------------------------------------------------------
FUNCTION get_payroll_name (p_emp_no VARCHAR2, p_date DATE)
RETURN VARCHAR2
AS
PRAGMA AUTONOMOUS_TRANSACTION;
l_payroll_name VARCHAR2 (200);
BEGIN
SELECT hr_general.decode_payroll (payroll_id)
INTO l_payroll_name
FROM per_all_people_f ppf, per_all_assignments_f paf
WHERE 1 = 1
AND ppf.employee_number = p_emp_no
AND ppf.person_id = paf.person_id
AND primary_flag = 'Y'
AND TRUNC (p_date) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND TRUNC (p_date) BETWEEN paf.effective_start_date
AND paf.effective_end_date;

RETURN l_payroll_name;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;

-- ----------------------------------------------------------------------------
-- |-------------------------< get_payroll_name - Overriding Function>--------|
-- ----------------------------------------------------------------------------
FUNCTION get_payroll_name (p_assignment_id NUMBER, p_date DATE)
RETURN VARCHAR2
AS
PRAGMA AUTONOMOUS_TRANSACTION;
l_payroll_name VARCHAR2 (200);
BEGIN
SELECT hr_general.decode_payroll (payroll_id)
INTO l_payroll_name
FROM per_all_assignments_f paf
WHERE 1 = 1
AND paf.assignment_id = p_assignment_id
AND primary_flag = 'Y'
AND TRUNC (p_date) BETWEEN paf.effective_start_date
AND paf.effective_end_date;

RETURN l_payroll_name;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;

-- ----------------------------------------------------------------------------
-- Get Notified Date
-- ----------------------------------------------------------------------------
FUNCTION get_notified_date (p_emp_no VARCHAR2, p_date DATE)
RETURN DATE
AS
PRAGMA AUTONOMOUS_TRANSACTION;
l_notified_date DATE;
BEGIN
SELECT notified_termination_date
INTO l_notified_date
FROM per_all_people_f ppf, per_periods_of_service pps
WHERE 1 = 1
AND ppf.employee_number = p_emp_no
AND ppf.person_id = pps.person_id
AND TRUNC (p_date) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;

RETURN l_notified_date;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
-- ----------------------------------------------------------------------------
-- Get Person Type
-- ----------------------------------------------------------------------------
FUNCTION get_person_type (
p_name_or_id VARCHAR2,
p_person_id NUMBER,
p_effective_date DATE DEFAULT SYSDATE
)
RETURN VARCHAR2
AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_pereson_type VARCHAR2 (100);
v_pereson_type_id VARCHAR2 (100);
BEGIN
SELECT ppt.user_person_type, ppt.person_type_id
INTO v_pereson_type, v_pereson_type_id
FROM per_all_people_f papf,
per_person_types ppt,
per_person_type_usages_f ptu
WHERE 1 = 1
AND papf.person_id = p_person_id
AND p_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.person_id = ptu.person_id
AND ppt.person_type_id = ptu.person_type_id
AND ppt.system_person_type = 'EMP'
AND ppt.business_group_id = papf.business_group_id;

IF UPPER (p_name_or_id) = 'NAME'

THEN
RETURN v_pereson_type;
ELSE
RETURN v_pereson_type_id;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END get_person_type;

-- ----------------------------------------------------------------------------
-- Get Insurance Type
-- ----------------------------------------------------------------------------
FUNCTION get_insurance_type (
p_business_group_id NUMBER,
p_assignment_id NUMBER,
p_date DATE
)
RETURN VARCHAR2
AS
l_ins_type VARCHAR2 (100);
BEGIN
SELECT ass_attribute2
INTO l_ins_type
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_date BETWEEN effective_start_date AND effective_end_date;

RETURN l_ins_type;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;

-- ----------------------------------------------------------------------------
-- Get GL Conversion Rate
-- ----------------------------------------------------------------------------
FUNCTION get_gl_daily_rate (
p_from_curr VARCHAR2,
p_to_curr VARCHAR2,
p_date DATE
)
RETURN NUMBER
AS
l_con_rate NUMBER;
BEGIN
SELECT conversion_rate
INTO l_con_rate
FROM gl_daily_rates
WHERE from_currency = p_from_curr
AND to_currency = p_to_curr
AND conversion_type = 'Corporate'
AND conversion_date = p_date;

RETURN l_con_rate;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;

-- ----------------------------------------------------------------------------
-- Get Person Type by Assignment_id
-- ----------------------------------------------------------------------------
FUNCTION get_person_type_ass (
p_ass_id NUMBER,
p_effective_date DATE DEFAULT SYSDATE
)
RETURN VARCHAR2
AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_pereson_type VARCHAR2 (100);
v_pereson_type_id VARCHAR2 (100);
BEGIN
SELECT ppt.user_person_type, ppt.person_type_id
INTO v_pereson_type, v_pereson_type_id
FROM per_all_people_f papf,
per_person_types ppt,
per_person_type_usages_f ptu,
per_all_assignments_f paaf
WHERE 1 = 1
AND paaf.assignment_id = p_ass_id
AND papf.person_id = paaf.person_id
AND p_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND p_effective_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND papf.person_id = ptu.person_id
AND ppt.person_type_id = ptu.person_type_id
AND ppt.system_person_type = 'EMP'
AND ppt.business_group_id = papf.business_group_id;

RETURN v_pereson_type;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END get_person_type_ass;

--------------------------------------------------------------------------------
-- Get Advance Salary Flag
--------------------------------------------------------------------------------
FUNCTION get_adv_sal_flag (p_element_entry_id NUMBER, p_effective_date DATE)
RETURN VARCHAR2
AS
l_adv_flag VARCHAR2 (10);
BEGIN
SELECT pev.screen_entry_value
INTO l_adv_flag
FROM pay_element_types_f_vl pet,
pay_input_values_f piv,
pay_element_entry_values_f pev,
pay_element_entries_f peef
WHERE 1 = 1
AND pet.element_name IN ('Annual Vacation')
AND pet.element_type_id = piv.element_type_id
AND piv.NAME = 'Advance Salary'
AND piv.input_value_id = pev.input_value_id
AND peef.element_entry_id = pev.element_entry_id
AND peef.element_entry_id = p_element_entry_id
AND p_effective_date BETWEEN pev.effective_start_date
AND pev.effective_end_date;

RETURN l_adv_flag;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'N';
END;

--------------------------------------------------------------------------------
-- Get OTL Regular Hours Date Earned
--------------------------------------------------------------------------------
FUNCTION get_otl_reg_hrs_date_earned (p_element_entry_id NUMBER)
RETURN DATE
AS
l_date_earned DATE;
BEGIN
SELECT date_earned
INTO l_date_earned
FROM pay_element_entries_f peef
WHERE 1 = 1 AND peef.element_entry_id = p_element_entry_id;

fnd_file.put_line (fnd_file.LOG, p_element_entry_id || l_date_earned);

l_date_earned :=
NVL (l_date_earned, TO_DATE ('31-DEC-4712', 'DD-MON-YYYY'));
RETURN l_date_earned;
EXCEPTION
WHEN OTHERS
THEN
l_date_earned := TO_DATE ('31-DEC-4712', 'DD-MON-YYYY');
RETURN l_date_earned;
END;

--------------------------------------------------------------------------------
-- Get OTL get week off pay
--------------------------------------------------------------------------------
FUNCTION xxotl_get_week_off_pay (
p_assignment_id IN NUMBER,
p_period_start_date IN DATE,
p_period_end_date IN DATE
)
RETURN NUMBER
IS
v_days NUMBER;
v_cur_date DATE;
v_employee_number VARCHAR2 (30);
v_shift VARCHAR2 (30);
v_holiday_y_n VARCHAR2 (30);
v_holiday NUMBER := 0;
v_person_id NUMBER := 0;
v_leave NUMBER := 0;
v_week_off NUMBER := 0;
v_cnt NUMBER := NULL;
v_date DATE;
v_prev_shift VARCHAR2 (30);
v_next_shift VARCHAR2 (30);
v_prev_holiday VARCHAR2 (30);
v_next_holiday VARCHAR2 (30);
v_cur_worked VARCHAR2 (30);
v_pre_worked VARCHAR2 (30);
v_next_worked VARCHAR2 (30);
-- All days
BEGIN
BEGIN
SELECT p_period_end_date - p_period_start_date
INTO v_days
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
v_days := 0;
END;

DBMS_OUTPUT.put_line ('v_days:' || v_days);

v_cnt := 0;

FOR i IN 0 .. v_days
LOOP
v_shift := NULL;
v_employee_number := NULL;
v_holiday_y_n := NULL;
v_cur_date := p_period_start_date + i;
v_week_off := 0;
v_holiday := 0;
v_leave := 0;
v_date := NULL;
v_prev_shift := NULL;
v_next_shift := NULL;
v_prev_holiday := NULL;
v_next_holiday := NULL;
v_cur_worked := 'N';

BEGIN
SELECT papf.employee_number, papf.person_id
INTOv_employee_number, v_person_id
FROMper_all_people_f papf, per_all_assignments_f paaf
WHERE 1 = 1
ANDpapf.person_id = paaf.person_id
ANDpaaf.assignment_id = p_assignment_id
ANDpaaf.primary_flag = 'Y'
ANDhr_general.get_user_status (paaf.assignment_status_type_id) =
'Active Assignment'
AND v_cur_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND v_cur_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
v_employee_number := NULL;
v_person_id := NULL;
END;

BEGIN
NULL;
/* commented by khurram
SELECT xxotl_utilities.xx_shift_schedule_function (
p_assignment_id,
v_cur_date
)
INTO v_shift
FROM DUAL; */
EXCEPTION
WHEN OTHERS
THEN
v_shift := NULL;
END;

DBMS_OUTPUT.put_line ( 'v_employee_number:'
|| v_employee_number
|| 'Shift-1:'
|| v_shift
|| ' cur date-1:'
|| v_cur_date
);

IF v_shift = 'Off'
THEN
v_week_off := 1;
/* Commented the below code
BEGIN
v_holiday :=
XXLAM_HR_ABSENCE_DURATION.holidays(p_assignment_id,v_cur_date,v_cur_date);
EXCEPTION
WHEN OTHERS
THEN
v_holiday := 0;
END;
Dbms_output.put_line ( 'v_employee_number:'||
v_employee_number||'Holiday:'||v_holiday);
IF v_holiday = 0 then
BEGIN
SELECT 1
into v_leave
FROM per_absence_attendances paa
WHERE 1=1
AND paa.person_id = v_person_id
AND v_cur_date between paa.date_start and
paa.date_end;
EXCEPTION
WHEN OTHERS
THEN
v_leave := 0;
END;
Dbms_output.put_line ( 'v_employee_number:'||
v_employee_number||'Leave:'||v_leave);
End if;
*/
END IF;

--v_week_off := v_week_off - v_holiday - v_leave ;

v_week_off := v_week_off;
-- Dbms_output.put_line ( 'v_employee_number:'||
v_employee_number||':cur date-1:'||v_cur_date );
v_cnt := v_cnt + v_week_off;
DBMS_OUTPUT.put_line ( 'v_employee_number:'
|| v_employee_number
|| ':v_cnt'
|| v_cnt
);
END LOOP;

DBMS_OUTPUT.put_line ( 'v_employee_number:'
|| v_employee_number
|| ':v_cnt'
|| v_cnt
);
fnd_file.put_line (fnd_file.LOG,
'v_employee_number:'
|| v_employee_number
|| 'v_cnt'
|| v_cnt
);
RETURN v_cnt;
EXCEPTION
WHEN OTHERS
THEN
v_cnt := 0;
RETURN v_cnt;
END;

--------------------------------------------------------------------------------
-- Get Hours Per Day
--------------------------------------------------------------------------------
FUNCTION xxotl_hours_day (p_assignment_id IN NUMBER, p_date IN DATE)
RETURN NUMBER
IS
v_hours NUMBER;
v_days NUMBER;
l_work_schedule VARCHAR2(200);
l_user_table_name VARCHAR2(200);
l_user_column_name VARCHAR2(200);
l_no_hours_in_Day NUMBER :=0;

BEGIN

---p_assignment_id := 141;
---p_date := to_Date('01-sep-2013');

select ASS_ATTRIBUTE1---,ASS_ATTRIBUTE1
into l_work_schedule
from per_All_Assignments_f
where assignment_id = p_assignment_id
and trunc(p_date) between effective_start_Date and effective_end_Date ;

Begin
select USER_TABLE_NAME , USER_COLUMN_NAME into
l_user_table_name , l_user_column_name
from
pay_user_tables t, pay_user_Columns c
where t.user_table_name = 'LAM_HR_WORK_SCHEDULE'
and c.user_table_id = t.user_table_id
and c.user_column_id = l_work_schedule;
DBMS_OUTPUT.put_line ('Work Schedule Assigned'||l_user_table_name||
'****** ' ||l_user_table_name );
Exception when Others then
DBMS_OUTPUT.put_line ('NO work Schedule Assigned' );
End;

BEGIN
selectNVL (HRUSERDT.GET_TABLE_VALUE ( 81,
'LAM_HR_WORK_SCHEDULE',
l_user_column_name,---- '5 Days
(Thursday, Friday off)' ,
'MONDAY',
p_date ---to_date('01-jan-1950')
),'0') into l_no_hours_in_Day
from dual;
DBMS_OUTPUT.put_line ('l_no_hours_in_Day ' || l_no_hours_in_Day );
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('No Table value for Work Schedule');
l_no_hours_in_Day:=0;
END;
v_hours := l_no_hours_in_Day;

RETURN v_hours;
END;

--------------------------------------------------------------------------------
-- Get Month Days
--------------------------------------------------------------------------------
FUNCTION get_month_days (p_assignment_id IN NUMBER, p_date IN DATE)
RETURN NUMBER
IS
l_month_days NUMBER;
v_person_type VARCHAR2 (30);
l_off_days NUMBER := 0;
l_START_DATE DATE;
l_END_DATE DATE;
BEGIN

l_START_DATE := to_date('01-'||''||TO_CHAR(p_date,'MON-YYYY'));
l_END_DATE := LAST_DAY (p_date);
-- IF TO_CHAR (p_date, 'DD') <> '20'
-- THEN

l_off_days :=
XXLAM_HR_PAYROLL_ALL.get_total_off_days(p_assignment_id,l_START_DATE,l_END_DATE);
l_month_days := TO_NUMBER (TO_CHAR (LAST_DAY (p_date), 'DD'))- l_off_days;

-- ELSE
-- l_month_days := TO_NUMBER (TO_CHAR (LAST_DAY (p_date - 21), 'DD'));
-- END IF;

v_person_type := get_person_type_ass (p_assignment_id, p_date);

-- IF v_person_type = 'Worker'
-- THEN
-- IF TO_CHAR (p_date, 'DD') <> '25'
-- THEN
-- l_month_days := TO_NUMBER (TO_CHAR (LAST_DAY (p_date - 25), 'DD'));
-- ELSE
-- l_month_days := TO_NUMBER (TO_CHAR (LAST_DAY (p_date - 26), 'DD'));
-- END IF;
-- END IF;
RETURN l_month_days;
EXCEPTION
WHEN OTHERS
THEN
l_month_days := NULL;
END;

--------------------------------------------------------------------------------
-- Get the Ticket allowance to be paid monthly
--------------------------------------------------------------------------------
FUNCTION get_ticket_allowance (
p_business_group_id NUMBER,
p_assignment_id NUMBER,
p_start_date DATE,
p_end_date DATE,
p_accrued_ticket_a OUT NUMBER,
p_accrued_ticket_c OUT NUMBER,
p_accrued_ticket_a_dep OUT NUMBER
)
RETURN NUMBER
AS
l_person_id NUMBER;
l_nod NUMBER := (p_end_date) - (p_start_date);
l_date DATE;
l_days NUMBER;
l_accrual_date DATE;
l_hire_date DATE;
l_age NUMBER;
l_person_type VARCHAR2 (30);
l_family_status VARCHAR2 (10);
l_accrued_amount NUMBER := 0;
l_ticket_class VARCHAR2 (30);
l_dep_ticket_class VARCHAR2 (30);
l_ticket_to VARCHAR2 (30);
l_ticket_adult NUMBER := 0;
l_ticket_child NUMBER := 0;
l_ticket_spouse NUMBER := 0;
l_ticket_a_balance NUMBER := 0;
l_ticket_c_balance NUMBER := 0;
l_no_of_tickets_a NUMBER := 0;
l_no_of_tickets_c NUMBER := 0;
l_absence NUMBER := 0;
l_accrued_ticket_s NUMBER := 0;
l_start_date DATE;
l_end_date DATE;
l_curr_month_amt NUMBER := 0;
l_curr_mon_a NUMBER := 0;
l_curr_mon_c NUMBER := 0;
l_accrued_ticket_ca NUMBER := 0;
l_mth_days NUMBER := 0;

CURSOR c_rel (p_person_id IN NUMBER, p_date IN DATE)

IS
SELECT pcr.person_id, pcr.contact_person_id, pcr.contact_type,
pcr.date_start, pap.date_of_birth,
pcr.cont_attribute2 ticket_eligibility
FROM per_contact_relationships pcr, per_all_people_f pap
WHERE 1 = 1
AND pap.person_id = pcr.contact_person_id
AND pcr.person_id = p_person_id
AND pcr.contact_type IN ('S', 'C')
AND p_date BETWEEN pap.effective_start_date AND pap.effective_end_date
AND p_date BETWEEN NVL (pcr.date_start, pap.start_date)
AND NVL
(pcr.date_end, TO_DATE ('31-DEC-4712'))
--AND pcr.cont_attribute2 = 'Y'
;
BEGIN
l_date := p_end_date;
l_start_date := p_start_date;
l_end_date := p_end_date;
l_mth_days := get_month_days (p_assignment_id, p_start_date);
-- Added 23-11-09
DBMS_OUTPUT.put_line (p_start_date);
DBMS_OUTPUT.put_line (p_end_date);

SELECT DISTINCT person_id

INTO l_person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND l_date BETWEEN effective_start_date AND effective_end_date;

BEGIN
l_person_type :=
xxlam_hr_utilities_pkg.get_person_type ('NAME',
l_person_id,
l_date
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

p_accrued_ticket_a := 0;
p_accrued_ticket_a_dep := 0;
p_accrued_ticket_c := 0;
l_accrued_ticket_s := 0; -- Changed
l_accrued_ticket_ca := 0; -- Changed

FOR i IN 0 .. l_nod
LOOP
l_date := (l_start_date) + i;
l_days := TO_NUMBER (TO_CHAR (LAST_DAY (l_date), 'DD'));
DBMS_OUTPUT.put_line ('DAYS :' || l_days);

BEGIN
-- l_no_of_tickets_a, l_no_of_tickets_c
SELECT pac.segment2 ticket_class, pac.segment3 ticket_to,
pac.segment5 dep_tickets_class
INTO l_ticket_class, l_ticket_to,
l_dep_ticket_class
FROM per_analysis_criteria pac,
fnd_id_flex_structures_vl fflex,
per_person_analyses ppa,
per_all_assignments_f paf
WHERE 1 = 1
AND UPPER (fflex.id_flex_structure_code) IN UPPER
('XXHR_TICKET_DETAILS'
)
AND fflex.id_flex_num = pac.id_flex_num
AND ppa.analysis_criteria_id = pac.analysis_criteria_id
AND paf.person_id = ppa.person_id
AND l_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND l_date BETWEEN NVL (ppa.date_from, TRUNC (l_date))
AND NVL (date_to, TRUNC (l_date))
AND paf.assignment_id = p_assignment_id;

fnd_file.put_line (fnd_file.LOG,
'Date:'
|| p_end_date
|| ' Class:'
|| l_ticket_class
|| ' Dest:'
|| l_ticket_to
|| 'No of Ticket'
|| l_no_of_tickets_a
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

IF l_dep_ticket_class = 'NA'
THEN
l_dep_ticket_class := l_ticket_class;
END IF;

DBMS_OUTPUT.put_line ('L_TICKET_ADULT' || l_ticket_adult);

BEGIN
l_ticket_child :=
NVL (hruserdt.get_table_value (p_business_group_id,
' HR Ticket Sector',
'CHILD-' || l_dep_ticket_class,
l_ticket_to,
l_date
),
0
);
EXCEPTION
WHEN OTHERS
THEN
l_ticket_child := 0;
DBMS_OUTPUT.put_line ('Child Adult:' || SQLERRM);
END;
BEGIN
l_ticket_spouse :=
NVL (hruserdt.get_table_value (p_business_group_id,
'HR Ticket Sector',
'ADULT-' || l_dep_ticket_class,
l_ticket_to,
l_date
),
0
);
fnd_file.put_line (fnd_file.LOG,
'Ticket Spouse:' || l_ticket_spouse
);
EXCEPTION
WHEN OTHERS
THEN
l_ticket_spouse := 0;
DBMS_OUTPUT.put_line ('Spouse Adult:' || SQLERRM);
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END;

DBMS_OUTPUT.put_line ('L_TICKET_CHILD' || l_ticket_child);

-- Checking Family Status

-- IF Family Status is no then only one ticket for adult

IF l_family_status = 'N'
THEN
p_accrued_ticket_a := p_accrued_ticket_a + NVL (1, 0);
ELSE
-- For Employee
p_accrued_ticket_a := p_accrued_ticket_a + 1;

FOR rec_rel IN c_rel (l_person_id, l_date)

LOOP
BEGIN
-- For Spouse
IF rec_rel.contact_type = 'S'
AND rec_rel.ticket_eligibility = 'Y'
THEN
--p_accrued_ticket_s := p_accrued_ticket_s + 1;
l_accrued_ticket_s := l_accrued_ticket_s + 1; -- Changed
END IF;

-- For Child
IF rec_rel.contact_type = 'C'
AND rec_rel.ticket_eligibility = 'Y'
THEN
SELECT MONTHS_BETWEEN (l_date, rec_rel.date_of_birth)
/ 12
INTO l_age
FROM DUAL;

IF l_age >= 0
AND l_age <= 12
AND rec_rel.ticket_eligibility = 'Y'
THEN
p_accrued_ticket_c := p_accrued_ticket_c + NVL (1, 0);
ELSIF l_age > 12 AND rec_rel.ticket_eligibility = 'Y'
THEN
l_accrued_ticket_ca := l_accrued_ticket_ca + 1;
-- Changed
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No Data Found');
NULL;
END;
END LOOP;
END IF;
END LOOP;

-- Changed
IF l_ticket_spouse = 0
THEN
l_accrued_ticket_s := 0;
l_accrued_ticket_ca := 0;
END IF;

IF l_ticket_child = 0
THEN
p_accrued_ticket_c := 0;
END IF;

IF l_ticket_adult = 0
THEN
p_accrued_ticket_a := 0;
END IF;

p_accrued_ticket_a_dep := l_accrued_ticket_s + l_accrued_ticket_ca;

IF UPPER (l_person_type) = 'STAFF'

THEN
DBMS_OUTPUT.put_line ('P_ACCRUED_TICKET_A_1' || p_accrued_ticket_a);
DBMS_OUTPUT.put_line ('P_ACCRUED_TICKET_C_1' || p_accrued_ticket_c);
p_accrued_ticket_a := p_accrued_ticket_a / l_mth_days * 30 / 360;
p_accrued_ticket_c := p_accrued_ticket_c / l_mth_days * 30 / 360;
p_accrued_ticket_a_dep :=
p_accrued_ticket_a_dep / l_mth_days * 30 / 360;
--l_accrued_ticket_ca := l_accrued_ticket_ca / 365; -- Changed
--l_accrued_ticket_s := l_accrued_ticket_s / 365; -- Changed
DBMS_OUTPUT.put_line ('P_ACCRUED_TICKET_A' || p_accrued_ticket_a);
DBMS_OUTPUT.put_line ('P_ACCRUED_TICKET_C' || p_accrued_ticket_c);
ELSE
p_accrued_ticket_a := p_accrued_ticket_a / l_mth_days * 30 / 720;
--(p_accrued_ticket_a + l_accrued_ticket_s) / 730;
l_accrued_ticket_s := 0; -- Changed
p_accrued_ticket_c := 0;
l_accrued_ticket_ca := 0; -- Changed
p_accrued_ticket_a_dep := 0;
END IF;

DBMS_OUTPUT.put_line ('P_ACCRUED_TICKET_A' || p_accrued_ticket_a);

--(l_ticket_spouse * l_accrued_ticket_ca) + -- Changed

(l_ticket_child * p_accrued_ticket_c
);
l_accrued_amount := l_curr_month_amt;
fnd_file.put_line (fnd_file.LOG,
'L_ACCRUED_AMOUNT: Current: ' || l_curr_month_amt
);
DBMS_OUTPUT.put_line ('L_ACCRUED_AMOUNT' || l_accrued_amount);
fnd_file.put_line (fnd_file.LOG,
'L_ACCRUED_AMOUNT: Total: ' || l_accrued_amount
);
p_accrued_ticket_a := p_accrued_ticket_a;
--p_accrued_ticket_a_dep := l_accrued_ticket_s + l_accrued_ticket_ca;
p_accrued_ticket_c := p_accrued_ticket_c;
l_accrued_amount := ROUND (NVL (l_accrued_amount, 0), 2);
RETURN l_accrued_amount;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;

--------------------------------------------------------------------------------
-- Skip Regular Hours Yes or No
--------------------------------------------------------------------------------
FUNCTION xxpay_skip_reg_hours_y_n (
p_element_entry_id IN NUMBER,
p_e_date IN DATE
)
RETURN VARCHAR2
IS
v_date_earned DATE;
v_day VARCHAR2 (30);
v_shift VARCHAR2 (500);
v_emp_no VARCHAR2 (50);
v_skip VARCHAR2 (10) := 'N';
v_start_day VARCHAR2 (30);
v_person_id NUMBER;
v_week_start_date DATE;

CURSOR cur_rec
IS
SELECT *
FROM pay_element_entries_f peef
WHERE peef.element_entry_id =
NVL (p_element_entry_id, peef.element_entry_id)
AND NVL (p_e_date, SYSDATE) BETWEEN effective_start_date
AND effective_end_date;
BEGIN
DBMS_OUTPUT.put_line ('begin');
v_date_earned := hr_general.start_of_time;
v_shift := NULL;
v_day := NULL;
v_skip := 'N';
v_start_day := NULL;
v_week_start_date := NULL;

FOR v_rec IN cur_rec

LOOP
DBMS_OUTPUT.put_line ('begin loop');
v_date_earned :=
xxlam_hr_payroll_all.get_otl_reg_hrs_date_earned
(v_rec.element_entry_id);
DBMS_OUTPUT.put_line ('date earned:' || v_date_earned);

BEGIN
SELECT TO_CHAR (p_e_date, 'DAY')
INTO v_day
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

DBMS_OUTPUT.put_line ('day:' || v_day);

IF v_day = 'THURSDAY'
THEN
NULL;
ELSE
/* commented by khurram
v_shift :=
xxotl_utilities.XX_SHIFT_SCHEDULE_FUNCTION (
v_rec.assignment_id,
v_date_earned
);
*/
BEGIN
v_person_id := 0;
v_start_day := NULL;

BEGIN
SELECT person_id
INTO v_person_id
FROM per_all_assignments_f
WHERE v_date_earned BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
v_person_id := -1;
END;

SELECT TRIM (TO_CHAR (hrp.start_date, 'DAY'))

INTO v_start_day
FROM (SELECT MAX (hrrp.rule_evaluation_order) OVER (PARTITION BY
person_id)
current_prec,
hrrp.*
FROM hxc_resource_rules_person_v hrrp
WHERE 1 = 1
AND TRUNC (v_date_earned)
BETWEEN hrrp.effective_start_date
AND hrrp.effective_end_date) qry,
hxc_pref_hierarchies_v hrchy,
hxc_recurring_periods hrp
WHERE 1 = 1
AND qry.current_prec = qry.rule_evaluation_order
AND qry.pref_hierarchy_id = hrchy.parent_pref_hierarchy_id
AND hrchy.pref_hierarchy =
'Self Service Timecard Period for Worker'
AND hrchy.attribute1 = hrp.recurring_period_id
AND person_id = v_person_id;
EXCEPTION
WHEN OTHERS
THEN
v_start_day := 'FRIDAY';
END;

BEGIN
v_week_start_date := NEXT_DAY (p_e_date - 7, v_start_day);
EXCEPTION
WHEN OTHERS
THEN
v_week_start_date := p_e_date - 5;
END;

DBMS_OUTPUT.put_line ('v_shift:' || v_shift);

IF (UPPER (v_shift) LIKE '%OFF' OR v_shift = 'NH')

AND (v_date_earned BETWEEN v_week_start_date AND p_e_date)
THEN
v_skip := 'Y';
DBMS_OUTPUT.put_line ('V_skip in the if:' || v_skip);
END IF;

DBMS_OUTPUT.put_line ('V_skip:' || v_skip);

END IF;
END LOOP;

DBMS_OUTPUT.put_line ('V_skip after loop :' || v_skip);

RETURN v_skip;
END;

--------------------------------------------------------------------------------
-- Function to return the advance salary value based on the repective day
--------------------------------------------------------------------------------
FUNCTION calculate_advance_vac_salary (
p_element_entry_id IN NUMBER,
p_payroll_id IN NUMBER,
p_date IN DATE
)
RETURN NUMBER
IS
v_leave VARCHAR2 (100);
v_leave_days NUMBER;
v_days NUMBER;
v_cur_date DATE;
v_cur_salary NUMBER;
v_cur_gross NUMBER;
v_sal_day NUMBER;
v_days_in_month NUMBER;
v_totl_sal NUMBER;

CURSOR cur_rec
IS
SELECT peef.assignment_id, paa.person_id, paa.date_start,
paa.date_end, paa.absence_days days, paa.business_group_id
FROM per_absence_attendances paa, pay_element_entries_f peef
WHERE 1 = 1
AND paa.absence_attendance_id = peef.creator_id
AND peef.element_entry_id = p_element_entry_id
AND p_date BETWEEN peef.effective_start_date
AND peef.effective_end_date;
BEGIN
FOR v_rec IN cur_rec
LOOP
v_leave := NULL;
v_days := 0;
v_cur_date := NULL;
v_cur_salary := 0;
v_cur_gross := 0;
v_sal_day := 0;
v_days_in_month := 0;
v_totl_sal := 0;
v_leave_days := 0;
v_days := (v_rec.date_end - v_rec.date_start);
DBMS_OUTPUT.put_line ('v_days = ' || v_days);

FOR i IN 0 .. v_days
LOOP
v_leave_days := 0;
v_cur_date := NULL;
v_cur_salary := 0;
v_cur_gross := 0;
v_sal_day := 0;
v_days_in_month := 0;
v_cur_date := v_rec.date_start + i;
DBMS_OUTPUT.put_line ('v_cur_date := ' || v_cur_date);
v_leave_days :=
xxlam_hr_absence_duration.get_no_of_leaves_taken_adv_sal
(v_rec.business_group_id,
v_rec.assignment_id,
'Annual Vacation With Advance',
v_cur_date,
v_cur_date
);
DBMS_OUTPUT.put_line ('v_leave_days = ' || v_leave_days);

IF v_leave_days > 0
THEN
v_cur_gross := get_gross_at (v_rec.assignment_id, v_cur_date);
DBMS_OUTPUT.put_line ('v_cur_gross = ' || v_cur_gross);

BEGIN
SELECT (end_date - start_date) + 1
INTO v_days_in_month
FROM per_time_periods
WHERE payroll_id = p_payroll_id
AND v_cur_date BETWEEN start_date AND end_date;
EXCEPTION
WHEN OTHERS
THEN
v_days_in_month := 30;
END;

v_sal_day := v_cur_gross / v_days_in_month;

DBMS_OUTPUT.put_line ('v_sal_day = ' || v_sal_day);
v_cur_salary := v_sal_day * v_leave_days;
DBMS_OUTPUT.put_line ('v_cur_salary = ' || v_cur_salary);
v_totl_sal := v_totl_sal + v_cur_salary;
DBMS_OUTPUT.put_line ('v_totl_sal = ' || v_totl_sal);
END IF;
END LOOP;
END LOOP;

RETURN ROUND (v_totl_sal, 2);

EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;

FUNCTION get_vacation_pay (
p_employee_number IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_monthly_value OUT NUMBER
)
RETURN NUMBER
IS
ld_period_start_date DATE := TO_DATE ('21-JUN-2010');
ld_period_end_date DATE := TO_DATE ('20-JUL-2010');
l_start_date DATE;
l_end_date DATE;
ln_no_of_days NUMBER (10);
ln_assignment_id NUMBER (10);
ln_sal_per_day NUMBER (10, 2);
ln_vac_sal NUMBER (10, 2);

CURSOR get_absence_days
IS
SELECT paa.*, paat.NAME
FROM per_absence_attendances paa,
per_absence_attendance_types paat
WHERE 1 = 1
AND paa.absence_attendance_type_id =
paat.absence_attendance_type_id
AND paa.person_id =
(SELECT person_id
FROM per_all_people_f
WHERE 1 = 1
AND employee_number = p_employee_number
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
AND ( (paa.date_start BETWEEN ld_period_start_date
AND ld_period_end_date
)
OR (paa.date_end BETWEEN ld_period_start_date
AND ld_period_end_date
)
);
BEGIN
FOR c1 IN get_absence_days
LOOP
IF UPPER (c1.NAME) LIKE 'ANNUAL VACATION'
THEN
SELECT assignment_id
INTO ln_assignment_id
FROM per_all_assignments_f
WHERE 1 = 1
AND person_id = c1.person_id
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date
AND assignment_type = 'E'
AND primary_flag = 'Y';

IF c1.date_start > ld_period_start_date

THEN
l_start_date := c1.date_start;
ELSE
l_start_date := ld_period_start_date;
END IF;

IF c1.date_end > ld_period_end_date

THEN
l_end_date := ld_period_end_date;
ELSE
l_end_date := c1.date_end;
END IF;

--DBMS_OUTPUT.put_line ('start Date:' || l_start_date);

--DBMS_OUTPUT.put_line ('end Date:' || l_end_date);
ln_no_of_days := l_end_date - l_start_date + 1;
--DBMS_OUTPUT.put_line ('ln_no_of_days:' || ln_no_of_days);
ln_sal_per_day := 0;
ln_vac_sal := 0;

FOR i IN 1 .. ln_no_of_days
LOOP
--ln_vac_sal:=ln_sal_per_day;
--DBMS_OUTPUT.put_line ('ln_sal_per_day:' || ln_sal_per_day );
--DBMS_OUTPUT.put_line ('ln_vac_sal1:' || ln_vac_sal ) ;
--DBMS_OUTPUT.put_line ('each Date:' || to_char(l_start_date) ||'
'||i );
ln_sal_per_day :=
xxlam_hr_payroll_all.get_gross_at (ln_assignment_id,
l_start_date
)
/ ((ld_period_end_date - ld_period_start_date) + 1);
l_start_date := l_start_date + 1;
ln_vac_sal := ln_vac_sal + ln_sal_per_day;
--DBMS_OUTPUT.put_line ('ln_vac_sal2:' || ln_vac_sal );
--DBMS_OUTPUT.put_line ('each Date:' || l_start_date + 1);
END LOOP;
ELSE
ln_vac_sal := 0;
END IF;

DBMS_OUTPUT.put_line ('ln_vac_sal3:' || ln_vac_sal);

END LOOP;

p_monthly_value := ln_vac_sal;
RETURN ln_vac_sal;
END;

FUNCTION get_los_sal_chg ( --p_business_group_id IN number,

p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_length_of_service OUT NUMBER,
p_basic_salary OUT NUMBER
)
RETURN NUMBER
IS
l_sal_chg_flag NUMBER (10, 2);
l_current_sal NUMBER (10);
l_prev_sal NUMBER (10);
--p_assignment_id number (10);
p_business_group_id NUMBER (10) := 81;
l_hire_date DATE;
l_los NUMBER (10, 2);
--p_start_date date;
l_unpaid_leave NUMBER (10);
BEGIN
l_current_sal :=
xxlam_hr_payroll_all.get_balance (p_assignment_id,
p_business_group_id,
'Paid Basic Salary',
'RUN',
p_end_date
);
BEGIN
-- SELECT (p_end_date - start_date) / 365
-- INTO l_los
-- FROM per_all_people_f
-- WHERE 1 = 1
-- AND person_id =
-- (SELECT person_id
-- FROM per_all_assignments_f
-- WHERE 1 = 1
-- AND assignment_id = p_assignment_id
-- AND primary_flag = 'Y'
-- AND p_end_date BETWEEN
effective_start_date
-- AND
effective_end_date)
-- AND p_end_date BETWEEN effective_start_date
-- AND effective_end_date;
SELECT (TRUNC (p_end_date) - TRUNC (MAX (date_start))) / 365
INTO l_los ----p_end_date -
FROM per_periods_of_service
WHERE 1 = 1
AND person_id =
(SELECT person_id
FROM per_all_assignments_f
WHERE 1 = 1
AND assignment_id = p_assignment_id
AND primary_flag = 'Y'
AND p_end_date BETWEEN effective_start_date
AND effective_end_date);
EXCEPTION
WHEN OTHERS
THEN
l_los := 0;
p_length_of_service := 0;
END;

DBMS_OUTPUT.put_line (' initial l_los = ' || l_los);

----l_los := ROUND (l_los - l_unpaid_leave, 2);
l_los := ROUND (l_los, 2);
p_length_of_service := l_los;
DBMS_OUTPUT.put_line ('l_sal_chg_flag khur' || l_sal_chg_flag);
--- DBMS_OUTPUT.PUT_LINE ('p_basic_salary' || p_basic_salary);
DBMS_OUTPUT.put_line ('after unpaid l_los = ' || l_los);

p_basic_salary :=
xxlam_hr_payroll_all.get_balance (p_assignment_id,
p_business_group_id,
'Monthly Basic',
'RUN',
p_end_date
);
p_length_of_service := l_los;
l_sal_chg_flag := l_los;
RETURN l_sal_chg_flag;
EXCEPTION
WHEN OTHERS
THEN
p_basic_salary := 0;
p_length_of_service := 0;
RETURN l_sal_chg_flag;
END get_los_sal_chg;
FUNCTION get_flag_adj_grat_accruals (
p_assignment_id IN NUMBER,
p_period_start_date IN DATE,
p_period_end_date IN DATE,
p_amount IN VARCHAR2
)
RETURN VARCHAR2
IS
ln_assignment_id NUMBER (10);
l_period_start_date DATE;
l_period_end_date DATE;
ln_element_type_id NUMBER (10);
ln_element_link_id NUMBER (10);
ln_payroll_id NUMBER (10);
ln_business_group_id NUMBER (10);
ln_amount NUMBER (10);
ln_element_rpt_name VARCHAR2 (100)
:= 'Gratuity Accrual Adjustment';
ln_input_value_id NUMBER (10);
v_start_date DATE;
v_end_date DATE;
v_warning BOOLEAN;
v_element_entry_id NUMBER;
v_object_version_number NUMBER;
BEGIN
ln_assignment_id := p_assignment_id;
l_period_start_date := p_period_start_date;
l_period_end_date := p_period_end_date;
ln_amount := p_amount;

BEGIN
SELECT payroll_id, business_group_id
INTO ln_payroll_id, ln_business_group_id
FROM per_all_assignments_f
WHERE 1 = 1
AND assignment_id = ln_assignment_id
AND primary_flag = 'Y'
AND assignment_type = 'E'
AND TRUNC (l_period_end_date) BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
ln_payroll_id := 0;
END;

BEGIN
SELECT petf.element_type_id, pelf.element_link_id, input_value_id
INTO ln_element_type_id, ln_element_link_id, ln_input_value_id
FROM pay_element_types_f petf,
pay_element_links_f pelf,
pay_input_values_f pivf
WHERE 1 = 1
AND petf.element_type_id = pelf.element_type_id
AND petf.element_type_id = pivf.element_type_id
AND payroll_id = ln_payroll_id
AND TRIM (UPPER (petf.element_name)) LIKE
TRIM (UPPER (ln_element_rpt_name))
AND TRUNC (l_period_end_date) BETWEEN pivf.effective_start_date
AND pivf.effective_end_date
AND TRUNC (l_period_end_date) BETWEEN petf.effective_start_date
AND petf.effective_end_date
AND TRUNC (l_period_end_date) BETWEEN pelf.effective_start_date
AND pelf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
ln_element_type_id := 0;
ln_element_link_id := 0;
END;

-- DBMS_OUTPUT.PUT_LINE ('ln_element_type_id = ' || ln_element_type_id);

-- DBMS_OUTPUT.PUT_LINE ('ln_element_link_id = ' || ln_element_link_id);
-- DBMS_OUTPUT.PUT_LINE ('ln_payroll_id = ' || ln_payroll_id);
pay_element_entry_api.create_element_entry
(
--p_validate => 'FALSE',
p_effective_date => TO_DATE

(p_period_end_date
)
+ 2,
p_business_group_id =>
ln_business_group_id,
p_assignment_id => ln_assignment_id,
p_element_link_id => ln_element_link_id,
p_entry_type => 'E',
p_cost_allocation_keyflex_id => NULL,
p_date_earned => TO_DATE

(p_period_end_date
)
+ 2,
p_input_value_id1 => ln_input_value_id,
p_entry_value1 => TO_CHAR
(ln_amount),
p_entry_information27 => 'SALARY CHG ADJ
GRAT',
p_effective_start_date => v_start_date,
p_effective_end_date => v_end_date,
p_element_entry_id => v_element_entry_id,
p_object_version_number =>
v_object_version_number,
p_create_warning => v_warning
);
RETURN 'Y';
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error message = ' || SQLERRM);
RETURN 'N';
END get_flag_adj_grat_accruals;

FUNCTION get_month_unpaid_days (
p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
)
RETURN NUMBER
IS
p_business_group_id NUMBER (10) := 81;
l_unpaid_leave NUMBER (10);
BEGIN
l_unpaid_leave :=
xxlam_hr_absence_duration.get_no_of_leaves_taken_ass_id
(p_business_group_id,
p_assignment_id,
'Unpaid Leave',
p_start_date,
p_end_date
);
RETURN l_unpaid_leave;
EXCEPTION
WHEN OTHERS
THEN
l_unpaid_leave := 0;
RETURN l_unpaid_leave;
END get_month_unpaid_days;

FUNCTION get_grat_accrual_adj ( --p_business_group_id IN number,

p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
)
RETURN NUMBER
IS
-- p_assignment_id number (10) := 1733;
-- p_start_date date := TO_DATE ('21-AUG-2011');
-- p_end_date date := TO_DATE ('20-SEP-2011');
-- p_value varchar2 (10);
l_employee_number VARCHAR2 (10);
l_value VARCHAR2 (10);
BEGIN
BEGIN
SELECT employee_number
INTO l_employee_number
FROM xxlam_hr_emp_data_all
WHERE 1 = 1 AND assignment_id = p_assignment_id;
EXCEPTION
WHEN OTHERS
THEN
l_employee_number := 'N';
END;

DBMS_OUTPUT.put_line ('l_employee_number: ' || l_employee_number);

IF l_employee_number != 'N'
THEN
BEGIN
SELECT meaning
INTO l_value
FROM hr_lookups
WHERE 1 = 1
AND lookup_type = 'XXHR_GRAT_ADJUSTMENT'
AND start_date_active = p_start_date
AND end_date_active = p_end_date
AND TRUNC (lookup_code) = TRUNC (l_employee_number);

l_value := TO_NUMBER (l_value);

EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_value := 0;
END;
ELSE
l_value := 0;
END IF;

RETURN l_value;
DBMS_OUTPUT.put_line ('l_value: ' || l_value);
END get_grat_accrual_adj;

FUNCTION get_final_stlmt_adj_amt (p_assignment_id IN NUMBER)

RETURN NUMBER
IS
l_assignment_number NUMBER;
l_termination_date DATE;
l_gross_salary NUMBER := 0;
l_hire_date DATE;
l_no_of_days NUMBER := 0;
l_no_of_days_in_month NUMBER := 0;
l_adj_amt NUMBER := 0;
l_payroll_name VARCHAR2 (50);
l_fs_adj_date DATE;
l_person_type VARCHAR2 (100);
/* BEGIN
-- Hire date,Termination
Date
BEGIN
SELECT paaf.assignment_number,
pos.actual_termination_date,
papf.original_date_of_hire,
PAP.PAYROLL_NAME,
XXLAM_HR_UTILITIES_PKG.get_person_type_with_asg_id (
paaf.assignment_id,
papf.ORIGINAL_DATE_OF_HIRE
)
INTO l_assignment_number,
l_termination_date,
l_hire_date,
l_PAYROLL_NAME,
l_person_type
FROM per_all_assignments_f PAAF,
PER_PERIODS_OF_SERVICE POS,
per_all_people_f papf,
hr_soft_coding_keyflex hsc,
PAY_ALL_PAYROLLS_F PAP
WHERE paaf.assignment_id = p_assignment_id
AND pos.business_group_id = paaf.business_group_id
AND pos.person_id = paaf.person_id
AND actual_termination_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND actual_termination_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.person_id = paaf.person_id
AND papf.business_group_id = paaf.business_group_id
AND PAP.PAYROLL_ID = PAAF.PAYROLL_ID
AND paaf.soft_coding_keyflex_id =
hsc.soft_coding_keyflex_id(+);
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
BEGIN
SELECT START_DATE_ACTIVE
INTO l_fs_adj_date
FROM hr_lookups
WHERE 1 = 1
AND lookup_type = 'XXHR_FS_COMPANY_START_DATE'
AND ENABLED_FLAG = 'Y'
AND MEANING = l_PAYROLL_NAME;
EXCEPTION
WHEN OTHERS
THEN
--- l_fs_adj_date := to_date('21-NOV-2009');
RETURN 0;
END;
DBMS_OUTPUT.PUT_LINE ('PAYROLL = ' || l_PAYROLL_NAME);
DBMS_OUTPUT.PUT_LINE ('l_fs_adj_date = ' || l_fs_adj_date);
IF l_termination_date IS NOT NULL AND l_hire_date < l_fs_adj_date ---- '21-
Nov-2009'
THEN
-- Gross Salary
BEGIN
SELECT proposed_salary_n
INTO l_gross_salary
FROM per_pay_proposals
WHERE assignment_id = p_assignment_id
AND l_termination_date BETWEEN change_date AND date_to;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
l_no_of_days_in_month := SUBSTR (LAST_DAY (l_hire_date), 1, 2);
-- No. of days as of joining date
IF UPPER (l_person_type) = 'STAFF'
THEN
IF SUBSTR (l_hire_date, 1, 2) >= 21
THEN
l_no_of_days :=
SUBSTR (LAST_DAY (l_hire_date), 1, 2)
- SUBSTR (l_hire_date, 1, 2)
+ 1;
ELSE
-- getting the end date of the month.
l_no_of_days := SUBSTR (LAST_DAY (l_hire_date), 1, 2) - 20;
--21ST OF THE MONTH
END IF;
ELSE
IF SUBSTR (l_hire_date, 1, 2) >= 25
THEN
l_no_of_days :=
SUBSTR (LAST_DAY (l_hire_date), 1, 2)
- SUBSTR (l_hire_date, 1, 2)
+ 1;
ELSE
-- getting the end date of the month.
l_no_of_days := SUBSTR (LAST_DAY (l_hire_date), 1, 2) - 20;
--21ST OF THE MONTH
END IF;
END IF;
l_adj_amt := (l_gross_salary / l_no_of_days_in_month) * l_no_of_days;
END IF;
RETURN ROUND (l_adj_amt, 2);
END get_final_stlmt_adj_amt;*/
BEGIN
-- Hire date,Termination Date
BEGIN
SELECT paaf.assignment_number, pos.actual_termination_date,
papf.original_date_of_hire, pap.payroll_name,
xxlam_hr_utilities_pkg.get_person_type_with_asg_id
(paaf.assignment_id,
papf.original_date_of_hire
)
INTO l_assignment_number, l_termination_date,
l_hire_date, l_payroll_name,
l_person_type
FROM per_all_assignments_f paaf,
per_periods_of_service pos,
per_all_people_f papf,
hr_soft_coding_keyflex hsc,
pay_all_payrolls_f pap
WHERE paaf.assignment_id = p_assignment_id
AND pos.business_group_id = paaf.business_group_id
AND pos.person_id = paaf.person_id
AND actual_termination_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND actual_termination_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.person_id = paaf.person_id
AND papf.business_group_id = paaf.business_group_id
AND pap.payroll_id = paaf.payroll_id
AND paaf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id(+);
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
--NULL;
END;

BEGIN
SELECT start_date_active
INTO l_fs_adj_date
FROM hr_lookups
WHERE 1 = 1
AND lookup_type = 'XXHR_FS_COMPANY_START_DATE'
AND enabled_flag = 'Y'
AND meaning = l_payroll_name;
EXCEPTION
WHEN OTHERS
THEN
l_fs_adj_date := TO_DATE ('21-NOV-2013');
RETURN 0;
--NULL;
END;

DBMS_OUTPUT.put_line ('PAYROLL = ' || l_payroll_name);

DBMS_OUTPUT.put_line ('l_fs_adj_date = ' || l_fs_adj_date);

IF l_termination_date IS NOT NULL AND l_hire_date < l_fs_adj_date

---- '21-Nov-2009'
THEN
-- Gross Salary
BEGIN
SELECT proposed_salary_n
INTO l_gross_salary
FROM per_pay_proposals
WHERE assignment_id = p_assignment_id
AND l_termination_date BETWEEN change_date AND date_to;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
--NULL;
END;

l_no_of_days_in_month := SUBSTR (LAST_DAY (l_fs_adj_date), 1, 2);

-- No. of days as of joining date
DBMS_OUTPUT.put_line ( 'l_no_of_days_in_month... = '
|| l_fs_adj_date
|| '~'
|| SUBSTR (LAST_DAY (l_fs_adj_date), 1, 2)
);

IF l_hire_date BETWEEN l_fs_adj_date AND LAST_DAY (l_fs_adj_date)

THEN
l_no_of_days :=
SUBSTR (LAST_DAY (l_fs_adj_date), 1, 2)
- SUBSTR (l_hire_date, 1, 2)
+ 1;
DBMS_OUTPUT.put_line ('l_no_of_days... = ' || l_no_of_days);
ELSE
IF UPPER (l_person_type) = 'STAFF'
THEN
-- getting the end date of the month.
l_no_of_days := SUBSTR (LAST_DAY (l_fs_adj_date), 1, 2) - 20;
ELSE
l_no_of_days := SUBSTR (LAST_DAY (l_fs_adj_date), 1, 2) - 25;
END IF;
--21ST OF THE MONTH
END IF;

l_no_of_days := SUBSTR (LAST_DAY (l_fs_adj_date), 1, 2) - 20;

DBMS_OUTPUT.put_line ('l_adj_amt .... in if loop= ' || l_no_of_days);
l_adj_amt := (l_gross_salary / l_no_of_days_in_month) * l_no_of_days;
END IF;

RETURN ROUND (l_adj_amt, 2);

DBMS_OUTPUT.put_line ('l_adj_amt = ' || l_adj_amt);
END get_final_stlmt_adj_amt;

FUNCTION calculate_food_all (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_month_value OUT NUMBER
)
RETURN NUMBER
IS
v_salary NUMBER := 0;
v_gross NUMBER := 0;
v_value NUMBER := 0;
v_days NUMBER := (p_end_date - p_start_date);
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_hrs_per_day NUMBER := 0;
v_otl_hrs NUMBER := 0;
v_curr_otl_hrs NUMBER := 0;
v_week_hrs NUMBER := 0;
v_curr_week_hrs NUMBER := 0;
v_holiday NUMBER := 0;
v_curr_holiday NUMBER := 0;
v_week_holi_pay NUMBER := 0;
v_skip_holiday NUMBER := 0;
v_employee_number NUMBER := 0;
v_business_group_id NUMBER := 81;
v_curr_sick NUMBER := 0;
v_total_sick_days NUMBER := 0;
v_holiday_flag VARCHAR2 (10);
v_leave_flag VARCHAR2 (10);
v_payroll_id NUMBER (10);
BEGIN
v_mth_days :=
xxlam_hr_payroll_all.get_month_days (p_assignment_id, p_start_date);
p_month_value := 0;

FOR i IN 0 .. v_days
LOOP
v_date := p_start_date + i;
v_curr_otl_hrs := 0;
v_curr_week_hrs := 0;
v_curr_holiday := 0;

BEGIN
v_mth_value :=
xxlam_hr_payroll_all.get_screen_entry_value_at
(p_assignment_id,
p_element_entry_id,
'Monthly Value',
v_date
);
END;

-- BEGIN
-- v_gross := get_gross_at (p_assignment_id, v_date);
-- END;
p_month_value := v_mth_value;
-- IF v_mth_value = 0
-- THEN
-- p_pay_value := v_mth_value;
-- END IF;
DBMS_OUTPUT.put_line ('p_month_value = ' || p_month_value);

BEGIN
v_hrs_per_day :=
xxlam_hr_payroll_all.xxotl_hours_day (p_assignment_id, v_date);
END;

BEGIN
v_curr_otl_hrs :=
xxlam_hr_payroll_all.get_otl_regular_hours (p_assignment_id,
v_date
);
END;

--For sick leave

IF v_curr_sick = 0
THEN
-- v_curr_sick :=
XXLAM_HR_ABSENCE_DURATION.get_parttime_absence (p_assignment_id,'Parttime Sick
Leave',v_date)/v_hrs_per_day;
v_curr_sick :=
xxlam_hr_absence_duration.get_parttime_absence
(p_assignment_id,
'Parttime Sick Leave',
v_date
);
END IF;

IF v_curr_sick > 0
THEN
IF v_total_sick_days <= 15
THEN
v_curr_otl_hrs := 8;
ELSE
IF v_total_sick_days > 15 AND v_total_sick_days <= 45
THEN
v_curr_otl_hrs := 8 * 0.5;
ELSIF v_total_sick_days > 45 AND v_total_sick_days <= 90
THEN
v_curr_otl_hrs := 0;
END IF;
END IF;
END IF;

SELECT payroll_id
INTO v_payroll_id
FROM per_all_assignments_f
WHERE 1 = 1
AND v_date BETWEEN effective_start_date AND effective_end_date
AND assignment_id = p_assignment_id;

IF v_curr_sick > 0 AND v_payroll_id = 209

THEN
v_curr_otl_hrs := 0;
END IF;

--To check whether the day is holiay or not

/* commentd by khurram
v_holiday_flag :=

xxotl_utilities.XXOTL_GET_HOLIDAY_YES_NO (p_assignment_id,
v_date); */

-- To check whether the day is leave or not

BEGIN
SELECT 'Y'
INTO v_leave_flag
FROM per_absence_attendances
WHERE 1 = 1
AND person_id =
(SELECT person_id
FROM per_all_assignments_f
WHERE 1 = 1
AND v_date BETWEEN effective_start_date
AND effective_end_date
AND assignment_id = p_assignment_id)
AND v_date BETWEEN date_start AND date_end;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_leave_flag := 'N';
WHEN TOO_MANY_ROWS
THEN
v_leave_flag := 'Y';
WHEN OTHERS
THEN
v_leave_flag := 'N';
END;
IF v_holiday_flag = 'Y' AND v_leave_flag = 'Y'
THEN
v_curr_otl_hrs := 0;
END IF;

v_otl_hrs := v_otl_hrs + v_curr_otl_hrs;

v_sal_per_day :=
v_sal_per_day
+ (p_month_value / v_mth_days / v_hrs_per_day) * v_curr_otl_hrs;
DBMS_OUTPUT.put_line ('v_sal_per_day = ' || v_sal_per_day);

BEGIN
v_curr_week_hrs :=
xxlam_hr_payroll_all.xxotl_get_week_off_pay (p_assignment_id,
v_date,
v_date
);
END;

IF v_curr_week_hrs <> 0
THEN
v_week_hrs := v_week_hrs + v_curr_week_hrs;
v_week_holi_pay :=
v_week_holi_pay
+ (p_month_value / v_mth_days) * v_curr_week_hrs;
END IF;

BEGIN
v_curr_holiday :=
xxlam_hr_absence_duration.holidays (p_assignment_id,
v_date,
v_date
);
END;

IF v_curr_holiday <> 0 AND v_leave_flag = 'N'

THEN
v_holiday := v_holiday + v_curr_holiday;
v_week_holi_pay :=
v_week_holi_pay
+ (p_month_value / v_mth_days) * v_curr_holiday;
ELSIF v_curr_holiday <> 0 AND v_leave_flag = 'Y'
THEN
v_curr_holiday := 0;
v_holiday := v_holiday + v_curr_holiday;
v_week_holi_pay :=
v_week_holi_pay
+ (p_month_value / v_mth_days) * v_curr_holiday;
END IF;

-- BEGIN
-- SELECT employee_number
-- INTO v_employee_number
-- FROM xxhr_emp_data_all
-- WHERE 1 = 1 AND assignment_id = p_assignment_id;
-- EXCEPTION
-- WHEN OTHERS
-- THEN
-- v_employee_number := 0;
-- END;

-- v_skip_holiday :=
-- XXLAM_HR_ABSENCE_DURATION.get_holiday_unpaid_leaves (
-- v_employee_number,
-- v_business_group_id,
-- v_date
-- );

-- IF v_skip_holiday <> 0
-- THEN
-- v_otl_hrs := 0;
-- END IF;
DBMS_OUTPUT.put_line ( v_sal_per_day
|| 'Week:'
|| v_week_hrs
|| 'OTL:'
|| v_otl_hrs
|| 'Holiday:'
|| v_holiday
);
END LOOP;

v_sal_per_day := v_sal_per_day + v_week_holi_pay;

v_value := ROUND (v_sal_per_day, 2);
DBMS_OUTPUT.put_line ( v_days
|| v_mth_days
|| (v_week_hrs + v_holiday)
|| v_otl_hrs
);
DBMS_OUTPUT.put_line ('v_value :' || v_value);
RETURN v_value;
END calculate_food_all;

FUNCTION get_all_unpaid_days (
p_assignment_id IN NUMBER,
p_effective_date IN DATE
)
RETURN NUMBER
IS
l_person_id NUMBER (10);
l_unpaid_days_bal NUMBER (10);
l_unpaid_days NUMBER (10);
l_total NUMBER (10);
BEGIN
SELECT person_id
INTO l_person_id
FROM per_all_assignments_f
WHERE 1 = 1
AND assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;

BEGIN
SELECT NVL (SUM (pev.screen_entry_value), 0)
INTO l_unpaid_days_bal
FROM pay_element_entries_f pee,
pay_element_entry_values_f pev,
pay_element_types_f pet,
pay_input_values_f piv
WHERE 1 = 1
AND pee.element_entry_id = pev.element_entry_id
AND pet.element_type_id = pee.element_type_id
AND pet.element_name IN ('Initial Balance Upload')
--and pet.business_group_id = 81
AND pet.element_type_id = piv.element_type_id
AND pev.input_value_id = piv.input_value_id
--and piv.name in ('Unpaid Days','Sick Leave Days','Days')
AND upper(piv.NAME) like upper('%Unpaid%')
AND pee.assignment_id = p_assignment_id;
---- Ini_Unpaid_Days,
---------------------
EXCEPTION
WHEN OTHERS
THEN
l_unpaid_days_bal := 0;
END;

BEGIN
SELECT --- xed.employee_number,
NVL (SUM (absence_days), 0)
INTO l_unpaid_days
FROM --- XXHR_EMP_DATA_UAE xed,
per_absence_attendances_v paa
WHERE 1 = 1
AND paa.person_id = l_person_id
AND upper(paa.c_type_desc ) like upper('%Unpaid%');---= 'Unpaid
Leave';
---aND upper(piv.NAME) IN upper('%Unpaid%Days%');
--- and xed.employee_number in ( 2699)
EXCEPTION
WHEN OTHERS
THEN
l_unpaid_days := 0;
END;

l_total := l_unpaid_days_bal + l_unpaid_days;

RETURN l_total;
EXCEPTION
WHEN OTHERS
THEN
l_total := 0;
RETURN l_total;
END get_all_unpaid_days;

FUNCTION get_staff_work_location (p_assignment_id IN NUMBER)

RETURN VARCHAR2
IS
l_staff_location VARCHAR2 (10);
BEGIN
SELECT NVL (ass_attribute9, 'PO')
INTO l_staff_location
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND TRUNC (SYSDATE) BETWEEN effective_start_date AND effective_end_date;

RETURN l_staff_location;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'PO';
END get_staff_work_location;

FUNCTION calculate_admin_salary (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
---p_month_value OUT NUMBER
-- p_week_holi_pay OUT NUMBER,
-- p_maternity_pay OUT NUMBER,
-- p_maternity_days OUT NUMBER,
-- p_sick_pay OUT NUMBER,
-- p_sick_ded OUT NUMBER,
-- p_sick_days OUT NUMBER
)
RETURN NUMBER
IS
v_salary NUMBER := 0;
--- p_end_date DATE;
--- p_start_date DATE;
v_gross NUMBER := 0;
v_basic NUMBER := 0;
v_days NUMBER;
---:= (trunc(to_date('01-JAN-2013')) - trunc(to_date('31-JAN-2013')));
------------p_end_date - p_start_date);
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_hrs_per_day NUMBER := 0;
v_otl_hrs NUMBER (5, 2) := 0;
v_curr_otl_hrs NUMBER (5, 2) := 0;
v_week_hrs NUMBER := 0;
v_curr_week_hrs NUMBER := 0;
v_holiday NUMBER := 0;
v_week_pay NUMBER := 0;
v_holi_pay NUMBER := 0;
v_curr_holiday NUMBER := 0;
v_curr_maternity NUMBER := 0;
v_curr_maternity_pay NUMBER := 0;
v_curr_sick NUMBER := 0;
v_curr_sick_pay NUMBER := 0;
v_curr_sick_ded NUMBER := 0;
v_total_sick_days NUMBER := 0;
v_business_group_id NUMBER := 0;
l_period_service NUMBER := 0;
v_person_type VARCHAR2 (100);
v_skip_holiday NUMBER := 0;
v_employee_number VARCHAR2 (250);
v_curr_sick_pay_p NUMBER;
v_curr_sick_ded_p NUMBER;
l_day_status VARCHAR2 (250);
l_half_day_sick_leave NUMBER := 0 ;
l_half_day_annual_leave NUMBER := 0 ;
--- p_month_value NUMBER;
--- p_assignment_id NUMBER;
---- p_element_entry_id NUMBER;
i NUMBER := 0;
BEGIN
v_basic := 0;
-- p_end_date := TO_DATE ('31-JAN-2013');
-- p_start_date := TO_DATE ('01-JAN-2013');
v_days := p_end_date - p_start_date;
v_mth_days :=
xxlam_hr_payroll_all.get_month_days (p_assignment_id, p_start_date);
DBMS_OUTPUT.put_line ('v_mth_days:' || v_mth_days);
DBMS_OUTPUT.put_line ('v_person_type:' || v_person_type);
v_business_group_id := hr_general.get_business_group_id ();
v_business_group_id := 81;
FOR i IN 0 .. v_days
LOOP
v_date := p_start_date + i;
v_curr_otl_hrs := 0;
v_curr_week_hrs := 0;
v_curr_holiday := 0;
v_curr_maternity_pay := 0;
v_curr_sick_pay := 0;
v_curr_sick_ded := 0;
v_curr_sick_pay_p := 0;
v_curr_sick_ded_p := 0;

BEGIN

l_day_status := get_day_status(p_assignment_id,v_date);
v_mth_value :=
xxlam_hr_payroll_all.get_screen_entry_value_at
(p_assignment_id,
p_element_entry_id,
'Monthly Value',
v_date
);
l_half_day_sick_leave := XXLAM_HR_ABSENCE_DURATION.get_parttime_absence
(p_assignment_id,
'Half Day Sick Leave',
v_date ) ;

l_half_day_annual_leave := XXLAM_HR_ABSENCE_DURATION.get_parttime_absence
(p_assignment_id,
'Half Day Annual Leave',
v_date ) ;

END;

--- p_month_value := v_mth_value;

BEGIN
IF l_half_day_sick_leave = 0.5
THEN
v_sal_per_day :=
ROUND (v_sal_per_day + ((v_mth_value) / v_mth_days/2),
3);
end if;

IF l_half_day_annual_leave = 0.5 AND l_day_status <> 'OFF' THEN

v_sal_per_day :=
ROUND (v_sal_per_day + ((v_mth_value) / v_mth_days/2),
3);
END IF;

SELECTabsence_days
INTOv_curr_holiday
FROMper_absence_attendances paa1
WHERE1 = 1
ANDperson_id = (SELECT distinct person_id
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
and v_date between paaf.EFFECTIVE_START_DATE and
paaf.EFFECTIVE_END_DATE )
AND v_date BETWEEN date_start AND date_end
--- and ABSENCE_ATTENDANCE_TYPE_ID <>72 ; ----TOIL is excluded;
AND absence_attendance_type_id not in( '72','68', '1061'); ---- Toil,
Half Day toil and Paid day off
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--- DBMS_OUTPUT.put_line( 'I am adding salary ' || v_date);

IF l_day_status <> 'OFF'

then
v_sal_per_day :=
ROUND (v_sal_per_day + (v_mth_value) / v_mth_days,
3);

END IF;
WHEN OTHERS
THEN
NULL;
END;

v_hrs_per_day := 9;
-- DBMS_OUTPUT.put_line ( 'v_sal_per_day'
-- || v_sal_per_day
-- || '~'
-- || 'Date:....'
-- || v_date
-- || ' p_month_value'
-- || p_month_value
-- );
END LOOP;

RETURN v_sal_per_day;
END calculate_admin_salary;

FUNCTION CALCULATE_OVERTIME (p_assignment_id IN NUMBER,

p_ot_hours IN NUMBER,
p_ot_date IN DATE
)
RETURN NUMBER
IS
l_ot_elig VARCHAR2 (10) := 'N';
Basic_salary Number ;
--- OT_Hours NUMBER ;
OT_Amount NUMBER := 0;
l_user_table_name VARCHAR2 (200);
l_user_column_name VARCHAR2 (200);
l_work_schedule VARCHAR2 (200);
l_no_hours_in_Day VARCHAR2 (20);
l_person_type VARCHAR2 (20);
l_month_days NUMBER;
l_ot_cap VARCHAR2 (20);
l_payroll_name VARCHAR2 (100);

BEGIN

----l_month_days := get_month_days(p_assignment_id,p_ot_date);
l_month_days := get_emp_work_plan(p_assignment_id,p_ot_date);
---OT_Hours := 4;
select ASS_ATTRIBUTE3,ASS_ATTRIBUTE1, NVL(ASS_ATTRIBUTE25,0)
into l_ot_elig , l_work_schedule,l_ot_cap
from per_All_Assignments_f
where assignment_id = p_assignment_id
and trunc(p_ot_date) between effective_start_Date and effective_end_Date ;

l_person_type:= get_person_type_ass ( p_assignment_id,

trunc(p_ot_date) );
l_payroll_name := get_payroll_name( p_assignment_id,
trunc(p_ot_date) );

If Upper(l_ot_elig) = 'Y' then

Begin
select USER_TABLE_NAME , USER_COLUMN_NAME into
l_user_table_name , l_user_column_name
from
pay_user_tables t, pay_user_Columns c
where t.user_table_name = 'LAM_HR_WORK_SCHEDULE'
and c.user_table_id = t.user_table_id
and c.user_column_id = l_work_schedule;
Exception when Others then
DBMS_OUTPUT.put_line ('NO work Schedule Assigned' );
End;

-- DBMS_OUTPUT.put_line ('OT Eligiliblity is ' || l_ot_elig );

-- DBMS_OUTPUT.put_line (' l_user_table_name ' || l_user_table_name );
-- DBMS_OUTPUT.put_line (' l_user_column_name ' || l_user_column_name);
--
Basic_salary :=XXLAM_HR_PAYROLL_ALL.get_basic_at(p_assignment_id,p_ot_date);
DBMS_OUTPUT.put_line ('Basic Salary is ' || Basic_salary);
--
BEGIN
select
NVL (HRUSERDT.GET_TABLE_VALUE ( 81,
'LAM_HR_WORK_SCHEDULE',
l_user_column_name,---- '5 Days
(Thursday, Friday off)' ,
'MONDAY',
p_ot_date ---to_date('01-jan-1950')

),'0') into l_no_hours_in_Day

from dual;
--- DBMS_OUTPUT.put_line ('l_no_hours_in_Day ' || l_no_hours_in_Day );
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('No Table value for Work Schedule');
END;

IF Basic_salary <4000 and l_payroll_name = 'LEL Yard Payroll' then ----- 22

is no of days as per the requirement ...
OT_Amount := Basic_salary/l_month_days/to_number(l_no_hours_in_Day)*1.5 *
p_ot_hours;
Else
OT_Amount := Basic_salary/l_month_days/to_number(l_no_hours_in_Day)*1.25 *
p_ot_hours;
End if ;

--DBMS_OUTPUT.put_line ('OT amount is ' || OT_Amount);

ELSE
DBMS_OUTPUT.put_line ('OT Not Eligilble');
END if;

-- IF TO_NUMBER(l_ot_cap) > 0
-- then
--
-- IF Ot_Amount > TO_NUMBER(l_ot_cap)
-- THEN
--
-- Ot_Amount := TO_NUMBER(l_ot_cap);
--
-- END IF;
--
-- END IF;

RETURN round(Ot_Amount,2);
EXCEPTION
WHEN OTHERS THEN
Null;

DBMS_OUTPUT.put_line ('In exception ' || l_ot_elig );

RETURN round(Ot_Amount,2);

END CALCULATE_OVERTIME;
FUNCTION GET_OTL_REGULAR_HOURS_YARD (
p_assignment_id NUMBER,
p_effective_date DATE
)
RETURN NUMBER
IS

--DECLARE
--p_assignment_id Number :=141;
--p_effective_date DATE := '01-JAN-1950';
l_value NUMBER := 0;
v_week_off NUMBER := 0;
v_holiday NUMBER := 0;
v_person_type VARCHAR2 (30);
v_element_entry_id NUMBER := 0;
v_date DATE;
v_skip_y_n VARCHAR2 (100) := 'N';
v_term_person_type VARCHAR2 (100) := NULL;
BEGIN
v_person_type := 'Yard';
IF v_person_type = 'Yard'
THEN
SELECT NVL
(SUM
(fnd_number.canonical_to_number (pev.screen_entry_value)
),
0
),
MAX (peef.element_entry_id)
INTO l_value,
v_element_entry_id
FROM pay_element_types_f_vl pet,
pay_input_values_f piv,
pay_element_entry_values_f pev,
pay_element_entries_f peef
WHERE 1 = 1
AND peef.assignment_id = p_assignment_id
AND piv.NAME = 'Hours'
AND pet.element_name IN ('Regular Hours', 'OTL Holiday Pay')
AND piv.element_type_id = pet.element_type_id
AND peef.element_type_id = pet.element_type_id
AND piv.input_value_id = pev.input_value_id
AND peef.element_entry_id = pev.element_entry_id
AND peef.date_earned = p_effective_date;
ELSE
SELECT NVL
(SUM
(fnd_number.canonical_to_number (pev.screen_entry_value)
),
0
),
MAX (peef.element_entry_id)
INTO l_value,
v_element_entry_id
FROM pay_element_types_f_vl pet,
pay_input_values_f piv,
pay_element_entry_values_f pev,
pay_element_entries_f peef
WHERE 1 = 1
AND peef.assignment_id = p_assignment_id
AND piv.NAME = 'Hours'
AND pet.element_name IN ('Regular Hours')
AND piv.element_type_id = pet.element_type_id
AND peef.element_type_id = pet.element_type_id
AND piv.input_value_id = pev.input_value_id
AND peef.element_entry_id = pev.element_entry_id
AND peef.date_earned = p_effective_date;
END IF;

--------Added the below code Starts here

-- IF v_term_person_type IN ('Yard', 'Staff')
-- THEN
-- BEGIN
-- v_skip_y_n :=
-- xxlam_hr_payroll_All.xxpay_skip_reg_hours_y_n
(v_element_entry_id, v_date);
-- END;
-- ELSE
-- BEGIN
-- v_skip_y_n :=
-- xxlam_hr_payroll_All.xxpay_skip_reg_hours_y_n
(v_element_entry_id,
-- p_effective_date);
-- END;
-- END IF;

--------Added the below code ENDSs here

-- BEGIN
-- v_week_off :=
-- xxlam_hr_payroll_All.xxotl_get_week_off_pay (p_assignment_id,
-- p_effective_date,
-- p_effective_date
-- );
-- END;

--- v_person_type := get_person_type_ass (p_assignment_id, p_effective_date);

BEGIN
v_holiday :=
xxlam_hr_absence_duration.holidays (p_assignment_id,
p_effective_date,
p_effective_date
);
END;

IF v_person_type = 'Yard'
THEN
IF (v_week_off <> 0 OR v_holiday <> 0)
THEN
l_value := 0;
END IF;
--------Added the below code Starts here
ELSIF v_person_type = 'Staff'
THEN
IF v_skip_y_n = 'Y'
THEN
l_value := 0;
END IF;
--------Added the below code ENDs here
END IF;

DBMS_OUTPUT.put_line ('Regular hours v_skip_y_n = ' || v_skip_y_n);

DBMS_OUTPUT.put_line (l_value);
RETURN l_value;
EXCEPTION
WHEN OTHERS
THEN
l_value := 0;
RETURN l_value;
END GET_OTL_REGULAR_HOURS_YARD;

FUNCTION CALCULATE_SALARY_YARD (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
--- p_month_value OUT NUMBER,
-- p_week_holi_pay OUT NUMBER,
-- p_maternity_pay OUT NUMBER,
-- p_maternity_days OUT NUMBER,
-- p_sick_pay OUT NUMBER,
-- p_sick_ded OUT NUMBER,
-- p_sick_days OUT NUMBER
)
RETURN NUMBER
IS
v_salary NUMBER := 0;
v_gross NUMBER := 0;
v_basic NUMBER := 0;
v_days NUMBER ;--- := (p_end_date - p_start_date);
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_hrs_per_day NUMBER := 0;
v_otl_hrs NUMBER (5, 2) := 0;
v_curr_otl_hrs NUMBER (5, 2) := 0;
v_week_hrs NUMBER := 0;
v_curr_week_hrs NUMBER := 0;
v_holiday NUMBER := 0;
v_week_pay NUMBER := 0;
v_holi_pay NUMBER := 0;
v_curr_holiday NUMBER := 0;
v_curr_maternity NUMBER := 0;
v_curr_maternity_pay NUMBER := 0;
v_curr_sick NUMBER := 0;
v_curr_sick_pay NUMBER := 0;
v_curr_sick_ded NUMBER := 0;
v_total_sick_days NUMBER := 0;
v_business_group_id NUMBER := 0;
l_period_service NUMBER := 0;
v_person_type VARCHAR2 (100);
v_skip_holiday NUMBER := 0;
v_employee_number VARCHAR2 (250);
v_curr_sick_pay_p NUMBER;
v_curr_sick_ded_p NUMBER;
p_month_value NUMBER;
-- p_assignment_id NUMBER;
-- p_element_entry_id NUMBER;
-- p_start_date DATE;
-- p_end_date DATE;

BEGIN

-- p_assignment_id :=141;
-- p_element_entry_id := 1083;
-- p_start_date :=to_Date('01-SEP-2013');
-- p_end_date :=to_Date('30-SEP-2013');
v_basic := 0;
---v_days := (p_end_date - p_start_date);

v_days := xxlam_hr_payroll_all.get_month_days (p_assignment_id,

p_start_date);
---xxlam_hr_payroll_all.get_month_days (p_assignment_id, p_start_date);
v_person_type := 'Yard';---get_person_type_ass (p_assignment_id, p_end_date);
DBMS_OUTPUT.put_line ('v_mth_days:' || v_mth_days);
DBMS_OUTPUT.put_line ('v_person_type:' || v_person_type);
-- p_month_value := 0;
-- p_week_holi_pay := 0;
-- p_month_value := 0;
-- p_maternity_pay := 0;
-- p_maternity_days := 0;
-- p_sick_pay := 0;
-- p_sick_ded := 0;
-- p_sick_days := 0;
v_business_group_id := hr_general.get_business_group_id ();

v_business_group_id := 81;
FOR i IN 0 .. v_days
LOOP
v_date := p_start_date + i;
v_curr_otl_hrs := 0;
v_curr_week_hrs := 0;
v_curr_holiday := 0;
v_curr_maternity_pay := 0;
v_curr_sick_pay := 0;
v_curr_sick_ded := 0;
v_curr_sick_pay_p := 0;
v_curr_sick_ded_p := 0;

BEGIN
v_mth_value :=
xxlam_hr_payroll_all.get_screen_entry_value_at (p_assignment_id,
p_element_entry_id,
'Monthly Value',
v_date
);
END;
DBMS_OUTPUT.PUT_LINE ( ' BAsic Salary ' ||v_mth_value );

p_month_value := v_mth_value;

BEGIN
v_hrs_per_day := xxlam_hr_payroll_all.xxotl_hours_day
(p_assignment_id, v_date);
END;
DBMS_OUTPUT.PUT_LINE ( ' HOUrs per day '||v_hrs_per_day );

BEGIN
v_curr_otl_hrs := xxlam_hr_payroll_all.GET_OTL_REGULAR_HOURS_YARD
(p_assignment_id, v_date);
END;

DBMS_OUTPUT.PUT_LINE ( ' OTL REGULAR HOURS '||v_curr_otl_hrs );

--- v_otl_hrs := v_otl_hrs + v_curr_otl_hrs;

-- v_sal_per_day :=round( v_sal_per_day + (p_month_value / v_mth_days/
v_hrs_per_day) * v_curr_otl_hrs,2);
v_sal_per_day := v_sal_per_day + (p_month_value / v_days / v_hrs_per_day)
* v_curr_otl_hrs; ---v_days
DBMS_OUTPUT.put_line ( 'v_sal_per_day:......'
|| v_sal_per_day
|| '~'
|| 'Date:....'
|| v_date
);

END LOOP;

DBMS_OUTPUT.put_line ('Week Pay:' || v_week_pay || 'Date:' ||

v_sal_per_day );
RETURN v_sal_per_day;
END CALCULATE_SALARY_YARD;

FUNCTION LEL_CALCULATE_GRATUITY (
p_assignment_id IN NUMBER,
p_element_entry_id in NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
)
RETURN NUMBER
IS

l_years NUMBER := 0;
--- p_assignment_id number := 143 ;
l_months NUMBER := 0;
l_days NUMBER := 0;
l_value NUMBER := 0;
l_sal_day NUMBER;
-- := (get_salary_at (p_assignment_id, p_end_date) * .6) / 365;
l_service_days NUMBER := 0;
l_salary NUMBER := 0;
l_unpaid_leave NUMBER := 0;
l_service_years NUMBER := 0;
p_month_factor NUMBER;
l_contract_duration NUMBER;
l_salary_tmp NUMBER;
l_bg_id NUMBER;
l_days_more_five_year NUMBER := 0;
l_max_gratuity NUMBER := 0;
--- p_start_date DATE := to_date('01-jan-2009');
--- p_end_date date := to_date('01-jan-2015');
--- p_month_factor NUMBER;
BEGIN
SELECT business_group_id
INTO l_bg_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_end_date BETWEEN effective_start_date AND effective_end_date;

l_salary_tmp :=
xxlam_hr_payroll_all.get_screen_entry_value_at
(p_assignment_id,
p_element_entry_id,
'Monthly Value',
p_end_date
);

---- l_sal_day := (l_salary_tmp * 12) / 365;

l_sal_day := l_salary_tmp /30;

l_unpaid_leave :=
xxlam_hr_payroll_all.get_all_unpaid_days (p_assignment_id,
p_end_date);
l_service_days := ((p_end_date - p_start_date) + 1) - l_unpaid_leave;

DBMS_OUTPUT.put_line ( 'l_sal_day = '||l_sal_day );

DBMS_OUTPUT.put_line ( 'per month salary = '||l_salary_tmp );

DBMS_OUTPUT.put_line ( 'l_service_days = '||l_service_days );
DBMS_OUTPUT.put_line ( 'l_unpaid_leave = '||l_unpaid_leave );
l_service_years :=l_service_days /365;

IF l_service_years <= 1 then

l_value := 0;

ElSIF l_service_years < 5

THEN
l_value := ROUND (l_service_days * (l_sal_day) * 21 / 365, 0);

ELSIF l_service_years >= 5

THEN

l_days_more_five_year := l_service_days - 1825;

l_value :=
ROUND (1825 * (l_sal_day) * 21 / 365, 0)
+ ROUND (l_days_more_five_year * (l_sal_day) * 30 / 365, 0);

END IF;

DBMS_OUTPUT.put_line ( 'l_service_years = '

|| l_service_years
|| ' l_service_days = '
|| l_service_days
|| ' l_value = '
|| l_value
|| ' l_sal_day= '
|| l_sal_day
);

l_max_gratuity := l_salary_tmp * 24 ; ---MAX Gratuity to paid is

Basic Salary * 24
IF l_value > l_max_gratuity then

l_value :=l_max_gratuity;
END IF;

RETURN l_value;

EXCEPTION
WHEN OTHERS
THEN
l_value := 0;
---p_service_period := NULL;
RETURN l_value;
END LEL_CALCULATE_GRATUITY;

FUNCTION CALCULATE_SICK_LEAVE_SALARY (
p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_sick_pay OUT NUMBER,
p_sick_ded OUT NUMBER,
p_sick_days OUT NUMBER
)
RETURN NUMBER
IS

---DECLARE
--p_assignment_id NUMBER :=61;
-- p_start_date DATE := to_Date('01-JAN-2014');
-- p_end_date DATE := to_Date('31-JAN-2014');
-- p_month_value NUMBER := 0;
-- p_week_holi_pay NUMBER := 0;
-- p_month_value NUMBER := 0;
-- p_maternity_pay NUMBER := 0;
-- p_maternity_days NUMBER:= 0;
-- p_sick_pay NUMBER:= 0;
-- p_sick_ded NUMBER := 0;
-- p_sick_days NUMBER:= 0;
--

v_salary NUMBER := 0;
v_gross NUMBER := 0;
v_sick NUMBER := 0;
v_days NUMBER := (p_end_date - p_start_date);
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_hrs_per_day NUMBER := 0;
v_otl_hrs NUMBER (5, 2) := 0;
v_curr_otl_hrs NUMBER (5, 2) := 0;
v_week_hrs NUMBER := 0;
v_curr_week_hrs NUMBER := 0;
v_holiday NUMBER := 0;
v_week_pay NUMBER := 0;
v_holi_pay NUMBER := 0;
v_curr_holiday NUMBER := 0;
v_curr_maternity NUMBER := 0;
v_curr_maternity_pay NUMBER := 0;
v_curr_sick NUMBER := 0;
v_curr_sick_pay NUMBER := 0;
v_curr_sick_ded NUMBER := 0;
v_total_sick_days NUMBER := 0;
v_business_group_id NUMBER := 0;
l_period_service NUMBER := 0;
v_person_type VARCHAR2 (100);
v_skip_holiday NUMBER := 0;
v_employee_number VARCHAR2 (250);
v_curr_sick_pay_p NUMBER;
v_curr_sick_ded_p NUMBER;
l_prob_date NUMBER :=0 ;
l_ini_sick_leave NUMBER :=0 ;

BEGIN
--- v_basic := 0;
v_mth_days := XXLAM_HR_PAYROLL_ALL.get_month_days (p_assignment_id,
p_start_date);
v_person_type := XXLAM_HR_PAYROLL_ALL.get_person_type_ass (p_assignment_id,
p_end_date);
--- p_month_value := 0;
-- p_week_holi_pay := 0;
-- -p_month_value := 0;
-- p_maternity_pay := 0;
-- p_maternity_days := 0;
p_sick_pay := 0;
p_sick_ded := 0;
p_sick_days := 0;
v_business_group_id := hr_general.get_business_group_id ();
v_business_group_id :=81;
DBMS_OUTPUT.put_line ('Business Grp id ' ||v_business_group_id);
-- || v_date
-- );
--v_business_group_id := 81;
l_ini_sick_leave := XXLAM_HR_ABSENCE_DURATION.INITIAL_UPLOAD_SICK_LEAVE
( 81,p_assignment_id) ;

FOR i IN 0 .. v_days
LOOP
v_date := p_start_date + i;
v_curr_otl_hrs := 0;
v_curr_week_hrs := 0;
v_curr_holiday := 0;
v_curr_maternity_pay := 0;
v_curr_sick_pay := 0;
v_curr_sick_ded := 0;
v_curr_sick_pay_p := 0;
v_curr_sick_ded_p := 0;

/***************************************Sick leave not on Probation

************************/
-- begin

-- select 1 into l_prob_date from per_all_assignments_f

-- where assignment_id = p_assignment_id
-- and DATE_PROBATION_END < v_date;---- ('01-apr-2014'); ----:v_date

-- DBMS_OUTPUT.PUT_LINE ( ' l_prob_date is ' || l_prob_date );

-- exception when others then

-- l_prob_date :=0;
-- DBMS_OUTPUT.PUT_LINE ( ' l_prob_date ' || l_prob_date );

-- end;

--
-- /***************************************Sick leave not on Probation
************************/
-- IF l_prob_date >1 THEN
--

BEGIN
v_gross := XXLAM_HR_PAYROLL_ALL.get_gross_at (p_assignment_id, v_date);
END;

/******** Sick Leave Calculation Starts /

v_total_sick_days :=
xxlam_hr_absence_duration.get_sick_leave_taken (p_assignment_id,
v_date
) ;
--- + l_ini_sick_leave
+ ---- remove this line from

---nvl( xxlam_hr_payroll_all.get_balance (p_assignment_id,

--- v_business_group_id,
--- 'Initial_Sick_Leave_Days',
--- 'ITD',
--- v_date
--- ),0); ---- remove this line
till

DBMS_OUTPUT.put_line ('v_total_sick_days ITD ' || v_total_sick_days );

v_curr_sick :=
xxlam_hr_absence_duration.get_no_of_leaves_taken_ass_id
(v_business_group_id,
p_assignment_id,
'Sick Leave',
v_date,
v_date
) +
XXLAM_HR_ABSENCE_DURATION.get_parttime_absence (p_assignment_id,
'Half Day Sick Leave',
v_date );

DBMS_OUTPUT.put_line ('current month sick ' || v_curr_sick );

IF v_curr_sick > 0
THEN
IF v_total_sick_days <= 15
THEN
v_curr_sick_pay := v_curr_sick * (v_gross / v_mth_days);
DBMS_OUTPUT.put_line ('Less than 15 days: Gross' || v_gross|| ' ***
v_mth_days' || v_mth_days);
ELSE
IF v_total_sick_days > 15 AND v_total_sick_days <= 45
THEN
v_curr_sick_pay := v_curr_sick * (v_gross / v_mth_days);
v_curr_sick_ded := v_curr_sick * (v_gross / v_mth_days)
* .5;
DBMS_OUTPUT.put_line
( 'between than 16 and 45 days: Gross'
|| v_gross
);
ELSIF v_total_sick_days > 45 AND v_total_sick_days <= 90
THEN
v_curr_sick_pay := v_curr_sick * (v_gross / v_mth_days);
v_curr_sick_ded := v_curr_sick * (v_gross / v_mth_days);
DBMS_OUTPUT.put_line
( 'between than 49 and 90 days: Gross'
|| v_gross
);
END IF;
END IF;
END IF;

p_sick_pay := p_sick_pay + v_curr_sick_pay;

p_sick_ded := p_sick_ded + v_curr_sick_ded;
p_sick_days := p_sick_days + v_curr_sick;
DBMS_OUTPUT.put_line ( 'Curr Sick:'
|| v_curr_sick
|| 'sick days:'
|| p_sick_days
|| ' sick pay:'
|| p_sick_pay
|| ' sick ded:'
|| p_sick_ded
|| ' Total Sick:'
|| v_total_sick_days
|| ' Date:'
|| v_date
);
---- END IF ;
END LOOP;
v_sick :=v_total_sick_days;
RETURN v_sick;
END CALCULATE_SICK_LEAVE_SALARY;

FUNCTION CALCULATE_LEAVE_SALARY(
p_assignment_id IN NUMBER,
p_leave_name IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_leave_days OUT NUMBER
)
RETURN NUMBER
IS

----DECLARE
---p_assignment_id NUMBER :=61; --110
---p_leave_name varchar2(200);
-- p_start_date DATE := to_Date('01-jul-2013');
--- p_end_date DATE := to_Date('31-jul-2013');
p_month_value NUMBER := 0;
p_week_holi_pay NUMBER := 0;
p_month_value NUMBER := 0;
p_maternity_pay NUMBER := 0;
p_maternity_days NUMBER:= 0;
p_sick_pay NUMBER:= 0;
p_sick_ded NUMBER := 0;
p_sick_days NUMBER:= 0;
v_salary NUMBER := 0;
v_gross NUMBER := 0;
v_basic NUMBER := 0;
v_days NUMBER := (p_end_date - p_start_date);
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_hrs_per_day NUMBER := 0;
v_otl_hrs NUMBER (5, 2) := 0;
v_curr_otl_hrs NUMBER (5, 2) := 0;
v_week_hrs NUMBER := 0;
v_curr_week_hrs NUMBER := 0;
v_holiday NUMBER := 0;
v_week_pay NUMBER := 0;
v_holi_pay NUMBER := 0;
v_curr_holiday NUMBER := 0;
v_curr_maternity NUMBER := 0;
v_curr_maternity_pay NUMBER := 0;
v_curr_sick NUMBER := 0;
v_curr_sick_pay NUMBER := 0;
v_curr_sick_ded NUMBER := 0;
v_total_sick_days NUMBER := 0;
v_business_group_id NUMBER := 0;
l_period_service NUMBER := 0;
v_person_type VARCHAR2 (100);
v_skip_holiday NUMBER := 0;
v_employee_number VARCHAR2 (250);
v_curr_sick_pay_p NUMBER;
v_curr_sick_ded_p NUMBER;
v_leave_salary NUMBER := 0;
t_leave_salary NUMBER := 0 ;
v_leave_days NUMBER := 0 ;
t_leave_days NUMBER := 0 ;

BEGIN
--- p_leave_name := 'Emergency Leave';---'Maternity Leave'; Compassionate Leave
---Emergency Leave
v_basic := 0;

v_person_type := XXLAM_HR_PAYROLL_ALL.get_person_type_ass (p_assignment_id,

p_end_date);
p_leave_days := 0;
p_week_holi_pay := 0;
---p_month_value := 0;
p_maternity_pay := 0;
p_maternity_days := 0;
p_sick_pay := 0;
p_sick_ded := 0;
p_sick_days := 0;
v_business_group_id := hr_general.get_business_group_id ();
v_business_group_id :=81;
DBMS_OUTPUT.put_line ('Business Grp id ' ||v_business_group_id);
-- || v_date
-- );
--v_business_group_id := 81;
FOR i IN 0 .. v_days
LOOP
v_date := p_start_date + i;
v_curr_otl_hrs := 0;
v_curr_week_hrs := 0;
v_curr_holiday := 0;
v_curr_maternity_pay := 0;
v_curr_sick_pay := 0;
v_curr_sick_ded := 0;
v_curr_sick_pay_p := 0;
v_curr_sick_ded_p := 0;

BEGIN
v_gross := XXLAM_HR_PAYROLL_ALL.get_gross_at (p_assignment_id, v_date);
DBMS_OUTPUT.put_line ( 'Gross Salary is ' || v_gross || '--- Days in
month is ----'|| to_char( v_days+1));
END;
v_mth_days := XXLAM_HR_PAYROLL_ALL.get_month_days (p_assignment_id,
v_date);

/************************ Maternity Leave Calculation Starts

****************/
IF p_leave_name = 'Maternity Leave' THEN
l_period_service :=
xxlam_hr_absence_duration.get_period_of_service (p_assignment_id,
v_date
);
v_curr_maternity :=
xxlam_hr_absence_duration.get_no_of_advance_leaves_taken
(v_business_group_id,
p_assignment_id,
'Maternity Leave',
v_date,
v_date
);

p_maternity_pay := p_maternity_pay + v_curr_maternity_pay;

p_leave_days := p_leave_days + v_curr_maternity;
DBMS_OUTPUT.put_line ( 'maternity days:'
|| p_maternity_days
|| ' maternity pay:'
|| p_maternity_pay
|| ' Service Period:'
|| l_period_service
|| ' Date:'
|| v_date
);
t_leave_salary := p_maternity_pay;
/************************ Maternity Leave Calculation Ends
****************/
END IF;

/************************ Annual Vacation , Compassionate , Emergency,

Garden Leave Calculation Starts ****************/

IF (p_leave_name = 'Annual Leave' or p_leave_name = 'Compassionate Leave'

or p_leave_name = 'Emergency Leave' or p_leave_name ='Garden Leave' or
p_leave_name = 'Half Day Annual Leave' ) THEN

v_leave_days :=
xxlam_hr_absence_duration.get_no_of_leaves_taken_ass_id
(v_business_group_id,
p_assignment_id,
p_leave_name, -- 'Annual
Leave',
v_date,
v_date
);

v_leave_salary := v_leave_days * (v_gross / v_mth_days);

t_leave_days := t_leave_days + v_leave_days;

t_leave_salary := t_leave_salary + v_leave_salary;

p_leave_days :=t_leave_days ;
DBMS_OUTPUT.PUT_LINE ( ' leave salary is ' ||t_leave_salary ||' --- Total
leave Days are ---- '|| t_leave_days);

End IF;
/************************ Annual Vacation , Compassionate , Emergency ,
Garden Leave Calculation Ends ****************/
/************************ Forced Leave Calculation Starts
****************/

IF (p_leave_name = 'Forced Leave' ) THEN

v_leave_days :=
xxlam_hr_absence_duration.get_no_of_leaves_taken_ass_id
(v_business_group_id,
p_assignment_id,
p_leave_name, -- 'Annual
Leave',
v_date,
v_date
);
t_leave_days := t_leave_days + v_leave_days;

IF t_leave_days <8 THEN

v_leave_salary := v_leave_days * (v_gross / v_mth_days);

t_leave_salary := t_leave_salary + v_leave_salary;
p_leave_days :=t_leave_days ;
DBMS_OUTPUT.PUT_LINE ( ' leave salary is ' ||t_leave_salary ||' ---
Total leave Days are ---- '|| t_leave_days);

End IF;

END IF;
/************************ Forced Leave, Compassionate , Emergency
Calculation Ends ****************/

END LOOP;
RETURN t_leave_salary;
END CALCULATE_LEAVE_SALARY;
FUNCTION CALCULATE_OVERTIME_HOLIDAY (p_assignment_id IN NUMBER,
p_ot_hours IN NUMBER,
p_ot_date IN DATE
)
RETURN NUMBER
IS
l_ot_elig VARCHAR2 (10) := 'N';
Basic_salary Number ;
--- OT_Hours NUMBER ;
OT_Amount NUMBER := 0;
l_user_table_name VARCHAR2 (200);
l_user_column_name VARCHAR2 (200);
l_work_schedule VARCHAR2 (200);
l_no_hours_in_Day VARCHAR2 (20);
l_month_days number;

BEGIN

---- l_month_days := get_month_days(p_assignment_id,p_ot_date);

l_month_days := get_emp_work_plan(p_assignment_id,p_ot_date);

---OT_Hours := 4;
select ASS_ATTRIBUTE3,ASS_ATTRIBUTE1
into l_ot_elig , l_work_schedule
from per_All_Assignments_f
where assignment_id = p_assignment_id
and trunc(p_ot_date) between effective_start_Date and effective_end_Date ;

If Upper(l_ot_elig) = 'Y' then

Begin
select USER_TABLE_NAME , USER_COLUMN_NAME into
l_user_table_name , l_user_column_name
from
pay_user_tables t, pay_user_Columns c
where t.user_table_name = 'LAM_HR_WORK_SCHEDULE'
and c.user_table_id = t.user_table_id
and c.user_column_id = l_work_schedule;
Exception when Others then
DBMS_OUTPUT.put_line ('NO work Schedule Assigned' );
End;

-- DBMS_OUTPUT.put_line ('OT Eligiliblity is ' || l_ot_elig );

),'0') into l_no_hours_in_Day

from dual;
--- DBMS_OUTPUT.put_line ('l_no_hours_in_Day ' || l_no_hours_in_Day );
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('No Table value for Work Schedule');
END;

----IF Basic_salary <=4000 then ----- 22 is no of days as per the requirement

...
OT_Amount := Basic_salary/l_month_days/to_number(l_no_hours_in_Day)*1.5 *
p_ot_hours;
----Else
----OT_Amount := Basic_salary/22/to_number(l_no_hours_in_Day)*1.25 *
p_ot_hours;
----End if ;

--DBMS_OUTPUT.put_line ('OT amount is ' || OT_Amount);

ELSE
DBMS_OUTPUT.put_line ('OT Not Eligilble');
END if;

RETURN round(Ot_Amount,2);
EXCEPTION
WHEN OTHERS THEN
Null;

DBMS_OUTPUT.put_line ('In exception ' || l_ot_elig );

RETURN round(Ot_Amount,2);

END CALCULATE_OVERTIME_HOLIDAY;
/* Calculate the Net Entitlement */
FUNCTION GET_NET_ENTITLEMENT_ANNUAL(
P_ASSG_ID IN NUMBER,
P_PAYROLL_ID IN NUMBER,
P_CALCULATION_DATE IN DATE
)
RETURN NUMBER
IS
L_START_DATE DATE;
L_END_DATE DATE;
L_ACC_END_DATE DATE;
L_VALUE NUMBER := 0;
L_NET_VALUE NUMBER (15, 3);
P_PLAN_ID NUMBER;
-- P_PLAN_ID NUMBER;

BEGIN
SELECT PAP.ACCRUAL_PLAN_ID
INTO P_PLAN_ID
FROM PAY_ACCRUAL_PLANS PAP
WHERE UPPER (PAP.ACCRUAL_PLAN_NAME) LIKE
(SELECT UPPER (PETF.ELEMENT_NAME)
FROM PAY_ELEMENT_ENTRIES_F PEEF, PAY_ELEMENT_TYPES_F PETF
WHERE PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND UPPER (PETF.ELEMENT_NAME) LIKE '%PLAN%'
AND PETF.PROCESSING_TYPE = 'R'
AND PEEF.ASSIGNMENT_ID = P_ASSG_ID
AND P_CALCULATION_DATE BETWEEN PEEF.EFFECTIVE_START_DATE
AND PEEF.EFFECTIVE_END_DATE);

-- PER_ACCRUAL_CALC_FUNCTIONS.GET_NET_ACCRUAL
-- (P_ASSIGNMENT_ID => P_ASSG_ID,
-- P_PLAN_ID => P_PLAN_ID,
-- P_PAYROLL_ID => P_PAYROLL_ID,
-- P_BUSINESS_GROUP_ID => 0,
-- P_ASSIGNMENT_ACTION_ID => -1,
-- P_CALCULATION_DATE => TO_DATE
--
(P_CALCULATION_DATE
-- )
---- DATE YOU WANT TO CHECK THE NET ENTITLEMENT EX. TO_DATE('01-MAR-2009', 'DD-MON-
YYYY')
-- ,
-- P_ACCRUAL_START_DATE => NULL,
-- P_ACCRUAL_LATEST_BALANCE => NULL,
-- P_CALLING_POINT => 'FRM',
-- P_START_DATE => L_START_DATE,
-- P_END_DATE => L_END_DATE,
-- P_ACCRUAL_END_DATE => L_ACC_END_DATE,
-- P_ACCRUAL => L_VALUE,
-- P_NET_ENTITLEMENT => L_NET_VALUE
-- );

L_NET_VALUE:= pay_us_pto_accrual.get_net_accrual
(P_ASSG_ID,
P_CALCULATION_DATE ,
P_PLAN_ID,
---pva1.accrual_plan_id,
'V',---
pva1.accrual_category,
''
) ;

RETURN NVL (L_NET_VALUE, 0);

EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END GET_NET_ENTITLEMENT_ANNUAL;

/------------------------ Get Leave Encashment-------------------------------- /

FUNCTION LEL_CALCULATE_LEAVE_ENCASHMENT (
p_assignment_id IN NUMBER,
p_end_date IN DATE,
p_start_date IN DATE,
p_net_entitle IN NUMBER
)
RETURN NUMBER
IS
l_salary_tmp NUMBER:= 0;
l_sal_day NUMBER:= 0;
l_service_months NUMBER:= 0;
l_value NUMBER:=0;
l_person_type varchar(100);
l_month_days NUMBER:=0;
l_days NUMBER:=0;
l_sal NUMBER:=0;
l_service_DAYS NUMBER:=0;
BEGIN

l_person_type:= get_person_type_ass ( p_assignment_id,

trunc(p_start_date) );
l_month_days := get_emp_work_plan(p_assignment_id,p_end_date);
l_salary_tmp :=
xxlam_hr_payroll_all.get_gross_at
(p_assignment_id,
p_end_date
);
IF l_person_type = 'Yard'
THEN
l_days := 30;
ELSIF l_person_type = 'Admin'
then
L_days := l_month_days;
ELSE
L_days := 0;
End if;

l_sal_day := l_salary_tmp / L_days ;

/* l_sal_day := (l_salary_tmp * 12) / 365 ; */

select MONTHS_BETWEEN(p_end_date ,p_start_date)

into l_service_months
FROM DUAL;

DBMS_OUTPUT.put_line ( 'l_salary_tmp = '||l_sal_day );

DBMS_OUTPUT.put_line ( 'per month salary = '||l_salary_tmp );

IF l_service_months < 6 then

l_value := 0;

ElSIF l_service_months > 6 AND l_service_months <= 11

THEN
---- l_value := (l_service_months * l_sal_day) * 2; ------ If service is
less than 1 year than divide by 30, confirmed from Violet

l_service_DAYS := (fffunc.days_between (p_end_date , p_start_date)+1)/30;

l_sal := l_salary_tmp / 30;
l_value := (l_service_DAYS * l_sal) * 2;
ElSIF l_service_months > 11 then
l_value := p_net_entitle * l_sal_day;

ELSE l_value := 0;
END IF;

-- IF l_value < 0
-- then
-- l_value := 0;
-- END IF;

DBMS_OUTPUT.put_line ( 'l_service_months = '

|| l_service_months
|| ' p_net_entitle = '
|| p_net_entitle
|| ' l_value = '
|| l_value
|| ' l_service_DAYS = '
|| l_service_DAYS
);

RETURN l_value;

EXCEPTION
WHEN OTHERS
THEN
l_value := 0;
---p_service_period := NULL;
RETURN l_value;
END LEL_CALCULATE_LEAVE_ENCASHMENT;
/*---------------------------- Function get the assingnment dff value used in fast
formulas ----------------------------*/
FUNCTION GET_ASSG_DFF_VAL (
P_ELEMENT_ENTRY_ID IN NUMBER,
P_ASSG_ID IN NUMBER,
P_END_DATE IN DATE
)
RETURN VARCHAR
IS
L_ELEMENT_NAME Varchar(240);
L_ATTRIBUTE_VALUE Varchar(240);

BEGIN

select distinct petf.ELEMENT_NAME

INTO L_ELEMENT_NAME
from pay_element_entries_f peef
,pay_element_types_f petf
where 1=1
and peef.ELEMENT_ENTRY_ID = P_ELEMENT_ENTRY_ID
and peef.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
and P_END_DATE BETWEEN peef.EFFECTIVE_START_DATE AND peef.EFFECTIVE_END_DATE
and P_END_DATE BETWEEN petf.EFFECTIVE_START_DATE and petf.EFFECTIVE_END_DATE ;

IF L_ELEMENT_NAME = 'Utility Allowance'

THEN
select Distinct NVL(DECODE(ASS_ATTRIBUTE8,
'A','Allowance',
'P','Payment',
'N','NO',
ASS_ATTRIBUTE8),'NO')
INTO L_ATTRIBUTE_VALUE
from per_all_assignments_f paaf
where paaf.ASSIGNMENT_ID = P_ASSG_ID
and P_END_DATE BETWEEN paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE;
ELSIF L_ELEMENT_NAME = 'Housing Allowance'
THEN
select Distinct NVL(Decode(ASS_ATTRIBUTE7,
'N','NO',
'Y','YES',
ASS_ATTRIBUTE7),'NO')
INTO L_ATTRIBUTE_VALUE
from per_all_assignments_f paaf
where paaf.ASSIGNMENT_ID = P_ASSG_ID
and P_END_DATE BETWEEN paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE;
ELSIF L_ELEMENT_NAME = 'Travel Allowance'
THEN
select Distinct NVL(Decode(ASS_ATTRIBUTE13,
'N','NO',
'Y','YES',
ASS_ATTRIBUTE13),'NO')
INTO L_ATTRIBUTE_VALUE
from per_all_assignments_f paaf
where paaf.ASSIGNMENT_ID = P_ASSG_ID
and P_END_DATE BETWEEN paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE;
ELSE
L_ATTRIBUTE_VALUE := 'No';
End if;

Return L_ATTRIBUTE_VALUE;

EXCEPTION
WHEN OTHERS
THEN
RETURN 'NO';
END GET_ASSG_DFF_VAL;

/*------------ Function to get status of day on given date of Employee

---------------------------*/
FUNCTION GET_DAY_STATUS (
p_assignment_id IN NUMBER,
p_plan_date IN DATE
)
RETURN VARCHAR2
IS
l_day_value VARCHAR2 (200);
l_work_schedule VARCHAR2 (200);
l_day VARCHAR2 (200);
l_user_column_name VARCHAR2 (200);
l_aternate_day VARCHAR2 (100);
BEGIN
BEGIN
l_day := TO_CHAR (p_plan_date, 'DAY');

-- SELECT COUNT (a.pdate)

-- INTO l_aternate_day
-- FROM xxstage.xxlel_employees_shift_detail a,
-- per_all_assignments_f paaf
-- WHERE a.emp_id = paaf.assignment_number
-- AND a.pdate = p_plan_date
-- AND paaf.assignment_id = p_assignment_id
-- AND TRUNC (p_plan_date) BETWEEN paaf.effective_start_date
-- AND paaf.effective_end_date;
-- ----- DBMS_OUTPUT.put_line ( 'l_aternate_day is ' || l_aternate_day) ;
SELECT ass_attribute1
INTO l_work_schedule
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND TRUNC (p_plan_date) BETWEEN effective_start_date
AND effective_end_date;
--- DBMS_OUTPUT.put_line ( 'l_work_schedule is
' || l_work_schedule) ;

EXCEPTION
WHEN OTHERS
THEN
SELECT ass_attribute1
INTO l_work_schedule
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND TRUNC (LAST_DAY(p_plan_date)) BETWEEN effective_start_date
AND effective_end_date;

DBMS_OUTPUT.put_line
('NO work Schedule Assigned To Employee on given date');
END;

BEGIN
SELECT DISTINCT pci.VALUE
INTO l_day_value
FROM pay_user_column_instances_f pci, pay_user_rows_f pur
WHERE pci.user_column_id = l_work_schedule
AND pci.user_row_id = pur.user_row_id
AND pur.row_low_range_or_name = TRIM (l_day); --like l_day;
END;

----DBMS_OUTPUT.put_line ( 'l_day_value is ' || l_day_value) ;

IF l_day_value = '8 ALTERNATE OFF'

THEN

SELECT a.DAY_STATUS
INTO l_aternate_day
FROM xxstage.xxlel_employees_shift_detail a,
per_all_assignments_f paaf
WHERE a.emp_id = paaf.assignment_number
AND a.pdate = p_plan_date
AND paaf.assignment_id = p_assignment_id
AND TRUNC (p_plan_date) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
-----

IF l_aternate_day IN ('SAT','THU')
THEN
l_day_value := 'OFF';
else
l_day_value := '8';

END IF;
END IF;

RETURN l_day_value;
EXCEPTION
WHEN OTHERS
THEN
RETURN '0';
DBMS_OUTPUT.put_line ('In exception ' || l_day_value);
-- RETURN to_char(l_day_value);
END GET_DAY_STATUS ;

/*-------------------------- Function to get total Off days on given date range of

Employee ------------------------------*/
FUNCTION get_total_off_days (
p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
)
RETURN NUMBER
IS
v_days NUMBER := (p_end_date - p_start_date);
l_day_value VARCHAR2 (100);
l_off_day NUMBER := 0;
v_date DATE;
l_total_days NUMBER := 0;
BEGIN
FOR i IN 0 .. v_days
LOOP
v_date := p_start_date + i;

BEGIN
l_day_value :=
xxlam_hr_payroll_all.GET_DAY_STATUS (p_assignment_id, v_date);
DBMS_OUTPUT.put_line ('DAY VALUE ' || l_day_value);
END;

IF l_day_value = 'OFF'
THEN
l_off_day := 1;
ELSE
l_off_day := 0;
END IF;

l_total_days := l_total_days + l_off_day;

END LOOP;
RETURN l_total_days;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
DBMS_OUTPUT.put_line ('In exception ' || l_total_days);

END get_total_off_days;

FUNCTION GET_ANNUAL_ACCURAL_AMT (l_assignment_id IN NUMBER,

l_start_date IN DATE,
l_end_date IN DATE
)
RETURN NUMBER
IS
l_totat_days NUMBER := 0;
l_current_sal NUMBER := 0;
l_annual_vacation_accrual NUMBER := 0;
l_leave_days NUMBER := 0;
l_annual_leave_days NUMBER := 0;
l_maternity_leave_days NUMBER := 0;
l_auth_unpaid_leave_days NUMBER := 0;
l_unauth_unpaid_leave_days NUMBER := 0;

BEGIN

begin
SELECT To_number(paaf.ASS_ATTRIBUTE2)
INTO l_leave_days
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = l_assignment_id
AND TRUNC (l_start_date) BETWEEN effective_start_date AND effective_end_date;
END;

/*************Annaul Leave Maternity leave and Unpaid leaves should be removed

*************************/
----DBMS_OUTPUT.put_line ('asgn id '||l_assignment_id|| 'st date '||
l_start_date|| 'l_end_date'||l_end_date || ' l_leave_days ' ||l_leave_days);

l_annual_leave_days := xxlam_hr_absence_duration.get_no_of_leaves_taken_ass_id
(81,
l_assignment_id,
'Annual Leave',
l_start_date,
l_end_date);

l_auth_unpaid_leave_days :=
xxlam_hr_absence_duration.get_no_of_leaves_taken_ass_id (81,
l_assignment_id,
'Authorised Unpaid Leave',
l_start_date,
l_end_date);

l_unauth_unpaid_leave_days :=
xxlam_hr_absence_duration.get_no_of_leaves_taken_ass_id (81,
l_assignment_id,
'Unauthorised Unpaid Leave',
l_start_date,
l_end_date);

l_maternity_leave_days :=
xxlam_hr_absence_duration.get_no_of_leaves_taken_ass_id (81,
l_assignment_id,
'Maternity Leave',
l_start_date,
l_end_date);

---DBMS_OUTPUT.put_line ('TOTAL NO DAYS of annual leaves ' || l_annual_leave_days);

/************* end Annaul Leave Maternity leave and Unpaid leaves

*************************/

l_totat_days := get_emp_work_plan(l_assignment_id, l_start_date);

----DBMS_OUTPUT.put_line ('work plan ' || l_totat_days );

l_current_sal := xxlam_hr_payroll_all.get_gross_at (l_assignment_id,

l_start_date) / l_totat_days;

---DBMS_OUTPUT.put_line ('Curent sal ' || l_current_sal );

l_annual_vacation_accrual := (l_current_sal *
(l_leave_days - l_annual_leave_days -
l_auth_unpaid_leave_days - l_unauth_unpaid_leave_days -l_maternity_leave_days )
) / 11 ;
--
-- DBMS_OUTPUT.put_line ('l_current_sal' || l_current_sal);

-- DBMS_OUTPUT.put_line ('l_annual_leave_days ' || l_annual_leave_days);

-- DBMS_OUTPUT.put_line ('l_leave_days' || l_leave_days);
-- DBMS_OUTPUT.put_line (' l_annual_vacation_accrual ' ||
l_annual_vacation_accrual);
--

-- DBMS_OUTPUT.put_line ('TOTAL NO DAYS ' || l_totat_days);

RETURN ROUND(NVL(l_annual_vacation_accrual,0));
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;

END GET_ANNUAL_ACCURAL_AMT ;
FUNCTION get_emp_work_plan (
p_assignment_id IN NUMBER,
p_plan_date IN DATE
)
RETURN NUMBER
IS
l_month_days NUMBER := 0;
l_work_schedule VARCHAR2 (200);
l_user_table_name VARCHAR2 (200);
l_user_column_name VARCHAR2 (200);
BEGIN
BEGIN
SELECT ass_attribute1
INTO l_work_schedule
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND TRUNC (p_plan_date) BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('NO work Schedule Assigned To Employee on given
date');
END;
BEGIN
SELECT user_table_name, user_column_name
INTO l_user_table_name, l_user_column_name
FROM pay_user_tables t, pay_user_columns c
WHERE t.user_table_name = 'LAM_HR_WORK_SCHEDULE'
AND c.user_table_id = t.user_table_id
AND c.user_column_id = l_work_schedule;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('NO work Schedule Assigned');
END;

IF l_user_column_name = '5 Days (Thursday, Friday off)'

THEN
l_month_days := 22;
ELSIF l_user_column_name = '5 Days (Friday, Saturday off)'
THEN
l_month_days := 22;
ELSIF l_user_column_name = '5.5 Days (Alternate Saturdays off)'
THEN
l_month_days := 24;
ELSIF l_user_column_name = '5.5 Days (Alternate Thursdays off)'
THEN
l_month_days := 24;
ELSIF l_user_column_name = '6 Days (Friday off)'
THEN
l_month_days := 26;
ELSIF l_user_column_name = '6 Days (Sunday off)'
THEN
l_month_days := 26;

ELSE
l_month_days := 0;
END IF;
RETURN l_month_days;
EXCEPTION
WHEN OTHERS
THEN
NULL;
DBMS_OUTPUT.put_line ('In exception ' || l_month_days);
RETURN l_month_days;
END get_emp_work_plan;

FUNCTION GET_OT_VALUE (P_ASSG_ID IN NUMBER,

P_END_DATE IN DATE
)
RETURN VARCHAR
IS
L_ATTRIBUTE_VALUE VARCHAR(240);
BEGIN

select DECODE(paaf.ASS_ATTRIBUTE3,
'Y','YES',
'N','NO',
paaf.ASS_ATTRIBUTE3)
INTO L_ATTRIBUTE_VALUE
from per_all_assignments_f paaf
where paaf.ASSIGNMENT_ID = P_ASSG_ID
and P_END_DATE BETWEEN paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE;

Return L_ATTRIBUTE_VALUE;

EXCEPTION
WHEN OTHERS
THEN
RETURN 'NO';
END ;

FUNCTION GET_TICKET_FLAG ( p_assg_id in NUMBER,

p_date_start in DATE,
p_date_end in DATE

)
RETURN VARCHAR2
IS

l_ticket_req VARCHAR2 (240);

BEGIN
l_ticket_req := 'N';
BEGIN
SELECT 'n' --- PAA.ATTRIBUTE10 ------ENABLE IT ONCE TO GIVE TICKET
INTO l_ticket_req
FROM per_all_assignments_f paaf
,per_absence_attendances paa
,per_absence_attendance_types paat
WHERE 1 = 1
AND paaf.person_id = paa.person_id
AND paa.ABSENCE_ATTENDANCE_TYPE_ID = paat.ABSENCE_ATTENDANCE_TYPE_ID
AND paat.NAME like 'Annual Leave'
---AND paa.attribute2 = 'Y'
AND paa.attribute3 = 'Y' --CHANGES DONE BY KHURAM ON 12-jan-14
AND PAA.ATTRIBUTE10 = 'Y' --CHANGES DONE BY KHURAM ON 12-jan-14
AND paaf.ASSIGNMENT_ID = p_assg_id
and paa.DATE_START between p_date_start and p_date_end
and p_date_end between paaf.EFFECTIVE_START_DATE and
paaf.EFFECTIVE_END_DATE ;

EXCEPTION WHEN OTHERS THEN

l_ticket_req := 'N';
end;

RETURN l_ticket_req ;
END ;

FUNCTION get_ticket_accrual_payment (
p_assignment_id NUMBER,
p_type VARCHAR2,
p_start_date DATE,
p_end_date DATE
)
RETURN NUMBER
AS
l_person_id NUMBER;
l_nod NUMBER := (p_end_date) - (p_start_date);
l_date DATE;
l_days NUMBER;
l_accrual_date DATE;
l_hire_date DATE;
l_age NUMBER;
l_person_type VARCHAR2 (30);
l_family_status VARCHAR2 (10);
l_ticket_amount NUMBER := 0;
l_ticket_sector VARCHAR2 (30);
l_dep_ticket_class VARCHAR2 (30);
l_ticket_to VARCHAR2 (30);
l_ticket_adult NUMBER := 0;
l_ticket_child NUMBER := 0;
l_ticket_spouse NUMBER := 0;
l_ticket_a_balance NUMBER := 0;
l_ticket_c_balance NUMBER := 0;
l_no_of_tickets_a NUMBER := 0;
l_no_of_tickets_c NUMBER := 0;
l_absence NUMBER := 0;
l_accrued_ticket_s NUMBER := 0;
l_start_date DATE;
l_end_date DATE;
l_curr_month_amt NUMBER := 0;
l_curr_mon_a NUMBER := 0;
l_curr_mon_c NUMBER := 0;
l_accrued_ticket_ca NUMBER := 0;
l_bg_id NUMBER;
l_ticket_a_dep_balance NUMBER := 0;
ln_payroll_id NUMBER := 0;
l_fare NUMBER := 0;
l_tax NUMBER := 0;
l_ticket_eligible VARCHAR2 (30) := 'T';

-- p_assignment_id NUMBER;
-- p_start_date DATE;
-- p_end_date DATE;

CURSOR c_rel (p_person_id IN NUMBER, p_date IN DATE)

IS
SELECT pcr.person_id, pcr.contact_person_id, pcr.contact_type,
pcr.date_start, pap.date_of_birth
--- pcr.cont_attribute2 ticket_eligibility
FROM per_contact_relationships pcr, per_all_people_f pap
WHERE 1 = 1
AND pap.person_id = pcr.contact_person_id
AND pcr.person_id = p_person_id
AND pcr.contact_type IN ('S', 'C')
AND p_date BETWEEN pap.effective_start_date AND pap.effective_end_date
AND p_date BETWEEN NVL (pcr.date_start, pap.start_date)
AND NVL (pcr.date_end, TO_DATE ('31-DEC-4712'));
BEGIN
--- p_assignment_id := 61;
---- p_start_date := TO_DATE ('01-nov-2013');
---p_end_date := TO_DATE ('30-nov-2013');
l_date := p_end_date;
l_start_date := p_start_date;
l_end_date := p_end_date;
DBMS_OUTPUT.put_line (p_start_date);
DBMS_OUTPUT.put_line (p_end_date);

SELECT DISTINCT person_id, business_group_id

INTO l_person_id, l_bg_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND l_date BETWEEN effective_start_date AND effective_end_date;

DBMS_OUTPUT.put_line ('person id ' || l_person_id);

DBMS_OUTPUT.put_line ('BG is ' || l_bg_id);

BEGIN

select ASS_ATTRIBUTE10 into l_ticket_eligible

from per_All_assignments_f
where assignment_id = p_assignment_id
AND TRUNC (p_start_date) BETWEEN TRUNC (effective_start_date)
AND TRUNC (effective_end_date);
DBMS_OUTPUT.put_line ('ticket
Eligibility ' || l_ticket_eligible);
IF (l_ticket_eligible IS null) then
l_ticket_eligible := 'T';

END IF ;

EXCEPTION
WHEN OTHERS
THEN
l_ticket_eligible := 'T';
DBMS_OUTPUT.put_line ('NO ticket Eligibility ' || l_ticket_eligible);
END;
IF l_ticket_eligible <> 'N' THEN

BEGIN
SELECT attribute1
INTO l_ticket_sector
FROM per_all_people_f
WHERE person_id = l_person_id
AND TRUNC (p_start_date) BETWEEN TRUNC (effective_start_date)
AND TRUNC (effective_end_date);

--- DBMS_OUTPUT.put_line ('l_ticket_sector' || l_ticket_sector);

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('NO ticket Sector');
END;

BEGIN

SELECT character2, character3

INTO l_fare, l_tax
FROM qa_results_v b
WHERE NAME = 'LAMPRELL TICKET SECTOR'
AND character1 =
(SELECT meaning
FROM hr_lookups
WHERE lookup_type = 'LAM_HR_SYS_APP_SECTOR'
AND enabled_flag = 'Y'
AND application_id = 800
AND lookup_code = l_ticket_sector)
AND last_update_date = (SELECT MAX (last_update_date)
FROM qa_results_v a
WHERE a.character1 = b.character1)
GROUP BY character1, last_update_date, character2, character3;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('NO ticket Sector RATE' );
END;

l_ticket_amount := l_fare + l_tax; ---- self ticket for employee

---DBMS_OUTPUT.put_line ( 'sector is ' || CHARACTER1);
-- DBMS_OUTPUT.put_line (' fare ' || l_fare);
-- DBMS_OUTPUT.put_line ('tax is ' || l_tax);
-- DBMS_OUTPUT.put_line ('self ticket is ' || l_ticket_amount);

-- -- Checking Family Status

BEGIN
SELECT ass_attribute5
INTO l_family_status
FROM per_all_assignments_f paf
WHERE 1 = 1
AND assignment_id = p_assignment_id
AND l_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_family_status := 'N';
END;

IF l_family_status = 'F'
THEN
DBMS_OUTPUT.put_line ('family ststs' ||l_family_status );

FOR rec_rel IN c_rel (l_person_id, l_date)

LOOP
BEGIN
-- For Spouse
IF rec_rel.contact_type = 'S'
THEN
BEGIN
-- DBMS_OUTPUT.put_line ('Spouce person id is ' || l_person_id);
-- DBMS_OUTPUT.put_line ( 'Spouce contact person id is '
-- || rec_rel.contact_person_id
-- );

SELECT DISTINCT segment1

INTO l_ticket_spouse
FROM per_analysis_criteria pac,
fnd_id_flex_structures_vl fflex,
per_contact_relationships pcr,
per_person_analyses ppa
WHERE 1 = 1
AND ppa.person_id = pcr.contact_person_id
AND UPPER (fflex.id_flex_structure_code) IN UPPER

('LAM_HR_MED_INS_INFO'
)
AND fflex.id_flex_num = pac.id_flex_num
AND ppa.analysis_criteria_id =
pac.analysis_criteria_id
AND l_date BETWEEN NVL (ppa.date_from,
TRUNC (l_date)
)
AND NVL (date_to, TRUNC (l_date))
AND ppa.person_id = l_person_id
AND segment1 = rec_rel.contact_person_id;

---DBMS_OUTPUT.put_line ( 'got medical record '

---|| l_ticket_spouse
---- );
l_ticket_amount := l_ticket_amount + l_fare + l_tax;
---DBMS_OUTPUT.put_line ('self + spouce ' || l_ticket_amount);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('No medical record');
END;
END IF;

-- -- For Child
IF rec_rel.contact_type = 'C'
-- ----AND rec_rel.ticket_eligibility = 'Y'
THEN
---- DBMS_OUTPUT.put_line ('child person id is ' || l_person_id);

BEGIN
SELECT DISTINCT segment1
INTO l_ticket_child
FROM per_analysis_criteria pac,
fnd_id_flex_structures_vl fflex,
per_contact_relationships pcr,
per_person_analyses ppa
WHERE 1 = 1
AND ppa.person_id = pcr.contact_person_id
AND UPPER (fflex.id_flex_structure_code) IN UPPER

--- DBMS_OUTPUT.put_line ('self + spouce ' ||

l_ticket_amount );
SELECT MONTHS_BETWEEN (l_date, rec_rel.date_of_birth) / 12
INTO l_age
FROM DUAL;

DBMS_OUTPUT.put_line (' age is ' || l_age);

IF l_age <= 2
THEN
l_ticket_amount :=
l_ticket_amount + (l_fare * 0.25)
+ l_tax; -- 25 % of the fare
ELSIF l_age > 2 AND l_age <= 18
-- AND rec_rel.ticket_eligibility = 'Y'
THEN
l_ticket_amount :=
l_ticket_amount + (l_fare * 0.75)
+ l_tax; -- 75 % of the fare
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('No medical record');
END;

DBMS_OUTPUT.put_line ('total ticket is ' || l_ticket_amount);

END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No Data Found');
NULL;
END;
END LOOP;

--
-- DBMS_OUTPUT.put_line ('payment type is '||p_type );
----- Making Accrual or Payment
-- IF UPPER (p_type) = 'ACCRUAL'
-- THEN
-- l_ticket_amount := l_ticket_amount / 11;
--
-- END IF;
END IF;

DBMS_OUTPUT.put_line ('payment type is '||p_type );

--- Making Accrual or Payment
IF UPPER (p_type) = 'ACCRUAL'
THEN
l_ticket_amount := l_ticket_amount / 11;

END IF;

END IF ;

RETURN ROUND(l_ticket_amount,0);
--END;
END GET_TICKET_ACCRUAL_PAYMENT;

FUNCTION CAL_ADV_LEAVE_SALARY(
p_assignment_id IN NUMBER,
p_leave_name IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_leave_days OUT NUMBER
)
RETURN NUMBER
IS

v_salary NUMBER := 0;
v_gross NUMBER := 0;
v_basic NUMBER := 0;
v_days NUMBER := (p_end_date - p_start_date);
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_hrs_per_day NUMBER := 0;
v_otl_hrs NUMBER (5, 2) := 0;
v_curr_otl_hrs NUMBER (5, 2) := 0;
v_week_hrs NUMBER := 0;
v_curr_week_hrs NUMBER := 0;
v_holiday NUMBER := 0;
v_week_pay NUMBER := 0;
v_holi_pay NUMBER := 0;
v_curr_holiday NUMBER := 0;
v_curr_maternity NUMBER := 0;
v_curr_maternity_pay NUMBER := 0;
v_curr_sick NUMBER := 0;
v_curr_sick_pay NUMBER := 0;
v_curr_sick_ded NUMBER := 0;
v_total_sick_days NUMBER := 0;
v_business_group_id NUMBER := 0;
l_period_service NUMBER := 0;
v_person_type VARCHAR2 (100);
v_skip_holiday NUMBER := 0;
v_employee_number VARCHAR2 (250);
v_curr_sick_pay_p NUMBER;
v_curr_sick_ded_p NUMBER;
v_leave_salary NUMBER := 0;
t_leave_salary NUMBER := 0 ;
v_leave_days NUMBER := 0 ;
t_leave_days NUMBER := 0 ;

BEGIN
v_basic := 0;
--v_mth_days := XXLAM_HR_PAYROLL_ALL.get_month_days (p_assignment_id,
p_start_date);
v_person_type := XXLAM_HR_PAYROLL_ALL.get_person_type_ass (p_assignment_id,
p_end_date);
p_leave_days := 0;
p_week_holi_pay := 0;
---p_month_value := 0;
p_maternity_pay := 0;
p_maternity_days := 0;
p_sick_pay := 0;
p_sick_ded := 0;
p_sick_days := 0;
v_business_group_id := hr_general.get_business_group_id ();
v_business_group_id :=81;
DBMS_OUTPUT.put_line ('Business Grp id ' ||v_business_group_id);
-- || v_date
-- );
--v_business_group_id := 81;
FOR i IN 0 .. v_days
LOOP
v_date := p_start_date + i;
v_curr_otl_hrs := 0;
v_curr_week_hrs := 0;
v_curr_holiday := 0;
v_curr_maternity_pay := 0;
v_curr_sick_pay := 0;
v_curr_sick_ded := 0;
v_curr_sick_pay_p := 0;
v_curr_sick_ded_p := 0;

BEGIN
v_gross := XXLAM_HR_PAYROLL_ALL.get_gross_at (p_assignment_id, v_date);
DBMS_OUTPUT.put_line ( 'Gross Salary is ' || v_gross || '--- Days in
month is ----'|| to_char( v_days+1));
END;

begin
v_mth_days := XXLAM_HR_PAYROLL_ALL.get_month_days (p_assignment_id,
v_date);

DBMS_OUTPUT.put_line ('Month Days ' ||v_mth_days);

END;

/******** Annual Vacation /

IF (p_leave_name = 'Annual Leave' ) THEN

v_leave_days :=
xxlam_hr_absence_duration.get_no_of_advance_leaves_taken
(v_business_group_id,
p_assignment_id,
p_leave_name, -- 'Annual
Leave',
v_date,
v_date
);

v_leave_salary := v_leave_days * (v_gross / v_mth_days);

t_leave_days := t_leave_days + v_leave_days;

t_leave_salary := t_leave_salary + v_leave_salary;

p_leave_days :=t_leave_days ;
DBMS_OUTPUT.PUT_LINE ( 'v_mth_days are ' ||v_mth_days);
-- DBMS_OUTPUT.PUT_LINE ( ' ' );
-- DBMS_OUTPUT.PUT_LINE ( ' ' );
-- DBMS_OUTPUT.PUT_LINE ( ' ' );
DBMS_OUTPUT.PUT_LINE ( ' leave salary is ' ||t_leave_salary ||' --- Total
leave Days are ---- '|| t_leave_days ||' v_days are '||v_days);
--
End IF;
/************************ Annual Vacation ***************/

END LOOP;
RETURN t_leave_salary;
END CAL_ADV_LEAVE_SALARY;

/*-------------------------------------- Food
Allowance--------------------------------------------*/

FUNCTION calculate_food_allowance (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_food_all IN NUMBER
)
RETURN NUMBER
IS
v_salary NUMBER := 0;
v_gross NUMBER := 0;
v_basic NUMBER := 0;
v_days NUMBER;
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_hrs_per_day NUMBER := 0;
v_otl_hrs NUMBER (5, 2) := 0;
v_curr_otl_hrs NUMBER (5, 2) := 0;
v_week_hrs NUMBER := 0;
v_curr_week_hrs NUMBER := 0;
v_holiday NUMBER := 0;
v_week_pay NUMBER := 0;
v_holi_pay NUMBER := 0;
v_curr_holiday NUMBER := 0;
v_curr_maternity NUMBER := 0;
v_curr_maternity_pay NUMBER := 0;
v_curr_sick NUMBER := 0;
v_curr_sick_pay NUMBER := 0;
v_curr_sick_ded NUMBER := 0;
v_total_sick_days NUMBER := 0;
v_business_group_id NUMBER := 0;
l_period_service NUMBER := 0;
v_person_type VARCHAR2 (100);
v_skip_holiday NUMBER := 0;
v_employee_number VARCHAR2 (250);
v_curr_sick_pay_p NUMBER;
v_curr_sick_ded_p NUMBER;
l_day_status VARCHAR2 (250);
i NUMBER := 0;
BEGIN
v_basic := 0;
v_days := p_end_date - p_start_date;
v_mth_days :=
xxlam_hr_payroll_all.get_month_days (p_assignment_id, p_start_date);
DBMS_OUTPUT.put_line ('v_mth_days:' || v_mth_days);
DBMS_OUTPUT.put_line ('v_person_type:' || v_person_type);
v_business_group_id := hr_general.get_business_group_id ();
v_business_group_id := 81;
v_mth_value := p_food_all;

BEGIN
l_day_status :=
xxlam_hr_payroll_all.get_day_status (p_assignment_id, v_date);
END;

--- p_month_value := v_mth_value;

BEGIN
SELECT absence_days
INTO v_curr_holiday
FROM per_absence_attendances paa1
WHERE 1 = 1
AND person_id = (SELECT distinct person_id
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
and v_date between paaf.EFFECTIVE_START_DATE and
paaf.EFFECTIVE_END_DATE)
-- (SELECT DISTINCT person_id
-- FROM xxlam_hr_emp_data_all
-- WHERE assignment_id = p_assignment_id)
AND v_date BETWEEN date_start AND date_end
AND absence_attendance_type_id not in( '72','68'); ----TOIL is
excluded;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--- DBMS_OUTPUT.put_line( 'I am adding salary ' || v_date);
IF l_day_status <> 'OFF'
THEN
v_sal_per_day :=
ROUND (v_sal_per_day + (v_mth_value) / v_mth_days,
3);
END IF;
WHEN OTHERS
THEN
NULL;
END;
END LOOP;

RETURN v_sal_per_day;
END;
/*====================== Function use to get the employee OT CAP
================================*/

FUNCTION GET_OT_CAP(
p_assignment_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
)
RETURN NUMBER
is

l_OT_CAP NUMBER := 0 ;
l_OFF_DAYS NUMBER := 0 ;
l_TOT_WORK_DAYS NUMBER := 0 ;
l_TOT_MON_DAYS NUMBER := 0 ;
l_LEAVES NUMBER := 0 ;
l_P_OT_CAP NUMBER := 0 ;

BEGIN

BEGIN
select NVL(TO_NUMBER(ASS_ATTRIBUTE25),-1)
INTO l_OT_CAP
from per_all_assignments_f paaf
where 1=1
and paaf.ASSIGNMENT_ID = p_assignment_id
--- and paaf.EFFECTIVE_START_DATE between p_start_date and p_end_date
and p_end_date between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_end_DATE
;

EXCEPTION
WHEN OTHERS
THEN
l_OT_CAP := -1;
DBMS_OUTPUT.put_line
('NO OT CAP ASSIGN TO EMPLOYEE');

END;
IF l_OT_CAP = -1
THEN l_P_OT_CAP := -1;
ELSE
l_OFF_DAYS :=
XXLAM_HR_PAYROLL_ALL.get_total_off_days(p_assignment_id,p_start_date,p_end_date) ;

SELECT (p_end_date - p_start_date) + 1 -

(XXLAM_HR_PAYROLL_ALL.get_total_off_days(p_assignment_id,p_start_date,p_end_date))
INTO l_TOT_MON_DAYS
FROM DUAL;

l_LEAVES := XXLAM_HR_ABSENCE_DURATION.get_no_of_leaves
(81,p_assignment_id,p_start_date,p_end_date);

l_TOT_WORK_DAYS := (l_TOT_MON_DAYS - l_LEAVES);

l_P_OT_CAP := (l_TOT_WORK_DAYS/l_TOT_MON_DAYS) * l_OT_CAP;

END IF;
RETURN NVL(l_P_OT_CAP,-1);
EXCEPTION
WHEN OTHERS
THEN
RETURN '-1';

END;

/*============================= Calculate Complete SRA Amount IN quater

======================================== */

FUNCTION calculate_sra_monthly_value (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE
)
RETURN NUMBER
IS
v_days NUMBER;
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_business_group_id NUMBER := 0;
l_day_status VARCHAR2 (250);
l_una_leave NUMBER := 0;
i NUMBER := 0;
BEGIN
v_days := p_end_date - p_start_date;
v_business_group_id := hr_general.get_business_group_id ();
v_business_group_id := 81;

FOR i IN 0 .. v_days
LOOP
v_date := p_start_date + i;

BEGIN
v_mth_days :=
xxlam_hr_payroll_all.get_month_days (p_assignment_id, v_date);
DBMS_OUTPUT.put_line ('v_mth_days:' || v_mth_days);
l_una_leave :=
xxlam_hr_absence_duration.get_no_of_leaves_taken_ass_id
(v_business_group_id,
p_assignment_id,
'Unauthorised Unpaid Leave',
v_date,
v_date
)
+ xxlam_hr_absence_duration.get_no_of_leaves_taken_ass_id
(v_business_group_id,
p_assignment_id,
'Authorised Unpaid Leave',
v_date,
v_date
);
DBMS_OUTPUT.put_line ('l_una_leave:' || l_una_leave);
l_day_status :=
xxlam_hr_payroll_all.get_day_status (p_assignment_id, v_date);
DBMS_OUTPUT.put_line ('l_day_status:' || l_day_status);
v_mth_value :=
xxlam_hr_payroll_all.get_screen_entry_value_at
(p_assignment_id,
p_element_entry_id,
'Monthly Value',
v_date
);
DBMS_OUTPUT.put_line ('v_mth_value:' || v_mth_value);
END;

BEGIN
IF (l_day_status <> 'OFF' AND l_una_leave <> 1)
THEN
v_sal_per_day :=
ROUND (v_sal_per_day + (v_mth_value) / v_mth_days,
3);
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;

RETURN round(v_sal_per_day);
END;

/*** =========================== Food ALLowance DAYS

=============================== ***/

FUNCTION GET_FOOD_ALLOANCE_DAYS ( p_assg_id in NUMBER,

p_date_start in DATE,
p_date_end in DATE

)
RETURN Number
IS
l_FOOD_ALL_DAYS number;

BEGIN

select FOOD_ALLOWANCE into l_FOOD_ALL_DAYS

from xxstage.xxlel_employee_overtime a
,per_all_assignments_f paaf
where a.FOOD_ALLOWANCE is not null
and a.EMP_ID = paaf.ASSIGNMENT_NUMBER
and paaf.ASSIGNMENT_ID = p_assg_id
and to_date(a.PDATE,'YYYY-MM-DD') between p_date_start and p_date_end
AND p_date_end between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE;

RETURN l_FOOD_ALL_DAYS;

EXCEPTION
WHEN OTHERS
THEN RETURN 0;

END;

FUNCTION GET_PAYSLIP_MISC_REMARK(
p_element_type_id IN NUMBER,
p_ELEMENT_ENTRY_ID IN NUMBER,
p_assignment_action_id IN NUMBER
)
RETURN CHAR
is

l_REMARKS VARCHAR(100);

Begin

SELECT distinct (prrv1.result_value) into l_REMARKS

FROM pay_input_values_f pivf1,
pay_element_types_f petf1,
pay_run_results prr1,
pay_run_result_values prrv1
WHERE prr1.element_type_id = petf1.element_type_id
AND petf1.element_type_id = pivf1.element_type_id
AND prr1.run_result_id = prrv1.run_result_id
AND pivf1.input_value_id = prrv1.input_value_id
AND pivf1.NAME = 'Remarks'
AND petf1.element_type_id = p_element_type_id
and prr1.ELEMENT_ENTRY_ID = p_ELEMENT_ENTRY_ID
AND prr1.assignment_action_id = p_assignment_action_id ;

return l_REMARKS ;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;

END;

FUNCTION GET_HSBC_PAYMENT_CODE ( p_assignment_id IN NUMBER,

P_PER_PAY_ID IN NUMBER
)
RETURN CHAR
is

L_ORG_PAY_ID NUMBER;
L_ORG_PAY_NAME VARCHAR2(200);
L_PERSONAL_PAY_ID NUMBER;
L_PERSONAL_PAY_CODE VARCHAR2(100);
l_VISA_CATA VARCHAR2(100);
L_PAYMENT_CODE VARCHAR2(100);
BEGIN

select
pppmf.ORG_PAYMENT_METHOD_ID,popmf.ORG_PAYMENT_METHOD_NAME,pppmf.PERSONAL_PAYMENT_ME
THOD_ID,pea.SEGMENT1
INTO L_ORG_PAY_ID,L_ORG_PAY_NAME,L_PERSONAL_PAY_ID,L_PERSONAL_PAY_CODE
from pay_personal_payment_methods_f pppmf
,pay_org_payment_methods_f popmf
,pay_external_accounts pea
,XXLEL_ALL_ASSIGNMENTS_V paaf
where 1=1
and pppmf.ORG_PAYMENT_METHOD_ID = popmf.ORG_PAYMENT_METHOD_ID
and pppmf.EXTERNAL_ACCOUNT_ID = pea.EXTERNAL_ACCOUNT_ID
and pppmf.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
and pppmf.ORG_PAYMENT_METHOD_ID = 83
and paaf.ASSIGNMENT_ID = p_assignment_id
and pppmf.PERSONAL_PAYMENT_METHOD_ID = P_PER_PAY_ID
and sysdate between pppmf.EFFECTIVE_START_DATE and pppmf.EFFECTIVE_END_DATE
and sysdate between popmf.EFFECTIVE_START_DATE and popmf.EFFECTIVE_END_DATE;

select NVL(pac.segment9,'NO VISA CATA')

INTO l_VISA_CATA
FROM per_special_info_types_v sit,
fnd_id_flex_structures_vl fifs,
per_analysis_criteria pac,
per_person_analyses ppa,
per_all_people_f papf,
XXLEL_ALL_ASSIGNMENTS_V paaf
WHERE sit.id_flex_num = 50433
AND papf.person_id = ppa.person_id
AND sit.id_flex_num = fifs.id_flex_num
AND fifs.id_flex_num = pac.id_flex_num
AND pac.analysis_criteria_id = ppa.analysis_criteria_id
AND PAPF.PERSON_ID = paaf.PERSON_ID
and paaf.ASSIGNMENT_ID = p_assignment_id
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date;

IF L_PERSONAL_PAY_CODE = 'HSBC' AND l_VISA_CATA IN ('HAFZA','JAFZA')

then
L_PAYMENT_CODE := '11111111';
ELSIF L_PERSONAL_PAY_CODE = 'HSBC' AND l_VISA_CATA NOT IN ('HAFZA','JAFZA')
THEN
L_PAYMENT_CODE := 'BBMEDUB';
ELSIF L_PERSONAL_PAY_CODE <> 'HSBC'
then
select FLVV.ATTRIBUTE3 INTO L_PAYMENT_CODE
from pay_personal_payment_methods_f pppmf
,pay_external_accounts pea
,FND_LOOKUP_VALUES_VL FLVV
,XXLEL_ALL_ASSIGNMENTS_V paaf
where pppmf.EXTERNAL_ACCOUNT_ID = pea.EXTERNAL_ACCOUNT_ID
and pea.SEGMENT1 = FLVV.LOOKUP_CODE
and FLVV.LOOKUP_TYPE = 'LAM_HR_SYS_BANK_RCODE_NWP'
and pppmf.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
and paaf.ASSIGNMENT_ID = p_assignment_id;
ELSE
L_PAYMENT_CODE := '00000000';
END IF;

RETURN L_PAYMENT_CODE;

EXCEPTION WHEN
OTHERS then RETURN NULL;

END;

FUNCTION GET_CURRENCY_EXCHANGE_RATE ( P_CURRENCY IN CHAR,

P_DATE IN DATE)
RETURN NUMBER

L_COVERSION_RATE NUMBER := 1;

BEGIN

select DISTINCT(SHOW_CONVERSION_RATE) INTO L_COVERSION_RATE

from GL_DAILY_RATES_V GDR
WHERE 1=1
and GDR.FROM_CURRENCY = 'AED'
and GDR.TO_CURRENCY = P_CURRENCY
and GDR.USER_CONVERSION_TYPE = 'Corporate'
and GDR.CONVERSION_DATE = P_DATE ;

RETURN L_COVERSION_RATE;

EXCEPTION WHEN
OTHERS THEN RETURN 1;

END ;

FUNCTION calculate_food_allo_OM_POOL (
p_assignment_id IN NUMBER,
p_element_entry_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_food_all IN NUMBER
)
RETURN NUMBER
IS
v_salary NUMBER := 0;
v_gross NUMBER := 0;
v_basic NUMBER := 0;
v_days NUMBER;
v_date DATE;
v_mth_days NUMBER;
v_mth_value NUMBER := 0;
v_sal_per_day NUMBER := 0;
v_hrs_per_day NUMBER := 0;
v_otl_hrs NUMBER (5, 2) := 0;
v_curr_otl_hrs NUMBER (5, 2) := 0;
v_week_hrs NUMBER := 0;
v_curr_week_hrs NUMBER := 0;
v_holiday NUMBER := 0;
v_week_pay NUMBER := 0;
v_holi_pay NUMBER := 0;
v_curr_holiday NUMBER := 0;
v_curr_maternity NUMBER := 0;
v_curr_maternity_pay NUMBER := 0;
v_curr_sick NUMBER := 0;
v_curr_sick_pay NUMBER := 0;
v_curr_sick_ded NUMBER := 0;
v_total_sick_days NUMBER := 0;
v_business_group_id NUMBER := 0;
l_period_service NUMBER := 0;
v_person_type VARCHAR2 (100);
v_skip_holiday NUMBER := 0;
v_employee_number VARCHAR2 (250);
v_curr_sick_pay_p NUMBER;
v_curr_sick_ded_p NUMBER;
l_day_status VARCHAR2 (250);
i NUMBER := 0;
BEGIN
v_basic := 0;
v_days := p_end_date - p_start_date;
v_mth_days :=
xxlam_hr_payroll_all.get_month_days (p_assignment_id, p_start_date);
DBMS_OUTPUT.put_line ('v_mth_days:' || v_mth_days);
DBMS_OUTPUT.put_line ('v_person_type:' || v_person_type);
v_business_group_id := hr_general.get_business_group_id ();
v_business_group_id := 81;
v_mth_value := p_food_all;

BEGIN
l_day_status :=
xxlam_hr_payroll_all.get_day_status (p_assignment_id, v_date);
END;

--- p_month_value := v_mth_value;

BEGIN
SELECT absence_days
INTO v_curr_holiday
FROM per_absence_attendances paa1
WHERE 1 = 1
AND person_id = (SELECT distinct person_id
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
and v_date between paaf.EFFECTIVE_START_DATE and
paaf.EFFECTIVE_END_DATE)
-- (SELECT DISTINCT person_id
-- FROM xxlam_hr_emp_data_all
-- WHERE assignment_id = p_assignment_id)
AND v_date BETWEEN date_start AND date_end
AND absence_attendance_type_id not in( '72','68','71','67','62');
----TOIL, Half Day Toil, Sick Leave , Half Day Sick Leave,Authorised Unpaid Leaves
excluded;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--- DBMS_OUTPUT.put_line( 'I am adding salary ' || v_date);
IF l_day_status <> 'OFF'
THEN
v_sal_per_day :=
ROUND (v_sal_per_day + (v_mth_value) / v_mth_days,
3);
END IF;
WHEN OTHERS
THEN
NULL;
END;
END LOOP;

RETURN v_sal_per_day;
END;
END xxlam_hr_payroll_all;
/

===========================

select employee_number,full_name,
XXLAM_HR_PAYROLL_ALL.get_month_days (paaf.assignment_id, '28-FEB-2014')
"FEB-14 Working Days",
XXLAM_HR_PAYROLL_ALL.get_month_days (paaf.assignment_id, '31-MAR-2014')
"MAR-14 Working Days",
from per_all_people_f papf,
per_all_assignments_f paaf
where papf.person_id = paaf.person_id
and sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
and sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE
order by employee_number

select XXLAM_HR_PAYROLL_ALL.get_month_days (8034, '28-feb-2014') from dual;

Accrual Plan Set Up
100% (1)
Accrual Plan Set Up
7 pages
Sync Assignment Costing and Create Auto User With Responsibility
No ratings yet
Sync Assignment Costing and Create Auto User With Responsibility
41 pages
Payroll Data Queries Guide
No ratings yet
Payroll Data Queries Guide
5 pages
Api To Update Supervisor
No ratings yet
Api To Update Supervisor
3 pages
HRMS Database Table Overview
No ratings yet
HRMS Database Table Overview
6 pages
BG Absence Duration Formula
No ratings yet
BG Absence Duration Formula
7 pages
Fast Formula 03 - Writing Fast Formulas
100% (1)
Fast Formula 03 - Writing Fast Formulas
27 pages
PDF Oracle Hrms Uk Payroll r12
No ratings yet
PDF Oracle Hrms Uk Payroll r12
30 pages
70523-Implement Custom Person Numbering Using FastFormula
No ratings yet
70523-Implement Custom Person Numbering Using FastFormula
5 pages
Payroll ERD
100% (1)
Payroll ERD
1 page
BG Absence Duration
No ratings yet
BG Absence Duration
7 pages
Oracle HRMS Payroll Tables Guide
100% (1)
Oracle HRMS Payroll Tables Guide
38 pages
40600-HRMS User Hooks
No ratings yet
40600-HRMS User Hooks
25 pages
FAQ's Oracle Hrms
No ratings yet
FAQ's Oracle Hrms
15 pages
Working FF
No ratings yet
Working FF
1 page
Sample Setup For Case Scenarios in Oracle Payroll Loan PDF
No ratings yet
Sample Setup For Case Scenarios in Oracle Payroll Loan PDF
8 pages
Basic Salary PROD
No ratings yet
Basic Salary PROD
22 pages
Oracle Alerts Guide for TCS Staff
No ratings yet
Oracle Alerts Guide for TCS Staff
35 pages
Global Absence FastFormula User Guide 201804130 PDF
100% (1)
Global Absence FastFormula User Guide 201804130 PDF
114 pages
HRMS Tables For Technical - HRMS-Msalah
No ratings yet
HRMS Tables For Technical - HRMS-Msalah
78 pages
Oracle HRMS Setup For Singapore
No ratings yet
Oracle HRMS Setup For Singapore
33 pages
Oracle Fusion Payroll Create Loan Using Reference Code 1718103834
No ratings yet
Oracle Fusion Payroll Create Loan Using Reference Code 1718103834
10 pages
How To Create Eit in Oracle Hrms
No ratings yet
How To Create Eit in Oracle Hrms
5 pages
OTL Setups - Worked Queries
No ratings yet
OTL Setups - Worked Queries
29 pages
Absence Calculation
No ratings yet
Absence Calculation
12 pages
Payroll Process Flow
0% (1)
Payroll Process Flow
10 pages
Oracle Payroll Basics for Beginners
100% (1)
Oracle Payroll Basics for Beginners
8 pages
To Delete Pending Leaves
0% (1)
To Delete Pending Leaves
3 pages
FusionAbsenceManagementREL92 SampleFastFormulas
100% (2)
FusionAbsenceManagementREL92 SampleFastFormulas
67 pages
Functions Used in Oracle HRMS Fast Formula
No ratings yet
Functions Used in Oracle HRMS Fast Formula
9 pages
2 Rollback Element Creation and Retry Part 2
100% (1)
2 Rollback Element Creation and Retry Part 2
16 pages
Assignment Time Information
No ratings yet
Assignment Time Information
6 pages
Oracle EBS R12 Payroll Guide
No ratings yet
Oracle EBS R12 Payroll Guide
96 pages
Les03 - Basic Fast Formula Syntax
No ratings yet
Les03 - Basic Fast Formula Syntax
34 pages
Element Termination Rules in Oracle HRMS
No ratings yet
Element Termination Rules in Oracle HRMS
2 pages
Arrays in Fast Formula
No ratings yet
Arrays in Fast Formula
12 pages
Oracle Alert Email Setup Guide
No ratings yet
Oracle Alert Email Setup Guide
12 pages
Payroll Processes: Archive Periodic Payroll Results
0% (1)
Payroll Processes: Archive Periodic Payroll Results
5 pages
OTL Setup - 88 Blog
No ratings yet
OTL Setup - 88 Blog
16 pages
Oracle HRMS Payroll Tables
No ratings yet
Oracle HRMS Payroll Tables
21 pages
Les02 - Fast Formula Overview
No ratings yet
Les02 - Fast Formula Overview
23 pages
Understanding Skip Rule
100% (1)
Understanding Skip Rule
5 pages
HCM Global Absence FF Guide 2209568
No ratings yet
HCM Global Absence FF Guide 2209568
105 pages
Use GET VALUE SET in Calculated Ratings Fast Formula
No ratings yet
Use GET VALUE SET in Calculated Ratings Fast Formula
2 pages
Core HRMS
No ratings yet
Core HRMS
95 pages
Retropay (Enhanced) and Retro-Notification Report (Enhanced) ID 305663-1
No ratings yet
Retropay (Enhanced) and Retro-Notification Report (Enhanced) ID 305663-1
8 pages
Ab MGMT
No ratings yet
Ab MGMT
61 pages
Steps To Enable Context Sensitive DFF Based On Absence Type in Absence Form and Page
No ratings yet
Steps To Enable Context Sensitive DFF Based On Absence Type in Absence Form and Page
10 pages
How To Enable Approval For SIT EIT
No ratings yet
How To Enable Approval For SIT EIT
7 pages
OTL Archive and Purge Guide
No ratings yet
OTL Archive and Purge Guide
17 pages
BPM Approvals Variation
50% (2)
BPM Approvals Variation
19 pages
Encrypted Document Analysis
No ratings yet
Encrypted Document Analysis
15 pages
Les04 - Basic Fast Formula Structure
No ratings yet
Les04 - Basic Fast Formula Structure
17 pages
Payroll International
No ratings yet
Payroll International
20 pages
BR100 FT SSHR Setup v1.0
No ratings yet
BR100 FT SSHR Setup v1.0
70 pages
Most Frequently Used Tables in HCM
100% (1)
Most Frequently Used Tables in HCM
4 pages
Sa Gosi Calculation Original
No ratings yet
Sa Gosi Calculation Original
3 pages
Applies To:: Using PYUPIP in Oracle HRMS (Doc ID 130374.1)
No ratings yet
Applies To:: Using PYUPIP in Oracle HRMS (Doc ID 130374.1)
8 pages
Create Absence and Accrual Plan Using API
No ratings yet
Create Absence and Accrual Plan Using API
6 pages
SQL Monitor
No ratings yet
SQL Monitor
46 pages
Form 19
No ratings yet
Form 19
1 page
Lab 1 Manual
No ratings yet
Lab 1 Manual
17 pages
EPQ Model for Inventory Cost Optimization
No ratings yet
EPQ Model for Inventory Cost Optimization
2 pages
Zlac8030 Can Index
No ratings yet
Zlac8030 Can Index
65 pages
Ai - PPT - 33 && 38
No ratings yet
Ai - PPT - 33 && 38
8 pages
Pricing & Product Comparison Microsoft Power BI
No ratings yet
Pricing & Product Comparison Microsoft Power BI
1 page
Lastexception 63772117857
No ratings yet
Lastexception 63772117857
1 page
Conference Template A4
No ratings yet
Conference Template A4
3 pages
Resume 123 PK
No ratings yet
Resume 123 PK
1 page
Linear Programming Fundamentals 2Nd Year - Ensia: Exercice C1
No ratings yet
Linear Programming Fundamentals 2Nd Year - Ensia: Exercice C1
2 pages
Data Center - IIT Bombay
No ratings yet
Data Center - IIT Bombay
151 pages
LOng Quiz Math 9
No ratings yet
LOng Quiz Math 9
4 pages
RB Catalogue 2021 01
No ratings yet
RB Catalogue 2021 01
30 pages
Strategic Groups
No ratings yet
Strategic Groups
3 pages
Instant Download Circuit Theory and Transmission Lines 2nd Edition Ravish R. Singh PDF All Chapter
100% (1)
Instant Download Circuit Theory and Transmission Lines 2nd Edition Ravish R. Singh PDF All Chapter
55 pages
Quantum Circuits for AI Experts
No ratings yet
Quantum Circuits for AI Experts
11 pages
Launchkey Mini (MK3) User Guide PDF
No ratings yet
Launchkey Mini (MK3) User Guide PDF
38 pages
Report (Quick Book) - Taxi-Service PDF
No ratings yet
Report (Quick Book) - Taxi-Service PDF
19 pages
DLD Research Paper1
No ratings yet
DLD Research Paper1
7 pages
Remote Sensing Gis Gps Dgps Ets Ntjkssb24
No ratings yet
Remote Sensing Gis Gps Dgps Ets Ntjkssb24
5 pages
File Del
No ratings yet
File Del
220 pages
Dynamic Programming in MIU Models
No ratings yet
Dynamic Programming in MIU Models
37 pages
Avamar 7.0 Release Notes
No ratings yet
Avamar 7.0 Release Notes
75 pages
SMU06T2 V100R024C10 Site Monitoring Unit User Manual
No ratings yet
SMU06T2 V100R024C10 Site Monitoring Unit User Manual
145 pages
Omit
No ratings yet
Omit
15 pages
RFP - Uk
No ratings yet
RFP - Uk
37 pages
Dầu bôi trơn máy phay MORISEIKI-MAINTENANCE-CLASS PDF
No ratings yet
Dầu bôi trơn máy phay MORISEIKI-MAINTENANCE-CLASS PDF
410 pages
Natural Language Processing For Requirements A Systematic Mapping Study PDF
No ratings yet
Natural Language Processing For Requirements A Systematic Mapping Study PDF
75 pages
Prog3113 1st Quarter Exam - Compress
No ratings yet
Prog3113 1st Quarter Exam - Compress
33 pages
TV Philips Chassis A8-0a Training Manual
No ratings yet
TV Philips Chassis A8-0a Training Manual
90 pages

Payroll Package Functions

Uploaded by

Payroll Package Functions

Uploaded by

APPS.

CREATE OR REPLACE PACKAGE APPS.XXLAM_HR_PAYROLL_ALL

FUNCTION get_balance (p_assignment_id IN NUMBER,

FUNCTION get_balance (p_assignment_id IN number,

FUNCTION calculate_basic_salary (p_assignment_id IN NUMBER,

FUNCTION calculate_trans_all (p_assignment_id IN NUMBER,

FUNCTION calculate_housing_all (p_assignment_id IN NUMBER,

FUNCTION calculate_other_all (p_assignment_id IN NUMBER,

FUNCTION GET_GL_DAILY_RATE (p_from_curr Varchar2,

FUNCTION xxotl_get_week_off_pay (p_assignment_id IN Number,

FUNCTION xxotl_hours_day (p_assignment_id IN number, p_date IN date)

FUNCTION get_month_days (p_assignment_id IN NUMBER, p_date IN DATE)

FUNCTION GET_TICKET_ALLOWANCE (p_business_group_id NUMBER,

FUNCTION get_vacation_pay (p_employee_number IN NUMBER,

FUNCTION get_los_sal_chg ( -- p_business_group_id IN number,

FUNCTION get_flag_adj_grat_accruals (p_assignment_id IN number,

FUNCTION get_month_unpaid_days (p_assignment_id IN NUMBER,

FUNCTION get_grat_accrual_adj ( --p_business_group_id IN number,

FUNCTION get_final_stlmt_adj_amt (p_assignment_id IN number)

FUNCTION calculate_food_all (p_assignment_id IN NUMBER,

FUNCTION get_all_unpaid_days (p_assignment_id IN NUMBER,

FUNCTION get_Staff_Work_Location(p_assignment_id IN NUMBER)

FUNCTION CALCULATE_ADMIN_SALARY (p_assignment_id IN NUMBER,

FUNCTION CALCULATE_OVERTIME (p_assignment_id IN NUMBER,

FUNCTION GET_ANNUAL_ACCURAL_AMT (l_assignment_id IN NUMBER,

FUNCTION GET_TICKET_FLAG ( p_assg_id in NUMBER,

-- AND p_effective_date BETWEEN ptp.start_date AND ptp.end_date;

SELECT MAX (NVL (fnd_number.canonical_to_number (pev.screen_entry_value),

IF NVL (l_basic_salary, 0) <> 0

IF UPPER (l_person_type) = 'STAFF'

g_message_text := 'Balance DB item does not exist';

p_action_id := get_action_id (p_assignment_id, p_effective_date);

IF l_defined_balance_id IS NOT NULL

p_action_id := get_action_id (p_assignment_id, p_effective_date);

IF l_defined_balance_id IS NOT NULL

IF l_defined_balance_id IS NOT NULL

-- AND ppp.change_date <= p_date

--------Added the below code Starts here

--------Added the below code ENDSs here

v_person_type := get_person_type_ass (p_assignment_id, p_effective_date);

DBMS_OUTPUT.put_line ('Regular hours v_skip_y_n = ' || v_skip_y_n);

/*FUNCTION get_otl_regular_hours (p_assignment_id Number

IF p_month_value > 10000

v_otl_hrs := v_otl_hrs + v_curr_otl_hrs;

DBMS_OUTPUT.put_line ('Week Pay:' || v_week_pay || 'Date:' || v_date);

-- for unpaid and vacation with Advance Salary

DBMS_OUTPUT.put_line ('Holiday Pay:' || v_holi_pay || 'Date:'

p_maternity_pay := p_maternity_pay + v_curr_maternity_pay;

/************************ Sick Leave Calculation Starts ****************/

--v_curr_sick := v_curr_sick + v_curr_sick_pay_P;

p_sick_pay := p_sick_pay + v_curr_sick_pay;

DBMS_OUTPUT.put_line ( 'Week off:'

v_otl_hrs := v_otl_hrs + v_curr_otl_hrs;

v_sal_per_day := v_sal_per_day + v_week_holi_pay;

v_otl_hrs := v_otl_hrs + v_curr_otl_hrs;

v_sal_per_day := v_sal_per_day + v_week_holi_pay;

SELECT DISTINCT peef.element_entry_id

SELECT DISTINCT peef.element_entry_id

/* Calculation of Housing Allowance */

DBMS_OUTPUT.put_line ('Month_Value:' || p_month_value);

v_otl_hrs := v_otl_hrs + v_curr_otl_hrs;

DBMS_OUTPUT.put_line ('Week off - OTher :' || v_curr_week_hrs);

-- Unpaid and vacation with advance to be ignored

v_sal_per_day := v_sal_per_day + v_week_holi_pay;

--l_unpaid_leave := nvl(XXLAM_HR_PAYROLL_ALL.get_balance (p_assignment_id,

SELECT TRUNC (p_month_factor / 12) YEAR,

DBMS_OUTPUT.put_line ( 'l_sal_day = '

/******************** Gratuity . Resignation Unlimited Contract

DBMS_OUTPUT.put_line ( 'l_service_years = '

SELECT DISTINCT person_id, business_group_id

DBMS_OUTPUT.put_line ('L_TICKET_A_BALANCE' || l_ticket_a_balance);

DBMS_OUTPUT.put_line ('L_TICKET_A_DEP_BALANCE' || l_ticket_a_balance);

DBMS_OUTPUT.put_line ('L_TICKET_C_BALANCE' || l_ticket_c_balance);

DBMS_OUTPUT.put_line ('L_TICKET_CHILD' || l_ticket_child);

-- Checking Family Status

-- IF Family Status is no then only one ticket for adult

/******** Sick Leave Calculation Starts /