eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGI_CIS2007_IGIPVERP_PKG SQL Statements

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

Line: 4


 /* PO API to update the CORE tables */
  PROCEDURE pr_po_core_update(p_header_id IN NUMBER) IS
    l_awt_group_id ap_suppliers.awt_group_id%TYPE;

Line: 10


      SELECT vendor_id,
             verification_number,
             match_status,
             tax_treatment_status,
             cis_verification_date
        FROM igi_cis_verify_lines_t
       WHERE header_id = p_header_id;

Line: 19


   SELECT request_id
   FROM igi_cis_verify_headers_t
   WHERE header_id = p_header_id;

Line: 50


        SELECT g.group_id
          INTO l_awt_group_id
          FROM ap_awt_groups g
         WHERE g.group_id = l_awt_group_id
           AND nvl(g.inactive_date, SYSDATE + 1) > SYSDATE;

Line: 55


        SELECT lookup_code
          INTO l_match_flag
          FROM igi_lookups
         WHERE upper(meaning) =
               upper(cur_igi_cis_verify_headers_rec.match_status)
           AND lookup_type = 'IGI_CIS2007_MATCH_STATUS';

Line: 62


        /* calling to update certificates .. */
        /* Bug 5705187 */
         IGI_CIS2007_TAX_EFF_DATE.main (
         p_vendor_id      => cur_igi_cis_verify_headers_rec.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         => 'VERIFY',
         p_effective_date =>cur_igi_cis_verify_headers_rec.cis_verification_date
         );

Line: 73


        /* calling PO API to update PO tables - AP_SUPPLIERS, AP_SUPPLIER_SITES_ALL */
        pr_po_api(cur_igi_cis_verify_headers_rec.vendor_id,
                  cur_igi_cis_verify_headers_rec.verification_number,
                  l_match_flag,
                  cur_igi_cis_verify_headers_rec.cis_verification_date,
                  l_awt_group_id,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
		  l_request_id);

Line: 86


  END pr_po_core_update;

Line: 87


  /* API to UPDATE the HISTORY tables */
  PROCEDURE pr_audit_update(p_header_id IN NUMBER) IS
  BEGIN
    /* Populating the history tables */
    INSERT INTO igi_cis_verify_headers_h
      (header_id,
       request_id,
       unique_tax_reference_num,
       request_status_code,
       accounts_office_reference,
       program_id,
       program_application_id,
       program_login_id,
       program_update_date,
       last_update_date,
       last_updated_by,
       last_update_login,
       creation_date,
       created_by,
       cis_sender_id,
       tax_office_number,
       paye_reference)
      SELECT header_id,
             request_id,
             unique_tax_reference_num,
             request_status_code,
             accounts_office_reference,
             program_id,
             program_application_id,
             program_login_id,
             program_update_date,
             last_update_date,
             last_updated_by,
             last_update_login,
             creation_date,
             created_by,
             cis_sender_id,
             tax_office_number,
             paye_reference
        FROM igi_cis_verify_headers_t
       WHERE header_id = p_header_id;

Line: 128


    INSERT INTO igi_cis_verify_lines_h
      (header_id,
       action_indicator,
       vendor_type_lookup_code,
       vendor_id,
       vendor_name,
       vendor_site_id,
       vendor_site_code,
       first_name,
       second_name,
       last_name,
       salutation,
       trading_name,
       unique_tax_reference_num,
       work_reference,
       company_registration_number,
       national_insurance_number,
       verification_number,
       cis_verification_date,
       address_line1,
       address_line2,
       address_line3,
       address_line4,
       match_status,
       post_code,
       country,
       contact_number,
       tax_treatment_status,
       last_update_date,
       last_updated_by,
       last_update_login,
       creation_date,
       created_by,
       partnership_utr,
       partnership_name)
      SELECT header_id,
             action_indicator,
             vendor_type_lookup_code,
             vendor_id,
             vendor_name,
             vendor_site_id,
             vendor_site_code,
             first_name,
             second_name,
             last_name,
             salutation,
             trading_name,
             unique_tax_reference_num,
             work_reference,
             company_registration_number,
             national_insurance_number,
             verification_number,
             cis_verification_date,
             address_line1,
             address_line2,
             address_line3,
             address_line4,
             match_status,
             post_code,
             country,
             contact_number,
             tax_treatment_status,
             last_update_date,
             last_updated_by,
             last_update_login,
             creation_date,
             created_by,
             partnership_utr,
             partnership_name
        FROM igi_cis_verify_lines_t
       WHERE header_id = p_header_id;

Line: 199


    DELETE FROM igi_cis_verify_headers_t WHERE header_id = p_header_id;

Line: 200


    DELETE FROM igi_cis_verify_lines_t WHERE header_id = p_header_id;

Line: 201


  END pr_audit_update;

Line: 221


    SELECT 'PAID'
      INTO paid_status
      FROM ap_checks ac,
           ap_invoice_payments pay,
           -- Bug 5642198 Start
           ap_invoices inv,
           ap_supplier_sites pvs
           -- Bug 5642198 End
     WHERE ac.vendor_id = l_vendor_id
       AND ac.void_date IS NULL
       AND pay.check_id = ac.check_id
       AND accounting_date > add_months(tax_year_start, -24)
       -- Bug 5642198 Start
       AND inv.invoice_id = pay.invoice_id
       AND pvs.vendor_id = inv.vendor_id
       AND pvs.vendor_site_id = inv.vendor_site_id
       AND upper(pvs.allow_awt_flag) = 'Y'
       -- Bug 5642198 End
       AND rownum = 1;

Line: 245


  /* PO API to update AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL */
  PROCEDURE pr_po_api
  (
    p_vendor_id         IN NUMBER,
    p_verification_no   IN VARCHAR2,
    p_match_status      IN VARCHAR2,
    p_verification_date DATE,
    p_awt_group_id      IN NUMBER,
    p_utr_type          IN VARCHAR2,
    p_utr               IN NUMBER,
    p_sc_name           IN VARCHAR2,
    p_sc_ref_id         IN VARCHAR2,
    p_req_id            IN NUMBER     --Bug 5606118
  ) IS

/* Bug#7218825 - CIS WITHHOLDING PROJECT DUE TO AP ENHANCEMENT 6639866 ON 12.1 */

l_org_id                  ap_supplier_sites_all.org_id%TYPE;

Line: 266


select distinct org_id
from ap_supplier_sites_all
where vendor_id = c_vendor_id
and upper(allow_awt_flag) = 'Y';

Line: 272


select CREATE_AWT_DISTS_TYPE
from ap_system_parameters_all
where org_id = c_org_id
and upper(allow_awt_flag) = 'Y';

Line: 280


    UPDATE ap_suppliers pov
       SET pov.verification_number         = decode(p_verification_no,
                                                    NULL,
                                                    pov.verification_number,
                                                    p_verification_no),
           pov.match_status_flag           = p_match_status,
           pov.cis_verification_date       = p_verification_date,
           pov.awt_group_id                = p_awt_group_id,
           pov.pay_awt_group_id            = p_awt_group_id,     /* Also Updating the PAY_AWT_GROUP_ID for Bug#7218825 */
           pov.allow_awt_flag              = 'Y',
           pov.partnership_utr             = decode(p_utr,
                                                    NULL,
                                                    pov.partnership_utr,
                                                    decode(p_utr_type,
                                                           'P',
                                                           p_utr,
                                                           decode(pov.vendor_type_lookup_code,
                                                                  'PARTNERSHIP',
                                                                  p_utr,
                                                                  pov.partnership_utr))),
           pov.verification_request_id                  = p_req_id,                               --Bug 5606118
           pov.unique_tax_reference_num    = decode(p_utr,
                                                    NULL,
                                                    pov.unique_tax_reference_num,
                                                    decode(p_utr_type,
                                                           'U',
                                                           p_utr,
                                                           decode(pov.vendor_type_lookup_code,
                                                                  'PARTNERSHIP',
                                                                  pov.unique_tax_reference_num,
                                                                  p_utr))),
 -- Start : Bug <5586655> Commented for change request
   /*        pov.trading_name                = decode(p_sc_name,
                                                    NULL,
                                                    pov.trading_name,
                                                    decode(substr(p_sc_name,
                                                                  1,
                                                                  1),
                                                           '*',
                                                           pov.trading_name,
                                                           p_sc_name)),
           pov.vendor_name                 = decode(p_sc_name,
                                                    NULL,
                                                    pov.vendor_name,
                                                    decode(substr(p_sc_name,
                                                                  1,
                                                                  1),
                                                           '*',
                                                           substr(p_sc_name,
                                                                  2,
                                                                  length(p_sc_name)),
                                                           pov.vendor_name)),
 */
 --  End : Bug <5586655>
           pov.national_insurance_number   = decode(p_sc_ref_id,
                                                    NULL,
                                                    pov.national_insurance_number,
                                                    decode(length(p_sc_ref_id),
                                                           9,
                                                           p_sc_ref_id,
                                                           pov.national_insurance_number)),
           pov.company_registration_number = decode(p_sc_ref_id,
                                                    NULL,
                                                    pov.company_registration_number,
                                                    decode(length(p_sc_ref_id),
                                                           9,
                                                           pov.company_registration_number,
                                                           p_sc_ref_id))
     WHERE pov.vendor_id = p_vendor_id;

Line: 351


    UPDATE ap_suppliers pov
       SET pov.awt_group_id = p_awt_group_id,
           pov.pay_awt_group_id = p_awt_group_id,        /* Also Updating the PAY_AWT_GROUP_ID for Bug#7218825 */
           pov.allow_awt_flag = 'Y'
     WHERE pov.cis_parent_vendor_id = p_vendor_id
       AND pov.vendor_type_lookup_code NOT IN
           ('SOLETRADER', 'PARTNERSHIP', 'TRUST', 'COMPANY');

Line: 378


           UPDATE ap_supplier_sites_all povs
           SET povs.awt_group_id = p_awt_group_id, povs.allow_awt_flag = 'Y'
           WHERE povs.vendor_id = p_vendor_id
           AND povs.org_id = l_org_id
	     AND povs.allow_awt_flag = 'Y'; --Bug 5642191

Line: 386


           UPDATE ap_supplier_sites_all povs
           SET povs.awt_group_id = p_awt_group_id, povs.allow_awt_flag = 'Y'
           WHERE povs.vendor_id IN
             (SELECT pov.vendor_id
                FROM ap_suppliers pov
               WHERE pov.cis_parent_vendor_id = p_vendor_id
                 AND pov.vendor_type_lookup_code NOT IN
                     ('SOLETRADER', 'PARTNERSHIP', 'TRUST', 'COMPANY'))
                 AND povs.org_id = l_org_id
                 AND povs.allow_awt_flag='Y';  --Bug 5642191

Line: 402


           UPDATE ap_supplier_sites_all povs
           SET povs.pay_awt_group_id = p_awt_group_id, povs.allow_awt_flag = 'Y'
           WHERE povs.vendor_id = p_vendor_id
           AND povs.org_id = l_org_id
	     AND povs.allow_awt_flag = 'Y'; --Bug 5642191

Line: 410


            UPDATE ap_supplier_sites_all povs
           SET povs.pay_awt_group_id = p_awt_group_id, povs.allow_awt_flag = 'Y'
           WHERE povs.vendor_id IN
             (SELECT pov.vendor_id
                FROM ap_suppliers pov
               WHERE pov.cis_parent_vendor_id = p_vendor_id
                 AND pov.vendor_type_lookup_code NOT IN
                     ('SOLETRADER', 'PARTNERSHIP', 'TRUST', 'COMPANY'))
                 AND povs.org_id = l_org_id
                 AND povs.allow_awt_flag='Y';  --Bug 5642191

Line: 426


            UPDATE ap_supplier_sites_all povs
             SET povs.awt_group_id = p_awt_group_id,
                povs.pay_awt_group_id = p_awt_group_id,
                povs.allow_awt_flag = 'Y'
             WHERE povs.vendor_id = p_vendor_id
                 AND povs.org_id = l_org_id
	           AND povs.allow_awt_flag = 'Y'; --Bug 5642191

Line: 436


            UPDATE ap_supplier_sites_all povs
             SET povs.awt_group_id = p_awt_group_id,
                povs.pay_awt_group_id = p_awt_group_id,
                povs.allow_awt_flag = 'Y'
             WHERE povs.vendor_id IN
               (SELECT pov.vendor_id
                 FROM ap_suppliers pov
                 WHERE pov.cis_parent_vendor_id = p_vendor_id
                 AND pov.vendor_type_lookup_code NOT IN
                     ('SOLETRADER', 'PARTNERSHIP', 'TRUST', 'COMPANY'))
                 AND povs.org_id = l_org_id
                 AND povs.allow_awt_flag='Y';  --Bug 5642191