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

                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: 274

                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: 321

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

Line: 325

            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 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: 345

            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 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: 373

            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')

                  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: 424

l_women_update_date date;

Line: 429

l_minority_update_date date;

Line: 431

l_small_buss_update_date date;

Line: 436

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

Line: 448

        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';

Line: 463

                l_women_update_date := sysdate ;

Line: 469

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

Line: 478

        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';

Line: 493

                l_small_buss_update_date := sysdate;

Line: 498

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

Line: 507

        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';

Line: 523

                l_minority_update_date := sysdate;

Line: 530

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

Line: 562

        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';

Line: 584

            select POS_BUS_CLASS_ATTR_S.NEXTVAL
            into l_id
            from dual;

Line: 588

            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: 610

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

Line: 618

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

Line: 656

l_last_update_date date;

Line: 661

l_updated varchar2(100);

Line: 663

    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: 702

            update pos_bus_class_attr
            set ext_attr_1 = l_minority
            where classification_id = l_classification_id;

Line: 716

            l_updated
        );

Line: 718

         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: 730

        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;