APPS.POS_PROFILE_PKG SQL Statements

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

Line: 24

select vendor_id, vendor_name, party_id, segment1 from ap_suppliers
where vendor_id = l_vendor_id;

Line: 75

    select employee_id, user_name,email_address into
      l_employee_id, l_username, l_email_address
      from fnd_user
      where user_id = p_user_id;

Line: 120

FUNCTION get_update_date_from_contact (
  p_contact_id IN NUMBER
) RETURN DATE
IS
  l_date DATE;

Line: 126

  SELECT last_update_date
  INTO l_date
  FROM hz_contact_points
  WHERE contact_point_id = p_contact_id;

Line: 131

END get_update_date_from_contact;

Line: 142

       SELECT count(contact_point_id)
       INTO l_count
       FROM hz_contact_points hcp
       WHERE hcp.owner_table_name = owner_table_name
       AND   hcp.owner_table_id = p_party_id
       AND   hcp.contact_point_type = p_contact_point_type
       AND   hcp.phone_line_type = p_phone_line_type
       AND   hcp.status = 'A' ;

Line: 167

       SELECT count(contact_point_id)
       INTO l_count
       FROM hz_contact_points hcp
       WHERE hcp.owner_table_name = p_owner_table_name
       AND   hcp.owner_table_id = p_party_id
       AND   hcp.contact_point_type = p_contact_point_type
       AND   hcp.primary_flag = 'Y'
       AND   hcp.status = 'A';

Line: 176

       SELECT count(contact_point_id)
       INTO l_count
       FROM hz_contact_points hcp
       WHERE hcp.owner_table_name = p_owner_table_name
       AND   hcp.owner_table_id = p_party_id
       AND   hcp.contact_point_type = p_contact_point_type
       AND   hcp.web_type = p_web_type
       AND   hcp.primary_flag = 'Y'
       AND   hcp.status = 'A';

Line: 193

PROCEDURE update_address_note (
  p_party_site_id	   IN NUMBER
, p_note       IN VARCHAR2
, x_status        out nocopy VARCHAR2
, x_exception_msg out nocopy VARCHAR2
)
IS
l_step NUMBER;

Line: 206

    POS_ADDRESS_NOTES_PKG.update_note(
    p_party_site_id
    ,p_note
    ,x_status
    ,x_exception_msg
    );

Line: 220

END update_address_note;

Line: 261

PROCEDURE update_address_type (
  p_party_site_use_id	   IN NUMBER
, p_status                 IN VARCHAR2
, p_object_version_number  IN NUMBER
, x_status        out nocopy VARCHAR2
, x_exception_msg out nocopy VARCHAR2
)
IS

l_msg_count 		NUMBER;

Line: 287

    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_obj_no,
        x_return_status => x_status,
        x_msg_count => l_msg_count,
        x_msg_data => x_exception_msg

    );

Line: 303

END update_address_type;

Line: 305

PROCEDURE update_all_address_type (
  p_party_site_id	   IN NUMBER
, p_rfq                IN VARCHAR2
, p_pur                IN VARCHAR2
, p_pay                IN VARCHAR2
, p_primaryPay                IN VARCHAR2
, x_status        out nocopy VARCHAR2
, x_exception_msg out nocopy VARCHAR2
)
IS

l_step NUMBER;

Line: 339

select pay.party_site_use_id, pur.party_site_use_id, rfq.party_site_use_id,
       pay.object_version_number, pur.object_version_number, rfq.object_version_number,
       pay.status, pur.status, rfq.status, hps.party_site_name
into l_pay_use_id, l_pur_use_id, l_rfq_use_id, l_pay_obj_no, l_pur_obj_no, l_rfq_obj_no,
     l_pay_status, l_pur_status, l_rfq_status , l_party_site_name
from hz_party_sites hps ,hz_party_site_uses pay, hz_party_site_uses pur, hz_party_site_uses rfq
where hps.party_site_id = p_party_site_id
--and hps.created_by_module like 'POS%'
and pay.party_site_id(+) = hps.party_site_id
and pur.party_site_id(+) = hps.party_site_id
and rfq.party_site_id(+) = hps.party_site_id
and pay.status(+) = 'A'
and pur.status(+) = 'A'
and rfq.status(+) = 'A'
and nvl(pay.end_date(+), sysdate) >= sysdate
and nvl(pur.end_date(+), sysdate) >= sysdate
and nvl(rfq.end_date(+), sysdate) >= sysdate
and nvl(pay.begin_date(+), sysdate) <= sysdate
and nvl(pur.begin_date(+), sysdate) <= sysdate
and nvl(rfq.begin_date(+), sysdate) <= sysdate
and pay.site_use_type(+) = 'PAY'
and pur.site_use_type(+) = 'PURCHASING'
and rfq.site_use_type(+) = 'RFQ';

Line: 378

            POS_PROFILE_PKG.update_address_type (
            p_party_site_use_id => l_rfq_use_id
            , p_status => l_status
            , p_object_version_number => l_rfq_obj_no
            , x_status => x_status
            , x_exception_msg => x_exception_msg
        );

Line: 414

            POS_PROFILE_PKG.update_address_type (
            p_party_site_use_id => l_pur_use_id
            , p_status => l_status
            , p_object_version_number => l_pur_obj_no
            , x_status => x_status
            , x_exception_msg => x_exception_msg
        );

Line: 452

            POS_PROFILE_PKG.update_address_type (
            p_party_site_use_id => l_pay_use_id
            , p_status => l_status
            , p_object_version_number => l_pay_obj_no
            , x_status => x_status
            , x_exception_msg => x_exception_msg
        );

Line: 481

END update_all_address_type;

Line: 483

PROCEDURE update_party_email(
  p_party_id           IN NUMBER
, p_party_type         IN VARCHAR2
, p_email              IN VARCHAR2
, x_status	OUT NOCOPY VARCHAR2
, x_exception_msg          OUT NOCOPY VARCHAR2
)
IS
  l_contact_point_id    NUMBER;

Line: 500

  l_update_date         DATE;

Line: 506

  x_exception_msg := 'BEGIN: update_party_email';

Line: 512

  SELECT hcp.contact_point_id, nvl(hcp.object_version_number,0), hcp.email_address
  INTO l_contact_point_id, l_object_version_number,l_old_email
  FROM hz_contact_points hcp
  WHERE hcp.owner_table_name = p_party_type
  AND   hcp.owner_table_id = p_party_id
  AND   hcp.contact_point_type = 'EMAIL'
  AND   hcp.primary_flag = 'Y'
  AND   HCP.STATUS = 'A';

Line: 536

  l_update_date             := get_update_date_from_contact(l_contact_point_id);

Line: 538

  HZ_CONTACT_POINT_V2PUB.update_contact_point(
       --p_api_version => 1.0,
       --p_commit  =>  fnd_api.g_false,
       p_contact_point_rec => l_contact_points_rec,
       p_email_rec => l_email_rec,
       p_object_version_number => l_object_version_number,
       --p_last_update_date => l_update_date, --get_update_date_from_contact(l_contact_point_id),
       x_return_status => return_status,
       x_msg_count => msg_count,
       x_msg_data => msg_data);

Line: 580

END update_party_email;

Line: 582

PROCEDURE update_party_phone(
  p_party_id           IN NUMBER
, p_party_type         IN VARCHAR2
, p_country_code       IN VARCHAR2
, p_area_code          IN VARCHAR2
, p_number             IN VARCHAR2
, p_extension          IN VARCHAR2
, x_status	OUT NOCOPY VARCHAR2
, x_exception_msg          OUT NOCOPY VARCHAR2
)
IS
  l_contact_point_id    NUMBER;

Line: 602

  l_update_date	          DATE;

Line: 608

  x_exception_msg := 'BEGIN: update_party_phone';

Line: 609

  x_exception_msg := 'SELECT: contact_point_id';

Line: 612

  SELECT hcp.contact_point_id, nvl(hcp.object_version_number,0), hcp.phone_number
  INTO l_contact_point_id, l_object_version_number, l_old_number
  FROM hz_contact_points hcp
  WHERE hcp.owner_table_name = p_party_type
  AND   hcp.owner_table_id = p_party_id
  AND   hcp.contact_point_type = 'PHONE'
  AND   hcp.phone_line_type = 'GEN'
  AND   hcp.primary_flag = 'Y'
  AND   hcp.status = 'A';

Line: 637

  l_update_date               := get_update_date_from_contact(l_contact_point_id);

Line: 639

  x_exception_msg := 'CALL: HZ_CONTACT_POINT_V2PUB.update_contact_point';

Line: 641

  HZ_CONTACT_POINT_V2PUB.update_contact_point(
       --p_api_version => 1.0,
       --p_commit  =>  fnd_api.g_false,
       p_contact_point_rec => l_contact_points_rec,
       p_phone_rec => l_phone_rec,
       p_object_version_number => l_object_version_number,
       --p_last_update_date => l_update_date, --get_update_date_from_contact(l_contact_point_id),
        x_return_status => return_status,
       x_msg_count => msg_count, x_msg_data => msg_data);

Line: 690

        raise_application_error(-20005,'Update phone number failed: '||x_exception_msg, true);

Line: 691

END update_party_phone;

Line: 693

PROCEDURE update_party_fax(
  p_party_id           IN NUMBER
, p_party_type           IN VARCHAR2
, p_country_code       IN VARCHAR2
, p_area_code          IN VARCHAR2
, p_number             IN VARCHAR2
, p_extension          IN VARCHAR2
, x_status	OUT NOCOPY VARCHAR2
, x_exception_msg          OUT NOCOPY VARCHAR2
)
IS
  l_contact_point_id    NUMBER;

Line: 713

  l_update_date	          DATE;

Line: 718

  x_exception_msg := 'BEGIN: update_party_fax';

Line: 723

  SELECT hcp.contact_point_id, nvl( hcp.object_version_number,0), hcp.phone_number
  INTO l_contact_point_id, l_object_version_number, l_old_number
  FROM hz_contact_points hcp
  WHERE hcp.owner_table_name = p_party_type
  AND   hcp.owner_table_id = p_party_id
  AND   hcp.contact_point_type = 'PHONE'
  AND   hcp.phone_line_type = 'FAX'
  AND   hcp.status = 'A' ;

Line: 747

  l_update_date               := get_update_date_from_contact(l_contact_point_id);

Line: 749

  HZ_CONTACT_POINT_V2PUB.update_contact_point(
       --p_api_version => 1.0,
       --p_commit  =>  fnd_api.g_false,
       p_contact_point_rec => l_contact_points_rec,
       p_phone_rec => l_phone_rec,
       p_object_version_number =>l_object_version_number,
       x_return_status => return_status,
       x_msg_count => msg_count, x_msg_data => msg_data);

Line: 793

END update_party_fax;

Line: 795

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_status           out nocopy VARCHAR2,
    x_exception_msg    out nocopy VARCHAR2
)
IS
l_step NUMBER;

Line: 819

lv_proc_name VARCHAR2(30) := 'buyer_update_address_site';

Line: 833

    POS_PROFILE_PKG.update_all_address_type(
      p_party_site_id => p_party_site_id
    , p_rfq           => p_rfqFlag
    , p_pur           => p_purFlag
    , p_pay           => p_payFlag
    , p_primaryPay           => p_primaryPayFlag
    , x_status        => x_status
    , x_exception_msg => x_exception_msg
    );

Line: 853

    POS_PROFILE_PKG.update_address_note(
        p_party_site_id => p_party_site_id
        , p_note => p_note
        , x_status  => x_status
        , x_exception_msg => x_exception_msg
    );

Line: 871

    update_party_phone(
        p_party_id => p_party_site_id,
        p_party_type => 'HZ_PARTY_SITES',
        p_country_code =>  null,
        p_area_code => p_phone_area_code ,
        p_number => p_phone,
        p_extension => null,
        x_status => x_status,
        x_exception_msg => x_exception_msg
    );

Line: 884

        raise_application_error(-20006,'Update Address: Failed to add phone for party site:'||p_party_site_id, true);

Line: 888

    update_party_fax(
        p_party_id => p_party_site_id,
        p_party_type => 'HZ_PARTY_SITES',
        p_country_code =>  null,
        p_area_code => p_fax_area_code ,
        p_number => p_fax,
        p_extension => null,
        x_status => x_status,
        x_exception_msg => x_exception_msg
    );

Line: 902

        raise_application_error(-20006,'Update Address: Failed to add fax for party site:'||p_party_site_id, true);

Line: 905

    update_party_email(
        p_party_id => p_party_site_id,
        p_party_type => 'HZ_PARTY_SITES',
        p_email => p_email,
        x_status => x_status,
        x_exception_msg => x_exception_msg
    );

Line: 916

        raise_application_error(-20006,'Update Address: Failed to add phone for party site:'||p_party_site_id, true);

Line: 919

END buyer_update_address_details;

Line: 934

select vendor_site_id, party_site_id, vendor_id from ap_supplier_sites_all where party_site_id = p_party_site_id;

Line: 941

select distinct ASCS.per_party_id
    from ap_supplier_contacts ASCS
    where (ASCS.inactive_date is null OR ASCS.inactive_date > sysdate)
    AND ASCS.org_party_site_id = p_party_site_id;

Line: 955

select uses.instrument_id, uses.order_of_preference, uses.start_date,
payee.supplier_site_id, payee.org_id, payee.org_type,
hps.party_id, uses.instrument_payment_use_id
from iby_pmt_instr_uses_all uses, iby_external_payees_all payee, hz_party_sites hps
where uses.instrument_type = 'BANKACCOUNT'
and payee.ext_payee_id = uses.ext_pmt_party_id
and payee.payee_party_id = hps.party_id
and payee.payment_function = 'PAYABLES_DISB'
and payee.party_site_id = hps.party_site_id
and hps.party_site_id = p_party_site_id
and (uses.end_date is null OR uses.end_date > sysdate)
order by uses.order_of_preference;

Line: 981

  select object_version_number, created_by_module
  into l_obj_ver, l_created_by_module
  from hz_party_sites
  where party_site_id = p_party_site_id;

Line: 990

  hz_party_site_v2pub.update_party_site(FND_API.G_FALSE,
      l_party_site_rec,
      l_obj_ver,
      x_status,
      l_msg_count,
      x_exception_msg);

Line: 1011

    POS_VENDOR_PUB_PKG.Update_Vendor_Site
    (
        l_vendor_site_rec,
        l_return_status,
        l_msg_count,
        l_msg_data
        );

Line: 1021

                        ' AP Update Vendor Site Status ' || l_return_status);

Line: 1023

                        ' AP Update Vendor Site Count ' || l_msg_count);

Line: 1025

                        ' AP Update Vendor Site exception msg ' || l_msg_data);

Line: 1048

                        ' AP Update Vendor Contact Status ' || l_return_status);

Line: 1050

                        ' AP Update Vendor Contact Count ' || l_msg_count);

Line: 1052

                        ' AP Update Vendor Contact exception msg ' || l_msg_data);