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