Tuesday, September 29, 2020

Query to fetch the Privilege required to access an ESS job

SELECT   P.CODE PRIVILEGE, 
  T.NAME, 
  PERM.CODE CODE, 
  PERM.RESOURCE_TYPE_NAME, 
  PERM.ACTION 
FROM   FUSION.ASE_PRIVILEGE_B P, 
  FUSION.ASE_PERMISSION_B PERM, 
  ASE_PRIVILEGE_TL T 
WHERE   P.PRIVILEGE_ID = PERM.PRIVILEGE_ID 
  AND P.PRIVILEGE_ID = T.PRIVILEGE_ID (+) 
  AND T.LANGUAGE = 'US' 
  AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE 
  AND NVL(P.EFFECTIVE_END_DATE, SYSDATE) 
  AND SYSDATE BETWEEN PERM.EFFECTIVE_START_DATE 
  AND NVL(PERM.EFFECTIVE_END_DATE, SYSDATE
  AND PERM.CODE LIKE '%AsePrivilegeDiscovererReportJob' -- Enter Job Name

Tuesday, September 22, 2020

Segment Value Security Rules

Introduction: Segment value security rules are setup on value sets to control access to parent or detail segment values for chart of accounts segments. Segment value security rules restrict data entry, online inquiry, and reporting. These are basically used for Non Balancing segment values security. Since we can control the access for Balancing segment values through Manage Data Access Sets.

Business case:

Data Access Sets

Ledger Name

Balancing Segment

 

User A

User B

US Primary Ledger

101

Comp101

Y

Y

102

Comp102

Y

N

Security Rules

Ledger Name

Cost Center

 

User A

User B

US Primary Ledger

110

CEO

Y

N

120

Division US

Y

N

Process:

·         Define roles for segment value security rules.

·         Enable segment value security for the value set.

·         Define the conditions.

·         Define the policies.

·         Deploy the accounting flexfield.

·         Publish the account hierarchies.

·         Assign segment value security roles to users.

Below picture illustrates steps for defining and implementing security rules for segment values.

Note: When you enable security on a value set, access to all values for that value set is denied.

Working Example: This example demonstrates how to enable security on a chart of accounts to control access to specific segment (Cost Center) values.

While creating journals by default, we are able to see all the values in cost center segment LOV. For this scenario, we need to control the access to 110 and 120.


Step 1: Create a custom job role solely for the purpose of segment value security. This role is then assigned to the users who need access. For this scenario, we created a role: VIS_General Accountant. 

Step 2: Navigate to ‘Manage Segment Value Security Rules’ task

Use the Manage Segment Value Security Rules task to enable security on the cost center value set associated with the chart of accounts.




Step 3: Enable the Security and Enter the Data Security Resource Name.

Step 4: Click on Save and Click on Edit Data Security button.


Step 5: Create a condition for the value set. For example, the condition (CostCenter110120) for the cost center is that the value must be equal to 110 or must be equal to 120.

Select Match as Any for OR operation; All for AND operation. Click Save.


Step 6: Create a policy to associate the conditions to the roles. For example, create a policy (CorpCostCenter110120) to assign the condition CostCenter110120 to the role VIS_General Accountant Role.

Enter Role code instead of Role Name. And select fscm as Application.


Step 7: Select Multiple Values as row set and assign condition to the policy.



Click Save and close.


Click Save and Submit.



Step 8: Navigate to Manage Chart of Accounts Structures.


 Select the module and click Deploy Flexfield.



Optionally, Publish the account hierarchies.

Use the Security Console to assign the appropriate role to the appropriate user. For example, assign the role VIS_General Accountant role to the users who should have access to the cost centers 110 and 120. Login as that user and verify in the Create journal screen.  Only cost centers 110 and 120 are visible as below.





Since, enabling data security on the value set will deny the access to all values for that value set. Which means other users who do not have VIS_General Accountant Role will not be able to access any values of Cost Center Segment.

We can define another similar Policy to provide access to All values of the Value set and assign to a custom role solely created to provide access to all the values of the value set. Use the Security Console to assign this role to the appropriate users who should have access to all the cost centers.

Note: It is not necessary to create a condition for this and we need to select All Values as row set.

Reference links: 

Friday, September 11, 2020

Review Cost Accounting Distributions Query

 SELECT CT.SOURCE_TABLE AS TXN_NAME, 
  C.COST_ORG_CODE, 
  C.COST_ORG_NAME, 
  CB.COST_BOOK_CODE, 
  V.VAL_UNIT_CODE, 
  IO.ORGANIZATION_CODE, 
  ITM.ITEM_NUMBER, 
  CT.QUANTITY, 
  CT.UOM_CODE, 
  CT.TXN_SOURCE_DOC_TYPE, 
  CT.TXN_SOURCE_DOC_NUMBER, 
  CT.TXN_SOURCE_REF_DOC_TYPE, 
  CT.TXN_SOURCE_REF_DOC_NUMBER, 
  CTYP.BASE_TXN_TYPE_NAME, 
  TO_CHAR(CT.BASE_TXN_SOURCE_TYPE_ID) AS TX_SRC_TYPE_ID, 
  TO_CHAR(CT.BASE_TXN_ACTION_ID) AS TX_ACT_ID, 
  CT.TRANSACTION_DATE, 
  CT.COST_DATE, 
  CT.TRANSACTION_ID, 
  CT.COST_STATUS, 
  CT.ACCOUNTING_STATUS, 
  CT.INTRANSIT_FLAG, 
  CT.COST_METHOD_CODE, 
  CD.DISTRIBUTION_ID, 
  CDL.LINE_NUMBER, 
  CD.EVENT_ID, 
  CD.GL_DATE, 
  CD.DEP_TRXN_ID, 
  CD.REC_TRXN_ID, 
  CDL.COST_ID, 
  CDL.SOURCE_TABLE, 
  CEL.COST_ELEMENT_TYPE, 
  CDL.ACCOUNTING_LINE_TYPE, 
  CASE WHEN CDL.LEDGER_AMOUNT >= 0 
        THEN CDL.LEDGER_AMOUNT 
    ELSE NULL 
   END AS LEDGER_DR, 
  CASE WHEN CDL.LEDGER_AMOUNT < 0 
        THEN -1.0 * CDL.LEDGER_AMOUNT 
     ELSE NULL
    END AS LEDGER_CR, 
  CDL.ENTERED_CURRENCY_AMOUNT, 
  CDL.CODE_COMBINATION_ID, 
  CD.ACCOUNTED_FLAG, 
  CD.ADDITIONAL_PROCESSING_CODE, 
  CEL.COST_ELEMENT_CODE, 
  CEL.SET_ID AS COST_ELEM_SETID, 
  CT.COST_TRANSACTION_TYPE, 
  CT.TRANSACTION_FLOW_TYPE, 
  CT.CONSIGNED_FLAG, 
  CT.LE_TIMEZONE_CODE, 
  CT.CST_INV_TRANSACTION_ID, 
  CT.CREATION_DATE, 
  CT.LAST_UPDATE_DATE 
FROM   FUSION.CST_ALL_COST_TRANSACTIONS_V CT, 
  FUSION.CST_COST_ORGS_V C, 
  FUSION.CST_COST_BOOKS_B CB, 
  FUSION.CST_COST_ORG_BOOKS COB, 
  FUSION.CST_VAL_UNITS_B V, 
  FUSION.INV_ORG_PARAMETERS IO, 
  FUSION.EGP_SYSTEM_ITEMS ITM, 
  FUSION.CST_ALL_TXN_TYPES_V CTYP, 
  FUSION.CST_COST_DISTRIBUTIONS CD, 
  FUSION.CST_COST_DISTRIBUTION_LINES CDL, 
  FUSION.CST_COST_ELEMENTS_B CEL 
WHERE   CD.DISTRIBUTION_ID = CDL.DISTRIBUTION_ID(+) 
  AND CDL.COST_ELEMENT_ID = CEL.COST_ELEMENT_ID(+) 
  AND CT.TRANSACTION_ID = CD.TRANSACTION_ID(+) 
  AND COB.COST_ORG_ID = C.COST_ORG_ID 
  AND COB.COST_BOOK_ID = CB.COST_BOOK_ID 
  AND CT.VAL_UNIT_ID = V.VAL_UNIT_ID(+) 
  AND CT.INVENTORY_ORG_ID = IO.ORGANIZATION_ID 
  AND NVL(
    IO.MASTER_ORGANIZATION_ID, IO.ORGANIZATION_ID
  ) = ITM.ORGANIZATION_ID 
  AND CT.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID 
  AND CT.COST_ORG_ID = COB.COST_ORG_ID 
  AND CT.COST_BOOK_ID = COB.COST_BOOK_ID 
  AND -- Enter the Reference Document such as Work Order# /PO#/ SO#/ TO#
  CT.TXN_SOURCE_REF_DOC_NUMBER IN (:REF_DOC_NUM) 
  -- itm.item_number = 'XYZ'
  AND CT.BASE_TXN_TYPE_ID = CTYP.BASE_TXN_TYPE_ID(+) 
  AND CT.BASE_TXN_SOURCE_TYPE_ID = CTYP.BASE_TXN_SOURCE_TYPE_ID(+) 
  AND CT.BASE_TXN_ACTION_ID = CTYP.BASE_TXN_ACTION_ID(+) 
ORDER BY   2,   4

Monday, September 7, 2020

Purchase Order Change History Query

--Submitted Change Orders--

select version.co_num,

            version.change_order_desc comments,

            version.creation_date,

            version.originator_role,

            (

                        select full_name

                        from per_person_names_f

                        where person_id = version.originator_id

                                    and name_type = 'global'

                        ) initiator,

            version.change_order_status status,

            nvl(plaa.amount, (plaa.quantity * plaa.unit_price)) initial_amount,

            (

                        select sum(nvl(plaa1.amount, (plaa1.quantity * plaa1.list_price)))

                        from po_lines_archive_all plaa1,

                                    po_versions pov1

                        where 1 = 1

                                    and pov1.co_num = version.co_num

                                    and pov1.co_sequence = plaa1.from_co_seq

                                    and pov1.po_header_id = plaa1.po_header_id

                                    and plaa1.po_line_id = plaa.po_line_id

                                    and plaa1.po_header_id = plaa.po_header_id

                        ) - nvl(plaa.amount, (plaa.quantity * plaa.unit_price)) amount_changed,

            (

                        select sum(nvl(plaa1.amount, (plaa1.quantity * plaa1.list_price)))

                        from po_lines_archive_all plaa1,

                                    po_versions pov1

                        where 1 = 1

                                    and pov1.co_num = version.co_num

                                    and pov1.co_sequence = plaa1.from_co_seq

                                    and pov1.po_header_id = plaa1.po_header_id

                                    and plaa1.po_line_id = plaa.po_line_id

                                    and plaa1.po_header_id = plaa.po_header_id

                        ) final_amount

from po_lines_archive_all plaa,

            po_lines_all pla,

            po_versions version,

            po_headers_all pha

where pla.po_header_id(+) = pha.po_header_id

            and version.po_header_id(+) = pha.po_header_id

            and version.co_sequence between plaa.from_co_seq

                        and plaa.to_co_seq

            and version.co_sequence = plaa.to_co_seq(+)

            and version.co_canceled_flag != 'y'

            and plaa.po_line_id(+) = pla.po_line_id

            and change_order_status != 'incomplete'

            and version.co_num is not null

            and pha.segment1 = :p_po_num

 

--Pending Change Orders--

select version.co_num,

            version.change_order_desc comments,

            version.creation_date,

            version.originator_role,

            (

                        select full_name

                        from per_person_names_f

                        where person_id = version.originator_id

                                    and name_type = 'global'

                        ) initiator,

            version.change_order_status status,

            nvl(pla.amount, (pla.quantity * pla.unit_price)) initial_amount,

            (

                        select sum(nvl(plaa1.amount, (plaa1.quantity * plaa1.list_price)))

                        from po_lines_draft_all plaa1

                        where 1 = 1

                                    and plaa1.po_line_id = pla.po_line_id

                                    and plaa1.po_header_id = pla.po_header_id

                        ) - nvl(pla.amount, (pla.quantity * pla.unit_price)) amount_changed,

            (

                        select sum(nvl(plaa1.amount, (plaa1.quantity * plaa1.list_price)))

                        from po_lines_draft_all plaa1

                        where 1 = 1

                                    and plaa1.po_line_id = pla.po_line_id

                                    and plaa1.po_header_id = pla.po_header_id

                        ) final_amount

from po_lines_all pla,

            po_versions version,

            po_headers_draft_all pha

where pla.po_header_id(+) = pha.po_header_id

            and version.po_header_id(+) = pha.po_header_id

            and version.change_order_status = 'incomplete'

            and version.co_canceled_flag != 'y'

            and version.co_num is not null

            and pha.segment1 = :p_po_num