[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Procedure update CC with the new statuses */
PROCEDURE Update_CC(
x_return_status OUT NOCOPY VARCHAR2);
PROCEDURE Select_Approver
(
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Select_Approver' ;
l_full_path VARCHAR2(255) := g_path||'Select_Approver';
SAVEPOINT Select_Approver;
ROLLBACK TO Select_Approver;
END Select_Approver ;
CURSOR c_update_cc IS
SELECT cc_apprvl_status
FROM igc_cc_headers
WHERE cc_header_id = g_cc_header_id
FOR UPDATE;
SELECT wf_approval_itemtype,
wf_approval_process
FROM igc_cc_routing_ctrls
WHERE (org_id,cc_type,cc_state)
IN (
SELECT org_id ,
cc_type ,
cc_state
FROM igc_cc_headers
WHERE cc_header_id = g_cc_header_id
);
OPEN c_update_cc;
FETCH c_update_cc INTO g_cc_state;
IF c_update_cc%NOTFOUND THEN
CLOSE c_update_cc;
Put_Debug_Msg( l_full_path,'Update CC header to IP');
UPDATE igc_cc_headers
SET cc_apprvl_status = 'IP',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE CURRENT OF c_update_cc;
CLOSE c_update_cc;
SELECT decode(p_action,
'A','PRAP'||decode(g_bc_reqired,'Y','P','N'),
'R','PRRJN',
'E','PRINN',
'F','PRINN'
)
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A','PRAPP',
'R','PRRJP',
'E','PRINP'
) -- No Failed action
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A','PRAP'||decode(g_bc_reqired,'Y','P','N'),
'R','PRRJN',
'E','PRRRN',
'F','PRRRN'
)
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A','PRAPP',
'R','PRRJP',
'E','PRRRP'
) -- No Failed action
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A','CMAP'||decode(g_bc_reqired,'Y','C','N'),
'R','PRRRN',
'E','CMINN',
'F','CMRJN'
)
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A','CMAPC',
'R','PRRRP',
'E','CMINT',
'F','CMRJT'
)
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A','CMAPC',
'R','CMRJC',
'E','CMINC'
)
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A','CMAP'||decode(g_bc_reqired,'Y','C','N'),
'R','CMRJN',
'E','CMRRN',
'F','CMRRN'
)
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A','CMAPC',
'R','CMRJC',
'E','CMRRC'
)
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A','CLAPN',
'R','PR'||l_old_appr_status||'N',
'E','CLINN'
)
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A','CLAPN',
'R','PR'||l_old_appr_status||'P',
'E','CLINP'
)
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A','CTAPN',
'R','CMAPC',
'E','CTINC',
'F','CTINC'
)
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A','CTAPN',
'R','CMAPN',
'E','CTINN'
)
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'A',g_cc_state||'AP'||decode(g_bc_reqired,'N',g_cc_enc_status,decode(g_cc_state,'PR','P','C')),
'R',g_cc_state||'RJ'||g_cc_enc_status,
'E',g_cc_state||'RJ'||g_cc_enc_status,
'F',g_cc_state||'RJ'||'N'
)
INTO l_new_state
FROM dual;
SELECT decode(p_action,
'F',g_bc_failure_message,
'E',substr(g_error_text,1,240),
'A',substr(g_reject_note,1,240),
'R',substr(g_reject_note,1,240))
INTO g_action_notes
FROM dual;
SELECT meaning
FROM fnd_lookups
WHERE lookup_code = l_code
AND lookup_type = l_type;
SELECT name
FROM hr_organization_units
WHERE organization_id = g_org_id;
SELECT org_id ,
cc_type ,
cc_num ,
cc_version_num ,
cc_state ,
cc_ctrl_status ,
cc_encmbrnc_status ,
cc_apprvl_status ,
set_of_books_id ,
cc_acct_date ,
cc_desc ,
cc_start_date ,
cc_end_date ,
f1.employee_id user_id ,
f2.employee_id owner_id
FROM igc_cc_headers,
fnd_user f1,
fnd_user f2
WHERE cc_header_id = g_cc_header_id
AND f1.user_id=cc_owner_user_id
AND f2.user_id=cc_preparer_user_id;
SELECT use_positions_flag
FROM financials_system_parameters;
SELECT cc_version_num ,
cc_encmbrnc_status
FROM igc_cc_headers
WHERE cc_header_id = g_cc_header_id;
Update_CC(x_return_status =>l_return_status );
IGC_CC_ACTIONS_PKG.Insert_Row
( p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rowid => l_rowid,
p_cc_header_id => g_cc_header_id,
p_cc_action_version_num => g_cc_version_number,
p_cc_action_type => g_cc_action_type,
p_cc_action_state => g_cc_new_state,
p_cc_action_ctrl_status => g_cc_ctrl_status,
p_cc_action_apprvl_status => g_cc_new_appr_status,
p_cc_action_notes => g_action_notes,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id
);
Put_Debug_Msg( l_full_path,'Error during history record insertion');
/* Procedure update CC with the new statuses */
PROCEDURE Update_CC(
x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_CC' ;
CURSOR c_update_cc IS
SELECT cc_state ,
cc_encmbrnc_status ,
cc_apprvl_status
FROM igc_cc_headers
WHERE cc_header_id = g_cc_header_id
FOR UPDATE;
l_full_path VARCHAR2(500) := g_path || 'Update_CC';
FOR c_update_cc_rec IN c_update_cc LOOP
UPDATE igc_cc_headers
SET cc_state = g_cc_new_state,
cc_encmbrnc_status = g_cc_new_enc_status,
cc_apprvl_status = g_cc_new_appr_status,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE CURRENT OF c_update_cc;
END Update_CC;
SELECT business_group_id
FROM financials_system_parameters;
SELECT use_positions_flag
FROM financials_system_parameters;
SELECT pos_structure_version_id
FROM per_pos_structure_versions
WHERE position_structure_id =
( SELECT default_approval_path_id
FROM igc_cc_routing_ctrls
WHERE org_id = g_org_id
AND cc_type = g_cc_type
AND cc_state = g_cc_state
)
AND sysdate
BETWEEN NVL(date_from,sysdate) AND NVL(date_to,sysdate);
SELECT position_id,
job_id,
supervisor_id
FROM per_assignments_f
WHERE person_id = g_approver_id
AND business_group_id = g_business_group_id
AND sysdate BETWEEN effective_start_date
AND effective_end_date;
SELECT pep.person_id
FROM per_assignments_f ass,
per_all_people_f pep
WHERE position_id = cpos_id
AND ass.person_id = pep.person_id
AND ass.business_group_id = g_business_group_id
AND pep.business_group_id = g_business_group_id
AND sysdate BETWEEN ass.effective_start_date
AND ass.effective_end_date
ORDER BY pep.full_name;
SELECT pep.person_id
FROM per_assignments_f ass,
per_all_people_f pep
WHERE job_id = cjob_id
AND ass.person_id = pep.person_id
AND ass.business_group_id = g_business_group_id
AND pep.business_group_id = g_business_group_id
AND sysdate BETWEEN ass.effective_start_date
AND ass.effective_end_date
ORDER BY pep.full_name;
SELECT parent_position_id
FROM per_pos_structure_elements
WHERE subordinate_position_id = subpos_id
AND business_group_id = g_business_group_id
AND pos_structure_version_id =g_pos_structure_version_id;
SELECT position_id,
job_id
FROM per_assignments_f
WHERE person_id = g_approver_id
AND business_group_id = g_business_group_id
AND sysdate BETWEEN effective_start_date
AND effective_end_date;
SELECT control_group_id
FROM igc_cc_control_functions
WHERE sysdate BETWEEN NVL(start_date,sysdate-1) AND NVL(end_date,sysdate+1)
AND ( (l_pos_id IS NOT NULL AND position_id = l_pos_id)
OR (l_job_id IS NOT NULL AND job_id = l_job_id)
)
AND cc_state = g_cc_state
AND cc_type = g_cc_type
AND org_id = g_org_id ;
SELECT control_group_id
FROM igc_cc_control_functions
WHERE sysdate BETWEEN nvl(start_date,sysdate) AND nvl(end_date,sysdate)
AND position_id = p_position_id
AND cc_state = g_cc_state
AND cc_type = g_cc_type
AND org_id = g_org_id;
SELECT control_rule_id ,
rule_type_code ,
amount_limit ,
segment1_low ,
segment2_low ,
segment3_low ,
segment4_low ,
segment5_low ,
segment6_low ,
segment7_low ,
segment8_low ,
segment9_low ,
segment10_low ,
segment11_low ,
segment12_low ,
segment13_low ,
segment14_low ,
segment15_low ,
segment16_low ,
segment17_low ,
segment18_low ,
segment19_low ,
segment20_low ,
segment21_low ,
segment22_low ,
segment23_low ,
segment24_low ,
segment25_low ,
segment26_low ,
segment27_low ,
segment28_low ,
segment29_low ,
segment30_low ,
segment1_high ,
segment2_high ,
segment3_high ,
segment4_high ,
segment5_high ,
segment6_high ,
segment7_high ,
segment8_high ,
segment9_high ,
segment10_high ,
segment11_high ,
segment12_high ,
segment13_high ,
segment14_high ,
segment15_high ,
segment16_high ,
segment17_high ,
segment18_high ,
segment19_high ,
segment20_high ,
segment21_high ,
segment22_high ,
segment23_high ,
segment24_high ,
segment25_high ,
segment26_high ,
segment27_high ,
segment28_high ,
segment29_high ,
segment30_high
FROM igc_cc_control_rules
WHERE org_id = g_org_id
AND control_group_id = l_control_group_id;
SELECT enabled_flag ,
active_date ,
amount
FROM igc_cc_control_groups
WHERE org_id = g_org_id
AND control_group_id = l_control_group_id;
SELECT ccal.cc_acct_line_id line_id,
ccal.cc_charge_code_combination_id ccid,
IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id,
NVL(ccal.cc_acct_entered_amt,0)) amount
FROM igc_cc_acct_lines ccal
WHERE ccal.cc_header_id = g_cc_header_id
ORDER BY ccal.cc_acct_line_id;
SELECT SUM(IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id,
NVL(ccal.cc_acct_entered_amt,0)))
FROM igc_cc_acct_lines ccal
WHERE ccal.cc_header_id = g_cc_header_id;
SELECT segment1 ,
segment2 ,
segment3 ,
segment4 ,
segment5 ,
segment6 ,
segment7 ,
segment8 ,
segment9 ,
segment10 ,
segment11 ,
segment12 ,
segment13 ,
segment14 ,
segment15 ,
segment16 ,
segment17 ,
segment18 ,
segment19 ,
segment20 ,
segment21 ,
segment22 ,
segment23 ,
segment24 ,
segment25 ,
segment26 ,
segment27 ,
segment28 ,
segment29 ,
segment30
FROM gl_code_combinations
WHERE code_combination_id = l_code_id;
SELECT cc_action_apprvl_status
FROM igc_cc_actions
WHERE (cc_header_id, cc_action_num)
IN ( SELECT cc_header_id,max(cc_action_num)
FROM igc_cc_actions
WHERE cc_action_state ='PR'
AND cc_header_id = g_cc_header_id
GROUP BY cc_header_id) ;
SELECT enforce_vendor_hold_flag
FROM igc_cc_system_options_all /*igc_cc_system_parameters*/
WHERE org_id = g_org_id;
SELECT hold_flag
FROM po_vendors
WHERE vendor_id =
(SELECT vendor_id
FROM igc_cc_headers
WHERE cc_header_id = g_cc_header_id);