[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_Sql := 'SELECT distinct tbl.outcome_id, outc.active active_outcome, '||
'outc.result_required result_required, '||
'tbl.result_id, result.active active_result, '||
'result.result_required reason_required, '||
'tbl.reason_id, reason.active active_reason, '||
'tbl.source_code, '||
'tbl.source_code_id FROM ';
SELECT source_code, ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID
INTO l_source_code, l_object_type, l_object_id
FROM AMS_SOURCE_CODES
WHERE source_code_id = l_source_code_id
and active_flag = l_active_flag;
SELECT source_code_id, ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID
INTO l_source_code_id, l_object_type, l_object_id
FROM AMS_SOURCE_CODES
WHERE source_code = l_source_code
AND active_flag = l_active_flag;
arr_Params.DELETE;
l_Sql := 'SELECT wrap_id as wrap_id, wrap_up_level as wrap_up_level '||
'FROM jtf_ih_wrap_ups WHERE outcome_id = :outcome_id ' ;
INSERT INTO jtf_ih_wrap_ups (
WRAP_ID,
OUTCOME_ID,
RESULT_ID,
REASON_ID,
OBJECT_ID,
OBJECT_TYPE,
SOURCE_CODE_ID,
SOURCE_CODE,
WRAP_UP_LEVEL,
START_DATE,
END_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES (
JTF_IH_WRAP_UPS_S1.NEXTVAL,
l_outcome_id,
l_result_id,
l_reason_id,
l_object_id,
l_object_type,
l_source_code_id,
l_source_code,
p_Wrap_Up_Level,
SYSDATE,
l_end_date_time,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id
);
l_Sql := 'UPDATE jtf_ih_wrap_ups SET ';
select count(*) into nCount from jtf_ih_outcomes_b;
For curOut in (select outcome_id, result_required from jtf_ih_outcomes_b where active <> 'N' or active is null ) loop
-- add outcome only wrap-up row if no Result is required
if curOut.result_required = 'N' or curOut.result_required is null then
ttWrpUps(nCnt).Outcome_Id := curOut.outcome_id;
for curOutRes in (select outres.result_id, res.result_required
from jtf_ih_outcome_results outres, jtf_ih_results_b res
where outres.outcome_id = curOut.outcome_id and
outres.result_id = res.result_id and
(res.active <> 'N' or res.active is null)) loop
-- if the result does not require a reason,
-- then add a Outcome_id, Result_id, null reason_id wrap-up.
if (curOutRes.result_required = 'N' or
curOutRes.result_required is null) then
ttWrpUps(nCnt).Outcome_Id := curOut.outcome_id;
for curResRea in (select rr.reason_id
from jtf_ih_result_reasons rr, jtf_ih_reasons_b rea
where rr.result_id = curOutRes.result_id and
rr.reason_id = rea.reason_id and
( rea.active <> 'N' or rea.active is null)) loop
ttWrpUps(nCnt).Outcome_Id := curOut.outcome_id;
for curOut in (select cmpOut.outcome_id, cmpOut.source_code,
outc.result_required
from jtf_ih_outcomes_b outc, jtf_ih_outcomes_campaigns cmpOut
where outc.outcome_id = cmpOut.outcome_id
and (outc.active <> 'N' or outc.active is null)
order by outc.outcome_id) loop
begin
-- validate the campaign values and get the source_code_id, object_id
-- and object_type values
begin
SELECT count(*), source_code_id, SOURCE_CODE_FOR_ID, ARC_SOURCE_CODE_FOR
INTO nCampCount, nSourceCodeID, nObjectId, vObjectType
FROM AMS_SOURCE_CODES
WHERE source_code = curOut.source_code
AND active_flag = l_active_flag
group by source_code_id, SOURCE_CODE_FOR_ID, ARC_SOURCE_CODE_FOR;
for curOutRes in (select outr.result_id, res.result_required
from jtf_ih_outcome_results outr, jtf_ih_results_b res
where outr.outcome_id = curOut.outcome_id and
outr.result_id = res.result_id and
(res.active <> 'N' or res.active is null)) loop
-- if the result does not require a reason,
-- then add a Outcome_id, Result_id, null reason_id wrap-up.
if (curOutRes.result_required = 'N' or
curOutRes.result_required is null) then
ttWrpUps(nCnt).Outcome_Id := curOut.outcome_id;
for curResRea in (select rr.reason_id from jtf_ih_result_reasons rr,
jtf_ih_reasons_b rea
where rr.result_id = curOutRes.result_id and
rr.reason_id = rea.reason_id and
(rea.active <> 'N' or rea.active is null)) loop
ttWrpUps(nCnt).Outcome_Id := curOut.outcome_id;
v_Params.delete;
sSql := 'SELECT COUNT(*) FROM jtf_ih_wrap_ups WHERE outcome_id = :outcome_id ';
INSERT INTO jtf_ih_wrap_ups ( WRAP_ID,
OUTCOME_ID,
RESULT_ID,
REASON_ID,
SOURCE_CODE_ID,
SOURCE_CODE,
OBJECT_ID,
OBJECT_TYPE,
START_DATE,
WRAP_UP_LEVEL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES (
jtf_ih_wrap_ups_s1.nextval,
ttWrpUps(i).Outcome_Id,
ttWrpUps(i).Result_Id,
ttWrpUps(i).Reason_Id,
ttWrpUps(i).source_code_id,
ttWrpUps(i).source_code,
ttWrpUps(i).object_id,
ttWrpUps(i).object_type,
SYSDATE,
ttWrpUps(i).wrap_up_level,
hz_utility_pub.user_id,
SYSDATE,
hz_utility_pub.last_update_login,
SYSDATE,
hz_utility_pub.last_update_login
);
UPDATE jtf_ih_outcomes_b SET active='Y' WHERE active IS NULL;
UPDATE jtf_ih_results_b SET active='Y' WHERE active IS NULL;
UPDATE jtf_ih_reasons_b SET active='Y' WHERE active IS NULL;
UPDATE JTF_IH_OUTCOMES_B set RESULT_REQUIRED = 'N' WHERE RESULT_REQUIRED is NULL;
UPDATE JTF_IH_RESULTS_B set RESULT_REQUIRED = 'N' WHERE RESULT_REQUIRED is NULL;