VIEW: APPS.XDP_ADAPTER_SUMMARY_V

Source

View Text - Preformatted

SELECT XFE.fe_id fe_id ,XFE.fulfillment_element_name fe_name ,XFE.display_name display_name ,COUNT(XAR.fe_id) num_of_adapters ,NVL(AVG(XAJQ.num_of_jobs),0) num_of_jobs FROM xdp_fes_vl XFE ,xdp_adapter_reg XAR ,(SELECT fe_id fe_id ,COUNT(fe_id) num_of_jobs FROM xdp_adapter_job_queue GROUP BY fe_id) XAJQ ,xdp_fe_generic_config XFGC ,xdp_fe_types XFET ,xdp_fe_sw_gen_lookup XFSGL WHERE XAR.fe_id = XAJQ.fe_id(+) AND XFE.fe_id = XAR.fe_id (+) AND XFE.fe_id = XFGC.fe_id AND XFE.fetype_id = XFET.fetype_id AND XFGC.fe_sw_gen_lookup_id = XFSGL.fe_sw_gen_lookup_id AND XFGC.start_date = (SELECT MAX( A.START_DATE ) FROM xdp_fe_generic_config A WHERE A.fe_id = XFE.fe_id AND SYSDATE >= A.start_date AND SYSDATE <= NVL(A.end_date,SYSDATE)) GROUP BY XFE.fe_id ,XFE.fulfillment_element_name ,XFE.display_name

View Text - HTML Formatted

SELECT XFE.FE_ID FE_ID
, XFE.FULFILLMENT_ELEMENT_NAME FE_NAME
, XFE.DISPLAY_NAME DISPLAY_NAME
, COUNT(XAR.FE_ID) NUM_OF_ADAPTERS
, NVL(AVG(XAJQ.NUM_OF_JOBS)
, 0) NUM_OF_JOBS
FROM XDP_FES_VL XFE
, XDP_ADAPTER_REG XAR
, (SELECT FE_ID FE_ID
, COUNT(FE_ID) NUM_OF_JOBS
FROM XDP_ADAPTER_JOB_QUEUE GROUP BY FE_ID) XAJQ
, XDP_FE_GENERIC_CONFIG XFGC
, XDP_FE_TYPES XFET
, XDP_FE_SW_GEN_LOOKUP XFSGL
WHERE XAR.FE_ID = XAJQ.FE_ID(+)
AND XFE.FE_ID = XAR.FE_ID (+)
AND XFE.FE_ID = XFGC.FE_ID
AND XFE.FETYPE_ID = XFET.FETYPE_ID
AND XFGC.FE_SW_GEN_LOOKUP_ID = XFSGL.FE_SW_GEN_LOOKUP_ID
AND XFGC.START_DATE = (SELECT MAX( A.START_DATE )
FROM XDP_FE_GENERIC_CONFIG A
WHERE A.FE_ID = XFE.FE_ID
AND SYSDATE >= A.START_DATE
AND SYSDATE <= NVL(A.END_DATE
, SYSDATE)) GROUP BY XFE.FE_ID
, XFE.FULFILLMENT_ELEMENT_NAME
, XFE.DISPLAY_NAME