VIEW: APPS.OZF_CLAIM_REQ_HEADER_V

Source

View Text - Preformatted

SELECT rh.request_header_id , rh.request_number , rh.request_name , lk_req.meaning , rh.start_date , rh.end_date , rh.status_code , med.media_type_name , rh.agreement_number , rh.approved_amount , (SELECT SUM(lu.plan_curr_amount) FROM ozf_claim_lines_util_all lu , ozf_claim_lines_all cl WHERE lu.claim_line_id = cl.claim_line_id AND cl.activity_type IN ('SOFT_FUND', 'SPECIAL_PRICE') AND cl.activity_id = rh.request_header_id ) , (SELECT SUM(lu.plan_curr_amount) FROM ozf_claim_lines_util_all lu , ozf_claim_lines_all cl , ozf_claims_all oc WHERE lu.claim_line_id = cl.claim_line_id AND cl.claim_id = oc.claim_id AND oc.status_code = 'CLOSED' AND cl.activity_type IN ('SOFT_FUND', 'SPECIAL_PRICE') AND cl.activity_id = rh.request_header_id) , (SELECT SUM(plan_curr_amount_remaining) FROM ozf_funds_utilized_all_vl fu WHERE fu.reference_type IN ('SOFT_FUND', 'SPECIAL_PRICE') AND fu.reference_id = rh.request_header_id AND fu.plan_type = 'OFFR' AND fu.plan_id = rh.offer_id) , rh.currency_code FROM ozf_request_headers_all_vl rh , ozf_lookups lk_req , ams_media_vl med WHERE lk_req.lookup_code = rh.request_type_code AND lk_req.lookup_type = 'OZF_REQUEST_TYPE' AND rh.activity_media_id = med.media_id (+)

View Text - HTML Formatted

SELECT RH.REQUEST_HEADER_ID
, RH.REQUEST_NUMBER
, RH.REQUEST_NAME
, LK_REQ.MEANING
, RH.START_DATE
, RH.END_DATE
, RH.STATUS_CODE
, MED.MEDIA_TYPE_NAME
, RH.AGREEMENT_NUMBER
, RH.APPROVED_AMOUNT
, (SELECT SUM(LU.PLAN_CURR_AMOUNT)
FROM OZF_CLAIM_LINES_UTIL_ALL LU
, OZF_CLAIM_LINES_ALL CL
WHERE LU.CLAIM_LINE_ID = CL.CLAIM_LINE_ID
AND CL.ACTIVITY_TYPE IN ('SOFT_FUND'
, 'SPECIAL_PRICE')
AND CL.ACTIVITY_ID = RH.REQUEST_HEADER_ID )
, (SELECT SUM(LU.PLAN_CURR_AMOUNT)
FROM OZF_CLAIM_LINES_UTIL_ALL LU
, OZF_CLAIM_LINES_ALL CL
, OZF_CLAIMS_ALL OC
WHERE LU.CLAIM_LINE_ID = CL.CLAIM_LINE_ID
AND CL.CLAIM_ID = OC.CLAIM_ID
AND OC.STATUS_CODE = 'CLOSED'
AND CL.ACTIVITY_TYPE IN ('SOFT_FUND'
, 'SPECIAL_PRICE')
AND CL.ACTIVITY_ID = RH.REQUEST_HEADER_ID)
, (SELECT SUM(PLAN_CURR_AMOUNT_REMAINING)
FROM OZF_FUNDS_UTILIZED_ALL_VL FU
WHERE FU.REFERENCE_TYPE IN ('SOFT_FUND'
, 'SPECIAL_PRICE')
AND FU.REFERENCE_ID = RH.REQUEST_HEADER_ID
AND FU.PLAN_TYPE = 'OFFR'
AND FU.PLAN_ID = RH.OFFER_ID)
, RH.CURRENCY_CODE
FROM OZF_REQUEST_HEADERS_ALL_VL RH
, OZF_LOOKUPS LK_REQ
, AMS_MEDIA_VL MED
WHERE LK_REQ.LOOKUP_CODE = RH.REQUEST_TYPE_CODE
AND LK_REQ.LOOKUP_TYPE = 'OZF_REQUEST_TYPE'
AND RH.ACTIVITY_MEDIA_ID = MED.MEDIA_ID (+)