Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Thursday, 29 August 2019

Query to Get Employee Corporate Card Census Details in Oracle Apps R12

SELECT   papf.employee_number
        ,haou.NAME department
        ,hrl.location_code LOCATION
        ,acpa.card_program_name card_program
        ,icc.masked_cc_number credit_card_num
        ,papf1.full_name supervisor
        ,past.user_status assignment_status
        ,TO_CHAR (icc.expirydate, 'DD-MON-YYYY') expiry_date
        ,TO_CHAR (aca.inactive_date, 'DD-MON-YYYY') inactive_on
        ,DECODE (icc.expired_flag, 'Y', 'Expired', 'N', 'Unexpired')
        ,aca.limit_override_amount max_amt_per_period
        ,aca.trx_limit_override_amount max_amt_per_trans
FROM     ap_cards_all aca
        ,ap_card_programs_all acpa
        ,iby_creditcard icc
        ,per_all_people_f papf
        ,per_all_assignments_f paaf
        ,per_assignment_status_types past
        ,hr_all_organization_units haou
        ,hr_locations hrl
        ,per_all_people_f papf1
WHERE    1 = 1
AND      aca.card_program_id = acpa.card_program_id
AND      aca.card_reference_id = icc.instrid
AND      aca.employee_id = papf.person_id
AND      papf.person_id = paaf.person_id
AND      paaf.assignment_status_type_id = past.assignment_status_type_id
AND      paaf.organization_id = haou.organization_id
AND      paaf.location_id = hrl.location_id
AND      paaf.supervisor_id = papf1.person_id
AND      SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND      SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND      SYSDATE BETWEEN papf1.effective_start_date AND papf1.effective_end_date
--AND      hrl.location_id = NVL (:P_EMP_LOC, hrl.location_id)
--AND      haou.organization_id = NVL (:P_EMP_DEPT, haou.organization_id)
---AND      aca.creation_date BETWEEN NVL (TO_DATE (SUBSTR ----(:P_FROM_DATE, 1, 10)
                                       , aca.creation_date - 1)
                             ---  AND NVL (TO_DATE (SUBSTR (:P_TO_DATE, 1, 10)
                                        + 86399 / 86400
                                       , aca.creation_date + 1)
AND      NVL (aca.inactive_date, SYSDATE + 1) > SYSDATE

Share this article :


Post a Comment