[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* delete and insert all the attributes into the trans table as name - value pair */
DELETE jty_terr_nvp_trans_gt;
INSERT INTO jty_terr_nvp_trans_gt (
attribute_name
,num_value
,char_value
,date_value )
( SELECT 'COUNTRY' attribute_name
,null num_value
,p_TerrServReq_Rec.COUNTRY char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'CITY' attribute_name
,null num_value
,upper(p_TerrServReq_Rec.CITY) char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'POSTAL_CODE' attribute_name
,null num_value
,p_TerrServReq_Rec.POSTAL_CODE char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'STATE' attribute_name
,null num_value
,p_TerrServReq_Rec.STATE char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'AREA_CODE' attribute_name
,null num_value
,p_TerrServReq_Rec.AREA_CODE char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'COUNTY' attribute_name
,null num_value
,p_TerrServReq_Rec.COUNTY char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'COMP_NAME_RANGE' attribute_name
,null num_value
,p_TerrServReq_Rec.COMP_NAME_RANGE char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PROVINCE' attribute_name
,null num_value
,p_TerrServReq_Rec.PROVINCE char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PROBLEM_CODE' attribute_name
,null num_value
,p_TerrServReq_Rec.PROBLEM_CODE char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SR_CREATION_CHANNEL' attribute_name
,null num_value
,p_TerrServReq_Rec.SR_CREATION_CHANNEL char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'VIP_CUSTOMER' attribute_name
,null num_value
,p_TerrServReq_Rec.squal_char11 char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SR_PROBLEM_CODE' attribute_name
,null num_value
,p_TerrServReq_Rec.squal_char12 char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SR_CUST_CNTCT_PREF' attribute_name
,null num_value
,p_TerrServReq_Rec.squal_char13 char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SR_SRVC_CNTCT_CVG' attribute_name
,null num_value
,p_TerrServReq_Rec.squal_char21 char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SR_LANGUAGE' attribute_name
,null num_value
,p_TerrServReq_Rec.squal_char20 char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PARTY_ID' attribute_name
,p_TerrServReq_Rec.PARTY_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PARTY_SITE_ID' attribute_name
,p_TerrServReq_Rec.PARTY_SITE_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'NUM_OF_EMPLOYEES' attribute_name
,p_TerrServReq_Rec.NUM_OF_EMPLOYEES num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'INCIDENT_TYPE_ID' attribute_name
,p_TerrServReq_Rec.INCIDENT_TYPE_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'INCIDENT_SEVERITY_ID' attribute_name
,p_TerrServReq_Rec.INCIDENT_SEVERITY_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'INCIDENT_URGENCY_ID' attribute_name
,p_TerrServReq_Rec.INCIDENT_URGENCY_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'INCIDENT_STATUS_ID' attribute_name
,p_TerrServReq_Rec.INCIDENT_STATUS_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PLATFORM_ID' attribute_name
,p_TerrServReq_Rec.PLATFORM_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SUPPORT_SITE_ID' attribute_name
,p_TerrServReq_Rec.SUPPORT_SITE_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'CUSTOMER_SITE_ID' attribute_name
,p_TerrServReq_Rec.CUSTOMER_SITE_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'INVENTORY_ITEM_ID' attribute_name
,p_TerrServReq_Rec.INVENTORY_ITEM_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SRP_INVENTORY_ITEM_ID' attribute_name
,p_TerrServReq_Rec.SQUAL_NUM12 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SRP_ORG_ID' attribute_name
,p_TerrServReq_Rec.SQUAL_NUM13 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SPC_CATEGORY_ID' attribute_name
,p_TerrServReq_Rec.SQUAL_NUM14 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PCS_INVENTORY_ITEM_ID' attribute_name
,p_TerrServReq_Rec.SQUAL_NUM15 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PCS_ORG_ID' attribute_name
,p_TerrServReq_Rec.SQUAL_NUM16 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PCS_COMPONENT_ID' attribute_name
,p_TerrServReq_Rec.SQUAL_NUM23 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PCS_SUBCOMPONENT_ID' attribute_name
,p_TerrServReq_Rec.SQUAL_NUM24 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SR_GROUP_OWNER_ID' attribute_name
,p_TerrServReq_Rec.SQUAL_NUM17 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SSI_INVENTORY_ITEM_ID' attribute_name
,p_TerrServReq_Rec.SQUAL_NUM18 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SSI_ORG_ID' attribute_name
,p_TerrServReq_Rec.SQUAL_NUM19 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'DAY_OF_WEEK' attribute_name
,null num_value
--,p_TerrServReq_Rec.DAY_OF_WEEK char_value
, DECODE(p_TerrServReq_Rec.DAY_OF_WEEK,FND_API.G_MISS_CHAR,null,
p_TerrServReq_Rec.DAY_OF_WEEK) char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'TIME_OF_DAY' attribute_name
, null num_value
--, p_TerrServReq_Rec.TIME_OF_DAY char_value
, DECODE(p_TerrServReq_Rec.TIME_OF_DAY,FND_API.G_MISS_CHAR,null,
p_TerrServReq_Rec.TIME_OF_DAY) char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SYSTEM_ID' attribute_name
,p_TerrServReq_Rec.SQUAL_NUM60 num_value
,null char_value
,null date_value
FROM DUAL
);
/* delete and insert all the attributes into the trans table as name - value pair */
DELETE jty_terr_nvp_trans_gt;
INSERT INTO jty_terr_nvp_trans_gt (
attribute_name
,num_value
,char_value
,date_value )
( SELECT 'COUNTRY' attribute_name
,null num_value
,p_TerrSrvTask_Rec.COUNTRY char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'CITY' attribute_name
,null num_value
,upper(p_TerrSrvTask_Rec.CITY) char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'POSTAL_CODE' attribute_name
,null num_value
,p_TerrSrvTask_Rec.POSTAL_CODE char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'STATE' attribute_name
,null num_value
,p_TerrSrvTask_Rec.STATE char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'AREA_CODE' attribute_name
,null num_value
,p_TerrSrvTask_Rec.AREA_CODE char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'COUNTY' attribute_name
,null num_value
,p_TerrSrvTask_Rec.COUNTY char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'COMP_NAME_RANGE' attribute_name
,null num_value
,p_TerrSrvTask_Rec.COMP_NAME_RANGE char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PROVINCE' attribute_name
,null num_value
,p_TerrSrvTask_Rec.PROVINCE char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PROBLEM_CODE' attribute_name
,null num_value
,p_TerrSrvTask_Rec.PROBLEM_CODE char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SR_CREATION_CHANNEL' attribute_name
,null num_value
,p_TerrSrvTask_Rec.SR_CREATION_CHANNEL char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'VIP_CUSTOMER' attribute_name
,null num_value
,p_TerrSrvTask_Rec.squal_char11 char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SR_PROBLEM_CODE' attribute_name
,null num_value
,p_TerrSrvTask_Rec.squal_char12 char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SR_CUST_CNTCT_PREF' attribute_name
,null num_value
,p_TerrSrvTask_Rec.squal_char13 char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SR_SRVC_CNTCT_CVG' attribute_name
,null num_value
,p_TerrSrvTask_Rec.squal_char21 char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SR_LANGUAGE' attribute_name
,null num_value
,p_TerrSrvTask_Rec.squal_char20 char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PARTY_ID' attribute_name
,p_TerrSrvTask_Rec.PARTY_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PARTY_SITE_ID' attribute_name
,p_TerrSrvTask_Rec.PARTY_SITE_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'NUM_OF_EMPLOYEES' attribute_name
,p_TerrSrvTask_Rec.NUM_OF_EMPLOYEES num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'INCIDENT_TYPE_ID' attribute_name
,p_TerrSrvTask_Rec.INCIDENT_TYPE_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'INCIDENT_SEVERITY_ID' attribute_name
,p_TerrSrvTask_Rec.INCIDENT_SEVERITY_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'INCIDENT_URGENCY_ID' attribute_name
,p_TerrSrvTask_Rec.INCIDENT_URGENCY_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'INCIDENT_STATUS_ID' attribute_name
,p_TerrSrvTask_Rec.INCIDENT_STATUS_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PLATFORM_ID' attribute_name
,p_TerrSrvTask_Rec.PLATFORM_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SUPPORT_SITE_ID' attribute_name
,p_TerrSrvTask_Rec.SUPPORT_SITE_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'CUSTOMER_SITE_ID' attribute_name
,p_TerrSrvTask_Rec.CUSTOMER_SITE_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'INVENTORY_ITEM_ID' attribute_name
,p_TerrSrvTask_Rec.INVENTORY_ITEM_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'TASK_TYPE_ID' attribute_name
,p_TerrSrvTask_Rec.TASK_TYPE_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'TASK_STATUS_ID' attribute_name
,p_TerrSrvTask_Rec.TASK_STATUS_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'TASK_PRIORITY_ID' attribute_name
,p_TerrSrvTask_Rec.TASK_PRIORITY_ID num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SRP_INVENTORY_ITEM_ID' attribute_name
,p_TerrSrvTask_Rec.SQUAL_NUM12 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SRP_ORG_ID' attribute_name
,p_TerrSrvTask_Rec.SQUAL_NUM13 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SPC_CATEGORY_ID' attribute_name
,p_TerrSrvTask_Rec.SQUAL_NUM14 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PCS_INVENTORY_ITEM_ID' attribute_name
,p_TerrSrvTask_Rec.SQUAL_NUM15 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PCS_ORG_ID' attribute_name
,p_TerrSrvTask_Rec.SQUAL_NUM16 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PCS_COMPONENT_ID' attribute_name
,p_TerrSrvTask_Rec.SQUAL_NUM23 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'PCS_SUBCOMPONENT_ID' attribute_name
,p_TerrSrvTask_Rec.SQUAL_NUM24 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SR_GROUP_OWNER_ID' attribute_name
,p_TerrSrvTask_Rec.SQUAL_NUM17 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SSI_INVENTORY_ITEM_ID' attribute_name
,p_TerrSrvTask_Rec.SQUAL_NUM18 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SSI_ORG_ID' attribute_name
,p_TerrSrvTask_Rec.SQUAL_NUM19 num_value
,null char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'DAY_OF_WEEK' attribute_name
,null num_value
--,p_TerrSrvTask_Rec.DAY_OF_WEEK char_value
, DECODE(p_TerrSrvTask_Rec.DAY_OF_WEEK,FND_API.G_MISS_CHAR,null,
p_TerrSrvTask_Rec.DAY_OF_WEEK) char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'TIME_OF_DAY' attribute_name
,null num_value
--,p_TerrSrvTask_Rec.TIME_OF_DAY char_value
,DECODE(p_TerrSrvTask_Rec.TIME_OF_DAY ,FND_API.G_MISS_CHAR,null,
p_TerrSrvTask_Rec.TIME_OF_DAY ) char_value
,null date_value
FROM DUAL
UNION ALL
SELECT 'SYSTEM_ID' attribute_name
,p_TerrSrvTask_Rec.SQUAL_NUM60 num_value
,null char_value
,null date_value
FROM DUAL
);