[Home] [Help]
1070: ---------------------------------------------------------------------------
1071: procedure archive_balances
1072: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
1073: p_person_id in per_all_people_f.person_id%type,
1074: p_business_group_id in hr_organization_units.business_group_id%type,
1075: p_tax_unit_id in ff_archive_item_contexts.context%type,
1076: p_basis_year in varchar2 )
1077: is
1078: v_run_ass_action_id pay_assignment_actions.assignment_action_id%type;
1138: -- Cursor month_year_action_sequence
1139: ---------------------------------------------------------------------------------------------------
1140: cursor month_year_action_sequence
1141: ( c_person_id per_all_people_f.person_id%type,
1142: c_business_group_id hr_organization_units.business_group_id%type,
1143: c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
1144: c_basis_year varchar2 )
1145: is
1146: select /*+ ORDERED USE_NL(pacmax) */
1163: -- cursor month_year_action
1164: ---------------------------------------------------------------------------------------------------
1165: cursor month_year_action
1166: ( c_person_id per_all_people_f.person_id%type,
1167: c_business_group_id hr_organization_units.business_group_id%type,
1168: c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
1169: c_basis_year varchar2,
1170: c_action_sequence pay_assignment_actions.action_sequence%type )
1171: is
1763: ---------------------------------------------------------------------------
1764: procedure archive_balance_dates
1765: ( p_person_id in per_all_people_f.person_id%TYPE,
1766: p_basis_year in varchar2,
1767: p_business_group_id in hr_organization_units.business_group_id%type,
1768: p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
1769: p_run_ass_action_id in pay_assignment_actions.assignment_action_id%type,
1770: p_tax_unit_id in pay_assignment_actions.tax_unit_id%type )
1771: is
1819: ---------------------------------------------------------------------------
1820: cursor get_max_assactid( c_prev_ass_id per_all_assignments_f.assignment_id%TYPE ,
1821: c_basis_year varchar2,
1822: c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
1823: c_business_group_id hr_organization_units.business_group_id%type )
1824: is
1825: select assact1.assignment_action_id
1826: from pay_assignment_actions assact1,
1827: pay_payroll_actions pact1,
1853: --
1854: procedure get_balance_dates ( p_asg_action_id in pay_assignment_actions.assignment_action_id%type,
1855: p_tax_unit_id in pay_assignment_actions.tax_unit_id%type,
1856: p_balance_name in pay_balance_types.balance_name%type,
1857: p_business_group_id in hr_organization_units.business_group_id%type,
1858: p_date_from out nocopy ff_archive_items.value%type,
1859: p_date_to out nocopy ff_archive_items.value%type,
1860: p_no_of_times out nocopy number )
1861: is
1947: and pbd.legislation_code = 'SG';
1948:
1949: --
1950: cursor get_balance_status(c_def_balance_id pay_defined_balances.defined_balance_id%type,
1951: c_business_group_id hr_organization_units.business_group_id%type )
1952: is
1953: select run_balance_status
1954: from pay_balance_validation
1955: where defined_balance_id = c_def_balance_id
2494: -- Copies the Org Developer DF route code to get Legal Entity information.
2495: ---------------------------------------------------------------------------
2496: procedure archive_org_info
2497: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2498: p_business_group_id in hr_organization_units.business_group_id%type,
2499: p_legal_entity_id in hr_organization_units.organization_id%type,
2500: p_person_id in per_all_people_f.person_id%type,
2501: p_basis_start in date,
2502: p_basis_end in date)
2495: ---------------------------------------------------------------------------
2496: procedure archive_org_info
2497: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2498: p_business_group_id in hr_organization_units.business_group_id%type,
2499: p_legal_entity_id in hr_organization_units.organization_id%type,
2500: p_person_id in per_all_people_f.person_id%type,
2501: p_basis_start in date,
2502: p_basis_end in date)
2503: is
2524:
2525:
2526: --
2527: cursor org_info
2528: ( c_business_group_id hr_organization_units.business_group_id%type,
2529: c_legal_entity_id hr_organization_units.organization_id%type)
2530: is
2531: select target.org_information1,
2532: target.org_information4,
2525:
2526: --
2527: cursor org_info
2528: ( c_business_group_id hr_organization_units.business_group_id%type,
2529: c_legal_entity_id hr_organization_units.organization_id%type)
2530: is
2531: select target.org_information1,
2532: target.org_information4,
2533: target.org_information12,
2537: target.org_information17,
2538: target.org_information18,
2539: target.org_information8,
2540: target.org_information9
2541: from hr_organization_units org,
2542: hr_organization_information target,
2543: hr_soft_coding_keyflex scl
2544: where org.business_group_id = c_business_group_id
2545: and org.organization_id = c_legal_entity_id
2548: and to_char(org.organization_id) = scl.segment1;
2549: --
2550: -- Added for bug 5435088
2551: cursor org_info2
2552: ( c_business_group_id hr_organization_units.business_group_id%type,
2553: c_legal_entity_id hr_organization_units.organization_id%type)
2554: is
2555: select target.org_information5,
2556: target.org_information7
2549: --
2550: -- Added for bug 5435088
2551: cursor org_info2
2552: ( c_business_group_id hr_organization_units.business_group_id%type,
2553: c_legal_entity_id hr_organization_units.organization_id%type)
2554: is
2555: select target.org_information5,
2556: target.org_information7
2557: from hr_organization_units org,
2553: c_legal_entity_id hr_organization_units.organization_id%type)
2554: is
2555: select target.org_information5,
2556: target.org_information7
2557: from hr_organization_units org,
2558: hr_organization_information target,
2559: hr_soft_coding_keyflex scl
2560: where org.business_group_id = c_business_group_id
2561: and org.organization_id = c_legal_entity_id
2564: and to_char(org.organization_id) = scl.segment1;
2565:
2566: -- Added for bug 7415444, modified for bug 16032637
2567: cursor shares_nsop_count
2568: ( c_business_group_id hr_organization_units.business_group_id%type,
2569: c_legal_entity_id hr_organization_units.organization_id%type,
2570: c_basis_start date,
2571: c_basis_end date)
2572: is
2565:
2566: -- Added for bug 7415444, modified for bug 16032637
2567: cursor shares_nsop_count
2568: ( c_business_group_id hr_organization_units.business_group_id%type,
2569: c_legal_entity_id hr_organization_units.organization_id%type,
2570: c_basis_start date,
2571: c_basis_end date)
2572: is
2573: select
2772: -- Bug 5435088, Added for payroll date
2773: --------------------------------------------------------------------------
2774: procedure archive_payroll_date
2775: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2776: p_business_group_id in hr_organization_units.business_group_id%type,
2777: p_legal_entity_id in hr_organization_units.organization_id%type,
2778: p_person_id in per_all_people_f.person_id%type,
2779: p_basis_year in varchar2) is
2780:
2773: --------------------------------------------------------------------------
2774: procedure archive_payroll_date
2775: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2776: p_business_group_id in hr_organization_units.business_group_id%type,
2777: p_legal_entity_id in hr_organization_units.organization_id%type,
2778: p_person_id in per_all_people_f.person_id%type,
2779: p_basis_year in varchar2) is
2780:
2781: v_payroll_date varchar2(30);
2779: p_basis_year in varchar2) is
2780:
2781: v_payroll_date varchar2(30);
2782: cursor payroll_date
2783: ( c_business_group_id hr_organization_units.business_group_id%type,
2784: c_legal_entity_id hr_organization_units.organization_id%type,
2785: c_person_id per_all_people_f.person_id%type,
2786: c_basis_year varchar2)
2787: is
2780:
2781: v_payroll_date varchar2(30);
2782: cursor payroll_date
2783: ( c_business_group_id hr_organization_units.business_group_id%type,
2784: c_legal_entity_id hr_organization_units.organization_id%type,
2785: c_person_id per_all_people_f.person_id%type,
2786: c_basis_year varchar2)
2787: is
2788: select fnd_date.date_to_canonical(max(ppamax.effective_date))
3038: ---------------------------------------------------------------------------
3039: procedure archive_emp_details
3040: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3041: p_person_id in per_all_people_f.person_id%type,
3042: p_legal_entity_id in hr_organization_units.organization_id%type,
3043: p_basis_start in date,
3044: p_basis_end in date) is
3045:
3046: v_ass_start_date date;
3052: /* Bug 13711297 */
3053: /* Bug 13786754 */
3054: cursor ass_latest_join_dates
3055: (c_person_id per_all_people_f.person_id%type,
3056: c_legal_entity_id hr_organization_units.organization_id%type,
3057: c_basis_start date,
3058: c_basis_end date )
3059: is
3060: select min(assign.effective_start_date),
3073: and assign.effective_start_date >= c_basis_start;
3074:
3075: cursor ass_check_dayb4_a
3076: (c_person_id per_all_people_f.person_id%type,
3077: c_legal_entity_id hr_organization_units.organization_id%type,
3078: c_date date )
3079: is
3080: select 'Y'
3081: from per_assignments_f assign,
3090: and c_date between assign.effective_start_date and assign.effective_end_date;
3091:
3092: cursor ass_latest_term_dates
3093: (c_person_id per_all_people_f.person_id%type,
3094: c_legal_entity_id hr_organization_units.organization_id%type,
3095: c_basis_start date,
3096: c_basis_end date )
3097: is
3098: select min(assign.effective_end_date),
3384: procedure archive_assignment_eits
3385: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3386: p_person_id in per_all_people_f.person_id%type,
3387: p_assignment_id in per_all_assignments_f.assignment_id%type,
3388: p_legal_entity_id in hr_organization_units.organization_id%type,
3389: p_basis_start in date,
3390: p_basis_end in date )
3391: is
3392: --
3403: ---------------------------------------------------------------------------
3404: cursor assignment_eits
3405: ( c_person_id per_all_people_f.person_id%type,
3406: c_assignment_id per_assignments_f.assignment_id%type,
3407: c_legal_entity_id hr_organization_units.organization_id%type,
3408: c_basis_start date,
3409: c_basis_end date )
3410: is
3411: select /*+ USE_NL(aei) */
3452: procedure archive_ass_bonus_date_eits
3453: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3454: p_person_id in per_all_people_f.person_id%type,
3455: p_assignment_id in per_all_assignments_f.assignment_id%type,
3456: p_legal_entity_id in hr_organization_units.organization_id%type,
3457: p_basis_start in date,
3458: p_basis_end in date )
3459: is
3460: --
3463:
3464: cursor ass_bonus_date_eits
3465: ( c_person_id per_all_people_f.person_id%type,
3466: c_assignment_id per_assignments_f.assignment_id%type,
3467: c_legal_entity_id hr_organization_units.organization_id%type,
3468: c_basis_start date,
3469: c_basis_end date )
3470: is
3471: select /*+ USE_NL(aei) */
3517: procedure archive_ass_payment_method
3518: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3519: p_person_id in per_all_people_f.person_id%type,
3520: p_assignment_id in per_all_assignments_f.assignment_id%type,
3521: p_legal_entity_id in hr_organization_units.organization_id%type,
3522: p_basis_start in date,
3523: p_basis_end in date)
3524: is
3525: --
3528:
3529: cursor ass_payment_method
3530: ( c_person_id per_all_people_f.person_id%type,
3531: c_assignment_id per_assignments_f.assignment_id%type,
3532: c_legal_entity_id hr_organization_units.organization_id%type,
3533: c_basis_start in date,
3534: c_basis_end in date)
3535: is
3536:
3595: procedure archive_job_designation
3596: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3597: p_person_id in per_all_people_f.person_id%type,
3598: p_assignment_id in per_all_assignments_f.assignment_id%type,
3599: p_legal_entity_id in hr_organization_units.organization_id%type,
3600: p_basis_start in date,
3601: p_basis_end in date,
3602: p_er_designation_type in hr_organization_information.org_information17%type,
3603: p_er_position_seg_type in hr_organization_information.org_information18%type)
3615: ---------------------------------------------------------------------------
3616: cursor grade
3617: ( c_person_id per_all_people_f.person_id%type,
3618: c_assignment_id per_assignments_f.assignment_id%type,
3619: c_legal_entity_id hr_organization_units.organization_id%type,
3620: c_basis_start date,
3621: c_basis_end date) is
3622:
3623: select grade.name
3649: ---------------------------------------------------------------------------
3650: cursor job
3651: ( c_person_id per_all_people_f.person_id%type,
3652: c_assignment_id per_all_assignments_f.assignment_id%type,
3653: c_legal_entity_id hr_organization_units.organization_id%type,
3654: c_basis_start date,
3655: c_basis_end date) is
3656:
3657: select jbt.name
3684: ---------------------------------------------------------------------------
3685: cursor position
3686: ( c_person_id per_all_people_f.person_id%type,
3687: c_assignment_id per_all_assignments_f.assignment_id%type,
3688: c_legal_entity_id hr_organization_units.organization_id%type,
3689: c_basis_start date,
3690: c_basis_end date) is
3691:
3692: select pst.name
3724: ( c_person_id per_all_people_f.person_id%type,
3725: c_assignment_id per_all_assignments_f.assignment_id%type,
3726: c_basis_start date,
3727: c_basis_end date,
3728: c_legal_entity_id hr_organization_units.organization_id%type,
3729: c_er_position_seg_type hr_organization_information.org_information18
3730: %type) is
3731:
3732: select decode(fifs.application_column_name, 'SEGMENT1', ppd.segment1,
3763: hr_soft_coding_keyflex hsc,
3764: hr_all_positions_f pos,
3765: hr_all_positions_f_tl pst,
3766: per_position_definitions ppd,
3767: hr_organization_units hou,
3768: hr_organization_information hoi,
3769: fnd_id_flex_segments fifs,
3770: fnd_id_flex_structures fift
3771: where assign.person_id = c_person_id
3811:
3812: cursor other
3813: ( c_person_id per_all_people_f.person_id%type,
3814: c_assignment_id per_all_assignments_f.assignment_id%type,
3815: c_legal_entity_id hr_organization_units.organization_id%type,
3816: c_basis_start date,
3817: c_basis_end date) is
3818:
3819: select /*+ USE_NL(aei) */
3899: ---------------------------------------------------------------------------
3900: procedure archive_os_assignment
3901: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3902: p_person_id in per_all_people_f.person_id%type,
3903: p_legal_entity_id in hr_organization_units.organization_id%type,
3904: p_basis_start in date,
3905: p_basis_end in date )
3906: is
3907: --
3917: -- Bug# 4688761 - Modified cursor to check the legal entity
3918: ---------------------------------------------------------------------------
3919: cursor os_assignment
3920: ( c_person_id per_all_people_f.person_id%type,
3921: c_legal_entity_id hr_organization_units.organization_id%type,
3922: c_basis_start date,
3923: c_basis_end date )
3924: is
3925: select aei.aei_information1,