[Home] [Help]
PACKAGE BODY: APPS.CSM_AD_SIMPLE_SEARCH_VIEW_PKG
Source
1 PACKAGE BODY CSM_AD_SIMPLE_SEARCH_VIEW_PKG as
2 /* $Header: csmlassb.pls 120.0 2008/01/25 19:16:41 trajasek noship $ */
3 PROCEDURE INSERT_ROW (
4 X_SEARCH_TYPE_ID IN NUMBER,
5 X_LEVEL_ID IN NUMBER,
6 X_LEVEL_VALUE IN NUMBER,
7 X_NAME IN VARCHAR2,
8 X_COLUMN_NAME IN VARCHAR2,
9 X_DISPLAY_SEQ IN NUMBER,
10 X_ORIGINAL_SEQ IN NUMBER,
11 X_GROUP_TYPE IN VARCHAR2,
12 X_IS_REMOVED IN VARCHAR2,
13 X_DB_TYPE IN VARCHAR2,
14 X_OPERATION IN VARCHAR2,
15 X_OWNER IN VARCHAR2
16 )
17 IS
18
19 BEGIN
20
21 INSERT INTO CSM_AD_SIMPLE_SEARCH_VIEW
22 (ID,
23 SEARCH_TYPE_ID,
24 LEVEL_ID,
25 LEVEL_VALUE,
26 NAME,
27 COLUMN_NAME,
28 DISPLAY_SEQ,
29 ORIGINAL_SEQ,
30 GROUP_TYPE,
31 IS_REMOVED,
32 DB_TYPE,
33 OPERATION,
34 CREATION_DATE,
35 CREATED_BY,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY)
38 VALUES
39 ( CSM_AD_SIMPLE_SEARCH_VIEW_S.NEXTVAL,
40 X_SEARCH_TYPE_ID,
41 X_LEVEL_ID,
42 X_LEVEL_VALUE,
43 X_NAME,
44 X_COLUMN_NAME,
45 X_DISPLAY_SEQ,
46 X_ORIGINAL_SEQ,
47 X_GROUP_TYPE,
48 X_IS_REMOVED,
49 X_DB_TYPE,
50 X_OPERATION,
51 SYSDATE,
52 DECODE(X_OWNER,'SEED',1,0),
53 SYSDATE,
54 DECODE(X_OWNER,'SEED',1,0)
55 );
56
57
58
59 END INSERT_ROW;
60
61 PROCEDURE UPDATE_ROW (
62 X_SEARCH_TYPE_ID IN NUMBER,
63 X_LEVEL_ID IN NUMBER,
64 X_LEVEL_VALUE IN NUMBER,
65 X_NAME IN VARCHAR2,
66 X_COLUMN_NAME IN VARCHAR2,
67 X_DISPLAY_SEQ IN NUMBER,
68 X_ORIGINAL_SEQ IN NUMBER,
69 X_GROUP_TYPE IN VARCHAR2,
70 X_IS_REMOVED IN VARCHAR2,
71 X_DB_TYPE IN VARCHAR2,
72 X_OPERATION IN VARCHAR2,
73 X_OWNER IN VARCHAR2
74 )
75
76
77 IS
78
79 BEGIN
80
81
82 UPDATE CSM_AD_SIMPLE_SEARCH_VIEW
83 SET
84 NAME = X_NAME,
85 DISPLAY_SEQ = X_DISPLAY_SEQ,
86 ORIGINAL_SEQ = X_ORIGINAL_SEQ,
87 GROUP_TYPE = X_GROUP_TYPE,
88 IS_REMOVED = X_IS_REMOVED,
89 DB_TYPE = X_DB_TYPE,
90 OPERATION = X_OPERATION,
91 LAST_UPDATE_DATE = SYSDATE,
92 LAST_UPDATED_BY = DECODE(X_OWNER,'SEED',1,0)
93 WHERE COLUMN_NAME = X_COLUMN_NAME
94 AND SEARCH_TYPE_ID = X_SEARCH_TYPE_ID ;
95
96
97 END UPDATE_ROW;
98
99 PROCEDURE LOAD_ROW (
100 X_ID IN NUMBER,
101 X_SEARCH_TYPE_ID IN NUMBER,
102 X_LEVEL_ID IN NUMBER,
103 X_LEVEL_VALUE IN NUMBER,
104 X_NAME IN VARCHAR2,
105 X_COLUMN_NAME IN VARCHAR2,
106 X_DISPLAY_SEQ IN NUMBER,
107 X_ORIGINAL_SEQ IN NUMBER,
108 X_GROUP_TYPE IN VARCHAR2,
109 X_IS_REMOVED IN VARCHAR2,
110 X_DB_TYPE IN VARCHAR2,
111 X_OPERATION IN VARCHAR2,
112 X_OWNER IN VARCHAR2
113 )
114
115 IS
116
117 CURSOR c_exists IS
118 SELECT 1
119 FROM CSM_AD_SIMPLE_SEARCH_VIEW
120 WHERE COLUMN_NAME =X_COLUMN_NAME
121 AND SEARCH_TYPE_ID=X_SEARCH_TYPE_ID;
122
123
124 l_exists NUMBER;
125
126 BEGIN
127
128
129
130 OPEN c_exists;
131 FETCH c_exists INTO l_exists;
132 CLOSE c_exists;
133
134
135
136 IF l_exists IS NULL THEN
137 INSERT_ROW (
138 X_SEARCH_TYPE_ID,
139 X_LEVEL_ID,
140 X_LEVEL_VALUE,
141 X_NAME,
142 X_COLUMN_NAME,
143 X_DISPLAY_SEQ,
144 X_ORIGINAL_SEQ,
145 X_GROUP_TYPE,
146 X_IS_REMOVED,
147 X_DB_TYPE,
148 X_OPERATION,
149 X_OWNER
150 );
151
152 ELSE
153
154 UPDATE_ROW (
155 X_SEARCH_TYPE_ID,
156 X_LEVEL_ID,
157 X_LEVEL_VALUE,
158 X_NAME,
159 X_COLUMN_NAME,
160 X_DISPLAY_SEQ,
161 X_ORIGINAL_SEQ,
162 X_GROUP_TYPE,
163 X_IS_REMOVED,
164 X_DB_TYPE,
165 X_OPERATION,
166 X_OWNER
167 );
168
169 END IF;
170
171 END LOAD_ROW;
172
173 END CSM_AD_SIMPLE_SEARCH_VIEW_PKG ;