[Home] [Help]
60: --
61: -- tables for dimension cache
62: --
63: type t_def_bal_rec is record
64: (dimension_type pay_balance_dimensions.dimension_type%type,
65: expiry_check_code pay_balance_dimensions.expiry_checking_code%type,
66: expiry_check_lvl pay_balance_dimensions.expiry_checking_level%type,
67: dimension_name pay_balance_dimensions.dimension_name%type,
68: jurisdiction_lvl pay_balance_types.jurisdiction_level%type,
61: -- tables for dimension cache
62: --
63: type t_def_bal_rec is record
64: (dimension_type pay_balance_dimensions.dimension_type%type,
65: expiry_check_code pay_balance_dimensions.expiry_checking_code%type,
66: expiry_check_lvl pay_balance_dimensions.expiry_checking_level%type,
67: dimension_name pay_balance_dimensions.dimension_name%type,
68: jurisdiction_lvl pay_balance_types.jurisdiction_level%type,
69: balance_type_column pay_dimension_routes.balance_type_column%type,
62: --
63: type t_def_bal_rec is record
64: (dimension_type pay_balance_dimensions.dimension_type%type,
65: expiry_check_code pay_balance_dimensions.expiry_checking_code%type,
66: expiry_check_lvl pay_balance_dimensions.expiry_checking_level%type,
67: dimension_name pay_balance_dimensions.dimension_name%type,
68: jurisdiction_lvl pay_balance_types.jurisdiction_level%type,
69: balance_type_column pay_dimension_routes.balance_type_column%type,
70: decode_required pay_dimension_routes.decode_required%type,
63: type t_def_bal_rec is record
64: (dimension_type pay_balance_dimensions.dimension_type%type,
65: expiry_check_code pay_balance_dimensions.expiry_checking_code%type,
66: expiry_check_lvl pay_balance_dimensions.expiry_checking_level%type,
67: dimension_name pay_balance_dimensions.dimension_name%type,
68: jurisdiction_lvl pay_balance_types.jurisdiction_level%type,
69: balance_type_column pay_dimension_routes.balance_type_column%type,
70: decode_required pay_dimension_routes.decode_required%type,
71: balance_dimension_id pay_balance_dimensions.balance_dimension_id%type,
67: dimension_name pay_balance_dimensions.dimension_name%type,
68: jurisdiction_lvl pay_balance_types.jurisdiction_level%type,
69: balance_type_column pay_dimension_routes.balance_type_column%type,
70: decode_required pay_dimension_routes.decode_required%type,
71: balance_dimension_id pay_balance_dimensions.balance_dimension_id%type,
72: dimension_lvl pay_balance_dimensions.dimension_level%type,
73: period_type pay_balance_dimensions.period_type%type,
74: balance_type_id pay_balance_types.balance_type_id%type,
75: dim_rou_rr_route_id pay_dimension_routes.route_id%type,
68: jurisdiction_lvl pay_balance_types.jurisdiction_level%type,
69: balance_type_column pay_dimension_routes.balance_type_column%type,
70: decode_required pay_dimension_routes.decode_required%type,
71: balance_dimension_id pay_balance_dimensions.balance_dimension_id%type,
72: dimension_lvl pay_balance_dimensions.dimension_level%type,
73: period_type pay_balance_dimensions.period_type%type,
74: balance_type_id pay_balance_types.balance_type_id%type,
75: dim_rou_rr_route_id pay_dimension_routes.route_id%type,
76: tu_needed boolean,
69: balance_type_column pay_dimension_routes.balance_type_column%type,
70: decode_required pay_dimension_routes.decode_required%type,
71: balance_dimension_id pay_balance_dimensions.balance_dimension_id%type,
72: dimension_lvl pay_balance_dimensions.dimension_level%type,
73: period_type pay_balance_dimensions.period_type%type,
74: balance_type_id pay_balance_types.balance_type_id%type,
75: dim_rou_rr_route_id pay_dimension_routes.route_id%type,
76: tu_needed boolean,
77: jc_needed boolean,
98: -- tables for dimension route cache
99: --
100: type t_dim_rou_rec is record
101: (
102: balance_dimension_id pay_balance_dimensions.balance_dimension_id%type,
103: priority pay_dimension_routes.priority%type,
104: route_type pay_dimension_routes.route_type%type,
105: run_def_bal_id pay_defined_balances.defined_balance_id%type,
106: retrieval_column pay_dimension_routes.retrieval_column%type
115: --
116: type t_int_bal_rec is record
117: (
118: defined_balance_id pay_defined_balances.defined_balance_id%type,
119: balance_dimension_id pay_balance_dimensions.balance_dimension_id%type,
120: balance_type_id pay_balance_types.balance_type_id%type,
121: balance_found boolean,
122: balance_value number,
123: jurisdiction_level pay_balance_types.jurisdiction_level%type,
257: --
258: type pay_act_id_tab is table of pay_payroll_actions.payroll_action_id%type
259: index by binary_integer;
260: type expiry_tab is table of NUMBER(38) index by binary_integer;
261: type dim_nm_tab is table of pay_balance_dimensions.dimension_name%type
262: index by binary_integer;
263: --
264: t_own_pay_action pay_act_id_tab;
265: t_usr_pay_action pay_act_id_tab;
686: cursor get_bal_contexts (p_def_bal_id in number) is
687: select context_name
688: from ff_contexts fc,
689: ff_route_context_usages frcu,
690: pay_balance_dimensions pbd,
691: pay_defined_balances pdb
692: where pdb.defined_balance_id = p_def_bal_id
693: and pdb.balance_dimension_id = pbd.balance_dimension_id
694: and pbd.route_id = frcu.route_id
751: g_defbal_cache(p_defined_balance_id).balance_dimension_id,
752: g_defbal_cache(p_defined_balance_id).balance_type_id,
753: g_defbal_cache(p_defined_balance_id).dimension_lvl,
754: g_defbal_cache(p_defined_balance_id).period_type
755: from pay_balance_dimensions DIM
756: , pay_defined_balances DEF
757: , pay_balance_types TYP
758: where DIM.balance_dimension_id = DEF.balance_dimension_id
759: and TYP.balance_type_id = DEF.balance_type_id
2943: l_load_date pay_balance_validation.balance_load_date%type;
2944: l_retrieval_date pay_payroll_actions.effective_date%type;
2945: l_bus_grp_id pay_payroll_actions.business_group_id%type;
2946: l_payroll_id pay_payroll_actions.payroll_id%type;
2947: l_period_type pay_balance_dimensions.period_type%type;
2948: l_start_date_code pay_balance_dimensions.start_date_code%type;
2949: l_action_type pay_payroll_actions.action_type%type;
2950: l_start_date date;
2951: --
2944: l_retrieval_date pay_payroll_actions.effective_date%type;
2945: l_bus_grp_id pay_payroll_actions.business_group_id%type;
2946: l_payroll_id pay_payroll_actions.payroll_id%type;
2947: l_period_type pay_balance_dimensions.period_type%type;
2948: l_start_date_code pay_balance_dimensions.start_date_code%type;
2949: l_action_type pay_payroll_actions.action_type%type;
2950: l_start_date date;
2951: --
2952: begin
2992: select nvl(pbd.period_type, 'NULL'),
2993: pbd.start_date_code
2994: into l_period_type,
2995: l_start_date_code
2996: from pay_balance_dimensions pbd,
2997: pay_defined_balances pdb
2998: where pdb.defined_balance_id = p_retreival_db_id
2999: and pdb.balance_dimension_id = pbd.balance_dimension_id;
3000:
4430: ,'Core Balance pkg - Dimension_type is NOT A or P. '
4431: ||'Latest balances are only availbale for assignment '
4432: ||'or person level balances, that are not also Run '
4433: ||'level balances. The level of the balance is set '
4434: ||'with the dimension_type on pay_balance_dimensions. '
4435: ||'Dimension types for seeded data must not be '
4436: ||'updated.');
4437: end if;
4438: --
5006: l_defbal_rec t_def_bal_rec;
5007: l_balance_value number;
5008: l_found boolean;
5009: l_balance_cache t_int_bal_cache;
5010: l_batch_bd_id pay_balance_dimensions.balance_dimension_id%type;
5011: l_batch_list t_batch_list;
5012: l_batch_cnt number;
5013: l_dummy_value number;
5014: l_decode_required boolean;
6664: select pdb.defined_balance_id
6665: into g_rlb_asg_defbals(l_cnt).defined_balance_id
6666: from pay_defined_balances pdb,
6667: pay_defined_balances pdb_grp,
6668: pay_balance_dimensions pbd
6669: where pdb_grp.defined_balance_id = g_rlb_grp_defbals(l_cnt).defined_balance_id
6670: and pdb_grp.balance_dimension_id = pbd.balance_dimension_id
6671: and pdb.balance_type_id = pdb_grp.balance_type_id
6672: and pdb.balance_dimension_id = pbd.asg_action_balance_dim_id;
7273: from
7274: pay_input_values_f piv
7275: ,pay_balance_feeds_f pbf
7276: ,pay_defined_balances pdb
7277: ,pay_balance_dimensions pbd
7278: ,pay_run_result_values prrv
7279: where
7280: pbd.dimension_level = 'ASG'
7281: and pdb.save_run_balance = 'Y'
8022: pay_input_values_f piv,
8023: pay_balance_feeds_f pbf,
8024: pay_balance_types pbt,
8025: pay_defined_balances pdb,
8026: pay_balance_dimensions pbd,
8027: pay_run_result_values prrv
8028: where pbd.dimension_level = 'GRP'
8029: and pdb.save_run_balance|| decode (pbt.balance_type_id, 0, '', '')= 'Y'
8030: and pdb.balance_dimension_id = pbd.balance_dimension_id
8085: from
8086: pay_bal_attribute_definitions pbad,
8087: pay_balance_attributes pba,
8088: pay_defined_balances pdb,
8089: pay_balance_dimensions pbd
8090: where
8091: pbad.attribute_name = p_bal_list
8092: and pbad.attribute_id = pba.attribute_id
8093: and pba.defined_balance_id = pdb.defined_balance_id
8112: from
8113: pay_bal_attribute_definitions pbad,
8114: pay_balance_attributes pba,
8115: pay_defined_balances pdb,
8116: pay_balance_dimensions pbd
8117: where
8118: pbad.attribute_name = p_bal_list
8119: and pbad.attribute_id = pba.attribute_id
8120: and pba.defined_balance_id = pdb.defined_balance_id
8147: from
8148: pay_bal_attribute_definitions pbad,
8149: pay_balance_attributes pba,
8150: pay_defined_balances pdb,
8151: pay_balance_dimensions pbd
8152: where
8153: pbad.attribute_name = p_bal_list
8154: and pbad.attribute_id = pba.attribute_id
8155: and pba.defined_balance_id = pdb.defined_balance_id
8825: pay_run_result_values prrv,
8826: pay_balance_feeds_f pbf,
8827: pay_balance_types pbt,
8828: pay_defined_balances pdb,
8829: pay_balance_dimensions pbd
8830: where pbd.dimension_level = 'ASG'
8831: and pdb.save_run_balance|| decode (pbt.balance_type_id, 0, '', '')= 'Y'
8832: and pdb.balance_dimension_id = pbd.balance_dimension_id
8833: and pdb.balance_type_id = pbt.balance_type_id
8877: from
8878: pay_bal_attribute_definitions pbad,
8879: pay_balance_attributes pba,
8880: pay_defined_balances pdb,
8881: pay_balance_dimensions pbd
8882:
8883: where
8884: pbad.attribute_name = p_bal_list
8885: and pbad.attribute_id = pba.attribute_id
8904: from
8905: pay_bal_attribute_definitions pbad,
8906: pay_balance_attributes pba,
8907: pay_defined_balances pdb,
8908: pay_balance_dimensions pbd
8909: where
8910: pbad.attribute_name = p_bal_list
8911: and pbad.attribute_id = pba.attribute_id
8912: and pba.defined_balance_id = pdb.defined_balance_id
8937: -- from
8938: -- pay_bal_attribute_definitions pbad,
8939: -- pay_balance_attributes pba,
8940: -- pay_defined_balances pdb,
8941: -- pay_balance_dimensions pbd
8942: -- where
8943: -- pbad.attribute_name = p_bal_list
8944: -- and pbad.attribute_id = pba.attribute_id
8945: -- and pba.defined_balance_id = pdb.defined_balance_id
8989: pay_input_values_f piv,
8990: pay_balance_feeds_f pbf,
8991: pay_defined_balances pdb,
8992: pay_balance_attributes pba,
8993: pay_balance_dimensions pbd,
8994: pay_balance_validation pbv,
8995: pay_run_result_values prrv
8996: where
8997: prr.assignment_action_id = p_asg_act_id
9407: cursor crs_asgact (p_asg_act_id number)
9408: is
9409: select pdb.defined_balance_id
9410: from pay_defined_balances pdb,
9411: pay_balance_dimensions pbd,
9412: pay_assignment_actions paa,
9413: pay_payroll_actions ppa,
9414: per_business_groups_perf pbg
9415: where pbd.dimension_level = 'ASG'
9436: pay_payroll_actions ppa,
9437: per_business_groups_perf pbg,
9438: pay_balance_validation pbv,
9439: pay_defined_balances pdb,
9440: pay_balance_dimensions pbd
9441: where pbd.dimension_level = 'ASG'
9442: and pdb.save_run_balance = 'Y'
9443: and pdb.balance_dimension_id = pbd.balance_dimension_id
9444: and paa.assignment_action_id = p_asg_act_id
9858: cursor crs_pact (p_pact_id number)
9859: is
9860: select pdb.defined_balance_id
9861: from pay_defined_balances pdb,
9862: pay_balance_dimensions pbd,
9863: pay_payroll_actions ppa,
9864: per_business_groups_perf pbg
9865: where pbd.dimension_level = 'GRP'
9866: and pdb.save_run_balance = 'Y'
9884: from pay_payroll_actions ppa,
9885: per_business_groups_perf pbg,
9886: pay_balance_validation pbv,
9887: pay_defined_balances pdb,
9888: pay_balance_dimensions pbd
9889: where pbd.dimension_level = 'GRP'
9890: and pdb.save_run_balance = 'Y'
9891: and pdb.balance_dimension_id = pbd.balance_dimension_id
9892: and ppa.payroll_action_id = p_pact_id
10037: --
10038: cursor get_dim_level(p_balance_dimension_id in number)
10039: is
10040: select dimension_level
10041: from pay_balance_dimensions
10042: where dimension_type = 'R'
10043: and balance_dimension_id = p_balance_dimension_id;
10044: --
10045: cursor get_leg_code(p_bg_id number)
10083: where ppa.action_type = pac.action_type
10084: and pac.classification_name = 'SEQUENCED'
10085: and ppa.business_group_id = p_business_group_id;
10086: --
10087: l_dim_level pay_balance_dimensions.dimension_level%type;
10088: l_leg_code per_business_groups_perf.legislation_code%type;
10089: l_bg_id per_business_groups_perf.business_group_id%type;
10090: l_bal_load_date pay_balance_validation.balance_load_date%type;
10091: l_rr_exists number;