[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT p_wip_entity_id wip_entity_id,
p_wip_entity_name wip_entity_name,
p_fm_op_seq fm_op_seq,
bso1.operation_code fm_op_code,
wo1.department_id fm_dept_id,
bd1.department_code fm_dept_code,
p_fm_step_type fm_step_type,
wo2.operation_seq_num to_op_seq,
bso2.operation_code to_op_code,
wo2.department_id to_dept_id,
bd2.department_code to_dept_code,
p_default_step_type to_step_type,
wdj.primary_item_id item_id,
msik.concatenated_segments item_name,
p_move_qty txn_qty,
msik.primary_uom_code txn_uom,
WIP_CONSTANTS.MOVE_TXN txn_type,
wdj.project_id project_id,
pjm_project.all_proj_idtonum(wdj.project_id) project_number,
wdj.task_id task_id,
pjm_project.all_task_idtonum(wdj.task_id) task_number,
wdj.bom_revision bom_revision
FROM wip_discrete_jobs wdj,
wip_operations wo1,
wip_operations wo2,
mtl_system_items_kfv msik,
bom_standard_operations bso1,
bom_standard_operations bso2,
bom_departments bd1,
bom_departments bd2
WHERE wo1.wip_entity_id = wdj.wip_entity_id
AND wo1.organization_id = wdj.organization_id
AND wo1.operation_seq_num = p_fm_op_seq
AND wo1.standard_operation_id = bso1.standard_operation_id(+)
AND wo1.department_id = bd1.department_id
AND wo2.wip_entity_id = wdj.wip_entity_id
AND wo2.organization_id = wdj.organization_id
AND wo2.operation_seq_num =
(SELECT min(wo3.operation_seq_num)
FROM wip_operations wo3
WHERE wo3.wip_entity_id = p_wip_entity_id
AND wo3.organization_id = p_org_id
AND ((wo1.next_operation_seq_num IS NOT NULL AND
wo3.operation_seq_num > wo1.operation_seq_num) OR
(wo1.next_operation_seq_num IS NULL AND
wo3.operation_seq_num >= wo1.operation_seq_num))
AND wo3.count_point_type = WIP_CONSTANTS.YES_AUTO)
AND wo2.standard_operation_id = bso2.standard_operation_id(+)
AND wo2.department_id = bd2.department_id
AND wdj.primary_item_id = msik.inventory_item_id
AND wdj.organization_id = msik.organization_id
AND wdj.wip_entity_id = p_wip_entity_id
AND wdj.organization_id = p_org_id;
SELECT p_wip_entity_id wip_entity_id,
p_wip_entity_name wip_entity_name,
p_fm_op_seq fm_op_seq,
bso1.operation_code fm_op_code,
wo1.department_id fm_dept_id,
bd1.department_code fm_dept_code,
p_fm_step_type fm_step_type,
wo2.operation_seq_num to_op_seq,
bso2.operation_code to_op_code,
wo2.department_id to_dept_id,
bd2.department_code to_dept_code,
WIP_CONSTANTS.SCRAP to_step_type,
wdj.primary_item_id item_id,
msik.concatenated_segments item_name,
p_scrap_qty txn_qty,
msik.primary_uom_code txn_uom,
WIP_CONSTANTS.MOVE_TXN txn_type,
wdj.project_id project_id,
pjm_project.all_proj_idtonum(wdj.project_id) project_number,
wdj.task_id task_id,
pjm_project.all_task_idtonum(wdj.task_id) task_number,
wdj.bom_revision bom_revision,
p_default_scrap_acct_id scrap_acct_id
FROM wip_discrete_jobs wdj,
wip_operations wo1,
wip_operations wo2,
mtl_system_items_kfv msik,
bom_standard_operations bso1,
bom_standard_operations bso2,
bom_departments bd1,
bom_departments bd2
WHERE wo1.wip_entity_id = wdj.wip_entity_id
AND wo1.organization_id = wdj.organization_id
AND wo1.operation_seq_num = p_fm_op_seq
AND wo1.standard_operation_id = bso1.standard_operation_id(+)
AND wo1.department_id = bd1.department_id
AND wo2.wip_entity_id = wdj.wip_entity_id
AND wo2.organization_id = wdj.organization_id
AND wo2.operation_seq_num =
(SELECT min(wo3.operation_seq_num)
FROM wip_operations wo3
WHERE wo3.wip_entity_id = p_wip_entity_id
AND wo3.organization_id = p_org_id
AND ((wo1.next_operation_seq_num IS NOT NULL AND
wo3.operation_seq_num > wo1.operation_seq_num) OR
(wo1.next_operation_seq_num IS NULL AND
wo3.operation_seq_num >= wo1.operation_seq_num))
AND wo3.count_point_type = WIP_CONSTANTS.YES_AUTO)
AND wo2.standard_operation_id = bso2.standard_operation_id(+)
AND wo2.department_id = bd2.department_id
AND wdj.primary_item_id = msik.inventory_item_id
AND wdj.organization_id = msik.organization_id
AND wdj.wip_entity_id = p_wip_entity_id
AND wdj.organization_id = p_org_id;
SELECT lookup_code step,
meaning
FROM mfg_lookups
WHERE lookup_type='WIP_INTRAOPERATION_STEP';
SELECT meaning
INTO move_txn_meaning
FROM mfg_lookups
WHERE lookup_type = 'WIP_MOVE_TRANSACTION_TYPE'
AND lookup_code = wip_constants.move_txn;
PROCEDURE insert_move_records(p_org_id IN NUMBER,
p_employee_id IN NUMBER,
p_move_table_pvt IN wip_batch_move.move_table_pvt,
p_assy_serial IN VARCHAR2,
x_group_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_log_level NUMBER := fnd_log.g_current_runtime_level;
wip_logger.entryPoint(p_procName => 'wip_batch_move.insert_move_records',
p_params => l_params,
x_returnStatus => l_return_status);
SELECT wip_transactions_s.nextval
INTO x_group_id
FROM dual;
INSERT INTO wip_move_txn_interface(
group_id,
transaction_id,
last_update_date,
last_updated_by,
last_updated_by_name,
creation_date,
created_by,
created_by_name,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
kanban_card_id,
source_code,
source_line_id,
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,
repetitive_schedule_id,
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,
reason_name,
reference,
qa_collection_id,
overcompletion_transaction_qty,
overcompletion_primary_qty,
overcompletion_transaction_id,
employee_id)
VALUES(
x_group_id, -- group_id
wip_transactions_s.nextval, -- transaction_id
SYSDATE, -- last_update_date
fnd_global.user_id, -- last_updated_by
NULL, -- last_updated_by_name
SYSDATE, -- creation_date
fnd_global.user_id, -- created_by
NULL, -- created_by_name
fnd_global.conc_login_id, -- last_update_login
NULL, -- request_id
NULL, -- program_application_id
NULL, -- program_id
NULL, -- program_update_date
NULL, -- kanban_card_id
NULL, -- source_code
NULL, -- source_line_id
WIP_CONSTANTS.MOVE_VAL, -- process_phase
WIP_CONSTANTS.RUNNING, -- process_status
WIP_CONSTANTS.MOVE_TXN, -- transaction_type
p_org_id, -- organization_id
NULL, -- organization_code
p_move_table_pvt(i).wip_entity_id,
p_move_table_pvt(i).wip_entity_name,
WIP_CONSTANTS.DISCRETE, -- entity_type
p_move_table_pvt(i).primary_item_id,
NULL, -- line_id
NULL, -- line_code
NULL, -- repetitive_schedule_id
SYSDATE, -- transaction_date
NULL, -- acct_period_id
p_move_table_pvt(i).fm_operation_seq_num,
p_move_table_pvt(i).fm_operation_code,
p_move_table_pvt(i).fm_department_id,
p_move_table_pvt(i).fm_department_code,
p_move_table_pvt(i).fm_intraoperation_step_type,
p_move_table_pvt(i).to_operation_seq_num,
p_move_table_pvt(i).to_operation_code,
p_move_table_pvt(i).to_department_id,
p_move_table_pvt(i).to_department_code,
p_move_table_pvt(i).to_intraoperation_step_type,
p_move_table_pvt(i).transaction_quantity,
p_move_table_pvt(i).transaction_uom,
NULL, -- primary_quantity
NULL, -- primaty_uom
p_move_table_pvt(i).scrap_account_id,
NULL, -- reason_id
NULL, -- reason_name
NULL, -- reference
NULL, -- qa_collection_id
NULL, -- overcompletion_transaction_qty
NULL, -- overcompletion_primary_qty
NULL, -- overcompletion_transaction_id
p_employee_id)
RETURNING transaction_id INTO l_txn_id;
IF(wma_move.insertSerial(groupID => x_group_id,
transactionID => l_txn_id,
serialNumber => p_assy_serial,
errMessage => l_error_msg) = FALSE) THEN
-- insert statement error out
RAISE fnd_api.g_exc_unexpected_error;
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 wmti.transaction_id,
msn.serial_number,
wmti.creation_date,
wmti.created_by,
wmti.created_by_name,
wmti.last_update_date,
wmti.last_updated_by,
wmti.last_updated_by_name,
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti,
mtl_serial_numbers msn,
wip_discrete_jobs wdj
WHERE wmti.transaction_id = l_txn_id
AND wmti.group_id = x_group_id
AND wmti.organization_id = wdj.organization_id
AND wmti.wip_entity_id = wdj.wip_entity_id
AND wmti.fm_operation_seq_num >= wdj.serialization_start_op
AND msn.wip_entity_id = wmti.wip_entity_id
AND (msn.operation_seq_num IS NULL OR
msn.operation_seq_num = wmti.fm_operation_seq_num)
AND (msn.intraoperation_step_type IS NULL OR
msn.intraoperation_step_type=
wmti.fm_intraoperation_step_type)
AND rownum <= wmti.transaction_quantity
ORDER BY msn.serial_number;
wip_logger.exitPoint(p_procName => 'wip_batch_move.insert_move_records',
p_procReturnStatus => x_return_status,
p_msg => 'procedure complete',
x_returnStatus => l_return_status);
wip_logger.exitPoint(p_procName => 'wip_batch_move.insert_move_records',
p_procReturnStatus => x_return_status,
p_msg => l_error_msg,
x_returnStatus => l_return_status);
END insert_move_records;
error_lists.delete ;
SELECT wtie.error_column,
wtie.error_message
FROM wip_txn_interface_errors wtie,
wip_move_txn_interface wmti
WHERE wtie.transaction_id = wmti.transaction_id
AND wmti.group_id = p_group_id;
SELECT wmti.wip_entity_id wip_id,
wmti.fm_operation_seq_num fm_op,
wmti.to_operation_seq_num to_op
FROM wip_move_txn_interface wmti
WHERE wmti.group_id = p_group_id
ORDER BY wmti.transaction_id;
p_insertAssy => fnd_api.g_true,
p_do_backflush => fnd_api.g_true,
x_returnStatus => x_return_status);
SELECT quantity_in_queue,
quantity_running,
quantity_waiting_to_move,
quantity_in_queue + quantity_running + quantity_waiting_to_move
INTO l_queue_qty,
l_run_qty,
l_to_move_qty,
l_available_qty
FROM wip_operations
WHERE organization_id = p_org_id
AND wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_op_seq;
SELECT nvl(msn.intraoperation_step_type, WIP_CONSTANTS.QUEUE)
INTO l_fm_step_type
FROM mtl_serial_numbers msn,
wip_discrete_jobs wdj
WHERE wdj.organization_id = p_org_id
AND wdj.wip_entity_id = p_wip_entity_id
AND msn.inventory_item_id = wdj.primary_item_id
AND msn.serial_number = p_assy_serial;
SELECT mandatory_scrap_flag
INTO l_require_scrap_acct
FROM wip_parameters
WHERE organization_id = p_org_id;
insert_move_records(p_org_id => p_org_id,
p_employee_id => p_employee_id,
p_move_table_pvt => l_move_table_pvt,
p_assy_serial => p_move_table(i).assy_serial,
x_group_id => l_group_id,
x_return_status => x_return_status);
l_error_msg := 'wip_batch_move.insert_move_records failed';
l_move_table_pvt.delete;