采购退货接口code和相关处理情况信息

 采购退货需在原有的采购接收上面退货

l_iface_rcv_rec.processing_mode_code := 'BATCH';--ONLINE

分在线联机处理和批次处理

在线联机处理：

l_return := fnd_transaction.synchronous(g_timeout,
                                            l_outcome,
                                            l_message,
                                            'PO',
                                            'RCVTPO',
                                            'ONLINE',
                                            p_group_id,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL,
                                            NULL);
    IF (l_return = 0 AND (l_outcome NOT IN ('WARNING',
                                            'ERROR'))) THEN

      NULL;

    ELSIF (l_return = 1) THEN

 

批次处理：直接运行请求接收事务处理处理器

一般采购退货可能存在现有量不足等报错情况出现，可直接更新接口表信息使其重新运行

 Update RCV_TRANSACTIONS_INTERFACE
          set Last_Update_Date       = SYSDATE,
           --   Transaction_Date       = SYSDATE,
              Processing_Status_Code = 'PENDING',
              Processing_Request_Id  = null,
              request_id             = null,

              Primary_Quantity          = null,
              Primary_Unit_Of_Measure   = null,
              Validation_Flag           = 'Y',
              Interface_Available_Qty   = null,
              Interface_Transaction_Qty = null,
              Order_Transaction_Id      = null
        where group_id = V1.GROUP_ID;

       Update rcv_headers_interface
          set Processing_Status_Code = 'PENDING'
        where group_id = V1.GROUP_ID;

采购退货接口code如下：

l_iface_rcv_rec              rcv_transactions_interface%ROWTYPE;
    l_iface_hdr_rec              rcv_headers_interface%ROWTYPE;
    l_temp_lots_rec              mtl_transaction_lots_temp%ROWTYPE;

 l_iface_hdr_rec.header_interface_id := rcv_headers_interface_s.nextval;
      IF p_rcv_transaction_tbl(i).receipt_source_code = 'VENDOR' THEN
        SELECT MAX(pll.ship_to_location_id),
               MAX(pll.receiving_routing_id),
               MAX(pll.org_id)
          INTO l_ship_to_location_id,
               l_receiving_routing_id,
               p_rcv_transaction_tbl(i).org_id
          FROM po_line_locations_all pll
         WHERE pll.line_location_id = p_rcv_transaction_tbl(i).po_line_location_id;
        p_rcv_transaction_tbl(i).ship_to_location_id := nvl(p_rcv_transaction_tbl(i).ship_to_location_id,
                                                            l_ship_to_location_id);
        SELECT MAX(pod.deliver_to_location_id),
               MAX(pod.destination_type_code)
          INTO l_deliver_to_location_id,
               l_destination_type_code
          FROM po_distributions_all pod
         WHERE pod.po_distribution_id = p_rcv_transaction_tbl(i).po_distribution_id;
      ELSE
        SELECT MAX(nvl(b.inspection_required_flag,
                       'N'))
          INTO l_inspection_required_flag
          FROM mtl_system_items_b b
         WHERE b.organization_id = p_rcv_transaction_tbl(i).organization_id
           AND b.inventory_item_id = p_rcv_transaction_tbl(i).inventory_item_id;
        IF l_inspection_required_flag = 'Y' THEN
          l_receiving_routing_id := 2;
        ELSE
          SELECT p.rma_receipt_routing_id
            INTO l_receiving_routing_id
            FROM rcv_parameters p
           WHERE p.organization_id = p_rcv_transaction_tbl(i).organization_id;
        END IF;
        IF l_receiving_routing_id = 3 THEN
          l_destination_type_code := 'INVENTORY';
        ELSE
          l_destination_type_code := 'RECEIVING';
        END IF;
      END IF;
      po_moac_utils_pvt.set_org_context(nvl(p_rcv_transaction_tbl(i).org_id,
                                            po_moac_utils_pvt.get_current_org_id));
      l_iface_hdr_rec.receipt_header_id := nvl(l_iface_hdr_rec.receipt_header_id,
                                               p_rcv_transaction_tbl(i).shipment_header_id);
      l_iface_hdr_rec.employee_id       := nvl(p_rcv_transaction_tbl(i).deliver_to_person_id,
                                               fnd_global.employee_id);

      l_iface_hdr_rec.vendor_id               := p_rcv_transaction_tbl(i).vendor_id;
      l_iface_hdr_rec.vendor_site_id          := p_rcv_transaction_tbl(i).vendor_site_id;
      l_iface_hdr_rec.ship_to_organization_id := p_rcv_transaction_tbl(i).organization_id;
      l_iface_hdr_rec.customer_id             := p_rcv_transaction_tbl(i).customer_id;
      l_iface_hdr_rec.customer_site_id        := p_rcv_transaction_tbl(i).customer_site_id;
      l_iface_hdr_rec.group_id                := p_group_id;
      l_iface_hdr_rec.receipt_source_code     := p_rcv_transaction_tbl(i).receipt_source_code;
      l_iface_hdr_rec.location_id             := p_rcv_transaction_tbl(i).ship_to_location_id;
      l_iface_hdr_rec.expected_receipt_date   := p_rcv_transaction_tbl(i).transaction_date;
      IF l_iface_hdr_rec.receipt_header_id IS NULL THEN
        l_iface_hdr_rec.transaction_type := 'NEW';
      ELSE
        l_iface_hdr_rec.transaction_type := 'ADD';
      END IF;
      l_iface_hdr_rec.validation_flag        := 'Y';
      l_iface_hdr_rec.auto_transact_code     := 'RECEIVE';
      l_iface_hdr_rec.processing_status_code := 'PENDING';
      l_iface_hdr_rec.org_id                 := p_rcv_transaction_tbl(i).org_id;
      l_iface_hdr_rec.last_update_date       := SYSDATE;
      l_iface_hdr_rec.last_updated_by        := nvl(p_rcv_transaction_tbl(i).last_updated_by,
                                                    g_user_id);
      l_iface_hdr_rec.creation_date          := SYSDATE;
      l_iface_hdr_rec.created_by             := nvl(p_rcv_transaction_tbl(i).created_by,
                                                    g_user_id);
      INSERT INTO po.rcv_headers_interface
      VALUES l_iface_hdr_rec;

      l_iface_rcv_rec.group_id := p_group_id;
      l_iface_rcv_rec.header_interface_id := l_iface_hdr_rec.header_interface_id;
      l_iface_rcv_rec.interface_transaction_id := rcv_transactions_interface_s.nextval;
      p_rcv_transaction_tbl(i).interface_transaction_id := l_iface_rcv_rec.interface_transaction_id;
      l_iface_rcv_rec.last_update_date := SYSDATE;
      l_iface_rcv_rec.last_updated_by := nvl(p_rcv_transaction_tbl(i).last_updated_by,
                                             g_user_id);
      l_iface_rcv_rec.creation_date := SYSDATE;
      l_iface_rcv_rec.created_by := nvl(p_rcv_transaction_tbl(i).created_by,
                                        g_user_id);
      l_iface_rcv_rec.org_id := p_rcv_transaction_tbl(i).org_id;
      l_iface_rcv_rec.last_update_login := g_login_id;
      l_iface_rcv_rec.to_organization_id := p_rcv_transaction_tbl(i).organization_id;
      l_iface_rcv_rec.subinventory := p_rcv_transaction_tbl(i).subinventory;
      l_iface_rcv_rec.locator_id := p_rcv_transaction_tbl(i).locator_id;
      l_iface_rcv_rec.vendor_lot_num := p_rcv_transaction_tbl(i).vendor_lot_num;
      l_iface_rcv_rec.comments := p_rcv_transaction_tbl(i).comments;
      l_iface_rcv_rec.processing_status_code := 'PENDING';
      l_iface_rcv_rec.transaction_status_code := 'PENDING';
      l_iface_rcv_rec.processing_mode_code := 'BATCH';--ONLINE
      l_iface_rcv_rec.oe_order_header_id := p_rcv_transaction_tbl(i).oe_order_header_id;
      l_iface_rcv_rec.oe_order_line_id := p_rcv_transaction_tbl(i).oe_order_line_id;
      l_iface_rcv_rec.source_document_code := p_rcv_transaction_tbl(i).source_document_code;
      l_iface_rcv_rec.receipt_source_code := p_rcv_transaction_tbl(i).receipt_source_code;
      l_iface_rcv_rec.interface_source_code := p_rcv_transaction_tbl(i).interface_source_code;
      l_iface_rcv_rec.interface_source_line_id := p_rcv_transaction_tbl(i).interface_source_line_id;

      l_iface_rcv_rec.transaction_type      := p_rcv_transaction_tbl(i).transaction_type;
      l_iface_rcv_rec.destination_type_code := l_destination_type_code;
      IF l_iface_rcv_rec.transaction_type IN ('RETURN TO VENDOR',
                                              'RETURN TO RECEIVING')
         AND l_iface_rcv_rec.destination_type_code = 'INVENTORY' THEN
        l_iface_rcv_rec.from_subinventory := p_rcv_transaction_tbl(i).subinventory;
        l_iface_rcv_rec.from_locator_id   := p_rcv_transaction_tbl(i).locator_id;
      ELSE
        IF l_iface_rcv_rec.destination_type_code = 'INVENTORY' THEN
          l_iface_rcv_rec.auto_transact_code := 'DELIVER';
        ELSIF l_iface_rcv_rec.destination_type_code IN ('EXPENSE',
                                                        'SHOP FLOOR')
              AND l_receiving_routing_id IN (0,
                                             3) THEN
          l_iface_rcv_rec.auto_transact_code := 'DELIVER';
        ELSE
          l_iface_rcv_rec.auto_transact_code := 'RECEIVE';
        END IF;
      END IF;
      IF l_iface_rcv_rec.transaction_type = 'RETURN TO VENDOR' THEN
        l_iface_rcv_rec.destination_type_code := 'RECEIVING';
      END IF;

      l_iface_rcv_rec.po_line_id          := p_rcv_transaction_tbl(i).po_line_id;
      l_iface_rcv_rec.reason_id           := p_rcv_transaction_tbl(i).reason_id;
      l_iface_rcv_rec.po_header_id        := p_rcv_transaction_tbl(i).po_header_id;
      l_iface_rcv_rec.po_line_location_id := p_rcv_transaction_tbl(i).po_line_location_id;
      l_iface_rcv_rec.po_distribution_id  := p_rcv_transaction_tbl(i).po_distribution_id;
      l_iface_rcv_rec.po_release_id       := p_rcv_transaction_tbl(i).po_release_id;

      l_iface_rcv_rec.item_id       := p_rcv_transaction_tbl(i).inventory_item_id;
      l_iface_rcv_rec.item_revision := p_rcv_transaction_tbl(i).item_revision;

      l_iface_rcv_rec.ship_to_location_id := p_rcv_transaction_tbl(i).ship_to_location_id;

      l_iface_rcv_rec.deliver_to_location_id := nvl(p_rcv_transaction_tbl(i).deliver_to_location_id,
                                                    l_deliver_to_location_id);
      l_iface_rcv_rec.deliver_to_person_id   := p_rcv_transaction_tbl(i).deliver_to_person_id;
      l_iface_rcv_rec.shipment_header_id     := l_iface_hdr_rec.receipt_header_id;
      l_iface_rcv_rec.parent_transaction_id  := p_rcv_transaction_tbl(i).parent_transaction_id;

      l_iface_rcv_rec.validation_flag  := 'Y';
      l_iface_rcv_rec.transaction_date := nvl(p_rcv_transaction_tbl(i).transaction_date,
                                              SYSDATE);
      l_iface_rcv_rec.uom_code         := p_rcv_transaction_tbl(i).uom_code;
      l_iface_rcv_rec.quantity         := p_rcv_transaction_tbl(i).quantity;

      SELECT MAX(b.primary_uom_code),
             MAX(b.primary_unit_of_measure),
             MAX(b.lot_control_code),
             MAX(b.serial_number_control_code)
        INTO l_primary_uom_code,
             l_primary_uom_name,
             l_lot_control_code,
             l_serial_number_control_code
        FROM mtl_system_items_b b
       WHERE b.organization_id = p_rcv_transaction_tbl(i).organization_id
         AND b.inventory_item_id = p_rcv_transaction_tbl(i).inventory_item_id;

      IF l_primary_uom_code = p_rcv_transaction_tbl(i).uom_code THEN
        l_iface_rcv_rec.primary_unit_of_measure := l_uom_name;
        l_iface_rcv_rec.primary_quantity        := p_rcv_transaction_tbl(i).quantity;
      ELSE
        l_avl_qty                               := inv_convert.inv_um_convert(item_id       => p_rcv_transaction_tbl(i).inventory_item_id,
                                                                              PRECISION     => 5,
                                                                              from_quantity => p_rcv_transaction_tbl(i).quantity,
                                                                              from_unit     => NULL,
                                                                              to_unit       => NULL,
                                                                              from_name     => l_uom_name,
                                                                              to_name       => l_primary_uom_name);
        l_iface_rcv_rec.primary_unit_of_measure := l_primary_uom_name;
        l_iface_rcv_rec.primary_quantity        := l_avl_qty;
      END IF;
      l_iface_rcv_rec.attribute_category := p_rcv_transaction_tbl(i).attribute_category;
      l_iface_rcv_rec.attribute1         := p_rcv_transaction_tbl(i).attribute1;
      l_iface_rcv_rec.attribute2         := p_rcv_transaction_tbl(i).attribute2;
      l_iface_rcv_rec.attribute3         := p_rcv_transaction_tbl(i).attribute3;
      l_iface_rcv_rec.attribute4         := p_rcv_transaction_tbl(i).attribute4;
      l_iface_rcv_rec.attribute5         := p_rcv_transaction_tbl(i).attribute5;
      l_iface_rcv_rec.attribute6         := p_rcv_transaction_tbl(i).attribute6;
      l_iface_rcv_rec.attribute7         := p_rcv_transaction_tbl(i).attribute7;
      l_iface_rcv_rec.attribute8         := p_rcv_transaction_tbl(i).attribute8;
      l_iface_rcv_rec.attribute9         := p_rcv_transaction_tbl(i).attribute9;
      l_iface_rcv_rec.attribute10        := p_rcv_transaction_tbl(i).attribute10;
      l_iface_rcv_rec.attribute11        := p_rcv_transaction_tbl(i).attribute11;
      l_iface_rcv_rec.attribute12        := p_rcv_transaction_tbl(i).attribute12;
      l_iface_rcv_rec.attribute13        := p_rcv_transaction_tbl(i).attribute13;
      l_iface_rcv_rec.attribute14        := p_rcv_transaction_tbl(i).attribute14;
      l_iface_rcv_rec.attribute15        := p_rcv_transaction_tbl(i).attribute15;

      INSERT INTO po.rcv_transactions_interface
      VALUES l_iface_rcv_rec;

      --2.插入批次事务处理临时表
      IF (l_iface_rcv_rec.destination_type_code = 'INVENTORY') THEN
        IF l_lot_control_code = 2 THEN
          l_temp_lots_rec.transaction_temp_id    := l_iface_rcv_rec.interface_transaction_id;
          l_temp_lots_rec.product_transaction_id := l_iface_rcv_rec.interface_transaction_id;
          l_temp_lots_rec.lot_number             := p_rcv_transaction_tbl(i).lot_number;
          l_temp_lots_rec.lot_expiration_date    := p_rcv_transaction_tbl(i).lot_expiration_date;
          l_temp_lots_rec.origination_date       := p_rcv_transaction_tbl(i).origination_date;
          l_temp_lots_rec.transaction_quantity   := p_rcv_transaction_tbl(i).quantity;
          l_temp_lots_rec.primary_quantity       := inv_convert.inv_um_convert(item_id       => p_rcv_transaction_tbl(i).inventory_item_id,
                                                                               PRECISION     => 5,
                                                                               from_quantity => l_temp_lots_rec.transaction_quantity,
                                                                               from_unit     => p_rcv_transaction_tbl(i).uom_code,
                                                                               to_unit       => l_primary_uom_code,
                                                                               from_name     => NULL,
                                                                               to_name       => NULL);

          IF l_temp_lots_rec.primary_quantity < 0 THEN
            hss_api.set_message(p_app_name => 'INV',
                                p_msg_name => 'INV_CONS_SUP_NO_UOM_CONV');
            x_return_status := fnd_api.g_ret_sts_error;
            raise_exception(x_return_status);
          END IF;

          l_temp_lots_rec.product_code      := 'RCV';
          l_temp_lots_rec.last_update_date  := SYSDATE;
          l_temp_lots_rec.last_updated_by   := nvl(p_rcv_transaction_tbl(i).last_updated_by,
                                                   g_user_id);
          l_temp_lots_rec.creation_date     := SYSDATE;
          l_temp_lots_rec.created_by        := nvl(p_rcv_transaction_tbl(i).created_by,
                                                   g_user_id);
          l_temp_lots_rec.last_update_login := g_login_id;

          INSERT INTO mtl_transaction_lots_temp
          VALUES l_temp_lots_rec;
        END IF;