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

Caused by: oracle.xdo.parser.v2.XPathException: Variable not defined: '_MR'.

Noticed that this issue is caused when you insert a graphical object like Text Box in rtf file, try to remove and this error should go away.

Dynamic Header and Footer in XML Publisher rtf Template

Code highlight in yellow is important.

--place below text in header

<?call@inlines:HeaderLogo?><?with-param:orgName;.//DocumentPayable/InvoiceBusinessUnit/InvoiceBusinessUnitName?><?end call?>

Place below in rtf main body

<?start-body?>

<?for-each-group@section:OutboundPayment;.//DocumentPayable/InvoiceBusinessUnit/InvoiceBusinessUnitName?>

---write desired code/tablees here ---

<?end for-each-group?>

<?end-body?>

--place below text in footer

<?call@inlines:footer_content?><?with-param:orgName; .//DocumentPayable/InvoiceBusinessUnit/InvoiceBusinessUnitName?><?end call?>

Subtemplates used :

<?template:HeaderLogo?>

<?param: orgName;’zz’?>

<?choose:?>

<?when: $orgName = ‘ABC Company’?>ABC LTD (You can attach Image here as well)<?end when?>

<?when: $orgName =’XYZ Company’?>XYZ LTD (You can attach Image here as well)<?end when?>

<?otherwise:?>

none

<?end otherwise?>

<?end choose?>

<?end template?>

                                                                       

<?template: footer_content?>

<?param: orgName;’tt’?>

<?choose:?>

<?when: $orgName = ‘ABC Company’?>ABC LTD (You can attach Image here as well)<?end when?>

<?when: $orgName =’XYZ Company’?>XYZ LTD (You can attach Image here as well)<?end when?>

<?end otherwise?>

none

<?end choose?>

<?end template?>

Fusion FA Asset Register Report : SQL

SELECT 
 FA.ASSET_NUMBER
,FAT.DESCRIPTION
,FB.ORIGINAL_COST
,FB.DATE_PLACED_IN_SERVICE
,FA.ATTRIBUTE_CATEGORY_CODE
,FCB.SEGMENT1 MAJOR_CATEGORY
,FCB.SEGMENT2 MINOR_CATEGORY
,NVL(FDS.DEPRN_RESERVE,0) DEPRN_RESERVE
,NVL(FDS.YTD_DEPRN,0) YTD_DEPRN
,(select FL.SEGMENT1 ||' ,'|| FL.SEGMENT2 ||' ,'|| FL.SEGMENT3 from fa_locations FL where FL.location_id = FDH.LOCATION_ID) location
,(SELECT FULL_NAME FROM PER_PERSON_NAMES_F WHERE PERSON_ID= FDH.ASSIGNED_TO AND NAME_TYPE ='GLOBAL') EMP_NAME
, (SELECT TO_CHAR(TRUNC(METH.LIFE_IN_MONTHS/12) +
             (MOD(METH.LIFE_IN_MONTHS,12)/100), '9000D00')
                                LIFE_IN_YR_MONTHS
FROM FA_METHODS METH
WHERE 1=1
AND METH.METHOD_ID = FB.METHOD_ID
AND ROWNUM =1
   ) LIFE_IN_YR_MONTHS
,1/(SELECT (METH.LIFE_IN_MONTHS/12) LIFE_IN_YR
FROM FA_METHODS METH
WHERE 1=1
AND METH.METHOD_ID = FB.METHOD_ID
AND ROWNUM =1
   ) DEPRN_RATIO
--,FCATB.ASSET_COST_ACCOUNT_CCID COST_ACCT_CCID
--,FCATB.RESERVE_ACCOUNT_CCID ACCUMULATED_DEPRN_CCID
--,FDH.CODE_COMBINATION_ID EXPENSE_CCID
--, (SELECT SEGMENT4 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = FCATB.ASSET_COST_ACCOUNT_CCID ) COST_ACCT
--, (SELECT SEGMENT4 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = FCATB.RESERVE_ACCOUNT_CCID    ) ACCDEP_ACCT
--, (SELECT SEGMENT4 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID       ) EXP_ACCT
--,FDS_P.DEPRN_RESERVE DEPRN_RESERVE_PREVIOUS
--,FDS_P.DEPRN_AMOUNT
--,FDH.ASSIGNED_TO
--,FDH.LOCATION_ID
--,(SELECT PERIOD_NAME FROM FA_DEPRN_PERIODS WHERE PERIOD_COUNTER = FDP_C.PERIOD_COUNTER and BOOK_TYPE_CODE = NVL(:P_BOOK_TYPE_CODE, '-1') ) CURRENT_PERIOD
--,FDP_C.PERIOD_COUNTER CURRENT_PERIOD_COUNTER
--,FDP_P.PERIOD_COUNTER PREVIOUS_PERIOD_COUNTER
--,(SELECT PERIOD_NAME FROM FA_DEPRN_PERIODS WHERE PERIOD_COUNTER = FDP_P.PERIOD_COUNTER and BOOK_TYPE_CODE = NVL(:P_BOOK_TYPE_CODE, '-1')) PREVIOUS_PERIOD
--ROUND(SYSDATE - FB.DATE_PLACED_IN_SERVICE,0) DAYS_IN_SERVICE
FROM
  FA_ADDITIONS_B FA
, FA_ADDITIONS_TL FAT
, FA_BOOKS FB
, FA_CATEGORIES_B FCB
, FA_CATEGORY_BOOKS FCATB
, FA_DEPRN_SUMMARY FDS
--, FA_DEPRN_SUMMARY FDS_P
, FA_DISTRIBUTION_HISTORY FDH
, (SELECT MAX(PERIOD_COUNTER) AS PERIOD_COUNTER FROM FA_DEPRN_PERIODS WHERE PERIOD_CLOSE_DATE IS NULL AND BOOK_TYPE_CODE =  NVL(:P_BOOK_TYPE_CODE, '-1') ) FDP_C
--, (SELECT MAX(PERIOD_COUNTER) AS PERIOD_COUNTER FROM FA_DEPRN_PERIODS WHERE PERIOD_CLOSE_DATE IS NOT NULL AND BOOK_TYPE_CODE =  NVL(:P_BOOK_TYPE_CODE, '-1') ) FDP_P
, FA_BOOK_CONTROLS FBC
WHERE 1=1
AND FAT.ASSET_ID = FA.ASSET_ID
AND ( userenv('LANG') ) = FAT.language
------
AND FA.ASSET_ID = FB.ASSET_ID 
AND FCB.CATEGORY_ID = FA.ASSET_CATEGORY_ID
AND FCATB.CATEGORY_ID = FCB.CATEGORY_ID
AND FB.BOOK_TYPE_CODE = FCATB.BOOK_TYPE_CODE
AND FB.DATE_INEFFECTIVE IS NULL
AND FDS.ASSET_ID(+) = FA.ASSET_ID
AND FDS.BOOK_TYPE_CODE(+) = FB.BOOK_TYPE_CODE
AND FDS.PERIOD_COUNTER(+) = FDP_C.PERIOD_COUNTER
--AND FDS_P.ASSET_ID = FA.ASSET_ID
--AND FDS_P.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
--AND FDS_P.PERIOD_COUNTER = FDP_P.PERIOD_COUNTER
AND FDH.TRANSACTION_HEADER_ID_OUT IS NULL
AND FDH.ASSET_ID = FA.ASSET_ID
AND FDH.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FBC.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FBC.SET_OF_BOOKS_ID = :P_LEDGER_ID
--AND NVL(FDH.ASSIGNED_TO,-1) = NVL(:P_EMPLOYEE_ID,NVL(FDH.ASSIGNED_TO,-1))
and (NVL(FDH.ASSIGNED_TO,-1) IN (:P_EMPLOYEE_ID) OR LEAST(:P_EMPLOYEE_ID) IS NULL)
and (FCB.SEGMENT1 IN (:P_MAJOR_CATEGORY_NAME) OR LEAST(:P_MAJOR_CATEGORY_NAME) IS NULL)
and (FCB.SEGMENT2 IN (:P_MINOR_CATEGORY_NAME) OR LEAST(:P_MINOR_CATEGORY_NAME) IS NULL)
AND NVL(FDH.LOCATION_ID,-1) BETWEEN NVL(:P_FROM_LOCATION_ID, NVL(FDH.LOCATION_ID,-1)) AND NVL(:P_TO_LOCATION_ID, NVL(FDH.LOCATION_ID,-1))
AND FA.ASSET_NUMBER BETWEEN NVL(:P_FROM_ASSET_NO, FA.ASSET_NUMBER) AND NVL(:P_TO_ASSET_NO, NVL(:P_FROM_ASSET_NO, FA.ASSET_NUMBER))
AND FB.BOOK_TYPE_CODE = NVL(:P_BOOK_TYPE_CODE, '-1')
AND EXISTS (select 'x' from FA_CATEGORY_BOOKS where book_type_code = :p_book_type_code and category_id = fcb.category_id)

Receipt Accounting SQL in Fusion Receivables and Transaction Accounting SQL in Fusion Receivables

SELECT
 xte.entity_code, 
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 = 222
    AND xah.application_id = 222
    AND xte.application_id = 222
    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 ='RECEIPTS'
and gled.ledger_id= :LEDGER_ID
and  xte.source_id_int_1 = :m_cash_receipt_id
order by  xal.accounted_dr,     xal.displayed_line_number

###################################################
Receivables Invoice or Transaction Accounting SQL
###################################################

SELECT
 xte.entity_code, 
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 = 222
    AND xah.application_id = 222
    AND xte.application_id = 222
    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 ='TRANSACTIONS'
and gled.ledger_id= :LEDGER_ID
and  xte.source_id_int_1 = :RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID
order by  xal.accounted_dr,     xal.displayed_line_number

##########################
select primary_ledger_id from fun_fin_business_units_v where bu_name like 'XX%'