PSP Reconciliation between payroll lines and sublines

➜ Back to Library
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
Download
Related Blitz Reports
PSP Encumbrance Suspense Charge PSP Reconciliation between distribution lines and summary PSP Distribution History by Project PSP Distribution History by Organization PSP Reconciliation between pre-generated distribution lines and summary lines PSP Encumbrance History by Organization PSP Encumbrance process run results PSP Encumbrance History by Award PSP Distribution History by Award
Blitz Report™

Blitz Report™ provides multiple benefits: