[Home] [Help]
PACKAGE BODY: APPS.WIP_WOL_PROCESSOR
Source
1 PACKAGE BODY wip_wol_processor AS
2 /* $Header: wipwolpb.pls 115.7 2002/11/29 14:22:38 rmahidha noship $ */
3
4 --copied from $INV_TOP/src/inltwv.ppc
5 PROCEDURE deriveItemRevision(p_header_id IN NUMBER) is
6 BEGIN
7 UPDATE wip_lpn_completions wlc
8 SET last_update_date = SYSDATE,
9 bom_revision =
10 (SELECT NVL(wlc.bom_revision, MAX(mir.revision))
11 FROM mtl_item_revisions mir
12 WHERE mir.organization_id = wlc.organization_id
13 AND mir.inventory_item_id = wlc.inventory_item_id
14 AND mir.effectivity_date <= SYSDATE
15 AND mir.effectivity_date =
16 (SELECT MAX(mir2.effectivity_date)
17 FROM mtl_item_revisions mir2
18 WHERE mir2.organization_id = wlc.organization_id
19 AND mir2.inventory_item_id = wlc.inventory_item_id
20 AND mir2.effectivity_date <= SYSDATE))
21 WHERE p_header_id = source_id
22 AND p_header_id <> header_id
23 AND bom_revision IS NULL
24 AND EXISTS (
25 SELECT 'X'
26 FROM mtl_system_items msi
27 WHERE msi.organization_id = wlc.organization_id
28 AND msi.inventory_item_id = wlc.inventory_item_id
29 AND msi.revision_qty_control_code = 2);
30 END deriveItemRevision;
31
32 --copied from $INV_TOP/src/inltwv.ppc
33 FUNCTION validateItemRevision(p_source_id IN NUMBER) return NUMBER is
34 BEGIN
35 UPDATE wip_lpn_completions wlc
36 SET last_update_date = sysdate,
37 error_code = 'HOOWAA'
38 WHERE p_source_id = source_id
39 AND source_id <> header_id
40 AND ((bom_revision is not null
41 AND ( (EXISTS (
42 SELECT 'item is under revision control'
43 FROM mtl_system_items msi
44 WHERE msi.organization_id = wlc.organization_id
45 AND msi.inventory_item_id = wlc.inventory_item_id
46 AND msi.revision_qty_control_code = 2)
47 AND NOT EXISTS (
48 SELECT 'revision is effective and not an open/hold eco'
49 FROM bom_bill_released_revisions_v bbrrv
50 WHERE bbrrv.inventory_item_id = wlc.inventory_item_id
51 AND bbrrv.organization_id = wlc.organization_id
52 AND bbrrv.revision = wlc.bom_revision
53 AND bbrrv.effectivity_date <= SYSDATE))
54 OR
55 (EXISTS (
56 SELECT 'item is not under revision control'
57 FROM mtl_system_items msi
58 WHERE msi.organization_id = wlc.organization_id
59 AND msi.inventory_item_id = wlc.inventory_item_id
60 AND msi.revision_qty_control_code = 1))))
61 OR
62 (bom_revision IS NULL
63 AND (EXISTS (
64 SELECT 'item is under revision control'
65 FROM mtl_system_items msi
66 WHERE msi.organization_id = wlc.organization_id
67 AND msi.inventory_item_id = wlc.inventory_item_id
68 AND msi.revision_qty_control_code = 2)
69 AND NOT EXISTS (
70 SELECT 'any effective revision'
71 FROM bom_bill_released_revisions_v bbrrv
72 WHERE bbrrv.inventory_item_id = wlc.inventory_item_id
73 AND bbrrv.organization_id = wlc.organization_id
74 AND bbrrv.effectivity_date <= SYSDATE))));
75 return SQL%ROWCOUNT;
76 END validateItemRevision;
77
78 FUNCTION Get_Component_ProjectSupply (
79 p_organization_id IN NUMBER,
80 p_project_id IN NUMBER,
81 p_task_id IN NUMBER,
82 p_wip_entity_id IN NUMBER,
83 p_supply_sub IN VARCHAR2,
84 p_supply_loc_id IN OUT NOCOPY NUMBER,
85 p_item_id IN NUMBER,
86 p_org_loc_control IN NUMBER) RETURN BOOLEAN IS
87
88 l_loc_id NUMBER := 0;
89 l_peg_flag VARCHAR2(1) := NULL;
90
91 CURSOR c1 IS
92 SELECT end_assembly_pegging_flag
93 FROM mtl_system_items
94 WHERE inventory_item_id = p_item_id
95 AND organization_id = p_organization_id;
96
97 BEGIN
98
99 OPEN c1;
100 FETCH c1 INTO l_peg_flag;
101 CLOSE c1;
102
103 IF ( l_peg_flag IN ( 'I' , 'X' ) ) THEN
104 IF (pjm_project_locator.Check_ItemLocatorControl(p_organization_id,
105 p_supply_sub, p_supply_loc_id, p_item_id, 0)) THEN
106
107 pjm_project_locator.Get_DefaultProjectLocator(p_organization_id,
108 p_supply_loc_id,
109 p_project_id,
110 p_task_id,
111 l_loc_id);
112 END IF;
113 ELSE
114 pjm_project_locator.Get_DefaultProjectLocator(p_organization_id,
115 p_supply_loc_id,
116 NULL,
117 NULL,
118 l_loc_id);
119
120 END IF;
121
122 PJM_UserProjectLocator_Pub.Get_UserProjectSupply(
123 p_item_id,
124 p_organization_id,
125 p_wip_entity_id,
126 l_loc_id);
127
128 IF l_loc_id <> 0 THEN
129 p_supply_loc_id := L_loc_id ;
130 END IF;
131 return(TRUE);
132 END Get_Component_ProjectSupply;
133
134 /* not currently used
135 --copy of pjm_project_locator.Get_Flow_ProjectSupply (PJMPLOC[S,B].pls)
136 FUNCTION generateLocatorIDs(p_header_id IN NUMBER,
137 p_org_id IN NUMBER,
138 p_wip_entity_id IN NUMBER,
139 p_project_id IN NUMBER,
140 p_task_id IN NUMBER) return boolean
141 IS
142 CURSOR c_items IS
143 SELECT inventory_item_id, subinventory_code, locator_id, rowid
144 FROM wip_lpn_completions
145 WHERE p_header_id = source_id
146 AND p_header_id <> header_id
147 AND locator_id is not null
148 ORDER BY operation_seq_num;
149
150 l_proj_ref_enabled NUMBER := 2;
151 l_org_loc_control NUMBER := 0;
152 l_success BOOLEAN := TRUE;
153 l_ROW_ID ROWID;
154
155 BEGIN
156 if (p_org_id is not null) then
157 BEGIN
158 SELECT NVL(mp.project_reference_enabled, 2),
159 mp.stock_locator_control_code
160 INTO l_proj_ref_enabled,
161 l_org_loc_control
162 FROM mtl_parameters mp
163 WHERE mp.organization_id = p_org_id;
164
165 EXCEPTION
166 when OTHERS then
167 return false;
168 END;
169 END if;
170
171 if ((l_proj_ref_enabled = 1) AND (p_project_id is not null)) then
172 FOR compRec IN c_items LOOP
173 l_success := Get_Component_ProjectSupply(
174 p_org_id,
175 p_project_id,
176 p_task_id,
177 p_wip_entity_id,
178 compRec.subinventory_code,
179 compRec.locator_id,
180 compRec.inventory_item_id,
181 l_org_loc_control);
182 if(l_success = false) then
183 return false;
184 else
185 if (compRec.locator_id <> 0) then
186 BEGIN
187 UPDATE wip_lpn_completions
188 SET (locator_id, item_project_id, item_task_id) =
189 (select inventory_location_id, project_id, task_id
190 from mtl_item_locations
191 where inventory_location_id = compRec.locator_id
192 and organization_id = p_org_id)
193 WHERE rowid = compRec.rowid;
194
195 EXCEPTION
196 when others then
197 return false;
198 END;
199 END if;
200 END if;
201 END loop;
202 END if;
203 return true;
204 END generateLocatorIDs;
205 */
206 PROCEDURE completeAssyItem(p_header_id IN NUMBER,
207 x_err_msg OUT NOCOPY VARCHAR2,
208 x_return_status OUT NOCOPY VARCHAR2)
209 IS
210 l_wlcRec wip_lpn_completions%ROWTYPE;
211 l_errNum NUMBER;
212 l_err_msg VARCHAR2(240);
213 l_wip_entity_id NUMBER;
214
215 BEGIN
216 SAVEPOINT preProcessing;
217 x_return_status := FND_API.G_RET_STS_ERROR;
218
219 SELECT *
220 INTO l_wlcRec
221 FROM wip_lpn_completions
222 WHERE header_id = p_header_id;
223
224 --default locator ids by the item's project
225 /* There is no place to enter the project in the mobile
226 * wol completion form. Thus there will never be an
227 * associated project for the completion and this
228 * check is not necessary. Note this commented code
229 * has not been tested and will need to be if pjm
230 * integration with the wol mobile form takes place
231 * if(l_wlcRec.item_project_id IS NOT NULL) then
232 * if(generateLocatorIDs(p_header_id,
233 * l_wlcRec.organization_id,
234 * l_wlcRec.wip_entity_id,
235 * l_wlcRec.item_project_id,
236 * l_wlcRec.item_task_id) = false) then
237 * x_err_msg := 'locator id defaulting error ' || x_err_msg;
238 * RAISE NO_DATA_FOUND;
239 * END if;
240 * END if;
241 */
242
243
244 --is this necessary?
245 if(validateItemRevision(l_wlcRec.header_id) <> 0) then
246 x_err_msg := fnd_message.get_string('WIP', 'TRANSACTION_FAILED') || ' ' || fnd_message.get_string('WIP', 'VALIDATE_ITEMS_ERROR');
247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
248 END if;
249
250 deriveItemRevision(l_wlcRec.header_id);
251
252 --charge resources for wol completion
253 --to do this we must insert into the wip_flow_schedules table
254 --which in turn inserts into the wip_entities table via a
255 --trigger
256
257 l_errNum := wip_flow_utilities.create_flow_schedule(
258 p_wip_entity_id => l_wlcRec.wip_entity_id,
259 p_organization_id => l_wlcRec.organization_id,
260 p_last_update_date => l_wlcRec.last_update_date,
261 p_last_updated_by => l_wlcRec.last_updated_by ,
262 p_creation_date => l_wlcRec.creation_date ,
263 p_created_by => l_wlcRec.created_by,
264 p_last_update_login => l_wlcRec.last_update_login,
265 p_request_id => null,
266 p_program_application_id => l_wlcRec.program_application_id,
267 p_program_id => l_wlcRec.program_id,
268 p_program_update_date => l_wlcRec.program_update_date,
269 p_primary_item_id => l_wlcRec.inventory_item_id,
270 p_class_code => l_wlcRec.accounting_class,
271 p_scheduled_start_date => l_wlcRec.transaction_date,
272 p_date_closed => null,
273 p_planned_quantity => 0,
274 p_quantity_completed => l_wlcRec.transaction_quantity,
275 p_quantity_scrapped => 0,
276 p_mps_sched_comp_date => null,
277 p_mps_net_quantity => null,
278 p_bom_revision => l_wlcRec.bom_revision,
279 p_routing_revision => l_wlcRec.routing_revision,
280 p_bom_revision_date => l_wlcRec.bom_revision_date,
281 p_routing_revision_date => l_wlcRec.routing_revision_date,
282 p_alternate_bom_designator => l_wlcRec.alternate_bom_designator,
283 p_alternate_routing_designator => l_wlcRec.alternate_routing_designator,
284 p_completion_subinventory => l_wlcRec.subinventory_code,
285 p_completion_locator_id => l_wlcRec.locator_id,
286 p_demand_class => null,
287 p_scheduled_completion_date => l_wlcRec.transaction_date,
288 p_schedule_group_id => null,
289 p_build_sequence => null,
290 p_line_id => null,
291 p_project_id => null,
292 p_task_id => null,
293 p_status => 1, --open
294 p_schedule_number => 'WMALPNWOL' || TO_CHAR(l_wlcRec.header_id),
295 p_scheduled_flag => 2, --not scheduled
296 p_unit_number => l_wlcRec.end_item_unit_number,
297 p_attribute_category => null,
298 p_attribute1 => null,
299 p_attribute2 => null,
300 p_attribute3 => null,
301 p_attribute4 => null,
302 p_attribute5 => null,
303 p_attribute6 => null,
304 p_attribute7 => null,
305 p_attribute8 => null,
306 p_attribute9 => null,
307 p_attribute10 => null,
308 p_attribute11 => null,
309 p_attribute12 => null,
310 p_attribute13 => null,
311 p_attribute14 => null,
312 p_attribute15 => null);
313
314 if(l_errNum = 0) then
315 x_err_msg := fnd_message.get_string('WIP', 'TRANSACTION_FAILED');
316 fnd_message.set_name('WIP', 'WIP_ERROR_FLOW_VALIDATION');
317 fnd_message.set_token('ENTITY1', to_char(l_wlcRec.wip_entity_id));
318 x_err_msg := x_err_msg || ' ' || fnd_message.get ;
319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
320 END IF;
321
322 UPDATE WIP_LPN_COMPLETIONS
323 SET wip_entity_id = l_wlcRec.wip_entity_id,
324 last_update_date = sysdate
325 WHERE l_wlcRec.header_id = header_id
326 AND l_wlcRec.header_id = source_id;
327
328 if(not wma_rsc_chrg.Charge_Resource_Overhead(p_header_id)) then
329 x_err_msg := fnd_message.get_string('WIP', 'TRANSACTION_FAILED') || ' ' || fnd_message.get_string('WIP', 'ERROR_RESOURCE_TXN');
333 --There is no way to enter a kanban card to
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
331 END if;
332
334 --complete against in the mobile form
335 --update kanban card status
336 -- if(l_wlcRec.kanban_card_id is not null) then
337 -- INV_Kanban_PVT.Update_Card_Supply_Status(x_return_status => l_err_msg,
338 -- p_kanban_card_id => l_wlcRec.kanban_card_id,
339 -- p_supply_status => INV_Kanban_PVT.G_Supply_Status_Full,
340 -- p_document_type => INV_Kanban_PVT.G_doc_type_Flow_Schedule,
341 -- p_document_header_id => l_wlcRec.wip_entity_id);
342 -- END if;
343 --
344 -- if(l_err_msg <> fnd_api.G_RET_STS_SUCCESS) then
345 -- x_err_msg := 'kanban error ' || l_err_msg || x_err_msg;
346 -- RAISE NO_DATA_FOUND;
347 -- END if;
348
349 x_return_status := FND_API.G_RET_STS_SUCCESS;--success!!
350
351 EXCEPTION
352 WHEN others Then
353 --rely on throwing code to set err_msg; also x_return_status defaulted to error, so nothing
354 --to do here except rollback.
355 ROLLBACK to SAVEPOINT preProcessing;
356 END completeAssyItem;
357 END wip_wol_processor;