[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fcu.currency_code
from hr_organization_information hoi,
hr_organization_units hou,
fnd_currencies fcu
where hou.business_group_id = c_business_group_id
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'Business Group Information'
and fcu.issuing_territory_code = hoi.org_information9;
select
pdb.defined_balance_id
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.balance_type_id = pdb.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_name = c_dimension_name
and pbt.balance_name = c_balance_name
and pbt.legislation_code = 'HK' ;
SELECT decode(pbt.balance_name, 'GROSS_PAY',1
, 'MANDATORY_DEDUCTIONS',2
, 'INVOLUNTARY_DEDUCTIONS',3
, 'VOLUNTARY_DEDUCTIONS',4
, 'NET',5
, 'DIRECT_PAYMENTS',6
, 'TOTAL_PAYMENTS',7) sort_index,
pdb.defined_balance_id defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name IN ('GROSS_PAY'
, 'MANDATORY_DEDUCTIONS'
, 'INVOLUNTARY_DEDUCTIONS'
, 'VOLUNTARY_DEDUCTIONS'
, 'NET'
, 'DIRECT_PAYMENTS'
, 'TOTAL_PAYMENTS')
AND pbd.dimension_name = c_dimension_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'HK'
ORDER BY sort_index;
SELECT max(locked_action_id)
FROM pay_action_interlocks
WHERE locking_action_id = p_assignment_action_id;
g_sql:=' SELECT element_reporting_name COL02
, TO_CHAR(amount, fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
FROM pay_hk_asg_elements_v
WHERE assignment_action_id = ' || p_assignment_action_id || '
AND (classification_name like ''%''||''' || p_classification_name || ''' ||''%'')
ORDER BY element_reporting_name';
g_sql:=' SELECT phaev.element_reporting_name COL02
, TO_CHAR(SUM(phaev.amount), fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
FROM pay_hk_asg_elements_v phaev,
pay_action_interlocks pai
WHERE pai.locked_action_id = phaev.assignment_action_id
AND pai.locking_action_id = ' || p_assignment_action_id || '
AND (phaev.classification_name like ''%''||''' || p_classification_name || ''' ||''%'')
GROUP BY phaev.element_reporting_name
ORDER BY phaev.element_reporting_name';
SELECT max(locked_action_id)
FROM pay_action_interlocks
WHERE locking_action_id = p_assignment_action_id;
SELECT ppa.date_earned, paa.tax_unit_id
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id;
g_sql := 'SELECT /*+ USE_NL(pbt) */ NVL(pbt.reporting_name, pbt.balance_name) COL04
, TO_CHAR(pay_balance_pkg.get_value( pdb_ptd.defined_balance_id
, ' || l_assignment_action_id || '
, ' || l_tax_unit_id || '
, NULL
, NULL
, NULL
, NULL
, fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
, NULL
, NULL)
, fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
, TO_CHAR(pay_balance_pkg.get_value( pdb_ytd.defined_balance_id
, ' || l_assignment_action_id || '
, ' || l_tax_unit_id || '
, NULL
, NULL
, NULL
, NULL
, fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
, NULL
, NULL)
, fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL18
FROM pay_balance_types pbt
, pay_balance_dimensions pbd_ptd
, pay_balance_dimensions pbd_ytd
, pay_defined_balances pdb_ptd
, pay_defined_balances pdb_ytd
WHERE pbt.balance_name IN (''GROSS_PAY''
, ''MANDATORY_DEDUCTIONS''
, ''INVOLUNTARY_DEDUCTIONS''
, ''VOLUNTARY_DEDUCTIONS''
, ''NET''
, ''DIRECT_PAYMENTS''
, ''TOTAL_PAYMENTS'')
AND pbd_ptd.dimension_name = '''|| l_dimension ||'''
AND pbd_ytd.dimension_name = ''_ASG_LE_YTD''
AND pbt.legislation_code = ''HK''
AND pbd_ptd.legislation_code = ''HK''
AND pbd_ytd.legislation_code = ''HK''
AND pbd_ptd.balance_dimension_id = pdb_ptd.balance_dimension_id
AND pbt.balance_type_id = pdb_ptd.balance_type_id
AND pbd_ytd.balance_dimension_id = pdb_ytd.balance_dimension_id
AND pbt.balance_type_id = pdb_ytd.balance_type_id
ORDER BY decode(pbt.balance_name, ''GROSS_PAY'',1
, ''MANDATORY_DEDUCTIONS'',2
, ''INVOLUNTARY_DEDUCTIONS'',3
, ''VOLUNTARY_DEDUCTIONS'',4
, ''NET'',5
, ''DIRECT_PAYMENTS'',6
, ''TOTAL_PAYMENTS'',7)';
g_sql := 'SELECT /*+
INDEX(opm PAY_ORG_PAYMENT_METHODS_F_PK) */
org_payment_method_name COL01
, pay_soe_util.getBankDetails('':legislation_code''
,ppm.external_account_id
,''BANK_NAME''
,NULL) COL02
, pay_soe_util.getBankDetails('':legislation_code''
,ppm.external_account_id
,''BANK_ACCOUNT_NUMBER''
,NULL) COL03
, TO_CHAR(:G_CURRENCY_CODE) COL04
, to_char(pp.value
,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
FROM pay_pre_payments pp
, pay_personal_payment_methods_f ppm
, pay_org_payment_methods_f opm
, pay_payment_types_tl pt
WHERE pp.assignment_action_id IN
(SELECT ai.locking_action_id
FROM pay_action_interlocks ai
WHERE ai.locked_action_id :action_clause)
AND pp.personal_payment_method_id = ppm.personal_payment_method_id(+)
AND :effective_date BETWEEN ppm.effective_start_date(+)
AND ppm.effective_end_date(+)
AND pp.org_payment_method_id = opm.org_payment_method_id
AND :effective_date BETWEEN opm.effective_start_date
AND opm.effective_end_date
AND opm.payment_type_id = pt.payment_type_id
AND pt.language = USERENV(''LANG'')';
SELECT MAX(locked_action_id)
FROM pay_action_interlocks
WHERE locking_action_id = p_assignment_action_id;
SELECT ppa.business_group_id,ppa.effective_date
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id;
'SELECT org.org_information7 COL02
, to_char(sum(prv.result_value)) COL16 /* BUG 5972299 */
FROM pay_run_result_values prv,
pay_run_results prr,
hr_organization_information_v org,
pay_input_values_f pivf
WHERE prr.status IN (''P'',''PA'')
AND org.organization_id = ' || l_business_group_id || '
AND org.org_information_context = ''Business Group:SOE Detail''
AND org.org_information1 = ''ELEMENT''
AND prv.run_result_id = prr.run_result_id
AND pivf.input_value_id = prv.input_value_id
AND ''' || l_effective_date || ''' between pivf.effective_start_date and pivf.effective_end_date
AND substr(pivf.uom,1,1) IN (''M'',''I'',''H'')
AND prr.assignment_action_id = ' || p_assignment_action_id || '
AND prr.element_type_id = org.org_information2
AND prv.input_value_id = org.org_information3
AND prv.result_value IS NOT NULL
group by prr.element_type_id,
org.org_information7
union all
SELECT org.org_information7 COL02
, to_char(prv.result_value) COL16
FROM pay_run_result_values prv,
pay_run_results prr,
hr_organization_information_v org,
pay_input_values_f pivf
WHERE prr.status IN (''P'',''PA'')
AND org.organization_id = ' || l_business_group_id || '
AND org.org_information_context = ''Business Group:SOE Detail''
AND org.org_information1 = ''ELEMENT''
AND prv.run_result_id = prr.run_result_id
AND pivf.input_value_id = prv.input_value_id
AND ''' || l_effective_date || ''' between pivf.effective_start_date and pivf.effective_end_date
AND substr(pivf.uom,1,1) NOT IN (''M'',''I'',''H'')
AND prr.assignment_action_id = ' || p_assignment_action_id || '
AND prr.element_type_id = org.org_information2
AND prv.input_value_id = org.org_information3
AND prv.result_value IS NOT NULL';
SELECT max(locked_action_id)
FROM pay_action_interlocks
WHERE locking_action_id = p_assignment_action_id;
SELECT ppa.date_earned, ppa.business_group_id, paa.tax_unit_id
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id;
g_sql := 'SELECT org.org_information7 COL02
, TO_CHAR(pay_balance_pkg.get_value( pdb.defined_balance_id
, ' || l_assignment_action_id || '
, ' || l_tax_unit_id || '
, NULL
, NULL
, NULL
, NULL
, fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
, NULL
, NULL)
,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
FROM pay_defined_balances pdb
, hr_organization_information_v org
WHERE org.organization_id = ' || l_business_group_id || '
AND org.org_information_context = ''Business Group:SOE Detail''
AND org.org_information1 = ''BALANCE''
AND pdb.balance_type_id = org.org_information4
AND pdb.balance_dimension_id = org.org_information5
AND pay_balance_pkg.get_value( pdb.defined_balance_id
, ' || l_assignment_action_id || '
, ' || l_tax_unit_id || '
, NULL
, NULL
, NULL
, NULL
, fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(l_date_earned) || ''')
, NULL
, NULL) <> 0';
SELECT pap.accrual_plan_name
,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
,hr_general.decode_lookup('HOURS_OR_DAYS',pap.accrual_units_of_measure)
,ppa.payroll_id
,pap.business_group_id
,pap.accrual_plan_id
,paa.assignment_id
FROM pay_accrual_plans pap
,pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE pet.element_type_id = pap.accrual_plan_element_type_id
AND pel.element_type_id = pet.element_type_id
AND pee.element_link_id = pel.element_link_id
AND paa.assignment_id = pee.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND pap.accrual_category = 'HKAL'
AND ppa.action_type IN ('R','Q')
AND ppa.action_status = 'C'
AND ppa.date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppa.date_earned BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND ppa.date_earned BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND paa.assignment_action_id = p_payroll_assignment_action_id;
SELECT ppa.date_earned
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = c_assignment_action_id;
SELECT MAX(locked_action_id)
FROM pay_action_interlocks
WHERE locking_action_id = p_assignment_action_id;
g_sql := 'SELECT ''' || l_plan_name || ''' COL01
, TO_CHAR(' || l_annual_leave_balance|| ',fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
, ''' || l_uom || ''' COL03
FROM DUAL';
SELECT MAX(locked_action_id)
FROM pay_action_interlocks
WHERE locking_action_id = p_assignment_action_id;
' SELECT pet.reporting_name COL01
,TO_CHAR(decode(pet.processing_type,''R'',greatest(pab.date_start,PTP.START_DATE),pab.date_start),''DD-Mon-YYYY'') COL02
,TO_CHAR(decode(pet.processing_type,''R'',least(pab.date_end,PTP.END_DATE),pab.date_end),''DD-Mon-YYYY'') COL03
,hr_general.decode_lookup(''HOURS_OR_DAYS'',pat.HOURS_OR_DAYS) COL04
,TO_CHAR(decode(pet.processing_type,''R'',to_number(prrv.result_value),nvl(pab.absence_days,pab.absence_hours))) COL16
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
,pay_run_result_values prrv
,per_time_periods ptp
,pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entries_f pee
,per_absence_attendance_types pat
,per_absence_attendances pab
WHERE paa.assignment_action_id = ' || p_assignment_action_id || '
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN (''Q'',''R'')
AND ptp.time_period_id = ppa.time_period_id
AND paa.assignment_action_id = prr.assignment_action_id
AND pet.element_type_id = prr.element_type_id
AND pet.element_type_id = piv.element_type_id
AND piv.input_value_id = pat.input_value_id
AND pat.absence_attendance_type_id = pab.absence_attendance_type_id
AND pab.absence_attendance_id = pee.creator_id
AND pee.creator_type = ''A''
AND pee.assignment_id = paa.assignment_id
AND pee.element_entry_id = prr.source_id
AND piv.input_value_id = prrv.input_value_id
AND prr.run_result_id = prrv.run_result_id
AND ppa.effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppa.effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND ppa.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date';