[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_BF_ASSOC_PKG
Source
1 package body FND_OAM_BF_ASSOC_PKG as
2 /* $Header: AFOAMFAB.pls 120.1 2005/07/02 03:03:10 appldev noship $ */
3 -- Module name for this package
4 MODULE constant varchar2(200) := 'fnd.plsql.FND_OAM_BF_ASSOC_PKG';
5
6 --
7 -- Name
8 -- check_loop
9 --
10 -- Purpose
11 -- Checks if adding the given subflow as a child of the given
12 -- parent flow could cause a potential loop. An exception is
13 -- raised if a possible loop could result
14 --
15 -- Input Arguments
16 -- x_biz_flow_parent_key - parent flow key
17 -- x_biz_flow_child_key - key for the flow to be added as child.
18 --
19 -- Output Arguments
20 --
21 -- Notes:
22 --
23 procedure check_loop (
24 X_BIZ_FLOW_PARENT_KEY in VARCHAR2,
25 X_BIZ_FLOW_CHILD_KEY in VARCHAR2)
26 is
27 v_ct_ancestors number := 0;
28 v_msg varchar2(1024) := '';
29 begin
30 select count(*) into v_ct_ancestors
31 from fnd_oam_bf_assoc a
32 where a.biz_flow_parent_key = X_BIZ_FLOW_CHILD_KEY
33 start with a.biz_flow_child_key = X_BIZ_FLOW_PARENT_KEY
34 connect by prior a.biz_flow_parent_key = a.biz_flow_child_key;
35
36 if v_ct_ancestors > 0 or X_BIZ_FLOW_PARENT_KEY = X_BIZ_FLOW_CHILD_KEY then
37 v_msg := 'Cannot add ' || X_BIZ_FLOW_CHILD_KEY || ' as a child of ' || X_BIZ_FLOW_PARENT_KEY || ' as ' || X_BIZ_FLOW_CHILD_KEY || ' is already an ancestor of ' || X_BIZ_FLOW_PARENT_KEY || '.';
38 if (fnd_log.level_error >= fnd_log.g_current_runtime_level) then
39 fnd_log.string(log_level=>fnd_log.level_error,
40 module=>MODULE||'.raise_alert',
41 message=>v_msg);
42 end if;
43 raise_application_error(-20500, v_msg);
44 end if;
45 exception
46 when no_data_found then
47 null;
48 when others then
49 raise;
50 end check_loop;
51
52 --
53 -- Name
54 -- load_row
55 --
56 -- Purpose
57 -- Loads the association between a parent flow and a child flow
58 -- into the database. This procedure will be called by the
59 -- OAM Business Flows Definition Loader program.
60 --
61 -- Input Arguments
62 -- x_biz_flow_parent_key - parent flow key. Pass in ROOT_KEY for
63 -- flows that are top level that dont have a parent.
64 -- x_biz_flow_child_key - child flow key.
65 -- x_monitored_flag - Y/N - whether the child flow is active in
66 -- context of its parent.
67 -- x_owner - owner e.g. ORACLE
68 -- Output Arguments
69 --
70 -- Notes:
71 --
72 --
73 procedure LOAD_ROW (
74 X_BIZ_FLOW_PARENT_KEY in VARCHAR2,
75 X_BIZ_FLOW_CHILD_KEY in VARCHAR2,
76 X_MONITORED_FLAG in VARCHAR2,
77 X_DISPLAY_ORDER in NUMBER,
78 X_OWNER in VARCHAR2)
79 is
80 begin
81 fnd_oam_bf_assoc_pkg.LOAD_ROW (
82 X_BIZ_FLOW_PARENT_KEY => X_BIZ_FLOW_PARENT_KEY,
83 X_BIZ_FLOW_CHILD_KEY => X_BIZ_FLOW_CHILD_KEY,
84 X_MONITORED_FLAG => X_MONITORED_FLAG,
85 X_DISPLAY_ORDER => X_DISPLAY_ORDER,
86 X_OWNER => X_OWNER,
87 x_custom_mode => '',
88 x_last_update_date => '');
89 end LOAD_ROW;
90
91 procedure LOAD_ROW (
92 X_BIZ_FLOW_PARENT_KEY in VARCHAR2,
93 X_BIZ_FLOW_CHILD_KEY in VARCHAR2,
94 X_MONITORED_FLAG in VARCHAR2,
95 X_DISPLAY_ORDER in NUMBER,
96 X_OWNER in VARCHAR2,
97 x_custom_mode in varchar2,
98 x_last_update_date in varchar2)
99 is
100 row_id varchar2(64);
101 f_luby number; -- entity owner in file
102 f_ludate date; -- entity update date in file
103 db_luby number; -- entity owner in db
104 db_ludate date; -- entity update date in db
105 begin
106 -- Translate owner to file_last_updated_by
107 f_luby := fnd_load_util.owner_id(x_owner);
108
109 -- Translate char last_update_date to date
110 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
111
112 begin
113 select LAST_UPDATED_BY, LAST_UPDATE_DATE
114 into db_luby, db_ludate
115 from fnd_oam_bf_assoc
116 where biz_flow_parent_key = X_BIZ_FLOW_PARENT_KEY
117 and biz_flow_child_key = X_BIZ_FLOW_CHILD_KEY;
118
119 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
120 db_ludate, X_CUSTOM_MODE)) then
121 fnd_oam_bf_assoc_pkg.UPDATE_ROW (
122 X_BIZ_FLOW_PARENT_KEY => X_BIZ_FLOW_PARENT_KEY,
123 X_BIZ_FLOW_CHILD_KEY => X_BIZ_FLOW_CHILD_KEY,
124 X_MONITORED_FLAG => X_MONITORED_FLAG,
125 X_DISPLAY_ORDER => X_DISPLAY_ORDER,
126 X_LAST_UPDATE_DATE => f_ludate,
127 X_LAST_UPDATED_BY => f_luby,
128 X_LAST_UPDATE_LOGIN => 0 );
129 end if;
130 exception
131 when NO_DATA_FOUND then
132
133 fnd_oam_bf_assoc_pkg.INSERT_ROW (
134 X_ROWID => row_id,
135 X_BIZ_FLOW_PARENT_KEY => X_BIZ_FLOW_PARENT_KEY,
136 X_BIZ_FLOW_CHILD_KEY => X_BIZ_FLOW_CHILD_KEY,
137 X_MONITORED_FLAG => X_MONITORED_FLAG,
138 X_DISPLAY_ORDER => X_DISPLAY_ORDER,
139 X_CREATION_DATE => f_ludate,
140 X_CREATED_BY => f_luby,
141 X_LAST_UPDATE_DATE => f_ludate,
142 X_LAST_UPDATED_BY => f_luby,
143 X_LAST_UPDATE_LOGIN => 0 );
144 end;
145 END LOAD_ROW;
146
147 procedure INSERT_ROW (
148 X_ROWID in out nocopy VARCHAR2,
149 X_BIZ_FLOW_PARENT_KEY in VARCHAR2,
150 X_BIZ_FLOW_CHILD_KEY in VARCHAR2,
151 X_MONITORED_FLAG in VARCHAR2,
152 X_DISPLAY_ORDER in NUMBER,
153 X_CREATION_DATE in DATE,
154 X_CREATED_BY in NUMBER,
155 X_LAST_UPDATE_DATE in DATE,
156 X_LAST_UPDATED_BY in NUMBER,
157 X_LAST_UPDATE_LOGIN in NUMBER)
158 is
159 cursor C is select ROWID from FND_OAM_BF_ASSOC
160 where BIZ_FLOW_PARENT_KEY = X_BIZ_FLOW_PARENT_KEY
161 and biz_flow_child_key = x_biz_flow_child_key;
162 begin
163 check_loop(
164 X_BIZ_FLOW_PARENT_KEY,
165 X_BIZ_FLOW_CHILD_KEY);
166
167 insert into fnd_oam_bf_assoc (
168 BIZ_FLOW_PARENT_KEY,
169 BIZ_FLOW_CHILD_KEY,
170 MONITORED_FLAG,
171 DISPLAY_ORDER,
172 CREATION_DATE,
173 CREATED_BY,
174 LAST_UPDATE_DATE,
175 LAST_UPDATED_BY,
176 LAST_UPDATE_LOGIN
177 ) values (
178 X_BIZ_FLOW_PARENT_KEY,
179 X_BIZ_FLOW_CHILD_KEY,
180 X_MONITORED_FLAG,
181 X_DISPLAY_ORDER,
182 X_CREATION_DATE,
183 X_CREATED_BY,
184 X_LAST_UPDATE_DATE,
185 X_LAST_UPDATED_BY,
186 X_LAST_UPDATE_LOGIN
187 );
188
189 open c;
190 fetch c into X_ROWID;
191 if (c%notfound) then
192 close c;
193 raise no_data_found;
194 end if;
195 close c;
196 end INSERT_ROW;
197
198 procedure UPDATE_ROW (
199 X_BIZ_FLOW_PARENT_KEY in VARCHAR2,
200 X_BIZ_FLOW_CHILD_KEY in VARCHAR2,
201 X_MONITORED_FLAG in VARCHAR2,
202 X_DISPLAY_ORDER in NUMBER,
203 X_LAST_UPDATE_DATE in DATE,
204 X_LAST_UPDATED_BY in NUMBER,
205 X_LAST_UPDATE_LOGIN in NUMBER
206 ) is
207 begin
208 update FND_OAM_BF_ASSOC set
209 monitored_flag = x_monitored_flag,
210 display_order = x_display_order,
211 last_update_date = x_last_update_date,
212 last_updated_by = x_last_updated_by,
213 last_update_login = x_last_update_login
214 where biz_flow_parent_key = x_biz_flow_parent_key
215 and biz_flow_child_key = x_biz_flow_child_key;
216
217 if (sql%notfound) then
218 raise no_data_found;
219 end if;
220 end UPDATE_ROW;
221
222 procedure DELETE_ROW (
223 X_BIZ_FLOW_PARENT_KEY in VARCHAR2,
224 X_BIZ_FLOW_CHILD_KEY in VARCHAR2
225 ) is
226 begin
227 delete from FND_OAM_BF_ASSOC
228 where biz_flow_parent_key = x_biz_flow_parent_key
229 and biz_flow_child_key = x_biz_flow_child_key;
230
231 if (sql%notfound) then
232 raise no_data_found;
233 end if;
234 end DELETE_ROW;
235
236 end FND_OAM_BF_ASSOC_PKG;