اريد عمل تقرير بيحسب رصيد الاجازات الاعتيادية لكل الموظفين من الابلكشن لو حد عنده كويري او طريقة لعمل التقرير ده

 

شكرا

السلام عليكم

استخدم هذا الـ Function:

 

CREATE OR REPLACE FUNCTION CUST_GET_NET_ENTITLEMENT (
   P_ASSG_ID            IN   NUMBER,
   P_PAYROLL_ID         IN   NUMBER,
   P_CALCULATION_DATE   IN   DATE
)
   RETURN NUMBER
IS
   L_START_DATE         DATE;
   L_END_DATE           DATE;
   L_ACC_END_DATE       DATE;
   L_VALUE              NUMBER         := 0;
   L_NET_VALUE          NUMBER (15, 2);
   P_PLAN_ID            NUMBER;
--   L_CALCULATION_DATE   DATE;
BEGIN
   SELECT PAP.ACCRUAL_PLAN_ID
     INTO P_PLAN_ID
     FROM PAY_ACCRUAL_PLANS PAP
    WHERE UPPER (PAP.ACCRUAL_PLAN_NAME) LIKE
             (SELECT UPPER (PETF.ELEMENT_NAME)
                FROM PAY_ELEMENT_ENTRIES_F PEEF, PAY_ELEMENT_TYPES_F PETF
               WHERE PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
                 AND UPPER (PETF.ELEMENT_NAME) LIKE '%PLAN%'
                 AND PETF.PROCESSING_TYPE = 'R'
                 AND PEEF.ASSIGNMENT_ID = P_ASSG_ID
                 AND P_CALCULATION_DATE BETWEEN PEEF.EFFECTIVE_START_DATE
                                            AND PEEF.EFFECTIVE_END_DATE);


/* QUERY ABOVE AUTOMATICALLY GETS THE PLAN ID OF THE ACCRUAL PLAN ID ATTACHED AS ON CALCULATION DATE*/
   PER_ACCRUAL_CALC_FUNCTIONS.GET_NET_ACCRUAL
                           (P_ASSIGNMENT_ID               => P_ASSG_ID,
                            P_PLAN_ID                     => P_PLAN_ID,
                            P_PAYROLL_ID                  => P_PAYROLL_ID,
                            P_BUSINESS_GROUP_ID           => 81,   -- Kindly change your business group id accordingly
                            P_ASSIGNMENT_ACTION_ID        => -1,
                            P_CALCULATION_DATE            => TO_DATE
                                                               (P_CALCULATION_DATE
                                                                )
-- DATE YOU WANT TO CHECK THE NET ENTITLEMENT EX. TO_DATE('01-MAR-2009', 'DD-MON-YYYY')
   ,
                            P_ACCRUAL_START_DATE          => NULL,
                            P_ACCRUAL_LATEST_BALANCE      => NULL,
                            P_CALLING_POINT               => 'FRM',
                            P_START_DATE                  => L_START_DATE,
                            P_END_DATE                    => L_END_DATE,
                            P_ACCRUAL_END_DATE            => L_ACC_END_DATE,
                            P_ACCRUAL                     => L_VALUE,
                            P_NET_ENTITLEMENT             => L_NET_VALUE
                           );
   RETURN NVL (L_NET_VALUE, 0);
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN 0;
END;
/