[Home] [Help]
PACKAGE BODY: APPS.ASG_ITEM_INDEX_PKG
Source
1 PACKAGE BODY ASG_ITEM_INDEX_PKG as
2 /* $Header: asgpindb.pls 120.1.12020000.2 2013/04/09 11:56:31 saradhak ship $ */
3
4 -- HISTORY
5 -- JUN 07, 2012 saradhak HTML5 Changes
6 -- DEC 03, 2002 ytian Modified the update_row not to update the customized object.
7 -- JUN 03, 2002 ytian modified _ID type to varchar2.
8 -- MAY 01, 2002 ytian created.
9
10 procedure insert_row (
11 x_INDEX_ID in VARCHAR2,
12 x_ITEM_ID in VARCHAR2,
13 x_INDEX_NAME in VARCHAR2,
14 x_ENABLED in VARCHAR2,
15 x_PMOD in VARCHAR2,
16 x_INDEX_COLUMNS in VARCHAR2,
17 x_LAST_RELEASE_VERSION in NUMBER,
18 x_CURRENT_RELEASE_VERSION in NUMBER,
19 x_CREATION_DATE in DATE,
20 x_CREATED_BY in NUMBER,
21 x_LAST_UPDATE_DATE in DATE,
22 x_LAST_UPDATED_BY in NUMBER) IS
23
24 l_multi_plat VARCHAR2(1);
25 begin
26
27 insert into ASG_PUB_ITEM_INDEX (
28 INDEX_ID,
29 ITEM_ID,
30 INDEX_NAME,
31 ENABLED,
32 STATUS,
33 PMOD,
34 INDEX_COLUMNS,
35 LAST_RELEASE_VERSION,
36 CURRENT_RELEASE_VERSION,
37 CREATION_DATE,
38 CREATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATED_BY
41 ) values (
42 x_index_id,
43 decode(X_ITEM_ID,FND_API.G_MISS_CHAR, NULL, x_ITEM_ID),
44 decode(X_INDEX_NAME, FND_API.G_MISS_CHAR, NULL, X_INDEX_NAME),
45 decode(X_ENABLED, FND_API.G_MISS_CHAR, NULL, x_ENABLED),
46 'N',
47 decode(X_PMOD, FND_API.G_MISS_CHAR, NULL, X_PMOD),
48 x_INDEX_COLUMNS,
49 -- decode(X_INDEX_COLUMNS,FND_API.G_MISS_CHAR, NULL, x_INDEX_COLUMNS),
50 0,
51 decode(x_CURRENT_release_version,FND_API.G_MISS_NUM, NULL, x_CURRENT_RELEASE_VERSION),
52 decode(X_CREATION_DATE,FND_API.G_MISS_DATE, NULL, x_creation_date),
53 decode(X_CREATED_BY,FND_API.G_MISS_NUM, NULL,x_created_by),
54 decode(X_LAST_UPDATE_DATE,FND_API.G_MISS_DATE, NULL, x_last_update_date),
55 decode(X_LAST_UPDATED_BY,FND_API.G_MISS_NUM, NULL,x_last_updated_by)
56 );
57
58 BEGIN
59 SELECT 'Y' INTO l_multi_plat FROM ASG_PUB_ITEM WHERE ITEM_ID=x_ITEM_ID AND HTML5_OFFLINE='Y';
60 EXECUTE IMMEDIATE 'BEGIN csm_html5_pkg.set_indx_ref(:1); END;' USING X_INDEX_ID;
61 EXCEPTION
62 When Others then
63 NULL;
64 END;
65
66 end insert_row;
67
68 procedure update_row (
69 x_INDEX_ID in VARCHAR2,
70 x_ITEM_ID in VARCHAR2,
71 x_INDEX_NAME in VARCHAR2,
72 x_ENABLED in VARCHAR2,
73 x_PMOD in VARCHAR2,
74 x_INDEX_COLUMNS in VARCHAR2,
75 x_LAST_RELEASE_VERSION in NUMBER,
76 x_CURRENT_RELEASE_VERSION in NUMBER,
77 x_CREATION_DATE in DATE,
78 x_CREATED_BY in NUMBER,
79 x_LAST_UPDATE_DATE in DATE,
80 x_LAST_UPDATED_BY in NUMBER) IS
81
82 l_custom_flag varchar2(1) ;
83 l_item_id varchar2(30);
84
85 l_multi_plat VARCHAR2(1);
86 l_last_release_version NUMBER;
87 begin
88
89 select a.item_id,a.LAST_RELEASE_VERSION,b.HTML5_OFFLINE
90 into l_item_id,l_last_release_version,l_multi_plat
91 from asg_pub_item_index a, asg_pub_item b
92 where index_id = x_index_id
93 and a.item_id=b.item_id;
94
95 BEGIN
96 select nvl(p.custom,'N')
97 into l_custom_flag
98 from asg_pub p, asg_pub_item i
99 where p.pub_id = i.pub_name
100 and i.item_id = l_ITEM_ID;
101 EXCEPTION
102 when no_data_found then
103 l_custom_flag := 'N';
104 END;
105
106 IF (l_custom_flag <>'Y') THEN
107 update asg_pub_item_index set
108 INDEX_ID = X_INDEX_ID,
109 ITEM_ID = X_ITEM_ID,
110 INDEX_NAME = X_INDEX_NAME,
111 ENABLED = X_ENABLED,
112 PMOD = X_PMOD,
113 INDEX_COLUMNS = X_INDEX_COLUMNS,
114 -- LAST_RELEASE_VERSION = X_LAST_RELEASE_VERSION,
115 CURRENT_RELEASE_VERSION = X_CURRENT_RELEASE_VERSION,
116 CREATION_DATE = X_CREATION_DATE,
117 CREATED_BY = X_CREATED_BY,
118 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
119 LAST_UPDATED_BY = X_LAST_UPDATED_BY
120 where INDEX_ID = X_INDEX_ID;
121 END IF;
122
123 if (sql%notfound) then
124 raise no_data_found;
125 else
126 IF (l_multi_plat='Y') AND (l_last_release_version < X_CURRENT_RELEASE_VERSION) THEN
127 BEGIN
128 EXECUTE IMMEDIATE 'BEGIN csm_html5_pkg.set_indx_ref(:1); END;' USING X_INDEX_ID;
129 EXCEPTION
130 When Others then
131 NULL;
132 END;
133 END IF;
134 end if;
135 END UPDATE_ROW;
136
137
138 procedure load_row (
139 x_INDEX_ID in VARCHAR2,
140 x_ITEM_ID in VARCHAR2,
141 x_INDEX_NAME in VARCHAR2,
142 x_ENABLED in VARCHAR2,
143 x_PMOD in VARCHAR2,
144 x_INDEX_COLUMNS in VARCHAR2,
145 x_LAST_RELEASE_VERSION in NUMBER,
146 x_CURRENT_RELEASE_VERSION in NUMBER,
147 x_CREATION_DATE in DATE,
148 x_CREATED_BY in NUMBER,
149 x_LAST_UPDATE_DATE in DATE,
150 x_LAST_UPDATED_BY in NUMBER,
151 p_owner in VARCHAR2) IS
152
153 l_user_id number := 0;
154
155 BEGIN
156
157
158 if (p_owner = 'SEED') then
159 l_user_id := 1;
160 end if;
161
162 asg_item_index_pkg.UPDATE_ROW (
163 X_INDEX_ID => x_INDEX_ID,
164 X_ITEM_ID => x_ITEM_ID,
165 X_INDEX_NAME => x_INDEX_NAME,
166 X_ENABLED => x_ENABLED,
167 X_PMOD => x_PMOD,
168 X_INDEX_COLUMNS => x_INDEX_COLUMNS,
169 X_LAST_RELEASE_VERSION => x_LAST_release_version,
170 X_CURRENT_RELEASE_VERSION => x_CURRENT_release_version,
171 X_CREATION_DATE => X_CREATION_DATE,
172 X_CREATED_BY => X_CREATED_BY,
173 X_LAST_UPDATE_DATE => sysdate,
174 X_LAST_UPDATED_BY => l_user_id);
175
176 EXCEPTION
177 WHEN NO_DATA_FOUND THEN
178
179 asg_item_index_pkg.insert_row (
180 X_INDEX_ID => x_INDEX_ID,
181 X_ITEM_ID => x_ITEM_ID,
182 X_INDEX_NAME => x_INDEX_NAME,
183 X_ENABLED => x_ENABLED,
184 X_PMOD => x_PMOD,
185 X_INDEX_COLUMNS => x_INDEX_COLUMNS,
186 X_LAST_RELEASE_VERSION => x_LAST_release_version,
187 X_CURRENT_RELEASE_VERSION => x_CURRENT_release_version,
188 X_CREATION_DATE => sysdate,
189 X_CREATED_BY => l_user_id,
190 X_LAST_UPDATE_DATE => sysdate,
191 X_LAST_UPDATED_BY => l_user_id);
192
193 END load_row;
194
195 END ASG_ITEM_INDEX_PKG;