PSP Distribution History by Award
Description
Categories: BI Publisher
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
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
Run
PSP Distribution History by Award and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Award Set |
|
LOV Oracle | |
Begin Date |
|
Date | |
End Date |
|
Date |