[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT actual_amount, accounted_amount, currency_code, due_date, rate, org_id
FROM pn_payment_items_all
WHERE payment_schedule_id = p_payment_schedule_id
AND payment_item_type_lookup_code = 'CASH';
SELECT NVL(SUM(ppi.actual_amount),0)
INTO l_totalTermAmt
FROM pn_payment_items_all ppi
WHERE ppi.PAYMENT_TERM_ID = p_paymentTermId
AND ppi.payment_item_type_lookup_code = 'CASH';
(SELECT NVL(SUM(Allocated_Area), 0) AS area
FROM PN_SPACE_ASSIGN_EMP_ALL
WHERE Cost_Center_Code = p_Cost_Center
AND emp_assign_start_date <= p_As_Of_Date
AND NVL(emp_assign_end_date, l_date) >= p_As_Of_Date
AND Location_Id IN (
SELECT Location_Id
FROM PN_LOCATIONS_ALL
WHERE Location_Type_Lookup_Code = 'OFFICE'
AND p_As_Of_Date BETWEEN active_start_date AND active_end_date
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = Parent_Location_Id
AND p_As_Of_Date BETWEEN PRIOR active_start_date AND
PRIOR active_end_date)
);
(SELECT NVL(SUM(Allocated_Area), 0) AS area
FROM PN_SPACE_ASSIGN_CUST_ALL
WHERE cust_assign_start_date <= p_As_Of_Date
AND NVL(cust_assign_end_date, l_date) >= p_As_Of_Date
AND Location_Id IN (
SELECT Location_Id
FROM PN_LOCATIONS_ALL
WHERE Location_Type_Lookup_Code = 'OFFICE'
AND p_As_Of_Date BETWEEN active_start_date AND active_end_date
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = Parent_Location_Id
AND p_As_Of_Date BETWEEN PRIOR active_start_date AND PRIOR active_end_date)
);
(SELECT NVL(SUM(assignable_area), 0) AS Area
FROM pn_locations_all
WHERE location_type_lookup_code = p_location_type
AND status = 'A'
AND p_as_of_date BETWEEN active_start_date AND active_end_date
START WITH location_id = p_Location_Id
CONNECT BY PRIOR location_id = parent_location_id
AND p_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date
);
(SELECT NVL(SUM(allocated_area), 0) AS Area
FROM pn_space_assign_emp_all
WHERE emp_assign_start_date <= p_as_of_date
AND NVL(emp_assign_end_date, l_date) >= p_as_of_date
AND location_id IN (SELECT Location_Id
FROM pn_locations_all
WHERE location_type_lookup_code = p_location_type
AND Status = 'A'
AND p_as_of_date BETWEEN active_start_date AND active_end_date
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = parent_location_id
AND p_as_of_date BETWEEN prior active_start_date AND
PRIOR active_end_date
)
);
SELECT Location_Id
FROM pn_locations_all
WHERE location_type_lookup_code = p_location_type
AND Status = 'A'
AND p_as_of_date BETWEEN active_start_date AND active_end_date
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = parent_location_id
AND p_as_of_date BETWEEN prior active_start_date AND
PRIOR active_end_date ;
(SELECT NVL(SUM(allocated_area), 0) AS Area
FROM pn_space_assign_cust_all
WHERE cust_assign_start_date <= p_As_of_date
AND NVL(cust_assign_end_date, l_date) >= p_As_of_date
AND location_Id IN (SELECT location_id
FROM pn_locations_all
WHERE location_type_lookup_code = p_location_type
AND status = 'A'
AND p_as_of_date BETWEEN active_start_date AND active_end_date
START WITH location_id = p_location_id
CONNECT BY PRIOR location_id = parent_location_id
AND p_as_of_date between PRIOR active_start_date AND
PRIOR active_end_date)
);
(SELECT NVL(SUM(assignable_area), 0) AS Area
FROM pn_locations_all
WHERE location_id = p_location_id
AND p_as_of_date BETWEEN active_start_date AND active_end_date
AND status = 'A'
);
(SELECT NVL(SUM(e.allocated_area), 0) AS Area
FROM pn_space_assign_emp_all e
WHERE E.emp_assign_start_date >= p_as_of_date
AND NVL(e.emp_assign_end_date, l_date) >= p_as_of_date
AND e.location_id = p_location_id
AND EXISTS (SELECT NULL
FROM pn_locations_all l
WHERE l.status = 'A'
AND l.location_id = p_Location_Id)
);
(SELECT NVL(SUM(c.allocated_area), 0) AS Area
FROM pn_space_assign_cust_all c
WHERE c.cust_assign_start_date <= p_as_of_date
AND NVL(c.cust_assign_end_date, l_date) >= p_as_of_date
AND c.location_id = p_location_id
AND EXISTS (SELECT NULL
FROM pn_locations_all l
WHERE l.status = 'A'
AND l.location_id = p_location_id)
);
l_query := 'SELECT NVL(SUM(allocated_area), 0) AS Area ';
| SELECT statements to SELECT only the active
| locations
| 30-OCT-02 Satish o Access _all table for performance issues.
|
| 31-OCT-01 graghuna o added p_as_of_date for Location Date-Effectivity.
| 20-OCT-03 ftanudja o removed nvl's for locations tbl. 3197410.
| 18-FEB-04 abanerje o Handled NO_DATA_FOUND exception to return null.
| The select statment has been changed to a
| cursor now.
| Bug #3384965.
+===========================================================================*/
FUNCTION get_floors ( p_Location_Id NUMBER ,
p_as_of_date DATE )
RETURN NUMBER IS
l_Location_Type pn_locations.location_type_lookup_code%type;
(SELECT COUNT(pn_locations_all.floor) AS floor_count
FROM pn_locations_all
WHERE Location_Type_Lookup_Code = p_location_type
AND Status = 'A'
AND p_as_of_date BETWEEN active_start_date AND active_end_date
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = Parent_Location_Id
AND p_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date
);
| SELECT statements to SELECT only the active
|
| 30-OCT-2002 Satish o Access _all table for performance issues.
|
| 31-OCT-2001 graghuna o added p_as_of_date for Location Date-Effectivity.
| 10-JUL-2003 Satish o Added for 'FLOOR/PARCEL'
| 20-OCT-2003 ftanudja o removed nvl's for locations tbl. 3197410.
| 05-MAY-2004 ftanudja o handle if location type is null.
+===========================================================================*/
FUNCTION get_offices ( p_Location_Id NUMBER ,
p_as_of_date IN DATE )
RETURN NUMBER IS
l_Location_Type pn_locations.location_type_lookup_code%type;
SELECT COUNT(office)
INTO l_offices
FROM pn_locations_all
WHERE Location_Type_Lookup_Code = l_location_type --'OFFICE'
AND Status = 'A' --BUG#2168485
AND l_as_of_date BETWEEN active_start_date AND active_end_date
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = Parent_Location_Id
AND l_as_of_date between prior active_start_date and
PRIOR active_end_date;
(SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
FROM pn_space_assign_emp_all
WHERE location_id IN (SELECT a.location_id
FROM pn_locations_all a
WHERE p_As_Of_Date BETWEEN active_start_date AND
active_end_date
START WITH a.location_id = p_location_id
CONNECT BY PRIOR a.location_id = a.parent_location_id
AND p_as_of_date
BETWEEN PRIOR active_start_date AND PRIOR active_end_date)
AND p_as_of_date BETWEEN emp_assign_start_date AND
NVL(emp_assign_end_date, g_end_of_time)
);*/
SELECT Location_Id
FROM pn_locations_all
WHERE p_as_of_date BETWEEN active_start_date AND
active_end_date
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = parent_location_id
AND p_as_of_date BETWEEN prior active_start_date AND
PRIOR active_end_date ;
(SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
FROM pn_space_assign_cust_all
WHERE location_id IN (SELECT a.location_id
FROM pn_locations_all a
WHERE p_as_of_date BETWEEN active_start_date AND
active_end_date
START WITH a.location_id = p_location_id
CONNECT BY PRIOR a.location_id = a.parent_location_id
AND p_as_of_date
BETWEEN PRIOR active_start_date AND PRIOR active_end_date)
AND p_as_of_date BETWEEN cust_assign_start_date AND
NVL(cust_assign_end_date, g_end_of_time)
);
(SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
FROM pn_space_assign_emp_all
WHERE location_id = p_location_id
AND p_as_of_date BETWEEN emp_assign_start_date AND
NVL(emp_assign_end_date, g_end_of_time)
);
(SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
FROM pn_space_assign_cust_all
WHERE location_id = p_location_id
AND p_as_of_date BETWEEN cust_assign_start_date AND
NVL(cust_assign_end_date, g_end_of_time)
);
l_query := 'SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area ';
| All the select statements have been
| converted to cursors. The l_location_type
| is checked for null to return 0 .
| Bug #3384965.
+===========================================================================*/
FUNCTION get_total_leased_area (
p_leaseId IN NUMBER,
p_as_of_date IN DATE ) RETURN NUMBER
IS
l_totalArea NUMBER := 0;
(SELECT NVL(SUM(pnl.RENTABLE_AREA),0) AS Area
FROM pn_locations_all pnl,
pn_tenancies_all pnt
WHERE pnt.lease_id = p_leaseId
AND pnt.status = 'A'
AND pnl.location_id = pnt.location_id
AND p_as_of_date BETWEEN pnl.active_start_date AND pnl.active_end_date
);
SELECT status
INTO l_leaseStatus
FROM pn_leases_all
WHERE lease_id = p_leaseId;
SELECT count(*)
INTO l_count
FROM pn_addresses_all
WHERE zip_code = p_zip_code
AND address_id <= p_address_id ;
SELECT NVL(count (*), 0)
INTO l_occupancyCountEmp
FROM pn_space_assign_emp_all
WHERE location_id = p_locationId
AND emp_assign_start_date <= l_as_of_date
AND NVL(emp_assign_end_date, l_date) >= l_as_of_date;
SELECT NVL(count (*), 0)
INTO l_occupancyCountCust
FROM pn_space_assign_cust_all
WHERE location_id = p_locationId
AND cust_assign_start_date <= l_as_of_date
AND NVL(cust_assign_end_date, l_date) >= l_as_of_date;
SELECT gl_sob.chart_of_accounts_id
FROM gl_sets_of_books gl_sob
WHERE gl_sob.set_of_books_id = l_set_of_books_id;
'SELECT ' || l_column_name || '
FROM gl_code_combinations,
per_employees_current_x
WHERE default_code_combination_id = code_combination_id
AND employee_id = :l_employee_id';
SELECT 'x'
INTO l_dummy
FROM fnd_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code ;
SELECT 'x'
INTO l_dummy
FROM fnd_territories_vl
WHERE territory_code = p_country;
SELECT 'x'
INTO l_dummy
FROM fnd_lookups
WHERE lookup_type = 'PN_UNITS_OF_MEASURE'
AND lookup_code = p_uom_code;
SELECT 'x'
INTO l_dummy
FROM mtl_units_of_measure
WHERE uom_class = 'Area'
AND uom_code = p_uom_code;
SELECT 'x'
INTO l_dummy
FROM per_employees_current_x
WHERE employee_id = p_employee_id;
SELECT chart_of_accounts_id
INTO coa_id
FROM gl_sets_of_books
WHERE set_of_books_id = l_set_of_books_id;
'select account_type
from gl_code_combinations where '||l_column_name||' = :l_cost_center';
SELECT 'X'
INTO l_Dummy
FROM pn_locations_all
WHERE Status = 'A'
AND Location_Id = p_Location_Id
AND l_as_of_date BETWEEN active_start_date AND active_end_date;
SELECT pn_locations_s.NEXTVAL
INTO l_seqnum
FROM DUAL;
SELECT PN_SPACE_ASSIGN_EMP_S.NEXTVAL
INTO l_seqnum
FROM dual;
SELECT glp.start_date
INTO l_start_date
FROM gl_sets_of_books gsob,
gl_periods glp
WHERE gsob.period_set_name = glp.period_set_name
AND gsob.set_of_books_id = l_set_of_books_id
AND glp.period_name = p_period_name;
SELECT 1
INTO l_retnum_emp
FROM pn_space_assign_emp_all
WHERE location_id = p_location_id
AND emp_assign_start_date <= l_as_of_date
AND NVL(emp_assign_end_date, l_date) >= l_as_of_date
AND rownum = 1 ;
SELECT 1
INTO l_retnum_cust
FROM pn_space_assign_cust_all
WHERE location_id = p_location_id
AND cust_assign_start_date <= l_as_of_date
AND NVL(cust_assign_end_date, l_date) >= l_as_of_date
AND rownum = 1 ;
SELECT location_code
FROM pn_locations_all
WHERE location_id = p_location_id
AND p_as_of_date BETWEEN active_start_date AND active_end_date;
SELECT location_code
FROM pn_locations_all
WHERE location_id = p_location_id
AND ROWNUM < 2;
SELECT location_type_lookup_code
FROM pn_locations_all
WHERE location_id = p_location_id
AND l_as_of_date BETWEEN active_start_date AND active_end_date;
SELECT location_type_lookup_code
FROM pn_locations_all
WHERE location_id = p_location_id
AND ROWNUM < 2;
SELECT MAX(schedule_date)
INTO l_date
FROM pn_payment_schedules_all
WHERE payment_status_lookup_code = 'APPROVED'
AND lease_id = p_leaseId;
SELECT TO_NUMBER(DECODE( p_freq_char
, 'OT', 0
, 'MON', 1
, 'QTR', 3
, 'SA', 6
, 'YR', 12
, -1)) AS freq_num
FROM DUAL;
SELECT 1
FROM dual
WHERE EXISTS
(SELECT 1
FROM pn_payment_schedules_all s,
pn_payment_items_all i
WHERE i.payment_term_id IN
(SELECT payment_term_id
FROM pn_payment_terms_all
WHERE lease_id = p_lease_ID
AND start_date > p_termination_date
AND normalize = 'Y')
AND i.payment_schedule_id = s.payment_schedule_id
AND s.payment_status_lookup_code = 'APPROVED'
AND s.lease_id = p_lease_ID);
SELECT MAX(s.schedule_date) AS schedule_date
FROM pn_payment_schedules_all s,
pn_payment_items_all i
WHERE i.payment_term_id IN
(SELECT t.payment_term_id
FROM pn_payment_terms_all t
WHERE t.lease_id = p_lease_ID
AND t.normalize = 'Y')
AND i.payment_schedule_id = s.payment_schedule_id
AND s.payment_status_lookup_code = 'APPROVED'
AND s.lease_id = p_lease_ID;
SELECT MAX(s.schedule_date) AS schedule_date
FROM pn_payment_schedules_all s,
pn_payment_items_all i
WHERE i.payment_term_id IN
(SELECT t.payment_term_id
FROM pn_payment_terms_all t
WHERE t.lease_id = p_lease_ID
AND t.frequency_code = 'OT'
AND NVL(t.normalize,'N')='N')
AND i.payment_schedule_id = s.payment_schedule_id
AND s.payment_status_lookup_code = 'APPROVED'
AND s.lease_id = p_lease_ID;
SELECT MAX(s.schedule_date) AS schedule_date
FROM pn_payment_schedules_all s,
pn_payment_items_all i
WHERE i.payment_term_id IN
(SELECT t.payment_term_id
FROM pn_payment_terms_all t
WHERE t.lease_id = p_lease_ID
AND t.frequency_code = 'MON'
AND NVL(t.normalize,'N')='N')
AND i.payment_schedule_id = s.payment_schedule_id
AND s.payment_status_lookup_code = 'APPROVED'
AND s.lease_id = p_lease_ID;
SELECT MAX(s.schedule_date) AS schedule_date
FROM pn_payment_schedules_all s,
pn_payment_items_all i
WHERE i.payment_term_id IN
(SELECT t.payment_term_id
FROM pn_payment_terms_all t
WHERE t.lease_id = p_lease_ID
AND t.frequency_code IN ('QTR', 'SA', 'YR')
AND NVL(t.normalize,'N')='N')
AND i.payment_schedule_id = s.payment_schedule_id
AND s.payment_status_lookup_code = 'APPROVED'
AND s.lease_id = p_lease_ID;
SELECT payment_term_ID
,start_date
,end_date
,schedule_day
,frequency_code
FROM pn_payment_terms_all
WHERE lease_ID = p_lease_ID
AND payment_term_ID IN
(SELECT DISTINCT i.payment_term_ID
FROM pn_payment_items_all i
,pn_payment_schedules_all s
WHERE s.schedule_date = p_sched_date
AND s.lease_ID = p_lease_ID
AND s.payment_status_lookup_code = 'APPROVED'
AND i.payment_schedule_ID = s.payment_schedule_ID);
SELECT MAX(s.schedule_date) AS schedule_date
FROM pn_payment_schedules_all s,
pn_payment_items_all i
WHERE i.payment_term_id = p_term_ID
AND i.payment_schedule_id = s.payment_schedule_id
AND s.payment_status_lookup_code = 'APPROVED'
AND s.lease_id = p_lease_ID;
SELECT payment_term_ID
,start_date
,end_date
,schedule_day
,frequency_code
FROM pn_payment_terms_all
WHERE payment_term_ID = p_term_ID;
SELECT 1
FROM pn_payment_terms_all
WHERE NVL(normalize,'N') = 'Y'
AND lease_id = p_lease_id;
SELECT LAST_DAY(MAX(schedule_date)) AS last_appr_schd_month_end_dt
FROM pn_payment_schedules_all
WHERE payment_status_lookup_code = 'APPROVED'
AND lease_id = p_lease_id;
SELECT MAX(schedule_date) AS item_end_date
FROM pn_payment_items_all item,
pn_payment_terms_all term,
pn_payment_schedules_all schd
WHERE term.payment_term_id = p_term_id
AND item.payment_term_id = term.payment_term_id
AND item.payment_schedule_id = schd.payment_schedule_id
AND item.payment_item_type_lookup_code = 'CASH'
AND item.actual_amount <>0
AND schd.payment_status_lookup_code = 'APPROVED';
SELECT LAST_DAY(MAX(schedule_date)) AS item_end_date
FROM pn_payment_items_all item,
pn_payment_terms_all term,
pn_payment_schedules_all schd
WHERE term.payment_term_id = p_term_id
AND item.payment_term_id = term.payment_term_id
AND item.payment_schedule_id = schd.payment_schedule_id
AND item.payment_item_type_lookup_code = 'CASH'
AND item.actual_amount <>0
AND schd.payment_status_lookup_code = 'APPROVED';
SELECT ADD_MONTHS(MAX(schedule_date),3) - 1 AS item_end_date
FROM pn_payment_items_all item,
pn_payment_terms_all term,
pn_payment_schedules_all schd
WHERE term.payment_term_id = p_term_id
AND item.payment_term_id = term.payment_term_id
AND item.payment_schedule_id = schd.payment_schedule_id
AND item.payment_item_type_lookup_code = 'CASH'
AND item.actual_amount <>0
AND schd.payment_status_lookup_code = 'APPROVED';
SELECT ADD_MONTHS(MAX(schedule_date),6) - 1 AS item_end_date
FROM pn_payment_items_all item,
pn_payment_terms_all term,
pn_payment_schedules_all schd
WHERE term.payment_term_id = p_term_id
AND item.payment_term_id = term.payment_term_id
AND item.payment_schedule_id = schd.payment_schedule_id
AND item.payment_item_type_lookup_code = 'CASH'
AND item.actual_amount <>0
AND schd.payment_status_lookup_code = 'APPROVED';
SELECT ADD_MONTHS(MAX(schedule_date),12) - 1 AS item_end_date
FROM pn_payment_items_all item,
pn_payment_terms_all term,
pn_payment_schedules_all schd
WHERE term.payment_term_id = p_term_id
AND item.payment_term_id = term.payment_term_id
AND item.payment_schedule_id = schd.payment_schedule_id
AND item.payment_item_type_lookup_code = 'CASH'
AND item.actual_amount <>0
AND schd.payment_status_lookup_code = 'APPROVED';
SELECT frequency_code,
payment_term_id,
index_period_id
FROM pn_payment_terms_all
WHERE lease_id = p_lease_id
AND NVL(status,'APPROVED') = 'APPROVED';
SELECT MAX(change_commencement_date)
INTO l_date
FROM pn_lease_changes_all
WHERE lease_id = p_leaseId;
| 26-OCT-2004 Satish Tripathi Fixed for BUG# 3927904; select job as of sysdate.
SELECT ppf.person_id,
ppf.effective_start_date,
ppf.effective_end_date,
paf.assignment_id,
ppf.last_name last_name,
ppf.employee_number employee_number,
ppf.email_address email_address,
ppf.first_name first_name,
ppf.full_name full_name,
ppf.person_type_id,
ppttl.user_person_type employee_type,
pp.phone_number phone_number,
paf.position_id position_id,
hr_general.decode_position_latest_name(paf.position_id) position,
paf.job_id job_id,
pj.name job ,
paf.organization_id organization_id,
hou.name organization,
paf.employment_category employment_category,
hrl.meaning employment_category_meaning
FROM per_jobs pj,
hr_organization_units hou,
hr_lookups hrl,
per_phones pp,
per_person_types_tl ppttl,
per_all_assignments_f paf,
per_all_people_f ppf
WHERE ppf.person_id = p_personId
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND paf.person_id = ppf.person_id
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.primary_flag = 'Y'
AND pp.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND pp.parent_id(+) = ppf.person_id
AND ppf.effective_start_date BETWEEN pp.date_FROM(+)
AND NVL(pp.date_to(+) ,TO_DATE('12/31/4712','MM/DD/YYYY'))
AND pp.phone_type(+) = 'W1'
AND ppttl.person_type_id = ppf.person_type_id
AND ppttl.language = userenv('LANG')
AND hou.organization_id = paf.organization_id -- no need of outer join it's mAND. col.
AND pj.job_id (+) = paf.job_id
AND hrl.lookup_type (+) = 'EMP_CAT'
AND hrl.lookup_code (+) = paf.employment_category;
SELECT pa.segment1,
hou.name
INTO l_emp_pr_data
FROM PA_PROJECTS_ALL pa,
HR_ORGANIZATION_UNITS hou
WHERE pa.project_id = p_projectId
AND hou.organization_id = pa.carrying_out_organization_id;
SELECT pat.task_name
INTO l_emp_tr_data
FROM PA_TASKS pat
WHERE pat.task_id = p_taskId;
| All the select statements have been
| converted to cursors. The l_location_type
| is checked for null to return 0.
| Bug #3384965.
+===========================================================================*/
FUNCTION get_floor_vacancy ( p_Location_Id NUMBER,
p_as_of_date DATE) RETURN NUMBER IS
l_Location_Type pn_locations.location_type_lookup_code%type;
| All the select statements have been
| converted to cursors. The l_location_type
| is checked for null to return 0.
| Bug #3384965.
+===========================================================================*/
FUNCTION get_office_vacancy ( p_Location_Id NUMBER ,
p_as_of_date IN DATE ) RETURN NUMBER IS
l_Location_Type pn_locations.location_type_lookup_code%type;
(SELECT NVL((max_capacity), 0) AS vacancy
FROM pn_locations_all
WHERE Location_Type_Lookup_Code = p_location_type
AND p_as_of_date BETWEEN active_start_date AND active_end_date
AND Location_Id = p_Location_Id
);
SELECT 'Y'
FROM pn_space_assign_emp_all
WHERE emp_assign_start_date > l_date
--Bug#5959164 AND allocated_area_pct > 0
AND location_id IN (SELECT location_id
FROM pn_locations_all
WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
START WITH location_id = p_Location_Id
CONNECT BY PRIOR location_id = parent_location_id
AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
SELECT 'Y'
FROM pn_space_assign_emp_all
WHERE l_date BETWEEN emp_assign_start_date AND emp_assign_end_date
AND location_id IN (SELECT location_id
FROM pn_locations_all
WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
START WITH location_id = p_Location_Id
CONNECT BY PRIOR location_id = parent_location_id
AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
SELECT 'Y'
FROM pn_space_assign_emp_all
WHERE l_date >= emp_assign_start_date AND emp_assign_end_date IS NULL -- for open assignments time
AND location_id IN (SELECT location_id
FROM pn_locations_all
WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
START WITH location_id = p_Location_Id
CONNECT BY PRIOR location_id = parent_location_id
AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
SELECT 'Y'
FROM pn_space_assign_cust_all
WHERE cust_assign_start_date > l_date
--Bug#5959164 AND allocated_area_pct > 0
AND location_id IN (SELECT location_id
FROM pn_locations_all
WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
START WITH location_id = p_Location_Id
CONNECT BY PRIOR location_id = parent_location_id
AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
SELECT 'Y'
FROM pn_space_assign_cust_all
WHERE l_date BETWEEN cust_assign_start_date AND cust_assign_end_date
AND location_id IN (SELECT location_id
FROM pn_locations_all
WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
START WITH location_id = p_Location_Id
CONNECT BY PRIOR location_id = parent_location_id
AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
SELECT 'Y'
FROM pn_space_assign_cust_all
WHERE l_date >= cust_assign_start_date AND cust_assign_end_date IS NULL -- for open assignments time
AND location_id IN (SELECT location_id
FROM pn_locations_all
WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
START WITH location_id = p_Location_Id
CONNECT BY PRIOR location_id = parent_location_id
AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
| All the select statements have been
| converted to cursors. The l_location_type
| is checked for null to return 0.
| Bug #3384965
+===========================================================================*/
FUNCTION get_office_secondary_area ( p_Location_Id NUMBER ,
p_as_of_date IN DATE ) RETURN NUMBER IS
l_Location_Type pn_locations.location_type_lookup_code%type;
(SELECT NVL((USABLE_AREA), 0) AS usable_area
,NVL((ASSIGNABLE_AREA), 0) AS assignable_area
,NVL((COMMON_AREA), 0) AS common_area
FROM pn_locations_all
WHERE Location_Type_Lookup_Code = p_location_type
AND p_as_of_date BETWEEN active_start_date AND active_end_date
AND Location_Id = p_Location_Id
);
SELECT NVL(SUM(COMMON_AREA),0)
INTO l_common_area
FROM pn_locations_all
WHERE Location_Type_Lookup_Code = l_location_type --'OFFICE'
AND Status = 'A'
AND l_as_of_date BETWEEN active_start_date AND active_end_date
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = Parent_Location_Id
AND l_as_of_date between prior active_start_date and --ASHISH
PRIOR active_end_date;
SELECT NVL(SUM(COMMON_AREA),0)
INTO l_common_area
FROM pn_locations_all
WHERE Location_Type_Lookup_Code = l_location_type
AND Status = 'A'
AND l_as_of_date BETWEEN active_start_date AND active_end_date
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = Parent_Location_Id
AND l_as_of_date between prior active_start_date and
PRIOR active_end_date;
| replace SELECT stmt and remove GROUP BY.
| 3197410.
+===========================================================================*/
FUNCTION get_parent_location_id (
p_location_id IN NUMBER
)
RETURN NUMBER
IS
CURSOR get_parent_loc_id IS
SELECT parent_location_id
FROM pn_locations_all
WHERE location_id = p_location_id;
SELECT NVL(normalize ,'N')
INTO normalize_flag
FROM pn_payment_terms_all
WHERE payment_term_id = p_paymentTermId ;
SELECT MAX(date_start)
INTO l_hire_date
FROM per_periods_of_service
WHERE PERSON_ID = p_PersonId ;
SELECT location_type_lookup_code,parent_location_id
INTO l_location_type_lookup_code,l_parent_location_id
FROM pn_locations_all
WHERE location_id = p_Location_Id
AND p_as_of_date BETWEEN active_start_date AND active_end_date;
SELECT a.location_code office_location_code,a.OFFICE,b.location_code floor_location_code,
b.FLOOR,c.location_code building_location_code,c.BUILDING,
prop.property_code,prop.property_name,
d.name office_park_name, e.name region_name
INTO l_location_name_rec
FROM pn_locations_all a,
pn_locations_all b,
pn_locations_all c,
pn_location_parks d,
pn_location_parks e,
pn_properties_all prop
WHERE a.location_id = p_Location_Id
AND l_as_of_date BETWEEN a.active_start_date AND a.active_end_date
AND b.location_id = l_parent_location_id
AND l_as_of_date BETWEEN b.active_start_date AND b.active_end_date
AND c.location_id = pnp_util_func.GET_PARENT_LOCATION_ID(l_parent_location_id)
AND l_as_of_date BETWEEN c.active_start_date AND c.active_end_date
AND prop.property_id(+) = c.property_id
AND d.location_park_id(+) = prop.location_park_id
AND d.location_park_type(+) = 'OFFPRK'
AND d.language(+) = userenv('LANG')
AND e.location_park_id(+) = d.parent_location_park_id
AND e.location_park_type(+) = 'REGION'
AND e.language(+) = userenv('LANG');
SELECT '' office_location_code,'' OFFICE,b.location_code floor_location_code,
b.FLOOR,c.location_code building_location_code,c.BUILDING,
prop.property_code,prop.property_name,
d.name office_park_name, e.name region_name
INTO l_location_name_rec
FROM pn_locations_all b,
pn_locations_all c,
pn_location_parks d,
pn_location_parks e,
pn_properties_all prop
WHERE b.location_id = p_Location_Id
AND l_as_of_date BETWEEN b.active_start_date AND b.active_end_date
AND c.location_id = l_parent_location_id
AND l_as_of_date BETWEEN c.active_start_date AND c.active_end_date
AND prop.property_id(+) = c.property_id
AND d.location_park_id(+) = prop.location_park_id
AND d.location_park_type(+) = 'OFFPRK'
AND d.language(+) = userenv('LANG')
AND e.location_park_id(+) = d.parent_location_park_id
AND e.location_park_type(+) = 'REGION'
AND e.language(+) = userenv('LANG');
SELECT '' office_location_code,'' OFFICE,'' floor_location_code,
'' FLOOR,c.location_code building_location_code,c.BUILDING,
prop.property_code,prop.property_name,
d.name office_park_name, e.name region_name
INTO l_location_name_rec
FROM pn_locations_all c,
pn_location_parks d,
pn_location_parks e,
pn_properties_all prop
WHERE c.location_id = p_Location_Id
AND l_as_of_date BETWEEN c.active_start_date AND c.active_end_date
AND prop.property_id(+) = c.property_id
AND d.location_park_id(+) = prop.location_park_id
AND d.location_park_type(+) = 'OFFPRK'
AND d.language(+) = userenv('LANG')
AND e.location_park_id(+) = d.parent_location_park_id
AND e.location_park_type(+) = 'REGION'
AND e.language(+) = userenv('LANG');
SELECT MAX(NVL(actual_termination_date,TO_DATE('12/31/4712','mm/dd/yyyy')))
INTO l_termination_date
FROM per_periods_of_service
WHERE person_id = p_PersonId ;
SELECT NVL(rentable_area,0)
INTO l_rentable_area
FROM pn_locations_all
WHERE location_id = p_location_id
AND active_start_date <= l_as_of_date
AND active_end_date >= l_as_of_date;
/* Selecting GL period name WHEN Schedule(GL) date lies between start date
AND end date of an open GL period. */
SELECT period_name
INTO l_gl_period_name
FROM gl_period_statuses
WHERE closing_status IN ('O', 'F')
AND set_of_books_id = pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
p_org_id)
AND application_id = p_application_id
AND adjustment_period_flag = 'N'
AND p_sch_date BETWEEN start_date AND end_date;
SELECT period_name
INTO l_gl_period_name
FROM gl_period_statuses
WHERE closing_status IN ('O', 'F')
AND set_of_books_id = pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
p_org_id)
AND application_id = p_application_id
AND adjustment_period_flag = 'N'
AND start_date = (SELECT MIN(start_date)
FROM gl_period_statuses
WHERE closing_status IN ('O', 'F')
AND set_of_books_id = pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
p_org_id)
AND application_id = p_application_id
AND adjustment_period_flag = 'N'
AND start_date >= p_sch_date);
SELECT uom_code
FROM pn_locations_all
WHERE location_type_lookup_code = l_loc_type
AND l_as_of_date BETWEEN active_start_date AND active_end_date
Start with location_id = p_location_id
CONNECT BY PRIOR parent_location_id = location_id
and rownum < 2 ;
SELECT name
INTO l_payment_term_name
FROM ap_terms
WHERE term_id = p_ap_term_id;
SELECT name
INTO l_payment_term_name
FROM ra_terms
WHERE term_id = p_ar_term_id;
SELECT distribution_set_name
INTO l_dist_set_name
FROM ap_distribution_sets_all
WHERE distribution_set_id = p_dist_set_id;
SELECT name
INTO l_project_name
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT task_name
INTO l_task_name
FROM pa_tasks_expend_v
WHERE task_id = p_task_id;
| 25-MAR-2004 Mrinal Misra o Changed view name in SELECT statement.
| 17-JUL-2009 bifernan o Bug 8370634: Added rownum condition to
| the query
+===========================================================================*/
FUNCTION Get_Ap_organization_Name (p_org_id IN NUMBER)
RETURN VARCHAR2 IS
l_org_name pa_organizations_expend_v.name%type;
SELECT name
INTO l_org_name
FROM pa_organizations_expend_v
WHERE organization_id = p_org_id
AND ROWNUM = 1;
SELECT name
INTO l_trx_type
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_trx_id;
SELECT name
INTO l_rule_name
FROM ra_rules
WHERE rule_id = p_rule_id;
SELECT name
FROM ra_salesreps
WHERE salesrep_id = p_salesrep_id
AND org_id = p_org_id;
SELECT MIN(emp_assign_start_date)
INTO l_emp_min_str_dt
FROM pn_space_assign_emp_all
WHERE location_id = p_loc_id
AND TRUNC(emp_assign_start_date) > TRUNC(p_str_dt);
SELECT MIN(cust_assign_start_date)
INTO l_cust_min_str_dt
FROM pn_space_assign_cust_all
WHERE location_id = p_loc_id
AND TRUNC(cust_assign_start_date) > TRUNC(p_str_dt);
ELSIF p_called_frm_mode = 'PNTSPACE_UPDATE' THEN
l_fut_str_dt := g_end_of_time;
SELECT NVL(SUM(allocated_area), 0)
,NVL(SUM(allocated_area_pct), 0)
INTO l_allocated_area_emp
,l_allocated_area_pct_emp
FROM pn_space_assign_emp_all
WHERE location_id = p_loc_id
AND emp_assign_start_date <= NVL(p_new_end_dt,TO_DATE('12/31/4712','mm/dd/yyyy'))
AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','mm/dd/yyyy')) >= p_str_dt;
SELECT NVL(SUM(allocated_area), 0)
,NVL(SUM(allocated_area_pct), 0)
INTO l_allocated_area_cust
,l_allocated_area_pct_cust
FROM pn_space_assign_cust_all
WHERE location_id = p_loc_id
AND cust_assign_start_date <= NVL(p_new_end_dt,TO_DATE('12/31/4712','mm/dd/yyyy'))
AND NVL(cust_assign_end_date,TO_DATE('12/31/4712','mm/dd/yyyy')) >= p_str_dt;
| 26-MAY-04 abanerje o Added NVL to the select statement so that the
| p_assignable_area is set to -99 when the area is common
| Using this method we are able to distinguish the
| condition when
| a) Location exists for the given date ranges but its a
| common area then set p_assignable_area=-99
| Bug 3598315.
| 30-DEC-04 Kiran o Bug # 4093603 - Added new param p_called_frm_mode
| and passed it to get_allocated_area.
| Corrected the calculation of l_new_allocated_area_pct
| and l_old_allocated_area_pct.
| 16-Jun-06 piagrawa o Bug #4314940 - handle case if p_assignable_area = 0
| 12-Jan-06 hkulkarn o Bug 4740867 - Deriving assignable_area based on underlying
| property/location. This is useful incase of freshly imported
| locations for assignment in Lease.
| 23-FEB-06 Hareesha o Bug # 4926472. Pop-up msg PN_CANNOT_ASSIGN_SPC_COMM
| when common-area-flag is set to Yes.
| 25-JAN-07 csriperu o Bug 5854636 - Moved the future assignment check from
| get_allocated_area
+===========================================================================*/
PROCEDURE validate_vacant_area (p_location_id IN NUMBER,
p_st_date IN DATE,
p_end_dt IN OUT NOCOPY DATE,
p_assignable_area IN OUT NOCOPY NUMBER,
p_old_allocated_area IN NUMBER,
p_new_allocated_area IN NUMBER,
p_old_allocated_area_pct IN NUMBER,
p_new_allocated_area_pct IN NUMBER,
p_display_message IN VARCHAR2,
p_future OUT NOCOPY VARCHAR2,
p_available_vacant_area OUT NOCOPY BOOLEAN,
p_called_frm_mode IN VARCHAR2) IS
l_new_allocated_area NUMBER;
SELECT common_area_flag
FROM pn_locations_all
WHERE location_id = p_location_id
AND active_start_date <= l_end_dt
AND active_end_date >= p_st_date;
ELSIF p_called_frm_mode = 'PNTSPACE_UPDATE' THEN
l_fut_str_dt := g_end_of_time;
SELECT conversion_type
FROM pn_currencies
WHERE currency_code = p_curr_code;
| loctn_assgn_area_update
|
| DESCRIPTION
| This PROCEDURE creates day tracking for current space assignments IF assignable
| area is changed for an assigned location AND updates percent allocated area.
| FOR future dated assignments it just updates percent allocated area.
|
| SCOPE - PUBLIC
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS:
| IN: p_loc_id,p_assgn_area,p_as_of_dt.
| OUT: none
|
| MODIFICATION HISTORY
| 02-MAY-02 Mrinal o Created
| 13-MAY-02 Mrinal o Populated tlempinfo, tlcustinfo variables
| used in row handlers. Removed record type
| var. FROM input param. of UPDATE_ROW.
| 15-MAY-02 Mrinal o Corrected passed values of assignment str dt
| AND end dt. in update_row calls.
| 10-JAN-03 Mrinal o Removed p_as_of_dt IN param's and added two
| new IN param's p_str_dt,p_end_dt and modified
| procedure to correct/update assignments as per
| Location Day Tracking.
| 27-aug-03 Kiran o Corrected the cursor queries to pick up the
| correct assignment records.
| Replaced p_str_dt with l_assgn_str_dt in calls
| to UPDATE_ROW. Populated l_assgn_str_dt conditionally.
| 10-Oct-03 Daniel o Created new cursors get_emp_assgn1 and get_cust_assgn2
| to date track space assignment when location
| attribute is changed. Fix for bug # 3174320
| 10-Nov-03 Daniel o Removed _all from the declaration for emp_rec
| and cust_rec
| 14-Nov-03 Satish o Fix for BUG# 3260023 (Issue 4). Made emp_rec, cust_rec
| as _ALL%ROWTYPE. Modified all 4 cursors to select from
| _ALL tables.
| 28-Apr-04 vmmehta o Fix for BUG# 3197182. Changed call to
| pn_space_assign_cust_pkg.update_row
| Added parameter x_return_status
| 18-JUN-04 Mrinal o Fixed for BUG# 3297892, calculate allocated_area
| based on alloc_area_pct.
| 13-AUG-04 Anand o Added NVL for emp/cust_end_date attributes.
| Also replaced all End Of Time occurances with
| g_end_of_time. Bug # 3821420.
+=============================================================================*/
PROCEDURE loctn_assgn_area_update(p_loc_id IN NUMBER,
p_assgn_area IN NUMBER,
p_str_dt IN DATE,
p_end_dt IN DATE) IS
l_new_emp_alloc_pct PN_SPACE_ASSIGN_EMP.allocated_area_pct%TYPE;
SELECT *
FROM pn_space_assign_emp_all
WHERE location_id = p_loc_id
AND emp_assign_start_date <= p_end_dt
AND NVL(emp_assign_end_date, g_end_of_time) >= p_str_dt;
SELECT *
FROM pn_space_assign_cust_all
WHERE location_id = p_loc_id
AND cust_assign_start_date <= p_end_dt
AND NVL(cust_assign_end_date, g_end_of_time) >= p_str_dt;
SELECT *
FROM pn_space_assign_emp_all
WHERE location_id = p_loc_id
AND NVL(emp_assign_end_date, g_end_of_time) >= p_str_dt;
SELECT *
FROM pn_space_assign_cust_all
WHERE location_id = p_loc_id
AND NVL(cust_assign_end_date, g_end_of_time) >= p_str_dt;
l_mode := 'UPDATE';
PN_SPACE_ASSIGN_EMP_PKG.UPDATE_ROW(
X_EMP_SPACE_ASSIGN_ID => emp_rec.emp_space_assign_id,
X_ATTRIBUTE1 => emp_rec.attribute1,
X_ATTRIBUTE2 => emp_rec.attribute2,
X_ATTRIBUTE3 => emp_rec.attribute3,
X_ATTRIBUTE4 => emp_rec.attribute4,
X_ATTRIBUTE5 => emp_rec.attribute5,
X_ATTRIBUTE6 => emp_rec.attribute6,
X_ATTRIBUTE7 => emp_rec.attribute7,
X_ATTRIBUTE8 => emp_rec.attribute8,
X_ATTRIBUTE9 => emp_rec.attribute9,
X_ATTRIBUTE10 => emp_rec.attribute10,
X_ATTRIBUTE11 => emp_rec.attribute11,
X_ATTRIBUTE12 => emp_rec.attribute12,
X_ATTRIBUTE13 => emp_rec.attribute13,
X_ATTRIBUTE14 => emp_rec.attribute14,
X_ATTRIBUTE15 => emp_rec.attribute15,
X_LOCATION_ID => emp_rec.location_id,
X_PERSON_ID => emp_rec.person_id,
X_PROJECT_ID => emp_rec.project_id,
X_TASK_ID => emp_rec.task_id,
X_EMP_ASSIGN_START_DATE => l_assgn_str_dt,
X_EMP_ASSIGN_END_DATE => emp_rec.emp_assign_end_date,
X_COST_CENTER_CODE => emp_rec.cost_center_code,
X_ALLOCATED_AREA_PCT => emp_rec.allocated_area_pct,
X_ALLOCATED_AREA => l_new_emp_alloc_area,
X_UTILIZED_AREA => emp_rec.utilized_area,
X_EMP_SPACE_COMMENTS => emp_rec.emp_space_comments,
X_ATTRIBUTE_CATEGORY => emp_rec.attribute_category,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id,
X_UPDATE_CORRECT_OPTION => l_mode,
X_CHANGED_START_DATE => l_date);
l_mode := 'UPDATE';
PN_SPACE_ASSIGN_CUST_PKG.UPDATE_ROW(
X_CUST_SPACE_ASSIGN_ID => cust_rec.CUST_SPACE_ASSIGN_ID,
X_LOCATION_ID => cust_rec.LOCATION_ID,
X_CUST_ACCOUNT_ID => cust_rec.CUST_ACCOUNT_ID,
X_SITE_USE_ID => cust_rec.SITE_USE_ID,
X_EXPENSE_ACCOUNT_ID => cust_rec.EXPENSE_ACCOUNT_ID,
X_PROJECT_ID => cust_rec.PROJECT_ID,
X_TASK_ID => cust_rec.TASK_ID,
X_CUST_ASSIGN_START_DATE => l_assgn_str_dt,
X_CUST_ASSIGN_END_DATE => cust_rec.CUST_ASSIGN_END_DATE,
X_ALLOCATED_AREA_PCT => cust_rec.ALLOCATED_AREA_PCT,
X_ALLOCATED_AREA => l_new_cust_alloc_area,
X_UTILIZED_AREA => cust_rec.UTILIZED_AREA,
X_CUST_SPACE_COMMENTS => cust_rec.CUST_SPACE_COMMENTS,
X_ATTRIBUTE_CATEGORY => cust_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => cust_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => cust_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => cust_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => cust_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => cust_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => cust_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => cust_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => cust_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => cust_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => cust_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => cust_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => cust_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => cust_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => cust_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => cust_rec.ATTRIBUTE15,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id,
X_UPDATE_CORRECT_OPTION => l_mode,
X_CHANGED_START_DATE => l_date,
X_LEASE_ID => cust_rec.LEASE_ID,
X_RECOVERY_SPACE_STD_CODE => cust_rec.RECOVERY_SPACE_STD_CODE,
X_RECOVERY_TYPE_CODE => cust_rec.RECOVERY_TYPE_CODE,
X_FIN_OBLIG_END_DATE => cust_rec.FIN_OBLIG_END_DATE,
X_TENANCY_ID => cust_rec.TENANCY_ID,
X_RETURN_STATUS => l_return_status);
END loctn_assgn_area_update;
SELECT NVL(SUM(ASSIGNABLE_AREA),0) , NVL(SUM(RENTABLE_AREA),0) , NVL(SUM(USABLE_AREA),0)
, NVL(SUM(COMMON_AREA),0),NVL(SUM(MAX_CAPACITY),0), NVL(SUM(OPTIMUM_CAPACITY),0)
FROM pn_locations_all
WHERE Location_Type_Lookup_Code = l_location_type_lookup_code
AND Status = 'A'
AND l_as_of_date BETWEEN active_start_date AND active_end_date
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = Parent_Location_Id
AND l_as_of_date between prior active_start_date and --ASHISH
PRIOR active_end_date;
SELECT NVL(SUM(Allocated_Area), 0),NVL(SUM(UTILIZED_AREA),0)
FROM PN_SPACE_ASSIGN_EMP_ALL
WHERE emp_assign_start_date <= l_as_of_date
AND NVL(emp_assign_end_date, l_date) >= l_as_of_date
AND Location_Id IN (SELECT Location_Id
FROM pn_locations_all
WHERE Location_Type_Lookup_Code = l_location_type_lookup_code
AND Status = 'A'
AND l_as_of_date BETWEEN active_start_date AND active_end_date
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = Parent_Location_Id
AND l_as_of_date between prior active_start_date and --ASHISH
PRIOR active_end_date
);
SELECT NVL(SUM(Allocated_Area), 0),NVL(SUM(UTILIZED_AREA),0)
FROM PN_SPACE_ASSIGN_CUST_ALL
WHERE cust_assign_start_date <= l_as_of_date
AND NVL(cust_assign_end_date, l_date) >= l_as_of_date
AND Location_Id IN (SELECT Location_Id
FROM pn_locations_all
WHERE Location_Type_Lookup_Code = l_location_type_lookup_code
AND Status = 'A'
AND l_as_of_date BETWEEN active_start_date AND active_end_date
START WITH Location_Id = p_Location_Id
CONNECT BY PRIOR Location_Id = Parent_Location_Id
AND l_as_of_date between prior active_start_date and --ASHISH
PRIOR active_end_date
);
SELECT emp_assign_start_date
FROM pn_space_assign_emp_all
WHERE location_id = p_Location_Id
UNION
SELECT cust_assign_start_date
FROM pn_space_assign_cust_all
WHERE location_id = p_Location_Id
ORDER BY 1;
| 13-APR-2004 Anand Tuppad o Changed the cursor to select only required
| cols and not all cols(ie removed *)
| 22-SEP-2008 kkorada o Modified the function to exclude customer information
| while validating the term template. bug#6660956
+===========================================================================*/
FUNCTION validate_term_template(p_term_temp_id IN NUMBER,
p_lease_cls_code IN VARCHAR2)
RETURN BOOLEAN IS
CURSOR term_temp_type_cur(p_term_temp_id IN NUMBER) IS
SELECT term_template_type
FROM pn_term_templates_all
WHERE term_template_id = p_term_temp_id;
SELECT payment_purpose_code,
payment_term_type_code,
currency_code,
customer_id,
customer_site_use_id,
ap_ar_term_id,
cust_trx_type_id
FROM pn_term_templates_all
WHERE term_template_id = p_term_temp_id;
SELECT payment_purpose_code,
payment_term_type_code,
currency_code,
ap_ar_term_id,
vendor_id,
vendor_site_id
FROM pn_term_templates_all
WHERE term_template_id = p_term_temp_id;
SELECT name
INTO l_term_temp_name
FROM pn_term_templates_all
WHERE term_template_id = p_term_temp_id;
SELECT *
FROM pn_space_assign_cust_all
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 )
AND NVL(cust_assign_end_date,g_end_of_time) >= p_start_date
AND cust_assign_start_date <= NVL(p_end_date, g_end_of_time);
SELECT *
FROM pn_space_assign_emp_all
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 )
AND NVL(emp_assign_end_date,g_end_of_time) >= p_start_date
AND emp_assign_start_date <= NVL(p_end_date, g_end_of_time);
SELECT 'x'
FROM DUAL
WHERE EXISTS (SELECT 'x'
FROM pn_space_assign_emp_all
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 )
--Bug#5959164 AND allocated_area > 0
--Bug#5959164 AND allocated_area_pct > 0
AND emp_assign_start_date < p_start_date
AND NVL(emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= p_start_date_old
UNION
SELECT 'x'
FROM pn_space_assign_cust_all
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 )
--Bug#5959164 AND allocated_area > 0
--Bug#5959164 AND allocated_area_pct > 0
AND cust_assign_start_date < p_start_date
AND NVL(cust_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= p_start_date_old);
SELECT 'x'
FROM DUAL
WHERE EXISTS (SELECT 'x'
FROM pn_space_assign_emp_all
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 )
--Bug#5959164 AND allocated_area > 0
--Bug#5959164 AND allocated_area_pct > 0
AND NVL(emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) > p_end_date
AND emp_assign_start_date <= p_end_date_old
UNION
SELECT 'x'
FROM pn_space_assign_cust_all
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 )
--Bug#5959164 AND allocated_area > 0
--Bug#5959164 AND allocated_area_pct > 0
AND NVL(cust_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) > p_end_date AND
cust_assign_start_date <= p_end_date_old);
| for UPDATE/CORRECT mode.
+===========================================================================*/
PROCEDURE validate_date_assignable_area
( p_location_id IN NUMBER,
p_location_type IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_active_start_date_old IN DATE,
p_active_end_date_old IN DATE,
p_change_mode IN VARCHAR2 ,
p_assignable_area IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
)
IS
l_space_assign_tbl SPACE_ASSIGNMENT_TBL;
SELECT *
FROM pn_locations_all
WHERE location_id = p_location_id
AND active_start_date = p_Active_start_date_old
AND active_end_date = p_active_end_date_old;
IF p_change_mode = 'UPDATE' THEN
l_str_date_old := p_start_date;
/* Bug#7666462 : Modified the Cursor SELECT query to handle NULL condition for end date */
CURSOR locations_cursor Is
SELECT *
FROM pn_locations_all
WHERE location_id = p_location_id
AND (active_start_date <= NVL(p_start_Date, active_start_date)
AND nvl(active_end_Date, TO_DATE('12/31/4712','MM/DD/YYYY')) >=
nvl( p_end_date, TO_DATE('12/31/4712','MM/DD/YYYY')));
SELECT 'Y'
FROM dual
WHERE exists
(select tenancy_id
from pn_tenancies_all
where location_id in
(select loc.location_id
from pn_locations_all loc
connect by prior loc.location_id = loc.parent_location_id
start with loc.location_id = p_Location_Id)
and primary_flag = 'Y'
and EXPIRATION_DATE > p_New_End_Date
);
SELECT 'Y'
FROM dual
WHERE exists
(select tenancy_id
from pn_tenancies_all
where location_id in
(select loc.location_id
from pn_locations_all loc
connect by prior loc.location_id = loc.parent_location_id
start with loc.location_id = p_Location_Id)
and primary_flag = 'Y'
and nvl(OCCUPANCY_DATE,ESTIMATED_OCCUPANCY_DATE) < p_New_Start_Date
);
SELECT 'Y'
FROM dual
WHERE exists
(select area_class_dtl_line_id
from pn_rec_arcl_dtlln_all
where location_id in
(select loc.location_id
from pn_locations_all loc
connect by prior loc.location_id = loc.parent_location_id
start with loc.location_id = p_Location_Id)
);
select 'Y'
from dual
where exists
(select arclDtl.area_class_dtl_line_id
from pn_rec_arcl_dtlln_all arclDtl
where arclDtl.location_id = p_location_id
and (arclDtl.from_date between p_active_start_date
and p_active_end_date
or
arclDtl.to_date between p_active_start_date
and p_active_end_date)
);
PROCEDURE batch_update_terms_area(
x_area_tbl num_tbl,
x_term_id_tbl num_tbl)
IS
l_user NUMBER := fnd_global.user_id;
UPDATE pn_payment_terms_all
SET area = x_area_tbl(i),
last_updated_by = l_user,
last_update_date = SYSDATE
WHERE payment_term_id = x_term_id_tbl(i);
END batch_update_terms_area;
SELECT 'Y' answer
FROM pn_tenancies_all tnc
WHERE tnc.tenancy_id = p_tenancy_id
AND EXISTS (SELECT 'Y' FROM pn_payment_terms_all trm
WHERE trm.lease_id = tnc.lease_id
AND trm.location_id = tnc.location_id
AND trm.area_type_code = p_type);
SELECT 'Y' answer
FROM pn_tenancies_all tnc
WHERE tnc.tenancy_id = p_tenancy_id
AND EXISTS (SELECT 'Y' FROM pn_payment_terms_all trm
WHERE trm.lease_id = tnc.lease_id
AND trm.location_id = tnc.location_id);
SELECT lease_rentable_area,
lease_usable_area,
lease_assignable_area,
tenants_proportionate_share,
estimated_occupancy_date,
occupancy_date,
expiration_date,
location_id
FROM pn_tenancies_all
WHERE tenancy_id = p_tenancy_id;
SELECT trm.area_type_code,
trm.payment_term_id,
trm.start_date,
loc.location_type_lookup_code,
loc.rentable_area,
loc.usable_area,
loc.assignable_area
FROM pn_payment_terms_all trm,
pn_locations_all loc
WHERE trm.lease_id = p_lease_id
AND loc.location_id = p_loc_id
AND trm.location_id = p_loc_id
AND trm.area_type_code NOT IN ('OTHER')
AND trm.area_type_code IS NOT NULL
AND trm.start_date BETWEEN p_occ_date AND p_exp_date
AND trm.start_date BETWEEN loc.active_start_date AND loc.active_end_date;
SELECT 'Y' answer
FROM dual
WHERE EXISTS (SELECT 'Y' FROM pn_payment_items_all
WHERE payment_term_id = p_payment_term_id);
SELECT allocated_area_pct share_pct /* 8819189 */
FROM pn_tenancies_all
WHERE location_id = p_location_id
AND lease_id = p_lease_id
AND p_as_of_date BETWEEN nvl(occupancy_date, estimated_occupancy_date)
AND expiration_date;
SELECT sum(tnc.lease_assignable_area) lease_assignable_area,
sum(tnc.lease_rentable_area) lease_rentable_area,
sum(tnc.lease_usable_area) lease_usable_area
FROM pn_tenancies_all tnc
WHERE tnc.lease_id = p_lease_id
AND tnc.location_id = p_location_id
AND p_as_of_date BETWEEN nvl(tnc.occupancy_date, tnc.estimated_occupancy_date) AND tnc.expiration_date;
SELECT rentable_area,
usable_area,
assignable_area,
active_start_date,
active_end_date
FROM pn_locations_all
WHERE location_id = p_ofc_loc_id;
SELECT area_type_code,
payment_term_id,
start_date,
location_id,
lease_id,
DECODE(location_id, p_ofc_id, 'OFFICE', p_flr_id, 'FLOOR', p_bld_id, 'BUILDING') type
FROM pn_payment_terms_all
WHERE location_id IN (p_ofc_id, p_flr_id, p_bld_id)
AND area_type_code IN ('LOCTN_RENTABLE','LOCTN_USABLE','LOCTN_ASSIGNABLE')
AND start_date BETWEEN p_start_date AND p_end_date;
SELECT 'Y' answer
FROM dual
WHERE EXISTS (SELECT 'Y' FROM pn_payment_items_all
WHERE payment_term_id = p_payment_term_id);
SELECT sum(nvl(rentable_area,0)) rentable,
sum(nvl(usable_area,0)) usable,
sum(nvl(assignable_area,0)) assignable
FROM pn_locations_all loc
WHERE loc.parent_location_id = p_flr_loc_id
AND p_as_of_date BETWEEN active_start_date AND active_end_date
AND location_id <> p_ofc_loc_id;
SELECT sum(nvl(o.rentable_area,0)) rentable,
sum(nvl(o.usable_area,0)) usable,
sum(nvl(o.assignable_area,0)) assignable
FROM pn_locations_all f, pn_locations_all o
WHERE p_bld_loc_id = f.parent_location_id
AND f.location_id = o.parent_location_id
AND p_as_of_date BETWEEN f.active_start_date AND f.active_end_date
AND p_as_of_date BETWEEN o.active_start_date AND o.active_end_date
AND o.location_id <> p_ofc_loc_id;
SELECT nvl(sum(ofc.rentable_area),0) rentable,
nvl(sum(ofc.usable_area),0) usable,
nvl(sum(ofc.assignable_area),0) assignable,
nvl(sum(ofc.max_capacity),0) max_capacity,
nvl(sum(ofc.optimum_capacity),0) optimum_capacity
FROM pn_locations_all ofc,
pn_locations_all flr
WHERE p_as_of_date BETWEEN ofc.active_start_date AND ofc.active_end_date
AND p_as_of_date BETWEEN flr.active_start_date AND flr.active_end_date
AND flr.parent_location_id = p_location_id
AND ofc.parent_location_id = flr.location_id;
SELECT nvl(sum(ofc.rentable_area),0) rentable,
nvl(sum(ofc.usable_area),0) usable,
nvl(sum(ofc.assignable_area),0) assignable,
nvl(sum(ofc.max_capacity),0) max_capacity,
nvl(sum(ofc.optimum_capacity),0) optimum_capacity
FROM pn_locations_all ofc
WHERE p_as_of_date BETWEEN ofc.active_start_date AND ofc.active_end_date
AND ofc.parent_location_id = p_location_id;
SELECT rentable_area rentable,
usable_area usable,
assignable_area assignable,
max_capacity max_capacity,
optimum_capacity optimum_capacity
FROM pn_locations_all ofc
WHERE p_as_of_date BETWEEN ofc.active_start_date AND ofc.active_end_date
AND ofc.location_id = p_location_id;
SELECT pvr.rent_num
FROM pn_var_rents_all pvr,
pn_var_rent_inv_all pvri,
pn_var_abatements_all pva
WHERE pva.payment_term_id = p_term_id
AND pvri.var_rent_inv_id = pva.var_rent_inv_id
AND pvri.invoice_date NOT BETWEEN p_new_start_date
AND p_new_end_date
AND pvr.var_rent_ID = pvri.var_rent_ID;
SELECT ADD_MONTHS(p_from_date, NVL(p_yrs,0) * 12 + NVL(p_mths,0)) + NVL(p_days,0)
INTO l_to_date
FROM DUAL;
SELECT ADD_MONTHS( (p_from_date - 1), NVL(p_yrs,0) * 12 + NVL(p_mths,0)) + NVL(p_days,0)
INTO l_to_date
FROM DUAL;
SELECT name
INTO l_trx_type
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = p_trx_id
AND org_id = p_org_id;
SELECT location_id
FROM pn_tenancies_all
WHERE lease_id = p_lease_id
AND NVL(primary_flag ,'N') = 'Y'
AND ROWNUM < 2;
SELECT location_id
FROM pn_tenancies_all
WHERE lease_id = p_lease_id
AND NVL(primary_flag ,'N') = 'Y'
AND ROWNUM < 2;
SELECT location_id
FROM pn_tenancies_all
WHERE lease_id = p_lease_id
AND NVL(primary_flag ,'N') = 'Y'
AND ROWNUM < 2;