[Home] [Help]
PACKAGE BODY: APPS.CSM_CUSTOMIZATION_VIEWS_PKG
Source
1 PACKAGE BODY CSM_CUSTOMIZATION_VIEWS_PKG AS
2 /* $Header: csmlcvb.pls 120.2 2005/11/23 05:28:32 saradhak noship $ */
3
4 PROCEDURE INSERT_ROW
5 (
6 X_PAGE_NAME VARCHAR2,
7 X_REGION_NAME VARCHAR2,
8 X_CUST_VIEW_KEY VARCHAR2,
9 X_LEVEL_ID NUMBER,
10 X_LEVEL_VALUE NUMBER,
11 X_MESSAGE_NAME VARCHAR2,
12 X_SELECT_STATEMENT VARCHAR2,
13 X_WHERE_CONDITION VARCHAR2,
14 X_WHERE_CLAUSE VARCHAR2,
15 X_ORDERBY_CLAUSE VARCHAR2,
16 X_IS_DEFAULT VARCHAR2,
17 X_DISPLAY_ROWS NUMBER,
18 X_DISPLAY_VIEW VARCHAR2,
19 X_BASE_VO_NAME VARCHAR2,
20 X_UPDATABLE VARCHAR2,
21 X_OWNER VARCHAR2
22 )
23 IS
24
25 BEGIN
26 --Insert
27 INSERT INTO CSM_CUSTOMIZATION_VIEWS
28 (CUST_VIEW_ID,
29 PAGE_NAME,
30 REGION_NAME,
31 CUST_VIEW_KEY,
32 LEVEL_ID,
33 LEVEL_VALUE,
34 MESSAGE_NAME,
35 SELECT_STATEMENT,
36 WHERE_CONDITION,
37 WHERE_CLAUSE,
38 ORDERBY_CLAUSE,
39 IS_DEFAULT,
40 DISPLAY_ROWS,
41 DISPLAY_VIEW,
42 BASE_VO_NAME,
43 UPDATABLE,
44 LAST_UPDATED_BY,
45 CREATION_DATE,
46 CREATED_BY,
47 LAST_UPDATE_DATE)
48 VALUES(CSM_CUSTOMIZATION_VIEWS_S.NEXTVAL,
49 X_PAGE_NAME,
50 X_REGION_NAME,
51 X_CUST_VIEW_KEY,
52 X_LEVEL_ID,
53 X_LEVEL_VALUE,
54 X_MESSAGE_NAME,
55 X_SELECT_STATEMENT,
56 X_WHERE_CONDITION,
57 X_WHERE_CLAUSE,
58 X_ORDERBY_CLAUSE,
59 X_IS_DEFAULT,
60 X_DISPLAY_ROWS,
61 X_DISPLAY_VIEW,
62 X_BASE_VO_NAME,
63 X_UPDATABLE,
64 DECODE(X_OWNER,'SEED',1,0),
65 SYSDATE,
66 DECODE(X_OWNER,'SEED',1,0),
67 SYSDATE);
68
69 END Insert_Row;
70
71 PROCEDURE UPDATE_ROW(
72 X_PAGE_NAME VARCHAR2,
73 X_REGION_NAME VARCHAR2,
74 X_CUST_VIEW_KEY VARCHAR2,
75 X_LEVEL_ID NUMBER,
76 X_LEVEL_VALUE NUMBER,
77 X_MESSAGE_NAME VARCHAR2,
78 X_SELECT_STATEMENT VARCHAR2,
79 X_WHERE_CONDITION VARCHAR2,
80 X_WHERE_CLAUSE VARCHAR2,
81 X_ORDERBY_CLAUSE VARCHAR2,
82 X_IS_DEFAULT VARCHAR2,
83 X_DISPLAY_ROWS NUMBER,
84 X_DISPLAY_VIEW VARCHAR2,
85 X_BASE_VO_NAME VARCHAR2,
86 X_UPDATABLE VARCHAR2,
87 X_OWNER VARCHAR2
88 )
89
90 IS
91
92 BEGIN
93 --Update
94 UPDATE CSM_CUSTOMIZATION_VIEWS
95 SET MESSAGE_NAME = X_MESSAGE_NAME,
96 SELECT_STATEMENT = X_SELECT_STATEMENT,
97 WHERE_CONDITION = X_WHERE_CONDITION,
98 WHERE_CLAUSE = X_WHERE_CLAUSE,
99 ORDERBY_CLAUSE = X_ORDERBY_CLAUSE,
100 IS_DEFAULT = X_IS_DEFAULT,
101 DISPLAY_ROWS = X_DISPLAY_ROWS,
102 DISPLAY_VIEW = X_DISPLAY_VIEW,
103 BASE_VO_NAME = X_BASE_VO_NAME,
104 UPDATABLE = X_UPDATABLE,
105 LAST_UPDATED_BY = DECODE(X_OWNER,'SEED',1,0),
106 LAST_UPDATE_DATE = SYSDATE
107 WHERE PAGE_NAME = X_PAGE_NAME
108 AND REGION_NAME = X_REGION_NAME
109 AND CUST_VIEW_KEY = X_CUST_VIEW_KEY
110 AND LEVEL_ID = X_LEVEL_ID
111 AND LEVEL_VALUE = X_LEVEL_VALUE;
112
113 END Update_Row;
114
115 PROCEDURE LOAD_ROW(
116 X_PAGE_NAME VARCHAR2,
117 X_REGION_NAME VARCHAR2,
118 X_CUST_VIEW_KEY VARCHAR2,
119 X_LEVEL_ID NUMBER,
120 X_LEVEL_VALUE NUMBER,
121 X_MESSAGE_NAME VARCHAR2,
122 X_SELECT_STATEMENT VARCHAR2,
123 X_WHERE_CONDITION VARCHAR2,
124 X_WHERE_CLAUSE VARCHAR2,
125 X_ORDERBY_CLAUSE VARCHAR2,
126 X_IS_DEFAULT VARCHAR2,
127 X_DISPLAY_ROWS NUMBER,
128 X_DISPLAY_VIEW VARCHAR2,
129 X_BASE_VO_NAME VARCHAR2,
130 X_UPDATABLE VARCHAR2,
131 X_OWNER VARCHAR2
132 )
133 IS
134
135
136 CURSOR c_view_exists(b_page_name VARCHAR2,b_region_name VARCHAR2,b_cust_view_key VARCHAR2,b_level_id NUMBER,b_level_value NUMBER) IS
137 SELECT 1
138 FROM CSM_CUSTOMIZATION_VIEWS CCV
139 WHERE CCV.PAGE_NAME = b_page_name
140 AND CCV.REGION_NAME = b_region_name
141 AND CCV.CUST_VIEW_KEY = b_cust_view_key
142 AND CCV.LEVEL_ID = b_level_id
143 AND CCV.LEVEL_VALUE = b_level_value;
144
145 l_exists NUMBER;
146
147 BEGIN
148
149 OPEN c_view_exists(X_PAGE_NAME,X_REGION_NAME,X_CUST_VIEW_KEY,X_LEVEL_ID,X_LEVEL_VALUE);
150 FETCH c_view_exists INTO l_exists;
151 CLOSE c_view_exists;
152
153 IF l_exists IS NULL THEN
154
155 Insert_Row(
156 X_PAGE_NAME,
157 X_REGION_NAME,
158 X_CUST_VIEW_KEY,
159 X_LEVEL_ID,
160 X_LEVEL_VALUE,
161 X_MESSAGE_NAME,
162 X_SELECT_STATEMENT,
163 X_WHERE_CONDITION,
164 X_WHERE_CLAUSE,
165 X_ORDERBY_CLAUSE,
166 X_IS_DEFAULT,
167 X_DISPLAY_ROWS,
168 X_DISPLAY_VIEW,
169 X_BASE_VO_NAME,
170 X_UPDATABLE,
171 X_OWNER );
172
173
174 ELSE
175 Update_Row(
176 X_PAGE_NAME,
177 X_REGION_NAME,
178 X_CUST_VIEW_KEY,
179 X_LEVEL_ID,
180 X_LEVEL_VALUE,
181 X_MESSAGE_NAME,
182 X_SELECT_STATEMENT,
183 X_WHERE_CONDITION,
184 X_WHERE_CLAUSE,
185 X_ORDERBY_CLAUSE,
186 X_IS_DEFAULT,
187 X_DISPLAY_ROWS,
188 X_DISPLAY_VIEW,
189 X_BASE_VO_NAME,
190 X_UPDATABLE,
191 X_OWNER);
192
193 END IF;
194
195
196 END load_row;
197
198 END ;