[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
function. Deleted gv_title heading
which was not being used.
17-DEC-2003 ssattini 115.20 3316062 Modified the cursor c_assignments to
to fix the bug#3316062, corrected
paa_key inline view to pick up
T4A Employee Payments also.
08-JAN-2004 ssattini 115.21 3359412 Modified the cursor c_assignments to
to fix the 11510 performance bug#3359412.
04-MAR-2004 ssattini 115.22 3479270 Modified the cursor c_assignments to
to fix the bug#3479270, corrected
paa_key inline view to avoid duplicate
payment records.
23-MAR-2004 ssattini 115.23 3517534 Modified the cursor c_assignments to
to fix the bug#3517534, corrected
parameter values validation.
02-May-2006 ssmukher 115.24 5178951 Added a new column to display whether
the Cheque/Third Party cheque/Deposit Advice has been voided.
Modified the procedure payment_extract.Added a new cursor
c_payment_status.
03_May-2006 ssmukher 115.25 5178951 Removed the effective date check from the
cursor c_payment_status.
16-May-2006 ydevi 115.26 5225939 Modified the code to get the check number and direct deposit
number printed for voided payments too in the payment report
26-Sep-2006 schowta 115.27 5383895 - Following modifications are done. search for 5383895
for all the changes done.
a. pay_ca_payreg_extract_pkg > c_payment_period and
other cursors the join to date_earned has been changed to effective_date
in all instances except in c_payroll_paydate
cursor c_tp_pmt_check is merged with c_assignments cursor.
c_assignments cursor is modified to include ,popm.defined_balance_id.
Associated open cursor is modified to check if it is null.
13-Nov-2006 schowta 115.28 Line No. 196 - Observed that "" was missing. Modified to " "
*/
/************************************************************
** Local Package Variables
************************************************************/
gc_csv_delimiter VARCHAR2(1) := ',';
select 1 ord, meaning
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and ( ( p_person_language is null and language = 'US' ) or
( p_person_language is not null and language = p_person_language ) )
union all
select 2 ord, meaning
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and ( language = 'US' and p_person_language is not null
and language <> p_person_language )
order by 1;
** can insert data and the label in this PL/SQL table which will
** be printed at the end of the report.
** The PL/SQL table which needs to be populated is
** LTT_PAYMENT_EXTRACT_DATA. This PL/SQL table is defined in the
** Package pay_ca_payreg_extract_data_pkg (pycaprpd.pkh/pkb).
*****************************************************************/
PROCEDURE formated_static_header(
p_output_file_type in varchar2
,p_static_label1 out NOCOPY varchar2
,p_static_label2 out NOCOPY varchar2
)
IS
lv_format1 varchar2(32000);
select legislation_code into lv_leg_code
from per_business_groups
where business_group_id = gv_business_group_id;
** can insert data and the label in this PL/SQL table which will
** be printed at the end of the report.
** The PL/SQL table which needs to be populated is
** LTT_PAYMENT_EXTRACT_DATA. This PL/SQL table is defined in the
** Package pay_ca_payreg_extract_data_pkg (pycaprpd.pkh/pkb).
*****************************************************************/
/* Added two columns p_case_number, p_payee_name to format the
Third Party Payments, to fix bug#2745577 */
PROCEDURE formated_static_data(
p_employee_full_name in varchar2
,p_employee_number in varchar2
,p_payment_type in varchar2
,p_payment_number in varchar2
,p_bank_number_bank_name in varchar2
,p_transit_code in varchar2
,p_account_number in varchar2
,p_payment_amount in varchar2
,p_payroll_name in varchar2
,p_gre_name in varchar2
,p_period in varchar2
,p_payment_method in varchar2
,p_pay_date in varchar2
,p_case_number in varchar2
,p_payee_name in varchar2
,p_payment_status in varchar2
,p_output_file_type in varchar2
,p_static_data1 out NOCOPY varchar2
,p_static_data2 out NOCOPY varchar2
)
IS
lv_format1 VARCHAR2(32000);
** Assignment Action for the selection parameters entered by the
** user in the SRS. The Assignment Action returned by this cursor
** is used to retreive the Payment Number and Period Name.
************************************************************/
cursor c_assignments (
cp_start_date in date
,cp_end_date in date
,cp_payroll_id in number default NULL
,cp_consolidation_set_id in number
,cp_payment_type_id in number default NULL
,cp_tax_unit_id in number default NULL
,cp_payment_method_id in number default NULL
,cp_business_group_id in number
) is
select hou.name
,paa_key.tax_unit_id
,ppf.full_name
,ppf.employee_number
,paf.assignment_number
,ppt_tl.payment_type_name
,ppp.value /* Payment Amount */
,ppp.personal_payment_method_id
,popm.org_payment_method_id
,popm_tl.org_payment_method_name
,ppf.person_id
,pap.payroll_name
,ppp.pre_payment_id
,paa_key.assignment_action_id
,paa_key.date_earned
,paa_key.effective_date /* BUG: 5383895 paa_key.date_earned changed to paa_key.effective_date */
,popm.defined_balance_id /* BUG: 5383895 added to avoid the cursor c_tp_pmt_check */
from per_all_people_f ppf
,per_all_assignments_f paf
,hr_all_organization_units_tl hou
,pay_all_payrolls_f pap
,pay_payment_types_tl ppt_tl
,pay_payment_types ppt
,pay_org_payment_methods_f_tl popm_tl
,pay_org_payment_methods_f popm
,pay_pre_payments ppp
,(select distinct paa_pre.assignment_action_id /* Locked Action Id */
,paa_run.tax_unit_id
,ppa_pre.date_earned
,paa_pre.assignment_id
,ppa_pre.payroll_id
,ppa_pre.effective_date /* BUG: 5383895 added ppa_pre.effective_date */
from pay_run_types_f prt
,pay_assignment_actions paa_run
,pay_action_interlocks pai
,pay_assignment_actions paa_pre
,pay_payroll_actions ppa_pre
where ppa_pre.business_group_id = cp_business_group_id
and ppa_pre.effective_date between cp_start_date and cp_end_date /* BUG: 5383895 ppa_pre.date_earned changed to ppa_pre.effective_date */
and ppa_pre.action_status = 'C'
and ppa_pre.action_type in ('U','P')
and ((ppa_pre.consolidation_set_id = cp_consolidation_set_id) OR
(cp_consolidation_set_id is NULL))
and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
and paa_pre.action_status = 'C'
and pai.locking_action_id = paa_pre.assignment_action_id
and paa_run.assignment_action_id = pai.locked_action_id
and ((paa_run.tax_unit_id = cp_tax_unit_id) OR
(cp_tax_unit_id is NULL))
and paa_run.action_status = 'C'
and paa_run.run_type_id is not NULL
and prt.run_type_id = paa_run.run_type_id
and prt.run_method <> 'C'
) paa_key
where pap.business_group_id = cp_business_group_id
and pap.payroll_id = paa_key.payroll_id
and paa_key.effective_date between pap.effective_start_date
and pap.effective_end_date /* BUG: 5383895 paa_key.date_earned changed to paa_key.effective_date */
and ((pap.payroll_id = cp_payroll_id) OR
(cp_payroll_id is NULL))
and ppp.assignment_action_id = paa_key.assignment_action_id
and ppp.org_payment_method_id = popm.org_payment_method_id
and paa_key.effective_date between popm.effective_start_date and
popm.effective_end_date /* BUG: 5383895 paa_key.date_earned changed to paa_key.effective_date */
and popm.business_group_id = cp_business_group_id
and ((popm.org_payment_method_id = cp_payment_method_id) OR
(cp_payment_method_id is NULL))
and popm.org_payment_method_id = popm_tl.org_payment_method_id
and popm_tl.language = userenv('LANG')
and ppt.payment_type_id = popm.payment_type_id
and ppt.payment_type_id = ppt_tl.payment_type_id
and ppt_tl.language = userenv('LANG')
and ((ppt.payment_type_id = cp_payment_type_id) OR
(cp_payment_type_id is NULL))
and hou.organization_id = paa_key.tax_unit_id
and hou.language = userenv('LANG')
and paf.assignment_id = paa_key.assignment_id
and paa_key.effective_date between paf.effective_start_date and
paf.effective_end_date /* BUG: 5383895 paa_key.date_earned changed to paa_key.effective_date */
and paf.person_id = ppf.person_id
and paa_key.effective_date between ppf.effective_start_date and
ppf.effective_end_date /* BUG: 5383895 paa_key.date_earned changed to paa_key.effective_date */
order by ppt_tl.payment_type_name,popm_tl.org_payment_method_name,
ppf.full_name;
select pay_us_employee_payslip_web.get_check_number(cp_assignment_action_id
,cp_pre_payment_id),/*check_no*/
/* nvl(to_number(paa.serial_number),paa.assignment_action_id),*/
paa.assignment_action_id,
paa.payroll_action_id,
paa.assignment_id,
ppa.effective_date
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locked_action_id = cp_assignment_action_id
and pai.locking_action_id = paa.assignment_action_id
and paa.action_status = 'C'
and paa.pre_payment_id = cp_pre_payment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.effective_date between cp_start_date and cp_end_date /* BUG: 5383895 ppa.date_earned changed to ppa.effective_date */
and ppa.business_group_id = cp_business_group_id;
select decode(gv_leg_code,'CA',
decode(pea.segment7,NULL,' ',
rtrim(substrb(pea.segment7,1,150))), 'US',--bug 2254026
decode(pea.segment5,NULL,' ',
rtrim(substrb(pea.segment5,1,150))))
/*Per'l Payment Method Bank_number for CA, Bank Name for US */
,pea.segment4 /* Per'l Payment Method Transit_code */
,pea.segment3 /* Per'l Payment Method Account_Number */
from pay_personal_payment_methods_f pppm /*added newly to fix bug#2133040 */
,pay_external_accounts pea
where pppm.personal_payment_method_id = cp_personal_paymeth_id
and cp_effective_date between pppm.effective_start_date and
pppm.effective_end_date /* BUG: 5383895 cp_date_earned changed to cp_effective_date */
and pppm.external_account_id = pea.external_account_id(+);
select ppa.effective_date,ptp.period_name
from per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = cp_assignment_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.action_status = 'C'
and paa.run_type_id is not null
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('Q','R')
and ppa.business_group_id = cp_business_group_id
and ptp.payroll_id = ppa.payroll_id
and ppa.date_earned between ptp.start_date and ptp.end_date;
select peev.screen_entry_value
from
pay_element_entry_values_f peev,
pay_input_values_f piv_att,
pay_element_entries_f peef,
pay_element_types_f pet
where peef.assignment_id = cp_asg_id
AND EXISTS (select null from pay_element_links_f pelf
where pelf.element_link_id= peef.element_link_id
and pelf.element_type_id = pet.element_type_id
and cp_effective_date between
pelf.effective_start_date and pelf.effective_end_date /* BUG: 5383895 date_earned changed to effective_date */
and cp_effective_date between
pet.effective_start_date and pet.effective_end_date /* BUG: 5383895 date_earned changed to effective_date */
AND pet.third_party_pay_only_flag = 'Y')
AND cp_effective_date between
peef.effective_start_date and peef.effective_end_date /* BUG: 5383895 date_earned changed to effective_date */
AND pet.element_type_id = piv_att.element_type_id
AND upper(piv_att.name) = 'ATTACHMENT NUMBER'
AND cp_effective_date between
piv_att.effective_start_date and piv_att.effective_end_date /* BUG: 5383895 date_earned changed to effective_date */
AND peef.element_entry_id = peev.element_entry_id
AND piv_att.input_value_id = peev.input_value_id
AND cp_effective_date between
peev.effective_start_date and peev.effective_end_date /* BUG: 5383895 date_earned changed to effective_date */
AND peef.personal_payment_method_id = cp_persnl_pmt_meth_id
AND peef.entry_information22 = cp_pmt_amount;
select 'Y'
from pay_org_payment_methods_f
where org_payment_method_id = cp_org_pmt_method_id
and defined_balance_id is null;
select pppm.payee_type,pppm.payee_id
from pay_personal_payment_methods_f pppm
where pppm.personal_payment_method_id = cp_personal_paymeth_id
and cp_effective_date between pppm.effective_start_date and
pppm.effective_end_date; /* BUG: 5383895 cp_date_earned changed to cp_effective_date */
select name from hr_all_organization_units_tl
where organization_id = cp_payee_id
and language = userenv('LANG');
select initcap(rtrim(ppf.title))||' '||rtrim(ppf.first_name)||' '||rtrim(ppf.last_name)
from per_all_people_f ppf
where ppf.person_id = cp_payee_id
and cp_effective_date between ppf.effective_start_date and
ppf.effective_end_date; /* BUG: 5383895 cp_date_earned changed to cp_effective_date */
SELECT void_pa.effective_date
FROM pay_assignment_actions chq_or_mag_aa,
pay_action_interlocks,
pay_assignment_actions void_aa,
pay_payroll_actions void_pa
WHERE chq_or_mag_aa.payroll_action_id = p_payact_id
AND ((fnd_number.canonical_to_number(chq_or_mag_aa.serial_number)
= p_chkno) OR ( p_chkno is NULL))
AND locked_action_id = chq_or_mag_aa.assignment_action_id
AND locking_action_id = void_aa.assignment_action_id
AND void_pa.payroll_action_id = void_aa.payroll_action_id
AND void_pa.action_type = 'D';
select decode(ppa_pymt.action_type,
'M', to_char(NVL(ppp.source_action_id,cp_pre_payment_action)),
paa_pymt.serial_number)
from pay_pre_payments ppp,
pay_assignment_actions paa_pymt,
pay_payroll_actions ppa_pymt,
pay_action_interlocks pai
where pai.locked_action_id = cp_pre_payment_action
and paa_pymt.assignment_action_id = pai.locking_action_id
and ppa_pymt.payroll_action_id = paa_pymt.payroll_action_id
and ppa_pymt.action_type in ('M','H', 'E')
and paa_pymt.pre_payment_id = cp_pre_payment_id
and ppp.pre_payment_id = paa_pymt.pre_payment_id;
update fnd_concurrent_requests
set output_file_type = 'HTML'
where request_id = FND_GLOBAL.CONC_REQUEST_ID ;