[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT CALENDAR_CODE,
CALENDAR_EXCEPTION_SET_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_org_id;
SELECT WRS.WIP_ENTITY_ID,
WRS.DAILY_PRODUCTION_RATE,
WRS.PROCESSING_WORK_DAYS,
CEIL(WRS.PROCESSING_WORK_DAYS),
WRS.QUANTITY_COMPLETED,
CD1.NEXT_SEQ_NUM,
CD2.NEXT_SEQ_NUM,
CD3.PRIOR_SEQ_NUM,
CD4.PRIOR_SEQ_NUM
FROM BOM_CALENDAR_DATES CD1,
BOM_CALENDAR_DATES CD2,
BOM_CALENDAR_DATES CD3,
BOM_CALENDAR_DATES CD4,
WIP_REPETITIVE_SCHEDULES WRS
WHERE WRS.ORGANIZATION_ID = p_org_id
AND WRS.REPETITIVE_SCHEDULE_ID = p_sched_id
AND CD1.CALENDAR_CODE = x_cal_code
AND CD1.EXCEPTION_SET_ID = x_excp_set_id
AND CD1.CALENDAR_DATE = TRUNC(WRS.FIRST_UNIT_START_DATE)
AND CD2.CALENDAR_CODE = x_cal_code
AND CD2.EXCEPTION_SET_ID = x_excp_set_id
AND CD2.CALENDAR_DATE = TRUNC(WRS.FIRST_UNIT_COMPLETION_DATE)
AND CD3.CALENDAR_CODE = x_cal_code
AND CD3.EXCEPTION_SET_ID = x_excp_set_id
AND CD3.CALENDAR_DATE = TRUNC(WRS.LAST_UNIT_START_DATE)
AND CD4.CALENDAR_CODE = x_cal_code
AND CD4.EXCEPTION_SET_ID = x_excp_set_id
AND CD4.CALENDAR_DATE = TRUNC(WRS.LAST_UNIT_COMPLETION_DATE);
SELECT NVL(MIN(OPERATION_SEQ_NUM), -1)
FROM WIP_OPERATIONS
WHERE ORGANIZATION_ID = p_org_id
AND WIP_ENTITY_ID = x_wip_id
AND REPETITIVE_SCHEDULE_ID = p_sched_id;
SELECT QUANTITY_RUNNING + QUANTITY_COMPLETED
FROM WIP_OPERATIONS
WHERE ORGANIZATION_ID = p_org_id
AND WIP_ENTITY_ID = x_wip_id
AND OPERATION_SEQ_NUM = x_first_op
AND REPETITIVE_SCHEDULE_ID = p_sched_id;
SELECT WIP_REPETITIVE_SCHEDULES_S.NEXTVAL
FROM DUAL;
SELECT WRS.line_id, WRI.class_code
FROM WIP_REPETITIVE_ITEMS WRI, WIP_REPETITIVE_SCHEDULES WRS
WHERE WRS.REPETITIVE_SCHEDULE_ID = p_new_sched_id
AND WRS.ORGANIZATION_ID = p_org_id
AND WRS.WIP_ENTITY_ID = x_wip_id
AND WRI.WIP_ENTITY_ID = x_wip_id
AND WRI.LINE_ID = wrs.line_id;
SELECT distinct pk2_value
FROM fnd_attached_documents
WHERE pk1_value = to_char(x_wip_id)
AND pk3_value = to_char(p_org_id)
AND pk4_value = to_char(p_sched_id)
AND entity_name = 'WIP_REPETITIVE_OPERATIONS';
INSERT INTO WIP_REPETITIVE_SCHEDULES
(REPETITIVE_SCHEDULE_ID, 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,
WIP_ENTITY_ID, LINE_ID,
DAILY_PRODUCTION_RATE, PROCESSING_WORK_DAYS,
STATUS_TYPE, FIRM_PLANNED_FLAG,
ALTERNATE_BOM_DESIGNATOR, COMMON_BOM_SEQUENCE_ID,
BOM_REVISION, BOM_REVISION_DATE,
ALTERNATE_ROUTING_DESIGNATOR, COMMON_ROUTING_SEQUENCE_ID,
ROUTING_REVISION, ROUTING_REVISION_DATE,
FIRST_UNIT_START_DATE, FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_START_DATE, LAST_UNIT_COMPLETION_DATE,
DATE_RELEASED, DATE_CLOSED,
QUANTITY_COMPLETED, DESCRIPTION,
DEMAND_CLASS, MATERIAL_ACCOUNT,
MATERIAL_OVERHEAD_ACCOUNT, MATERIAL_VARIANCE_ACCOUNT,
OUTSIDE_PROCESSING_ACCOUNT, OUTSIDE_PROC_VARIANCE_ACCOUNT,
OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT,
RESOURCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT,
ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15)
SELECT p_new_sched_id, WRS.ORGANIZATION_ID,
SYSDATE, x_user_id,
SYSDATE, x_user_id, x_login_id,
DECODE(x_request_id, 0, '', x_request_id),
DECODE(x_appl_id, 0, '', x_appl_id),
DECODE(x_program_id, 0, '', x_program_id),
DECODE(x_program_id, 0, '', SYSDATE),
WRS.WIP_ENTITY_ID, WRS.LINE_ID,
x_rate, x_act_days_left,
WRS.STATUS_TYPE, WRS.FIRM_PLANNED_FLAG,
WRS.ALTERNATE_BOM_DESIGNATOR, WRS.COMMON_BOM_SEQUENCE_ID,
WRS.BOM_REVISION, WRS.BOM_REVISION_DATE,
WRS.ALTERNATE_ROUTING_DESIGNATOR,
WRS.COMMON_ROUTING_SEQUENCE_ID,
WRS.ROUTING_REVISION, WRS.ROUTING_REVISION_DATE,
TO_DATE(TO_CHAR(CD1.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
TO_CHAR(WRS.FIRST_UNIT_START_DATE, WIP_CONSTANTS.TIMESEC_FMT),
WIP_CONSTANTS.DATETIME_FMT),
TO_DATE(TO_CHAR(CD2.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
TO_CHAR(WRS.FIRST_UNIT_COMPLETION_DATE, WIP_CONSTANTS.TIMESEC_FMT),
WIP_CONSTANTS.DATETIME_FMT),
WRS.LAST_UNIT_START_DATE, WRS.LAST_UNIT_COMPLETION_DATE,
SYSDATE, WRS.DATE_CLOSED,
0, WRS.DESCRIPTION,
WRS.DEMAND_CLASS, WRS.MATERIAL_ACCOUNT,
WRS.MATERIAL_OVERHEAD_ACCOUNT,
WRS.MATERIAL_VARIANCE_ACCOUNT,
WRS.OUTSIDE_PROCESSING_ACCOUNT,
WRS.OUTSIDE_PROC_VARIANCE_ACCOUNT,
WRS.OVERHEAD_ACCOUNT, WRS.OVERHEAD_VARIANCE_ACCOUNT,
WRS.RESOURCE_ACCOUNT, WRS.RESOURCE_VARIANCE_ACCOUNT,
WRS.ATTRIBUTE_CATEGORY, WRS.ATTRIBUTE1,
WRS.ATTRIBUTE2, WRS.ATTRIBUTE3,
WRS.ATTRIBUTE4, WRS.ATTRIBUTE5,
WRS.ATTRIBUTE6, WRS.ATTRIBUTE7,
WRS.ATTRIBUTE8, WRS.ATTRIBUTE9,
WRS.ATTRIBUTE10, WRS.ATTRIBUTE11,
WRS.ATTRIBUTE12, WRS.ATTRIBUTE13,
WRS.ATTRIBUTE14, WRS.ATTRIBUTE15
FROM BOM_CALENDAR_DATES CD1,
BOM_CALENDAR_DATES CD2,
WIP_REPETITIVE_SCHEDULES WRS
WHERE WRS.ORGANIZATION_ID = p_org_id
AND WRS.REPETITIVE_SCHEDULE_ID = p_sched_id
AND CD1.CALENDAR_CODE = x_cal_code
AND CD1.EXCEPTION_SET_ID = x_excp_set_id
AND CD1.SEQ_NUM = x_fusd + x_rnd_days_ran
AND CD2.CALENDAR_CODE = x_cal_code
AND CD2.EXCEPTION_SET_ID = x_excp_set_id
AND CD2.SEQ_NUM = x_fucd + x_rnd_days_ran;
wip_change_status.insert_period_balances(x_wip_id, p_org_id,
p_new_sched_id, x_line_id,
x_class_code);
UPDATE WIP_REPETITIVE_SCHEDULES
SET PROCESSING_WORK_DAYS = x_rnd_days_ran,
LAST_UNIT_START_DATE =
(SELECT TO_DATE(TO_CHAR(CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
TO_CHAR(LAST_UNIT_START_DATE, WIP_CONSTANTS.TIMESEC_FMT),
WIP_CONSTANTS.DATETIME_FMT)
FROM BOM_CALENDAR_DATES
WHERE CALENDAR_CODE = x_cal_code
AND EXCEPTION_SET_ID = x_excp_set_id
AND SEQ_NUM = x_lusd - x_rnd_days_left),
LAST_UNIT_COMPLETION_DATE =
(SELECT TO_DATE(TO_CHAR(CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
TO_CHAR(LAST_UNIT_COMPLETION_DATE,
WIP_CONSTANTS.TIMESEC_FMT),
WIP_CONSTANTS.DATETIME_FMT)
FROM BOM_CALENDAR_DATES
WHERE CALENDAR_CODE = x_cal_code
AND EXCEPTION_SET_ID = x_excp_set_id
AND SEQ_NUM = x_lucd - x_rnd_days_left),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = x_user_id,
REQUEST_ID =
DECODE(x_request_id, 0, REQUEST_ID, x_request_id),
PROGRAM_APPLICATION_ID =
DECODE(x_appl_id, 0, PROGRAM_APPLICATION_ID, x_appl_id),
PROGRAM_ID =
DECODE(x_program_id, 0, PROGRAM_ID, x_program_id),
PROGRAM_UPDATE_DATE =
DECODE(x_program_id, 0, PROGRAM_UPDATE_DATE, SYSDATE)
WHERE ORGANIZATION_ID = p_org_id
AND REPETITIVE_SCHEDULE_ID = p_sched_id;
INSERT INTO WIP_OPERATIONS
(WIP_ENTITY_ID, OPERATION_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,
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, DATE_LAST_MOVED,
CUMULATIVE_SCRAP_QUANTITY, /* Enh#2864382*/
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, LONG_DESCRIPTION,
ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15)
SELECT OPS.WIP_ENTITY_ID, OPS.OPERATION_SEQ_NUM,
OPS.ORGANIZATION_ID, p_new_sched_id,
SYSDATE, x_user_id,
SYSDATE, x_user_id, x_login_id,
DECODE(x_request_id, 0, '', x_request_id),
DECODE(x_appl_id, 0, '', x_appl_id),
DECODE(x_program_id, 0, '', x_program_id),
DECODE(x_program_id, 0, '', SYSDATE),
OPS.OPERATION_SEQUENCE_ID, OPS.STANDARD_OPERATION_ID,
OPS.DEPARTMENT_ID, OPS.DESCRIPTION,
x_rate * x_act_days_left,
DECODE(OPS.OPERATION_SEQ_NUM,
x_first_op, x_rate * x_act_days_left, 0),
0, 0,
0, 0,
0, '',0,
TO_DATE(TO_CHAR(CD1.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
TO_CHAR(OPS.FIRST_UNIT_START_DATE, WIP_CONSTANTS.TIMESEC_FMT),
WIP_CONSTANTS.DATETIME_FMT),
TO_DATE(TO_CHAR(CD2.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
TO_CHAR(OPS.FIRST_UNIT_COMPLETION_DATE, WIP_CONSTANTS.TIMESEC_FMT),
WIP_CONSTANTS.DATETIME_FMT),
OPS.LAST_UNIT_START_DATE, OPS.LAST_UNIT_COMPLETION_DATE,
OPS.PREVIOUS_OPERATION_SEQ_NUM, OPS.NEXT_OPERATION_SEQ_NUM,
OPS.COUNT_POINT_TYPE, OPS.BACKFLUSH_FLAG,
OPS.MINIMUM_TRANSFER_QUANTITY, OPS.LONG_DESCRIPTION,
OPS.ATTRIBUTE_CATEGORY, OPS.ATTRIBUTE1,
OPS.ATTRIBUTE2, OPS.ATTRIBUTE3,
OPS.ATTRIBUTE4, OPS.ATTRIBUTE5,
OPS.ATTRIBUTE6, OPS.ATTRIBUTE7,
OPS.ATTRIBUTE8, OPS.ATTRIBUTE9,
OPS.ATTRIBUTE10, OPS.ATTRIBUTE11,
OPS.ATTRIBUTE12, OPS.ATTRIBUTE13,
OPS.ATTRIBUTE14, OPS.ATTRIBUTE15
FROM BOM_CALENDAR_DATES CD1,
BOM_CALENDAR_DATES CD2,
WIP_OPERATIONS OPS
WHERE OPS.ORGANIZATION_ID = p_org_id
AND OPS.WIP_ENTITY_ID = x_wip_id
AND OPS.REPETITIVE_SCHEDULE_ID = p_sched_id
AND CD1.CALENDAR_CODE = x_cal_code
AND CD1.EXCEPTION_SET_ID = x_excp_set_id
AND CD1.SEQ_NUM =
(SELECT NEXT_SEQ_NUM + x_rnd_days_ran
FROM BOM_CALENDAR_DATES
WHERE CALENDAR_CODE = x_cal_code
AND EXCEPTION_SET_ID = x_excp_set_id
AND CALENDAR_DATE =
TRUNC(OPS.FIRST_UNIT_START_DATE))
AND CD2.CALENDAR_CODE = x_cal_code
AND CD2.EXCEPTION_SET_ID = x_excp_set_id
AND CD2.SEQ_NUM =
(SELECT NEXT_SEQ_NUM + x_rnd_days_ran
FROM BOM_CALENDAR_DATES
WHERE CALENDAR_CODE = x_cal_code
AND EXCEPTION_SET_ID = x_excp_set_id
AND CALENDAR_DATE =
TRUNC(OPS.FIRST_UNIT_COMPLETION_DATE));
UPDATE WIP_OPERATIONS OPS
SET QUANTITY_IN_QUEUE =
DECODE(OPERATION_SEQ_NUM,
x_first_op,
QUANTITY_IN_QUEUE - x_act_days_left * x_rate,
QUANTITY_IN_QUEUE),
SCHEDULED_QUANTITY = x_rate * x_rnd_days_ran,
LAST_UNIT_START_DATE =
(SELECT TO_DATE(TO_CHAR(CD2.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
TO_CHAR(OPS.LAST_UNIT_START_DATE,
WIP_CONSTANTS.TIMESEC_FMT),
WIP_CONSTANTS.DATETIME_FMT)
FROM BOM_CALENDAR_DATES CD1,
BOM_CALENDAR_DATES CD2
WHERE CD1.CALENDAR_CODE = x_cal_code
AND CD1.EXCEPTION_SET_ID = x_excp_set_id
AND CD1.CALENDAR_DATE =
TRUNC(OPS.LAST_UNIT_START_DATE)
AND CD2.CALENDAR_CODE = x_cal_code
AND CD2.EXCEPTION_SET_ID = x_excp_set_id
AND CD2.SEQ_NUM = CD1.PRIOR_SEQ_NUM - x_rnd_days_left),
LAST_UNIT_COMPLETION_DATE =
(SELECT TO_DATE(TO_CHAR(CD2.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
TO_CHAR(OPS.LAST_UNIT_COMPLETION_DATE,
WIP_CONSTANTS.TIMESEC_FMT),
WIP_CONSTANTS.DATETIME_FMT)
FROM BOM_CALENDAR_DATES CD1,
BOM_CALENDAR_DATES CD2
WHERE CD1.CALENDAR_CODE = x_cal_code
AND CD1.EXCEPTION_SET_ID = x_excp_set_id
AND CD1.CALENDAR_DATE =
TRUNC(OPS.LAST_UNIT_COMPLETION_DATE)
AND CD2.CALENDAR_CODE = x_cal_code
AND CD2.EXCEPTION_SET_ID = x_excp_set_id
AND CD2.SEQ_NUM = CD1.PRIOR_SEQ_NUM - x_rnd_days_left),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = x_user_id,
REQUEST_ID =
DECODE(x_request_id, 0, REQUEST_ID, x_request_id),
PROGRAM_APPLICATION_ID =
DECODE(x_appl_id, 0, PROGRAM_APPLICATION_ID, x_appl_id),
PROGRAM_ID =
DECODE(x_program_id, 0, PROGRAM_ID, x_program_id),
PROGRAM_UPDATE_DATE =
DECODE(x_program_id, 0, PROGRAM_UPDATE_DATE, SYSDATE)
WHERE ORGANIZATION_ID = p_org_id
AND WIP_ENTITY_ID = x_wip_id
AND REPETITIVE_SCHEDULE_ID = p_sched_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)
SELECT R.WIP_ENTITY_ID, R.OPERATION_SEQ_NUM,
R.RESOURCE_SEQ_NUM, R.ORGANIZATION_ID,
p_new_sched_id, SYSDATE,
x_user_id, SYSDATE,
x_user_id, x_login_id,
DECODE(x_request_id, 0, '', x_request_id),
DECODE(x_appl_id, 0, '', x_appl_id),
DECODE(x_program_id, 0, '', x_program_id),
DECODE(x_program_id, 0, '', SYSDATE),
R.RESOURCE_ID, R.UOM_CODE, R.BASIS_TYPE,
R.USAGE_RATE_OR_AMOUNT, R.ACTIVITY_ID,
R.SCHEDULED_FLAG, R.ASSIGNED_UNITS,
R.AUTOCHARGE_TYPE, R.STANDARD_RATE_FLAG,
0, 0,
O.FIRST_UNIT_START_DATE, O.LAST_UNIT_COMPLETION_DATE,
R.ATTRIBUTE_CATEGORY, R.ATTRIBUTE1,
R.ATTRIBUTE2, R.ATTRIBUTE3,
R.ATTRIBUTE4, R.ATTRIBUTE5,
R.ATTRIBUTE6, R.ATTRIBUTE7,
R.ATTRIBUTE8, R.ATTRIBUTE9,
R.ATTRIBUTE10, R.ATTRIBUTE11,
R.ATTRIBUTE12, R.ATTRIBUTE13,
R.ATTRIBUTE14, R.ATTRIBUTE15
FROM WIP_OPERATION_RESOURCES R,
WIP_OPERATIONS O
WHERE R.WIP_ENTITY_ID = x_wip_id
AND R.ORGANIZATION_ID = p_org_id
AND R.REPETITIVE_SCHEDULE_ID = p_sched_id
AND O.WIP_ENTITY_ID = x_wip_id
AND O.ORGANIZATION_ID = p_org_id
AND O.REPETITIVE_SCHEDULE_ID = p_new_sched_id
AND R.OPERATION_SEQ_NUM = O.OPERATION_SEQ_NUM;
UPDATE WIP_OPERATION_RESOURCES OPS
SET COMPLETION_DATE =
(SELECT TO_DATE(TO_CHAR(CD2.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
TO_CHAR(OPS.COMPLETION_DATE,
WIP_CONSTANTS.TIMESEC_FMT),
WIP_CONSTANTS.DATETIME_FMT)
FROM BOM_CALENDAR_DATES CD1,
BOM_CALENDAR_DATES CD2
WHERE CD1.CALENDAR_CODE = x_cal_code
AND CD1.EXCEPTION_SET_ID = x_excp_set_id
AND CD1.CALENDAR_DATE =
TRUNC(OPS.COMPLETION_DATE)
AND CD2.CALENDAR_CODE = x_cal_code
AND CD2.EXCEPTION_SET_ID = x_excp_set_id
AND CD2.SEQ_NUM = CD1.PRIOR_SEQ_NUM - x_rnd_days_left),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = x_user_id,
REQUEST_ID =
DECODE(x_request_id, 0, REQUEST_ID, x_request_id),
PROGRAM_APPLICATION_ID =
DECODE(x_appl_id, 0, PROGRAM_APPLICATION_ID, x_appl_id),
PROGRAM_ID =
DECODE(x_program_id, 0, PROGRAM_ID, x_program_id),
PROGRAM_UPDATE_DATE =
DECODE(x_program_id, 0, PROGRAM_UPDATE_DATE, SYSDATE)
WHERE ORGANIZATION_ID = p_org_id
AND WIP_ENTITY_ID = x_wip_id
AND REPETITIVE_SCHEDULE_ID = p_sched_id;
/* jkent->lyao: Self-referential insertion? Hmm...
INSERT INTO WIP_OPERATION_INSTRUCTIONS
(WIP_ENTITY_ID, OPERATION_SEQ_NUM,
OPERATION_DESCRIPTION_CODE, 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,
ATTRIBUTE_CATEGORY, ATTRIBUTE1,
. . .
ATTRIBUTE14, ATTRIBUTE15)
SELECT WIP_ENTITY_ID, OPERATION_SEQ_NUM,
OPERATION_DESCRIPTION_CODE, ORGANIZATION_ID,
p_new_sched_id, SYSDATE,
x_user_id, SYSDATE,
x_user_id, x_login_id,
DECODE(x_request_id, 0, '', x_request_id),
DECODE(x_appl_id, 0, '', x_appl_id),
DECODE(x_program_id, 0, '', x_program_id),
DECODE(x_program_id, 0, '', SYSDATE),
ATTRIBUTE_CATEGORY, ATTRIBUTE1,
. . .
ATTRIBUTE14, ATTRIBUTE15
FROM WIP_OPERATION_INSTRUCTIONS
WHERE WIP_ENTITY_ID = x_wip_id
AND ORGANIZATION_ID = p_org_id
AND REPETITIVE_SCHEDULE_ID = p_sched_id;
X_LAST_UPDATE_LOGIN => x_login_id,
X_PROGRAM_APPLICATION_ID => x_appl_id,
X_PROGRAM_ID => x_program_id,
X_REQUEST_ID => x_request_id);
UPDATE WIP_REQUIREMENT_OPERATIONS R
SET REQUIRED_QUANTITY = QUANTITY_PER_ASSEMBLY *
x_rate * x_rnd_days_ran,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = x_user_id,
REQUEST_ID =
DECODE(x_request_id, 0, REQUEST_ID, x_request_id),
PROGRAM_APPLICATION_ID =
DECODE(x_appl_id, 0, PROGRAM_APPLICATION_ID, x_appl_id),
PROGRAM_ID =
DECODE(x_program_id, 0, PROGRAM_ID, x_program_id),
PROGRAM_UPDATE_DATE =
DECODE(x_program_id, 0, PROGRAM_UPDATE_DATE, SYSDATE)
WHERE WIP_ENTITY_ID = x_wip_id
AND ORGANIZATION_ID = p_org_id
AND REPETITIVE_SCHEDULE_ID = p_sched_id
AND REQUIRED_QUANTITY > 0;
UPDATE WIP_OPERATIONS
SET QUANTITY_IN_QUEUE =
DECODE(OPERATION_SEQ_NUM,
x_first_op, x_rate * x_act_days_left,
QUANTITY_IN_QUEUE),
SCHEDULED_QUANTITY = x_rate * x_act_days_left,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = x_user_id,
REQUEST_ID =
DECODE(x_request_id, 0, REQUEST_ID, x_request_id),
PROGRAM_APPLICATION_ID =
DECODE(x_appl_id, 0, PROGRAM_APPLICATION_ID, x_appl_id),
PROGRAM_ID =
DECODE(x_program_id, 0, PROGRAM_ID, x_program_id),
PROGRAM_UPDATE_DATE =
DECODE(x_program_id, 0, PROGRAM_UPDATE_DATE, SYSDATE)
WHERE ORGANIZATION_ID = p_org_id
AND WIP_ENTITY_ID = x_wip_id
AND REPETITIVE_SCHEDULE_ID = p_sched_id;
| x_update_status True if want status to be updated
| x_class_code Class code
*===========================================================================*/
PROCEDURE roll_forward
(p_closed_sched_id IN NUMBER,
p_rollfwd_sched_id IN OUT NOCOPY NUMBER,
p_rollfwd_type IN NUMBER,
p_org_id IN NUMBER,
p_update_status IN BOOLEAN) IS
x_date_reqd DATE;
SELECT wrs.wip_entity_id,
wrs.line_id,
decode(p_rollfwd_type,
WIP_CONSTANTS.ROLL_EC_IMP,
0, wrs.quantity_completed)
FROM wip_repetitive_schedules wrs
WHERE wrs.organization_id = p_org_id
AND wrs.repetitive_schedule_id =
decode(p_rollfwd_type, WIP_CONSTANTS.ROLL_EC_IMP, p_rollfwd_sched_id,
p_closed_sched_id);
SELECT r1.repetitive_schedule_id,
r1.status_type,
r1.daily_production_rate * r1.processing_work_days,
r1.first_unit_start_date
FROM wip_repetitive_schedules r1,
wip_repetitive_schedules r2,
wip_parameters p
WHERE r1.organization_id = p_org_id
AND r2.organization_id = p_org_id
AND p.organization_id = p_org_id
AND r2.repetitive_schedule_id = p_closed_sched_id
AND r1.wip_entity_id = x_wip_id
AND r1.line_id = x_line_id
AND r1.status_type IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD)
AND r1.first_unit_start_date > r2.last_unit_start_date
AND r1.first_unit_start_date <= SYSDATE +
decode(r1.status_type, WIP_CONSTANTS.UNRELEASED,
p.autorelease_days,NUM_DAYS_IN_10_YEARS)
ORDER BY r1.first_unit_start_date;
SELECT wrs.status_type,
wrs.daily_production_rate * wrs.processing_work_days,
wrs.first_unit_start_date
FROM wip_repetitive_schedules wrs
WHERE wrs.organization_id = p_org_id
AND wrs.repetitive_schedule_id = p_rollfwd_sched_id;
SELECT nvl(min(wo.operation_seq_num), 1)
FROM wip_operations wo
WHERE wo.organization_id = p_org_id
AND wo.wip_entity_id = x_wip_id
AND wo.repetitive_schedule_id = p_rollfwd_sched_id;
SELECT WRI.class_code
FROM WIP_REPETITIVE_ITEMS WRI
WHERE WRI.ORGANIZATION_ID = p_org_id
AND WRI.WIP_ENTITY_ID = x_wip_id
AND WRI.LINE_ID = x_line_id;
UPDATE wip_requirement_operations new
SET new.quantity_issued =
(SELECT new.quantity_issued +
nvl(max(wro.quantity_issued -
(x_qty_completed * wro.quantity_per_assembly)), 0)
FROM wip_requirement_operations wro
WHERE wro.organization_id = p_org_id
AND wro.wip_entity_id = x_wip_id
AND wro.repetitive_schedule_id = p_closed_sched_id
AND new.inventory_item_id = wro.inventory_item_id
AND new.operation_seq_num = wro.operation_seq_num
AND wro.quantity_issued >
x_qty_completed * wro.quantity_per_assembly
AND wro.required_quantity >
decode(p_rollfwd_type,
WIP_CONSTANTS.ROLL_EC_IMP, -1, 0)),
new.last_updated_by = x_user_id,
new.last_update_date = SYSDATE,
new.request_id =
decode(x_request_id, 0, new.request_id, x_request_id),
new.program_application_id =
decode(x_appl_id, 0, new.program_application_id, x_appl_id),
new.program_id =
decode(x_program_id, 0, new.program_id, x_program_id),
new.program_update_date =
decode(x_program_id, 0, new.program_update_date, SYSDATE)
WHERE new.organization_id = p_org_id
AND new.wip_entity_id = x_wip_id
AND new.repetitive_schedule_id = p_rollfwd_sched_id;
-- insert into wip_requirement_operations
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,
request_id, program_application_id,
program_id, program_update_date,
component_sequence_id, department_id,
wip_supply_type, date_required,
required_quantity, quantity_issued,
quantity_per_assembly, comments,
supply_subinventory, supply_locator_id,
mrp_net_flag, mps_date_required,
mps_required_quantity,
segment1, segment2, segment3, segment4,
segment5, segment6, segment7, segment8,
segment9, segment10, segment11, segment12,
segment13, segment14, segment15, segment16,
segment17, segment18, segment19, segment20,
attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8,
attribute9, attribute10, attribute11,
attribute12, attribute13, attribute14,
attribute15)
SELECT wro.inventory_item_id, wro.organization_id,
wro.wip_entity_id, wro.operation_seq_num,
p_rollfwd_sched_id, SYSDATE,
x_user_id, SYSDATE,
x_user_id, x_login_id,
DECODE(x_request_id, 0, '', x_request_id),
DECODE(x_appl_id, 0, '', x_appl_id),
DECODE(x_program_id, 0, '', x_program_id),
DECODE(x_program_id, 0, '', SYSDATE),
wro.component_sequence_id, wo.department_id,
wro.wip_supply_type,
nvl(wo.first_unit_start_date, x_date_reqd),
0, wro.quantity_issued -
(x_qty_completed * wro.quantity_per_assembly),
0, wro.comments,
wro.supply_subinventory, wro.supply_locator_id,
wro.mrp_net_flag, wro.mps_date_required,
wro.mps_required_quantity,
wro.segment1, wro.segment2, wro.segment3, wro.segment4,
wro.segment5, wro.segment6, wro.segment7, wro.segment8,
wro.segment9, wro.segment10, wro.segment11, wro.segment12,
wro.segment13, wro.segment14, wro.segment15, wro.segment16,
wro.segment17, wro.segment18, wro.segment19, wro.segment20,
wro.attribute_category, wro.attribute1, wro.attribute2,
wro.attribute3, wro.attribute4, wro.attribute5,
wro.attribute6, wro.attribute7, wro.attribute8,
wro.attribute9, wro.attribute10, wro.attribute11,
wro.attribute12, wro.attribute13, wro.attribute14,
wro.attribute15
FROM wip_requirement_operations wro,
wip_operations wo
WHERE wro.organization_id = p_org_id
AND wo.organization_id (+) = p_org_id
AND wro.wip_entity_id = x_wip_id
AND wo.wip_entity_id (+) = x_wip_id
AND wro.repetitive_schedule_id = p_closed_sched_id
AND wo.repetitive_schedule_id (+) = p_rollfwd_sched_id
AND wro.operation_seq_num = wo.operation_seq_num (+)
AND wro.quantity_issued > x_qty_completed *
wro.quantity_per_assembly
AND wro.required_quantity > decode(p_rollfwd_type,
WIP_CONSTANTS.ROLL_EC_IMP, -1, 0)
AND NOT EXISTS
(SELECT 'does the requirement already exist?'
FROM wip_requirement_operations wro1
WHERE wro1.inventory_item_id = wro.inventory_item_id
AND wro1.operation_seq_num = wro.operation_seq_num
AND wro1.organization_id = p_org_id
AND wro1.wip_entity_id = x_wip_id
AND wro1.repetitive_schedule_id = p_rollfwd_sched_id);
-- update closed schedule in wip_requirement_operations
UPDATE wip_requirement_operations wro
SET wro.quantity_issued = x_qty_completed * wro.quantity_per_assembly,
wro.last_update_date = SYSDATE,
wro.last_updated_by = x_user_id,
wro.request_id = DECODE(x_request_id, 0, wro.request_id,
x_request_id),
wro.program_application_id =
decode(x_appl_id, 0, wro.program_application_id, x_appl_id),
wro.program_id = decode(x_program_id, 0, wro.program_id,
x_program_id),
wro.program_update_date =
decode(x_program_id, 0, wro.program_update_date, SYSDATE)
WHERE wro.organization_id = p_org_id
AND wro.wip_entity_id = x_wip_id
AND wro.repetitive_schedule_id = p_closed_sched_id
AND wro.quantity_issued > x_qty_completed * wro.quantity_per_assembly
AND wro.required_quantity > DECODE(p_rollfwd_type,
WIP_CONSTANTS.ROLL_EC_IMP, -1, 0);
IF (p_update_status) THEN
IF ((p_rollfwd_type = WIP_CONSTANTS.ROLL_COMPLETE) OR
(p_rollfwd_type = WIP_CONSTANTS.ROLL_CANCEL)) THEN
-- set the closed status of closed schedule
UPDATE wip_repetitive_schedules wrs
SET wrs.status_type = x_closed_status_type,
wrs.date_closed =
decode(x_closed_status_type,
WIP_CONSTANTS.COMP_CHRG,
wrs.date_closed, SYSDATE),
wrs.last_updated_by = x_user_id,
wrs.last_update_date = SYSDATE,
wrs.request_id = decode(x_request_id, 0, wrs.request_id,
x_request_id),
wrs.program_application_id =
decode(x_appl_id, 0, wrs.program_application_id,
x_appl_id),
wrs.program_id = decode(x_program_id, 0, wrs.program_id,
x_program_id),
wrs.program_update_date =
decode(x_program_id, 0, wrs.program_update_date,
SYSDATE)
WHERE wrs.organization_id = p_org_id
AND wrs.repetitive_schedule_id = p_closed_sched_id;
UPDATE wip_repetitive_schedules wrs
SET wrs.status_type = WIP_CONSTANTS.RELEASED,
wrs.date_released = SYSDATE,
wrs.last_update_date = SYSDATE,
wrs.last_updated_by = x_user_id,
wrs.request_id = decode(x_request_id, 0, wrs.request_id,
x_request_id),
wrs.program_application_id =
decode(x_appl_id, 0, wrs.program_application_id, x_appl_id),
wrs.program_id = decode(x_program_id, 0, wrs.program_id,
x_program_id),
wrs.program_update_date =
decode(x_program_id, 0, wrs.program_update_date, SYSDATE)
WHERE wrs.organization_id = p_org_id
AND wrs.repetitive_schedule_id = p_rollfwd_sched_id;
wip_change_status.insert_period_balances(x_wip_id,
p_org_id, p_rollfwd_sched_id,
x_line_id, x_class_code);
UPDATE wip_operations wo
SET wo.quantity_in_queue = x_rollfwd_qty,
wo.last_update_date = SYSDATE,
wo.last_updated_by = x_user_id,
wo.request_id = decode(x_request_id, 0, wo.request_id,
x_request_id),
wo.program_application_id =
decode(x_appl_id, 0, wo.program_application_id, x_appl_id),
wo.program_id = decode(x_program_id, 0, wo.program_id,
x_program_id),
wo.program_update_date =
decode(x_program_id, 0, wo.program_update_date, SYSDATE)
WHERE wo.organization_id = p_org_id
AND wo.wip_entity_id = x_wip_id
AND wo.repetitive_schedule_id = p_rollfwd_sched_id
AND wo.operation_seq_num = x_rollfwd_first_op;
p_update_status IN NUMBER,
p_success_flag OUT NOCOPY NUMBER,
p_error_msg OUT NOCOPY VARCHAR2) IS
x_sched NUMBER := p_rollfwd_sched_id;
x_update_status BOOLEAN;
IF p_update_status = 1 THEN
x_update_status := TRUE;
x_update_status := FALSE;
x_update_status);
select wrs.repetitive_schedule_id
from wip_repetitive_schedules wrs
where wrs.organization_id = p_org_id
and wrs.wip_entity_id = p_wip_entity_id
and wrs.line_id = p_line_id
and wrs.status_type in (3,4)
order by wrs.LAST_UNIT_START_DATE asc;
select wrs.repetitive_schedule_id
from wip_repetitive_schedules wrs
where wrs.organization_id = p_org_id
and wrs.wip_entity_id = p_wip_entity_id
and wrs.line_id = p_line_id
and wrs.status_type in (3,4)
order by wrs.LAST_UNIT_START_DATE desc;
select line_id
from wip_repetitive_schedules wrs
where wrs.repetitive_schedule_id = p_rep_sched_id
and wrs.organization_id = p_org_id;