ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D471F751A9E05362FB09050512 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 Y PSP PSPDAREG_XML PTB PSP Registro de Ajuste de Classificação Contábil Imported from BI Publisher Description: Registro de Ajuste de Classificação Contábil Application: Distribuição da Mão de Obra Source: PSP: Registro de Ajuste de Classificação Contábil (XML) Short Name: PSPDAREG_XML DB package: PSP_PSPDAREG_XMLP_PKG US PSP Distribution Adjustment Register 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 BI Publisher :cf_currency_format :cs_adjusted_amount_total :cs_orig_amount_total :p_approved_by :p_assignment_organization_id :p_batch_name :p_begin_date :p_business_group_id :p_conc_request_id :p_end_date :p_primary_org_where_clause :p_primary_organization :p_primary_organization_id :p_set_of_books_id :p_submitted_by 1 -30 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y PTB Id do Grupo Funcional Id do Grupo Funcional US Business Group Id Business Group Id 2 -20 :p_set_of_books_id Number fnd_profile.value('GL_SET_OF_BKS_ID') Y PTB P_SET_OF_BOOKS_ID Id do Razão US P_SET_OF_BOOKS_ID Ledger Id 3 10 :p_begin_date Date Y PTB PSP_DATE Data Inicial da faixa de Ajuste de Classificação Contábil. US PSP_DATE Begin Date for the Distribution Adjustment range. 4 20 :p_end_date Date Y PTB PSP_DATE2 Data Final da faixa de Ajuste de Classificação Contábil. US PSP_DATE2 End Date for the Distribution Adjustment range. 5 30 :p_batch_name LOV Oracle PSP_DA_REG_BATCH_NAME Y select pact.adjustment_batch_name id, pact.adjustment_batch_name value, null description from psp_adjustment_control_table pact where (pact.creation_date between fnd_date.canonical_to_date(:$flex$.psp_date) and fnd_date.canonical_to_date(:$flex$.psp_date2)+0.99999) and pact.adjustment_batch_name in (select batch_name from psp_payroll_controls where source_type='A' and status_code='P') order by value,description PTB Nome do Lote de Ajuste Nome de um lote de ajuste de classificação contábil em uma determinada faixa de datas. US Adjustment Batch Name Name of a distribution adjustment batch within the given date range. 6 40 :p_submitted_by LOV Oracle PSP_DA_REG_SUBMITTED_BY Y select fu.user_id id, ppx.full_name value, null description from fnd_user fu,per_people_x ppx where ppx.person_id(+)=fu.employee_id and fu.user_id in (select created_by from psp_adjustment_control_table pact where (pact.creation_date between fnd_date.canonical_to_date(:$flex$.psp_date) and fnd_date.canonical_to_date(:$flex$.psp_date2)+0.99999) and ( (:$flex$.psp_da_reg_batch_name:null is not null and pact.adjustment_batch_name= :$flex$.psp_da_reg_batch_name:null) or (:$flex$.psp_da_reg_batch_name:null is null and pact.adjustment_batch_name in (select batch_name from psp_payroll_controls where source_type='A' and status_code='P')))) order by value,description PTB Submetido por Id da pessoa que submeteu os ajustes na faixa de datas fornecida US Submitted By Id of the person who had submitted the adjustments within the given date range 7 50 :p_approved_by LOV Oracle PSP_DA_REG_APPROVED_BY Y select fu.user_id id, ppx.full_name value, null description from fnd_user fu,per_people_x ppx where ppx.person_id(+)=fu.employee_id and fu.user_id in (select pact.approver_id from psp_adjustment_control_table pact where (pact.creation_date between fnd_date.canonical_to_date(:$flex$.psp_date) and fnd_date.canonical_to_date(:$flex$.psp_date2)+0.99999) and ((:$flex$.psp_da_reg_batch_name:null is not null and pact.adjustment_batch_name= :$flex$.psp_da_reg_batch_name:null) or (:$flex$.psp_da_reg_batch_name:null is null and pact.adjustment_batch_name in (select batch_name from psp_payroll_controls where source_type='A' and status_code='P'))) and pact.created_by= nvl(:$flex$.psp_da_reg_submitted_by:null,pact.created_by)) order by value,description PTB Aprovado por Id da pessoa que aprovou os ajustes na faixa de datas fornecida US Approved By Id of the person who had approved the adjustments within the given date range 8 60 :p_assignment_organization_id LOV Oracle PSP_DA_REG_ASSIGN_ORG_NAME Y select hrou.organization_id id, hrou.name value, null description from hr_organization_units hrou where hrou.organization_id in (select paf.organization_id from per_assignments_f paf where paf.assignment_id in (select pact.assignment_id from psp_adjustment_control_table pact where (pact.creation_date between fnd_date.canonical_to_date(:$flex$.psp_date) and fnd_date.canonical_to_date(:$flex$.psp_date2)+0.99999) and ((:$flex$.psp_da_reg_batch_name:null is not null and pact.adjustment_batch_name= :$flex$.psp_da_reg_batch_name:null) or (:$flex$.psp_da_reg_batch_name:null is null and pact.adjustment_batch_name in (select batch_name from psp_payroll_controls where source_type='A' and status_code='P'))) and pact.created_by= nvl(:$flex$.psp_da_reg_submitted_by:null, pact.created_by) and pact.approver_id= nvl(:$flex$.psp_da_reg_approved_by:null, pact.approver_id))) order by value,description PTB Nome da Org. da Atribuição Id da Org. da Atribuição da pessoa que está sendo ajustada na faixa de datas fornecida US Assignment Org. Name Assignment Organization Id of the person being adjusted within the given date range 9 70 :p_primary_organization_id LOV Oracle PSP_DA_REG_PRIMARY_ASSIGN_ORG Y select hrou.organization_id id, hrou.name value, null description from hr_organization_units hrou where hrou.organization_id in (select paf.organization_id from per_assignments_f paf where paf.primary_flag='Y' and paf.person_id in (select pact.person_id from psp_adjustment_control_table pact where (pact.creation_date between fnd_date.canonical_to_date(:$flex$.psp_date) and fnd_date.canonical_to_date(:$flex$.psp_date2)+0.99999) and ((:$flex$.psp_da_reg_batch_name:null is not null and pact.adjustment_batch_name= :$flex$.psp_da_reg_batch_name:null) or (:$flex$.psp_da_reg_batch_name:null is null and pact.adjustment_batch_name in (select batch_name from psp_payroll_controls where source_type='A' and status_code='P'))) and pact.created_by= nvl(:$flex$.psp_da_reg_submitted_by:null, pact.created_by) and pact.approver_id= nvl(:$flex$.psp_da_reg_approved_by:null, pact.approver_id))) and :$flex$.psp_da_reg_assign_org_name:null is null order by value,description PTB Nome da Org. Principal Id da Organização da Atribuição Principal da pessoa que está sendo ajustada US Primary Org. Name Primary Assignment Organization Id of person being adjusted :$flex$.psp_date PSP_DATE Y Adjustment Batch Name :$flex$.psp_date PSP_DATE Y Approved By :$flex$.psp_date PSP_DATE Y Assignment Org. Name :$flex$.psp_date PSP_DATE Y Primary Org. Name :$flex$.psp_date PSP_DATE Y Submitted By :$flex$.psp_date2 PSP_DATE2 Y Adjustment Batch Name :$flex$.psp_date2 PSP_DATE2 Y Approved By :$flex$.psp_date2 PSP_DATE2 Y Assignment Org. Name :$flex$.psp_date2 PSP_DATE2 Y Primary Org. Name :$flex$.psp_date2 PSP_DATE2 Y Submitted By