[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_ROW(
x_billing_rule_header_id IN NUMBER ,
x_name IN VARCHAR2 ,
x_description IN VARCHAR2 ,
x_service_agreement IN VARCHAR2 ,
x_service_agreement_id IN NUMBER ,
x_start_date IN DATE ,
x_end_date IN DATE ,
x_creation_date IN DATE ,
x_created_by IN NUMBER,
x_last_update_date IN DATE,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER
) AS
BEGIN
INSERT INTO mtl_billing_rule_headers_b
( billing_rule_header_id,
service_agreement_id,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
( x_billing_rule_header_id ,
x_service_agreement_id ,
x_creation_date ,
x_created_by ,
x_last_update_date ,
x_last_updated_by ,
x_last_update_login
);
INSERT INTO mtl_billing_rule_headers_tl
(
billing_rule_header_id ,
name ,
description ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
LANGUAGE ,
source_lang
)
SELECT
x_billing_rule_header_id ,
x_name ,
x_description ,
x_creation_date ,
x_created_by ,
x_last_update_date ,
x_last_updated_by ,
x_last_update_login ,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
( select 1
from mtl_billing_rule_headers_tl T
where T.billing_rule_header_id = x_billing_rule_header_id
and T.LANGUAGE = L.LANGUAGE_CODE
);
END INSERT_ROW;
procedure UPDATE_ROW(
x_billing_rule_header_id IN NUMBER ,
x_name IN VARCHAR2 ,
x_description IN VARCHAR2 ,
x_service_agreement IN VARCHAR2 ,
x_service_agreement_id IN NUMBER ,
x_start_date IN DATE ,
x_end_date IN DATE ,
x_creation_date IN DATE ,
x_created_by IN NUMBER,
x_last_update_date IN DATE,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER
) AS
CURSOR cur_sources IS SELECT 1 FROM mtl_billing_rule_headers_b
WHERE billing_rule_header_id = x_billing_rule_header_id;
update mtl_billing_rule_headers_b set
service_agreement_id = x_service_agreement_id,
creation_date = x_creation_date,
created_by = x_created_by,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login
where billing_rule_header_id = x_billing_rule_header_id;
update mtl_billing_rule_headers_tl set
name = x_name,
description = x_description ,
creation_date = x_creation_date,
created_by = x_created_by,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
SOURCE_LANG = userenv('LANG')
where billing_rule_header_id = x_billing_rule_header_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
END UPDATE_ROW;
procedure DELETE_ROW (
x_billing_rule_header_id in NUMBER
) is
begin
delete from mtl_billing_rule_headers_tl
where billing_rule_header_id = x_billing_rule_header_id;
delete from mtl_billing_rule_headers_b
where billing_rule_header_id = x_billing_rule_header_id;
end DELETE_ROW;
x_last_update_date IN DATE,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER
) is
cursor c is select
billing_rule_header_id ,
service_agreement_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
from mtl_billing_rule_headers_b
where billing_rule_header_id = x_billing_rule_header_id
for update of billing_rule_header_id nowait;
cursor c1 is select
name,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from mtl_billing_rule_headers_tl
where billing_rule_header_id = x_billing_rule_header_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of billing_rule_header_id nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
delete from mtl_billing_rule_headers_tl T
where not exists
(select NULL
from mtl_billing_rule_headers_b B
where B.billing_rule_header_id = T.billing_rule_header_id
);
update mtl_billing_rule_headers_tl T
set (
name,
description
) = (select
B.name,
B.description
from mtl_billing_rule_headers_tl B
where B.billing_rule_header_id = T.billing_rule_header_id
and B.language = T.source_lang
)
where (
T.billing_rule_header_id,
T.language
) in (select
blrult.billing_rule_header_id,
blrult.language
from mtl_billing_rule_headers_tl blrulb, mtl_billing_rule_headers_tl blrult
where blrulb.billing_rule_header_id = blrult.billing_rule_header_id
and blrulb.language = blrult.source_lang
and (blrulb.name <> blrult.name
or blrulb.description <> blrult.description
or (blrulb.description is null and blrult.description is not null)
or (blrulb.description is not null and blrult.description is null)
)
);
insert into mtl_billing_rule_headers_tl
(
billing_rule_header_id,
name,
description,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
language,
source_lang
)
select /*+ ORDERED */
B.billing_rule_header_id,
B.name,
B.description,
B.creation_date,
B.created_by,
B.last_update_date,
B.last_updated_by,
B.last_update_login,
L.language_code,
B.source_lang
from mtl_billing_rule_headers_tl B, fnd_languages L
where L.installed_flag in ('I', 'B')
and B.language = userenv('LANG')
and not exists
(select NULL
from mtl_billing_rule_headers_tl T
where T.billing_rule_header_id = B.billing_rule_header_id
and T.language = L.language_code
);