APPS.CSTPCINT SQL Statements

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

Line: 4

FUNCTION update_interface(
        i_group_id IN NUMBER,
        error_msg OUT NOCOPY VARCHAR2
        ) RETURN INTEGER IS

location NUMBER(2) := 1;

Line: 15

	 * This must be done first, before the following updates.
	 *
	 */
	UPDATE CST_ITEM_COSTS_INTERFACE CICI
	SET
	CICI.ORGANIZATION_ID = (
          SELECT MP.ORGANIZATION_ID
          FROM MTL_PARAMETERS MP
          WHERE MP.ORGANIZATION_CODE = CICI.ORGANIZATION_CODE
        )
	WHERE CICI.GROUP_ID = i_group_id;

Line: 39

	 * of the following update statement is BAD.
	 */

	UPDATE CST_ITEM_COSTS_INTERFACE CICI
        SET
	INVENTORY_ITEM_ID = (
                SELECT MIF.ITEM_ID
                FROM MTL_ITEM_FLEXFIELDS MIF
		WHERE MIF.ORGANIZATION_ID = CICI.ORGANIZATION_ID
		AND MIF.ITEM_NUMBER = CICI.INVENTORY_ITEM
	)
	WHERE CICI.GROUP_ID = i_group_id
        AND CICI.INVENTORY_ITEM_ID IS NULL;

Line: 53

	UPDATE CST_ITEM_COSTS_INTERFACE CICI
        SET
	COST_TYPE_ID = (
                SELECT CCT.COST_TYPE_ID
                FROM CST_COST_TYPES CCT
                WHERE NVL( ORGANIZATION_ID, CICI.ORGANIZATION_ID)
			= CICI.ORGANIZATION_ID
                AND CCT.COST_TYPE = CICI.COST_TYPE
	)
	WHERE CICI.GROUP_ID = i_group_id;

Line: 66

         * This must be done first, before the following updates.
         *
         */
	location := 3;

Line: 71

        UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
        SET
        CICDI.ORGANIZATION_ID = (
          SELECT MP.ORGANIZATION_ID
          FROM MTL_PARAMETERS MP
          WHERE MP.ORGANIZATION_CODE = CICDI.ORGANIZATION_CODE
	)
	WHERE CICDI.GROUP_ID = i_group_id;

Line: 93

	 * of the following update statement is BAD.
	 */

	UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
        SET
        INVENTORY_ITEM_ID = (
                SELECT MIF.ITEM_ID
		FROM MTL_ITEM_FLEXFIELDS MIF
                WHERE MIF.ORGANIZATION_ID = CICDI.ORGANIZATION_ID
                AND MIF.ITEM_NUMBER = CICDI.INVENTORY_ITEM
        )
        WHERE CICDI.GROUP_ID = i_group_id
        AND CICDI.INVENTORY_ITEM_ID IS NULL;

Line: 107

	UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
        SET
        COST_TYPE_ID = (
                SELECT CCT.COST_TYPE_ID
                FROM CST_COST_TYPES CCT
                WHERE NVL( ORGANIZATION_ID, CICDI.ORGANIZATION_ID)
                        = CICDI.ORGANIZATION_ID
                AND CCT.COST_TYPE = CICDI.COST_TYPE
        ),
	DEPARTMENT_ID = (
                SELECT BD.DEPARTMENT_ID
                FROM BOM_DEPARTMENTS BD
                WHERE BD.ORGANIZATION_ID = CICDI.ORGANIZATION_ID
                AND BD.DEPARTMENT_CODE = CICDI.DEPARTMENT
	),
	ACTIVITY_ID = (
                SELECT CA.ACTIVITY_ID
		FROM CST_ACTIVITIES CA
                WHERE NVL(CA.ORGANIZATION_ID,CICDI.ORGANIZATION_ID) =
                                                   CICDI.ORGANIZATION_ID
                AND CA.ACTIVITY = CICDI.ACTIVITY
	),
        /* Bug 5443502: for resource_id and basis_resource_id, added join with cost_element_id */
	RESOURCE_ID = (
		SELECT BR.RESOURCE_ID
		FROM BOM_RESOURCES BR
		WHERE BR.RESOURCE_CODE = CICDI.RESOURCE_CODE
		AND BR.ORGANIZATION_ID = CICDI.ORGANIZATION_ID
                AND BR.COST_ELEMENT_ID = CICDI.COST_ELEMENT_ID
	),
	BASIS_RESOURCE_ID = (
                SELECT BR.RESOURCE_ID
                FROM BOM_RESOURCES BR
                WHERE BR.RESOURCE_CODE = CICDI.BASIS_RESOURCE_CODE
                AND BR.ORGANIZATION_ID = CICDI.ORGANIZATION_ID
                AND BR.COST_ELEMENT_ID = CICDI.COST_ELEMENT_ID
	),
	COST_ELEMENT_ID = (
                SELECT CCE.COST_ELEMENT_ID
                FROM CST_COST_ELEMENTS CCE
                WHERE CCE.COST_ELEMENT = CICDI.COST_ELEMENT
	)
        WHERE CICDI.GROUP_ID = i_group_id;

Line: 160

        error_msg         := 'update_interface(' || location || '):' || SQLERRM(100);

Line: 163

END update_interface;

Line: 177

        select count(*)
        into l_to_wsm_flag
        from mtl_parameters mp,wsm_parameters wsm
        where wsm.organization_id = i_to_org_id
        and mp.organization_id = wsm.organization_id
        and UPPER(mp.wsm_enabled_flag) = 'Y';

Line: 185

             update cst_item_cst_dtls_interface
             set yielded_cost = null
             where organization_id = i_to_org_id
             and group_id = i_group_id
             and yielded_cost is not null;

Line: 197

	INSERT into cst_interface_errors (
		inventory_item,
		entity_code,
		error_type,
		group_id
	)
	SELECT
		inventory_item,
		resource_code,
		DECODE(cost_element_id,
		       3,1,
		       5,5),
		i_group_id
	FROM cst_item_cst_dtls_interface CICDI
	WHERE CICDI.group_id = i_group_id
	AND CICDI.resource_code IS NOT NULL
	AND CICDI.resource_id IS NULL
	UNION
	SELECT
		INVENTORY_ITEM,
		ACTIVITY,
		2,
		i_group_id
	FROM CST_ITEM_CST_DTLS_INTERFACE CICDI
	WHERE CICDI.GROUP_ID = i_group_id
	AND CICDI.ACTIVITY_ID IS NULL
	AND CICDI.ACTIVITY IS NOT NULL;

Line: 227

        insert into cst_interface_errors (
                inventory_item,
                entity_code,
                error_type,
                group_id
        )
        select  inventory_item,
                resource_code,
                6,
                i_group_id
        from cst_item_cst_dtls_interface cicdi
        where cicdi.group_id = i_group_id
        /* TL Material Overhead needs to have Subelement specified */
        and cicdi.resource_code is null
        and (cicdi.cost_element_id = 2 and cicdi.level_type = 1);

Line: 245

	INSERT into cst_interface_errors (
		inventory_item,
		entity_code,
		error_type,
		group_id
	)
	SELECT
		INVENTORY_ITEM,
		DEPARTMENT,
		3,
		i_group_id
	FROM CST_ITEM_CST_DTLS_INTERFACE CICDI
	WHERE CICDI.GROUP_ID = i_group_id
	AND CICDI.DEPARTMENT_ID IS NULL
	AND CICDI.DEPARTMENT IS NOT NULL
	UNION
	SELECT
		INVENTORY_ITEM,
		BASIS_RESOURCE_CODE,
		4,
		i_group_id
	FROM CST_ITEM_CST_DTLS_INTERFACE CICDI
	WHERE CICDI.GROUP_ID = i_group_id
	AND CICDI.BASIS_RESOURCE_ID IS NULL
	AND CICDI.BASIS_RESOURCE_CODE IS NOT NULL;

Line: 273

	INSERT into cst_interface_errors (
		inventory_item,
		entity_code,
		error_type,
		group_id
	)
	SELECT
		INVENTORY_ITEM,
		COST_ELEMENT,
		5,
		i_group_id
	FROM CST_ITEM_CST_DTLS_INTERFACE CICDI
	WHERE CICDI.GROUP_ID = i_group_id
	AND CICDI.COST_ELEMENT_ID IS NULL
	AND CICDI.COST_ELEMENT IS NOT NULL;

Line: 306

FUNCTION insert_to_dest(
        i_group_id	IN NUMBER,
        i_user_id	IN NUMBER,
	i_request_id	IN NUMBER,
	i_prog_applid	IN NUMBER,
	i_prog_id	IN NUMBER,
	i_rowcount	OUT NOCOPY NUMBER,
        error_msg       OUT NOCOPY VARCHAR2
) RETURN INTEGER IS

location	NUMBER := 0;

Line: 322

	INSERT INTO CST_ITEM_COSTS (
		INVENTORY_ITEM_ID,
		ORGANIZATION_ID,
		COST_TYPE_ID,
		LAST_UPDATE_DATE,
		LAST_UPDATED_BY,
		CREATION_DATE,
		CREATED_BY,
		LAST_UPDATE_LOGIN,
		INVENTORY_ASSET_FLAG,
		LOT_SIZE,
		BASED_ON_ROLLUP_FLAG,
		SHRINKAGE_RATE,
		DEFAULTED_FLAG,
		COST_UPDATE_ID,
		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,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		REQUEST_ID,
		PROGRAM_APPLICATION_ID,
		PROGRAM_ID,
		PROGRAM_UPDATE_DATE
	) SELECT
		INVENTORY_ITEM_ID,
		ORGANIZATION_ID,
		COST_TYPE_ID,
		SYSDATE,
		i_user_id,
		SYSDATE,
		i_user_id,
		-1,
		INVENTORY_ASSET_FLAG,
		LOT_SIZE,
		BASED_ON_ROLLUP_FLAG,
		SHRINKAGE_RATE,
		DEFAULTED_FLAG,
		COST_UPDATE_ID,
		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,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		i_request_id,
		i_prog_applid,
		i_prog_id,
		SYSDATE
	FROM	CST_ITEM_COSTS_INTERFACE CICI
	WHERE	CICI.GROUP_ID = i_group_id;

Line: 439

	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,
		OPERATION_SEQUENCE_ID,
		OPERATION_SEQ_NUM,
		DEPARTMENT_ID,
		LEVEL_TYPE,
		ACTIVITY_ID,
		RESOURCE_SEQ_NUM,
		RESOURCE_ID,
		RESOURCE_RATE,
		ITEM_UNITS,
		ACTIVITY_UNITS,
		USAGE_RATE_OR_AMOUNT,
		BASIS_TYPE,
		BASIS_RESOURCE_ID,
		BASIS_FACTOR,
		NET_YIELD_OR_SHRINKAGE_FACTOR,
		ITEM_COST,
		COST_ELEMENT_ID,
		ROLLUP_SOURCE_TYPE,
		ACTIVITY_CONTEXT,
		REQUEST_ID,
		PROGRAM_APPLICATION_ID,
		PROGRAM_ID,
		PROGRAM_UPDATE_DATE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		--bug5839929
		YIELDED_COST
	) SELECT
		INVENTORY_ITEM_ID,
		ORGANIZATION_ID,
		COST_TYPE_ID,
		SYSDATE,
		i_user_id,
		SYSDATE,
		i_user_id,
		NULL,
		OPERATION_SEQUENCE_ID,
		OPERATION_SEQ_NUM,
		DEPARTMENT_ID,
		LEVEL_TYPE,
		ACTIVITY_ID,
		RESOURCE_SEQ_NUM,
		RESOURCE_ID,
		RESOURCE_RATE,
		ITEM_UNITS,
		ACTIVITY_UNITS,
		USAGE_RATE_OR_AMOUNT,
		BASIS_TYPE,
		BASIS_RESOURCE_ID,
		BASIS_FACTOR,
		NET_YIELD_OR_SHRINKAGE_FACTOR,
		ITEM_COST,
		COST_ELEMENT_ID,
		ROLLUP_SOURCE_TYPE,
		ACTIVITY_CONTEXT,
		i_request_id,
		i_prog_applid,
		i_prog_id,
		SYSDATE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		--bug5839929
		YIELDED_COST
	FROM	CST_ITEM_CST_DTLS_INTERFACE CICDI
	WHERE	CICDI.GROUP_ID = i_group_id;

Line: 548

        error_msg := 'insert_to_dest(' || location ||'): ' || SQLERRM(100);

Line: 551

END insert_to_dest;

Line: 553

FUNCTION delete_from_interface(
        i_group_id	IN NUMBER,
        error_msg	OUT NOCOPY VARCHAR2
) RETURN INTEGER IS

location	NUMBER := 0;

Line: 564

	DELETE FROM CST_ITEM_COSTS_INTERFACE CICI
	WHERE CICI.GROUP_ID = i_group_id;

Line: 569

	DELETE FROM CST_ITEM_CST_DTLS_INTERFACE CICDI
	WHERE CICDI.GROUP_ID = i_group_id;

Line: 577

        error_msg := 'delete_from_interface(' || location || '): ' ||
		SQLERRM(100);

Line: 581

END delete_from_interface;

Line: 647

 result := cstpcint.update_interface( i_group_id, error_msg );

Line: 660

 result := cstpcint.insert_to_dest(i_group_id,
				   i_user_id,
				   i_request_id,
				   i_prog_applid,
				   i_prog_id,
				   i_rowcount,
				   error_msg );

Line: 673

 result := cstpcint.delete_from_interface( i_group_id, error_msg );