[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT text_value
INTO l_value
FROM wf_notification_attributes
WHERE notification_id = p_nid
AND name = 'TU_FORM';
UPDATE wf_notification_attributes
SET text_value = l_new_value
WHERE notification_id = p_nid
AND name = 'TU_FORM';
PROCEDURE Selector(itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2) IS
l_session_org_id NUMBER;
DEBUG_LOG_STRING (l_proc_level, 'Selector.Msg1',
' ** BEGIN SELECTOR ** ');
DEBUG_LOG_STRING (l_proc_level, 'Selector.Msg',
' Setting apps context with userid, respid, applid as '
||l_user_id ||' '|| l_resp_id ||' '|| l_appl_id );
DEBUG_LOG_STRING (l_proc_level, 'Selector.Msg3',
' result --> ' || resultout);
DEBUG_LOG_STRING (l_proc_level, 'Selector.Msg4',
' ** END SELECTOR ** ');
DEBUG_LOG_UNEXP_ERROR ('Selector.Unexp1','DEFAULT');
Wf_Core.Context ('IGI_EXP_APPROVAL_PKG','Selector', itemtype, itemkey,
TO_CHAR(actid),funcmode);
END Selector;
UPDATE igi_exp_tus tus
SET tus.tu_status = 'TRA'
WHERE tus.tu_id = p_tu_id;
UPDATE igi_exp_dus dus
SET dus.du_status = 'TRA'
WHERE dus.tu_id = p_tu_id;
SELECT igi_exp_tu_run_s1.nextval
INTO l_approval_run_id
FROM sys.dual;
select tu_legal_number into l_tu_legal_number
from igi_exp_tus WHERE tu_id = p_tu_id;
SELECT employee_id
INTO l_transmitter_emp_id
FROM fnd_user
WHERE user_id = l_transmitter_fnd_id;
SELECT hap.position_id,
hap.name,
hap.business_group_id,
hap.organization_id
FROM hr_all_positions_f hap,
per_all_assignments_f paa,
per_employees_current_x pec
WHERE pec.employee_id = p_emp_id
AND NVL(pec.inactive_date,SYSDATE) >= SYSDATE
AND pec.business_group_id = paa.business_group_id
AND pec.assignment_id = paa.assignment_id
AND pec.organization_id = paa.organization_id
AND paa.assignment_type = 'E'
AND paa.effective_start_date <= SYSDATE
AND paa.effective_end_date >= SYSDATE
AND paa.business_group_id = hap.business_group_id
AND paa.position_id IS NOT NULL
AND paa.position_id = hap.position_id
AND paa.organization_id = hap.organization_id
AND hap.date_effective <= SYSDATE
AND NVL(hap.date_end, SYSDATE) >= SYSDATE
AND NVL(UPPER(hap.status), 'VALID') NOT IN ('INVALID');
SELECT pos_structure_version_id
FROM per_pos_structure_versions
WHERE position_structure_id = p_hierarchy_id
AND sysdate BETWEEN date_from AND NVL(date_to, sysdate)
AND business_group_id = p_business_group_id
AND version_number =
(SELECT MAX(version_number)
FROM per_pos_structure_versions
WHERE position_structure_id = p_hierarchy_id
AND sysdate BETWEEN date_from AND NVL(date_to,sysdate)
AND business_group_id = p_business_group_id);
SELECT pos_structure_element_id
FROM per_pos_structure_elements
WHERE pos_structure_version_id = p_pos_structure_ver_id
AND business_group_id = p_business_group_id
AND subordinate_position_id = p_position_id;
SELECT pos_structure_element_id
FROM per_pos_structure_elements
WHERE pos_structure_version_id = p_pos_structure_ver_id
AND business_group_id = p_business_group_id
AND parent_position_id = p_position_id;
SELECT apprv_profile_id
INTO l_apprv_profile_id
FROM igi_exp_tus
WHERE tu_id = l_tu_id;
SELECT pos_hierarchy_id
,legal_num_pos_id
,final_apprv_pos_id
INTO l_pos_hierarchy_id
,l_legal_num_pos_id
,l_final_apprv_pos_id
FROM igi_exp_apprv_profiles
WHERE apprv_profile_id = l_apprv_profile_id;
SELECT parent_position_id
FROM per_pos_structure_elements
WHERE pos_structure_version_id = p_pos_structure_version_id
AND business_group_id = p_business_group_id
AND subordinate_position_id = p_position_id;
PROCEDURE update_tu_status_to_avl ( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2) IS
l_tu_id NUMBER;
DEBUG_LOG_STRING (l_proc_level, 'Update_tu_status_to_avl.Msg1',
' ** BEGIN UPDATE_TU_STATUS_TO_AVL ** ');
DEBUG_LOG_STRING (l_proc_level, 'Update_tu_status_to_avl.Msg2',
' funcmode = RUN ');
DEBUG_LOG_STRING (l_proc_level, 'Update_tu_status_to_avl.Msg3',
' GetItemAttrNumber TU_ID --> '|| l_tu_id);
UPDATE igi_exp_tus tus
SET tus.tu_status = 'AVL'
WHERE tus.tu_id = l_tu_id;
DEBUG_LOG_STRING (l_proc_level, 'Update_tu_status_to_avl.Msg4',
' UPDATE igi_exp_tus tu_status = AVL ');
DEBUG_LOG_STRING (l_proc_level, 'Update_tu_status_to_avl.Msg5',
' funcmode = RUN -- result --> ' || result);
DEBUG_LOG_STRING (l_proc_level, 'Update_tu_status_to_avl.Msg6',
' ** END UPDATE_TU_STATUS_TO_AVL ** ');
DEBUG_LOG_UNEXP_ERROR ('Update_tu_status_to_avl.Unexp1','DEFAULT');
wf_core.context('IGI_EXP_APPROVAL_PKG','update_tu_status_to_avl',itemtype,itemkey,
to_char(actid),funcmode);
END update_tu_status_to_avl;
PROCEDURE update_dus_to_in_a_tu ( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2) IS
l_tu_id igi_exp_dus.tu_id%TYPE;
DEBUG_LOG_STRING (l_proc_level, 'Update_dus_to_in_a_tu.Msg1',
' ** BEGIN UPDATE_DUS_TO_IN_A_TU ** ');
DEBUG_LOG_STRING (l_proc_level, 'Update_dus_to_in_a_tu.Msg2',
' funcmode = RUN ');
DEBUG_LOG_STRING (l_proc_level, 'Update_dus_to_in_a_tu.Msg3',
' GetItemAttrNumber TU_ID --> ' || l_tu_id);
UPDATE igi_exp_dus dus
SET dus.du_status = 'ITU'
WHERE dus.tu_id = l_tu_id;
DEBUG_LOG_STRING (l_proc_level, 'Update_dus_to_in_a_tu.Msg4',
' UPDATE igi_exp_dus du_status = ITU ');
DEBUG_LOG_STRING (l_proc_level, 'Update_dus_to_in_a_tu.Msg5',
' funcmode = RUN -- result --> ' || result);
DEBUG_LOG_STRING (l_proc_level, 'Update_dus_to_in_a_tu.Msg6',
' ** END UPDATE_DUS_TO_IN_A_TU ** ');
DEBUG_LOG_UNEXP_ERROR ('Update_tu_status_to_avl.Unexp1','DEFAULT');
wf_core.context('IGI_EXP_APPROVAL_PKG','update_dus_to_in_a_tu',itemtype,itemkey,
to_char(actid),funcmode);
END update_dus_to_in_a_tu;
SELECT fu.user_name
FROM hr_all_positions_f hap,
per_all_assignments_f paa,
per_employees_current_x pec,
fnd_user fu
WHERE fu.start_date <= SYSDATE
AND NVL(fu.end_date,SYSDATE) >= SYSDATE
AND fu.employee_id IS NOT NULL
AND fu.employee_id = pec.employee_id
AND NVL(pec.inactive_date,SYSDATE) >= SYSDATE
AND pec.business_group_id = paa.business_group_id
AND pec.assignment_id = paa.assignment_id
AND pec.organization_id = paa.organization_id
AND pec.business_group_id = p_business_group_id
AND pec.organization_id = p_organization_id
AND paa.assignment_type = 'E'
AND paa.effective_start_date <= SYSDATE
AND paa.effective_end_date >= SYSDATE
AND paa.business_group_id = hap.business_group_id
AND paa.position_id IS NOT NULL
AND paa.position_id = hap.position_id
AND paa.organization_id = hap.organization_id
AND hap.date_effective <= SYSDATE
AND NVL(hap.date_end, SYSDATE) >= SYSDATE
AND NVL(UPPER(hap.status), 'VALID') NOT IN ('INVALID')
AND hap.position_id = p_position_id;
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
UPDATE igi_exp_tus
SET next_approver_user_id = l_user_id
WHERE tu_id = l_tu_id;
' UPDATE igi_exp_tus set next_approver_user_id --> '|| l_user_id);
UPDATE igi_exp_tus
SET next_approver_user_id = null
WHERE tu_id = l_tu_id;
' Update igi_exp_tus for tu_id ' ||l_tu_id );
SELECT user_id
INTO l_auth_id
FROM fnd_user
WHERE user_name = l_current_auth_role;
UPDATE igi_exp_tus
SET next_approver_user_id = l_auth_id
WHERE tu_id = l_tu_id;
' UPDATE igi_exp_tus with ' || l_auth_id || ' for ' || l_tu_id);
SELECT return
FROM igi_exp_pos_actions
WHERE position_id = p_position_id;
SELECT subordinate_position_id
FROM per_pos_structure_elements
WHERE pos_structure_version_id = p_pos_structure_version_id
AND business_group_id = p_business_group_id
AND parent_position_id = p_position_id;
SELECT name
INTO l_subord_position_name
FROM hr_all_positions_f
WHERE position_id = l_subord_position_id
AND business_group_id = l_business_group_id
AND organization_id = l_organization_id;
UPDATE igi_exp_dus dus
SET dus.du_status = 'APP'
WHERE dus.du_status = 'HLD'
AND dus.tu_id = l_tu_id;
' UPDATE igi_exp_dus dus HLD to APP ' || SQL%ROWCOUNT);
UPDATE igi_exp_dus dus
SET dus.du_status = 'APP'
WHERE dus.du_status = 'REJ'
AND dus.tu_id = l_tu_id
AND EXISTS (SELECT 'AR'
FROM igi_exp_ar_trans ar
WHERE ar.du_id = dus.du_id
UNION
SELECT 'AP'
FROM igi_exp_ap_trans ap
WHERE ap.du_id = dus.du_id);
' UPDATE igi_exp_dus status - REJ to APP' || SQL%ROWCOUNT);
UPDATE igi_exp_dus dus
SET dus.du_status = 'APP'
WHERE dus.du_status = 'ATR'
AND dus.tu_id = l_tu_id;
' UPDATE igi_exp_dus status -- ATR to APP ' || SQL%ROWCOUNT);
SELECT count(*)
INTO l_no_of_dus
FROM igi_exp_dus
WHERE tu_id = l_tu_id
AND du_status NOT IN ('APP','REJ','HLD');
UPDATE igi_exp_dus
SET du_status = 'ATR'
WHERE du_status = 'APP'
AND tu_id = l_tu_id;
' UPDATE igi_exp_dus APP - ATR');
SELECT du_order_number
,du_legal_number
FROM igi_exp_dus
WHERE tu_id = p_tu_id
AND du_status = 'HLD';
SELECT count(*)
INTO l_hold_count
FROM igi_exp_dus
WHERE du_status = 'HLD'
AND tu_id = l_tu_id;
SELECT du_id
,du_by_user_id
FROM igi_exp_dus
WHERE tu_id = p_tu_id
AND du_status = 'HLD'
AND rownum = 1;
SELECT du_order_number
,du_legal_number
FROM igi_exp_dus
WHERE tu_id = p_tu_id
AND du_status = 'HLD'
AND du_by_user_id = p_du_preparer_id;
SELECT user_name
INTO l_du_prep_name
FROM fnd_user
WHERE user_id = l_du_prep_id;
SELECT sum(du_amount)
INTO l_du_amount
FROM igi_exp_dus
WHERE tu_id = l_tu_id
AND du_by_user_id = l_du_prep_id
AND du_status = 'HLD';
UPDATE igi_exp_tus
SET tu_amount = tu_amount - l_du_amount
WHERE tu_id = l_tu_id;
' UPDATE igi_exp_tus tu-amount ' || SQL%ROWCOUNT);
UPDATE igi_exp_dus
SET tu_id = null
WHERE tu_id = l_tu_id
AND du_by_user_id = l_du_prep_id
AND du_status = 'HLD';
' UPDATE igi_exp_dus tu_id to NULL --> ' || SQL%ROWCOUNT);
SELECT du_order_number
,du_legal_number
FROM igi_exp_dus du
WHERE tu_id = p_tu_id
AND du_status = 'REJ';
SELECT count(*)
INTO l_rej_count
FROM igi_exp_dus du
WHERE tu_id = l_tu_id
AND du_status = 'REJ';
SELECT du_id
,du_by_user_id
FROM igi_exp_dus du
WHERE tu_id = p_tu_id
AND du_status = 'REJ'
AND EXISTS (SELECT 'AR'
FROM igi_exp_ar_trans ar
WHERE ar.du_id = du.du_id
UNION
SELECT 'AP'
FROM igi_exp_ap_trans ap
WHERE ap.du_id = du.du_id)
AND rownum = 1;
SELECT du_order_number
,du_legal_number
FROM igi_exp_dus du
WHERE tu_id = p_tu_id
AND du_status = 'REJ'
AND du_by_user_id = p_du_preparer_id;
SELECT user_name
INTO l_du_prep_name
FROM fnd_user
WHERE user_id = l_du_prep_id;
SELECT du_id
FROM igi_exp_dus du
WHERE tu_id = p_tu_id
AND du_status = 'REJ'
AND du_by_user_id = p_du_preparer_id
AND EXISTS (SELECT 'AR'
FROM igi_exp_ar_trans ar
WHERE ar.du_id = du.du_id
UNION
SELECT 'AP'
FROM igi_exp_ap_trans ap
WHERE ap.du_id = du.du_id);
DELETE FROM igi_exp_ar_trans
WHERE du_id = I.du_id;
'DELETE FROM igi_exp_ar_trans --> ' || SQL%ROWCOUNT);
DELETE FROM igi_exp_ap_trans
WHERE du_id = I.du_id;
' DELETE FROM igi_exp_ap_trans --> ' || SQL%ROWCOUNT);
SELECT sum(du_amount)
INTO l_du_amount
FROM igi_exp_dus
WHERE tu_id = l_tu_id
AND du_by_user_id = l_du_prep_id
AND du_status = 'REJ';
UPDATE igi_exp_tus
SET tu_amount = tu_amount - l_du_amount
WHERE tu_id = l_tu_id;
' UPDATE igi_exp_tus --> ' || SQL%ROWCOUNT);
UPDATE igi_exp_dus
SET du_amount = 0
WHERE tu_id = l_tu_id
AND du_by_user_id = l_du_prep_id
AND du_status = 'REJ';
' UPDATE igi_exp_dus du_amount = 0 --> ' || SQL%ROWCOUNT);
SELECT tu_type_header_id
,tu_fiscal_year
INTO l_tu_type_header_id
,l_tu_fiscal_year
FROM igi_exp_tus
WHERE tu_id = l_tu_id;
UPDATE igi_exp_tus
SET tu_legal_number = l_tu_legal_number
WHERE tu_id = l_tu_id;
'UPDATE igi_exp_tus - ' || SQL%ROWCOUNT);
SELECT du_id
,du_type_header_id
,du_fiscal_year
,du_order_number
FROM igi_exp_dus
WHERE tu_id = p_tu_id
AND du_status = 'ATR';
UPDATE igi_exp_dus
SET du_legal_number = l_du_legal_number
WHERE du_id = l_du_id;
' UPDATE igi_exp_dus --> ' || SQL%ROWCOUNT);
SELECT tu_status
FROM igi_exp_tus
WHERE tu_id = p_tu_id;
UPDATE igi_exp_tus
SET tu_status = 'ATR'
WHERE tu_id = l_tu_id;
' UPDATE igi_exp_tus --> ' || SQL%ROWCOUNT);
SELECT count(*)
INTO l_atr_du_count
FROM igi_exp_dus
WHERE tu_id = l_tu_id
AND du_status = 'ATR';
SELECT parent_position_id
FROM per_pos_structure_elements
WHERE pos_structure_version_id = p_pos_structure_version_id
AND business_group_id = p_business_group_id
AND subordinate_position_id = p_position_id;
SELECT name
INTO l_parent_pos_name
FROM hr_all_positions_f
WHERE position_id = l_parent_pos_id;
SELECT du.du_id
,fnd.user_name
INTO l_du_id
,l_du_prep_name
FROM igi_exp_dus du
,fnd_user fnd
WHERE du.tu_id = l_tu_id
AND du.du_status = 'ATR'
AND du.du_by_user_id = fnd.user_id
AND rownum = 1;
SELECT du_type.application_id
INTO l_app_id
FROM igi_exp_du_type_headers_all du_type
,igi_exp_dus du
WHERE du.du_id = l_du_id
AND du.du_type_header_id = du_type.du_type_header_id;
SELECT du_order_number, du_legal_number
INTO l_order_number, l_legal_number
FROM igi_exp_dus
WHERE du_id = l_du_id;
SELECT invoice_num
INTO l_failed_trx
FROM ap_invoices
WHERE invoice_id = l_trx_id;
SELECT trx_number
INTO l_failed_trx
FROM ra_customer_trx
WHERE customer_trx_id = l_trx_id;
UPDATE igi_exp_dus
SET du_status = 'COM'
WHERE du_id = l_du_id;
' UPDATE igi_exp_dus status - COM --> ' || SQL%ROWCOUNT);
UPDATE igi_exp_dus
SET du_status = 'HLD'
WHERE du_id = l_du_id;
' UPDATE igi_exp_dus du_status = HLD --> ' || SQL%ROWCOUNT);
SELECT count(*)
INTO l_incom_count
FROM igi_exp_dus
WHERE tu_id = l_tu_id
AND du_status = 'ATR';
SELECT du_order_number
,du_legal_number
FROM igi_exp_dus
WHERE tu_id = p_tu_id
AND du_status = 'HLD';
SELECT count(*)
INTO l_failed_du_count
FROM igi_exp_dus
WHERE tu_id = l_tu_id
AND du_status = 'HLD';
SELECT sum(du_amount)
INTO l_du_amount
FROM igi_exp_dus
WHERE tu_id = l_tu_id
AND du_status = 'HLD';
UPDATE igi_exp_tus
SET tu_amount = tu_amount - l_du_amount
WHERE tu_id = l_tu_id;
' UPDATE igi_exp_tus -> ' || SQL%ROWCOUNT);
UPDATE igi_exp_dus
SET tu_id = null
WHERE tu_id = l_tu_id
AND du_status = 'HLD';
' UPDATE igi_exp_dus status = HLD --> ' || SQL%ROWCOUNT);
SELECT count(*)
INTO l_com_du_count
FROM igi_exp_dus
WHERE tu_id = l_tu_id
AND du_status = 'COM';
SELECT distinct du_by_user_id
FROM igi_exp_dus
WHERE tu_id = p_tu_id
AND du_status = 'COM';
SELECT du_order_number
,du_legal_number
FROM igi_exp_dus
WHERE tu_id = p_tu_id
AND du_status = 'COM'
AND du_by_user_id = p_du_by_user_id;
SELECT user_name
INTO l_next_prep_name
FROM fnd_user
WHERE user_id = l_next_prep;
UPDATE igi_exp_tus
SET tu_status = 'COM'
WHERE tu_id = l_tu_id;
' UPDATE igi_exp_tus --> ' || SQL%ROWCOUNT);
SELECT tu_legal_number
FROM igi_exp_tus
WHERE tu_id = c_tu_id;
/*UPDATE igi_exp_dus
SET tu_id = null
WHERE tu_id = l_tu_id
AND du_by_user_id = l_du_prep_id
AND du_status = 'REJ';*/
' UPDATE igi_exp_dus du_status = REJ --> ' || SQL%ROWCOUNT);
SELECT du_id
,du_by_user_id
FROM igi_exp_dus du
WHERE tu_id = p_tu_id
AND du_status = 'REJ'
AND EXISTS (SELECT 'AR'
FROM igi_exp_ar_trans ar
WHERE ar.du_id = du.du_id
UNION
SELECT 'AP'
FROM igi_exp_ap_trans ap
WHERE ap.du_id = du.du_id)
AND rownum = 1;
SELECT du_order_number
,du_legal_number
FROM igi_exp_dus du
WHERE tu_id = p_tu_id
AND du_status = 'REJ'
AND du_by_user_id = p_du_preparer_id;
SELECT user_name
INTO l_du_prep_name
FROM fnd_user
WHERE user_id = l_du_prep_id;