eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_NL_DIM_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 26


        select ptp.start_date
        into   l_period_start_date
        from   per_time_periods ptp, pay_payroll_actions ppa
        where  ppa.payroll_action_id = p_user_payroll_action_id
        and    ppa.payroll_id = ptp.payroll_id
        and    p_user_effective_date between ptp.start_date and ptp.end_date;

Line: 51


   SELECT TP.end_date
   INTO   p_expiry_information
   FROM   per_time_periods    TP
         ,pay_payroll_actions PACT
   WHERE  PACT.payroll_action_id = p_owner_payroll_action_id
     AND  PACT.payroll_id        = TP.payroll_id
     AND  p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;

Line: 86


        select ptp.start_date
        into   l_period_start_date
        from   per_time_periods ptp, pay_payroll_actions ppa
        where  ppa.payroll_action_id = p_user_payroll_action_id
        and    ppa.payroll_id = ptp.payroll_id
        and    p_user_effective_date between ptp.start_date and ptp.end_date;

Line: 111


   SELECT TP.end_date
   INTO   p_expiry_information
   FROM   per_time_periods    TP
         ,pay_payroll_actions PACT
   WHERE  PACT.payroll_action_id = p_owner_payroll_action_id
     AND  PACT.payroll_id        = TP.payroll_id
     AND  p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;

Line: 360


        select ptp.start_date
        into   l_period_start_date
        from   per_time_periods ptp, pay_payroll_actions ppa
        where  ppa.payroll_action_id = p_user_payroll_action_id
        and    ppa.payroll_id = ptp.payroll_id
        and    p_user_effective_date between ptp.start_date and ptp.end_date;

Line: 385


        select ptp.END_DATE
        into   p_expiry_information
        from   per_time_periods ptp, pay_payroll_actions ppa
        where  ppa.payroll_action_id = p_user_payroll_action_id
        and    ppa.payroll_id = ptp.payroll_id
        and    p_owner_effective_date between ptp.start_date and ptp.end_date;

Line: 415


        select ptp.start_date
        into   l_period_start_date
        from   per_time_periods ptp, pay_payroll_actions ppa
        where  ppa.payroll_action_id = p_user_payroll_action_id
        and    ppa.payroll_id = ptp.payroll_id
        and    p_user_effective_date between ptp.start_date and ptp.end_date;

Line: 440


   SELECT TP.end_date
   INTO   p_expiry_information
   FROM   per_time_periods    TP
         ,pay_payroll_actions PACT
   WHERE  PACT.payroll_action_id = p_owner_payroll_action_id
     AND  PACT.payroll_id        = TP.payroll_id
     AND  p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;

Line: 734


    SELECT DECODE(trunc((to_number(to_char(p_owner_effective_date,'IW'))-1)/12)
                 ,4,3
                 ,trunc((to_number(to_char(p_owner_effective_date,'IW'))-1)/12))
          ,DECODE(trunc((to_number(to_char(p_user_effective_date,'IW'))-1)/12)
                 ,4,3
                 ,trunc((to_number(to_char(p_user_effective_date,'IW'))-1)/12))
    INTO  l_owner_quarter, l_user_quarter
    FROM  dual;

Line: 768


select trunc(trunc(trunc(p_owner_effective_date,'IW')+6,'Y'),'IW')-1, trunc(p_owner_effective_date,'IW')+6
into  l_lastyr_wkend ,l_curr_wkend
FROM DUAL;

Line: 777


select (l_curr_wkend-l_lastyr_wkend)/7 into l_week_no from dual;

Line: 778


select (decode(trunc((l_week_no-1)/12+1)*12 ,48,l_max_period,60,l_max_period ,trunc((l_week_no-1)/12+1)*12))*7 into l_no_days from dual;

Line: 821


   SELECT TP.end_date
   INTO   p_expiry_information
   FROM   per_time_periods    TP
         ,pay_payroll_actions PACT
   WHERE  PACT.payroll_action_id = p_owner_payroll_action_id
     AND  PACT.payroll_id        = TP.payroll_id
     AND  p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;

Line: 850


  SELECT ptp.start_date
  INTO   l_period_start_date
  FROM   per_time_periods ptp, pay_payroll_actions ppa
  WHERE  ppa.payroll_action_id = p_user_payroll_action_id
  AND    ppa.payroll_id = ptp.payroll_id
  AND    p_user_effective_date BETWEEN ptp.start_date AND ptp.end_date;

Line: 876


   SELECT TP.end_date
   INTO   p_expiry_information
   FROM   per_time_periods    TP
         ,pay_payroll_actions PACT
   WHERE  PACT.payroll_action_id = p_owner_payroll_action_id
     AND  PACT.payroll_id        = TP.payroll_id
     AND  p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;

Line: 908


    SELECT DECODE(trunc((to_number(to_char(p_owner_effective_date,'IW'))-1)/4)
                 ,13,12
                 ,trunc((to_number(to_char(p_owner_effective_date,'IW'))-1)/4))
          ,DECODE(trunc((to_number(to_char(p_user_effective_date,'IW'))-1)/4)
                 ,13,12
                 ,trunc((to_number(to_char(p_user_effective_date,'IW'))-1)/4))
    INTO  l_owner_month, l_user_month
    FROM  dual;

Line: 941


SELECT 	MAX(TP.period_num)
INTO 	l_max_period
FROM 	per_time_periods TP
        ,pay_payroll_actions PACT
WHERE  	PACT.payroll_action_id = p_owner_payroll_action_id
AND  	PACT.payroll_id        = TP.payroll_id
AND 	TP.end_date BETWEEN TRUNC(p_owner_effective_date,'Y')
	AND (TRUNC(ADD_MONTHS(p_owner_effective_date,12),'Y')-1);

Line: 951


	SELECT 	TP.end_date
	INTO   	p_expiry_information
	FROM   	per_time_periods    TP
	,pay_payroll_actions PACT
	WHERE  	PACT.payroll_action_id = p_owner_payroll_action_id
	AND  	PACT.payroll_id        = TP.payroll_id
	AND  	p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;

Line: 960


	select trunc(trunc(trunc(p_owner_effective_date,'IW')+6,'Y'),'IW')-1, trunc(p_owner_effective_date,'IW')+6
	into  l_lastyr_wkend ,l_curr_wkend
	FROM DUAL;

Line: 969


	select (l_curr_wkend-l_lastyr_wkend)/7 into l_week_no from dual;

Line: 970


	select (decode(trunc((l_week_no-1)/4+1)*4 ,52,l_max_period,56,l_max_period ,trunc((l_week_no-1)/4+1)*4))*7 into l_no_days from dual;

Line: 1002


    SELECT DECODE(trunc((to_number(to_char(p_owner_effective_date,'IW'))-1)/4)
                 ,13,12
                 ,trunc((to_number(to_char(p_owner_effective_date,'IW'))-1)/4))
          ,DECODE(trunc((to_number(to_char(p_user_effective_date,'IW'))-1)/4)
                 ,13,12
                 ,trunc((to_number(to_char(p_user_effective_date,'IW'))-1)/4))
    INTO  l_owner_month, l_user_month
    FROM  dual;

Line: 1035


SELECT 	MAX(TP.period_num)
INTO 	l_max_period
FROM 	per_time_periods TP
        ,pay_payroll_actions PACT
WHERE  	PACT.payroll_action_id = p_owner_payroll_action_id
AND  	PACT.payroll_id        = TP.payroll_id
AND 	TP.end_date BETWEEN TRUNC(p_owner_effective_date,'Y')
	AND (TRUNC(ADD_MONTHS(p_owner_effective_date,12),'Y')-1);

Line: 1045


	SELECT 	TP.end_date
	INTO   	p_expiry_information
	FROM   	per_time_periods    TP
	,pay_payroll_actions PACT
	WHERE  	PACT.payroll_action_id = p_owner_payroll_action_id
	AND  	PACT.payroll_id        = TP.payroll_id
	AND  	p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;

Line: 1054


	select trunc(trunc(trunc(p_owner_effective_date,'IW')+6,'Y'),'IW')-1, trunc(p_owner_effective_date,'IW')+6
	into  l_lastyr_wkend ,l_curr_wkend
	FROM DUAL;

Line: 1063


	select (l_curr_wkend-l_lastyr_wkend)/7 into l_week_no from dual;

Line: 1064


	select (decode(trunc((l_week_no-1)/4+1)*4 ,52,l_max_period,56,l_max_period ,trunc((l_week_no-1)/4+1)*4))*7 into l_no_days from dual;