[Home] [Help]
1: package body pay_balance_upload as
2: /* $Header: pybalupl.pkb 120.9.12020000.5 2012/12/04 10:22:17 divicker ship $ */
3: /*
4: Copyright (c) Oracle Corporation 1995 All rights reserved
5: PRODUCT
453: ,BL.source_text2
454: ,BL.run_type_id
455: ,BL.value
456: ,trunc(nvl(BL.upload_date, BH.upload_date)) upload_date
457: ,pay_balance_upload.count_contexts
458: (BL.balance_dimension_id, BL.dimension_name) no_of_contexts
459: ,pay_balance_upload.dim_expiry_date
460: (BH.business_group_id
461: ,trunc(nvl(BL.upload_date, BH.upload_date))
455: ,BL.value
456: ,trunc(nvl(BL.upload_date, BH.upload_date)) upload_date
457: ,pay_balance_upload.count_contexts
458: (BL.balance_dimension_id, BL.dimension_name) no_of_contexts
459: ,pay_balance_upload.dim_expiry_date
460: (BH.business_group_id
461: ,trunc(nvl(BL.upload_date, BH.upload_date))
462: ,BL.dimension_name
463: ,BL.assignment_id
473: order by BL.assignment_id
474: ,decode(BL.batch_line_status,'T',1 ,'E',2 ,'V',3)
475: ,BL.balance_type_id
476: ,trunc(nvl(BL.upload_date, BH.upload_date))
477: ,pay_balance_upload.dim_expiry_date
478: (BH.business_group_id
479: ,trunc(nvl(BL.upload_date,BH.upload_date))
480: ,BL.dimension_name
481: ,BL.assignment_id
482: ,BL.tax_unit_id
483: ,BL.jurisdiction_code
484: ,BL.original_entry_id
485: ,BL.batch_line_status) desc
486: ,pay_balance_upload.count_contexts(
487: BL.balance_dimension_id, BL.dimension_name) desc
488: ,BL.batch_line_id;
489:
490: --
559: from pay_balance_batch_lines
560: where batch_id = p_batch_id;
561: --
562: begin
563: hr_utility.trace('Entering pay_balance_upload.remove_messages');
564: --
565: -- Remove the messages
566: --
567: for pmlrec in pml(p_batch_id) loop
569: where source_type = 'L'
570: and source_id = pmlrec.batch_line_id;
571: end loop;
572: --
573: hr_utility.set_location('pay_balance_upload.remove_messages',10);
574: delete from pay_message_lines
575: where source_type = 'H'
576: and source_id = p_batch_id;
577: --
574: delete from pay_message_lines
575: where source_type = 'H'
576: and source_id = p_batch_id;
577: --
578: hr_utility.trace('Exiting pay_balance_upload.remove_messages');
579: end remove_messages;
580: --
581: -----------------------------------------------------------------------------
582: -- NAME
632: l_run_type_name varchar2(80);
633: l_count NUMBER;
634: l_found BOOLEAN;
635: begin
636: hr_utility.trace('Entering pay_balance_upload.get_run_type_id');
637: --
638: -- Search for the defined balance in the Cache.
639: --
640: l_found := FALSE;
638: -- Search for the defined balance in the Cache.
639: --
640: l_found := FALSE;
641: if (p_run_type_id is not null) then
642: hr_utility.set_location('pay_balance_upload.get_run_type_id',10);
643: l_count := 1;
644: while (l_count < g_runtyp_tbl_nxt and l_found = FALSE) loop
645: if (p_run_type_id = g_runtyp_tbl_id(l_count)) then
646: p_run_type_id := g_runtyp_tbl_id(l_count);
650: l_count := l_count + 1;
651: end loop;
652: else
653: if (p_run_type_name is not null) then
654: hr_utility.set_location('pay_balance_upload.get_run_type_id',20);
655: l_run_type_name := upper(p_run_type_name);
656: l_count := 1;
657: while (l_count < g_runtyp_tbl_nxt and l_found = FALSE) loop
658: if (l_run_type_name = g_runtyp_tbl_name(l_count)) then
663: l_count := l_count + 1;
664: end loop;
665: end if;
666: end if;
667: hr_utility.set_location('pay_balance_upload.get_run_type_id',30);
668: --
669: -- If the balance is not in the Cache get it from the database.
670: --
671: if (l_found = FALSE) then
668: --
669: -- If the balance is not in the Cache get it from the database.
670: --
671: if (l_found = FALSE) then
672: hr_utility.set_location('pay_balance_upload.get_run_type_id',40);
673: --
674: open csr_run_type(p_business_group,
675: p_run_type_name,
676: p_run_type_id,
694: g_runtyp_tbl_id(g_runtyp_tbl_nxt) := l_run_type_rec.run_type_id;
695: g_runtyp_tbl_nxt := g_runtyp_tbl_nxt + 1;
696: end if;
697: --
698: hr_utility.trace('Exiting pay_balance_upload.get_run_type_id');
699: exception
700: when no_data_found then
701: hr_utility.set_location('pay_balance_upload.get_run_type_id',50);
702: -- close csr_tax_unit;
697: --
698: hr_utility.trace('Exiting pay_balance_upload.get_run_type_id');
699: exception
700: when no_data_found then
701: hr_utility.set_location('pay_balance_upload.get_run_type_id',50);
702: -- close csr_tax_unit;
703: hr_utility.trace('Exiting pay_balance_upload.get_run_type_id');
704: raise;
705: end;
699: exception
700: when no_data_found then
701: hr_utility.set_location('pay_balance_upload.get_run_type_id',50);
702: -- close csr_tax_unit;
703: hr_utility.trace('Exiting pay_balance_upload.get_run_type_id');
704: raise;
705: end;
706: --
707: -----------------------------------------------------------------------------
764: l_gre_name varchar2(80);
765: l_count NUMBER;
766: l_found BOOLEAN;
767: begin
768: hr_utility.trace('Entering pay_balance_upload.get_tax_unit_id');
769: --
770: -- Search for the defined balance in the Cache.
771: --
772: l_found := FALSE;
770: -- Search for the defined balance in the Cache.
771: --
772: l_found := FALSE;
773: if (p_tax_unit_id is not null) then
774: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',10);
775: l_count := 1;
776: while (l_count < g_gre_tbl_nxt and l_found = FALSE) loop
777: if (p_tax_unit_id = g_gre_tbl_id(l_count)) then
778: p_tax_unit_id := g_gre_tbl_id(l_count);
782: l_count := l_count + 1;
783: end loop;
784: else
785: if (p_gre_name is not null) then
786: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',20);
787: l_gre_name := upper(p_gre_name);
788: l_count := 1;
789: while (l_count < g_gre_tbl_nxt and l_found = FALSE) loop
790: if (l_gre_name = g_gre_tbl_name(l_count)) then
795: l_count := l_count + 1;
796: end loop;
797: end if;
798: end if;
799: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',30);
800: --
801: -- If the balance is not in the Cache get it from the database.
802: --
803: if (l_found = FALSE) then
800: --
801: -- If the balance is not in the Cache get it from the database.
802: --
803: if (l_found = FALSE) then
804: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',40);
805: begin
806: --
807: select plr.rule_mode
808: into g_leg_rule
858: g_gre_tbl_id(g_gre_tbl_nxt) := l_gre_rec.tax_unit_id;
859: g_gre_tbl_nxt := g_gre_tbl_nxt + 1;
860: end if;
861: --
862: hr_utility.trace('Exiting pay_balance_upload.get_tax_unit_id');
863: exception
864: when no_data_found then
865: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',50);
866: -- close csr_tax_unit;
861: --
862: hr_utility.trace('Exiting pay_balance_upload.get_tax_unit_id');
863: exception
864: when no_data_found then
865: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',50);
866: -- close csr_tax_unit;
867: hr_utility.trace('Exiting pay_balance_upload.get_tax_unit_id');
868: raise;
869: end;
863: exception
864: when no_data_found then
865: hr_utility.set_location('pay_balance_upload.get_tax_unit_id',50);
866: -- close csr_tax_unit;
867: hr_utility.trace('Exiting pay_balance_upload.get_tax_unit_id');
868: raise;
869: end;
870: -----------------------------------------------------------------------------
871: -- NAME
898: l_message_text varchar2(500);
899: --
900: begin
901: --
902: hr_utility.trace('Entering pay_balance_upload.write_message_line');
903: --
904: -- Set global error indicator.
905: --
906: status_indicator := SRS_ERROR;
941: ,decode(p_meesage_level, HEADER, p_batch_id, LINE, p_batch_line_id)
942: ,decode(p_meesage_level, HEADER, 'H' , LINE, 'L')
943: ,substr(l_message_text, 1, 240));
944: --
945: hr_utility.trace('Exiting pay_balance_upload.write_message_line');
946: --
947: end write_message_line;
948: --
949: -----------------------------------------------------------------------------
999: statem varchar2(512);
1000: --
1001: begin
1002: --
1003: hr_utility.trace('Entering pay_balance_upload.dim_expiry_date');
1004: --
1005: if g_legislation_code is null then
1006: --
1007: -- Get the legislation code for the business group.
1017: --
1018: -- line is not valid and hence no point working
1019: -- out expiry date as it is not used anyway
1020: --
1021: hr_utility.trace('pay_balance_upload.dim_expiry_date invalid line');
1022: --
1023: l_expiry_date := p_upload_date;
1024: --
1025: --
1039: elsif l_legislation_code = 'GB' then
1040: --
1041: -- GB dimensions.
1042: --
1043: hr_utility.trace('pay_balance_upload.dim_expiry_date UK dimensions');
1044: --
1045: statem := 'BEGIN
1046: :l_expiry_date := pay_uk_bal_upload.expiry_date
1047: (:p_upload_date
1060: elsif (l_legislation_code = 'US') OR (l_legislation_code = 'BF') then
1061: --
1062: -- US + BF dimensions.
1063: --
1064: hr_utility.trace('pay_balance_upload.dim_expiry_date US or BF dimensions');
1065: --
1066: statem := 'BEGIN
1067: :l_expiry_date := pay_'||lower(l_legislation_code)||'_bal_upload.expiry_date
1068: (:p_upload_date
1102: ;
1103: --
1104: end if;
1105: --
1106: hr_utility.trace('Exiting pay_balance_upload.dim_expiry_date');
1107: --
1108: -- Return the expiry date for the dimension.
1109: --
1110: return (l_expiry_date);
1115: -- Ensures not to raise any error since it causes
1116: -- csr_batch_line_transfer to fail, and therefore it
1117: -- cannot be trapped in transfer_assignment.
1118: --
1119: hr_utility.trace('Error in pay_balance_upload.dim_expiry_date');
1120: hr_utility.trace(sqlerrm);
1121: return null;
1122:
1123: end dim_expiry_date;
1309: l_batch_id number;
1310: --
1311: begin
1312: --
1313: hr_utility.trace('Entering pay_balance_upload.lock_batch_header');
1314: --
1315: -- Lock the specified batch header.
1316: --
1317: begin
1325: end if;
1326: raise;
1327: end;
1328: --
1329: hr_utility.trace('Exiting pay_balance_upload.lock_batch_header');
1330: --
1331: end lock_batch_header;
1332: --
1333: -----------------------------------------------------------------------------
1415: = nvl(BA.source_text2, '~nvl~')
1416: ;
1417: begin
1418: --
1419: hr_utility.trace('Entering pay_balance_upload.get_current_value');
1420: --
1421: if p_glbl_data_rec.include_adj_rule = 'N' then
1422: --
1423: -- Generic calculation without include_adjustment.
1568: -- running total. Also keep track of the earliest expiry date of the
1569: -- balance adjustments.
1570: --
1571: if l_include then
1572: hr_utility.set_location('pay_balance_upload.get_current_value',10);
1573: l_current_value :=
1574: l_current_value + l_bal_adjustment_rec.adjustment_amount;
1575: l_min_expiry_date :=
1576: least(l_min_expiry_date, l_bal_adjustment_rec.expiry_date);
1587: p_current_value := l_current_value;
1588: p_min_expiry_date := l_min_expiry_date;
1589: --
1590: hr_utility.trace('Current Value = '|| l_current_value);
1591: hr_utility.trace('Exiting pay_balance_upload.get_current_value');
1592: --
1593: end get_current_value;
1594: --
1595: -----------------------------------------------------------------------------
1650: --
1651: l_tax_unit_id number;
1652: begin
1653: --
1654: hr_utility.trace('Entering pay_balance_upload.calculate_adjustment');
1655: --
1656: -- Retrieve the current value for the initial balance as set by previous
1657: -- balance adjustments calculated for the assignment.
1658: --
1792: ,p_batch_line_rec.source_text2
1793: ,p_batch_line_rec.run_type_id
1794: ,p_batch_line_rec.original_entry_id);
1795: --
1796: hr_utility.trace('Exiting pay_balance_upload.calculate_adjustment');
1797: --
1798: end calculate_adjustment;
1799: --
1800: -----------------------------------------------------------------------------
2410: -- WCB balances, bug 11657441
2411:
2412: begin
2413: --
2414: hr_utility.trace('Entering pay_balance_upload.apply_adjustments');
2415: --
2416: open csr_bal_adj;
2417: --
2418: -- Get the first balance adjustment.
2420: fetch csr_bal_adj into l_bal_adj_rec;
2421: --
2422: -- At least one balance adjustment exists.
2423: --
2424: hr_utility.set_location('pay_balance_upload.apply_adjustments', 10);
2425: if csr_bal_adj%found then
2426: --
2427: -- Keep track of balance adjustment information.
2428: --
2458: --
2459: p_num_lines := 1;
2460: p_batch_line_list(p_num_lines) := l_bal_adj_rec.batch_line_id;
2461: --
2462: hr_utility.set_location('pay_balance_upload.apply_adjustments', 15);
2463: -- Add to the list of entry values to be used with the next balance
2464: -- adjustment.
2465: --
2466:
2484: );
2485: --
2486: -- Loop for all the balance adjustments in the list.
2487: --
2488: hr_utility.set_location('pay_balance_upload.apply_adjustments', 20);
2489: loop
2490: hr_utility.set_location('pay_balance_upload.apply_adjustments', 30);
2491: --
2492: -- Get the next balance adjustment.
2486: -- Loop for all the balance adjustments in the list.
2487: --
2488: hr_utility.set_location('pay_balance_upload.apply_adjustments', 20);
2489: loop
2490: hr_utility.set_location('pay_balance_upload.apply_adjustments', 30);
2491: --
2492: -- Get the next balance adjustment.
2493: --
2494: fetch csr_bal_adj into l_bal_adj_rec;
2526: and l_bal_adj_rec.balance_type_id <> l_bal_type_id
2527: )
2528: or csr_bal_adj%notfound then
2529: --
2530: hr_utility.set_location('pay_balance_upload.apply_adjustments', 40);
2531:
2532: l_payroll_action_id := null;
2533: --
2534: -- Firstly see if the last payroll action is available.
2715: l_entry_value_tbl
2716: );
2717: --
2718: end loop;
2719: hr_utility.set_location('pay_balance_upload.apply_adjustments', 50);
2720: --
2721: end if;
2722: --
2723: close csr_bal_adj;
2721: end if;
2722: --
2723: close csr_bal_adj;
2724: --
2725: hr_utility.trace('Exiting pay_balance_upload.apply_adjustments');
2726: --
2727: -- Trap any exceptions, put the error message into the message
2728: -- variable and raise an internal exception to indicate that there
2729: -- has been a failure. Close any open cursors.
2765: l_idx number;
2766: --
2767: begin
2768: --
2769: hr_utility.trace('Entering pay_balance_upload.cache_balances');
2770: --
2771: -- Clear the balance cache.
2772: --
2773: g_balances.delete;
2791: end if;
2792: --
2793: end loop;
2794: --
2795: hr_utility.trace('Exiting pay_balance_upload.cache_balances');
2796: --
2797: end cache_balances;
2798: --
2799: --
2846: l_chunk_size number;
2847: --
2848: begin
2849: --
2850: hr_utility.trace('Entering pay_balance_upload.lock_batch');
2851: --
2852: -- Lock the batch.
2853: --
2854: open csr_lock_batch(p_batch_id);
2888: p_glbl_data_rec.batch_id := l_batch_rec.batch_id;
2889: p_glbl_data_rec.upload_date := l_batch_rec.upload_date;
2890: p_glbl_data_rec.chunk_size := l_chunk_size;
2891: --
2892: hr_utility.trace('Exiting pay_balance_upload.lock_batch');
2893: --
2894: end lock_batch;
2895: --
2896: -----------------------------------------------------------------------------
2939: l_one_status boolean := FALSE;
2940: --
2941: begin
2942: --
2943: hr_utility.trace('Entering pay_balance_upload.set_batch_status');
2944: --
2945: open csr_status(p_glbl_data_rec.batch_id);
2946: --
2947: -- Loop for all the differnent statuses for the batch lines within a natch.
2998: where BH.batch_id = p_glbl_data_rec.batch_id;
2999: end if;
3000: close csr_status;
3001: --
3002: hr_utility.trace('Exiting pay_balance_upload.set_batch_status');
3003: --
3004: end set_batch_status;
3005: --
3006: -----------------------------------------------------------------------------
3115: l_ctx_idx number;
3116: --
3117: begin
3118: --
3119: hr_utility.trace('Entering pay_balance_upload.validate_batch_header');
3120: --
3121: -- Retrieve the batch header.
3122: --
3123: open csr_batch_header(p_glbl_data_rec.batch_id);
3131: end if;
3132: --
3133: -- See if the business group exists.
3134: --
3135: hr_utility.set_location('pay_balance_upload.validate_batch_header',10);
3136: open csr_business_group(l_batch_header_rec.business_group_id
3137: ,l_batch_header_rec.business_group_name
3138: ,p_glbl_data_rec.upload_date);
3139: fetch csr_business_group into l_bg_rec;
3148: close csr_business_group;
3149: --
3150: -- See if the payroll exists.
3151: --
3152: hr_utility.set_location('pay_balance_upload.validate_batch_header',20);
3153: open csr_payroll(l_batch_header_rec.business_group_id
3154: ,l_batch_header_rec.payroll_id
3155: ,l_batch_header_rec.payroll_name
3156: ,p_glbl_data_rec.upload_date);
3168: -- batch header being validated.
3169: --
3170: -- Batch Type is not valid.
3171: --
3172: hr_utility.set_location('pay_balance_upload.validate_batch_header',25);
3173: if l_batchtyp_invld then
3174: write_message_line
3175: (p_meesage_level => HEADER
3176: ,p_batch_id => l_batch_header_rec.batch_id
3180: end if;
3181: --
3182: -- Business group is not valid.
3183: --
3184: hr_utility.set_location('pay_balance_upload.validate_batch_header',30);
3185: if l_bg_invld then
3186: write_message_line
3187: (p_meesage_level => HEADER
3188: ,p_batch_id => l_batch_header_rec.batch_id
3192: end if;
3193: --
3194: -- Payroll is not valid.
3195: --
3196: hr_utility.set_location('pay_balance_upload.validate_batch_header',40);
3197: if l_pyrl_invld then
3198: write_message_line
3199: (p_meesage_level => HEADER
3200: ,p_batch_id => l_batch_header_rec.batch_id
3207: --
3208: if l_batchtyp_invld or
3209: l_bg_invld or
3210: l_pyrl_invld then
3211: hr_utility.set_location('pay_balance_upload.validate_batch_header',50);
3212: l_batch_header_rec.batch_status := 'E'; -- Error
3213: --
3214: -- All tests have succeeded so mark the batch header as valid.
3215: --
3213: --
3214: -- All tests have succeeded so mark the batch header as valid.
3215: --
3216: else
3217: hr_utility.set_location('pay_balance_upload.validate_batch_header',60);
3218: l_batch_header_rec.batch_status := 'V'; -- Valid
3219: end if;
3220: --
3221: -- Update the batch header with information retrieved during validation
3290: end if;
3291: --
3292: end if;
3293: --
3294: hr_utility.trace('Exiting pay_balance_upload.validate_batch_header');
3295: --
3296: end validate_batch_header;
3297: --
3298: -----------------------------------------------------------------------------
3341: l_dim_rec t_dimension_validation_rec:= p_dim_vald_rec;
3342: --
3343: begin
3344: --
3345: hr_utility.trace('Entering pay_balance_upload.which_contexts');
3346: --
3347: -- Indicator variables showing which contexts a balance dimension has.
3348: --
3349: l_dim_rec.jc_cntxt := FALSE;
3394: -- Set the indicator flags.
3395: --
3396: p_dim_vald_rec := l_dim_rec;
3397: --
3398: hr_utility.trace('Exiting pay_balance_upload.which_contexts');
3399: --
3400: end which_contexts;
3401: --
3402: -----------------------------------------------------------------------------
3464: l_status varchar2(30);
3465: ctx_id number;
3466: begin
3467: --
3468: hr_utility.trace('Entering pay_balance_upload.ins_latest_balance');
3469: open csr_get_def_bal (p_bal_type_id,
3470: p_bal_dimension_id);
3471: fetch csr_get_def_bal into l_defined_bal;
3472: close csr_get_def_bal;
3470: p_bal_dimension_id);
3471: fetch csr_get_def_bal into l_defined_bal;
3472: close csr_get_def_bal;
3473: --
3474: hr_utility.set_location('pay_balance_upload.ins_latest_balance',10);
3475: select pay_latest_balances_s.nextval
3476: into l_lat_bal_id
3477: from sys.dual;
3478: --
3548: -9999);
3549: --
3550: if p_tax_unit_id is not null then
3551: --
3552: hr_utility.set_location('pay_balance_upload.ins_latest_balance',20);
3553: open csr_get_context_id('TAX_UNIT_ID');
3554: fetch csr_get_context_id into ctx_id;
3555: close csr_get_context_id;
3556: --
3564: end if;
3565: --
3566: if p_jurisdiction_code is not null then
3567: --
3568: hr_utility.set_location('pay_balance_upload.ins_latest_balance',30);
3569: open csr_get_context_id('JURISDICTION_CODE');
3570: fetch csr_get_context_id into ctx_id;
3571: close csr_get_context_id;
3572: --
3579: p_jurisdiction_code);
3580: end if;
3581: if p_oee_id is not null then
3582: --
3583: hr_utility.set_location('pay_balance_upload.ins_latest_balance',40);
3584: open csr_get_context_id('ORIGINAL_ENTRY_ID');
3585: fetch csr_get_context_id into ctx_id;
3586: close csr_get_context_id;
3587: --
3596: --
3597: --
3598: if p_source_id is not null then
3599: --
3600: hr_utility.set_location('pay_balance_upload.ins_latest_balance',50);
3601: open csr_get_context_id('SOURCE_ID');
3602: fetch csr_get_context_id into ctx_id;
3603: close csr_get_context_id;
3604: --
3612: end if;
3613: --
3614: if p_source_text is not null then
3615: --
3616: hr_utility.set_location('pay_balance_upload.ins_latest_balance',50);
3617: open csr_get_context_id('SOURCE_TEXT');
3618: fetch csr_get_context_id into ctx_id;
3619: close csr_get_context_id;
3620: --
3628: end if;
3629: --
3630: if p_source_number is not null then
3631: --
3632: hr_utility.set_location('pay_balance_upload.ins_latest_balance',60);
3633: open csr_get_context_id('SOURCE_NUMBER');
3634: fetch csr_get_context_id into ctx_id;
3635: close csr_get_context_id;
3636: --
3644: end if;
3645: --
3646: if p_source_text2 is not null then
3647: --
3648: hr_utility.set_location('pay_balance_upload.ins_latest_balance',70);
3649: open csr_get_context_id('SOURCE_TEXT2');
3650: fetch csr_get_context_id into ctx_id;
3651: close csr_get_context_id;
3652: --
3659: p_source_text2);
3660: end if;
3661: --
3662: end if;
3663: hr_utility.trace('Exiting pay_balance_upload.ins_latest_balance');
3664: end ins_latest_balance;
3665: --
3666: -----------------------------------------------------------------------------
3667: -- NAME
3731: l_max_act_seq number;
3732: --
3733: begin
3734: --
3735: hr_utility.trace('Entering pay_balance_upload.load_latest_asg_balances');
3736: --
3737: open csr_latest_asg_balances(p_glbl_data_rec.batch_id
3738: ,p_glbl_data_rec.assignment_id);
3739: --
3751: p_batch_line_list(p_num_lines) := l_lat_asg_balance.batch_line_id;
3752: hr_utility.trace(p_num_lines||' '||p_batch_line_list(p_num_lines));
3753: --
3754: if (l_lat_asg_balance.value <> 0) then
3755: hr_utility.set_location('pay_balance_upload.load_latest_asg_balances',
3756: 10);
3757: select /*+ ORDERED
3758: USE_NL(pbl ppa pbf paa rr rrv)
3759: INDEX(pbl PAY_BALANCE_BATCH_LINES_N51)
3811: l_lat_asg_balance.original_entry_id), -1) =
3812: nvl(nvl(l_lat_asg_balance.original_entry_id,
3813: pbl.original_entry_id), -1);
3814: else
3815: hr_utility.set_location('pay_balance_upload.load_latest_asg_balances',
3816: 20);
3817: --
3818: open csr_get_asg_act (l_lat_asg_balance.payroll_action_id,
3819: p_glbl_data_rec.assignment_id);
3827: close csr_get_asg_act;
3828: --
3829: end if;
3830: --
3831: hr_utility.set_location('pay_balance_upload.load_latest_asg_balances', 30);
3832: ins_latest_balance(l_lat_asg_balance.balance_type_id,
3833: l_lat_asg_balance.balance_dimension_id,
3834: l_lat_asg_balance.value,
3835: p_glbl_data_rec.assignment_id,
3848: close csr_latest_asg_balances;
3849: --
3850: p_num_lines := 0;
3851: --
3852: hr_utility.trace('Exiting pay_balance_upload.load_latest_asg_balances');
3853: --
3854: end load_latest_asg_balances;
3855: --
3856: -----------------------------------------------------------------------------
4012: l_context ff_contexts.context_name%type;
4013: --
4014: begin
4015: --
4016: hr_utility.trace('Entering pay_balance_upload.validate_dimension');
4017: --
4018: -- Search through list of dimensions that have already been validated NB
4019: -- the list of dimensions is held in a PLSQL table.
4020: --
4453: l_oee_cntxt_invld then
4454: p_batch_line_rec.batch_line_status := 'E'; -- Error
4455: end if;
4456: --
4457: hr_utility.trace('Exiting pay_balance_upload.validate_dimension');
4458: --
4459: end validate_dimension;
4460: --
4461: -----------------------------------------------------------------------------
4618: l_balance_found boolean := FALSE;
4619: --
4620: begin
4621: --
4622: hr_utility.trace('Entering pay_balance_upload.validate_balance');
4623: --
4624: -- Search through list of balances that have already been validated NB. the
4625: -- list of balances is held in a PLSQL table.
4626: --
4790: l_bal_invl_link then
4791: p_batch_line_rec.batch_line_status := 'E'; -- Error
4792: end if;
4793: --
4794: hr_utility.trace('Exiting pay_balance_upload.validate_balance');
4795: --
4796: end validate_balance;
4797: --
4798: -----------------------------------------------------------------------------
4899: l_pay_act_id number;
4900: --
4901: begin
4902: --
4903: hr_utility.trace('Entering pay_balance_upload.validate_assignment');
4904: --
4905: -- Assignment has already been validated, so there is no need to do the
4906: -- validation again NB. the assignment_id overrides the assignment_number.
4907: --
5054: p_asg_processed then
5055: p_batch_line_rec.batch_line_status := 'E'; -- Error
5056: end if;
5057: --
5058: hr_utility.trace('Exiting pay_balance_upload.validate_assignment');
5059: --
5060: end validate_assignment;
5061: --
5062: -----------------------------------------------------------------------------
5104: l_bal_vald_rec t_balance_validation_rec;
5105: --
5106: begin
5107: --
5108: hr_utility.trace('Entering pay_balance_upload.validate_batch_lines');
5109: --
5110: -- Clear the validation cache.
5111: --
5112: g_bal_vald.delete;
5195: end loop;
5196: --
5197: close csr_batch_line_validate;
5198: --
5199: hr_utility.trace('Exiting pay_balance_upload.validate_batch_lines');
5200: --
5201: end validate_batch_lines;
5202: --
5203: -----------------------------------------------------------------------------
5228: l_validation_supp varchar2(30);
5229: --
5230: begin
5231: --
5232: hr_utility.trace('Entering pay_balance_upload.validate_batch');
5233: --
5234: -- Ensure the batch header is valid.
5235: --
5236: validate_batch_header(p_glbl_data_rec);
5300: end if;
5301: end if;
5302: end if;
5303: --
5304: hr_utility.trace('Exiting pay_balance_upload.validate_batch');
5305: --
5306: end validate_batch;
5307: --
5308: -----------------------------------------------------------------------------
5369: nvl(substr(TBA2.jurisdiction_code,1,BT.jurisdiction_level), -1);
5370: --
5371: begin
5372: --
5373: hr_utility.trace('Entering pay_balance_upload.transfer_assignment');
5374: --
5375: open csr_batch_line_transfer(p_glbl_data_rec.batch_id
5376: ,p_glbl_data_rec.assignment_id);
5377: --
5470: --
5471: --
5472: delete from pay_temp_balance_adjustments;
5473: --
5474: hr_utility.trace('Exiting pay_balance_upload.transfer_assignment');
5475: --
5476: -- The transfer has failed.
5477: --
5478: exception
5608: l_asg_count number := 0;
5609: --
5610: begin
5611: --
5612: hr_utility.trace('Entering pay_balance_upload.transfer_batch');
5613: --
5614: -- Cache the initial balance feed information for each balance found in
5615: -- the batch.
5616: --
5665: lock_batch_header(p_glbl_data_rec.batch_id);
5666: end if;
5667: end if;
5668: --
5669: hr_utility.trace('Exiting pay_balance_upload.transfer_batch');
5670: --
5671: end transfer_batch;
5672: --
5673: -----------------------------------------------------------------------------
5699: l_asg_count number;
5700: --
5701: begin
5702: --
5703: hr_utility.trace('Entering pay_balance_upload.undo_transfer_batch');
5704: --
5705: l_asg_count := 0;
5706: --
5707: open csr_batch_line_undo_transfer(p_glbl_data_rec.batch_id);
5781: commit;
5782: end if;
5783: end if;
5784: --
5785: hr_utility.trace('Exiting pay_balance_upload.undo_transfer_batch');
5786: --
5787: end undo_transfer_batch;
5788: --
5789: -----------------------------------------------------------------------------
5810: and BL.batch_line_status = 'E';
5811: --
5812: begin
5813: --
5814: hr_utility.trace('Entering pay_balance_upload.purge_batch');
5815: --
5816: -- Delete batch line messages.
5817: --
5818: for errline in csr_get_err_lines(p_glbl_data_rec.batch_id) loop
5838: --
5839: delete from pay_balance_batch_headers BBH
5840: where BBH.batch_id = p_glbl_data_rec.batch_id;
5841: --
5842: hr_utility.trace('Exiting pay_balance_upload.purge_batch');
5843: --
5844: end purge_batch;
5845: --
5846: -----------------------------------------------------------------------------
5951: pay_balance_batch_headers pbh
5952: where pbh.batch_id = p_batch_id
5953: and pbh.business_group_id = pbg.business_group_id;
5954: begin
5955: hr_utility.trace('Entering pay_balance_upload.load_latest_balances');
5956: --
5957: -- Freeze the batch while processing it and initialise the global data
5958: -- structure.
5959: --
6000: -- then commit them.
6001: --
6002: commit;
6003: --
6004: hr_utility.trace('Exiting pay_balance_upload.load_latest_balances');
6005: end load_latest_balances;
6006: -----------------------------------------------------------------------------
6007: -- NAME
6008: -- post_transfer_batch
6020: procedure post_transfer_batch
6021: (p_glbl_data_rec in glbl_data_rec_type
6022: )
6023: is
6024: l_proc varchar2(80) := ' pay_balance_upload.post_transfer_batch';
6025:
6026: --
6027: -- Retrieves incomplete payroll actions.
6028: --
6080: procedure validate_transfer_batch
6081: (p_glbl_data_rec in glbl_data_rec_type
6082: )
6083: is
6084: l_proc varchar2(80) := ' pay_balance_upload.validate_transfer_batch';
6085: l_message varchar2(240);
6086: l_glbl_data_rec glbl_data_rec_type:= p_glbl_data_rec;
6087: begin
6088: --
6202: l_null_batch_info t_batch_info_rec;
6203: --
6204: begin
6205: --
6206: hr_utility.trace('Entering pay_balance_upload.process');
6207: status_indicator := SRS_SUCCESS; -- Success
6208: g_legislation_code := null;
6209: --
6210: -- Reset the batch info.
6222: if l_batch_status = 'L' then
6223: --
6224: -- Set the return code and message for SRS.
6225: --
6226: hr_utility.trace('pay_balance_upload.process: batch locked');
6227: status_indicator := SRS_ERROR; -- Error
6228: errbuf := 'Batch currently being processed by another process';
6229: else
6230: --
6287: retcode := SRS_ERROR; -- Error
6288: errbuf := 'Invalid mode';
6289: end if;
6290: --
6291: hr_utility.trace('Exiting pay_balance_upload.process');
6292: --
6293: end process;
6294: --
6295: begin
6294: --
6295: begin
6296: g_gre_tbl_nxt := 1;
6297: g_runtyp_tbl_nxt := 1;
6298: end pay_balance_upload;