eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CST_MGD_MSTR_BOOK_RPT SQL Statements

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

Line: 185


PROCEDURE Insert_Rpt_Data (
  p_rpt_item_rec    IN  Report_Rec_Type
)
IS
BEGIN
  INSERT INTO
    CST_MGD_MSTR_BOOK_TEMP(
      TRANSACTION_ID
    , ORGANIZATION_ID
    , INVENTORY_ITEM_ID
    , UOM_CODE   -- added for inv book
    , ITEM_CODE
    , ITEM_DESC
    , ORG_NAME
    , CURRENCY_CODE
    , TXN_SOURCE -- added for inv book
    , TXN_DATE
    , TXN_TYPE
    , TXN_INI_QTY
    , TXN_INI_UNIT_COST
    , TXN_INI_H_TOTAL_COST
    , TXN_INI_ADJ_TOTAL_COST
    , TXN_QTY
    , TXN_UNIT_COST
    , TXN_H_TOTAL_COST
    , TXN_ADJ_TOTAL_COST
    , TXN_FNL_QTY
    , TXN_FNL_UNIT_COST
    , TXN_FNL_H_TOTAL_COST
    , TXN_FNL_ADJ_TOTAL_COST
    , CREATION_DATE
    )
  VALUES(
      p_rpt_item_rec.transaction_id
    , p_rpt_item_rec.organization_id
    , p_rpt_item_rec.inventory_item_id
    , p_rpt_item_rec.uom_code           -- added for inv book
    , p_rpt_item_rec.item_code          -- added for inv book
    , p_rpt_item_rec.item_desc          -- added for inv book
    , p_rpt_item_rec.org_name           -- added for inv book
    , p_rpt_item_rec.currency_code      -- added for inv book
    , p_rpt_item_rec.txn_source         -- added for inv book
    , p_rpt_item_rec.txn_date
    , p_rpt_item_rec.txn_type
    , p_rpt_item_rec.txn_ini_qty
    , p_rpt_item_rec.txn_ini_unit_cost
    , p_rpt_item_rec.txn_ini_h_total_cost
    , p_rpt_item_rec.txn_ini_adj_total_cost
    , p_rpt_item_rec.txn_qty
    , p_rpt_item_rec.txn_unit_cost
    , p_rpt_item_rec.txn_h_total_cost
    , p_rpt_item_rec.txn_adj_total_cost
    , p_rpt_item_rec.txn_fnl_qty
    , p_rpt_item_rec.txn_fnl_unit_cost
    , p_rpt_item_rec.txn_fnl_h_total_cost
    , p_rpt_item_rec.txn_fnl_adj_total_cost
    , p_rpt_item_rec.CREATION_DATE
    );

Line: 250


                             , 'Insert_Rpt_Data'
                             );

Line: 255


END Insert_Rpt_Data;

Line: 284


SELECT
  f.acct_period_id
FROM
  org_acct_periods f
WHERE f.organization_id      = p_org_id
  AND f.period_start_date   <= c_rpt_from_date
  AND f.schedule_close_date >= c_rpt_from_date;

Line: 295


SELECT
  t.acct_period_id
FROM
  org_acct_periods t
WHERE t.organization_id      = p_org_id
  AND t.period_start_date   <= c_rpt_to_date
  AND t.schedule_close_date >= c_rpt_to_date;

Line: 385


SELECT
  f.acct_period_id
FROM
  org_acct_periods f
WHERE f.organization_id      = p_org_id
  AND f.period_start_date   <= c_rpt_from_date
  AND f.schedule_close_date >= c_rpt_from_date
  AND F.Open_Flag           = 'N'
  AND F.Period_Close_Date IS NOT NULL;

Line: 398


SELECT
  t.acct_period_id
FROM
  org_acct_periods t
WHERE t.organization_id      = p_org_id
  AND t.period_start_date   <= c_rpt_to_date
  AND t.schedule_close_date >= c_rpt_to_date
  AND T.Open_Flag           = 'N'
  AND T.Period_Close_Date IS NOT NULL;

Line: 485


  SELECT
    Begin_Qty
  , NVL((Actual_Inflation_Adj - Issue_Inflation_Adj), 0)
  , NVL((Actual_Qty - Issue_Qty), 0)
  INTO
    x_init_qty
  , l_final_infl_adj
  , l_final_qty
  FROM
    CST_MGD_INFL_ADJUSTED_COSTS
  WHERE Organization_ID   = p_org_id
    AND Acct_Period_ID    = p_acct_period_id
    AND Inventory_Item_ID = p_item_id;

Line: 534


  SELECT
    Transaction_Type_Name
  INTO
    x_txn_type_name
  FROM
    MTL_TRANSACTION_TYPES
  WHERE Transaction_Type_ID = p_txn_type_id;

Line: 615


  SELECT
    mmt.Transaction_ID  Transaction_ID
  , mmt.Transaction_Type_ID  Transaction_Type_ID
  , mmt.Transaction_Source_Type_Id  Transaction_Source_Type_Id
  , mmt.Transaction_action_id Transaction_action_id
  , mmt.Transaction_Date Transaction_Date
  , mmt.Primary_Quantity Primary_Quantity
  , mmt.Actual_Cost Actual_Cost
  , mmt.Prior_Cost Prior_Cost
  , mmt.New_Cost New_Cost
  , mmt.value_change value_change
  , mmt.percentage_change percentage_change
  , mmt.Transfer_Organization_ID Transfer_Organization_ID
  , mmt.Creation_date Creation_date
  , mmt.quantity_adjusted
  FROM
    MTL_MATERIAL_TRANSACTIONS mmt
  WHERE mmt.Organization_ID   = p_org_id
    AND mmt.Inventory_Item_ID = p_item_id
    AND NVL(mmt.Acct_Period_ID,0) = NVL(p_acct_period_id,NVL(mmt.ACCT_PERIOD_ID,0))
    AND NVL(mmt.Subinventory_code,'0') >= NVL(p_subinv_from,NVL(mmt.Subinventory_code,'0'))
    AND NVL(mmt.Subinventory_code,'0') <= NVL(p_subinv_to,NVL(mmt.Subinventory_code,'0'))
    AND mmt.Transaction_Date  BETWEEN l_per_first_txn_date
                                  AND l_per_last_txn_date
    AND ( mmt.SUBINVENTORY_CODE is NULL
          OR
          mmt.SUBINVENTORY_CODE =
          ( SELECT SECONDARY_INVENTORY_NAME
            FROM   MTL_SECONDARY_INVENTORIES
            WHERE  SECONDARY_INVENTORY_NAME = mmt.SUBINVENTORY_CODE
            AND    ORGANIZATION_ID = mmt.ORGANIZATION_ID
            AND    QUANTITY_TRACKED = 1
            AND    ASSET_INVENTORY = 1)
        )
    AND mmt.transaction_id NOT IN
        (SELECT mmt1.transaction_id
         FROM
           MTL_MATERIAL_TRANSACTIONS mmt1
         WHERE mmt1.organization_id   = p_org_id
           AND mmt1.inventory_item_id = p_item_id
           AND NVL(mmt1.Acct_Period_ID,0) = NVL(p_acct_period_id,NVL(mmt1.ACCT_PERIOD_ID,0))
           AND NVL(mmt1.Subinventory_code,'0') >= NVL(p_subinv_from,NVL(mmt1.Subinventory_code,'0'))
    AND NVL(mmt1.Subinventory_code,'0') <= NVL(p_subinv_to,NVL(mmt1.Subinventory_code,'0'))
           AND mmt1.transaction_source_type_id = 5
           AND mmt1.transaction_action_id = 24
           AND nvl(mmt1.owning_tp_type,2) = 1
         )
    AND mmt.Transaction_action_id <> 30
    AND nvl(mmt.owning_tp_type,2) <> 1
  ORDER BY
    mmt.Acct_Period_ID
  , trunc(mmt.Transaction_Date)
  , mmt.CREATION_DATE
  , mmt.transaction_id;

Line: 753


    Select Transaction_source_type_name
    Into   l_rpt_item_tbl_rec(l_index).txn_source
    From   MTL_TXN_SOURCE_TYPES
    Where  Transaction_Source_Type_Id = l_item_txn_info.transaction_source_type_id;

Line: 759


	  select 'No TXN Source'
          INTO l_rpt_item_tbl_rec(l_index).txn_source
          from dual;

Line: 1006


v_select_clause		VARCHAR2(4000);

Line: 1016


  SELECT
    primary_cost_method
  FROM
    mtl_parameters
  WHERE
    organization_id = c_organization_id;

Line: 1038


v_select_clause:=NULL;

Line: 1046


   SELECT min(secondary_inventory_name)
   INTO subinv_min
   FROM MTL_SECONDARY_INVENTORIES
   WHERE ORGANIZATION_ID = p_org_id;

Line: 1053


   SELECT max(secondary_inventory_name)
   INTO subinv_max
   FROM MTL_SECONDARY_INVENTORIES
   WHERE ORGANIZATION_ID = p_org_id;

Line: 1060


v_select_clause := 'SELECT distinct MSI.Inventory_Item_Id
                                  , MSI.Primary_UOM_Code
                                  , MSI.concatenated_segments
                                  , MSI.description
                                  , ood.organization_name
                                  , gsob.currency_code ';

Line: 1134


v_final_query := v_select_clause || v_from_clause || v_where_clause || v_order_by;

Line: 1243


SELECT NVL(SUM(transaction_quantity),0)
INTO   l_item_ohq
FROM   mtl_onhand_quantities
WHERE  inventory_item_id = l_item_id
AND    organization_id = p_org_id
AND    subinventory_code BETWEEN  nvl(p_subinv_from,subinventory_code)
AND    nvl(p_subinv_to,subinventory_code)
AND    subinventory_code not in
       ( SELECT secondary_inventory_name
         FROM   mtl_secondary_inventories
         WHERE  organization_id = p_org_id
         AND    asset_inventory = 2 ) ;

Line: 1267


SELECT NVL(SUM(mmt.Primary_Quantity), 0)
INTO   l_item_trx_qty
FROM   MTL_MATERIAL_TRANSACTIONS mmt
WHERE  mmt.ORGANIZATION_ID = p_org_id
AND    mmt.INVENTORY_ITEM_ID = l_item_id
AND    mmt.SUBINVENTORY_CODE BETWEEN nvl(p_subinv_from, mmt.subinventory_code)
AND    nvl(p_subinv_to,mmt.subinventory_code)
AND  ( mmt.SUBINVENTORY_CODE is NULL
       OR
       mmt.SUBINVENTORY_CODE =
     ( SELECT SECONDARY_INVENTORY_NAME
       FROM   MTL_SECONDARY_INVENTORIES
       WHERE  SECONDARY_INVENTORY_NAME = mmt.SUBINVENTORY_CODE
       AND    ORGANIZATION_ID = mmt.ORGANIZATION_ID
       AND    QUANTITY_TRACKED = 1
       AND    ASSET_INVENTORY = 1 )
      )
AND    mmt.transaction_id NOT IN
       (SELECT mmt1.transaction_id
        FROM MTL_MATERIAL_TRANSACTIONS mmt1
        WHERE mmt1.organization_id   = p_org_id
          AND mmt1.inventory_item_id = l_item_id
          AND mmt1.SUBINVENTORY_CODE
              BETWEEN nvl(p_subinv_from, mmt1.subinventory_code)
          AND nvl(p_subinv_to,mmt1.subinventory_code)
          AND mmt1.transaction_source_type_id = 5
          AND mmt1.transaction_action_id = 24
          AND nvl(mmt1.owning_tp_type,2) = 1
         )
AND    mmt.Transaction_action_id <> 30
AND    nvl(mmt.owning_tp_type,2) <> 1
AND    mmt.TRANSACTION_DATE between l_rpt_from_date
       AND sysdate;

Line: 1318


	Select NVL(mmt.PRIOR_COST,mmt.ACTUAL_COST)
        INTO l_item_unit_cost
	FROM MTL_MATERIAL_TRANSACTIONS mmt
	WHERE mmt.TRANSACTION_ID = (SELECT min(transaction_id)
		  	            From Mtl_Material_Transactions
				    Where Organization_Id    = p_org_id
				      And Inventory_Item_Id    = l_item_id
				      And Transaction_date =
	                                  (Select min(Transaction_date)
				           From Mtl_Material_Transactions
				           Where Organization_Id    = p_org_id
                                             And Transaction_action_id NOT IN (24,30)
                                             AND nvl(owning_tp_type, 2) <> 1
				             And Transaction_date between l_rpt_from_date and l_rpt_to_date
				             And Inventory_Item_Id    = l_item_id
                                     AND subinventory_code BETWEEN nvl(p_subinv_from, subinventory_code)
                                     AND nvl(p_subinv_to, subinventory_code) ))
        AND ( mmt.SUBINVENTORY_CODE is NULL
              OR
              mmt.SUBINVENTORY_CODE =
            ( SELECT SECONDARY_INVENTORY_NAME
              FROM   MTL_SECONDARY_INVENTORIES
              WHERE  SECONDARY_INVENTORY_NAME = mmt.SUBINVENTORY_CODE
              AND    ORGANIZATION_ID = mmt.ORGANIZATION_ID
              AND    QUANTITY_TRACKED = 1
              AND    ASSET_INVENTORY = 1 )
            )
       AND mmt.transaction_id NOT IN
       (SELECT mmt1.transaction_id
        FROM MTL_MATERIAL_TRANSACTIONS mmt1
        WHERE mmt1.organization_id   = p_org_id
          AND mmt1.inventory_item_id = l_item_id
          AND nvl(mmt1.owning_tp_type,2) = 1
          AND mmt1.transaction_date
              BETWEEN l_rpt_from_date and l_rpt_to_date
          AND mmt1.subinventory_code
              BETWEEN nvl(p_subinv_from, mmt1.subinventory_code) AND nvl(p_subinv_to, mmt1.subinventory_code)
          AND mmt1.transaction_source_type_id = 5
          AND mmt1.transaction_action_id = 24
         )
       AND mmt.Transaction_action_id <> 30
       AND nvl(mmt.owning_tp_type,2) <> 1;

Line: 1397


        Insert_Rpt_Data
	   ( p_rpt_item_rec    => l_rpt_item_tbl_rec(l_index)
	   );