[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ASSOC.WARRANTY_TEMPLATE_ID,
INSTANCE.INSTANCE_ID
FROM AHL_WARRANTY_TMPL_ITEMS ASSOC,
CSI_ITEM_INSTANCES INSTANCE
WHERE ASSOC.INVENTORY_ITEM_ID = INSTANCE.INVENTORY_ITEM_ID
AND ASSOC.INVENTORY_ORG_ID = INSTANCE.INV_MASTER_ORGANIZATION_ID
-- avikukum :: changes for bug# 11664006 :: START
AND TRUNC(NVL(INSTANCE.ACTIVE_START_DATE, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(INSTANCE.ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
-- avikukum :: changes for bug# 11664006 :: END
AND ASSOC.AUTO_ASSIGN_FLAG = 'Y'
AND AHL_WARRANTY_CONTRACTS_PVT.Can_Contract_Be_Created -- utility function defined in this package
(
ASSOC.WARRANTY_TEMPLATE_ID,
ASSOC.INVENTORY_ITEM_ID,
ASSOC.INVENTORY_ORG_ID
) = 'Y'
AND NOT EXISTS
(
SELECT 'X'
FROM AHL_WARRANTY_CONTRACTS_B
WHERE WARRANTY_TEMPLATE_ID = ASSOC.WARRANTY_TEMPLATE_ID
AND ITEM_INSTANCE_ID = INSTANCE.INSTANCE_ID
)
-- avikukum :: changes for bug# 11664006 :: START
AND EXISTS
(
SELECT 'X'
FROM AHL_UNIT_CONFIG_HEADERS UCH
WHERE UCH.NAME = AHL_UTIL_UC_PKG.GET_UNIT_NAME(INSTANCE.INSTANCE_ID)
AND UCH.UNIT_CONFIG_STATUS_CODE IN ('INCOMPLETE', 'COMPLETE')
UNION ALL
SELECT 'X'
FROM dual
WHERE AHL_UTIL_UC_PKG.GET_UNIT_NAME(INSTANCE.INSTANCE_ID) IS NULL);
SELECT 'X'
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = c_item_instance_id;
SELECT
AWTB.WARRANTY_TYPE,
AWTB.WARRANTY_VENDOR_ID,
AWTB.CONTRACT_START_DATE,
AWTB.CONTRACT_END_DATE,
AWTB.WARRANTY_PERIOD,
AWTB.PERIOD_UOM_CODE,
AWTB.CONTRACT_EXPIRY_TYPE,
AWTB.CLAIM_LABOUR_HOURS,
ASSOC.OSP_CLAIM_FLAG
FROM AHL_WARRANTY_TEMPLATES_B AWTB,
AHL_WARRANTY_TMPL_ITEMS ASSOC,
CSI_ITEM_INSTANCES INSTANCE
WHERE AWTB.WARRANTY_TEMPLATE_ID = c_warranty_template_id
AND INSTANCE.INSTANCE_ID = c_item_instance_id
AND ASSOC.WARRANTY_TEMPLATE_ID = AWTB.WARRANTY_TEMPLATE_ID
AND ASSOC.INVENTORY_ITEM_ID = INSTANCE.INVENTORY_ITEM_ID
AND ASSOC.INVENTORY_ORG_ID = INSTANCE.INV_MASTER_ORGANIZATION_ID;
SELECT counter.counter_id,
tmpl_ctr.warranty_tmpl_counter_id,
tmpl_ctr.threshold
FROM ahl_warranty_tmpl_ctr_b tmpl_ctr,
csi_counters_vl counter,
CSI_COUNTER_ASSOCIATIONS CCA
WHERE TRUNC(NVL(counter.start_date_active, sysdate)) <= TRUNC(sysdate)
AND TRUNC(NVL(counter.end_date_active, sysdate+1)) > TRUNC(sysdate)
AND counter.COUNTER_ID = CCA.COUNTER_ID
AND CCA.SOURCE_OBJECT_CODE = 'CP'
AND counter.created_from_counter_tmpl_id = tmpl_ctr.counter_id
AND tmpl_ctr.WARRANTY_TEMPLATE_ID = c_warranty_template_id
AND CCA.SOURCE_OBJECT_ID = c_item_instance_id;
INSERT INTO AHL_WARRANTY_CONTRACTS_B
(
WARRANTY_CONTRACT_ID,
OBJECT_VERSION_NUMBER,
CONTRACT_NUMBER,
CONTRACT_STATUS_CODE,
ITEM_INSTANCE_ID,
WARRANTY_TEMPLATE_ID,
EXPIRATION_DATE,
OSP_CLAIM_FLAG,
WARRANTY_TYPE,
WARRANTY_VENDOR_ID,
ACTIVE_START_DATE,
ACTIVE_END_DATE,
WARRANTY_PERIOD,
PERIOD_UOM_CODE,
CONTRACT_EXPIRY_TYPE,
CLAIM_LABOUR_HOURS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
values
(
l_warranty_contract_id,
1,
l_warranty_contract_id,
'PENDING',
p_item_instance_id,
p_warranty_template_id,
NULL,
warranty_template_rec.OSP_CLAIM_FLAG,
warranty_template_rec.WARRANTY_TYPE,
warranty_template_rec.WARRANTY_VENDOR_ID,
NVL(warranty_template_rec.CONTRACT_START_DATE, SYSDATE),
warranty_template_rec.CONTRACT_END_DATE,
warranty_template_rec.WARRANTY_PERIOD,
warranty_template_rec.PERIOD_UOM_CODE,
warranty_template_rec.CONTRACT_EXPIRY_TYPE,
warranty_template_rec.CLAIM_LABOUR_HOURS,
SYSDATE,
Fnd_Global.USER_ID,
SYSDATE,
Fnd_Global.USER_ID,
Fnd_Global.LOGIN_ID,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
INSERT INTO AHL_WARRANTY_CONTRACTS_TL
(
WARRANTY_CONTRACT_ID,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
DESCRIPTION,
TERMS_AND_CONDITIONS,
REACTION_AND_RESOL_TIME,
SERVICE_LEVEL_AGREEMENT
)
SELECT l_warranty_contract_id,
L.LANGUAGE_CODE,
userenv('LANG'),
SYSDATE,
Fnd_Global.USER_ID,
SYSDATE,
Fnd_Global.USER_ID,
Fnd_Global.LOGIN_ID,
AWTL.DESCRIPTION,
AWTL.TERMS_AND_CONDITIONS,
AWTL.REACTION_AND_RESOL_TIME,
AWTL.SERVICE_LEVEL_AGREEMENT
FROM AHL_WARRANTY_TEMPLATES_TL AWTL,
FND_LANGUAGES L
WHERE AWTL.WARRANTY_TEMPLATE_ID (+) = P_WARRANTY_TEMPLATE_ID
AND AWTL.LANGUAGE (+) = L.LANGUAGE_CODE
AND L.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS
(SELECT 'X'
FROM AHL_WARRANTY_CONTRACTS_TL T
WHERE T.WARRANTY_CONTRACT_ID = l_warranty_contract_id
AND T.LANGUAGE = L.LANGUAGE_CODE);
INSERT INTO AHL_WARRANTY_CONT_CTR_B
(
WARRANTY_CNTRT_COUNTER_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
WARRANTY_CONTRACT_ID,
COUNTER_ID,
THRESHOLD,
START_VALUE,
EXPIRATION_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
values
(
l_warranty_cont_ctr_id,
1,
SYSDATE,
Fnd_Global.USER_ID,
SYSDATE,
Fnd_Global.USER_ID,
Fnd_Global.LOGIN_ID,
l_warranty_contract_id,
warranty_contract_counter_rec.counter_id,
warranty_contract_counter_rec.threshold,
0,
l_active_end_date,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
INSERT INTO AHL_WARRANTY_CONT_CTR_TL
(
WARRANTY_CNTRT_COUNTER_ID,
LANGUAGE,
SOURCE_LANG,
TERMS_AND_CONDITIONS,
REACTION_AND_RESOL_TIME,
SERVICE_LEVEL_AGREEMENT,
REMARKS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
SELECT l_warranty_cont_ctr_id,
L.LANGUAGE_CODE,
userenv('LANG'),
AWTCL.terms_and_conditions,
AWTCL.reaction_and_resol_time,
AWTCL.service_level_agreement,
AWTCL.remarks,
SYSDATE,
Fnd_Global.USER_ID,
SYSDATE,
Fnd_Global.USER_ID,
Fnd_Global.LOGIN_ID
FROM AHL_WARRANTY_TMPL_CTR_TL AWTCL,
FND_LANGUAGES L
WHERE AWTCL.WARRANTY_TMPL_COUNTER_ID (+) = warranty_contract_counter_rec.warranty_tmpl_counter_id
AND AWTCL.LANGUAGE (+) = L.LANGUAGE_CODE
AND L.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS
(SELECT 'X'
FROM AHL_WARRANTY_CONT_CTR_TL T
WHERE T.WARRANTY_CNTRT_COUNTER_ID = l_warranty_cont_ctr_id
AND T.LANGUAGE = L.LANGUAGE_CODE);
SELECT 'X'
FROM AHL_WARRANTY_TEMPLATES_B
WHERE warranty_template_id = c_warranty_tmpl_id
AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
AND TRUNC(NVL(contract_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT 'X'
FROM AHL_WARRANTY_TEMPLATES_B
WHERE warranty_template_id = c_warranty_tmpl_id
AND enabled_flag = 'Y';
SELECT 'X'
FROM AHL_WARRANTY_TMPL_ITEMS
WHERE warranty_template_id = c_warranty_tmpl_id
AND inventory_item_id = c_warranty_item_id
AND inventory_org_id = c_item_org_id
AND enabled_flag = 'Y';
SELECT 'X'
FROM AHL_WARRANTY_CONTRACTS_B
WHERE item_instance_id = c_instance_id
AND contract_status_code = 'ACTIVE';
delete from AHL_WARRANTY_CONTRACTS_TL T
where not exists
(select NULL
from AHL_WARRANTY_CONTRACTS_B B
where B.WARRANTY_CONTRACT_ID = T.WARRANTY_CONTRACT_ID
);
update AHL_WARRANTY_CONTRACTS_TL T set (
REMARKS,
DESCRIPTION,
TERMS_AND_CONDITIONS,
REACTION_AND_RESOL_TIME,
SERVICE_LEVEL_AGREEMENT
) = (select
B.REMARKS,
B.DESCRIPTION,
B.TERMS_AND_CONDITIONS,
B.REACTION_AND_RESOL_TIME,
B.SERVICE_LEVEL_AGREEMENT
from AHL_WARRANTY_CONTRACTS_TL B
where B.WARRANTY_CONTRACT_ID = T.WARRANTY_CONTRACT_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.WARRANTY_CONTRACT_ID,
T.LANGUAGE
) in (select
SUBT.WARRANTY_CONTRACT_ID,
SUBT.LANGUAGE
from AHL_WARRANTY_CONTRACTS_TL SUBB, AHL_WARRANTY_CONTRACTS_TL SUBT
where SUBB.WARRANTY_CONTRACT_ID = SUBT.WARRANTY_CONTRACT_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.REMARKS <> SUBT.REMARKS
or (SUBB.REMARKS is null and SUBT.REMARKS is not null)
or (SUBB.REMARKS is not null and SUBT.REMARKS is null)
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
or SUBB.TERMS_AND_CONDITIONS <> SUBT.TERMS_AND_CONDITIONS
or (SUBB.TERMS_AND_CONDITIONS is null and SUBT.TERMS_AND_CONDITIONS is not null)
or (SUBB.TERMS_AND_CONDITIONS is not null and SUBT.TERMS_AND_CONDITIONS is null)
or SUBB.REACTION_AND_RESOL_TIME <> SUBT.REACTION_AND_RESOL_TIME
or (SUBB.REACTION_AND_RESOL_TIME is null and SUBT.REACTION_AND_RESOL_TIME is not null)
or (SUBB.REACTION_AND_RESOL_TIME is not null and SUBT.REACTION_AND_RESOL_TIME is null)
or SUBB.SERVICE_LEVEL_AGREEMENT <> SUBT.SERVICE_LEVEL_AGREEMENT
or (SUBB.SERVICE_LEVEL_AGREEMENT is null and SUBT.SERVICE_LEVEL_AGREEMENT is not null)
or (SUBB.SERVICE_LEVEL_AGREEMENT is not null and SUBT.SERVICE_LEVEL_AGREEMENT is null)
));
insert into AHL_WARRANTY_CONTRACTS_TL (
WARRANTY_CONTRACT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REMARKS,
DESCRIPTION,
TERMS_AND_CONDITIONS,
REACTION_AND_RESOL_TIME,
SERVICE_LEVEL_AGREEMENT,
LANGUAGE,
SOURCE_LANG
) select /*+ ORDERED */
B.WARRANTY_CONTRACT_ID,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
B.REMARKS,
B.DESCRIPTION,
B.TERMS_AND_CONDITIONS,
B.REACTION_AND_RESOL_TIME,
B.SERVICE_LEVEL_AGREEMENT,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from AHL_WARRANTY_CONTRACTS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from AHL_WARRANTY_CONTRACTS_TL T
where T.WARRANTY_CONTRACT_ID = B.WARRANTY_CONTRACT_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
delete from AHL_WARRANTY_CONT_CTR_TL T
where not exists
(select NULL
from AHL_WARRANTY_CONT_CTR_B B
where B.WARRANTY_CNTRT_COUNTER_ID = T.WARRANTY_CNTRT_COUNTER_ID
);
update AHL_WARRANTY_CONT_CTR_TL T set (
REMARKS,
TERMS_AND_CONDITIONS,
REACTION_AND_RESOL_TIME,
SERVICE_LEVEL_AGREEMENT
) = (select
B.REMARKS,
B.TERMS_AND_CONDITIONS,
B.REACTION_AND_RESOL_TIME,
B.SERVICE_LEVEL_AGREEMENT
from AHL_WARRANTY_CONT_CTR_TL B
where B.WARRANTY_CNTRT_COUNTER_ID = T.WARRANTY_CNTRT_COUNTER_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.WARRANTY_CNTRT_COUNTER_ID,
T.LANGUAGE
) in (select
SUBT.WARRANTY_CNTRT_COUNTER_ID,
SUBT.LANGUAGE
from AHL_WARRANTY_CONT_CTR_TL SUBB, AHL_WARRANTY_CONT_CTR_TL SUBT
where SUBB.WARRANTY_CNTRT_COUNTER_ID = SUBT.WARRANTY_CNTRT_COUNTER_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.REMARKS <> SUBT.REMARKS
or (SUBB.REMARKS is null and SUBT.REMARKS is not null)
or (SUBB.REMARKS is not null and SUBT.REMARKS is null)
or SUBB.TERMS_AND_CONDITIONS <> SUBT.TERMS_AND_CONDITIONS
or (SUBB.TERMS_AND_CONDITIONS is null and SUBT.TERMS_AND_CONDITIONS is not null)
or (SUBB.TERMS_AND_CONDITIONS is not null and SUBT.TERMS_AND_CONDITIONS is null)
or SUBB.REACTION_AND_RESOL_TIME <> SUBT.REACTION_AND_RESOL_TIME
or (SUBB.REACTION_AND_RESOL_TIME is null and SUBT.REACTION_AND_RESOL_TIME is not null)
or (SUBB.REACTION_AND_RESOL_TIME is not null and SUBT.REACTION_AND_RESOL_TIME is null)
or SUBB.SERVICE_LEVEL_AGREEMENT <> SUBT.SERVICE_LEVEL_AGREEMENT
or (SUBB.SERVICE_LEVEL_AGREEMENT is null and SUBT.SERVICE_LEVEL_AGREEMENT is not null)
or (SUBB.SERVICE_LEVEL_AGREEMENT is not null and SUBT.SERVICE_LEVEL_AGREEMENT is null)
));
insert into AHL_WARRANTY_CONT_CTR_TL (
WARRANTY_CNTRT_COUNTER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REMARKS,
TERMS_AND_CONDITIONS,
REACTION_AND_RESOL_TIME,
SERVICE_LEVEL_AGREEMENT,
LANGUAGE,
SOURCE_LANG
) select /*+ ORDERED */
B.WARRANTY_CNTRT_COUNTER_ID,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
B.REMARKS,
B.TERMS_AND_CONDITIONS,
B.REACTION_AND_RESOL_TIME,
B.SERVICE_LEVEL_AGREEMENT,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from AHL_WARRANTY_CONT_CTR_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from AHL_WARRANTY_CONT_CTR_TL T
where T.WARRANTY_CNTRT_COUNTER_ID = B.WARRANTY_CNTRT_COUNTER_ID
and T.LANGUAGE = L.LANGUAGE_CODE);