1 package body HR_NAVIGATION_NODE_USAGES_PKG as
2 /* $Header: hrnvnlct.pkb 115.3 2004/01/08 01:24:36 adudekul noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_NAV_NODE_USAGE_ID in NUMBER,
6 X_WORKFLOW_ID in NUMBER,
7 X_NAV_NODE_ID in NUMBER,
8 X_TOP_NODE in VARCHAR2
9 ) is
10 cursor C is select ROWID from HR_NAVIGATION_NODE_USAGES
11 where NAV_NODE_USAGE_ID = X_NAV_NODE_USAGE_ID
12 ;
13 begin
14 insert into HR_NAVIGATION_NODE_USAGES (
15 NAV_NODE_USAGE_ID,
16 WORKFLOW_ID,
17 NAV_NODE_ID,
18 TOP_NODE
19 ) values (
20 X_NAV_NODE_USAGE_ID,
21 X_WORKFLOW_ID,
22 X_NAV_NODE_ID,
23 X_TOP_NODE);
24
25 open c;
26 fetch c into X_ROWID;
27 if (c%notfound) then
28 close c;
29 raise no_data_found;
30 end if;
31 close c;
32
33 end INSERT_ROW;
34
35 procedure LOCK_ROW (
36 X_NAV_NODE_USAGE_ID in NUMBER,
37 X_WORKFLOW_ID in NUMBER,
38 X_NAV_NODE_ID in NUMBER,
39 X_TOP_NODE in VARCHAR2
40 ) is
41 cursor c1 is select
42 WORKFLOW_ID,
43 NAV_NODE_ID,
44 TOP_NODE
45 from HR_NAVIGATION_NODE_USAGES
46 where NAV_NODE_USAGE_ID = X_NAV_NODE_USAGE_ID
47 for update of NAV_NODE_USAGE_ID nowait;
48 begin
49 for tlinfo in c1 loop
50 if ( (tlinfo.TOP_NODE = X_TOP_NODE)
51 AND (tlinfo.WORKFLOW_ID = X_WORKFLOW_ID)
52 AND (tlinfo.NAV_NODE_ID = X_NAV_NODE_ID)
53 ) then
54 null;
55 else
56 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
57 app_exception.raise_exception;
58 end if;
59 end loop;
60 return;
61 end LOCK_ROW;
62
63 procedure UPDATE_ROW (
64 X_NAV_NODE_USAGE_ID in NUMBER,
65 X_WORKFLOW_ID in NUMBER,
66 X_NAV_NODE_ID in NUMBER,
67 X_TOP_NODE in VARCHAR2
68 ) is
69 begin
70 update HR_NAVIGATION_NODE_USAGES set
71 WORKFLOW_ID = X_WORKFLOW_ID,
72 NAV_NODE_ID = X_NAV_NODE_ID,
73 TOP_NODE = X_TOP_NODE
74 where NAV_NODE_USAGE_ID = X_NAV_NODE_USAGE_ID;
75
76 if (sql%notfound) then
77 raise no_data_found;
78 end if;
79 end UPDATE_ROW;
80
81 procedure DELETE_ROW (
82 X_NAV_NODE_USAGE_ID in NUMBER
83 ) is
84 begin
85 delete from HR_NAVIGATION_NODE_USAGES
86 where NAV_NODE_USAGE_ID = X_NAV_NODE_USAGE_ID;
87
88 if (sql%notfound) then
89 raise no_data_found;
90 end if;
91
92 end DELETE_ROW;
93
94 procedure LOAD_ROW(
95 X_WORKFLOW_NAME in VARCHAR2,
96 X_NODE_NAME in VARCHAR2,
97 X_TOP_NODE in VARCHAR2
98 ) is
99 X_NAV_NODE_USAGE_ID NUMBER;
100 X_WORKFLOW_ID NUMBER;
101 X_NAV_NODE_ID NUMBER;
102 X_ROWID VARCHAR2(30);
103 begin
104
105 if hr_workflows_pkg.g_load_taskflow <> 'N' then
106
107 if X_WORKFLOW_NAME is not null then
108 select WORKFLOW_ID
109 into X_WORKFLOW_ID
110 from HR_WORKFLOWS
111 where WORKFLOW_NAME = X_WORKFLOW_NAME;
112 else
113 X_WORKFLOW_ID := null;
114 end if;
115
116 if X_NODE_NAME is not null then
117 select NAV_NODE_ID
118 into X_NAV_NODE_ID
119 from HR_NAVIGATION_NODES
120 where NAME = X_NODE_NAME;
121 else
122 X_NAV_NODE_ID := null;
123 end if;
124
125 begin
126 select NAV_NODE_USAGE_ID
127 into X_NAV_NODE_USAGE_ID
128 from HR_NAVIGATION_NODE_USAGES
129 where WORKFLOW_ID = X_WORKFLOW_ID
130 and NAV_NODE_ID = X_NAV_NODE_ID
131 and TOP_NODE = X_TOP_NODE;
132 exception
133 when no_data_found then
134 select HR_NAVIGATION_NODE_USAGES_S.NEXTVAL
135 into X_NAV_NODE_USAGE_ID
136 from dual;
137 --
138 -- Fix for bug 3274423 starts here.
139 -- Before updating the record, compare the database row with the row in ldt file.
140 -- The SELECT statement above is checking for all the columns in the table. So no
141 -- need for explicit check. Therefore if not found then insert.
142 --
143 /* end;
144
145 begin
146 UPDATE_ROW(
147 X_NAV_NODE_USAGE_ID,
148 X_WORKFLOW_ID,
149 X_NAV_NODE_ID,
150 X_TOP_NODE
151 );
152 exception
153 when no_data_found then */
154
155 --
156 -- Fix for bug 3274423 ends here.
157 --
158 INSERT_ROW(
159 X_ROWID,
160 X_NAV_NODE_USAGE_ID,
161 X_WORKFLOW_ID,
162 X_NAV_NODE_ID,
163 X_TOP_NODE
164 );
165 end;
166
167 end if;
168
169 end LOAD_ROW;
170
171 end HR_NAVIGATION_NODE_USAGES_PKG;