[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
11-Feb-2000 A.Parkes 110.1 1178972 Changed select of gross_pay
in emps_cur cursor.
29-Feb-2000 A.Mills 115.0 forward ported.
22-Mar-2000 A.Mills 115.1 1232417 Expanded error message size
for bug fix of eoy process.
Using DBI for error message.
31-Mar-2000 A.Parkes 115.2 1232417 Allow length(tax_code) <= 7
and smp <= 99999999
17-Apr-2000 A.Mills 115.3 1265531 Changed emps_cur to ensure
Middle name is 7 chars
in the Magtape.
12-Jun-2000 A.Blinko 115.4 1268568 Now processes assignments with
>5 NI categories correctly.
13-Jul-2000 A.Mills 115.5 1364509 Add EET, Student Loans,
=110.6 Tax Credits and Ees Rebate
to outputs to MAG_RECORD2 and 4.
Altered validation of name fields
removed SCON checking in main
procedure, now validated in the
formula using call to new
generic validate function.
02-Aug-2000 A.Mills 115.6 Fixed minor conversion error
found in unit testing.
07-Sep-2000 A.Parkes 115.7 Fixed magtape validation so
+ is disallowed.
Added EDI validation checks.
19-Oct-2000 A.Mills 115.8 Performance tune for emps_cur.
NB more substr etc formatting
inline with 10.7 code, this
speeds up code due to reduced
sort key.
16-Feb-2001 A.Parkes 115.9 Allow = in EDI charset
13-Mar-2001 A.Parkes 115.10 1682586 Changed header_cur subquery
to filter on char payroll_ids
Also removed 'Dan Tow Decode'.
18-Sep-2001 A.Mills 115.11 1778139 Added Assignment Message for
asgs that have been updated
during the run (warnings).
20-Sep-2001 R. Makhija 115.12 1585510 Removed references to EET
1802363 balance values, changed emps_cur
to select full assignment_number,
increased length of number
variabes.
19-Oct-2001 K.Thampan 115.13 Put blank into tax code field of
mag record row two
17-DEC-2001 R.Makhija 115.14 Increased length of student
loan variables
18-Nov-2001 R.Makhija 115.15 Added P14 EDI functionality
09-JAN-2002 R.Makhija 115.16 Added Checkfile commands
29-JAN-2002 R.Makhija 115.17 Added 'SET VARIFY OFF' at the
beginning to fix GSCC warning.
11-FEB-2002 R.Makhija 115.18 Added 2 more parameters for
EDI EMP HEADER formula to
pass middle name and
and title of an employee.
08-MAY-2002 A.Mills 115.19 Aggregated PAYE changes. Skip
the employee type 2 record if
all balances are zero, must be
aggregated.
19-jul-2002 Vimal 115.21 Fixes bug 2392279. The chanegs added
to version 20 does not work as the pkg
fails to compile on UTF8 database.
So the fix was to chaneg the variable
declaration of the address line
to size greater than 27. Some other
variable were also changed so that
the process does not fail bcos of this
error again.
05-DEC-2002 V.Vinod 115.22 2696015 P14 EDI Enhancement for Year 2003
13-SEP-2003 npershad 115.25 3133921 P14 EDI/P35 MT Functional Changes
for End of Year 2003/2004
24-MAR-2004 A.Mills 115.26 3527428 Fixed header_cur to ensure that
Tax District Ref is 3 characters,
issue found in P14EDI with short
Tax Dist Ref No.
10-MAY-2004 npershad 115.27 3614251 Added nvl call in cursor emps_cur,
for field X_SUPERANNUATION_PAID.
21-OCT-2004 rmakhija 115.28 3962706 Changed cursor emps_cur to suppress
21-OCT-2004 rmakhija 115.28 3962706 Changed cursor emps_cur to suppress
21-OCT-2004 rmakhija 115.28 3962706 Changed cursor emps_cur to suppress
secondary aggregated asssignments.
15-NOV-2004 rmakhija 115.29 4011263 P14 EDI Changes for 2004-2005.
07-DEC-2004 rmakhija 115.30 4011263 Added coomit and exit at the end
21-JAN-2005 rmakhija 115.31 4108896 Added new validations for First, Last
and Middle name in validate_input
function. Also changed emp_values
cursor to select only non-zero
NI records.
01-MAR-2005 rmakhija 115.32 4216135 Changed emp_values to make sure
atleast NI Cat X is reported
when there is not enough earning
therefore NI balances are 0
11-MAR-2005 rmakhija 115.33 4234348 Changed emp_values to make sure
Ni Cats with 0 lel/et/uel are
processed first so that contribs
in these NI Cats can be rolled up
into another NI Cat
19-MAY-2005 rmakhija 115.34 4362883 Change submit_reports to set
printer and copies oprions
as entered by the user on EOY
request before submitting the
reports.
09-JUN-2005 rmakhija 115.35 Added nvl,ltrim and rtrim around
first_name, middle_names, title
and country to handle spaces in
these fields as null values.
16-JUN-2005 rmakhija 115.36 Increased length of some number
variables in this package so
pl/sql error is not raised by
this package when value is too
large but a user friendly error
message will be raised by the EOY
formula
14-Nov-2005 rmakhija 115.37 Changed for EOY 2005-06
01-Dec-205 mgera 115.38 Added extra validation for SCON check.
in validate_input function
02-Dec-2005 rmakhija 115.39 Further changes for EOY 2005-06
10-JAN-2005 rmakhija 115.40 Further changes for EOY 2005-06
08-FEB-2006 kthampan 115.41 Added validation for P11D_EDI
08-DEC-2006 rmakhija 115.42 EOY 2006-07 changes
21-JAN-2007 rmakhija 115.43 Excluded NI Cat C from aggregated
validations. Also added sum of
total contributions as a
parameter to the P14 emp trailer
formula.
25-Nov-2007 A.Ganguly 115.44 Added validate_tax_code_1
29-Oct-2007 pbalu 115.45 6281170 Added a new parameter for Formula
PAY_GB_EDI_P14_EMP_TRAILER
29-Oct-2007 rlingama 115.46 5671777 BUG 5671777-5 Changed Start date of the EOY process
to reflect start of the tax year.so no need to add
12 months to the start date
2-Nov-2007 parusia 115.47 6345375 Included 2 additional validation modes for
in validate_input function for validating Last_name
and First_name in P45(3) and P46 PENNOT
13-Nov-2007 A.Ganguly 115.48 6345375 Added function get_payroll_version
for the EOY Apr 08 Changes
26-Nov-2007 parusia 115.49 6345375 Added validation modes in validate_input()
for PostalCode and Title.
Added code to remove leading minus
sign from NUMBER_1 validate_mode.
28-Nov-2007 parusia 115.50 6345375 Remove hardcoded 'apps' from csr_get_version
as it was failing in GSCC checks.
30-Nov-2007 parusia 115.51 6345375 Removed numbers from valid character set for
P45_46_FIRST_NAME, P45_46_LAST_NAME, P45_46_TITLE
30-NOV-2007 pbalu 115.52 6281170 To change the condition for contribution
rollup and LEL rollup as part of EOY 07/08
*/
fetch_new_header BOOLEAN := TRUE; -- Shows if new header record needed
SELECT UPPER(a.permit_number)
,a.payroll_id
,lpad(TO_CHAR(a.tax_district_reference),3,'0')
,a.tax_reference_number
,NVL(TO_CHAR(a.tax_year),' ') -- 4011263
,a.employers_name
/* 4752018 - EOY 2005-06
,a.employers_address_line
4752018 */
/* Start 4011263
,UPPER(NVL(a.econ,'?'))
,nvl(a.ssp_recovered,0)
,nvl(a.smp_recovered,0)
,nvl(a.smp_compensation,0)
--Added the below four fields for P35/P14 EOY 2003/2004
,nvl(a.spp_recovered,0)
,nvl(a.spp_compensation,0)
,nvl(a.sap_recovered,0)
,nvl(a.sap_compensation,0)
End 4011263 */
FROM pay_gb_year_end_payrolls_v a
WHERE a.payroll_action_id = c_payroll_action_id
AND EXISTS (SELECT '1'
FROM pay_assignment_actions paa,
ff_user_entities fue,
ff_archive_items fai
WHERE paa.payroll_action_id = a.payroll_action_id
AND fue.user_entity_name = 'X_PAYROLL_ID'
AND fai.user_entity_id = fue.user_entity_id
AND fai.context1 = paa.assignment_action_id
AND fai.value = to_char(a.payroll_id))
ORDER BY a.tax_district_reference, a.tax_reference_number, a.permit_number,a.payroll_id;
SELECT
max(decode(fue2.user_entity_name,'X_ASSIGNMENT_NUMBER',
fai2.VALUE))
,act.assignment_action_id
,nvl(max(decode(fue2.user_entity_name,'X_LAST_NAME',
substr(fai2.value,1,35))),' ') LAST_NAME
,nvl(max(decode(fue2.user_entity_name,'X_FIRST_NAME',
SUBSTR(ltrim(rtrim(fai2.value)),1,35))), ' ') FIRST_NAME
,nvl(max(decode(fue2.user_entity_name,'X_MIDDLE_NAME', SUBSTR(ltrim(rtrim(fai2.value)),1,35))), ' ')
,nvl(max(decode(fue2.user_entity_name,'X_TITLE', SUBSTR(ltrim(rtrim(fai2.value)),1,35))), ' ')
,nvl(max(decode(fue2.user_entity_name,'X_DATE_OF_BIRTH',
TO_CHAR(fnd_date.canonical_to_date(fai2.value),'DDMMYYYY'))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_SEX', substr(UPPER(fai2.value),1,1))),' ')
,nvl(ltrim(max(decode(fue2.user_entity_name,'X_ADDRESS_LINE1',
decode(fai2.value,'','',rpad(fai2.value,35))))), ' ')
,nvl(ltrim(max(decode(fue2.user_entity_name,'X_ADDRESS_LINE2',
decode(fai2.value,'','',rpad(fai2.value,35))))), ' ')
,nvl(ltrim(max(decode(fue2.user_entity_name,'X_ADDRESS_LINE3',
decode(fai2.value,'','',rpad(fai2.value,35))))), ' ')
,nvl(ltrim(max(decode(fue2.user_entity_name,'X_TOWN_OR_CITY',
decode(fai2.value,'','',rpad(fai2.value,35))))), ' ')
,nvl(max(decode(fue2.user_entity_name,'X_COUNTRY', -- 4011263
decode(fai2.value,'','',rpad(ltrim(rtrim(fai2.value)),27)))), ' ')
,nvl(max(decode(fue2.user_entity_name,'X_POSTAL_CODE',
substr(fai2.value,1,9))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_TAX_CODE',
ltrim(rtrim(UPPER(fai2.value))))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_W1_M1_INDICATOR',
substr(UPPER(fai2.value),1,1))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',
substr(UPPER(fai2.value),1,9))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_SSP', to_number(fai2.value))),0)
,nvl(max(decode(fue2.user_entity_name,'X_SMP', to_number(fai2.value))),0)
-- Added the below 2 fields for P35/P14 EOY 2003/2004
,nvl(max(decode(fue2.user_entity_name,'X_SPP_ADOPT', to_number(fai2.value))),0) -- for SPP
,nvl(max(decode(fue2.user_entity_name,'X_SPP_BIRTH', to_number(fai2.value))),0) -- for SPP
,nvl(max(decode(fue2.user_entity_name,'X_SAP', to_number(fai2.value))),0) -- for SAP
/*4011263: Gross Pay not needed anymore
,nvl(max(decode(fue2.user_entity_name,'X_GROSS_PAY',to_number(fai2.VALUE))),0) gross_pay
*/
--
,decode(max(decode(fue2.user_entity_name,'X_TAX_REFUND',substr(fai2.VALUE,1,1))), 'R',
NVL(-1*max(decode(fue2.user_entity_name,'X_TAX_PAID',to_number(fai2.VALUE))),0),
NVL(max(decode(fue2.user_entity_name,'X_TAX_PAID',to_number(fai2.VALUE))),0)) tax_paid
--
,nvl(max(decode(fue2.user_entity_name,'X_TAX_REFUND',
substr(UPPER(fai2.value),1,1))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_PREVIOUS_TAXABLE_PAY',
to_number(fai2.value))),0) previous_taxable
,nvl(max(decode(fue2.user_entity_name,'X_PREVIOUS_TAX_PAID', to_number(fai2.value))),0)
,nvl(max(decode(fue2.user_entity_name,'X_START_OF_EMP',
TO_CHAR(fnd_date.canonical_to_date(fai2.value),'DDMMYYYY'))),' ')
,max(decode(fue2.user_entity_name,'X_TERMINATION_DATE',
TO_CHAR(fnd_date.canonical_to_date(fai2.value),'DDMMYYYY')))
,nvl(max(decode(fue2.user_entity_name,'X_WIDOWS_AND_ORPHANS',
ROUND(to_number(fai2.value)/100))),0)
,nvl(max(decode(fue2.user_entity_name,'X_STUDENT_LOANS', trunc(fai2.value/100))),0) student_loans
,nvl(max(decode(fue2.user_entity_name,'X_WEEK_53_INDICATOR',
substr(UPPER(fai2.value),1,1))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_TAXABLE_PAY', to_number(fai2.value))),0) taxable_pay
/* 4011263
,nvl(max(decode(fue2.user_entity_name,'X_PENSIONER_INDICATOR',
substr(UPPER(fai2.value),1,1))),' ')
4011263 */
,nvl(max(decode(fue2.user_entity_name,'X_DIRECTOR_INDICATOR',
substr(UPPER(fai2.value),1,1))),' ')
,act.assignment_id
,max(decode(fue2.user_entity_name,'X_EFFECTIVE_END_DATE',
fnd_date.canonical_to_date(fai2.value)))
,nvl(max(decode(fue2.user_entity_name,'X_ASSIGNMENT_MESSAGE',
SUBSTR(fai2.VALUE, 1,60))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_MULTIPLE_ASG_FLAG',
SUBSTR(fai2.VALUE, 1,1))),' ')
FROM
ff_archive_items fai1,
ff_user_entities fue1,
ff_archive_items fai2,
ff_user_entities fue2,
pay_assignment_actions act
WHERE act.assignment_action_id = fai1.context1
AND act.payroll_action_id = c_payroll_action_id
AND act.action_status = 'C'
AND fue1.legislation_code = 'GB'
AND fue1.user_entity_name = 'X_PAYROLL_ID'
AND fue1.business_group_id IS NULL
AND fue1.user_entity_id + decode(act.assignment_action_id,0,0,0) = fai1.user_entity_id
and fai1.value = to_char(c_payroll_id)
AND fue2.user_entity_id = fai2.user_entity_id
AND fai2.context1 = act.assignment_action_id
GROUP BY
act.assignment_action_id
, act.assignment_id
HAVING
(
nvl(max(decode(fue2.user_entity_name,'X_AGGREGATED_PAYE_FLAG', fai2.value)), 'N')='N'
OR (
nvl(max(decode(fue2.user_entity_name,'X_AGGREGATED_PAYE_FLAG', fai2.value)), 'N')='Y'
AND nvl(max(decode(fue2.user_entity_name,'X_EOY_PRIMARY_FLAG', fai2.value)), 'N')='Y'
)
)
AND
(
nvl(max(decode(fue2.user_entity_name, 'X_TAXABLE_PAY', to_number(fai2.value))),0) <> 0
OR NVL(max(decode(fue2.user_entity_name,'X_TAX_PAID',to_number(fai2.VALUE))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_STUDENT_LOANS', trunc(fai2.value/100))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_PREVIOUS_TAXABLE_PAY', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_PREVIOUS_TAX_PAID', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_SSP', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_SMP', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_SAP', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_SPP_ADOPT', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_SPP_BIRTH', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_REPORTABLE_NI', fai2.value)),'N') <> 'N'
)
ORDER BY last_name, first_name;
SELECT NVL(UPPER(a.scon),' ') scon
,UPPER(a.ni_category_code) cat_code
FROM pay_gb_year_end_values_v a
WHERE a.assignment_action_id = c_assignment_action_id
AND a.reportable <> 'N'
AND NVL(trunc(a.ni_able_uel/100),0) > 0
AND NVL(a.employees_contributions,0) > 0
AND UPPER(a.ni_category_code) <> 'X'
AND UPPER(a.ni_category_code) <> 'C'
ORDER BY NVL(trunc(a.ni_able_uel/100),0), NVL(a.employees_contributions,0),
UPPER(a.ni_category_code), NVL(UPPER(a.scon),' ') DESC;
SELECT UPPER(a.ni_category_code) cat_code
FROM pay_gb_year_end_values_v a
WHERE a.assignment_action_id = c_assignment_action_id
AND a.reportable <> 'N'
AND NVL(trunc(a.ni_able_et/100),0) > 0
AND NVL(trunc(a.ni_able_lel/100),0) > 0
AND UPPER(a.ni_category_code) <> 'X'
ORDER BY NVL(trunc(a.ni_able_et/100),0),
NVL(trunc(a.ni_able_lel/100),0),
NVL(a.employees_contributions,0),
UPPER(a.ni_category_code), NVL(UPPER(a.scon),' ') DESC;
SELECT NVL(sum(trunc(a.ni_able_lel/100)),0) tot_ni_able_lel
FROM pay_gb_year_end_values_v a
WHERE a.assignment_action_id = c_assignment_action_id
AND a.reportable <> 'N'
AND UPPER(a.ni_category_code) <> 'X'
-- Check LEL > 0 but ET, UEL, EE and ER Conrib = 0
--AND NVL(a.total_contributions,0) = 0 EOY 07/08 removed Total contribution from LEL roll up
AND NVL(trunc(a.ni_able_et/100),0) = 0
AND NVL(trunc(a.ni_able_uel/100),0) = 0
--AND NVL(a.employees_contributions,0) = 0 EOY 07/08 removed Employee contribution from LEL roll up
AND NVL(trunc(a.ni_able_lel/100),0) > 0;
SELECT NVL(UPPER(a.scon),' ') scon
,UPPER(a.ni_category_code) cat_code
,NVL(a.total_contributions,0) tot_cont
,NVL(a.employees_contributions,0) emps_cont
,NVL(trunc(a.ni_able_et/100),0) ni_able_et
,NVL(trunc(a.ni_able_lel/100),0) ni_able_lel
,NVL(trunc(a.ni_able_uel/100),0) ni_able_uel
,NVL(trunc(a.ni_able_auel/100),0) ni_able_auel --EOY 07/08 added AUEL for contributions rollup
,NVL(a.employers_rebate,0) employers_rebate
,NVL(a.employees_rebate,0) employees_rebate
FROM pay_gb_year_end_values_v a
WHERE a.assignment_action_id = c_assignment_action_id
AND a.reportable <> 'N'
-- Check atleast one value is non-zero to report
AND NOT (NVL(a.total_contributions,0) = 0
AND NVL(trunc(a.ni_able_et/100),0) = 0
AND NVL(trunc(a.ni_able_lel/100),0) = 0
AND NVL(trunc(a.ni_able_uel/100),0) = 0
AND NVL(a.employees_contributions,0) = 0)
UNION -- Added union to fix 4216135
SELECT ' '
,'X'
,0
,0
,0
,0 --EOY 07/08
,0
,0
,0
,0
FROM dual
WHERE NOT EXISTS
(SELECT 1 FROM pay_gb_year_end_values_v b
WHERE b.assignment_action_id = c_assignment_action_id
AND b.reportable <> 'N'
AND NOT (NVL(b.total_contributions,0) = 0
AND NVL(trunc(b.ni_able_et/100),0) = 0
AND NVL(trunc(b.ni_able_lel/100),0) = 0
AND NVL(trunc(b.ni_able_uel/100),0) = 0
AND NVL(b.employees_contributions,0) = 0))
ORDER BY 6, 5, 7, 2, 1; -- order by clause added for 4234348 to ensure Ni Cats with 0 lel/et/uel are processed first
SELECT 1
FROM ff_archive_item_contexts fac,
ff_archive_items fai,
ff_user_entities fue,
ff_archive_items fai2,
ff_user_entities fue2,
pay_assignment_actions paa
WHERE paa.payroll_action_id = c_payroll_action_id
AND fue.user_entity_name = 'X_NI_TOTAL_CONTRIBUTIONS'
AND fue.user_entity_id + decode(paa.assignment_action_id,0,0,0)
= fai.user_entity_id
AND fue.legislation_code = 'GB'
AND fai.context1 = paa.assignment_action_id
AND fai.archive_item_id = fac.archive_item_id
AND fac.sequence_no = 2
AND fac.context in ('D','E','L') --P35/P14 EOY 2003/2004
AND fue2.user_entity_name = 'X_PAYROLL_ID'
AND fue2.user_entity_id + decode(paa.assignment_action_id,0,0,0)
= fai2.user_entity_id
AND fue2.legislation_code = 'GB'
AND fai2.context1 = paa.assignment_action_id
AND decode (c_tax_dist_ref,NULL,1,
pay_gb_eoy_archive.get_arch_num(c_payroll_action_id,
'X_TAX_DISTRICT_REFERENCE',fai2.value),1,0) = 1
AND decode (c_tax_ref_no,NULL,1,
pay_gb_eoy_archive.get_arch_str(c_payroll_action_id,
'X_TAX_REFERENCE_NUMBER',fai2.value),1,0) = 1
AND decode (c_permit_no,NULL,1,
pay_gb_eoy_archive.get_arch_str(c_payroll_action_id,
'X_PERMIT_NUMBER',fai2.value),1,0) = 1;
SELECT printer, number_of_copies
FROM fnd_concurrent_requests
WHERE request_id = fnd_global.conc_request_id;
SELECT printer, number_of_copies
FROM fnd_concurrent_requests
WHERE request_id = fnd_global.conc_request_id;
SELECT a.formula_id
FROM ff_formulas_f a,
ff_formula_types t
WHERE a.formula_name = p_formula_name
AND a.formula_type_id = t.formula_type_id
AND t.formula_type_name = 'Oracle Payroll';
SELECT upper(nvl(org_information11,' ')) edi_sender_id,
pact.request_id
FROM pay_payroll_actions pact,
hr_organization_information hoi
WHERE pact.payroll_action_id = p_payroll_action_id
AND hoi.org_information_context = 'Tax Details References'
AND hoi.org_information1 = g_tax_district_ref||'/'||g_tax_ref_no
AND hoi.organization_id = pact.business_group_id;
SELECT nvl(org_information13,' ') edi_submitter_no
FROM pay_payroll_actions pact,
hr_organization_information hoi
WHERE pact.payroll_action_id = p_payroll_action_id
AND hoi.org_information_context = 'Tax Details References'
AND hoi.org_information1 = g_tax_district_ref||'/'||g_tax_ref_no
AND hoi.organization_id = pact.business_group_id;
select report_category
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id;
select '1' from dual where exists
(select action_status
from pay_assignment_actions
where payroll_action_id = c_payroll_action_id
and action_status = 'E');
SELECT to_date('06/04/'||to_char(start_date,'YYYY'),'dd/mm/yyyy')
-- add_months(to_date('06/04/'||to_char(start_date,'YYYY'),'dd/mm/yyyy'),12)
-- End of BUG 5671777-5
start_year,
effective_date end_year
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
hr_utility.trace('Update rollup figures.');
select
assignment_id
from pay_assignment_actions
where assignment_action_id = c_assignment_action_id;
select ver.version from
ad_file_versions ver, ad_files f
where f.file_id = ver.file_id
and f.filename = 'pygbffedi.hdt'
order by ver.file_version_id desc;