ASL批准供应商-sql

l_rec_null      po_approved_supplier_list%ROWTYPE;

    l_rec           po_approved_supplier_list%ROWTYPE;

    l_asl_id NUMBER;

  begin

      l_asl_id := NULL;

      l_rec    := l_rec_null;

      l_rec.using_organization_id := p_organization_id;

      l_rec.owning_organization_id := p_organization_id;

      l_rec.vendor_business_type   := 'DIRECT';

      l_rec.asl_status_id := 2;

      l_rec.last_update_date := SYSDATE;

      l_rec.last_updated_by  := fnd_global.user_id;

      l_rec.creation_date    := SYSDATE;

      l_rec.created_by       := fnd_global.user_id;

      l_rec.manufacturer_id  := NULL;

      l_rec.vendor_id        := p_vendor_id;

      l_rec.vendor_site_id   := p_vendor_site_id;

      l_rec.item_id          := p_item_id;

      l_rec.category_id      := NULL;

      /*IF rec_lines.vendor_site_id IS NULL THEN

        l_rec.vendor_site_id := get_vendor_site_id(rec_lines.org_id,

                                                   rec_lines.vendor_id);

      ELSE

        l_rec.vendor_site_id := rec_lines.vendor_site_id;

      END IF;*/

      /*l_rec.vendor_site_id         := null;

      l_rec.primary_vendor_item    := NULL;

      l_rec.manufacturer_asl_id    := NULL;

      l_rec.review_by_date         := NULL;*/

      l_rec.comments               := 'AUTO_CREATE';

      /*l_rec.attribute_category     := rec_lines.attribute_category;

      l_rec.attribute1             := rec_lines.attribute1;

      l_rec.attribute2             := rec_lines.attribute2;

      l_rec.attribute3             := rec_lines.attribute3;

      l_rec.attribute4             := rec_lines.attribute4;

      l_rec.attribute5             := rec_lines.attribute5;

      l_rec.attribute6             := rec_lines.attribute6;

      l_rec.attribute7             := rec_lines.attribute7;

      l_rec.attribute8             := rec_lines.attribute8;

      l_rec.attribute9             := rec_lines.attribute9;

      l_rec.attribute10            := rec_lines.attribute10;

      l_rec.attribute11            := rec_lines.attribute11;

      l_rec.attribute12            := rec_lines.attribute12;

      l_rec.attribute13            := rec_lines.attribute13;

      l_rec.attribute14            := rec_lines.attribute14;

      l_rec.attribute15            := rec_lines.attribute15;*/

      l_rec.last_update_login      := fnd_global.login_id;

      /*l_rec.request_id             := NULL;

      l_rec.program_application_id := NULL;

      l_rec.program_id             := NULL;

      l_rec.program_update_date    := NULL;

      l_rec.disable_flag           := rec_lines.disable_flag;*/

      --调用api插入到正式表--

      cux_pub_iface_comm_api_pub.process_asl_list(p_init_msg_list   => fnd_api.g_false,

                                                  p_commit          => fnd_api.g_false,

                                                  x_return_status   => x_return_status,

                                                  x_msg_count       => x_msg_count,

                                                  x_msg_data        => x_msg_data,

                                                  p_org_id          => p_org_id,

                                                  p_po_asl_list_rec => l_rec,

                                                  x_asl_id          => l_asl_id);

po_moac_utils_pvt.set_org_context(p_org_id);

    l_vendor_site_id := p_po_asl_list_rec.vendor_site_id;

    IF l_vendor_site_id IS NULL THEN

      SELECT MAX(sit.vendor_site_id)

        INTO l_vendor_site_id

        FROM ap_supplier_sites_all sit

       WHERE sit.vendor_id = p_po_asl_list_rec.vendor_id

         AND sit.org_id = p_org_id

         AND purchasing_site_flag = 'Y'

         AND nvl(rfq_only_site_flag,

                 'N') != 'Y'

         AND SYSDATE < nvl(inactive_date,

                           SYSDATE + 1);

    END IF;

    l_record_unique := po_asl_sv.check_record_unique(NULL,

                                                     p_po_asl_list_rec.vendor_id,

                                                     l_vendor_site_id,

                                                     p_po_asl_list_rec.item_id,

                                                     p_po_asl_list_rec.category_id,

                                                     p_po_asl_list_rec.owning_organization_id);

    IF l_record_unique THEN

      l_asl_id := po_approved_supplier_list_s.nextval;

      INSERT INTO po_approved_supplier_list

        (asl_id,

         using_organization_id,

         owning_organization_id,

         vendor_business_type,

         asl_status_id,

         last_update_date,

         last_updated_by,

         creation_date,

         created_by,

         vendor_id,

         vendor_site_id,

         item_id,

         primary_vendor_item,

         last_update_login,

         comments,

         disable_flag,

         attribute_category,

         attribute1,

         attribute2,

         attribute3,

         attribute4,

         attribute5,

         attribute6,

         attribute7,

         attribute8,

         attribute9,

         attribute10,

         attribute11,

         attribute12,

         attribute13,

         attribute14,

         attribute15)

      VALUES

        (l_asl_id,

         p_po_asl_list_rec.using_organization_id,

         p_po_asl_list_rec.owning_organization_id,

         nvl(p_po_asl_list_rec.vendor_business_type,

             'DIRECT'),

         p_po_asl_list_rec.asl_status_id,

         SYSDATE,

         g_user_id,

         SYSDATE,

         g_user_id,

         p_po_asl_list_rec.vendor_id,

         l_vendor_site_id,

         p_po_asl_list_rec.item_id,

         p_po_asl_list_rec.primary_vendor_item,

         g_login_id,

         p_po_asl_list_rec.comments,

         p_po_asl_list_rec.disable_flag,

         p_po_asl_list_rec.attribute_category,

         p_po_asl_list_rec.attribute1,

         p_po_asl_list_rec.attribute2,

         p_po_asl_list_rec.attribute3,

         p_po_asl_list_rec.attribute4,

         p_po_asl_list_rec.attribute5,

         p_po_asl_list_rec.attribute6,

         p_po_asl_list_rec.attribute7,

         p_po_asl_list_rec.attribute8,

         p_po_asl_list_rec.attribute9,

         p_po_asl_list_rec.attribute10,

         p_po_asl_list_rec.attribute11,

         p_po_asl_list_rec.attribute12,

         p_po_asl_list_rec.attribute13,

         p_po_asl_list_rec.attribute14,

         p_po_asl_list_rec.attribute15);

      INSERT INTO po_asl_attributes

        (asl_id,

         using_organization_id,

         vendor_id,

         vendor_site_id,

         item_id,

         category_id,

         created_by,

         creation_date,

         last_updated_by,

         last_update_date,

         last_update_login)

      VALUES

        (l_asl_id,

         p_po_asl_list_rec.using_organization_id,

         p_po_asl_list_rec.vendor_id,

         l_vendor_site_id,

         p_po_asl_list_rec.item_id,

         p_po_asl_list_rec.category_id,

         g_user_id,

         SYSDATE,

         g_user_id,

         SYSDATE,

         g_login_id);

    ELSE

      IF (p_po_asl_list_rec.item_id IS NOT NULL) THEN

        SELECT MAX(asl_id)

          INTO l_asl_id

          FROM po_approved_supplier_list pasl

         WHERE pasl.vendor_id = p_po_asl_list_rec.vendor_id

           AND (pasl.vendor_site_id = l_vendor_site_id OR (pasl.vendor_site_id IS NULL AND l_vendor_site_id IS NULL))

           AND pasl.item_id = p_po_asl_list_rec.item_id

           AND using_organization_id = p_po_asl_list_rec.using_organization_id;

      ELSE

        SELECT MAX(asl_id)

          INTO l_asl_id

          FROM po_approved_supplier_list pasl

         WHERE pasl.vendor_id = p_po_asl_list_rec.vendor_id

           AND (pasl.vendor_site_id = l_vendor_site_id OR (pasl.vendor_site_id IS NULL AND l_vendor_site_id IS NULL))

           AND pasl.category_id = p_po_asl_list_rec.category_id

           AND using_organization_id = p_po_asl_list_rec.using_organization_id;

      END IF;

      UPDATE po_approved_supplier_list pasl

         SET pasl.asl_status_id      = p_po_asl_list_rec.asl_status_id,

             pasl.disable_flag       = p_po_asl_list_rec.disable_flag,

             pasl.last_update_date   = SYSDATE,

             pasl.last_updated_by    = g_user_id,

             pasl.last_update_login  = g_login_id,

             pasl.comments           = p_po_asl_list_rec.comments,

             pasl.attribute_category = p_po_asl_list_rec.attribute_category,

             pasl.attribute1         = p_po_asl_list_rec.attribute1,

             pasl.attribute2         = p_po_asl_list_rec.attribute2,

             pasl.attribute3         = p_po_asl_list_rec.attribute3,

             pasl.attribute4         = p_po_asl_list_rec.attribute4,

             pasl.attribute5         = p_po_asl_list_rec.attribute5,

             pasl.attribute6         = p_po_asl_list_rec.attribute6,

             pasl.attribute7         = p_po_asl_list_rec.attribute7,

             pasl.attribute8         = p_po_asl_list_rec.attribute8,

             pasl.attribute9         = p_po_asl_list_rec.attribute9,

             pasl.attribute10        = p_po_asl_list_rec.attribute10,

             pasl.attribute11        = p_po_asl_list_rec.attribute11,

             pasl.attribute12        = p_po_asl_list_rec.attribute12,

             pasl.attribute13        = p_po_asl_list_rec.attribute13,

             pasl.attribute14        = p_po_asl_list_rec.attribute14,

             pasl.attribute15        = p_po_asl_list_rec.attribute15

       WHERE pasl.asl_id = l_asl_id;