[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select /*+ ORDERED */ count(*)
from
pay_balance_attributes pba,
pay_balance_validation pbv
where pba.attribute_id = cp_attribute_id
and (pba.business_group_id = cp_business_group_id
or pba.legislation_code = p_legislation_code)
and pba.defined_balance_id = pbv.defined_balance_id
and pbv.business_group_id = cp_business_group_id
and NVL(pbv.balance_load_date, cp_start_date) <= cp_start_date
and nvl(pbv.run_balance_status, 'I') = 'V';
select count(*)
from
pay_balance_attributes pba
where pba.attribute_id = cp_attribute_id
and (pba.business_group_id = cp_business_group_id
or pba.legislation_code = p_legislation_code);
** paramaters selected in SRS the report will
**
**
**
*****************************************************************/
PROCEDURE ebra_diagnostics
(errbuf OUT nocopy varchar2,
retcode OUT nocopy number,
p_output_file_type IN VARCHAR2,
p_attribute_balance IN VARCHAR2
)
IS
/************************************************************
** get_legi_Cd : Cursor to get legislation code and Business
group name
** Parameter : Business Group Id from SRS
** Return : 1.Legislation Code,
2.Name of the Business group
************************************************************/
cursor c_get_leg_cd(cp_business_group_id number) is
select org_information9,hou.name
from hr_organization_information hoi,hr_all_organization_units hou
where hoi.organization_id = cp_business_group_id
and hoi.org_information_context = 'Business Group Information'
and hou.organization_id = hoi.organization_id;
SELECT attribute_id, attribute_name
FROM
pay_bal_attribute_Definitions pbad
WHERE pbad.legislation_code = cp_legislation_code
ORDER BY attribute_name;
SELECT attribute_id, attribute_name
FROM
pay_bal_attribute_Definitions pbad
WHERE pbad.business_group_id = cp_business_group_id
order by attribute_name;
SELECT hl1.meaning,hl2.meaning
FROM hr_lookups hl1,hr_lookups hl2
WHERE hl2.lookup_type = 'RUN_BALANCE_STATUS'
AND hl2.lookup_code = 'V'
AND hl1.lookup_type = 'RUN_BALANCE_STATUS'
AND hl1.lookup_code = 'I';
SELECT pbt.balance_name, pbd.dimension_name
, decode(pbv.run_balance_status, 'I', null
,to_char(pbv.balance_load_date,'yyyy/mm/dd')) balance_load_date
,nvl(hl.meaning, hl2.meaning) Status
FROM
pay_defined_balances pdb, pay_balance_types pbt
, pay_balance_dimensions pbd
, per_business_groups pbg, hr_lookups hl
, pay_balance_validation pbv
, hr_lookups hl2
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pdb.save_run_balance = 'Y'
AND ((pdb.legislation_code = pbg.legislation_code)
or (pdb.business_group_id = pbg.business_group_id))
AND pbg.business_group_id = cp_business_group_id
AND hl.lookup_type(+) = 'RUN_BALANCE_STATUS'
AND hl.lookup_code(+) = pbv.run_balance_status
AND pbv.defined_balance_id (+) = pdb.defined_balance_id
AND pbv.business_group_id (+) = nvl(pdb.business_group_id, cp_business_group_id)
AND hl2.lookup_type = 'RUN_BALANCE_STATUS'
AND hl2.lookup_code = 'I'
ORDER BY STATUS,BALANCE_LOAD_DATE,BALANCE_NAME,DIMENSION_NAME ;
select distinct pba.attribute_id,
max(balance_load_date)
FROM
PAY_BALANCE_VALIDATION PBV
,PAY_BALANCE_ATTRIBUTES PBA
WHERE
PBV.business_group_id = cp_business_group_id
AND PBA.attribute_id = cp_attribute_id
AND PBV.defined_balance_id = pba.defined_balance_id
group by attribute_id;
SELECT
PBT.balance_name
,DIM.DIMENSION_NAME
FROM
PAY_BALANCE_DIMENSIONS DIM
, PAY_BALANCE_TYPES PBT
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_ATTRIBUTES PBA
WHERE
PBA.attribute_id = cp_attribute_id
and PBA.defined_balance_id = PDB.defined_balance_id
and PDB.balance_type_id = PBT.balance_type_id
and (PBT.business_group_id = cp_business_group_id or
PBT.legislation_code = cp_legislation_code)
and PDB.balance_dimension_id = DIM.balance_dimension_id;
SELECT
BAD.ATTRIBUTE_NAME
,PBT.balance_name
,DIM.DIMENSION_NAME
,PDB.save_run_balance
FROM
PAY_BALANCE_ATTRIBUTES PBA
, PAY_BAL_ATTRIBUTE_DEFINITIONS BAD
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_DIMENSIONS DIM
Where pba.attribute_id = bad.attribute_id
and pba.defined_balance_id = pdb.defined_balance_id
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = dim.balance_dimension_id
and pdb.save_run_balance is NULL
and ((pba.business_group_id = cp_business_group_id and pba.legislation_code is NULL) OR
(pba.legislation_code = cp_legislation_code and pba.business_group_id is NULL))
and nvl(bad.legislation_code, cp_legislation_code) = cp_legislation_Code
/* and not Exists
(select 1 from pay_balance_validation PBV
where PBV.defined_balance_id = PBA.defined_balance_id
)*/
order by bad.attribute_name;
/* If the Parameter has been selected in SRS as Yes then only Execute this Section 3 */
IF p_attribute_balance = 'Y' THEN
FOR i in 1..4 LOOP
fnd_file.put_line(fnd_file.output, formated_header_string(
' '
,p_output_file_type
));
UPDATE fnd_concurrent_requests
SET output_file_type = 'HTML'
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;