[Home] [Help]
99: type t_feeds_tab is table of t_feed_rec index by binary_integer;
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:
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,
198: source_text pay_run_result_values.result_value%type,
302: g_low_volume pay_action_parameters.parameter_value%type := 'N';
303: --
304: -- tables for expiry_checking cache
305: --
306: type pay_act_id_tab is table of pay_payroll_actions.payroll_action_id%type
307: index by binary_integer;
308: type expiry_tab is table of NUMBER(38) index by binary_integer;
309: type dim_nm_tab is table of pay_balance_dimensions.dimension_name%type
310: index by binary_integer;
314: t_dim_nm dim_nm_tab;
315: t_expiry expiry_tab;
316: --
317: -- The following globals are used in remove_asg_contrib
318: g_payroll_action pay_payroll_actions.payroll_action_id%type;
319: g_rlb_grp_defbals t_balance_value_tab;
320: g_rlb_asg_defbals t_balance_value_tab;
321: g_grp_maintained_rb t_int_rb_cache;
322: g_grp_rb_ptr_list number_array;
329: g_save_run_bals pay_legislation_rules.rule_mode%type;
330: --
331: -- The following globals are used in get_rb_status
332: g_aa_id NUMBER := NULL;
333: g_retrieval_date pay_payroll_actions.effective_date%type := NULL;
334: g_bus_grp_id pay_payroll_actions.business_group_id%type := NULL;
335: g_payroll_id pay_payroll_actions.payroll_id%type := NULL;
336: g_action_type pay_payroll_actions.action_type%type := NULL;
337: --
330: --
331: -- The following globals are used in get_rb_status
332: g_aa_id NUMBER := NULL;
333: g_retrieval_date pay_payroll_actions.effective_date%type := NULL;
334: g_bus_grp_id pay_payroll_actions.business_group_id%type := NULL;
335: g_payroll_id pay_payroll_actions.payroll_id%type := NULL;
336: g_action_type pay_payroll_actions.action_type%type := NULL;
337: --
338: -- Run result record, used in get_run_result_info.
331: -- The following globals are used in get_rb_status
332: g_aa_id NUMBER := NULL;
333: g_retrieval_date pay_payroll_actions.effective_date%type := NULL;
334: g_bus_grp_id pay_payroll_actions.business_group_id%type := NULL;
335: g_payroll_id pay_payroll_actions.payroll_id%type := NULL;
336: g_action_type pay_payroll_actions.action_type%type := NULL;
337: --
338: -- Run result record, used in get_run_result_info.
339: g_run_result_rec t_run_result_rec;
332: g_aa_id NUMBER := NULL;
333: g_retrieval_date pay_payroll_actions.effective_date%type := NULL;
334: g_bus_grp_id pay_payroll_actions.business_group_id%type := NULL;
335: g_payroll_id pay_payroll_actions.payroll_id%type := NULL;
336: g_action_type pay_payroll_actions.action_type%type := NULL;
337: --
338: -- Run result record, used in get_run_result_info.
339: g_run_result_rec t_run_result_rec;
340: --
570: elsif (p_action_type in ('P', 'U')) then
571: --
572: select min(effective_date)
573: into l_return_date
574: from pay_payroll_actions ppa,
575: pay_assignment_actions paa,
576: pay_action_interlocks pai
577: where pai.locking_action_id = p_asg_action
578: and pai.locked_action_id = paa.assignment_action_id
733: elsif (p_action_type in ('P', 'U')) then
734: --
735: select max(effective_date)
736: into l_return_date
737: from pay_payroll_actions ppa,
738: pay_assignment_actions paa,
739: pay_action_interlocks pai
740: where pai.locking_action_id = p_asg_action
741: and pai.locked_action_id = paa.assignment_action_id
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;
3225: l_start_date_code pay_balance_dimensions.start_date_code%type;
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;
3225: l_start_date_code pay_balance_dimensions.start_date_code%type;
3226: l_action_type pay_payroll_actions.action_type%type;
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;
3225: l_start_date_code pay_balance_dimensions.start_date_code%type;
3226: l_action_type pay_payroll_actions.action_type%type;
3227: l_start_date date;
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;
3225: l_start_date_code pay_balance_dimensions.start_date_code%type;
3226: l_action_type pay_payroll_actions.action_type%type;
3227: l_start_date date;
3228: --
3229: begin
3230: --
3238: into g_retrieval_date,
3239: g_bus_grp_id,
3240: g_payroll_id,
3241: g_action_type
3242: from pay_payroll_actions ppa,
3243: pay_assignment_actions paa
3244: where paa.assignment_action_id = p_asg_action_id
3245: and paa.payroll_action_id = ppa.payroll_action_id;
3246:
3442: p_bal_context_str in varchar2 -- list of context values.
3443: ) return number is
3444: p_balance_expired number;
3445: --
3446: l_bal_owner_pay_action pay_payroll_actions.payroll_action_id%type;
3447: l_bal_owner_eff_date pay_payroll_actions.effective_date%type;
3448: l_payroll_action pay_payroll_actions.payroll_action_id%type;
3449: l_effective_date pay_payroll_actions.effective_date%type;
3450: l_jul_effect_date number; -- Julian value of effective date
3443: ) return number is
3444: p_balance_expired number;
3445: --
3446: l_bal_owner_pay_action pay_payroll_actions.payroll_action_id%type;
3447: l_bal_owner_eff_date pay_payroll_actions.effective_date%type;
3448: l_payroll_action pay_payroll_actions.payroll_action_id%type;
3449: l_effective_date pay_payroll_actions.effective_date%type;
3450: l_jul_effect_date number; -- Julian value of effective date
3451: l_expiry_chk_str varchar2(2000); -- used with dynamic pl/sql
3444: p_balance_expired number;
3445: --
3446: l_bal_owner_pay_action pay_payroll_actions.payroll_action_id%type;
3447: l_bal_owner_eff_date pay_payroll_actions.effective_date%type;
3448: l_payroll_action pay_payroll_actions.payroll_action_id%type;
3449: l_effective_date pay_payroll_actions.effective_date%type;
3450: l_jul_effect_date number; -- Julian value of effective date
3451: l_expiry_chk_str varchar2(2000); -- used with dynamic pl/sql
3452: l_expiry_information number;
3445: --
3446: l_bal_owner_pay_action pay_payroll_actions.payroll_action_id%type;
3447: l_bal_owner_eff_date pay_payroll_actions.effective_date%type;
3448: l_payroll_action pay_payroll_actions.payroll_action_id%type;
3449: l_effective_date pay_payroll_actions.effective_date%type;
3450: l_jul_effect_date number; -- Julian value of effective date
3451: l_expiry_chk_str varchar2(2000); -- used with dynamic pl/sql
3452: l_expiry_information number;
3453: sql_cursor integer;
3478: PAY.effective_date
3479: into l_bal_owner_pay_action,
3480: l_bal_owner_eff_date
3481: from pay_assignment_actions ASG
3482: , pay_payroll_actions PAY
3483: where ASG.assignment_action_id = p_bal_owner_asg_action
3484: and PAY.payroll_action_id = ASG.payroll_action_id;
3485: --
3486: -- get the actual payroll information for this assignment action:
3492: PAY.effective_date
3493: into l_payroll_action,
3494: l_effective_date
3495: from pay_assignment_actions ASG
3496: , pay_payroll_actions PAY
3497: where ASG.assignment_action_id = p_assignment_action_id
3498: and PAY.payroll_action_id = ASG.payroll_action_id;
3499: --
3500: -- before building up the sql string to call the expiry checking pl/sql
3841: PERBAL.prev_balance_value
3842: from pay_person_latest_balances PERBAL
3843: , per_all_assignments_f ASSIGN
3844: , pay_assignment_actions ACT
3845: , pay_payroll_actions PACT
3846: , pay_assignment_actions OWN_ACT
3847: where PERBAL.defined_balance_id = p_defined_balance_id
3848: and ACT.assignment_action_id = p_assignment_action_id
3849: and ASSIGN.assignment_id = ACT.assignment_id
4096: end if;
4097: select count(*)
4098: into l_num_of_runs
4099: from
4100: pay_payroll_actions ppa2,
4101: pay_assignment_actions paa2,
4102: pay_assignment_actions paa
4103: where paa.assignment_action_id = l_bal_expired_action
4104: and paa2.assignment_id = paa.assignment_id
4129: end if;
4130: select count(*)
4131: into l_num_of_runs
4132: from
4133: pay_payroll_actions ppa2,
4134: pay_assignment_actions paa2,
4135: pay_assignment_actions paa
4136: where paa.assignment_action_id = l_prev_action
4137: and paa2.assignment_id = paa.assignment_id
4290: plb.source_number2
4291: from pay_latest_balances plb
4292: , per_all_assignments_f ASSIGN
4293: , pay_assignment_actions ACT
4294: , pay_payroll_actions PACT
4295: , pay_assignment_actions OWN_ACT
4296: where plb.defined_balance_id = p_defined_balance_id
4297: and ACT.assignment_action_id = p_assignment_action_id
4298: and ASSIGN.assignment_id = ACT.assignment_id
4402: end if;
4403: select count(*)
4404: into l_num_of_runs
4405: from
4406: pay_payroll_actions ppa2,
4407: pay_assignment_actions paa2,
4408: pay_assignment_actions paa
4409: where paa.assignment_action_id = lbrec.expired_assignment_action_id
4410: and paa2.assignment_id = paa.assignment_id
4436: end if;
4437: select count(*)
4438: into l_num_of_runs
4439: from
4440: pay_payroll_actions ppa2,
4441: pay_assignment_actions paa2,
4442: pay_assignment_actions paa
4443: where paa.assignment_action_id = lbrec.prev_assignment_action_id
4444: and paa2.assignment_id = paa.assignment_id
4642: begin
4643: --
4644: select ppa.business_group_id
4645: into l_business_group_id
4646: from pay_payroll_actions ppa,
4647: pay_assignment_actions paa
4648: where ppa.payroll_action_id = paa.payroll_action_id
4649: and paa.assignment_action_id = p_assignment_action_id;
4650: --
5820: l_payroll_id per_all_assignments_f.payroll_id%type;
5821: l_bus_grp_id per_all_assignments_f.business_group_id%type;
5822: l_consol_set_id pay_all_payrolls_f.payroll_id%type;
5823: l_ass_action_id pay_assignment_actions.assignment_action_id%type;
5824: l_pay_action_id pay_payroll_actions.payroll_action_id%type;
5825: l_time_period_id per_time_periods.time_period_id%type;
5826: l_asg_lock boolean;
5827: --
5828: begin
5884: end;
5885: --
5886: -- get the next value for payroll action id
5887: --
5888: select pay_payroll_actions_s.nextval
5889: into l_pay_action_id
5890: from dual;
5891: --
5892: -- insert a temporary row into pay_payroll_actions
5888: select pay_payroll_actions_s.nextval
5889: into l_pay_action_id
5890: from dual;
5891: --
5892: -- insert a temporary row into pay_payroll_actions
5893: --
5894: if g_debug then
5895: hr_utility.set_location ('pay_balance_pkg.get_value', 20);
5896: end if;
5893: --
5894: if g_debug then
5895: hr_utility.set_location ('pay_balance_pkg.get_value', 20);
5896: end if;
5897: insert into pay_payroll_actions
5898: (payroll_action_id,
5899: action_type,
5900: business_group_id,
5901: consolidation_set_id,
5972: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'
5973: 0')||
5974: paa.assignment_action_id),16))
5975: FROM pay_assignment_actions paa,
5976: pay_payroll_actions ppa
5977: WHERE
5978: paa.assignment_id = c_assignment_id
5979: AND ppa.payroll_action_id = paa.payroll_action_id
5980: AND ppa.effective_date <= c_effective_date
5981: AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
5982: --
5983: cursor get_per_latest_id (c_assignment_id IN NUMBER,
5984: c_effective_date IN DATE) is
5985: SELECT /*+ INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
5986: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
5987: paa.assignment_action_id),16))
5988: FROM pay_assignment_actions paa,
5989: pay_payroll_actions ppa,
5985: SELECT /*+ INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
5986: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
5987: paa.assignment_action_id),16))
5988: FROM pay_assignment_actions paa,
5989: pay_payroll_actions ppa,
5990: (SELECT DISTINCT paf2.assignment_id
5991: FROM per_all_assignments_f paf1,
5992: per_all_assignments_f paf2
5993: WHERE paf1.assignment_id = c_assignment_id
6029: p_balance_value out nocopy number
6030: )
6031: is
6032: l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
6033: l_pact_payroll_id pay_payroll_actions.payroll_action_id%type;
6034: l_pact_eff_date date;
6035: l_expiry_date date;
6036: l_balance_value number := null;
6037: l_found boolean;
6076: select ppa.effective_date,
6077: ppa.payroll_id
6078: into l_pact_eff_date,
6079: l_pact_payroll_id
6080: from pay_payroll_actions ppa,
6081: pay_assignment_actions paa
6082: where paa.assignment_action_id = l_assignment_action_id
6083: and paa.payroll_action_id = ppa.payroll_action_id;
6084: --
6526: ,pbg.legislation_code
6527: ,ppa.business_group_id
6528: from
6529: per_business_groups_perf pbg
6530: ,pay_payroll_actions ppa
6531: where
6532: pbg.business_group_id = ppa.business_group_id
6533: and ppa.payroll_action_id = p_payroll_action_id
6534: ;
7086: , nvl(prr.end_date, ptp.end_date) balance_date
7087: , prr.local_unit_id local_unit_id
7088: , ppa.effective_date
7089: from pay_assignment_actions paa,
7090: pay_payroll_actions ppa,
7091: per_time_periods ptp,
7092: pay_run_results prr
7093: where paa.assignment_action_id = p_asg_action
7094: and ppa.payroll_action_id = paa.payroll_action_id
7099: order by 1, 2, 3, 4;
7100: --
7101: cursor get_bg (pactid number) is
7102: select pbg.business_group_id, pbg.legislation_code
7103: from pay_payroll_actions ppa,
7104: per_business_groups_perf pbg
7105: where payroll_action_id = pactid
7106: and pbg.business_group_id = ppa.business_group_id;
7107: --
7127: select plr.rule_mode
7128: into g_save_run_bals
7129: from pay_legislation_rules plr,
7130: per_business_groups_perf pbg,
7131: pay_payroll_actions ppa
7132: where ppa.payroll_action_id = p_payroll_action_id
7133: and ppa.business_group_id = pbg.business_group_id
7134: and pbg.legislation_code = plr.legislation_code
7135: and plr.rule_type = 'SAVE_RUN_BAL';
7436: p_asgact_id in pay_assignment_actions.assignment_action_id%type,
7437: p_pact_id in pay_assignment_actions.payroll_action_id%type,
7438: p_assignment_id in pay_assignment_actions.assignment_id%type,
7439: p_action_sequence in pay_assignment_actions.action_sequence%type,
7440: p_effective_date in pay_payroll_actions.effective_date%type
7441: )
7442: is
7443:
7444: Type t_pay_run_bal is table of pay_run_balances%rowtype index by binary_integer;
7529: l_tx_ut pay_assignment_actions.tax_unit_id%type;
7530: l_asg_id pay_assignment_actions.assignment_id%type;
7531: l_act_seq pay_assignment_actions.action_sequence%type;
7532: l_asgact_id pay_assignment_actions.assignment_action_id%type;
7533: l_pactid pay_payroll_actions.payroll_action_id%type;
7534: begin
7535: --
7536: --
7537: if g_debug then
8077: , prr.local_unit_id
8078: , prr.time_definition_id
8079: , nvl(prr.end_date, ptp.end_date) balance_date
8080: from pay_assignment_actions paa,
8081: pay_payroll_actions ppa,
8082: per_time_periods ptp,
8083: pay_run_results prr
8084: where paa.assignment_action_id = asgact
8085: and paa.assignment_action_id = prr.assignment_action_id
8094: ppa.effective_date,
8095: paa.assignment_id,
8096: paa.action_sequence
8097: from pay_assignment_actions paa,
8098: pay_payroll_actions ppa
8099: where ppa.payroll_action_id = paa.payroll_action_id
8100: and paa.assignment_action_id = asgact;
8101: --
8102: cursor get_bg (aaid number) is
8100: and paa.assignment_action_id = asgact;
8101: --
8102: cursor get_bg (aaid number) is
8103: select pbg.business_group_id, pbg.legislation_code
8104: from pay_payroll_actions ppa,
8105: pay_assignment_actions paa,
8106: per_business_groups_perf pbg
8107: where ppa.payroll_action_id = paa.payroll_action_id
8108: and paa.assignment_action_id = aaid
8389: , decode(p_org_needed,
8390: 'Y', find_context('ORGANIZATION_ID', prr.run_result_id),
8391: null) organization_id
8392: from pay_assignment_actions paa,
8393: pay_payroll_actions ppa,
8394: pay_run_results prr
8395: where paa.payroll_action_id = p_pact_id
8396: and paa.assignment_action_id = prr.assignment_action_id
8397: and ppa.payroll_action_id = paa.payroll_action_id
8456: end if;
8457: if (l_defbal_rec.bd_needed = TRUE) then
8458: l_bd_needed_chr := 'Y';
8459: select ptp.end_date into l_earned_period_end_date
8460: from per_time_periods ptp, pay_payroll_actions ppa
8461: where ppa.payroll_action_id = p_pactid
8462: and ptp.payroll_id = ppa.payroll_id
8463: and ppa.date_earned between ptp.start_date and ptp.end_date;
8464: end if;
8782: ppa.effective_date,
8783: paa.assignment_id,
8784: paa.action_sequence
8785: from pay_assignment_actions paa,
8786: pay_payroll_actions ppa
8787: where ppa.payroll_action_id = paa.payroll_action_id
8788: and ppa.payroll_action_id = p_pact_id
8789: and rownum = 1;
8790: --
8821: , prr.time_definition_id
8822: , nvl(prr.end_date, ptp.end_date) balance_date
8823: , prr.local_unit_id local_unit_id
8824: from pay_assignment_actions paa,
8825: pay_payroll_actions ppa,
8826: per_time_periods ptp,
8827: pay_run_results prr
8828: where paa.payroll_action_id = p_pact_id
8829: and paa.assignment_action_id = prr.assignment_action_id
8834: order by 1, 2, 3, 4;
8835: --
8836: cursor get_bg (p_pact_id number) is
8837: select pbg.business_group_id, pbg.legislation_code
8838: from pay_payroll_actions ppa,
8839: per_business_groups_perf pbg
8840: where ppa.payroll_action_id = p_pact_id
8841: and pbg.business_group_id = ppa.business_group_id;
8842: --
8879: into l_bg_id,
8880: l_leg_code,
8881: l_eff_date
8882: from per_business_groups_perf pbg,
8883: pay_payroll_actions ppa
8884: where ppa.payroll_action_id = p_pact_id
8885: and ppa.business_group_id = pbg.business_group_id;
8886: --
8887: l_next_free := 1;
9264: , decode(p_lu_needed,
9265: 'Y', prr.local_unit_id,
9266: null) local_unit_id
9267: from pay_assignment_actions paa,
9268: pay_payroll_actions ppa,
9269: pay_run_results prr
9270: where paa.assignment_action_id = asgact
9271: and paa.assignment_action_id = prr.assignment_action_id
9272: and ppa.payroll_action_id = paa.payroll_action_id
9330: if (l_defbal_rec.bd_needed = TRUE) then
9331: l_bd_needed_chr := 'Y';
9332: select ptp.end_date into l_earned_period_end_date
9333: from pay_assignment_actions paa,
9334: pay_payroll_actions ppa,
9335: per_time_periods ptp
9336: where paa.assignment_action_id = p_asgact_id
9337: and ppa.payroll_action_id = paa.payroll_action_id
9338: and ptp.payroll_id = ppa.payroll_id
9689: ppa.effective_date,
9690: paa.assignment_id,
9691: paa.action_sequence
9692: from pay_assignment_actions paa,
9693: pay_payroll_actions ppa
9694: where ppa.payroll_action_id = paa.payroll_action_id
9695: and paa.assignment_action_id = asgact;
9696: --
9697: cursor get_contexts_2(asgact in number,
9726: , prr.time_definition_id
9727: , nvl(prr.end_date, ptp.end_date) balance_date
9728: , prr.local_unit_id
9729: from pay_assignment_actions paa,
9730: pay_payroll_actions ppa,
9731: per_time_periods ptp,
9732: pay_run_results prr
9733: where paa.assignment_action_id = asgact
9734: and paa.assignment_action_id = prr.assignment_action_id
9739: order by 1, 2, 3, 4;
9740: --
9741: cursor get_bg (aaid number) is
9742: select pbg.business_group_id, pbg.legislation_code
9743: from pay_payroll_actions ppa,
9744: pay_assignment_actions paa,
9745: per_business_groups_perf pbg
9746: where ppa.payroll_action_id = paa.payroll_action_id
9747: and paa.assignment_action_id = aaid
9784: pbg.legislation_code
9785: into l_bg_id,
9786: l_leg_code
9787: from per_business_groups_perf pbg,
9788: pay_payroll_actions ppa,
9789: pay_assignment_actions paa
9790: where paa.assignment_action_id = p_asgact_id
9791: and ppa.payroll_action_id = paa.payroll_action_id
9792: and ppa.business_group_id = pbg.business_group_id;
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
10122: where pbd.dimension_level = 'ASG'
10123: and pdb.save_run_balance = 'Y'
10124: and pdb.balance_dimension_id = pbd.balance_dimension_id
10139: is
10140: select /*+ ORDERED */
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
10199: select plr.rule_mode
10200: into save_run_bals
10201: from pay_legislation_rules plr,
10202: per_business_groups_perf pbg,
10203: pay_payroll_actions ppa,
10204: pay_assignment_actions paa
10205: where ppa.payroll_action_id = paa.payroll_action_id
10206: and paa.assignment_action_id = p_asgact_id
10207: and ppa.business_group_id = pbg.business_group_id
10315: , nvl(prr.end_date, ptp.end_date) balance_date
10316: , prr.local_unit_id
10317: from pay_assignment_actions paa,
10318: pay_run_results prr,
10319: pay_payroll_actions ppa,
10320: per_time_periods ptp
10321: where ppa.payroll_action_id = p_pact_id
10322: and paa.payroll_action_id = ppa.payroll_action_id
10323: and paa.assignment_action_id = prr.assignment_action_id
10329: cursor get_aa (p_pact_id in number) is
10330: select paa.assignment_action_id,
10331: ppa.effective_date
10332: from pay_assignment_actions paa,
10333: pay_payroll_actions ppa
10334: where ppa.payroll_action_id = p_pact_id
10335: and ppa.payroll_action_id = paa.payroll_action_id
10336: and rownum = 1;
10337: --
10336: and rownum = 1;
10337: --
10338: cursor get_bg (pactid number) is
10339: select pbg.business_group_id, pbg.legislation_code
10340: from pay_payroll_actions ppa,
10341: per_business_groups_perf pbg
10342: where payroll_action_id = pactid
10343: and pbg.business_group_id = ppa.business_group_id;
10344: --
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'
10587: and pdb.save_run_balance = 'Y'
10588: and pdb.balance_dimension_id = pbd.balance_dimension_id
10601: cursor crs_invalid (p_pact_id number)
10602: is
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
10659: select plr.rule_mode
10660: into save_run_bals
10661: from pay_legislation_rules plr,
10662: per_business_groups_perf pbg,
10663: pay_payroll_actions ppa
10664: where ppa.payroll_action_id = p_pact_id
10665: and ppa.business_group_id = pbg.business_group_id
10666: and pbg.legislation_code = plr.legislation_code
10667: and plr.rule_type = 'SAVE_RUN_BAL';
10798: --
10799: cursor bal_load_date(p_business_group_id number)
10800: is
10801: select nvl((max(ppa.effective_date)+1),fnd_date.canonical_to_date('0001/01/01')) bal_load_date
10802: from pay_payroll_actions ppa
10803: , pay_action_classifications pac
10804: where ppa.action_type = pac.action_type
10805: and pac.classification_name = 'SEQUENCED'
10806: and ppa.business_group_id = p_business_group_id;
11035: null) organization_id,
11036: ppa.effective_date
11037: from pay_assignment_actions paa,
11038: pay_run_results prr,
11039: pay_payroll_actions ppa
11040: where paa.assignment_action_id = revassactid
11041: and ppa.payroll_action_id = paa.payroll_action_id
11042: and paa.assignment_action_id = prr.assignment_action_id;
11043: --
11051: l_inp_val_name pay_input_values_f.name%type;
11052: l_asg_id number;
11053: l_pactid number;
11054: l_eff_date date;
11055: l_act_type pay_payroll_actions.action_type%type;
11056: lat_bal_maintenance boolean;
11057: l_src_iv varchar2(30);
11058: l_src_num varchar2(30);
11059: l_src_num2 varchar2(30);
11085: l_pactid,
11086: l_act_type,
11087: l_bus_grp
11088: from pay_assignment_actions paa,
11089: pay_payroll_actions ppa
11090: where paa.assignment_action_id = p_asg_action
11091: and ppa.payroll_action_id = paa.payroll_action_id;
11092: --
11093: create_all_asg_balances(p_asgact_id => p_asg_action,
11426: p_assignment_id number,
11427: p_from_date date )
11428: is
11429: select /*+ ORDERED INDEX(assact PAY_ASSIGNMENT_ACTIONS_N51)
11430: INDEX(pact PAY_PAYROLL_ACTIONS_PK)
11431: INDEX(rr PAY_RUN_RESULTS_N50)
11432: */
11433: assact.assignment_action_id,
11434: count(1) feed_count,
11434: count(1) feed_count,
11435: nvl(sum(fnd_number.canonical_to_number(rrv.result_value) * p_scale), 0) value
11436: from
11437: pay_assignment_actions assact,
11438: pay_payroll_actions pact,
11439: pay_run_results rr,
11440: pay_run_result_values rrv
11441: where assact.assignment_id = p_assignment_id
11442: --and assact.assignment_action_id in
11532: if g_debug then
11533: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. classbal feed.cnt:'||g_feeds_tab.count, 26);
11534: end if;
11535: select /*+ ORDERED INDEX(assact PAY_ASSIGNMENT_ACTIONS_N51)
11536: INDEX(pact PAY_PAYROLL_ACTIONS_PK)
11537: INDEX(rr PAY_RUN_RESULTS_N50)
11538: */
11539: assact.assignment_action_id,
11540: count(1) feed_count,
11541: nvl(sum(fnd_number.canonical_to_number(rrv.result_value) * FEED.scale), 0) asg_run
11542: bulk collect into g_asg_actions_tab
11543: from
11544: pay_assignment_actions assact,
11545: pay_payroll_actions pact,
11546: pay_run_results rr,
11547: pay_run_result_values rrv,
11548: pay_balance_feeds_f feed
11549: where assact.assignment_id = g_assignment_id
11726: is
11727: select /*+ ORDERED
11728: INDEX(gen_assact PAY_ASSIGNMENT_ACTIONS_N50)
11729: INDEX(assact PAY_ASSIGNMENT_ACTIONS_N51)
11730: INDEX(pact PAY_PAYROLL_ACTIONS_PK)
11731: INDEX(rr PAY_RUN_RESULTS_N50)
11732: */
11733: assact.assignment_action_id,
11734: count(1) feed_count,
11735: nvl(sum(fnd_number.canonical_to_number(rrv.result_value) * p_scale), 0) value
11736: from
11737: pay_assignment_actions gen_assact,
11738: pay_assignment_actions assact,
11739: pay_payroll_actions pact,
11740: pay_run_results rr,
11741: pay_run_result_values rrv
11742: where gen_assact.payroll_action_id = p_generate_pact
11743: and gen_assact.chunk_number = p_chunk_number
11836: end if;
11837: select /*+ ORDERED
11838: INDEX(gen_assact PAY_ASSIGNMENT_ACTIONS_N50)
11839: INDEX(assact PAY_ASSIGNMENT_ACTIONS_N51)
11840: INDEX(pact PAY_PAYROLL_ACTIONS_PK)
11841: INDEX(rr PAY_RUN_RESULTS_N50)
11842: */
11843: assact.assignment_action_id,
11844: count(1) feed_count,
11846: bulk collect into g_asg_actions_tab
11847: from
11848: pay_assignment_actions gen_assact,
11849: pay_assignment_actions assact,
11850: pay_payroll_actions pact,
11851: pay_run_results rr,
11852: pay_run_result_values rrv,
11853: pay_balance_feeds_f feed
11854: where gen_assact.payroll_action_id = p_generate_pact