[Home] [Help]
5839: L_INSTR_XML XMLTYPE;
5840:
5841: BEGIN
5842: --This SQL Query would provide all the instruction details for the specified process ID in XMLType.
5843: SELECT XMLELEMENT("INSTRUCTIONS",XMLAGG(XMLELEMENT("INSTRUCTION_SET_DETAILS",
5844: XMLFOREST(INSTR_SET.INSTRUCTION_PROCESS_ID AS INSTRUCTION_PROCESS_ID,
5845: INSTR_SET.INSTR_SET_NAME AS INSTRUCTION_SET_NAME,
5846: INSTR_SET.INSTR_SET_DESC AS INSTRUCTION_SET_DESC,
5847: (SELECT
5852: LK.LOOKUP_TYPE = 'GMO_INSTR_'||INSTR_SET.ENTITY_NAME
5853: AND
5854: LK.LOOKUP_CODE = INSTR_SET.INSTRUCTION_TYPE) AS INSTRUCTION_TYPE,
5855: (SELECT
5856: XMLAGG(XMLELEMENT("INSTRUCTION_DETAILS",
5857: XMLFOREST(INSTR.INSTR_NUMBER AS INSTRUCTION_NUMBER,
5858: INSTR.INSTRUCTION_TEXT AS INSTRUCTION_TEXT,
5859: (SELECT
5860: LK1.MEANING
6263:
6264: BEGIN
6265:
6266: --This SQLX query will fetch us the required XML.
6267: SELECT XMLELEMENT("INSTRUCTION_SET",XMLAGG(XMLELEMENT("INSTRUCTION_SET_DETAILS",
6268: XMLFOREST(INSTR_SET.INSTRUCTION_PROCESS_ID AS INSTRUCTION_PROCESS_ID,
6269: INSTR_SETVL.INSTR_SET_NAME AS INSTRUCTION_SET_NAME,
6270: INSTR_SETVL.INSTR_SET_DESC AS INSTRUCTION_SET_DESC,
6271: (SELECT
6276: LK.LOOKUP_TYPE = 'GMO_INSTR_'||INSTR_SETVL.ENTITY_NAME
6277: AND
6278: LK.LOOKUP_CODE = INSTR_SETVL.INSTRUCTION_TYPE) AS INSTRUCTION_TYPE,
6279: (SELECT
6280: XMLAGG(XMLELEMENT("INSTRUCTION_DETAILS",XMLFOREST(INSTRVL.INSTRUCTION_TEXT,
6281: (SELECT
6282: MEANING
6283: FROM
6284: FND_LOOKUP_VALUES_VL LK1
6306: GMO_INSTR_EREC_INSTANCE EREC
6307: WHERE
6308: EREC.INSTRUCTION_ID = INSTR.INSTRUCTION_ID)
6309: AS INSTRUCTION_ERECORD_ID,
6310: (SELECT XMLAGG(XMLELEMENT("TASK_DETAILS",XMLFOREST(TK.TASK_EREC_ID AS TASK_ERECORD_ID,
6311: TK.TASK_IDENTIFIER AS TASK_IDENTIFIER,
6312: TK.TASK_VALUE AS TASK_VALUE)))
6313: FROM
6314: GMO_INSTR_TASK_INSTANCE TK