[Home] [Help]
184: tax unit id to balance adjustments,
185: also passing batch_line_id to
186: include_adjustment.
187: 115.16 A.Logue 13-JAN-2000 Ensure that error messages fetched from
188: hr_utility.get_message are of max length 240
189: to fit into pay_message_lines.
190: 115.14 A.Logue 07-OCT-1999 Pass batch_line_status to dim_expiry_date so that
191: it does not call the legislative expiry_date
192: procedure if the line is to be discarded (ie not 'V').
197: and hence can be called from the cursor (nb it
198: contains dynamic sql). Thus can remove the insert
199: sort implemented as part of 730491. This
200: should give an improvement of performance.
201: 115.12 A.Logue 18-MAY-1999 Change dbms_output to hr_utility.trace.
202: 115.11 A.Logue 14-MAY-1999 Order by line_id on line fetch.
203: 115.9 T.Battoo 20-APR-1999 setting the previous value for
204: latest balances - this code had been
205: deleted for some reason.
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;
913: -- The message token for the message text has been passed so extract the
914: -- message text.
915: --
916: else
917: hr_utility.set_message(801, p_message_token);
918: if p_token_name is not null then
919: --
920: -- Set the token value if specified.
921: --
918: if p_token_name is not null then
919: --
920: -- Set the token value if specified.
921: --
922: hr_utility.set_message_token
923: (p_token_name, p_token_value);
924: end if;
925: l_message_text := substrb(hr_utility.get_message, 1, 500);
926: end if;
921: --
922: hr_utility.set_message_token
923: (p_token_name, p_token_value);
924: end if;
925: l_message_text := substrb(hr_utility.get_message, 1, 500);
926: end if;
927: --
928: -- Create new message line.
929: --
926: end if;
927: --
928: -- Create new message line.
929: --
930: hr_utility.trace(l_message_text);
931: --
932: insert into pay_message_lines
933: (line_sequence
934: ,message_level
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;
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;
1124: --
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);
1586: --
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: --
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: --
1679: --
1680: -- The expiry date could not be derived successfully NB. this is signified
1681: -- by an expiry date of the end of time.
1682: --
1683: hr_utility.trace(p_batch_line_rec.dimension_name||' '||
1684: p_batch_line_rec.jurisdiction_code||' '||
1685: p_batch_line_rec.gre_name);
1686: hr_utility.trace('Adjustment Date '||l_adjustment_date);
1687: hr_utility.trace('Adjustment Value '|| l_adjustment_amount);
1682: --
1683: hr_utility.trace(p_batch_line_rec.dimension_name||' '||
1684: p_batch_line_rec.jurisdiction_code||' '||
1685: p_batch_line_rec.gre_name);
1686: hr_utility.trace('Adjustment Date '||l_adjustment_date);
1687: hr_utility.trace('Adjustment Value '|| l_adjustment_amount);
1688: hr_utility.trace('Adjustment Run Type '|| p_batch_line_rec.run_type_id);
1689:
1690: if nvl(l_adjustment_date, END_OF_TIME) = END_OF_TIME
1683: hr_utility.trace(p_batch_line_rec.dimension_name||' '||
1684: p_batch_line_rec.jurisdiction_code||' '||
1685: p_batch_line_rec.gre_name);
1686: hr_utility.trace('Adjustment Date '||l_adjustment_date);
1687: hr_utility.trace('Adjustment Value '|| l_adjustment_amount);
1688: hr_utility.trace('Adjustment Run Type '|| p_batch_line_rec.run_type_id);
1689:
1690: if nvl(l_adjustment_date, END_OF_TIME) = END_OF_TIME
1691: or l_adjustment_date > p_batch_line_rec.upload_date then
1684: p_batch_line_rec.jurisdiction_code||' '||
1685: p_batch_line_rec.gre_name);
1686: hr_utility.trace('Adjustment Date '||l_adjustment_date);
1687: hr_utility.trace('Adjustment Value '|| l_adjustment_amount);
1688: hr_utility.trace('Adjustment Run Type '|| p_batch_line_rec.run_type_id);
1689:
1690: if nvl(l_adjustment_date, END_OF_TIME) = END_OF_TIME
1691: or l_adjustment_date > p_batch_line_rec.upload_date then
1692:
1689:
1690: if nvl(l_adjustment_date, END_OF_TIME) = END_OF_TIME
1691: or l_adjustment_date > p_batch_line_rec.upload_date then
1692:
1693: hr_utility.set_message(801, 'HR_7030_ELE_ENTRY_INV_ADJ');
1694: hr_utility.set_message_token('ADJ_DATE', l_adjustment_date);
1695: raise hr_utility.hr_error;
1696: -- null; -- need to set up message and raise hr_utility.hr_error.
1697: end if;
1690: if nvl(l_adjustment_date, END_OF_TIME) = END_OF_TIME
1691: or l_adjustment_date > p_batch_line_rec.upload_date then
1692:
1693: hr_utility.set_message(801, 'HR_7030_ELE_ENTRY_INV_ADJ');
1694: hr_utility.set_message_token('ADJ_DATE', l_adjustment_date);
1695: raise hr_utility.hr_error;
1696: -- null; -- need to set up message and raise hr_utility.hr_error.
1697: end if;
1698: --
1691: or l_adjustment_date > p_batch_line_rec.upload_date then
1692:
1693: hr_utility.set_message(801, 'HR_7030_ELE_ENTRY_INV_ADJ');
1694: hr_utility.set_message_token('ADJ_DATE', l_adjustment_date);
1695: raise hr_utility.hr_error;
1696: -- null; -- need to set up message and raise hr_utility.hr_error.
1697: end if;
1698: --
1699: -- Amount cannot be set as the current value is greater than the amount to
1692:
1693: hr_utility.set_message(801, 'HR_7030_ELE_ENTRY_INV_ADJ');
1694: hr_utility.set_message_token('ADJ_DATE', l_adjustment_date);
1695: raise hr_utility.hr_error;
1696: -- null; -- need to set up message and raise hr_utility.hr_error.
1697: end if;
1698: --
1699: -- Amount cannot be set as the current value is greater than the amount to
1700: -- be set eg. QTD was 2000.00 while the YTD was 1500.00.
1701: --
1702: -- Negative ajustments of balances are valid.
1703: /*
1704: if l_adjustment_amount < 0 then
1705: hr_utility.set_message(801, 'HR_7030_ELE_ENTRY_INV_ADJ');
1706: raise hr_utility.hr_error;
1707: -- null; -- need to set up message and raise hr_utility.hr_error.
1708: end if;
1709: */
1702: -- Negative ajustments of balances are valid.
1703: /*
1704: if l_adjustment_amount < 0 then
1705: hr_utility.set_message(801, 'HR_7030_ELE_ENTRY_INV_ADJ');
1706: raise hr_utility.hr_error;
1707: -- null; -- need to set up message and raise hr_utility.hr_error.
1708: end if;
1709: */
1710: --
1703: /*
1704: if l_adjustment_amount < 0 then
1705: hr_utility.set_message(801, 'HR_7030_ELE_ENTRY_INV_ADJ');
1706: raise hr_utility.hr_error;
1707: -- null; -- need to set up message and raise hr_utility.hr_error.
1708: end if;
1709: */
1710: --
1711: -- Assignment is not to a payroll on the adjustment date NB. an optimisation
1717: ,l_adjustment_date);
1718: fetch csr_payroll into l_payroll_id;
1719: close csr_payroll;
1720: if l_payroll_id is null then
1721: hr_utility.set_message(801, 'HR_7789_SETUP_ASG_HAS_NO_PAYR');
1722: hr_utility.set_message_token('ADJ_DATE', l_adjustment_date);
1723: raise hr_utility.hr_error;
1724: -- null; -- need to set up message and raise hr_utility.hr_error.
1725: end if;
1718: fetch csr_payroll into l_payroll_id;
1719: close csr_payroll;
1720: if l_payroll_id is null then
1721: hr_utility.set_message(801, 'HR_7789_SETUP_ASG_HAS_NO_PAYR');
1722: hr_utility.set_message_token('ADJ_DATE', l_adjustment_date);
1723: raise hr_utility.hr_error;
1724: -- null; -- need to set up message and raise hr_utility.hr_error.
1725: end if;
1726: --
1719: close csr_payroll;
1720: if l_payroll_id is null then
1721: hr_utility.set_message(801, 'HR_7789_SETUP_ASG_HAS_NO_PAYR');
1722: hr_utility.set_message_token('ADJ_DATE', l_adjustment_date);
1723: raise hr_utility.hr_error;
1724: -- null; -- need to set up message and raise hr_utility.hr_error.
1725: end if;
1726: --
1727: -- In the US, each assignment always belongs to a legal company. If the
1720: if l_payroll_id is null then
1721: hr_utility.set_message(801, 'HR_7789_SETUP_ASG_HAS_NO_PAYR');
1722: hr_utility.set_message_token('ADJ_DATE', l_adjustment_date);
1723: raise hr_utility.hr_error;
1724: -- null; -- need to set up message and raise hr_utility.hr_error.
1725: end if;
1726: --
1727: -- In the US, each assignment always belongs to a legal company. If the
1728: -- legal compamy was not specified as a context then get the legal comapny
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: -----------------------------------------------------------------------------
1816: l_start_ptr number;
1817: l_ptr number;
1818: begin
1819: --
1820: hr_utility.trace('Entering load_element_contexts');
1821: --
1822: p_start_ptr:= null;
1823: p_end_ptr := null;
1824: --
1872: p_end_ptr := g_element_link_contexts(p_element_link_id).end_ptr;
1873: --
1874: end if;
1875: --
1876: hr_utility.trace('Exitting load_element_contexts');
1877: --
1878: end load_element_contexts;
1879: --
1880:
1906: found boolean;
1907: --
1908: begin
1909: --
1910: hr_utility.trace('Entering set_entry_context');
1911: --
1912: load_element_contexts(p_element_link_id,
1913: l_start_ptr,
1914: l_end_ptr
1957: --
1958: end if;
1959: end if;
1960: --
1961: hr_utility.trace('Exitting set_entry_context');
1962: --
1963: end set_entry_context;
1964: -----------------------------------------------------------------------------
1965: -- NAME
1988: )
1989: is
1990: begin
1991: --
1992: hr_utility.trace('Entering create_entry_values');
1993: -- OK setup the contexts
1994: --
1995: if (p_jurisdiction_code is not null) then
1996: set_entry_context(
2047: p_num_entry_values := p_num_entry_values + 1;
2048: p_input_value_id_tbl(p_num_entry_values) := p_adj_iv_id;
2049: p_entry_value_tbl(p_num_entry_values) := to_char(p_adj_amount);
2050: --
2051: hr_utility.trace('Exitting create_entry_values');
2052: --
2053: end create_entry_values;
2054:
2055: --
2156: and JOB.job_id (+)= ASSIGN.job_id;
2157:
2158:
2159: begin
2160: hr_utility.trace('wcb_context_setup 10');
2161: hr_utility.trace('p_asg_id '||p_asg_id);
2162: hr_utility.trace('p_business_group_id '||p_business_group_id);
2163: hr_utility.trace('p_payroll_action_id '||p_payroll_action_id);
2164: hr_utility.trace('p_tax_unit_id '||p_tax_unit_id);
2157:
2158:
2159: begin
2160: hr_utility.trace('wcb_context_setup 10');
2161: hr_utility.trace('p_asg_id '||p_asg_id);
2162: hr_utility.trace('p_business_group_id '||p_business_group_id);
2163: hr_utility.trace('p_payroll_action_id '||p_payroll_action_id);
2164: hr_utility.trace('p_tax_unit_id '||p_tax_unit_id);
2165:
2158:
2159: begin
2160: hr_utility.trace('wcb_context_setup 10');
2161: hr_utility.trace('p_asg_id '||p_asg_id);
2162: hr_utility.trace('p_business_group_id '||p_business_group_id);
2163: hr_utility.trace('p_payroll_action_id '||p_payroll_action_id);
2164: hr_utility.trace('p_tax_unit_id '||p_tax_unit_id);
2165:
2166: OPEN c_assignment_jur;
2159: begin
2160: hr_utility.trace('wcb_context_setup 10');
2161: hr_utility.trace('p_asg_id '||p_asg_id);
2162: hr_utility.trace('p_business_group_id '||p_business_group_id);
2163: hr_utility.trace('p_payroll_action_id '||p_payroll_action_id);
2164: hr_utility.trace('p_tax_unit_id '||p_tax_unit_id);
2165:
2166: OPEN c_assignment_jur;
2167: FETCH c_assignment_jur into l_jur_code;
2160: hr_utility.trace('wcb_context_setup 10');
2161: hr_utility.trace('p_asg_id '||p_asg_id);
2162: hr_utility.trace('p_business_group_id '||p_business_group_id);
2163: hr_utility.trace('p_payroll_action_id '||p_payroll_action_id);
2164: hr_utility.trace('p_tax_unit_id '||p_tax_unit_id);
2165:
2166: OPEN c_assignment_jur;
2167: FETCH c_assignment_jur into l_jur_code;
2168: CLOSE c_assignment_jur;
2166: OPEN c_assignment_jur;
2167: FETCH c_assignment_jur into l_jur_code;
2168: CLOSE c_assignment_jur;
2169:
2170: hr_utility.trace('l_jur_code '||l_jur_code);
2171:
2172: OPEN WCB_input_value('Account Number');
2173: FETCH WCB_input_value into l_wcb_acc_number;
2174: hr_utility.trace('l_wcb_acc_number1 '||l_wcb_acc_number);
2170: hr_utility.trace('l_jur_code '||l_jur_code);
2171:
2172: OPEN WCB_input_value('Account Number');
2173: FETCH WCB_input_value into l_wcb_acc_number;
2174: hr_utility.trace('l_wcb_acc_number1 '||l_wcb_acc_number);
2175: if WCB_input_value%notfound or l_wcb_acc_number is null then
2176:
2177: /* Account Number was not supplied through the input value, so look at the
2178: default for Account number on the Soft Coded KeyFlex */
2178: default for Account number on the Soft Coded KeyFlex */
2179:
2180: OPEN WCB_ACC_AT_STAT_INFO;
2181: FETCH WCB_ACC_AT_STAT_INFO into l_wcb_acc_number;
2182: hr_utility.trace('l_wcb_acc_number2 '||l_wcb_acc_number);
2183: if WCB_ACC_AT_STAT_INFO%notfound or l_wcb_acc_number is null then
2184:
2185: /* Account Number was not supplied through the Soft Coded Keyflex,
2186: so look for default Account number at the GRE level on Provincial Reporting
2187: Info Descriptive flex. */
2188:
2189: OPEN WCB_ACC_AT_PROV_RPT;
2190: FETCH WCB_ACC_AT_PROV_RPT into l_wcb_acc_number;
2191: hr_utility.trace('l_wcb_acc_number3 '||l_wcb_acc_number);
2192: if WCB_ACC_AT_PROV_RPT%notfound or l_wcb_acc_number is null then
2193:
2194: /* Account Number was not supplied through the Org Developer DF flexfield,
2195: in fact never supplied, return an error message */
2206: CLOSE WCB_input_value;
2207:
2208: OPEN WCB_input_value('Rate Code');
2209: FETCH WCB_input_value into l_wcb_rate_code;
2210: hr_utility.trace('l_wcb_rate_code1 '||l_wcb_rate_code);
2211: if WCB_input_value%notfound or l_wcb_rate_code is null then
2212:
2213: /* Code override not suplied through the input value, so try the default
2214: Code on the Soft Coded KeyFlex. */
2214: Code on the Soft Coded KeyFlex. */
2215:
2216: OPEN RATE_CODE_STAT_INFO;
2217: FETCH RATE_CODE_STAT_INFO into l_wcb_rate_code;
2218: hr_utility.trace('l_wcb_rate_code2 '||l_wcb_rate_code);
2219: if RATE_CODE_STAT_INFO%notfound or l_wcb_rate_code is null then
2220:
2221: /* Code was not supplied at all, check to see if a the asg has a job */
2222:
2221: /* Code was not supplied at all, check to see if a the asg has a job */
2222:
2223: OPEN C_ASSIGNMENT_JOB;
2224: FETCH C_ASSIGNMENT_JOB into l_assignment_job_name;
2225: hr_utility.trace('l_assignment_job_name '||l_assignment_job_name);
2226:
2227: if C_ASSIGNMENT_JOB%notfound or l_assignment_job_name is null then
2228: CLOSE WCB_input_value;
2229: CLOSE RATE_CODE_STAT_INFO;
2235: l_reporting_unit_id := pay_ca_wcb_ff_functions.get_rate_id_for_job(
2236: l_wcb_acc_number,
2237: l_assignment_job_name,
2238: l_jur_code);
2239: hr_utility.trace('l_reporting_unit_id1 '||l_reporting_unit_id);
2240: CLOSE C_ASSIGNMENT_JOB;
2241: else
2242: l_reporting_unit_id := pay_ca_wcb_ff_functions.get_rate_id_for_wcb_code(
2243: p_business_group_id,
2243: p_business_group_id,
2244: l_wcb_acc_number,
2245: l_wcb_rate_code,
2246: l_jur_code);
2247: hr_utility.trace('l_reporting_unit_id2 '||l_reporting_unit_id);
2248: CLOSE RATE_CODE_STAT_INFO;
2249: end if;
2250: else
2251: l_reporting_unit_id := pay_ca_wcb_ff_functions.get_rate_id_for_wcb_code(
2252: p_business_group_id,
2253: l_wcb_acc_number,
2254: l_wcb_rate_code,
2255: l_jur_code);
2256: hr_utility.trace('l_reporting_unit_id3 '||l_reporting_unit_id);
2257: CLOSE WCB_input_value;
2258: end if;
2259:
2260: IF l_reporting_unit_id = 1.1 THEN
2275: return;
2276: END IF;
2277:
2278: begin
2279: hr_utility.trace('Updating pay_action_contexts');
2280: select assignment_action_id into l_assignment_action_id from pay_assignment_actions where
2281: payroll_action_id = p_payroll_action_id and assignment_id = p_asg_id;
2282: hr_utility.trace('l_assignment_action_id '||l_assignment_action_id);
2283: INSERT INTO pay_action_contexts
2278: begin
2279: hr_utility.trace('Updating pay_action_contexts');
2280: select assignment_action_id into l_assignment_action_id from pay_assignment_actions where
2281: payroll_action_id = p_payroll_action_id and assignment_id = p_asg_id;
2282: hr_utility.trace('l_assignment_action_id '||l_assignment_action_id);
2283: INSERT INTO pay_action_contexts
2284: (ASSIGNMENT_ACTION_ID
2285: ,ASSIGNMENT_ID
2286: ,CONTEXT_ID
2293: commit;
2294: p_message := 'Insert Record in pay_action_contexts successfully';
2295: EXCEPTION
2296: when others then
2297: hr_utility.trace('Exception raised while inserting data in pay_action_contexts '||p_asg_id);
2298: p_message := 'Exception raised while inserting data in pay_action_contexts '||p_asg_id;
2299: end;
2300:
2301: end wcb_context_setup;
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: --
2425: if csr_bal_adj%found then
2426: --
2427: -- Keep track of balance adjustment information.
2428: --
2429: hr_utility.trace('Adding to the Adjustment List');
2430: hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2431: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2432: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2433: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2426: --
2427: -- Keep track of balance adjustment information.
2428: --
2429: hr_utility.trace('Adding to the Adjustment List');
2430: hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2431: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2432: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2433: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2434: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2427: -- Keep track of balance adjustment information.
2428: --
2429: hr_utility.trace('Adding to the Adjustment List');
2430: hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2431: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2432: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2433: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2434: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2435: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2428: --
2429: hr_utility.trace('Adding to the Adjustment List');
2430: hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2431: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2432: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2433: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2434: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2435: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2436: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2429: hr_utility.trace('Adding to the Adjustment List');
2430: hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2431: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2432: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2433: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2434: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2435: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2436: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2437: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2430: hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2431: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2432: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2433: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2434: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2435: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2436: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2437: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2438: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2431: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2432: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2433: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2434: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2435: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2436: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2437: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2438: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2439: hr_utility.trace(' Source Number '|| l_bal_adj_rec.source_number);
2432: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2433: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2434: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2435: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2436: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2437: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2438: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2439: hr_utility.trace(' Source Number '|| l_bal_adj_rec.source_number);
2440: hr_utility.trace(' Source Text2 '|| l_bal_adj_rec.source_text2);
2433: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2434: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2435: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2436: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2437: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2438: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2439: hr_utility.trace(' Source Number '|| l_bal_adj_rec.source_number);
2440: hr_utility.trace(' Source Text2 '|| l_bal_adj_rec.source_text2);
2441: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2434: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2435: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2436: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2437: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2438: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2439: hr_utility.trace(' Source Number '|| l_bal_adj_rec.source_number);
2440: hr_utility.trace(' Source Text2 '|| l_bal_adj_rec.source_text2);
2441: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2442: hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2435: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2436: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2437: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2438: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2439: hr_utility.trace(' Source Number '|| l_bal_adj_rec.source_number);
2440: hr_utility.trace(' Source Text2 '|| l_bal_adj_rec.source_text2);
2441: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2442: hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2443: hr_utility.trace(' JIV ID '|| l_bal_adj_rec.jc_input_value_id);
2436: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2437: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2438: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2439: hr_utility.trace(' Source Number '|| l_bal_adj_rec.source_number);
2440: hr_utility.trace(' Source Text2 '|| l_bal_adj_rec.source_text2);
2441: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2442: hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2443: hr_utility.trace(' JIV ID '|| l_bal_adj_rec.jc_input_value_id);
2444: l_ele_link_id := l_bal_adj_rec.element_link_id;
2437: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2438: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2439: hr_utility.trace(' Source Number '|| l_bal_adj_rec.source_number);
2440: hr_utility.trace(' Source Text2 '|| l_bal_adj_rec.source_text2);
2441: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2442: hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2443: hr_utility.trace(' JIV ID '|| l_bal_adj_rec.jc_input_value_id);
2444: l_ele_link_id := l_bal_adj_rec.element_link_id;
2445: l_oee_id := l_bal_adj_rec.original_entry_id;
2438: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2439: hr_utility.trace(' Source Number '|| l_bal_adj_rec.source_number);
2440: hr_utility.trace(' Source Text2 '|| l_bal_adj_rec.source_text2);
2441: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2442: hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2443: hr_utility.trace(' JIV ID '|| l_bal_adj_rec.jc_input_value_id);
2444: l_ele_link_id := l_bal_adj_rec.element_link_id;
2445: l_oee_id := l_bal_adj_rec.original_entry_id;
2446: l_bal_type_id := l_bal_adj_rec.balance_type_id;
2439: hr_utility.trace(' Source Number '|| l_bal_adj_rec.source_number);
2440: hr_utility.trace(' Source Text2 '|| l_bal_adj_rec.source_text2);
2441: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2442: hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2443: hr_utility.trace(' JIV ID '|| l_bal_adj_rec.jc_input_value_id);
2444: l_ele_link_id := l_bal_adj_rec.element_link_id;
2445: l_oee_id := l_bal_adj_rec.original_entry_id;
2446: l_bal_type_id := l_bal_adj_rec.balance_type_id;
2447: l_adj_date := l_bal_adj_rec.adjustment_date;
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:
2466:
2467: OPEN c_wcb_balance(l_bal_adj_rec.balance_type_id);
2468: FETCH c_wcb_balance into l_is_wcb;
2469: CLOSE c_wcb_balance;
2470: hr_utility.trace('l_is_wcb '||l_is_wcb);
2471:
2472: create_entry_values (
2473: l_ele_link_id,
2474: l_jurisdiction_code,
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.
2587: );
2588:
2589: l_idx := g_payroll_actions.count + 1;
2590:
2591: hr_utility.trace('Adding new payroll action information to cache.');
2592: hr_utility.trace(' payroll_action_id = '|| l_payroll_action_id);
2593: hr_utility.trace(' effective_date = '|| l_adj_date);
2594: hr_utility.trace(' payroll_id = '|| l_payroll_id);
2595:
2588:
2589: l_idx := g_payroll_actions.count + 1;
2590:
2591: hr_utility.trace('Adding new payroll action information to cache.');
2592: hr_utility.trace(' payroll_action_id = '|| l_payroll_action_id);
2593: hr_utility.trace(' effective_date = '|| l_adj_date);
2594: hr_utility.trace(' payroll_id = '|| l_payroll_id);
2595:
2596: l_payroll_action_rec.payroll_action_id := l_payroll_action_id;
2589: l_idx := g_payroll_actions.count + 1;
2590:
2591: hr_utility.trace('Adding new payroll action information to cache.');
2592: hr_utility.trace(' payroll_action_id = '|| l_payroll_action_id);
2593: hr_utility.trace(' effective_date = '|| l_adj_date);
2594: hr_utility.trace(' payroll_id = '|| l_payroll_id);
2595:
2596: l_payroll_action_rec.payroll_action_id := l_payroll_action_id;
2597: l_payroll_action_rec.effective_date := l_adj_date;
2590:
2591: hr_utility.trace('Adding new payroll action information to cache.');
2592: hr_utility.trace(' payroll_action_id = '|| l_payroll_action_id);
2593: hr_utility.trace(' effective_date = '|| l_adj_date);
2594: hr_utility.trace(' payroll_id = '|| l_payroll_id);
2595:
2596: l_payroll_action_rec.payroll_action_id := l_payroll_action_id;
2597: l_payroll_action_rec.effective_date := l_adj_date;
2598: l_payroll_action_rec.payroll_id := l_payroll_id;
2620: );
2621:
2622: -- WCB balances, bug 11657441
2623: if nvl(l_is_wcb,'N') = 'Y' then
2624: hr_utility.trace('Entered in l_is_wcb');
2625: wcb_context_setup(p_glbl_data_rec.assignment_id,
2626: p_glbl_data_rec.business_group_id,
2627: l_payroll_action_id,
2628: l_tax_unit_id,
2627: l_payroll_action_id,
2628: l_tax_unit_id,
2629: l_adj_date,
2630: l_message);
2631: hr_utility.trace(l_message);
2632: end if;
2633: -- WCB balances, bug 11657441
2634:
2635: --
2639: -- used to set each initial balance is held against the corresponding
2640: -- batch line.
2641: --
2642: for l_index in 1..p_num_lines loop
2643: hr_utility.trace('Updating Line '||p_batch_line_list(l_index)||
2644: ' With Payroll Action '||l_payroll_action_id);
2645: update pay_balance_batch_lines BL
2646: set BL.batch_line_status = 'T' -- Transferred
2647: ,BL.payroll_action_id = l_payroll_action_id
2660: exit when csr_bal_adj%notfound;
2661: --
2662: -- Keep track of balance adjustment information.
2663: --
2664: hr_utility.trace('Adding to the Adjustment List');
2665: hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2666: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2667: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2668: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2661: --
2662: -- Keep track of balance adjustment information.
2663: --
2664: hr_utility.trace('Adding to the Adjustment List');
2665: hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2666: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2667: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2668: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2669: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2662: -- Keep track of balance adjustment information.
2663: --
2664: hr_utility.trace('Adding to the Adjustment List');
2665: hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2666: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2667: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2668: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2669: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2670: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2663: --
2664: hr_utility.trace('Adding to the Adjustment List');
2665: hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2666: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2667: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2668: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2669: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2670: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2671: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2664: hr_utility.trace('Adding to the Adjustment List');
2665: hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2666: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2667: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2668: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2669: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2670: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2671: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2672: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2665: hr_utility.trace(' Line Id '|| l_bal_adj_rec.batch_line_id);
2666: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2667: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2668: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2669: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2670: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2671: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2672: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2673: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2666: hr_utility.trace(' Ele Lnk '|| l_bal_adj_rec.element_link_id);
2667: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2668: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2669: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2670: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2671: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2672: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2673: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2674: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2667: hr_utility.trace(' OEE Id '|| l_bal_adj_rec.original_entry_id);
2668: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2669: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2670: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2671: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2672: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2673: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2674: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2675: hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2668: hr_utility.trace(' Bal Type '|| l_bal_adj_rec.balance_type_id);
2669: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2670: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2671: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2672: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2673: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2674: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2675: hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2676: hr_utility.trace(' JIV ID '|| l_bal_adj_rec.jc_input_value_id);
2669: hr_utility.trace(' Adj Date '|| l_bal_adj_rec.adjustment_date);
2670: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2671: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2672: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2673: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2674: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2675: hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2676: hr_utility.trace(' JIV ID '|| l_bal_adj_rec.jc_input_value_id);
2677: l_ele_link_id := l_bal_adj_rec.element_link_id;
2670: hr_utility.trace(' Jur Code '|| l_bal_adj_rec.jurisdiction_code);
2671: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2672: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2673: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2674: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2675: hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2676: hr_utility.trace(' JIV ID '|| l_bal_adj_rec.jc_input_value_id);
2677: l_ele_link_id := l_bal_adj_rec.element_link_id;
2678: l_oee_id := l_bal_adj_rec.original_entry_id;
2671: hr_utility.trace(' Tax Unit '|| l_bal_adj_rec.tax_unit_id);
2672: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2673: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2674: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2675: hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2676: hr_utility.trace(' JIV ID '|| l_bal_adj_rec.jc_input_value_id);
2677: l_ele_link_id := l_bal_adj_rec.element_link_id;
2678: l_oee_id := l_bal_adj_rec.original_entry_id;
2679: l_bal_type_id := l_bal_adj_rec.balance_type_id;
2672: hr_utility.trace(' Source ID '|| l_bal_adj_rec.source_id);
2673: hr_utility.trace(' Source Text '|| l_bal_adj_rec.source_text);
2674: hr_utility.trace(' Run Type ID '|| l_bal_adj_rec.run_type_id);
2675: hr_utility.trace(' IV ID '|| l_bal_adj_rec.ibf_input_value_id);
2676: hr_utility.trace(' JIV ID '|| l_bal_adj_rec.jc_input_value_id);
2677: l_ele_link_id := l_bal_adj_rec.element_link_id;
2678: l_oee_id := l_bal_adj_rec.original_entry_id;
2679: l_bal_type_id := l_bal_adj_rec.balance_type_id;
2680: l_adj_date := l_bal_adj_rec.adjustment_date;
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: --
3487: p_status=>l_status);
3488:
3489: if (l_status='Y')
3490: then
3491: hr_utility.trace('latest balances table');
3492: insert into pay_latest_balances
3493: (latest_balance_id,
3494: assignment_id,
3495: defined_balance_id,
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: --
3748: -- Store the details of linein case of error
3749: --
3750: p_num_lines := 1;
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);
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: --
5383: --
5384: -- Get the next batch line for the assignment.
5385: --
5386: fetch csr_batch_line_transfer into l_batch_line_rec;
5387: hr_utility.trace('BATCH_LINE_ID = '||l_batch_line_rec.batch_line_id);
5388: hr_utility.trace('EXPIRY_DATE = '||l_batch_line_rec.expiry_date);
5389: --
5390: -- Terminate the loop either if there are no more batch lines to process
5391: -- or the batch line has been transferred already or is invalid. The
5384: -- Get the next batch line for the assignment.
5385: --
5386: fetch csr_batch_line_transfer into l_batch_line_rec;
5387: hr_utility.trace('BATCH_LINE_ID = '||l_batch_line_rec.batch_line_id);
5388: hr_utility.trace('EXPIRY_DATE = '||l_batch_line_rec.expiry_date);
5389: --
5390: -- Terminate the loop either if there are no more batch lines to process
5391: -- or the batch line has been transferred already or is invalid. The
5392: -- transfer of the batch lines for an assignment will only continue if all
5435:
5436: end loop;
5437: --
5438: if conflicts <> 0 then
5439: hr_utility.set_message(801, 'PAY_52152_INV_ASS_BALS');
5440: raise hr_utility.hr_error;
5441: end if;
5442: --
5443: -- Create all the balance adjustments for the assignment which will set the
5436: end loop;
5437: --
5438: if conflicts <> 0 then
5439: hr_utility.set_message(801, 'PAY_52152_INV_ASS_BALS');
5440: raise hr_utility.hr_error;
5441: end if;
5442: --
5443: -- Create all the balance adjustments for the assignment which will set the
5444: -- initial balances as specified by the batch lines for that assignment.
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
5475: --
5476: -- The transfer has failed.
5477: --
5478: exception
5479: when hr_utility.hr_error then
5480: --
5481: -- Close the batch line cursor if it is open.
5482: --
5483: if csr_batch_line_transfer%isopen then
5485: end if;
5486: --
5487: -- Extract the error message.
5488: --
5489: l_message := substrb(nvl(hr_utility.get_message, sqlerrm), 1, 240);
5490: --
5491: -- Undo all the work relating to the assignment.
5492: --
5493: rollback to assignment_savepoint;
5520: delete from pay_temp_balance_adjustments;
5521: --
5522: when others then
5523: --
5524: hr_utility.trace(sqlerrm);
5525: --
5526: -- Extract the error message.
5527: --
5528: l_message := substrb(sqlerrm, 1, 240);
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: --
5628: -- Get the next assignment.
5629: --
5630: fetch csr_assignment into p_glbl_data_rec.assignment_id;
5631: exit when csr_assignment%notfound;
5632: hr_utility.trace('ASG = '||p_glbl_data_rec.assignment_id);
5633: --
5634: -- Keep a count of the number of assignments that have been processed.
5635: --
5636: l_asg_count := l_asg_count + 1;
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: -----------------------------------------------------------------------------
5882: return;
5883: end if;
5884: --
5885: close csr_get_payroll_actions;
5886: hr_utility.set_message(801, 'HR_51053_ASA_PREV_PROCESSED');
5887: raise hr_utility.hr_error;
5888: end valid_latest_balance_run;
5889: --
5890: --
5883: end if;
5884: --
5885: close csr_get_payroll_actions;
5886: hr_utility.set_message(801, 'HR_51053_ASA_PREV_PROCESSED');
5887: raise hr_utility.hr_error;
5888: end valid_latest_balance_run;
5889: --
5890: --
5891: -----------------------------------------------------------------------------
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
6040: and pbbl.batch_id = p_glbl_data_rec.batch_id
6041: ;
6042: begin
6043: --
6044: hr_utility.set_location('Entering:'||l_proc, 5);
6045:
6046: if p_glbl_data_rec.upload_mode = 'TRANSFER' then
6047:
6048: for l_batch in csr_batch loop
6046: if p_glbl_data_rec.upload_mode = 'TRANSFER' then
6047:
6048: for l_batch in csr_batch loop
6049: --
6050: hr_utility.trace(' payroll_action_id=:'||l_batch.payroll_action_id);
6051: --
6052: -- Completes the payroll action.
6053: --
6054: pay_bal_adjust.process_batch(l_batch.payroll_action_id);
6056: end loop;
6057:
6058: end if;
6059:
6060: hr_utility.set_location('Leaving:'||l_proc, 50);
6061:
6062: end post_transfer_batch;
6063:
6064: -----------------------------------------------------------------------------
6085: l_message varchar2(240);
6086: l_glbl_data_rec glbl_data_rec_type:= p_glbl_data_rec;
6087: begin
6088: --
6089: hr_utility.set_location('Entering:'||l_proc, 5);
6090:
6091: --
6092: -- Validate the batch.
6093: --
6113: -- Set the status of the batch.
6114: --
6115: set_batch_status(l_glbl_data_rec);
6116:
6117: hr_utility.set_location('Leaving:'||l_proc, 50);
6118: --
6119: exception
6120: --
6121: -- Treats the exception trapped here as a batch header error.
6119: exception
6120: --
6121: -- Treats the exception trapped here as a batch header error.
6122: --
6123: when hr_utility.hr_error then
6124: hr_utility.set_location(l_proc, 55);
6125:
6126: l_message := substrb(nvl(hr_utility.get_message, sqlerrm), 1, 240);
6127:
6120: --
6121: -- Treats the exception trapped here as a batch header error.
6122: --
6123: when hr_utility.hr_error then
6124: hr_utility.set_location(l_proc, 55);
6125:
6126: l_message := substrb(nvl(hr_utility.get_message, sqlerrm), 1, 240);
6127:
6128: write_message_line
6122: --
6123: when hr_utility.hr_error then
6124: hr_utility.set_location(l_proc, 55);
6125:
6126: l_message := substrb(nvl(hr_utility.get_message, sqlerrm), 1, 240);
6127:
6128: write_message_line
6129: (p_meesage_level => HEADER
6130: ,p_batch_id => l_glbl_data_rec.batch_id
6137: set batch_status = 'E'
6138: where batch_id = l_glbl_data_rec.batch_id;
6139:
6140: when others then
6141: hr_utility.set_location(l_proc, 60);
6142:
6143: l_message := substrb(sqlerrm, 1, 240);
6144:
6145: write_message_line
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