[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT WIP_JOB_SCHEDULE_INTERFACE_S.nextval
FROM DUAL;
SELECT we.wip_entity_id
FROM WIP_ENTITIES we, WIP_JOB_SCHEDULE_INTERFACE wjsi
WHERE wjsi.process_status = 4
AND wjsi.group_id = l_group_id
AND we.wip_entity_id = wjsi.wip_entity_id;
SELECT wip_entity_id
FROM wip_discrete_jobs
WHERE primary_item_id = l_pri_item_id
AND source_code = l_src_code;
UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
l_update_by NUMBER := fnd_global.user_id;
l_update_name VARCHAR2(100);
CURSOR update_cur IS
SELECT user_name
FROM fnd_user_view
WHERE user_id = l_update_by;
OPEN update_cur;
FETCH update_cur INTO l_update_name;
CLOSE update_cur;
INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
PRIMARY_ITEM_ID,
SOURCE_CODE,
LOAD_TYPE,
PROCESS_PHASE,
PROCESS_STATUS,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY_NAME,
CREATED_BY,
FIRST_UNIT_START_DATE,
LAST_UNIT_COMPLETION_DATE,
CLASS_CODE,
ORGANIZATION_ID,
START_QUANTITY,
NET_QUANTITY,
JOB_NAME,
PROJECT_NUMBER,
TASK_NUMBER,
BOM_REFERENCE_ID,
BOM_REVISION,
ROUTING_REFERENCE_ID,
ROUTING_REVISION
)
VALUES
(
p_item_id,
l_source_code,
4,
2,
1,
p_group_id,
SYSDATE,
l_update_by,
l_update_name,
SYSDATE,
l_update_name,
l_update_by,
p_job_start,
p_job_end,
p_jclass,
p_organization_id,
p_quantity,
p_job_mrp_net_quantity,
p_job_name,
nvl(p_project_number,NULL),
nvl(p_task_number,NULL),
p_bill_id,
p_bill_revision,
p_routing_id,
p_routing_revision
);
SELECT wip_transactions_s.nextval
FROM DUAL;
SELECT request_id
FROM FND_CONC_REQ_SUMMARY_V
WHERE parent_request_id = l_request;
SELECT group_id
FROM WIP_MOVE_TXN_INTERFACE
WHERE source_code = l_src_code;
SELECT distribution_account
FROM mtl_generic_dispositions_kfv
WHERE concatenated_segments = p_scrap_alias
AND organization_id = l_organization_id;
SELECT transaction_id
FROM wip_move_transactions
WHERE source_code = l_src_code;
SELECT reason_id
FROM mtl_transaction_reasons_val_v
WHERE reason_name LIKE p_reason_code;
SELECT argument_text
FROM FND_CONC_REQ_SUMMARY_V
WHERE request_id = l_child_request;
UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
l_update_by NUMBER := fnd_global.user_id;
l_update_name VARCHAR2(100);
CURSOR update_cur IS
SELECT user_name
FROM fnd_user_view
WHERE user_id = l_update_by;
OPEN update_cur;
FETCH update_cur INTO l_update_name;
CLOSE update_cur;
INSERT INTO WIP_MOVE_TXN_INTERFACE(
PRIMARY_ITEM_ID,
TRANSACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY_NAME,
ORGANIZATION_CODE,
WIP_ENTITY_NAME,
TRANSACTION_DATE,
SOURCE_CODE,
SCRAP_ACCOUNT_ID,
PROCESS_PHASE,
PROCESS_STATUS,
TRANSACTION_TYPE,
FM_OPERATION_SEQ_NUM,
FM_INTRAOPERATION_STEP_TYPE,
TO_OPERATION_SEQ_NUM,
TO_INTRAOPERATION_STEP_TYPE,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
FM_OPERATION_CODE,
TO_OPERATION_CODE,
QA_COLLECTION_ID,
REASON_ID
)
VALUES(
p_item_id,
p_txn_id,
sysdate,
l_update_name,
sysdate,
l_update_name,
p_organization_code,
p_job_name,
p_txn_date,
'QA WIP SCRAP:'||to_char(p_txn_id),
p_dist_account_id,
1,
1,
1,
p_from_op_seq,
p_from_intra_step,
p_to_op_seq,
p_to_intra_step,
p_quantity,
p_uom,
p_fm_op_code,
p_to_op_code,
p_collection_id,
NVL(p_reason_id, 0));
SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_set_id = set_id;
SELECT transaction_interface_id
FROM mtl_transactions_interface
WHERE transaction_header_id = header_id;
SELECT disposition_id
FROM mtl_generic_dispositions_kfv
WHERE organization_id = l_organization_id
AND concatenated_segments = p_inv_acc_alias;
UPDATE_STATUS(l_plan_id, p_collection_id, p_occurrence);
l_update_by NUMBER := fnd_global.user_id;
SELECT lot_control_code,serial_number_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
INSERT INTO MTL_TRANSACTIONS_INTERFACE (
TRANSACTION_HEADER_ID,
TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_HEADER_ID,
SOURCE_LINE_ID,
PROCESS_FLAG,
TRANSACTION_MODE,
LOCK_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
INVENTORY_ITEM_ID,
REVISION,
ORGANIZATION_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID)
VALUES(
mtl_material_transactions_s.nextval,
mtl_material_transactions_s.nextval,
'QA ACTION: INVSCRAP',
p_collection_id,
p_occurrence,
1,
3,
2,
SYSDATE,
l_update_by,
SYSDATE,
l_update_by,
p_item_id,
p_revision,
p_organization_id,
l_transaction_qty,
p_transaction_uom,
p_transaction_date,
p_subinventory,
p_locator_id,
31,
1,
6,
p_disposition_id)
RETURNING TRANSACTION_HEADER_ID INTO l_header_id;
INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
TRANSACTION_INTERFACE_ID,
SERIAL_TRANSACTION_TEMP_ID,
LOT_NUMBER,
TRANSACTION_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY
) VALUES (
l_interface_id,
l_interface_id,
p_lot_number,
l_transaction_qty,
SYSDATE,
l_update_by,
SYSDATE,
l_update_by
);
INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
TRANSACTION_INTERFACE_ID,
FM_SERIAL_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY
) VALUES (
l_interface_id,
p_serial_number,
SYSDATE,
l_update_by,
SYSDATE,
l_update_by
);
UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
SELECT revision_qty_control_code,
lot_control_code,serial_number_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
l_trohdr_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_trohdr_rec.last_update_date := sysdate;
l_trohdr_rec.last_update_login := FND_GLOBAL.USER_ID;
l_trolin_tbl(l_order_count).last_updated_by := FND_GLOBAL.USER_ID;
l_trolin_tbl(l_order_count).last_update_date := sysdate;
l_trolin_tbl(l_order_count).last_update_login := FND_GLOBAL.LOGIN_ID;
SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_set_id = set_id;
SELECT transaction_interface_id
FROM mtl_transactions_interface
WHERE transaction_header_id = header_id;
SELECT reason_id
FROM mtl_transaction_reasons_val_v
WHERE reason_name LIKE p_reason_code;
UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_set_id = set_id;
SELECT transaction_interface_id
FROM mtl_transactions_interface
WHERE transaction_header_id = header_id;
SELECT reason_id
FROM mtl_transaction_reasons_val_v
WHERE reason_name LIKE p_reason_code;
UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
l_update_by NUMBER := fnd_global.user_id;
SELECT wip_entity_id, entity_type
FROM wip_entities
WHERE wip_entity_name = p_job_name
AND organization_id = p_organization_id;
SELECT lot_control_code,serial_number_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
INSERT INTO MTL_TRANSACTIONS_INTERFACE (
TRANSACTION_HEADER_ID,
TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_HEADER_ID,
SOURCE_LINE_ID,
PROCESS_FLAG,
TRANSACTION_MODE,
LOCK_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
INVENTORY_ITEM_ID,
REVISION,
ORGANIZATION_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
WIP_ENTITY_TYPE,
OPERATION_SEQ_NUM,
REASON_ID)
VALUES (
mtl_material_transactions_s.nextval,
mtl_material_transactions_s.nextval,
p_source_code,
p_collection_id,
p_occurrence,
1,
3,
2,
SYSDATE,
l_update_by,
SYSDATE,
l_update_by,
p_item_id,
p_revision,
p_organization_id,
p_transaction_qty,
p_transaction_uom,
p_transaction_date,
p_subinventory,
p_locator_id,
p_txn_type_id,
p_txn_action_id,
5,
l_entity_id,
l_entity_type,
p_op_seq_num,
p_reason_id)
RETURNING TRANSACTION_HEADER_ID INTO l_header_id;
INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
TRANSACTION_INTERFACE_ID,
SERIAL_TRANSACTION_TEMP_ID,
LOT_NUMBER,
TRANSACTION_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES (
l_interface_id,
l_interface_id,
p_lot_number,
p_transaction_qty,
SYSDATE,
l_update_by,
SYSDATE,
l_update_by);
INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
TRANSACTION_INTERFACE_ID,
FM_SERIAL_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES (
l_interface_id,
p_serial_number,
SYSDATE,
l_update_by,
SYSDATE,
l_update_by);
SELECT wip_transactions_s.nextval
FROM DUAL;
SELECT request_id
FROM FND_CONC_REQ_SUMMARY_V
WHERE parent_request_id = l_request;
SELECT group_id
FROM WIP_MOVE_TXN_INTERFACE
WHERE source_code = l_src_code;
SELECT transaction_id
FROM wip_move_transactions
WHERE source_code = l_src_code;
SELECT reason_id
FROM mtl_transaction_reasons_val_v
WHERE reason_name LIKE p_reason_name;
SELECT argument_text
FROM FND_CONC_REQ_SUMMARY_V
WHERE request_id = l_child_request;
UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
SELECT WIP_JOB_SCHEDULE_INTERFACE_S.nextval
FROM DUAL;
SELECT wip_entity_id
FROM wip_entities
WHERE wip_entity_name = p_job_name
AND organization_id = l_organization_id;
SELECT bsoav.standard_operation_id
FROM bom_standard_operations_all_v bsoav, bom_departments_val_v bdvv
WHERE bsoav.organization_id = l_organization_id
AND bsoav.department_id = bdvv.department_id
AND NVL (bsoav.operation_type, 1) = 1
AND bsoav.operation_code = p_operation_code;
SELECT operation_seq_num
FROM wip_operations
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = p_op_seq_num;
SELECT department_id
FROM bom_departments
WHERE organization_id = l_organization_id
AND nvl(disable_date, sysdate + 2) > sysdate
AND department_code = p_department_code;
SELECT resource_id
FROM bom_resources_val_v
WHERE organization_id = l_organization_id
AND resource_code = p_resource_code;
SELECT resource_seq_num
FROM wip_operation_resources
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = p_op_seq_num
AND resource_seq_num = p_res_seq_num;
SELECT status_type
FROM wip_discrete_jobs
WHERE wip_entity_id = l_wip_entity_id;
UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
l_update_by NUMBER := fnd_global.user_id;
l_update_name VARCHAR2(100);
CURSOR update_cur IS
SELECT user_name
FROM fnd_user_view
WHERE user_id = l_update_by;
SELECT bdp.department_id, nvl(bso.count_point_type, 1) count_point_type,
bso.backflush_flag, nvl(bso.minimum_transfer_quantity, 0) minimum_transfer_quantity
FROM bom_departments bdp, bom_standard_operations bso
WHERE bso.organization_id = p_organization_id
AND bso.line_id is null
AND nvl(bso.operation_type,1) = 1
AND bdp.organization_id = p_organization_id
AND bso.department_id = bdp.department_id
AND nvl(bdp.disable_date, sysdate + 2) > sysdate
AND bso.standard_operation_id = p_operation_id;
OPEN update_cur;
FETCH update_cur INTO l_update_name;
CLOSE update_cur;
INSERT INTO WIP_JOB_SCHEDULE_INTERFACE
(
SOURCE_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY,
CREATED_BY_NAME,
GROUP_ID,
ORGANIZATION_ID,
LOAD_TYPE,
STATUS_TYPE,
JOB_NAME,
WIP_ENTITY_ID,
INTERFACE_ID,
PROCESS_PHASE,
PROCESS_STATUS,
HEADER_ID
)
VALUES
(
'QA_ACTION: ADD_OP',
sysdate,
l_update_by,
l_update_name,
sysdate,
l_update_by,
l_update_name,
p_group_id,
p_organization_id,
3,
p_status_type,
p_job_name,
p_wip_entity_id,
WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
2,
1,
WIP_JOB_SCHEDULE_INTERFACE_S.nextval
) returning header_id into l_header_id;
INSERT INTO WIP_JOB_DTLS_INTERFACE
(
INTERFACE_ID,
GROUP_ID,
WIP_ENTITY_ID,
ORGANIZATION_ID,
OPERATION_SEQ_NUM,
DEPARTMENT_ID,
LOAD_TYPE,
SUBSTITUTION_TYPE,
PROCESS_PHASE,
PROCESS_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PARENT_HEADER_ID,
STANDARD_OPERATION_ID,
FIRST_UNIT_START_DATE,
FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_START_DATE,
LAST_UNIT_COMPLETION_DATE,
MINIMUM_TRANSFER_QUANTITY,
BACKFLUSH_FLAG,
COUNT_POINT_TYPE
)
VALUES
(
WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
p_group_id,
p_wip_entity_id,
p_organization_id,
p_op_seq_num,
l_department_id,
3, -- ( 3- Load Operation) LOAD_TYPE
2, -- ( 2- Add wip_job_details.wip_add) SUBSTITUTION_TYPE
2, -- ( 2- wip_constants.ml_validation) PROCESS_PHASE
1, -- ( 1- wip_constants.pending) PROCESS_STATUS
sysdate,
l_update_by,
sysdate,
l_update_by,
l_header_id,
p_operation_id,
p_start_date,
p_start_date,
p_end_date,
p_end_date,
l_min_txfr_qty,
l_backflush_flag,
l_count_point_type
);
l_update_by NUMBER := fnd_global.user_id;
l_update_name VARCHAR2(100);
CURSOR update_cur IS
SELECT user_name
FROM fnd_user_view
WHERE user_id = l_update_by;
SELECT bsor.resource_seq_num, bsor.resource_id, bsor.activity_id,
bsor.assigned_units, bsor.basis_type, bsor.schedule_flag,
bsor.standard_rate_flag, bsor.usage_rate_or_amount,
bsor.autocharge_type, br.unit_of_measure
FROM bom_std_op_resources bsor, bom_resources br
WHERE bsor.resource_id = br.resource_id
AND bsor.standard_operation_id = p_operation_id;
SELECT res.unit_of_measure, nvl(res.default_basis_type,1) basis_type,
2 "scheduled_flag", res.default_activity_id,
nvl(res.autocharge_type, 1) autocharge_type,
nvl(res.standard_rate_flag,1) standard_rate_flag
FROM bom_resources res, bom_department_resources bdr
WHERE res.organization_id = p_organization_id
AND nvl(res.disable_date, sysdate + 2) > sysdate
AND res.resource_id = bdr.resource_id
AND bdr.department_id = p_department_id
AND res.resource_id = p_resource_id;
OPEN update_cur;
FETCH update_cur INTO l_update_name;
CLOSE update_cur;
INSERT INTO WIP_JOB_SCHEDULE_INTERFACE
(
SOURCE_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY,
CREATED_BY_NAME,
GROUP_ID,
ORGANIZATION_ID,
LOAD_TYPE,
STATUS_TYPE,
JOB_NAME,
WIP_ENTITY_ID,
INTERFACE_ID,
PROCESS_PHASE,
PROCESS_STATUS,
HEADER_ID
)
VALUES
(
'QA_ACTION: ADD_RES',
sysdate,
l_update_by,
l_update_name,
sysdate,
l_update_by,
l_update_name,
p_group_id,
p_organization_id,
3,
p_status_type,
p_job_name,
p_wip_entity_id,
WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
2,
1,
WIP_JOB_SCHEDULE_INTERFACE_S.nextval
) returning header_id into l_header_id;
INSERT INTO WIP_JOB_DTLS_INTERFACE
(
INTERFACE_ID,
GROUP_ID,
ORGANIZATION_ID,
OPERATION_SEQ_NUM,
LOAD_TYPE,
SUBSTITUTION_TYPE,
PROCESS_PHASE,
PROCESS_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PARENT_HEADER_ID,
ACTIVITY_ID,
ASSIGNED_UNITS,
AUTOCHARGE_TYPE,
BASIS_TYPE,
RESOURCE_ID_NEW,
RESOURCE_SEQ_NUM,
SCHEDULED_FLAG,
STANDARD_RATE_FLAG,
USAGE_RATE_OR_AMOUNT,
UOM_CODE
)
VALUES
(
WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
p_group_id,
p_organization_id,
p_op_seq_num,
1, -- ( 1- Load Resource) LOAD_TYPE
2, -- ( 2- Add wip_job_details.wip_add) SUBSTITUTION_TYPE
2, -- ( 2- wip_constants.ml_validation) PROCESS_PHASE
1, -- ( 1- wip_constants.pending) PROCESS_STATUS
sysdate,
l_update_by,
sysdate,
l_update_by,
l_header_id,
l_activity_id,
p_assigned_units,
l_autocharge_type,
l_basis_type,
p_resource_id,
p_res_seq_num,
l_schedule_flag,
l_std_rate_flag,
p_usage_rate,
l_uom
);
INSERT INTO WIP_JOB_DTLS_INTERFACE
(
INTERFACE_ID,
GROUP_ID,
ORGANIZATION_ID,
OPERATION_SEQ_NUM,
LOAD_TYPE,
SUBSTITUTION_TYPE,
PROCESS_PHASE,
PROCESS_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PARENT_HEADER_ID,
ACTIVITY_ID,
ASSIGNED_UNITS,
AUTOCHARGE_TYPE,
BASIS_TYPE,
RESOURCE_ID_NEW,
RESOURCE_SEQ_NUM,
SCHEDULED_FLAG,
STANDARD_RATE_FLAG,
USAGE_RATE_OR_AMOUNT,
UOM_CODE
)
VALUES
(
WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
p_group_id,
p_organization_id,
p_op_seq_num,
1, -- ( 1- Load Resource) LOAD_TYPE
2, -- ( 2- Add wip_job_details.wip_add) SUBSTITUTION_TYPE
2, -- ( 2- wip_constants.ml_validation) PROCESS_PHASE
1, -- ( 1- wip_constants.pending) PROCESS_STATUS
sysdate,
l_update_by,
sysdate,
l_update_by,
l_header_id,
l_activity_id,
l_assigned_units,
l_autocharge_type,
l_basis_type,
l_resource_id,
l_res_seq_num,
l_schedule_flag,
l_std_rate_flag,
l_usage_rate,
l_uom
);
PROCEDURE UPDATE_STATUS(p_plan_id IN NUMBER,
p_collection_id IN NUMBER,
p_occurrence IN NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE qa_results
SET disposition_status = 'PENDING',
txn_header_id = mtl_material_transactions_s.nextval
WHERE collection_id = p_collection_id AND occurrence = p_occurrence
RETURNING txn_header_id INTO l_txnheader_id;
QA_PARENT_CHILD_PKG.insert_history_auto_rec(p_plan_id,l_txnheader_id, 1, 4);
END UPDATE_STATUS;
SELECT mtl_material_transactions_s.nextval
FROM DUAL;
SELECT substr(ltrim(rtrim(meaning)),1,20)
FROM mfg_lookups
WHERE lookup_type = 'SYS_YES_NO'
AND lookup_code = l_lookup_code ;
UPDATE qa_results
SET disposition_status = p_status,
wip_rework_id = NVL(p_job_id, 0),
wjsi_group_id = NVL(p_wjsi_group_id, 0),
mti_transaction_header_id = NVL(p_mti_transaction_header_id, 0),
mti_transaction_interface_id = NVL(p_mti_transaction_interface_id, 0),
mmt_transaction_id = NVL(p_mmt_transaction_id, 0),
wmti_group_id = NVL(p_wmti_group_id, 0),
wmt_transaction_id = NVL(p_wmt_transaction_id, 0),
rti_interface_transaction_id = NVL(p_rti_interface_transaction_id, 0),
concurrent_request_id = NVL(p_request_id, 0),
eco_name = p_eco_name,
txn_header_id = mtl_material_transactions_s.nextval
WHERE collection_id = p_collection_id
AND occurrence = p_occurrence
RETURNING txn_header_id INTO l_txnheader_id;
l_sql_string := 'UPDATE qa_results SET '||
l_move_order_column ||' = :move_order_number, ' ||
l_action_fired_column||' = :action_fired, '||
l_disp_message_column||' = :message '||
' WHERE collection_id = :coll_id AND occurrence = :occ';
l_sql_string := 'UPDATE qa_results SET ' ||
l_action_fired_column||' = :action_fired, '||
l_disp_message_column||' = :message '||
' WHERE collection_id = :coll_id AND occurrence = :occ';
QA_PARENT_CHILD_PKG.insert_history_auto_rec(p_plan_id, l_txnheader_id, 1, 4);
SELECT lookup_code
FROM mfg_lookups
WHERE lookup_type = p_lookup_type
AND upper(meaning) = upper(ltrim(rtrim(p_meaning)));
SELECT plan_id
FROM QA_PLANS
WHERE name = p_plan_name;
SELECT organization_id
FROM mtl_parameters
WHERE organization_code = upper(p_organization_code);
SELECT organization_id
FROM org_organization_definitions
WHERE organization_code = p_organization_code;
SELECT short_code
FROM qa_plan_char_value_lookups
WHERE plan_id = p_plan_id
AND char_id = p_char_id
AND upper(short_code) = upper(p_short_code);
'select code
from
(select ''x'' code, ''x'' description
from dual
where 1 = 2
union
select * from
( '|| sql_string ||
' )) where upper(code) = upper(:1)';