[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE rcv_parameters
SET next_receipt_num = next_receipt_num + 1
WHERE organization_id = p_organization_id
RETURNING next_receipt_num INTO x_receipt_num;
SELECT 1
INTO l_receipt_exists
FROM rcv_shipment_headers rsh
WHERE receipt_num = x_receipt_num
AND ship_to_org_id = p_organization_id;
SELECT HR.EMPLOYEE_ID,
HR.FULL_NAME,
NVL(HR.LOCATION_ID,0)
INTO X_emp_id,
X_emp_name,
X_location_id
FROM FND_USER FND, PER_EMPLOYEES_CURRENT_X HR
WHERE FND.USER_ID = X_user_id
AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID
AND ROWNUM = 1;
/* if no rows selected
then user is not an employee
else user is an employee */
emp_flag := TRUE;
select hr.location_code
into x_location_code
from hr_locations hr,
financials_system_parameters fsp,
hr_organization_information hoi
where hr.location_id = x_location_id
and hr.inventory_organization_id = hoi.organization_id
and to_char(fsp.set_of_books_id) = hoi.org_information1
AND ROWNUM = 1;
SELECT 'Y'
INTO X_buyer_code
FROM PO_AGENTS
WHERE agent_id = X_emp_id
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1)
AND NVL(END_DATE_ACTIVE, SYSDATE + 1);
Select 'Y'
FROM mtl_lot_numbers
WHERE lot_number = Ltrim(Rtrim(p_lot_number))
AND inventory_item_id = p_item_id
AND organization_id = p_org_id;
Select 'Y'
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND inventory_item_id = p_item_id;
Select subinventory_code,organization_id
from csp_inv_loc_assignments
where csp_inv_loc_assignment_id = p_inv_loc_assignment_id;
l_serial_interface_inserted BOOLEAN;
select RECEIPT_NUM
from RCV_SHIPMENT_HEADERS
where shipment_header_id = p_receive_hdr_rec.receipt_header_id;
SELECT reqd.org_id
INTO l_org_id
FROM PO_REQ_DISTRIBUTIONS_ALL reqd,
RCV_SHIPMENT_LINES rsl
WHERE rsl.shipment_header_id = p_receive_hdr_rec.receipt_header_id
AND reqd.distribution_id = rsl.req_distribution_id
AND rownum = 1;
insert_rcv_hdr_interface
(P_Api_Version_Number => 1.0
,P_init_Msg_List => FND_API.G_FALSE
,P_Commit => FND_API.G_FALSE
,P_Validation_Level => p_validation_level
,X_Return_Status => X_return_Status
,X_Msg_Count => X_Msg_Count
,X_Msg_Data => X_Msg_Data
,p_header_interface_id => p_receive_hdr_rec.header_interface_id
,p_group_id => p_receive_hdr_rec.group_id
,p_source_type_code => p_receive_hdr_rec.source_type_code
,p_receipt_source_code => p_receive_hdr_rec.receipt_source_code
,p_vendor_id => p_receive_hdr_rec.vendor_id
,p_vendor_site_id => p_receive_hdr_rec.vendor_site_id
,p_ship_to_org_id => p_receive_hdr_rec.ship_to_org_id
,p_shipment_num => p_receive_hdr_rec.rcv_shipment_num
,p_receipt_header_id => p_receive_hdr_rec.receipt_header_id
,p_receipt_num => l_rcpt_num
,p_bill_of_lading => p_receive_hdr_rec.bill_of_lading
,p_packing_slip => p_receive_hdr_rec.packing_slip
,p_shipped_date => p_receive_hdr_rec.shipped_date
,p_freight_carrier_code => p_receive_hdr_rec.freight_carrier_code
,p_expected_receipt_date => p_receive_hdr_rec.expected_receipt_date
,p_employee_id => nvl(p_receive_hdr_rec.employee_id,l_employee_id)
,p_waybill_airbill_num => p_receive_hdr_rec.waybill_airbill_num
,p_usggl_transaction_code => p_receive_hdr_rec.usggl_transaction_code
,p_processing_request_id => p_receive_hdr_rec.processing_request_id
,p_customer_id => p_receive_hdr_rec.customer_id
,p_customer_site_id => p_receive_hdr_rec.customer_site_id
,x_header_interface_id => l_header_interface_id
,x_group_id => l_group_id);
insert_rcv_txn_interface
(P_Api_Version_Number => 1.0
,P_init_Msg_List => FND_API.G_FALSE
,P_Commit => FND_API.G_FALSE
,P_Validation_Level => p_Validation_Level
,X_Return_Status => X_return_Status
,X_Msg_Count => X_Msg_Count
,X_Msg_Data => X_Msg_Data
,x_interface_transaction_id => x_interface_transaction_id
,p_receive_rec => l_rcv_transaction_rec);
l_serial_interface_inserted := FALSE;
insert_serial_interface (
p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, px_transaction_interface_id => l_serial_interface_id
, p_product_transaction_id => x_interface_transaction_id
, p_product_code => p_receive_rec_tbl(i).product_code
, p_fm_serial_number => p_receive_rec_tbl(i).fm_serial_number
, p_to_serial_number => p_receive_rec_tbl(i).to_serial_number);
insert_lots_interface (
p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_transaction_interface_id => l_lot_interface_id
, p_lot_number => p_receive_rec_tbl(i).lot_number
, p_transaction_quantity => p_receive_rec_tbl(i).lot_quantity
, p_primary_quantity => p_receive_rec_tbl(i).lot_primary_quantity
, p_organization_id => p_receive_rec_tbl(i).to_organization_id
, p_inventory_item_id => p_receive_rec_tbl(i).item_id
, p_serial_transaction_temp_id => l_serial_interface_id
, p_product_transaction_id => x_interface_transaction_id
, p_product_code => p_receive_rec_tbl(i).product_code);
PROCEDURE insert_rcv_hdr_interface
(P_Api_Version_Number IN NUMBER,
P_init_Msg_List IN VARCHAR2,
P_Commit IN VARCHAR2,
P_Validation_Level IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
p_header_interface_id IN NUMBER,
p_group_id IN NUMBER,
p_receipt_source_code IN VARCHAR2,
p_source_type_code IN VARCHAR2,
p_vendor_id IN NUMBER,
p_vendor_site_id IN NUMBER,
p_ship_to_org_id IN NUMBER,
p_shipment_num IN VARCHAR2,
p_receipt_header_id IN NUMBER,
p_receipt_num IN VARCHAR2,
p_bill_of_lading IN VARCHAR2,
p_packing_slip IN VARCHAR2,
p_shipped_date IN DATE,
p_freight_carrier_code IN VARCHAR2,
p_expected_receipt_date IN DATE,
p_employee_id IN NUMBER,
p_waybill_airbill_num IN VARCHAR2,
p_usggl_transaction_code IN VARCHAR2,
p_processing_request_id IN NUMBER,
p_customer_id IN NUMBER,
p_customer_site_id IN NUMBER,
x_header_interface_id OUT NOCOPY NUMBER,
x_group_id OUT NOCOPY NUMBER) IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RCV_HDR_INTERFACE';
SAVEPOINT insert_rcv_hdr_interface_pvt;
SELECT rcv_headers_interface_s.NEXTVAL
INTO l_header_interface_id
FROM sys.dual;
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM sys.dual;
INSERT INTO RCV_HEADERS_INTERFACE (
header_interface_id
, group_id
, processing_status_code
, transaction_type
, validation_flag
, auto_transact_code
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, receipt_source_code
, vendor_id
, vendor_site_id
, ship_to_organization_id
, shipment_num
, receipt_header_id
, receipt_num
, bill_of_lading
, packing_slip
, shipped_date
, freight_carrier_code
, expected_receipt_date
, employee_id
, waybill_airbill_num
, usggl_transaction_code
, processing_request_id
, customer_id
, customer_site_id)
VALUES
(l_header_interface_id
,l_group_id
,'PENDING'
,'NEW'
,'Y'
,'RECEIVE'
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, p_receipt_source_code
, p_vendor_id
, p_vendor_site_id
, p_ship_to_org_id
, l_shipment_num
, l_receipt_header_id
, l_receipt_num
, p_bill_of_lading
, p_packing_slip
, p_shipped_date
, p_freight_carrier_code
, nvl(p_expected_receipt_date,sysdate)
, p_employee_id
, p_waybill_airbill_num
, p_usggl_transaction_code
, p_processing_request_id
, p_customer_id
, p_customer_site_id);
PROCEDURE insert_rcv_txn_interface
(P_Api_Version_Number IN NUMBER,
P_init_Msg_List IN VARCHAR2,
P_Commit IN VARCHAR2,
P_Validation_Level IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2,
x_interface_transaction_id OUT NOCOPY NUMBER,
p_receive_rec IN rcv_rec_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RCV_TXN_INTERFACE';
SAVEPOINT insert_rcv_txn_interface_pvt;
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_interface_transaction_id
FROM sys.dual;
SELECT BLIND_RECEIVING_FLAG
INTO l_blind_receiving_flag
FROM rcv_parameters
WHERE organization_id = l_to_org_id;
SELECT project_id
, task_id
INTO x_project_id
, x_task_id
FROM po_req_distributions
WHERE requisition_line_id = x_req_line_id;
INSERT INTO rcv_transactions_interface
(
interface_transaction_id
, header_interface_id
, GROUP_ID
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, transaction_type
, transaction_date
, processing_status_code
, processing_mode_code
, processing_request_id
, transaction_status_code
, category_id
, quantity
, unit_of_measure
, interface_source_code
, interface_source_line_id
, inv_transaction_id
, item_id
, item_description
, item_revision
, uom_code
, employee_id
, auto_transact_code
, shipment_header_id
, shipment_line_id
, ship_to_location_id
, primary_quantity
, primary_unit_of_measure
, receipt_source_code
, vendor_id
, vendor_site_id
, from_organization_id
, to_organization_id
, routing_header_id
, routing_step_id
, source_document_code
, parent_transaction_id
, po_header_id
, po_revision_num
, po_release_id
, po_line_id
, po_line_location_id
, po_unit_price
, currency_code
, currency_conversion_type
, currency_conversion_rate
, currency_conversion_date
, po_distribution_id
, requisition_line_id
, req_distribution_id
, charge_account_id
, substitute_unordered_code
, receipt_exception_flag
, accrual_status_code
, inspection_status_code
, inspection_quality_code
, destination_type_code
, deliver_to_person_id
, location_id
, deliver_to_location_id
, subinventory
, locator_id
, wip_entity_id
, wip_line_id
, department_code
, wip_repetitive_schedule_id
, wip_operation_seq_num
, wip_resource_seq_num
, bom_resource_id
, shipment_num
, freight_carrier_code
, bill_of_lading
, packing_slip
, shipped_date
, expected_receipt_date
, actual_cost
, transfer_cost
, transportation_cost
, transportation_account_id
, num_of_containers
, waybill_airbill_num
, vendor_item_num
, vendor_lot_num
, rma_reference
, comments
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_head_attribute_category
, ship_head_attribute1
, ship_head_attribute2
, ship_head_attribute3
, ship_head_attribute4
, ship_head_attribute5
, ship_head_attribute6
, ship_head_attribute7
, ship_head_attribute8
, ship_head_attribute9
, ship_head_attribute10
, ship_head_attribute11
, ship_head_attribute12
, ship_head_attribute13
, ship_head_attribute14
, ship_head_attribute15
, ship_line_attribute_category
, ship_line_attribute1
, ship_line_attribute2
, ship_line_attribute3
, ship_line_attribute4
, ship_line_attribute5
, ship_line_attribute6
, ship_line_attribute7
, ship_line_attribute8
, ship_line_attribute9
, ship_line_attribute10
, ship_line_attribute11
, ship_line_attribute12
, ship_line_attribute13
, ship_line_attribute14
, ship_line_attribute15
, ussgl_transaction_code
, government_context
, reason_id
, destination_context
, source_doc_quantity
, source_doc_unit_of_measure
, use_mtl_lot
, use_mtl_serial
, qa_collection_id
, country_of_origin_code
, oe_order_header_id
, oe_order_line_id
, customer_item_num
, customer_id
, customer_site_id
, mobile_txn
, lpn_group_id
, validation_flag
--, project_id
--, task_id
,org_id
)
VALUES (
l_interface_transaction_id
, l_header_interface_id --l_rcv_transaction_rec.header_interface_id
, l_rcv_transaction_rec.group_id
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, 'RECEIVE'
, SYSDATE
, 'PENDING' /* Processing status code */
, 'ONLINE'
, NULL
, 'PENDING' /* Transaction status code */
, l_rcv_transaction_rec.item_category_id
, l_rcv_transaction_rec.transaction_quantity
, l_rcv_transaction_rec.transaction_uom
, l_rcv_transaction_rec.product_code /* interface source code */
, NULL /* interface source line id */
, NULL /* inv_transaction id */
, l_rcv_transaction_rec.item_id
, l_rcv_transaction_rec.item_description
, l_rcv_transaction_rec.item_revision
, l_rcv_transaction_rec.uom_code
, l_rcv_transaction_rec.employee_id
, l_auto_transact_code /* Auto transact code */
, l_shipment_header_id /* shipment header id */
, l_shipment_line_id /* shipment line id */
, l_rcv_transaction_rec.ship_to_location_id
, l_rcv_transaction_rec.primary_quantity /* primary quantity */
, l_rcv_transaction_rec.primary_uom /* primary uom */
, l_receipt_source_code /* receipt source code */
, l_vendor_id
, l_vendor_site_id
, l_from_org_id /* from org id */
, l_to_org_id /* to org id */
, l_rcv_transaction_rec.routing_id
, 1 /* routing step id*/
, l_source_doc_code /* source document code */
, NULL /* Parent trx id */
, l_po_header_id
, NULL /* PO Revision number */
, l_po_release_id
, l_po_line_id
, l_po_line_location_id
, l_rcv_transaction_rec.unit_price
, l_rcv_transaction_rec.currency_code /* Currency_Code */
, l_rcv_transaction_rec.currency_conversion_type
, l_rcv_transaction_rec.currency_conversion_rate
, TRUNC(l_rcv_transaction_rec.currency_conversion_date)
, l_po_distribution_id /* po_distribution_Id */
, l_req_line_id
, l_rcv_transaction_rec.req_distribution_id
, NULL /* Charge_Account_Id */
, l_sub_unordered_code /* Substitute_Unordered_Code */
, l_rcv_transaction_rec.receipt_exception /* Receipt_Exception_Flag forms check box?*/
, NULL /* Accrual_Status_Code */
, 'NOT INSPECTED' /* Inspection_Status_Code */
, NULL /* Inspection_Quality_Code */
, l_rcv_transaction_rec.destination_type_code /* Destination_Type_Code */
, l_deliver_to_person_id /* Deliver_To_Person_Id */
, l_location_id /* Location_Id */
, l_deliver_to_location_id /* Deliver_To_Location_Id */
, l_subinventory /* Subinventory */
, l_locator_id /* Locator_Id */
, l_wip_entity_id /* Wip_Entity_Id */
, l_wip_line_id /* Wip_Line_Id */
, l_department_code /* Department_Code */
, l_wip_rep_sched_id /* Wip_Repetitive_Schedule_Id */
, l_wip_oper_seq_num /* Wip_Operation_Seq_Num */
, l_wip_res_seq_num /* Wip_Resource_Seq_Num */
, l_bom_resource_id /* Bom_Resource_Id */
, l_rcv_transaction_rec.rcv_shipment_number
, NULL
, NULL /* Bill_Of_Lading */
, NULL /* Packing_Slip */
, TRUNC(l_rcv_transaction_rec.shipped_date)
, TRUNC(l_rcv_transaction_rec.expected_receipt_date) /* Expected_Receipt_Date */
, NULL /* Actual_Cost */
, NULL /* Transfer_Cost */
, NULL /* Transportation_Cost */
, NULL /* Transportation_Account_Id */
, NULL /* Num_Of_Containers */
, NULL /* Waybill_Airbill_Num */
, l_rcv_transaction_rec.vendor_item_number /* Vendor_Item_Num */
, l_rcv_transaction_rec.vendor_lot_num /* Vendor_Lot_Num */
, NULL /* Rma_Reference */
, l_rcv_transaction_rec.comments /* Comments ? from form*/
, l_rcv_transaction_rec.attribute_category /* Attribute_Category */
, l_rcv_transaction_rec.attribute1 /* Attribute1 */
, l_rcv_transaction_rec.attribute2 /* Attribute2 */
, l_rcv_transaction_rec.attribute3 /* Attribute3 */
, l_rcv_transaction_rec.attribute4 /* Attribute4 */
, l_rcv_transaction_rec.attribute5 /* Attribute5 */
, l_rcv_transaction_rec.attribute6 /* Attribute6 */
, l_rcv_transaction_rec.attribute7 /* Attribute7 */
, l_rcv_transaction_rec.attribute8 /* Attribute8 */
, l_rcv_transaction_rec.attribute9 /* Attribute9 */
, l_rcv_transaction_rec.attribute10 /* Attribute10 */
, l_rcv_transaction_rec.attribute11 /* Attribute11 */
, l_rcv_transaction_rec.attribute12 /* Attribute12 */
, l_rcv_transaction_rec.attribute13 /* Attribute13 */
, l_rcv_transaction_rec.attribute14 /* Attribute14 */
, l_rcv_transaction_rec.attribute15 /* Attribute15 */
, NULL /* Ship_Head_Attribute_Category */
, NULL /* Ship_Head_Attribute1 */
, NULL /* Ship_Head_Attribute2 */
, NULL /* Ship_Head_Attribute3 */
, NULL /* Ship_Head_Attribute4 */
, NULL /* Ship_Head_Attribute5 */
, NULL /* Ship_Head_Attribute6 */
, NULL /* Ship_Head_Attribute7 */
, NULL /* Ship_Head_Attribute8 */
, NULL /* Ship_Head_Attribute9 */
, NULL /* Ship_Head_Attribute10 */
, NULL /* Ship_Head_Attribute11 */
, NULL /* Ship_Head_Attribute12 */
, NULL /* Ship_Head_Attribute13 */
, NULL /* Ship_Head_Attribute14 */
, NULL /* Ship_Head_Attribute15 */
, NULL /* Ship_Line_Attribute_Category */
, NULL /* Ship_Line_Attribute1 */
, NULL /* Ship_Line_Attribute2 */
, NULL /* Ship_Line_Attribute3 */
, NULL /* Ship_Line_Attribute4 */
, NULL /* Ship_Line_Attribute5 */
, NULL /* Ship_Line_Attribute6 */
, NULL /* Ship_Line_Attribute7 */
, NULL /* Ship_Line_Attribute8 */
, NULL /* Ship_Line_Attribute9 */
, NULL /* Ship_Line_Attribute10 */
, NULL /* Ship_Line_Attribute11 */
, NULL /* Ship_Line_Attribute12 */
, NULL /* Ship_Line_Attribute13 */
, NULL /* Ship_Line_Attribute14 */
, NULL /* Ship_Line_Attribute15 */
, l_rcv_transaction_rec.ussgl_transaction_code /* Ussgl_Transaction_Code */
, l_rcv_transaction_rec.government_context /* Government_Context */
, l_rcv_transaction_rec.reason_id /* ? */
, l_rcv_transaction_rec.destination_type_code /* Destination_Context */
, l_rcv_transaction_rec.ordered_qty
, l_rcv_transaction_rec.ordered_uom
, l_rcv_transaction_rec.lot_control_code
, l_rcv_transaction_rec.serial_number_control_code
, NULL
, l_rcv_transaction_rec.country_of_origin_code
, l_oe_order_header_id
, l_oe_order_line_id
, l_customer_item_num
, l_customer_id
, l_customer_site_id
, 'N' /* mobile_txn */
, NULL -- l_lpn_group_id
, l_validation_flag
--, l_project_id
--, l_task_id
,l_operating_unit
);
PROCEDURE insert_lots_interface (
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_serial_transaction_temp_id IN NUMBER
, p_transaction_interface_id IN NUMBER
, p_lot_number IN VARCHAR2
, p_transaction_quantity IN NUMBER
, p_primary_quantity IN NUMBER
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_product_transaction_id IN NUMBER
, p_product_code IN VARCHAR2) IS
CURSOR c_mln_attributes( v_lot_number VARCHAR2
, v_inventory_item_id NUMBER
, v_organization_id NUMBER) IS
SELECT lot_number
, expiration_date
, description
, vendor_name
, supplier_lot_number
, grade_code
, origination_date
, date_code
, status_id
, change_date
, age
, retest_date
, maturity_date
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, vendor_id
, territory_code
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
FROM mtl_lot_numbers
WHERE lot_number = Ltrim(Rtrim(v_lot_number))
AND inventory_item_id = v_inventory_item_id
AND organization_id = v_organization_id;
l_api_name CONSTANT VARCHAR2(30) := 'insert_lots_interface';
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_interface_id
FROM sys.dual;
INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
transaction_interface_id
, source_code
, source_line_id
, product_code
, product_transaction_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, lot_number
, lot_expiration_date
, transaction_quantity
, primary_quantity
, serial_transaction_temp_id
, description
, vendor_name
, supplier_lot_number
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, status_id
, retest_date
, age
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, length
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, vendor_id
, territory_code
)
VALUES (
l_transaction_interface_id
, 1
, -1
, p_product_code
, p_product_transaction_id
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, Ltrim(Rtrim(p_lot_number))
, l_expiration_date
, p_transaction_quantity
, p_primary_quantity
, p_serial_transaction_temp_id
, l_description
, l_vendor_name
, l_supplier_lot_number
, l_origination_date
, l_date_code
, l_grade_code
, l_change_date
, l_maturity_date
, l_status_id
, l_retest_date
, l_age
, l_item_size
, l_color
, l_volume
, l_volume_uom
, l_place_of_origin
, l_best_by_date
, l_length
, l_length_uom
, l_recycled_content
, l_thickness
, l_thickness_uom
, l_width
, l_width_uom
, l_curl_wrinkle_fold
, l_lot_attribute_category
, l_c_attribute1
, l_c_attribute2
, l_c_attribute3
, l_c_attribute4
, l_c_attribute5
, l_c_attribute6
, l_c_attribute7
, l_c_attribute8
, l_c_attribute9
, l_c_attribute10
, l_c_attribute11
, l_c_attribute12
, l_c_attribute13
, l_c_attribute14
, l_c_attribute15
, l_c_attribute16
, l_c_attribute17
, l_c_attribute18
, l_c_attribute19
, l_c_attribute20
, l_d_attribute1
, l_d_attribute2
, l_d_attribute3
, l_d_attribute4
, l_d_attribute5
, l_d_attribute6
, l_d_attribute7
, l_d_attribute8
, l_d_attribute9
, l_d_attribute10
, l_n_attribute1
, l_n_attribute2
, l_n_attribute3
, l_n_attribute4
, l_n_attribute5
, l_n_attribute6
, l_n_attribute7
, l_n_attribute8
, l_n_attribute9
, l_n_attribute10
, l_vendor_id
, l_territory_code
);
* This procedure inserts a record into MTL_SERIAL_NUMBERS_INTERFACE
* Generate transaction_interface_id if the parameter is NULL
* Generate product_transaction_id if the parameter is NULL
* The insert logic is based on the parameter p_att_exist.
* If p_att_exist is "N" Then (attributes are not available in table)
* Read the input parameters (including attributes) into a PL/SQL table
* Insert one record into MSNI with the from and to serial numbers passed
* Else
* Loop through each serial number between the from and to serial number
* Fetch the attributes into one row of the PL/SQL table and
* For each row in the PL/SQL table, insert one MSNI record
* End If
*
* @param p_api_version - Version of the API
* @param p_init_msg_list - Flag to initialize message list
* @param x_return_status
* Return status indicating Success (S), Error (E), Unexpected Error (U)
* @param x_msg_count
* Number of messages in message list
* @param x_msg_data
* Stacked messages text
* @param p_transaction_interface_id - MTLI.Interface Transaction ID
* @param p_fm_serial_number - From Serial Number
* @param p_to_serial_number - To Serial Number
* @param p_organization_id - Organization ID
* @param p_inventory_item_id - Inventory Item ID
* @param p_status_id - Material Status for the lot
* @param p_product_transaction_id - Product Transaction Id. This parameter
* is stamped with the transaction identifier with
* @param p_product_code - Code of the product creating this record
* @param p_att_exist - Flag to indicate if attributes exist
* @param p_update_msn - Flag to update MSN with attributes
* @param named attributes - Named attributes
* @param C Attributes - Character atributes (1 - 20)
* @param D Attributes - Date atributes (1 - 10)
* @param N Attributes - Number atributes (1 - 10)
* @param p_attribute_cateogry - Attribute Category
* @param Attribute1-15 - Serial Attributes
*
* @ return: NONE
*---------------------------------------------------------------------------*/
PROCEDURE insert_serial_interface(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, px_transaction_interface_id IN OUT NOCOPY NUMBER
, p_product_transaction_id IN NUMBER
, p_product_code IN VARCHAR2
, p_fm_serial_number IN VARCHAR2
, p_to_serial_number IN VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'insert_serial_interface';
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_interface_id
FROM sys.dual;
Insert into MTL_SERIAL_NUMBERS_INTERFACE
(
transaction_interface_id,
Source_Code,
Source_Line_Id,
Process_flag,
Last_Update_Date,
Last_Updated_By,
Last_update_login,
Creation_Date,
Created_By,
Fm_Serial_Number,
To_Serial_Number,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)
VALUES
(
l_transaction_interface_id,
1,
-1,
1,
SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,sysdate
,FND_GLOBAL.USER_ID
,p_fm_serial_number
,p_to_serial_number
,p_product_code
,p_product_transaction_id);
select ERROR_MESSAGE INTO po_message
from po_interface_errors
where BATCH_ID = p_group_id;
select ms.po_header_id
from mtl_supply ms,
po_lines_all pla,
mtl_system_items_b msi
where msi.organization_id = ms.to_organization_id
and msi.inventory_item_id = ms.item_id
and pla.po_line_id = ms.po_line_id
and ms.po_header_id = v_header_id
and (msi.serial_number_control_code <> 1
or msi.lot_control_code <> 1
or (msi.revision_qty_control_code = 2 and pla.item_revision is null));
select ms.item_id,
ms.to_organization_id,
ms.to_subinventory
from mtl_supply ms
where ms.po_header_id = v_header_id;
select 'Y'
from mtl_parameters a,
mtl_system_items_b b,
mtl_secondary_inventories c
where a.organization_id = b.organization_id
and a.organization_id = c.organization_id
and a.organization_id = v_org_id
and b.inventory_item_id = v_item_id
and c.secondary_inventory_name = v_sub_inv
and ( a.stock_locator_control_code in (2,3) --Org Control should be 2 or 3
OR a.stock_locator_control_code = 4 AND c.locator_type in (2,3) --org Control 4 and sub control 2 or 3
OR a.stock_locator_control_code = 4 AND c.locator_type = 5 AND b.location_control_code in (2,3) );
select vendor_name
from po_vendors
where vendor_id = p_vendor_id;