對這文章發表回應

DECLARE
  l_iface_rec       po.po_headers_interface%ROWTYPE;
  l_iface_lines_rec po.po_lines_interface%ROWTYPE;
  l_iface_dis_rec   po.po_distributions_interface%ROWTYPE;
  --l_org_id          NUMBER := 81; --OU ID l_user_id NUMBER := 0; --User ID?Sysadmin
  l_batch_id NUMBER;
  l_line_num NUMBER;
  CURSOR headers IS
    SELECT DISTINCT old_po_number,
                    ou_name,
                    org_id,
                    vendor_name,
                    vendor_id,
                    vendor_site_code,
                    vendor_site_id,
                    agent_name,
                    agent_id,
                    currency_code,
                    terms_name,
                    terms_id,
                    rate,
                    rate_type

      FROM cux_po_inport;

  CURSOR lines(p_old_po_number  VARCHAR2,
               p_org_id         NUMBER,
               p_vendor_id      NUMBER,
               p_vendor_site_id NUMBER,
               p_agent_id       NUMBER,
               p_currency_code  VARCHAR2,
               p_terms_id       NUMBER) IS
    SELECT item_code,
           item_id,
           quantity,
           uom,
           unit_price,
           promised_date,
           need_by_date,
           destination_subinventory
      FROM cux_po_inport
     WHERE old_po_number = p_old_po_number
       AND org_id = p_org_id
       AND vendor_id = p_vendor_id
       AND vendor_site_id = p_vendor_site_id
       AND agent_id = p_agent_id
       AND currency_code = p_currency_code
       AND terms_id = p_terms_id;
BEGIN
  SELECT po_headers_interface_s.NEXTVAL INTO l_batch_id FROM dual;

  FOR h IN headers LOOP
    --l_iface_rec.org_id := l_org_id;
    SELECT po_headers_interface_s.NEXTVAL
      INTO l_iface_rec.interface_header_id
      FROM dual;
    l_iface_rec.process_code       := 'PENDING';
    l_iface_rec.action             := 'ORIGINAL';
    l_iface_rec.document_type_code := 'STANDARD';
    l_iface_rec.document_subtype   := NULL;
    --l_iface_rec.quote_warning_delay   := 10;
    l_iface_rec.document_num    := NULL;
    l_iface_rec.approval_status := 'APPROVED';
    l_iface_rec.agent_id        := h.agent_id;
    l_iface_rec.vendor_id       := h.vendor_id;
    l_iface_rec.vendor_site_id  := h.vendor_site_id;
    l_iface_rec.currency_code   := h.currency_code;
    l_iface_rec.terms_id        := h.terms_id;
    l_iface_rec.attribute5      := h.old_po_number;
    IF h.rate_type IS NULL THEN
      l_iface_rec.rate_date := NULL;
    ELSE
      l_iface_rec.rate_date := TO_DATE('2012-12-06', 'YYYY-MM-DD') /*TRUNC(SYSDATE)*/
       ;
    END IF;

    l_iface_rec.RATE_TYPE := h.rate_type;
    l_iface_rec.rate      := h.rate;

    l_iface_rec.interface_source_code := 'CUX_PO_INPORT';
    l_iface_rec.batch_id              := l_batch_id;

    INSERT INTO po.po_headers_interface VALUES l_iface_rec;
    l_line_num := 0;
    FOR l IN lines(h.old_po_number,
                   h.org_id,
                   h.vendor_id,
                   h.vendor_site_id,
                   h.agent_id,
                   h.currency_code,
                   h.terms_id) LOOP
      l_line_num                            := l_line_num + 1;
      l_iface_lines_rec.interface_header_id := l_iface_rec.interface_header_id;
      l_iface_lines_rec.process_code        := 'PENDING';
      l_iface_lines_rec.action              := 'ORIGINAL';
      l_iface_lines_rec.line_num            := l_line_num;
      l_iface_lines_rec.item_id             := l.item_id;
      --l_iface_lines_rec.promised_date       := SYSDATE;
      --Shipment 1
      --l_iface_lines_rec.shipment_num  := 1;
      l_iface_lines_rec.quantity      := l.quantity;
      l_iface_lines_rec.unit_price    := l.unit_price;
      l_iface_lines_rec.promised_date := l.promised_date; --承諾日期
      l_iface_lines_rec.need_by_date  := l.need_by_date; --需求日期

      SELECT po_lines_interface_s.NEXTVAL
        INTO l_iface_lines_rec.interface_line_id
        FROM dual;
      INSERT INTO po_lines_interface VALUES l_iface_lines_rec;
      l_iface_dis_rec.interface_header_id := l_iface_lines_rec.interface_header_id;
      l_iface_dis_rec.distribution_num    := 1;
      --l_iface_dis_rec.quantity_delivered  := 0;
      --l_iface_dis_rec.quantity_billed     := 0;
      --l_iface_dis_rec.quantity_cancelled  := 0; --Must Be 0
      --l_iface_dis_rec.charge_account_id   := NULL;
      l_iface_dis_rec.interface_line_id        := l_iface_lines_rec.interface_line_id;
      l_iface_dis_rec.quantity_ordered         := l_iface_lines_rec.quantity;
      l_iface_dis_rec.destination_subinventory := l.destination_subinventory; --子庫
      SELECT po.po_distributions_interface_s.NEXTVAL
        INTO l_iface_dis_rec.interface_distribution_id
        FROM dual;
      INSERT INTO po.po_distributions_interface VALUES l_iface_dis_rec;
    END LOOP;
  END LOOP;
  /* --Shipment 2
  l_iface_lines_rec.shipment_num := 2;
  l_iface_lines_rec.quantity     := 300;
  --l_iface_lines_rec.unit_price   := 1;
  SELECT po_lines_interface_s.NEXTVAL
    INTO l_iface_lines_rec.interface_line_id
    FROM dual;
  INSERT INTO po_lines_interface VALUES l_iface_lines_rec;

  l_iface_dis_rec.interface_line_id        := l_iface_lines_rec.interface_line_id;
  l_iface_dis_rec.quantity_ordered         := l_iface_lines_rec.quantity;
  l_iface_dis_rec.destination_subinventory := 'W009'; --子庫
  SELECT po.po_distributions_interface_s.NEXTVAL
    INTO l_iface_dis_rec.interface_distribution_id
    FROM dual;
  INSERT INTO po.po_distributions_interface VALUES l_iface_dis_rec;*/
  --Should initialize before po_docs_interface_sv5.process_po_headers_interface
  apps.fnd_global.apps_initialize(user_id      => 0,
                                  resp_id      => 50819,
                                  resp_appl_id => 201);
  mo_global.set_policy_context('S', 84 /*fnd_profile.VALUE('ORG_ID')*/);
  po_docs_interface_sv5.process_po_headers_interface(x_selected_batch_id          => l_iface_rec.batch_id,
                                                     x_buyer_id                   => NULL,
                                                     x_document_type              => l_iface_rec.document_type_code,
                                                     x_document_subtype           => l_iface_rec.document_subtype,
                                                     x_create_items               => 'N',
                                                     x_create_sourcing_rules_flag => NULL,
                                                     x_rel_gen_method             => NULL,
                                                     x_approved_status            => l_iface_rec.approval_status,
                                                     x_commit_interval            => 1,
                                                     x_process_code               => 'PENDING',
                                                     x_interface_header_id        => NULL,
                                                     x_org_id_param               => NULL,
                                                     x_ga_flag                    => NULL);
END;