PACKAGE BODY: APPS.CSM_AD_SEARCH_TITLE_VIEW_PKG

Source

1 PACKAGE BODY CSM_AD_SEARCH_TITLE_VIEW_PKG as
2 /* $Header: csmlastb.pls 120.1 2008/02/07 09:15:18 trajasek noship $ */
3 PROCEDURE INSERT_ROW (
4                       X_SEARCH_TYPE    IN VARCHAR2,
5                       X_LEVEL_ID       IN NUMBER,
6                       X_LEVEL_VALUE    IN NUMBER,
7                       X_SEARCH_TITLE   IN VARCHAR2,
8                       X_VO_NAME        IN VARCHAR2,
9                       X_SEARCH_TYPE_ID IN NUMBER,
10                       X_OWNER          IN VARCHAR2
11                       )
12 IS
13 
14 BEGIN
15 
16   INSERT INTO CSM_AD_SEARCH_TITLE_VIEW
17                            (ID,
18                             SEARCH_TYPE,
19                             LEVEL_ID,
20                             LEVEL_VALUE,
21                             SEARCH_TITLE,
22                             VO_NAME,
23                             CREATION_DATE,
24                             CREATED_BY,
25                             LAST_UPDATE_DATE,
26                             LAST_UPDATED_BY,
27                             SEARCH_TYPE_ID)
28                           VALUES
29                           ( CSM_AD_SEARCH_TITLE_VIEW_S.NEXTVAL,
30                             X_SEARCH_TYPE,
31                             X_LEVEL_ID,
32                             X_LEVEL_VALUE,
33                             X_SEARCH_TITLE,
34                             X_VO_NAME,
35                             SYSDATE,
36                             DECODE(X_OWNER,'SEED',1,0),
37                             SYSDATE,
38                             DECODE(X_OWNER,'SEED',1,0),
39                             X_SEARCH_TYPE_ID
40                             );
41 
42 
43 
44 END INSERT_ROW;
45 
46 PROCEDURE UPDATE_ROW (
47                      X_SEARCH_TYPE    IN VARCHAR2,
48                      X_LEVEL_ID       IN NUMBER,
49                      X_LEVEL_VALUE    IN NUMBER,
50                      X_SEARCH_TITLE   IN VARCHAR2,
51                      X_VO_NAME        IN VARCHAR2,
52                      X_SEARCH_TYPE_ID IN NUMBER,
53                      X_OWNER          IN VARCHAR2
54                      )
55 
56 IS
57 
58 BEGIN
59 
60 
61   UPDATE CSM_AD_SEARCH_TITLE_VIEW
62    SET
63       SEARCH_TITLE     =  X_SEARCH_TITLE,
64       VO_NAME          =  X_VO_NAME,
65       SEARCH_TYPE_ID   =  X_SEARCH_TYPE_ID,
66       LAST_UPDATE_DATE =  SYSDATE,
67       LAST_UPDATED_BY  =  DECODE(X_OWNER,'SEED',1,0)
68    WHERE SEARCH_TYPE   =  X_SEARCH_TYPE;
69 
70 
71 END UPDATE_ROW;
72 
73 PROCEDURE LOAD_ROW (
74                     X_ID             IN NUMBER,
75                     X_SEARCH_TYPE    IN VARCHAR2,
76                     X_LEVEL_ID       IN NUMBER,
77                     X_LEVEL_VALUE    IN NUMBER,
78                     X_SEARCH_TITLE   IN VARCHAR2,
79                     X_VO_NAME        IN VARCHAR2,
80                     X_SEARCH_TYPE_ID IN NUMBER,
81                     X_OWNER          IN VARCHAR2)
82 
83 IS
84 
85 CURSOR c_exists IS
86    SELECT 1
87    FROM  CSM_AD_SEARCH_TITLE_VIEW
88    WHERE SEARCH_TYPE=X_SEARCH_TYPE;
89 
90 l_exists NUMBER;
91 
92 BEGIN
93 
94 
95 
96  OPEN c_exists;
97  FETCH c_exists INTO l_exists;
98  CLOSE c_exists;
99 
100  IF l_exists IS NULL THEN
101    INSERT_ROW (
102                  X_SEARCH_TYPE,
103                  X_LEVEL_ID,
104                  X_LEVEL_VALUE,
105                  X_SEARCH_TITLE,
106                  X_VO_NAME,
107                  X_SEARCH_TYPE_ID,
108                  X_OWNER
109                  );
110 
111 
112  ELSE
113 
114    UPDATE_ROW (
115                 X_SEARCH_TYPE,
116                 X_LEVEL_ID,
117                 X_LEVEL_VALUE,
118                 X_SEARCH_TITLE,
119                 X_VO_NAME,
120                 X_SEARCH_TYPE_ID,
121                 X_OWNER
122                  );
123 
124  END IF;
125 
126 END LOAD_ROW;
127 
128 END CSM_AD_SEARCH_TITLE_VIEW_PKG ;