[Home] [Help]
PACKAGE BODY: APPS.PAY_USER_COLUMN_INSTANCES_PKG
Source
1 package body PAY_USER_COLUMN_INSTANCES_PKG as
2 /* $Header: pyusi01t.pkb 115.0 99/07/17 06:44:37 porting ship $ */
3 --
4 --
5 procedure insert_row(p_rowid in out varchar2,
6 p_user_column_instance_id in out number,
7 p_effective_start_date in date,
8 p_effective_end_date in date,
9 p_user_row_id in number,
10 p_user_column_id in number,
11 p_business_group_id in number,
12 p_legislation_code in varchar2,
13 p_legislation_subgroup in varchar2,
14 p_value in varchar2 ) is
15 --
16 cursor c1 is
17 select pay_user_column_instances_s.nextval
18 from sys.dual ;
19
20 cursor c2 is
21 select rowid
22 from pay_user_column_instances_f
23 where user_column_instance_id = p_user_column_instance_id ;
24 --
25 begin
26 --
27 open c1 ;
28 fetch c1 into p_user_column_instance_id ;
29 close c1 ;
30
31 insert into PAY_USER_COLUMN_INSTANCES_F
32 ( USER_COLUMN_INSTANCE_ID,
33 EFFECTIVE_START_DATE,
34 EFFECTIVE_END_DATE,
35 USER_ROW_ID,
36 USER_COLUMN_ID,
37 BUSINESS_GROUP_ID,
38 LEGISLATION_CODE,
39 LEGISLATION_SUBGROUP,
40 VALUE )
41 values ( p_user_column_instance_id ,
42 p_effective_start_date ,
43 p_effective_end_date ,
44 p_user_row_id ,
45 p_user_column_id ,
46 p_business_group_id ,
47 p_legislation_code ,
48 p_legislation_subgroup ,
49 p_value ) ;
50
51 --
52 open c2 ;
53 fetch c2 into p_rowid ;
54 close c2 ;
55 --
56 end insert_row ;
57 --
58 procedure update_row(p_rowid in varchar2,
59 p_user_column_instance_id in number,
60 p_effective_start_date in date,
61 p_effective_end_date in date,
62 p_user_row_id in number,
63 p_user_column_id in number,
64 p_business_group_id in number,
65 p_legislation_code in varchar2,
66 p_legislation_subgroup in varchar2,
67 p_value in varchar2 ) is
68 begin
69 --
70 update PAY_USER_COLUMN_INSTANCES_F
71 set USER_COLUMN_INSTANCE_ID = p_user_column_instance_id,
72 EFFECTIVE_START_DATE = p_effective_start_date ,
73 EFFECTIVE_END_DATE = p_effective_end_date ,
74 USER_ROW_ID = p_user_row_id,
75 USER_COLUMN_ID = p_user_column_id,
76 BUSINESS_GROUP_ID = p_business_group_id,
77 LEGISLATION_CODE = p_legislation_code ,
78 LEGISLATION_SUBGROUP = p_legislation_subgroup,
79 VALUE = p_value
80 where ROWID = p_rowid;
81 --
82 end update_row;
83 --
84 procedure delete_row(p_rowid in varchar2) is
85 --
86 begin
87 --
88 delete from PAY_USER_COLUMN_INSTANCES_F
89 where ROWID = p_rowid;
90 --
91 end delete_row;
92 --
93 procedure lock_row (p_rowid in varchar2,
94 p_user_column_instance_id in number,
95 p_effective_start_date in date,
96 p_effective_end_date in date,
97 p_user_row_id in number,
98 p_user_column_id in number,
99 p_business_group_id in number,
100 p_legislation_code in varchar2,
101 p_legislation_subgroup in varchar2,
102 p_value in varchar2 ) is
103 --
104 cursor C is select *
105 from PAY_USER_COLUMN_INSTANCES_F
106 where rowid = p_rowid
107 for update of USER_COLUMN_INSTANCE_ID NOWAIT ;
108 --
109 rowinfo C%rowtype;
110 --
111 begin
112 --
113 open C;
114 fetch C into rowinfo;
115 close C;
116 --
117 rowinfo.legislation_code := rtrim(rowinfo.legislation_code);
118 rowinfo.legislation_subgroup := rtrim(rowinfo.legislation_subgroup);
119 rowinfo.value := rtrim(rowinfo.value);
120 --
121 if ( (rowinfo.USER_COLUMN_INSTANCE_ID = p_user_column_instance_id )
122 or (rowinfo.USER_COLUMN_INSTANCE_ID is null and p_user_column_instance_id
123 is null ))
124 and( (rowinfo.EFFECTIVE_START_DATE = p_effective_start_date)
125 or (rowinfo.EFFECTIVE_START_DATE is null and p_effective_start_date
126 is null ))
127 and( (rowinfo.EFFECTIVE_END_DATE = p_effective_end_date)
128 or (rowinfo.EFFECTIVE_END_DATE is null and p_effective_end_date
129 is null ))
130 and( (rowinfo.USER_ROW_ID = p_user_row_id )
131 or (rowinfo.USER_ROW_ID is null and p_user_row_id is null ))
132 and( (rowinfo.USER_COLUMN_ID = p_user_column_id )
133 or (rowinfo.USER_COLUMN_ID is null and p_user_column_id is null ))
134 and( (rowinfo.BUSINESS_GROUP_ID = p_business_group_id )
135 or (rowinfo.BUSINESS_GROUP_ID is null and p_business_group_id
136 is null ))
137 and( (rowinfo.LEGISLATION_CODE = p_legislation_code )
138 or (rowinfo.LEGISLATION_CODE is null and p_legislation_code
139 is null ))
140 and( (rowinfo.LEGISLATION_SUBGROUP = p_legislation_subgroup )
141 or (rowinfo.LEGISLATION_SUBGROUP is null and p_legislation_subgroup
142 is null ))
143 and( (rowinfo.VALUE = p_value )
144 or (rowinfo.VALUE is null and p_value is null ))
145 then
146 return ;
147 else
148 fnd_message.set_name( 'FND' , 'FORM_RECORD_CHANGED');
149 app_exception.raise_exception ;
150 end if;
151 end lock_row;
152 --
153 function latest_end_date ( p_user_row_id in number ) return date is
154 cursor c1 is
155 select max(effective_end_date)
156 from pay_user_rows_f
157 where user_row_id = p_user_row_id ;
158 l_return_value date := null ;
159 begin
160 --
161 open c1 ;
162 fetch c1 into l_return_value ;
163 close c1 ;
164 --
165 return ( l_return_value ) ;
166 end ;
167 --
168 end PAY_USER_COLUMN_INSTANCES_PKG ;