[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT XMLELEMENT("PERMIT_HEADER",
XMLFOREST(EWP.PERMIT_NAME AS PERMIT_NAME,
ml.meaning AS PERMIT_TYPE,
EWP.DESCRIPTION AS PERMIT_DESC,
EPSV.PERMIT_STATUS AS PERMIT_STATUS,
ppf.full_name AS PREPARED_BY,
fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWP.CREATION_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS PREPARED_DATE,
fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWP.VALID_FROM), calendar_aware => FND_DATE.calendar_aware_alt) AS PERMIT_VALID_FROM,
fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWP.VALID_TO), calendar_aware => FND_DATE.calendar_aware_alt) AS PERMIT_VALID_TO
))"PERMIT_HEADER"
FROM EAM_WORK_PERMITS EWP,
EAM_PERMIT_STATUSES_VL EPSV,
mfg_lookups ml,
fnd_user fu,
per_people_f ppf
WHERE EWP.PERMIT_ID =P_PERMIT_ID
AND EWP.ORGANIZATION_ID =P_ORGANIZATION_ID
AND EPSV.STATUS_ID =EWP.USER_DEFINED_STATUS_ID
and EWP.created_by = fu.user_id(+)
and fu.employee_id = ppf.person_id (+)
and ml.lookup_type (+) = 'EAM_WORK_PERMIT_TYPE'
AND ml.lookup_code=ewp.permit_type
AND Nvl(ppf.effective_end_date,SYSDATE)>=sysdate
;
SELECT XMLELEMENT("APPROVER",
XMLFOREST(
ppf.full_name AS APPROVER_NAME)) "APPROVER"
FROM
EAM_WORK_PERMITS EWP,
per_people_f ppf ,
fnd_user fu
WHERE EWP.PERMIT_ID =P_PERMIT_ID
AND EWP.ORGANIZATION_ID =P_ORGANIZATION_ID
AND EWP.approved_by = fu.user_id(+)
and fu.employee_id = ppf.person_id (+)
AND Nvl(ppf.effective_end_date,SYSDATE)>=sysdate;
SELECT XMLELEMENT("WORK_CLEARANCE",
XMLFOREST(WORK_CLEARANCE_NAME,
WORK_CLEARANCE_DESCRIPTION,
WORK_CLEARANCE_STATUS,
ISOLATION_TYPE,
SCHEDULED_ESTAB_START_DATE,
SCHEDULED_REESTAB_END_DATE)) "WORK_CLEARANCE"
FROM
(
SELECT
EWC.WORK_CLEARANCE_NAME AS WORK_CLEARANCE_NAME,
EWC.DESCRIPTION AS WORK_CLEARANCE_DESCRIPTION,
EWCSV.WORK_CLEARANCE_STATUS AS WORK_CLEARANCE_STATUS,
ML.MEANING AS ISOLATION_TYPE,
fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWC.SCHEDULED_ESTAB_START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS SCHEDULED_ESTAB_START_DATE,
fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWC.SCHEDULED_REESTAB_END_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS SCHEDULED_REESTAB_END_DATE
FROM
EAM_WORK_CLEARANCES EWC,
EAM_SAFETY_ASSOCIATIONS ESA,
EAM_WORK_CLEARANCE_STATUSES_VL EWCSV,
EAM_ISOLATIONS ISO,
MFG_LOOKUPS ml
WHERE
ESA.ASSOCIATION_TYPE= 2
AND ESA.SOURCE_ID =P_PERMIT_ID
AND ESA.ORGANIZATION_ID =P_ORGANIZATION_ID
AND ESA.TARGET_REF_ID = EWC.WORK_CLEARANCE_ID
AND EWC.LAST_ISOLATION_ID = ISO.ISOLATION_ID(+)
AND ISO.ISOLATION_TYPE = ML.LOOKUP_CODE (+)
AND ML.LOOKUP_TYPE(+) = 'EAM_ISOLATION_TYPE'
AND EWC.USER_DEFINED_STATUS_ID = EWCSV.STATUS_ID
UNION
SELECT
EWC.WORK_CLEARANCE_NAME AS WORK_CLEARANCE_NAME,
EWC.DESCRIPTION AS WORK_CLEARANCE_DESCRIPTION,
EWCSV.WORK_CLEARANCE_STATUS AS WORK_CLEARANCE_STATUS,
ML.MEANING AS ISOLATION_TYPE,
fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWC.SCHEDULED_ESTAB_START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS SCHEDULED_ESTAB_START_DATE,
fnd_date.date_to_displayDT(dateval => Convert_to_client_time(EWC.SCHEDULED_REESTAB_END_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS SCHEDULED_REESTAB_END_DATE
FROM
EAM_WORK_CLEARANCES EWC,
EAM_WORK_PERMITS PERMIT,
EAM_SAFETY_ASSOCIATIONS ESA1,
EAM_SAFETY_ASSOCIATIONS ESA2,
MFG_LOOKUPS ML,
EAM_ISOLATIONS ISO,
WIP_DISCRETE_JOBS WDJ,
EAM_WORK_CLEARANCE_STATUSES_VL EWCSV
WHERE
ESA1.SOURCE_ID=PERMIT.PERMIT_ID AND
ESA1.TARGET_REF_ID=WDJ.WIP_ENTITY_ID AND
ESA1.ASSOCIATION_TYPE=3 AND
ESA2.SOURCE_ID=EWC.WORK_CLEARANCE_ID AND
ESA2.TARGET_REF_ID=WDJ.WIP_ENTITY_ID AND
ESA2.ASSOCIATION_TYPE=4 AND
EWC.LAST_ISOLATION_ID = ISO.ISOLATION_ID(+) AND
ML.LOOKUP_CODE(+) = ISO.ISOLATION_TYPE AND
ML.LOOKUP_TYPE (+) = 'EAM_ISOLATION_TYPE' AND
PERMIT.PERMIT_ID=P_PERMIT_ID AND
PERMIT.ORGANIZATION_ID = P_ORGANIZATION_ID
AND EWC.WORK_CLEARANCE_ID NOT IN(SELECT TARGET_REF_ID
FROM EAM_SAFETY_ASSOCIATIONS
WHERE SOURCE_ID = P_PERMIT_ID
AND ASSOCIATION_TYPE=2
AND ORGANIZATION_ID=P_ORGANIZATION_ID)
AND EWC.USER_DEFINED_STATUS_ID = EWCSV.STATUS_ID
)
ORDER BY WORK_CLEARANCE_NAME;
SELECT XMLELEMENT("WORK_ORDER",
XMLFOREST(WORK_ORDER_NAME,
WO_ASSET_NUMBER,
WO_ASSET_DESC,
WO_DESC,
WO_SCHEDULED_START_DATE,
WO_SCHEDULED_COMPL_DATE)) "WORK_ORDER"
FROM(
SELECT
WEDV.WIP_ENTITY_NAME AS WORK_ORDER_NAME,
WEDV.ASSET_NUMBER AS WO_ASSET_NUMBER,
ASSET_DESCRIPTION AS WO_ASSET_DESC,
WEDV.DESCRIPTION AS WO_DESC,
fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WEDV.SCHEDULED_START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS WO_SCHEDULED_START_DATE,
fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WEDV.SCHEDULED_COMPLETION_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS WO_SCHEDULED_COMPL_DATE
FROM
EAM_WORK_ORDERS_V WEDV,
EAM_SAFETY_ASSOCIATIONS ESA
WHERE
ESA.ASSOCIATION_TYPE= 3
AND ESA.SOURCE_ID =P_PERMIT_ID
AND ESA.ORGANIZATION_ID =P_ORGANIZATION_ID
AND WEDV.WIP_ENTITY_ID =ESA.TARGET_REF_ID
UNION
SELECT
WEDV.WIP_ENTITY_NAME AS WORK_ORDER_NAME,
WEDV.ASSET_NUMBER AS WO_ASSET_NUMBER,
ASSET_DESCRIPTION AS WO_ASSET_DESC,
WEDV.DESCRIPTION AS WO_DESC,
fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WEDV.SCHEDULED_START_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS WO_SCHEDULED_START_DATE,
fnd_date.date_to_displayDT(dateval => Convert_to_client_time(WEDV.SCHEDULED_COMPLETION_DATE), calendar_aware => FND_DATE.calendar_aware_alt) AS WO_SCHEDULED_COMPL_DATE
FROM
EAM_SAFETY_ASSOCIATIONS ESA1,
EAM_SAFETY_ASSOCIATIONS ESA2,
EAM_WORK_PERMITS PERMIT,
EAM_WORK_CLEARANCES EWC,
EAM_WORK_ORDERS_V WEDV
WHERE
ESA1.SOURCE_ID=PERMIT.PERMIT_ID AND
ESA1.TARGET_REF_ID=EWC.WORK_CLEARANCE_ID AND
ESA1.ASSOCIATION_TYPE=2 AND
ESA2.SOURCE_ID=EWC.WORK_CLEARANCE_ID AND
ESA2.TARGET_REF_ID=WEDV.WIP_ENTITY_ID AND
ESA2.ASSOCIATION_TYPE=4 AND
PERMIT.PERMIT_ID = P_PERMIT_ID AND
PERMIT.ORGANIZATION_ID = P_ORGANIZATION_ID
)
ORDER BY WORK_ORDER_NAME;
SELECT XMLELEMENT("TEXT_ATTACHMENT",
XMLFOREST( fdst.short_text AS "TEXT_BODY",
fdv.description AS "TEXT_DESC",
fdv.category_description AS "TEXT_CATEGORY")) textAttachment
FROM fnd_documents_short_text fdst,
fnd_documents_vl fdv,
fnd_attached_documents fad
WHERE fdst.media_id = fdv.media_id
AND fad.document_id = fdv.document_id
AND fad.entity_name = 'EAM_WORK_PERMIT'
AND fad.pk1_value = p_org_id
AND fad.pk2_value = p_permit_id
AND fdv.datatype_id=1; --Text attachments
SELECT XMLELEMENT("URL_ATTACHMENT",
XMLFOREST( fdv.URL AS "URL_NAME",
fdv.description AS "URL_DESC",
fdv.category_description AS "URL_CATEGORY")) urlAttachment
FROM
fnd_documents_vl fdv,
fnd_attached_documents fad
WHERE
fad.document_id = fdv.document_id
AND fad.entity_name = 'EAM_WORK_PERMIT'
AND fad.pk1_value = p_org_id
AND fad.pk2_value = p_permit_id
AND fdv.datatype_id=5; --URL attachments
SELECT XMLELEMENT("FILE_ATTACHMENT",
XMLFOREST( fdv.file_name AS "FILE_NAME",
fdv.description AS "FILE_DESC",
fdv.category_description AS "FILE_CATEGORY")) fileAttachment
FROM fnd_documents_vl fdv,
fnd_attached_documents fad
WHERE fad.document_id = fdv.document_id
AND fad.entity_name = 'EAM_WORK_PERMIT'
AND fad.pk1_value = p_org_id
AND fad.pk2_value = p_permit_id
AND fdv.datatype_id=6
AND fdv.file_name IS NOT NULL
AND fdv.datatype_name NOT IN ('Long Text', 'Short Text');
select ewp.organization_id into l_organization_id
from EAM_WORK_PERMITS ewp
where ewp.permit_id=p_permit_ids(i).permit_id;
SELECT XMLConcat(l_xmlTypePermitHeader,permit_header_record.PERMIT_HEADER)
INTO l_xmlTypePermitHeader
FROM dual;
SELECT XMLConcat(l_xmlTypeApprover,approver_record.APPROVER)
INTO l_xmlTypeApprover
FROM dual;
SELECT XMLELEMENT("APPROVERS",l_xmlTypeApprover)
INTO l_xmlTypeApprover
FROM dual;
SELECT XMLConcat(l_xmlTypeAssociatedClearance,clearance_record.WORK_CLEARANCE)
INTO l_xmlTypeAssociatedClearance
FROM dual;
SELECT XMLELEMENT("WORK_CLEARANCES",l_xmlTypeAssociatedClearance)
INTO l_xmlTypeAssociatedClearance
FROM dual;
SELECT XMLConcat(l_xmlTypeAssociatedWO,wo_record.WORK_ORDER)
INTO l_xmlTypeAssociatedWO
FROM dual;
SELECT XMLELEMENT("WORK_ORDERS",l_xmlTypeAssociatedWO)
INTO l_xmlTypeAssociatedWO
FROM dual;
SELECT XMLConcat(l_xmlTypetextattachment,textattachment_record.textAttachment)
INTO l_xmlTypetextattachment
FROM dual;
SELECT XMLELEMENT("TEXT_ATTACHMENTS",l_xmlTypeTextAttachment)
INTO l_xmlTypeTextAttachment
FROM dual;
SELECT XMLConcat(l_xmlTypeURLAttachment,urlAttachment_record.URLAttachment)
INTO l_xmlTypeURLAttachment
FROM dual;
SELECT XMLELEMENT("URL_ATTACHMENTS",l_xmlTypeURLAttachment)
INTO l_xmlTypeURLAttachment
FROM dual;
SELECT XMLConcat(l_xmlTypefileattachment,fileattachment_record.FileAttachment)
INTO l_xmlTypefileattachment
FROM dual;
SELECT XMLELEMENT("FILE_ATTACHMENTS",l_xmlTypefileattachment)
INTO l_xmlTypefileattachment
FROM dual;
/* SELECT XMLCONCAT(l_xmlType,
XMLELEMENT("PERMIT_HEADER"
XMLFOREST(EWP.PERMIT_NAME AS PERMIT_NAME,
EWP.PERMIT_TYPE AS PERMIT_TYPE,
EWP.DESCRIPTION AS PERMIT_DESC,
EPSV.PERMIT_STATUS AS PERMIT_STATUS,
fnd_date.date_to_displayDT(Convert_to_client_time(EWP.VALID_FROM)) AS PERMIT_VALID_FROM,
fnd_date.date_to_displayDT(Convert_to_client_time(EWP.VALID_TO)) AS PERMIT_VALID_TO,
EWP.APPROVED_BY AS approverName),
XMLConcat(l_xmlTypeAssociatedClearance,l_xmlTypeAssociatedWO,l_xmlTypefileattachment)))AS "RESULT"
INTO l_xmlType
FROM EAM_WORK_PERMITS EWP,
EAM_PERMIT_STATUSES_VL EPSV
WHERE EWP.PERMIT_ID =p_permit_ids(i).permit_id
AND EWP.ORGANIZATION_ID =l_organization_id
AND EPSV.STATUS_ID =EWP.USER_DEFINED_STATUS_ID;*/
SELECT XMLCONCAT(l_xmlType,
XMLELEMENT("PERMIT",
XMLConcat(l_xmlTypePermitHeader,l_xmlTypeApprover,l_xmlTypeAssociatedClearance,l_xmlTypeAssociatedWO,l_xmlTypeTextAttachment,l_xmlTypeURLAttachment,l_xmlTypefileattachment))) "PERMIT"
INTO l_xmlType
FROM dual;
SELECT
XMLELEMENT("PERMITS",
XMLConcat(l_xmlTypeParamList,l_xmlType)) "PERMITS"
INTO l_xmlType
FROM dual;