茫茫網海中的冷日
         
茫茫網海中的冷日
發生過的事,不可能遺忘,只是想不起來而已!
 恭喜您是本站第 1664624 位訪客!  登入  | 註冊
主選單

Google 自訂搜尋

Goole 廣告

隨機相片
IMG_60D_00203.jpg

授權條款

使用者登入
使用者名稱:

密碼:


忘了密碼?

現在就註冊!

DB研討會 : [轉貼]EBS 接口開發

發表者 討論內容
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15766
[轉貼]EBS 接口開發
EBS 接口開發
2012年02月21日 15:01:42



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.

原文出處:EBS 接口开发 - CSDN博客
前一個主題 | 下一個主題 | | | |

討論串




Powered by XOOPS 2.0 © 2001-2008 The XOOPS Project|