[Home] [Help]
1: PACKAGE BODY hr_gbbal AS
2: /* $Header: pygbbal.pkb 120.14 2011/04/26 11:57:01 npannamp ship $ */
3: --------------------------------------------------------------------------------
4: g_assignment_action_id number;
5: g_start_of_year per_time_periods.regular_payment_date%type;
6: -- g_initialization_exists number;
7: g_asg_td_ytd number;
8: g_assignment_action_id2 number;
9: g_assignment_id number;
546: p_effective_date in date) return number is
547: --
548: cursor expired_time_period (c_assignment_action_id IN NUMBER) is
549: SELECT ptp.end_date, ppa.effective_date
550: FROM per_time_periods ptp,
551: pay_payroll_actions ppa,
552: pay_assignment_actions paa
553: WHERE
554: paa.assignment_action_id = c_assignment_action_id
667: BACT.business_group_id
668: FROM
669: pay_assignment_actions BAL_ASSACT,
670: pay_payroll_actions BACT,
671: per_time_periods PTP
672: WHERE
673: BAL_ASSACT.assignment_action_id = p_assignment_action_id
674: AND PTP.time_period_id = BACT.time_period_id
675: AND BACT.payroll_action_id = BAL_ASSACT.payroll_action_id;
2015: -- Has the processing time period expired
2016: --
2017: cursor expired_time_period (c_assignment_action_id IN NUMBER) is
2018: SELECT ptp.end_date, ppa.effective_date
2019: FROM per_time_periods ptp,
2020: pay_payroll_actions ppa,
2021: pay_assignment_actions paa
2022: WHERE
2023: paa.assignment_action_id = c_assignment_action_id
2170: l_date_paid DATE;
2171: --
2172: cursor expired_time_period (c_assignment_action_id IN NUMBER) is
2173: SELECT ptp.end_date, ppa.effective_date
2174: FROM per_time_periods ptp,
2175: pay_payroll_actions ppa,
2176: pay_assignment_actions paa
2177: WHERE
2178: paa.assignment_action_id = c_assignment_action_id
2312: l_date_paid DATE;
2313: --
2314: cursor expired_time_period (c_assignment_action_id IN NUMBER) is
2315: SELECT ptp.end_date, ppa.effective_date
2316: FROM per_time_periods ptp,
2317: pay_payroll_actions ppa,
2318: pay_assignment_actions paa
2319: WHERE
2320: paa.assignment_action_id = c_assignment_action_id
2690: -- Has the processing time period expired
2691: --
2692: cursor expired_time_period (c_assignment_action_id IN NUMBER) is
2693: SELECT ptp.end_date, ppa.effective_date
2694: FROM per_time_periods ptp,
2695: pay_payroll_actions ppa,
2696: pay_assignment_actions paa
2697: WHERE
2698: paa.assignment_action_id = c_assignment_action_id
3188: --
3189: SELECT /*+ ORDERED INDEX (ASSACT PAY_ASSIGNMENT_ACTIONS_N51,
3190: PACT PAY_PAYROLL_ACTIONS_PK,
3191: FEED PAY_BALANCE_FEEDS_F_UK2,
3192: PPTP PER_TIME_PERIODS_PK,
3193: RR PAY_RUN_RESULTS_N50,
3194: TARGET PAY_RUN_RESULT_VALUES_PK) */
3195: NVL(SUM(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)
3196: INTO
3198: FROM
3199: pay_assignment_actions ASSACT
3200: ,pay_payroll_actions PACT
3201: ,pay_balance_feeds_f FEED
3202: ,per_time_periods PPTP
3203: ,pay_run_results RR
3204: ,pay_run_result_values TARGET
3205: WHERE
3206: FEED.balance_type_id = P_BALANCE_TYPE_ID
3483: BEGIN
3484:
3485: SELECT PTP.regular_payment_date
3486: INTO l_regular_payment_date
3487: FROM per_time_periods PTP,
3488: pay_payroll_actions BACT
3489: WHERE BACT.payroll_action_id = p_owner_payroll_action_id
3490: AND PTP.time_period_id = BACT.time_period_id;
3491:
3632: function ni_category_exists_in_year (p_assignment_action_id in number,
3633: p_category in varchar2)
3634: RETURN number is
3635: l_return number;
3636: l_regular_payment_date per_time_periods.regular_payment_date%type;
3637: l_niable_def_id pay_defined_balances.defined_balance_id%type;
3638: l_nitotal_def_id pay_defined_balances.defined_balance_id%type;
3639: l_nitotal_value number;
3640: l_niable_value number;
3663: from pay_element_entries_f ent,
3664: pay_element_links_f pel,
3665: pay_user_rows_f urows,
3666: pay_payroll_actions bact,
3667: per_time_periods bptp,
3668: pay_assignment_actions bassact
3669: where bassact.assignment_action_id = p_assignment_action_id
3670: and UROWS.user_table_id = g_ni_cat_indicator_table_id
3671: and fnd_number.canonical_to_number(UROWS.ROW_LOW_RANGE_OR_NAME) = PEL.ELEMENT_TYPE_ID
3693: , max(decode(PRR.element_type_id,g_ni_l_element_type_id,1,0))
3694: , max(decode(PRR.element_type_id,g_ni_s_element_type_id,1,0))
3695: from
3696: PAY_RUN_RESULTS PRR
3697: , PER_TIME_PERIODS PPTP
3698: , PAY_PAYROLL_ACTIONS PACT
3699: , PAY_ASSIGNMENT_ACTIONS ASSACT
3700: , PAY_ASSIGNMENT_ACTIONS BASSACT
3701: where PRR.source_id = p_source_id
3723: from
3724: PAY_BALANCE_FEEDS_F FEED
3725: , PAY_RUN_RESULT_VALUES PRRV
3726: , PAY_RUN_RESULTS PRR
3727: , PER_TIME_PERIODS PPTP
3728: , PAY_PAYROLL_ACTIONS PACT
3729: , PAY_ASSIGNMENT_ACTIONS ASSACT
3730: , PAY_ASSIGNMENT_ACTIONS BASSACT
3731: WHERE FEED.balance_type_id in (
3759: --Remove this cursor for bug 4120063
3760: /*
3761: cursor CSR_initialization_exists is
3762: select 1
3763: from per_time_periods ptp
3764: where ptp.regular_payment_date >= g_start_of_year
3765: and ptp.time_period_id in
3766: (
3767: select
3906: ,g_ni_j_element_type_id
3907: ,g_ni_l_element_type_id
3908: ,g_ni_s_element_type_id
3909: from pay_element_types_f e,
3910: per_time_periods ptp,
3911: pay_payroll_actions bact,
3912: pay_assignment_actions bassact
3913: where element_name in ( 'NI'
3914: ,'NI A'