Payroll Functions - Assignment action id and related function to fetch run result values

----query to fetch assignment action id for regular run

SELECT MAX(PAA.ASSIGNMENT_ACTION_ID) ASG_ACT_ID
,PAA.ASSIGNMENT_ID ASSIGNMENT_ID
,PAAF.PERSON_ID PERSON_ID
FROM PER_TIME_PERIODS PTP
,PAY_ASSIGNMENT_ACTIONS PAA
,PER_ALL_PEOPLE_F PAPF
,PER_ALL_ASSIGNMENTS_F PAAF
,PAY_PAYROLL_ACTIONS PPA
,PER_PERIODS_OF_SERVICE PPOS
WHERE PPA.ACTION_TYPE IN('R', 'Q', 'I', 'B')
AND PPA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
--And paa.source_action_id is not null
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAPF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND PAAF.ORGANIZATION_ID = NVL(P_ORG_ID, PAAF.ORGANIZATION_ID)
AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAAF.PAYROLL_ID = PTP.PAYROLL_ID
AND PTP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
AND PTP.PAYROLL_ID = PPA.PAYROLL_ID
AND PAAF.PAYROLL_ID = P_PAYROLL_ID
AND PPA.EFFECTIVE_DATE BETWEEN V_DATE AND LAST_DAY(V_DATE)
AND ( V_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
OR PAAF.EFFECTIVE_START_DATE BETWEEN V_DATE AND LAST_DAY(V_DATE))
GROUP BY PAA.ASSIGNMENT_ID ,PAAF.PERSON_ID;

--- For process separate Run

SELECT   MAX(paa.assignment_action_id)
             ,paa.assignment_id
  
    FROM      pay_assignment_actions         paa
             ,pay_payroll_actions            ppa
             ,per_time_periods               ptp
             ,per_all_assignments_f          paaf
             ,pay_run_types_f                prtf
    WHERE     PPA.action_type                in ('R','Q','I','B')
    AND       PPA.action_status              = 'C'
    AND       paa.payroll_action_id          = ppa.payroll_action_id
    AND       PAA.action_status              = 'C'
    AND       ppa.payroll_id                 = ptp.payroll_id
    AND       ptp.time_period_id             = ppa.time_period_id
    AND       paa.assignment_id              = paaf.assignment_id
    AND       paaf.person_id                 = p_person_id
    and       prtf.run_type_id               = paa.run_type_id
    and       prtf.run_type_name    like '%Process Separate%'      ---- updated for separate run
    AND       paaf.business_group_id         =fnd_profile.value('PER_BUSINESS_GROUP_ID')
    AND       l_date               BETWEEN prtf.effective_start_date AND prtf.effective_end_date
    AND       ppa.effective_date             BETWEEN l_date          AND last_day(l_date)
    AND       (l_date              BETWEEN paaf.effective_start_date AND paaf.effective_end_date
               OR  paaf.effective_start_date BETWEEN l_date          AND last_day(l_date))
    GROUP BY  paa.assignment_id;
-------------------------------------------------------------------------------------------------

----Function to fetch Run Result Value from assignment id


FUNCTION XXARJ_PR_ELEMENT_VALUE_ASG_FNC
  (
      P_ASSIGNMENT_ID         NUMBER
     ,P_INPUT_VALUE_NAME      VARCHAR2
     ,P_ELEMENT_NAME          VARCHAR2
     ,P_DATE                  DATE
  )  RETURN NUMBER
AS
      L_ELEMENT_VALUE NUMBER:=0;

BEGIN

SELECT  SUM(fnd_number.canonical_to_number(NVL(PRRV.RESULT_VALUE,0)))

  INTO L_ELEMENT_VALUE

FROM PAY_ELEMENT_TYPES_F PETF,
    PAY_RUN_RESULTS PRR,
    PAY_RUN_RESULT_VALUES PRRV,
    PAY_INPUT_VALUES_F PIVF,
    PAY_ASSIGNMENT_ACTIONS PAA,
    PAY_PAYROLL_ACTIONS PPA

WHERE PPA.ACTION_TYPE               IN('R',   'Q',   'I',   'B')
AND PPA.ACTION_STATUS               = 'C'
AND PPA.PAYROLL_ACTION_ID           = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS               = 'C'
AND PRR.ASSIGNMENT_ACTION_ID        = PAA.ASSIGNMENT_ACTION_ID
AND PAA.ASSIGNMENT_ID               = P_ASSIGNMENT_ID
AND PETF.ELEMENT_NAME               = P_ELEMENT_NAME
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID            = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID               = PRRV.RUN_RESULT_ID
AND PIVF.NAME                       = P_INPUT_VALUE_NAME
And     paa.source_action_id is not null
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.INPUT_VALUE_ID             = PRRV.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID            = PIVF.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED   BETWEEN P_DATE AND LAST_DAY(P_DATE)
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;

RETURN L_ELEMENT_VALUE;

EXCEPTION
WHEN OTHERS THEN
  RETURN L_ELEMENT_VALUE;
END XXARJ_PR_ELEMENT_VALUE_ASG_FNC;

----Function to fetch Run Result Value from assignment ACTION id

FUNCTION XXARJ_PR_CALC_SITE_FNC
  (
      P_ASSIGNMENT_ACTION_ID  NUMBER
     ,P_INPUT_VALUE_NAME      VARCHAR2
     ,P_ELEMENT_NAME          VARCHAR2
     ,P_DATE                  DATE
  )  RETURN NUMBER
AS
      L_ELEMENT_VALUE NUMBER:=0;
BEGIN
SELECT  SUM(fnd_number.canonical_to_number(NVL(PRRV.RESULT_VALUE,0)))
  INTO L_ELEMENT_VALUE
FROM PAY_ELEMENT_TYPES_F PETF,
    PAY_RUN_RESULTS PRR,
    PAY_RUN_RESULT_VALUES PRRV,
    PAY_INPUT_VALUES_F PIVF
WHERE PRR.ASSIGNMENT_ACTION_ID      = P_ASSIGNMENT_ACTION_ID
AND PETF.ELEMENT_NAME               = P_ELEMENT_NAME
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID            = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID               = PRRV.RUN_RESULT_ID
AND PIVF.NAME                       = P_INPUT_VALUE_NAME
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.INPUT_VALUE_ID             = PRRV.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID            = PIVF.ELEMENT_TYPE_ID
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;
RETURN L_ELEMENT_VALUE;
EXCEPTION WHEN OTHERS THEN
  L_ELEMENT_VALUE :=-1;
  RETURN L_ELEMENT_VALUE;
  END XXARJ_PR_CALC_SITE_FNC;

---FUNCTION TO GET VALUE FROM BALANCE


FUNCTION XXARJ_PR_NET_SAL_FNC
    (
      P_ASSIGNMENT_ACTION_ID NUMBER,
      P_BAL_NAME      IN VARCHAR2 ,
      P_DIM_NAME      IN VARCHAR2 )
RETURN VARCHAR2
AS
     L_NET_SALARY     NUMBER:=0;
     V_NET_PAY_ID     NUMBER;
BEGIN
    /*V_NET_PAY_ID := xxplg_pay_payroll_reports_pkg.get_defined_bal_id(P_BAL_NAME, P_DIM_NAME);*/
    BEGIN
    SELECT pdb.defined_balance_id
    INTO V_NET_PAY_ID
    FROM   pay_balance_types pbt
          ,pay_balance_dimensions pbd
          ,pay_defined_balances pdb
    WHERE  pdb.balance_type_id       = pbt.balance_type_id
    AND    pdb.balance_dimension_id  = pbd.balance_dimension_id
    AND    pbt.balance_name          = P_BAL_NAME
    AND    (pbt.business_group_id    = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') or pbt.legislation_code = 'IN')
    AND    (pbd.business_group_id    = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') or pbd.legislation_code = 'IN')
    AND    (pdb.business_group_id    = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') or pdb.legislation_code = 'IN')
    AND    pbd.database_item_suffix  = P_DIM_NAME;
    EXCEPTION WHEN OTHERS THEN
    V_NET_PAY_ID :=1;
    END;
   L_NET_SALARY := pay_balance_pkg.get_value(V_NET_PAY_ID, P_ASSIGNMENT_ACTION_ID);
   --FND_FILE.PUT_LINE(FND_FILE.log,'~~L_NET_SALARY :'|| '    ' ||L_NET_SALARY);
RETURN L_NET_SALARY;
EXCEPTION
  WHEN OTHERS THEN
RETURN L_NET_SALARY;
END XXARJ_PR_NET_SAL_FNC;

-- Query to Fetch Standard Value

SELECT NVL(SUM(fnd_number.canonical_to_number(peevf.screen_entry_value)),0)
--INTO l_standard_value

FROM pay_element_types_f petf,
pay_element_entries_f peef,
pay_element_entry_values_f peevf,
pay_input_values_f pivf

WHERE peef.element_entry_id = peevf.element_entry_id
AND peef.assignment_id = L_ASS_ID
AND petf.element_name = 'Thailand Allowance'
AND (petf.legislation_code = 'IN' OR petf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---fnd_profile.value('PER_BUSINESS_GROUP_ID')) --83)-- fnd_profile.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.NAME IN ('Standard Value','Bonus Amount')
AND (pivf.legislation_code = 'IN' OR pivf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))---fnd_profile.value('PER_BUSINESS_GROUP_ID')) --83)-- fnd_profile.value('PER_BUSINESS_GROUP_ID'))
AND petf.element_type_id = pivf.element_type_id
AND peevf.input_value_id = pivf.input_value_id
AND l_date BETWEEN petf.effective_start_date AND petf.effective_end_date
--AND p_date BETWEEN peef.effective_start_date AND peef.effective_end_date
--AND p_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
AND (l_date BETWEEN peef.effective_start_date AND peef.effective_end_date
OR peef.effective_start_date BETWEEN l_date and last_day( l_date))
AND (l_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
OR peevf.effective_start_date BETWEEN l_date and last_day( l_date))
-- AND peef.effective_start_date BETWEEN p_date and last_day(p_date)
-- AND peevf.effective_start_date BETWEEN p_date and last_day(p_date)
AND l_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;

------- Function For Summation of result values in between two dates
function sum_value(  c_assignment_id            in number
                          , l_start_date        in date
                          , l_last_date         in date
                          , l_element_name      in varchar2
                          )
return number
as
  l_sum number;
begin
  select  sum(fnd_number.canonical_to_number(nvl(prrv.result_value,0)))
   into   l_sum
  from       pay_assignment_actions        paa
           , pay_payroll_actions           ppa
           , pay_run_results               prr
           , pay_run_result_values         prrv
           , pay_element_types_f           petf
           , pay_input_values_f            pivf
  where   ppa.action_type             IN('R','Q','I','B')
  and     ppa.action_status           = 'C'
  and     ppa.payroll_action_id       = paa.payroll_action_id
  and     paa.action_status           = 'C'
  and     paa.assignment_action_id    = prr.assignment_action_id
  and     prr.run_result_id           = prrv.run_result_id
  and     petf.element_type_id        = prr.element_type_id
  and     (petf.legislation_code  = 'IN' OR petf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))
  and     (pivf.legislation_code  = 'IN' OR petf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))
  and     pivf.input_value_id         = prrv.input_value_id
  and     petf.element_type_id        = pivf.element_type_id
  and     petf.element_name           = l_element_name
  and     pivf.name                   = 'Pay Value'
  and     paa.assignment_id           = c_assignment_id       ---   9644
  and     ppa.effective_date  between l_start_date               and l_last_date
  and     l_last_date         between petf.effective_start_date  and petf.effective_end_date
  and     l_last_date         between pivf.effective_start_date  and pivf.effective_end_date;
--
return l_sum;
EXCEPTION
    WHEN OTHERS THEN
    l_sum :=0;
     FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in function   '||sqlerrm);
   RETURN l_sum;
end;