DBA Data[Home] [Help]

APPS.AR_BFB_UTILS_PVT SQL Statements

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

Line: 20

 select to_char(the_date, 'MM' ) - 3 into m from dual;
Line: 21

 select to_char(the_date, 'YYYY' ) into y from dual;
Line: 22

 select to_char(the_date, 'DD' ) into d from dual;
Line: 47

  select min(trunc(billable_date))
   into l_billing_date
  from ar_cons_bill_cycles_b cy,
       ar_cons_bill_cycle_dates cyd
  where cy.billing_cycle_id = p_billing_cycle_id
    and cy.billing_cycle_id = cyd.billing_cycle_id
    and cyd.billable_date
         between trunc(greatest(nvl(p_billing_date,sysdate), nvl(cy.START_DATE, sysdate)))
           and (trunc(greatest(nvl(p_billing_date,sysdate), nvl(cy.START_DATE, sysdate)))  + decode(cy.cycle_frequency,
                                       'DAILY',   1*nvl(cy.REPEAT_DAILY,0),
                                       'WEEKLY',  7*nvl(cy.REPEAT_WEEKLY,0),
                                       'MONTHLY', 31*nvl(cy.REPEAT_MONTHLY,0),
                                       0));
Line: 81

   select max(billable_date)
   into   l_billing_date
   from   ar_cons_bill_cycle_dates
   where  billing_cycle_id = p_billing_cycle_id
   and    billable_date between  trunc(p_last_bill_date) and trunc(p_billing_date);
Line: 87

   select max(billable_date)
   into   l_billing_date
   from   ar_cons_bill_cycle_dates
   where  billing_cycle_id = p_billing_cycle_id
   and    billable_date between trunc(p_billing_date) and trunc(p_last_bill_date);
Line: 117

  select due_days,
         due_day_of_month,
         due_months_forward
    into dued,
         duedom,
         duemf
    from ra_terms_lines
   where term_id = p_payment_term_id;
Line: 149

   select billing_cycle_id
     into bill_cycle_id
     from ra_terms
    where term_id = p_payment_term_id;
Line: 172

   select billing_cycle_id
     into bill_cycle_id
     from ra_terms
    where name = p_payment_term_name;
Line: 195

   select decode(cp.cons_bill_level,'ACCOUNT','A','SITE','S','N')
     into bfb_level
     from hz_customer_profiles cp
    where cp.cust_account_id = p_cust_account_id
      and cp.site_use_id IS NULL;
Line: 213

   select billing_cycle_id
     into bill_cycle_id
     from ra_terms
    where term_id = p_payment_term_id;
Line: 230

   select bill_cycle_type
     into cycle_type
     from ar_cons_bill_cycles_b
    where billing_cycle_id = p_bill_cycle_id;
Line: 245

   select nvl(accounting_affect_flag,'Y')
     into open_rec
     from ra_cust_trx_types
    where cust_trx_type_id = p_cust_trx_type_id;
Line: 266

   select nvl(su.payment_term_id,
               decode(spt.billing_cycle_id,
                      -- if cycle is NULL
                      NULL, nvl(sp.standard_terms,
                                decode(apt.billing_cycle_id,
                                       -- if cycle is NULL
                                       NULL, nvl(nvl(ap.standard_terms, tt.default_term) , -94) ,
                                       -- if cycle is NOT NULL
                                       -92)),
                      -- if cycle is NOT NULL
                      decode(ap.cons_bill_level,
                             -- if bill level = Account
                             'ACCOUNT', nvl(ap.standard_terms, -93),
                             -- if bill level = Site
                             'SITE', nvl(nvl(sp.standard_terms, ap.standard_terms), -95)
                             -- if bill level is not set
                             , -91)))
   into   l_default_term
   from   ra_cust_trx_types     tt,
          hz_customer_profiles  ap,
          hz_customer_profiles  sp,
          hz_cust_site_uses     su,
          ra_terms_b            apt,
          ra_terms_b            spt
   where  p_trx_type_id = tt.cust_trx_type_id
   and    p_org_id = tt.org_id
   and    p_bill_to_site = su.site_use_id
   and    p_bill_to_customer = ap.cust_account_id
   and    ap.site_use_id is null
   and    p_bill_to_customer = sp.cust_account_id (+)
   and    su.site_use_id = sp.site_use_id (+)
   and    ap.standard_terms = apt.term_id (+)
   and    sysdate between nvl(apt.start_date_active, sysdate) and
          nvl(apt.end_date_active, sysdate)
   and    sp.standard_terms = spt.term_id (+)
   and    sysdate between nvl(spt.start_date_active, sysdate) and
          nvl(spt.end_date_active, sysdate);
Line: 308

/* Procedure that bulk updates the term_ids on imported transactions
   using the predefined algorithm for BFB/ECBI.  Takes a request
   ID in and processes all invoices in that request batch.  Also
   inserts errors into ra_interface_errors for those situations
   where the term is in conflict with the setups */

PROCEDURE validate_and_default_term( p_request_id     IN NUMBER,
                                     p_error_count  IN OUT NOCOPY NUMBER)
IS

CURSOR c_terms(p_request_id NUMBER) IS
select
    decode(invt.billing_cycle_id,
           NULL, decode(decode(ap.cons_bill_level,'SITE',sp.override_terms,ap.override_terms),
                        'Y',  trx.term_id,
                        nvl(su.payment_term_id,
                            decode(spt.billing_cycle_id,
                                   NULL, nvl(sp.standard_terms,
                                             decode(apt.billing_cycle_id,
                                               NULL, nvl(nvl(ap.standard_terms, tt.default_term) , -94),
                                               -92)),
                                   decode(ap.cons_bill_level,
                                          'ACCOUNT', nvl(ap.standard_terms, -93),
                                          'SITE', nvl(nvl(sp.standard_terms, ap.standard_terms), -95),
                                          -91)))),
           nvl(su.payment_term_id,
               decode(spt.billing_cycle_id,
                      NULL, nvl(sp.standard_terms,
                                decode(apt.billing_cycle_id,
                                       NULL, nvl(nvl(ap.standard_terms, tt.default_term) , -94) ,
                                       -92)),
                      decode(ap.cons_bill_level,
                             'ACCOUNT', nvl(ap.standard_terms, -93),
                             'SITE', nvl(nvl(sp.standard_terms, ap.standard_terms), -95)
                             , -91))))  new_term_id, trx.customer_trx_id
from   ra_customer_trx       trx,
       ra_cust_trx_types     tt,
       hz_customer_profiles  ap,
       hz_customer_profiles  sp,
       hz_cust_site_uses     su,
       ra_terms_b            sut,
       ra_terms_b            apt,
       ra_terms_b            spt,
       ra_terms_b            ttt,
       ra_terms_b            invt
where  trx.request_id = p_request_id
and    trx.previous_customer_trx_id IS NULL -- invoices only
and    trx.term_id = invt.term_id
and    trx.cust_trx_type_id = tt.cust_trx_type_id
and    trx.org_id = tt.org_id
and    tt.default_term = ttt.term_id (+)
and    trx.trx_date between nvl(ttt.start_date_active, trx.trx_date) and
                            nvl(ttt.end_date_active,   trx.trx_date)
and    trx.bill_to_site_use_id = su.site_use_id
and    su.payment_term_id = sut.term_id (+)
and    trx.trx_date between nvl(sut.start_date_active, trx.trx_date) and
                            nvl(sut.end_date_active,   trx.trx_date)
and    trx.bill_to_customer_id = ap.cust_account_id
and    ap.site_use_id is null
and    NVL(ap.cons_inv_flag, 'N') = 'Y' -- 7575555
and    trx.bill_to_customer_id = sp.cust_account_id (+)
and    trx.bill_to_site_use_id = sp.site_use_id (+)
and    ap.standard_terms = apt.term_id (+)
and    trx.trx_date between nvl(apt.start_date_active, trx.trx_date) and
                            nvl(apt.end_date_active,   trx.trx_date)
and    sp.standard_terms = spt.term_id (+)
and    trx.trx_date between nvl(spt.start_date_active, trx.trx_date) and
                            nvl(spt.end_date_active,   trx.trx_date);
Line: 380

  l_rows_selected   number;
Line: 383

  l_rows_updated    number;
Line: 404

     l_rows_selected := c_terms%ROWCOUNT;
Line: 408

  IF l_rows_selected > 0
  THEN
     /* Process what we've got */
     IF PG_DEBUG in ('Y', 'C') THEN
        arp_standard.debug('  rows selected = ' || l_rows_selected);
Line: 415

     /* Bulk update of transaction terms
        NOTE:  This excludes those in error or null */
     FORALL i IN t_trx_id.FIRST..t_trx_id.LAST
        UPDATE ra_customer_trx trx
           SET term_id             = t_term_id(i)
         WHERE trx.customer_trx_id = t_trx_id(i)
           AND NVL(t_term_id(i), -99) > 0;
Line: 426

        were in error, we need to insert the correct error
        message into RA_INTERFACE_ERRORS */
     IF NVL(l_rows_processed,0) < l_rows_selected
     THEN
        /* Get error messages for bulk insert */
        fnd_message.set_name('AR', 'AR_BFB_TERM_BILL_LEVEL_NULL');
Line: 444

           INSERT into RA_INTERFACE_ERRORS
             (interface_line_id,
              message_text,
              org_id)
           SELECT line.customer_trx_line_id,
                  DECODE(t_term_id(err),
                      -91,l_msg_91,
                      -92,l_msg_92,
                      -93,l_msg_93,
                      -94,l_msg_94,
                      -95,l_msg_95),
                  line.org_id
           FROM  RA_CUSTOMER_TRX_LINES line
           WHERE line.customer_trx_id = t_trx_id(err)
           AND   t_term_id(err) < 0;
Line: 472

     arp_standard.debug('  rows processed = ' || l_rows_updated);
Line: 507

 l_last_day_month_insert_flag varchar2(1); -- Added for Bug 7476810
Line: 519

  select cycle_frequency ,
         decode(cycle_frequency, 'MONTHLY', repeat_monthly ,
                                 'WEEKLY', repeat_weekly,
                                 'DAILY', repeat_daily ) repeat_frequency,
         skip_weekends,
         day_type,
         trunc(start_date)
  into l_cycle_frequency ,
       l_repeat_frequency , l_skip_weekends, l_day_type,l_start_date
  from ar_cons_bill_cycles_b
  where billing_cycle_id = p_billing_cycle_id;
Line: 566

      INSERT INTO AR_CONS_BILL_CYCLE_DATES (
                  BILLING_CYCLE_ID ,
                  BILLABLE_DATE    ,
                  CREATED_BY       ,
                  CREATION_DATE    ,
                  LAST_UPDATE_LOGIN,
                  LAST_UPDATE_DATE ,
                  LAST_UPDATED_BY  )
             values
                ( p_billing_cycle_id,
                  l_next_billing_date,
                  fnd_global.user_id,
                  sysdate,
                  fnd_global.user_id,
                  sysdate,
                  fnd_global.user_id);
Line: 597

  select day_monday, day_tuesday, day_wednesday, day_thursday, day_friday, day_saturday, day_sunday
  into week_tab(2), week_tab(3), week_tab(4), week_tab(5), week_tab(6),week_tab(7),week_tab(1)
  from AR_CONS_BILL_CYCLES_B
  where billing_cycle_id = p_billing_cycle_id;
Line: 621

    INSERT INTO AR_CONS_BILL_CYCLE_DATES (
                  BILLING_CYCLE_ID ,
                  BILLABLE_DATE    ,
                  CREATED_BY       ,
                  CREATION_DATE    ,
                  LAST_UPDATE_LOGIN,
                  LAST_UPDATE_DATE ,
                  LAST_UPDATED_BY  )
     values
                ( p_billing_cycle_id,
                  l_next_billing_date,
                  fnd_global.user_id,
                  sysdate,
                  fnd_global.user_id,
                  sysdate,
                  fnd_global.user_id);
Line: 685

    /* Bug 7476810. Initializing the variable l_last_day_month_insert_flag each
       time to 'N' at the start of the new month */
    l_last_day_month_insert_flag := 'N';
Line: 699

   select day_1, day_2, day_3, day_4, day_5,
          day_6, day_7, day_8, day_9, day_10,
          day_11, day_12, day_13, day_14, day_15,
          day_16, day_17, day_18, day_19, day_20,
          day_21, day_22, day_23, day_24, day_25,
          day_26, day_27, day_28, day_29, day_30, day_31, last_day
   into daytab(1), daytab(2), daytab(3), daytab(4), daytab(5),
        daytab(6), daytab(7), daytab(8), daytab(9), daytab(10),
        daytab(11), daytab(12), daytab(13), daytab(14), daytab(15),
        daytab(16), daytab(17), daytab(18), daytab(19), daytab(20),
        daytab(21), daytab(22), daytab(23), daytab(24), daytab(25),
        daytab(26), daytab(27), daytab(28), daytab(29), daytab(30), daytab(31),
        l_last_day
   from ar_cons_bill_cycles_b
  where billing_cycle_id = p_billing_cycle_id;
Line: 720

     /* Bug 7476810. If the last day of the month is inserted in the table as a
        billling date, then dont insert the last date again. */
     if daytab(i) = 'Y' and  l_last_day_month_insert_flag = 'N'  then

        if i >=  l_month_days then
         l_last_day_month_insert_flag := 'Y';
Line: 737

      would not be inserted in the table AR_CONS_BILL_CYCLE_DATES */
        if l_day_type = 'W' and ar_day_of_week(l_next_billing_date) in (6,7) then
         if ar_day_of_week(l_next_billing_date) = 6 then
            -- If the billable day is 'Saturday', make the coming monday as billable day and skip Saturday.
            daytab(i+2) := 'Y';
Line: 750

               INSERT INTO AR_CONS_BILL_CYCLE_DATES (
                          BILLING_CYCLE_ID ,
                          BILLABLE_DATE    ,
                          CREATED_BY       ,
                          CREATION_DATE    ,
                          LAST_UPDATE_LOGIN,
                          LAST_UPDATE_DATE ,
                          LAST_UPDATED_BY  )
                     values
                        ( p_billing_cycle_id,
                          l_next_billing_date,
                          fnd_global.user_id,
                          sysdate,
                          fnd_global.user_id,
                          sysdate,
                          fnd_global.user_id);