6: Cursor get_gen_no(p_payroll_action_id number, p_code varchar2) is
7: Select
8: nvl(max(acb.gen_number),0) + 1
9: From
10: pay_za_acb_user_gen_nos acb
11: Where
12: acb.user_code = p_code
13: And acb.payroll_action_id =
14: (
12: acb.user_code = p_code
13: And acb.payroll_action_id =
14: (
15: Select max(sub.payroll_action_id)
16: From pay_za_acb_user_gen_nos sub
17: Where sub.user_code = p_code
18: )
19: And not exists
20: (
18: )
19: And not exists
20: (
21: Select 1
22: From pay_za_acb_user_gen_nos sub
23: Where sub.payroll_action_id = p_payroll_action_id
24: And sub.user_code = p_code
25: )
26: Union
26: Union
27: Select
28: acb.gen_number
29: From
30: pay_za_acb_user_gen_nos acb
31: Where
32: acb.user_code = p_code
33: And acb.payroll_action_id = p_payroll_action_id;
34:
62: If user_gen > 9999 then
63: user_gen := 1;
64: End if;
65:
66: /* Insert the new gen number into pay_za_acb_user_gen_nos */
67:
68: Insert into pay_za_acb_user_gen_nos
69: (
70: payroll_action_id
64: End if;
65:
66: /* Insert the new gen number into pay_za_acb_user_gen_nos */
67:
68: Insert into pay_za_acb_user_gen_nos
69: (
70: payroll_action_id
71: ,user_code
72: ,gen_number
80: Where
81: not exists
82: (
83: Select 1
84: From pay_za_acb_user_gen_nos sub
85: Where sub.payroll_action_id = p_payroll_action_id
86: And sub.user_code = p_user_code
87: );
88:
125:
126:
127: /* Insert new user generation number */
128:
129: Insert into pay_za_acb_user_gen_nos
130: (
131: payroll_action_id
132: ,user_code
133: ,gen_number
141: Where
142: not exists
143: (
144: Select 1
145: From pay_za_acb_user_gen_nos sub
146: Where sub.payroll_action_id = p_payroll_action_id
147: And sub.user_code = p_acb_inst_code
148: );
149: Else
152:
153: /* Select the next installation gen no from sequence */
154:
155: Select
156: pay_za_acb_user_gen_nos_s.nextval into inst_gen
157: From
158: sys.dual;
159:
160: /* Check that installation gen no > user gen, since the