[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT LOWER(cocv.name) column_name, cocv.column_datatype data_type,cocv.data_type datatype, --RC Chnaged
--from data_type to column_datatype as per new functionality in 11i
--which will enable to distinguish between numeric and alphanumeric usage
--of the attribute columns
column_value value, high_value, low_value,
not_flag, dimension_hierarchy_id
FROM cn_attribute_rules car, cn_obj_columns_v cocv
WHERE rule_id = X_rule_id
AND car.org_id=cocv.org_id
and car.org_id=x_org_id
AND cocv.column_id = car.column_id;
SELECT attribute_rule_id, LOWER(cocv.name) column_name,
cocv.column_datatype data_type,cocv.data_type datatype,column_value value,
high_value, low_value, not_flag, dimension_hierarchy_id
FROM cn_attribute_rules car, cn_obj_columns_v cocv
WHERE rule_id = X_rule_id
and car.org_id= cocv.org_id
AND cocv.column_id = car.column_id;
SELECT *
FROM cn_rule_attr_expression
WHERE rule_id = X_rule_id
ORDER BY rule_attr_expression_id;
SELECT ruleset_id
INTO x_ruleset_id
FROM cn_rules
WHERE rule_id = X_rule_id
AND org_id=x_org_id
AND ruleset_id = g_ruleset_id;
SELECT user INTO X_userid FROM sys.dual;
SELECT cn_objects_s.NEXTVAL
INTO object_id
FROM dual;
insert into CN_OBJECTS (
OBJECT_ID, DEPENDENCY_MAP_COMPLETE, NAME, OBJECT_TYPE, REPOSITORY_ID,
OBJECT_STATUS, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, DESCRIPTION, NEXT_SYNCHRONIZATION_DATE,
SYNCHRONIZATION_FREQUENCY, DATA_LENGTH, DATA_TYPE, NULLABLE, PRIMARY_KEY,
POSITION, DIMENSION_ID, DATA_SCALE, COLUMN_TYPE, TABLE_ID, UNIQUE_FLAG,
PACKAGE_TYPE, PACKAGE_SPECIFICATION_ID, PARAMETER_LIST, RETURN_TYPE,
PROCEDURE_TYPE, PACKAGE_ID, START_VALUE, INCREMENT_VALUE, STATEMENT_TEXT,
ALIAS, TABLE_LEVEL, TABLE_TYPE, WHEN_CLAUSE, TRIGGERING_EVENT, EVENT_ID,
PUBLIC_FLAG, CHILD_FLAG, FOR_EACH_ROW, TRIGGER_TYPE, USER_COLUMN_NAME,
SEED_OBJECT_ID, PRIMARY_KEY_COLUMN_ID, USER_NAME_COLUMN_ID,
CONNECT_TO_USERNAME, CONNECT_TO_PASSWORD, CONNECT_TO_HOST, USER_NAME,
SCHEMA, FOREIGN_KEY, CLASSIFICATION_COLUMN,OBJECT_VERSION_NUMBER,ORG_ID)
values(
object_id, 'N', name,object_type, next_id,
'A', NULL, NULL, NULL, NULL,
NULL, description, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
'CLS', NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
'CN', NULL, NULL,1,x_org_id);
delete_flag VARCHAR2(1) := 'Y';
delete from cn_objects where name = package_name and object_type = 'PKS';
delete from cn_objects where name = package_name and object_type = 'PKB';
delete_flag := 'N';
IF (delete_flag = 'Y') THEN
cn_utils.delete_module(module_id, package_spec_id, package_body_id,x_org_id);
SELECT user INTO X_userid FROM sys.dual;
SELECT count(*)
INTO dummy
FROM cn_rules_hierarchy crh
WHERE parent_rule_id = X_rule_id
AND org_id=x_org_id
AND ruleset_id = x_ruleset_id;
SELECT revenue_class_id
INTO X_revenue_class
FROM cn_rules
WHERE rule_id = X_rule_id
AND ruleset_id = X_ruleset_id;
SELECT expense_ccid
INTO X_expense_ccid
FROM cn_rules
WHERE rule_id = X_rule_id
AND org_id=x_org_id
AND ruleset_id = X_ruleset_id;
SELECT liability_ccid
INTO X_liability_ccid
FROM cn_rules
WHERE rule_id = X_rule_id
AND org_id=x_org_id
AND ruleset_id = X_ruleset_id;
cn_utils.appendcr(code,'p_program_update_date DATE, ');
cn_utils.appendcr(code,'p_last_update_date DATE, ');
cn_utils.appendcr(code,'p_last_updated_by NUMBER, ');
cn_utils.appendcr(code,'p_last_update_login NUMBER, ');
cn_utils.appendcr(code,'p_program_update_date DATE, ');
cn_utils.appendcr(code,'p_last_update_date DATE, ');
cn_utils.appendcr(code,'p_last_updated_by NUMBER, ');
cn_utils.appendcr(code,'p_last_update_login NUMBER, ');
cn_utils.appindcr(code,'l_rec.program_update_date := p_program_update_date;');
cn_utils.appindcr(code,'l_rec.last_update_date := p_last_update_date;');
cn_utils.appindcr(code,'l_rec.last_updated_by := p_last_updated_by;');
cn_utils.appindcr(code,'l_rec.last_update_login := p_last_update_login;');
SELECT crh.rule_id rule_id,cr.org_id,cr.package_id package_id, cr.ruleset_id ruleset_id
FROM cn_rules_hierarchy crh,cn_rules cr
WHERE parent_rule_id = X_rule_id
AND crh.rule_id = cr.rule_id
AND crh.org_id=cr.org_id
AND crh.org_id=x_org_id
AND cr.ruleset_id = x_ruleset_id
--ORDER BY sequence_number;
SELECT revenue_class_id
INTO X_revenue_class
FROM cn_rules
WHERE rule_id = X_rule_id
AND ruleset_id = x_ruleset_id
AND org_id=x_org_id;
SELECT expense_ccid
INTO X_expense_ccid
FROM cn_rules
WHERE rule_id = X_rule_id
AND org_id=x_org_id
AND ruleset_id = x_ruleset_id;
SELECT liability_ccid
INTO X_liability_ccid
FROM cn_rules
WHERE rule_id = X_rule_id
AND org_id=x_org_id
AND ruleset_id = x_ruleset_id;
SELECT rule_id,org_id
FROM cn_rules_hierarchy crh
WHERE parent_rule_id = X_rule_id
AND org_id = x_org_id
AND ruleset_id = X_ruleset_id --RC added condition for multiple classification rulesets
ORDER BY sequence_number;
UPDATE cn_rules_all_b
SET package_id = x_package_count
WHERE rule_id = x_rule_id
AND org_id = x_org_id
AND ruleset_id = X_ruleset_id;
SELECT count(*)
INTO dummy
FROM cn_rules_hierarchy
WHERE parent_rule_id = r.rule_id
AND org_id=r.org_id ;
SELECT DISTINCT cr.rule_id rule_id,cr.org_id org_id,cr.package_id package_id
FROM cn_rules_hierarchy crh, cn_rules cr
WHERE NOT EXISTS (SELECT rule_id
FROM cn_rules_hierarchy where rule_id = crh.parent_rule_id and org_id=crh.org_id)
AND cr.rule_id = crh.parent_rule_id
AND crh.org_id=cr.org_id
and cr.org_id=x_org_id
AND cr.ruleset_id = X_ruleset_id;
SELECT DISTINCT cr.rule_id rule_id,cr.org_id org_id
FROM cn_rules cr
WHERE cr.package_id = x_package_count
and org_id = x_org_id
and ruleset_id = x_ruleset_id;
SELECT LOWER(cocv.name) dest_column, cr.ruleset_id,cr.org_id,module_type
FROM cn_rulesets cr, cn_obj_columns_v cocv
WHERE cr.destination_column_id = cocv.column_id
AND cr.repository_id = x_repository_id
AND cr.org_id = cocv.org_id
AND cr.org_id=x_org_id_in
AND ruleset_id = x_ruleset_id_in;
SELECT count(*)
INTO dummy
FROM cn_rules_hierarchy
WHERE parent_rule_id = r.rule_id
and org_id=r.org_id;
SELECT count(*)
INTO dummy
FROM cn_rules_hierarchy
WHERE parent_rule_id = rgen.rule_id and
org_id=rgen.org_id;
cn_utils.appindcr(body_code, 'SELECT ancestor_external_id');
cn_utils.appindcr(body_code, ' SELECT cdh.dim_hierarchy_id');
cn_utils.appindcr(body_code, 'SELECT * INTO row FROM cn_commission_headers WHERE commission_header_id = p_commission_header_id;');
cn_utils.appindcr(body_code, 'SELECT * INTO row FROM cn_proj_compensation_gtt WHERE line_id = p_line_id;');
SELECT cs.line_no
FROM cn_source cs
WHERE cs.object_id = p_object_id
AND substr(cs.text, 1, 25) = 'CREATE OR REPLACE PACKAGE'
AND instr(cs.text, '--START-OF-PKS') <> 0
ORDER BY line_no;
SELECT cs.line_no
FROM cn_source cs
WHERE cs.object_id = p_object_id
AND cs.text like 'END%'
ORDER BY line_no;
SELECT cs.line_no
FROM cn_source cs
WHERE cs.object_id = p_object_id
AND substr(cs.text, 1, 30) = 'CREATE OR REPLACE PACKAGE BODY'
AND instr(cs.text, '--START-OF-PKB') <> 0
ORDER BY line_no;
SELECT cs.line_no
FROM cn_source cs
WHERE cs.object_id = p_object_id
AND cs.text like 'END%'
ORDER BY line_no;
SELECT cs.text
FROM cn_source cs
WHERE cs.object_id = p_pks_object_id
AND cs.line_no between p_pks_start and (p_pks_end - 1)
ORDER BY cs.line_no;
SELECT *
FROM cn_rulesets
WHERE ruleset_id = p_ruleset_id and
org_id=p_org_id;
SELECT co.object_id
INTO l_pks_object_id
FROM cn_objects co
WHERE co.name = 'cn_clsfn_'||x_ruleset_id||'_'||x_org_id
AND co.org_id=x_org_id
AND co.object_type = 'PKS';
SELECT co.object_id
INTO l_pkb_object_id
FROM cn_objects co
WHERE co.name = 'cn_clsfn_'||x_ruleset_id||'_'||x_org_id
AND co.org_id=x_org_id
AND co.object_type = 'PKB';
SELECT substr(text, instr(text, 'cn_clsfn'),
instr(substr(text, instr(text, 'cn_clsfn'), length(text)),
' AS --START-OF'))
INTO l_pkg_name
FROM cn_source
WHERE line_no = l_pks_start
AND object_id = l_pks_object_id;
SELECT user
INTO l_applsys_schema
FROM dual;
insert_newlines => 'FALSE',
comp_error => l_comp_error);
SELECT substr(text, instr(text, 'cn_clsfn_'),
instr(substr(text, instr(text, 'cn_clsfn_'), length(text)),
' AS --START-OF'))
INTO l_pkg_name
FROM cn_source
WHERE line_no = l_pkb_start
AND object_id = l_pkb_object_id;
insert_newlines => 'TRUE',
comp_error => l_comp_error);
(SELECT
'*** '||type||' '||LOWER(name)||' LINE: '||line||'/'||position||fnd_global.local_CHR(10)||text||fnd_global.local_CHR(10) outstr
FROM user_errors WHERE name = l_pkg_name)
LOOP
-- If there is enough space, append this error to the end of the
-- Errbuf, otherwise aappend as mauch as possible and then quit
-- the loop.
IF LENGTHB(x_errbuf) + LENGTHB(rec.outstr) <= l_max_len THEN
x_errbuf := x_errbuf || rec.outstr;
cn_syin_rulesets_pkg.update_row(l_get_ruleset_data_rec.ruleset_id,
l_get_ruleset_data_rec.object_version_number,
l_get_ruleset_data_rec.ruleset_status,
l_get_ruleset_data_rec.destination_column_id,
l_get_ruleset_data_rec.repository_id,
l_get_ruleset_data_rec.start_date,
l_get_ruleset_data_rec.end_date,
l_get_ruleset_data_rec.name,
l_get_ruleset_data_rec.module_type,
null,
null,
null,
l_get_ruleset_data_rec.org_id);
SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) INTO x_cached_org_id
FROM DUAL;