APPS.GMF_LOTCOSTADJUSTMENT_PUB SQL Statements

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

Line: 180

	, p_operation		=>	'INSERT'
	, x_user_id		=>	l_user_id
	, x_return_status	=>	l_return_status
	);

Line: 206

      		log_msg('Calling private API to insert record...');

Line: 340

PROCEDURE Update_LotCost_Adjustment
(
p_api_version		IN  		NUMBER
, p_init_msg_list	IN  		VARCHAR2 := FND_API.G_FALSE
, p_commit		IN  		VARCHAR2 := FND_API.G_FALSE
, x_return_status	OUT 	NOCOPY 	VARCHAR2
, x_msg_count		OUT 	NOCOPY 	NUMBER
, x_msg_data		OUT 	NOCOPY 	VARCHAR2
, p_header_rec		IN OUT 	NOCOPY 	Lc_Adjustment_Header_Rec_Type
, p_dtl_Tbl		IN OUT 	NOCOPY 	Lc_adjustment_dtls_Tbl_Type
)
IS
  l_api_name		CONSTANT 	VARCHAR2(30)	:= 'Update_LotCost_Adjustment';

Line: 361

	SAVEPOINT	 Update_LotCost_Adjustment_PUB ;

Line: 384

    		log_msg('Beginning Update Lot Cost Adjustment process.');

Line: 398

	, p_operation		=>	'UPDATE'
	, x_user_id		=>	l_user_id
	, x_return_status	=>	l_return_status
	);

Line: 424

      		log_msg('Calling private API to Update record...');

Line: 428

	GMF_LotCostAdjustment_PVT.Update_LotCost_Adjustment
	(
	p_api_version		=>		p_api_version
	, p_init_msg_list	=>		FND_API.G_FALSE
	, x_return_status	=>		x_return_status
	, x_msg_count		=>		x_msg_count
	, x_msg_data		=>		x_msg_data
	, p_header_rec		=>		p_header_Rec
	, p_dtl_Tbl		=>		p_dtl_tbl
        , p_user_id             =>              l_user_id
	);

Line: 440

	-- Return if update fails for any reason
	IF l_return_status = FND_API.G_RET_STS_ERROR
	THEN
		add_header_to_error_stack ( p_header_Rec => p_header_rec );

Line: 473

		ROLLBACK TO Update_LotCost_Adjustment_PUB;

Line: 483

		ROLLBACK TO Update_LotCost_Adjustment_PUB;

Line: 493

		ROLLBACK TO Update_LotCost_Adjustment_PUB;

Line: 509

END Update_LotCost_Adjustment;

Line: 558

PROCEDURE Delete_LotCost_Adjustment
(
p_api_version		IN  		NUMBER
, p_init_msg_list	IN  		VARCHAR2 := FND_API.G_FALSE
, p_commit		IN  		VARCHAR2 := FND_API.G_FALSE
, x_return_status	OUT 	NOCOPY 	VARCHAR2
, x_msg_count		OUT 	NOCOPY 	NUMBER
, x_msg_data		OUT 	NOCOPY 	VARCHAR2
, p_header_rec		IN OUT 	NOCOPY 	Lc_Adjustment_Header_Rec_Type
, p_dtl_Tbl		IN OUT 	NOCOPY 	Lc_adjustment_dtls_Tbl_Type
)
IS
  l_api_name		CONSTANT 	VARCHAR2(30)	:= 'Delete_LotCost_Adjustment';

Line: 579

	SAVEPOINT	 Delete_LotCost_Adjustment_PUB ;

Line: 602

    		log_msg('Beginning Delete Lot Cost Adjustment process.');

Line: 616

	, p_operation		=>	'DELETE'
	, x_user_id		=>	l_user_id
	, x_return_status	=>	l_return_status
	);

Line: 642

      		log_msg('Calling private API to Delete record...');

Line: 646

	GMF_LotCostAdjustment_PVT.Delete_LotCost_Adjustment
	(
	p_api_version		=>		p_api_version
	, p_init_msg_list	=>		FND_API.G_FALSE
	, x_return_status	=>		x_return_status
	, x_msg_count		=>		x_msg_count
	, x_msg_data		=>		x_msg_data
	, p_header_rec		=>		p_header_Rec
	, p_dtl_Tbl		=>		p_dtl_tbl
	);

Line: 657

	-- Return if delete fails for any reason
	IF l_return_status = FND_API.G_RET_STS_ERROR
	THEN
		add_header_to_error_stack ( p_header_Rec => p_header_rec );

Line: 690

		ROLLBACK TO Delete_LotCost_Adjustment_PUB;

Line: 700

		ROLLBACK TO Delete_LotCost_Adjustment_PUB;

Line: 710

		ROLLBACK TO Delete_LotCost_Adjustment_PUB;

Line: 726

END Delete_LotCost_Adjustment;

Line: 976

        IF P_OPERATION IN ('INSERT', 'UPDATE', 'DELETE', 'GET')
	THEN
		IF	p_header_rec.adjustment_id IS NOT NULL
                THEN
			BEGIN
				SELECT	applied_ind
				INTO	l_applied_ind
				FROM	gmf_lot_cost_adjustments
				WHERE	adjustment_id = p_header_rec.adjustment_id
				AND	ROWNUM = 1;

Line: 993

			AND	P_OPERATION IN ('INSERT', 'UPDATE', 'DELETE')
			THEN

				FND_MESSAGE.SET_NAME('GMF','GMF_API_LCA_ADJ_APPLIED');

Line: 1001

			AND	p_operation IN ('INSERT')
			THEN

				FND_MESSAGE.SET_NAME('GMF','GMF_API_IGNORE_HEADER');

Line: 1251

             SELECT 	mic.category_id
	          INTO 	l_cost_category_id
             FROM 	mtl_default_category_sets mdc,
            		   mtl_category_sets mcs,
                    	mtl_item_categories mic,
                    	mtl_categories mc
              WHERE 	mic.inventory_item_id = p_header_rec.item_id
                AND 	mic.organization_id = p_header_rec.organization_id
                AND 	mic.category_id = mc.category_id
                AND 	mcs.structure_id = mc.structure_id
                AND 	mdc.functional_area_id = 19
		          AND  mcs.category_set_id = mic.category_set_id
		          AND  mcs.category_set_id = mdc.category_set_id;

Line: 1271

					SELECT 	1
					INTO	l_lot_costed_items_cnt
					FROM	GMF_LOT_COSTED_ITEMS
					WHERE	legal_entity_id = p_header_Rec.legal_entity_id
					AND	cost_type_id = p_header_rec.cost_type_id
					AND	inventory_item_id = p_header_rec.item_id;

Line: 1281

							SELECT 	1
							INTO	l_lot_costed_items_cnt
							FROM	GMF_LOT_COSTED_ITEMS
							WHERE	legal_entity_id = p_header_Rec.legal_entity_id
					                  AND	cost_type_id = p_header_rec.cost_type_id
							  AND	cost_category_id = l_cost_category_id;

Line: 1336

					SELECT MAX(cost_trans_date)
					INTO l_adjustment_date
					FROM gmf_material_lot_cost_txns txns, gmf_lot_costs lc
					WHERE lc.inventory_item_id = p_header_Rec.item_id
					AND lc.lot_number = p_header_Rec.lot_number
					AND lc.organization_id = p_header_rec.organization_id
					AND lc.final_cost_flag = 1
					AND txns.cost_header_id = lc.header_id
					AND txns.final_cost_flag = 1;

Line: 1378

					SELECT	1
					INTO	l_reason_cnt
					FROM	cm_reas_cds
					WHERE	reason_code = p_header_rec.reason_code
					AND	delete_mark = 0
					AND	ROWNUM = 1;

Line: 1399

			-- Delete Mark
		        IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
			THEN
            			log_msg('Validating Delete_mark : ' || p_header_Rec.delete_mark);

Line: 1405

            		IF (p_header_Rec.delete_mark <> FND_API.G_MISS_NUM) AND (p_header_Rec.delete_mark IS NOT NULL)
			THEN
              			IF p_header_Rec.delete_mark NOT IN (0,1) THEN
          				add_header_to_error_stack(p_header_rec);

Line: 1409

                			FND_MESSAGE.SET_NAME('GMF','GMF_API_INVALID_DELETE_MARK');

Line: 1410

                			FND_MESSAGE.SET_TOKEN('DELETE_MARK',p_header_Rec.delete_mark);

Line: 1414

            		ELSIF (p_header_Rec.delete_mark = FND_API.G_MISS_NUM AND p_operation = 'UPDATE') OR
		  	(p_operation = 'INSERT') THEN
          	  		add_header_to_error_stack(p_header_rec);

Line: 1417

                  		FND_MESSAGE.SET_NAME('GMF','GMF_API_DELETE_MARK_REQ');

Line: 1421

            		IF ((p_operation = 'UPDATE') AND (p_header_Rec.delete_mark = 1)) THEN
              			add_header_to_error_stack(p_header_rec);

Line: 1435

			IF p_operation IN ('UPDATE', 'DELETE', 'INSERT')
			THEN
				IF	p_header_rec.adjustment_id IS NULL
				AND	(p_header_Rec.legal_entity_id IS NOT NULL
				AND	( p_header_Rec.COST_MTHD_CODE IS NOT NULL OR p_header_Rec.cost_type_id IS NOT NULL)
				AND	(p_header_rec.organization_code  IS NOT NULL OR p_header_rec.organization_id IS NOT NULL )
				AND	p_header_Rec.ADJUSTMENT_date IS NOT NULL
				AND	(p_header_Rec.item_id IS NOT NULL OR	p_header_rec.item_number IS NOT NULL)
				AND	p_header_Rec.lot_number IS NOT NULL )
				THEN
					IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
					THEN
						log_msg('Fetching Adjustment ID for Code Combination for ' ||
						' Legal Entity ' || p_header_rec.legal_entity_id ||
						' Cost Type Id ' || p_header_rec.cost_type_id ||
                  ' Cost Type ' || p_header_rec.cost_mthd_code ||
                  ' Organization Id ' || p_header_rec.organization_id ||
						' Organization Code ' || p_header_rec.organization_code ||
						' Adjustment Date ' || p_header_rec.adjustment_date ||
                  ' Item Id ' || p_header_rec.item_id ||
						' Item Code ' || p_header_rec.item_number ||
						' for '|| p_operation );

Line: 1460

						SELECT	adjustment_id, applied_ind
						INTO	p_header_Rec.adjustment_id, l_applied_ind
						FROM	gmf_lot_cost_adjustments
						WHERE	legal_entity_id = p_header_rec.legal_entity_id
						AND	cost_type_id = p_header_rec.cost_type_id
						AND	organization_id = p_header_rec.organization_id
						AND	inventory_item_id = p_header_rec.item_id
						AND	lot_number = p_header_rec.lot_number
						AND	adjustment_date = p_header_rec.adjustment_date
						AND	ROWNUM = 1;

Line: 1477

					AND P_OPERATION IN ('UPDATE', 'DELETE')
					THEN
						IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
						THEN
							log_msg('Adjustment ID for Code Combination for ' ||
						' Legal Entity ' || p_header_rec.legal_entity_id ||
						' Cost Type Id ' || p_header_rec.cost_type_id ||
                  ' Cost Type ' || p_header_rec.cost_mthd_code ||
                  ' Organization Id ' || p_header_rec.organization_id ||
						' Organization Code ' || p_header_rec.organization_code ||
						' Adjustment Date ' || p_header_rec.adjustment_date ||
                  ' Item Id ' || p_header_rec.item_id ||
						' Item Code ' || p_header_rec.item_number ||
							' for '|| p_operation ||' doesn''t exist ');

Line: 1499

					AND	P_OPERATION IN ('INSERT', 'UPDATE', 'DELETE')
					THEN

						FND_MESSAGE.SET_NAME('GMF','GMF_API_LCA_ADJ_APPLIED');

Line: 1507

					AND	p_operation IN ('INSERT')
					THEN

						FND_MESSAGE.SET_NAME('GMF','GMF_API_IGNORE_HEADER');

Line: 1552

  IF P_OPERATION IN ('INSERT', 'UPDATE', 'DELETE')
	THEN
	FOR i IN p_dtl_tbl.FIRST .. p_dtl_tbl.LAST
	LOOP
       IF p_dtl_tbl(i).adjustment_dtl_id IS NOT NULL
			THEN
				BEGIN
					SELECT	1
					INTO	l_detail_cnt
					FROM	gmf_lot_cost_adjustment_dtls
					WHERE	adjustment_dtl_id = p_dtl_tbl(i).adjustment_dtl_id
					AND	ROWNUM = 1;

Line: 1571

				AND	P_OPERATION IN ('INSERT')
				THEN

					FND_MESSAGE.SET_NAME('GMF','CM_DUP_RECORD'); -- Bug # 3755374 ANTHIYAG 12-Jul-2004

Line: 1673

						SELECT	1
						INTO	l_text_cnt
						FROM	cm_text_hdr
						WHERE	text_code = p_dtl_tbl(i).text_code
						AND	ROWNUM = 1;

Line: 1694

			IF P_OPERATION IN ('UPDATE', 'INSERT')
			THEN
				IF p_dtl_tbl(i).adjustment_cost IS NULL
				THEN
					FND_MESSAGE.SET_NAME('GMF','GMF_API_LCA_ADJ_COST');

Line: 1710

			IF	p_operation IN ('UPDATE', 'DELETE', 'INSERT')
			THEN
				IF	p_dtl_tbl(i).adjustment_dtl_id IS NULL
				THEN
					IF	(p_dtl_tbl(i).cost_cmpntcls_id IS NOT NULL
					AND	p_dtl_tbl(i).cost_analysis_code IS NOT NULL)
					THEN
						BEGIN
							SELECT	adjustment_dtl_id
							INTO	p_dtl_tbl(i).adjustment_dtl_id
							FROM	gmf_lot_cost_adjustment_dtls
							WHERE	cost_cmpntcls_id = p_dtl_tbl(i).cost_cmpntcls_id
							AND	cost_analysis_code = p_dtl_tbl(i).cost_analysis_code
							AND	adjustment_id = p_header_rec.adjustment_id
							AND	ROWNUM = 1;

Line: 1728

								IF	P_operation IN ('UPDATE', 'DELETE')
								THEN
									FND_MESSAGE.SET_NAME('GMF','GMF_API_LCA_ADJ_DTL_ID');

Line: 1738

						IF	P_OPERATION IN ('INSERT')
						AND	p_dtl_tbl(i).adjustment_dtl_id IS NOT NULL
						THEN

							FND_MESSAGE.SET_NAME('GMF','CM_DUP_RECORD'); -- Bug # 3755374 ANTHIYAG 12-Jul-2004