[Home] [Help]
5817: L_INSTR_XML XMLTYPE;
5818:
5819: BEGIN
5820: --This SQL Query would provide all the instruction details for the specified process ID in XMLType.
5821: SELECT XMLELEMENT("INSTRUCTIONS",XMLAGG(XMLELEMENT("INSTRUCTION_SET_DETAILS",
5822: XMLFOREST(INSTR_SET.INSTRUCTION_PROCESS_ID AS INSTRUCTION_PROCESS_ID,
5823: INSTR_SET.INSTR_SET_NAME AS INSTRUCTION_SET_NAME,
5824: INSTR_SET.INSTR_SET_DESC AS INSTRUCTION_SET_DESC,
5825: (SELECT
5830: LK.LOOKUP_TYPE = 'GMO_INSTR_'||INSTR_SET.ENTITY_NAME
5831: AND
5832: LK.LOOKUP_CODE = INSTR_SET.INSTRUCTION_TYPE) AS INSTRUCTION_TYPE,
5833: (SELECT
5834: XMLAGG(XMLELEMENT("INSTRUCTION_DETAILS",
5835: XMLFOREST(INSTR.INSTR_NUMBER AS INSTRUCTION_NUMBER,
5836: INSTR.INSTRUCTION_TEXT AS INSTRUCTION_TEXT,
5837: (SELECT
5838: LK1.MEANING
6240:
6241: BEGIN
6242:
6243: --This SQLX query will fetch us the required XML.
6244: SELECT XMLELEMENT("INSTRUCTION_SET",XMLAGG(XMLELEMENT("INSTRUCTION_SET_DETAILS",
6245: XMLFOREST(INSTR_SET.INSTRUCTION_PROCESS_ID AS INSTRUCTION_PROCESS_ID,
6246: INSTR_SETVL.INSTR_SET_NAME AS INSTRUCTION_SET_NAME,
6247: INSTR_SETVL.INSTR_SET_DESC AS INSTRUCTION_SET_DESC,
6248: (SELECT
6253: LK.LOOKUP_TYPE = 'GMO_INSTR_'||INSTR_SETVL.ENTITY_NAME
6254: AND
6255: LK.LOOKUP_CODE = INSTR_SETVL.INSTRUCTION_TYPE) AS INSTRUCTION_TYPE,
6256: (SELECT
6257: XMLAGG(XMLELEMENT("INSTRUCTION_DETAILS",XMLFOREST(INSTRVL.INSTRUCTION_TEXT,
6258: (SELECT
6259: MEANING
6260: FROM
6261: FND_LOOKUP_VALUES_VL LK1
6283: GMO_INSTR_EREC_INSTANCE EREC
6284: WHERE
6285: EREC.INSTRUCTION_ID = INSTR.INSTRUCTION_ID)
6286: AS INSTRUCTION_ERECORD_ID,
6287: (SELECT XMLAGG(XMLELEMENT("TASK_DETAILS",XMLFOREST(TK.TASK_EREC_ID AS TASK_ERECORD_ID,
6288: TK.TASK_IDENTIFIER AS TASK_IDENTIFIER,
6289: TK.TASK_VALUE AS TASK_VALUE)))
6290: FROM
6291: GMO_INSTR_TASK_INSTANCE TK