新建采购订单-SQL

l_iface_rec.org_id             := l_org_id;

        l_iface_rec.process_code       := 'PENDING';

        l_iface_rec.action             := 'ORIGINAL';

        l_iface_rec.document_type_code := 'STANDARD';

        --  l_iface_rec.document_subtype      := 'CATALOG';

        -- l_iface_rec.quote_warning_delay   := 10;

        l_iface_rec.attribute1         := 'XP';

        l_iface_rec.document_num          := cux_po_tax_pkg.get_po_number(l_org_id,l_iface_rec.attribute1);

        l_iface_rec.approval_status       := 'APPROVED';

        l_iface_rec.approved_date         := SYSDATE;

        l_iface_rec.agent_id              := l_agent_id;

        begin

          select ap.vendor_id,apsa.vendor_site_id

            into l_iface_rec.vendor_id,l_iface_rec.vendor_site_id

            from ap_suppliers ap,ap_supplier_sites_all apsa,hr_operating_units hou

           where hou.name = ap.vendor_name

             and ap.vendor_id = apsa.vendor_id

             and apsa.org_id = l_org_id

             and sysdate < nvl(apsa.inactive_date,sysdate + 1)

             and hou.organization_id = rec_header.h_org_id

             and rownum = 1;

        exception

          when others then

             null;

        end;

      --  l_iface_rec.vendor_id             := rec_header.vendor_id;

     --   l_iface_rec.vendor_site_id        := rec_header.vendor_site_id;

        l_iface_rec.interface_source_code := 'IMP';

        l_iface_rec.currency_code         := rec_header.h_transactional_curr_code;

        if rec_header.h_transactional_curr_code <> 'USD' THEN

          l_iface_rec.rate_type_code        := L_rate_type_code;

          --l_iface_rec.Payment_Terms         :='25%预付,73%月结30天,2%尾款';--

          l_iface_rec.rate_date     := rec_header.h_ordered_date;

        END IF;

        l_iface_rec.payment_terms := l_pay_term;

        --l_iface_rec.rate                  := rec_header.rate;

        --l_iface_rec.batch_id           := l_batch_id;

        l_iface_rec.attribute_category := l_org_id;

        --l_iface_rec.attribute1         := 'XP';

        l_iface_rec.attribute8         := rec_header.h_cust_po_number;--关联单号

       /* l_iface_rec.attribute2         := rec_header.h_attribute2;

        l_iface_rec.attribute3         := rec_header.h_attribute3;

        l_iface_rec.attribute4         := rec_header.h_attribute4;

        l_iface_rec.attribute5         := rec_header.h_attribute5;

        l_iface_rec.attribute6         := rec_header.h_attribute6;

        l_iface_rec.attribute7         := rec_header.h_attribute7;

        l_iface_rec.attribute9         := rec_header.h_attribute9;

        l_iface_rec.attribute10        := rec_header.h_attribute10;

        l_iface_rec.attribute11        := rec_header.h_attribute11;

        l_iface_rec.attribute12        := rec_header.h_attribute12;

        l_iface_rec.attribute13        := rec_header.h_attribute3;

        l_iface_rec.attribute14        := rec_header.h_attribute14;

        l_iface_rec.attribute15        := rec_header.h_attribute15;*/

        INSERT INTO po.po_headers_interface

        VALUES l_iface_rec;

l_iface_lines_rec                               := NULL;

          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                      := to_number(rec_line.l_orig_sys_line_ref);

          l_iface_lines_rec.item_id                       := rec_line.l_inventory_item_id;

          l_iface_lines_rec.quantity                      := rec_line.l_ordered_quantity;

          l_iface_lines_rec.unit_price                    := rec_line.l_unit_selling_price;--round(rec_line.unit_price / (1 + l_rate / 100),6);-----含税转不含税

          l_iface_lines_rec.need_by_date                  := rec_line.l_schedule_ship_date;

          l_iface_lines_rec.line_attribute_category_lines := l_org_id;

          l_iface_lines_rec.line_attribute8              := rec_header.h_cust_po_number;

         /* l_iface_lines_rec.line_attribute1               := rec_line.l_attribute1;

          l_iface_lines_rec.line_attribute2               := rec_line.l_attribute2;

          l_iface_lines_rec.line_attribute3               := rec_line.l_attribute3;

          l_iface_lines_rec.line_attribute4               := rec_line.l_attribute4;

          l_iface_lines_rec.line_attribute5               := rec_line.l_attribute5;

          l_iface_lines_rec.line_attribute6               := rec_line.l_attribute6;

          l_iface_lines_rec.line_attribute7               := rec_line.l_attribute7;

          l_iface_lines_rec.line_attribute8               := rec_line.l_attribute8;

          l_iface_lines_rec.line_attribute9               := rec_line.l_attribute9;

          l_iface_lines_rec.line_attribute10              := rec_line.l_attribute10;

          l_iface_lines_rec.line_attribute11              := rec_line.l_attribute11;

          l_iface_lines_rec.line_attribute12              := rec_line.l_attribute12;

          l_iface_lines_rec.line_attribute13              := rec_line.l_attribute13;

          l_iface_lines_rec.line_attribute14              := rec_line.l_attribute14;

          l_iface_lines_rec.line_attribute15              := rec_line.l_attribute15;*/

          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.QUANTITY_ORDERED := 10;

          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;

          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;

        mo_global.set_policy_context('S',

                                     l_iface_rec.org_id);

        po_docs_interface_sv5.process_po_headers_interface(x_selected_batch_id          => NULL, --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            => NULL, -- l_iface_rec.approval_status,

                                                           x_commit_interval            => 1,

                                                           x_process_code               => 'PENDING',

                                                           x_interface_header_id        => l_iface_rec.interface_header_id,

                                                           x_org_id_param               => NULL,

                                                           x_ga_flag                    => NULL);