所建TABLE为
CREATE TABLE jy_qp_list(
list_id NUMBER PRIMARY KEY NOT NULL,
created_by NUMBER NOT NULL,
creation_date DATE NOT NULL,
last_updated_by NUMBER NOT NULL,
last_update_date DATE NOT NULL,
last_update_login NUMBER,
attribute1 VARCHAR2(240),
attribute2 VARCHAR2(240),
attribute3 VARCHAR2(240),
attribute4 VARCHAR2(240),
attribute5 VARCHAR2(240),
attribute6 VARCHAR2(240),
attribute7 VARCHAR2(240),
attribute8 VARCHAR2(240),
attribute9 VARCHAR2(240),
attribute10 VARCHAR2(240),
attribute11 VARCHAR2(240),
attribute12 VARCHAR2(240),
attribute13 VARCHAR2(240),
attribute14 VARCHAR2(240),
attribute15 VARCHAR2(240),
org_id NUMBER NOT NULL,
mtl_id NUMBER NOT NULL,
custom_id NUMBER,
price NUMBER NOT NULL,
valid_date DATE NOT NULL,
invalid_date DATE
);
在此TABLE基础上创建一个VIEW:
CREATE OR REPLACE VIEW jy_qp_list_v AS
SELECT qp.rowid row_id,qp.list_id,qp.created_by,qp.creation_date,qp.last_updated_by,qp.last_update_date,
qp.last_update_login,qp.attribute1,qp.attribute2,qp.attribute3,qp.attribute4,
qp.attribute5,qp.attribute6,qp.attribute7,qp.attribute8,qp.attribute9,
qp.attribute10,qp.attribute11,qp.attribute12,qp.attribute13,qp.attribute14,qp.attribute15,
qp.org_id,qp.mtl_id,qp.custom_id,qp.price,qp.valid_date,qp.invalid_date,
mtl.segment1 mtl_no,mtl.description mtl_desc,mtl.primary_uom_code mtl_unit,
cust.account_number cust_no,
party.party_name cust_name
FROM jy_qp_list qp,
mtl_system_items_b mtl,
hz_cust_accounts cust,
hz_parties party
WHERE 1=1
AND qp.mtl_id = mtl.inventory_item_id
AND qp.org_id = mtl.organization_id
AND qp.custom_id = cust.cust_account_id(+)
AND cust.party_id = party.party_id
/
PACKAGE:
--此包是创建在数据库中
CREATE OR REPLACE PACKAGE BODY jy_qp_list_pkg AS
/*=====================================
** PROCEDURE: lock_row()
**=====================================*/
PROCEDURE lock_row(p_list_id IN NUMBER,
p_organization_id IN NUMBER,
p_mtl_id IN NUMBER,
p_custom_id IN NUMBER,
p_price IN NUMBER,
p_valid_date IN DATE,
p_invalid_date IN DATE,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2) IS
CURSOR c IS
SELECT list_id,
org_id,
mtl_id,
custom_id,
price,
valid_date,
invalid_date,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM jy_qp_list jqp
WHERE 1=1
AND jqp.list_id = p_list_id
AND jqp.org_id = p_organization_id
AND jqp.mtl_id = p_mtl_id
AND jqp.custom_id = p_custom_id
AND jqp.price = p_price
AND jqp.valid_date = p_valid_date
AND jqp.invalid_date = p_invalid_date
FOR UPDATE NOWAIT;
rec c%ROWTYPE;
BEGIN
OPEN c;
FETCH c
INTO rec;
IF (c%NOTFOUND)
THEN
CLOSE c;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
fnd_message.raise_error;
app_exception.raise_exception;
END IF;
CLOSE c;
IF( ((rec.list_id = p_list_id) OR((rec.list_id IS NULL) AND (p_list_id IS NULL)))
AND ((rec.org_id = p_organization_id) OR ((rec.org_id IS NULL) AND (p_organization_id IS NULL)))
AND ((rec.mtl_id = p_mtl_id) OR ((rec.mtl_id IS NULL) AND (p_mtl_id IS NULL)))
AND ((rec.custom_id = p_custom_id) OR ((rec.custom_id IS NULL) AND (p_custom_id IS NULL)))
AND ((rec.price = p_price) OR ((rec.price IS NULL) AND (p_price IS NULL)))
AND ((rec.valid_date = p_valid_date) OR ((rec.valid_date IS NULL) AND (p_valid_date IS NULL)))
AND ((rec.invalid_date = p_invalid_date) OR ((rec.invalid_date IS NULL) AND (p_invalid_date IS NULL)))
AND ((rec.attribute1 = p_attribute1) OR ((rec.attribute1 IS NULL) AND (p_attribute1 IS NULL)))
AND ((rec.attribute2 = p_attribute2) OR ((rec.attribute2 IS NULL) AND (p_attribute2 IS NULL)))
AND ((rec.attribute3 = p_attribute3) OR ((rec.attribute3 IS NULL) AND (p_attribute3 IS NULL)))
AND ((rec.attribute4 = p_attribute4) OR ((rec.attribute4 IS NULL) AND (p_attribute4 IS NULL)))
AND ((rec.attribute5 = p_attribute5) OR ((rec.attribute5 IS NULL) AND (p_attribute5 IS NULL)))
AND ((rec.attribute6 = p_attribute6) OR ((rec.attribute6 IS NULL) AND (p_attribute6 IS NULL)))
AND ((rec.attribute7 = p_attribute7) OR ((rec.attribute7 IS NULL) AND (p_attribute7 IS NULL)))
AND ((rec.attribute8 = p_attribute8) OR ((rec.attribute8 IS NULL) AND (p_attribute8 IS NULL)))
AND ((rec.attribute9 = p_attribute9) OR ((rec.attribute9 IS NULL) AND (p_attribute9 IS NULL)))
AND ((rec.attribute10 = p_attribute10) OR ((rec.attribute10 IS NULL) AND (p_attribute10 IS NULL)))
AND ((rec.attribute11 = p_attribute11) OR ((rec.attribute11 IS NULL) AND (p_attribute11 IS NULL)))
AND ((rec.attribute12 = p_attribute12) OR ((rec.attribute12 IS NULL) AND (p_attribute12 IS NULL)))
AND ((rec.attribute13 = p_attribute13) OR ((rec.attribute13 IS NULL) AND (p_attribute13 IS NULL)))
AND ((rec.attribute14 = p_attribute14) OR ((rec.attribute14 IS NULL) AND (p_attribute14 IS NULL)))
AND ((rec.attribute15 = p_attribute15) OR ((rec.attribute15 IS NULL) AND (p_attribute15 IS NULL)))
)
THEN
NULL;
ELSE
fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
fnd_message.raise_error;
app_exception.raise_exception;
END IF;
END lock_row;
END jy_qp_list_pkg;
在FORM上的PROGRAM UNIT中创建:
PACKAGE BODY jy_qp_list_th IS
/*=====================================
** PROCEDURE: lock_row()
**=====================================*/
PROCEDURE lock_row IS
i NUMBER := 0;
BEGIN
LOOP
BEGIN
i := i + 1;
jy_qp_list_pkg.lock_row(p_list_id => name_in('jycustprice.list_id'),
p_organization_id => name_in('jycustprice.organization_id'),
p_mtl_id => name_in('jycustprice.mtl_id'),
p_custom_id => name_in('jycustprice.custom_id'),
p_price => name_in('jycustprice.price'),
p_valid_date => name_in('jycustprice.valid_date'),
p_invalid_date => name_in('jycustprice.invalid_date'),
p_attribute1 => name_in('jycustprice.attribute1'),
p_attribute2 => name_in('jycustprice.attribute2'),
p_attribute3 => name_in('jycustprice.attribute3'),
p_attribute4 => name_in('jycustprice.attribute4'),
p_attribute5 => name_in('jycustprice.attribute5'),
p_attribute6 => name_in('jycustprice.attribute6'),
p_attribute7 => name_in('jycustprice.attribute7'),
p_attribute8 => name_in('jycustprice.attribute8'),
p_attribute9 => name_in('jycustprice.attribute9'),
p_attribute10 => name_in('jycustprice.attribute10'),
p_attribute11 => name_in('jycustprice.attribute11'),
p_attribute12 => name_in('jycustprice.attribute12'),
p_attribute13 => name_in('jycustprice.attribute13'),
p_attribute14 => name_in('jycustprice.attribute14'),
p_attribute15 => name_in('jycustprice.attribute15'));
RETURN;
EXCEPTION
WHEN app_exception.record_lock_exception THEN
app_exception.record_lock_error(i);
END;
END LOOP;
END lock_row;
END jy_qp_list_th;
然后在BLOCK的TRIGGER: on-lock中调用:
jy_qp_list_th.lock_row;
在系统中操作,进行UPDATE的时候就会提示以上错误