1 PACKAGE BODY CSP_ASL_PUB AS
2 /* $Header: cspgrecb.pls 115.8 2004/03/27 00:48:27 hhaugeru ship $ */
3 -- Start of Comments
4 -- Package name : CSP_ASL_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgrecb.pls';
12 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
13 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
14
15 PROCEDURE IMPORT_RECOMENDED_QUANTITIES(p_Api_Version_Number IN NUMBER,
16 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
17 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
18 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
19 p_item_id IN NUMBER,
20 p_item_segment1 IN VARCHAR2,
21 p_item_segment2 IN VARCHAR2,
22 p_item_segment3 IN VARCHAR2,
23 p_item_segment4 IN VARCHAR2,
24 p_item_segment5 IN VARCHAR2,
25 p_item_segment6 IN VARCHAR2,
26 p_item_segment7 IN VARCHAR2,
27 p_item_segment8 IN VARCHAR2,
28 p_item_segment9 IN VARCHAR2,
29 p_item_segment10 IN VARCHAR2,
30 p_item_segment11 IN VARCHAR2,
31 p_item_segment12 IN VARCHAR2,
32 p_item_segment13 IN VARCHAR2,
33 p_item_segment14 IN VARCHAR2,
34 p_item_segment15 IN VARCHAR2,
35 p_item_segment16 IN VARCHAR2,
36 p_item_segment17 IN VARCHAR2,
37 p_item_segment18 IN VARCHAR2,
38 p_item_segment19 IN VARCHAR2,
39 p_item_segment20 IN VARCHAR2,
40 p_organization_id IN NUMBER,
41 p_organization_name IN VARCHAR2,
42 p_organization_code IN VARCHAR2,
43 p_subinventory_code IN VARCHAR2,
44 p_recommended_max IN NUMBER,
45 p_recommended_min IN NUMBER,
46 x_return_status OUT NOCOPY VARCHAR2,
47 X_Msg_Count OUT NOCOPY NUMBER,
48 X_Msg_Data OUT NOCOPY VARCHAR2) IS
49 CURSOR csp_inv_org_code(inv_org_code VARCHAR2) IS
50 SELECT ORGANIZATION_ID
51 FROM MTL_PARAMETERS
52 WHERE ORGANIZATION_CODE=inv_org_code;
53
54 CURSOR csp_inv_org_name(inv_org_name VARCHAR2) IS
55 SELECT ORGANIZATION_ID
56 FROM HR_ALL_ORGANIZATION_UNITS
57 WHERE NAME = inv_org_name;
58
59 cursor get_delimiter is
60 select CONCATENATED_SEGMENT_DELIMITER
61 from FND_ID_FLEX_STRUCTURES_VL
62 where APPLICATION_ID=401
63 and ID_FLEX_CODE='MSTK';
64
65 cursor get_no_of_segments_enabled is
66 select count(*)
67 from FND_ID_FLEX_SEGMENTS_VL
68 where application_id= 401
69 and ID_FLEX_CODE='MSTK'
70 and ENABLED_FLAG='Y';
71
72 Type v_cur_type IS REF CURSOR;
73 l_cur v_cur_type;
74
75 l_api_version_number NUMBER := 1.0;
76 L_API_NAME CONSTANT VARCHAR2(30) := 'IMPORT_RECOMENDED_QUANTITIES';
77 l_organization_id NUMBER;
78 l_item_id NUMBER;
79 l_msg_count NUMBER;
80 l_header_id NUMBER;
81 l_sql_string VARCHAR2(3000);
82 I NUMBER;
83 l_dummy INTEGER;
84
85 l_delimiter varchar2(1);
86 l_number_of_segments number;
87 conc_segments varchar2(1000);
88 BEGIN
89 SAVEPOINT IMPORT_RECOMENDED_QUANTITIS;
90 IF NOT FND_API.Compatible_API_Call
91 ( l_api_version_number
92 , p_api_version_number
93 , L_API_NAME
94 , G_PKG_NAME
95 )
96 THEN
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 END IF;
99
100 -- Initialize message stack if required
101 IF FND_API.to_Boolean(p_init_msg_list)
102 THEN
103 FND_MSG_PUB.initialize;
104 END IF;
105
106 x_return_status := FND_API.G_RET_STS_SUCCESS;
107 IF p_organization_id IS NOT NULL THEN
108 l_organization_id := p_organization_id ;
109 ELSE IF p_organization_name IS NOT NULL THEN
110 OPEN csp_inv_org_name(p_organization_name);
111 LOOP
112 FETCH csp_inv_org_name INTO l_organization_id;
113 EXIT WHEN csp_inv_org_name% NOTFOUND;
114 END LOOP;
115 ELSE IF p_organization_code IS NOT NULL THEN
116 OPEN csp_inv_org_code(p_organization_code);
117 LOOP
118 FETCH csp_inv_org_code INTO l_organization_id;
119 EXIT WHEN csp_inv_org_code% NOTFOUND;
120 END LOOP;
121 END IF;
122 END IF;
123 END IF;
124 IF csp_inv_org_code% ISOPEN THEN
125 CLOSE csp_inv_org_code;
126 END IF;
127 IF csp_inv_org_name% ISOPEN THEN
128 CLOSE csp_inv_org_name;
129 END IF;
130
131 IF p_item_id IS NULL THEN
132 OPEN get_delimiter ;
133 FETCH get_delimiter into l_delimiter;
134 CLOSE get_delimiter ;
135 OPEN get_no_of_segments_enabled;
136 FETCH get_no_of_segments_enabled into l_number_of_segments;
137 CLOSE get_no_of_segments_enabled;
138 conc_segments := p_item_segment1;
139 IF l_number_of_segments > 1 THEN
140 conc_segments := conc_segments || l_delimiter || p_item_segment2;
141 END IF;
142 IF l_number_of_segments > 2 THEN
143 conc_segments := conc_segments || l_delimiter|| p_item_segment3;
144 END IF;
145 IF l_number_of_segments > 3 THEN
146 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
147 END IF;
148 IF l_number_of_segments > 4 THEN
149 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
150 END IF;
151 IF l_number_of_segments > 5 THEN
152 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
153 END IF;
154 IF l_number_of_segments > 6 THEN
155 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
156 END IF;
157 IF l_number_of_segments > 7 THEN
158 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
159 END IF;
160 IF l_number_of_segments > 8 THEN
161 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
162 END IF;
163 IF l_number_of_segments > 9 THEN
164 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
165 END IF;
166 IF l_number_of_segments > 10 THEN
167 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
168 END IF;
169 IF l_number_of_segments > 11 THEN
170 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
171 END IF;
172 IF l_number_of_segments > 12 THEN
173 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
174 END IF;
175 IF l_number_of_segments > 13 THEN
176 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
177 END IF;
178 IF l_number_of_segments > 14 THEN
179 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
180 END IF;
181 IF l_number_of_segments > 15 THEN
182 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
183 END IF;
184 IF l_number_of_segments > 16 THEN
185 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
186 END IF;
187 IF l_number_of_segments > 17 THEN
188 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
189 END IF;
190 IF l_number_of_segments > 18 THEN
191 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
192 END IF;
193 IF l_number_of_segments > 19 THEN
194 conc_segments := conc_segments || l_delimiter|| p_item_segment2;
195 END IF;
196 l_sql_string := 'SELECT INVENTORY_ITEM_ID from mtl_system_items_b_kfv where CONCATENATED_SEGMENTS =' || '''' || conc_segments || '''';
197 OPEN l_cur FOR
198 l_sql_string;
199 FETCH l_cur INTO l_item_id;
200 CLOSE l_cur;
201 ELSE
202 l_item_id := p_item_id ;
203 END IF;
204 IF l_item_id IS NOT NULL THEN
205 SELECT CSP_USAGE_HEADERS_S1.NEXTVAL INTO l_header_id FROM DUAL;
206 INSERT INTO CSP_USAGE_HEADERS (USAGE_HEADER_ID,INVENTORY_ITEM_ID,ORGANIZATION_ID,RECOMMENDED_MIN_QUANTITY,
207 RECOMMENDED_MAX_QUANTITY,EXTERNAL_DATA,PROCESS_STATUS,CREATED_BY,
208 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,HEADER_DATA_TYPE)
209 VALUES (l_header_id,l_item_id,l_organization_id, p_recommended_min,p_recommended_max,
210 'Y','M',G_USER_ID, SYSDATE, G_USER_ID, SYSDATE, G_LOGIN_ID,4);
211
212 IF p_subinventory_code IS NOT NULL THEN
213 INSERT INTO CSP_USAGE_HEADERS (USAGE_HEADER_ID,INVENTORY_ITEM_ID,ORGANIZATION_ID,RECOMMENDED_MIN_QUANTITY,
214 RECOMMENDED_MAX_QUANTITY,EXTERNAL_DATA,PROCESS_STATUS,CREATED_BY,
215 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,HEADER_DATA_TYPE,SECONDARY_INVENTORY)
216 VALUES (l_header_id,l_item_id,l_organization_id, p_recommended_min,p_recommended_max,
217 'Y','M',G_USER_ID, SYSDATE, G_USER_ID, SYSDATE, G_LOGIN_ID,1,p_subinventory_code);
218
219 END IF;
220 END IF;
221 IF FND_API.to_Boolean( p_commit )
222 THEN
223 COMMIT WORK;
224 END IF;
225 EXCEPTION
226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227 ROLLBACK TO IMPORT_RECOMENDED_QUANTITIS;
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 WHEN OTHERS THEN
230 ROLLBACK TO IMPORT_RECOMENDED_QUANTITIS;
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
232 END IMPORT_RECOMENDED_QUANTITIES;
233
234 PROCEDURE PURGE_OLD_RECOMMENDATIONS(P_Api_Version_Number IN NUMBER,
235 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
236 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
237 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
238 x_return_status OUT NOCOPY VARCHAR2,
239 x_Msg_Count OUT NOCOPY NUMBER,
240 x_Msg_Data OUT NOCOPY VARCHAR2) IS
241 l_api_version_number NUMBER := 1.0;
242 L_API_NAME CONSTANT VARCHAR2(30) := 'PURGE_OLD_RECOMMENDATIONS';
243 BEGIN
244 SAVEPOINT PURGE_OLD_RECOMMENDATIONS;
245 IF NOT FND_API.Compatible_API_Call
246 ( l_api_version_number
247 , p_api_version_number
248 , L_API_NAME
249 , G_PKG_NAME
250 )
251 THEN
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253 END IF;
254 -- Initialize message stack if required
255 IF FND_API.to_Boolean(p_init_msg_list)
256 THEN
257 FND_MSG_PUB.initialize;
258 END IF;
259 CSP_AUTO_ASLMSL_PVT.Purge_Planning_Data (
260 P_Api_Version_Number => l_api_version_number,
261 P_Init_Msg_List => FND_API.G_FALSE,
262 P_Commit => FND_API.G_FALSE,
263 P_validation_level => FND_API.G_VALID_LEVEL_FULL,
264 X_Return_Status => x_return_status,
265 X_Msg_Count => x_msg_count,
266 X_Msg_Data => x_msg_data);
267
268 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
269 IF FND_API.to_Boolean( p_commit )
270 THEN
271 COMMIT WORK;
272 END IF;
273 ELSE
274 x_return_status := FND_API.G_RET_STS_ERROR;
275 END IF;
276
277 EXCEPTION
278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
279 ROLLBACK TO PURGE_OLD_RECOMMENDATIONS;
280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
281 WHEN OTHERS THEN
282 ROLLBACK TO PURGE_OLD_RECOMMENDATIONS;
283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
284 END PURGE_OLD_RECOMMENDATIONS;
285 END CSP_ASL_PUB;
[Home] [Help]