Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Monday, June 20, 2011

Oracle XML Publisher Queries

SELECT
templatesvleo.template_id,
templatesvleo.application_id,
templatesvleo.application_short_name,
templatesvleo.template_code,
templatesvleo.ds_app_short_name,
templatesvleo.data_source_code,
templatesvleo.template_type_code,
templatesvleo.default_language,
templatesvleo.default_territory,
templatesvleo.template_status,
templatesvleo.use_alias_table,
templatesvleo.start_date,
templatesvleo.end_date,
templatesvleo.template_name,
templatesvleo.description,
templatesvleo.created_by,
templatesvleo.creation_date,
templatesvleo.last_updated_by,
templatesvleo.last_update_date,
templatesvleo.last_update_login,
templatesvleo.object_version_number,
templatesvleo.attribute_category,
templatesvleo.attribute1,
templatesvleo.attribute15,
templatesvleo.dependency_flag,
(
SELECT application_name
FROM fnd_application_vl
WHERE application_short_name = templatesvleo.application_short_name
) application_name,
(
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'XDO_TEMPLATE_TYPE'
AND lookup_code = templatesvleo.template_type_code
) template_type,
(
SELECT data_source_name
FROM xdo_ds_definitions_vl
WHERE data_source_code = templatesvleo.data_source_code
AND application_short_name = templatesvleo.ds_app_short_name
) data_source_name,
(
SELECT file_name
FROM xdo_lobs
WHERE
(
(
lob_type = 'TEMPLATE'
AND xdo_file_type != 'RTF'
AND xdo_file_type = templatesvleo.template_type_code
AND xdo_lobs.LANGUAGE = templatesvleo.default_language
AND xdo_lobs.territory = templatesvleo.default_territory
)
OR ( lob_type = 'TEMPLATE_SOURCE' AND xdo_file_type IN ('RTF', 'RTF-ETEXT')
AND xdo_lobs.LANGUAGE = templatesvleo.default_language
AND xdo_lobs.territory = templatesvleo.default_territory
)
OR ( xdo_file_type = 'RTF'
AND lob_type = 'TEMPLATE_SOURCE'
AND LANGUAGE = templatesvleo.mls_language
AND territory = templatesvleo.mls_territory
AND EXISTS
(
SELECT mls.lob_code
FROM xdo_lobs mls
WHERE mls.lob_type = 'MLS_TEMPLATE'
AND mls.lob_code = templatesvleo.template_code
AND mls.application_short_name = templatesvleo.application_short_name
AND mls.LANGUAGE = templatesvleo.default_language
AND mls.territory = templatesvleo.default_territory
) AND NOT EXISTS
(
SELECT LOCAL.lob_code
FROM xdo_lobs LOCAL
WHERE LOCAL.lob_type = 'TEMPLATE_SOURCE'
AND LOCAL.lob_code = templatesvleo.template_code
AND LOCAL.application_short_name = templatesvleo.application_short_name
AND LOCAL.LANGUAGE = templatesvleo.default_language
AND LOCAL.territory = templatesvleo.default_territory
) ) )
AND lob_code = templatesvleo.template_code
AND xdo_lobs.application_short_name =
templatesvleo.application_short_name
) AS default_template_file,
(
SELECT file_name
FROM xdo_lobs
WHERE lob_type = 'TEMPLATE_SOURCE'
AND lob_code = templatesvleo.template_code
AND xdo_lobs.application_short_name =
templatesvleo.application_short_name
AND xdo_lobs.LANGUAGE = templatesvleo.mls_language
AND xdo_lobs.territory = templatesvleo.mls_territory
) AS mls_template_file,
(
SELECT NAME
FROM fnd_iso_languages_vl
WHERE iso_language_2 = templatesvleo.default_language
) AS default_file_lang,
DECODE (templatesvleo.default_territory,
'00', '',
fndterritoriesvl.territory_short_name
) AS default_file_terr,
templatesvleo.mls_language,
templatesvleo.mls_territory,
templatesvleo.default_output_type

FROM
xdo_templates_vl templatesvleo,
fnd_application_vl fndapplicationvl,
fnd_territories_vl fndterritoriesvl

WHERE fndapplicationvl.application_short_name = templatesvleo.application_short_name
AND fndterritoriesvl.territory_code(+) = templatesvleo.default_territory
AND template_name = "Template Name"

No comments:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect