/* Formatted on 7/17/2014 12:44:27 PM (QP5 v5.115.810.9015) */
SELECT XMLELEMENT ("UsersList",
XMLAGG (XMLELEMENT ("Users",
xmlattributes (usr.user_id AS "Id",
user_name AS "Name",
NVL (usr.description,
'Null'
) AS "Description",
TO_CHAR (creation_date,
'DD-MON-YYYY'
) AS "CreatedOn",
TO_CHAR (last_update_date,
'DD-MON-YYYY'
) AS "LastUpdatedOn",
usr.end_date AS "EndDate"
),
(SELECT XMLAGG(XMLELEMENT ("Responsibility",
xmlattributes (urgd.responsibility_id AS "Id",
urgd.responsibility_application_id AS "Appl_id",
resp.responsibility_name AS "Name",
TO_CHAR (urgd.start_date,
'dd-mon-yyyy'
) AS "StartDate",
DECODE (urgd.end_date,
NULL,
'NULL',
TO_CHAR (urgd.end_date,
'dd-mon-yyyy'
)
) AS "EndDate"
)
))
FROM apps.fnd_user_resp_groups_direct urgd,
apps.fnd_responsibility_vl resp
WHERE urgd.user_id = usr.user_id
AND urgd.responsibility_id =
resp.responsibility_id
AND urgd.responsibility_application_id =
resp.application_id)
)
)
).getclobval ()
FROM apps.fnd_user usr
WHERE ROWNUM < 20
SELECT XMLELEMENT ("UsersList",
XMLAGG (XMLELEMENT ("Users",
xmlattributes (usr.user_id AS "Id",
user_name AS "Name",
NVL (usr.description,
'Null'
) AS "Description",
TO_CHAR (creation_date,
'DD-MON-YYYY'
) AS "CreatedOn",
TO_CHAR (last_update_date,
'DD-MON-YYYY'
) AS "LastUpdatedOn",
usr.end_date AS "EndDate"
),
(SELECT XMLAGG(XMLELEMENT ("Responsibility",
xmlattributes (urgd.responsibility_id AS "Id",
urgd.responsibility_application_id AS "Appl_id",
resp.responsibility_name AS "Name",
TO_CHAR (urgd.start_date,
'dd-mon-yyyy'
) AS "StartDate",
DECODE (urgd.end_date,
NULL,
'NULL',
TO_CHAR (urgd.end_date,
'dd-mon-yyyy'
)
) AS "EndDate"
)
))
FROM apps.fnd_user_resp_groups_direct urgd,
apps.fnd_responsibility_vl resp
WHERE urgd.user_id = usr.user_id
AND urgd.responsibility_id =
resp.responsibility_id
AND urgd.responsibility_application_id =
resp.application_id)
)
)
).getclobval ()
FROM apps.fnd_user usr
WHERE ROWNUM < 20
No comments:
Post a Comment