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