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