[Home] [Help]
PACKAGE BODY: APPS.PQP_FLXDU_COLUMNS_PKG
Source
1 PACKAGE BODY PQP_FLXDU_COLUMNS_PKG AS
2 /* $Header: pqpflxcol.pkb 120.0 2006/04/26 23:52:10 pbhure noship $ */
3
4 -- ===========================================================================
5 -- This procedure loads data into pqp_flxdu_columns table
6 -- If data is already there then update else insert
7 -- ===========================================================================
8 PROCEDURE load_row
9 (x_flxdu_column_id IN NUMBER
10 ,x_flxdu_column_name IN VARCHAR2
11 ,x_flxdu_column_xml_tag IN VARCHAR2
12 ,x_flxdu_column_data_type IN VARCHAR2
13 ,x_flxdu_column_data_length IN VARCHAR2
14 ,x_flxdu_seq_num IN NUMBER
15 ,x_flxdu_group_name IN VARCHAR2
16 ,x_entity_type IN VARCHAR2
17 ,x_required_flag IN VARCHAR2
18 ,x_display_flag IN VARCHAR2
19 ,x_description IN VARCHAR2
20 ,x_flxdu_column_xml_data IN VARCHAR2
21 ,x_object_version_number IN NUMBER) IS
22 BEGIN
23 BEGIN
24 -- update row
25 pqp_flxdu_columns_pkg.update_row
26 (x_flxdu_column_id => x_flxdu_column_id
27 ,x_flxdu_column_name => x_flxdu_column_name
28 ,x_flxdu_column_xml_tag => x_flxdu_column_xml_tag
29 ,x_flxdu_column_data_type => x_flxdu_column_data_type
30 ,x_flxdu_column_data_length => x_flxdu_column_data_length
31 ,x_flxdu_seq_num => x_flxdu_seq_num
32 ,x_flxdu_group_name => x_flxdu_group_name
33 ,x_entity_type => x_entity_type
34 ,x_required_flag => x_required_flag
35 ,x_display_flag => x_display_flag
36 ,x_description => x_description
37 ,x_flxdu_column_xml_data => x_flxdu_column_xml_data
38 ,x_object_version_number => x_object_version_number
39 ,x_created_by => 1
40 ,x_creation_date => sysdate
41 ,x_last_update_date => sysdate
42 ,x_last_updated_by => 1
43 ,x_last_update_login => 0 );
44 EXCEPTION
45 -- when no data found then insert
46 WHEN NO_DATA_FOUND THEN
47 pqp_flxdu_columns_pkg.insert_row
48 (x_flxdu_column_id => x_flxdu_column_id
49 ,x_flxdu_column_name => x_flxdu_column_name
50 ,x_flxdu_column_xml_tag => x_flxdu_column_xml_tag
51 ,x_flxdu_column_data_type => x_flxdu_column_data_type
52 ,x_flxdu_column_data_length => x_flxdu_column_data_length
53 ,x_flxdu_seq_num => x_flxdu_seq_num
54 ,x_flxdu_group_name => x_flxdu_group_name
55 ,x_entity_type => x_entity_type
56 ,x_required_flag => x_required_flag
57 ,x_display_flag => x_display_flag
58 ,x_description => x_description
59 ,x_flxdu_column_xml_data => x_flxdu_column_xml_data
60 ,x_object_version_number => x_object_version_number
61 ,x_created_by => 1
62 ,x_creation_date => sysdate
63 ,x_last_update_date => sysdate
64 ,x_last_updated_by => 1
65 ,x_last_update_login => 0 );
66 END;
67 END load_row;
68
69
70 -- =============================================================================
71 -- This procedure inserts data into pqp_flxdu_columns table
72 -- =============================================================================
73 PROCEDURE insert_row
74 (x_flxdu_column_id IN NUMBER
75 ,x_flxdu_column_name IN VARCHAR2
76 ,x_flxdu_column_xml_tag IN VARCHAR2
77 ,x_flxdu_column_data_type IN VARCHAR2
78 ,x_flxdu_column_data_length IN VARCHAR2
79 ,x_flxdu_seq_num IN NUMBER
80 ,x_flxdu_group_name IN VARCHAR2
81 ,x_entity_type IN VARCHAR2
82 ,x_required_flag IN VARCHAR2
83 ,x_display_flag IN VARCHAR2
84 ,x_description IN VARCHAR2
85 ,x_flxdu_column_xml_data IN VARCHAR2
86 ,x_object_version_number IN NUMBER
87 ,x_created_by IN NUMBER
88 ,x_creation_date IN DATE
89 ,x_last_update_date IN DATE
90 ,x_last_updated_by IN NUMBER
91 ,x_last_update_login IN NUMBER) is
92
93 -- cursor to find no of rows being inserted
94 CURSOR cur_flxdu_columns IS
95 SELECT rowid
96 FROM pqp_flxdu_columns;
97
98 x_rowid rowid;
99
100 BEGIN
101 -- insert into table pqp_flxdu_columns
102 INSERT INTO pqp_flxdu_columns
103 (flxdu_column_id
104 ,flxdu_column_name
105 ,flxdu_column_xml_tag
106 ,flxdu_column_data_type
107 ,flxdu_column_data_length
108 ,flxdu_seq_num
109 ,flxdu_group_name
110 ,entity_type
111 ,required_flag
112 ,display_flag
113 ,description
114 ,flxdu_column_xml_data
115 ,object_version_number
116 ,created_by
117 ,creation_date
118 ,last_update_date
119 ,last_updated_by
120 ,last_update_login)
121 VALUES
122 (x_flxdu_column_id
123 ,x_flxdu_column_name
124 ,x_flxdu_column_xml_tag
125 ,x_flxdu_column_data_type
126 ,x_flxdu_column_data_length
127 ,x_flxdu_seq_num
128 ,x_flxdu_group_name
129 ,x_entity_type
130 ,x_required_flag
131 ,x_display_flag
132 ,x_description
133 ,x_flxdu_column_xml_data
134 ,x_object_version_number
135 ,x_created_by
136 ,x_creation_date
137 ,x_last_update_date
138 ,x_last_updated_by
139 ,x_last_update_login);
140
141 -- if no rows are inserted raise error
142 OPEN cur_flxdu_columns;
143 FETCH cur_flxdu_columns INTO x_rowid;
144 IF (cur_flxdu_columns%notfound) THEN
145 CLOSE cur_flxdu_columns;
146 RAISE NO_DATA_FOUND;
147 END IF;
148 CLOSE cur_flxdu_columns;
149 END insert_row;
150
151
152 -- =============================================================================
153 -- This procedure updates data in pqp_flxdu_columns table
154 -- =============================================================================
155 PROCEDURE update_row
156 (x_flxdu_column_id IN NUMBER
157 ,x_flxdu_column_name IN VARCHAR2
158 ,x_flxdu_column_xml_tag IN VARCHAR2
159 ,x_flxdu_column_data_type IN VARCHAR2
160 ,x_flxdu_column_data_length IN VARCHAR2
161 ,x_flxdu_seq_num IN NUMBER
162 ,x_flxdu_group_name IN VARCHAR2
163 ,x_entity_type IN VARCHAR2
164 ,x_required_flag IN VARCHAR2
165 ,x_display_flag IN VARCHAR2
166 ,x_description IN VARCHAR2
167 ,x_flxdu_column_xml_data IN VARCHAR2
168 ,x_object_version_number IN NUMBER
169 ,x_created_by IN NUMBER
170 ,x_creation_date IN DATE
171 ,x_last_update_date IN DATE
172 ,x_last_updated_by IN NUMBER
173 ,x_last_update_login IN NUMBER) IS
174 BEGIN
175 -- update table pqp_flxdu_columns
176 UPDATE pqp_flxdu_columns
177 SET flxdu_column_id = x_flxdu_column_id
178 ,flxdu_column_name = x_flxdu_column_name
179 ,flxdu_column_xml_tag = x_flxdu_column_xml_tag
180 ,flxdu_column_data_type = x_flxdu_column_data_type
181 ,flxdu_column_data_length = x_flxdu_column_data_length
182 ,flxdu_seq_num = x_flxdu_seq_num
183 ,flxdu_group_name = x_flxdu_group_name
184 ,entity_type = x_entity_type
185 ,required_flag = x_required_flag
186 ,display_flag = x_display_flag
187 ,description = x_description
188 ,flxdu_column_xml_data = x_flxdu_column_xml_data
189 ,object_version_number = x_object_version_number
190 ,created_by = x_created_by
191 ,creation_date = x_creation_date
192 ,last_update_date = x_last_update_date
193 ,last_updated_by = x_last_updated_by
194 ,last_update_login = x_last_update_login
195 WHERE flxdu_column_id = x_flxdu_column_id;
196
197 IF (SQL%NOTFOUND) THEN
198 RAISE no_data_found;
199 END IF;
200 END update_row;
201
202 END pqp_flxdu_columns_pkg;