[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update Resource
This package validates the input parameters to these procedures and then
Calls corresponding procedures from jtf_rs_resource_pvt to do business
validations and to do actual inserts, updates and deletes into tables.
******************************************************************************************/
/* Package variables. */
G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_RESOURCE_PUB';
cursor c1 is select select_id , from_table , where_clause from jtf_objects_vl
where object_code in ( select object_code from jtf_object_usages
where object_user_code = 'RESOURCE_WORKFLOW' ) ;
SELECT resource_name from jtf_rs_resource_extns_tl
WHERE resource_id = l_resource_id
AND language = userenv ('LANG');
select_statement varchar2(2000) := null ;
select_statement := select_statement || ' union ' ;
select_statement := select_statement || ' select '||i.select_id||' from '||i.from_table
||' where '||i.where_clause ||' and rs.resource_id = :x_resource_id ';
EXECUTE IMMEDIATE select_statement INTO wf_rolename USING bind_table(1).bind_value;
EXECUTE IMMEDIATE select_statement INTO wf_rolename USING bind_table(1).bind_value,
bind_table(2).bind_value;
EXECUTE IMMEDIATE select_statement INTO wf_rolename USING bind_table(1).bind_value,
bind_table(2).bind_value,
bind_table(3).bind_value;
EXECUTE IMMEDIATE select_statement INTO wf_rolename USING bind_table(1).bind_value,
bind_table(2).bind_value,
bind_table(3).bind_value,
bind_table(4).bind_value;
EXECUTE IMMEDIATE select_statement INTO wf_rolename USING bind_table(1).bind_value,
bind_table(2).bind_value,
bind_table(3).bind_value,
bind_table(4).bind_value,
bind_table(5).bind_value;
SELECT 'Y'
FROM jtf_rs_partners_vl
WHERE party_id = l_party_id;
SELECT 'Y'
FROM HZ_PARTIES PARTY, HZ_PARTIES PARTY2,
HZ_PARTIES PARTY3, HZ_RELATIONSHIPS REL
WHERE (PARTY.PARTY_TYPE = 'ORGANIZATION' AND PARTY.PARTY_ID = REL.SUBJECT_ID)
AND REL.RELATIONSHIP_CODE IN ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER',
'CUSTOMER_INDIRECTLY_MANAGED_BY')
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.STATUS = 'A'
AND PARTY.STATUS = 'A'
AND PARTY2.STATUS = 'A'
AND PARTY3.STATUS = 'A'
AND REL.SUBJECT_ID = PARTY2.PARTY_ID
AND (PARTY2.PARTY_TYPE = 'PERSON' OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
AND REL.OBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
AND party.party_id = l_party_id
UNION ALL
SELECT 'Y'
FROM HZ_PARTIES PARTY, HZ_PARTIES PARTY2,
HZ_PARTIES PARTY3, HZ_RELATIONSHIPS REL
WHERE (PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP' AND PARTY.PARTY_ID = REL.PARTY_ID )
AND REL.RELATIONSHIP_CODE IN ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER',
'CUSTOMER_INDIRECTLY_MANAGED_BY')
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.STATUS = 'A'
AND PARTY.STATUS = 'A'
AND PARTY2.STATUS = 'A'
AND PARTY3.STATUS = 'A'
AND REL.SUBJECT_ID = PARTY2.PARTY_ID
AND (PARTY2.PARTY_TYPE = 'PERSON' OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
AND REL.OBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
AND party.party_id = l_party_id
;
SELECT 'Y',party_name
FROM jtf_rs_ptnr_migr_vl
WHERE party_id = l_party_id;
SELECT 'Y'
FROM hz_party_sites
WHERE party_id = l_party_id
AND party_site_id = l_party_site_id;
SELECT 'Y'
FROM jtf_rs_party_contacts_vl
WHERE party_id = l_party_id
AND nvl (party_site_id,-99) = nvl (l_party_site_id,-99)
AND contact_id = l_contact_id;
SELECT 'Y'
-- SELECT PARTY.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID , ORG_CONT.ORG_CONTACT_ID CONTACT_ID ,
-- ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER , PARTY.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
FROM HZ_PARTIES PARTY , HZ_RELATIONSHIPS PARTY_REL , HZ_ORG_CONTACTS ORG_CONT ,
HZ_ORG_CONTACT_ROLES CONT_ROLE
WHERE PARTY.STATUS = 'A'
AND PARTY.PARTY_TYPE = 'PERSON'
AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
AND PARTY.PARTY_ID = PARTY_REL.SUBJECT_ID
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
AND PARTY_REL.STATUS = 'A'
AND party.party_id = l_party_id
AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
UNION ALL
SELECT 'Y'
-- SELECT PARTY5.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID ,
-- ORG_CONT.ORG_CONTACT_ID CONTACT_ID , ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER ,
-- PARTY5.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
FROM HZ_PARTIES PARTY3 , HZ_PARTIES PARTY4 , HZ_PARTIES PARTY5 , HZ_RELATIONSHIPS PARTY_REL ,
HZ_ORG_CONTACTS ORG_CONT , HZ_ORG_CONTACT_ROLES CONT_ROLE
WHERE PARTY_REL.PARTY_ID = PARTY5.PARTY_ID
AND PARTY5.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND PARTY5.STATUS = 'A'
AND TRUNC (NVL (PARTY_REL.END_DATE, SYSDATE)) >= TRUNC (SYSDATE)
AND PARTY_REL.SUBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'PERSON'
AND PARTY3.STATUS = 'A'
AND PARTY_REL.OBJECT_ID = PARTY4.PARTY_ID
AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
AND PARTY4.STATUS = 'A'
AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
AND PARTY_REL.STATUS = 'A'
AND party5.party_id = l_party_id
AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
UNION ALL
SELECT 'Y'
-- SELECT PARTY4.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID ,
-- ORG_CONT.ORG_CONTACT_ID CONTACT_ID , ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER ,
-- PARTY3.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
FROM HZ_PARTIES PARTY3 , HZ_PARTIES PARTY4 , HZ_RELATIONSHIPS PARTY_REL ,
HZ_ORG_CONTACTS ORG_CONT , HZ_ORG_CONTACT_ROLES CONT_ROLE
WHERE PARTY_REL.SUBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'PERSON'
AND PARTY3.STATUS = 'A'
AND PARTY_REL.OBJECT_ID = PARTY4.PARTY_ID
AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
AND PARTY4.STATUS = 'A'
AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
AND TRUNC (PARTY_REL.START_DATE) <= TRUNC (SYSDATE)
AND TRUNC (NVL (PARTY_REL.END_DATE, SYSDATE)) >= TRUNC (SYSDATE)
AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
AND PARTY_REL.STATUS = 'A'
AND party4.party_id = l_party_id
AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
;
SELECT 'Y'
FROM hz_party_sites
WHERE party_site_id = l_party_site_id;
SELECT 'Y'
/* FROM jtf_rs_party_contacts_vl
WHERE party_id = l_party_id
AND nvl(party_site_id, 0) = nvl(l_party_site_id, 0)
AND contact_id = l_contact_id; */
SELECT u.user_id
FROM fnd_user u,
jtf_rs_resource_extns r
WHERE u.user_id = r.user_id
AND u.user_id = l_assigned_to_group_id;
SELECT 'Y'
FROM jtf_rs_resource_extns
WHERE user_id = l_user_id;
SELECT 'x' value,full_name
FROM per_all_people_f
WHERE person_id = p_person_id;
P_SELECT_ID => l_source_id,
P_OBJECT_USER_CODE => 'RESOURCE_CATEGORIES',
P_RS_ID_PUB_FLAG => G_RS_ID_PUB_FLAG,
X_FOUND => l_found,
X_RETURN_STATUS => x_return_status
);
/* Procedure to update the resource based on input values
passed by calling routines . */
PROCEDURE update_resource
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
-- P_LOCATION IN MDSYS.SDO_GEOMETRY,
P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
SELECT resource_id,category
FROM jtf_rs_resource_extns
WHERE resource_id = l_resource_id;
SELECT resource_id,category
FROM jtf_rs_resource_extns
WHERE resource_number = l_resource_number;
SAVEPOINT update_resource_pub;
jtf_rs_resource_pvt.update_resource
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => l_resource_id,
P_MANAGING_EMP_ID => l_managing_emp_id,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_TIME_ZONE => l_time_zone,
P_COST_PER_HR => l_cost_per_hr,
P_PRIMARY_LANGUAGE => l_primary_language,
P_SECONDARY_LANGUAGE => l_secondary_language,
P_SUPPORT_SITE_ID => l_support_site_id,
P_IES_AGENT_LOGIN => l_ies_agent_login,
P_SERVER_GROUP_ID => l_server_group_id,
P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
P_COST_CENTER => l_cost_center,
P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
P_COMP_CURRENCY_CODE => l_comp_currency_code,
P_COMMISSIONABLE_FLAG => l_commissionable_flag,
P_HOLD_REASON_CODE => l_hold_reason_code,
P_HOLD_PAYMENT => l_hold_payment,
P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
P_USER_ID => l_user_id,
--P_LOCATION => l_location,
P_OBJECT_VERSION_NUM => l_object_version_num,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data,
--added this so that the overloaded procedure for update resource is called
-- otherwise all source coulmns were being set to null
P_SOURCE_NAME => fnd_api.g_miss_char
);
ROLLBACK TO update_resource_pub;
ROLLBACK TO update_resource_pub;
ROLLBACK TO update_resource_pub;
END update_resource;
| existing UPDATE_RESOURSE parameter list.
| Complete business logic moved from old procedure to this new procedure.
*/
/* Procedure to update the resource with new columns based on input values
passed by calling routines along with FS Setup Complete Flag. */
PROCEDURE update_resource
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
-- P_LOCATION IN MDSYS.SDO_GEOMETRY,
P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE,
P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE,
P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE,
P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE,
P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE,
P_SOURCE_ORG_ID IN NUMBER,
P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE,
P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE,
P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE,
P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE,
P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE,
P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE,
P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE,
P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE,
P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE,
P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE,
P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE,
P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%TYPE,
P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%TYPE,
P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%TYPE,
P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%TYPE,
P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE,
P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE,
P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE,
P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE,
P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE,
P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE,
P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE,
P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE,
P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE,
P_USER_NAME IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE,
P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE,
P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE,
P_FS_SETUP_COMPLETE IN JTF_RS_RESOURCE_EXTNS.FS_SETUP_COMPLETE%TYPE
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
SELECT resource_id,category
FROM jtf_rs_resource_extns
WHERE resource_id = l_resource_id;
SELECT resource_id,category
FROM jtf_rs_resource_extns
WHERE resource_number = l_resource_number;
SAVEPOINT update_resource_pub;
jtf_rs_resource_pvt.update_resource
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => l_resource_id,
P_MANAGING_EMP_ID => l_managing_emp_id,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_TIME_ZONE => l_time_zone,
P_COST_PER_HR => l_cost_per_hr,
P_PRIMARY_LANGUAGE => l_primary_language,
P_SECONDARY_LANGUAGE => l_secondary_language,
P_SUPPORT_SITE_ID => l_support_site_id,
P_IES_AGENT_LOGIN => l_ies_agent_login,
P_SERVER_GROUP_ID => l_server_group_id,
P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
P_COST_CENTER => l_cost_center,
P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
P_COMP_CURRENCY_CODE => l_comp_currency_code,
P_COMMISSIONABLE_FLAG => l_commissionable_flag,
P_HOLD_REASON_CODE => l_hold_reason_code,
P_HOLD_PAYMENT => l_hold_payment,
P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
P_USER_ID => l_user_id,
--P_LOCATION => l_location,
P_ATTRIBUTE1 => p_attribute1,
P_ATTRIBUTE2 => p_attribute2,
P_ATTRIBUTE3 => p_attribute3,
P_ATTRIBUTE4 => p_attribute4,
P_ATTRIBUTE5 => p_attribute5,
P_ATTRIBUTE6 => p_attribute6,
P_ATTRIBUTE7 => p_attribute7,
P_ATTRIBUTE8 => p_attribute8,
P_ATTRIBUTE9 => p_attribute9,
P_ATTRIBUTE10 => p_attribute10,
P_ATTRIBUTE11 => p_attribute11,
P_ATTRIBUTE12 => p_attribute12,
P_ATTRIBUTE13 => p_attribute13,
P_ATTRIBUTE14 => p_attribute14,
P_ATTRIBUTE15 => p_attribute15,
P_ATTRIBUTE_CATEGORY => p_attribute_category,
P_OBJECT_VERSION_NUM => l_object_version_num,
P_RESOURCE_NAME => P_RESOURCE_NAME,
P_SOURCE_NAME => P_SOURCE_NAME,
P_SOURCE_NUMBER => P_SOURCE_NUMBER,
P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
P_SOURCE_EMAIL => P_SOURCE_EMAIL,
P_SOURCE_PHONE => P_SOURCE_PHONE,
P_SOURCE_ORG_ID => P_SOURCE_ORG_ID,
P_SOURCE_ORG_NAME => P_SOURCE_ORG_NAME,
P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
P_SOURCE_ADDRESS4 => P_SOURCE_ADDRESS4,
P_SOURCE_CITY => P_SOURCE_CITY,
P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
P_SOURCE_STATE => P_SOURCE_STATE,
P_SOURCE_PROVINCE => P_SOURCE_PROVINCE,
P_SOURCE_COUNTY => P_SOURCE_COUNTY,
P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME,
P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
P_SOURCE_CATEGORY => P_SOURCE_CATEGORY,
P_SOURCE_STATUS => P_SOURCE_STATUS,
P_SOURCE_OFFICE => P_SOURCE_OFFICE,
P_SOURCE_LOCATION => P_SOURCE_LOCATION,
P_SOURCE_MAILSTOP => P_SOURCE_MAILSTOP,
P_ADDRESS_ID => P_ADDRESS_ID,
P_USER_NAME => P_USER_NAME,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data,
P_SOURCE_MOBILE_PHONE => P_SOURCE_MOBILE_PHONE,
P_SOURCE_PAGER => P_SOURCE_PAGER,
P_FS_SETUP_COMPLETE => l_fs_setup_complete
);
ROLLBACK TO update_resource_pub;
ROLLBACK TO update_resource_pub;
ROLLBACK TO update_resource_pub;
END update_resource;
| and calling new update_resource procedure with extra parameter.
*/
/* Procedure to update the resource with new columns based on input values
passed by calling routines. */
PROCEDURE update_resource
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
P_RESOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
-- P_LOCATION IN MDSYS.SDO_GEOMETRY,
P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE,
P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE,
P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE,
P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE,
P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE,
P_SOURCE_ORG_ID IN NUMBER,
P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE,
P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE,
P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE,
P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE,
P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE,
P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE,
P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE,
P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE,
P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE,
P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE,
P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE,
P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%TYPE,
P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%TYPE,
P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%TYPE,
P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%TYPE,
P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE,
P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE,
P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE,
P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE,
P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE,
P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE,
P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE,
P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE,
P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE,
P_USER_NAME IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE,
P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE,
P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE
) IS
BEGIN
update_resource
(P_API_VERSION => P_API_VERSION,
P_INIT_MSG_LIST => P_INIT_MSG_LIST,
P_COMMIT => P_COMMIT,
P_RESOURCE_ID => P_RESOURCE_ID,
P_RESOURCE_NUMBER => P_RESOURCE_NUMBER,
P_MANAGING_EMP_ID => P_MANAGING_EMP_ID,
P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
P_TIME_ZONE => P_TIME_ZONE,
P_COST_PER_HR => P_COST_PER_HR,
P_PRIMARY_LANGUAGE => P_PRIMARY_LANGUAGE,
P_SECONDARY_LANGUAGE => P_SECONDARY_LANGUAGE,
P_SUPPORT_SITE_ID => P_SUPPORT_SITE_ID,
P_IES_AGENT_LOGIN => P_IES_AGENT_LOGIN,
P_SERVER_GROUP_ID => P_SERVER_GROUP_ID,
P_ASSIGNED_TO_GROUP_ID => P_ASSIGNED_TO_GROUP_ID,
P_COST_CENTER => P_COST_CENTER,
P_CHARGE_TO_COST_CENTER => P_CHARGE_TO_COST_CENTER,
P_COMP_CURRENCY_CODE => P_COMP_CURRENCY_CODE,
P_COMMISSIONABLE_FLAG => P_COMMISSIONABLE_FLAG,
P_HOLD_REASON_CODE => P_HOLD_REASON_CODE,
P_HOLD_PAYMENT => P_HOLD_PAYMENT,
P_COMP_SERVICE_TEAM_ID => P_COMP_SERVICE_TEAM_ID,
P_USER_ID => P_USER_ID,
P_RESOURCE_NAME => P_RESOURCE_NAME,
P_SOURCE_NAME => P_SOURCE_NAME,
P_SOURCE_NUMBER => P_SOURCE_NUMBER,
P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
P_SOURCE_EMAIL => P_SOURCE_EMAIL,
P_SOURCE_PHONE => P_SOURCE_PHONE,
P_SOURCE_ORG_ID => P_SOURCE_ORG_ID,
P_SOURCE_ORG_NAME => P_SOURCE_ORG_NAME,
P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
P_SOURCE_ADDRESS4 => P_SOURCE_ADDRESS4,
P_SOURCE_CITY => P_SOURCE_CITY,
P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
P_SOURCE_STATE => P_SOURCE_STATE,
P_SOURCE_PROVINCE => P_SOURCE_PROVINCE,
P_SOURCE_COUNTY => P_SOURCE_COUNTY,
P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME,
P_SOURCE_CATEGORY => P_SOURCE_CATEGORY,
P_SOURCE_STATUS => P_SOURCE_STATUS,
P_SOURCE_OFFICE => P_SOURCE_OFFICE,
P_SOURCE_LOCATION => P_SOURCE_LOCATION,
P_SOURCE_MAILSTOP => P_SOURCE_MAILSTOP,
P_ADDRESS_ID => P_ADDRESS_ID,
P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
P_USER_NAME => P_USER_NAME,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA,
P_SOURCE_MOBILE_PHONE => P_SOURCE_MOBILE_PHONE,
P_SOURCE_PAGER => P_SOURCE_PAGER,
P_ATTRIBUTE1 => P_ATTRIBUTE1,
P_ATTRIBUTE2 => P_ATTRIBUTE2,
P_ATTRIBUTE3 => P_ATTRIBUTE3,
P_ATTRIBUTE4 => P_ATTRIBUTE4,
P_ATTRIBUTE5 => P_ATTRIBUTE5,
P_ATTRIBUTE6 => P_ATTRIBUTE6,
P_ATTRIBUTE7 => P_ATTRIBUTE7,
P_ATTRIBUTE8 => P_ATTRIBUTE8,
P_ATTRIBUTE9 => P_ATTRIBUTE9,
P_ATTRIBUTE10 => P_ATTRIBUTE10,
P_ATTRIBUTE11 => P_ATTRIBUTE11,
P_ATTRIBUTE12 => P_ATTRIBUTE12,
P_ATTRIBUTE13 => P_ATTRIBUTE13,
P_ATTRIBUTE14 => P_ATTRIBUTE14,
P_ATTRIBUTE15 => P_ATTRIBUTE15,
P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
P_FS_SETUP_COMPLETE => FND_API.G_MISS_CHAR
) ;
END update_resource;
/* Procedure to delete the resource of type TBH */
PROCEDURE delete_resource
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE';
SELECT resource_id,
category
FROM jtf_rs_resource_extns
WHERE resource_id = l_resource_id;
SAVEPOINT delete_resource_pub;
JTF_RS_RESOURCE_PVT.DELETE_RESOURCE(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => l_resource_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
ROLLBACK TO delete_resource_pub;
ROLLBACK TO delete_resource_pub;
ROLLBACK TO delete_resource_pub;
END delete_resource;
SELECT 'Y'
FROM jtf_rs_partners_vl
WHERE party_id = l_party_id;
SELECT 'Y'
FROM HZ_PARTIES PARTY, HZ_PARTIES PARTY2,
HZ_PARTIES PARTY3, HZ_RELATIONSHIPS REL
WHERE (PARTY.PARTY_TYPE = 'ORGANIZATION' AND PARTY.PARTY_ID = REL.SUBJECT_ID)
AND REL.RELATIONSHIP_CODE IN ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER',
'CUSTOMER_INDIRECTLY_MANAGED_BY')
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.STATUS = 'A'
AND PARTY.STATUS = 'A'
AND PARTY2.STATUS = 'A'
AND PARTY3.STATUS = 'A'
AND REL.SUBJECT_ID = PARTY2.PARTY_ID
AND (PARTY2.PARTY_TYPE = 'PERSON' OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
AND REL.OBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
AND party.party_id = l_party_id
UNION ALL
SELECT 'Y'
FROM HZ_PARTIES PARTY, HZ_PARTIES PARTY2,
HZ_PARTIES PARTY3, HZ_RELATIONSHIPS REL
WHERE (PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP' AND PARTY.PARTY_ID = REL.PARTY_ID )
AND REL.RELATIONSHIP_CODE IN ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER',
'CUSTOMER_INDIRECTLY_MANAGED_BY')
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.STATUS = 'A'
AND PARTY.STATUS = 'A'
AND PARTY2.STATUS = 'A'
AND PARTY3.STATUS = 'A'
AND REL.SUBJECT_ID = PARTY2.PARTY_ID
AND (PARTY2.PARTY_TYPE = 'PERSON' OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
AND REL.OBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
AND party.party_id = l_party_id
;
SELECT 'Y'
FROM hz_party_sites
WHERE party_id = l_party_id
AND party_site_id = l_party_site_id;
SELECT 'Y'
FROM jtf_rs_party_contacts_vl
WHERE party_id = l_party_id
AND nvl (party_site_id,-99) = nvl (l_party_site_id,-99)
AND contact_id = l_contact_id;
SELECT 'Y'
-- SELECT PARTY.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID , ORG_CONT.ORG_CONTACT_ID CONTACT_ID ,
-- ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER , PARTY.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
FROM HZ_PARTIES PARTY , HZ_RELATIONSHIPS PARTY_REL , HZ_ORG_CONTACTS ORG_CONT ,
HZ_ORG_CONTACT_ROLES CONT_ROLE
WHERE PARTY.STATUS = 'A'
AND PARTY.PARTY_TYPE = 'PERSON'
AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
AND PARTY.PARTY_ID = PARTY_REL.SUBJECT_ID
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
AND PARTY_REL.STATUS = 'A'
AND party.party_id = l_party_id
AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
UNION ALL
SELECT 'Y'
-- SELECT PARTY5.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID ,
-- ORG_CONT.ORG_CONTACT_ID CONTACT_ID , ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER ,
-- PARTY5.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
FROM HZ_PARTIES PARTY3 , HZ_PARTIES PARTY4 , HZ_PARTIES PARTY5 , HZ_RELATIONSHIPS PARTY_REL ,
HZ_ORG_CONTACTS ORG_CONT , HZ_ORG_CONTACT_ROLES CONT_ROLE
WHERE PARTY_REL.PARTY_ID = PARTY5.PARTY_ID
AND PARTY5.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND PARTY5.STATUS = 'A'
AND TRUNC (NVL (PARTY_REL.END_DATE, SYSDATE)) >= TRUNC (SYSDATE)
AND PARTY_REL.SUBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'PERSON'
AND PARTY3.STATUS = 'A'
AND PARTY_REL.OBJECT_ID = PARTY4.PARTY_ID
AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
AND PARTY4.STATUS = 'A'
AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
AND PARTY_REL.STATUS = 'A'
AND party5.party_id = l_party_id
AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
UNION ALL
SELECT 'Y'
-- SELECT PARTY4.PARTY_ID PARTY_ID , ORG_CONT.PARTY_SITE_ID PARTY_SITE_ID ,
-- ORG_CONT.ORG_CONTACT_ID CONTACT_ID , ORG_CONT.CONTACT_NUMBER CONTACT_NUMBER ,
-- PARTY3.PARTY_NAME CONTACT_NAME , CONT_ROLE.PRIMARY_FLAG PRIMARY_FLAG
FROM HZ_PARTIES PARTY3 , HZ_PARTIES PARTY4 , HZ_RELATIONSHIPS PARTY_REL ,
HZ_ORG_CONTACTS ORG_CONT , HZ_ORG_CONTACT_ROLES CONT_ROLE
WHERE PARTY_REL.SUBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'PERSON'
AND PARTY3.STATUS = 'A'
AND PARTY_REL.OBJECT_ID = PARTY4.PARTY_ID
AND PARTY4.PARTY_TYPE = 'ORGANIZATION'
AND PARTY4.STATUS = 'A'
AND PARTY_REL.RELATIONSHIP_ID = ORG_CONT.PARTY_RELATIONSHIP_ID
AND TRUNC (PARTY_REL.START_DATE) <= TRUNC (SYSDATE)
AND TRUNC (NVL (PARTY_REL.END_DATE, SYSDATE)) >= TRUNC (SYSDATE)
AND ORG_CONT.ORG_CONTACT_ID = CONT_ROLE.ORG_CONTACT_ID (+)
AND CONT_ROLE.PRIMARY_FLAG (+) = 'Y'
AND PARTY_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PARTY_REL.DIRECTIONAL_FLAG = 'F'
AND PARTY_REL.STATUS = 'A'
AND party4.party_id = l_party_id
AND ORG_CONT.ORG_CONTACT_ID = l_contact_id
AND NVL(ORG_CONT.PARTY_SITE_ID,-99) = NVL(l_party_site_id,-99)
;
SELECT 'Y'
FROM hz_party_sites
WHERE party_site_id = l_party_site_id;
SELECT 'Y'
/* FROM jtf_rs_party_contacts_vl
WHERE party_id = l_party_id
AND nvl(party_site_id, 0) = nvl(l_party_site_id, 0)
AND contact_id = l_contact_id; */
SELECT u.user_id
FROM fnd_user u,
jtf_rs_resource_extns r
WHERE u.user_id = r.user_id
AND u.user_id = l_assigned_to_group_id;
SELECT 'Y'
FROM jtf_rs_resource_extns
WHERE user_id = l_user_id;
SELECT 'x' value,full_name
FROM per_all_people_f
WHERE person_id = p_person_id;
P_SELECT_ID => l_source_id,
P_OBJECT_USER_CODE => 'RESOURCE_CATEGORIES',
P_RS_ID_PUB_FLAG => G_RS_ID_PUB_FLAG,
X_FOUND => l_found,
X_RETURN_STATUS => x_return_status
);