[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT GEM5_COST_ADJUST_ID_S.NEXTVAL
INTO l_cost_adjust_id
FROM dual;
log_msg( ' Inserting Actual Cost Adjustments for '||
' Item ' || p_adjustment_rec.inventory_item_id ||
' Organization ' || p_adjustment_rec.organization_id ||
' Cost Type ' || p_adjustment_rec.cost_type_id ||
' Period Id ' || p_adjustment_rec.period_id ||
' Cost Component Class ' || p_adjustment_rec.cost_cmpntcls_id ||
' Analysis code ' || p_adjustment_rec.cost_analysis_code ||
' Adjustment Indicator '|| p_adjustment_rec.adjustment_ind
);
INSERT INTO cm_adjs_dtl
(
ORGANIZATION_ID
, INVENTORY_ITEM_ID
, COST_TYPE_ID
, PERIOD_ID
, COST_CMPNTCLS_ID
, COST_ANALYSIS_CODE
, COST_ADJUST_ID
, ADJUST_QTY
, ADJUST_QTY_UOM
, ADJUST_COST
, REASON_CODE
, ADJUST_STATUS
, CREATION_DATE
, LAST_UPDATE_LOGIN
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, TEXT_CODE
, TRANS_CNT
, DELETE_MARK
, 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
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, ATTRIBUTE21
, ATTRIBUTE22
, ATTRIBUTE23
, ATTRIBUTE24
, ATTRIBUTE25
, ATTRIBUTE26
, ATTRIBUTE27
, ATTRIBUTE28
, ATTRIBUTE29
, ATTRIBUTE30
, ADJUSTMENT_IND
, SUBLEDGER_IND
, ADJUSTMENT_DATE
, GL_POSTED_IND
)
VALUES
(
p_adjustment_rec.organization_id
, p_adjustment_rec.inventory_item_id
, p_adjustment_rec.cost_type_id
, p_adjustment_rec.period_id
, p_adjustment_rec.cost_cmpntcls_id
, p_adjustment_rec.cost_analysis_code
, l_cost_adjust_id
, p_adjustment_rec.adjust_qty
, p_adjustment_rec.adjust_qty_uom
, p_adjustment_rec.adjust_cost
, p_adjustment_rec.reason_code
, p_adjustment_rec.adjust_status
, SYSDATE
, FND_GLOBAL.LOGIN_ID
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, p_adjustment_rec.text_code
, 0
, 0
, p_adjustment_rec.request_id
, p_adjustment_rec.program_application_id
, p_adjustment_rec.program_id
, p_adjustment_rec.program_update_date
, p_adjustment_rec.attribute_category
, p_adjustment_rec.attribute1
, p_adjustment_rec.attribute2
, p_adjustment_rec.attribute3
, p_adjustment_rec.attribute4
, p_adjustment_rec.attribute5
, p_adjustment_rec.attribute6
, p_adjustment_rec.attribute7
, p_adjustment_rec.attribute8
, p_adjustment_rec.attribute9
, p_adjustment_rec.attribute10
, p_adjustment_rec.attribute11
, p_adjustment_rec.attribute12
, p_adjustment_rec.attribute13
, p_adjustment_rec.attribute14
, p_adjustment_rec.attribute15
, p_adjustment_rec.attribute16
, p_adjustment_rec.attribute17
, p_adjustment_rec.attribute18
, p_adjustment_rec.attribute19
, p_adjustment_rec.attribute20
, p_adjustment_rec.attribute21
, p_adjustment_rec.attribute22
, p_adjustment_rec.attribute23
, p_adjustment_rec.attribute24
, p_adjustment_rec.attribute25
, p_adjustment_rec.attribute26
, p_adjustment_rec.attribute27
, p_adjustment_rec.attribute28
, p_adjustment_rec.attribute29
, p_adjustment_rec.attribute30
, p_adjustment_rec.adjustment_ind
, p_adjustment_rec.subledger_ind
, p_adjustment_rec.adjustment_date
, 0
) RETURNING cost_adjust_id INTO p_adjustment_rec.cost_adjust_id;
log_msg( SQL%ROWCOUNT || ' Record Inserted for Actual Cost Adjustments for '||
' Item ' || p_adjustment_rec.inventory_item_id ||
' Organization ' || p_adjustment_rec.organization_id ||
' Cost Type ' || p_adjustment_rec.cost_type_id ||
' Period Id ' || p_adjustment_rec.period_id ||
' Cost Component Class ' || p_adjustment_rec.cost_cmpntcls_id ||
' Analysis code ' || p_adjustment_rec.cost_analysis_code ||
' Adjustment Indicator '|| p_adjustment_rec.adjustment_ind
);
* UPDATE_ACTUAL_COST_ADJUSTMENT *
* *
* TYPE *
* PUBLIC *
* *
* FUNCTION *
* Updates Actual Cost Adjustment based on the input into table *
* GMF_LOT_COST_ADJUSTMENTS *
* *
* PARAMETERS *
* IN : *
* p_api_version IN NUMBER *
* p_init_msg_list IN VARCHAR2 *
* p_adjustment_rec IN OUT NOCOPY Adjustment_Rec_Type *
* *
* OUT : *
* x_return_status OUT NOCOPY VARCHAR2 *
* x_msg_count OUT NOCOPY VARCHAR2 *
* x_msg_data OUT NOCOPY VARCHAR2 *
* *
* DESCRIPTION *
* This procedure updates Actual Cost Adjustments *
* *
* HISTORY *
* 16-Sep-2005 Anand Thiyagarajan Created *
* 4-Nov-2009 Prasad marada Bug 9005515, updating adjust status *
* column value with 2 (modified) to consider by ACP *
********************************************************************/
PROCEDURE UPDATE_ACTUAL_COST_ADJUSTMENT
(
p_api_version IN NUMBER,
p_init_msg_list 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_adjustment_rec IN OUT NOCOPY GMF_ACTUAL_COST_ADJUSTMENT_PUB.ADJUSTMENT_REC_TYPE
)
IS
/******************
* Local Variables *
******************/
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ACTUAL_COST_ADJUSTMENT';
SAVEPOINT UPDATE_ACT_COST_ADJUSTMENT_PVT ;
log_msg('Beginning Private Update Actual Cost Adjustment API');
UPDATE cm_adjs_dtl
SET cost_cmpntcls_id = p_adjustment_rec.cost_cmpntcls_id,
cost_analysis_code = p_adjustment_rec.cost_analysis_code,
adjust_qty = p_adjustment_rec.adjust_qty,
adjust_qty_uom = p_adjustment_rec.adjust_qty_uom,
adjust_cost = p_adjustment_rec.adjust_cost,
reason_code = p_adjustment_rec.reason_code,
adjustment_ind = p_adjustment_rec.adjustment_ind,
subledger_ind = p_adjustment_rec.subledger_ind,
adjustment_date = p_adjustment_rec.adjustment_date,
adjust_status = 2 , /* bug 9005515, changing status to modified */
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE cost_adjust_id = p_adjustment_rec.cost_adjust_id
AND delete_mark = 0
AND gl_posted_ind <> 1;
/* AND adjust_status <> 1; bug 9005515, allow applied adjustment to be updated */
ROLLBACK TO UPDATE_ACT_COST_ADJUSTMENT_PVT;
ROLLBACK TO UPDATE_ACT_COST_ADJUSTMENT_PVT;
ROLLBACK TO UPDATE_ACT_COST_ADJUSTMENT_PVT;
END UPDATE_ACTUAL_COST_ADJUSTMENT;
* DELETE_ACTUAL_COST_ADJUSTMENT *
* *
* TYPE *
* PUBLIC *
* *
* FUNCTION *
* Deletes Actual Cost Adjustment based on the input from table *
* GMF_LOT_COST_ADJUSTMENTS *
* *
* PARAMETERS *
* IN : *
* p_api_version IN NUMBER *
* p_init_msg_list IN VARCHAR2 *
* p_adjustment_rec IN OUT NOCOPY Adjustment_Rec_Type *
* *
* OUT : *
* x_return_stat OUT NOCOPY VARCHAR2 *
* x_msg_count OUT NOCOPY VARCHAR2 *
* x_msg_data OUT NOCOPY VARCHAR2 *
* *
* DESCRIPTION *
* This procedure deletes Actual Cost Adjustments *
* *
* HISTORY *
* 16-Sep-2005 Anand Thiyagarajan Created *
* 10-Nov-2009 Prasad marada bug9005515,we shd not delete adjustments*
* instead of that update delete_mark =1, *
********************************************************************/
PROCEDURE DELETE_ACTUAL_COST_ADJUSTMENT
(
p_api_version IN NUMBER,
p_init_msg_list 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_adjustment_rec IN OUT NOCOPY GMF_ACTUAL_COST_ADJUSTMENT_PUB.ADJUSTMENT_REC_TYPE
)
IS
/******************
* Local Variables *
******************/
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ACTUAL_COST_ADJUSTMENT';
SAVEPOINT DELETE_ACT_COST_ADJUSTMENT_PVT ;
log_msg('Beginning Private Delete Actual Cost Adjustment API');
/* bug 9005515, update the adjustment with delete mark =1, through form we are not deleting the adjustments
DELETE cm_adjs_dtl
WHERE cost_adjust_id = p_adjustment_rec.cost_adjust_id
AND adjust_status <> 1; bug 9005515 */
UPDATE cm_adjs_dtl
SET delete_mark = 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE cost_adjust_id = p_adjustment_rec.cost_adjust_id
AND gl_posted_ind <> 1;
ROLLBACK TO DELETE_ACT_COST_ADJUSTMENT_PVT;
ROLLBACK TO DELETE_ACT_COST_ADJUSTMENT_PVT;
ROLLBACK TO DELETE_ACT_COST_ADJUSTMENT_PVT;
END DELETE_ACTUAL_COST_ADJUSTMENT;
SELECT ORGANIZATION_ID, INVENTORY_ITEM_ID, COST_TYPE_ID, PERIOD_ID, COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
COST_ADJUST_ID, ADJUST_QTY, ADJUST_QTY_UOM, ADJUST_COST, REASON_CODE, ADJUST_STATUS, CREATION_DATE,
LAST_UPDATE_LOGIN, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, TEXT_CODE, TRANS_CNT, DELETE_MARK,
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, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18,
ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24, ATTRIBUTE25, ATTRIBUTE26,
ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30, ADJUSTMENT_IND, SUBLEDGER_IND, ADJUSTMENT_DATE
FROM cm_adjs_dtl
WHERE p_type = 1
AND COST_ADJUST_ID = p_cost_adjust_id
UNION
SELECT ORGANIZATION_ID, INVENTORY_ITEM_ID, COST_TYPE_ID, PERIOD_ID, COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
COST_ADJUST_ID, ADJUST_QTY, ADJUST_QTY_UOM, ADJUST_COST, REASON_CODE, ADJUST_STATUS, CREATION_DATE,
LAST_UPDATE_LOGIN, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, TEXT_CODE, TRANS_CNT, DELETE_MARK,
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, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18,
ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24, ATTRIBUTE25, ATTRIBUTE26,
ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30, ADJUSTMENT_IND, SUBLEDGER_IND, ADJUSTMENT_DATE
FROM cm_adjs_dtl
WHERE p_type = 2
AND ORGANIZATION_ID = p_organization_id
AND INVENTORY_ITEM_ID = p_inventory_item_id
AND COST_TYPE_ID = p_cost_type_id
AND PERIOD_ID = p_period_id
AND COST_CMPNTCLS_ID = p_cost_cmpntcls_id
AND COST_ANALYSIS_CODE = p_cost_analysis_code
AND ADJUSTMENT_IND = p_adjustment_ind;
p_adjustment_rec.last_update_login := i.LAST_UPDATE_LOGIN;
p_adjustment_rec.last_update_date := i.LAST_UPDATE_DATE;
p_adjustment_rec.last_updated_by := i.LAST_UPDATED_BY;
p_adjustment_rec.delete_mark := i.DELETE_MARK;
p_adjustment_rec.program_update_date := i.PROGRAM_UPDATE_DATE;