Payment Accounting SQL: Oracle Fusion R13

Payment Accounting SQL

SELECT
    xah.ae_header_id,
    xte.source_id_int_1,
    xal.ae_line_num,
    xal.displayed_line_number,
    xal.description,
    xal.party_id,
    xal.party_site_id,
    xal.party_type_code,
    xal.accounting_class_code,
    (select meaning from FND_LOOKUP_VALUES_TL
     where lookup_type='XLA_ACCOUNTING_CLASS'
     and lookup_code = xal.accounting_class_code
     and language='US'
     and source_lang='US') ACCOUNTING_CLASS_NAME,
    xal.code_combination_id,
    xal.accounted_dr,
    xal.accounted_cr,
    --gcc.code_combination_id AS code_combination_id,
    gcc.segment1
    || '.'
    || gcc.segment2
    || '.'
    || gcc.segment3
    || '.'
    || gcc.segment4
    || '.'
    || gcc.segment5
    || '.'
    || gcc.segment6
    || '.'
    || gcc.segment7 AS account_segments,
    gled.chart_of_accounts_id,
gl_flexfields_pkg.get_concat_description(gled.chart_of_accounts_id,gcc.code_combination_id) account_description,
gled.ledger_id
FROM
xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc,
gl_ledgers gled
WHERE 1=1
    AND xah.entity_id = xte.entity_id
    AND xal.ae_header_id = xah.ae_header_id
    AND xal.application_id = 200
    AND xah.application_id = 200
    AND xte.application_id = 200
    AND xte.ledger_id = gled.ledger_id
    AND xte.ledger_id = xah.ledger_id
    AND xah.ledger_id = xal.ledger_id
    AND gcc.code_combination_id = xal.code_combination_id
    and xte.entity_code ='AP_PAYMENTS'
order by  xal.accounted_dr,     xal.displayed_line_number

How to Develop a Salary Change Report in HCM Fusion R13

How to Develop a Salary Change Report in HCM Fusion R13
----------------------------------------------------------------------

Lets start with Master Tables, which has Data Elements related to identifying salary.

Table: pay_balance_types
       -> Pass:  BASE_BALANCE_NAME = 'XX_GROSS_SALARY_NPV'
       -> Get :  BALANCE_TYPE_ID -> 300000006261657
     
Table: pay_balance_dimensions:
       -> Pass : base_dimension_name = 'Core Assignment Run'
       -> Get balance_dimension_id -> 300000000809093

Table: pay_defined_balances
       -> Pass [JOIN balance_dimension_id AND BALANCE_TYPE_ID]
       -> Get : defined_balance_id

Table: Pay_run_balances: this table contains person specfic balances for payroll runs
       -> Pass: defined_balance_id : [filter data based on base balance and dimension]
       -> Pass: other parameters like payroll_relationship_id, and pay_rel_action_id
     
--------------
To filter data based on person: Lets start from person details

Table: per_all_people_f : pass person_number -> get person_id

Table: per_all_assignments_f : pass person_id and -> get assignment_id [filter: primary_flag =Y and record is active]

Table: Pay_pay_relationships_dn [pprd]: 1 record: specific to person and non-trx table
       -> Pass: Person_id
       -> Get:  payroll_relationship_id

Table: pay_payroll_rel_actions [ppra] : This table contains details of the individuals processed in a payroll process: more than 1 record for 1 person as its based on payroll ran 
       -> Pass: Payroll_relationship_id
       -> get payroll_action_id -> specif to a person_id
     
Table: pay_payroll_actions
       -> Pass: payroll_action_id
       -> Pass: action_types [Q,R]
       -> you may get multiple records of person should join with pay_time_periods with EARN_TIME_PERIOD_ID
       -> get PAYROLL_ID, EARN_TIME_PERIOD_ID, PAYROLL_ACTION_ID

Table: pay_time_periods     
       -> Pass time_period_id , payroll_action_id

---------------
again come to Table: Pay_run_balances:

-> you have : defined_balance_id
            : payroll_relationship_id
            : payroll_action_id
            : pay_rel_action_id


-> after passing above: you will get balance value for the period.

Oracle Reports: Bind Vs Lexical, Summary, PlaceHolder, Formula Columns

Bind Vs Lexicals, Summary, PlaceHolder, Formula Columns

Bind-Lexical Parameters in Report:
-----------------------------
bind parameters: input dept number at runtime: bind parameters. prefix with colon :
lexical parameters: input conditions at run time., prefixed with (ampersand) &

summary columns and formula columns in Report:
----------------------------------------------
Each of below can be used two ways: inside a group, or outside a group
                                Inside the group: repeated for each row
                                Outside Group: Once for report

Summary Columns: [CS_] are used for calculating aggregates: sum, min, max [these are predefined]
Formula Columns: [CF_] If you want to sum salary and commision , or any calculated value based on formula:
PlaceHolder Column: [CP_]: Placeholder Columns acts like a variable. 



Ex: find the name of the employee earning the maximum salary. 
---------------------------------------------------------------
Add a summary col outside group: Maximum salary can be stored in CS_MaxSal (Summary col),
Add a placeholder column outside group to hold employee name. CP_1
Add a formula column inside Group to find while query runs for each record: CF_1

CODE in Formula Column:

IF :SALARY = :CS_MaxSal THEN
    :CP_1 := :EMPLOYEE_NAME
END IF;
RETURN (:CP_1)   



Ex2: find the Total Salary , where salary > 10000. Only salary > 10000 we should add
-----------------------------------------------------------------------------------
Add a placeholder column outside group to hold Total Salary. CP_1
Add a formula column inside Group to find while query runs for each record: CF_1

CODE in Formula Column:

IF :SALARY > 10000 THEN
    :CP_1 := NVL(:CP_1,0) + :SALARY
END IF;
RETURN (:CP_1)   

Oracle Apps: Basic Tables

Business Groups [Only]: per_business_groups

Operating Units [Only]: hr_operating_units

Inventory Orgs [Only]: org_organization_definitions

---------------------------------------------------
BG/OU/IO: hr_all_organization_units
---------------------------------------------------

Database Not Open Error

Resolving :

C:\Users\abhishek>sqlplus sys/*****@localhost:1521/XEPDB2 as SYSDBA

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 19 16:39:29 2019
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> alter database open;

Database altered.

Oracle Fusion Financials Tables and Views Link

Important Links:

Tables and Views
https://docs.oracle.com/cloud/farel12/financialscs_gs/OEDMF/toc.htm

Tables:

https://docs.oracle.com/cloud/farel12/financialscs_gs/OEDMF/TablesOverview.htm#TablesOverview

Views:

https://docs.oracle.com/cloud/farel12/financialscs_gs/OEDMF/ViewsOverview.htm#ViewsOverview

plsql authid current_user explained

====================

There are two types of rights on procedure

1. definer [by default]
2. invoker [can be used by adding authid current_user]

---------------------
Lets take an example, there are two users , user1, user2, both have defined temp table.

user1/user 1                        user2/user2/u

table: temp                         table: temp
empid empname                empid empname
100   smith                         100   johns
----------------------------------------------------------
create a procedure in user1 [schema]

create or replace procedure get_user
is
v_user varchar2(100);
begin
select empname into v_user from temp;
dbms_output.put_line(v_user);
end;

grant this procedure to user2 as well.
> grant execute on get_user to user2;
---------------------------------------------------------------

when you run it from user1> you get smith.
when you run it from user2> you get smith.

reason: since proc is run by definer rights by default.
--------------------------------------------------------------

to get johns printed:

you need to define as below in user1: so that while executed, it will be executed with invoker rights

create or replace procedure get_user authid current_user
is
v_user varchar2(100);
begin
select empname into v_user from temp;
dbms_output.put_line(v_user);
end;

==============================================================

We always create package and proc in custom schema, and then use those pkgs on schema's like HR and GL etc, then if don't use authid current_user, your proc will give undesired results because of above reason.

Refer: https://www.youtube.com/watch?v=L8YYimP7cAQ&list=PLVlQHNRLflP-B-e7daJkgKPS_vmya5mY6&index=13&t=0s