PSP Distribution Adjustment Register
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Distribution Adjustment Register
Application: Labor Distribution
Source: PSP: Distribution Adjustment Register (XML)
Short Name: PSPDAREG_XML
DB package: PSP_PSPDAREG_XMLP_PKG
Description: Distribution Adjustment Register
Application: Labor Distribution
Source: PSP: Distribution Adjustment Register (XML)
Short Name: PSPDAREG_XML
DB package: PSP_PSPDAREG_XMLP_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
SELECT pact.adjustment_batch_name Batch, TO_CHAR(pact.distribution_start_date,'DD-MON-YYYY') ||' To '||TO_CHAR(pact.distribution_end_date,'DD-MON-YYYY') "Adjustment From", psp_general.get_person_name(fu1.employee_id, TRUNC(SYSDATE)) "Submitted By", psp_general.get_person_name(fu2.employee_id, TRUNC(SYSDATE)) "Approved By", TO_CHAR(pact.approval_date,'DD-MON-YYYY') "Approval Date", psp_general.get_person_name(pact.person_id, TRUNC(SYSDATE)) "Full Name", paf.assignment_number Assignment, pj.name Job, psp_general.get_payroll_name(ppc.payroll_id, TRUNC(SYSDATE)) "Payroll Name", psp_general.get_element_name(palh.element_type_id, TRUNC(SYSDATE)) "Earnings Element", peg.element_group_name, pact.comments Comments, DECODE(palh.original_line_flag,'Y',palh.gl_code_combination_id,'N',NULL) glccid_old, DECODE(palh.original_line_flag,'Y',palh.project_id, 'N',NULL) project_id_old, DECODE(palh.original_line_flag,'Y',palh.award_id, 'N',NULL) award_id_old, DECODE(palh.original_line_flag,'Y', palh.task_id,'N',NULL) task_id_old, DECODE(palh.original_line_flag,'Y', palh.expenditure_type,'N',NULL) expenditure_type_old, DECODE(palh.original_line_flag,'Y', palh.expenditure_organization_id,'N',NULL) exp_org_id_old, DECODE(palh.original_line_flag, 'Y',(SUM(DECODE(palh.dr_cr_flag,'D',palh.distribution_amount, -palh.distribution_amount))),'N',NULL) "Original Amount", DECODE(palh.original_line_flag,'N',palh.gl_code_combination_id,'Y',NULL) glccid_new, DECODE(palh.original_line_flag,'N',palh.project_id, 'Y',NULL) project_id_new, DECODE(palh.original_line_flag,'N',palh.award_id, 'Y',NULL) award_id_new, DECODE(palh.original_line_flag,'N', palh.task_id,'Y',NULL) task_id_new, DECODE(palh.original_line_flag,'N', palh.expenditure_type,'Y',NULL) expenditure_type_new, DECODE(palh.original_line_flag,'N', palh.expenditure_organization_id,'Y',NULL) exp_org_id_new, DECODE(palh.original_line_flag,'N', (SUM(DECODE(palh.dr_cr_flag,'D',palh.distribution_amount, -palh.distribution_amount))),'Y',NULL) "Adjusted Amount" , DECODE(pact.adjust_by, NULL, 'E', pact.adjust_by) "Adjustment By", pact.currency_code, PSP_PSPDAREG_XMLP_PKG.cf_adjustment_byformula(DECODE ( pact.adjust_by , NULL , 'E' , pact.adjust_by )) CF_adjustment_by, PSP_PSPDAREG_XMLP_PKG.cf_sum_currencyformula(pact.currency_code) CF_sum_currency, PSP_PSPDAREG_XMLP_PKG.cf_currency_formatformula(pact.currency_code) CF_currency_format, PSP_PSPDAREG_XMLP_PKG.cf_orig_amount_total_dspformul(:CF_currency_format, :CS_Orig_Amount_Total) CF_orig_amount_total_dsp, PSP_PSPDAREG_XMLP_PKG.cf_adjusted_amount_total_dspfo(:CF_currency_format, :CS_Adjusted_Amount_Total) CF_adjusted_amount_total_dsp, PSP_PSPDAREG_XMLP_PKG.cf_original_amount_dspformula(:CF_currency_format, DECODE ( palh.original_line_flag , 'Y' , ( SUM ( DECODE ( palh.dr_cr_flag , 'D' , palh.distribution_amount , - palh.distribution_amount ) ) ) , 'N' , NULL )) CF_original_amount_dsp, PSP_PSPDAREG_XMLP_PKG.cf_adjusted_amount_dspformula(:CF_currency_format, DECODE ( palh.original_line_flag , 'N' , ( SUM ( DECODE ( palh.dr_cr_flag , 'D' , palh.distribution_amount , - palh.distribution_amount ) ) ) , 'Y' , NULL )) CF_adjusted_amount_dsp, PSP_PSPDAREG_XMLP_PKG.cf_new_ciformula(DECODE ( palh.original_line_flag , 'N' , palh.gl_code_combination_id , 'Y' , NULL ), DECODE ( palh.original_line_flag , 'N' , palh.project_id , 'Y' , NULL ), DECODE ( palh.original_line_flag , 'N' , palh.task_id , 'Y' , NULL ), DECODE ( palh.original_line_flag , 'N' , palh.award_id , 'Y' , NULL ), DECODE ( palh.original_line_flag , 'N' , palh.expenditure_organization_id , 'Y' , NULL ), DECODE ( palh.original_line_flag , 'N' , palh.expenditure_type , 'Y' , NULL )) CF_New_CI, PSP_PSPDAREG_XMLP_PKG.cf_old_ciformula(DECODE ( palh.original_line_flag , 'Y' , palh.gl_code_combination_id , 'N' , NULL ), DECODE ( palh.original_line_flag , 'Y' , palh.project_id , 'N' , NULL ), DECODE ( palh.original_line_flag , 'Y' , palh.task_id , 'N' , NULL ), DECODE ( palh.original_line_flag , 'Y' , palh.award_id , 'N' , NULL ), DECODE ( palh.original_line_flag , 'Y' , palh.expenditure_organization_id , 'N' , NULL ), DECODE ( palh.original_line_flag , 'Y' , palh.expenditure_type , 'N' , NULL )) CF_Old_CI FROM psp_adjustment_lines_history palh, fnd_user fu1, fnd_user fu2, per_assignments_f paf, per_jobs pj, psp_payroll_controls ppc, psp_adjustment_control_table pact, psp_group_element_list pgel, psp_element_groups peg WHERE pact.assignment_id = paf.assignment_id AND paf.effective_start_date = (select max(pt.effective_start_date) from per_assignments_f pt where pt.assignment_id = paf.assignment_id and pt.assignment_type = 'E') AND pact.created_by = fu1.user_id AND pact.approver_id = fu2.user_id AND pj.job_id = paf.job_id AND ppc.payroll_control_id = palh.payroll_control_id AND pact.adjustment_batch_name = palh.batch_name AND pact.business_group_id = :p_business_group_id AND pact.set_of_books_id = :p_set_of_books_id AND peg.business_group_id = pact.business_group_id AND peg.set_of_books_id = pact.set_of_books_id AND paf.organization_id = NVL(:p_assignment_organization_id, paf.organization_id) AND pact.approver_id = NVL(:p_approved_by, pact.approver_id) AND pact.created_by = NVL(:p_submitted_by, pact.created_by) AND pact.adjustment_batch_name = NVL(:p_batch_name, pact.adjustment_batch_name) AND trunc(pact.creation_date) BETWEEN trunc(:p_begin_date) AND TRUNC(:p_end_date)+0.99999 AND pgel.element_type_id (+) = palh.element_type_id AND peg.element_group_id = nvl(pgel.element_group_id,peg.element_group_id) AND (:P_PRIMARY_ORGANIZATION_ID IS NULL OR (:P_PRIMARY_ORGANIZATION_ID IS NOT NULL AND EXISTS (SELECT 1 FROM per_all_assignments_f paf2 WHERE paf2.organization_id = :P_PRIMARY_ORGANIZATION_ID AND paf2.person_id = pact.person_id AND paf2.primary_flag = 'Y'))) GROUP BY pact.adjustment_batch_name, peg.element_group_name, psp_general.get_element_name(palh.element_type_id, TRUNC(SYSDATE)), palh.original_line_flag, palh.gl_code_combination_id, palh.project_id, palh.task_id, palh.award_id, palh.expenditure_type, palh.expenditure_organization_id, palh.original_line_flag, pact.distribution_start_date, pact.distribution_end_date, pact.comments, psp_general.get_person_name(fu1.employee_id, TRUNC(SYSDATE)), psp_general.get_person_name(fu2.employee_id, TRUNC(SYSDATE)), pact.approval_date, psp_general.get_person_name(pact.person_id, TRUNC(SYSDATE)), paf.assignment_number, pj.name, psp_general.get_payroll_name(ppc.payroll_id, TRUNC(SYSDATE)), palh.dr_cr_flag, pact.adjust_by, pact.currency_code /*ORDER BY pact.adjustment_batch_name asc, peg.element_group_name asc, 10 asc, palh.original_line_flag desc*/ ORDER BY 1 ASC,27 ASC,28 ASC,11 ASC,10 ASC , pact.adjustment_batch_name asc , peg.element_group_name asc , 10 asc , palh.original_line_flag desc |
Parameter Name | SQL text | Validation | |
---|---|---|---|
PSP_DATE |
|
Date | |
PSP_DATE2 |
|
Date | |
Adjustment Batch Name |
|
LOV Oracle | |
Submitted By |
|
LOV Oracle | |
Approved By |
|
LOV Oracle | |
Assignment Org. Name |
|
LOV Oracle | |
Primary Org. Name |
|
LOV Oracle |