[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE_COLLECTORS
This procedure takes the following primary parameters in the process
1. P_debug to enable or disable the log messages.
------------------------------------------------------------------------*/
PROCEDURE MERGE_COLLECTORS(P_DEBUG IN VARCHAR2);
PROCEDURE UPDATE_COLLECTORS
(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
P_RESP1 IN VARCHAR2,
P_RESP2 IN VARCHAR2,
P_RESP3 IN VARCHAR2,
P_RESP4 IN VARCHAR2,
P_RESP5 IN VARCHAR2,
P_debug IN VARCHAR2
)
IS
l_date_time VARCHAR2(25);
Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
fnd_file.put_line(FND_FILE.LOG,'Date and Time Before starting the update on ar_collectors '|| l_date_time );
/* kasreeni 07/14/2005 Missed a COMMIT, when no responsibility selected */
MERGE_COLLECTORS(P_DEBUG);
UPDATE_COLLECTORS_PVT(p_resp => P_RESP1,p_debug => p_debug);
UPDATE_COLLECTORS_PVT(p_resp => P_RESP2,p_debug => p_debug);
UPDATE_COLLECTORS_PVT(p_resp => P_RESP3,p_debug => p_debug);
UPDATE_COLLECTORS_PVT(p_resp => P_RESP4,p_debug => p_debug);
UPDATE_COLLECTORS_PVT(p_resp => P_RESP5,p_debug => p_debug);
fnd_file.put_line(FND_FILE.LOG,' Select atleast one responsibility in the parameters ');
Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
fnd_file.put_line(FND_FILE.LOG,'Date and Time Before starting the update on ar_collectors '|| l_date_time );
UPDATE ar_collectors ARC set
resource_type = 'RS_RESOURCE',
resource_id =
( SELECT max(jtfrs.resource_id)
FROM jtf_rs_resource_extns jtfrs
WHERE
jtfrs.source_id is NOT NULL
AND jtfrs.category = 'EMPLOYEE'
AND Trunc(start_date_active) <= Trunc(sysdate)
AND Nvl(Trunc(end_date_active),sysdate) >= Trunc(sysdate)
AND arc.employee_id = jtfrs.source_id)
WHERE employee_id is not null
AND resource_id is null;
fnd_file.put_line(FND_FILE.LOG, l_colcount || ' Record(s) updated in ar_collectors by merging Resource' ) ;
Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
fnd_file.put_line(FND_FILE.LOG,'Date and Time After finishing the update on ar_collectors'|| l_date_time );
fnd_file.put_line(FND_FILE.LOG, 'Error while update the ar_collectors' || sqlerrm);
PROCEDURE UPDATE_COLLECTORS_PVT
(
p_resp IN VARCHAR2,
P_debug IN VARCHAR2
)
IS
l_date_time VARCHAR2(20);
SELECT role_id from JTF_RS_ROLES_B WHERE ROLE_CODE = 'IEX_AGENT' and ROLE_TYPE_CODE = 'COLLECTIONS';
SELECT RESPONSIBILITY_ID FROM fnd_responsibility_vl fr
WHERE responsibility_name LIKE P_RESP;
CURSOR INSERT_RESOURCE IS
SELECT jtfrs.resource_id,SOURCE_ID,SOURCE_NAME,jtfrs.user_name
from fnd_user_resp_groups furg, fnd_responsibility_vl fr, jtf_rs_resource_extns jtfrs
WHERE fr.responsibility_id = furg.responsibility_id
AND jtfrs.user_id = furg.user_id
AND furg.start_date < sysdate and (furg.end_date is null or furg.end_date > sysdate)
AND jtfrs.source_id is not null and jtfrs.user_id is not null
AND jtfrs.category = 'EMPLOYEE'
-- AND fr.responsibility_name like P_RESP --Bug4930348.
AND fr.responsibility_id = l_responsibility_id --Bug4930348.
AND jtfrs.RESOURCE_ID NOT IN
(SELECT resource_id FROM
ar_collectors ac
WHERE employee_id is not null
AND ac.employee_id = jtfrs.source_id
AND ac.resource_id = jtfrs.resource_id
AND ac.resource_type = 'RS_RESOURCE' )
AND jtfrs.user_id is not null
ORDER BY 1;
SELECT to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
fnd_file.put_line(FND_FILE.LOG,'Date and Time After finishing the update '|| l_date_time );
FOR I IN INSERT_RESOURCE LOOP
If (P_debug = 'Y') then
Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
fnd_file.put_line(FND_FILE.LOG,'Date and Time Before starting the Insert '|| l_date_time );
SELECT AR_COLLECTORS_S.NEXTVAL
INTO L_COLLECTOR_ID
FROM DUAL;
fnd_file.put_line(FND_FILE.LOG,'Error while selecting the colector_id sequence ' || SQLERRM);
fnd_file.put_line(FND_FILE.LOG,'Before Starting to insert Resouce_id ' || i.resource_id || ' For Responsibility ' || p_resp);
INSERT INTO AR_COLLECTORS
(COLLECTOR_ID ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
NAME ,
EMPLOYEE_ID ,
DESCRIPTION ,
STATUS ,
RESOURCE_ID ,
RESOURCE_TYPE )
VALUES
(l_collector_id ,
FND_GLOBAL.user_id ,
sysdate ,
FND_GLOBAL.login_id ,
sysdate ,
FND_GLOBAL.user_id ,
i.user_name ,
i.source_id ,
i.source_name ,
'A',
i.resource_id,
'RS_RESOURCE' ) ;
fnd_file.put_line(FND_FILE.LOG, 'Error while Inserting into AR_COLLECTORS' || SQLERRM);
SELECT 1
INTO l_check
FROM
jtf_rs_resource_extns jtfrs,JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtv
WHERE
jtrr.role_resource_id = jtfrs.resource_id
AND jtv.role_id = jtrr.role_id
AND jtv.ROLE_CODE = 'IEX_AGENT'
AND jtrr.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
AND jtfrs.source_id is not null
AND jtfrs.resource_id = i.resource_id;
Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
fnd_file.put_line(FND_FILE.LOG,'Error in update_colectors_pvt for Responsibility ' || P_RESP);
END UPDATE_COLLECTORS_PVT;
PROCEDURE UPDATE_RESOURCES
(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
P_debug IN VARCHAR2
)
IS
l_return_status VARCHAR2(5);
CURSOR INSERT_RESOURCES IS
SELECT employee_id,inactive_date,description,name,resource_id FROM
ar_collectors ac
WHERE employee_id is not null
AND employee_id not in (SELECT jtr.source_id
FROM JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtv,
JTF_RS_RESOURCE_EXTNS jtr
WHERE jtv.ROLE_CODE = 'IEX_AGENT' AND jtv.role_id = jtrr.role_id
AND jtrr.role_resource_id = jtr.resource_id
AND jtrr.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
AND jtr.source_id is not null)
AND employee_id not in (SELECT jtr.source_id from
jtf_rs_resource_extns jtr where source_id is not null)
AND resource_id is not null
ORDER BY 1;
3.Update the resource in AR_COLLECTOR. */
--Bug4929658. Fix By LKKUMAR on 12-Jan-2005. Include source phone, job title, email, addresses. Start.
CURSOR INSERT_RESOURCES IS
SELECT ac.collector_id, ac.name, ac.employee_id, ac.inactive_date, ac.description, ac.resource_id,
fuser.user_id, fuser.user_name, pemp.employee_number,
pemp.full_name, pemp.first_name, pemp.last_name ,
--New Columns
hp.primary_phone_number,
hp.person_title,
hp.address1,
hp.address2,
hp.address3,
hp.address4,
hp.city,
hp.postal_code,
hp.state,
hp.county,
hp.country,
hp.email_address
FROM
ar_collectors ac, FND_USER fuser, per_all_people_f pemp ,hz_parties hp
WHERE ac.employee_id is not null
AND ac.employee_id not in (SELECT jtr.source_id from
jtf_rs_resource_extns jtr where source_id is not null)
AND ac.resource_id is null
AND ac.employee_id = fuser.employee_id
and ac.employee_id = pemp.person_id
and pemp.party_id = hp.party_id
ORDER BY 1;
SELECT employee_number,
full_name,
first_name,
middle_names,
last_name,
email_address,
business_group_id,
office_number,
internal_location,
mailstop
FROM per_all_people_f
WHERE person_id = p_person_id
ORDER BY effective_start_date DESC;
SELECT role_id from JTF_RS_ROLES_B WHERE ROLE_CODE = 'IEX_AGENT' and ROLE_TYPE_CODE = 'COLLECTIONS';
CURSOR update_resource IS
SELECT
DISTINCT
FU.USER_NAME,
FU.USER_ID,
FU.EMPLOYEE_ID,
JRS.RESOURCE_ID
FROM
FND_USER_RESP_GROUPS FURG,
FND_RESPONSIBILITY FR ,
FND_USER FU,
JTF_RS_RESOURCE_EXTNS JRS
WHERE
FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
AND JRS.SOURCE_ID = FU.EMPLOYEE_ID
AND FU.USER_ID = FURG.USER_ID
AND TRUNC(NVL(FU.START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(FU.END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
AND TRUNC(NVL(FURG.END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
AND TRUNC(NVL(JRS.START_DATE_ACTIVE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(JRS.END_DATE_ACTIVE,SYSDATE)) >= TRUNC(SYSDATE)
AND FU.EMPLOYEE_ID IS NOT NULL
AND JRS.USER_ID IS NULL
AND JRS.USER_NAME IS NULL
AND JRS.CATEGORY = 'EMPLOYEE'
AND FR.MENU_ID IN (
SELECT DISTINCT FCMF.MENU_ID
FROM
FND_COMPILED_MENU_FUNCTIONS FCMF, FND_FORM_FUNCTIONS FFF
WHERE FCMF.FUNCTION_ID = FFF.FUNCTION_ID
AND FFF.FUNCTION_NAME = 'IEXRCALL');
Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
OPEN update_resource;
FETCH update_resource BULK COLLECT INTO
L_USER_NAME,
L_USER_ID,
L_EMPLOYEE_ID,
L_RESOURCE_ID_LST
LIMIT g_bulk_fetch_rows;
UPDATE JTF_RS_RESOURCE_EXTNS
SET USER_ID = L_USER_ID(i),
USER_NAME = L_USER_NAME(i),
last_update_date = SYSDATE,
last_updated_by = -1
WHERE SOURCE_ID = L_EMPLOYEE_ID(I)
AND CATEGORY = 'EMPLOYEE'
-- Begin - Bug#5383877 - Andre Araujo - 07/18/2006 - With this statement there will be no updates
--AND L_USER_ID(I) NOT IN (SELECT NVL(USER_ID,-1) FROM JTF_RS_RESOURCE_EXTNS WHERE CATEGORY = 'EMPLOYEE');
-- End - Bug#5383877 - Andre Araujo - 07/18/2006 - With this statement there will be no updates
EXCEPTION WHEN OTHERS THEN
-- Begin - Bug#5383877 - Andre Araujo - 07/18/2006 - Since I am here correcting the exception
--NULL;
SELECT ROLE_ID INTO L_CHECK_ROLE
FROM JTF_RS_ROLE_RELATIONS_VL JRR,
JTF_RS_RESOURCE_EXTNS JRE
WHERE
JRE.SOURCE_ID = L_EMPLOYEE_ID(I)
AND JRR.ROLE_RESOURCE_ID = JRE.RESOURCE_ID
AND JRR.ROLE_CODE = 'IEX_AGENT';
CLOSE update_resource;
FOR I in insert_resources loop
BEGIN
--Bug4929658. Fix By LKKUMAR on 12-Jan-2005. Include source phone, job title, email, addresses. Start.
/*
JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_TRUE,
P_CATEGORY => 'EMPLOYEE',
P_SOURCE_ID => i.employee_id,
P_START_DATE_ACTIVE => SYSDATE,
P_END_DATE_ACTIVE => i.inactive_date,
P_COMMISSIONABLE_FLAG => 'Y',
P_HOLD_PAYMENT => 'N',
P_USER_ID => i.user_id,
P_USER_NAME => i.user_name,
P_RESOURCE_NAME => i.full_name,
P_SOURCE_NUMBER => i.employee_number,
P_SOURCE_NAME => i.full_name,
P_SOURCE_FIRST_NAME => i.first_name,
P_SOURCE_LAST_NAME => i.last_name,
P_TRANSACTION_NUMBER => NULL,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
X_RESOURCE_ID => l_resource_id,
X_RESOURCE_NUMBER => l_resource_number
);
UPDATE AR_COLLECTORS
SET resource_id = l_resource_id
WHERE collector_id = i.collector_id;
Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;