[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
into dummy
from dual
where not exists ( select 1
from pn_companies_all
where name = p_company_name
and (( p_rowid is null ) or (rowid <> p_rowid))
and org_id = p_org_id
);
SELECT 1
INTO dummy
FROM dual
WHERE NOT EXISTS ( SELECT 1
FROM pn_companies_all
WHERE company_number = p_company_number
AND (( p_rowid is null ) or (rowid <> p_rowid))
AND org_id = p_org_id
);
| PROCEDURE : insert_row
| DESCRIPTION : inserts a row in pn_companies_all
| SCOPE : PUBLIC
| INVOKED FROM :
| ARGUMENTS : IN : x_rowid, x_company_id, x_company_number,
| x_last_update_date, x_last_updated_by, x_creation_date
| x_created_by, x_last_update_login, x_name,
| x_enabled_flag, x_parent_company_id ,
| x_attribute_category, x_attribute1, x_attribute2,
| x_attribute3, x_attribute4 , x_attribute5, x_attribute6
| x_attribute7, x_attribute8, x_attribute9, x_attribute10
| x_attribute11, x_attribute12, x_attribute13,
| x_attribute14, x_attribute15, x_org_id
| OUT : NONE
| RETURNS : NONE
| HISTORY :
| 26-APR-05 piagrawa o Modified the select statements to retrieve values
| from pn_companies_all instead of pn_companies_all
| 28-NOV-05 pikhar o fetched org_id using cursor
+===========================================================================*/
PROCEDURE insert_row ( x_rowid IN OUT NOCOPY VARCHAR2,
x_company_id IN OUT NOCOPY NUMBER,
x_company_number IN OUT NOCOPY VARCHAR2,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_creation_date DATE,
x_created_by NUMBER,
x_last_update_login NUMBER,
x_name VARCHAR2,
x_enabled_flag VARCHAR2,
x_parent_company_id NUMBER,
x_attribute_category VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2,
x_org_id NUMBER
) IS
CURSOR C is
SELECT rowid
FROM pn_companies_all
WHERE company_id = x_company_id;
select pn_companies_s.nextval
into x_company_id
from dual;
select pn_companies_num_s.nextval
into x_company_number
from dual;
insert into pn_companies_all (
company_id,
company_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
name,
enabled_flag,
parent_company_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id
)
values
(
x_company_id,
x_company_number,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
x_name,
x_enabled_flag,
x_parent_company_id,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_org_id
);
END insert_row;
PROCEDURE update_row ( x_rowid VARCHAR2,
x_company_id NUMBER,
x_company_number VARCHAR2,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_last_update_login NUMBER,
x_name VARCHAR2,
x_enabled_flag VARCHAR2,
x_parent_company_id NUMBER,
x_attribute_category VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2
)
IS
l_org_id NUMBER;
SELECT org_id
INTO l_org_id
FROM pn_companies_all
WHERE company_id = x_company_id;
UPDATE pn_companies_all
SET company_number = x_company_number,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
name = x_name,
enabled_flag = x_enabled_flag,
parent_company_id = x_parent_company_id,
attribute_category = x_attribute_category,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
attribute6 = x_attribute6,
attribute7 = x_attribute7,
attribute8 = x_attribute8,
attribute9 = x_attribute9,
attribute10 = x_attribute10,
attribute11 = x_attribute11,
attribute12 = x_attribute12,
attribute13 = x_attribute13,
attribute14 = x_attribute14,
attribute15 = x_attribute15
WHERE company_id = x_company_id;
END update_row;
SELECT *
FROM pn_companies_all
WHERE rowid = x_rowid
FOR UPDATE OF company_id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');