[Home] [Help]
PACKAGE BODY: APPS.WIP_OSP
Source
1 PACKAGE BODY WIP_OSP AS
2 /* $Header: wipospvb.pls 120.26.12020000.8 2013/02/01 11:47:42 akuppa ship $ */
3
4 Additional_Quantity NUMBER := 0;
5
6 PROCEDURE RELEASE_VALIDATION
7 (P_Wip_Entity_Id NUMBER,
8 P_Organization_id NUMBER,
9 P_Repetitive_Schedule_Id NUMBER) IS
10
11 firstop NUMBER; /* First Op Seq of the Routing */
12 outside_proc_acct NUMBER; /* OSP account from Job or Schedule */
13 line_id NUMBER; /* Line Id for Repetitive Schedule */
14 x_osp_found BOOLEAN;
15 op_seq_num NUMBER := -1;
16 res_seq_num NUMBER := -1;
17 l_success number := 0 ;
18 -- l_po_receipt_found BOOLEAN := FALSE ;
19 -- we will use l_launch_req_import to determine whether we have to launch
20 -- Req Import concurrent program or not
21 l_launch_req_import NUMBER := WIP_CONSTANTS.NO;
22 l_itemkey VARCHAR2(240) := NULL;
23 l_primary_uom VARCHAR2(25);
24 l_osp_item_id NUMBER := -1; /*Added for the bug 2018510 */
25 l_ou_id number;
26 l_org_acct_ctxt VARCHAR2(30):= 'Accounting Information';
27 l_req_import VARCHAR2(25); -- Fix for bug 8919025(Fp 8850950)
28 l_req_enabled BOOLEAN; /*Bug 13768020*/
29 l_init_reqappr VARCHAR2(1);/*ER 4276433*/
30
31 /* Select Op Seq that have outside processing resources and have
32 * po_creation_time set to "At Job/Schedule Release"
33 */
34
35 /* Fixed Bug# 1883170. Defaulted po_creation_time to "At Operation" when
36 * po_creation_time is null for upgraded records
37 */
38
39
40 CURSOR Cdisc IS
41 SELECT WO.OPERATION_SEQ_NUM, WDJ.OUTSIDE_PROCESSING_ACCOUNT,
42 NVL(WDJ.PO_CREATION_TIME, 2) PO_CREATION_TIME,
43 WOR.RESOURCE_SEQ_NUM,
44 WO.COUNT_POINT_TYPE, WOR.AUTOCHARGE_TYPE,
45 decode (WO.PREVIOUS_OPERATION_SEQ_NUM, NULL, 'YES', 'NO') FIRST_OP,
46 WO.SCHEDULED_QUANTITY,
47 WE.ENTITY_TYPE
48 FROM WIP_OPERATION_RESOURCES WOR,
49 WIP_OPERATIONS WO,
50 WIP_DISCRETE_JOBS WDJ,
51 WIP_ENTITIES WE
52 WHERE WO.WIP_ENTITY_ID = P_Wip_Entity_Id
53 AND WO.ORGANIZATION_ID = P_Organization_Id
54 AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
55 AND WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
56 AND WDJ.ORGANIZATION_ID = WO.ORGANIZATION_ID
57 AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
58 AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
59 AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
60 AND WOR.AUTOCHARGE_TYPE IN (WIP_CONSTANTS.PO_RECEIPT,
61 WIP_CONSTANTS.PO_MOVE)
62 ORDER BY WO.OPERATION_SEQ_NUM;
63
64 CURSOR Crep IS
65 SELECT WO.OPERATION_SEQ_NUM,
66 WRS.OUTSIDE_PROCESSING_ACCOUNT,
67 WOR.RESOURCE_SEQ_NUM, WRS.LINE_ID,
68 NVL(WRS.PO_CREATION_TIME, 2) PO_CREATION_TIME,
69 WO.COUNT_POINT_TYPE, WOR.AUTOCHARGE_TYPE,
70 decode (WO.PREVIOUS_OPERATION_SEQ_NUM, NULL, 'YES', 'NO') FIRST_OP,
71 WO.SCHEDULED_QUANTITY
72 FROM WIP_OPERATION_RESOURCES WOR,
73 WIP_OPERATIONS WO,
74 WIP_REPETITIVE_SCHEDULES WRS
75 WHERE WO.WIP_ENTITY_ID = P_Wip_Entity_Id
76 AND WO.ORGANIZATION_ID = P_Organization_Id
77 AND WO.REPETITIVE_SCHEDULE_ID = P_Repetitive_Schedule_Id
78 AND WRS.ORGANIZATION_ID = WO.ORGANIZATION_ID
79 AND WRS.REPETITIVE_SCHEDULE_ID = WO.REPETITIVE_SCHEDULE_ID
80 AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
81 AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
82 AND WOR.REPETITIVE_SCHEDULE_ID = WO.REPETITIVE_SCHEDULE_ID
83 AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
84 AND WOR.AUTOCHARGE_TYPE IN (WIP_CONSTANTS.PO_RECEIPT,
85 WIP_CONSTANTS.PO_MOVE)
86 ORDER BY WO.OPERATION_SEQ_NUM;
87
88 CURSOR Cuom IS
89 SELECT msi.PRIMARY_UOM_CODE
90 FROM wip_entities we,
91 mtl_system_items msi
92 WHERE we.wip_entity_id = P_Wip_Entity_Id
93 AND we.organization_id = P_Organization_Id
94 AND msi.inventory_item_id = we.primary_item_id
95 AND msi.organization_id = we.organization_id;
96
97 BEGIN
98
99 /* Bug 13768020: if the 'WIP:Launch Requisition Import for PO Receipt' is set to 'N'
100 do not launch Requistion Import for PO Receipt resource*/
101 if(fnd_profile.value('WIP_OSP_REQ') = WIP_CONSTANTS.NO) then
102 l_req_enabled := FALSE;
103 else
104 l_req_enabled := TRUE;
105 end if;
106
107 IF P_Repetitive_Schedule_Id IS NULL THEN
108 FOR cdis_rec in Cdisc LOOP
109 IF(cdis_rec.operation_seq_num <> op_seq_num OR
110 cdis_rec.resource_seq_num <> res_seq_num) THEN
111
112 IF((cdis_rec.count_point_type <> WIP_CONSTANTS.NO_DIRECT) AND
113 ((cdis_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
114 OR (cdis_rec.first_op = 'YES' AND
115 cdis_rec.po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION)
116 )
117 ) THEN
118 CREATE_REQUISITION(
119 P_Wip_Entity_Id => P_Wip_Entity_Id,
120 P_Organization_Id => P_Organization_Id,
121 P_Repetitive_Schedule_Id => P_Repetitive_Schedule_Id,
122 P_Operation_Seq_Num => cdis_rec.OPERATION_SEQ_NUM,
123 P_Resource_Seq_Num => cdis_rec.RESOURCE_SEQ_NUM);
124 END IF;
125
126 -- Fix bug 2174078 EAM do not want to launch workflow
127 if cdis_rec.entity_type <> 6 then -- not eam workorder
128 /* If first operation start workflow process (Shipping and Receiving
129 * Intermediate) to notify supplier that intermediate has been shipped
130 */
131
132 -- Added the following for bug 2018510
133 begin
134 l_osp_item_id := -1;
135 SELECT br.PURCHASE_ITEM_ID
136 into l_osp_item_id
137 from wip_entities we,
138 wip_operation_resources wor,
139 bom_resources br
140 where we.wip_entity_id = p_wip_entity_id
141 and we.organization_id = p_organization_id
142 and wor.wip_entity_id = we.wip_entity_id
143 and wor.organization_id = we.organization_id
144 and nvl(wor.repetitive_schedule_id, -1)
145 = nvl(P_Repetitive_Schedule_Id, -1)
146 and wor.operation_seq_num = cdis_rec.operation_seq_num
147 and wor.resource_seq_num = cdis_rec.resource_seq_num
148 and wor.autocharge_type = WIP_CONSTANTS.PO_MOVE
149 and br.resource_id = wor.resource_id
150 and br.organization_id = wor.organization_id;
151 exception
152 when no_data_found then null;
153 end;
154
155 IF((cdis_rec.first_op = 'YES') and
156 (cdis_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE) and
157 (l_osp_item_id <> -1)) THEN
158
159 OPEN Cuom;
160 FETCH Cuom into l_primary_uom;
161 CLOSE Cuom;
162
163 -- Fixed bug 5569556. Need to set org context before launching
164 -- workflow because Req Import need this information.
165 -- get the OU, set context for MOAC.
166
167 select to_number(ORG_INFORMATION3) into l_ou_id
168 from HR_ORGANIZATION_INFORMATION
169 where ORGANIZATION_ID = p_organization_id
170 and ORG_INFORMATION_CONTEXT = l_org_acct_ctxt;
171
172 FND_REQUEST.SET_ORG_ID (l_ou_id);
173
174 wip_osp_shp_i_wf.StartWFProcess(
175 p_itemtype => 'WIPISHPW',
176 p_itemkey => l_itemkey,
177 p_workflow_process => 'INTERMEDIATE_SHIP',
178 p_wip_entity_id => P_Wip_Entity_Id,
179 p_rep_sched_id => P_Repetitive_Schedule_Id,
180 p_organization_id => P_Organization_Id,
181 p_primary_qty => cdis_rec.SCHEDULED_QUANTITY,
182 p_primary_uom => l_primary_uom,
183 p_op_seq_num => cdis_rec.OPERATION_SEQ_NUM );
184
185 if l_itemkey is not null then
186 update wip_operations
187 set wf_itemtype = 'WIPISHPW',
188 wf_itemkey = l_itemkey
189 where wip_entity_id = P_Wip_Entity_Id
190 and organization_id = P_Organization_Id
191 and operation_seq_num = cdis_rec.OPERATION_SEQ_NUM;
192 end if;
193 /* Fixed Bug# 1967211 */
194 /* Fix for Bug#2389789. Added po_creation_time and ospEnabled condition */
195 ELSIF (cdis_rec.autocharge_type = WIP_CONSTANTS.PO_RECEIPT and
196 /* Fix for bug 2777387: Reqimport should be spawned even if
197 PO Creation Time is set to At Operation, provided OSP resource
198 is attached to the first operation.
199 */
200 (cdis_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE or
201 (cdis_rec.po_creation_time = WIP_CONSTANTS.AT_OPERATION
202 and cdis_rec.first_op = 'YES'))
203 AND l_req_enabled) /*Bug 13768020: launch requistion interface for po receipt resource when 'WIP:Launch Requisition Import for PO Receipt' is set to Yes*/
204 /* and wip_common_wf_pkg.ospEnabled ) */ -- FP Bug 5125900, Base Bug 4529326, Commented out this 'and' condition
205 THEN
206 l_launch_req_import := WIP_CONSTANTS.YES;
207 /* Fix for bug 3127921: Adding elsif condition to launch reqimport
208 if po_creation_time is at job schedule release and a PO Move
209 resource is present in some operation other than the first
210 operation
211 */
212 ELSIF (cdis_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE and
213 cdis_rec.first_op <> 'YES' and
214 cdis_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE and
215 wip_common_wf_pkg.ospEnabled) then
216 l_launch_req_import := WIP_CONSTANTS.YES;
217 END IF;
218 ELSE -- if EAM work order
219 /* Fix for Bug#2389789. Added po_creation_time and ospEnabled condition */
220 if(cdis_rec.po_creation_time
221 IN(WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE,
222 WIP_CONSTANTS.AT_OPERATION))
223 /* and wip_common_wf_pkg.ospEnabled) */ -- FP Bug 5125900, Base Bug 4529326, Commented out this 'and' condition
224 THEN
225 -- l_po_receipt_found := TRUE;
226 -- We should always launch Req Import for EAM even if the PO
227 -- creation time is "At Operation".(got confirmation from Amit)
228 l_launch_req_import := WIP_CONSTANTS.YES;
229 end if ;
230 END IF; --check for eam work order
231 END IF;
232 op_seq_num := cdis_rec.operation_seq_num;
233 res_seq_num := cdis_rec.resource_seq_num;
234 END LOOP;
235 ELSE
236 FOR crep_rec in Crep LOOP
237 IF (crep_rec.operation_seq_num <> op_seq_num OR
238 crep_rec.resource_seq_num <> res_seq_num) THEN
239
240 IF(crep_rec.count_point_type <> WIP_CONSTANTS.NO_DIRECT AND
241 ((crep_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
242 OR (crep_rec.first_op = 'YES' AND
243 crep_rec.po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION)
244 )
245 ) THEN
246 CREATE_REQUISITION(
247 P_Wip_Entity_Id => P_Wip_Entity_Id,
248 P_Organization_Id => P_Organization_Id,
249 P_Repetitive_Schedule_Id => P_Repetitive_Schedule_Id,
250 P_Operation_Seq_Num => crep_rec.OPERATION_SEQ_NUM);
251 END IF;
252
253 -- Added the following for bug 2018510
254 BEGIN
255 l_osp_item_id := -1;
256 SELECT br.PURCHASE_ITEM_ID
257 into l_osp_item_id
258 from wip_entities we,
259 wip_operation_resources wor,
260 bom_resources br
261 where we.wip_entity_id = p_wip_entity_id
262 and we.organization_id = p_organization_id
263 and wor.wip_entity_id = we.wip_entity_id
264 and wor.organization_id = we.organization_id
265 and nvl(wor.repetitive_schedule_id, -1)
266 = nvl(P_Repetitive_Schedule_Id, -1)
267 and wor.operation_seq_num = crep_rec.operation_seq_num
268 and wor.resource_seq_num = crep_rec.resource_seq_num
269 and wor.autocharge_type = WIP_CONSTANTS.PO_MOVE
270 and br.resource_id = wor.resource_id
271 and br.organization_id = wor.organization_id;
272 EXCEPTION
273 when no_data_found then null;
274 END;
275 IF((crep_rec.first_op = 'YES') and
276 (crep_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE) and
277 (l_osp_item_id <> -1)) THEN
278
279 OPEN Cuom;
280 FETCH Cuom into l_primary_uom;
281 CLOSE Cuom;
282
283 -- Fixed bug 5569556. Need to set org context before launching
284 -- workflow because Req Import need this information.
285 -- get the OU, set context for MOAC.
286
287 select to_number(ORG_INFORMATION3) into l_ou_id
288 from HR_ORGANIZATION_INFORMATION
289 where ORGANIZATION_ID = p_organization_id
290 and ORG_INFORMATION_CONTEXT = l_org_acct_ctxt;
291
292 FND_REQUEST.SET_ORG_ID (l_ou_id);
293
294 wip_osp_shp_i_wf.StartWFProcess(
295 p_itemtype => 'WIPISHPW',
296 p_itemkey => l_itemkey,
297 p_workflow_process => 'INTERMEDIATE_SHIP',
298 p_wip_entity_id => P_Wip_Entity_Id,
299 p_rep_sched_id => P_Repetitive_Schedule_Id,
300 p_organization_id => P_Organization_Id,
301 p_primary_qty => crep_rec.SCHEDULED_QUANTITY,
302 p_primary_uom => l_primary_uom,
303 p_op_seq_num => crep_rec.OPERATION_SEQ_NUM );
304
305 if l_itemkey is not null then
306 update wip_operations
307 set wf_itemtype = 'WIPISHPW',
308 wf_itemkey = l_itemkey
309 where wip_entity_id = P_Wip_Entity_Id
310 and repetitive_schedule_id = P_Repetitive_Schedule_Id
311 and organization_id = P_Organization_Id
312 and operation_seq_num = crep_rec.OPERATION_SEQ_NUM;
313 end if;
314 /* Fixed Bug# 1967211 */
315 /* Fix for Bug#2389789. Added po_creation_time and ospEnabled condition */
316 ELSIF (crep_rec.autocharge_type = WIP_CONSTANTS.PO_RECEIPT AND
317 /* Fix for bug 2777387: Reqimport should be spawned even if
318 PO Creation Time is set to At Operation, provided OSP resource
319 is attached to the first operation.
320 */
321 (crep_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE or
322 (crep_rec.po_creation_time = WIP_CONSTANTS.AT_OPERATION AND
323 crep_rec.first_op = 'YES'))
324 AND l_req_enabled)/*Bug 13768020: launch requistion interface for po receipt resource when 'WIP:Launch Requisition Import for PO Receipt' is set to Yes*/
325 /* AND wip_common_wf_pkg.ospEnabled ) */ -- FP Bug 5125900, Base Bug 4529326, Commented out this 'and' condition
326 THEN
327 l_launch_req_import := WIP_CONSTANTS.YES;
328 /* Fix for bug 3127921: Adding elsif condition to launch reqimport
329 if po_creation_time is at job schedule release and a PO Move
330 resource is present in some operation other than the first
331 operation
332 */
333 ELSIF (crep_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE AND
334 crep_rec.first_op <> 'YES' AND
335 crep_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE AND
336 wip_common_wf_pkg.ospEnabled) THEN
337 l_launch_req_import := WIP_CONSTANTS.YES;
338 END IF;
339 END IF;
340 op_seq_num := crep_rec.operation_seq_num;
341 res_seq_num := crep_rec.resource_seq_num;
342 END LOOP;
343 END IF;
344
345 /* Fixed Bug# 1967211 */
346
347 -- if (l_po_receipt_found = TRUE ) then
348 if(l_launch_req_import = WIP_CONSTANTS.YES) then
349
350 -- get the OU, set context for MOAC
351 select to_number(ORG_INFORMATION3) into l_ou_id
352 from HR_ORGANIZATION_INFORMATION
353 where ORGANIZATION_ID = p_organization_id
354 and ORG_INFORMATION_CONTEXT = l_org_acct_ctxt;
355 FND_REQUEST.SET_ORG_ID (l_ou_id);
356
357 /*Fix for bug 8919025(Fp 8850950)*/
358 BEGIN
359 select reqimport_group_by_code
360 into l_req_import
361 from po_system_parameters_all
362 where org_id = l_ou_id;
363 EXCEPTION
364 WHEN NO_DATA_FOUND THEN
365 raise fnd_api.g_exc_unexpected_error;
366 END;
367
368 /*ER 4276433*/
369 if(fnd_profile.value('WIP_OSP_INITIATE_REQAPPR') = WIP_CONSTANTS.NO) then
370 l_init_reqappr := 'N';
371 else
372 l_init_reqappr := 'Y';
373 end if;
374
375 l_success := fnd_request.submit_request(
376 'PO', 'REQIMPORT', NULL, NULL, FALSE,'WIP', NULL, l_req_import, --Fix for 8919025(Fp 8850950)
377 NULL ,'N', l_init_reqappr , chr(0), NULL, NULL, NULL,
378 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
379 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
380 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
381 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
382 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
383 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
384 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
385 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
386 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
387 ) ;
388
389 end if ;
390
391
392 WIP_SF_STATUS.CREATE_OSP_STATUS (
393 p_org_id => P_Organization_id,
394 p_wip_entity_id => P_Wip_Entity_Id,
395 p_repetitive_sched_id => P_Repetitive_Schedule_Id );
396
397 END RELEASE_VALIDATION;
398
399 PROCEDURE CREATE_REQUISITION(
400 P_Wip_Entity_Id NUMBER,
401 P_Organization_Id NUMBER,
402 P_Repetitive_Schedule_Id NUMBER,
403 P_Operation_Seq_Num NUMBER,
404 P_Resource_Seq_Num IN NUMBER DEFAULT NULL,
405 P_Run_ReqImport IN NUMBER DEFAULT WIP_CONSTANTS.NO
406 ) IS
407
408 x_emp_found BOOLEAN;
409 x_no_loc_found BOOLEAN;
410 x_dummy VARCHAR2(2);
411 x_project_id NUMBER:=NULL;
412 x_task_id NUMBER:=NULL;
413 l_success number := 0 ;
414 x_released_revs_type NUMBER ;
415 x_released_revs_meaning Varchar2(30);
416 l_org_acct_ctxt VARCHAR2(30):= 'Accounting Information';
417 l_ou_id number;
418 l_req_import VARCHAR2(25); --Fix for bug 8919025(Fp 8850950)
419 l_po_creation_status VARCHAR(30); /*ER 4276433*/
420 l_init_reqappr VARCHAR2(1);/*ER 4276433*/
421
422 /* You cannot create a requisition unless
423 you are an employee. If this returns a row, you are OK */
424
425 CURSOR Cemp IS
426 SELECT 'x'
427 FROM FND_USER FU,
428 PER_PEOPLE_F PPF
429 WHERE FU.USER_ID = FND_GLOBAL.User_Id
430 AND FU.EMPLOYEE_ID = PPF.PERSON_ID;
431
432 /* You cannot move to queue of the first op if
433 * PO RECEIPT: The 1st op department has no location
434 * PO MOVE, Only 1 operation: The 1st op department has no location
435 * PO MOVE, Multiple ops: The 2nd op department has no location
436 */
437
438 -- If this cursor returns a row, we have an error condition.
439 CURSOR Cops IS
440 select 'X'
441 from wip_operations wo,bom_departments bd
442 where wo.department_id = bd.department_id
443 and wo.organization_id = bd.organization_id
444 and bd.location_id IS NULL
445 and level < 3
446 and exists (select 1
447 from wip_operation_resources wor
448 where wor.wip_entity_id = P_Wip_Entity_Id
449 and wor.organization_id = P_Organization_Id
450 and wor.operation_seq_num = P_Operation_Seq_num
451 and wor.autocharge_type =
452 decode(wo.operation_seq_num,P_Operation_Seq_num,
453 WIP_CONSTANTS.PO_RECEIPT,WIP_CONSTANTS.PO_MOVE))
454 start with wo.wip_entity_id = P_Wip_Entity_Id
455 and wo.organization_id = P_Organization_Id
456 and wo.operation_seq_num = P_Operation_Seq_num
457 connect by wo.wip_entity_id = P_Wip_Entity_Id
458 and wo.operation_seq_num = prior wo.next_operation_seq_num
459 and wo.organization_id = P_Organization_Id;
460
461 CURSOR Cproject IS
462 SELECT project_id , task_id
463 FROM WIP_DISCRETE_JOBS
464 WHERE organization_id = P_Organization_Id
465 AND wip_entity_id = P_Wip_Entity_Id;
466
467 BEGIN
468 OPEN Cemp;
469 FETCH Cemp INTO x_dummy;
470 x_emp_found := Cemp%FOUND;
471 CLOSE Cemp;
472
473 IF x_emp_found = FALSE THEN
474 FND_MESSAGE.SET_NAME('WIP', 'WIP_RELEASE_VALID_EMPLOYEE');
475 APP_EXCEPTION.RAISE_EXCEPTION;
476 END IF;
477
478 OPEN Cops;
479 FETCH Cops INTO X_dummy;
480 x_no_loc_found := Cops%FOUND;
481 CLOSE Cops;
482
483 IF x_no_loc_found = TRUE THEN
484 FND_MESSAGE.SET_NAME('WIP', 'WIP_RELEASE_PO_MOVE');
485 APP_EXCEPTION.RAISE_EXCEPTION;
486 END IF;
487
488 IF P_Repetitive_Schedule_Id IS NULL THEN
489 OPEN Cproject;
490 FETCH CProject INTO x_project_id,x_task_id;
491 CLOSE Cproject;
492 END IF;
493
494 wip_common.Get_Released_Revs_Type_Meaning (x_released_revs_type,
495 x_released_revs_meaning
496 );
497
498 /*ER 4276433*/
499 select NVL(po_creation_status, 'APPROVED')
500 into l_po_creation_status
501 from WIP_PARAMETERS
502 where organization_id = P_Organization_id;
503 /* Create the requisition */
504 IF Additional_Quantity > 0 THEN
505 /* This additional_quantity is set to the increased quantity
506 of the job that is modified by the user. */
507 INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
508 ( last_update_date, last_updated_by, creation_date,
509 created_by, last_update_login, request_id,
510 program_application_id, program_id, program_update_date,
511 org_id, /* Operating unit org */ preparer_id,
512 interface_source_code, authorization_status, source_type_code,
513 destination_organization_id, destination_type_code, item_id,
514 item_revision, uom_code, quantity, line_type_id, charge_account_id,
515 deliver_to_location_id, deliver_to_requestor_id, wip_entity_id,
516 wip_line_id, wip_operation_seq_num, wip_resource_seq_num,
517 bom_resource_id, wip_repetitive_schedule_id, need_by_date,
518 autosource_flag, group_code, suggested_buyer_id,
519 project_id, task_id, project_accounting_context
520 )
521 SELECT SYSDATE,
522 FND_GLOBAL.USER_ID,
523 SYSDATE,
524 FND_GLOBAL.USER_ID,
525 FND_GLOBAL.LOGIN_ID,
526 FND_GLOBAL.CONC_REQUEST_ID,
527 FND_GLOBAL.PROG_APPL_ID,
528 FND_GLOBAL.CONC_PROGRAM_ID,
529 SYSDATE,
530 TO_NUMBER(hoi.ORG_INFORMATION3) operating_unit,
531 fu.employee_id,
532 'WIP',
533 l_po_creation_status, /*ER 4276433*/
534 'VENDOR',
535 wor.organization_id,
536 'SHOP FLOOR',
537 br.purchase_item_id,
538 DECODE (msi.revision_qty_control_code,
539 1, null ,
540 2, decode(br.purchase_item_id,
541 we.primary_item_id, DECODE (we.entity_type,
542 WIP_CONSTANTS.REPETITIVE, wrs.bom_revision,
543 /*Fixed bug2174078 to support eam and osfm*/
544 wdj.bom_revision),
545 BOM_REVISIONS.GET_ITEM_REVISION_FN (
546 x_released_revs_meaning, -- eco_status
547 'ALL', -- examine_type
548 br.ORGANIZATION_ID, -- org_id
549 br.purchase_item_id, -- item_id
550 /*Fixed bug2174078 to support eam and osfm*/
551 decode (we.entity_type,-- rev_date
552 WIP_CONSTANTS.REPETITIVE, wrs.FIRST_UNIT_START_DATE,
553 /* Fixed for Bug1623063 */
554 /*Bug 14286109:user job released date instead of job start date to retrieve revision */
555 NVL(wdj.DATE_RELEASED,wdj.SCHEDULED_START_DATE))))),
556 msi.primary_uom_code,
557 DECODE(msi.outside_operation_uom_type,
558 'RESOURCE',
559 DECODE(wor.BASIS_TYPE, WIP_CONSTANTS.PER_ITEM,
560 round (wor.usage_rate_or_amount * additional_quantity,6),
561 round(wor.usage_rate_or_amount,6)),
562 'ASSEMBLY',
563 DECODE(wor.BASIS_TYPE,
564 WIP_CONSTANTS.PER_ITEM, additional_quantity,1)),
565 3,
566 DECODE(we.entity_type,
567 WIP_CONSTANTS.REPETITIVE, wrs.OUTSIDE_PROCESSING_ACCOUNT,
568 wdj.OUTSIDE_PROCESSING_ACCOUNT),
569 bd.location_id,
570 fu.employee_id,
571 wor.wip_entity_id,
572 DECODE(we.entity_type,
573 WIP_CONSTANTS.REPETITIVE, wrs.line_id,
574 NULL),
575 wor.operation_seq_num,
576 wor.resource_seq_num,
577 wor.resource_id,
578 P_Repetitive_Schedule_Id,
579 /* Fix Bug#2374334 */
580 /* Bug 4398047 commented following portion of the sql
581 DECODE(we.entity_type,
582 WIP_CONSTANTS.LOTBASED, bcd1.calendar_date,*/
583 /*Bug 14687249: when post processing leadtime is 0, use operation date directly to populate need_by_date, so it will not flip to the next working date.*/
584 Decode(NVL(msi.postprocessing_lead_time,0),
585 0,DECODE(we.entity_type,
586 WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
587 WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
588 DECODE(op1.next_operation_seq_num,
589 NULL, op1.last_unit_completion_date,
590 op2.first_unit_start_date)),
591 (bcd3.calendar_date +
592 (DECODE(we.entity_type,
593 WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
594 WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
595 DECODE(op1.next_operation_seq_num,
596 NULL, op1.last_unit_completion_date,
597 op2.first_unit_start_date)) -
598 TRUNC(DECODE(we.entity_type,
599 WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
600 WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
601 DECODE(op1.next_operation_seq_num,
602 NULL, op1.last_unit_completion_date,
603 op2.first_unit_start_date)))))),
604 'Y',
605 NULL,
606 msi.buyer_id,
607 x_project_id,
608 x_task_id,
609 DECODE(x_project_id,NULL,NULL,'Y')
610 FROM WIP_REPETITIVE_SCHEDULES wrs,
611 HR_ORGANIZATION_INFORMATION hoi,
612 -- BOM_CALENDAR_DATES bcd1, BOM_CALENDAR_DATES bcd2,-- Bug 4398047 removed bcd1 and bcd2
613 BOM_CALENDAR_DATES bcd3, BOM_CALENDAR_DATES bcd4,
614 FND_USER fu, BOM_DEPARTMENTS bd,
615 MTL_SYSTEM_ITEMS msi, BOM_RESOURCES br,
616 MTL_PARAMETERS mp, WIP_OPERATION_RESOURCES wor,
617 WIP_OPERATIONS op2, WIP_OPERATIONS op1,
618 WIP_DISCRETE_JOBS wdj, WIP_ENTITIES we
619 WHERE op1.organization_id = wor.organization_id
620 AND op1.wip_entity_id = wor.wip_entity_id
621 AND op1.operation_seq_num = wor.operation_seq_num
622 AND decode(nvl(P_Resource_Seq_Num, -1),
623 -1, -1, wor.resource_seq_num)
624 = decode(nvl(P_Resource_Seq_Num, -1), -1, -1, P_Resource_Seq_Num)
625 and NVL(wor.repetitive_schedule_id,-1) =
626 NVL(P_repetitive_schedule_id, -1)
627 and NVL(op1.repetitive_schedule_id,-1)=
628 NVL(P_repetitive_schedule_id, -1)
629 and op1.organization_id = P_organization_id
630 and op1.wip_entity_id = P_wip_entity_id
631 and op1.operation_seq_num = P_operation_seq_num
632 and NVL(op2.repetitive_schedule_id,-1)=
633 NVL(P_repetitive_schedule_id, -1)
634 and op2.organization_id = op1.organization_id
635 and op2.wip_entity_id = op1.wip_entity_id
636 and wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
637 WIP_CONSTANTS.PO_MOVE)
638 and op2.operation_seq_num = NVL(op1.next_operation_seq_num,
639 op1.operation_seq_num)
640 AND wor.organization_id = br.organization_id
641 AND wor.resource_id = br.resource_id
642 /* Additional requisitions are created only
643 for resources/assy of basis type ITEM. */
644 AND wor.basis_type = WIP_CONSTANTS.PER_ITEM
645 AND br.organization_id = msi.organization_id
646 AND br.purchase_item_id = msi.inventory_item_id
647 AND FND_GLOBAL.User_Id = fu.user_id
648 AND op1.organization_id = bd.organization_id
649 /* Fix for bug 3092030: Corrected condition to ensure we insert
650 correct deliver_to_location_id */
651 AND ( (wor.autocharge_type = WIP_CONSTANTS.PO_RECEIPT and
652 op1.department_id = bd.department_id)
653 OR (wor.autocharge_type = WIP_CONSTANTS.PO_MOVE and
654 op2.department_id = bd.department_id))
655 AND mp.organization_id = op1.organization_id
656 AND hoi.organization_id = op1.organization_id
657 AND hoi.ORG_INFORMATION_CONTEXT = l_org_acct_ctxt
658 AND we.wip_entity_id = op1.wip_entity_id
659 AND we.organization_id = op1.organization_id
660 AND wdj.wip_entity_id (+) = we.wip_entity_id
661 AND wdj.organization_id (+) = we.organization_id
662 AND wrs.repetitive_schedule_id (+) =
663 NVL(P_repetitive_schedule_id, -1)
664 AND wrs.organization_id (+) = we.organization_id
665 /* Bug 4398047 commenting out following portion of the sql
666 AND bcd2.calendar_code = mp.calendar_code -- Fix for Bug#2374334
667 AND bcd2.exception_set_id = mp.calendar_exception_set_id
668 AND bcd2.calendar_date = trunc(SYSDATE)
669 AND bcd1.calendar_code = mp.calendar_code
670 AND bcd1.exception_set_id = mp.calendar_exception_set_id
671 AND bcd1.seq_num = (bcd2.next_seq_num +
672 CEIL(NVL(msi.preprocessing_lead_time,0) +
673 NVL(msi.fixed_lead_time,0) +
674 (NVL(msi.variable_lead_time,0) *
675 DECODE(msi.outside_operation_uom_type,
676 'RESOURCE',
677 DECODE(wor.basis_type, WIP_CONSTANTS.PER_ITEM,
678 wor.usage_rate_or_amount * op1.scheduled_quantity,
679 wor.usage_rate_or_amount),
680 'ASSEMBLY',
681 DECODE(wor.basis_type, WIP_CONSTANTS.PER_ITEM,
682 op1.scheduled_quantity,
683 1)
684 )) +
685 NVL(msi.postprocessing_lead_time,0))) end commenting out for Bug 4398047 */
686 -- consider post processing lead time before inserting need-by-date
687 AND bcd4.calendar_code = mp.calendar_code
688 AND bcd4.exception_set_id = mp.calendar_exception_set_id
689 AND bcd4.calendar_date =
690 TRUNC(DECODE(we.entity_type,
691 WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
692 WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
693 DECODE(op1.next_operation_seq_num,
694 NULL, op1.last_unit_completion_date,
695 op2.first_unit_start_date)))
696 AND bcd3.calendar_code = mp.calendar_code
697 AND bcd3.exception_set_id = mp.calendar_exception_set_id
698 AND bcd3.seq_num = (bcd4.next_seq_num -
699 CEIL(NVL(msi.postprocessing_lead_time,0)));
700
701 ELSE
702 INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
703 ( last_update_date, last_updated_by, creation_date,
704 created_by, last_update_login, request_id,
705 program_application_id, program_id, program_update_date,
706 org_id, /* Operating unit org */ preparer_id,
707 interface_source_code, authorization_status, source_type_code,
708 destination_organization_id, destination_type_code, item_id,
709 item_revision, uom_code, quantity, line_type_id, charge_account_id,
710 deliver_to_location_id, deliver_to_requestor_id, wip_entity_id,
711 wip_line_id, wip_operation_seq_num, wip_resource_seq_num,
712 bom_resource_id, wip_repetitive_schedule_id, need_by_date,
713 autosource_flag, group_code, suggested_buyer_id,
714 project_id, task_id, project_accounting_context
715 )
716 SELECT SYSDATE,
717 FND_GLOBAL.USER_ID,
718 SYSDATE,
719 FND_GLOBAL.USER_ID,
720 FND_GLOBAL.LOGIN_ID,
721 FND_GLOBAL.CONC_REQUEST_ID,
722 FND_GLOBAL.PROG_APPL_ID,
723 FND_GLOBAL.CONC_PROGRAM_ID,
724 SYSDATE,
725 TO_NUMBER(hoi.ORG_INFORMATION3) operating_unit,
726 fu.employee_id,
727 'WIP',
728 l_po_creation_status, /*ER 4276433*/
729 'VENDOR',
730 wor.organization_id,
731 'SHOP FLOOR',
732 br.purchase_item_id,
733 DECODE (msi.revision_qty_control_code,
734 1, null ,
735 2, decode(br.purchase_item_id,
736 we.primary_item_id, DECODE (we.entity_type,
737 WIP_CONSTANTS.REPETITIVE,wrs.bom_revision,
738 wdj.bom_revision),
739 BOM_REVISIONS.GET_ITEM_REVISION_FN (
740 x_released_revs_meaning, -- eco_status
741 'ALL', -- examine_type
742 br.ORGANIZATION_ID, -- org_id
743 br.purchase_item_id, -- item_id
744 decode (we.entity_type,-- rev_date
745 WIP_CONSTANTS.REPETITIVE, wrs.FIRST_UNIT_START_DATE,
746 /*Bug 14286109:user job released date instead of job start date to retrieve revision */
747 NVL(wdj.DATE_RELEASED,wdj.SCHEDULED_START_DATE))))), /* Fixed Bug# 1623063 */
748 msi.primary_uom_code,
749 DECODE(msi.outside_operation_uom_type,
750 'RESOURCE',
751 DECODE(wor.BASIS_TYPE,
752 WIP_CONSTANTS.PER_ITEM, round (wor.usage_rate_or_amount *
753 op1.scheduled_quantity,6),
754 round(wor.usage_rate_or_amount,6)),
755 'ASSEMBLY',
756 DECODE(wor.BASIS_TYPE,
757 WIP_CONSTANTS.PER_ITEM, op1.scheduled_quantity,1)),
758 3,
759 DECODE(we.entity_type,
760 WIP_CONSTANTS.REPETITIVE, wrs.OUTSIDE_PROCESSING_ACCOUNT,
761 wdj.OUTSIDE_PROCESSING_ACCOUNT),
762 bd.location_id,
763 fu.employee_id,
764 wor.wip_entity_id,
765 DECODE(we.entity_type,
766 WIP_CONSTANTS.REPETITIVE, wrs.line_id,
767 NULL),
768 wor.operation_seq_num ,
769 wor.resource_seq_num,
770 wor.resource_id,
771 P_Repetitive_Schedule_Id,
772 /* Fix Bug#2374334 */
773 /* Bug 4398047 Commented out following portion of the sql
774 DECODE(we.entity_type,
775 WIP_CONSTANTS.LOTBASED, bcd1.calendar_date,*/
776 /*Bug 14687249: when post processing leadtime is 0, use operation date directly to populate need_by_date, so it will not flip to the next working date.*/
777 Decode(NVL(msi.postprocessing_lead_time,0),
778 0,DECODE(we.entity_type,
779 WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
780 WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
781 DECODE(op1.next_operation_seq_num,
782 NULL, op1.last_unit_completion_date,
783 op2.first_unit_start_date)),
784 (bcd3.calendar_date +
785 (DECODE(we.entity_type,
786 WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
787 WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
788 DECODE(op1.next_operation_seq_num,
789 NULL, op1.last_unit_completion_date,
790 op2.first_unit_start_date)) -
791 TRUNC(DECODE(we.entity_type,
792 WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
793 WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
794 DECODE(op1.next_operation_seq_num,
795 NULL, op1.last_unit_completion_date,
796 op2.first_unit_start_date)))))),
797 'Y',
798 NULL,
799 msi.buyer_id,
800 x_project_id,
801 x_task_id,
802 DECODE(x_project_id,NULL,NULL,'Y')
803 FROM WIP_REPETITIVE_SCHEDULES wrs, /*MTL_ITEM_REVISIONS mir, */
804 HR_ORGANIZATION_INFORMATION hoi,
805 -- BOM_CALENDAR_DATES bcd1, BOM_CALENDAR_DATES bcd2, -- Bug 4398047 Commented out bcd1 and bcd2
806 BOM_CALENDAR_DATES bcd3, BOM_CALENDAR_DATES bcd4,
807 FND_USER fu, BOM_DEPARTMENTS bd,
808 MTL_SYSTEM_ITEMS msi, BOM_RESOURCES br,
809 MTL_PARAMETERS mp, WIP_OPERATION_RESOURCES wor,
810 WIP_OPERATIONS op2, WIP_OPERATIONS op1,
811 WIP_DISCRETE_JOBS wdj, WIP_ENTITIES we
812 WHERE op1.organization_id = wor.organization_id
813 AND op1.wip_entity_id = wor.wip_entity_id
814 AND op1.operation_seq_num = wor.operation_seq_num
815 AND decode(nvl(P_Resource_Seq_Num, -1),
816 -1, -1, wor.resource_seq_num)
817 = decode(nvl(P_Resource_Seq_Num, -1),
818 -1, -1, P_Resource_Seq_Num)
819 and NVL(wor.repetitive_schedule_id,-1)=
820 NVL(P_repetitive_schedule_id, -1)
821 and NVL(op1.repetitive_schedule_id,-1)=
822 NVL(P_repetitive_schedule_id, -1)
823 and op1.organization_id = P_organization_id
824 and op1.wip_entity_id = P_wip_entity_id
825 and op1.operation_seq_num = P_operation_seq_num
826 and NVL(op2.repetitive_schedule_id,-1)=
827 NVL(P_repetitive_schedule_id, -1)
828 and op2.organization_id = op1.organization_id
829 and op2.wip_entity_id = op1.wip_entity_id
830 and wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
831 WIP_CONSTANTS.PO_MOVE)
832 and op2.operation_seq_num = NVL(op1.next_operation_seq_num,
833 op1.operation_seq_num)
834 AND wor.organization_id = br.organization_id
835 AND wor.resource_id = br.resource_id
836 AND br.organization_id = msi.organization_id
837 AND br.purchase_item_id = msi.inventory_item_id
838 AND FND_GLOBAL.User_Id = fu.user_id
839 AND op1.organization_id = bd.organization_id
840 /* Fix for bug 3092030: Corrected condition to ensure we insert
841 correct deliver_to_location_id */
842 AND ( (wor.autocharge_type = WIP_CONSTANTS.PO_RECEIPT and
843 op1.department_id = bd.department_id)
844 OR (wor.autocharge_type = WIP_CONSTANTS.PO_MOVE and
845 op2.department_id = bd.department_id))
846 AND mp.organization_id = op1.organization_id
847 AND hoi.organization_id = op1.organization_id
848 AND hoi.ORG_INFORMATION_CONTEXT = l_org_acct_ctxt
849 AND we.wip_entity_id = op1.wip_entity_id
850 AND we.organization_id = op1.organization_id
851 AND wdj.wip_entity_id (+) = we.wip_entity_id
852 AND wdj.organization_id (+) = we.organization_id
853 AND wrs.repetitive_schedule_id (+) =
854 NVL (P_repetitive_schedule_id, -1)
855 AND wrs.organization_id (+) = we.organization_id
856 /* Bug 4398047 commented out the following portion of the sql
857 AND bcd2.calendar_code = mp.calendar_code -- Fix for Bug#2374334
858 AND bcd2.exception_set_id = mp.calendar_exception_set_id
859 AND bcd2.calendar_date = trunc(SYSDATE)
860 AND bcd1.calendar_code = mp.calendar_code
861 AND bcd1.exception_set_id = mp.calendar_exception_set_id
862 AND bcd1.seq_num = (bcd2.next_seq_num +
863 CEIL(NVL(msi.preprocessing_lead_time,0) +
864 NVL(msi.fixed_lead_time,0) +
865 (NVL(msi.variable_lead_time,0) *
866 DECODE(msi.outside_operation_uom_type,
867 'RESOURCE',
868 DECODE(wor.basis_type, WIP_CONSTANTS.PER_ITEM,
869 wor.usage_rate_or_amount * op1.scheduled_quantity,
870 wor.usage_rate_or_amount),
871 'ASSEMBLY',
872 DECODE(wor.basis_type, WIP_CONSTANTS.PER_ITEM,
873 op1.scheduled_quantity,
874 1)
875 )) +
876 NVL(msi.postprocessing_lead_time,0))) end of commented sql for bug 4398047 */
877 -- consider post processing lead time before inserting need-by-date
878 AND bcd4.calendar_code = mp.calendar_code
879 AND bcd4.exception_set_id = mp.calendar_exception_set_id
880 AND bcd4.calendar_date =
881 TRUNC(DECODE(we.entity_type,
882 WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
883 WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
884 DECODE(op1.next_operation_seq_num,
885 NULL, op1.last_unit_completion_date,
886 op2.first_unit_start_date)))
887 AND bcd3.calendar_code = mp.calendar_code
888 AND bcd3.exception_set_id = mp.calendar_exception_set_id
889 AND bcd3.seq_num = (bcd4.next_seq_num -
890 CEIL(NVL(msi.postprocessing_lead_time,0)));
891 END IF;
892
893 IF (P_Run_ReqImport = WIP_CONSTANTS.YES) THEN
894
895 -- get the OU, set context for MOAC
896 select to_number(ORG_INFORMATION3) into l_ou_id
897 from HR_ORGANIZATION_INFORMATION
898 where ORGANIZATION_ID = p_organization_id
899 and ORG_INFORMATION_CONTEXT = l_org_acct_ctxt;
900 FND_REQUEST.SET_ORG_ID (l_ou_id);
901
902 /*Fix for bug 8919025(Fp 8850950) */
903 BEGIN
904 select reqimport_group_by_code
905 into l_req_import
906 from po_system_parameters_all
907 where org_id = l_ou_id;
908 EXCEPTION
909 WHEN NO_DATA_FOUND THEN
910 raise fnd_api.g_exc_unexpected_error;
911 END;
912
913 /*ER 4276433*/
914 if(fnd_profile.value('WIP_OSP_INITIATE_REQAPPR') = WIP_CONSTANTS.NO) then
915 l_init_reqappr := 'N';
916 else
917 l_init_reqappr := 'Y';
918 end if;
919
920 l_success := fnd_request.submit_request(
921 'PO', 'REQIMPORT', NULL, NULL, FALSE,'WIP', NULL, l_req_import, --Fix for bug 8919025(Fp 8850950)
922 NULL ,'N', l_init_reqappr , chr(0), NULL, NULL, NULL,
923 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
924 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
925 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
926 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
927 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
928 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
929 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
930 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
931 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
932 ) ;
933 END IF;
934
935 END CREATE_REQUISITION;
936
937 /* Create additional PO requisitions */
938 PROCEDURE CREATE_ADDITIONAL_REQ
939 (P_Wip_Entity_Id NUMBER,
940 P_Organization_id NUMBER,
941 P_Repetitive_Schedule_Id NUMBER,
942 P_Added_Quantity NUMBER,
943 P_Op_Seq NUMBER default null) IS
944
945 BEGIN
946
947 Additional_Quantity:=P_Added_Quantity;
948
949 IF P_Op_Seq is NULL then
950
951 RELEASE_VALIDATION(P_Wip_Entity_Id,
952 P_Organization_id,
953 P_Repetitive_Schedule_Id);
954 ELSE
955 CREATE_REQUISITION(
956 P_Wip_Entity_Id => P_Wip_Entity_Id,
957 P_Organization_Id => P_Organization_id,
958 P_Repetitive_Schedule_Id => P_Repetitive_Schedule_Id,
959 P_Operation_Seq_Num => P_Op_Seq);
960 END IF;
961
962 Additional_Quantity:=0; -- As this is a global variable it has to be
963 -- reinitialized immediately.
964
965 END CREATE_ADDITIONAL_REQ;
966
967 FUNCTION PO_REQ_EXISTS (p_wip_entity_id in NUMBER,
968 p_rep_sched_id in NUMBER,
969 p_organization_id in NUMBER,
970 p_op_seq_num in NUMBER default NULL,
971 p_entity_type in NUMBER
972 ) RETURN BOOLEAN IS
973 /* Bug 4057595 - Modified the following cursors to consider
974 Finally Closed POR/PO/PO LINE/SHIPMENT.
975 */
976
977 CURSOR disc_check_po_req_cur IS
978 SELECT 'PO/REQ Linked'
979 FROM PO_RELEASES_ALL PR,
980 PO_HEADERS_ALL PH,
981 PO_DISTRIBUTIONS_ALL PD,
982 PO_LINE_LOCATIONS_ALL PLL /* Added as part of Bug2308832 */ /* Fixed bug 3115844 */
983 WHERE pd.po_line_id IS NOT NULL
984 AND pd.line_location_id IS NOT NULL
985 AND PD.WIP_ENTITY_ID = p_wip_entity_id
986 AND PD.DESTINATION_ORGANIZATION_ID = p_organization_id
987 AND (p_op_seq_num is NULL OR PD.WIP_OPERATION_SEQ_NUM = p_op_seq_num)
988 AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
989 AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID /* Added as part of Bug 2308832 */
990 AND PR.PO_RELEASE_ID (+) = PD.PO_RELEASE_ID
991 -- check cancel flag at shipment level instead of at header level
992 -- because PO will cancel upto shipment level
993 AND (pll.cancel_flag IS NULL OR
994 pll.cancel_flag = 'N')
995 -- AND ((PH.TYPE_LOOKUP_CODE = 'STANDARD' AND
996 -- nvl(PH.CANCEL_FLAG,'N') ='N')
997 -- OR
998 -- (PH.TYPE_LOOKUP_CODE = 'BLANKET' AND
999 -- PR.PO_RELEASE_ID = PD.PO_RELEASE_ID AND
1000 -- nvl(PR.CANCEL_FLAG, 'N') = 'N'))
1001 /*Added as part of Bug 2308832 */
1002 AND (PLL.QUANTITY_RECEIVED < (PLL.QUANTITY-PLL.QUANTITY_CANCELLED))
1003 AND nvl(pll.closed_code,'OPEN') <> 'FINALLY CLOSED'
1004 UNION ALL
1005 SELECT 'PO/REQ Linked'
1006 FROM PO_REQUISITION_LINES_ALL PRL
1007 WHERE PRL.WIP_ENTITY_ID = p_wip_entity_id
1008 AND PRL.DESTINATION_ORGANIZATION_ID = p_organization_id
1009 AND (p_op_seq_num is NULL OR
1010 PRL.WIP_OPERATION_SEQ_NUM = p_op_seq_num)
1011 AND nvl(PRL.cancel_flag, 'N') = 'N'
1012 AND PRL.LINE_LOCATION_ID is NULL /* added as part of 2740352 */
1013 AND NVL(PRL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED' /* BUG:13019044 Added condition of Finally Closed requisition lines */
1014 UNION ALL
1015 SELECT 'PO/REQ Linked'
1016 FROM PO_REQUISITIONS_INTERFACE_ALL PRI
1017 WHERE PRI.WIP_ENTITY_ID = p_wip_entity_id
1018 AND PRI.DESTINATION_ORGANIZATION_ID = p_organization_id
1019 AND (p_op_seq_num is NULL OR PRI.WIP_OPERATION_SEQ_NUM = p_op_seq_num);
1020
1021
1022 CURSOR rep_check_po_req_cur IS
1023 SELECT 'PO/REQ Linked'
1024 FROM PO_RELEASES_ALL PR,
1025 PO_HEADERS_ALL PH,
1026 PO_DISTRIBUTIONS_ALL PD,
1027 PO_LINE_LOCATIONS_ALL PLL
1028 /* Fixed bug 3115844 */
1029 WHERE pd.po_line_id IS NOT NULL
1030 AND pd.line_location_id IS NOT NULL
1031 AND PD.WIP_ENTITY_ID = p_wip_entity_id
1032 AND PD.DESTINATION_ORGANIZATION_ID = p_organization_id
1033 AND PD.WIP_REPETITIVE_SCHEDULE_ID = p_rep_sched_id
1034 AND (p_op_seq_num is NULL OR PD.WIP_OPERATION_SEQ_NUM = p_op_seq_num)
1035 AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
1036 AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1037 AND PR.PO_RELEASE_ID (+) = PD.PO_RELEASE_ID
1038 -- check cancel flag at shipment level instead of at header level
1039 -- because PO will cancel upto shipment level
1040 AND (pll.cancel_flag IS NULL OR
1041 pll.cancel_flag = 'N')
1042 AND nvl(pll.closed_code,'OPEN') <> 'FINALLY CLOSED'
1043 -- AND ((PH.TYPE_LOOKUP_CODE = 'STANDARD' AND
1044 -- nvl(PH.CANCEL_FLAG, 'N') = 'N' )
1045 -- OR
1046 -- (PH.TYPE_LOOKUP_CODE = 'BLANKET' AND
1047 -- nvl(PR.CANCEL_FLAG, 'N') = 'N'))
1048 UNION ALL
1049 SELECT 'PO/REQ Linked'
1050 FROM PO_REQUISITION_LINES_ALL PRL
1051 WHERE PRL.WIP_ENTITY_ID = p_wip_entity_id
1052 AND PRL.DESTINATION_ORGANIZATION_ID = p_organization_id
1053 AND PRL.WIP_REPETITIVE_SCHEDULE_ID = p_rep_sched_id
1054 AND (p_op_seq_num is NULL OR
1055 PRL.WIP_OPERATION_SEQ_NUM = p_op_seq_num)
1056 AND nvl(PRL.cancel_flag, 'N') = 'N'
1057 AND NVL(PRL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED' /* BUG:13019044 Added condition of Finally Closed requisition lines */
1058 UNION ALL
1059 SELECT 'PO/REQ Linked'
1060 FROM PO_REQUISITIONS_INTERFACE_ALL PRI
1061 WHERE PRI.WIP_ENTITY_ID = p_wip_entity_id
1062 AND PRI.DESTINATION_ORGANIZATION_ID = p_organization_id
1063 AND PRI.WIP_REPETITIVE_SCHEDULE_ID = p_rep_sched_id
1064 AND (p_op_seq_num is NULL OR PRI.WIP_OPERATION_SEQ_NUM = p_op_seq_num);
1065
1066 po_req_exist VARCHAR2(20);
1067
1068 begin
1069 IF(p_entity_type = WIP_CONSTANTS.REPETITIVE) THEN
1070 OPEN rep_check_po_req_cur;
1071 FETCH rep_check_po_req_cur INTO po_req_exist;
1072
1073 IF (rep_check_po_req_cur%FOUND) THEN
1074 CLOSE rep_check_po_req_cur;
1075 RETURN TRUE;
1076 ELSE
1077 CLOSE rep_check_po_req_cur;
1078 RETURN FALSE;
1079 END IF;
1080 ELSE /*FOR DISCRETE, OSFM, AND EAM*/
1081 OPEN disc_check_po_req_cur;
1082 FETCH disc_check_po_req_cur INTO po_req_exist;
1083
1084 IF (disc_check_po_req_cur%FOUND) THEN
1085 CLOSE disc_check_po_req_cur;
1086 return TRUE;
1087 ELSE
1088 CLOSE disc_check_po_req_cur;
1089 return FALSE;
1090 END IF;
1091 END IF; -- End check POs and REQs
1092 END PO_REQ_EXISTS;
1093
1094 FUNCTION ConvertToPrimaryMoveQty (p_item_id NUMBER,
1095 p_organization_id NUMBER,
1096 p_quantity NUMBER,
1097 p_uom_code VARCHAR2,
1098 p_primary_uom_code VARCHAR2,
1099 p_usage_rate_or_amount NUMBER
1100 ) RETURN NUMBER IS
1101
1102 l_primary_qty NUMBER;
1103 l_move_qty NUMBER;
1104
1105 BEGIN
1106 --Bug#14083389: Check if the value returned by inv_Convert is -99999, only then convert it. If the value returned is -99999, proper UOM conversion has not been defined
1107 /* Bug#16239604(FP of bug#9276689): Converting the actual quantity instead of 1 quantity */
1108 select decode (msi.outside_operation_uom_type,
1109 'ASSEMBLY', inv_convert.inv_um_convert(
1110 p_item_id, -- item_id
1111 NULL, -- precision
1112 p_quantity, -- from_quantity
1113 NULL, -- from_unit
1114 NULL, -- to_unit
1115 p_uom_code, -- from_name
1116 p_primary_uom_code), -- to_name
1117 inv_convert.inv_um_convert(
1118 p_item_id, -- item_id
1119 NULL, -- precision
1120 decode (nvl(p_usage_rate_or_amount, 0) ,
1121 0, 0,
1122 p_quantity/p_usage_rate_or_amount), -- from_quantity
1123 NULL, -- from_unit
1124 NULL, -- to_unit
1125 p_uom_code, -- from_name
1126 p_primary_uom_code))
1127 into l_move_qty
1128 from mtl_system_items msi
1129 where msi.inventory_item_id = p_item_id
1130 and msi.organization_id = p_organization_id;
1131
1132 --apparently this value indicates an error condition
1133 if(l_move_qty = -99999) then
1134 return null;
1135 end if;
1136 return l_move_qty;
1137 END ConvertToPrimaryMoveQty;
1138
1139 FUNCTION IS_ORDER_OPEN(approved_flag VARCHAR2 := NULL,
1140 closed_code VARCHAR2 := NULL,
1141 line_closed_status VARCHAR2 := NULL,
1142 cancel_flag VARCHAR2 := NULL,
1143 frozen_flag VARCHAR2 := NULL,
1144 user_hold_flag VARCHAR2 := NULL,
1145 line_expiration_date DATE := NULL,
1146 line_cancel_flag VARCHAR2 := NULL
1147 )Return VARCHAR2 AS
1148
1149 BEGIN
1150 if((approved_flag = 'Y') AND
1151 (nvl(closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED')) AND
1152 (nvl(line_closed_status,'OPEN') not in ('FINALLY CLOSED', 'CLOSED'))AND
1153 (nvl(cancel_flag,'N') = 'N') AND
1154 (nvl(frozen_flag,'N') = 'N') AND
1155 (nvl(user_hold_flag, 'N') = 'N') AND
1156 (trunc(nvl(line_expiration_date, sysdate + 1)) > trunc(sysdate)) AND
1157 (nvl(line_cancel_flag,'N') = 'N')) then
1158 return 'Y';
1159 else
1160 return 'N';
1161 end if;
1162 END IS_ORDER_OPEN;
1163
1164 PROCEDURE ARE_QA_PLANS_AVAILABLE(
1165 P_AssemblyItemNumber IN VARCHAR2 DEFAULT NULL,
1166 P_VendorName IN VARCHAR2 DEFAULT NULL,
1167 P_WipEntityName IN VARCHAR2 DEFAULT NULL,
1168 P_BasePoNum IN VARCHAR2 DEFAULT NULL,
1169 P_SupplierItemNumber IN VARCHAR2 DEFAULT NULL,
1170 P_AssemblyPrimaryUom IN VARCHAR2 DEFAULT NULL,
1171 P_Uom IN VARCHAR2 DEFAULT NULL,
1172 P_WipLineCode IN VARCHAR2 DEFAULT NULL,
1173 P_BomRevision IN VARCHAR2 DEFAULT NULL,
1174 P_StartDate IN DATE DEFAULT NULL,
1175 P_PoReleaseNumber IN NUMBER DEFAULT NULL,
1176 P_OrganizationId IN NUMBER DEFAULT NULL,
1177 P_WipEntityType IN NUMBER DEFAULT NULL,
1178 P_WipEntityId IN NUMBER DEFAULT NULL,
1179 P_WipRepetitiveScheduleId IN NUMBER DEFAULT NULL,
1180 P_ResourceSeqNum IN NUMBER DEFAULT NULL,
1181 P_ItemId IN NUMBER DEFAULT NULL,
1182 P_AssemblyItemId IN NUMBER DEFAULT NULL,
1183 P_WipOperationSeqNum IN NUMBER DEFAULT NULL,
1184 R_QaAvailable OUT NOCOPY VARCHAR2) IS
1185
1186 l_qty NUMBER;
1187 l_usage wip_operation_resources.usage_rate_or_amount%TYPE;
1188 l_rev VARCHAR2(30);
1189 x_released_revs_type NUMBER ;
1190 x_released_revs_meaning Varchar2(30);
1191
1192
1193 BEGIN
1194 SELECT wor.usage_rate_or_amount
1195 INTO l_usage
1196 FROM wip_operation_resources wor
1197 WHERE wor.wip_entity_id = P_WipEntityId
1198 and NVL(wor.repetitive_schedule_id, -1) =
1199 NVL(P_WipRepetitiveScheduleId, -1)
1200 and wor.operation_seq_num = P_WipOperationSeqNum
1201 and wor.organization_id = P_OrganizationId
1202 and wor.resource_seq_num = P_ResourceSeqNum;
1203
1204 l_qty := WIP_OSP.ConvertToPrimaryMoveQty(
1205 p_item_id => P_ItemId,
1206 p_organization_id => P_OrganizationId,
1207 p_quantity => P_AssemblyItemId,
1208 p_uom_code => P_Uom,
1209 p_primary_uom_code => P_AssemblyPrimaryUom,
1210 p_usage_rate_or_amount => l_usage);
1211
1212 wip_common.Get_Released_Revs_Type_Meaning (x_released_revs_type,
1213 x_released_revs_meaning
1214 );
1215
1216
1217 IF(P_ItemId = P_AssemblyItemId) THEN
1218 l_rev := P_BomRevision;
1219 ELSE
1220 l_rev := BOM_REVISIONS.GET_ITEM_REVISION_FN(
1221 eco_status => x_released_revs_meaning,
1222 examine_type => 'ALL',
1223 org_id => P_OrganizationId,
1224 item_id => P_ItemId,
1225 rev_date => P_StartDate);
1226 END IF;
1227
1228 R_QaAvailable := QA_SS_OSP.are_osp_plans_applicable(
1229 P_Item_Number => P_AssemblyItemNumber,
1230 P_Supplier => P_VendorName,
1231 P_Wip_Entity_Name => P_WipEntityName,
1232 P_Po_Number => P_BasePoNum,
1233 P_Vendor_Item_Number => P_SupplierItemNumber,
1234 P_Wip_Operation_Seq_Num => P_WipOperationSeqNum,
1235 P_UOM_Name => P_AssemblyPrimaryUom,
1236 P_Production_Line => P_WipLineCode,
1237 P_Quantity_Ordered => l_qty,
1238 P_Item_Revision => l_rev,
1239 P_Po_Release_Number => P_PoReleaseNumber,
1240 P_Organization_Id => P_OrganizationId,
1241 P_Wip_Entity_type => P_WipEntityId);
1242 END ARE_QA_PLANS_AVAILABLE;
1243
1244
1245 /**
1246 * This function validates the from op and to op for the user relating to
1247 * OSP operation steps. The follow rules apply to OSP:
1248 * - Users cannot move into a Queue of an OSP operation unless that
1249 * department has a location setup.
1250 * - Users cannot move forward into a queue of an operation that has
1251 * PO resource unless the user is an employee
1252 * The error message for the first case would be WIP_PO_MOVE_LOCATION and
1253 * WIP_VALID_EMPLOYEE for the second case.
1254 * Parameters:
1255 * p_orgID The organization identifier.
1256 * p_wipEntityID The wip entity identifier.
1257 * p_lineID The line id used only for repetitive schedule. For
1258 * discrete and lotbased, do not need to pass this value.
1259 * p_entityType The wip entity type. (usually discrete)
1260 * p_fmOpSeqNum The from operation sequence number that user is moving.
1261 * p_toOpSeqNum The to operation sequence number that user is moving.
1262 * p_toStep The to intraoperation step that user is moving to.
1263 * userID The user identifier.
1264 * error The error message stack for displaying error to user.
1265 * Return:
1266 * boolean A flag indicating whether update successful or not.
1267 */
1268 FUNCTION checkOSP(p_orgID NUMBER,
1269 p_wipEntityID NUMBER,
1270 p_lineID NUMBER := NULL,
1271 p_entityType NUMBER,
1272 p_fmOpSeqNum NUMBER,
1273 p_toOpSeqNum NUMBER,
1274 p_toStep NUMBER,
1275 p_userID NUMBER,
1276 x_msg OUT NOCOPY VARCHAR2,
1277 x_error OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
1278
1279 CURSOR check_osp(c_org_id NUMBER,
1280 c_wip_entity_id NUMBER,
1281 c_entity_type NUMBER,
1282 c_line_id NUMBER,
1283 c_fm_op NUMBER,
1284 c_to_op NUMBER,
1285 c_to_step NUMBER,
1286 c_user_id NUMBER) IS
1287
1288 -- you cannot move into a queue of operation unless that department
1289 -- has a location set up
1290 SELECT 'WIP_PO_MOVE_LOCATION' error_message
1291 FROM bom_departments bd,
1292 wip_operation_resources wor,
1293 wip_operations wo1,
1294 wip_operations wo2
1295 WHERE wor.organization_id = c_org_id
1296 AND wor.wip_entity_id = c_wip_entity_id
1297 AND wor.operation_seq_num = c_to_op
1298 AND c_fm_op < c_to_op
1299 AND c_to_step = WIP_CONSTANTS.QUEUE
1300 AND (c_entity_type IN (WIP_CONSTANTS.DISCRETE,
1301 WIP_CONSTANTS.LOTBASED)
1302 OR
1303 (c_entity_type = WIP_CONSTANTS.REPETITIVE AND
1304 wor.repetitive_schedule_id IN
1305 (SELECT wrs.repetitive_schedule_id
1306 FROM wip_repetitive_schedules wrs
1307 WHERE wrs.wip_entity_id = c_wip_entity_id
1308 AND wrs.organization_id = c_org_id
1309 AND wrs.line_id = c_line_id
1310 AND wrs.status_type in (WIP_CONSTANTS.RELEASED,
1311 WIP_CONSTANTS.COMP_CHRG)
1312 )
1313 ))
1314 AND wo1.organization_id = wor.organization_id
1315 AND wo1.wip_entity_id = wor.wip_entity_id
1316 AND NVL(wo1.repetitive_schedule_id,-1) =
1317 NVL(wor.repetitive_schedule_id,-1)
1318 AND wo1.operation_seq_num = wor.operation_seq_num
1319 AND wo2.organization_id = wo1.organization_id
1320 AND wo2.wip_entity_id = wo1.wip_entity_id
1321 AND NVL(wo2.repetitive_schedule_id,-1) =
1322 NVL(wo1.repetitive_schedule_id,-1)
1323 AND ((wor.autocharge_type = WIP_CONSTANTS.PO_RECEIPT AND
1324 wo2.operation_seq_num = wor.operation_seq_num)
1325 OR
1326 (wor.autocharge_type = WIP_CONSTANTS.PO_MOVE AND
1327 ((wo1.next_operation_seq_num IS NOT NULL AND
1328 wo1.next_operation_seq_num = wo2.operation_seq_num)
1329 OR
1330 (wo1.next_operation_seq_num IS NULL AND
1331 wo2.operation_seq_num = wor.operation_seq_num)
1332 )))
1333 AND bd.organization_id = c_org_id
1334 AND wo2.department_id = bd.department_id
1335 AND bd.location_id IS NULL
1336
1337 UNION ALL
1338
1339 -- you cannot forward move into a queue of operation that has
1340 -- PO resources unless you are an employee
1341 SELECT 'WIP_VALID_EMPLOYEE' error_message
1342 FROM wip_operation_resources wor
1343 WHERE wor.organization_id = c_org_id
1344 AND wor.wip_entity_id = c_wip_entity_id
1345 AND wor.operation_seq_num = c_to_op
1346 AND c_fm_op < c_to_op
1347 AND c_to_step = WIP_CONSTANTS.QUEUE
1348 AND wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
1349 WIP_CONSTANTS.PO_MOVE)
1350 AND (c_entity_type IN (WIP_CONSTANTS.DISCRETE,
1351 WIP_CONSTANTS.LOTBASED)
1352 OR
1353 (c_entity_type = WIP_CONSTANTS.REPETITIVE AND
1354 wor.repetitive_schedule_id IN
1355 (SELECT repetitive_schedule_id
1356 FROM wip_repetitive_schedules wrs
1357 WHERE wrs.organization_id = c_org_id
1358 AND wrs.wip_entity_id = c_wip_entity_id
1359 AND wrs.line_id = c_line_id
1360 AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
1361 WIP_CONSTANTS.COMP_CHRG)
1362 )
1363 ))
1364 AND NOT EXISTS
1365 (SELECT 'Current user is an employee'
1366 FROM fnd_user fu,
1367 per_people_f ppf
1368 WHERE fu.user_id = c_user_id
1369 AND fu.employee_id = ppf.person_id);
1370
1371 result NUMBER;
1372 l_found boolean;
1373 l_msg VARCHAR2(80);
1374 BEGIN
1375 open check_osp(
1376 c_org_id => p_orgID,
1377 c_wip_entity_id => p_wipEntityID,
1378 c_entity_type => p_entityType,
1379 c_line_id => p_lineID,
1380 c_fm_op => p_fmOpSeqNum,
1381 c_to_op => p_toOpSeqNum,
1382 c_to_step => p_toStep,
1383 c_user_id => p_userID);
1384 fetch check_osp into l_msg;
1385 l_found := check_osp%FOUND;
1386 close check_osp;
1387
1388 if (l_found) then
1389 -- cannot perform move
1390 fnd_message.set_name('WIP', l_msg);
1391 x_error := substrb(fnd_message.get, 1, 240);
1392 x_msg := l_msg;
1393 return false;
1394 end if;
1395 return (TRUE);
1396 END checkOSP;
1397
1398 PROCEDURE updatePOReqNBDManager(errbuf OUT NOCOPY VARCHAR2,
1399 retcode OUT NOCOPY NUMBER,
1400 p_project_id IN NUMBER,
1401 p_task_id IN NUMBER,
1402 p_days_forward_fm IN NUMBER,
1403 p_days_forward_to IN NUMBER,
1404 p_org_id IN NUMBER,
1405 p_entity_type IN NUMBER) IS
1406 CURSOR c_job_schedule IS
1407 SELECT wdj.wip_entity_id job_id,
1408 to_number(null) rep_schedule_id
1409 FROM wip_discrete_jobs wdj
1410 WHERE wdj.status_type IN (WIP_CONSTANTS.RELEASED,
1411 WIP_CONSTANTS.UNRELEASED,
1412 WIP_CONSTANTS.HOLD)
1413 AND (p_project_id IS NULL OR wdj.project_id = p_project_id)
1414 AND (p_task_id IS NULL OR wdj.task_id = p_task_id)
1415 AND wdj.organization_id = p_org_id
1416 AND p_entity_type <> WIP_CONSTANTS.REPETITIVE
1417 UNION ALL
1418 SELECT wrs.wip_entity_id job_id,
1419 wrs.repetitive_schedule_id rep_schedule_id
1420 FROM wip_repetitive_schedules wrs
1421 WHERE wrs.status_type IN (WIP_CONSTANTS.RELEASED,
1422 WIP_CONSTANTS.UNRELEASED,
1423 WIP_CONSTANTS.HOLD)
1424 AND wrs.organization_id = p_org_id
1425 AND p_entity_type = WIP_CONSTANTS.REPETITIVE;
1426
1427 CURSOR c_po_req (p_job_id NUMBER,
1428 p_repetitive_id NUMBER) IS
1429 SELECT pd.po_header_id po_header_id,
1430 to_number(null) po_release_id,
1431 pd.line_location_id po_line_location_id,
1432 to_number(null) req_header_id,
1433 to_number(null) req_line_id,
1434 ph.type_lookup_code po_req_type,
1435 ph.authorization_status approval_status,
1436 pll.need_by_date old_need_by_date,
1437 pd.wip_operation_seq_num wip_op_seq,
1438 pl.item_id item_id,
1439 pd.org_id ou_id -- operating unit
1440 FROM po_distributions_all pd,
1441 po_headers_all ph,
1442 po_lines_all pl,
1443 po_line_locations_all pll,
1444 po_line_types plt
1445 WHERE ph.type_lookup_code = 'STANDARD'
1446 AND ph.po_header_id = pd.po_header_id
1447 AND pd.po_line_id = pl.po_line_id
1448 AND pd.line_location_id = pll.line_location_id
1449 AND pl.line_type_id = plt.line_type_id
1450 AND plt.outside_operation_flag = 'Y'
1451 AND pd.wip_entity_id = p_job_id
1452 AND pd.destination_organization_id = p_org_id
1453 AND (p_repetitive_id IS NULL OR
1454 pd.wip_repetitive_schedule_id = p_repetitive_id)
1455 AND (ph.authorization_status IS NULL OR -- INCOMPLETE
1456 ph.authorization_status IN ('INCOMPLETE',
1457 'APPROVED',
1458 'REQUIRES REAPPROVAL'))
1459 AND (pll.cancel_flag IS NULL OR pll.cancel_flag = 'N')
1460 UNION ALL
1461 SELECT pd.po_header_id po_header_id,
1462 pr.po_release_id po_release_id,
1463 pd.line_location_id po_line_location_id,
1464 to_number(null) req_header_id,
1465 to_number(null) req_line_id,
1466 ph.type_lookup_code po_req_type,
1467 pr.authorization_status approval_status,
1468 pll.need_by_date old_need_by_date,
1469 pd.wip_operation_seq_num wip_op_seq,
1470 pl.item_id item_id,
1471 pd.org_id ou_id -- operating unit
1472 FROM po_distributions_all pd,
1473 po_headers_all ph,
1474 po_lines_all pl,
1475 po_line_locations_all pll,
1476 po_releases_all pr,
1477 po_line_types plt
1478 WHERE ph.type_lookup_code = 'BLANKET'
1479 AND pr.po_release_id = pll.po_release_id
1480 AND pr.po_header_id = ph.po_header_id
1481 AND ph.po_header_id = pd.po_header_id
1482 AND pd.po_line_id = pl.po_line_id
1483 AND pd.line_location_id = pll.line_location_id
1484 AND pl.line_type_id = plt.line_type_id
1485 AND plt.outside_operation_flag = 'Y'
1486 AND pd.wip_entity_id = p_job_id
1487 AND pd.destination_organization_id = p_org_id
1488 AND (p_repetitive_id IS NULL OR
1489 pd.wip_repetitive_schedule_id = p_repetitive_id)
1490 AND (pr.authorization_status IS NULL OR -- INCOMPLETE
1491 pr.authorization_status IN ('INCOMPLETE',
1492 'APPROVED',
1493 'REQUIRES REAPPROVAL'))
1494 AND (pll.cancel_flag IS NULL OR pll.cancel_flag = 'N')
1495 UNION ALL
1496 SELECT to_number(null) po_header_id,
1497 to_number(null) po_release_id,
1498 to_number(null) po_line_location_id,
1499 prl.requisition_header_id req_header_id,
1500 prl.requisition_line_id req_line_id,
1501 'REQUISITION' po_req_type,
1502 prh.authorization_status approval_status,
1503 prl.need_by_date old_need_by_date,
1504 prl.wip_operation_seq_num wip_op_seq,
1505 prl.item_id item_id,
1506 prl.org_id ou_id -- operating unit
1507 FROM po_requisition_headers_all prh,
1508 po_requisition_lines_all prl,
1509 po_line_types plt
1510 WHERE NOT EXISTS
1511 (SELECT 'x'
1512 FROM po_line_locations_all pll
1513 WHERE prl.line_location_id = pll.line_location_id)
1514 AND prh.requisition_header_id = prl.requisition_header_id
1515 AND prl.line_type_id = plt.line_type_id
1516 AND plt.outside_operation_flag = 'Y'
1517 AND prl.wip_entity_id = p_job_id
1518 AND prl.destination_organization_id = p_org_id
1519 AND (p_repetitive_id IS NULL OR
1520 prl.wip_repetitive_schedule_id = p_repetitive_id)
1521 AND (prl.cancel_flag IS NULL OR prl.cancel_flag = 'N');
1522
1523 CURSOR c_new_NBD (p_job_id NUMBER,
1524 p_repetitive_id NUMBER,
1525 p_op_seq NUMBER,
1526 p_item_id NUMBER) IS
1527 /*Bug 14687249: when post processing leadtime is 0, use operation date directly to populate need_by_date, so it will not flip to the next working date.*/
1528 SELECT Decode(NVL(msi.postprocessing_lead_time,0),
1529 0,DECODE(p_entity_type,
1530 WIP_CONSTANTS.EAM, wo1.last_unit_completion_date,
1531 DECODE(wo1.next_operation_seq_num,
1532 NULL, wo1.last_unit_completion_date,
1533 wo2.first_unit_start_date)),
1534 (bcd1.calendar_date +
1535 (DECODE( p_entity_type,
1536 WIP_CONSTANTS.EAM, wo1.last_unit_completion_date,
1537 DECODE(wo1.next_operation_seq_num,
1538 NULL, wo1.last_unit_completion_date,
1539 wo2.first_unit_start_date)) -
1540 TRUNC(DECODE( p_entity_type,
1541 WIP_CONSTANTS.EAM, wo1.last_unit_completion_date,
1542 DECODE(wo1.next_operation_seq_num,
1543 NULL, wo1.last_unit_completion_date,
1544 wo2.first_unit_start_date)))))) new_need_by_date
1545 FROM bom_calendar_dates bcd1,
1546 bom_calendar_dates bcd2,
1547 mtl_system_items msi,
1548 mtl_parameters mp,
1549 wip_operations wo1,
1550 wip_operations wo2
1551 WHERE mp.organization_id = p_org_id
1552 AND mp.organization_id = msi.organization_id
1553 AND msi.inventory_item_id = p_item_id
1554 AND wo1.organization_id = mp.organization_id
1555 AND wo1.wip_entity_id = p_job_id
1556 AND wo1.operation_seq_num = p_op_seq
1557 AND (p_repetitive_id IS NULL OR
1558 wo1.repetitive_schedule_id = p_repetitive_id)
1559 AND wo2.organization_id = wo1.organization_id
1560 AND wo2.wip_entity_id = wo1.wip_entity_id
1561 AND ((wo1.next_operation_seq_num IS NOT NULL AND
1562 wo2.operation_seq_num = wo1.next_operation_seq_num)
1563 OR
1564 (wo1.next_operation_seq_num IS NULL AND
1565 wo2.operation_seq_num = p_op_seq))
1566 -- consider post processing lead time before inserting need-by-date
1567 AND bcd2.calendar_code = mp.calendar_code
1568 AND bcd2.exception_set_id = mp.calendar_exception_set_id
1569 AND bcd2.calendar_date =
1570 TRUNC(DECODE( p_entity_type,
1571 WIP_CONSTANTS.EAM, wo1.last_unit_completion_date,
1572 DECODE(wo1.next_operation_seq_num,
1573 NULL, wo1.last_unit_completion_date,
1574 wo2.first_unit_start_date)))
1575 AND bcd1.calendar_code = mp.calendar_code
1576 AND bcd1.exception_set_id = mp.calendar_exception_set_id
1577 AND bcd1.seq_num = (bcd2.next_seq_num -
1578 CEIL(NVL(msi.postprocessing_lead_time,0)));
1579
1580 l_job_schedule c_job_schedule%ROWTYPE;
1581 l_po_req c_po_req%ROWTYPE;
1582 l_new_NBD c_new_NBD%ROWTYPE;
1583 l_early_nbd NUMBER;
1584 l_late_nbd NUMBER;
1585 l_return_status VARCHAR2(1);
1586 l_fm_date DATE;
1587 l_to_date DATE;
1588 BEGIN
1589 retcode := 0; -- success
1590 IF(po_code_release_grp.Current_Release >=
1591 po_code_release_grp.PRC_11i_Family_Pack_J) THEN
1592 -- set l_fm_date,and l_to_date
1593 l_fm_date := trunc(sysdate + p_days_forward_fm);
1594 l_to_date := trunc(sysdate + p_days_forward_to) + (1 - (1/(24*3600)));
1595
1596 SELECT early_need_by_date_tolerance,
1597 late_need_by_date_tolerance
1598 INTO l_early_nbd,
1599 l_late_nbd
1600 FROM wip_parameters
1601 WHERE organization_id = p_org_id;
1602
1603 FOR l_job_schedule IN c_job_schedule LOOP
1604 FOR l_po_req IN c_po_req(
1605 p_job_id => l_job_schedule.job_id,
1606 p_repetitive_id =>l_job_schedule.rep_schedule_id)LOOP
1607 FOR l_new_NBD IN c_new_NBD(
1608 p_job_id => l_job_schedule.job_id,
1609 p_repetitive_id => l_job_schedule.rep_schedule_id,
1610 p_op_seq => l_po_req.wip_op_seq,
1611 p_item_id => l_po_req.item_id) LOOP
1612 IF((l_new_NBD.new_need_by_date >= l_fm_date) AND
1613 (l_new_NBD.new_need_by_date <= l_to_date) AND
1614 (l_new_NBD.new_need_by_date <
1615 l_po_req.old_need_by_date - l_early_nbd
1616 OR
1617 l_new_NBD.new_need_by_date >
1618 l_po_req.old_need_by_date + l_late_nbd)) THEN
1619
1620 wip_osp.updatePOReqNBD (
1621 p_po_header_id => l_po_req.po_header_id,
1622 p_po_release_id => l_po_req.po_release_id,
1623 p_po_line_location_id => l_po_req.po_line_location_id,
1624 p_req_header_id => l_po_req.req_header_id,
1625 p_req_line_id => l_po_req.req_line_id,
1626 p_po_req_type => l_po_req.po_req_type,
1627 p_approval_status => l_po_req.approval_status,
1628 p_new_NBD => l_new_NBD.new_need_by_date,
1629 p_ou_id => l_po_req.ou_id,
1630 x_return_status => l_return_status);
1631 -- No need to check return status because the PO that we cannot
1632 -- update now will be picked up by the next concurrent program.
1633 END IF; -- check new need-by date
1634 END LOOP; -- new NBD
1635 END LOOP; -- for each PO associated to the job
1636 END LOOP; -- for each job
1637 ELSE
1638 -- Customers do not have PO FPJ
1639 retcode := 1; -- warning
1640 fnd_message.set_name('WIP','WIP_MISSING_PO_FPJ_ONWARD');
1641 errbuf := fnd_message.get;
1642 END IF;
1643 EXCEPTION
1644 WHEN others THEN
1645 retcode := 2; -- error
1646 errbuf := SQLERRM;
1647 END updatePOReqNBDManager;
1648
1649 PROCEDURE updatePOReqNBD(p_po_header_id IN NUMBER,
1650 p_po_release_id IN NUMBER,
1651 p_po_line_location_id IN NUMBER,
1652 p_req_header_id IN NUMBER,
1653 p_req_line_id IN NUMBER,
1654 p_po_req_type IN VARCHAR2,
1655 p_approval_status IN VARCHAR2,
1656 p_new_NBD IN DATE,
1657 p_ou_id IN NUMBER,
1658 x_return_status OUT NOCOPY VARCHAR2) IS
1659
1660 l_params wip_logger.param_tbl_t;
1661 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1662 l_returnStatus VARCHAR2(1);
1663 l_errMsg VARCHAR2(240);
1664 l_msgCount NUMBER;
1665 l_msgData VARCHAR2(2000);
1666 l_po_changes PO_CHANGES_REC_TYPE;
1667 l_errors_rec PO_API_ERRORS_REC_TYPE;
1668 l_req_changes PO_REQ_CHANGES_REC_TYPE;
1669
1670 BEGIN
1671 -- write parameter value to log file
1672 IF (l_logLevel <= wip_constants.trace_logging) THEN
1673 l_params(1).paramName := 'p_po_header_id';
1674 l_params(1).paramValue := p_po_header_id;
1675 l_params(2).paramName := 'p_po_release_id';
1676 l_params(2).paramValue := p_po_release_id;
1677 l_params(3).paramName := 'p_po_line_location_id';
1678 l_params(3).paramValue := p_po_line_location_id;
1679 l_params(4).paramName := 'p_req_header_id';
1680 l_params(4).paramValue := p_req_header_id;
1681 l_params(5).paramName := 'p_req_line_id';
1682 l_params(5).paramValue := p_req_line_id;
1683 l_params(6).paramName := 'p_po_req_type';
1684 l_params(6).paramValue := p_po_req_type;
1685 l_params(7).paramName := 'p_approval_status';
1686 l_params(7).paramValue := p_approval_status;
1687 l_params(8).paramName := 'p_new_NBD';
1688 l_params(8).paramValue := p_new_NBD;
1689 l_params(9).paramName := 'p_ou_id';
1690 l_params(9).paramValue := p_ou_id;
1691 wip_logger.entryPoint(p_procName => 'wip_osp.updatePOReqNBD',
1692 p_params => l_params,
1693 x_returnStatus => l_returnStatus);
1694 END IF;
1695
1696 SAVEPOINT s_update_po_nbd;
1697 x_return_status := fnd_api.g_ret_sts_success;
1698 -- Set OU context before calling PO API. This change is mandatory for
1699 -- MOAC change in R12.
1700 mo_global.set_policy_context('S',p_ou_id);
1701 -- Update PO need by date and promise date
1702 IF(p_po_req_type IN('STANDARD', 'BLANKET')) THEN
1703 IF(p_approval_status IS NULL OR -- INCOMPLETE
1704 p_approval_status IN('INCOMPLETE',
1705 'APPROVED',
1706 'REQUIRES REAPPROVAL')) THEN
1707 -- Call PO API to update NBD. This API will notify supplier after
1708 -- the PO get approved. Supplier will then change promise date
1709 -- through iSupplier or communicate the change to buyer if they can
1710 -- accommodate the new change
1711 l_po_changes := PO_CHANGES_REC_TYPE.create_object(
1712 p_po_header_id => p_po_header_id,
1713 p_po_release_id => p_po_release_id);
1714
1715 l_po_changes.shipment_changes.add_change(
1716 p_po_line_location_id => p_po_line_location_id,
1717 p_need_by_date => p_new_NBD);
1718
1719 po_wip_integration_grp.update_document(
1720 p_api_version => 1.0,
1721 p_init_msg_list => fnd_api.g_true,
1722 p_changes => l_po_changes,
1723 p_run_submission_checks => fnd_api.g_true,
1724 p_launch_approvals_flag => fnd_api.g_true,
1725 p_buyer_id => NULL,
1726 p_update_source => NULL,
1727 p_override_date => NULL,
1728 x_return_status => x_return_status,
1729 x_api_errors => l_errors_rec);
1730
1731 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
1732 FOR i IN 1..l_errors_rec.message_name.count LOOP
1733 fnd_message.set_name('PO', l_errors_rec.message_name(i));
1734 fnd_msg_pub.add;
1735 END LOOP;
1736 raise fnd_api.g_exc_unexpected_error;
1737 END IF;
1738 ELSE -- PO in status that do not allow update
1739 fnd_message.set_name('WIP', 'WIP_INVALID_PO_STATUS');
1740 fnd_msg_pub.add;
1741 l_errMsg := 'PO is in status that does not allow update';
1742 raise fnd_api.g_exc_unexpected_error;
1743 END IF; -- Check PO status
1744 ELSIF (p_po_req_type = 'REQUISITION') THEN
1745 -- Call PO API to update requisition
1746 l_req_changes := PO_REQ_CHANGES_REC_TYPE(
1747 req_header_id => p_req_header_id,
1748 line_changes => NULL,
1749 distribution_changes => NULL);
1750
1751 l_req_changes.line_changes := PO_REQ_LINES_REC_TYPE(
1752 req_line_id => PO_TBL_NUMBER(p_req_line_id),
1753 unit_price => PO_TBL_NUMBER(NULL),
1754 currency_unit_price => PO_TBL_NUMBER(NULL),
1755 quantity => PO_TBL_NUMBER(NULL),
1756 secondary_quantity => PO_TBL_NUMBER(NULL),
1757 need_by_date => PO_TBL_DATE(p_new_NBD),
1758 deliver_to_location_id => PO_TBL_NUMBER(NULL),
1759 assignment_start_date => PO_TBL_DATE(NULL),
1760 assignment_end_date => PO_TBL_DATE(NULL),
1761 amount => PO_TBL_NUMBER(NULL));
1762
1763 po_wip_integration_grp.update_requisition(
1764 p_api_version => 1.0,
1765 p_req_changes => l_req_changes,
1766 p_update_source => NULL,
1767 x_return_status => x_return_status,
1768 x_msg_count => l_msgCount,
1769 x_msg_data => l_msgData);
1770
1771 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
1772 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1773 fnd_message.set_token('MESSAGE', l_msgData);
1774 fnd_msg_pub.add;
1775 raise fnd_api.g_exc_unexpected_error;
1776 END IF;
1777 END IF; -- PO or Requisition
1778 -- write to the log file
1779 IF (l_logLevel <= wip_constants.trace_logging) THEN
1780 wip_logger.exitPoint(p_procName => 'wip_osp.updatePOReqNBD',
1781 p_procReturnStatus => x_return_status,
1782 p_msg => 'procedure complete',
1783 x_returnStatus => l_returnStatus);
1784 END IF;
1785 EXCEPTION
1786 WHEN fnd_api.g_exc_unexpected_error THEN
1787 ROLLBACK TO SAVEPOINT s_update_po_nbd;
1788 x_return_status := fnd_api.g_ret_sts_unexp_error;
1789 IF (l_logLevel <= wip_constants.trace_logging) THEN
1790 wip_logger.exitPoint(p_procName => 'wip_osp.updatePOReqNBD',
1791 p_procReturnStatus => x_return_status,
1792 p_msg => l_errMsg,
1793 x_returnStatus => l_returnStatus);
1794 END IF;
1795
1796 WHEN others THEN
1797 ROLLBACK TO SAVEPOINT s_update_po_nbd;
1798 x_return_status := fnd_api.g_ret_sts_unexp_error;
1799 l_errMsg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
1800 IF (l_logLevel <= wip_constants.trace_logging) THEN
1801 wip_logger.exitPoint(p_procName => 'wip_osp.updatePOReqNBD',
1802 p_procReturnStatus => x_return_status,
1803 p_msg => l_errMsg,
1804 x_returnStatus => l_returnStatus);
1805 END IF;
1806 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1807 fnd_message.set_token('MESSAGE', l_errMsg);
1808 fnd_msg_pub.add;
1809 END updatePOReqNBD;
1810
1811 /* Fix for bug 4734309: Added new parameter p_is_scrap_txn.
1812 * If passed as WIP_CONSTANTS.YES,
1813 * then PO/REQ changes affect only future operations. */
1814
1815 PROCEDURE updatePOReqQuantity(p_job_id IN NUMBER,
1816 p_repetitive_id IN NUMBER :=NULL,
1817 p_org_id IN NUMBER,
1818 p_changed_qty IN NUMBER,
1819 p_fm_op IN NUMBER,
1820 p_is_scrap_txn IN NUMBER := NULL, /* Bug 4734309 */
1821 x_return_status OUT NOCOPY VARCHAR2) IS
1822
1823 -- Bugfix 5000087 : Modified the cursor so that it checks the PO creation
1824 -- time and throw the multiple PO error appropriately.
1825 CURSOR c_multiple_po IS
1826 SELECT count(*)
1827 FROM po_distributions_all pd,
1828 po_lines_all pl,
1829 po_headers_all ph,
1830 po_releases_all pr,
1831 po_line_locations_all pll,
1832 wip_discrete_jobs wdj, -- bugfix 5000087
1833 wip_repetitive_schedules wrs, -- bugfix 5000087
1834 wip_operations wo -- bugfix 5000087
1835 WHERE pd.po_line_id = pl.po_line_id
1836 AND ph.po_header_id = pd.po_header_id
1837 AND pd.line_location_id = pll.line_location_id
1838 AND pd.po_release_id = pr.po_release_id (+)
1839 AND pd.wip_entity_id = p_job_id
1840 AND (p_repetitive_id IS NULL OR
1841 pd.wip_repetitive_schedule_id = p_repetitive_id)
1842 AND pd.destination_organization_id = p_org_id
1843 AND (pll.cancel_flag IS NULL OR
1844 pll.cancel_flag = 'N')
1845 /* begin bugfix 5000087 */
1846 AND pd.wip_entity_id = wdj.wip_entity_id (+)
1847 AND pd.destination_organization_id = wdj.organization_id (+)
1848 AND pd.wip_repetitive_schedule_id = wrs.repetitive_schedule_id (+)
1849 AND pd.destination_organization_id = wrs.organization_id (+)
1850 AND wo.wip_entity_id = pd.wip_entity_id
1851 AND wo.organization_id = pd.destination_organization_id
1852 AND wo.operation_seq_num = pd.wip_operation_seq_num
1853 AND (p_repetitive_id IS NULL OR
1854 wo.repetitive_schedule_id = p_repetitive_id)
1855 AND (
1856 (((p_repetitive_id IS NULL AND
1857 wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
1858 OR
1859 (p_repetitive_id IS NOT NULL AND
1860 wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
1861 AND pd.wip_operation_seq_num > p_fm_op)
1862 OR
1863 (((p_repetitive_id IS NULL AND
1864 wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
1865 OR
1866 (p_repetitive_id IS NOT NULL AND
1867 wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
1868 AND wo.previous_operation_seq_num IS NULL
1869 AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO)) -- bugfix 4702642
1870 )
1871 /* end bugfix 5000087 */
1872 GROUP BY pd.wip_operation_seq_num,
1873 pl.item_id
1874 HAVING count(*) > 1
1875 UNION ALL
1876 SELECT count(*)
1877 FROM po_requisition_lines_all prl,
1878 wip_discrete_jobs wdj, -- bugfix 5000087
1879 wip_repetitive_schedules wrs, -- bugfix 5000087
1880 wip_operations wo -- bugfix 5000087
1881 WHERE prl.wip_entity_id = p_job_id
1882 AND (p_repetitive_id IS NULL OR
1883 prl.wip_repetitive_schedule_id = p_repetitive_id)
1884 AND prl.destination_organization_id = p_org_id
1885 AND (prl.cancel_flag IS NULL OR
1886 prl.cancel_flag = 'N')
1887 /* begin bugfix 5000087 */
1888 AND prl.wip_entity_id = wdj.wip_entity_id (+)
1889 AND prl.destination_organization_id = wdj.organization_id (+)
1890 AND prl.wip_repetitive_schedule_id = wrs.repetitive_schedule_id (+)
1891 AND prl.destination_organization_id = wrs.organization_id (+)
1892 AND wo.wip_entity_id = prl.wip_entity_id
1893 AND wo.organization_id = prl.destination_organization_id
1894 AND wo.operation_seq_num = prl.wip_operation_seq_num
1895 AND (p_repetitive_id IS NULL OR
1896 wo.repetitive_schedule_id = p_repetitive_id)
1897 AND ((((p_repetitive_id IS NULL AND
1898 wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
1899 OR
1900 (p_repetitive_id IS NOT NULL AND
1901 wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
1902 AND prl.wip_operation_seq_num > p_fm_op)
1903 OR
1904 (((p_repetitive_id IS NULL AND
1905 wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
1906 OR
1907 (p_repetitive_id IS NOT NULL AND
1908 wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
1909 AND wo.previous_operation_seq_num IS NULL
1910 AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO))) -- bugfix 4702642
1911 /* end bugfix 5000087 */
1912 GROUP BY prl.wip_operation_seq_num,
1913 prl.item_id
1914 HAVING count(*) > 1;
1915
1916 /* Fixed bug 4153549. We also need to update PO/requistion quantity if PO
1917 * creation time is at operation and OSP operation is the first operation
1918 * Fixed bug 4734309. Need to update quantities only for future PO/REQs when
1919 * quantity is scrapped.
1920 * Fixed Bug 8980631 Need to update only future PO/REQ's irrespective of
1921 * PO creation time parameter
1922 */
1923 CURSOR c_update_po_qty IS
1924 SELECT pd.po_header_id po_header_id,
1925 to_number(null) po_release_id,
1926 pd.po_distribution_id po_distribution_id,
1927 to_number(null) req_header_id,
1928 to_number(null) req_line_id,
1929 /* Fixed bug 9877798. Add inv_convert.inv_um_convert function for uom conversions */
1930 /* Bug#16239604(FP of bug#9276689): Converting the actual quantity instead of 1 quantity */
1931 (pd.quantity_ordered +(
1932 inv_convert.inv_um_convert (msi.inventory_item_id, -- item_id
1933 NULL, -- precision
1934 DECODE(msi.outside_operation_uom_type,
1935 'RESOURCE', ROUND(wor.usage_rate_or_amount * p_changed_qty,
1936 WIP_CONSTANTS.INV_MAX_PRECISION),
1937 'ASSEMBLY', ROUND(p_changed_qty,
1938 WIP_CONSTANTS.INV_MAX_PRECISION)), -- from_quantity
1939 msi.primary_uom_code, -- from_unit
1940 (SELECT muom.uom_code
1941 FROM mtl_units_of_measure muom
1942 WHERE muom.unit_of_measure =pl.unit_meas_lookup_code),
1943 -- to_unit,
1944 NULL, --from_name
1945 NULL --to_name
1946 )
1947 ) ) new_po_qty,
1948 ph.type_lookup_code po_req_type,
1949 ph.authorization_status approval_status,
1950 msi.primary_uom_code uom_code,
1951 pd.org_id ou_id -- operating unit
1952 FROM mtl_system_items msi,
1953 po_distributions_all pd,
1954 po_headers_all ph,
1955 po_lines_all pl,
1956 po_line_locations_all pll,
1957 wip_operation_resources wor,
1958 wip_operations wo,
1959 wip_discrete_jobs wdj,
1960 wip_repetitive_schedules wrs
1961 WHERE ph.type_lookup_code = 'STANDARD'
1962 AND ph.po_header_id = pd.po_header_id
1963 AND pd.line_location_id = pll.line_location_id
1964 AND pd.po_line_id = pl.po_line_id
1965 AND pd.wip_entity_id = wdj.wip_entity_id (+)
1966 AND pd.destination_organization_id = wdj.organization_id (+)
1967 AND pd.wip_repetitive_schedule_id = wrs.repetitive_schedule_id (+)
1968 AND pd.destination_organization_id = wrs.organization_id (+)
1969 AND pl.item_id = msi.inventory_item_id
1970 AND pd.destination_organization_id = msi.organization_id
1971 AND pd.wip_entity_id = p_job_id
1972 AND pd.destination_organization_id = p_org_id
1973 AND (p_repetitive_id IS NULL OR
1974 pd.wip_repetitive_schedule_id = p_repetitive_id)
1975 AND pd.wip_operation_seq_num > p_fm_op /*Bug 8980631*/
1976 AND (((p_repetitive_id IS NULL AND
1977 wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
1978 OR
1979 (p_repetitive_id IS NOT NULL AND
1980 wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
1981 OR
1982 (((p_repetitive_id IS NULL AND
1983 wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
1984 OR
1985 (p_repetitive_id IS NOT NULL AND
1986 wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
1987 AND wo.previous_operation_seq_num IS NULL
1988 AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO)))/* 4734309 */
1989 AND wor.organization_id = wo.organization_id
1990 AND wor.wip_entity_id = wo.wip_entity_id
1991 AND wor.operation_seq_num = wo.operation_seq_num
1992 AND wor.resource_seq_num = pd.wip_resource_seq_num -- Bug 10274866 (FP of 10211569)
1993 AND wor.organization_id = pd.destination_organization_id
1994 AND wor.wip_entity_id = pd.wip_entity_id
1995 AND wor.operation_seq_num = pd.wip_operation_seq_num
1996 AND (p_repetitive_id IS NULL OR
1997 wor.repetitive_schedule_id = p_repetitive_id)
1998 AND wor.basis_type = WIP_CONSTANTS.PER_ITEM
1999 AND (pll.cancel_flag IS NULL OR pll.cancel_flag = 'N')
2000 UNION /* FP bug 10383628 */
2001 SELECT pd.po_header_id po_header_id,
2002 pr.po_release_id po_release_id,
2003 pd.po_distribution_id po_distribution_id,
2004 to_number(null) req_header_id,
2005 to_number(null) req_line_id,
2006 /* Fixed bug 9877798. Add inv_convert.inv_um_convert function for uom conversions */
2007 (pd.quantity_ordered +(
2008 inv_convert.inv_um_convert (msi.inventory_item_id, -- item_id
2009 NULL, -- precision
2010 DECODE(msi.outside_operation_uom_type,
2011 'RESOURCE', ROUND(wor.usage_rate_or_amount * p_changed_qty,
2012 WIP_CONSTANTS.INV_MAX_PRECISION),
2013 'ASSEMBLY', ROUND(p_changed_qty,
2014 WIP_CONSTANTS.INV_MAX_PRECISION)), -- from_quantity
2015 msi.primary_uom_code, -- from_unit
2016 (SELECT muom.uom_code
2017 FROM mtl_units_of_measure muom
2018 WHERE muom.unit_of_measure =pl.unit_meas_lookup_code),
2019 -- to_unit,
2020 NULL, --from_name
2021 NULL --to_name
2022 )
2023 )) new_po_qty,
2024 ph.type_lookup_code po_req_type,
2025 pr.authorization_status approval_status,
2026 msi.primary_uom_code uom_code,
2027 pd.org_id ou_id -- operating unit
2028 FROM mtl_system_items msi,
2029 po_distributions_all pd,
2030 po_headers_all ph,
2031 po_lines_all pl,
2032 po_line_locations_all pll,
2033 po_releases_all pr,
2034 wip_operation_resources wor,
2035 wip_operations wo,
2036 wip_discrete_jobs wdj,
2037 wip_repetitive_schedules wrs
2038 WHERE ph.type_lookup_code = 'BLANKET'
2039 /* Fixed bug 4240329. Add condition below to join pr.po_release_id and
2040 pll.po_release_id together to prevent the cursor to pick all release
2041 document
2042 */
2043 AND pr.po_release_id = pll.po_release_id
2044 AND pr.po_header_id = ph.po_header_id
2045 AND ph.po_header_id = pd.po_header_id
2046 AND pd.line_location_id = pll.line_location_id
2047 AND pd.po_line_id = pl.po_line_id
2048 AND pd.wip_entity_id = wdj.wip_entity_id (+)
2049 AND pd.destination_organization_id = wdj.organization_id (+)
2050 AND pd.wip_repetitive_schedule_id = wrs.repetitive_schedule_id (+)
2051 AND pd.destination_organization_id = wrs.organization_id (+)
2052 AND pl.item_id = msi.inventory_item_id
2053 AND pd.destination_organization_id = msi.organization_id
2054 AND pd.wip_entity_id = p_job_id
2055 AND pd.destination_organization_id = p_org_id
2056 AND (p_repetitive_id IS NULL OR
2057 pd.wip_repetitive_schedule_id = p_repetitive_id)
2058 AND pd.wip_operation_seq_num > p_fm_op /*bug 8980631*/
2059 AND (((p_repetitive_id IS NULL AND
2060 wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
2061 OR
2062 (p_repetitive_id IS NOT NULL AND
2063 wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
2064 OR
2065 (((p_repetitive_id IS NULL AND
2066 wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
2067 OR
2068 (p_repetitive_id IS NOT NULL AND
2069 wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
2070 AND wo.previous_operation_seq_num IS NULL
2071 AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO)))
2072 AND wor.organization_id = wo.organization_id
2073 AND wor.wip_entity_id = wo.wip_entity_id
2074 AND wor.operation_seq_num = wo.operation_seq_num
2075 AND wor.organization_id = pd.destination_organization_id
2076 AND wor.wip_entity_id = pd.wip_entity_id
2077 AND wor.operation_seq_num = pd.wip_operation_seq_num
2078 AND wor.resource_seq_num = pd.wip_resource_seq_num -- Bug 10274866 (FP of 10211569)
2079 AND (p_repetitive_id IS NULL OR
2080 wor.repetitive_schedule_id = p_repetitive_id)
2081 AND wor.basis_type = WIP_CONSTANTS.PER_ITEM
2082 AND (pll.cancel_flag IS NULL OR pll.cancel_flag = 'N')
2083 UNION /* FP bug 10383628 */
2084 SELECT to_number(null) po_header_id,
2085 to_number(null) po_release_id,
2086 to_number(null) po_distribution_id,
2087 prl.requisition_header_id req_header_id,
2088 prl.requisition_line_id req_line_id,
2089 (prl.quantity +(DECODE(msi.outside_operation_uom_type,
2090 'RESOURCE', ROUND(wor.usage_rate_or_amount * p_changed_qty,
2091 WIP_CONSTANTS.INV_MAX_PRECISION),
2092 'ASSEMBLY', ROUND(p_changed_qty,
2093 WIP_CONSTANTS.INV_MAX_PRECISION)))) new_po_qty,
2094 'REQUISITION' po_req_type,
2095 prh.authorization_status approval_status,
2096 msi.primary_uom_code uom_code,
2097 prl.org_id ou_id -- operating unit
2098 FROM mtl_system_items msi,
2099 po_requisition_headers_all prh,
2100 po_requisition_lines_all prl,
2101 wip_operation_resources wor,
2102 wip_operations wo,
2103 wip_discrete_jobs wdj,
2104 wip_repetitive_schedules wrs
2105 WHERE NOT EXISTS
2106 (SELECT 'x'
2107 FROM po_line_locations_all pll
2108 WHERE prl.line_location_id = pll.line_location_id)
2109 AND prh.requisition_header_id = prl.requisition_header_id
2110 AND prl.wip_entity_id = wdj.wip_entity_id (+)
2111 AND prl.destination_organization_id = wdj.organization_id (+)
2112 AND prl.wip_repetitive_schedule_id = wrs.repetitive_schedule_id (+)
2113 AND prl.destination_organization_id = wrs.organization_id (+)
2114 AND prl.item_id = msi.inventory_item_id
2115 AND prl.destination_organization_id = msi.organization_id
2116 AND prl.wip_entity_id = p_job_id
2117 AND prl.destination_organization_id = p_org_id
2118 AND (p_repetitive_id IS NULL OR
2119 prl.wip_repetitive_schedule_id = p_repetitive_id)
2120 AND prl.wip_operation_seq_num > p_fm_op
2121 AND (((p_repetitive_id IS NULL AND
2122 wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
2123 OR
2124 (p_repetitive_id IS NOT NULL AND
2125 wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
2126 OR
2127 (((p_repetitive_id IS NULL AND
2128 wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
2129 OR
2130 (p_repetitive_id IS NOT NULL AND
2131 wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
2132 AND wo.previous_operation_seq_num IS NULL
2133 AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO)))
2134 AND wor.organization_id = wo.organization_id
2135 AND wor.wip_entity_id = wo.wip_entity_id
2136 AND wor.operation_seq_num = wo.operation_seq_num
2137 AND wor.organization_id = prl.destination_organization_id
2138 AND wor.wip_entity_id = prl.wip_entity_id
2139 AND wor.operation_seq_num = prl.wip_operation_seq_num
2140 AND wor.resource_seq_num = prl.wip_resource_seq_num -- Bug 10274866 (FP of 10211569)
2141 AND (p_repetitive_id IS NULL OR
2142 wor.repetitive_schedule_id = p_repetitive_id)
2143 AND wor.basis_type = WIP_CONSTANTS.PER_ITEM
2144 AND (prl.cancel_flag IS NULL OR prl.cancel_flag = 'N')
2145 ORDER BY 1; --Order by added for bug#14782614, so that the new po_header_id can be used for comparison
2146
2147 l_pending_recs NUMBER;
2148 l_multiple_po c_multiple_po%ROWTYPE;
2149 l_update_po_qty c_update_po_qty%ROWTYPE;
2150 l_params wip_logger.param_tbl_t;
2151 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2152 l_returnStatus VARCHAR2(1);
2153 l_errMsg VARCHAR2(240);
2154 l_debugMsg VARCHAR2(240);
2155 l_msgCount NUMBER;
2156 l_msgData VARCHAR2(2000);
2157 indx NUMBER:=0;
2158 TYPE pochanges_tbl IS TABLE OF PO_CHANGES_REC_TYPE INDEX BY BINARY_INTEGER;
2159 l_pochanges_tab pochanges_tbl; --Added for bug#14782614 to store the different POs
2160 l_po_changes PO_CHANGES_REC_TYPE;
2161 l_errors_rec PO_API_ERRORS_REC_TYPE;
2162 l_req_changes PO_REQ_CHANGES_REC_TYPE;
2163 l_po_creation_time NUMBER;
2164 BEGIN
2165 -- write parameter value to log file
2166 IF (l_logLevel <= wip_constants.trace_logging) THEN
2167 l_params(1).paramName := 'p_job_id';
2168 l_params(1).paramValue := p_job_id;
2169 l_params(2).paramName := 'p_repetitive_id';
2170 l_params(2).paramValue := p_repetitive_id;
2171 l_params(3).paramName := 'p_org_id';
2172 l_params(3).paramValue := p_org_id;
2173 l_params(4).paramName := 'p_changed_qty';
2174 l_params(4).paramValue := p_changed_qty;
2175 l_params(5).paramName := 'p_fm_op';
2176 l_params(5).paramValue := p_fm_op;
2177 wip_logger.entryPoint(p_procName => 'wip_osp.updatePOReqQuantity',
2178 p_params => l_params,
2179 x_returnStatus => l_returnStatus);
2180 END IF;
2181
2182 SAVEPOINT s_update_po_qty;
2183 x_return_status := fnd_api.g_ret_sts_success;
2184
2185 IF(p_repetitive_id IS NULL) THEN
2186 -- Discrete jobs
2187 SELECT po_creation_time
2188 INTO l_po_creation_time
2189 FROM wip_discrete_jobs
2190 WHERE wip_entity_id = p_job_id
2191 AND organization_id = p_org_id;
2192 ELSE
2193 -- Repetitive schedules
2194 SELECT po_creation_time
2195 INTO l_po_creation_time
2196 FROM wip_repetitive_schedules
2197 WHERE repetitive_schedule_id = p_repetitive_id
2198 AND organization_id = p_org_id;
2199 END IF;
2200
2201 IF(l_po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION) THEN
2202 -- Check whether the record is still in the requisition interface table
2203
2204 /* Fix for bug 5685068: When performing scrap transactions, validate for pending
2205 requisitions only if PO Creation Time is set to At Job/Schedule Release. */
2206 if((p_is_scrap_txn = WIP_CONSTANTS.YES AND
2207 l_po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE) OR
2208 p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO) THEN
2209
2210 SELECT count(*)
2211 INTO l_pending_recs
2212 FROM po_requisitions_interface_all
2213 WHERE wip_entity_id = p_job_id
2214 AND (p_repetitive_id IS NULL OR
2215 wip_repetitive_schedule_id = p_repetitive_id);
2216
2217 IF(l_pending_recs <> 0) THEN
2218 fnd_message.set_name('WIP', 'WIP_REQUISITION_PENDING');
2219 fnd_msg_pub.add;
2220 l_errMsg := 'There are some pending records in ' ||
2221 'PO_REQUISITIONS_INTERFACE_ALL';
2222 raise fnd_api.g_exc_unexpected_error;
2223 END IF;
2224 end if; /* if((p_is_scrap_txn = WIP_CONSTANTS.YES AND */
2225
2226 OPEN c_multiple_po;
2227 FETCH c_multiple_po INTO l_multiple_po;
2228
2229 IF (c_multiple_po%FOUND) THEN
2230 fnd_message.set_name('WIP', 'WIP_MULTIPLE_PO_FOUND');
2231 fnd_msg_pub.add;
2232 l_errMsg := 'Multiple PO/requisitions found for this job/schedule';
2233 raise fnd_api.g_exc_unexpected_error;
2234 ELSE
2235 -- Update PO quantity
2236 FOR l_update_po_qty IN c_update_po_qty LOOP
2237 -- Set OU context before calling PO API. This change is
2238 -- mandatory for MOAC change in R12.
2239 mo_global.set_policy_context('S',l_update_po_qty.ou_id);
2240 IF (l_update_po_qty.po_req_type IN ('STANDARD', 'BLANKET')) THEN
2241 IF (l_update_po_qty.approval_status IS NULL OR -- INCOMPLETE
2242 l_update_po_qty.approval_status IN ('INCOMPLETE',
2243 'APPROVED',
2244 'REQUIRES REAPPROVAL')) THEN
2245 -- Call an API to update QUANTITY at the distribution level.
2246 -- This API will recalculate the shipment and line quantity
2247 -- automatically.
2248 /* Added for FP bug 10383628 - added l_update_po assign stmt
2249 and if statement around PO_CHANGES_REC_TYPE call */
2250 if l_po_changes is NULL OR l_po_changes.po_header_id<>l_update_po_qty.po_header_id then
2251 l_po_changes := PO_CHANGES_REC_TYPE.create_object(
2252 p_po_header_id => l_update_po_qty.po_header_id,
2253 p_po_release_id => l_update_po_qty.po_release_id);
2254 indx:=indx+1;
2255 end if;
2256 l_po_changes.distribution_changes.add_change(
2257 p_po_distribution_id => l_update_po_qty.po_distribution_id,
2258 p_quantity_ordered => l_update_po_qty.new_po_qty);
2259 IF (l_logLevel <= wip_constants.full_logging) THEN
2260 l_debugMsg := 'po_header_id = ' || l_update_po_qty.po_header_id
2261 || ' ; ' || 'po_release_id = ' ||
2262 l_update_po_qty.po_release_id || ' ; ' ||
2263 'po_distribution_id = ' ||
2264 l_update_po_qty.po_distribution_id || ' ; ' ||
2265 'new_po_qty = ' || l_update_po_qty.new_po_qty||';indx='||indx;
2266
2267 wip_logger.log(p_msg => l_debugMsg,
2268 x_returnStatus => l_returnStatus);
2269 END IF;
2270 l_pochanges_tab(indx):=l_po_changes;
2271 /* Commented by tekang for FP bug 10383628
2272 po_wip_integration_grp.update_document(
2273 p_api_version => 1.0,
2274 p_init_msg_list => fnd_api.g_true,
2275 p_changes => l_po_changes,
2276 p_run_submission_checks => fnd_api.g_true,
2277 p_launch_approvals_flag => fnd_api.g_true,
2278 p_buyer_id => NULL,
2279 p_update_source => NULL,
2280 p_override_date => NULL,
2281 x_return_status => x_return_status,
2282 x_api_errors => l_errors_rec);
2283 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
2284 FOR i IN 1..l_errors_rec.message_name.count LOOP
2285 fnd_message.set_name('PO', l_errors_rec.message_name(i));
2286 fnd_msg_pub.add;
2287 END LOOP;
2288 raise fnd_api.g_exc_unexpected_error;
2289 END IF;
2290 */
2291
2292 ELSE -- PO is in the status that does not allow update
2293 fnd_message.set_name('WIP', 'WIP_INVALID_PO_STATUS');
2294 fnd_msg_pub.add;
2295 l_errMsg := 'PO is in status that does not allow update';
2296 raise fnd_api.g_exc_unexpected_error;
2297 END IF; -- Check PO status
2298 ELSIF (l_update_po_qty.po_req_type = 'REQUISITION') THEN
2299 -- Call PO API to update QUANTITY in PO_REQUISITION_LINES_ALL.
2300 l_req_changes := PO_REQ_CHANGES_REC_TYPE(
2301 req_header_id => l_update_po_qty.req_header_id,
2302 line_changes => NULL,
2303 distribution_changes => NULL);
2304 l_req_changes.line_changes := PO_REQ_LINES_REC_TYPE(
2305 req_line_id => PO_TBL_NUMBER(l_update_po_qty.req_line_id),
2306 unit_price => PO_TBL_NUMBER(NULL),
2307 currency_unit_price => PO_TBL_NUMBER(NULL),
2308 quantity => PO_TBL_NUMBER(l_update_po_qty.new_po_qty),
2309 secondary_quantity => PO_TBL_NUMBER(NULL),
2310 need_by_date => PO_TBL_DATE(NULL),
2311 deliver_to_location_id => PO_TBL_NUMBER(NULL),
2312 assignment_start_date => PO_TBL_DATE(NULL),
2313 assignment_end_date => PO_TBL_DATE(NULL),
2314 amount => PO_TBL_NUMBER(NULL));
2315
2316 IF (l_logLevel <= wip_constants.full_logging) THEN
2317 l_debugMsg := 'req_header_id = ' || l_update_po_qty.req_header_id
2318 || ' ; ' || 'req_line_id = ' ||
2319 l_update_po_qty.req_line_id || ' ; ' ||
2320 'new_po_qty = ' || l_update_po_qty.new_po_qty;
2321
2322 wip_logger.log(p_msg => l_debugMsg,
2323 x_returnStatus => l_returnStatus);
2324 END IF;
2325
2326 po_wip_integration_grp.update_requisition(
2327 p_api_version => 1.0,
2328 p_req_changes => l_req_changes,
2329 p_update_source => NULL,
2330 x_return_status => x_return_status,
2331 x_msg_count => l_msgCount,
2332 x_msg_data => l_msgData);
2333
2334 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
2335 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2336 fnd_message.set_token('MESSAGE', l_msgData);
2337 fnd_msg_pub.add;
2338 raise fnd_api.g_exc_unexpected_error;
2339 END IF;
2340 END IF; -- PO or Requisition
2341 END LOOP;
2342 /* Added by tekang for FP bug 10383623 */
2343 If l_pochanges_tab.count>0 then
2344 FOR i in 1..l_pochanges_tab.count LOOP --Added for bug#14782614- To call the update_document for all records
2345 po_wip_integration_grp.update_document(
2346 p_api_version => 1.0,
2347 p_init_msg_list => fnd_api.g_true,
2348 p_changes => l_pochanges_tab(i),
2349 p_run_submission_checks => fnd_api.g_true,
2350 p_launch_approvals_flag => fnd_api.g_true,
2351 p_buyer_id => NULL,
2352 p_update_source => NULL,
2353 p_override_date => NULL,
2354 x_return_status => x_return_status,
2355 x_api_errors => l_errors_rec);
2356
2357 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
2358 FOR i IN 1..l_errors_rec.message_name.count LOOP
2359 fnd_message.set_name('PO', l_errors_rec.message_name(i));
2360 fnd_msg_pub.add;
2361 END LOOP;
2362 raise fnd_api.g_exc_unexpected_error;
2363 END IF;
2364 END LOOP;
2365 end if; --If l_pochanges_tab.count>0
2366
2367 /* End of Section - Added by tekang for FP bug 10383623 */
2368
2369 END IF;-- Multiple PO found for the same job,same item,and same op.
2370 END IF; -- po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION
2371
2372 IF(c_multiple_po%ISOPEN) THEN
2373 CLOSE c_multiple_po;
2374 END IF;
2375
2376 -- write to the log file
2377 IF (l_logLevel <= wip_constants.trace_logging) THEN
2378 wip_logger.exitPoint(p_procName => 'wip_osp.updatePOReqQuantity',
2379 p_procReturnStatus => x_return_status,
2380 p_msg => 'procedure complete',
2381 x_returnStatus => l_returnStatus);
2382 END IF;
2383 EXCEPTION
2384 WHEN fnd_api.g_exc_unexpected_error THEN
2385 ROLLBACK TO SAVEPOINT s_update_po_qty;
2386 IF(c_multiple_po%ISOPEN) THEN
2387 CLOSE c_multiple_po;
2388 END IF;
2389 x_return_status := fnd_api.g_ret_sts_unexp_error;
2390 IF (l_logLevel <= wip_constants.trace_logging) THEN
2391 wip_logger.exitPoint(p_procName => 'wip_osp.updatePOReqQuantity',
2392 p_procReturnStatus => x_return_status,
2393 p_msg => l_errMsg,
2394 x_returnStatus => l_returnStatus);
2395 END IF;
2396
2397 WHEN others THEN
2398 ROLLBACK TO SAVEPOINT s_update_po_qty;
2399 IF(c_multiple_po%ISOPEN) THEN
2400 CLOSE c_multiple_po;
2401 END IF;
2402 x_return_status := fnd_api.g_ret_sts_unexp_error;
2403 l_errMsg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
2404 IF (l_logLevel <= wip_constants.trace_logging) THEN
2405 wip_logger.exitPoint(p_procName => 'wip_osp.updatePOReqQuantity',
2406 p_procReturnStatus => x_return_status,
2407 p_msg => l_errMsg,
2408 x_returnStatus => l_returnStatus);
2409 END IF;
2410 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2411 fnd_message.set_token('MESSAGE', l_errMsg);
2412 fnd_msg_pub.add;
2413 END updatePOReqQuantity;
2414
2415
2416 PROCEDURE cancelPOReq (p_job_id IN NUMBER,
2417 p_repetitive_id IN NUMBER :=NULL,
2418 p_org_id IN NUMBER,
2419 p_op_seq_num IN NUMBER :=NULL,
2420 x_return_status OUT NOCOPY VARCHAR2,
2421 p_clr_fnd_mes_flag IN VARCHAR2 DEFAULT NULL) IS
2422 -- added parameter p_clr_fnd_mes_flag for bugfix 7229689.
2423 -- Bug fix 8681037: Changed the default value from 'N' to NULL for p_clr_fnd_mes_flag parameter
2424 -- as per the standards.
2425
2426 CURSOR c_po_req IS
2427 SELECT pd.po_header_id po_header_id,
2428 to_number(null) po_release_id,
2429 pd.po_line_id po_line_id,
2430 pd.line_location_id po_line_location_id,
2431 to_number(null) req_header_id,
2432 to_number(null) req_line_id,
2433 ph.type_lookup_code po_req_type,
2434 ph.authorization_status approval_status,
2435 'PO' document_type,
2436 ph.type_lookup_code document_subtype,
2437 pd.org_id ou_id -- operating unit
2438 FROM po_distributions_all pd,
2439 po_headers_all ph,
2440 po_line_locations_all pll
2441 /* Fixed bug 3115844 */
2442 WHERE pd.po_line_id IS NOT NULL
2443 AND pd.line_location_id IS NOT NULL
2444 AND ph.type_lookup_code = 'STANDARD'
2445 AND ph.po_header_id = pd.po_header_id
2446 AND pd.line_location_id = pll.line_location_id
2447 AND pd.wip_entity_id = p_job_id
2448 AND pd.destination_organization_id = p_org_id
2449 AND (p_repetitive_id IS NULL OR
2450 pd.wip_repetitive_schedule_id = p_repetitive_id)
2451 AND (p_op_seq_num IS NULL OR
2452 pd.wip_operation_seq_num = p_op_seq_num)
2453 AND (pll.cancel_flag IS NULL OR pll.cancel_flag = 'N')
2454 UNION ALL
2455 SELECT pd.po_header_id po_header_id,
2456 pr.po_release_id po_release_id,
2457 to_number(null) po_line_id,/* Fix for 4368095. Removed pd.po_line_id po_line_id,*/
2458 pd.line_location_id po_line_location_id,
2459 to_number(null) req_header_id,
2460 to_number(null) req_line_id,
2461 ph.type_lookup_code po_req_type,
2462 pr.authorization_status approval_status,
2463 'RELEASE' document_type,
2464 ph.type_lookup_code document_subtype,
2465 pd.org_id ou_id -- operating unit
2466 FROM po_distributions_all pd,
2467 po_headers_all ph,
2468 po_line_locations_all pll,
2469 po_releases_all pr
2470 /* Fixed bug 3115844 */
2471 WHERE pd.po_line_id IS NOT NULL
2472 AND pd.line_location_id IS NOT NULL
2473 AND ph.type_lookup_code = 'BLANKET'
2474 AND pr.po_header_id = ph.po_header_id
2475 /* Bug 4892265: Added condition to pick correct release */
2476 AND pr.po_release_id = pd.po_release_id
2477 /* End fix of bug 4892265 */
2478 AND ph.po_header_id = pd.po_header_id
2479 AND pd.line_location_id = pll.line_location_id
2480 AND pd.wip_entity_id = p_job_id
2481 AND pd.destination_organization_id = p_org_id
2482 AND (p_repetitive_id IS NULL OR
2483 pd.wip_repetitive_schedule_id = p_repetitive_id)
2484 AND (p_op_seq_num IS NULL OR
2485 pd.wip_operation_seq_num = p_op_seq_num)
2486 AND (pll.cancel_flag IS NULL OR pll.cancel_flag = 'N')
2487 UNION ALL
2488 SELECT to_number(null) po_header_id,
2489 to_number(null) po_release_id,
2490 to_number(null) po_line_id,
2491 to_number(null) po_line_location_id,
2492 prl.requisition_header_id req_header_id,
2493 prl.requisition_line_id req_line_id,
2494 'REQUISITION' po_req_type,
2495 prh.authorization_status approval_status,
2496 to_char(null) document_type,
2497 to_char(null) document_subtype,
2498 prl.org_id ou_id -- operating unit
2499 FROM po_requisition_headers_all prh,
2500 po_requisition_lines_all prl
2501 WHERE NOT EXISTS
2502 (SELECT 'x'
2503 FROM po_line_locations_all pll
2504 WHERE prl.line_location_id = pll.line_location_id)
2505 AND prh.requisition_header_id = prl.requisition_header_id
2506 AND prl.wip_entity_id = p_job_id
2507 AND prl.destination_organization_id = p_org_id
2508 AND (p_repetitive_id IS NULL OR
2509 prl.wip_repetitive_schedule_id = p_repetitive_id)
2510 AND (p_op_seq_num IS NULL OR
2511 prl.wip_operation_seq_num = p_op_seq_num)
2512 AND (prl.cancel_flag IS NULL OR
2513 prl.cancel_flag = 'N');
2514
2515
2516 l_po_req c_po_req%ROWTYPE;
2517 l_err_count NUMBER := 0;
2518 l_pending_recs NUMBER;
2519 l_params wip_logger.param_tbl_t;
2520 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2521 l_returnStatus VARCHAR2(1);
2522 l_errMsg VARCHAR2(240);
2523 l_debugMsg VARCHAR2(240);
2524 l_msgCount NUMBER;
2525 l_msgData VARCHAR2(2000);
2526 BEGIN
2527 -- write parameter value to log file
2528 IF (l_logLevel <= wip_constants.trace_logging) THEN
2529 l_params(1).paramName := 'p_job_id';
2530 l_params(1).paramValue := p_job_id;
2531 l_params(2).paramName := 'p_repetitive_id';
2532 l_params(2).paramValue := p_repetitive_id;
2533 l_params(3).paramName := 'p_org_id';
2534 l_params(3).paramValue := p_org_id;
2535 l_params(4).paramName := 'p_op_seq_num';
2536 l_params(4).paramValue := p_op_seq_num;
2537 wip_logger.entryPoint(p_procName => 'wip_osp.cancelPOReq',
2538 p_params => l_params,
2539 x_returnStatus => l_returnStatus);
2540 END IF;
2541
2542 FOR l_po_req IN c_po_req LOOP
2543 BEGIN
2544 -- Set OU context before calling PO API. This change is
2545 -- mandatory for MOAC change in R12.
2546 mo_global.set_policy_context('S',l_po_req.ou_id);
2547 IF (l_po_req.po_req_type IN ('STANDARD', 'BLANKET'))THEN
2548 -- Call PO API to cancel PO/release. If unable to cancel PO/release
2549 -- for any reason,skip the error one and try to cancel the next one.
2550
2551 IF (l_logLevel <= wip_constants.full_logging) THEN
2552 l_debugMsg := 'po_header_id = ' || l_po_req.po_header_id|| ' ; ' ||
2553 'po_release_id = ' || l_po_req.po_release_id || ' ; '
2554 ||
2555 'po_line_id = ' || l_po_req.po_line_id || ' ; ' ||
2556 'po_line_location_id = ' ||
2557 l_po_req.po_line_location_id;
2558
2559 wip_logger.log(p_msg => l_debugMsg,
2560 x_returnStatus => l_returnStatus);
2561 END IF;
2562
2563 -- Call PO API to cancel requisition
2564 -- Bug fix 8681037: Added NVL in the if condition.
2565 IF NVL(p_clr_fnd_mes_flag, 'N') = 'Y' Then -- added for bug fix 7415801
2566 fnd_msg_pub.initialize;
2567 END IF; --bug fix 7415801
2568
2569 po_wip_integration_grp.cancel_document
2570 (p_api_version => 1.0,
2571 p_doc_type => PO_TBL_VARCHAR30(l_po_req.document_type),
2572 p_doc_subtype => PO_TBL_VARCHAR30(l_po_req.document_subtype),
2573 p_doc_id => PO_TBL_NUMBER(l_po_req.po_header_id),
2574 p_doc_num => PO_TBL_VARCHAR30(NULL),
2575 p_release_id => PO_TBL_NUMBER(l_po_req.po_release_id),
2576 p_release_num => PO_TBL_NUMBER(NULL),
2577 p_doc_line_id => PO_TBL_NUMBER(l_po_req.po_line_id),
2578 p_doc_line_num => PO_TBL_NUMBER(NULL),
2579 p_doc_line_loc_id => PO_TBL_NUMBER(l_po_req.po_line_location_id),
2580 p_doc_shipment_num => PO_TBL_NUMBER(NULL),
2581 p_source => NULL,
2582 p_cancel_date => SYSDATE,
2583 p_cancel_reason => NULL,
2584 p_cancel_reqs_flag => 'Y',
2585 p_print_flag => 'N',
2586 p_note_to_vendor => NULL,
2587 x_return_status => x_return_status,
2588 x_msg_count => l_msgCount,
2589 x_msg_data => l_msgData);
2590
2591 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
2592 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2593 fnd_message.set_token('MESSAGE', l_msgData);
2594 fnd_msg_pub.add;
2595 raise fnd_api.g_exc_unexpected_error;
2596 END IF;
2597 ELSE
2598 IF (l_logLevel <= wip_constants.full_logging) THEN
2599 l_debugMsg := 'req_header_id = ' || l_po_req.req_header_id|| ' ; '
2600 || 'req_line_id = ' || l_po_req.req_line_id;
2601
2602 wip_logger.log(p_msg => l_debugMsg,
2603 x_returnStatus => l_returnStatus);
2604 END IF;
2605 -- Call PO API to cancel requisition
2606 po_wip_integration_grp.cancel_requisition
2607 (p_api_version => 1.0,
2608 p_req_header_id => PO_TBL_NUMBER(l_po_req.req_header_id),
2609 p_req_line_id => PO_TBL_NUMBER(l_po_req.req_line_id),
2610 p_cancel_date => SYSDATE,
2611 p_cancel_reason => NULL,
2612 p_source => NULL,
2613 x_return_status => x_return_status,
2614 x_msg_count => l_msgCount,
2615 x_msg_data => l_msgData);
2616
2617 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
2618 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2619 fnd_message.set_token('MESSAGE', l_msgData);
2620 fnd_msg_pub.add;
2621 raise fnd_api.g_exc_unexpected_error;
2622 END IF;
2623 END IF;
2624 EXCEPTION
2625 WHEN others THEN
2626 l_err_count := l_err_count + 1;
2627 END;
2628 END LOOP;
2629
2630 IF (l_err_count > 0) THEN
2631 x_return_status := fnd_api.g_ret_sts_unexp_error;
2632 fnd_message.set_name('WIP','WIP_UNABLE_TO_CANCEL_PO');
2633 fnd_msg_pub.add;
2634 ELSE
2635 SELECT count(*)
2636 INTO l_pending_recs
2637 FROM po_requisitions_interface_all
2638 WHERE wip_entity_id = p_job_id;
2639 IF(l_pending_recs <> 0) THEN
2640 fnd_message.set_name('WIP', 'WIP_REQUISITION_PENDING');
2641 fnd_msg_pub.add;
2642 x_return_status := fnd_api.g_ret_sts_unexp_error;
2643 ELSE
2644 x_return_status := fnd_api.g_ret_sts_success;
2645 END IF;
2646 END IF;
2647 -- write to the log file
2648 IF (l_logLevel <= wip_constants.trace_logging) THEN
2649 wip_logger.exitPoint(p_procName => 'wip_osp.cancelPOReq',
2650 p_procReturnStatus => x_return_status,
2651 p_msg => 'procedure complete',
2652 x_returnStatus => l_returnStatus);
2653 END IF;
2654 EXCEPTION
2655 WHEN others THEN
2656 x_return_status := fnd_api.g_ret_sts_unexp_error;
2657 l_errMsg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
2658 IF (l_logLevel <= wip_constants.trace_logging) THEN
2659 wip_logger.exitPoint(p_procName => 'wip_osp.cancelPOReq',
2660 p_procReturnStatus => x_return_status,
2661 p_msg => l_errMsg,
2662 x_returnStatus => l_returnStatus);
2663 END IF;
2664 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
2665 fnd_message.set_token('MESSAGE', l_errMsg);
2666 fnd_msg_pub.add;
2667 END cancelPOReq;
2668
2669 /* Fix for bug 4446607: This function returns TRUE if a PO/REQ is ever
2670 * created for this particular job/operation, irrespective of whether the
2671 * PO/REQ is cancelled or closed. This will be used to determine whether
2672 * to call release_validation when rescheduling the job through mass-load.
2673 * We had been using PO_REQ_EXISTS but that would return FALSE if either
2674 * the PO/REQ is cancelled or if all the quantity is received for the PO.
2675 * Because of this, requisition creation was erroneously triggered when
2676 * rescheduling a job, whose associated PO has been received in total.
2677 */
2678 FUNCTION PO_REQ_CREATED ( p_wip_entity_id in NUMBER
2679 ,p_rep_sched_id in NUMBER
2680 ,p_organization_id in NUMBER
2681 ,p_op_seq_num in NUMBER default NULL
2682 ,p_entity_type in NUMBER
2683 ) RETURN BOOLEAN IS
2684
2685 CURSOR disc_check_po_req_cur IS
2686 SELECT 'No PO/REQ Created'
2687 FROM DUAL
2688 WHERE NOT EXISTS
2689 (SELECT '1'
2690 FROM PO_RELEASES_ALL PR,
2691 PO_HEADERS_ALL PH,
2692 PO_DISTRIBUTIONS_ALL PD
2693 WHERE PD.WIP_ENTITY_ID = p_wip_entity_id
2694 AND PD.DESTINATION_ORGANIZATION_ID = p_organization_id
2695 AND (p_op_seq_num is NULL
2696 OR PD.WIP_OPERATION_SEQ_NUM = p_op_seq_num)
2697 AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
2698 AND PR.PO_RELEASE_ID (+) = PD.PO_RELEASE_ID)
2699 AND NOT EXISTS
2700 (SELECT '1'
2701 FROM PO_REQUISITION_LINES_ALL PRL
2702 WHERE PRL.WIP_ENTITY_ID = p_wip_entity_id
2703 AND PRL.DESTINATION_ORGANIZATION_ID = p_organization_id
2704 AND (p_op_seq_num is NULL
2705 OR PRL.WIP_OPERATION_SEQ_NUM = p_op_seq_num))
2706 AND NOT EXISTS
2707 (SELECT '1'
2708 FROM PO_REQUISITIONS_INTERFACE_ALL PRI
2709 WHERE PRI.WIP_ENTITY_ID = p_wip_entity_id
2710 AND PRI.DESTINATION_ORGANIZATION_ID = p_organization_id
2711 AND (p_op_seq_num is NULL
2712 OR PRI.WIP_OPERATION_SEQ_NUM = p_op_seq_num));
2713
2714 CURSOR rep_check_po_req_cur IS
2715 SELECT 'No PO Created'
2716 FROM DUAL
2717 WHERE NOT EXISTS
2718 (SELECT '1'
2719 FROM PO_RELEASES_ALL PR,
2720 PO_HEADERS_ALL PH,
2721 PO_DISTRIBUTIONS_ALL PD
2722 WHERE PD.WIP_ENTITY_ID = p_wip_entity_id
2723 AND PD.DESTINATION_ORGANIZATION_ID = p_organization_id
2724 AND PD.WIP_REPETITIVE_SCHEDULE_ID = p_rep_sched_id
2725 AND (p_op_seq_num is NULL
2726 OR PD.WIP_OPERATION_SEQ_NUM = p_op_seq_num)
2727 AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
2728 AND PR.PO_RELEASE_ID (+) = PD.PO_RELEASE_ID)
2729 AND NOT EXISTS
2730 (SELECT '1'
2731 FROM PO_REQUISITION_LINES PRL
2732 WHERE PRL.WIP_ENTITY_ID = p_wip_entity_id
2733 AND PRL.DESTINATION_ORGANIZATION_ID = p_organization_id
2734 AND PRL.WIP_REPETITIVE_SCHEDULE_ID = p_rep_sched_id
2735 AND (p_op_seq_num is NULL
2736 OR PRL.WIP_OPERATION_SEQ_NUM = p_op_seq_num))
2737 AND NOT EXISTS
2738 (SELECT '1'
2739 FROM PO_REQUISITIONS_INTERFACE_ALL PRI
2740 WHERE PRI.WIP_ENTITY_ID = p_wip_entity_id
2741 AND PRI.DESTINATION_ORGANIZATION_ID = p_organization_id
2742 AND PRI.WIP_REPETITIVE_SCHEDULE_ID = p_rep_sched_id
2743 AND (p_op_seq_num is NULL
2744 OR PRI.WIP_OPERATION_SEQ_NUM = p_op_seq_num));
2745
2746 po_req_exist VARCHAR2(20);
2747
2748 BEGIN
2749 IF(p_entity_type = WIP_CONSTANTS.REPETITIVE) THEN
2750 OPEN rep_check_po_req_cur;
2751 FETCH rep_check_po_req_cur INTO po_req_exist;
2752
2753 IF(rep_check_po_req_cur%NOTFOUND) THEN
2754 CLOSE rep_check_po_req_cur;
2755 RETURN TRUE;
2756 ELSE
2757 CLOSE rep_check_po_req_cur;
2758 RETURN FALSE;
2759 END IF;
2760 ELSE /*FOR DISCRETE, OSFM, AND EAM*/
2761 OPEN disc_check_po_req_cur;
2762 FETCH disc_check_po_req_cur INTO po_req_exist;
2763
2764 IF(disc_check_po_req_cur%NOTFOUND) THEN
2765 CLOSE disc_check_po_req_cur;
2766 return TRUE;
2767 ELSE
2768 CLOSE disc_check_po_req_cur;
2769 return FALSE;
2770 END IF;
2771 END IF; -- End check POs and REQs
2772 END PO_REQ_CREATED;
2773 /* End of fix for Bug 4446607. */
2774
2775 /* Added this API for Bug 14208818.
2776 This function gives information if there are subsequent OSPs having location same as that of the current ship-to-location.
2777 This API returns 1 incase of back to back else returns 0.
2778 This API will be called from iSupplier portal while receiving ASNs.
2779 This will be used to default the transaction type for Back-to-Back OSP as well as trigger WIP Workflows for next suppliers. */
2780
2781 Function IS_BACK_TO_BACK_OSP( p_wip_entity_id in NUMBER,
2782 p_repetitive_id in NUMBER := NULL,
2783 p_op_seq_num in NUMBER,
2784 p_res_seq_num in NUMBER,
2785 p_location_id in NUMBER
2786 ) RETURN NUMBER
2787 IS
2788 l_count NUMBER := 0;
2789 BEGIN
2790 select count(1)
2791 into l_count
2792 from
2793 wip_operation_resources wor,
2794 wip_operations wo,
2795 bom_departments bd
2796 where
2797 wor.wip_entity_id = wo.wip_entity_id
2798 and wor.organization_id = wo.organization_id
2799 and wor.operation_seq_num = wo.operation_seq_num
2800 and wo.department_id = bd.department_id
2801 and wo.organization_id = bd.organization_id
2802 and wor.autocharge_type in (3,4)
2803 and wo.wip_entity_id = p_wip_entity_id
2804 and bd.location_id = p_location_id
2805 and nvl(wor.repetitive_schedule_id,-1) = nvl(p_repetitive_id,-1)
2806 and nvl(wo.repetitive_schedule_id,-1) = nvl(p_repetitive_id,-1)
2807 and ((wo.operation_seq_num = p_op_seq_num
2808 and wor.resource_seq_num > p_res_seq_num) or
2809 (wo.operation_seq_num = (select next_operation_seq_num
2810 from wip_operations
2811 where wip_entity_id = p_wip_entity_id
2812 and operation_seq_num = p_op_seq_num)))
2813 and rownum = 1;
2814 return l_count;
2815 END IS_BACK_TO_BACK_OSP;
2816 END WIP_OSP;
2817