oracle ebs系统二开,Oracle EBS 二次开发问题,高手进

所建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的时候就会提示以上错误