APPS.BIM_POPDATES_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 9

SELECT TRUNC(min(start_date))
FROM gl_periods
WHERE  period_set_name = fnd_profile.value('AMS_CAMPAIGN_DEFAULT_CALENDER');

Line: 14

SELECT TRUNC(max(end_date))
FROM   gl_periods
WHERE  period_set_name = fnd_profile.value('AMS_CAMPAIGN_DEFAULT_CALENDER');

Line: 19

select trunc(min(de)) from (
select min(bu1.approval_date) de
FROM      ozf_funds_all_b o,
          ozf_act_budgets BU1
WHERE  o.start_date_active > p_date
and o.status_code in ('ACTIVE','CANCELLED', 'CLOSED')
AND    bu1.transfer_type in ('TRANSFER', 'REQUEST')
AND    bu1.approval_date <=trunc(o.start_date_active)
AND    bu1.status_code = 'APPROVED'
AND    bu1.arc_act_budget_used_by = 'FUND'
AND    bu1.act_budget_used_by_id = o.fund_id
AND    bu1.budget_source_type ='FUND'
union all
select min(bu2.approval_date) de
FROM      ozf_funds_all_b o,
          ozf_act_budgets BU2
WHERE  o.start_date_active > p_date --between p_start_datel and p_end_datel
AND    o.status_code in ('ACTIVE','CANCEL', 'CLOSED')
AND    bu2.approval_date <=trunc(o.start_date_active)
AND    bu2.status_code= 'APPROVED'
AND    bu2.arc_act_budget_used_by = 'FUND'
AND    bu2.budget_source_type ='FUND'
AND    bu2.budget_source_id = o.fund_id
union all
SELECT min(a.approval_date) de
FROM ozf_act_budgets a,
     ams_campaigns_all_b b
WHERE a.budget_source_type ='FUND'
AND   a.ARC_ACT_BUDGET_USED_BY = 'CAMP'
AND   a.status_code ='APPROVED'
and  a.act_budget_used_by_id=b.campaign_id
and  b.actual_exec_start_date>p_date
and  b.status_code in ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
union all
SELECT min(a.approval_date)
FROM ozf_act_budgets a,
     ams_event_headers_all_b b
WHERE a.budget_source_type ='FUND'
AND   a.ARC_ACT_BUDGET_USED_BY = 'EVEH'
AND   a.status_code ='APPROVED'
and  a.act_budget_used_by_id=b.event_header_id
and  b.active_from_date>p_date
and  b.system_status_code in ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
union all
SELECT   min(a.last_reg_status_date) de
FROM     ams_event_registrations A,
         ams_event_headers_all_b b,
		 ams_event_offers_all_b c
where 	 b.active_from_date>p_date
and      b.system_status_code in ('ACTIVE', 'CANCELLED', 'COMPLETED')
and	     a.event_offer_id = c.event_offer_id
and      c.event_header_id = b.event_header_id );

Line: 106

     INSERT
     INTO BIM_INTL_DATES fdf (
     TRDATE
    ,FISCAL_MONTH
    ,MONTH_FLAG
    ,FISCAL_QTR
    ,QTR_FLAG
    ,FISCAL_YEAR
    ,FISCAL_MONTH_START
    ,FISCAL_MONTH_END
    ,MONTH_NUM
    ,FISCAL_QTR_START
    ,FISCAL_QTR_END
    ,QTR_NUM
    ,FISCAL_YEAR_START
    ,FISCAL_YEAR_END
    ,FISCAL_ROLL_YEAR_START
    ,PRE_FISCAL_MONTH_START
    ,PRE_FISCAL_MONTH_END
    ,PRE_FISCAL_QTR_START
    ,PRE_FISCAL_QTR_END
    ,PRE_FISCAL_YEAR_START
    ,PRE_FISCAL_YEAR_END
    ,PRE_FISCAL_ROLL_YEAR_START
    ,PRE_FISCAL_ROLL_YEAR_END
    ,YEAR_FLAG
    )
    SELECT
     l_min_date
    ,BIM_SET_OF_BOOKS.GET_FISCAL_MONTH(l_min_date,204)
    ,'N'
    ,BIM_SET_OF_BOOKS.GET_FISCAL_QTR(l_min_date,204)
     ,'N'
    ,BIM_SET_OF_BOOKS.GET_FISCAL_YEAR(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_START(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_END(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_NUM(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_FISCAL_QTR_START(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_FISCAL_QTR_END(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_FISCAL_QTR_NUM(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_FISCAL_YEAR_START(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_FISCAL_YEAR_END(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_FISCAL_ROLL_YEAR_START(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_MONTH_START(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_MONTH_END(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_QTR_START(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_QTR_END(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_YEAR_START(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_YEAR_END(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_ROLL_YEAR_START(l_min_date,204)
    ,BIM_SET_OF_BOOKS.GET_PRE_FISCAL_ROLL_YEAR_END(l_min_date,204)
    ,'N'
     FROM DUAL;

Line: 162

     ams_utility_pvt.write_conc_log('Error inserting bim_intl_dates ' || sqlerrm(sqlcode));

Line: 169

 update bim_intl_dates p set month_flag = 'Y'
 where p.trdate in (SELECT min(trdate)
 FROM bim_intl_dates
 GROUP BY fiscal_month);

Line: 181

update bim_intl_dates p set qtr_flag = 'Y'
where p.trdate in (SELECT min(trdate)
FROM bim_intl_dates
GROUP BY fiscal_qtr);

Line: 192

update bim_intl_dates p set year_flag = 'Y'
where p.trdate in (SELECT min(trdate)
FROM bim_intl_dates
GROUP BY fiscal_year);

Line: 202

DELETE FROM bim_rep_history
WHERE object='DATES';

Line: 204

INSERT INTO
    bim_rep_history
       (creation_date,
        last_update_date,
        created_by,
        last_updated_by,
        object,
        object_last_updated_date)
VALUES
       (sysdate,
        sysdate,
        -1,
        -1,
        'DATES',
        sysdate);