[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT process_enabled_flag INTO v_process_enabled_flag
FROM mtl_parameters
WHERE organization_id = x_inventory_org_id ;
Select decode(length(uom.unit_of_measure), 1, uom.unit_of_measure,
2, uom.unit_of_measure, 3, uom.unit_of_measure,
4, uom.unit_of_measure, uom.uom_code) um_code
into v_um_code
from mtl_units_of_measure uom
WHERE unit_of_measure = x_apps_unit_meas_lookup_code;
Select um_code
Into v_um_code
From sy_uoms_mst
Where unit_of_measure = x_apps_unit_meas_lookup_code;
v_delete_mark NUMBER;
uom_deleted EXCEPTION ;
SELECT uom.unit_of_measure,decode(sign(sysdate-uom.disable_date),1,1,0)
INTO v_unit_of_measure,v_delete_mark
FROM mtl_units_of_measure uom
WHERE decode(length(uom.unit_of_measure), 1, uom.unit_of_measure,
2, uom.unit_of_measure, 3, uom.unit_of_measure,
4, uom.unit_of_measure, uom.uom_code) = x_opm_um_code;
If v_delete_mark = 1 then
raise uom_deleted;
select unit_of_measure,delete_mark
into v_unit_of_measure,v_delete_mark
from sy_uoms_mst
where um_code = x_opm_um_code;
If v_delete_mark = 1 then
raise uom_deleted;
WHEN uom_deleted THEN
FND_MESSAGE.Set_Name('RLM', 'RLM_UOM_INACTIVE');
select lot_id INTO l_lot_id
from ic_lots_mst
where item_id = pitem_id
and lot_no = plot_no
and sublot_no is null;
select lot_id INTO l_lot_id
from ic_lots_mst
where item_id = pitem_id
and lot_no = plot_no
and sublot_no = psublot_no;
select whse_code INTO l_whse_code
from ic_whse_mst
where mtl_organization_id = porg_id;
select location INTO l_location
from ic_loct_mst
where whse_code = l_whse_code
and inventory_location_id = plocator_id;
select ROUND(loct_onhand,6) INTO l_quantity_onhand
from ic_loct_inv
where item_id = pitem_id
and lot_id = l_lot_id
and whse_code = l_whse_code
and location = l_location;
# insert_po_errors
#
# DESCRIPTION
# This procedure inserts records in po_interface_errors table.
# This is an autonomous transaction.
#
#
# MODIFICATION HISTORY
#
#########################################################################*/
PROCEDURE INSERT_PO_ERRORS( p_interface_type IN VARCHAR2
, p_interface_transaction_id IN NUMBER
, p_error_message IN VARCHAR2
, p_processing_date IN DATE
, p_creation_date IN DATE
, p_created_by IN NUMBER
, p_last_update_date IN DATE
, p_last_updated_by IN NUMBER
, p_last_update_login IN NUMBER
, p_request_id IN NUMBER
, p_program_application_id IN NUMBER
, p_program_id IN NUMBER
, p_program_update_date IN DATE
, p_table_name IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
INSERT INTO po_interface_errors
( interface_type
, interface_transaction_id
, error_message
, processing_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, table_name )
VALUES ( p_interface_type
, p_interface_transaction_id
, p_error_message
, p_processing_date
, p_creation_date
, p_created_by
, p_last_update_date
, p_last_updated_by
, p_last_update_login
, p_request_id
, p_program_application_id
, p_program_id
, p_program_update_date
, p_table_name);
END INSERT_PO_ERRORS;
Select shipped_quantity, shipped_quantity2 from wsh_delivery_details
where delivery_detail_id = l_delivery_detail_id;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
select TRANS_ID ,
ITEM_ID ,
LINE_ID ,
CO_CODE ,
ORGN_CODE ,
WHSE_CODE ,
LOT_ID ,
LOCATION ,
DOC_ID ,
DOC_TYPE ,
DOC_LINE ,
LINE_TYPE ,
REASON_CODE ,
CREATION_DATE ,
TRANS_DATE ,
TRANS_QTY ,
TRANS_QTY2 ,
QC_GRADE ,
LOT_STATUS ,
TRANS_STAT ,
TRANS_UM ,
TRANS_UM2 ,
OP_CODE ,
COMPLETED_IND ,
STAGED_IND ,
GL_POSTED_IND ,
EVENT_ID ,
DELETE_MARK ,
TEXT_CODE ,
LAST_UPDATE_DATE ,
CREATED_BY ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
REQUEST_ID ,
REVERSE_ID ,
PICK_SLIP_NUMBER ,
MVT_STAT_STATUS ,
MOVEMENT_ID ,
LINE_DETAIL_ID ,
INVOICED_FLAG
FROM ic_tran_pnd
WHERE doc_type = 'OMSO'
AND line_id = l_oe_line_id
AND line_detail_id = l_oe_line_detail_id
AND COMPLETED_IND = 1;
SELECT primary_quantity,
quantity,
secondary_quantity,
lot_num,
sublot_num,
reason_code
FROM rcv_lots_interface
WHERE interface_transaction_id = p_interface_trx_id ;
SELECT primary_quantity,
transaction_quantity quantity,
secondary_quantity,
lot_number lot_num,
sublot_num,
reason_code
FROM mtl_transaction_lots_temp
WHERE product_transaction_id = p_interface_trx_id
AND product_code = 'RCV' ;
Select QUANTITY, UNIT_OF_MEASURE
From rcv_transactions
where TRANSACTION_ID = l_parent_transaction_id;
Select PRIMARY_QUANTITY
From rcv_lot_transactions
where TRANSACTION_ID = l_parent_transaction_id
and LOT_NUM = v_lot_num
and SUBLOT_NUM = v_sublot_num;
SELECT trans_qty, trans_qty2
FROM IC_TRAN_PND
where doc_type = 'PORC'
and line_id = l_line_id
and delete_mark = 0
and completed_ind = 1
and lot_id = v_lot_id;
SELECT rti.source_document_code,
rti.transaction_type,
rti.to_organization_id,
rti.shipment_header_id,
rti.shipment_line_id,
rti.item_id,
rti.locator_id,
rti.from_locator_id,
rti.transaction_date,
rti.creation_date,
rti.PRIMARY_QUANTITY,
rti.PRIMARY_UNIT_OF_MEASURE,
rti.SECONDARY_QUANTITY,
rti.SECONDARY_UNIT_OF_MEASURE,
rti.created_by ,
rti.interface_source_line_id,
rti.document_shipment_line_num,
rti.auto_transact_code,
rti.transaction_type,
rti.group_id,
rti.created_by,
rti.last_update_date,
rti.last_updated_by,
rti.last_update_login,
rti.request_id,
rti.program_application_id,
rti.program_id,
rti.quantity,
rti.unit_of_measure,
rti.destination_type_code,
rti.validation_flag,
rti.requisition_line_id, -- lot status: bug 3278027
rti.receipt_source_code, -- lot status: bug 3278027
rti.parent_transaction_id, -- Bug 3991705
rsl.comments -- Bug 3936459
INTO l_src_doc_type,
l_trx_type,
l_organization_id,
l_doc_id,
l_line_id,
l_ora_item_id,
l_to_locator_id,
l_from_locator_id,
l_trans_date,
l_creation_date,
l_trans_qty,
l_trans_um,
l_trans_qty2,
l_trans_um2,
l_user_id,
l_oe_line_id,
l_oe_line_detail_id,
l_auto_transact_code,
l_transaction_type,
l_group_id,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_request_id,
l_program_application_id,
l_program_id ,
l_receipt_qty,
l_receipt_unit_of_measure,
l_destination_type_code,
l_validation_flag,
l_req_line_id,
l_receipt_source_code,
l_parent_transaction_id,
l_comments
FROM rcv_transactions_interface rti,
rcv_shipment_lines rsl
WHERE rti.shipment_line_id = rsl.shipment_line_id(+) and
rti.interface_transaction_id = p_interface_trx_id;
FOR rliret in (select * from rcv_lots_interface where interface_transaction_id = p_interface_trx_id)
LOOP
OPEN Cur_rcv_lot_txn(l_parent_transaction_id, rliret.lot_num, rliret.sublot_num);
select rs.line_num
into l_line_num
from rcv_shipment_lines rs, rcv_transactions rt
where rt.interface_transaction_id = p_interface_trx_id
and rs.shipment_header_id = rt.shipment_header_id
and rs.shipment_line_id = rt.shipment_line_id
and rt.transaction_type IN('DELIVER','RETURN TO RECEIVING','CORRECT');
SELECT w.whse_code, w.orgn_code, o.co_code,w.loct_ctl
INTO l_whse_code, l_orgn_code, l_co_code, l_loct_ctl
FROM ic_whse_mst w, sy_orgn_mst o
WHERE mtl_organization_id = l_organization_id
AND w.orgn_code = o.orgn_code;
SELECT oi.item_id , oi.noninv_ind, oi.loct_ctl, oi.lot_ctl, oi.status_ctl
INTO l_item_id, l_non_inv, l_item_loct_ctl, l_item_lot_ctl, l_item_sts_ctl
FROM ic_item_mst oi, mtl_system_items ai
WHERE ai.organization_id = l_organization_id
AND ai.inventory_item_id = l_ora_item_id
AND ai.segment1 = oi.item_no;
select location
into l_location
from ic_loct_mst
where whse_code = l_whse_code
and inventory_location_id = l_locator_id;
select substrb(segment1,1,16)
into l_location
from mtl_item_locations
where inventory_location_id = l_locator_id;
select qc_grade
into l_tran_rec.qc_grade
from ic_lots_mst
where item_id = l_tran_rec.item_id
and lot_id = l_tran_rec.lot_id;
/* Select lot status Check a record in ic_loct_inv for the item,lot,warehouse,location
if no record in ic_loct_inv then get default status from ic_item_mst */
-- lot status: bug 3278027, this is for direct shipment of internal orders
-- Bug 3917381 changed following IF condition from l_item_sts_ctl = 1 to l_item_sts_ctl <> 0
IF l_item_sts_ctl <> 0 THEN
IF ( GML_INTORD_LOT_STS.G_retain_ship_lot_sts = 'Y'
AND l_receipt_source_code = 'INTERNAL ORDER' ) THEN
GML_INTORD_LOT_STS.derive_porc_lot_status( p_item_id => l_item_id
, p_whse_code => l_tran_rec.whse_code
, p_lot_id => l_tran_rec.lot_id
, p_location => l_tran_rec.location
, p_ship_lot_status => cr_intorg_tran_rec.lot_status
, x_rcpt_lot_status => l_rcpt_status
, x_txn_allowed => l_txn_allowed
, x_return_status => l_return_status
, x_msg_data => l_msg_data );
SELECT lot_status
INTO l_rcpt_status
FROM ic_loct_inv
WHERE item_id = l_item_id
AND whse_code = l_tran_rec.whse_code
AND lot_id = l_tran_rec.lot_id
AND location = l_tran_rec.location ;
select lot_status
into l_tran_rec.lot_status
from ic_loct_inv
where item_id = l_item_id
and WHSE_CODE = l_tran_rec.whse_code
and LOT_ID = l_tran_rec.lot_id
and LOCATION = l_tran_rec.location;
select lot_status
into l_tran_rec.lot_status
from ic_item_mst
where item_id = l_item_id;
Select lot_id, qc_grade
into l_tran_rec.lot_id, l_tran_rec.qc_grade
from ic_lots_mst
where lot_no = l_lot_attributes_rec.lot_num
and sublot_no = l_lot_attributes_rec.sublot_num
and item_id = l_item_id;
Select lot_id, qc_grade
into l_tran_rec.lot_id, l_tran_rec.qc_grade
from ic_lots_mst
where lot_no = l_lot_attributes_rec.lot_num
and sublot_no is null
and item_id = l_item_id;
SELECT lot_status
INTO l_rcpt_status
FROM ic_loct_inv
WHERE item_id = l_item_id
AND whse_code = l_tran_rec.whse_code
AND lot_id = l_tran_rec.lot_id
AND location = l_tran_rec.location ;
/* Select lot status Check a record in ic_loct_inv for the item,lot,warehouse,location
if no record in ic_loct_inv then get default status from ic_item_mst */
BEGIN
select lot_status
into l_tran_rec.lot_status
from ic_loct_inv
where item_id = l_item_id
and WHSE_CODE = l_whse_code
and LOT_ID = l_tran_rec.lot_id
and LOCATION = l_location;
select lot_status
into l_tran_rec.lot_status
from ic_item_mst
where item_id = l_item_id;
/*If there were no lots for this transactions then do the following insert */
IF l_lot_exists = 0 THEN
gmi_trans_engine_pub.create_completed_transaction(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_tran_rec => l_tran_rec,
x_tran_row => l_tran_row,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_table_name => 'IC_TRAN_PND');
INSERT_PO_ERRORS( 'RECEIVING',
p_interface_trx_id,
l_error_message,
sysdate,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_request_id,
l_program_application_id,
l_program_id,
sysdate,
'IC_TRAN_PND' );
INSERT_PO_ERRORS( 'RECEIVING',
p_interface_trx_id,
l_error_message,
sysdate,
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_request_id,
l_program_application_id,
l_program_id,
sysdate,
'IC_TRAN_PND' );
Select transaction_type,
secondary_quantity
from rcv_transactions
where parent_transaction_id = v_tran_id
and transaction_type in ('CORRECT','RETURN TO RECEIVING') ;
select transaction_type,
secondary_quantity
from rcv_transactions
where transaction_id = v_transaction_id;
select transaction_id,
transaction_type,
secondary_quantity
from rcv_transactions
where parent_transaction_id = v_p_transaction_id;
# selects a yes to the question asked in RCVGMLCR.pld in when-validate-record
# of Lot Entry block.
# MODIFICATION HISTORY
# 20-JUN-2002 pbamb Created
#
## #######################################################################*/
PROCEDURE CREATE_LOT_SPECIFIC_CONVERSION(
x_item_number IN VARCHAR2,
x_lot_number IN VARCHAR2,
x_sublot_number IN VARCHAR2,
x_from_uom IN VARCHAR2,
x_to_uom IN VARCHAR2,
x_type_factor IN NUMBER,
x_status IN OUT NOCOPY VARCHAR2,
x_data IN OUT NOCOPY VARCHAR2) IS
l_trans_rec Gmigapi.conv_rec_typ;