The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_update_allowed VARCHAR2(1);
SELECT org_id
INTO l_doc_org_id
FROM po_headers_all poh
WHERE poh.po_header_id = p_action_ctl_rec.document_id;
SELECT org_id
INTO l_doc_org_id
FROM po_releases_all por
WHERE por.po_release_id = p_action_ctl_rec.document_id;
SELECT org_id
INTO l_doc_org_id
FROM po_requisition_headers_all porh
WHERE porh.requisition_header_id = p_action_ctl_rec.document_id;
PO_DRAFTS_PVT.update_permission_check
( p_calling_module => PO_DRAFTS_PVT.g_call_mod_API,
p_po_header_id => p_action_ctl_rec.document_id,
p_role => PO_GLOBAL.g_role_BUYER,
p_skip_cat_upload_chk => FND_API.G_TRUE,
x_update_allowed => l_update_allowed,
x_locking_applicable => l_locking_applicable,
x_unlock_required => l_unlock_required,
x_message => l_error_message,
x_message_text => l_error_message_text --Bug#4651122
);
IF (l_update_allowed = FND_API.G_FALSE) THEN
d_log_msg := 'unable to perform control action to doc: ' ||
l_error_message_text;
ELSIF (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_UPDATE_CLOSE_AUTO)
THEN
d_progress := 60.2;
PO_DELREC_PVT.create_update_delrec(
p_api_version => 1.0
, x_return_status => l_ret_sts
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_action => p_action_ctl_rec.action
, p_doc_type => p_action_ctl_rec.document_type
, p_doc_subtype => p_action_ctl_rec.document_subtype
, p_doc_id => p_action_ctl_rec.document_id
, p_line_id => p_action_ctl_rec.line_id
, p_line_location_id => p_action_ctl_rec.shipment_id
);
d_log_msg := 'create_update_delrec not successful';
SELECT /*+ ordered use_nl (poeh a p past b) */ poeh.superior_id
FROM po_employee_hierarchies_all poeh,
per_all_people_f p,
per_all_assignments_f a,
per_assignment_status_types past,
per_periods_of_service b
WHERE a.person_id = p.person_id
AND poeh.business_group_id in (select fsp.business_group_id
from financials_system_parameters fsp)
AND a.period_of_service_id = b.period_of_service_id
AND a.primary_flag = 'Y'
AND Trunc(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
AND Trunc(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND (NVL(CURRENT_EMPLOYEE_FLAG,'N') = 'Y'
OR NVL(CURRENT_NPW_FLAG,'N') = 'Y')
AND a.assignment_type in ('E',decode(
nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N'),'Y','C','E'))
AND a.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND poeh.position_structure_id = p_path_id
AND poeh.employee_id = p_emp_id
AND p.person_id = poeh.superior_id
AND poeh.superior_level > 0
AND 'TRUE' = Decode(hr_security.view_all, 'Y', 'TRUE',
hr_security.Show_person(p.person_id,
p.current_applicant_flag,
p.current_employee_flag,
p.current_npw_flag,
p.employee_number,
p.applicant_number,
p.npw_number))
AND 'TRUE' = Decode(hr_security.view_all, 'Y', 'TRUE',
hr_security.Show_record('PER_ALL_ASSIGNMENTS_F',
a.assignment_id,
a.person_id,
a.assignment_type))
ORDER BY poeh.superior_level, p.full_name;
SELECT pera.supervisor_id
FROM per_assignments_f pera
WHERE pera.business_group_id = p_bus_group_id
AND trunc(SYSDATE) BETWEEN pera.effective_start_date
AND pera.effective_end_date
START WITH pera.person_id = p_emp_id
AND pera.business_group_id = p_bus_group_id
AND trunc(SYSDATE) BETWEEN pera.effective_start_date
AND pera.effective_end_date
CONNECT BY pera.person_id = PRIOR pera.supervisor_id
AND pera.business_group_id = p_bus_group_id
AND trunc(SYSDATE) BETWEEN pera.effective_start_date
AND pera.effective_end_date;
SELECT pera.supervisor_id
FROM per_assignments_f pera
WHERE trunc(SYSDATE) BETWEEN pera.effective_start_date
AND pera.effective_end_date
START WITH pera.person_id = p_emp_id
AND trunc(SYSDATE) BETWEEN pera.effective_start_date
AND pera.effective_end_date
CONNECT BY pera.person_id = PRIOR pera.supervisor_id
AND trunc(SYSDATE) BETWEEN pera.effective_start_date
AND pera.effective_end_date;
SELECT /*+ ordered use_nl (poeh a p past b) */ poeh.superior_id
FROM po_employee_hierarchies_all poeh,
per_all_people_f p,
per_all_assignments_f a,
per_assignment_status_types past,
per_periods_of_service b
WHERE a.person_id = p.person_id
AND poeh.business_group_id in (select fsp.business_group_id
from financials_system_parameters fsp)
AND a.period_of_service_id = b.period_of_service_id
AND a.primary_flag = 'Y'
AND Trunc(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
AND Trunc(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
AND (NVL(CURRENT_EMPLOYEE_FLAG,'N') = 'Y'
OR NVL(CURRENT_NPW_FLAG,'N') = 'Y')
AND a.assignment_type in ('E',decode(
nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N'),'Y','C','E'))
AND a.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND poeh.position_structure_id = p_path_id
AND poeh.employee_id = p_emp_id
AND p.person_id = poeh.superior_id
AND poeh.superior_level = 1
AND 'TRUE' = Decode(hr_security.view_all, 'Y', 'TRUE',
hr_security.Show_person(p.person_id,
p.current_applicant_flag,
p.current_employee_flag,
p.current_npw_flag,
p.employee_number,
p.applicant_number,
p.npw_number))
AND 'TRUE' = Decode(hr_security.view_all, 'Y', 'TRUE',
hr_security.Show_record('PER_ALL_ASSIGNMENTS_F',
a.assignment_id,
a.person_id,
a.assignment_type))
ORDER BY p.full_name;
SELECT podt.forwarding_mode_code
INTO l_forwarding_mode
FROM po_document_types podt
WHERE podt.document_type_code = p_document_type
AND podt.document_subtype = p_document_subtype;
SELECT NVL(fsp.use_positions_flag, 'N'), fsp.business_group_id
INTO l_using_positions, l_bus_group_id
FROM financials_system_parameters fsp;
SELECT hre.supervisor_id
INTO l_fwd_to_id
FROM per_workforce_current_x hre --R12 CWK Enhancement
WHERE hre.person_id = p_employee_id;
PROCEDURE auto_update_close_state(
p_document_id IN NUMBER
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
, p_line_id IN NUMBER
, p_shipment_id IN NUMBER
, p_calling_mode IN VARCHAR2 DEFAULT 'PO'
, p_called_from_conc IN BOOLEAN DEFAULT FALSE
, x_return_status OUT NOCOPY VARCHAR2
, x_exception_msg OUT NOCOPY VARCHAR2
, x_return_code OUT NOCOPY VARCHAR2
)
IS
l_da_call_rec DOC_ACTION_CALL_REC_TYPE;
l_da_call_rec.action := g_doc_action_UPDATE_CLOSE_AUTO;
END auto_update_close_state;
PROCEDURE update_secondary_qty_cancelled (
p_join_column IN VARCHAR2
, p_entity_id IN NUMBER
)
IS
CURSOR cur_ship_lines
IS
SELECT pol.item_id, poll.ship_to_organization_id, poll.po_header_id, poll.po_line_id,
poll.line_location_id, poll.po_release_id, poll.quantity_cancelled,
pol.unit_meas_lookup_code, poll.secondary_unit_of_measure
FROM po_line_locations poll, po_lines pol;
FOR 'select '
|| l_ship_column_list
|| ' from '
|| l_ship_table_list
|| ' where '
|| l_ship_where_clause;
UPDATE po_line_locations
SET secondary_quantity_cancelled = l_converted_qty
WHERE line_location_id = l_ship_rec.line_location_id;
END update_secondary_qty_cancelled;