DBA Data[Home] [Help]

VIEW: APPS.OZF_FUND_CHECKBOOK_V

Source

View Text - Preformatted

SELECT DECODE(act1.transfer_type,'REQUEST',act1.act_budget_used_by_id, act1.budget_source_id) , a.campaign_name, DECODE(act1.transfer_type,'REQUEST',act1.arc_act_budget_used_by, act1.budget_source_type) , a.custom_setup_id, a.source_code, a.status_code , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_amount, 0-act1.approved_original_amount)) , DECODE (act1.status_code, 'PENDING', act1.request_amount), a.owner_user_id , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_id,act1.act_budget_used_by_id) , DECODE(act1.transfer_type,'REQUEST',act1.budget_source_type, act1.arc_act_budget_used_by) , a.actual_exec_start_date, a.actual_exec_end_date , DECODE(act1.transfer_type,'REQUEST',act1.request_currency, act1.approved_in_currency), act1.activity_budget_id, TO_NUMBER(null),null component_type,null component_number,null component_type_code,null component_name, act1.parent_source_id,act1.parent_src_curr, DECODE ( act1.transfer_type ,'REQUEST', act1.parent_src_apprvd_amt, 0-act1.parent_src_apprvd_amt) , act1.status_code,act1.transfer_type,act1.recal_flag, DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_original_amount, 0-act1.approved_amount)),act1.src_curr_request_amt,fund.parent_fund_id FROM ams_campaigns_vl a, ozf_Act_budgets act1,ozf_funds_all_b fund WHERE DECODE(act1.transfer_type,'REQUEST',act1.act_budget_used_by_id, act1.budget_source_id) = a.campaign_id AND DECODE(act1.transfer_type,'REQUEST',act1.arc_act_budget_used_by, act1.budget_source_type) ='CAMP' AND act1.arc_act_budget_used_by <> act1.budget_source_type AND act1.status_code IN ('APPROVED','PENDING') AND DECODE(act1.transfer_type,'REQUEST',act1.budget_source_id, act1.act_budget_used_by_id) = fund.fund_id(+) UNION ALL SELECT sch.campaign_id,a.campaign_name,'CAMP',a.custom_setup_id,a.source_code, a.status_code , ozf_fund_adjustment_pvt.Convert_Currency(DECODE(act1.transfer_type,'REQUEST' ,act1.request_currency,act1.approved_in_currency), a.transaction_currency_code, DECODE(act1.status_code,'APPROVED', DECODE ( act1.transfer_type ,'REQUEST', act1.approved_amount, 0-act1.approved_original_amount))) , ozf_fund_adjustment_pvt.Convert_Currency(act1.request_currency, a.transaction_currency_code, DECODE (act1.status_code, 'PENDING', act1.request_amount)), a.owner_user_id, DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_id,act1.act_budget_used_by_id) , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_type,act1.arc_act_budget_used_by) , a.actual_exec_start_date, a.actual_exec_end_date, a.transaction_currency_code, act1.activity_budget_id, DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) , DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) , sch.source_code, sch.activity_type_code, sch.schedule_name, act1.parent_source_id, act1.parent_src_curr, DECODE ( act1.transfer_type ,'REQUEST', act1.parent_src_apprvd_amt, 0-act1.parent_src_apprvd_amt) , act1.status_code ,act1.transfer_type, act1.recal_flag , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_original_amount, 0-act1.approved_amount)) ,act1.src_curr_request_amt ,fund.parent_fund_id FROM ams_campaigns_vl a,ozf_act_budgets act1, ams_campaign_schedules_vl sch ,ozf_funds_all_b fund WHERE DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id)= sch.schedule_id AND DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) ='CSCH' AND DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_type,act1.arc_act_budget_used_by) ='FUND' AND act1.parent_act_budget_id is null AND sch.campaign_id = a.campaign_id AND act1.budget_source_type <> act1.arc_act_budget_used_by AND act1.status_code IN ('APPROVED','PENDING') AND DECODE(act1.transfer_type,'REQUEST',act1.budget_source_id, act1.act_budget_used_by_id) = fund.fund_id(+) UNION ALL SELECT DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) , qp.description, DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) , a.custom_setup_id, a.offer_code, a.status_code , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_amount, 0-act1.approved_original_amount)) , DECODE(act1.status_code, 'PENDING', act1.request_amount), a.owner_id , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_id,act1.act_budget_used_by_id) , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_type,act1.arc_act_budget_used_by) , qp.start_date_active, qp.end_date_active , DECODE(act1.transfer_type,'REQUEST',act1.request_currency, act1.approved_in_currency), act1.activity_budget_id, TO_NUMBER(null), null component_type,null component_number,null component_type_code,null component_name, act1.parent_source_id,act1.parent_src_curr, DECODE ( act1.transfer_type ,'REQUEST', act1.parent_src_apprvd_amt, 0-act1.parent_src_apprvd_amt) , act1.status_code,act1.transfer_type,act1.recal_flag , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_original_amount, 0-act1.approved_amount)),act1.src_curr_request_amt ,fund.parent_fund_id FROM ozf_offers a, ozf_act_budgets act1,qp_list_headers_vl qp ,ozf_funds_all_b fund WHERE DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) = qp.list_header_id AND DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) ='OFFR' AND act1.parent_act_budget_id is null AND act1.arc_act_budget_used_by <> act1.budget_source_type AND act1.status_code IN ('APPROVED','PENDING','PENDING_VALIDATION') AND a.reusable = 'Y' AND qp.list_header_id = a.qp_list_header_id AND DECODE(act1.transfer_type,'REQUEST',act1.budget_source_id, act1.act_budget_used_by_id) = fund.fund_id(+) UNION ALL SELECT DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) , qp.description, DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) , a.custom_setup_id, a.offer_code, a.status_code , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_amount, 0-act1.approved_original_amount)) , DECODE (act1.status_code, 'PENDING', act1.request_amount), a.owner_id , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_id,act1.act_budget_used_by_id) , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_type,act1.arc_act_budget_used_by) , qp.start_date_active, qp.end_date_active , DECODE(act1.transfer_type,'REQUEST',act1.request_currency, act1.approved_in_currency), act1.activity_budget_id, TO_NUMBER(null), null component_type,null component_number,null component_type_code,null component_name, act1.parent_source_id,act1.parent_src_curr, DECODE ( act1.transfer_type ,'REQUEST', act1.parent_src_apprvd_amt, 0-act1.parent_src_apprvd_amt) , act1.status_code,act1.transfer_type,act1.recal_flag , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_original_amount, 0-act1.approved_amount)),act1.src_curr_request_amt ,fund.parent_fund_id FROM ozf_offers a, ozf_act_budgets act1,qp_list_headers_vl qp,ozf_funds_all_b fund WHERE DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) = qp.list_header_id AND DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) ='OFFR' AND act1.parent_act_budget_id is null AND act1.arc_act_budget_used_by <> act1.budget_source_type AND act1.status_code IN ('APPROVED','PENDING','PENDING_VALIDATION') AND a.reusable = 'N' AND qp.list_header_id = a.qp_list_header_id AND DECODE(act1.transfer_type ,'TRANSFER',act1.arc_act_budget_used_by,act1.budget_source_type) ='FUND' AND DECODE(act1.transfer_type,'REQUEST',act1.budget_source_id, act1.act_budget_used_by_id) = fund.fund_id(+) AND NOT EXISTS (SELECT 'X' from ozf_act_offers where qp_list_header_id = a.qp_list_header_id ) UNION ALL SELECT DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) , a.event_header_name, DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) , a.setup_type_id, a.source_code, a.system_status_code, DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_amount, 0-act1.approved_original_amount)) , DECODE (act1.status_code, 'PENDING', act1.request_amount), a.owner_user_id , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_id,act1.act_budget_used_by_id) , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_type,act1.arc_act_budget_used_by) , a.active_from_date, a.active_to_date , DECODE(act1.transfer_type,'REQUEST',act1.request_currency, act1.approved_in_currency), act1.activity_budget_id, TO_NUMBER(null) ,null component_type, null component_number, null component_type_code, null component_name, act1.parent_source_id, act1.parent_src_curr, DECODE ( act1.transfer_type ,'REQUEST', act1.parent_src_apprvd_amt, 0-act1.parent_src_apprvd_amt) , act1.status_code ,act1.transfer_type,act1.recal_flag , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_original_amount, 0-act1.approved_amount)),act1.src_curr_request_amt,fund.parent_fund_id FROM ams_event_headers_vl a, ozf_act_budgets act1 ,ozf_funds_all_b fund WHERE DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) = a.event_header_id AND DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) ='EVEH' AND act1.status_code IN ('APPROVED','PENDING') AND act1.arc_act_budget_used_by <> act1.budget_source_type AND DECODE(act1.transfer_type,'REQUEST',act1.budget_source_id, act1.act_budget_used_by_id) = fund.fund_id(+) UNION ALL SELECT DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) , a.event_offer_name, DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) , TO_NUMBER(null),a.source_code,a.system_status_code , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_amount, 0-act1.approved_original_amount)) , DECODE (act1.status_code, 'PENDING', act1.request_amount), a.owner_user_id , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_id,act1.act_budget_used_by_id) , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_type,act1.arc_act_budget_used_by) , a.event_start_date,a.event_end_date , DECODE(act1.transfer_type,'REQUEST',act1.request_currency, act1.approved_in_currency),act1.activity_budget_id, TO_NUMBER(null),null component_type,null component_number, null component_type_code,null component_name , act1.parent_source_id,act1.parent_src_curr,DECODE ( act1.transfer_type ,'REQUEST', act1.parent_src_apprvd_amt, 0-act1.parent_src_apprvd_amt) ,act1.status_code,act1.transfer_type,act1.recal_flag , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_original_amount, 0-act1.approved_amount)),act1.src_curr_request_amt,fund.parent_fund_id FROM ams_event_offers_vl a,ozf_act_budgets act1 ,ozf_funds_all_b fund WHERE DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) = a.event_offer_id AND (DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) ='EVEO' OR DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) ='EONE') AND act1.parent_act_budget_id is null AND act1.budget_source_type <> act1.arc_act_budget_used_by AND act1.status_code IN('APPROVED','PENDING') AND DECODE(act1.transfer_type ,'TRANSFER',act1.arc_act_budget_used_by,act1.budget_source_type) ='FUND' AND DECODE(act1.transfer_type,'REQUEST',act1.budget_source_id, act1.act_budget_used_by_id) = fund.fund_id(+) UNION ALL SELECT DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) , a.DELIVERABLE_name, DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) , a.custom_setup_id,a.version source_code,a.status_code , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_amount, 0-act1.approved_original_amount)) , DECODE (act1.status_code, 'PENDING', act1.request_amount), a.owner_user_id , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_id,act1.act_budget_used_by_id) , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_type,act1.arc_act_budget_used_by) , a.actual_avail_from_date,a.actual_avail_to_date , DECODE(act1.transfer_type,'REQUEST',act1.request_currency, act1.approved_in_currency), act1.activity_budget_id, TO_NUMBER(null),null component_type,null component_number, null component_type_code,null component_name,act1.parent_source_id,act1.parent_src_curr, DECODE (act1.transfer_type ,'REQUEST', act1.parent_src_apprvd_amt, 0-act1.parent_src_apprvd_amt) , act1.status_code,act1.transfer_type,act1.recal_flag , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_original_amount, 0-act1.approved_amount)),act1.src_curr_request_amt ,fund.parent_fund_id FROM ams_deliverables_vl a, ozf_act_budgets act1 ,ozf_funds_all_b fund WHERE DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) = a.deliverable_id AND DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) = 'DELV' AND act1.budget_source_type <> act1.arc_act_budget_used_by AND act1.status_code IN ('APPROVED','PENDING') AND DECODE(act1.transfer_type,'REQUEST',act1.budget_source_id, act1.act_budget_used_by_id) = fund.fund_id(+) UNION ALL SELECT actof.act_offer_used_by_id, a.campaign_name, 'CAMP', a.custom_setup_id, a.source_code, a.status_code , ozf_fund_adjustment_pvt.Convert_Currency(DECODE(act1.transfer_type,'REQUEST' ,act1.request_currency,act1.approved_in_currency), a.transaction_currency_code,DECODE(act1.status_code,'APPROVED', DECODE ( act1.transfer_type ,'REQUEST', act1.approved_amount, 0-act1.approved_original_amount))) , ozf_fund_adjustment_pvt.Convert_Currency(act1.request_currency, a.transaction_currency_code,DECODE (act1.status_code, 'PENDING', act1.request_amount)), a.owner_user_id , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_id,act1.act_budget_used_by_id) , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_type,act1.arc_act_budget_used_by) , a.actual_exec_start_date, a.actual_exec_end_date , a.transaction_currency_code, act1.activity_budget_id, DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) ,DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) , offs.offer_code, offs.offer_type, qp.description, act1.parent_source_id, act1.parent_src_curr, DECODE ( act1.transfer_type ,'REQUEST', act1.parent_src_apprvd_amt, 0-act1.parent_src_apprvd_amt) , act1.status_code ,act1.transfer_type,act1.recal_flag , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_original_amount, 0-act1.approved_amount)),act1.src_curr_request_amt,fund.parent_fund_id FROM ams_campaigns_vl a, ozf_act_budgets act1, ozf_Act_offers actof, ozf_offers offs,qp_list_headers_vl qp ,ozf_funds_all_b fund WHERE DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) = actof.qp_list_header_id AND DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) ='OFFR' AND offs.reusable = 'N' AND actof.act_offer_used_by_id = a.campaign_id AND actof.arc_act_offer_used_by = 'CAMP' AND act1.parent_act_budget_id is null AND act1.budget_source_type <> act1.arc_act_budget_used_by AND act1.status_code IN ('APPROVED','PENDING','PENDING_VALIDATION') AND actof.qp_list_header_id = offs.qp_list_header_id(+) AND actof.qp_list_header_id = qp.list_header_id(+) AND DECODE(act1.transfer_type,'REQUEST',act1.budget_source_id, act1.act_budget_used_by_id) = fund.fund_id(+) UNION ALL SELECT DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) , qp.description, DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) , TO_NUMBER(null) custom_setup_id, qp.name, null status_code , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_amount, 0-act1.approved_original_amount)) , DECODE(act1.status_code, 'PENDING', act1.request_amount), TO_NUMBER(null) owner_id , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_id,act1.act_budget_used_by_id) , DECODE(act1.transfer_type ,'REQUEST',act1.budget_source_type,act1.arc_act_budget_used_by) , qp.start_date_active, qp.end_date_active , DECODE(act1.transfer_type,'REQUEST',act1.request_currency, act1.approved_in_currency), act1.activity_budget_id, TO_NUMBER(null), null component_type,null component_number,null component_type_code,null component_name, act1.parent_source_id,act1.parent_src_curr, DECODE ( act1.transfer_type ,'REQUEST', act1.parent_src_apprvd_amt, 0-act1.parent_src_apprvd_amt) , act1.status_code,act1.transfer_type,act1.recal_flag , DECODE(act1.status_code,'APPROVED',DECODE ( act1.transfer_type ,'REQUEST', act1.approved_original_amount, 0-act1.approved_amount)),act1.src_curr_request_amt ,fund.parent_fund_id FROM ozf_act_budgets act1,qp_list_headers_vl qp ,ozf_funds_all_b fund WHERE DECODE(act1.transfer_type ,'REQUEST',act1.act_budget_used_by_id,act1.budget_source_id) = qp.list_header_id AND DECODE(act1.transfer_type ,'REQUEST',act1.arc_act_budget_used_by,act1.budget_source_type) ='PRIC' AND act1.arc_act_budget_used_by <> act1.budget_source_type AND act1.status_code IN ('APPROVED','PENDING') AND DECODE(act1.transfer_type,'REQUEST',act1.budget_source_id, act1.act_budget_used_by_id) = fund.fund_id(+)
View Text - HTML Formatted

SELECT DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID)
, A.CAMPAIGN_NAME
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE)
, A.CUSTOM_SETUP_ID
, A.SOURCE_CODE
, A.STATUS_CODE
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_AMOUNT
, 0-ACT1.APPROVED_ORIGINAL_AMOUNT))
, DECODE (ACT1.STATUS_CODE
, 'PENDING'
, ACT1.REQUEST_AMOUNT)
, A.OWNER_USER_ID
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID)
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_TYPE
, ACT1.ARC_ACT_BUDGET_USED_BY)
, A.ACTUAL_EXEC_START_DATE
, A.ACTUAL_EXEC_END_DATE
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.REQUEST_CURRENCY
, ACT1.APPROVED_IN_CURRENCY)
, ACT1.ACTIVITY_BUDGET_ID
, TO_NUMBER(NULL)
, NULL COMPONENT_TYPE
, NULL COMPONENT_NUMBER
, NULL COMPONENT_TYPE_CODE
, NULL COMPONENT_NAME
, ACT1.PARENT_SOURCE_ID
, ACT1.PARENT_SRC_CURR
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.PARENT_SRC_APPRVD_AMT
, 0-ACT1.PARENT_SRC_APPRVD_AMT)
, ACT1.STATUS_CODE
, ACT1.TRANSFER_TYPE
, ACT1.RECAL_FLAG
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_ORIGINAL_AMOUNT
, 0-ACT1.APPROVED_AMOUNT))
, ACT1.SRC_CURR_REQUEST_AMT
, FUND.PARENT_FUND_ID
FROM AMS_CAMPAIGNS_VL A
, OZF_ACT_BUDGETS ACT1
, OZF_FUNDS_ALL_B FUND
WHERE DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID) = A.CAMPAIGN_ID
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE) ='CAMP'
AND ACT1.ARC_ACT_BUDGET_USED_BY <> ACT1.BUDGET_SOURCE_TYPE
AND ACT1.STATUS_CODE IN ('APPROVED'
, 'PENDING')
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID) = FUND.FUND_ID(+) UNION ALL SELECT SCH.CAMPAIGN_ID
, A.CAMPAIGN_NAME
, 'CAMP'
, A.CUSTOM_SETUP_ID
, A.SOURCE_CODE
, A.STATUS_CODE
, OZF_FUND_ADJUSTMENT_PVT.CONVERT_CURRENCY(DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.REQUEST_CURRENCY
, ACT1.APPROVED_IN_CURRENCY)
, A.TRANSACTION_CURRENCY_CODE
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_AMOUNT
, 0-ACT1.APPROVED_ORIGINAL_AMOUNT)))
, OZF_FUND_ADJUSTMENT_PVT.CONVERT_CURRENCY(ACT1.REQUEST_CURRENCY
, A.TRANSACTION_CURRENCY_CODE
, DECODE (ACT1.STATUS_CODE
, 'PENDING'
, ACT1.REQUEST_AMOUNT))
, A.OWNER_USER_ID
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID)
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_TYPE
, ACT1.ARC_ACT_BUDGET_USED_BY)
, A.ACTUAL_EXEC_START_DATE
, A.ACTUAL_EXEC_END_DATE
, A.TRANSACTION_CURRENCY_CODE
, ACT1.ACTIVITY_BUDGET_ID
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID)
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE)
, SCH.SOURCE_CODE
, SCH.ACTIVITY_TYPE_CODE
, SCH.SCHEDULE_NAME
, ACT1.PARENT_SOURCE_ID
, ACT1.PARENT_SRC_CURR
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.PARENT_SRC_APPRVD_AMT
, 0-ACT1.PARENT_SRC_APPRVD_AMT)
, ACT1.STATUS_CODE
, ACT1.TRANSFER_TYPE
, ACT1.RECAL_FLAG
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_ORIGINAL_AMOUNT
, 0-ACT1.APPROVED_AMOUNT))
, ACT1.SRC_CURR_REQUEST_AMT
, FUND.PARENT_FUND_ID
FROM AMS_CAMPAIGNS_VL A
, OZF_ACT_BUDGETS ACT1
, AMS_CAMPAIGN_SCHEDULES_VL SCH
, OZF_FUNDS_ALL_B FUND
WHERE DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID)= SCH.SCHEDULE_ID
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE) ='CSCH'
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_TYPE
, ACT1.ARC_ACT_BUDGET_USED_BY) ='FUND'
AND ACT1.PARENT_ACT_BUDGET_ID IS NULL
AND SCH.CAMPAIGN_ID = A.CAMPAIGN_ID
AND ACT1.BUDGET_SOURCE_TYPE <> ACT1.ARC_ACT_BUDGET_USED_BY
AND ACT1.STATUS_CODE IN ('APPROVED'
, 'PENDING')
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID) = FUND.FUND_ID(+) UNION ALL SELECT DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID)
, QP.DESCRIPTION
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE)
, A.CUSTOM_SETUP_ID
, A.OFFER_CODE
, A.STATUS_CODE
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_AMOUNT
, 0-ACT1.APPROVED_ORIGINAL_AMOUNT))
, DECODE(ACT1.STATUS_CODE
, 'PENDING'
, ACT1.REQUEST_AMOUNT)
, A.OWNER_ID
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID)
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_TYPE
, ACT1.ARC_ACT_BUDGET_USED_BY)
, QP.START_DATE_ACTIVE
, QP.END_DATE_ACTIVE
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.REQUEST_CURRENCY
, ACT1.APPROVED_IN_CURRENCY)
, ACT1.ACTIVITY_BUDGET_ID
, TO_NUMBER(NULL)
, NULL COMPONENT_TYPE
, NULL COMPONENT_NUMBER
, NULL COMPONENT_TYPE_CODE
, NULL COMPONENT_NAME
, ACT1.PARENT_SOURCE_ID
, ACT1.PARENT_SRC_CURR
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.PARENT_SRC_APPRVD_AMT
, 0-ACT1.PARENT_SRC_APPRVD_AMT)
, ACT1.STATUS_CODE
, ACT1.TRANSFER_TYPE
, ACT1.RECAL_FLAG
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_ORIGINAL_AMOUNT
, 0-ACT1.APPROVED_AMOUNT))
, ACT1.SRC_CURR_REQUEST_AMT
, FUND.PARENT_FUND_ID
FROM OZF_OFFERS A
, OZF_ACT_BUDGETS ACT1
, QP_LIST_HEADERS_VL QP
, OZF_FUNDS_ALL_B FUND
WHERE DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID) = QP.LIST_HEADER_ID
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE) ='OFFR'
AND ACT1.PARENT_ACT_BUDGET_ID IS NULL
AND ACT1.ARC_ACT_BUDGET_USED_BY <> ACT1.BUDGET_SOURCE_TYPE
AND ACT1.STATUS_CODE IN ('APPROVED'
, 'PENDING'
, 'PENDING_VALIDATION')
AND A.REUSABLE = 'Y'
AND QP.LIST_HEADER_ID = A.QP_LIST_HEADER_ID
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID) = FUND.FUND_ID(+) UNION ALL SELECT DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID)
, QP.DESCRIPTION
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE)
, A.CUSTOM_SETUP_ID
, A.OFFER_CODE
, A.STATUS_CODE
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_AMOUNT
, 0-ACT1.APPROVED_ORIGINAL_AMOUNT))
, DECODE (ACT1.STATUS_CODE
, 'PENDING'
, ACT1.REQUEST_AMOUNT)
, A.OWNER_ID
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID)
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_TYPE
, ACT1.ARC_ACT_BUDGET_USED_BY)
, QP.START_DATE_ACTIVE
, QP.END_DATE_ACTIVE
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.REQUEST_CURRENCY
, ACT1.APPROVED_IN_CURRENCY)
, ACT1.ACTIVITY_BUDGET_ID
, TO_NUMBER(NULL)
, NULL COMPONENT_TYPE
, NULL COMPONENT_NUMBER
, NULL COMPONENT_TYPE_CODE
, NULL COMPONENT_NAME
, ACT1.PARENT_SOURCE_ID
, ACT1.PARENT_SRC_CURR
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.PARENT_SRC_APPRVD_AMT
, 0-ACT1.PARENT_SRC_APPRVD_AMT)
, ACT1.STATUS_CODE
, ACT1.TRANSFER_TYPE
, ACT1.RECAL_FLAG
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_ORIGINAL_AMOUNT
, 0-ACT1.APPROVED_AMOUNT))
, ACT1.SRC_CURR_REQUEST_AMT
, FUND.PARENT_FUND_ID
FROM OZF_OFFERS A
, OZF_ACT_BUDGETS ACT1
, QP_LIST_HEADERS_VL QP
, OZF_FUNDS_ALL_B FUND
WHERE DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID) = QP.LIST_HEADER_ID
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE) ='OFFR'
AND ACT1.PARENT_ACT_BUDGET_ID IS NULL
AND ACT1.ARC_ACT_BUDGET_USED_BY <> ACT1.BUDGET_SOURCE_TYPE
AND ACT1.STATUS_CODE IN ('APPROVED'
, 'PENDING'
, 'PENDING_VALIDATION')
AND A.REUSABLE = 'N'
AND QP.LIST_HEADER_ID = A.QP_LIST_HEADER_ID
AND DECODE(ACT1.TRANSFER_TYPE
, 'TRANSFER'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE) ='FUND'
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID) = FUND.FUND_ID(+)
AND NOT EXISTS (SELECT 'X'
FROM OZF_ACT_OFFERS
WHERE QP_LIST_HEADER_ID = A.QP_LIST_HEADER_ID ) UNION ALL SELECT DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID)
, A.EVENT_HEADER_NAME
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE)
, A.SETUP_TYPE_ID
, A.SOURCE_CODE
, A.SYSTEM_STATUS_CODE
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_AMOUNT
, 0-ACT1.APPROVED_ORIGINAL_AMOUNT))
, DECODE (ACT1.STATUS_CODE
, 'PENDING'
, ACT1.REQUEST_AMOUNT)
, A.OWNER_USER_ID
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID)
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_TYPE
, ACT1.ARC_ACT_BUDGET_USED_BY)
, A.ACTIVE_FROM_DATE
, A.ACTIVE_TO_DATE
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.REQUEST_CURRENCY
, ACT1.APPROVED_IN_CURRENCY)
, ACT1.ACTIVITY_BUDGET_ID
, TO_NUMBER(NULL)
, NULL COMPONENT_TYPE
, NULL COMPONENT_NUMBER
, NULL COMPONENT_TYPE_CODE
, NULL COMPONENT_NAME
, ACT1.PARENT_SOURCE_ID
, ACT1.PARENT_SRC_CURR
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.PARENT_SRC_APPRVD_AMT
, 0-ACT1.PARENT_SRC_APPRVD_AMT)
, ACT1.STATUS_CODE
, ACT1.TRANSFER_TYPE
, ACT1.RECAL_FLAG
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_ORIGINAL_AMOUNT
, 0-ACT1.APPROVED_AMOUNT))
, ACT1.SRC_CURR_REQUEST_AMT
, FUND.PARENT_FUND_ID
FROM AMS_EVENT_HEADERS_VL A
, OZF_ACT_BUDGETS ACT1
, OZF_FUNDS_ALL_B FUND
WHERE DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID) = A.EVENT_HEADER_ID
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE) ='EVEH'
AND ACT1.STATUS_CODE IN ('APPROVED'
, 'PENDING')
AND ACT1.ARC_ACT_BUDGET_USED_BY <> ACT1.BUDGET_SOURCE_TYPE
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID) = FUND.FUND_ID(+) UNION ALL SELECT DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID)
, A.EVENT_OFFER_NAME
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE)
, TO_NUMBER(NULL)
, A.SOURCE_CODE
, A.SYSTEM_STATUS_CODE
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_AMOUNT
, 0-ACT1.APPROVED_ORIGINAL_AMOUNT))
, DECODE (ACT1.STATUS_CODE
, 'PENDING'
, ACT1.REQUEST_AMOUNT)
, A.OWNER_USER_ID
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID)
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_TYPE
, ACT1.ARC_ACT_BUDGET_USED_BY)
, A.EVENT_START_DATE
, A.EVENT_END_DATE
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.REQUEST_CURRENCY
, ACT1.APPROVED_IN_CURRENCY)
, ACT1.ACTIVITY_BUDGET_ID
, TO_NUMBER(NULL)
, NULL COMPONENT_TYPE
, NULL COMPONENT_NUMBER
, NULL COMPONENT_TYPE_CODE
, NULL COMPONENT_NAME
, ACT1.PARENT_SOURCE_ID
, ACT1.PARENT_SRC_CURR
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.PARENT_SRC_APPRVD_AMT
, 0-ACT1.PARENT_SRC_APPRVD_AMT)
, ACT1.STATUS_CODE
, ACT1.TRANSFER_TYPE
, ACT1.RECAL_FLAG
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_ORIGINAL_AMOUNT
, 0-ACT1.APPROVED_AMOUNT))
, ACT1.SRC_CURR_REQUEST_AMT
, FUND.PARENT_FUND_ID
FROM AMS_EVENT_OFFERS_VL A
, OZF_ACT_BUDGETS ACT1
, OZF_FUNDS_ALL_B FUND
WHERE DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID) = A.EVENT_OFFER_ID
AND (DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE) ='EVEO' OR DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE) ='EONE')
AND ACT1.PARENT_ACT_BUDGET_ID IS NULL
AND ACT1.BUDGET_SOURCE_TYPE <> ACT1.ARC_ACT_BUDGET_USED_BY
AND ACT1.STATUS_CODE IN('APPROVED'
, 'PENDING')
AND DECODE(ACT1.TRANSFER_TYPE
, 'TRANSFER'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE) ='FUND'
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID) = FUND.FUND_ID(+) UNION ALL SELECT DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID)
, A.DELIVERABLE_NAME
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE)
, A.CUSTOM_SETUP_ID
, A.VERSION SOURCE_CODE
, A.STATUS_CODE
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_AMOUNT
, 0-ACT1.APPROVED_ORIGINAL_AMOUNT))
, DECODE (ACT1.STATUS_CODE
, 'PENDING'
, ACT1.REQUEST_AMOUNT)
, A.OWNER_USER_ID
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID)
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_TYPE
, ACT1.ARC_ACT_BUDGET_USED_BY)
, A.ACTUAL_AVAIL_FROM_DATE
, A.ACTUAL_AVAIL_TO_DATE
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.REQUEST_CURRENCY
, ACT1.APPROVED_IN_CURRENCY)
, ACT1.ACTIVITY_BUDGET_ID
, TO_NUMBER(NULL)
, NULL COMPONENT_TYPE
, NULL COMPONENT_NUMBER
, NULL COMPONENT_TYPE_CODE
, NULL COMPONENT_NAME
, ACT1.PARENT_SOURCE_ID
, ACT1.PARENT_SRC_CURR
, DECODE (ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.PARENT_SRC_APPRVD_AMT
, 0-ACT1.PARENT_SRC_APPRVD_AMT)
, ACT1.STATUS_CODE
, ACT1.TRANSFER_TYPE
, ACT1.RECAL_FLAG
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_ORIGINAL_AMOUNT
, 0-ACT1.APPROVED_AMOUNT))
, ACT1.SRC_CURR_REQUEST_AMT
, FUND.PARENT_FUND_ID
FROM AMS_DELIVERABLES_VL A
, OZF_ACT_BUDGETS ACT1
, OZF_FUNDS_ALL_B FUND
WHERE DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID) = A.DELIVERABLE_ID
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE) = 'DELV'
AND ACT1.BUDGET_SOURCE_TYPE <> ACT1.ARC_ACT_BUDGET_USED_BY
AND ACT1.STATUS_CODE IN ('APPROVED'
, 'PENDING')
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID) = FUND.FUND_ID(+) UNION ALL SELECT ACTOF.ACT_OFFER_USED_BY_ID
, A.CAMPAIGN_NAME
, 'CAMP'
, A.CUSTOM_SETUP_ID
, A.SOURCE_CODE
, A.STATUS_CODE
, OZF_FUND_ADJUSTMENT_PVT.CONVERT_CURRENCY(DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.REQUEST_CURRENCY
, ACT1.APPROVED_IN_CURRENCY)
, A.TRANSACTION_CURRENCY_CODE
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_AMOUNT
, 0-ACT1.APPROVED_ORIGINAL_AMOUNT)))
, OZF_FUND_ADJUSTMENT_PVT.CONVERT_CURRENCY(ACT1.REQUEST_CURRENCY
, A.TRANSACTION_CURRENCY_CODE
, DECODE (ACT1.STATUS_CODE
, 'PENDING'
, ACT1.REQUEST_AMOUNT))
, A.OWNER_USER_ID
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID)
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_TYPE
, ACT1.ARC_ACT_BUDGET_USED_BY)
, A.ACTUAL_EXEC_START_DATE
, A.ACTUAL_EXEC_END_DATE
, A.TRANSACTION_CURRENCY_CODE
, ACT1.ACTIVITY_BUDGET_ID
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID)
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE)
, OFFS.OFFER_CODE
, OFFS.OFFER_TYPE
, QP.DESCRIPTION
, ACT1.PARENT_SOURCE_ID
, ACT1.PARENT_SRC_CURR
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.PARENT_SRC_APPRVD_AMT
, 0-ACT1.PARENT_SRC_APPRVD_AMT)
, ACT1.STATUS_CODE
, ACT1.TRANSFER_TYPE
, ACT1.RECAL_FLAG
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_ORIGINAL_AMOUNT
, 0-ACT1.APPROVED_AMOUNT))
, ACT1.SRC_CURR_REQUEST_AMT
, FUND.PARENT_FUND_ID
FROM AMS_CAMPAIGNS_VL A
, OZF_ACT_BUDGETS ACT1
, OZF_ACT_OFFERS ACTOF
, OZF_OFFERS OFFS
, QP_LIST_HEADERS_VL QP
, OZF_FUNDS_ALL_B FUND
WHERE DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID) = ACTOF.QP_LIST_HEADER_ID
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE) ='OFFR'
AND OFFS.REUSABLE = 'N'
AND ACTOF.ACT_OFFER_USED_BY_ID = A.CAMPAIGN_ID
AND ACTOF.ARC_ACT_OFFER_USED_BY = 'CAMP'
AND ACT1.PARENT_ACT_BUDGET_ID IS NULL
AND ACT1.BUDGET_SOURCE_TYPE <> ACT1.ARC_ACT_BUDGET_USED_BY
AND ACT1.STATUS_CODE IN ('APPROVED'
, 'PENDING'
, 'PENDING_VALIDATION')
AND ACTOF.QP_LIST_HEADER_ID = OFFS.QP_LIST_HEADER_ID(+)
AND ACTOF.QP_LIST_HEADER_ID = QP.LIST_HEADER_ID(+)
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID) = FUND.FUND_ID(+) UNION ALL SELECT DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID)
, QP.DESCRIPTION
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE)
, TO_NUMBER(NULL) CUSTOM_SETUP_ID
, QP.NAME
, NULL STATUS_CODE
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_AMOUNT
, 0-ACT1.APPROVED_ORIGINAL_AMOUNT))
, DECODE(ACT1.STATUS_CODE
, 'PENDING'
, ACT1.REQUEST_AMOUNT)
, TO_NUMBER(NULL) OWNER_ID
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID)
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_TYPE
, ACT1.ARC_ACT_BUDGET_USED_BY)
, QP.START_DATE_ACTIVE
, QP.END_DATE_ACTIVE
, DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.REQUEST_CURRENCY
, ACT1.APPROVED_IN_CURRENCY)
, ACT1.ACTIVITY_BUDGET_ID
, TO_NUMBER(NULL)
, NULL COMPONENT_TYPE
, NULL COMPONENT_NUMBER
, NULL COMPONENT_TYPE_CODE
, NULL COMPONENT_NAME
, ACT1.PARENT_SOURCE_ID
, ACT1.PARENT_SRC_CURR
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.PARENT_SRC_APPRVD_AMT
, 0-ACT1.PARENT_SRC_APPRVD_AMT)
, ACT1.STATUS_CODE
, ACT1.TRANSFER_TYPE
, ACT1.RECAL_FLAG
, DECODE(ACT1.STATUS_CODE
, 'APPROVED'
, DECODE ( ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.APPROVED_ORIGINAL_AMOUNT
, 0-ACT1.APPROVED_AMOUNT))
, ACT1.SRC_CURR_REQUEST_AMT
, FUND.PARENT_FUND_ID
FROM OZF_ACT_BUDGETS ACT1
, QP_LIST_HEADERS_VL QP
, OZF_FUNDS_ALL_B FUND
WHERE DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ACT_BUDGET_USED_BY_ID
, ACT1.BUDGET_SOURCE_ID) = QP.LIST_HEADER_ID
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.ARC_ACT_BUDGET_USED_BY
, ACT1.BUDGET_SOURCE_TYPE) ='PRIC'
AND ACT1.ARC_ACT_BUDGET_USED_BY <> ACT1.BUDGET_SOURCE_TYPE
AND ACT1.STATUS_CODE IN ('APPROVED'
, 'PENDING')
AND DECODE(ACT1.TRANSFER_TYPE
, 'REQUEST'
, ACT1.BUDGET_SOURCE_ID
, ACT1.ACT_BUDGET_USED_BY_ID) = FUND.FUND_ID(+)