[Home] [Help]
PACKAGE BODY: APPS.BOMPOPIF
Source
1 package body BOMPOPIF as
2 /* $Header: BOMOPIFB.pls 120.3 2006/05/25 05:38:15 bbpatel ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMOPIFB.pls |
9 | DESCRIPTION : This is the main package used to validate and process |
10 | open interface Bill and Routing data. |
11 | Parameters: org_id organization_id |
12 | all_org process all orgs or just current org |
13 | 1 - all orgs |
14 | 2 - only org_id |
15 | val_rtg_flag validate routings |
16 | val_bom_flag validate boms |
17 | pro_rtg_flag process routings |
18 | pro_bom_flag process boms |
19 | del_rec_flag delete processed rows |
20 | prog_appid program application_id |
21 | prog_id program id |
22 | request_id request_id |
23 | user_id user id |
24 | login_id login id |
25 | Return: 1 if success |
26 | SQLCODE if failure |
27 | History: |
28 | 09/26/93 Shreyas Shah creation date |
29 | 03/27/97 Julie Maeyama Modified to call new packages |
30 | 01/15/05 Bhavnesh Patel Added Batch Id |
31 | |
32 +==========================================================================*/
33
34 /*--------------------------bmopinp_open_interface_process-------------------
35
36 NAME
37 bmopinp_open_interface_process
38 DESCRIPTION
39 Open Interface Import for null batch id .
40 RETURNS
41 0 if successful
42 SQLCODE if unsuccessful
43 NOTES
44 -----------------------------------------------------------------------------*/
45 FUNCTION bmopinp_open_interface_process (
46 org_id NUMBER,
47 all_org NUMBER := 1,
48 val_rtg_flag NUMBER := 1,
49 val_bom_flag NUMBER := 1,
50 pro_rtg_flag NUMBER := 1,
51 pro_bom_flag NUMBER := 1,
52 del_rec_flag NUMBER := 1,
53 prog_appid NUMBER := -1,
54 prog_id NUMBER := -1,
55 request_id NUMBER := -1,
56 user_id NUMBER := -1,
57 login_id NUMBER := -1,
58 err_text IN OUT NOCOPY VARCHAR2
59 )
60 return INTEGER
61 IS
62 l_return_status INTEGER := 0;
63 BEGIN
64 l_return_status := bmopinp_open_interface_process (
65 org_id => org_id,
66 all_org => all_org,
67 val_rtg_flag => val_rtg_flag,
68 val_bom_flag => val_bom_flag,
69 pro_rtg_flag => pro_rtg_flag,
70 pro_bom_flag => pro_bom_flag,
71 del_rec_flag => del_rec_flag,
72 prog_appid => prog_appid,
73 prog_id => prog_id,
74 request_id => request_id,
75 user_id => user_id,
76 login_id => login_id,
77 err_text => err_text,
78 p_batch_id => NULL
79 );
80
81 RETURN l_return_status;
82 END;
83
84 /*--------------------------bmopinp_open_interface_process-------------------
85
86 NAME
87 bmopinp_open_interface_process
88 DESCRIPTION
89 Open Interface Import for given batch id .
90 RETURNS
91 0 if successful
92 SQLCODE if unsuccessful
93 NOTES
94 -----------------------------------------------------------------------------*/
95 FUNCTION bmopinp_open_interface_process (
96 org_id NUMBER,
97 all_org NUMBER := 1,
98 val_rtg_flag NUMBER := 1,
99 val_bom_flag NUMBER := 1,
100 pro_rtg_flag NUMBER := 1,
101 pro_bom_flag NUMBER := 1,
102 del_rec_flag NUMBER := 1,
103 prog_appid NUMBER := -1,
104 prog_id NUMBER := -1,
105 request_id NUMBER := -1,
106 user_id NUMBER := -1,
107 login_id NUMBER := -1,
108 err_text IN OUT NOCOPY VARCHAR2,
109 p_batch_id IN NUMBER
110 )
111 return INTEGER
112 IS
113 err_msg VARCHAR2(2000);
114 ret_code NUMBER := 1;
115 l_func_return_code NUMBER;
116 l_msg_count NUMBER;
117 l_msg_data VARCHAR2(2000);
118 l_all_org VARCHAR2(1); -- boolean value
119 l_delete_rows VARCHAR2(1); -- boolean value
120 l_RoutingError exception;
121 l_BillError exception;
122 -- To collect stats for INTERFACE tables. Bug#3537226
123 l_schema VARCHAR2(30);
124 l_industry VARCHAR2(1);
125 l_status VARCHAR2(1);
126 BEGIN
127 /*
128 ** Import Routings
129 */
130 l_func_return_code := 0;
131
132 IF (val_rtg_flag = 1) THEN
133 /*
134 If all_org = 1 then
135 l_all_org := FND_API.G_TRUE;
136 Else
137 l_all_org := FND_API.G_FALSE;
138 End if;
139 If del_rec_flag = 1 then
140 l_delete_rows := FND_API.G_TRUE;
141 Else
142 l_delete_rows := FND_API.G_FALSE;
143 End if;
144 */
145 -- Collect the stats INTERFACE tables before procesing anything.
146 /* IF NVL(prog_id,-1) <> -1 THEN
147 IF FND_INSTALLATION.GET_APP_INFO('BOM',l_status, l_industry, l_schema) THEN
148 IF l_schema IS NOT NULL THEN
149 FND_STATS.GATHER_TABLE_STATS(l_schema,'BOM_OP_ROUTINGS_INTERFACE');
150 FND_STATS.GATHER_TABLE_STATS(l_schema,'BOM_OP_SEQUENCES_INTERFACE');
151 FND_STATS.GATHER_TABLE_STATS(l_schema,'BOM_OP_NETWORKS_INTERFACE');
152 FND_STATS.GATHER_TABLE_STATS(l_schema,'BOM_OP_RESOURCES_INTERFACE');
153 FND_STATS.GATHER_TABLE_STATS(l_schema,'BOM_SUB_OP_RESOURCES_INTERFACE');
154 END IF;
155 END IF;
156 IF FND_INSTALLATION.GET_APP_INFO('INV',l_status, l_industry, l_schema) THEN
157 IF l_schema IS NOT NULL THEN
158 FND_STATS.GATHER_TABLE_STATS(l_schema,'MTL_RTG_ITEM_REVS_INTERFACE');
159 END IF;
160 END IF;
161 END IF;
162 commented for bug 4350033 for performance issue */
163
164 ret_code := BOM_RTG_OPEN_INTERFACE.IMPORT_RTG
165 ( p_organization_id => org_id
166 , p_all_org => all_org
167 , p_delete_rows => del_rec_flag
168 , x_err_text => err_msg
169 , p_batch_id => p_batch_id
170 );
171 --bug:5235742 Success = 0, Warning if any entity's import failed with error = 1.
172 If ( ret_code NOT IN (0,1) ) Then
173 Raise l_RoutingError;
174 ELSIF ( ret_code = 1 ) THEN
175 l_func_return_code := 1;
176 End if;
177
178 /* Replaced the following call with API calling RBO
179 BOM_RoutingInterface_PUB.ImportRouting(
180 p_api_version => 1.0,
181 p_init_msg_list => FND_API.G_TRUE,
182 p_commit => FND_API.G_TRUE,
183 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
184 x_return_status => l_return_status,
185 x_msg_count => l_msg_count,
186 x_msg_data => l_msg_data,
187 p_organization_id => org_id,
188 p_all_organizations => l_all_org,
189 p_commit_rows => 500,
190 p_delete_rows => l_delete_rows
191 );
192
193 If l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
194 Raise l_RoutingError;
195 End if;
196 */
197 End if; -- Routing specified
198
199 /*
200 ** Import Bills
201 */
202
203 IF (val_bom_flag = 1) THEN
204 -- Collect the stats INTERFACE tables before procesing anything.
205 /* IF NVL(prog_id,-1) <> -1 THEN
206 IF FND_INSTALLATION.GET_APP_INFO('BOM',l_status, l_industry, l_schema) THEN
207 IF l_schema IS NOT NULL THEN
208 FND_STATS.GATHER_TABLE_STATS(l_schema,'BOM_BILL_OF_MTLS_INTERFACE');
209 FND_STATS.GATHER_TABLE_STATS(l_schema,'BOM_INVENTORY_COMPS_INTERFACE');
210 FND_STATS.GATHER_TABLE_STATS(l_schema,'BOM_COMPONENT_OPS_INTERFACE');
211 FND_STATS.GATHER_TABLE_STATS(l_schema,'BOM_REF_DESGS_INTERFACE');
212 FND_STATS.GATHER_TABLE_STATS(l_schema,'BOM_SUB_COMPS_INTERFACE');
213 END IF;
214 END IF;
215 IF FND_INSTALLATION.GET_APP_INFO('INV',l_status, l_industry, l_schema) THEN
216 IF l_schema IS NOT NULL THEN
217 FND_STATS.GATHER_TABLE_STATS(l_schema,'MTL_ITEM_REVISIONS_INTERFACE');
218 END IF;
219 END IF;
220 END IF;
221 commented for bug 4350033 for performance issue */
222 ret_code := Bom_Open_Interface_Api.Import_BOM (
223 org_id => org_id,
224 all_org => all_org,
225 user_id => user_id,
226 login_id => login_id,
227 prog_appid => prog_appid,
228 prog_id => prog_id,
229 req_id => request_id,
230 del_rec_flag => del_rec_flag,
231 err_text => err_msg,
232 p_batch_id => p_batch_id);
233 IF (ret_code NOT IN (0,1) ) THEN
234 Raise l_BillError;
235 ELSIF ( ret_code = 1 ) THEN
236 l_func_return_code := 1;
237 END IF;
238 /*
239 ret_code := Bom_Revision_Api.Import_Item_Revision (
240 org_id => org_id,
241 all_org => all_org,
242 user_id => user_id,
243 login_id => login_id,
244 prog_appid => prog_appid,
245 prog_id => prog_id,
246 req_id => request_id,
247 del_rec_flag => del_rec_flag,
248 err_text => err_msg);
249 IF (ret_code <> 0) THEN
250 Raise l_BillError;
251 END IF;
252
253 -- ASSEMBLY COMMENTS ????
254
255 ret_code := Bom_Component_Api.Import_Component (
256 org_id => org_id,
257 all_org => all_org,
258 user_id => user_id,
259 login_id => login_id,
260 prog_appid => prog_appid,
261 prog_id => prog_id,
262 req_id => request_id,
263 del_rec_flag => del_rec_flag,
264 err_text => err_msg);
265 IF (ret_code <> 0) THEN
266 Raise l_BillError;
267 END IF;
268
269 ret_code := Bom_Reference_Designator_Api.Import_Reference_Designator (
270 org_id => org_id,
271 all_org => all_org,
272 user_id => user_id,
273 login_id => login_id,
274 prog_appid => prog_appid,
275 prog_id => prog_id,
276 req_id => request_id,
277 del_rec_flag => del_rec_flag,
278 err_text => err_msg);
279 IF (ret_code <> 0) THEN
280 Raise l_BillError;
281 END IF;
282
283 ret_code := Bom_Substitute_Component_Api.Import_Substitute_Component (
284 org_id => org_id,
285 all_org => all_org,
286 user_id => user_id,
287 login_id => login_id,
288 prog_appid => prog_appid,
289 prog_id => prog_id,
290 req_id => request_id,
291 del_rec_flag => del_rec_flag,
292 err_text => err_msg);
293 IF (ret_code <> 0) THEN
294 Raise l_BillError;
295 END IF;
296 */
297 END IF;
298
299 RETURN(l_func_return_code);
300
301 EXCEPTION
302 When l_RoutingError then
303 err_text := 'BOMPOPIF(bmopinp) ' || substrb(err_msg,1,1500);
304 RETURN(ret_code);
305 /*
306 err_text := Fnd_Msg_Pub.Get(p_msg_index => Fnd_Msg_Pub.G_First,
307 p_encoded => FND_API.G_FALSE);
308 return(-1);
309 */
310 When l_BillError then
311 err_text := 'BOMPOPIF(bmopinp) ' || substrb(err_msg,1,1500);
312 RETURN(ret_code);
313 WHEN others THEN
314 err_text := 'BOMPOPIF(bmopinp) ' || substrb(SQLERRM,1,240);
315 RETURN(SQLCODE);
316 END bmopinp_open_interface_process;
317
318 END BOMPOPIF;