[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT conversion_rate
INTO l_rate
FROM gl_daily_rates
WHERE from_currency = p_currency_code AND
to_currency = p_func_currency_code AND
conversion_type = p_rate_type AND
conversion_date = p_rate_date;
INSERT INTO
igc_cc_process_exceptions
( process_type,
process_phase,
cc_header_id,
cc_acct_line_id,
cc_det_pf_line_id,
exception_reason,
org_id,
set_of_books_id,
request_id
)
VALUES
( 'R',
p_process_phase,
NULL,
NULL,
NULL,
l_message,
p_org_id,
p_sob_id,
p_request_id
);
SELECT gps.closing_status
INTO l_period_status
FROM gl_period_statuses gps,
gl_periods gp,
gl_sets_of_books gb,
gl_period_types gpt,
fnd_application fa
WHERE
gb.set_of_books_id = p_sob_id AND
gp.period_set_name = gb.period_set_name AND
gp.period_type = gb.accounted_period_type AND
/* begin fix for bug 1569051*/
gp.adjustment_period_flag = 'N' AND
/* end fix for bug 1569051*/
gpt.period_type = gp.period_type AND
gps.set_of_books_id = gb.set_of_books_id AND
gps.period_name = gp.period_name AND
gps.application_id = fa.application_id AND
fa.application_short_name = 'SQLGL' AND
(gp.start_date <= p_rate_date AND gp.end_date >= p_rate_date);
INSERT INTO
igc_cc_process_exceptions
( process_type,
process_phase,
cc_header_id,
cc_acct_line_id,
cc_det_pf_line_id,
exception_reason,
org_id,
set_of_books_id,
request_id
)
VALUES
( 'R',
p_process_phase,
NULL,
NULL,
NULL,
l_message,
p_org_id,
p_sob_id,
p_request_id
);
SELECT ccp.cc_period_status
INTO l_cc_period_status
FROM igc_cc_periods ccp,
gl_periods gp ,
gl_sets_of_books gb
WHERE
ccp.period_set_name = gp.period_set_name AND
gp.period_set_name = gb.period_set_name AND
ccp.org_id = p_org_id AND
ccp.period_name = gp.period_name AND
gp.period_type = gb.accounted_period_type AND
gp.adjustment_period_flag = 'N' AND
gb.set_of_books_id = p_sob_id AND
(gp.start_date <= p_rate_date AND gp.end_date >= p_rate_date);
INSERT INTO
igc_cc_process_exceptions
( process_type,
process_phase,
cc_header_id,
cc_acct_line_id,
cc_det_pf_line_id,
exception_reason,
org_id,
set_of_books_id,
request_id
)
VALUES
( 'R',
p_process_phase,
NULL,
NULL,
NULL,
l_message,
p_org_id,
p_sob_id,
p_request_id
);
SELECT cc_num
INTO l_cc_num
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
INSERT
INTO igc_cc_process_exceptions
( process_type,
process_phase,
cc_header_id,
cc_acct_line_id,
cc_det_pf_line_id,
exception_reason,
org_id,
set_of_books_id,
request_id
)
VALUES
( 'R',
'F',
p_cc_header_id,
NULL,
NULL,
l_message,
p_org_id,
p_sob_id,
p_request_id
);
INSERT
INTO igc_cc_process_exceptions
( process_type,
process_phase,
cc_header_id,
cc_acct_line_id,
cc_det_pf_line_id,
exception_reason,
org_id,
set_of_books_id,
request_id
)
VALUES
( 'R',
'F',
p_cc_header_id,
NULL,
NULL,
l_message,
p_org_id,
p_sob_id,
p_request_id
);
FUNCTION reval_update(p_cc_header_id IN NUMBER,
p_rate_date IN DATE,
p_rate IN NUMBER,
p_sob_id IN NUMBER,
p_org_id IN NUMBER,
p_sbc_on IN BOOLEAN,
p_cbc_on IN BOOLEAN,
p_prov_enc_on IN BOOLEAN,
p_conf_enc_on IN BOOLEAN,
p_validate_only IN VARCHAR2,
p_request_id IN NUMBER,
p_message OUT NOCOPY VARCHAR2,
p_err_header_id OUT NOCOPY NUMBER,
p_err_acct_line_id OUT NOCOPY NUMBER,
p_err_det_pf_line_id OUT NOCOPY NUMBER
)
RETURN VARCHAR2
IS
l_cc_headers_rec igc_cc_headers%ROWTYPE;
SELECT *
FROM igc_cc_acct_lines_v
WHERE cc_header_id = p_cc_header_id;
SELECT ccal.ROWID,
ccal.cc_header_id,
NULL org_id,
NULL cc_type,
NULL cc_type_code,
NULL cc_num,
ccal.cc_acct_line_id,
ccal.cc_acct_line_num,
ccal.cc_acct_desc,
ccal.parent_header_id,
ccal.parent_acct_line_id,
NULL parent_cc_acct_line_num,
NULL cc_budget_acct_desc,
ccal.cc_budget_code_combination_id,
NULL cc_charge_acct_desc,
ccal.cc_charge_code_combination_id,
ccal.cc_acct_entered_amt,
ccal.cc_acct_func_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_func_billed_amt,
ccal.cc_acct_encmbrnc_amt,
( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0) ) - NVL(ccal.cc_acct_encmbrnc_amt,0) ) cc_acct_unencmrd_amt,
ccal.cc_acct_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0)) cc_acct_comp_func_amt,
NULL project_number ,
ccal.project_id,
NULL task_number,
ccal.task_id,
ccal.expenditure_type,
NULL expenditure_org_name,
ccal.expenditure_org_id,
ccal.expenditure_item_date,
ccal.cc_acct_taxable_flag,
NULL tax_name,
NULL tax_id, -- Bug 6472296 Ebtax uptake for cc
ccal.cc_acct_encmbrnc_status,
ccal.cc_acct_encmbrnc_date,
ccal.context,
ccal.attribute1,
ccal.attribute2,
ccal.attribute3,
ccal.attribute4,
ccal.attribute5,
ccal.attribute6,
ccal.attribute7,
ccal.attribute8,
ccal.attribute9,
ccal.attribute10,
ccal.attribute11,
ccal.attribute12,
ccal.attribute13,
ccal.attribute14,
ccal.attribute15,
ccal.created_by,
ccal.creation_date,
ccal.last_updated_by,
ccal.last_update_date,
ccal.last_update_login,
ccal.cc_func_withheld_amt,
ccal.cc_ent_withheld_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id,
NVL(ccal.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
ccal.tax_classif_code -- added Bug 6472296 Ebtax uptake for cc
FROM igc_cc_acct_lines ccal
WHERE ccal.cc_header_id = p_cc_header_id;
-- SELECT *
-- FROM igc_cc_det_pf_v
-- WHERE cc_acct_line_id = p_cc_acct_line_id;
SELECT ccdpf.ROWID,
ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
NULL cc_acct_line_num,
ccdpf.cc_acct_line_id,
NULL parent_det_pf_line_num,
ccdpf.parent_det_pf_line_id,
ccdpf.parent_acct_line_id,
ccdpf.cc_det_pf_entered_amt,
ccdpf.cc_det_pf_func_amt,
ccdpf.cc_det_pf_date,
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
ccdpf.cc_det_pf_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
ccdpf.cc_det_pf_encmbrnc_amt,
( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
ccdpf.cc_det_pf_encmbrnc_date,
ccdpf.cc_det_pf_encmbrnc_status,
ccdpf.context,
ccdpf.attribute1,
ccdpf.attribute2,
ccdpf.attribute3,
ccdpf.attribute4,
ccdpf.attribute5,
ccdpf.attribute6,
ccdpf.attribute7,
ccdpf.attribute8,
ccdpf.attribute9,
ccdpf.attribute10,
ccdpf.attribute11,
ccdpf.attribute12,
ccdpf.attribute13,
ccdpf.attribute14,
ccdpf.attribute15,
ccdpf.last_update_date,
ccdpf.last_updated_by,
ccdpf.last_update_login,
ccdpf.creation_date,
ccdpf.created_by
FROM igc_cc_det_pf ccdpf
WHERE cc_acct_line_id = p_cc_acct_line_id
AND ccdpf.cc_det_pf_entered_amt <> IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id,ccdpf.cc_det_pf_line_num,
ccdpf.cc_acct_line_id); -- Bug 3856265
l_Last_Updated_By NUMBER;
l_Last_Update_Login NUMBER;
l_Last_Updated_By := FND_GLOBAL.USER_ID;
l_Last_Update_Login := FND_GLOBAL.LOGIN_ID;
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg1',
' IGCCREPB -- Reval Update Begins ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
SELECT old_approval_status
INTO l_approval_status
FROM igc_cc_process_data
WHERE cc_header_id = p_cc_header_id AND
request_id = p_request_id ;
/* Update Header History */
l_return_status := FND_API.G_RET_STS_SUCCESS;
IGC_CC_HEADER_HISTORY_PKG.Insert_Row(
l_api_version,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_hist_hdr_row_id,
l_cc_headers_rec.CC_HEADER_ID,
l_cc_headers_rec.ORG_ID,
l_cc_headers_rec.CC_TYPE,
l_cc_headers_rec.CC_NUM,
l_cc_headers_rec.CC_VERSION_NUM,
'R',
l_cc_headers_rec.CC_STATE,
l_cc_headers_rec.PARENT_HEADER_ID,
l_cc_headers_rec.CC_CTRL_STATUS,
l_cc_headers_rec.CC_ENCMBRNC_STATUS,
l_approval_status,
l_cc_headers_rec.VENDOR_ID,
l_cc_headers_rec.VENDOR_SITE_ID,
l_cc_headers_rec.VENDOR_CONTACT_ID,
l_cc_headers_rec.TERM_ID,
l_cc_headers_rec.LOCATION_ID,
l_cc_headers_rec.SET_OF_BOOKS_ID,
l_cc_headers_rec.CC_ACCT_DATE,
l_cc_headers_rec.CC_DESC,
l_cc_headers_rec.CC_START_DATE,
l_cc_headers_rec.CC_END_DATE,
l_cc_headers_rec.CC_OWNER_USER_ID,
l_cc_headers_rec.CC_PREPARER_USER_ID,
l_cc_headers_rec.CURRENCY_CODE,
l_cc_headers_rec.CONVERSION_TYPE,
l_cc_headers_rec.CONVERSION_DATE,
l_cc_headers_rec.CONVERSION_RATE,
l_cc_headers_rec.LAST_UPDATE_DATE,
l_cc_headers_rec.LAST_UPDATED_BY,
l_cc_headers_rec.LAST_UPDATE_LOGIN,
l_cc_headers_rec.CREATED_BY,
l_cc_headers_rec.CREATION_DATE,
l_cc_headers_rec.WF_ITEM_TYPE,
l_cc_headers_rec.WF_ITEM_KEY,
l_cc_headers_rec.CC_CURRENT_USER_ID,
l_cc_headers_rec.ATTRIBUTE1,
l_cc_headers_rec.ATTRIBUTE2,
l_cc_headers_rec.ATTRIBUTE3,
l_cc_headers_rec.ATTRIBUTE4,
l_cc_headers_rec.ATTRIBUTE5,
l_cc_headers_rec.ATTRIBUTE6,
l_cc_headers_rec.ATTRIBUTE7,
l_cc_headers_rec.ATTRIBUTE8,
l_cc_headers_rec.ATTRIBUTE9,
l_cc_headers_rec.ATTRIBUTE10,
l_cc_headers_rec.ATTRIBUTE11,
l_cc_headers_rec.ATTRIBUTE12,
l_cc_headers_rec.ATTRIBUTE13,
l_cc_headers_rec.ATTRIBUTE14,
l_cc_headers_rec.ATTRIBUTE15,
--bug 4137743 changed the position
l_cc_headers_rec.CONTEXT,
-- bug 2043221 ssmales - added Guarantee Flag argument in line below
l_cc_headers_rec.CC_GUARANTEE_FLAG,
G_FLAG);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_HEADER_HST_INSERT');
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg2',
' IGCCREPB -- Reval Update Ends 9 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
/* Update Account Line History*/
l_return_status := FND_API.G_RET_STS_SUCCESS;
IGC_CC_ACCT_LINE_HISTORY_PKG.Insert_Row(
l_api_version ,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_hist_acct_row_id,
l_cc_acct_lines_rec.CC_Acct_Line_Id,
l_cc_acct_lines_rec.CC_Header_Id,
l_cc_acct_lines_rec.Parent_Header_Id,
l_cc_acct_lines_rec.Parent_Acct_Line_Id ,
l_cc_acct_lines_rec.CC_Acct_Line_Num,
l_cc_headers_rec.cc_version_num,
'U',
l_cc_acct_lines_rec.CC_Charge_Code_Combination_Id,
l_cc_acct_lines_rec.CC_Budget_Code_Combination_Id,
l_cc_acct_lines_rec.CC_Acct_Entered_Amt ,
l_cc_acct_lines_rec.CC_Acct_Func_Amt,
l_cc_acct_lines_rec.CC_Acct_Desc ,
l_cc_acct_lines_rec.CC_Acct_Billed_Amt ,
l_cc_acct_lines_rec.CC_Acct_Unbilled_Amt,
l_cc_acct_lines_rec.CC_Acct_Taxable_Flag,
l_cc_acct_lines_rec.Tax_Id,
l_cc_acct_lines_rec.CC_Acct_Encmbrnc_Amt,
l_cc_acct_lines_rec.CC_Acct_Encmbrnc_Date,
l_cc_acct_lines_rec.CC_Acct_Encmbrnc_Status,
l_cc_acct_lines_rec.Project_Id,
l_cc_acct_lines_rec.Task_Id,
l_cc_acct_lines_rec.Expenditure_Type,
l_cc_acct_lines_rec.Expenditure_Org_Id,
l_cc_acct_lines_rec.Expenditure_Item_Date,
l_cc_acct_lines_rec.Last_Update_Date,
l_cc_acct_lines_rec.Last_Updated_By,
l_cc_acct_lines_rec.Last_Update_Login ,
l_cc_acct_lines_rec.Creation_Date ,
l_cc_acct_lines_rec.Created_By ,
l_cc_acct_lines_rec.Attribute1,
l_cc_acct_lines_rec.Attribute2,
l_cc_acct_lines_rec.Attribute3,
l_cc_acct_lines_rec.Attribute4,
l_cc_acct_lines_rec.Attribute5,
l_cc_acct_lines_rec.Attribute6,
l_cc_acct_lines_rec.Attribute7,
l_cc_acct_lines_rec.Attribute8,
l_cc_acct_lines_rec.Attribute9,
l_cc_acct_lines_rec.Attribute10 ,
l_cc_acct_lines_rec.Attribute11,
l_cc_acct_lines_rec.Attribute12,
l_cc_acct_lines_rec.Attribute13,
l_cc_acct_lines_rec.Attribute14,
l_cc_acct_lines_rec.Attribute15,
l_cc_acct_lines_rec.Context,
-- bug 2043221 ssmales - added argument for Ent Withheld and Func Withheld amts in 2 lines below
l_cc_acct_lines_rec.CC_Func_Withheld_Amt,
l_cc_acct_lines_rec.CC_Ent_Withheld_Amt,
G_FLAG,
l_cc_acct_lines_rec.tax_classif_code -- added for Bug 6472296 Ebtax uptake for cc
);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACT_LINE_HST_INSERT');
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg3',
' IGCCREPB -- Reval Update Ends 8 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
/* Update cc_acct_encmbrnc_date,cc_acct_encmbrnc_amt depending on budgetary control set up */
IF ( ( (l_cc_headers_rec.cc_state = 'PR') OR (l_cc_headers_rec.cc_state = 'CL') )
AND (p_cbc_on = TRUE) AND (p_prov_enc_on = TRUE)
) OR
( ( (l_cc_headers_rec.cc_state = 'CM') OR (l_cc_headers_rec.cc_state = 'CT') )
AND (p_cbc_on = TRUE) AND (p_conf_enc_on = TRUE)
)
THEN
l_cc_acct_encmbrnc_date := p_rate_date;
SELECT rowid
INTO l_acct_row_id
FROM igc_cc_acct_lines
WHERE cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
IGC_CC_ACCT_LINES_PKG.Update_Row(
l_api_version ,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_acct_row_id,
l_cc_acct_lines_rec.CC_Acct_Line_Id,
l_cc_acct_lines_rec.CC_Header_Id,
l_cc_acct_lines_rec.Parent_Header_Id,
l_cc_acct_lines_rec.Parent_Acct_Line_Id ,
l_cc_acct_lines_rec.CC_Charge_Code_Combination_Id,
l_cc_acct_lines_rec.CC_Acct_Line_Num,
l_cc_acct_lines_rec.CC_Budget_Code_Combination_Id,
l_cc_acct_lines_rec.CC_Acct_Entered_Amt ,
l_cc_acct_func_amt,
l_cc_acct_lines_rec.CC_Acct_Desc ,
l_cc_acct_lines_rec.CC_Acct_Billed_Amt ,
l_cc_acct_lines_rec.CC_Acct_Unbilled_Amt,
l_cc_acct_lines_rec.CC_Acct_Taxable_Flag,
l_cc_acct_lines_rec.Tax_Id,
l_cc_acct_encmbrnc_amt,
l_cc_acct_encmbrnc_date,
l_cc_acct_lines_rec.CC_Acct_Encmbrnc_Status,
l_cc_acct_lines_rec.Project_Id,
l_cc_acct_lines_rec.Task_Id,
l_cc_acct_lines_rec.Expenditure_Type,
l_cc_acct_lines_rec.Expenditure_Org_Id,
l_cc_acct_lines_rec.Expenditure_Item_Date,
sysdate,
l_Last_Updated_By,
l_Last_Update_Login ,
l_cc_acct_lines_rec.Creation_Date ,
l_cc_acct_lines_rec.Created_By ,
l_cc_acct_lines_rec.Attribute1,
l_cc_acct_lines_rec.Attribute2,
l_cc_acct_lines_rec.Attribute3,
l_cc_acct_lines_rec.Attribute4,
l_cc_acct_lines_rec.Attribute5,
l_cc_acct_lines_rec.Attribute6,
l_cc_acct_lines_rec.Attribute7,
l_cc_acct_lines_rec.Attribute8,
l_cc_acct_lines_rec.Attribute9,
l_cc_acct_lines_rec.Attribute10 ,
l_cc_acct_lines_rec.Attribute11,
l_cc_acct_lines_rec.Attribute12,
l_cc_acct_lines_rec.Attribute13,
l_cc_acct_lines_rec.Attribute14,
l_cc_acct_lines_rec.Attribute15,
l_cc_acct_lines_rec.Context,
l_cc_func_withheld_amt,
l_cc_acct_lines_rec.CC_Ent_Withheld_Amt,
G_FLAG,
l_cc_acct_lines_rec.tax_classif_code);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACT_LINES_UPDATE');
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg4',
' IGCCREPB -- Reval Update Ends 7 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
/* Update PF Line History */
l_return_status := FND_API.G_RET_STS_SUCCESS;
IGC_CC_DET_PF_HISTORY_PKG.Insert_Row(
l_api_version,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_hist_pf_row_id,
l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id,
l_cc_pmt_fcst_rec.CC_Det_PF_Line_Num,
l_cc_pmt_fcst_rec.CC_Acct_Line_Id,
l_cc_pmt_fcst_rec.Parent_Acct_Line_Id,
l_cc_pmt_fcst_rec.Parent_Det_PF_Line_Id,
l_cc_headers_rec.cc_version_num,
'U',
l_cc_pmt_fcst_rec.CC_Det_PF_Entered_Amt,
l_cc_pmt_fcst_rec.CC_Det_PF_Func_Amt,
l_cc_pmt_fcst_rec.CC_Det_PF_Date,
l_cc_pmt_fcst_rec.CC_Det_PF_Billed_Amt,
l_cc_pmt_fcst_rec.CC_Det_PF_Unbilled_Amt,
l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Amt,
l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Date,
l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Status,
l_cc_pmt_fcst_rec.Last_Update_Date,
l_cc_pmt_fcst_rec.Last_Updated_By,
l_cc_pmt_fcst_rec.Last_Update_Login,
l_cc_pmt_fcst_rec.Creation_Date,
l_cc_pmt_fcst_rec.Created_By,
l_cc_pmt_fcst_rec.Attribute1,
l_cc_pmt_fcst_rec.Attribute2,
l_cc_pmt_fcst_rec.Attribute3,
l_cc_pmt_fcst_rec.Attribute4,
l_cc_pmt_fcst_rec.Attribute5,
l_cc_pmt_fcst_rec.Attribute6,
l_cc_pmt_fcst_rec.Attribute7,
l_cc_pmt_fcst_rec.Attribute8,
l_cc_pmt_fcst_rec.Attribute9,
l_cc_pmt_fcst_rec.Attribute10,
l_cc_pmt_fcst_rec.Attribute11,
l_cc_pmt_fcst_rec.Attribute12,
l_cc_pmt_fcst_rec.Attribute13,
l_cc_pmt_fcst_rec.Attribute14,
l_cc_pmt_fcst_rec.Attribute15,
l_cc_pmt_fcst_rec.Context,
G_FLAG );
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_DET_PF_HST_INSERT');
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg5',
' IGCCREPB -- Reval Update Ends 6 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
SELECT rowid
INTO l_pf_row_id
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = l_cc_pmt_fcst_rec.cc_det_pf_line_id;
IGC_CC_DET_PF_PKG.Update_Row(
l_api_version,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_pf_row_id,
l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id,
l_cc_pmt_fcst_rec.CC_Det_PF_Line_Num,
l_cc_pmt_fcst_rec.CC_Acct_Line_Id,
l_cc_pmt_fcst_rec.Parent_Acct_Line_Id,
l_cc_pmt_fcst_rec.Parent_Det_PF_Line_Id,
l_cc_pmt_fcst_rec.CC_Det_PF_Entered_Amt,
l_cc_det_pf_func_amt,
l_cc_det_pf_date,
l_cc_pmt_fcst_rec.CC_Det_PF_Billed_Amt,
l_cc_pmt_fcst_rec.CC_Det_PF_Unbilled_Amt,
l_cc_det_pf_encmbrnc_amt,
l_cc_det_pf_encmbrnc_date,
l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Status,
sysdate,
l_Last_Updated_By,
l_last_Update_Login,
l_cc_pmt_fcst_rec.Creation_Date,
l_cc_pmt_fcst_rec.Created_By,
l_cc_pmt_fcst_rec.Attribute1,
l_cc_pmt_fcst_rec.Attribute2,
l_cc_pmt_fcst_rec.Attribute3,
l_cc_pmt_fcst_rec.Attribute4,
l_cc_pmt_fcst_rec.Attribute5,
l_cc_pmt_fcst_rec.Attribute6,
l_cc_pmt_fcst_rec.Attribute7,
l_cc_pmt_fcst_rec.Attribute8,
l_cc_pmt_fcst_rec.Attribute9,
l_cc_pmt_fcst_rec.Attribute10,
l_cc_pmt_fcst_rec.Attribute11,
l_cc_pmt_fcst_rec.Attribute12,
l_cc_pmt_fcst_rec.Attribute13,
l_cc_pmt_fcst_rec.Attribute14,
l_cc_pmt_fcst_rec.Attribute15,
l_cc_pmt_fcst_rec.Context,
G_FLAG );
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_DET_PF_UPDATE');
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg6',
' IGCCREPB -- Reval Update Ends 5 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
Update Accounting date at CC Header if Account Date <= rate_date
*/
IF ( ( (l_cc_headers_rec.cc_state = 'PR') OR (l_cc_headers_rec.cc_state = 'CL') )
AND (p_cbc_on = TRUE) AND (p_prov_enc_on = TRUE)
) OR
( ( (l_cc_headers_rec.cc_state = 'CM') OR (l_cc_headers_rec.cc_state = 'CT') )
AND (p_cbc_on = TRUE) AND (p_conf_enc_on = TRUE)
)
THEN
IF (l_cc_headers_rec.cc_acct_date IS NOT NULL)
THEN
IF (l_cc_headers_rec.cc_acct_date <= p_rate_date)
THEN
l_cc_acct_date := p_rate_date;
SELECT rowid
INTO l_hdr_row_id
FROM igc_cc_headers
WHERE CC_HEADER_ID = l_cc_headers_rec.cc_header_id;
IGC_CC_HEADERS_PKG.Update_Row(
l_api_version,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_hdr_row_id,
l_cc_headers_rec.CC_HEADER_ID,
l_cc_headers_rec.ORG_ID,
l_cc_headers_rec.CC_TYPE,
l_cc_headers_rec.CC_NUM,
l_cc_version_num,
l_cc_headers_rec.PARENT_HEADER_ID,
l_cc_headers_rec.CC_STATE,
l_cc_headers_rec.CC_CTRL_STATUS,
l_cc_headers_rec.CC_ENCMBRNC_STATUS,
l_cc_apprvl_status,
l_cc_headers_rec.VENDOR_ID,
l_cc_headers_rec.VENDOR_SITE_ID,
l_cc_headers_rec.VENDOR_CONTACT_ID,
l_cc_headers_rec.TERM_ID,
l_cc_headers_rec.LOCATION_ID,
l_cc_headers_rec.SET_OF_BOOKS_ID,
l_cc_acct_date,
l_cc_headers_rec.CC_DESC,
l_cc_headers_rec.CC_START_DATE,
l_cc_headers_rec.CC_END_DATE,
l_cc_headers_rec.CC_OWNER_USER_ID,
l_cc_headers_rec.CC_PREPARER_USER_ID,
l_cc_headers_rec.CURRENCY_CODE,
l_cc_headers_rec.CONVERSION_TYPE,
l_conversion_date,
l_conversion_rate,
sysdate,
l_LAST_UPDATED_BY,
l_LAST_UPDATE_LOGIN,
l_cc_headers_rec.CREATED_BY,
l_cc_headers_rec.CREATION_DATE,
l_cc_headers_rec.CC_CURRENT_USER_ID,
l_cc_headers_rec.WF_ITEM_TYPE,
l_cc_headers_rec.WF_ITEM_KEY,
l_cc_headers_rec.ATTRIBUTE1,
l_cc_headers_rec.ATTRIBUTE2,
l_cc_headers_rec.ATTRIBUTE3,
l_cc_headers_rec.ATTRIBUTE4,
l_cc_headers_rec.ATTRIBUTE5,
l_cc_headers_rec.ATTRIBUTE6,
l_cc_headers_rec.ATTRIBUTE7,
l_cc_headers_rec.ATTRIBUTE8,
l_cc_headers_rec.ATTRIBUTE9,
l_cc_headers_rec.ATTRIBUTE10,
l_cc_headers_rec.ATTRIBUTE11,
l_cc_headers_rec.ATTRIBUTE12,
l_cc_headers_rec.ATTRIBUTE13,
l_cc_headers_rec.ATTRIBUTE14,
l_cc_headers_rec.ATTRIBUTE15,
l_cc_headers_rec.CONTEXT,
l_cc_headers_rec.CC_GUARANTEE_FLAG,
G_FLAG);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_HEADERS_UPDATE');
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg7',
' IGCCREPB -- Reval Update Ends 4 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
UPDATE igc_cc_headers
SET cc_apprvl_status = l_approval_status
WHERE cc_header_id = p_cc_header_id;
/* Update Corresponding PO */
IF ( (l_cc_headers_rec.cc_type = 'S') OR
(l_cc_headers_rec.cc_type = 'R') ) AND
/* Changed l_cc_headers_rec.cc_apprvl_status to l_approval_status in the following line to fix bug 1613811 */
( ( (l_cc_headers_rec.cc_state = 'CM') AND (l_approval_status = 'AP') ) OR
(l_cc_headers_rec.cc_state = 'CT') )
THEN
l_return_status := FND_API.G_RET_STS_SUCCESS;
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg8',
' IGCCREPB -- Reval Update Ends 3 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
IGC_CC_ACTIONS_PKG.Insert_Row(
l_api_version,
l_init_msg_list,
l_commit,
l_validation_level,
l_return_status,
l_msg_count,
l_msg_data,
l_action_row_id,
l_cc_headers_rec.CC_HEADER_ID,
l_cc_version_num,
'RP',
l_cc_headers_rec.CC_STATE,
l_cc_headers_rec.CC_CTRL_STATUS,
l_cc_apprvl_status,
l_action_hist_msg,
Sysdate,
l_Last_Updated_By,
l_Last_Update_Login,
Sysdate,
l_Created_By);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACTION_HST_INSERT');
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg9',
' IGCCREPB -- Reval Update Ends 1 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
/* Update validation status, in temporary table*/
UPDATE igc_cc_process_data
SET
processed = 'Y'
WHERE request_id = p_request_id AND
cc_header_id = P_cc_header_id ;
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg10',
' IGCCREPB -- Reval Update Ends 2 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg11',
' IGCCREPB -- Reval Update Ends 10 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
END reval_update;
/* Update validation_status to 'F' in temporary table for releases */
-- IF l_debug_mode = 'Y' THEN
-- Output_Debug (' IGCCREPB -- Populate Error Begins ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
UPDATE igc_cc_process_data a
SET a.validation_status = 'F'
WHERE a.cc_header_id = p_cc_header_id AND
request_id = p_request_id;
INSERT
INTO igc_cc_process_exceptions
(process_type,
process_phase,
cc_header_id,
cc_acct_line_id,
cc_det_pf_line_id,
exception_reason,
org_id,
set_of_books_id,
request_id)
SELECT 'R',
p_process_phase,
b.cc_header_id,
NULL,
NULL,
l_message,
p_org_id,
p_sob_id,
p_request_id
FROM igc_cc_headers b,
igc_cc_process_data a
WHERE
NVL(b.parent_header_id,0) = p_cc_header_id AND
b.cc_header_id = a.cc_header_id AND
b.currency_code = p_currency_code AND
b.conversion_type = p_rate_type AND
a.request_id = p_request_id;
SELECT *
FROM igc_cc_headers a
WHERE
( (a.cc_state = 'PR') OR
(a.cc_state = 'CM') OR
( (a.cc_state = 'CT') AND (a.cc_apprvl_status <> 'AP')) OR
( (a.cc_state = 'CL') AND (a.cc_apprvl_status <> 'AP'))
) AND
(
( (a.org_id = p_org_id AND
a.set_of_books_id = p_sob_id AND
a.currency_code = p_currency_code AND
( p_process_phase = 'P' OR
(p_process_phase = 'F' AND
a.conversion_rate <> NVL(p_rate, -99999))
) AND
a.conversion_type = p_rate_type AND
( (a.cc_type = 'C') OR
(a.cc_type = 'S') OR
( (a.cc_type = 'R') AND
EXISTS (SELECT 'x'
FROM igc_cc_headers b
WHERE b.cc_header_id = NVL(a.parent_header_id,0) AND
b.currency_code = p_func_currency_code)
)
)
) AND
p_cc_header_id IS NULL
)
OR
( (a.org_id = p_org_id AND
a.set_of_books_id = p_sob_id AND
a.currency_code = p_currency_code AND
a.cc_header_id = NVL(p_cc_header_id,-999999) AND
( p_process_phase = 'P' OR
(p_process_phase = 'F' AND
a.conversion_rate <> NVL(p_rate, -99999))
) AND
a.conversion_type = p_rate_type
) AND
p_cc_header_id IS NOT NULL
)
);
SELECT a.cc_header_id
FROM igc_cc_process_data a ,
igc_cc_headers b
WHERE a.request_id = p_request_id AND
a.cc_header_id = b.cc_header_id AND
/* bug 1622969 */
a.validation_status = 'I' AND
/* bug 1622969 */
b.cc_type = 'C';
SELECT a.cc_header_id
FROM igc_cc_process_data a ,
igc_cc_headers b
WHERE a.request_id = p_request_id AND
a.cc_header_id = b.cc_header_id AND
/* bug 1622969 */
(a.validation_status = 'I' OR a.validation_status = 'P') AND
/* bug 1622969 */
(b.cc_type = 'C' OR b.cc_type = 'S');
SELECT *
FROM igc_cc_headers
WHERE NVL(parent_header_id,0) = p_cc_header_id;
SELECT a.cc_header_id
FROM igc_cc_headers a
WHERE NVL(a.parent_header_id,0) = p_cc_header_id;
SELECT a.cc_header_id
FROM igc_cc_headers a
WHERE NVL(a.parent_header_id,0) = p_cc_header_id AND
a.currency_code = p_currency_code AND
a.conversion_type = p_rate_type ;
SELECT *
FROM igc_cc_acct_lines_v
WHERE cc_header_id = p_cc_header_id;
SELECT ccal.ROWID,
ccal.cc_header_id,
NULL org_id,
NULL cc_type,
NULL cc_type_code,
NULL cc_num,
ccal.cc_acct_line_id,
ccal.cc_acct_line_num,
ccal.cc_acct_desc,
ccal.parent_header_id,
ccal.parent_acct_line_id,
NULL parent_cc_acct_line_num,
NULL cc_budget_acct_desc,
ccal.cc_budget_code_combination_id,
NULL cc_charge_acct_desc,
ccal.cc_charge_code_combination_id,
ccal.cc_acct_entered_amt,
ccal.cc_acct_func_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_func_billed_amt,
ccal.cc_acct_encmbrnc_amt,
( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0) ) - NVL(ccal.cc_acct_encmbrnc_amt,0) ) cc_acct_unencmrd_amt,
ccal.cc_acct_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0)) cc_acct_comp_func_amt,
NULL project_number ,
ccal.project_id,
NULL task_number,
ccal.task_id,
ccal.expenditure_type,
NULL expenditure_org_name,
ccal.expenditure_org_id,
ccal.expenditure_item_date,
ccal.cc_acct_taxable_flag,
NULL tax_name,
NULL tax_id, -- added for Bug 6472296 Ebtax uptake for cc
ccal.cc_acct_encmbrnc_status,
ccal.cc_acct_encmbrnc_date,
ccal.context,
ccal.attribute1,
ccal.attribute2,
ccal.attribute3,
ccal.attribute4,
ccal.attribute5,
ccal.attribute6,
ccal.attribute7,
ccal.attribute8,
ccal.attribute9,
ccal.attribute10,
ccal.attribute11,
ccal.attribute12,
ccal.attribute13,
ccal.attribute14,
ccal.attribute15,
ccal.created_by,
ccal.creation_date,
ccal.last_updated_by,
ccal.last_update_date,
ccal.last_update_login,
ccal.cc_func_withheld_amt,
ccal.cc_ent_withheld_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id,
NVL(ccal.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
ccal.tax_classif_code -- added for Bug 6472296 Ebtax uptake for cc
FROM igc_cc_acct_lines ccal
WHERE cc_header_id = p_cc_header_id;
-- SELECT *
-- FROM igc_cc_det_pf_v
-- WHERE cc_acct_line_id = p_cc_acct_line_id;
SELECT ccdpf.ROWID,
ccdpf.cc_det_pf_line_id,
ccdpf.cc_det_pf_line_num,
NULL cc_acct_line_num,
ccdpf.cc_acct_line_id,
NULL parent_det_pf_line_num,
ccdpf.parent_det_pf_line_id,
ccdpf.parent_acct_line_id,
ccdpf.cc_det_pf_entered_amt,
ccdpf.cc_det_pf_func_amt,
ccdpf.cc_det_pf_date,
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
ccdpf.cc_det_pf_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
ccdpf.cc_det_pf_encmbrnc_amt,
( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
ccdpf.cc_det_pf_encmbrnc_date,
ccdpf.cc_det_pf_encmbrnc_status,
ccdpf.context,
ccdpf.attribute1,
ccdpf.attribute2,
ccdpf.attribute3,
ccdpf.attribute4,
ccdpf.attribute5,
ccdpf.attribute6,
ccdpf.attribute7,
ccdpf.attribute8,
ccdpf.attribute9,
ccdpf.attribute10,
ccdpf.attribute11,
ccdpf.attribute12,
ccdpf.attribute13,
ccdpf.attribute14,
ccdpf.attribute15,
ccdpf.last_update_date,
ccdpf.last_updated_by,
ccdpf.last_update_login,
ccdpf.creation_date,
ccdpf.created_by
FROM igc_cc_det_pf ccdpf
WHERE cc_acct_line_id = p_cc_acct_line_id
AND ccdpf.cc_det_pf_entered_amt <> IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id,ccdpf.cc_det_pf_line_num,
ccdpf.cc_acct_line_id); -- Bug 3856265
insert_data EXCEPTION;
SELECT meaning
INTO l_option_name
FROM igi_lookups
WHERE lookup_code = 'CC'
AND lookup_type = 'GCC_DESCRIPTION';
DELETE FROM igc_cc_interface
WHERE to_date(creation_date,'DD/MM/YYYY') <= to_date(sysdate ,'DD/MM/YYYY') - 2;
INSERT INTO
igc_cc_process_exceptions
(process_type,
process_phase,
cc_header_id,
cc_acct_line_id,
cc_det_pf_line_id,
exception_reason,
org_id,
set_of_books_id,
request_id)
VALUES
( 'R',
p_process_phase,
NULL,
NULL,
NULL,
l_usr_msg,
l_org_id,
l_sob_id,
l_request_id1);
' IGCCREPB -- Inserting CC type S record into process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
INSERT INTO igc_cc_process_data
(
process_type,
process_phase,
cc_header_id,
validation_status,
reservation_status,
processed,
old_approval_status,
org_id,
set_of_books_id,
validate_only,
request_id)
VALUES
( 'R',
p_process_phase,
l_cc_headers_rec.cc_header_id,
'I',
'F',
'N',
NULL,
l_org_id,
l_sob_id,
'Y',
l_request_id1);
SELECT NVL(processed,'N')
INTO l_processed
FROM igc_cc_process_data a
WHERE a.cc_header_id = NVL(l_cc_headers_rec.parent_header_id,0) AND
a.request_id = l_request_id1;
' IGCCREPB -- Inserting cover into process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
INSERT INTO igc_cc_process_data
(
process_type,
process_phase,
cc_header_id,
validation_status,
reservation_status,
processed,
old_approval_status,
org_id,
set_of_books_id,
validate_only,
request_id)
VALUES
( 'R',
p_process_phase,
l_cc_headers_rec.parent_header_id,
'I',
'F',
'N',
NULL,
l_org_id,
l_sob_id,
'Y',
l_request_id1);
' IGCCREPB -- Inserting release into process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
INSERT INTO igc_cc_process_data
(
process_type,
process_phase,
cc_header_id,
validation_status,
reservation_status,
processed,
old_approval_status,
org_id,
set_of_books_id,
validate_only,
request_id)
VALUES
( 'R',
p_process_phase,
l_cc_header_id,
'I',
'F',
'N',
NULL,
l_org_id,
l_sob_id,
'N',
l_request_id1);
INSERT INTO igc_cc_process_data
(
process_type,
process_phase,
cc_header_id,
validation_status,
reservation_status,
processed,
old_approval_status,
org_id,
set_of_books_id,
validate_only,
request_id)
VALUES
( 'R',
p_process_phase,
l_cc_header_id,
'I',
'F',
'N',
NULL,
l_org_id,
l_sob_id,
'Y',
l_request_id1);
UPDATE igc_cc_process_data a
SET
validate_only = 'N'
WHERE a.request_id = l_request_id1 AND
a.cc_header_id = l_cc_headers_rec.cc_header_id;
SELECT NVL(processed,'N')
INTO l_processed
FROM igc_cc_process_data a
WHERE a.cc_header_id = l_cc_headers_rec.cc_header_id AND
a.request_id = l_request_id1;
' IGCCREPB -- Inserting into process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
INSERT INTO igc_cc_process_data
(
process_type,
process_phase,
cc_header_id,
validation_status,
reservation_status,
processed,
old_approval_status,
org_id,
set_of_books_id,
validate_only,
request_id)
VALUES
( 'R',
p_process_phase,
l_cc_header_id,
'I',
'F',
'N',
NULL,
l_org_id,
l_sob_id,
'N',
l_request_id1);
' IGCCREPB -- Inserting cover into process data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
INSERT INTO igc_cc_process_data
(
process_type,
process_phase,
cc_header_id,
validation_status,
reservation_status,
processed,
old_approval_status,
org_id,
set_of_books_id,
validate_only,
request_id)
VALUES
( 'R',
p_process_phase,
l_cc_headers_rec.cc_header_id,
'I',
'F',
'N',
NULL,
l_org_id,
l_sob_id,
'N',
l_request_id1);
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = l_cc_header_id;
UPDATE igc_cc_process_data
SET validation_status = 'F',
processed = 'Y'
WHERE cc_header_id = l_cc_header_id AND
request_id = l_request_id1;
/* Update releases status in igc_cc_process_data */
UPDATE igc_cc_process_data
SET validation_status = 'F',
processed = 'Y'
WHERE cc_header_id = l_rel_cc_header_id AND
request_id = l_request_id1;
/* Update cover status in igc_cc_process_data */
UPDATE igc_cc_process_data
SET validation_status = 'F',
processed = 'Y'
WHERE cc_header_id = l_cc_header_id AND
request_id = l_request_id1;
/* Update cover status in igc_cc_process_data */
UPDATE igc_cc_process_data
SET validation_status = 'F',
processed = 'Y'
WHERE cc_header_id = l_cc_header_id AND
request_id = l_request_id1;
/* Update releases statuses in igc_cc_process_data */
UPDATE igc_cc_process_data
SET
validation_status = 'F' ,
processed = 'Y'
WHERE request_id = l_request_id1 AND
cc_header_id IN (SELECT cc_header_id
FROM igc_cc_headers
WHERE NVL(parent_header_id,0) = l_cc_header_id);
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = l_cc_header_id;
SELECT NVL(SUM(NVL(CC_ACCT_FUNC_AMT,0)),0)
INTO l_non_reval_acct_amt_total
FROM igc_cc_acct_lines a,
igc_cc_headers b
WHERE NVL(parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
a.cc_header_id = b.cc_header_id AND
NVL(b.parent_header_id,0) = l_cc_header_id AND
b.cc_header_id <> p_cc_header_id;
SELECT SUM(
NVL(a.cc_acct_func_amt,0) +
(
( ( NVL(a.cc_acct_func_amt,0) -
-- a.cc_acct_func_billed_amt)
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( a.cc_acct_line_id)) / b.conversion_rate
) * (l_rate - b.conversion_rate )
)
)
INTO l_reval_acct_amt_total
--FROM igc_cc_acct_lines_v a,
FROM igc_cc_acct_lines a,
igc_cc_headers b
WHERE NVL(parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
a.cc_header_id = b.cc_header_id AND
b.cc_header_id = p_cc_header_id;
SELECT validate_only
INTO l_validate_only
FROM igc_cc_process_data
WHERE request_id = l_request_id1 AND
cc_header_id = l_cc_headers_rec.cc_header_id;
SELECT NVL(SUM(NVL(CC_ACCT_FUNC_AMT,0)),0)
INTO l_non_reval_acct_amt_total
FROM igc_cc_acct_lines a,
igc_cc_headers b
WHERE NVL(parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
a.cc_header_id = b.cc_header_id AND
NVL(b.parent_header_id,0) = l_cc_header_id AND
( (b.currency_code = p_currency_code AND b.conversion_type <> p_rate_type) OR
(b.currency_code <> p_currency_code) ) ;
SELECT SUM(
NVL(a.cc_acct_func_amt,0) +
(
( ( NVL(a.cc_acct_func_amt,0) -
-- a.cc_acct_func_billed_amt)
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( a.cc_acct_line_id)) / b.conversion_rate
) * (l_rate - b.conversion_rate )
)
)
INTO l_reval_acct_amt_total
--FROM igc_cc_acct_lines_v a,
FROM igc_cc_acct_lines a,
igc_cc_headers b
WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
a.cc_header_id = b.cc_header_id AND
NVL(b.parent_header_id,0) = l_cc_header_id AND
( (b.currency_code = p_currency_code) AND
( b.conversion_type = p_rate_type));
SELECT SUM(NVL(CC_ACCT_FUNC_AMT,0))
INTO l_non_reval_acct_amt_total
FROM igc_cc_acct_lines a,
igc_cc_headers b
WHERE NVL(parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
NVL(b.parent_header_id,0) = l_cc_header_id AND
a.cc_header_id = b.cc_header_id AND
( (b.currency_code = p_currency_code AND b.conversion_type <> p_rate_type) OR
(b.currency_code <> p_currency_code) ) ;
SELECT SUM(
NVL(a.cc_acct_func_amt,0) +
(
( ( NVL(a.cc_acct_func_amt,0) -
--a.cc_acct_func_billed_amt)
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( a.cc_acct_line_id)) / b.conversion_rate
) * (l_rate - b.conversion_rate )
)
)
INTO l_reval_acct_amt_total
--FROM igc_cc_acct_lines_v a,
FROM igc_cc_acct_lines a,
igc_cc_headers b
WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
a.cc_header_id = b.cc_header_id AND
( (b.currency_code = p_currency_code) AND
( b.conversion_type = p_rate_type));
SELECT NVL(SUM(NVL(CC_DET_PF_FUNC_AMT,0)),0)
INTO l_non_reval_pf_amt_total
FROM igc_cc_det_pf a,
igc_cc_acct_lines b,
igc_cc_headers c
WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
NVL(a.parent_det_pf_line_id,0) = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
a.cc_acct_line_id = b.cc_acct_line_id AND
b.cc_header_id = c.cc_header_id AND
NVL(c.parent_header_id,0) = l_cc_header_id AND
c.cc_header_id <> p_cc_header_id;
SELECT SUM( NVL(a.cc_det_pf_func_amt,0) +
(
(
( NVL(a.cc_det_pf_func_amt,0) -
NVL(IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(a.cc_det_pf_line_id, a.cc_det_pf_line_num, a.cc_acct_line_id),0)
) / c.conversion_rate
) * (l_rate - c.conversion_rate)
)
)
INTO l_reval_pf_amt_total
FROM igc_cc_det_pf a,
igc_cc_acct_lines b,
igc_cc_headers c
WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
NVL(a.parent_det_pf_line_id,0) = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
a.cc_acct_line_id = b.cc_acct_line_id AND
b.cc_header_id = c.cc_header_id AND
c.org_id = l_org_id AND
c.cc_header_id = p_cc_header_id;
SELECT SUM(NVL(CC_DET_PF_FUNC_AMT,0))
INTO l_non_reval_pf_amt_total
FROM igc_cc_det_pf a,
igc_cc_acct_lines b,
igc_cc_headers c
WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
NVL(a.parent_det_pf_line_id,0) = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
NVL(c.parent_header_id,0) = l_cc_header_id AND
a.cc_acct_line_id = b.cc_acct_line_id AND
b.cc_header_id = c.cc_header_id AND
( (c.currency_code = p_currency_code AND c.conversion_type <> p_rate_type) OR
(c.currency_code <> p_currency_code) ) ;
SELECT SUM( NVL(a.cc_det_pf_func_amt,0) +
(
(
( NVL(a.cc_det_pf_func_amt,0) -
NVL(IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(a.cc_det_pf_line_id, a.cc_det_pf_line_num, a.cc_acct_line_id),0)
) / c.conversion_rate
) * (l_rate - c.conversion_rate)
)
)
INTO l_reval_pf_amt_total
FROM igc_cc_det_pf a,
igc_cc_acct_lines b,
igc_cc_headers c
WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
NVL(a.parent_det_pf_line_id,0) = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
NVL(c.parent_header_id,0) = l_cc_header_id AND
a.cc_acct_line_id = b.cc_acct_line_id AND
b.cc_header_id = c.cc_header_id AND
( c.currency_code = p_currency_code AND
c.conversion_type = p_rate_type) ;
SELECT NVL(SUM(NVL(CC_DET_PF_FUNC_AMT,0)),0)
INTO l_non_reval_pf_amt_total
FROM igc_cc_det_pf a,
igc_cc_acct_lines b,
igc_cc_headers c
WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
NVL(a.parent_det_pf_line_id,0) = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
NVL(c.parent_header_id,0) = l_cc_header_id AND
a.cc_acct_line_id = b.cc_acct_line_id AND
b.cc_header_id = c.cc_header_id AND
( (c.currency_code = p_currency_code AND c.conversion_type <> p_rate_type) OR
(c.currency_code <> p_currency_code) ) ;
SELECT SUM( NVL(a.cc_det_pf_func_amt,0) +
(
(
( NVL(a.cc_det_pf_func_amt,0) -
Nvl(IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(a.cc_det_pf_line_id, a.cc_det_pf_line_num, a.cc_acct_line_id),0)
) / c.conversion_rate
) * (l_rate - c.conversion_rate)
)
)
INTO l_reval_pf_amt_total
FROM igc_cc_det_pf a,
igc_cc_acct_lines b,
igc_cc_headers c
WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
NVL(a.parent_det_pf_line_id,0) = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
NVL(c.parent_header_id,0) = l_cc_header_id AND
a.cc_acct_line_id = b.cc_acct_line_id AND
b.cc_header_id = c.cc_header_id AND
( c.currency_code = p_currency_code AND
c.conversion_type = p_rate_type) ;
/* Update validation_status to 'F' in temporary table for releases */
-- IF l_debug_mode = 'Y' THEN
-- Output_Debug (' IGCCREPB -- Populate error PF amt ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = l_cc_header_id;
SELECT validation_status
INTO l_curr_validation_status
FROM igc_cc_process_data
WHERE cc_header_id = l_cc_header_id AND
request_id = l_request_id1;
/* Update validation status in temporary table*/
-- IF l_debug_mode = 'Y' THEN
-- Output_Debug (' IGCCREPB -- Preliminary Phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
UPDATE igc_cc_process_data
SET
validation_status = l_validation_Status,
processed = 'Y'
WHERE
request_id = l_request_id1 AND
cc_header_id = l_cc_header_id;
UPDATE igc_cc_process_data
SET
validation_status = l_validation_Status,
processed = 'Y'
WHERE
request_id = l_request_id1 AND
cc_header_id IN (SELECT cc_header_id
FROM igc_cc_headers
WHERE NVL(parent_header_id,0) = l_cc_header_id);
/* Update validation status, store old status in temporary table */
-- IF l_debug_mode = 'Y' THEN
-- Output_Debug (' IGCCREPB -- Passed validation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
UPDATE igc_cc_process_data
SET
validation_status = l_validation_Status,
old_approval_status = l_cc_headers_rec.cc_apprvl_status
WHERE
request_id = l_request_id1 AND
cc_header_id = l_cc_header_id;
UPDATE igc_cc_headers
SET cc_apprvl_status = 'IP'
WHERE cc_header_id = l_cc_header_id;
SELECT count(po_header_id)
INTO l_po_count
FROM po_headers_all
WHERE segment1 = l_cc_headers_rec.cc_num AND
org_id = l_cc_headers_rec.org_id AND
type_lookup_code = 'STANDARD' ;
UPDATE po_headers_all
SET approved_flag = 'N'
WHERE segment1 = l_cc_headers_rec.cc_num AND
org_id = l_cc_headers_rec.org_id AND
type_lookup_code = 'STANDARD' AND
approved_flag = 'Y';
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg78',' IGCCREPB -- Update process data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
UPDATE igc_cc_process_data
SET
validation_status = l_validation_Status,
old_approval_status = l_rel_cc_headers_rec.cc_apprvl_status
WHERE request_id = l_request_id1 AND
cc_header_id = l_rel_cc_headers_rec.cc_header_id;
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg79',' IGCCREPB -- Update Header data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
UPDATE igc_cc_headers
SET cc_apprvl_status = 'IP'
WHERE cc_header_id = l_rel_cc_headers_rec.cc_header_id ;
SELECT count(po_header_id)
INTO l_po_count
FROM po_headers_all
WHERE segment1 = l_rel_cc_headers_rec.cc_num AND
org_id = l_rel_cc_headers_rec.org_id AND
type_lookup_code = 'STANDARD' ;
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg81',' IGCCREPB -- Update PO Header data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
UPDATE po_headers_all
SET approved_flag = 'N'
WHERE segment1 = l_rel_cc_headers_rec.cc_num AND
org_id = l_rel_cc_headers_rec.org_id AND
type_lookup_code = 'STANDARD' AND
/* Changed condition below from approved_flag = Y to approved flag = N to fix bug 1613811 */
approved_flag = 'Y';
/* Update validation status, in temporary table*/
-- IF l_debug_mode = 'Y' THEN
-- Output_Debug (' IGCCREPB -- Failed validation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
UPDATE igc_cc_process_data
SET
validation_status = l_validation_Status ,
processed = 'Y'
WHERE request_id = l_request_id1 AND
cc_header_id = l_cc_header_id;
UPDATE igc_cc_process_data
SET
validation_status = l_validation_Status ,
processed = 'Y'
WHERE request_id = l_request_id1 AND
cc_header_id IN (SELECT cc_header_id
FROM igc_cc_headers
WHERE NVL(parent_header_id,0) = l_cc_header_id);
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = l_cc_header_id;
SELECT validation_status
INTO l_validation_status
FROM igc_cc_process_data
WHERE request_id = l_request_id1 AND
cc_header_id = l_cc_header_id ;
SELECT validate_only
INTO l_validate_only
FROM igc_cc_process_data
WHERE request_id = l_request_id1 AND
cc_header_id = l_cc_headers_rec.cc_header_id;
UPDATE igc_cc_headers
SET cc_version_num = cc_version_num + 1
WHERE cc_header_id = l_cc_header_id;
UPDATE igc_cc_headers
SET cc_version_num = cc_version_num - 1
WHERE cc_header_id = l_cc_header_id;
select old_approval_status
into l_approval_status
from igc_cc_process_data
where cc_header_id = l_cc_headers_rec.cc_header_id
and request_id = l_request_id1;
update igc_cc_headers
set cc_apprvl_status = l_approval_status
WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
INSERT INTO
igc_cc_process_exceptions
( process_type,
process_phase,
cc_header_id,
cc_acct_line_id,
cc_det_pf_line_id,
exception_reason,
org_id,
set_of_books_id,
request_id
)
VALUES
(
'R',
'F',
l_cc_headers_rec.cc_header_id,
NULL,
NULL,
l_message,
l_org_id,
l_sob_id,
l_request_id1
);
/* Update validation status, in temporary table*/
-- IF l_debug_mode = 'Y' THEN
-- Output_Debug (' IGCCREPB -- Updating process data after encumber CC ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
UPDATE igc_cc_process_data
SET
reservation_status = l_reservation_Status
WHERE
request_id = l_request_id1 AND
cc_header_id = l_cc_header_id ;
UPDATE igc_cc_process_data
SET
reservation_status = l_reservation_Status
WHERE
request_id = l_request_id1 AND
cc_header_id IN (SELECT cc_header_id
FROM igc_cc_headers
WHERE NVL(parent_header_id,0) = l_cc_header_id);
SELECT validate_only
INTO l_validate_only
FROM igc_cc_process_data
WHERE request_id = l_request_id1 AND
cc_header_id = l_rel_cc_header_id;
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg96',' IGCCREPB -- Calling reval_update P ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
l_process_flag := reval_update(l_rel_cc_header_id,
l_rate_date,
p_rate,
l_sob_id,
l_org_id,
l_sbc_on,
l_cbc_on,
l_prov_enc_on,
l_conf_enc_on,
l_validate_only,
l_request_id1,
l_message,
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id);
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg97',' IGCCREPB -- Calling reval_update F ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
l_process_flag := reval_update(l_rel_cc_header_id,
l_rate_date,
l_rate,
l_sob_id,
l_org_id,
l_sbc_on,
l_cbc_on,
l_prov_enc_on,
l_conf_enc_on,
'Y',
l_request_id1,
l_message,
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id);
SELECT validate_only
INTO l_validate_only
FROM igc_cc_process_data
WHERE request_id = l_request_id1 AND
cc_header_id = l_cc_headers_rec.cc_header_id;
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg102','IGCCREPB -- Reval Cover Update P ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
l_process_flag := reval_update(l_cc_headers_rec.cc_header_id,
l_rate_date,
l_rate,
l_sob_id,
l_org_id,
l_sbc_on,
l_cbc_on,
l_prov_enc_on,
l_conf_enc_on,
l_validate_only,
l_request_id1,
l_message,
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id);
FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg103','IGCCREPB -- Reval Cover Update F ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
l_process_flag := reval_update(l_cc_headers_rec.cc_header_id,
l_rate_date,
l_rate,
l_sob_id,
l_org_id,
l_sbc_on,
l_cbc_on,
l_prov_enc_on,
l_conf_enc_on,
'Y',
l_request_id1,
l_message,
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES(
'R',
'F',
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id,
l_message,
l_ORG_ID,
l_SOB_ID,
l_REQUEST_ID1);
l_process_flag := reval_update(l_cc_headers_rec.cc_header_id,
l_rate_date,
l_rate,
l_sob_id,
l_org_id,
l_sbc_on,
l_cbc_on,
l_prov_enc_on,
l_conf_enc_on,
'N',
l_request_id1,
l_message,
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id);
l_process_flag := reval_update(l_cc_headers_rec.cc_header_id,
l_rate_date,
l_rate,
l_sob_id,
l_org_id,
l_sbc_on,
l_cbc_on,
l_prov_enc_on,
l_conf_enc_on,
'Y',
l_request_id1,
l_message,
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id);
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES(
'R',
'F',
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id,
l_message,
l_ORG_ID,
l_SOB_ID,
l_REQUEST_ID1);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_REP_NO_CC_SELECTED');
INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
(PROCESS_TYPE,
PROCESS_PHASE,
CC_HEADER_ID,
CC_ACCT_LINE_ID,
CC_DET_PF_LINE_ID,
EXCEPTION_REASON,
ORG_ID,
SET_OF_BOOKS_ID,
REQUEST_ID)
VALUES(
'R',
p_process_phase,
NULL,
NULL,
NULL,
l_message,
l_ORG_ID,
l_SOB_ID,
l_REQUEST_ID1);
WHEN insert_data
THEN
IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'revalue_main');