[Home] [Help]
PACKAGE BODY: APPS.AMS_MET_TPL_HEADERS_PKG
Source
1 package body AMS_MET_TPL_HEADERS_PKG as
2 /* $Header: amslmthb.pls 115.1 2000/01/09 17:55:04 pkm ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_METRIC_TPL_HEADER_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_METRIC_TPL_HEADER_NAME in VARCHAR2,
8 X_DESCRIPTION in VARCHAR2,
9 X_ARC_ACT_METRIC_USED_BY in VARCHAR2,
10 X_ACT_METRIC_USED_BY_TYPE in VARCHAR2,
11 X_ACT_METRIC_USED_BY_SUBTYPE in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from AMS_MET_TPL_HEADERS
19 where METRIC_TPL_HEADER_ID = X_METRIC_TPL_HEADER_ID;
20 begin
21 insert into AMS_MET_TPL_HEADERS (
22 METRIC_TPL_HEADER_ID,
23 LAST_UPDATE_DATE,
24 LAST_UPDATED_BY,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_LOGIN,
28 OBJECT_VERSION_NUMBER,
29 METRIC_TPL_HEADER_NAME,
30 DESCRIPTION,
31 ARC_ACT_METRIC_USED_BY,
32 ACT_METRIC_USED_BY_TYPE,
33 ACT_METRIC_USED_BY_SUBTYPE
34 ) values(
35 X_METRIC_TPL_HEADER_ID,
36 X_LAST_UPDATE_DATE,
37 X_LAST_UPDATED_BY,
38 X_CREATION_DATE,
39 X_CREATED_BY,
40 X_LAST_UPDATE_LOGIN,
41 X_OBJECT_VERSION_NUMBER,
42 X_METRIC_TPL_HEADER_NAME,
43 X_DESCRIPTION,
44 X_ARC_ACT_METRIC_USED_BY,
45 X_ACT_METRIC_USED_BY_TYPE,
46 X_ACT_METRIC_USED_BY_SUBTYPE);
47
48 open c;
49 fetch c into X_ROWID;
50 if (c%notfound) then
51 close c;
52 raise no_data_found;
53 end if;
54 close c;
55
56 end INSERT_ROW;
57
58 procedure UPDATE_ROW (
59 X_METRIC_TPL_HEADER_ID in NUMBER,
60 X_OBJECT_VERSION_NUMBER in NUMBER,
61 X_METRIC_TPL_HEADER_NAME in VARCHAR2,
62 X_DESCRIPTION in VARCHAR2,
63 X_ARC_ACT_METRIC_USED_BY in VARCHAR2,
64 X_ACT_METRIC_USED_BY_TYPE in VARCHAR2,
65 X_ACT_METRIC_USED_BY_SUBTYPE in VARCHAR2,
66 X_LAST_UPDATE_DATE in DATE,
67 X_LAST_UPDATED_BY in NUMBER,
68 X_LAST_UPDATE_LOGIN in NUMBER
69 ) is
70 begin
71 update AMS_MET_TPL_HEADERS set
72 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
73 METRIC_TPL_HEADER_NAME = X_METRIC_TPL_HEADER_NAME,
74 DESCRIPTION = X_DESCRIPTION,
75 ARC_ACT_METRIC_USED_BY = X_ARC_ACT_METRIC_USED_BY,
76 ACT_METRIC_USED_BY_TYPE = X_ACT_METRIC_USED_BY_TYPE,
77 ACT_METRIC_USED_BY_SUBTYPE = X_ACT_METRIC_USED_BY_SUBTYPE,
78 METRIC_TPL_HEADER_ID = X_METRIC_TPL_HEADER_ID,
79 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
80 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
81 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
82 where METRIC_TPL_HEADER_ID = X_METRIC_TPL_HEADER_ID;
83
84 if (sql%notfound) then
85 raise no_data_found;
86 end if;
87 end UPDATE_ROW;
88
89 procedure DELETE_ROW (
90 X_METRIC_TPL_HEADER_ID in NUMBER
91 ) is
92 begin
93 delete from AMS_MET_TPL_HEADERS
94 where METRIC_TPL_HEADER_ID = X_METRIC_TPL_HEADER_ID;
95
96 if (sql%notfound) then
97 raise no_data_found;
98 end if;
99
100 end DELETE_ROW;
101
102 procedure LOAD_ROW (
103 X_METRIC_TPL_HEADER_ID in NUMBER,
104 X_OBJECT_VERSION_NUMBER in NUMBER,
105 X_METRIC_TPL_HEADER_NAME in VARCHAR2,
106 X_DESCRIPTION in VARCHAR2,
107 X_ARC_ACT_METRIC_USED_BY in VARCHAR2,
108 X_ACT_METRIC_USED_BY_TYPE in VARCHAR2,
109 X_ACT_METRIC_USED_BY_SUBTYPE in VARCHAR2,
110 X_Owner VARCHAR2
111 )is
112
113 l_user_id number := 0;
114 l_obj_verno number;
115 l_dummy_char varchar2(1);
116 l_row_id varchar2(100);
117 l_met_tpl_hdr_id number;
118
119 cursor c_obj_verno is
120 select object_version_number
121 from AMS_MET_TPL_HEADERS
122 where METRIC_TPL_HEADER_ID = X_METRIC_TPL_HEADER_ID;
123
124 cursor c_chk_mth_exists is
125 select 'x'
126 from AMS_MET_TPL_HEADERS
127 where METRIC_TPL_HEADER_ID = X_METRIC_TPL_HEADER_ID;
128
129 cursor c_get_mthid is
130 select AMS_MET_TPL_HEADERS_ALL_S.nextval
131 from dual;
132
133 BEGIN
134
135 if X_OWNER = 'SEED' then
136 l_user_id := 1;
137 end if;
138
139 open c_chk_mth_exists;
140 fetch c_chk_mth_exists into l_dummy_char;
141 if c_chk_mth_exists%notfound
142 then
143 close c_chk_mth_exists;
144 if X_METRIC_TPL_HEADER_ID is null
145 then
146 open c_get_mthid;
147 fetch c_get_mthid into l_met_tpl_hdr_id;
148 close c_get_mthid;
149 else
150 l_met_tpl_hdr_id := X_METRIC_TPL_HEADER_ID;
151 end if;
152 l_obj_verno := 1;
153 AMS_MET_TPL_HEADERS_PKG.INSERT_ROW(
154 X_ROWID => l_row_id,
155 X_METRIC_TPL_HEADER_ID => l_met_tpl_hdr_id,
156 X_OBJECT_VERSION_NUMBER => l_obj_verno,
157 X_METRIC_TPL_HEADER_NAME => X_METRIC_TPL_HEADER_NAME,
158 X_DESCRIPTION => X_DESCRIPTION,
159 X_ARC_ACT_METRIC_USED_BY => X_ARC_ACT_METRIC_USED_BY,
160 X_ACT_METRIC_USED_BY_TYPE => X_ACT_METRIC_USED_BY_TYPE,
161 X_ACT_METRIC_USED_BY_SUBTYPE => X_ACT_METRIC_USED_BY_SUBTYPE,
162 X_CREATION_DATE => SYSDATE,
163 X_CREATED_BY => l_user_id,
164 X_LAST_UPDATE_DATE => SYSDATE,
165 X_LAST_UPDATED_BY => l_user_id,
166 X_LAST_UPDATE_LOGIN => 0
167 );
168 else
169 close c_chk_mth_exists;
170 open c_obj_verno;
171 fetch c_obj_verno into l_obj_verno;
172 close c_obj_verno;
173 AMS_MET_TPL_HEADERS_PKG.UPDATE_ROW(
174 X_METRIC_TPL_HEADER_ID => X_METRIC_TPL_HEADER_ID,
175 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
176 X_METRIC_TPL_HEADER_NAME => X_METRIC_TPL_HEADER_NAME,
177 X_DESCRIPTION => X_DESCRIPTION,
178 X_ARC_ACT_METRIC_USED_BY => X_ARC_ACT_METRIC_USED_BY,
179 X_ACT_METRIC_USED_BY_TYPE => X_ACT_METRIC_USED_BY_TYPE,
180 X_ACT_METRIC_USED_BY_SUBTYPE => X_ACT_METRIC_USED_BY_SUBTYPE,
181 X_LAST_UPDATE_DATE => SYSDATE,
182 X_LAST_UPDATED_BY => l_user_id,
183 X_LAST_UPDATE_LOGIN => 0
184 );
185
186 end if;
187
188 END LOAD_ROW;
189
190 end AMS_MET_TPL_HEADERS_PKG;