APPS.POS_SUPP_CLASSIFICATION_PKG SQL Statements

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

Line: 29

        insert into pos_bus_class_attr
        (
                classification_id, certificate_number,
                certifying_agency, expiration_date,
                class_status, status, created_by, creation_date,
                ext_attr_1, attribute1, attribute2, attribute3,
                attribute4, attribute5, last_updated_by,
                last_update_date, last_update_login,
                party_id, lookup_type, lookup_code, start_date_active,
                vendor_id
        )
        values
        ( POS_BUS_CLASS_ATTR_S.NEXTVAL , p_cert_num,
          p_cert_agency, p_exp_date,
          'APPROVED', 'A', fnd_global.user_id, sysdate,
          p_ext_attr_1, null, null, null,
          null, null, fnd_global.user_id,
          sysdate, fnd_global.login_id,
          p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate,
          p_vendor_id
        );

Line: 51

        select count(mapping_id)
        into l_count
        from pos_supplier_mappings
        where party_id = p_party_id;

Line: 57

            insert into pos_supplier_mappings
            (
                mapping_id, party_id , vendor_id ,
                created_by, creation_date,
                last_updated_by, last_update_date, last_update_login
            )
            values
            (
                pos_supplier_mapping_s.nextval, p_party_id, p_vendor_id,
                fnd_global.user_id, sysdate,
                fnd_global.user_id, sysdate, fnd_global.login_id
            );

Line: 71

        select mapping_id
        into l_mapping_id
        from pos_supplier_mappings
        where party_id = p_party_id;

Line: 76

        insert into pos_bus_class_reqs
        (
                bus_class_request_id, mapping_id,
                request_type, request_status,
                classification_id, lookup_type, lookup_code,
                ext_attr_1, certification_no, certification_agency,
                expiration_date, created_by, creation_date,
                last_updated_by, last_update_date, last_update_login
        )
        values
        (
          POS_BUS_CLASS_REQUEST_S.NEXTVAL, l_mapping_id ,
          'ADD', 'PENDING',
          null,  BUSINESS_CLASSIFICATION, p_lookup_code,
          p_ext_attr_1, p_cert_num , p_cert_agency,
          p_exp_date, fnd_global.user_id, sysdate,
          fnd_global.user_id, sysdate, fnd_global.login_id
        );

Line: 100

PROCEDURE update_bus_class_attr
(
p_party_id in number,
p_vendor_id in number,
p_selected  in varchar2,
p_classification_id in number,
p_request_id in number,
p_lookup_code in varchar2,
p_exp_date    in date,
p_cert_num  in varchar2,
p_cert_agency in varchar2,
p_ext_attr_1 in varchar2,
p_class_status in varchar2,
x_classification_id out nocopy number,
x_request_id  out nocopy number,
x_status    out nocopy varchar2,
x_exception_msg out nocopy varchar2
)
IS
l_class_id number;

Line: 125

    IF ( p_selected = 'N' ) then
        if (p_classification_id is not null and p_classification_id > 0 ) then
            update pos_bus_class_attr
            set status='I', last_update_date = sysdate,
            last_updated_by = fnd_global.user_id,
            end_date_active = sysdate
            where classification_id = p_classification_id;

Line: 136

	    update pos_bus_class_reqs
            set request_status = 'REJECTED',
            last_update_date = sysdate,
            last_updated_by = fnd_global.user_id,
            last_update_login = fnd_global.login_id
            where bus_class_request_id = p_request_id;

Line: 144

            update pos_bus_class_reqs
            set request_status = 'REJECTED',
            last_update_date = sysdate,
            last_updated_by = fnd_global.user_id,
            last_update_login = fnd_global.login_id
            where bus_class_request_id = p_request_id;

Line: 159

                update pos_bus_class_attr
                set certificate_number = p_cert_num,
                    certifying_agency = p_cert_agency,
                    expiration_date = p_exp_date,
                    class_status = 'APPROVED',
                    status = 'A',
                    ext_attr_1= p_ext_attr_1,
                    last_updated_by = fnd_global.user_id,
                    last_update_date = sysdate,
                    last_update_login= fnd_global.login_id
                where classification_id = p_classification_id;

Line: 173

                    update pos_bus_class_reqs
                        set request_status = 'APPROVED',
                        classification_id = l_class_id,
                        last_updated_by = fnd_global.user_id,
                        last_update_login = fnd_global.login_id,
                        last_update_date = sysdate
                    where  bus_class_request_id = p_request_id;

Line: 182

                select pos_bus_class_attr_s.nextval
                into l_class_id
                from dual;

Line: 187

                insert into pos_bus_class_attr
                (
                    classification_id, certificate_number,
                    certifying_agency, expiration_date,
                    class_status, status, created_by, creation_date,
                    ext_attr_1, attribute1, attribute2, attribute3,
                    attribute4, attribute5, last_updated_by,
                    last_update_date, last_update_login,
                    party_id, lookup_type, lookup_code, start_date_active,
                    vendor_id
                )
                values
                ( l_class_id, p_cert_num,
                  p_cert_agency, p_exp_date,
                  'APPROVED', 'A', fnd_global.user_id, sysdate,
                  p_ext_attr_1, null, null, null,
                  null, null, fnd_global.user_id,
                  sysdate, -1,
                  p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate
                  , p_vendor_id
                );

Line: 209

                update pos_bus_class_reqs
                    set request_status = 'APPROVED',
                    classification_id = l_class_id,
                    last_updated_by = fnd_global.user_id,
                    last_update_login = fnd_global.login_id,
                    last_update_date = sysdate
                where  bus_class_request_id = p_request_id;

Line: 223

                update pos_bus_class_attr
                set status='I', last_update_date = sysdate,
                last_updated_by = fnd_global.user_id,
                last_update_login = fnd_global.login_id
                where classification_id = p_classification_id;*/

Line: 229

                select count(mapping_id)
                into l_count
                from pos_supplier_mappings
                where party_id = p_party_id;

Line: 235

                insert into pos_supplier_mappings
                    (
                        mapping_id, party_id , vendor_id ,
                        created_by, creation_date,
                        last_updated_by, last_update_date, last_update_login
                    )
                    values
                    (
                        pos_supplier_mapping_s.nextval, p_party_id, p_vendor_id,
                        fnd_global.user_id, sysdate,
                        fnd_global.user_id, sysdate, fnd_global.login_id
                    );

Line: 249

                select mapping_id
                into l_mapping_id
                from pos_supplier_mappings
                where party_id = p_party_id;

Line: 255

                UPDATE pos_bus_class_reqs
                SET request_status = 'REJECTED'
                WHERE mapping_id = l_mapping_id
                AND lookup_type = BUSINESS_CLASSIFICATION
                AND lookup_code = p_lookup_code;

Line: 261

                insert into pos_bus_class_reqs
                (
                    bus_class_request_id, mapping_id,
                    request_type, request_status,
                    classification_id, lookup_type, lookup_code,
                    ext_attr_1, certification_no, certification_agency,
                    expiration_date, created_by, creation_date,
                    last_updated_by, last_update_date, last_update_login
                )
                values
                (
                  POS_BUS_CLASS_REQUEST_S.NEXTVAL, l_mapping_id ,
                  'ADD', 'PENDING',
                  p_classification_id,  BUSINESS_CLASSIFICATION, p_lookup_code,
                  p_ext_attr_1, p_cert_num , p_cert_agency,
                  p_exp_date, fnd_global.user_id, sysdate,
                  fnd_global.user_id, sysdate, fnd_global.login_id
                );

Line: 280

                update pos_bus_class_reqs
                set
                    request_type = 'ADD',
                    request_status = 'PENDING',
                    certification_no= p_cert_num,
                    certification_agency = p_cert_agency,
                    expiration_date = p_exp_date,
                    ext_attr_1= p_ext_attr_1,
                    last_updated_by = fnd_global.user_id,
                    last_update_date = sysdate,
                    last_update_login= fnd_global.login_id
                where bus_class_request_id = p_request_id;

Line: 327

            SELECT mapping_id INTO l_map_id
              FROM pos_supplier_mappings psm
             WHERE psm.party_id = p_party_id;

Line: 331

            SELECT request_status INTO req_status
              FROM pos_bus_class_reqs
             WHERE lookup_type = p_lkp_type_tbl(i)
               AND lookup_code = p_lkp_code_tbl(i)
			   AND mapping_id = l_map_id
               AND last_update_date = (SELECT Max(last_update_date)
                                         FROM pos_bus_class_reqs
                                        WHERE lookup_type = p_lkp_type_tbl(i)
                                          AND lookup_code = p_lkp_code_tbl(i)
                                          AND mapping_id = l_map_id);

Line: 352

            SELECT Max(classification_id) INTO l_class_id
              FROM pos_bus_class_attr
             WHERE party_id = p_party_id
               AND lookup_type = p_lkp_type_tbl(i)
               AND lookup_code = p_lkp_code_tbl(i)
               AND ( end_date_active is null or trunc(end_date_active) > sysdate )
               AND status='A'
               AND class_status = 'APPROVED'
               AND party_site_id IS NULL
               AND vendor_site_id IS NULL
               AND classification_id not in ( select classification_id
                                              from pos_bus_class_reqs pbcr,
                                                   pos_supplier_mappings psm
                                             where psm.party_id = p_party_id
                                               and psm.mapping_id = pbcr.mapping_id
                                               and pbcr.request_status = 'PENDING'
                                               and pbcr.request_type in ( 'ADD', 'UPDATE' )
                                               and pbcr.classification_id is not null
                                            );

Line: 382

            SELECT Count(*) INTO rec_count
            FROM (SELECT lookup_code
                    FROM pos_bus_class_attr pca1
                    WHERE status = 'A'
                      AND ( pca1.end_date_active is null or trunc(pca1.end_date_active) > sysdate )
                      AND lookup_type = p_lkp_type_tbl(i)
                      AND lookup_code = p_lkp_code_tbl(i)
                      and party_id = p_party_id
                      AND class_status in ('APPROVED')
                      AND party_site_id IS NULL
                      AND vendor_site_id IS NULL

                  UNION

                  SELECT lookup_code
                    FROM pos_bus_class_reqs pbcr, pos_supplier_mappings psm
                   WHERE psm.party_id = p_party_id
                     AND psm.mapping_id = pbcr.mapping_id
                     AND pbcr.lookup_type = p_lkp_type_tbl(i)
                     AND pbcr.lookup_code = p_lkp_code_tbl(i)
                     AND pbcr.request_status = 'PENDING'
                     AND pbcr.request_type in ('ADD', 'UPDATE')
                  )tbl_all;

Line: 435

l_women_update_date date;

Line: 440

l_minority_update_date date;

Line: 442

l_small_buss_update_date date;

Line: 447

    select decode(WOMEN_OWNED_FLAG, 'Y', 'Y','N'),
    decode(SMALL_BUSINESS_FLAG,'Y','Y','N'), MINORITY_GROUP_LOOKUP_CODE
    into l_women, l_small_business, l_minority
    from ap_suppliers
    where vendor_id = pVendorId;

Line: 459

        select decode(pca.status, 'A', 'Y', 'N')
        , pca.last_update_date
        into l_women_status, l_women_update_date
        from pos_bus_class_attr pca
        where pca.lookup_type='POS_BUSINESS_CLASSIFICATIONS'
        and pca.lookup_code='WOMEN_OWNED'
        and pca.start_date_active <= sysdate
        and (pca.end_date_active is null or pca.end_date_active > sysdate)
        and pca.party_id = pPartyId
        and pca.status = 'A'
        and pca.class_status = 'APPROVED'
        AND party_site_id IS NULL
        AND vendor_site_id IS null;

Line: 476

                l_women_update_date := sysdate ;

Line: 482

        update ap_suppliers
        set women_owned_flag = l_women_status
        , last_update_date = l_women_update_date
        where vendor_id = pVendorId;

Line: 491

        select decode(pca.status, 'A', 'Y', 'N')
        , pca.last_update_date
        into l_small_business_status, l_small_buss_update_date
        from pos_bus_class_attr pca
        where pca.lookup_type='POS_BUSINESS_CLASSIFICATIONS'
        and pca.lookup_code='SMALL_BUSINESS'
        and pca.start_date_active <= sysdate
        and (pca.end_date_active is null or pca.end_date_active > sysdate)
        and pca.party_id = pPartyId
        and pca.status = 'A'
        and pca.class_status = 'APPROVED'
        AND party_site_id IS NULL
        AND vendor_site_id IS null;

Line: 508

                l_small_buss_update_date := sysdate;

Line: 513

        update ap_suppliers
        set small_business_flag = l_small_business_status
        , last_update_date = l_small_buss_update_date
        where vendor_id = pVendorId;

Line: 522

        select decode(pca.status, 'A', 'Y', 'N')
        , pca.last_update_date, pca.ext_attr_1
        into l_minority_status
        , l_minority_update_date, l_minority_type
        from pos_bus_class_attr pca
        where pca.lookup_type='POS_BUSINESS_CLASSIFICATIONS'
        and pca.lookup_code='MINORITY_OWNED'
        and pca.start_date_active <= sysdate
        and (pca.end_date_active is null or pca.end_date_active > sysdate)
        and pca.party_id = pPartyId
        and pca.status = 'A'
        and pca.class_status = 'APPROVED'
        AND party_site_id IS NULL
        AND vendor_site_id IS null;

Line: 540

                l_minority_update_date := sysdate;

Line: 547

        update ap_suppliers
        set MINORITY_GROUP_LOOKUP_CODE = l_minority_type
        , last_update_date = l_minority_update_date
        where vendor_id = pVendorId;

Line: 579

        select decode(pca.status, 'A', 'Y', 'N'), pca.classification_id,
        -- if the Status is A then returns Y else return N
        pca.class_status
        into l_pos_status, x_classification_id
        ,l_approval_status
        from  pos_bus_class_attr pca
        where pca.lookup_type=p_class_category
        and pca.lookup_code=p_class_code
        and pca.start_date_active <= sysdate
        and (pca.end_date_active is null or pca.end_date_active > sysdate)
        and pca.party_id = pPartyId
	      and pca.status = 'A'
        AND party_site_id IS NULL
        AND vendor_site_id IS null;

Line: 603

            select POS_BUS_CLASS_ATTR_S.NEXTVAL
            into l_id
            from dual;

Line: 607

            insert into pos_bus_class_attr
                (
                classification_id, certificate_number,
                certifying_agency, expiration_date,
                class_status, status, created_by, creation_date,
                attribute1, attribute2, attribute3,
                attribute4, attribute5, last_updated_by,
                last_update_date, last_update_login,
                party_id, lookup_type, lookup_code, start_date_active, vendor_id
                )
                values
                (l_id, null, null, null,
                 'APPROVED', 'A', -1, sysdate, null, null, null, null, null,
                 -1, sysdate, -1,
                 pPartyId, p_class_category, p_class_code,sysdate, pVendorId);

Line: 629

            update pos_bus_class_attr
            set status='I' , end_date_active = sysdate
            where classification_id = x_classification_id;

Line: 637

            update pos_bus_class_attr
            set class_status = 'APPROVED'
            where classification_id = x_classification_id;

Line: 675

l_last_update_date date;

Line: 680

l_updated varchar2(100);

Line: 682

    select decode(WOMEN_OWNED_FLAG, 'Y', 'Y','N'), decode (SMALL_BUSINESS_FLAG, 'Y','Y','N'),
    MINORITY_GROUP_LOOKUP_CODE, last_update_date
    into l_women, l_small_business, l_minority, l_last_update_date
    from ap_suppliers
    where vendor_id = pVendorId;

Line: 721

            update pos_bus_class_attr
            set ext_attr_1 = l_minority
            where classification_id = l_classification_id;

Line: 735

            l_updated
        );

Line: 737

         if(l_classification_id is not null AND l_updated ='Y') then
            update pos_bus_class_attr
            set ext_attr_1 = l_minority
            where classification_id = l_classification_id;

Line: 749

        update pos_bus_class_attr
            set status='I', last_update_date = sysdate,
            last_updated_by = fnd_global.user_id,
            end_date_active = sysdate
            where classification_id = pClassificationId;

Line: 779

    insert into pos_bus_class_attr
    (
                classification_id, certificate_number,
                certifying_agency, expiration_date,
                class_status, status, created_by, creation_date,
                ext_attr_1, attribute1, attribute2, attribute3,
                attribute4, attribute5, last_updated_by,
                last_update_date, last_update_login,
                party_id, lookup_type, lookup_code, start_date_active,
                vendor_id, party_site_id
    )
    values
    (           POS_BUS_CLASS_ATTR_S.NEXTVAL , p_cert_num,
                p_cert_agency, p_exp_date,
                'APPROVED', 'A', fnd_global.user_id, sysdate,
                p_ext_attr_1, null, null, null,
                null, null, fnd_global.user_id,
                sysdate, fnd_global.login_id,
                p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate,
                p_vendor_id, p_party_site_id
    );

Line: 804

PROCEDURE update_addr_bus_class_attr
(
p_party_id in number,
p_vendor_id in number,
p_party_site_id IN NUMBER,
p_selected  in varchar2,
p_classification_id in number,
p_lookup_code in varchar2,
p_exp_date    in date,
p_cert_num  in varchar2,
p_cert_agency in varchar2,
p_ext_attr_1 in varchar2,
p_class_status in varchar2,
x_classification_id out nocopy number,
x_request_id  out nocopy number,
x_status    out nocopy varchar2,
x_exception_msg out nocopy varchar2
)
IS
l_class_id number;

Line: 829

    IF ( p_selected = 'N' ) then

      if (p_classification_id is not null and p_classification_id > 0 ) then
        update pos_bus_class_attr
        set status='I', last_update_date = sysdate,
        last_updated_by = fnd_global.user_id,
        end_date_active = sysdate
        where classification_id = p_classification_id;

Line: 847

      update pos_bus_class_attr
      set certificate_number = p_cert_num,
      certifying_agency = p_cert_agency,
      expiration_date = p_exp_date,
      class_status = 'APPROVED',
      status = 'A',
      ext_attr_1= p_ext_attr_1,
      last_updated_by = fnd_global.user_id,
      last_update_date = sysdate,
      last_update_login= fnd_global.login_id
      where classification_id = p_classification_id;

Line: 861

      select pos_bus_class_attr_s.nextval
      into l_class_id
      from dual;

Line: 866

      insert into pos_bus_class_attr
      (
        classification_id, certificate_number,
        certifying_agency, expiration_date,
        class_status, status, created_by, creation_date,
        ext_attr_1, attribute1, attribute2, attribute3,
        attribute4, attribute5, last_updated_by,
        last_update_date, last_update_login,
        party_id, lookup_type, lookup_code, start_date_active,
        vendor_id, party_site_id
      )
      values
      ( l_class_id, p_cert_num,
        p_cert_agency, p_exp_date,
        'APPROVED', 'A', fnd_global.user_id, sysdate,
        p_ext_attr_1, null, null, null,
        null, null, fnd_global.user_id,
        sysdate, -1,
        p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate
        , p_vendor_id, p_party_site_id
      );

Line: 910

  SELECT lookup_code, ext_attr_1, end_date_active, certificate_number, certifying_agency
  FROM pos_bus_class_attr
  WHERE vendor_id = p_vendor_id
  AND party_site_id = p_party_site_id
  AND vendor_site_id IS null
  AND status = 'A';

Line: 921

    SELECT party_id INTO p_party_id FROM ap_suppliers WHERE vendor_id = p_vendor_id;

Line: 930

        insert into pos_bus_class_attr
        (
          classification_id, certificate_number,
          certifying_agency, expiration_date,
          class_status, status, created_by, creation_date,
          ext_attr_1, attribute1, attribute2, attribute3,
          attribute4, attribute5, last_updated_by,
          last_update_date, last_update_login,
          party_id, lookup_type, lookup_code, start_date_active,
          vendor_id, party_site_id, vendor_site_id
        )
        values
        (
          POS_BUS_CLASS_ATTR_S.NEXTVAL , p_cert_num,
          p_cert_agency, p_exp_date,
          'APPROVED', 'A', fnd_global.user_id, sysdate,
          p_ext_attr_1, null, null, null,
          null, null, fnd_global.user_id,
          sysdate, fnd_global.login_id,
          p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate,
          p_vendor_id, p_party_site_id, p_vendor_site_id
        );

Line: 980

    insert into pos_bus_class_attr
    (
                classification_id, certificate_number,
                certifying_agency, expiration_date,
                class_status, status, created_by, creation_date,
                ext_attr_1, attribute1, attribute2, attribute3,
                attribute4, attribute5, last_updated_by,
                last_update_date, last_update_login,
                party_id, lookup_type, lookup_code, start_date_active,
                vendor_id, party_site_id, vendor_site_id
    )
    values
    (           POS_BUS_CLASS_ATTR_S.NEXTVAL , p_cert_num,
                p_cert_agency, p_exp_date,
                'APPROVED', 'A', fnd_global.user_id, sysdate,
                p_ext_attr_1, null, null, null,
                null, null, fnd_global.user_id,
                sysdate, fnd_global.login_id,
                p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate,
                p_vendor_id, p_party_site_id, p_vendor_site_id
    );

Line: 1005

PROCEDURE update_site_bus_class_attr
(
p_party_id in number,
p_vendor_id in number,
p_party_site_id IN NUMBER,
p_vendor_site_id IN NUMBER,
p_selected  in varchar2,
p_classification_id in number,
p_lookup_code in varchar2,
p_exp_date    in date,
p_cert_num  in varchar2,
p_cert_agency in varchar2,
p_ext_attr_1 in varchar2,
p_class_status in varchar2,
x_classification_id out nocopy number,
x_request_id  out nocopy number,
x_status    out nocopy varchar2,
x_exception_msg out nocopy varchar2
)
IS
l_class_id number;

Line: 1031

    IF ( p_selected = 'N' ) then

      if (p_classification_id is not null and p_classification_id > 0 ) then
        update pos_bus_class_attr
        set status='I', last_update_date = sysdate,
        last_updated_by = fnd_global.user_id,
        end_date_active = sysdate
        where classification_id = p_classification_id;

Line: 1049

      update pos_bus_class_attr
      set certificate_number = p_cert_num,
      certifying_agency = p_cert_agency,
      expiration_date = p_exp_date,
      class_status = 'APPROVED',
      status = 'A',
      ext_attr_1= p_ext_attr_1,
      last_updated_by = fnd_global.user_id,
      last_update_date = sysdate,
      last_update_login= fnd_global.login_id
      where classification_id = p_classification_id;

Line: 1063

      select pos_bus_class_attr_s.nextval
      into l_class_id
      from dual;

Line: 1068

      insert into pos_bus_class_attr
      (
        classification_id, certificate_number,
        certifying_agency, expiration_date,
        class_status, status, created_by, creation_date,
        ext_attr_1, attribute1, attribute2, attribute3,
        attribute4, attribute5, last_updated_by,
        last_update_date, last_update_login,
        party_id, lookup_type, lookup_code, start_date_active,
        vendor_id, party_site_id, vendor_site_id
      )
      values
      ( l_class_id, p_cert_num,
        p_cert_agency, p_exp_date,
        'APPROVED', 'A', fnd_global.user_id, sysdate,
        p_ext_attr_1, null, null, null,
        null, null, fnd_global.user_id,
        sysdate, -1,
        p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate
        , p_vendor_id, p_party_site_id, p_vendor_site_id
      );