[Home] [Help]
PACKAGE BODY: APPS.AK_DELETE_GRP
Source
1 package body AK_DELETE_GRP as
2 /* $Header: akgdeldb.pls 120.2 2005/09/15 22:27:04 tshort noship $ */
3
4 procedure DELETE (
5 p_business_object IN VARCHAR2,
6 p_appl_short_name IN VARCHAR2,
7 p_primary_key IN VARCHAR2 := FND_API.G_MISS_CHAR,
8 p_return_status OUT NOCOPY VARCHAR2,
9 p_delete_cascade IN VARCHAR2 := 'Y'
10 ) is
11 cursor l_get_appl_id_csr (short_name_param varchar2) is
12 select application_id
13 from fnd_application
14 where application_short_name = short_name_param;
15 l_api_name CONSTANT varchar2(30) := 'Delete';
16 l_appl_short_name varchar2(30);
17 l_appl_id NUMBER;
18 l_buffer_tbl AK_ON_OBJECTS_PUB.Buffer_Tbl_Type;
19 l_by_object varchar2(30);
20 l_index number;
21 l_index2 number;
22 l_msg_count number;
23 l_msg_data varchar2(2000);
24 l_return_boolean BOOLEAN;
25 l_return_status VARCHAR2(1);
26 l_string_pos number;
27 l_table_index number;
28 l_dum NUMBER;
29 l_object_pk varchar2(30) := FND_API.G_MISS_CHAR;
30 l_flow_pk_tbl AK_FLOW_PUB.Flow_PK_Tbl_Type;
31 l_region_pk_tbl AK_REGION_PUB.Region_PK_Tbl_Type;
32 l_custom_pk_tbl AK_CUSTOM_PUB.Custom_PK_Tbl_Type;
33 l_object_pk_tbl AK_OBJECT_PUB.Object_PK_Tbl_Type;
34 l_sec_pk_tbl AK_SECURITY_PUB.Resp_PK_Tbl_Type;
35 l_attr_pk_tbl AK_ATTRIBUTE_PUB.Attribute_PK_Tbl_Type;
36 l_queryobj_pk_tbl AK_QUERYOBJ_PUB.QueryObj_PK_Tbl_Type;
37
38 begin
39 --
40 -- Get object to be downloaded from argument
41 --
42 l_by_object := p_business_object;
43 --
44 -- Get application short name from argument
45 --
46 l_appl_short_name := upper(p_appl_short_name);
47
48 --
49 -- Get Primary key for the object
50 --
51 if (p_primary_key is not null and p_primary_key <> FND_API.G_MISS_CHAR) then
52 l_object_pk := p_primary_key;
53 else
54 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
55 FND_MESSAGE.SET_NAME('AK','AK_PRIMARY_KEY_INVALID');
56 FND_MESSAGE.SET_TOKEN('PRIMARY_KEY', p_primary_key);
57 FND_MSG_PUB.Add;
58 end if;
59 end if;
60
61 --
62 -- Set what error messages are to be displayed
63 --
64 --FND_MSG_PUB.G_MSG_LEVEL_THRESHOLD := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
65 FND_MSG_PUB.G_MSG_LEVEL_THRESHOLD := FND_MSG_PUB.G_MSG_LVL_ERROR;
66
67 -- Get application id from application short name
68 --
69 open l_get_appl_id_csr(l_appl_short_name);
70 fetch l_get_appl_id_csr into l_appl_id;
71 if (l_get_appl_id_csr%notfound) then
72 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
73 FND_MESSAGE.SET_NAME('AK','AK_APPL_SHORT_NAME_INVALID');
74 FND_MESSAGE.SET_TOKEN('APPL_SHORT_NAME', l_appl_short_name);
75 FND_MSG_PUB.Add;
76 end if;
77 close l_get_appl_id_csr;
78 raise FND_API.G_EXC_ERROR;
79 end if;
80 close l_get_appl_id_csr;
81
82 -- set Loading mode
83 AK_ON_OBJECTS_PUB.G_LOAD_MODE := 'DELETE';
84
85 -- set G_WRITE_HEADER to indicate writing the header or not
86 --
87 -- open l_check_table_csr;
88 -- fetch l_check_table_csr into l_dum;
89 -- if l_check_table_csr%NOTFOUND then
90 -- G_WRITE_HEADER := TRUE;
91 -- else
92 -- G_WRITE_HEADER := FALSE;
93 -- end if;
94 -- close l_check_table_csr;
95
96 --
97 -- Load buffer table with log file heading info to be written
98 -- to the log file
99 --
100 l_index := 1;
101 l_buffer_tbl(l_index) := '**********';
102
103 l_index := l_index + 1;
104 FND_MESSAGE.SET_NAME('AK','AK_START_DELETE_SESSION');
105 l_buffer_tbl(l_index) := FND_MESSAGE.GET;
106 l_index := l_index + 1;
107 l_buffer_tbl(l_index) := to_char(sysdate, 'DY MON DD YYYY HH24:MI:SS');
108 l_index := l_index + 1;
109 l_buffer_tbl(l_index) := '**********';
110 l_index := l_index + 1;
111 l_buffer_tbl(l_index) := ' ';
112
113 --dbms_output.put_line('Begin ' || l_by_object || ' at:' ||
114 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
115 --
116 -- Write heading info to a log file
117 --
118 AK_ON_OBJECTS_PVT.WRITE_LOG_FILE (
119 p_return_status => l_return_status,
120 p_buffer_tbl => l_buffer_tbl,
121 p_write_mode => AK_ON_OBJECTS_PUB.G_OVERWRITE
122 );
123
124 --
125 -- Clean up buffer table for use by other messages later
126 --
127 -- l_buffer_tbl.delete;
128 -- Initialize loader table index
129 if (G_WRITE_HEADER) then
130 AK_ON_OBJECTS_PVT.G_TBL_INDEX := 0;
131 end if;
132 --
133 -- Download data from database
134 --
135 if (upper(l_by_object) = 'REGION') then
136 AK_REGION_GRP.DELETE_REGION (
137 -- p_validation_level => FND_API.G_VALID_LEVEL_NONE,
138 p_api_version_number => 1.0,
139 p_init_msg_tbl => TRUE,
140 p_msg_count => l_msg_count,
141 p_msg_data => l_msg_data,
142 p_return_status => l_return_status,
143 p_region_application_id => l_appl_id,
144 p_region_code => upper(l_object_pk),
145 p_delete_cascade => p_delete_cascade
146 );
147
148 else
149 --dbms_output.put_line(upper(l_by_object) ||
150 -- ' is invalid - it must be FLOW, REGION, OBJECT, ATTRIBUTE or SECURITY');
151 FND_MESSAGE.SET_NAME('AK','AK_INVALID_BUSINESS_OBJECT');
152 FND_MESSAGE.SET_TOKEN('INVALID',l_by_object);
153 FND_MSG_PUB.Add;
154 end if;
155
156 p_return_status := l_return_status;
157 --dbms_output.put_line('Finish downloading at:' ||
158 -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
159
160 --dbms_output.put_line('Return status is: ' || l_return_status);
161 --dbms_output.put_line('Return message: ' || l_msg_data);
162
163 if FND_MSG_PUB.Count_Msg > 0 then
164 FND_MSG_PUB.Reset;
165 --dbms_output.put_line('Messages: ');
166 for i in 1 .. FND_MSG_PUB.Count_Msg loop
167 l_buffer_tbl(i + l_index) := FND_MSG_PUB.GET(p_encoded=>FND_API.G_FALSE);
168 end loop;
169 FND_MSG_PUB.Initialize;
170 end if;
171
172 --
173 -- Add ending to log file
174 --
175 l_index := nvl(l_buffer_tbl.last,0) + 1;
176 l_buffer_tbl(l_index) := '**********';
177 l_index := l_index + 1;
178 l_buffer_tbl(l_index) := to_char(sysdate, 'DY MON DD YYYY HH24:MI:SS');
179 l_index := l_index + 1;
180 l_buffer_tbl(l_index) := 'Finished processing application: '||l_appl_short_name;
181 if (p_primary_key is not null and p_primary_key <> FND_API.G_MISS_CHAR) then
182 l_index := l_index + 1;
183 l_buffer_tbl(l_index) := 'Primary key: '||p_primary_key;
184 end if;
185 l_index := l_index + 1;
186 FND_MESSAGE.SET_NAME('AK','AK_END_DELETE_SESSION');
187 l_buffer_tbl(l_index) := FND_MESSAGE.GET;
188
189 --
190 -- Write all messages and ending to a log file
191 --
192
193 AK_ON_OBJECTS_PVT.WRITE_LOG_FILE (
194 p_return_status => l_return_status,
195 p_buffer_tbl => l_buffer_tbl,
196 p_write_mode => AK_ON_OBJECTS_PUB.G_APPEND
197 );
198
199 --if (l_return_status = FND_API.G_RET_STS_SUCCESS) then
200 -- dbms_output.put_line('Log file has been printed out to screen');
201 --else
202 -- dbms_output.put_line('Failed to write log file to Global PL/SQL table');
203 --end if;
204
205 EXCEPTION
206 WHEN FND_API.G_EXC_ERROR THEN
207 p_return_status := FND_API.G_RET_STS_ERROR;
208 WHEN NO_DATA_FOUND THEN
209 p_return_status := FND_API.G_RET_STS_ERROR;
210 WHEN OTHERS THEN
211 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
212 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name,
213 SUBSTR (SQLERRM, 1, 240) );
214
215 end DELETE;
216
217 end AK_DELETE_GRP;