Batch Migration And Loading Of Oracle Fnd Data
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Batch Migration And Loading Of Oracle Fnd Data as PDF for free.
More details
- Words: 2,569
- Pages: 20
AIM TECHNICAL DESIGN Batch Migration and Loading of Oracle FND Data Team:
Technology
Creation Date:
22 May 2009
Created By:
Brendan Furey ([email protected])
Last Updated:
4 September 2009
Control:
21729560.doc
Version:
1.1
Approvals:
Document Control
Change Record Date
Author
Version
Change Reference
22-May-2009 04-Sep-2009
BP Furey BP Furey
1.0 1.1
Initial Upload User Responsibilities: Minor table numbering correction
Document Control
ii
Contents Document Control............................................................................................... .........ii Change Record........................................................................... ..........................ii Introduction.......................................................................................................... ........5 Technical Overview................................................................................ .....................6 Entity-Relationship Diagram...................................................... ...........................6 Transfer/Load Methods................................................................. ........................6 FNDLOAD Download/Upload................................................ .........................6 FNDLOAD Upload from Generated LDT File.................................................6 SQL*Loader / Oracle API Upload from CSV File.......................................... ..7 Implementation Notes........................................................................... ................7 Data Integrity............................................................................. .....................7 Values Unknown at Download Time............................................................... .7 Who Columns.................................................................................... .............7 Forcing Updates.................................................................. ...........................7 Batch Size........................................................................... ...........................8 Process Flow Diagrams............................................................... .........................8 Responsibilities Transfer............................................................................ .....9 User Responsibilities Load................................................ ...........................12 Unix Script List....................................................................................... .............13 Module Designs.......................................................................................... ...............14 Download Profile Value........................................................................ ...............14 Parameters................................................................................ ...................14 FNDLOAD Parameters.................................................................. ...............14 Process............................................................................................. ............14 Download Responsibility................................................................. ....................14 Parameters................................................................................ ...................14 FNDLOAD Parameters.................................................................. ...............14 Process............................................................................................. ............15 Download Responsibilities....................................................... ...........................15 Parameters................................................................................ ...................15 Parameters of extract_values (Local Procedure).........................................15 Process – extract_values (Local Procedure)............................... .................15 Process – Main Script.......................................................................... .........15 Upload Profiles.............................................................................................. ......16 Parameters................................................................................ ...................16 FNDLOAD Parameters.................................................................. ...............16 Process............................................................................................. ............16 Upload Responsibilities........................................................................... ............16 Parameters................................................................................ ...................17 FNDLOAD Parameters.................................................................. ...............17 Process............................................................................................. ............17 Upload User Responsibilities................................................... ...........................17 Input File Structure.................................................................... ...................17 Temporary Table Structure......................................................................... ...17 Parameters................................................................................ ...................17 API Parameters..................................................................... .......................18
Document Control
iii
Process............................................................................................. ............18 References......................................................................................... .......................20
Document Control
iv
Introduction FNDLOAD is an Oracle Unix utility that allows for the transfer of a wide range of Oracle Foundation (FND) data from one instance to another. It works by downloading the data in the source instance into a text file (the LDT file) that can then be uploaded into target instances. It is most often used for transferring concurrent program definitions one at a time between development and test and production instances. However, it can also be used when there is a requirement to transfer (or load) large batches of records such as responsibilities and user assignments, which may arise for example when new operating units are added. Its use in such cases can save a very large amount of time compared with manual migrations, but there are a number of possible pitfalls that need to be avoided, so that careful planning and design are important. This document describes our approach to such a batch transfer, explaining how FNDLOAD has been used along with Unix scripting and Oracle API calls to transfer a large batch of responsibilities (around 188), with their profile option values (around 216), and associated user assignments (around 3,638). The requirement arose from a project to add three additional operating units to a system previously transacting through a single operating unit. The document deals with technical and functional problems encountered, and how they were resolved, and includes designs for the Unix and PL/SQL scripts written.
Technical Overview
5
Technical Overview Entity-Relationship Diagram The entities that need to be transferred (or loaded) are shown on the ERD below. The entities depicted are all Oracle foundation entities, except for the two HR entities on the right, and details of the table structures can be found in REF-1. If the notation is not obvious, refer to REF-2.
Transfer/Load Methods Three basic methods are used to transfer (or load) the different entities, as follows: FNDLOAD Download/Upload Responsibility and Exclusion records are downloaded via FNDLOAD. Each responsibility is downloaded individually, and appended onto a cumulative LDT file by means of a Unix script FNDLOAD Upload from Generated LDT File Profile Value records could be downloaded via FNDLOAD, but the structure is extremely simple, and for programming and performance reasons we have chosen a different approach. A single example profile value (of each type) is downloaded, and used to create the header sections of the LDT files, including the profile option definition, but without the nested value block. In addition, the value block is extracted with the value itself replaced by a placeholder to create a template file. In the Unix script the
Technical Overview
6
appropriate value is specified, and replaces the placeholder as the template file is appended onto the cumulative LDT files, which are iniitialised using the header files. SQL*Loader / Oracle API Upload from CSV File User Responsibilities will mostly not exist on the source system, so we can’t download them. Unlike with profile values, there may be a large number of distinct users, so we do not want to hard-code them in our script. Therefore the best approach seems to be to load from a CSV file and upload into Oracle using the same Oracle API that FNDLOAD uses. This seems simpler than generating the LDT file in order to use FNDLOAD for the upload.
Implementation Notes Data Integrity FNDLOAD allows for the download of individual entity instances or groups of entity instances. For example, all responsibilities couild be downloaded within a specified application. However, this creates a serious risk of uploading bad data from development into production. For this reason, we download only specified individual entity instances that are assumed to have been fully tested. We then combine the LDT files into one file (or a small number of files) for installation convenience. A second risk factor arises from the fact that FNDLOAD downloads referenced entity instances as well as the named entity instance. For example, downloading a concurrent program results in all value sets referenced also being downloaded into the LDT file. Since referenced entities may not be part of the relevant development project, they may have been separately modified. In general, only what has been modified as part of the development, and hence tested, should be uploaded. For this reason, we post-process each downloaded LDT file to remove referenced entities (but not the references to them of course). Values Unknown at Download Time Certain values downloaded may not be appropriate for uploading. For example, effective start dates should not be earlier than the installation date, for auditing reasons; also values that are sequencegenerated references and are new need to be different in production from development (one of our additional operating units falls into this category, the other two already existed as organisations). Who columns are another case, as discussed in the next section. For these values, we substitute placeholders via our download scripts, and replace them with the correct values in the upload scripts. Who Columns ‘Who’ columns are standard Oracle columns storing the users who created and last updated the record, with the datetimes. FNDLOAD normally uploads the update values from the source system, and the same values for the creation columns for new records. We believe this is inappropriate for system-generated updates (for auditing reasons), and so we substitute SYSADMIN for the user, and use a placeholder for the update/creation date for processing as discussed in the previous section. Forcing Updates FNDLOAD uses an algorithm to determine whether an entity instance that exists on the target system should be overwritten or not, depending on such things as the update user and datetime on each side. We are in fact only uploading new records, but in the case of updates, as long as the data integrity measures outlined above have been taken, it would be appropriate to pass the parameter CUSTOM_MODE=FORCE, which causes the updates to happen disregarding the usual algorithm.
Technical Overview
7
Batch Size FNDLOAD was found to fail with an error message concerning a variable’s size being exceeded when the number of top-level entity instances in the LDT file exceeded about one hundred. Therefore files were split where necessary.
Process Flow Diagrams The diagrams below show the high level flow of data and processes. The flowchart convention of rectangles for processes and parallelograms for datastores is followed. Red rectangles correspond to Unix scripts that we have developed, and that are described in the detailed design sections following.
Technical Overview
8
Responsibilities Transfer
Notes The diagram shows the processes involved in creating the three header files and the template that will be used in the main download script.
Technical Overview
9
Notes •
The diagram shows the processes involved in the main download script, with its inputs and outputs.
•
Note that the Responsibility/Profile List datastore is actually contained in the script itself.
•
The output files may be split up into multiple smaller files where necessary, and the profiles files can be combined if desired
Technical Overview
10
Notes •
The diagram shows the processes involved in the main responsibility upload scripts, with their inputs and outputs.
•
The processes are repeated for each input file where necessary
Technical Overview
11
User Responsibilities Load
Notes The diagram shows the processes involved in the main user responsibility upload script, with its inputs and outputs.
Technical Overview
12
Unix Script List Description Download Profile Value Download Responsibility Download Responsibilities Upload Profiles Upload Responsibilities Upload User Responsibilities
Script XX_DownPR.x XX_DownRy.x XX_GW_Down_CRP_RSP.x XX_UpPR.x XX_UpRy.x XX_UpURG.x
Technical Overview
13
Module Designs Download Profile Value Parameters Name Apps Password Profile Name Responsibility Key
Description Password of apps Oracle user Internal name of profile Responsibility key
FNDLOAD Parameters Position Name 1 User/Password 2 3 4 Upload/Download? 5 Control File 6 LDT File 7 Entity Code 8 Entity Value 9 Profile Level 10 Profile Level Value
Value apps/[Apps Password] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct [Profile Name]_PR.ldt PROFILE PROFILE_NAME=[Profile Name] LEV=RESPONSIBILITY LEV_NAME=[Responsibility Key]
Process •
Validate parameters
•
Download using FNDLOAD
•
Search LDT file and report on success or failure
Download Responsibility Parameters Name Apps Password Responsibility Key Application Short Name
Description Password of apps Oracle user Responsibility key Application short name
FNDLOAD Parameters Position Name 1 User/Password 2 3 4 Upload/Download? 5 Control File 6 LDT File 7 Entity Code 8 Entity Value 9 Profile Level Value
Value apps/[Apps Password] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct [Responsibility Key]_Ry.ldt FND_RESPONSIBILITY RESP_KEY=[Responsibility Key] APPLICATION_SHORT_NAME=[Application Short Name]
Technical Overview
14
Process •
Validate parameters
•
Download using FNDLOAD to temporary LDT file
•
Remove all Application blocks from the temporary LDT file and copy to the output LDT file
•
List the Applications that were removed
•
Search LDT file and report on success or failure, writing out the number of function and menu exclusions
Download Responsibilities This script creates the LDT files for all the responsibilities included in the file, together with LDT files for the Operating Unit and HR User type profile values. A local procedure is defined to do the processing for each set of responsibilities. It is passed the Application short name, the Operating Unit Id, a flag to indicate whether to set the HR User Type profile or not (if so, it’s set to ‘PER’), followed by the list of responsibility keys. Parameters Name Apps Password
Description Password of apps Oracle user
Parameters of extract_values (Local Procedure) Position Name Value 1 Application Short Name Application short name 2 Org Id Org Id (may be placeholder or actual value) 3 User Type Profile Flag Y/N [Responsibility Key] list (space separated, list within single 4 Responsibility List quotes) Process – extract_values (Local Procedure) •
Loop over the Responsibility List passed o
Download the responsibility, using XX_DownRy.x
o
Append the tail of the file to the cumulative output LDT file
o
Append the profile template file to the cumulative Operating Unit LDT file, replacing placeholders with actual values (or Org Id placeholder)
o
If User Type Profile Flag = ‘Y’ Then
o •
Append the profile template file to the cumulative HR User Type LDT file, replacing placeholders with actual values
End If
End Loop
Process – Main Script •
Validate parameters
Technical Overview
15
•
Assign values to local variables, including actual Operating Unit ids, or placeholders as applicable
•
Define extract_values local procedure
•
Call extract_values as many times as required, passing parameters as specified above
•
Replace owner with SYSADMIN in all output files, and last update date with placeholder sysdate
•
Add an end line to the two profile LDT files
•
List the responsibilities and profiles processed to a log file
Upload Profiles This script uses FNDLOAD to upload from a profile values LDT file that may contain either or both types of profile. Parameters Name Apps Password Input File Name
Description Password of apps Oracle user Profile values LDT file name
FNDLOAD Parameters Position Name 1 User/Password 2 3 4 Upload/Download? 5 Control File 6 LDT File
Value apps/[Apps Password] 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct [LDT File Name]
Process •
Validate parameters
•
Validate file existences and Oracle password
•
Obtain new Operating Unit id (SQL session)
•
Replace Operating Unit and sysdate placeholders in input file with new Operating Unit id and today’s date respectively, to create the actual LDT file to upload
•
Upload using FNDLOAD
•
Obtain lists of profiles and responsibility keys from the input file into local variables
•
List the profile values uploaded, passing to SQL the local variables as lexical parameters (SQL session)
Upload Responsibilities This script uses FNDLOAD to upload from a responsibilities LDT file.
Technical Overview
16
Parameters Name Apps Password Input File Name
Description Password of apps Oracle user Responsibilities LDT file name
FNDLOAD Parameters Position Name 1 User/Password 2 3 4 Upload/Download? 5 Control File 6 LDT File
Value apps/[Apps Password] 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct [LDT File Name]
Process •
Validate parameters
•
Validate file existences and Oracle password
•
Replace sysdate placeholders in input file with today’s date, to create the actual LDT file
•
Upload using FNDLOAD
•
Obtain list of responsibility keys from the input file into local variable
•
List the responsibilities uploaded, with the number of function and menu exclusions, passing to SQL the local variable as a lexical parameter (SQL session)
Upload User Responsibilities This script uses an Oracle API, FND_User_Resp_Groups_API.Load_Row, to upload user responsibilities, using SQL Loader to load them from a comma-separated file into a temporary table that is then processed in a PL/SQL block. Input File Structure Name Application Name Responsibility Name User Name Temporary Table Structure Name app_name responsibility_name user_name status error_msg Parameters Name Apps Password Input File Name
Description Application name Responsibility name User Name
Type VARCHAR2(60), VARCHAR2(60) VARCHAR2(60) VARCHAR2(1) VARCHAR2(2000)
Description Application Name Responsibility Name User Name Load status Error message
Description Password of apps Oracle user Input CSV file name
Technical Overview
17
API Parameters Position Name 1 User Name 2 Responsibility Key 3 Application Short Name 4 Security Group 5 Owner 6 Start Date 7 End Date 8 Description 9 Last Update Date
Value [User Name] [Responsibility Key] [Application Short Name] ‘STANDARD’ ‘SYSADMIN’ SYSDATE NULL 'Added by API' SYSDATE
Process •
Create temporary SQL Loader control file
•
Create temporary SQL Loader table (SQL session)
•
Call SQL Loader to load input data file into temporary table
•
Start SQL session
•
List the records in temporary table
•
Start PL/SQL Block
Loop over temporary table, outer-joining User, Responsibility, and User Assignment records If User Assignment already exists Then Set status to N Elsif User or Responsibility do not exist Then Set status to M Else Call API to load Assignment, with start date today and end date null Set status to C End If Update table with status End Loop •
End PL/SQL Block
•
Summarise records by status
•
List successful and failed uploads
•
Commit
•
End SQL session
Technical Overview
18
•
Remove temporary SQL Loader control file
•
Drop temporary SQL Loader table (SQL session)
Technical Overview
19
References REF
Document
REF-1
Oracle, eTRM, R11.5.10
REF-2
A Structured Approach to SQL Query Design
Location https://etrm.oracle.com/pls/trm11510/etr m_search.search http://www.pdfcoke.com/doc/15723877/AStructured-Approach-to-SQL-QueryDesign
Technical Overview
20
Technology
Creation Date:
22 May 2009
Created By:
Brendan Furey ([email protected])
Last Updated:
4 September 2009
Control:
21729560.doc
Version:
1.1
Approvals:
Document Control
Change Record Date
Author
Version
Change Reference
22-May-2009 04-Sep-2009
BP Furey BP Furey
1.0 1.1
Initial Upload User Responsibilities: Minor table numbering correction
Document Control
ii
Contents Document Control............................................................................................... .........ii Change Record........................................................................... ..........................ii Introduction.......................................................................................................... ........5 Technical Overview................................................................................ .....................6 Entity-Relationship Diagram...................................................... ...........................6 Transfer/Load Methods................................................................. ........................6 FNDLOAD Download/Upload................................................ .........................6 FNDLOAD Upload from Generated LDT File.................................................6 SQL*Loader / Oracle API Upload from CSV File.......................................... ..7 Implementation Notes........................................................................... ................7 Data Integrity............................................................................. .....................7 Values Unknown at Download Time............................................................... .7 Who Columns.................................................................................... .............7 Forcing Updates.................................................................. ...........................7 Batch Size........................................................................... ...........................8 Process Flow Diagrams............................................................... .........................8 Responsibilities Transfer............................................................................ .....9 User Responsibilities Load................................................ ...........................12 Unix Script List....................................................................................... .............13 Module Designs.......................................................................................... ...............14 Download Profile Value........................................................................ ...............14 Parameters................................................................................ ...................14 FNDLOAD Parameters.................................................................. ...............14 Process............................................................................................. ............14 Download Responsibility................................................................. ....................14 Parameters................................................................................ ...................14 FNDLOAD Parameters.................................................................. ...............14 Process............................................................................................. ............15 Download Responsibilities....................................................... ...........................15 Parameters................................................................................ ...................15 Parameters of extract_values (Local Procedure).........................................15 Process – extract_values (Local Procedure)............................... .................15 Process – Main Script.......................................................................... .........15 Upload Profiles.............................................................................................. ......16 Parameters................................................................................ ...................16 FNDLOAD Parameters.................................................................. ...............16 Process............................................................................................. ............16 Upload Responsibilities........................................................................... ............16 Parameters................................................................................ ...................17 FNDLOAD Parameters.................................................................. ...............17 Process............................................................................................. ............17 Upload User Responsibilities................................................... ...........................17 Input File Structure.................................................................... ...................17 Temporary Table Structure......................................................................... ...17 Parameters................................................................................ ...................17 API Parameters..................................................................... .......................18
Document Control
iii
Process............................................................................................. ............18 References......................................................................................... .......................20
Document Control
iv
Introduction FNDLOAD is an Oracle Unix utility that allows for the transfer of a wide range of Oracle Foundation (FND) data from one instance to another. It works by downloading the data in the source instance into a text file (the LDT file) that can then be uploaded into target instances. It is most often used for transferring concurrent program definitions one at a time between development and test and production instances. However, it can also be used when there is a requirement to transfer (or load) large batches of records such as responsibilities and user assignments, which may arise for example when new operating units are added. Its use in such cases can save a very large amount of time compared with manual migrations, but there are a number of possible pitfalls that need to be avoided, so that careful planning and design are important. This document describes our approach to such a batch transfer, explaining how FNDLOAD has been used along with Unix scripting and Oracle API calls to transfer a large batch of responsibilities (around 188), with their profile option values (around 216), and associated user assignments (around 3,638). The requirement arose from a project to add three additional operating units to a system previously transacting through a single operating unit. The document deals with technical and functional problems encountered, and how they were resolved, and includes designs for the Unix and PL/SQL scripts written.
Technical Overview
5
Technical Overview Entity-Relationship Diagram The entities that need to be transferred (or loaded) are shown on the ERD below. The entities depicted are all Oracle foundation entities, except for the two HR entities on the right, and details of the table structures can be found in REF-1. If the notation is not obvious, refer to REF-2.
Transfer/Load Methods Three basic methods are used to transfer (or load) the different entities, as follows: FNDLOAD Download/Upload Responsibility and Exclusion records are downloaded via FNDLOAD. Each responsibility is downloaded individually, and appended onto a cumulative LDT file by means of a Unix script FNDLOAD Upload from Generated LDT File Profile Value records could be downloaded via FNDLOAD, but the structure is extremely simple, and for programming and performance reasons we have chosen a different approach. A single example profile value (of each type) is downloaded, and used to create the header sections of the LDT files, including the profile option definition, but without the nested value block. In addition, the value block is extracted with the value itself replaced by a placeholder to create a template file. In the Unix script the
Technical Overview
6
appropriate value is specified, and replaces the placeholder as the template file is appended onto the cumulative LDT files, which are iniitialised using the header files. SQL*Loader / Oracle API Upload from CSV File User Responsibilities will mostly not exist on the source system, so we can’t download them. Unlike with profile values, there may be a large number of distinct users, so we do not want to hard-code them in our script. Therefore the best approach seems to be to load from a CSV file and upload into Oracle using the same Oracle API that FNDLOAD uses. This seems simpler than generating the LDT file in order to use FNDLOAD for the upload.
Implementation Notes Data Integrity FNDLOAD allows for the download of individual entity instances or groups of entity instances. For example, all responsibilities couild be downloaded within a specified application. However, this creates a serious risk of uploading bad data from development into production. For this reason, we download only specified individual entity instances that are assumed to have been fully tested. We then combine the LDT files into one file (or a small number of files) for installation convenience. A second risk factor arises from the fact that FNDLOAD downloads referenced entity instances as well as the named entity instance. For example, downloading a concurrent program results in all value sets referenced also being downloaded into the LDT file. Since referenced entities may not be part of the relevant development project, they may have been separately modified. In general, only what has been modified as part of the development, and hence tested, should be uploaded. For this reason, we post-process each downloaded LDT file to remove referenced entities (but not the references to them of course). Values Unknown at Download Time Certain values downloaded may not be appropriate for uploading. For example, effective start dates should not be earlier than the installation date, for auditing reasons; also values that are sequencegenerated references and are new need to be different in production from development (one of our additional operating units falls into this category, the other two already existed as organisations). Who columns are another case, as discussed in the next section. For these values, we substitute placeholders via our download scripts, and replace them with the correct values in the upload scripts. Who Columns ‘Who’ columns are standard Oracle columns storing the users who created and last updated the record, with the datetimes. FNDLOAD normally uploads the update values from the source system, and the same values for the creation columns for new records. We believe this is inappropriate for system-generated updates (for auditing reasons), and so we substitute SYSADMIN for the user, and use a placeholder for the update/creation date for processing as discussed in the previous section. Forcing Updates FNDLOAD uses an algorithm to determine whether an entity instance that exists on the target system should be overwritten or not, depending on such things as the update user and datetime on each side. We are in fact only uploading new records, but in the case of updates, as long as the data integrity measures outlined above have been taken, it would be appropriate to pass the parameter CUSTOM_MODE=FORCE, which causes the updates to happen disregarding the usual algorithm.
Technical Overview
7
Batch Size FNDLOAD was found to fail with an error message concerning a variable’s size being exceeded when the number of top-level entity instances in the LDT file exceeded about one hundred. Therefore files were split where necessary.
Process Flow Diagrams The diagrams below show the high level flow of data and processes. The flowchart convention of rectangles for processes and parallelograms for datastores is followed. Red rectangles correspond to Unix scripts that we have developed, and that are described in the detailed design sections following.
Technical Overview
8
Responsibilities Transfer
Notes The diagram shows the processes involved in creating the three header files and the template that will be used in the main download script.
Technical Overview
9
Notes •
The diagram shows the processes involved in the main download script, with its inputs and outputs.
•
Note that the Responsibility/Profile List datastore is actually contained in the script itself.
•
The output files may be split up into multiple smaller files where necessary, and the profiles files can be combined if desired
Technical Overview
10
Notes •
The diagram shows the processes involved in the main responsibility upload scripts, with their inputs and outputs.
•
The processes are repeated for each input file where necessary
Technical Overview
11
User Responsibilities Load
Notes The diagram shows the processes involved in the main user responsibility upload script, with its inputs and outputs.
Technical Overview
12
Unix Script List Description Download Profile Value Download Responsibility Download Responsibilities Upload Profiles Upload Responsibilities Upload User Responsibilities
Script XX_DownPR.x XX_DownRy.x XX_GW_Down_CRP_RSP.x XX_UpPR.x XX_UpRy.x XX_UpURG.x
Technical Overview
13
Module Designs Download Profile Value Parameters Name Apps Password Profile Name Responsibility Key
Description Password of apps Oracle user Internal name of profile Responsibility key
FNDLOAD Parameters Position Name 1 User/Password 2 3 4 Upload/Download? 5 Control File 6 LDT File 7 Entity Code 8 Entity Value 9 Profile Level 10 Profile Level Value
Value apps/[Apps Password] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct [Profile Name]_PR.ldt PROFILE PROFILE_NAME=[Profile Name] LEV=RESPONSIBILITY LEV_NAME=[Responsibility Key]
Process •
Validate parameters
•
Download using FNDLOAD
•
Search LDT file and report on success or failure
Download Responsibility Parameters Name Apps Password Responsibility Key Application Short Name
Description Password of apps Oracle user Responsibility key Application short name
FNDLOAD Parameters Position Name 1 User/Password 2 3 4 Upload/Download? 5 Control File 6 LDT File 7 Entity Code 8 Entity Value 9 Profile Level Value
Value apps/[Apps Password] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct [Responsibility Key]_Ry.ldt FND_RESPONSIBILITY RESP_KEY=[Responsibility Key] APPLICATION_SHORT_NAME=[Application Short Name]
Technical Overview
14
Process •
Validate parameters
•
Download using FNDLOAD to temporary LDT file
•
Remove all Application blocks from the temporary LDT file and copy to the output LDT file
•
List the Applications that were removed
•
Search LDT file and report on success or failure, writing out the number of function and menu exclusions
Download Responsibilities This script creates the LDT files for all the responsibilities included in the file, together with LDT files for the Operating Unit and HR User type profile values. A local procedure is defined to do the processing for each set of responsibilities. It is passed the Application short name, the Operating Unit Id, a flag to indicate whether to set the HR User Type profile or not (if so, it’s set to ‘PER’), followed by the list of responsibility keys. Parameters Name Apps Password
Description Password of apps Oracle user
Parameters of extract_values (Local Procedure) Position Name Value 1 Application Short Name Application short name 2 Org Id Org Id (may be placeholder or actual value) 3 User Type Profile Flag Y/N [Responsibility Key] list (space separated, list within single 4 Responsibility List quotes) Process – extract_values (Local Procedure) •
Loop over the Responsibility List passed o
Download the responsibility, using XX_DownRy.x
o
Append the tail of the file to the cumulative output LDT file
o
Append the profile template file to the cumulative Operating Unit LDT file, replacing placeholders with actual values (or Org Id placeholder)
o
If User Type Profile Flag = ‘Y’ Then
o •
Append the profile template file to the cumulative HR User Type LDT file, replacing placeholders with actual values
End If
End Loop
Process – Main Script •
Validate parameters
Technical Overview
15
•
Assign values to local variables, including actual Operating Unit ids, or placeholders as applicable
•
Define extract_values local procedure
•
Call extract_values as many times as required, passing parameters as specified above
•
Replace owner with SYSADMIN in all output files, and last update date with placeholder sysdate
•
Add an end line to the two profile LDT files
•
List the responsibilities and profiles processed to a log file
Upload Profiles This script uses FNDLOAD to upload from a profile values LDT file that may contain either or both types of profile. Parameters Name Apps Password Input File Name
Description Password of apps Oracle user Profile values LDT file name
FNDLOAD Parameters Position Name 1 User/Password 2 3 4 Upload/Download? 5 Control File 6 LDT File
Value apps/[Apps Password] 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct [LDT File Name]
Process •
Validate parameters
•
Validate file existences and Oracle password
•
Obtain new Operating Unit id (SQL session)
•
Replace Operating Unit and sysdate placeholders in input file with new Operating Unit id and today’s date respectively, to create the actual LDT file to upload
•
Upload using FNDLOAD
•
Obtain lists of profiles and responsibility keys from the input file into local variables
•
List the profile values uploaded, passing to SQL the local variables as lexical parameters (SQL session)
Upload Responsibilities This script uses FNDLOAD to upload from a responsibilities LDT file.
Technical Overview
16
Parameters Name Apps Password Input File Name
Description Password of apps Oracle user Responsibilities LDT file name
FNDLOAD Parameters Position Name 1 User/Password 2 3 4 Upload/Download? 5 Control File 6 LDT File
Value apps/[Apps Password] 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct [LDT File Name]
Process •
Validate parameters
•
Validate file existences and Oracle password
•
Replace sysdate placeholders in input file with today’s date, to create the actual LDT file
•
Upload using FNDLOAD
•
Obtain list of responsibility keys from the input file into local variable
•
List the responsibilities uploaded, with the number of function and menu exclusions, passing to SQL the local variable as a lexical parameter (SQL session)
Upload User Responsibilities This script uses an Oracle API, FND_User_Resp_Groups_API.Load_Row, to upload user responsibilities, using SQL Loader to load them from a comma-separated file into a temporary table that is then processed in a PL/SQL block. Input File Structure Name Application Name Responsibility Name User Name Temporary Table Structure Name app_name responsibility_name user_name status error_msg Parameters Name Apps Password Input File Name
Description Application name Responsibility name User Name
Type VARCHAR2(60), VARCHAR2(60) VARCHAR2(60) VARCHAR2(1) VARCHAR2(2000)
Description Application Name Responsibility Name User Name Load status Error message
Description Password of apps Oracle user Input CSV file name
Technical Overview
17
API Parameters Position Name 1 User Name 2 Responsibility Key 3 Application Short Name 4 Security Group 5 Owner 6 Start Date 7 End Date 8 Description 9 Last Update Date
Value [User Name] [Responsibility Key] [Application Short Name] ‘STANDARD’ ‘SYSADMIN’ SYSDATE NULL 'Added by API' SYSDATE
Process •
Create temporary SQL Loader control file
•
Create temporary SQL Loader table (SQL session)
•
Call SQL Loader to load input data file into temporary table
•
Start SQL session
•
List the records in temporary table
•
Start PL/SQL Block
Loop over temporary table, outer-joining User, Responsibility, and User Assignment records If User Assignment already exists Then Set status to N Elsif User or Responsibility do not exist Then Set status to M Else Call API to load Assignment, with start date today and end date null Set status to C End If Update table with status End Loop •
End PL/SQL Block
•
Summarise records by status
•
List successful and failed uploads
•
Commit
•
End SQL session
Technical Overview
18
•
Remove temporary SQL Loader control file
•
Drop temporary SQL Loader table (SQL session)
Technical Overview
19
References REF
Document
REF-1
Oracle, eTRM, R11.5.10
REF-2
A Structured Approach to SQL Query Design
Location https://etrm.oracle.com/pls/trm11510/etr m_search.search http://www.pdfcoke.com/doc/15723877/AStructured-Approach-to-SQL-QueryDesign
Technical Overview
20
Related Documents
Data Storage And Migration
May 2020 10
Migration To Oracle File
November 2019 19
Data Loading Training Manual
November 2019 2
What Is Data Migration
December 2019 14
Batch 38 Cmc Data
June 2020 1More Documents from ""
Oracle Forms Generic Utp
May 2020 10
A Structured Approach To Sql Query Design
May 2020 9
