EBS 接口开发

http://blog.csdn.net/whhitgen/article/details/7279625

1 根据数据源创建临时表

2 将数据源导入临时表

3将临时表的数据导入标准接口表

4 创建采购员

5 提交Import Standard Purchase Orders请求

 

1 根据数据源创建临时表

     /* Formatted on 2012/02/20 10:24 (Formatter Plus v4.8.7) */

CREATE TABLE po_headers_interface_beke(

      org_id NUMBER,

      process_code VARCHAR2(25),

      action VARCHAR2(25),

      document_type_code VARCHAR2(25),

      document_subtype VARCHAR2(25),

      document_num VARCHAR2(20),

      approval_status VARCHAR2(25),

      agent_id NUMBER,

      vendor_id NUMBER,

      vendor_site_id NUMBER,

      interface_source_code VARCHAR2(25),

      batch_id NUMBER,

      interface_header_id NUMBER NOT NULL

)

 

/* Formatted on 2012/02/20 10:38 (Formatter Plus v4.8.7) */

CREATE TABLE po_lines_interface_beke(

       process_code VARCHAR2(25),

       action VARCHAR2(25),

       line_num NUMBER,

       item_id NUMBER,

       unit_price NUMBER,

       interface_header_id NUMBER NOT NULL

)

2 将数据源导入临时表，可以用sql loader，也可以在PL/SQL Developer中复制粘贴，

    由于数据不多，在这里用复制粘贴的方式

/* Formatted on 2012/02/21 11:31 (Formatter Plus v4.8.7) */

SELECT *

  FROM po_headers_interface_beke for update

 

/* Formatted on 2012/02/21 11:32 (Formatter Plus v4.8.7) */

SELECT *

  FROM po_lines_interface_beke for update

 

3 创建存储过程，将临时表的数据导入标准接口表，在此创建了一个包，包两个存储过程

3.1 package

                /* Formatted on 2012/02/21 13:34 (Formatter Plus v4.8.7) */

CREATE OR REPLACE PACKAGE po_interface_beke_pkg

IS

   PROCEDURE po_interface_add_bpa_beke (

      p_user_id        IN   NUMBER := 0,

      p_resp_id        IN   NUMBER := 52054,

      p_resp_appl_id   IN   NUMBER := 201

   );

 

   PROCEDURE po_interface_add_bpa_beke2 (

      p_user_id        IN   NUMBER := 0,

      p_resp_id        IN   NUMBER := 52054,

      p_resp_appl_id   IN   NUMBER := 201

   );

END po_interface_beke_pkg;

 

3.2 package body

                /* Formatted on 2012/02/21 13:37 (Formatter Plus v4.8.7) */

CREATE OR REPLACE PACKAGE BODY po_interface_beke_pkg

IS

   PROCEDURE po_interface_add_bpa_beke (

      p_user_id        IN   NUMBER := 0,

      p_resp_id        IN   NUMBER := 52054,

      p_resp_appl_id   IN   NUMBER := 201

   )

   IS

      l_iface_rec         po_headers_interface%ROWTYPE;

      l_iface_lines_rec   po_lines_interface%ROWTYPE;

   BEGIN

      SELECT po_headers_interface_s.NEXTVAL

        INTO l_iface_rec.interface_header_id

        FROM DUAL;

 

      SELECT phib.org_id, phib.process_code,

             phib.action, phib.document_type_code,

             phib.document_subtype, phib.document_num,

             phib.approval_status, phib.agent_id,

             phib.vendor_id, phib.vendor_site_id,

             phib.interface_source_code, phib.batch_id

        INTO l_iface_rec.org_id, l_iface_rec.process_code,

             l_iface_rec.action, l_iface_rec.document_type_code,

             l_iface_rec.document_subtype, l_iface_rec.document_num,

             l_iface_rec.approval_status, l_iface_rec.agent_id,

             l_iface_rec.vendor_id, l_iface_rec.vendor_site_id,

             l_iface_rec.interface_source_code, l_iface_rec.batch_id

        FROM po_headers_interface_beke phib;

 

      INSERT INTO po_headers_interface

           VALUES l_iface_rec;

 

      SELECT po_lines_interface_s.NEXTVAL

        INTO l_iface_lines_rec.interface_line_id

        FROM DUAL;

 

      l_iface_lines_rec.interface_header_id := l_iface_rec.interface_header_id;

 

      SELECT plib.process_code, plib.action,

             plib.line_num, plib.item_id,

             plib.unit_price

        INTO l_iface_lines_rec.process_code, l_iface_lines_rec.action,

             l_iface_lines_rec.line_num, l_iface_lines_rec.item_id,

             l_iface_lines_rec.unit_price

        FROM po_lines_interface_beke plib;

 

      INSERT INTO po_lines_interface

           VALUES l_iface_lines_rec;

 

      --Should initialize before po_docs_interface_sv5.process_po_header_interface

      fnd_global.apps_initialize (user_id           => p_user_id,

                                  resp_id           => p_resp_id,

                                  resp_appl_id      => p_resp_appl_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                    => l_iface_lines_rec.process_code,

                           x_interface_header_id             => NULL,

                           x_org_id_param                    => NULL,

                           x_ga_flag                         => NULL

                          );

   END po_interface_add_bpa_beke;

 

 

   PROCEDURE po_interface_add_bpa_beke2 (

      p_user_id        IN   NUMBER := 0,

      p_resp_id        IN   NUMBER := 52054,

      p_resp_appl_id   IN   NUMBER := 201

   )

   IS

      l_iface_rec         po_headers_interface%ROWTYPE;

      l_iface_lines_rec   po_lines_interface%ROWTYPE;

 

      CURSOR cur_headers

      IS

         SELECT phib.org_id, phib.process_code, phib.action,

                phib.document_type_code, phib.document_subtype,

                phib.document_num, phib.approval_status, phib.agent_id,

                phib.vendor_id, phib.vendor_site_id,

                phib.interface_source_code, phib.batch_id,

                phib.interface_header_id

           FROM po_headers_interface_beke phib;

 

      CURSOR cur_lines

      IS

         SELECT plib.process_code, plib.action, plib.line_num, plib.item_id,

                plib.unit_price, plib.interface_header_id

           FROM po_lines_interface_beke plib;

   BEGIN

      FOR rec_headers IN cur_headers

      LOOP

         SELECT po_headers_interface_s.NEXTVAL

           INTO l_iface_rec.interface_header_id

           FROM DUAL;

 

         l_iface_rec.org_id := rec_headers.org_id;

         l_iface_rec.process_code := rec_headers.process_code;

         l_iface_rec.action := rec_headers.action;

         l_iface_rec.document_type_code := rec_headers.document_type_code;

         l_iface_rec.document_subtype := rec_headers.document_subtype;

         l_iface_rec.document_num := rec_headers.document_num;

         l_iface_rec.approval_status := rec_headers.approval_status;

         l_iface_rec.agent_id := rec_headers.agent_id;

         l_iface_rec.vendor_id := rec_headers.vendor_id;

         l_iface_rec.vendor_site_id := rec_headers.vendor_site_id;

         l_iface_rec.interface_source_code :=

                                             rec_headers.interface_source_code;

         l_iface_rec.batch_id := rec_headers.batch_id;

 

         INSERT INTO po_headers_interface

              VALUES l_iface_rec;

 

         FOR rec_lines IN cur_lines

         LOOP

            IF rec_lines.interface_header_id =

                                              rec_headers.interface_header_id

            THEN

               SELECT po_lines_interface_s.NEXTVAL

                 INTO l_iface_lines_rec.interface_line_id

                 FROM DUAL;

 

               l_iface_lines_rec.interface_header_id :=

                                               l_iface_rec.interface_header_id;

               l_iface_lines_rec.process_code := rec_lines.process_code;

               l_iface_lines_rec.action := rec_lines.action;

               l_iface_lines_rec.line_num := rec_lines.line_num;

               l_iface_lines_rec.item_id := rec_lines.item_id;

               l_iface_lines_rec.unit_price := rec_lines.unit_price;

 

               INSERT INTO po_lines_interface

                    VALUES l_iface_lines_rec;

 

               --Should initialize before po_docs_interface_sv5.process_po_header_interface

               fnd_global.apps_initialize (user_id           => p_user_id,

                                           resp_id           => p_resp_id,

                                           resp_appl_id      => p_resp_appl_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                    => l_iface_lines_rec.process_code,

                           x_interface_header_id             => NULL,

                           x_org_id_param                    => NULL,

                           x_ga_flag                         => NULL

                          );

            END IF;

         END LOOP;

      END LOOP;

   END po_interface_add_bpa_beke2;

END po_interface_beke_pkg;

 

4 创建采购员

4.1 在Human Resources模块里创建employee

4.2 在System Adminstrator里面，把刚创建的employee添加到Person项里

4.3 在Purchasing Super User里，在Setup->Personnel->Buyers表单里把刚才创建的employee设置为Buyer采购员

5 提交Import Standard Purchase Orders请求，根据Batch Id可以看出是刚才导入的数据

That’s all.