[Home] [Help]
304: -- ----------------------------------------------------------------------------
305: --
306: -- Description:
307: -- This procedures deletes all records associated with the batch balance
308: -- adjustment tables and the pay_message_lines table.
309: --
310: -- ----------------------------------------------------------------------------
311: --
312: procedure purge
343: --
344: -- Delete all messages of the batch line
345: --
346: delete
347: from pay_message_lines
348: where source_type = 'I'
349: and source_id = cssr.batch_line_id;
350: --
351:
366: --
367: -- Delete all messages of the batch group
368: --
369: delete
370: from pay_message_lines
371: where source_type = 'G'
372: and source_id = cssr.batch_group_id;
373: --
374:
390: --
391: -- Deletes all messages of the batch header
392: --
393: delete
394: from pay_message_lines
395: where source_type = 'H'
396: and source_id = p_batch_id;
397: --
398:
465: l_message_source_type_tbl hr_entry.varchar2_table;
466: l_message_level_tbl hr_entry.varchar2_table;
467: l_message_text_tbl varchar240_table;
468: l_count binary_integer := 0;
469: l_error_text pay_message_lines.line_text%type;
470:
471: --
472: cursor csr_batch_groups is
473: select batch_group_id
555: for i in 1..l_count loop
556:
557: if l_message_text_tbl(i) is not null then
558: --
559: insert into pay_message_lines
560: (line_sequence,
561: payroll_id,
562: message_level,
563: source_id,
563: source_id,
564: source_type,
565: line_text )
566: values
567: (pay_message_lines_s.nextval,
568: null,
569: l_message_level_tbl(i),
570: l_message_source_id_tbl(i),
571: l_message_source_type_tbl(i),
619: l_element_link_id number := 0;
620:
621: dummy_consolidation_id pay_consolidation_sets.consolidation_set_id%type;
622: dummy_payroll_id pay_all_payrolls_f.payroll_id%type;
623: dummy_msg_source_id pay_message_lines.source_id%type;
624:
625: -- Temperorary table for storing error messages and used for later putting into
626: -- pay_message_lines
627: l_message_source_id_tbl hr_entry.number_table;
622: dummy_payroll_id pay_all_payrolls_f.payroll_id%type;
623: dummy_msg_source_id pay_message_lines.source_id%type;
624:
625: -- Temperorary table for storing error messages and used for later putting into
626: -- pay_message_lines
627: l_message_source_id_tbl hr_entry.number_table;
628: l_message_source_type_tbl hr_entry.varchar2_table;
629: l_message_level_tbl hr_entry.varchar2_table;
630: l_message_text_tbl varchar240_table;
628: l_message_source_type_tbl hr_entry.varchar2_table;
629: l_message_level_tbl hr_entry.varchar2_table;
630: l_message_text_tbl varchar240_table;
631: l_count binary_integer := 0;
632: l_error_text pay_message_lines.line_text%type;
633: l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
634: p_batch_name pay_balance_batch_headers.batch_name%type;
635:
636: --
683: set batch_group_status = 'P'
684: where batch_group_id = group_csr.batch_group_id;
685:
686: -- deleting all the message lines for this group
687: delete from pay_message_lines
688: where source_type = 'G'
689: and source_id = group_csr.batch_group_id;
690: --deleting all the message lines for the batch lines in this group
691: for line_csr in csr_batch_lines(group_csr.batch_group_id) loop
688: where source_type = 'G'
689: and source_id = group_csr.batch_group_id;
690: --deleting all the message lines for the batch lines in this group
691: for line_csr in csr_batch_lines(group_csr.batch_group_id) loop
692: delete from pay_message_lines
693: where source_type = 'I'
694: and source_id = line_csr.batch_line_id;
695: end loop;
696:
742: exception
743: when others then
744: hr_utility.set_message(801,'HR_6614_PAY_NO_TIME_PERIOD');
745: l_error_text := substrb(hr_utility.get_message, 1, 240);
746: insert into pay_message_lines
747: (line_sequence,
748: payroll_id,
749: message_level,
750: source_id,
750: source_id,
751: source_type,
752: line_text)
753: values
754: (pay_message_lines_s.nextval,
755: null,
756: 'F',
757: group_csr.batch_group_id,
758: 'G',
949: */
950: for j in 1..l_count loop
951: --
952: if l_message_text_tbl(j) is not null then
953: insert into pay_message_lines
954: (line_sequence,
955: payroll_id,
956: message_level,
957: source_id,
957: source_id,
958: source_type,
959: line_text)
960: values
961: (pay_message_lines_s.nextval,
962: null,
963: l_message_level_tbl(j),
964: l_message_source_id_tbl(j),
965: l_message_source_type_tbl(j),
966: l_message_text_tbl(j)
967: );
968: end if;
969: --
970: end loop; -- for inserting into pay_message_lines
971:
972: commit;
973:
974: -- the main exception block handling errors in initial checking like if payroll_id, consolidation_id
982: then
983: update pay_adjust_batch_groups
984: set batch_group_status = 'E'
985: where batch_group_id = group_csr.batch_group_id;
986: --put a message against batch_group in pay_message_lines at group_level
987: begin
988: select pml.source_id id into dummy_msg_source_id
989: from pay_message_lines pml
990: where pml.source_id = group_csr.batch_group_id
985: where batch_group_id = group_csr.batch_group_id;
986: --put a message against batch_group in pay_message_lines at group_level
987: begin
988: select pml.source_id id into dummy_msg_source_id
989: from pay_message_lines pml
990: where pml.source_id = group_csr.batch_group_id
991: and source_type = 'G';
992: exception
993: when no_data_found then
995: l_proc := 'validating the batch group';
996: hr_utility.set_message_token('PROC', l_proc);
997: l_error_text := substrb(hr_utility.get_message || ' ' ||
998: sqlerrm, 1, 240);
999: insert into pay_message_lines
1000: (line_sequence,
1001: payroll_id,
1002: message_level,
1003: source_id,
1003: source_id,
1004: source_type,
1005: line_text)
1006: values
1007: (pay_message_lines_s.nextval,
1008: null,
1009: 'F',
1010: group_csr.batch_group_id,
1011: 'G',