BIAPPS STANDARD COLUMNS AND DATATYPES

Please find the BIAPPS columns and their standard datatypes in below table.

COLUMN	DATATYPE
_WID	NUMBER(10)
_.AMT/_QTY	NUMBER(28,10)
_ID	VARCHAR2(80 CHAR)
CODE	VARCHAR2(30 CHAR)
_FLAG	CHAR(1)
INTEGRATION_ID	VARCHAR2(80 CHAR) -- Dimension VARCHAR2(400 CHAR) -- Fact
DATASOURCE_NUM_ID	NUMBER(10)
ETL_PROC_WID	NUMBER(10)
X_CUSTOM	VARCHAR2(10 CHAR)
TENANT_ID	VARCHAR2(80 CHAR)

ODI ERRORS AND SOLUTIONS

1.  The error ODI - 1226/1240 occurs if we miss to select sub select option in  blue interface against the yellow interface.

                                                                                                                                                              

2.  The error ODI - 1228 occurs if there is a data issue with the source like if the target table is having lesser precison than the source query or there is a data issue with source like numeric column storing character values etc.
 Note: This is mostly source side issue, so check the source query and data carefully.

   3. The error ODImay error if precision is missing for columns in target table.

                  

STRING CONCATENATION USING XMLAGG AND LISTAGG FUNCTIONS

 STRING CONCATENATION USING XMLAGG AND LISTAGG FUNCTIONS

XMLAGG function will return data in CLOB DATATYPE.

LISTAGG function will return data in VARCHAR2(4000 CHAR).

------------------XMLAGG QUERY---------------------------

SELECT FM_FORM_MST_B.FORMULA_ID    FORMULA_ID,
       RTRIM(XMLAGG(XMLELEMENT(E,COLNAME,',').EXTRACT('//TEXT()') ORDER BY COLNAME).GETCLOBVAL(),',') X_REASON_FOR_REVISION
FROM    APPS.FM_TEXT_TBL   FM_TEXT_TBL, APPS.FM_FORM_MST_B   FM_FORM_MST_B
WHERE   (1=1)
      AND (FM_TEXT_TBL.TEXT_CODE (+)=FM_FORM_MST_B.TEXT_CODE)
      AND (FM_TEXT_TBL.PARAGRAPH_CODE (+) = 'RSRV')
      AND FM_TEXT_TBL.LINE_NO <> -1
GROUP BY FM_FORM_MST_B.FORMULA_ID;

----------------QUERY TO TRIM STRING 3900 CHARACTERS-----------------------

  SELECT FM_FORM_MST_B.FORMULA_ID FORMULA_ID,
         DBMS_LOB.SUBSTR( RTRIM(XMLAGG(XMLELEMENT(E,FM_TEXT_TBL.TEXT,',').EXTRACT('//TEXT()') ORDER BY FM_TEXT_TBL.LINE_NO).GETCLOBVAL(),',')  , 3900,1) X_REASON_FOR_REVISION
--If you set the limit to 4000 characters the query might fail as sometimes it might extend beyond 4000 characters so setting it to 3900 characters.
    FROM APPS.FM_TEXT_TBL FM_TEXT_TBL, APPS.FM_FORM_MST_B FM_FORM_MST_B
    WHERE     (1 = 1)
         AND (FM_TEXT_TBL.TEXT_CODE(+) = FM_FORM_MST_B.TEXT_CODE)
         AND (FM_TEXT_TBL.PARAGRAPH_CODE(+) = 'RSRV')
         AND FM_TEXT_TBL.LINE_NO <> -1
GROUP BY FM_FORM_MST_B.FORMULA_ID;

------------------LISTAGG QUERY-----------------------

SELECT 
    FM_FORM_MST_B.FORMULA_ID    FORMULA_ID,
    LISTAGG (FM_TEXT_TBL.TEXT, '') WITHIN GROUP (ORDER BY FM_TEXT_TBL.LINE_NO)    X_REASON_FOR_REVISION
FROM    APPS.FM_TEXT_TBL   FM_TEXT_TBL, APPS.FM_FORM_MST_B   FM_FORM_MST_B
WHERE    (1=1)
 AND (FM_TEXT_TBL.TEXT_CODE (+)=FM_FORM_MST_B.TEXT_CODE)
AND (FM_TEXT_TBL.PARAGRAPH_CODE (+) = 'RSRV')
AND FM_TEXT_TBL.LINE_NO <> -1
GROUP BY FM_FORM_MST_B.FORMULA_ID;

-------------------------------------------------------

EBS QUERY TO GET OPM FORMULA - REASON FOR REVISION

  SELECT fm_form_mst_b.formula_id formula_id,
         LISTAGG (fm_text_tbl.text, '')
             WITHIN GROUP (ORDER BY fm_text_tbl.line_no)
             reason_for_revision
    FROM apps.fm_text_tbl fm_text_tbl, apps.fm_form_mst_b fm_form_mst_b
   WHERE     (1 = 1)
         AND (fm_text_tbl.text_code(+) = fm_form_mst_b.text_code)
         AND (fm_text_tbl.paragraph_code(+) = 'RSRV')
         AND fm_text_tbl.line_no <> -1
GROUP BY fm_form_mst_b.formula_id;