tdd pn variable rent and breakpoints apis v1.4
Post on 02-Jan-2016
267 Views
Preview:
DESCRIPTION
TRANSCRIPT
TECHNICAL DESIGN
Property Manager
PN Variable Rent and Breakpoints APIs
Author: Sreedhar Maya
Creation Date: 19-Aug-10
Last Updated: 24-Nov-10
File URL: http://files.oraclecorp.com/
Version: 1.4
Company Confidential - For Oracle Internal Use Only
Copyright 2023 Oracle Corporation All Rights Reserved
This document is not a promise to deliver and may not be included as part of any contract.
1. Document Control
1.1 Change Record
Date Author Version Change Reference
18-Aug-2010 Sreedhar Maya 1.0 First Draft2-Sep-2010 Sreedhar Maya 1.1 Updated the API details with new approach, separate procedures for
generate periods and generate abatement, constraints and break points.
30-Sep-2010 Sreedhar Maya 1.2 Updated with Update Variable Rent part of the API.03-Nov-2010 Vamshi Katta 1.3 Updated Record types, table types of the API & also Apendix.24-Nov-2010 Vamshi Katta 1.4 Updated TDD.
1.2 Contributors
Contributor Role Position
Kranti Maddi Property Manager DevelopmentBadsaheb Noorbhasha Property Manager DevelopmentNikhil Mishra Property Manager Development
1.3 Reviewers
Name Role Position Document Status Date Reviewed Comments Incorporated
Document Control Oracle Confidential - For internal use only Page ii of 34
Contents
1. Document Control...........................................................................................................................ii
1.1 Change Record..........................................................................................................................................ii
1.2 Contributors.............................................................................................................................................ii
1.3 Reviewers.................................................................................................................................................ii
2. Introduction....................................................................................................................................1
2.1 Scope for this Document...........................................................................................................................1
2.2 Document Reference................................................................................................................................12.2.1 Current Document References.....................................................................................................................................12.2.2 Historical Document References..................................................................................................................................1
3. Technical Overview.........................................................................................................................2
3.1 Design Goals.............................................................................................................................................2
3.2 Technology...............................................................................................................................................2
3.3 Design Assumptions..................................................................................................................................23.3.1 Create Variable Rent....................................................................................................................................................23.3.2 Update Variable Rent:..................................................................................................................................................23.3.3 Generate Periods:..........................................................................................................................................................23.3.4 Calculation Override....................................................................................................................................................2
3.4 Design Constraints....................................................................................................................................3
4. Architecture....................................................................................................................................4
4.1 System Flow and Interaction Diagram.......................................................................................................4
4.2 System Interfaces.....................................................................................................................................5
4.3 Physical Data Model.................................................................................................................................5
5. Technical Components....................................................................................................................6
6. PN_VAR_RENT_PUB Program Design..............................................................................................7
6.1 Parameters (IN/OUT)................................................................................................................................76.1.1 Procedure CREATE_VAR_RENT accepts the following parameters.........................................................................76.1.2 Procedure GENERATE_PERIODS accepts the following parameters.......................................................................76.1.3 Procedure CREATE_BKPT_CONST_ALLOW_ABAT accepts the following parameters.......................................86.1.4 Procedure UPDATE_VAR_RENT accepts the following parameters........................................................................86.1.5 Package PN_VRA_CLIENT_EXTN.CALCULATE_OVERRIDE accepts the following parameters.......................9
6.2 Calling Arguments...................................................................................................................................10
6.3 Special Validation Logic...........................................................................................................................106.3.1 PN_VAR_RENT_PUB Pseudo-Code........................................................................................................................10
6.3.1.1 CREATE_VAR_RENT Pseudo-Code.......................................................................................................................106.3.1.2 GENERATE_PERIODS Pseudo-Code......................................................................................................................11
Document Control Oracle Confidential - For internal use only Page iii of 34
6.3.1.3 GENERATE_ABAT_BREAKPOINTS Pseudo-Code...................................................................................................116.3.1.4 UPDATE_VAR_RENT Pseudo-Code.......................................................................................................................11
6.3.2 PN_VAREN_PVT Pseudo-Code...............................................................................................................................126.3.2.1 PN_VAREN_PVT.CREATE_VAR_RENT Procedure.................................................................................................136.3.2.2 PN_VAREN_PVT.GENERATE_PERIODS Procedure................................................................................................166.3.2.3 PN_VAREN_PVT.UNDO_BREAKPOINTS Procedure..............................................................................................176.3.2.4 PN_VAREN_PVT.UNDO_CONSTRAINTS Procedure..............................................................................................186.3.2.5 PN_VAREN_PVT.GENERATE_ABAT_BREKPOINTS Procedure...............................................................................186.3.2.6 PN_VAREN_PVT.UPDATE_VAR_RENT Procedure................................................................................................19
6.3.2.7 PN_VAREN_UTIL Pseudo-Code.................................................................................................................................216.3.2.8 PN_VRA_CLIENT_EXTN.CALCULATE_OVERRIDE Pseudo-Code......................................................................25
7 Log Output.................................................................................................................................................26
8 Concurrent Request Output........................................................................................................................27
a. Table and View Usage................................................................................................................................27
b. Sequence Usage.........................................................................................................................................28
c. Restart Strategy.........................................................................................................................................28
d. Crash Recovery...........................................................................................................................................28
e. Database Triggers.......................................................................................................................................28
f. Table Handlers (NEW/ CHANGED)..............................................................................................................28
9 Seed Data.........................................................................................................................................29
10 Installation and Upgrade..............................................................................................................30
a. Installation.................................................................................................................................................30
11 Unit Test Considerations...............................................................................................................31
a. Create Variable Rent..................................................................................................................................31
b. Update Variable Rent.................................................................................................................................31
12 Open and Closed Issues.................................................................................................................33
a. Closed Issues..............................................................................................................................................33
b. Solution for issues raised by McDonald’s team...........................................................................................33
13 Appendix A: Parameters for PN_VAR_RENT_PUB..........................................................................34
13.1 CREATE_VAR_RENT Record Type.........................................................................................................3413.1. 1 Variable Rent Record Type............................................................................................................................................3413.1.2 Variable Rent Lines Table Type......................................................................................................................................3413.1.3 Breakpoint Header Table Type.......................................................................................................................................3413.1.4 Breakpoint Details Table Type........................................................................................................................................3413.1.5 Constraints Table Type....................................................................................................................................................3413.1.6 Allowances & Abatements Table Type...........................................................................................................................34
13.2 UPDATE_VAR_RENT Record Type........................................................................................................3513.2.1 Update Variable Rent record type...................................................................................................................................35
14 Appendix B: Parameters for PN_VAREN_PVT................................................................................36
14.1 CREATE_VAR_RENT Record Type.........................................................................................................36
13.1 UPDATE_VAR_RENT Record Type...............................................................................................................36
Document Control Oracle Confidential - For internal use only Page iv of 34
2. IntroductionThis document defines the technical components required to create the Variable Rent and Breakpoint APIs for Property Manager. This Application Extension Technical Design document complements the application extension function design document for Variable Rent and Breakpoint APIs (FDD PN Variable Rent and Breakpoints APIs v1.3.doc).
2.1 Scope for this Document
The scope of this document is confined to the Functional document of Variable Rent and Breakpoint APIs (FDD PN Variable Rent and Breakpoints APIs v1.3.doc).
2.2 Document Reference
2.2.1 Current Document References
Author Document Name URL Comments
Vamshi Katta FDD PN Variable Rent and Breakpoints APIs v1.3.doc
N/A None
2.2.2 Historical Document References
Author Document Name URL Comments
Vamshi Katta FDD PN Variable Rent and Breakpoints APIs v1.3.doc
N/A None
Document Control Oracle Confidential - For internal use only Page 1 of 34
3. Technical OverviewThe section list the technical overview for creating Variable rent and Breakpoint APIs.
3.1 Design Goals
Below is the list of goals defined in this document.
1. Designing an API for Create Variable Rent.
2. Designing and API for Update Variable Rent.
3.2 Technology
No New technology is used for designing and developing the APIs for Variable Rent and Breakpoints.
3.3 Design Assumptions
This design assumes that the following assumptions are true:
3.3.1 Create Variable Rent
This section lists the design assumption for Create Variable Rent.
Lease is created and in ‘Final’ status to create the variable rent.
Term templates are defined prior to creation of variable rent.
Agreement template should be defined prior to creation of variable rent.
Note: Creation of Lease is not part of this API.
3.3.2 Update Variable Rent:
This section lists the design assumptions for Update Variable Rent.
Variable Rent is created and ready for update
Note: Creation of Variable rent will not be part of Update Variable rent API.
3.3.3 Generate Periods:
This section lists the design assumptions for Generate Periods.
Periods are generated by calling an existing concurrent program for Generate Periods – ‘Calculate Variable Rent’
Calculate Variable Rent is initiated if the input variable generate_periods is ‘Yes’ for the calling program i.e., Create program for Variable Rent.
Periods can be generated from Create APIs for Variable Rent
If the ‘Calculate Variable Rent’ program completes in status Error or Warning, the creation/updating of Variable Rent cannot be reverted.
3.3.4 Calculation Override
This section lists the design assumptions for Variable rent Override.
Profile option PN: Override Variable Rent is set to Yes, to override the calculations
Document Control Oracle Confidential - For internal use only Page 2 of 34
Hook is customized to override the calculations
Calculations are overridden only upon ‘Calculate All’ button is pressed manually.
No pl/sql code is provided for initiating ‘Calculate All’ button’s logic.
3.4 Design Constraints
Not Applicable
Document Control Oracle Confidential - For internal use only Page 3 of 34
4. Architecture This section describes the technical architecture for Variable Rent and Breakpoint APIs.
4.1 System Flow and Interaction Diagram
This section shows the technical components of the Create/Update Variable Rent APIs and the database logic from one form of component to the other.
Document Control Oracle Confidential - For internal use only Page 4 of 34
4.2 System Interfaces
This document doesn’t capture the interaction of Oracle application user interface.
4.3 Physical Data Model
Not Applicable
Document Control Oracle Confidential - For internal use only Page 5 of 34
5. Technical ComponentsThere are no new Tables/Views are created as part of Create/Update Variable Rent APIs.
Document Control Oracle Confidential - For internal use only Page 6 of 34
6. PN_VAR_RENT_PUB Program DesignThis section describes the technical design considerations for Create/Update Variable Rent APIs.
6.1 Parameters (IN/OUT)
6.1.1 Procedure CREATE_VAR_RENT accepts the following parameters
Parameter Usage Type Required Description
P_API_VERSION IN NUMBER Yes API StandardP_INIT_MSG_LIST IN NUMBER Yes API StandardP_COMMIT IN NUMBER Yes API StandardP_VALIDATE_FLAG IN VARCHAR2 No Default value is N’. If ‘Y’ then, program takes
care of validation of the input parameters and returns whether they are valid or not. If ‘N’ then, program not only validates the input parameters but also after successful validation inserts records into base tables.
P_VAR_RENT_REC IN RECORD TYPE
Yes Variable Rent Record Type
P_VARIABLE_RENT_LINES_TBL
IN TABLE TYPE Yes Variable Rent Lines Table Type
P_BREAKPOINT_HEADER_TBL IN TABLE TYPE Yes Breakpoint Header Table Type
P_BREAKPOINT_DETAILS_TBL IN TABLE TYPE Yes Breakpoint Details Table Type
P_CONSTRAINTS_TBL IN TABLE TYPE Yes Constraints Table Type
P_ALLOW_ABAT_TBL IN TABLE TYPE Yes Allowances & Abatements Table Type
P_GENERATE_PERIODS IN VARCHAR2 No Default value is N’. If ‘Y’ then generate the periods and able to create Breakpoints, Constraints, Allowances and Abatements.
P_GENERATE_BREAKPOINTS IN VARCHAR2 No Generate Breakpoints Button. If Value is 'Y' then needs to generate Breakpoints. Default Value is 'N".
P_GENERATE_CONSTRAINTS IN VARCHAR2 No Generate Constraints Button. If the Value is 'Y' then, need to generate constraints. Default value is 'N'.
X_RETURN_STATUS OUT VARCHAR2 Return status.Valid values are: S (Success), E (Error), and U (Unexpected error).
X_MSG_COUNT OUT NUMBER API StandardX_MSG_DATA OUT VARCHAR2 API StandardX_VAR_RENT_ID OUT NUMBER Variable rent IdX_VAR_RENT_NUM OUT VARCHAR2 Variable Rent Number
6.1.2 Procedure GENERATE_PERIODS accepts the following parameters
Parameter Usage Type Required Description
P_API_VERSION IN NUMBER Yes API StandardP_INIT_MSG_LIST IN NUMBER Yes API StandardP_COMMIT IN NUMBER Yes API StandardP_VALIDATE_FLAG IN VARCHAR2 No Default value is N’. If ‘Y’ then, program takes
care of validation of the input parameters and returns whether they are valid or not. If ‘N’ then, program not only validates the input parameters but also after successful validation inserts records into base tables.
P_VAR_RENT_ID IN NUMBER Variable Rent ID
P_GENERATE_PERIODS IN VARCHAR2 Default ‘N’. If Value is ‘Y’, then for the
Document Control Oracle Confidential - For internal use only Page 7 of 34
Parameter Usage Type Required Description
variable rent period’s would be generated.X_RETURN_STATUS OUT VARCHAR2 Return status.
Valid values are: S (Success), E (Error), and U (Unexpected error).
X_MSG_COUNT OUT NUMBER API Standard
X_MSG_DATA OUT VARCHAR2 API Standard
6.1.3 Procedure CREATE_BKPT_CONST_ALLOW_ABAT accepts the following parameters
Parameter Usage Type Required Description
P_API_VERSION IN NUMBER Yes API StandardP_INIT_MSG_LIST IN NUMBER Yes API StandardP_COMMIT IN NUMBER Yes API StandardP_VALIDATE_FLAG IN VARCHAR2 No Default value is N’. If ‘Y’ then, program takes
care of validation of the input parameters and returns whether they are valid or not. If ‘N’ then, program not only validates the input parameters but also after successful validation inserts records into base tables.
P_VARIABLE_RENT_LINES_TBL
IN TABLE TYPE Yes Variable Rent Lines Table Type
P_BREAKPOINT_HEADER_TBL IN TABLE TYPE Yes Breakpoint Header Table Type
P_BREAKPOINT_DETAILS_TBL IN TABLE TYPE Yes Breakpoint Details Table Type
P_CONSTRAINTS_TBL IN TABLE TYPE Yes Constraints Table Type
P_ALLOW_ABAT_TBL IN TABLE TYPE Yes Allowances & Abatements Table Type
X_RETURN_STATUS OUT VARCHAR2 Return status.Valid values are: S (Success), E (Error), and U (Unexpected error).
X_MSG_COUNT OUT NUMBER API StandardX_MSG_DATA OUT VARCHAR2 API StandardX_VAR_RENT_ID OUT NUMBER Variable rent IdX_VAR_RENT_NUM OUT VARCHAR2 Variable Rent Number
6.1.4 Procedure UPDATE_VAR_RENT accepts the following parameters
Parameter Usage Type Required Description
P_API_VERSION IN NUMBER Yes API StandardP_INIT_MSG_LIST IN NUMBER Yes API StandardP_COMMIT IN NUMBER Yes API StandardP_VALIDATE_FLAG IN VARCHAR2 No Default value is N’. If ‘Y’ then, program takes
care of validation of the input parameters and returns whether they are valid or not. If ‘N’ then, program not only validates the input parameters but also after successful validation inserts records into base tables.
P_UPD_VAR_RENT_REC IN TABLE TYPE Yes Update Variable Rent Record TypeP_VARIABLE_RENT_LINES_TBL IN TABLE TYPE Yes Variable Rent Lines Table Type
Update of Variable Rent will handle creation of a new VRA line, if LINE_DEFAULT_ID is null of that record type.
P_BREAKPOINT_HEADER_TBL IN TABLE TYPE Yes Breakpoint Header Table TypeUpdate of Variable Rent will handle creation of a new Breakpoint Header line, if BKHD_DEFAULT_ID is null of that record type.
P_BREAKPOINT_DETAILS_TBL IN TABLE TYPE Yes Breakpoint Details Table Type
Document Control Oracle Confidential - For internal use only Page 8 of 34
Parameter Usage Type Required Description
Update of Variable Rent will handle creation of a new Breakpoint Detail line, if BKDT_DEFAULT_ID is null of that record type.
P_CONSTRAINTS_TBL IN TABLE TYPE Yes Constraints Table TypeUpdate of Variable Rent will handle creation of a new Constraint line, if CONSTR_DEFAULT_ID is null of that record type. Note: Need to make sure that for single VRA we can have only two constraint records i.e. of constraint type as ‘Maximum’ and ‘Minimum’.
P_ALLOW_ABAT_TBL IN TABLE TYPE Yes Allowances & Abatements Table TypeUpdate of Variable Rent will handle creation of a new Allowance and Abatement line, if ABATEMENT_ID is null of that record type.
P_UNDO_PERIODS IN VARCHAR2 No If ‘Y’ then Undo the periods and able to update Breakpoints, Constraints, Allowances and Abatements. If ‘N’, then no need to undo periods i.e., no need to update the Variable Rent details.Default value is 'Y'.
P_UNDO_BREAKPOINTS IN VARCHAR2 No Undo Breakpoints Button. If Value is 'Y' means undo Breakpoints. If ‘N’ then no need to undo Breakpoints i.e., no need to update breakpoints details.Default value is 'Y'.
P_UNDO_CONSTRAINTS IN VARCHAR2 No Undo Constraints Button. If the Value is 'Y' means Undo constraints. If the Value is ‘N’ means no need to undo constraints i.e., no need to update the constraints.Default value is 'Y'.
X_RETURN_STATUS OUT VARCHAR2 Return status.Valid values are: S (Success), E (Error), and U (Unexpected error).
X_MSG_COUNT OUT NUMBER API StandardX_MSG_DATA OUT VARCHAR2 API Standard
6.1.5 Package PN_VRA_CLIENT_EXTN.CALCULATE_OVERRIDE accepts the following parameters
Parameter Usage Type Required
Description
P_VAR_RENT_ID IN NUMBER Yes Var_rent_id for which the calculation override is used.
P_TRX_HEADER_ID IN NUMBER Yes Trx_header_idP_CALCULATED_RENT IN OUT NUMBER No Calculated_rentP_PRORATED_RENT IN OUT NUMBER No Prorated_rentP_PERCENT_RENT IN OUT NUMBER No Percent_rentP_YTD_PERCENT_RENT IN OUT NUMBER No Ytd_percent_rentP_FIRST_YR_RENT IN OUT NUMBER No First_yr_rentX_RETURN_STATUS OUT VARCHAR2 No API StandardX_MSG_COUNT OUT NUMBER No API Standard
Document Control Oracle Confidential - For internal use only Page 9 of 34
Parameter Usage Type Required
Description
X_MSG_DATA OUT VARCHAR2 No API Standard
6.2 Calling Arguments
This program is called by:
Any custom package which passes the above parameters (Section 6.1.1) for creating new variable rent agreement.
The program calls the following other programs:
Program Unit Argument Description
PN_VAREN_PVT Listed in Appendix B This is a Private package for calling Utility packages and already existing variable rent create packages. This package is called by PN_VAR_RENT_PUB API.
PN_VAREN_UTIL Based on the validation, different arguments are passed to call this package.
This is Validation package; it contains all the validations which have to be performed before Creating the Variable Rent agreement.
6.3 Special Validation Logic
PN_VAREN_UTIL Utility package contains all the validations for Create and Update Variable Rent API.
PN_VAREN_PVT private package uses utility package and validate the records for creating Variable rent.
PN_VAR_RENT_PUB is public API which can be called by any custom program and can Create/Update Variable Rent.
6.3.1 PN_VAR_RENT_PUB Pseudo-Code
6.3.1.1 CREATE_VAR_RENT Pseudo-Code
--Get the debug profile value for PN – PN_DEBUG_MODEpg_debug VARCHAR2 (1) := NVL (fnd_profile.VALUE('PN_DEBUG_MODE'),'N');Begin -- Initialize MOAC for R12--Check if the api version passed as a parameter is equal to the existing api version(global variable)If ( l_api_version = g_api_version) THEN --Call private api to validate the input parameters and call inbuilt package PN_VAR_RENTS_PKG.INSERT_ROW package to create the variable rent.
PN_VAREN_PVT.create_var_rent (p_commit => p_commit , p_validate_flag => p_validate_flag , p_var_rent_rec => p_var_rent_rec , p_variable_rent_lines_tbl => p_variable_rent_lines_tbl , p_breakpoint_header_tbl => p_breakpoint_header_tbl , p_breakpoint_details_tbl => p_breakpoint_details_tbl , p_constraints_tbl => p_constraints_tbl
Document Control Oracle Confidential - For internal use only Page 10 of 34
, p_allow_abat_tbl => p_allow_abat_tbl , p_generate_periods => p_generate_periods , p_generate_breakpoints => p_generate_breakpoints , p_generate_constraints => p_generate_constraints , x_return_status => x_return_status , x_msg_count => x_msg_count , x_msg_data => x_msg_data , x_var_rent_id => x_var_rent_id , x_var_rent_num => x_var_rent_num );ELSE --Log message with the api version error END IF;
--Standard check for the commit.
IF fnd_api.to_boolean (p_commit) THEN COMMIT WORK;
END IF;
EXCEPTION --Calling api standard package to check the errors
WHEN fnd_api.g_exc_error THEN ROLLBACK; <Log message with the error message>
WHEN fnd_api.g_exc_unexpected_error THEN ROLLBACK; <Log message with the error message>
WHEN OTHERS THEN ROLLBACK; <Log message with the error message>
END create_var_rent;
6.3.1.2 GENERATE_PERIODS Pseudo-Code
Input parameter for this api is variable rent id
Call private package to call the concurrent program to generate the periods, for the given variable rent id.
6.3.1.3 GENERATE_ABAT_BREAKPOINTS Pseudo-Code
Input paramters for this api is variable rent id
Call private package to call the concurrent program to generate the periods, for the given variable rent id.
6.3.1.4 UPDATE_VAR_RENT Pseudo-Code
--Get the debug profile value for PN – PN_DEBUG_MODEpg_debug VARCHAR2 (1) := NVL (fnd_profile.VALUE('PN_DEBUG_MODE'),'N');Begin -- Initialize MOAC for R12
Document Control Oracle Confidential - For internal use only Page 11 of 34
--Check if the api version passed as a parameter is equal to the existing api version(global variable)If ( l_api_version = g_api_version) THEN --Call private api to validate the input parameters and update the variable rent tables accordingly.
PN_VAREN_PVT.update_var_rent (p_commit => p_commit , p_validate_flag => p_validate_flag , p_upd_var_rent_rec => p_upd_var_rent_rec , p_variable_rent_lines_tbl => p_variable_rent_lines_tbl , p_breakpoint_header_tbl => p_breakpoint_header_tbl , p_breakpoint_details_tbl => p_breakpoint_details_tbl , p_constraints_tbl => p_constraints_tbl , p_allow_abat_tbl => p_allow_abat_tbl , p_generate_periods => p_generate_periods , p_generate_breakpoints => p_generate_breakpoints , p_generate_constraints => p_generate_constraints , x_return_status => x_return_status , x_msg_count => x_msg_count , x_msg_data => x_msg_data );ELSE --Log message with the api version error END IF;
--Standard check for the commit.
IF fnd_api.to_boolean (p_commit) THEN COMMIT WORK;
END IF;
EXCEPTION --Calling api standard package to check the errors
WHEN fnd_api.g_exc_error THEN ROLLBACK; <Log message with the error message>
WHEN fnd_api.g_exc_unexpected_error THEN ROLLBACK; <Log message with the error message>
WHEN OTHERS THEN ROLLBACK; <Log message with the error message>
END update_var_rent;
6.3.2 PN_VAREN_PVT Pseudo-Code
PN_VAREN_PVT Specification contains all the global variables for lookup codes, record types and table types declared as shown below:
-- Declaring Lookup Type constants g_cumulative_vol CONSTANT VARCHAR2 (30) := 'PN_VAR_CALCULATION_METHOD'; g_proration_rule CONSTANT VARCHAR2 (30) := 'PN_VAR_PRORATION_RULE'; g_invoice_on CONSTANT VARCHAR2 (30) := 'PN_VAR_INVOICE_ON'; g_negative_rent CONSTANT VARCHAR2 (30) := 'PN_VAR_NEGATIVE_RENT';
Document Control Oracle Confidential - For internal use only Page 12 of 34
g_rent_type CONSTANT VARCHAR2 (30) := 'PN_VAR_RENT_TYPE'; g_payment_purpose CONSTANT VARCHAR2 (30) := 'PN_PAYMENT_PURPOSE_TYPE'; g_payment_frequency CONSTANT VARCHAR2 (30) := 'PN_PAYMENT_FREQUENCY_TYPE'; g_sales_channel CONSTANT VARCHAR2 (30) := 'PN_SALES_CHANNEL'; g_item_category CONSTANT VARCHAR2 (30) := 'PN_ITEM_CATEGORY'; g_break_type CONSTANT VARCHAR2 (30) := 'PN_VAR_BREAK_TYPE'; g_type_code CONSTANT VARCHAR2 (30) := 'PN_MAX_MIN'; g_appl_order CONSTANT VARCHAR2 (30) := 'PN_APPLICATION_ORDER'; g_excess_abate_code CONSTANT VARCHAR2 (30) := 'PN_EXCESS_ABATEMENT'; g_abate_type_code CONSTANT VARCHAR2 (30) := 'PN_ABATEMENT_TYPE'; g_pay_terms_status CONSTANT VARCHAR2 (30) := 'PN_INDEX_PAYMENT_TERM_STATUS';
--Record type for variable rent detailsTYPE var_rent_rec_type IS RECORD (definition contains all the columns as shown in section 11.1.1);
--Record type for Variable rent lines detailsTYPE variable_rent_lines_rec_type IS RECORD (definition contains all the columns as shown in section 11.1.2);
--Table type for variable rent lines detailsTYPE variable_rent_lines_tbl_type IS TABLE OF variable_rent_lines_rec_type INDEX BY BINARY_INTEGER;
--Record type for Breakpoint header detailsTYPE breakpoint_header_rec_type IS RECORD (definition contains all the columns as shown in
section 11.1.3);
--Table type for breakpoint header details TYPE breakpoint_header_tbl_type IS TABLE OF breakpoint_header_rec_type INDEX BY BINARY_INTEGER;
--Record type for Breakpoint detailsTYPE breakpoint_details_rec_type IS RECORD (definition contains all the columns as shown in section 11.1.4);
--Table type for breakpoint details TYPE breakpoint_details_tbl_type IS TABLE OF breakpoint_details_rec_type INDEX BY BINARY_INTEGER;
--Record type for constraints detailsTYPE constraints_rec_type IS RECORD (definition contains all the columns as shown in section 11.1.5);
--Table type for constraints detailsTYPE constraints_tbl_type IS TABLE OF constraints_rec_type INDEX BY BINARY_INTEGER;
--Record type for allow abatement detailsTYPE allow_abat_rec_rec_type IS RECORD (definition contains all the columns as shown in section 11.1.6);
--Table type for allow abatement detailsTYPE allow_abat_rec_tbl_type IS TABLE OF allow_abat_rec_rec_type INDEX BY BINARY_INTEGER;
6.3.2.1 PN_VAREN_PVT.CREATE_VAR_RENT Procedure
BEGIN -- Validate Lease Id or Lease Num or Lease Name IF ( lease_id or lease_num or lease_name is not null) THEN
Document Control Oracle Confidential - For internal use only Page 13 of 34
Call the package pn_varen_utils_pkg.validate_lease with appropriate parameters.
<Error handing based on the l_return_status from the above package> END IF;
BEGIN -- Validate Variable Rent Number IF ( rent_num is not null) THEN Call the package pn_varen_utils_pkg.validate_rent_num with appropriate parameters.
<Error handing based on the l_return_status from the above package> END IF;
--Variable Rent : Commencement date validation Check if the below conditions for commencement date to be success 1. commencement date is not null 2. commencement date > = lease commencement date 3. commencement date < = termination date If the commencement date is NULL then commencement date := lease commencement date; Else Capture the error; END IF; Similarly check the below conditions for Termination date 1. Termination date is not null 2. Termination date > lease commencement date 3. Termination date <= lease Termination date If the Termination date is NULL then Termination date := lease Termination date; Else Capture the error; END IF; END IF;
-- Validating location id, based on the lease_id IF ( location_id or location_code is not null) THEN Call the package pn_varen_utils_pkg.validate_location with appropriate parameters
<Error handing based on the l_return_status from the above package> END IF;
-- Validating CATEGORY i.e., PROCESS_CODE
IF ( purpose_code or purpose_code_meaning IS NOT NULL) THEN Call package pn_varen_utils_pkg.validate_lookups with appropriate parameters for validating purpose_code.
<Error handing based on the l_return_status from the above package> END IF;
-- Validating VOLUME TYPE i.e.,Variable Rent type
Document Control Oracle Confidential - For internal use only Page 14 of 34
IF ( type_code or type_meaning IS NOT NULL) THEN Call package pn_varen_utils_pkg.validate_lookups with appropriate parameters for validating type_code. <Error handing based on the l_return_status from the above package> END IF;
-- Validating ABSTRACTED BY i.e., USER
IF (abstracted_by_user IS NOT NULL) THEN Call package pn_varen_utils_pkg.validate_abst_user to validate the abstracted by user.
<Error handing based on the l_return_status from the above package> END IF;
-- Validating CUMMULATIVE VOLUME i.e., Calculation Method IF ( cumulative_vol_code or cumulative_vol_meaning IS NOT NULL) THEN Call package pn_varen_utils_pkg.validate_lookups to validate the cummulative volume
<Error handing based on the l_return_status from the above package> END IF;
-- Validating PRORATION RULE i.e., Partial Year method IF ( proration_rule_code or proration_rule_meaning IS NOT NULL) THEN Call package pn_varen_utils_pkg.validate_lookups to validate proration rule. <Error handing based on the l_return_status from the above package> END IF;
-- Validating invoice on IF ( invoice_on_code or invoice_on_meaning IS NOT NULL) THEN Call package pn_varen_utils_pkg.validate_lookups to validate invoice_on_code.
<Error handing based on the l_return_status from the above package> END IF;
-- Validating NEGATIVE RENT IF ( negative_rent_code Or negative_rent_meaning IS NOT NULL) THEN Call package pn_varen_utils_pkg.validate_lookups to validate Negative Rent.
<Error handing based on the l_return_status from the above package> END IF;
-- Validating TERM TEMPLATE IF ( term_template_id or term_template_name IS NOT NULL) THEN Call package pn_varen_utils_pkg.validate_term_template to validate the term template.
<Error handing based on the l_return_status from the above package> END IF;
--Validating CURRENCY code IF ( currency_code IS NOT NULL)
Document Control Oracle Confidential - For internal use only Page 15 of 34
THEN Call pn_varen_utils_pkg.validate_currency to validate the currency code. <Error handing based on the l_return_status from the above package> END IF;
--If the program is not run with validate mode, then the insert_row program is called to insert the records into pn_var_rents_all table IF l_validate_falg <> 'Y' THEN PN_VAR_RENTS_PKG.INSERT_ROW ( <Pass the parameters required by this package> );
PN_VAR_RENT_DATES_PKG.INSERT_ROW( <Pass the parameters required by this package> );
END IF;
IF the variable rent record has been created without errors and p_generate_periods = 'Y' Then <Concurrent program ‘Calculate Variable Rent’ is called with appropriate paramters>If the periods are generated without warning/error THEN <Call the Validation program for breakpoints headers and break point details >End IF;
IF p_generate_constraints = 'Y' Then < validated the mandatory fields> <Create the constraints using below package> PN_VAR_CONSTRAINTS_PKG.INSERT_ROW( <Pass the parameters required by this package> ); < Call the package to generate Constraints> <Concurrent program ‘Calculate Variable Rent’ is called with appropriate paramters>END IF;
IF p_generate_breakpoints= 'Y' Then <Validate the mandatory columns for breakpoints > <Create the breakpoints header and details using below package> PN_VAR_BKPTS_HEAD_PKG.INSERT_ROW( <Pass the parameters required by this package> );
PN_VAR_BKPTS_DET_PKG.INSERT_ROW( <Pass the parameters required by this package> ); < Call the package to generate the Break Point periods> <Concurrent program ‘Calculate Variable Rent’ is called with appropriate paramters>END IF;
IF p_recurring_abatements = 'Y' Then <Validate the mandatory columns for abatements/allowances > < Call the package to generate recurring abatements> <Concurrent program ‘Calculate Variable Rent’ is called with appropriate paramters>END IF;
END create_var_rent;
6.3.2.2 PN_VAREN_PVT.GENERATE_PERIODS Procedure<Check if dates are created using below query> CURSOR c_dates_created IS SELECT COUNT (*) FROM pn_var_rent_dates_all
Document Control Oracle Confidential - For internal use only Page 16 of 34
WHERE var_rent_id = p_var_rent_id;If <the dates are created> THEN Fetch the date’s related columns from pn_var_rent_dates_all table as shown below. SELECT pvrd.use_gl_calendar , pvrd.year_start_date , pvra.var_rent_id , pvra.cumulative_vol , pvra.chg_cal_var_rent_id FROM pn_var_rents_all pvra , pn_var_rent_dates_all pvrd WHERE pvra.var_rent_id = pvrd.var_rent_id AND pvra.var_rent_id = p_var_rent_id;END IF;
--Check if periods existsl_period_exists := pn_var_rent_pkg.find_if_period_exists (l_varentid);
IF NVL (l_period_exists , 9898 ) = 9898 THEN IF NVL (l_use_gl_calendar , 'N' ) = 'Y' THEN-- Call create_var_rent_periods with parameters var_rent_id and cummulative_vol pn_var_rent_pkg.create_var_rent_periods (l_varentid , l_cumulativevol ); x_return_status := fnd_api.g_ret_sts_success; ELSIF NVL (l_use_gl_calendar , 'N' ) = 'N' THEN-- Call create_var_rent_periods_nocal with parameters var_rent_id, cummulative_vol and year_start_date pn_var_rent_pkg.create_var_rent_periods_nocal (l_varentid , l_cumulativevol , l_yearstartdate ); END IF; ELSE x_return_status := fnd_api.g_ret_sts_error; END IF;
6.3.2.3 PN_VAREN_PVT.UNDO_BREAKPOINTS Procedurel_period_exists := PN_VAR_RENT_PKG.find_if_period_exists(l_varentId); IF l_period_exists IS NOT NULL THEN
/*codev*/
IF name_in('vol_hist_blk.vol_hist_id') IS NOT NULL THEN go_block('vol_hist_blk'); clear_block(NO_VALIDATE); END IF;
DELETE FROM PN_VAR_VOL_HIST_ALL WHERE period_id IN (SELECT period_id FROM PN_VAR_PERIODS_ALL WHERE var_rent_id = l_varentId);
DELETE FROM pn_var_constraints_all WHERE period_id IN (SELECT period_id FROM PN_VAR_PERIODS_ALL WHERE var_rent_id = l_varentId);
DELETE FROM pn_var_bkpts_det_all WHERE var_rent_id = l_varentid;
Document Control Oracle Confidential - For internal use only Page 17 of 34
DELETE pn_var_bkpts_head_all WHERE var_rent_id = l_varentid;
DELETE pn_var_lines_all WHERE var_rent_id = l_varentid;
DELETE FROM pn_var_abatements_all abat WHERE abat.var_rent_id=l_varentId;
PN_VAR_RENT_PKG.delete_var_rent_periods(l_varentId);
PN_VAR_TRX_PKG.delete_transactions( p_var_rent_id => l_varentId ,p_period_id => NULL ,p_line_item_id => NULL);
PN_VAR_DEFAULTS_PKG.delete_default_lines(l_varentId);
DELETE FROM pn_var_abat_defaults_all WHERE var_rent_id = l_varentId;
DELETE FROM pn_var_bkdt_defaults_all WHERE var_rent_id = l_varentid;
DELETE pn_var_bkhd_defaults_all WHERE var_rent_id = l_varentid;
DELETE pn_var_line_defaults_all WHERE var_rent_id = l_varentid;
DELETE pn_var_constr_defaults_all WHERE var_rent_id = l_varentid;
Commit;
6.3.2.4 PN_VAREN_PVT.UNDO_CONSTRAINTS Procedure delete from pn_var_constraints_all where constr_default_id in (select constr_default_id from pn_var_constr_defaults_all where var_rent_id = l_var_rent_id);
COMMIT;
6.3.2.5 PN_VAREN_PVT.GENERATE_ABAT_BREKPOINTS Procedure--Generate Constraints--Check if constraint_default_id exists from pn_var_constr_defaults_all for the given var_rent_id.IF constraint_default_id <> 0 THEN pn_var_defaults_pkg.create_default_constraints (p_var_rent_id);END IF;
--Generate Breakpoints--Check if breakpoint defaults are created from the table pn_var_bkdt_defaults_all for the given var_rent_idIF breakpoints_defaults created THEN--Call the below packages to generate breakpoints pn_var_defaults_pkg.create_setup_data (p_var_rent_id); pn_var_chg_cal_pkg.populate_transactions (p_var_rent_id);END IF;
Document Control Oracle Confidential - For internal use only Page 18 of 34
6.3.2.6 PN_VAREN_PVT.UPDATE_VAR_RENT Procedure
PN_VAREN_PVT Specification contains all the global variables for lookup codes, as shown in section 6.3.2
<Declaration section>
--Cursor to fetch the records from PN_VAR_RENTS_ALL Cursor1
--Cursor to fetch the records from PN_VAR_RENT_DATES_ALLCursor2
--Cursor to fetch the records from PN_VAR_LINE_DEFAULTS_ALLCursor3
--Cursor to fetch the records from PN_VAR_BKHD_DEFAULTS_ALLcursor4
--Cursor to fetch the records from PN_VAR_BKDT_DEFAULTS_ALLcursor5
--Cursor to fetch the records from PN_VAR_CONSTR_DEFAULTS_ALLcursor6
--Cursor to fetch the records from PN_VAR_ABAT_DEFAULTS_ALLcursor7
BEGIN -- Update for Variable Rent -- Open cursor1 and fetch all the cursor values into variables -- Compare each column from the parameter P_VAR_RENT_REC variable with the cursor1 variables -- If the input parameter from p_var_rent_rec is different from the existing value from the cursor1 then assign the new value to p_var_rent_rec column -- Below is an example for above 3 validation points IF cursor1.cumulative_vol IS NULL or cursor1.cumulative_vol = g_mis_char THEN cursor1.cumulative_vol := p_var_rent_rec.cumulative_vol; ELSIF cursor1.cumulative_vol IS NOT NULL and (cursor1.cumulative_vol = p_var_rent_rec.cumulative_vol) THEN cursor1.cumulative_vol := p_var_rent_rec.cumulative_vol; ELSIF cursor1.cumulative_vol IS NOT NULL and (cursor1.cumulative_vol <> p_var_rent_rec.cumulative_vol) THEN validate cursor1.cumulative_vol; IF cursor1.cumulative_vol - Validated THEN cursor1.cumulative_vol := cursor1.cumulative_vol; ELSE fnd_api.set_error; END IF; END IF; -- After all the validations call the UPDATE_ROW package with the appropriate parameters IF l_validate_falg <> 'Y' THEN PN_VAR_RENTS_PKG.UPDATE_ROW( <Pass the parameters required by this package> );
END IF;
Document Control Oracle Confidential - For internal use only Page 19 of 34
-- Update for Variable Rent Dates -- Open Cursor2 and fetch all the cursor values into variables -- Compare each column from the parameter P_VAR_RENT_REC variable with the cursor2 variables -- If the input parameter from p_var_rent_rec is different from the existing value from the cursor2 then assign the new value to p_var_rent_rec column -- After all the validations, call the update_row for dates IF l_validate_falg <> 'Y' THEN PN_VAR_RENT_DATES_PKG.UPDATE_ROW( <Pass the parameters required by this package> );
END IF; -- Undo Periods -- Check if periods exist -- Call the private procedure to Undo Periods -- See section 6.3.2.3 for Undo periods logic -- Update for Breakpoints lines, headers, and details -- Call UNDO generate Breakpoints procedure before updating the breakpoints --Below is the procedures for UNDO Breakpoints pn_var_defaults_pkg.delete_default_lines (l_var_rent_id, exists_hd_def_rec.bkhd_default_id, l_bkpt_header_id); pn_var_defaults_pkg.delete_transactions (l_var_rent_id, exists_hd_def_rec.bkhd_default_id, l_bkpt_header_id); -- Open Cursor3, Cursor4 and Cursor5 and fetch the cursor values into variables -- Validate Cursor3 variables with P_VARIABLE_RENT_LINES_TBL -- Validate cursor4 variables with P_BREAKPOINT_HEADER_TBL -- and Validate cursor5 variables with P_BREAKPOINT_DETAILS_TBL --Upon validation of all the cursor values with corresponding IN parameters, call respective UPDATE_ROW package -- Below is the packages for updating Variable Lines, Breakpoint Headers and Breakpoint Details respectively
If row exists Then PN_VAR_LINE_DEFAULTS_PKG.UPDATE_ROW( <Pass the parameters required by this package> );Else PN_VAR_LINE_DEFAULTS_PKG.INSERT_ROW( <Pass the parameters required by this package> );End if;
If row exists Then PN_VAR_BKHD_DEFAULTS_PKG.UPDATE_ROW( <Pass the parameters required by this package> ); ELSE PN_VAR_BKHD_DEFAULTS_PKG.INSERT_ROW( <Pass the parameters required by this package> ); END IF;
If row exists Then PN_VAR_BKDT_DEFAULTS_PKG.UPDATE_ROW( <Pass the parameters required by this package> );ELSE package> );
Document Control Oracle Confidential - For internal use only Page 20 of 34
END IF;
--After successful updating, call Generate Breakpoints procedure shown in section 6.3.2.4 -- Update Constraints -- Undo constraints -- Below is the logic to undo Constraints delete from pn_var_constraints_all where constr_default_id in (select constr_default_id from pn_var_constr_defaults_all where var_rent_id = l_var_rent_id); -- Open the cursor6 and fetch all the values into variables -- Compare the values from P_CONSTRAINTS_TBL with cursor6 variables -- upon validations, call the update_row packageIF ROW EXISTS (CONSTR_DEFAULT_ID IS NOT NULL)THEN PN_VAR_CONSTR_DEFAULTS_PKG.UPDATE_ROW( <Pass the parameters required by this package> );ELSEPN_VAR_CONSTR_DEFAULTS_PKG.INSERT_ROW( <Pass the parameters required by this package> );
-- After successful updating of constraints, call Generate Constraints procedure as shown in section 6.3.2.4 -- Update Abatements -- Open the cursor7 and fetch the values into variables -- Compare each variable from cursor7 with the values from P_ALLOW_ABAT_TBL -- Upon validation call the update_row package for Allow abatementsIF ROW EXISTS (ABATEMENT_ID IS NOT NULL)THEN PN_VAR_ABAT_DEFAULTS_PKG.UPDATE_ROW( <Pass the parameters required by this package> );ELSE PN_VAR_ABAT_DEFAULTS_PKG.INSERT_ROW( <Pass the parameters required by this package> ); END IF; -- Call generate periods END update_var_rent;
6.3.2.7 PN_VAREN_UTIL Pseudo-Code
Utility package contains all the validations required for Variable Rent Creation/Updating. This section lists the validations and the SQL query used for validation.
1. Initialize MOAC for R12
Initialize the log in and application details using below package
FND_GLOBAL.APPS_INITIALIZE(p_user
,p_responsibility
,p_Application );
Document Control Oracle Confidential - For internal use only Page 21 of 34
Initialize the Multi Org access control (MOAC) for PN application
MO_GLOBAL.INIT('SQLPN'); --This will initialize MOAC for PN application
2. Validate Variable Rent Number
This query is used when either of variable rent id or variable Rent number is given. This procedure checks if the variable_rent_id is null. IF variable_rent_id IS NULL THEN
SELECT pn_var_rents_s.NEXTVAL INTO x_var_rent_id FROM DUAL;END IF;
Check for unique varibale_rent_numberpn_var_rents_pkg.check_unique_rent_number
(l_return_status , x_var_rent_id , x_rent_num , p_org_id );
Check for org_id, if the p_org_id is Null, then get the org_id from pn_leases_all based on lease_id.SELECT org_id FROM pn_leases_all WHERE lease_id = p_lease_id;
SELECT ROWID FROM pn_var_rents_all WHERE var_rent_id = x_var_rent_id;
3. Validate Lease Id/Lease Num/Lease Name Validate if the lease details given as the parameter are existing in pn_leases_all table,
using below query. Lease_num, lease_name or lease_id can be passed as parameter for the below query.SELECT lease_id , status , payment_term_proration_rule FROM pn_leases_all WHERE lease_class_code = 'DIRECT' AND ( lease_id = x_lease_id OR UPPER (lease_num) = UPPER (p_lease_num) OR UPPER (NAME) = UPPER (p_lease_name)) AND org_id = p_org_id;
If the lease ‘Status’ is not ‘Final’, variable rent cannot be created. Thus log messages are displayed if the ‘Status’ <> ‘Final’
If the lease details are not found, log messages are displayed and variable rent cannot be created.
Lease cannot be created from CREATE_VAR_RENT API.
4. Validate Location
Location details are validated based on lease_id and Location_code or Location_id. Below query is used to validate the location details.
SELECT pl.location_code FROM pn_tenancies_all pt , pn_locations_all pl , pn_properties_all prop , fnd_lookups loc_lkp , fnd_lookups flg_lkp
Document Control Oracle Confidential - For internal use only Page 22 of 34
WHERE pt.location_id = pl.location_id AND prop.property_id(+) = pl.property_id AND NVL (pt.occupancy_date, pt.estimated_occupancy_date) BETWEEN pl.active_start_date AND pl.active_end_date AND pt.lease_id = p_lease_id AND pl.location_id = p_location_id AND loc_lkp.lookup_code = pl.location_type_lookup_code AND loc_lkp.lookup_type = 'PN_LOCATION_TYPE' AND flg_lkp.lookup_code = pt.primary_flag
AND flg_lkp.lookup_type = 'PN_YES_NO';
5. Validate Lookups
This validation procedure validates all the parameters which are based on lookup.
All the global variables listed in section 6.3.2 are validated by this procedure.
Below is the query used for validation lookup based values:
SELECT meaning , lookup_code FROM fnd_lookups WHERE lookup_type = p_lookup_type AND ( UPPER (lookup_code) = UPPER (x_lookup_code) OR UPPER (meaning) = UPPER (x_lookup_meaning)) AND enabled_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active, SYSDATE)) AND TRUNC (NVL (end_date_active, SYSDATE));
6. Validate Term Template
Term templates are validated based on the below query
SELECT term.term_template_id , term.NAME FROM pn_term_templates_all term WHERE active = 'Y' AND ( UPPER (NAME) = UPPER (x_term_template_name) OR term_template_id = x_term_template_id) AND term.term_template_type IN ('NEUTRAL', 'PAYMENT') AND org_id = p_org_id;
If the term_template details are not given, then based on the org_id the term template details are fetched send it to the private package for creating variable rent.
7. Validate Abstracted by User
This validation procedure checks if the abstracted by user exist and valid during the creation of variable rent.
SELECT user_id , user_name FROM fnd_user WHERE ( UPPER (user_name) = x_user_name OR user_id = x_user_id)
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date, SYSDATE)) AND TRUNC (NVL (end_date, SYSDATE));
8. Validate Currency
This procedure checks if the currency code passed to the PUB is valid. SELECT COUNT (1) INTO l_count FROM pn_currencies pn
Document Control Oracle Confidential - For internal use only Page 23 of 34
, gl_daily_conversion_types gl WHERE pn.conversion_type = gl.conversion_type AND currency_code = p_currency_code;
9. Validate Periods
This procedure is basically used for validating Calculation frequency, Reporting frequency and Invoicing frequency.
Below query is used to validate above fields:
SELECT meaning , lookup_code FROM fnd_lookups WHERE lookup_type = p_lookup_type AND ( UPPER (lookup_code) = UPPER (x_lookup_code) OR UPPER (meaning) = UPPER (x_lookup_meaning)) AND enabled_flag = 'Y' AND lookup_code <> 'OT'
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active,
SYSDATE)) AND TRUNC (NVL (end_date_active, SYSDATE));
10. Validate Attribute Category
Attribute category is validated against the existing descriptive flex field setups.
Flex field context value has to be fetched from FND_DESCR_FLEX_COL_USAGE_VL
11. VAL_RENT_DETAILS
This Procedure for Fetching Lease id, Location Id and Variable Rent Details
Below query is used to for Fetching Lease id, Location Id and Variable Rent Details.
SELECT var_rent_id , lease_id , location_id FROM pn_var_rents_all WHERE UPPER (rent_num) = UPPER (p_rent_num);
12. PVT_DEBUG
This Procedure for capture custom messages based on the package pn_debug.write.
13. VALIDATE_PERIOD_SET_TYPE
Procedure for validate Period set type
Below query is used to for validate Period set type.
SELECT period_type
, period_set_name
FROM gl_periods
WHERE UPPER (period_set_name) = UPPER (p_gl_period_set_name)
AND UPPER (period_type) = UPPER (p_period_type)
GROUP BY period_type
, period_set_name;
14. BREAKPOINT_EXT
Procedure for verifying whether breakpoint are generated or not
Document Control Oracle Confidential - For internal use only Page 24 of 34
Below query is used to for verifying whether breakpoints are generated or not.
SELECT COUNT (1)
FROM pn_var_bkpts_head_all
WHERE var_rent_id = p_var_rent_id;
15. CONSTR_EXT
Procedure for verifying whether Constraints are generated or not.
Below query is used to for verifying whether Constraints are generated or not.
SELECT COUNT (1)
FROM pn_var_constr_defaults_all
WHERE var_rent_id = p_var_rent_id;
6.3.2.8 PN_VRA_CLIENT_EXTN.CALCULATE_OVERRIDE Pseudo-Code
PROCEDURE calculate_override ( p_api_version IN NUMBER , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false , p_var_rent_id IN NUMBER , p_trx_header_id IN NUMBER , p_calculated_rent IN OUT NOCOPY NUMBER , p_prorated_rent IN OUT NOCOPY NUMBER , p_percent_rent IN OUT NOCOPY NUMBER , p_ytd_percent_rent IN OUT NOCOPY NUMBER , p_first_yr_rent IN OUT NOCOPY NUMBER , x_return_status OUT NOCOPY VARCHAR2 , x_msg_count OUT NOCOPY NUMBER , x_msg_data OUT NOCOPY VARCHAR2 ) IS BEGIN
<Customize the hook to override the input parameters, do not change the output parameters>.
END;
Document Control Oracle Confidential - For internal use only Page 25 of 34
7 Log Output
.........1.........2.........3.........4.........5.........6.........7.........8
Document Control Oracle Confidential - For internal use only Page 26 of 34
8 Concurrent Request Output
Not Applicable
a. Table and View Usage
Object Name Object Type Select Insert Update Delete
PN_LEASES_ALLTable
X
PN_VAR_RENTS_ALLTable
X X X
PN_VAR_RENT_DATES_ALLTable
X X
PN_VAR_LINE_DEFAULTS_ALLTable
X X
PN_VAR_BKHD_DEFAULTS_ALLTable
X X
PN_VAR_BKDT_DEFAULTS_ALLTable
X X
PN_VAR_CONSTR_DEFAULTS_ALLTable
X X
PN_VAR_ABAT_DEFAULTS_ALLTable
X X
PN_VAR_TEMPLATES_ALLTable
X
PN_SYSTEM_SETUP_OPTIONSTable
X
PN_TENANCIES_ALLTable
X
PN_LOCATIONS_ALLTable
X
PN_PROPERTIES_ALLTable
X
FND_LOOKUPS X
PN_TERM_TEMPLATES_ALL X
FND_USER X
PN_CURRENCIES X
GL_DAILY_CONVERSION_TYPES X
Document Control Oracle Confidential - For internal use only Page 27 of 34
b. Sequence Usage
Used below sequence for generating variable_rent_id. If PN_SYSTEM_OPTION contains generate auto numbering for variable rent as ‘Yes’ then the same variable_rent_id is used for variable rent number
pn_var_rents_s
c. Restart Strategy
Not Applicable
d. Crash Recovery
Not Applicable
e. Database Triggers
Not Applicable
f. Table Handlers (NEW/ CHANGED)
Not Applicable
Document Control Oracle Confidential - For internal use only Page 28 of 34
9 Seed DataNo Seeded data changes.
Document Control Oracle Confidential - For internal use only Page 29 of 34
10 Installation and Upgrade
a. Installation
There are no impacts on a fresh install.
This API will be installed as a part of patch release, thus no impact on installation or upgrade.
Document Control Oracle Confidential - For internal use only Page 30 of 34
11 Unit Test Considerations
a. Create Variable Rent
Component (s) Step No.
General Step Description Test Data Expected Results
Create Variable Rent 1 Invoke the Create Variable Rent API with input parameters.
The input parameters specified should include appropriate parameters and they should be valid.
Variable Rent, Variable Rent Lines, Breakpoints, Constraints, Allowances and Abatements in the PN System with specified data should be created in the PN System with specified data.
Create Variable Rent 2 Invoke the Create Variable Rent API with invalid input parameters.
The input parameters specified include invalid data.
Variable Rent, Variable Rent Lines, Breakpoints, Constraints, Allowances and Abatements in the PN System with specified data should not be created and the corresponding error messages should be captured in a log file.
Generate Periods 3 Invoke GENERATE_PERIODS with variable rent id as input
The input parameters specified include invalid data.
Periods should be generated
Allow Abatements 4 Invoke CREATE_BKPT_CONST_ALLOW_ABAT with valid input parameters
The input parameters specified include invalid data.
Allow abatements and constraints should be created with all the validations.
b. Update Variable Rent
Component (s) Step No.
General Step Description Test Data Expected Results
Update Variable Rent 1 Invoke the Update Variable Rent API with input parameters.
The input parameters specified should include appropriate parameters and they should be valid.
Variable Rent parameters which are different from the existing variable rent value are updated.
Update Variable Rent dates 2 Invoke the Update Variable Rent API with input parameters
The input parameters specified should include appropriate parameters and they should be valid.
Variable Rent dates parameters which are different from the existing variable rent values are updated.
Update Breakpoints 3 Invoke the Update Variable Rent API with input parameters
The input parameters specified should include
Variable Rent Breakpoint is updated with the parameters
Document Control Oracle Confidential - For internal use only Page 31 of 34
Component (s) Step No.
General Step Description Test Data Expected Results
appropriate parameters and they should be valid.
which are different from the existing variable rent breakpoint details.
Update Constraints 4 Invoke the Update Variable Rent API with input parameters
The input parameters specified should include appropriate parameters and they should be valid.
Variable Rent Constraints is updated with the input parameters which are different from the existing constraint details.
Update Abatements 5 Invoke the Update Variable Rent API with input parameters
The input parameters specified should include appropriate parameters and they should be valid.
Variable Rent Abatements is updated with the input parameters which are different from the existing constraint details.
Undo Periods 6 Invoke the Update Variable Rent API with input parameters
Input parameter p_undo_periods is passed as ‘Y’
Undo Periods is initiated.
Undo Breakpoints 7 Invoke the Update Variable Rent API with input parameters
Input parameter p_undo_breakpoints is passed as ‘Y’
Undo Breakpoint is initiated.
Undo Constraints 8 Invoke the Update Variable Rent API with input parameters
Input parameter p_undo_constraints is passed as ‘Y’
Undo Constraints is initiated.
Document Control Oracle Confidential - For internal use only Page 32 of 34
12 Open and Closed Issues
a. Closed Issues
Number Description Date Raised Priority Owner Resolution Date Closed
Priority: H= High; M=Medium, L=Low
b. Solution for issues raised by McDonald’s team.
While you should include any and all comments you have, please use the questions and guidelines for each of the applicable sections to assess the solution described in the document.
Document Section Focus Group Member Name
User Comment Product Management Comment
3.3.1 Harvinder Rakhra Variable Rent could be created even if the Base Lease is in ‘Draft’ status. There are a few features in Variable Rent which can’t be generated until lease is Finalized, like creating sales figures, but Variable Rent definition could be created in ‘Draft’ status of the base lease.
These scenarios are taken care while coding.
6.1.4 Harvinder Rakhra Both the scenarios of updating the present VRA Lines and creating a new VRA Line should be handled in Procedure Update_Vra_Rent.
In description of parameter P_VARIABLE_RENT_LINES_TBL, its mentioned: “If they are passing new variable rent lines details then API is not going to consider it.”
Same applies for Breakpoint Header, Break Point Details, Constraints and Allowances & Abatements Information.
These scenarios are taken care while coding.
Document Control Oracle Confidential - For internal use only Page 33 of 34
13 Appendix A: Parameters for PN_VAR_RENT_PUBThis section details the Input Record/Table type parameter details for PN_VAR_RENT_PUB.
13.1 CREATE_VAR_RENT Record Type
13.1. 1 Variable Rent Record Type
13.1.2 Variable Rent Lines Table Type
13.1.3 Breakpoint Header Table Type
13.1.4 Breakpoint Details Table Type
13.1.5 Constraints Table Type
13.1.6 Allowances & Abatements Table Type
Document Control Oracle Confidential - For internal use only Page 34 of 34
13.2 UPDATE_VAR_RENT Record Type
13.2.1 Update Variable Rent record type
Document Control Oracle Confidential - For internal use only Page 35 of 34
14 Appendix B: Parameters for PN_VAREN_PVT
This section details the Input Record/Table type parameters for PN_VAREN_PVT package.
14.1 CREATE_VAR_RENT Record Type
Below is the list of parameters for private package PN_VAREN_PVT.CREATE_VAR_RENT.
Parameter Usage Type Required Description
p_api_version IN NUMBER Yes API Standardp_init_msg_list IN VARCHAR2 Yes API Standardp_commit IN VARCHAR2 Yes API Standardp_validate_flag IN VARCHAR2 No Default value is 'N'p_var_rent_rec IN OUT Record Type Yes Similar to section 13.1.1p_variable_rent_lines_tbl IN OUT Table Type Yes Similar to section 13.1.2p_breakpoint_header_tbl IN OUT Table Type Yes Similar to section 13.1.3p_breakpoint_details_tbl IN OUT Table Type Yes Similar to section 13.1.4p_constraints_tbl IN OUT Table Type Yes Similar to section 13.1.5p_allow_abat_tbl IN OUT Table Type Yes Similar to section 13.1.6p_generate_periods IN OUT VARCHAR2 No Default value is 'N'p_generate_breakpoints IN VARCHAR2 No Default value is 'N'p_generate_constraints IN VARCHAR2 No Default value is 'N'
x_return_status OUT VARCHAR2
Return status. Valid values are: S (Success), E (Error), and U (Unexpected error).
x_msg_count OUT NUMBER API Standardx_msg_data OUT VARCHAR2 API Standardx_var_rent_id OUT NUMBER Variable rent Idx_var_rent_num OUT VARCHAR2 Variable Rent Number
13.1 UPDATE_VAR_RENT Record Type
Below is the list of parameters for private package PN_VAREN_PVT.UPDATE_VAR_RENT.
Document Control Oracle Confidential - For internal use only Page 36 of 34
Document Control Oracle Confidential - For internal use only Page 37 of 34
Parameter Usage Type Required Description
p_api_version IN NUMBER Yes API Standardp_init_msg_list IN VARCHAR2 Yes API Standardp_commit IN VARCHAR2 Yes API Standardp_validate_flag IN VARCHAR2 No Default value is 'N'p_upd_var_rent_rec IN OUT Record Type Yes Similar to section 13.2.1p_variable_rent_lines_tbl IN OUT Table Type Yes Similar to section 13.1.2p_breakpoint_header_tbl IN OUT Table Type Yes Similar to section 13.1.3p_breakpoint_details_tbl IN OUT Table Type Yes Similar to section 13.1.4p_constraints_tbl IN OUT Table Type Yes Similar to section 13.1.5p_allow_abat_tbl IN OUT Table Type Yes Similar to section 13.1.6p_undo_periods IN VARCHAR2 No Default value is 'N'p_ undo _breakpoints IN VARCHAR2 No Default value is 'N'p_ undo _constraints IN VARCHAR2 No Default value is 'N'
x_return_status OUT VARCHAR2
Return status. Valid values are: S (Success), E (Error), and U (Unexpected error).
x_msg_count OUT NUMBER API Standardx_msg_data OUT VARCHAR2 API Standard
top related