[Home] [Help]
109: p_effective_date - Effective date of Payroll Run
110: ******************************************************************************/
111: FUNCTION get_element_payment_hours
112: (
113: p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
114: p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
115: p_pay_bases_id IN per_all_assignments_f.pay_basis_id%TYPE,
116: p_run_result_id IN pay_run_results.run_result_ID%TYPE,
117: p_effective_date IN pay_payroll_actions.effective_date%TYPE
738: pay_element_types_f_tl petfl,
739: pay_element_classifications pec,
740: pay_run_results prr,
741: pay_action_interlocks pai,
742: pay_assignment_actions paa_pre,
743: pay_payroll_actions ppa_pre
744: WHERE ppa_pre.action_type IN ('U', 'P')
745: AND ppa_pre.effective_date > cp_last_xfr_eff_date
746: AND ppa_pre.effective_date <= cp_last_pymt_eff_date
1430: FROM pay_element_types_f petf,
1431: pay_element_types_f_tl petl,
1432: pay_element_classifications pec,
1433: pay_run_results prr,
1434: pay_assignment_actions paa,
1435: pay_payroll_actions ppa
1436: WHERE
1437: petf.element_type_id = prr.element_type_id
1438: AND pec.classification_id = petf.classification_id
1465: FROM pay_element_types_f petf,
1466: pay_element_types_f_tl petl,
1467: pay_element_classifications pec,
1468: pay_run_results prr,
1469: pay_assignment_actions paa,
1470: pay_action_interlocks pai,
1471: pay_payroll_actions ppa
1472: WHERE
1473: petf.element_type_id = prr.element_type_id
1489: ORDER BY 1;
1490:
1491: CURSOR c_ytd_action_seq(cp_asg_act_id IN NUMBER) IS
1492: SELECT paa.action_sequence
1493: FROM pay_assignment_actions paa
1494: WHERE paa.assignment_action_id = cp_asg_act_id;
1495:
1496: CURSOR c_business_grp_id IS
1497: SELECT DISTINCT business_group_id
1697: ,p_action_type IN VARCHAR2 DEFAULT NULL
1698: )
1699: IS
1700:
1701: ln_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
1702: lv_classification_name pay_element_classifications.classification_name%TYPE;
1703: lv_reporting_name pay_element_types_f.reporting_name%TYPE;
1704: ln_element_type_id pay_element_types_f.element_type_id%TYPE;
1705: lv_element_category pay_element_classifications.classification_name%TYPE;
1754: ,get_element_payment_hours(prr.assignment_action_id, pet.element_type_id
1755: ,paf.pay_basis_id, prr.run_result_id, ppa.effective_date) hours
1756: ,pet.element_type_id element_type_id
1757: FROM pay_payroll_actions ppa
1758: ,pay_assignment_actions paa
1759: ,pay_run_results prr
1760: ,pay_run_result_values prrv
1761: ,pay_input_values_f piv
1762: ,pay_element_types_f pet
1840: ,get_element_payment_hours(prr.assignment_action_id, pet.element_type_id
1841: ,paf.pay_basis_id, prr.run_result_id, ppa.effective_date) hours
1842: ,pet.element_type_id element_type_id
1843: FROM pay_payroll_actions ppa
1844: ,pay_assignment_actions paa
1845: ,pay_run_results prr
1846: ,pay_run_result_values prrv
1847: ,pay_input_values_f piv
1848: ,pay_element_types_f pet
2067: pec.classification_name
2068: FROM pay_element_types_f petf,
2069: pay_element_types_f_tl petl,
2070: pay_element_classifications pec,
2071: pay_assignment_actions paa,
2072: pay_payroll_actions ppa,
2073: pay_run_results prr
2074: WHERE petf.element_type_id = prr.element_type_id
2075: AND pec.classification_id = petf.classification_id
2298: hr_utility.set_location(gv_package || lv_procedure_name, 20);
2299:
2300: lv_sql_string :=
2301: 'SELECT DISTINCT paf.person_id
2302: FROM pay_assignment_actions paa,
2303: pay_payroll_actions ppa,
2304: per_assignments_f paf
2305: WHERE ppa.business_group_id = ''' || ln_business_group_id || '''
2306: AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
2321: AND paf.effective_end_date
2322: AND NOT EXISTS
2323: (SELECT ''x''
2324: FROM pay_action_interlocks pai,
2325: pay_assignment_actions paa1,
2326: pay_payroll_actions ppa1
2327: WHERE pai.locked_action_id = paa.assignment_action_id
2328: AND paa1.assignment_action_id = pai.locking_action_id
2329: AND ppa1.payroll_action_id = paa1.payroll_action_id
2369: ppa.action_type,
2370: paa.assignment_action_id,
2371: paa.payroll_action_id
2372: FROM pay_payroll_actions ppa,
2373: pay_assignment_actions paa,
2374: per_assignments_f paf
2375: WHERE paf.person_id BETWEEN cp_start_person_id
2376: AND cp_end_person_id
2377: AND paa.assignment_id = paf.assignment_id
2392: AND paa.source_action_id IS NULL
2393: AND NOT EXISTS
2394: (SELECT 'x'
2395: FROM pay_action_interlocks pai1,
2396: pay_assignment_actions paa1,
2397: pay_payroll_actions ppa1
2398: WHERE pai1.locked_action_id = paa.assignment_action_id
2399: AND paa1.assignment_action_id = pai1.locking_action_id
2400: AND ppa1.payroll_action_id = paa1.payroll_action_id
2418: ppa.action_type,
2419: paa.assignment_action_id,
2420: paa.payroll_action_id
2421: FROM pay_payroll_actions ppa,
2422: pay_assignment_actions paa,
2423: per_assignments_f paf,
2424: pay_population_ranges ppr
2425: WHERE ppr.payroll_action_id = cp_payroll_action_id
2426: AND ppr.chunk_number = cp_chunk_number
2443: AND paa.source_action_id IS NULL
2444: AND NOT EXISTS
2445: (SELECT 'x'
2446: FROM pay_action_interlocks pai1,
2447: pay_assignment_actions paa1,
2448: pay_payroll_actions ppa1
2449: WHERE pai1.locked_action_id = paa.assignment_action_id
2450: AND paa1.assignment_action_id = pai1.locking_action_id
2451: AND ppa1.payroll_action_id = paa1.payroll_action_id
2455:
2456: CURSOR c_master_action(cp_prepayment_action_id NUMBER) IS
2457: SELECT MAX(paa.assignment_action_id)
2458: FROM pay_payroll_actions ppa,
2459: pay_assignment_actions paa,
2460: pay_action_interlocks pai
2461: WHERE pai.locking_action_Id = cp_prepayment_action_id
2462: AND paa.assignment_action_id = pai.locked_action_id
2463: AND paa.source_action_id IS NULL
2487: ld_prev_effective_date DATE;
2488:
2489: ln_xfr_action_id NUMBER;
2490:
2491: lv_serial_number pay_assignment_actions.serial_number%TYPE;
2492: lv_procedure_name VARCHAR2(100);
2493: lv_error_message VARCHAR2(200);
2494: ln_step NUMBER;
2495:
2600: ELSE
2601: hr_utility.set_location(gv_package || lv_procedure_name, 60);
2602: hr_utility.trace('Action_type = '||lv_action_type );
2603:
2604: SELECT pay_assignment_actions_s.NEXTVAL
2605: INTO ln_xfr_action_id
2606: FROM dual;
2607:
2608: -- insert into pay_assignment_actions.
2604: SELECT pay_assignment_actions_s.NEXTVAL
2605: INTO ln_xfr_action_id
2606: FROM dual;
2607:
2608: -- insert into pay_assignment_actions.
2609: hr_nonrun_asact.insact(ln_xfr_action_id,
2610: ln_assignment_id,
2611: p_payroll_action_id,
2612: p_chunk,
2633: ** For Balance Adj we put only the first assignment action
2634: ********************************************************/
2635: lv_serial_number := lv_action_type || 'N' || ln_asg_action_id;
2636:
2637: UPDATE pay_assignment_actions
2638: SET serial_number = lv_serial_number
2639: WHERE assignment_action_id = ln_xfr_action_id;
2640:
2641: hr_utility.set_location(gv_package || lv_procedure_name, 100);
3080: AND paf.effective_end_date;
3081:
3082: CURSOR c_get_gre IS
3083: SELECT DISTINCT paa.tax_unit_id
3084: FROM pay_assignment_actions paa
3085: WHERE paa.payroll_action_id = p_payroll_action_id;
3086:
3087: PROCEDURE get_ER_address(p_organization_id IN NUMBER,
3088: p_org_or_gre IN VARCHAR2)
3365: ,pet.reporting_name reporting_name
3366: ,decode(pet.processing_type,'R',greatest(pab.date_start,PTP.START_DATE),pab.date_start) start_date
3367: ,decode(pet.processing_type,'R',least(pab.date_end,PTP.END_DATE),pab.date_end) end_date
3368: ,decode(pet.processing_type,'R',to_number(prrv.result_value),nvl(pab.absence_days,pab.absence_hours)) absence_days
3369: FROM pay_assignment_actions paa
3370: ,pay_payroll_actions ppa
3371: ,pay_run_results prr
3372: ,pay_run_result_values prrv
3373: ,per_time_periods ptp
3702:
3703: CURSOR c_ytd_aaid(cp_prepayment_action_id IN NUMBER
3704: ,cp_assignment_id IN NUMBER) IS
3705: SELECT paa.assignment_action_id
3706: FROM pay_assignment_actions paa,
3707: pay_action_interlocks pai,
3708: pay_payroll_actions ppa
3709: WHERE pai.locking_action_id = cp_prepayment_action_id
3710: AND paa.assignment_action_id = pai.locked_action_id
3717: CURSOR c_time_period(cp_run_assignment_action IN NUMBER) IS
3718: SELECT ptp.time_period_id,
3719: ppa.date_earned,
3720: ppa.effective_date
3721: FROM pay_assignment_actions paa,
3722: pay_payroll_actions ppa,
3723: per_time_periods ptp
3724: WHERE paa.assignment_action_id = cp_run_assignment_action
3725: AND ppa.payroll_action_id = paa.payroll_action_id
3727: AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
3728:
3729: CURSOR c_chk_act_type(cp_last_xfr_act_id NUMBER) IS
3730: SELECT SUBSTR(serial_number,1,1)
3731: FROM pay_assignment_actions paa
3732: WHERE paa.assignment_action_id = cp_last_xfr_act_id;
3733:
3734: CURSOR c_get_archive_ytd_flag(cp_business_group_id IN NUMBER,
3735: cp_legislation_code IN VARCHAR2) is
3739: AND ORG_INFORMATION_CONTEXT = cp_legislation_code||'_REPORTING_PREFERENCES';
3740:
3741: CURSOR c_get_archive_absences_aaid(cp_action_id IN NUMBER) IS
3742: SELECT assignment_action_id
3743: FROM pay_assignment_actions
3744: WHERE source_action_id = cp_action_id
3745: ORDER BY assignment_action_id;
3746:
3747: lv_pre_xfr_act_type VARCHAR2(80);
4130: paa.assignment_id,
4131: paa.tax_unit_id,
4132: paa.serial_number,
4133: paa.chunk_number
4134: FROM pay_assignment_actions paa
4135: WHERE paa.assignment_action_id = cp_assignment_action;
4136:
4137: CURSOR c_assignment_run (cp_prepayment_action_id IN NUMBER) IS
4138: SELECT DISTINCT paa.assignment_id
4136:
4137: CURSOR c_assignment_run (cp_prepayment_action_id IN NUMBER) IS
4138: SELECT DISTINCT paa.assignment_id
4139: FROM pay_action_interlocks pai,
4140: pay_assignment_actions paa,
4141: pay_payroll_actions ppa
4142: WHERE pai.locking_action_id = cp_prepayment_action_id
4143: AND paa.assignment_action_id = pai.locked_action_id
4144: AND ppa.payroll_action_id = paa.payroll_action_id
4154: cp_assignment_id IN NUMBER) IS
4155: SELECT paa.assignment_action_id, paa.payroll_action_id,
4156: ppa.action_type
4157: FROM pay_payroll_actions ppa,
4158: pay_assignment_actions paa,
4159: pay_action_interlocks pai
4160: WHERE pai.locking_action_id = cp_prepayment_action_id
4161: AND pai.locked_action_id = paa.assignment_action_id
4162: AND paa.assignment_id = cp_assignment_id
4166:
4167: CURSOR c_pymt_eff_date(cp_prepayment_action_id IN NUMBER) IS
4168: SELECT ppa.effective_date
4169: FROM pay_payroll_actions ppa,
4170: pay_assignment_actions paa
4171: WHERE ppa.payroll_action_id = paa.payroll_action_id
4172: AND paa.assignment_action_id = cp_prepayment_action_id;
4173:
4174: CURSOR c_check_pay_action( cp_payroll_action_id IN NUMBER) IS
4190: ,cp_assignment_id NUMBER
4191: ,cp_tax_unit_id NUMBER) IS
4192: SELECT paa.assignment_action_id
4193: ,paa.source_action_id
4194: FROM pay_assignment_actions paa
4195: ,pay_action_interlocks pai
4196: where pai.locking_action_id = cp_pp_asg_act_id
4197: AND paa.assignment_action_id = pai.locked_action_id
4198: AND paa.assignment_id = cp_assignment_id
4209:
4210: CURSOR c_get_prepay_aaid_for_sepchk( cp_asg_act_id NUMBER,
4211: cp_source_act_id NUMBER ) IS
4212: SELECT ppp.assignment_action_id
4213: FROM pay_assignment_actions paa
4214: ,pay_pre_payments ppp
4215: WHERE ( paa.assignment_action_id = cp_asg_act_id OR
4216: paa.source_action_id = cp_asg_act_id )
4217: AND ppp.assignment_action_id = paa.assignment_action_id
4222: ,cp_assignment_id NUMBER
4223: ,cp_tax_unit_id NUMBER) IS
4224: SELECT paa.assignment_action_id
4225: ,paa.source_action_id
4226: FROM pay_assignment_actions paa
4227: ,pay_action_interlocks pai
4228: WHERE pai.locking_action_id = cp_pp_asg_act_id
4229: AND paa.assignment_action_id = pai.locked_action_id
4230: AND paa.assignment_id = cp_assignment_id
4236: ,cp_assignment_id IN NUMBER
4237: ,cp_tax_unit_id IN NUMBER
4238: ,cp_legislation_code IN VARCHAR2) IS
4239: SELECT paa.assignment_action_id
4240: FROM pay_assignment_actions paa,
4241: pay_action_interlocks pai
4242: WHERE pai.locking_action_id = cp_pp_asg_act_id
4243: AND paa.assignment_action_id = pai.locked_action_id
4244: AND paa.assignment_id = cp_assignment_id
4268: ln_xfr_assignment_id NUMBER;
4269: ln_assignment_id NUMBER;
4270: ln_chunk_number NUMBER;
4271:
4272: lv_xfr_master_serial_number pay_assignment_actions.serial_number%TYPE;
4273: lv_master_action_type pay_payroll_actions.action_type%TYPE;
4274:
4275: lv_master_sepcheck_flag VARCHAR2(1);
4276: ln_asg_action_id NUMBER;
4305: ln_rqp_action_id NUMBER;
4306: ln_ppp_source_action_id NUMBER;
4307: ln_master_run_aa_id NUMBER;
4308: ln_earnings NUMBER;
4309: lv_serial_number pay_assignment_actions.serial_number%TYPE;
4310:
4311: ln_is_run_qp_found NUMBER;
4312: ln_all_run_asg_act_id NUMBER;
4313:
4509: FETCH c_get_prepay_aaid_for_sepchk INTO ln_asg_action_id;
4510: CLOSE c_get_prepay_aaid_for_sepchk;
4511:
4512: ln_step := 13;
4513: SELECT pay_assignment_actions_s.nextval
4514: INTO ln_child_xfr_action_id
4515: FROM dual;
4516:
4517: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4515: FROM dual;
4516:
4517: hr_utility.set_location(gv_package || lv_procedure_name, 70);
4518:
4519: -- insert into pay_assignment_actions.
4520:
4521: ln_step := 14;
4522:
4523:
4548: lv_master_sepcheck_flag || ln_source_action_id;
4549:
4550: ln_step := 17;
4551:
4552: update pay_assignment_actions
4553: set serial_number = lv_serial_number
4554: WHERE assignment_action_id = ln_child_xfr_action_id;
4555:
4556: hr_utility.trace('Processing Child action ' ||