[Home] [Help]
1: package body pay_balance_upload as
2: /* $Header: pybalupl.pkb 120.8.12010000.2 2008/10/01 06:12:44 ankagarw ship $ */
3: /*
4: Copyright (c) Oracle Corporation 1995 All rights reserved
5: PRODUCT
451: ,BL.source_text2
452: ,BL.run_type_id
453: ,BL.value
454: ,trunc(nvl(BL.upload_date, BH.upload_date)) upload_date
455: ,pay_balance_upload.count_contexts
456: (BL.balance_dimension_id, BL.dimension_name) no_of_contexts
457: ,pay_balance_upload.dim_expiry_date
458: (BH.business_group_id
459: ,trunc(nvl(BL.upload_date, BH.upload_date))
453: ,BL.value
454: ,trunc(nvl(BL.upload_date, BH.upload_date)) upload_date
455: ,pay_balance_upload.count_contexts
456: (BL.balance_dimension_id, BL.dimension_name) no_of_contexts
457: ,pay_balance_upload.dim_expiry_date
458: (BH.business_group_id
459: ,trunc(nvl(BL.upload_date, BH.upload_date))
460: ,BL.dimension_name
461: ,BL.assignment_id
471: order by BL.assignment_id
472: ,decode(BL.batch_line_status,'T',1 ,'E',2 ,'V',3)
473: ,BL.balance_type_id
474: ,trunc(nvl(BL.upload_date, BH.upload_date))
475: ,pay_balance_upload.dim_expiry_date
476: (BH.business_group_id
477: ,trunc(nvl(BL.upload_date,BH.upload_date))
478: ,BL.dimension_name
479: ,BL.assignment_id
480: ,BL.tax_unit_id
481: ,BL.jurisdiction_code
482: ,BL.original_entry_id
483: ,BL.batch_line_status) desc
484: ,pay_balance_upload.count_contexts(
485: BL.balance_dimension_id, BL.dimension_name) desc
486: ,BL.batch_line_id;
487:
488: --
557: from pay_balance_batch_lines
558: where batch_id = p_batch_id;
559: --
560: begin
561: hr_utility.trace('Entering pay_balance_upload.remove_messages');
562: --
563: -- Remove the messages
564: --
565: for pmlrec in pml(p_batch_id) loop
567: where source_type = 'L'
568: and source_id = pmlrec.batch_line_id;
569: end loop;
570: --
571: hr_utility.set_location('pay_balance_upload.remove_messages',10);
572: delete from pay_message_lines
573: where source_type = 'H'
574: and source_id = p_batch_id;
575: --
572: delete from pay_message_lines
573: where source_type = 'H'
574: and source_id = p_batch_id;
575: --
576: hr_utility.trace('Exiting pay_balance_upload.remove_messages');
577: end remove_messages;
578: --
579: -----------------------------------------------------------------------------
580: -- NAME
630: l_run_type_name varchar2(80);
631: l_count NUMBER;
632: l_found BOOLEAN;
633: begin
634: hr_utility.trace('Entering pay_balance_upload.get_run_type_id');
635: --
636: -- Search for the defined balance in the Cache.
637: --
638: l_found := FALSE;
636: -- Search for the defined balance in the Cache.
637: --
638: l_found := FALSE;
639: if (p_run_type_id is not null) then
640: hr_utility.set_location('pay_balance_upload.get_run_type_id',10);
641: l_count := 1;
642: while (l_count < g_runtyp_tbl_nxt and l_found = FALSE) loop
643: if (p_run_type_id = g_runtyp_tbl_id(l_count)) then
644: p_run_type_id := g_runtyp_tbl_id(l_count);
648: l_count := l_count + 1;
649: end loop;
650: else
651: if (p_run_type_name is not null) then
652: hr_utility.set_location('pay_balance_upload.get_run_type_id',20);
653: l_run_type_name := upper(p_run_type_name);
654: l_count := 1;
655: while (l_count < g_runtyp_tbl_nxt and l_found = FALSE) loop
656: if (l_run_type_name = g_runtyp_tbl_name(l_count)) then
661: l_count := l_count + 1;
662: end loop;
663: end if;
664: end if;
665: hr_utility.set_location('pay_balance_upload.get_run_type_id',30);
666: --
667: -- If the balance is not in the Cache get it from the database.
668: --
669: if (l_found = FALSE) then
666: --
667: -- If the balance is not in the Cache get it from the database.
668: --
669: if (l_found = FALSE) then
670: hr_utility.set_location('pay_balance_upload.get_run_type_id',40);
671: --
672: open csr_run_type(p_business_group,
673: p_run_type_name,
674: p_run_type_id,
692: g_runtyp_tbl_id(g_runtyp_tbl_nxt) := l_run_type_rec.run_type_id;
693: g_runtyp_tbl_nxt := g_runtyp_tbl_nxt + 1;
694: end if;
695: --
696: hr_utility.trace('Exiting pay_balance_upload.get_run_type_id');
697: exception
698: when no_data_found then
699: hr_utility.set_location('pay_balance_upload.get_run_type_id',50);
700: -- close csr_tax_unit;
695: --
696: hr_utility.trace('Exiting pay_balance_upload.get_run_type_id');
697: exception
698: when no_data_found then
699: hr_utility.set_location('pay_balance_upload.get_run_type_id',50);
700: -- close csr_tax_unit;
701: hr_utility.trace('Exiting pay_balance_upload.get_run_type_id');
702: raise;
703: end;
697: exception
698: when no_data_found then
699: hr_utility.set_location('pay_balance_upload.get_run_type_id',50);
700: -- close csr_tax_unit;
701: hr_utility.trace('Exiting pay_balance_upload.get_run_type_id');
702: raise;
703: end;
704: --
705: -----------------------------------------------------------------------------
762: l_gre_name varchar2(80);
763: l_count NUMBER;
764: l_found BOOLEAN;
765: begin
766: hr_utility.trace('Entering pay_balance_upload.get_tax_unit_id');
767: --
768: -- Search for the defined balance in the Cache.
769: --
770: l_found := FALSE;
768: -- Search for the defined balance in the Cache.
769: --
770: l_found := FALSE;
771: if (p_tax_unit_id is not null) then
772: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',10);
773: l_count := 1;
774: while (l_count < g_gre_tbl_nxt and l_found = FALSE) loop
775: if (p_tax_unit_id = g_gre_tbl_id(l_count)) then
776: p_tax_unit_id := g_gre_tbl_id(l_count);
780: l_count := l_count + 1;
781: end loop;
782: else
783: if (p_gre_name is not null) then
784: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',20);
785: l_gre_name := upper(p_gre_name);
786: l_count := 1;
787: while (l_count < g_gre_tbl_nxt and l_found = FALSE) loop
788: if (l_gre_name = g_gre_tbl_name(l_count)) then
793: l_count := l_count + 1;
794: end loop;
795: end if;
796: end if;
797: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',30);
798: --
799: -- If the balance is not in the Cache get it from the database.
800: --
801: if (l_found = FALSE) then
798: --
799: -- If the balance is not in the Cache get it from the database.
800: --
801: if (l_found = FALSE) then
802: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',40);
803: begin
804: --
805: select plr.rule_mode
806: into g_leg_rule
856: g_gre_tbl_id(g_gre_tbl_nxt) := l_gre_rec.tax_unit_id;
857: g_gre_tbl_nxt := g_gre_tbl_nxt + 1;
858: end if;
859: --
860: hr_utility.trace('Exiting pay_balance_upload.get_tax_unit_id');
861: exception
862: when no_data_found then
863: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',50);
864: -- close csr_tax_unit;
859: --
860: hr_utility.trace('Exiting pay_balance_upload.get_tax_unit_id');
861: exception
862: when no_data_found then
863: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',50);
864: -- close csr_tax_unit;
865: hr_utility.trace('Exiting pay_balance_upload.get_tax_unit_id');
866: raise;
867: end;
861: exception
862: when no_data_found then
863: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',50);
864: -- close csr_tax_unit;
865: hr_utility.trace('Exiting pay_balance_upload.get_tax_unit_id');
866: raise;
867: end;
868: -----------------------------------------------------------------------------
869: -- NAME
896: l_message_text varchar2(500);
897: --
898: begin
899: --
900: hr_utility.trace('Entering pay_balance_upload.write_message_line');
901: --
902: -- Set global error indicator.
903: --
904: status_indicator := SRS_ERROR;
939: ,decode(p_meesage_level, HEADER, p_batch_id, LINE, p_batch_line_id)
940: ,decode(p_meesage_level, HEADER, 'H' , LINE, 'L')
941: ,substr(l_message_text, 1, 240));
942: --
943: hr_utility.trace('Exiting pay_balance_upload.write_message_line');
944: --
945: end write_message_line;
946: --
947: -----------------------------------------------------------------------------
997: statem varchar2(512);
998: --
999: begin
1000: --
1001: hr_utility.trace('Entering pay_balance_upload.dim_expiry_date');
1002: --
1003: if g_legislation_code is null then
1004: --
1005: -- Get the legislation code for the business group.
1015: --
1016: -- line is not valid and hence no point working
1017: -- out expiry date as it is not used anyway
1018: --
1019: hr_utility.trace('pay_balance_upload.dim_expiry_date invalid line');
1020: --
1021: l_expiry_date := p_upload_date;
1022: --
1023: --
1037: elsif l_legislation_code = 'GB' then
1038: --
1039: -- GB dimensions.
1040: --
1041: hr_utility.trace('pay_balance_upload.dim_expiry_date UK dimensions');
1042: --
1043: statem := 'BEGIN
1044: :l_expiry_date := pay_uk_bal_upload.expiry_date
1045: (:p_upload_date
1058: elsif (l_legislation_code = 'US') OR (l_legislation_code = 'BF') then
1059: --
1060: -- US + BF dimensions.
1061: --
1062: hr_utility.trace('pay_balance_upload.dim_expiry_date US or BF dimensions');
1063: --
1064: statem := 'BEGIN
1065: :l_expiry_date := pay_'||lower(l_legislation_code)||'_bal_upload.expiry_date
1066: (:p_upload_date
1100: ;
1101: --
1102: end if;
1103: --
1104: hr_utility.trace('Exiting pay_balance_upload.dim_expiry_date');
1105: --
1106: -- Return the expiry date for the dimension.
1107: --
1108: return (l_expiry_date);
1113: -- Ensures not to raise any error since it causes
1114: -- csr_batch_line_transfer to fail, and therefore it
1115: -- cannot be trapped in transfer_assignment.
1116: --
1117: hr_utility.trace('Error in pay_balance_upload.dim_expiry_date');
1118: hr_utility.trace(sqlerrm);
1119: return null;
1120:
1121: end dim_expiry_date;
1307: l_batch_id number;
1308: --
1309: begin
1310: --
1311: hr_utility.trace('Entering pay_balance_upload.lock_batch_header');
1312: --
1313: -- Lock the specified batch header.
1314: --
1315: begin
1323: end if;
1324: raise;
1325: end;
1326: --
1327: hr_utility.trace('Exiting pay_balance_upload.lock_batch_header');
1328: --
1329: end lock_batch_header;
1330: --
1331: -----------------------------------------------------------------------------
1413: = nvl(BA.source_text2, '~nvl~')
1414: ;
1415: begin
1416: --
1417: hr_utility.trace('Entering pay_balance_upload.get_current_value');
1418: --
1419: if p_glbl_data_rec.include_adj_rule = 'N' then
1420: --
1421: -- Generic calculation without include_adjustment.
1566: -- running total. Also keep track of the earliest expiry date of the
1567: -- balance adjustments.
1568: --
1569: if l_include then
1570: hr_utility.set_location('pay_balance_upload.get_current_value',10);
1571: l_current_value :=
1572: l_current_value + l_bal_adjustment_rec.adjustment_amount;
1573: l_min_expiry_date :=
1574: least(l_min_expiry_date, l_bal_adjustment_rec.expiry_date);
1585: p_current_value := l_current_value;
1586: p_min_expiry_date := l_min_expiry_date;
1587: --
1588: hr_utility.trace('Current Value = '|| l_current_value);
1589: hr_utility.trace('Exiting pay_balance_upload.get_current_value');
1590: --
1591: end get_current_value;
1592: --
1593: -----------------------------------------------------------------------------
1648: --
1649: l_tax_unit_id number;
1650: begin
1651: --
1652: hr_utility.trace('Entering pay_balance_upload.calculate_adjustment');
1653: --
1654: -- Retrieve the current value for the initial balance as set by previous
1655: -- balance adjustments calculated for the assignment.
1656: --
1790: ,p_batch_line_rec.source_text2
1791: ,p_batch_line_rec.run_type_id
1792: ,p_batch_line_rec.original_entry_id);
1793: --
1794: hr_utility.trace('Exiting pay_balance_upload.calculate_adjustment');
1795: --
1796: end calculate_adjustment;
1797: --
1798: -----------------------------------------------------------------------------
2143: c_varchar2 constant varchar2(10) := '~nvl~';
2144: --
2145: begin
2146: --
2147: hr_utility.trace('Entering pay_balance_upload.apply_adjustments');
2148: --
2149: open csr_bal_adj;
2150: --
2151: -- Get the first balance adjustment.
2153: fetch csr_bal_adj into l_bal_adj_rec;
2154: --
2155: -- At least one balance adjustment exists.
2156: --
2157: hr_utility.set_location('pay_balance_upload.apply_adjustments', 10);
2158: if csr_bal_adj%found then
2159: --
2160: -- Keep track of balance adjustment information.
2161: --
2191: --
2192: p_num_lines := 1;
2193: p_batch_line_list(p_num_lines) := l_bal_adj_rec.batch_line_id;
2194: --
2195: hr_utility.set_location('pay_balance_upload.apply_adjustments', 15);
2196: -- Add to the list of entry values to be used with the next balance
2197: -- adjustment.
2198: --
2199: create_entry_values (
2211: );
2212: --
2213: -- Loop for all the balance adjustments in the list.
2214: --
2215: hr_utility.set_location('pay_balance_upload.apply_adjustments', 20);
2216: loop
2217: hr_utility.set_location('pay_balance_upload.apply_adjustments', 30);
2218: --
2219: -- Get the next balance adjustment.
2213: -- Loop for all the balance adjustments in the list.
2214: --
2215: hr_utility.set_location('pay_balance_upload.apply_adjustments', 20);
2216: loop
2217: hr_utility.set_location('pay_balance_upload.apply_adjustments', 30);
2218: --
2219: -- Get the next balance adjustment.
2220: --
2221: fetch csr_bal_adj into l_bal_adj_rec;
2253: and l_bal_adj_rec.balance_type_id <> l_bal_type_id
2254: )
2255: or csr_bal_adj%notfound then
2256: --
2257: hr_utility.set_location('pay_balance_upload.apply_adjustments', 40);
2258:
2259: l_payroll_action_id := null;
2260: --
2261: -- Firstly see if the last payroll action is available.
2423: l_entry_value_tbl
2424: );
2425: --
2426: end loop;
2427: hr_utility.set_location('pay_balance_upload.apply_adjustments', 50);
2428: --
2429: end if;
2430: --
2431: close csr_bal_adj;
2429: end if;
2430: --
2431: close csr_bal_adj;
2432: --
2433: hr_utility.trace('Exiting pay_balance_upload.apply_adjustments');
2434: --
2435: -- Trap any exceptions, put the error message into the message
2436: -- variable and raise an internal exception to indicate that there
2437: -- has been a failure. Close any open cursors.
2472: l_idx number;
2473: --
2474: begin
2475: --
2476: hr_utility.trace('Entering pay_balance_upload.cache_balances');
2477: --
2478: -- Clear the balance cache.
2479: --
2480: g_balances.delete;
2498: end if;
2499: --
2500: end loop;
2501: --
2502: hr_utility.trace('Exiting pay_balance_upload.cache_balances');
2503: --
2504: end cache_balances;
2505: --
2506: --
2553: l_chunk_size number;
2554: --
2555: begin
2556: --
2557: hr_utility.trace('Entering pay_balance_upload.lock_batch');
2558: --
2559: -- Lock the batch.
2560: --
2561: open csr_lock_batch(p_batch_id);
2595: p_glbl_data_rec.batch_id := l_batch_rec.batch_id;
2596: p_glbl_data_rec.upload_date := l_batch_rec.upload_date;
2597: p_glbl_data_rec.chunk_size := l_chunk_size;
2598: --
2599: hr_utility.trace('Exiting pay_balance_upload.lock_batch');
2600: --
2601: end lock_batch;
2602: --
2603: -----------------------------------------------------------------------------
2646: l_one_status boolean := FALSE;
2647: --
2648: begin
2649: --
2650: hr_utility.trace('Entering pay_balance_upload.set_batch_status');
2651: --
2652: open csr_status(p_glbl_data_rec.batch_id);
2653: --
2654: -- Loop for all the differnent statuses for the batch lines within a natch.
2705: where BH.batch_id = p_glbl_data_rec.batch_id;
2706: end if;
2707: close csr_status;
2708: --
2709: hr_utility.trace('Exiting pay_balance_upload.set_batch_status');
2710: --
2711: end set_batch_status;
2712: --
2713: -----------------------------------------------------------------------------
2822: l_ctx_idx number;
2823: --
2824: begin
2825: --
2826: hr_utility.trace('Entering pay_balance_upload.validate_batch_header');
2827: --
2828: -- Retrieve the batch header.
2829: --
2830: open csr_batch_header(p_glbl_data_rec.batch_id);
2838: end if;
2839: --
2840: -- See if the business group exists.
2841: --
2842: hr_utility.set_location('pay_balance_upload.validate_batch_header',10);
2843: open csr_business_group(l_batch_header_rec.business_group_id
2844: ,l_batch_header_rec.business_group_name
2845: ,p_glbl_data_rec.upload_date);
2846: fetch csr_business_group into l_bg_rec;
2855: close csr_business_group;
2856: --
2857: -- See if the payroll exists.
2858: --
2859: hr_utility.set_location('pay_balance_upload.validate_batch_header',20);
2860: open csr_payroll(l_batch_header_rec.business_group_id
2861: ,l_batch_header_rec.payroll_id
2862: ,l_batch_header_rec.payroll_name
2863: ,p_glbl_data_rec.upload_date);
2875: -- batch header being validated.
2876: --
2877: -- Batch Type is not valid.
2878: --
2879: hr_utility.set_location('pay_balance_upload.validate_batch_header',25);
2880: if l_batchtyp_invld then
2881: write_message_line
2882: (p_meesage_level => HEADER
2883: ,p_batch_id => l_batch_header_rec.batch_id
2887: end if;
2888: --
2889: -- Business group is not valid.
2890: --
2891: hr_utility.set_location('pay_balance_upload.validate_batch_header',30);
2892: if l_bg_invld then
2893: write_message_line
2894: (p_meesage_level => HEADER
2895: ,p_batch_id => l_batch_header_rec.batch_id
2899: end if;
2900: --
2901: -- Payroll is not valid.
2902: --
2903: hr_utility.set_location('pay_balance_upload.validate_batch_header',40);
2904: if l_pyrl_invld then
2905: write_message_line
2906: (p_meesage_level => HEADER
2907: ,p_batch_id => l_batch_header_rec.batch_id
2914: --
2915: if l_batchtyp_invld or
2916: l_bg_invld or
2917: l_pyrl_invld then
2918: hr_utility.set_location('pay_balance_upload.validate_batch_header',50);
2919: l_batch_header_rec.batch_status := 'E'; -- Error
2920: --
2921: -- All tests have succeeded so mark the batch header as valid.
2922: --
2920: --
2921: -- All tests have succeeded so mark the batch header as valid.
2922: --
2923: else
2924: hr_utility.set_location('pay_balance_upload.validate_batch_header',60);
2925: l_batch_header_rec.batch_status := 'V'; -- Valid
2926: end if;
2927: --
2928: -- Update the batch header with information retrieved during validation
2997: end if;
2998: --
2999: end if;
3000: --
3001: hr_utility.trace('Exiting pay_balance_upload.validate_batch_header');
3002: --
3003: end validate_batch_header;
3004: --
3005: -----------------------------------------------------------------------------
3048: l_dim_rec t_dimension_validation_rec:= p_dim_vald_rec;
3049: --
3050: begin
3051: --
3052: hr_utility.trace('Entering pay_balance_upload.which_contexts');
3053: --
3054: -- Indicator variables showing which contexts a balance dimension has.
3055: --
3056: l_dim_rec.jc_cntxt := FALSE;
3101: -- Set the indicator flags.
3102: --
3103: p_dim_vald_rec := l_dim_rec;
3104: --
3105: hr_utility.trace('Exiting pay_balance_upload.which_contexts');
3106: --
3107: end which_contexts;
3108: --
3109: -----------------------------------------------------------------------------
3171: l_status varchar2(30);
3172: ctx_id number;
3173: begin
3174: --
3175: hr_utility.trace('Entering pay_balance_upload.ins_latest_balance');
3176: open csr_get_def_bal (p_bal_type_id,
3177: p_bal_dimension_id);
3178: fetch csr_get_def_bal into l_defined_bal;
3179: close csr_get_def_bal;
3177: p_bal_dimension_id);
3178: fetch csr_get_def_bal into l_defined_bal;
3179: close csr_get_def_bal;
3180: --
3181: hr_utility.set_location('pay_balance_upload.ins_latest_balance',10);
3182: select pay_latest_balances_s.nextval
3183: into l_lat_bal_id
3184: from sys.dual;
3185: --
3255: -9999);
3256: --
3257: if p_tax_unit_id is not null then
3258: --
3259: hr_utility.set_location('pay_balance_upload.ins_latest_balance',20);
3260: open csr_get_context_id('TAX_UNIT_ID');
3261: fetch csr_get_context_id into ctx_id;
3262: close csr_get_context_id;
3263: --
3271: end if;
3272: --
3273: if p_jurisdiction_code is not null then
3274: --
3275: hr_utility.set_location('pay_balance_upload.ins_latest_balance',30);
3276: open csr_get_context_id('JURISDICTION_CODE');
3277: fetch csr_get_context_id into ctx_id;
3278: close csr_get_context_id;
3279: --
3286: p_jurisdiction_code);
3287: end if;
3288: if p_oee_id is not null then
3289: --
3290: hr_utility.set_location('pay_balance_upload.ins_latest_balance',40);
3291: open csr_get_context_id('ORIGINAL_ENTRY_ID');
3292: fetch csr_get_context_id into ctx_id;
3293: close csr_get_context_id;
3294: --
3303: --
3304: --
3305: if p_source_id is not null then
3306: --
3307: hr_utility.set_location('pay_balance_upload.ins_latest_balance',50);
3308: open csr_get_context_id('SOURCE_ID');
3309: fetch csr_get_context_id into ctx_id;
3310: close csr_get_context_id;
3311: --
3319: end if;
3320: --
3321: if p_source_text is not null then
3322: --
3323: hr_utility.set_location('pay_balance_upload.ins_latest_balance',50);
3324: open csr_get_context_id('SOURCE_TEXT');
3325: fetch csr_get_context_id into ctx_id;
3326: close csr_get_context_id;
3327: --
3335: end if;
3336: --
3337: if p_source_number is not null then
3338: --
3339: hr_utility.set_location('pay_balance_upload.ins_latest_balance',60);
3340: open csr_get_context_id('SOURCE_NUMBER');
3341: fetch csr_get_context_id into ctx_id;
3342: close csr_get_context_id;
3343: --
3351: end if;
3352: --
3353: if p_source_text2 is not null then
3354: --
3355: hr_utility.set_location('pay_balance_upload.ins_latest_balance',70);
3356: open csr_get_context_id('SOURCE_TEXT2');
3357: fetch csr_get_context_id into ctx_id;
3358: close csr_get_context_id;
3359: --
3366: p_source_text2);
3367: end if;
3368: --
3369: end if;
3370: hr_utility.trace('Exiting pay_balance_upload.ins_latest_balance');
3371: end ins_latest_balance;
3372: --
3373: -----------------------------------------------------------------------------
3374: -- NAME
3438: l_max_act_seq number;
3439: --
3440: begin
3441: --
3442: hr_utility.trace('Entering pay_balance_upload.load_latest_asg_balances');
3443: --
3444: open csr_latest_asg_balances(p_glbl_data_rec.batch_id
3445: ,p_glbl_data_rec.assignment_id);
3446: --
3458: p_batch_line_list(p_num_lines) := l_lat_asg_balance.batch_line_id;
3459: hr_utility.trace(p_num_lines||' '||p_batch_line_list(p_num_lines));
3460: --
3461: if (l_lat_asg_balance.value <> 0) then
3462: hr_utility.set_location('pay_balance_upload.load_latest_asg_balances',
3463: 10);
3464: select /*+ ORDERED
3465: USE_NL(pbl ppa pbf paa rr rrv)
3466: INDEX(pbl PAY_BALANCE_BATCH_LINES_N51)
3518: l_lat_asg_balance.original_entry_id), -1) =
3519: nvl(nvl(l_lat_asg_balance.original_entry_id,
3520: pbl.original_entry_id), -1);
3521: else
3522: hr_utility.set_location('pay_balance_upload.load_latest_asg_balances',
3523: 20);
3524: --
3525: open csr_get_asg_act (l_lat_asg_balance.payroll_action_id,
3526: p_glbl_data_rec.assignment_id);
3534: close csr_get_asg_act;
3535: --
3536: end if;
3537: --
3538: hr_utility.set_location('pay_balance_upload.load_latest_asg_balances', 30);
3539: ins_latest_balance(l_lat_asg_balance.balance_type_id,
3540: l_lat_asg_balance.balance_dimension_id,
3541: l_lat_asg_balance.value,
3542: p_glbl_data_rec.assignment_id,
3555: close csr_latest_asg_balances;
3556: --
3557: p_num_lines := 0;
3558: --
3559: hr_utility.trace('Exiting pay_balance_upload.load_latest_asg_balances');
3560: --
3561: end load_latest_asg_balances;
3562: --
3563: -----------------------------------------------------------------------------
3719: l_context ff_contexts.context_name%type;
3720: --
3721: begin
3722: --
3723: hr_utility.trace('Entering pay_balance_upload.validate_dimension');
3724: --
3725: -- Search through list of dimensions that have already been validated NB
3726: -- the list of dimensions is held in a PLSQL table.
3727: --
4160: l_oee_cntxt_invld then
4161: p_batch_line_rec.batch_line_status := 'E'; -- Error
4162: end if;
4163: --
4164: hr_utility.trace('Exiting pay_balance_upload.validate_dimension');
4165: --
4166: end validate_dimension;
4167: --
4168: -----------------------------------------------------------------------------
4325: l_balance_found boolean := FALSE;
4326: --
4327: begin
4328: --
4329: hr_utility.trace('Entering pay_balance_upload.validate_balance');
4330: --
4331: -- Search through list of balances that have already been validated NB. the
4332: -- list of balances is held in a PLSQL table.
4333: --
4497: l_bal_invl_link then
4498: p_batch_line_rec.batch_line_status := 'E'; -- Error
4499: end if;
4500: --
4501: hr_utility.trace('Exiting pay_balance_upload.validate_balance');
4502: --
4503: end validate_balance;
4504: --
4505: -----------------------------------------------------------------------------
4606: l_pay_act_id number;
4607: --
4608: begin
4609: --
4610: hr_utility.trace('Entering pay_balance_upload.validate_assignment');
4611: --
4612: -- Assignment has already been validated, so there is no need to do the
4613: -- validation again NB. the assignment_id overrides the assignment_number.
4614: --
4761: p_asg_processed then
4762: p_batch_line_rec.batch_line_status := 'E'; -- Error
4763: end if;
4764: --
4765: hr_utility.trace('Exiting pay_balance_upload.validate_assignment');
4766: --
4767: end validate_assignment;
4768: --
4769: -----------------------------------------------------------------------------
4811: l_bal_vald_rec t_balance_validation_rec;
4812: --
4813: begin
4814: --
4815: hr_utility.trace('Entering pay_balance_upload.validate_batch_lines');
4816: --
4817: -- Clear the validation cache.
4818: --
4819: g_bal_vald.delete;
4902: end loop;
4903: --
4904: close csr_batch_line_validate;
4905: --
4906: hr_utility.trace('Exiting pay_balance_upload.validate_batch_lines');
4907: --
4908: end validate_batch_lines;
4909: --
4910: -----------------------------------------------------------------------------
4935: l_validation_supp varchar2(30);
4936: --
4937: begin
4938: --
4939: hr_utility.trace('Entering pay_balance_upload.validate_batch');
4940: --
4941: -- Ensure the batch header is valid.
4942: --
4943: validate_batch_header(p_glbl_data_rec);
5007: end if;
5008: end if;
5009: end if;
5010: --
5011: hr_utility.trace('Exiting pay_balance_upload.validate_batch');
5012: --
5013: end validate_batch;
5014: --
5015: -----------------------------------------------------------------------------
5076: nvl(substr(TBA2.jurisdiction_code,1,BT.jurisdiction_level), -1);
5077: --
5078: begin
5079: --
5080: hr_utility.trace('Entering pay_balance_upload.transfer_assignment');
5081: --
5082: open csr_batch_line_transfer(p_glbl_data_rec.batch_id
5083: ,p_glbl_data_rec.assignment_id);
5084: --
5177: --
5178: --
5179: delete from pay_temp_balance_adjustments;
5180: --
5181: hr_utility.trace('Exiting pay_balance_upload.transfer_assignment');
5182: --
5183: -- The transfer has failed.
5184: --
5185: exception
5315: l_asg_count number := 0;
5316: --
5317: begin
5318: --
5319: hr_utility.trace('Entering pay_balance_upload.transfer_batch');
5320: --
5321: -- Cache the initial balance feed information for each balance found in
5322: -- the batch.
5323: --
5372: lock_batch_header(p_glbl_data_rec.batch_id);
5373: end if;
5374: end if;
5375: --
5376: hr_utility.trace('Exiting pay_balance_upload.transfer_batch');
5377: --
5378: end transfer_batch;
5379: --
5380: -----------------------------------------------------------------------------
5406: l_asg_count number;
5407: --
5408: begin
5409: --
5410: hr_utility.trace('Entering pay_balance_upload.undo_transfer_batch');
5411: --
5412: l_asg_count := 0;
5413: --
5414: open csr_batch_line_undo_transfer(p_glbl_data_rec.batch_id);
5488: commit;
5489: end if;
5490: end if;
5491: --
5492: hr_utility.trace('Exiting pay_balance_upload.undo_transfer_batch');
5493: --
5494: end undo_transfer_batch;
5495: --
5496: -----------------------------------------------------------------------------
5517: and BL.batch_line_status = 'E';
5518: --
5519: begin
5520: --
5521: hr_utility.trace('Entering pay_balance_upload.purge_batch');
5522: --
5523: -- Delete batch line messages.
5524: --
5525: for errline in csr_get_err_lines(p_glbl_data_rec.batch_id) loop
5545: --
5546: delete from pay_balance_batch_headers BBH
5547: where BBH.batch_id = p_glbl_data_rec.batch_id;
5548: --
5549: hr_utility.trace('Exiting pay_balance_upload.purge_batch');
5550: --
5551: end purge_batch;
5552: --
5553: -----------------------------------------------------------------------------
5658: pay_balance_batch_headers pbh
5659: where pbh.batch_id = p_batch_id
5660: and pbh.business_group_id = pbg.business_group_id;
5661: begin
5662: hr_utility.trace('Entering pay_balance_upload.load_latest_balances');
5663: --
5664: -- Freeze the batch while processing it and initialise the global data
5665: -- structure.
5666: --
5707: -- then commit them.
5708: --
5709: commit;
5710: --
5711: hr_utility.trace('Exiting pay_balance_upload.load_latest_balances');
5712: end load_latest_balances;
5713: -----------------------------------------------------------------------------
5714: -- NAME
5715: -- post_transfer_batch
5727: procedure post_transfer_batch
5728: (p_glbl_data_rec in glbl_data_rec_type
5729: )
5730: is
5731: l_proc varchar2(80) := ' pay_balance_upload.post_transfer_batch';
5732:
5733: --
5734: -- Retrieves incomplete payroll actions.
5735: --
5787: procedure validate_transfer_batch
5788: (p_glbl_data_rec in glbl_data_rec_type
5789: )
5790: is
5791: l_proc varchar2(80) := ' pay_balance_upload.validate_transfer_batch';
5792: l_message varchar2(240);
5793: l_glbl_data_rec glbl_data_rec_type:= p_glbl_data_rec;
5794: begin
5795: --
5909: l_null_batch_info t_batch_info_rec;
5910: --
5911: begin
5912: --
5913: hr_utility.trace('Entering pay_balance_upload.process');
5914: status_indicator := SRS_SUCCESS; -- Success
5915: g_legislation_code := null;
5916: --
5917: -- Reset the batch info.
5929: if l_batch_status = 'L' then
5930: --
5931: -- Set the return code and message for SRS.
5932: --
5933: hr_utility.trace('pay_balance_upload.process: batch locked');
5934: status_indicator := SRS_ERROR; -- Error
5935: errbuf := 'Batch currently being processed by another process';
5936: else
5937: --
5994: retcode := SRS_ERROR; -- Error
5995: errbuf := 'Invalid mode';
5996: end if;
5997: --
5998: hr_utility.trace('Exiting pay_balance_upload.process');
5999: --
6000: end process;
6001: --
6002: begin
6001: --
6002: begin
6003: g_gre_tbl_nxt := 1;
6004: g_runtyp_tbl_nxt := 1;
6005: end pay_balance_upload;