[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT name
INTO x_revenue_class_name
FROM cn_revenue_classes
WHERE revenue_class_id = x_revenue_class_id and org_id=x_org_id;
PROCEDURE Insert_Row (x_rule_id number,
x_name varchar2,
x_ruleset_id number,
x_revenue_class_id number,
x_expense_ccid NUMBER,
x_liability_ccid NUMBER,
x_parent_rule_id number,
x_sequence_number number,
x_org_id number) IS
l_rowid ROWID;
insert_row_into_cn_rules_only(
x_rowid => l_rowid,
x_rule_id => x_rule_id,
x_name => x_name,
x_ruleset_id => x_ruleset_id,
x_revenue_class_id => x_revenue_class_id,
x_expense_ccid => x_expense_ccid,
x_liability_ccid => x_liability_ccid,
x_org_id =>x_org_id);
INSERT INTO cn_rules_hierarchy
(rule_id, parent_rule_id, sequence_number, ruleset_id,org_id)
VALUES (x_rule_id, x_parent_rule_id, x_sequence_number, x_ruleset_id, x_org_id);
END Insert_Row;
procedure insert_row_into_cn_rules_only
(
X_ROWID in out nocopy VARCHAR2,
X_RULE_ID in NUMBER,
X_RULESET_ID in NUMBER,
X_PACKAGE_ID in NUMBER,
X_REVENUE_CLASS_ID in NUMBER,
x_expense_ccid IN NUMBER,
x_liability_ccid IN NUMBER,
X_NAME in VARCHAR2,
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,
X_ORG_ID in number
) IS
L_RULE_ID NUMBER;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
SELECT Decode(x_RULE_ID, FND_API.G_MISS_NUM, NULL,
Ltrim(Rtrim(x_RULE_ID)))
INTO l_RULE_ID FROM sys.dual;
SELECT Decode(x_RULESET_ID, FND_API.G_MISS_NUM, NULL,
Ltrim(Rtrim(x_RULESET_ID)))
INTO l_RULESET_ID FROM sys.dual;
SELECT Decode(x_PACKAGE_ID, FND_API.G_MISS_NUM, NULL,
Ltrim(Rtrim(x_PACKAGE_ID)))
INTO l_PACKAGE_ID FROM sys.dual;
SELECT Decode(x_REVENUE_CLASS_ID, FND_API.G_MISS_NUM, NULL,
Ltrim(Rtrim(x_REVENUE_CLASS_ID)))
INTO l_REVENUE_CLASS_ID FROM sys.dual;
SELECT Decode(x_expense_ccid, FND_API.G_MISS_NUM, NULL,
Ltrim(Rtrim(x_expense_ccid)))
INTO l_expense_ccid FROM sys.dual;
SELECT Decode(x_liability_ccid, FND_API.G_MISS_NUM, NULL,
Ltrim(Rtrim(x_liability_ccid)))
INTO l_liability_ccid FROM sys.dual;
SELECT Decode(x_NAME, FND_API.G_MISS_CHAR, NULL,
Ltrim(Rtrim(x_NAME)))
INTO l_NAME FROM sys.dual;
SELECT Decode(x_CREATION_DATE, FND_API.G_MISS_DATE, NULL,
Ltrim(Rtrim(x_CREATION_DATE)))
INTO l_CREATION_DATE FROM sys.dual;
SELECT Decode(x_CREATED_BY, FND_API.G_MISS_NUM, NULL,
Ltrim(Rtrim(x_CREATED_BY)))
INTO l_CREATED_BY FROM sys.dual;
SELECT Decode(x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL,
Ltrim(Rtrim(x_LAST_UPDATE_DATE)))
INTO l_LAST_UPDATE_DATE FROM sys.dual;
SELECT Decode(x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,
Ltrim(Rtrim(x_LAST_UPDATED_BY)))
INTO l_LAST_UPDATED_BY FROM sys.dual;
SELECT Decode(x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,
Ltrim(Rtrim(x_LAST_UPDATE_LOGIN)))
INTO l_LAST_UPDATE_LOGIN FROM sys.dual;
SELECT Decode(x_ORG_ID, FND_API.G_MISS_NUM, NULL,
Ltrim(Rtrim(x_ORG_ID)))
INTO l_ORG_ID FROM sys.dual;
insert into CN_RULES_ALL_B
(
PACKAGE_ID,
RULE_ID,
RULESET_ID,
REVENUE_CLASS_ID,
expense_ccid,
liability_ccid,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ORG_ID
) values
(
L_PACKAGE_ID,
L_RULE_ID,
L_RULESET_ID,
L_REVENUE_CLASS_ID,
l_expense_ccid,
l_liability_ccid,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN,
L_ORG_ID
);
insert into CN_RULES_ALL_TL (
NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
RULESET_ID,
RULE_ID,
LANGUAGE,
SOURCE_LANG,
ORG_ID
) select
L_NAME,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN,
L_CREATION_DATE,
L_CREATED_BY,
L_RULESET_ID,
L_RULE_ID,
L.LANGUAGE_CODE,
userenv('LANG'),
L_ORG_ID
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from CN_RULES_ALL_TL T
where T.RULE_ID = L_RULE_ID
and T.RULESET_ID = L_RULESET_ID --RC 06-APR-99 Added code
and T.LANGUAGE = L.language_code AND
T.ORG_ID=L_ORG_ID);
end INSERT_ROW_into_cn_rules_only;
UPDATE cn_rulesets_all_b
SET ruleset_status = 'UNSYNC'
WHERE ruleset_id = x_ruleset_id
and ORG_ID= x_org_id ;
procedure UPDATE_ROW
(
X_RULE_ID in NUMBER,
X_RULESET_ID in NUMBER,
X_PACKAGE_ID in NUMBER,
X_REVENUE_CLASS_ID in NUMBER,
x_expense_ccid IN NUMBER,
x_liability_ccid IN NUMBER,
X_NAME in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_ORG_ID IN NUMBER,
X_OBJECT_VERSION_NO IN OUT NOCOPY NUMBER
) is
begin
X_OBJECT_VERSION_NO:=X_OBJECT_VERSION_NO+1;
update CN_RULES_ALL_B set
PACKAGE_ID = X_PACKAGE_ID,
REVENUE_CLASS_ID = X_REVENUE_CLASS_ID,
expense_ccid = x_expense_ccid,
liability_ccid = x_liability_ccid,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER=X_OBJECT_VERSION_NO
where RULE_ID = X_RULE_ID
and RULESET_ID = x_ruleset_id AND
ORG_ID=X_ORG_ID;
update CN_RULES_ALL_TL set
NAME = X_NAME,
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 RULE_ID = X_RULE_ID
and RULESET_ID = X_RULESET_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
ORG_ID=X_ORG_ID;
end UPDATE_ROW;
procedure DELETE_ROW (X_RULE_ID in NUMBER,
X_RULESET_ID in NUMBER,
X_ORG_ID IN NUMBER) IS --RC 2/25/99 Added ruleset id
Cursor Cascade IS (SELECT rule_id
FROM cn_rules_hierarchy
WHERE parent_rule_id = x_rule_id
AND ruleset_id = x_ruleset_id AND
ORG_ID=X_ORG_ID);
DELETE cn_attribute_rules
WHERE rule_id = x_rule_id
AND ruleset_id = x_ruleset_id ;
DELETE cn_rules_hierarchy
WHERE rule_id = x_rule_id
AND ruleset_id = x_ruleset_id;
delete from CN_RULES_ALL_TL
where RULE_ID = X_RULE_ID
and ruleset_id = x_ruleset_id
and ORG_ID=X_ORG_ID;
delete from CN_RULES_ALL_B
where RULE_ID = X_RULE_ID
and ruleset_id = x_ruleset_id AND
ORG_ID=X_ORG_ID;
Delete_Row(C.rule_id, x_ruleset_id,X_ORG_ID);
DELETE cn_rules_hierarchy
WHERE parent_rule_id = x_rule_id
AND ruleset_id = x_ruleset_id AND
ORG_ID=X_ORG_ID;
end DELETE_ROW;
delete from CN_RULES_ALL_TL T
where not exists
(select NULL
from CN_RULES_ALL_B B
where B.RULE_ID = T.RULE_ID
and B.RULESET_ID = T.ruleset_id
and B.ORG_ID= T.ORG_ID
);
update CN_RULES_ALL_TL T set (
NAME
) = (select
B.NAME
from CN_RULES_ALL_TL B
where B.RULE_ID = T.RULE_ID
and B.RULESET_ID = T.RULESET_ID
and B.LANGUAGE = T.source_lang
and B.ORG_ID= T.ORG_ID)
where (
T.RULE_ID,
T.RULESET_ID,
T.LANGUAGE
) in (select
SUBT.RULE_ID,
SUBT.RULESET_ID,
SUBT.LANGUAGE
from CN_RULES_ALL_TL SUBB, CN_RULES_ALL_TL SUBT
where SUBB.RULE_ID = SUBT.RULE_ID
and SUBB.RULESET_ID = SUBT.RULESET_ID
and SUBB.LANGUAGE = SUBT.source_lang
and SUBB.ORG_ID=SUBT.ORG_ID
and (SUBB.NAME <> SUBT.NAME
or (SUBB.NAME is null and SUBT.NAME is not null)
or (SUBB.NAME is not null and SUBT.NAME is null)
));
insert into CN_RULES_ALL_TL (
NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
RULESET_ID,
RULE_ID,
LANGUAGE,
SOURCE_LANG,
ORG_ID
) select
B.NAME,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.CREATION_DATE,
B.CREATED_BY,
B.RULESET_ID,
B.RULE_ID,
L.LANGUAGE_CODE,
B.SOURCE_LANG,
B.ORG_ID
from CN_RULES_ALL_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from CN_RULES_ALL_TL T
where T.RULE_ID = B.RULE_ID
and T.RULESET_ID = B.RULESET_ID
and T.LANGUAGE = L.language_code AND
T.ORG_ID=B.ORG_ID);
SELECT *
FROM cn_clrl_api_v;
SELECT cn_clrl_api_s.NEXTVAL
INTO l_api_id
FROM dual;
INSERT INTO cn_clrl_api
(clrl_api_id,
ruleset_name,
start_date,
end_date,
rule_name,
parent_rule_name,
revenue_class_name,
object_name,
not_flag,
value_1,
value_2,
data_flag)
VALUES
(cnclrl_tbl(i).clrl_api_id,
cnclrl_tbl(i).ruleset_name,
cnclrl_tbl(i).start_date,
cnclrl_tbl(i).end_date,
cnclrl_tbl(i).rule_name,
cnclrl_tbl(i).parent_rule_name,
cnclrl_tbl(i).revenue_class_name,
cnclrl_tbl(i).object_name,
cnclrl_tbl(i).not_flag,
cnclrl_tbl(i).value_1,
cnclrl_tbl(i).value_2,
cnclrl_tbl(i).data_flag);
IS SELECT ruleset_name, start_date, end_date
FROM cn_clrl_api
WHERE loading_status <> 'CN_INSERTED' OR loading_status IS NULL
GROUP BY ruleset_name, start_date, end_date;
IS SELECT rule_name
FROM cn_clrl_api cna1
WHERE ruleset_name = p_ruleset_name
AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL)
AND parent_rule_name NOT IN
(SELECT rule_name
FROM cn_clrl_api
WHERE ruleset_name = p_ruleset_name
AND start_date = p_start_date
AND end_date = p_end_date
AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL) );
SELECT rule_name, parent_rule_name, revenue_class_name
FROM (SELECT rule_name, parent_rule_name, revenue_class_name
FROM cn_clrl_api
WHERE ruleset_name = p_ruleset_name
AND start_date = p_start_date
AND end_date = p_end_date
AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL)
GROUP BY rule_name, parent_rule_name, revenue_class_name)
CONNECT BY PRIOR rule_name = parent_rule_name
START WITH rule_name = p_start_rule_name;
SELECT attribute_rule_name, not_flag,
value_1, value_2, data_flag, object_name
FROM cn_clrl_api
WHERE loading_status <> 'CN_INSERTED' OR loading_status IS NULL
AND ruleset_name = p_ruleset_name
AND rule_name = p_rule_name
AND parent_rule_name = p_parent_rule_name;
SELECT COUNT(1)
INTO l_count
FROM cn_rulesets
WHERE name = i.ruleset_name
AND start_date = i.start_date
AND end_date = i.end_date;
UPDATE cn_clrl_api
SET loading_status = 'CN_RULESET_INSERTED'
WHERE ruleset_name = i.ruleset_name
AND start_date = i.start_date
AND end_date = i.end_date
AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL);
UPDATE cn_clrl_api
SET loading_status = l_loading_status,
message_text = l_msg_data,
return_status = l_return_status
WHERE ruleset_name = i.ruleset_name
AND start_date = i.start_date
AND end_date = i.end_date
AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL);
UPDATE cn_clrl_api
SET loading_status = 'CN_ALREADY_EXISTS',
message_text = l_msg_data,
return_status = l_return_status
WHERE ruleset_name = i.ruleset_name
AND start_date = i.start_date
AND end_date = i.end_date
AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL);
UPDATE cn_clrl_api
SET loading_status = 'CN_RULE_INSERTED'
WHERE ruleset_name = i.ruleset_name
AND start_date = i.start_date
AND end_date = i.end_date
AND rule_name = k.rule_name
AND parent_rule_name = k.parent_rule_name
AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL);
SELECT COUNT(1)
INTO l_count
FROM cn_clrl_api
WHERE ruleset_name = i.ruleset_name
AND start_date = i.start_date
AND end_date = i.end_date
AND rule_name = k.rule_name
AND parent_rule_name = k.parent_rule_name
AND attribute_rule_name IS NOT NULL
AND (loading_status <> 'CN_INSERTED' OR loading_status IS NULL);
UPDATE cn_clrl_api
SET loading_status = l_loading_status,
return_status = l_return_status,
message_text = l_msg_data
WHERE ruleset_name = i.ruleset_name
AND start_date = i.start_date
AND end_date = i.end_date
AND rule_name = k.rule_name
AND parent_rule_name = k.parent_rule_name
AND (loading_status <> 'CN_INSERTED'
OR loading_status IS NULL);
UPDATE cn_clrl_api
SET loading_status = 'CN_INSERTED'
WHERE ruleset_name = i.ruleset_name
AND start_date = i.start_date
AND end_date = i.end_date
AND rule_name = k.rule_name
AND parent_rule_name = k.parent_rule_name
AND object_name = l.object_name
AND (loading_status <> 'CN_INSERTED'
OR loading_status IS NULL);
UPDATE cn_clrl_api
SET loading_status = l_loading_status,
return_status = l_return_status,
message_text = l_msg_data
WHERE ruleset_name = i.ruleset_name
AND start_date = i.start_date
AND end_date = i.end_date
AND rule_name = k.rule_name
AND parent_rule_name = k.parent_rule_name
AND object_name = l.object_name
AND (loading_status <> 'CN_INSERTED'
OR loading_status IS NULL);
UPDATE cn_rules_all_b SET
ruleset_id = x_ruleset_id,
revenue_class_id = x_revenue_class_id,
package_id = x_package_id,
last_update_date = sysdate,
last_updated_by = user_id,
last_update_login = 0,
org_id=x_org_id
WHERE rule_id = x_rule_id AND ruleset_id = x_ruleset_id AND ORG_ID=x_org_id;
INSERT INTO cn_rules_all_b
(rule_id,
ruleset_id,
revenue_class_id,
package_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
org_id
) values
(x_rule_id,
x_ruleset_id,
x_revenue_class_id,
x_package_id,
sysdate,
user_id,
sysdate,
user_id,
0,
x_org_id
);
UPDATE cn_rules_all_tl SET
ruleset_id = x_ruleset_id,
name = x_name,
last_update_date = sysdate,
last_updated_by = user_id,
last_update_login = 0,
org_id=x_org_id,
source_lang = userenv('LANG')
WHERE rule_id = x_rule_id AND ruleset_id = x_ruleset_id and org_id=x_org_id
AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
INSERT INTO cn_rules_all_tl
(rule_id,
ruleset_id,
name,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
language,
source_lang,
org_id)
SELECT
x_rule_id,
x_ruleset_id,
x_name,
sysdate,
user_id,
sysdate,
user_id,
0,
l.language_code,
userenv('LANG'),
x_org_id
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS
(SELECT NULL
FROM cn_rules_all_tl t
WHERE t.rule_id = x_rule_id and t.org_id=x_org_id
AND t.language = l.language_code);
UPDATE cn_rules_all_tl SET
name = x_name,
last_update_date = sysdate,
last_updated_by = user_id,
last_update_login = 0,
source_lang = userenv('LANG')
WHERE rule_id = x_rule_id
AND ruleset_id = x_ruleset_id and org_id=x_org_id
AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);