[Home] [Help]
79: g_asg_actions_tab t_asg_actions_tab;
80: --
81: g_assignment_id pay_assignment_actions.assignment_id%type;
82: g_chunk_number pay_assignment_actions.chunk_number%type;
83: g_defined_balance_id pay_defined_balances.defined_balance_id%type;
84: g_balance_type_id pay_defined_balances.balance_type_id%type;
85: g_business_group_id per_all_assignments_f.business_group_id%type;
86:
87: g_feed_input_tab FND_TABLE_OF_NUMBER;
80: --
81: g_assignment_id pay_assignment_actions.assignment_id%type;
82: g_chunk_number pay_assignment_actions.chunk_number%type;
83: g_defined_balance_id pay_defined_balances.defined_balance_id%type;
84: g_balance_type_id pay_defined_balances.balance_type_id%type;
85: g_business_group_id per_all_assignments_f.business_group_id%type;
86:
87: g_feed_input_tab FND_TABLE_OF_NUMBER;
88: g_feed_element_tab FND_TABLE_OF_NUMBER;
100: g_feeds_tab t_feeds_tab;
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: --
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
110: --
149: (
150: balance_dimension_id pay_balance_dimensions.balance_dimension_id%type,
151: priority pay_dimension_routes.priority%type,
152: route_type pay_dimension_routes.route_type%type,
153: run_def_bal_id pay_defined_balances.defined_balance_id%type,
154: retrieval_column pay_dimension_routes.retrieval_column%type
155: );
156: --
157: type t_dim_rou_tab is table of t_dim_rou_rec index by binary_integer;
162: -- Internal cache type for batch retrieval
163: --
164: type t_int_bal_rec is record
165: (
166: defined_balance_id pay_defined_balances.defined_balance_id%type,
167: balance_dimension_id pay_balance_dimensions.balance_dimension_id%type,
168: balance_type_id pay_balance_types.balance_type_id%type,
169: balance_found boolean,
170: balance_value number,
189: --
190: type t_int_rb_rec is record
191: (
192: run_balance_id pay_run_balances.run_balance_id%type,
193: defined_balance_id pay_defined_balances.defined_balance_id%type,
194: payroll_action_id pay_payroll_actions.payroll_action_id%type,
195: tax_unit_id pay_assignment_actions.tax_unit_id%type,
196: jurisdiction_code pay_run_results.jurisdiction_code%type,
197: source_id number,
777: select context_name
778: from ff_contexts fc,
779: ff_route_context_usages frcu,
780: pay_balance_dimensions pbd,
781: pay_defined_balances pdb
782: where pdb.defined_balance_id = p_def_bal_id
783: and pdb.balance_dimension_id = pbd.balance_dimension_id
784: and pbd.route_id = frcu.route_id
785: and frcu.context_id = fc.context_id;
790: , pdr.route_type
791: , null run_def_bal_id
792: , pdr.retrieval_column
793: from pay_dimension_routes pdr
794: , pay_defined_balances pdb -- balance defined balance
795: where pdb.balance_dimension_id = pdr.balance_dimension_id
796: and pdb.defined_balance_id = p_def_bal_id
797: and pdr.route_type = 'RR';
798: --
803: , pdr.route_type
804: , rdb.defined_balance_id run_def_bal_id
805: , pdr.retrieval_column
806: from pay_dimension_routes pdr
807: , pay_defined_balances pdb -- balance defined balance
808: , pay_defined_balances rdb -- run defined balance
809: where pdb.balance_dimension_id = pdr.balance_dimension_id
810: and pdb.defined_balance_id = p_def_bal_id
811: and rdb.balance_type_id = pdb.balance_type_id
804: , rdb.defined_balance_id run_def_bal_id
805: , pdr.retrieval_column
806: from pay_dimension_routes pdr
807: , pay_defined_balances pdb -- balance defined balance
808: , pay_defined_balances rdb -- run defined balance
809: where pdb.balance_dimension_id = pdr.balance_dimension_id
810: and pdb.defined_balance_id = p_def_bal_id
811: and rdb.balance_type_id = pdb.balance_type_id
812: and rdb.balance_dimension_id = pdr.run_dimension_id
842: g_defbal_cache(p_defined_balance_id).balance_type_id,
843: g_defbal_cache(p_defined_balance_id).dimension_lvl,
844: g_defbal_cache(p_defined_balance_id).period_type
845: from pay_balance_dimensions DIM
846: , pay_defined_balances DEF
847: , pay_balance_types TYP
848: where DIM.balance_dimension_id = DEF.balance_dimension_id
849: and TYP.balance_type_id = DEF.balance_type_id
850: and DEF.defined_balance_id = p_defined_balance_id;
858: into g_defbal_cache(p_defined_balance_id).balance_type_column,
859: g_defbal_cache(p_defined_balance_id).decode_required,
860: g_defbal_cache(p_defined_balance_id).dim_rou_rr_route_id
861: from pay_dimension_routes pdr
862: , pay_defined_balances pdb
863: where pdb.balance_dimension_id = pdr.balance_dimension_id
864: and pdb.defined_balance_id = p_defined_balance_id
865: and pdr.route_type = 'RR';
866: --
3055: l_user_name ff_user_entities.user_entity_name%type;
3056: l_business_group_id ff_user_entities.business_group_id%type;
3057: l_legislation_code ff_user_entities.legislation_code%type;
3058: l_balance_list t_batch_list;
3059: l_balance_type_id pay_defined_balances.balance_type_id%type;
3060: l_route_id pay_dimension_routes.route_id%type;
3061: l_balance_type_column pay_dimension_routes.balance_type_column%type;
3062: l_retrieval_column pay_dimension_routes.retrieval_column%type;
3063: l_decode_required boolean;
3085: l_balance_type_column,
3086: l_retrieval_column,
3087: l_decode_required_chr,
3088: l_jur_lvl
3089: from pay_defined_balances pdb,
3090: pay_dimension_routes pdr,
3091: pay_balance_types pbt
3092: where pdb.defined_balance_id = p_def_bal_id
3093: and pdb.balance_dimension_id = pdr.balance_dimension_id
3270: pbd.start_date_code
3271: into l_period_type,
3272: l_start_date_code
3273: from pay_balance_dimensions pbd,
3274: pay_defined_balances pdb
3275: where pdb.defined_balance_id = p_retreival_db_id
3276: and pdb.balance_dimension_id = pbd.balance_dimension_id;
3277:
3278: --
3332: , pdr.priority
3333: , pdr.route_type
3334: , rdb.defined_balance_id run_def_bal_id
3335: from pay_dimension_routes pdr
3336: , pay_defined_balances pdb -- balance defined balance
3337: , pay_defined_balances rdb -- run defined balance
3338: where pdb.balance_dimension_id = pdr.balance_dimension_id
3339: and pdb.defined_balance_id = p_def_bal_id
3340: and rdb.balance_type_id = pdb.balance_type_id
3333: , pdr.route_type
3334: , rdb.defined_balance_id run_def_bal_id
3335: from pay_dimension_routes pdr
3336: , pay_defined_balances pdb -- balance defined balance
3337: , pay_defined_balances rdb -- run defined balance
3338: where pdb.balance_dimension_id = pdr.balance_dimension_id
3339: and pdb.defined_balance_id = p_def_bal_id
3340: and rdb.balance_type_id = pdb.balance_type_id
3341: and rdb.balance_dimension_id = pdr.run_dimension_id
3342: and pdr.route_type = 'SRB'
3343: order by 2;
3344: --
3345: l_balval_found boolean;
3346: l_valid pay_defined_balances.run_balance_status%type;
3347: l_balance_value number;
3348: l_defbal_rec t_def_bal_rec;
3349: l_position number;
3350: --
6234: is
6235: -- bug 13455833 only fetch run balances after the invalidate date
6236: -- and check if there are results that will feed them from that date
6237: select pdb.defined_balance_id
6238: from pay_defined_balances pdb,
6239: pay_balance_dimensions pbd
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'
6263: p_inv_date date
6264: )
6265: is
6266: select pdb.defined_balance_id
6267: from pay_defined_balances pdb,
6268: pay_balance_dimensions pbd
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'
6298: is
6299: -- bug 13455833 only fetch run balances after the invalidate date
6300: -- and check if there are results that will feed them from that date
6301: select pdb.defined_balance_id
6302: from pay_defined_balances pdb,
6303: pay_balance_dimensions pbd
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'
6327: p_inv_date date
6328: )
6329: is
6330: select pdb.defined_balance_id
6331: from pay_defined_balances pdb,
6332: pay_balance_dimensions pbd
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'
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
6399: where balance_type_id = p_balance_type_id);
6400:
6401: if g_debug then
6402: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances SQL%ROWCOUNT:'||SQL%ROWCOUNT,20);
6461: --
6462: cursor get_def_bals(p_bal_id number)
6463: is
6464: select pdb.defined_balance_id
6465: from pay_defined_balances pdb
6466: where pdb.balance_type_id = p_bal_id
6467: and pdb.save_run_balance = 'Y';
6468: --
6469: BEGIN
7156: --
7157: -- Now maintain the assignment ones
7158: select pdb.defined_balance_id
7159: into g_rlb_asg_defbals(l_cnt).defined_balance_id
7160: from pay_defined_balances pdb,
7161: pay_defined_balances pdb_grp,
7162: pay_balance_dimensions pbd
7163: where pdb_grp.defined_balance_id = g_rlb_grp_defbals(l_cnt).defined_balance_id
7164: and pdb_grp.balance_dimension_id = pbd.balance_dimension_id
7157: -- Now maintain the assignment ones
7158: select pdb.defined_balance_id
7159: into g_rlb_asg_defbals(l_cnt).defined_balance_id
7160: from pay_defined_balances pdb,
7161: pay_defined_balances pdb_grp,
7162: pay_balance_dimensions pbd
7163: where pdb_grp.defined_balance_id = g_rlb_grp_defbals(l_cnt).defined_balance_id
7164: and pdb_grp.balance_dimension_id = pbd.balance_dimension_id
7165: and pdb.balance_type_id = pdb_grp.balance_type_id
7846: distinct pdb.defined_balance_id
7847: from
7848: pay_input_values_f piv
7849: ,pay_balance_feeds_f pbf
7850: ,pay_defined_balances pdb
7851: ,pay_balance_dimensions pbd
7852: ,pay_run_result_values prrv
7853: where
7854: pbd.dimension_level = 'ASG'
8621: pay_run_results prr,
8622: pay_input_values_f piv,
8623: pay_balance_feeds_f pbf,
8624: pay_balance_types pbt,
8625: pay_defined_balances pdb,
8626: pay_balance_dimensions pbd,
8627: pay_run_result_values prrv
8628: where pbd.dimension_level = 'GRP'
8629: and pdb.save_run_balance|| decode (pbt.balance_type_id, 0, '', '')= 'Y'
8683: pdb.balance_dimension_id
8684: from
8685: pay_bal_attribute_definitions pbad,
8686: pay_balance_attributes pba,
8687: pay_defined_balances pdb,
8688: pay_balance_dimensions pbd
8689: where
8690: pbad.attribute_name = p_bal_list
8691: and pbad.attribute_id = pba.attribute_id
8713: pdb.balance_dimension_id
8714: from
8715: pay_bal_attribute_definitions pbad,
8716: pay_balance_attributes pba,
8717: pay_defined_balances pdb,
8718: pay_balance_dimensions pbd
8719: where
8720: pbad.attribute_name = p_bal_list
8721: and pbad.attribute_id = pba.attribute_id
8747: pdb.balance_dimension_id
8748: from
8749: pay_bal_attribute_definitions pbad,
8750: pay_balance_attributes pba,
8751: pay_defined_balances pdb,
8752: pay_balance_dimensions pbd
8753: where
8754: pbad.attribute_name = p_bal_list
8755: and pbad.attribute_id = pba.attribute_id
9488: pay_run_results prr,
9489: pay_run_result_values prrv,
9490: pay_balance_feeds_f pbf,
9491: pay_balance_types pbt,
9492: pay_defined_balances pdb,
9493: pay_balance_dimensions pbd
9494: where pbd.dimension_level = 'ASG'
9495: and pdb.save_run_balance|| decode (pbt.balance_type_id, 0, '', '')= 'Y'
9496: and pdb.balance_dimension_id = pbd.balance_dimension_id
9541: pdb.balance_dimension_id
9542: from
9543: pay_bal_attribute_definitions pbad,
9544: pay_balance_attributes pba,
9545: pay_defined_balances pdb,
9546: pay_balance_dimensions pbd
9547: where
9548: pbad.attribute_name = p_bal_list
9549: and pbad.attribute_id = pba.attribute_id
9571: pdb.balance_dimension_id
9572: from
9573: pay_bal_attribute_definitions pbad,
9574: pay_balance_attributes pba,
9575: pay_defined_balances pdb,
9576: pay_balance_dimensions pbd
9577: where
9578: pbad.attribute_name = p_bal_list
9579: and pbad.attribute_id = pba.attribute_id
9603: -- pdb.balance_dimension_id
9604: -- from
9605: -- pay_bal_attribute_definitions pbad,
9606: -- pay_balance_attributes pba,
9607: -- pay_defined_balances pdb,
9608: -- pay_balance_dimensions pbd
9609: -- where
9610: -- pbad.attribute_name = p_bal_list
9611: -- and pbad.attribute_id = pba.attribute_id
9654: from
9655: pay_run_results prr,
9656: pay_input_values_f piv,
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
10113: --
10114: cursor crs_asgact (p_asg_act_id number)
10115: is
10116: select pdb.defined_balance_id
10117: from pay_defined_balances pdb,
10118: pay_balance_dimensions pbd,
10119: pay_assignment_actions paa,
10120: pay_payroll_actions ppa,
10121: per_business_groups_perf pbg
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'
10150: and pdb.balance_dimension_id = pbd.balance_dimension_id
10578: --
10579: cursor crs_pact (p_pact_id number)
10580: is
10581: select pdb.defined_balance_id
10582: from pay_defined_balances pdb,
10583: pay_balance_dimensions pbd,
10584: pay_payroll_actions ppa,
10585: per_business_groups_perf pbg
10586: where pbd.dimension_level = 'GRP'
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'
10612: and pdb.balance_dimension_id = pbd.balance_dimension_id
11465: hr_utility.set_location('entering pay_balance_pkg.run_balance_row_status', 10);
11466: end if;
11467: if ((p_defined_balance_id <> g_defined_balance_id) or (g_defined_balance_id is null)) then
11468: select balance_type_id into g_balance_type_id
11469: from pay_defined_balances
11470: where defined_balance_id = p_defined_balance_id;
11471: g_defined_balance_id := p_defined_balance_id;
11472: g_business_group_id := -1;
11473: g_assignment_id := -1;
11765: hr_utility.set_location('entering pay_balance_pkg.run_balance_row_status', 10);
11766: end if;
11767: if ((p_defined_balance_id <> g_defined_balance_id) or (g_defined_balance_id is null)) then
11768: select balance_type_id into g_balance_type_id
11769: from pay_defined_balances
11770: where defined_balance_id = p_defined_balance_id;
11771: g_defined_balance_id := p_defined_balance_id;
11772: g_business_group_id := -1;
11773: g_assignment_id := -1;
11988: p_business_group_id in number,
11989: p_balance_load_date in date)
11990: return number is
11991: l_balance_type_id pay_balance_types.balance_type_id%type;
11992: l_balance_dimension_id pay_defined_balances.balance_dimension_id%type;
11993: l_user_entity_name ff_user_entities.user_entity_name%type;
11994: l_linked_ue_name ff_user_entities.user_entity_name%type;
11995: l_ue_leg_code ff_user_entities.legislation_code%type;
11996: l_ue_bg ff_user_entities.business_group_id%type;
11993: l_user_entity_name ff_user_entities.user_entity_name%type;
11994: l_linked_ue_name ff_user_entities.user_entity_name%type;
11995: l_ue_leg_code ff_user_entities.legislation_code%type;
11996: l_ue_bg ff_user_entities.business_group_id%type;
11997: l_linked_def_bal pay_defined_balances.defined_balance_id%type;
11998:
11999: begin
12000:
12001: if g_debug then
12015: end if;
12016:
12017: select linked_db.defined_balance_id
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
12017: select linked_db.defined_balance_id
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