[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_TX_TYPE_UPDATE CONSTANT VARCHAR2(6) := 'UPDATE';
select 'S'
into x_verify_sites_data
from RRS_SITES_INTERFACE
WHERE batch_id = p_batch_id
and Process_status = G_PS_TO_BE_PROCESSED
and rownum < 2;
* This Update is for defaulting the Address1 in case of Site
* creation/updation using no value for address1 field. We are populating
* the Site Name in Address1 field.
* ************************************************************/
Update RRS_SITES_INTERFACE
Set Address1 = Site_name
where batch_id = p_batch_id
and process_status = G_PS_TO_BE_PROCESSED
and Address1 is NULL
and country is NOT NULL
and rowid in (select rowid
from RRS_SITES_INTERFACE
where batch_id = p_batch_id
and process_status = G_PS_TO_BE_PROCESSED
and Address1 is NULL);
Validate_update_rows( p_batch_id => p_batch_id
,p_purge_rows => p_purge_rows
,x_return_flag => x_return_flag
);
select 'Y'
into l_site_exist
from RRS_SITE_UA_INTF A
where a.batch_id = p_batch_id
and (A.SITE_ID is NOT NULL )
and (A.Process_status = G_PS_TO_BE_PROCESSED )
and rownum < 2;
select 'Y'
into l_loc_exist
from RRS_LOCATION_UA_INTF A
where a.batch_id = p_batch_id
and (A.LOCATION_ID is NOT NULL )
and (A.Process_status = G_PS_TO_BE_PROCESSED )
and rownum < 2;
select 'Y'
into l_ta_exist
from RRS_TRADEAREA_UA_INTF A
where a.batch_id = p_batch_id
and (A.TRADE_AREA_ID is NOT NULL )
and (A.Process_status = G_PS_TO_BE_PROCESSED )
and rownum < 2;
SELECT RRS_SITE_INTF_SETS_S.NEXTVAL
INTO l_data_set_id
FROM DUAL;
UPDATE RRS_SITE_UA_INTF
SET DATA_SET_ID = l_data_set_id,
Transaction_id = l_data_set_id,
ATTR_GROUP_TYPE = 'RRS_SITEMGMT_GROUP',
DATA_LEVEL_ID = 71802,
DATA_LEVEL_NAME = 'SITE_LEVEL'
where batch_id = p_batch_id
and SITE_ID is NOT NULL
and Process_status = G_PS_TO_BE_PROCESSED;
UPDATE RRS_LOCATION_UA_INTF
SET DATA_SET_ID = l_data_set_id,
Transaction_id = l_data_set_id,
ATTR_GROUP_TYPE = 'RRS_LOCATION_GROUP',
DATA_LEVEL_ID = 71801,
DATA_LEVEL_NAME = 'LOCATION_LEVEL'
where batch_id = p_batch_id
and LOCATION_ID is NOT NULL
and Process_status = G_PS_TO_BE_PROCESSED;
UPDATE RRS_TRADEAREA_UA_INTF
SET DATA_SET_ID = l_data_set_id,
Transaction_id = l_data_set_id,
ATTR_GROUP_TYPE = 'RRS_TRADE_AREA_GROUP',
DATA_LEVEL_ID = 71803,
DATA_LEVEL_NAME = 'TRADE_AREA_LEVEL'
where batch_id = p_batch_id
and trade_area_id is not null
and Process_status = G_PS_TO_BE_PROCESSED;
SELECT SITE_IDENTIFICATION_NUMBER , count(*)
BULK COLLECT
INTO l_site_counts
FROM RRS_SITES_INTERFACE
WHERE BATCH_ID = p_batch_id
AND TRANSACTION_TYPE = G_TX_TYPE_CREATE
AND PROCESS_STATUS = G_PS_TO_BE_PROCESSED
AND SITE_IDENTIFICATION_NUMBER IS NOT NULL
GROUP BY SITE_IDENTIFICATION_NUMBER
HAVING count(*) > 1;
Delete from RRS_INTERFACE_ERRORS
Where batch_id = p_batch_id
and Process_status = '3';
* This Update is for defaulting the Address1 in case of Site
* creation/updation using no value for address1 field. We are populating
* the Site Name in Address1 field.
* ************************************************************/
/*
* Moved this SQL just before calling Validat_new_rows and Validate_update_rows.
*
*
Update RRS_SITES_INTERFACE
Set Address1 = Site_name
where batch_id = p_batch_id
and process_status = G_PS_TO_BE_PROCESSED
and Address1 is NULL
and country is NOT NULL
and rowid in (select rowid
from RRS_SITES_INTERFACE
where batch_id = p_batch_id
and process_status = G_PS_TO_BE_PROCESSED
and Address1 is NULL);
select count(distinct(site_status_code))
into l_count
from rrs_sites_interface
where batch_id = p_batch_id
and process_status = G_PS_TO_BE_PROCESSED;
Select site_identification_number,
ROWID
from RRS_SITES_INTERFACE
where batch_id = p_batch_id
and process_status = G_PS_IN_PROCESS
and transaction_type = G_TX_TYPE_CREATE
and site_identification_number is NULL;
select
SITE_ID,
SITE_IDENTIFICATION_NUMBER,
SITE_NAME,
SITE_TYPE_CODE,
SITE_STATUS_CODE,
SITE_USE_TYPE_CODE,
BRANDNAME_CODE,
CALENDAR_CODE,
LOCATION_STATUS,
LOCATION_ID,
SITE_PARTY_ID,
PARTY_SITE_ID,
LE_PARTY_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
ADDRESS_LINES_PHONETIC,
CITY,
POSTAL_CODE,
STATE,
PROVINCE,
COUNTY,
COUNTRY,
GEOMETRY_SOURCE,
LONGITUDE,
LATITUDE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
TRANSACTION_TYPE,
BATCH_PROCESSING,
BATCH_ID
FROM RRS_SITES_INTERFACE
WHERE TRANSACTION_TYPE = G_TX_TYPE_CREATE
and BATCH_ID = l_batch_id
and Process_status = G_PS_IN_PROCESS;
UPDATE RRS_SITES_INTERFACE
SET PROCESS_STATUS = G_PS_IN_PROCESS,
REQUEST_ID = G_REQUEST_ID,
PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID,
PROGRAM_ID = G_PROGAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = G_USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = G_LOGIN_ID
WHERE BATCH_ID = p_batch_id
AND PROCESS_STATUS = G_PS_TO_BE_PROCESSED
AND TRANSACTION_TYPE = G_TX_TYPE_CREATE;
Update RRS_SITES_INTERFACE
Set Process_status = '3'
Where Batch_id = p_batch_id
and process_status = G_PS_IN_PROCESS
and transaction_type = G_TX_TYPE_CREATE;
SELECT RRS_DEFAULT_SITE_NUMBER_S.NEXTVAL
INTO l_site_id_num
FROM DUAL;
Select site_identification_number
into l_site_id_num_exist
from RRS_SITES_INTERFACE
where site_identification_number = to_char(l_site_id_num)
and batch_id = p_batch_id
and process_status = G_PS_IN_PROCESS
and transaction_type = G_TX_TYPE_CREATE;
Update RRS_SITES_INTERFACE
Set site_identification_number = l_site_id_num
where site_identification_number is NULL
and batch_id = p_batch_id
and process_status = G_PS_IN_PROCESS
and transaction_type = G_TX_TYPE_CREATE
and ROWID = site_id_num.ROWID;
select geometry_source
into l_db_geo_source
from hz_locations
where location_id = site_data.location_id;
UPDATE HZ_LOCATIONS
SET GEOMETRY_SOURCE = l_geo_source
WHERE LOCATION_ID = site_data.location_id;
RRS_SITE_UTILS.Update_geometry_for_locations ( p_loc_id => nvl(site_data.location_id , x_location_id)
,p_lat => site_data.latitude
,p_long => site_data.longitude
,p_status => 'GOOD'
,p_geo_source => nvl(site_data.geometry_source , l_geo_source )
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
we will update the process_status of this row to be Validation_succeeded
( 4 )
*********************************************/
If l_row_status = 'S' then
-- dbms_output.put_line (' Create Loaction Flag is : '||l_create_location);
update rrs_sites_interface
set location_id = x_location_id,
site_party_id = x_party_id ,
party_site_id = x_party_site_id
where site_identification_number = site_data.site_identification_number
and batch_id = p_batch_id
and Transaction_type = G_TX_TYPE_CREATE
and process_status = G_PS_IN_PROCESS
and location_status = 'N';
update rrs_sites_interface
set site_party_id = x_party_id ,
party_site_id = x_party_site_id
where site_identification_number = site_data.site_identification_number
and process_status = G_PS_IN_PROCESS
and batch_id = p_batch_id;
update rrs_sites_interface
set location_id = x_location_id
where site_identification_number = site_data.site_identification_number
and batch_id = p_batch_id
and process_status = G_PS_IN_PROCESS
and Transaction_type = G_TX_TYPE_CREATE;
Update RRS_SITES_INTERFACE
Set Process_status = G_PS_SUCCESS
Where Batch_id = p_batch_id
and process_status = G_PS_IN_PROCESS
and site_identification_number = site_data.site_identification_number
and transaction_type = G_TX_TYPE_CREATE;
Update RRS_SITES_INTERFACE
Set Process_status = '3'
Where Batch_id = p_batch_id
and process_status = G_PS_IN_PROCESS
and site_identification_number = site_data.site_identification_number
and transaction_type = G_TX_TYPE_CREATE;
* case. Update all the rows with process_status = '2' to '1' after
* completion of the processing logic. It should always return 0 rows.
* *********************/
Update RRS_SITES_INTERFACE
Set Process_status = G_PS_TO_BE_PROCESSED
Where Batch_id = p_batch_id
and process_status = G_PS_IN_PROCESS
and transaction_type in ( G_TX_TYPE_CREATE );
select 'E'
into x_return_flag
from rrs_sites_b
where site_identification_number = p_site_id_num;
elsIf p_site_id_num is NOT NULL and p_transaction_type = G_TX_TYPE_UPDATE then
Begin
select 'S'
into x_return_flag
from rrs_sites_b
where site_identification_number = p_site_id_num;
select 'S'
into x_return_flag
from rrs_sites_b
where site_id = p_site_id;
select LOOKUP_CODE
into l_lookup_code
from rrs_lookups_v
where LOOKUP_TYPE = 'RRS_SITE_TYPE'
and LOOKUP_CODE = p_site_type_code;
select LOOKUP_CODE
into l_lookup_code
from rrs_lookups_v
where LOOKUP_TYPE = 'RRS_SITE_STATUS'
and LOOKUP_CODE = p_site_status_code;
select Lookup_code
into l_lookup_code
from rrs_lookups_v
where lookup_type = 'RRS_BRAND_NAME'
and lookup_code = p_site_brand_code;
Select LOOKUP_CODE
into l_site_use_type_code
From Fnd_Lookup_Values
Where Lookup_Type In ('PARTY_SITE_USE_CODE' )
AND View_Application_Id In ( 222 )
AND Language = userenv('Lang')
And Security_Group_Id = 0
AND LOOKUP_CODE = p_site_use_type_code;
select calendar_code
into l_calendar_code
from BOM_CALENDARS
where calendar_code = p_site_calendar_code;
select location_id
into l_location_id
from hz_locations
where location_id = p_location_id;
select address1
/*
,address2
,address3
,address4
,address_lines_phonetic
,city
,postal_code
,state
,province
,county
*/
Into l_address1
From HZ_locations
where address1 = p_address1
and country = p_country_code;
SELECT HP.PARTY_ID
into l_site_party_id
FROM HZ_PARTIES HP, HZ_ORGANIZATION_PROFILES HOP
WHERE HP.PARTY_ID = HOP.PARTY_ID
AND HP.PARTY_ID = p_site_party_id
AND NVL(HOP.INTERNAL_FLAG,'N') = 'N'
AND HP.PARTY_TYPE = 'ORGANIZATION'
AND SYSDATE BETWEEN NVL(TRUNC(EFFECTIVE_START_DATE),TRUNC(SYSDATE)) AND NVL(EFFECTIVE_END_DATE,TRUNC(SYSDATE)+1);
SELECT xep.legal_entity_id
into l_le_party_id
FROM xle_entity_profiles xep,
xle_registrations xr,
xle_jurisdictions_vl jur,
hr_locations hl,
hz_geographies b,
hz_parties hp,
xle_lookups l,
xle_lookups l1
WHERE l.lookup_type = 'XLE_YES_NO'
AND l.lookup_code = xep.transacting_entity_flag
AND xep.geography_id = b.geography_id
AND xr.location_id = hl.location_id
AND xr.source_id = xep.legal_entity_id
AND xr.identifying_flag='Y'
AND xr.source_table = 'XLE_ENTITY_PROFILES'
AND jur.jurisdiction_id=xr.jurisdiction_id
AND l1.lookup_type = 'XLE_REG_CODE'
AND jur.registration_code_le = l1.lookup_code
AND hp.party_id = xep.party_id
AND SYSDATE < NVL(xep.effective_to, SYSDATE + 1)
AND xep.party_id = p_le_party_id;
Select Territory_code
into l_country_code
From Fnd_Territories_Vl
where Territory_code = p_country_code
and obsolete_flag = 'N';
select LOOKUP_CODE
into l_lookup_code
from rrs_lookups_v
where LOOKUP_TYPE = 'RRS_GEO_SOURCE'
and LOOKUP_CODE = p_geo_source_code;
INSERT into RRS_INTERFACE_ERRORS(
SITE_ID,
SITE_IDENTIFICATION_NUMBER,
COLUMN_NAME,
MESSAGE_NAME,
MESSAGE_TYPE,
MESSAGE_TEXT,
SOURCE_TABLE_NAME,
DESTINATION_TABLE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
PROCESS_STATUS,
TRANSACTION_TYPE,
BATCH_ID
)
(select
*
from table( p_processing_errors)
);
Select RSB.Site_identification_number,RSB.site_id
from RRS_SITES_B RSB , RRS_SITES_INTERFACE RSI
where RSB.site_identification_number = RSI.site_identification_number
and RSI.batch_id = p_batch_id
and RSI.process_status = G_PS_IN_PROCESS
and RSI.transaction_type = G_TX_TYPE_CREATE;
insert into rrs_sites_b
(
SITE_ID
,SITE_IDENTIFICATION_NUMBER
,SITE_TYPE_CODE
,SITE_STATUS_CODE
,BRANDNAME_CODE
,CALENDAR_CODE
,LOCATION_ID
,SITE_PARTY_ID
,PARTY_SITE_ID
,LE_PARTY_ID
,IS_TEMPLATE_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
(select
rrs_sites_s.nextval
,SITE_IDENTIFICATION_NUMBER
,SITE_TYPE_CODE
,SITE_STATUS_CODE
,BRANDNAME_CODE
,CALENDAR_CODE
,LOCATION_ID
,SITE_PARTY_ID
,PARTY_SITE_ID
,LE_PARTY_ID
,'N'
,G_USER_ID
,SYSDATE
,LAST_UPDATED_BY
,SYSDATE
,LAST_UPDATE_LOGIN
From RRS_SITES_INTERFACE
where batch_id = p_batch_id
and transaction_type = G_TX_TYPE_CREATE
and process_status = G_PS_IN_PROCESS
) ;
insert into RRS_SITES_TL(
SITE_ID
,NAME
,LANGUAGE
,SOURCE_LANG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,DESCRIPTION
)
(select
B.site_id
,a.SITE_NAME
,L.LANGUAGE_CODE
,userenv('LANG')
,G_USER_ID
,SYSDATE
,a.LAST_UPDATED_BY
,SYSDATE
,a.LAST_UPDATE_LOGIN
,NULL
From RRS_SITES_INTERFACE A ,RRS_SITES_B B, FND_LANGUAGES L
where batch_id = p_batch_id
and transaction_type = G_TX_TYPE_CREATE
and process_status = G_PS_IN_PROCESS
and A.site_identification_number = b.site_identification_number
and L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from RRS_SITES_TL T
where T.SITE_ID = B.site_id
and T.LANGUAGE = L.LANGUAGE_CODE)
);
insert into RRS_SITE_USES(
SITE_USE_ID
,SITE_ID
,SITE_USE_TYPE_CODE
,STATUS_CODE
,IS_PRIMARY_FLAG
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
(
Select
rrs_site_uses_s.nextval
,B.SITE_ID
,A.SITE_USE_TYPE_CODE
,'A'
,'Y'
,1
,G_USER_ID
,SYSDATE
,G_USER_ID
,SYSDATE
,G_LOGIN_ID
From RRS_SITES_INTERFACE A ,RRS_SITES_B B
where batch_id = p_batch_id
and transaction_type = G_TX_TYPE_CREATE
and process_status = G_PS_IN_PROCESS
and A.SITE_USE_TYPE_CODE is NOT NULL
and A.site_identification_number = B.site_identification_number
);
/* RBAC 2 write a cursor code for checking every row for Grants and then insert the data */
if l_security_enabled = 'Y' and l_site_author_role = 'Y' then
select nvl(customer_id,person_party_id)
into l_party_id
from fnd_user
where user_id = FND_GLOBAL.USER_ID;
/* RBAC 2 write a cursor code for checking every row for Grants and then insert the data */
If ( p_purge_rows = 'Y' ) THEN
DELETE from RRS_SITES_INTERFACE
where batch_id = p_batch_id
and transaction_type = G_TX_TYPE_CREATE
and process_status = G_PS_IN_PROCESS;
UPDATE RRS_SITES_INTERFACE
SET PROCESS_STATUS = G_PS_SUCCESS
WHERE PROCESS_STATUS= G_PS_IN_PROCESS
AND BATCH_ID = p_batch_id
AND TRANSACTION_TYPE= G_TX_TYPE_CREATE;
Procedure Validate_update_rows(
p_batch_id IN number
,p_purge_rows IN varchar2
,x_return_flag OUT NOCOPY varchar2
) is
p_site_id varchar2(30);
cursor c_update_interface_row (l_batch_id number )is
select
a.SITE_ID site_id_intf
,a.SITE_IDENTIFICATION_NUMBER site_id_num_intf
,a.SITE_NAME site_name_intf
,a.SITE_TYPE_CODE site_type_code_intf
,a.SITE_STATUS_CODE site_status_code_intf
,a.SITE_USE_TYPE_CODE site_use_type_code_intf
,a.BRANDNAME_CODE brandname_code_intf
,a.CALENDAR_CODE calendar_code_intf
,a.LOCATION_STATUS location_status_intf
,a.LOCATION_ID location_id_intf
,a.SITE_PARTY_ID site_party_id_intf
,a.PARTY_SITE_ID party_site_id_intf
,a.LE_PARTY_ID le_party_id_intf
,a.ADDRESS1 address1_intf
,a.ADDRESS2 address2_intf
,a.ADDRESS3 address3_intf
,a.ADDRESS4 address4_intf
,a.ADDRESS_LINES_PHONETIC address_lines_phonetic_intf
,a.CITY city_intf
,a.POSTAL_CODE postal_code_intf
,a.STATE state_intf
,a.PROVINCE province_intf
,a.COUNTY county_intf
,a.COUNTRY country_intf
,a.GEOMETRY_SOURCE geometry_source_intf
,a.Longitude Longitude_intf
,a.Latitude Latitude_intf
,a.TRANSACTION_TYPE transaction_type_intf
,BATCH_PROCESSING
,BATCH_ID
,b.SITE_ID site_id
,b.SITE_IDENTIFICATION_NUMBER site_identification_number
,c.NAME site_name
,b.SITE_TYPE_CODE site_type_code
,b.SITE_STATUS_CODE site_status_code
,d.SITE_USE_TYPE_CODE site_use_type_code
,b.BRANDNAME_CODE brandname_code
,b.CALENDAR_CODE calendar_code
,b.LOCATION_ID location_id
,b.SITE_PARTY_ID site_party_id
,b.PARTY_SITE_ID party_site_id
,b.LE_PARTY_ID le_party_id
,h.ADDRESS1 address1
,h.ADDRESS2 address2
,h.ADDRESS3 address3
,h.ADDRESS4 address4
,h.ADDRESS_LINES_PHONETIC address_lines_phonetic
,h.CITY city
,h.POSTAL_CODE postal_code
,h.STATE state
,h.PROVINCE province
,h.COUNTY county
,h.COUNTRY country
,h.GEOMETRY_SOURCE geometry_source
,h.object_version_number
FROM RRS_SITES_INTERFACE a, RRS_SITES_B B , RRS_SITES_TL C, RRS_SITE_USES D,
HZ_LOCATIONS H
WHERE TRANSACTION_TYPE = G_TX_TYPE_UPDATE
and BATCH_ID = l_batch_id
and Process_status = G_PS_IN_PROCESS
and b.site_id = c.site_id
and c.language = userenv('Lang')
and a.site_id = d.site_id(+)
and d.is_primary_flag(+) = 'Y'
and h.location_id = b.location_id
and a.site_identification_number = b.site_identification_number;
l_update_location varchar2(1);
Update RRS_SITES_INTERFACE
Set Process_status = G_PS_IN_PROCESS,
REQUEST_ID = G_REQUEST_ID,
PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID,
PROGRAM_ID = G_PROGAM_ID,
PROGRAM_UPDATE_DATE = SYSDATE,
CREATED_BY = G_USER_ID,
CREATION_DATE = SYSDATE,
LAST_UPDATED_BY = G_USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = G_LOGIN_ID
Where Batch_id = p_batch_id
and process_status = G_PS_TO_BE_PROCESSED
and transaction_type = G_TX_TYPE_UPDATE;
FOR site_data IN c_update_interface_row(l_batch_id) LOOP
p_processing_errors := rrs_processing_errors_tab();
l_update_location := 'N';
l_update_location := 'Y';
update_address( p_site_id_num => site_data.site_id_num_intf
,p_location_id => site_data.location_id_intf
,p_location_status => site_data.location_status_intf
,p_country_code => site_data.country_intf
,p_address1 => site_data.address1_intf
,x_return_flag => x_return_flag
);
If l_update_location = 'Y' and l_row_status = 'S' then
l_location_rec.location_id := site_data.location_id;
hz_location_v2pub.update_location(p_init_msg_list => 'T'
,p_location_rec => l_location_rec
,p_do_addr_val => 'Y'
,p_object_version_number => site_data.object_version_number
,x_addr_val_status => x_addr_val_status
,x_addr_warn_msg => x_addr_warn_msg
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
-- dbms_output.put_line('location updated for Site : '||site_data.site_identification_number);
RRS_SITE_UTILS.Update_geometry_for_locations ( p_loc_id => site_data.location_id
,p_lat => site_data.latitude_intf
,p_long => site_data.longitude_intf
,p_status => 'GOOD'
,p_geo_source => nvl(site_data.geometry_source_intf , l_geo_source )
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
we will update the process_status of this row to be Validation_succeeded
( 4 )
*********************************************/
If l_row_status = 'S' then
if l_create_party = 'Y' then
update rrs_sites_interface
set site_party_id = x_party_id ,
party_site_id = x_party_site_id
where site_identification_number = site_data.site_id_num_intf;
Update RRS_SITES_INTERFACE
Set Process_status = G_PS_SUCCESS
Where Batch_id = p_batch_id
and process_status = G_PS_IN_PROCESS
and site_identification_number = site_data.site_id_num_intf
and transaction_type = G_TX_TYPE_UPDATE;
Update RRS_SITES_INTERFACE
Set Process_status = '3'
Where Batch_id = p_batch_id
and process_status = G_PS_IN_PROCESS
and site_identification_number = site_data.site_id_num_intf
and transaction_type = G_TX_TYPE_UPDATE;
* case. Update all the rows with process_status = '2' to '1' after
* completion of the processing logic. It should always return 0 rows.
* *********************/
Update RRS_SITES_INTERFACE
Set Process_status = G_PS_TO_BE_PROCESSED
Where Batch_id = p_batch_id
and process_status = G_PS_IN_PROCESS
and transaction_type in ( G_TX_TYPE_UPDATE );
Update_sites(
p_batch_id => p_batch_id
,p_transaction_type => G_TX_TYPE_UPDATE
,p_purge_rows => p_purge_rows
,x_num_rows => x_num_rows
,x_return_status => x_return_status
);
Procedure Update_sites(
p_batch_id IN number
,p_transaction_type IN varchar2
,p_purge_rows IN varchar2
,x_num_rows OUT NOCOPY number
,x_return_status OUT NOCOPY varchar2
)is
begin
/***********************************************
* Here the code starts for copying the validation succeeded data from
* Interface tables into base tables.
* ************************************************/
Update RRS_SITES_B A
Set (
A.SITE_TYPE_CODE, A.SITE_STATUS_CODE , A.BRANDNAME_CODE , A.CALENDAR_CODE , A.SITE_PARTY_ID , A.PARTY_SITE_ID ,
A.LE_PARTY_ID , A.OBJECT_VERSION_NUMBER , A.LAST_UPDATED_BY , A.LAST_UPDATE_DATE
) =
(SELECT
B.SITE_TYPE_CODE , B.SITE_STATUS_CODE, B.BRANDNAME_CODE, B.CALENDAR_CODE, B.SITE_PARTY_ID, B.PARTY_SITE_ID,
B.LE_PARTY_ID, A.OBJECT_VERSION_NUMBER + 1, B.LAST_UPDATED_BY, sysdate
From RRS_SITES_INTERFACE B
where A.site_identification_number = B.site_identification_number
and B.Batch_id = p_batch_id
and B.PROCESS_STATUS = G_PS_IN_PROCESS
and B.Transaction_type = G_TX_TYPE_UPDATE )
Where A.site_identification_number in (select C.site_identification_number
from RRS_SITES_INTERFACE C
Where C.Batch_id = p_batch_id
and C.Process_status = G_PS_IN_PROCESS
and C.Transaction_type = G_TX_TYPE_UPDATE );
FND_FILE.put_line(FND_FILE.LOG, 'Total No. of Sites Updated : '||to_char(sql%rowcount));
Update RRS_SITES_TL A
Set Name = (select site_name from RRS_SITES_INTERFACE B, RRS_SITES_B RSB
where RSB.site_identification_number = B.site_identification_number
and RSB.Site_id = A.Site_id
and B.Batch_id = p_batch_id
and B.PROCESS_STATUS = G_PS_IN_PROCESS
and B.Transaction_type = G_TX_TYPE_UPDATE ),
SOURCE_LANG = userenv('LANG')
Where A.site_id in (select RSB1.site_id
from RRS_SITES_INTERFACE C, RRS_SITES_B RSB1
Where C.Batch_id = p_batch_id
and RSB1.site_identification_number = C.site_identification_number
and C.Process_status = G_PS_IN_PROCESS
and C.Transaction_type = G_TX_TYPE_UPDATE )
AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
insert into RRS_SITE_USES(
SITE_USE_ID
,SITE_ID
,SITE_USE_TYPE_CODE
,STATUS_CODE
,IS_PRIMARY_FLAG
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
(
Select
rrs_site_uses_s.nextval
,B.SITE_ID
,SITE_USE_TYPE_CODE
,'A'
,'Y'
,1
,G_USER_ID
,SYSDATE
,A.LAST_UPDATED_BY
,SYSDATE
,A.LAST_UPDATE_LOGIN
From RRS_SITES_INTERFACE A ,RRS_SITES_B B
where batch_id = p_batch_id
and transaction_type = G_TX_TYPE_UPDATE
and process_status = G_PS_IN_PROCESS
and A.SITE_USE_TYPE_CODE is NOT NULL
and A.site_identification_number = B.site_identification_number
and NOT EXISTS ( select C.site_id
from RRS_SITE_USES C
where B.Site_id = C.Site_id )
);
DELETE from RRS_SITES_INTERFACE
where batch_id = p_batch_id
and transaction_type = G_TX_TYPE_UPDATE
and process_status = G_PS_IN_PROCESS;
UPDATE RRS_SITES_INTERFACE
SET PROCESS_STATUS = G_PS_SUCCESS
WHERE PROCESS_STATUS=G_PS_IN_PROCESS
AND BATCH_ID = p_batch_id
AND TRANSACTION_TYPE=G_TX_TYPE_UPDATE;