ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4720051A9E05362FB09050512Y SELECT psp_general.get_org_name(paf.organization_id) name, b.currency_code, a.person_id, psp_general.get_person_name(a.person_id, TRUNC(pdl.effective_date)) person_name, a.assignment_id, psp_general.get_assignment_num(a.assignment_id, TRUNC(pdl.effective_date)) assignment_number, ppl.element_type_id, psp_general.get_element_name(ppl.element_type_id, TRUNC(pdl.effective_date)) element_name, a.gl_code_combination_id gl_id, a.project_id, DECODE(a.project_id, NULL, NULL, psp_general.get_project_number(a.project_id)) project_number, a.award_id, DECODE(a.award_id, NULL, NULL, psp_general.get_award_number(a.award_id)) award_number, a.task_id, DECODE(a.task_id, NULL, NULL, psp_general.get_task_number(a.task_id)) task_number, a.expenditure_type exp_type, a.expenditure_organization_id, DECODE(a.expenditure_organization_id, NULL, NULL, psp_general.get_org_name(a.expenditure_organization_id)) expenditure_organization_name, SUM(DECODE(a.dr_cr_flag, 'C', -1*pdl.distribution_amount, 'D', pdl.distribution_amount)) EARNINGS_AMOUNT, MAX(pdl.effective_date) Effective_date, ptp.period_name, PSP_PSPLDORG_XMLP_PKG.cf_currency_formatformula(b.currency_code) CF_currency_format, PSP_PSPLDORG_XMLP_PKG.cf_currency_codeformula(b.currency_code) CF_currency_code, PSP_PSPLDORG_XMLP_PKG.cf_org_curr_amt_total_dspformu(:CF_currency_format, :CS_ORG_curr_AMT_TOTAL) CF_org_curr_amt_total_dsp, PSP_PSPLDORG_XMLP_PKG.cf_charging_descformula(a.gl_code_combination_id, DECODE ( a.project_id , NULL , NULL , psp_general.get_project_number ( a.project_id ) ), DECODE ( a.task_id , NULL , NULL , psp_general.get_task_number ( a.task_id ) ), DECODE ( a.award_id , NULL , NULL , psp_general.get_award_number ( a.award_id ) ), DECODE ( a.expenditure_organization_id , NULL , NULL , psp_general.get_org_name ( a.expenditure_organization_id ) ), a.expenditure_type) CF_CHARGING_DESC, PSP_PSPLDORG_XMLP_PKG.cf_employee_numberformula(a.person_id, MAX ( pdl.effective_date )) CF_Employee_Number, PSP_PSPLDORG_XMLP_PKG.cf_earnings_amount_dspformula(SUM ( DECODE ( a.dr_cr_flag , 'C' , - 1 * pdl.distribution_amount , 'D' , pdl.distribution_amount ) ), :CF_currency_format) CF_earnings_amount_dsp, PSP_PSPLDORG_XMLP_PKG.cf_time_period_nameformula() CF_Time_Period_Name FROM psp_distribution_lines_history pdl, psp_summary_lines a, per_assignments_f paf, psp_payroll_lines ppl, psp_payroll_sub_lines psl, psp_payroll_controls b, per_time_periods ptp WHERE &P_ORG_ID AND paf.assignment_id = ppl.assignment_id AND paf.business_group_id = :P_BUSINESS_GROUP_ID AND ppl.effective_date between paf.effective_start_date and paf.effective_end_date AND pdl.distribution_date between :P_BEGIN_DATE AND :P_END_DATE AND ppl.payroll_line_id = psl.payroll_line_id AND b.payroll_control_id = ppl.payroll_control_id AND psl.payroll_sub_line_id = pdl.payroll_sub_line_id AND pdl.summary_line_id = a.summary_line_id AND a.business_group_id = :P_BUSINESS_GROUP_ID AND a.set_of_books_id = :P_SET_OF_BOOKS_ID AND PTP.time_period_id = a.time_period_id AND NOT EXISTS (SELECT 1 FROM psp_adjustment_lines_history pdlh WHERE pdlh.orig_line_id = pdl.distribution_line_id AND pdlh.original_line_flag ='Y' AND pdlh.orig_source_type = 'D') GROUP BY psp_general.get_org_name(paf.organization_id), b.currency_code, a.person_id, psp_general.get_person_name(a.person_id, TRUNC(pdl.effective_date)), a.assignment_id, psp_general.get_assignment_num(a.assignment_id, TRUNC(pdl.effective_date)), ppl.element_type_id, psp_general.get_element_name(ppl.element_type_id, TRUNC(pdl.effective_date)), a.gl_code_combination_id, a.project_id, DECODE(a.project_id, NULL, NULL, psp_general.get_project_number(a.project_id)), a.award_id, DECODE(a.award_id, NULL, NULL, psp_general.get_award_number(a.award_id)), a.task_id, DECODE(a.task_id, NULL, NULL, psp_general.get_task_number(a.task_id)), a.expenditure_type, a.expenditure_organization_id, DECODE(a.expenditure_organization_id, NULL, NULL, psp_general.get_org_name(a.expenditure_organization_id)), ptp.period_name UNION ALL SELECT psp_general.get_org_name(paf.organization_id) name, b.currency_code, a.person_id, psp_general.get_person_name(a.person_id, TRUNC(a.effective_date)) person_name, a.assignment_id, psp_general.get_assignment_num(a.assignment_id, TRUNC(a.effective_date)) assignment_number, a.element_type_id, psp_general.get_element_name(a.element_type_id, TRUNC(a.effective_date)) element_name, a.gl_code_combination_id gl_id, a.project_id, DECODE(a.project_id, NULL, NULL, psp_general.get_project_number(a.project_id)) project_number, a.award_id, DECODE(a.award_id, NULL, NULL, psp_general.get_award_number(a.award_id)) award_number, a.task_id, DECODE(a.task_id, NULL, NULL, psp_general.get_task_number(a.task_id)) task_number, a.expenditure_type exp_type, a.expenditure_organization_id, DECODE(a.expenditure_organization_id, NULL, NULL, psp_general.get_org_name(a.expenditure_organization_id)) expenditure_organization_name, sum(decode(A.dr_cr_flag,'C',-1*A.distribution_amount,'D',A.distribution_amount)) EARNINGS_AMOUNT, MAX(a.effective_date) Effective_date, PTP.period_name, PSP_PSPLDORG_XMLP_PKG.cf_currency_formatformula(b.currency_code) CF_currency_format, PSP_PSPLDORG_XMLP_PKG.cf_currency_codeformula(b.currency_code) CF_currency_code, PSP_PSPLDORG_XMLP_PKG.cf_org_curr_amt_total_dspformu(:CF_currency_format, :CS_ORG_curr_AMT_TOTAL) CF_org_curr_amt_total_dsp, PSP_PSPLDORG_XMLP_PKG.cf_charging_descformula(a.gl_code_combination_id, DECODE ( a.project_id , NULL , NULL , psp_general.get_project_number ( a.project_id ) ), DECODE ( a.task_id , NULL , NULL , psp_general.get_task_number ( a.task_id ) ), DECODE ( a.award_id , NULL , NULL , psp_general.get_award_number ( a.award_id ) ), DECODE ( a.expenditure_organization_id , NULL , NULL , psp_general.get_org_name ( a.expenditure_organization_id ) ), a.expenditure_type) CF_CHARGING_DESC, PSP_PSPLDORG_XMLP_PKG.cf_employee_numberformula(a.person_id, MAX ( a.effective_date )) CF_Employee_Number, PSP_PSPLDORG_XMLP_PKG.cf_earnings_amount_dspformula(SUM ( DECODE ( a.dr_cr_flag , 'C' , - 1 * a.distribution_amount , 'D' , a.distribution_amount ) ), :CF_currency_format) CF_earnings_amount_dsp, PSP_PSPLDORG_XMLP_PKG.cf_time_period_nameformula() CF_Time_Period_Name FROM PSP_ADJUSTMENT_LINES_HISTORY a, PSP_PAYROLL_CONTROLS b, PER_ASSIGNMENTS_F paf, per_time_periods ptp WHERE a.assignment_id = paf.assignment_id AND &P_ORG_ID AND paf.business_group_id = :P_BUSINESS_GROUP_ID AND distribution_date between :P_BEGIN_DATE AND :P_END_DATE AND distribution_date between paf.effective_start_date and paf.effective_end_date AND a.PAYROLL_CONTROL_ID = b.PAYROLL_CONTROL_ID AND a.business_group_id = :P_BUSINESS_GROUP_ID AND a.set_of_books_id = :P_SET_OF_BOOKS_ID AND NVL(a.original_line_flag,'N') = 'N' AND PTP.time_period_id = a.time_period_id AND NOT EXISTS ( SELECT 1 FROM psp_adjustment_lines_history palh WHERE palh.orig_line_id = a.adjustment_line_id AND palh.original_line_flag = 'Y' AND palh.orig_source_type = 'A') GROUP BY psp_general.get_org_name(paf.organization_id), b.currency_code, a.person_id, psp_general.get_person_name(a.person_id, TRUNC(a.effective_date)), a.assignment_id, psp_general.get_assignment_num(a.assignment_id, TRUNC(a.effective_date)), a.element_type_id, psp_general.get_element_name(a.element_type_id, TRUNC(a.effective_date)), a.gl_code_combination_id, a.project_id, DECODE(a.project_id, NULL, NULL, psp_general.get_project_number(a.project_id)), a.award_id, DECODE(a.award_id, NULL, NULL, psp_general.get_award_number(a.award_id)), a.task_id, DECODE(a.task_id, NULL, NULL, psp_general.get_task_number(a.task_id)), a.expenditure_type, a.expenditure_organization_id, DECODE(a.expenditure_organization_id, NULL, NULL, psp_general.get_org_name(a.expenditure_organization_id)), ptp.period_name UNION ALL SELECT psp_general.get_org_name(paf.organization_id) name, b.currency_code, a.person_id, psp_general.get_person_name(a.person_id, TRUNC(a.effective_date)) person_name, a.assignment_id, psp_general.get_assignment_num(a.assignment_id, TRUNC(a.effective_date)) assignment_number, a.element_type_id, psp_general.get_element_name(a.element_type_id, TRUNC(a.effective_date)) element_name, a.gl_code_combination_id gl_id, a.project_id, DECODE(a.project_id, NULL, NULL, psp_general.get_project_number(a.project_id)) project_number, a.award_id, DECODE(a.award_id, NULL, NULL, psp_general.get_award_number(a.award_id)) award_number, a.task_id, DECODE(a.task_id, NULL, NULL, psp_general.get_task_number(a.task_id)) task_number, a.expenditure_type exp_type, a.expenditure_organization_id, DECODE(a.expenditure_organization_id, NULL, NULL, psp_general.get_org_name(a.expenditure_organization_id)) expenditure_organization_name, sum(decode(A.dr_cr_flag,'C',-1*A.distribution_amount,'D',A.distribution_amount)) EARNINGS_AMOUNT, MAX(a.effective_date) Effective_date, ptp.period_name, PSP_PSPLDORG_XMLP_PKG.cf_currency_formatformula(b.currency_code) CF_currency_format, PSP_PSPLDORG_XMLP_PKG.cf_currency_codeformula(b.currency_code) CF_currency_code, PSP_PSPLDORG_XMLP_PKG.cf_org_curr_amt_total_dspformu(:CF_currency_format, :CS_ORG_curr_AMT_TOTAL) CF_org_curr_amt_total_dsp, PSP_PSPLDORG_XMLP_PKG.cf_charging_descformula(a.gl_code_combination_id, DECODE ( a.project_id , NULL , NULL , psp_general.get_project_number ( a.project_id ) ), DECODE ( a.task_id , NULL , NULL , psp_general.get_task_number ( a.task_id ) ), DECODE ( a.award_id , NULL , NULL , psp_general.get_award_number ( a.award_id ) ), DECODE ( a.expenditure_organization_id , NULL , NULL , psp_general.get_org_name ( a.expenditure_organization_id ) ), a.expenditure_type) CF_CHARGING_DESC, PSP_PSPLDORG_XMLP_PKG.cf_employee_numberformula(a.person_id, MAX ( a.effective_date )) CF_Employee_Number, PSP_PSPLDORG_XMLP_PKG.cf_earnings_amount_dspformula(SUM ( DECODE ( a.dr_cr_flag , 'C' , - 1 * a.distribution_amount , 'D' , a.distribution_amount ) ), :CF_currency_format) CF_earnings_amount_dsp, PSP_PSPLDORG_XMLP_PKG.cf_time_period_nameformula() CF_Time_Period_Name FROM PSP_PRE_GEN_DIST_LINES_HISTORY a, PSP_PAYROLL_CONTROLS b, PER_ASSIGNMENTS_F paf, per_time_periods ptp WHERE a.assignment_id = paf.assignment_id AND &P_ORG_ID AND paf.business_group_id = :P_BUSINESS_GROUP_ID AND distribution_date between :P_BEGIN_DATE AND :P_END_DATE AND distribution_date between paf.effective_start_date and paf.effective_end_date AND a.PAYROLL_CONTROL_ID = b.PAYROLL_CONTROL_ID AND a.business_group_id = :P_BUSINESS_GROUP_ID AND a.set_of_books_id = :P_SET_OF_BOOKS_ID AND ptp.time_period_id = a.time_period_id AND NOT EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh WHERE palh.orig_line_id = a.pre_gen_dist_line_id AND palh.original_line_flag = 'Y' AND palh.orig_source_type = 'P') GROUP BY psp_general.get_org_name(paf.organization_id), b.currency_code, a.person_id, psp_general.get_person_name(a.person_id, TRUNC(a.effective_date)), a.assignment_id, psp_general.get_assignment_num(a.assignment_id, TRUNC(a.effective_date)), a.element_type_id, psp_general.get_element_name(a.element_type_id, TRUNC(a.effective_date)), a.gl_code_combination_id, a.project_id, DECODE(a.project_id, NULL, NULL, psp_general.get_project_number(a.project_id)), a.award_id, DECODE(a.award_id, NULL, NULL, psp_general.get_award_number(a.award_id)), a.task_id, DECODE(a.task_id, NULL, NULL, psp_general.get_task_number(a.task_id)), a.expenditure_type, a.expenditure_organization_id, DECODE(a.expenditure_organization_id, NULL, NULL, psp_general.get_org_name(a.expenditure_organization_id)), ptp.period_name ORDER BY name, currency_code, person_name, assignment_number , element_name PSP PSPLDORG_XML PTB PSP Relatório do Histórico de Classificação Contábil por Organização Imported from BI Publisher Description: Histórico de Classificação Contábil de Mão de Obra por Organização Application: Distribuição da Mão de Obra Source: PSP: Relatório do Histórico de Classificação Contábil por Organização (XML) Short Name: PSPLDORG_XML DB package: PSP_PSPLDORG_XMLP_PKG US PSP Distribution History by Organization Imported from BI Publisher Description: Labor Distribution History by Organization Application: Labor Distribution Source: PSP: Distribution History by Organization Report (XML) Short Name: PSPLDORG_XML DB package: PSP_PSPLDORG_XMLP_PKG BI Publisher &p_org_id :cf_currency_format :cs_org_curr_amt_total :p_begin_date :p_business_group_id :p_conc_request_id :p_end_date :p_ogm_period :p_org_id :p_org_template_id :p_set_of_books_id 1 -40 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') PTB P_BUSINESS_GROUP_ID Id do Grupo Funcional US P_BUSINESS_GROUP_ID Business Group Id 2 -30 :p_set_of_books_id Number fnd_profile.value('GL_SET_OF_BKS_ID') PTB P_SET_OF_BOOKS_ID Id do Razão US P_SET_OF_BOOKS_ID Ledger Id 3 -20 :p_org_param_type Char ORG_SET PTB PSP_REPORT_TYPE Tipo de Parâmetro da Organização US PSP_REPORT_TYPE Org Parameter Type 4 10 :p_org_template_id LOV Oracle PSP_REPORT_TEMPLATE_NAME Y select template_id id, template_name value, null description from psp_report_templates where business_group_id=fnd_global.per_business_group_id and set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID') and report_type=:$flex$.psp_report_type order by value,description PTB Conjunto de Organizações Conjunto de Parâmetros da Organização US Organization Set Organization Parameter Set 5 20 :p_begin_date Date Y PTB Data Inicial Data Inicial US Begin Date Begin Date 6 30 :p_end_date Date Y PTB Data Final Data Final US End Date End Date