Thursday, February 9, 2012

Oracle Property Manager Conversion


Oracle Property Manager Conversion
Oracle Property Manager provides an effective tool to manage major real estate tasks, including lease administration and office space allocation. It also produces reports in a variety of formats that present records, depending upon the business needs.
Property Manager automatically generates payment or billing schedules for each lease you define, normalizing the payment or billing
stream in accordance with GAAP (Generally Accepted Accounting
Principles).
Property manager helps to maintain following information related to the business
Payment terms
Billing terms
Options
Key contacts for vendors or service providers
Landlord services
Insurance requirements
It is integrated with other Oracle applications like Payables and billings generated can be exported in Payables Application.

Conversion approach

During this Conversion the approach followed was standard three Stage approach
1.    Insertion of Data into staging table from legacy Tables
2.    Validation of Data
3.    Insertion of Data into Oracle tables using Standard Insert Packages.
Creation of Schedules and Items

The conversion was carried out for creation of Prime and Sub leases with lease status as ‘Active’ and approval status as ‘Final’. Also it was required to generate the Payment Schedule and Items for the leases.
Different Programs with similar conversion approach were used for conversion of Sublease and Prime Leases as different details were required to be populated.

The Conversion was done to populate following details in the leases
1.    Lease Header
2.    Lease Details
3.    Milestones
4.    Party Roles
5.    Contacts
6.    Rights and Obligations
7.    Options
8.    Payments/Billings and its Account Distribution
9.    Notes

Following is the detailed approach for the Property Manager Conversion.
1.    Insertion:
First the Data from legacy system was put into the Staging Tables. Different Tables were used to populate different details of the Leases. The different staging tables that were created as a part of the Conversion Process are
a)    Header staging table: Data for creating Lease Header and Lease Details was stored in this table.
b)    Payments staging table: Data for creating Payments/Billings was stored in this table. The Payments/Billings account Distribution and Payments Milestones was also created using the data from this table.
c)    Other Charges staging table: The Data for creating other charges payment streams along with the account Distribution was stored in this table.
d)    Comments staging table: Data for Lease notes Creation.
e)    Options staging table: Data for Creation of Renewal and Original terms Option along with Options Milestones.
f)    Lease Contacts staging table

2.    Columns such as Process status, Error flag and Error description were created in the staging table. The value in the column Process status indicates the
     stage at which data is being processed by the Conversion program. Following are   
     the Process status values that were followed during this conversion Process
a.    ‘1’ – Insertion of Data into Staging Tables
b.    ‘2’ – Validation
c.    ‘3’ – API insertion into the Base Tables
d.    ‘4’ -  Completion of Processing
    The columns process status, error flag and error description were updated        
    whenever the errors were encountered for the record at different stages of
    processing.
3.    Validation:
Once the Data was inserted into the Staging tables the process status was updated to ‘1’. Various validations such as Business specific validations, Mandatory values, etc...  were performed on the Data.
For validation only those Header records and corresponding Payments, Milestones, Options and Notes records which have error flag as ‘N’ were picked and processed. Then the next header and corresponding records from other staging tables were picked and validated.
If the record is validated successfully the process status was updated to ‘2’. Incase the record failed validation the process status was updated to ‘2’, also the error flag and error description columns for the record were updated for the specific validation failure Message.
4.    API’S insertion into Base Tables:
Those Lease header records which are validated successfully were picked for further processing. Corresponding to Header record, Payments, Milestones, Options and Notes records which have error flag  as ‘N’ were picked and using the Standard Insert Packages the data was inserted into the Oracle Base tables. Incase of any errors the insertion for that particular Lease record was roll back and then record was updated as process status ‘3’ along with error flag and error description.
For all successfully inserted records the process status was updates as ‘4’.
5.  Schedules and Items:
Once the Lease record was created successfully the ‘Schedules and Items’ program was submitted with lease context as 'ABS' to finalize the Lease. This Program finalizes the Lease and creates the Payment Schedules and items for that lease.

Setups for Property Manager Conversion
Some of the important setups required for this conversion are
1)    Define the Lookup Codes:
According to the Specific Business requirement various custom lookup code and meaning should be defined in the Standard Lookups.

Responsibility: Property Manager User
Setup>Options>Lookups

Some of the Important Lookups that needs to be setup are
a)    PN_LEASE_CLASS:
This lookups store the Classes for Leases such as Direct, Sub lease, etc…
b)    PN_MILESTONES_TYPE:
Lookup code for Milestones type is defined in this lookup.
c)    PN_LEASE_OPTION_TYPE:
This lookup stores the Option types that can be created for the Lease.
d)    PN_NOTE_TYPE:
This lookup store the different Note types that can be created for the Lease.
e)    PN_PAYMENT_PURPOSE_TYPE:
Define any custom Payment types in this Lookup.
f)    PN_PAYMENT_TERM_TYPE:
Define any custom Payment term types in this Lookup.
g)    PN_TENANCY_USAGE_TYPE:
This Lookup is used to define any custom Usage type for the Locations in the Lease.
h)    PN_RIGHTS_TYPE:
                   Define any custom Right types that will be created for the Leases.


2)    Define Locations:
Locations for which lease is to be created needs to be defined in this screen. The name of Location was defined as Profit center number (Store Number) for the store and class of Location was defined as ‘Store’.
The Tenure needs to be defined as ‘Leased’. Address of the Store was defined as the address of the Location. The Start and End date of the locations defines the period for which the location will be occupied during the lease agreement.

Responsibility: Property Manager User
Assignments>Define Locations

3)    Define Contacts:
The Contacts for each company was defined in this screen. A single company can have number of roles and each role can have different contact person all these data can be entered here. The details such as Contact Roles, Site address, Telephone number and Contact names were defined.

Responsibility: Property Manager User
Agents>Contacts



APIs used for Conversion:
Standard Insert packages were used for the conversion. The details of the API’S used are given below in the sequence which they were used in the Conversion Program.

1.    Lease Header and Details API
This API creates the Lease Header and Details.
pn_leases_pkg.insert_row
Tables populated:
          PN_LEASES
          PN_LEASE_DETAILS
          PN_LEASE_CHANGES
      
     Parameters for API:
         pn_leases_pkg.insert_row
                  (          /*Output Parameter for Row Id*/
                             x_rowid                                               => v_chr_rowid
                  
                             /*Output Parameter for lease Id*/
                  ,x_lease_id                                           => v_num_lease_id

                   /*Output Parameter for lease change Id*/
                  ,x_lease_change_id                                => v_num_lease_ch_id
                 
                  /*Output parameter for Lease Detail id*/
                  ,x_lease_detail_id                                  => v_num_detail_id
                  
                  /* Input the Lease Name*/
                  ,x_name                                              => 'Test1'
                 
                  /* Input Lease number*/
                  ,x_lease_num                                       => v_chr_lease_num
                  ,x_parent_lease_id                                 => NULL
                 
                             /*Input lease type and class code set in the above lookups*/
                  ,x_lease_type_code                               => v_chr_lease_code
                  ,x_lease_class_code                               => v_chr_lease_class
                  
                    /*Input the Proration rule Value*/
,x_payment_term_proration_rule              => 365
                   ,x_abstracted_by_user                           => g_num_user_id
                  
/*Input approval status as ‘DRAFT’. Initially lease will be created in ‘DRAFT’ status and the Schedules and Items Program will finalize the Lease*/
                  ,x_status                                               => 'D'          --DRAFT
                  ,x_lease_status                                                => ‘ACT’ –-Active Status
                  
                  /*WHO Columns*/
                  ,x_creation_date                                     => g_dte_sysdate
                  ,x_created_by                                        => g_num_user_id
                  ,x_last_update_date                                => g_dte_sysdate
                  ,x_last_updated_by                                => g_num_user_id
                  ,x_last_update_login                                => g_num_login_id
                  ,x_responsible_user                                => g_num_user_id
                 
                  ,x_expense_account_id                            => NULL
                  ,x_accrual_account_id                                       => NULL
                  ,x_receivable_account_id                          => NULL
                  ,x_term_template_id                               => NULL
                   


                  /*Input Commencement, Termination and Execution Date for Lease*/   
                  ,x_lease_commencement_date                  => v_dte_ls_com_dte
                  ,x_lease_termination_date                        => v_dte_ls_ter_dte
                  ,x_lease_execution_date                          => c_xshs_rec.lease_date
                  
                   /*Pass the values of any Attribute Columns*/
                  ,x_attribute_category                              => NULL
                  ,x_attribute1                                          => v_dte_begin_date
                  ,x_attribute2                                          => v_dte_at_end_date
                  ,x_attribute3                                          => NULL
                  ,x_attribute4                                          => NULL                       
                  ,x_attribute5                                          => NULL
                  ,x_attribute6                                          => c_xshs_rec.rent_deposit
                  ,x_attribute7                                          => v_chr_auto_lye
                  ,x_attribute8                                          => v_chr_rent_invoice
                  ,x_attribute9                                          => v_chr_spl_terms
                  ,x_attribute10                                                  => corp_number
                  ,x_attribute11                                                  => v_chr_escrow
                  ,x_attribute12                                                  => NULL
                  ,x_attribute13                                                  => NULL
                  ,x_attribute14                                                  => NULL
                  ,x_attribute15                                                  => NULL
                    
                  ,x_org_id                                              => g_num_org_id
                   
/*Input Location Id from PN_LOCATIONS for the Profit Center (Store Number) and Customer Id for which the is to be created*/
                  ,x_location_id                                         => v_num_location_id
                  ,x_customer_id                                      => v_num_customer_id
                    
                  ,x_grouping_rule_id                                 => NULL
                  );

This API returns the Lease Id, Lease details Id and Lease change Id as the output parameters.

2.    Locations API’s:
This API populates the Location Details for the Lease. This Location was created for the Profit Center (Store) which is defined as Location in PN_LOCATIONS.
pn_tenancies_pkg.insert_row
Tables populated:
     PN_TENANCIES

Parameters for API:
          pn_tenancies_pkg.insert_row
       ( /*Ouput row id*/
          x_rowid                                               => v_chr_row_id1
       
        /*Output Tenancy Id*/
                   ,x_tenancy_id                                       => v_num_tenancy_id
                  
                   /*Input Location Id for Profit Center*/
                   ,x_location_id                                        => v_num_location_id
                   
 /*Input the Lease Id and Lease Change Id from the Lease Header API*/                
                     ,x_lease_id                                         => v_num_lease_id
                     ,x_lease_change_id                                        => v_num_lease_ch_id
                     
                     /*Input Property Class Lookup Code*/
                   ,x_tenancy_usage_lookup_code                => property_class
                         
                   ,x_primary_flag                                    => 'N'
                    
                    /*Inpute the Lease Commencement and Termination Date*/     
                    ,x_estimated_occupancy_date                 => v_dte_ls_com_dte
                    ,x_expiration_date                                => v_dte_ls_ter_dte
                        
                   ,x_occupancy_date                                => NULL
                        ,x_assignable_flag                                 => NULL
                        ,x_subleaseable_flag                              => NULL
              ,x_tenants_proportionate_share               => 100
,x_status                                                       => 'A' –  Active Status

/*Input Attribute Values*/
                    ,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

                  /*WHO columns*/
             ,x_creation_date                                   => g_dte_sysdate
               ,x_created_by                                      => g_num_user_id
               ,x_last_update_date                              => g_dte_sysdate
               ,x_last_updated_by                               => g_num_user_id
               ,x_last_update_login                                        => g_num_login_id
               ,x_org_id                                                      => g_num_org_id

/*This is the output parameter which gives the warning Message if the Location is used by some other lease with in same tenure. */
,x_tenancy_ovelap_wrn                          => v_chr_tenancy_wrn

,x_recovery_type_code                          => NULL
,x_recovery_space_std_code                   => NULL
,x_fin_oblig_end_date                             => NULL
,x_customer_id                                     => NULL
,x_customer_site_use_id                         => NULL
,x_lease_rentable_area                           => NULL
,x_lease_usable_area                             => NULL
,x_lease_assignable_area                        => NULL
,x_lease_load_factor                              => NULL
,x_location_rentable_area                       => NULL
,x_location_usable_area                          => NULL
,x_location_assignable_area           => NULL
,x_location_load_factor                           => NULL
);
This API returns tenancy id for the location.

3.    Options API’s:
This API’s was used to create the various Options such as Renewal, Original terms for the Lease.
pn_options_pkg.insert_row

Tables populated:
     PN_OPTIONS

Parameters for API:
            /*Creation of Lease Orignal Terms for Header*/
               pn_options_pkg.insert_row(
                    /*Output parameter Row id*/
                   x_rowid                                               => v_chr_row_id2
                    
                   /*Output parameters Option Id and Option Number*/
                             ,x_option_id                                          => v_num_option_id
                             ,x_option_num                                      => v_chr_option_num

                   /*Input parameter Lease Id and Lease Change Id*/
                             ,x_lease_id                                           => v_num_lease_id
                             ,x_lease_change_id                                => v_num_lease_ch_id

                   /*Input Option type Code from ‘PN_LEASE_OPTION_TYPE’ */
                             ,x_option_type_code                     => v_chr_option_type_code
                            
                   /*Start date and End Date*/
                    ,x_start_date                                       => c_xshs_rec.begin_date
                             ,x_expiration_date                                 => c_xshs_rec.end_date1
                    ,x_option_size                                      => NULL
                    ,x_uom_code                                       => NULL

                   /*Input Option status lookup from ‘PN_OPTION_STATUS_TYPE’*/
,x_option_status_lookup_code                  => v_chr_option_status_code

                   /*Input attributes*/
,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                
                   /*WHO columns*/
,x_creation_date                                   => g_dte_sysdate
,x_created_by                                       => g_num_user_id
,x_last_update_date                               => g_dte_sysdate
,x_last_updated_by                                => g_num_user_id
,x_last_update_login                     => g_num_login_id

,x_option_exer_start_date                       => NULL
,x_option_exer_end_date                        => NULL
,x_option_action_date                             => NULL
,x_option_cost                                      => NULL
,x_option_area_change                           => NULL
,x_option_reference                              => NULL
,x_option_notice_reqd                            => NULL
,x_option_comments                              => NULL
,x_org_id                                             => g_num_org_id                           );
          This API returns the Option id and Option Number for the lease.

4.    Rights API’s:
This API’s was used to create the type of Rights such as Buyout, Purchase, etc... for the Lease.
PN_RIGHTS_PKG.Insert_Row

Tables populated:
     PN_RIGHTS

Parameters for API:
    PN_RIGHTS_PKG.Insert_Row
                     (
                   /*Output Parameter for Row id*/
                  x_rowid                                                =>    v_chr_row_id8,

                   /*Output Parameter for Right Id and Right Number*/
                             x_right_id                                             =>    v_num_right_id,
                             x_right_num                                         =>    v_num_right_num,

/*Input Parameter for Lease and Lease Change ID for the Lease Header*/
                     x_lease_id                                          =>    v_num_lease_id,
                     x_lease_change_id                               =>    v_num_lease_ch_id,

                  /*Input Parameter for Right type code from ‘PN_RIGHTS-TYPE*/
x_right_type_code                                 =>    v_chr_right_type_code,
                   
                   /*Input Parameter for Right Status code*/ 
                      x_right_status_code                                      =>    'N',

                      x_right_reference                               =>    NULL,
                  
/*Input Lease Commencement Date and Termination Date as start and End date for the lease*/
                      x_start_date                                      =>    v_dte_ls_com_dte,
                      x_expiration_date                               =>    v_dte_ls_ter_dte,
                  
                      x_right_comments                                        =>    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,
                  
                   /*WHO Columns*/
                      x_creation_date                                  =>    g_dte_sysdate,
                      x_created_by                                     =>    g_num_user_id,
                      x_last_update_date                                       =>    g_dte_sysdate,
                      x_last_updated_by                                        =>    g_num_user_id,
                      x_last_update_login                             =>    g_num_login_id,
                      x_org_id                                           =>    g_num_org_id
                      );
          This API returns the Right Id and Right Number for the Lease.

5.    Contacts API’s:
This API’s was used to create the type of Rights such as Buyout, Purchase, etc... for the Lease.
PN_CONTACT_ASSIGNMENTS_PKG.Insert_Row

Tables populated:
     PN_CONTACT_ASSIGNMENTS

Parameters for API:
PN_CONTACT_ASSIGNMENTS_PKG.Insert_Row(
                   /*Output Parameter for Row id*/
x_rowid                                               =>    v_chr_row_id7,

/*Ouput parameter for Contact assignment Id*/
x_contact_assignment_id                        =>    v_num_cnt_assig_id,

          /*WHO Columns*/
          x_last_update_date                                =>    g_dte_sysdate,
          x_last_updated_by                                 =>    g_num_user_id,
          x_creation_date                                    =>    g_dte_sysdate,
          x_created_by                                        =>    g_num_user_id,
          x_last_update_login                                =>    g_num_login_id,

/*Input the Company Id and Company Site Id for the Contact defined in the Contacts screen*/
          x_company_id                                       =>    v_num_company_id,
          x_company_site_id                                =>    v_num_comp_site_id,

          /*Input lease Header Id and change Id*/
          x_lease_id                                            =>    v_num_lease_id,
          x_lease_change_id                                 =>    v_num_lease_ch_id,

          x_location_id                                         =>    NULL,
          x_status                                              =>    'A', --Active Status
          /*Attribute Columns*/
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_num_org_id
          );

This API returns the Contact assignment Id for the Lease as output parameter.

6.    Payment/Billings and Account Distribution  API’s:
This API’s was used to create the Billing streams for Sublease and Payment streams for Prime Lease. In order to create the Account Distribution for the Payment stream pn_distributions_pkg.insert_row was used.
First payment streams were created for each lease by calling the Payments API.
Then the Account distribution was created for the Payment stream calling the Distribution API for each account entry. Payment streams were created for Regular and Other Charges Payments.

pnt_payment_terms_pkg.insert_row

Tables populated:
     PN_PAYMENT_TERMS

Parameters for API:
     pnt_payment_terms_pkg.insert_row (
/*Output parameter for Row Id*/
x_rowid                                      => v_chr_row_id6

/*Output Parameter for Payment Term Id*/
,x_payment_term_id                    => v_num_payment_term_id

/*Input Billing Purpose Code and the Billing term*/
,x_payment_purpose_code            => v_chr_billing_purpose
,x_payment_term_type_code         => v_chr_billing_term

/*Input the frequency as Montly, One time, etc... */
,x_frequency_code                       => v_chr_billing_frequency

/*Input parameter Lease Id and lease Change Id*/
,x_lease_id                                 => v_num_lease_id
,x_lease_change_id                      => v_num_lease_ch_id


/*Input Start and End date for the Payment Stream*/
,x_start_date                              => v_dte_mon_start_date
,x_end_date                                => v_dte_mon_end_date

,x_vendor_id                               => NULL
,x_vendor_site_id                         => NULL
,x_customer_id                            => v_num_customer_id
,x_customer_site_use_id               => v_num_site_use_id
,x_target_date                            => NULL

/*Input the Amount for Payment Stream*/
,x_actual_amount                        => c_xsos_rec.payment_amount

,x_estimated_amount                   => NULL

/*Input Set of Books and Currency*/
,x_set_of_books_id                      => g_num_set_books_id
,x_currency_code                        => g_chr_currency
,x_rate                                      => 1

/*Input Normalize flag value as ‘Y’ if the payment stream needs to be Normalized*/
,x_normalize                               =>’Y’

,x_location_id                              => NULL

/*Input Special terms and Customer ship to Site*/
,x_schedule_day                          => v_chr_special_terms
,x_cust_ship_site_id                      => v_num_cust_ship_site

/*Input the term Id from the table RA_TERMS and transaction Id from RA_CUST_TRX_TYPES*/
,x_ap_ar_term_id                        => v_num_term_id
,x_cust_trx_type_id                      => v_num_trx_type_id

,x_project_id                               => NULL
,x_task_id                                  => NULL
,x_organization_id                        => NULL
,x_expenditure_type                     => NULL
,x_expenditure_item_date             => NULL
,x_tax_group_id                           => NULL

/*Input the tax Code Id from the table AR_VAT_TAX*/
,x_tax_code_id                            => v_num_tax_code_id
,x_tax_included                           => NULL
,x_distribution_set_id                    => NULL
,x_inv_rule_id                              => NULL
,x_account_rule_id                       => NULL
,x_salesrep_id                             => NULL
,x_approved_by                           => NULL
,x_status                                     => NULL
,x_index_period_id                       => NULL
,x_index_term_indicator                => NULL
,x_po_header_id                          => NULL
,x_cust_po_number                      => NULL
,x_receipt_method_id                   => NULL
,x_var_rent_inv_id                       => NULL
,x_var_rent_type                         => NULL
,x_period_billrec_id                       => NULL
,x_rec_agr_line_id                        => NULL
,x_amount_type                          => NULL
,x_changed_flag                          => NULL
,x_term_template_id                    => 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_project_attribute_category        => NULL
,x_project_attribute1                    => NULL
,x_project_attribute2                    => NULL
,x_project_attribute3                    => NULL
,x_project_attribute4                    => NULL
,x_project_attribute5                    => NULL
,x_project_attribute6                    => NULL
,x_project_attribute7                    => NULL
,x_project_attribute8                    => NULL
,x_project_attribute9                    => NULL
,x_project_attribute10                  => NULL
,x_project_attribute11                  => NULL
,x_project_attribute12                  => NULL
,x_project_attribute13                  => NULL
,x_project_attribute14                  => NULL
,x_project_attribute15                  => NULL

/*WHO Columns*/
,x_creation_date                          => g_dte_sysdate
,x_created_by                             => g_num_user_id
,x_last_update_date                     => g_dte_sysdate
,x_last_updated_by                      => g_num_user_id
,x_last_update_login           => g_num_login_id

/*Input Calling form value*/
,x_calling_form                            => 'PNTLEASE'
,x_org_id                                    => g_num_org_id
,x_lease_status                           => 'ACT'  --Active Status
,x_recoverable_flag                      => NULL
,x_area_type_code                       => NULL
,x_area                                               => NULL
,x_grouping_rule_id                      => NULL
);
This API returns the Payment term Id which will be used for creating the Account Distribution and Milestones for the Payments.

pn_distributions_pkg.insert_row
Call this API with different Account Classes for a Payment stream in order to create multiple Account distribution.

Tables populated:
     PN_DISTRIBUTIONS


      Parameters for API:
          pn_distributions_pkg.insert_row (
                  
                   /*Output Parameter for Rowid*/
                    x_rowid                                              => v_chr_row_id

                   /*Output Parameter for Distribution Id*/
,x_distribution_id                          => v_num_dist_id

/*Input Account Id from the table GL_CODE_COMBINATIONS*/
,x_account_id                              => v_num_account_id

/*Input Payment term for which account Distribution is to be created*/
,x_payment_term_id                    => in_num_payment_term_id
,x_term_template_id                    => NULL

/*Input Account Class such as ‘Revenue’*/
,x_account_class                          => in_chr_account_class

/*Input Percentage Distribution for each account*/
,x_percentage                             => 100

/*Output Line Number*/
,x_line_number                            => v_num_line_number

/*WHO Columns*/
,x_last_update_date                     => g_dte_sysdate
,x_last_updated_by                      => g_num_user_id
,x_creation_date                          => g_dte_sysdate
,x_created_by                             => g_num_user_id
,x_last_update_login           => g_num_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_num_org_id
);

7.    Milestones API’s:
This API’s can be used to create the Milestones for any of the following
a.    Lease Payment streams
b.    Lease Details
c.    Lease Options
d.    Insurance
    Depending upon the Parameters passed to this API, it will create the Milestone for
that part of the lease. For example: to create the Milestone for Payment Stream pass the value of the parameter payment_term_id and pass the ‘NULL’ to Option id and Insurance requirement id.

pn_lease_milestones_pkg.insert_row
Tables populated:
     PN_LEASE_MILESTONES

Parameters for API:
pn_lease_milestones_pkg.insert_row(
          /*Output Parameter for Row Id*/
x_rowid                                      => v_chr_row_id4

/*Output Milestone Id*/
,x_lease_milestone_id                   => v_num_milestone_id

/*Input lease Id and Lease Change Id*/
,x_lease_change_id                      => v_num_lease_ch_id
,x_lease_id                                 => v_num_lease_id

/*Input the Milestone type code from lookup ‘PN_MILESTONES_TYPE’*/
,x_milestone_type_code                => v_chr_ml_type_code

/*Input Option Id If the Milestone is to be created for Option otherwise pass it as NULL*/
,x_option_id                                => v_num_option_id

/*Input insurance requirement Id If the Milestone is to be created for Insurance otherwise pass it as NULL*/
,x_insurance_requirement_id          => NULL

/*Input Payment term Id If the Milestone is to be created for Payment term otherwise pass it as NULL*/
,x_payment_term_id                    => NULL


/*Input Lead days and Every Days value*/
,x_lead_days                               => 185
,x_every_days                            => 90
,x_action_taken                           => NULL
,x_action_date                             => NULL
,x_attribute_category                   => v_chr_ml_type_code

/*Input the Milestone Message in the corresponding Attribute*/
,x_attribute1                               => 'Test Renewal'
,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

/*Input the Milestone Date*/
,x_milestone_date                        => c_xsrs_rec.renewal_ntce_date

/*Input the WHO Columns*/
,x_user_id                                  => v_num_user_id
,x_creation_date                          => g_dte_sysdate
,x_created_by                             => g_num_user_id
,x_last_update_date                     => g_dte_sysdate
,x_last_updated_by                      => g_num_user_id
,x_last_update_login           => g_num_login_id
,x_org_id                                    => g_num_org_id
);

Schedules and Items Program:
Once the Lease and its Payment terms are created the next step will be to submit the Concurrent Program ‘Schedules and Items’. This Program will perform following things
a.    Create entries into Payment Schedules table(PN_PAYMENT_SCHEDULES) for each Payment term of the Lease
b.    Create Entries into Payment Items Table(PN_PAYMENT_ITEMS)for each Payment Schedule.
c.    Finalize the Lease. The Program will finalize the Lease if the Lease context is passed as ‘ABS’.
The same program can be submitted  for Renewing the Lease or Extending the Lease
by passing different Lease Context.

The Payment Schedules generated is viewed in

Responsibility: Property Manager User
Payments>Authorize

Click on Details tab to check the Payment Items for each Payment Schedule.

This Payment Schedule can be approved and then exported to the Oracle Payables by using the Export to Payables in the same menu option.

5 comments:

Soumya Prakash said...

Its awesome , Thanks a bunch !

Kavya said...

Thanks for the wonderful post..
Can you please help me out in finding how the calculation of rent will done for normalized leases..especially in accounts distribution tab..

oraclefusion said...

Thanks for sharing the useful information and good points were stated in the article which is very informative and for the further information visit our site
Oracle Fusion Financials Training

Tulsi Singh said...

I need to know, from where 'location_code' and 'Location Alias' in building form is being populated?
what i know is: we have to give all the details related to site (Address, Date, UOM etc.), while saving the form, 'location_code' and 'Location Alias' is getting populated/generated in a sequence manner. Just need to know where the code is defined for the sequence.
Please mail the answer in tulsi1621@gmail.com

Rainbow Training Institute said...

Thank you for sharing such a nice and interesting blog and really very helpful article
Oracle Fusion HCM Online Training