[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select organization_id, item_id, lpn_id
from qa_insp_collections_dtl_temp
where collection_id = p_collection_id
and rownum = 1;
insert into qa_insp_collections_dtl_temp
(collection_id,
occurrence,
organization_id,
item_id,
lpn_id,
xfr_lpn_id,
lot_number,
serial_number,
insp_result,
insp_qty
)
values
(p_collection_id,
NULL,
l_org_id,
l_item_id,
l_lpn_id,
l_lpn_id,
'DEFERRED',
'DEFERRED',
p_result,
p_quantity
);
l_sql_string := 'UPDATE qa_insp_collections_dtl_temp '||
' SET insp_result = ''REJECT'''||
' WHERE collection_id = :1';
select rls.lot_num, rss.serial_num, decode(rss.serial_num, NULL, rls.quantity, 1)
from rcv_lots_supply rls, rcv_serials_supply rss
where rls.transaction_id = rss.transaction_id (+)
and rls.transaction_id = p_transaction_id
and rls.lot_num = rss.lot_num (+)
UNION
select rls.lot_num, rss.serial_num, decode(rss.serial_num, NULL, rls.quantity, 1)
from rcv_lots_supply rls, rcv_serials_supply rss
where rls.transaction_id (+) = rss.transaction_id
and rss.transaction_id = p_transaction_id
and rls.lot_num (+) = rss.lot_num;
insert into qa_rcv_lot_ser_temp
(rcv_txn_id,
lot_num,
serial_num,
quantity,
valid_flag
)
values
(p_transaction_id,
l_lot_num,
l_serial_num,
l_quantity,
1
);
select quantity
from qa_rcv_lot_ser_temp
where rcv_txn_id = p_transaction_id
and nvl(lot_num, '@@') = nvl(l_lot_number, '@@')
and nvl(serial_num, '@@') = nvl(l_serial_number, '@@');
select lot_number, serial_number, insp_qty
from qa_insp_collections_dtl_temp
where collection_id = p_collection_id;
update qa_rcv_lot_ser_temp
set valid_flag = 2
where rcv_txn_id = p_transaction_id
and nvl(lot_num, '@@') = nvl(l_lot_number, '@@')
and nvl(serial_num, '@@') = nvl(l_serial_number, '@@');
update qa_rcv_lot_ser_temp
set quantity = l_qty_rem
where rcv_txn_id = p_transaction_id
and nvl(lot_num, '@@') = nvl(l_lot_number, '@@')
and nvl(serial_num, '@@') = nvl(l_serial_number, '@@');
PROCEDURE insert_lot_serial_txn(p_parent_txn_id NUMBER,
p_transaction_id NUMBER,
p_item_id NUMBER,
p_org_id NUMBER,
p_uom VARCHAR2) IS
x_return_status VARCHAR2(5);
select primary_unit_of_measure
from mtl_system_items_b
where inventory_item_id = p_item_id
and organization_id = p_org_id;
select lot_num, serial_num, quantity
from qa_rcv_lot_ser_temp
where rcv_txn_id = p_parent_txn_id
and valid_flag = 1;
INV_RCV_INTEGRATION_APIS.INSERT_MTLI
(p_api_version => 1.0,
p_init_msg_lst => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_transaction_interface_id => l_int_txn_id,
p_transaction_quantity => l_qty_rem,
p_primary_quantity => l_primary_qty,
p_organization_id => p_org_id,
p_inventory_item_id => p_item_id,
p_lot_number => l_lot_number,
p_expiration_date => NULL,
p_status_id => NULL,
x_serial_transaction_temp_id => l_ser_txn_id,
p_product_code => 'RCV',
p_product_transaction_id => l_rti_txn_id);
qa_skiplot_utility.insert_error_log (
p_module_name => 'QA_SAMPLING_PKG.LAUNCH_SHIPMENT_ACTION_INT',
p_error_message => 'QA_WMS_LOT_INSERT_FAIL',
p_comments => x_msg_data);
fnd_message.set_name ('QA', 'QA_WMS_LOT_INSERT_FAIL');
INV_RCV_INTEGRATION_APIS.INSERT_MSNI
(p_api_version => 1.0,
p_init_msg_lst => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_transaction_interface_id => l_int_txn_id,
p_fm_serial_number => l_serial_number,
p_to_serial_number => l_serial_number,
p_organization_id => p_org_id,
p_inventory_item_id => p_item_id,
p_status_id => NULL,
p_product_code => 'RCV',
p_product_transaction_id => l_rti_txn_id);
qa_skiplot_utility.insert_error_log (
p_module_name => 'QA_SAMPLING_PKG.LAUNCH_SHIPMENT_ACTION_INT',
p_error_message => 'QA_WMS_SER_INSERT_FAIL',
p_comments => x_msg_data);
fnd_message.set_name ('QA', 'QA_WMS_SER_INSERT_FAIL');
END insert_lot_serial_txn;
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_lot_size IN NUMBER,
p_lot_result IN VARCHAR2) IS
l_lot_qty_not_insp NUMBER;
select lot_size, lot_result, total_rejected_qty
from qa_insp_collections_temp
where collection_id = p_collection_id;
select sum(insp_qty) AS total_insp_qty
from qa_insp_collections_dtl_temp
where collection_id = p_collection_id;
select organization_id, item_id, lpn_id, xfr_lpn_id,
lot_number, serial_number, insp_result, sum(insp_qty)
from qa_insp_collections_dtl_temp
where collection_id = p_collection_id
group by organization_id, item_id, lpn_id, xfr_lpn_id,
lot_number, serial_number, insp_result;
select primary_unit_of_measure
from mtl_system_items_b
where inventory_item_id = l_item
and organization_id = l_org;
SELECT max(interface_transaction_id)
FROM rcv_transactions_interface
WHERE group_id = grp_id
AND parent_transaction_id = txn_id;
SELECT vendor_lot_num
FROM rcv_transactions
WHERE transaction_id = txn_id;
RCV_INSPECTION_GRP.INSERT_INSPECTION
(p_api_version => 1.1,
p_init_msg_list => NULL,
p_commit => 'F',
p_validation_level => NULL,
p_created_by => p_created_by,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_employee_id => p_employee_id,
p_group_id => p_po_group_id,
p_transaction_id => p_transaction_id,
p_transaction_type => l_insp_result,
p_processing_mode => p_po_txn_processor_mode,
p_quantity => l_insp_qty,
p_uom => p_uom,
p_quality_code => null,
p_transaction_date => p_transaction_date,
p_comments => null,
p_reason_id => null,
p_vendor_lot => l_vendor_lot_num, -- Bug 8806035
p_lpn_id => l_lpn_id,
p_transfer_lpn_id => l_xfr_lpn_id,
p_qa_collection_id => p_collection_id,
p_return_status => x_return_status,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data,
p_subinventory => L_RTI_SUB_CODE,
p_locator_id => L_RTI_LOC_ID,
p_from_subinventory => L_RTI_SUB_CODE,
p_from_locator_id => L_RTI_LOC_ID);
INV_RCV_INTEGRATION_APIS.INSERT_MTLI
(p_api_version => 1.0,
p_init_msg_lst => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_transaction_interface_id => l_int_txn_id,
p_transaction_quantity => l_insp_qty,
p_primary_quantity => l_primary_qty,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_lot_number => l_lot_number,
p_expiration_date => NULL,
p_status_id => NULL,
x_serial_transaction_temp_id => l_ser_txn_id,
p_product_code => 'RCV',
p_product_transaction_id => l_rti_int_txn_id);
qa_skiplot_utility.insert_error_log (
p_module_name => 'QA_SAMPLING_PKG.LAUNCH_SHIPMENT_ACTION_INT',
p_error_message => 'QA_WMS_LOT_INSERT_FAIL',
p_comments => x_msg_data);
fnd_message.set_name ('QA', 'QA_WMS_LOT_INSERT_FAIL');
INV_RCV_INTEGRATION_APIS.INSERT_MSNI
(p_api_version => 1.0,
p_init_msg_lst => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_transaction_interface_id => l_int_txn_id,
p_fm_serial_number => l_serial_number,
p_to_serial_number => l_serial_number,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_status_id => NULL,
p_product_code => 'RCV',
p_product_transaction_id => l_rti_int_txn_id);
qa_skiplot_utility.insert_error_log (
p_module_name => 'QA_SAMPLING_PKG.LAUNCH_SHIPMENT_ACTION_INT',
p_error_message => 'QA_WMS_SER_INSERT_FAIL',
p_comments => x_msg_data);
fnd_message.set_name ('QA', 'QA_WMS_SER_INSERT_FAIL');
insert_lot_serial_txn(p_transaction_id,
l_rti_int_txn_id,
l_item_id,
l_org_id,
p_uom);
procedure update_qa_insp_plans_temp(p_sample_size number,
p_c_num number,
p_rej_num number,
p_aql number,
p_coll_id number,
p_plan_id number)
is
begin
update qa_insp_plans_temp
set sample_size = p_sample_size,
c_number = p_c_num,
rejection_number = p_rej_num,
aql = p_aql
where collection_id = p_coll_id
and plan_id = p_plan_id;
end ; -- update_qa_insp_plans_temp
select result_column_name
from qa_plan_chars
where plan_id = p_plan_id
and char_id = 8; --8 is Inspection Result
select vendor_name,
vendor_site_code,
item,
item_revision,
category_desc,
project_number,
task_number,
wf_role_name
from qa_sampling_rcv_criteria_v
where criteria_id = x_criteria_id;
select name
from qa_chars
where char_id in (10, 11, 13, 26, 121, 122, 130)
order by char_id;
select plan_id
from qa_insp_plans_temp qipt
where collection_id = p_collection_id;
select
sampling_plan_id, criteria_id
from
qa_sampling_rcv_criteria_val_v qsrc
where
qsrc.vendor_id in (-1, x_vendor_id) AND
qsrc.vendor_site_id in (-1, x_vendor_site_id) AND
qsrc.item_id in (-1, x_item_id)AND
qsrc.item_revision in ('-1', x_item_revision) AND
qsrc.item_category_id in (-1, x_item_category_id) AND
qsrc.project_id in (-1, x_project_id) AND
qsrc.task_id in (-1, x_task_id) AND
qsrc.collection_plan_id in (-1, x_collection_plan_id) AND
qsrc.organization_id = x_organization_id AND
trunc(sysdate) BETWEEN
nvl(trunc(qsrc.effective_from), trunc(sysdate)) AND
nvl(trunc(qsrc.effective_to), trunc(sysdate))
ORDER BY
task_id desc, project_id desc ,
vendor_site_id desc, vendor_id desc, item_revision desc,
item_id desc, item_category_id desc, collection_plan_id desc,
last_update_date desc;
SELECT Nvl(micv.category_id,-1) item_category_id
FROM MTL_ITEM_CATEGORIES_V MICV,
QA_SAMPLING_PLANS qsp,
qa_sampling_rcv_criteria_val_v qsrcvv
WHERE micv.inventory_item_id= x_item_id AND
micv.organization_id= x_organization_id AND
qsrcvv.item_category_id = micv.category_id AND
qsp.sampling_plan_id = qsrcvv.sampling_plan_id AND
micv.category_id <> x_item_category_id
ORDER BY qsp.sampling_plan_code asc;
update qa_insp_plans_temp
set sampling_plan_id = l_sampling_plan_id,
sampling_criteria_id = l_criteria_id
where collection_id = p_collection_id
and plan_id = plan_rec.plan_id;
update qa_insp_collections_temp
set sampling_flag = 'Y'
where collection_id = p_collection_id;
update qa_insp_plans_temp
set sampling_plan_id = -1,
sampling_criteria_id = -1
where collection_id = p_collection_id
and plan_id = plan_rec.plan_id;
select sampling_std_code, AQL, insp_level_code
from QA_SAMPLING_PLANS qsp
where sampling_plan_id = p_sampling_plan_id;
select sample_size
from QA_SAMPLING_CUSTOM_RULES qscr
where qscr.sampling_plan_id = p_sampling_plan_id
and p_lot_size between qscr.min_lot_size
and nvl(qscr.max_lot_size,p_lot_size);
select sample_size, c_number, rejection_number
from qa_sampling_std_rules qssr
where qssr.sampling_std_code = x_std_code
and qssr.aql = x_aql
and qssr.table_seq = x_table_seq
and qssr.lot_size_code = x_lot_code;
select lot_size_code
from qa_sampling_insp_level qsil
where qsil.insp_level_code = x_insp_level
and p_lot_size between qsil.min_lot_size
and nvl(qsil.max_lot_size, p_lot_size);
update_qa_insp_plans_temp(l_sample_size, l_c_num, l_rej_num,
l_aql, p_collection_id,
p_collection_plan_id);
update qa_insp_plans_temp
set sample_size = l_sample_size,
c_number = l_c_num,
rejection_number = l_rej_num,
aql = l_aql
where collection_id = p_collection_id
and plan_id = p_collection_plan_id;
update_qa_insp_plans_temp(l_sample_size, l_c_num,
l_rej_num, null, p_collection_id,
p_collection_plan_id);
update qa_insp_plans_temp
set sample_size = l_sample_size,
c_number = l_c_num,
rejection_number = l_rej_num,
aql = null -- no aql for custom sampling
where collection_id = p_collection_id
and plan_id = p_collection_plan_id;
update_qa_insp_plans_temp(l_sample_size, l_c_num,
l_rej_num, l_aql, p_collection_id,
p_collection_plan_id);
update qa_insp_plans_temp
set sample_size = l_sample_size,
c_number = l_c_num,
rejection_number = l_rej_num,
aql = l_aql
where collection_id = p_collection_id
and plan_id = p_collection_plan_id;
select sampling_flag
from qa_insp_collections_temp
where collection_id = p_collection_id;
select c_number, rejection_number,
sampling_plan_id, sampling_criteria_id
from qa_insp_plans_temp
where collection_id = p_collection_id
and plan_id = p_coll_plan_id;
select sampling_std_code
from qa_sampling_plans
where sampling_plan_id = x_sampling_plan_id;
'select displayed_field ' ||
' from po_lookup_codes ' ||
' where lookup_type = ''ERT RESULTS ACTION''' ||
' and lookup_code = ''REJECT''';
'select meaning ' ||
'from fnd_lookup_values lv ' ||
'where view_application_id = 201 and ' ||
-- following line commented out based upon code review feedback
-- anagarwa Thu Oct 25 10:52:48 PDT 2001
-- 'security_group_id = fnd_global.lookup_security_group ' ||
'(lv.lookup_type,lv.view_application_id) and ' ||
'lookup_type = ''ERT RESULTS ACTION'' and lookup_code = ''REJECT''';
sql_str := 'select sum(quantity) from qa_results where exists ' ||
'(select ' || result_column || ' from qa_results ' ||
'where collection_id = :1 and plan_id = :2 and '||
result_column || ' in (' || in_str || ' ))';
sql_str := 'select sum(quantity) from qa_results ' ||
'where collection_id = :1 and plan_id = :2 and ' ||
result_column || ' in (' || in_str || ' )';
sql_str := 'select sum(quantity) from qa_results ' ||
'where collection_id = :1 and plan_id = :2 and ';
update qa_insp_plans_temp
set plan_rejected_qty = reject_qty
where collection_id = p_collection_id
and plan_id = p_coll_plan_id;
update qa_insp_plans_temp
set plan_insp_result = result
where collection_id = p_collection_id
and plan_id = p_coll_plan_id;
select sampling_flag
from qa_insp_collections_temp
where collection_id = p_collection_id;
select count(*) AS rejected_plans
from qa_insp_plans_temp
where collection_id = p_collection_id
and plan_insp_result = 'REJECT';
select sum(plan_rejected_qty) AS total_rej_qty
from qa_insp_plans_temp
where collection_id = p_collection_id;
update qa_insp_collections_temp
set lot_result = result
where collection_id = p_collection_id;
update qa_insp_collections_temp
set total_rejected_qty = total_number_rej
where collection_id = p_collection_id;
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER)
IS
l_lot_size number;
select lot_size, lot_result,total_rejected_qty
from qa_insp_collections_temp
where collection_id = p_collection_id;
select count(*) AS insp_plans
from qa_insp_plans_temp
where collection_id = p_collection_id;
SELECT vendor_lot_num
FROM rcv_transactions
WHERE transaction_id = txn_id;
SELECT Count(lpn_id) INTO l_license_plate_no_id
FROM qa_results
WHERE collection_id = p_collection_id;
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_lot_size => l_lot_size,
p_lot_result => l_lot_result);
RCV_INSPECTION_GRP.INSERT_INSPECTION(
p_api_version => 1.1,
p_init_msg_list => NULL,
p_commit => 'F',
p_validation_level => NULL,
p_created_by => p_created_by,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_employee_id => p_employee_id,
p_group_id => p_po_group_id,
p_transaction_id => p_transaction_id,
p_transaction_type => l_lot_result,
p_processing_mode => p_po_txn_processor_mode,
p_quantity => l_accepted_qty,
p_uom => p_uom,
p_quality_code => null,
p_transaction_date => p_transaction_date,
p_comments => null,
p_reason_id => null,
p_vendor_lot => l_vendor_lot_num, -- Bug 8806035
p_lpn_id => null,
p_transfer_lpn_id => null,
p_qa_collection_id => p_collection_id,
p_return_status => x_return_status,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data);
RCV_INSPECTION_GRP.INSERT_INSPECTION(
p_api_version => 1.1,
p_init_msg_list => NULL,
p_commit => 'F',
p_validation_level => NULL,
p_created_by => p_created_by,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_employee_id => p_employee_id,
p_group_id => p_po_group_id,
p_transaction_id => p_transaction_id,
p_transaction_type => 'REJECT',
p_processing_mode => p_po_txn_processor_mode,
p_quantity => l_rejected_qty,
p_uom => p_uom,
p_quality_code => null,
p_transaction_date => p_transaction_date,
p_comments => null,
p_reason_id => null,
p_vendor_lot => l_vendor_lot_num, -- Bug 8806035
p_lpn_id => null,
p_transfer_lpn_id => null,
p_qa_collection_id => p_collection_id,
p_return_status => x_return_status,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data);
RCV_INSPECTION_GRP.INSERT_INSPECTION(
p_api_version => 1.1,
p_init_msg_list => NULL,
p_commit => 'F',
p_validation_level => NULL,
p_created_by => p_created_by,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_employee_id => p_employee_id,
p_group_id => p_po_group_id,
p_transaction_id => p_transaction_id,
p_transaction_type => l_lot_result,
p_processing_mode => p_po_txn_processor_mode,
p_quantity => l_lot_size,
p_uom => p_uom,
p_quality_code => null,
p_transaction_date => p_transaction_date,
p_comments => null,
p_reason_id => null,
p_vendor_lot => l_vendor_lot_num, -- Bug 8806035
p_lpn_id => null,
p_transfer_lpn_id => null,
p_qa_collection_id => p_collection_id,
p_return_status => x_return_status,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data);
select sampling_flag
from qa_insp_collections_temp
where collection_id = p_collection_id;
select name
from qa_plans
where plan_id = p_coll_plan_id;
SELECT total_rejected_qty, lot_size
FROM qa_insp_collections_temp
WHERE collection_id = p_collection_id;
select plan_id
from qa_insp_plans_temp
where collection_id = p_collection_id;
' select displayed_field' ||
' from po_lookup_codes' ||
' where lookup_type = ''ERT RESULTS ACTION''' ||
' and lookup_code = ''REJECT''';
' select displayed_field' ||
' from po_lookup_codes ' ||
' where lookup_type = ''ERT RESULTS ACTION''' ||
' and lookup_code = ''ACCEPT''';
l_sql_rej := 'select occurrence, organization_id, item_id, lpn_id, xfr_lpn_id,'||
' lot_number, serial_number, quantity from qa_results' ||
' where collection_id = :1 and plan_id = :2 and ' ||
l_result_column || ' in (' || in_str_reject || ' )';
l_sql_acc := 'select occurrence, organization_id, item_id, lpn_id, xfr_lpn_id,'||
' lot_number, serial_number, quantity from qa_results' ||
' where collection_id = :1 and plan_id = :2 and ' ||
l_result_column || ' in (' || in_str_accept || ' )';
insert into qa_insp_collections_dtl_temp
(collection_id,
occurrence,
organization_id,
item_id,
lpn_id,
xfr_lpn_id,
lot_number,
serial_number,
insp_result,
insp_qty
)
values
(p_collection_id,
l_occurrence,
l_org_id,
l_item_id,
l_lpn_id,
l_xfr_lpn_id,
l_lot_num,
l_serial_num,
'ACCEPT',
l_qty
);
insert into qa_insp_collections_dtl_temp
(collection_id,
occurrence,
organization_id,
item_id,
lpn_id,
xfr_lpn_id,
lot_number,
serial_number,
insp_result,
insp_qty
)
values
(p_collection_id,
l_occurrence,
l_org_id,
l_item_id,
l_lpn_id,
l_xfr_lpn_id,
l_lot_num,
l_serial_num,
'REJECT',
l_qty
);