Conceptually : Record to Reporting Cycle – R2R

Record to report or R2R is the management process for providing strategic, financial and operational feedback to understand how a business is performing.

This process involves collecting, transforming and delivering relevant, timely and accurate information to all stakeholders inside and outside the organization, to provide insight into how their expectations have been met.

It covers the steps involved in preparing and reporting the overall accounts which are typically stored in a general or nominal ledger and managed by a comptroller. The detailed steps involved are:

• data extraction
• data collection
• data validation
• data transformation (generation of voucher)
• voucher posting (to general ledger)
• storing vouchers in de-normalized and compressed format
• generating analysis account trial balance or consolidated analysis account trial balance
• generating user-defined financial and management reports

In ERP systems , This is part  month end  process in which basically user will close all the periods  and import all the journals in GL from other modules
(Payable, Receivable, Fixed Assets etc.). Then run all the month end GL reports like Trial Balance, Account Analysis  etc.

Following are broader division of activity involved in Record to Report Cycle

Reconciliations

• Intercompany
• Multiple systems
• Bank
• Subsidiary to main set of books
• Customer
• Vendor

General Accounting

• Journal Entries
• Intercompany accounting
• Book closures
• Trial Balance preparation
• Accruals
• Consolidations
• Cost Accounting

Record to Report process might seem simple but it require considerable efforts. It is the only process scrutinized by outside auditors who draw conclusions about controls in place during financial close and reporting cycle.

References:

https://knoworacle.wordpress.com/2015/06/11/conceptually-record-to-reporting-cycle-r2r/

WIP JOB Related Scripts

Query to find WIP Job details:
SELECT
 E.WIP_ENTITY_ID,
 E.WIP_ENTITY_NAME Job,
 M.SEGMENT1 ITEM, --M.INVENTORY_ITEM_ID,
 M.DESCRIPTION Item_Desc,
 D.DESCRIPTION JOB_DESCRIPTION,
 M.PRIMARY_UOM_CODE UOM,
 D.START_QUANTITY Start_Quantity,
 D.JOB_TYPE Job_Type,
 D.COMPLETION_SUBINVENTORY Subinventory,
 D.COMPLETION_LOCATOR_ID Compl_Loc_Id,
 D.BOM_REVISION BOM_Revision,
 D.BOM_REVISION_DATE BOM_REVISION_DATE,
 SUBSTR(TO_CHAR(D.BOM_REVISION_DATE,'HH24:MI'),1,5) BOM_TIME,
 D.SCHEDULED_START_DATE Scheduled_Start,
 SUBSTR(TO_CHAR(D.SCHEDULED_START_DATE,'HH24:MI'),1,5) START_TIME,
 LU.MEANING Status,
 D.ROUTING_REVISION Routing_Revision,
 D.ROUTING_REVISION_DATE ROUTING_REVISION_DATE,
 SUBSTR(TO_CHAR(D.ROUTING_REVISION_DATE,'HH24:MI'),1,5) ROUTING_TIME,
 D.SCHEDULED_COMPLETION_DATE Scheduled_Complete,
 SI2.DESCRIPTION Bill_Ref_Description,
 SI3.DESCRIPTION Rout_Ref_Description, SUBSTR(TO_CHAR(D.SCHEDULED_COMPLETION_DATE,'HH24:MI'),1,5) COMPLETION_TIME,
 SG.SCHEDULE_GROUP_NAME,
 D.BUILD_SEQUENCE,
 WL.LINE_CODE
FROM MTL_SYSTEM_ITEMS_VL M,
 MTL_SYSTEM_ITEMS SI2,
 MTL_SYSTEM_ITEMS SI3,
 MFG_LOOKUPS LU,
 MTL_ITEM_LOCATIONS L,
 WIP_LINES WL,
 WIP_SCHEDULE_GROUPS SG,
 WIP_ENTITIES E,
 WIP_DISCRETE_JOBS D
WHERE E.WIP_ENTITY_NAME='17303'
AND E.ORGANIZATION_ID = 207
AND SG.SCHEDULE_GROUP_ID (+) = D.SCHEDULE_GROUP_ID
AND SG.ORGANIZATION_ID(+) = D.ORGANIZATION_ID
AND WL.LINE_ID (+) = D.LINE_ID
AND WL.ORGANIZATION_ID (+) = D.ORGANIZATION_ID
AND L.INVENTORY_LOCATION_ID(+) = NVL(D.COMPLETION_LOCATOR_ID,'-1')
AND L.ORGANIZATION_ID (+) = D.ORGANIZATION_ID
AND D.WIP_ENTITY_ID = E.WIP_ENTITY_ID
AND M.INVENTORY_ITEM_ID(+) = E.PRIMARY_ITEM_ID
AND LU.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND LU.LOOKUP_CODE = D.STATUS_TYPE
--AND D.STATUS_TYPE IN (1,3,4,6)
AND E.ENTITY_TYPE in (1,5)
AND D.ORGANIZATION_ID = 207
AND M.ORGANIZATION_ID(+) = 207
AND    SI2.ORGANIZATION_ID (+) = 207
AND    SI3.ORGANIZATION_ID (+) = 207
AND    SI2.INVENTORY_ITEM_ID (+) = D.BOM_REFERENCE_ID
AND    SI3.INVENTORY_ITEM_ID (+) = D.ROUTING_REFERENCE_ID
ORDER BY 1
/

Query to find WIP Job components:
SELECT WRO.WIP_ENTITY_ID WIP_Entity_ID,
       SI.SEGMENT1 ITEM,
       SI.DESCRIPTION CI_Description,
       WRO.OPERATION_SEQ_NUM CI_Op_Seq,
       SI.PRIMARY_UOM_CODE CI_UOM,
       WRO.REQUIRED_QUANTITY Required_Quantity,
       WRO.QUANTITY_ISSUED Quantity_Issued,
       ( SELECT NVL(SUM(OHQD.PRIMARY_TRANSACTION_QUANTITY),0)
           FROM MTL_ONHAND_QUANTITIES_DETAIL OHQD
           WHERE OHQD.ORGANIZATION_ID = WRO.ORGANIZATION_ID
             AND OHQD.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
         ) Quantity_On_Hand,
       WRO.DATE_REQUIRED Date_Required,
       ( SELECT ML1.MEANING
           FROM MFG_LOOKUPS ML1
           WHERE ML1.LOOKUP_TYPE = 'SYS_YES_NO'
             AND ML1.LOOKUP_CODE = WRO.MRP_NET_FLAG
         ) MRP_Net,
       ( SELECT ML2.MEANING
           FROM MFG_LOOKUPS ML2
           WHERE ML2.LOOKUP_TYPE = 'WIP_SUPPLY_SHORT'
             AND ML2.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE
         ) CI_Supply_Type,
       WRO.SUPPLY_SUBINVENTORY Subinv,
       ( SELECT WRO.SUPPLY_LOCATOR_ID
           FROM MTL_ITEM_LOCATIONS IL
           WHERE IL.INVENTORY_LOCATION_ID = WRO.SUPPLY_LOCATOR_ID
             AND IL.ORGANIZATION_ID = WRO.ORGANIZATION_ID
         ) C_Supply_Loc_Data
  FROM  MTL_SYSTEM_ITEMS_VL SI,
        WIP_REQUIREMENT_OPERATIONS WRO
 WHERE  WRO.WIP_ENTITY_ID = 31649
 AND   WRO.ORGANIZATION_ID = 207
 AND    SI.ORGANIZATION_ID = 207
 AND    SI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
 AND    WRO.WIP_SUPPLY_TYPE <> 6
ORDER BY 1,2
/
Query to find WIP Job operations:
SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID,
       WO.OPERATION_SEQ_NUM Op_Seq,
       BD.DEPARTMENT_CODE Department,
       ML1.MEANING Count_Point_Type,
       ML2.MEANING Backflush,
       TO_CHAR(WO.LAST_UNIT_COMPLETION_DATE) Op_Cmpl_Date,
       TO_CHAR(WO.LAST_UNIT_COMPLETION_DATE, 'HH24:MI') Op_Cmpl_Time,
       WO.SCHEDULED_QUANTITY Op_Scheduled_Qty,
       (WO.QUANTITY_IN_QUEUE +
       WO.QUANTITY_RUNNING +
       WO.QUANTITY_WAITING_TO_MOVE +
       WO.QUANTITY_REJECTED +
       WO.QUANTITY_SCRAPPED) Qty_In_Operation,
       WO.QUANTITY_COMPLETED Op_Qty_Completed,
       WO.DESCRIPTION Description
FROM   MFG_LOOKUPS ML1,
       MFG_LOOKUPS ML2,
       BOM_DEPARTMENTS BD,
       WIP_DISCRETE_JOBS DJ,
       WIP_OPERATIONS WO,
       WIP_ENTITIES WE
WHERE WO.WIP_ENTITY_ID = 31649 
AND    WO.ORGANIZATION_ID = 207
AND    DJ.ORGANIZATION_ID = 207
AND    BD.ORGANIZATION_ID = 207
AND    DJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND    WO.DEPARTMENT_ID = BD.DEPARTMENT_ID
AND    ML1.LOOKUP_TYPE = 'BOM_COUNT_POINT_TYPE'
AND    ML1.LOOKUP_CODE = WO.COUNT_POINT_TYPE
AND    ML2.LOOKUP_TYPE = 'SYS_YES_NO'
AND    ML2.LOOKUP_CODE = WO.BACKFLUSH_FLAG
ORDER BY WO.OPERATION_SEQ_NUM
/

Query to find WIP Job resources:
SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID,
       WOR.OPERATION_SEQ_NUM R_Op_Seq,
       WOR.RESOURCE_SEQ_NUM Res_Seq,
       WOR.SCHEDULE_SEQ_NUM Sched_Seq,
       BR.RESOURCE_CODE Res_Name,
       WOR.UOM_CODE Res_Uom,
       ML1.MEANING Basis_Type,
       WOR.USAGE_RATE_OR_AMOUNT Usage_Rate,
       NVL(WOR.ASSIGNED_UNITS,0) Assigned_Units,
       ML2.MEANING Scheduled,
       CA.ACTIVITY Activity,
       ML3.MEANING Autocharge,
       ML4.MEANING Standard_Rate,
       DECODE(WOR.BASIS_TYPE,1,WOR.USAGE_RATE_OR_AMOUNT *
       DJ.START_QUANTITY,2,WOR.USAGE_RATE_OR_AMOUNT) Standard_Units,
       WOR.APPLIED_RESOURCE_UNITS Units_Applied
FROM   MFG_LOOKUPS ML1,
       MFG_LOOKUPS ML2,
       MFG_LOOKUPS ML3,
       MFG_LOOKUPS ML4,
       BOM_RESOURCES BR,
       CST_ACTIVITIES CA,
       WIP_OPERATION_RESOURCES WOR,
       WIP_DISCRETE_JOBS DJ,
       WIP_ENTITIES WE
WHERE  WOR.WIP_ENTITY_ID = 31649 
AND    WOR.ORGANIZATION_ID = 207
AND    DJ.ORGANIZATION_ID = 207
AND    BR.ORGANIZATION_ID = 207
AND    DJ.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND    WOR.RESOURCE_ID = BR.RESOURCE_ID
AND    WOR.ACTIVITY_ID = CA.ACTIVITY_ID (+)
AND    ML1.LOOKUP_TYPE = 'CST_BASIS'
AND    ML1.LOOKUP_CODE = WOR.BASIS_TYPE
AND    ML2.LOOKUP_TYPE = 'BOM_RESOURCE_SCHEDULE_TYPE'
AND    ML2.LOOKUP_CODE = WOR.SCHEDULED_FLAG
AND    ML3.LOOKUP_TYPE = 'BOM_AUTOCHARGE_TYPE'
AND    ML3.LOOKUP_CODE = WOR.AUTOCHARGE_TYPE
AND    ML4.LOOKUP_TYPE = 'SYS_YES_NO'
AND    ML4.LOOKUP_CODE = WOR.STANDARD_RATE_FLAG
ORDER BY WOR.OPERATION_SEQ_NUM, WOR.RESOURCE_SEQ_NUM
/

Query to find WIP Job reservations:
SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID,
       WRV.DEMAND_SOURCE_LINE_NUMBER CR_Line,
       WRV.DEMAND_CLASS_CODE Reservation_Demand_Class,
       WRV.PRIMARY_QUANTITY  Reserved_Primary_Qty,
       WRV.PRIMARY_UOM_CODE  Reserved_Primary_UOM,
        WRV.RESERVED_LINE_UOM_QUANTITY Reserved_Line_Qty,
        WRV.ORDER_LINE_UOM_CODE Reserved_Line_UOM
FROM   MTL_SALES_ORDERS MS,
       WIP_DISCRETE_JOBS DJ,
       WIP_RESERVATIONS_V WRV,
       WIP_ENTITIES WE
WHERE  WRV.WIP_ENTITY_ID = 31649
AND    WRV.ORGANIZATION_ID = 207
AND    DJ.ORGANIZATION_ID = 207
AND    DJ.WIP_ENTITY_ID = WRV.WIP_ENTITY_ID
AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND    MS.SALES_ORDER_ID = WRV.DEMAND_SOURCE_HEADER_ID
ORDER BY  WRV.DEMAND_SOURCE_LINE_NUMBER

References:

http://snroracle.blogspot.com/2012/09/wip-job-related-scripts.html

Query to get the Defaulting rules defined in Order Managment

SELECT d.database_object_display_name,
       d.attribute_display_name,
       d.precedence,
       d.display_name,
       d.system_flag,
       d.enabled_flag,
       k.sequence_no,
       l.meaning,
       DECODE (
          src_type,
          'API', src_api_pkg || '.' || src_api_fn,
          'CONSTANT', src_constant_value,
          'SYSTEM', src_system_variable_expr,
          'SEQUENCE', src_sequence_name,
          'DATABASE', src_database_object_name || '.' || src_attribute_code,
          'WAD_ATTR', ' ',
          'WAD_OBJATTR', ' ',
          src_constant_value)
          source,
       src_type,
       k.attribute_code
  FROM oe_def_attr_condns_v d, oe_lookups l, oe_def_attr_rules_v k
 WHERE     d.database_object_name = k.database_object_name
       AND d.attr_def_condition_id = k.attr_def_condition_id
       AND d.condition_id = k.condition_id
       AND d.attribute_code = k.attribute_code
       AND k.src_type NOT IN
              ('RELATED_RECORD', 'SAME_RECORD', 'PROFILE_OPTION')
       AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
       AND (k.src_type = l.lookup_code
            OR (k.src_type = k.attribute_code AND l.lookup_code = 'CONSTANT'))
UNION
SELECT d.database_object_display_name,
       d.attribute_display_name,
       d.precedence,
       d.display_name,
       d.system_flag,
       d.enabled_flag,
       k.sequence_no,
       l.meaning,
       a.object_name || '.' || a.name source,
       src_type,
       k.attribute_code
  FROM oe_def_attr_condns_v d,
       oe_lookups l,
       oe_def_attr_rules_v k,
       ak_object_attributes_vl a
 WHERE     d.database_object_name = k.database_object_name
       AND d.attr_def_condition_id = k.attr_def_condition_id
       AND d.condition_id = k.condition_id
       AND d.attribute_code = k.attribute_code
       AND a.database_object_name(+) = k.src_database_object_name
       AND a.attribute_code(+) = k.src_attribute_code
       AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
       AND k.src_type = l.lookup_code
       AND k.src_type = 'RELATED_RECORD'
UNION
SELECT d.database_object_display_name,
       d.attribute_display_name,
       d.precedence,
       d.display_name,
       d.system_flag,
       d.enabled_flag,
       k.sequence_no,
       l.meaning,
       a.name source,
       src_type,
       k.attribute_code
  FROM oe_def_attr_condns_v d,
       oe_lookups l,
       oe_def_attr_rules_v k,
       ak_object_attributes_vl a
 WHERE     d.database_object_name = k.database_object_name
       AND d.attr_def_condition_id = k.attr_def_condition_id
       AND d.condition_id = k.condition_id
       AND d.attribute_code = k.attribute_code
       AND a.database_object_name(+) = k.database_object_name
       AND a.attribute_code(+) = k.src_attribute_code
       AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
       AND k.src_type = l.lookup_code
       AND k.src_type = 'SAME_RECORD'
UNION
SELECT d.database_object_display_name,
       d.attribute_display_name,
       d.precedence,
       d.display_name,
       d.system_flag,
       d.enabled_flag,
       k.sequence_no,
       l.meaning,
       a.user_profile_option_name source,
       src_type,
       k.attribute_code
  FROM oe_def_attr_condns_v d,
       oe_lookups l,
       oe_def_attr_rules_v k,
       fnd_profile_options_vl a
 WHERE     d.database_object_name = k.database_object_name
       AND d.attr_def_condition_id = k.attr_def_condition_id
       AND d.condition_id = k.condition_id
       AND a.profile_option_name(+) = k.src_profile_option
       AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
       AND k.src_type = l.lookup_code
       AND k.src_type = 'PROFILE_OPTION';

References:
http://oracleappsscriptsandtips.blogspot.ca/2012/09/query-to-get-defaulting-rules-defined.html

Accessing Approval Management Engine (AME) - Roles and Grants

Accessing Approval Management Engine (AME) is not possible as we access other responsibility / functions in Oracle Applications like Order Management / Application Developer etc.

AME is controlled by RBAC (Role Based Access Control). So in this case even you assign responsibility to the user but if user don't have the appropriate roles then you will not be able to access Approval Management functions.

There are two seeded responsibility for AME :-

• Approvals Management Administrator
• Approvals Management Business Analyst

Let's assign the responsibility directly to user and see what happens.

 Login with respective user.

Chose Approval Management Responsibility

You will receive below error.

With the screen shots above we can see that even though the responsibility is assigned to user while trying to access it, It says There is no valid navigation for this responsibility.

Accessing AME is two step process :-

• Assign the required roles to the user : This will give user access to use Approval Management functions.
• Grant access to respective AME transaction type : This will provide access to AME transaction type. If we don't perform this step then we will be able to access AME responsibility but will not be able to access any transaction types.

We have following seeded roles provided by Oracle that can be used to access AME.

• Approvals Management Administrator
• Approvals Management Business Analyst
• Approvals Management Process Owner
• Approvals Management System Administrator
• Approvals Management System Viewer

Step -1 : Assign the Roles

• Login with administrator / sysadmin user.
  
• Navigate to User Management -> User.
  
• Enter User Name and click on Go to find the user.
• Click on Update button
  
• Click on Assign Roles button.
  
• Search the role 'Approval Management Administrator' from LOV and select it.
  
  
• Based on the role chosen you will see number of functions added under users roles.
  

Step -2 : Grant Access to Transaction Types

• Login with administrator / sysadmin user.
• Navigate to Functional Administrator -> Grants -> Create Grants.
  
  
• Enter a name and description for the Grant
• Enter effective from date and optionally effective to date.
• Select Grantee Type from Security Context region
• All Users : The grant will be applied to all the users and all users will be given same right / privileges that is part of this grant. 
• Group Of Users : The grant will be applied to users those are part of the group.
• Specific User : Grant will be applicable of specific set of users that you select.
• I have opted for specific user.
• In addition to this you can also choose Operating Unit and Responsibility to make is more secure. Say you want to provide a user to AME access to US operating Unit only. In that case you can choose operating unit. If there is not such restriction required then leave it blank.
• From Data Security region select an object. In our case it will be 'AME Transaction Types'
  
• Click Next
• In the Data Context Type select 'All Rows'.
  
• Select 'AME Calling Applications' as permission set and click Next to review the setup.
  
• Finish to complete.
  

Now you are good to use AME and setup approvals. Happy Setups...

References:
http://www.oracleappsnfusion.com/2014/01/accessing-ame.html
http://mtalmasri.blogspot.com/2012/04/oracle-ebs-repairing-xxx-is-not-valid.html

How to search within Long data type column in Oracle (this is one way of doing it)

select at.trigger_name, (at.trigger_body) trig_body, at.*
from all_triggers at
where (trigger_name like 'XX%'
OR table_name like 'XX%')

create xx_triggers
as
select at.trigger_name, to_lob(at.trigger_body) trig_body
from all_triggers at
where (trigger_name like 'XX%'
OR table_name like 'XX%');

select xt.trigger_name,  upper(xt.trig_body)--, at.*
from xx_triggers xt
where upper(xt.trig_body) like '%SEGMENT1%';

Patch history tables in Oracle Apps

AD_APPL_TOPS - Stores information about all APPL_TOPS utilizing this instance.
AD_APPLIED_PATCHES - Stores information about all distinct updates applied.
AD_BUGS - Stores information about all distinct bug fixes applied.
AD_DRIVERS - Stores information about the drivers that comprise an update.
AD_FILE_VERSIONS - Stores version information for files.
AD_FILES - Stores information about files in the system.
AD_PATCH_COMMON_ACTIONS - Stores information about actions in update driver files.
AD_PATCH_DRIVER_LANGS - Stores information about the languages included in an update driver file.
AD_PATCH_DRIVER_MINIPKS - Stores information about minipacks.
AD_PATCH_RUN_BUG_ACTIONS - Stores detailed information about actions applied by an update.
AD_PATCH_RUN_BUGS - Stores information about all bug fixes included in an update , applied or not.
AD_PATCH_RUNS - Stores information about all incvocations of AutoPatch for all of the various updates applied.
AD_RELEASES - Stores release information about the Oracle Applications system.

References:
http://appsoracle.blogspot.ca/2012/03/patch-history-tables-in-oracle-apps.html

Oracle Application URL from the Apps database

SELECT profile_option_value
  FROM apps.fnd_profile_option_values
 WHERE profile_option_id =
                         (SELECT profile_option_id
                            FROM apps.fnd_profile_options
                           WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')
   AND level_value = 0