[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_flag IN OUT NOCOPY VARCHAR2
);
p_update_flag IN OUT NOCOPY VARCHAR2
);
p_update_flag IN OUT NOCOPY VARCHAR2
);
p_update_flag IN OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Account_Line_Wrapper';
IGC_CC_ACCT_LINES_PKG.Update_Row (
p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
X_msg_count,
X_msg_data,
x_rowid,
p_cc_acct_lines_rec.CC_acct_line_id,
p_cc_acct_lines_rec.CC_header_id,
p_cc_acct_lines_rec.Parent_Header_Id,
p_cc_acct_lines_rec.Parent_Acct_Line_Id,
p_cc_acct_lines_rec.CC_Charge_Code_Combination_Id,
p_cc_acct_lines_rec.CC_Acct_Line_Num,
p_cc_acct_lines_rec.CC_Budget_Code_Combination_Id,
p_cc_acct_lines_rec.CC_Acct_Entered_Amt,
p_cc_acct_lines_rec.CC_Acct_Func_Amt,
p_cc_acct_lines_rec.CC_Acct_Desc,
p_cc_acct_lines_rec.CC_Acct_Billed_Amt,
p_cc_acct_lines_rec.CC_Acct_Unbilled_Amt,
p_cc_acct_lines_rec.CC_Acct_Taxable_Flag,
p_cc_acct_lines_rec.Tax_Id,
p_cc_acct_lines_rec.cc_acct_encmbrnc_amt,
p_cc_acct_lines_rec.cc_acct_encmbrnc_date,
p_cc_acct_lines_rec.CC_Acct_Encmbrnc_Status,
p_cc_acct_lines_rec.Project_Id,
p_cc_acct_lines_rec.Task_Id,
p_cc_acct_lines_rec.Expenditure_Type,
p_cc_acct_lines_rec.Expenditure_Org_Id,
p_cc_acct_lines_rec.Expenditure_Item_Date,
p_cc_acct_lines_rec.Last_Update_Date,
p_cc_acct_lines_rec.Last_Updated_By,
p_cc_acct_lines_rec.Last_Update_Login,
p_cc_acct_lines_rec.Creation_Date,
p_cc_acct_lines_rec.Created_By,
p_cc_acct_lines_rec.Attribute1,
p_cc_acct_lines_rec.Attribute2,
p_cc_acct_lines_rec.Attribute3,
p_cc_acct_lines_rec.Attribute4,
p_cc_acct_lines_rec.Attribute5,
p_cc_acct_lines_rec.Attribute6,
p_cc_acct_lines_rec.Attribute7,
p_cc_acct_lines_rec.Attribute8,
p_cc_acct_lines_rec.Attribute9,
p_cc_acct_lines_rec.Attribute10,
p_cc_acct_lines_rec.Attribute11,
p_cc_acct_lines_rec.Attribute12,
p_cc_acct_lines_rec.Attribute13,
p_cc_acct_lines_rec.Attribute14,
p_cc_acct_lines_rec.Attribute15,
p_cc_acct_lines_rec.Context,
p_cc_acct_lines_rec.cc_func_withheld_amt,
p_cc_acct_lines_rec.cc_ent_withheld_amt,
p_update_flag,
p_cc_acct_lines_rec.Tax_Classif_Code
);
p_update_flag IN OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Det_Pf_Wrapper';
IGC_CC_DET_PF_PKG.Update_Row (
p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
x_rowid,
p_cc_pmt_fcst_rec.CC_det_pf_line_id,
p_cc_pmt_fcst_rec.CC_Det_PF_Line_Num,
p_cc_pmt_fcst_rec.CC_Acct_Line_Id,
p_cc_pmt_fcst_rec.Parent_Acct_Line_Id,
p_cc_pmt_fcst_rec.Parent_Det_PF_Line_Id,
p_cc_pmt_fcst_rec.CC_Det_PF_Entered_Amt,
p_cc_pmt_fcst_rec.CC_Det_PF_Func_Amt,
p_cc_pmt_fcst_rec.CC_Det_PF_Date,
p_cc_pmt_fcst_rec.CC_Det_PF_Billed_Amt,
p_cc_pmt_fcst_rec.CC_Det_PF_Unbilled_Amt,
p_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Amt,
p_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Date,
p_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Status,
p_cc_pmt_fcst_rec.Last_Update_Date,
p_cc_pmt_fcst_rec.Last_Updated_By,
p_cc_pmt_fcst_rec.Last_Update_Login,
p_cc_pmt_fcst_rec.Creation_Date,
p_cc_pmt_fcst_rec.Created_By,
p_cc_pmt_fcst_rec.Attribute1,
p_cc_pmt_fcst_rec.Attribute2,
p_cc_pmt_fcst_rec.Attribute3,
p_cc_pmt_fcst_rec.Attribute4,
p_cc_pmt_fcst_rec.Attribute5,
p_cc_pmt_fcst_rec.Attribute6,
p_cc_pmt_fcst_rec.Attribute7,
p_cc_pmt_fcst_rec.Attribute8,
p_cc_pmt_fcst_rec.Attribute9,
p_cc_pmt_fcst_rec.Attribute10,
p_cc_pmt_fcst_rec.Attribute11,
p_cc_pmt_fcst_rec.Attribute12,
p_cc_pmt_fcst_rec.Attribute13,
p_cc_pmt_fcst_rec.Attribute14,
p_cc_pmt_fcst_rec.Attribute15,
p_cc_pmt_fcst_rec.Context,
p_update_flag
);
p_update_flag IN OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Header_Wrapper';
IGC_CC_HEADERS_PKG.Update_Row (
p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
x_rowid,
p_cc_header_rec.CC_Header_Id,
p_cc_header_rec.Org_id,
p_cc_header_rec.CC_Type,
p_cc_header_rec.CC_Num,
p_cc_header_rec.CC_Version_num,
p_cc_header_rec.Parent_Header_Id,
p_cc_header_rec.CC_State,
p_cc_header_rec.CC_ctrl_status,
p_cc_header_rec.CC_Encmbrnc_Status,
p_cc_header_rec.CC_Apprvl_Status,
p_cc_header_rec.Vendor_Id,
p_cc_header_rec.Vendor_Site_Id,
p_cc_header_rec.Vendor_Contact_Id,
p_cc_header_rec.Term_Id,
p_cc_header_rec.Location_Id,
p_cc_header_rec.Set_Of_Books_Id,
p_cc_header_rec.CC_Acct_Date,
p_cc_header_rec.CC_Desc,
p_cc_header_rec.CC_Start_Date,
p_cc_header_rec.CC_End_Date,
p_cc_header_rec.CC_Owner_User_Id,
p_cc_header_rec.CC_Preparer_User_Id,
p_cc_header_rec.Currency_Code,
p_cc_header_rec.Conversion_Type,
p_cc_header_rec.Conversion_Date,
p_cc_header_rec.Conversion_Rate,
p_cc_header_rec.Last_Update_Date,
p_cc_header_rec.Last_Updated_By,
p_cc_header_rec.Last_Update_Login,
p_cc_header_rec.Created_By,
p_cc_header_rec.Creation_Date,
p_cc_header_rec.CC_Current_User_Id,
p_cc_header_rec.Wf_Item_Type,
p_cc_header_rec.Wf_Item_Key,
p_cc_header_rec.Attribute1,
p_cc_header_rec.Attribute2,
p_cc_header_rec.Attribute3,
p_cc_header_rec.Attribute4,
p_cc_header_rec.Attribute5,
p_cc_header_rec.Attribute6,
p_cc_header_rec.Attribute7,
p_cc_header_rec.Attribute8,
p_cc_header_rec.Attribute9,
p_cc_header_rec.Attribute10,
p_cc_header_rec.Attribute11,
p_cc_header_rec.Attribute12,
p_cc_header_rec.Attribute13,
p_cc_header_rec.Attribute14,
p_cc_header_rec.Attribute15,
p_cc_header_rec.Context,
p_cc_header_rec.Cc_Guarantee_Flag,
p_update_flag
);
l_update_flag VARCHAR2(1);
l_update_login igc_cc_acct_lines.last_update_login%TYPE;
l_update_by igc_cc_acct_lines.last_updated_by%TYPE;
SELECT ccac.ROWID,
ccac.cc_header_id,
NULL org_id,
NULL cc_type,
NULL cc_type_code,
NULL cc_num,
ccac.cc_acct_line_id,
ccac.cc_acct_line_num,
ccac.cc_acct_desc,
ccac.parent_header_id,
ccac.parent_acct_line_id,
NULL parent_cc_acct_line_num,
NULL cc_budget_acct_desc,
ccac.cc_budget_code_combination_id,
NULL cc_charge_acct_desc,
ccac.cc_charge_code_combination_id,
ccac.cc_acct_entered_amt,
ccac.cc_acct_func_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT(ccac.cc_acct_line_id) cc_acct_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT(ccac.cc_acct_line_id) cc_acct_func_billed_amt,
ccac.cc_acct_encmbrnc_amt,
(IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccac.cc_header_id, NVL(ccac.cc_acct_entered_amt,0)) - NVL(ccac.cc_acct_encmbrnc_amt,0)) cc_acct_unencmrd_amt,
ccac.cc_acct_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccac.cc_header_id, NVL(ccac.cc_acct_entered_amt,0))
cc_acct_comp_func_amt,
NULL project_number,
ccac.project_id,
NULL task_number,
ccac.task_id,
ccac.expenditure_type,
NULL expenditure_org_name,
ccac.expenditure_org_id,
ccac.expenditure_item_date,
ccac.cc_acct_taxable_flag,
NULL tax_name,
ccac.tax_id,
ccac.cc_acct_encmbrnc_status,
ccac.cc_acct_encmbrnc_date,
ccac.context,
ccac.attribute1,
ccac.attribute2,
ccac.attribute3,
ccac.attribute4,
ccac.attribute5,
ccac.attribute6,
ccac.attribute7,
ccac.attribute8,
ccac.attribute9,
ccac.attribute10,
ccac.attribute11,
ccac.attribute12,
ccac.attribute13,
ccac.attribute14,
ccac.attribute15,
ccac.created_by,
ccac.creation_date,
ccac.last_updated_by,
ccac.last_update_date,
ccac.last_update_login,
ccac.cc_func_withheld_amt,
ccac.cc_ent_withheld_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccac.cc_header_id, NVL(ccac.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
ccac.Tax_Classif_Code
FROM igc_cc_acct_lines ccac
WHERE ccac.cc_header_id = t_cc_header_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 = t_cc_acct_line_id;
SELECT *
FROM igc_cc_acct_lines
WHERE cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
SELECT *
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = l_cc_pmt_fcst_rec.cc_det_pf_line_id;
l_update_login := FND_GLOBAL.LOGIN_ID;
l_update_by := FND_GLOBAL.USER_ID;
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
THEN*/ --Bug 5464993. Update amounts even when cbc is disabled
IF (l_cc_headers_rec.cc_state = 'CL') THEN
-- Added for Bug 3219208
-- Entered Amt should be set to 0 when the CC is being
-- cancelled.
l_acct_line_rec.cc_acct_entered_amt := 0;
l_acct_line_rec.last_update_date := SYSDATE;
l_acct_line_rec.last_update_login := l_update_login;
l_acct_line_rec.last_updated_by := l_update_by;
l_acct_line_rec.last_update_date := SYSDATE;
l_acct_line_rec.last_update_login := l_update_login;
l_acct_line_rec.last_updated_by := l_update_by;
l_acct_line_rec.last_update_date := SYSDATE;
l_acct_line_rec.last_update_login := l_update_login;
l_acct_line_rec.last_updated_by := l_update_by;
SELECT 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
INTO l_billed_amt,l_func_billed_amt
FROM igc_cc_acct_lines ccal
WHERE ccal.cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
l_acct_line_rec.last_update_date := SYSDATE;
l_acct_line_rec.last_update_login := l_update_login;
l_acct_line_rec.last_updated_by := l_update_by;
l_acct_line_rec.last_update_date := SYSDATE;
l_acct_line_rec.last_update_login := l_update_login;
l_acct_line_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_acct_lines
WHERE cc_acct_line_id = l_acct_line_rec.cc_acct_line_id;
p_update_flag => l_update_flag
);
l_det_pf_rec.last_update_date := SYSDATE;
l_det_pf_rec.last_update_login := l_update_login;
l_det_pf_rec.last_updated_by := l_update_by;
l_det_pf_rec.last_update_date := SYSDATE;
l_det_pf_rec.last_update_login := l_update_login;
l_det_pf_rec.last_updated_by := l_update_by;
l_det_pf_rec.last_update_date := SYSDATE;
l_det_pf_rec.last_update_login := l_update_login;
l_det_pf_rec.last_updated_by := l_update_by;
SELECT 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
INTO l_billed_amt, l_func_billed_amt
FROM igc_cc_det_pf ccdpf
WHERE ccdpf.cc_det_pf_line_id = l_cc_pmt_fcst_rec.cc_det_pf_line_id;
l_det_pf_rec.last_update_date := SYSDATE;
l_det_pf_rec.last_update_login := l_update_login;
l_det_pf_rec.last_updated_by := l_update_by;
l_det_pf_rec.last_update_date := SYSDATE;
l_det_pf_rec.last_update_login := l_update_login;
l_det_pf_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = l_det_pf_rec.cc_det_pf_line_id;
p_update_flag => l_update_flag
);
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
p_update_flag => l_update_flag
);
PROCEDURE Insert_Interface_Row(
p_cc_interface_rec IN igc_cc_interface%ROWTYPE,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Interface_Row';
l_full_path := g_path || 'Insert_Interface_Row';
INSERT
INTO igc_cc_interface (
batch_line_num,
cc_header_id,
cc_version_num,
cc_acct_line_id,
cc_det_pf_line_id,
set_of_books_id,
code_combination_id,
cc_transaction_date,
transaction_description,
encumbrance_type_id,
currency_code,
cc_func_dr_amt,
cc_func_cr_amt,
je_source_name,
je_category_name,
actual_flag,
budget_dest_flag,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
period_set_name,
period_name,
cbc_result_code,
status_code,
budget_version_id,
budget_amt,
commitment_encmbrnc_amt,
obligation_encmbrnc_amt,
funds_available_amt,
document_type,
reference_1,
reference_2,
reference_3,
reference_4,
reference_5,
reference_6,
reference_7,
reference_8,
reference_9,
reference_10,
cc_encmbrnc_date,
project_line --Bug 6341012 Added this column
)
VALUES
(p_cc_interface_rec.batch_line_num,
p_cc_interface_rec.cc_header_id,
p_cc_interface_rec.cc_version_num,
p_cc_interface_rec.cc_acct_line_id,
p_cc_interface_rec.cc_det_pf_line_id,
p_cc_interface_rec.set_of_books_id,
p_cc_interface_rec.code_combination_id,
p_cc_interface_rec.cc_transaction_date,
p_cc_interface_rec.transaction_description,
p_cc_interface_rec.encumbrance_type_id,
p_cc_interface_rec.currency_code,
p_cc_interface_rec.cc_func_dr_amt,
p_cc_interface_rec.cc_func_cr_amt,
p_cc_interface_rec.je_source_name,
p_cc_interface_rec.je_category_name,
p_cc_interface_rec.actual_flag,
p_cc_interface_rec.budget_dest_flag,
p_cc_interface_rec.last_update_date,
p_cc_interface_rec.last_updated_by,
p_cc_interface_rec.last_update_login,
p_cc_interface_rec.creation_date,
p_cc_interface_rec.created_by,
p_cc_interface_rec.period_set_name,
p_cc_interface_rec.period_name,
p_cc_interface_rec.cbc_result_code,
p_cc_interface_rec.status_code,
p_cc_interface_rec.budget_version_id,
p_cc_interface_rec.budget_amt,
p_cc_interface_rec.commitment_encmbrnc_amt,
p_cc_interface_rec.obligation_encmbrnc_amt,
p_cc_interface_rec.funds_available_amt,
p_cc_interface_rec.document_type,
p_cc_interface_rec.reference_1,
p_cc_interface_rec.reference_2,
p_cc_interface_rec.reference_3,
p_cc_interface_rec.reference_4,
p_cc_interface_rec.reference_5,
p_cc_interface_rec.reference_6,
p_cc_interface_rec.reference_7,
p_cc_interface_rec.reference_8,
p_cc_interface_rec.reference_9,
p_cc_interface_rec.reference_10,
p_cc_interface_rec.cc_encmbrnc_date,
p_cc_interface_rec.project_line --Bug 6341012 Added this column
);
END Insert_Interface_Row;
DELETE FROM igc_cc_interface
WHERE to_date(creation_date,'DD/MM/YYYY') <= to_date(sysdate ,'DD/MM/YYYY') - 2;
l_cc_interface_rec.last_update_date := sysdate;
l_cc_interface_rec.last_updated_by := -1;
l_cc_interface_rec.last_update_login := -1;
/* SELECT 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,
cc_acct_func_amt
INTO l_billed_amt, l_func_billed_amt, l_func_amt
FROM igc_cc_acct_lines ccal
WHERE ccal.cc_acct_line_id = p_cc_acct_lines_rec.cc_acct_line_id;
SELECT IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_billed_amt,
CC_ACCT_ENCMBRNC_AMT,
cc_acct_func_amt
INTO l_billed_amt, l_func_billed_amt, l_func_amt
FROM igc_cc_acct_lines ccal
WHERE ccal.cc_acct_line_id = p_cc_acct_lines_rec.cc_acct_line_id;
/* SELECT 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_func_amt
INTO l_billed_amt, l_func_billed_amt, l_func_amt
FROM igc_cc_det_pf ccdpf
WHERE ccdpf.cc_det_pf_line_id = p_cc_pmt_fcst_rec.cc_det_pf_line_id;
SELECT 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,
ccdpf.CC_DET_PF_ENCMBRNC_AMT cc_det_pf_func_billed_amt,
ccdpf.cc_det_pf_func_amt
INTO l_billed_amt, l_func_billed_amt, l_func_amt
FROM igc_cc_det_pf ccdpf
WHERE ccdpf.cc_det_pf_line_id = p_cc_pmt_fcst_rec.cc_det_pf_line_id;
Insert_Interface_Row(l_cc_interface_rec,
x_msg_count,
x_msg_data,
l_return_status);
Insert_Interface_Row(l_cc_interface_rec,
x_msg_count,
x_msg_data,
l_return_status);
Insert_Interface_Row(l_cc_interface_rec,
x_msg_count,
x_msg_data,
l_return_status);
Insert_Interface_Row(l_cc_interface_rec,
x_msg_count,
x_msg_data,
l_return_status);
Insert_Interface_Row(l_cc_interface_rec,
x_msg_count,
x_msg_data,
l_return_status);
Insert_Interface_Row(l_cc_interface_rec,
x_msg_count,
x_msg_data,
l_return_status);
Insert_Interface_Row(l_cc_interface_rec,
x_msg_count,
x_msg_data,
l_return_status);
Insert_Interface_Row(l_cc_interface_rec,
x_msg_count,
x_msg_data,
l_return_status);
l_update_flag VARCHAR2(1);
l_update_login igc_cc_acct_lines.last_update_login%TYPE;
l_update_by igc_cc_acct_lines.last_updated_by%TYPE;
e_update EXCEPTION;
e_delete EXCEPTION;
e_update_cc_tables EXCEPTION;
SELECT distinct cc_header_id, cc_acct_line_id, cc_det_pf_line_id, budget_dest_flag, cc_transaction_date
FROM igc_cc_interface
WHERE cc_header_id = t_cc_header_id AND
actual_flag = 'E';
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 = t_cc_acct_line_id;
SELECT ccac.ROWID,
ccac.cc_header_id,
NULL org_id,
NULL cc_type,
NULL cc_type_code,
NULL cc_num,
ccac.cc_acct_line_id,
ccac.cc_acct_line_num,
ccac.cc_acct_desc,
ccac.parent_header_id,
ccac.parent_acct_line_id,
NULL parent_cc_acct_line_num,
NULL cc_budget_acct_desc,
ccac.cc_budget_code_combination_id,
NULL cc_charge_acct_desc,
ccac.cc_charge_code_combination_id,
ccac.cc_acct_entered_amt,
ccac.cc_acct_func_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT(ccac.cc_acct_line_id) cc_acct_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT(ccac.cc_acct_line_id) cc_acct_func_billed_amt,
ccac.cc_acct_encmbrnc_amt,
(IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccac.cc_header_id, NVL(ccac.cc_acct_entered_amt,0)) - NVL(ccac.cc_acct_encmbrnc_amt,0)) cc_acct_unencmrd_amt,
ccac.cc_acct_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccac.cc_header_id, NVL(ccac.cc_acct_entered_amt,0))
cc_acct_comp_func_amt,
NULL project_number,
ccac.project_id,
NULL task_number,
ccac.task_id,
ccac.expenditure_type,
NULL expenditure_org_name,
ccac.expenditure_org_id,
ccac.expenditure_item_date,
ccac.cc_acct_taxable_flag,
NULL tax_name,
ccac.tax_id,
ccac.cc_acct_encmbrnc_status,
ccac.cc_acct_encmbrnc_date,
ccac.context,
ccac.attribute1,
ccac.attribute2,
ccac.attribute3,
ccac.attribute4,
ccac.attribute5,
ccac.attribute6,
ccac.attribute7,
ccac.attribute8,
ccac.attribute9,
ccac.attribute10,
ccac.attribute11,
ccac.attribute12,
ccac.attribute13,
ccac.attribute14,
ccac.attribute15,
ccac.created_by,
ccac.creation_date,
ccac.last_updated_by,
ccac.last_update_date,
ccac.last_update_login,
ccac.cc_func_withheld_amt,
ccac.cc_ent_withheld_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccac.cc_header_id, NVL(ccac.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
ccac.Tax_Classif_Code
FROM igc_cc_acct_lines ccac
WHERE ccac.cc_header_id = t_cc_header_id;
SELECT *
FROM igc_cc_acct_lines
WHERE cc_acct_line_id = l_cc_acct_line_id;
SELECT *
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = l_cc_det_pf_line_id;
SELECT *
FROM igc_cc_acct_lines
WHERE cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
SELECT *
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = l_cc_pmt_fcst_rec.cc_det_pf_line_id;
SELECT SUM(( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(cp_cc_header_id, NVL(cc_det_pf_entered_amt,0) ) -
NVL(cc_det_pf_encmbrnc_amt,0) )) cc_det_pf_unencmbrd_amt
FROM igc_cc_det_pf
WHERE cc_acct_line_id IN (SELECT cc_acct_line_id
FROM igc_cc_acct_lines
WHERE cc_header_id = cp_cc_header_id);
l_update_login := FND_GLOBAL.LOGIN_ID;
l_update_by := FND_GLOBAL.USER_ID;
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
p_update_flag => l_update_flag
);
RAISE E_UPDATE;
DELETE igc_cc_interface
WHERE cc_header_id = p_cc_header_id AND
actual_flag = 'E';
RAISE E_DELETE;
SELECT enable_budgetary_control_flag,currency_code
INTO l_enable_budg_control_flag, l_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = l_cc_headers_rec.set_of_books_id;
SELECT cc_bc_enable_flag
INTO l_cc_bc_enable_flag
FROM igc_cc_bc_enable
WHERE set_of_books_id = l_cc_headers_rec.set_of_books_id;
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
p_update_flag => l_update_flag
);
RAISE E_UPDATE;
select count(*)
INTO l_interface_row_count
FROM igc_cc_interface
WHERE cc_header_id = p_cc_header_id;
/* Update CC Tables with feedback from igc_cc_interface */
/* If mode = Reservation then update CC Tables encumbrance status, encumbered_amount */
BEGIN
IF ( (p_mode = 'R') AND (x_bc_status = FND_API.G_TRUE) AND (l_interface_row_count <> 0) )
THEN
OPEN c_cc_interface(p_cc_header_id);
/* Update CC Tables with the feedback */
/* IF ((l_cc_acct_line_id IS NOT NULL) AND
(l_budget_dest_flag = 'C'))
THEN */--Bug 5464993. Update amounts even when cbc is disabled
-- Performance Tuning, Replaced view
-- igc_cc_acct_lines_v with
-- igc_cc_acct_lines and replaced the line
-- below.
-- SELECT cc_acct_comp_func_amt
SELECT IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0)) cc_acct_comp_func_amt
INTO l_cc_acct_comp_func_amt
FROM igc_cc_acct_lines ccal
WHERE ccal.cc_acct_line_id = l_cc_acct_line_id;
l_acct_line_rec.last_update_date := SYSDATE;
l_acct_line_rec.last_update_login := l_update_login;
l_acct_line_rec.last_updated_by := l_update_by;
l_acct_line_rec.last_update_date := SYSDATE;
l_acct_line_rec.last_update_login := l_update_login;
l_acct_line_rec.last_updated_by := l_update_by;
l_acct_line_rec.last_update_date := SYSDATE;
l_acct_line_rec.last_update_login := l_update_login;
l_acct_line_rec.last_updated_by := l_update_by;
SELECT 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
INTO l_billed_amt, l_func_billed_amt
FROM igc_cc_acct_lines ccal
WHERE ccal.cc_acct_line_id = l_cc_acct_line_id;
l_acct_line_rec.last_update_date := SYSDATE;
l_acct_line_rec.last_update_login := l_update_login;
l_acct_line_rec.last_updated_by := l_update_by;
l_acct_line_rec.last_update_date := SYSDATE;
l_acct_line_rec.last_update_login := l_update_login;
l_acct_line_rec.last_updated_by := l_update_by;
g_debug_msg := ' Bad CC State for Update';
RAISE E_UPDATE_CC_TABLES;
SELECT rowid
INTO l_row_id
FROM igc_cc_acct_lines
WHERE cc_acct_line_id = l_acct_line_rec.cc_acct_line_id;
p_update_flag => l_update_flag
);
g_debug_msg := ' FAILED Updated Acct Line ..... 1 ';
RAISE E_UPDATE_CC_TABLES;
SELECT 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
INTO l_cc_det_pf_comp_func_amt
FROM igc_cc_det_pf ccdpf
WHERE ccdpf.cc_det_pf_line_id = l_cc_det_pf_line_id;
l_det_pf_rec.last_update_date := SYSDATE;
l_det_pf_rec.last_update_login := l_update_login;
l_det_pf_rec.last_updated_by := l_update_by;
l_det_pf_rec.last_update_date := SYSDATE;
l_det_pf_rec.last_update_login := l_update_login;
l_det_pf_rec.last_updated_by := l_update_by;
l_det_pf_rec.last_update_date := SYSDATE;
l_det_pf_rec.last_update_login := l_update_login;
l_det_pf_rec.last_updated_by := l_update_by;
SELECT 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
INTO l_billed_amt, l_func_billed_amt
FROM igc_cc_det_pf ccdpf
WHERE ccdpf.cc_det_pf_line_id = l_cc_det_pf_line_id;
l_det_pf_rec.last_update_date := SYSDATE;
l_det_pf_rec.last_update_login := l_update_login;
l_det_pf_rec.last_updated_by := l_update_by;
l_det_pf_rec.last_update_date := SYSDATE;
l_det_pf_rec.last_update_login := l_update_login;
l_det_pf_rec.last_updated_by := l_update_by;
RAISE E_UPDATE_CC_TABLES;
SELECT rowid
INTO l_row_id
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = l_det_pf_rec.cc_det_pf_line_id;
p_update_flag => l_update_flag
);
RAISE E_UPDATE_CC_TABLES;
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
RAISE E_UPDATE_CC_TABLES;
SELECT rowid
INTO l_row_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
p_update_flag => l_update_flag
);
RAISE E_UPDATE_CC_TABLES;
g_debug_msg := ' Inserting Actions Record.....';
IGC_CC_ACTIONS_PKG.Insert_Row
(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
l_return_status,
l_msg_count,
l_msg_data,
l_rowid,
p_cc_header_id,
l_cc_headers_rec.cc_version_num,
'EC',
l_cc_headers_rec.cc_state,
l_cc_headers_rec.cc_ctrl_status,
l_cc_headers_rec.cc_apprvl_status,
p_notes,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
sysdate,
fnd_global.user_id
);
g_debug_msg := ' Falure Inserting Actions Row..... 1 ';
RAISE E_UPDATE_CC_TABLES;
END IF; /* Update CC Tables */
RAISE E_UPDATE_CC_TABLES;
g_debug_msg := ' Beginning Phase 2....... Updates';
l_acct_line_rec.last_update_date := SYSDATE;
l_acct_line_rec.last_update_login := l_update_login;
l_acct_line_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_acct_lines
WHERE cc_acct_line_id = l_acct_line_rec.cc_acct_line_id;
p_update_flag => l_update_flag
);
g_debug_msg := ' FAILED update accout line ........ 2';
RAISE E_UPDATE_CC_TABLES;
l_det_pf_rec.last_update_date := SYSDATE;
l_det_pf_rec.last_update_login := l_update_login;
l_det_pf_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = l_det_pf_rec.cc_det_pf_line_id;
p_update_flag => l_update_flag
);
RAISE E_UPDATE_CC_TABLES;
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
p_update_flag => l_update_flag
);
g_debug_msg := ' FAILED Updated Header Line INFO ...... 3 ';
RAISE E_UPDATE_CC_TABLES;
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
p_update_flag => l_update_flag
);
g_debug_msg := ' FAILED Update Header Line Info ...... 4 ';
RAISE E_UPDATE_CC_TABLES;
WHEN FND_API.G_EXC_UNEXPECTED_ERROR OR E_CC_NOT_FOUND OR E_INVALID_MODE OR E_UPDATE OR E_DELETE
THEN
ROLLBACK TO Execute_Budgetary_Ctrl1;
FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR OR E_CC_NOT_FOUND OR E_INVALID_MODE OR E_UPDATE OR E_DELETE Exception Raised');
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
p_update_flag => l_update_flag
);
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
p_update_flag => l_update_flag
);
WHEN E_BC_EXECUTION /*OR E_UPDATE_CC_TABLES */
THEN
--ROLLBACK TO Execute_Budgetary_Ctrl4;
select count(*)
INTO x_msg_count
FROM igc_cc_interface
WHERE cc_header_id = p_cc_header_id;
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
p_update_flag => l_update_flag
);
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
p_update_flag => l_update_flag
);
SELECT NVL(enable_budgetary_control_flag,'N')
INTO l_enable_budg_control_flag
FROM gl_sets_of_books
WHERE set_of_books_id = p_sob_id;
SELECT req_encumbrance_flag, purch_encumbrance_flag
INTO l_req_encumbrance_flag, l_purch_encumbrance_flag
FROM financials_system_params_all
WHERE set_of_books_id = p_sob_id AND
org_id = p_org_id;
SELECT cc_bc_enable_flag
INTO l_cc_bc_enable_flag
FROM igc_cc_bc_enable
WHERE set_of_books_id = p_sob_id;
l_update_flag VARCHAR2(1);
l_update_login igc_cc_acct_lines.last_update_login%TYPE;
l_update_by igc_cc_acct_lines.last_updated_by%TYPE;
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 = t_cc_acct_line_id;
SELECT ccac.ROWID,
ccac.cc_header_id,
NULL org_id,
NULL cc_type,
NULL cc_type_code,
NULL cc_num,
ccac.cc_acct_line_id,
ccac.cc_acct_line_num,
ccac.cc_acct_desc,
ccac.parent_header_id,
ccac.parent_acct_line_id,
NULL parent_cc_acct_line_num,
NULL cc_budget_acct_desc,
ccac.cc_budget_code_combination_id,
NULL cc_charge_acct_desc,
ccac.cc_charge_code_combination_id,
ccac.cc_acct_entered_amt,
ccac.cc_acct_func_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT(ccac.cc_acct_line_id) cc_acct_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT(ccac.cc_acct_line_id) cc_acct_func_billed_amt,
ccac.cc_acct_encmbrnc_amt,
(IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccac.cc_header_id, NVL(ccac.cc_acct_entered_amt,0)) - NVL(ccac.cc_acct_encmbrnc_amt,0)) cc_acct_unencmrd_amt,
ccac.cc_acct_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccac.cc_header_id, NVL(ccac.cc_acct_entered_amt,0))
cc_acct_comp_func_amt,
NULL project_number,
ccac.project_id,
NULL task_number,
ccac.task_id,
ccac.expenditure_type,
NULL expenditure_org_name,
ccac.expenditure_org_id,
ccac.expenditure_item_date,
ccac.cc_acct_taxable_flag,
NULL tax_name,
ccac.tax_id,
ccac.cc_acct_encmbrnc_status,
ccac.cc_acct_encmbrnc_date,
ccac.context,
ccac.attribute1,
ccac.attribute2,
ccac.attribute3,
ccac.attribute4,
ccac.attribute5,
ccac.attribute6,
ccac.attribute7,
ccac.attribute8,
ccac.attribute9,
ccac.attribute10,
ccac.attribute11,
ccac.attribute12,
ccac.attribute13,
ccac.attribute14,
ccac.attribute15,
ccac.created_by,
ccac.creation_date,
ccac.last_updated_by,
ccac.last_update_date,
ccac.last_update_login,
ccac.cc_func_withheld_amt,
ccac.cc_ent_withheld_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccac.cc_header_id, NVL(ccac.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
ccac.Tax_Classif_Code
FROM igc_cc_acct_lines ccac
WHERE ccac.cc_header_id = t_cc_header_id;
SELECT *
FROM igc_cc_acct_lines
WHERE cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
SELECT *
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = l_cc_pmt_fcst_rec.cc_det_pf_line_id;
l_update_login := FND_GLOBAL.LOGIN_ID;
l_update_by := FND_GLOBAL.USER_ID;
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
l_acct_line_rec.last_update_date := SYSDATE;
l_acct_line_rec.last_update_login := l_update_login;
l_acct_line_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_acct_lines
WHERE cc_acct_line_id = l_acct_line_rec.cc_acct_line_id;
p_update_flag => l_update_flag
);
l_det_pf_rec.last_update_date := SYSDATE;
l_det_pf_rec.last_update_login := l_update_login;
l_det_pf_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_det_pf
WHERE cc_det_pf_line_id = l_det_pf_rec.cc_det_pf_line_id;
p_update_flag => l_update_flag
);
l_cc_headers_rec.last_update_date := SYSDATE;
l_cc_headers_rec.last_update_login := l_update_login;
l_cc_headers_rec.last_updated_by := l_update_by;
SELECT rowid
INTO l_row_id
FROM igc_cc_headers
WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
p_update_flag => l_update_flag
);
SELECT ccac.ROWID,
ccac.cc_header_id,
NULL org_id,
NULL cc_type,
NULL cc_type_code,
NULL cc_num,
ccac.cc_acct_line_id,
ccac.cc_acct_line_num,
ccac.cc_acct_desc,
ccac.parent_header_id,
ccac.parent_acct_line_id,
NULL parent_cc_acct_line_num,
NULL cc_budget_acct_desc,
ccac.cc_budget_code_combination_id,
NULL cc_charge_acct_desc,
ccac.cc_charge_code_combination_id,
ccac.cc_acct_entered_amt,
ccac.cc_acct_func_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT(ccac.cc_acct_line_id) cc_acct_billed_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT(ccac.cc_acct_line_id) cc_acct_func_billed_amt,
ccac.cc_acct_encmbrnc_amt,
(IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccac.cc_header_id, NVL(ccac.cc_acct_entered_amt,0)) - NVL(ccac.cc_acct_encmbrnc_amt,0)) cc_acct_unencmrd_amt,
ccac.cc_acct_unbilled_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccac.cc_header_id, NVL(ccac.cc_acct_entered_amt,0))
cc_acct_comp_func_amt,
NULL project_number,
ccac.project_id,
NULL task_number,
ccac.task_id,
ccac.expenditure_type,
NULL expenditure_org_name,
ccac.expenditure_org_id,
ccac.expenditure_item_date,
ccac.cc_acct_taxable_flag,
NULL tax_name,
ccac.tax_id,
ccac.cc_acct_encmbrnc_status,
ccac.cc_acct_encmbrnc_date,
ccac.context,
ccac.attribute1,
ccac.attribute2,
ccac.attribute3,
ccac.attribute4,
ccac.attribute5,
ccac.attribute6,
ccac.attribute7,
ccac.attribute8,
ccac.attribute9,
ccac.attribute10,
ccac.attribute11,
ccac.attribute12,
ccac.attribute13,
ccac.attribute14,
ccac.attribute15,
ccac.created_by,
ccac.creation_date,
ccac.last_updated_by,
ccac.last_update_date,
ccac.last_update_login,
ccac.cc_func_withheld_amt,
ccac.cc_ent_withheld_amt,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccac.cc_header_id, NVL(ccac.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
ccac.Tax_Classif_Code
FROM igc_cc_acct_lines ccac
WHERE ccac.cc_header_id = t_cc_header_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 = t_cc_acct_line_id;
SELECT application_id
FROM fnd_application
WHERE application_short_name = 'SQLGL';
SELECT NVL(enable_budgetary_control_flag,'N')
INTO l_sbc_enable_flag
FROM gl_sets_of_books
WHERE set_of_books_id = p_sob_id;
SELECT cc_bc_enable_flag
INTO l_cbc_enable_flag
FROM igc_cc_bc_enable
WHERE set_of_books_id = p_sob_id;
SELECT cc_prov_encmbrnc_enable_flag,
cc_conf_encmbrnc_enable_flag
INTO l_cc_prov_encmbrnc_flag,
l_cc_conf_encmbrnc_flag
FROM igc_cc_encmbrnc_ctrls_v
WHERE org_id = p_org_id;
SELECT count(*)
INTO l_COUNT
FROM gl_sets_of_books sob, gl_periods gp, igc_cc_periods cp
WHERE sob.set_of_books_id = p_sob_id
AND sob.period_set_name = gp.period_set_name
AND cp.org_id = p_org_id
AND gp.period_set_name = cp.period_set_name
AND cp.period_name = gp.period_name
AND cp.cc_period_status IN ('O','F')
AND (p_start_date BETWEEN gp.start_date AND gp.end_date);
SELECT count(*)
INTO l_COUNT
FROM gl_sets_of_books sob,
gl_period_statuses gl,
igc_cc_periods cp
WHERE sob.set_of_books_id = p_sob_id
AND gl.set_of_books_id = sob.set_of_books_id
AND gl.application_id = 101
AND cp.org_id = p_org_id
AND cp.period_set_name = sob.period_set_name
AND cp.period_name = gl.period_name
AND cp.cc_period_status IN ('O','F')
AND gl.closing_status IN ('O','F')
AND (p_start_date BETWEEN gl.start_date AND gl.end_date);
SELECT MIN(cch.cc_start_date)
INTO l_min_rel_start_date
FROM igc_cc_headers cch
WHERE cch.parent_header_id = p_cc_header_id;
SELECT cch.cc_start_date
INTO l_cover_start_date
FROM igc_cc_headers cch
WHERE cch.cc_header_id = p_parent_cc_header_id;
SELECT MAX(cch.cc_end_date)
INTO l_max_rel_end_date
FROM igc_cc_headers cch
WHERE cch.parent_header_id = p_cc_header_id;
SELECT cch.cc_end_date
INTO l_cover_end_date
FROM igc_cc_headers cch
WHERE cch.cc_header_id = p_parent_cc_header_id;
SELECT count(*)
INTO l_COUNT
FROM gl_sets_of_books sob,
gl_period_statuses gl,
igc_cc_periods cp
WHERE sob.set_of_books_id = p_sob_id
AND gl.set_of_books_id = sob.set_of_books_id
AND gl.application_id = l_gl_application_id -- Bug 1830385
AND cp.org_id = p_org_id
AND cp.period_set_name = sob.period_set_name
AND cp.period_name = gl.period_name
AND cp.cc_period_status IN ('O','F')
AND gl.closing_status IN ('O','F')
AND (p_cc_det_pf_date BETWEEN gl.start_date AND gl.end_date);
SELECT count(*)
INTO l_COUNT
FROM gl_sets_of_books sob,
gl_period_statuses gl,
igc_cc_periods cp
WHERE sob.set_of_books_id = p_sob_id
AND gl.set_of_books_id = sob.set_of_books_id
AND gl.application_id = l_gl_application_id -- Bug 1830385
AND cp.org_id = p_org_id
AND cp.period_set_name = sob.period_set_name
AND cp.period_name = gl.period_name
AND cp.cc_period_status IN ('O','F')
AND gl.closing_status IN ('O','F')
AND (p_acct_date BETWEEN gl.start_date AND gl.end_date);
SELECT distinct gl.period_year
INTO l_orig_fiscal_year
FROM gl_sets_of_books sob,
gl_periods gl
WHERE sob.set_of_books_id = p_sob_id
AND gl.period_set_name= sob.period_set_name
AND gl.period_type = sob.accounted_period_type
AND (p_prev_acct_date between gl.start_date and gl.end_date);
SELECT distinct gl.period_year
INTO l_new_fiscal_year
FROM gl_sets_of_books sob,
gl_periods gl
WHERE sob.set_of_books_id = p_sob_id
AND gl.period_set_name= sob.period_set_name
AND gl.period_type = sob.accounted_period_type
AND (p_acct_date between gl.start_date and gl.end_date);
SELECT count(*)
INTO l_COUNT
FROM gl_sets_of_books sob,
gl_period_statuses gl,
igc_cc_periods cp
WHERE sob.set_of_books_id = p_sob_id
AND gl.set_of_books_id = sob.set_of_books_id
AND gl.application_id = l_gl_application_id -- Bug 1830385
AND cp.org_id = p_org_id
AND cp.period_set_name = sob.period_set_name
AND cp.period_name = gl.period_name
AND cp.cc_period_status = 'O'
AND gl.closing_status = 'O'
AND (p_acct_date BETWEEN gl.start_date AND gl.end_date);
SELECT count(*)
INTO l_COUNT
FROM gl_sets_of_books sob,
gl_period_statuses gl,
igc_cc_periods cp
WHERE sob.set_of_books_id = p_sob_id
AND gl.set_of_books_id = sob.set_of_books_id
AND gl.application_id = l_gl_application_id -- Bug 1830385
AND cp.org_id = p_org_id
AND cp.period_set_name = sob.period_set_name
AND cp.period_name = gl.period_name
AND cp.cc_period_status IN ('O','F')
AND gl.closing_status IN ('O','F')
AND (p_acct_date BETWEEN gl.start_date AND gl.end_date);
SELECT distinct gl.period_year
INTO l_orig_fiscal_year
FROM gl_sets_of_books sob,
gl_periods gl
WHERE sob.set_of_books_id = p_sob_id
AND gl.period_set_name= sob.period_set_name
AND gl.period_type = sob.accounted_period_type
AND (p_prev_acct_date between gl.start_date and gl.end_date);
SELECT distinct gl.period_year
INTO l_new_fiscal_year
FROM gl_sets_of_books sob,
gl_periods gl
WHERE sob.set_of_books_id = p_sob_id
AND gl.period_set_name= sob.period_set_name
AND gl.period_type = sob.accounted_period_type
AND (p_acct_date between gl.start_date and gl.end_date);
SELECT count(*)
INTO l_COUNT
FROM gl_sets_of_books sob,
gl_period_statuses gl,
igc_cc_periods cp
WHERE sob.set_of_books_id = p_sob_id
AND gl.set_of_books_id = sob.set_of_books_id
AND gl.application_id = l_gl_application_id -- Bug 1830385
AND cp.org_id = p_org_id
AND cp.period_set_name = sob.period_set_name
AND cp.period_name = gl.period_name
AND cp.cc_period_status = 'O'
AND gl.closing_status = 'O'
AND (p_acct_date BETWEEN gl.start_date AND gl.end_date);
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
SELECT count(*)
INTO l_cc_det_pf_cnt
FROM igc_cc_det_pf
WHERE cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
SELECT count(*)
INTO l_cc_det_pf_cnt
FROM igc_cc_det_pf
WHERE cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
SELECT count(*)
INTO l_COUNT
FROM gl_sets_of_books sob,
gl_period_statuses gl,
igc_cc_periods cp
WHERE sob.set_of_books_id = p_sob_id
AND gl.set_of_books_id = sob.set_of_books_id
AND gl.application_id = l_gl_application_id -- Bug 1830385
AND cp.org_id = p_org_id
AND cp.period_set_name = sob.period_set_name
AND cp.period_name = gl.period_name
AND cp.cc_period_status IN ('O','F')
AND gl.closing_status IN ('O','F')
AND (l_cc_det_pf_lines_rec.cc_det_pf_date
BETWEEN gl.start_date AND gl.end_date
);
fnd_message.set_name('IGC','IGC_CC_DET_PF_DATE_NO_UPDATE');
SELECT distinct gl.period_year
INTO l_orig_fiscal_year
FROM gl_sets_of_books sob,
gl_periods gl
WHERE sob.set_of_books_id = p_sob_id
AND gl.period_set_name= sob.period_set_name
AND gl.period_type = sob.accounted_period_type
AND (l_cc_det_pf_lines_rec.cc_det_pf_encmbrnc_date between gl.start_date and gl.end_date);
SELECT distinct gl.period_year
INTO l_new_fiscal_year
FROM gl_sets_of_books sob,
gl_periods gl
WHERE sob.set_of_books_id = p_sob_id
AND gl.period_set_name= sob.period_set_name
AND gl.period_type = sob.accounted_period_type
AND (l_cc_det_pf_lines_rec.cc_det_pf_date between gl.start_date and gl.end_date);
SELECT SUM(NVL(CC_DET_PF_ENTERED_AMT,0))
INTO l_total_pf_entered_amt
FROM igc_cc_det_pf
WHERE cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
SELECT *
INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
SELECT count(*)
INTO l_cc_det_pf_cnt
FROM igc_cc_det_pf
WHERE cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
SELECT count(*)
INTO l_COUNT
FROM gl_sets_of_books sob,
gl_period_statuses gl,
igc_cc_periods cp
WHERE sob.set_of_books_id = p_sob_id
AND gl.set_of_books_id = sob.set_of_books_id
AND gl.application_id = l_gl_application_id -- Bug 1830385
AND cp.org_id = p_org_id
AND cp.period_set_name = sob.period_set_name
AND cp.period_name = gl.period_name
AND cp.cc_period_status IN ('O','F')
AND gl.closing_status IN ('O','F')
AND (l_cc_det_pf_lines_rec.cc_det_pf_date
BETWEEN gl.start_date AND gl.end_date
);
'IGC_CC_DET_PF_DATE_NO_UPDATE');
SELECT distinct gl.period_year
INTO l_orig_fiscal_year
FROM gl_sets_of_books sob,
gl_periods gl
WHERE sob.set_of_books_id = p_sob_id
AND gl.period_set_name= sob.period_set_name
AND gl.period_type = sob.accounted_period_type
AND (l_cc_det_pf_lines_rec.cc_det_pf_encmbrnc_date between gl.start_date and gl.end_date);
SELECT distinct gl.period_year
INTO l_new_fiscal_year
FROM gl_sets_of_books sob,
gl_periods gl
WHERE sob.set_of_books_id = p_sob_id
AND gl.period_set_name= sob.period_set_name
AND gl.period_type = sob.accounted_period_type
AND (l_cc_det_pf_lines_rec.cc_det_pf_date between gl.start_date and gl.end_date);
SELECT SUM(NVL(CC_DET_PF_ENTERED_AMT,0))
INTO l_total_pf_entered_amt
FROM igc_cc_det_pf
WHERE cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
SELECT Nvl(tax_recovery_rate,100),
Nvl(tax_rate,0)
FROM ap_tax_codes a,
financials_system_parameters b
WHERE a.set_of_books_id = b.set_of_books_id
AND a.tax_id = p_tax_id;