QUERY TO FETCH BIAPPS LOAD PLAN CURRENT RUN STATUS AND TIME

   SELECT LOAD_PLAN_NAME,

          I_LP_INST,

          NB_RUN,

          START_DATE,

          END_DATE,

          STATUS,

          LAST_SUCCESSFUL_RUN,

          THE_CHOICE,

          THE_RANK

     FROM (  SELECT LOAD_PLAN_NAME,

                    I_LP_INST,

                    NB_RUN,

                    START_DATE,

                    END_DATE,

                    STATUS,

                    LSR                                   AS "LAST_SUCCESSFUL_RUN",

                    THE_CHOICE,

                    RANK () OVER (ORDER BY THE_CHOICE DESC) AS "THE_RANK"

               FROM (SELECT /*+ USE_NL(A,B,C,D) */

                           NVL (B.LOAD_PLAN_NAME, 'NO DATA AVAILABLE')

                               AS "LOAD_PLAN_NAME",

                            NVL (C.I_LP_INST, 0) AS "I_LP_INST",

                            NVL (C.NB_RUN, 0)  AS "NB_RUN",

                            NVL (C.START_DATE,

                                 TO_DATE ('19900101', 'YYYYMMDD'))

                               AS "START_DATE",

                            NVL (C.END_DATE, TO_DATE ('19900101', 'YYYYMMDD'))

                               AS "END_DATE",

                            NVL (C.STATUS, 'D') AS "STATUS",

                            NVL (LSR, TO_DATE ('19900101', 'YYYYMMDD'))

                               AS "LSR",

                            C.NB_RUN           AS "THE_CHOICE"

                       FROM (SELECT MAX (I_LP_INST) AS "I_LP_INST"

                               FROM SNP_LP_INST

                              WHERE LOAD_PLAN_NAME = 'Enter Load Plan Name Here') A,

                            SNP_LP_INST B,

                            SNP_LPI_RUN C,

                            (SELECT MAX (C.END_DATE) AS LSR

                               FROM SNP_LP_INST B, SNP_LPI_RUN C

                              WHERE     1 = 1

                                    AND B.I_LP_INST = C.I_LP_INST

                                    AND B.LOAD_PLAN_NAME = 'Enter Load Plan Name Here'

                                    AND C.STATUS = 'D') D

                      WHERE     1 = 1

                            AND A.I_LP_INST = B.I_LP_INST

                            AND B.I_LP_INST = C.I_LP_INST

                            AND B.LOAD_PLAN_NAME = 'Enter Load Plan Name Here'

                     UNION ALL

                     SELECT 'NO DATA AVAILABLE'            AS "LOAD_PLAN_NAME",

                            0                              AS "I_LP_INST",

                            0                              AS "NB_RUN",

                            TO_DATE ('19900101', 'YYYYMMDD') AS "START_DATE",

                            TO_DATE ('19900101', 'YYYYMMDD') AS "END_DATE",

                            'D'                            AS "STATUS",

                            TO_DATE ('19900101', 'YYYYMMDD') AS "LSR",

                            -1                             AS "THE_CHOICE"

                       FROM DUAL)

           ORDER BY 9)

    WHERE THE_RANK = 1;

BIAPPS STANDARD COLUMNS LIST

HOW TO IDENTIFY BIAPPS VERSION

Below are the steps to identify the BIAPPS version

1. Login to BIACM (http://machinename:port/biacm) using Admin user.

2. On the right hand side top corner click on the drop down link on logged in username --> Settings and Actions --> About Configuration Manager.

3. Check the BIAPPS version in the new window.

ODI METADATA QUERY TO FIND EXECUTION TIME TAKEN BY EACH LOAD STEP

The below query should be executed in ODI metadata schema(ODI_REPO)

SELECT A.LP_STEP_NAME, B.START_DATE,B.END_DATE,(B.END_DATE-B.START_DATE)*24*60 DURATION_IN_MIN
FROM SNP_LPI_STEP A , SNP_LPI_STEP_LOG B,SNP_LP_INST C
WHERE A.I_LP_STEP=B.I_LP_STEP
AND C.LOAD_PLAN_NAME='%'
AND  A.I_LP_INST =C. I_LP_INST
AND LP_STEP_NAME LIKE '%'
AND B.START_DATE > TO_DATE('10/22/2018 5:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND B.END_DATE < TO_DATE('10/22/2018 12:00:00', 'MM/DD/YYYY HH24:MI:SS');

SOURCE TABLES FOR W_SALES_CYCLE_LINE_F

W_SALES_CYCLE_LINE_F.

W_SALES_CYCLE_LINE_F is loaded from W_SALES_PICKLINE_F,W_SALES_ORDER_LINE_F and  W_SALES_SCHEDULE_LINE_F.

W_SALES_PICKLINE_F has below sources :

WSH_DELIVERY_DETAILS
WSH_NEW_DELIVERIES
WSH_DELIVERY_ASSIGNMENTS
WSH_CARRIER_SERVICES
WSH_CARRIER_VEHICLE_TYPES
WSH_CARRIERS
OE_ORDER_LINES_ALL
OE_ORDER_HEADERS_ALL

W_SALES_ORDER_LINE_F has below sources :
 
OE_ORDER_LINES_ALL
OE_ORDER_HEADERS_ALL
GL_LEDGERS
OE_AGREEMENTS_B
HR_ORGANIZATION_INFORMATION
RA_SALESREPS_ALL

W_SALES_SCHEDULE_LINE_F is loaded from W_SALES_ORDER_LINE_F

LIST OF BIAPPS MODULES

Below is the list of Modules available in BI APPS

1. Oracle Financial Analytics
2. Oracle Procurement and Spend Analytics
3. Oracle Supply Chain and Order Management Analytics
4. Oracle Project Analytics
5. Oracle Human Resources Analytics
6. Oracle Sales Analytics
7. Oracle Price Analytics
8. Oracle Marketing Analytics
9. Oracle Loyalty Analytics
10. Oracle Service Analytics
11. Oracle Call Center Telephony Analytics

BIAPPPS W_AP_XACT_F,W_AR_XACT_F,W_GL_OTHER_F LINEAGE

BIAPPS W_GL_ACCOUNT_D DATA LINEAGE

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)