Showing posts with label Oracle Application. Show all posts
Showing posts with label Oracle Application. Show all posts

Friday, 12 January 2018

MOAC

What is MOAC?

Multi-Org or multiple organization access (MOAC) is basically an ability to access multiple operating units from a single application responsibility.

What are its advantages?

Multi-Org Access Control (MOAC) enables companies that have implemented a Shared Services operating model to efficiently process business transactions by allowing them to access, process and report on data for an unlimited number of operating units within a single applications responsibility.
This increases the productivity of Shared Service Centers, as users no longer have to switch application responsibilities when processing transactions for multiple operating units at a time.
Ability to view data from multiple operating units from a single responsibility, gives users more information. This enables them to make better decisions.
The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them.

SELECT   psp.SECURITY_PROFILE_NAME,
         psp.SECURITY_PROFILE_ID,
         hou.NAME,
         hou.ORGANIZATION_ID
FROM     PER_SECURITY_PROFILES psp,
         PER_SECURITY_ORGANIZATIONS pso,
         HR_OPERATING_UNITS hou
WHERE    pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
         AND pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

There are three Profile Options you need to be aware of related to Multi-Org that should be set at the Responsibility Level.

MO: Security Profile– Always evaluated first.
MO: Operating Unit– Secondary priority being evaluated after ‘MO: Security Profile’
MO: Default Operating Unit– Sets the default Operating Unit for transactions when running under a Security Profile.

How it is done in R12?

In Release 12, one creates a Security Profile and assigns as many operating units as you required. One can tie that security profile to a single responsibility using a profile option called MO: Security Profile. For example, you could assign the security profile to the EMEA Payables responsibility to allow that responsibility to process invoices across all operating units.

In Release 12, define a security profile in HR using the Security profile form or the Global Security profile form, and assign all of the operating units that one would want a responsibility to access. The one needs to run a concurrent request called “Run Security List Maintenance” from HR which will make those security profile available and allow one to assign them to a responsibility via a profile option called MO: Security Profile.

One can define another profile option called MO: Default Operating Unit which is optional and allows one to specify a default operating unit that will be the default when you open different subledger application forms.

Multi-Org in EBS - Technical Implementation

For ERP applications, data partitioning is performed by database views. These views reside in the APPS Oracle schema and derive the appropriate operating unit context from an RDBMS variable.

All applications code is run against the APPS schema. The Applications database architecture is now the same for a Multiple Organizations and non-Multiple Organizations implementation.

Multi Organization Tables

Multiple Organizations in Oracle Applications is enabled by partitioning some database tables by operating unit. Other tables are shared across operating units (and thus across sets of books).
In general, the following criteria determine if a table would be partitioned:
The table contains a GL Account Code (code combination ID).
There is a business reason for the table to be partitioned (for example, the entity should not be shared).
The table contains transaction data.
The table is an interface table where data being loaded is partitioned.

Applications with Partitioned tables

  • Oracle Cash Management
  • Oracle Order Management and Shipping Execution
  • Oracle Payables
  • Oracle Property Manager
  • Oracle Projects
  • Oracle Purchasing
  • Oracle Release Management
  • Oracle Receivables
  • Oracle Sales Compensation
  • Oracle Sales and Marketing
  • Oracle Service
  • European Localizations
  • Latin America Localizations
  • Regional Localizations


RDBMS Variable

A global variable exists in the Oracle database called CLIENT_INFO, which is 64 bytes long. The first 10 bytes are used to store the operating unit ID (or ORG_ID) for the Multiple Organization Support feature. The CLIENT_INFO context is derived from a profile option that the user sets for each responsibility as part of the Multi-Org setup steps.

Org_id column in the warehouse contains the Operating Unit ID
Organization_id column contains the warehouse / Inventory Org.

select * from <table name>_ALL
where ORG_ID = SUBSTRB(USERENV('CLIENT_INFO'),1,10);

Saturday, 18 November 2017

FNDLOAD Commands:Others - Printer Styles, Audit Group, Audit Schema, NLS Language

Menu -> Printer Styles

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

FNDLOAD <username>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt

Audit Group

FNDLOAD <username>/<password> O Y DOWNLOAD $FND_TOP/patch/115/import/affaudit.lct file_name.ldt FND_AUDIT_GROUPS APPLICATION_SHORT_NAME="prod" GROUP_NAME="Audit Group Name"

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/affaudit.lct file_name.ldt

Audit Schema

FNDLOAD <username>/<password> O Y DOWNLOAD $FND_TOP/patch/115/import/affaudit.lct file_name.ldt FND_AUDIT_SCHEMAS  ORACLE_USERNAME= "ORACLE_USERNAME" APPLICATION_SHORT_NAME="prod" 

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/affaudit.lct file_name.ldt

NLS Language

FNDLOAD <username>/<password> 0 Y UPLOAD <controlfile.lct> <datafile.ldt> \
- UPLOAD_MODE=NLS CUSTOM_MODE=FORCE WARNINGS=TRUE

FNDLOAD Commands: Work Flows Migration

Menu -> Work Flows Migration

WFLOAD <username>/<password> 0 Y DOWNLOAD <filepath.wft> <item type>

WFLOAD <username>/<password> 0 Y UPLOAD <filepath.wft>

FNDLOAD Commands: DATA Template and RTF Template

Menu -> DATA_TEMPLATE (Data Source .xml file)

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE XML -FILE_NAME $DATA_FILE_PATH/$DATA_FILE_NAME.xml -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

RTF TEMPLATE (Report Layout .rtf file)

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -TERRITORY US -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE RTF -FILE_NAME $RTF_FILE_PATH/$RTF_FILE_NAME.rtf -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

FNDLOAD Commands: Data Definition and Associated Template

Menu -> Data Definition and Associated Template

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_SOURCE_CODE'

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt

FNDLOAD Commands: Users

Menu -> User

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_USER

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt

Notes for using FNDLOAD against FND_USER:-

1. After uploading using FNDLOAD, user will be promoted to change their password again during their next signon attempt.
2. All the responsibilities will be extracted by FNDLOAD alongwith User Definition in FND_USER
3. In the Target Environment, make sure that you have done FNDLOAD for new responsibilities prior to running FNDLOAD on users.

FNDLOAD Commands: Responsibilities/ Responsibilities with all Security Groups

Menu -> Responsibilities

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct xxaoa_file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt

Responsibilities with all Security Groups

FNDLOAD <username>/<PASSWORD> 0 Y DOWNLOAD FND_TOP/patch/115/import/afscursp.lct <USER>.ldt FND_USER USER_NAME="<USER>" SECURITY_GROUP=% DATA_GROUP_NAME=%

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct <USER>.ldt

FNDLOAD Commands: Menus

Menu -> Menus

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt MENU MENU_NAME="XXCUST_MENU"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt

FNDLOAD Commands: Forms/Functions/Personalizations

Menu -> Forms/Functions/Personalizations

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <xxaoa_file_name.ldt> FND_FORM_CUSTOM_RULES form_name=<form name>
OR
FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct xxaoa_file_name.ldt FUNCTION FUNCTION_NAME=<function_name>
OR
FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct xxaoa_file_name.ldt FORM FORM_NAME=<form_name>
OR
FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <xxaoa_file_name.ldt> FND_FORM_CUSTOM_RULES function_name=<function name>

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: Request Group

Menu -> Request Group

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct xxaoa_file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group"APPLICATION_SHORT_NAME="prod"

FNDLOAD <username>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct xxaoa_file_name.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: Request Set and Links

Menu -> Request Set 

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_CUSTOM_RS.ldt REQ_SET REQUEST_SET_NAME='REQUEST_SET_NAME'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XX_CUSTOM_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Link

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_LNK.ldt REQ_SET_LINKS REQUEST_SET_NAME='XXCUST_LNK'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XXCUST_LNK.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: Concurrent Program

Menu -> Concurrent Program

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: D2k Forms

Menu -> D2K FORMS

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt FORM FORM_NAME="FORM_NAME" 
      
$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: DFF/KFF - Specific Context/ Multiple Flexfields

Menu -> DFF with all of specific Contexts

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=:COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt

DFF – Download Multiple Flexfields

Use a combination of APPLICATION_SHORT_NAME and DESCRIPTIVE_FLEXFIELD_NAME names ie. APPLICATION_SHORT_NAME=PER >> will download all PER flexfields DESCRIPTIVE_FLEXFIELD_NAME=PER_% >> will download all flexfields that start with ‘PER_’.

FNDLOAD <username>/<password> O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME="PER_%"
FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt

KFF 

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=:COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt

FNDLOAD Commands: Alerts

Menu -> Alerts

FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - Alert Name"

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE

FNDLOAD Commands: Value sets / Value Set with Values

Menu -> Value Set

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Value Set with Values

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: Messages

Menu -> FND Message

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Command: Profiles

Menu -> Profiles

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt -WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Command: Lookups

Menu -> Lookups

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE