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).
Wednesday 19 June 2019
Tuesday 18 June 2019
Designing XSL Sub Templates
Why?
1. Allow Reusable advance functionality for RTF templates.
What?
Its a xsl file, which consists of
1. Allow Reusable advance functionality for RTF templates.
- Transform Data structure for section of report
- 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.
Monday 17 June 2019
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.
------------------------------------------------
--------------------------------
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.
------------------------------------------------
Sunday 16 June 2019
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
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
Friday 14 June 2019
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)?>
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)?>
Thursday 13 June 2019
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.
<?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.
Wednesday 12 June 2019
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?>
Friday 7 June 2019
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(+)
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(+)
Thursday 6 June 2019
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?>
|
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
|
|
Subscribe to:
Posts (Atom)