[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR For_Insert_Cur (p_loc_cd VARCHAR2, p_loc_type_cd VARCHAR2, p_org_id NUMBER)
IS
SELECT MIN(active_start_date), MAX(active_end_date)
FROM pn_locations_all
WHERE location_code = p_loc_cd
AND location_type_lookup_code = p_loc_type_cd
AND org_id = p_org_id;
CURSOR For_Update_St_Cur (p_loc_id NUMBER, p_str_dt DATE, p_str_dt_old DATE)
IS
SELECT TO_NUMBER(MIN(p_str_dt - active_end_date)) start_date_diff
FROM pn_locations_all
WHERE location_id = p_loc_id
AND ROWID <> g_pn_locations_rowid
AND active_end_date < p_str_dt_old;
CURSOR For_Update_End_Cur (p_loc_id NUMBER, p_end_dt DATE, p_end_dt_old DATE)
IS
SELECT TO_NUMBER(MAX(p_end_dt - active_start_date)) end_date_diff
FROM pn_locations_all
WHERE location_id = p_loc_id
AND ROWID <> g_pn_locations_rowid
AND active_start_date > p_end_dt_old;
SELECT location_id, active_start_date, active_end_date, NVL(rentable_area,0) rentable_area
FROM pn_locations_all
WHERE location_type_lookup_code = p_lkp_code
AND active_start_date <= NVL(p_act_end_dt, TO_DATE('12/31/4712','MM/DD/YYYY'))
AND active_end_date >= p_act_str_dt
START WITH location_id = p_loc_id
CONNECT BY PRIOR location_id = parent_location_id
AND p_act_str_dt between prior active_start_date and
NVL(prior active_end_date,TO_DATE('12/31/4712','MM/DD/YYYY'));
loc_info_tbl.delete;
OPEN for_insert_cur(p_loc_cd => p_location_code, p_loc_type_cd => p_location_type_lookup_code, p_org_id => p_org_id);
FETCH for_insert_cur
INTO l_min_start_date,
l_max_end_date;
CLOSE for_insert_cur;
FOR update_rec in for_update_st_cur(p_loc_id => p_location_id, p_str_dt => p_active_start_date, p_str_dt_old => p_active_start_date_old)
LOOP
pnp_debug_pkg.debug(' ChkLocOlap> Start date diff = '|| update_rec.start_date_diff);
IF update_rec.start_date_diff < 1 then
x_return_status := FND_API.G_RET_STS_ERROR;
FOR update_rec in for_update_end_cur(p_loc_id => p_location_id, p_end_dt => p_active_end_date, p_end_dt_old => p_active_end_date_old)
LOOP
pnp_debug_pkg.debug(' ChkLocOlap> End date diff = '|| update_rec.end_date_diff);
IF update_rec.end_date_diff > -1 then
x_return_status := FND_API.G_RET_STS_ERROR;
OPEN for_insert_cur(p_loc_cd => p_location_code, p_loc_type_cd => p_location_type_lookup_code, p_org_id => p_org_id);
FETCH for_insert_cur
INTO l_min_start_date,
l_max_end_date;
CLOSE for_insert_cur;
FOR update_rec in for_update_st_cur(p_loc_id => p_location_id, p_str_dt => p_active_start_date, p_str_dt_old => p_active_start_date_old)
LOOP
pnp_debug_pkg.debug(' ChkLocGap> Start date diff = '|| update_rec.start_date_diff);
IF update_rec.start_date_diff <> 1 THEN
pnp_debug_pkg.debug(' ChkLocGap> StrDt not Null, Diff <> 1');
FOR update_rec in for_update_end_cur(p_loc_id => p_location_id, p_end_dt => p_active_end_date, p_end_dt_old => p_active_end_date_old)
LOOP
pnp_debug_pkg.debug(' ChkLocGap> End date diff = '|| update_rec.end_date_diff);
IF update_rec.end_date_diff <> -1 then
pnp_debug_pkg.debug(' ChkLocGap> EndDt not Null, Diff <> 1');
SELECT ROWID
INTO l_rowid
FROM pn_locations_all
WHERE location_id = p_location_id
AND active_start_date = p_active_start_date
AND active_end_date = NVL(p_active_end_date,g_end_of_time);
SELECT *
FROM pn_locations_all
WHERE location_id = g_loc_recinfo_tmp.parent_location_id
AND active_start_date <= g_loc_recinfo_tmp.active_end_date
AND active_end_date >= g_loc_recinfo_tmp.active_start_date;
g_loc_recinfo_tmp.program_update_date := NULL;
PROCEDURE Correct_Update_Row(
p_pn_locations_rec IN pn_locations_all%ROWTYPE
,p_pn_addresses_rec IN pn_addresses_all%ROWTYPE
,p_change_mode IN VARCHAR2
,p_as_of_date IN DATE
,p_active_start_date_old IN DATE
,p_active_end_date_old IN DATE
,p_assgn_area_chgd_flag IN VARCHAR2
,p_validate IN BOOLEAN
,p_cascade IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_return_message OUT NOCOPY VARCHAR2
)
IS
l_rowid rowid;
SELECT *
FROM pn_locations_all
WHERE location_id = p_pn_locations_rec.location_id
AND active_start_date = NVL(p_active_start_date_old, g_start_of_time)
AND active_end_date = NVL(p_active_end_date_old, g_end_of_time);
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Correct_Update_Row (+) Mode: '||p_change_mode||', Cascade: '||p_cascade
||', AsofDt: '||p_as_of_date);
ELSIF p_change_mode = 'UPDATE' THEN
l_str_dt := p_as_of_date;
PNT_LOCATIONS_PKG.update_assignments (
p_location_id => p_pn_locations_rec.location_id,
p_active_start_date => p_pn_locations_Rec.active_start_date,
p_active_end_date => p_pn_locations_rec.active_end_date,
p_active_start_date_old => p_active_start_date_old,
p_active_end_date_old => p_active_end_date_old,
x_return_status => x_return_status,
x_return_message => x_return_message);
PNT_LOCATIONS_PKG.Update_child_for_dates (
p_location_id => p_pn_locations_rec.location_id,
p_active_start_date => p_pn_locations_Rec.active_start_date,
p_active_end_date => p_pn_locations_rec.active_end_date,
p_active_start_date_old => p_active_start_date_old,
p_active_end_date_old => p_active_end_date_old,
p_location_type_lookup_code => p_pn_locations_rec.location_type_lookup_code,
x_return_status => x_return_status,
x_return_message => x_return_message);
PNP_DEBUG_PKG.put_log_msg('call update row ');
pnt_locations_pkg.Update_Locn_Row(
p_loc_recinfo => g_loc_recinfo_tmp
,p_adr_recinfo => g_loc_adrinfo_tmp
,p_assgn_area_chgd_flag => p_change_mode
,x_return_status => x_return_status
,x_return_message => x_return_message
);
ELSIF p_change_mode = 'UPDATE' THEN
l_active_start_date_new := G_LOC_RECINFO.active_start_date;
pnt_locations_pkg.Update_Locn_Row(
p_loc_recinfo => g_loc_recinfo_tmp
,p_adr_recinfo => g_loc_adrinfo_tmp
,p_assgn_area_chgd_flag => p_change_mode
,x_return_status => x_return_status
,x_return_message => x_return_message
);
pnp_debug_pkg.put_log_msg('Calling UPDATE ROW.');
pnp_debug_pkg.put_log_msg('Calling INSERT ROW address = ' || G_LOC_RECINFO.ADDRESS_ID);
pnt_locations_pkg.Insert_Locn_Row(
p_loc_recinfo => g_loc_recinfo_tmp
,p_adr_recinfo => g_loc_adrinfo_tmp
,p_change_mode => p_change_mode
,x_return_status => x_return_status
,x_return_message => x_return_message
);
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Correct_Update_Row (-) ReturnStatus: '||x_return_status);
END Correct_Update_Row;
SELECT *
FROM pn_locations_all
START WITH ( parent_location_id = p_location_id
AND active_start_date <= NVL(p_end_date, TO_DATE('12/31/4712','MM/DD/YYYY'))
AND active_end_date >= p_start_date
)
CONNECT BY ( PRIOR location_id = parent_location_id
AND active_end_date >= prior active_start_date
AND active_start_date <= prior active_end_date
)
ORDER BY location_type_lookup_code, active_start_date;
pnt_locations_pkg.Update_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
pnt_locations_pkg.Insert_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
pnt_locations_pkg.Insert_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
pnt_locations_pkg.Update_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
pnt_locations_pkg.Insert_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
pnt_locations_pkg.Update_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
pnt_locations_pkg.Insert_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
pnt_locations_pkg.Update_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
SELECT 'TRUE'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pn_space_assign_cust_all, (SELECT location_id loc_id
FROM pn_locations_all
WHERE active_start_date <= l_end_date
AND active_end_date >= l_str_date
START WITH location_id = p_location_id
CONNECT BY PRIOR location_id = parent_location_id
UNION
SELECT -1 loc_id
FROM DUAL) loc
WHERE p_asgn_mode IN ('ALL', 'CUST')
AND ((p_location_type IN ('OFFICE', 'SECTION') AND
location_id = p_location_id AND
loc.loc_id=-1) OR
(p_location_type NOT IN ('OFFICE', 'SECTION') AND
location_id =loc.loc_id))
AND cust_assign_start_date <= l_end_date
AND NVL(cust_assign_end_date, TO_DATE('12/31/4712','MM/DD/YYYY')) >= l_str_date)
OR EXISTS (SELECT NULL
FROM pn_space_assign_emp_all, (SELECT location_id loc_id
FROM pn_locations_all
WHERE active_start_date <= l_end_date
AND active_end_date >= l_str_date
START WITH location_id = p_location_id
CONNECT BY PRIOR location_id = parent_location_id
UNION
SELECT -1 loc_id
FROM DUAL) loc
WHERE p_asgn_mode IN ('ALL', 'EMP', 'CC')
AND ((p_location_type IN ('OFFICE', 'SECTION') AND
location_id = p_location_id AND
loc.loc_id=-1) OR
(p_location_type NOT IN ('OFFICE', 'SECTION') AND
location_id =loc.loc_id))
AND ((p_asgn_mode = 'ALL') OR
(p_asgn_mode = 'EMP' AND person_id IS NOT NULL) OR
(p_asgn_mode = 'CC' AND cost_center_code IS NOT NULL))
AND emp_assign_start_date <= l_end_date
AND NVL(emp_assign_end_date, TO_DATE('12/31/4712','MM/DD/YYYY')) >= l_str_date);
SELECT 'TRUE'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pn_locations_all
WHERE location_id = p_parent_location_id
AND active_start_date <= l_end_date
AND active_end_date >= l_str_date
AND ((p_status_mode IN ('OCC') AND NVL(occupancy_status_code, 'Y') = 'N')
OR (p_status_mode IN ('EMP') AND NVL(assignable_emp, 'Y') = 'N')
OR (p_status_mode IN ('CC') AND NVL(assignable_cc, 'Y') = 'N')
OR (p_status_mode IN ('CUST') AND NVL(assignable_cust, 'Y') = 'N')));
PROCEDURE insert_row (
x_rowid IN OUT NOCOPY rowid
,x_org_id IN NUMBER
,x_LOCATION_ID IN OUT NOCOPY NUMBER
,x_LAST_UPDATE_DATE DATE
,x_LAST_UPDATED_BY NUMBER
,x_CREATION_DATE DATE
,x_CREATED_BY NUMBER
,x_LAST_UPDATE_LOGIN NUMBER
,x_LOCATION_PARK_ID NUMBER
,x_LOCATION_TYPE_LOOKUP_CODE VARCHAR2
,x_SPACE_TYPE_LOOKUP_CODE VARCHAR2
,x_FUNCTION_TYPE_LOOKUP_CODE VARCHAR2
,x_STANDARD_TYPE_LOOKUP_CODE VARCHAR2
,x_LOCATION_ALIAS VARCHAR2
,x_LOCATION_CODE VARCHAR2
,x_BUILDING VARCHAR2
,x_LEASE_OR_OWNED VARCHAR2
,x_CLASS VARCHAR2
,x_STATUS_TYPE VARCHAR2
,x_FLOOR VARCHAR2
,x_OFFICE VARCHAR2
,x_ADDRESS_ID IN OUT NOCOPY NUMBER
,x_MAX_CAPACITY NUMBER
,x_OPTIMUM_CAPACITY NUMBER
,x_GROSS_AREA NUMBER
,x_RENTABLE_AREA NUMBER
,x_USABLE_AREA NUMBER
,x_ASSIGNABLE_AREA NUMBER
,x_COMMON_AREA NUMBER
,x_SUITE VARCHAR2
,x_ALLOCATE_COST_CENTER_CODE VARCHAR2
,x_UOM_CODE VARCHAR2
,x_DESCRIPTION VARCHAR2
,x_PARENT_LOCATION_ID NUMBER
,x_INTERFACE_FLAG VARCHAR2
,x_REQUEST_ID NUMBER
,x_PROGRAM_APPLICATION_ID NUMBER
,x_PROGRAM_ID NUMBER
,x_PROGRAM_UPDATE_DATE DATE
,x_STATUS VARCHAR2
,x_PROPERTY_ID NUMBER
,x_ATTRIBUTE_CATEGORY VARCHAR2
,x_ATTRIBUTE1 VARCHAR2
,x_ATTRIBUTE2 VARCHAR2
,x_ATTRIBUTE3 VARCHAR2
,x_ATTRIBUTE4 VARCHAR2
,x_ATTRIBUTE5 VARCHAR2
,x_ATTRIBUTE6 VARCHAR2
,x_ATTRIBUTE7 VARCHAR2
,x_ATTRIBUTE8 VARCHAR2
,x_ATTRIBUTE9 VARCHAR2
,x_ATTRIBUTE10 VARCHAR2
,x_ATTRIBUTE11 VARCHAR2
,x_ATTRIBUTE12 VARCHAR2
,x_ATTRIBUTE13 VARCHAR2
,x_ATTRIBUTE14 VARCHAR2
,x_ATTRIBUTE15 VARCHAR2
,x_address_line1 VARCHAR2
,x_address_line2 VARCHAR2
,x_address_line3 VARCHAR2
,x_address_line4 VARCHAR2
,x_county VARCHAR2
,x_city VARCHAR2
,x_state VARCHAR2
,x_province VARCHAR2
,x_zip_code VARCHAR2
,x_country VARCHAR2
,x_territory_id NUMBER
,x_addr_last_update_date DATE
,x_addr_last_updated_by NUMBER
,x_addr_creation_date DATE
,x_addr_created_by NUMBER
,x_addr_last_update_login NUMBER
,x_addr_attribute_category VARCHAR2
,x_addr_attribute1 VARCHAR2
,x_addr_attribute2 VARCHAR2
,x_addr_attribute3 VARCHAR2
,x_addr_attribute4 VARCHAR2
,x_addr_attribute5 VARCHAR2
,x_addr_attribute6 VARCHAR2
,x_addr_attribute7 VARCHAR2
,x_addr_attribute8 VARCHAR2
,x_addr_attribute9 VARCHAR2
,x_addr_attribute10 VARCHAR2
,x_addr_attribute11 VARCHAR2
,x_addr_attribute12 VARCHAR2
,x_addr_attribute13 VARCHAR2
,x_addr_attribute14 VARCHAR2
,x_addr_attribute15 VARCHAR2
,x_common_area_flag VARCHAR2
,x_active_start_date DATE
,x_active_end_date DATE
,x_return_status OUT NOCOPY VARCHAR2
,x_return_message OUT NOCOPY VARCHAR2
,x_bookable_flag VARCHAR2
,x_change_mode VARCHAR2
,x_occupancy_status_code VARCHAR2
,x_assignable_emp VARCHAR2
,x_assignable_cc VARCHAR2
,x_assignable_cust VARCHAR2
,x_disposition_code VARCHAR2
,x_acc_treatment_code VARCHAR2
,x_source VARCHAR2
)
IS
CURSOR c IS
SELECT ROWID
FROM pn_locations_all
WHERE location_id = x_location_id
AND active_start_date = NVL(x_active_start_date, g_start_of_time)
AND active_end_date = NVL(x_active_end_date, g_end_of_time);
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.insert_row (+) LocId: '||x_location_id||', LocCd: '||x_location_code
||', Type: '||x_location_type_lookup_code
||', ActStrDt: '||TO_CHAR(x_active_start_date, 'MM/DD/YYYY')
||', ActEndDt: '||TO_CHAR(x_active_end_date, 'MM/DD/YYYY'));
IF NVL(x_change_mode,'X') not in ('UPDATE') THEN
PNT_LOCATIONS_PKG.check_location_overlap (
p_org_id => x_org_id,
p_location_id => NULL,
p_location_code => x_location_code,
p_location_type_lookup_code => x_location_type_lookup_code,
p_active_start_date => x_active_start_date,
p_active_end_date => NVL(x_active_end_date,G_END_OF_TIME),
p_active_start_date_old => null,
p_active_end_date_old => null,
x_return_status => l_return_status,
x_return_message => l_return_message);
and NVL(x_change_mode,'X') not in ('UPDATE') THEN
PNT_LOCATIONS_PKG.check_location_gaps (
p_org_id => x_org_id,
p_location_id => null,
p_location_code => x_location_code,
p_location_type_lookup_code => x_location_type_lookup_code,
p_active_start_date => x_active_start_date,
p_active_end_date => x_active_end_date,
p_active_start_date_old => null,
p_active_end_date_old => null,
x_return_status => l_return_status,
x_return_message => l_return_message);
pnp_debug_pkg.put_log_msg('Calling address insert address =' || x_address_id);
PNT_ADDR_PKG.insert_row (
x_address_id,
x_address_line1,
x_address_line2,
x_address_line3,
x_address_line4,
x_county,
x_city,
x_state,
x_province,
x_zip_code,
x_country,
x_territory_id,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
x_addr_attribute_category,
x_addr_attribute1,
x_addr_attribute2,
x_addr_attribute3,
x_addr_attribute4,
x_addr_attribute5,
x_addr_attribute6,
x_addr_attribute7,
x_addr_attribute8,
x_addr_attribute9,
x_addr_attribute10,
x_addr_attribute11,
x_addr_attribute12,
x_addr_attribute13,
x_addr_attribute14,
x_addr_attribute15,
x_org_id
);
SELECT pn_locations_s.NEXTVAL
INTO x_location_id
FROM DUAL;
pnp_debug_pkg.debug(' InsRow> before insert' || x_change_mode);
INSERT INTO pn_locations_all (
LOCATION_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LOCATION_PARK_ID
,LOCATION_TYPE_LOOKUP_CODE
,SPACE_TYPE_LOOKUP_CODE
,FUNCTION_TYPE_LOOKUP_CODE
,STANDARD_TYPE_LOOKUP_CODE
,LOCATION_ALIAS
,LOCATION_CODE
,BUILDING
,LEASE_OR_OWNED
,CLASS
,STATUS_TYPE
,FLOOR
,OFFICE
,ADDRESS_ID
,MAX_CAPACITY
,OPTIMUM_CAPACITY
,GROSS_AREA
,RENTABLE_AREA
,USABLE_AREA
,ASSIGNABLE_AREA
,COMMON_AREA
,SUITE
,ALLOCATE_COST_CENTER_CODE
,UOM_CODE
,DESCRIPTION
,PARENT_LOCATION_ID
,INTERFACE_FLAG
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,STATUS
,PROPERTY_ID
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,COMMON_AREA_FLAG
,ORG_ID
,ACTIVE_START_DATE
,ACTIVE_END_DATE
,BOOKABLE_FLAG
,occupancy_status_code
,assignable_emp
,assignable_cc
,assignable_cust
,disposition_code
,acc_treatment_code
,source
)
VALUES
(
x_LOCATION_ID
,x_LAST_UPDATE_DATE
,x_LAST_UPDATED_BY
,x_CREATION_DATE
,x_CREATED_BY
,x_LAST_UPDATE_LOGIN
,x_LOCATION_PARK_ID
,x_LOCATION_TYPE_LOOKUP_CODE
,x_SPACE_TYPE_LOOKUP_CODE
,x_FUNCTION_TYPE_LOOKUP_CODE
,x_STANDARD_TYPE_LOOKUP_CODE
,x_LOCATION_ALIAS
,x_LOCATION_CODE
,x_BUILDING
,x_LEASE_OR_OWNED
,x_CLASS
,x_STATUS_TYPE
,x_FLOOR
,x_OFFICE
,x_ADDRESS_ID
,x_MAX_CAPACITY
,x_OPTIMUM_CAPACITY
,x_GROSS_AREA
,x_RENTABLE_AREA
,x_USABLE_AREA
,x_ASSIGNABLE_AREA
,x_COMMON_AREA
,x_SUITE
,x_ALLOCATE_COST_CENTER_CODE
,x_UOM_CODE
,x_DESCRIPTION
,x_PARENT_LOCATION_ID
,x_INTERFACE_FLAG
,x_REQUEST_ID
,x_PROGRAM_APPLICATION_ID
,x_PROGRAM_ID
,x_PROGRAM_UPDATE_DATE
,x_STATUS
,x_PROPERTY_ID
,x_ATTRIBUTE_CATEGORY
,x_ATTRIBUTE1
,x_ATTRIBUTE2
,x_ATTRIBUTE3
,x_ATTRIBUTE4
,x_ATTRIBUTE5
,x_ATTRIBUTE6
,x_ATTRIBUTE7
,x_ATTRIBUTE8
,x_ATTRIBUTE9
,x_ATTRIBUTE10
,x_ATTRIBUTE11
,x_ATTRIBUTE12
,x_ATTRIBUTE13
,x_ATTRIBUTE14
,x_ATTRIBUTE15
,x_COMMON_AREA_FLAG
,x_org_id
,x_ACTIVE_START_DATE
,NVL(x_ACTIVE_END_DATE,G_END_OF_TIME)
,x_bookable_flag
,x_occupancy_status_code
,x_assignable_emp
,x_assignable_cc
,x_assignable_cust
,x_disposition_code
,x_acc_treatment_code
,x_source
);
pnp_debug_pkg.debug(' InsRow> Error in insert');
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.insert_row (-) ReturnStatus: '||x_return_status);
END insert_row;
PROCEDURE UPDATE_ROW (
x_LOCATION_ID NUMBER
,x_LAST_UPDATE_DATE DATE
,x_LAST_UPDATED_BY NUMBER
,x_LAST_UPDATE_LOGIN NUMBER
,x_LOCATION_PARK_ID NUMBER
,x_LOCATION_TYPE_LOOKUP_CODE VARCHAR2
,x_SPACE_TYPE_LOOKUP_CODE VARCHAR2
,x_FUNCTION_TYPE_LOOKUP_CODE VARCHAR2
,x_STANDARD_TYPE_LOOKUP_CODE VARCHAR2
,x_BUILDING VARCHAR2
,x_LEASE_OR_OWNED VARCHAR2
,x_CLASS VARCHAR2
,x_STATUS_TYPE VARCHAR2
,x_FLOOR VARCHAR2
,x_OFFICE VARCHAR2
,x_ADDRESS_ID NUMBER
,x_MAX_CAPACITY NUMBER
,x_OPTIMUM_CAPACITY NUMBER
,x_GROSS_AREA NUMBER
,x_RENTABLE_AREA NUMBER
,x_USABLE_AREA NUMBER
,x_ASSIGNABLE_AREA NUMBER
,x_COMMON_AREA NUMBER
,x_SUITE VARCHAR2
,x_ALLOCATE_COST_CENTER_CODE VARCHAR2
,x_UOM_CODE VARCHAR2
,x_DESCRIPTION VARCHAR2
,x_PARENT_LOCATION_ID NUMBER
,x_INTERFACE_FLAG VARCHAR2
,x_STATUS VARCHAR2
,x_PROPERTY_ID NUMBER
,x_ATTRIBUTE_CATEGORY VARCHAR2
,x_ATTRIBUTE1 VARCHAR2
,x_ATTRIBUTE2 VARCHAR2
,x_ATTRIBUTE3 VARCHAR2
,x_ATTRIBUTE4 VARCHAR2
,x_ATTRIBUTE5 VARCHAR2
,x_ATTRIBUTE6 VARCHAR2
,x_ATTRIBUTE7 VARCHAR2
,x_ATTRIBUTE8 VARCHAR2
,x_ATTRIBUTE9 VARCHAR2
,x_ATTRIBUTE10 VARCHAR2
,x_ATTRIBUTE11 VARCHAR2
,x_ATTRIBUTE12 VARCHAR2
,x_ATTRIBUTE13 VARCHAR2
,x_ATTRIBUTE14 VARCHAR2
,x_ATTRIBUTE15 VARCHAR2
,x_address_line1 VARCHAR2
,x_address_line2 VARCHAR2
,x_address_line3 VARCHAR2
,x_address_line4 VARCHAR2
,x_county VARCHAR2
,x_city VARCHAR2
,x_state VARCHAR2
,x_province VARCHAR2
,x_zip_code VARCHAR2
,x_country VARCHAR2
,x_territory_id NUMBER
,x_addr_last_update_date DATE
,x_addr_last_updated_by NUMBER
,x_addr_last_update_login NUMBER
,x_addr_attribute_category VARCHAR2
,x_addr_attribute1 VARCHAR2
,x_addr_attribute2 VARCHAR2
,x_addr_attribute3 VARCHAR2
,x_addr_attribute4 VARCHAR2
,x_addr_attribute5 VARCHAR2
,x_addr_attribute6 VARCHAR2
,x_addr_attribute7 VARCHAR2
,x_addr_attribute8 VARCHAR2
,x_addr_attribute9 VARCHAR2
,x_addr_attribute10 VARCHAR2
,x_addr_attribute11 VARCHAR2
,x_addr_attribute12 VARCHAR2
,x_addr_attribute13 VARCHAR2
,x_addr_attribute14 VARCHAR2
,x_addr_attribute15 VARCHAR2
,x_common_area_flag VARCHAR2
,x_assgn_area_chgd_flag VARCHAR2
,x_active_start_date DATE
,x_active_end_date DATE
,x_return_status OUT NOCOPY varchar2
,x_return_message OUT NOCOPY varchar2
,x_bookable_flag VARCHAR2
,x_occupancy_status_code VARCHAR2
,x_assignable_emp VARCHAR2
,x_assignable_cc VARCHAR2
,x_assignable_cust VARCHAR2
,x_disposition_code VARCHAR2
,x_acc_treatment_code VARCHAR2
,x_source VARCHAR2
)
IS
l_return_status VARCHAR2(2000) := NULL;
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.update_row (+) LocId: '||x_location_id||', Type: '||x_location_type_lookup_code
||', ActStrDt: '||TO_CHAR(x_active_start_date, 'MM/DD/YYYY')
||', ActEndDt: '||TO_CHAR(x_active_end_date, 'MM/DD/YYYY'));
'UPDATE') THEN
fnd_message.set_name('PN', 'PN_GROSS_RENTABLE');
IF x_assgn_area_chgd_flag IN ('CORRECT', 'UPDATE') THEN
pnp_util_func.loctn_assgn_area_update(p_loc_id => x_location_id
,p_assgn_area => x_assignable_area
,p_str_dt => x_active_start_date
,p_end_dt => x_active_end_date
);
pnp_debug_pkg.put_log_msg('update locations');
UPDATE PN_LOCATIONS_ALL
SET
LAST_UPDATE_DATE = x_LAST_UPDATE_DATE
,LAST_UPDATED_BY = x_LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN
,LOCATION_PARK_ID = x_LOCATION_PARK_ID
,LOCATION_TYPE_LOOKUP_CODE = x_LOCATION_TYPE_LOOKUP_CODE
,SPACE_TYPE_LOOKUP_CODE = x_SPACE_TYPE_LOOKUP_CODE
,FUNCTION_TYPE_LOOKUP_CODE = x_FUNCTION_TYPE_LOOKUP_CODE
,STANDARD_TYPE_LOOKUP_CODE = x_STANDARD_TYPE_LOOKUP_CODE
,BUILDING = x_BUILDING
,LEASE_OR_OWNED = x_LEASE_OR_OWNED
,CLASS = x_CLASS -- Added redwin
,STATUS_TYPE = x_STATUS_TYPE
,FLOOR = x_FLOOR
,OFFICE = x_OFFICE
,ADDRESS_ID = x_ADDRESS_ID
,MAX_CAPACITY = x_MAX_CAPACITY
,OPTIMUM_CAPACITY = x_OPTIMUM_CAPACITY
,GROSS_AREA = x_GROSS_AREA
,RENTABLE_AREA = x_RENTABLE_AREA
,USABLE_AREA = x_USABLE_AREA
,ASSIGNABLE_AREA = x_ASSIGNABLE_AREA
,COMMON_AREA = x_COMMON_AREA
,SUITE = x_SUITE
,ALLOCATE_COST_CENTER_CODE = x_ALLOCATE_COST_CENTER_CODE
,UOM_CODE = x_UOM_CODE
,DESCRIPTION = x_DESCRIPTION
,PARENT_LOCATION_ID = x_PARENT_LOCATION_ID
,INTERFACE_FLAG = x_INTERFACE_FLAG
,STATUS = x_STATUS
,PROPERTY_ID = x_PROPERTY_ID
,ATTRIBUTE_CATEGORY = x_ATTRIBUTE_CATEGORY
,ATTRIBUTE1 = x_ATTRIBUTE1
,ATTRIBUTE2 = x_ATTRIBUTE2
,ATTRIBUTE3 = x_ATTRIBUTE3
,ATTRIBUTE4 = x_ATTRIBUTE4
,ATTRIBUTE5 = x_ATTRIBUTE5
,ATTRIBUTE6 = x_ATTRIBUTE6
,ATTRIBUTE7 = x_ATTRIBUTE7
,ATTRIBUTE8 = x_ATTRIBUTE8
,ATTRIBUTE9 = x_ATTRIBUTE9
,ATTRIBUTE10 = x_ATTRIBUTE10
,ATTRIBUTE11 = x_ATTRIBUTE11
,ATTRIBUTE12 = x_ATTRIBUTE12
,ATTRIBUTE13 = x_ATTRIBUTE13
,ATTRIBUTE14 = x_ATTRIBUTE14
,ATTRIBUTE15 = x_ATTRIBUTE15
,COMMON_AREA_FLAG = x_COMMON_AREA_FLAG
,ACTIVE_START_DATE = x_active_start_date
,ACTIVE_END_DATE = NVL(x_active_end_date,g_end_of_time)
,BOOKABLE_FLAG = x_bookable_flag
,occupancy_status_code = x_occupancy_status_code
,assignable_emp = x_assignable_emp
,assignable_cc = x_assignable_cc
,assignable_cust = x_assignable_cust
,disposition_code = x_disposition_code
,acc_treatment_code = x_acc_treatment_code
,source = x_source
WHERE ROWID = g_pn_locations_rowid;
PNT_ADDR_PKG.update_row (
x_address_id,
x_address_line1,
x_address_line2,
x_address_line3,
x_address_line4,
x_county,
x_city,
x_state,
x_province,
x_zip_code,
x_country,
x_territory_id,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
x_addr_attribute_category,
x_addr_attribute1,
x_addr_attribute2,
x_addr_attribute3,
x_addr_attribute4,
x_addr_attribute5,
x_addr_attribute6,
x_addr_attribute7,
x_addr_attribute8,
x_addr_attribute9,
x_addr_attribute10,
x_addr_attribute11,
x_addr_attribute12,
x_addr_attribute13,
x_addr_attribute14,
x_addr_attribute15
);
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.update_row (-) ReturnStatus: '||x_return_status);
END UPDATE_ROW;
SELECT *
FROM pn_locations_all
WHERE location_id = x_location_id
AND active_start_date = NVL(x_active_start_date_old, g_start_of_time)
AND active_end_date = NVL(x_active_end_date_old, g_end_of_time)
FOR UPDATE OF location_id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Child_for_Dates (
p_location_id IN NUMBER
,p_active_start_date IN DATE
,p_active_end_date IN DATE
,p_active_start_date_old IN DATE
,p_active_end_date_old IN DATE
,p_location_type_lookup_code IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_return_message OUT NOCOPY VARCHAR2
)
IS
CURSOR check_tenancy IS
SELECT 'Y'
FROM pn_tenancies_all pt
WHERE location_id IN (SELECT location_id
FROM pn_locations_all
WHERE active_start_date > p_active_end_date
START WITH location_id = p_location_id
CONNECT BY PRIOR location_id = parent_location_id)
AND NOT EXISTS (SELECT '1'
FROM pn_locations_all pl1
where pl1.location_id = pt.location_id
AND active_start_date <= p_active_end_date)
AND ROWNUM < 2;
SELECT 'Y'
FROM pn_tenancies_all pt
WHERE location_id IN (SELECT location_id
FROM pn_locations_all
WHERE active_end_date < p_active_start_date
START WITH location_id = p_location_id
CONNECT BY PRIOR location_id = parent_location_id)
AND NOT EXISTS (SELECT '1'
FROM pn_locations_all pl1
WHERE pl1.location_id = pt.location_id
AND active_end_date >= p_active_start_date)
AND ROWNUM < 2;
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.update_child_for_dates (+)');
DELETE FROM pn_locations_all
WHERE (location_id, active_start_date,active_end_date) IN
(SELECT location_id, active_start_date,active_end_date
FROM pn_locations_all pl
WHERE active_start_date > p_active_end_date
AND NOT EXISTS (SELECT '1'
FROM pn_space_assign_emp_all
WHERE location_id = pl.location_id
AND p_active_end_date BETWEEN emp_assign_start_date
AND emp_assign_end_date)
AND NOT EXISTS (SELECT '1'
FROM pn_space_assign_cust_all
WHERE location_id = pl.location_id
AND p_active_end_date BETWEEN cust_assign_start_date
AND cust_assign_end_date)
START WITH location_id = p_location_id
CONNECT BY PRIOR location_id = parent_location_id);
UPDATE pn_locations_all
SET active_end_date = p_active_end_date
WHERE active_start_date <= p_active_end_date
AND active_end_date > p_active_end_date
AND location_id IN (SELECT location_id
FROM pn_locations_all
START WITH location_id = p_location_id
CONNECT BY PRIOR location_id = parent_location_id);
DELETE FROM pn_locations_all
WHERE (location_id, active_start_date,active_end_date) IN
(SELECT location_id, active_start_date,active_end_date
FROM pn_locations_all pl
WHERE active_end_date < p_active_start_date
AND NOT EXISTS (SELECT '1'
FROM pn_space_assign_emp_all
WHERE location_id = pl.location_id
AND p_active_start_date BETWEEN emp_assign_start_date
AND emp_assign_end_date)
AND NOT EXISTS (SELECT '1'
FROM pn_space_assign_cust_all
WHERE location_id = pl.location_id
AND p_active_start_date BETWEEN cust_assign_start_date
AND cust_assign_end_date)
START WITH location_id = p_location_id
CONNECT BY PRIOR location_id = parent_location_id);
UPDATE pn_locations_all
SET active_start_date = p_active_start_date
WHERE active_start_date < p_active_start_date
AND active_end_date >= p_active_start_date
AND location_id IN (SELECT location_id
FROM pn_locations_all
START WITH location_id = p_location_id
CONNECT BY PRIOR location_id = parent_location_id);
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.update_child_for_dates (-) ReturnStatus: '||x_return_status);
END update_child_for_dates;
PROCEDURE Update_Status (p_Location_Id Number) IS
BEGIN
UPDATE pn_locations_all
SET Status = 'I'
WHERE Location_Id IN (SELECT Location_Id
FROM pn_locations_all
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = Parent_Location_id);
End Update_Status ;
PROCEDURE update_assignments (
p_location_id IN NUMBER
,p_active_start_date IN DATE
,p_active_end_date IN DATE
,p_active_start_date_old IN DATE
,p_active_end_date_old IN DATE
,x_return_status OUT NOCOPY VARCHAR2
,x_return_message OUT NOCOPY VARCHAR2
)
IS
CURSOR location_cursor IS
SELECT *
FROM pn_locations_all
START WITH location_id = p_location_id
CONNECT BY PRIOR location_id = parent_location_id;
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.update_assignments (+)');
DELETE FROM pn_space_assign_emp_all
WHERE location_id = l_location_rec.location_id
AND emp_assign_start_date > p_active_end_date
AND allocated_area = 0;
UPDATE pn_space_assign_emp_all
SET emp_assign_end_date = p_active_end_date
WHERE location_id = l_location_rec.location_id
AND emp_assign_end_date > p_active_end_date;
DELETE FROM pn_space_assign_cust_all
WHERE location_id = l_location_rec.location_id
AND cust_assign_start_date > p_active_end_date
AND allocated_area = 0;
UPDATE pn_space_assign_cust_all
SET cust_assign_end_date = p_active_end_date
WHERE location_id = l_location_rec.location_id
AND cust_assign_end_date > p_active_end_date;
DELETE FROM pn_space_assign_emp_all
WHERE location_id = l_location_rec.location_id
AND emp_assign_end_date < p_active_start_date
AND allocated_area = 0;
UPDATE pn_space_assign_emp_all
SET emp_assign_start_date = p_active_start_date
WHERE location_id = l_location_rec.location_id
AND emp_assign_start_date < p_active_start_date;
DELETE FROM pn_space_assign_cust_all
WHERE location_id = l_location_rec.location_id
AND cust_assign_end_date < p_active_start_date
AND allocated_area = 0;
UPDATE pn_space_assign_cust_all
SET cust_assign_start_date = p_active_start_date
WHERE location_id = l_location_rec.location_id
AND cust_assign_start_date < p_active_start_date;
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.update_assignments (-) ReturnStatus: '||x_return_status);
pnp_debug_pkg.log('Other error update_assignments' || sqlerrm);
END update_assignments;
SELECT MIN(gross_area) min_gross_area,
location_id location_id
FROM pn_locations_all
WHERE parent_location_id IS NULL
AND active_start_date <= l_act_end_dt
AND active_end_date >= p_act_str_dt
START WITH location_id = p_loc_id
CONNECT BY PRIOR parent_location_id = location_id
GROUP BY location_id;
SELECT rentable_area
FROM pn_locations_all
WHERE location_id = p_loc_id
AND location_type_lookup_code = p_lkp_code
AND active_start_date <= NVL(p_act_end_dt, TO_DATE('12/31/4712','MM/DD/YYYY'))
AND active_end_date >= p_act_str_dt;
IF p_change_mode = 'UPDATE' THEN
OPEN get_old_rent_area;
SELECT location_id,
active_start_date,
active_end_date
FROM pn_locations_all
WHERE active_end_date >= p_str_dt
AND active_start_date <= p_end_dt
AND location_id = p_loc_id
AND ((p_asgn_mode = 'NONE') OR
(p_asgn_mode = 'EMP' AND NVL(assignable_emp, 'Y') = 'Y' AND NVL(assignable_cc, 'Y') = 'Y') OR
(p_asgn_mode = 'CC' AND NVL(assignable_cc, 'Y') = 'Y') OR
(p_asgn_mode = 'CUST' AND NVL(assignable_cust, 'Y') = 'Y')
)
ORDER BY active_start_date;
SELECT 'Y'
FROM DUAL
WHERE NOT EXISTS (SELECT NULL
FROM pn_locations_all
WHERE l_date BETWEEN active_start_date AND active_end_date
AND location_id = p_loc_id
AND ((p_asgn_mode = 'NONE') OR
(p_asgn_mode = 'EMP' AND NVL(assignable_emp, 'Y') = 'Y' AND NVL(assignable_cc, 'Y') = 'Y') OR
(p_asgn_mode = 'CC' AND NVL(assignable_cc, 'Y') = 'Y') OR
(p_asgn_mode = 'CUST' AND NVL(assignable_cust, 'Y') = 'Y')
));
loctn_tab.delete;
SELECT MIN(active_start_date)
,MAX(active_end_date)
FROM pn_locations_all
WHERE location_id = p_loc_id
AND ((p_asgn_mode = 'NONE') OR
(p_asgn_mode = 'EMP' AND NVL(assignable_emp, 'Y') = 'Y' AND NVL(assignable_cc, 'Y') = 'Y') OR
(p_asgn_mode = 'CC' AND NVL(assignable_cc, 'Y') = 'Y') OR
(p_asgn_mode = 'CUST' AND NVL(assignable_cust, 'Y') = 'Y'));
PROCEDURE Update_Locn_Row (
p_loc_recinfo IN pn_locations_all%ROWTYPE
,p_adr_recinfo IN pn_addresses_all%ROWTYPE
,p_assgn_area_chgd_flag IN VARCHAR2
,x_return_status IN OUT NOCOPY VARCHAR2
,x_return_message IN OUT NOCOPY VARCHAR2
)
IS
l_rowid ROWID;
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Update_Locn_Row (+) LocId: '||p_loc_recinfo.location_id
||', LocCd: '||p_loc_recinfo.location_code
||', Type: '||p_loc_recinfo.location_type_lookup_code);
pnt_locations_pkg.Update_Row
(
x_location_id => l_location_id
,x_last_update_date => SYSDATE
,x_last_updated_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id
,x_location_park_id => p_loc_recinfo.location_park_id
,x_location_type_lookup_code => p_loc_recinfo.location_type_lookup_code
,x_space_type_lookup_code => p_loc_recinfo.space_type_lookup_code
,x_function_type_lookup_code => p_loc_recinfo.function_type_lookup_code
,x_standard_type_lookup_code => p_loc_recinfo.standard_type_lookup_code
,x_building => p_loc_recinfo.building
,x_lease_or_owned => p_loc_recinfo.lease_or_owned
,x_class => p_loc_recinfo.class
,x_status_type => p_loc_recinfo.status_type
,x_floor => p_loc_recinfo.floor
,x_office => p_loc_recinfo.office
,x_max_capacity => p_loc_recinfo.max_capacity
,x_optimum_capacity => p_loc_recinfo.optimum_capacity
,x_gross_area => p_loc_recinfo.gross_area
,x_rentable_area => p_loc_recinfo.rentable_area
,x_usable_area => p_loc_recinfo.usable_area
,x_assignable_area => p_loc_recinfo.assignable_area
,x_common_area => p_loc_recinfo.common_area
,x_suite => p_loc_recinfo.suite
,x_allocate_cost_center_code => p_loc_recinfo.allocate_cost_center_code
,x_uom_code => p_loc_recinfo.uom_code
,x_description => p_loc_recinfo.description
,x_parent_location_id => p_loc_recinfo.parent_location_id
,x_interface_flag => p_loc_recinfo.interface_flag
,x_status => p_loc_recinfo.status
,x_property_id => p_loc_recinfo.property_id
,x_common_area_flag => p_loc_recinfo.common_area_flag
,x_active_start_date => p_loc_recinfo.active_start_date
,x_active_end_date => p_loc_recinfo.active_end_date
,x_bookable_flag => p_loc_recinfo.bookable_flag
,x_occupancy_status_code => p_loc_recinfo.occupancy_status_code
,x_assignable_emp => p_loc_recinfo.assignable_emp
,x_assignable_cc => p_loc_recinfo.assignable_cc
,x_assignable_cust => p_loc_recinfo.assignable_cust
,x_disposition_code => p_loc_recinfo.disposition_code
,x_acc_treatment_code => p_loc_recinfo.acc_treatment_code
,x_attribute_category => p_loc_recinfo.attribute_category
,x_attribute1 => p_loc_recinfo.attribute1
,x_attribute2 => p_loc_recinfo.attribute2
,x_attribute3 => p_loc_recinfo.attribute3
,x_attribute4 => p_loc_recinfo.attribute4
,x_attribute5 => p_loc_recinfo.attribute5
,x_attribute6 => p_loc_recinfo.attribute6
,x_attribute7 => p_loc_recinfo.attribute7
,x_attribute8 => p_loc_recinfo.attribute8
,x_attribute9 => p_loc_recinfo.attribute9
,x_attribute10 => p_loc_recinfo.attribute10
,x_attribute11 => p_loc_recinfo.attribute11
,x_attribute12 => p_loc_recinfo.attribute12
,x_attribute13 => p_loc_recinfo.attribute13
,x_attribute14 => p_loc_recinfo.attribute14
,x_attribute15 => p_loc_recinfo.attribute15
,x_address_id => l_address_id
,x_addr_last_update_date => SYSDATE
,x_addr_last_updated_by => fnd_globaL.user_id
,x_addr_last_update_login => fnd_global.login_id
,x_address_line1 => p_adr_recinfo.address_line1
,x_address_line2 => p_adr_recinfo.address_line2
,x_address_line3 => p_adr_recinfo.address_line3
,x_address_line4 => p_adr_recinfo.address_line4
,x_county => p_adr_recinfo.county
,x_city => p_adr_recinfo.city
,x_state => p_adr_recinfo.state
,x_province => p_adr_recinfo.province
,x_zip_code => p_adr_recinfo.zip_code
,x_country => p_adr_recinfo.country
,x_territory_id => p_adr_recinfo.territory_id
,x_addr_attribute_category => p_adr_recinfo.addr_attribute_category
,x_addr_attribute1 => p_adr_recinfo.addr_attribute1
,x_addr_attribute2 => p_adr_recinfo.addr_attribute2
,x_addr_attribute3 => p_adr_recinfo.addr_attribute3
,x_addr_attribute4 => p_adr_recinfo.addr_attribute4
,x_addr_attribute5 => p_adr_recinfo.addr_attribute5
,x_addr_attribute6 => p_adr_recinfo.addr_attribute6
,x_addr_attribute7 => p_adr_recinfo.addr_attribute7
,x_addr_attribute8 => p_adr_recinfo.addr_attribute8
,x_addr_attribute9 => p_adr_recinfo.addr_attribute9
,x_addr_attribute10 => p_adr_recinfo.addr_attribute10
,x_addr_attribute11 => p_adr_recinfo.addr_attribute11
,x_addr_attribute12 => p_adr_recinfo.addr_attribute12
,x_addr_attribute13 => p_adr_recinfo.addr_attribute13
,x_addr_attribute14 => p_adr_recinfo.addr_attribute14
,x_addr_attribute15 => p_adr_recinfo.addr_attribute15
,x_assgn_area_chgd_flag => p_assgn_area_chgd_flag
,x_return_status => x_return_status
,x_return_message => x_return_message
,x_source => p_loc_recinfo.source
);
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Update_Locn_Row (-) ReturnStatus: '||x_return_status);
END Update_Locn_Row;
PROCEDURE Insert_Locn_Row (
p_loc_recinfo IN pn_locations_all%ROWTYPE
,p_adr_recinfo IN pn_addresses_all%ROWTYPE
,p_change_mode IN VARCHAR2
,x_return_status IN OUT NOCOPY VARCHAR2
,x_return_message IN OUT NOCOPY VARCHAR2
)
IS
l_rowid ROWID;
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Insert_Locn_Row (+) LocId: '||p_loc_recinfo.location_id
||', LocCd: '||p_loc_recinfo.location_code
||', Type: '||p_loc_recinfo.location_type_lookup_code);
pnt_locations_pkg.Insert_Row
(
x_rowid => l_rowid
,x_last_update_date => SYSDATE
,x_last_updated_by => fnd_global.user_id
,x_creation_date => SYSDATE
,x_created_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id
,x_location_id => l_location_id
,x_org_id => p_loc_recinfo.org_id
,x_location_park_id => p_loc_recinfo.location_park_id
,x_location_type_lookup_code => p_loc_recinfo.location_type_lookup_code
,x_space_type_lookup_code => p_loc_recinfo.space_type_lookup_code
,x_function_type_lookup_code => p_loc_recinfo.function_type_lookup_code
,x_standard_type_lookup_code => p_loc_recinfo.standard_type_lookup_code
,x_location_alias => p_loc_recinfo.location_alias
,x_location_code => p_loc_recinfo.location_code
,x_building => p_loc_recinfo.building
,x_lease_or_owned => p_loc_recinfo.lease_or_owned
,x_class => p_loc_recinfo.class
,x_status_type => p_loc_recinfo.status_type
,x_floor => p_loc_recinfo.floor
,x_office => p_loc_recinfo.office
,x_max_capacity => p_loc_recinfo.max_capacity
,x_optimum_capacity => p_loc_recinfo.optimum_capacity
,x_gross_area => p_loc_recinfo.gross_area
,x_rentable_area => p_loc_recinfo.rentable_area
,x_usable_area => p_loc_recinfo.usable_area
,x_assignable_area => p_loc_recinfo.assignable_area
,x_common_area => p_loc_recinfo.common_area
,x_suite => p_loc_recinfo.suite
,x_allocate_cost_center_code => p_loc_recinfo.allocate_cost_center_code
,x_uom_code => p_loc_recinfo.uom_code
,x_description => p_loc_recinfo.description
,x_parent_location_id => p_loc_recinfo.parent_location_id
,x_interface_flag => p_loc_recinfo.interface_flag
,x_request_id => p_loc_recinfo.request_id
,x_program_id => p_loc_recinfo.program_id
,x_program_application_id => p_loc_recinfo.program_application_id
,x_program_update_date => p_loc_recinfo.program_update_date
,x_status => p_loc_recinfo.status
,x_property_id => p_loc_recinfo.property_id
,x_common_area_flag => p_loc_recinfo.common_area_flag
,x_active_start_date => p_loc_recinfo.active_start_date
,x_active_end_date => p_loc_recinfo.active_end_date
,x_bookable_flag => p_loc_recinfo.bookable_flag
,x_occupancy_status_code => p_loc_recinfo.occupancy_status_code
,x_assignable_emp => p_loc_recinfo.assignable_emp
,x_assignable_cc => p_loc_recinfo.assignable_cc
,x_assignable_cust => p_loc_recinfo.assignable_cust
,x_disposition_code => p_loc_recinfo.disposition_code
,x_acc_treatment_code => p_loc_recinfo.acc_treatment_code
,x_attribute_category => p_loc_recinfo.attribute_category
,x_attribute1 => p_loc_recinfo.attribute1
,x_attribute2 => p_loc_recinfo.attribute2
,x_attribute3 => p_loc_recinfo.attribute3
,x_attribute4 => p_loc_recinfo.attribute4
,x_attribute5 => p_loc_recinfo.attribute5
,x_attribute6 => p_loc_recinfo.attribute6
,x_attribute7 => p_loc_recinfo.attribute7
,x_attribute8 => p_loc_recinfo.attribute8
,x_attribute9 => p_loc_recinfo.attribute9
,x_attribute10 => p_loc_recinfo.attribute10
,x_attribute11 => p_loc_recinfo.attribute11
,x_attribute12 => p_loc_recinfo.attribute12
,x_attribute13 => p_loc_recinfo.attribute13
,x_attribute14 => p_loc_recinfo.attribute14
,x_attribute15 => p_loc_recinfo.attribute15
,x_address_id => l_address_id
,x_address_line1 => p_adr_recinfo.address_line1
,x_address_line2 => p_adr_recinfo.address_line2
,x_address_line3 => p_adr_recinfo.address_line3
,x_address_line4 => p_adr_recinfo.address_line4
,x_county => p_adr_recinfo.county
,x_city => p_adr_recinfo.city
,x_state => p_adr_recinfo.state
,x_province => p_adr_recinfo.province
,x_zip_code => p_adr_recinfo.zip_code
,x_country => p_adr_recinfo.country
,x_territory_id => p_adr_recinfo.territory_id
,x_addr_last_update_date => SYSDATE
,x_addr_last_updated_by => fnd_globaL.user_id
,x_addr_creation_date => SYSDATE
,x_addr_created_by => fnd_global.user_id
,x_addr_last_update_login => fnd_global.login_id
,x_addr_attribute_category => p_adr_recinfo.addr_attribute_category
,x_addr_attribute1 => p_adr_recinfo.addr_attribute1
,x_addr_attribute2 => p_adr_recinfo.addr_attribute2
,x_addr_attribute3 => p_adr_recinfo.addr_attribute3
,x_addr_attribute4 => p_adr_recinfo.addr_attribute4
,x_addr_attribute5 => p_adr_recinfo.addr_attribute5
,x_addr_attribute6 => p_adr_recinfo.addr_attribute6
,x_addr_attribute7 => p_adr_recinfo.addr_attribute7
,x_addr_attribute8 => p_adr_recinfo.addr_attribute8
,x_addr_attribute9 => p_adr_recinfo.addr_attribute9
,x_addr_attribute10 => p_adr_recinfo.addr_attribute10
,x_addr_attribute11 => p_adr_recinfo.addr_attribute11
,x_addr_attribute12 => p_adr_recinfo.addr_attribute12
,x_addr_attribute13 => p_adr_recinfo.addr_attribute13
,x_addr_attribute14 => p_adr_recinfo.addr_attribute14
,x_addr_attribute15 => p_adr_recinfo.addr_attribute15
,x_change_mode => p_change_mode
,x_return_status => x_return_status
,x_return_message => x_return_message
,x_source => p_loc_recinfo.source
);
pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Insert_Locn_Row (-) ReturnStatus: '||x_return_status);
END Insert_Locn_Row;
SELECT location_id
INTO l_location_id
FROM pn_locations_all
WHERE location_code = p_location_code
AND location_type_lookup_code = p_loctn_type_lookup_code
AND org_id = p_org_id
AND ROWNUM = 1;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM pn_locations_all pnl
WHERE pnl.location_code = x_location_code
AND pnl.active_start_date = x_active_start_date
AND pnl.active_end_date = NVL(x_active_end_date,g_end_of_time)
AND ((x_location_id IS NULL) OR (pnl.location_id <> x_location_id))
AND pnl.org_id = x_org_id
);
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM pn_locations_all pnl
WHERE UPPER(pnl.building) = UPPER(x_building)
AND ((x_location_id IS NULL) OR (pnl.location_id <> x_location_id))
AND pnl.active_start_date <= x_active_start_date
AND pnl.active_end_date >= x_active_end_date
AND pnl.org_id = x_org_id
);
SELECT loc.location_id AS location_id
FROM pn_locations_all loc
WHERE loc.LOCATION_TYPE_LOOKUP_CODE = p_location_type_lookup_code
AND loc.location_alias = p_location_alias
AND loc.location_id <> NVL(p_location_id,-1)
AND loc.org_id = p_org_id;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM pn_locations_all pnl
WHERE pnl.location_alias = x_location_alias
AND location_type_lookup_code = x_location_type_lookup_code
AND ((x_location_id IS NULL) OR (pnl.location_id <> x_location_id))
AND pnl.parent_location_id = x_parent_location_id
AND pnl.active_start_date = x_active_start_date
AND pnl.active_end_date = NVL(x_active_end_date,g_end_of_time)
AND pnl.org_id = x_org_id
);