[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_current_user_id := l_cc_header_rec.last_updated_by;
l_current_login_id := l_cc_header_rec.last_update_login;
SELECT currency_code INTO l_func_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = l_current_set_of_books_id;
g_debug_msg := 'CC Open API Main Header Record Insert Row Starts Here...';
IGC_CC_HEADERS_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_row_id,
l_header_id,
l_cc_header_rec.org_id,
l_cc_header_rec.CC_Type,
l_cc_header_rec.CC_Num,
l_cc_header_rec.CC_Ref_Num,
0, -- CC version number
l_cc_header_rec.parent_header_id,
'PR', -- CC state
'E', -- CC Control Status
'N', -- CC Encumbrance Status
'IN', -- CC Approval Status
l_cc_header_rec.Vendor_Id,
l_cc_header_rec.Vendor_Site_Id,
l_cc_header_rec.Vendor_Contact_Id,
l_cc_header_rec.Term_Id,
l_cc_header_rec.Location_Id,
l_cc_header_rec.Set_Of_Books_Id,
NULL, -- CC_Acct_Date
l_cc_header_rec.CC_Desc,
l_cc_header_rec.CC_Start_Date,
l_cc_header_rec.CC_End_Date,
l_cc_header_rec.CC_Owner_User_Id,
l_cc_header_rec.CC_Preparer_User_Id,
x_currency_code,
x_conversion_type,
x_conversion_date,
x_conversion_rate,
SYSDATE,
l_current_user_id,
l_current_login_id,
NVL(l_cc_header_rec.Created_By, l_current_user_id),
NVL(l_cc_header_rec.Creation_Date, sysdate),
l_cc_header_rec.CC_Preparer_User_Id, -- CC_Current_User_Id,
NULL, -- Wf_Item_Type,
NULL, -- Wf_Item_Key,
l_cc_header_rec.Attribute1,
l_cc_header_rec.Attribute2,
l_cc_header_rec.Attribute3,
l_cc_header_rec.Attribute4,
l_cc_header_rec.Attribute5,
l_cc_header_rec.Attribute6,
l_cc_header_rec.Attribute7,
l_cc_header_rec.Attribute8,
l_cc_header_rec.Attribute9,
l_cc_header_rec.Attribute10,
l_cc_header_rec.Attribute11,
l_cc_header_rec.Attribute12,
l_cc_header_rec.Attribute13,
l_cc_header_rec.Attribute14,
l_cc_header_rec.Attribute15,
l_cc_header_rec.Context,
l_cc_header_rec.CC_Guarantee_Flag,
l_flag
);
g_debug_msg := 'CC Open API Main Header Record Insert Row Not Successful...'||l_msg_data;
SELECT GL.set_of_books_id
FROM gl_sets_of_books GL
WHERE GL.set_of_books_id = p_cc_header_rec.set_of_books_id;
SELECT name
FROM hr_organization_units
WHERE organization_id = p_cc_header_rec.org_id;
SELECT HAOU.name
FROM hr_organization_information OOD,
hr_all_organization_units HAOU
WHERE OOD.organization_id = p_cc_header_rec.org_id
AND OOD.organization_id = HAOU.organization_id
AND OOD.org_information3 || '' = to_char(p_cc_header_rec.set_of_books_id)
AND HAOU.organization_id || '' = OOD.organization_id;
SELECT cc_num
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_rec.parent_header_id
AND cc_state IN ('PR','CM')
AND cc_apprvl_status = 'AP';
SELECT CCNM.cc_num_method,
CCNM.cc_num_datatype
INTO l_cc_num_method,
l_cc_num_datatype
FROM igc_cc_system_options_all CCNM
WHERE CCNM.org_id = p_current_org_id;
SELECT COUNT(*) INTO l_count
FROM igc_cc_headers
WHERE /*org_id = p_cc_header_rec.org_id
AND --Commented during MOAC uptake */
cc_num = p_cc_header_rec.cc_num;
SELECT COUNT(*) INTO l_count
FROM igc_cc_headers
WHERE /*org_id = p_cc_header_rec.org_id
AND --Commented during MOAC uptake */
cc_ref_num = p_cc_header_rec.cc_ref_num;
SELECT cchd.currency_code,
cchd.conversion_type,
cchd.conversion_rate,
cchd.conversion_date
INTO l_cov_curr_code,
l_cov_conversion_type,
l_cov_conversion_rate,
l_cov_conversion_date
FROM igc_cc_headers cchd
WHERE cchd.cc_header_id = p_cc_header_rec.parent_header_id
AND cchd.cc_type = 'C';
SELECT vendor_id,
invoice_currency_code
INTO l_vendor_id,
l_vendor_curr_code
FROM po_vendors
WHERE vendor_id = p_cc_header_rec.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,
invoice_currency_code,
terms_id,
bill_to_location_id
INTO l_vendor_site_id,
l_vendor_site_curr_code,
l_populate_terms_id,
l_billed_to_location_id
FROM po_vendor_sites_all
WHERE org_id = p_cc_header_rec.org_id /* Addded this condition for MOAC uptake */
AND vendor_site_id = p_cc_header_rec.vendor_site_id
AND vendor_id = p_cc_header_rec.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_cc_header_rec.vendor_site_id
AND vendor_contact_id = p_cc_header_rec.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_cc_header_rec.term_id;
SELECT location_id
INTO l_location_id
FROM hr_locations
WHERE location_id = p_cc_header_rec.location_id
AND bill_to_site_flag = 'Y'
AND NVL(inactive_date, sysdate+1) > sysdate;
SELECT fu.user_id
INTO l_user_id
FROM fnd_user fu,
per_people_f p, /* per_all_people_f p, --Commented during MOAC uptake for bug#6341012*/
per_all_assignments_f a,
per_assignment_status_types past
WHERE fu.user_id = p_cc_header_rec.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) --Commented during MOAC uptake for bug #6341012 */
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_people_f p, /* per_all_people_f p, --Commented for Bug#6341012
during MOAC uptake*/
per_all_assignments_f a,
per_assignment_status_types past
WHERE fu.user_id = p_cc_header_rec.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)
--Commented during MOAC uptake for bug#6341012 */
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 SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE)
AND currency_code = x_currency_code;
SELECT ccsp.default_rate_type
INTO x_conversion_type
FROM igc_cc_system_options_all ccsp
WHERE ccsp.org_id = p_cc_header_rec.org_id;
SELECT conversion_type
INTO l_conversion_type
FROM gl_daily_conversion_types
WHERE conversion_type <> 'Period Average (Upgrade)'
AND conversion_type <> 'EMU FIXED'
AND conversion_type = x_conversion_type;
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_id = p_cc_header_rec.created_by;
IF p_cc_header_rec.last_updated_by IS NOT NULL THEN
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_id = p_cc_header_rec.last_updated_by;
FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LAST_UPDATED_BY');
FND_MESSAGE.SET_TOKEN('LAST_UPDATED_BY', TO_CHAR(p_cc_header_rec.last_updated_by), TRUE);
g_debug_msg := 'CC Open API Validate Invalid Last Updated By...';
FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LAST_UPDATED_BY');
FND_MESSAGE.SET_TOKEN('LAST_UPDATED_BY', TO_CHAR(p_cc_header_rec.last_updated_by), TRUE);
g_debug_msg := 'CC Open API Validate Invalid Last Updated By is NULL...';
IF p_cc_header_rec.last_update_login IS NOT NULL THEN
BEGIN
SELECT login_id
INTO l_login_id
FROM fnd_logins
WHERE login_id = p_cc_header_rec.last_update_login;
FND_MESSAGE.SET_TOKEN('LAST_UPDATE_LOGIN', TO_CHAR(p_cc_header_rec.last_update_login), TRUE);
g_debug_msg := 'CC Open API Validate Invalid Last Update Login...';
FND_MESSAGE.SET_TOKEN('LAST_UPDATE_LOGIN', TO_CHAR(p_cc_header_rec.last_update_login), TRUE);
g_debug_msg := 'CC Open API Validate Invalid Last Update Login is NULL...';
SELECT igc_cc_headers_s.nextval
INTO x_header_id
FROM DUAL;
PROCEDURE CC_Update_Control_Status_API (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_cc_num IN igc_cc_headers.cc_num%TYPE,
p_set_of_books_id IN igc_cc_headers.set_of_books_id%TYPE,
p_org_id IN igc_cc_headers.org_id%TYPE,
p_action_code IN fnd_lookups.lookup_code%TYPE,
p_last_updated_by IN igc_cc_headers.last_updated_by%TYPE,
p_last_update_login IN igc_cc_headers.last_update_login%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name VARCHAR2(30);
SELECT HAOU.name
FROM hr_organization_information OOD,
hr_all_organization_units HAOU
WHERE OOD.organization_id = l_current_org_id
AND OOD.organization_id = HAOU.organization_id
AND OOD.org_information3 || '' = to_char(p_set_of_books_id)
AND HAOU.organization_id || '' = OOD.organization_id;
SELECT cchd.cc_header_id
FROM igc_cc_headers cchd
WHERE cchd.cc_num = p_cc_num;
SELECT cchd.cc_ref_num
FROM igc_cc_headers cchd
WHERE cchd.cc_header_id = l_cc_header_id
AND cchd.cc_ref_num IS NOT NULL;
SELECT *
FROM igc_cc_headers cchd
WHERE cchd.cc_header_id = l_cc_header_id
AND cchd.cc_apprvl_status = 'AP'
AND cchd.cc_state = 'CM';
l_api_name := 'CC_Update_Control_Status_API';
l_full_path := g_path||'cc_update_control_status_API';
SAVEPOINT CC_Update_API_PT;
g_debug_msg := 'CC Update API Debug mode enabled...';
g_debug_msg := 'CC Update APi Incorrect version...';
g_debug_msg := 'CC Update API Starts Here...';
l_current_user_id := p_last_updated_by;
l_current_login_id := p_last_update_login;
g_debug_msg := 'CC Update API Validate Set of books ID and Org ID Combo Failed...';
g_debug_msg := 'CC Update APi CC Found or not...'||x_msg_data;
g_debug_msg := 'CC Update APi CC Found or not...'||x_msg_data;
g_debug_msg := 'CC Update APi CC Found or not approved / confirmed...'||x_msg_data;
g_debug_msg := 'CC Update APi Action Not Allowed...'||x_msg_data;
SELECT lkup.lookup_code,
lkup.meaning
INTO l_action_type_code,
l_action_meaning
FROM fnd_lookups lkup
WHERE lkup.lookup_type = 'IGC_CC_ACTION_TYPE'
AND lkup.lookup_code = p_action_code
AND lkup.lookup_code IN ('OP','CL','OH','RH');
g_debug_msg := 'CC Update APi Invalid Action Code...'||x_msg_data;
SELECT login_id
INTO l_login_id
FROM fnd_logins
WHERE login_id = l_current_login_id;
FND_MESSAGE.SET_TOKEN('LAST_UPDATE_LOGIN', TO_CHAR(l_current_login_id), TRUE);
g_debug_msg := 'CC Update API Validate Invalid Last Update Login...';
FND_MESSAGE.SET_TOKEN('LAST_UPDATE_LOGIN', TO_CHAR(l_current_login_id), TRUE);
g_debug_msg := 'CC Update API Validate Invalid Last Update Login is NULL...';
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_id = l_current_user_id;
FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LAST_UPDATED_BY');
FND_MESSAGE.SET_TOKEN('LAST_UPDATED_BY', TO_CHAR(l_current_user_id), TRUE);
g_debug_msg := 'CC Update API Validate Invalid Last Updated By...';
FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LAST_UPDATED_BY');
FND_MESSAGE.SET_TOKEN('LAST_UPDATED_BY', TO_CHAR(l_current_user_id), TRUE);
g_debug_msg := 'CC Update API Validate Invalid Last Updated By is NULL...';
SELECT lkup.lookup_code,
lkup.meaning
INTO l_action_type_code,
l_control_meaning
FROM fnd_lookups lkup
WHERE lkup.lookup_type = 'IGC_CC_CONTROL_STATUS'
AND lkup.lookup_code = l_cc_header_rec.cc_ctrl_status;
g_debug_msg := 'CC Update APi Invalid Control Status ...'||x_msg_data;
g_debug_msg := 'CC Update APi '||p_action_code||' Action not allowed ...'||x_msg_data;
g_debug_msg := 'CC Update APi '||p_action_code||' Action not allowed ...'||x_msg_data;
g_debug_msg := 'CC Update APi '||p_action_code||' Action not allowed ...'||x_msg_data;
g_debug_msg := 'CC Update APi '||p_action_code||' Action not allowed ...'||x_msg_data;
g_debug_msg := 'CC Update APi Invalid Action Code...'||x_msg_data;
g_debug_msg := 'CC Update APi Release On Hold begins here ...';
g_debug_msg := 'CC Update APi Check Budgetary Control Not Successful...'||x_msg_data;
g_debug_msg := 'CC Update APi Validate CC begins here ...';
g_debug_msg := 'CC Update APi Validate CC Not Successful...'||x_msg_data;
g_debug_msg := 'CC Update APi Approval Process begins here ...';
SELECT to_char(IGC_CC_WF_ITEMKEY_S.NEXTVAL)
INTO l_seq
FROM sys.dual;
UPDATE igc_cc_headers_all ICH
SET ICH.cc_ctrl_status = l_new_ctrl_status,
ICH.cc_apprvl_status = l_new_apprvl_status,
ICH.wf_item_type = 'CCAPPWF',
ICH.wf_item_key = l_itemkey
WHERE ICH.cc_header_id = l_cc_header_id
AND ICH.cc_num = l_cc_header_rec.cc_num
AND ICH.set_of_books_id = l_cc_header_rec.set_of_books_id
AND ICH.org_id = l_cc_header_rec.org_id;
g_debug_msg := 'CC Update APi Incorrect Update ...';
ROLLBACK to CC_Update_API_PT;
g_debug_msg := 'CC Update APi Insert into Action History begins here ...';
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_row_id,
l_cc_header_id,
NVL(l_cc_header_rec.cc_version_num, 0),
SUBSTR(p_action_code,1,2),
l_cc_header_rec.cc_state,
l_new_ctrl_status,
l_new_apprvl_status,
'CC Update API',
SYSDATE,
l_current_user_id,
l_current_login_id,
SYSDATE,
l_current_user_id
);
ROLLBACK to CC_Update_API_PT;
g_debug_msg := 'CC Update APi Action History Insertion Not Successful...'||l_msg_data;
g_debug_msg := 'CC Update API Commiting before Approval process...';
g_debug_msg := 'CC Update APi Preparer Can Approve Check begins here ...';
g_debug_msg := 'CC Update APi Preparer Can Approve Not Successful...'||x_msg_data;
g_debug_msg := 'CC Update APi Workflow Call begins here ...'||l_result;
p_note => 'CC Update API',
p_debug_mode => g_debug_mode
);
g_debug_msg := 'CC Update APi Approved by preparer begins here ...';
p_cc_notes => 'CC Update API',
p_acct_date => SYSDATE
);
END IF; -- Update of statuses.
g_debug_msg := 'CC Update APi Open, Close, On Hold Actions begins here ...';
UPDATE igc_cc_headers_all ICH
SET ICH.cc_ctrl_status = l_new_ctrl_status
WHERE ICH.cc_header_id = l_cc_header_id
AND ICH.cc_num = l_cc_header_rec.cc_num
AND ICH.set_of_books_id = l_cc_header_rec.set_of_books_id
AND ICH.org_id = l_cc_header_rec.org_id;
ROLLBACK to CC_Update_API_PT;
g_debug_msg := 'CC Update APi Incorrect Update...'||x_msg_data;
IGC_CC_PO_INTERFACE_PKG.UPDATE_PO_APPROVED_FLAG (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
X_return_status => l_return_status,
X_msg_count => l_msg_count,
X_msg_data => l_msg_data,
p_cc_header_id => l_cc_header_rec.cc_header_id);
ROLLBACK to CC_Update_API_PT;
g_debug_msg := 'CC Update APi Update PO Approved Flag Not Successful...'||x_msg_data;
g_debug_msg := 'CC Update APi Insert into Action History begins here ...';
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_row_id,
l_cc_header_id,
NVL(l_cc_header_rec.cc_version_num, 0),
SUBSTR(p_action_code,1,2),
l_cc_header_rec.cc_state,
l_new_ctrl_status,
l_cc_header_rec.cc_apprvl_status,
'CC Update API',
sysdate,
l_current_user_id,
l_current_login_id,
sysdate,
l_current_user_id );
ROLLBACK to CC_Update_API_PT;
g_debug_msg := 'CC Update APi Action History Insertion Not Successful...'||l_msg_data;
g_debug_msg := 'CC Update API Commiting After Successful Commitment Actions Open or Close or On Hold...';
END IF; -- Update PO Approved Flag results.
END IF; -- Update Control Statuses.
ROLLBACK to CC_Update_API_PT;
ROLLBACK to CC_Update_API_PT;
ROLLBACK to CC_Update_API_PT;
END CC_Update_Control_Status_API;
SELECT ICH.cc_header_id,
ICH.cc_ref_num
FROM igc_cc_headers ICH
WHERE ICH.org_id = p_org_id
AND ICH.set_of_books_id = p_set_of_books_id
AND ICH.cc_num = p_cc_num;
SELECT HAOU.name
FROM hr_organization_information OOD,
hr_all_organization_units HAOU
WHERE OOD.organization_id = p_org_id
AND OOD.organization_id = HAOU.organization_id
AND OOD.org_information3 || '' = to_char(p_set_of_books_id)
AND HAOU.organization_id || '' = OOD.organization_id;
SELECT ICH.cc_num,
ICH.cc_header_id
FROM igc_cc_headers ICH
WHERE /*ICH.org_id = p_org_id
AND ICH.set_of_books_id = p_set_of_books_id
AND --Commented during r12 MOAC uptake */
ICH.cc_num = p_cc_num;
SELECT ICH.cc_ref_num
FROM igc_cc_headers ICH
WHERE /*ICH.org_id = p_org_id
AND ICH.set_of_books_id = p_set_of_books_id
AND --Commented during MOAC uptake */
ICH.cc_ref_num = p_cc_ref_num;
SELECT HAOU.name
FROM hr_organization_information OOD,
hr_all_organization_units HAOU
WHERE OOD.organization_id = p_org_id
AND OOD.organization_id = HAOU.organization_id
AND OOD.org_information3 || '' = to_char(p_set_of_books_id)
AND HAOU.organization_id || '' = OOD.organization_id;
UPDATE igc_cc_headers_all ICH
SET ICH.cc_ref_num = p_cc_ref_num
WHERE ICH.cc_header_id = l_cc_header_id
AND ICH.cc_num = p_cc_num
AND ICH.set_of_books_id = p_set_of_books_id
AND ICH.org_id = p_org_id;
SELECT 'x'
FROM fnd_responsibility
WHERE responsibility_id = p_responsibility_id
AND application_id = p_resp_appl_id;
SELECT 'x'
FROM fnd_user
WHERE user_id = p_user_id;