[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
moveRecord MoveTxnRec; -- move record for insertion
IF(insertSerial(groupID => moveRecord.row.group_id,
transactionID => moveRecord.row.transaction_id,
serialNumber => parameters.serial,
errMessage => error) = FALSE) THEN
-- process error
status := -1;
p_insertAssy => FND_API.G_FALSE,
p_do_backflush => FND_API.G_FALSE,
x_returnStatus => l_returnStatus);
SELECT backflush_lot_entry_type
INTO x_lotEntryType
FROM wip_parameters
WHERE organization_id = p_orgID;
SELECT NVL(MAX(operation_seq_num), 1)
INTO l_maxOpSeqNum
FROM wip_operations
WHERE wip_entity_id = p_jobID;
SELECT MIN(operation_seq_num)
INTO l_first_op
FROM wip_operations
WHERE wip_entity_id = p_jobID;
* moveRecord preparing it to be inserted into the interface table.
* This is the most important procedure for move background transaction
* Parameters:
* moveRecord record to be populated. The minimum number of fields to
* execute the move transaction successfully are populated
* parameters move mobile form parameters
* errMessage populated if an error occurrs
* Return:
* boolean flag indicating the successful derivation of necessary values
*/
Function derive(moveRecord IN OUT NOCOPY MoveTxnRec,
parameters IN MoveParam,
errMessage IN OUT NOCOPY VARCHAR2)
return boolean IS
job wma_common.Job;
moveRecord.row.last_update_date := sysdate;
moveRecord.row.last_updated_by := parameters.environment.userID;
moveRecord.row.last_updated_by_name := parameters.environment.userName;
SELECT p_moveQty *
DECODE(
SIGN(p_to_step - WIP_CONSTANTS.SCRAP),
0, DECODE(SIGN(p_fm_step - WIP_CONSTANTS.SCRAP),
0, DECODE(SIGN(p_to_op - p_fm_op),
1,1,
-1,-1),
-1, 1),
-1, DECODE(SIGN(p_fm_step - WIP_CONSTANTS.SCRAP),
0, -1)) txn_qty,
MAX(wop.operation_seq_num) last_op
FROM wip_operations wop
WHERE wop.wip_entity_id = p_jobID
AND ((wop.operation_seq_num = p_fm_op AND
p_fm_step = WIP_CONSTANTS.SCRAP)
OR
(wop.operation_seq_num = p_to_op AND
p_to_step = WIP_CONSTANTS.SCRAP)
);
SELECT MIN(wop.operation_seq_num) first_op
FROM wip_operations wop
WHERE wop.wip_entity_id = p_jobID
AND wop.operation_seq_num >
DECODE(SIGN(p_fm_step - WIP_CONSTANTS.SCRAP),
0, DECODE(SIGN(p_to_step - WIP_CONSTANTS.SCRAP),
0, DECODE(SIGN(p_to_op - p_fm_op),
1, p_fm_op,
p_to_op),
0),
0);
SELECT p_moveQty *
DECODE(
SIGN(p_to_op - p_fm_op),
0, DECODE(SIGN(p_to_step - p_fm_step),
1, 1,
-1),
1, 1,
-1,-1) txn_qty,
MAX(wop.operation_seq_num) last_op
FROM wip_operations wop
WHERE wop.wip_entity_id = p_jobID
AND DECODE(SIGN(DECODE(SIGN(p_to_op - p_fm_op),
-1, p_fm_step,
1, p_to_step,
0, DECODE(SIGN(p_to_step - p_fm_step),
1, p_to_step,
p_fm_step))
- WIP_CONSTANTS.RUN),
1, DECODE(SIGN(p_to_op - p_fm_op), -1, p_fm_op, p_to_op)
+ 0.0000001,
DECODE(SIGN(p_to_op - p_fm_op), -1, p_fm_op, p_to_op))
> wop.operation_seq_num
AND wop.operation_seq_num >= DECODE(SIGN(p_to_op - p_fm_op),
-1, p_to_op,
p_fm_op)
AND (wop.backflush_flag = WIP_CONSTANTS.YES
OR
(wop.operation_seq_num = p_fm_op AND
p_fm_step = WIP_CONSTANTS.SCRAP)
OR
(wop.operation_seq_num = p_to_op AND
p_to_step = WIP_CONSTANTS.SCRAP));
SELECT MIN(wop.operation_seq_num) first_op
FROM wip_operations wop
WHERE wop.wip_entity_id = p_jobID
AND wop.operation_seq_num >
(SELECT NVL(MAX(wop1.operation_seq_num), 0)
FROM wip_operations wop1
WHERE wop1.wip_entity_id = p_jobID
AND DECODE(SIGN(DECODE(SIGN(p_to_op - p_fm_op),
-1, p_to_step,
1, p_fm_step,
0, DECODE(SIGN(p_to_step - p_fm_step),
1, p_fm_step,
p_to_step))
- WIP_CONSTANTS.RUN),
1, DECODE(SIGN(p_to_op - p_fm_op), -1,p_to_op, p_fm_op)
+ 0.0000001,
DECODE(SIGN(p_to_op - p_fm_op), -1, p_to_op,p_fm_op))
> wop1.operation_seq_num
AND (wop1.backflush_flag = WIP_CONSTANTS.YES
OR
(p_to_op > p_fm_op AND
wop1.operation_seq_num = p_fm_op AND
p_fm_step = WIP_CONSTANTS.SCRAP)
OR
(p_to_op < p_fm_op AND
wop1.operation_seq_num = p_to_op AND
p_to_step = WIP_CONSTANTS.SCRAP)));
SELECT MIN(wop.operation_seq_num) first_op,
p_to_op last_op,
p_moveQty txn_qty
FROM wip_operations wop
WHERE wop.wip_entity_id = p_jobID
AND wop.operation_seq_num >
(SELECT NVL(MAX(wop1.operation_seq_num),0)
FROM wip_operations wop1
WHERE wop1.wip_entity_id = p_jobID
AND p_fm_op > p_to_op
AND p_to_step = WIP_CONSTANTS.SCRAP
AND p_to_op >= wop1.operation_seq_num
AND (wop1.backflush_flag = WIP_CONSTANTS.YES));
SELECT count(*)
INTO l_noMoveCount
FROM wip_shop_floor_status_codes wsc,
wip_shop_floor_statuses ws
WHERE wsc.organization_id = p_orgID
AND ws.organization_id = wsc.organization_id
AND ws.wip_entity_id = p_jobID
AND ws.operation_seq_num = p_fmOp
AND ws.intraoperation_step_type = p_fmStep
AND ws.shop_floor_status_code = wsc.shop_floor_status_code
AND wsc.status_move_flag = WIP_CONSTANTS.NO
AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE;
* This function inserts into the MOVE_TXN_INTERFACE table with values
* derived and minimally validated by prior validate call with data stored
* in the moveRecord passed in parameter.
* Parameters:
* moveRecord The MoveTxnRec representing the row to be inserted.
* Return:
* boolean A flag indicating whether update successful or not.
*/
Function put(moveRecord IN MoveTxnRec,
errMessage IN OUT NOCOPY VARCHAR2)
return boolean IS
BEGIN
insert into wip_move_txn_interface
(group_id,
source_code,
transaction_id,
last_update_date,
last_updated_by,
last_updated_by_name,
creation_date, created_by,
created_by_name,
process_phase,
process_status,
transaction_type,
organization_id,
organization_code,
wip_entity_id,
wip_entity_name,
entity_type,
primary_item_id,
line_id,
line_code,
transaction_date,
acct_period_id,
fm_operation_seq_num,
fm_operation_code,
fm_department_id,
fm_department_code,
fm_intraoperation_step_type,
to_operation_seq_num,
to_operation_code,
to_department_id,
to_department_code,
to_intraoperation_step_type,
transaction_quantity,
transaction_uom,
primary_quantity,
primary_uom,
scrap_account_id,
reason_id,
qa_collection_id,
overcompletion_transaction_qty,
overcompletion_primary_qty
)
values (moveRecord.row.group_id,
moveRecord.row.source_code,
moveRecord.row.transaction_id,
moveRecord.row.last_update_date,
moveRecord.row.last_updated_by,
moveRecord.row.last_updated_by_name,
moveRecord.row.creation_date,
moveRecord.row.created_by,
moveRecord.row.created_by_name,
moveRecord.row.process_phase,
moveRecord.row.process_status,
moveRecord.row.transaction_type,
moveRecord.row.organization_id,
moveRecord.row.organization_code,
moveRecord.row.wip_entity_id,
moveRecord.row.wip_entity_name,
moveRecord.row.entity_type,
moveRecord.row.primary_item_id,
moveRecord.row.line_id,
moveRecord.row.line_code,
moveRecord.row.transaction_date,
moveRecord.row.acct_period_id,
moveRecord.row.fm_operation_seq_num,
moveRecord.row.fm_operation_code,
moveRecord.row.fm_department_id,
moveRecord.row.fm_department_code,
moveRecord.row.fm_intraoperation_step_type,
moveRecord.row.to_operation_seq_num,
moveRecord.row.to_operation_code,
moveRecord.row.to_department_id,
moveRecord.row.to_department_code,
moveRecord.row.to_intraoperation_step_type,
moveRecord.row.transaction_quantity,
moveRecord.row.transaction_uom,
moveRecord.row.primary_quantity,
moveRecord.row.primary_uom,
moveRecord.row.scrap_account_id,
moveRecord.row.reason_id,
moveRecord.row.qa_collection_id,
moveRecord.row.overcompletion_transaction_qty,
moveRecord.row.overcompletion_primary_qty
);
* This function inserts into the WIP_SERIAL_MOVE_INTERFACE table with values
* selected from wip_move_txn_interface table
*
* Parameters:
* transactionID The transaction_id in wip_move_txn_interface table
* serialNumber The serial number
* Return:
* boolean A flag indicating whether update successful or not.
*/
Function insertSerial(groupID IN NUMBER,
transactionID IN NUMBER,
serialNumber IN VARCHAR2,
errMessage IN OUT NOCOPY VARCHAR2)
return boolean IS
BEGIN
insert into wip_serial_move_interface
(transaction_id,
assembly_serial_number,
creation_date,
created_by,
created_by_name,
last_update_date,
last_updated_by,
last_updated_by_name,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
select transaction_id,
serialNumber,
creation_date,
created_by,
created_by_name,
last_update_date,
last_updated_by,
last_updated_by_name,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
FROM wip_move_txn_interface wmti
WHERE transaction_id = transactionID
AND group_id = groupID;
fnd_message.set_token ('FUNCTION', 'wma_move.insertSerial');
END insertSerial;