APPS.OPI_DBI_INV_TURNS_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 129

    'select min (transaction_date)
        from (
        select min(TRANSACTION_DATE) transaction_date
          from OPI_INV_ITD_ORG_MV
        union all
        select min (trunc (cogs_date)) transaction_date
          from opi_dbi_cogs_f
          where cogs_date is not null
            and turns_cogs_flag = 1)';

Line: 151

    INSERT INTO OPI_DBI_INV_TURNS_STG (
        ORGANIZATION_ID,
        TRANSACTION_DATE,
        INV_BALANCE_G,
        INV_BALANCE_B,
        INV_BALANCE_SG
    )
    SELECT
        ORGANIZATION_ID,
        TRANSACTION_DATE,
        INV_BALANCE_G,
        INV_BALANCE_B,
        INV_BALANCE_SG
      FROM
        (
        (
        select  keys.ORGANIZATION_ID ORGANIZATION_ID,
                keys.start_date TRANSACTION_DATE,
                (select INV_BALANCE_G
                  from OPI_INV_ITD_ORG_MV
                  where
                      ORGANIZATION_ID = keys.ORGANIZATION_ID
                  and TRANSACTION_DATE =
                            (select max(TRANSACTION_DATE) max_date
                               from OPI_INV_ITD_ORG_MV
                               where
                                   TRANSACTION_DATE <= keys.start_date
                               and ORGANIZATION_ID = keys.ORGANIZATION_ID)
                  and rownum < 2
                ) INV_BALANCE_G,
                (select INV_BALANCE_B
                  from OPI_INV_ITD_ORG_MV
                  where
                      ORGANIZATION_ID = keys.ORGANIZATION_ID
                  and TRANSACTION_DATE =
                            (select max(TRANSACTION_DATE) max_date
                               from OPI_INV_ITD_ORG_MV
                               where
                                   TRANSACTION_DATE <= keys.start_date
                               and ORGANIZATION_ID = keys.ORGANIZATION_ID)
                  and rownum < 2
                ) INV_BALANCE_B,
                (select INV_BALANCE_SG
                  from OPI_INV_ITD_ORG_MV
                  where
                      ORGANIZATION_ID = keys.ORGANIZATION_ID
                  and TRANSACTION_DATE =
                            (select max(TRANSACTION_DATE) max_date
                               from OPI_INV_ITD_ORG_MV
                               where
                                   TRANSACTION_DATE <= keys.start_date
                               and ORGANIZATION_ID = keys.ORGANIZATION_ID)
                  and rownum < 2
                ) INV_BALANCE_SG
          from
            (SELECT
                    ot.organization_id organization_id,
                    sd.start_date start_date
              FROM
                (
                select ORGANIZATION_ID
                  from OPI_INV_ITD_ORG_MV
                  group by ORGANIZATION_ID
                ) ot,
                (
                select start_date
                  from
                    (
                    select distinct WEEK_START_DATE start_date
                      from FII_TIME_DAY_ALL_V
                      where REPORT_DATE between l_min_trx_date and
                                                l_max_trx_date
                    union
                    select distinct ENT_PERIOD_START_DATE start_date
                      from FII_TIME_DAY_ALL_V
                      where REPORT_DATE between l_min_trx_date and
                                                l_max_trx_date
                    union
                    select distinct ENT_QTR_START_DATE start_date
                      from FII_TIME_DAY_ALL_V
                      where REPORT_DATE between l_min_trx_date and
                                                l_max_trx_date
                    union
                    select distinct ENT_YEAR_START_DATE start_date
                      from FII_TIME_DAY_ALL_V
                      where REPORT_DATE between l_min_trx_date and
                                                l_max_trx_date
                    )
                 where start_date between l_min_trx_date and l_max_trx_date
                ) sd
             UNION
             SELECT organization_id,
                    start_date
               FROM
                (
                 SELECT DISTINCT
                        organization_id organization_id,
                        trunc (cogs_date) start_date
                   FROM opi_dbi_cogs_f
                   WHERE cogs_date is not null
                     AND turns_cogs_flag = 1
                 MINUS
                 SELECT DISTINCT
                        organization_id organization_id,
                        transaction_date start_date
                   FROM opi_inv_itd_org_mv) cogs_keys
            ) keys
        )
        union
        select
            ORGANIZATION_ID,
            TRANSACTION_DATE,
            INV_BALANCE_G,
            INV_BALANCE_B,
            INV_BALANCE_SG
          from OPI_INV_ITD_ORG_MV
        );

Line: 297

    INSERT /*+ append */
    INTO opi_dbi_inv_turns_f
    (
        organization_id,
        transaction_date,
        start_date_wtd,
        start_date_mtd,
        start_date_qtd,
        start_date_ytd,
        weight,
        inv_balance_g,
        inv_balance_b,
        inv_balance_sg,
        cogs_val_g,
        cogs_val_b,
        cogs_val_sg,
        source,
        created_by,
        last_update_login,
        creation_date,
        last_updated_by,
        last_update_date
    )
    SELECT
        turns.organization_id,
        turns.transaction_date,
        cal.week_start_date start_date_wtd,
        cal.ent_period_start_date start_date_mtd,
        cal.ent_qtr_start_date start_date_qtd,
        cal.ent_year_start_date start_date_ytd,
        nvl (lead (turns.transaction_date, 1) over
             (partition by turns.organization_id
              order by turns.transaction_date) - turns.transaction_date,
             1) weight,
        turns.inv_balance_g inv_balance_g,
        turns.inv_balance_b inv_balance_b,
        turns.inv_balance_sg inv_balance_sg,
        nvl (cogs.cogs_val_g, 0) cogs_val_g,
        nvl (cogs.cogs_val_b, 0) cogs_val_b,
        nvl (cogs.cogs_val_sg, 0) cogs_val_sg,
        1, -- actually this does not matter here.
        g_user_id,
        g_login_id,
        sysdate,
        g_user_id,
        sysdate
      FROM
        opi_dbi_inv_turns_stg turns,
        fii_time_day_all_v cal,
        (SELECT /*+ no_merge */
                organization_id,
                trunc (cogs_date) cogs_date,
                nvl (sum (cogs_val_b), 0) cogs_val_b,
                nvl (sum (cogs_val_g), 0) cogs_val_g,
                nvl (sum (cogs_val_sg), 0) cogs_val_sg
           FROM opi_dbi_cogs_f
           WHERE cogs_date is not null
             AND turns_cogs_flag = 1
           GROUP BY
                organization_id,
                trunc (cogs_date)) cogs
        WHERE turns.transaction_date = cal.report_date
          AND turns.transaction_date = cogs.cogs_date(+)
          AND turns.organization_id = cogs.organization_id(+);

Line: 364

    BIS_COLLECTION_UTILITIES.PUT_LINE ('Inserted ' || l_row_count ||
                                       ' rows into the turns table successfully.');