EBS實踐—PO內部申請自動接收入庫

declare
   CURSOR cur_req_data IS
      SELECT prl.*
        FROM po_requisition_lines_all   prl,
             po_requisition_headers_all prh
       WHERE prh.requisition_header_id = prl.requisition_header_id
         AND prl.requisition_line_id = p_requisition_line_id;
 
    l_iface_hdr_rec po.rcv_headers_interface%ROWTYPE;
    l_iface_rcv_rec po.rcv_transactions_interface%ROWTYPE;
 
    l_shipment_number VARCHAR2(30);
  BEGIN
    BEGIN
      SELECT rsh.shipment_num
        INTO l_shipment_number
        FROM rcv_shipment_headers rsh,
             rcv_shipment_lines   rsl
       WHERE rsh.shipment_header_id = rsl.shipment_header_id
         AND rsl.requisition_line_id = p_requisition_line_id
         AND rsl.shipment_line_id = p_shipment_line_id;
    EXCEPTION
      WHEN no_data_found THEN
        l_shipment_number := NULL;
      WHEN OTHERS THEN
        l_shipment_number := NULL;
    END;
    FOR l_req_data IN cur_req_data
    LOOP
      BEGIN
        SELECT po.rcv_headers_interface_s.NEXTVAL
          INTO l_iface_hdr_rec.header_interface_id
          FROM dual;
        /*SELECT po.rcv_interface_groups_s.NEXTVAL
        INTO l_iface_hdr_rec.group_id
        FROM dual;*/
        l_iface_hdr_rec.group_id          := g_receipt_group_id;
        l_iface_hdr_rec.last_update_date  := SYSDATE;
        l_iface_hdr_rec.last_updated_by   := fnd_global.user_id;
        l_iface_hdr_rec.creation_date     := SYSDATE;
        l_iface_hdr_rec.created_by        := fnd_global.user_id;
        l_iface_hdr_rec.last_update_login := fnd_global.login_id;
     
        l_iface_rcv_rec.last_update_date  := SYSDATE;
        l_iface_rcv_rec.last_updated_by   := fnd_global.user_id;
        l_iface_rcv_rec.creation_date     := SYSDATE;
        l_iface_rcv_rec.created_by        := fnd_global.user_id;
        l_iface_rcv_rec.last_update_login := fnd_global.login_id;
     
        l_iface_hdr_rec.processing_status_code  := 'PENDING';
        l_iface_hdr_rec.receipt_source_code     := 'INTERNAL ORDER';
        l_iface_hdr_rec.transaction_type        := 'NEW';
        l_iface_hdr_rec.receipt_num             := l_shipment_number; --Automatic Number
        l_iface_hdr_rec.expected_receipt_date   := SYSDATE;
        l_iface_hdr_rec.validation_flag         := 'Y';
        l_iface_hdr_rec.ship_to_organization_id := l_req_data.destination_organization_id;
     
        INSERT INTO rcv_headers_interface
        VALUES l_iface_hdr_rec;
     
        SELECT rcv_transactions_interface_s.NEXTVAL
          INTO l_iface_rcv_rec.interface_transaction_id
          FROM dual;
        --l_requisition_line_id                   := l_req_data.requisition_line_id;
        l_iface_rcv_rec.header_interface_id     := l_iface_hdr_rec.header_interface_id;
        l_iface_rcv_rec.group_id                := l_iface_hdr_rec.group_id;
        l_iface_rcv_rec.processing_status_code  := 'PENDING';
        l_iface_rcv_rec.transaction_status_code := 'PENDING';
        l_iface_rcv_rec.processing_mode_code    := 'IMMEDIATE';
        l_iface_rcv_rec.validation_flag         := 'Y';
     
        --l_iface_rcv_rec.receipt_source_code     := 'VENDOR';
        l_iface_rcv_rec.requisition_line_id := l_req_data.requisition_line_id;
     
        l_iface_rcv_rec.source_document_code := 'REQ'; --po
     
        l_iface_rcv_rec.transaction_type      := 'RECEIVE';
        l_iface_rcv_rec.destination_type_code := 'RECEIVING';
     
        l_iface_rcv_rec.transaction_date     := SYSDATE;
        l_iface_rcv_rec.employee_id          := l_req_data.to_person_id;
        l_iface_rcv_rec.auto_transact_code   := 'DELIVER';
        l_iface_rcv_rec.receipt_source_code  := 'INTERNAL ORDER';
        l_iface_rcv_rec.from_organization_id := l_req_data.source_organization_id;
        l_iface_rcv_rec.to_organization_id   := l_req_data.destination_organization_id;
     
        l_iface_rcv_rec.requisition_line_id    := l_req_data.requisition_line_id;
        l_iface_rcv_rec.req_distribution_id    := 959926;
        l_iface_rcv_rec.shipment_num           := l_iface_hdr_rec.receipt_num;
       -- l_iface_rcv_rec.freight_carrier_code   := l_req_data.unit_meas_lookup_code; #B001
        l_iface_rcv_rec.location_id            := l_req_data.deliver_to_location_id;
        l_iface_rcv_rec.deliver_to_location_id := l_req_data.deliver_to_location_id;
        l_iface_rcv_rec.interface_source_code  := 'NOT INSPECTED';
        l_iface_rcv_rec.routing_header_id      := 3;
        l_iface_rcv_rec.routing_step_id        := 1;
     
        l_iface_rcv_rec.interface_source_code := 'NOT INSPECTED';
        l_iface_rcv_rec.destination_type_code := 'INVENTORY';
     
        l_iface_rcv_rec.subinventory               := p_subinventory_code; --實際入至子庫存
        l_iface_rcv_rec.destination_context        := 'INVENTORY';
        l_iface_rcv_rec.source_doc_quantity        := p_quantity;--實際入庫數量
        l_iface_rcv_rec.source_doc_unit_of_measure := l_req_data.unit_meas_lookup_code;
     
        l_iface_rcv_rec.item_id          := l_req_data.item_id;
        l_iface_rcv_rec.category_id      := l_req_data.category_id;
        l_iface_rcv_rec.item_description := l_req_data.item_description;
        l_iface_rcv_rec.quantity         := p_quantity;
        l_iface_rcv_rec.unit_of_measure  := l_req_data.unit_meas_lookup_code; --Not Code
     
        l_iface_rcv_rec.ship_to_location_id := l_req_data.deliver_to_location_id;
        l_iface_rcv_rec.location_id         := l_req_data.line_location_id;
     
        l_iface_rcv_rec.interface_source_code    := 'PO Requisition';
        l_iface_rcv_rec.interface_source_line_id := '123456789';
     
        INSERT INTO rcv_transactions_interface
        VALUES l_iface_rcv_rec;
      EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line('Exception in calling po_requisition_process:' || SQLERRM);
      END;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Exception in calling po_requisition_process:' || SQLERRM);
end;