ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F44E3869E053B46B63588994Y 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) PSP PSPRCLSL_XML US PSP Reconciliation between payroll lines and sublines Application: Labor Distribution Source: PSP: Reconciliation between payroll lines and sublines (XML) Short Name: PSPRCLSL_XML DB package: PSP_PSPRCLSL_XMLP_PKG BI Publisher :cf_amt_sl_d :cf_currency_format :cp_credit_amount_sl :cs_sum_l_c_total :cs_sum_l_d_total :cs_sum_sl_c_total :cs_sum_sl_d_total :l_credit_amount :l_debit_amount :p_batch_name :p_conc_request_id :p_payroll_id :p_source_code :p_source_type :p_start_date :p_time_period_id :sum_l_c_assg :sum_l_c_person :sum_l_d_assg :sum_l_d_person :sum_sl_c_assg :sum_sl_c_person :sum_sl_d_assg :sum_sl_d_person 1 10 :p_source_type LOV Oracle PSP_RECON_SOURCE_TYPE Y select lookup_code id, meaning value, null description from psp_lookups where lookup_type='PSP_SOURCE_TYPE' and lookup_code in ('O','N') and lookup_code in (select source_type from psp_payroll_controls where business_group_id=fnd_global.per_business_group_id and set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID')) order by meaning Y US Source Type 2 20 :p_source_code LOV Oracle PSP_RECON_SOURCE_CODE Y select source_code id, source_code value, null description from psp_payroll_sources where source_type=:$flex$.psp_recon_source_type and source_code in (select payroll_source_code from psp_payroll_controls where business_group_id=fnd_global.per_business_group_id and set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID')) order by source_code Y US Source Code 3 30 :p_payroll_id LOV Oracle PSP_SRS_IMP_PAYROLL_NAME Y select payroll_id id, payroll_name value, null description from pay_payrolls_f where sysdate between effective_start_date and effective_end_date and business_group_id=fnd_global.per_business_group_id and gl_set_of_books_id= fnd_profile.value('GL_SET_OF_BKS_ID') order by value,description Y US Payroll Name 4 40 :p_time_period_id LOV Oracle PSP_RECON_TIME_PERIOD Y select time_period_id id, period_name value, null description from per_time_periods where time_period_id in (select time_period_id from psp_payroll_controls where business_group_id=fnd_global.per_business_group_id and set_of_books_id=fnd_profile.value('gl_set_of_bks_id') and payroll_id=:$flex$.psp_srs_imp_payroll_name and source_type=:$flex$.psp_recon_source_type and payroll_source_code=:$flex$.psp_recon_source_code) order by time_period_id Y US Time Period 5 50 :p_batch_name LOV Oracle PSP_RECON_BATCH_NAME Y select batch_name id, batch_name value, null description from psp_batch_names_v where batch_name in (select batch_name from psp_payroll_controls where business_group_id=fnd_global.per_business_group_id and set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID') and source_type=:$flex$.psp_recon_source_type and payroll_source_code=:$flex$.psp_recon_source_code) order by batch_name US Batch Name