APPS.BIM_LEAD_IMPORT_FACTS_PKG SQL Statements

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

Line: 44

    SELECT  MAX(end_date)
    FROM    bim_rep_history
    WHERE   object = 'LEAD_IMPORT';

Line: 211

    INSERT INTO
    bim_rep_history
       (creation_date,
	last_update_date,
	created_by,
	last_updated_by,
	object,
	object_last_updated_date,
	start_date,
	end_date)
    VALUES
       (sysdate,
	sysdate,
	l_user_id,
	l_user_id,
	p_object,
	sysdate,
	p_start_date,
	p_end_date);

Line: 334

   INSERT INTO /*+ append parallel(EDF,1) */
          BIM_R_LIMP_DAILY_FACTS EDF(
	      Daily_transaction_id,
          Creation_date,
          Last_update_date,
          Created_by,
          Last_updated_by,
          Last_update_login,
          parent_object_id,
          Object_id,
          parent_object_type,
          object_type,
          lead_region,
          lead_country,
          object_business_unit_id,
          lead_import_status,
          Failure_reason,
          month,
          qtr,
          year,
          leads_valid,
          leads_invalid,
          leads_new,
          Transaction_create_date,
          weekend_date)
   select /*+ parallel(OUTER,1) */
        BIM_R_LIMP_DAILY_FACTS_S.nextval,
        sysdate,
        sysdate,
        l_user_id,
        l_user_id,
        l_user_id,
        parent_object_id,
        Object_id,
        parent_object_type,
        object_type,
        lead_region,
        lead_country,
        object_business_unit_id,
        lead_import_status,
        Failure_reason,
        month,
        qtr,
        year,
        leads_valid,
        leads_invalid,
        leads_new,
        creation_date,
        trunc((decode(decode( to_char(creation_date,'MM') , to_char(next_day(creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
      	        ,'TRUE'
      	        ,decode(decode(creation_date , (next_day(creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
       	        ,'TRUE'
      	        ,creation_date
      	        ,'FALSE'
      	        ,next_day(creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
      	        ,'FALSE'
      	        ,decode(decode(to_char(creation_date,'MM'),to_char(next_day(creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
      	        ,'FALSE'
      	        ,last_day(creation_date)))))    weekend_date
from(
SELECT  b.parent_object_type parent_object_type,
        b.object_type object_type,
        b.parent_object_id parent_object_id,
        b.object_id object_id,
        b.business_unit_id object_business_unit_id,
        a.last_update_date creation_date,
        c.country lead_country,
        c.region lead_region,
        a.load_status lead_import_status,
        d.fiscal_month month,
        d.fiscal_qtr qtr,
        d.fiscal_year year,
        a.Failure_reason Failure_reason,
        sum(a.leads_valid) leads_valid,
        SUM(a.leads_new) leads_new,
        SUM(a.leads_Invalid) leads_Invalid
FROM(
SELECT  a.promotion_code,
        a.country,
        TRUNC(a.last_update_date) last_update_date,
        a.Import_interface_id,
        a.load_status,
        NULL failure_reason,
        decode(A.Load_Status, 'SUCCESS', 1, 0) leads_valid,
        decode(A.Load_Status, 'NEW', 1, 0) leads_new,
        0 leads_invalid
from    AS_IMPORT_INTERFACE A
where   a.last_update_date between p_start_datel and p_end_datel+0.9999
AND     a.load_status IN ('SUCCESS', 'NEW')
group by a.promotion_code,
        a.country,
        TRUNC(a.last_update_date),
        a.Import_interface_id,
        a.load_status
UNION ALL
SELECT  a.promotion_code,
        a.country,
        MAX(TRUNC(B.last_update_date)) last_update_date,
        a.Import_interface_id,
        a.load_status,
        max(b.error_text) FAILURE_REASON,
        0 leads_valid,
        0 leads_new,
        decode(A.Load_Status, 'ERROR', 1,'UNEXP_ERROR', 1, 0) leads_Invalid
from    AS_IMPORT_INTERFACE a,
        AS_LEAD_IMPORT_ERRORS B
where   a.last_update_date between p_start_datel and p_end_datel +0.9999
and     a.Import_interface_id = B.import_interface_id(+)
AND     a.load_status IN ('ERROR', 'UNEXP_ERROR')
group by a.promotion_code,
        a.country,
        TRUNC(a.last_update_date),
        a.Import_interface_id,
        a.load_status) A,
      bim_r_source_codes b,
      bim_r_locations c,
      bim_intl_dates d
where   a.promotion_code = b.source_code(+)
and     a.country = c.country
and     a.last_update_date = d.trdate
group by b.parent_object_type,
        b.object_type,
        b.parent_object_id,
        b.object_id,
        b.business_unit_id,
        a.last_update_date,
        c.country,
        c.region,
        a.load_status,
        b.business_unit_id,
        d.fiscal_month,
        d.fiscal_qtr,
        d.fiscal_year,
        a.failure_reason) OUTER;

Line: 488

		  FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');

Line: 491

   ams_utility_pvt.write_conc_log('LEAD IMPORT: EXCEPTION FOR FIRST INSERT. '||sqlerrm(sqlcode));

Line: 509

   INSERT INTO
          BIM_R_LIMP_WEEKLY_FACTS EDF(
	      weekly_transaction_id,
          Creation_date,
          Last_update_date,
          Created_by,
          Last_updated_by,
          Last_update_login,
          Lead_Region,
          Lead_Country,
          Object_Business_unit_id,
          Lead_Import_Status,
          Object_id,
          Object_type,
          Parent_object_id,
          Parent_object_type,
          FAILURE_REASON,
          Month,
          Qtr,
          Year,
          leads_valid,
          leads_invalid,
          leads_new,
          Weekend_date
	     )
     SELECT
          BIM_R_LIMP_WEEKLY_FACTS_S.nextval,
          sysdate,
          sysdate,
          l_user_id,
          l_user_id,
          l_user_id,
          Lead_Region,
          Lead_Country,
          Object_Business_unit_id,
          Lead_Import_Status,
          Object_id,
          Object_type,
          Parent_object_id,
          Parent_object_type,
          FAILURE_REASON,
          Month,
          Qtr,
          Year,
          leads_valid,
          leads_invalid,
          leads_new,
          Weekend_date
  from( select Lead_Region,
          Lead_Country,
          Object_Business_unit_id,
          Lead_Import_Status,
          Object_id,
          Object_type,
          Parent_object_id,
          Parent_object_type,
          FAILURE_REASON,
          Month,
          Qtr,
          Year,
          sum(Leads_valid) leads_valid,
          sum(Leads_invalid) leads_invalid,
          sum(Leads_new) leads_new,
          Weekend_date
     FROM BIM_R_LIMP_DAILY_FACTS
     group by
          Lead_Region,
          Lead_Country,
          Object_Business_unit_id,
          Lead_Import_Status,
          Object_id,
          Object_type,
          Parent_object_id,
          Parent_object_type,
          FAILURE_REASON,
          Month,
          Qtr,
          Year,
          Weekend_date);