[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 09/29/00 GRATNAM Modified update_job_name()for |
| assy returns |
| 01/15/01 SBHASKAR Bugfix 1523334:Incase of Jump, insert |
| into wip_operation_resources |
| using bom_std_op_resources |
+============================================================================*/
g_update_flag boolean:=TRUE;
delete_operation (
X_Wip_Entity_id,
X_Organization_id,
-- X_From_Op,
X_To_Op,
X_Error_Code,
X_Error_Msg);
SELECT --nvl(last_operation_seq_num, 9999), -- DEL: CZH.I_9999
nvl(op_seq_num_increment, 10)
INTO --l_last_op_seq, -- DEL: CZH.I_9999
l_op_seq_incr
FROM wsm_parameters
WHERE organization_id = X_Organization_Id;
SELECT unique max(operation_seq_num)
INTO l_max_op_seq
FROM wip_operations
WHERE WIP_ENTITY_ID = x_wip_entity_id
AND operation_seq_num NOT IN
( SELECT nvl(last_operation_seq_num, 9999)
FROM wsm_parameters
WHERE organization_id = x_organization_id ) ;
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: Calling delete_operation..');
delete_operation (X_Wip_Entity_id,
X_Organization_id,
x_to_op, -- l_max_op_seq + l_op_seq_incr, --X_To_Op,-- Fix bug #1496147
X_Error_Code,
X_Error_Msg);
SELECT NVL(ROUTING_REVISION_DATE, SYSDATE), start_quantity
INTO p_rtg_rev_date, l_start_quantity
FROM WIP_DISCRETE_JOBS wdj
WHERE wdj.ORGANIZATION_ID = X_Organization_Id
AND wdj.WIP_ENTITY_ID = X_Wip_Entity_Id;
SELECT count(*)
INTO l_count
FROM BOM_OPERATION_SEQUENCES bos,
WIP_DISCRETE_JOBS wdj
WHERE wdj.WIP_ENTITY_ID = X_Wip_Entity_Id
AND wdj.ORGANIZATION_ID = X_Organization_Id
-- AND bos.standard_operation_id = X_Standard_Operation_Id
AND bos.operation_sequence_id = X_Op_Seq_Id
AND bos.routing_sequence_id = wdj.common_routing_sequence_id
--BC: CZH.I_OED-1, should honor ROUTING_REVISION_DATE
--bug1725145
--validate disabled operation
--AND sysdate <= nvl(bos.disable_date, sysdate+1)
--AND bos.effectivity_date <= sysdate;
SELECT count(*)
INTO l_count
FROM WSM_COPY_OPERATIONS WCO
WHERE WCO.WIP_ENTITY_ID = X_Wip_Entity_Id
AND wco.operation_sequence_id = x_op_seq_id;
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: NOT a Jump operation. Inserting into WO..');
INSERT INTO WIP_OPERATIONS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OPERATION_SEQUENCE_ID,
STANDARD_OPERATION_ID,
DEPARTMENT_ID,
DESCRIPTION,
SCHEDULED_QUANTITY,
QUANTITY_IN_QUEUE,
QUANTITY_RUNNING,
QUANTITY_WAITING_TO_MOVE,
QUANTITY_REJECTED,
QUANTITY_SCRAPPED,
QUANTITY_COMPLETED,
FIRST_UNIT_START_DATE,
FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_START_DATE,
LAST_UNIT_COMPLETION_DATE,
PREVIOUS_OPERATION_SEQ_NUM,
NEXT_OPERATION_SEQ_NUM,
COUNT_POINT_TYPE,
BACKFLUSH_FLAG,
MINIMUM_TRANSFER_QUANTITY,
DATE_LAST_MOVED,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OPERATION_YIELD,
OPERATION_YIELD_ENABLED,
CUMULATIVE_SCRAP_QUANTITY,
WSM_COSTED_QUANTITY_COMPLETED,
LOWEST_ACCEPTABLE_YIELD) --mes
SELECT X_Wip_Entity_Id,
-- X_From_Op + 10,
-- X_From_Op + l_op_seq_incr,
x_to_op, -- l_max_op_seq + l_op_seq_incr, -- Fix bug #1496147
X_Organization_Id,
p_curdate,
p_user,
p_curdate,
p_user,
p_login,
DECODE(p_req_id, 0, '', p_req_id),
DECODE(p_appl_id, 0, '', p_appl_id),
DECODE(p_commit, 1, p_prog_id, -999),
DECODE(p_prog_id, 0, '', p_curdate),
SEQ.OPERATION_SEQUENCE_ID,
SEQ.STANDARD_OPERATION_ID,
SEQ.DEPARTMENT_ID,
SEQ.OPERATION_DESCRIPTION,
ROUND(DJ.Start_Quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
0, 0, 0, 0, 0, 0,
DJ.SCHEDULED_START_DATE,
DJ.SCHEDULED_COMPLETION_DATE,
DJ.SCHEDULED_START_DATE,
DJ.SCHEDULED_COMPLETION_DATE,
-- Bug 4614970 0, 0,
-- x_to_op - l_op_seq_incr,0, -- Bug 4614970 The previous line is commented and replaced by this line
x_to_op - l_op_seq_incr,null, -- Bug 5336643 Populated null instead of zero
SEQ.COUNT_POINT_TYPE,
SEQ.BACKFLUSH_FLAG,
NVL(SEQ.MINIMUM_TRANSFER_QUANTITY, 0),
'',
SEQ.ATTRIBUTE_CATEGORY,
SEQ.ATTRIBUTE1,
SEQ.ATTRIBUTE2,
SEQ.ATTRIBUTE3,
SEQ.ATTRIBUTE4,
SEQ.ATTRIBUTE5,
SEQ.ATTRIBUTE6,
SEQ.ATTRIBUTE7,
SEQ.ATTRIBUTE8,
SEQ.ATTRIBUTE9,
SEQ.ATTRIBUTE10,
SEQ.ATTRIBUTE11,
SEQ.ATTRIBUTE12,
SEQ.ATTRIBUTE13,
SEQ.ATTRIBUTE14,
SEQ.ATTRIBUTE15,
SEQ.YIELD,
to_char(SEQ.OPERATION_YIELD_ENABLED),
DJ.QUANTITY_SCRAPPED,
0,
SEQ.LOWEST_ACCEPTABLE_YIELD
FROM BOM_OPERATIONAL_ROUTINGS R,
BOM_OPERATION_SEQUENCES SEQ,
WIP_DISCRETE_JOBS DJ
WHERE SEQ.ROUTING_SEQUENCE_ID =
nvl(r.common_routing_sequence_id, r.routing_sequence_id)
-- BC: CZH.I_OED-1, should honor routing revision date
--AND p_curdate >= SEQ.effectivity_date
--AND p_curdate <= NVL(SEQ.DISABLE_DATE,p_curdate+2)
AND p_rtg_rev_date >= SEQ.effectivity_date
AND p_rtg_rev_date <= NVL(SEQ.DISABLE_DATE, p_rtg_rev_date+2)
-- EC: CZH.I_OED-1
AND R.ASSEMBLY_ITEM_ID =
DECODE( DJ.JOB_TYPE, 1,
DJ.PRIMARY_ITEM_ID, DJ.ROUTING_REFERENCE_ID)
AND DJ.WIP_ENTITY_ID = X_Wip_Entity_Id
AND DJ.ORGANIZATION_ID = X_Organization_Id
AND SEQ.operation_sequence_id = X_Op_Seq_Id
--NSO Modification by abedajna begin
--AND SEQ.Standard_operation_id = X_Standard_Operation_Id
--NSO Modification by abedajna end
AND SEQ.routing_sequence_id = dj.common_routing_sequence_id;
select operation_sequence_id
into l_op_seq_id
from wip_operations
where wip_entity_id = x_wip_entity_id
and operation_seq_num = x_to_op
and organization_id = x_organization_id;
X_LAST_UPDATE_LOGIN => p_login,
X_PROGRAM_APPLICATION_ID => p_appl_id,
X_PROGRAM_ID => p_prog_id,
X_REQUEST_ID => p_req_id);
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: Inserted '
||sql%rowcount||' records in WO.');
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: JUMP operation. Inserting into WO..');
select nvl(operation_sequence_id, previous_operation_seq_id)
into l_fm_op_seq_id
from wip_operations
where wip_entity_id = x_wip_entity_id
and operation_seq_num = x_from_op
and organization_id = x_organization_id;
INSERT INTO WIP_OPERATIONS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OPERATION_SEQUENCE_ID,
STANDARD_OPERATION_ID,
DEPARTMENT_ID,
DESCRIPTION,
SCHEDULED_QUANTITY,
QUANTITY_IN_QUEUE,
QUANTITY_RUNNING,
QUANTITY_WAITING_TO_MOVE,
QUANTITY_REJECTED,
QUANTITY_SCRAPPED,
QUANTITY_COMPLETED,
FIRST_UNIT_START_DATE,
FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_START_DATE,
LAST_UNIT_COMPLETION_DATE,
PREVIOUS_OPERATION_SEQ_NUM,
NEXT_OPERATION_SEQ_NUM,
COUNT_POINT_TYPE,
BACKFLUSH_FLAG,
MINIMUM_TRANSFER_QUANTITY,
DATE_LAST_MOVED,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OPERATION_YIELD,
OPERATION_YIELD_ENABLED, -- BC: BUG2256872
PREVIOUS_OPERATION_SEQ_ID,
CUMULATIVE_SCRAP_QUANTITY, -- EC: BUG2256872
WSM_COSTED_QUANTITY_COMPLETED,
LOWEST_ACCEPTABLE_YIELD) --mes
SELECT X_Wip_Entity_Id,
-- X_From_Op + 10,
-- X_From_Op + l_op_seq_incr,
x_to_op, -- l_max_op_seq + l_op_seq_incr, -- Fix bug #1496147
X_Organization_Id,
p_curdate,
p_user,
p_curdate,
p_user,
p_login,
DECODE(p_req_id, 0, '', p_req_id),
DECODE(p_appl_id, 0, '', p_appl_id),
DECODE(p_commit, 1, p_prog_id, -999),
DECODE(p_prog_id, 0, '', p_curdate),
NULL, --SEQ.OPERATION_SEQUENCE_ID,
bso.STANDARD_OPERATION_ID,
bso.DEPARTMENT_ID,
bso.OPERATION_DESCRIPTION,
ROUND(DJ.Start_Quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
0, 0, 0, 0, 0, 0,
DJ.SCHEDULED_START_DATE,
DJ.SCHEDULED_COMPLETION_DATE,
DJ.SCHEDULED_START_DATE,
DJ.SCHEDULED_COMPLETION_DATE,
-- Bug 4614970 0, 0,
-- x_to_op - l_op_seq_incr,0, -- Bug 4614970 The previous line is commented and replaced by this line
x_to_op - l_op_seq_incr,null, -- Bug 5336643 Populated null instead of zero
bso.COUNT_POINT_TYPE,
bso.BACKFLUSH_FLAG,
NVL(bso.MINIMUM_TRANSFER_QUANTITY, 0),
'',
bso.ATTRIBUTE_CATEGORY,
bso.ATTRIBUTE1,
bso.ATTRIBUTE2,
bso.ATTRIBUTE3,
bso.ATTRIBUTE4,
bso.ATTRIBUTE5,
bso.ATTRIBUTE6,
bso.ATTRIBUTE7,
bso.ATTRIBUTE8,
bso.ATTRIBUTE9,
bso.ATTRIBUTE10,
bso.ATTRIBUTE11,
bso.ATTRIBUTE12,
bso.ATTRIBUTE13,
bso.ATTRIBUTE14,
bso.ATTRIBUTE15,
bso.YIELD,
to_char(bso.OPERATION_YIELD_ENABLED), --BC: BUG2256872
l_fm_op_seq_id, --EC: BUG2256872
DJ.QUANTITY_SCRAPPED,
0,
BSO.LOWEST_ACCEPTABLE_YIELD
FROM BOM_STANDARD_OPERATIONS bso,
WIP_DISCRETE_JOBS DJ
WHERE DJ.WIP_ENTITY_ID = X_Wip_Entity_Id
AND DJ.ORGANIZATION_ID = X_Organization_Id
AND bso.Standard_operation_id = X_Standard_Operation_Id;
X_LAST_UPDATE_LOGIN => p_login,
X_PROGRAM_APPLICATION_ID => p_appl_id,
X_PROGRAM_ID => p_prog_id,
X_REQUEST_ID => p_req_id);
'WSMPOPRN.add_operation: Inserted '||sql%rowcount||' records in WO.');
UPDATE WSM_LOT_BASED_JOBS
SET current_job_op_seq_num = x_to_op,
current_rtg_op_seq_num = null
WHERE WIP_ENTITY_ID = x_wip_entity_id;
X_LAST_UPDATE_LOGIN => p_login,
X_PROGRAM_APPLICATION_ID => p_appl_id,
X_PROGRAM_ID => p_prog_id,
X_REQUEST_ID => p_req_id);
'WSMPOPRN.add_operation: Inserted '||sql%rowcount||' records in WO.');
SELECT BD.DEPARTMENT_ID, BD.SCRAP_ACCOUNT, BD.EST_ABSORPTION_ACCOUNT
into l_dept_id, l_scrap_account, l_est_absorption_account
FROM WIP_OPERATIONS WO,
BOM_DEPARTMENTS BD
WHERE WO.WIP_ENTITY_ID = x_wip_entity_id
AND WO.OPERATION_SEQ_NUM = x_to_op
AND WO.ORGANIZATION_ID = x_organization_id
AND WO.DEPARTMENT_ID = BD.DEPARTMENT_ID;
select job_type
into l_job_type
from wip_discrete_jobs
where wip_entity_id = x_wip_entity_id;
SELECT nvl(WCO.recommended, 'N'), WCO.RECO_START_DATE, WCO.reco_completion_date,
WCO.DEPARTMENT_ID, WCO.SCRAP_ACCOUNT, WCO.EST_ABSORPTION_ACCOUNT
INTO l_recommended_op, l_reco_start_date, l_reco_completion_date,
l_dept_id, l_scrap_account, l_est_absorption_account
FROM WSM_COPY_OPERATIONS WCO
WHERE wip_entity_id = X_Wip_Entity_Id
AND operation_sequence_id = x_op_seq_id;
SELECT WCO.DEPARTMENT_ID, WCO.SCRAP_ACCOUNT, WCO.EST_ABSORPTION_ACCOUNT
into l_dept_id, l_scrap_account, l_est_absorption_account
FROM WSM_COPY_OPERATIONS WCO
WHERE WCO.WIP_ENTITY_ID = X_Wip_Entity_Id
AND WCO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
AND WCO.ORGANIZATION_ID = X_Organization_Id;
select job_type
into l_job_type
from wip_discrete_jobs
where wip_entity_id = X_Wip_Entity_Id;
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: Inserted '
||sql%rowcount||' records in WO.');
UPDATE WSM_LOT_BASED_JOBS wlbj
SET wlbj.on_rec_path = l_recommended_op
WHERE wlbj.wip_entity_id = X_Wip_Entity_Id
AND wlbj.organization_id = X_Organization_Id
AND wlbj.on_rec_path <> l_recommended_op;
SELECT nvl(max(resource_seq_num), 10)
INTO l_wsor_max_res_seq_num
FROM WIP_SUB_OPERATION_RESOURCES WSOR
WHERE wip_entity_id = x_wip_entity_id
AND OPERATION_SEQ_NUM = x_to_op;
INSERT INTO WIP_SUB_OPERATION_RESOURCES
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
resource_id,
uom_code,
basis_type,
usage_rate_or_amount,
activity_id,
scheduled_flag,
assigned_units,
maximum_assigned_units, /* ST : Detailed Scheduling */
autocharge_type,
standard_rate_flag,
applied_resource_units,
applied_resource_value,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
completion_date,
start_date,
schedule_seq_num,
substitute_group_num,
replacement_group_num,
setup_id)
SELECT WO.wip_entity_id,
x_to_op,
--bug 3311695 changed the select below to a private function to be compatible with db 8.1.7.4
/* rownum + (SELECT nvl(max(resource_seq_num), 10)
FROM WIP_SUB_OPERATION_RESOURCES WSOR
WHERE wip_entity_id = x_wip_entity_id
AND OPERATION_SEQ_NUM = x_to_op),--.resource_seq_num
*/
(rownum + l_wsor_max_res_seq_num),
WO.organization_id,
null,
SYSDATE ,
p_user,
SYSDATE,
p_user,
p_login,
BSSOR.resource_id,
BR.unit_of_measure,
BSSOR.basis_type,
BSSOR.usage_rate_or_amount,
BSSOR.activity_id,
BSSOR.schedule_flag,
BSSOR.assigned_units,
BSSOR.assigned_units, /* ST : Detailed Scheduling */
BSSOR.autocharge_type,
BSSOR.standard_rate_flag,
0, --WCOR.applied_resource_units,
0, -- WCOR.applied_resource_value,
BSSOR.attribute_category,
BSSOR.attribute1,
BSSOR.attribute2,
BSSOR.attribute3,
BSSOR.attribute4,
BSSOR.attribute5,
BSSOR.attribute6,
BSSOR.attribute7,
BSSOR.attribute8,
BSSOR.attribute9,
BSSOR.attribute10,
BSSOR.attribute11,
BSSOR.attribute12,
BSSOR.attribute13,
BSSOR.attribute14,
BSSOR.attribute15,
p_txn_date,
p_txn_date,
BSSOR.schedule_seq_num , --NULL, --schedule_seq_num, / -- Bug 7371846
BSSOR.substitute_group_num,
BSSOR.replacement_group_num, --replacement_group_num,
NULL --setup_id
FROM BOM_RESOURCES BR,
BOM_STD_SUB_OP_RESOURCES BSSOR,
WIP_OPERATIONS wo
WHERE WO.WIP_ENTITY_ID = x_wip_entity_id
AND WO.OPERATION_SEQ_NUM = x_to_op
AND BSSOR.standard_operation_id = WO.standard_operation_id
AND BSSOR.RESOURCE_ID = BR.RESOURCE_ID;
SELECT last_unit_completion_date
INTO l_infi_start_date
FROM WIP_OPERATIONS
WHERE wip_entity_id = X_Wip_Entity_Id
AND organization_id = X_Organization_Id
AND operation_seq_num = X_From_Op;
UPDATE wip_operations
SET count_point_type = 3,
scheduled_quantity = 0, -- Added to fix bug #2686501
--bug 3595728 change from sysdate to p_txn_date-1 second
-- disable_date = sysdate -- Bug 2931071
disable_date = p_txn_date - 1/(24*60*60)
--end bug 3595728
WHERE wip_entity_id = x_wip_entity_id
AND operation_seq_num > x_from_op;
UPDATE wip_operation_resources
SET autocharge_type = 2
WHERE wip_entity_id = x_wip_entity_id
AND operation_seq_num > x_from_op;
UPDATE wip_requirement_operations
SET required_quantity = 0
--quantity_per_assembly = 0 abb, bug 2931071
WHERE wip_entity_id = x_wip_entity_id
AND operation_seq_num > x_from_op;
PROCEDURE Delete_Operation(
X_Wip_Entity_Id IN NUMBER,
X_Organization_id IN NUMBER,
--X_From_Op IN NUMBER,
X_To_Op IN NUMBER,
x_error_code OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2
) IS
l_stat_num NUMBER;
DELETE FROM WIP_OPERATIONS
WHERE WIP_ENTITY_ID = X_Wip_Entity_id
--AND OPERATION_SEQ_NUM > X_From_Op
AND OPERATION_SEQ_NUM = X_To_Op
AND PROGRAM_ID = -999
AND ORGANIZATION_ID = X_Organization_Id;
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
X_entity_name => 'WSM_LOT_BASED_OPERATIONS',
X_pk1_value => to_char(x_wip_entity_id),
X_pk2_value => to_char(x_to_op),
x_pk3_value => to_char(x_organization_id),
X_delete_document_flag => 'Y');
DELETE FROM WIP_OPERATION_RESOURCES
WHERE WIP_ENTITY_ID = X_Wip_Entity_id
--AND OPERATION_SEQ_NUM > X_From_Op
AND OPERATION_SEQ_NUM = X_To_Op
AND PROGRAM_ID = -999
AND ORGANIZATION_ID = X_Organization_Id;
DELETE FROM WIP_OPERATION_YIELDS
WHERE WIP_ENTITY_ID = X_Wip_Entity_id
--AND OPERATION_SEQ_NUM > X_From_Op
AND OPERATION_SEQ_NUM = X_To_Op
AND PROGRAM_ID = -999
AND ORGANIZATION_ID = X_Organization_Id;
DELETE FROM WIP_REQUIREMENT_OPERATIONS
WHERE WIP_ENTITY_ID = X_Wip_Entity_id
--AND OPERATION_SEQ_NUM > X_From_Op
AND OPERATION_SEQ_NUM = X_To_Op
AND ORGANIZATION_ID = X_Organization_Id;
x_error_msg := 'WSMOPRNB.delete_operation('||l_stat_num||')'|| substr(SQLERRM,1,200);
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.delete_operation: other excpn: '|| x_error_msg);
END delete_operation;
INSERT INTO WIP_OPERATION_RESOURCES
(WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
RESOURCE_ID, UOM_CODE,
BASIS_TYPE, USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
SCHEDULED_FLAG, ASSIGNED_UNITS, AUTOCHARGE_TYPE,
STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
START_DATE, COMPLETION_DATE,
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
ATTRIBUTE15,
SCHEDULE_SEQ_NUM, --bugfix 2493065
SUBSTITUTE_GROUP_NUM,
PRINCIPLE_FLAG,
SETUP_ID)
SELECT OPS.WIP_ENTITY_ID, OPS.OPERATION_SEQ_NUM, ORS.RESOURCE_SEQ_NUM,
OPS.ORGANIZATION_ID, OPS.REPETITIVE_SCHEDULE_ID,
OPS.LAST_UPDATE_DATE, OPS.LAST_UPDATED_BY, OPS.CREATION_DATE,
OPS.CREATED_BY, OPS.LAST_UPDATE_LOGIN, OPS.REQUEST_ID,
OPS.PROGRAM_APPLICATION_ID, OPS.PROGRAM_ID,
OPS.PROGRAM_UPDATE_DATE, ORS.RESOURCE_ID, RSC.UNIT_OF_MEASURE,
ORS.BASIS_TYPE, ORS.USAGE_RATE_OR_AMOUNT, ORS.ACTIVITY_ID,
ORS.SCHEDULE_FLAG, ORS.ASSIGNED_UNITS, ORS.AUTOCHARGE_TYPE,
ORS.STANDARD_RATE_FLAG, 0, 0,
OPS.FIRST_UNIT_START_DATE, OPS.LAST_UNIT_COMPLETION_DATE,
ORS.ATTRIBUTE_CATEGORY, ORS.ATTRIBUTE1, ORS.ATTRIBUTE2,
ORS.ATTRIBUTE3, ORS.ATTRIBUTE4, ORS.ATTRIBUTE5,
ORS.ATTRIBUTE6, ORS.ATTRIBUTE7, ORS.ATTRIBUTE8,
ORS.ATTRIBUTE9, ORS.ATTRIBUTE10, ORS.ATTRIBUTE11,
ORS.ATTRIBUTE12, ORS.ATTRIBUTE13, ORS.ATTRIBUTE14,
ORS.ATTRIBUTE15,
ORS.SCHEDULE_SEQ_NUM, --bugfix 2493065
ORS.SUBSTITUTE_GROUP_NUM,
ORS.PRINCIPLE_FLAG,
ORS.SETUP_ID
FROM BOM_RESOURCES RSC,
BOM_OPERATION_RESOURCES ORS,
WIP_OPERATIONS OPS
WHERE OPS.ORGANIZATION_ID = X_Organization_Id
AND OPS.WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPS.OPERATION_SEQ_NUM = X_op_seq_num
AND OPS.OPERATION_SEQUENCE_ID = ORS.OPERATION_SEQUENCE_ID
AND ORS.RESOURCE_ID = RSC.RESOURCE_ID
AND RSC.ORGANIZATION_ID = OPS.ORGANIZATION_ID;
INSERT INTO WIP_OPERATION_RESOURCES
(WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
RESOURCE_ID, UOM_CODE,
BASIS_TYPE, USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
SCHEDULED_FLAG, ASSIGNED_UNITS, AUTOCHARGE_TYPE,
STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
START_DATE, COMPLETION_DATE,
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
ATTRIBUTE15,
SCHEDULE_SEQ_NUM, --Added : -- bug 7371846
SUBSTITUTE_GROUP_NUM, --Added : -- bug 7371846
REPLACEMENT_GROUP_NUM ) --Added : -- bug 7371846
SELECT OPS.WIP_ENTITY_ID, OPS.OPERATION_SEQ_NUM, ORS.RESOURCE_SEQ_NUM,
OPS.ORGANIZATION_ID, OPS.REPETITIVE_SCHEDULE_ID,
OPS.LAST_UPDATE_DATE, OPS.LAST_UPDATED_BY, OPS.CREATION_DATE,
OPS.CREATED_BY, OPS.LAST_UPDATE_LOGIN, OPS.REQUEST_ID,
OPS.PROGRAM_APPLICATION_ID, OPS.PROGRAM_ID,
OPS.PROGRAM_UPDATE_DATE, ORS.RESOURCE_ID, RSC.UNIT_OF_MEASURE,
ORS.BASIS_TYPE, ORS.USAGE_RATE_OR_AMOUNT, ORS.ACTIVITY_ID,
ORS.SCHEDULE_FLAG, ORS.ASSIGNED_UNITS, ORS.AUTOCHARGE_TYPE,
ORS.STANDARD_RATE_FLAG, 0, 0,
OPS.FIRST_UNIT_START_DATE, OPS.LAST_UNIT_COMPLETION_DATE,
ORS.ATTRIBUTE_CATEGORY, ORS.ATTRIBUTE1, ORS.ATTRIBUTE2,
ORS.ATTRIBUTE3, ORS.ATTRIBUTE4, ORS.ATTRIBUTE5,
ORS.ATTRIBUTE6, ORS.ATTRIBUTE7, ORS.ATTRIBUTE8,
ORS.ATTRIBUTE9, ORS.ATTRIBUTE10, ORS.ATTRIBUTE11,
ORS.ATTRIBUTE12, ORS.ATTRIBUTE13, ORS.ATTRIBUTE14,
ORS.ATTRIBUTE15,
ORS.RESOURCE_SEQ_NUM, --Added : -- bug 7371846
ORS.SUBSTITUTE_GROUP_NUM, --Added : -- bug 7371846
0 --Added : -- make it as zero on resources level -- bug 7371846
FROM BOM_RESOURCES RSC,
BOM_STD_OP_RESOURCES ORS,
WIP_OPERATIONS OPS
WHERE OPS.ORGANIZATION_ID = X_Organization_Id
AND OPS.WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPS.OPERATION_SEQ_NUM = X_op_seq_num
AND OPS.STANDARD_OPERATION_ID = ORS.STANDARD_OPERATION_ID
AND ORS.RESOURCE_ID = RSC.RESOURCE_ID
AND RSC.ORGANIZATION_ID = OPS.ORGANIZATION_ID;
INSERT INTO WIP_OPERATION_YIELDS
(WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID,PROGRAM_UPDATE_DATE,
STATUS, SCRAP_ACCOUNT, EST_SCRAP_ABSORB_ACCOUNT)
SELECT WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, WO.ORGANIZATION_ID,
WO.LAST_UPDATE_DATE, WO.LAST_UPDATED_BY, WO.CREATION_DATE,
WO.CREATED_BY, WO.LAST_UPDATE_LOGIN, WO.REQUEST_ID,
WO.PROGRAM_APPLICATION_ID, WO.PROGRAM_ID, WO.PROGRAM_UPDATE_DATE,
NULL, BD.SCRAP_ACCOUNT, BD.EST_ABSORPTION_ACCOUNT
FROM WIP_OPERATIONS WO,
-- BOM_OPERATION_SEQUENCES BOS, fix bug 1611094
BOM_DEPARTMENTS BD
WHERE WO.WIP_ENTITY_ID = X_Wip_Entity_Id
AND WO.OPERATION_SEQ_NUM = X_op_seq_num
AND WO.ORGANIZATION_ID = X_Organization_Id
-- AND WO.OPERATION_SEQUENCE_ID = BOS.OPERATION_SEQUENCE_ID --bugfix 1611094
AND WO.DEPARTMENT_ID = BD.DEPARTMENT_ID; --bugfix 1611094
UPDATE WIP_OPERATIONS WO
SET WO.PREVIOUS_OPERATION_SEQ_NUM =
(SELECT MAX(OPERATION_SEQ_NUM)
FROM WIP_OPERATIONS
WHERE WIP_ENTITY_ID = X_Wip_Entity_Id
-- bugfix 2644217: AND ORGANIZATION_ID = X_Organization_Id
AND OPERATION_SEQ_NUM < WO.OPERATION_SEQ_NUM),
WO.NEXT_OPERATION_SEQ_NUM =
(SELECT MIN(OPERATION_SEQ_NUM)
FROM WIP_OPERATIONS
WHERE WIP_ENTITY_ID = X_Wip_Entity_Id
-- bugfix 2644217: AND ORGANIZATION_ID = X_Organization_Id
AND OPERATION_SEQ_NUM > WO.OPERATION_SEQ_NUM)
WHERE WO.WIP_ENTITY_ID = X_Wip_Entity_Id
AND WO.ORGANIZATION_ID = X_Organization_Id;
UPDATE WIP_OPERATIONS WO
SET WO.PREVIOUS_OPERATION_SEQ_NUM = decode(WO.OPERATION_SEQ_NUM - x_op_seq_incr,0,null,WO.OPERATION_SEQ_NUM - x_op_seq_incr) ,
-- Bug 5336643 Added decode so that null is populated instead of zero
WO.NEXT_OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM + x_op_seq_incr
WHERE WO.WIP_ENTITY_ID = X_Wip_Entity_Id
AND WO.ORGANIZATION_ID = X_Organization_Id
AND WO.OPERATION_SEQ_NUM >= x_from_op
AND WO.OPERATION_SEQ_NUM < x_to_op ;
SELECT operation_seq_num,
WIP_CONSTANTS.SCRAP,
nvl(to_number(STANDARD_OPERATION_ID),0),
ml.meaning
INTO l_op,
l_step,
l_std_operation_id,
l_step_code
FROM
mfg_lookups ml,
wip_operations wo
WHERE wip_entity_id = p_wip_entity_id
AND quantity_in_queue = 0
AND quantity_running = 0
AND quantity_waiting_to_move = 0
AND quantity_scrapped = quantity_completed
AND quantity_completed > 0
AND count_point_type <> 3
AND ml.lookup_type = 'WIP_INTRAOPERATION_STEP'
AND ml.lookup_code = WIP_CONSTANTS.SCRAP;
return; -- if the above select returns a row, then, the
when NO_DATA_FOUND then null; -- if the above select did not return a row, proceed further..
SELECT operation_seq_num,
decode(quantity_in_queue, 0,
decode(quantity_running, 0, WIP_CONSTANTS.TOMOVE, WIP_CONSTANTS.RUN),
WIP_CONSTANTS.QUEUE),
nvl(to_number(STANDARD_OPERATION_ID),0),
ml.meaning
INTO l_op,
l_step,
l_std_operation_id,
l_step_code
FROM
mfg_lookups ml,
wip_operations wo
WHERE wip_entity_id = p_wip_entity_id
AND (quantity_in_queue <> 0
OR quantity_running <> 0
OR quantity_waiting_to_move <> 0)
AND ml.lookup_type = 'WIP_INTRAOPERATION_STEP'
AND ml.lookup_code =
decode(quantity_in_queue, 0,
decode(quantity_running, 0, WIP_CONSTANTS.TOMOVE, WIP_CONSTANTS.RUN),
WIP_CONSTANTS.QUEUE);
SELECT QUEUE_MANDATORY_FLAG,
RUN_MANDATORY_FLAG,
TO_MOVE_MANDATORY_FLAG
INTO l_queue_flag,
l_run_flag,
l_to_move_flag
FROM WSM_OPERATION_DETAILS
WHERE STANDARD_OPERATION_ID = p_std_op_id;
SELECT decode(QUEUE_MANDATORY_FLAG, 0, 2, NULL, 2, QUEUE_MANDATORY_FLAG),
decode(RUN_MANDATORY_FLAG, 0, 2, NULL, 2, RUN_MANDATORY_FLAG),
decode(TO_MOVE_MANDATORY_FLAG, 0, 2, NULL, 2, TO_MOVE_MANDATORY_FLAG)
INTO l_queue_flag,
l_run_flag,
l_to_move_flag
FROM BOM_STANDARD_OPERATIONS
WHERE STANDARD_OPERATION_ID = p_std_op_id;
l_msg_tokens.delete;
p_msg_text => 'After SELECT from BOM_STANDARD_OPERATIONS '||
';l_queue_flag '||
SELECT SECONDARY_INVENTORY_NAME,
INVENTORY_LOCATION_ID
INTO l_secondary_inventory_name,
l_secondary_locator
FROM WSM_OPERATION_DETAILS WOD,
BOM_OPERATION_SEQUENCES BOS
WHERE WOD.STANDARD_OPERATION_ID = BOS.STANDARD_OPERATION_ID
AND BOS.OPERATION_SEQUENCE_ID = l_end_op_seq_id;
SELECT DEFAULT_SUBINVENTORY,
DEFAULT_LOCATOR_ID
INTO l_secondary_inventory_name,
l_secondary_locator
FROM BOM_STANDARD_OPERATIONS BSO,
BOM_OPERATION_SEQUENCES BOS
WHERE BSO.STANDARD_OPERATION_ID = BOS.STANDARD_OPERATION_ID
AND BOS.OPERATION_SEQUENCE_ID = l_end_op_seq_id;
FUNCTION update_job_name (
p_wip_entity_id IN NUMBER,
p_subinventory IN VARCHAR2,
p_org_id IN NUMBER,
p_txn_type IN NUMBER,
/*BA#1803065*/
p_dup_job_name OUT NOCOPY VARCHAR2,
/*BA#1803065*/
x_error_code OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2
) return VARCHAR2 IS
-- Added the parameter P_UPDATE_FLAG to the function update_job_name.
-- The function will update wip_entities with the new job name only
-- if p_update_flag has the value 'TRUE'.
l_sep VARCHAR2(1);
select completion_subinventory
into l_comp_subinv
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id; -- as part of bugfix 2062110: added orgn_id
select bor.completion_subinventory
into l_comp_subinv
from wip_discrete_jobs wdj,BOM_OPERATIONAL_ROUTINGS bor
where wdj.wip_entity_id = p_wip_entity_id
and wdj.organization_id = p_org_id
and wdj.common_routing_sequence_id = bor.routing_sequence_id;
select JOB_COMPLETION_SEPARATOR,nvl(REUSE_JOBNAME,1)
into l_sep,l_reuse_jobname
from wsm_parameters
where organization_id = p_org_id;
select l_sep || wse.sector_extension_code
into l_suffix
from wip_entities we,
wsm_sector_extensions wse,
wsm_item_extensions wie
where we.wip_entity_id = p_wip_entity_id
and we.primary_item_id = wie.inventory_item_id
and we.organization_id = wie.organization_id
and wie.sector_extension_id = wse.sector_extension_id;
select l_sep || wse.sector_extension_code
into l_suffix
from wsm_sector_extensions wse,
wsm_subinventory_extensions wsube
where wse.sector_extension_id = wsube.sector_extension_id
and wsube.secondary_inventory_name = l_comp_subinv -- p_subinventory Fix bug #1504009
and wsube.organization_id = p_org_id;
select wip_entity_name
into l_new_name
from wip_entities
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id; -- as part of bugfix 2062110 : added orgn_id
select count(*)
into l_count
from wip_entities
where wip_entity_name = l_new_name
/*BA#2073251*/
and organization_id = p_org_id;
if (g_update_flag) THEN
update wip_entities
set wip_entity_name = l_new_name
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id; -- as part of bugfix 2062110 : added orgn_id
select wip_entity_name
into l_entity_name
from wip_entities
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
select substr(l_entity_name,1, decode(instr(l_entity_name,l_sep) -1, -1, length(l_entity_name), instr(l_entity_name, l_sep) -1))
into l_new_name
from sys.dual;
select count(*)
into l_count
from wip_entities
where wip_entity_name = l_new_name
and organization_id = p_org_id;
if (g_update_flag) THEN
update wip_entities
set wip_entity_name = l_new_name
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
select wip_entity_name
into l_entity_name
from wip_entities
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
x_error_msg := 'WSMOPRNB.update_job_name('||l_stat_num||'): No Data Found';
x_error_msg := 'WSMOPRNB.update_job_name('||l_stat_num||')'|| substr(SQLERRM,1,200);
END update_job_name;
FUNCTION update_job_name (
p_wip_entity_id IN NUMBER,
p_subinventory IN VARCHAR2,
p_org_id IN NUMBER,
p_txn_type IN NUMBER,
p_update_flag IN BOOLEAN,
/*BA#1803065*/
p_dup_job_name OUT NOCOPY VARCHAR2,
/*BA#1803065*/
x_error_code OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2
) return VARCHAR2 IS
x_ret_val VARCHAR2(240);
g_update_flag:=p_update_flag;
x_ret_val:=update_job_name(p_wip_entity_id,p_subinventory,p_org_id,
p_txn_type,p_dup_job_name,x_error_code,x_error_msg);
g_update_flag:=TRUE; -- Set the default value TRUE
END update_job_name;
PROCEDURE update_job_name1 (
p_wip_entity_id IN NUMBER,
p_org_id IN NUMBER,
p_reentered_job_name IN OUT NOCOPY VARCHAR2,
x_error_code OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2
) IS
l_stat_num NUMBER;
select count(*)
into l_count
from wip_entities
where wip_entity_name = p_reentered_job_name
/*BA#2073251*/
and organization_id = p_org_id;
update wip_entities
set wip_entity_name = p_reentered_job_name
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
x_error_msg := 'WSMOPRNB.update_job_name1('||l_stat_num||'): No Data Found';
x_error_msg := 'WSMOPRNB.update_job_name1('||l_stat_num||')'|| substr(SQLERRM,1,200);
END update_job_name1;
INSERT INTO WIP_OPERATIONS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OPERATION_SEQUENCE_ID,
STANDARD_OPERATION_ID,
DEPARTMENT_ID,
DESCRIPTION,
SCHEDULED_QUANTITY,
QUANTITY_IN_QUEUE,
QUANTITY_RUNNING,
QUANTITY_WAITING_TO_MOVE,
QUANTITY_REJECTED,
QUANTITY_SCRAPPED,
QUANTITY_COMPLETED,
FIRST_UNIT_START_DATE,
FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_START_DATE,
LAST_UNIT_COMPLETION_DATE,
PREVIOUS_OPERATION_SEQ_NUM,
NEXT_OPERATION_SEQ_NUM,
COUNT_POINT_TYPE,
BACKFLUSH_FLAG,
MINIMUM_TRANSFER_QUANTITY,
DATE_LAST_MOVED,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OPERATION_YIELD,
OPERATION_YIELD_ENABLED,
RECOMMENDED,
CUMULATIVE_SCRAP_QUANTITY,
WSM_OP_SEQ_NUM,
WSM_COSTED_QUANTITY_COMPLETED,
LOWEST_ACCEPTABLE_YIELD)
SELECT p_wip_entity_id,
p_to_job_op_seq_num,
p_org_id,
SYSDATE,
p_user,
SYSDATE,
p_user,
p_login,
DECODE(p_request_id, 0, '', p_request_id),
DECODE(p_program_application_id, 0, '', p_program_application_id),
DECODE(p_commit, 1, p_program_id, -999),
DECODE(p_program_id, 0, '', SYSDATE),
WCO.OPERATION_SEQUENCE_ID,
WCO.STANDARD_OPERATION_ID,
WCO.DEPARTMENT_ID,
WCO.OPERATION_DESCRIPTION,
/*bug 3686872 added nvl(WCO.RECO_SCHEDULED_QUANTITY to the code below removed p_txn_quantity*/
--ROUND(nvl(p_txn_quantity, 0), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
ROUND(nvl(WCO.RECO_SCHEDULED_QUANTITY, 0), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
0, 0, 0, 0, 0, 0,
decode(recommended, 'Y', nvl(RECO_START_DATE, p_txn_date), p_txn_date), --move_enh? populate the reco dates for planned op or ...
decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, p_txn_date), p_txn_date),
decode(recommended, 'Y', nvl(RECO_START_DATE, p_txn_date), p_txn_date),
decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, p_txn_date), p_txn_date),
0, 0,
WCO.COUNT_POINT_TYPE,
WCO.BACKFLUSH_FLAG,
NVL(WCO.MINIMUM_TRANSFER_QUANTITY, 0),
'',
WCO.ATTRIBUTE_CATEGORY,
WCO.ATTRIBUTE1,
WCO.ATTRIBUTE2,
WCO.ATTRIBUTE3,
WCO.ATTRIBUTE4,
WCO.ATTRIBUTE5,
WCO.ATTRIBUTE6,
WCO.ATTRIBUTE7,
WCO.ATTRIBUTE8,
WCO.ATTRIBUTE9,
WCO.ATTRIBUTE10,
WCO.ATTRIBUTE11,
WCO.ATTRIBUTE12,
WCO.ATTRIBUTE13,
WCO.ATTRIBUTE14,
WCO.ATTRIBUTE15,
WCO.YIELD,
to_char(WCO.OPERATION_YIELD_ENABLED),
nvl(RECOMMENDED, 'N'),
WDJ.QUANTITY_SCRAPPED,
WCO.operation_seq_num,
0,
WCO.LOWEST_ACCEPTABLE_YIELD
FROM WSM_COPY_OPERATIONS WCO,
WIP_DISCRETE_JOBS WDJ
WHERE WCO.wip_entity_id=p_wip_entity_id
AND WCO.organization_id=p_org_id
AND WCO.operation_seq_num = p_to_rtg_op_seq_num
AND WDJ.organization_id = WCO.organization_id
AND WDJ.wip_entity_id = WCO.wip_entity_id;
X_LAST_UPDATE_LOGIN => p_login,
X_PROGRAM_APPLICATION_ID => p_program_application_id,
X_PROGRAM_ID => p_program_id,
X_REQUEST_ID => p_request_id);
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted ' ||sql%rowcount||' records in WO.');
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||sql%rowcount||' records in WO.');
INSERT INTO WIP_OPERATION_YIELDS
(WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID,PROGRAM_UPDATE_DATE,
STATUS, SCRAP_ACCOUNT, EST_SCRAP_ABSORB_ACCOUNT)
SELECT WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, WO.ORGANIZATION_ID,
SYSDATE,
p_user,
SYSDATE,
p_user,
p_login,
DECODE(p_request_id, 0, '', p_request_id),
DECODE(p_program_application_id, 0, '', p_program_application_id),
DECODE(p_commit, 1, p_program_id, -999),
DECODE(p_program_id, 0, '', SYSDATE),
NULL, BD.SCRAP_ACCOUNT, BD.EST_ABSORPTION_ACCOUNT
FROM WIP_OPERATIONS WO,
BOM_DEPARTMENTS BD
WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
AND WO.OPERATION_SEQ_NUM = p_to_job_op_seq_num
AND WO.ORGANIZATION_ID = p_org_id
AND WO.DEPARTMENT_ID = BD.DEPARTMENT_ID; --bugfix 1611094
INSERT INTO WIP_OPERATION_YIELDS
(WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID,PROGRAM_UPDATE_DATE,
STATUS, SCRAP_ACCOUNT, EST_SCRAP_ABSORB_ACCOUNT)
SELECT WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, WO.ORGANIZATION_ID,
SYSDATE,
p_user,
SYSDATE,
p_user,
p_login,
DECODE(p_request_id, 0, '', p_request_id),
DECODE(p_program_application_id, 0, '', p_program_application_id),
DECODE(p_commit, 1, p_program_id, -999),
DECODE(p_program_id, 0, '', SYSDATE),
NULL, WCO.SCRAP_ACCOUNT, WCO.EST_ABSORPTION_ACCOUNT
FROM WIP_OPERATIONS WO,
WSM_COPY_OPERATIONS WCO
WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
AND WO.OPERATION_SEQ_NUM = p_to_job_op_seq_num
AND WO.ORGANIZATION_ID = p_org_id
AND WCO.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WCO.OPERATION_SEQ_NUM = WO.WSM_OP_SEQ_NUM;
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||sql%rowcount||' records in WOY.');
SELECT 1
INTO l_phantom_exists
FROM WSM_COPY_REQUIREMENT_OPS
WHERE EXISTS (SELECT null
FROM WSM_COPY_REQUIREMENT_OPS WCRO
WHERE WCRO.WIP_ENTITY_ID = p_wip_entity_id
AND WCRO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
AND WCRO.recommended='Y');
SELECT 1
INTO l_phantom_exists
FROM WSM_COPY_REQUIREMENT_OPS WCRO
WHERE WCRO.WIP_ENTITY_ID = p_wip_entity_id
AND WCRO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
AND WCRO.recommended='Y'
AND WCRO.source_phantom_id <> -1;
SELECT WCRO.COMPONENT_ITEM_ID,
WCRO.organization_id,
WCRO.wip_entity_id,
WCRO.COMPONENT_SEQUENCE_ID,
WCRO.WIP_SUPPLY_TYPE,
decode(WCRO.recommended, 'Y', Nvl(WCRO.reco_date_required, p_txn_date), p_txn_date),
--move enh 115.77 component yield no longer factored in
/* round((WCRO.quantity_per_assembly/decode(WCRO.COMPONENT_YIELD_FACTOR,
0, 1, WCRO.COMPONENT_YIELD_FACTOR)), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION)*p_txn_quantity,
round((WCRO.quantity_per_assembly/decode(WCRO.COMPONENT_YIELD_FACTOR,
0, 1, WCRO.COMPONENT_YIELD_FACTOR)), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
*/
--bug 3587239 Round the required quantity to 6 places
WCRO.basis_type, --LBM enh
--Bug 5236684:Component yield factor should include the yield of the source phantom also.
decode(nvl(wcro.basis_type,1),1,nvl(wcro1.component_yield_factor,1),1)*wcro.component_yield_factor, --LBM enh
--component shrinkage
-- ROUND((WCRO.quantity_per_assembly/WCRO.component_yield_factor)
ROUND((WCRO.quantity_per_assembly)
*decode(wcro.basis_type, 2, 1, p_txn_quantity), WSMPCNST.NUMBER_OF_DECIMALS), --LBM enh
--component shrinkage
-- WCRO.quantity_per_assembly,
--Bug 5236684:Bill_quantity_per_assembly should include the Bill_quantity_per_assembly of source phantom also.
decode(nvl(wcro.basis_type,1),1,nvl(WCRO1.bill_quantity_per_assembly,1),1)*WCRO.bill_quantity_per_assembly,
WCRO.supply_subinventory,
WCRO.supply_locator_id,
decode(WCRO.wip_supply_type,
5, 2,
decode(sign(WCRO.quantity_per_assembly),
-1, 2,
1)) mrp_net_flag,
WCRO.component_remarks,
WCRO.attribute_category,
WCRO.attribute1,
WCRO.attribute2,
WCRO.attribute3,
WCRO.attribute4,
WCRO.attribute5,
WCRO.attribute6,
WCRO.attribute7,
WCRO.attribute8,
WCRO.attribute9,
WCRO.attribute10,
WCRO.attribute11,
WCRO.attribute12,
WCRO.attribute13,
WCRO.attribute14,
WCRO.attribute15,
MSI.segment1,
MSI.segment2,
MSI.segment3,
MSI.segment4,
MSI.segment5,
MSI.segment6,
MSI.segment7,
MSI.segment8,
MSI.segment9,
MSI.segment10,
MSI.segment11,
MSI.segment12,
MSI.segment13,
MSI.segment14,
MSI.segment15,
MSI.segment16,
MSI.segment17,
MSI.segment18,
MSI.segment19,
MSI.segment20,
WCRO.department_id
FROM WSM_COPY_REQUIREMENT_OPS WCRO,
WSM_COPY_REQUIREMENT_OPS WCRO1,--Added for bug 5236684
MTL_SYSTEM_ITEMS MSI
WHERE WCRO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
AND WCRO.WIP_ENTITY_ID= p_wip_entity_id
AND MSI.inventory_item_id = WCRO.component_item_id
AND MSI.organization_id = WCRO.organization_id
AND WCRO.RECOMMENDED='Y'
--Added for bug 5236684
AND WCRO.WIP_ENTITY_ID = WCRO1.WIP_ENTITY_ID(+)
AND WCRO1.OPERATION_SEQ_NUM(+)= -1*p_to_rtg_op_seq_num
AND WCRO.source_phantom_id = WCRO1.component_item_id(+)
ORDER BY WCRO.component_item_id, WCRO.wip_supply_type desc;
v_basis_type.delete(i); --LBM enh
v_component_yield_factor.delete(i); --LBM enh
v_inventory_item_id.delete(i);
v_organization_id.delete(i);
v_wip_entity_id.delete(i);
v_component_sequence_id.delete(i);
v_wip_supply_type.delete(i);
v_date_required.delete(i);
v_required_quantity.delete(i);
v_quantity_per_assembly.delete(i);
v_supply_subinventory.delete(i);
v_supply_locator_id.delete(i);
v_mrp_net_flag.delete(i);
v_comments.delete(i);
v_attribute_category.delete(i);
v_attribute1.delete(i);
v_attribute2.delete(i);
v_attribute3.delete(i);
v_attribute4.delete(i);
v_attribute5.delete(i);
v_attribute6.delete(i);
v_attribute7.delete(i);
v_attribute8.delete(i);
v_attribute9.delete(i);
v_attribute10.delete(i);
v_attribute11.delete(i);
v_attribute12.delete(i);
v_attribute13.delete(i);
v_attribute14.delete(i);
v_attribute15.delete(i);
v_segment1.delete(i);
v_segment2.delete(i);
v_segment3.delete(i);
v_segment4.delete(i);
v_segment5.delete(i);
v_segment6.delete(i);
v_segment7.delete(i);
v_segment8.delete(i);
v_segment9.delete(i);
v_segment10.delete(i);
v_segment11.delete(i);
v_segment12.delete(i);
v_segment13.delete(i);
v_segment14.delete(i);
v_segment15.delete(i);
v_segment16.delete(i);
v_segment17.delete(i);
v_segment18.delete(i);
v_segment19.delete(i);
v_segment20.delete(i);
v_department_id.delete(i);
INSERT INTO WIP_REQUIREMENT_OPERATIONS
(inventory_item_id,
organization_id,
wip_entity_id,
operation_seq_num,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
component_sequence_id,
wip_supply_type,
date_required,
basis_type, --LBM enh
required_quantity,
quantity_issued,
quantity_per_assembly,
supply_subinventory,
supply_locator_id,
mrp_net_flag,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
department_id,
released_quantity,
component_yield_factor) --component shrinkage
VALUES (v_inventory_item_id(i)
, v_organization_id(i)
, v_wip_entity_id(i)
, p_to_job_op_seq_num
, NULL,
SYSDATE,
p_user,
SYSDATE,
p_user,
p_login
, v_component_sequence_id(i)
, v_wip_supply_type(i)
, v_date_required(i)
, v_basis_type(i) --LBM enh
, v_required_quantity(i)
, 0
, v_quantity_per_assembly(i)
, v_supply_subinventory(i)
, v_supply_locator_id(i)
, v_mrp_net_flag(i)
, v_comments(i)
, v_attribute_category(i)
, v_attribute1(i)
, v_attribute2(i)
, v_attribute3(i)
, v_attribute4(i)
, v_attribute5(i)
, v_attribute6(i)
, v_attribute7(i)
, v_attribute8(i)
, v_attribute9(i)
, v_attribute10(i)
, v_attribute11(i)
, v_attribute12(i)
, v_attribute13(i)
, v_attribute14(i)
, v_attribute15(i)
, v_segment1(i)
, v_segment2(i)
, v_segment3(i)
, v_segment4(i)
, v_segment5(i)
, v_segment6(i)
, v_segment7(i)
, v_segment8(i)
, v_segment9(i)
, v_segment10(i)
, v_segment11(i)
, v_segment12(i)
, v_segment13(i)
, v_segment14(i)
, v_segment15(i)
, v_segment16(i)
, v_segment17(i)
, v_segment18(i)
, v_segment19(i)
, v_segment20(i)
, v_department_id(i)
--bug 3587239 Round the released quantity to 6 places
, ROUND( decode(v_basis_type(i), 2, 1, p_start_quantity)
*(v_quantity_per_assembly(i)/v_component_yield_factor(i)), WSMPCNST.NUMBER_OF_DECIMALS) --LBM enh
, v_component_yield_factor(i));
INSERT INTO WIP_REQUIREMENT_OPERATIONS
(inventory_item_id,
organization_id,
wip_entity_id,
operation_seq_num,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
component_sequence_id,
wip_supply_type,
date_required,
basis_type, --LBM enh
required_quantity,
quantity_issued,
quantity_per_assembly,
supply_subinventory,
supply_locator_id,
mrp_net_flag,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
department_id,
--VJ: Start additions for Costing enhancement for WLTEnh--
costed_quantity_issued,
costed_quantity_relieved,
--VJ: End additions for Costing enhancement for WLTEnh--
released_quantity,
component_yield_factor) --component shrinkage
SELECT WCRO.COMPONENT_ITEM_ID,
WCRO.organization_id,
WCRO.wip_entity_id,
p_to_job_op_seq_num,
NULL,
SYSDATE,
p_user,
SYSDATE,
p_user,
p_login,
WCRO.COMPONENT_SEQUENCE_ID,
WCRO.WIP_SUPPLY_TYPE,
decode(recommended, 'Y', Nvl(WCRO.reco_date_required, p_txn_date), p_txn_date),
--move enh 115.77 component yield no longer factored in
/* round((WCRO.quantity_per_assembly/decode(WCRO.COMPONENT_YIELD_FACTOR,
0, 1, WCRO.COMPONENT_YIELD_FACTOR)), WSMPCNST.NUMBER_OF_DECIMALS)*p_txn_quantity,
*/
/* round((WCRO.quantity_per_assembly/decode(WCRO.COMPONENT_YIELD_FACTOR,
0, 1, WCRO.COMPONENT_YIELD_FACTOR)), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
*/
--bug 3587239 Round the required quantity to 6 places
WCRO.basis_type, --LBM enh
-- Component shrinkage
-- ROUND((WCRO.quantity_per_assembly/wcro.component_yield_factor)
ROUND((WCRO.quantity_per_assembly)
* decode(wcro.basis_type, 2, 1, p_txn_quantity), WSMPCNST.NUMBER_OF_DECIMALS), --LBM enh
0,
--component shrinkage
-- WCRO.quantity_per_assembly,
WCRO.bill_quantity_per_assembly,
WCRO.supply_subinventory,
WCRO.supply_locator_id,
decode(WCRO.wip_supply_type,
5, 2,
decode(sign(WCRO.quantity_per_assembly),
-1, 2,
1)) mrp_net_flag,
WCRO.component_remarks,
WCRO.attribute_category,
WCRO.attribute1,
WCRO.attribute2,
WCRO.attribute3,
WCRO.attribute4,
WCRO.attribute5,
WCRO.attribute6,
WCRO.attribute7,
WCRO.attribute8,
WCRO.attribute9,
WCRO.attribute10,
WCRO.attribute11,
WCRO.attribute12,
WCRO.attribute13,
WCRO.attribute14,
WCRO.attribute15,
MSI.segment1,
MSI.segment2,
MSI.segment3,
MSI.segment4,
MSI.segment5,
MSI.segment6,
MSI.segment7,
MSI.segment8,
MSI.segment9,
MSI.segment10,
MSI.segment11,
MSI.segment12,
MSI.segment13,
MSI.segment14,
MSI.segment15,
MSI.segment16,
MSI.segment17,
MSI.segment18,
MSI.segment19,
MSI.segment20,
WCRO.department_id,
NULL,
NULL,
--bug 3587239 Round the released quantity to 6 places
ROUND(decode(wcro.basis_type, 2, 1, p_start_quantity)
*(WCRO.quantity_per_assembly/wcro.component_yield_factor), WSMPCNST.NUMBER_OF_DECIMALS), --LBM enh
WCRO.component_yield_factor --component shrinkage
FROM WSM_COPY_REQUIREMENT_OPS WCRO,
MTL_SYSTEM_ITEMS MSI
WHERE WCRO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
AND WCRO.WIP_ENTITY_ID= p_wip_entity_id
AND MSI.inventory_item_id = WCRO.component_item_id
AND MSI.organization_id = WCRO.organization_id
AND WCRO.RECOMMENDED='Y';
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||SQL%ROWCOUNT||' records in WRO.');
INSERT INTO WIP_OPERATION_RESOURCES
(WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
RESOURCE_ID, UOM_CODE,
BASIS_TYPE, USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
SCHEDULED_FLAG, ASSIGNED_UNITS,
/* ST : Detailed Scheduling */
maximum_assigned_units,
batch_id,
firm_flag,
group_sequence_id,
group_sequence_number,
parent_resource_seq,
/* ST : Detailed Scheduling */
AUTOCHARGE_TYPE,
STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
START_DATE, COMPLETION_DATE,
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
ATTRIBUTE15,
SCHEDULE_SEQ_NUM, --bugfix 2493065
SUBSTITUTE_GROUP_NUM,
REPLACEMENT_GROUP_NUM,
PRINCIPLE_FLAG,
SETUP_ID)
SELECT WCOR.WIP_ENTITY_ID
, p_to_job_op_seq_num
, WCOR.RESOURCE_SEQ_NUM
, WCOR.ORGANIZATION_ID
, NULL
, SYSDATE
, p_user
, SYSDATE
, p_user
, p_login
, DECODE(p_request_id, 0, '', p_request_id)
, DECODE(p_program_application_id, 0, '', p_program_application_id)
, DECODE(p_commit, 1, p_program_id, -999)
, DECODE(p_program_id, 0, '', SYSDATE)
, WCOR.RESOURCE_ID
, WCOR.UOM_CODE
, WCOR.BASIS_TYPE
, WCOR.USAGE_RATE_OR_AMOUNT
, WCOR.ACTIVITY_ID
, WCOR.SCHEDULE_FLAG
, WCOR.ASSIGNED_UNITS
/* ST : Detailed Scheduling */
, WCOR.MAX_ASSIGNED_UNITS
, WCOR.batch_id
, WCOR.firm_type
, WCOR.group_sequence_id
, WCOR.group_sequence_num
, WCOR.parent_resource_seq_num
/* ST : Detailed Scheduling */
, WCOR.AUTOCHARGE_TYPE
, WCOR.STANDARD_RATE_FLAG
, 0
, 0
, decode(recommended, 'Y', nvl(RECO_START_DATE, p_txn_date), p_txn_date)
, decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, p_txn_date), p_txn_date)
, WCOR.ATTRIBUTE_CATEGORY
, WCOR.ATTRIBUTE1
, WCOR.ATTRIBUTE2
, WCOR.ATTRIBUTE3
, WCOR.ATTRIBUTE4
, WCOR.ATTRIBUTE5
, WCOR.ATTRIBUTE6
, WCOR.ATTRIBUTE7
, WCOR.ATTRIBUTE8
, WCOR.ATTRIBUTE9
, WCOR.ATTRIBUTE10
, WCOR.ATTRIBUTE11
, WCOR.ATTRIBUTE12
, WCOR.ATTRIBUTE13
, WCOR.ATTRIBUTE14
, WCOR.ATTRIBUTE15
, WCOR.SCHEDULE_SEQ_NUM
, WCOR.SUBSTITUTE_GROUP_NUM
, WCOR.REPLACEMENT_GROUP_NUM
, WCOR.PRINCIPLE_FLAG
, WCOR.SETUP_ID
FROM WSM_COPY_OP_RESOURCES WCOR
WHERE WCOR.ORGANIZATION_ID = p_org_id
AND WCOR.WIP_ENTITY_ID = p_Wip_Entity_Id
AND WCOR.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
AND WCOR.recommended='Y';
INSERT INTO WIP_SUB_OPERATION_RESOURCES
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
resource_id,
uom_code,
basis_type,
usage_rate_or_amount,
activity_id,
scheduled_flag,
assigned_units,
maximum_assigned_units, /* ST : Detailed Scheduling */
autocharge_type,
standard_rate_flag,
applied_resource_units,
applied_resource_value,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
completion_date,
start_date,
schedule_seq_num,
substitute_group_num,
replacement_group_num,
setup_id,
department_id)
SELECT WCOR.wip_entity_id,
p_to_job_op_seq_num,
WCOR.resource_seq_num,
WCOR.organization_id,
null,
SYSDATE ,
p_user,
SYSDATE,
p_user,
p_login,
WCOR.resource_id,
WCOR.uom_code,
WCOR.basis_type,
WCOR.usage_rate_or_amount,
WCOR.activity_id,
WCOR.schedule_flag,
WCOR.assigned_units,
WCOR.max_assigned_units,
WCOR.autocharge_type,
WCOR.standard_rate_flag,
0, --WCOR.applied_resource_units,--move enh?
0, -- WCOR.applied_resource_value, --move enh?
WCOR.attribute_category,
WCOR.attribute1,
WCOR.attribute2,
WCOR.attribute3,
WCOR.attribute4,
WCOR.attribute5,
WCOR.attribute6,
WCOR.attribute7,
WCOR.attribute8,
WCOR.attribute9,
WCOR.attribute10,
WCOR.attribute11,
WCOR.attribute12,
WCOR.attribute13,
WCOR.attribute14,
WCOR.attribute15,
--as per Zhaohui copying the dates from WCOR
nvl(WCOR.RECO_START_DATE, p_txn_date),
nvl(WCOR.RECO_COMPLETION_DATE, p_txn_date),
WCOR.schedule_seq_num,
WCOR.substitute_group_num,
WCOR.replacement_group_num,
WCOR.setup_id,
WCOR.department_id
FROM WSM_COPY_OP_RESOURCES WCOR
WHERE WCOR.ORGANIZATION_ID = p_org_id
AND WCOR.WIP_ENTITY_ID = p_Wip_Entity_Id
AND WCOR.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
AND WCOR.PHANTOM_ITEM_ID IS NULL
AND WCOR.recommended<>'Y';
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||SQL%ROWCOUNT||
' records in WIP_SUB_OPERATION_RESOURCES.');
INSERT into WIP_OP_RESOURCE_INSTANCES
(WIP_ENTITY_ID
, OPERATION_SEQ_NUM
, RESOURCE_SEQ_NUM
, ORGANIZATION_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, INSTANCE_ID
, SERIAL_NUMBER
, START_DATE
, COMPLETION_DATE
, BATCH_ID)
SELECT WCORI.WIP_ENTITY_ID
, p_to_job_op_seq_num
, WCORI.RESOURCE_SEQ_NUM
, WCORI.ORGANIZATION_ID
, SYSDATE
, p_user
, SYSDATE
, p_user
, p_login
, WCORI.INSTANCE_ID
, WCORI.SERIAL_NUMBER
, WCORI.START_DATE
, WCORI.COMPLETION_DATE
, WCORI.BATCH_ID
FROM WSM_COPY_OP_RESOURCE_INSTANCES WCORI
-- WIP_OPERATION_RESOURCES WOR Bug 5478658 join with WOR not required
WHERE WCORI.WIP_ENTITY_ID= p_wip_entity_id
AND WCORI.Operation_seq_num = p_to_rtg_op_seq_num ;
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||SQL%ROWCOUNT
||' rows in WIP_OP_RESOURCE_INSTANCES');
INSERT into wip_operation_resource_usage
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
REPETITIVE_SCHEDULE_ID,
ORGANIZATION_ID,
START_DATE,
COMPLETION_DATE,
ASSIGNED_UNITS,
-- resource_hours, /* ST : Detailed scheduling */
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
INSTANCE_ID,
SERIAL_NUMBER,
CUMULATIVE_PROCESSING_TIME)
SELECT WCORU.WIP_ENTITY_ID,
p_to_job_op_seq_num,
WCORU.RESOURCE_SEQ_NUM,
null,
WCORU.ORGANIZATION_ID,
WCORU.START_DATE,
WCORU.COMPLETION_DATE,
WCORU.ASSIGNED_UNITS,
-- WCORU.RESOURCE_HOURS, /* ST : Detailed scheduling */
SYSDATE ,
p_user,
SYSDATE,
p_user,
p_login,
DECODE(p_request_id, 0, '', p_request_id),
DECODE(p_program_application_id, 0, '', p_program_application_id),
DECODE(p_commit, 1, p_program_id, -999),
DECODE(p_program_id, 0, '', SYSDATE),
WCORU.INSTANCE_ID,
WCORU.SERIAL_NUMBER,
WCORU.CUMULATIVE_PROCESSING_TIME
FROM -- WIP_OPERATION_RESOURCES WOR, Bug 5478658 join with WOR not required
WSM_COPY_OP_RESOURCE_USAGE WCORU
WHERE WCORU.WIP_ENTITY_ID= p_wip_entity_id
AND WCORU.Operation_seq_num = p_to_rtg_op_seq_num ;
fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||SQL%ROWCOUNT
||' rows in wip_operation_resource_usage');
l_msg_tokens.delete;
SELECT nvl(recommended, 'N'), RECO_START_DATE, reco_completion_date
INTO l_recommended_op, l_reco_start_date, l_reco_completion_date
FROM WSM_COPY_OPERATIONS
WHERE wip_entity_id = p_wip_entity_id
AND operation_sequence_id = p_to_op_seq_id;
SELECT last_unit_completion_date
INTO l_infi_start_date
FROM WIP_OPERATIONS
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_org_id
AND operation_seq_num = p_fm_job_op_seq_num;
UPDATE WIP_OPERATIONS WO
SET cumulative_scrap_quantity =
(SELECT quantity_scrapped
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.wip_entity_id = p_wip_entity_id
AND WDJ.organization_id = p_org_id)
WHERE WO.wip_entity_id = p_wip_entity_id
AND WO.organization_id = p_org_id
AND WO.operation_seq_num = p_job_op_seq_num;
l_param_tbl.delete;
INSERT INTO WSM_OP_SECONDARY_QUANTITIES
(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
ORGANIZATION_ID,
WIP_ENTITY_ID,
UOM_CODE,
OPERATION_SEQ_NUM,
MOVE_IN_QUANTITY,
MOVE_OUT_QUANTITY
)
SELECT SYSDATE,
p_user,
p_login,
SYSDATE,
p_user,
ORGANIZATION_ID,
WIP_ENTITY_ID,
UOM_CODE,
p_to_job_op_seq_num,
CURRENT_QUANTITY,
NULL
FROM WSM_JOB_SECONDARY_QUANTITIES
WHERE WIP_ENTITY_ID = p_wip_entity_id
and currently_active = 1;
INSERT INTO WSM_OP_REASON_CODES
(
ORGANIZATION_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
CODE_TYPE,
REASON_CODE,
QUANTITY,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
LAST_UPDATED_LOGIN
)
SELECT DISTINCT ORGANIZATION_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
1, --CODE_TYPE,
SCRAP_CODE,
NULL,
p_user,
SYSDATE,
p_user,
SYSDATE,
p_login
FROM BOM_STD_OP_SCRAP_CODES BSOSC,
WIP_OPERATIONS WO
WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
AND WO.OPERATION_SEQ_NUM = p_to_job_op_seq_num
AND BSOSC.STANDARD_OPERATION_ID = WO.STANDARD_OPERATION_ID;
INSERT INTO WSM_OP_REASON_CODES
(
ORGANIZATION_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
CODE_TYPE,
REASON_CODE,
QUANTITY,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
LAST_UPDATED_LOGIN
)
SELECT DISTINCT ORGANIZATION_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
2, --CODE_TYPE,
BONUS_CODE,
NULL,
p_user,
SYSDATE,
p_user,
SYSDATE,
p_login
FROM BOM_STD_OP_BONUS_CODES BSOSC,
WIP_OPERATIONS WO
WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
AND WO.OPERATION_SEQ_NUM = p_to_job_op_seq_num
AND BSOSC.STANDARD_OPERATION_ID = WO.STANDARD_OPERATION_ID;
UPDATE WSM_LOT_BASED_JOBS
SET current_job_op_seq_num = p_to_job_op_seq_num,
current_rtg_op_seq_num = p_to_rtg_op_seq_num
WHERE WIP_ENTITY_ID = p_wip_entity_id;
function update_job_name(p_wip_entity_id NUMBER
, p_subinventory VARCHAR2
, p_org_id NUMBER
, p_txn_type NUMBER
, p_update_flag number
, p_dup_job_name out nocopy VARCHAR2
, x_error_code out nocopy NUMBER
, x_error_msg out nocopy VARCHAR2
) return varchar2
as
ddp_update_flag boolean;
if p_update_flag is null
then ddp_update_flag := null;
elsif p_update_flag = 0
then ddp_update_flag := false;
else ddp_update_flag := true;
ddrosetta_retval := WSMPOPRN.update_job_name(p_wip_entity_id,
p_subinventory,
p_org_id,
p_txn_type,
ddp_update_flag,
p_dup_job_name,
x_error_code,
x_error_msg);