VIEW: APPS.IGS_DA_XML_CONTROL_V

Source

View Text - Preformatted

SELECT dr.batch_id, hp.party_number, (select to_char(to_date(drf.feature_value, 'YYYY-MM-DD'), 'yyyy-mm-dd') from IGS_DA_REQ_FTRS drf WHERE drf.feature_code = 'DAT' and drf.batch_id = dr.batch_id), (select to_char(to_date(drf.feature_value, 'YYYY-MM-DD'), 'HH24:MI:SS') from IGS_DA_REQ_FTRS drf where drf.feature_code = 'DAT' and drf.batch_id = dr.batch_id), (select drf.feature_value from IGS_DA_REQ_FTRS drf where drf.feature_code = 'SRV' and drf.batch_id = dr.batch_id), (select drf.feature_value from IGS_DA_REQ_FTRS drf where drf.feature_code = 'COM' and drf.batch_id = dr.batch_id), (select drf.feature_value from IGS_DA_REQ_FTRS drf where drf.feature_code = 'PNT' and drf.batch_id = dr.batch_id), (select drf.feature_value from IGS_DA_REQ_FTRS drf where drf.feature_code = 'SZE' and drf.batch_id = dr.batch_id), nvl ( (SELECT drf.feature_value FROM igs_da_req_ftrs drf WHERE drf.feature_code = 'RPT' AND drf.batch_id = dr.batch_id),' '), nvl ( (SELECT drf.feature_value FROM igs_da_req_ftrs drf WHERE drf.feature_code = 'RQT' AND drf.batch_id = dr.batch_id), ' ') request_type, '', '', '', (select drf.feature_value from IGS_DA_REQ_FTRS drf where drf.feature_code = 'LIN' and drf.batch_id = dr.batch_id), decode((select drf.feature_value from IGS_DA_REQ_FTRS drf where drf.feature_code = 'DMP' and drf.batch_id = dr.batch_id),'Y', 'true', 'false'), (select drf.feature_value from IGS_DA_REQ_FTRS drf where drf.feature_code = 'DPM' and drf.batch_id = dr.batch_id), (select drf.feature_value from IGS_DA_REQ_FTRS drf where drf.feature_code = 'FMT' and drf.batch_id = dr.batch_id), dr.batch_id, hp1.party_number FROM igs_da_rqst dr, igs_da_cnfg_req_typ dcrt, hz_parties hp, igs_pe_hz_parties ihp, hz_parties hp1 WHERE dr.requestor_id = hp.party_id AND dr.request_type_id = dcrt.request_type_id AND ihp.oi_local_institution_ind = 'Y' AND hp1.party_id = ihp.party_id AND ihp.inst_org_ind = 'I'

View Text - HTML Formatted

SELECT DR.BATCH_ID
, HP.PARTY_NUMBER
, (SELECT TO_CHAR(TO_DATE(DRF.FEATURE_VALUE
, 'YYYY-MM-DD')
, 'YYYY-MM-DD')
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.FEATURE_CODE = 'DAT'
AND DRF.BATCH_ID = DR.BATCH_ID)
, (SELECT TO_CHAR(TO_DATE(DRF.FEATURE_VALUE
, 'YYYY-MM-DD')
, 'HH24:MI:SS')
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.FEATURE_CODE = 'DAT'
AND DRF.BATCH_ID = DR.BATCH_ID)
, (SELECT DRF.FEATURE_VALUE
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.FEATURE_CODE = 'SRV'
AND DRF.BATCH_ID = DR.BATCH_ID)
, (SELECT DRF.FEATURE_VALUE
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.FEATURE_CODE = 'COM'
AND DRF.BATCH_ID = DR.BATCH_ID)
, (SELECT DRF.FEATURE_VALUE
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.FEATURE_CODE = 'PNT'
AND DRF.BATCH_ID = DR.BATCH_ID)
, (SELECT DRF.FEATURE_VALUE
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.FEATURE_CODE = 'SZE'
AND DRF.BATCH_ID = DR.BATCH_ID)
, NVL ( (SELECT DRF.FEATURE_VALUE
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.FEATURE_CODE = 'RPT'
AND DRF.BATCH_ID = DR.BATCH_ID)
, ' ')
, NVL ( (SELECT DRF.FEATURE_VALUE
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.FEATURE_CODE = 'RQT'
AND DRF.BATCH_ID = DR.BATCH_ID)
, ' ') REQUEST_TYPE
, ''
, ''
, ''
, (SELECT DRF.FEATURE_VALUE
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.FEATURE_CODE = 'LIN'
AND DRF.BATCH_ID = DR.BATCH_ID)
, DECODE((SELECT DRF.FEATURE_VALUE
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.FEATURE_CODE = 'DMP'
AND DRF.BATCH_ID = DR.BATCH_ID)
, 'Y'
, 'TRUE'
, 'FALSE')
, (SELECT DRF.FEATURE_VALUE
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.FEATURE_CODE = 'DPM'
AND DRF.BATCH_ID = DR.BATCH_ID)
, (SELECT DRF.FEATURE_VALUE
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.FEATURE_CODE = 'FMT'
AND DRF.BATCH_ID = DR.BATCH_ID)
, DR.BATCH_ID
, HP1.PARTY_NUMBER
FROM IGS_DA_RQST DR
, IGS_DA_CNFG_REQ_TYP DCRT
, HZ_PARTIES HP
, IGS_PE_HZ_PARTIES IHP
, HZ_PARTIES HP1
WHERE DR.REQUESTOR_ID = HP.PARTY_ID
AND DR.REQUEST_TYPE_ID = DCRT.REQUEST_TYPE_ID
AND IHP.OI_LOCAL_INSTITUTION_IND = 'Y'
AND HP1.PARTY_ID = IHP.PARTY_ID
AND IHP.INST_ORG_IND = 'I'