10 Replies Latest reply on Aug 2, 2012 6:05 AM by Saro

    Edit Privileges

    Saro
      Hi friends,

      Currently im as a Administrator user couldnt edit any changes in the informatica in designer, repository, wf monitor and wf manager.

      suppose, if i need to edit a mapping means, it shows in a greyed out mode and due to that i couldnt edit it. How to disable this greyed out mode for the user Administrator.

      Thanks

      Regards,
      Saro
        • 1. Re: Edit Privileges
          Srini VEERAVALLI
          Log on to Designer-> Open repository folder->Open mapping->
          Check out the mapping for changes
          you have go for version check out and check in for Mapping, session and Workflow.

          If helps Pls mark correct or helpful
          • 2. Re: Edit Privileges
            Saro
            Thanks Svee,

            For pointing me towards edit. Why i need to edit really is

            For my one of the failed task in DAC which is named "SDE_ORA_PersisterStage_WorkforceEvent_Salary" while checking under the sesslogs for that task, i found the below oracle error like

            ORA-00907: missing right parenthesis, which implies like missing parenthesis

            So for this error what i did is went to the designer and under the mappings of that task, i double clicked the source qualifier and in under the properties tab i seen the sql query of the task and that is below
            SELECT 
             assignment_id
            ,person_id
            ,pay_proposal_id
            ,proposal_reason_code 
            ,CASE WHEN proposal_reason_code IS NOT NULL
                          THEN 'PROPOSAL_REASON' 
             END    proposal_reason_type
            ,effective_start_date
            ,effective_end_date
            ,business_group_id
            ,salary
            ,annualization_factor
            ,salary_change_ind
            ,currency_code
            , NVL(last_sal_incr_dt, LAST_VALUE(last_sal_incr_dt IGNORE NULLS) OVER (PARTITION BY assignment_id order by assignment_id, person_id, effective_start_date) ) last_sal_incr_dt 
            FROM 
            (
            select
             tab.assignment_id
            ,tab.person_id
            ,tab.pay_proposal_id
            ,tab.proposal_reason_code 
            ,'PROPOSAL_REASON' proposal_reason_type
            ,tab.effective_start_date
            ,tab.effective_end_date
            ,tab.business_group_id
            ,tab.salary
            ,NVL(tab.pay_annualization_factor, NVL(tpt.number_per_fiscal_year, 1))
                                       annualization_factor
            ,tab.salary_change_ind
            ,tab.currency_code
            ,tab.data_last_update_date
            ,CASE WHEN tab.salary_change_ind = 1
                  THEN tab.effective_start_date
             END last_sal_incr_dt 
            from
            (select /*+ USE_HASH(asg pro pet piv ppb) */
               pro.assignment_id
              ,asg.person_id
              ,pro.pay_proposal_id
              ,pro.proposal_reason     proposal_reason_code
              ,pro.change_date         effective_start_date
              ,NVL(LEAD(pro.change_date, 1) OVER
                     (PARTITION BY pro.assignment_id ORDER BY pro.change_date) - 1
                  ,to_date('31-DEC-4712','DD-MON-YYYY'))     effective_end_date
              ,asg.business_group_id
              ,pro.proposed_salary_n                         salary
              ,ppb.pay_annualization_factor
              ,CASE WHEN pro.proposed_salary_n = LAG(pro.proposed_salary_n, 1) OVER
                     (PARTITION BY pro.assignment_id ORDER BY pro.change_date) 
                    THEN 0
                    ELSE 1
               END                  salary_change_ind
              ,pet.input_currency_code     currency_code
              ,pro.last_update_date       data_last_update_date
              ,asg.payroll_id
              from
               per_pay_proposals       pro
              ,per_all_assignments_f   asg
              ,per_pay_bases           ppb
              ,pay_input_values_f      piv
              ,pay_element_types_f     pet
              where pro.approved = 'Y'
              and asg.assignment_id = pro.assignment_id
              and pro.change_date between asg.effective_start_date and asg.effective_end_date
              and asg.pay_basis_id = ppb.pay_basis_id
              and ppb.input_value_id = piv.input_value_id
              and pro.change_date between piv.effective_start_date and piv.effective_end_date
              and piv.element_type_id = pet.element_type_id
              and pro.change_date between pet.effective_start_date and pet.effective_end_date
             )    tab
            ,per_time_period_types   tpt
            ,pay_all_payrolls_f      prl
            where tab.effective_end_date >= TO_DATE('$$INITIAL_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS')  
            and tab.effective_start_date between prl.effective_start_date (+) and prl.effective_end_date (+)
            and tab.payroll_id = prl.payroll_id (+)
            and prl.period_type=tpt.period_type (+)
            )
            ORDER BY  assignment_id, person_id, effective_start_date
            When i tried to execute the above query in toad, over there also it shows that missing right parenthesis. From the query i identified that the error in the below line in select statement
            NVL(last_sal_incr_dt, LAST_VALUE(last_sal_incr_dt IGNORE NULLS) OVER (PARTITION BY assignment_id order by assignment_id, person_id, effective_start_date) ) last_sal_incr_dt
            since due to the IGNORE NULLS only it is returning an error like missing parenthesis.

            So, if i removed and run the query in the toad means then it is fetching values.

            So, how i can rectify this error, whether can i edit it in the sql expression in designer directly and after that i can restart the ETL in DAC or else i need to follow other steps. Can you guide me towards the right direction svee for correcting the above issue.

            Thanks for your help

            Regards,
            Svee
            • 3. Re: Edit Privileges
              Srini VEERAVALLI
              I'm not sure what is missing if you takeout that.
              I would suggest make a notes, for time being go ahead with changes. Later investigate on this.
              • 4. Re: Edit Privileges
                Saro
                So, Svee

                can i correct sql expression in designer for missing right parenthesis error for the failed mapping and restart the ETL run in DAC??

                Regards,
                Saro
                • 5. Re: Edit Privileges
                  Srini VEERAVALLI
                  Yes. Make sure you got backup of the mapping.
                  • 6. Re: Edit Privileges
                    Saro
                    Hi Svee,

                    Backup in the sense u meant to export the objects of the mapping as a xml file. Okay once i exported it a xml file then how i can restore it.

                    Regards,
                    Saro
                    • 7. Re: Edit Privileges
                      Srini VEERAVALLI
                      Once you have xml, File Import the mapping.
                      • 8. Re: Edit Privileges
                        Saro
                        Hi svee,

                        U mean under designer in mappings we have an option of Import Mapping template over there we need to import this xml file inorder to restore it.

                        Regards,
                        Saro
                        • 9. Re: Edit Privileges
                          Srini VEERAVALLI
                          I was away from desk :)

                          Log on to Repository Manager->Open repository->double Click on folder->Expand Workflows->Select workflow
                          Menu Repository->Export Obejcts

                          Import Objects is for Importing.

                          The same for Other objects for Mapping,Session etc.

                          If you export Workflow that will take care of objects. I prefer Repository Manager to these activities.

                          If helps pls mark correct or helpful :)
                          1 person found this helpful
                          • 10. Re: Edit Privileges
                            Saro
                            Thanks for the info, svee

                            Regards,
                            Saro