APPS.CSTPACIR SQL Statements

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

Line: 40

	*    which did not exist during the last update, or        *
	* 2. This is the first issue being costed for the job, then*
	*    INSERT is required.				   *
	***********************************************************/


	INSERT INTO WIP_REQ_OPERATION_COST_DETAILS
	(WIP_ENTITY_ID,
	 OPERATION_SEQ_NUM,
	 ORGANIZATION_ID,
	 INVENTORY_ITEM_ID,
	 COST_ELEMENT_ID,
	 APPLIED_MATL_VALUE,
	 LAST_UPDATED_BY,
	 LAST_UPDATE_DATE,
	 CREATION_DATE,
	 CREATED_BY,
	 LAST_UPDATE_LOGIN,
	 REQUEST_ID,
	 PROGRAM_APPLICATION_ID,
	 PROGRAM_ID,
	 PROGRAM_UPDATE_DATE)
	SELECT
	 i_wip_entity_id,
	 i_op_seq_num,
	 i_org_id,
	 i_inv_item_id,
	 c.COST_ELEMENT_ID,
	 0,
	 i_user_id,
	 SYSDATE,
	 SYSDATE,
	 i_user_id,
	 i_login_id,
	 i_request_id,
	 i_prog_id,
	 i_prog_appl_id,
	 SYSDATE
	from CST_LAYER_COST_DETAILS c
	WHERE
	 c.LAYER_ID = i_layer_id AND
	 NOT EXISTS
	(
	 SELECT 'X'
	 FROM
	 WIP_REQ_OPERATION_COST_DETAILS W2
	 WHERE
	 W2.COST_ELEMENT_ID	=	C.COST_ELEMENT_ID	AND
	 W2.WIP_ENTITY_ID	=	i_wip_entity_id		AND
	 W2.ORGANIZATION_ID	=	i_org_id		AND
	 W2.INVENTORY_ITEM_ID	=	i_inv_item_id		AND
	 W2.OPERATION_SEQ_NUM	=	i_op_seq_num
         )
	group by
	c.COST_ELEMENT_ID;

Line: 122

        * update wip_req_op_cost_details,per cost element *
        * for the item that has been issued/returned.     *
        **************************************************/

        UPDATE WIP_REQ_OPERATION_COST_DETAILS w
        SET (LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
            PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
            applied_matl_value )=
        (SELECT
            i_user_id, i_login_id,
            i_prog_appl_id,
            i_prog_id,
            SYSDATE,
            nvl(w.applied_matl_value,0)+
               (-1*i_txn_qty)*SUM(decode(l_zero_cost_flag, 1, 0, ITEM_COST))
            from
            CST_LAYER_COST_DETAILS c
            WHERE
            c.LAYER_ID = i_layer_id AND
            c.COST_ELEMENT_ID=w.COST_ELEMENT_ID
            GROUP BY c.COST_ELEMENT_ID
        )
        WHERE
        w.WIP_ENTITY_ID=i_wip_entity_id and
        w.INVENTORY_ITEM_ID=i_inv_item_id and
        w.ORGANIZATION_ID=i_org_id and
        w.OPERATION_SEQ_NUM=i_op_seq_num
        AND exists
        (select 'layer exists' from
         CST_LAYER_COST_DETAILS c2
         where c2.LAYER_ID = i_layer_id
        and     c2.cost_element_id = w.cost_element_id);

Line: 157

	* Insert into cst_txn_cst_details ...		    *
	* is not required since this occurs at current avg  *
        * cost.						    *
	*****************************************************/



   EXCEPTION

	WHEN OTHERS THEN
	err_num := SQLCODE;