[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* To insert data */
PROCEDURE insert_data(
errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY VARCHAR2
,p_batch_id IN NUMBER
)
IS
CURSOR c_constraint_data IS
SELECT cst_interface_id,
cst_name,
risk_name,
cst_start_date,
cst_type_code,
cst_entries_function_id,
cst_entries_resp_id
FROM amw_constraint_interface
WHERE batch_id = p_batch_id;
SELECT cst_interface_id
FROM amw_constraint_interface
WHERE batch_id = p_batch_id
AND cst_type_code in ('ALL','ME','SET')
AND cst_violat_obj_type = 'FUNC'
AND cst_entries_function_id IS NOT NULL
AND NOT EXISTS
( SELECT 'Y'
FROM fnd_form_functions
WHERE function_id = cst_entries_function_id
)
UNION
SELECT cst_interface_id
FROM amw_constraint_interface
WHERE batch_id = p_batch_id
AND cst_type_code in ('ALL','ME','SET')
AND cst_violat_obj_type = 'CP'
AND cst_entries_function_id IS NOT NULL
AND NOT EXISTS
(
SELECT 'Y'
FROM fnd_request_group_units rgu ,
fnd_concurrent_programs cpv
WHERE rgu.request_unit_type = 'P'
AND rgu.request_unit_id = cpv.concurrent_program_id
AND cpv.enabled_flag = 'Y'
AND cpv.concurrent_program_id =cst_entries_function_id
);
SELECT cst_interface_id
FROM amw_constraint_interface
WHERE batch_id = p_batch_id
AND cst_entries_resp_id IS NOT NULL
AND NOT EXISTS
( SELECT 'Y'
FROM FND_RESPONSIBILITY
WHERE responsibility_id = cst_entries_resp_id
AND start_date <= sysdate
AND (end_date >= sysdate OR end_date IS NULL)
);
SELECT cst_interface_id
FROM amw_constraint_interface
WHERE batch_id = p_batch_id
AND NOT EXISTS
( SELECT 'Y'
FROM amw_lookups
WHERE lookup_code = cst_type_code
AND lookup_type='AMW_CONSTRAINT_TYPE'
AND enabled_flag ='Y'
AND (end_date_active > SYSDATE OR end_date_active IS NULL)
);
SELECT cst_interface_id,
cst_entries_resp_id,
cst_entries_function_id
FROM amw_constraint_interface
WHERE batch_id = p_batch_id
AND (substr(cst_type_code,1,4) = 'RESP')
AND (cst_entries_resp_id IS NULL OR cst_entries_function_id IS NOT NULL);
SELECT cst_interface_id,
cst_entries_resp_id,
cst_entries_function_id
FROM amw_constraint_interface
WHERE batch_id = p_batch_id
AND cst_type_code IN ('ALL','ME','SET')
AND (cst_entries_function_id IS NULL OR cst_entries_resp_id IS NOT NULL);
SELECT cst_interface_id
FROM amw_constraint_interface
WHERE batch_id = p_batch_id
AND cst_type_code in ('ALL','ME','SET')
AND cst_entries_function_id IS NOT NULL
AND (cst_violat_obj_type IS NULL OR cst_violat_obj_type NOT IN ('FUNC','CP') );
SELECT cst_interface_id
FROM amw_constraint_interface
WHERE batch_id = p_batch_id
AND cst_type_code in ('SET','RESPSET')
AND (cst_entries_group_code IS NULL OR cst_entries_group_code NOT IN ('1','2'));
update_interface_with_error(v_error_msg,cstfunc_rec.cst_interface_id);
update_interface_with_error(v_error_msg,cstresp_rec.cst_interface_id);
update_interface_with_error(v_error_msg,typecode_rec.cst_interface_id);
update_interface_with_error(v_error_msg,cstresptype_rec.cst_interface_id);
update_interface_with_error(v_error_msg,cstfunctype_rec.cst_interface_id);
update_interface_with_error(v_error_msg,objtype_rec.cst_interface_id);
update_interface_with_error(v_error_msg,grpcode_rec.cst_interface_id);
END insert_data;
PROCEDURE update_interface_with_error (
p_err_msg IN VARCHAR2
,p_interface_id IN NUMBER
)
IS
l_interface_status amw_constraint_interface.interface_status%TYPE;
ROLLBACK; -- rollback any inserts done during the current loop process
SELECT interface_status INTO l_interface_status FROM amw_constraint_interface
WHERE cst_interface_id = p_interface_id;
UPDATE amw_constraint_interface SET interface_status = l_interface_status
,error_flag = 'Y'
WHERE cst_interface_id = p_interface_id;
END update_interface_with_error;
SELECT Interface_id
FROM amw_cst_waiver_interface
WHERE batch_id = p_batch_id
AND constraint_rev_id IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
SELECT Interface_id
FROM amw_cst_waiver_interface
WHERE constraint_rev_id IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
SELECT Interface_id
FROM amw_cst_waiver_interface
WHERE type_code in ('RESPALL','RESPME','RESPSET')
AND object_type = 'RESP'
AND batch_id = p_batch_id
AND pk1 IS NOT NULL
AND pk2 IS NOT NULL
AND (process_flag IS NULL OR process_flag = 'N');
SELECT Interface_id
FROM amw_cst_waiver_interface
WHERE type_code in ('RESPALL','RESPME','RESPSET')
AND object_type = 'RESP'
AND pk1 IS NOT NULL
AND pk2 IS NOT NULL
AND (process_flag IS NULL OR process_flag = 'N');
SELECT Interface_id
FROM amw_cst_waiver_interface
WHERE object_type = 'USER'
AND batch_id = p_batch_id
AND pk1 IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
SELECT Interface_id
FROM amw_cst_waiver_interface
WHERE object_type = 'USER'
AND pk1 IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
SELECT Interface_id
FROM amw_cst_waiver_interface
WHERE object_type = 'RESP'
AND batch_id = p_batch_id
AND pk1 IS NULL
AND pk2 IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
SELECT Interface_id
FROM amw_cst_waiver_interface
WHERE object_type = 'RESP'
AND pk1 IS NULL
AND pk2 IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
SELECT Interface_id
FROM amw_cst_waiver_interface
WHERE TRUNC(start_date)
SELECT Interface_id
FROM amw_cst_waiver_interface
WHERE TRUNC(start_date)
SELECT interface_id, decode(sign(sysdate-acv.start_date),1,sysdate,acv.start_date) start_date
FROM amw_constraints_vl acv, amw_cst_waiver_interface acwi
WHERE acwi.constraint_name = acv.constraint_name
AND acwi.constraint_name IS NOT NULL
AND acv.start_date IS NOT NULL
AND TRUNC(acwi.start_date)
SELECT interface_id, decode(sign(sysdate-acv.start_date),1,sysdate,acv.start_date) start_date
FROM amw_constraints_vl acv, amw_cst_waiver_interface acwi
WHERE acwi.constraint_name = acv.constraint_name
AND acwi.constraint_name IS NOT NULL
AND acv.start_date IS NOT NULL
AND TRUNC(acwi.start_date)
SELECT Interface_id
FROM amw_cst_waiver_interface
WHERE ( TRUNC(end_date)< TRUNC(start_date)
OR TRUNC(end_date)
SELECT Interface_id
FROM amw_cst_waiver_interface
WHERE ( TRUNC(end_date)< TRUNC(start_date)
OR TRUNC(end_date)
SELECT acwi.Interface_id
FROM amw_cst_waiver_interface acwi,
amw_constraint_waivers_b cstw
WHERE acwi.object_type = 'USER'
AND cstw.object_type = 'USER'
AND acwi.batch_id = p_batch_id
AND cstw.constraint_rev_id= acwi.constraint_rev_id
AND cstw.pk1 = acwi.pk1
AND TRUNC(cstw.start_date)=TRUNC(acwi.start_date)
AND (cstw.end_date IS NULL OR TRUNC(cstw.end_date)=TRUNC(acwi.end_date) )
AND acwi.constraint_rev_id IS NOT NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
UNION
SELECT acwi.Interface_id
FROM amw_cst_waiver_interface acwi
WHERE acwi.object_type = 'USER'
AND acwi.batch_id = p_batch_id
AND acwi.constraint_rev_id IS NOT NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
AND EXISTS ( SELECT 'Y'
FROM amw_cst_waiver_interface acw
WHERE acw.batch_id = p_batch_id
AND acw.object_type = 'USER'
AND acw.pk1 = acwi.pk1
AND acw.object_type = acwi.object_type
AND acw.Interface_id <> acwi.Interface_id
AND acw.constraint_rev_id = acwi.constraint_rev_id
AND acw.constraint_rev_id IS NOT NULL
AND (acw.process_flag IS NULL OR acw.process_flag = 'N')
);
SELECT acwi.Interface_id
FROM amw_cst_waiver_interface acwi,
amw_constraint_waivers_b cstw
WHERE acwi.object_type = 'USER'
AND cstw.object_type = 'USER'
AND cstw.constraint_rev_id= acwi.constraint_rev_id
AND cstw.pk1 = acwi.pk1
AND TRUNC(cstw.start_date)=TRUNC(acwi.start_date)
AND (cstw.end_date IS NULL OR TRUNC(cstw.end_date)=TRUNC(acwi.end_date) )
AND acwi.constraint_rev_id IS NOT NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
UNION
SELECT acwi.Interface_id
FROM amw_cst_waiver_interface acwi
WHERE acwi.object_type = 'USER'
AND acwi.constraint_rev_id IS NOT NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
AND EXISTS ( SELECT 'Y'
FROM amw_cst_waiver_interface acw
WHERE acw.object_type = 'USER'
AND acw.pk1 = acwi.pk1
AND acw.object_type = acwi.object_type
AND acw.Interface_id <> acwi.Interface_id
AND acw.constraint_rev_id = acwi.constraint_rev_id
AND acw.constraint_rev_id IS NOT NULL
AND (acw.process_flag IS NULL OR acw.process_flag = 'N')
);
SELECT acwi.Interface_id
FROM amw_cst_waiver_interface acwi,
amw_constraint_waivers_b cstw
WHERE acwi.object_type = 'RESP'
AND cstw.object_type = 'RESP'
AND acwi.batch_id = p_batch_id
AND cstw.constraint_rev_id= acwi.constraint_rev_id
AND cstw.pk1 = acwi.pk1
AND cstw.pk2 = acwi.pk2
AND TRUNC(cstw.start_date)=TRUNC(acwi.start_date)
AND (cstw.end_date IS NULL OR TRUNC(cstw.end_date)=TRUNC(acwi.end_date))
AND acwi.constraint_rev_id IS NOT NULL
AND acwi.type_code in ('ALL','ME','SET')
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
UNION
SELECT acwi.Interface_id
FROM amw_cst_waiver_interface acwi
WHERE acwi.object_type = 'RESP'
AND acwi.batch_id = p_batch_id
AND acwi.constraint_rev_id IS NOT NULL
AND acwi.type_code in ('ALL','ME','SET')
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
AND EXISTS ( SELECT 'Y'
FROM amw_cst_waiver_interface acw
WHERE acw.batch_id = p_batch_id
AND acw.object_type = 'RESP'
AND acw.pk1 = acwi.pk1
AND acw.pk2 = acwi.pk2
AND acw.object_type = acwi.object_type
AND acw.Interface_id <> acwi.Interface_id
AND acw.constraint_rev_id = acwi.constraint_rev_id
AND acw.constraint_rev_id IS NOT NULL
AND (acw.process_flag IS NULL OR acw.process_flag = 'N')
);
SELECT acwi.Interface_id
FROM amw_cst_waiver_interface acwi,
amw_constraint_waivers_b cstw
WHERE acwi.object_type = 'RESP'
AND cstw.object_type = 'RESP'
AND cstw.constraint_rev_id= acwi.constraint_rev_id
AND cstw.pk1 = acwi.pk1
AND cstw.pk2 = acwi.pk2
AND TRUNC(cstw.start_date)=TRUNC(acwi.start_date)
AND (cstw.end_date IS NULL OR TRUNC(cstw.end_date)=TRUNC(acwi.end_date))
AND acwi.constraint_rev_id IS NOT NULL
AND acwi.type_code in ('ALL','ME','SET')
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
UNION
SELECT acwi.Interface_id
FROM amw_cst_waiver_interface acwi
WHERE acwi.object_type = 'RESP'
AND acwi.constraint_rev_id IS NOT NULL
AND acwi.type_code in ('ALL','ME','SET')
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
AND EXISTS ( SELECT 'Y'
FROM amw_cst_waiver_interface acw
WHERE acw.object_type = 'RESP'
AND acw.pk1 = acwi.pk1
AND acw.pk2 = acwi.pk2
AND acw.object_type = acwi.object_type
AND acw.Interface_id <> acwi.Interface_id
AND acw.constraint_rev_id = acwi.constraint_rev_id
AND acw.constraint_rev_id IS NOT NULL
AND (acw.process_flag IS NULL OR acw.process_flag = 'N')
);
UPDATE amw_cst_waiver_interface
SET last_update_date = SYSDATE
WHERE batch_id = p_batch_id
AND last_update_date IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
UPDATE amw_cst_waiver_interface
SET creation_date = SYSDATE
WHERE batch_id = p_batch_id
AND creation_date IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
UPDATE amw_cst_waiver_interface
SET last_updated_by = g_user_id
WHERE batch_id = p_batch_id
AND last_updated_by IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
UPDATE amw_cst_waiver_interface
SET created_by = g_user_id
WHERE batch_id = p_batch_id
AND created_by IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
UPDATE amw_cst_waiver_interface
SET last_update_login = g_user_id
WHERE batch_id = p_batch_id
AND last_update_login IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
UPDATE amw_cst_waiver_interface acwi
SET acwi.constraint_rev_id = ( SELECT acv.constraint_rev_id
FROM amw_constraints_vl acv
WHERE acwi.constraint_name = acv.constraint_name
AND acv.start_date IS NOT NULL
AND (acv.end_date IS NULL OR acv.end_date>=sysdate))
WHERE acwi.batch_id = p_batch_id
AND acwi.constraint_name IS NOT NULL
AND acwi.constraint_rev_id IS NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
UPDATE amw_cst_waiver_interface acwi
SET acwi.type_code = ( SELECT acv.type_code
FROM amw_constraints_vl acv
WHERE acwi.constraint_rev_id = acv.constraint_rev_id
AND acv.start_date IS NOT NULL
AND (acv.end_date IS NULL OR acv.end_date>=sysdate))
WHERE acwi.batch_id = p_batch_id
AND acwi.constraint_name IS NOT NULL
AND acwi.constraint_rev_id IS NOT NULL
AND acwi.type_code IS NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
UPDATE amw_cst_waiver_interface acwi
SET acwi.pk1 = ( SELECT user_id
FROM fnd_user usr
WHERE usr.user_name = acwi.user_name
AND usr.start_date IS NOT NULL
AND (usr.end_date IS NULL OR usr.end_date>=sysdate))
WHERE acwi.batch_id = p_batch_id
AND acwi.object_type = 'USER'
AND acwi.user_name IS NOT NULL
AND acwi.pk1 IS NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
UPDATE amw_cst_waiver_interface acwi
SET acwi.pk2 = ( SELECT application_id
FROM fnd_application appl
WHERE appl.Application_short_name = acwi.application_short_name)
WHERE acwi.batch_id = p_batch_id
AND acwi.object_type = 'RESP'
AND acwi.application_short_name IS NOT NULL
AND acwi.pk2 IS NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
UPDATE amw_cst_waiver_interface acwi
SET acwi.pk1 = ( SELECT responsibility_id
FROM fnd_responsibility_vl resp
WHERE resp.application_id = acwi.pk2
AND resp.responsibility_name = acwi.responsibility_name)
WHERE acwi.batch_id = p_batch_id
AND acwi.object_type = 'RESP'
AND acwi.responsibility_name IS NOT NULL
AND acwi.pk2 IS NOT NULL
AND acwi.pk1 IS NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
UPDATE amw_cst_waiver_interface acwi
SET acwi.pk2 = NULL
WHERE acwi.batch_id = p_batch_id
AND acwi.object_type = 'RESP'
AND acwi.pk1 IS NULL
AND pk2 IS NOT NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
update_waiver_intf_with_error(v_error_msg,invldcst_rec.interface_id);
UPDATE amw_cst_waiver_interface acwi
SET acwi.start_date = invldstdate_rec.start_date
WHERE acwi.interface_id=invldstdate_rec.interface_id;
update_waiver_intf_with_error(v_error_msg,invldrespcst_rec.interface_id);
update_waiver_intf_with_error(v_error_msg,invlduser_rec.interface_id);
update_waiver_intf_with_error(v_error_msg,invldresp_rec.interface_id);
update_waiver_intf_with_error(v_error_msg,invldstdate_rec.interface_id);
update_waiver_intf_with_error(v_error_msg,invldenddate_rec.interface_id);
update_waiver_intf_with_error(v_error_msg,dupuserwaiv_rec.interface_id);
update_waiver_intf_with_error(v_error_msg,duprespwaiv_rec.interface_id);
UPDATE amw_cst_waiver_interface
SET error_flag = 'Y',
interface_status = 'Please correct the invalid waiver defined for this Constraint'
WHERE error_flag IS NULL
AND batch_id = p_batch_id
AND (process_flag IS NULL OR process_flag = 'N')
AND constraint_rev_id IN ( SELECT DISTINCT constraint_rev_id
FROM amw_cst_waiver_interface
WHERE error_flag = 'Y'
AND batch_id = p_batch_id
AND (process_flag IS NULL OR process_flag = 'N') );
constraint waiver id by executing select sequence.nextval.
This also helps us to insert all the data in one single query.
*/
UPDATE amw_cst_waiver_interface
SET constraint_waiver_id = amw_constraint_waiver_s.nextval
WHERE error_flag IS NULL
AND (process_flag IS NULL OR process_flag = 'N')
AND batch_id = p_batch_id;
Insert the valid constraint wavers into the amw_constraint_waivers_b
*/
INSERT INTO amw_constraint_waivers_b(
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
security_group_id,
constraint_rev_id,
object_type,
pk1,
pk2,
pk3,
pk4,
pk5,
start_date,
end_date,
constraint_waiver_id,
object_version_number
)
SELECT acwi.last_update_date,
acwi.last_updated_by,
acwi.last_update_login,
acwi.creation_date,
acwi.created_by,
NULL,
acwi.constraint_rev_id,
acwi.object_type,
acwi.pk1,
acwi.pk2,
acwi.pk3,
acwi.pk4,
acwi.pk5,
acwi.start_date,
acwi.end_date,
acwi.constraint_waiver_id,
1
FROM amw_cst_waiver_interface acwi
WHERE acwi.error_flag IS NULL
AND acwi.batch_id = p_batch_id
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
Insert the valid constraint wavers into the amw_constraint_waivers_tl
*/
INSERT INTO amw_constraint_waivers_tl (
constraint_waiver_id,
justification,
language,
source_lang,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
security_group_id
)
SELECT acwi.constraint_waiver_id,
trim(acwi.justification),
l.language_code,
userenv('LANG'),
acwi.last_update_date,
acwi.last_updated_by,
acwi.creation_date,
acwi.created_by,
acwi.last_update_login,
NULL
FROM fnd_languages l,
amw_cst_waiver_interface acwi
WHERE l.installed_flag IN ('I', 'B')
AND acwi.error_flag IS NULL
AND acwi.batch_id = p_batch_id
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
AND NOT EXISTS ( SELECT NULL
FROM amw_constraint_waivers_tl t
WHERE t.constraint_waiver_id = acwi.constraint_waiver_id
AND t.language = l.language_code);
DELETE FROM amw_cst_waiver_interface
WHERE batch_id = p_batch_id
AND error_flag IS NULL;
UPDATE amw_cst_waiver_interface
SET process_flag = 'Y'
WHERE batch_id = p_batch_id
AND error_flag IS NULL;
UPDATE amw_cst_waiver_interface
SET last_update_date = SYSDATE
WHERE last_update_date IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
UPDATE amw_cst_waiver_interface
SET creation_date = SYSDATE
WHERE creation_date IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
UPDATE amw_cst_waiver_interface
SET last_updated_by = g_user_id
WHERE last_updated_by IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
UPDATE amw_cst_waiver_interface
SET created_by = g_user_id
WHERE created_by IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
UPDATE amw_cst_waiver_interface
SET last_update_login = g_user_id
WHERE last_update_login IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
UPDATE amw_cst_waiver_interface acwi
SET acwi.constraint_rev_id = ( SELECT acv.constraint_rev_id
FROM amw_constraints_vl acv
WHERE acwi.constraint_name = acv.constraint_name
AND acv.start_date IS NOT NULL
AND (acv.end_date IS NULL OR acv.end_date>=sysdate))
WHERE acwi.constraint_name IS NOT NULL
AND acwi.constraint_rev_id IS NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
UPDATE amw_cst_waiver_interface acwi
SET acwi.type_code = ( SELECT acv.type_code
FROM amw_constraints_vl acv
WHERE acwi.constraint_rev_id = acv.constraint_rev_id
AND acv.start_date IS NOT NULL
AND (acv.end_date IS NULL OR acv.end_date>=sysdate))
WHERE acwi.constraint_name IS NOT NULL
AND acwi.constraint_rev_id IS NOT NULL
AND acwi.type_code IS NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
UPDATE amw_cst_waiver_interface acwi
SET acwi.pk1 = ( SELECT user_id
FROM fnd_user usr
WHERE usr.user_name = acwi.user_name
AND usr.start_date IS NOT NULL
AND (usr.end_date IS NULL OR usr.end_date>=sysdate))
WHERE acwi.object_type = 'USER'
AND acwi.user_name IS NOT NULL
AND acwi.pk1 IS NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
UPDATE amw_cst_waiver_interface acwi
SET acwi.pk2 = ( SELECT application_id
FROM fnd_application appl
WHERE appl.Application_short_name = acwi.application_short_name)
WHERE acwi.object_type = 'RESP'
AND acwi.application_short_name IS NOT NULL
AND acwi.pk2 IS NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
UPDATE amw_cst_waiver_interface acwi
SET acwi.pk1 = ( SELECT responsibility_id
FROM fnd_responsibility_vl resp
WHERE resp.application_id = acwi.pk2
AND resp.responsibility_name = acwi.responsibility_name)
WHERE acwi.object_type = 'RESP'
AND acwi.responsibility_name IS NOT NULL
AND acwi.pk2 IS NOT NULL
AND acwi.pk1 IS NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
UPDATE amw_cst_waiver_interface acwi
SET acwi.pk2 = NULL
WHERE acwi.object_type = 'RESP'
AND acwi.pk1 IS NULL
AND pk2 IS NOT NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
update_waiver_intf_with_error(v_error_msg,invldcst_rec.interface_id);
UPDATE amw_cst_waiver_interface acwi
SET acwi.start_date = invldstdate_rec.start_date
WHERE acwi.interface_id=invldstdate_rec.interface_id;
update_waiver_intf_with_error(v_error_msg,invldrespcst_rec.interface_id);
update_waiver_intf_with_error(v_error_msg,invlduser_rec.interface_id);
update_waiver_intf_with_error(v_error_msg,invldresp_rec.interface_id);
update_waiver_intf_with_error(v_error_msg,invldstdate_rec.interface_id);
update_waiver_intf_with_error(v_error_msg,invldenddate_rec.interface_id);
update_waiver_intf_with_error(v_error_msg,dupuserwaiv_rec.interface_id);
update_waiver_intf_with_error(v_error_msg,duprespwaiv_rec.interface_id);
UPDATE amw_cst_waiver_interface
SET error_flag = 'Y',
interface_status = 'Please correct the invalid waiver defined for this Constraint'
WHERE error_flag IS NULL
AND (process_flag IS NULL OR process_flag = 'N')
AND constraint_rev_id IN ( SELECT DISTINCT constraint_rev_id
FROM amw_cst_waiver_interface
WHERE error_flag = 'Y');
constraint waiver id by executing select sequence.nextval.
This also helps us to insert all the data in one single query.
*/
UPDATE amw_cst_waiver_interface
SET constraint_waiver_id = amw_constraint_waiver_s.nextval
WHERE error_flag IS NULL
AND (process_flag IS NULL OR process_flag = 'N');
Insert the valid constraint wavers into the amw_constraint_waivers_b
*/
INSERT INTO amw_constraint_waivers_b(
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
security_group_id,
constraint_rev_id,
object_type,
pk1,
pk2,
pk3,
pk4,
pk5,
start_date,
end_date,
constraint_waiver_id,
object_version_number
)
SELECT acwi.last_update_date,
acwi.last_updated_by,
acwi.last_update_login,
acwi.creation_date,
acwi.created_by,
NULL,
acwi.constraint_rev_id,
acwi.object_type,
acwi.pk1,
acwi.pk2,
acwi.pk3,
acwi.pk4,
acwi.pk5,
acwi.start_date,
acwi.end_date,
acwi.constraint_waiver_id,
1
FROM amw_cst_waiver_interface acwi
WHERE acwi.error_flag IS NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
Insert the valid constraint wavers into the amw_constraint_waivers_tl
*/
INSERT INTO amw_constraint_waivers_tl (
constraint_waiver_id,
justification,
language,
source_lang,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
security_group_id
)
SELECT acwi.constraint_waiver_id,
trim(acwi.justification),
l.language_code,
userenv('LANG'),
acwi.last_update_date,
acwi.last_updated_by,
acwi.creation_date,
acwi.created_by,
acwi.last_update_login,
NULL
FROM fnd_languages l,
amw_cst_waiver_interface acwi
WHERE l.installed_flag IN ('I', 'B')
AND acwi.error_flag IS NULL
AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
AND NOT EXISTS ( SELECT NULL
FROM amw_constraint_waivers_tl t
WHERE t.constraint_waiver_id = acwi.constraint_waiver_id
AND t.language = l.language_code);
DELETE FROM amw_cst_waiver_interface
WHERE error_flag IS NULL;
UPDATE amw_cst_waiver_interface
SET process_flag = 'Y'
WHERE error_flag IS NULL;
PROCEDURE update_waiver_intf_with_error (
p_err_msg IN VARCHAR2,
p_interface_id IN NUMBER
)
IS
l_interface_status amw_cst_waiver_interface.interface_status%TYPE;
SELECT interface_status
INTO l_interface_status
FROM amw_cst_waiver_interface
WHERE interface_id = p_interface_id;
UPDATE amw_cst_waiver_interface
SET interface_status = l_interface_status,
error_flag = 'Y'
WHERE interface_id = p_interface_id;
END update_waiver_intf_with_error;
PROCEDURE cst_table_update_report (
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2
) is
TYPE G_NUMBER_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
SELECT distinct acv.CONSTRAINT_REV_ID,
acv.CONSTRAINT_NAME,
avue.RESPONSIBILITY_ID
FROM AMW_CONSTRAINTS_VL acv,
AMW_VIOLATIONS av,
AMW_VIOLATION_USERS avu,
AMW_VIOLAT_USER_ENTRIES avue
WHERE acv.START_DATE<= SYSDATE
AND (acv.END_DATE IS NULL OR acv.END_DATE>=SYSDATE)
AND acv.CONSTRAINT_REV_ID=av.CONSTRAINT_REV_ID
AND av.VIOLATION_ID=avu.VIOLATION_ID
AND av.STATUS_CODE <> 'NA'
AND avu.USER_VIOLATION_ID=avue.USER_VIOLATION_ID
AND avue.RESPONSIBILITY_ID IS NOT NULL
AND 1 < ( SELECT COUNT(1)
FROM FND_RESPONSIBILITY resp
WHERE resp.START_DATE <= SYSDATE
AND (resp.END_DATE >= SYSDATE or resp.END_DATE IS NULL)
AND resp.RESPONSIBILITY_ID=avue.RESPONSIBILITY_ID);
SELECT acv.CONSTRAINT_REV_ID,
acv.CONSTRAINT_NAME,
ace.OBJECT_TYPE,
ace.FUNCTION_ID
FROM AMW_CONSTRAINTS_VL acv,
AMW_CONSTRAINT_ENTRIES ace
WHERE acv.START_DATE<= SYSDATE
AND (acv.END_DATE IS NULL OR acv.END_DATE>=SYSDATE)
AND acv.CONSTRAINT_REV_ID=ace.CONSTRAINT_REV_ID
AND ace.OBJECT_TYPE = 'RESP'
AND 1 < ( SELECT COUNT(1)
FROM FND_RESPONSIBILITY resp
WHERE resp.START_DATE <= SYSDATE
AND (resp.END_DATE >= SYSDATE or resp.END_DATE IS NULL)
AND resp.RESPONSIBILITY_ID=ace.FUNCTION_ID)
UNION ALL
SELECT acv.CONSTRAINT_REV_ID,
acv.CONSTRAINT_NAME,
ace.OBJECT_TYPE,
ace.FUNCTION_ID
FROM AMW_CONSTRAINTS_VL acv,
AMW_CONSTRAINT_ENTRIES ace
WHERE acv.START_DATE<= SYSDATE
AND (acv.END_DATE IS NULL OR acv.END_DATE>=SYSDATE)
AND acv.CONSTRAINT_REV_ID=ace.CONSTRAINT_REV_ID
AND ace.OBJECT_TYPE = 'CP'
AND 1 < ( SELECT COUNT(1)
FROM FND_CONCURRENT_PROGRAMS conc
WHERE conc.CONCURRENT_PROGRAM_ID=ace.FUNCTION_ID
AND ENABLED_FLAG='Y');
SELECT DISTINCT acv.CONSTRAINT_REV_ID,
acv.CONSTRAINT_NAME,
avue.FUNCTION_ID
FROM AMW_CONSTRAINTS_VL acv,
AMW_VIOLATIONS av,
AMW_VIOLATION_USERS avu,
AMW_VIOLAT_USER_ENTRIES avue
WHERE acv.CONSTRAINT_REV_ID=av.CONSTRAINT_REV_ID
AND av.VIOLATION_ID=avu.VIOLATION_ID
AND avu.USER_VIOLATION_ID=avue.USER_VIOLATION_ID
AND avue.OBJECT_TYPE='CP'
AND av.STATUS_CODE <> 'NA'
AND 1 < ( SELECT COUNT(1)
FROM FND_CONCURRENT_PROGRAMS conc
WHERE conc.CONCURRENT_PROGRAM_ID=avue.FUNCTION_ID
AND ENABLED_FLAG='Y');
SELECT DISTINCT acv.CONSTRAINT_REV_ID,
acv.CONSTRAINT_NAME,
avre.FUNCTION_ID
FROM AMW_VIOLATIONS av,
AMW_CONSTRAINTS_VL acv,
AMW_VIOLATION_RESP avr,
AMW_VIOLAT_RESP_ENTRIES avre
WHERE av.CONSTRAINT_REV_ID = acv.CONSTRAINT_REV_ID
AND av.VIOLATION_ID = avr.VIOLATION_ID
AND avr.RESP_VIOLATION_ID = avre.RESP_VIOLATION_ID
AND avre.OBJECT_TYPE='CP'
AND av.STATUS_CODE <> 'NA'
AND 1 < ( SELECT COUNT(1)
FROM FND_CONCURRENT_PROGRAMS conc
WHERE conc.CONCURRENT_PROGRAM_ID = avre.FUNCTION_ID
AND ENABLED_FLAG='Y');
SELECT acwb.CONSTRAINT_REV_ID,
acv.CONSTRAINT_NAME,
acwb.PK1
FROM AMW_CONSTRAINT_WAIVERS_B acwb,
AMW_CONSTRAINTs_VL acv
WHERE acwb.CONSTRAINT_REV_ID =acv.CONSTRAINT_REV_ID
AND acwb.OBJECT_TYPE='RESP'
AND 1 < ( SELECT COUNT(1)
FROM FND_RESPONSIBILITY resp
WHERE resp.START_DATE <= SYSDATE
AND (resp.END_DATE >= SYSDATE or resp.END_DATE IS NULL)
AND resp.RESPONSIBILITY_ID=acwb.PK1);
l_cst_rev_id_list.delete();
l_cst_name_list.delete();
l_resp_id_list.delete();
l_appl_id_list.delete();
l_resp_name_list.delete();
l_appl_name_list.delete();
SELECT APPL.APPLICATION_ID,APPL.APPLICATION_NAME,RESP.RESPONSIBILITY_NAME
BULK COLLECT INTO l_appl_id_list,
l_appl_name_list,
l_resp_name_list
FROM FND_RESPONSIBILITY_VL RESP,
FND_APPLICATION_VL APPL
WHERE RESP.RESPONSIBILITY_ID = l_resp_id_list(i)
AND RESP.START_DATE <= SYSDATE
AND (RESP.END_DATE >= SYSDATE OR RESP.END_DATE IS NULL)
AND RESP.APPLICATION_ID=APPL.APPLICATION_ID;
l_cst_rev_id_list.delete();
l_cst_name_list.delete();
l_function_id_list.delete();
l_object_type_list.delete();
l_appl_id_list.delete();
l_appl_name_list.delete();
l_resp_name_list.delete();
SELECT APPL.APPLICATION_ID,APPL.APPLICATION_NAME,RESP.RESPONSIBILITY_NAME
BULK COLLECT INTO l_appl_id_list,
l_appl_name_list,
l_resp_name_list
FROM FND_RESPONSIBILITY_VL RESP,
FND_APPLICATION_VL APPL
WHERE RESP.RESPONSIBILITY_ID = l_function_id_list(i)
AND RESP.START_DATE <= SYSDATE
AND (RESP.END_DATE >= SYSDATE OR RESP.END_DATE IS NULL)
AND RESP.APPLICATION_ID=APPL.APPLICATION_ID
ORDER BY APPLICATION_ID;
l_appl_id_list.delete();
l_cp_name_list.delete();
l_appl_name_list.delete();
SELECT appl.APPLICATION_ID,appl.APPLICATION_NAME,conc.USER_CONCURRENT_PROGRAM_NAME
BULK COLLECT INTO l_appl_id_list,
l_appl_name_list,
l_cp_name_list
FROM FND_CONCURRENT_PROGRAMS_VL conc,
FND_APPLICATION_VL appl
WHERE conc.CONCURRENT_PROGRAM_ID=l_function_id_list(i)
AND conc.APPLICATION_ID=appl.APPLICATION_ID
AND conc.ENABLED_FLAG='Y'
ORDER BY APPLICATION_ID;
l_cst_rev_id_list.delete();
l_cst_name_list.delete();
l_function_id_list.delete();
l_appl_id_list.delete();
l_cp_name_list.delete();
l_appl_name_list.delete();
SELECT appl.APPLICATION_ID,appl.APPLICATION_NAME,conc.USER_CONCURRENT_PROGRAM_NAME
BULK COLLECT INTO l_appl_id_list,
l_appl_name_list,
l_cp_name_list
FROM FND_CONCURRENT_PROGRAMS_VL conc,
FND_APPLICATION_VL appl
WHERE conc.CONCURRENT_PROGRAM_ID=l_function_id_list(i)
AND conc.APPLICATION_ID=appl.APPLICATION_ID
AND conc.ENABLED_FLAG='Y';
l_cst_rev_id_list.delete();
l_cst_name_list.delete();
l_function_id_list.delete();
l_appl_id_list.delete();
l_cp_name_list.delete();
l_appl_name_list.delete();
SELECT appl.APPLICATION_ID,appl.APPLICATION_NAME,conc.USER_CONCURRENT_PROGRAM_NAME
BULK COLLECT INTO l_appl_id_list,
l_appl_name_list,
l_cp_name_list
FROM FND_CONCURRENT_PROGRAMS_VL conc,
FND_APPLICATION_VL appl
WHERE conc.CONCURRENT_PROGRAM_ID=l_function_id_list(i)
AND conc.APPLICATION_ID=appl.APPLICATION_ID
AND conc.ENABLED_FLAG='Y';
l_cst_rev_id_list.delete();
l_resp_id_list.delete();
l_cst_name_list.delete();
l_appl_id_list.delete();
l_appl_name_list.delete();
l_resp_name_list.delete();
SELECT APPL.APPLICATION_ID,APPL.APPLICATION_NAME,RESP.RESPONSIBILITY_NAME
BULK COLLECT INTO l_appl_id_list,
l_appl_name_list,
l_resp_name_list
FROM FND_RESPONSIBILITY_VL RESP,
FND_APPLICATION_VL APPL
WHERE RESP.RESPONSIBILITY_ID = l_resp_id_list(i)
AND RESP.START_DATE <= SYSDATE
AND (RESP.END_DATE >= SYSDATE OR RESP.END_DATE IS NULL)
AND RESP.APPLICATION_ID=APPL.APPLICATION_ID
ORDER BY APPLICATION_ID;