APPS.POS_SUPPLIER_ADDRESS_PKG SQL Statements

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

Line: 16

        SELECT party_site_use_id
          FROM hz_party_site_uses
         WHERE party_site_id = p_party_site_id
           AND site_use_type = p_address_type
           AND status = 'A';

Line: 114

        SELECT party_site_use_id, object_version_number
          FROM hz_party_site_uses
         WHERE party_site_id = p_party_site_id
           AND site_use_type = p_address_type
           AND status = 'A';

Line: 165

      l_step := 'call hz_party_site_v2pub.update_party_site_use';

Line: 169

      hz_party_site_v2pub.update_party_site_use
        ( p_init_msg_list         => FND_API.G_FALSE,
          p_party_site_use_rec    => l_party_site_use_rec,
          p_object_version_number => l_rec.object_version_number,
          x_return_status         => x_return_status,
          x_msg_count             => x_msg_count,
          x_msg_data              => x_msg_data
          );

Line: 267

        SELECT ou_id
        INTO l_reg_ou_id
        FROM pos_supplier_registrations
        WHERE po_vendor_id = p_vendor_id;

Line: 278

   FOR x IN (SELECT name
             FROM hr_operating_units o
             WHERE mo_global.check_access(organization_id) = 'Y'
             AND o.organization_id = nvl(l_reg_ou_id,o.organization_id)
             AND NOT exists
                 (SELECT 1
                  FROM ap_system_parameters_all
                  WHERE o.organization_id = org_id
                  )
             )
     LOOP
        l_found := TRUE;

Line: 472

   pos_hz_contact_point_pkg.update_party_site_phone
     (
      p_party_site_id     => l_party_site_id,
      p_country_code      =>  NULL,
      p_area_code         => p_phone_area_code ,
      p_number            => p_phone_number,
      p_extension         => NULL,
      x_return_status     => x_return_status,
      x_msg_count         => x_msg_count,
      x_msg_data          => x_msg_data
    );

Line: 490

   pos_hz_contact_point_pkg.update_party_site_fax
     (
      p_party_site_id     => l_party_site_id,
      p_country_code      =>  NULL,
      p_area_code         => p_fax_area_code ,
      p_number            => p_fax_number,
      p_extension         => NULL,
      x_return_status     => x_return_status,
      x_msg_count         => x_msg_count,
      x_msg_data          => x_msg_data
    );

Line: 508

   pos_hz_contact_point_pkg.update_party_site_email
     (
      p_party_site_id     => l_party_site_id,
      p_email             => p_email_address,
      x_return_status     => x_return_status,
      x_msg_count         => x_msg_count,
      x_msg_data          => x_msg_data
    );

Line: 532

        SELECT ou_id
        INTO l_reg_ou_id
	FROM pos_supplier_registrations
	WHERE po_vendor_id = p_vendor_id;

Line: 628

PROCEDURE update_supplier_address
  (p_vendor_id        IN  NUMBER,
   p_vendor_party_id  IN  NUMBER,
   p_party_site_id    IN  NUMBER,
   p_party_site_name  IN  VARCHAR2,
   p_address_line1    IN  VARCHAR2,
   p_address_line2    IN  VARCHAR2,
   p_address_line3    IN  VARCHAR2,
   p_address_line4    IN  VARCHAR2,
   p_country          IN  VARCHAR2,
   p_city             IN  VARCHAR2,
   p_state            IN  VARCHAR2,
   p_province         IN  VARCHAR2,
   p_postal_code      IN  VARCHAR2,
   p_county           IN  VARCHAR2,
   p_rfq_flag         IN  VARCHAR2,
   p_pur_flag         IN  VARCHAR2,
   p_pay_flag         IN  VARCHAR2,
   p_primary_pay_flag IN  VARCHAR2,
   p_phone_area_code  IN  VARCHAR2,
   p_phone_number     IN  VARCHAR2,
   p_phone_extension  IN  VARCHAR2,
   p_fax_area_code    IN  VARCHAR2,
   p_fax_number       IN  VARCHAR2,
   p_email_address    IN  VARCHAR2,
   x_return_status    OUT nocopy VARCHAR2,
   x_msg_count        OUT nocopy NUMBER,
   x_msg_data         OUT nocopy VARCHAR2
   )
   IS
     l_party_site_rec    hz_party_site_v2pub.party_site_rec_type;

Line: 663

        SELECT object_version_number,location_id
          from hz_locations
          where location_id =
          (SELECT location_id
           FROM hz_party_sites
           WHERE party_site_id = p_party_site_id
           ) FOR UPDATE;

Line: 674

        select object_version_number, party_site_name
          from hz_party_sites
          where party_site_id = p_party_site_id FOR UPDATE;

Line: 681

   SAVEPOINT update_supplier_address_sp;

Line: 714

   hz_location_v2pub.update_location
     (  p_init_msg_list         => fnd_api.g_true,
        p_location_rec          => l_location_rec,
        p_object_version_number => l_obj_ver,
        x_return_status         => x_return_status,
        x_msg_count             => x_msg_count,
        x_msg_data              => x_msg_data
    );

Line: 724

      ROLLBACK TO update_supplier_address_sp;

Line: 735

      hz_party_site_v2pub.update_party_site
        (p_init_msg_list         => fnd_api.g_false,
         p_party_site_rec        => l_party_site_rec,
         p_object_version_number => l_rec2.object_version_number,
         x_return_status         => x_return_status,
         x_msg_count             => x_msg_count,
         x_msg_data              => x_msg_data
         );

Line: 745

         ROLLBACK TO update_supplier_address_sp;

Line: 751

   pos_hz_contact_point_pkg.update_party_site_phone
     (
      p_party_site_id     => p_party_site_id,
      p_country_code      =>  NULL,
      p_area_code         => p_phone_area_code ,
      p_number            => p_phone_number,
      p_extension         => NULL,
      x_return_status     => x_return_status,
      x_msg_count         => x_msg_count,
      x_msg_data          => x_msg_data
    );

Line: 764

      ROLLBACK TO update_supplier_address_sp;

Line: 769

   pos_hz_contact_point_pkg.update_party_site_fax
     (
      p_party_site_id     => p_party_site_id,
      p_country_code      =>  NULL,
      p_area_code         => p_fax_area_code ,
      p_number            => p_fax_number,
      p_extension         => NULL,
      x_return_status     => x_return_status,
      x_msg_count         => x_msg_count,
      x_msg_data          => x_msg_data
    );

Line: 782

      ROLLBACK TO update_supplier_address_sp;

Line: 787

   pos_hz_contact_point_pkg.update_party_site_email
     (
      p_party_site_id     => p_party_site_id,
      p_email             => p_email_address,
      x_return_status     => x_return_status,
      x_msg_count         => x_msg_count,
      x_msg_data          => x_msg_data
      );

Line: 797

      ROLLBACK TO update_supplier_address_sp;

Line: 811

      ROLLBACK TO update_supplier_address_sp;

Line: 825

      ROLLBACK TO update_supplier_address_sp;

Line: 839

      ROLLBACK TO update_supplier_address_sp;

Line: 846

END update_supplier_address;

Line: 859

        select ASCS.vendor_site_id, ASCS.relationship_id, ASCS.org_contact_id,
        ASCS.rel_party_id, ASCS.party_site_id, ASCS.vendor_contact_id
        from ap_supplier_contacts ASCS
        where (ASCS.inactive_date is null OR ASCS.inactive_date > sysdate)
        AND ASCS.org_party_site_id = p_org_party_site_id
        AND ASCS.per_party_id = p_contact_party_id;

Line: 885

            ' Before Calling pos_vendor_pub_pkg.update_vendor_contact');

Line: 901

      AP_VENDOR_PUB_PKG.Update_Vendor_Contact
        (
          p_api_version           => 1.0,
          p_init_msg_list         => FND_API.G_TRUE,
          p_commit                => FND_API.G_FALSE,
          p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
          p_vendor_contact_rec    => l_vendor_contact_rec,
          x_return_status         => x_return_status,
          x_msg_count             => x_msg_count,
          x_msg_data              => x_msg_data
        );

Line: 915

            ' After Calling pos_vendor_pub_pkg.update_vendor_contact');

Line: 938

address assignments are not added or deleted */
PROCEDURE update_address_to_contact
  (p_contact_party_id   IN  NUMBER,
   p_org_party_site_id  IN  NUMBER,
   p_vendor_id          IN  NUMBER,
   x_return_status      OUT nocopy VARCHAR2,
   x_msg_count          OUT nocopy NUMBER,
   x_msg_data           OUT nocopy VARCHAR2
   )
IS

CURSOR l_cur IS
        select ASCS.vendor_site_id, ASCS.relationship_id, ASCS.org_contact_id,
        ASCS.rel_party_id, ASCS.party_site_id, ASCS.vendor_contact_id, ASCS.inactive_date
        from ap_supplier_contacts ASCS
        where (ASCS.inactive_date is null OR ASCS.inactive_date > sysdate)
        AND ASCS.org_party_site_id = p_org_party_site_id
        AND ASCS.per_party_id = p_contact_party_id;

Line: 976

            ' Before Calling pos_vendor_pub_pkg.update_vendor_contact from update_address_to_contact');

Line: 992

      AP_VENDOR_PUB_PKG.Update_Vendor_Contact
        (
          p_api_version           => 1.0,
          p_init_msg_list         => FND_API.G_TRUE,
          p_commit                => FND_API.G_FALSE,
          p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
          p_vendor_contact_rec    => l_vendor_contact_rec,
          x_return_status         => x_return_status,
          x_msg_count             => x_msg_count,
          x_msg_data              => x_msg_data
        );

Line: 1006

            ' After Calling pos_vendor_pub_pkg.update_vendor_contact from update_address_to_contact');

Line: 1024

END update_address_to_contact;

Line: 1054

        SELECT 1
          FROM ap_supplier_contacts
         WHERE org_party_site_id = p_org_party_site_id
           AND per_party_id = p_contact_party_id
           AND (inactive_date is null OR inactive_date >= sysdate)
           AND rownum = 1;

Line: 1069

        SELECT hzr.relationship_id, hzr.party_id rel_party_id, hoc.org_contact_id
          FROM ap_suppliers pv, hz_relationships hzr, hz_org_contacts hoc
         WHERE pv.vendor_id = p_vendor_id
            AND hzr.relationship_type = 'CONTACT'
            AND hzr.relationship_code = 'CONTACT_OF'
            AND hzr.subject_id = p_contact_party_id
            AND hzr.subject_type = 'PERSON'
            AND hzr.subject_table_name = 'HZ_PARTIES'
            AND hzr.object_type = 'ORGANIZATION'
            AND hzr.object_table_name = 'HZ_PARTIES'
            AND hzr.object_id = pv.party_id
            AND hzr.status = 'A'
	    AND trunc(SYSDATE) between Trunc(hzr.START_DATE) AND
	        NVL(Trunc(hzr.END_DATE),trunc(SYSDATE + 1))
            AND hzr.relationship_id = hoc.party_relationship_id;

Line: 1208

PROCEDURE update_address_assignment_dff
  (x_contact_party_id   IN  NUMBER,
   x_org_party_site_id  IN  NUMBER,
   x_vendor_id          IN  NUMBER,
   x_attribute_category   IN VARCHAR2 default null,
   x_attribute1 IN VARCHAR2 default null,
   x_attribute2 IN VARCHAR2 default null,
   x_attribute3 IN VARCHAR2 default null,
   x_attribute4 IN VARCHAR2 default null,
   x_attribute5 IN VARCHAR2 default null,
   x_attribute6 IN VARCHAR2 default null,
   x_attribute7 IN VARCHAR2 default null,
   x_attribute8 IN VARCHAR2 default null,
   x_attribute9 IN VARCHAR2 default null,
   x_attribute10 IN VARCHAR2 default null,
   x_attribute11 IN VARCHAR2 default null,
   x_attribute12 IN VARCHAR2 default null,
   x_attribute13 IN VARCHAR2 default null,
   x_attribute14 IN VARCHAR2 default null,
   x_attribute15 IN VARCHAR2 default null,
   x_return_status OUT nocopy VARCHAR2,
   x_msg_count OUT nocopy NUMBER,
   x_msg_data  OUT nocopy VARCHAR2
   ) IS
   BEGIN

   /* Here We Need To Call AP Package For Updating The Address Assignments */

   AP_VENDOR_PUB_PKG.Update_Address_Assignments_DFF
    (
    p_api_version       => 1,
    p_init_msg_list     => FND_API.G_FALSE,
    p_commit            => FND_API.G_FALSE,
    p_contact_party_id  => x_contact_party_id,
    p_org_party_site_id => x_org_party_site_id,
    p_attribute_category=> x_attribute_category,
    p_attribute1        => x_attribute1,
    p_attribute2        => x_attribute2,
    p_attribute3        => x_attribute3,
    p_attribute4        => x_attribute4,
    p_attribute5        => x_attribute5,
    p_attribute6        => x_attribute6,
    p_attribute7        => x_attribute7,
    p_attribute8        => x_attribute8,
    p_attribute9        => x_attribute9,
    p_attribute10       => x_attribute10,
    p_attribute11       => x_attribute11,
    p_attribute12       => x_attribute12,
    p_attribute13       => x_attribute13,
    p_attribute14       => x_attribute14,
    p_attribute15       => x_attribute15,
    x_return_status     => x_return_status,
    x_msg_count         => x_msg_count,
    x_msg_data          => x_msg_data
    );

Line: 1266

            ' After Calling AP_VENDOR_PUB_PKG.Update_Address_Assignments_DFF ');

Line: 1280

   END update_address_assignment_dff;

Line: 1287

PROCEDURE buyer_update_address_details
(p_party_site_id     IN  NUMBER,
 p_rfqFlag           IN  VARCHAR2,
 p_purFlag           IN  VARCHAR2,
 p_payFlag           IN  VARCHAR2,
 p_primaryPayFlag    IN  VARCHAR2,
 p_note              IN  VARCHAR2,
 p_phone_area_code   IN  VARCHAR2 DEFAULT NULL,
 p_phone             IN  VARCHAR2 DEFAULT NULL,
 p_phone_contact_id  IN  NUMBER DEFAULT NULL,
 p_phone_obj_ver_num IN  NUMBER DEFAULT NULL,
 p_fax_area_code     IN  VARCHAR2 DEFAULT NULL,
 p_fax               IN  VARCHAR2 DEFAULT NULL,
 p_fax_contact_id    IN  NUMBER DEFAULT NULL,
 p_fax_obj_ver_num   IN  NUMBER DEFAULT NULL,
 p_email             IN  VARCHAR2 DEFAULT NULL,
 p_email_contact_id  IN  NUMBER DEFAULT NULL,
 p_email_obj_ver_num IN  NUMBER DEFAULT NULL,
 x_return_status     OUT NOCOPY VARCHAR2,
 x_msg_count         OUT NOCOPY NUMBER,
 x_msg_data          OUT NOCOPY VARCHAR2
 )
  IS
     l_status VARCHAR2(1);

Line: 1313

   SAVEPOINT update_supplier_address_sp;

Line: 1316

   pos_hz_contact_point_pkg.update_party_site_phone
     (
      p_party_site_id     => p_party_site_id,
      p_country_code      =>  NULL,
      p_area_code         => p_phone_area_code ,
      p_number            => p_phone,
      p_extension         => NULL,
      x_return_status     => x_return_status,
      x_msg_count         => x_msg_count,
      x_msg_data          => x_msg_data
    );

Line: 1329

      ROLLBACK TO update_supplier_address_sp;

Line: 1334

   pos_hz_contact_point_pkg.update_party_site_fax
     (
      p_party_site_id     => p_party_site_id,
      p_country_code      =>  NULL,
      p_area_code         => p_fax_area_code ,
      p_number            => p_fax,
      p_extension         => NULL,
      x_return_status     => x_return_status,
      x_msg_count         => x_msg_count,
      x_msg_data          => x_msg_data
    );

Line: 1347

      ROLLBACK TO update_supplier_address_sp;

Line: 1352

   pos_hz_contact_point_pkg.update_party_site_email
     (
      p_party_site_id     => p_party_site_id,
      p_email             => p_email,
      x_return_status     => x_return_status,
      x_msg_count         => x_msg_count,
      x_msg_data          => x_msg_data
      );

Line: 1362

      ROLLBACK TO update_supplier_address_sp;

Line: 1376

      ROLLBACK TO update_supplier_address_sp;

Line: 1390

      ROLLBACK TO update_supplier_address_sp;

Line: 1404

      ROLLBACK TO update_supplier_address_sp;

Line: 1409

   pos_address_notes_pkg.update_note
     ( p_party_site_id => p_party_site_id,
       p_note          => p_note,
       x_status        => l_status,
       x_exception_msg => l_msg
       );

Line: 1428

END buyer_update_address_details;