[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- SELECT *
-- FROM igc_cc_det_pf_v
-- WHERE cc_acct_line_id = t_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 = t_cc_acct_line_id;
-- view igc_cc_acct_lines_v. Instead of selecting from the view,
-- select is being done from the base table, but all the columns
-- as defined in the view are retained even though they are not used.
-- This is just so that minimal change is made to the code.
/*
SELECT *
FROM igc_cc_acct_lines_v ccac
WHERE ccac.cc_header_id = t_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 r12 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 /* Bug 6472296 for r12 EBtax uptake for CC */
FROM igc_cc_acct_lines ccal
WHERE ccal.cc_header_id = t_cc_header_id;
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_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
(
'F',
'F',
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 1569324 */
gp.adjustment_period_flag = 'N' AND
/* End fix for bug 1569324 */
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_revalue_fix_date AND gp.end_date >= p_revalue_fix_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
( 'F',
'F',
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_revalue_fix_date AND gp.end_date >= p_revalue_fix_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
( 'F',
'F',
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
( 'F',
'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
( 'F',
'F',
p_cc_header_id,
NULL,
NULL,
l_message,
p_org_id,
p_sob_id,
p_request_id
);
FUNCTION reval_fix_update(p_cc_header_id IN NUMBER,
p_rel_cc_header_id IN NUMBER,
p_revalue_fix_date IN DATE,
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 for r12 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 /* Bug No : 6341012. E-BTax uptake.New field is ebing added to Account_Lines Table R12 */
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 ccdpf.cc_acct_line_id = p_cc_acct_line_id;
l_Last_Updated_By NUMBER := FND_GLOBAL.USER_ID;
l_Last_Update_Login NUMBER := FND_GLOBAL.LOGIN_ID;
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 ;
SELECT conversion_rate
INTO l_rel_conversion_rate
FROM igc_cc_headers
WHERE cc_header_id = p_rel_cc_header_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,
-- Context should be after attributes, so moved below - ssmales 18/10/01
-- l_cc_headers_rec.CONTEXT,
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_HEADER_HST_INSERT');
SELECT
( ( ( a.cc_det_pf_entered_amt - IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(a.cc_det_pf_line_id, a.cc_det_pf_line_num, a.cc_acct_line_id))
* l_rel_conversion_rate
)
- (a.cc_det_pf_func_amt - 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))
)
INTO l_det_pf_func_amt_total
FROM igc_cc_det_pf a,
igc_cc_acct_lines b
WHERE a.parent_det_pf_line_id = 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 = p_rel_cc_header_id;
/* 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');
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_pmt_fcst_rec.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');
/* 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,
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 /* r12 EBtax uptake for CC */
);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACT_LINE_HST_INSERT');
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_lines_rec.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_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 /* Bug 6472296 for r12 EBtax uptake for CC */
);
FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACT_LINES_UPDATE');
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_cc_headers_rec.conversion_date,
l_cc_headers_rec.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');
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 to fix bug 1632984 */
( ( (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;
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,
'RF',
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');
/* 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 ;
END reval_fix_update;
/* Update validation_status to 'F' in temporary table for releases */
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 'F',
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
b.parent_header_id = p_cc_header_id AND
b.cc_header_id = a.cc_header_id AND
a.request_id = p_request_id;
SELECT *
FROM igc_cc_headers a
WHERE a.cc_header_id = p_cc_header_id;
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
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
(b.cc_type = 'C' OR b.cc_type = 'S');
SELECT *
FROM igc_cc_headers
WHERE parent_header_id = p_cc_header_id;
SELECT a.cc_header_id
FROM igc_cc_headers a
WHERE a.parent_header_id = p_cc_header_id;
SELECT a.cc_header_id
FROM igc_cc_headers a
WHERE a.parent_header_id = p_cc_header_id;
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 for r12 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 /* Bug No : 6341012. E-BTax uptake.New field is ebing added to Account_Lines Table R12 */
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 ccdpf.cc_acct_line_id = p_cc_acct_line_id;
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
( 'F',
p_process_phase,
NULL,
NULL,
NULL,
l_usr_msg,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
l_org_id,
l_sob_id,
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
( 'F',
p_process_phase,
l_cc_headers_rec.cc_header_id,
'I',
'F',
'N',
NULL,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
l_org_id,
l_sob_id,
'Y',
l_request_id1);
SELECT currency_code
INTO l_cover_currency_code
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.parent_header_id;
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
( 'F',
p_process_phase,
l_cc_headers_rec.parent_header_id,
'I',
'F',
'N',
NULL,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
l_org_id,
l_sob_id,
'Y',
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
( 'F',
p_process_phase,
l_cc_headers_rec.parent_header_id,
'I',
'F',
'N',
NULL,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
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
( 'F',
p_process_phase,
l_cc_header_id,
'I',
'F',
'N',
NULL,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
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
( 'F',
p_process_phase,
l_cc_header_id,
'I',
'F',
'N',
NULL,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
l_org_id,
l_sob_id,
'Y',
l_request_id1);
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = 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) +
( ( ( a.cc_acct_entered_amt -
--a.cc_acct_billed_amt)
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( a.cc_acct_line_id))
* b.conversion_rate
)
-
(a.cc_acct_func_amt -
-- a.cc_acct_func_billed_amt)
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( a.cc_acct_line_id))
)
)
INTO l_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
b.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
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) +
( ( ( a.cc_det_pf_entered_amt - IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(a.cc_det_pf_line_id, a.cc_det_pf_line_num, a.cc_acct_line_id))
* c.conversion_rate
)
-
(a.cc_det_pf_func_amt - 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))
)
)
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
/* Bug No : 6341012. R12 Uptake. p_org_id is changed to l_org_id */
c.org_id = l_org_id AND
c.cc_header_id = p_cc_header_id;
/* Update validation_status to 'F' in temporary table for releases */
populate_errors(l_cc_headers_rec.cc_header_id,
p_process_phase,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
l_sob_id,
l_org_id,
l_request_id1);
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*/
BEGIN
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 parent_header_id = l_cc_header_id);
/* Update validation status, store old status in temporary table */
IF (l_cc_headers_rec.cc_type = 'C') OR (l_cc_headers_rec.cc_type = 'S')
THEN
BEGIN
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';
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;
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' ;
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 statement below from approved_flag = N to approved_flag = Y to fix bug 1632984 */
approved_flag = 'Y';
/* Update validation status, in temporary table*/
IF (l_cc_headers_rec.cc_type = 'C') OR (l_cc_headers_rec.cc_type = 'S')
THEN
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 parent_header_id = l_cc_header_id);
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_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 = 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
( 'F',
'F',
l_cc_headers_rec.parent_header_id,
NULL,
NULL,
l_message,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
l_org_id,
l_sob_id,
l_request_id1
);
/* Update validation status, in temporary table*/
IF (l_cc_headers_rec.cc_type = 'S')
THEN
UPDATE igc_cc_process_data
SET
reservation_status = l_reservation_Status
WHERE
request_id = l_request_id1 AND
cc_header_id = p_cc_header_id ;
/* Update Cover */
UPDATE igc_cc_process_data
SET
reservation_status = l_reservation_Status
WHERE
request_id = l_request_id1 AND
cc_header_id = l_cc_headers_rec.parent_header_id ;
/* Update Relases */
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 parent_header_id =
l_cc_headers_rec.parent_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.parent_header_id;
l_process_flag := reval_fix_update(l_cc_headers_rec.parent_header_id,
p_cc_header_id,
l_revalue_fix_date,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
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);
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 (
'F',
'F',
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id,
l_message,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
l_org_id,
l_sob_id,
l_REQUEST_ID1);
l_process_flag := reval_fix_update(l_cc_headers_rec.parent_header_id,
NULL,
l_revalue_fix_date,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
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 (
'F',
'F',
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id,
l_message,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
l_org_id,
l_sob_id,
l_REQUEST_ID1);
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;
l_process_flag := reval_fix_update(l_rel_cc_header_id,
NULL,
l_revalue_fix_date,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
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);
l_process_flag := reval_fix_update(l_rel_cc_header_id,
NULL,
l_revalue_fix_date,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
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 (
'F',
'F',
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id,
l_message,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
l_org_id,
l_sob_id,
l_REQUEST_ID1);
l_process_flag := reval_fix_update(l_cc_headers_rec.cc_header_id,
NULL,
l_revalue_fix_date,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
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);
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 (
'F',
'F',
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id,
l_message,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
l_org_id,
l_org_id,
l_REQUEST_ID1);
l_process_flag := reval_fix_update(l_cc_headers_rec.cc_header_id,
NULL,
l_revalue_fix_date,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
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 (
'F',
'F',
l_err_header_id,
l_err_acct_line_id,
l_err_det_pf_line_id,
l_message,
/* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
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_fix_main');