CREATE OR REPLACE package body APPS.xxiron_pn_lease_pkg is procedure xxiron_pn_lease_proc(errbuf out varchar2,retcode OUT NUMBER) is begin null; end; function xxiron_pn_lease_func( P_LEASE_NAME VARCHAR2, P_PARENT_LEASE_NAME VARCHAR2, P_LEASE_TYPE VARCHAR2, P_LEASE_CLASS VARCHAR2, P_LOCATION_CODE VARCHAR2, P_STATUS VARCHAR2, P_LEASE_STATUS VARCHAR2, P_LEASE_COMMENCEMENT_DATE DATE, P_LEASE_TERMINATION_DATE DATE, P_LEASE_EXECUTION_DATE DATE, P_LEASE_EXTENSION_DATE DATE, P_PYMT_TERM_PRO_RULE VARCHAR2, P_TERM_TEMPLATE_NAME VARCHAR2, P_CUSTOMER_NAME VARCHAR2, P_GROUPING_RULE_NAME VARCHAR2, P_SEND_ENTRIES VARCHAR2, P_AMEND_COMM_DATE DATE, P_AMEND_EXEC_DATE DATE, P_AMEND_LEASE_NAME VARCHAR2, P_CAL_START VARCHAR2, P_ATTRIBUTE1 VARCHAR2, P_ATTRIBUTE2 VARCHAR2, P_ATTRIBUTE3 VARCHAR2, P_ATTRIBUTE4 VARCHAR2, P_ATTRIBUTE5 VARCHAR2, P_ATTRIBUTE6 VARCHAR2, P_ATTRIBUTE7 VARCHAR2, P_ATTRIBUTE8 VARCHAR2, P_ATTRIBUTE9 VARCHAR2, P_ATTRIBUTE10 VARCHAR2, P_ATTRIBUTE11 VARCHAR2, P_ATTRIBUTE12 VARCHAR2, P_ATTRIBUTE13 VARCHAR2, P_ATTRIBUTE14 VARCHAR2, P_ATTRIBUTE15 VARCHAR2) return varchar2 is v_error_msg varchar2(10000); v_location_id number; v_pymt_term_pro_id fnd_lookups.lookup_code%type; v_lease_status_code fnd_lookups.lookup_code%type; v_status_code fnd_lookups.lookup_code%type; v_lease_class_type fnd_lookups.lookup_code%type; lv_return_status fnd_lookups.lookup_code%type; v_lease_type fnd_lookups.lookup_code%type; v_lease_name pn_leases_all.name%type; begin v_error_msg := null; v_lease_name := null; v_lease_type := null; v_lease_class_type := null; v_status_code := null; v_lease_status_code := null; v_pymt_term_pro_id := null; v_location_id := null; lv_return_status := null; begin select name into v_lease_name from pn_leases_all where 1=1 and upper(name) = upper(P_lease_name); v_error_msg := 'Lease Name already exists'; exception when no_data_found then null; when others then v_error_msg := ' Error at Lease Name'||SQLERRM; end; if p_location_code is not null then begin select location_id into v_location_id from pn_locations_all where 1=1 and upper(location_code) = upper(p_location_code); exception when no_data_found then v_error_msg := v_error_msg||' Location not exists'; when others then v_error_msg := v_error_msg||' and Location code'||SQLERRM; end; end if; Begin select lookup_code into v_lease_type from fnd_lookups where 1=1 and lookup_type = 'PN_LEASE_TYPE' and upper(meaning) = upper(p_lease_type) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Lease type not exists'; when others then v_error_msg := v_error_msg||' and Lease Type'||SQLERRM; end; Begin select lookup_code into v_lease_class_type from fnd_lookups where 1=1 and lookup_type = 'PN_CLASS_TYPE' and upper(meaning) = upper(P_LEASE_CLASS) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Lease Class type not exists'; when others then v_error_msg := v_error_msg||' and Lease Class Type'||SQLERRM; end; Begin select lookup_code into v_status_code from fnd_lookups where 1=1 and lookup_type = 'PN_LEASE_STATUS_TYPE' and upper(meaning) = upper(P_STATUS) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' status code not exists'; when others then v_error_msg := v_error_msg||' and status code'||SQLERRM; end; Begin select lookup_code into v_lease_status_code from fnd_lookups where 1=1 and lookup_type = 'PN_LEASESTATUS_TYPE' and upper(meaning) = upper(P_LEASE_STATUS) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Lease status code not exists'; when others then v_error_msg := v_error_msg||' and Lease status code'||SQLERRM; end; Begin select lookup_code into v_pymt_term_pro_id from fnd_lookups where 1=1 and lookup_type = 'PN_PRORATION_RULE' and upper(meaning) = upper(P_PYMT_TERM_PRO_RULE) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Payment proration rule not exists'; when others then v_error_msg := v_error_msg||' and Payment proration rule '||SQLERRM; end; if v_error_msg is null then insert into APPS.XXIRON_PN_LEASE_STG ( PN_LEASE_ID , LEASE_NAME ,PARENT_LEASE_NAME ,PARENT_LEASE_ID ,LEASE_TYPE ,LEASE_TYPE_CODE ,LEASE_CLASS ,LEASE_CLASS_CODE ,LOCATION_CODE ,LOCATION_ID ,STATUS_CODE ,STATUS ,LEASE_STATUS_CODE ,LEASE_STATUS ,LEASE_COMMENCEMENT_DATE ,LEASE_TERMINATION_DATE ,LEASE_EXECUTION_DATE ,LEASE_EXTENSION_DATE ,PYMT_TERM_PRO_RULE_ID ,PYMT_TERM_PRO_RULE ,TERM_TEMPLATE_NAME ,TERM_TEMPLATE_ID ,CUSTOMER_ID ,CUSTOMER_NAME ,GROUPING_RULE_NAME ,GROUPING_RULE_ID ,SEND_ENTRIES ,AMEND_COMM_DATE ,AMEND_EXEC_DATE ,AMEND_LEASE_NAME ,CAL_START ,REQUEST_ID ,PROCESS_FLAG ,ERROR_MESSAGE ,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 ) values ( null---PN_LEASE_ID , P_LEASE_NAME ,P_PARENT_LEASE_NAME ,null--PARENT_LEASE_ID ,P_LEASE_TYPE ,v_lease_type---LEASE_TYPE_CODE ,P_LEASE_CLASS ,v_lease_class_type---LEASE_CLASS_CODE ,P_LOCATION_CODE ,v_location_id--LOCATION_ID ,v_status_code---STATUS_CODE ,P_STATUS ,v_lease_status_code--P_LEASE_STATUS_CODE ,P_LEASE_STATUS ,P_LEASE_COMMENCEMENT_DATE ,P_LEASE_TERMINATION_DATE ,P_LEASE_EXECUTION_DATE ,P_LEASE_EXTENSION_DATE ,v_pymt_term_pro_id----PYMT_TERM_PRO_RULE_ID ,P_PYMT_TERM_PRO_RULE ,P_TERM_TEMPLATE_NAME ,null----P_TERM_TEMPLATE_ID ,null--P_CUSTOMER_ID ,P_CUSTOMER_NAME ,P_GROUPING_RULE_NAME ,null----P_GROUPING_RULE_ID ,P_SEND_ENTRIES ,P_AMEND_COMM_DATE ,P_AMEND_EXEC_DATE ,P_AMEND_LEASE_NAME ,P_CAL_START ,null , null ,null ,sysdate ,fnd_global.user_id ,sysdate ,fnd_global.user_id ,fnd_global.user_id ,null ,P_ATTRIBUTE1 ,P_ATTRIBUTE2 ,P_ATTRIBUTE3 ,P_ATTRIBUTE4 ,P_ATTRIBUTE5 ,P_ATTRIBUTE6 ,P_ATTRIBUTE7 ,P_ATTRIBUTE8 ,P_ATTRIBUTE9 ,P_ATTRIBUTE10 ,P_ATTRIBUTE11 ,P_ATTRIBUTE12 ,P_ATTRIBUTE13 ,P_ATTRIBUTE14 ,P_ATTRIBUTE15 ); return null; else return v_error_msg; end if; end; function xxiron_lease_locations_func( P_LEASE_NAME VARCHAR2, P_TENANCY_USAGE VARCHAR2, P_LOCATION_CODE VARCHAR2, P_OCCUPANCY_DATE DATE, P_ESTIMATED_OCCUPANCY_DATE DATE, P_EXPIRATION_DATE DATE, P_LOCATION_ASSIGNABLE_AREA NUMBER, P_LOCATION_RENTABLE_AREA NUMBER, P_LOCATION_USABLE_AREA NUMBER, P_ATTRIBUTE1 VARCHAR2, P_ATTRIBUTE2 VARCHAR2, P_ATTRIBUTE3 VARCHAR2, P_ATTRIBUTE4 VARCHAR2, P_ATTRIBUTE5 VARCHAR2, P_ATTRIBUTE6 VARCHAR2, P_ATTRIBUTE7 VARCHAR2, P_ATTRIBUTE8 VARCHAR2, P_ATTRIBUTE9 VARCHAR2, P_ATTRIBUTE10 VARCHAR2, P_ATTRIBUTE11 VARCHAR2, P_ATTRIBUTE12 VARCHAR2, P_ATTRIBUTE13 VARCHAR2, P_ATTRIBUTE14 VARCHAR2, P_ATTRIBUTE15 VARCHAR2 ) return varchar2 is v_error_msg varchar2(10000); v_lease_name pn_leases_all.name%type; v_lease_usage_code fnd_lookups.lookup_code%type; v_location_id number; begin v_error_msg := null; v_lease_name := null; v_lease_usage_code := null; v_location_id := null; begin select pnl.name into v_lease_name from pn_leases_all pnl where 1=1 and upper(name) = upper(P_lease_name); exception when no_data_found then v_error_msg := 'Lease Name not exists'; when others then v_error_msg := ' Error at Lease Name'||SQLERRM; end; if p_location_code is not null then begin select location_id into v_location_id from pn_locations_all where 1=1 and upper(location_code) = upper(p_location_code); exception when no_data_found then v_error_msg := v_error_msg||' Location not exists'; when others then v_error_msg := v_error_msg||' and Location code'||SQLERRM; end; end if; Begin select lookup_code into v_lease_usage_code from fnd_lookups where 1=1 and lookup_type = 'PN_TENANCY_USAGE_TYPE_L' and upper(meaning) = upper(P_TENANCY_USAGE) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Tenancy usage not exists'; when others then v_error_msg := v_error_msg||' and Error at Tenancy usage'||SQLERRM; end; if v_error_msg is null then insert into APPS.XXIRON_PN_LEASE_LOCATION_STG ( PN_LEASE_LOCATION_ID ,LEASE_NAME ,TENANCY_USAGE ,TENANCY_USAGE_LOOKUP_CODE ,LOCATION_CODE ,OCCUPANCY_DATE ,ESTIMATED_OCCUPANCY_DATE ,EXPIRATION_DATE ,LOCATION_ASSIGNABLE_AREA ,LOCATION_RENTABLE_AREA ,LOCATION_USABLE_AREA ,REQUEST_ID ,PROCESS_FLAG ,ERROR_MESSAGE ,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 ) values ( null---PN_LEASE_LOCATION_ID ,P_LEASE_NAME ,P_TENANCY_USAGE ,v_lease_usage_code--TENANCY_USAGE_LOOKUP_CODE ,P_LOCATION_CODE ,P_OCCUPANCY_DATE ,P_ESTIMATED_OCCUPANCY_DATE ,P_EXPIRATION_DATE ,P_LOCATION_ASSIGNABLE_AREA ,P_LOCATION_RENTABLE_AREA ,P_LOCATION_USABLE_AREA ,null , null ,null ,sysdate ,fnd_global.user_id ,sysdate ,fnd_global.user_id ,fnd_global.user_id ,null ,P_ATTRIBUTE1 ,P_ATTRIBUTE2 ,P_ATTRIBUTE3 ,P_ATTRIBUTE4 ,P_ATTRIBUTE5 ,P_ATTRIBUTE6 ,P_ATTRIBUTE7 ,P_ATTRIBUTE8 ,P_ATTRIBUTE9 ,P_ATTRIBUTE10 ,P_ATTRIBUTE11 ,P_ATTRIBUTE12 ,P_ATTRIBUTE13 ,P_ATTRIBUTE14 ,P_ATTRIBUTE15 ); return null; else return v_error_msg; end if; end; function xxiron_lease_rights_func( P_LEASE_NAME VARCHAR2, P_RIGHT_TYPE VARCHAR2, P_RIGHT_STATUS VARCHAR2, P_RIGHT_REFERENCE VARCHAR2, P_RIGHT_COMMENTS VARCHAR2, P_ATTRIBUTE1 VARCHAR2, P_ATTRIBUTE2 VARCHAR2, P_ATTRIBUTE3 VARCHAR2, P_ATTRIBUTE4 VARCHAR2, P_ATTRIBUTE5 VARCHAR2, P_ATTRIBUTE6 VARCHAR2, P_ATTRIBUTE7 VARCHAR2, P_ATTRIBUTE8 VARCHAR2, P_ATTRIBUTE9 VARCHAR2, P_ATTRIBUTE10 VARCHAR2, P_ATTRIBUTE11 VARCHAR2, P_ATTRIBUTE12 VARCHAR2, P_ATTRIBUTE13 VARCHAR2, P_ATTRIBUTE14 VARCHAR2, P_ATTRIBUTE15 VARCHAR2 ) return varchar2 is v_error_msg varchar2(10000); v_lease_name pn_leases_all.name%type; v_right_status_code fnd_lookups.lookup_code%type; v_right_type_code fnd_lookups.lookup_code%type; begin v_error_msg := null; v_lease_name := null; v_right_status_code := null; v_right_type_code := null; begin select pnl.name into v_lease_name from pn_leases_all pnl where 1=1 and upper(name) = upper(P_lease_name); exception when no_data_found then v_error_msg := 'Lease Name not exists'; when others then v_error_msg := ' Error at Lease Name'||SQLERRM; end; Begin select lookup_code into v_right_type_code from fnd_lookups where 1=1 and lookup_type = 'PN_RIGHTS_TYPE' and upper(meaning) = upper(p_right_type) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Right Type not exists'; when others then v_error_msg := v_error_msg||' and Error at Right Type '||SQLERRM; end; Begin select lookup_code into v_right_status_code from fnd_lookups where 1=1 and lookup_type = 'PN_RIGHT_STATUS_TYPE' and upper(meaning) = upper(P_right_status) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Right Status not exists'; when others then v_error_msg := v_error_msg||' and Error at Right Status'||SQLERRM; end; if v_error_msg is null then insert into APPS.XXIRON_PN_LEASE_RIGHTS_STG ( PN_LEASE_RIGHTS_ID ,LEASE_NAME ,RIGHT_TYPE ,RIGHT_TYPE_CODE ,RIGHT_STATUS ,RIGHT_STATUS_CODE ,RIGHT_REFERENCE ,RIGHT_COMMENTS ,REQUEST_ID ,PROCESS_FLAG ,ERROR_MESSAGE ,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 ) values ( null--P_PN_LEASE_RIGHTS_ID ,P_LEASE_NAME ,P_RIGHT_TYPE ,v_right_type_code ,P_RIGHT_STATUS ,v_right_status_code ,P_RIGHT_REFERENCE ,P_RIGHT_COMMENTS ,null ,null ,null ,sysdate ,fnd_global.user_id ,sysdate ,fnd_global.user_id ,fnd_global.user_id ,null ,P_ATTRIBUTE1 ,P_ATTRIBUTE2 ,P_ATTRIBUTE3 ,P_ATTRIBUTE4 ,P_ATTRIBUTE5 ,P_ATTRIBUTE6 ,P_ATTRIBUTE7 ,P_ATTRIBUTE8 ,P_ATTRIBUTE9 ,P_ATTRIBUTE10 ,P_ATTRIBUTE11 ,P_ATTRIBUTE12 ,P_ATTRIBUTE13 ,P_ATTRIBUTE14 ,P_ATTRIBUTE15 ); return null; else return v_error_msg; end if; end; function xxiron_lease_options_func( P_LEASE_NAME VARCHAR2, P_OPTION_TYPE VARCHAR2, P_OPTION_STATUS_TYPE VARCHAR2, P_START_DATE DATE, P_EXPIRATION_DATE DATE, P_ATTRIBUTE1 VARCHAR2, P_ATTRIBUTE2 VARCHAR2, P_ATTRIBUTE3 VARCHAR2, P_ATTRIBUTE4 VARCHAR2, P_ATTRIBUTE5 VARCHAR2, P_ATTRIBUTE6 VARCHAR2, P_ATTRIBUTE7 VARCHAR2, P_ATTRIBUTE8 VARCHAR2, P_ATTRIBUTE9 VARCHAR2, P_ATTRIBUTE10 VARCHAR2, P_ATTRIBUTE11 VARCHAR2, P_ATTRIBUTE12 VARCHAR2, P_ATTRIBUTE13 VARCHAR2, P_ATTRIBUTE14 VARCHAR2, P_ATTRIBUTE15 VARCHAR2 ) return varchar2 is v_error_msg varchar2(10000); v_lease_name pn_leases_all.name%type; v_option_type_code fnd_lookups.lookup_code%type; v_option_status_lookup_code fnd_lookups.lookup_code%type; begin v_error_msg := null; v_lease_name := null; v_option_type_code := null; v_option_status_lookup_code := null; begin select pnl.name into v_lease_name from pn_leases_all pnl where 1=1 and upper(name) = upper(P_lease_name); exception when no_data_found then v_error_msg := 'Lease Name not exists'; when others then v_error_msg := ' Error at Lease Name'||SQLERRM; end; Begin select lookup_code into v_option_type_code from fnd_lookups where 1=1 and lookup_type = 'PN_LEASE_OPTION_TYPE' and upper(meaning) = upper(P_OPTION_TYPE) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Option type not exists'; when others then v_error_msg := v_error_msg||' and Error at Option Type '||SQLERRM; end; Begin select lookup_code into v_option_status_lookup_code from fnd_lookups where 1=1 and lookup_type = 'PN_OPTION_STATUS_TYPE' and upper(meaning) = upper(P_OPTION_STATUS_TYPE) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Option Status not exists'; when others then v_error_msg := v_error_msg||' and Error at Option Status'||SQLERRM; end; if v_error_msg is null then insert into APPS.XXIRON_PN_LEASE_OPTIONS_STG ( PN_LEASE_OPTIONS_ID ,LEASE_NAME ,OPTION_TYPE ,OPTION_TYPE_CODE ,OPTION_STATUS_TYPE ,OPTION_STATUS_LOOKUP_CODE ,START_DATE ,EXPIRATION_DATE ,REQUEST_ID ,PROCESS_FLAG ,ERROR_MESSAGE ,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 ) values ( null ,P_LEASE_NAME ,P_OPTION_TYPE ,v_option_type_code ,P_OPTION_STATUS_TYPE ,v_option_status_lookup_code ,P_START_DATE ,P_EXPIRATION_DATE ,null ,null ,null ,sysdate ,fnd_global.user_id ,sysdate ,fnd_global.user_id ,fnd_global.user_id ,null ,P_ATTRIBUTE1 ,P_ATTRIBUTE2 ,P_ATTRIBUTE3 ,P_ATTRIBUTE4 ,P_ATTRIBUTE5 ,P_ATTRIBUTE6 ,P_ATTRIBUTE7 ,P_ATTRIBUTE8 ,P_ATTRIBUTE9 ,P_ATTRIBUTE10 ,P_ATTRIBUTE11 ,P_ATTRIBUTE12 ,P_ATTRIBUTE13 ,P_ATTRIBUTE14 ,P_ATTRIBUTE15 ); return null; else return v_error_msg; end if; end; function xxiron_lease_notes_func( P_LEASE_NAME VARCHAR2, P_NOTE_TYPE VARCHAR2, P_NOTE_DATE DATE, P_TEXT VARCHAR2, P_ATTRIBUTE1 VARCHAR2, P_ATTRIBUTE2 VARCHAR2, P_ATTRIBUTE3 VARCHAR2, P_ATTRIBUTE4 VARCHAR2, P_ATTRIBUTE5 VARCHAR2, P_ATTRIBUTE6 VARCHAR2, P_ATTRIBUTE7 VARCHAR2, P_ATTRIBUTE8 VARCHAR2, P_ATTRIBUTE9 VARCHAR2, P_ATTRIBUTE10 VARCHAR2, P_ATTRIBUTE11 VARCHAR2, P_ATTRIBUTE12 VARCHAR2, P_ATTRIBUTE13 VARCHAR2, P_ATTRIBUTE14 VARCHAR2, P_ATTRIBUTE15 VARCHAR2 ) return varchar2 is v_error_msg varchar2(10000); v_lease_name pn_leases_all.name%type; v_note_type_code fnd_lookups.lookup_code%type; begin v_error_msg := null; v_lease_name := null; v_note_type_code := null; begin select pnl.name into v_lease_name from pn_leases_all pnl where 1=1 and upper(name) = upper(P_lease_name); exception when no_data_found then v_error_msg := 'Lease Name not exists'; when others then v_error_msg := ' Error at Lease Name'||SQLERRM; end; Begin select lookup_code into v_note_type_code from fnd_lookups where 1=1 and lookup_type = 'PN_NOTE_TYPE' and upper(meaning) = upper(P_NOTE_TYPE) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Note type not exists'; when others then v_error_msg := v_error_msg||' and Error at Note Type '||SQLERRM; end; if v_error_msg is null then insert into APPS.XXIRON_PN_LEASE_NOTES_STG ( PN_LEASE_NOTES_ID ,LEASE_NAME ,NOTE_TYPE ,NOTE_TYPE_LOOKUP_CODE ,NOTE_DATE ,TEXT ,REQUEST_ID ,PROCESS_FLAG ,ERROR_MESSAGE ,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 ) values ( null----PN_LEASE_NOTES_ID ,P_LEASE_NAME ,P_NOTE_TYPE ,v_note_type_code ,P_NOTE_DATE ,P_TEXT ,null ,null ,null ,sysdate ,fnd_global.user_id ,sysdate ,fnd_global.user_id ,fnd_global.user_id ,null ,P_ATTRIBUTE1 ,P_ATTRIBUTE2 ,P_ATTRIBUTE3 ,P_ATTRIBUTE4 ,P_ATTRIBUTE5 ,P_ATTRIBUTE6 ,P_ATTRIBUTE7 ,P_ATTRIBUTE8 ,P_ATTRIBUTE9 ,P_ATTRIBUTE10 ,P_ATTRIBUTE11 ,P_ATTRIBUTE12 ,P_ATTRIBUTE13 ,P_ATTRIBUTE14 ,P_ATTRIBUTE15 ); return null; else return v_error_msg; end if; end; function xxiron_lease_obligation_func( P_LEASE_NAME VARCHAR2, P_SERVICE_TYPE VARCHAR2, P_RESPONSIBILITY VARCHAR2, P_FINANCIAL_RESP_PARTY VARCHAR2, P_START_DATE DATE, P_END_DATE DATE, P_OBLIGATION_COMMENTS VARCHAR2, P_ATTRIBUTE1 VARCHAR2, P_ATTRIBUTE2 VARCHAR2, P_ATTRIBUTE3 VARCHAR2, P_ATTRIBUTE4 VARCHAR2, P_ATTRIBUTE5 VARCHAR2, P_ATTRIBUTE6 VARCHAR2, P_ATTRIBUTE7 VARCHAR2, P_ATTRIBUTE8 VARCHAR2, P_ATTRIBUTE9 VARCHAR2, P_ATTRIBUTE10 VARCHAR2, P_ATTRIBUTE11 VARCHAR2, P_ATTRIBUTE12 VARCHAR2, P_ATTRIBUTE13 VARCHAR2, P_ATTRIBUTE14 VARCHAR2, P_ATTRIBUTE15 VARCHAR2 ) return varchar2 is v_error_msg varchar2(10000); v_lease_name pn_leases_all.name%type; v_service_type_code fnd_lookups.lookup_code%type; v_responsibility_code fnd_lookups.lookup_code%type; v_financial_resp_code fnd_lookups.lookup_code%type; begin v_error_msg := null; v_lease_name := null; v_service_type_code := null; v_responsibility_code := null; v_financial_resp_code := null; begin select pnl.name into v_lease_name from pn_leases_all pnl where 1=1 and upper(name) = upper(P_lease_name); exception when no_data_found then v_error_msg := 'Lease Name not exists'; when others then v_error_msg := ' Error at Lease Name'||SQLERRM; end; Begin select lookup_code into v_service_type_code from fnd_lookups where 1=1 and lookup_type = 'PN_LANDLORD_SERVICE_TYPE' and upper(meaning) = upper(P_service_type) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Service type not exists'; when others then v_error_msg := v_error_msg||' and Error at Service Type '||SQLERRM; end; Begin select lookup_code into v_responsibility_code from fnd_lookups where 1=1 and lookup_type = 'PN_OBLIGATION_RESP_TYPE' and upper(meaning) = upper(P_responsibility) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Responsibility type not exists'; when others then v_error_msg := v_error_msg||' and Error at Responsibility Type '||SQLERRM; end; Begin select lookup_code into v_financial_resp_code from fnd_lookups where 1=1 and lookup_type = 'PN_OBLIGATION_FIN_RESP_PARTY' and upper(meaning) = upper(P_financial_resp_party) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Financial Responsibility type not exists'; when others then v_error_msg := v_error_msg||' and Error at Financial Responsibility Type '||SQLERRM; end; if v_error_msg is null then insert into APPS.XXIRON_PN_LEASE_OBLIGATION_STG ( PN_LEASE_OBLIGATION_ID ,LEASE_NAME ,SERVICE_TYPE ,SERVICE_TYPE_LOOKUP_CODE ,RESPONSIBILITY ,RESPONSIBILITY_CODE ,FINANCIAL_RESP_PARTY ,FINANCIAL_RESP_PARTY_CODE ,START_DATE ,END_DATE ,OBLIGATION_COMMENTS ,REQUEST_ID ,PROCESS_FLAG ,ERROR_MESSAGE ,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 ) values ( null----PN_LEASE_OBLIGATION_ID ,P_LEASE_NAME ,P_SERVICE_TYPE ,v_service_type_code--SERVICE_TYPE_LOOKUP_CODE ,P_RESPONSIBILITY ,v_responsibility_code--RESPONSIBILITY_CODE ,P_FINANCIAL_RESP_PARTY ,v_financial_resp_code--FINANCIAL_RESP_PARTY_CODE ,P_START_DATE ,P_END_DATE ,P_OBLIGATION_COMMENTS ,null ,null ,null ,sysdate ,fnd_global.user_id ,sysdate ,fnd_global.user_id ,fnd_global.user_id ,null ,P_ATTRIBUTE1 ,P_ATTRIBUTE2 ,P_ATTRIBUTE3 ,P_ATTRIBUTE4 ,P_ATTRIBUTE5 ,P_ATTRIBUTE6 ,P_ATTRIBUTE7 ,P_ATTRIBUTE8 ,P_ATTRIBUTE9 ,P_ATTRIBUTE10 ,P_ATTRIBUTE11 ,P_ATTRIBUTE12 ,P_ATTRIBUTE13 ,P_ATTRIBUTE14 ,P_ATTRIBUTE15 ); return null; else return v_error_msg; end if; end; function xxiron_lease_payments_func( P_LEASE_NAME VARCHAR2, P_TERM_TEMPLATE_NAME VARCHAR2, P_LOCATION_CODE VARCHAR2, P_FREQUENCY_TYPE VARCHAR2, P_VENDOR_NAME VARCHAR2, P_VENDOR_SITE_CODE VARCHAR2, P_ACTUAL_AMOUNT NUMBER, P_DISTRIBUTION_SET_NAME VARCHAR2, P_START_DATE DATE, P_END_DATE DATE, P_ATTRIBUTE1 VARCHAR2, P_ATTRIBUTE2 VARCHAR2, P_ATTRIBUTE3 VARCHAR2, P_ATTRIBUTE4 VARCHAR2, P_ATTRIBUTE5 VARCHAR2, P_ATTRIBUTE6 VARCHAR2, P_ATTRIBUTE7 VARCHAR2, P_ATTRIBUTE8 VARCHAR2, P_ATTRIBUTE9 VARCHAR2, P_ATTRIBUTE10 VARCHAR2, P_ATTRIBUTE11 VARCHAR2, P_ATTRIBUTE12 VARCHAR2, P_ATTRIBUTE13 VARCHAR2, P_ATTRIBUTE14 VARCHAR2, P_ATTRIBUTE15 VARCHAR2 ) return varchar2 is v_error_msg varchar2(10000); v_lease_name pn_leases_all.name%type; v_service_type_code fnd_lookups.lookup_code%type; v_responsibility_code fnd_lookups.lookup_code%type; v_financial_resp_code fnd_lookups.lookup_code%type; v_template_name pn_term_templates_all.name%type; v_DISTRIBUTION_SET_ID number; v_vendor_id number; v_vendor_site_id number; v_Frequency_code fnd_lookups.lookup_code%type; begin v_error_msg := null; v_lease_name := null; v_template_name := null; v_DISTRIBUTION_SET_ID := null; v_vendor_id := null; v_vendor_site_id := null; v_Frequency_code := null; begin select pnl.name into v_lease_name from pn_leases_all pnl where 1=1 and upper(name) = upper(P_lease_name); exception when no_data_found then v_error_msg := 'Lease Name not exists'; when others then v_error_msg := ' Error at Lease Name'||SQLERRM; end; Begin select name,DISTRIBUTION_SET_ID,vendor_id,vendor_site_id into v_template_name,v_DISTRIBUTION_SET_ID,v_vendor_id,v_vendor_site_id from pn_term_templates_all where 1=1 and upper(name) = upper(P_TERM_TEMPLATE_NAME); exception when no_data_found then v_error_msg := v_error_msg||' Payment term template not exists'; when others then v_error_msg := v_error_msg||' and Error at Payment term template '||SQLERRM; end; Begin select lookup_code into v_Frequency_code from fnd_lookups where 1=1 and lookup_type = 'PN_PAYMENT_FREQUENCY_TYPE' and upper(meaning) = upper(P_FREQUENCY_type) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Frequency type not exists'; when others then v_error_msg := v_error_msg||' and Error at Frequency Type '||SQLERRM; end; if v_error_msg is null then insert into APPS.XXIRON_PN_LEASE_PAYMENTS_STG ( PN_LEASE_PAYMENT_ID ,LEASE_NAME ,TERM_TEMPLATE_NAME ,LOCATION_CODE ,FREQUENCY_TYPE ,FREQUENCY_CODE ,VENDOR_NAME ,VENDOR_SITE_CODE ,ACTUAL_AMOUNT ,DISTRIBUTION_SET_NAME ,DISTRIBUTION_SET_ID ,START_DATE ,END_DATE ,REQUEST_ID ,PROCESS_FLAG ,ERROR_MESSAGE ,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 ) values ( null----PN_LEASE_PAYMENT_ID ,P_LEASE_NAME ,P_TERM_TEMPLATE_NAME ,P_LOCATION_CODE ,P_FREQUENCY_TYPE ,v_Frequency_code--FREQUENCY_CODE ,P_VENDOR_NAME ,P_VENDOR_SITE_CODE ,P_ACTUAL_AMOUNT ,P_DISTRIBUTION_SET_NAME ,v_DISTRIBUTION_SET_ID---DISTRIBUTION_SET_ID ,P_START_DATE ,P_END_DATE ,null ,null ,null ,sysdate ,fnd_global.user_id ,sysdate ,fnd_global.user_id ,fnd_global.user_id ,null ,P_ATTRIBUTE1 ,P_ATTRIBUTE2 ,P_ATTRIBUTE3 ,P_ATTRIBUTE4 ,P_ATTRIBUTE5 ,P_ATTRIBUTE6 ,P_ATTRIBUTE7 ,P_ATTRIBUTE8 ,P_ATTRIBUTE9 ,P_ATTRIBUTE10 ,P_ATTRIBUTE11 ,P_ATTRIBUTE12 ,P_ATTRIBUTE13 ,P_ATTRIBUTE14 ,P_ATTRIBUTE15 ); return null; else return v_error_msg; end if; end; function xxiron_lease_insurance_func( P_LEASE_NAME VARCHAR2, P_INSURANCE_TYPE VARCHAR2, P_POLICY_START_DATE DATE, P_POLICY_EXPIRATION_DATE DATE, P_INSURER_NAME VARCHAR2, P_POLICY_NUMBER VARCHAR2, P_INSURED_AMOUNT NUMBER, P_REQUIRED_AMOUNT NUMBER, P_INSURANCE_COMMENTS VARCHAR2, P_ATTRIBUTE1 VARCHAR2, P_ATTRIBUTE2 VARCHAR2, P_ATTRIBUTE3 VARCHAR2, P_ATTRIBUTE4 VARCHAR2, P_ATTRIBUTE5 VARCHAR2, P_ATTRIBUTE6 VARCHAR2, P_ATTRIBUTE7 VARCHAR2, P_ATTRIBUTE8 VARCHAR2, P_ATTRIBUTE9 VARCHAR2, P_ATTRIBUTE10 VARCHAR2, P_ATTRIBUTE11 VARCHAR2, P_ATTRIBUTE12 VARCHAR2, P_ATTRIBUTE13 VARCHAR2, P_ATTRIBUTE14 VARCHAR2, P_ATTRIBUTE15 VARCHAR2 ) return varchar2 is v_error_msg varchar2(10000); v_lease_name pn_leases_all.name%type; v_INSURANCE_code fnd_lookups.lookup_code%type; begin v_error_msg := null; v_lease_name := null; v_INSURANCE_code := null; begin select pnl.name into v_lease_name from pn_leases_all pnl where 1=1 and upper(name) = upper(P_lease_name); exception when no_data_found then v_error_msg := 'Lease Name not exists'; when others then v_error_msg := ' Error at Lease Name'||SQLERRM; end; Begin select lookup_code into v_INSURANCE_code from fnd_lookups where 1=1 and lookup_type = 'PN_INSURANCE_TYPE' and upper(meaning) = upper(P_INSURANCE_TYPE) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Insurance type not exists'; when others then v_error_msg := v_error_msg||' and Error at Insurance Type '||SQLERRM; end; if v_error_msg is null then insert into APPS.XXIRON_LEASE_INSURANCE_STG ( PN_INSURANCE_ID , LEASE_NAME ,INSURANCE_TYPE ,INSURANCE_TYPE_CODE ,POLICY_START_DATE ,POLICY_EXPIRATION_DATE ,INSURER_NAME ,POLICY_NUMBER ,INSURED_AMOUNT ,REQUIRED_AMOUNT ,INSURANCE_COMMENTS ,REQUEST_ID ,PROCESS_FLAG ,ERROR_MESSAGE ,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 ) values ( null----PN_INSURANCE_ID , P_LEASE_NAME ,P_INSURANCE_TYPE ,v_INSURANCE_code ,P_POLICY_START_DATE ,P_POLICY_EXPIRATION_DATE ,P_INSURER_NAME ,P_POLICY_NUMBER ,P_INSURED_AMOUNT ,P_REQUIRED_AMOUNT ,P_INSURANCE_COMMENTS ,null ,null ,null ,sysdate ,fnd_global.user_id ,sysdate ,fnd_global.user_id ,fnd_global.user_id ,null ,P_ATTRIBUTE1 ,P_ATTRIBUTE2 ,P_ATTRIBUTE3 ,P_ATTRIBUTE4 ,P_ATTRIBUTE5 ,P_ATTRIBUTE6 ,P_ATTRIBUTE7 ,P_ATTRIBUTE8 ,P_ATTRIBUTE9 ,P_ATTRIBUTE10 ,P_ATTRIBUTE11 ,P_ATTRIBUTE12 ,P_ATTRIBUTE13 ,P_ATTRIBUTE14 ,P_ATTRIBUTE15 ); return null; else return v_error_msg; end if; end; function xxiron_lease_contacts_func( P_LEASE_NAME VARCHAR2, P_LEASE_ROLE VARCHAR2 , P_COMPANY_NAME VARCHAR2, P_COMPANY_SITE_NAME VARCHAR2, P_LOCATION_CODE VARCHAR2, P_ATTRIBUTE1 VARCHAR2, P_ATTRIBUTE2 VARCHAR2, P_ATTRIBUTE3 VARCHAR2, P_ATTRIBUTE4 VARCHAR2, P_ATTRIBUTE5 VARCHAR2, P_ATTRIBUTE6 VARCHAR2, P_ATTRIBUTE7 VARCHAR2, P_ATTRIBUTE8 VARCHAR2, P_ATTRIBUTE9 VARCHAR2, P_ATTRIBUTE10 VARCHAR2, P_ATTRIBUTE11 VARCHAR2, P_ATTRIBUTE12 VARCHAR2, P_ATTRIBUTE13 VARCHAR2, P_ATTRIBUTE14 VARCHAR2, P_ATTRIBUTE15 VARCHAR2 ) return varchar2 is v_error_msg varchar2(10000); v_lease_name pn_leases_all.name%type; v_role_code fnd_lookups.lookup_code%type; v_location_id number; v_company_site_id number; v_company_id number; begin v_error_msg := null; v_lease_name := null; v_role_code := null; v_location_id := null; v_company_site_id := null; v_company_id := null; begin select pnl.name into v_lease_name from pn_leases_all pnl where 1=1 and upper(name) = upper(P_lease_name); exception when no_data_found then v_error_msg := 'Lease Name not exists'; when others then v_error_msg := ' Error at Lease Name'||SQLERRM; end; if p_location_code is not null then begin select location_id into v_location_id from pn_locations_all where 1=1 and upper(location_code) = upper(p_location_code); exception when no_data_found then v_error_msg := v_error_msg||' Location not exists'; when others then v_error_msg := v_error_msg||' and Location code'||SQLERRM; end; end if; Begin select lookup_code into v_role_code from fnd_lookups where 1=1 and lookup_type = 'PN_LEASE_ROLE_TYPE' and upper(meaning) = upper(P_LEASE_ROLE) and enabled_flag = 'Y' and end_date_active is null; exception when no_data_found then v_error_msg := v_error_msg||' Lease Role Type not exists'; when others then v_error_msg := v_error_msg||' and Error at Lease Role Type '||SQLERRM; end; Begin select company_id into v_company_id from pn_companies_all where 1=1 and enabled_flag = 'Y' and upper(name) =upper(P_COMPANY_NAME); exception when no_data_found then v_error_msg := v_error_msg||' Company Name not exists'; when others then v_error_msg := v_error_msg||' and Error at Company Name '||SQLERRM; end; Begin select company_site_id into v_company_site_id from pn_company_sites_all where 1=1 and lease_role_type = v_role_code and enabled_flag = 'Y' and upper(name) =upper(P_COMPANY_SITE_NAME) and company_id = v_company_id; exception when no_data_found then v_error_msg := v_error_msg||' Company Site Name not exists'; when others then v_error_msg := v_error_msg||' and Error at Company Site Name '||SQLERRM; end; if v_error_msg is null then insert into APPS.XXIRON_LEASE_CONTACTS_STG ( PN_CONTACT_ID , LEASE_NAME , LEASE_ROLE ,ROLE_CODE ,COMPANY_NAME ,COMPANY_SITE_NAME ,LOCATION_CODE ,REQUEST_ID ,PROCESS_FLAG ,ERROR_MESSAGE ,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 ) values ( null , P_LEASE_NAME , P_LEASE_ROLE ,V_ROLE_CODE ,P_COMPANY_NAME ,P_COMPANY_SITE_NAME ,P_LOCATION_CODE ,null ,null ,null ,sysdate ,fnd_global.user_id ,sysdate ,fnd_global.user_id ,fnd_global.user_id ,null ,P_ATTRIBUTE1 ,P_ATTRIBUTE2 ,P_ATTRIBUTE3 ,P_ATTRIBUTE4 ,P_ATTRIBUTE5 ,P_ATTRIBUTE6 ,P_ATTRIBUTE7 ,P_ATTRIBUTE8 ,P_ATTRIBUTE9 ,P_ATTRIBUTE10 ,P_ATTRIBUTE11 ,P_ATTRIBUTE12 ,P_ATTRIBUTE13 ,P_ATTRIBUTE14 ,P_ATTRIBUTE15 ); return null; else return v_error_msg; end if; end; end xxiron_pn_lease_pkg;