[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fnd_date.canonical_to_date(org_information11)
FROM hr_organization_information
WHERE org_information_context = 'Business Group Information'
AND organization_id = p_business_group_id;
SELECT to_char(nvl(FND_DATE.CANONICAL_TO_DATE(org_information11),to_date(to_char(p_input_date,'YYYY')||'/04/01','YYYY/MM/DD')),'DD-MM-YYYY')
FROM hr_organization_information
WHERE org_information_context='Business Group Information'
AND organization_id = p_business_group_id;
/* -- select from Org. Develper PDF */
/*
open csr_start_date;
SELECT to_char(nvl(FND_DATE.CANONICAL_TO_DATE(org_information11),to_date(to_char(p_input_date,'YYYY')||'/04/01','YYYY/MM/DD')),'DD-MM-YYYY')
FROM hr_organization_information
WHERE org_information_context='Business Group Information'
AND organization_id = p_business_group_id;
/* -- select from Org. Develper PDF */
/*
open csr_start_date;
select /*+ ORDERED
USE_NL(FEED)
INDEX(FEED PAY_BALANCE_FEEDS_F_N50) */
NVL(SUM(FND_NUMBER.CANONICAL_TO_NUMBER(TARGET.result_value) * FEED.scale),0)
FROM pay_assignment_actions ASSACT,
pay_payroll_actions PACT,
pay_run_results RR,
pay_run_result_values TARGET,
pay_balance_feeds_f FEED
WHERE ASSACT.assignment_id = P_ASSIGNMENT_ID
-- AND ASSACT.action_sequence <= NVL(P_ACTION_SEQUENCE,ASSACT.action_sequence)
AND PACT.payroll_action_id = ASSACT.payroll_action_id
AND PACT.date_earned >= P_FROM_DATE
AND ( ( PACT.date_earned < P_TO_DATE)
or ( PACT.date_earned = P_TO_DATE
and ASSACT.action_sequence <= P_ACTION_SEQUENCE))
AND RR.assignment_action_id = ASSACT.assignment_action_id
AND RR.status IN ('P','PA')
AND TARGET.run_result_id = RR.run_result_id
AND nvl(TARGET.result_value, '0') <> '0'
AND FEED.input_value_id = TARGET.input_value_id
AND FEED.balance_type_id = P_BALANCE_TYPE_ID
-- AND PACT.effective_date
AND PACT.date_earned
BETWEEN FEED.effective_start_date AND FEED.effective_end_date;
SELECT /*+ ORDERED */
NVL(SUM(FND_NUMBER.CANONICAL_TO_NUMBER(TARGET.result_value) * FEED.scale),0)
FROM ( select /*+ ORDERED */
ASSACT.assignment_action_id,
PACT.effective_date
from pay_assignment_actions ASSACT,
pay_payroll_actions PACT
WHERE ASSACT.assignment_id = p_assignment_id
AND ASSACT.action_sequence <= NVL(p_action_sequence, ASSACT.action_sequence)
AND PACT.payroll_action_id = ASSACT.payroll_action_id
AND PACT.action_type <> 'V'
AND PACT.effective_date >= p_from_date
AND PACT.effective_date <= p_to_date
AND NOT EXISTS(
SELECT NULL
FROM pay_payroll_actions RPACT,
pay_assignment_actions RASSACT,
pay_action_interlocks RINTL
WHERE RINTL.locked_action_id = ASSACT.assignment_action_id
AND RASSACT.assignment_action_id = RINTL.locking_action_id
AND RPACT.payroll_action_id = RASSACT.payroll_action_id
AND RPACT.action_type = 'V')) V1,
pay_run_results RR,
pay_run_result_values TARGET,
pay_balance_feeds_f FEED
WHERE RR.assignment_action_id = V1.assignment_action_id
AND RR.status IN ('P','PA')
AND TARGET.run_result_id = RR.run_result_id
AND nvl(TARGET.result_value, '0') <> '0'
AND FEED.input_value_id = TARGET.input_value_id
AND FEED.balance_type_id = p_balance_type_id
AND V1.effective_date
BETWEEN FEED.effective_start_date AND FEED.effective_end_date;
SELECT /*+ ORDERED
INDEX(FEED PAY_BALANCE_FEEDS_F_N50) */
NVL(SUM(FND_NUMBER.CANONICAL_TO_NUMBER(TARGET.result_value) * FEED.scale),0)
FROM
( select BAL_ASSACT.assignment_id assignment_id,
BAL_ASSACT.action_sequence action_sequence,
-- BACT.action_type action_type,
BACT.date_earned date_earned,
--
-- When null, apply 1st January
--
add_months(
nvl(fnd_date.canonical_to_date(HROG.org_information11), trunc(BACT.date_earned, 'YYYY')),
floor(
months_between( BACT.date_earned,
nvl(fnd_date.canonical_to_date(HROG.org_information11), trunc(BACT.date_earned, 'YYYY')))
/ 12) * 12) start_date
from hr_organization_information HROG,
pay_payroll_actions BACT,
pay_assignment_actions BAL_ASSACT
where BAL_ASSACT.assignment_action_id = assact_id
and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
and HROG.organization_id = BACT.business_group_id
and HROG.org_information_context = 'Business Group Information') V1,
pay_assignment_actions ASSACT,
pay_payroll_actions PACT,
pay_run_results RR,
pay_run_result_values TARGET,
pay_balance_feeds_f FEED
WHERE ASSACT.assignment_id = V1.assignment_id
AND PACT.payroll_action_id = ASSACT.payroll_action_id
-- AND ASSACT.action_sequence <= decode(V1.action_type,'N',ASSACT.action_sequence,V1.action_sequence)
/* -- AND ( (V1.action_type = 'N')
-- or ( V1.action_type <> 'N'
-- AND ASSACT.action_sequence <= NVL(V1.action_sequence,ASSACT.action_sequence))) */
--
-- The system guarantees assignment actions ordered by effective_date as sequential action,
-- but does not guarantee actions ordered by date_earned as sequential action.
--
AND PACT.date_earned >= V1.start_date
AND ( ( PACT.date_earned < V1.date_earned)
or ( PACT.date_earned = V1.date_earned
and ASSACT.action_sequence <= V1.action_sequence))
AND RR.assignment_action_id = ASSACT.assignment_action_id
AND RR.status IN ('P','PA')
AND TARGET.run_result_id = RR.run_result_id
AND nvl(TARGET.result_value, '0') <> '0'
AND FEED.input_value_id = TARGET.input_value_id
AND FEED.balance_type_id = baltype
AND PACT.date_earned
BETWEEN FEED.effective_start_date AND FEED.effective_end_date;