[Home] [Help]
PACKAGE BODY: APPS.CSM_AD_SEARCH_REGION_VIEW_PKG
Source
1 PACKAGE BODY CSM_AD_SEARCH_REGION_VIEW_PKG as
2 /* $Header: csmlasrb.pls 120.0 2008/01/25 19:16:06 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_FIELD_TYPE IN VARCHAR2,
10 X_BASE_VO IN VARCHAR2,
11 X_PARAMETERS IN VARCHAR2,
12 X_VO_TEXT_COL IN VARCHAR2,
13 X_VO_VALUE_COL IN VARCHAR2,
14 X_DISPLAY_SEQ IN NUMBER,
15 X_ORIGINAL_SEQ IN NUMBER,
16 X_IS_REMOVED IN VARCHAR2,
17 X_DB_TYPE IN VARCHAR2,
18 X_OPERATION IN VARCHAR2,
19 X_OWNER IN VARCHAR2
20 )
21
22 IS
23
24 BEGIN
25
26 INSERT INTO CSM_AD_SEARCH_REGION_VIEW
27 (ID,
28 SEARCH_TYPE_ID,
29 LEVEL_ID,
30 LEVEL_VALUE,
31 NAME,
32 COLUMN_NAME,
33 FIELD_TYPE,
34 BASE_VO,
35 PARAMETERS,
36 VO_TEXT_COL,
37 VO_VALUE_COL,
38 DISPLAY_SEQ,
39 ORIGINAL_SEQ,
40 IS_REMOVED,
41 CREATION_DATE,
42 CREATED_BY,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 DB_TYPE,
46 OPERATION
47 )
48 VALUES
49 ( CSM_AD_SEARCH_REGION_VIEW_S.NEXTVAL,
50 X_SEARCH_TYPE_ID,
51 X_LEVEL_ID,
52 X_LEVEL_VALUE,
53 X_NAME,
54 X_COLUMN_NAME,
55 X_FIELD_TYPE,
56 X_BASE_VO,
57 X_PARAMETERS,
58 X_VO_TEXT_COL,
59 X_VO_VALUE_COL,
60 X_DISPLAY_SEQ,
61 X_ORIGINAL_SEQ,
62 X_IS_REMOVED,
63 SYSDATE,
64 DECODE(X_OWNER,'SEED',1,0),
65 SYSDATE,
66 DECODE(X_OWNER,'SEED',1,0),
67 X_DB_TYPE,
68 X_OPERATION
69 );
70 END INSERT_ROW;
71
72 PROCEDURE UPDATE_ROW (
73 X_SEARCH_TYPE_ID IN NUMBER,
74 X_LEVEL_ID IN NUMBER,
75 X_LEVEL_VALUE IN NUMBER,
76 X_NAME IN VARCHAR2,
77 X_COLUMN_NAME IN VARCHAR2,
78 X_FIELD_TYPE IN VARCHAR2,
79 X_BASE_VO IN VARCHAR2,
80 X_PARAMETERS IN VARCHAR2,
81 X_VO_TEXT_COL IN VARCHAR2,
82 X_VO_VALUE_COL IN VARCHAR2,
83 X_DISPLAY_SEQ IN NUMBER,
84 X_ORIGINAL_SEQ IN NUMBER,
85 X_IS_REMOVED IN VARCHAR2,
86 X_DB_TYPE IN VARCHAR2,
87 X_OPERATION IN VARCHAR2,
88 X_OWNER IN VARCHAR2
89 )
90 IS
91
92 BEGIN
93
94
95 UPDATE CSM_AD_SEARCH_REGION_VIEW
96 SET
97 NAME = X_NAME,
98 FIELD_TYPE = X_FIELD_TYPE,
99 BASE_VO = X_BASE_VO,
100 PARAMETERS = X_PARAMETERS,
101 VO_TEXT_COL = X_VO_TEXT_COL,
102 VO_VALUE_COL = X_VO_VALUE_COL,
103 DISPLAY_SEQ = X_DISPLAY_SEQ,
104 ORIGINAL_SEQ = X_ORIGINAL_SEQ,
105 IS_REMOVED = X_IS_REMOVED,
106 DB_TYPE = X_DB_TYPE,
107 OPERATION = X_OPERATION,
108 LAST_UPDATE_DATE = SYSDATE,
109 LAST_UPDATED_BY = DECODE(X_OWNER,'SEED',1,0)
110 WHERE COLUMN_NAME = X_COLUMN_NAME
111 AND SEARCH_TYPE_ID = X_SEARCH_TYPE_ID ;
112
113
114 END UPDATE_ROW;
115
116 PROCEDURE LOAD_ROW (
117 X_ID IN NUMBER,
118 X_SEARCH_TYPE_ID IN NUMBER,
119 X_LEVEL_ID IN NUMBER,
120 X_LEVEL_VALUE IN NUMBER,
121 X_NAME IN VARCHAR2,
122 X_COLUMN_NAME IN VARCHAR2,
123 X_FIELD_TYPE IN VARCHAR2,
124 X_BASE_VO IN VARCHAR2,
125 X_PARAMETERS IN VARCHAR2,
126 X_VO_TEXT_COL IN VARCHAR2,
127 X_VO_VALUE_COL IN VARCHAR2,
128 X_DISPLAY_SEQ IN NUMBER,
129 X_ORIGINAL_SEQ IN NUMBER,
130 X_IS_REMOVED IN VARCHAR2,
131 X_DB_TYPE IN VARCHAR2,
132 X_OPERATION IN VARCHAR2,
133 X_OWNER IN VARCHAR2
134 )
135
136
137 IS
138
139 CURSOR c_exists IS
140 SELECT 1
141 FROM CSM_AD_SEARCH_REGION_VIEW
142 WHERE COLUMN_NAME =X_COLUMN_NAME
143 AND SEARCH_TYPE_ID=X_SEARCH_TYPE_ID;
144
145
146 l_exists NUMBER;
147
148 BEGIN
149
150
151
152 OPEN c_exists;
153 FETCH c_exists INTO l_exists;
154 CLOSE c_exists;
155
156
157
158 IF l_exists IS NULL THEN
159 INSERT_ROW (
160 X_SEARCH_TYPE_ID,
161 X_LEVEL_ID,
162 X_LEVEL_VALUE,
163 X_NAME,
164 X_COLUMN_NAME,
165 X_FIELD_TYPE,
166 X_BASE_VO,
167 X_PARAMETERS,
168 X_VO_TEXT_COL,
169 X_VO_VALUE_COL,
170 X_DISPLAY_SEQ,
171 X_ORIGINAL_SEQ,
172 X_IS_REMOVED,
173 X_DB_TYPE,
174 X_OPERATION,
175 X_OWNER
176 );
177
178 ELSE
179
180 UPDATE_ROW (
181 X_SEARCH_TYPE_ID,
182 X_LEVEL_ID,
183 X_LEVEL_VALUE,
184 X_NAME,
185 X_COLUMN_NAME,
186 X_FIELD_TYPE,
187 X_BASE_VO,
188 X_PARAMETERS,
189 X_VO_TEXT_COL,
190 X_VO_VALUE_COL,
191 X_DISPLAY_SEQ,
192 X_ORIGINAL_SEQ,
193 X_IS_REMOVED,
194 X_DB_TYPE,
195 X_OPERATION,
196 X_OWNER
197 );
198 END IF;
199
200 END LOAD_ROW;
201
202 END CSM_AD_SEARCH_REGION_VIEW_PKG ;