[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER) IS
x_return_status VARCHAR2(5);
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;
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 => null,
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);
qa_skiplot_utility.insert_error_log (
p_module_name => 'QA_SKIPLOT_RES_ENGINE.LAUNCH_SHIPMENT_ACTION_INT',
p_error_message => 'QA_PO_INSP_ACTION_FAIL',
p_comments => x_msg_data);
/*-- Bug 3225280. Moved the Lot and serial insertion code after RCV
-- insert_inspection API because, we want the interface_transaction_id
-- of the ACCEPT and REJECT transactions to be passed to the WMS APIs
-- as product_transaction_id.
--
-- For this, first we need to find the interface_transaction_id of the
-- inspection record inserted by RCV API. The logic here is to fetch the
-- max(interface_transaction_id) from rti for the parent_transaction_id
-- and group_id combination. Since we are implementing this just after
-- RCV API call, it will fetch the interface_transaction_id of the
-- inspection record just inserted.
-- kabalakr. Wed Oct 29 23:19:22 PST 2003.
--
OPEN int_txn(p_po_group_id, p_transaction_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_SKIPLOT_RES_ENGINE.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_SKIPLOT_RES_ENGINE.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');
'select result_column_name
from qa_plan_chars
where plan_id = :1 and
char_id = 8'
using p_plan_id;
select sum(quantity)
from qa_results
where collection_id = x_coll_id and
plan_id = x_plan_id;
'select displayed_field ' ||
'from po_lookup_codes ' ||
'where lookup_type = ''ERT RESULTS ACTION'' and lookup_code = ''REJECT''';
sql_str := 'select sum(quantity) from qa_results ' ||
'where collection_id = :1 and plan_id = :2 and '||
result_column || ' in (' || in_str || ' )';
select 'FINISHED' from qa_skiplot_rcv_results
where shipment_line_id = x_shl_id and
lot_qty <= transacted_qty + nvl(x_txn_qty, 0) ;
procedure update_plan_states(
p_insp_result in varchar2,
p_criteria_id in number,
p_lot_id in number default null,
p_shipment_line_id in number default null,
p_lot_size in number,
p_lot_plans in lotPlanTable,
p_txn in number,
p_prev_txn_type in varchar2 default null,
p_reinsp_flag in varchar2 default null) is
i number;
select receipt_date
from qa_skiplot_rcv_results
where insp_lot_id = x_insp_lot_id;
select receipt_date
from qa_skiplot_rcv_results
where shipment_line_id = x_shl_id;
select qsa.process_id
from qa_skiplot_association qsa
where criteria_id = x_cid and
trunc(x_receipt_date) between
nvl(trunc(qsa.effective_from), trunc(x_receipt_date)) and
nvl(trunc(qsa.effective_to), trunc(x_receipt_date)) and
x_lotsize between
nvl(qsa.lotsize_from, x_lotsize) and
nvl(qsa.lotsize_to, x_lotsize);
update_plan_state(
p_insp_result => p_insp_result,
p_criteria_id => p_criteria_id,
p_process_id => pid,
p_lot_plan => p_lot_plans(i),
p_txn =>p_txn);
end update_plan_states;
update qa_insp_collections_temp
set skiplot_flag = 'Y'
where collection_id = p_collection_id;
select transaction_type
from rcv_transactions
where transaction_id = x_txn_id;
update_lot_plans(
p_collection_id => p_collection_id,
p_insp_lot_id => p_insp_lot_id,
p_shipment_line_id => p_shipment_line_id,
p_rcv_txn_id => p_rcv_txn_id,
p_inspected_qty => p_inspected_qty,
p_prev_txn_type => prev_txn_type,
p_reinsp_flag => reinsp_flag);
update_skiplot_result(
p_collection_id => p_collection_id,
p_insp_lot_id => p_insp_lot_id,
p_shipment_line_id => p_shipment_line_id,
p_total_txn_qty => p_total_txn_qty,
p_prev_txn_type => prev_txn_type,
p_reinsp_flag => reinsp_flag,
p_criteria_id => criteria_id, -- out parameter
p_result => lot_result, -- out parameter
p_lot_plans => lot_plans); -- out parameter
update_plan_states(
p_insp_result => lot_result,
p_criteria_id => criteria_id,
p_shipment_line_id => p_shipment_line_id,
p_lot_id => p_insp_lot_id,
p_lot_size => p_total_txn_qty,
p_lot_plans => lot_plans,
p_txn =>txn,
p_prev_txn_type => prev_txn_type,
p_reinsp_flag => reinsp_flag);
qa_skiplot_utility.insert_error_log (
p_module_name => 'QA_SKIPLOT_RES_ENGINE.PROCESS_SKIPLOT_RESULT',
p_error_message => 'QA_SKIPLOT_PROCESS_RES_ERROR',
p_comments => SUBSTR (SQLERRM , 1 , 240));
select shipment_line_id
from qa_skiplot_rcv_results
where lpn_id = x_lpn_id;
' select distinct rs.shipment_line_id ' ||
' from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh ' ||
' where rsh.receipt_source_code = ''CUSTOMER'' ' ||
' and rs.oe_order_header_id = :1 ' ||
' and rs.to_organization_id = :2 ' ||
' and rs.item_id = :3 ' ||
' and (rs.item_revision = :4 OR ' ||
' (rs.item_revision is null and :4 is null )) ' ||
' and rs.rcv_transaction_id = rt.transaction_id ' ||
' and rsh.shipment_header_id = rs.shipment_header_id ' ||
' and rt.inspection_status_code = ''NOT INSPECTED'' ' ||
' and rs.supply_type_code = ''RECEIVING'' ' ||
' and rt.transaction_type <> ''UNORDERED'' ' ||
' and rt.routing_header_id = 2 ';
' select distinct rs.shipment_line_id ' ||
' from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh ' ||
' where rsh.receipt_source_code <> ''VENDOR'' ' ||
' and rs.shipment_header_id = :1 ' ||
' and rs.to_organization_id = :2 ' ||
' and rs.item_id = :3 ' ||
' and (rs.item_revision = :4 OR ' ||
' (rs.item_revision is null and :4 is null)) ' ||
' and rs.rcv_transaction_id = rt.transaction_id ' ||
' and rsh.shipment_header_id = rs.shipment_header_id ' ||
' and rt.inspection_status_code = ''NOT INSPECTED'' ' ||
' and rs.supply_type_code = ''RECEIVING'' ' ||
' and rt.transaction_type <> ''UNORDERED'' ' ||
' and rt.routing_header_id = 2) ';
' select distinct rsl.shipment_line_id ' ||
' from rcv_supply rs, ' ||
' rcv_transactions rt, ' ||
' rcv_shipment_headers rsh, ' ||
' rcv_shipment_lines rsl ' ||
' where rsh.receipt_num = :1 ' ||
' and rsh.shipment_header_id = rs.shipment_header_id ' ||
' and rs.supply_type_code = ''RECEIVING'' ' ||
' and rs.rcv_transaction_id = rt.transaction_id ' ||
' and rt.inspection_status_code = ''NOT INSPECTED'' ' ||
' and rt.transaction_type <> ''UNORDERED'' ' ||
' and rt.routing_header_id = 2 ' ||
' and rsh.shipment_header_id = rsl.shipment_header_id ';
' SELECT distinct rs.shipment_line_id ' ||
' FROM rcv_supply rs, rcv_transactions rt, po_headers ph ' ||
' WHERE rs.rcv_transaction_id = rt.transaction_id ' ||
' AND rs.po_header_id = ph.po_header_id ' ||
' AND ph.segment1 = :1 ' ||
' AND rs.to_organization_id = :2 ' ||
' AND rs.item_id = :3 ' ||
' AND (rs.item_revision = :4 OR ' ||
' (rs.item_revision is null AND :4 is null)) ' ||
' AND rt.inspection_status_code = ''NOT INSPECTED'' ';
select qa_collection_id
from rcv_transactions
where transaction_id = x_txn_id;
PROCEDURE UPDATE_LOT_PLANS(
p_collection_id IN NUMBER,
p_insp_lot_id IN NUMBER,
p_rcv_txn_id IN NUMBER,
p_shipment_line_id IN NUMBER,
p_inspected_qty IN NUMBER DEFAULT NULL,
p_prev_txn_type IN VARCHAR2 DEFAULT NULL,
p_reinsp_flag IN VARCHAR2 DEFAULT NULL) IS
cursor lotPlans (x_insp_lot_id number) is
select *
from qa_skiplot_lot_plans
where insp_lot_id = x_insp_lot_id;
select *
from qa_skiplot_lot_plans
where shipment_line_id = x_shl_id;
sql_str := 'update qa_skiplot_lot_plans set ' ||
'plan_insp_status = ''INSPECTED'', ' ||
'plan_insp_result = :1, ' ||
'inspected_qty = :2 ,' ||
'accepted_qty = :3, ' ||
'rejected_qty = :4, ' ||
'collection_id = :5 ' ||
'where shipment_line_id = :6 and ' ||
'plan_id = :7 ';
sql_str := 'update qa_skiplot_lot_plans set ' ||
'plan_insp_status = ''INSPECTED'', ' ||
'plan_insp_result = :1, ' ||
'inspected_qty = :2 ,' ||
'accepted_qty = :3, ' ||
'rejected_qty = :4, ' ||
'collection_id = :5 ' ||
'where insp_lot_id = :6 and ' ||
'plan_id = :7 ';
qa_skiplot_utility.insert_error_log (
p_module_name => 'QA_SKIPLOT_RES_ENGINE.UPDATE_LOT_PLANS',
p_error_message => 'QA_SKIPLOT_RES_UPDATE_PLANS_ERR',
p_comments => SUBSTR (SQLERRM , 1 , 240));
fnd_message.set_name ('QA', 'QA_SKIPLOT_RES_UPDATE_PLANS_ERR');
END UPDATE_LOT_PLANS;
PROCEDURE UPDATE_SKIPLOT_RESULT(
p_collection_id IN NUMBER,
p_insp_lot_id IN NUMBER DEFAULT NULL,
p_shipment_line_id IN NUMBER DEFAULT NULL,
p_total_txn_qty IN NUMBER DEFAULT NULL,
p_prev_txn_type IN VARCHAR2 DEFAULT NULL,
p_reinsp_flag IN VARCHAR2 DEFAULT NULL,
p_criteria_id OUT NOCOPY NUMBER,
p_lot_plans OUT NOCOPY lotPlanTable,
p_result OUT NOCOPY VARCHAR2) IS
c_id NUMBER := null;
select *
from qa_skiplot_lot_plans
where insp_lot_id = x_insp_lot_id;
select *
from qa_skiplot_lot_plans
where shipment_line_id = x_shl_id;
select sampling_flag, lot_result
from qa_insp_collections_temp
where collection_id = x_coll_id;
update qa_skiplot_rcv_results
set inspection_status = lot_status,
inspection_result = decode(p_reinsp_flag, fnd_api.g_true, lot_result,
decode (inspection_result, 'REJECT', 'REJECT', lot_result)),
transacted_qty = decode (p_reinsp_flag, fnd_api.g_true, transacted_qty,
(nvl(transacted_qty, 0) + nvl(p_total_txn_qty, 0))),
last_insp_date = sysdate,
valid_flag = 2,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where shipment_line_id = p_shipment_line_id
returning criteria_id, inspection_result into c_id, lot_result;
update qa_skiplot_rcv_results
set inspection_status = lot_status,
inspection_result = decode(p_reinsp_flag, fnd_api.g_true, lot_result,
decode (inspection_result, 'REJECT', 'REJECT', lot_result)),
transacted_qty = decode(p_reinsp_flag, fnd_api.g_true, transacted_qty,
(nvl(transacted_qty, 0) + nvl(p_total_txn_qty, 0))),
last_insp_date = sysdate,
valid_flag = 2,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where insp_lot_id = p_insp_lot_id
returning criteria_id, inspection_result into c_id, lot_result;
qa_skiplot_utility.insert_error_log (
p_module_name => 'QA_SKIPLOT_RES_ENGINE.UPDATE_SKIPLOT_RESULT',
p_error_message => 'QA_SKIPLOT_RES_UPDATE_RESULT_ERR',
p_comments => SUBSTR (SQLERRM , 1 , 240));
fnd_message.set_name ('QA', 'QA_SKIPLOT_RES_UPDATE_RESULT_ERR');
END UPDATE_SKIPLOT_RESULT;
PROCEDURE UPDATE_PLAN_STATE(
p_insp_result IN VARCHAR2,
p_criteria_id IN NUMBER,
p_process_id IN NUMBER,
p_lot_plan IN lot_plan_rec,
p_txn IN NUMBER,
p_prev_txn_type IN VARCHAR2 DEFAULT NULL,
p_reinsp_flag IN VARCHAR2 DEFAULT NULL) IS
plan_state qa_skiplot_utility.plan_state_rec;
qa_skiplot_utility.update_plan_state(
p_process_plan_id => plan_state.process_plan_id,
p_criteria_id => plan_state.criteria_id,
p_next_lot => plan_state.current_lot + forward_lot,
p_lot_accepted => plan_state.lot_accepted + 1,
p_txn => p_txn);
qa_skiplot_utility.update_plan_state(
p_process_plan_id => plan_state.process_plan_id,
p_criteria_id => plan_state.criteria_id,
p_next_round => plan_state.current_round + 1,
p_next_lot => 1,
p_lot_accepted => 1,
p_txn => p_txn);
qa_skiplot_utility.update_plan_state(
p_process_plan_id => plan_state.process_plan_id,
p_criteria_id => plan_state.criteria_id,
p_next_rule => next_rule,
p_next_round => 1,
p_next_lot => 1,
p_lot_accepted => 1,
p_txn => p_txn);
qa_skiplot_utility.insert_error_log (
p_module_name => 'QA_SKIPLOT_RES_ENGINE.UPDATE_PLAN_STATE',
p_error_message => 'QA_SKIPLOT_RES_UPDATE_STATE_ERR',
p_comments => SUBSTR (SQLERRM , 1 , 240));
fnd_message.set_name ('QA', 'QA_SKIPLOT_RES_UPDATE_STATE_ERR');
END UPDATE_PLAN_STATE;
select skiplot_flag
from qa_insp_collections_temp
where collection_id = x_coll_id;
update qa_insp_collections_temp
set skiplot_flag = decode(p_skiplot_flag, 'T', 'Y','Y', 'Y', 'N')
where collection_id = p_collection_id;
select sum(rejected_qty)
from qa_skiplot_lot_plans
where collection_id = x_coll_id;
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER) IS
x_rejected_qty number;
select count(*) AS insp_plans
from qa_insp_plans_temp
where collection_id = p_collection_id;
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login);
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 => x_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 => null,
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);
qa_skiplot_utility.insert_error_log (
p_module_name => 'QA_SKIPLOT_RES_ENGINE.LAUNCH_SHIPMENT_ACTION',
p_error_message => 'QA_SKIPLOT_REJECTION_ACTION_ERROR',
p_comments => 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 => 'ACCEPT',
p_processing_mode => p_po_txn_processor_mode,
p_quantity => x_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 => null,
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);
qa_skiplot_utility.insert_error_log (
p_module_name => 'QA_SKIPLOT_RES_ENGINE.LAUNCH_SHIPMENT_ACTION',
p_error_message => 'QA_SKIPLOT_ACCEPTANCE_ACTION_ERROR',
p_comments => x_msg_data);
SELECT past.status
FROM po_approved_supplier_list pasl,
po_asl_statuses past,
po_headers ph
WHERE ph.segment1 = c_po_num AND
ph.vendor_id = pasl.vendor_id(+) AND
ph.vendor_site_id = pasl.vendor_site_id(+) AND
pasl.using_organization_id = c_org_id AND
pasl.item_id = c_item_id AND
pasl.asl_status_id = past.status_id(+);
select asl_status_dsp
from po_asl_suppliers_v pasv,
po_headers ph
where ph.segment1 = c_po_num
and ph.vendor_id = pasv.vendor_id(+)
and ph.vendor_site_id = pasv.vendor_site_id(+)
and pasv.using_organization_id = c_org_id
and pasv.item_id = c_item_id;