APPS.IGI_CIS2007_CDROM_PKG SQL Statements

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

Line: 322

    Procedure Match_And_Update(P_Upl_Option IN Varchar2) IS
      l_message      Varchar2(1000);

Line: 337

         Select subcontractor_utr,
                subcontractor_name,
                subcontractor_ref_id,
                tax_month_last_paid,
                tax_treatment
         From igi_cis_cdrom_lines_t;

Line: 345

         Select count(*) cnt
         From igi_cis_cdrom_lines_t
         Where subcontractor_utr = p_utr;

Line: 363

                  Select pov.vendor_id
                  Into l_vendor_id
                  From ap_suppliers pov
                  Where pov.partnership_utr = C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr;

Line: 379

                     Select pov.vendor_id
                     Into l_vendor_id
                     From ap_suppliers pov
                     Where pov.unique_tax_reference_num = C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr;

Line: 396

         Update igi_cis_cdrom_lines_t
         Set match_flag = l_match_flag
         Where subcontractor_utr = C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr;

Line: 408

            /* calling to update existing certificates .. */
            /* Bug 5705187 */
            IGI_CIS2007_TAX_EFF_DATE.main (
             p_vendor_id      => l_vendor_id,
             p_vendor_site_id => NULL,
             p_tax_grp_id     => l_awt_group_id,
             p_pay_tax_grp_id => l_awt_group_id,                     /* Bug 7218825 */
             p_source         => 'CDROM',
             p_effective_date => l_date
                                  );

Line: 419

            /* calling PO API to update PO tables - AP_SUPPLIERS, AP_SUPPLIER_SITES */
            Igi_cis2007_igipverp_pkg.pr_po_api(l_vendor_id,
                                             NULL,
                                             l_matched_flag,
                                             l_date,
                                             l_awt_group_id,
                                             l_utr_type,
                                             C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr,
                                             C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_name,
                                             C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_ref_id,
                                             fnd_global.conc_request_id); --Bug 5606118

Line: 438

   End Match_And_Update;

Line: 443

         Select subcontractor_utr,
                subcontractor_name,
                subcontractor_ref_id,
                tax_month_last_paid,
                tax_treatment
         From igi_cis_cdrom_lines_t
         Where match_flag = 'M'
         Order by subcontractor_name;

Line: 453

         Select subcontractor_utr,
                subcontractor_name,
                subcontractor_ref_id,
                tax_month_last_paid,
                tax_treatment
         From igi_cis_cdrom_lines_t
         Where match_flag = 'U'
         Order by subcontractor_name;

Line: 463

         Select vendor_name,
                partnership_utr ,
                unique_tax_reference_num ,
                national_insurance_number,
                company_registration_number
         From ap_suppliers
         Where cis_enabled_flag = 'Y'
           and vendor_id not in (
              SELECT p.vendor_id
              FROM ap_suppliers p, igi_cis_cdrom_lines_t l
              WHERE (p.partnership_utr = l.subcontractor_utr
                     Or p.unique_tax_reference_num = l.subcontractor_utr)
                And match_flag = 'M')
         Order by vendor_name;

Line: 479

         Select atr.tax_name tax_name,
                atr.tax_rate tax_rate,
                pvs.vendor_site_code site_code,
                nvl(inv.inv_count,0) inv_count
         From ap_suppliers pv,
              ap_supplier_sites pvs,
              ap_awt_tax_rates atr,
              (Select count(distinct(aps.invoice_id)) inv_count,
                      api.vendor_id,
                      api.vendor_site_id
               From ap_payment_schedules aps,
                    ap_invoices api
               Where aps.amount_remaining > 0
                 And api.invoice_id = aps.invoice_id
               Group by api.vendor_id, api.vendor_site_id) inv
         Where (pv.partnership_utr = P_Utr
                   Or pv.unique_tax_reference_num = P_Utr)
           And pvs.vendor_id = pv.vendor_id
           And atr.vendor_id(+) = pvs.vendor_id
           And atr.vendor_site_id(+) = pvs.vendor_site_id
           And trunc(sysdate) between trunc(nvl(atr.start_date(+), sysdate))
               And trunc(nvl(atr.end_date(+), sysdate))
           And inv.vendor_id(+) = atr.vendor_id
           And inv.vendor_site_id(+) = atr.vendor_site_id
         Order by pvs.vendor_site_code, atr.tax_name;

Line: 508

          fnd_file.put_line(fnd_file.output,'CD-ROM Data : Matched and Updated');

Line: 555

          fnd_file.put_line(fnd_file.output,'CD-ROM Data : Unmatched and Not Updated');

Line: 609

         Select PARTNERSHIP_UTR
         From ap_suppliers
         Where cis_enabled_flag='Y'
         Group by PARTNERSHIP_UTR
         Having count(PARTNERSHIP_UTR) > 1;

Line: 616

         Select UNIQUE_TAX_REFERENCE_NUM
         From ap_suppliers
         Where cis_enabled_flag='Y'
         Group by UNIQUE_TAX_REFERENCE_NUM
         Having count(UNIQUE_TAX_REFERENCE_NUM) > 1;

Line: 673

         Select SUBCONTRACTOR_UTR
         From igi_cis_cdrom_lines_t
         Group by SUBCONTRACTOR_UTR
         Having count(SUBCONTRACTOR_UTR) >1;

Line: 714

      E_Update_Not_Allowed Exception;

Line: 717

         Select Meaning
         From Igi_lookups
         Where Lookup_type = 'IGI_CIS2007_CDROM_OPTION'
         and lookup_code = 'U';

Line: 742

            Raise E_Update_Not_Allowed;

Line: 755

      Match_And_Update(p_upl_option);