[Home] [Help]
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;
56: AND WDJ.ORGANIZATION_ID = WO.ORGANIZATION_ID
57: AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
58: AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
59: AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
60: AND WOR.AUTOCHARGE_TYPE IN (WIP_CONSTANTS.PO_RECEIPT,
61: WIP_CONSTANTS.PO_MOVE)
62: ORDER BY WO.OPERATION_SEQ_NUM;
63:
64: CURSOR Crep IS
57: AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
58: AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
59: AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
60: AND WOR.AUTOCHARGE_TYPE IN (WIP_CONSTANTS.PO_RECEIPT,
61: WIP_CONSTANTS.PO_MOVE)
62: ORDER BY WO.OPERATION_SEQ_NUM;
63:
64: CURSOR Crep IS
65: SELECT WO.OPERATION_SEQ_NUM,
80: AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
81: AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
82: AND WOR.REPETITIVE_SCHEDULE_ID = WO.REPETITIVE_SCHEDULE_ID
83: AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
84: AND WOR.AUTOCHARGE_TYPE IN (WIP_CONSTANTS.PO_RECEIPT,
85: WIP_CONSTANTS.PO_MOVE)
86: ORDER BY WO.OPERATION_SEQ_NUM;
87:
88: CURSOR Cuom IS
81: AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
82: AND WOR.REPETITIVE_SCHEDULE_ID = WO.REPETITIVE_SCHEDULE_ID
83: AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
84: AND WOR.AUTOCHARGE_TYPE IN (WIP_CONSTANTS.PO_RECEIPT,
85: WIP_CONSTANTS.PO_MOVE)
86: ORDER BY WO.OPERATION_SEQ_NUM;
87:
88: CURSOR Cuom IS
89: SELECT msi.PRIMARY_UOM_CODE
97: BEGIN
98:
99: /* Bug 13768020: if the 'WIP:Launch Requisition Import for PO Receipt' is set to 'N'
100: do not launch Requistion Import for PO Receipt resource*/
101: if(fnd_profile.value('WIP_OSP_REQ') = WIP_CONSTANTS.NO) then
102: l_req_enabled := FALSE;
103: else
104: l_req_enabled := TRUE;
105: end if;
108: FOR cdis_rec in Cdisc LOOP
109: IF(cdis_rec.operation_seq_num <> op_seq_num OR
110: cdis_rec.resource_seq_num <> res_seq_num) THEN
111:
112: IF((cdis_rec.count_point_type <> WIP_CONSTANTS.NO_DIRECT) AND
113: ((cdis_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
114: OR (cdis_rec.first_op = 'YES' AND
115: cdis_rec.po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION)
116: )
109: IF(cdis_rec.operation_seq_num <> op_seq_num OR
110: cdis_rec.resource_seq_num <> res_seq_num) THEN
111:
112: IF((cdis_rec.count_point_type <> WIP_CONSTANTS.NO_DIRECT) AND
113: ((cdis_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
114: OR (cdis_rec.first_op = 'YES' AND
115: cdis_rec.po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION)
116: )
117: ) THEN
111:
112: IF((cdis_rec.count_point_type <> WIP_CONSTANTS.NO_DIRECT) AND
113: ((cdis_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
114: OR (cdis_rec.first_op = 'YES' AND
115: cdis_rec.po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION)
116: )
117: ) THEN
118: CREATE_REQUISITION(
119: P_Wip_Entity_Id => P_Wip_Entity_Id,
144: and nvl(wor.repetitive_schedule_id, -1)
145: = nvl(P_Repetitive_Schedule_Id, -1)
146: and wor.operation_seq_num = cdis_rec.operation_seq_num
147: and wor.resource_seq_num = cdis_rec.resource_seq_num
148: and wor.autocharge_type = WIP_CONSTANTS.PO_MOVE
149: and br.resource_id = wor.resource_id
150: and br.organization_id = wor.organization_id;
151: exception
152: when no_data_found then null;
152: when no_data_found then null;
153: end;
154:
155: IF((cdis_rec.first_op = 'YES') and
156: (cdis_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE) and
157: (l_osp_item_id <> -1)) THEN
158:
159: OPEN Cuom;
160: FETCH Cuom into l_primary_uom;
191: and operation_seq_num = cdis_rec.OPERATION_SEQ_NUM;
192: end if;
193: /* Fixed Bug# 1967211 */
194: /* Fix for Bug#2389789. Added po_creation_time and ospEnabled condition */
195: ELSIF (cdis_rec.autocharge_type = WIP_CONSTANTS.PO_RECEIPT and
196: /* Fix for bug 2777387: Reqimport should be spawned even if
197: PO Creation Time is set to At Operation, provided OSP resource
198: is attached to the first operation.
199: */
196: /* Fix for bug 2777387: Reqimport should be spawned even if
197: PO Creation Time is set to At Operation, provided OSP resource
198: is attached to the first operation.
199: */
200: (cdis_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE or
201: (cdis_rec.po_creation_time = WIP_CONSTANTS.AT_OPERATION
202: and cdis_rec.first_op = 'YES'))
203: AND l_req_enabled) /*Bug 13768020: launch requistion interface for po receipt resource when 'WIP:Launch Requisition Import for PO Receipt' is set to Yes*/
204: /* and wip_common_wf_pkg.ospEnabled ) */ -- FP Bug 5125900, Base Bug 4529326, Commented out this 'and' condition
197: PO Creation Time is set to At Operation, provided OSP resource
198: is attached to the first operation.
199: */
200: (cdis_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE or
201: (cdis_rec.po_creation_time = WIP_CONSTANTS.AT_OPERATION
202: and cdis_rec.first_op = 'YES'))
203: AND l_req_enabled) /*Bug 13768020: launch requistion interface for po receipt resource when 'WIP:Launch Requisition Import for PO Receipt' is set to Yes*/
204: /* and wip_common_wf_pkg.ospEnabled ) */ -- FP Bug 5125900, Base Bug 4529326, Commented out this 'and' condition
205: THEN
202: and cdis_rec.first_op = 'YES'))
203: AND l_req_enabled) /*Bug 13768020: launch requistion interface for po receipt resource when 'WIP:Launch Requisition Import for PO Receipt' is set to Yes*/
204: /* and wip_common_wf_pkg.ospEnabled ) */ -- FP Bug 5125900, Base Bug 4529326, Commented out this 'and' condition
205: THEN
206: l_launch_req_import := WIP_CONSTANTS.YES;
207: /* Fix for bug 3127921: Adding elsif condition to launch reqimport
208: if po_creation_time is at job schedule release and a PO Move
209: resource is present in some operation other than the first
210: operation
208: if po_creation_time is at job schedule release and a PO Move
209: resource is present in some operation other than the first
210: operation
211: */
212: ELSIF (cdis_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE and
213: cdis_rec.first_op <> 'YES' and
214: cdis_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE and
215: wip_common_wf_pkg.ospEnabled) then
216: l_launch_req_import := WIP_CONSTANTS.YES;
210: operation
211: */
212: ELSIF (cdis_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE and
213: cdis_rec.first_op <> 'YES' and
214: cdis_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE and
215: wip_common_wf_pkg.ospEnabled) then
216: l_launch_req_import := WIP_CONSTANTS.YES;
217: END IF;
218: ELSE -- if EAM work order
212: ELSIF (cdis_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE and
213: cdis_rec.first_op <> 'YES' and
214: cdis_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE and
215: wip_common_wf_pkg.ospEnabled) then
216: l_launch_req_import := WIP_CONSTANTS.YES;
217: END IF;
218: ELSE -- if EAM work order
219: /* Fix for Bug#2389789. Added po_creation_time and ospEnabled condition */
220: if(cdis_rec.po_creation_time
217: END IF;
218: ELSE -- if EAM work order
219: /* Fix for Bug#2389789. Added po_creation_time and ospEnabled condition */
220: if(cdis_rec.po_creation_time
221: IN(WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE,
222: WIP_CONSTANTS.AT_OPERATION))
223: /* and wip_common_wf_pkg.ospEnabled) */ -- FP Bug 5125900, Base Bug 4529326, Commented out this 'and' condition
224: THEN
225: -- l_po_receipt_found := TRUE;
218: ELSE -- if EAM work order
219: /* Fix for Bug#2389789. Added po_creation_time and ospEnabled condition */
220: if(cdis_rec.po_creation_time
221: IN(WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE,
222: WIP_CONSTANTS.AT_OPERATION))
223: /* and wip_common_wf_pkg.ospEnabled) */ -- FP Bug 5125900, Base Bug 4529326, Commented out this 'and' condition
224: THEN
225: -- l_po_receipt_found := TRUE;
226: -- We should always launch Req Import for EAM even if the PO
224: THEN
225: -- l_po_receipt_found := TRUE;
226: -- We should always launch Req Import for EAM even if the PO
227: -- creation time is "At Operation".(got confirmation from Amit)
228: l_launch_req_import := WIP_CONSTANTS.YES;
229: end if ;
230: END IF; --check for eam work order
231: END IF;
232: op_seq_num := cdis_rec.operation_seq_num;
236: FOR crep_rec in Crep LOOP
237: IF (crep_rec.operation_seq_num <> op_seq_num OR
238: crep_rec.resource_seq_num <> res_seq_num) THEN
239:
240: IF(crep_rec.count_point_type <> WIP_CONSTANTS.NO_DIRECT AND
241: ((crep_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
242: OR (crep_rec.first_op = 'YES' AND
243: crep_rec.po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION)
244: )
237: IF (crep_rec.operation_seq_num <> op_seq_num OR
238: crep_rec.resource_seq_num <> res_seq_num) THEN
239:
240: IF(crep_rec.count_point_type <> WIP_CONSTANTS.NO_DIRECT AND
241: ((crep_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
242: OR (crep_rec.first_op = 'YES' AND
243: crep_rec.po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION)
244: )
245: ) THEN
239:
240: IF(crep_rec.count_point_type <> WIP_CONSTANTS.NO_DIRECT AND
241: ((crep_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
242: OR (crep_rec.first_op = 'YES' AND
243: crep_rec.po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION)
244: )
245: ) THEN
246: CREATE_REQUISITION(
247: P_Wip_Entity_Id => P_Wip_Entity_Id,
265: and nvl(wor.repetitive_schedule_id, -1)
266: = nvl(P_Repetitive_Schedule_Id, -1)
267: and wor.operation_seq_num = crep_rec.operation_seq_num
268: and wor.resource_seq_num = crep_rec.resource_seq_num
269: and wor.autocharge_type = WIP_CONSTANTS.PO_MOVE
270: and br.resource_id = wor.resource_id
271: and br.organization_id = wor.organization_id;
272: EXCEPTION
273: when no_data_found then null;
272: EXCEPTION
273: when no_data_found then null;
274: END;
275: IF((crep_rec.first_op = 'YES') and
276: (crep_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE) and
277: (l_osp_item_id <> -1)) THEN
278:
279: OPEN Cuom;
280: FETCH Cuom into l_primary_uom;
312: and operation_seq_num = crep_rec.OPERATION_SEQ_NUM;
313: end if;
314: /* Fixed Bug# 1967211 */
315: /* Fix for Bug#2389789. Added po_creation_time and ospEnabled condition */
316: ELSIF (crep_rec.autocharge_type = WIP_CONSTANTS.PO_RECEIPT AND
317: /* Fix for bug 2777387: Reqimport should be spawned even if
318: PO Creation Time is set to At Operation, provided OSP resource
319: is attached to the first operation.
320: */
317: /* Fix for bug 2777387: Reqimport should be spawned even if
318: PO Creation Time is set to At Operation, provided OSP resource
319: is attached to the first operation.
320: */
321: (crep_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE or
322: (crep_rec.po_creation_time = WIP_CONSTANTS.AT_OPERATION AND
323: crep_rec.first_op = 'YES'))
324: AND l_req_enabled)/*Bug 13768020: launch requistion interface for po receipt resource when 'WIP:Launch Requisition Import for PO Receipt' is set to Yes*/
325: /* AND wip_common_wf_pkg.ospEnabled ) */ -- FP Bug 5125900, Base Bug 4529326, Commented out this 'and' condition
318: PO Creation Time is set to At Operation, provided OSP resource
319: is attached to the first operation.
320: */
321: (crep_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE or
322: (crep_rec.po_creation_time = WIP_CONSTANTS.AT_OPERATION AND
323: crep_rec.first_op = 'YES'))
324: AND l_req_enabled)/*Bug 13768020: launch requistion interface for po receipt resource when 'WIP:Launch Requisition Import for PO Receipt' is set to Yes*/
325: /* AND wip_common_wf_pkg.ospEnabled ) */ -- FP Bug 5125900, Base Bug 4529326, Commented out this 'and' condition
326: THEN
323: crep_rec.first_op = 'YES'))
324: AND l_req_enabled)/*Bug 13768020: launch requistion interface for po receipt resource when 'WIP:Launch Requisition Import for PO Receipt' is set to Yes*/
325: /* AND wip_common_wf_pkg.ospEnabled ) */ -- FP Bug 5125900, Base Bug 4529326, Commented out this 'and' condition
326: THEN
327: l_launch_req_import := WIP_CONSTANTS.YES;
328: /* Fix for bug 3127921: Adding elsif condition to launch reqimport
329: if po_creation_time is at job schedule release and a PO Move
330: resource is present in some operation other than the first
331: operation
329: if po_creation_time is at job schedule release and a PO Move
330: resource is present in some operation other than the first
331: operation
332: */
333: ELSIF (crep_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE AND
334: crep_rec.first_op <> 'YES' AND
335: crep_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE AND
336: wip_common_wf_pkg.ospEnabled) THEN
337: l_launch_req_import := WIP_CONSTANTS.YES;
331: operation
332: */
333: ELSIF (crep_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE AND
334: crep_rec.first_op <> 'YES' AND
335: crep_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE AND
336: wip_common_wf_pkg.ospEnabled) THEN
337: l_launch_req_import := WIP_CONSTANTS.YES;
338: END IF;
339: END IF;
333: ELSIF (crep_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE AND
334: crep_rec.first_op <> 'YES' AND
335: crep_rec.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE AND
336: wip_common_wf_pkg.ospEnabled) THEN
337: l_launch_req_import := WIP_CONSTANTS.YES;
338: END IF;
339: END IF;
340: op_seq_num := crep_rec.operation_seq_num;
341: res_seq_num := crep_rec.resource_seq_num;
344:
345: /* Fixed Bug# 1967211 */
346:
347: -- if (l_po_receipt_found = TRUE ) then
348: if(l_launch_req_import = WIP_CONSTANTS.YES) then
349:
350: -- get the OU, set context for MOAC
351: select to_number(ORG_INFORMATION3) into l_ou_id
352: from HR_ORGANIZATION_INFORMATION
365: raise fnd_api.g_exc_unexpected_error;
366: END;
367:
368: /*ER 4276433*/
369: if(fnd_profile.value('WIP_OSP_INITIATE_REQAPPR') = WIP_CONSTANTS.NO) then
370: l_init_reqappr := 'N';
371: else
372: l_init_reqappr := 'Y';
373: end if;
401: P_Organization_Id NUMBER,
402: P_Repetitive_Schedule_Id NUMBER,
403: P_Operation_Seq_Num NUMBER,
404: P_Resource_Seq_Num IN NUMBER DEFAULT NULL,
405: P_Run_ReqImport IN NUMBER DEFAULT WIP_CONSTANTS.NO
406: ) IS
407:
408: x_emp_found BOOLEAN;
409: x_no_loc_found BOOLEAN;
449: and wor.organization_id = P_Organization_Id
450: and wor.operation_seq_num = P_Operation_Seq_num
451: and wor.autocharge_type =
452: decode(wo.operation_seq_num,P_Operation_Seq_num,
453: WIP_CONSTANTS.PO_RECEIPT,WIP_CONSTANTS.PO_MOVE))
454: start with wo.wip_entity_id = P_Wip_Entity_Id
455: and wo.organization_id = P_Organization_Id
456: and wo.operation_seq_num = P_Operation_Seq_num
457: connect by wo.wip_entity_id = P_Wip_Entity_Id
538: DECODE (msi.revision_qty_control_code,
539: 1, null ,
540: 2, decode(br.purchase_item_id,
541: we.primary_item_id, DECODE (we.entity_type,
542: WIP_CONSTANTS.REPETITIVE, wrs.bom_revision,
543: /*Fixed bug2174078 to support eam and osfm*/
544: wdj.bom_revision),
545: BOM_REVISIONS.GET_ITEM_REVISION_FN (
546: x_released_revs_meaning, -- eco_status
548: br.ORGANIZATION_ID, -- org_id
549: br.purchase_item_id, -- item_id
550: /*Fixed bug2174078 to support eam and osfm*/
551: decode (we.entity_type,-- rev_date
552: WIP_CONSTANTS.REPETITIVE, wrs.FIRST_UNIT_START_DATE,
553: /* Fixed for Bug1623063 */
554: /*Bug 14286109:user job released date instead of job start date to retrieve revision */
555: NVL(wdj.DATE_RELEASED,wdj.SCHEDULED_START_DATE))))),
556: msi.primary_uom_code,
555: NVL(wdj.DATE_RELEASED,wdj.SCHEDULED_START_DATE))))),
556: msi.primary_uom_code,
557: DECODE(msi.outside_operation_uom_type,
558: 'RESOURCE',
559: DECODE(wor.BASIS_TYPE, WIP_CONSTANTS.PER_ITEM,
560: round (wor.usage_rate_or_amount * additional_quantity,6),
561: round(wor.usage_rate_or_amount,6)),
562: 'ASSEMBLY',
563: DECODE(wor.BASIS_TYPE,
560: round (wor.usage_rate_or_amount * additional_quantity,6),
561: round(wor.usage_rate_or_amount,6)),
562: 'ASSEMBLY',
563: DECODE(wor.BASIS_TYPE,
564: WIP_CONSTANTS.PER_ITEM, additional_quantity,1)),
565: 3,
566: DECODE(we.entity_type,
567: WIP_CONSTANTS.REPETITIVE, wrs.OUTSIDE_PROCESSING_ACCOUNT,
568: wdj.OUTSIDE_PROCESSING_ACCOUNT),
563: DECODE(wor.BASIS_TYPE,
564: WIP_CONSTANTS.PER_ITEM, additional_quantity,1)),
565: 3,
566: DECODE(we.entity_type,
567: WIP_CONSTANTS.REPETITIVE, wrs.OUTSIDE_PROCESSING_ACCOUNT,
568: wdj.OUTSIDE_PROCESSING_ACCOUNT),
569: bd.location_id,
570: fu.employee_id,
571: wor.wip_entity_id,
569: bd.location_id,
570: fu.employee_id,
571: wor.wip_entity_id,
572: DECODE(we.entity_type,
573: WIP_CONSTANTS.REPETITIVE, wrs.line_id,
574: NULL),
575: wor.operation_seq_num,
576: wor.resource_seq_num,
577: wor.resource_id,
578: P_Repetitive_Schedule_Id,
579: /* Fix Bug#2374334 */
580: /* Bug 4398047 commented following portion of the sql
581: DECODE(we.entity_type,
582: WIP_CONSTANTS.LOTBASED, bcd1.calendar_date,*/
583: /*Bug 14687249: when post processing leadtime is 0, use operation date directly to populate need_by_date, so it will not flip to the next working date.*/
584: Decode(NVL(msi.postprocessing_lead_time,0),
585: 0,DECODE(we.entity_type,
586: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
582: WIP_CONSTANTS.LOTBASED, bcd1.calendar_date,*/
583: /*Bug 14687249: when post processing leadtime is 0, use operation date directly to populate need_by_date, so it will not flip to the next working date.*/
584: Decode(NVL(msi.postprocessing_lead_time,0),
585: 0,DECODE(we.entity_type,
586: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
587: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
588: DECODE(op1.next_operation_seq_num,
589: NULL, op1.last_unit_completion_date,
590: op2.first_unit_start_date)),
583: /*Bug 14687249: when post processing leadtime is 0, use operation date directly to populate need_by_date, so it will not flip to the next working date.*/
584: Decode(NVL(msi.postprocessing_lead_time,0),
585: 0,DECODE(we.entity_type,
586: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
587: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
588: DECODE(op1.next_operation_seq_num,
589: NULL, op1.last_unit_completion_date,
590: op2.first_unit_start_date)),
591: (bcd3.calendar_date +
589: NULL, op1.last_unit_completion_date,
590: op2.first_unit_start_date)),
591: (bcd3.calendar_date +
592: (DECODE(we.entity_type,
593: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
594: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
595: DECODE(op1.next_operation_seq_num,
596: NULL, op1.last_unit_completion_date,
597: op2.first_unit_start_date)) -
590: op2.first_unit_start_date)),
591: (bcd3.calendar_date +
592: (DECODE(we.entity_type,
593: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
594: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
595: DECODE(op1.next_operation_seq_num,
596: NULL, op1.last_unit_completion_date,
597: op2.first_unit_start_date)) -
598: TRUNC(DECODE(we.entity_type,
595: DECODE(op1.next_operation_seq_num,
596: NULL, op1.last_unit_completion_date,
597: op2.first_unit_start_date)) -
598: TRUNC(DECODE(we.entity_type,
599: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
600: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
601: DECODE(op1.next_operation_seq_num,
602: NULL, op1.last_unit_completion_date,
603: op2.first_unit_start_date)))))),
596: NULL, op1.last_unit_completion_date,
597: op2.first_unit_start_date)) -
598: TRUNC(DECODE(we.entity_type,
599: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
600: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
601: DECODE(op1.next_operation_seq_num,
602: NULL, op1.last_unit_completion_date,
603: op2.first_unit_start_date)))))),
604: 'Y',
632: and NVL(op2.repetitive_schedule_id,-1)=
633: NVL(P_repetitive_schedule_id, -1)
634: and op2.organization_id = op1.organization_id
635: and op2.wip_entity_id = op1.wip_entity_id
636: and wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
637: WIP_CONSTANTS.PO_MOVE)
638: and op2.operation_seq_num = NVL(op1.next_operation_seq_num,
639: op1.operation_seq_num)
640: AND wor.organization_id = br.organization_id
633: NVL(P_repetitive_schedule_id, -1)
634: and op2.organization_id = op1.organization_id
635: and op2.wip_entity_id = op1.wip_entity_id
636: and wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
637: WIP_CONSTANTS.PO_MOVE)
638: and op2.operation_seq_num = NVL(op1.next_operation_seq_num,
639: op1.operation_seq_num)
640: AND wor.organization_id = br.organization_id
641: AND wor.resource_id = br.resource_id
640: AND wor.organization_id = br.organization_id
641: AND wor.resource_id = br.resource_id
642: /* Additional requisitions are created only
643: for resources/assy of basis type ITEM. */
644: AND wor.basis_type = WIP_CONSTANTS.PER_ITEM
645: AND br.organization_id = msi.organization_id
646: AND br.purchase_item_id = msi.inventory_item_id
647: AND FND_GLOBAL.User_Id = fu.user_id
648: AND op1.organization_id = bd.organization_id
647: AND FND_GLOBAL.User_Id = fu.user_id
648: AND op1.organization_id = bd.organization_id
649: /* Fix for bug 3092030: Corrected condition to ensure we insert
650: correct deliver_to_location_id */
651: AND ( (wor.autocharge_type = WIP_CONSTANTS.PO_RECEIPT and
652: op1.department_id = bd.department_id)
653: OR (wor.autocharge_type = WIP_CONSTANTS.PO_MOVE and
654: op2.department_id = bd.department_id))
655: AND mp.organization_id = op1.organization_id
649: /* Fix for bug 3092030: Corrected condition to ensure we insert
650: correct deliver_to_location_id */
651: AND ( (wor.autocharge_type = WIP_CONSTANTS.PO_RECEIPT and
652: op1.department_id = bd.department_id)
653: OR (wor.autocharge_type = WIP_CONSTANTS.PO_MOVE and
654: op2.department_id = bd.department_id))
655: AND mp.organization_id = op1.organization_id
656: AND hoi.organization_id = op1.organization_id
657: AND hoi.ORG_INFORMATION_CONTEXT = l_org_acct_ctxt
673: NVL(msi.fixed_lead_time,0) +
674: (NVL(msi.variable_lead_time,0) *
675: DECODE(msi.outside_operation_uom_type,
676: 'RESOURCE',
677: DECODE(wor.basis_type, WIP_CONSTANTS.PER_ITEM,
678: wor.usage_rate_or_amount * op1.scheduled_quantity,
679: wor.usage_rate_or_amount),
680: 'ASSEMBLY',
681: DECODE(wor.basis_type, WIP_CONSTANTS.PER_ITEM,
677: DECODE(wor.basis_type, WIP_CONSTANTS.PER_ITEM,
678: wor.usage_rate_or_amount * op1.scheduled_quantity,
679: wor.usage_rate_or_amount),
680: 'ASSEMBLY',
681: DECODE(wor.basis_type, WIP_CONSTANTS.PER_ITEM,
682: op1.scheduled_quantity,
683: 1)
684: )) +
685: NVL(msi.postprocessing_lead_time,0))) end commenting out for Bug 4398047 */
687: AND bcd4.calendar_code = mp.calendar_code
688: AND bcd4.exception_set_id = mp.calendar_exception_set_id
689: AND bcd4.calendar_date =
690: TRUNC(DECODE(we.entity_type,
691: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
692: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
693: DECODE(op1.next_operation_seq_num,
694: NULL, op1.last_unit_completion_date,
695: op2.first_unit_start_date)))
688: AND bcd4.exception_set_id = mp.calendar_exception_set_id
689: AND bcd4.calendar_date =
690: TRUNC(DECODE(we.entity_type,
691: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
692: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
693: DECODE(op1.next_operation_seq_num,
694: NULL, op1.last_unit_completion_date,
695: op2.first_unit_start_date)))
696: AND bcd3.calendar_code = mp.calendar_code
733: DECODE (msi.revision_qty_control_code,
734: 1, null ,
735: 2, decode(br.purchase_item_id,
736: we.primary_item_id, DECODE (we.entity_type,
737: WIP_CONSTANTS.REPETITIVE,wrs.bom_revision,
738: wdj.bom_revision),
739: BOM_REVISIONS.GET_ITEM_REVISION_FN (
740: x_released_revs_meaning, -- eco_status
741: 'ALL', -- examine_type
741: 'ALL', -- examine_type
742: br.ORGANIZATION_ID, -- org_id
743: br.purchase_item_id, -- item_id
744: decode (we.entity_type,-- rev_date
745: WIP_CONSTANTS.REPETITIVE, wrs.FIRST_UNIT_START_DATE,
746: /*Bug 14286109:user job released date instead of job start date to retrieve revision */
747: NVL(wdj.DATE_RELEASED,wdj.SCHEDULED_START_DATE))))), /* Fixed Bug# 1623063 */
748: msi.primary_uom_code,
749: DECODE(msi.outside_operation_uom_type,
748: msi.primary_uom_code,
749: DECODE(msi.outside_operation_uom_type,
750: 'RESOURCE',
751: DECODE(wor.BASIS_TYPE,
752: WIP_CONSTANTS.PER_ITEM, round (wor.usage_rate_or_amount *
753: op1.scheduled_quantity,6),
754: round(wor.usage_rate_or_amount,6)),
755: 'ASSEMBLY',
756: DECODE(wor.BASIS_TYPE,
753: op1.scheduled_quantity,6),
754: round(wor.usage_rate_or_amount,6)),
755: 'ASSEMBLY',
756: DECODE(wor.BASIS_TYPE,
757: WIP_CONSTANTS.PER_ITEM, op1.scheduled_quantity,1)),
758: 3,
759: DECODE(we.entity_type,
760: WIP_CONSTANTS.REPETITIVE, wrs.OUTSIDE_PROCESSING_ACCOUNT,
761: wdj.OUTSIDE_PROCESSING_ACCOUNT),
756: DECODE(wor.BASIS_TYPE,
757: WIP_CONSTANTS.PER_ITEM, op1.scheduled_quantity,1)),
758: 3,
759: DECODE(we.entity_type,
760: WIP_CONSTANTS.REPETITIVE, wrs.OUTSIDE_PROCESSING_ACCOUNT,
761: wdj.OUTSIDE_PROCESSING_ACCOUNT),
762: bd.location_id,
763: fu.employee_id,
764: wor.wip_entity_id,
762: bd.location_id,
763: fu.employee_id,
764: wor.wip_entity_id,
765: DECODE(we.entity_type,
766: WIP_CONSTANTS.REPETITIVE, wrs.line_id,
767: NULL),
768: wor.operation_seq_num ,
769: wor.resource_seq_num,
770: wor.resource_id,
771: P_Repetitive_Schedule_Id,
772: /* Fix Bug#2374334 */
773: /* Bug 4398047 Commented out following portion of the sql
774: DECODE(we.entity_type,
775: WIP_CONSTANTS.LOTBASED, bcd1.calendar_date,*/
776: /*Bug 14687249: when post processing leadtime is 0, use operation date directly to populate need_by_date, so it will not flip to the next working date.*/
777: Decode(NVL(msi.postprocessing_lead_time,0),
778: 0,DECODE(we.entity_type,
779: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
775: WIP_CONSTANTS.LOTBASED, bcd1.calendar_date,*/
776: /*Bug 14687249: when post processing leadtime is 0, use operation date directly to populate need_by_date, so it will not flip to the next working date.*/
777: Decode(NVL(msi.postprocessing_lead_time,0),
778: 0,DECODE(we.entity_type,
779: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
780: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
781: DECODE(op1.next_operation_seq_num,
782: NULL, op1.last_unit_completion_date,
783: op2.first_unit_start_date)),
776: /*Bug 14687249: when post processing leadtime is 0, use operation date directly to populate need_by_date, so it will not flip to the next working date.*/
777: Decode(NVL(msi.postprocessing_lead_time,0),
778: 0,DECODE(we.entity_type,
779: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
780: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
781: DECODE(op1.next_operation_seq_num,
782: NULL, op1.last_unit_completion_date,
783: op2.first_unit_start_date)),
784: (bcd3.calendar_date +
782: NULL, op1.last_unit_completion_date,
783: op2.first_unit_start_date)),
784: (bcd3.calendar_date +
785: (DECODE(we.entity_type,
786: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
787: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
788: DECODE(op1.next_operation_seq_num,
789: NULL, op1.last_unit_completion_date,
790: op2.first_unit_start_date)) -
783: op2.first_unit_start_date)),
784: (bcd3.calendar_date +
785: (DECODE(we.entity_type,
786: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
787: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
788: DECODE(op1.next_operation_seq_num,
789: NULL, op1.last_unit_completion_date,
790: op2.first_unit_start_date)) -
791: TRUNC(DECODE(we.entity_type,
788: DECODE(op1.next_operation_seq_num,
789: NULL, op1.last_unit_completion_date,
790: op2.first_unit_start_date)) -
791: TRUNC(DECODE(we.entity_type,
792: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
793: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
794: DECODE(op1.next_operation_seq_num,
795: NULL, op1.last_unit_completion_date,
796: op2.first_unit_start_date)))))),
789: NULL, op1.last_unit_completion_date,
790: op2.first_unit_start_date)) -
791: TRUNC(DECODE(we.entity_type,
792: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
793: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
794: DECODE(op1.next_operation_seq_num,
795: NULL, op1.last_unit_completion_date,
796: op2.first_unit_start_date)))))),
797: 'Y',
826: and NVL(op2.repetitive_schedule_id,-1)=
827: NVL(P_repetitive_schedule_id, -1)
828: and op2.organization_id = op1.organization_id
829: and op2.wip_entity_id = op1.wip_entity_id
830: and wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
831: WIP_CONSTANTS.PO_MOVE)
832: and op2.operation_seq_num = NVL(op1.next_operation_seq_num,
833: op1.operation_seq_num)
834: AND wor.organization_id = br.organization_id
827: NVL(P_repetitive_schedule_id, -1)
828: and op2.organization_id = op1.organization_id
829: and op2.wip_entity_id = op1.wip_entity_id
830: and wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
831: WIP_CONSTANTS.PO_MOVE)
832: and op2.operation_seq_num = NVL(op1.next_operation_seq_num,
833: op1.operation_seq_num)
834: AND wor.organization_id = br.organization_id
835: AND wor.resource_id = br.resource_id
838: AND FND_GLOBAL.User_Id = fu.user_id
839: AND op1.organization_id = bd.organization_id
840: /* Fix for bug 3092030: Corrected condition to ensure we insert
841: correct deliver_to_location_id */
842: AND ( (wor.autocharge_type = WIP_CONSTANTS.PO_RECEIPT and
843: op1.department_id = bd.department_id)
844: OR (wor.autocharge_type = WIP_CONSTANTS.PO_MOVE and
845: op2.department_id = bd.department_id))
846: AND mp.organization_id = op1.organization_id
840: /* Fix for bug 3092030: Corrected condition to ensure we insert
841: correct deliver_to_location_id */
842: AND ( (wor.autocharge_type = WIP_CONSTANTS.PO_RECEIPT and
843: op1.department_id = bd.department_id)
844: OR (wor.autocharge_type = WIP_CONSTANTS.PO_MOVE and
845: op2.department_id = bd.department_id))
846: AND mp.organization_id = op1.organization_id
847: AND hoi.organization_id = op1.organization_id
848: AND hoi.ORG_INFORMATION_CONTEXT = l_org_acct_ctxt
864: NVL(msi.fixed_lead_time,0) +
865: (NVL(msi.variable_lead_time,0) *
866: DECODE(msi.outside_operation_uom_type,
867: 'RESOURCE',
868: DECODE(wor.basis_type, WIP_CONSTANTS.PER_ITEM,
869: wor.usage_rate_or_amount * op1.scheduled_quantity,
870: wor.usage_rate_or_amount),
871: 'ASSEMBLY',
872: DECODE(wor.basis_type, WIP_CONSTANTS.PER_ITEM,
868: DECODE(wor.basis_type, WIP_CONSTANTS.PER_ITEM,
869: wor.usage_rate_or_amount * op1.scheduled_quantity,
870: wor.usage_rate_or_amount),
871: 'ASSEMBLY',
872: DECODE(wor.basis_type, WIP_CONSTANTS.PER_ITEM,
873: op1.scheduled_quantity,
874: 1)
875: )) +
876: NVL(msi.postprocessing_lead_time,0))) end of commented sql for bug 4398047 */
878: AND bcd4.calendar_code = mp.calendar_code
879: AND bcd4.exception_set_id = mp.calendar_exception_set_id
880: AND bcd4.calendar_date =
881: TRUNC(DECODE(we.entity_type,
882: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
883: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
884: DECODE(op1.next_operation_seq_num,
885: NULL, op1.last_unit_completion_date,
886: op2.first_unit_start_date)))
879: AND bcd4.exception_set_id = mp.calendar_exception_set_id
880: AND bcd4.calendar_date =
881: TRUNC(DECODE(we.entity_type,
882: WIP_CONSTANTS.EAM, op1.last_unit_completion_date,
883: WIP_CONSTANTS.LOTBASED, op1.last_unit_completion_date, -- Bug 4398047 Added this line
884: DECODE(op1.next_operation_seq_num,
885: NULL, op1.last_unit_completion_date,
886: op2.first_unit_start_date)))
887: AND bcd3.calendar_code = mp.calendar_code
889: AND bcd3.seq_num = (bcd4.next_seq_num -
890: CEIL(NVL(msi.postprocessing_lead_time,0)));
891: END IF;
892:
893: IF (P_Run_ReqImport = WIP_CONSTANTS.YES) THEN
894:
895: -- get the OU, set context for MOAC
896: select to_number(ORG_INFORMATION3) into l_ou_id
897: from HR_ORGANIZATION_INFORMATION
910: raise fnd_api.g_exc_unexpected_error;
911: END;
912:
913: /*ER 4276433*/
914: if(fnd_profile.value('WIP_OSP_INITIATE_REQAPPR') = WIP_CONSTANTS.NO) then
915: l_init_reqappr := 'N';
916: else
917: l_init_reqappr := 'Y';
918: end if;
1065:
1066: po_req_exist VARCHAR2(20);
1067:
1068: begin
1069: IF(p_entity_type = WIP_CONSTANTS.REPETITIVE) THEN
1070: OPEN rep_check_po_req_cur;
1071: FETCH rep_check_po_req_cur INTO po_req_exist;
1072:
1073: IF (rep_check_po_req_cur%FOUND) THEN
1295: WHERE wor.organization_id = c_org_id
1296: AND wor.wip_entity_id = c_wip_entity_id
1297: AND wor.operation_seq_num = c_to_op
1298: AND c_fm_op < c_to_op
1299: AND c_to_step = WIP_CONSTANTS.QUEUE
1300: AND (c_entity_type IN (WIP_CONSTANTS.DISCRETE,
1301: WIP_CONSTANTS.LOTBASED)
1302: OR
1303: (c_entity_type = WIP_CONSTANTS.REPETITIVE AND
1296: AND wor.wip_entity_id = c_wip_entity_id
1297: AND wor.operation_seq_num = c_to_op
1298: AND c_fm_op < c_to_op
1299: AND c_to_step = WIP_CONSTANTS.QUEUE
1300: AND (c_entity_type IN (WIP_CONSTANTS.DISCRETE,
1301: WIP_CONSTANTS.LOTBASED)
1302: OR
1303: (c_entity_type = WIP_CONSTANTS.REPETITIVE AND
1304: wor.repetitive_schedule_id IN
1297: AND wor.operation_seq_num = c_to_op
1298: AND c_fm_op < c_to_op
1299: AND c_to_step = WIP_CONSTANTS.QUEUE
1300: AND (c_entity_type IN (WIP_CONSTANTS.DISCRETE,
1301: WIP_CONSTANTS.LOTBASED)
1302: OR
1303: (c_entity_type = WIP_CONSTANTS.REPETITIVE AND
1304: wor.repetitive_schedule_id IN
1305: (SELECT wrs.repetitive_schedule_id
1299: AND c_to_step = WIP_CONSTANTS.QUEUE
1300: AND (c_entity_type IN (WIP_CONSTANTS.DISCRETE,
1301: WIP_CONSTANTS.LOTBASED)
1302: OR
1303: (c_entity_type = WIP_CONSTANTS.REPETITIVE AND
1304: wor.repetitive_schedule_id IN
1305: (SELECT wrs.repetitive_schedule_id
1306: FROM wip_repetitive_schedules wrs
1307: WHERE wrs.wip_entity_id = c_wip_entity_id
1306: FROM wip_repetitive_schedules wrs
1307: WHERE wrs.wip_entity_id = c_wip_entity_id
1308: AND wrs.organization_id = c_org_id
1309: AND wrs.line_id = c_line_id
1310: AND wrs.status_type in (WIP_CONSTANTS.RELEASED,
1311: WIP_CONSTANTS.COMP_CHRG)
1312: )
1313: ))
1314: AND wo1.organization_id = wor.organization_id
1307: WHERE wrs.wip_entity_id = c_wip_entity_id
1308: AND wrs.organization_id = c_org_id
1309: AND wrs.line_id = c_line_id
1310: AND wrs.status_type in (WIP_CONSTANTS.RELEASED,
1311: WIP_CONSTANTS.COMP_CHRG)
1312: )
1313: ))
1314: AND wo1.organization_id = wor.organization_id
1315: AND wo1.wip_entity_id = wor.wip_entity_id
1319: AND wo2.organization_id = wo1.organization_id
1320: AND wo2.wip_entity_id = wo1.wip_entity_id
1321: AND NVL(wo2.repetitive_schedule_id,-1) =
1322: NVL(wo1.repetitive_schedule_id,-1)
1323: AND ((wor.autocharge_type = WIP_CONSTANTS.PO_RECEIPT AND
1324: wo2.operation_seq_num = wor.operation_seq_num)
1325: OR
1326: (wor.autocharge_type = WIP_CONSTANTS.PO_MOVE AND
1327: ((wo1.next_operation_seq_num IS NOT NULL AND
1322: NVL(wo1.repetitive_schedule_id,-1)
1323: AND ((wor.autocharge_type = WIP_CONSTANTS.PO_RECEIPT AND
1324: wo2.operation_seq_num = wor.operation_seq_num)
1325: OR
1326: (wor.autocharge_type = WIP_CONSTANTS.PO_MOVE AND
1327: ((wo1.next_operation_seq_num IS NOT NULL AND
1328: wo1.next_operation_seq_num = wo2.operation_seq_num)
1329: OR
1330: (wo1.next_operation_seq_num IS NULL AND
1343: WHERE wor.organization_id = c_org_id
1344: AND wor.wip_entity_id = c_wip_entity_id
1345: AND wor.operation_seq_num = c_to_op
1346: AND c_fm_op < c_to_op
1347: AND c_to_step = WIP_CONSTANTS.QUEUE
1348: AND wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
1349: WIP_CONSTANTS.PO_MOVE)
1350: AND (c_entity_type IN (WIP_CONSTANTS.DISCRETE,
1351: WIP_CONSTANTS.LOTBASED)
1344: AND wor.wip_entity_id = c_wip_entity_id
1345: AND wor.operation_seq_num = c_to_op
1346: AND c_fm_op < c_to_op
1347: AND c_to_step = WIP_CONSTANTS.QUEUE
1348: AND wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
1349: WIP_CONSTANTS.PO_MOVE)
1350: AND (c_entity_type IN (WIP_CONSTANTS.DISCRETE,
1351: WIP_CONSTANTS.LOTBASED)
1352: OR
1345: AND wor.operation_seq_num = c_to_op
1346: AND c_fm_op < c_to_op
1347: AND c_to_step = WIP_CONSTANTS.QUEUE
1348: AND wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
1349: WIP_CONSTANTS.PO_MOVE)
1350: AND (c_entity_type IN (WIP_CONSTANTS.DISCRETE,
1351: WIP_CONSTANTS.LOTBASED)
1352: OR
1353: (c_entity_type = WIP_CONSTANTS.REPETITIVE AND
1346: AND c_fm_op < c_to_op
1347: AND c_to_step = WIP_CONSTANTS.QUEUE
1348: AND wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
1349: WIP_CONSTANTS.PO_MOVE)
1350: AND (c_entity_type IN (WIP_CONSTANTS.DISCRETE,
1351: WIP_CONSTANTS.LOTBASED)
1352: OR
1353: (c_entity_type = WIP_CONSTANTS.REPETITIVE AND
1354: wor.repetitive_schedule_id IN
1347: AND c_to_step = WIP_CONSTANTS.QUEUE
1348: AND wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
1349: WIP_CONSTANTS.PO_MOVE)
1350: AND (c_entity_type IN (WIP_CONSTANTS.DISCRETE,
1351: WIP_CONSTANTS.LOTBASED)
1352: OR
1353: (c_entity_type = WIP_CONSTANTS.REPETITIVE AND
1354: wor.repetitive_schedule_id IN
1355: (SELECT repetitive_schedule_id
1349: WIP_CONSTANTS.PO_MOVE)
1350: AND (c_entity_type IN (WIP_CONSTANTS.DISCRETE,
1351: WIP_CONSTANTS.LOTBASED)
1352: OR
1353: (c_entity_type = WIP_CONSTANTS.REPETITIVE AND
1354: wor.repetitive_schedule_id IN
1355: (SELECT repetitive_schedule_id
1356: FROM wip_repetitive_schedules wrs
1357: WHERE wrs.organization_id = c_org_id
1356: FROM wip_repetitive_schedules wrs
1357: WHERE wrs.organization_id = c_org_id
1358: AND wrs.wip_entity_id = c_wip_entity_id
1359: AND wrs.line_id = c_line_id
1360: AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
1361: WIP_CONSTANTS.COMP_CHRG)
1362: )
1363: ))
1364: AND NOT EXISTS
1357: WHERE wrs.organization_id = c_org_id
1358: AND wrs.wip_entity_id = c_wip_entity_id
1359: AND wrs.line_id = c_line_id
1360: AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
1361: WIP_CONSTANTS.COMP_CHRG)
1362: )
1363: ))
1364: AND NOT EXISTS
1365: (SELECT 'Current user is an employee'
1406: CURSOR c_job_schedule IS
1407: SELECT wdj.wip_entity_id job_id,
1408: to_number(null) rep_schedule_id
1409: FROM wip_discrete_jobs wdj
1410: WHERE wdj.status_type IN (WIP_CONSTANTS.RELEASED,
1411: WIP_CONSTANTS.UNRELEASED,
1412: WIP_CONSTANTS.HOLD)
1413: AND (p_project_id IS NULL OR wdj.project_id = p_project_id)
1414: AND (p_task_id IS NULL OR wdj.task_id = p_task_id)
1407: SELECT wdj.wip_entity_id job_id,
1408: to_number(null) rep_schedule_id
1409: FROM wip_discrete_jobs wdj
1410: WHERE wdj.status_type IN (WIP_CONSTANTS.RELEASED,
1411: WIP_CONSTANTS.UNRELEASED,
1412: WIP_CONSTANTS.HOLD)
1413: AND (p_project_id IS NULL OR wdj.project_id = p_project_id)
1414: AND (p_task_id IS NULL OR wdj.task_id = p_task_id)
1415: AND wdj.organization_id = p_org_id
1408: to_number(null) rep_schedule_id
1409: FROM wip_discrete_jobs wdj
1410: WHERE wdj.status_type IN (WIP_CONSTANTS.RELEASED,
1411: WIP_CONSTANTS.UNRELEASED,
1412: WIP_CONSTANTS.HOLD)
1413: AND (p_project_id IS NULL OR wdj.project_id = p_project_id)
1414: AND (p_task_id IS NULL OR wdj.task_id = p_task_id)
1415: AND wdj.organization_id = p_org_id
1416: AND p_entity_type <> WIP_CONSTANTS.REPETITIVE
1412: WIP_CONSTANTS.HOLD)
1413: AND (p_project_id IS NULL OR wdj.project_id = p_project_id)
1414: AND (p_task_id IS NULL OR wdj.task_id = p_task_id)
1415: AND wdj.organization_id = p_org_id
1416: AND p_entity_type <> WIP_CONSTANTS.REPETITIVE
1417: UNION ALL
1418: SELECT wrs.wip_entity_id job_id,
1419: wrs.repetitive_schedule_id rep_schedule_id
1420: FROM wip_repetitive_schedules wrs
1417: UNION ALL
1418: SELECT wrs.wip_entity_id job_id,
1419: wrs.repetitive_schedule_id rep_schedule_id
1420: FROM wip_repetitive_schedules wrs
1421: WHERE wrs.status_type IN (WIP_CONSTANTS.RELEASED,
1422: WIP_CONSTANTS.UNRELEASED,
1423: WIP_CONSTANTS.HOLD)
1424: AND wrs.organization_id = p_org_id
1425: AND p_entity_type = WIP_CONSTANTS.REPETITIVE;
1418: SELECT wrs.wip_entity_id job_id,
1419: wrs.repetitive_schedule_id rep_schedule_id
1420: FROM wip_repetitive_schedules wrs
1421: WHERE wrs.status_type IN (WIP_CONSTANTS.RELEASED,
1422: WIP_CONSTANTS.UNRELEASED,
1423: WIP_CONSTANTS.HOLD)
1424: AND wrs.organization_id = p_org_id
1425: AND p_entity_type = WIP_CONSTANTS.REPETITIVE;
1426:
1419: wrs.repetitive_schedule_id rep_schedule_id
1420: FROM wip_repetitive_schedules wrs
1421: WHERE wrs.status_type IN (WIP_CONSTANTS.RELEASED,
1422: WIP_CONSTANTS.UNRELEASED,
1423: WIP_CONSTANTS.HOLD)
1424: AND wrs.organization_id = p_org_id
1425: AND p_entity_type = WIP_CONSTANTS.REPETITIVE;
1426:
1427: CURSOR c_po_req (p_job_id NUMBER,
1421: WHERE wrs.status_type IN (WIP_CONSTANTS.RELEASED,
1422: WIP_CONSTANTS.UNRELEASED,
1423: WIP_CONSTANTS.HOLD)
1424: AND wrs.organization_id = p_org_id
1425: AND p_entity_type = WIP_CONSTANTS.REPETITIVE;
1426:
1427: CURSOR c_po_req (p_job_id NUMBER,
1428: p_repetitive_id NUMBER) IS
1429: SELECT pd.po_header_id po_header_id,
1526: p_item_id NUMBER) IS
1527: /*Bug 14687249: when post processing leadtime is 0, use operation date directly to populate need_by_date, so it will not flip to the next working date.*/
1528: SELECT Decode(NVL(msi.postprocessing_lead_time,0),
1529: 0,DECODE(p_entity_type,
1530: WIP_CONSTANTS.EAM, wo1.last_unit_completion_date,
1531: DECODE(wo1.next_operation_seq_num,
1532: NULL, wo1.last_unit_completion_date,
1533: wo2.first_unit_start_date)),
1534: (bcd1.calendar_date +
1532: NULL, wo1.last_unit_completion_date,
1533: wo2.first_unit_start_date)),
1534: (bcd1.calendar_date +
1535: (DECODE( p_entity_type,
1536: WIP_CONSTANTS.EAM, wo1.last_unit_completion_date,
1537: DECODE(wo1.next_operation_seq_num,
1538: NULL, wo1.last_unit_completion_date,
1539: wo2.first_unit_start_date)) -
1540: TRUNC(DECODE( p_entity_type,
1537: DECODE(wo1.next_operation_seq_num,
1538: NULL, wo1.last_unit_completion_date,
1539: wo2.first_unit_start_date)) -
1540: TRUNC(DECODE( p_entity_type,
1541: WIP_CONSTANTS.EAM, wo1.last_unit_completion_date,
1542: DECODE(wo1.next_operation_seq_num,
1543: NULL, wo1.last_unit_completion_date,
1544: wo2.first_unit_start_date)))))) new_need_by_date
1545: FROM bom_calendar_dates bcd1,
1567: AND bcd2.calendar_code = mp.calendar_code
1568: AND bcd2.exception_set_id = mp.calendar_exception_set_id
1569: AND bcd2.calendar_date =
1570: TRUNC(DECODE( p_entity_type,
1571: WIP_CONSTANTS.EAM, wo1.last_unit_completion_date,
1572: DECODE(wo1.next_operation_seq_num,
1573: NULL, wo1.last_unit_completion_date,
1574: wo2.first_unit_start_date)))
1575: AND bcd1.calendar_code = mp.calendar_code
1668: l_req_changes PO_REQ_CHANGES_REC_TYPE;
1669:
1670: BEGIN
1671: -- write parameter value to log file
1672: IF (l_logLevel <= wip_constants.trace_logging) THEN
1673: l_params(1).paramName := 'p_po_header_id';
1674: l_params(1).paramValue := p_po_header_id;
1675: l_params(2).paramName := 'p_po_release_id';
1676: l_params(2).paramValue := p_po_release_id;
1775: raise fnd_api.g_exc_unexpected_error;
1776: END IF;
1777: END IF; -- PO or Requisition
1778: -- write to the log file
1779: IF (l_logLevel <= wip_constants.trace_logging) THEN
1780: wip_logger.exitPoint(p_procName => 'wip_osp.updatePOReqNBD',
1781: p_procReturnStatus => x_return_status,
1782: p_msg => 'procedure complete',
1783: x_returnStatus => l_returnStatus);
1785: EXCEPTION
1786: WHEN fnd_api.g_exc_unexpected_error THEN
1787: ROLLBACK TO SAVEPOINT s_update_po_nbd;
1788: x_return_status := fnd_api.g_ret_sts_unexp_error;
1789: IF (l_logLevel <= wip_constants.trace_logging) THEN
1790: wip_logger.exitPoint(p_procName => 'wip_osp.updatePOReqNBD',
1791: p_procReturnStatus => x_return_status,
1792: p_msg => l_errMsg,
1793: x_returnStatus => l_returnStatus);
1796: WHEN others THEN
1797: ROLLBACK TO SAVEPOINT s_update_po_nbd;
1798: x_return_status := fnd_api.g_ret_sts_unexp_error;
1799: l_errMsg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
1800: IF (l_logLevel <= wip_constants.trace_logging) THEN
1801: wip_logger.exitPoint(p_procName => 'wip_osp.updatePOReqNBD',
1802: p_procReturnStatus => x_return_status,
1803: p_msg => l_errMsg,
1804: x_returnStatus => l_returnStatus);
1808: fnd_msg_pub.add;
1809: END updatePOReqNBD;
1810:
1811: /* Fix for bug 4734309: Added new parameter p_is_scrap_txn.
1812: * If passed as WIP_CONSTANTS.YES,
1813: * then PO/REQ changes affect only future operations. */
1814:
1815: PROCEDURE updatePOReqQuantity(p_job_id IN NUMBER,
1816: p_repetitive_id IN NUMBER :=NULL,
1853: AND (p_repetitive_id IS NULL OR
1854: wo.repetitive_schedule_id = p_repetitive_id)
1855: AND (
1856: (((p_repetitive_id IS NULL AND
1857: wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
1858: OR
1859: (p_repetitive_id IS NOT NULL AND
1860: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
1861: AND pd.wip_operation_seq_num > p_fm_op)
1856: (((p_repetitive_id IS NULL AND
1857: wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
1858: OR
1859: (p_repetitive_id IS NOT NULL AND
1860: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
1861: AND pd.wip_operation_seq_num > p_fm_op)
1862: OR
1863: (((p_repetitive_id IS NULL AND
1864: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
1860: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
1861: AND pd.wip_operation_seq_num > p_fm_op)
1862: OR
1863: (((p_repetitive_id IS NULL AND
1864: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
1865: OR
1866: (p_repetitive_id IS NOT NULL AND
1867: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
1868: AND wo.previous_operation_seq_num IS NULL
1863: (((p_repetitive_id IS NULL AND
1864: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
1865: OR
1866: (p_repetitive_id IS NOT NULL AND
1867: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
1868: AND wo.previous_operation_seq_num IS NULL
1869: AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO)) -- bugfix 4702642
1870: )
1871: /* end bugfix 5000087 */
1865: OR
1866: (p_repetitive_id IS NOT NULL AND
1867: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
1868: AND wo.previous_operation_seq_num IS NULL
1869: AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO)) -- bugfix 4702642
1870: )
1871: /* end bugfix 5000087 */
1872: GROUP BY pd.wip_operation_seq_num,
1873: pl.item_id
1894: AND wo.operation_seq_num = prl.wip_operation_seq_num
1895: AND (p_repetitive_id IS NULL OR
1896: wo.repetitive_schedule_id = p_repetitive_id)
1897: AND ((((p_repetitive_id IS NULL AND
1898: wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
1899: OR
1900: (p_repetitive_id IS NOT NULL AND
1901: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
1902: AND prl.wip_operation_seq_num > p_fm_op)
1897: AND ((((p_repetitive_id IS NULL AND
1898: wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
1899: OR
1900: (p_repetitive_id IS NOT NULL AND
1901: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
1902: AND prl.wip_operation_seq_num > p_fm_op)
1903: OR
1904: (((p_repetitive_id IS NULL AND
1905: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
1901: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
1902: AND prl.wip_operation_seq_num > p_fm_op)
1903: OR
1904: (((p_repetitive_id IS NULL AND
1905: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
1906: OR
1907: (p_repetitive_id IS NOT NULL AND
1908: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
1909: AND wo.previous_operation_seq_num IS NULL
1904: (((p_repetitive_id IS NULL AND
1905: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
1906: OR
1907: (p_repetitive_id IS NOT NULL AND
1908: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
1909: AND wo.previous_operation_seq_num IS NULL
1910: AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO))) -- bugfix 4702642
1911: /* end bugfix 5000087 */
1912: GROUP BY prl.wip_operation_seq_num,
1906: OR
1907: (p_repetitive_id IS NOT NULL AND
1908: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
1909: AND wo.previous_operation_seq_num IS NULL
1910: AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO))) -- bugfix 4702642
1911: /* end bugfix 5000087 */
1912: GROUP BY prl.wip_operation_seq_num,
1913: prl.item_id
1914: HAVING count(*) > 1;
1932: inv_convert.inv_um_convert (msi.inventory_item_id, -- item_id
1933: NULL, -- precision
1934: DECODE(msi.outside_operation_uom_type,
1935: 'RESOURCE', ROUND(wor.usage_rate_or_amount * p_changed_qty,
1936: WIP_CONSTANTS.INV_MAX_PRECISION),
1937: 'ASSEMBLY', ROUND(p_changed_qty,
1938: WIP_CONSTANTS.INV_MAX_PRECISION)), -- from_quantity
1939: msi.primary_uom_code, -- from_unit
1940: (SELECT muom.uom_code
1934: DECODE(msi.outside_operation_uom_type,
1935: 'RESOURCE', ROUND(wor.usage_rate_or_amount * p_changed_qty,
1936: WIP_CONSTANTS.INV_MAX_PRECISION),
1937: 'ASSEMBLY', ROUND(p_changed_qty,
1938: WIP_CONSTANTS.INV_MAX_PRECISION)), -- from_quantity
1939: msi.primary_uom_code, -- from_unit
1940: (SELECT muom.uom_code
1941: FROM mtl_units_of_measure muom
1942: WHERE muom.unit_of_measure =pl.unit_meas_lookup_code),
1973: AND (p_repetitive_id IS NULL OR
1974: pd.wip_repetitive_schedule_id = p_repetitive_id)
1975: AND pd.wip_operation_seq_num > p_fm_op /*Bug 8980631*/
1976: AND (((p_repetitive_id IS NULL AND
1977: wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
1978: OR
1979: (p_repetitive_id IS NOT NULL AND
1980: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
1981: OR
1976: AND (((p_repetitive_id IS NULL AND
1977: wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
1978: OR
1979: (p_repetitive_id IS NOT NULL AND
1980: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
1981: OR
1982: (((p_repetitive_id IS NULL AND
1983: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
1984: OR
1979: (p_repetitive_id IS NOT NULL AND
1980: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
1981: OR
1982: (((p_repetitive_id IS NULL AND
1983: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
1984: OR
1985: (p_repetitive_id IS NOT NULL AND
1986: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
1987: AND wo.previous_operation_seq_num IS NULL
1982: (((p_repetitive_id IS NULL AND
1983: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
1984: OR
1985: (p_repetitive_id IS NOT NULL AND
1986: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
1987: AND wo.previous_operation_seq_num IS NULL
1988: AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO)))/* 4734309 */
1989: AND wor.organization_id = wo.organization_id
1990: AND wor.wip_entity_id = wo.wip_entity_id
1984: OR
1985: (p_repetitive_id IS NOT NULL AND
1986: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
1987: AND wo.previous_operation_seq_num IS NULL
1988: AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO)))/* 4734309 */
1989: AND wor.organization_id = wo.organization_id
1990: AND wor.wip_entity_id = wo.wip_entity_id
1991: AND wor.operation_seq_num = wo.operation_seq_num
1992: AND wor.resource_seq_num = pd.wip_resource_seq_num -- Bug 10274866 (FP of 10211569)
1994: AND wor.wip_entity_id = pd.wip_entity_id
1995: AND wor.operation_seq_num = pd.wip_operation_seq_num
1996: AND (p_repetitive_id IS NULL OR
1997: wor.repetitive_schedule_id = p_repetitive_id)
1998: AND wor.basis_type = WIP_CONSTANTS.PER_ITEM
1999: AND (pll.cancel_flag IS NULL OR pll.cancel_flag = 'N')
2000: UNION /* FP bug 10383628 */
2001: SELECT pd.po_header_id po_header_id,
2002: pr.po_release_id po_release_id,
2008: inv_convert.inv_um_convert (msi.inventory_item_id, -- item_id
2009: NULL, -- precision
2010: DECODE(msi.outside_operation_uom_type,
2011: 'RESOURCE', ROUND(wor.usage_rate_or_amount * p_changed_qty,
2012: WIP_CONSTANTS.INV_MAX_PRECISION),
2013: 'ASSEMBLY', ROUND(p_changed_qty,
2014: WIP_CONSTANTS.INV_MAX_PRECISION)), -- from_quantity
2015: msi.primary_uom_code, -- from_unit
2016: (SELECT muom.uom_code
2010: DECODE(msi.outside_operation_uom_type,
2011: 'RESOURCE', ROUND(wor.usage_rate_or_amount * p_changed_qty,
2012: WIP_CONSTANTS.INV_MAX_PRECISION),
2013: 'ASSEMBLY', ROUND(p_changed_qty,
2014: WIP_CONSTANTS.INV_MAX_PRECISION)), -- from_quantity
2015: msi.primary_uom_code, -- from_unit
2016: (SELECT muom.uom_code
2017: FROM mtl_units_of_measure muom
2018: WHERE muom.unit_of_measure =pl.unit_meas_lookup_code),
2056: AND (p_repetitive_id IS NULL OR
2057: pd.wip_repetitive_schedule_id = p_repetitive_id)
2058: AND pd.wip_operation_seq_num > p_fm_op /*bug 8980631*/
2059: AND (((p_repetitive_id IS NULL AND
2060: wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
2061: OR
2062: (p_repetitive_id IS NOT NULL AND
2063: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
2064: OR
2059: AND (((p_repetitive_id IS NULL AND
2060: wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
2061: OR
2062: (p_repetitive_id IS NOT NULL AND
2063: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
2064: OR
2065: (((p_repetitive_id IS NULL AND
2066: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
2067: OR
2062: (p_repetitive_id IS NOT NULL AND
2063: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
2064: OR
2065: (((p_repetitive_id IS NULL AND
2066: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
2067: OR
2068: (p_repetitive_id IS NOT NULL AND
2069: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
2070: AND wo.previous_operation_seq_num IS NULL
2065: (((p_repetitive_id IS NULL AND
2066: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
2067: OR
2068: (p_repetitive_id IS NOT NULL AND
2069: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
2070: AND wo.previous_operation_seq_num IS NULL
2071: AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO)))
2072: AND wor.organization_id = wo.organization_id
2073: AND wor.wip_entity_id = wo.wip_entity_id
2067: OR
2068: (p_repetitive_id IS NOT NULL AND
2069: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
2070: AND wo.previous_operation_seq_num IS NULL
2071: AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO)))
2072: AND wor.organization_id = wo.organization_id
2073: AND wor.wip_entity_id = wo.wip_entity_id
2074: AND wor.operation_seq_num = wo.operation_seq_num
2075: AND wor.organization_id = pd.destination_organization_id
2077: AND wor.operation_seq_num = pd.wip_operation_seq_num
2078: AND wor.resource_seq_num = pd.wip_resource_seq_num -- Bug 10274866 (FP of 10211569)
2079: AND (p_repetitive_id IS NULL OR
2080: wor.repetitive_schedule_id = p_repetitive_id)
2081: AND wor.basis_type = WIP_CONSTANTS.PER_ITEM
2082: AND (pll.cancel_flag IS NULL OR pll.cancel_flag = 'N')
2083: UNION /* FP bug 10383628 */
2084: SELECT to_number(null) po_header_id,
2085: to_number(null) po_release_id,
2087: prl.requisition_header_id req_header_id,
2088: prl.requisition_line_id req_line_id,
2089: (prl.quantity +(DECODE(msi.outside_operation_uom_type,
2090: 'RESOURCE', ROUND(wor.usage_rate_or_amount * p_changed_qty,
2091: WIP_CONSTANTS.INV_MAX_PRECISION),
2092: 'ASSEMBLY', ROUND(p_changed_qty,
2093: WIP_CONSTANTS.INV_MAX_PRECISION)))) new_po_qty,
2094: 'REQUISITION' po_req_type,
2095: prh.authorization_status approval_status,
2089: (prl.quantity +(DECODE(msi.outside_operation_uom_type,
2090: 'RESOURCE', ROUND(wor.usage_rate_or_amount * p_changed_qty,
2091: WIP_CONSTANTS.INV_MAX_PRECISION),
2092: 'ASSEMBLY', ROUND(p_changed_qty,
2093: WIP_CONSTANTS.INV_MAX_PRECISION)))) new_po_qty,
2094: 'REQUISITION' po_req_type,
2095: prh.authorization_status approval_status,
2096: msi.primary_uom_code uom_code,
2097: prl.org_id ou_id -- operating unit
2118: AND (p_repetitive_id IS NULL OR
2119: prl.wip_repetitive_schedule_id = p_repetitive_id)
2120: AND prl.wip_operation_seq_num > p_fm_op
2121: AND (((p_repetitive_id IS NULL AND
2122: wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
2123: OR
2124: (p_repetitive_id IS NOT NULL AND
2125: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
2126: OR
2121: AND (((p_repetitive_id IS NULL AND
2122: wdj.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE)
2123: OR
2124: (p_repetitive_id IS NOT NULL AND
2125: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
2126: OR
2127: (((p_repetitive_id IS NULL AND
2128: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
2129: OR
2124: (p_repetitive_id IS NOT NULL AND
2125: wrs.po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE))
2126: OR
2127: (((p_repetitive_id IS NULL AND
2128: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
2129: OR
2130: (p_repetitive_id IS NOT NULL AND
2131: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
2132: AND wo.previous_operation_seq_num IS NULL
2127: (((p_repetitive_id IS NULL AND
2128: wdj.po_creation_time = WIP_CONSTANTS.AT_OPERATION)
2129: OR
2130: (p_repetitive_id IS NOT NULL AND
2131: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
2132: AND wo.previous_operation_seq_num IS NULL
2133: AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO)))
2134: AND wor.organization_id = wo.organization_id
2135: AND wor.wip_entity_id = wo.wip_entity_id
2129: OR
2130: (p_repetitive_id IS NOT NULL AND
2131: wrs.po_creation_time = WIP_CONSTANTS.AT_OPERATION))
2132: AND wo.previous_operation_seq_num IS NULL
2133: AND (p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO)))
2134: AND wor.organization_id = wo.organization_id
2135: AND wor.wip_entity_id = wo.wip_entity_id
2136: AND wor.operation_seq_num = wo.operation_seq_num
2137: AND wor.organization_id = prl.destination_organization_id
2139: AND wor.operation_seq_num = prl.wip_operation_seq_num
2140: AND wor.resource_seq_num = prl.wip_resource_seq_num -- Bug 10274866 (FP of 10211569)
2141: AND (p_repetitive_id IS NULL OR
2142: wor.repetitive_schedule_id = p_repetitive_id)
2143: AND wor.basis_type = WIP_CONSTANTS.PER_ITEM
2144: AND (prl.cancel_flag IS NULL OR prl.cancel_flag = 'N')
2145: ORDER BY 1; --Order by added for bug#14782614, so that the new po_header_id can be used for comparison
2146:
2147: l_pending_recs NUMBER;
2162: l_req_changes PO_REQ_CHANGES_REC_TYPE;
2163: l_po_creation_time NUMBER;
2164: BEGIN
2165: -- write parameter value to log file
2166: IF (l_logLevel <= wip_constants.trace_logging) THEN
2167: l_params(1).paramName := 'p_job_id';
2168: l_params(1).paramValue := p_job_id;
2169: l_params(2).paramName := 'p_repetitive_id';
2170: l_params(2).paramValue := p_repetitive_id;
2197: WHERE repetitive_schedule_id = p_repetitive_id
2198: AND organization_id = p_org_id;
2199: END IF;
2200:
2201: IF(l_po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION) THEN
2202: -- Check whether the record is still in the requisition interface table
2203:
2204: /* Fix for bug 5685068: When performing scrap transactions, validate for pending
2205: requisitions only if PO Creation Time is set to At Job/Schedule Release. */
2202: -- Check whether the record is still in the requisition interface table
2203:
2204: /* Fix for bug 5685068: When performing scrap transactions, validate for pending
2205: requisitions only if PO Creation Time is set to At Job/Schedule Release. */
2206: if((p_is_scrap_txn = WIP_CONSTANTS.YES AND
2207: l_po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE) OR
2208: p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO) THEN
2209:
2210: SELECT count(*)
2203:
2204: /* Fix for bug 5685068: When performing scrap transactions, validate for pending
2205: requisitions only if PO Creation Time is set to At Job/Schedule Release. */
2206: if((p_is_scrap_txn = WIP_CONSTANTS.YES AND
2207: l_po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE) OR
2208: p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO) THEN
2209:
2210: SELECT count(*)
2211: INTO l_pending_recs
2204: /* Fix for bug 5685068: When performing scrap transactions, validate for pending
2205: requisitions only if PO Creation Time is set to At Job/Schedule Release. */
2206: if((p_is_scrap_txn = WIP_CONSTANTS.YES AND
2207: l_po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE) OR
2208: p_is_scrap_txn IS NULL OR p_is_scrap_txn = WIP_CONSTANTS.NO) THEN
2209:
2210: SELECT count(*)
2211: INTO l_pending_recs
2212: FROM po_requisitions_interface_all
2220: l_errMsg := 'There are some pending records in ' ||
2221: 'PO_REQUISITIONS_INTERFACE_ALL';
2222: raise fnd_api.g_exc_unexpected_error;
2223: END IF;
2224: end if; /* if((p_is_scrap_txn = WIP_CONSTANTS.YES AND */
2225:
2226: OPEN c_multiple_po;
2227: FETCH c_multiple_po INTO l_multiple_po;
2228:
2255: end if;
2256: l_po_changes.distribution_changes.add_change(
2257: p_po_distribution_id => l_update_po_qty.po_distribution_id,
2258: p_quantity_ordered => l_update_po_qty.new_po_qty);
2259: IF (l_logLevel <= wip_constants.full_logging) THEN
2260: l_debugMsg := 'po_header_id = ' || l_update_po_qty.po_header_id
2261: || ' ; ' || 'po_release_id = ' ||
2262: l_update_po_qty.po_release_id || ' ; ' ||
2263: 'po_distribution_id = ' ||
2312: assignment_start_date => PO_TBL_DATE(NULL),
2313: assignment_end_date => PO_TBL_DATE(NULL),
2314: amount => PO_TBL_NUMBER(NULL));
2315:
2316: IF (l_logLevel <= wip_constants.full_logging) THEN
2317: l_debugMsg := 'req_header_id = ' || l_update_po_qty.req_header_id
2318: || ' ; ' || 'req_line_id = ' ||
2319: l_update_po_qty.req_line_id || ' ; ' ||
2320: 'new_po_qty = ' || l_update_po_qty.new_po_qty;
2366:
2367: /* End of Section - Added by tekang for FP bug 10383623 */
2368:
2369: END IF;-- Multiple PO found for the same job,same item,and same op.
2370: END IF; -- po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION
2371:
2372: IF(c_multiple_po%ISOPEN) THEN
2373: CLOSE c_multiple_po;
2374: END IF;
2373: CLOSE c_multiple_po;
2374: END IF;
2375:
2376: -- write to the log file
2377: IF (l_logLevel <= wip_constants.trace_logging) THEN
2378: wip_logger.exitPoint(p_procName => 'wip_osp.updatePOReqQuantity',
2379: p_procReturnStatus => x_return_status,
2380: p_msg => 'procedure complete',
2381: x_returnStatus => l_returnStatus);
2386: IF(c_multiple_po%ISOPEN) THEN
2387: CLOSE c_multiple_po;
2388: END IF;
2389: x_return_status := fnd_api.g_ret_sts_unexp_error;
2390: IF (l_logLevel <= wip_constants.trace_logging) THEN
2391: wip_logger.exitPoint(p_procName => 'wip_osp.updatePOReqQuantity',
2392: p_procReturnStatus => x_return_status,
2393: p_msg => l_errMsg,
2394: x_returnStatus => l_returnStatus);
2400: CLOSE c_multiple_po;
2401: END IF;
2402: x_return_status := fnd_api.g_ret_sts_unexp_error;
2403: l_errMsg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
2404: IF (l_logLevel <= wip_constants.trace_logging) THEN
2405: wip_logger.exitPoint(p_procName => 'wip_osp.updatePOReqQuantity',
2406: p_procReturnStatus => x_return_status,
2407: p_msg => l_errMsg,
2408: x_returnStatus => l_returnStatus);
2524: l_msgCount NUMBER;
2525: l_msgData VARCHAR2(2000);
2526: BEGIN
2527: -- write parameter value to log file
2528: IF (l_logLevel <= wip_constants.trace_logging) THEN
2529: l_params(1).paramName := 'p_job_id';
2530: l_params(1).paramValue := p_job_id;
2531: l_params(2).paramName := 'p_repetitive_id';
2532: l_params(2).paramValue := p_repetitive_id;
2547: IF (l_po_req.po_req_type IN ('STANDARD', 'BLANKET'))THEN
2548: -- Call PO API to cancel PO/release. If unable to cancel PO/release
2549: -- for any reason,skip the error one and try to cancel the next one.
2550:
2551: IF (l_logLevel <= wip_constants.full_logging) THEN
2552: l_debugMsg := 'po_header_id = ' || l_po_req.po_header_id|| ' ; ' ||
2553: 'po_release_id = ' || l_po_req.po_release_id || ' ; '
2554: ||
2555: 'po_line_id = ' || l_po_req.po_line_id || ' ; ' ||
2594: fnd_msg_pub.add;
2595: raise fnd_api.g_exc_unexpected_error;
2596: END IF;
2597: ELSE
2598: IF (l_logLevel <= wip_constants.full_logging) THEN
2599: l_debugMsg := 'req_header_id = ' || l_po_req.req_header_id|| ' ; '
2600: || 'req_line_id = ' || l_po_req.req_line_id;
2601:
2602: wip_logger.log(p_msg => l_debugMsg,
2644: x_return_status := fnd_api.g_ret_sts_success;
2645: END IF;
2646: END IF;
2647: -- write to the log file
2648: IF (l_logLevel <= wip_constants.trace_logging) THEN
2649: wip_logger.exitPoint(p_procName => 'wip_osp.cancelPOReq',
2650: p_procReturnStatus => x_return_status,
2651: p_msg => 'procedure complete',
2652: x_returnStatus => l_returnStatus);
2654: EXCEPTION
2655: WHEN others THEN
2656: x_return_status := fnd_api.g_ret_sts_unexp_error;
2657: l_errMsg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
2658: IF (l_logLevel <= wip_constants.trace_logging) THEN
2659: wip_logger.exitPoint(p_procName => 'wip_osp.cancelPOReq',
2660: p_procReturnStatus => x_return_status,
2661: p_msg => l_errMsg,
2662: x_returnStatus => l_returnStatus);
2745:
2746: po_req_exist VARCHAR2(20);
2747:
2748: BEGIN
2749: IF(p_entity_type = WIP_CONSTANTS.REPETITIVE) THEN
2750: OPEN rep_check_po_req_cur;
2751: FETCH rep_check_po_req_cur INTO po_req_exist;
2752:
2753: IF(rep_check_po_req_cur%NOTFOUND) THEN