[Home] [Help]
PACKAGE BODY: APPS.GCS_FORMULA_VARIABLES_PKG
Source
1 PACKAGE BODY GCS_FORMULA_VARIABLES_PKG AS
2 /* $Header: gcserfvb.pls 120.1 2005/10/30 05:18:19 appldev noship $ */
3 --
4 -- Package
5 -- gcs_formula_variables_pkg
6 -- Purpose
7 -- Package procedures for Lexical Mapping Structures
8 -- History
9 -- 27-APR-04 J Huang Created
10 --
11
12 PROCEDURE Insert_Row( row_id IN OUT NOCOPY VARCHAR2,
13 user_variable_name VARCHAR2,
14 rule_type_code VARCHAR2,
15 sql_statement_value NUMBER,
16 compiled_variable_name VARCHAR2,
17 sql_expression VARCHAR2,
18 last_update_date DATE,
19 last_updated_by NUMBER,
20 last_update_login NUMBER,
21 creation_date DATE,
22 created_by NUMBER) IS
23 CURSOR var_row IS
24 SELECT rowid
25 FROM gcs_formula_variables fv
26 WHERE fv.user_variable_name = insert_row.user_variable_name;
27 BEGIN
28 IF user_variable_name IS NULL THEN
29 raise no_data_found;
30 END IF;
31
32 INSERT INTO gcs_formula_variables( user_variable_name,
33 rule_type_code,
34 sql_statement_value,
35 compiled_variable_name,
36 sql_expression,
37 last_update_date,
38 last_updated_by,
39 last_update_login,
40 creation_date,
41 created_by)
42 SELECT user_variable_name,
43 rule_type_code,
44 sql_statement_value,
45 compiled_variable_name,
46 sql_expression,
47 last_update_date,
48 last_updated_by,
49 last_update_login,
50 creation_date,
51 created_by
52 FROM dual
53 WHERE NOT EXISTS
54 (SELECT 1
55 FROM gcs_formula_variables fv
56 WHERE fv.user_variable_name = insert_row.user_variable_name);
57
58 OPEN var_row;
59 FETCH var_row INTO row_id;
60 IF var_row%NOTFOUND THEN
61 CLOSE var_row;
62 raise no_data_found;
63 END IF;
64 CLOSE var_row;
65 END Insert_Row;
66
67 PROCEDURE Update_Row( user_variable_name VARCHAR2,
68 rule_type_code VARCHAR2,
69 sql_statement_value NUMBER,
70 compiled_variable_name VARCHAR2,
71 sql_expression VARCHAR2,
72 last_update_date DATE,
73 last_updated_by NUMBER,
74 last_update_login NUMBER) IS
75 BEGIN
76 UPDATE gcs_formula_variables fv
77 SET rule_type_code = update_row.rule_type_code,
78 sql_statement_value = update_row.sql_statement_value,
79 compiled_variable_name = update_row.compiled_variable_name,
80 sql_expression = update_row.sql_expression,
81 last_update_date = update_row.last_update_date,
82 last_updated_by = update_row.last_updated_by,
83 last_update_login = update_row.last_update_login
84 WHERE fv.user_variable_name = update_row.user_variable_name;
85
86 IF SQL%NOTFOUND THEN
87 raise no_data_found;
88 END IF;
89 END Update_Row;
90
91 PROCEDURE Load_Row( user_variable_name VARCHAR2,
92 owner VARCHAR2,
93 last_update_date VARCHAR2,
94 rule_type_code VARCHAR2,
95 sql_statement_value NUMBER,
96 compiled_variable_name VARCHAR2,
97 sql_expression VARCHAR2,
98 custom_mode VARCHAR2)IS
99 row_id VARCHAR2(64);
100 f_luby NUMBER; -- entity owner in file
101 f_ludate DATE; -- entity update date in file
102 db_luby NUMBER; -- entity owner in db
103 db_ludate DATE; -- entity update date in db
104 BEGIN
105 -- Get last updated information from the loader data file
106 f_luby := fnd_load_util.owner_id(owner);
107 f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
108
109 BEGIN
110 SELECT fv.last_updated_by, fv.last_update_date
111 INTO db_luby, db_ludate
112 FROM GCS_FORMULA_VARIABLES fv
113 WHERE fv.user_variable_name = load_row.user_variable_name;
114
115 -- Test for customization information
116 IF fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate,
117 custom_mode) THEN
118 update_row( user_variable_name => user_variable_name,
119 rule_type_code => rule_type_code,
120 sql_statement_value => sql_statement_value,
121 compiled_variable_name => compiled_variable_name,
122 sql_expression => sql_expression,
123 last_update_date => f_ludate,
124 last_updated_by => f_luby,
125 last_update_login => 0);
126 END IF;
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 insert_row( row_id => row_id,
130 user_variable_name => user_variable_name,
131 rule_type_code => rule_type_code,
132 sql_statement_value => sql_statement_value,
133 compiled_variable_name => compiled_variable_name,
134 sql_expression => sql_expression,
135 last_update_date => f_ludate,
136 last_updated_by => f_luby,
137 last_update_login => 0,
138 creation_date => f_ludate,
139 created_by => f_luby);
140 END;
141 END Load_Row;
142
143 PROCEDURE Translate_Row( user_variable_name VARCHAR2,
144 rule_type_code VARCHAR2,
145 owner VARCHAR2,
146 last_update_date VARCHAR2,
147 custom_mode VARCHAR2) IS
148 f_luby NUMBER; -- entity owner in file
149 f_ludate DATE; -- entity update date in file
150 db_luby NUMBER; -- entity owner in db
151 db_ludate DATE; -- entity update date in db
152 BEGIN
153 -- Get last updated information from the loader data file
154 f_luby := fnd_load_util.owner_id(owner);
155 f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
156
157 BEGIN
158 SELECT fv.last_updated_by, fv.last_update_date
159 INTO db_luby, db_ludate
160 FROM GCS_FORMULA_VARIABLES fv
161 WHERE fv.user_variable_name = translate_row.user_variable_name;
162
163 -- Test for customization information
164 IF fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate,
165 custom_mode) THEN
166 UPDATE gcs_formula_variables fv
167 SET last_update_date = f_ludate,
168 last_updated_by = f_luby,
169 last_update_login = 0
170 WHERE fv.user_variable_name = translate_row.user_variable_name;
171 END IF;
172 EXCEPTION
173 WHEN NO_DATA_FOUND THEN
174 null;
175 END;
176 END Translate_Row;
177
178 END GCS_FORMULA_VARIABLES_PKG;