Query for Supplier Payment Comparison of that Period(Vendor Spend Query)

 SELECT POS.SEGMENT1

       , HP.PARTY_NAME

  ,cur_period.PAYMENT_AMOUNT  cur_pay_amt

  ,prev_period.PAYMENT_AMOUNT  prev_pay_amt

  ,ROUND((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100),1)||'%' increase_decrease

FROM

        (SELECT    AIA.VENDOR_ID              VENDOR_ID

          ,SUM(NVL(aipa.AMOUNT,0))    PAYMENT_AMOUNT

        FROM  AP_INVOICES_ALL AIA,

         ap_invoice_payments_all       aipa,

         gl_periods                    gl

        WHERE 1=1

        AND   AIA.INVOICE_ID                = AIPA.INVOICE_ID

        AND   aipa.period_name              = gl.period_name

        AND gl.period_set_name ='XX Period Name' 

AND gl.PERIOD_YEAR = :P_Fiscal_Year

AND gl.PERIOD_NAME = :P_Period

--AND   SYSDATE BETWEEN gl.start_date and gl.end_date

GROUP BY AIA.VENDOR_ID

        ) cur_period,

        (SELECT     AIA.VENDOR_ID     VENDOR_ID  

          ,SUM(NVL(aipa.AMOUNT,0))    PAYMENT_AMOUNT

        FROM  AP_INVOICES_ALL AIA,

         ap_invoice_payments_all       aipa,

         gl_periods                    gl

        WHERE 1=1

        AND   AIA.INVOICE_ID                = AIPA.INVOICE_ID

        AND   aipa.period_name              = gl.period_name

        AND gl.period_set_name ='XX Period Name'

AND gl.period_name = (

                      SELECT  gl.period_name

                              FROM  

                               gl_periods      gl,

                       gl_periods      gl2

                              WHERE 1=1

                      AND gl.period_set_name ='XX Period Name'

                      AND (

       ( 

           gl2.period_name = :P_Comp_Period

  AND gl2.PERIOD_YEAR = :P_Comp_Fis_Year

  AND trunc(gl2.start_date) between gl.start_date and gl.end_date

    )

  OR

  (

  ( trunc(gl2.start_date-1) between gl.start_date and gl.end_date ) 

   AND gl2.period_name = :P_Period

   AND gl2.PERIOD_YEAR = :P_Fiscal_Year

   AND :P_Comp_Period IS NULL

   AND :P_Comp_Fis_Year IS NULL

  )

  )

                      AND gl2.period_set_name ='XX Period Name'

  )   

        GROUP BY AIA.VENDOR_ID

        ) prev_period

        ,POZ_SUPPLIERS POS

        ,HZ_PARTIES     HP

WHERE POS.VENDOR_ID = cur_period.vendor_id(+)

AND   POS.VENDOR_ID  = Prev_period.vendor_id(+)

AND   POS.party_id  = hp.party_id

AND ((COALESCE(NULL, :P_Supplier) IS NULL)

       OR (HP.PARTY_NAME IN (:P_Supplier)))

and (NVL(cur_period.PAYMENT_AMOUNT ,0)<>0 OR NVl(prev_period.PAYMENT_AMOUNT ,0)<>0)

AND abs((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100)) > = NVL(:P_Change,abs((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100)))

Employee with all element entries Query in Oracle HCM Cloud

 SELECT 

       ldg.name                         ldg_name,

       pect1.classification_name        primary_classification,

       (SELECT pect2.classification_name

       FROM pay_ele_classifications_tl pect2

       WHERE pect2.classification_id          = pet.secondary_classification_id

       AND pect2.LANGUAGE                     = USERENV('LANG')

       )                                secondary_classification,

       pldf.attribute1                  region,

       ftl.territory_short_name         country_name,

       ppslv.person_number              person_number,

       ppnf.full_name                   employee_name,

       pldft.location_name              location,

       houft.name                       department,

       (SELECT ffvv.description

        FROM gl_code_combinations   gcc,

             fnd_flex_values_vl     ffvv,

             fnd_id_flex_segments   fifs

        WHERE gcc.code_combination_id   = paam.default_code_comb_id

        AND gcc.segment5                = ffvv.flex_value

        AND ffvv.flex_value_set_id      = fifs.flex_value_set_id

        AND fifs.SEGMENT_NAME           = 'Team'

        AND fifs.id_flex_code           = 'GL#'

       ) team,

       pet.reporting_name                  reporting_name,

       to_char(pee.creation_date, 'MM-DD-YYYY') entry_date,

       to_char(pee.effective_start_date, 'MM-DD-YYYY') effective_start_date,

       to_char(pee.effective_end_date, 'MM-DD-YYYY') effective_end_date,

       pet.element_name                    element_name,

       pet.base_element_name               element_code,

       pet.description                     element_description,

       DECODE(pet.processing_type, 'R', 'Yes', 'N', 'No', 'No') recurring_entry,

       ldg.default_currency_code           currency,

       peevf.screen_entry_value            amount,

       ( CASE

WHEN ldg.default_currency_code = 'USD'

THEN to_number(peevf.screen_entry_value)

            ELSE

            ROUND((to_number(peevf.screen_entry_value)) * gdr.conversion_rate,2)

         END

) amount_usd,

       pgft.name                        job_grade,

       pjf.job_code                     job_code,

       pelf.attribute1                  bonus_eligible,

       pelf.attribute2                  merit_eligible,

       pelf.attribute3                  pension_eligible

  FROM pay_legislative_data_groups      ldg,

       pay_element_types_vl             pet,

   (select gdr1.conversion_rate, gdr1.from_currency

FROM gl_daily_rates gdr1

WHERE  gdr1.to_currency = 'USD'

AND gdr1.conversion_type = 'Corporate'

AND gdr1.conversion_date = (SELECT MAX(gdr2.conversion_date)

FROM  gl_daily_rates gdr2

WHERE gdr2.to_currency = 'USD'

AND gdr2.conversion_type = 'Corporate'

AND gdr2.from_currency=gdr1.from_currency

AND gdr2.conversion_date <= sysdate)

)                    gdr,

       pay_element_links_f              pelf,

       pay_ele_classifications_tl       pect1,

       pay_element_entries_f            pee,

       pay_element_entry_values_f       peevf,

       pay_input_values_f               piv,

       per_person_names_f               ppnf,

       per_person_secured_list_v        ppslv,

       per_all_assignments_m            paam,

       hr_organization_units_f_tl       houft,

       per_location_details_f           pldf,

       per_location_details_f_tl        pldft,

       per_grades_f_tl                  pgft,

       per_jobs_f                       pjf,

       per_locations                    pl,

       fnd_territories_tl               ftl

WHERE (( coalesce(NULL, :p_ldg_name) IS NULL )

          OR ( ldg.name IN (:p_ldg_name) ) )

   AND ( ( coalesce(NULL, :p_reporting_name) IS NULL )

          OR ( pet.reporting_name IN ( :p_reporting_name ) ) )

   AND ( ( coalesce(NULL, :p_element_name) IS NULL )

          OR ( pet.element_type_id IN ( :p_element_name ) ) )

   AND ( ( coalesce(NULL, :p_processing_type) IS NULL )

          OR ( pet.processing_type IN ( :p_processing_type ) ) )

   AND ldg.legislative_data_group_id  = pet.legislative_data_group_id

   AND ldg.default_currency_code      = gdr.from_currency(+)

   AND pet.element_type_id            = pee.element_type_id

   AND pet.element_type_id            = pelf.element_type_id

   AND pet.classification_id          = pect1.classification_id

   AND UPPER(pet.element_name) NOT LIKE '%SALARY%'

   AND pect1.LANGUAGE                 = USERENV('LANG')

   AND TRUNC(SYSDATE) BETWEEN pet.effective_start_date AND pet.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN pelf.effective_start_date AND pelf.effective_end_date

   AND pee.person_id                  = ppnf.person_id

   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

   AND pee.element_entry_id           = peevf.element_entry_id

   AND pee.last_update_date           = peevf.last_update_date

   AND pee.effective_start_date       = peevf.effective_start_date

   AND peevf.input_value_id           = piv.input_value_id

   AND piv.element_type_id            = pet.element_type_id

   AND piv.base_name                  = 'Amount'

   AND TRUNC(SYSDATE) BETWEEN piv.effective_start_date AND piv.effective_end_date

   AND ppnf.person_id                 = ppslv.person_id

   AND TRUNC(SYSDATE) BETWEEN ppslv.effective_start_date AND ppslv.effective_end_date

   AND ppnf.person_id                 = paam.person_id

   AND ppnf.name_type                 = 'GLOBAL'

   AND paam.organization_id           = houft.organization_id

   AND houft.LANGUAGE                 = USERENV('LANG')

   AND paam.effective_latest_change   = 'Y'

   AND paam.assignment_type           = 'E'

   AND paam.assignment_status_type    = 'ACTIVE'

   AND paam.primary_assignment_flag   = 'Y'

   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

   AND paam.location_id               = pldf.location_id

   AND pldf.location_details_id       = pldft.location_details_id

   AND pldft.LANGUAGE                 = USERENV('LANG')

   AND paam.grade_id                  = pgft.grade_id

   AND pgft.LANGUAGE                  = USERENV('LANG')

   AND TRUNC(SYSDATE) BETWEEN pgft.effective_start_date AND pgft.effective_end_date

   AND paam.job_id                    = pjf.job_id

   AND TRUNC(SYSDATE) BETWEEN pjf.effective_start_date AND pjf.effective_end_date

   AND paam.location_id               = pl.location_id

   AND pl.country                     = ftl.TERRITORY_CODE

   AND ftl.LANGUAGE                   = USERENV('LANG')

   AND (( coalesce(NULL, :p_full_name) IS NULL )

         OR ( ppnf.full_name IN (:p_full_name) ) )

   AND pee.effective_start_date <= :p_end_date

   AND pee.effective_end_date >= :p_start_date 

   --AND :p_start_date <= :P_end_date

ORDER BY ldg.name, ppnf.full_name, pet.element_name,pee.effective_start_date DESC

Active Employees and Their Jobs in oracle apps

Active Employees and Their Jobs

SELECT papf.full_name emp_name,
      papf.current_employee_flag current_emp_flag,
      papf.employee_number emp_number,
      b.d_job_id job_title,
      papf.email_address email

FROM  apps.PER_ALL_PEOPLE_F       papf,
      apps.PER_ALL_ASSIGNMENTS_F asg,
      apps.PER_ASSIGNMENTS_V7    b,
      apps.PER_JOBS              pjb
   
WHERE papf.person_id       = asg.person_id(+)
AND asg.person_id          = b.person_id
AND b.effective_start_date =
  (SELECT MAX (b2.effective_start_date)
  FROM apps.per_assignments_v7 b2
  WHERE b2.person_id = b.person_id
  )
AND NVL (papf.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (asg.effective_end_date, SYSDATE  + 1) > SYSDATE
AND b.job_id                                   = pjb.job_id(+)
ORDER BY papf.full_name

Employee supervisor Oracle R12 SQL Query

# Oracle EBS SQL Query that will list all employees in the hr.per_all_assignments_f 
# table and their supervisor, in readable format.

SELECT DISTINCT papf1.full_name leve1_full_name
     , papf1.employee_number level1_empno
     , papf2.full_name leve2_full_name
     , papf2.employee_number level2_empno
  FROM hr.per_all_people_f papf1
     , hr.per_all_assignments_f paaf1
     , hr.per_all_assignments_f paaf2
     , hr.per_all_people_f papf2
  WHERE  papf1.person_id = paaf1.person_id
  AND paaf1.supervisor_id = papf2.person_id(+)
  AND papf2.person_id = paaf2.person_id

  ORDER BY leve1_full_name;