ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D471FE51A9E05362FB09050512 Select a.PERSON_ID, a.ASSIGNMENT_ID, b.ELEMENT_TYPE_ID, a.TIME_PERIOD_ID, SUM(decode(a.dr_cr_flag,'C',(-1)*d.DISTRIBUTION_AMOUNT,'D',d.DISTRIBUTION_AMOUNT)) EARNINGS_AMOUNT, a.AWARD_ID, a.EXPENDITURE_TYPE, ppc.currency_code, a.project_id, a.task_id , a.expenditure_organization_id, PSP_PSPLDAWD_XMLP_PKG.cf_award_nameformula(a.AWARD_ID) CF_AWARD_NAME, PSP_PSPLDAWD_XMLP_PKG.cf_currency_formatformula(ppc.currency_code) CF_currency_format, PSP_PSPLDAWD_XMLP_PKG.cf_sum_currency_codeformula(ppc.currency_code) CF_sum_currency_code, PSP_PSPLDAWD_XMLP_PKG.cf_curr_awd_amt_totalformula(:CF_currency_format, :CS_curr_AWD_AMT_TOTAL) CF_curr_awd_amt_total_dsp, PSP_PSPLDAWD_XMLP_PKG.cf_employee_nameformula(a.PERSON_ID) CF_Employee_Name, PSP_PSPLDAWD_XMLP_PKG.cf_assignment_numberformula(a.ASSIGNMENT_ID, a.PERSON_ID) CF_Assignment_Number, PSP_PSPLDAWD_XMLP_PKG.cf_element_nameformula(b.ELEMENT_TYPE_ID) CF_Element_Name, PSP_PSPLDAWD_XMLP_PKG.cf_time_period_nameformula(a.TIME_PERIOD_ID) CF_Time_Period_Name, PSP_PSPLDAWD_XMLP_PKG.cf_charging_instructionsformul(a.expenditure_organization_id, a.task_id, a.project_id, a.AWARD_ID, a.EXPENDITURE_TYPE) CF_charging_instructions, PSP_PSPLDAWD_XMLP_PKG.cf_employee_numberformula() CF_Employee_Number, PSP_PSPLDAWD_XMLP_PKG.cf_earnings_amountformula(:CF_currency_format, SUM ( decode ( a.dr_cr_flag , 'C' , ( - 1 ) * d.DISTRIBUTION_AMOUNT , 'D' , d.DISTRIBUTION_AMOUNT ) )) CF_earnings_amount_dsp FROM psp_summary_lines a, PSP_PAYROLL_LINES b, PSP_PAYROLL_SUB_LINES c, PSP_DISTRIBUTION_LINES_HISTORY d, PSP_PAYROLL_CONTROLS ppc, per_assignments_f paf WHERE B.PAYROLL_LINE_ID = C.PAYROLL_LINE_ID AND C.PAYROLL_SUB_LINE_ID = d.payroll_sub_line_id &P_Award_ID and a.award_id is not null AND d.summary_line_id = a.summary_line_id AND ppc.payroll_control_id = b.payroll_control_id AND d.gl_project_flag = 'P' AND NOT EXISTS( SELECT 1 FROM psp_adjustment_lines_history pdlh WHERE pdlh.orig_line_id =d.distribution_line_id AND pdlh.original_line_flag ='Y' AND pdlh.orig_source_type = 'D' ) and d.business_group_id = :P_BUSINESS_GROUP_ID and d.set_of_books_id = :P_SET_OF_BOOKS_ID and d.distribution_date BETWEEN :p_start_date AND :p_end_date and paf.business_group_id =:P_BUSINESS_GROUP_ID and paf.assignment_id = b.assignment_id and b.effective_date between paf.effective_start_date and paf.effective_end_date group by a.AWARD_ID, ppc.currency_code, a.EXPENDITURE_TYPE, a.PERSON_ID, a.ASSIGNMENT_ID, b.ELEMENT_TYPE_ID, a.TIME_PERIOD_ID, a.project_id, a.task_id , a.expenditure_organization_id UNION ALL Select a.PERSON_ID, a.ASSIGNMENT_ID, a.ELEMENT_TYPE_ID, a.TIME_PERIOD_ID, SUM(decode(a.dr_cr_flag,'C',(-1)*a.DISTRIBUTION_AMOUNT,'D',a.DISTRIBUTION_AMOUNT )) EARNINGS_AMOUNT, a.AWARD_ID, a.EXPENDITURE_TYPE, ppc.CURRENCY_CODE, a.project_id, a.task_id , a.expenditure_organization_id, PSP_PSPLDAWD_XMLP_PKG.cf_award_nameformula(a.AWARD_ID) CF_AWARD_NAME, PSP_PSPLDAWD_XMLP_PKG.cf_currency_formatformula(ppc.currency_code) CF_currency_format, PSP_PSPLDAWD_XMLP_PKG.cf_sum_currency_codeformula(ppc.currency_code) CF_sum_currency_code, PSP_PSPLDAWD_XMLP_PKG.cf_curr_awd_amt_totalformula(:CF_currency_format, :CS_curr_AWD_AMT_TOTAL) CF_curr_awd_amt_total_dsp, PSP_PSPLDAWD_XMLP_PKG.cf_employee_nameformula(a.PERSON_ID) CF_Employee_Name, PSP_PSPLDAWD_XMLP_PKG.cf_assignment_numberformula(a.ASSIGNMENT_ID, a.PERSON_ID) CF_Assignment_Number, PSP_PSPLDAWD_XMLP_PKG.cf_element_nameformula(a.ELEMENT_TYPE_ID) CF_Element_Name, PSP_PSPLDAWD_XMLP_PKG.cf_time_period_nameformula(a.TIME_PERIOD_ID) CF_Time_Period_Name, PSP_PSPLDAWD_XMLP_PKG.cf_charging_instructionsformul(a.expenditure_organization_id, a.task_id, a.project_id, a.AWARD_ID, a.EXPENDITURE_TYPE) CF_charging_instructions, PSP_PSPLDAWD_XMLP_PKG.cf_employee_numberformula() CF_Employee_Number, PSP_PSPLDAWD_XMLP_PKG.cf_earnings_amountformula(:CF_currency_format, SUM ( decode ( a.dr_cr_flag , 'C' , ( - 1 ) * a.DISTRIBUTION_AMOUNT , 'D' , a.DISTRIBUTION_AMOUNT ) )) CF_earnings_amount_dsp from PSP_ADJUSTMENT_LINES_HISTORY a, PSP_PAYROLL_CONTROLS ppc where a.distribution_date BETWEEN :p_start_date AND :p_end_date &P_Award_ID and a.award_id is not null and a.business_group_id = :P_BUSINESS_GROUP_ID and a.set_of_books_id = :P_SET_OF_BOOKS_ID AND ppc.PAYROLL_CONTROL_ID = a.PAYROLL_CONTROL_ID AND NVL(a.original_line_flag,'N') = 'N' 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 a.AWARD_ID, ppc.CURRENCY_CODE, a.EXPENDITURE_TYPE, a.PERSON_ID, a.ASSIGNMENT_ID, a.ELEMENT_TYPE_ID, a.TIME_PERIOD_ID, a.project_id, a.task_id , a.expenditure_organization_id UNION ALL Select a.PERSON_ID, a.ASSIGNMENT_ID, a.ELEMENT_TYPE_ID, a.TIME_PERIOD_ID, SUM(DECODE(a.dr_cr_flag, 'C', -1 * a.DISTRIBUTION_AMOUNT, 'D', a.DISTRIBUTION_AMOUNT)) EARNINGS_AMOUNT, a.AWARD_ID, a.EXPENDITURE_TYPE, ppc.CURRENCY_CODE, a.project_id, a.task_id , a.expenditure_organization_id, PSP_PSPLDAWD_XMLP_PKG.cf_award_nameformula(a.AWARD_ID) CF_AWARD_NAME, PSP_PSPLDAWD_XMLP_PKG.cf_currency_formatformula(ppc.currency_code) CF_currency_format, PSP_PSPLDAWD_XMLP_PKG.cf_sum_currency_codeformula(ppc.currency_code) CF_sum_currency_code, PSP_PSPLDAWD_XMLP_PKG.cf_curr_awd_amt_totalformula(:CF_currency_format, :CS_curr_AWD_AMT_TOTAL) CF_curr_awd_amt_total_dsp, PSP_PSPLDAWD_XMLP_PKG.cf_employee_nameformula(a.PERSON_ID) CF_Employee_Name, PSP_PSPLDAWD_XMLP_PKG.cf_assignment_numberformula(a.ASSIGNMENT_ID, a.PERSON_ID) CF_Assignment_Number, PSP_PSPLDAWD_XMLP_PKG.cf_element_nameformula(a.ELEMENT_TYPE_ID) CF_Element_Name, PSP_PSPLDAWD_XMLP_PKG.cf_time_period_nameformula(a.TIME_PERIOD_ID) CF_Time_Period_Name, PSP_PSPLDAWD_XMLP_PKG.cf_charging_instructionsformul(a.expenditure_organization_id, a.task_id, a.project_id, a.AWARD_ID, a.EXPENDITURE_TYPE) CF_charging_instructions, PSP_PSPLDAWD_XMLP_PKG.cf_employee_numberformula() CF_Employee_Number, PSP_PSPLDAWD_XMLP_PKG.cf_earnings_amountformula(:CF_currency_format, SUM ( decode ( a.dr_cr_flag , 'C' , ( - 1 ) * a.DISTRIBUTION_AMOUNT , 'D' , a.DISTRIBUTION_AMOUNT ) )) CF_earnings_amount_dsp from PSP_PRE_GEN_DIST_LINES_HISTORY a, PSP_PAYROLL_CONTROLS ppc where a.distribution_date BETWEEN :p_start_date AND :p_end_date &P_Award_ID and a.award_id is not null and a.business_group_id = :P_BUSINESS_GROUP_ID and a.set_of_books_id = :P_SET_OF_BOOKS_ID AND ppc.PAYROLL_CONTROL_ID = a.PAYROLL_CONTROL_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 a.AWARD_ID, ppc.CURRENCY_CODE, a.EXPENDITURE_TYPE, a.PERSON_ID, a.ASSIGNMENT_ID, a.ELEMENT_TYPE_ID, a.TIME_PERIOD_ID, a.project_id, a.task_id , a.expenditure_organization_id --ORDER BY 12,8,16,17,18,19,20,9,10,7,11,3,4,5 ORDER BY 8 ASC, 6 Y PSP PSPLDAWD_XML PTB PSP Relatório do Histórico de Classificação Contábil por Prêmio Imported from BI Publisher Description: Histórico de Classificação Contábil de Mão de Obra por Prêmio Application: Distribuição da Mão de Obra Source: PSP: Relatório do Histórico de Classificação Contábil por Prêmio (XML) Short Name: PSPLDAWD_XML DB package: PSP_PSPLDAWD_XMLP_PKG US PSP Distribution History by Award Imported from BI Publisher Description: Labor Distribution History by Award Application: Labor Distribution Source: PSP: Distribution History by Award Report (XML) Short Name: PSPLDAWD_XML DB package: PSP_PSPLDAWD_XMLP_PKG BI Publisher &p_award_id :cf_currency_format :cs_curr_awd_amt_total :p_award_id :p_awd_template_id :p_business_group_id :p_conc_request_id :p_end_date :p_set_of_books_id :p_start_date 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_awd_param_type Char AWD_SET PTB PSP_REPORT_TYPE Tipo de Parâmetro do Prêmio US PSP_REPORT_TYPE Award Parameter Type 4 10 :p_awd_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 Prêmios Conjunto de Parâmetros do Prêmio US Award Set Award Parameter Set 5 20 :p_start_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 :$flex$.psp_report_type PSP_REPORT_TYPE Award Set