The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE WIP_OPERATIONS
SET QUANTITY_IN_QUEUE =
SCHEDULED_QUANTITY -
(QUANTITY_IN_QUEUE + QUANTITY_RUNNING + QUANTITY_COMPLETED),
LAST_UPDATED_BY = X_user_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = X_login_id
WHERE WIP_ENTITY_ID = P_wip_entity_id
AND ORGANIZATION_ID = P_organization_id
AND REPETITIVE_SCHEDULE_ID IS NULL
AND PREVIOUS_OPERATION_SEQ_NUM IS NULL;
UPDATE WIP_OPERATIONS
SET QUANTITY_IN_QUEUE =
SCHEDULED_QUANTITY -
(QUANTITY_IN_QUEUE + QUANTITY_RUNNING + QUANTITY_COMPLETED),
LAST_UPDATED_BY = X_user_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = X_login_id
WHERE WIP_ENTITY_ID = P_wip_entity_id
AND ORGANIZATION_ID = P_organization_id
AND REPETITIVE_SCHEDULE_ID = P_repetitive_schedule_id
AND PREVIOUS_OPERATION_SEQ_NUM IS NULL;
PROCEDURE INSERT_PERIOD_BALANCES
(P_wip_entity_id NUMBER,
P_organization_id NUMBER,
P_repetitive_schedule_id NUMBER,
P_line_id NUMBER,
P_class_code VARCHAR2,
P_release_date DATE DEFAULT SYSDATE) IS
X_user_id NUMBER := FND_GLOBAL.USER_ID;
INSERT INTO WIP_PERIOD_BALANCES
(ACCT_PERIOD_ID, WIP_ENTITY_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
ORGANIZATION_ID, CLASS_TYPE,
TL_RESOURCE_IN, TL_OVERHEAD_IN,
TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
TL_MATERIAL_OUT, TL_RESOURCE_OUT,
TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
PL_OUTSIDE_PROCESSING_OUT, PL_MATERIAL_OVERHEAD_VAR,
PL_MATERIAL_VAR, PL_OUTSIDE_PROCESSING_VAR,
PL_OVERHEAD_VAR, PL_RESOURCE_VAR,
TL_MATERIAL_VAR, TL_OUTSIDE_PROCESSING_VAR,
TL_OVERHEAD_VAR, TL_RESOURCE_VAR,
TL_MATERIAL_OVERHEAD_OUT, TL_MATERIAL_OVERHEAD_VAR)
SELECT OAP.ACCT_PERIOD_ID, P_wip_entity_id,
SYSDATE, X_user_id,
SYSDATE, X_user_id, X_login_id,
P_organization_id, WC.CLASS_TYPE,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0
FROM ORG_ACCT_PERIODS OAP,
WIP_ACCOUNTING_CLASSES WC
WHERE WC.CLASS_CODE = P_class_code
AND WC.ORGANIZATION_ID = P_organization_id
AND OAP.ORGANIZATION_ID = P_organization_id
AND OAP.SCHEDULE_CLOSE_DATE >=
TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(P_release_date,
P_organization_id))
AND OAP.PERIOD_CLOSE_DATE IS NULL
AND NOT EXISTS
(SELECT 'balance record already there'
FROM WIP_PERIOD_BALANCES WPB
WHERE WPB.WIP_ENTITY_ID = P_wip_entity_id
AND WPB.ACCT_PERIOD_ID = OAP.ACCT_PERIOD_ID
AND WPB.ORGANIZATION_ID = OAP.ORGANIZATION_ID);
INSERT INTO WIP_PERIOD_BALANCES
(ACCT_PERIOD_ID, WIP_ENTITY_ID, REPETITIVE_SCHEDULE_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
ORGANIZATION_ID, CLASS_TYPE,
TL_RESOURCE_IN, TL_OVERHEAD_IN,
TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
TL_MATERIAL_OUT, TL_RESOURCE_OUT,
TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
PL_OUTSIDE_PROCESSING_OUT, PL_MATERIAL_OVERHEAD_VAR,
PL_MATERIAL_VAR, PL_OUTSIDE_PROCESSING_VAR,
PL_OVERHEAD_VAR, PL_RESOURCE_VAR,
TL_MATERIAL_VAR, TL_OUTSIDE_PROCESSING_VAR,
TL_OVERHEAD_VAR, TL_RESOURCE_VAR,
TL_MATERIAL_OVERHEAD_OUT, TL_MATERIAL_OVERHEAD_VAR)
SELECT OAP.ACCT_PERIOD_ID, P_wip_entity_id, P_repetitive_schedule_id,
SYSDATE, X_user_id,
SYSDATE, X_user_id, X_login_id,
P_organization_id, WC.CLASS_TYPE,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0
FROM ORG_ACCT_PERIODS OAP,
WIP_ACCOUNTING_CLASSES WC
WHERE WC.CLASS_CODE = P_class_code
AND WC.ORGANIZATION_ID = P_organization_id
AND OAP.ORGANIZATION_ID = P_organization_id
AND OAP.PERIOD_CLOSE_DATE IS NULL
AND OAP.SCHEDULE_CLOSE_DATE >=
(SELECT NVL(MIN(
TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(DATE_RELEASED,
P_organization_id))),
TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(P_RELEASE_DATE,
P_organization_id)))
FROM WIP_REPETITIVE_SCHEDULES
WHERE WIP_ENTITY_ID = P_wip_entity_id
AND ORGANIZATION_ID = P_organization_id
AND LINE_ID = P_line_id
AND STATUS_TYPE IN (WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG,
WIP_CONSTANTS.HOLD));
/* It is possible that no records can be inserted in discrete
even if there is an open accounting period. This can happen when
reexploding jobs of Status failed load that were defined as released.
It can also happen if you unclose a job that was released in the current
accounting period.
The explicit rollback is needed by the Define Discrete form to roll
back other commit logic that we dont want to be executed again if
the user tries to recommit in the same session.
*/
IF SQL%NOTFOUND THEN
IF P_repetitive_schedule_id IS NOT NULL THEN
FND_MESSAGE.SET_NAME('WIP', 'WIP_NO_ACCT_PERIOD');
SELECT 'x'
FROM ORG_ACCT_PERIODS
WHERE ORGANIZATION_ID = P_Organization_Id
AND TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(
P_RELEASE_DATE,P_Organization_Id))
BETWEEN PERIOD_START_DATE AND SCHEDULE_CLOSE_DATE
AND PERIOD_CLOSE_DATE IS NULL;
END INSERT_PERIOD_BALANCES;
SELECT 'identical routing'
FROM wip_operations wo1,
wip_operations wo2,
wip_repetitive_schedules wrs
WHERE wrs.organization_id = P_organization_id
AND wo1.organization_id = P_organization_id
AND wo2.organization_id = P_organization_id
AND wrs.wip_entity_id = P_wip_entity_id
AND wo1.wip_entity_id = P_wip_entity_id
AND wo2.wip_entity_id = P_wip_entity_id
AND wo1.repetitive_schedule_id = P_repetitive_schedule_id
AND wrs.repetitive_schedule_id = wo2.repetitive_schedule_id
AND wrs.line_id = P_line_id
AND wrs.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG,
WIP_CONSTANTS.HOLD)
AND wo1.operation_seq_num = wo2.operation_seq_num
AND wo1.department_id = wo2.department_id
AND wo1.count_point_type = wo2.count_point_type
AND wo1.backflush_flag = wo2.backflush_flag
HAVING count(*) =
(SELECT count(*)
FROM wip_operations O,
wip_repetitive_schedules S
WHERE O.organization_id = P_organization_id
AND S.organization_id = P_organization_id
AND O.wip_entity_id = P_wip_entity_id
AND S.wip_entity_id = P_wip_entity_id
AND S.line_id = P_line_id
AND S.status_type in (WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD)
AND O.repetitive_schedule_id = P_repetitive_schedule_id)
AND count(*) =
(SELECT count(*)
FROM wip_operations O,
wip_repetitive_schedules S
WHERE O.organization_id = P_organization_id
AND S.organization_id = P_organization_id
AND O.wip_entity_id = P_wip_entity_id
AND S.wip_entity_id = P_wip_entity_id
AND S.line_id = P_line_id
AND S.status_type in (WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD)
AND O.repetitive_schedule_id = S.repetitive_schedule_id);
INSERT_PERIOD_BALANCES (P_wip_entity_id, P_organization_id,
P_repetitive_schedule_id, P_line_id,
P_class_code, P_Release_Date); /* fix for bug 2424987 */
SELECT (nvl(QUANTITY_IN_QUEUE,0) + nvl(QUANTITY_RUNNING,0) + nvl(QUANTITY_COMPLETED,0))
INTO x_tot_op_qty FROM WIP_OPERATIONS
WHERE WIP_ENTITY_ID = P_wip_entity_id
AND ORGANIZATION_ID = P_organization_id
AND REPETITIVE_SCHEDULE_ID IS NULL
AND ROWNUM = 1 --Bug 6052835: EAM Work orders can have multiple start (independant) operations
AND PREVIOUS_OPERATION_SEQ_NUM IS NULL;
SELECT STATUS_TYPE,
CLASS_CODE
FROM WIP_DISCRETE_JOBS
WHERE WIP_ENTITY_ID = P_wip_entity_id
AND ORGANIZATION_ID = P_organization_id
AND STATUS_TYPE IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD,
WIP_CONSTANTS.PEND_SCHED);
UPDATE WIP_DISCRETE_JOBS
SET STATUS_TYPE = WIP_CONSTANTS.HOLD,
DATE_RELEASED = NVL(DATE_RELEASED, SYSDATE),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = X_user_id,
LAST_UPDATE_LOGIN = X_login_id
WHERE WIP_ENTITY_ID = P_wip_entity_id
AND ORGANIZATION_ID = P_organization_id;
UPDATE WIP_REPETITIVE_SCHEDULES
SET STATUS_TYPE = WIP_CONSTANTS.HOLD,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = X_user_id,
LAST_UPDATE_LOGIN = X_login_id
WHERE LINE_ID = P_line_id
AND WIP_ENTITY_ID = P_wip_entity_id
AND ORGANIZATION_ID = P_organization_id
AND STATUS_TYPE IN (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG,
WIP_CONSTANTS.HOLD);
SELECT STATUS_TYPE,
CLASS_CODE
FROM WIP_DISCRETE_JOBS
WHERE WIP_ENTITY_ID = P_wip_entity_id
AND ORGANIZATION_ID = P_organization_id;
UPDATE WIP_DISCRETE_JOBS
SET STATUS_TYPE = WIP_CONSTANTS.RELEASED,
DATE_RELEASED = NVL(DATE_RELEASED, SYSDATE),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = X_user_id,
LAST_UPDATE_LOGIN = X_login_id
WHERE WIP_ENTITY_ID = P_wip_entity_id
AND ORGANIZATION_ID = P_organization_id;