[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_tenancies_row (
p_lease_tenancies_rec IN OUT NOCOPY lease_tenancies_rec
, p_lease_rec IN lease_rec
, p_lease_context IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
);
Select customer_id,
location_id,
customer_site_use_id,
occupancy_date,
estimated_occupancy_date,
expiration_date,
recovery_space_std_code,
recovery_type_code,
fin_oblig_end_date,
allocated_area_pct
From pn_tenancies_all
Where tenancy_id = p_tenancy_id;
PROCEDURE Update_Space_Assign( p_lease_tenancies_rec IN lease_tenancies_rec
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_assign_count NUMBER := 0;
pvt_debug(g_pkg_name||'-Update_Space_Assign Begin',3);
pvt_debug(g_pkg_name||'-Calling Update_auto_space_assign Begin',3);
pn_tenancies_pkg.update_auto_space_assign
(
p_location_id => p_lease_tenancies_rec.location_id
,p_lease_id => p_lease_tenancies_rec.lease_id
,p_customer_id => p_lease_tenancies_rec.customer_id
,p_cust_site_use_id => p_lease_tenancies_rec.customer_site_use_id
,p_cust_assign_start_dt => NVL(p_lease_tenancies_rec.occupancy_date,p_lease_tenancies_rec.estimated_occupancy_date)
,p_cust_assign_end_dt => p_lease_tenancies_rec.expiration_date
,p_recovery_space_std_code => p_lease_tenancies_rec.recovery_space_std_code
,p_recovery_type_code => p_lease_tenancies_rec.recovery_type_code
,p_fin_oblig_end_date => p_lease_tenancies_rec.fin_oblig_end_date
,p_allocated_pct => p_lease_tenancies_rec.allocated_area_pct
,p_tenancy_id => p_lease_tenancies_rec.tenancy_id
,p_org_id => p_lease_tenancies_rec.org_id
,p_location_id_old => c_loc_rec.location_id
,p_customer_id_old => c_loc_rec.customer_id
,p_cust_site_use_id_old => c_loc_rec.customer_site_use_id
,p_cust_assign_start_dt_old => NVL(c_loc_rec.Occupancy_Date,c_loc_rec.Estimated_Occupancy_Date)
,p_cust_assign_end_dt_old => c_loc_rec.Expiration_Date
,p_recovery_space_std_code_old => c_loc_rec.recovery_space_std_code
,p_recovery_type_code_old => c_loc_rec.recovery_type_code
,p_fin_oblig_end_date_old => c_loc_rec.Fin_Oblig_End_Date
,p_allocated_pct_old => c_loc_rec.allocated_area_pct
,p_action => l_action
,p_msg => l_message
);
pvt_debug(g_pkg_name||'-Update_Space_Assign End',3);
pvt_debug(g_pkg_name||'-Update_Space_Assign.G_EXC_ERROR'||SQLERRM,3);
pvt_debug(g_pkg_name||'-Update_Space_assign.G_RET_STS_UNEXP_ERROR'||SQLERRM,3);
pvt_debug(g_pkg_name||'-Update_Space_Assign :When Others '||SQLERRM,3);
END Update_Space_Assign;
IF (trim(p_operation) <> 'UPDATE_TENANCY') THEN
/* pn_lease_utils.add_null_parameter_msg
(p_token_apiname => l_api_name_full
, p_token_nullparam => 'Tenancy_Usage');*/
IF (trim(p_operation) <> 'UPDATE_TENANCY') THEN
/*pn_lease_utils.add_null_parameter_msg
(p_token_apiname => l_api_name_full
, p_token_nullparam => 'Tenancy_Location');*/
IF (trim(p_operation) <> 'UPDATE_TENANCY') THEN
IF p_lease_tenancies_tbl (i).location_id = pn_lease_utils.g_pn_miss_num
THEN
p_lease_tenancies_tbl (i).location_id := NULL;
IF (trim(p_operation) <> 'UPDATE_TENANCY') THEN
/* pn_lease_utils.add_null_parameter_msg(p_token_apiname => l_api_name_full
, p_token_nullparam => 'Tenancy_Dates'
);*/
/*ELSIF(p_lease_tenancies_tbl (i).occupancy_date IS NULL AND trim(p_operation) = 'UPDATE_TENANCY') THEN
--------------dbms_output.put_line('Inside Occ:'||p_lease_tenancies_tbl (i).occupancy_date);
/*IF (trim(p_operation) <> 'UPDATE_TENANCY') THEN
l_error_flag:='Y';
IF (trim(p_operation) <> 'UPDATE_TENANCY') THEN
l_error_flag:='Y';
IF (trim(p_operation) <> 'UPDATE_TENANCY') THEN
l_error_flag:='Y';
pvt_debug(g_pkg_name||'-VALIDATE_TENANCIES_TBL:Calling Insert Tenancies Row',3);
pn_lease_pvt.insert_tenancies_row (p_lease_tenancies_rec => p_lease_tenancies_tbl(i)
, p_lease_rec => p_lease_rec
, p_lease_context => p_lease_context
, x_return_status => x_return_status);
PROCEDURE update_tenancies_row (
p_lease_tenancies_rec IN OUT NOCOPY lease_tenancies_rec
, p_lease_id IN NUMBER
, p_lease_context IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2)
IS
l_tenancy_ovelap_wrn VARCHAR2 (30) := NULL;
SELECT nvl(min(allocated_area_pct),0) allocated_area_pct
FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id;
pvt_debug(g_pkg_name||'-UPDATE_TENANCIES_ROW:Tenancy ID'||p_lease_tenancies_rec.tenancy_id,3);
pn_tenancies_pkg.update_row
(x_tenancy_id => p_lease_tenancies_rec.tenancy_id
, x_location_id => p_lease_tenancies_rec.location_id
, x_lease_id => p_lease_id
, x_lease_change_id => p_lease_tenancies_rec.lease_change_id
, x_tenancy_usage_lookup_code => p_lease_tenancies_rec.tenancy_usage_lookup_code
, x_primary_flag => p_lease_tenancies_rec.primary_flag
, x_estimated_occupancy_date => p_lease_tenancies_rec.estimated_occupancy_date
, x_occupancy_date => p_lease_tenancies_rec.occupancy_date
, x_expiration_date => p_lease_tenancies_rec.expiration_date
, x_assignable_flag => p_lease_tenancies_rec.assignable_flag
, x_subleaseable_flag => p_lease_tenancies_rec.subleaseable_flag
, x_tenants_proportionate_share => p_lease_tenancies_rec.tenants_proportionate_share
, x_allocated_area_pct => p_lease_tenancies_rec.allocated_area_pct
, x_allocated_area => p_lease_tenancies_rec.allocated_area
, x_status => p_lease_tenancies_rec.status
, x_attribute_category => p_lease_tenancies_rec.attribute_category
, x_attribute1 => p_lease_tenancies_rec.attribute1
, x_attribute2 => p_lease_tenancies_rec.attribute2
, x_attribute3 => p_lease_tenancies_rec.attribute3
, x_attribute4 => p_lease_tenancies_rec.attribute4
, x_attribute5 => p_lease_tenancies_rec.attribute5
, x_attribute6 => p_lease_tenancies_rec.attribute6
, x_attribute7 => p_lease_tenancies_rec.attribute7
, x_attribute8 => p_lease_tenancies_rec.attribute8
, x_attribute9 => p_lease_tenancies_rec.attribute9
, x_attribute10 => p_lease_tenancies_rec.attribute10
, x_attribute11 => p_lease_tenancies_rec.attribute11
, x_attribute12 => p_lease_tenancies_rec.attribute12
, x_attribute13 => p_lease_tenancies_rec.attribute13
, x_attribute14 => p_lease_tenancies_rec.attribute14
, x_attribute15 => p_lease_tenancies_rec.attribute15
, x_last_update_date => SYSDATE
, x_last_updated_by => g_user_id
, x_last_update_login => fnd_global.login_id
, x_tenancy_ovelap_wrn => l_tenancy_ovelap_wrn
, x_recovery_type_code => p_lease_tenancies_rec.recovery_type_code
, x_recovery_space_std_code => p_lease_tenancies_rec.recovery_space_std_code
, x_fin_oblig_end_date => p_lease_tenancies_rec.fin_oblig_end_date
, x_customer_id => p_lease_tenancies_rec.customer_id
, x_customer_site_use_id => p_lease_tenancies_rec.customer_site_use_id
, x_lease_rentable_area => p_lease_tenancies_rec.lease_rentable_area
, x_lease_usable_area => p_lease_tenancies_rec.lease_usable_area
, x_lease_assignable_area => p_lease_tenancies_rec.lease_assignable_area
, x_lease_load_factor => p_lease_tenancies_rec.lease_load_factor
, x_location_rentable_area => p_lease_tenancies_rec.location_rentable_area
, x_location_usable_area => p_lease_tenancies_rec.location_usable_area
, x_location_assignable_area => p_lease_tenancies_rec.location_assignable_area
, x_location_load_factor => p_lease_tenancies_rec.location_load_factor);
pvt_debug(g_pkg_name||'-UPDATE_TENANCIES_ROW:After Updating the Tenancy Record'||p_lease_tenancies_rec.tenancy_id,3);
pvt_debug(g_pkg_name||'-UPDATE_TENANCIES_ROW:Exception while updating:'||SQLERRM,3);
END update_tenancies_row;
PROCEDURE update_contacts_row (
p_lease_contacts_rec IN OUT NOCOPY lease_contacts_rec
, p_lease_id IN NUMBER
, p_lease_context IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
pvt_debug(g_pkg_name||'-UPDATE_CONTACTS_ROW:Contact Assignment ID'||p_lease_contacts_rec.contact_assignment_id,3);
pn_contact_assignments_pkg.update_row
(x_contact_assignment_id => p_lease_contacts_rec.contact_assignment_id
, x_last_update_date => SYSDATE
, x_last_updated_by => g_user_id
, x_last_update_login => fnd_global.login_id
, x_company_id => p_lease_contacts_rec.company_id
, x_company_site_id => p_lease_contacts_rec.company_site_id
, x_lease_id => p_lease_contacts_rec.lease_id
, x_lease_change_id => p_lease_contacts_rec.lease_change_id
, x_location_id => p_lease_contacts_rec.location_id
, x_status => p_lease_contacts_rec.status
, x_attribute_category => p_lease_contacts_rec.attribute_category
, x_attribute1 => p_lease_contacts_rec.attribute1
, x_attribute2 => p_lease_contacts_rec.attribute2
, x_attribute3 => p_lease_contacts_rec.attribute3
, x_attribute4 => p_lease_contacts_rec.attribute4
, x_attribute5 => p_lease_contacts_rec.attribute5
, x_attribute6 => p_lease_contacts_rec.attribute6
, x_attribute7 => p_lease_contacts_rec.attribute7
, x_attribute8 => p_lease_contacts_rec.attribute8
, x_attribute9 => p_lease_contacts_rec.attribute9
, x_attribute10 => p_lease_contacts_rec.attribute10
, x_attribute11 => p_lease_contacts_rec.attribute11
, x_attribute12 => p_lease_contacts_rec.attribute12
, x_attribute13 => p_lease_contacts_rec.attribute13
, x_attribute14 => p_lease_contacts_rec.attribute14
, x_attribute15 => p_lease_contacts_rec.attribute15
);
pvt_debug(g_pkg_name||'-UPDATE_CONTACTS_ROW:Update of the Contact Record Successful'||p_lease_contacts_rec.contact_assignment_id,3);
pvt_debug(g_pkg_name||'-UPDATE_CONTACTS_ROW:Exception in update of the Contact Record'||SQLERRM,3);
END update_contacts_row;
PROCEDURE update_tenancies (
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validate IN VARCHAR2 := fnd_api.g_false
, p_lease_tenancies_tbl IN OUT NOCOPY lease_tenancies_tbl
, p_lease_id IN NUMBER
, p_lease_change_id IN NUMBER
, p_lease_context IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR cur_tenancies_details (
p_tenancy_id NUMBER)
IS
SELECT *
FROM pn_tenancies_all
WHERE tenancy_id = p_tenancy_id;
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Tenancies';
p_lease_tenancies_tbl_update lease_tenancies_tbl;
pvt_debug(g_pkg_name||'-UPDATE_TENANCIES_TBL:Procedure Begin',3);
SELECT pla.lease_class_code
, pld.lease_commencement_date
, pld.lease_termination_date
, pla.parent_lease_id
INTO l_lease_rec.lease_class_code
, l_lease_rec.lease_commencement_date
, l_lease_rec.lease_termination_date
, l_lease_rec.parent_lease_id
FROM pn_leases_all pla
, pn_lease_details_all pld
WHERE pla.lease_id = p_lease_id
AND pla.lease_id = pld.lease_id
AND pld.org_id = g_org_id
AND pla.org_id = g_org_id;
pvt_debug(g_pkg_name||'-UPDATE_TENANCIES:Before Validating the tenancy table records',3);
pvt_debug(g_pkg_name||'-update_contacts:Assigning the tenancy table values to other table',3);
p_lease_tenancies_tbl_update(q) := p_lease_tenancies_tbl (i);
IF (p_lease_tenancies_tbl_update.COUNT > 0) THEN
validate_tenancies_tbl (p_lease_tenancies_tbl => p_lease_tenancies_tbl_update
, p_lease_rec => l_lease_rec
, p_validate => p_validate
, p_lease_context => p_lease_context
, p_operation => 'UPDATE_TENANCY'
, x_return_status => x_return_status
);
FOR i IN p_lease_tenancies_tbl_update.FIRST .. p_lease_tenancies_tbl_update.LAST
LOOP
IF(p_lease_tenancies_tbl_update (i).tenancy_id IS NOT NULL OR p_lease_tenancies_tbl_update (i).tenancy_id <> pn_lease_utils.g_pn_miss_num) THEN
l_assign_flag := 'N';
IF (p_lease_tenancies_tbl_update(i).error_flag = 'N') THEN
----------------------dbms_output.put_line('In Update tenancies:in TABLE TYPE:p_lease_id'||p_lease_id);--Rupak
pvt_debug(g_pkg_name||'-UPDATE_TENANCIES:In Table Type',3);
FOR r_tenancies_details IN cur_tenancies_details (p_lease_tenancies_tbl_update (i).tenancy_id)
LOOP
BEGIN
----------------------dbms_output.put_line('r_tenancies_details.location_id'||r_tenancies_details.location_id);--Rupak
pvt_debug(g_pkg_name||'-UPDATE_TENANCIES:R_TENANCIES_DETAILS.LOCATION_ID'||r_tenancies_details.location_id,3);
IF ( (r_tenancies_details.tenancy_id = p_lease_tenancies_tbl_update (i).tenancy_id)
AND (l_assign_flag = 'N'))
THEN
----------------------dbms_output.put_line('In Update Tenancy:in IF CONDITION:p_lease_id'||p_lease_id);--Rupak
IF ( p_lease_tenancies_tbl_update (i).location_id IS NULL
OR p_lease_tenancies_tbl_update (i).location_id = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).location_id := r_tenancies_details.location_id;
IF ( p_lease_tenancies_tbl_update (i).tenancy_usage_lookup_code IS NULL
OR p_lease_tenancies_tbl_update (i).tenancy_usage_lookup_code = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).tenancy_usage_lookup_code := r_tenancies_details.tenancy_usage_lookup_code;
IF ( p_lease_tenancies_tbl_update (i).primary_flag IS NULL
OR p_lease_tenancies_tbl_update (i).primary_flag = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).primary_flag := r_tenancies_details.primary_flag;
IF ( p_lease_tenancies_tbl_update (i).estimated_occupancy_date IS NULL
OR p_lease_tenancies_tbl_update (i).estimated_occupancy_date = pn_lease_utils.g_pn_miss_date)
THEN
p_lease_tenancies_tbl_update (i).estimated_occupancy_date := r_tenancies_details.estimated_occupancy_date;
IF ( p_lease_tenancies_tbl_update (i).occupancy_date IS NULL
OR p_lease_tenancies_tbl_update (i).occupancy_date = pn_lease_utils.g_pn_miss_date)
THEN
p_lease_tenancies_tbl_update (i).occupancy_date := r_tenancies_details.occupancy_date;
IF ( p_lease_tenancies_tbl_update (i).expiration_date IS NULL
OR p_lease_tenancies_tbl_update (i).expiration_date = pn_lease_utils.g_pn_miss_date)
THEN
p_lease_tenancies_tbl_update (i).expiration_date := r_tenancies_details.expiration_date;
IF ( p_lease_tenancies_tbl_update (i).assignable_flag IS NULL
OR p_lease_tenancies_tbl_update (i).assignable_flag = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).assignable_flag := r_tenancies_details.assignable_flag;
IF ( p_lease_tenancies_tbl_update (i).subleaseable_flag IS NULL
OR p_lease_tenancies_tbl_update (i).subleaseable_flag = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).subleaseable_flag := r_tenancies_details.subleaseable_flag;
IF ( p_lease_tenancies_tbl_update (i).tenants_proportionate_share IS NULL
OR p_lease_tenancies_tbl_update (i).tenants_proportionate_share = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).tenants_proportionate_share := r_tenancies_details.tenants_proportionate_share;
IF ( p_lease_tenancies_tbl_update (i).status IS NULL
OR p_lease_tenancies_tbl_update (i).status = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).status := r_tenancies_details.status;
IF ( p_lease_tenancies_tbl_update (i).recovery_type_code IS NULL
OR p_lease_tenancies_tbl_update (i).recovery_type_code = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).recovery_type_code := r_tenancies_details.recovery_type_code;
IF ( p_lease_tenancies_tbl_update (i).recovery_space_std_code IS NULL
OR p_lease_tenancies_tbl_update (i).recovery_space_std_code = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).recovery_space_std_code := r_tenancies_details.recovery_space_std_code;
IF ( p_lease_tenancies_tbl_update (i).fin_oblig_end_date IS NULL
OR p_lease_tenancies_tbl_update (i).fin_oblig_end_date = pn_lease_utils.g_pn_miss_date)
THEN
p_lease_tenancies_tbl_update (i).fin_oblig_end_date := r_tenancies_details.fin_oblig_end_date;
IF ( p_lease_tenancies_tbl_update (i).customer_site_use_id IS NULL
OR p_lease_tenancies_tbl_update (i).customer_site_use_id = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).customer_site_use_id := r_tenancies_details.customer_site_use_id;
IF ( p_lease_tenancies_tbl_update (i).lease_rentable_area IS NULL
OR p_lease_tenancies_tbl_update (i).lease_rentable_area = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).lease_rentable_area := r_tenancies_details.lease_rentable_area;
IF ( p_lease_tenancies_tbl_update (i).lease_usable_area IS NULL
OR p_lease_tenancies_tbl_update (i).lease_usable_area = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).lease_usable_area := r_tenancies_details.lease_usable_area;
IF ( p_lease_tenancies_tbl_update (i).lease_assignable_area IS NULL
OR p_lease_tenancies_tbl_update (i).lease_assignable_area = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).lease_assignable_area := r_tenancies_details.lease_assignable_area;
IF ( p_lease_tenancies_tbl_update (i).lease_load_factor IS NULL
OR p_lease_tenancies_tbl_update (i).lease_load_factor = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).lease_load_factor := r_tenancies_details.lease_load_factor;
IF ( p_lease_tenancies_tbl_update (i).location_rentable_area IS NULL
OR p_lease_tenancies_tbl_update (i).location_rentable_area = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).location_rentable_area := r_tenancies_details.location_rentable_area;
IF ( p_lease_tenancies_tbl_update (i).location_usable_area IS NULL
OR p_lease_tenancies_tbl_update (i).location_usable_area = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).location_usable_area := r_tenancies_details.location_usable_area;
IF ( p_lease_tenancies_tbl_update (i).location_assignable_area IS NULL
OR p_lease_tenancies_tbl_update (i).location_assignable_area = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).location_assignable_area := r_tenancies_details.location_assignable_area;
IF ( p_lease_tenancies_tbl_update (i).location_load_factor IS NULL
OR p_lease_tenancies_tbl_update (i).location_load_factor = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).location_load_factor := r_tenancies_details.location_load_factor;
IF ( p_lease_tenancies_tbl_update (i).allocated_area IS NULL
OR p_lease_tenancies_tbl_update (i).allocated_area = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).allocated_area := r_tenancies_details.allocated_area;
IF ( p_lease_tenancies_tbl_update (i).allocated_area_pct IS NULL
OR p_lease_tenancies_tbl_update (i).allocated_area_pct = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).allocated_area_pct := r_tenancies_details.allocated_area_pct;
IF ( p_lease_tenancies_tbl_update (i).lease_change_id IS NULL
OR p_lease_tenancies_tbl_update (i).lease_change_id = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_tenancies_tbl_update (i).lease_change_id := r_tenancies_details.lease_change_id;
IF NOT ((p_lease_tenancies_tbl_update(i).attribute_category = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute1 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute2 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute3 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute4 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute5 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute6 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute7 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute8 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute9 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute10 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute11 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute12 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute13 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute14 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_tenancies_tbl_update(i).attribute15 = pn_lease_utils.g_pn_miss_char)
)
THEN
IF (p_lease_tenancies_tbl_update (i).attribute_category = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute_category := r_tenancies_details.attribute_category;
IF (p_lease_tenancies_tbl_update (i).attribute1 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute1 := r_tenancies_details.attribute1;
IF (p_lease_tenancies_tbl_update (i).attribute2 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute2 := r_tenancies_details.attribute2;
IF (p_lease_tenancies_tbl_update (i).attribute3 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute3 := r_tenancies_details.attribute3;
IF (p_lease_tenancies_tbl_update (i).attribute4 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute4 := r_tenancies_details.attribute4;
IF (p_lease_tenancies_tbl_update (i).attribute5 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute5 := r_tenancies_details.attribute5;
IF (p_lease_tenancies_tbl_update (i).attribute6 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute6 := r_tenancies_details.attribute6;
IF (p_lease_tenancies_tbl_update (i).attribute7 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute7 := r_tenancies_details.attribute7;
IF (p_lease_tenancies_tbl_update (i).attribute8 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute8 := r_tenancies_details.attribute8;
IF (p_lease_tenancies_tbl_update (i).attribute9 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute9 := r_tenancies_details.attribute9;
IF (p_lease_tenancies_tbl_update (i).attribute10 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute10 := r_tenancies_details.attribute10;
IF (p_lease_tenancies_tbl_update (i).attribute11 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute11 := r_tenancies_details.attribute11;
IF (p_lease_tenancies_tbl_update (i).attribute12 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute12 := r_tenancies_details.attribute12;
IF (p_lease_tenancies_tbl_update (i).attribute13 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute13 := r_tenancies_details.attribute13;
IF (p_lease_tenancies_tbl_update (i).attribute14 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute14 := r_tenancies_details.attribute14;
IF (p_lease_tenancies_tbl_update (i).attribute15 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_tenancies_tbl_update (i).attribute15 := r_tenancies_details.attribute15;
, p_desc_segment1 => p_lease_tenancies_tbl_update (i).attribute1
, p_desc_segment2 => p_lease_tenancies_tbl_update (i).attribute2
, p_desc_segment3 => p_lease_tenancies_tbl_update (i).attribute3
, p_desc_segment4 => p_lease_tenancies_tbl_update (i).attribute4
, p_desc_segment5 => p_lease_tenancies_tbl_update (i).attribute5
, p_desc_segment6 => p_lease_tenancies_tbl_update (i).attribute6
, p_desc_segment7 => p_lease_tenancies_tbl_update (i).attribute7
, p_desc_segment8 => p_lease_tenancies_tbl_update (i).attribute8
, p_desc_segment9 => p_lease_tenancies_tbl_update (i).attribute9
, p_desc_segment10 => p_lease_tenancies_tbl_update (i).attribute10
, p_desc_segment11 => p_lease_tenancies_tbl_update (i).attribute11
, p_desc_segment12 => p_lease_tenancies_tbl_update (i).attribute12
, p_desc_segment13 => p_lease_tenancies_tbl_update (i).attribute13
, p_desc_segment14 => p_lease_tenancies_tbl_update (i).attribute14
, p_desc_segment15 => p_lease_tenancies_tbl_update (i).attribute15
, p_desc_context => p_lease_tenancies_tbl_update (i).attribute_category
, p_resp_appl_id => fnd_global.resp_appl_id
, p_resp_id => fnd_global.resp_id
, p_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-UPDATE_TENANCIES:Calling UPDATE_TENANCIES_ROW',3);
update_tenancies_row (p_lease_tenancies_rec => p_lease_tenancies_tbl_update (i)
, p_lease_id => p_lease_id
, p_lease_context => p_lease_context
, x_return_status => x_return_status
);
(nvl(p_lease_tenancies_tbl_update(i).location_id,-99) <> nvl(c_loc_rec.location_id,-99) OR
(nvl(p_lease_tenancies_tbl_update(i).occupancy_date,p_lease_tenancies_tbl_update(i).estimated_occupancy_date) <>
nvl(c_loc_rec.occupancy_date,c_loc_rec.estimated_occupancy_date)) OR
nvl(p_lease_tenancies_tbl_update(i).expiration_date,sysdate) <> nvl(c_loc_rec.expiration_date,sysdate) OR
nvl(p_lease_tenancies_tbl_update(i).customer_id,-99) <> nvl(c_loc_rec.customer_id,-99) OR
nvl(p_lease_tenancies_tbl_update(i).customer_site_use_id,-99) <> nvl(c_loc_rec.customer_site_use_id,-99) OR
nvl(p_lease_tenancies_tbl_update(i).recovery_type_code,' ') <> nvl(c_loc_rec.recovery_type_code,' ') OR
nvl(p_lease_tenancies_tbl_update(i).recovery_space_std_code,' ') <> nvl(c_loc_rec.recovery_space_std_code,' ') OR
nvl(p_lease_tenancies_tbl_update(i).fin_oblig_end_date,sysdate) <> nvl(c_loc_rec.fin_oblig_end_date,sysdate) OR
nvl(p_lease_tenancies_tbl_update(i).allocated_area_pct,-99) <> nvl(c_loc_rec.allocated_area_pct,-99)) THEN
pvt_debug(g_pkg_name||'-VALIDATE_TENANCIES:Calling Update_Space_Assign Begin',3);
pn_lease_pvt.Update_Space_Assign( p_lease_tenancies_rec => p_lease_tenancies_tbl_update(i)
, x_return_status => x_return_status);
pvt_debug(g_pkg_name||'-VALIDATE_TENANCIES:Calling Update_Space_Assign End with Status '|| x_return_status,3);
pvt_debug(g_pkg_name||'-update_tenancies:LOOOPING:EXCEPTION:FND_API.G_EXC_ERROR'||SQLERRM,3);
pvt_debug(g_pkg_name||'-update_tenancies:LOOOPING:EXCEPTION:'||SQLERRM,3);
pvt_debug(g_pkg_name||'-update_contacts:Assigning the tenancy table values to other table',3);
pvt_debug(g_pkg_name||'-update_contacts:Inserting the values of tenancy record as ID is not passed for this record',3);
pvt_debug(g_pkg_name||'-update_tenancies:Inserting the values of tenancy record return Status'||x_return_status,3);
pvt_debug(g_pkg_name||'-update_tenancies:the temperory table is with out any records',3);
pvt_debug(g_pkg_name||'-update_tenancies:MAIN:EXCEPTION'||SQLERRM,3);
END update_tenancies;
PROCEDURE update_contacts (
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validate IN VARCHAR2 := fnd_api.g_false
, p_lease_contacts_tbl IN OUT NOCOPY lease_contacts_tbl
, p_lease_id IN NUMBER
, p_lease_change_id IN NUMBER
, p_lease_context IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR cur_contacts_details (l_lease_id NUMBER)
IS
SELECT *
FROM pn_contact_assignments_all
WHERE lease_id = p_lease_id;
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_CONTACTS';
p_lease_contacts_tbl_update lease_contacts_tbl;
pvt_debug(g_pkg_name||'-UPDATE_CONTACTS:Calling VALIDATE_CONTACTS_TBL',3);
pvt_debug(g_pkg_name||'-update_contacts:Assigning the contact table values to other table',3);
p_lease_contacts_tbl_update(q) := p_lease_contacts_tbl (i);
IF(p_lease_contacts_tbl_update.COUNT > 0) THEN
validate_contacts_tbl (p_lease_contacts_tbl => p_lease_contacts_tbl_update
, p_lease_rec => l_lease_rec
, p_validate => p_validate
, p_lease_context => p_lease_context
, p_operation => 'UPDATE_CONTACTS'
, x_return_status => x_return_status);
l_contact_record_cnt := p_lease_contacts_tbl_update.COUNT;
FOR i IN p_lease_contacts_tbl_update.FIRST .. p_lease_contacts_tbl_update.LAST
LOOP
IF(p_lease_contacts_tbl_update (i).contact_assignment_id IS NOT NULL OR p_lease_contacts_tbl_update (i).contact_assignment_id <> pn_lease_utils.g_pn_miss_num) THEN
l_assign_flag := 'N';
IF (p_lease_contacts_tbl_update (i).error_flag = 'N') THEN
------------------------dbms_output.put_line('In Update Contacts:in TABLE TYPE:p_lease_id'||p_lease_id);
IF ((r_contact_details.contact_assignment_id =p_lease_contacts_tbl_update (i).contact_assignment_id) AND (l_assign_flag = 'N')) THEN
----------------------dbms_output.put_line ( 'In Update Contacts:in IF CONDITION:p_lease_id'|| p_lease_id);
IF ( p_lease_contacts_tbl_update (i).company_id IS NULL
OR p_lease_contacts_tbl_update (i).company_id = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_contacts_tbl_update (i).company_id := r_contact_details.company_id;
IF ( p_lease_contacts_tbl_update (i).company_site_id IS NULL
OR p_lease_contacts_tbl_update (i).company_site_id = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_contacts_tbl_update (i).company_site_id := r_contact_details.company_site_id;
IF ( p_lease_contacts_tbl_update (i).location_id IS NULL
OR p_lease_contacts_tbl_update (i).location_id = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_contacts_tbl_update (i).location_id := r_contact_details.location_id;
IF ( p_lease_contacts_tbl_update (i).status IS NULL
OR p_lease_contacts_tbl_update (i).status = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).status := r_contact_details.status;
IF ( p_lease_contacts_tbl_update (i).lease_change_id IS NULL
OR p_lease_contacts_tbl_update (i).lease_change_id = pn_lease_utils.g_pn_miss_num)
THEN
p_lease_contacts_tbl_update (i).lease_change_id := r_contact_details.lease_change_id;
p_lease_contacts_tbl_update (i).lease_id := p_lease_id;
IF NOT ((p_lease_contacts_tbl_update(i).attribute_category = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute1 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute2 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute3 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute4 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute5 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute6 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute7 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute8 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute9 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute10 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute11 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute12 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute13 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute14 = pn_lease_utils.g_pn_miss_char)
AND (p_lease_contacts_tbl_update(i).attribute15 = pn_lease_utils.g_pn_miss_char)
)
THEN
------------------------dbms_output.put_line('IN CONTACT ATTRIBUTES');
IF (p_lease_contacts_tbl_update (i).attribute_category = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute_category := NULL;
p_lease_contacts_tbl_update (i).attribute_category := r_contact_details.attribute_category;
IF (p_lease_contacts_tbl_update (i).attribute1 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute1 := NULL;
p_lease_contacts_tbl_update (i).attribute1 := r_contact_details.attribute1;
IF (p_lease_contacts_tbl_update (i).attribute2 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute2 := NULL;
p_lease_contacts_tbl_update (i).attribute2 := r_contact_details.attribute2;
IF (p_lease_contacts_tbl_update (i).attribute3 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute3 := NULL;
p_lease_contacts_tbl_update (i).attribute3 := r_contact_details.attribute3;
IF (p_lease_contacts_tbl_update (i).attribute4 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute4 := NULL;
p_lease_contacts_tbl_update (i).attribute4 := r_contact_details.attribute4;
IF (p_lease_contacts_tbl_update (i).attribute5 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute5 := NULL;
p_lease_contacts_tbl_update (i).attribute5 := r_contact_details.attribute5;
IF (p_lease_contacts_tbl_update (i).attribute6 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute6 := NULL;
p_lease_contacts_tbl_update (i).attribute6 := r_contact_details.attribute6;
IF (p_lease_contacts_tbl_update (i).attribute7 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute7 := NULL;
p_lease_contacts_tbl_update (i).attribute7 := r_contact_details.attribute7;
IF (p_lease_contacts_tbl_update (i).attribute8 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute8 := NULL;
p_lease_contacts_tbl_update (i).attribute8 := r_contact_details.attribute8;
IF (p_lease_contacts_tbl_update (i).attribute9 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute9 := NULL;
p_lease_contacts_tbl_update (i).attribute9 := r_contact_details.attribute9;
IF (p_lease_contacts_tbl_update (i).attribute10 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute10 := NULL;
p_lease_contacts_tbl_update (i).attribute10 := r_contact_details.attribute10;
IF (p_lease_contacts_tbl_update (i).attribute11 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute11 := NULL;
p_lease_contacts_tbl_update (i).attribute11 := r_contact_details.attribute11;
IF (p_lease_contacts_tbl_update (i).attribute12 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute12 := NULL;
p_lease_contacts_tbl_update (i).attribute12 := r_contact_details.attribute12;
IF (p_lease_contacts_tbl_update (i).attribute13 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute13 := NULL;
p_lease_contacts_tbl_update (i).attribute13 := r_contact_details.attribute13;
IF (p_lease_contacts_tbl_update (i).attribute14 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute14 := NULL;
p_lease_contacts_tbl_update (i).attribute14 := r_contact_details.attribute14;
IF (p_lease_contacts_tbl_update (i).attribute15 = pn_lease_utils.g_pn_miss_char)
THEN
p_lease_contacts_tbl_update (i).attribute15 := NULL;
p_lease_contacts_tbl_update (i).attribute15 := r_contact_details.attribute15;
, p_desc_segment1 => p_lease_contacts_tbl_update (i).attribute1
, p_desc_segment2 => p_lease_contacts_tbl_update (i).attribute2
, p_desc_segment3 => p_lease_contacts_tbl_update (i).attribute3
, p_desc_segment4 => p_lease_contacts_tbl_update (i).attribute4
, p_desc_segment5 => p_lease_contacts_tbl_update (i).attribute5
, p_desc_segment6 => p_lease_contacts_tbl_update (i).attribute6
, p_desc_segment7 => p_lease_contacts_tbl_update (i).attribute7
, p_desc_segment8 => p_lease_contacts_tbl_update (i).attribute8
, p_desc_segment9 => p_lease_contacts_tbl_update (i).attribute9
, p_desc_segment10 => p_lease_contacts_tbl_update (i).attribute10
, p_desc_segment11 => p_lease_contacts_tbl_update (i).attribute11
, p_desc_segment12 => p_lease_contacts_tbl_update (i).attribute12
, p_desc_segment13 => p_lease_contacts_tbl_update (i).attribute13
, p_desc_segment14 => p_lease_contacts_tbl_update (i).attribute14
, p_desc_segment15 => p_lease_contacts_tbl_update (i).attribute15
, p_desc_context => p_lease_contacts_tbl_update (i).attribute_category
, p_resp_appl_id => fnd_global.resp_appl_id
,p_resp_id => fnd_global.resp_id
, p_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-UPDATE_CONTACTS:Initialize the values to NULL and Call the UPDATE_CONTACTS_ROW',3);
INIT_CONTACTS_REC( p_lease_contacts_rec => p_lease_contacts_tbl_update(i)
, p_lease_context => p_lease_context
, x_return_status => x_return_status
);
update_contacts_row (p_lease_contacts_rec => p_lease_contacts_tbl_update (i)
, p_lease_id => p_lease_id
, p_lease_context => p_lease_context
, x_return_status => x_return_status);
pvt_debug(g_pkg_name||'-update_contacts:LOOOPING:EXCEPTION:FND_API.G_EXC_ERROR'||SQLERRM,3);
pvt_debug(g_pkg_name||'-update_contacts:LOOOPING:EXCEPTION:'||SQLERRM,3);
pvt_debug(g_pkg_name||'-update_contacts:Assigning the contact table values to other table',3);
pvt_debug(g_pkg_name||'-update_contacts:Inserting the values of contact record as ID is not passed for this record',3);
pvt_debug(g_pkg_name||'-update_contacts:Inserting the values of contact record return Status'||x_return_status,3);
pvt_debug(g_pkg_name||'-update_contacts:MAIN:EXCEPTION'||SQLERRM,3);
END update_contacts;
/* Update Lease API*/
PROCEDURE direct_update_lease (
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
, p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_lease_id IN NUMBER
, p_lease_detail_id IN NUMBER
, p_lease_change_id IN NUMBER
, p_name IN VARCHAR2
, p_lease_num IN VARCHAR2
, p_parent_lease_id IN NUMBER
, p_lease_type_code IN VARCHAR2
, p_lease_class_code IN VARCHAR2
, p_payment_term_proration_rule IN NUMBER
, p_abstracted_by_user IN NUMBER
, p_status IN VARCHAR2
, p_lease_status IN VARCHAR2
, p_last_update_date IN DATE
, p_last_updated_by IN NUMBER
, p_last_update_login IN NUMBER
, p_responsible_user IN NUMBER
, p_expense_account_id IN NUMBER
, p_accrual_account_id IN NUMBER
, p_receivable_account_id IN NUMBER
, p_term_template_id IN NUMBER
, p_lease_commencement_date IN DATE
, p_lease_termination_date IN DATE
, p_lease_execution_date IN DATE
, p_cal_start IN VARCHAR2
, p_attribute_category IN VARCHAR2
, p_attribute1 IN VARCHAR2
, p_attribute2 IN VARCHAR2
, p_attribute3 IN VARCHAR2
, p_attribute4 IN VARCHAR2
, p_attribute5 IN VARCHAR2
, p_attribute6 IN VARCHAR2
, p_attribute7 IN VARCHAR2
, p_attribute8 IN VARCHAR2
, p_attribute9 IN VARCHAR2
, p_attribute10 IN VARCHAR2
, p_attribute11 IN VARCHAR2
, p_attribute12 IN VARCHAR2
, p_attribute13 IN VARCHAR2
, p_attribute14 IN VARCHAR2
, p_attribute15 IN VARCHAR2
, p_location_id IN NUMBER
, p_customer_id IN NUMBER
, p_grouping_rule_id IN NUMBER DEFAULT NULL
, p_lease_extension_end_date IN DATE)
AS
l_leasecommencementdate DATE := NULL;
l_lastupdatedate DATE := NULL;
SAVEPOINT update_lease_pvt;
pvt_debug(g_pkg_name||'-DIRECT_UPDATE_LEASE:Updating the Lease Record by calling the standard Package',3);
l_lastupdatedate := p_last_update_date;
pn_leases_pkg.update_row (x_lease_id => p_lease_id
, x_lease_detail_id => p_lease_detail_id
, x_lease_change_id => l_leasechangeid
, x_name => p_name
, x_lease_num => p_lease_num
, x_parent_lease_id => p_parent_lease_id
, x_lease_type_code => p_lease_type_code
, x_lease_class_code => p_lease_class_code
, x_payment_term_proration_rule => p_payment_term_proration_rule
, x_abstracted_by_user => p_abstracted_by_user
, x_status => p_status
, x_lease_status => p_lease_status
, x_last_update_date => sysdate
, x_last_updated_by => fnd_global.user_id
, x_last_update_login => fnd_global.login_id
, x_responsible_user => p_responsible_user
, x_expense_account_id => p_expense_account_id
, x_accrual_account_id => p_accrual_account_id
, x_receivable_account_id => p_receivable_account_id
, x_term_template_id => p_term_template_id
, x_lease_commencement_date => l_leasecommencementdate
, x_lease_termination_date => l_leaseterminationdate
, x_lease_execution_date => l_leaseexecutiondate
, x_lease_extension_end_date => l_leaseextensionenddate
, x_calendar_year_start_date => p_cal_start
, x_attribute_category => p_attribute_category
, x_attribute1 => p_attribute1
, x_attribute2 => p_attribute2
, x_attribute3 => p_attribute3
, x_attribute4 => p_attribute4
, x_attribute5 => p_attribute5
, x_attribute6 => p_attribute6
, x_attribute7 => p_attribute7
, x_attribute8 => p_attribute8
, x_attribute9 => p_attribute9
, x_attribute10 => p_attribute10
, x_attribute11 => p_attribute11
, x_attribute12 => p_attribute12
, x_attribute13 => p_attribute13
, x_attribute14 => p_attribute14
, x_attribute15 => p_attribute15
, x_location_id => p_location_id
, x_customer_id => p_customer_id
, x_grouping_rule_id => p_grouping_rule_id
);
pvt_debug(g_pkg_name||'-DIRECT_UPDATE_LEASE:Update of the Record Successful',3);
ROLLBACK TO update_lease_pvt;
ROLLBACK TO update_lease_pvt;
fnd_message.set_token ('P_TEXT', 'pn_lease_pvt.direct_update_lease'|| SQLERRM);
END direct_update_lease;
, p_last_update_date IN DATE DEFAULT SYSDATE
, p_user_id IN NUMBER
, x_lease_change_id OUT NOCOPY NUMBER
, x_lease_change_num OUT NOCOPY NUMBER)
IS
l_rowid VARCHAR2 (18) := NULL;
l_lastupdatedate DATE := p_last_update_date;
l_lastupdatedate := p_last_update_date;
pn_lease_changes_pkg.insert_row (x_rowid => l_rowid
, x_lease_change_id => l_leasechangeid
, x_lease_id => p_lease_id
, x_lease_change_number => l_leasechangenumber
, x_lease_change_name => p_lease_name
, x_responsible_user => p_responsible_user
, x_change_commencement_date => l_changecommencementdate
, x_change_termination_date => l_changeterminationdate
, x_change_type_lookup_code => 'AMEND'
, x_change_execution_date => l_changeexecutiondate
, x_attribute_category => p_attribute_category
, x_attribute1 => p_attribute1
, x_attribute2 => p_attribute2
, x_attribute3 => p_attribute3
, x_attribute4 => p_attribute4
, x_attribute5 => p_attribute5
, x_attribute6 => p_attribute6
, x_attribute7 => p_attribute7
, x_attribute8 => p_attribute8
, x_attribute9 => p_attribute9
, x_attribute10 => p_attribute10
, x_attribute11 => p_attribute11
, x_attribute12 => p_attribute12
, x_attribute13 => p_attribute13
, x_attribute14 => p_attribute14
, x_attribute15 => p_attribute15
, x_abstracted_by_user => p_abstracted_by_user
, x_creation_date => l_creationdate
, x_created_by => p_user_id
, x_last_update_date => l_lastupdatedate
, x_last_updated_by => p_user_id
, x_last_update_login => fnd_global.login_id
, x_org_id => l_org_id
, x_cutoff_date => NULL);
pvt_debug(g_pkg_name||'-AMEND_LEASE:Insertion into the table successful',3);
, p_last_update_date IN DATE DEFAULT SYSDATE
, x_lease_change_id OUT NOCOPY NUMBER
, x_lease_change_num OUT NOCOPY NUMBER)
IS
l_rowid VARCHAR2 (18) := NULL;
l_lastupdatedate DATE := p_last_update_date;
pn_lease_changes_pkg.insert_row (x_rowid => l_rowid
, x_lease_change_id => l_leasechangeid
, x_lease_id => p_lease_id
, x_lease_change_number => l_leasechangenumber
, x_lease_change_name => 'EDIT'
, x_responsible_user => NULL
, x_change_commencement_date => NULL
, x_change_termination_date => NULL
, x_change_type_lookup_code => 'EDIT'
, x_change_execution_date => NULL
, x_attribute_category => NULL
, x_attribute1 => NULL
, x_attribute2 => NULL
, x_attribute3 => NULL
, x_attribute4 => NULL
, x_attribute5 => NULL
, x_attribute6 => NULL
, x_attribute7 => NULL
, x_attribute8 => NULL
, x_attribute9 => NULL
, x_attribute10 => NULL
, x_attribute11 => NULL
, x_attribute12 => NULL
, x_attribute13 => NULL
, x_attribute14 => NULL
, x_attribute15 => NULL
, x_abstracted_by_user => NULL
, x_creation_date => l_creationdate
, x_created_by => fnd_global.user_id
, x_last_update_date => l_lastupdatedate
, x_last_updated_by => fnd_global.user_id
, x_last_update_login => fnd_global.login_id
, x_org_id => l_org_id);
pvt_debug(g_pkg_name||'-EDIT_LEASE:Insertion into the table successful',3);
IF p_lease_tenancies_rec.last_update_date = pn_lease_utils.g_pn_miss_date
THEN
p_lease_tenancies_rec.last_update_date:= NULL;
IF p_lease_tenancies_rec.last_updated_by = pn_lease_utils.g_pn_miss_num
THEN
p_lease_tenancies_rec.last_updated_by:= NULL;
IF p_lease_tenancies_rec.last_update_login = pn_lease_utils.g_pn_miss_num
THEN
p_lease_tenancies_rec.last_update_login:= NULL;
PROCEDURE insert_tenancies_row (
p_lease_tenancies_rec IN OUT NOCOPY lease_tenancies_rec
, p_lease_rec IN lease_rec
, p_lease_context IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_rowid VARCHAR2 (18) := NULL;
pvt_debug(g_pkg_name||'-Inside INSERT_TENANCIES_ROW',3);
pvt_debug(g_pkg_name||'-INSERT_TENANCIES_ROW:Before Calling Standard tenancies package:insert_row',3);
pn_tenancies_pkg.insert_row
(x_rowid => l_rowid
, x_tenancy_id => p_lease_tenancies_rec.tenancy_id
, x_location_id => p_lease_tenancies_rec.location_id
, x_lease_id => p_lease_tenancies_rec.lease_id
, x_lease_change_id => p_lease_tenancies_rec.lease_change_id
, x_tenancy_usage_lookup_code => p_lease_tenancies_rec.tenancy_usage_lookup_code
, x_primary_flag => p_lease_tenancies_rec.primary_flag
, x_estimated_occupancy_date => p_lease_tenancies_rec.estimated_occupancy_date
, x_occupancy_date => p_lease_tenancies_rec.occupancy_date
, x_expiration_date => p_lease_tenancies_rec.expiration_date
, x_assignable_flag => p_lease_tenancies_rec.assignable_flag
, x_subleaseable_flag => p_lease_tenancies_rec.subleaseable_flag
, x_tenants_proportionate_share => p_lease_tenancies_rec.tenants_proportionate_share
, x_allocated_area_pct => p_lease_tenancies_rec.allocated_area_pct
, x_allocated_area => p_lease_tenancies_rec.allocated_area
, x_status => p_lease_tenancies_rec.status
, x_attribute_category => p_lease_tenancies_rec.attribute_category
, x_attribute1 => p_lease_tenancies_rec.attribute1
, x_attribute2 => p_lease_tenancies_rec.attribute2
, x_attribute3 => p_lease_tenancies_rec.attribute3
, x_attribute4 => p_lease_tenancies_rec.attribute4
, x_attribute5 => p_lease_tenancies_rec.attribute5
, x_attribute6 => p_lease_tenancies_rec.attribute6
, x_attribute7 => p_lease_tenancies_rec.attribute7
, x_attribute8 => p_lease_tenancies_rec.attribute8
, x_attribute9 => p_lease_tenancies_rec.attribute9
, x_attribute10 => p_lease_tenancies_rec.attribute10
, x_attribute11 => p_lease_tenancies_rec.attribute11
, x_attribute12 => p_lease_tenancies_rec.attribute12
, x_attribute13 => p_lease_tenancies_rec.attribute13
, x_attribute14 => p_lease_tenancies_rec.attribute14
, x_attribute15 => p_lease_tenancies_rec.attribute15
, x_creation_date => NVL (p_lease_tenancies_rec.creation_date, SYSDATE)
, x_created_by => NVL (p_lease_tenancies_rec.created_by, fnd_global.user_id)
, x_last_update_date => NVL (p_lease_tenancies_rec.last_update_date, SYSDATE)
, x_last_updated_by => NVL (p_lease_tenancies_rec.last_updated_by, fnd_global.user_id)
, x_last_update_login => NVL (p_lease_tenancies_rec.last_update_login, fnd_global.login_id)
, x_org_id => p_lease_tenancies_rec.org_id
, x_tenancy_ovelap_wrn => x_tenancy_ovelap_wrn
, x_recovery_type_code => p_lease_tenancies_rec.recovery_type_code
, x_recovery_space_std_code => p_lease_tenancies_rec.recovery_space_std_code
, x_fin_oblig_end_date => p_lease_tenancies_rec.fin_oblig_end_date
, x_customer_id => p_lease_tenancies_rec.customer_id
, x_customer_site_use_id => p_lease_tenancies_rec.customer_site_use_id
, x_lease_rentable_area => p_lease_tenancies_rec.lease_rentable_area
, x_lease_usable_area => p_lease_tenancies_rec.lease_usable_area
, x_lease_assignable_area => p_lease_tenancies_rec.lease_assignable_area
, x_lease_load_factor => p_lease_tenancies_rec.lease_load_factor
, x_location_rentable_area => p_lease_tenancies_rec.location_rentable_area
, x_location_usable_area => p_lease_tenancies_rec.location_usable_area
, x_location_assignable_area => p_lease_tenancies_rec.location_assignable_area
, x_location_load_factor => p_lease_tenancies_rec.location_load_factor
);
pvt_debug(g_pkg_name||'- INSERT_TENANCIES_ROW:Insertion into tenancies table successful',3);
pvt_debug(g_pkg_name||'- INSERT_TENANCIES_ROW:Exception in Insertion into tenancies table'||SQLERRM,3);
END insert_tenancies_row;
IF p_lease_contacts_rec.last_updated_by = pn_lease_utils.g_pn_miss_num
THEN
p_lease_contacts_rec.last_updated_by := NULL;
IF p_lease_contacts_rec.last_update_login = pn_lease_utils.g_pn_miss_num
THEN
p_lease_contacts_rec.last_update_login := NULL;
IF p_lease_contacts_rec.last_update_date = pn_lease_utils.g_pn_miss_date
THEN
p_lease_contacts_rec.last_update_date := NULL;
PROCEDURE insert_contacts_row (
p_lease_contacts_rec IN OUT NOCOPY lease_contacts_rec
, p_lease_rec IN lease_rec
, p_lease_context IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2)
IS
l_rowid VARCHAR2 (18) := NULL;
pvt_debug(g_pkg_name||'- INSERT_CONTACTS_ROW:Calling INIT_CONTACTS_REC',3);
pvt_debug(g_pkg_name||'- INSERT_CONTACTS_ROW:Calling PN_CONTACT_ASSIGNMENTS_PKG.INSERT_ROW',3);
pn_contact_assignments_pkg.insert_row
(x_rowid => l_rowid
, x_contact_assignment_id => p_lease_contacts_rec.contact_assignment_id
, x_last_update_date => NVL
(p_lease_contacts_rec.last_update_date
, SYSDATE)
, x_last_updated_by => NVL
(p_lease_contacts_rec.last_updated_by
, fnd_global.user_id)
, x_creation_date => NVL
(p_lease_contacts_rec.creation_date
, SYSDATE)
, x_created_by => NVL (p_lease_contacts_rec.created_by
, fnd_global.user_id)
, x_last_update_login => NVL
(p_lease_contacts_rec.last_update_login
, fnd_global.login_id)
, x_company_id => p_lease_contacts_rec.company_id
, x_company_site_id => p_lease_contacts_rec.company_site_id
, x_lease_id => p_lease_contacts_rec.lease_id
, x_lease_change_id => p_lease_contacts_rec.lease_change_id
, x_location_id => p_lease_contacts_rec.location_id
, x_status => p_lease_contacts_rec.status
, x_attribute_category => p_lease_contacts_rec.attribute_category
, x_attribute1 => p_lease_contacts_rec.attribute1
, x_attribute2 => p_lease_contacts_rec.attribute2
, x_attribute3 => p_lease_contacts_rec.attribute3
, x_attribute4 => p_lease_contacts_rec.attribute4
, x_attribute5 => p_lease_contacts_rec.attribute5
, x_attribute6 => p_lease_contacts_rec.attribute6
, x_attribute7 => p_lease_contacts_rec.attribute7
, x_attribute8 => p_lease_contacts_rec.attribute8
, x_attribute9 => p_lease_contacts_rec.attribute9
, x_attribute10 => p_lease_contacts_rec.attribute10
, x_attribute11 => p_lease_contacts_rec.attribute11
, x_attribute12 => p_lease_contacts_rec.attribute12
, x_attribute13 => p_lease_contacts_rec.attribute13
, x_attribute14 => p_lease_contacts_rec.attribute14
, x_attribute15 => p_lease_contacts_rec.attribute15
, x_org_id => p_lease_contacts_rec.org_id);
pvt_debug(g_pkg_name||'- INSERT_CONTACTS_ROW:PN_CONTACT_ASSIGNMENTS_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'- INSERT_CONTACTS_ROW:Exception:PN_CONTACT_ASSIGNMENTS_PKG.INSERT_ROW'||SQLERRM,3);
END insert_contacts_row;
IF (trim(p_operation) <> 'UPDATE_CONTACTS') THEN
pn_lease_utils.add_null_parameter_msg
(p_token_apiname => l_api_name_full
, p_token_nullparam => 'CONTACT_ROLE');
IF (trim(p_operation) = 'UPDATE_CONTACTS' AND ( (p_lease_contacts_tbl (i).role_code IS NULL OR p_lease_contacts_tbl (i).role_code = pn_lease_utils.g_pn_miss_char)
AND(p_lease_contacts_tbl (i).role IS NULL OR p_lease_contacts_tbl (i).role = pn_lease_utils.g_pn_miss_char)
)
) THEN
BEGIN
SELECT lease_role,lease_role_type
INTO p_lease_contacts_tbl (i).role,p_lease_contacts_tbl (i).role_code
FROM pn_contact_assignments_v
WHERE contact_assignment_id = p_lease_contacts_tbl (i).contact_assignment_id;
IF (trim(p_operation) <> 'UPDATE_CONTACTS') THEN
----------------------dbms_output.put_line('Private Contacts:5');--Rupak
IF (trim(p_operation) <> 'UPDATE_CONTACTS') THEN
/*pn_lease_utils.add_null_parameter_msg
(p_token_apiname => l_api_name_full
, p_token_nullparam => 'CONTACT_CUSTOMER_SITE');*/
pvt_debug(g_pkg_name||'-VALIDATE_CONTACTS_TBL:Calling the INSERT_CONTACTS_ROW',3);
pn_lease_pvt.insert_contacts_row (p_lease_contacts_rec => p_lease_contacts_tbl(i)
, p_lease_rec => p_lease_rec
, p_lease_context => p_lease_context
, x_return_status => x_return_status);
pvt_debug(g_pkg_name||'-VALIDATE_CONTACTS_TBL:Return Status of the INSERT_CONTACTS_ROW'||x_return_status,3);
IF p_lease_rec.last_update_date = pn_lease_utils.g_pn_miss_date
THEN
p_lease_rec.last_update_date:= NULL;
IF p_lease_rec.last_updated_by = pn_lease_utils.g_pn_miss_num
THEN
p_lease_rec.last_updated_by:= NULL;
IF p_lease_rec.last_update_login = pn_lease_utils.g_pn_miss_num
THEN
p_lease_rec.last_update_login:= NULL;
PROCEDURE lease_insert_row (
p_lease_rec IN OUT NOCOPY lease_rec
, p_lease_exp_rev_accounts_rec IN OUT NOCOPY lease_account_rec
, p_lease_accrual_account_rec IN OUT NOCOPY lease_account_rec
, p_lease_liab_rcvl_account_rec IN OUT NOCOPY lease_account_rec
, p_location_exist IN VARCHAR2 := fnd_api.g_false
, p_lease_context IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2)
IS
l_rowid VARCHAR2 (18) := NULL;
pvt_debug(g_pkg_name||'-Inside LEASE_INSERT_ROW',3);
pvt_debug(g_pkg_name||'-LEASE_INSERT_ROW:Calling Insert Row of the Lease:PN_LEASES_PKG.INSERT_ROW',3);
pn_leases_pkg.insert_row
(x_rowid => l_rowid
, x_lease_id => p_lease_rec.lease_id
, x_lease_change_id => p_lease_rec.lease_change_id
, x_lease_detail_id => p_lease_rec.lease_detail_id
, x_name => p_lease_rec.lease_name
, x_lease_num => p_lease_rec.lease_num
, x_parent_lease_id => p_lease_rec.parent_lease_id
, x_lease_type_code => p_lease_rec.lease_type_code
, x_lease_class_code => p_lease_rec.lease_class_code
, x_payment_term_proration_rule => p_lease_rec.pymt_term_pro_rule_id
, x_abstracted_by_user => p_lease_rec.abstracted_by_user_id
, x_status => p_lease_rec.status_code
, x_lease_status => p_lease_rec.lease_status_code
, x_creation_date => SYSDATE
, x_created_by => fnd_global.user_id
, x_last_update_date => p_lease_rec.last_update_date
, x_last_updated_by => fnd_global.user_id
, x_last_update_login => fnd_global.login_id
, x_responsible_user => p_lease_rec.responsible_user_id
, x_expense_account_id => p_lease_exp_rev_accounts_rec.account_id
, x_accrual_account_id => p_lease_accrual_account_rec.account_id
, x_receivable_account_id => p_lease_liab_rcvl_account_rec.account_id
, x_term_template_id => p_lease_rec.term_template_id
, x_lease_commencement_date => p_lease_rec.lease_commencement_date
, x_lease_termination_date => p_lease_rec.lease_termination_date
, x_lease_execution_date => p_lease_rec.lease_execution_date
, x_attribute_category => p_lease_rec.attribute_category
, x_attribute1 => p_lease_rec.attribute1
, x_attribute2 => p_lease_rec.attribute2
, x_attribute3 => p_lease_rec.attribute3
, x_attribute4 => p_lease_rec.attribute4
, x_attribute5 => p_lease_rec.attribute5
, x_attribute6 => p_lease_rec.attribute6
, x_attribute7 => p_lease_rec.attribute7
, x_attribute8 => p_lease_rec.attribute8
, x_attribute9 => p_lease_rec.attribute9
, x_attribute10 => p_lease_rec.attribute10
, x_attribute11 => p_lease_rec.attribute11
, x_attribute12 => p_lease_rec.attribute12
, x_attribute13 => p_lease_rec.attribute13
, x_attribute14 => p_lease_rec.attribute14
, x_attribute15 => p_lease_rec.attribute15
, x_org_id => g_org_id--p_lease_rec.org_id
, x_location_id => p_lease_rec.location_id
, x_customer_id => p_lease_rec.customer_id
, x_grouping_rule_id => p_lease_rec.grouping_rule_id
, x_calendar_year_start_date => p_lease_rec.cal_start
);
pvt_debug(g_pkg_name||'-LEASE_INSERT_ROW:PN_LEASES_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'-LEASE_INSERT_ROW:Exception'||SQLERRM,3);
END lease_insert_row;
PROCEDURE NOTE_INSERT_ROW(p_note_tbl IN OUT NOCOPY lease_note_tbl_type
, p_lease_id IN PN_LEASES_ALL.lease_id%type
, x_return_status OUT NOCOPY VARCHAR2
, p_tbl IN temp_tbl_type
, p_commit IN VARCHAR2
)
IS
l_index BINARY_INTEGER;
pvt_debug(g_pkg_name||'-Inside NOTE_INSERT_ROW',3);
pvt_debug(g_pkg_name||'-NOTE_INSERT_ROW:Calling PN_NOTE_HEADERS_PKG.INSERT_ROW',3);
pn_note_headers_pkg.insert_row (
x_rowid => l_hdr_row_id
, x_note_header_id => l_note_header_id
, x_lease_id => p_lease_id
, x_note_type_lookup_code => l_note_header_tbl(l_index).note_type_lookup_code
, x_note_date => trunc(l_note_header_tbl(l_index).note_date)
, x_creation_date => sysdate
, x_created_by => fnd_global.user_id
, x_last_update_date => sysdate
, x_last_updated_by => fnd_global.user_id
, x_last_update_login => fnd_global.user_id
, x_attribute_category => l_note_header_tbl(l_index).attribute_category
, x_attribute1 => l_note_header_tbl(l_index).attribute1
, x_attribute2 => l_note_header_tbl(l_index).attribute2
, x_attribute3 => l_note_header_tbl(l_index).attribute3
, x_attribute4 => l_note_header_tbl(l_index).attribute4
, x_attribute5 => l_note_header_tbl(l_index).attribute5
, x_attribute6 => l_note_header_tbl(l_index).attribute6
, x_attribute7 => l_note_header_tbl(l_index).attribute7
, x_attribute8 => l_note_header_tbl(l_index).attribute8
, x_attribute9 => l_note_header_tbl(l_index).attribute9
, x_attribute10 => l_note_header_tbl(l_index).attribute10
, x_attribute11 => l_note_header_tbl(l_index).attribute11
, x_attribute12 => l_note_header_tbl(l_index).attribute12
, x_attribute13 => l_note_header_tbl(l_index).attribute13
, x_attribute14 => l_note_header_tbl(l_index).attribute14
, x_attribute15 => l_note_header_tbl(l_index).attribute15
);
pvt_debug(g_pkg_name||'-NOTE_INSERT_ROW:PN_NOTE_HEADERS_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'-NOTE_INSERT_ROW:Calling PN_NOTE_DETAILS_PKG.INSERT_ROW',3);
pn_note_details_pkg.insert_row (
x_rowid => l_dt_row_id
, x_note_detail_id => l_note_detail_id
, x_note_header_id => l_note_header_tbl(l_index).note_header_id
, x_text => l_note_header_tbl(l_index).text
, x_creation_date => sysdate
, x_created_by => fnd_global.user_id
, x_last_update_date => sysdate
, x_last_updated_by => fnd_global.user_id
, x_last_update_login => fnd_global.user_id
);
pvt_debug(g_pkg_name||'-NOTE_INSERT_ROW:PN_NOTE_DETAILS_PKG.INSERT_ROW Succesful',3);
END NOTE_INSERT_ROW;
pvt_debug(g_pkg_name||'-CREATE_NOTE:Calling NOTE_INSERT_ROW',3);
NOTE_INSERT_ROW( p_note_tbl => p_note_tbl
,p_lease_id => p_lease_id
,x_return_status => x_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
);
pvt_debug(g_pkg_name||'-CREATE_NOTE:Call to NOTE_INSERT_ROW Succesful',3);
pvt_debug(g_pkg_name||'-CREATE_NOTE:Exception:NOTE_INSERT_ROW',3);
PROCEDURE VALIDATE_NOTE_FOR_UPDATE(
p_note_tbl IN OUT NOCOPY lease_note_tbl_type
,p_lease_id PN_LEASES_ALL.LEASE_ID%TYPE
,x_tbl OUT NOCOPY temp_tbl_type
,x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
l_api_name VARCHAR2(30) := 'UPDATE_NOTES';
SELECT note_header_id
, note_date
, last_updated_by
, last_update_date
, last_update_login
, created_by
, creation_date
, note_type_lookup_code
, lease_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM pn_note_headers
WHERE note_header_id = l_cur_id;
pvt_debug(g_pkg_name||'-Inside VALIDATE_NOTE_FOR_UPDATE',3);
pvt_debug(g_pkg_name||'-Validation:VALIDATE_NOTE_FOR_UPDATE',3);
pvt_debug(g_pkg_name||'-VALIDATE_NOTE_FOR_UPDATE:Lease ID Invalid',3);
, p_operation => 'UPDATE_NOTE'
, p_lookup_type => l_pn_note_type--'PN_NOTE_TYPE'
, x_return_status => x_return_status
, x_lookup_type_code => p_note_tbl(l_index).note_type_lookup_code
);
pvt_debug(g_pkg_name||'-VALIDATE_NOTE_FOR_UPDATE:LOOOPING:EXCEPTION:FND_API.G_EXC_ERROR'||SQLERRM,3);
pvt_debug(g_pkg_name||'-VALIDATE_NOTE_FOR_UPDATE:LOOOPING:EXCEPTION:'||SQLERRM,3);
pvt_debug(g_pkg_name||'-Exception:VALIDATE_NOTE_FOR_UPDATE'||SQLERRM,3);
END VALIDATE_NOTE_FOR_UPDATE;
PROCEDURE NOTE_UPDATE_ROW ( p_note_tbl IN OUT NOCOPY lease_note_tbl_type
,p_lease_id IN PN_LEASES_ALL.LEASE_ID%TYPE
,x_return_status OUT NOCOPY VARCHAR2
,p_tbl IN temp_tbl_type
,p_commit IN VARCHAR2
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
pvt_debug(g_pkg_name||'-Inside NOTE_UPDATE_ROW',3);
pvt_debug(g_pkg_name||'-NOTE_UPDATE_ROW:Calling PN_NOTE_HEADERS_PKG.UPDATE_ROW',3);
pn_note_headers_pkg.update_row (
x_note_header_id => p_note_tbl(l_index).note_header_id
, x_lease_id => p_lease_id
, x_note_type_lookup_code => p_note_tbl(l_index).note_type_lookup_code
, x_note_date => trunc(p_note_tbl(l_index).note_date)
, x_last_update_date => sysdate
, x_last_updated_by => fnd_global.user_id
, x_last_update_login => fnd_global.user_id
, x_attribute_category => p_note_tbl(l_index).attribute_category
, x_attribute1 => p_note_tbl(l_index).attribute1
, x_attribute2 => p_note_tbl(l_index).attribute2
, x_attribute3 => p_note_tbl(l_index).attribute3
, x_attribute4 => p_note_tbl(l_index).attribute4
, x_attribute5 => p_note_tbl(l_index).attribute5
, x_attribute6 => p_note_tbl(l_index).attribute6
, x_attribute7 => p_note_tbl(l_index).attribute7
, x_attribute8 => p_note_tbl(l_index).attribute8
, x_attribute9 => p_note_tbl(l_index).attribute9
, x_attribute10 => p_note_tbl(l_index).attribute10
, x_attribute11 => p_note_tbl(l_index).attribute11
, x_attribute12 => p_note_tbl(l_index).attribute12
, x_attribute13 => p_note_tbl(l_index).attribute13
, x_attribute14 => p_note_tbl(l_index).attribute14
, x_attribute15 => p_note_tbl(l_index).attribute15
);
pvt_debug(g_pkg_name||'-NOTE_UPDATE_ROW:Calling PN_NOTE_HEADERS_PKG.UPDATE_ROW Succesful',3);
SELECT note_detail_id
INTO p_note_tbl(l_index).note_detail_id
FROM pn_note_details
WHERE note_header_id = p_note_tbl(l_index).note_header_id
AND LANGUAGE = 'US';--userenv('lang');
pvt_debug(g_pkg_name||'-NOTE_UPDATE_ROW:NOTE_DETAIL_ID'||p_note_tbl(l_index).note_detail_id,3);
pn_note_details_pkg.update_row (
x_note_detail_id => p_note_tbl(l_index).note_detail_id
, x_text => p_note_tbl(l_index).text
, x_last_update_date => sysdate
, x_last_updated_by => fnd_global.user_id
, x_last_update_login => fnd_global.user_id
);
pvt_debug(g_pkg_name||'-NOTE_UPDATE_ROW:NOTE DETAIL UPDATED WITH NOTE DETAIL ID'||p_note_tbl(l_index).note_detail_id,3);
pn_note_details_pkg.insert_row (
x_rowid => l_dt_row_id
, x_note_detail_id => l_note_detail_id
, x_note_header_id => p_note_tbl(l_index).note_header_id
, x_text => p_note_tbl(l_index).text
, x_creation_date => sysdate
, x_created_by => fnd_global.user_id
, x_last_update_date => sysdate
, x_last_updated_by => fnd_global.user_id
, x_last_update_login => fnd_global.user_id
);
pvt_debug(g_pkg_name||'-NOTE_UPDATE_ROW:PN_NOTE_DETAILS_PKG.INSERT_ROW Succesful'||l_note_detail_id,3);
END NOTE_UPDATE_ROW;
PROCEDURE UPDATE_NOTE (
p_lease_id IN PN_LEASES_ALL.LEASE_ID%TYPE
, p_commit IN VARCHAR2
, p_validate IN VARCHAR2
, p_note_tbl IN OUT NOCOPY lease_note_tbl_type
, x_return_status OUT NOCOPY VARCHAR2
-- , x_msg_count OUT NUMBER
-- , x_msg_data OUT VARCHAR2
)
IS
l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
l_api_name VARCHAR2(30) := 'UPDATE NOTES';
p_note_tbl_update lease_note_tbl_type;
pvt_debug(g_pkg_name||'-Inside UPDATE_NOTE',3);
p_note_tbl_update(n):=p_note_tbl(i);
IF(p_note_tbl_update.COUNT > 0) THEN
VALIDATE_NOTE_FOR_UPDATE(p_note_tbl => p_note_tbl_update
,p_lease_id => p_lease_id
,x_tbl => x_out_tbl
,x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-UPDATE_NOTE:VALIDATE_NOTE_FOR_UPDATE Return Status:'||x_return_status,3);
NOTE_UPDATE_ROW(
p_note_tbl => p_note_tbl_update
, p_lease_id => p_lease_id
, x_return_status => x_return_status
, p_tbl => x_out_tbl
, p_commit => p_commit
);
pvt_debug(g_pkg_name||'-UPDATE_NOTE:NOTE_UPDATE_ROW Return Status:'||x_return_status,3);
pvt_debug(g_pkg_name||'-CREATE_NOTE:Calling NOTE_INSERT_ROW',3);
NOTE_INSERT_ROW( p_note_tbl => p_note_tbl_create
,p_lease_id => p_lease_id
,x_return_status => x_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
);
pvt_debug(g_pkg_name||'-CREATE_NOTE:Call to NOTE_INSERT_ROW Succesful',3);
pvt_debug(g_pkg_name||'-UPDATE_NOTE:Unexpected Exception:'||SQLERRM,3);
END UPDATE_NOTE;
PROCEDURE RIGHT_INSERT_ROW(p_right_tbl IN OUT NOCOPY lease_right_tbl_type
, p_lease_id IN PN_LEASES_ALL.lease_id%type
, p_lease_change_id IN PN_LEASE_CHANGES_ALL.lease_change_id%type
, x_return_status OUT NOCOPY VARCHAR2
, p_tbl IN temp_tbl_type
, p_commit IN VARCHAR2
)
IS
l_index BINARY_INTEGER;
pvt_debug(g_pkg_name||'-Inside RIGHT_INSERT_ROW',3);
pvt_debug(g_pkg_name||'-RIGHT_INSERT_ROW:Calling INITIALIZE_RIGHTS_REC',3);
pvt_debug(g_pkg_name||'-RIGHT_INSERT_ROW:Calling PN_RIGHTS_PKG.INSERT_ROW',3);
pn_rights_pkg.insert_row(
x_rowid => l_row_id
, x_right_id => l_right_id
, x_right_num => p_right_tbl(l_index).right_num
, x_lease_id => p_lease_id
, x_lease_change_id => p_lease_change_id
, x_right_type_code => p_right_tbl(l_index).right_type_code
, x_right_status_code => p_right_tbl(l_index).right_status_code
, x_right_reference => p_right_tbl(l_index).right_reference
, x_start_date => null
, x_expiration_date => null
, x_right_comments => p_right_tbl(l_index).right_comments
, x_attribute_category => p_right_tbl(l_index).attribute_category
, x_attribute1 => p_right_tbl(l_index).attribute1
, x_attribute2 => p_right_tbl(l_index).attribute2
, x_attribute3 => p_right_tbl(l_index).attribute3
, x_attribute4 => p_right_tbl(l_index).attribute4
, x_attribute5 => p_right_tbl(l_index).attribute5
, x_attribute6 => p_right_tbl(l_index).attribute6
, x_attribute7 => p_right_tbl(l_index).attribute7
, x_attribute8 => p_right_tbl(l_index).attribute8
, x_attribute9 => p_right_tbl(l_index).attribute9
, x_attribute10 => p_right_tbl(l_index).attribute10
, x_attribute11 => p_right_tbl(l_index).attribute11
, x_attribute12 => p_right_tbl(l_index).attribute12
, x_attribute13 => p_right_tbl(l_index).attribute13
, x_attribute14 => p_right_tbl(l_index).attribute14
, x_attribute15 => p_right_tbl(l_index).attribute15
, x_creation_date => sysdate
, x_created_by => fnd_global.user_id
, x_last_update_date => sysdate
, x_last_updated_by => fnd_global.user_id
, x_last_update_login => fnd_global.user_id
, x_org_id => fnd_global.org_id
);
pvt_debug(g_pkg_name||'-RIGHT_INSERT_ROW:PN_RIGHTS_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'-RIGHT_INSERT_ROW:PN_RIGHTS_PKG.INSERT_ROW:Exception'||SQLERRM,3);
pvt_debug(g_pkg_name||'-RIGHT_INSERT_ROW::Exception'||SQLERRM,3);
END RIGHT_INSERT_ROW;
RIGHT_INSERT_ROW(p_right_tbl => p_right_tbl
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,x_return_status => x_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
);
pvt_debug(g_pkg_name||'-CREATE_RIGHT:RIGHT_INSERT_ROW Return Status:'||l_return_status,3);
PROCEDURE VALIDATE_RIGHT_FOR_UPDATE(
p_right_tbl IN OUT NOCOPY lease_right_tbl_type
,p_lease_id IN PN_LEASES_ALL.LEASE_ID%TYPE
,p_lease_change_id IN PN_LEASE_CHANGES_ALL.LEASE_CHANGE_ID%TYPE
,x_tbl OUT NOCOPY temp_tbl_type
,x_return_status IN OUT NOCOPY VARCHAR2
,p_commit IN VARCHAR2
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
l_api_name VARCHAR2(30) := 'UPDATE_RIGHTS';
SELECT right_id
, lease_id
, lease_change_id
, right_num
, right_type_code
, right_status_code
, right_reference
, right_comments
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, org_id
FROM pn_rights_all
WHERE right_id = p_right_id;
pvt_debug(g_pkg_name||'-Inside VALIDATE_RIGHT_FOR_UPDATE',3);
pvt_debug(g_pkg_name||'-VALIDATE_RIGHT_FOR_UPDATE:ELSE Part',3);
pvt_debug(g_pkg_name||'-VALIDATE_RIGHT_FOR_UPDATE:LOOOPING:EXCEPTION:FND_API.G_EXC_ERROR'||SQLERRM,3);
pvt_debug(g_pkg_name||'-VALIDATE_RIGHT_FOR_UPDATE:LOOOPING:EXCEPTION:'||SQLERRM,3);
END VALIDATE_RIGHT_FOR_UPDATE;
PROCEDURE RIGHT_UPDATE_ROW( p_right_tbl IN OUT NOCOPY lease_right_tbl_type
,p_lease_id IN PN_LEASES_ALL.LEASE_ID%type
,p_lease_change_id IN PN_LEASE_CHANGES_ALL.lease_change_id%type
,x_return_status OUT NOCOPY VARCHAR2
,p_tbl IN temp_tbl_type
,p_commit IN VARCHAR2
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
pvt_debug(g_pkg_name||'-Inside RIGHT_UPDATE_ROW',3);
pvt_debug(g_pkg_name||'-RIGHT_UPDATE_ROW:Calling PN_RIGHTS_PKG.UPDATE_ROW',3);
pn_rights_pkg.update_row(
x_right_id => p_right_tbl(l_index).right_id
, x_lease_id => p_lease_id
, x_lease_change_id => p_lease_change_id
, x_right_num => p_right_tbl(l_index).right_num
, x_right_type_code => p_right_tbl(l_index).right_type_code
, x_right_status_code => p_right_tbl(l_index).right_status_code
, x_right_reference => p_right_tbl(l_index).right_reference
, x_start_date => NULL
, x_expiration_date => NULL
, x_right_comments => p_right_tbl(l_index).right_comments
, x_attribute_category => p_right_tbl(l_index).attribute_category
, x_attribute1 => p_right_tbl(l_index).attribute1
, x_attribute2 => p_right_tbl(l_index).attribute2
, x_attribute3 => p_right_tbl(l_index).attribute3
, x_attribute4 => p_right_tbl(l_index).attribute4
, x_attribute5 => p_right_tbl(l_index).attribute5
, x_attribute6 => p_right_tbl(l_index).attribute6
, x_attribute7 => p_right_tbl(l_index).attribute7
, x_attribute8 => p_right_tbl(l_index).attribute8
, x_attribute9 => p_right_tbl(l_index).attribute9
, x_attribute10 => p_right_tbl(l_index).attribute10
, x_attribute11 => p_right_tbl(l_index).attribute11
, x_attribute12 => p_right_tbl(l_index).attribute12
, x_attribute13 => p_right_tbl(l_index).attribute13
, x_attribute14 => p_right_tbl(l_index).attribute14
, x_attribute15 => p_right_tbl(l_index).attribute15
, x_last_update_date => sysdate
, x_last_updated_by => fnd_global.user_id
, x_last_update_login => fnd_global.user_id
);
pvt_debug(g_pkg_name||'-RIGHT_UPDATE_ROW:PN_RIGHTS_PKG.UPDATE_ROW Successful',3);
pvt_debug(g_pkg_name||'-RIGHT_INSERT_ROW:PN_RIGHTS_PKG.INSERT_ROW:Exception'||SQLERRM,3);
pvt_debug(g_pkg_name||'-RIGHT_UPDATE_ROW:Exception'||SQLERRM,3);
END RIGHT_UPDATE_ROW;
PROCEDURE UPDATE_RIGHT (
p_lease_id IN PN_LEASES_ALL.LEASE_ID%TYPE
, p_lease_change_id IN PN_LEASE_CHANGES_ALL.LEASE_CHANGE_ID%TYPE
, p_commit IN VARCHAR2
, p_validate IN VARCHAR2
, p_right_tbl IN OUT NOCOPY lease_right_tbl_type
, x_return_status OUT NOCOPY VARCHAR2
-- , x_msg_count OUT NUMBER
-- , x_msg_data OUT VARCHAR2
)
IS
l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
p_right_tbl_update lease_right_tbl_type;
pvt_debug(g_pkg_name||'-Inside UPDATE_RIGHT:Calling VALIDATE_RIGHT_FOR_UPDATE',3);
p_right_tbl_update(n):=p_right_tbl(i);
IF(p_right_tbl_update.COUNT > 0) THEN
VALIDATE_RIGHT_FOR_UPDATE(p_right_tbl => p_right_tbl_update
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,x_tbl => x_out_tbl
,x_return_status => x_return_status
,p_commit => p_commit
);
pvt_debug(g_pkg_name||'-Inside UPDATE_RIGHT:Calling RIGHT_UPDATE_ROW',3);
RIGHT_UPDATE_ROW(
p_right_tbl => p_right_tbl_update
, p_lease_id => p_lease_id
, p_lease_change_id => p_lease_change_id
, x_return_status => x_return_status
, p_tbl => x_out_tbl
, p_commit => p_commit
);
pvt_debug(g_pkg_name||'-UPDATE_RIGHT:RIGHT_UPDATE_ROW Successful',3);
RIGHT_INSERT_ROW(p_right_tbl => p_right_tbl_create
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,x_return_status => x_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
);
pvt_debug(g_pkg_name||'-CREATE_RIGHT:RIGHT_INSERT_ROW Return Status:'||l_return_status,3);
pvt_debug(g_pkg_name||'-UPDATE_RIGHT:Exception:'||SQLERRM,3);
END UPDATE_RIGHT;
PROCEDURE OBLIGATION_INSERT_ROW(p_obligation_tbl IN OUT NOCOPY LEASE_OBLIGATION_TBL_TYPE
,p_lease_id IN PN_LEASES_ALL.LEASE_ID%TYPE
,p_lease_change_id IN PN_LEASE_CHANGES_ALL.LEASE_CHANGE_ID%TYPE
,x_return_status IN OUT NOCOPY VARCHAR2
,p_tbl IN OUT NOCOPY TEMP_TBL_TYPE
,p_commit IN VARCHAR2
)
IS
--l_index NUMBER;
pvt_debug(g_pkg_name||'-Inside OBLIGATION_INSERT_ROW',3);
pvt_debug(g_pkg_name||'-OBLIGATION_INSERT_ROW:Calling PN_LANDLORD_SERVICES_PKG.INSERT_ROW',3);
PN_LANDLORD_SERVICES_PKG.insert_row(
X_ROWID => l_row_id,
X_LANDLORD_SERVICE_ID => l_landlord_service_id,
X_LEASE_ID => p_lease_id,
X_LEASE_CHANGE_ID => p_lease_change_id,
X_SERVICE_TYPE_LOOKUP_CODE => p_obligation_tbl(l_index).service_type_lookup_code,
X_START_DATE => p_obligation_tbl(l_index).start_date,
X_END_DATE => p_obligation_tbl(l_index).end_date,
X_STATUS => p_obligation_tbl(l_index).status,
X_ATTRIBUTE_CATEGORY => p_obligation_tbl(l_index).attribute_category,
X_ATTRIBUTE1 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE2 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE3 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE4 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE5 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE6 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE7 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE8 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE9 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE10 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE11 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE12 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE13 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE14 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE15 => p_obligation_tbl(l_index).attribute1,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.user_id,
X_OBLIGATION_NUM => p_obligation_tbl(l_index).obligation_num,
X_RESPONSIBILITY_CODE => p_obligation_tbl(l_index).responsibility_code,
X_COMMON_AREA_RESP => p_obligation_tbl(l_index).common_area_resp,
X_FINANCIAL_RESP_PARTY_CODE => p_obligation_tbl(l_index).financial_resp_party_code,
X_FINANCIAL_PCT_RESP => p_obligation_tbl(l_index).financial_pct_resp,
X_RESPONSIBILITY_MAINT => p_obligation_tbl(l_index).responsibility_maint,
X_COMPANY_ID => p_obligation_tbl(l_index).company_id,
X_OBLIGATION_REFERENCE => p_obligation_tbl(l_index).obligation_reference,
X_OBLIGATION_COMMENTS => p_obligation_tbl(l_index).obligation_comments,
X_org_id => fnd_global.org_id
);
pvt_debug(g_pkg_name||'-OBLIGATION_INSERT_ROW:PN_LANDLORD_SERVICES_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'-OBLIGATION_INSERT_ROW:Exception:PN_LANDLORD_SERVICES_PKG.INSERT_ROW',3);
END OBLIGATION_INSERT_ROW;
OBLIGATION_INSERT_ROW(
p_obligation_tbl => p_obligation_tbl
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,x_return_status => x_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
);
PROCEDURE VALIDATE_OBLIGATION_FOR_UPDATE(
p_obligation_tbl IN OUT NOCOPY lease_obligation_tbl_type
,p_lease_id IN PN_LEASES_ALL.LEASE_ID%TYPE
,p_lease_change_id IN PN_LEASE_CHANGES_ALL.LEASE_CHANGE_ID%TYPE
,x_tbl OUT NOCOPY temp_tbl_type
,x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
l_api_name VARCHAR2(30) := 'UPDATE_OBLIGATIONS';
SELECT landlord_service_id
, lease_id
, lease_change_id
, status
, service_type_lookup_code
, start_date
, end_date
, obligation_comments
, obligation_reference
, company_id
, responsibility_maint
, financial_pct_resp
, financial_resp_party_code
, common_area_resp
, responsibility_code
, org_id
, obligation_num
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM pn_landlord_services_all
WHERE landlord_service_id = p_landlord_service_id;
pvt_debug(g_pkg_name||'-Inside VALIDATE_OBLIGATION_FOR_UPDATE',3);
pvt_debug(g_pkg_name||'-VALIDATE_OBLIGATION_FOR_UPDATE:landlord_service_id IS NULL',3);
, p_operation => 'UPDATE_OBLIGATION'
, p_lookup_type => l_pn_landlord_service_type
, x_return_status => x_return_status
, x_lookup_type_code => p_obligation_tbl(l_index).service_type_lookup_code
);
,p_operation => 'UPDATE_OBLIGATION'
,p_lookup_type => l_pn_obligation_resp_type
,x_return_status => x_return_status
,x_lookup_type_code => p_obligation_tbl(l_index).responsibility_code
);
, p_operation => 'UPDATE_OBLIGATION'
, p_lookup_type => l_pn_obligation_fin_resp_type
, x_return_status => x_return_status
, x_lookup_type_code => p_obligation_tbl(l_index).financial_resp_party_code
);
pvt_debug(g_pkg_name||'-VALIDATE_OBLIGATION_FOR_UPDATE:LOOOPING:EXCEPTION:FND_API.G_EXC_ERROR'||SQLERRM,3);
pvt_debug(g_pkg_name||'-VALIDATE_OBLIGATION_FOR_UPDATE:LOOOPING:EXCEPTION:'||SQLERRM,3);
pvt_debug(g_pkg_name||'-VALIDATE_OBLIGATION_FOR_UPDATE:OTHERS:EXCEPTION:'||SQLERRM,3);
END VALIDATE_OBLIGATION_FOR_UPDATE;
PROCEDURE OBLIGATION_UPDATE_ROW (p_obligation_tbl IN OUT NOCOPY lease_obligation_tbl_type
,p_lease_id IN PN_LEASES_ALL.LEASE_ID%type
,p_lease_change_id IN PN_LEASE_CHANGES_ALL.lease_change_id%type
,x_return_status OUT NOCOPY VARCHAR2
,p_tbl IN temp_tbl_type
,p_commit IN VARCHAR2
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
pvt_debug(g_pkg_name||'-Inside OBLIGATION_UPDATE_ROW',3);
pvt_debug(g_pkg_name||'-OBLIGATION_UPDATE_ROW:Calling PN_LANDLORD_SERVICES_PKG.update_Row',3);
PN_LANDLORD_SERVICES_PKG.update_Row
(
X_LANDLORD_SERVICE_ID => p_obligation_tbl(l_index).landlord_service_id,
X_LEASE_ID => p_lease_id,
X_LEASE_CHANGE_ID => p_lease_change_id,
X_SERVICE_TYPE_LOOKUP_CODE => p_obligation_tbl(l_index).service_type_lookup_code,
X_START_DATE => p_obligation_tbl(l_index).start_date,
X_END_DATE => p_obligation_tbl(l_index).end_date,
X_STATUS => p_obligation_tbl(l_index).status,
X_ATTRIBUTE_CATEGORY => p_obligation_tbl(l_index).attribute_category,
X_ATTRIBUTE1 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE2 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE3 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE4 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE5 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE6 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE7 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE8 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE9 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE10 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE11 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE12 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE13 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE14 => p_obligation_tbl(l_index).attribute1,
X_ATTRIBUTE15 => p_obligation_tbl(l_index).attribute1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.user_id,
X_OBLIGATION_NUM => p_obligation_tbl(l_index).obligation_num,
X_RESPONSIBILITY_CODE => p_obligation_tbl(l_index).responsibility_code,
X_COMMON_AREA_RESP => p_obligation_tbl(l_index).common_area_resp,
X_FINANCIAL_RESP_PARTY_CODE => p_obligation_tbl(l_index).financial_resp_party_code,
X_FINANCIAL_PCT_RESP => p_obligation_tbl(l_index).financial_pct_resp,
X_RESPONSIBILITY_MAINT => p_obligation_tbl(l_index).responsibility_maint,
X_COMPANY_ID => p_obligation_tbl(l_index).company_id,
X_OBLIGATION_REFERENCE => p_obligation_tbl(l_index).obligation_reference,
X_OBLIGATION_COMMENTS => p_obligation_tbl(l_index).obligation_comments
);
pvt_debug(g_pkg_name||'-OBLIGATION_UPDATE_ROW:PN_LANDLORD_SERVICES_PKG.update_Row Successful',3);
pvt_debug(g_pkg_name||'-OBLIGATION_UPDATE_ROW:Exception:PN_LANDLORD_SERVICES_PKG.update_Row'||SQLERRM,3);
pvt_debug(g_pkg_name||'-OBLIGATION_UPDATE_ROW:Exception:'||SQLERRM,3);
END OBLIGATION_UPDATE_ROW;
PROCEDURE UPDATE_OBLIGATION (
p_lease_id IN PN_LEASES_ALL.LEASE_ID%TYPE
, p_lease_change_id IN PN_LEASE_CHANGES_ALL.LEASE_CHANGE_ID%TYPE
, p_commit IN VARCHAR2
, p_validate IN VARCHAR2
, p_obligation_tbl IN OUT NOCOPY lease_obligation_tbl_type
, x_return_status OUT NOCOPY VARCHAR2
-- , x_msg_count OUT NUMBER
-- , x_msg_data OUT VARCHAR2
)
IS
l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
p_obligation_tbl_update lease_obligation_tbl_type;
p_obligation_tbl_update(n):=p_obligation_tbl(i);
IF(p_obligation_tbl_update.COUNT > 0) THEN
VALIDATE_OBLIGATION_FOR_UPDATE(
p_obligation_tbl => p_obligation_tbl_update
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,x_tbl => x_out_tbl
,x_return_status => x_return_status
);
OBLIGATION_UPDATE_ROW(
p_obligation_tbl => p_obligation_tbl_update
, p_lease_id => p_lease_id
, p_lease_change_id => p_lease_change_id
, x_return_status => x_return_status
, p_tbl => x_out_tbl
, p_commit => p_commit
);
pvt_debug(g_pkg_name||'-UPDATE_OBLIGATION:RETURN STATUS:'||x_return_status,3);
OBLIGATION_INSERT_ROW(
p_obligation_tbl => p_obligation_tbl_create
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,x_return_status => x_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
);
END UPDATE_OBLIGATION;
PROCEDURE MILESTONE_INSERT_ROW(
p_milestone_tbl IN OUT NOCOPY lease_milestone_tbl_type
,p_lease_id IN PN_LEASES_ALL.lease_id%type
,p_lease_change_id IN PN_LEASE_CHANGES_ALL.lease_change_id%type
,p_option_id IN PN_LEASE_MILESTONES_ALL.option_id%type
,p_insurance_req_id IN PN_LEASE_MILESTONES_ALL.insurance_requirement_id%type
,p_payment_term_id IN PN_LEASE_MILESTONES_ALL.payment_term_id%type
,x_return_status OUT NOCOPY VARCHAR2
,p_tbl IN temp_tbl_type
,p_commit IN VARCHAR2
)
IS
l_index BINARY_INTEGER;
pvt_debug(g_pkg_name||'-Inside MILESTONE_INSERT_ROW:',3);
pvt_debug(g_pkg_name||'-MILESTONE_INSERT_ROW:Calling PN_LEASE_MILESTONES_PKG.INSERT_ROW:',3);
pn_lease_milestones_pkg.insert_row(
X_ROWID => l_row_id,
X_LEASE_MILESTONE_ID => l_milestone_id,
X_LEASE_CHANGE_ID => p_lease_change_id,
X_MILESTONE_TYPE_CODE => p_milestone_tbl(l_index).milestone_type_code,
X_OPTION_ID => p_option_id,
X_INSURANCE_REQUIREMENT_ID => p_insurance_req_id,
X_PAYMENT_TERM_ID => p_payment_term_id,
X_LEAD_DAYS => p_milestone_tbl(l_index).lead_days,
X_EVERY_DAYS => p_milestone_tbl(l_index).frequency,
X_ACTION_TAKEN => NULL,
X_ACTION_DATE => NULL,
X_ATTRIBUTE_CATEGORY => p_milestone_tbl(l_index).attribute_category,
X_ATTRIBUTE1 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE2 => p_milestone_tbl(l_index).attribute2,
X_ATTRIBUTE3 => p_milestone_tbl(l_index).attribute3,
X_ATTRIBUTE4 => p_milestone_tbl(l_index).attribute4,
X_ATTRIBUTE5 => p_milestone_tbl(l_index).attribute5,
X_ATTRIBUTE6 => p_milestone_tbl(l_index).attribute6,
X_ATTRIBUTE7 => p_milestone_tbl(l_index).attribute7,
X_ATTRIBUTE8 => p_milestone_tbl(l_index).attribute8,
X_ATTRIBUTE9 => p_milestone_tbl(l_index).attribute9,
X_ATTRIBUTE10 => p_milestone_tbl(l_index).attribute10,
X_ATTRIBUTE11 => p_milestone_tbl(l_index).attribute11,
X_ATTRIBUTE12 => p_milestone_tbl(l_index).attribute12,
X_ATTRIBUTE13 => p_milestone_tbl(l_index).attribute13,
X_ATTRIBUTE14 => p_milestone_tbl(l_index).attribute14,
X_ATTRIBUTE15 => p_milestone_tbl(l_index).attribute15,
X_MILESTONE_DATE => p_milestone_tbl(l_index).action_due_date,
X_USER_ID => p_milestone_tbl(l_index).user_id,
X_LEASE_ID => p_lease_id,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.user_id,
x_org_id => fnd_global.org_id
);
pvt_debug(g_pkg_name||'-MILESTONE_INSERT_ROW:Calling PN_LEASE_MILESTONES_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'-MILESTONE_INSERT_ROW:Exception'||SQLERRM,3);
END MILESTONE_INSERT_ROW;
MILESTONE_INSERT_ROW(p_milestone_tbl => p_milestone_tbl
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,p_option_id => p_option_id
,p_insurance_req_id => p_insurance_req_id
,p_payment_term_id => p_payment_term_id
,x_return_status => l_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
);
PROCEDURE VALIDATE_MILESTONE_FOR_UPDATE(
p_milestone_tbl IN OUT NOCOPY lease_milestone_tbl_type
,p_lease_id IN PN_LEASES_ALL.LEASE_ID%TYPE
,p_lease_change_id IN PN_LEASE_CHANGES_ALL.LEASE_CHANGE_ID%TYPE
,p_option_id IN PN_LEASE_MILESTONES_ALL.OPTION_ID%TYPE
,p_insurance_req_id IN PN_LEASE_MILESTONES_ALL.INSURANCE_REQUIREMENT_ID%TYPE
,p_payment_term_id IN PN_LEASE_MILESTONES_ALL.PAYMENT_TERM_ID%TYPE
,x_tbl OUT NOCOPY temp_tbl_type
,x_return_status IN OUT NOCOPY VARCHAR2
,p_commit IN VARCHAR2
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
l_api_name VARCHAR2(30) := 'UPDATE_MILESTONE';
select LEASE_MILESTONE_ID,
LEASE_CHANGE_ID,
MILESTONE_TYPE_CODE,
OPTION_ID,
INSURANCE_REQUIREMENT_ID,
PAYMENT_TERM_ID,
LEAD_DAYS,
EVERY_DAYS,
ACTION_TAKEN,
ACTION_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MILESTONE_DATE,
USER_ID,
LEASE_ID
from pn_lease_milestones_all
where lease_milestone_id = p_milestone_id;
pvt_debug(g_pkg_name||'-Inside VALIDATE_MILESTONE_FOR_UPDATE',3);
pvt_debug(g_pkg_name||'-VALIDATE_MILESTONE_FOR_UPDATE:MILESTONE TYPE AND MILESTONE TYPE CODE ARE NOT NULL',3);
, p_operation => 'UPDATE_MILESTONE'
, p_lookup_type => l_pn_milestones_type
, x_return_status => x_return_status
, x_lookup_type_code => p_milestone_tbl(l_index).milestone_type_code
);
pvt_debug(g_pkg_name||'-VALIDATE_MILESTONE_FOR_UPDATE:MILESTONE TYPE Code'||p_milestone_tbl(l_index).milestone_type_code,3);
, p_operation => 'UPDATE_MILESTONE'
, p_user_name => p_milestone_tbl(l_index).user_name
, x_user_id => p_milestone_tbl(l_index).user_id
, x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-VALIDATE_MILESTONE_FOR_UPDATE:LOOOPING:EXCEPTION:FND_API.G_EXC_ERROR'||SQLERRM,3);
pvt_debug(g_pkg_name||'-VALIDATE_MILESTONE_FOR_UPDATE:LOOOPING:EXCEPTION:'||SQLERRM,3);
pvt_debug(g_pkg_name||'-VALIDATE_MILESTONE_FOR_UPDATE:Exception:'||SQLERRM,3);
END VALIDATE_MILESTONE_FOR_UPDATE;
PROCEDURE MILESTONE_UPDATE_ROW(
p_milestone_tbl IN OUT NOCOPY lease_milestone_tbl_type
,p_lease_id IN PN_LEASES_ALL.LEASE_ID%TYPE
,p_lease_change_id IN PN_LEASE_CHANGES_ALL.LEASE_CHANGE_ID%TYPE
,p_option_id IN PN_LEASE_MILESTONES_ALL.OPTION_ID%TYPE
,p_insurance_req_id IN PN_LEASE_MILESTONES_ALL.INSURANCE_REQUIREMENT_ID%TYPE
,p_payment_term_id IN PN_LEASE_MILESTONES_ALL.PAYMENT_TERM_ID%TYPE
,x_return_status OUT NOCOPY VARCHAR2
,p_tbl IN temp_tbl_type
,p_commit IN VARCHAR2
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
pvt_debug(g_pkg_name||'-Inside MILESTONE_UPDATE_ROW',3);
pn_lease_milestones_pkg.Update_Row (
X_LEASE_MILESTONE_ID => p_milestone_tbl(l_index).lease_milestone_id,
X_LEASE_CHANGE_ID => p_lease_change_id,
X_MILESTONE_TYPE_CODE => p_milestone_tbl(l_index).milestone_type_code,
X_OPTION_ID => p_option_id,
X_INSURANCE_REQUIREMENT_ID => p_insurance_req_id,
X_PAYMENT_TERM_ID => p_payment_term_id,
X_LEAD_DAYS => p_milestone_tbl(l_index).lead_days,
X_EVERY_DAYS => p_milestone_tbl(l_index).frequency,
X_ACTION_TAKEN => null,
X_ACTION_DATE => null,
X_ATTRIBUTE_CATEGORY => p_milestone_tbl(l_index).attribute_category,
X_ATTRIBUTE1 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE2 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE3 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE4 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE5 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE6 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE7 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE8 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE9 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE10 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE11 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE12 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE13 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE14 => p_milestone_tbl(l_index).attribute1,
X_ATTRIBUTE15 => p_milestone_tbl(l_index).attribute1,
X_MILESTONE_DATE => p_milestone_tbl(l_index).action_due_date,
X_USER_ID => p_milestone_tbl(l_index).user_id,
X_LEASE_ID => p_lease_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.user_id
);
pvt_debug(g_pkg_name||'-MILESTONE_UPDATE_ROW:PN_LEASE_MILESTONES_PKG.UPDATE_ROW Successful',3);
pvt_debug(g_pkg_name||'-MILESTONE_UPDATE_ROW:PN_LEASE_MILESTONES_PKG.UPDATE_ROW:Exception'||SQLERRM,3);
pvt_debug(g_pkg_name||'-MILESTONE_UPDATE_ROW:Exception'||SQLERRM,3);
END MILESTONE_UPDATE_ROW;
PROCEDURE UPDATE_MILESTONE (
p_milestone_tbl IN OUT NOCOPY lease_milestone_tbl_type
, p_lease_id IN PN_LEASES_ALL.lease_id%type
, p_lease_change_id IN PN_LEASE_CHANGES_ALL.lease_change_id%type
, p_option_id IN PN_LEASE_MILESTONES_ALL.option_id%type
, p_insurance_req_id IN PN_LEASE_MILESTONES_ALL.insurance_requirement_id%type
, p_payment_term_id IN PN_LEASE_MILESTONES_ALL.payment_term_id%type
, p_commit IN VARCHAR2
, p_validate IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
-- , x_msg_count OUT NUMBER
-- , x_msg_data OUT VARCHAR2
)
IS
l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
pvt_debug(g_pkg_name||'-Inside UPDATE_MILESTONE',3);
VALIDATE_MILESTONE_FOR_UPDATE(p_milestone_tbl => p_milestone_tbl
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,p_option_id => p_option_id
,p_insurance_req_id => p_insurance_req_id
,p_payment_term_id => p_payment_term_id
,x_tbl => x_out_tbl
,x_return_status => x_return_status
,p_commit => p_commit
);
pvt_debug(g_pkg_name||'-UPDATE_MILESTONE:VALIDATE_MILESTONE_FOR_UPDATE Return Status:'||l_return_status,3);
pvt_debug(g_pkg_name||'-UPDATE_MILESTONE:Calling MILESTONE_UPDATE_ROW:',3);
MILESTONE_UPDATE_ROW(
p_milestone_tbl => p_milestone_tbl
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,p_option_id => p_option_id
,p_insurance_req_id => p_insurance_req_id
,p_payment_term_id => p_payment_term_id
,x_return_status => x_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
);
MILESTONE_INSERT_ROW(
p_milestone_tbl => p_milestone_tbl
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,p_option_id => p_option_id
,p_insurance_req_id => p_insurance_req_id
,p_payment_term_id => p_payment_term_id
,x_return_status => l_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
);*/
pvt_debug(g_pkg_name||'-UPDATE_MILESTONE:Exception'||SQLERRM,3);
END UPDATE_MILESTONE;
PROCEDURE INSURANCE_INSERT_ROW (p_insurance_tbl IN OUT NOCOPY lease_insurance_tbl_type
, p_lease_id IN PN_LEASES_ALL.lease_id%type
, p_lease_change_id IN PN_LEASE_CHANGES_ALL.lease_change_id%type
, x_return_status OUT NOCOPY VARCHAR2
, p_tbl IN temp_tbl_type
, p_commit IN VARCHAR2
, p_validate IN VARCHAR2
)
IS
l_index BINARY_INTEGER;
pvt_debug(g_pkg_name||'-Inside INSURANCE_INSERT_ROW',3);
pvt_debug(g_pkg_name||'-INSURANCE_INSERT_ROW:Calling PN_INSURANCE_REQUIREMENTS_PKG.INSERT_ROW',3);
pn_insurance_requirements_pkg.insert_row (
X_ROWID => l_row_id
,X_INSURANCE_REQUIREMENT_ID => l_insurance_requirement_id
,X_INSURANCE_TYPE_LOOKUP_CODE => p_insurance_tbl(l_index).insurance_type_lookup_code
,X_LEASE_ID => p_lease_id
,X_LEASE_CHANGE_ID => p_lease_change_id
,X_POLICY_START_DATE => trunc(p_insurance_tbl(l_index).policy_start_date)
,X_POLICY_EXPIRATION_DATE => trunc(p_insurance_tbl(l_index).policy_expiration_date)
,X_INSURER_NAME => p_insurance_tbl(l_index).insurer_name
,X_POLICY_NUMBER => p_insurance_tbl(l_index).policy_number
,X_INSURED_AMOUNT => p_insurance_tbl(l_index).insured_amount
,X_REQUIRED_AMOUNT => p_insurance_tbl(l_index).required_amount
,X_STATUS => p_insurance_tbl(l_index).status_code
,X_INSURANCE_COMMENTS => p_insurance_tbl(l_index).insurance_comments
,X_ATTRIBUTE_CATEGORY => p_insurance_tbl(l_index).attribute_category
,X_ATTRIBUTE1 => p_insurance_tbl(l_index).attribute1
,X_ATTRIBUTE2 => p_insurance_tbl(l_index).attribute2
,X_ATTRIBUTE3 => p_insurance_tbl(l_index).attribute3
,X_ATTRIBUTE4 => p_insurance_tbl(l_index).attribute4
,X_ATTRIBUTE5 => p_insurance_tbl(l_index).attribute5
,X_ATTRIBUTE6 => p_insurance_tbl(l_index).attribute6
,X_ATTRIBUTE7 => p_insurance_tbl(l_index).attribute7
,X_ATTRIBUTE8 => p_insurance_tbl(l_index).attribute8
,X_ATTRIBUTE9 => p_insurance_tbl(l_index).attribute9
,X_ATTRIBUTE10 => p_insurance_tbl(l_index).attribute10
,X_ATTRIBUTE11 => p_insurance_tbl(l_index).attribute11
,X_ATTRIBUTE12 => p_insurance_tbl(l_index).attribute12
,X_ATTRIBUTE13 => p_insurance_tbl(l_index).attribute13
,X_ATTRIBUTE14 => p_insurance_tbl(l_index).attribute14
,X_ATTRIBUTE15 => p_insurance_tbl(l_index).attribute15
,X_CREATION_DATE => sysdate
,X_CREATED_BY => fnd_global.user_id
,X_LAST_UPDATE_DATE => sysdate
,X_LAST_UPDATED_BY => fnd_global.user_id
,X_LAST_UPDATE_LOGIN => fnd_global.user_id
,x_org_id => g_org_id
);
pvt_debug(g_pkg_name||'-INSURANCE_INSERT_ROW:PN_INSURANCE_REQUIREMENTS_PKG.INSERT_ROW Succesful',3);
pvt_debug(g_pkg_name||'-INSURANCE_INSERT_ROW:Calling CREATE_MILESTONE',3);
pvt_debug(g_pkg_name||'-INSURANCE_INSERT_ROW:Exception'||SQLERRM,3);
END INSURANCE_INSERT_ROW;
INSURANCE_INSERT_ROW(p_insurance_tbl => p_insurance_tbl
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,x_return_status => x_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
,p_validate => p_validate );
PROCEDURE validate_insurance_for_update (
p_insurance_tbl IN OUT NOCOPY lease_insurance_tbl_type,
x_tbl OUT NOCOPY temp_tbl_type,
x_return_status IN OUT NOCOPY VARCHAR2,
p_lease_id IN pn_leases_all.lease_id%TYPE,
p_lease_change_id IN pn_lease_changes_all.lease_change_id%TYPE
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
l_api_name VARCHAR2(30) := 'UPDATE_INSURANCE';
SELECT insurance_requirement_id,
status,
insurance_type_lookup_code,
lease_id,
lease_change_id,
policy_start_date,
policy_expiration_date,
notification_date,
insurer_name,
policy_number,
insured_amount,
required_amount,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
insurance_comments
from PN_INSURANCE_REQUIREMENTS_ALL
where insurance_requirement_id = l_cur_id;
pvt_debug(g_pkg_name||'-Inside VALIDATE_INSURANCE_FOR_UPDATE',3);
pvt_debug(g_pkg_name||'-Inside VALIDATE_INSURANCE_FOR_UPDATE ELSE Part',3);
, p_operation => 'UPDATE_INSURANCE'
, p_lease_exec_date => NULL
, p_lease_comm_date => p_insurance_tbl(l_index).policy_start_date
, p_lease_term_date => p_insurance_tbl(l_index).policy_expiration_date
, x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-Inside VALIDATE_INSURANCE_FOR_UPDATE:CROSSVAL_LEASEDATES Return Status'||x_return_status,3);
, p_operation => 'UPDATE_INSURANCE'
, p_lookup_type => l_pn_status
, x_return_status => x_return_status
, x_lookup_type_code => p_insurance_tbl(l_index).status_code
);
pvt_debug(g_pkg_name||'-VALIDATE_INSURANCE_FOR_UPDATE:Insurance Status:'||p_insurance_tbl(l_index).status_code,3);
, p_operation => 'UPDATE_INSURANCE'
, p_lookup_type => l_pn_insurance_type
, x_return_status => x_return_status
, x_lookup_type_code => p_insurance_tbl(l_index).insurance_type_lookup_code
);
pvt_debug(g_pkg_name||'-VALIDATE_INSURANCE_FOR_UPDATE:Insurance Type:'||p_insurance_tbl(l_index).insurance_type_lookup_code,3);
pvt_debug(g_pkg_name||'-VALIDATE_INSURANCE_FOR_UPDATE:LOOOPING:EXCEPTION:FND_API.G_EXC_ERROR'||SQLERRM,3);
pvt_debug(g_pkg_name||'-VALIDATE_INSURANCE_FOR_UPDATE:LOOOPING:EXCEPTION:'||SQLERRM,3);
pvt_debug(g_pkg_name||'-VALIDATE_INSURANCE_FOR_UPDATE:Exception'||SQLERRM,3);
END VALIDATE_INSURANCE_FOR_UPDATE;
PROCEDURE INSURANCE_UPDATE_ROW (p_insurance_tbl IN OUT NOCOPY lease_insurance_tbl_type
,p_lease_id IN PN_LEASES_ALL.LEASE_ID%type
,p_lease_change_id IN PN_LEASE_CHANGES_ALL.lease_change_id%type
,x_return_status OUT NOCOPY VARCHAR2
,p_tbl IN temp_tbl_type
,p_commit IN VARCHAR2
,p_validate IN VARCHAR2
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
pvt_debug(g_pkg_name||'-Inside INSURANCE_UPDATE_ROW:',3);
pvt_debug(g_pkg_name||'-INSURANCE_UPDATE_ROW:'||'UPDATE INSURANCE PROCEDURE FOR RECORD: '||l_index ||'STARTED',3);
pn_insurance_requirements_pkg.Update_Row
(
X_INSURANCE_REQUIREMENT_ID => p_insurance_tbl(l_index).insurance_requirement_id,
X_INSURANCE_TYPE_LOOKUP_CODE => p_insurance_tbl(l_index).insurance_type_lookup_code,
X_LEASE_ID => p_lease_id,
X_LEASE_CHANGE_ID => p_lease_change_id,
X_POLICY_START_DATE => trunc(p_insurance_tbl(l_index).policy_start_date),
X_POLICY_EXPIRATION_DATE => trunc(p_insurance_tbl(l_index).policy_expiration_date),
X_INSURER_NAME => p_insurance_tbl(l_index).insurer_name,
X_POLICY_NUMBER => p_insurance_tbl(l_index).policy_number,
X_INSURED_AMOUNT => p_insurance_tbl(l_index).insured_amount,
X_REQUIRED_AMOUNT => p_insurance_tbl(l_index).required_amount,
X_STATUS => p_insurance_tbl(l_index).status_code,
X_INSURANCE_COMMENTS => p_insurance_tbl(l_index).insurance_comments,
X_ATTRIBUTE_CATEGORY => p_insurance_tbl(l_index).attribute_category,
X_ATTRIBUTE1 => p_insurance_tbl(l_index).attribute1,
X_ATTRIBUTE2 => p_insurance_tbl(l_index).attribute2,
X_ATTRIBUTE3 => p_insurance_tbl(l_index).attribute3,
X_ATTRIBUTE4 => p_insurance_tbl(l_index).attribute4,
X_ATTRIBUTE5 => p_insurance_tbl(l_index).attribute5,
X_ATTRIBUTE6 => p_insurance_tbl(l_index).attribute6,
X_ATTRIBUTE7 => p_insurance_tbl(l_index).attribute7,
X_ATTRIBUTE8 => p_insurance_tbl(l_index).attribute8,
X_ATTRIBUTE9 => p_insurance_tbl(l_index).attribute9,
X_ATTRIBUTE10 => p_insurance_tbl(l_index).attribute10,
X_ATTRIBUTE11 => p_insurance_tbl(l_index).attribute11,
X_ATTRIBUTE12 => p_insurance_tbl(l_index).attribute12,
X_ATTRIBUTE13 => p_insurance_tbl(l_index).attribute13,
X_ATTRIBUTE14 => p_insurance_tbl(l_index).attribute14,
X_ATTRIBUTE15 => p_insurance_tbl(l_index).attribute15,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
pvt_debug(g_pkg_name||'-INSURANCE_UPDATE_ROW:'||'UPDATE INSURANCE PROCEDURE FOR RECORD: '||l_index ||'Completed',3);
pvt_debug(g_pkg_name||'-INSURANCE_UPDATE_ROW:PN_INSURANCE_REQUIREMENTS_PKG.UPDATE_ROW:Exception:'||SQLERRM,3);
UPDATE_MILESTONE (
p_milestone_tbl => p_insurance_tbl(l_index).insurance_milestone_tbl
, p_lease_id => p_lease_id
, p_lease_change_id => p_lease_change_id
, p_option_id => NULL
, p_insurance_req_id => l_insurance_requirement_id
, p_payment_term_id => NULL
, p_commit => p_commit
, p_validate => 'N'
, x_return_status => x_return_status
-- , x_msg_count => x_msg_count
-- , x_msg_data => x_msg_data
);
pvt_debug(g_pkg_name||'-INSURANCE_UPDATE_ROW:Exception:'||SQLERRM,3);
END INSURANCE_UPDATE_ROW;
PROCEDURE UPDATE_INSURANCE (
p_lease_id IN PN_LEASES_ALL.LEASE_ID%TYPE
, p_lease_change_id IN PN_LEASE_CHANGES_ALL.LEASE_CHANGE_ID%TYPE
, p_commit IN VARCHAR2
, p_validate IN VARCHAR2
, p_insurance_tbl IN OUT NOCOPY lease_insurance_tbl_type
, x_return_status OUT NOCOPY VARCHAR2
-- , x_msg_count OUT NUMBER
-- , x_msg_data OUT VARCHAR2
)
IS
l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
p_insurance_tbl_update lease_insurance_tbl_type;
p_insurance_tbl_update(n):=p_insurance_tbl(i);
IF(p_insurance_tbl_update.COUNT > 0) THEN
VALIDATE_INSURANCE_FOR_UPDATE(
p_insurance_tbl => p_insurance_tbl_update
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,x_tbl => x_out_tbl
,x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-Inside UPDATE_INSURANCE:',3);
INSURANCE_UPDATE_ROW(
p_insurance_tbl => p_insurance_tbl_update--p_insurance_tbl
, p_lease_id => p_lease_id
, p_lease_change_id => p_lease_change_id
, x_return_status => x_return_status
, p_tbl => x_out_tbl
, p_commit => p_commit
, p_validate => p_validate
);
pvt_debug(g_pkg_name||'-UPDATE_INSURANCE:INSURANCE_UPDATE_ROW Return Status:'||x_return_status,3);
pvt_debug(g_pkg_name||'Inside UPDATE_INSURANCE:VALIDATE_INSURANCE_FOR_CREATE Return Status'||x_return_status,3);
INSURANCE_INSERT_ROW(p_insurance_tbl => p_insurance_tbl_create
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,x_return_status => x_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
,p_validate => p_validate );
pvt_debug(g_pkg_name||'Inside UPDATE_INSURANCE:INSURANCE_INSERT_ROW Return Status'||x_return_status,3);
END UPDATE_INSURANCE;
PROCEDURE OPTION_INSERT_ROW(
p_option_tbl IN OUT NOCOPY lease_option_tbl_type
, p_lease_id IN PN_LEASES_ALL.lease_id%type
, p_lease_change_id IN PN_LEASE_CHANGES_ALL.lease_change_id%type
, x_return_status OUT NOCOPY VARCHAR2
, p_tbl IN temp_tbl_type
, p_commit IN VARCHAR2
, p_validate IN VARCHAR2
)
IS
l_index BINARY_INTEGER;
pvt_debug(g_pkg_name||'-Inside OPTION_INSERT_ROW',3);
pvt_debug(g_pkg_name||'OPTION_INSERT_ROW:Calling pn_options_pkg.insert_row',3);
pn_options_pkg.insert_row (
x_rowid => l_row_id
, x_option_id => l_option_id
, x_attribute14 => p_option_tbl(l_index).attribute14
, x_attribute15 => p_option_tbl(l_index).attribute15
, x_lease_id => p_lease_id
, x_lease_change_id => p_lease_change_id
, x_option_num => p_option_tbl(l_index).option_num
, x_option_type_code => p_option_tbl(l_index).option_type_code
, x_start_date => p_option_tbl(l_index).start_date
, x_expiration_date => p_option_tbl(l_index).expiration_date
, x_option_size => p_option_tbl(l_index).option_size
, x_uom_code => p_option_tbl(l_index).uom_code
, x_option_status_lookup_code=> p_option_tbl(l_index).option_status_lookup_code
, x_attribute_category => p_option_tbl(l_index).attribute_category
, x_attribute1 => p_option_tbl(l_index).attribute1
, x_attribute2 => p_option_tbl(l_index).attribute2
, x_attribute3 => p_option_tbl(l_index).attribute3
, x_attribute4 => p_option_tbl(l_index).attribute4
, x_attribute5 => p_option_tbl(l_index).attribute5
, x_attribute6 => p_option_tbl(l_index).attribute6
, x_attribute7 => p_option_tbl(l_index).attribute7
, x_attribute8 => p_option_tbl(l_index).attribute8
, x_attribute9 => p_option_tbl(l_index).attribute9
, x_attribute10 => p_option_tbl(l_index).attribute10
, x_attribute11 => p_option_tbl(l_index).attribute11
, x_attribute12 => p_option_tbl(l_index).attribute12
, x_attribute13 => p_option_tbl(l_index).attribute13
, x_creation_date => sysdate
, x_created_by => fnd_global.user_id
, x_last_update_date => sysdate
, x_last_updated_by => fnd_global.user_id
, x_last_update_login => fnd_global.login_id
, x_option_exer_start_date => p_option_tbl(l_index).option_exer_start_date
, x_option_exer_end_date => p_option_tbl(l_index).option_exer_end_date
, x_option_action_date => p_option_tbl(l_index).option_action_date
, x_option_cost => p_option_tbl(l_index).option_cost
, x_option_area_change => p_option_tbl(l_index).option_area_change
, x_option_reference => p_option_tbl(l_index).option_reference
, x_option_notice_reqd => p_option_tbl(l_index).option_notice_reqd
, x_option_comments => p_option_tbl(l_index).comments
, x_org_id => fnd_global.org_id
);
pvt_debug(g_pkg_name||'OPTION_INSERT_ROW:Calling PN_OPTIONS_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'OPTION_INSERT_ROW:Exception:PN_OPTIONS_PKG.INSERT_ROW',3);
pvt_debug(g_pkg_name||'OPTION_INSERT_ROW:CREATE_MILESTONE Return Status:'||l_return_status,3);
pvt_debug(g_pkg_name||'OPTION_INSERT_ROW:CREATE_MILESTONE:Exception:'||SQLERRM,3);
END OPTION_INSERT_ROW;
OPTION_INSERT_ROW( p_option_tbl => p_option_tbl
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,x_return_status => x_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
,p_validate => p_validate
);
pvt_debug(g_pkg_name||'Inside CREATE_OPTION:OPTION_INSERT_ROW Return Status'||x_return_status,3);
PROCEDURE VALIDATE_OPTION_FOR_UPDATE(
p_option_tbl IN OUT NOCOPY lease_option_tbl_type
,p_lease_id IN PN_LEASES_ALL.LEASE_ID%TYPE
,p_lease_change_id IN PN_LEASE_CHANGES_ALL.LEASE_CHANGE_ID%TYPE
,x_tbl OUT NOCOPY temp_tbl_type
,x_return_status IN OUT NOCOPY VARCHAR2
,p_commit IN VARCHAR2
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
l_api_name VARCHAR2(30):= 'UPDATE OPTIONS';
SELECT
option_id,
lease_id,
lease_change_id,
option_num,
option_type_code,
option_status_lookup_code,
start_date,
expiration_date,
option_size,
uom_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
option_exer_start_date,
option_exer_end_date,
option_action_date,
option_cost,
option_area_change,
option_reference,
option_notice_reqd,
option_comments
FROM PN_OPTIONS_ALL
where option_id = p_option_id;
pvt_debug(g_pkg_name||'Inside VALIDATE_OPTION_FOR_UPDATE',3);
pvt_debug(g_pkg_name||'VALIDATE_OPTION_FOR_UPDATE:OPTION_ID IS NULL',3);
pvt_debug(g_pkg_name||'-VALIDATE_OPTION_FOR_UPDATE:LOOOPING:EXCEPTION:FND_API.G_EXC_ERROR'||SQLERRM,3);
pvt_debug(g_pkg_name||'-VALIDATE_OPTION_FOR_UPDATE:LOOOPING:EXCEPTION:'||SQLERRM,3);
pvt_debug(g_pkg_name||'VALIDATE_OPTION_FOR_UPDATE:Exception:'||SQLERRM,3);
END VALIDATE_OPTION_FOR_UPDATE;
PROCEDURE OPTION_UPDATE_ROW(
p_option_tbl IN OUT NOCOPY lease_option_tbl_type
,p_lease_id IN PN_LEASES_ALL.LEASE_ID%type
,p_lease_change_id IN PN_LEASE_CHANGES_ALL.lease_change_id%type
,x_return_status OUT NOCOPY VARCHAR2
,p_tbl IN temp_tbl_type
,p_commit IN VARCHAR2
,p_validate IN VARCHAR2
)
IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
pvt_debug(g_pkg_name||'OPTION_UPDATE_ROW:Calling PN_OPTIONS_PKG.UPDATE_ROW',3);
pn_options_pkg.update_row (
x_option_id => p_option_tbl(l_index).option_id
, x_lease_id => p_lease_id
, x_lease_change_id => p_lease_change_id
, x_option_num => p_option_tbl(l_index).option_num
, x_option_type_code => p_option_tbl(l_index).option_type_code
, x_start_date => p_option_tbl(l_index).start_date
, x_expiration_date => p_option_tbl(l_index).expiration_date
, x_option_size => p_option_tbl(l_index).option_size
, x_uom_code => p_option_tbl(l_index).uom_code
, x_option_status_lookup_code => p_option_tbl(l_index).option_status_lookup_code
, x_attribute_category => p_option_tbl(l_index).attribute_category
, x_attribute1 => p_option_tbl(l_index).attribute1
, x_attribute2 => p_option_tbl(l_index).attribute2
, x_attribute3 => p_option_tbl(l_index).attribute3
, x_attribute4 => p_option_tbl(l_index).attribute4
, x_attribute5 => p_option_tbl(l_index).attribute5
, x_attribute6 => p_option_tbl(l_index).attribute6
, x_attribute7 => p_option_tbl(l_index).attribute7
, x_attribute8 => p_option_tbl(l_index).attribute8
, x_attribute9 => p_option_tbl(l_index).attribute9
, x_attribute10 => p_option_tbl(l_index).attribute10
, x_attribute11 => p_option_tbl(l_index).attribute11
, x_attribute12 => p_option_tbl(l_index).attribute12
, x_attribute13 => p_option_tbl(l_index).attribute13
, x_attribute14 => p_option_tbl(l_index).attribute14
, x_attribute15 => p_option_tbl(l_index).attribute15
, x_last_update_date => sysdate
, x_last_updated_by => fnd_global.user_id
, x_last_update_login => fnd_global.login_id
, x_option_exer_start_date => p_option_tbl(l_index).option_exer_start_date
, x_option_exer_end_date => p_option_tbl(l_index).option_exer_end_date
, x_option_action_date => p_option_tbl(l_index).option_action_date
, x_option_cost => p_option_tbl(l_index).option_cost
, x_option_area_change => p_option_tbl(l_index).option_area_change
, x_option_reference => p_option_tbl(l_index).option_reference
, x_option_notice_reqd => p_option_tbl(l_index).option_notice_reqd
, x_option_comments => p_option_tbl(l_index).comments
);
pvt_debug(g_pkg_name||'OPTION_UPDATE_ROW:PN_OPTIONS_PKG.UPDATE_ROW Successful',3);
pvt_debug(g_pkg_name||'OPTION_UPDATE_ROW:PN_OPTIONS_PKG.UPDATE_ROW;Exception'||SQLERRM,3);
pvt_debug(g_pkg_name||'OPTION_UPDATE_ROW:Calling UPDATE_MILESTONE',3);
UPDATE_MILESTONE (
p_milestone_tbl => p_option_tbl(l_index).option_milestone_tbl
, p_lease_id => p_lease_id
, p_lease_change_id => p_lease_change_id
, p_option_id => l_option_id
, p_insurance_req_id => NULL
, p_payment_term_id => NULL
, p_commit => p_commit
, p_validate => 'N'
, x_return_status => x_return_status
-- , x_msg_count => x_msg_count
-- , x_msg_data => x_msg_data
);
pvt_debug(g_pkg_name||'OPTION_UPDATE_ROW:Update Mile Stone Successful',3);
pvt_debug(g_pkg_name||'OPTION_UPDATE_ROW:Exception:'||SQLERRM,3);
END OPTION_UPDATE_ROW;
PROCEDURE UPDATE_OPTION (
p_lease_id IN PN_LEASES_ALL.LEASE_ID%TYPE
, p_lease_change_id IN PN_LEASE_CHANGES_ALL.LEASE_CHANGE_ID%TYPE
, p_commit IN VARCHAR2
, p_validate IN VARCHAR2
, p_option_tbl IN OUT NOCOPY lease_option_tbl_type
, x_return_status OUT NOCOPY VARCHAR2
-- , x_msg_count OUT NUMBER
-- , x_msg_data OUT VARCHAR2
)
IS
l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
p_option_tbl_update lease_option_tbl_type;
p_option_tbl_update(n):=p_option_tbl(i);
IF(p_option_tbl_update.COUNT > 0) THEN
VALIDATE_OPTION_FOR_UPDATE(
p_option_tbl => p_option_tbl_update--p_option_tbl
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,x_tbl => x_out_tbl
,x_return_status => x_return_status
,p_commit => p_commit
);
pvt_debug(g_pkg_name||'Inside UPDATE_OPTION',3);
OPTION_UPDATE_ROW(
p_option_tbl => p_option_tbl_update--p_option_tbl
, p_lease_id => p_lease_id
, p_lease_change_id => p_lease_change_id
, x_return_status => x_return_status
, p_tbl => x_out_tbl
, p_commit => p_commit
, p_validate => p_validate
);
pvt_debug(g_pkg_name||'UPDATE_OPTION:OPTION_UPDATE_ROW Return Status:'||x_return_status,3);
OPTION_INSERT_ROW( p_option_tbl => p_option_tbl_create
,p_lease_id => p_lease_id
,p_lease_change_id => p_lease_change_id
,x_return_status => x_return_status
,p_tbl => x_out_tbl
,p_commit => p_commit
,p_validate => p_validate
);
pvt_debug(g_pkg_name||'Inside UPDATE_OPTION:OPTION_INSERT_ROW Return Status'||x_return_status,3);
pvt_debug(g_pkg_name||'UPDATE_OPTION:Exception:'||SQLERRM,3);
END UPDATE_OPTION;
lease_insert_row ( p_lease_rec => p_lease_rec
, p_lease_exp_rev_accounts_rec => p_lease_exp_rev_accounts_rec
, p_lease_accrual_account_rec => p_lease_accrual_account_rec
, p_lease_liab_rcvl_account_rec => p_lease_liab_rcvl_account_rec
, p_location_exist => fnd_api.g_false
, p_lease_context => p_lease_context
, x_return_status => x_return_status);
pvt_debug(g_pkg_name||'-CREATE_LEASE:LEASE_INSERT_ROW Return Status:'||x_return_status,3);
/* Update Lease API*/
PROCEDURE update_lease (
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validate IN VARCHAR2 := fnd_api.g_false
, p_lease_rec IN OUT NOCOPY lease_rec
, p_lease_exp_rev_accounts_rec IN OUT NOCOPY lease_account_rec
, p_lease_accrual_account_rec IN OUT NOCOPY lease_account_rec
, p_lease_liab_rcvl_account_rec IN OUT NOCOPY lease_account_rec
, p_lease_contacts_tbl IN OUT NOCOPY lease_contacts_tbl
, p_lease_tenancies_tbl IN OUT NOCOPY lease_tenancies_tbl
, p_lease_context IN VARCHAR2 := 'UPD'
, p_right_tbl IN OUT NOCOPY lease_right_tbl_type
, p_note_tbl IN OUT NOCOPY lease_note_tbl_type
, p_obligation_tbl IN OUT NOCOPY lease_obligation_tbl_type
, p_insurance_tbl IN OUT NOCOPY lease_insurance_tbl_type
, p_option_tbl IN OUT NOCOPY lease_option_tbl_type
, p_lease_payment_term_tbl IN OUT NOCOPY lease_terms_tbl --Rupak
-- , x_lease_id OUT NOCOPY NUMBER
-- , x_lease_number OUT NOCOPY VARCHAR2
-- , x_lease_name OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
AS
CURSOR cur_lease_header
IS
SELECT lease_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, NAME
, lease_num
, parent_lease_id
, lease_type_code
, payment_term_proration_rule
, abstracted_by_user
, comments
, status
, org_id
, lease_class_code
, lease_status
, location_id
, customer_id
, cal_start
FROM pn_leases_all
WHERE lease_id = p_lease_rec.lease_id;
SELECT lease_detail_id
, lease_change_id
, lease_id
, responsible_user
, expense_account_id
, lease_commencement_date
, lease_termination_date
, lease_execution_date
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, org_id
, accrual_account_id
, receivable_account_id
, send_entries
, term_template_id
, lease_extension_end_date
, grouping_rule_id
FROM pn_lease_details_all
WHERE lease_id = p_lease_rec.lease_id;
l_lastupdatedate DATE := NULL;
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_LEASE';
SAVEPOINT update_lease_pvt;
, p_operation => 'UPDATE_LEASE'
, p_lookup_meaning => p_lease_rec.lease_type
, p_lookup_type => l_leasetype_lookup_type
, x_lookup_type_code => p_lease_rec.lease_type_code--l_lease_hdr_rec.lease_type_code
, x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-UPDATE_LEASE:Lease Type Code'||p_lease_rec.lease_type_code,3);
, p_operation => 'UPDATE_LEASE'
, p_customer_name => p_lease_rec.customer_name
, x_customer_id => p_lease_rec.customer_id
, x_return_status => x_return_status
);
, p_operation => 'UPDATE_LEASE'
, p_user_name => p_lease_rec.abstracted_by_user
, x_user_id => p_lease_rec.abstracted_by_user_id--l_lease_hdr_rec.abstracted_by_user
, x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-UPDATE_LEASE:ABSTRACTED_BY_USERNAME:'||p_lease_rec.abstracted_by_user_id,3);
, p_operation => 'UPDATE_LEASE'
, x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-UPDATE_LEASE:GET_CAL_START_DATE Return Status:'||x_return_status,3);
, p_operation => 'UPDATE_LEASE'
, p_lookup_meaning => p_lease_rec.pymt_term_pro_rule
, p_lookup_type => l_proration_rule_lookup_type
, x_lookup_type_code => p_lease_rec.pymt_term_pro_rule_id--l_lease_hdr_rec.payment_term_proration_rule
, x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-UPDATE_LEASE:Proration Rule ID:'||l_lease_hdr_rec.payment_term_proration_rule,3);
, p_operation => 'UPDATE_LEASE'
, p_lookup_meaning => p_lease_rec.status
, p_lookup_type => l_status_lookup_type
, x_lookup_type_code => p_lease_rec.status_code
, x_return_status => x_return_status
);
fnd_message.set_token('ERR_MSG', 'You are trying to update an existing finalized lease by passing status as Draft. Please provide valid lease status');
, p_operation => 'UPDATE_LEASE'
, p_org_id => l_lease_hdr_rec.org_id
, p_lease_class_code => l_lease_hdr_rec.lease_class_code
, p_parent_lease_id => l_lease_hdr_rec.parent_lease_id
, p_lease_comm_date => l_lease_dtl_rec.lease_commencement_date
, p_lease_term_date => l_lease_dtl_rec.lease_termination_date
, x_return_status => x_return_status
, x_location_id => p_lease_rec.location_id--l_lease_hdr_rec.location_id
);
pvt_debug(g_pkg_name||'-UPDATE_LEASE:PN_LEASE_UTILS.GET_LOCATION_ID Return Status:'||x_return_status,3);
, p_operation => 'UPDATE_LEASE'
, p_lookup_meaning => p_lease_rec.lease_status
, p_lookup_type => l_lease_status_lookup_type
, x_lookup_type_code => p_lease_rec.lease_status_code--l_lease_hdr_rec.lease_status
, x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-UPDATE_LEASE:Lease Status:'||p_lease_rec.lease_status,3);
, p_operation => 'UPDATE_LEASE'
, x_return_status => x_return_status
, x_user_id => p_lease_rec.responsible_user_id--l_lease_dtl_rec.responsible_user
);
pvt_debug(g_pkg_name||'-UPDATE_LEASE:RESPONSIBLE_USER:'||p_lease_rec.responsible_user_id,3);
, p_operation => 'UPDATE_LEASE'
, p_lease_exec_date => l_lease_dtl_rec.lease_execution_date
, p_lease_comm_date => l_lease_dtl_rec.lease_commencement_date
, p_lease_term_date => l_lease_dtl_rec.lease_termination_date
, x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-UPDATE_LEASE:PN_LEASE_UTILS.CROSSVAL_LEASEDATES Return Status:'||x_return_status,3);
, p_operation => 'UPDATE_LEASE'
, p_account_id => p_lease_exp_rev_accounts_rec.account_id
, p_account_conc_segs => p_lease_exp_rev_accounts_rec.account_conc_segs
, p_account_segment1 => p_lease_exp_rev_accounts_rec.account_segment1
, p_account_segment2 => p_lease_exp_rev_accounts_rec.account_segment2
, p_account_segment3 => p_lease_exp_rev_accounts_rec.account_segment3
, p_account_segment4 => p_lease_exp_rev_accounts_rec.account_segment4
, p_account_segment5 => p_lease_exp_rev_accounts_rec.account_segment5
, p_account_segment6 => p_lease_exp_rev_accounts_rec.account_segment6
, p_account_segment7 => p_lease_exp_rev_accounts_rec.account_segment7
, p_account_segment8 => p_lease_exp_rev_accounts_rec.account_segment8
, p_account_segment9 => p_lease_exp_rev_accounts_rec.account_segment9
, p_account_segment10 => p_lease_exp_rev_accounts_rec.account_segment10
, p_account_segment11 => p_lease_exp_rev_accounts_rec.account_segment11
, p_account_segment12 => p_lease_exp_rev_accounts_rec.account_segment12
, p_account_segment13 => p_lease_exp_rev_accounts_rec.account_segment13
, p_account_segment14 => p_lease_exp_rev_accounts_rec.account_segment14
, p_account_segment15 => p_lease_exp_rev_accounts_rec.account_segment15
, p_account_segment16 => p_lease_exp_rev_accounts_rec.account_segment16
, p_account_segment17 => p_lease_exp_rev_accounts_rec.account_segment17
, p_account_segment18 => p_lease_exp_rev_accounts_rec.account_segment18
, p_account_segment19 => p_lease_exp_rev_accounts_rec.account_segment19
, p_account_segment20 => p_lease_exp_rev_accounts_rec.account_segment20
, x_return_status => x_return_status);
, p_operation => 'UPDATE_LEASE'
, p_account_id => p_lease_accrual_account_rec.account_id
, p_account_conc_segs => p_lease_accrual_account_rec.account_conc_segs
, p_account_segment1 => p_lease_accrual_account_rec.account_segment1
, p_account_segment2 => p_lease_accrual_account_rec.account_segment2
, p_account_segment3 => p_lease_accrual_account_rec.account_segment3
, p_account_segment4 => p_lease_accrual_account_rec.account_segment4
, p_account_segment5 => p_lease_accrual_account_rec.account_segment5
, p_account_segment6 => p_lease_accrual_account_rec.account_segment6
, p_account_segment7 => p_lease_accrual_account_rec.account_segment7
, p_account_segment8 => p_lease_accrual_account_rec.account_segment8
, p_account_segment9 => p_lease_accrual_account_rec.account_segment9
, p_account_segment10 => p_lease_accrual_account_rec.account_segment10
, p_account_segment11 => p_lease_accrual_account_rec.account_segment11
, p_account_segment12 => p_lease_accrual_account_rec.account_segment12
, p_account_segment13 => p_lease_accrual_account_rec.account_segment13
, p_account_segment14 => p_lease_accrual_account_rec.account_segment14
, p_account_segment15 => p_lease_accrual_account_rec.account_segment15
, p_account_segment16 => p_lease_accrual_account_rec.account_segment16
, p_account_segment17 => p_lease_accrual_account_rec.account_segment17
, p_account_segment18 => p_lease_accrual_account_rec.account_segment18
, p_account_segment19 => p_lease_accrual_account_rec.account_segment19
, p_account_segment20 => p_lease_accrual_account_rec.account_segment20
, x_return_status => x_return_status);
, p_operation => 'UPDATE_LEASE'
, p_account_id => p_lease_liab_rcvl_account_rec.account_id
, p_account_conc_segs => p_lease_liab_rcvl_account_rec.account_conc_segs
, p_account_segment1 => p_lease_liab_rcvl_account_rec.account_segment1
, p_account_segment2 => p_lease_liab_rcvl_account_rec.account_segment2
, p_account_segment3 => p_lease_liab_rcvl_account_rec.account_segment3
, p_account_segment4 => p_lease_liab_rcvl_account_rec.account_segment4
, p_account_segment5 => p_lease_liab_rcvl_account_rec.account_segment5
, p_account_segment6 => p_lease_liab_rcvl_account_rec.account_segment6
, p_account_segment7 => p_lease_liab_rcvl_account_rec.account_segment7
, p_account_segment8 => p_lease_liab_rcvl_account_rec.account_segment8
, p_account_segment9 => p_lease_liab_rcvl_account_rec.account_segment9
, p_account_segment10 => p_lease_liab_rcvl_account_rec.account_segment10
, p_account_segment11 => p_lease_liab_rcvl_account_rec.account_segment11
, p_account_segment12 => p_lease_liab_rcvl_account_rec.account_segment12
, p_account_segment13 => p_lease_liab_rcvl_account_rec.account_segment13
, p_account_segment14 => p_lease_liab_rcvl_account_rec.account_segment14
, p_account_segment15 => p_lease_liab_rcvl_account_rec.account_segment15
, p_account_segment16 => p_lease_liab_rcvl_account_rec.account_segment16
, p_account_segment17 => p_lease_liab_rcvl_account_rec.account_segment17
, p_account_segment18 => p_lease_liab_rcvl_account_rec.account_segment18
, p_account_segment19 => p_lease_liab_rcvl_account_rec.account_segment19
, p_account_segment20 => p_lease_liab_rcvl_account_rec.account_segment20
, x_return_status => x_return_status);
pvt_debug(g_pkg_name||'UPDATE_LEASE:ATTRIBUTES: Return Status:'||x_return_status,3);
l_lastupdatedate := SYSDATE;
pvt_debug(g_pkg_name||'UPDATE_LEASE:INIT_LEASE_REC: Return Status:'||x_return_status,3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:INIT_LEASE_REC: Exception:'||SQLERRM,3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Calling Edit Lease',3);
, p_last_update_date => SYSDATE
, x_lease_change_id => l_leasechangeid
, x_lease_change_num => l_lease_change_num);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Edit Lease Return Status'||l_return_status,3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Calling Amend Lease',3);
, p_last_update_date => SYSDATE
, p_user_id => g_user_id
, x_lease_change_id => l_leasechangeid
, x_lease_change_num => l_lease_change_num);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Amend Lease Return Status'||l_return_status,3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Raising the Business Event in Amend Mode',3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Business Event Successful',3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Business Event Exception in Amend Mode'||SQLERRM,3);
SELECT tenancy_id
INTO l_tenancy_id
FROM pn_tenancies_all
WHERE lease_id = l_lease_hdr_rec.lease_id
AND org_id = g_org_id;
pvt_debug(g_pkg_name||'UPDATE_LEASE:After Direct Update Lease tenancy_id:'||l_tenancy_id||'-'||x_return_status||'-'||l_lease_hdr_rec.status||'-'||l_status,3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Calling Direct Update Lease:Status'||l_status_new||'-'||l_lease_hdr_rec.status,3);
direct_update_lease
(p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_lease_id => p_lease_rec.lease_id
, p_lease_detail_id => l_lease_dtl_rec.lease_detail_id
, p_lease_change_id => l_leasechangeid
, p_name => l_lease_hdr_rec.NAME
, p_lease_num => l_lease_hdr_rec.lease_num
, p_parent_lease_id => l_lease_hdr_rec.parent_lease_id
, p_lease_type_code => l_lease_hdr_rec.lease_type_code
, p_lease_class_code => l_lease_hdr_rec.lease_class_code
, p_payment_term_proration_rule => l_lease_hdr_rec.payment_term_proration_rule
, p_abstracted_by_user => l_lease_hdr_rec.abstracted_by_user
, p_status => l_status_new--l_lease_hdr_rec.status
, p_lease_status => l_lease_hdr_rec.lease_status
, p_last_update_date => l_lastupdatedate
, p_last_updated_by => g_user_id
, p_last_update_login => g_login_id
, p_responsible_user => l_lease_dtl_rec.responsible_user
, p_expense_account_id => l_lease_dtl_rec.expense_account_id
, p_accrual_account_id => l_lease_dtl_rec.accrual_account_id
, p_receivable_account_id => l_lease_dtl_rec.receivable_account_id
, p_term_template_id => l_lease_dtl_rec.term_template_id
, p_lease_commencement_date => l_lease_dtl_rec.lease_commencement_date
, p_lease_termination_date => l_lease_dtl_rec.lease_termination_date
, p_lease_execution_date => l_lease_dtl_rec.lease_execution_date
, p_lease_extension_end_date => l_leaseextensionenddate
, p_cal_start => l_lease_hdr_rec.cal_start
, p_attribute_category => l_lease_rec.attribute_category
, p_attribute1 => l_lease_rec.attribute1
, p_attribute2 => l_lease_rec.attribute2
, p_attribute3 => l_lease_rec.attribute3
, p_attribute4 => l_lease_rec.attribute4
, p_attribute5 => l_lease_rec.attribute5
, p_attribute6 => l_lease_rec.attribute6
, p_attribute7 => l_lease_rec.attribute7
, p_attribute8 => l_lease_rec.attribute8
, p_attribute9 => l_lease_rec.attribute9
, p_attribute10 => l_lease_rec.attribute10
, p_attribute11 => l_lease_rec.attribute11
, p_attribute12 => l_lease_rec.attribute12
, p_attribute13 => l_lease_rec.attribute13
, p_attribute14 => l_lease_rec.attribute14
, p_attribute15 => l_lease_rec.attribute15
, p_location_id => l_lease_hdr_rec.location_id
, p_customer_id => l_lease_hdr_rec.customer_id
, p_grouping_rule_id => l_lease_dtl_rec.grouping_rule_id);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Direct Update Lease Return Status'||x_return_status,3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Raising the Business Event',3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Business Event Successful',3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Business Event Exception'||SQLERRM,3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Approval Work Flow Initiated',3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Approval Work Flow Success',3);
pvt_debug(g_pkg_name||'UPDATE_LEASE:Tenancies are needed to make the lease Final',3);
update_contacts (p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validate => p_validate
, p_lease_contacts_tbl => p_lease_contacts_tbl
, p_lease_id => p_lease_rec.lease_id
, p_lease_change_id => l_leasechangeid
, p_lease_context => l_lease_context
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
pvt_debug(g_pkg_name||'UPDATE_LEASE: UPDATE_CONTACTS Return Status'||x_return_status,3);
update_tenancies (p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validate => p_validate
, p_lease_tenancies_tbl => p_lease_tenancies_tbl
, p_lease_id => p_lease_rec.lease_id
, p_lease_change_id => l_leasechangeid
, p_lease_context => l_lease_context
, x_return_status => x_return_status
-- , x_msg_count => x_msg_count
-- , x_msg_data => x_msg_data
);
pvt_debug(g_pkg_name||'UPDATE_LEASE: UPDATE_TENANCIES Return Status:'||x_return_status,3);
update_terms (p_api_version => p_api_version
, p_init_msg_list => NULL
, p_commit => NULL
, p_validate => p_validate
, p_payment_terms_tbl => p_lease_payment_term_tbl
, p_lease_id => p_lease_rec.lease_id
, p_lease_context => l_lease_context
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
pvt_debug(g_pkg_name||'UPDATE_LEASE: UPDATE_terms Return Status:'||x_return_status,3);
UPDATE_NOTE (
p_lease_id => p_lease_rec.lease_id
, p_commit => p_commit
, p_validate => p_validate
, p_note_tbl => p_note_tbl
, x_return_status => x_return_status
-- , x_msg_count => x_msg_count
-- , x_msg_data => x_msg_data
);
pvt_debug(g_pkg_name||'UPDATE_LEASE: UPDATE_NOTE Return Status'||x_return_status,3);
UPDATE_RIGHT (
p_lease_id => nvl(p_lease_rec.lease_id,l_lease_hdr_rec.lease_id)
, p_lease_change_id => nvl(p_lease_rec.lease_change_id,l_lease_dtl_rec.lease_change_id)
, p_commit => p_commit
, p_validate => p_validate
, p_right_tbl => p_right_tbl
, x_return_status => x_return_status
-- , x_msg_count => x_msg_count
-- , x_msg_data => x_msg_data
);
pvt_debug(g_pkg_name||'UPDATE_LEASE: UPDATE_RIGHT Return Status'||x_return_status,3);
UPDATE_OBLIGATION (
p_lease_id => nvl(p_lease_rec.lease_id,l_lease_hdr_rec.lease_id)
, p_lease_change_id => nvl(p_lease_rec.lease_change_id,l_lease_dtl_rec.lease_change_id)
, p_commit => p_commit
, p_validate => p_validate
, p_obligation_tbl => p_obligation_tbl
, x_return_status => x_return_status
-- , x_msg_count => x_msg_count
-- , x_msg_data => x_msg_data
);
pvt_debug(g_pkg_name||'UPDATE_LEASE: UPDATE_OBLIGATION Return Status'||x_return_status,3);
UPDATE_INSURANCE (
p_lease_id => nvl(p_lease_rec.lease_id,l_lease_hdr_rec.lease_id),
p_lease_change_id => nvl(p_lease_rec.lease_change_id,l_lease_dtl_rec.lease_change_id),
p_commit => p_commit,
p_validate => p_validate,
p_insurance_tbl => p_insurance_tbl,
x_return_status => x_return_status
-- x_msg_count => x_msg_count,
-- x_msg_data => x_msg_data
);
pvt_debug(g_pkg_name||'UPDATE_LEASE: UPDATE_INSURANCE Return Status:'||x_return_status,3);
UPDATE_OPTION (
p_lease_id => nvl(p_lease_rec.lease_id,l_lease_hdr_rec.lease_id)
, p_lease_change_id => nvl(p_lease_rec.lease_change_id,l_lease_dtl_rec.lease_change_id)
, p_commit => p_commit
, p_validate => p_validate
, p_option_tbl => p_option_tbl
, x_return_status => x_return_status
-- , x_msg_count => x_msg_count
-- , x_msg_data => x_msg_data
);
pvt_debug(g_pkg_name||'UPDATE_LEASE: UPDATE_OPTION Return Status:'||x_return_status,3);
pvt_debug(g_pkg_name||'-UPDATE_LEASE:Exception:'||SQLERRM,3);
END update_lease;
SELECT rowid row_id,rownum row_num,pda.*
FROM pn_distributions_all pda
WHERE pda.payment_term_id = l_term_id
AND pda.account_class = l_acc_code
AND pda.org_id = g_org_id
AND rownum = 1;
SELECT max(rownum)
INTO l_max_rownum
FROM pn_distributions_all pda
WHERE pda.payment_term_id = l_term_id;
SELECT count(1)
INTO l_record_count
FROM pn_distributions_all
WHERE payment_term_id = l_term_id
AND account_class = l_acc_code
AND org_id = g_org_id;
pn_distributions_pkg.update_row( x_rowid => r_dist_details.row_id
, x_distribution_id => r_dist_details.distribution_id
, x_account_id => NVL(p_terms_exp_rev_accounts_tbl(i).account_id,r_dist_details.account_id)
,x_payment_term_id => r_dist_details.payment_term_id
,x_term_template_id => r_dist_details.term_template_id
,x_account_class => p_terms_exp_rev_accounts_tbl(i).account_code
,x_percentage => p_terms_exp_rev_accounts_tbl(i).account_percentage
,x_line_number => r_dist_details.line_number
,x_last_update_date => SYSDATE
,x_last_updated_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id
,x_attribute_category => r_dist_details.attribute_category
,x_attribute1 => r_dist_details.attribute1
,x_attribute2 => r_dist_details.attribute2
,x_attribute3 => r_dist_details.attribute3
,x_attribute4 => r_dist_details.attribute4
,x_attribute5 => r_dist_details.attribute5
,x_attribute6 => r_dist_details.attribute6
,x_attribute7 => r_dist_details.attribute7
,x_attribute8 => r_dist_details.attribute8
,x_attribute9 => r_dist_details.attribute9
,x_attribute10 => r_dist_details.attribute10
,x_attribute11 => r_dist_details.attribute11
,x_attribute12 => r_dist_details.attribute12
,x_attribute13 => r_dist_details.attribute13
,x_attribute14 => r_dist_details.attribute14
,x_attribute15 => r_dist_details.attribute15
,x_lease_change_id => p_lease_terms_rec.lease_change_id
);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:PN_DISTRIBUTIONS_PKG.UPDATE_ROW Successful',3);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:PN_DISTRIBUTIONS_PKG.UPDATE_ROW:Exception:'||SQLERRM,3);
pn_distributions_pkg.insert_row (x_rowid => x_rowid
,x_distribution_id => x_distribution_id--r_dist_details.distribution_id
,x_account_id => NVL(p_terms_exp_rev_accounts_tbl(i).account_id,r_dist_details.account_id)
,x_payment_term_id => r_dist_details.payment_term_id
,x_term_template_id => r_dist_details.term_template_id
,x_account_class => p_terms_exp_rev_accounts_tbl(i).account_code
,x_percentage => p_terms_exp_rev_accounts_tbl(i).account_percentage
,x_line_number => l_max_rownum
,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_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_org_id => fnd_global.org_id
);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:PN_DISTRIBUTIONS_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:PN_DISTRIBUTIONS_PKG.INSERT_ROW:Exception:'||SQLERRM,3);
pn_distributions_pkg.insert_row (x_rowid => x_rowid
,x_distribution_id => x_distribution_id--r_dist_details.distribution_id
,x_account_id => p_terms_exp_rev_accounts_tbl(i).account_id
,x_payment_term_id => p_lease_terms_rec.payment_term_id
,x_term_template_id => p_lease_terms_rec.term_template_id
,x_account_class => p_terms_exp_rev_accounts_tbl(i).account_code
,x_percentage => p_terms_exp_rev_accounts_tbl(i).account_percentage
,x_line_number => l_max_rownum
,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_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_org_id => fnd_global.org_id
);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:PN_DISTRIBUTIONS_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:PN_DISTRIBUTIONS_PKG.INSERT_ROW:Exception:'||SQLERRM,3);
SELECT term_template_id
, NAME
, set_of_books_id
, currency_code
, normalize
, schedule_day
, payment_purpose_code
, payment_term_type_code
, accrual_account_id
, project_id
, task_id
, organization_id
, expenditure_type
, expenditure_item_date
, vendor_id
, vendor_site_id
, customer_id
, customer_site_use_id
, cust_ship_site_id
, ap_ar_term_id
, cust_trx_type_id
, tax_group_id
, tax_code_id
, distribution_set_id
, inv_rule_id
, account_rule_id
, salesrep_id
, tax_included
, po_header_id
, cust_po_number
, receipt_method_id
, active
, description
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, org_id
, location_id
, term_template_type
, tax_classification_code
, include_in_var_rent
FROM pn_term_templates_all
WHERE term_template_id = p_term_temp_id
AND active = 'Y';
PROCEDURE insert_payment_terms_row (
p_lease_terms_rec IN OUT NOCOPY lease_terms_rec
, p_lease_id IN NUMBER
, p_change_lease_id IN NUMBER
, p_lease_context IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
)
IS
x_rowid VARCHAR2(18);
pvt_debug(g_pkg_name||'INSERT_PAYMENT_TERMS_ROW:Calling PNT_PAYMENT_TERMS_PKG.INSERT_ROW',3);
pnt_payment_terms_pkg.insert_row( x_rowid => x_rowid
, x_payment_term_id => p_lease_terms_rec.payment_term_id
, x_payment_purpose_code => p_lease_terms_rec.payment_purpose_code
, x_payment_term_type_code => p_lease_terms_rec.payment_term_type_code
, x_frequency_code => p_lease_terms_rec.frequency_code
, x_lease_id => p_lease_id--p_lease_terms_rec.lease_id
, x_lease_change_id => p_change_lease_id--p_lease_terms_rec.lease_change_id
, x_start_date => trunc(p_lease_terms_rec.start_date)
, x_end_date => trunc(p_lease_terms_rec.end_date)
, x_vendor_id => p_lease_terms_rec.vendor_id
, x_vendor_site_id => p_lease_terms_rec.vendor_site_id
, x_customer_id => p_lease_terms_rec.customer_id
, x_customer_site_use_id => p_lease_terms_rec.customer_site_use_id
, x_target_date => trunc(p_lease_terms_rec.target_date)
, x_actual_amount => p_lease_terms_rec.actual_amount
, x_estimated_amount => p_lease_terms_rec.estimated_amount
, x_set_of_books_id => pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',g_org_id)--p_lease_terms_rec.set_of_books_id
, x_currency_code => p_lease_terms_rec.currency_code
, x_rate => p_lease_terms_rec.rate
, x_normalize => p_lease_terms_rec.normalize
, x_location_id => p_lease_terms_rec.location_id
, x_schedule_day => p_lease_terms_rec.schedule_day
, x_cust_ship_site_id => p_lease_terms_rec.cust_ship_site_id
, x_ap_ar_term_id => p_lease_terms_rec.ap_ar_term_id
, x_cust_trx_type_id => p_lease_terms_rec.cust_trx_type_id
, x_project_id => p_lease_terms_rec.project_id
, x_task_id => p_lease_terms_rec.task_id
, x_organization_id => p_lease_terms_rec.organization_id
, x_expenditure_type => p_lease_terms_rec.expenditure_type
, x_expenditure_item_date => trunc(p_lease_terms_rec.expenditure_item_date)
, x_tax_group_id => p_lease_terms_rec.tax_group_id
, x_tax_code_id => p_lease_terms_rec.tax_code_id
, x_tax_classification_code => p_lease_terms_rec.tax_classification_code
, x_tax_included => p_lease_terms_rec.tax_included
, x_distribution_set_id => p_lease_terms_rec.distribution_set_id
, x_inv_rule_id => p_lease_terms_rec.inv_rule_id
, x_account_rule_id => p_lease_terms_rec.account_rule_id
, x_salesrep_id => p_lease_terms_rec.salesrep_id
, x_approved_by => p_lease_terms_rec.approved_by
, x_status => p_lease_terms_rec.status
, x_index_period_id => p_lease_terms_rec.index_period_id
, x_index_term_indicator => p_lease_terms_rec.index_term_indicator
, x_po_header_id => p_lease_terms_rec.po_header_id
, x_cust_po_number => p_lease_terms_rec.cust_po_number
, x_receipt_method_id => p_lease_terms_rec.receipt_method_id
, x_var_rent_inv_id => p_lease_terms_rec.var_rent_inv_id
, x_var_rent_type => p_lease_terms_rec.var_rent_type
, x_period_billrec_id => p_lease_terms_rec.period_billrec_id
, x_rec_agr_line_id => p_lease_terms_rec.rec_agr_line_id
, x_amount_type => p_lease_terms_rec.amount_type
, x_changed_flag => p_lease_terms_rec.changed_flag
, x_term_template_id => p_lease_terms_rec.term_template_id
, x_attribute_category => p_lease_terms_rec.attribute_category
, x_attribute1 => p_lease_terms_rec.attribute1
, x_attribute2 => p_lease_terms_rec.attribute2
, x_attribute3 => p_lease_terms_rec.attribute3
, x_attribute4 => p_lease_terms_rec.attribute4
, x_attribute5 => p_lease_terms_rec.attribute5
, x_attribute6 => p_lease_terms_rec.attribute6
, x_attribute7 => p_lease_terms_rec.attribute7
, x_attribute8 => p_lease_terms_rec.attribute8
, x_attribute9 => p_lease_terms_rec.attribute9
, x_attribute10 => p_lease_terms_rec.attribute10
, x_attribute11 => p_lease_terms_rec.attribute11
, x_attribute12 => p_lease_terms_rec.attribute12
, x_attribute13 => p_lease_terms_rec.attribute13
, x_attribute14 => p_lease_terms_rec.attribute14
, x_attribute15 => p_lease_terms_rec.attribute15
, x_project_attribute_category => p_lease_terms_rec.project_attribute_category
, x_project_attribute1 => p_lease_terms_rec.attribute1
, x_project_attribute2 => p_lease_terms_rec.attribute2
, x_project_attribute3 => p_lease_terms_rec.attribute3
, x_project_attribute4 => p_lease_terms_rec.attribute4
, x_project_attribute5 => p_lease_terms_rec.attribute5
, x_project_attribute6 => p_lease_terms_rec.attribute6
, x_project_attribute7 => p_lease_terms_rec.attribute7
, x_project_attribute8 => p_lease_terms_rec.attribute8
, x_project_attribute9 => p_lease_terms_rec.attribute9
, x_project_attribute10 => p_lease_terms_rec.attribute10
, x_project_attribute11 => p_lease_terms_rec.attribute11
, x_project_attribute12 => p_lease_terms_rec.attribute12
, x_project_attribute13 => p_lease_terms_rec.attribute13
, x_project_attribute14 => p_lease_terms_rec.attribute14
, x_project_attribute15 => p_lease_terms_rec.attribute15
, x_creation_date => SYSDATE--p_lease_terms_rec.creation_date
, x_created_by => fnd_global.user_id--p_lease_terms_rec.
, x_last_update_date => SYSDATE --p_lease_terms_rec.
, x_last_updated_by => fnd_global.user_id--p_lease_terms_rec.
, x_last_update_login => fnd_global.login_id--p_lease_terms_rec.
, x_calling_form => NULL
, x_org_id => g_org_id--p_lease_terms_rec.org_id--OOPS
, x_lease_status => p_lease_terms_rec.lease_status
, x_recoverable_flag => p_lease_terms_rec.recoverable_flag
, x_area_type_code => p_lease_terms_rec.area_type_code
, x_area => p_lease_terms_rec.area
, x_grouping_rule_id => p_lease_terms_rec.grouping_rule_id
, x_term_altered_flag => p_lease_terms_rec.term_altered_flag
, x_source_code => p_lease_terms_rec.source_code
, x_term_comments => p_lease_terms_rec.term_comments
, x_norm_start_date => p_lease_terms_rec.norm_start_date
, x_parent_term_id => p_lease_terms_rec.parent_term_id
, x_index_norm_flag => p_lease_terms_rec.index_norm_flag
, x_include_in_var_rent => p_lease_terms_rec.include_in_var_rent
, x_recur_bb_calc_date => p_lease_terms_rec.recur_bb_calc_date
, x_opex_agr_id => p_lease_terms_rec.opex_agr_id
, x_opex_recon_id => p_lease_terms_rec.opex_recon_id
, x_opex_type => p_lease_terms_rec.opex_type
);
pvt_debug(g_pkg_name||'INSERT_PAYMENT_TERMS_ROW:Calling PNT_PAYMENT_TERMS_PKG.INSERT_ROW Succesful',3);
pvt_debug(g_pkg_name||'INSERT_PAYMENT_TERMS_ROW:Exception:'||SQLERRM,3);
END insert_payment_terms_row;
PROCEDURE update_payment_terms_row (
p_lease_terms_rec IN OUT NOCOPY lease_terms_rec
, p_lease_id IN NUMBER
, p_change_lease_id IN NUMBER
, p_lease_context IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
----------------------dbms_output.put_line('IN UPDATE : p_lease_terms_rec.payment_term_id'||p_lease_terms_rec.payment_term_id);--Rupak
pnt_payment_terms_pkg.update_row (x_payment_term_id => p_lease_terms_rec.payment_term_id
, x_payment_purpose_code => p_lease_terms_rec.payment_purpose_code
, x_payment_term_type_code => p_lease_terms_rec.payment_term_type_code
, x_frequency_code => p_lease_terms_rec.frequency_code
, x_lease_id => p_lease_terms_rec.lease_id
, x_lease_change_id => p_lease_terms_rec.lease_change_id
, x_start_date => trunc(p_lease_terms_rec.start_date)
, x_end_date => trunc(p_lease_terms_rec.end_date)
, x_vendor_id => p_lease_terms_rec.vendor_id
, x_vendor_site_id => p_lease_terms_rec.vendor_site_id
, x_customer_id => p_lease_terms_rec.customer_id
, x_customer_site_use_id => p_lease_terms_rec.customer_site_use_id
, x_target_date => trunc(p_lease_terms_rec.target_date)
, x_actual_amount => p_lease_terms_rec.actual_amount
, x_estimated_amount => p_lease_terms_rec.estimated_amount
, x_set_of_books_id => p_lease_terms_rec.set_of_books_id
, x_currency_code => p_lease_terms_rec.currency_code
, x_rate => p_lease_terms_rec.rate
, x_normalize => p_lease_terms_rec.normalize
, x_location_id => p_lease_terms_rec.location_id
, x_schedule_day => p_lease_terms_rec.schedule_day
, x_cust_ship_site_id => p_lease_terms_rec.cust_ship_site_id
, x_ap_ar_term_id => p_lease_terms_rec.ap_ar_term_id
, x_cust_trx_type_id => p_lease_terms_rec.cust_trx_type_id
, x_project_id => p_lease_terms_rec.project_id
, x_task_id => p_lease_terms_rec.task_id
, x_organization_id => p_lease_terms_rec.organization_id
, x_expenditure_type => p_lease_terms_rec.expenditure_type
, x_expenditure_item_date => trunc(p_lease_terms_rec.expenditure_item_date)
, x_tax_group_id => p_lease_terms_rec.tax_group_id
, x_tax_code_id => p_lease_terms_rec.tax_code_id
, x_tax_classification_code => p_lease_terms_rec.tax_classification_code
, x_tax_included => p_lease_terms_rec.tax_included
, x_distribution_set_id => p_lease_terms_rec.distribution_set_id
, x_inv_rule_id => p_lease_terms_rec.inv_rule_id
, x_account_rule_id => p_lease_terms_rec.account_rule_id
, x_salesrep_id => p_lease_terms_rec.salesrep_id
, x_approved_by => p_lease_terms_rec.approved_by
, x_status => p_lease_terms_rec.status
, x_index_period_id => p_lease_terms_rec.index_period_id
, x_index_term_indicator => p_lease_terms_rec.index_term_indicator
, x_po_header_id => p_lease_terms_rec.po_header_id
, x_cust_po_number => p_lease_terms_rec.cust_po_number
, x_receipt_method_id => p_lease_terms_rec.receipt_method_id
, x_var_rent_inv_id => p_lease_terms_rec.var_rent_inv_id
, x_var_rent_type => p_lease_terms_rec.var_rent_type
, x_changed_flag => p_lease_terms_rec.changed_flag
, x_attribute_category => p_lease_terms_rec.attribute_category
, x_attribute1 => p_lease_terms_rec.attribute1
, x_attribute2 => p_lease_terms_rec.attribute2
, x_attribute3 => p_lease_terms_rec.attribute3
, x_attribute4 => p_lease_terms_rec.attribute4
, x_attribute5 => p_lease_terms_rec.attribute5
, x_attribute6 => p_lease_terms_rec.attribute6
, x_attribute7 => p_lease_terms_rec.attribute7
, x_attribute8 => p_lease_terms_rec.attribute8
, x_attribute9 => p_lease_terms_rec.attribute9
, x_attribute10 => p_lease_terms_rec.attribute10
, x_attribute11 => p_lease_terms_rec.attribute11
, x_attribute12 => p_lease_terms_rec.attribute12
, x_attribute13 => p_lease_terms_rec.attribute13
, x_attribute14 => p_lease_terms_rec.attribute14
, x_attribute15 => p_lease_terms_rec.attribute15
, x_project_attribute_category => p_lease_terms_rec.project_attribute_category
, x_project_attribute1 => p_lease_terms_rec.project_attribute1
, x_project_attribute2 => p_lease_terms_rec.project_attribute2
, x_project_attribute3 => p_lease_terms_rec.project_attribute3
, x_project_attribute4 => p_lease_terms_rec.project_attribute4
, x_project_attribute5 => p_lease_terms_rec.project_attribute5
, x_project_attribute6 => p_lease_terms_rec.project_attribute6
, x_project_attribute7 => p_lease_terms_rec.project_attribute7
, x_project_attribute8 => p_lease_terms_rec.project_attribute8
, x_project_attribute9 => p_lease_terms_rec.project_attribute9
, x_project_attribute10 => p_lease_terms_rec.project_attribute10
, x_project_attribute11 => p_lease_terms_rec.project_attribute11
, x_project_attribute12 => p_lease_terms_rec.project_attribute12
, x_project_attribute13 => p_lease_terms_rec.project_attribute13
, x_project_attribute14 => p_lease_terms_rec.project_attribute14
, x_project_attribute15 => p_lease_terms_rec.project_attribute15
, x_last_update_date => SYSDATE
, x_last_updated_by => p_lease_terms_rec.last_updated_by
, x_last_update_login => p_lease_terms_rec.last_update_login
, x_recoverable_flag => p_lease_terms_rec.recoverable_flag
, x_area_type_code => p_lease_terms_rec.area_type_code
, x_area => p_lease_terms_rec.area
, x_grouping_rule_id => p_lease_terms_rec.grouping_rule_id
, x_term_altered_flag => p_lease_terms_rec.term_altered_flag
, x_source_code => p_lease_terms_rec.source_code
, x_term_comments => p_lease_terms_rec.term_comments
, x_include_in_var_rent => p_lease_terms_rec.include_in_var_rent
, x_opex_agr_id => p_lease_terms_rec.opex_agr_id
, x_opex_recon_id => p_lease_terms_rec.opex_recon_id
, x_opex_type => p_lease_terms_rec.opex_type
, x_term_template_id => p_lease_terms_rec.term_template_id
);
pvt_debug(g_pkg_name||'UPDATE_PAYMENT_TERMS_ROW:PNT_PAYMENT_TERMS_PKG.UPDATE_ROW Sucessful',3);
fnd_message.set_token ('P_TEXT', 'pn_lease_pvt.update_payment' || SQLERRM);
pvt_debug(g_pkg_name||'UPDATE_PAYMENT_TERMS_ROW:PNT_PAYMENT_TERMS_PKG.UPDATE_ROW:Exception'||SQLERRM,3);
END update_payment_terms_row;
SELECT rowid row_id,rownum row_num,pda.*
FROM pn_distributions_all pda
WHERE pda.payment_term_id = l_term_id
AND pda.account_class = l_acc_code
AND pda.org_id = g_org_id
AND rownum = 1;
select account_id,percentage
from pn_distributions_all
where term_template_id = l_term_template_id
and account_class = l_account_class
and payment_term_id is null; --Bug 16423198
SELECT payment_term_type_code
, ap_ar_term_id
, cust_trx_type_id
, payment_purpose_code
, schedule_day
, currency_code
, vendor_id
, vendor_site_id
, customer_id
, customer_site_use_id
INTO l_payment_term_type_code
,l_p_ar_term_id
,l_cust_trx_type_id
,l_payment_purpose_code
,l_schedule_day
,l_currency_code
,l_vendor_id
,l_vendor_site_id
,l_customer_id
,l_customer_site_use_id
FROM pn_term_templates_all
WHERE term_template_id = p_payment_terms_tbl(i).term_template_id;
SELECT COUNT(1)
INTO l_count_template
FROM pn_payment_terms_all
WHERE payment_term_id = p_payment_terms_tbl(i).payment_term_id
AND term_template_id = p_payment_terms_tbl(i).term_template_id
AND org_id = g_org_id;
fnd_message.set_token('ERR_MSG', 'Template Name/ID is non-updatable in Update Mode');
SELECT pld.expense_account_id,pld.receivable_account_id,pld.accrual_account_id
INTO l_expense_account_id,l_receivable_account_id,l_accrual_account_id
FROM pn_leases_all pla,pn_lease_details pld
WHERE pla.lease_id = pld.lease_id
AND pla.lease_id = p_lease_id;
SELECT chart_of_accounts_id
INTO p_payment_terms_tbl(i).set_of_books_id
FROM gl_sets_of_books
WHERE set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
IF(trim(p_operation) = 'UPDATE_TERMS') THEN
BEGIN
SELECT project_id
, task_id
, expenditure_type
, expenditure_item_date
, organization_id
INTO l_term_project_id--p_payment_terms_tbl(i).project_id
, l_term_task_id--p_payment_terms_tbl(i).task_id
, l_term_expenditure_type--p_payment_terms_tbl(i).expenditure_type
, l_term_expenditure_item_date--p_payment_terms_tbl(i).expenditure_item_date
, l_term_organization_id--p_payment_terms_tbl(i).organization_id
FROM pn_payment_terms_all
WHERE payment_term_id = p_payment_terms_tbl(i).payment_term_id
AND org_id = g_org_id;
SELECT 'x'
INTO l_check
FROM pa_projects_all p,
pa_project_types_all pt
WHERE p.project_type = pt.project_type
AND NVL(p.template_flag,'N') <>'Y'
AND pt.org_id = g_org_id
AND project_id = p_payment_terms_tbl(i).project_id;
SELECT 'x'
INTO l_check
FROM pa_tasks_expend_v
WHERE project_id = p_payment_terms_tbl(i).project_id
AND task_id = p_payment_terms_tbl(i).task_id
AND org_id = g_org_id;
SELECT 'x'
INTO l_check
FROM pa_expenditure_types_expend_v
WHERE system_linkage_function='VI'
AND (sysdate between expnd_typ_start_date_active and nvl(expnd_typ_end_date_active, sysdate))
AND (sysdate between sys_link_start_date_active and nvl(sys_link_end_date_active,sysdate))
AND trim(expenditure_type) = trim(p_payment_terms_tbl(i).expenditure_type);
SELECT p_payment_terms_tbl(i).expenditure_item_date
INTO l_check_date
FROM dual;
SELECT 'x'
INTO l_check
FROM pa_organizations_expend_v a
WHERE active_flag='Y'
AND org_id = g_org_id
AND p_payment_terms_tbl(i).expenditure_item_date between date_from and nvl(date_to,pn_lease_utils.g_pn_miss_date)
AND organization_id = p_payment_terms_tbl(i).organization_id;
SELECT account_id,percentage
INTO p_payment_terms_tbl(i).p_terms_exp_rev_accounts_tbl(1).account_id,p_payment_terms_tbl(i).p_terms_exp_rev_accounts_tbl(1).account_percentage
FROM pn_distributions_all
WHERE term_template_id = p_payment_terms_tbl(i).term_template_id
AND account_class = p_payment_terms_tbl(i).p_terms_exp_rev_accounts_tbl(1).account_code
AND payment_term_id IS NULL;
SELECT account_id,percentage
INTO p_payment_terms_tbl(i).p_terms_liab_rcvl_account_tbl(1).account_id,p_payment_terms_tbl(i).p_terms_liab_rcvl_account_tbl(1).account_percentage
FROM pn_distributions_all
WHERE term_template_id = p_payment_terms_tbl(i).term_template_id
AND account_class = p_payment_terms_tbl(i).p_terms_liab_rcvl_account_tbl(1).account_code
AND payment_term_id IS NULL;
SELECT account_id,percentage
INTO p_payment_terms_tbl(i).p_terms_accrual_account_tbl(1).account_id,p_payment_terms_tbl(i).p_terms_accrual_account_tbl(1).account_percentage
FROM pn_distributions_all
WHERE term_template_id = p_payment_terms_tbl(i).term_template_id
AND account_class = p_payment_terms_tbl(i).p_terms_accrual_account_tbl(1).account_code
AND payment_term_id IS NULL;
pvt_debug(g_pkg_name||'-VALIDATE_PAYMENT_TERMS_TBL:Before Account Insert Account Check',3);
insert_payment_terms_row (p_lease_terms_rec => p_payment_terms_tbl(i)
, p_lease_id => p_lease_id
, p_change_lease_id => p_lease_change_id
, p_lease_context => p_lease_context
, x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-VALIDATE_PAYMENT_TERMS_TBL:INSERT_PAYMENT_TERMS_ROW Return Status:'||x_return_status,3);
pn_distributions_pkg.insert_row (x_rowid => x_rowid
,x_distribution_id => x_distribution_id--r_dist_details.distribution_id
,x_account_id => p_payment_terms_tbl(i).p_terms_exp_rev_accounts_tbl(m).account_id
,x_payment_term_id => p_payment_terms_tbl(i).payment_term_id
,x_term_template_id => NULL--p_payment_terms_tbl(i).term_template_id
,x_account_class => p_payment_terms_tbl(i).p_terms_exp_rev_accounts_tbl(m).account_code
,x_percentage => p_payment_terms_tbl(i).p_terms_exp_rev_accounts_tbl(m).account_percentage
,x_line_number => l_max_rownum
,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_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_org_id => g_org_id
);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:PN_DISTRIBUTIONS_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:PN_DISTRIBUTIONS_PKG.INSERT_ROW:Exception:'||SQLERRM,3);
pn_distributions_pkg.insert_row (x_rowid => x_rowid
,x_distribution_id => x_distribution_id--r_dist_details.distribution_id
,x_account_id => p_payment_terms_tbl(i).p_terms_liab_rcvl_account_tbl(m).account_id
,x_payment_term_id => p_payment_terms_tbl(i).payment_term_id
,x_term_template_id => NULL--p_payment_terms_tbl(i).term_template_id
,x_account_class => p_payment_terms_tbl(i).p_terms_liab_rcvl_account_tbl(m).account_code
,x_percentage => p_payment_terms_tbl(i).p_terms_liab_rcvl_account_tbl(m).account_percentage
,x_line_number => l_max_rownum
,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_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_org_id => g_org_id
);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:PN_DISTRIBUTIONS_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'p_terms_liab_rcvl_account_tbl:PN_DISTRIBUTIONS_PKG.INSERT_ROW:Exception:'||SQLERRM,3);
pn_distributions_pkg.insert_row (x_rowid => x_rowid
,x_distribution_id => x_distribution_id--r_dist_details.distribution_id
,x_account_id => p_payment_terms_tbl(i).p_terms_accrual_account_tbl(m).account_id
,x_payment_term_id => p_payment_terms_tbl(i).payment_term_id
,x_term_template_id => NULL--p_payment_terms_tbl(i).term_template_id
,x_account_class => p_payment_terms_tbl(i).p_terms_accrual_account_tbl(m).account_code
,x_percentage => p_payment_terms_tbl(i).p_terms_accrual_account_tbl(m).account_percentage
,x_line_number => l_max_rownum
,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_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_org_id => g_org_id
);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:PN_DISTRIBUTIONS_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'p_terms_accrual_account_tbl:PN_DISTRIBUTIONS_PKG.INSERT_ROW:Exception:'||SQLERRM,3);
ELSIF(trim(p_operation) = trim('UPDATE_TERMS')) THEN --UPDATE Version of the Check Starts
pvt_debug(g_pkg_name||'-VALIDATE_PAYMENT_TERMS_TBL:Before Account Update Account Check',3);
SELECT max(rownum)
INTO l_max_rownum
FROM pn_distributions_all pda
WHERE pda.payment_term_id = p_payment_terms_tbl(i).payment_term_id
AND pda.org_id = g_org_id;
SELECT count(1)
INTO l_record_count_exp_rev
FROM pn_distributions_all
WHERE payment_term_id = p_payment_terms_tbl(i).payment_term_id
AND account_class = l_acc_code
AND org_id = g_org_id;
pn_distributions_pkg.update_row( x_rowid => r_dist_details.row_id
, x_distribution_id => r_dist_details.distribution_id
, x_account_id => NVL(p_payment_terms_tbl(i).p_terms_exp_rev_accounts_tbl(p).account_id,r_dist_details.account_id)
,x_payment_term_id => r_dist_details.payment_term_id
,x_term_template_id => NULL--r_dist_details.term_template_id
,x_account_class => l_acc_code
,x_percentage => p_payment_terms_tbl(i).p_terms_exp_rev_accounts_tbl(p).account_percentage
,x_line_number => r_dist_details.line_number
,x_last_update_date => SYSDATE
,x_last_updated_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id
,x_attribute_category => r_dist_details.attribute_category
,x_attribute1 => r_dist_details.attribute1
,x_attribute2 => r_dist_details.attribute2
,x_attribute3 => r_dist_details.attribute3
,x_attribute4 => r_dist_details.attribute4
,x_attribute5 => r_dist_details.attribute5
,x_attribute6 => r_dist_details.attribute6
,x_attribute7 => r_dist_details.attribute7
,x_attribute8 => r_dist_details.attribute8
,x_attribute9 => r_dist_details.attribute9
,x_attribute10 => r_dist_details.attribute10
,x_attribute11 => r_dist_details.attribute11
,x_attribute12 => r_dist_details.attribute12
,x_attribute13 => r_dist_details.attribute13
,x_attribute14 => r_dist_details.attribute14
,x_attribute15 => r_dist_details.attribute15
,x_lease_change_id => g_lease_change_id
);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:PN_DISTRIBUTIONS_PKG.UPDATE_ROW Successful',3);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:PN_DISTRIBUTIONS_PKG.UPDATE_ROW:Exception:'||SQLERRM,3);
pn_distributions_pkg.insert_row (x_rowid => x_rowid
,x_distribution_id => x_distribution_id--r_dist_details.distribution_id
,x_account_id => NVL(p_payment_terms_tbl(i).p_terms_exp_rev_accounts_tbl(p).account_id,r_dist_details.account_id)
,x_payment_term_id => r_dist_details.payment_term_id
,x_term_template_id => NULL--r_dist_details.term_template_id
,x_account_class => l_acc_code
,x_percentage => p_payment_terms_tbl(i).p_terms_exp_rev_accounts_tbl(p).account_percentage
,x_line_number => l_max_rownum
,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_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_org_id => g_org_id--fnd_global.org_id
);
pvt_debug(g_pkg_name||'PN_DISTRIBUTIONS_PKG.INSERT_ROW for Expense Revenue Successful',3);
pvt_debug(g_pkg_name||'PN_DISTRIBUTIONS_PKG.INSERT_ROW for Expense Revenue :Exception:'||SQLERRM,3);
SELECT COUNT(1)
INTO l_lia_exist_count
FROM pn_distributions_all
WHERE account_class = l_acc_code
AND payment_term_id = p_payment_terms_tbl(i).payment_term_id
AND org_id = g_org_id;
pn_distributions_pkg.update_row( x_rowid => r_dist_details.row_id
, x_distribution_id => r_dist_details.distribution_id
, x_account_id => NVL(p_payment_terms_tbl(i).p_terms_liab_rcvl_account_tbl(p).account_id,r_dist_details.account_id)
,x_payment_term_id => r_dist_details.payment_term_id
,x_term_template_id => NULL--r_dist_details.term_template_id
,x_account_class => l_acc_code
,x_percentage => p_payment_terms_tbl(i).p_terms_liab_rcvl_account_tbl(p).account_percentage
,x_line_number => r_dist_details.line_number
,x_last_update_date => SYSDATE
,x_last_updated_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id
,x_attribute_category => r_dist_details.attribute_category
,x_attribute1 => r_dist_details.attribute1
,x_attribute2 => r_dist_details.attribute2
,x_attribute3 => r_dist_details.attribute3
,x_attribute4 => r_dist_details.attribute4
,x_attribute5 => r_dist_details.attribute5
,x_attribute6 => r_dist_details.attribute6
,x_attribute7 => r_dist_details.attribute7
,x_attribute8 => r_dist_details.attribute8
,x_attribute9 => r_dist_details.attribute9
,x_attribute10 => r_dist_details.attribute10
,x_attribute11 => r_dist_details.attribute11
,x_attribute12 => r_dist_details.attribute12
,x_attribute13 => r_dist_details.attribute13
,x_attribute14 => r_dist_details.attribute14
,x_attribute15 => r_dist_details.attribute15
,x_lease_change_id => g_lease_change_id
);
pvt_debug(g_pkg_name||'PN_DISTRIBUTIONS_PKG.UPDATE_ROW for Liability and Receivable Successful',3);
pvt_debug(g_pkg_name||'PN_DISTRIBUTIONS_PKG.UPDATE_ROW for Liability and Receivable:Exception:'||SQLERRM,3);
DELETE FROM pn_distributions_all
WHERE account_class = l_acc_code
AND payment_term_id = p_payment_terms_tbl(i).payment_term_id
AND org_id = g_org_id;
pvt_debug(g_pkg_name||'PN_DISTRIBUTIONS_PKG.Liability and Receivable DELETE ROW',3);
pvt_debug(g_pkg_name||'PN_DISTRIBUTIONS_PKG.Liability and Receivable DELETE ROW Successful',3);
pvt_debug(g_pkg_name||'PN_DISTRIBUTIONS_PKG.Cannot delete the Liability and Receivable for Revenue/Sublease',3);
pn_distributions_pkg.insert_row (x_rowid => x_rowid
,x_distribution_id => x_distribution_id--r_dist_details.distribution_id
,x_account_id => p_payment_terms_tbl(i).p_terms_liab_rcvl_account_tbl(p).account_id
,x_payment_term_id => p_payment_terms_tbl(i).payment_term_id
,x_term_template_id => NULL--p_payment_terms_tbl(i).term_template_id
,x_account_class => p_payment_terms_tbl(i).p_terms_liab_rcvl_account_tbl(p).account_code
,x_percentage => p_payment_terms_tbl(i).p_terms_liab_rcvl_account_tbl(p).account_percentage
,x_line_number => l_max_rownum
,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_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_org_id => g_org_id
);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:UPDATE_TERMS:RECEIVABLES?LIABILITY:PN_DISTRIBUTIONS_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'p_terms_liab_rcvl_account_tbl:UPDATE_TERMS:RECEIVABLES?LIABILITY:PN_DISTRIBUTIONS_PKG.INSERT_ROW:Exception:'||SQLERRM,3);
SELECT COUNT(1)
INTO l_acc_exist_count
FROM pn_distributions_all
WHERE account_class = l_acc_code
AND payment_term_id = p_payment_terms_tbl(i).payment_term_id
AND org_id = g_org_id;
pn_distributions_pkg.update_row( x_rowid => r_dist_details.row_id
, x_distribution_id => r_dist_details.distribution_id
, x_account_id => NVL(p_payment_terms_tbl(i).p_terms_accrual_account_tbl(p).account_id,r_dist_details.account_id)
,x_payment_term_id => r_dist_details.payment_term_id
,x_term_template_id => NULL--r_dist_details.term_template_id
,x_account_class => l_acc_code
,x_percentage => p_payment_terms_tbl(i).p_terms_accrual_account_tbl(p).account_percentage
,x_line_number => r_dist_details.line_number
,x_last_update_date => SYSDATE
,x_last_updated_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id
,x_attribute_category => r_dist_details.attribute_category
,x_attribute1 => r_dist_details.attribute1
,x_attribute2 => r_dist_details.attribute2
,x_attribute3 => r_dist_details.attribute3
,x_attribute4 => r_dist_details.attribute4
,x_attribute5 => r_dist_details.attribute5
,x_attribute6 => r_dist_details.attribute6
,x_attribute7 => r_dist_details.attribute7
,x_attribute8 => r_dist_details.attribute8
,x_attribute9 => r_dist_details.attribute9
,x_attribute10 => r_dist_details.attribute10
,x_attribute11 => r_dist_details.attribute11
,x_attribute12 => r_dist_details.attribute12
,x_attribute13 => r_dist_details.attribute13
,x_attribute14 => r_dist_details.attribute14
,x_attribute15 => r_dist_details.attribute15
,x_lease_change_id => g_lease_change_id
);
pvt_debug(g_pkg_name||'PN_DISTRIBUTIONS_PKG.UPDATE_ROW for Accrual Successful',3);
pvt_debug(g_pkg_name||'PN_DISTRIBUTIONS_PKG.UPDATE_ROW for Accrual:Exception:'||SQLERRM,3);
DELETE FROM pn_distributions_all
WHERE account_class = l_acc_code
AND payment_term_id = p_payment_terms_tbl(i).payment_term_id
AND org_id = g_org_id;
pvt_debug(g_pkg_name||'PN_DISTRIBUTIONS_PKG.DELETE ROW for Accrual',3);
pvt_debug(g_pkg_name||'PN_DISTRIBUTIONS_PKG.DELETE ROW for Accrual Successful',3);
pn_distributions_pkg.insert_row (x_rowid => x_rowid
,x_distribution_id => x_distribution_id--r_dist_details.distribution_id
,x_account_id => p_payment_terms_tbl(i).p_terms_accrual_account_tbl(p).account_id
,x_payment_term_id => p_payment_terms_tbl(i).payment_term_id
,x_term_template_id => NULL--p_payment_terms_tbl(i).term_template_id
,x_account_class => p_payment_terms_tbl(i).p_terms_accrual_account_tbl(p).account_code
,x_percentage => p_payment_terms_tbl(i).p_terms_accrual_account_tbl(p).account_percentage
,x_line_number => l_max_rownum
,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_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_org_id => g_org_id
);
pvt_debug(g_pkg_name||'ADD_DIST_ACCOUNT:UPDATE_TERMS:PN_DISTRIBUTIONS_PKG.INSERT_ROW Successful',3);
pvt_debug(g_pkg_name||'p_terms_accrual_account_tbl:UPDATE_TERMS:PN_DISTRIBUTIONS_PKG.INSERT_ROW:Exception:'||SQLERRM,3);
END IF;--CREATE_TERMS-UPDATE_TERMS Check
PROCEDURE update_terms (
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_validate IN VARCHAR2 := FND_API.G_FALSE
, p_payment_terms_tbl IN OUT NOCOPY lease_terms_tbl
, p_lease_id IN NUMBER
, p_lease_context IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
AS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Payment';
SELECT *
FROM pn_payment_terms_all
WHERE payment_term_id = p_payment_term_id;
SELECT *
FROM pn_distributions_all
WHERE payment_term_id = p_payment_term_id;
p_payment_terms_tbl_update lease_terms_tbl;
pvt_debug(g_pkg_name||'-Inside UPDATE_TERMS:',3);
SAVEPOINT update_payment_pvt;
SELECT pla.lease_class_code
, pld.lease_commencement_date
, pld.lease_termination_date
, pla.parent_lease_id
, DECODE (pla.lease_class_code
, 'DIRECT', 'PAYMENT'
, 'THIRD_PARTY', 'BILLING'
)
, PN_LEASE_UTILS.get_lease_change_id(p_lease_id,g_org_id)
, pla.status
INTO l_lease_class_code
, l_lease_commencement_date
, l_lease_termination_date
, l_parent_lease_id
, l_termtemp_type
, l_lease_change_id
, l_lease_status
FROM pn_leases_all pla, pn_lease_details_all pld
WHERE pla.lease_id = p_lease_id
AND pla.lease_id = pld.lease_id
AND pld.org_id = pla.org_id
AND pla.org_id = g_org_id;
pvt_debug(g_pkg_name||'-update_terms:Assigning the term table values to other table',3);
p_payment_terms_tbl_update(q) := p_payment_terms_tbl(i);
IF(p_payment_terms_tbl_update.COUNT > 0 ) THEN
validate_payment_terms_tbl( p_api_name_full => l_api_name_full
, p_payment_terms_tbl => p_payment_terms_tbl_update
, p_lease_id => p_lease_id
, p_lease_class_code => l_lease_class_code
, p_lease_commencement_date => l_lease_commencement_date
, p_lease_termination_date => l_lease_termination_date
, p_termtemp_type => l_termtemp_type
, p_parent_lease_id => l_parent_lease_id
, p_lease_change_id => l_parent_lease_id
, p_lease_status => l_lease_status
, p_lease_context => p_lease_context
, p_operation => 'UPDATE_TERMS'
, x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'-UPDATE_TERMS:VALIDATE_PAYMENT_TERMS_TBL Return Status'||x_return_status,3);
pvt_debug(g_pkg_name||'-UPDATE_TERMS:validate_payment_terms_tbl:Exception:'||SQLERRM,3);
pvt_debug(g_pkg_name||'-UPDATE_TERMS:validate_payment_terms_tbl:Unexpected Exception:'||SQLERRM,3);
pvt_debug(g_pkg_name||'-UPDATE_TERMS:About to Update Payments',3);
l_terms_record_cnt := p_payment_terms_tbl_update.COUNT;
FOR i in p_payment_terms_tbl_update.FIRST..p_payment_terms_tbl_update.LAST
LOOP
--------dbms_output.put_line('123:HELLO');--Rupak
IF(p_payment_terms_tbl_update (i).payment_term_id IS NOT NULL OR p_payment_terms_tbl_update (i).payment_term_id <> pn_lease_utils.g_pn_miss_num) THEN
--------dbms_output.put_line('123:HELLO-1');--Rupak
IF (trim(p_payment_terms_tbl_update(i).error_flag) <> 'Y') THEN
BEGIN
l_assign_flag := 'N';
pvt_debug(g_pkg_name||'-UPDATE_TERMS:Inside IF Condition of the Payment Table'||p_payment_terms_tbl_update(i).payment_term_id,3);
FOR r_payment_terms in cur_payment_terms(p_payment_terms_tbl_update(i).payment_term_id)
LOOP
BEGIN
--------dbms_output.put_line('Inside The IF Y and Loop:2'||p_payment_terms_tbl_update(i).payment_term_id||l_assign_flag);--Rupak
IF ((r_payment_terms.payment_term_id = p_payment_terms_tbl_update(i).payment_term_id ) AND (l_assign_flag = 'N')) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:Deep Inside IF Condition of the Payment Table PaymentID:'||p_payment_terms_tbl_update(i).payment_term_id,3);
IF (p_payment_terms_tbl_update(i).payment_purpose_code IS NOT NULL AND p_payment_terms_tbl_update(i).payment_purpose_code <> r_payment_terms.payment_purpose_code) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:payment_purpose_code',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:payment_purpose_code was being updated');
IF(p_payment_terms_tbl_update(i).payment_term_type_code IS NOT NULL AND p_payment_terms_tbl_update(i).payment_term_type_code <> r_payment_terms.payment_term_type_code) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:payment_term_type_code',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:payment_term_type_code was being updated');
IF(p_payment_terms_tbl_update(i).frequency_code IS NOT NULL AND p_payment_terms_tbl_update(i).frequency_code <> r_payment_terms.frequency_code) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:frequency_code',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:frequency_code was being updated');
IF(p_payment_terms_tbl_update(i).lease_id IS NOT NULL AND p_payment_terms_tbl_update(i).lease_id <> r_payment_terms.lease_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:lease_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:lease_id was being updated');
IF(p_payment_terms_tbl_update(i).start_date IS NOT NULL AND p_payment_terms_tbl_update(i).start_date <> r_payment_terms.start_date) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:start_date',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:start_date was being updated');
IF(p_payment_terms_tbl_update(i).code_combination_id IS NOT NULL AND p_payment_terms_tbl_update(i).code_combination_id <> r_payment_terms.code_combination_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:code_combination_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:code_combination_id was being updated');
IF(p_payment_terms_tbl_update(i).vendor_site_id IS NOT NULL AND p_payment_terms_tbl_update(i).vendor_site_id <> r_payment_terms.vendor_site_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:vendor_site_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:vendor_site_id was being updated');
IF(p_payment_terms_tbl_update(i).target_date IS NOT NULL AND p_payment_terms_tbl_update(i).target_date <> r_payment_terms.target_date) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:target_date',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:target_date was being updated');
IF(p_payment_terms_tbl_update(i).actual_amount IS NOT NULL AND p_payment_terms_tbl_update(i).actual_amount <> r_payment_terms.actual_amount) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:actual_amount',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:actual_amount was being updated');
IF(p_payment_terms_tbl_update(i).estimated_amount IS NOT NULL AND p_payment_terms_tbl_update(i).estimated_amount <> r_payment_terms.estimated_amount) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:estimated_amount',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:estimated_amount was being updated');
IF(p_payment_terms_tbl_update(i).set_of_books_id IS NOT NULL AND p_payment_terms_tbl_update(i).set_of_books_id <> r_payment_terms.set_of_books_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:set_of_books_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:set_of_books_id was being updated');
IF(p_payment_terms_tbl_update(i).currency_code IS NOT NULL AND p_payment_terms_tbl_update(i).currency_code <> r_payment_terms.currency_code) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:currency_code',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:currency_code was being updated');
IF(p_payment_terms_tbl_update(i).rate IS NOT NULL AND p_payment_terms_tbl_update(i).rate <> r_payment_terms.rate) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:rate',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:rate was being updated');
IF(p_payment_terms_tbl_update(i).normalize IS NOT NULL AND p_payment_terms_tbl_update(i).normalize <> r_payment_terms.normalize) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:normalize',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:normalize was being updated');
IF(p_payment_terms_tbl_update(i).schedule_day IS NOT NULL AND p_payment_terms_tbl_update(i).schedule_day <> r_payment_terms.schedule_day) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:schedule_day',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:schedule_day was being updated');
IF(p_payment_terms_tbl_update(i).project_id IS NOT NULL AND p_payment_terms_tbl_update(i).project_id <> r_payment_terms.project_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:project_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:project_id was being updated');
IF(p_payment_terms_tbl_update(i).task_id IS NOT NULL AND p_payment_terms_tbl_update(i).task_id <> r_payment_terms.task_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:task_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:task_id was being updated');
IF(p_payment_terms_tbl_update(i).organization_id IS NOT NULL AND p_payment_terms_tbl_update(i).organization_id <> r_payment_terms.organization_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:organization_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:organization_id was being updated');
IF(p_payment_terms_tbl_update(i).expenditure_type IS NOT NULL AND p_payment_terms_tbl_update(i).expenditure_type <> r_payment_terms.expenditure_type) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:expenditure_type',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:expenditure_type was being updated');
IF(p_payment_terms_tbl_update(i).expenditure_item_date IS NOT NULL AND p_payment_terms_tbl_update(i).expenditure_item_date <> r_payment_terms.expenditure_item_date) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:expenditure_item_date',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:expenditure_item_date was being updated');
IF(p_payment_terms_tbl_update(i).tax_group_id IS NOT NULL AND p_payment_terms_tbl_update(i).tax_group_id <> r_payment_terms.tax_group_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:tax_group_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:tax_group_id was being updated');
IF(p_payment_terms_tbl_update(i).tax_code_id IS NOT NULL AND p_payment_terms_tbl_update(i).tax_code_id <> r_payment_terms.tax_code_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:tax_code_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:tax_code_id was being updated');
IF(p_payment_terms_tbl_update(i).tax_included IS NOT NULL AND p_payment_terms_tbl_update(i).tax_included <> r_payment_terms.tax_included) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:tax_included',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:tax_included was being updated');
IF(p_payment_terms_tbl_update(i).distribution_set_id IS NOT NULL AND p_payment_terms_tbl_update(i).distribution_set_id <> r_payment_terms.distribution_set_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:distribution_set_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:distribution_set_id was being updated');
IF(p_payment_terms_tbl_update(i).inv_rule_id IS NOT NULL AND p_payment_terms_tbl_update(i).inv_rule_id <> r_payment_terms.inv_rule_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:inv_rule_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:inv_rule_id was being updated');
IF(p_payment_terms_tbl_update(i).account_rule_id IS NOT NULL AND p_payment_terms_tbl_update(i).account_rule_id <> r_payment_terms.account_rule_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:account_rule_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:account_rule_id was being updated');
IF(p_payment_terms_tbl_update(i).salesrep_id IS NOT NULL AND p_payment_terms_tbl_update(i).salesrep_id <> r_payment_terms.salesrep_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:salesrep_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:salesrep_id was being updated');
IF(p_payment_terms_tbl_update(i).approved_by IS NOT NULL AND p_payment_terms_tbl_update(i).approved_by <> r_payment_terms.approved_by) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:approved_by',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:approved_by was being updated');
IF(p_payment_terms_tbl_update(i).status IS NOT NULL AND p_payment_terms_tbl_update(i).status <> r_payment_terms.status) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:status',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:status was being updated');
IF(p_payment_terms_tbl_update(i).index_period_id IS NOT NULL AND p_payment_terms_tbl_update(i).index_period_id <> r_payment_terms.index_period_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:index_period_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:index_period_id was being updated');
IF(p_payment_terms_tbl_update(i).index_term_indicator IS NOT NULL AND p_payment_terms_tbl_update(i).index_term_indicator <> r_payment_terms.index_term_indicator) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:index_term_indicator',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:index_term_indicator was being updated');
IF(p_payment_terms_tbl_update(i).cust_po_number IS NOT NULL AND p_payment_terms_tbl_update(i).cust_po_number <> r_payment_terms.cust_po_number) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:index_term_indicator',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:index_term_indicator was being updated');
IF(p_payment_terms_tbl_update(i).po_header_id IS NOT NULL AND p_payment_terms_tbl_update(i).po_header_id <> r_payment_terms.po_header_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:po_header_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:po_header_id was being updated');
IF(p_payment_terms_tbl_update(i).receipt_method_id IS NOT NULL AND p_payment_terms_tbl_update(i).receipt_method_id <> r_payment_terms.receipt_method_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:receipt_method_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:receipt_method_id was being updated');
IF(p_payment_terms_tbl_update(i).var_rent_inv_id IS NOT NULL AND p_payment_terms_tbl_update(i).var_rent_inv_id <> r_payment_terms.var_rent_inv_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:var_rent_inv_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:var_rent_inv_id was being updated');
IF(p_payment_terms_tbl_update(i).var_rent_type IS NOT NULL AND p_payment_terms_tbl_update(i).var_rent_type <> r_payment_terms.var_rent_type) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:var_rent_type',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:var_rent_type was being updated');
IF(p_payment_terms_tbl_update(i).norm_start_date IS NOT NULL AND p_payment_terms_tbl_update(i).norm_start_date <> r_payment_terms.norm_start_date) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:norm_start_date',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:norm_start_date was being updated');
IF(p_payment_terms_tbl_update(i).term_template_id IS NOT NULL AND p_payment_terms_tbl_update(i).term_template_id <> r_payment_terms.term_template_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:term_template_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:term_template_id was being updated');
IF(p_payment_terms_tbl_update(i).event_type_code IS NOT NULL AND p_payment_terms_tbl_update(i).event_type_code <> r_payment_terms.event_type_code) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:event_type_code',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:event_type_code was being updated');
IF(p_payment_terms_tbl_update(i).lease_status IS NOT NULL AND p_payment_terms_tbl_update(i).lease_status <> r_payment_terms.lease_status) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:lease_status',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:lease_status was being updated');
IF(p_payment_terms_tbl_update(i).norm_end_date IS NOT NULL AND p_payment_terms_tbl_update(i).norm_end_date <> r_payment_terms.norm_end_date) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:norm_end_date',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:norm_end_date was being updated');
IF(p_payment_terms_tbl_update(i).recoverable_flag IS NOT NULL AND p_payment_terms_tbl_update(i).recoverable_flag <> r_payment_terms.recoverable_flag) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:recoverable_flag',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:recoverable_flag was being updated');
IF(p_payment_terms_tbl_update(i).period_billrec_id IS NOT NULL AND p_payment_terms_tbl_update(i).period_billrec_id <> r_payment_terms.period_billrec_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:period_billrec_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:period_billrec_id was being updated');
IF(p_payment_terms_tbl_update(i).amount_type IS NOT NULL AND p_payment_terms_tbl_update(i).amount_type <> r_payment_terms.amount_type) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:amount_type',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:amount_type was being updated');
IF(p_payment_terms_tbl_update(i).rec_agr_line_id IS NOT NULL AND p_payment_terms_tbl_update(i).rec_agr_line_id <> r_payment_terms.rec_agr_line_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:rec_agr_line_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:rec_agr_line_id was being updated');
IF(p_payment_terms_tbl_update(i).grouping_rule_id IS NOT NULL AND p_payment_terms_tbl_update(i).grouping_rule_id <> r_payment_terms.grouping_rule_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:grouping_rule_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:grouping_rule_id was being updated');
IF(p_payment_terms_tbl_update(i).area_type_code IS NOT NULL AND p_payment_terms_tbl_update(i).area_type_code <> r_payment_terms.area_type_code) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:area_type_code',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:area_type_code was being updated');
IF(p_payment_terms_tbl_update(i).area IS NOT NULL AND p_payment_terms_tbl_update(i).area <> r_payment_terms.area) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:area',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:area was being updated');
IF(p_payment_terms_tbl_update(i).term_altered_flag IS NOT NULL AND p_payment_terms_tbl_update(i).term_altered_flag <> r_payment_terms.term_altered_flag) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:term_altered_flag',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:term_altered_flag was being updated');
IF(p_payment_terms_tbl_update(i).source_code IS NOT NULL AND p_payment_terms_tbl_update(i).source_code <> r_payment_terms.source_code) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:source_code',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:source_code was being updated');
IF(p_payment_terms_tbl_update(i).term_comments IS NOT NULL AND p_payment_terms_tbl_update(i).term_comments <> r_payment_terms.term_comments) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:term_comments',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:term_comments was being updated');
IF(p_payment_terms_tbl_update(i).legal_entity_id IS NOT NULL AND p_payment_terms_tbl_update(i).legal_entity_id <> r_payment_terms.legal_entity_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:legal_entity_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:legal_entity_id was being updated');
IF(p_payment_terms_tbl_update(i).tax_classification_code IS NOT NULL AND p_payment_terms_tbl_update(i).tax_classification_code <> r_payment_terms.tax_classification_code) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:tax_classification_code',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:tax_classification_code was being updated');
IF(p_payment_terms_tbl_update(i).index_norm_flag IS NOT NULL AND p_payment_terms_tbl_update(i).index_norm_flag <> r_payment_terms.index_norm_flag) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:index_norm_flag',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:index_norm_flag was being updated');
IF(p_payment_terms_tbl_update(i).parent_term_id IS NOT NULL AND p_payment_terms_tbl_update(i).parent_term_id <> r_payment_terms.parent_term_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:parent_term_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:parent_term_id was being updated');
IF(p_payment_terms_tbl_update(i).include_in_var_rent IS NOT NULL AND p_payment_terms_tbl_update(i).include_in_var_rent <> r_payment_terms.include_in_var_rent) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:include_in_var_rent',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:include_in_var_rent was being updated');
IF(p_payment_terms_tbl_update(i).update_nbp_flag IS NOT NULL AND p_payment_terms_tbl_update(i).update_nbp_flag <> r_payment_terms.update_nbp_flag) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:update_nbp_flag',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:update_nbp_flag was being updated');
IF(p_payment_terms_tbl_update(i).recur_bb_calc_date IS NOT NULL AND p_payment_terms_tbl_update(i).recur_bb_calc_date <> r_payment_terms.recur_bb_calc_date) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:recur_bb_calc_date',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:recur_bb_calc_date was being updated');
IF(p_payment_terms_tbl_update(i).opex_type IS NOT NULL AND p_payment_terms_tbl_update(i).opex_type <> r_payment_terms.opex_type) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:opex_type',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:opex_type was being updated');
IF(p_payment_terms_tbl_update(i).opex_agr_id IS NOT NULL AND p_payment_terms_tbl_update(i).opex_agr_id <> r_payment_terms.opex_agr_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:opex_agr_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:opex_agr_id was being updated');
IF(p_payment_terms_tbl_update(i).opex_recon_id IS NOT NULL AND p_payment_terms_tbl_update(i).opex_recon_id <> r_payment_terms.opex_recon_id) THEN
pvt_debug(g_pkg_name||'-UPDATE_TERMS:In Edit OR AMEND Final Mode.Cant Update the field:opex_recon_id',3);
fnd_message.set_token('ERR_MSG', 'In Edit OR AMEND Final Mode.Non-Updatable field:opex_recon_id was being updated');
IF ( p_payment_terms_tbl_update(i).payment_purpose_code IS NULL )
THEN
p_payment_terms_tbl_update(i).payment_purpose_code := r_payment_terms.payment_purpose_code;
IF ( p_payment_terms_tbl_update(i).payment_term_type_code IS NULL )--OR p_payment_terms_tbl_update(i).payment_term_type_code = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).payment_term_type_code := r_payment_terms.payment_term_type_code;
IF ( p_payment_terms_tbl_update(i).frequency_code IS NULL )--OR p_payment_terms_tbl_update(i).frequency_code = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).frequency_code := r_payment_terms.frequency_code;
IF ( p_payment_terms_tbl_update(i).lease_id IS NULL )--OR p_payment_terms_tbl_update(i).lease_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).lease_id := r_payment_terms.lease_id;
IF ( p_payment_terms_tbl_update(i).lease_change_id IS NULL )--OR p_payment_terms_tbl_update(i).lease_change_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).lease_change_id := r_payment_terms.lease_change_id;
IF ( p_payment_terms_tbl_update(i).start_date IS NULL )--OR p_payment_terms_tbl_update(i).start_date = PN_LEASE_UTILS.G_PN_MISS_DATE)
THEN
p_payment_terms_tbl_update(i).start_date := r_payment_terms.start_date;
IF ( p_payment_terms_tbl_update(i).end_date IS NULL )--OR p_payment_terms_tbl_update(i).end_date = PN_LEASE_UTILS.G_PN_MISS_DATE)
THEN
p_payment_terms_tbl_update(i).end_date := r_payment_terms.end_date;
IF ( p_payment_terms_tbl_update(i).code_combination_id IS NULL )--OR p_payment_terms_tbl_update(i).code_combination_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).code_combination_id := r_payment_terms.code_combination_id;
IF ( p_payment_terms_tbl_update(i).vendor_id IS NULL )--OR p_payment_terms_tbl_update(i).vendor_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).vendor_id := r_payment_terms.vendor_id;
IF ( p_payment_terms_tbl_update(i).vendor_site_id IS NULL )--OR p_payment_terms_tbl_update(i).vendor_site_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).vendor_site_id := r_payment_terms.vendor_site_id;
IF ( p_payment_terms_tbl_update(i).target_date IS NULL )--OR p_payment_terms_tbl_update(i).target_date = PN_LEASE_UTILS.G_PN_MISS_DATE)
THEN
p_payment_terms_tbl_update(i).target_date := r_payment_terms.target_date;
IF ( p_payment_terms_tbl_update(i).actual_amount IS NULL )--OR p_payment_terms_tbl_update(i).actual_amount = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).actual_amount := r_payment_terms.actual_amount;
IF ( p_payment_terms_tbl_update(i).estimated_amount IS NULL )--OR p_payment_terms_tbl_update(i).estimated_amount = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).estimated_amount := r_payment_terms.estimated_amount;
IF ( p_payment_terms_tbl_update(i).set_of_books_id IS NULL )--OR p_payment_terms_tbl_update(i).set_of_books_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).set_of_books_id := r_payment_terms.set_of_books_id;
IF ( p_payment_terms_tbl_update(i).currency_code IS NULL )--OR p_payment_terms_tbl_update(i).currency_code = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).currency_code := r_payment_terms.currency_code;
IF ( p_payment_terms_tbl_update(i).rate IS NULL )--OR p_payment_terms_tbl_update(i).rate = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).rate := r_payment_terms.rate;
IF ( p_payment_terms_tbl_update(i).customer_id IS NULL )--OR p_payment_terms_tbl_update(i).customer_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).customer_id := r_payment_terms.customer_id;
IF ( p_payment_terms_tbl_update(i).customer_site_use_id IS NULL )--OR p_payment_terms_tbl_update(i).customer_site_use_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).customer_site_use_id := r_payment_terms.customer_site_use_id;
IF ( p_payment_terms_tbl_update(i).normalize IS NULL )--OR p_payment_terms_tbl_update(i).normalize = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).normalize := r_payment_terms.normalize;
IF ( p_payment_terms_tbl_update(i).location_id IS NULL )--OR p_payment_terms_tbl_update(i).location_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).location_id := r_payment_terms.location_id;
IF ( p_payment_terms_tbl_update(i).schedule_day IS NULL )--OR p_payment_terms_tbl_update(i).schedule_day = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).schedule_day := r_payment_terms.schedule_day;
IF ( p_payment_terms_tbl_update(i).cust_ship_site_id IS NULL )--OR p_payment_terms_tbl_update(i).cust_ship_site_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).cust_ship_site_id := r_payment_terms.cust_ship_site_id;
IF ( p_payment_terms_tbl_update(i).ap_ar_term_id IS NULL )--OR p_payment_terms_tbl_update(i).ap_ar_term_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).ap_ar_term_id := r_payment_terms.ap_ar_term_id;
IF ( p_payment_terms_tbl_update(i).cust_trx_type_id IS NULL )--OR p_payment_terms_tbl_update(i).cust_trx_type_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).cust_trx_type_id := r_payment_terms.cust_trx_type_id;
IF ( p_payment_terms_tbl_update(i).project_id IS NULL )--OR p_payment_terms_tbl_update(i).project_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).project_id := r_payment_terms.project_id;
IF ( p_payment_terms_tbl_update(i).task_id IS NULL )--OR p_payment_terms_tbl_update(i).task_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).task_id := r_payment_terms.task_id;
IF ( p_payment_terms_tbl_update(i).organization_id IS NULL )--OR p_payment_terms_tbl_update(i).organization_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).organization_id := r_payment_terms.organization_id;
IF ( p_payment_terms_tbl_update(i).expenditure_type IS NULL )--OR p_payment_terms_tbl_update(i).expenditure_type = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).expenditure_type := r_payment_terms.expenditure_type;
IF ( p_payment_terms_tbl_update(i).expenditure_item_date IS NULL )--OR p_payment_terms_tbl_update(i).expenditure_item_date = PN_LEASE_UTILS.G_PN_MISS_DATE)
THEN
p_payment_terms_tbl_update(i).expenditure_item_date := r_payment_terms.expenditure_item_date;
IF ( p_payment_terms_tbl_update(i).tax_group_id IS NULL )--OR p_payment_terms_tbl_update(i).tax_group_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).tax_group_id := r_payment_terms.tax_group_id;
IF ( p_payment_terms_tbl_update(i).tax_code_id IS NULL )--OR p_payment_terms_tbl_update(i).tax_code_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).tax_code_id := r_payment_terms.tax_code_id;
IF ( p_payment_terms_tbl_update(i).tax_included IS NULL )--OR p_payment_terms_tbl_update(i).tax_included = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).tax_included := r_payment_terms.tax_included;
IF ( p_payment_terms_tbl_update(i).distribution_set_id IS NULL )--OR p_payment_terms_tbl_update(i).distribution_set_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).distribution_set_id := r_payment_terms.distribution_set_id;
IF ( p_payment_terms_tbl_update(i).inv_rule_id IS NULL )--OR p_payment_terms_tbl_update(i).inv_rule_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).inv_rule_id := r_payment_terms.inv_rule_id;
IF ( p_payment_terms_tbl_update(i).account_rule_id IS NULL )--OR p_payment_terms_tbl_update(i).account_rule_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).account_rule_id := r_payment_terms.account_rule_id;
IF ( p_payment_terms_tbl_update(i).salesrep_id IS NULL )--OR p_payment_terms_tbl_update(i).salesrep_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).salesrep_id := r_payment_terms.salesrep_id;
IF ( p_payment_terms_tbl_update(i).approved_by IS NULL )--OR p_payment_terms_tbl_update(i).approved_by = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).approved_by := r_payment_terms.approved_by;
IF ( p_payment_terms_tbl_update(i).status IS NULL )--OR p_payment_terms_tbl_update(i).status = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).status := r_payment_terms.status;
IF ( p_payment_terms_tbl_update(i).index_period_id IS NULL )--OR p_payment_terms_tbl_update(i).index_period_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).index_period_id := r_payment_terms.index_period_id;
IF ( p_payment_terms_tbl_update(i).index_term_indicator IS NULL )--OR p_payment_terms_tbl_update(i).index_term_indicator = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).index_term_indicator := r_payment_terms.index_term_indicator;
IF ( p_payment_terms_tbl_update(i).po_header_id IS NULL )--OR p_payment_terms_tbl_update(i).po_header_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).po_header_id := r_payment_terms.po_header_id;
IF ( p_payment_terms_tbl_update(i).cust_po_number IS NULL )--OR p_payment_terms_tbl_update(i).cust_po_number = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).cust_po_number := r_payment_terms.cust_po_number;
IF ( p_payment_terms_tbl_update(i).receipt_method_id IS NULL )--OR p_payment_terms_tbl_update(i).receipt_method_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).receipt_method_id := r_payment_terms.receipt_method_id;
IF ( p_payment_terms_tbl_update(i).var_rent_inv_id IS NULL )--OR p_payment_terms_tbl_update(i).var_rent_inv_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).var_rent_inv_id := r_payment_terms.var_rent_inv_id;
IF ( p_payment_terms_tbl_update(i).var_rent_type IS NULL )--OR p_payment_terms_tbl_update(i).var_rent_type = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).var_rent_type := r_payment_terms.var_rent_type;
IF ( p_payment_terms_tbl_update(i).changed_flag IS NULL )--OR p_payment_terms_tbl_update(i).changed_flag = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).changed_flag := r_payment_terms.changed_flag;
IF ( p_payment_terms_tbl_update(i).norm_start_date IS NULL )--OR p_payment_terms_tbl_update(i).norm_start_date = PN_LEASE_UTILS.G_PN_MISS_DATE)
THEN
p_payment_terms_tbl_update(i).norm_start_date := r_payment_terms.norm_start_date;
IF ( p_payment_terms_tbl_update(i).term_template_id IS NULL )--OR p_payment_terms_tbl_update(i).term_template_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).term_template_id := r_payment_terms.term_template_id;
IF ( p_payment_terms_tbl_update(i).event_type_code IS NULL )--OR p_payment_terms_tbl_update(i).event_type_code = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).event_type_code := r_payment_terms.event_type_code;
IF ( p_payment_terms_tbl_update(i).lease_status IS NULL )--OR p_payment_terms_tbl_update(i).lease_status = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).lease_status := r_payment_terms.lease_status;
IF ( p_payment_terms_tbl_update(i).norm_end_date IS NULL )--OR p_payment_terms_tbl_update(i).norm_end_date = PN_LEASE_UTILS.G_PN_MISS_DATE)
THEN
p_payment_terms_tbl_update(i).norm_end_date := r_payment_terms.norm_end_date;
IF ( p_payment_terms_tbl_update(i).recoverable_flag IS NULL )--OR p_payment_terms_tbl_update(i).recoverable_flag = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).recoverable_flag := r_payment_terms.recoverable_flag;
IF ( p_payment_terms_tbl_update(i).period_billrec_id IS NULL )--OR p_payment_terms_tbl_update(i).period_billrec_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).period_billrec_id := r_payment_terms.period_billrec_id;
IF ( p_payment_terms_tbl_update(i).amount_type IS NULL )--OR p_payment_terms_tbl_update(i).amount_type = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).amount_type := r_payment_terms.amount_type;
IF ( p_payment_terms_tbl_update(i).rec_agr_line_id IS NULL )--OR p_payment_terms_tbl_update(i).rec_agr_line_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).rec_agr_line_id := r_payment_terms.rec_agr_line_id;
IF ( p_payment_terms_tbl_update(i).grouping_rule_id IS NULL )--OR p_payment_terms_tbl_update(i).grouping_rule_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).grouping_rule_id := r_payment_terms.grouping_rule_id;
IF ( p_payment_terms_tbl_update(i).area_type_code IS NULL )--OR p_payment_terms_tbl_update(i).area_type_code = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).area_type_code := r_payment_terms.area_type_code;
IF ( p_payment_terms_tbl_update(i).area IS NULL )--OR p_payment_terms_tbl_update(i).area = PN_LEASE_UTILS.G_PN_MISS_NUM
THEN
p_payment_terms_tbl_update(i).area := r_payment_terms.area;
IF ( p_payment_terms_tbl_update(i).term_altered_flag IS NULL )--OR p_payment_terms_tbl_update(i).term_altered_flag = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).term_altered_flag := r_payment_terms.term_altered_flag;
IF ( p_payment_terms_tbl_update(i).source_code IS NULL )--OR p_payment_terms_tbl_update(i).source_code = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).source_code := r_payment_terms.source_code;
IF ( p_payment_terms_tbl_update(i).term_comments IS NULL )--OR p_payment_terms_tbl_update(i).term_comments = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).term_comments := r_payment_terms.term_comments;
IF ( p_payment_terms_tbl_update(i).legal_entity_id IS NULL )--OR p_payment_terms_tbl_update(i).legal_entity_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).legal_entity_id := r_payment_terms.legal_entity_id;
IF ( p_payment_terms_tbl_update(i).tax_classification_code IS NULL )--OR p_payment_terms_tbl_update(i).tax_classification_code = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).tax_classification_code := r_payment_terms.tax_classification_code;
IF ( p_payment_terms_tbl_update(i).index_norm_flag IS NULL )--OR p_payment_terms_tbl_update(i).index_norm_flag = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).index_norm_flag := r_payment_terms.index_norm_flag;
IF ( p_payment_terms_tbl_update(i).parent_term_id IS NULL )--OR p_payment_terms_tbl_update(i).parent_term_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).parent_term_id := r_payment_terms.parent_term_id;
IF ( p_payment_terms_tbl_update(i).include_in_var_rent IS NULL )--OR p_payment_terms_tbl_update(i).include_in_var_rent = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).include_in_var_rent := r_payment_terms.include_in_var_rent;
IF ( p_payment_terms_tbl_update(i).update_nbp_flag IS NULL )--OR p_payment_terms_tbl_update(i).update_nbp_flag = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).update_nbp_flag := r_payment_terms.update_nbp_flag;
IF ( p_payment_terms_tbl_update(i).recur_bb_calc_date IS NULL )--OR p_payment_terms_tbl_update(i).recur_bb_calc_date = PN_LEASE_UTILS.G_PN_MISS_DATE)
THEN
p_payment_terms_tbl_update(i).recur_bb_calc_date := r_payment_terms.recur_bb_calc_date;
IF ( p_payment_terms_tbl_update(i).opex_type IS NULL )--OR p_payment_terms_tbl_update(i).opex_type = PN_LEASE_UTILS.G_PN_MISS_CHAR)
THEN
p_payment_terms_tbl_update(i).opex_type := r_payment_terms.opex_type;
IF ( p_payment_terms_tbl_update(i).opex_agr_id IS NULL )--OR p_payment_terms_tbl_update(i).opex_agr_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).opex_agr_id := r_payment_terms.opex_agr_id;
IF ( p_payment_terms_tbl_update(i).opex_recon_id IS NULL )--OR p_payment_terms_tbl_update(i).opex_recon_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
THEN
p_payment_terms_tbl_update(i).opex_recon_id := r_payment_terms.opex_recon_id;
pvt_debug(g_pkg_name||'UPDATE_TERMS:Before UPDATE_PAYMENT_TERMS_ROW',3);
update_payment_terms_row ( p_lease_terms_rec => p_payment_terms_tbl_update(i)
, p_lease_id => p_lease_id
, p_change_lease_id => l_lease_change_id
, p_lease_context => p_lease_context
, x_return_status => x_return_status
);
pvt_debug(g_pkg_name||'UPDATE_TERMS:UPDATE_PAYMENT_TERMS_ROW Return Status:'||x_return_status,3);
ADD_DIST_ACCOUNT (p_payment_terms_tbl_update(i),p_payment_terms_tbl_update(i).p_terms_exp_rev_accounts_tbl,l_change_account_count,'U');
pvt_debug(g_pkg_name||'UPDATE_TERMS:ADD_DIST_ACCOUNT:Exception:'||SQLERRM,3);
pvt_debug(g_pkg_name||'-update_terms:LOOOPING:EXCEPTION:FND_API.G_EXC_ERROR'||SQLERRM,3);
pvt_debug(g_pkg_name||'-update_terms:LOOOPING:EXCEPTION:'||SQLERRM,3);
pvt_debug(g_pkg_name||'UPDATE_TERMS:E_EXIT_CURRENT_TERM:Exception:'||SQLERRM,3);
pvt_debug(g_pkg_name||'UPDATE_TERMS:ELSE:',3);
END IF; --Create-Update Check
pvt_debug(g_pkg_name||'-update_terms:Assigning the term table values to other table',3);
pvt_debug(g_pkg_name||'-update_terms::MAIN EXCEPTION:FND_API.G_EXC_ERROR'||SQLERRM,3);
pvt_debug(g_pkg_name||'-update_terms:MAIN:EXCEPTION:'||SQLERRM,3);
END update_terms;
/*Insert payment */
PROCEDURE create_terms (
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_validate IN VARCHAR2 := FND_API.G_FALSE
, p_payment_terms_tbl IN OUT NOCOPY lease_terms_tbl
, p_lease_id IN NUMBER
, p_lease_context IN VARCHAR2 DEFAULT NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
AS
l_api_version CONSTANT NUMBER := 1.0;
SAVEPOINT insert_payment_pvt;
SELECT pla.lease_class_code
, pld.lease_commencement_date
, pld.lease_termination_date
, pla.parent_lease_id
, DECODE (pla.lease_class_code
, 'DIRECT', 'PAYMENT'
, 'THIRD_PARTY', 'BILLING'
, 'SUB_LEASE','BILLING' /* Bug#13915457*/
)
, PN_LEASE_UTILS.get_lease_change_id(p_lease_id ,g_org_id)
,pla.status
INTO l_lease_class_code
, l_lease_commencement_date
, l_lease_termination_date
, l_parent_lease_id
, l_termtemp_type
, l_lease_change_id
, l_lease_status
FROM pn_leases_all pla, pn_lease_details_all pld
WHERE pla.lease_id = p_lease_id
AND pla.lease_id = pld.lease_id
AND pld.org_id = pla.org_id
AND pla.org_id = g_org_id;
SELECT COUNT(1)
INTO l_locations_count
FROM pn_tenancies_all
WHERE lease_id = p_lease_id
AND org_id = g_org_id;
SELECT decode (p_parameter,pn_lease_utils.g_pn_miss_char,'',p_parameter)
INTO l_char
FROM dual;
SELECT decode (p_parameter,pn_lease_utils.g_pn_miss_num,'',p_parameter)
INTO l_num
FROM dual;
SELECT decode (p_parameter,pn_lease_utils.g_pn_miss_date,'',p_parameter)
INTO l_date
FROM dual;
SELECT count(1)
INTO l_count
FROM fnd_descr_flex_col_usage_vl
WHERE descriptive_flexfield_name = UPPER(trim(p_desc_name))
AND enabled_flag = 'Y'
AND display_flag = 'Y';
SELECT a.*
FROM fnd_descr_flex_col_usage_vl a
WHERE a.descriptive_flexfield_name = trim(p_desc_name)
AND a.enabled_flag = 'Y'
AND a.display_flag = 'Y'
AND a.required_flag = 'Y';
PROCEDURE update_status( p_lease_id IN OUT NOCOPY NUMBER
, p_new_approval_status_code IN OUT NOCOPY VARCHAR2
, p_new_lease_status IN OUT NOCOPY VARCHAR2
, p_operation IN VARCHAR2 DEFAULT 'UPDATE_STATUS'
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR c_lease_id( p_lease_id IN NUMBER
, p_org_id IN NUMBER
)
IS SELECT PLA.lease_id lease_id
,PLA.status status
,PLA.lease_status lease_status
,PLD.lease_commencement_date lease_commencement_date
FROM pn_leases_all PLA
,pn_lease_details_all PLD
WHERE PLD.lease_id = PLA.lease_id
AND PLD.org_id = PLA.org_id
AND PLA.lease_id = p_lease_id
AND PLA.org_id = g_org_id;
IS SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 1
FROM pn_tenancies_ALL
WHERE lease_id= p_lease_id
AND org_id = g_org_id
);
l_schedule_context VARCHAR2 (30) := 'UPDATE';
g_program_unit := 'UPDATE_STATUS';
pvt_debug (g_pkg_name||'.'|| g_program_unit || ' - Start of status update procedure', 3);
fnd_message.set_token ('ERR_MSG', 'The provided lease id is invalid. Please provide a valid lease id to update the status');
fnd_message.set_token ('ERR_MSG', 'Please provide a valid lease id to update the status');
fnd_message.set_token ('ERR_MSG', 'Please provide a valid approval status code to update');
fnd_message.set_token ('ERR_MSG', 'Please provide a valid lease status code to update');
UPDATE pn_leases_all
SET lease_status = p_new_lease_status
WHERE lease_id = p_lease_id
AND org_id= g_org_id;
pvt_debug('PN_LEASE_PVT-update_status:Exception in Raising Business Event',3);
UPDATE pn_leases_all
SET status = l_submitted_status -- Holds the value of S'
WHERE lease_id = p_lease_id
AND org_id= g_org_id;
UPDATE pn_leases_all
SET status = p_new_approval_status_code -- Will hold the value of 'F'
WHERE lease_id = p_lease_id
AND org_id= g_org_id;
END update_status;