1 package body pay_za_acb_tape as
2 /* $Header: pyzaacb.pkb 120.1 2006/05/17 03:24:20 nragavar ship $ */
3 /* This cursor is used for the retrieval of both */
4 /* User and Installation generation numbers */
5
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 (
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 (
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
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
35
36 Function get_acb_user_gen_num
37 (
38 p_payroll_action_id in number
39 ,p_user_code in varchar2
40 )
41 return number is
42
43 -- l_return_val number(30);
44
45 Begin
46
47 /* get gen number used on last tape submitted */
48
49 Open get_gen_no(p_payroll_action_id, p_user_code);
50 Fetch get_gen_no into user_gen;
51
52 /* Set the gen number = 1 if table contains no entries */
53
54 If get_gen_no%notfound then
55 user_gen := 1;
56 End if;
57
58 Close get_gen_no;
59
60 /* Check that gen no does not exceed 9999, if so, reset to 1 */
61
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
71 ,user_code
72 ,gen_number
73 )
74 Select
75 p_payroll_action_id
76 , p_user_code
77 , user_gen
78 From
79 sys.dual
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
89 return user_gen;
90
91 End get_acb_user_gen_num;
92
93 Function get_acb_inst_gen_num
94 (
95 p_payroll_action_id in number
96 ,p_acb_user_type in varchar2
97 ,p_acb_inst_code in varchar2
98 )
99 Return Number
100 is
101
102 -- l_inst_gen number(10);
103
104 Begin
105
106 If p_acb_user_type = 'S' then
107 /* Single User: Inst Gen must = User Gen */
108
109 Open get_gen_no(p_payroll_action_id, p_acb_inst_code);
110 Fetch get_gen_no into inst_gen;
111
112 /* Set the gen number = 1 if table contains no entries */
113
114 If get_gen_no%notfound then
115 inst_gen := 1;
116 End if;
117
118 Close get_gen_no;
119
120 /* Check that gen no does not exceed 9999, if so, reset to 1 */
121
122 If inst_gen > 9999 then
123 inst_gen := 1;
124 End if;
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
134 )
135 Select
136 p_payroll_action_id
137 , p_acb_inst_code
138 , inst_gen
139 From
140 sys.dual
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
150
151 /* Bureau User: Inst Gen must > User Gen */
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
161 installation gen no may have been reset from 9999 to
162 1 and may then be < user gen no and this cannot be the
163 case with bureau users. */
164
165 If inst_gen <= user_gen then
166 /* add 1 to user gen */
167 inst_gen := user_gen + 1;
168 /* inst gen cannot be > 9999, it is allowed to have inst gen 1
169 when user gen is 9999 */
170 If inst_gen > 9999 then
171 inst_gen := 1;
172 End if;
173 End if;
174
175
176 End if;
177
178 Return (inst_gen);
179
180 End get_acb_inst_gen_num;
181
182 End pay_za_acb_tape;
[Home] [Help]