[Home] [Help]
33: --------------------------------------------------------------------------------------------------------
34: -- Bug# 3501927 A8A Balance store rec
35: --------------------------------------------------------------------------------------------------------
36: type ytd_a8a_balance_store_rec is record
37: ( balance_id ff_user_entities.user_entity_id%type,
38: balance_value number );
39: type ytd_a8a_balance_tab is table of ytd_a8a_balance_store_rec index by binary_integer;
40: ytd_a8a_balance_rec ytd_a8a_balance_tab;
41: -- Bug# 3933332
418: --
419: cursor ytd_A8A_balances is
420: select fue.user_entity_id,
421: pdb.defined_balance_id def_bal_id
422: from ff_user_entities fue,
423: pay_balance_types pbt,
424: pay_defined_balances pdb,
425: pay_balance_dimensions pbd
426: where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_YTD'
899: --
900: type t_date_earned_tab is table of varchar2(30) index by binary_integer;
901: t_date_earned t_date_earned_tab;
902: --
903: type t_user_entity_tab is table of ff_user_entities.user_entity_id%TYPE index by binary_integer;
904: t_user_entity_id t_user_entity_tab;
905: t_amend_ue_id t_user_entity_tab;
906: t_orig_user_entity_id t_user_entity_tab;
907: t_user_entity_id_ir8a t_user_entity_tab;
916: ---------------------------------------------------------------------------------------------------
917: cursor ytd_balances_ir8s is
918: select fue.user_entity_id,
919: pdb.defined_balance_id def_bal_id
920: from ff_user_entities fue,
921: pay_balance_types pbt,
922: pay_defined_balances pdb,
923: pay_balance_dimensions pbd
924: where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_YTD'
946:
947: cursor ytd_balances_ir8a is
948: select fue.user_entity_id,
949: pdb.defined_balance_id def_bal_id
950: from ff_user_entities fue,
951: pay_balance_types pbt,
952: pay_defined_balances pdb,
953: pay_balance_dimensions pbd
954: where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_YTD'
1021: cursor month_balances
1022: is
1023: select fue.user_entity_id,
1024: pdb.defined_balance_id def_bal_id
1025: from ff_user_entities fue,
1026: pay_balance_types pbt,
1027: pay_defined_balances pdb,
1028: pay_balance_dimensions pbd
1029: where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_MONTH'
1049: -- Balance Store Record
1050: ---------------------------------------------------------------------------------------------------
1051: --
1052: type ytd_balance_store_rec is record
1053: ( balance_id ff_user_entities.user_entity_id%type,
1054: balance_value number );
1055: type ytd_balance_tab is table of ytd_balance_store_rec index by binary_integer;
1056: ytd_balance_rec_ir8a ytd_balance_tab;
1057: ytd_balance_rec_ir8s ytd_balance_tab;
1056: ytd_balance_rec_ir8a ytd_balance_tab;
1057: ytd_balance_rec_ir8s ytd_balance_tab;
1058: --
1059: type mtd_balance_store_rec is record
1060: ( balance_id ff_user_entities.user_entity_id%type,
1061: balance_value number,
1062: date_earned varchar2(6),
1063: date_earned_archive varchar2(30),
1064: person_id number,
1533: FOR i IN 1..t_user_entity_id.count LOOP
1534:
1535: select user_entity_name
1536: into l_name_ue
1537: from ff_user_entities where user_entity_id = t_user_entity_id(i);
1538:
1539: SELECT assignment_id
1540: INTO l_assignment_id
1541: FROM pay_assignment_Actions paa
1646: FOR counter IN 1..t_user_entity_id_ir8a.COUNT LOOP
1647:
1648: select user_entity_name
1649: into l_name_ue
1650: from ff_user_entities
1651: where user_entity_id = t_user_entity_id_ir8a(counter);
1652:
1653: begin
1654: l_temp_value :='';
1703: FOR counter IN 1..t_user_entity_id_ir8s.COUNT LOOP
1704:
1705: select user_entity_name
1706: into l_name_ue
1707: from ff_user_entities
1708: where user_entity_id = t_user_entity_id_ir8s(counter);
1709:
1710: begin
1711: l_temp_value :='';
1775: FOR i in 1..t_user_entity_id_a8a.count LOOP
1776:
1777: select user_entity_name
1778: into l_name_ue
1779: from ff_user_entities
1780: where user_entity_id = t_user_entity_id_a8a(i);
1781:
1782: begin
1783: l_temp_value :='';
1840: archive_item ('X_IRAS_METHOD', p_assignment_action_id, 'A');
1841:
1842: select ue.user_entity_id
1843: bulk collect into t_amend_ue_id
1844: from ff_user_entities ue
1845: where ue.user_entity_name in ('X_IR8A_AMEND_INDICATOR',
1846: 'X_A8B_AMEND_INDICATOR',
1847: 'X_IR8S_AMEND_INDICATOR',
1848: 'X_A8A_AMEND_INDICATOR')
2072: hr_utility.set_location('pysgiraa: archive_balances ',160);
2073:
2074: select arch.value,ue.user_entity_id
2075: bulk collect into t_orig_value,t_orig_user_entity_id
2076: from ff_user_entities ue ,
2077: ff_archive_items arch
2078: where ue.user_entity_name in ('X_PER_NATIONAL_IDENTIFIER',
2079: 'X_PER_SEX',
2080: 'X_PER_DATE_OF_BIRTH',
2226:
2227: type t_amend_value_tab is table of ff_archive_items.value%TYPE index by binary_integer;
2228: t_amend_value_a8b t_amend_value_tab;
2229:
2230: type t_user_entity_tab is table of ff_user_entities.user_entity_id%TYPE index by binary_integer;
2231: t_user_entity_id_a8b t_user_entity_tab;
2232:
2233: type t_user_entity_name_tab is table of ff_user_entities.user_entity_name%TYPE index by binary_integer;
2234: t_user_entity_name_a8b t_user_entity_name_tab;
2229:
2230: type t_user_entity_tab is table of ff_user_entities.user_entity_id%TYPE index by binary_integer;
2231: t_user_entity_id_a8b t_user_entity_tab;
2232:
2233: type t_user_entity_name_tab is table of ff_user_entities.user_entity_name%TYPE index by binary_integer;
2234: t_user_entity_name_a8b t_user_entity_name_tab;
2235:
2236: type t_assignment_extra_info_tab is table of per_assignment_extra_info.assignment_extra_info_id%TYPE index by binary_integer;
2237: t_aeid_a8b t_assignment_extra_info_tab;
2337: and (pap.effective_start_date <= c_basis_end and pap.effective_end_date >= c_basis_start);
2338: --
2339:
2340: cursor shares_removed
2341: (c_user_entity_id ff_user_entities.user_entity_id%type,
2342: c_assignment_id per_assignment_extra_info.assignment_id%type,
2343: c_person_id per_people_f.person_id%type,
2344: c_tax_unit_id ff_archive_item_contexts.context%type) is
2345:
2406: WHERE paa.assignment_action_id = p_assignment_action_id;
2407:
2408: select arch.value
2409: into v_er_incorp_date
2410: from ff_user_entities ue ,
2411: ff_archive_items arch
2412: where ue.user_entity_name = 'X_SG_LEGAL_ENTITY_SG_A8B_INCORP_DATE'
2413: and ue.user_entity_id = arch.user_entity_id
2414: AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
2431: end if;
2432:
2433: select ue.user_entity_id, ue.user_entity_name
2434: bulk collect into t_user_entity_id_a8b, t_user_entity_name_a8b
2435: from ff_user_entities ue
2436: where ue.user_entity_name in (
2437: 'X_A8B_COMPANY'
2438: ,'X_A8B_COMPANY_TYPE'
2439: ,'X_A8B_EXERCISED_DATE'
2490: g_amend_a8b_flag := 'Y';
2491:
2492: select arch.value
2493: bulk collect into t_amend_value_a8b
2494: from ff_user_entities ue ,
2495: ff_archive_items arch
2496: where ue.user_entity_name in ('X_A8B_COMPANY'
2497: ,'X_A8B_RCB'
2498: ,'X_A8B_COMPANY_TYPE'
2862: type t_amend_value_tab is table of ff_archive_items.value%TYPE index by binary_integer;
2863: t_amend_value_ir8s_c t_amend_value_tab;
2864: t_amend_value_ir8s_c1 t_amend_value_tab;
2865:
2866: type t_user_entity_tab is table of ff_user_entities.user_entity_id%TYPE index by binary_integer;
2867: t_user_entity_id_ir8s_c t_user_entity_tab;
2868:
2869: type t_user_entity_name_tab is table of ff_user_entities.user_entity_name%TYPE index by binary_integer;
2870: t_user_entity_name_ir8s_c t_user_entity_name_tab;
2865:
2866: type t_user_entity_tab is table of ff_user_entities.user_entity_id%TYPE index by binary_integer;
2867: t_user_entity_id_ir8s_c t_user_entity_tab;
2868:
2869: type t_user_entity_name_tab is table of ff_user_entities.user_entity_name%TYPE index by binary_integer;
2870: t_user_entity_name_ir8s_c t_user_entity_name_tab;
2871:
2872:
2873: type t_assignment_extra_info_tab is table of per_assignment_extra_info.assignment_extra_info_id%TYPE index by binary_integer;
2918: and (ass.effective_start_date <= c_basis_end
2919: and ass.effective_end_date >= c_basis_start);
2920:
2921: cursor ir8s_c_removed
2922: (c_user_entity_id ff_user_entities.user_entity_id%type,
2923: c_assignment_id per_assignment_extra_info.assignment_id%type,
2924: c_tax_unit_id ff_archive_item_contexts.context%type) is
2925:
2926: select distinct con2.context assignment_extra_info_id
2966: end if;
2967: --
2968: select ue.user_entity_id, ue.user_entity_name
2969: bulk collect into t_user_entity_id_ir8s_c, t_user_entity_name_ir8s_c
2970: from ff_user_entities ue
2971: where ue.user_entity_name in ('X_MOA410',
2972: 'X_MOA411',
2973: 'X_MOA412',
2974: 'X_MOA413',
3043: end loop;
3044:
3045: select arch.value
3046: bulk collect into t_amend_value_ir8s_c1
3047: from ff_user_entities ue ,
3048: ff_archive_items arch
3049: where ue.user_entity_name in ('X_DTM502'
3050: ,'X_DTM503'
3051: ,'X_DTM504'
3121: for counter in 1..t_user_entity_id_ir8s_c.count
3122: loop
3123: select user_entity_name
3124: into l_name_ue
3125: from ff_user_entities where user_entity_id = t_user_entity_id_ir8s_c(counter);
3126:
3127: begin
3128: l_temp_value := ' ';
3129: select sum(value)
3229: ---------------------------------------------------------------------------
3230: -- Calls the archive utility to actually perform the archive of the item.
3231: ---------------------------------------------------------------------------
3232: procedure archive_item
3233: ( p_user_entity_name in ff_user_entities.user_entity_name%type,
3234: p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3235: p_archive_value in ff_archive_items.value%type )
3236: is
3237: --
3234: p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3235: p_archive_value in ff_archive_items.value%type )
3236: is
3237: --
3238: v_user_entity_id ff_user_entities.user_entity_id%type;
3239: v_archive_item_id ff_archive_items.archive_item_id%type;
3240: v_object_version_number ff_archive_items.object_version_number%type;
3241: v_some_warning boolean;
3242: ---------------------------------------------------------------------------
3242: ---------------------------------------------------------------------------
3243: -- Cursor User_Entity_ID
3244: ---------------------------------------------------------------------------
3245: cursor user_entity_id
3246: ( c_user_entity_name ff_user_entities.user_entity_name%type )
3247: is
3248: select user_entity_id
3249: from ff_user_entities
3250: where user_entity_name = c_user_entity_name;
3245: cursor user_entity_id
3246: ( c_user_entity_name ff_user_entities.user_entity_name%type )
3247: is
3248: select user_entity_id
3249: from ff_user_entities
3250: where user_entity_name = c_user_entity_name;
3251: --
3252: begin
3253: if g_debug then
3280: -- Calls the archive utility to actually perform the archive of the item with
3281: -- one another context
3282: -----------------------------------------------------------------------------
3283: procedure archive_item_2
3284: ( p_user_entity_name in ff_user_entities.user_entity_name%type,
3285: p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3286: p_archive_value in ff_archive_items.value%type,
3287: p_context_value2 in ff_archive_item_contexts.context%type )
3288: is
3286: p_archive_value in ff_archive_items.value%type,
3287: p_context_value2 in ff_archive_item_contexts.context%type )
3288: is
3289: --
3290: v_user_entity_id ff_user_entities.user_entity_id%type;
3291: v_archive_item_id ff_archive_items.archive_item_id%type;
3292: v_object_version_number ff_archive_items.object_version_number%type;
3293: v_some_warning boolean;
3294: ---------------------------------------------------------------------------
3294: ---------------------------------------------------------------------------
3295: -- Cursor User_Entity_ID
3296: ---------------------------------------------------------------------------
3297: cursor user_entity_id
3298: ( c_user_entity_name ff_user_entities.user_entity_name%type )
3299: is
3300: select user_entity_id
3301: from ff_user_entities
3302: where user_entity_name = c_user_entity_name;
3297: cursor user_entity_id
3298: ( c_user_entity_name ff_user_entities.user_entity_name%type )
3299: is
3300: select user_entity_id
3301: from ff_user_entities
3302: where user_entity_name = c_user_entity_name;
3303: --
3304: begin
3305: if g_debug then
3334: -- Calls the archive utility to actually perform the archive of the item with
3335: -- one another context
3336: -----------------------------------------------------------------------------
3337: procedure archive_item_3
3338: ( p_user_entity_name in ff_user_entities.user_entity_name%type,
3339: p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3340: p_archive_value in ff_archive_items.value%type,
3341: p_context_value2 in ff_archive_item_contexts.context%type,
3342: p_context_value3 in ff_archive_item_contexts.context%type )
3341: p_context_value2 in ff_archive_item_contexts.context%type,
3342: p_context_value3 in ff_archive_item_contexts.context%type )
3343: is
3344: --
3345: v_user_entity_id ff_user_entities.user_entity_id%type;
3346: v_archive_item_id ff_archive_items.archive_item_id%type;
3347: v_object_version_number ff_archive_items.object_version_number%type;
3348: v_some_warning boolean;
3349: ---------------------------------------------------------------------------
3349: ---------------------------------------------------------------------------
3350: -- Cursor User_Entity_ID
3351: ---------------------------------------------------------------------------
3352: cursor user_entity_id
3353: ( c_user_entity_name ff_user_entities.user_entity_name%type )
3354: is
3355: select user_entity_id
3356: from ff_user_entities
3357: where user_entity_name = c_user_entity_name;
3352: cursor user_entity_id
3353: ( c_user_entity_name ff_user_entities.user_entity_name%type )
3354: is
3355: select user_entity_id
3356: from ff_user_entities
3357: where user_entity_name = c_user_entity_name;
3358: --
3359: begin
3360: if g_debug then