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) |
