[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
NVL(maint_organization_id,-1)
, NVL(master_organization_id,-1)
, NVL(eam_enabled_flag,'X')
FROM mtl_parameters
WHERE organization_id = p_org_id;
, x_disallow_request_update OUT NOCOPY VARCHAR2
, x_disallow_agent_dispatch OUT NOCOPY VARCHAR2
, x_disallow_product_update OUT NOCOPY VARCHAR2
, x_pending_approval_flag OUT NOCOPY VARCHAR2
, x_intermediate_status_id OUT NOCOPY VARCHAR2
, x_approval_action_status_id OUT NOCOPY VARCHAR2
, x_rejection_action_status_id OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_dt DATE;
SELECT
NVL(close_flag ,'N')
, NVL(disallow_request_update,'N')
, NVL(disallow_agent_dispatch,'N')
, NVL(disallow_product_update,'N')
, NVL(pending_approval_flag ,'N')
, intermediate_status_id
, approval_action_status_id
, rejection_action_status_id
INTO
x_close_flag
, x_disallow_request_update
, x_disallow_agent_dispatch
, x_disallow_product_update
, x_pending_approval_flag
, x_intermediate_status_id
, x_approval_action_status_id
, x_rejection_action_status_id
FROM
cs_incident_statuses_b
WHERE incident_status_id = p_status_id
-- AND trunc(sysdate) BETWEEN NVL(start_date_active,l_dt)
-- AND NVL(end_date_active ,l_dt)
;
SELECT
owner_party_source_table
, owner_party_id
, inv_master_organization_id
, inventory_item_id
, last_vld_organization_id
, active_start_date
, active_end_date
, location_type_code
, external_reference
, serial_number
FROM
csi_item_instances
WHERE instance_id = p_customer_product_id;
SELECT
NVL(enabled_flag,'N')
, NVL(serv_req_enabled_code,'N')
, NVL(eam_item_type,-1)
, start_date_active
, end_date_active
INTO
x_enabled_flag
, x_serv_req_enabled
, x_eam_item_type
, x_start_date_active
, x_end_date_active
FROM
mtl_system_items
WHERE organization_id = p_inventory_org_id
AND inventory_item_id = p_inv_item_id;
SELECT incident_id INTO p_request_id
FROM cs_incidents_all_b
WHERE incident_number = p_request_number ;
SELECT incident_type_id INTO p_type_id
FROM cs_incident_types_vl
WHERE incident_subtype = p_subtype
AND UPPER(name) = UPPER(p_type_name);
SELECT incident_type_id INTO p_type_id
FROM cs_incident_types_vl
WHERE incident_subtype = p_subtype
AND UPPER(name) = UPPER(p_type_name)
AND ((parent_incident_type_id = p_parent_type_id)
OR (parent_incident_type_id IS NULL
AND p_parent_type_id IS NULL));
SELECT incident_type_id INTO p_type_id
FROM cs_incident_types_vl
WHERE incident_subtype = p_subtype
AND UPPER(name) = UPPER(p_type_name)
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE))
AND rownum<2;
SELECT incident_status_id INTO p_status_id
FROM cs_incident_statuses_vl
WHERE incident_subtype = p_subtype
AND UPPER(name) = UPPER(p_status_name)
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE))
AND rownum<2;
SELECT incident_severity_id INTO p_severity_id
FROM cs_incident_severities_vl
WHERE incident_subtype = p_subtype
AND UPPER(name) = UPPER(p_severity_name)
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE))
AND rownum<2;
SELECT incident_urgency_id INTO p_urgency_id
FROM cs_incident_urgencies_vl
WHERE UPPER(name) = UPPER(p_urgency_name)
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE))
AND rownum<2;
SELECT a.party_id INTO p_customer_id
FROM hz_parties a
WHERE a.party_number = p_customer_number;
SELECT a.party_id INTO p_customer_id
FROM hz_parties a
WHERE UPPER(a.party_name) = UPPER(p_customer_name);
SELECT person_id INTO p_employee_id
FROM per_people_x
WHERE employee_number = p_employee_number;
SELECT instance_id
INTO p_customer_product_id
FROM csi_item_instances
WHERE instance_number = l_cp_ref_number;
SELECT header_id INTO p_rma_header_id
FROM so_headers_all
WHERE order_number = p_rma_number
AND order_type_id = NVL(p_order_type_id,order_type_id) ;
SELECT 'x' INTO l_dummy
FROM fnd_user
WHERE user_id = p_user_id;
SELECT 'x' INTO l_dummy
FROM fnd_user
WHERE user_id = p_user_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date, SYSDATE))
AND TRUNC(NVL(end_date, SYSDATE));
SELECT 'x' INTO l_dummy
FROM fnd_logins
WHERE login_id = p_login_id
AND user_id = p_user_id;
p_column_name => 'LAST_UPDATE_LOGIN' );
SELECT 'x'
FROM cs_sr_type_mapping
WHERE incident_type_id = p_type_id
AND responsibility_id = p_resp_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date, SYSDATE))
AND TRUNC(NVL(end_date, SYSDATE));
SELECT CMRO_flag,Maintenance_flag
FROM cs_incident_types_b
WHERE incident_type_id = p_type_id
AND incident_subtype = p_subtype
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE));
select 'x'
from cs_sr_type_mapping
where incident_type_id = p_type_id
and responsibility_id = p_resp_id
and application_id = p_resp_appl_id -- new for 11.5.10
and trunc(sysdate) between trunc(nvl(start_date, sysdate))
and trunc(nvl(end_date, sysdate));
select UPPER(cmro_flag), UPPER(maintenance_flag),start_date_active,end_date_active
from cs_incident_types_b
where incident_type_id = p_type_id
and incident_subtype = p_subtype;
select cmro_flag, maintenance_flag ,start_date_active,end_date_active
from cs_incident_types_vl_sec
where incident_type_id = p_type_id
and incident_subtype = p_subtype;
if ( p_operation = 'UPDATE') then
open val_type_unsecure;
elsif ( p_operation = 'UPDATE_OLD') then
open val_type_unsecure;
end if; -- if ( p_operation = 'UPDATE') then
if ( p_operation = 'UPDATE' OR p_operation = 'CREATE') then
open val_type_secure;
elsif ( p_operation = 'UPDATE_OLD') then
open val_type_secure;
end if; -- if ( p_operation = 'UPDATE') then
SELECT seeded_flag, close_flag
FROM cs_incident_statuses_b
WHERE incident_subtype = p_subtype
AND incident_status_id = p_status_id
AND valid_in_create_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE));
SELECT seeded_flag, close_flag
FROM cs_incident_statuses_b
WHERE incident_subtype = p_subtype
AND incident_status_id = p_status_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE));
SELECT status_Group_id
FROM cs_incident_types_B
WHERE Incident_type_id = p_type_id;
SELECT status_Group_id
FROM cs_sr_type_mapping
WHERE Incident_type_id = p_type_id
AND responsibility_id = p_resp_id
AND status_group_id IS NOT NULL;
SELECT incident_status_id
FROM cs_sr_allowed_statuses
WHERE status_Group_id = l_status_group_id
AND incident_Status_id = p_status_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date, SYSDATE))
AND TRUNC(NVL(end_date, SYSDATE));
** Validate_Updated_Status
** Note : This procedure is used during Update Service Request.
** 1. Find whether Status Group is Mapped to Type, If yes use Status Group (SG1)
** 2. Find whether Status Group is mapped to Type-Resp. If yes, use status Group (SG2)
** 3. If No status Group is mapped
** Check whether status is a valid status.
** 4. If a Status Group is mapped (SG1) or (SG2)
** Is Status Transition defined for this group
** If yes, Is the current Transition Allowed
** If Status Transition are not defined.
**** Does Given Status exist in CS_ALLOWED_STATUSES for the Given Group?
** 5. Return to the caller via the p_close_flag parameter whether or not the
** status is a "closed" status. This will be useful to the caller for
** managing the closed date.
*/
PROCEDURE Validate_Updated_Status
( p_api_name IN VARCHAR2,
p_parameter_name IN VARCHAR2,
p_resp_id IN NUMBER,
p_new_status_id IN NUMBER,
p_old_status_id IN NUMBER,
p_subtype IN VARCHAR2,
p_type_id IN NUMBER,
p_old_type_id IN NUMBER := NULL,
p_close_flag OUT NOCOPY VARCHAR2,
p_disallow_request_update OUT NOCOPY VARCHAR2,
p_disallow_owner_update OUT NOCOPY VARCHAR2,
p_disallow_product_update OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_status_group_id NUMBER := 0;
SELECT seeded_flag, close_flag
FROM cs_incident_statuses_b
WHERE incident_subtype = p_subtype
AND incident_status_id = p_new_status_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE));
SELECT status_Group_id
FROM cs_incident_types_B
WHERE Incident_type_id = l_type_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE));
SELECT status_Group_id
FROM cs_sr_type_mapping
WHERE Incident_type_id = l_type_id
AND responsibility_id = l_resp_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date, SYSDATE))
AND TRUNC(NVL(end_date, SYSDATE)) ;
SELECT incident_status_id
FROM cs_sr_allowed_statuses
WHERE status_Group_id = p_status_group_id
AND incident_Status_id = p_new_status_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date, SYSDATE))
AND TRUNC(NVL(end_date, SYSDATE));
SELECT status_Transition_id
FROM cs_sr_status_transitions
WHERE status_Group_id = l_status_group_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date, SYSDATE))
AND TRUNC(NVL(end_date, SYSDATE));
SELECT status_Transition_id
FROM cs_sr_status_transitions
WHERE status_Group_id = l_status_group_id
AND FROM_Incident_status_id = p_old_status_id
AND TO_Incident_status_id = p_new_status_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date, SYSDATE))
AND TRUNC(NVL(end_date, SYSDATE));
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Updated_Status';
SELECT close_flag, disallow_request_update,
disallow_agent_dispatch, disallow_product_update
INTO p_close_flag, p_disallow_request_update,
p_disallow_owner_update, p_disallow_product_update
FROM cs_incident_statuses_b
WHERE incident_status_id = p_old_status_id
AND incident_subtype = p_subtype;
SELECT close_flag INTO p_close_flag
FROM cs_incident_statuses_b
WHERE incident_status_id = p_new_status_id
AND incident_subtype = p_subtype
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE));
END Validate_Updated_Status;
SELECT 'x' INTO l_dummy
FROM cs_incident_severities_b
WHERE incident_subtype = p_subtype
AND incident_severity_id = p_severity_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE));
SELECT 'x' INTO l_dummy
FROM cs_incident_urgencies_b
WHERE incident_urgency_id = p_urgency_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE));
SELECT country INTO x_incident_country
FROM hz_locations
WHERE location_id = p_incident_location_id;
SELECT b.country INTO x_incident_country
FROM hz_party_sites a,
hz_locations b
WHERE party_site_id = p_incident_location_id
AND a.location_id = b.location_id;
SELECT 'x' INTO l_dummy
FROM fnd_territories
WHERE territory_code = p_incident_country;
SELECT 'x'
FROM per_workforce_x hr
WHERE hr.person_id = p_employee_id
AND NVL(hr.termination_date,SYSDATE) >= SYSDATE;
SELECT party_type INTO l_party_type -- Bug 3666089
FROM hz_parties a
WHERE a.party_id = p_customer_id
AND a.status = 'A'
AND a.party_type IN ('ORGANIZATION','PERSON');
SELECT party_type from hz_parties
WHERE party_id = p_bill_to_party_id;
SELECT 'x'
FROM Hz_Parties sub,
Hz_Relationships r
WHERE r.object_id = p_bill_to_party_id
AND r.party_id = p_bill_to_contact_id
AND sub.status = 'A'
AND r.status = 'A'
AND r.subject_id = sub.party_id
AND sub.party_type = 'PERSON'
AND NVL(r.start_date, SYSDATE-1) < SYSDATE
AND NVL(r.end_date, SYSDATE+1) > SYSDATE;
SELECT 'x'
FROM Hz_Parties sub,
Hz_Relationships r
WHERE r.object_id = p_customer_id
AND r.party_id = p_customer_contact_id
AND sub.status = 'A'
AND r.status = 'A'
AND r.subject_id = sub.party_id
AND sub.party_type = 'PERSON'
AND NVL(r.start_date, SYSDATE-1) < SYSDATE
AND NVL(r.end_date, SYSDATE+1) > SYSDATE;
SELECT 'x'
FROM Hz_Parties sub,
Hz_Relationships r,
Hz_Parties obj
WHERE r.object_id = p_customer_id
AND r.party_id = p_customer_contact_id
AND sub.status = 'A'
AND r.status = 'A'
AND obj.status = 'A'
AND r.subject_id = sub.party_id
AND r.object_id = obj.party_id
AND sub.party_type = 'PERSON'
AND obj.party_type = 'ORGANIZATION'
AND NVL(r.start_date, SYSDATE-1) <= SYSDATE
AND NVL(r.end_date, SYSDATE+1) > SYSDATE;
SELECT 'x'
FROM Hz_Parties sub,
Hz_Relationships r,
Hz_Parties obj
WHERE r.object_id = p_customer_id
AND r.party_id = p_customer_contact_id
AND sub.status = 'A'
AND r.status = 'A'
AND obj.status = 'A'
AND r.subject_id = sub.party_id
AND r.object_id = obj.party_id
AND sub.party_type = 'PERSON'
AND obj.party_type = 'PERSON'
-- Made below changes as per bug6629807 put <= instead of < rtripath 12/30/2007
AND NVL(r.start_date, SYSDATE-1) <= SYSDATE
AND NVL(r.end_date, SYSDATE+1) > SYSDATE;
SELECT a.inventory_item_id
INTO p_inventory_item_id
FROM csi_item_instances a,
mtl_system_items_b b,
csi_i_parties cip
WHERE a.instance_id = p_customer_product_id
--AND a.owner_party_id = p_customer_id
AND cip.party_id = p_customer_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
AND TRUNC(NVL(a.active_end_date, SYSDATE))
AND a.location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS')
AND a.owner_party_source_table = 'HZ_PARTIES'
AND a.instance_id = cip.instance_id
AND cip.party_source_table = 'HZ_PARTIES'
AND a.inventory_item_id = b.inventory_item_id
AND b.organization_id = p_inventory_org_id
-- Commented service_item_flag condition as this is not used
-- anymore and added contract_item_type_code condition
-- AND b.service_item_flag = 'N'
-- For ER 3701924
-- AND contract_item_type_code IS NULL
AND b.enabled_flag = 'Y'
-- Added for Bug# 2167129,2175917
AND b.serv_req_enabled_code = 'E'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(b.start_date_active, SYSDATE))
AND TRUNC(NVL(b.end_date_active, SYSDATE))
AND rownum<2;
SELECT a.inventory_item_id
INTO p_inventory_item_id
FROM csi_item_instances a,
mtl_system_items_b b,
csi_i_parties cip
WHERE a.instance_id = p_customer_product_id
-- AND a.owner_party_id = p_customer_id
AND cip.party_id = p_customer_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
AND TRUNC(NVL(a.active_end_date, SYSDATE))
AND a.owner_party_source_table = 'HZ_PARTIES'
AND a.instance_id = cip.instance_id
AND cip.party_source_table = 'HZ_PARTIES'
AND a.inventory_item_id = b.inventory_item_id
AND b.organization_id = p_inventory_org_id
-- Commented service_item_flag condition as this is not used
-- anymore and added contract_item_type_code condition
-- AND b.service_item_flag = 'N'
-- For ER 3701924
-- AND contract_item_type_code IS NULL
AND b.enabled_flag = 'Y'
-- Added for Bug# 2167129,2175917
AND b.serv_req_enabled_code = 'E'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(b.start_date_active, SYSDATE))
AND TRUNC(NVL(b.end_date_active, SYSDATE))
AND rownum<2;
SELECT 'x' INTO l_dummy
FROM csi_item_instances a,
mtl_system_items_b b,
csi_i_parties cip
WHERE a.instance_id = p_customer_product_id
AND a.serial_number = p_current_serial_number
-- AND a.owner_party_account_id = p_customer_id
AND cip.party_id = p_customer_id
AND a.instance_id = cip.instance_id
AND cip.party_source_table = 'HZ_PARTIES'
AND b.serv_req_enabled_code = 'E'
-- Added contract_item_type_code condition
-- For ER 3701924
-- AND contract_item_type_code IS NULL
-- Added for Bug# 2167129,2175917
AND a.location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS')
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
AND TRUNC(NVL(a.active_end_date, SYSDATE))
AND b.inventory_item_id = a.inventory_item_id
AND b.organization_id = p_inventory_org_id
AND rownum<2;
SELECT 'x' INTO l_dummy
FROM csi_item_instances a,
mtl_system_items_b b,
csi_i_parties cip
WHERE a.instance_id = p_customer_product_id
AND a.serial_number = p_current_serial_number
-- AND a.owner_party_account_id = p_customer_id
AND cip.party_id = p_customer_id
AND a.instance_id = cip.instance_id
AND cip.party_source_table = 'HZ_PARTIES'
AND b.serv_req_enabled_code = 'E'
-- Added contract_item_type_code condition
-- For ER 3701924
-- AND contract_item_type_code IS NULL
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
AND TRUNC(NVL(a.active_end_date, SYSDATE))
AND b.inventory_item_id = a.inventory_item_id
AND b.organization_id = p_inventory_org_id
AND rownum < 2;
select 'x' INTO l_dummy
from mtl_parameters
where organization_id = p_inv_org_id;
select 'x' INTO l_dummy
from bom_departments b
where b.department_id = p_owning_dept_id
and TRUNC(NVL(b.disable_date,SYSDATE+1)) > TRUNC(SYSDATE)
and b.organization_id = p_inv_org_id;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msi,
mtl_parameters mp
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.enabled_flag = 'Y'
AND msi.serv_req_enabled_code = 'E'
AND msi.organization_id = mp.organization_id
AND mp.maint_organization_id = p_maint_organization_id
AND mp.master_organization_id = p_inventory_org_id
AND msi.eam_item_type IN (1,3)
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active , SYSDATE))
AND rownum = 1;
SELECT 'X'
INTO l_dummy
FROM mtl_system_items_b msi,
mtl_parameters mp
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.enabled_flag = 'Y'
AND msi.serv_req_enabled_code = 'E'
AND msi.organization_id = mp.organization_id
AND mp.maint_organization_id = p_maint_organization_id
AND mp.organization_id = p_inventory_org_id
AND msi.eam_item_type IN (1,3)
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active , SYSDATE));
SELECT 'x' INTO l_dummy
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.enabled_flag = 'Y'
AND msi.serv_req_enabled_code = 'E'
-- For ER 3701924
-- AND msi.contract_item_type_code IS NULL
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active , SYSDATE))
AND msi.organization_id = p_inventory_org_id;
SELECT 'x' INTO l_dummy
FROM cs_sr_preferred_lang_v
WHERE language_code = p_cust_pref_lang_code
AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
AND trunc(nvl(end_date_active,sysdate));
SELECT DISTINCT lookup_code INTO l_dummy
FROM ar_lookups
WHERE lookup_type = 'COMMUNICATION_TYPE'
AND lookup_code = p_comm_pref_code;
SELECT 'x' INTO l_dummy
--FROM mtl_item_categories ic
FROM mtl_category_set_valid_cats ic
WHERE ic.category_id = p_category_id
AND ic.category_set_id = l_profile;
SELECT 'x' INTO l_dummy
FROM mtl_category_set_valid_cats
WHERE category_id = p_category_id
AND category_set_id = p_category_set_id;
SELECT 'x' INTO l_dummy
FROM mtl_item_categories
WHERE inventory_item_id = p_inventory_item_id
AND category_id = p_category_id
AND category_set_id = p_category_set_id
--AND organization_id = CS_STD.Get_Item_Valdn_Orgzn_Id;
IS SELECT COMMS_NL_TRACKABLE_FLAG
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_inventory_org_id;
SELECT 'x' INTO l_dummy
FROM csi_item_instances a,
mtl_system_items_b b,
csi_i_parties cip
WHERE a.instance_id = p_customer_product_id
AND a.external_reference = p_external_reference
AND cip.party_id = p_customer_id
AND a.instance_id = cip.instance_id
AND cip.party_source_table = 'HZ_PARTIES'
AND b.serv_req_enabled_code = 'E'
AND a.location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS')
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
AND TRUNC(NVL(a.active_end_date, SYSDATE))
AND b.inventory_item_id = a.inventory_item_id
AND b.organization_id = p_inventory_org_id
AND rownum<2;
SELECT 'x' INTO l_dummy
FROM csi_item_instances a,
mtl_system_items_b b,
csi_i_parties cip
WHERE a.instance_id = p_customer_product_id
AND a.external_reference = p_external_reference
AND cip.party_id = p_customer_id
AND a.instance_id = cip.instance_id
AND cip.party_source_table = 'HZ_PARTIES'
AND b.serv_req_enabled_code = 'E'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
AND TRUNC(NVL(a.active_end_date, SYSDATE))
AND b.inventory_item_id = a.inventory_item_id
AND b.organization_id = p_inventory_org_id
AND rownum < 2;
SELECT 'X' INTO l_dummy
FROM csi_item_instances a,
mtl_system_items_b b,
csi_i_parties cip
WHERE b.inventory_item_id = p_inventory_item_id
AND b.organization_id = p_inventory_org_id
AND b.inventory_item_id = a.inventory_item_id
AND cip.party_id = p_customer_id
AND a.instance_id = cip.instance_id
AND a.external_reference = p_external_reference
AND a.location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS')
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
AND TRUNC(NVL(a.active_end_date, SYSDATE))
AND rownum < 2;
SELECT 'X' INTO l_dummy
FROM csi_item_instances a,
mtl_system_items_b b,
csi_i_parties cip
WHERE b.inventory_item_id = p_inventory_item_id
AND b.organization_id = p_inventory_org_id
AND b.inventory_item_id = a.inventory_item_id
AND cip.party_id = p_customer_id
AND a.instance_id = cip.instance_id
AND a.external_reference = p_external_reference
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
AND TRUNC(NVL(a.active_end_date, SYSDATE))
AND rownum < 2;
SELECT 'x' INTO l_dummy
FROM csi_systems_vl
WHERE system_id = p_system_id;
SELECT p.party_type
FROM Hz_parties p ,
Hz_Relationships r
WHERE p.party_id = p_bill_to_party_id
AND p.status = 'A'
AND p.party_type IN ('PERSON','ORGANIZATION')
AND r.object_id = p_customer_id
AND r.subject_id = p.party_id
AND r.status = 'A'
-- Added to remove TCA violation -- relationship should be active -- anmukher -- 08/14/03
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(r.START_DATE, SYSDATE)) AND TRUNC(NVL(r.END_DATE, SYSDATE));
SELECT su.party_site_use_id
FROM Hz_Party_Sites s,
Hz_Party_Site_Uses su
WHERE s.party_site_id = su.party_site_id
AND s.party_site_id = p_bill_to_site_id
AND s.party_id = p_bill_to_party_id
AND s.status = 'A'
AND su.status = 'A'
-- Commented out to remove TCA Violation -- Party site use dates not to be checked -- anmukher -- 08/14/03
-- AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(su.begin_date,SYSDATE))
-- AND TRUNC(NVL(su.end_date,SYSDATE))
AND su.site_use_type = p_site_use_type;
-- Fix to bug # 2529361.included keyword distinct in select clause.
-- # 2615775 - removed reference to hz_party_site_uses..
SELECT DISTINCT s.party_id INTO l_install_customer_id
FROM Hz_Party_Sites s
WHERE s.party_site_id = p_install_site_id
AND s.status = 'A'
-- Belongs to SR Customer
AND ( s.party_id = p_customer_id
-- or one of its relationships
OR s.party_id IN (
SELECT r.party_id
FROM Hz_Relationships r
WHERE r.object_id = p_customer_id
AND r.status = 'A'
-- Added to remove TCA violation -- Relationship should be active -- anmukher -- 08/14/03
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(r.START_DATE, SYSDATE)) AND TRUNC(NVL(r.END_DATE, SYSDATE)) )
-- or one of its Related parties
OR s.party_id IN (
SELECT sub.party_id
FROM Hz_Parties p,
Hz_Parties sub,
Hz_Parties obj,
Hz_Relationships r
WHERE obj.party_id = p_customer_id
AND sub.status = 'A'
AND obj.status = 'A'
AND r.status = 'A'
AND p.status = 'A'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(r.START_DATE, SYSDATE)) AND TRUNC(NVL(r.END_DATE, SYSDATE))
AND sub.party_type IN ('PERSON','ORGANIZATION')
AND p.party_id = r.party_id
AND r.object_id = obj.party_id
AND r.subject_id = sub.party_id ));
select chr_id
into l_contract_id
from okc_k_lines_b
where id = p_contract_service_id;
SELECT contract_number INTO x_contract_number
FROM okc_k_headers_all_b
WHERE id = l_contract_id;
/* SELECT line_number, chr_id
INTO l_test, x_contract_id
FROM okc_k_lines_b
WHERE id = p_contract_service_id ; */
SELECT contract_number
INTO x_contract_number
FROM okc_k_headers_all_b
WHERE id = p_contract_id ;
SELECT 'x'
INTO l_test
FROM cs_sr_owners_v
WHERE resource_type = UPPER(p_resource_type)
AND resource_id = p_owner_id
AND support_site_id = p_support_site_id ;
SELECT grp.group_name INTO x_group_name
FROM jtf_rs_groups_vl grp,
jtf_rs_group_usages usg
WHERE grp.group_id = p_owner_group_id
AND grp.group_id = usg.group_id
AND usg.usage = 'SUPPORT'
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
AND NVL( end_date_active, SYSDATE ) ;
SELECT res.resource_name, res.resource_id, 'RS_' || res.category,
DECODE(res.category, 'EMPLOYEE', res.support_site_id, NULL)
FROM jtf_rs_group_members grp,
cs_jtf_rs_resource_extns_sec res
-- jtf_rs_resource_extns_tl res; Replaced with CS Secure view
SELECT resource_name, resource_id , 'RS_' || res.category,
DECODE(res.category, 'EMPLOYEE', res.support_site_id, NULL)
INTO x_owner_name, x_owner_id, x_resource_type, x_support_site_id
FROM cs_jtf_rs_resource_extns_sec res
-- FROM jtf_rs_resource_extns_vl res; Replaced with CS secure view
SELECT NVL(revision_qty_control_code,0)
INTO l_control_code
FROM mtl_system_items_vl item,
mtl_item_categories ic
WHERE item.organization_id = p_organization_id
AND item.inventory_item_id = p_platform_id
AND item.organization_id = ic.organization_id
AND item.inventory_item_id = ic.inventory_item_id
AND ic.category_set_id = fnd_profile.value('CS_SR_PLATFORM_CATEGORY_SET');
SELECT 'x'
INTO l_test
FROM cs_sr_new_acc_cp_rg_v
WHERE instance_id = p_cp_component_id
AND object_id = p_customer_product_id
AND rownum < 2;
SELECT revision_qty_control_code
INTO l_rev_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_inventory_org_id;
SELECT 'x'
INTO l_test
FROM csi_item_instances
WHERE instance_id = p_customer_product_id
AND inventory_revision = p_product_revision;
IS SELECT inventory_item_id, inventory_revision
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = p_instance_id;
IS SELECT revision_qty_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = l_inv_item_id
AND organization_id = p_inventory_org_id;
IS SELECT inventory_item_id
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = p_cp_component_id;
IS SELECT revision_qty_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = l_inv_item_id
AND organization_id = p_inventory_org_id;
SELECT 'x'
INTO l_test
FROM csi_item_instances
WHERE instance_id = p_cp_component_id
AND inventory_revision = p_component_version;
IS SELECT inventory_item_id
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = p_cp_subcomponent_id;
IS SELECT revision_qty_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = l_inv_item_id
AND organization_id = p_inventory_org_id;
SELECT 'x'
INTO l_test
FROM csi_item_instances
WHERE instance_id = p_cp_subcomponent_id
AND inventory_revision = p_subcomponent_version;
/* SELECT 'x'
INTO l_test
FROM cs_customer_products_all
WHERE customer_product_id = p_cp_subcomponent_id
AND config_parent_id = p_cp_component_id
AND config_root_id = p_customer_product_id; */
SELECT 'x'
INTO l_test
FROM cs_sr_new_acc_cp_rg_v
WHERE instance_id = p_cp_subcomponent_id
AND object_id = p_cp_component_id
AND rownum < 2;
SELECT revision_qty_control_code
INTO l_rev_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND serv_req_enabled_code = 'E'
AND organization_id = p_inventory_org_id;
SELECT 'x'
INTO l_test
FROM mtl_item_revisions
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_inventory_org_id
AND revision = p_inv_item_revision;
SELECT 'x'
INTO l_test
FROM cs_sr_inv_components_v
WHERE organization_id = p_inventory_org_id
AND component_id = p_inv_component_id
AND inventory_item_id = p_inventory_item_id;
SELECT 'X' INTO l_test
FROM
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items_b kfv
WHERE
bom.organization_id = kfv.organization_id AND
bic.bill_sequence_id = bom.common_bill_sequence_id AND
trunc(sysdate) between trunc(bic.effectivity_date) and
nvl(bic.disable_date, trunc(sysdate)) AND
trunc(sysdate) between trunc(nvl(kfv.start_date_active,sysdate)) and
nvl(kfv.end_date_active, trunc(sysdate)) AND
kfv.inventory_item_id = bic.component_item_id AND
bom.organization_id = p_inventory_org_id AND
bom.assembly_item_id = p_inventory_item_id AND
bic.component_item_id = p_inv_component_id AND
bom.alternate_bom_designator IS NULL;
SELECT revision_qty_control_code
INTO l_rev_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_inv_component_id
AND organization_id = p_inventory_org_id;
SELECT 'x'
INTO l_test
FROM mtl_item_revisions
WHERE organization_id = p_inventory_org_id
AND inventory_item_id = p_inv_component_id
AND revision = p_inv_component_version ;
SELECT 'x'
INTO l_test
FROM cs_sr_inv_subcomponents_v
WHERE organization_id = p_inventory_org_id
AND subcomponent_id = p_inv_subcomponent_id
AND component_id = p_inv_component_id
AND rownum < 2 ;
SELECT 'X' INTO l_test
FROM
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items_b kfv
WHERE
bom.organization_id = kfv.organization_id AND
bic.bill_sequence_id = bom.common_bill_sequence_id AND
trunc(sysdate) between trunc(bic.effectivity_date) and
nvl(bic.disable_date, trunc(sysdate)) AND
trunc(sysdate) between trunc(nvl(kfv.start_date_active,sysdate)) and
nvl(kfv.end_date_active, trunc(sysdate)) AND
kfv.inventory_item_id = bic.component_item_id AND
bom.organization_id = p_inventory_org_id AND
bom.assembly_item_id = p_inv_component_id AND
bic.component_item_id = p_inv_subcomponent_id AND
bom.alternate_bom_designator IS NULL;
SELECT revision_qty_control_code
INTO l_rev_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_inv_subcomponent_id
AND organization_id = p_inventory_org_id;
SELECT 'x'
INTO l_test
FROM mtl_item_revisions
WHERE organization_id = p_inventory_org_id
AND inventory_item_id = p_inv_subcomponent_id
AND revision = p_inv_subcomponent_version ;
SELECT 'x' INTO l_dummy
FROM CS_LOOKUPS
WHERE lookup_code = UPPER(p_sr_creation_channel)
AND lookup_type = 'CS_SR_CREATION_CHANNEL'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE)) ;
SELECT 'x'
INTO l_test
FROM cs_cp_languages
WHERE cp_language_id = p_language_id
AND customer_product_id = p_customer_product_id;
SELECT 'x' INTO l_test
FROM JTF_TERR_RSC_ALL
WHERE resource_id = p_owner_id
AND terr_id = p_territory_id;
SELECT a.contact_point_id INTO l_test
FROM HZ_CONTACT_POINTS a
WHERE a.contact_point_type = p_contact_point_type
AND a.contact_point_id = p_contact_point_id
AND a.OWNER_TABLE_ID = p_party_id
AND a.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND a.STATUS = 'A';
SELECT PHONE_ID
INTO l_test
FROM per_phones
WHERE parent_id = p_employee_id
AND parent_table = 'PER_ALL_PEOPLE_F'
AND phone_id = p_contact_point_id ;
SELECT 'x'
INTO l_test
FROM AR_LOOKUPS
WHERE lookup_code = UPPER(p_contact_point_type)
AND (lookup_type = 'COMMUNICATION_TYPE' OR lookup_type = 'PHONE_LINE_TYPE')
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE))
AND ROWNUM <= 1 ;
SELECT 'x' INTO l_test
FROM CS_LOOKUPS
WHERE lookup_code = UPPER(p_contact_type)
AND (lookup_type = 'CS_SR_CONTACT_TYPES')
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE))
AND ROWNUM <= 1;
SELECT 'x' INTO l_test
FROM hz_cust_accounts a
WHERE a.cust_account_id = p_account_id
AND a.party_id = p_customer_id
AND a.status = 'A'
-- Added to remove TCA violation -- Sysdate should fall within Account Activation Date and Termination Date -- anmukher -- 08/14/03
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.ACCOUNT_ACTIVATION_DATE, SYSDATE)) AND TRUNC(NVL(a.ACCOUNT_TERMINATION_DATE, SYSDATE));
SELECT multi_org_flag INTO l_multiorg_enabled
FROM FND_PRODUCT_GROUPS;
PROCEDURE Add_Same_Val_Update_Msg( p_token_an IN VARCHAR2,
p_token_p IN VARCHAR2 ) IS
BEGIN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
FND_MESSAGE.Set_Name('CS', 'CS_API_ALL_SAME_VAL_UPDATE');
END Add_Same_Val_Update_Msg;
SELECT hook_package, hook_api
FROM jtf_hooks_data
WHERE package_name = p_package_name
AND api_name = p_api_name
AND execute_flag = 'Y'
AND processing_type = p_processing_type
ORDER BY execution_order;
SELECT 'x' INTO l_dummy
FROM hz_cust_accounts
WHERE cust_account_id = p_account_id
AND party_id = p_party_id
AND status = 'A';
SELECT 'x' INTO l_dummy
FROM CS_LOOKUPS
WHERE lookup_code = UPPER(p_source_program_code)
AND lookup_type = 'CS_SR_SOURCE_PROGRAMS'
AND enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE));
SELECT 'x' INTO l_dummy
FROM CS_LOOKUPS
WHERE lookup_code = UPPER(p_INC_DIST_QUAL_UOM)
AND lookup_type = 'CS_SR_DISTANCE_UOM'
AND enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE));
SELECT 'x' INTO l_dummy
FROM CS_LOOKUPS
WHERE lookup_code = UPPER(p_INC_DIRECTION_QUAL)
AND lookup_type = 'CS_SR_DIRECTIONS'
AND enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE));
PROCEDURE Add_Same_Val_Update_Msg( p_token_an IN VARCHAR2,
p_token_p IN VARCHAR2,
p_table_name IN VARCHAR2 ,
p_column_name IN VARCHAR2 ) IS
l_associated_col1 VARCHAR2(240);
FND_MESSAGE.Set_Name('CS', 'CS_API_ALL_SAME_VAL_UPDATE');
END Add_Same_Val_Update_Msg;
SELECT s.status
FROM Hz_Party_Sites s,
Hz_Party_Site_Uses su
WHERE s.party_site_id = su.party_site_id
AND s.party_site_id = p_site_id
AND s.party_id = p_party_id
AND su.party_site_use_id = p_site_use_id
AND s.status = l_literal_a
AND su.status = l_literal_a
-- Commented out to remove TCA Violation -- Party site use dates not to be checked -- anmukher -- 08/14/03
-- AND trunc(sysdate) between trunc(nvl(su.begin_date,sysdate))
-- and trunc(nvl(su.end_date,sysdate))
AND su.site_use_type = p_site_use_type;
SELECT s.party_site_id
FROM Hz_Party_Sites s,
Hz_Party_Site_Uses su
WHERE s.party_site_id = su.party_site_id
AND su.party_site_use_id = p_site_use_id
AND s.party_id = p_party_id
AND s.status = 'A'
AND su.status = 'A'
-- Commented out to remove TCA Violation -- Party site use dates not to be checked -- anmukher -- 08/14/03
-- AND trunc(sysdate) between trunc(nvl(su.begin_date,sysdate))
-- and trunc(nvl(su.end_date,sysdate))
AND su.site_use_type = p_site_use_type;
select sr_agent_security
into l_sr_agent_security
from cs_system_options
where rownum = 1;
select count(*)
into l_dummy
from jtf_tasks_b a,
jtf_task_statuses_b b
where a.source_object_id = p_incident_id
and a.source_object_type_code = 'SR'
and a.task_status_id = b.task_status_id
and b.closed_flag <> 'Y';
SELECT 'x' INTO l_dummy
FROM csi_item_instances a
WHERE a.instance_id = p_customer_product_id
AND a.location_id = p_incident_location_id;
SELECT a.install_location_id,a.install_location_type_code
INTO l_install_site_id,l_install_site_type
FROM csi_item_instances a
WHERE a.instance_id = p_customer_product_id;
SELECT close_flag
INTO l_close_flag
FROM cs_incident_statuses_b
WHERE incident_status_id = p_status_id;
SELECT COUNT(*)
INTO l_dummy
FROM jtf_tasks_b
WHERE source_object_id = p_incident_id
AND source_object_type_code = 'SR'
AND NVL(open_flag,'Y') = 'Y'
AND restrict_closure_flag = 'Y'
AND NVL(deleted_flag,'N') = 'N';
SELECT 'X'
INTO l_dummy
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = p_cp_component_id
AND INVENTORY_ITEM_ID = p_inv_component_id;
SELECT 'X' INTO l_dummy
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = p_cp_subcomponent_id
AND INVENTORY_ITEM_ID = p_inv_subcomponent_id;
SELECT 'Y'
INTO l_access
FROM cs_incidents_b_sec
WHERE incident_id = p_incident_id;
select 1
into l_dummy
from csi_item_instances
where
*/
END CP_COMP_ID_CROSS_VAL;
-- The system could not update the Type. The Type cannot be changed from a
-- non-Maintenance Type to a Maintenance Type.
fnd_message.set_name ('CS', 'CS_SR_DISALLOW_TYPE_UPD_2');
select business_process_id
into l_busi_proc_id
from cs_incident_types_b
where incident_type_id = l_sr_type_id;
SELECT
INCIDENT_STATUS_ID INCIDENT_STATUS_ID ,
INCIDENT_STATUS_ID OLD_INCIDENT_STATUS_ID ,
'N' CHANGE_INCIDENT_STATUS_FLAG ,
INCIDENT_TYPE_ID INCIDENT_TYPE_ID ,
INCIDENT_TYPE_ID OLD_INCIDENT_TYPE_ID,
'N' CHANGE_INCIDENT_TYPE_FLAG ,
INCIDENT_URGENCY_ID INCIDENT_URGENCY_ID ,
INCIDENT_URGENCY_ID OLD_INCIDENT_URGENCY_ID ,
'N' CHANGE_INCIDENT_URGENCY_FLAG ,
INCIDENT_SEVERITY_ID INCIDENT_SEVERITY_ID,
INCIDENT_SEVERITY_ID OLD_INCIDENT_SEVERITY_ID ,
'N' CHANGE_INCIDENT_SEVERITY_FLAG ,
TO_NUMBER(NULL) RESPONSIBLE_GROUP_ID,
TO_NUMBER(NULL) OLD_RESPONSIBLE_GROUP_ID ,
'N' CHANGE_RESPONSIBLE_GROUP_FLAG ,
INCIDENT_OWNER_ID INCIDENT_OWNER_ID,
INCIDENT_OWNER_ID OLD_INCIDENT_OWNER_ID ,
'N' CHANGE_INCIDENT_OWNER_FLAG,
'N' CREATE_MANUAL_ACTION ,
to_number(null) ACTION_ID ,
EXPECTED_RESOLUTION_DATE EXPECTED_RESOLUTION_DATE ,
EXPECTED_RESOLUTION_DATE OLD_EXPECTED_RESOLUTION_DATE ,
'N' CHANGE_RESOLUTION_FLAG ,
'N' NEW_WORKFLOW_FLAG,
TO_CHAR(NULL) WORKFLOW_PROCESS_NAME ,
TO_CHAR(NULL) WORKFLOW_PROCESS_ITEMKEY ,
OWNER_GROUP_ID GROUP_ID ,
OWNER_GROUP_ID OLD_GROUP_ID ,
'N' CHANGE_GROUP_FLAG,
OBLIGATION_DATE OBLIGATION_DATE ,
OBLIGATION_DATE OLD_OBLIGATION_DATE ,
'N' CHANGE_OBLIGATION_FLAG ,
SITE_ID SITE_ID ,
SITE_ID OLD_SITE_ID ,
'N' CHANGE_SITE_FLAG,
BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID ,
BILL_TO_CONTACT_ID OLD_BILL_TO_CONTACT_ID ,
'N' CHANGE_BILL_TO_FLAG,
SHIP_TO_CONTACT_ID SHIP_TO_CONTACT_ID ,
SHIP_TO_CONTACT_ID OLD_SHIP_TO_CONTACT_ID ,
'N' CHANGE_SHIP_TO_FLAG ,
INCIDENT_DATE INCIDENT_DATE ,
INCIDENT_DATE OLD_INCIDENT_DATE,
'N' CHANGE_INCIDENT_DATE_FLAG ,
CLOSE_DATE CLOSE_DATE ,
CLOSE_DATE OLD_CLOSE_DATE ,
'N' CHANGE_CLOSE_DATE_FLAG ,
CUSTOMER_PRODUCT_ID CUSTOMER_PRODUCT_ID ,
CUSTOMER_PRODUCT_ID OLD_CUSTOMER_PRODUCT_ID ,
'N' CHANGE_CUSTOMER_PRODUCT_FLAG ,
PLATFORM_ID PLATFORM_ID ,
PLATFORM_ID OLD_PLATFORM_ID ,
'N' CHANGE_PLATFORM_ID_FLAG ,
PLATFORM_VERSION_ID PLATFORM_VERSION_ID,
PLATFORM_VERSION_ID OLD_PLATFORM_VERSION_ID ,
'N' CHANGE_PLAT_VER_ID_FLAG ,
CP_COMPONENT_ID CP_COMPONENT_ID ,
CP_COMPONENT_ID OLD_CP_COMPONENT_ID ,
'N' CHANGE_CP_COMPONENT_ID_FLAG,
CP_COMPONENT_VERSION_ID CP_COMPONENT_VERSION_ID ,
CP_COMPONENT_VERSION_ID OLD_CP_COMPONENT_VERSION_ID,
'N' CHANGE_CP_COMP_VER_ID_FLAG ,
CP_SUBCOMPONENT_ID CP_SUBCOMPONENT_ID ,
CP_SUBCOMPONENT_ID OLD_CP_SUBCOMPONENT_ID ,
'N' CHANGE_CP_SUBCOMPONENT_ID_FLAG ,
CP_SUBCOMPONENT_VERSION_ID CP_SUBCOMPONENT_VERSION_ID ,
CP_SUBCOMPONENT_VERSION_ID OLD_CP_SUBCOMPONENT_VERSION_ID ,
'N' CHANGE_CP_SUBCOMP_VER_ID_FLAG ,
LANGUAGE_ID LANGUAGE_ID ,
LANGUAGE_ID OLD_LANGUAGE_ID ,
'N' CHANGE_LANGUAGE_ID_FLAG ,
TERRITORY_ID TERRITORY_ID ,
TERRITORY_ID OLD_TERRITORY_ID ,
'N' CHANGE_TERRITORY_ID_FLAG ,
CP_REVISION_ID CP_REVISION_ID ,
CP_REVISION_ID OLD_CP_REVISION_ID ,
'N' CHANGE_CP_REVISION_ID_FLAG ,
INV_ITEM_REVISION INV_ITEM_REVISION ,
INV_ITEM_REVISION OLD_INV_ITEM_REVISION ,
'N' CHANGE_INV_ITEM_REVISION ,
INV_COMPONENT_ID INV_COMPONENT_ID ,
INV_COMPONENT_ID OLD_INV_COMPONENT_ID,
'N' CHANGE_INV_COMPONENT_ID ,
INV_COMPONENT_VERSION INV_COMPONENT_VERSION ,
INV_COMPONENT_VERSION OLD_INV_COMPONENT_VERSION ,
'N' CHANGE_INV_COMPONENT_VERSION ,
INV_SUBCOMPONENT_ID INV_SUBCOMPONENT_ID,
INV_SUBCOMPONENT_ID OLD_INV_SUBCOMPONENT_ID ,
'N' CHANGE_INV_SUBCOMPONENT_ID ,
INV_SUBCOMPONENT_VERSION INV_SUBCOMPONENT_VERSION ,
INV_SUBCOMPONENT_VERSION OLD_INV_SUBCOMPONENT_VERSION ,
'N' CHANGE_INV_SUBCOMP_VERSION ,
RESOURCE_TYPE RESOURCE_TYPE ,
RESOURCE_TYPE OLD_RESOURCE_TYPE,
'N' CHANGE_RESOURCE_TYPE_FLAG ,
SECURITY_GROUP_ID SECURITY_GROUP_ID ,
'N' UPGRADED_STATUS_FLAG,
GROUP_TYPE OLD_GROUP_TYPE ,
GROUP_TYPE GROUP_TYPE ,
'N' CHANGE_GROUP_TYPE_FLAG ,
OWNER_ASSIGNED_TIME OLD_OWNER_ASSIGNED_TIME ,
OWNER_ASSIGNED_TIME OWNER_ASSIGNED_TIME ,
'N' CHANGE_ASSIGNED_TIME_FLAG ,
INV_PLATFORM_ORG_ID INV_PLATFORM_ORG_ID,
INV_PLATFORM_ORG_ID OLD_INV_PLATFORM_ORG_ID ,
'N' CHANGE_PLATFORM_ORG_ID_FLAG,
COMPONENT_VERSION COMPONENT_VERSION ,
COMPONENT_VERSION OLD_COMPONENT_VERSION ,
'N' CHANGE_COMP_VER_FLAG,
SUBCOMPONENT_VERSION SUBCOMPONENT_VERSION ,
SUBCOMPONENT_VERSION OLD_SUBCOMPONENT_VERSION ,
'N' CHANGE_SUBCOMP_VER_FLAG ,
PRODUCT_REVISION PRODUCT_REVISION ,
PRODUCT_REVISION OLD_PRODUCT_REVISION,
'N' CHANGE_PRODUCT_REVISION_FLAG ,
STATUS_FLAG STATUS_FLAG ,
STATUS_FLAG OLD_STATUS_FLAG ,
'N' CHANGE_STATUS_FLAG ,
INVENTORY_ITEM_ID INVENTORY_ITEM_ID ,
INVENTORY_ITEM_ID OLD_INVENTORY_ITEM_ID ,
'N' CHANGE_INVENTORY_ITEM_FLAG,
INV_ORGANIZATION_ID INV_ORGANIZATION_ID ,
INV_ORGANIZATION_ID OLD_INV_ORGANIZATION_ID ,
'N' CHANGE_INV_ORGANIZATION_FLAG ,
PRIMARY_CONTACT_ID PRIMARY_CONTACT_ID ,
'N' CHANGE_PRIMARY_CONTACT_FLAG ,
PRIMARY_CONTACT_ID OLD_PRIMARY_CONTACT_ID ,
TO_CHAR(null) UPGRADE_FLAG_FOR_CREATE,
INCIDENT_NUMBER OLD_INCIDENT_NUMBER,
INCIDENT_NUMBER INCIDENT_NUMBER,
CUSTOMER_ID OLD_CUSTOMER_ID ,
CUSTOMER_ID CUSTOMER_ID ,
BILL_TO_SITE_USE_ID OLD_BILL_TO_SITE_USE_ID,
BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID,
EMPLOYEE_ID OLD_EMPLOYEE_ID,
EMPLOYEE_ID EMPLOYEE_ID,
SHIP_TO_SITE_USE_ID OLD_SHIP_TO_SITE_USE_ID,
SHIP_TO_SITE_USE_ID SHIP_TO_SITE_USE_ID,
PROBLEM_CODE OLD_PROBLEM_CODE,
PROBLEM_CODE PROBLEM_CODE,
ACTUAL_RESOLUTION_DATE OLD_ACTUAL_RESOLUTION_DATE,
ACTUAL_RESOLUTION_DATE ACTUAL_RESOLUTION_DATE,
INSTALL_SITE_USE_ID OLD_INSTALL_SITE_USE_ID,
INSTALL_SITE_USE_ID INSTALL_SITE_USE_ID,
CURRENT_SERIAL_NUMBER OLD_CURRENT_SERIAL_NUMBER,
CURRENT_SERIAL_NUMBER CURRENT_SERIAL_NUMBER,
SYSTEM_ID OLD_SYSTEM_ID,
SYSTEM_ID SYSTEM_ID,
INCIDENT_ATTRIBUTE_1 OLD_INCIDENT_ATTRIBUTE_1,
INCIDENT_ATTRIBUTE_1 INCIDENT_ATTRIBUTE_1,
INCIDENT_ATTRIBUTE_2 OLD_INCIDENT_ATTRIBUTE_2 ,
INCIDENT_ATTRIBUTE_2 INCIDENT_ATTRIBUTE_2,
INCIDENT_ATTRIBUTE_3 OLD_INCIDENT_ATTRIBUTE_3,
INCIDENT_ATTRIBUTE_3 INCIDENT_ATTRIBUTE_3,
INCIDENT_ATTRIBUTE_4 OLD_INCIDENT_ATTRIBUTE_4,
INCIDENT_ATTRIBUTE_4 INCIDENT_ATTRIBUTE_4,
INCIDENT_ATTRIBUTE_5 OLD_INCIDENT_ATTRIBUTE_5,
INCIDENT_ATTRIBUTE_5 INCIDENT_ATTRIBUTE_5,
INCIDENT_ATTRIBUTE_6 OLD_INCIDENT_ATTRIBUTE_6,
INCIDENT_ATTRIBUTE_6 INCIDENT_ATTRIBUTE_6,
INCIDENT_ATTRIBUTE_7 OLD_INCIDENT_ATTRIBUTE_7,
INCIDENT_ATTRIBUTE_7 INCIDENT_ATTRIBUTE_7,
INCIDENT_ATTRIBUTE_8 OLD_INCIDENT_ATTRIBUTE_8,
INCIDENT_ATTRIBUTE_8 INCIDENT_ATTRIBUTE_8,
INCIDENT_ATTRIBUTE_9 OLD_INCIDENT_ATTRIBUTE_9,
INCIDENT_ATTRIBUTE_9 INCIDENT_ATTRIBUTE_9,
INCIDENT_ATTRIBUTE_10 OLD_INCIDENT_ATTRIBUTE_10,
INCIDENT_ATTRIBUTE_10 INCIDENT_ATTRIBUTE_10,
INCIDENT_ATTRIBUTE_11 OLD_INCIDENT_ATTRIBUTE_11,
INCIDENT_ATTRIBUTE_11 INCIDENT_ATTRIBUTE_11,
INCIDENT_ATTRIBUTE_12 OLD_INCIDENT_ATTRIBUTE_12,
INCIDENT_ATTRIBUTE_12 INCIDENT_ATTRIBUTE_12,
INCIDENT_ATTRIBUTE_13 OLD_INCIDENT_ATTRIBUTE_13,
INCIDENT_ATTRIBUTE_13 INCIDENT_ATTRIBUTE_13,
INCIDENT_ATTRIBUTE_14 OLD_INCIDENT_ATTRIBUTE_14,
INCIDENT_ATTRIBUTE_14 INCIDENT_ATTRIBUTE_14,
INCIDENT_ATTRIBUTE_15 OLD_INCIDENT_ATTRIBUTE_15,
INCIDENT_ATTRIBUTE_15 INCIDENT_ATTRIBUTE_15,
INCIDENT_CONTEXT OLD_INCIDENT_CONTEXT,
INCIDENT_CONTEXT INCIDENT_CONTEXT,
RESOLUTION_CODE OLD_RESOLUTION_CODE,
RESOLUTION_CODE RESOLUTION_CODE,
ORIGINAL_ORDER_NUMBER OLD_ORIGINAL_ORDER_NUMBER,
ORIGINAL_ORDER_NUMBER ORIGINAL_ORDER_NUMBER,
ORG_ID OLD_ORG_ID,
ORG_ID ORG_ID,
PURCHASE_ORDER_NUM OLD_PURCHASE_ORDER_NUMBER,
PURCHASE_ORDER_NUM PURCHASE_ORDER_NUMBER,
PUBLISH_FLAG OLD_PUBLISH_FLAG,
PUBLISH_FLAG PUBLISH_FLAG,
QA_COLLECTION_ID OLD_QA_COLLECTION_ID,
QA_COLLECTION_ID QA_COLLECTION_ID,
CONTRACT_ID OLD_CONTRACT_ID,
CONTRACT_ID CONTRACT_ID,
CONTRACT_NUMBER OLD_CONTRACT_NUMBER,
CONTRACT_NUMBER CONTRACT_NUMBER,
CONTRACT_SERVICE_ID OLD_CONTRACT_SERVICE_ID,
CONTRACT_SERVICE_ID CONTRACT_SERVICE_ID,
TIME_ZONE_ID OLD_TIME_ZONE_ID,
TIME_ZONE_ID TIME_ZONE_ID,
ACCOUNT_ID OLD_ACCOUNT_ID,
ACCOUNT_ID ACCOUNT_ID,
TIME_DIFFERENCE OLD_TIME_DIFFERENCE,
TIME_DIFFERENCE TIME_DIFFERENCE,
CUSTOMER_PO_NUMBER OLD_CUSTOMER_PO_NUMBER,
CUSTOMER_PO_NUMBER CUSTOMER_PO_NUMBER,
CUSTOMER_TICKET_NUMBER OLD_CUSTOMER_TICKET_NUMBER,
CUSTOMER_TICKET_NUMBER CUSTOMER_TICKET_NUMBER,
CUSTOMER_SITE_ID OLD_CUSTOMER_SITE_ID,
CUSTOMER_SITE_ID CUSTOMER_SITE_ID,
CALLER_TYPE OLD_CALLER_TYPE,
CALLER_TYPE CALLER_TYPE,
SECURITY_GROUP_ID OLD_SECURITY_GROUP_ID,
ORIG_SYSTEM_REFERENCE OLD_ORIG_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE,
ORIG_SYSTEM_REFERENCE_ID OLD_ORIG_SYSTEM_REFERENCE_ID,
ORIG_SYSTEM_REFERENCE_ID ORIG_SYSTEM_REFERENCE_ID,
REQUEST_ID REQUEST_ID,
PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID,
PROGRAM_ID PROGRAM_ID,
PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE,
PROJECT_NUMBER OLD_PROJECT_NUMBER,
PROJECT_NUMBER PROJECT_NUMBER,
PLATFORM_VERSION OLD_PLATFORM_VERSION,
PLATFORM_VERSION PLATFORM_VERSION,
DB_VERSION OLD_DB_VERSION,
DB_VERSION DB_VERSION,
CUST_PREF_LANG_ID OLD_CUST_PREF_LANG_ID,
CUST_PREF_LANG_ID CUST_PREF_LANG_ID,
TIER OLD_TIER,
TIER TIER,
CATEGORY_ID OLD_CATEGORY_ID,
CATEGORY_ID CATEGORY_ID,
OPERATING_SYSTEM OLD_OPERATING_SYSTEM,
OPERATING_SYSTEM OPERATING_SYSTEM,
OPERATING_SYSTEM_VERSION OLD_OPERATING_SYSTEM_VERSION,
OPERATING_SYSTEM_VERSION OPERATING_SYSTEM_VERSION,
DATABASE OLD_DATABASE ,
DATABASE DATABASE,
GROUP_TERRITORY_ID OLD_GROUP_TERRITORY_ID,
GROUP_TERRITORY_ID GROUP_TERRITORY_ID,
COMM_PREF_CODE OLD_COMM_PREF_CODE,
COMM_PREF_CODE COMM_PREF_CODE,
LAST_UPDATE_CHANNEL OLD_LAST_UPDATE_CHANNEL,
LAST_UPDATE_CHANNEL LAST_UPDATE_CHANNEL,
CUST_PREF_LANG_CODE OLD_CUST_PREF_LANG_CODE,
CUST_PREF_LANG_CODE CUST_PREF_LANG_CODE,
ERROR_CODE OLD_ERROR_CODE,
ERROR_CODE ERROR_CODE,
CATEGORY_SET_ID OLD_CATEGORY_SET_ID,
CATEGORY_SET_ID CATEGORY_SET_ID,
EXTERNAL_REFERENCE OLD_EXTERNAL_REFERENCE,
EXTERNAL_REFERENCE EXTERNAL_REFERENCE,
INCIDENT_OCCURRED_DATE OLD_INCIDENT_OCCURRED_DATE,
INCIDENT_OCCURRED_DATE INCIDENT_OCCURRED_DATE,
INCIDENT_RESOLVED_DATE OLD_INCIDENT_RESOLVED_DATE,
INCIDENT_RESOLVED_DATE INCIDENT_RESOLVED_DATE,
INC_RESPONDED_BY_DATE OLD_INC_RESPONDED_BY_DATE,
INC_RESPONDED_BY_DATE INC_RESPONDED_BY_DATE,
INCIDENT_LOCATION_ID OLD_INCIDENT_LOCATION_ID ,
INCIDENT_LOCATION_ID INCIDENT_LOCATION_ID,
INCIDENT_ADDRESS OLD_INCIDENT_ADDRESS,
INCIDENT_ADDRESS INCIDENT_ADDRESS,
INCIDENT_CITY OLD_INCIDENT_CITY,
INCIDENT_CITY INCIDENT_CITY,
INCIDENT_STATE OLD_INCIDENT_STATE,
INCIDENT_STATE INCIDENT_STATE,
INCIDENT_COUNTRY OLD_INCIDENT_COUNTRY,
INCIDENT_COUNTRY INCIDENT_COUNTRY,
INCIDENT_PROVINCE OLD_INCIDENT_PROVINCE,
INCIDENT_PROVINCE INCIDENT_PROVINCE,
INCIDENT_POSTAL_CODE OLD_INCIDENT_POSTAL_CODE,
INCIDENT_POSTAL_CODE INCIDENT_POSTAL_CODE,
INCIDENT_COUNTY OLD_INCIDENT_COUNTY,
INCIDENT_COUNTY INCIDENT_COUNTY,
SR_CREATION_CHANNEL OLD_SR_CREATION_CHANNEL,
SR_CREATION_CHANNEL SR_CREATION_CHANNEL,
DEF_DEFECT_ID OLD_DEF_DEFECT_ID,
DEF_DEFECT_ID DEF_DEFECT_ID,
DEF_DEFECT_ID2 OLD_DEF_DEFECT_ID2,
DEF_DEFECT_ID2 DEF_DEFECT_ID2,
EXTERNAL_ATTRIBUTE_1 OLD_EXTERNAL_ATTRIBUTE_1,
EXTERNAL_ATTRIBUTE_1 EXTERNAL_ATTRIBUTE_1,
EXTERNAL_ATTRIBUTE_2 OLD_EXTERNAL_ATTRIBUTE_2,
EXTERNAL_ATTRIBUTE_2 EXTERNAL_ATTRIBUTE_2,
EXTERNAL_ATTRIBUTE_3 OLD_EXTERNAL_ATTRIBUTE_3,
EXTERNAL_ATTRIBUTE_3 EXTERNAL_ATTRIBUTE_3,
EXTERNAL_ATTRIBUTE_4 OLD_EXTERNAL_ATTRIBUTE_4,
EXTERNAL_ATTRIBUTE_4 EXTERNAL_ATTRIBUTE_4,
EXTERNAL_ATTRIBUTE_5 OLD_EXTERNAL_ATTRIBUTE_5,
EXTERNAL_ATTRIBUTE_5 EXTERNAL_ATTRIBUTE_5,
EXTERNAL_ATTRIBUTE_6 OLD_EXTERNAL_ATTRIBUTE_6,
EXTERNAL_ATTRIBUTE_6 EXTERNAL_ATTRIBUTE_6,
EXTERNAL_ATTRIBUTE_7 OLD_EXTERNAL_ATTRIBUTE_7,
EXTERNAL_ATTRIBUTE_7 EXTERNAL_ATTRIBUTE_7,
EXTERNAL_ATTRIBUTE_8 OLD_EXTERNAL_ATTRIBUTE_8,
EXTERNAL_ATTRIBUTE_8 EXTERNAL_ATTRIBUTE_8,
EXTERNAL_ATTRIBUTE_9 OLD_EXTERNAL_ATTRIBUTE_9,
EXTERNAL_ATTRIBUTE_9 EXTERNAL_ATTRIBUTE_9,
EXTERNAL_ATTRIBUTE_10 OLD_EXTERNAL_ATTRIBUTE_10,
EXTERNAL_ATTRIBUTE_10 EXTERNAL_ATTRIBUTE_10,
EXTERNAL_ATTRIBUTE_11 OLD_EXTERNAL_ATTRIBUTE_11,
EXTERNAL_ATTRIBUTE_11 EXTERNAL_ATTRIBUTE_11,
EXTERNAL_ATTRIBUTE_12 OLD_EXTERNAL_ATTRIBUTE_12,
EXTERNAL_ATTRIBUTE_12 EXTERNAL_ATTRIBUTE_12,
EXTERNAL_ATTRIBUTE_13 OLD_EXTERNAL_ATTRIBUTE_13,
EXTERNAL_ATTRIBUTE_13 EXTERNAL_ATTRIBUTE_13,
EXTERNAL_ATTRIBUTE_14 OLD_EXTERNAL_ATTRIBUTE_14,
EXTERNAL_ATTRIBUTE_14 EXTERNAL_ATTRIBUTE_14,
EXTERNAL_ATTRIBUTE_15 OLD_EXTERNAL_ATTRIBUTE_15,
EXTERNAL_ATTRIBUTE_15 EXTERNAL_ATTRIBUTE_15,
EXTERNAL_CONTEXT OLD_EXTERNAL_CONTEXT,
EXTERNAL_CONTEXT EXTERNAL_CONTEXT,
LAST_UPDATE_PROGRAM_CODE OLD_LAST_UPDATE_PROGRAM_CODE,
LAST_UPDATE_PROGRAM_CODE LAST_UPDATE_PROGRAM_CODE,
CREATION_PROGRAM_CODE OLD_CREATION_PROGRAM_CODE,
CREATION_PROGRAM_CODE CREATION_PROGRAM_CODE,
COVERAGE_TYPE OLD_COVERAGE_TYPE,
COVERAGE_TYPE COVERAGE_TYPE,
BILL_TO_ACCOUNT_ID OLD_BILL_TO_ACCOUNT_ID,
BILL_TO_ACCOUNT_ID BILL_TO_ACCOUNT_ID,
SHIP_TO_ACCOUNT_ID OLD_SHIP_TO_ACCOUNT_ID,
SHIP_TO_ACCOUNT_ID SHIP_TO_ACCOUNT_ID,
CUSTOMER_EMAIL_ID OLD_CUSTOMER_EMAIL_ID,
CUSTOMER_EMAIL_ID CUSTOMER_EMAIL_ID,
CUSTOMER_PHONE_ID OLD_CUSTOMER_PHONE_ID,
CUSTOMER_PHONE_ID CUSTOMER_PHONE_ID,
BILL_TO_PARTY_ID OLD_BILL_TO_PARTY_ID,
BILL_TO_PARTY_ID BILL_TO_PARTY_ID,
SHIP_TO_PARTY_ID OLD_SHIP_TO_PARTY_ID,
SHIP_TO_PARTY_ID SHIP_TO_PARTY_ID,
BILL_TO_SITE_ID OLD_BILL_TO_SITE_ID,
BILL_TO_SITE_ID BILL_TO_SITE_ID,
SHIP_TO_SITE_ID OLD_SHIP_TO_SITE_ID ,
SHIP_TO_SITE_ID SHIP_TO_SITE_ID,
PROGRAM_LOGIN_ID OLD_PROGRAM_LOGIN_ID,
PROGRAM_LOGIN_ID PROGRAM_LOGIN_ID,
INCIDENT_POINT_OF_INTEREST OLD_INCIDENT_POINT_OF_INTEREST,
INCIDENT_POINT_OF_INTEREST INCIDENT_POINT_OF_INTEREST,
INCIDENT_CROSS_STREET OLD_INCIDENT_CROSS_STREET,
INCIDENT_CROSS_STREET INCIDENT_CROSS_STREET,
INCIDENT_DIRECTION_QUALIFIER OLD_INCIDENT_DIRECTION_QUALIF,
INCIDENT_DIRECTION_QUALIFIER INCIDENT_DIRECTION_QUALIF,
INCIDENT_DISTANCE_QUALIFIER OLD_INCIDENT_DISTANCE_QUALIF,
INCIDENT_DISTANCE_QUALIFIER INCIDENT_DISTANCE_QUALIF,
INCIDENT_DISTANCE_QUAL_UOM OLD_INCIDENT_DISTANCE_QUAL_UOM,
INCIDENT_DISTANCE_QUAL_UOM INCIDENT_DISTANCE_QUAL_UOM,
INCIDENT_ADDRESS2 OLD_INCIDENT_ADDRESS2,
INCIDENT_ADDRESS2 INCIDENT_ADDRESS2,
INCIDENT_ADDRESS3 OLD_INCIDENT_ADDRESS3,
INCIDENT_ADDRESS3 INCIDENT_ADDRESS3,
INCIDENT_ADDRESS4 OLD_INCIDENT_ADDRESS4,
INCIDENT_ADDRESS4 INCIDENT_ADDRESS4,
INCIDENT_ADDRESS_STYLE OLD_INCIDENT_ADDRESS_STYLE,
INCIDENT_ADDRESS_STYLE INCIDENT_ADDRESS_STYLE,
INCIDENT_ADDR_LINES_PHONETIC OLD_INCIDENT_ADDR_LNS_PHONETIC,
INCIDENT_ADDR_LINES_PHONETIC INCIDENT_ADDR_LNS_PHONETIC,
INCIDENT_PO_BOX_NUMBER OLD_INCIDENT_PO_BOX_NUMBER,
INCIDENT_PO_BOX_NUMBER INCIDENT_PO_BOX_NUMBER,
INCIDENT_HOUSE_NUMBER OLD_INCIDENT_HOUSE_NUMBER,
INCIDENT_HOUSE_NUMBER INCIDENT_HOUSE_NUMBER,
INCIDENT_STREET_SUFFIX OLD_INCIDENT_STREET_SUFFIX,
INCIDENT_STREET_SUFFIX INCIDENT_STREET_SUFFIX,
INCIDENT_STREET OLD_INCIDENT_STREET,
INCIDENT_STREET INCIDENT_STREET,
INCIDENT_STREET_NUMBER OLD_INCIDENT_STREET_NUMBER,
INCIDENT_STREET_NUMBER INCIDENT_STREET_NUMBER,
INCIDENT_FLOOR OLD_INCIDENT_FLOOR,
INCIDENT_FLOOR INCIDENT_FLOOR,
INCIDENT_SUITE OLD_INCIDENT_SUITE,
INCIDENT_SUITE INCIDENT_SUITE,
INCIDENT_POSTAL_PLUS4_CODE OLD_INCIDENT_POSTAL_PLUS4_CODE,
INCIDENT_POSTAL_PLUS4_CODE INCIDENT_POSTAL_PLUS4_CODE,
INCIDENT_POSITION OLD_INCIDENT_POSITION,
INCIDENT_POSITION INCIDENT_POSITION,
INCIDENT_LOCATION_DIRECTIONS OLD_INCIDENT_LOC_DIRECTIONS,
INCIDENT_LOCATION_DIRECTIONS INCIDENT_LOC_DIRECTIONS,
INCIDENT_LOCATION_DESCRIPTION OLD_INCIDENT_LOC_DESCRIPTION,
INCIDENT_LOCATION_DESCRIPTION INCIDENT_LOC_DESCRIPTION,
INSTALL_SITE_ID OLD_INSTALL_SITE_ID,
INSTALL_SITE_ID INSTALL_SITE_ID,
INCIDENT_LAST_MODIFIED_DATE INCIDENT_LAST_MODIFIED_DATE,
TO_CHAR(null) UPDATED_ENTITY_CODE,
TO_NUMBER(null) UPDATED_ENTITY_ID,
TO_CHAR(null) ENTITY_ACTIVITY_CODE,
TIER_VERSION OLD_TIER_VERSION,
TIER_VERSION TIER_VERSION,
OBJECT_VERSION_NUMBER OLD_INC_OBJECT_VERSION_NUMBER ,
OBJECT_VERSION_NUMBER INC_OBJECT_VERSION_NUMBER,
REQUEST_ID OLD_INC_REQUEST_ID,
REQUEST_ID INC_REQUEST_ID,
PROGRAM_APPLICATION_ID OLD_INC_PROGRAM_APPLICATION_ID,
PROGRAM_APPLICATION_ID INC_PROGRAM_APPLICATION_ID,
PROGRAM_ID OLD_INC_PROGRAM_ID,
PROGRAM_ID INC_PROGRAM_ID,
PROGRAM_UPDATE_DATE OLD_INC_PROGRAM_UPDATE_DATE,
PROGRAM_UPDATE_DATE INC_PROGRAM_UPDATE_DATE,
OWNING_DEPARTMENT_ID OLD_OWNING_DEPARTMENT_ID ,
OWNING_DEPARTMENT_ID OWNING_DEPARTMENT_ID,
INCIDENT_LOCATION_TYPE OLD_INCIDENT_LOCATION_TYPE,
INCIDENT_LOCATION_TYPE INCIDENT_LOCATION_TYPE,
UNASSIGNED_INDICATOR OLD_UNASSIGNED_INDICATOR,
UNASSIGNED_INDICATOR UNASSIGNED_INDICATOR,
MAINT_ORGANIZATION_ID OLD_MAINT_ORGANIZATION_ID,
MAINT_ORGANIZATION_ID MAINT_ORGANIZATION_ID,
--siahmed 12.2+ project auditing project attributes
project_id OLD_PROJECT_ID,
project_id PROJECT_ID,
project_task_id OLD_PROJECT_TASK_ID,
project_task_id PROJECT_TASK_ID,
expenditure_org_id OLD_exp_org_id,
expenditure_org_id exp_org_id,
--end of addition siahmed auditing attributes
--15995804 Audit price list header id and sla info.This API is not used anywhere,
--but need to changefor compilation purpose
PRICE_LIST_HEADER_ID OLD_PRICE_LIST_HEADER_ID,
PRICE_LIST_HEADER_ID PRICE_LIST_HEADER_ID,
'N' CHANGE_SLA_DATE_1_FLAG,
SLA_DATE_1 OLD_SLA_DATE_1,
SLA_DATE_1 SLA_DATE_1,
'N' CHANGE_SLA_DATE_2_FLAG,
SLA_DATE_2 OLD_SLA_DATE_2,
SLA_DATE_2 SLA_DATE_2,
'N' CHANGE_SLA_DATE_3_FLAG,
SLA_DATE_3 OLD_SLA_DATE_3,
SLA_DATE_3 SLA_DATE_3,
'N' CHANGE_SLA_DATE_4_FLAG,
SLA_DATE_4 OLD_SLA_DATE_4,
SLA_DATE_4 SLA_DATE_4,
'N' CHANGE_SLA_DATE_5_FLAG,
SLA_DATE_5 OLD_SLA_DATE_5,
SLA_DATE_5 SLA_DATE_5,
'N' CHANGE_SLA_DATE_6_FLAG,
SLA_DATE_6 OLD_SLA_DATE_6,
SLA_DATE_6 SLA_DATE_6,
'N' CHANGE_SLA_DURATION_1_FLAG,
SLA_DURATION_1 OLD_SLA_DURATION_1,
SLA_DURATION_1 SLA_DURATION_1,
'N' CHANGE_SLA_DURATION_2_FLAG,
SLA_DURATION_2 OLD_SLA_DURATION_2,
SLA_DURATION_2 SLA_DURATION_2
FROM cs_incidents_all_b
WHERE incident_id = p_request_id;
SELECT
NVL(start_date_active, SYSDATE-1)
, NVL(end_date_active , SYSDATE+1)
FROM
cs_party_roles_b
WHERE party_role_code = p_party_role_code;
SELECT
date_from
, date_to
INTO
l_dt_from
, l_dt_to
FROM
hr_operating_units
WHERE organization_id = p_org_id;
SELECT 1
FROM csi_i_parties
WHERE instance_id = p_customer_product_id
AND party_id = p_customer_id
AND party_source_table = 'HZ_PARTIES';
SELECT
responded_flag
, resolved_flag
INTO
l_new_responded_flag
, l_new_resolved_flag
FROM cs_incident_statuses_vl
WHERE incident_status_id=p_incident_status_id ;
SELECT
responded_flag
, resolved_flag
INTO
l_old_responded_flag
, l_old_resolved_flag
FROM cs_incident_statuses_vl
WHERE incident_status_id=p_old_incident_status_id ;
SELECT
party_type
, status
INTO
x_party_type
, x_status
FROM
hz_parties a
WHERE a.party_id = p_party_id;
PROCEDURE update_task_address
( p_incident_id IN NUMBER
, p_location_type IN VARCHAR2
, p_location_id IN NUMBER
, p_old_location_id IN NUMBER -- Bug 8947959
, x_return_status OUT NOCOPY VARCHAR2
) IS
--
CURSOR c_tasks IS
SELECT
task.task_id
, task.object_version_number
, DECODE(typ.rule, 'DISPATCH', 'Y', 'N') fs_task
, address_id -- Bug 8947959
, location_id -- Bug 8947959
FROM
jtf_tasks_b task
, jtf_task_statuses_b stat
, jtf_task_types_b typ
WHERE source_object_type_code = 'SR'
AND source_object_id = p_incident_id
AND stat.task_status_id = task.task_status_id
AND typ.task_type_id = task.task_type_id
AND NVL(stat.completed_flag, 'N') <> 'Y'
AND NVL(stat.closed_flag , 'N') <> 'Y'
AND NVL(stat.cancelled_flag, 'N') <> 'Y'
;
, 'CS_SERVICEREQUEST_UTIL.update_task_address'
);
CSF_TASKS_PUB.update_task
( p_api_version => 1.0
, p_task_id => task_rec.task_id
, p_object_version_number => task_rec.object_version_number
, p_location_id => l_location_id
, p_address_id => l_site_id
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
JTF_TASKS_PUB.update_task
( p_api_version => 1.0
, p_task_id => task_rec.task_id
, p_object_version_number => task_rec.object_version_number
, p_location_id => l_location_id
, p_address_id => l_site_id
, p_enable_workflow => FND_API.G_MISS_CHAR
, p_abort_workflow => FND_API.G_MISS_CHAR
, p_task_split_flag => FND_API.G_MISS_CHAR
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
END update_task_address;
PROCEDURE Verify_LocUpdate_For_FSTasks
(p_incident_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_task_id NUMBER ;
loc_update_not_alwd_exp EXCEPTION;
SELECT task_id,
task_type_id,
task_status_id,
planned_start_date,
planned_end_date,
scheduled_start_date,
scheduled_end_date,
actual_start_date,
actual_end_date,
planned_effort,
planned_effort_uom,
actual_effort,
actual_effort_uom
FROM jtf_tasks_vl
WHERE source_object_id = p_incident_id
AND source_object_type_code = 'SR' ;
SELECT assigned_flag,
completed_flag,
cancelled_flag,
rejected_flag,
schedulable_flag,
closed_flag,
start_date_type,
end_date_type
FROM jtf_task_statuses_vl
WHERE task_status_id = l_task_status_id ;
SELECT rule
FROM jtf_task_types_vl
WHERE task_type_id = l_task_type_id ;
SELECT resource_id
FROM jtf_task_assignments
WHERE task_id = l_task_id ;
RAISE loc_update_not_alwd_exp ;
WHEN loc_update_not_alwd_exp THEN
FND_MESSAGE.SET_Name('CS','CS_SR_LOC_UPD_NOT_ALLWD');
END Verify_LocUpdate_For_FSTasks;
SELECT revision_id
INTO l_platform_Version_id
FROM mtl_item_revisions
WHERE organization_id = p_organization_id
AND inventory_item_id = p_platform_id
AND revision_id = p_platform_Version_id ;
SELECT revision_qty_control_code
INTO l_revision_qty_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_platform_id
AND organization_id = p_organization_id ;
Select ifpai.instr_assignment_id instrument_payment_use_id
FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V ifpai,
Iby_fndcpt_all_pmt_channels_v ifac
Where ifpai.cust_account_id = l_bill_to_acct_id
AND ifpai.instr_assignment_id = l_instr_payment_use_id
And ifac.instrument_type = ifpai.instrument_type
And ifac.payment_channel_code = 'CREDIT_CARD'
And nvl(ifpai.card_single_use_flag, 'N') = 'N'
And masked_card_expirydate is not null
And ifpai.CARD_HOLDER_NAME is not null;
FND_MESSAGE.Set_Name('CS', 'CS_Create_UPDATE_CARD_ERROR');
SELECT pp.project_id, pt.task_id, HOU.organization_id
INTO l_project_id, l_project_task_id, l_exp_org_id
FROM pa_projects_expend_v pp,
pa_tasks_expend_v pt,
pa_organizations_expend_v hou
WHERE pp.project_id = pt.project_id
AND pp.project_id = p_project_id
AND pt.task_id = p_project_task_id
AND hou.organization_id =p_exp_org_id;
-- selected Operating Unit.Please enter valid combination
if (l_project_id IS NULL OR l_project_task_id IS NULL OR l_exp_org_id IS NULL) THEN
FND_MESSAGE.Set_Name('CS', 'CS_INC_CLEAR_PROJECT_INFO');
SELECT vl.LIST_HEADER_ID price_list_header_id
FROM qp_list_headers_vl vl,
qp_secu_list_headers_v slh
WHERE slh.list_header_id = vl.LIST_HEADER_ID
AND to_date(sysdate) between nvl(vl.start_date_active,to_date(sysdate)) and nvl(vl.end_date_active,to_date(sysdate))
and (slh.global_flag = 'Y' OR slh.orig_org_id = l_org_id)
AND vl.LIST_HEADER_ID = l_price_list_header_id;