[Home] [Help]
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;
wip_logger.entryPoint(p_procName => 'WIP_CHANGE_STATUS.INSERT_PERIOD_BALANCES',
p_params => l_params,
x_returnStatus => x_returnStatus);
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);
wip_logger.log(p_msg => SQL%ROWCOUNT ||' of rows inserted into WPB ',
x_returnStatus => x_returnStatus);
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));
wip_logger.log(p_msg => SQL%ROWCOUNT ||' of rows inserted into WPB ',
x_returnStatus => x_returnStatus);
/* 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
if (l_logLevel <= wip_constants.trace_logging) then
wip_logger.log(p_msg => 'WIP_NO_ACCT_PERIOD exception' ,
x_returnStatus => x_returnStatus);
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;
wip_logger.exitPoint(p_procName => 'WIP_CHANGE_STATUS.INSERT_PERIOD_BALANCES',
p_procReturnStatus => 'S',
p_msg => 'Finished!',
x_returnStatus => x_returnStatus); --discard logging return status
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);
/* Bug 13003859(FP of bug#12849554) when changing from on hold to release/complete, we want INSERT_PERIOD_BALANCES only inserting wpb for current acct period only to prevent backdated txn*/
IF(P_new_status_type IN (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG) AND P_old_status_type = WIP_CONSTANTS.HOLD) THEN
l_release_date := sysdate;
INSERT_PERIOD_BALANCES (P_wip_entity_id, P_organization_id,
P_repetitive_schedule_id, P_line_id,
P_class_code, l_Release_Date); /* fix for bug 2424987 *//* Bug 13003859 (FP of bug#12849554) */
wip_logger.log(p_msg => 'INSERT_PERIOD_BALANCES successfully',
x_returnStatus => x_returnStatus);
/*Bug 13004887(FP of 11935424 and 13003859) we only want to call INSERT_PERIOD_BALANCES when change EAM work orders from hold to release*/
IF(P_old_status_type <> WIP_CONSTANTS.HOLD) THEN
/* For Bug 5859224: load_queue API would be called only if the sum of quantity_in_queue,quantity_running
and quantity_completed of first operation is zero */
BEGIN
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 nvl(REPETITIVE_SCHEDULE_ID, 0) = nvl(p_repetitive_schedule_id, 0) --Bug 8670946
AND ROWNUM = 1 --Bug 6052835: EAM Work orders can have multiple start (independant) operations
AND PREVIOUS_OPERATION_SEQ_NUM IS NULL
for update nowait; /*Fix Bug 8977276 (FP 8946106)*/
If the sql SELECT (nvl(QUANTITY_IN_QUEUE,0) + nvl(QUANTITY_RUNNING,0) + nvl(QUANTITY_COMPLETED,0))
INTO x_tot_op_qty FROM WIP_OPERATIONS does not throw exception it implies that there is a routing exists
in WIP_OPERATIONS and so the P_routing_exists flag should be 1(WIP_CONSTANTS.YES). If it throws exception,
LOAD_QUEUE will handle the P_routing_exists flag
*/
P_routing_exists := WIP_CONSTANTS.YES;
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;