APPS.CSTPACIT SQL Statements

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

Line: 33

    SELECT COST_ELEMENT_ID
    FROM   MTL_TXN_COST_DET_INTERFACE
    WHERE  TRANSACTION_INTERFACE_ID = i_txn_interface_id;

Line: 50

  SELECT INVENTORY_ASSET_FLAG
  INTO   l_inv_asset_flag
  FROM   MTL_SYSTEM_ITEMS
  WHERE  INVENTORY_ITEM_ID = i_item_id
  AND    ORGANIZATION_ID = i_org_id;

Line: 67

  SELECT count(*)
  INTO   l_num_detail
  FROM   MTL_TXN_COST_DET_INTERFACE
  WHERE  TRANSACTION_INTERFACE_ID = i_txn_interface_id;

Line: 113

    SELECT count(*)
    INTO   l_err_num
    FROM   mtl_parameters mp,
           hr_all_organization_units_tl haout,
           mtl_txn_cost_det_interface mtcdi
    WHERE  mp.organization_id = i_org_id
    AND    haout.organization_id = mp.organization_id
    AND    haout.LANGUAGE = userenv('LANG')
    AND    mtcdi.transaction_interface_id = i_txn_interface_id
    AND    NVL(mtcdi.organization_id, mp.organization_id) = mp.organization_id
    AND    NVL(mtcdi.organization_code, mp.organization_code) = mp.organization_code
    AND    NVL(mtcdi.organization_name, haout.name) = haout.name
    AND    (  mtcdi.organization_id IS NOT NULL
           OR mtcdi.organization_code IS NOT NULL
           OR mtcdi.organization_name IS NOT NULL);

Line: 140

    SELECT count(*)
    INTO   l_err_num
    FROM   CST_COST_ELEMENTS CCE,
           MTL_TXN_COST_DET_INTERFACE MTCDI
    WHERE  MTCDI.TRANSACTION_INTERFACE_ID = i_txn_interface_id
    AND    CCE.COST_ELEMENT_ID = NVL(MTCDI.COST_ELEMENT_ID, CCE.COST_ELEMENT_ID)
    AND    CCE.COST_ELEMENT = NVL(MTCDI.COST_ELEMENT, CCE.COST_ELEMENT)
    AND    (MTCDI.COST_ELEMENT_ID IS NOT NULL OR
            MTCDI.COST_ELEMENT IS NOT NULL);

Line: 164

    SELECT count(*)
    INTO   l_err_num
    FROM   MFG_LOOKUPS ML,
           MTL_TXN_COST_DET_INTERFACE MTCDI
    WHERE  MTCDI.TRANSACTION_INTERFACE_ID = i_txn_interface_id
    AND    ML.LOOKUP_TYPE = 'CST_LEVEL'
    AND    ML.LOOKUP_CODE = NVL(MTCDI.LEVEL_TYPE, ML.LOOKUP_CODE)
    AND    ML.MEANING = NVL(MTCDI.LEVEL_NAME, ML.MEANING)
    AND    (MTCDI.LEVEL_TYPE IS NOT NULL OR
            MTCDI.LEVEL_NAME IS NOT NULL);

Line: 189

    SELECT count(*)
    INTO   l_err_num
    FROM   MTL_TXN_COST_DET_INTERFACE
    WHERE  TRANSACTION_INTERFACE_ID = i_txn_interface_id
    AND  NOT  ((NEW_AVERAGE_COST >= 0 AND  -- Bug 4759820
            (PERCENTAGE_CHANGE IS NULL AND VALUE_CHANGE IS NULL)) OR
            (PERCENTAGE_CHANGE >= -100 AND
            (NEW_AVERAGE_COST IS NULL AND  VALUE_CHANGE IS NULL)) OR
            (VALUE_CHANGE IS NOT NULL AND
            (NEW_AVERAGE_COST IS NULL AND PERCENTAGE_CHANGE IS NULL)));

Line: 215

    UPDATE MTL_TXN_COST_DET_INTERFACE MTCDI
    SET ORGANIZATION_ID = i_org_id
    WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id
    AND   ORGANIZATION_ID IS NULL;

Line: 221

    UPDATE MTL_TXN_COST_DET_INTERFACE MTCDI
    SET COST_ELEMENT_ID =
        (SELECT CCE.COST_ELEMENT_ID
         FROM   CST_COST_ELEMENTS CCE
         WHERE  CCE.COST_ELEMENT = NVL(MTCDI.COST_ELEMENT, CCE.COST_ELEMENT))
    WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id
    AND   COST_ELEMENT_ID IS NULL;

Line: 230

    UPDATE MTL_TXN_COST_DET_INTERFACE MTCDI
    SET LEVEL_TYPE =
        (SELECT ML.LOOKUP_CODE
         FROM   MFG_LOOKUPS ML
         WHERE  ML.LOOKUP_TYPE = 'CST_LEVEL'
         AND    ML.MEANING = NVL(MTCDI.LEVEL_NAME, ML.MEANING))
    WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id
    AND   LEVEL_TYPE IS NULL;

Line: 243

    SELECT count(*)
    INTO   l_err_num
    FROM   MTL_TXN_COST_DET_INTERFACE
    WHERE  TRANSACTION_INTERFACE_ID = i_txn_interface_id
    AND    ORGANIZATION_ID = i_org_id;

Line: 351

  SELECT count(*)
  INTO   l_num_detail
  FROM   MTL_TXN_COST_DET_INTERFACE
  WHERE  TRANSACTION_INTERFACE_ID = i_txn_interface_id;

Line: 358

   *  In this case, call cstpacit.cost_det_new_insert.
   */

  if (l_num_detail = 0) then
    cstpacit.cost_det_new_insert(i_txn_id, i_txn_action_id, i_org_id,
				 i_item_id, i_cost_group_id, i_txn_cost,
				 i_new_avg_cost, i_per_change, i_val_change,
				 i_mat_accnt, i_mat_ovhd_accnt, i_res_accnt,
				 i_osp_accnt, i_ovhd_accnt,
				 i_user_id, i_login_id, i_request_id,
				 i_prog_appl_id, i_prog_id,
				 l_err_num, l_err_code, l_err_msg);

Line: 385

    INSERT INTO MTL_CST_TXN_COST_DETAILS (
      TRANSACTION_ID,
      ORGANIZATION_ID,
      INVENTORY_ITEM_ID,
      COST_ELEMENT_ID,
      LEVEL_TYPE,
      TRANSACTION_COST,
      NEW_AVERAGE_COST,
      PERCENTAGE_CHANGE,
      VALUE_CHANGE,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_ID,
      PROGRAM_UPDATE_DATE
      )
    SELECT
      i_txn_id,
      i_org_id,
      i_item_id,
      COST_ELEMENT_ID,
      LEVEL_TYPE,
      ITEM_COST,

      NULL, /*changed from item_cost to NULL for bug 6404902 as for CL/LE not in
MTCDI, new avg cost should be taken as NULL*/

      NULL,
      0, /* changed from NULL to o for but 6404902 so that for CL/LE combination
not in MTCDI, it would not have any effect*/
      sysdate,
      i_user_id,
      sysdate,
      i_user_id,
      i_login_id,
      i_request_id,
      i_prog_appl_id,
      i_prog_id,
      sysdate
    FROM CST_LAYER_COST_DETAILS CLCD
    WHERE CLCD.LAYER_ID = l_layer_id;

Line: 431

UPDATE MTL_CST_TXN_COST_DETAILS mctcd
set (VALUE_CHANGE,
    PERCENTAGE_CHANGE,
    NEW_AVERAGE_COST)
=
(select
 mtcdi.VALUE_CHANGE,
 mtcdi.PERCENTAGE_CHANGE,
 mtcdi.NEW_AVERAGE_COST
 from MTL_TXN_COST_DET_INTERFACE mtcdi
 where mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id
 and mctcd.transaction_id = i_txn_id
 and mtcdi.level_type = mctcd.level_type
 and mtcdi.cost_element_id = mctcd.cost_element_id
)
where
mctcd.transaction_id = i_txn_id
and exists (select 1
            from MTL_TXN_COST_DET_INTERFACE mtcdi
            where mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id
            and mtcdi.level_type = mctcd.level_type
            and mtcdi.cost_element_id = mctcd.cost_element_id);

Line: 455

 INSERT INTO MTL_CST_TXN_COST_DETAILS (
      TRANSACTION_ID,
      ORGANIZATION_ID,
      INVENTORY_ITEM_ID,
      COST_ELEMENT_ID,
      LEVEL_TYPE,
      TRANSACTION_COST,
      NEW_AVERAGE_COST,
      PERCENTAGE_CHANGE,
      VALUE_CHANGE,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_ID,
      PROGRAM_UPDATE_DATE
      )
    SELECT
      i_txn_id,
      i_org_id,
      i_item_id,
      mtcdi.COST_ELEMENT_ID,
      mtcdi.LEVEL_TYPE,
      mtcdi.TRANSACTION_COST,
      mtcdi.NEW_AVERAGE_COST,
      mtcdi.PERCENTAGE_CHANGE,
      mtcdi.VALUE_CHANGE,
      sysdate,
      i_user_id,
      sysdate,
      i_user_id,
      i_login_id,
      i_request_id,
      i_prog_appl_id,
      i_prog_id,
      sysdate
    FROM MTL_TXN_COST_DET_INTERFACE MTCDI
    WHERE mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id
    and   (  (MTCDI.cost_element_id, MTCDI.level_type) not in
              (select mctcd1.cost_element_id,mctcd1.level_type
               from mtl_cst_txn_cost_details mctcd1
               where mctcd1.transaction_id=i_txn_id
               )
        );

Line: 508

/*INSERT INTO MTL_CST_TXN_COST_DETAILS (
      TRANSACTION_ID,
      ORGANIZATION_ID,
      INVENTORY_ITEM_ID,
      COST_ELEMENT_ID,
      LEVEL_TYPE,
      TRANSACTION_COST,
      NEW_AVERAGE_COST,
      PERCENTAGE_CHANGE,
      VALUE_CHANGE,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_ID,
      PROGRAM_UPDATE_DATE
      )
    values (
      i_txn_id,
      i_org_id,
      i_item_id,
      1,                        -- Hard coded to This level Material
      1,
      i_txn_cost,
      i_new_avg_cost,
      i_per_change,
      i_val_change,
      sysdate,
      i_user_id,
      sysdate,
      i_user_id,
      i_login_id,
      i_request_id,
      i_prog_appl_id,
      i_prog_id,
      sysdate);*/

Line: 547

INSERT INTO MTL_CST_TXN_COST_DETAILS (
      TRANSACTION_ID,
      ORGANIZATION_ID,
      INVENTORY_ITEM_ID,
      COST_ELEMENT_ID,
      LEVEL_TYPE,
      TRANSACTION_COST,
      NEW_AVERAGE_COST,
      PERCENTAGE_CHANGE,
      VALUE_CHANGE,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_ID,
      PROGRAM_UPDATE_DATE
      )
SELECT
      i_txn_id,
      i_org_id,
      i_item_id,
      mtcdi.COST_ELEMENT_ID,
      mtcdi.LEVEL_TYPE,
      mtcdi.TRANSACTION_COST,
      mtcdi.NEW_AVERAGE_COST,
      mtcdi.PERCENTAGE_CHANGE,
      mtcdi.VALUE_CHANGE,
      sysdate,
      i_user_id,
      sysdate,
      i_user_id,
      i_login_id,
      i_request_id,
      i_prog_appl_id,
      i_prog_id,
      sysdate
 FROM MTL_TXN_COST_DET_INTERFACE MTCDI
 WHERE mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id;

Line: 610

procedure cost_det_new_insert (
  i_txn_id                  in number,
  i_txn_action_id           in number,
  i_org_id	            in number,
  i_item_id		    in number,
  i_cost_group_id	    in number,
  i_txn_cost		    in number,
  i_new_avg_cost	    in number,
  i_per_change		    in number,
  i_val_change		    in number,
  i_mat_accnt		    in number,
  i_mat_ovhd_accnt	    in number,
  i_res_accnt		    in number,
  i_osp_accnt		    in number,
  i_ovhd_accnt		    in number,
  i_user_id                 in number,
  i_login_id                in number,
  i_request_id              in number,
  i_prog_appl_id            in number,
  i_prog_id                 in number,
  o_err_num                 out NOCOPY number,
  o_err_code		    out NOCOPY varchar2,
  o_err_msg                 out NOCOPY varchar2
)
is
  l_err_num                 number;

Line: 644

  cost_det_new_insert_error EXCEPTION;

Line: 648

    SELECT CLCD.COST_ELEMENT_ID
    FROM   CST_QUANTITY_LAYERS CL,
           CST_LAYER_COST_DETAILS CLCD
    WHERE  CL.LAYER_ID = l_layer_id
    AND    CLCD.LAYER_ID = l_layer_id;

Line: 668

	raise cost_det_new_insert_error;

Line: 672

   *  insert each elements into MTL_CST_TXN_COST_DETAILS.
   */

  if (l_layer_id <> 0) then

    if (i_txn_action_id = 24) then
      -- checking the existence of accounts for average cost update case
      open cost_elmt_ids;

Line: 697

          raise cost_det_new_insert_error;

Line: 703

    SELECT ITEM_COST
    INTO cl_item_cost
    FROM CST_QUANTITY_LAYERS
    WHERE LAYER_ID = l_layer_id;

Line: 712

      SELECT count(COST_ELEMENT_ID)
      INTO cost_element_count
      FROM CST_LAYER_COST_DETAILS
      WHERE LAYER_ID = l_layer_id;

Line: 718

      INSERT INTO MTL_CST_TXN_COST_DETAILS (
      TRANSACTION_ID,
      ORGANIZATION_ID,
      INVENTORY_ITEM_ID,
      COST_ELEMENT_ID,
      LEVEL_TYPE,
      TRANSACTION_COST,
      NEW_AVERAGE_COST,
      PERCENTAGE_CHANGE,
      VALUE_CHANGE,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_ID,
      PROGRAM_UPDATE_DATE
      )
      SELECT
      i_txn_id,
      i_org_id,
      i_item_id,
      CLCD.COST_ELEMENT_ID,
      CLCD.LEVEL_TYPE,
      DECODE(CL.ITEM_COST, 0, i_txn_cost / cost_element_count,
      i_txn_cost * CLCD.ITEM_COST / CL.ITEM_COST),
      DECODE(CL.ITEM_COST, 0, i_new_avg_cost / cost_element_count,
      i_new_avg_cost * CLCD.ITEM_COST / CL.ITEM_COST),
      i_per_change,
      DECODE(CL.ITEM_COST, 0, i_val_change / cost_element_count,
      i_val_change * CLCD.ITEM_COST / CL.ITEM_COST),
      sysdate,
      i_user_id,
      sysdate,
      i_user_id,
      i_login_id,
      i_request_id,
      i_prog_appl_id,
      i_prog_id,
      sysdate
      FROM  CST_QUANTITY_LAYERS CL, CST_LAYER_COST_DETAILS CLCD
      WHERE CL.LAYER_ID = l_layer_id
      AND CLCD.LAYER_ID = l_layer_id;

Line: 764

  /*  If layer detail does not exist, then insert a new row
   *  as a this level material.
   */
  else

    if (i_txn_action_id = 24 and i_mat_accnt is null) then
      -- Error occured only for average cost update

      FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');

Line: 777

      raise cost_det_new_insert_error;

Line: 781

    INSERT INTO MTL_CST_TXN_COST_DETAILS (
      TRANSACTION_ID,
      ORGANIZATION_ID,
      INVENTORY_ITEM_ID,
      COST_ELEMENT_ID,
      LEVEL_TYPE,
      TRANSACTION_COST,
      NEW_AVERAGE_COST,
      PERCENTAGE_CHANGE,
      VALUE_CHANGE,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_ID,
      PROGRAM_UPDATE_DATE
      )
    values (
      i_txn_id,
      i_org_id,
      i_item_id,
      1,			/* Hard coded to This level Material */
      1,
      i_txn_cost,
      i_new_avg_cost,
      i_per_change,
      i_val_change,
      sysdate,
      i_user_id,
      sysdate,
      i_user_id,
      i_login_id,
      i_request_id,
      i_prog_appl_id,
      i_prog_id,
      sysdate);

Line: 824

  when cost_det_new_insert_error then
    o_err_num := l_err_num;

Line: 827

    o_err_msg := 'CSTPACIT.COST_DET_NEW_INSERT:' || l_err_msg;

Line: 830

    o_err_msg := 'CSTPACIT.COST_DET_NEW_INSERT:' || substr(SQLERRM,1,150);

Line: 832

end cost_det_new_insert;