[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT igc_cc_headers_s.nextval
INTO P_Header_Id FROM DUAL;
SELECT igc_cc_acct_lines_s.nextval
INTO P_Acct_Line_Id FROM DUAL;
SELECT igc_cc_det_pf_s.nextval
INTO P_Det_Pf_Line_Id FROM DUAL;
INSERT INTO IGC_CC_INTERFACE_ERRORS
( batch_id,
interface_header_id,
interface_acct_line_id,
interface_det_pf_line_id,
org_id,
set_of_books_id,
error_message )
VALUES ( g_batch_id,
P_Interface_Header_Id,
P_Interface_Acct_Line_Id,
P_Interface_Det_Pf_Line_Id,
P_Org_Id,
P_Set_of_Books_Id,
P_Error_Message);
PROCEDURE INSERT_ORPHAN_RECORDS
( P_X_Error_Status IN OUT NOCOPY VARCHAR2 )
IS
l_interface_header_id NUMBER;
SELECT ICALI.interface_header_id,
ICALI.interface_acct_line_id
FROM igc_cc_acct_lines_interface ICALI
WHERE ICALI.batch_id = g_batch_Id
AND NOT EXISTS (SELECT ICALI1.interface_header_id
FROM igc_cc_headers_interface ICALI1
WHERE ICALI1.batch_id = g_batch_id
AND ICALI.interface_header_id = ICALI1.interface_header_id);
SELECT ICDPI.interface_acct_line_id,
ICDPI.interface_det_pf_line_id
FROM igc_cc_det_pf_interface ICDPI
WHERE ICDPI.batch_id = g_batch_id
AND NOT EXISTS (SELECT ICALI.interface_acct_line_id
FROM igc_cc_headers_interface ICHI,
igc_cc_acct_lines_interface ICALI
WHERE ICHI.batch_id = g_batch_id
AND ICHI.batch_id = ICALI.batch_id
AND ICHI.interface_header_id = ICALI.interface_header_id
AND ICDPI.interface_acct_line_id = ICALI.interface_acct_line_id);
SELECT cch.cc_header_id INTO P_Parent_Header_Id
FROM igc_cc_headers cch, igc_cc_headers_interface cchi
WHERE cchi.interface_header_id = P_Interface_Parent_Header_Id
AND cchi.cc_num = cch.cc_num
AND cchi.org_id = cch.org_id;
SELECT cca.cc_acct_line_id INTO P_Parent_Acct_Line_Id
FROM igc_cc_acct_lines cca, igc_cc_acct_lines_interface ccai
WHERE ccai.interface_acct_line_id = P_Interface_Parent_AcctLine_Id
AND cca.cc_header_id = P_Parent_Header_Id
AND ccai.cc_acct_line_num = cca.cc_acct_line_num;
SELECT ccd.cc_det_pf_line_id INTO P_Parent_Det_Pf_Id
FROM igc_cc_det_pf ccd, igc_cc_det_pf_interface ccdi
WHERE ccdi.interface_det_pf_line_id = P_Interface_Parent_Det_Pf_Id
AND ccd.cc_acct_line_id = P_Parent_Acct_Line_Id
AND ccdi.cc_det_pf_line_num = ccd.cc_det_pf_line_num;
SELECT Nvl(cc_bc_enable_flag,'N')
INTO p_cc_bc_enable_flag
FROM igc_cc_bc_enable
WHERE set_of_books_id = p_set_of_books_id;
SELECT NVL(enable_budgetary_control_flag,'N')
INTO p_sbc_enable_flag
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
SELECT
-- req_encumbrance_type_id,
-- purch_encumbrance_type_id,
-- inv_encumbrance_type_id,
req_encumbrance_flag,
purch_encumbrance_flag
INTO
-- l_ap_req_encmbrnc_type_id,
-- l_ap_purch_encmbrnc_type_id,
-- p_inv_encumbrance_type_id,
p_sb_prov_encmbrnc_flag,
p_sb_conf_encmbrnc_flag
FROM financials_system_parameters;
SELECT count(*)
INTO l_COUNT
FROM fnd_application app,
gl_sets_of_books sob,
gl_period_statuses gl,
igc_cc_periods cp
WHERE sob.set_of_books_id = p_set_of_books_id
AND gl.set_of_books_id = sob.set_of_books_id
AND gl.application_id = app.application_id
AND app.application_short_name = 'SQLGL'
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 gl.adjustment_period_flag = 'N'
AND (p_date BETWEEN gl.start_date AND gl.end_date);
SELECT MIN(cc_start_date)
INTO l_min_rel_start_date
FROM igc_cc_headers_interface
WHERE interface_parent_header_id = p_interface_header_id;
SELECT MAX(cc_end_date)
INTO l_max_rel_end_date
FROM igc_cc_headers_interface
WHERE interface_parent_header_id = p_interface_header_id;
SELECT cc_det_pf_date
INTO l_cover_cc_det_pf_date
FROM igc_cc_det_pf_interface
WHERE interface_det_pf_line_id = p_interface_parent_det_pf_id;
SELECT gp.period_name,
gp.period_num,
gp.period_year
FROM gl_periods gp,
gl_sets_of_books sob
WHERE gp.period_set_name = sob.period_set_name
AND gp.period_type = sob.accounted_period_type
AND sob.set_of_books_id = p_sob_id
AND p_date BETWEEN gp.start_date AND gp.end_date
AND gp.adjustment_period_flag = 'N';
g_pa_fc_com_rec_tab( l_index ).last_update_date := SYSDATE;
g_pa_fc_com_rec_tab( l_index ).last_updated_by := FND_GLOBAL.user_id;
g_pa_fc_com_rec_tab( l_index ).last_update_login := FND_GLOBAL.login_id;
g_pa_fc_pay_rec_tab( l_index ).last_update_date := SYSDATE;
g_pa_fc_pay_rec_tab( l_index ).last_updated_by := FND_GLOBAL.user_id;
g_pa_fc_pay_rec_tab( l_index ).last_update_login := FND_GLOBAL.login_id;
SELECT * FROM igc_cc_headers_interface
WHERE batch_id = P_Batch_Id
ORDER BY cc_type DESC;
SELECT MIN(start_date) start_date, MAX(end_date) end_date
FROM GL_PERIODS GP,
GL_SETS_OF_BOOKS GB
WHERE
GP.period_set_name = GB.period_set_name AND
GP.period_type = GB.accounted_period_type AND
GB.set_of_books_id = P_Sob_Id AND
TO_CHAR(start_date, 'YYYY') = to_char(sysdate, 'YYYY') AND
TO_CHAR(end_date, 'YYYY') = to_char(sysdate, 'YYYY') AND
GP.adjustment_period_flag = 'N';
SELECT gl_bc_packets_s.nextval
FROM dual;
SELECT meaning
INTO l_option_name
FROM igi_lookups
WHERE lookup_code = 'CC'
AND lookup_type = 'GCC_DESCRIPTION';
DELETE IGC_CC_INTERFACE_ERRORS;
g_pa_fc_com_rec_tab.DELETE;
g_pa_fc_pay_rec_tab.DELETE;
SELECT NVL(cc_bc_enable_flag,'N') INTO l_cbc_enable_flag
FROM igc_cc_bc_enable
WHERE set_of_books_id = l_current_set_of_books_id;
SELECT currency_code INTO l_func_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = l_current_set_of_books_id;
IGC_CC_HEADERS_PKG.Insert_Row(
1.0,
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
l_return_status,
l_msg_count,
l_msg_data,
l_row_id,
l_header_id,
l_interface_header_record.Org_Id,
l_interface_header_record.CC_Type,
l_interface_header_record.CC_Num,
NVL(l_interface_header_record.CC_Version_num, 0) + 1,
l_parent_header_id,
l_interface_header_record.CC_State,
l_interface_header_record.CC_Ctrl_status,
l_interface_header_record.CC_Encmbrnc_Status,
l_interface_header_record.CC_Apprvl_Status,
l_interface_header_record.Vendor_Id,
l_interface_header_record.Vendor_Site_Id,
l_interface_header_record.Vendor_Contact_Id,
l_interface_header_record.Term_Id,
l_interface_header_record.Location_Id,
l_interface_header_record.Set_Of_Books_Id,
l_interface_header_record.CC_Acct_Date,
l_interface_header_record.CC_Desc,
l_interface_header_record.CC_Start_Date,
l_interface_header_record.CC_End_Date,
l_interface_header_record.CC_Owner_User_Id,
l_interface_header_record.CC_Preparer_User_Id,
l_interface_header_record.Currency_Code,
l_interface_header_record.Conversion_Type,
l_interface_header_record.Conversion_Date,
l_interface_header_record.Conversion_Rate,
sysdate,
l_current_user_id,
l_current_login_id,
NVL(l_interface_header_record.Created_By, l_current_user_id),
NVL(l_interface_header_record.Creation_Date, sysdate),
l_interface_header_record.CC_Current_User_Id,
l_interface_header_record.Wf_Item_Type,
l_interface_header_record.Wf_Item_Key,
l_interface_header_record.Attribute1,
l_interface_header_record.Attribute2,
l_interface_header_record.Attribute3,
l_interface_header_record.Attribute4,
l_interface_header_record.Attribute5,
l_interface_header_record.Attribute6,
l_interface_header_record.Attribute7,
l_interface_header_record.Attribute8,
l_interface_header_record.Attribute9,
l_interface_header_record.Attribute10,
l_interface_header_record.Attribute11,
l_interface_header_record.Attribute12,
l_interface_header_record.Attribute13,
l_interface_header_record.Attribute14,
l_interface_header_record.Attribute15,
l_interface_header_record.Context,
Nvl(l_interface_header_record.CC_Guarantee_Flag,'N'),
l_flag);
SELECT count(interface_det_pf_line_id)
INTO l_curr_year_pf_lines
FROM igc_cc_det_pf_interface b
WHERE b.cc_det_pf_date <= l_end_date
AND b.interface_acct_line_id IN (SELECT interface_acct_line_id
FROM igc_cc_acct_lines_interface a
WHERE a.interface_header_id = l_interface_header_record.Interface_Header_Id);
IGC_CC_PO_INTERFACE_PKG.UPDATE_PO_APPROVED_FLAG
( 1.0,
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
l_return_status,
l_msg_count,
l_msg_data,
l_header_id);
IGC_CC_ACTIONS_PKG.Insert_Row(
1.0,
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
l_return_status,
l_msg_count,
l_msg_data,
l_row_id,
l_header_id,
NVL(l_interface_header_record.CC_Version_num, 0) + 1,
'EN',
l_interface_header_record.CC_State,
l_interface_header_record.CC_ctrl_status,
l_interface_header_record.CC_Apprvl_Status,
l_history_message,
sysdate,
l_current_user_id,
l_current_login_id,
sysdate,
l_current_user_id );
IGC_CC_ACTIONS_PKG.Insert_Row(
1.0,
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
l_return_status,
l_msg_count,
l_msg_data,
l_row_id,
l_header_id,
NVL(l_interface_header_record.CC_Version_num, 0) + 1,
'EC',
l_interface_header_record.CC_State,
l_interface_header_record.CC_ctrl_status,
l_interface_header_record.CC_Apprvl_Status,
l_history_message,
sysdate,
l_current_user_id,
l_current_login_id,
sysdate,
l_current_user_id );
INSERT_ORPHAN_RECORDS
( l_error_status );
ELSE -- insert into pa_bc_packet unsuccessfull.
l_pa_cb_funds_check_pass := FALSE;
ELSE -- insert into pa_bc_packet unsuccessfull.
-- Log Interface error;
DELETE IGC.igc_cc_headers_interface
WHERE batch_id = P_Batch_Id;
DELETE IGC.igc_cc_acct_lines_interface
WHERE batch_id = P_Batch_Id;
DELETE IGC.igc_cc_det_pf_interface
WHERE batch_id = P_Batch_Id;
SELECT COUNT(*) INTO l_count
FROM igc_cc_headers
WHERE org_id = P_Org_Id
AND cc_num = P_Cc_Num;
SELECT interface_header_id, cc_start_date, cc_end_date, currency_code, conversion_type, conversion_rate, conversion_date
INTO l_interface_parent_header_id, l_start_date, l_end_date, l_curr_code, l_conversion_type, l_conversion_rate, l_conversion_date
FROM igc_cc_headers_interface
WHERE interface_header_id = P_Interface_Parent_Header_Id
AND cc_type = 'C';
SELECT vendor_id INTO l_vendor_id
FROM po_vendors
WHERE vendor_id = P_Vendor_Id
AND enabled_flag = 'Y'
AND sysdate BETWEEN NVL(start_date_active, sysdate-1)
AND NVL(end_date_active, sysdate+1);
SELECT vendor_site_id INTO l_vendor_site_id
FROM po_vendor_sites_all
WHERE vendor_site_id = P_Vendor_Site_Id
AND vendor_id = P_Vendor_Id
AND purchasing_site_flag = 'Y'
AND NVL(inactive_date, sysdate+1) > sysdate;
SELECT vendor_contact_id INTO l_vendor_contact_id
FROM po_vendor_contacts
WHERE vendor_site_id = P_Vendor_Site_Id
AND vendor_contact_id = P_Vendor_Contact_Id
AND NVL(inactive_date, sysdate+1) > sysdate;
SELECT term_id INTO l_term_id
FROM ap_terms_val_v
WHERE term_id = P_Term_Id;
SELECT location_id INTO l_location_id
FROM hr_locations
WHERE location_id = P_location_Id
AND bill_to_site_flag = 'Y'
AND NVL(inactive_date, sysdate+1) > sysdate;
SELECT 1 INTO l_count
FROM igc_cc_periods ccp, gl_sets_of_books sob, gl_periods glp
WHERE sob.set_of_books_id = P_Set_of_Books_Id
AND sob.period_set_name = glp.period_set_name
AND sob.accounted_period_type = glp.period_type
AND glp.adjustment_period_flag = 'N'
AND ccp.period_set_name = glp.period_set_name
AND ccp.period_name = glp.period_name
AND ccp.org_id = P_Org_Id
AND P_Cc_Acct_Date BETWEEN glp.start_date AND glp.end_date
AND ccp.cc_period_status IN ('O','F');
SELECT fu.user_id
INTO l_user_id
FROM fnd_user fu,
per_all_people_f p,
per_all_assignments_f a,
per_assignment_status_types past
WHERE fu.user_id = P_Cc_Owner_User_Id
AND sysdate BETWEEN NVL(fu.start_date, sysdate)
AND NVL(fu.end_date, sysdate)
AND fu.employee_id IS NOT NULL
AND fu.employee_id = p.person_id
/* AND p.business_group_id = (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp) */
AND p.business_group_id = (Decode (FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP') , 'Y' , p.business_group_id , (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp)))
AND p.employee_number is not null
AND trunc(sysdate) between p.effective_start_date and p.effective_end_date
AND a.person_id = p.person_id
AND a.primary_flag = 'Y'
AND trunc(sysdate) between a.effective_start_date
AND a.effective_end_date
AND a.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
AND a.assignment_type = 'E';
SELECT fu.user_id
INTO l_user_id
FROM fnd_user fu,
per_all_people_f p,
per_all_assignments_f a,
per_assignment_status_types past
WHERE fu.user_id = P_Cc_Preparer_User_Id
AND sysdate BETWEEN NVL(fu.start_date, sysdate)
AND NVL(fu.end_date, sysdate)
AND fu.employee_id IS NOT NULL
AND fu.employee_id = p.person_id
/* AND p.business_group_id = (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp) */
AND p.business_group_id = (Decode (FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP') , 'Y' , p.business_group_id , (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp)))
AND p.employee_number is not null
AND trunc(sysdate) between p.effective_start_date and p.effective_end_date
AND a.person_id = p.person_id
AND a.primary_flag = 'Y'
AND trunc(sysdate) between a.effective_start_date
AND a.effective_end_date
AND a.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
AND a.assignment_type = 'E';
SELECT currency_code INTO l_currency_code
FROM fnd_currencies_vl
WHERE enabled_flag = 'Y'
AND currency_flag = 'Y'
AND currency_code = P_Currency_Code;
SELECT user_id INTO l_user_id
FROM fnd_user
WHERE user_id = P_Created_By;
SELECT fu.user_id
INTO l_user_id
FROM fnd_user fu,
per_all_people_f p,
per_all_assignments_f a,
per_assignment_status_types past
WHERE fu.user_id = P_Cc_Current_User_Id
AND sysdate BETWEEN NVL(fu.start_date, sysdate)
AND NVL(fu.end_date, sysdate)
AND fu.employee_id IS NOT NULL
AND fu.employee_id = p.person_id
/* AND p.business_group_id = (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp) */
AND p.business_group_id = (Decode (FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP') , 'Y' , p.business_group_id , (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp)))
AND p.employee_number is not null
AND trunc(sysdate) between p.effective_start_date and p.effective_end_date
AND a.person_id = p.person_id
AND a.primary_flag = 'Y'
AND trunc(sysdate) between a.effective_start_date
AND a.effective_end_date
AND a.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
AND a.assignment_type = 'E';
SELECT * FROM igc_cc_acct_lines_interface
WHERE batch_id = g_batch_id
AND interface_header_id = P_Interface_Header_Id;
IGC_CC_ACCT_LINES_PKG.Insert_Row(
1.0,
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
l_return_status,
l_msg_count,
l_msg_data,
l_row_id,
l_acct_line_id,
P_Header_Id,
P_Parent_Header_Id,
l_parent_acct_line_id,
l_interface_acct_line_record.CC_Charge_Code_Combination_Id,
l_interface_acct_line_record.CC_Acct_Line_Num,
l_interface_acct_line_record.CC_Budget_Code_Combination_Id,
l_interface_acct_line_record.CC_Acct_Entered_Amt,
l_interface_acct_line_record.CC_Acct_Func_Amt,
l_interface_acct_line_record.CC_Acct_Desc,
l_interface_acct_line_record.CC_Acct_Billed_Amt,
l_interface_acct_line_record.CC_Acct_Unbilled_Amt,
l_interface_acct_line_record.CC_Acct_Taxable_Flag,
NULL,-- modified for Ebtax uptake for CC (Bug No-6472296) l_interface_acct_line_record.Tax_Id
l_interface_acct_line_record.CC_Acct_Encmbrnc_Amt,
l_interface_acct_line_record.CC_Acct_Encmbrnc_Date,
l_interface_acct_line_record.CC_Acct_Encmbrnc_Status,
l_interface_acct_line_record.Project_Id,
l_interface_acct_line_record.Task_Id,
l_interface_acct_line_record.Expenditure_Type,
l_interface_acct_line_record.Expenditure_Org_Id,
l_interface_acct_line_record.Expenditure_Item_Date,
sysdate,
P_User_Id,
P_Login_Id,
NVL(l_interface_acct_line_record.Creation_Date, sysdate),
NVL(l_interface_acct_line_record.Created_By, P_User_Id),
l_interface_acct_line_record.Attribute1,
l_interface_acct_line_record.Attribute2,
l_interface_acct_line_record.Attribute3,
l_interface_acct_line_record.Attribute4,
l_interface_acct_line_record.Attribute5,
l_interface_acct_line_record.Attribute6,
l_interface_acct_line_record.Attribute7,
l_interface_acct_line_record.Attribute8,
l_interface_acct_line_record.Attribute9,
l_interface_acct_line_record.Attribute10,
l_interface_acct_line_record.Attribute11,
l_interface_acct_line_record.Attribute12,
l_interface_acct_line_record.Attribute13,
l_interface_acct_line_record.Attribute14,
l_interface_acct_line_record.Attribute15,
l_interface_acct_line_record.Context,
Nvl(l_interface_acct_line_record.CC_Func_Withheld_Amt,0),
Nvl(l_interface_acct_line_record.CC_Ent_Withheld_Amt,0),
l_flag,
l_interface_acct_line_record.tax_classif_code); -- modified for Ebtax uptake (Bug No-6472296)
ELSE -- Insert was successfull.
-- The insert into igc_cc_acct_lines has been sucessfull
-- Call procedure to populate PLSQL table for PA
-- The call should be made -
-- If budgetary control is enabled in the commitment budget
-- And ((CC is provisIonal,
-- CC is already encumbered,
-- Provisional CCs are being encumbered)
-- Or (CC is confirmed,
-- CC is already encumbered,
-- Confirmed CCs are being encumbered))
-- And (CC acct line is attached to a project
-- And project is budgetary controlled)
-- And cc is of type Cover or Standard
-- Bug 2871052
IF g_cc_bc_enable_flag = 'Y'
AND ((g_cc_state = 'PR'
AND l_interface_acct_line_record.CC_Acct_Encmbrnc_Status = 'P'
/* Bug No : 6341012. SLA uptake. cc_flags no more exists AND g_cc_prov_encmbrnc_flag = 'Y' */ )
OR (g_cc_state = 'CM'
AND l_interface_acct_line_record.CC_Acct_Encmbrnc_Status = 'C'
/* Bug No : 6341012. SLA uptake. cc_flags no more exists AND g_cc_conf_encmbrnc_flag = 'Y' */
))
AND p_cc_type IN ('C', 'S')
AND (l_interface_acct_line_record.project_id IS NOT NULL
AND PA_BUDGET_FUND_PKG.Is_bdgt_intg_enabled
(p_project_id => l_interface_acct_line_record.project_id,
p_mode => 'C' ))
THEN
g_pa_cb_funds_check_required := TRUE;
END IF; -- insert into igc_cc_acct_line successfull
SELECT interface_acct_line_id
INTO l_interface_parent_acctline_id
FROM igc_cc_acct_lines_interface
WHERE interface_acct_line_id = P_Interface_Parent_AcctLine_Id
AND interface_header_id = P_Interface_Parent_Header_Id;
SELECT code_combination_id INTO l_code_combination_id
FROM gl_code_combinations
WHERE code_combination_id = P_Charge_Code_Combination_Id
AND enabled_flag = 'Y'
AND P_Cc_Acct_Date BETWEEN NVL(start_date_active, P_Cc_Acct_Date)
AND NVL(end_date_active, P_Cc_Acct_Date);
SELECT code_combination_id INTO l_code_combination_id
FROM gl_code_combinations
WHERE code_combination_id = P_Charge_Code_Combination_Id
AND enabled_flag = 'Y';
SELECT code_combination_id INTO l_code_combination_id
FROM gl_code_combinations
WHERE code_combination_id = P_Budget_Code_Combination_Id
AND enabled_flag = 'Y'
AND P_Cc_Acct_Date BETWEEN NVL(start_date_active, P_Cc_Acct_Date)
AND NVL(end_date_active, P_Cc_Acct_Date);
SELECT code_combination_id INTO l_code_combination_id
FROM gl_code_combinations
WHERE code_combination_id = P_Budget_Code_Combination_Id
AND enabled_flag = 'Y';
SELECT NVL(SUM(cc_det_pf_entered_amt), 0) INTO l_entered_amt
FROM igc_cc_det_pf_interface
WHERE interface_acct_line_id = P_Interface_Acct_Line_Id;
SELECT NVL(SUM(cc_acct_func_amt), 0) INTO l_func_amt
FROM igc_cc_acct_lines_interface
WHERE interface_parent_acct_line_id = P_Interface_Acct_Line_Id;
SELECT project_id INTO l_project_id
FROM pa_projects
WHERE project_id = P_Project_Id;
SELECT task_id INTO l_task_id
FROM pa_tasks
WHERE task_id = P_Task_Id
AND project_id = P_Project_Id;
-- select expenditure_type from pa_expenditure_types_expend_v et
-- where system_linkage_function = 'VI'
-- and et.project_id is null
-- and et.expenditure_type = P_Expenditure_Type
-- and (sysdate between expnd_typ_start_date_active and
-- nvl(expnd_typ_end_date_active, sysdate))
-- and (sysdate between sys_link_start_date_active and
-- nvl(sys_link_end_date_active,sysdate))
-- );
SELECT expenditure_type
INTO l_expenditure_type
FROM pa_expenditure_types_expend_v et
WHERE system_linkage_function = 'VI'
AND (sysdate between expnd_typ_start_date_active
AND nvl(expnd_typ_end_date_active, sysdate))
AND (sysdate between sys_link_start_date_active
AND nvl(sys_link_end_date_active,sysdate))
AND expenditure_type = p_expenditure_type;
-- SELECT organization_id INTO l_expenditure_org_id
-- FROM pa_organizations_expend_v
-- WHERE active_flag = 'Y'
-- AND organization_id = P_Expenditure_Org_Id
-- AND sysdate between date_from and nvl(date_to, sysdate);
SELECT a.organization_id INTO l_expenditure_org_id
FROM hr_all_organization_units a,
pa_all_organizations b
WHERE a.organization_id = b.organization_id
AND sysdate between a.date_from and nvl(a.date_to, sysdate)
AND b.pa_org_use_type = 'EXPENDITURES'
AND b.inactive_date IS NULL
AND b.organization_id = P_Expenditure_Org_Id;
SELECT cc_charge_code_combination_id, cc_budget_code_combination_id,
project_id, task_id, expenditure_type,
expenditure_org_id, expenditure_item_date
INTO l_charge_ccid, l_budget_ccid, l_cov_project_id, l_cov_task_id,
l_cov_expenditure_type, l_cov_expenditure_org_id, l_cov_expenditure_item_date
FROM igc_cc_acct_lines_interface
WHERE interface_acct_line_id = P_Interface_Parent_AcctLine_Id;
SELECT user_id INTO l_user_id
FROM fnd_user
WHERE user_id = P_Created_By;
SELECT * FROM igc_cc_det_pf_interface
WHERE batch_id = g_batch_id
AND interface_acct_line_id = P_Interface_Acct_Line_Id;
IGC_CC_DET_PF_PKG.Insert_Row(
1.0,
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
l_return_status,
l_msg_count,
l_msg_data,
l_row_id,
l_det_pf_id,
l_interface_det_pf_record.CC_Det_PF_Line_Num,
P_Acct_Line_Id,
l_parent_acct_line_id,
l_parent_det_pf_id ,
l_interface_det_pf_record.CC_Det_PF_Entered_Amt,
l_interface_det_pf_record.CC_Det_PF_Func_Amt,
l_interface_det_pf_record.CC_Det_PF_Date,
l_interface_det_pf_record.CC_Det_PF_Billed_Amt,
l_interface_det_pf_record.CC_Det_PF_Unbilled_Amt,
l_interface_det_pf_record.CC_Det_PF_Encmbrnc_Amt,
l_interface_det_pf_record.CC_Det_PF_Encmbrnc_Date,
l_interface_det_pf_record.CC_Det_PF_Encmbrnc_Status,
sysdate,
P_User_Id,
P_Login_Id,
NVL(l_interface_det_pf_record.Creation_Date, sysdate),
NVL(l_interface_det_pf_record.Created_By, P_User_Id),
l_interface_det_pf_record.Attribute1,
l_interface_det_pf_record.Attribute2,
l_interface_det_pf_record.Attribute3,
l_interface_det_pf_record.Attribute4,
l_interface_det_pf_record.Attribute5,
l_interface_det_pf_record.Attribute6,
l_interface_det_pf_record.Attribute7,
l_interface_det_pf_record.Attribute8,
l_interface_det_pf_record.Attribute9,
l_interface_det_pf_record.Attribute10,
l_interface_det_pf_record.Attribute11,
l_interface_det_pf_record.Attribute12,
l_interface_det_pf_record.Attribute13,
l_interface_det_pf_record.Attribute14,
l_interface_det_pf_record.Attribute15,
l_interface_det_pf_record.Context,
l_flag);
ELSE -- Insert was successfull.
-- The insert into igc_cc_acct_lines has been sucessfull
-- Call procedure to populate PLSQL table for PA
-- The call should be made -
-- If budgetary control is enabled in the standard budget
-- And ((CC is provisIonal,
-- CC is already encumbered,
-- Provisional CCs are being encumbered)
-- Or (CC is confirmed,
-- CC is already encumbered,
-- Confirmed CCs are being encumbered))
-- And (CC acct line is attached to a project
-- And project is budgetary controlled)
-- And cc is of type Cover or Standard
-- Bug 2871052
IF g_sbc_enable_flag = 'Y'
AND ((g_cc_state = 'PR' AND l_interface_det_pf_record.CC_Det_PF_Encmbrnc_Status = 'P')
/* Bug No : 6341012. SLA uptake. cc_flags no more exists AND g_cc_prov_encmbrnc_flag = 'Y' */
OR (g_cc_state = 'CM' AND l_interface_det_pf_record.CC_Det_PF_Encmbrnc_Status = 'C'))
/* Bug No : 6341012. SLA uptake. cc_flags no more exists AND g_cc_conf_encmbrnc_flag = 'Y' */
AND p_cc_type IN ('C', 'S')
AND (p_project_id IS NOT NULL
AND PA_BUDGET_FUND_PKG.Is_bdgt_intg_enabled
(p_project_id => p_project_id,
p_mode => 'S' ))
THEN
g_pa_sb_funds_check_required := TRUE;
END IF; -- Insert into igc_cc_det_pf was sucessfull.
SELECT application_id
INTO l_gl_application_id
FROM fnd_application
WHERE application_short_name = 'SQLGL';
SELECT interface_det_pf_line_id
INTO l_interface_parent_det_pf_id
FROM igc_cc_det_pf_interface
WHERE interface_det_pf_line_id = P_Interface_Parent_Det_Pf_Id;
SELECT NVL(SUM(cc_det_pf_func_amt), 0) INTO l_func_amt
FROM igc_cc_det_pf_interface
WHERE interface_par_det_pf_line_id = P_Interface_Det_Pf_Id;
SELECT 1 INTO l_count
FROM igc_cc_periods ccp, gl_sets_of_books sob, gl_period_statuses glp
WHERE sob.set_of_books_id = P_Set_of_Books_Id
AND sob.set_of_books_id = glp.set_of_books_id
AND sob.accounted_period_type = glp.period_type
AND sob.period_set_name = ccp.period_set_name
AND glp.adjustment_period_flag = 'N'
AND glp.application_id = l_gl_application_id
AND ccp.period_name = glp.period_name
AND ccp.org_id = P_Org_Id
AND P_Cc_Det_Pf_Encmbrnc_Date BETWEEN glp.start_date AND glp.end_date
AND ccp.cc_period_status IN ('O','F')
AND glp.closing_status IN ('O','F');
SELECT cc_det_pf_encmbrnc_date INTO l_det_pf_date
FROM igc_cc_det_pf_interface
WHERE interface_det_pf_line_id = P_Interface_Parent_Det_Pf_Id;
SELECT user_id INTO l_user_id
FROM fnd_user
WHERE user_id = P_Created_By;