WebADI_WebADI常用代码bne_integrator

WebADI_WebADI常用代码bne_integrator_utils

20150906 Created By BaoXinjian

1. 简介

2. 注册Date

/* Formatted on 2015/9/3 19:44:25 (QP5 v5.163.1008.3004) */
BEGIN
   BNE_INTEGRATOR_UTILS.CREATE_CALENDAR_LOV (
      P_APPLICATION_ID       => 200,                  -- Your custom application
      P_INTERFACE_CODE       => 'BXJADI_XINTG_INTF1', --Your custom interface code
      P_INTERFACE_COL_NAME   => 'P_EMP_HIREDATE', --Your date item in interface
      P_WINDOW_CAPTION       => 'Select Data',                 --Window Prompt
      P_WINDOW_WIDTH         => NULL,                            --Use default
      P_WINDOW_HEIGHT        => NULL,                            --Use default
      P_TABLE_COLUMNS        => 'P_EMP_HIREDATE',       --Your table date fields
      P_USER_ID              => 1318);
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Run Successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('SQLCODE=' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE ('SQLERRM=' || SQLERRM);
END;

3. 注册Table Lov

/* Formatted on 2015/9/3 20:06:17 (QP5 v5.163.1008.3004) */
BEGIN
   BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV (
      P_APPLICATION_ID       => 200,
      P_INTERFACE_CODE       => 'BXJADI_XINTG_INTF1',
      P_INTERFACE_COL_NAME   => 'P_EMP_TYPE',
      P_ID_COL               => 'PERSON_TYPE_ID',
      P_MEAN_COL             => 'EMPLOYEE_TYPE',
      P_DESC_COL             => NULL,
      P_TABLE                => 'BXJ_EMPLOYEE_TYPES',
      P_ADDL_W_C             => 'SOURCE_LANG = ''US''',
      P_WINDOW_CAPTION       => 'Yes/No with Meaning, selecting Meaning, Meaning sortable',
      P_WINDOW_WIDTH         => 400,
      P_WINDOW_HEIGHT        => 300,
      P_TABLE_BLOCK_SIZE     => 10,
      P_TABLE_SORT_ORDER     => 'ascending',
      P_USER_ID              => 1318);
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Run Successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('SQLCODE=' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE ('SQLERRM=' || SQLERRM);
END;

4. 注册Key Flexfield

/* Formatted on 2015/9/3 21:51:42 (QP5 v5.163.1008.3004) */
BEGIN
   BNE_INTEGRATOR_UTILS.CREATE_CCID_KFF (
      P_APPLICATION_ID        => 200,             --Your custom application id
      P_INTERFACE_CODE        => 'BXJADI_XINTG_INTF1', --Your custom interface code
      P_INTERFACE_COL_NAME    => 'P_EMP_ACCOUNT_ID', -- Your CCID item in interface
      P_NUM_FLEX_SEGS         => 5, --GL code combination segments count, 最大30,实际显示的为启用的段
      P_GROUP_NAME            => 'Employee Account Group', --Attribute Group name, 随便写
      P_FLEX_APPLICATION_ID   => 101,                      --GL application id
      P_FLEX_CODE             => 'GL#',                    --Account flex code
      P_FLEX_NUM              => 101, --Charts of account id , 这里可用参数，比如“SOB.ChartOfAccountsID "
      p_vrule                 => NULL,
      p_effective_date_col    => 'P_ACCOUNT_EFFECTIVE_DATE', --如interface中有effective date 字段的话，会提供CCID的effective date 到此字段（还没测）
      p_prompt_above          => 'EmpCCID Above',
      p_prompt_left           => 'EmpCCID Left',
      p_user_hint             => 'EmpCCID Hint',
      p_user_id               => 1318);
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Run Successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('SQLCODE=' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE ('SQLERRM=' || SQLERRM);
END;

/* Formatted on 2015/9/3 21:20:41 (QP5 v5.163.1008.3004) */
BEGIN
   BNE_INTEGRATOR_UTILS.CREATE_CCID_KFF (
      P_APPLICATION_ID        => 200,
      P_INTERFACE_CODE        => 'BXJADI_XINTG_INTF1',
      P_INTERFACE_COL_NAME    => 'P_EMP_ACCOUNT_ID',
      P_NUM_FLEX_SEGS         => 5,
      P_GROUP_NAME            => 'Emp Account Group',
      P_FLEX_APPLICATION_ID   => 101,
      P_FLEX_CODE             => 'GL#',
      P_FLEX_NUM              => '101',
      P_VRULE                 => NULL,
      P_EFFECTIVE_DATE_COL    => 'P_ACCOUNT_EFFECTIVE_DATE',
      P_PROMPT_ABOVE          => 'EmpCCID Above',
      P_PROMPT_LEFT           => 'EmpCCID Left',
      P_USER_HINT             => 'EmpCCID Hint',
      P_USER_ID               => 1318);
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Run Successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('SQLCODE=' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE ('SQLERRM=' || SQLERRM);
END;

5. 注册Description Flexfield

/* Formatted on 2015/9/3 21:20:53 (QP5 v5.163.1008.3004) */
BEGIN
   BNE_INTEGRATOR_UTILS.CREATE_DFF (
      P_APPLICATION_ID             => 200,
      P_INTERFACE_CODE             => 'BXJADI_XINTG_INTF1',
      P_FLEX_SEG_COL_NAME_PREFIX   => 'P_EMP_ADDRESS',
      P_CONTEXT_COL_NAME           => 'P_ADDRESS_CONTEXT',
      P_GROUP_NAME                 => 'Employee Address Group',
      P_REQUIRED_FLAG              => 'N',
      P_FLEX_APPLICATION_ID        => 101,
      P_FLEX_CODE                  => 'HR_LOCATIONS',
      P_VRULE                      => NULL,
      P_EFFECTIVE_DATE_COL         => 'P_ADDRESS_EFFECTIVE_DATE',
      P_PROMPT_ABOVE               => 'EmpAddr Above',
      P_PROMPT_LEFT                => 'EmpAddr Left',
      P_USER_HINT                  => 'EmpAddr Hint',
      P_USER_ID                    => 1318);
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Run Successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('SQLCODE=' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE ('SQLERRM=' || SQLERRM);
END;

/* Formatted on 2015/9/3 20:19:11 (QP5 v5.163.1008.3004) */
DECLARE
   P_INTEGRATOR   NUMBER := 0;
   P_INTERFACE    NUMBER := 0;
BEGIN
   P_INTEGRATOR :=
      BNE_INTEGRATOR_UTILS.DELETE_INTEGRATOR (
         P_APPLICATION_ID    => 200,
         P_INTEGRATOR_CODE   => 'BXJADI_XINTG');

   P_INTERFACE :=
      BNE_INTEGRATOR_UTILS.DELETE_INTERFACE (
         P_APPLICATION_ID   => 200,
         P_INTERFACE_CODE   => 'BXJADI_XINTG_INTF1');
   DBMS_OUTPUT.PUT_LINE ('Run Successfully' || P_INTEGRATOR || P_INTERFACE);
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('SQLCODE=' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE ('SQLERRM=' || SQLERRM);
END;

SELECT * FROM DBA_OBJECTS
WHERE OBJECT_TYPE IN ('TABLE','VIEW')
AND OBJECT_NAME LIKE '%KEY%FLEX%'

SELECT * FROM CE_GL_ACCOUNTS_CCID

SELECT * FROM BXJ_COUNTRY

alter session set nls_language='AMERICAN';

select * from FND_ID_FLEX_STRUCTURES_VL
where id_flex_code = 'GL#'

select * from FND_DESCR_FLEX_CONTEXTS_VL
where descriptive_flexfield_name like 'HR%LOCATION%'

select * from bne_interfaces_b
where interface_name LIKE '%BXJ%'

select * from bne_interface_cols_b
WHERE INTERFACE_CODE LIKE '%BXJ%'

SELECT * FROM BXJ_EMPLOYEE_TYPES

SELECT * FROM BXJ_WEBADI_DEPT

SELECT * FROM FND_APPLICATION
WHERE application_short_name ='SQLAP'

SELECT PERSON_TYPE_ID,EMPLOYEE_TYPE FROM BXJ_EMPLOYEE_TYPES WHERE SOURCE_LANG = 'US'

SELECT * FROM FND_DESCR_FLEX_CONTEXTS_TL

BXJ_WEBADI_EMPLOYEE_PKG

Thanks and Regards