Using Boolean in Functions

Using Boolean in Functions

CREATE or replace FUNCTION testfn (
    date1 IN DATE
) RETURN BOOLEAN
    AS
BEGIN
    IF
        date1 > SYSDATE
    THEN
        RETURN true;
    ELSE
        RETURN false;
    END IF;
END;

----
SET SERVEROUTPUT ON;
----
DECLARE
    v_2   VARCHAR2(10);
BEGIN
       
    v_2 := case when testfn('1-Jan-2019') then 'true' else 'false' end;
    DBMS_OUTPUT.PUT_LINE(sys.diutil.bool_to_int(testfn('1-Dec-2019')));

    dbms_output.put_line(v_2);
    IF
        testfn('1-Jan-2019')
    THEN
        dbms_output.put_line('TRUE');
    ELSE
        dbms_output.put_line('FALSE');
    END IF;

END;



--convert boolean to int
SYS.DIUTIL.BOOL_TO_INT() function:

DECLARE
status BOOLEAN:= false;
BEGIN
  DBMS_OUTPUT.PUT_LINE(sys.diutil.bool_to_int(status));
END;

This will return 1 for true and 0 for false (and null for null).

Designing XSL Sub Templates

Why?

1. Allow Reusable advance functionality for RTF templates.

1. Transform Data structure for section of report
2. create a stylesheet to manage a complex layout

What?

Its a xsl file, which consists of

• one or more <xsl:template> definitions, each containing
• block of formatting
• block of processing commands

How to Use?

Once its developed as subtemplate, its called from main rtf file for processing and formatting requirements.

High Level Development Process

1. xsl template contains serveral <xsl: template> definitions, these definitions contains rules to apply, when a specified node is matched.

2. main template import the xsl template, and also command to apply

3. upload the main template to report definition, and subtemplate to catalog.

SQL Optimization: Optimizer Process

Query Optimizer SQL Processing
--------------------------------

1. Parsing: 
    Optimizer:
        -> Syntax Check
        -> Objects exists in sql [semantic check]
        -> Search shared pool, if statement is ever executed [cache]
            -> if this is the first time optimizer received the sql, it will create a hash of the sql statememt. and then saved it. [sha256]
           
2. Optimzation Stage:
        -> generate possible execution plans
        -> save the selected execution plan along side with parsed sql hash in shared pool.

3. Row Source Generation:
        -> convert execution plan in iterative binary format.
        -> similar to compiling execution plan.
        -> expensive
       
4. Execution: 
        -> Excuting the Row Source Tree produced by row source generator.
       
------------------------------------------------------------------------

Hard Parse: 1->2->3->4 [first time always hardparse]
when the optimizer perform all above steps to execute SQL statement, it is called Hard Parse.

Soft Parse: 1, 4 Only: [no 2,3]
------------------------------------------------------------------------

Lowest Cost Plan is chosen: CBO

------------------------------------------------------------------------

Execution Plan overall cost =
        -> selectivity [stat based]
        -> Cardinality [stat based]
        -> Cost: CPU, I/O and Network
       
*selectivity and cardinality changes based on data changes in objects

-> after any signifacant data changes: we should update these stats [gather stats program in Oracle apps]

-------------------------------

1. selectivity[relative no]: (retrieved rows/total rows)
            -> always <= 1
    [so how many rows you are selecting the query]
 
2.  cardinality [Rows]: no of rows returned by each operation in execution plan [ex. select empname from emp, dept where e.deptid= d.deptid and deptid = 10]           
    ex. if 10 employees are returned by sql, then cardinality is 10


   
Why selectivity and cardinality are important
----------------------------------------------
1. 10/10000, selectivity = 0.0001, get the rowid of the rows and search in index.

2. 5000/10000: low selectivity ex: search in index for half rows, so its better to skip index, access table dierctly is better.

3. cardinality: similar to selectivity, cardinality concept, Optimizer choose, if Index to be used or not.

Since cardinality is stat based, and optimizer look at these stats for
cardinality and then choose the execution plan, it most of times issue with stats which causes sub-optimal plan.
------------------------------------------------

How to build a BI Publisher data model query with receivables transactions information and notes text.

How to build a BI Publisher data model query with receivables transactions information and notes text.
In other words, how to link the tables RA_CUSTOMER_TRX_ALL with ZMM_NOTES.
--------------------------

SOLUTION: Following sample query can be used to join RA_CUSTOMER_TRX_ALL with ZMM_NOTES.

SELECT *
   FROM ra_customer_trx_all trx,
             zmm_notes note
WHERE note.source_object_code = 'AR_TRANSACTION'
    AND trx.customer_trx_id          = note.source_object_uid

RTF Template Various Number Formats

Double click on element, and

BI Publisher Properties > Formatting [Number] > Format

1. You want to show Negative Number -200.34 as (200.34) then enter below into format.

#,##0.0000;(#,##0.0000)


2. If Number is just decimal place to 0000, then place #,##0.0000

3. In case there is number which length is long like 20 char long, then it will display as scientific , to avoid it use Force LTR check box, which will read element from Left to Right (like Arabic style), and it will be read as text instead of number, and you can also check the code generated after applying this check box.

Also any operations being done on xml element , first it should be converted to number type.

<?number(invoiceAmt) - number(amtPaid)?>

page breaks in rtf template

there are 3 ways to create page breaks


<?split-by-page-break:?>

#below code should be written inside the BIP Field element
<xsl:attribute name="break-before">page</xsl:attribute>

directly add page breaks in pages.

------------------------------------------------------

Also in case you want to display different header and footer on different pages then you may use section break, and remove link to previous in the rtf template.

setting variables using variable@incontext

<?for-each-group:G_EMPBANK;./DEP?>
<?variable@incontext:DEPT_ID;M_DEPT_ID?>

<?sum(current-group()[$DEPT_ID=DEPARTMENT_ID]/EARNINGS)?>

<?end for-each-group?>

HCM Payroll Audit Report SQLs

1. Main data set

SELECT
    person.person_id,
    person.time_period_id,
    payroll.earn_time_period_id,
    payroll.effective_date,
    payroll.payroll_action_id,
    payroll.flow_name,
    person.payroll_relationship_id,
    person.payroll_id,
    person.ptp_end_date,
    person.employeeid,
    person.employeename,
    person.hiredate,
    --PREVIOUS PERIOD END DATE
    (select last_day(add_months(START_DATE,-1)) PRE_PERIOD_ENDDATE from PAY_TIME_PERIODS WHERE TIME_PERIOD_ID = :P_PERIOD_NAME ) M_PP_END_DATE,
    (SELECT END_DATE FROM PAY_TIME_PERIODS WHERE TIME_PERIOD_ID = :P_PERIOD_NAME) M_CP_END_DATE
FROM
    (
        SELECT DISTINCT
            papf.person_id,
            paam.assignment_id,
            ppr.payroll_relationship_id,
            pay.payroll_id,         
            ptp.end_date ptp_end_date,
            papf.person_number employeeid,
            ppn.display_name employeename,
            ppos.date_start hiredate,
            ptp.time_period_id
        FROM
            per_all_people_f papf,
            per_all_assignments_m paam,
            per_person_names_f ppn,
            per_periods_of_service ppos,
            pay_all_payrolls_f pay,
            pay_time_periods ptp,
            pay_pay_relationships_dn ppr
        WHERE
            1 = 1
            AND papf.person_id = paam.person_id
            AND paam.primary_flag = 'Y'
            AND paam.effective_latest_change = 'Y'
            AND ppn.person_id = papf.person_id
            AND ppn.name_type = 'GLOBAL'
            AND ppos.person_id = papf.person_id
            AND ppos.period_of_service_id = paam.period_of_service_id
            AND ptp.payroll_id = pay.payroll_id
            AND ptp.period_category = 'E'
            AND ppr.person_id = papf.person_id
            AND trunc(ptp.end_date) BETWEEN papf.effective_start_date AND papf.effective_end_date
            AND trunc(ptp.end_date) BETWEEN paam.effective_start_date AND paam.effective_end_date
            AND trunc(ptp.end_date) BETWEEN ppn.effective_start_date AND ppn.effective_end_date
            AND trunc(ptp.end_date) BETWEEN pay.effective_start_date AND pay.effective_end_date
            AND trunc(ptp.end_date) BETWEEN ppr.start_date AND ppr.end_date
            AND paam.legal_entity_id =:p_le
            AND ptp.legislative_data_group_id =:p_ldg
            AND pay.legislative_data_group_id =:p_ldg
            AND (
                pay.payroll_id IN (
                    :p_payrollname
                )
                OR least(:p_payrollname) IS NULL
            )
            AND ptp.time_period_id =:p_period_name
            AND paam.assignment_type = 'E' --as per discussion 17042019
            AND (
                papf.person_id IN (
                    :p_pn
                )
                OR least(:p_pn) IS NULL
            )
        ORDER BY
            lpad(employeeid,10,'0')    ) person,
    (
        SELECT distinct /* added for issue after commenting source_action_id multiple rows*/
            ppra.payroll_action_id,
            ppa.payroll_id,
            ppa.EARN_TIME_PERIOD_ID,
            (
                SELECT DISTINCT
                    pfi.instance_name
                FROM
                    pay_payroll_actions ppa1,
                    pay_requests prq,
                    pay_flow_instances pfi,
                    pay_flows_tl pft
                WHERE
                    prq.pay_request_id (+) = ppa1.pay_request_id
                    AND prq.flow_instance_id = pfi.flow_instance_id (+)
                    AND pfi.base_flow_id = pft.flow_id
                    AND pft.source_lang = 'US'
                    AND ppa1.earn_time_period_id =:p_period_name
                    AND ppa1.payroll_id =:p_payrollname
                    AND pfi.legislative_data_group_id =:p_ldg
                    AND ppa1.legislative_data_group_id =:p_ldg
                    AND ppa1.payroll_action_id = ppa.payroll_action_id
            ) flow_name,
           ppra.payroll_relationship_id,
            --ppa.effective_date,
           --last_day(nvl(ppa.effective_date,ptp.end_date)) effective_date,         
           ptp.end_date effective_date,         
           payrel.assignment_id
        FROM
            pay_rel_groups_dn payrel,
            pay_payroll_actions ppa,
            pay_payroll_rel_actions ppra,
            pay_assigned_payrolls_dn pap,
            pay_all_payrolls_f pay,
            pay_time_periods ptp
        WHERE
            1 = 1
            AND payrel.group_type = 'A'
            AND payrel.parent_rel_group_id = pap.payroll_term_id
            AND pay.payroll_id = pap.payroll_id
            AND ptp.payroll_id = pay.payroll_id
            AND ptp.period_category = 'E'
            AND trunc(ptp.end_date) BETWEEN payrel.start_date AND payrel.end_date
            AND trunc(ptp.end_date) BETWEEN pay.effective_start_date AND pay.effective_end_date
            AND ptp.legislative_data_group_id =:p_ldg
            AND pay.legislative_data_group_id =:p_ldg
            AND (
                pay.payroll_id IN (
                    :p_payrollname
                )
                OR least(:p_payrollname) IS NULL
            )
            AND ptp.time_period_id =:p_period_name         
            AND ppra.payroll_action_id = ppa.payroll_action_id
            --AND ppra.source_action_id IS NOT NULL --issue in suspended
            AND ppra.retro_component_id IS NULL
            AND ppra.action_status = 'C'
            AND ppa.legislative_data_group_id =:p_ldg
            AND (
                ppa.payroll_action_id IN (
                    :p_flowname
                )
                OR least(:p_flowname) IS NULL
            )
            AND (
                ppa.action_type IN (
                    :p_action_type
                )
                OR least(:p_action_type) IS NULL
            )
    ) payroll
WHERE
    1 = 1
    AND payroll.payroll_relationship_id (+) = person.payroll_relationship_id
    AND payroll.payroll_id (+) = person.payroll_id
    and payroll.effective_date (+) = person.ptp_end_date
    and payroll.assignment_id(+)  = person.assignment_id
    and (
                payroll.payroll_action_id IN (
                    :p_flowname
                )
                OR least(:p_flowname) IS NULL
            )


2. Get Data for a Balance Name for Current/Previous Period


select
current_tp.person_id,
current_tp.payroll_action_id,
current_tp.Totalpay c_Totalpay,
previous_tp.Totalpay p_Totalpay,
case when (nvl(previous_tp.Totalpay,0) = nvl(current_tp.Totalpay,0)) then 'N' else 'Y' end diff_tp
from
(SELECT papf1.person_id,
       ppa.payroll_action_id,
       NVL(sum(bal.balance_value),'0') Totalpay
    from pay_pay_relationships_dn pprd
    ,pay_payroll_rel_actions pra
    ,pay_payroll_actions ppa
    ,pay_action_classes pac
    ,pay_balance_types_vl pbt
    ,per_all_people_f papf1
    ,per_people_legislative_f ppl
    ,table(pay_balance_view_pkg.get_balance_dimensions
    (p_balance_type_id => pbt.balance_type_id
    ,p_payroll_rel_action_id => pra.payroll_rel_action_id
    ,p_payroll_term_id => null
    ,p_payroll_assignment_id => null)) bal
    ,pay_dimension_usages_vl pdu
    ,pay_time_periods    ptp
    where PAYROLL_RELATIONSHIP_NUMBER = papf1.person_number
    and papf1.person_id = ppl.person_id
    and ppa.effective_date BETWEEN ptp.start_date and ptp.end_date
    --and pdu.dimension_name = 'Assignment Run'
    and pdu.dimension_name = 'Relationship Run'
    and pdu.LEGISLATION_CODE = ppl.LEGISLATION_CODE
    AND ptp.time_period_id = ppa.earn_time_period_id
    and trunc(ptp.end_date) BETWEEN PTP.START_DATE AND PTP.END_DATE
    AND trunc(ptp.end_date) BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE
    AND trunc(ptp.end_date) BETWEEN PPL.EFFECTIVE_START_DATE AND PPL.EFFECTIVE_END_DATE
    and pra.payroll_relationship_id = pprd.payroll_relationship_id
    and pra.retro_component_id is null
    and exists (select 1 from pay_run_results prr
    where prr.payroll_rel_action_id = pra.payroll_rel_action_id)
    and ppa.payroll_action_id = pra.payroll_action_id
    AND ( pra.payroll_action_id IN (:P_FLOWNAME))
    and pac.action_type = ppa.action_type
    and pac.classification_name = 'SEQUENCED'
    and Upper(pbt.balance_name) = 'Total Salary'
    and nvl(bal.balance_value,0) != 0
    and pdu.balance_dimension_id = bal.balance_dimension_id
    AND ppa.earn_time_period_id =:P_PERIOD_NAME
group by papf1.person_id, ppa.payroll_action_id) current_tp,
(SELECT papf1.person_id,
       ppa.payroll_action_id,
       NVL(sum(bal.balance_value),'0') Totalpay
    from pay_pay_relationships_dn pprd
    ,pay_payroll_rel_actions pra
    ,pay_payroll_actions ppa
    ,pay_action_classes pac
    ,pay_balance_types_vl pbt
    ,per_all_people_f papf1
    ,per_people_legislative_f ppl
    ,table(pay_balance_view_pkg.get_balance_dimensions
    (p_balance_type_id => pbt.balance_type_id
    ,p_payroll_rel_action_id => pra.payroll_rel_action_id
    ,p_payroll_term_id => null
    ,p_payroll_assignment_id => null)) bal
    ,pay_dimension_usages_vl pdu
    ,pay_time_periods    ptp
    where PAYROLL_RELATIONSHIP_NUMBER = papf1.person_number
    and papf1.person_id = ppl.person_id
    and ppa.effective_date BETWEEN ptp.start_date and ptp.end_date
    --and pdu.dimension_name = 'Assignment Run'
    and pdu.dimension_name = 'Relationship Run'
    and pdu.LEGISLATION_CODE = ppl.LEGISLATION_CODE
    AND ptp.time_period_id = ppa.earn_time_period_id
    and trunc(ptp.end_date) BETWEEN PTP.START_DATE AND PTP.END_DATE
    AND trunc(ptp.end_date) BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE
    AND trunc(ptp.end_date) BETWEEN PPL.EFFECTIVE_START_DATE AND PPL.EFFECTIVE_END_DATE
    and pra.payroll_relationship_id = pprd.payroll_relationship_id
    and pra.retro_component_id is null
    and exists (select 1 from pay_run_results prr
    where prr.payroll_rel_action_id = pra.payroll_rel_action_id)
    and ppa.payroll_action_id = pra.payroll_action_id
    AND ( pra.payroll_action_id IN (:P_PRE_FLOW_NAME))
    and pac.action_type = ppa.action_type
    and pac.classification_name = 'SEQUENCED'
    and Upper(pbt.balance_name) = 'Total Salary'
    and nvl(bal.balance_value,0) != 0
    and pdu.balance_dimension_id = bal.balance_dimension_id
    AND ppa.earn_time_period_id =:P_PRE_PERIOD_NAME
group by papf1.person_id, ppa.payroll_action_id) previous_tp
where 1=1
and current_tp.person_id = previous_tp.person_id(+)

3. Old and New Bank Details

SELECT 

current_b.bankname c_bank_name,

current_b.bank_account_num c_bank_account_num,

current_b.iban c_iban,

previous_b.bankname p_bank_name,

previous_b.bank_account_num p_bank_account_num,

previous_b.iban p_iban,

current_b.payroll_relationship_id,

case when ( nvl(current_b.bankname,-1) != nvl(previous_b.bankname,-1) ) then 'Y' else 'N' end bank_diff,

case when ( nvl(current_b.bank_account_num,-1) != nvl(previous_b.bank_account_num,-1) ) then 'Y' else 'N' end bankacct_diff,

case when ( nvl(current_b.iban,-1) != nvl(previous_b.iban,-1) ) then 'Y' else 'N' end bankiban_diff

from

(SELECT DISTINCT

            ieb.bank_name bankname,

            TO_CHAR(iba.bank_account_num) bank_account_num,

            iba.iban,

            ppp.payroll_relationship_id

        FROM

            pay_person_pay_methods_f ppp,

            iby_ext_bank_accounts_v ieb,

            iby_ext_bank_accounts iba

        WHERE

            1 = 1

            AND PPP.LEGISLATIVE_DATA_GROUP_ID =:P_LDG

            AND ppp.payroll_relationship_id (+) = :M_payroll_relationship_id

            AND ieb.bank_account_id (+) = ppp.bank_account_id

            AND iba.ext_bank_account_id (+) = ieb.ext_bank_account_id

            AND trunc(:M_CP_END_DATE) BETWEEN ppp.effective_start_date (+) AND ppp.effective_end_date (+)

 ) current_b,          

(SELECT DISTINCT

            ieb.bank_name bankname,

            TO_CHAR(iba.bank_account_num) bank_account_num,

            iba.iban,

            ppp.payroll_relationship_id

        FROM

            pay_person_pay_methods_f ppp,

            iby_ext_bank_accounts_v ieb,

            iby_ext_bank_accounts iba

        WHERE

            1 = 1

            AND PPP.LEGISLATIVE_DATA_GROUP_ID =:P_LDG

            AND ppp.payroll_relationship_id (+) = :M_payroll_relationship_id

            AND ieb.bank_account_id (+) = ppp.bank_account_id

            AND iba.ext_bank_account_id (+) = ieb.ext_bank_account_id

            AND trunc(:M_PP_END_DATE) BETWEEN ppp.effective_start_date (+) AND ppp.effective_end_date (+)            

 ) previous_b           

where current_b.payroll_relationship_id = previous_b.payroll_relationship_id(+)

4. Earnings List

select 

          curr.person_id

,curr.earn_time_period_id earn_time_period_id_c

,curr.payroll_action_id  payroll_action_id_c 

         ,curr.element_type_id    element_type_id_c

,curr.base_element_name base_element_name_c

         ,curr.base_name  base_name_c

         ,curr.earnings earnings_c

         ,curr.TOTAL_EARNINGS TOTAL_EARNINGS_c

         -- curr.person_id

,previous.earn_time_period_id earn_time_period_id_p

,previous.payroll_action_id  payroll_action_id_p 

         ,previous.element_type_id    element_type_id_p

,previous.base_element_name base_element_name_p

         ,previous.base_name  base_name_p

         ,previous.earnings earnings_p

         ,previous.TOTAL_EARNINGS TOTAL_EARNINGS_p

         ,(nvl(curr.earnings,0) - nvl(previous.earnings,0)) DIFF_EARNINGS

         ,(nvl(curr.TOTAL_EARNINGS,0) - nvl(previous.TOTAL_EARNINGS,0)) DIFF_TOTAL_EARNINGS   

from

(SELECT  distinct  papf.person_id

,ppa.earn_time_period_id

,ppa.payroll_action_id

         ,petf.element_type_id

,petf_tl.element_name base_element_name

         ,pivf.base_name 

         ,sum(nvl(prrv.result_value,0)) OVER (PARTITION BY papf.person_id,ppa.earn_time_period_id,ppa.payroll_action_id,petf_tl.element_name) earnings

        , SUM(nvl(prrv.result_value,0)) OVER (PARTITION BY papf.person_id) TOTAL_EARNINGS

FROM      pay_run_result_values prrv

         ,pay_input_values_f pivf

         ,pay_run_results prr

         ,per_all_people_f papf

         ,hr_locations hl

         ,per_all_assignments_m paam

         ,pay_rel_groups_dn prgd

         ,pay_payroll_rel_actions ppra

         ,pay_flow_instances pfi

         ,pay_requests pr

         ,pay_payroll_actions ppa

,pay_element_types_f petf

,PAY_ELEMENT_TYPES_TL petf_tl

,pay_ele_classifications pec

,pay_time_periods ptp

WHERE     1=1    

  AND prrv.input_value_id = pivf.input_value_id

          AND prrv.run_result_id = prr.run_result_id

          AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date

          --AND pivf.base_name in ('Pay Value')

          AND NVL(PIVF.reserved_input_value,'X') =  'SPECIAL_OUTPUT'

          AND pivf.element_type_id = petf.element_type_id

          AND petf.element_type_id = prr.element_type_id

  AND petf.classification_id=pec.classification_id

  and petf_tl.element_type_id = petf.element_type_id

  and petf_tl.element_type_id = prr.element_type_id

  and petf_tl.language='US'

  and petf_tl.source_lang='US'

          AND prr.payroll_rel_action_id = ppra.payroll_rel_action_id

          AND ppa.effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date

          AND papf.person_id = paam.person_id

          AND hl.location_id(+) = paam.location_id

          AND ppa.effective_date BETWEEN paam.effective_start_date AND paam.effective_end_date

          AND paam.effective_latest_change = 'Y'

          AND paam.assignment_id = prgd.assignment_id

          AND prgd.group_type = 'A'

          AND ppra.source_id IS NULL

          --AND prr.payroll_term_id = prgd.parent_rel_group_id

          and prr.status='P' -- 06052019

          AND prgd.payroll_relationship_id = ppra.payroll_relationship_id

          AND ppra.action_status = 'C'

          AND ppra.payroll_action_id = ppa.payroll_action_id

          AND pfi.flow_instance_id = pr.flow_instance_id

          AND pr.pay_request_id = ppa.pay_request_id

          --AND ppa.action_type IN ('Q', 'R','B')

          and (ppa.action_type in (:p_action_type) or least(:p_action_type) is null)

  AND ppa.payroll_id=ptp.payroll_id

  and ptp.time_period_id=ppa.earn_time_period_id

  and ptp.period_category = 'E'

  AND pec.base_classification_name in ('Standard Earnings','Supplemental Earnings','Direct Payments')

          AND ppa.earn_time_period_id =:P_PERIOD_NAME

          and (ppa.payroll_action_id in (:P_FLOWNAME ) OR LEAST(:P_FLOWNAME ) IS NULL)

          --AND ppa.payroll_action_id = :p_flowname

  AND trunc(ptp.end_date) between petf.effective_start_date and petf.effective_end_date

         and paam.legal_entity_id=:P_LE

         and (papf.person_id in (:P_PN) OR LEAST(:P_PN) IS NULL)

         ) curr,

(SELECT  distinct  papf.person_id

,ppa.earn_time_period_id

,ppa.payroll_action_id

         ,petf.element_type_id

,petf_tl.element_name base_element_name

         ,pivf.base_name 

         ,sum(nvl(prrv.result_value,0)) OVER (PARTITION BY papf.person_id,ppa.earn_time_period_id,ppa.payroll_action_id,petf_tl.element_name) earnings

        , SUM(nvl(prrv.result_value,0)) OVER (PARTITION BY papf.person_id) TOTAL_EARNINGS

FROM      pay_run_result_values prrv

         ,pay_input_values_f pivf

         ,pay_run_results prr

         ,per_all_people_f papf

         ,hr_locations hl

         ,per_all_assignments_m paam

         ,pay_rel_groups_dn prgd

         ,pay_payroll_rel_actions ppra

         ,pay_flow_instances pfi

         ,pay_requests pr

         ,pay_payroll_actions ppa

,pay_element_types_f petf

,PAY_ELEMENT_TYPES_TL petf_tl

,pay_ele_classifications pec

,pay_time_periods ptp

WHERE     1=1    

  AND prrv.input_value_id = pivf.input_value_id

          AND prrv.run_result_id = prr.run_result_id

          AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date

          --AND pivf.base_name in ('Pay Value')

          AND NVL(PIVF.reserved_input_value,'X') =  'SPECIAL_OUTPUT'

          AND pivf.element_type_id = petf.element_type_id

          AND petf.element_type_id = prr.element_type_id

  AND petf.classification_id=pec.classification_id

  and petf_tl.element_type_id = petf.element_type_id

  and petf_tl.element_type_id = prr.element_type_id

  and petf_tl.language='US'

  and petf_tl.source_lang='US'

          AND prr.payroll_rel_action_id = ppra.payroll_rel_action_id

          AND ppa.effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date

          AND papf.person_id = paam.person_id

          AND hl.location_id(+) = paam.location_id

          AND ppa.effective_date BETWEEN paam.effective_start_date AND paam.effective_end_date

          AND paam.effective_latest_change = 'Y'

          AND paam.assignment_id = prgd.assignment_id

          AND prgd.group_type = 'A'

          AND ppra.source_id IS NULL

          --AND prr.payroll_term_id = prgd.parent_rel_group_id

          and prr.status='P' -- 06052019

          AND prgd.payroll_relationship_id = ppra.payroll_relationship_id

          AND ppra.action_status = 'C'

          AND ppra.payroll_action_id = ppa.payroll_action_id

          AND pfi.flow_instance_id = pr.flow_instance_id

          AND pr.pay_request_id = ppa.pay_request_id

          --AND ppa.action_type IN ('Q', 'R','B')

          and (ppa.action_type in (:p_action_type) or least(:p_action_type) is null)

  AND ppa.payroll_id=ptp.payroll_id

  and ptp.time_period_id=ppa.earn_time_period_id

  and ptp.period_category = 'E'

  AND pec.base_classification_name in ('Standard Earnings','Supplemental Earnings','Direct Payments')

          --AND ppa.earn_time_period_id =:P_PRE_PERIOD_NAME

          and (ppa.payroll_action_id in (:P_PRE_FLOW_NAME ) OR LEAST(:P_PRE_FLOW_NAME ) IS NULL)

  AND trunc(ptp.end_date) between petf.effective_start_date and petf.effective_end_date

         and paam.legal_entity_id=:P_LE

         and (papf.person_id in (:P_PN) OR LEAST(:P_PN) IS NULL)

         ) previous     

where 1=1

and           curr.person_id = previous.person_id(+)

and           curr.element_type_id = previous.element_type_id(+)

and           curr.base_element_name =  previous.base_element_name(+)

Add Dynamic Columns in RTF Based BI Publisher Report

Objective is to get Layout as below dynamically, where first row columns are dynamic.

Basic Salary			Exchange Commission			Food Allowance
Current	Prev.	Diff.	Current	Prev.	Diff.	Current	Prev.	Diff.
12,345.00	12,344.00	1.00	12,345.00	12,344.00	1.00	12,345.00	12,344.00	1.00

<G_EARNINGS>

<PERSON_ID>100000003353654</PERSON_ID>

<PAYROLL_ACTION_ID>43029</PAYROLL_ACTION_ID>

<BASE_ELEMENT_NAME>Basic Salary</BASE_ELEMENT_NAME>

<CURRENT></CURRENT>

<PREVIOUS></PREVIOUS>

<DIFFERENCE></DIFFERENCE>

</G_EARNINGS>

<?for-each-group@column://G_EARNINGS;./BASE_ELEMENT_NAME?><?sort:current-group()/BASE_ELEMENT_NAME;'ascending';data-type='text'?><?BASE_ELEMENT_NAME?> <?end for-each-group?>
<?for-each-group@column://G_EARNINGS;./BASE_ELEMENT_NAME?>Current	Prev.	Diff. <?end for-each-group?>
<?for-each-group@column://G_EARNINGS;./BASE_ELEMENT_NAME?><?CURRENT?>	<?PREVIOUS?>	<?DIFFERENCE?><?end for-each-group?>

Row#1 : It will create dynamic columns for each value of BASE_ELEMENT_NAME, which is grouped by base_element_name, hence it will place distinct values of same.

Row#2: It will also repeat but will add 3 columns for each iteration of for-each-group.

Row#3: same as row2 but instead of static data, it will add values.

Basic Salary			Exchange Commission			Food Allowance
Current	Prev.	Diff.	Current	Prev.	Diff.	Current	Prev.	Diff.
12,345.00	12,344.00	1.00	12,345.00	12,344.00	1.00	12,345.00	12,344.00	1.00