APPS.AP_CARD_INVOICE_PKG SQL Statements

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

Line: 49

  select sum(nvl(efd.amount,0)),
         efd.dist_code_combination_id,
         efd.tax_code,
         efd.amount_includes_tax_flag,
         decode(P_ROLLUP_FLAG,
                  'N',efd.feed_distribution_id),
         decode(P_ROLLUP_FLAG,
                  'N',efl.transaction_date,
                      nvl(P_START_DATE,nvl(P_END_DATE,sysdate))),
         decode(P_ROLLUP_FLAG,
                  'N',efd.description),
	 efd.org_id -- Bug 5592139
  from   ap_expense_feed_lines efl,
         ap_expense_feed_dists efd,
         ap_card_programs cp
  where  efl.feed_line_id = efd.feed_line_id
  and    efl.card_program_id = P_CARD_PROGRAM_ID
  and    efl.card_program_id = cp.card_program_id
  and    nvl(efd.invoiced_flag,'N') = 'N'
  and    ((nvl(efd.status_lookup_code,'VALIDATED') = 'APPROVED')
          OR
          (nvl(cp.exclude_unverified_flag,'N') = 'N' AND
           nvl(efd.status_lookup_code,'VALIDATED') = 'VALIDATED')
          OR
          (nvl(cp.exclude_rejected_flag,'N') = 'N' AND
           nvl(efd.status_lookup_code,'VALIDATED') in ('REJECTED','VERIFIED'))
          OR
          (nvl(cp.exclude_personal_flag,'N') = 'N' AND
           nvl(efd.status_lookup_code,'VALIDATED') = 'PERSONAL')
          OR
          (nvl(cp.exclude_disputed_flag,'N') = 'N' AND
           nvl(efd.status_lookup_code,'VALIDATED') = 'DISPUTED')
          OR
          (nvl(cp.exclude_held_flag,'N') = 'N' AND
           nvl(efd.status_lookup_code,'VALIDATED') = 'HOLD'))
  and    (efl.posted_date is null OR
          (efl.posted_date between
                  nvl(P_START_DATE,efl.posted_date-1) and
                  nvl(P_END_DATE,efl.posted_date+1)))
  group by efd.dist_code_combination_id,
         efd.tax_code,
         efd.amount_includes_tax_flag,
         decode(P_ROLLUP_FLAG,
                  'N',efd.feed_distribution_id),
         decode(P_ROLLUP_FLAG,
                  'N',efl.transaction_date,
                      nvl(P_START_DATE,nvl(P_END_DATE,sysdate))),
         decode(P_ROLLUP_FLAG,
                  'N',efd.description),
	 efd.org_id;  -- Bug 5620010

Line: 153

      select ap_invoices_interface_s.nextval
      into   l_invoice_id
      from   dual;

Line: 167

    select ap_invoice_lines_interface_s.nextval
    into   l_invoice_line_id
    from   dual;

Line: 172

    l_debug_info := 'inserting into ap_invoice_lines_interface';

Line: 174

    insert into ap_invoice_lines_interface
      (INVOICE_ID,
       INVOICE_LINE_ID,
       LINE_NUMBER,
       LINE_TYPE_LOOKUP_CODE,
       AMOUNT,
       ACCOUNTING_DATE,
       DESCRIPTION,
       TAX_CODE,
       AMOUNT_INCLUDES_TAX_FLAG,
       DIST_CODE_COMBINATION_ID,
       ORG_ID) VALUES -- Bug 5592139
      (l_invoice_id,
       l_invoice_line_id,
       l_count,
       'ITEM',
       l_amount,
       l_transaction_date,
       l_description,
       l_tax_code,
       l_amount_includes_tax_flag,
       l_ccid,
       l_org_id);  -- Bug 5592139

Line: 209

      update AP_EXPENSE_FEED_DISTS
      set    invoiced_flag = 'Y',
             INVOICE_ID = l_invoice_id,
             INVOICE_LINE_ID = l_invoice_line_id
      where  feed_distribution_id = l_feed_distribution_id;

Line: 229

      update AP_EXPENSE_FEED_DISTS EFD
      set    invoiced_flag = 'Y',
             INVOICE_ID = l_invoice_id,
             INVOICE_LINE_ID = l_invoice_line_id
      where  nvl(invoiced_flag,'N') = 'N'
      and    dist_code_combination_id = l_ccid
      and    exists
             (select 'Parent record meets group criteria from lines_cursor'
              from   AP_EXPENSE_FEED_LINES EFL,
                     AP_CARD_PROGRAMS CP
              where  EFL.feed_line_id = EFD.feed_line_id
              and    EFL.card_program_id = P_CARD_PROGRAM_ID
              and    EFL.card_program_id = CP.card_program_id
              and    ((nvl(efd.status_lookup_code,'VALIDATED') = 'APPROVED')
                      OR
                      (nvl(cp.exclude_unverified_flag,'N') = 'N' AND
                       nvl(efd.status_lookup_code,'VALIDATED') = 'VALIDATED')
                      OR
                      (nvl(cp.exclude_rejected_flag,'N') = 'N' AND
                       nvl(efd.status_lookup_code,'VALIDATED') = 'REJECTED')
                      OR
                      (nvl(cp.exclude_personal_flag,'N') = 'N' AND
                       nvl(efd.status_lookup_code,'VALIDATED') = 'PERSONAL')
                      OR
                      (nvl(cp.exclude_disputed_flag,'N') = 'N' AND
                       nvl(efd.status_lookup_code,'VALIDATED') = 'DISPUTED')
                      OR
                      (nvl(cp.exclude_held_flag,'N') = 'N' AND
                       nvl(efd.status_lookup_code,'VALIDATED') = 'HOLD')
                      OR
                      (nvl(cp.exclude_unreconciled_flag,'N') = 'N' AND
                       nvl(efd.status_lookup_code,'VALIDATED') = 'VERIFIED'))
              and    (efl.posted_date is null OR
                      (efl.posted_date between
                              nvl(P_START_DATE,efl.posted_date-1) and
                              nvl(P_END_DATE,efl.posted_date+1))));

Line: 282

    select vendor_id,
           vendor_site_id,
           card_program_currency_code
    into   l_vendor_id,
           l_vendor_site_id,
           l_invoice_currency_code
    from   ap_card_programs
    where  card_program_id = P_CARD_PROGRAM_ID;

Line: 292

    l_debug_info := 'Inserting into AP_INVOICES_INTERFACE';

Line: 294

    insert into AP_INVOICES_INTERFACE
    (INVOICE_ID,
     INVOICE_NUM,
     VENDOR_ID,
     VENDOR_SITE_ID,
     INVOICE_AMOUNT,
     INVOICE_CURRENCY_CODE,
     SOURCE,
     ORG_ID
     ) VALUES
    (l_invoice_id,
     substrb(to_char(l_invoice_id)||'-'||to_char(sysdate),1,50),
     l_vendor_id,
     l_vendor_site_id,
     l_sum,
     l_invoice_currency_code,
     'CREDIT CARD',
     l_org_id  -- Bug 5592139
     );