Search This Blog

Thursday, February 8, 2018

Steps to create TCC Batch file


Steps to create TCC Batch file
 
1.       Open any configuration file in TCC
2.       Go to General tab à Enable Schedule Monitoring à Click on “Click Here to Create Schedule” à Build Command Line à Copy the path e.g. "D:\Taleo Connect Client\TaleoConnectClient.bat" "D:\Taleo_Interfaces\Dev\Outbound\TCC_Scripts\DefaultHiringManagerRecruiter_cfg.xml"
3.       Open Notepad
4.       Type call and path from step 2. It will be like call "D:\Taleo Connect Client\TaleoConnectClient.bat" "D:\Taleo_Interfaces\Dev\Outbound\TCC_Scripts\DefaultHiringManagerRecruiter_cfg.xml"
5.       Save the notepad file as “DefaultHiringManagerRecruiter.bat”
6.       Go to Task Scheduler of Server where TCC is installed.
7.       Click on Create Basic Task
8.       Define the schedule and under Program/script browse file created in step 5.
9.       If needed Task can be updated.

Tuesday, February 6, 2018

WF Comments in Fusion BIP Reports

SELECT txnh.module_identifier ProcessName,
  wft.creator Requestor,
  wft.assignees CurrentAssignee,
  wft.assigneddate AssignedDate,
  wft.title NotificationTitle,
  txnd.status TxnStatus,
  txnh.object ObjectName,  wfc.wfcomment
FROM fusion.per_all_people_f dp,
  fusion.per_person_names_f_v n,
  fusion.per_all_assignments_m asg,
  fusion.hrc_txn_header txnh,
  fusion.hrc_txn_data txnd,
  hcm_fusion_soainfra.WFTASK wft,
  HCM_FUSION_SOAINFRA.wfcomments wfc

WHERE dp.person_id             =n.person_id

AND asg.person_id              =n.person_id

AND LENGTH(asg.assignment_type)=1

AND asg.assignment_id          =txnh.object_id

AND wft.identificationkey      =TO_CHAR(txnh.transaction_id)
and wfc.taskid = wft.taskid

AND txnh.object                ='PER_ALL_ASSIGNMENTS_M'

AND txnh.transaction_id        =txnd.transaction_id

AND sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date

AND asg.effective_latest_change='Y'

AND sysdate BETWEEN dp.effective_start_date AND dp.effective_end_date

AND sysdate BETWEEN n.effective_start_date AND n.effective_end_date

Saturday, November 25, 2017

Supervisor Query

SELECT    papf.person_number
         ,TO_CHAR (pps.date_start
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             person_hire_date
         ,TO_CHAR (pps.actual_termination_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             person_termination_date
         ,TO_CHAR (paaf.effective_start_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             assignment_start_date
         ,TO_CHAR (paaf.effective_end_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             assignment_end_date
         ,TO_CHAR (pasf.effective_start_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_start_date
         ,TO_CHAR (pasf.effective_end_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_end_date
         ,pasf.manager_type
         ,pasf.primary_flag
         ,paaf.assignment_number
         ,papfs.person_number manager_number
         ,TO_CHAR (ppss.date_start
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_hire_date
         ,TO_CHAR (ppss.actual_termination_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_termination_date
FROM      per_periods_of_service ppss
         ,per_all_people_f papfs
         ,per_assignment_supervisors_f pasf
         ,per_all_people_f papf
         ,per_all_assignments_m paaf
         ,per_periods_of_service pps
WHERE         ppss.person_id(+) = papfs.person_id
          AND pasf.effective_start_date BETWEEN papfs.effective_start_date(+) AND papfs.effective_end_date(+)
          AND papfs.person_id(+) = pasf.manager_id
          AND paaf.effective_start_date BETWEEN pasf.effective_start_date(+) AND pasf.effective_end_date(+)
          AND pasf.assignment_id(+) = paaf.assignment_id
          AND papf.person_number = NVL (:p_person_number, papf.person_number)
          AND pps.date_start BETWEEN papf.effective_start_date AND papf.effective_end_date
          AND papf.person_id = paaf.person_id
          AND paaf.effective_latest_change = 'Y'
          AND paaf.primary_assignment_flag = 'Y'
          AND paaf.primary_flag = 'Y'
          AND paaf.assignment_type = 'E'
          AND paaf.period_of_service_id = pps.period_of_service_id
ORDER BY  papf.person_number
         ,paaf.effective_start_date

Element Links

SELECT    pldgv.name legislative_data_group
         ,pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,TO_CHAR (pelf.effective_start_date
                  ,'YYYY/MM/DD')
             effective_start_date
         ,TO_CHAR (pelf.effective_end_date
                  ,'YYYY/MM/DD')
             effective_end_date
         ,haou.name legal_entity
         ,ppg.segment1 people_group_segment1
         ,ppg.segment2 people_group_segment2
         ,ppg.segment3 people_group_segment3
         ,pcac.source_sub_type
         ,pcac.segment1 cost_segment1
         ,pcac.segment2 cost_segment2
         ,pcac.segment3 cost_segment3
         ,pcac.segment4 cost_segment4
         ,pcac.segment5 cost_segment5
         ,pcac.segment6 cost_segment6
         ,pcac.segment7 cost_segment7
         ,pcac.segment8 cost_segment8
         ,pcif.costable_type costing_type
         ,pcif.transfer_to_gl_flag
FROM      pay_cost_alloc_accounts pcac
         ,pay_cost_allocations_f pcaf
         ,pay_cost_info_f pcif
         ,per_people_groups ppg
         ,hr_all_organization_units haou
         ,pay_element_criteria pec
         ,pay_element_links_f pelf
         ,pay_ele_classifications_tl pect
         ,per_legislative_data_groups_vl pldgv
         ,pay_element_types_f petf
         ,pay_element_types_tl pett
WHERE         pcac.cost_allocation_record_id(+) = pcaf.cost_allocation_record_id
          AND pelf.effective_start_date BETWEEN pcaf.effective_start_date(+) AND pcaf.effective_end_date(+)
          AND pcaf.source_type(+) = 'EL'
          AND pcaf.source_id(+) = pelf.element_link_id
          AND pelf.effective_start_date BETWEEN pcif.effective_start_date(+) AND pcif.effective_end_date(+)
          AND pcif.source_type(+) = 'EL'
          AND pcif.source_id(+) = pelf.element_link_id
          AND ppg.people_group_id(+) = pec.people_group_id
          AND haou.organization_id(+) = pec.legal_employer_id
          AND pec.element_criteria_id(+) = pelf.element_criteria_id
          AND pelf.element_type_id(+) = pett.element_type_id
          AND pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pldgv.legislative_data_group_id = petf.legislative_data_group_id
          AND petf.effective_start_date = (SELECT  MAX (petfi.effective_start_date)
                                           FROM    pay_element_types_f petfi
                                           WHERE   petfi.element_type_id = petf.element_type_id)
          AND petf.element_type_id = pett.element_type_id
          AND pett.language = 'US'
ORDER BY  pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,pelf.effective_start_date




SELECT    pect.classification_name link_classification_name
         ,pett.element_name link_element_name
         ,pelf.element_link_name link_element_link_name
         ,TO_CHAR (plivf.effective_start_date
                  ,'YYYY/MM/DD')
             link_effective_start_date
         ,TO_CHAR (plivf.effective_end_date
                  ,'YYYY/MM/DD')
             link_effective_end_date
         ,pivt.name link_input_name
         ,pcif.costable_type link_costable_type
         ,pcif.costed_flag link_costed_flag
         ,pcif.transfer_to_gl_flag link_transfer_to_gl_flag
FROM      pay_ele_classifications_tl pect
         ,pay_element_types_f petf
         ,pay_element_types_tl pett
         ,pay_element_links_f pelf
         ,pay_input_values_tl pivt
         ,pay_link_input_values_f plivf
         ,pay_cost_info_f pcif
WHERE         pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pcif.effective_start_date BETWEEN petf.effective_start_date AND petf.effective_end_date
          AND petf.element_type_id = pett.element_type_id
          AND pett.language = 'US'
          AND pett.element_type_id = pelf.element_type_id
          AND pcif.effective_start_date BETWEEN pelf.effective_start_date AND pelf.effective_end_date
          AND pelf.element_link_id = plivf.element_link_id
          AND pivt.language = 'US'
          AND pivt.input_value_id = plivf.input_value_id
          AND pcif.effective_start_date BETWEEN plivf.effective_start_date AND plivf.effective_end_date
          AND plivf.link_input_value_id = pcif.source_id
          AND pcif.source_type = 'LIV'
ORDER BY  pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,pelf.effective_start_date



SELECT    pldg.name ele_leg_data_group_name
         ,pect.classification_name ele_classification_name
         ,pett.element_name ele_element_name
         ,pivf.display_sequence ele_display_sequence
         ,pivt.name ele_input_name
         ,pivf.user_enterable_flag ele_user_enterable_flag
         ,pivf.mandatory_flag ele_mandatory_flag
         ,pivf.hot_default_flag ele_hot_default_flag
         ,hr_general.decode_lookup ('UOM'
                                   ,pivf.uom)
             ele_uom
         ,pivf.user_display_flag ele_user_display_flag
         ,pivf.DEFAULT_VALUE ele_default_value
         ,pivf.lookup_type ele_lookup_type
         ,pivf.min_value ele_min_value
         ,pivf.max_value ele_max_value
         ,pivf.value_set_code ele_value_set_code
FROM      pay_input_values_tl pivt
         ,pay_input_values_f pivf
         ,per_legislative_data_groups_vl pldg
         ,pay_ele_classifications_tl pect
         ,pay_element_types_tl pett
         ,pay_element_types_f petf
WHERE         pivt.language = 'US'
          AND pivt.input_value_id = pivf.input_value_id
          AND petf.effective_start_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
          AND pldg.legislative_data_group_id = petf.legislative_data_group_id
          AND pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pett.language = 'US'
          AND pett.element_type_id = petf.element_type_id
and 1=2
ORDER BY  pect.classification_name
         ,pett.element_name
         ,petf.effective_start_date
         ,pivf.display_sequence

Bank Details Query

SELECT   papf.person_number
,       ppnf.full_name
,           pp.attribute1 initials
,           bank_dtls.bank_account_name
,           bank_dtls.bank_name
,           bank_dtls.bank_branch_name
,          bank_dtls.bank_account_num
,           bank_dtls.iban_number
,           bank_dtls.currency_code
,           bank_dtls.base_org_pay_method_name
FROM    per_all_people_f papf
,    per_person_names_f ppnf
,    per_all_assignments_f paaf
,    pay_rel_groups_dn payrel
,           per_persons pp
,    (SELECT pba.bank_account_name
       ,       pba.bank_name
       ,       pba.bank_branch_name
       ,      pba.bank_account_num
       ,       pba.iban_number
       ,       popf.currency_code
       ,       popf.base_org_pay_method_name
     ,       pppmf.payroll_relationship_id
     FROM  pay_bank_accounts pba
     ,   pay_person_pay_methods_f pppmf
     ,   pay_org_pay_methods_f popf
     WHERE pppmf.bank_account_id = pba.bank_account_id
     AND     pppmf.org_payment_method_id = popf.org_payment_method_id
     AND TRUNC(SYSDATE) BETWEEN pppmf.effective_start_date AND pppmf.effective_end_date
     AND TRUNC(SYSDATE) BETWEEN popf.effective_start_date AND popf.effective_end_date
     AND TRUNC(SYSDATE) BETWEEN pba.start_date AND pba.end_date) bank_dtls
WHERE papf.person_id = ppnf.person_id
AND ppnf.name_type ='GLOBAL'
AND payrel.payroll_relationship_id = bank_dtls.payroll_relationship_id(+)
AND payrel.assignment_id = paaf.assignment_id
AND papf.person_id = paaf.person_id
AND   paaf.assignment_status_type = 'ACTIVE'
--AND papf.person_number = '44'
-- AND paaf.primary_flag = 'Y'
AND   papf.person_id = pp.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
--AN D TRUNC(SYSDATE) BETWEen epbad.account_start_date and epbad.account_end_date
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN payrel.start_date AND payrel.end_date

Assigned Payrolls

SELECT    papf.person_number
         ,paam.assignment_number
         ,TO_CHAR (pps.date_start
                  ,'YYYY/MM/DD')
             hire_date
         ,TO_CHAR (pps.actual_termination_date
                  ,'YYYY/MM/DD')
             actual_termination_date
         ,papfp.payroll_name
         ,TO_CHAR (papd.start_date
                  ,'YYYY/MM/DD')
             start_date
         ,TO_CHAR (papd.lsed
                  ,'YYYY/MM/DD')
             close_date
         ,TO_CHAR (papd.lspd
                  ,'YYYY/MM/DD')
             last_standard_process_date
         ,TO_CHAR (papd.fsed
                  ,'YYYY/MM/DD')
             final_process_date
FROM      pay_all_payrolls_f papfp
         ,pay_assigned_payrolls_dn papd
         ,pay_payroll_assignments ppay
         ,per_all_assignments_m paam
         ,per_all_people_f papf
         ,per_periods_of_service pps
WHERE         1 = 1
          AND papd.start_date BETWEEN papfp.effective_start_date(+) AND papfp.effective_end_date(+)
          AND papfp.payroll_id(+) = papd.payroll_id
          AND papd.payroll_term_id(+) = ppay.payroll_term_id
          AND ppay.hr_assignment_id(+) = paam.assignment_id
          AND LEAST (NVL (pps.actual_termination_date
                         ,TO_DATE ('31-12-4712'
                                  ,'DD-MM-YYYY'))
                    ,TO_DATE ('31-12-4712'
                             ,'DD-MM-YYYY')) BETWEEN paam.effective_start_date
                                                 AND paam.effective_end_date
          AND paam.effective_latest_change = 'Y'
          AND paam.assignment_type = 'E'
          AND paam.primary_assignment_flag = 'Y'
          AND paam.primary_flag = 'Y'
          AND paam.period_of_service_id = pps.period_of_service_id
          AND pps.date_start BETWEEN papf.effective_start_date AND papf.effective_end_date
          AND papf.person_id = pps.person_id
ORDER BY  papf.person_number
         ,papd.start_date

Area of Responsibility AOR Query

SELECT (SELECT DISTINCT PERSON_NUMBER FROM PER_ALL_PEOPLE_F PER WHERE PER.PERSON_ID = AOR.PERSON_ID) PERSON_NUMBER,
    (SELECT DISTINCT FULL_NAME FROM PER_PERSON_NAMES_F PER WHERE PER.PERSON_ID = AOR.PERSON_ID AND NAME_TYPE = 'GLOBAL') PERSON_NAME,
    AOR.RESPONSIBILITY_NAME,
    TO_CHAR(AOR.START_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') START_DATE,
    TO_CHAR(AOR.END_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') END_DATE,
    AOR.RESPONSIBILITY_TYPE,
    AOR.STATUS,
    (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID = AOR.BUSINESS_UNIT_ID) BUSINESS_UNIT,
    (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID = AOR.LEGAL_ENTITY_ID) LEGAL_ENTITY,
    (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID = AOR.ORGANIZATION_ID) DEPARTMENT,
    (SELECT LOCATION_NAME FROM HR_LOCATIONS HL WHERE HL.LOCATION_ID = AOR.LOCATION_ID) LOCATION,
    (SELECT DISTINCT NAME FROM HR_ALL_POSITIONS_F_VL PP WHERE PP.POSITION_ID = AOR.POSITION_ID) POSITION,
    (SELECT DISTINCT NAME FROM PER_JOBS_F_TL PJ WHERE PJ.JOB_ID = AOR.JOB_ID) JOB,
    (SELECT DISTINCT NAME FROM PER_GRADES_F_TL  PG WHERE PG.GRADE_ID = AOR.GRADE_ID) GRADE,
    AOR.ASSIGNMENT_CATEGORY,
    (SELECT PAY.PAYROLL_NAME
            FROM PAY_ALL_PAYROLLS_F    PAY
        WHERE PAY.PAYROLL_ID = AOR.PAYROLL_ID
             AND TRUNC(SYSDATE) BETWEEN PAY.EFFECTIVE_START_DATE  AND PAY.EFFECTIVE_END_DATE ) PAYROLL,
   (SELECT NAME
     FROM PER_LEGISLATIVE_DATA_GROUPS_VL PLD
      WHERE PLD.LEGISLATIVE_DATA_GROUP_ID = AOR.LEGISLATIVE_DATA_GROUP_ID) LEGISLATIVE_DATA_GROUP
FROM PER_ASG_RESPONSIBILITIES AOR