EBS 12.1.3 WEBADI

最新推荐文章于 2024-06-09 18:40:04 发布

小丑伍陸柒

最新推荐文章于 2024-06-09 18:40:04 发布

阅读量436

点赞数

分类专栏： EBS 文章标签： webadi

本文链接：https://blog.csdn.net/weixin_39338752/article/details/98211157

版权

EBS 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

EBS 12.1.3 WEBADI

表

create table TEST_WEBADI_T
(
  supplier_number    VARCHAR2(10) not null,
  supplier_name      VARCHAR2(200),
  supplier_site_name VARCHAR2(100) not null,
  invoice_number     VARCHAR2(100) not null,
  invoice_date       DATE not null,
  invoice_curr_code  VARCHAR2(10),
  invoice_header_des VARCHAR2(100),
  invoice_amount_h   NUMBER(15) not null,
  invoice_amount_l   NUMBER(15) not null,
  gl_date            DATE not null,
  segment1           VARCHAR2(4) not null,
  segment2           VARCHAR2(4) not null,
  segment3           VARCHAR2(10) not null,
  line_num           NUMBER,
  invoice_line_des   VARCHAR2(100),
  invoice_batch_name VARCHAR2(100) not null,
  run_id             NUMBER not null,
  result             VARCHAR2(5),
  message            VARCHAR2(50)
)

创建序列,用于groupid

CREATE SEQUENCE TEST_IMPORTER_S;

包

CREATE OR REPLACE PACKAGE test_webadi_pkg IS

  -- Author  : Adolph
  -- Created : 2019/7/25 10:18:13
  -- Purpose : 

  PROCEDURE upload(p_supplier_number    IN VARCHAR2,
                   p_supplier_name      IN VARCHAR2,
                   p_supplier_site_name IN VARCHAR2,
                   p_invoice_number     IN VARCHAR2,
                   p_invoice_date       IN DATE,
                   p_invoice_curr_code  IN VARCHAR2,
                   p_invoice_header_des IN VARCHAR2,
                   p_invoice_amount_h   IN NUMBER,
                   p_invoice_amount_l   IN NUMBER,
                   p_gl_date            IN DATE,
                   p_segment1           IN VARCHAR2,
                   p_segment2           IN VARCHAR2,
                   p_segment3           IN VARCHAR2,
                   p_line_num           IN NUMBER,
                   p_invoice_line_des   IN VARCHAR2,
                   p_invoice_batch_name IN VARCHAR2,
                   p_run_id             IN NUMBER,
                   p_result         IN VARCHAR2,
                   p_message      IN VARCHAR2);

  PROCEDURE importer(p_run_id IN NUMBER);

END test_webadi_pkg;

CREATE OR REPLACE PACKAGE BODY test_webadi_pkg IS

  PROCEDURE upload(p_supplier_number    IN VARCHAR2,
                   p_supplier_name      IN VARCHAR2,
                   p_supplier_site_name IN VARCHAR2,
                   p_invoice_number     IN VARCHAR2,
                   p_invoice_date       IN DATE,
                   p_invoice_curr_code  IN VARCHAR2,
                   p_invoice_header_des IN VARCHAR2,
                   p_invoice_amount_h   IN NUMBER,
                   p_invoice_amount_l   IN NUMBER,
                   p_gl_date            IN DATE,
                   p_segment1           IN VARCHAR2,
                   p_segment2           IN VARCHAR2,
                   p_segment3           IN VARCHAR2,
                   p_line_num           IN NUMBER,
                   p_invoice_line_des   IN VARCHAR2,
                   p_invoice_batch_name IN VARCHAR2,
                   p_run_id             IN NUMBER,
                   p_result         IN VARCHAR2,
                   p_message      IN VARCHAR2) IS
  BEGIN
    INSERT INTO cux.test_webadi_t
      (supplier_number
      ,supplier_name
      ,supplier_site_name
      ,invoice_number
      ,invoice_date
      ,invoice_curr_code
      ,invoice_header_des
      ,invoice_amount_h
      ,invoice_amount_l
      ,gl_date
      ,segment1
      ,segment2
      ,segment3
      ,line_num
      ,invoice_line_des
      ,invoice_batch_name
      ,run_id
      ,RESULT
      ,message)
    VALUES
      (p_supplier_number
      ,p_supplier_name
      ,p_supplier_site_name
      ,p_invoice_number
      ,p_invoice_date
      ,p_invoice_curr_code
      ,p_invoice_header_des
      ,p_invoice_amount_h
      ,p_invoice_amount_l
      ,p_gl_date
      ,p_segment1
      ,p_segment2
      ,p_segment3
      ,p_line_num
      ,p_invoice_line_des
      ,p_invoice_batch_name
      ,p_run_id
      ,p_result
      ,p_message);
  END upload;

  PROCEDURE importer(p_run_id IN NUMBER) IS
    CURSOR cur_recs_interface IS
      SELECT run_id
            ,invoice_number
            ,line_num
            ,invoice_amount_l
            ,RESULT
            ,message
        FROM test_webadi_t
       WHERE run_id = p_run_id
         FOR UPDATE OF RESULT, message;
    rec cur_recs_interface%ROWTYPE;
  BEGIN
    FOR rec IN cur_recs_interface
    LOOP
      IF rec.invoice_amount_l > 50 AND rec.invoice_amount_l <=100 THEN
        UPDATE test_webadi_t
           SET RESULT    = 'E1'
              ,message = 'Line Num ' || rec.line_num || ' is between 50 and 100.'
         WHERE CURRENT OF cur_recs_interface;
         ELSIF rec.invoice_amount_l > 100 AND rec.invoice_amount_l <=150 THEN
           UPDATE test_webadi_t
           SET RESULT    = 'E2'
              ,message = 'Line Num ' || rec.line_num || ' is between 101 and 150.'
         WHERE CURRENT OF cur_recs_interface;
         ELSIF rec.invoice_amount_l > 150 THEN
           UPDATE test_webadi_t
           SET RESULT    = 'E3'
              ,message = 'Line Num ' || rec.line_num || ' is greater than 150.'
         WHERE CURRENT OF cur_recs_interface;
      ELSE
        UPDATE test_webadi_t
           SET RESULT    = 'SUCC'
              ,message = 'Viewer Success for ' || rec.line_num
         WHERE CURRENT OF cur_recs_interface;
      END IF;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      fnd_message.set_name('AP', 'Error');
      fnd_message.raise_error;
  END importer;
END test_webadi_pkg;

创建错误的lookup code

Application Developer -> Application -> Lookups -> Application Object Library

WEBADI_TEST_ERROR

注册表

EXEC ad_dd.register_table ('SQLAP', 'TEST_WEBADI_T', 'T',8,10,90);
BEGIN
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T','SUPPLIER_NUMBER',5,'VARCHAR2',10,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T','SUPPLIER_NAME',10,'VARCHAR2',200,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'SUPPLIER_SITE_NAME',15,'VARCHAR2',100,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'INVOICE_NUMBER',20,'VARCHAR2',100,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'INVOICE_DATE',25,'DATE',7,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'INVOICE_CURR_CODE',30,'VARCHAR2',10,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'INVOICE_HEADER_DES',35,'VARCHAR2',100,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'INVOICE_AMOUNT_H',40,'NUMBER',15,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'INVOICE_AMOUNT_L',45,'NUMBER',15,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'GL_DATE',50,'DATE',7,'Y', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'SEGMENT1',55,'VARCHAR2',4,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'SEGMENT2',60,'VARCHAR2',4,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T','SEGMENT3',65,'VARCHAR2',10,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T','LINE_NUM',70,'NUMBER',15,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'INVOICE_LINE_DES',75,'VARCHAR2',100,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'INVOICE_BATCH_NAME',80,'VARCHAR2',100,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'RUN_ID',85,'NUMBER',15,'N', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'RESULT',90,'VARCHAR2',5,'Y', 'N' );
ad_dd.register_column ('SQLAP', 'TEST_WEBADI_T' ,'MESSAGE',95,'VARCHAR2',50,'Y', 'N' );
End;
/

创建Integrator

Integrator Name : TEST INVOICE UPLOAD
Internal Name : TEST_INVOICE_UPLOAD
Application : Payables

创建Interfaces

Interface Name : TEST_AP_INVOICE_UPLOAD_ADI_INT
Type : Procedure
Package Name : TEST_WEBADI_PKG
Procedure Name : UPLOAD
API Returns : FND Message Code
Interfaces
P_MESSAGE : NULL
P_RESULT : NEW
P_RUN_ID : select TEST_IMPORTER_S.nextval from dual

创建Contents

这里先不定义
Contents

创建Uploader

Uploader

创建Importer

Importer Type : PL/SQL API
Importer Name : AP_INVOICE_UPLOAD_IMP
在这里插入图片描述

Importer Rules

Group Definition : 已这个参数分组,处理这组数据
Document Row : Interface Attribute Mapping : 主键,根据此区分每行数据
PL/SQL API call : 处理的程序

注意:
1. 如果uploader是基于表的,Value = TableName.ColumnName
2. 如果uploader是基于过程的, Value = ProcedureName.Parameter
Error Row Definition :

SQL Query

SELECT * FROM TEST_WEBADI_T
WHERE RESULT like 'E%' AND RUN_ID = $PARAM$.RUN_ID_DEV_SEQ

Error Row Definition

Error Message Lookup(这里是lookup code,基于表中message向下看Importer: Error Messsage Lookup 2)

SQL Query

SELECT MEANING FROM FND_LOOKUPS WHERE LOOKUP_TYPE = 'WEBADI_TEST_ERROR' AND LOOKUP_CODE = $PARAM$.RESULT

Source : Interface Table Value : TEST_WEBADI_T.RESULT
Error Message Lookup

创建Layout

找到刚刚创建的Integrator
Select Integrator
Layout Name : TEST_INVOICE_UPLOAD_LOY
在这里插入图片描述

在这里插入图片描述

创建Function

Type:       SSWA servlet function
Parameter:  bne:page=BneCreateDoc&bne:viewer=BNE:EXCEL%25&bne:reporting=N&bne:integrator=<integer name>_XINTG&bne:layout=<integer LOY name>
HTML Call:  BneApplicationService

SELECT intg.application_id
      ,intg.integrator_code
      ,intg.user_name
      ,intg.date_format
  FROM bne_integrators_vl intg
 WHERE intg.user_name = '&integrator_name';

创建Menu

测试

测试结果1

Importer: Error Messsage Lookup 2

SELECT message FROM TEST_WEBADI_T
WHERE RESULT = $PARAM$.RESULT AND RUN_ID = $PARAM$.RUN_ID_DEV_SEQ

Importer: Error Messsage Lookup

测试结果2

参考文档:

How to Define an Importer Returning Error Messages to the Oracle Web Applications Desktop Integrator Document
WebADI_数据验证2_建立基于Date的LOV验证（案例）
WebADI_数据验证3_建立基于Table的LOV验证（案例）
R12 WEBADI 基于TABLE的LOV详细研究

小丑伍陸柒

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
EBS 12.1.3 WEBADI

EBS 12.1.3 WEBADI表create table TEST_WEBADI_T( supplier_number VARCHAR2(10) not null, supplier_name VARCHAR2(200), supplier_site_name VARCHAR2(100) not null, invoice_number VARCH...
复制链接

扫一扫