CREATE OR REPLACE PACKAGE BODY cux_main_pkg AS
/*=====================================
** PROCEDURE: insert_row()
**=====================================*/
PROCEDURE insert_row(x_row_id IN OUT VARCHAR2,
x_insp_header_id IN OUT NUMBER,
p_org_id IN NUMBER,
p_doc_type_class IN VARCHAR2,
p_inspection_number IN VARCHAR2,
p_main_subsidiary IN VARCHAR2 DEFAULT NULL,
p_status IN VARCHAR2,
p_oe_header_id IN NUMBER DEFAULT NULL,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER DEFAULT NULL)
IS
CURSOR c IS
SELECT ROWID
FROM table1
WHERE insp_header_id = x_insp_header_id;
BEGIN
IF x_insp_header_id IS NULL THEN
SELECT main_s.nextval
INTO x_insp_header_id
FROM dual;
END IF;
INSERT INTO table1
(insp_header_id,
org_id,
doc_type_class,
inspection_number,
main_subsidiary,
status,
oe_header_id,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(x_insp_header_id,
p_org_id,
p_doc_type_class,
p_inspection_number,
p_main_subsidiary,
p_status,
p_oe_header_id,
p_creation_date,
p_created_by,
p_last_updated_by,
p_last_update_date,
p_last_update_login
);
OPEN c;
FETCH c
INTO x_row_id;
IF (c%NOTFOUND) THEN
CLOSE c;
RAISE no_data_found;
END IF;
CLOSE c;
END insert_row;
/*=====================================
** PROCEDURE: lock_row()
**=====================================*/
PROCEDURE lock_row(p_insp_header_id IN NUMBER,
p_org_id IN NUMBER,
p_doc_type_class IN VARCHAR2,
p_inspection_number IN VARCHAR2,
p_main_subsidiary IN VARCHAR2 DEFAULT NULL,
p_status IN VARCHAR2,
p_oe_header_id IN NUMBER DEFAULT NULL,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER
)
IS
CURSOR c IS
SELECT insp_header_id,
org_id,
doc_type_class,
inspection_number,
nvl(main_subsidiary, 'MAIN') main_subsidiary,
status,
oe_header_id,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login
FROM table1
WHERE insp_header_id = p_insp_header_id
FOR UPDATE OF insp_header_id 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');
app_exception.raise_exception;
END IF;
CLOSE c;
IF ((rec.insp_header_id = p_insp_header_id) AND
((rec.org_id = p_org_id) OR
((rec.org_id IS NULL) AND (p_org_id IS NULL))) AND
((rec.doc_type_class = p_doc_type_class) OR
((rec.doc_type_class IS NULL) AND (p_doc_type_class IS NULL))) AND
((rec.inspection_number = p_inspection_number) OR
((rec.inspection_number IS NULL) AND (p_inspection_number IS NULL))) AND
((rec.main_subsidiary = p_main_subsidiary) OR
((rec.main_subsidiary IS NULL) AND (p_main_subsidiary IS NULL))) AND
((rec.status = p_status) OR
((rec.status IS NULL) AND (p_status IS NULL))) AND
((rec.oe_header_id = p_oe_header_id) OR
((rec.oe_header_id IS NULL) AND (p_oe_header_id IS NULL))) AND
((rec.creation_date = p_creation_date) OR
((rec.creation_date IS NULL) AND (p_creation_date IS NULL))) AND
((rec.created_by = p_created_by) OR
((rec.created_by IS NULL) AND (p_created_by IS NULL))) AND
((rec.last_updated_by = p_last_updated_by) OR
((rec.last_updated_by IS NULL) AND (p_last_updated_by IS NULL))) AND
((rec.last_update_date = p_last_update_date) OR
((rec.last_update_date IS NULL) AND (p_last_update_date IS NULL))) AND
((rec.last_update_login = p_last_update_login) OR
((rec.last_update_login IS NULL) AND (p_last_update_login IS NULL)))) THEN
NULL;
ELSE
fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
app_exception.raise_exception;
END IF;
END lock_row;
/*=====================================
** PROCEDURE: update_row()
**=====================================*/
PROCEDURE update_row(p_insp_header_id IN NUMBER,
p_org_id IN NUMBER,
p_doc_type_class IN VARCHAR2,
p_inspection_number IN VARCHAR2,
p_main_subsidiary IN VARCHAR2 DEFAULT NULL,
p_status IN VARCHAR2,
p_oe_header_id IN NUMBER DEFAULT NULL,
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER DEFAULT NULL
)
IS
BEGIN
UPDATE table1
SET insp_header_id = p_insp_header_id,
org_id = p_org_id,
doc_type_class = p_doc_type_class,
inspection_number = p_inspection_number,
main_subsidiary = p_main_subsidiary,
status = p_status,
oe_header_id = p_oe_header_id,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date,
last_update_login = p_last_update_login
WHERE insp_header_id = p_insp_header_id;
IF (SQL%NOTFOUND) THEN
RAISE no_data_found;
END IF;
END update_row;
/*=====================================
** PROCEDURE: delete_row()
**=====================================*/
PROCEDURE delete_row(p_insp_header_id IN NUMBER)
IS
BEGIN
DELETE FROM table1
WHERE insp_header_id = p_insp_header_id;
IF (SQL%NOTFOUND) THEN
RAISE no_data_found;
END IF;
END delete_row;
END cux_main_pkg;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26189725/viewspace-1433497/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26189725/viewspace-1433497/