eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OZF_FUNDS_PAID_V

Source

View Text - Preformatted


SELECT util.utilization_id, util.fund_id, util.plan_type, util.plan_id, util.component_type, util.component_id, null object_type, null claim_num, util.adjustment_date, hz.party_name, null payment_method, util.plan_curr_amount, TO_NUMBER(null), TO_NUMBER(null), TO_NUMBER(null), util.currency_code, util.amount,TO_NUMBER(null), util.object_type doc_type, util.object_id doc_id, util.product_level_type, util.product_id, util.gl_posted_flag, util.univ_curr_amount, util.acctd_amount FROM ozf_funds_utilized_all_b util, hz_cust_accounts hza,hz_parties hz WHERE utilization_type = 'UTILIZED' AND util.cust_account_id = hza.cust_account_id(+) AND hza.party_id = hz.party_id(+) AND NVL(util.gl_posted_flag,'Y') = 'Y' UNION ALL SELECT cuti.utilization_id, util.fund_id, util.plan_type, util.plan_id, util.component_type, util.component_id, cty.name, cla.claim_number, cla.settled_date, hz.party_name, cla.payment_method, cuti.plan_curr_amount, cla.claim_id, cuti.claim_line_id ,cla.custom_setup_id, util.currency_code, cuti.util_curr_amount, cuti.claim_line_util_id, util.object_type doc_type, util.object_id doc_id, util.product_level_type, util.product_id, util.gl_posted_flag, cuti.univ_curr_amount, cuti.acctd_amount FROM ozf_funds_utilized_all_b util, ozf_claim_lines_util_all cuti, ozf_claim_lines_all cln, ozf_claims_all cla , hz_cust_accounts hza, hz_parties hz, ozf_claim_types_all_vl cty WHERE util.utilization_id = cuti.utilization_id AND util.utilization_type IN ('ACCRUAL','SALES_ACCRUAL','ADJUSTMENT', 'CHARGEBACK', 'LEAD_ACCRUAL') AND cuti.claim_line_id = cln.claim_line_id AND cln.claim_id = cla.claim_id AND cla.status_code = 'CLOSED' AND cla.cust_account_id = hza.cust_account_id(+) AND cty.claim_type_id = cla.claim_type_id AND hza.party_id = hz.party_id(+) UNION ALL SELECT util.utilization_id, util.fund_id, util.plan_type, util.plan_id, util.component_type, util.component_id, null object_type, null claim_num, util.adjustment_date, hz.party_name, null payment_method, util.plan_curr_amount, TO_NUMBER(null), TO_NUMBER(null), TO_NUMBER(null), util.currency_code, util.amount,TO_NUMBER(null), util.object_type doc_type, util.object_id doc_id, util.product_level_type, util.product_id, util.gl_posted_flag, util.univ_curr_amount, util.acctd_amount FROM ozf_funds_utilized_all_b util, hz_cust_accounts hza,hz_parties hz WHERE utilization_type = 'ADJUSTMENT'AND util.cust_account_id = hza.cust_account_id(+) AND hza.party_id = hz.party_id(+) AND util.gl_posted_flag is NULL UNION ALL SELECT util.utilization_id, util.fund_id, util.plan_type, util.plan_id, util.component_type, util.component_id, null object_type, null claim_num, NULL adjustment_date, hz.party_name, 'ADJUSTMENT' payment_method, - SUM(util.plan_curr_amount_remaining + NVL(cuti.plan_curr_amount,0)), TO_NUMBER(null), TO_NUMBER(null), TO_NUMBER(null), util.currency_code, - SUM(util.amount_remaining + NVL(cuti.util_curr_amount,0)), TO_NUMBER(null), util.object_type doc_type, util.object_id doc_id, util.product_level_type, util.product_id, util.gl_posted_flag, - SUM(util.univ_curr_amount_remaining + NVL(cuti.univ_curr_amount,0)), - SUM(util.acctd_amount_remaining + NVL(cuti.acctd_amount,0)) FROM ozf_funds_utilized_all_b util, hz_cust_accounts hza, hz_parties hz, ozf_claim_lines_util_all cuti WHERE utilization_type = 'ADJUSTMENT' AND util.adjustment_type IN ('INCREASE_PAID', 'DECREASE_PAID') AND util.cust_account_id = hza.cust_account_id AND hza.party_id = hz.party_id AND util.gl_posted_flag = 'Y' AND util.utilization_id = cuti.utilization_id(+) GROUP BY util.utilization_id, util.fund_id, util.plan_type, util.plan_id, util.component_type, util.component_id, hz.party_name, util.currency_code, util.object_type, util.object_id, util.product_level_type, util.product_id, util.gl_posted_flag

View Text - HTML Formatted

SELECT UTIL.UTILIZATION_ID
, UTIL.FUND_ID
, UTIL.PLAN_TYPE
, UTIL.PLAN_ID
, UTIL.COMPONENT_TYPE
, UTIL.COMPONENT_ID
, NULL OBJECT_TYPE
, NULL CLAIM_NUM
, UTIL.ADJUSTMENT_DATE
, HZ.PARTY_NAME
, NULL PAYMENT_METHOD
, UTIL.PLAN_CURR_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, UTIL.CURRENCY_CODE
, UTIL.AMOUNT
, TO_NUMBER(NULL)
, UTIL.OBJECT_TYPE DOC_TYPE
, UTIL.OBJECT_ID DOC_ID
, UTIL.PRODUCT_LEVEL_TYPE
, UTIL.PRODUCT_ID
, UTIL.GL_POSTED_FLAG
, UTIL.UNIV_CURR_AMOUNT
, UTIL.ACCTD_AMOUNT
FROM OZF_FUNDS_UTILIZED_ALL_B UTIL
, HZ_CUST_ACCOUNTS HZA
, HZ_PARTIES HZ
WHERE UTILIZATION_TYPE = 'UTILIZED'
AND UTIL.CUST_ACCOUNT_ID = HZA.CUST_ACCOUNT_ID(+)
AND HZA.PARTY_ID = HZ.PARTY_ID(+)
AND NVL(UTIL.GL_POSTED_FLAG
, 'Y') = 'Y' UNION ALL SELECT CUTI.UTILIZATION_ID
, UTIL.FUND_ID
, UTIL.PLAN_TYPE
, UTIL.PLAN_ID
, UTIL.COMPONENT_TYPE
, UTIL.COMPONENT_ID
, CTY.NAME
, CLA.CLAIM_NUMBER
, CLA.SETTLED_DATE
, HZ.PARTY_NAME
, CLA.PAYMENT_METHOD
, CUTI.PLAN_CURR_AMOUNT
, CLA.CLAIM_ID
, CUTI.CLAIM_LINE_ID
, CLA.CUSTOM_SETUP_ID
, UTIL.CURRENCY_CODE
, CUTI.UTIL_CURR_AMOUNT
, CUTI.CLAIM_LINE_UTIL_ID
, UTIL.OBJECT_TYPE DOC_TYPE
, UTIL.OBJECT_ID DOC_ID
, UTIL.PRODUCT_LEVEL_TYPE
, UTIL.PRODUCT_ID
, UTIL.GL_POSTED_FLAG
, CUTI.UNIV_CURR_AMOUNT
, CUTI.ACCTD_AMOUNT
FROM OZF_FUNDS_UTILIZED_ALL_B UTIL
, OZF_CLAIM_LINES_UTIL_ALL CUTI
, OZF_CLAIM_LINES_ALL CLN
, OZF_CLAIMS_ALL CLA
, HZ_CUST_ACCOUNTS HZA
, HZ_PARTIES HZ
, OZF_CLAIM_TYPES_ALL_VL CTY
WHERE UTIL.UTILIZATION_ID = CUTI.UTILIZATION_ID
AND UTIL.UTILIZATION_TYPE IN ('ACCRUAL'
, 'SALES_ACCRUAL'
, 'ADJUSTMENT'
, 'CHARGEBACK'
, 'LEAD_ACCRUAL')
AND CUTI.CLAIM_LINE_ID = CLN.CLAIM_LINE_ID
AND CLN.CLAIM_ID = CLA.CLAIM_ID
AND CLA.STATUS_CODE = 'CLOSED'
AND CLA.CUST_ACCOUNT_ID = HZA.CUST_ACCOUNT_ID(+)
AND CTY.CLAIM_TYPE_ID = CLA.CLAIM_TYPE_ID
AND HZA.PARTY_ID = HZ.PARTY_ID(+) UNION ALL SELECT UTIL.UTILIZATION_ID
, UTIL.FUND_ID
, UTIL.PLAN_TYPE
, UTIL.PLAN_ID
, UTIL.COMPONENT_TYPE
, UTIL.COMPONENT_ID
, NULL OBJECT_TYPE
, NULL CLAIM_NUM
, UTIL.ADJUSTMENT_DATE
, HZ.PARTY_NAME
, NULL PAYMENT_METHOD
, UTIL.PLAN_CURR_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, UTIL.CURRENCY_CODE
, UTIL.AMOUNT
, TO_NUMBER(NULL)
, UTIL.OBJECT_TYPE DOC_TYPE
, UTIL.OBJECT_ID DOC_ID
, UTIL.PRODUCT_LEVEL_TYPE
, UTIL.PRODUCT_ID
, UTIL.GL_POSTED_FLAG
, UTIL.UNIV_CURR_AMOUNT
, UTIL.ACCTD_AMOUNT
FROM OZF_FUNDS_UTILIZED_ALL_B UTIL
, HZ_CUST_ACCOUNTS HZA
, HZ_PARTIES HZ
WHERE UTILIZATION_TYPE = 'ADJUSTMENT'AND UTIL.CUST_ACCOUNT_ID = HZA.CUST_ACCOUNT_ID(+)
AND HZA.PARTY_ID = HZ.PARTY_ID(+)
AND UTIL.GL_POSTED_FLAG IS NULL UNION ALL SELECT UTIL.UTILIZATION_ID
, UTIL.FUND_ID
, UTIL.PLAN_TYPE
, UTIL.PLAN_ID
, UTIL.COMPONENT_TYPE
, UTIL.COMPONENT_ID
, NULL OBJECT_TYPE
, NULL CLAIM_NUM
, NULL ADJUSTMENT_DATE
, HZ.PARTY_NAME
, 'ADJUSTMENT' PAYMENT_METHOD
, - SUM(UTIL.PLAN_CURR_AMOUNT_REMAINING + NVL(CUTI.PLAN_CURR_AMOUNT
, 0))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, UTIL.CURRENCY_CODE
, - SUM(UTIL.AMOUNT_REMAINING + NVL(CUTI.UTIL_CURR_AMOUNT
, 0))
, TO_NUMBER(NULL)
, UTIL.OBJECT_TYPE DOC_TYPE
, UTIL.OBJECT_ID DOC_ID
, UTIL.PRODUCT_LEVEL_TYPE
, UTIL.PRODUCT_ID
, UTIL.GL_POSTED_FLAG
, - SUM(UTIL.UNIV_CURR_AMOUNT_REMAINING + NVL(CUTI.UNIV_CURR_AMOUNT
, 0))
, - SUM(UTIL.ACCTD_AMOUNT_REMAINING + NVL(CUTI.ACCTD_AMOUNT
, 0))
FROM OZF_FUNDS_UTILIZED_ALL_B UTIL
, HZ_CUST_ACCOUNTS HZA
, HZ_PARTIES HZ
, OZF_CLAIM_LINES_UTIL_ALL CUTI
WHERE UTILIZATION_TYPE = 'ADJUSTMENT'
AND UTIL.ADJUSTMENT_TYPE IN ('INCREASE_PAID'
, 'DECREASE_PAID')
AND UTIL.CUST_ACCOUNT_ID = HZA.CUST_ACCOUNT_ID
AND HZA.PARTY_ID = HZ.PARTY_ID
AND UTIL.GL_POSTED_FLAG = 'Y'
AND UTIL.UTILIZATION_ID = CUTI.UTILIZATION_ID(+) GROUP BY UTIL.UTILIZATION_ID
, UTIL.FUND_ID
, UTIL.PLAN_TYPE
, UTIL.PLAN_ID
, UTIL.COMPONENT_TYPE
, UTIL.COMPONENT_ID
, HZ.PARTY_NAME
, UTIL.CURRENCY_CODE
, UTIL.OBJECT_TYPE
, UTIL.OBJECT_ID
, UTIL.PRODUCT_LEVEL_TYPE
, UTIL.PRODUCT_ID
, UTIL.GL_POSTED_FLAG