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