[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hrl.location_id
INTO l_temp_location_id
FROM hr_locations hrl
WHERE hrl.location_id = p_location_id;
SELECT hrl.address_line_1
, hrl.address_line_2
, hrl.address_line_3
--, hrl.town_or_city -- bug#15993315 commented to take town_or_city from fnd_lookup_values
, Decode(hrl.town_or_city,flv4.lookup_code,flv4.meaning,hrl.town_or_city)
, NVL(DECODE(hrl.region_1,NULL,hrl.region_2,
DECODE(flv1.meaning,NULL,
DECODE(flv2.meaning,NULL,flv3.meaning,flv2.lookup_code)
, flv1.lookup_code)), hrl.region_2)
, hrl.postal_code
, NVL(ftel.territory_short_name,hrl.country)
, fte.iso_territory_code
INTO x_address_line_1
, x_address_line_2
, x_address_line_3
, x_town_or_city
, x_state_or_province
, x_postal_code
, x_territory_short_name
, x_iso_territory_code
FROM hr_locations_all hrl
, fnd_territories fte
, fnd_territories_tl ftel
, fnd_lookup_values flv1
, fnd_lookup_values flv2
, fnd_lookup_values flv3
, fnd_lookup_values flv4
WHERE hrl.location_id = p_location_id
AND hrl.country = fte.territory_code (+)
AND hrl.country = ftel.territory_code (+)
AND DECODE(ftel.territory_code, NULL, '1', ftel.language) =
DECODE(ftel.territory_code, NULL, '1', USERENV('LANG'))
AND hrl.region_1 = flv1.lookup_code (+)
AND hrl.country || '_PROVINCE' = flv1.lookup_type (+)
AND DECODE(flv1.lookup_code, NULL, '1', flv1.security_group_id) =
DECODE(flv1.lookup_code, NULL, '1',
FND_GLOBAL.lookup_security_group(flv1.lookup_type, flv1.view_application_id))
AND DECODE(flv1.lookup_code, NULL, '1', flv1.view_application_id) =
DECODE(flv1.lookup_code, NULL, '1', 3)
AND DECODE(flv1.lookup_code, NULL, '1', flv1.language) =
DECODE(flv1.lookup_code, NULL, '1', USERENV('LANG'))
AND hrl.region_2 = flv2.lookup_code (+)
AND hrl.country || '_STATE' = flv2.lookup_type (+)
AND DECODE(flv2.lookup_code, NULL, '1', flv2.security_group_id) =
DECODE(flv2.lookup_code, NULL, '1',
FND_GLOBAL.lookup_security_group(flv2.lookup_type, flv2.view_application_id))
AND DECODE(flv2.lookup_code, NULL, '1', flv2.view_application_id) =
DECODE(flv2.lookup_code, NULL, '1', 3)
AND DECODE(flv2.lookup_code, NULL, '1', flv2.language) =
DECODE(flv2.lookup_code, NULL, '1', USERENV('LANG'))
AND hrl.region_1 = flv3.lookup_code (+)
AND hrl.country || '_COUNTY' = flv3.lookup_type (+)
AND DECODE(flv3.lookup_code, NULL, '1', flv3.security_group_id) =
DECODE(flv3.lookup_code, NULL, '1',
FND_GLOBAL.lookup_security_group(flv3.lookup_type, flv3.view_application_id))
AND DECODE(flv3.lookup_code, NULL, '1', flv3.view_application_id) =
DECODE(flv3.lookup_code, NULL, '1', 3)
AND DECODE(flv3.lookup_code, NULL, '1', flv3.language) =
DECODE(flv3.lookup_code, NULL, '1', USERENV('LANG'))
AND hrl.town_or_city = flv4.lookup_code(+)
AND hrl.country || '_PROVINCE' = flv4.lookup_type (+)
AND DECODE(flv4.lookup_code, NULL, '1', flv4.security_group_id) =
DECODE(flv4.lookup_code, NULL, '1',
FND_GLOBAL.lookup_security_group(flv4.lookup_type, flv4.view_application_id))
AND DECODE(flv4.lookup_code, NULL, '1', flv4.view_application_id) =
DECODE(flv4.lookup_code, NULL, '1', 3)
AND DECODE(flv4.lookup_code, NULL, '1', flv4.language) =
DECODE(flv4.lookup_code, NULL, '1', USERENV('LANG'))
;
SELECT hzl.address1
, hzl.address2
, hzl.address3
, hzl.city
, NVL(DECODE(hzl.county,NULL,hzl.state,
DECODE(flv1.meaning,NULL,
DECODE(flv2.meaning,NULL,flv3.meaning,flv2.lookup_code)
, flv1.lookup_code)), hzl.state)|| Decode (hzl.province, NULL , '', ', ' || hzl.province) --bug10245785
, hzl.postal_code
, NVL(ftel.territory_short_name, hzl.country)
, fte.iso_territory_code
INTO x_address_line_1
, x_address_line_2
, x_address_line_3
, x_town_or_city
, x_state_or_province
, x_postal_code
, x_territory_short_name
, x_iso_territory_code
FROM hz_locations hzl
, fnd_territories fte
, fnd_territories_tl ftel
, fnd_lookup_values flv1
, fnd_lookup_values flv2
, fnd_lookup_values flv3
WHERE hzl.location_id = p_location_id
AND hzl.country = fte.territory_code (+)
AND hzl.country = ftel.territory_code (+)
AND DECODE(ftel.territory_code, NULL, '1', ftel.language) =
DECODE(ftel.territory_code, NULL, '1', USERENV('LANG'))
AND hzl.county = flv1.lookup_code (+)
AND hzl.country || '_PROVINCE' = flv1.lookup_type (+)
AND DECODE(flv1.lookup_code, NULL, '1', flv1.security_group_id) =
DECODE(flv1.lookup_code, NULL, '1',
FND_GLOBAL.lookup_security_group(flv1.lookup_type, flv1.view_application_id))
AND DECODE(flv1.lookup_code, NULL, '1', flv1.view_application_id) =
DECODE(flv1.lookup_code, NULL, '1', 3)
AND DECODE(flv1.lookup_code, NULL, '1', flv1.language) =
DECODE(flv1.lookup_code, NULL, '1', USERENV('LANG'))
AND hzl.state = flv2.lookup_code (+)
AND hzl.country || '_STATE' = flv2.lookup_type (+)
AND DECODE(flv2.lookup_code, NULL, '1', flv2.security_group_id) =
DECODE(flv2.lookup_code, NULL, '1',
FND_GLOBAL.lookup_security_group(flv2.lookup_type, flv2.view_application_id))
AND DECODE(flv2.lookup_code, NULL, '1', flv2.view_application_id) =
DECODE(flv2.lookup_code, NULL, '1', 3)
AND DECODE(flv2.lookup_code, NULL, '1', flv2.language) =
DECODE(flv2.lookup_code, NULL, '1', USERENV('LANG'))
AND hzl.county = flv3.lookup_code (+)
AND hzl.country || '_COUNTY' = flv3.lookup_type (+)
AND DECODE(flv3.lookup_code, NULL, '1', flv3.security_group_id) =
DECODE(flv3.lookup_code, NULL, '1',
FND_GLOBAL.lookup_security_group(flv3.lookup_type, flv3.view_application_id))
AND DECODE(flv3.lookup_code, NULL, '1', flv3.view_application_id) =
DECODE(flv3.lookup_code, NULL, '1', 3)
AND DECODE(flv3.lookup_code, NULL, '1', flv3.language) =
DECODE(flv3.lookup_code, NULL, '1', USERENV('LANG'))
;
** 2. Based on the x_temp_location_id the address will be selected from either
** hr_locations or from hz_locations
**
********************************************************************************/
PROCEDURE get_address
( x_location_id IN Number,
Address_line_1 OUT NOCOPY Varchar2,
Address_line_2 OUT NOCOPY Varchar2,
Address_line_3 OUT NOCOPY Varchar2,
Territory_short_name OUT NOCOPY VArchar2,
Address_info OUT NOCOPY Varchar2 )
IS
l_town_or_city HR_LOCATIONS_ALL.town_or_city%TYPE;
** 2. Based on the x_temp_location_id the address will be selected from either
** hr_locations or from hz_locations
**--Change Hisotry: bug#3438608 added the out variables x_town_or_city
--x_postal_code and x_state_or_province
********************************************************************************/
PROCEDURE get_address
( p_location_id IN Number,
x_address_line_1 OUT NOCOPY Varchar2,
x_address_line_2 OUT NOCOPY Varchar2,
x_address_line_3 OUT NOCOPY Varchar2,
x_territory_short_name OUT NOCOPY VArchar2,
x_address_info OUT NOCOPY Varchar2,
x_location_name OUT NOCOPY Varchar2,
x_contact_phone OUT NOCOPY Varchar2,
x_contact_fax OUT NOCOPY Varchar2,
x_address_line_4 OUT NOCOPY Varchar2,
x_town_or_city OUT NOCOPY HR_LOCATIONS.town_or_city%type,
x_postal_code OUT NOCOPY HR_LOCATIONS.postal_code%type,
x_state_or_province OUT NOCOPY varchar2)
IS
l_town_or_city Varchar2(240);
/* Select the location id from hr_locations. If the location is in hr_locations
it will be populated. Else the l_temp_location_id will be made NULL */
Begin
Select location_id into l_temp_location_id
from hr_locations
where location_id = p_location_id;
/* If the l_addr_select_qry location id is not null then get the address from hr_locations */
Begin
/*Bug 5084855 Adding the NVL to get the Country value for Generic Address Style */
Select HLC.ADDRESS_LINE_1,
HLC.ADDRESS_LINE_2,
HLC.ADDRESS_LINE_3,
-- HLC.TOWN_OR_CITY, --bug#15993315 commented to fetch town_or_city from fnd_looup_values
Decode(HLC.TOWN_OR_CITY,FCL4.lookup_code,FCL4.meaning,HLC.TOWN_OR_CITY),
NVL(DECODE(HLC.REGION_1, NULL, HLC.REGION_2,
DECODE(FCL1.MEANING, NULL,
DECODE(FCL2.MEANING, NULL,FCL3.MEANING, FCL2.LOOKUP_CODE),
FCL1.LOOKUP_CODE)), HLC.REGION_2) ,
HLC.POSTAL_CODE,
NVL(FTE.TERRITORY_SHORT_NAME,HLC.COUNTRY),
HLC.LOCATION_CODE,
HLC.TELEPHONE_NUMBER_1,
HLC.TELEPHONE_NUMBER_2
INTO
x_address_line_1 ,
x_address_line_2 ,
x_address_line_3 ,
l_town_or_city ,
l_state_or_province,
l_postal_code,
x_territory_short_name,
x_location_name,
x_contact_phone,
x_contact_fax
FROM
HR_LOCATIONS HLC,
FND_TERRITORIES_TL FTE,
FND_LOOKUP_VALUES FCL1,
FND_LOOKUP_VALUES FCL2,
FND_LOOKUP_VALUES FCL3,
FND_LOOKUP_VALUES FCL4
Where
HLC.LOCATION_ID = p_location_id AND
HLC.COUNTRY = FTE.TERRITORY_CODE (+) AND
DECODE(FTE.TERRITORY_CODE, NULL, '1', FTE.LANGUAGE) =
DECODE(FTE.TERRITORY_CODE, NULL, '1', USERENV('LANG')) AND
HLC.REGION_1 = FCL1.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_PROVINCE' = FCL1.LOOKUP_TYPE (+) AND
DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID) =
DECODE(FCL1.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID)) AND
DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) =
DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) =
DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
HLC.REGION_2 = FCL2.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_STATE' = FCL2.LOOKUP_TYPE (+) AND
DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID) =
DECODE(FCL2.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID)) AND
DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) =
DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE) =
DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
HLC.REGION_1 = FCL3.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_COUNTY' = FCL3.LOOKUP_TYPE (+) AND
DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID)) AND
DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
HLC.TOWN_OR_CITY = FCL4.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_PROVINCE' = FCL4.LOOKUP_TYPE (+) AND
DECODE(FCL4.LOOKUP_CODE, NULL, '1', FCL4.SECURITY_GROUP_ID) =
DECODE(FCL4.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL4.LOOKUP_TYPE, FCL4.VIEW_APPLICATION_ID)) AND
DECODE(FCL4.LOOKUP_CODE, NULL, '1', FCL4.VIEW_APPLICATION_ID) =
DECODE(FCL4.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL4.LOOKUP_CODE, NULL, '1', FCL4.LANGUAGE) =
DECODE(FCL4.LOOKUP_CODE, NULL, '1', USERENV('LANG')) ;
/* If the l_addr_select_qry location id is null then select the address from hz_locations */
/*
bug#3463617: address4 is selected from hz_locations.
*/
Begin
/*Bug 5084855 Adding the NVL to get the Country value for Generic Address Style */
SELECT
HLC.ADDRESS1,
HLC.ADDRESS2,
HLC.ADDRESS3,
HLC.CITY,
NVL(DECODE(HLC.county, NULL, HLC.state,
DECODE(FCL1.MEANING, NULL,
DECODE(FCL2.MEANING, NULL,FCL3.MEANING, FCL2.LOOKUP_CODE),
FCL1.LOOKUP_CODE)), HLC.state)|| Decode (HLC.province, NULL , '', ', ' || HLC.province) , --bug10245785
HLC.POSTAL_CODE,
NVL(FTE.TERRITORY_SHORT_NAME, HLC.COUNTRY),
HLC.ADDRESS4
INTO
x_address_line_1 ,
x_address_line_2 ,
x_address_line_3 ,
l_town_or_city ,
l_state_or_province,
l_postal_code,
x_territory_short_name,
x_address_line_4
FROM
HZ_LOCATIONS HLC,
FND_TERRITORIES_TL FTE,
FND_LOOKUP_VALUES FCL1,
FND_LOOKUP_VALUES FCL2,
FND_LOOKUP_VALUES FCL3
WHERE
HLC.LOCATION_ID = p_location_id AND
HLC.COUNTRY = FTE.TERRITORY_CODE (+) AND
DECODE(FTE.TERRITORY_CODE, NULL, '1', FTE.LANGUAGE) =
DECODE(FTE.TERRITORY_CODE, NULL, '1', USERENV('LANG')) AND
HLC.county = FCL1.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_PROVINCE' = FCL1.LOOKUP_TYPE (+) AND
DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID) =
DECODE(FCL1.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID)) AND
DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) =
DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) =
DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
HLC.state = FCL2.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_STATE' = FCL2.LOOKUP_TYPE (+) AND
DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID) =
DECODE(FCL2.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID)) AND
DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) =
DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE) =
DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
HLC.county = FCL3.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_COUNTY' = FCL3.LOOKUP_TYPE (+) AND
DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID)) AND
DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG')) ;
l_addr_select_qry varchar2(4000);
/* Select the location id from hr_locations. If the location is in hr_locations
it will be populated. Else the l_temp_location_id will be made NULL */
Begin
Select location_id, style into l_temp_location_id, l_style_code
from hr_locations
where location_id = p_location_id;
Select HLC.ADDRESS_LINE_1,
HLC.ADDRESS_LINE_2,
HLC.ADDRESS_LINE_3,
-- HLC.TOWN_OR_CITY, --bug#15993315 commented to fetch town_or_city from fnd_lookup_values
Decode(HLC.TOWN_OR_CITY,FCL4.lookup_code,FCL4.meaning,HLC.TOWN_OR_CITY),
NVL(DECODE(HLC.REGION_1, NULL, HLC.REGION_2,
DECODE(FCL1.MEANING, NULL,
DECODE(FCL2.MEANING, NULL,FCL3.MEANING, FCL2.LOOKUP_CODE),
FCL1.LOOKUP_CODE)), HLC.REGION_2) ,
HLC.POSTAL_CODE,
NVL(FTE.TERRITORY_SHORT_NAME, HLC.COUNTRY),
HLC.LOCATION_CODE,
HLC.TELEPHONE_NUMBER_1,
HLC.TELEPHONE_NUMBER_2
INTO
x_address_line_1 ,
x_address_line_2 ,
x_address_line_3 ,
x_town_or_city ,
x_state_or_province,
x_postal_code,
x_territory_short_name,
x_location_name,
x_contact_phone,
x_contact_fax
FROM
HR_LOCATIONS HLC,
FND_TERRITORIES_TL FTE,
FND_LOOKUP_VALUES FCL1,
FND_LOOKUP_VALUES FCL2,
FND_LOOKUP_VALUES FCL3,
FND_LOOKUP_VALUES FCL4
Where
HLC.LOCATION_ID = p_location_id AND
HLC.COUNTRY = FTE.TERRITORY_CODE (+) AND
DECODE(FTE.TERRITORY_CODE, NULL, '1', FTE.LANGUAGE) =
DECODE(FTE.TERRITORY_CODE, NULL, '1', USERENV('LANG')) AND
HLC.REGION_1 = FCL1.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_PROVINCE' = FCL1.LOOKUP_TYPE (+) AND
DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID) =
DECODE(FCL1.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID)) AND
DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) =
DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) =
DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
HLC.REGION_2 = FCL2.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_STATE' = FCL2.LOOKUP_TYPE (+) AND
DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID) =
DECODE(FCL2.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID)) AND
DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) =
DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE) =
DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
HLC.REGION_1 = FCL3.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_COUNTY' = FCL3.LOOKUP_TYPE (+) AND
DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID)) AND
DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
HLC.TOWN_OR_CITY = FCL4.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_PROVINCE' = FCL4.LOOKUP_TYPE (+) AND
DECODE(FCL4.LOOKUP_CODE, NULL, '1', FCL4.SECURITY_GROUP_ID) =
DECODE(FCL4.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL4.LOOKUP_TYPE, FCL4.VIEW_APPLICATION_ID)) AND
DECODE(FCL4.LOOKUP_CODE, NULL, '1', FCL4.VIEW_APPLICATION_ID) =
DECODE(FCL4.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL4.LOOKUP_CODE, NULL, '1', FCL4.LANGUAGE) =
DECODE(FCL4.LOOKUP_CODE, NULL, '1', USERENV('LANG')) ;
4. If the style code exists retrieve select query and prompts list from g_addr_prompt_query PL/SQL table,
else use FND_DFLEX package to retrieve the column names and prompts and add them to
g_addr_prompt_query PL/SQL table.
*******************************************************************************************************/
l_table_count := g_address_details.count; -- Number of rows in the PL/SQl table
l_addr_select_qry := g_addr_prompt_query(i).query;
l_addr_select_qry := 'select '|| l_addr_col_names || ' from hr_locations where location_id = :1 ' ;
FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'select query:'||l_addr_select_qry);
--Add style code, address prompts array and select query to PL/SQL if the style code is not in PL/SQL table.
IF l_style_code_exists <> 'Y' THEN
l_addr_prompt_query_count := l_addr_prompt_query_count+1;
g_addr_prompt_query(l_addr_prompt_query_count).query := l_addr_select_qry;
execute immediate l_addr_select_qry INTO g_address_details(l_table_count).addr_data_1, g_address_details(l_table_count).addr_data_2, g_address_details(l_table_count).addr_data_3,
g_address_details(l_table_count).addr_data_4, g_address_details(l_table_count).addr_data_5, g_address_details(l_table_count).addr_data_6,
g_address_details(l_table_count).addr_data_7, g_address_details(l_table_count).addr_data_8, g_address_details(l_table_count).addr_data_9,
g_address_details(l_table_count).addr_data_10, g_address_details(l_table_count).addr_data_11, g_address_details(l_table_count).addr_data_12,
g_address_details(l_table_count).addr_data_13, g_address_details(l_table_count).addr_data_14, g_address_details(l_table_count).addr_data_15,
g_address_details(l_table_count).addr_data_16, g_address_details(l_table_count).addr_data_17, g_address_details(l_table_count).addr_data_18,
g_address_details(l_table_count).addr_data_19, g_address_details(l_table_count).addr_data_20 USING p_location_id ;
/* If location id is null then select the address from hz_locations */
IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head, l_api_name ||'Before executing the HZ_LOCATIONS query');
SELECT
HLC.ADDRESS1,
HLC.ADDRESS2,
HLC.ADDRESS3,
HLC.CITY,
NVL(DECODE(HLC.county, NULL, HLC.state,
DECODE(FCL1.MEANING, NULL,
DECODE(FCL2.MEANING, NULL,FCL3.MEANING, FCL2.LOOKUP_CODE),
FCL1.LOOKUP_CODE)), HLC.state)|| Decode (HLC.province, NULL , '', ', ' || HLC.province) ,--bug10245785
HLC.POSTAL_CODE,
NVL(FTE.TERRITORY_SHORT_NAME, HLC.COUNTRY),
HLC.ADDRESS4,
ADDRESS_STYLE
INTO
x_address_line_1 ,
x_address_line_2 ,
x_address_line_3 ,
x_town_or_city ,
x_state_or_province,
x_postal_code,
x_territory_short_name,
x_address_line_4,
l_style_code
FROM
HZ_LOCATIONS HLC,
FND_TERRITORIES_TL FTE,
FND_LOOKUP_VALUES FCL1,
FND_LOOKUP_VALUES FCL2,
FND_LOOKUP_VALUES FCL3
WHERE
HLC.LOCATION_ID = p_location_id AND
HLC.COUNTRY = FTE.TERRITORY_CODE (+) AND
DECODE(FTE.TERRITORY_CODE, NULL, '1', FTE.LANGUAGE) =
DECODE(FTE.TERRITORY_CODE, NULL, '1', USERENV('LANG')) AND
HLC.county = FCL1.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_PROVINCE' = FCL1.LOOKUP_TYPE (+) AND
DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID) =
DECODE(FCL1.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID)) AND
DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) =
DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) =
DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
HLC.state = FCL2.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_STATE' = FCL2.LOOKUP_TYPE (+) AND
DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID) =
DECODE(FCL2.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID)) AND
DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) =
DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE) =
DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG')) AND
HLC.county = FCL3.LOOKUP_CODE (+) AND
HLC.COUNTRY || '_COUNTY' = FCL3.LOOKUP_TYPE (+) AND
DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1',
FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID)) AND
DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3) AND
DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG')) ;
DML queries cannot be used in the select queries. The work around for this
is populate the PL/SQL table when the function is called in the select query
and then insert values into global temp table after selecting the values.
Why Global temp table: XML cannot be generated from global PL/SQL table.
***********************************************************************************/
PROCEDURE populate_gt is
l_count number := 0;
FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PO_HR_LOCATION', 'populate_gt Before inserting values in global temp table');
INSERT INTO po_address_details_gt
(location_id,
address_style,
addr_label_1,
addr_label_2,
addr_label_3,
addr_label_4,
addr_label_5,
addr_label_6,
addr_label_7,
addr_label_8,
addr_label_9,
addr_label_10,
addr_label_11,
addr_label_12,
addr_label_13,
addr_label_14,
addr_label_15,
addr_label_16,
addr_label_17,
addr_label_18,
addr_label_19,
addr_label_20,
addr_data_1,
addr_data_2,
addr_data_3,
addr_data_4,
addr_data_5,
addr_data_6,
addr_data_7,
addr_data_8,
addr_data_9,
addr_data_10,
addr_data_11,
addr_data_12,
addr_data_13,
addr_data_14,
addr_data_15,
addr_data_16,
addr_data_17,
addr_data_18,
addr_data_19,
addr_data_20)
VALUES(
g_address_details(i).location_id,
g_address_details(i).address_style,
g_address_details(i).addr_label_1,
g_address_details(i).addr_label_2,
g_address_details(i).addr_label_3,
g_address_details(i).addr_label_4,
g_address_details(i).addr_label_5,
g_address_details(i).addr_label_6,
g_address_details(i).addr_label_7,
g_address_details(i).addr_label_8,
g_address_details(i).addr_label_9,
g_address_details(i).addr_label_10,
g_address_details(i).addr_label_11,
g_address_details(i).addr_label_12,
g_address_details(i).addr_label_13,
g_address_details(i).addr_label_14,
g_address_details(i).addr_label_15,
g_address_details(i).addr_label_16,
g_address_details(i).addr_label_17,
g_address_details(i).addr_label_18,
g_address_details(i).addr_label_19,
g_address_details(i).addr_label_20,
g_address_details(i).addr_data_1,
g_address_details(i).addr_data_2,
g_address_details(i).addr_data_3,
g_address_details(i).addr_data_4,
g_address_details(i).addr_data_5,
g_address_details(i).addr_data_6,
g_address_details(i).addr_data_7,
g_address_details(i).addr_data_8,
g_address_details(i).addr_data_9,
g_address_details(i).addr_data_10,
g_address_details(i).addr_data_11,
g_address_details(i).addr_data_12,
g_address_details(i).addr_data_13,
g_address_details(i).addr_data_14,
g_address_details(i).addr_data_15,
g_address_details(i).addr_data_16,
g_address_details(i).addr_data_17,
g_address_details(i).addr_data_18,
g_address_details(i).addr_data_19,
g_address_details(i).addr_data_20);
FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PO_HR_LOCATION', 'populate_gt: After inserting values in global temp table');
g_address_details.delete;
FND_LOG.string(FND_LOG.LEVEL_STATEMENT,'PO_HR_LOCATION', 'populate_gt: Error while inserting values in global temp table');