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_OBJECT_CHECKBOOK_V

Source

View Text - Preformatted


SELECT DECODE(act1.transfer_type,'TRANSFER',act1.budget_source_type, act1.arc_act_budget_used_by), DECODE(act1.transfer_type,'TRANSFER',act1.budget_source_id, act1.act_budget_used_by_id) , DECODE (act1.status_code, 'PENDING', act1.request_amount), DECODE(act1.status_code,'APPROVED',DECODE( act1.transfer_type ,'REQUEST', act1.approved_amount, 0-act1.approved_original_amount)), TO_NUMBER(null), TO_NUMBER(null), TO_NUMBER(null), DECODE (act1.status_code, 'PENDING', act1.src_curr_request_amt), DECODE(act1.status_code,'APPROVED',DECODE( act1.transfer_type ,'REQUEST', act1.approved_original_amount, 0-act1. approved_amount)), TO_NUMBER(null), TO_NUMBER(null), TO_NUMBER(null), DECODE(act1.transfer_type,'REQUEST',act1.budget_source_id, act1.act_budget_used_by_id), NVL(act1.recal_flag,'N') from ozf_act_budgets act1 WHERE act1.transfer_type IN('REQUEST', 'TRANSFER') AND act1.arc_act_budget_used_by <> act1.budget_source_type AND act1.status_code IN ('APPROVED','PENDING','PENDING_VALIDATION') AND act1.parent_act_budget_id is null UNION ALL SELECT uti.plan_type, uti.plan_id, TO_NUMBER(null), TO_NUMBER(null), uti.plan_curr_amount, DECODE(NVL(uti.gl_posted_flag,'Y'), 'Y',uti.plan_curr_amount), DECODE(uti.utilization_type,'UTILIZED',DECODE(NVL(uti.gl_posted_flag,'Y'),'Y', uti.plan_curr_amount-NVL(plan_curr_amount_remaining,0))), TO_NUMBER(null), TO_NUMBER(null), uti.amount, DECODE(NVL(uti.gl_posted_flag,'Y'), 'Y',uti.amount), DECODE(uti.utilization_type,'UTILIZED',DECODE(NVL(uti.gl_posted_flag,'Y'), 'Y',uti.amount-NVL(amount_remaining,0))), uti.fund_id, null from ozf_funds_utilized_all_vl uti WHERE uti.utilization_type IN ('UTILIZED','ACCRUAL','SALES_ACCRUAL','ADJUSTMENT', 'CHARGEBACK','LEAD_ACCRUAL') UNION ALL SELECT util.plan_type, util.plan_id, TO_NUMBER(null), TO_NUMBER(null), TO_NUMBER(null), TO_NUMBER(null), cuti.plan_curr_amount, TO_NUMBER(null), TO_NUMBER(null), TO_NUMBER(null), TO_NUMBER(null), cuti.util_curr_amount, util.fund_id, null FROM ozf_funds_utilized_all_b util, ozf_claim_lines_util_all cuti, ozf_claim_lines_all cln, ozf_claims_all cla 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' UNION ALL SELECT actof.arc_act_offer_used_by, actof.act_offer_used_by_id, TO_NUMBER(null), TO_NUMBER(null), uti.plan_curr_amount, uti.plan_curr_amount, uti.plan_curr_amount-NVL(plan_curr_amount_remaining,0), TO_NUMBER(null), TO_NUMBER(null), uti.amount, uti.amount, uti.amount-NVL(amount_remaining,0), uti.fund_id, null from ozf_funds_utilized_all_vl uti,ozf_Act_offers actof WHERE uti.utilization_type IN ('UTILIZED','ACCRUAL','SALES_ACCRUAL','ADJUSTMENT', 'CHARGEBACK','LEAD_ACCRUAL') AND uti.plan_id = actof.qp_list_header_id AND uti.plan_type ='OFFR' UNION ALL SELECT 'CAMP', sch.campaign_id, TO_NUMBER(null), TO_NUMBER(null), uti.plan_curr_amount, uti.plan_curr_amount, uti.plan_curr_amount-NVL(plan_curr_amount_remaining,0), TO_NUMBER(null), TO_NUMBER(null), uti.amount, uti.amount, uti.amount-NVL(amount_remaining,0), uti.fund_id, null from ozf_funds_utilized_all_vl uti,ams_campaign_schedules_vl sch WHERE uti.utilization_type = 'UTILIZED' AND sch.schedule_id = uti.plan_id AND uti.plan_type ='CSCH'

View Text - HTML Formatted

SELECT DECODE(ACT1.TRANSFER_TYPE
, 'TRANSFER'
, ACT1.BUDGET_SOURCE_TYPE
, ACT1.ARC_ACT_BUDGET_USED_BY)
, DECODE(ACT1.TRANSFER_TYPE
, 'TRANSFER'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID)
, DECODE (ACT1.STATUS_CODE
, 'PENDING'
, ACT1.REQUEST_AMOUNT)
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_AMOUNT
, 0-ACT1.APPROVED_ORIGINAL_AMOUNT))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE (ACT1.STATUS_CODE
, 'PENDING'
, ACT1.SRC_CURR_REQUEST_AMT)
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_ORIGINAL_AMOUNT
, 0-ACT1. APPROVED_AMOUNT))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID)
, NVL(ACT1.RECAL_FLAG
, 'N')
FROM OZF_ACT_BUDGETS ACT1
WHERE ACT1.TRANSFER_TYPE IN('REQUEST'
, 'TRANSFER')
AND ACT1.ARC_ACT_BUDGET_USED_BY <> ACT1.BUDGET_SOURCE_TYPE
AND ACT1.STATUS_CODE IN ('APPROVED'
, 'PENDING'
, 'PENDING_VALIDATION')
AND ACT1.PARENT_ACT_BUDGET_ID IS NULL UNION ALL SELECT UTI.PLAN_TYPE
, UTI.PLAN_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, UTI.PLAN_CURR_AMOUNT
, DECODE(NVL(UTI.GL_POSTED_FLAG
, 'Y')
, 'Y'
, UTI.PLAN_CURR_AMOUNT)
, DECODE(UTI.UTILIZATION_TYPE
, 'UTILIZED'
, DECODE(NVL(UTI.GL_POSTED_FLAG
, 'Y')
, 'Y'
, UTI.PLAN_CURR_AMOUNT-NVL(PLAN_CURR_AMOUNT_REMAINING
, 0)))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, UTI.AMOUNT
, DECODE(NVL(UTI.GL_POSTED_FLAG
, 'Y')
, 'Y'
, UTI.AMOUNT)
, DECODE(UTI.UTILIZATION_TYPE
, 'UTILIZED'
, DECODE(NVL(UTI.GL_POSTED_FLAG
, 'Y')
, 'Y'
, UTI.AMOUNT-NVL(AMOUNT_REMAINING
, 0)))
, UTI.FUND_ID
, NULL
FROM OZF_FUNDS_UTILIZED_ALL_VL UTI
WHERE UTI.UTILIZATION_TYPE IN ('UTILIZED'
, 'ACCRUAL'
, 'SALES_ACCRUAL'
, 'ADJUSTMENT'
, 'CHARGEBACK'
, 'LEAD_ACCRUAL') UNION ALL SELECT UTIL.PLAN_TYPE
, UTIL.PLAN_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CUTI.PLAN_CURR_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CUTI.UTIL_CURR_AMOUNT
, UTIL.FUND_ID
, NULL
FROM OZF_FUNDS_UTILIZED_ALL_B UTIL
, OZF_CLAIM_LINES_UTIL_ALL CUTI
, OZF_CLAIM_LINES_ALL CLN
, OZF_CLAIMS_ALL CLA
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' UNION ALL SELECT ACTOF.ARC_ACT_OFFER_USED_BY
, ACTOF.ACT_OFFER_USED_BY_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, UTI.PLAN_CURR_AMOUNT
, UTI.PLAN_CURR_AMOUNT
, UTI.PLAN_CURR_AMOUNT-NVL(PLAN_CURR_AMOUNT_REMAINING
, 0)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, UTI.AMOUNT
, UTI.AMOUNT
, UTI.AMOUNT-NVL(AMOUNT_REMAINING
, 0)
, UTI.FUND_ID
, NULL
FROM OZF_FUNDS_UTILIZED_ALL_VL UTI
, OZF_ACT_OFFERS ACTOF
WHERE UTI.UTILIZATION_TYPE IN ('UTILIZED'
, 'ACCRUAL'
, 'SALES_ACCRUAL'
, 'ADJUSTMENT'
, 'CHARGEBACK'
, 'LEAD_ACCRUAL')
AND UTI.PLAN_ID = ACTOF.QP_LIST_HEADER_ID
AND UTI.PLAN_TYPE ='OFFR' UNION ALL SELECT 'CAMP'
, SCH.CAMPAIGN_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, UTI.PLAN_CURR_AMOUNT
, UTI.PLAN_CURR_AMOUNT
, UTI.PLAN_CURR_AMOUNT-NVL(PLAN_CURR_AMOUNT_REMAINING
, 0)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, UTI.AMOUNT
, UTI.AMOUNT
, UTI.AMOUNT-NVL(AMOUNT_REMAINING
, 0)
, UTI.FUND_ID
, NULL
FROM OZF_FUNDS_UTILIZED_ALL_VL UTI
, AMS_CAMPAIGN_SCHEDULES_VL SCH
WHERE UTI.UTILIZATION_TYPE = 'UTILIZED'
AND SCH.SCHEDULE_ID = UTI.PLAN_ID
AND UTI.PLAN_TYPE ='CSCH'