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