APPS.INV_INVTRHAN_XMLP_PKG SQL Statements

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

Line: 48

      IF ((C_COST_TYPE = 1) AND (P_SORT_ID = 1) AND (P_SELECTION = 2) AND (ASS_INV <> 1)) THEN
        RETURN (0);

Line: 54

      IF (P_WMS_ENABLED = 'Y' OR P_PJM_ENABLED = 'Y') AND P_SELECTION in (2,3) THEN
        RETURN CUR_QTY_VAL_OLD;

Line: 57

      IF C_COST_TYPE = 1 OR P_SELECTION = 1 THEN
        RETURN (CUR_QTY_VAL - SOURCE_TYPE1 - SOURCE_TYPE2 - SOURCE_TYPE3 - SOURCE_TYPE4 - SOURCE_TYPE5 - OTHER);

Line: 66

      SELECT
        NVL(MIN(TRANSACTION_ID)
           ,0)
      INTO MY_MIN_TRX_ID
      FROM
        MTL_MATERIAL_TRANSACTIONS
      WHERE ORGANIZATION_ID = MY_ORG_ID
        AND INVENTORY_ITEM_ID = MY_ITEM_ID
        AND ( SUBINVENTORY_CODE in (
        SELECT
          SECONDARY_INVENTORY_NAME
        FROM
          MTL_SECONDARY_INVENTORIES
        WHERE ORGANIZATION_ID = MY_ORG_ID
          AND ASSET_INVENTORY <> 2 )
      OR SUBINVENTORY_CODE is null )
        AND TRANSACTION_DATE >= TO_DATE(MY_HIS_DATE
             ,'DD-MON-RRRR') + 1
        AND TRANSACTION_ACTION_ID <> 30;

Line: 88

        SELECT
          PRIOR_COST
        INTO MY_HIS_VALUE
        FROM
          MTL_MATERIAL_TRANSACTIONS
        WHERE ORGANIZATION_ID = MY_ORG_ID
          AND INVENTORY_ITEM_ID = MY_ITEM_ID
          AND TRANSACTION_ID = MY_MIN_TRX_ID;

Line: 120

      SELECT
        SUBSTR(TRANSACTION_SOURCE_TYPE_NAME
              ,0
              ,14)
      INTO NAME
      FROM
        MTL_TXN_SOURCE_TYPES
      WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;

Line: 144

      SELECT
        SUBSTR(TRANSACTION_SOURCE_TYPE_NAME
              ,0
              ,14)
      INTO NAME
      FROM
        MTL_TXN_SOURCE_TYPES
      WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;

Line: 168

      SELECT
        SUBSTR(TRANSACTION_SOURCE_TYPE_NAME
              ,0
              ,13)
      INTO NAME
      FROM
        MTL_TXN_SOURCE_TYPES
      WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;

Line: 192

      SELECT
        SUBSTR(TRANSACTION_SOURCE_TYPE_NAME
              ,0
              ,13)
      INTO NAME
      FROM
        MTL_TXN_SOURCE_TYPES
      WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;

Line: 216

      SELECT
        SUBSTR(TRANSACTION_SOURCE_TYPE_NAME
              ,0
              ,11)
      INTO NAME
      FROM
        MTL_TXN_SOURCE_TYPES
      WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;

Line: 261

        SELECT
          CATEGORY_SET_NAME
        INTO CAT_SET_NAME
        FROM
          MTL_CATEGORY_SETS
        WHERE CATEGORY_SET_ID = CAT_SET_ID;

Line: 274

                   ,'Error:No category set selected')*/NULL;

Line: 369

      SELECTION VARCHAR2(20);

Line: 391

      SELECTION := TO_CHAR(P_SELECTION);

Line: 408

      IF P_SELECTION = 1 THEN
        EXECUTE IMMEDIATE
          'create view ' || VIEW_NAME || ' as
          select  moqd.subinventory_code       subinv,
                 moqd.inventory_item_id        item_id,
                 0                             item_cost,
                 0                            source_type1,
                 0                            source_type2,
                 0                            source_type3,
                 0                            source_type4,
                 0                            source_type5,
                 0                            other,
                 sum(primary_transaction_quantity)    cur_qty_val,
                 sum(primary_transaction_quantity)      cur_qty,
                 sum(primary_transaction_quantity)      target_qty
          from mtl_onhand_quantities_detail moqd
          where moqd.organization_id = ' || VAR_ORG || '
          and moqd.owning_tp_type = DECODE(' || CONSIGNED || ', 2, 2, moqd.owning_tp_type)
          group by moqd.subinventory_code, moqd.inventory_item_id
          UNION
          select     mmt.subinventory_code        subinv,
                     mmt.inventory_item_id        item_id,
                     0                            item_cost,
                     sum(decode(mtst.transaction_source_type_id,' || STYPE1 || ',primary_quantity))          source_type1,
                     sum(decode(mtst.transaction_source_type_id,' || STYPE2 || ',primary_quantity))          source_type2,
                     sum(decode(mtst.transaction_source_type_id,' || STYPE3 || ',primary_quantity))          source_type3,
                     sum(decode(mtst.transaction_source_type_id,' || STYPE4 || ',primary_quantity))          source_type4,
                     0      source_type5,
                     sum(decode(mtst.transaction_source_type_id,' || STYPE1 || ',0,' || STYPE2 || ',0,' || STYPE3 || ',0,' || STYPE4 || ',0,primary_quantity ))        other,
                     0      cur_qty_val,
                     0      cur_qty,
                     -sum(primary_quantity)      target_qty
          from mtl_material_transactions mmt,
          mtl_txn_source_types      mtst,
          mtl_parameters            mp
          where mmt.organization_id = ' || VAR_ORG || '
          and   mp.organization_id = ' || VAR_ORG || '
          /*and   transaction_date >= to_date(''' || HIST_DATE || ''' , ''DD-MON-RRRR'' ) + 1   --GSCC change hist_date + 1 */
          and   transaction_date >= ''' || L_HIST_DATE || '''  --GSCC change hist_date + 1
          and   NVL(mmt.owning_tp_type, 2) = DECODE(' || CONSIGNED || ', 2, 2, NVL(mmt.owning_tp_type, 2))
          and   mmt.transaction_source_type_id = mtst.transaction_source_type_id
          and   nvl(mmt.logical_transaction,2) <> 1   --added for bug 5501066
          group by mmt.subinventory_code,mmt.inventory_item_id,mp.primary_cost_method
          ';

Line: 458

            select moqv.subinventory_code        subinv,
                   moqv.inventory_item_id        item_id,
                   round(moqv.item_cost,15)              item_cost,
                   0                            source_type1,
                   0                            source_type2,
                   0                            source_type3,
                   0                            source_type4,
                   0                            source_type5,
                   0                            other,
                   decode(' || SELECTION || ',1,sum(transaction_quantity),sum(transaction_quantity * NVL(moqv.item_cost,0)))                           cur_qty_val,
                   sum(transaction_quantity)                  cur_qty,
                   sum(transaction_quantity)                  target_qty
            from mtl_onhand_qty_cost_v moqv
            where moqv.organization_id = ' || VAR_ORG || '
            group by moqv.subinventory_code, moqv.inventory_item_id, moqv.item_cost
            UNION
            select mmt.subinventory_code        subinv,
                   mmt.inventory_item_id        item_id,
                   round(cst.item_cost,15)              item_cost,
                   sum(decode(mtst.transaction_source_type_id,' || STYPE1 || ',
                         decode(' || SELECTION || ',1,primary_quantity,
                           decode(mp.primary_cost_method,2,primary_quantity,
                             decode(' || STYPE1 || ',11,quantity_adjusted * (new_cost-prior_cost),13,
                               decode(mmt.transaction_action_id,24,quantity_adjusted * (new_cost-prior_cost),primary_quantity* actual_cost),
                                    primary_quantity * actual_cost
                               )
                             )
                           )
                       ,0)
                       )    source_type1,
                   sum(decode(mtst.transaction_source_type_id,' || STYPE2 || ',
                         decode(' || SELECTION || ',1,primary_quantity,
                           decode(mp.primary_cost_method,2,primary_quantity,
                             decode(' || STYPE2 || ',11,quantity_adjusted * (new_cost-prior_cost),13,
                               decode(mmt.transaction_action_id,24,quantity_adjusted * (new_cost-prior_cost),primary_quantity* actual_cost),
                                    primary_quantity * actual_cost
                               )
                             )
                           )
                       ,0)
                       )    source_type2,
                   sum(decode(mtst.transaction_source_type_id,' || STYPE3 || ',
                         decode(' || SELECTION || ',1,primary_quantity,
                           decode(mp.primary_cost_method,2,primary_quantity,
                             decode(' || STYPE3 || ',11,quantity_adjusted * (new_cost-prior_cost),13,
                               decode(mmt.transaction_action_id,24,quantity_adjusted * (new_cost-prior_cost),primary_quantity* actual_cost),
                                      primary_quantity * actual_cost
                               )
                             )
                           )
                       ,0)
                       )    source_type3,
                   sum(decode(mtst.transaction_source_type_id,' || STYPE4 || ',
                         decode(' || SELECTION || ',1,primary_quantity,
                           decode(mp.primary_cost_method,2,primary_quantity,
                             decode(mp.primary_cost_method,2,primary_quantity,
                               decode(' || STYPE4 || ',11,quantity_adjusted * (new_cost-prior_cost),13,
                                 decode(mmt.transaction_action_id,24,quantity_adjusted * (new_cost-prior_cost),primary_quantity* actual_cost),
                                    primary_quantity * actual_cost
                                 )
                               )
                             )
                           )
                       ,0)
                       )    source_type4,
                  0   source_type5,
                  sum(decode(mtst.transaction_source_type_id,' || STYPE1 || ',0,' || STYPE2 || ',0,' || STYPE3 || ',0,' || STYPE4 || ',0,
                        decode(' || SELECTION || ',1,primary_quantity,
                          decode(mp.primary_cost_method,2,primary_quantity,
                            decode(mtst.transaction_source_type_id,11,quantity_adjusted*(new_cost-prior_cost),13,
                              decode(mmt.transaction_action_id,24,quantity_adjusted * (new_cost-prior_cost),primary_quantity* actual_cost),
                                   primary_quantity * actual_cost
                              )
                            )
                          )
                        )
                      )       other,
                   0                                                   cur_qty_val,
                   0                                                   cur_qty,
                   -sum(primary_quantity)                          target_qty
            from mtl_material_transactions mmt,
                 mtl_txn_source_types      mtst,
                 mtl_parameters            mp,
                 cst_item_costs_for_gl_view cst
            where mmt.organization_id = ' || VAR_ORG || '
            and   mp.organization_id = ' || VAR_ORG || '
            and   cst.organization_id = ' || VAR_ORG || '
            and   cst.inventory_item_id = mmt.inventory_item_id
            and   transaction_date >= ''' || L_HIST_DATE || ''' -- GSCC Change hist_date + 1
            and   NVL(mmt.owning_tp_type, 2) = 2
            and   mmt.transaction_source_type_id = mtst.transaction_source_type_id
            and   nvl(mmt.logical_transaction,2) <> 1   --added for bug 5501066
            group by mmt.subinventory_code,mmt.inventory_item_id,cst.item_cost, mp.primary_cost_method
            ';

Line: 555

            IF P_SELECTION = 3 THEN
              /*SRW.MESSAGE(1
                         ,'Clearing the source type defaults')*/NULL;

Line: 567

                          select
                            to_char(NULL)     subinv,
                            to_number(NULL)   item_id,
                            0                 item_cost,
                            0                 source_type1,
                            0                 source_type2,
                            0                 source_type3,
                            0                 source_type4,
                            0                 source_type5,
                            0                 other,
                            0                 cur_qty_val,
                            0                 cur_qty,
                            0                 target_qty
              from DUAL
              WHERE 1=2';

Line: 680

        SELECT
          VIEW_NAME
        FROM
          USER_VIEWS
        WHERE VIEW_NAME LIKE 'TXN_ANALYSIS_VIEW%';

Line: 722

      IF ((P_SORT_ID = 1) AND (P_SELECTION = 2) AND (ASS_INV <> 1)) THEN
        RETURN (0);

Line: 724

      ELSIF (P_WMS_ENABLED = 'Y' OR P_PJM_ENABLED = 'Y') AND P_SELECTION = 2 THEN
        RETURN (CUR_QTY_VAL_OLD + SOURCE_TYPE1 + SOURCE_TYPE2 + SOURCE_TYPE3 + SOURCE_TYPE4 + OTHER);

Line: 726

      ELSIF (P_WMS_ENABLED = 'Y' OR P_PJM_ENABLED = 'Y') AND P_SELECTION = 3 THEN
        RETURN (CUR_QTY_VAL_OLD + OTHER);

Line: 741

      SELECT
        PRIMARY_COST_METHOD
      INTO COST_TYPE
      FROM
        MTL_PARAMETERS
      WHERE ORGANIZATION_ID = ORG_ID;

Line: 786

      IF (P_SELECTION = 1) THEN
        RETURN (OTHER);

Line: 793

        SELECT
          ITEM_COST
        INTO CURRENT_ITEM_COST
        FROM
          CST_ITEM_COSTS_FOR_GL_VIEW
        WHERE ORGANIZATION_ID = P_ORG_ID
          AND INVENTORY_ITEM_ID = ITEM_ID;

Line: 821

      SELECT
        NVL(MIN(TRANSACTION_ID)
           ,0)
      INTO MY_MIN_TRX_ID
      FROM
        MTL_MATERIAL_TRANSACTIONS
      WHERE ORGANIZATION_ID = MY_ORG_ID
        AND INVENTORY_ITEM_ID = MY_ITEM_ID
        AND TRANSACTION_DATE > TO_DATE(MY_HIS_DATE
             ,'DD-MON-RRRR');

Line: 834

        SELECT
          NVL(PRIOR_COST
             ,0)
        INTO MY_HIS_VALUE
        FROM
          MTL_MATERIAL_TRANSACTIONS
        WHERE ORGANIZATION_ID = MY_ORG_ID
          AND INVENTORY_ITEM_ID = MY_ITEM_ID
          AND TRANSACTION_ID = MY_MIN_TRX_ID;

Line: 882

      IF (P_SELECTION = 1) THEN
        RETURN (SOURCE_TYPE1);

Line: 909

      SELECT
        ITEM_COST
      INTO CURRENT_ITEM_COST
      FROM
        CST_ITEM_COSTS_FOR_GL_VIEW
      WHERE ORGANIZATION_ID = P_ORG_ID
        AND INVENTORY_ITEM_ID = ITEM_ID;

Line: 921

      SELECT
        NVL(MIN(TRANSACTION_ID)
           ,0)
      INTO MY_MIN_TRX_ID
      FROM
        MTL_MATERIAL_TRANSACTIONS
      WHERE ORGANIZATION_ID = MY_ORG_ID
        AND INVENTORY_ITEM_ID = MY_ITEM_ID
        AND TRANSACTION_DATE > TO_DATE(MY_HIS_DATE
             ,'DD-MON-RRRR');

Line: 934

        SELECT
          PRIOR_COST
        INTO MY_HIS_VALUE
        FROM
          MTL_MATERIAL_TRANSACTIONS
        WHERE ORGANIZATION_ID = MY_ORG_ID
          AND INVENTORY_ITEM_ID = MY_ITEM_ID
          AND TRANSACTION_ID = MY_MIN_TRX_ID;

Line: 980

      IF (P_SELECTION = 1) THEN
        RETURN (SOURCE_TYPE2);

Line: 1007

      SELECT
        ITEM_COST
      INTO CURRENT_ITEM_COST
      FROM
        CST_ITEM_COSTS_FOR_GL_VIEW
      WHERE ORGANIZATION_ID = P_ORG_ID
        AND INVENTORY_ITEM_ID = ITEM_ID;

Line: 1019

      SELECT
        NVL(MIN(TRANSACTION_ID)
           ,0)
      INTO MY_MIN_TRX_ID
      FROM
        MTL_MATERIAL_TRANSACTIONS
      WHERE ORGANIZATION_ID = MY_ORG_ID
        AND INVENTORY_ITEM_ID = MY_ITEM_ID
        AND TRANSACTION_DATE > TO_DATE(MY_HIS_DATE
             ,'DD-MON-RRRR');

Line: 1032

        SELECT
          PRIOR_COST
        INTO MY_HIS_VALUE
        FROM
          MTL_MATERIAL_TRANSACTIONS
        WHERE ORGANIZATION_ID = MY_ORG_ID
          AND INVENTORY_ITEM_ID = MY_ITEM_ID
          AND TRANSACTION_ID = MY_MIN_TRX_ID;

Line: 1078

      IF (P_SELECTION = 1) THEN
        RETURN (SOURCE_TYPE3);

Line: 1105

      SELECT
        ITEM_COST
      INTO CURRENT_ITEM_COST
      FROM
        CST_ITEM_COSTS_FOR_GL_VIEW
      WHERE ORGANIZATION_ID = P_ORG_ID
        AND INVENTORY_ITEM_ID = ITEM_ID;

Line: 1117

      SELECT
        NVL(MIN(TRANSACTION_ID)
           ,0)
      INTO MY_MIN_TRX_ID
      FROM
        MTL_MATERIAL_TRANSACTIONS
      WHERE ORGANIZATION_ID = MY_ORG_ID
        AND INVENTORY_ITEM_ID = MY_ITEM_ID
        AND TRANSACTION_DATE > TO_DATE(MY_HIS_DATE
             ,'DD-MON-RRRR');

Line: 1130

        SELECT
          PRIOR_COST
        INTO MY_HIS_VALUE
        FROM
          MTL_MATERIAL_TRANSACTIONS
        WHERE ORGANIZATION_ID = MY_ORG_ID
          AND INVENTORY_ITEM_ID = MY_ITEM_ID
          AND TRANSACTION_ID = MY_MIN_TRX_ID;

Line: 1176

      IF (P_SELECTION = 1) THEN
        RETURN (SOURCE_TYPE4);

Line: 1203

      SELECT
        ITEM_COST
      INTO CURRENT_ITEM_COST
      FROM
        CST_ITEM_COSTS_FOR_GL_VIEW
      WHERE ORGANIZATION_ID = P_ORG_ID
        AND INVENTORY_ITEM_ID = ITEM_ID;

Line: 1215

      SELECT
        NVL(MIN(TRANSACTION_ID)
           ,0)
      INTO MY_MIN_TRX_ID
      FROM
        MTL_MATERIAL_TRANSACTIONS
      WHERE ORGANIZATION_ID = MY_ORG_ID
        AND INVENTORY_ITEM_ID = MY_ITEM_ID
        AND TRANSACTION_DATE > TO_DATE(MY_HIS_DATE
             ,'DD-MON-RRRR');

Line: 1228

        SELECT
          PRIOR_COST
        INTO MY_HIS_VALUE
        FROM
          MTL_MATERIAL_TRANSACTIONS
        WHERE ORGANIZATION_ID = MY_ORG_ID
          AND INVENTORY_ITEM_ID = MY_ITEM_ID
          AND TRANSACTION_ID = MY_MIN_TRX_ID;