APPS.CSTPAVCP SQL Statements

Line: 3703

  I_NO_UPDATE_MMT IN	NUMBER,
  I_NO_UPDATE_QTY IN	NUMBER,
  I_USER_ID	IN	NUMBER,
  I_LOGIN_ID	IN	NUMBER,
  I_REQ_ID	IN	NUMBER,
  I_PRG_APPL_ID IN	NUMBER,
  I_PRG_ID	IN	NUMBER,
  O_Err_Num	OUT NOCOPY	NUMBER,
  O_Err_Code	OUT NOCOPY	VARCHAR2,
  O_Err_Msg	OUT NOCOPY	VARCHAR2
) IS
  layer_qty	NUMBER;

Line: 3732

   ** Update Mtl_Material_Transactions to set actual cost, prior     **
   ** cost, new cost and prior costed quantity.			     **
   ********************************************************************/
  if (i_no_update_mmt = 0) then
    CSTPAVCP.update_mmt(
			i_org_id,
			i_txn_id,
			-1,		-- txfr_txn_id is not applicable
			i_layer_id,
			0,
			i_user_id,
			i_login_id,
			i_req_id,
			i_prg_appl_id,
			i_prg_id,
			l_err_num,
			l_err_code,
			l_err_msg);

Line: 3757

   ** Update layer quantity information in cst_quantity_layers.      **
   ** There is no need to update the layer quantity for the 	     **
   ** following transactions:                                        **
   ** 1) wip scrap transactions 				     **
   ** 2) Expense flag = 1					     **
   ********************************************************************/
  if ((i_txn_action_id = 30) or (i_no_update_qty = 1) or (i_exp_flag = 1)
      ) then
    return;

Line: 3770

    Update cst_quantity_layers cql
    set last_update_date = sysdate,
        last_updated_by = i_user_id,
        last_update_login = i_login_id,
        request_id = i_req_id,
        program_application_id = i_prg_appl_id,
        program_id = i_prg_id,
        program_update_date = sysdate,
        layer_quantity = (cql.layer_quantity + decode(i_txn_action_id, 22, -1*abs(i_txn_qty), i_txn_qty)),
        update_transaction_id = i_txn_id
    where layer_id = i_layer_id;

Line: 3785

  CSTPAVCP.update_item_cost(
			i_org_id,
			i_txn_id,
			i_layer_id,
			i_cost_type,
			i_item_id,
			0,                -- mandatory_update flag is not set
			i_user_id,
			i_login_id,
			i_req_id,
			i_prg_appl_id,
			i_prg_id,
			l_err_num,
			l_err_code,
  			l_err_msg);

Line: 3826

procedure update_mmt(
  I_ORG_ID	IN	NUMBER,
  I_TXN_ID	IN 	NUMBER,
  I_TXFR_TXN_ID	IN 	NUMBER,
  I_LAYER_ID	IN	NUMBER,
  I_COST_UPDATE	IN	NUMBER,
  I_USER_ID	IN	NUMBER,
  I_LOGIN_ID	IN	NUMBER,
  I_REQ_ID	IN	NUMBER,
  I_PRG_APPL_ID IN	NUMBER,
  I_PRG_ID	IN	NUMBER,
  O_Err_Num	OUT NOCOPY	NUMBER,
  O_Err_Code	OUT NOCOPY	VARCHAR2,
  O_Err_Msg	OUT NOCOPY	VARCHAR2
) IS
  layer_qty		NUMBER;

Line: 3866

    fnd_file.put_line(fnd_file.log, '>>>>Inside Update_MMT');

Line: 3871

  Select transaction_action_id
  into   l_transaction_action_id
  from mtl_material_transactions
  where transaction_id = i_txn_id;

Line: 3889

  Select layer_quantity
  into layer_qty
  from cst_quantity_layers cql
  where cql.layer_id = i_layer_id;

Line: 3896

  select count(*)
  into l_cost_exists
  from mtl_cst_actual_cost_details cacd
  where cacd.transaction_id = i_txn_id
  and cacd.organization_id = i_org_id
  and cacd.layer_id = i_layer_id;

Line: 3908

    select decode(transaction_action_id, 1,
             decode(transaction_source_type_id, 5,
               decode(cost_group_id, NVL(transfer_cost_group_id, cost_group_id), 0, 1)
               ,0),
              27,decode(transaction_source_type_id, 5,
                          decode(cost_group_id, NVL(transfer_cost_group_id, cost_group_id), 0, 1),
                          0)
             ,0)
    into l_citw_flag
    from mtl_material_transactions
    where transaction_id = i_txn_id;

Line: 3924

      SELECT inventory_asset_flag
        INTO l_asset_item_flag
        FROM mtl_system_items
       WHERE organization_id = i_org_id
         AND inventory_item_id = (SELECT inventory_item_id
                                    FROM mtl_material_transactions
                                   WHERE transaction_id = i_txn_id);

Line: 3939

    Update mtl_material_transactions mmt
    set (last_update_date,
	 last_updated_by,
	 last_update_login,
	 request_id,
	 program_application_id,
	 program_id,
	 program_update_date,
 	 actual_cost,
	 prior_cost,
 	 new_cost,
	 variance_amount,
	 prior_costed_quantity,
	 quantity_adjusted) =
    (select sysdate,
	    i_user_id,
	    i_login_id,
	    i_req_id,
	    i_prg_appl_id,
	    i_prg_id,
	    sysdate,
            SUM(DECODE(l_asset_item_flag, 'N', 0, NVL(actual_cost,0))), -- Bug5137993
	    sum(nvl(prior_cost,0)),
	    sum(nvl(new_cost,0)),
	    sum(nvl(variance_amount,0)),
	    layer_qty,
	    decode(i_cost_update,1,layer_qty,NULL)
      from mtl_cst_actual_cost_details cacd
      where cacd.transaction_id = i_txn_id
      and cacd.organization_id = i_org_id
      and cacd.layer_id = i_layer_id
      and cacd.transaction_action_id =
          decode(l_citw_flag, 1, 2,		-- for citw, just select
                 cacd.transaction_action_id))	-- sub_txfr rows
    where mmt.transaction_id = l_txn_id;

Line: 3977

    Update mtl_material_transactions mmt
    set	last_update_date = sysdate,
    	last_updated_by = i_user_id,
        last_update_login = i_login_id,
        request_id = i_req_id,
 	program_application_id = i_prg_appl_id,
	program_id = i_prg_id,
	program_update_date = sysdate,
        actual_cost = 0,
	prior_cost = 0,
 	new_cost = 0,
	variance_amount=0,
	prior_costed_quantity = layer_qty
    where mmt.transaction_id = l_txn_id;

Line: 3999

      SELECT
         mmt.inventory_item_id
      INTO
         l_item_id
      FROM mtl_material_transactions mmt
      WHERE mmt.transaction_id = i_txn_id;

Line: 4007

      UPDATE mtl_material_transactions mmt
      SET    prior_costed_quantity = (
               SELECT cql.layer_quantity - mmt.primary_quantity
               FROM   cst_quantity_layers cql,
                     mtl_parameters mp
              WHERE  cql.organization_id = i_org_id
              AND    cql.inventory_item_id = l_item_id
              AND    cql.cost_group_id = mp.default_cost_group_id
              AND    mp.organization_id = i_org_id
            )
      WHERE mmt.transaction_id = i_txn_id
      AND   EXISTS (
	      SELECT 'X'
              FROM   cst_quantity_layers cql,
                     mtl_parameters mp
              WHERE  cql.organization_id = i_org_id
	      AND    cql.inventory_item_id = l_item_id
              AND    cql.cost_group_id = mp.default_cost_group_id
              AND    mp.organization_id = i_org_id
           );

Line: 4048

   SELECT nvl(msi.asset_inventory,-9),
          mmt.transfer_transaction_id,
          mmt.inventory_item_id,
          mmt.transfer_cost_group_id
   INTO l_from_inv,
        l_transfer_txn_id,
        l_item_id,
        l_transfer_cost_grp_id
   FROM mtl_material_transactions mmt,
        mtl_secondary_inventories msi
   WHERE mmt.transaction_id = i_txn_id
   AND mmt.subinventory_code = msi.secondary_inventory_name
   AND mmt.organization_id = msi.organization_id;

Line: 4063

   SELECT nvl(msi.asset_inventory,-9)
   INTO l_to_inv
   FROM mtl_material_transactions mmt,
        mtl_secondary_inventories msi
   WHERE mmt.transaction_id = i_txn_id
   AND nvl(mmt.transfer_subinventory,mmt.subinventory_code) = msi.secondary_inventory_name
   AND mmt.organization_id = msi.organization_id;

Line: 4075

	UPDATE mtl_material_transactions mmt
	SET prior_costed_quantity =
	(SELECT
		layer_quantity
	FROM	cst_quantity_layers cql
	WHERE	cql.organization_id = i_org_id
	AND	cql.inventory_item_id = l_item_id
	AND	cql.cost_group_id = l_transfer_cost_grp_id)
	WHERE mmt.transaction_id = l_transfer_txn_id
	AND EXISTS (
			SELECT 'X'
			FROM cst_quantity_layers cql
			WHERE cql.organization_id = i_org_id
			AND   cql.inventory_item_id = l_item_id
                        AND   cql.cost_group_id = l_transfer_cost_grp_id);

Line: 4093

          update mtl_material_transactions mmt
		 set prior_costed_quantity = 0
          where  mmt.transaction_id = l_transfer_txn_id;

Line: 4105

    fnd_file.put_line(fnd_file.log, 'Update_MMT >>>');

Line: 4111

      o_err_msg := 'CSTPAVCP.update_mmt (' || to_char(l_stmt_num) ||
                   '): '
	           || substr(SQLERRM,1,200);

Line: 4115

END update_mmt;

Line: 4136

PROCEDURE update_item_cost(
  I_ORG_ID	IN	NUMBER,
  I_TXN_ID	IN	NUMBER,
  I_LAYER_ID	IN	NUMBER,
  I_COST_TYPE	IN	NUMBER,
  I_ITEM_ID	IN	NUMBER,
  I_MANDATORY_UPDATE    IN      NUMBER,
  I_USER_ID	IN	NUMBER,
  I_LOGIN_ID	IN	NUMBER,
  I_REQ_ID	IN	NUMBER,
  I_PRG_APPL_ID	IN	NUMBER,
  I_PRG_ID	IN	NUMBER,
  O_Err_Num	OUT NOCOPY	NUMBER,
  O_Err_Code	OUT NOCOPY	VARCHAR2,
  O_Err_Msg	OUT NOCOPY	VARCHAR2
) IS
  total_value   NUMBER;  -- Added for bug 4905189

Line: 4168

 select DEFAULT_MATL_OVHD_COST_ID
 into l_default_MOH_subelement
 from mtl_parameters
 where organization_id= I_ORG_ID;

Line: 4174

    fnd_file.put_line(fnd_file.log, '>>>>Inside Update_Item_Cost');

Line: 4179

  select nvl(sum(layer_quantity),0), nvl(sum(layer_quantity*nvl(item_cost,0)),0)
  into total_qty, total_value
  from cst_quantity_layers cql
  where cql.inventory_item_id = i_item_id
  and cql.organization_id = i_org_id;

Line: 4187

  if ( (total_qty <= 0) and (i_mandatory_update = 0) ) then
    return;

Line: 4198

  Delete from cst_item_cost_details
  where inventory_item_id = i_item_id
  and organization_id = i_org_id
  and cost_type_id = i_cost_type;

Line: 4209

  Insert into cst_item_cost_details (
	inventory_item_id,
	organization_id,
	cost_type_id,
	last_update_date,
	last_updated_by,
	creation_date,
	created_by,
	last_update_login,
	level_type,
	usage_rate_or_amount,
	basis_type,
	basis_factor,
	net_yield_or_shrinkage_factor,
	item_cost,
	cost_element_id,
	rollup_source_type,
	request_id,
	program_application_id,
	program_id,
	program_update_date,
	resource_id)	-----------------Bug 3959770
  select
	i_item_id,
	i_org_id,
	i_cost_type,
	sysdate,
	i_user_id,
	sysdate,
	i_user_id,
	i_login_id,
	clcd.level_type,
        (sum(clcd.item_cost*decode(sign(total_qty),1,cql.layer_quantity,1)))
           /decode(sign(total_qty),1,total_qty,1), -- modified for bug#3835412
	1,
	1,
	1,
        (sum(clcd.item_cost*decode(sign(total_qty),1,cql.layer_quantity,1)))
           /decode(sign(total_qty),1,total_qty,1), -- modified for bug#3835412
	clcd.cost_element_id,
	1,
	i_req_id,
      	i_prg_appl_id,
      	i_prg_id,
      	sysdate,
	decode(clcd.cost_element_id,2,l_default_MOH_subelement,NULL) --------------Bug 3959770
  from cst_layer_cost_details clcd,
       cst_quantity_layers cql
  where cql.organization_id = i_org_id
  and cql.inventory_item_id = i_item_id
  and cql.layer_id = clcd.layer_id
  group by cost_element_id, level_type;

Line: 4264

    Update cst_item_costs cic
  Set (last_updated_by,
	last_update_date,
	last_update_login,
	request_id,
	program_application_id,
	program_id,
	program_update_date,
	pl_material,
	pl_material_overhead,
	pl_resource,
	pl_outside_processing,
	pl_overhead,
	tl_material,
	tl_material_overhead,
	tl_resource,
	tl_outside_processing,
	tl_overhead,
	material_cost,
	material_overhead_cost,
	resource_cost,
	outside_processing_cost,
	overhead_cost,
	pl_item_cost,
	tl_item_cost,
	item_cost,
	unburdened_cost,
	burden_cost) =
  (SELECT
        i_user_id,
        sysdate,
        i_login_id,
	i_req_id,
      	i_prg_appl_id,
      	i_prg_id,
      	sysdate,
	SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)),
        SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)),
        SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)),
        SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,4,ITEM_COST,0),0)),
        SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)),
        SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)),
        SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)),
        SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)),
        SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,4,ITEM_COST,0),0)),
        SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)),
        SUM(DECODE(COST_ELEMENT_ID,1,ITEM_COST)),
        SUM(DECODE(COST_ELEMENT_ID,2,ITEM_COST)),
        SUM(DECODE(COST_ELEMENT_ID,3,ITEM_COST)),
        SUM(DECODE(COST_ELEMENT_ID,4,ITEM_COST)),
        SUM(DECODE(COST_ELEMENT_ID,5,ITEM_COST)),
        SUM(DECODE(LEVEL_TYPE,2,ITEM_COST,0)),
        SUM(DECODE(LEVEL_TYPE,1,ITEM_COST,0)),
        SUM(ITEM_COST),
        SUM(DECODE(COST_ELEMENT_ID, 2,DECODE(LEVEL_TYPE,2,ITEM_COST,0), ITEM_COST)),
        SUM(DECODE(COST_ELEMENT_ID, 2,DECODE(LEVEL_TYPE,1,ITEM_COST,0),0))
   from CST_ITEM_COST_DETAILS  cicd
   where cicd.inventory_item_id = i_item_id
   and cicd.organization_id = i_org_id
   and cicd.cost_type_id = i_cost_type)
  where cic.inventory_item_id = i_item_id
  and cic.organization_id = i_org_id
  and cic.cost_type_id = i_cost_type
  and exists
     (select 'there is detail cost'
      from cst_item_cost_details cicd
      where cicd.inventory_item_id = i_item_id
      and cicd.organization_id = i_org_id
      and cicd.cost_type_id = i_cost_type);

Line: 4335

    fnd_file.put_line(fnd_file.log, 'Update_Item_Cost >>>');

Line: 4342

      o_err_msg := 'CSTPAVCP.update_item_cost (' || to_char(l_stmt_num) ||
                   '): '
		   || substr(SQLERRM, 1,200);

Line: 4346

END update_item_cost;

Line: 4376

  O_NO_UPDATE_MMT IN OUT NOCOPY NUMBER,
  O_EXP_FLAG    IN OUT NOCOPY   NUMBER,
  O_HOOK_USED   OUT NOCOPY	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: 4388

  l_txn_update_id NUMBER;

Line: 4405

  l_update_std          NUMBER;

Line: 4460

  l_update_std := 0;

Line: 4506

  select NVL(elemental_visibility_enabled,'N')
  into l_elemental_visible
  from mtl_interorg_parameters
  where from_organization_id = l_from_org
  and to_organization_id = l_to_org;

Line: 4513

  select decode(primary_cost_method, 2,
  		(select decode(primary_cost_method, 2, 1, 0)
       		from mtl_parameters
       		where organization_id = l_from_org),
		0)
  into l_ave_to_ave
  from mtl_parameters
  where organization_id = l_to_org;

Line: 4530

  select decode(l_std_from_org, 1, l_from_org,
    decode(l_std_to_org,1,l_to_org,-1))
  into l_std_org
  from dual;

Line: 4539

    select cost_organization_id
    into l_std_cost_org
    from mtl_parameters
    where organization_id = l_std_org;

Line: 4547

    select decode(inventory_asset_flag, 'Y', 0, 1)
    into l_std_exp
    from mtl_system_items
    where inventory_item_id = i_item_id
    and organization_id = l_std_org;

Line: 4554

    select decode(l_std_exp,1,1,decode(asset_inventory,1,0,1))
    into l_std_exp
    from mtl_secondary_inventories msi
        ,mtl_material_transactions mmt
    where mmt.transaction_id = i_txn_id
    and mmt.organization_id = l_std_org
    and msi.organization_id = l_std_org
    and msi.secondary_inventory_name = mmt.subinventory_code;

Line: 4586

    o_no_update_mmt := 1;

Line: 4600

    select decode(inventory_asset_flag, 'Y',0,1)
    into o_exp_flag
    from mtl_system_items
    where inventory_item_id = i_item_id
    and organization_id = i_org_id;

Line: 4652

  UPDATE mtl_material_transactions mmt
  SET TRANSFER_PRIOR_COSTED_QUANTITY =
  (SELECT
    layer_quantity
  FROM  cst_quantity_layers cql
  WHERE cql.organization_id = l_which_org
  AND   cql.inventory_item_id = i_item_id
  AND   cql.cost_group_id = l_which_cst_grp)
  WHERE mmt.transaction_id = i_txn_id
  AND EXISTS (
      SELECT 'X'
      FROM cst_quantity_layers cql
      WHERE cql.organization_id = l_which_org
      AND   cql.inventory_item_id = i_item_id
                        AND   cql.cost_group_id = l_which_cst_grp);

Line: 4669

          update mtl_material_transactions mmt
     set TRANSFER_PRIOR_COSTED_QUANTITY = 0
          where  mmt.transaction_id = i_txn_id;

Line: 4681

    UPDATE mtl_material_transactions mmt
    SET mmt.transaction_cost = (select (mmt1.transaction_cost * mmt1.currency_conversion_rate)
                                from mtl_material_transactions mmt1
                                where mmt1.transaction_id = mmt.transfer_transaction_id
                                and mmt1.costed_flag is null)
    WHERE mmt.transaction_id = i_txn_id
    AND nvl(mmt.transaction_cost,0) = 0;

Line: 4716

		-- set l_to_std_exp = 1 to later insert into mcacd from mctcd.
		    l_stmt_num := 102;

Line: 4718

			select decode(inventory_asset_flag, 'Y', 0, 1)
			into l_to_std_exp
			from mtl_system_items
			where inventory_item_id = i_item_id
			and organization_id = l_std_org;

Line: 4740

      select count(*)
      into l_count
      from cst_item_cost_details
      where /* organization_id = l_std_org : bugfix 3048258 */
            organization_id = l_std_cost_org
      and cost_type_id = 1
      and inventory_item_id = i_item_id;

Line: 4749

      /* If no rows exist in cicd (item hasn't been costed), insert into */
      /* mcacd using 0 value of this level material */
      if (l_count > 0) then
        insert into mtl_cst_actual_cost_details (
    transaction_id,
    organization_id,
    layer_id,
    cost_element_id,
    level_type,
    transaction_action_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    last_update_login,
    request_id,
    program_application_id,
    program_id,
    program_update_date,
    inventory_item_id,
    actual_cost,
    prior_cost,
    new_cost,
    insertion_flag,
    variance_amount,
    user_entered)
        select i_txn_id,
    l_std_org,
    -1,
    cicd.cost_element_id,
    cicd.level_type,
    i_txn_action_id,
    sysdate,
          i_user_id,
          sysdate,
          i_user_id,
          i_login_id,
          i_req_id,
          i_prg_appl_id,
          i_prg_id,
          sysdate,
    i_item_id,
          nvl(sum(cicd.item_cost),0),
          NULL,
    NULL,
          'N',
          0,
          'N'
        from cst_item_cost_details cicd
        where /* organization_id = l_std_org : bugfix 3048258 */
              organization_id = l_std_cost_org
        and cost_type_id = 1
        and inventory_item_id = i_item_id
        group by cost_element_id, level_type;

Line: 4804

        insert into mtl_cst_actual_cost_details (
    transaction_id,
    organization_id,
    layer_id,
    cost_element_id,
    level_type,
    transaction_action_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    last_update_login,
    request_id,
    program_application_id,
    program_id,
    program_update_date,
    inventory_item_id,
    actual_cost,
    prior_cost,
    new_cost,
    insertion_flag,
    variance_amount,
    user_entered)
        values ( i_txn_id,
    l_std_org,
    -1,
    1,
    1,
    i_txn_action_id,
    sysdate,
          i_user_id,
          sysdate,
          i_user_id,
          i_login_id,
          i_req_id,
          i_prg_appl_id,
          i_prg_id,
          sysdate,
    i_item_id,
          0,
          NULL,
    NULL,
          'N',
          0,
          'N');

Line: 4882

            FND_FILE.PUT_LINE(fnd_file.log, to_char(l_stmt_num) || 'Insert into MACS from CICD');

Line: 4884

          Insert into mtl_actual_cost_subelement(
            layer_id,
            transaction_id,
            organization_id,
            cost_element_id,
            level_type,
            resource_id,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login,
            request_id,
            program_application_id,
            program_id,
            program_update_date,
            actual_cost,
            user_entered)
          select
            -1,
            i_txn_id,
            l_std_org,
            cicd.cost_element_id,
            cicd.level_type,
            cicd.resource_id,
            sysdate,
            i_user_id,
            sysdate,
            i_user_id,
            i_login_id,
            i_req_id,
            i_prg_appl_id,
            i_prg_id,
            sysdate,
            cicd.item_cost,
            'N'
          from cst_item_cost_details cicd
          where inventory_item_id = i_item_id
          and /* organization_id = l_std_org : bugfix 3048258 */
              organization_id = l_std_cost_org
          and cost_type_id = 1
          and cost_element_id = 2
          and level_type = 1;

Line: 4933

        update mtl_material_transactions mmt
        set (last_update_date,
     last_updated_by,
     last_update_login,
     request_id,
     program_application_id,
         program_id,
     program_update_date,
     actual_cost) =
        (select sysdate,
    i_user_id,
    i_login_id,
    i_req_id,
    i_prg_appl_id,
    i_prg_id,
    sysdate,
    nvl(sum(actual_cost),0)
         from mtl_cst_actual_cost_details cacd
         where cacd.transaction_id = i_txn_id
         and cacd.organization_id = l_std_org
   and cacd.layer_id = -1)
        where mmt.transaction_id = i_txn_id;

Line: 4957

      l_update_std := 1;

Line: 4968

    select transfer_transaction_id
    into l_txn_update_id
    from mtl_material_transactions
    where transaction_id = i_txn_id;

Line: 4973

    l_txn_update_id := i_txn_id;

Line: 4977

    fnd_file.put_line(fnd_file.log, '>>>Transaction update id'||to_char(l_txn_update_id));

Line: 5010

      /* Exception block inserted for bug 1399079, (non costed items) */
      BEGIN
         select item_cost, -1
         into l_snd_txn_cost, l_from_layer_id
         from cst_item_costs
         where cost_type_id = l_cost_type_id
         and inventory_item_id = i_item_id
         /* and organization_id = l_from_org; : bugfix 3048258 */

Line: 5086

                select sum(actual_cost), layer_id
                into l_snd_txn_cost, l_from_layer_id
                from mtl_cst_actual_cost_details
                where transaction_id= i_txn_id
                and   organization_id= i_org_id
		and   layer_id= i_layer_id
                group by layer_id;

Line: 5098

                select item_cost, layer_id
                into l_snd_txn_cost, l_from_layer_id
                from cst_quantity_layers
                where organization_id = l_from_org
                and inventory_item_id = i_item_id
                and cost_group_id = l_from_cost_grp;

Line: 5110

        select item_cost, layer_id
        into l_snd_txn_cost, l_from_layer_id
        from cst_quantity_layers
        where organization_id = l_from_org
        and inventory_item_id = i_item_id
        and cost_group_id = l_from_cost_grp;

Line: 5164

    Update mtl_material_transactions
    Set transfer_cost =
     (select decode(nvl(transfer_percentage, -999),-999, transfer_cost,
                   (transfer_percentage * l_snd_txn_cost *
        decode(i_txn_action_id, 12, abs(primary_quantity)/l_um_rate,
                    abs(primary_quantity)))*l_xfer_conv_rate/100) -- bug 2827548-added l_xfer_conv_rate
      from mtl_material_transactions
      where transaction_id = i_txn_id)
    where transaction_id = i_txn_id
       or (transaction_id = decode(i_txn_action_id,3,l_txn_update_id,-1));

Line: 5178

    select nvl(transfer_cost,0), nvl(transportation_cost,0),
           decode(i_txn_action_id,12,(primary_quantity / l_um_rate),primary_quantity)
    into l_txfr_cost, l_trans_cost, l_snd_qty
    from mtl_material_transactions
    where transaction_id = i_txn_id;

Line: 5220

               insert price into MCTCD */
    if (i_tprice_option = 2) then
       l_rcv_txn_cost := i_txf_price;

Line: 5234

               l_txn_update_id,l_from_org, l_to_org,
               l_snd_qty,l_txfr_cost,l_trans_cost,l_conv_rate,l_um_rate,
               i_user_id,i_login_id,i_req_id,i_prg_appl_id,i_prg_id,
               l_err_num,l_err_code,l_err_msg,l_cost_hook_io);

Line: 5245

       insert into mtl_cst_txn_cost_details (
          transaction_id,
          organization_id,
        cost_element_id,
        level_type,
        last_update_date,
        last_updated_by,
        creation_date,
        created_by,
        last_update_login,
        request_id,
        program_application_id,
        program_id,
        program_update_date,
        inventory_item_id,
        transaction_cost,
        new_average_cost,
        percentage_change,
        value_change)
          values (l_txn_update_id,
        l_to_org,
        1,
          1,
        sysdate,
              i_user_id,
              sysdate,
              i_user_id,
              i_login_id,
              i_req_id,
              i_prg_appl_id,
              i_prg_id,
              sysdate,
              i_item_id,
              l_rcv_txn_cost,
        0,
        0,
        0);

Line: 5286

    Update mtl_material_transactions
    Set transaction_cost = l_new_txn_cost
    where transaction_id = i_txn_id;

Line: 5291

      Update mtl_material_transactions
      Set transaction_cost = l_rcv_txn_cost
      where transaction_id = l_txn_update_id;

Line: 5299

      update mtl_material_transactions mmt
      set mmt.transaction_cost = l_rcv_txn_cost
      where mmt.transfer_transaction_id = i_txn_id
      and mmt.transaction_action_id = 12;

Line: 5306

      update mtl_material_transactions mmt
      set mmt.transaction_cost = l_snd_txn_cost
      where mmt.transaction_id =
        (select mmt1.transfer_transaction_id
         from mtl_material_transactions mmt1
         where mmt1.transaction_id = i_txn_id)
      and mmt.transaction_action_id = 21
      and nvl(mmt.transaction_cost,0) = 0;

Line: 5324

    select decode(inventory_asset_flag, 'Y', 0, 1)
    into l_txfr_std_exp
    from mtl_system_items
    where inventory_item_id = i_item_id
    and organization_id = l_std_org;

Line: 5331

    select transfer_transaction_id
    into l_txfr_txn_id
    from mtl_material_transactions mmt
    where mmt.transaction_id = i_txn_id;

Line: 5337

    select decode(l_txfr_std_exp,1,1,decode(asset_inventory,1,0,1))
    into l_txfr_std_exp
    from mtl_secondary_inventories msi
        ,mtl_material_transactions mmt
    where mmt.transaction_id = l_txfr_txn_id
    and mmt.organization_id = l_std_org
    and msi.organization_id = l_std_org
    and msi.secondary_inventory_name = mmt.subinventory_code;

Line: 5348

      insert into mtl_cst_actual_cost_details (
          transaction_id,
          organization_id,
          layer_id,
          cost_element_id,
          level_type,
          transaction_action_id,
          last_update_date,
          last_updated_by,
          creation_date,
          created_by,
          last_update_login,
          request_id,
          program_application_id,
          program_id,
          program_update_date,
          inventory_item_id,
          actual_cost,
          prior_cost,
          new_cost,
          insertion_flag,
          variance_amount,
          user_entered)
      select
          l_txfr_txn_id,
          l_std_org,
          -1,
          decode(l_elemental_visible,'Y',ctcd.cost_element_id,1),
          decode(l_elemental_visible,'Y',ctcd.level_type,1),
          i_txn_action_id,
          sysdate,
          i_user_id,
          sysdate,
          i_user_id,
          i_login_id,
          i_req_id,
          i_prg_appl_id,
          i_prg_id,
          sysdate,
          ctcd.inventory_item_id,
          decode(l_elemental_visible,'Y',ctcd.transaction_cost,l_rcv_txn_cost),
          NULL,
          NULL,
          'N',
          0,
          'N'
      FROM mtl_cst_txn_cost_details ctcd
      WHERE ctcd.transaction_id = l_txn_update_id
      AND ctcd.organization_id = l_std_org
      /* AND ctcd.transaction_cost >= 0 */; -- modified for bug#3835412

Line: 5399

	  update mtl_material_transactions mmt
	  set (last_update_date,
			last_updated_by,
			last_update_login,
			request_id,
			program_application_id,
			program_id,
			program_update_date,
			actual_cost) =
        	(select sysdate,
			i_user_id,
			i_login_id,
			i_req_id,
			i_prg_appl_id,
			i_prg_id,
			sysdate,
			nvl(sum(actual_cost),0)
			from mtl_cst_actual_cost_details cacd
			where cacd.transaction_id = l_txn_update_id
			and cacd.organization_id = l_std_org
			and cacd.layer_id = -1)
	  where mmt.transaction_id = l_txn_update_id;

Line: 5425

    if (l_update_std = 1) then
       /*  the receiving org is standard exp. */
      l_stmt_num := 210;

Line: 5430

      insert into mtl_cst_actual_cost_details (
	  transaction_id,
 	  organization_id,
    	  layer_id,
	  cost_element_id,
	  level_type,
	  transaction_action_id,
	  last_update_date,
	  last_updated_by,
	  creation_date,
	  created_by,
	  last_update_login,
	  request_id,
	  program_application_id,
	  program_id,
	  program_update_date,
	  inventory_item_id,
	  actual_cost,
	  prior_cost,
	  new_cost,
	  insertion_flag,
	  variance_amount,
	  user_entered)
      select
    i_txn_id,
          l_std_org,
    -1,
          decode(l_elemental_visible,'Y',ctcd.cost_element_id,1),
          decode(l_elemental_visible,'Y',ctcd.level_type,1),
    i_txn_action_id,
          sysdate,
          i_user_id,
          sysdate,
          i_user_id,
          i_login_id,
          i_req_id,
          i_prg_appl_id,
          i_prg_id,
          sysdate,
          ctcd.inventory_item_id,
          decode(l_elemental_visible,'Y',ctcd.transaction_cost,l_rcv_txn_cost),
          NULL,
          NULL,
          'N',
          0,
          'N'
      FROM mtl_cst_txn_cost_details ctcd
      WHERE ctcd.transaction_id = l_txn_update_id
      AND ctcd.organization_id = l_std_org
      /* AND ctcd.transaction_cost >= 0 */; -- modified for bug#3835412

Line: 5483

        update mtl_material_transactions mmt
        set (last_update_date,
     last_updated_by,
     last_update_login,
     request_id,
     program_application_id,
         program_id,
     program_update_date,
     actual_cost) =
        (select sysdate,
    i_user_id,
    i_login_id,
    i_req_id,
    i_prg_appl_id,
    i_prg_id,
    sysdate,
    nvl(sum(actual_cost),0)
         from mtl_cst_actual_cost_details cacd
         where cacd.transaction_id = l_txn_update_id
         and cacd.organization_id = l_std_org
   and cacd.layer_id = -1)
        where mmt.transaction_id = l_txn_update_id;

Line: 5517

    	select transfer_transaction_id
    	into l_txfr_txn_id
    	from mtl_material_transactions mmt
    	where mmt.transaction_id = i_txn_id;

Line: 5524

	insert into mtl_cst_actual_cost_details (
          transaction_id,
          organization_id,
          layer_id,
          cost_element_id,
          level_type,
          transaction_action_id,
          last_update_date,
          last_updated_by,
          creation_date,
          created_by,
          last_update_login,
          request_id,
          program_application_id,
          program_id,
          program_update_date,
          inventory_item_id,
          actual_cost,
          prior_cost,
          new_cost,
          insertion_flag,
          variance_amount,
          user_entered)
  select
          i_txn_id,
          l_std_org,
          -1,
          ctcd.cost_element_id,
          ctcd.level_type,
          i_txn_action_id,
          sysdate,
          i_user_id,
          sysdate,
          i_user_id,
          i_login_id,
          i_req_id,
          i_prg_appl_id,
          i_prg_id,
          sysdate,
          ctcd.inventory_item_id,
          ctcd.transaction_cost,
          NULL,
          NULL,
          'N',
          0,
          'N'
	FROM mtl_cst_txn_cost_details ctcd
	WHERE ctcd.transaction_id = decode(i_txn_org_id, l_std_org, l_txfr_txn_id, l_txn_update_id) -- sending txn id
	AND ctcd.organization_id = l_std_org
	/* AND ctcd.transaction_cost >= 0 */; -- modified for bug#3835412

Line: 5575

	-- update mmt if this is the receiving transaction id
	if (i_txn_org_id = l_std_org) then
		update mtl_material_transactions mmt
		set (last_update_date,
			last_updated_by,
			last_update_login,
			request_id,
			program_application_id,
			program_id,
			program_update_date,
			actual_cost) =
        	(select sysdate,
			i_user_id,
			i_login_id,
			i_req_id,
			i_prg_appl_id,
			i_prg_id,
			sysdate,
			nvl(sum(actual_cost),0)
			from mtl_cst_actual_cost_details cacd
			where cacd.transaction_id = l_txn_update_id
			and cacd.organization_id = l_std_org
			and cacd.layer_id = -1)
		where mmt.transaction_id = l_txn_update_id;

Line: 5665

/* The following FROM clause in the select statement has been commented out
   because we now have to refer CST_ORGANIZATION_DEFINITIONS as a result of the
   impact of the HR-PROFILE option  */

  select set_of_books_id
  into l_snd_sob_id
  /*from org_organization_definitions */
  from cst_organization_definitions
  where organization_id = i_from_org;

Line: 5677

  select currency_code
  into l_snd_curr
  from gl_sets_of_books
  where set_of_books_id = l_snd_sob_id;

Line: 5684

/* The following line in the FROM clause of the select statement has been
   commented out because it will now be refering to cst_organization_definitions   as an impact of the HR-PROFILE option */

  select set_of_books_id
  into l_rcv_sob_id
  /*from org_organization_definitions */
  from cst_organization_definitions
  where organization_id = i_to_org;

Line: 5695

  select currency_code
  into l_rcv_curr
  from gl_sets_of_books
  where set_of_books_id = l_rcv_sob_id;

Line: 5702

  select currency_conversion_type, TRUNC(transaction_date)
  into l_curr_type, l_txn_date
  from mtl_material_transactions
  where transaction_id = i_txn_id;

Line: 5716

    select conversion_rate, conversion_date
    into l_conv_rate, l_conv_date
    from gl_daily_conversion_rates
    where set_of_books_id = l_rcv_sob_id
    and from_currency_code = l_snd_curr
    and conversion_type = l_curr_type
    and conversion_date =
	  (select max(conversion_date)
	   from gl_daily_conversion_rates
	   where set_of_books_id = l_rcv_sob_id
	   and from_currency_code = l_snd_curr
	   and conversion_type = l_curr_type
	   and conversion_date <= l_txn_date);

Line: 5792

  select primary_uom_code
  into o_snd_uom
  from mtl_system_items
  where organization_id = i_from_org
  and inventory_item_id = i_item_id;

Line: 5800

  select primary_uom_code
  into o_rcv_uom
  from mtl_system_items
  where organization_id = i_to_org
  and inventory_item_id = i_item_id;

Line: 5825

  select decode(primary_cost_method,1,1,0)
  into l_ret_val
  from mtl_parameters
  where organization_id = i_org_id;

Line: 5858

       select pbp.borrow_transaction_id,
              pbp.payback_quantity
         from pjm_borrow_paybacks pbp
         where pbp.payback_transaction_id = c_cur_txn_id;

Line: 5866

     select mcacd.transaction_id,
            mcacd.cost_element_id,
            mcacd.level_type,
            mcacd.inventory_item_id,
            mcacd.actual_cost,
            mcacd.prior_cost,
            mcacd.new_cost,
            mcacd.layer_id
       from mtl_cst_actual_cost_details mcacd
       where mcacd.transaction_id = c_transaction_id
       and mcacd.layer_id = i_to_layer;

Line: 5954

            if l_temp_element_cost(l_index_counter) <> 0 then -- if element cost is not 0 then insert into MCTCD
               l_stmt_num := 20;

Line: 5956

               insert into mtl_cst_txn_cost_details(
                            TRANSACTION_ID,
                            ORGANIZATION_ID,
                            COST_ELEMENT_ID,
                            LEVEL_TYPE,
                            LAST_UPDATE_DATE,
                            LAST_UPDATED_BY,
                            CREATION_DATE,
                            CREATED_BY,
                            LAST_UPDATE_LOGIN,
                            REQUEST_ID,
                            PROGRAM_APPLICATION_ID,
                            PROGRAM_ID,
                            PROGRAM_UPDATE_DATE,
                            INVENTORY_ITEM_ID,
                            TRANSACTION_COST)
               values(
                            i_txn_id,
                            i_org_id,
                            decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5)),
                            l_level_type,
                            sysdate,
                            i_user_id,
                            sysdate,
                            i_user_id,
                            i_login_id,
                            i_req_id,
                            i_prg_appl_id,
                            i_prg_id,
                            sysdate,
                            i_item_id,
                            l_temp_element_cost(l_index_counter));

Line: 5991

                 Select count(*) into l_count
                 from mtl_cst_txn_cost_details
                 where transaction_id = i_txn_id;

Line: 5998

                       /* Insert int MCTCD only if cost element exists in MCACD with zero transaction cost */
                        insert into mtl_cst_txn_cost_details(
                               TRANSACTION_ID,
                               ORGANIZATION_ID,
                               COST_ELEMENT_ID,
                               LEVEL_TYPE,
                               LAST_UPDATE_DATE,
                               LAST_UPDATED_BY,
                               CREATION_DATE,
                               CREATED_BY,
                               LAST_UPDATE_LOGIN,
                               REQUEST_ID,
                               PROGRAM_APPLICATION_ID,
                               PROGRAM_ID,
                               PROGRAM_UPDATE_DATE,
                               INVENTORY_ITEM_ID,
                               TRANSACTION_COST)
                        values(
                               i_txn_id,
                               i_org_id,
                               l_cost_element(i),
                               l_level_type,
                               sysdate,
                               i_user_id,
                               sysdate,
                               i_user_id,
                               i_login_id,
                               i_req_id,
                               i_prg_appl_id,
                               i_prg_id,
                               sysdate,
                               i_item_id,
                               0);

Line: 6038

            if l_temp_element_cost(l_index_counter) <> 0 then -- if element cost <>0 then insert update MCACD
               update mtl_cst_actual_cost_details mcacd
                  set mcacd.actual_cost = l_temp_element_cost(l_index_counter)
                  where mcacd.transaction_id = i_txn_id
                  and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
                  and mcacd.level_type = l_level_type
                  and mcacd.layer_id = i_to_layer;

Line: 6045

            else -- if the element cost == 0 then we need to delete MCACD.
               delete from mtl_cst_actual_cost_details mcacd
                where mcacd.transaction_id = i_txn_id
                and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
                and mcacd.level_type = l_level_type
                and mcacd.layer_id = i_to_layer;

Line: 6107

         select max(mcacd.actual_cost)
           into l_cur_cost
           from mtl_cst_actual_cost_details mcacd
           where mcacd.level_type = l_level_type
           and mcacd.cost_element_id = l_ce
           and mcacd.transaction_id = i_txn_id
           and mcacd.layer_id = i_from_layer_id;

Line: 6115

         select max(mcacd.actual_cost)
           into l_borrowed_cost
           from mtl_cst_actual_cost_details mcacd
           where mcacd.level_type = l_level_type
           and mcacd.cost_element_id = l_ce
           and mcacd.transaction_id = i_txn_id
           and mcacd.layer_id = i_to_layer_id;

Line: 6127

         update mtl_cst_actual_cost_details mcacd
            set mcacd.payback_variance_amount = l_variance
            where mcacd.transaction_id = i_txn_id
            and mcacd.cost_element_id = l_ce
            and mcacd.level_type = l_level_type
            and mcacd.layer_id = i_from_layer_id;

Line: 6155

  i_txn_update_id	IN NUMBER,
  i_from_org		IN NUMBER,
  i_to_org		IN NUMBER,
  i_snd_qty		IN NUMBER,
  i_txfr_cost		IN NUMBER,
  i_trans_cost		IN NUMBER,
  i_conv_rate		IN NUMBER,
  i_um_rate		IN NUMBER,
  i_user_id		IN NUMBER,
  i_login_id		IN NUMBER,
  i_req_id		IN NUMBER,
  i_prg_appl_id		IN NUMBER,
  i_prg_id		IN NUMBER,
  i_hook_used		IN NUMBER := 0,
  o_err_num		OUT NOCOPY NUMBER,
  o_err_code		OUT NOCOPY VARCHAR2,
  o_err_msg		OUT NOCOPY VARCHAR2)
IS
  l_err_num     NUMBER;

Line: 6211

    select cost_organization_id
      into l_from_cost_org
      from mtl_parameters
     where organization_id = i_from_org;

Line: 6216

    insert into mtl_cst_txn_cost_details (
      transaction_id,
      organization_id,
      cost_element_id,
      level_type,
      last_update_date,
      last_updated_by,
      creation_date,
      created_by,
      last_update_login,
      request_id,
      program_application_id,
      program_id,
      program_update_date,
      inventory_item_id,
      transaction_cost,
      new_average_cost,
      percentage_change,
      value_change)
    select
      i_txn_update_id,
      i_to_org,
      cost_element_id,
      level_type,
      sysdate,
      i_user_id,
      sysdate,
      i_user_id,
      i_login_id,
      i_req_id,
      i_prg_appl_id,
      i_prg_id,
      sysdate,
      i_item_id,
      sum(item_cost)*i_conv_rate/i_um_rate,
      0,
      0,
      0
    from cst_item_cost_details cicd
    where cicd.cost_type_id = i_cost_type_id
      and cicd.inventory_item_id = i_item_id
      /* and cicd.organization_id = i_from_org : bugfix 3048258 */
      and cicd.organization_id = l_from_cost_org
    group by cicd.cost_element_id,cicd.level_type;

Line: 6265

    		insert into mtl_cst_txn_cost_details (
      		transaction_id,
      		organization_id,
      		cost_element_id,
     		level_type,
      		last_update_date,
      		last_updated_by,
      		creation_date,
      		created_by,
      		last_update_login,
      		request_id,
      		program_application_id,
      		program_id,
      		program_update_date,
      		inventory_item_id,
      		transaction_cost,
      		new_average_cost,
      		percentage_change,
      		value_change)
    		select
      		i_txn_update_id,
      		i_to_org,
     		cost_element_id,
      		level_type,
      		sysdate,
      		i_user_id,
      		sysdate,
      		i_user_id,
      		i_login_id,
      		i_req_id,
      		i_prg_appl_id,
     		i_prg_id,
      		sysdate,
      		i_item_id,
      		clcd.item_cost*i_conv_rate/i_um_rate,
      		0,
      		0,
      		0
    		from cst_layer_cost_details clcd
    		where clcd.layer_id = i_from_layer_id;

Line: 6307

    			fnd_file.put_line(fnd_file.log, '>>>>Hook has been used, inserting in MCTCD from MCACD');

Line: 6309

    		insert into mtl_cst_txn_cost_details (
    		transaction_id,
     		organization_id,
     		cost_element_id,
     		level_type,
     		last_update_date,
      		last_updated_by,
      		creation_date,
      		created_by,
      		last_update_login,
      		request_id,
      		program_application_id,
      		program_id,
     		program_update_date,
      		inventory_item_id,
      		transaction_cost,
     		new_average_cost,
     		percentage_change,
      		value_change)
    		select
      		i_txn_update_id,
      		i_to_org,
      		cost_element_id,
      		level_type,
      		sysdate,
      		i_user_id,
      		sysdate,
      		i_user_id,
      		i_login_id,
      		i_req_id,
      		i_prg_appl_id,
      		i_prg_id,
      		sysdate,
      		i_item_id,
      		mcacd.actual_cost*i_conv_rate/i_um_rate,
      		0,
      		0,
      		0
    		from mtl_cst_actual_cost_details mcacd
    		where mcacd.layer_id = i_from_layer_id
		and mcacd.transaction_id = i_txn_id;

Line: 6357

	insert into mtl_cst_txn_cost_details (
	      transaction_id,
	      organization_id,
	      cost_element_id,
	      level_type,
	      last_update_date,
	      last_updated_by,
	      creation_date,
	      created_by,
	      last_update_login,
	      request_id,
	      program_application_id,
	      program_id,
	      program_update_date,
	      inventory_item_id,
	      transaction_cost,
	      new_average_cost,
	      percentage_change,
	      value_change)
	  values (
	      i_txn_update_id,
	      i_to_org,
	      1,
	      1,
	      sysdate,
	      i_user_id,
	      sysdate,
	      i_user_id,
	      i_login_id,
	      i_req_id,
	      i_prg_appl_id,
	      i_prg_id,
	      sysdate,
	      i_item_id,
	      0,
	      0,
	      0,
	      0);

Line: 6399

  select count(*)
  into l_movh_cnt
  from mtl_cst_txn_cost_details mctcd
  where mctcd.transaction_id = i_txn_update_id
    and mctcd.organization_id = i_to_org
    and mctcd.inventory_item_id = i_item_id
    and mctcd.level_type = 1
    and mctcd.cost_element_id = 2;

Line: 6410

    select NVL(mctcd.transaction_cost,0)
    into l_rcv_movh
    from mtl_cst_txn_cost_details mctcd
    where mctcd.transaction_id = i_txn_update_id
      and mctcd.organization_id = i_to_org
      and mctcd.inventory_item_id = i_item_id
      and mctcd.level_type = 1
      and mctcd.cost_element_id = 2;

Line: 6442

    update mtl_cst_txn_cost_details mctcd
    set mctcd.transaction_cost = l_rcv_movh
    where mctcd.transaction_id = i_txn_update_id
      and mctcd.organization_id = i_to_org
      and mctcd.inventory_item_id = i_item_id
      and mctcd.level_type = 1
      and mctcd.cost_element_id = 2;

Line: 6451

    insert into mtl_cst_txn_cost_details (
      transaction_id,
      organization_id,
      cost_element_id,
      level_type,
      last_update_date,
      last_updated_by,
      creation_date,
      created_by,
      last_update_login,
      request_id,
      program_application_id,
      program_id,
      program_update_date,
      inventory_item_id,
      transaction_cost,
      new_average_cost,
      percentage_change,
      value_change)
    values (
      i_txn_update_id,
      i_to_org,
      2,
      1,
      sysdate,
      i_user_id,
      sysdate,
      i_user_id,
      i_login_id,
      i_req_id,
      i_prg_appl_id,
      i_prg_id,
      sysdate,
      i_item_id,
      l_rcv_movh,
      0,
      0,
      0);

Line: 6581

    SELECT mmt.TRANSACTION_ID "TRANSACTION_ID",
           mmt.PRIMARY_QUANTITY "PRIMARY_QUANTITY",
           mmt.TRANSACTION_TYPE_ID "TRANSACTION_TYPE_ID",
           mmt.TRANSACTION_ACTION_ID "TRANSACTION_ACTION_ID",
           mmt.TRANSACTION_SOURCE_TYPE_ID "TRANSACTION_SOURCE_TYPE_ID",
           mmt.ORGANIZATION_ID "ORGANIZATION_ID",
           mmt.TRANSFER_ORGANIZATION_ID "TRANSFER_ORGANIZATION_ID",
           mmt.TRANSACTION_DATE "TRANSACTION_DATE",
           mmt.INVENTORY_ITEM_ID "INVENTORY_ITEM_ID",
           mmt.SUBINVENTORY_CODE "SUBINVENTORY_CODE",
           NVL(mmt.TRANSFER_COST_GROUP_ID,-1) "TRANSFER_COST_GROUP_ID",
           NVL(mmt.COST_GROUP_ID,mp.DEFAULT_COST_GROUP_ID) "COST_GROUP_ID",
           mmt.COSTED_FLAG "COSTED_FLAG",
           mmt.ACCT_PERIOD_ID "ACCT_PERIOD_ID",
           NVL(mmt.PARENT_TRANSACTION_ID, mmt.transaction_id) "PARENT_ID",
           mmt.transaction_quantity "TRANSACTION_QUANTITY",
           NVL(mmt.LOGICAL_TRX_TYPE_CODE, -1) "DROP_SHIP_FLAG",
           NVL(mmt.logical_transaction, 3) "LOGICAL_TRANSACTION",
           mp.primary_cost_method "PRIMARY_COST_METHOD",
           mp.cost_organization_id "COST_ORGANIZATION_ID",
           NVL(mmt.DISTRIBUTION_ACCOUNT_ID, -1) "DISTRIBUTION_ACCOUNT_ID",
           mp.primary_cost_method "COST_TYPE_ID", /* For use as cost_type_id */
           NVL(mp.AVG_RATES_COST_TYPE_ID, -1) "AVG_RATES_COST_TYPE_ID",
           decode(msi.INVENTORY_ASSET_FLAG,'Y',0,1) "EXP_ITEM"
    FROM   mtl_material_transactions mmt,
           mtl_parameters mp,
           mtl_system_items_b msi
    WHERE  mmt.organization_id = mp.organization_id
    AND    mmt.inventory_item_id = msi.inventory_item_id
    AND    mmt.organization_id = msi.organization_id
    AND    (( p_parent_id is not null and
                    mmt.parent_transaction_id = p_parent_id)
            or (p_parent_id is null and p_txn_id is not null and
                mmt.transaction_id = p_txn_id))
    ORDER BY mmt.transaction_id;

Line: 6692

      SELECT decode(INVENTORY_ASSET_FLAG,'Y',0,1)
        INTO   l_exp_item
      FROM   MTL_SYSTEM_ITEMS
      WHERE  INVENTORY_ITEM_ID = c_mmt_txn_rec.inventory_item_id
      AND    ORGANIZATION_ID = c_mmt_txn_rec.organization_id;

Line: 6703

        SELECT decode(l_exp_item,1,1,decode(ASSET_INVENTORY,1,0,1))
        INTO   l_exp_flag
        FROM   MTL_SECONDARY_INVENTORIES
        WHERE  SECONDARY_INVENTORY_NAME = c_mmt_txn_rec.subinventory_code
        AND    ORGANIZATION_ID = c_mmt_txn_rec.organization_id;

Line: 6723

        select organization_id, decode(nvl(logical_transaction, 2), 1, 0, 1)
        into l_parent_organization_id, l_parent_transaction_type
        from mtl_material_transactions
        where transaction_id = p_parent_id;

Line: 6754

      select count(*)
      into l_mctcd_count
      from mtl_cst_txn_cost_details
      where transaction_id = c_mmt_txn_rec.transaction_id;

Line: 6836

          insert into mtl_cst_actual_cost_details (
                             transaction_id,
                             organization_id,
                             layer_id,
                             cost_element_id,
                             level_type,
                             transaction_action_id,
                             last_update_date,
                             last_updated_by,
                             creation_date,
                             created_by,
                             last_update_login,
                             request_id,
                             program_application_id,
                             program_id,
                             program_update_date,
                             inventory_item_id,
                             actual_cost,
                             prior_cost,
                             new_cost,
                             insertion_flag,
                             variance_amount,
                             user_entered)
                             select
                             c_mmt_txn_rec.transaction_id,
                             c_mmt_txn_rec.organization_id,
                             l_layer_id,
                             1,
                             1,
                             c_mmt_txn_rec.transaction_action_id,
                             sysdate,
                             p_user_id,
                             sysdate,
                             p_user_id,
                             p_login_id,
                             p_request_id,
                             p_prog_app_id,
                             p_prog_id,
                             sysdate,
                             c_mmt_txn_rec.inventory_item_id,
                             decode(c_mmt_txn_rec.transaction_type_id, 20, ctcd.value_change,ctcd.transaction_cost),
                             NULL,
                             NULL,
                             'N',
                             0,
                             'N'
          FROM mtl_cst_txn_cost_details ctcd
          WHERE transaction_id = c_mmt_txn_rec.transaction_id;

Line: 6886

                fnd_file.put_line (fnd_file.log, 'Inserted in mcacd:' || sql%rowcount);

Line: 6889

          /* Update MMT */

          l_stmt_num := 42;

Line: 6893

          Update mtl_material_transactions mmt
            set (last_update_date,
	               last_updated_by,
	               last_update_login,
	               request_id,
	               program_application_id,
	               program_id,
	               program_update_date,
 	               actual_cost,
	               variance_amount) =
            ( select sysdate,
	               p_user_id,
	               p_login_id,
	               p_request_id,
	               p_prog_app_id,
	               p_prog_id,
	               sysdate,
                 sum(nvl(actual_cost,0)),
	               sum(nvl(variance_amount,0))
             from mtl_cst_actual_cost_details cacd
             where cacd.transaction_id = c_mmt_txn_rec.transaction_id)
             where mmt.transaction_id = c_mmt_txn_rec.transaction_id;

Line: 6984

  /* Update Costed Flag */
  l_stmt_num := 70;

Line: 6988

  update mtl_material_transactions
  set costed_flag = NULL
  WHERE parent_transaction_id = p_parent_id;

Line: 6992

  update mtl_material_transactions
  set costed_flag = NULL
  WHERE transaction_id = p_txn_id ;

Line: 7009

/* Modified update statement for performance reasons. See bug#3585779*/
if (p_parent_id is null) then
update mtl_material_transactions
set costed_flag =  'E',
error_code = x_err_code,
error_explanation = x_err_msg
 where  (p_txn_id is not null
         and transaction_id = p_txn_id);

Line: 7021

    update mtl_material_transactions
    set costed_flag = decode(transaction_id, p_parent_id, 'E', 'N'),
    error_code = x_err_code,
    error_explanation = x_err_msg
    where parent_transaction_id  = p_parent_id or
    (transaction_id = p_parent_id and parent_transaction_id is null) or
    (transaction_id = p_txn_id);

Line: 7029

    update mtl_material_transactions
    set costed_flag = decode(transaction_id, p_parent_id, 'E', 'N'),
    error_code = x_err_code,
    error_explanation = x_err_msg
    where parent_transaction_id  = p_parent_id or
    (transaction_id = p_parent_id and parent_transaction_id is null);

Line: 7044

/* Modified update statement for performance reasons. See bug#3585779*/
if (p_parent_id is null) then
update mtl_material_transactions
set costed_flag =  'E',
error_code = x_err_code,
error_explanation = x_err_msg,
request_id = p_request_id
 where  (p_txn_id is not null
         and transaction_id = p_txn_id);

Line: 7057

   update mtl_material_transactions
   set costed_flag = decode(transaction_id, p_parent_id, 'E', 'N'),
   error_code = x_err_code,
   error_explanation = x_err_msg,
   request_id = p_request_id
   where parent_transaction_id  = p_parent_id or
   (transaction_id = p_parent_id and parent_transaction_id is null) or
   (transaction_id = p_txn_id);

Line: 7066

   update mtl_material_transactions
   set costed_flag = decode(transaction_id, p_parent_id, 'E', 'N'),
   error_code = x_err_code,
   error_explanation = x_err_msg,
   request_id = p_request_id
   where parent_transaction_id  = p_parent_id or
   (transaction_id = p_parent_id and parent_transaction_id is null);

Line: 7194

       select count(*)
       into l_count
       from cst_item_cost_details
       where inventory_item_id = p_item_id
       and organization_id = p_org_id
       and cost_type_id = 1;

Line: 7202

       /* Insert into MCACD using 0 cost for This Level Material */

          insert into mtl_cst_actual_cost_details (
          transaction_id,
          organization_id,
          layer_id,
          cost_element_id,
          level_type,
          transaction_action_id,
          last_update_date,
          last_updated_by,
          creation_date,
          created_by,
          last_update_login,
          request_id,
          program_application_id,
          program_id,
          program_update_date,
          inventory_item_id,
          actual_cost,
          insertion_flag,
          variance_amount,
          user_entered)
          values ( p_txn_id,
          p_org_id,
          -1,
          1,
          1,
          p_txn_action_id,
          sysdate,
          p_user_id,
          sysdate,
          p_user_id,
          p_login_id,
          p_request_id,
          p_prog_app_id,
          p_prog_id,
          sysdate,
          p_item_id,
          0,
          'N',
          0,
          'N');

Line: 7247

     /* Insert into MCACD cost details from CICD */
      l_stmt_num := 30;

Line: 7249

           insert into mtl_cst_actual_cost_details (
           transaction_id,
           organization_id,
           layer_id,
           cost_element_id,
           level_type,
           transaction_action_id,
           last_update_date,
           last_updated_by,
           creation_date,
           created_by,
           last_update_login,
           request_id,
           program_application_id,
           program_id,
           program_update_date,
           inventory_item_id,
           actual_cost,
           insertion_flag,
           variance_amount,
           user_entered)
           select p_txn_id,
           p_org_id,
           -1,
           cicd.cost_element_id,
           cicd.level_type,
           p_txn_action_id,
           sysdate,
           p_user_id,
           sysdate,
           p_user_id,
           p_login_id,
           p_request_id,
           p_prog_app_id,
           p_prog_id,
           sysdate,
           p_item_id,
           nvl(sum(cicd.item_cost),0),
           'N',
           0,
           'N'
           from cst_item_cost_details cicd
           where organization_id = p_org_id
           and inventory_item_id = p_item_id
           and cost_type_id = 1
           group by cost_element_id, level_type;

Line: 7305

       select count(*)
       into l_count
       from cst_item_cost_details
       where inventory_item_id = p_item_id
       and organization_id = p_cost_org_id
       and cost_type_id = 1;

Line: 7313

       /* Insert into MCACD using 0 cost for This Level Material */

          insert into mtl_cst_actual_cost_details (
          transaction_id,
          organization_id,
          layer_id,
          cost_element_id,
          level_type,
          transaction_action_id,
          last_update_date,
          last_updated_by,
          creation_date,
          created_by,
          last_update_login,
          request_id,
          program_application_id,
          program_id,
          program_update_date,
          inventory_item_id,
          actual_cost,
          insertion_flag,
          variance_amount,
          user_entered)
          values ( p_txn_id,
          p_org_id,
          -1,
          1,
          1,
          p_txn_action_id,
          sysdate,
          p_user_id,
          sysdate,
          p_user_id,
          p_login_id,
          p_request_id,
          p_prog_app_id,
          p_prog_id,
          sysdate,
          p_item_id,
          0,
          'N',
          0,
          'N');

Line: 7358

     /* Insert into MCACD cost details from CICD */
      l_stmt_num := 38;

Line: 7360

           insert into mtl_cst_actual_cost_details (
           transaction_id,
           organization_id,
           layer_id,
           cost_element_id,
           level_type,
           transaction_action_id,
           last_update_date,
           last_updated_by,
           creation_date,
           created_by,
           last_update_login,
           request_id,
           program_application_id,
           program_id,
           program_update_date,
           inventory_item_id,
           actual_cost,
           insertion_flag,
           variance_amount,
           user_entered)
           select p_txn_id,
           p_org_id,
           -1,
           cicd.cost_element_id,
           cicd.level_type,
           p_txn_action_id,
           sysdate,
           p_user_id,
           sysdate,
           p_user_id,
           p_login_id,
           p_request_id,
           p_prog_app_id,
           p_prog_id,
           sysdate,
           p_item_id,
           nvl(sum(cicd.item_cost),0),
           'N',
           0,
           'N'
           from cst_item_cost_details cicd
           where organization_id = p_cost_org_id
           and inventory_item_id = p_item_id
           and cost_type_id = 1
           group by cost_element_id, level_type;

Line: 7557

                                0,--update mmt flag
                                0,
                                p_user_id,
                                p_login_id,
                                p_request_id,
                                p_prog_app_id,
                                p_prog_id,
                                l_error_num,
                                l_err_code,
                                l_err_msg);

Line: 7644

    Update mtl_material_transactions mmt
    set (last_update_date,
	 last_updated_by,
	 last_update_login,
	 request_id,
	 program_application_id,
	 program_id,
	 program_update_date,
 	 actual_cost,
	 prior_cost,
 	 new_cost,
	 variance_amount,
	 prior_costed_quantity,
	 quantity_adjusted) =
    (select sysdate,
	    p_user_id,
	    p_login_id,
	    p_request_id,
	    p_prog_app_id,
	    p_prog_id,
	    sysdate,
	    mmt2.actual_cost,
	    mmt2.prior_cost,
	    mmt2.new_cost,
	    mmt2.variance_amount,
	    mmt2.prior_costed_quantity,
	    mmt2.quantity_adjusted
      from mtl_material_transactions mmt2
      where mmt2.transaction_id = p_txn_id
      and mmt2.organization_id = p_org_id)
    where mmt.transaction_id = p_parent_txn_id;

Line: 7738

       select pbp.borrow_transaction_id,
              pbp.payback_quantity
         from pjm_borrow_paybacks pbp
         where pbp.payback_transaction_id = c_cur_txn_id;

Line: 7746

     select mcacd.transaction_id,
            mcacd.cost_element_id,
            mcacd.level_type,
            mcacd.inventory_item_id,
            mcacd.actual_cost,
            mcacd.prior_cost,
            mcacd.new_cost,
            mcacd.layer_id
       from mtl_cst_actual_cost_details mcacd
       where mcacd.transaction_id = c_transaction_id
       and mcacd.layer_id = i_from_layer;

Line: 7827

         select max(mcacd.actual_cost)
           into l_cur_cost
           from mtl_cst_actual_cost_details mcacd
           where mcacd.level_type = l_level_type
           and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
           and mcacd.transaction_id = i_txn_id
           and mcacd.layer_id = i_from_layer;

Line: 7837

           select   l_temp_element_cost(l_index_counter)
           into l_borrowed_cost
           from dual;

Line: 7843

          update mtl_cst_actual_cost_details mcacd
          set mcacd.payback_variance_amount = l_variance
          where mcacd.transaction_id = i_txn_id
          and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
          and mcacd.level_type = l_level_type
          and mcacd.layer_id = i_from_layer;

Line: 7948

INSERT_ACCT_ERROR       EXCEPTION;

Line: 7949

INSERT_MCACD_ERROR      EXCEPTION;

Line: 7958

SELECT
  DECODE(asset_inventory,1,0,1)
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = c_subinventory_code
  AND organization_id = c_organization_id;

Line: 7997

  SELECT
    mmt.inventory_item_id,
    mmt.subinventory_code,
    mmt.transfer_transaction_id,
    mmt.organization_id,
    mp.process_enabled_flag,
    mmt.transfer_organization_id,
    mpx.process_enabled_flag
  INTO
    l_item_id,
    l_subinventory_code,
    l_logical_txn_id,
    l_parent_org_id,
    l_parent_org_process_flag,
    l_logical_org_id,
    l_logical_org_process_flag
  FROM
    mtl_material_transactions mmt, mtl_parameters mpx, mtl_parameters mp
  WHERE
      mmt.transaction_id  = p_parent_txn_id
  AND mpx.organization_id = mmt.transfer_organization_id
  AND mp.organization_id  = mmt.organization_id
  ;

Line: 8065

  SELECT INVENTORY_ASSET_FLAG
  INTO   l_exp_item
  FROM   MTL_SYSTEM_ITEMS
  WHERE  INVENTORY_ITEM_ID = l_item_id
  AND    ORGANIZATION_ID   = l_parent_org_id;

Line: 8076

    SELECT decode(ASSET_INVENTORY, 1, 'N', 'Y')
    INTO   l_exp_flag
    FROM   MTL_SECONDARY_INVENTORIES
    WHERE  SECONDARY_INVENTORY_NAME = l_subinventory_code
    AND    ORGANIZATION_ID          = l_parent_org_id;

Line: 8095

    UPDATE
      MTL_MATERIAL_TRANSACTIONS
    SET
      COSTED_FLAG            = NULL,
      LAST_UPDATE_DATE       = sysdate,
      LAST_UPDATED_BY        = p_user_id,
      LAST_UPDATE_LOGIN      = p_login_id,
      REQUEST_ID             = p_request_id,
      PROGRAM_APPLICATION_ID = p_prog_app_id,
      PROGRAM_ID             = p_prog_id
    WHERE
      TRANSACTION_ID = l_logical_txn_id;

Line: 8117

  SELECT
    PRIMARY_QUANTITY,
    TRANSACTION_DATE,
    TRANSACTION_SOURCE_ID,
    TRANSACTION_SOURCE_TYPE_ID,
    TRANSACTION_TYPE_ID,
    TRANSACTION_ACTION_ID,
    CURRENCY_CODE,
    CURRENCY_CONVERSION_RATE,
    CURRENCY_CONVERSION_DATE,
    CURRENCY_CONVERSION_TYPE,
    DISTRIBUTION_ACCOUNT_ID,
    COST_GROUP_ID,
    TRANSFER_PRICE  -- Bug 5349860: umoogala
  INTO
    l_primary_quantity,
    l_txn_date,
    l_txn_src_id,
    l_txn_src_typ_id,
    l_txn_typ_id,
    l_txn_act_id,
    l_alt_curr,
    l_curr_conv_rate,
    l_curr_conv_date,
    l_curr_conv_type,
    l_debit_account,
    l_logical_cost_group_id,
    l_transfer_price  -- Bug 5349860: umoogala
  FROM
    MTL_MATERIAL_TRANSACTIONS
  WHERE
    TRANSACTION_ID = l_logical_txn_id;

Line: 8217

  SELECT
    PRIMARY_COST_METHOD
  INTO
    l_cost_method
  FROM
    MTL_PARAMETERS
  WHERE
    organization_id = l_logical_org_id;

Line: 8279

    SELECT SUM(ACTUAL_COST)
    INTO   l_actual_cost
    FROM   MTL_CST_ACTUAL_COST_DETAILS
    WHERE  transaction_id = p_parent_txn_id;

Line: 8290

  SELECT CURRENCY_CODE,
         SET_OF_BOOKS_ID,
	 OPERATING_UNIT
  INTO   l_pri_curr,
         l_set_of_books_id,
	 l_ou_id
  FROM   CST_ORGANIZATION_DEFINITIONS
  WHERE  ORGANIZATION_ID = l_logical_org_id;

Line: 8327

      SELECT CURRENCY_CODE
      INTO   l_sending_curr
      FROM   CST_ORGANIZATION_DEFINITIONS
      WHERE  ORGANIZATION_ID = l_parent_org_id;

Line: 8398

        SELECT
          INTERORG_PAYABLES_ACCOUNT
        INTO
          l_credit_account
        FROM
          MTL_INTERORG_PARAMETERS
        WHERE
          FROM_ORGANIZATION_ID = l_parent_org_id
        AND TO_ORGANIZATION_ID = l_logical_org_id;

Line: 8428

    INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
      transaction_id,
      organization_id,
      layer_id,
      cost_element_id,
      level_type,
      transaction_action_id,
      last_update_date,
      last_updated_by,
      creation_date,
      created_by,
      last_update_login,
      request_id,
      program_application_id,
      program_id,
      program_update_date,
      inventory_item_id,
      actual_cost,
      insertion_flag,
      variance_amount,
      user_entered )
    VALUES (
      l_logical_txn_id,
      l_logical_org_id,
      l_layer_id,
      1, -- All Costs into MTL
      1, -- Level: TL
      decode(l_pd_xfer_ind, 'Y', 17, 1), -- Bug 5349860: umoogala
      sysdate,
      p_user_id,
      sysdate,
      p_user_id,
      p_login_id,
      p_request_id,
      p_prog_app_id,
      p_prog_id,
      sysdate,
      l_item_id,
      l_actual_cost,
      'N',
      0,
      'N');

Line: 8472

      RAISE INSERT_MCACD_ERROR;

Line: 8482

  CSTPACDP.INSERT_ACCOUNT(
             l_logical_org_id,
             l_logical_txn_id,
             l_item_id,
             l_actual_cost * l_primary_quantity,
             l_primary_quantity,
             l_debit_account,
             l_set_of_books_id,
             l_dr_acct_line_type,  -- Accounting_Line_Type
             1,                    -- Cost_Element
             NULL,                 -- Resource_Id
             l_txn_date,
             l_txn_src_id,
             l_txn_src_typ_id,
             l_pri_curr,
             l_alt_curr,
             l_curr_conv_date,
             l_curr_conv_rate,
             l_curr_conv_type,
             1,
             p_user_id,
             p_login_id,
             p_request_id,
             p_prog_app_id,
             p_prog_id,
             l_err_num,
             l_err_code,
             l_err_msg );

Line: 8512

    RAISE INSERT_ACCT_ERROR;

Line: 8522

  CSTPACDP.INSERT_ACCOUNT(
             l_logical_org_id,
             l_logical_txn_id,
             l_item_id,
             -1 * l_actual_cost * l_primary_quantity,
             -1 * l_primary_quantity,
             l_credit_account,
             l_set_of_books_id,
             l_cr_acct_line_type,  -- Accounting_Line_Type
             1,                    -- Cost_Element
             NULL,                 -- Resource_Id
             l_txn_date,
             l_txn_src_id,
             l_txn_src_typ_id,
             l_pri_curr,
             l_alt_curr,
             l_curr_conv_date,
             l_curr_conv_rate,
             l_curr_conv_type,
             1,               -- Actual_Flag
             p_user_id,
             p_login_id,
             p_request_id,
             p_prog_app_id,
             p_prog_id,
             l_err_num,
             l_err_code,
             l_err_msg );

Line: 8552

    RAISE INSERT_ACCT_ERROR;

Line: 8560

  SELECT nvl(req_encumbrance_flag,'N') /*nvl(purch_encumbrance_flag, 'N')Bug 6469694*/
  INTO   l_encumbrance_flag
  FROM   FINANCIALS_SYSTEM_PARAMS_ALL
  WHERE  set_of_books_id = l_set_of_books_id
  AND    ( ( l_ou_id is not NULL AND org_id = l_ou_id ) OR
           ( l_ou_id is null ) );

Line: 8609

        RAISE INSERT_ACCT_ERROR;

Line: 8616

  UPDATE
    MTL_MATERIAL_TRANSACTIONS
  SET
    COSTED_FLAG            = NULL,
    transaction_group_id   = NULL,
    ENCUMBRANCE_AMOUNT     = l_enc_amount,
    ENCUMBRANCE_ACCOUNT    = l_enc_account,
    LAST_UPDATE_DATE       = sysdate,
    LAST_UPDATED_BY        = p_user_id,
    LAST_UPDATE_LOGIN      = p_login_id,
    REQUEST_ID             = p_request_id,
    PROGRAM_APPLICATION_ID = p_prog_app_id,
    PROGRAM_ID             = p_prog_id
  WHERE
    TRANSACTION_ID = l_logical_txn_id;

Line: 8701

  WHEN INSERT_MCACD_ERROR THEN
    x_err_num := -1;

Line: 8703

    x_err_code := 'CSTPAVCP.Cost_LogicalSOReceipt( '||to_char(l_stmt_num)||' ): '||'Error Inserting into MCACD: '||SQLERRM;

Line: 8706

  WHEN INSERT_ACCT_ERROR THEN
    x_err_num := -1;

Line: 8708

    x_err_code := 'CSTPAVCP.Cost_LogicalSOReceipt( '||to_char(l_stmt_num)||' ): '||'Error in Insert_Account: ';

Line: 8721

    UPDATE mtl_material_transactions
    SET    costed_flag = 'E',
           error_code = substrb(x_err_code,1,240),
           error_explanation = substrb(x_err_msg,1,240),
           request_id = p_request_id,
           program_application_id = p_prog_app_id,
           program_id = p_prog_id,
           program_update_date = sysdate
    WHERE  transaction_id = l_trx_info.TRANSACTION_ID;

Line: 8767

  O_NO_UPDATE_MMT IN OUT NOCOPY	NUMBER,
  O_EXP_FLAG	IN OUT NOCOPY	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: 8778

  l_txn_update_id NUMBER;

Line: 8796

  l_update_std		NUMBER;

Line: 8837

  l_update_std := 0;

Line: 8921

      UPDATE mtl_material_transactions mmt
      SET TRANSFER_PRIOR_COSTED_QUANTITY =
      (SELECT
              layer_quantity
      FROM	cst_quantity_layers cql
      WHERE	cql.organization_id = l_which_org
      AND	cql.inventory_item_id = i_item_id
      AND	cql.cost_group_id = l_which_cst_grp)
      WHERE mmt.transaction_id = i_txn_id
      AND EXISTS (
                      SELECT 'X'
                      FROM cst_quantity_layers cql
                      WHERE cql.organization_id = l_which_org
                      AND   cql.inventory_item_id = i_item_id
                      AND   cql.cost_group_id = l_which_cst_grp);

Line: 8939

        update mtl_material_transactions mmt
               set TRANSFER_PRIOR_COSTED_QUANTITY = 0
        where  mmt.transaction_id = i_txn_id;

Line: 8959

   SELECT nvl(transportation_cost,0)
     INTO l_trans_cost
     FROM mtl_material_transactions
    WHERE transaction_id = i_txn_id;

Line: 9000

     select item_cost, layer_id
       into l_snd_txn_cost, l_from_layer_id
       from cst_quantity_layers
      where organization_id = l_from_org
        and inventory_item_id = i_item_id
        and cost_group_id = l_from_cost_grp;

Line: 9014

      Update mtl_material_transactions
      Set transaction_cost = l_new_txn_cost
      where transaction_id = i_txn_id;

Line: 9026

      Update mtl_material_transactions
      Set transaction_cost = l_rcv_txn_cost
      where transaction_id = i_txn_id;

Line: 9036

      fnd_file.put_line(fnd_file.log, 'inserting to MCTCD for txn: ' || i_txn_id || '. trxCost: ' || l_rcv_txn_cost);

Line: 9040

   insert into mtl_cst_txn_cost_details (
   	      transaction_id,
   	      organization_id,
	      cost_element_id,
	      level_type,
	      last_update_date,
	      last_updated_by,
	      creation_date,
	      created_by,
	      last_update_login,
	      request_id,
	      program_application_id,
	      program_id,
	      program_update_date,
	      inventory_item_id,
	      transaction_cost,
	      new_average_cost,
	      percentage_change,
	      value_change)
          values (i_txn_id,
	      l_to_org,
	      1,
  	      1,
	      sysdate,
      	      i_user_id,
      	      sysdate,
      	      i_user_id,
      	      i_login_id,
      	      i_req_id,
      	      i_prg_appl_id,
      	      i_prg_id,
      	      sysdate,
      	      i_item_id,
      	      l_rcv_txn_cost,
	      0,
	      0,
	      0);

Line: 9080

    Update mtl_material_transactions
    Set transaction_cost = l_rcv_txn_cost
    where transaction_id = i_txn_id;

Line: 9090

     * Bug 5631478: unique constraint violation because of this insert.
     * Same insert is being done in compute_actual_cost procedure
     *
    IF g_debug = 'Y' THEN
      fnd_file.put_line(fnd_file.log, 'inserting to MCACD for IO Issue to exp txn');

Line: 9097

    insert into mtl_cst_actual_cost_details(
    transaction_id,
    organization_id,
    layer_id,
    cost_element_id,
    level_type,
    transaction_action_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    last_update_login,
    request_id,
    program_application_id,
    program_id,
    program_update_date,
    inventory_item_id,
    actual_cost,
    prior_cost,
    new_cost,
    insertion_flag,
    variance_amount,
    user_entered)
    select
    i_txn_id,
    i_org_id,
    i_layer_id,
    clcd.cost_element_id,
    clcd.level_type,
    i_txn_action_id,
    sysdate,
    i_user_id,
    sysdate,
    i_user_id,
    i_login_id,
    i_req_id,
    i_prg_appl_id,
    i_prg_id,
    sysdate,
    i_item_id,
    clcd.item_cost,
    clcd.item_cost,
    clcd.item_cost,
    'N',
    0,
    'N'
    from cst_layer_cost_details clcd
    where layer_id = i_layer_id;

Line: 9222

  SELECT um.uom_code,
         rl.quantity,
         rl.unit_price,
         rd.budget_account_id,
	 nvl(rd.nonrecoverable_tax,0) /* Bug 6405593 */
  INTO   l_doc_uom_code,
         l_doc_line_qty,
         l_unit_price,
         x_encumbrance_account,
         l_non_recoverable_tax  /* Bug 6405593 */
  FROM   po_req_distributions_all rd,
         po_requisition_lines_all rl,
         mtl_units_of_measure um
  WHERE  rd.requisition_line_id   = p_req_line_id
  and    rd.requisition_line_id   = rl.requisition_line_id
  and    rl.UNIT_MEAS_LOOKUP_CODE = um.unit_of_measure;

Line: 9245

  SELECT primary_uom_code
  INTO   l_primary_uom_code
  FROM   mtl_system_items
  WHERE  organization_id   = p_organization_id
  AND    inventory_item_id = p_item_id;

Line: 9407

  SELECT
    mmt.trx_source_line_id,
    mmt.primary_quantity,
    mmt.organization_id,
    mmt.inventory_item_id,
    mmt.transaction_action_id,
    mmt.transaction_source_type_id,
    mmt.transaction_type_id,
    mmt.rcv_transaction_id
  INTO
    l_trx_source_line_id,
    l_primary_qty,
    l_organization_id,
    l_item_id,
    l_txn_action_id,
    l_txn_src_type_id,
    l_txn_type_id,
    l_rcv_txn_id
  FROM
    MTL_MATERIAL_TRANSACTIONS mmt
  WHERE
    transaction_id = p_transaction_id;

Line: 9433

  SELECT sum(primary_quantity)
  INTO   l_total_primary_qty
  from   mtl_material_transactions
  where  transaction_action_id      = l_txn_action_id
  and    transaction_source_type_id = l_txn_src_type_id
  and    transaction_type_id        = l_txn_type_id
  and    trx_source_line_id         = l_trx_source_line_id
  and    costed_flag IS NULL ;

Line: 9471

      SELECT
        oel.SOURCE_DOCUMENT_LINE_ID
      INTO
        l_req_line_id
      FROM
        OE_ORDER_LINES_ALL oel,
        cst_acct_info_v caiv
      WHERE
          oel.LINE_ID          = l_trx_source_line_id
      and oel.org_id           = caiv.operating_unit
      and caiv.organization_id = l_organization_id;

Line: 9487

       SELECT
         REQUISITION_LINE_ID
       INTO
         l_req_line_id
       FROM
         RCV_TRANSACTIONS
       WHERE
         TRANSACTION_ID = l_rcv_txn_id;

Line: 9547

|               raise exception no_mcacd_for_hook. If the data inserted  in  |
|               MCACD has the insertion flag as 'Y' and there  are  details  |
|               in CLCD we will raise exception insertion_flag_in_mcacd      |
|                                                                            |
|  Parameters:  i_txn_id: Transaction id                                     |
|               i_org_id: Organization id                                    |
|               i_layer_id: Layer id                                         |
|                                                                            |
|                                                                            |
|===========================================================================*/
PROCEDURE validate_actual_cost_hook(
i_txn_id IN NUMBER,
i_org_id IN NUMBER,
i_layer_id IN NUMBER,
i_req_id IN NUMBER,
i_prg_appl_id IN NUMBER,
i_prg_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: 9574

insertion_flag_in_mcacd EXCEPTION;

Line: 9578

SELECT 	COUNT(*)
INTO 	l_test_mcacd
FROM 	MTL_CST_ACTUAL_COST_DETAILS MCACD
WHERE 	TRANSACTION_ID = i_txn_id
AND  	LAYER_ID = i_layer_id
AND     ORGANIZATION_ID = i_org_id;

Line: 9589

/* There shouldn't be details in CLCD if the insertion flag in MCACD is set as Y
   for that cost element, it will suffice that one of the cost element violates
   this condition to error out                                                 */
SELECT  SUM(decode(MCACD.insertion_flag, 'Y', 1, 0))
INTO  	l_test_clcd
FROM 	MTL_CST_ACTUAL_COST_DETAILS MCACD
WHERE 	MCACD.transaction_id = i_txn_id
AND     MCACD.organization_id = i_org_id
AND 	MCACD.layer_id = i_layer_id
AND     EXISTS (SELECT 'X'
               FROM CST_LAYER_COST_DETAILS CLCD
               WHERE MCACD.layer_id = CLCD.layer_id
               AND   MCACD.cost_element_id = CLCD.cost_element_id
               AND   MCACD.level_type = CLCD.level_type)
GROUP BY MCACD.layer_id;

Line: 9611

	fnd_file.put_line(fnd_file.log, 'There should not be details in CLCD if the insertion flag in MCACD is set to Y in the hook');

Line: 9612

	raise insertion_flag_in_mcacd;

Line: 9623

      UPDATE mtl_material_transactions
      SET    costed_flag = 'E',
             error_code = substrb(o_err_code,1,240),
             error_explanation = substrb(o_err_msg,1,240),
             request_id = i_req_id,
             program_application_id = i_prg_appl_id,
             program_id = i_prg_id,
             program_update_date = sysdate
      WHERE  transaction_id = i_txn_id;

Line: 9632

WHEN insertion_flag_in_mcacd THEN
      rollback;

Line: 9639

      UPDATE mtl_material_transactions
      SET    costed_flag = 'E',
             error_code = substrb(o_err_code,1,240),
             error_explanation = substrb(o_err_msg,1,240),
             request_id = i_req_id,
             program_application_id = i_prg_appl_id,
             program_id = i_prg_id,
             program_update_date = sysdate
      WHERE  transaction_id = i_txn_id;

Line: 9652

      UPDATE mtl_material_transactions
      SET    costed_flag = 'E',
             error_code = substrb(o_err_code,1,240),
             error_explanation = substrb(o_err_msg,1,240),
             request_id = i_req_id,
             program_application_id = i_prg_appl_id,
             program_id = i_prg_id,
             program_update_date = sysdate
      WHERE  transaction_id = i_txn_id;