[Home] [Help]
PACKAGE BODY: APPS.AHL_WF_MAPPING_PKG
Source
1 PACKAGE BODY AHL_WF_MAPPING_PKG as
2 /*$Header: AHLLWFMB.pls 115.6 2003/12/10 13:39:06 rroy noship $*/
3 procedure INSERT_ROW
4 (
5 X_ROWID IN out NOCOPY VARCHAR2,
6 X_WF_MAPPING_ID IN NUMBER,
7 X_OBJECT_VERSION_NUMBER IN NUMBER,
8 X_LAST_UPDATE_DATE IN DATE,
9 X_LAST_UPDATED_BY IN NUMBER,
10 X_CREATION_DATE IN DATE,
11 X_CREATED_BY IN NUMBER,
12 X_LAST_UPDATE_LOGIN IN NUMBER,
13 X_ACTIVE_FLAG IN VARCHAR2,
14 X_WF_PROCESS_NAME IN VARCHAR2,
15 X_APPROVAL_OBJECT IN VARCHAR2,
16 X_ITEM_TYPE IN VARCHAR2,
17 X_APPLICATION_USG_CODE IN VARCHAR2
18 )
19 is
20 cursor C is select ROWID from AHL_WF_MAPPING where
21 WF_MAPPING_ID = X_WF_MAPPING_ID ;
22 begin
23 insert into AHL_WF_MAPPING
24 (
25 WF_MAPPING_ID ,
26 OBJECT_VERSION_NUMBER ,
27 LAST_UPDATE_DATE ,
28 LAST_UPDATED_BY ,
29 CREATION_DATE ,
30 CREATED_BY ,
31 LAST_UPDATE_LOGIN ,
32 ACTIVE_FLAG ,
33 WF_PROCESS_NAME ,
34 APPROVAL_OBJECT ,
35 ITEM_TYPE ,
36 APPLICATION_USG_CODE
37 )
38 values
39 (
40 X_WF_MAPPING_ID ,
41 X_OBJECT_VERSION_NUMBER ,
42 X_LAST_UPDATE_DATE ,
43 X_LAST_UPDATED_BY ,
44 X_CREATION_DATE ,
45 X_CREATED_BY ,
46 X_LAST_UPDATE_LOGIN ,
47 X_ACTIVE_FLAG ,
48 X_WF_PROCESS_NAME ,
49 X_APPROVAL_OBJECT ,
50 X_ITEM_TYPE ,
51 X_APPLICATION_USG_CODE
52 );
53
54 open c;
55 fetch c into X_ROWID;
56 if (c%notfound) then
57 close c;
58 raise no_data_found;
59 end if;
60 close c;
61
62 end INSERT_ROW;
63
64 procedure UPDATE_ROW
65 (
66 X_WF_MAPPING_ID IN NUMBER,
67 X_OBJECT_VERSION_NUMBER IN NUMBER,
68 X_LAST_UPDATE_DATE IN DATE,
69 X_LAST_UPDATED_BY IN NUMBER,
70 X_LAST_UPDATE_LOGIN IN NUMBER,
71 X_ACTIVE_FLAG IN VARCHAR2,
72 X_WF_PROCESS_NAME IN VARCHAR2,
73 X_APPROVAL_OBJECT IN VARCHAR2,
74 X_ITEM_TYPE IN VARCHAR2,
75 X_APPLICATION_USG_CODE IN VARCHAR2
76 ) is
77 begin
78 update AHL_WF_MAPPING set
79 WF_MAPPING_ID = X_WF_MAPPING_ID,
80 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
81 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
82 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
83 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
84 ACTIVE_FLAG = X_ACTIVE_FLAG ,
85 WF_PROCESS_NAME = X_WF_PROCESS_NAME,
86 APPROVAL_OBJECT = X_APPROVAL_OBJECT,
87 ITEM_TYPE = X_ITEM_TYPE,
88 APPLICATION_USG_CODE = X_APPLICATION_USG_CODE
89 where WF_MAPPING_ID = X_WF_MAPPING_ID;
90
91 if (sql%notfound) then
92 raise no_data_found;
93 end if;
94
95 end UPDATE_ROW;
96
97 procedure LOAD_ROW
98 (
99 X_WF_MAPPING_ID IN NUMBER,
100 X_ACTIVE_FLAG IN VARCHAR2,
101 X_APPLICATION_USG_CODE IN VARCHAR2,
102 X_ITEM_TYPE IN VARCHAR2,
103 X_WF_PROCESS_NAME IN VARCHAR2,
104 X_APPROVAL_OBJECT IN VARCHAR2,
105 X_OWNER in VARCHAR2
106
107 )
108 is
109 l_user_id number := 0;
110 l_obj_verno number;
111 l_dummy_char varchar2(1);
112 l_row_id varchar2(100);
113 l_wfm_id number;
114
115
116 cursor c_obj_verno is
117 select object_version_number
118 from AHL_WF_MAPPING
119 where WF_MAPPING_ID = X_WF_MAPPING_ID;
120
121 cursor c_chk_wfm_exists is
122 select 'x'
123 from AHL_WF_MAPPING
124 where WF_MAPPING_ID = X_WF_MAPPING_ID;
125
126 cursor c_get_wfm_id is
127 select Ahl_Wf_Mapping_S.NEXTVAL
128 from dual;
129 begin
130 if X_OWNER = 'SEED' then
131 l_user_id := 1;
132 end if;
133
134 open c_chk_wfm_exists;
135 fetch c_chk_wfm_exists into l_dummy_char;
136 if c_chk_wfm_exists%notfound
137 then
138 close c_chk_wfm_exists;
139
140 if X_WF_MAPPING_ID is null then
141 open c_get_wfm_id;
142 fetch c_get_wfm_id into l_wfm_id;
143 close c_get_wfm_id;
144 else
145 l_wfm_id := X_WF_MAPPING_ID;
146 end if ;
147
148 l_obj_verno := 1;
149
150 AHL_WF_MAPPING_PKG.INSERT_ROW
151 (
152 X_ROWID => l_row_id,
153 X_WF_MAPPING_ID => l_wfm_id,
154 X_OBJECT_VERSION_NUMBER => l_obj_verno,
155 X_LAST_UPDATE_DATE => SYSDATE,
156 X_LAST_UPDATED_BY => l_user_id,
157 X_CREATION_DATE => SYSDATE,
158 X_CREATED_BY =>l_user_id,
159 X_LAST_UPDATE_LOGIN =>0,
160 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
161 X_WF_PROCESS_NAME => X_WF_PROCESS_NAME,
162 X_APPROVAL_OBJECT => X_APPROVAL_OBJECT,
163 X_ITEM_TYPE => X_ITEM_TYPE,
164 X_APPLICATION_USG_CODE => X_APPLICATION_USG_CODE
165 );
166
167 else
168 close c_chk_wfm_exists;
169 open c_obj_verno;
170 fetch c_obj_verno into l_obj_verno;
171 close c_obj_verno;
172
173 AHL_WF_MAPPING_PKG.UPDATE_ROW
174 (
175 X_WF_MAPPING_ID => X_WF_MAPPING_ID,
176 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
177 X_LAST_UPDATE_DATE => SYSDATE,
178 X_LAST_UPDATED_BY => l_user_id,
179 X_LAST_UPDATE_LOGIN => 0,
180 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
181 X_WF_PROCESS_NAME => X_WF_PROCESS_NAME,
182 X_APPROVAL_OBJECT => X_APPROVAL_OBJECT,
183 X_ITEM_TYPE => X_ITEM_TYPE ,
184 X_APPLICATION_USG_CODE => X_APPLICATION_USG_CODE
185 );
186
187 end if;
188 end LOAD_ROW;
189
190 procedure DELETE_ROW(
191 X_WF_MAPPING_ID in NUMBER
192 )
193 is
194 begin
195 delete from AHL_WF_MAPPING
196 where WF_MAPPING_ID = X_WF_MAPPING_ID;
197
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201 end DELETE_ROW;
202
203 end AHL_WF_MAPPING_PKG;