PSP Reconciliation between payroll lines and sublines

Description
Categories: BI Publisher
Application: Labor Distribution
Source: PSP: Reconciliation between payroll lines and sublines (XML)
Short Name: PSPRCLSL_XML
DB package: PSP_PSPRCLSL_XMLP_PKG
Run PSP Reconciliation between payroll lines and sublines and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT	ppl.person_id person_id,
	ppl.assignment_id assignment_id,
	ppl.element_type_id element_type_id,
	psp_general.get_element_name(ppl.element_type_id,ppl.effective_date) element_name , 
	sum(decode(ppl.dr_cr_flag, 'C', ppl.pay_amount, 0)) l_credit_amount,
	sum(decode(ppl.dr_cr_flag, 'D', ppl.pay_amount, 0)) l_Debit_amount,
                   ppc.currency_code,
	PSP_PSPRCLSL_XMLP_PKG.CF_currency_codeFormula(ppc.currency_code) CF_currency_code,
PSP_PSPRCLSL_XMLP_PKG.CF_currency_formatFormula(ppc.currency_code) CF_currency_format,
PSP_PSPRCLSL_XMLP_PKG.CF_sum_l_d_total_dspFormula(:cs_sum_l_d_total,:cf_currency_format) CF_sum_l_d_total_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_sum_sl_d_total_dspFormula(:cs_sum_sl_d_total,:cf_currency_format) CF_sum_sl_d_total_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_sum_sl_c_total_dspFormula(:cs_sum_sl_c_total,:cf_currency_format) CF_sum_sl_c_total_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_mismatch_currencyFormula(:cs_sum_l_d_total,:cs_sum_sl_d_total,:cs_sum_l_c_total,:cs_sum_sl_c_total) CF_mismatch_currency,
PSP_PSPRCLSL_XMLP_PKG.CF_sum_l_c_total_dspFormula(:cs_sum_l_c_total,:cf_currency_format) CF_sum_l_c_total_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_person_nameFormula(ppl.person_id) CF_person_name,
PSP_PSPRCLSL_XMLP_PKG.CF_sum_l_d_person_dspFormula(:sum_l_d_person,:cf_currency_format) CF_sum_l_d_person_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_sum_l_c_person_dspFormula(:sum_l_c_person,:cf_currency_format) CF_sum_l_c_person_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_sum_sl_d_person_dspFormula(:sum_sl_d_person,:cf_currency_format) CF_sum_sl_d_person_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_sum_sl_c_person_dspFormula(:sum_sl_c_person,:cf_currency_format) CF_sum_sl_c_person_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_mismatch_personFormula(:sum_l_d_person,:sum_sl_d_person,:sum_l_c_person,:sum_sl_c_person) CF_mismatch_person,
PSP_PSPRCLSL_XMLP_PKG.CF_assignment_numberFormula(ppl.assignment_id) CF_assignment_number,
PSP_PSPRCLSL_XMLP_PKG.CF_sum_l_d_assg_dspFormula(:sum_l_d_assg,:cf_currency_format) CF_sum_l_d_assg_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_sum_l_c_assg_dspFormula(:sum_l_c_assg,:cf_currency_format) CF_sum_l_c_assg_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_sum_sl_d_assg_dspFormula(:sum_sl_d_assg,:cf_currency_format) CF_sum_sl_d_assg_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_sum_sl_c_assg_dspFormula(:sum_sl_c_assg,:cf_currency_format) CF_sum_sl_c_assg_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_mismatch_assgFormula(:sum_l_d_assg,:sum_sl_d_assg,:sum_l_c_assg,:sum_sl_c_assg) CF_mismatch_assg,
PSP_PSPRCLSL_XMLP_PKG.CF_l_debit_amount_dspFormula(:l_debit_amount,:cf_currency_format) CF_l_debit_amount_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_l_credit_amount_dspFormula(:l_credit_amount,:cf_currency_format) CF_l_credit_amount_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_amt_l_dFormula() CF_amt_l_d,
PSP_PSPRCLSL_XMLP_PKG.CF_amt_l_cFormula() CF_amt_l_c,
PSP_PSPRCLSL_XMLP_PKG.CF_amt_sl_dFormula(ppl.person_id,ppl.assignment_id,ppl.element_type_id,ppc.currency_code) CF_amt_sl_d,
PSP_PSPRCLSL_XMLP_PKG.CF_amt_sl_d_dspFormula(:CF_amt_sl_d,:CF_currency_format) CF_amt_sl_d_dsp,
PSP_PSPRCLSL_XMLP_PKG.CF_amt_sl_cFormula() CF_amt_sl_c,
PSP_PSPRCLSL_XMLP_PKG.CF_credit_amount_sl_dspFormula(:cf_currency_format) CF_credit_amount_sl_dsp,
PSP_PSPRCLSL_XMLP_PKG.CP_credit_amount_slFormula() CP_credit_amount_sl,
PSP_PSPRCLSL_XMLP_PKG.CF_mismatch_eltFormula(:l_debit_amount,:CF_amt_sl_d,:l_credit_amount ) CF_mismatch_elt
FROM	psp_payroll_lines ppl,
	psp_payroll_controls ppc
WHERE 	ppl.payroll_control_id	= ppc.payroll_control_id
AND	ppc.source_type	= :p_source_type
AND	ppc.payroll_source_code = :p_source_code
AND	ppc.time_period_id	= :p_time_period_id
AND	(ppc.batch_name = :p_batch_name OR ppc.batch_name IS NULL)
GROUP BY
                   ppc.currency_code , 	ppl.person_id,
	ppl.assignment_id,
	ppl.element_type_id,
	psp_general.get_element_name(ppl.element_type_id,ppl.effective_date)
ORDER BY ppc.currency_code,ppl.person_id,ppl.assignment_id,ppl.element_type_id,psp_general.get_element_name(ppl.element_type_id,ppl.effective_date)
Parameter Name SQL text Validation
Source Type
 
LOV Oracle
Source Code
 
LOV Oracle
Payroll Name
 
LOV Oracle
Time Period
 
LOV Oracle
Batch Name
 
LOV Oracle
Blitz Report™