[Home] [Help]
101:
102: -- globals used in linked_defined_balance
103: g_ldb_business_group_id pay_payroll_actions.business_group_id%type;
104: g_ldb_defined_balance_id pay_defined_balances.defined_balance_id%type;
105: g_ldb_balance_load_date pay_balance_validation.balance_load_date%type;
106: g_ldb_linked_def_bal pay_defined_balances.defined_balance_id%type;
107:
108: --
109: -- tables for dimension cache
3215: p_asg_action_id in number,
3216: p_status out nocopy varchar2)
3217: is
3218: --
3219: l_run_bal_status pay_balance_validation.run_balance_status%type;
3220: l_load_date pay_balance_validation.balance_load_date%type;
3221: l_retrieval_date pay_payroll_actions.effective_date%type;
3222: l_bus_grp_id pay_payroll_actions.business_group_id%type;
3223: l_payroll_id pay_payroll_actions.payroll_id%type;
3216: p_status out nocopy varchar2)
3217: is
3218: --
3219: l_run_bal_status pay_balance_validation.run_balance_status%type;
3220: l_load_date pay_balance_validation.balance_load_date%type;
3221: l_retrieval_date pay_payroll_actions.effective_date%type;
3222: l_bus_grp_id pay_payroll_actions.business_group_id%type;
3223: l_payroll_id pay_payroll_actions.payroll_id%type;
3224: l_period_type pay_balance_dimensions.period_type%type;
3253: l_action_type := g_action_type;
3254: --
3255: select pbv.run_balance_status, pbv.balance_load_date
3256: into l_run_bal_status, l_load_date
3257: from pay_balance_validation pbv
3258: where pbv.defined_balance_id = p_run_db_id
3259: and pbv.business_group_id = l_bus_grp_id;
3260: --
3261: if (l_run_bal_status = 'V') then
6240: where pdb.balance_type_id = p_bal_id
6241: and pbd.balance_dimension_id = pdb.balance_dimension_id
6242: and pbd.dimension_level <> 'GRP'
6243: and pdb.save_run_balance = 'Y'
6244: and exists (select /*+ no_unnest */ 'x' from pay_balance_validation pbv
6245: where pbv.defined_balance_id = pdb.defined_balance_id
6246: and pbv.run_balance_status in ('P', 'V') )
6247: and exists ( select /*+ no_unnest */ 'x'
6248: from pay_run_balances rb
6269: where pdb.balance_type_id = p_bal_id
6270: and pbd.balance_dimension_id = pdb.balance_dimension_id
6271: and pbd.dimension_level = 'GRP'
6272: and pdb.save_run_balance = 'Y'
6273: and exists (select /*+ no_unnest */ 'x' from pay_balance_validation pbv
6274: where pbv.defined_balance_id = pdb.defined_balance_id
6275: and pbv.run_balance_status in ('P', 'V') )
6276: and exists ( select /*+ no_unnest */ 'x'
6277: from pay_run_balances rb,
6304: where pdb.balance_type_id = p_bal_id
6305: and pbd.balance_dimension_id = pdb.balance_dimension_id
6306: and pbd.dimension_level <> 'GRP'
6307: and pdb.save_run_balance = 'Y'
6308: and exists (select 'x' from pay_balance_validation pbv
6309: where pbv.defined_balance_id = pdb.defined_balance_id
6310: and pbv.run_balance_status in ('P', 'V') )
6311: and exists ( select 'x'
6312: from pay_run_balances rb
6333: where pdb.balance_type_id = p_bal_id
6334: and pbd.balance_dimension_id = pdb.balance_dimension_id
6335: and pbd.dimension_level = 'GRP'
6336: and pdb.save_run_balance = 'Y'
6337: and exists (select 'x' from pay_balance_validation pbv
6338: where pbv.defined_balance_id = pdb.defined_balance_id
6339: and pbv.run_balance_status in ('P', 'V') )
6340: and exists ( select 'x'
6341: from pay_run_balances rb,
6390: from pay_input_values_f
6391: where input_value_id = p_input_value_id;
6392:
6393: -- bug 14007061 balance initialized from sot but not generated
6394: update pay_balance_validation bv set run_balance_status = 'I'
6395: where balance_load_date = to_date('0001/01/01','yyyy/mm/dd')
6396: and run_balance_status in ('V','P')
6397: and bv.defined_balance_id in (
6398: select defined_balance_id from pay_defined_balances db
6404: -- Bug 16048613, calling cursors with 'no_unnest' hints if db version is less than 11g
6405: if(nvl(l_ora_db_vers,0) < 11.0) then
6406: for each_row in get_def_bals(p_balance_type_id, p_input_value_id ,l_element_type_id, p_invalid_date) loop
6407: --
6408: update pay_balance_validation
6409: set run_balance_status = 'I'
6410: where defined_balance_id = each_row.defined_balance_id;
6411: if g_debug then
6412: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances asg_level', 15);
6414: end loop;
6415: -- repeat for any group level dimensions
6416: for grp_row in get_def_bals_grp(p_balance_type_id, p_input_value_id ,l_element_type_id, p_invalid_date) loop
6417: --
6418: update pay_balance_validation
6419: set run_balance_status = 'I'
6420: where defined_balance_id = grp_row.defined_balance_id;
6421: if g_debug then
6422: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances grp_level', 17);
6424: end loop;
6425: else
6426: for each_row in get_def_bals_g(p_balance_type_id, p_input_value_id ,l_element_type_id, p_invalid_date) loop
6427: --
6428: update pay_balance_validation
6429: set run_balance_status = 'I'
6430: where defined_balance_id = each_row.defined_balance_id;
6431: if g_debug then
6432: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances asg_level', 18);
6434: end loop;
6435: -- repeat for any group level dimensions
6436: for grp_row in get_def_bals_grp_g(p_balance_type_id, p_input_value_id ,l_element_type_id, p_invalid_date) loop
6437: --
6438: update pay_balance_validation
6439: set run_balance_status = 'I'
6440: where defined_balance_id = grp_row.defined_balance_id;
6441: if g_debug then
6442: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances grp_level', 19);
6472: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances',5);
6473: end if;
6474: for each_row in get_def_bals(p_balance_type_id) loop
6475: --
6476: update pay_balance_validation
6477: set run_balance_status = 'I'
6478: where defined_balance_id = each_row.defined_balance_id;
6479: if g_debug then
6480: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances', 15);
8640: )
8641: and ( p_bal_list <> 'INVALID'
8642: or ( p_bal_list = 'INVALID'
8643: and exists (select ''
8644: from pay_balance_validation pbv
8645: where pbv.defined_balance_id = pdb.defined_balance_id
8646: and pbv.business_group_id = p_bus_grp_id
8647: and pbv.run_balance_status = 'P')
8648: )
8756: and pba.defined_balance_id = pdb.defined_balance_id
8757: and pdb.balance_dimension_id = pbd.balance_dimension_id
8758: and pbd.dimension_level = 'GRP'
8759: and exists (select ''
8760: from pay_balance_validation pbv
8761: where pdb.defined_balance_id + decode(PDB.LEGISLATION_SUBGROUP, ' ', 0, 0) = pbv.defined_balance_id
8762: and pbv.run_balance_status = 'V'
8763: and pbv.business_group_id = p_bus_grp_id
8764: and pbv.balance_load_date is not null
9497: and pdb.balance_type_id = pbt.balance_type_id
9498: and ( p_bal_list <> 'INVALID'
9499: or ( p_bal_list = 'INVALID'
9500: and exists (select ''
9501: from pay_balance_validation pbv
9502: where pbv.defined_balance_id = pdb.defined_balance_id
9503: and pbv.business_group_id = p_bus_grp_id
9504: and pbv.run_balance_status = 'P')
9505: )
9612: -- and pba.defined_balance_id = pdb.defined_balance_id
9613: -- and pdb.balance_dimension_id = pbd.balance_dimension_id
9614: -- and pbd.dimension_level = 'ASG'
9615: -- and exists (select ''
9616: -- from pay_balance_validation pbv
9617: -- where pdb.defined_balance_id + decode(PDB.LEGISLATION_SUBGROUP, ' ', 0, 0) = pbv.defined_balance_id
9618: -- and pbv.run_balance_status = 'V'
9619: -- and pbv.business_group_id = p_bus_grp_id
9620: -- and pbv.balance_load_date is not null
9657: pay_balance_feeds_f pbf,
9658: pay_defined_balances pdb,
9659: pay_balance_attributes pba,
9660: pay_balance_dimensions pbd,
9661: pay_balance_validation pbv,
9662: pay_run_result_values prrv
9663: where
9664: prr.assignment_action_id = p_asg_act_id
9665: and piv.element_type_id = prr.element_type_id
10141: pdb.defined_balance_id
10142: from pay_assignment_actions paa,
10143: pay_payroll_actions ppa,
10144: per_business_groups_perf pbg,
10145: pay_balance_validation pbv,
10146: pay_defined_balances pdb,
10147: pay_balance_dimensions pbd
10148: where pbd.dimension_level = 'ASG'
10149: and pdb.save_run_balance = 'Y'
10603: select /*+ ORDERED */
10604: pdb.defined_balance_id
10605: from pay_payroll_actions ppa,
10606: per_business_groups_perf pbg,
10607: pay_balance_validation pbv,
10608: pay_defined_balances pdb,
10609: pay_balance_dimensions pbd
10610: where pbd.dimension_level = 'GRP'
10611: and pdb.save_run_balance = 'Y'
10734: --
10735: --------------------------------------------------------------------------
10736: -- procedure initialise_run_balance
10737: -- This procedure initialises a run balance by creating a row in
10738: -- pay_balance_validation. This procedure will be called from the
10739: -- defined_balance trigger.
10740: -- For a user defined balance, a row will be inserted for the bg of the def bal.
10741: -- For a legislative defined balance, a row will be inserted for each bg within
10742: -- the specific legislation.
10807: --
10808: l_dim_level pay_balance_dimensions.dimension_level%type;
10809: l_leg_code per_business_groups_perf.legislation_code%type;
10810: l_bg_id per_business_groups_perf.business_group_id%type;
10811: l_bal_load_date pay_balance_validation.balance_load_date%type;
10812: l_rr_exists number;
10813: --
10814: BEGIN
10815: hr_utility.set_location('Entering: pay_balance_pkg.initialise_run_balance', 10);
10868: hr_utility.trace('def_bal: '||to_char(p_defbal_id));
10869: hr_utility.trace('bg: '||to_char(each_bg.business_group_id));
10870: hr_utility.trace('bal_load_date: '||to_char(l_bal_load_date,'DD-MON-YYYY'));
10871: --
10872: insert into pay_balance_validation
10873: (BALANCE_VALIDATION_ID
10874: ,DEFINED_BALANCE_ID
10875: ,BUSINESS_GROUP_ID
10876: ,RUN_BALANCE_STATUS
10874: ,DEFINED_BALANCE_ID
10875: ,BUSINESS_GROUP_ID
10876: ,RUN_BALANCE_STATUS
10877: ,BALANCE_LOAD_DATE)
10878: select pay_balance_validation_s.nextval
10879: ,p_defbal_id
10880: ,each_bg.business_group_id
10881: ,'V'
10882: ,l_bal_load_date
10881: ,'V'
10882: ,l_bal_load_date
10883: from dual
10884: where not exists (select 1
10885: from pay_balance_validation
10886: where defined_balance_id = p_defbal_id
10887: and business_group_id = each_bg.business_group_id);
10888: --
10889: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 90);
12018: into g_ldb_linked_def_bal
12019: from pay_defined_balances db,
12020: pay_balance_dimensions dim,
12021: pay_defined_balances linked_db,
12022: pay_balance_validation bv
12023: where db.defined_balance_id = p_defined_balance_id
12024: and dim.balance_dimension_id = db.balance_dimension_id
12025: and linked_db.balance_type_id = db.balance_type_id
12026: and linked_db.balance_dimension_id = dim.ASG_ACTION_BALANCE_DIM_ID