[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| INSERT_ROW - insert a row into PN_INDEX_LEASES.
| DELETE_ROW - deletes a row from PN_INDEX_LEASES.
| UPDATE_ROW - updates a row from PN_INDEX_LEASES.
| LOCKS_ROW - will check if a row has been modified since being queried by form.
|
|
| HISTORY
| 10-APR-01 jbreyes o Created
| 15-JUN-01 jbreyes o Added new column BASE_INDEX_LINE_ID
| 21-JUN-01 jbreyes o Added new column INDEX_FINDER_MONTHS
| 07-AUG-01 psidhu o Added new columns AGGEGATION_FLAG and GROSS_FLAG
| 13-DEC-01 Mrinal o Added dbdrv command.
| 15-JAN-02 Mrinal o In dbdrv command changed phase=pls to phase=plb.
| Added checkfile.Ref. Bug# 2184724.
| 17-Jul-02 Psidhu o Added currency_code as a parameter to insert_row,
| update_row and lock_row.
| 09-JUL-02 ftanudja o added x_org_id param in insert_row for
| shared services enh.
| 23-JUL-02 ftanudja o changed lock_row to comply with new standards
| 05-AUG-02 psidhu o added x_carry_forward_flag parameter to insert_row,
| update_row and lock_row.
| 17-MAY-04 vmmehta o added x_retain_initial_flag parameter to insert_row,
| update_row and lock_row.
| 05-Jul-05 hrodda o overloaded delete_row proc to take PK as parameter
| 14-AUG-06 pikhar o Added vr_nbp_flag to insert/update/lock
| 09-NOV-06 prabhakar o Added index_multiplier to insert/update/lock
+===========================================================================*/
-------------------------------------------------------------------------------
-- PROCDURE : INSERT_ROW
-- INVOKED FROM : insert_row procedure
-- PURPOSE : inserts the row
-- HISTORY :
-- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_leases with _ALL table.
-- 14-AUG-06 pikhar o Added vr_nbp_flag to insert
-- 09-NOV-06 prabhakar o Added index_multiplier to insert_row
-- 08-DEC-06 Prabhakar o Added proration_rule and proration_period_start_date.
-------------------------------------------------------------------------------
PROCEDURE insert_row (
x_rowid IN OUT NOCOPY VARCHAR2
,x_org_id IN NUMBER
,x_index_lease_id IN OUT NOCOPY NUMBER
,x_lease_id IN NUMBER
,x_index_id IN NUMBER
,x_commencement_date IN DATE
,x_termination_date IN DATE
,x_index_lease_number IN OUT NOCOPY VARCHAR2
,x_last_update_date IN DATE
,x_last_updated_by IN NUMBER
,x_creation_date IN DATE
,x_created_by IN NUMBER
,x_location_id IN NUMBER
,x_term_template_id IN NUMBER
,x_abstracted_by IN NUMBER
,x_assessment_date IN DATE
,x_assessment_interval IN NUMBER
,x_spread_frequency IN VARCHAR2
,x_relationship_default IN VARCHAR2
,x_basis_percent_default IN NUMBER
,x_initial_basis IN NUMBER
,x_base_index IN NUMBER
,x_base_index_line_id IN NUMBER
,x_index_finder_method IN VARCHAR2
,x_index_finder_months IN NUMBER
,x_negative_rent_type IN VARCHAR2
,x_increase_on IN VARCHAR2
,x_basis_type IN VARCHAR2
,x_reference_period IN VARCHAR2
,x_base_year IN DATE
,x_leased_area IN NUMBER
,x_rounding_flag IN VARCHAR2
,x_aggregation_flag IN VARCHAR2
,x_gross_flag IN VARCHAR2
,x_last_update_login IN NUMBER
,x_attribute_category IN VARCHAR2
,x_attribute1 IN VARCHAR2
,x_attribute2 IN VARCHAR2
,x_attribute3 IN VARCHAR2
,x_attribute4 IN VARCHAR2
,x_attribute5 IN VARCHAR2
,x_attribute6 IN VARCHAR2
,x_attribute7 IN VARCHAR2
,x_attribute8 IN VARCHAR2
,x_attribute9 IN VARCHAR2
,x_attribute10 IN VARCHAR2
,x_attribute11 IN VARCHAR2
,x_attribute12 IN VARCHAR2
,x_attribute13 IN VARCHAR2
,x_attribute14 IN VARCHAR2
,x_attribute15 IN VARCHAR2
,x_agreement_category IN VARCHAR2
,x_agreement_attribute1 IN VARCHAR2
,x_agreement_attribute2 IN VARCHAR2
,x_agreement_attribute3 IN VARCHAR2
,x_agreement_attribute4 IN VARCHAR2
,x_agreement_attribute5 IN VARCHAR2
,x_agreement_attribute6 IN VARCHAR2
,x_agreement_attribute7 IN VARCHAR2
,x_agreement_attribute8 IN VARCHAR2
,x_agreement_attribute9 IN VARCHAR2
,x_agreement_attribute10 IN VARCHAR2
,x_agreement_attribute11 IN VARCHAR2
,x_agreement_attribute12 IN VARCHAR2
,x_agreement_attribute13 IN VARCHAR2
,x_agreement_attribute14 IN VARCHAR2
,x_agreement_attribute15 IN VARCHAR2
,x_currency_code IN VARCHAR2
,x_carry_forward_flag IN VARCHAR2
,x_retain_initial_basis_flag IN VARCHAR2
,x_vr_nbp_flag IN VARCHAR2
,x_index_multiplier IN NUMBER
,x_proration_rule IN VARCHAR2
,x_proration_period_start_date IN DATE)
IS
CURSOR c IS
SELECT ROWID
FROM pn_index_leases_all
WHERE index_lease_id = x_index_lease_id;
SELECT org_id FROM pn_leases_all WHERE lease_id = x_lease_id;
PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.insert_row (+)');
SELECT pn_index_leases_s.NEXTVAL
INTO x_index_lease_id
FROM DUAL;
INSERT INTO pn_index_leases_all
(
index_lease_id
,org_id
,lease_id
,index_id
,commencement_date
,termination_date
,index_lease_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,location_id
,term_template_id
,abstracted_by
,assessment_date
,assessment_interval
,spread_frequency
,relationship_default
,basis_percent_default
,initial_basis
,base_index
,base_index_line_id
,index_finder_method
,index_finder_months
,negative_rent_type
,increase_on
,basis_type
,reference_period
,base_year
,leased_area
,rounding_flag
,aggregation_flag
,gross_flag
,last_update_login
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,agreement_category
,agreement_attribute1
,agreement_attribute2
,agreement_attribute3
,agreement_attribute4
,agreement_attribute5
,agreement_attribute6
,agreement_attribute7
,agreement_attribute8
,agreement_attribute9
,agreement_attribute10
,agreement_attribute11
,agreement_attribute12
,agreement_attribute13
,agreement_attribute14
,agreement_attribute15
,currency_code
,carry_forward_flag
,retain_initial_basis_flag
,vr_nbp_flag
,index_multiplier
,proration_rule
,proration_period_start_date )
VALUES
(
x_index_lease_id
,l_org_ID
,x_lease_id
,x_index_id
,x_commencement_date
,x_termination_date
,x_index_lease_number
,x_last_update_date
,x_last_updated_by
,x_creation_date
,x_created_by
,x_location_id
,x_term_template_id
,x_abstracted_by
,x_assessment_date
,x_assessment_interval
,x_spread_frequency
,x_relationship_default
,x_basis_percent_default
,x_initial_basis
,x_base_index
,x_base_index_line_id
,x_index_finder_method
,x_index_finder_months
,x_negative_rent_type
,x_increase_on
,x_basis_type
,x_reference_period
,x_base_year
,x_leased_area
,x_rounding_flag
,x_aggregation_flag
,x_gross_flag
,x_last_update_login
,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_agreement_category
,x_agreement_attribute1
,x_agreement_attribute2
,x_agreement_attribute3
,x_agreement_attribute4
,x_agreement_attribute5
,x_agreement_attribute6
,x_agreement_attribute7
,x_agreement_attribute8
,x_agreement_attribute9
,x_agreement_attribute10
,x_agreement_attribute11
,x_agreement_attribute12
,x_agreement_attribute13
,x_agreement_attribute14
,x_agreement_attribute15
,x_currency_code
,x_carry_forward_flag
,x_retain_initial_basis_flag
,x_vr_nbp_flag
,x_index_multiplier
,x_proration_rule
,x_proration_period_start_date );
END insert_row;
PROCEDURE update_row (
x_rowid IN VARCHAR2
,x_index_lease_id IN NUMBER
,x_lease_id IN NUMBER
,x_index_id IN NUMBER
,x_commencement_date IN DATE
,x_termination_date IN DATE
,x_index_lease_number IN VARCHAR2
,x_last_update_date IN DATE
,x_last_updated_by IN NUMBER
,x_location_id IN NUMBER
,x_term_template_id IN NUMBER
,x_abstracted_by IN NUMBER
,x_assessment_date IN DATE
,x_assessment_interval IN NUMBER
,x_spread_frequency IN VARCHAR2
,x_relationship_default IN VARCHAR2
,x_basis_percent_default IN NUMBER
,x_initial_basis IN NUMBER
,x_base_index IN NUMBER
,x_base_index_line_id IN NUMBER
,x_index_finder_method IN VARCHAR2
,x_index_finder_months IN NUMBER
,x_negative_rent_type IN VARCHAR2
,x_increase_on IN VARCHAR2
,x_basis_type IN VARCHAR2
,x_reference_period IN VARCHAR2
,x_base_year IN DATE
,x_leased_area IN NUMBER
,x_rounding_flag IN VARCHAR2
,x_aggregation_flag IN VARCHAR2
,x_gross_flag IN VARCHAR2
,x_last_update_login IN NUMBER
,x_attribute_category IN VARCHAR2
,x_attribute1 IN VARCHAR2
,x_attribute2 IN VARCHAR2
,x_attribute3 IN VARCHAR2
,x_attribute4 IN VARCHAR2
,x_attribute5 IN VARCHAR2
,x_attribute6 IN VARCHAR2
,x_attribute7 IN VARCHAR2
,x_attribute8 IN VARCHAR2
,x_attribute9 IN VARCHAR2
,x_attribute10 IN VARCHAR2
,x_attribute11 IN VARCHAR2
,x_attribute12 IN VARCHAR2
,x_attribute13 IN VARCHAR2
,x_attribute14 IN VARCHAR2
,x_attribute15 IN VARCHAR2
,x_agreement_category IN VARCHAR2
,x_agreement_attribute1 IN VARCHAR2
,x_agreement_attribute2 IN VARCHAR2
,x_agreement_attribute3 IN VARCHAR2
,x_agreement_attribute4 IN VARCHAR2
,x_agreement_attribute5 IN VARCHAR2
,x_agreement_attribute6 IN VARCHAR2
,x_agreement_attribute7 IN VARCHAR2
,x_agreement_attribute8 IN VARCHAR2
,x_agreement_attribute9 IN VARCHAR2
,x_agreement_attribute10 IN VARCHAR2
,x_agreement_attribute11 IN VARCHAR2
,x_agreement_attribute12 IN VARCHAR2
,x_agreement_attribute13 IN VARCHAR2
,x_agreement_attribute14 IN VARCHAR2
,x_agreement_attribute15 IN VARCHAR2
,x_currency_code IN VARCHAR2
,x_carry_forward_flag IN VARCHAR2
,x_retain_initial_basis_flag IN VARCHAR2
,x_vr_nbp_flag IN VARCHAR2
,x_index_multiplier IN NUMBER
,x_proration_rule IN VARCHAR2
,x_proration_period_start_date IN DATE)
IS
l_return_status VARCHAR2 (30) := NULL;
PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.update_row (+)');
UPDATE pn_index_leases_all
SET lease_id = x_lease_id
,index_id = x_index_id
,commencement_date = x_commencement_date
,termination_date = x_termination_date
,index_lease_number = x_index_lease_number
,last_update_date = x_last_update_date
,last_updated_by = x_last_updated_by
,location_id = x_location_id
,term_template_id = x_term_template_id
,abstracted_by = x_abstracted_by
,assessment_date = x_assessment_date
,assessment_interval = x_assessment_interval
,spread_frequency = x_spread_frequency
,relationship_default = x_relationship_default
,basis_percent_default = x_basis_percent_default
,initial_basis = x_initial_basis
,base_index = x_base_index
,base_index_line_id = x_base_index_line_id
,index_finder_method = x_index_finder_method
,index_finder_months = x_index_finder_months
,negative_rent_type = x_negative_rent_type
,increase_on = x_increase_on
,basis_type = x_basis_type
,reference_period = x_reference_period
,base_year = x_base_year
,leased_area = x_leased_area
,rounding_flag = x_rounding_flag
,aggregation_flag = x_aggregation_flag
,gross_flag = x_gross_flag
,last_update_login = x_last_update_login
,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
,agreement_category = x_agreement_category
,agreement_attribute1 = x_agreement_attribute1
,agreement_attribute2 = x_agreement_attribute2
,agreement_attribute3 = x_agreement_attribute3
,agreement_attribute4 = x_agreement_attribute4
,agreement_attribute5 = x_agreement_attribute5
,agreement_attribute6 = x_agreement_attribute6
,agreement_attribute7 = x_agreement_attribute7
,agreement_attribute8 = x_agreement_attribute8
,agreement_attribute9 = x_agreement_attribute9
,agreement_attribute10 = x_agreement_attribute10
,agreement_attribute11 = x_agreement_attribute11
,agreement_attribute12 = x_agreement_attribute12
,agreement_attribute13 = x_agreement_attribute13
,agreement_attribute14 = x_agreement_attribute14
,agreement_attribute15 = x_agreement_attribute15
,currency_code = x_currency_code
,carry_forward_flag = x_carry_forward_flag
,retain_initial_basis_flag = x_retain_initial_basis_flag
,vr_nbp_flag = x_vr_nbp_flag
,index_multiplier = x_index_multiplier
,proration_rule = x_proration_rule
,proration_period_start_date = x_proration_period_start_date
WHERE index_lease_id = x_index_lease_id;
PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.update_row (-)');
END update_row;
SELECT *
FROM pn_index_leases_all
WHERE INDEX_LEASE_ID = x_index_lease_id
FOR UPDATE OF index_lease_id NOWAIT;
PROCEDURE delete_row (
x_rowid IN VARCHAR2)
IS
BEGIN
PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.delete_row (+)');
DELETE FROM pn_index_leases_all
WHERE ROWID = x_rowid;
PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.delete_row (-)');
END delete_row;
PROCEDURE delete_row (
x_index_lease_id IN NUMBER) IS
BEGIN
PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.delete_row (+)');
DELETE FROM pn_index_leases_all
WHERE index_lease_id = x_index_lease_id;
PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.delete_row (-)');
END delete_row;
SELECT 1
INTO l_dummy
FROM DUAL
WHERE NOT EXISTS ( SELECT 1
FROM pn_index_leases_all
WHERE (index_lease_number = x_index_lease_number)
AND ( (x_index_lease_id IS NULL)
OR (index_lease_id <> x_index_lease_id)));