[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;
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
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,
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
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
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: )
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
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,
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;
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;
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: */
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
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;
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
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;
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
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
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;
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
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;
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: )
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
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,
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;
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;
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: */
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
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;
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
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;
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;
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;
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
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;
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
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
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,
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,
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),
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,
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
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)) -
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,
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
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',
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
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
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
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
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
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,
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 */
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)))
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
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
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',
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,
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),
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,
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
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)) -
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,
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
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',
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
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
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
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
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,
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 */
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)))
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
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
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
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
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
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
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
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
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
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
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
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)
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
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
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
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
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
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
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'
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)
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
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
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
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;
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:
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,
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,
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,
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,
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
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;
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);
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);
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);
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,
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)
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)
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
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 */
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
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)
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)
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
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,
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;
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,
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
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)
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)
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
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
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,
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,
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
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)
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)
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
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
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,
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,
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
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)
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)
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
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
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;
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;
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. */
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(*)
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
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
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:
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 = ' ||
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;
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;
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);
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);
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);
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;
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 || ' ; ' ||
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,
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);
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);
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