[Home] [Help]
PACKAGE BODY: APPS.CS_KB_ASSOC_PKG
Source
1 PACKAGE BODY CS_KB_ASSOC_PKG AS
2 /* $Header: cskbasb.pls 115.9 2003/08/28 18:03:57 mkettle noship $ */
3 /*======================================================================+
4 | Copyright (c) 1999 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 | Package Name : CS_KB_ASSOC_PKG |
9 | Package Spec File Name : cskbass.pls |
10 | Package Body File Name : cskbasb.pls |
11 | |
12 | PURPOSE: |
13 | SOLUTION ASSOCIATIONS PACKAGE |
14 | |
15 | NOTES |
16 | |
17 | History |
18 | 5-16-2001 JAWSMITH Created |
19 | 03.27.2003 BAYU Fix bug 2869963 |
20 | Remove hard-coded owner prefix: "CS." |
21 | 09-Jul-2003 MKETTLE Add Insert into SET_CATEGORIES in clone_link |
22 +======================================================================*/
23
24
25 /****************************************************
26 -------------FUNCTIONS--------------------------------------------------------------------------
27 ****************************************************/
28 function Clone_Link(
29 P_SET_SOURCE_ID in NUMBER,
30 P_SET_TARGET_ID in NUMBER
31 )return number IS
32 l_count number;
33 cursor plat_link_csr is
34 select * from cs_kb_set_platforms
35 where set_id = p_set_source_id;
36 cursor prod_link_csr is
37 select * from cs_kb_set_products
38 where set_id = p_set_source_id;
39 cursor cat_link_csr is
40 select * from cs_kb_set_categories
41 where set_id = p_set_source_id;
42
43 BEGIN
44
45 for rec_plat_link in plat_link_csr loop
46
47 insert into CS_KB_SET_PLATFORMS (
48 SET_ID,
49 PLATFORM_ID,
50 PLATFORM_ORG_ID,
51 CREATION_DATE,
52 CREATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATED_BY,
55 LAST_UPDATE_LOGIN
56 ) values (
57 P_SET_TARGET_ID,
58 REC_PLAT_LINK.PLATFORM_ID,
59 REC_PLAT_LINK.PLATFORM_ORG_ID,
60 REC_PLAT_LINK.CREATION_DATE,
61 REC_PLAT_LINK.CREATED_BY,
62 REC_PLAT_LINK.LAST_UPDATE_DATE,
63 REC_PLAT_LINK.LAST_UPDATED_BY,
64 REC_PLAT_LINK.LAST_UPDATE_LOGIN
65 );
66
67
68 end loop;
69
70 for rec_prod_link in prod_link_csr loop
71
72 insert into CS_KB_SET_PRODUCTS (
73 SET_ID,
74 PRODUCT_ID,
75 PRODUCT_ORG_ID,
76 CREATION_DATE,
77 CREATED_BY,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_LOGIN
81 ) values (
82 P_SET_TARGET_ID,
83 REC_PROD_LINK.PRODUCT_ID,
84 REC_PROD_LINK.PRODUCT_ORG_ID,
85 REC_PROD_LINK.CREATION_DATE,
86 REC_PROD_LINK.CREATED_BY,
87 REC_PROD_LINK.LAST_UPDATE_DATE,
88 REC_PROD_LINK.LAST_UPDATED_BY,
89 REC_PROD_LINK.LAST_UPDATE_LOGIN
90 );
91
92 end loop;
93
94 for rec_cat_link in cat_link_csr loop
95
96 insert into CS_KB_SET_CATEGORIES (
97 SET_ID,
98 CATEGORY_ID,
99 CREATION_DATE,
100 CREATED_BY,
101 LAST_UPDATE_DATE,
102 LAST_UPDATED_BY,
103 LAST_UPDATE_LOGIN,
104 ATTRIBUTE_CATEGORY,
105 ATTRIBUTE1,
106 ATTRIBUTE2,
107 ATTRIBUTE3,
108 ATTRIBUTE4,
109 ATTRIBUTE5,
110 ATTRIBUTE6,
111 ATTRIBUTE7,
112 ATTRIBUTE8,
113 ATTRIBUTE9,
114 ATTRIBUTE10,
115 ATTRIBUTE11,
116 ATTRIBUTE12,
117 ATTRIBUTE13,
118 ATTRIBUTE14,
119 ATTRIBUTE15
120
121 ) values (
122 P_SET_TARGET_ID,
123 REC_CAT_LINK.CATEGORY_ID,
124 REC_CAT_LINK.CREATION_DATE,
125 REC_CAT_LINK.CREATED_BY,
126 REC_CAT_LINK.LAST_UPDATE_DATE,
127 REC_CAT_LINK.LAST_UPDATED_BY,
128 REC_CAT_LINK.LAST_UPDATE_LOGIN,
129 REC_CAT_LINK.ATTRIBUTE_CATEGORY,
130 REC_CAT_LINK.ATTRIBUTE1,
131 REC_CAT_LINK.ATTRIBUTE2,
132 REC_CAT_LINK.ATTRIBUTE3,
133 REC_CAT_LINK.ATTRIBUTE4,
134 REC_CAT_LINK.ATTRIBUTE5,
135 REC_CAT_LINK.ATTRIBUTE6,
136 REC_CAT_LINK.ATTRIBUTE7,
137 REC_CAT_LINK.ATTRIBUTE8,
138 REC_CAT_LINK.ATTRIBUTE9,
139 REC_CAT_LINK.ATTRIBUTE10,
140 REC_CAT_LINK.ATTRIBUTE11,
141 REC_CAT_LINK.ATTRIBUTE12,
142 REC_CAT_LINK.ATTRIBUTE13,
143 REC_CAT_LINK.ATTRIBUTE14,
144 REC_CAT_LINK.ATTRIBUTE15
145 );
146
147 end loop;
148
149 return OKAY_STATUS;
150
151 <<error_found>>
152 return ERROR_STATUS;
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 RAISE;
157
158 END Clone_Link;
159
160
161
162 /*********************************** ADD LINK ********************************
163 -- This procedure adds AND deletes rows from two seperate link tables.
164 -- The tables both are very similar and serve as link tables for solutions.
165 -- Because this procedure is flexible, it require a couple of flags. Those
166 -- variables are as follows:
167 --
168 -- P_LINK_TYPE:
169 -- 0 = CS_KB_SET_PLATFORMS
170 -- 1 = CS_KB_SET_PRODUCTS
171 --
172 -- P_TASK:
173 -- 0 = REMOVE
174 -- 1 = ADD
175 --
176 -- P_RESULT:
177 -- 0 = general error
178 -- 1 = everything fine.
179 -- 2 = no valid table to insert into.
180 ******************************************************************************/
181
182 PROCEDURE add_link(
183 p_item_id IN JTF_NUMBER_TABLE,
184 p_org_id IN JTF_NUMBER_TABLE,
185 p_set_id IN NUMBER,
186 p_link_type IN NUMBER,
187 p_task IN NUMBER,
188 p_result OUT NOCOPY NUMBER
189 ) IS
190
191 -- Vars to hold sql statement
192 sqlStatement VARCHAR2(200);
193 a_sql VARCHAR2(100) := 'CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY) VALUES(:S,:P,:O,:D,:U,:D,:U)';
194
195 -- Var to hold cursor value for sql statement
196 stmt INTEGER;
197 cursorReturn INTEGER;
198
199 -- Loop control variables
200 length INTEGER;
201 counter INTEGER;
202
203
204 BEGIN
205 p_result := 1;
206 -- Getting table name
207 IF (p_link_type = 0) AND (p_task = 0) THEN
208 sqlStatement := 'DELETE FROM CS_KB_SET_PLATFORMS WHERE SET_ID = :S AND PLATFORM_ID = :P AND PLATFORM_ORG_ID = :O';
209 ELSIF (p_link_type = 0) AND (p_task = 1) THEN
210 sqlStatement := 'INSERT INTO CS_KB_SET_PLATFORMS(SET_ID,PLATFORM_ID,PLATFORM_ORG_ID,'||a_sql;
211 ELSIF (p_link_type = 1) AND (p_task = 0) THEN
212 sqlStatement := 'DELETE FROM CS_KB_SET_PRODUCTS WHERE SET_ID = :S AND PRODUCT_ID = :P AND PRODUCT_ORG_ID = :O';
213 ELSIF (p_link_type = 1) AND (p_task = 1) THEN
214 sqlStatement := 'INSERT INTO CS_KB_SET_PRODUCTS(SET_ID,PRODUCT_ID,PRODUCT_ORG_ID,'||a_sql;
215 END IF;
216
217 IF (sqlStatement is not null) THEN
218 length := p_item_id.COUNT;
219 stmt := DBMS_SQL.OPEN_CURSOR;
220 DBMS_SQL.PARSE(stmt, sqlStatement, DBMS_SQL.V7);
221
222 -- shared bind variable
223 DBMS_SQL.BIND_VARIABLE(stmt, ':S', p_set_id);
224
225 -- bind variables (used only with creating links)
226 IF (p_task = 1) THEN
227 DBMS_SQL.BIND_VARIABLE(stmt, ':D', sysdate);
228 DBMS_SQL.BIND_VARIABLE(stmt, ':U', fnd_global.user_id);
229 END IF;
230
231 FOR counter IN 1..length LOOP
232 -- bind variables (temporary)
233 DBMS_SQL.BIND_VARIABLE(stmt, ':P', p_item_id(counter));
234 DBMS_SQL.BIND_VARIABLE(stmt, ':O', p_org_id(counter));
235 -- execute everytime in loop
236 cursorReturn := DBMS_SQL.EXECUTE(stmt);
237 END LOOP;
238
239 -- Clean up!
240 DBMS_SQL.CLOSE_CURSOR(stmt);
241 --p_result := 1;
242 ELSE
243 p_result := 2;
244 END IF;
245 EXCEPTION
246 WHEN OTHERS THEN
247 DBMS_SQL.CLOSE_CURSOR(stmt);
248 p_result := 0;
249 RAISE;
250 END;
251
252
253 END;