[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(SUM(quantity * unit_price), 0)
FROM po_requisition_lines_all
WHERE requisition_header_id = p_doc_id
AND NVL(cancel_flag,'N') = 'N'
AND NVL(modified_by_agent_flag, 'N') = 'N';
SELECT nvl(sum(nonrecoverable_tax), 0)
FROM po_requisition_lines_all rl,
po_req_distributions_all rd
WHERE rl.requisition_header_id = p_doc_id
AND rd.requisition_line_id = rl.requisition_line_id
AND NVL(rl.cancel_flag,'N') = 'N'
AND NVL(rl.modified_by_agent_flag, 'N') = 'N';
SELECT nvl(sum(nonrecoverable_tax), 0)
INTO l_tax_amt
FROM po_requisition_lines rl,
po_req_distributions_all rd --
WHERE rl.requisition_header_id = l_document_id
AND rd.requisition_line_id = rl.requisition_line_id;
SELECT count(*) into l_attr_exist
FROM WF_ITEM_ATTRIBUTE_VALUES
WHERE ITEM_TYPE = l_item_type
AND ITEM_KEY = l_item_key
AND NAME = 'ADVISORY_WARNING';
SELECT nvl(sum(nonrecoverable_tax), 0)
INTO l_tax_amt
FROM po_requisition_lines rl,
po_req_distributions_all rd --
WHERE rl.requisition_header_id = l_document_id
AND rd.requisition_line_id = rl.requisition_line_id;
SELECT nvl(sum(nonrecoverable_tax), 0)
INTO l_tax_amt
FROM po_requisition_lines rl,
po_req_distributions_all rd --
WHERE rl.requisition_header_id = l_document_id
AND rd.requisition_line_id = rl.requisition_line_id;
SELECT nvl(sum(nonrecoverable_tax), 0)
INTO l_tax_amt
FROM po_requisition_lines rl,
po_req_distributions_all rd --
WHERE rl.requisition_header_id = l_document_id
AND rd.requisition_line_id = rl.requisition_line_id;
SELECT rql.requisition_line_id,
rql.line_num,
msi.concatenated_segments,
rql.item_revision,
rql.item_description,
-- rql.unit_meas_lookup_code, -- bug 2401933.remove
nvl(muom.unit_of_measure_tl, rql.unit_meas_lookup_code), -- bug 2401933.add
rql.quantity,
rql.unit_price,
rql.quantity * rql.unit_price,
rql.need_by_date,
hrt.location_code,
per.full_name,
rql.suggested_vendor_name,
rql.suggested_vendor_location,
rql.currency_code,
rql.currency_unit_price
FROM po_requisition_lines rql,
mtl_system_items_kfv msi,
hr_locations_all hrt,
mtl_units_of_measure muom, -- bug 2401933.add
per_all_people_f per -- Bug 3404451
WHERE rql.requisition_header_id = v_document_id
AND NVL(rql.cancel_flag,'N') = 'N'
AND NVL(rql.modified_by_agent_flag, 'N') = 'N'
AND hrt.location_id (+) = rql.deliver_to_location_id
AND rql.item_id = msi.inventory_item_id(+)
AND rql.destination_organization_id = msi.organization_id(+)
AND rql.to_person_id = per.person_id(+)
AND per.effective_start_date(+) <= trunc(sysdate)
AND per.effective_end_date(+) >= trunc(sysdate)
AND muom.unit_of_measure = rql.unit_meas_lookup_code -- bug 2401933.add
ORDER BY rql.line_num;
l_req_updates_url VARCHAR2(2000) := '';
SELECT rql.requisition_line_id,
rql.line_num,
msi.concatenated_segments,
rql.item_revision,
rql.item_description,
-- rql.unit_meas_lookup_code, -- bug 2401933.remove
nvl(muom.unit_of_measure_tl, rql.unit_meas_lookup_code), -- bug 2401933.add
rql.quantity,
rql.unit_price,
rql.quantity * rql.unit_price,
rql.need_by_date,
hrt.location_code,
per.full_name,
decode(rql.source_type_code,'VENDOR', rql.suggested_vendor_name, org.organization_code ||' - '|| org.organization_name),
decode(rql.source_type_code, 'VENDOR',rql.suggested_vendor_location,''),
rql.currency_code,
rql.currency_unit_price
FROM po_requisition_lines rql,
mtl_system_items_kfv msi,
hr_locations_all hrt,
per_all_people_f per, -- Bug 3404451
mtl_units_of_measure muom, -- bug 2401933.add
org_organization_definitions org
WHERE rql.requisition_header_id = v_document_id
AND NVL(rql.cancel_flag,'N') = 'N'
AND NVL(rql.modified_by_agent_flag, 'N') = 'N'
AND hrt.location_id (+) = rql.deliver_to_location_id
AND rql.item_id = msi.inventory_item_id(+)
AND rql.destination_organization_id = msi.organization_id(+)
AND rql.to_person_id = per.person_id(+)
AND per.effective_start_date(+) <= trunc(sysdate)
AND per.effective_end_date(+) >= trunc(sysdate)
AND rql.source_organization_id = org.organization_id (+)
AND muom.unit_of_measure(+) = rql.unit_meas_lookup_code
ORDER BY rql.line_num;
SELECT CODE_COMBINATION_ID
FROM PO_REQ_DISTRIBUTIONS_ALL
WHERE REQUISITION_LINE_ID = req_line_id;
select fs.segment_num, gls.chart_of_accounts_id
into l_segment_num, l_account_id
from FND_ID_FLEX_SEGMENTS fs,
fnd_segment_attribute_values fsav,
financials_system_parameters fsp,
gl_sets_of_books gls
where fsp.set_of_books_id = gls.set_of_books_id and
fsav.id_flex_num = gls.chart_of_accounts_id and
fsav.id_flex_code = 'GL#' and
fsav.application_id = 101 and
fsav.segment_attribute_type = 'FA_COST_CTR' and
fsav.id_flex_num = fs.id_flex_num and
fsav.id_flex_code = fs.id_flex_code and
fsav.application_id = fs.application_id and
fsav.application_column_name = fs.application_column_name and
fsav.attribute_value='Y';
** Desc: Modified the select to only count lines that are not cancelled
*/
select count(1)
into l_num_lines
from po_requisition_lines
where requisition_header_id = l_document_id
AND NVL(cancel_flag,'N') = 'N'
AND NVL(modified_by_agent_flag, 'N') = 'N';
SELECT poh.SEQUENCE_NUM,
per.FULL_NAME,
polc.DISPLAYED_FIELD,
poh.ACTION_DATE,
poh.NOTE,
poh.OBJECT_REVISION_NUM,
poh.employee_id, /* bug 2788683 */
poh.created_by, /* bug 2788683 */
poh.action_code /* bug 3090563 */
from po_action_history poh,
per_all_people_f per, -- Bug 3404451
po_lookup_codes polc
where OBJECT_TYPE_CODE = v_object_type
and nvl(poh.action_code, 'PENDING') = polc.lookup_code
and POLC.LOOKUP_TYPE = 'APPR_HIST_ACTIONS'
and per.person_id(+) = poh.employee_id /* bug 2788683 */
and trunc(sysdate) between per.effective_start_date(+)
and per.effective_end_date(+)
and OBJECT_ID = v_document_id
order by 1 asc; /* bug 3090563 reverse display order */
SELECT count(*)
from po_action_history poh,
per_all_people_f per,
po_lookup_codes polc
where OBJECT_TYPE_CODE = v_object_type
and nvl(poh.action_code, 'PENDING') = polc.lookup_code
and POLC.LOOKUP_TYPE = 'APPR_HIST_ACTIONS'
and per.person_id(+) = poh.employee_id
and trunc(sysdate) between per.effective_start_date(+)
and per.effective_end_date(+)
and OBJECT_ID = v_document_id;
/* if action history is updated by vendor
* show vendor true name(vendor name)
* else action history is updated by buyer
* show buyer's true name
*/
IF l_history.employee_id IS NULL THEN
SELECT fu.user_name, hp.party_name
INTO l_user_name, l_party_name
FROM fnd_user fu,
hz_parties hp
WHERE hp.party_id = fu.customer_id
AND fu.user_id = l_history.created_by;
/* if action history is updated by vendor
* show vendor true name(vendor name)
* else action history is updated by buyer
* show buyer's true name
*/
IF l_history.employee_id IS NULL THEN
SELECT fu.user_name, hp.party_name
INTO l_user_name, l_party_name
FROM fnd_user fu,
hz_parties hp
WHERE hp.party_id = fu.customer_id
AND fu.user_id = l_history.created_by;
SELECT pal.SEQUENCE_NUM,per.FULL_NAME,null,null,null,null,
NULL, NULL, NULL /* bug 2788683*/
FROM per_all_people_f per, -- Bug 3404451
po_approval_list_lines pal,
po_approval_list_headers pah
WHERE pah.document_id = v_document_id
and pah.document_type = v_object_type
and pah.latest_revision = 'Y'
and pal.APPROVAL_LIST_HEADER_ID = pah.APPROVAL_LIST_HEADER_ID
and pal.STATUS IS NULL
and per.PERSON_ID = pal.APPROVER_ID
and trunc(sysdate) between per.EFFECTIVE_START_DATE
and per.EFFECTIVE_END_DATE
ORDER BY 1 desc;
SELECT count(*)
FROM per_all_people_f per,
po_approval_list_lines pal,
po_approval_list_headers pah
WHERE pah.document_id = v_document_id
and pah.document_type = v_object_type
and pah.latest_revision = 'Y'
and pal.APPROVAL_LIST_HEADER_ID = pah.APPROVAL_LIST_HEADER_ID
and pal.STATUS IS NULL
and per.PERSON_ID = pal.APPROVER_ID
and trunc(sysdate) between per.EFFECTIVE_START_DATE
and per.EFFECTIVE_END_DATE;
select full_name
into fullName from per_all_people_f
where person_id = approverList(i).person_id
and trunc(sysdate) between effective_start_date and effective_end_date;
SELECT poh.SEQUENCE_NUM,
per.FULL_NAME,
polc.DISPLAYED_FIELD,
poh.ACTION_DATE,
poh.NOTE,
poh.OBJECT_REVISION_NUM,
poh.employee_id, /* bug 2788683 */
poh.created_by /* bug 2788683 */
from po_action_history poh,
per_all_people_f per, -- Bug 3404451
po_lookup_codes polc
where OBJECT_TYPE_CODE = v_object_type
and poh.action_code = polc.lookup_code
and POLC.LOOKUP_TYPE IN ('APPROVER ACTIONS','CONTROL ACTIONS')
and per.person_id(+) = poh.employee_id /* bug 2788683 */
and trunc(sysdate) between per.effective_start_date(+)
and per.effective_end_date(+)
and OBJECT_ID = v_document_id
UNION ALL
SELECT poh.SEQUENCE_NUM,
per.FULL_NAME,
NULL,
poh.ACTION_DATE,
poh.NOTE,
poh.OBJECT_REVISION_NUM,
poh.employee_id, /* bug 2788683 */
poh.created_by /* bug 2788683 */
from po_action_history poh,
per_all_people_f per -- Bug 3404451
where OBJECT_TYPE_CODE = v_object_type
and poh.action_code is null
and per.person_id(+) = poh.employee_id /* bug 2788683 */
and trunc(sysdate) between per.effective_start_date(+)
and per.effective_end_date(+)
and OBJECT_ID = v_document_id
order by 1 desc;
PROCEDURE update_action_history (p_action_code IN VARCHAR2,
p_recipient_id IN NUMBER,
p_note IN VARCHAR2,
p_req_header_id IN NUMBER,
p_current_id IN NUMBER,
p_doc_type IN po_action_history.OBJECT_TYPE_CODE%TYPE)
IS
pragma AUTONOMOUS_TRANSACTION;
SELECT max(sequence_num)
INTO l_sequence_num
FROM PO_ACTION_HISTORY
WHERE object_type_code = p_doc_type
AND object_id = p_req_header_id;
SELECT object_sub_type_code,
object_revision_num, approval_path_id, request_id,
program_application_id, program_date, program_id
INTO l_object_sub_type_code,
l_object_revision_num, l_approval_path_id, l_request_id,
l_program_application_id, l_program_date, l_program_id
FROM PO_ACTION_HISTORY
WHERE object_type_code = p_doc_type
AND object_id = p_req_header_id
AND employee_id = p_current_id
AND action_code IS NULL
AND rownum=1;
SELECT object_sub_type_code,
object_revision_num, approval_path_id, request_id,
program_application_id, program_date, program_id
INTO l_object_sub_type_code,
l_object_revision_num, l_approval_path_id, l_request_id,
l_program_application_id, l_program_date, l_program_id
FROM PO_ACTION_HISTORY
WHERE object_type_code = p_doc_type
AND object_id = p_req_header_id
AND employee_id = p_current_id
AND rownum=1;
SELECT distinct approval_group_id
INTO l_approval_group_id
FROM PO_ACTION_HISTORY
WHERE object_type_code = p_doc_type
AND object_id = p_req_header_id
AND employee_id = p_recipient_id;
UPDATE PO_ACTION_HISTORY
SET last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id ,
action_date = sysdate,
action_code = p_action_code,
note = p_note,
offline_code = decode(offline_code,
'PRINTED', 'PRINTED', NULL)
WHERE employee_id = p_current_id
AND object_id = p_req_header_id
AND object_type_code = p_doc_type
AND action_code IS NULL
AND rownum=1;
po_forward_sv1.insert_action_history (
p_req_header_id,
p_doc_type,
l_object_sub_type_code,
l_sequence_num + 1,
NULL,
NULL,
p_recipient_id,
l_approval_path_id,
NULL,
l_object_revision_num,
NULL, /* offline_code */
l_request_id,
l_program_application_id,
l_program_id,
l_program_date,
fnd_global.user_id,
fnd_global.login_id,
l_approval_group_id);
wf_core.context('PO_WF_REQ_NOTIFICATION','update_action_history',l_progress,sqlerrm);
SELECT flv.MEANING
FROM po_requisition_headers_all prh, FND_LOOKUP_VALUES FLV
WHERE prh.requisition_header_id = p_doc_id
AND prh.clm_mipr_type = flv.LOOKUP_CODE
AND NVL(prh.cancel_flag,'N') = 'N'
AND FLV.LOOKUP_TYPE='MIPR_TYPE'
AND FLV.VIEW_APPLICATION_ID = 201;
SELECT original_recipient, Decode(MORE_INFO_ROLE,
NULL, RECIPIENT_ROLE,
MORE_INFO_ROLE)
INTO l_original_recipient, l_current_recipient_role
FROM wf_notifications
WHERE notification_id = WF_ENGINE.context_nid
AND ( MORE_INFO_ROLE IS NOT NULL OR
RECIPIENT_ROLE <> ORIGINAL_RECIPIENT );
update_action_history(p_action_code => l_action,
p_recipient_id => l_new_recipient_id,
p_note => WF_ENGINE.CONTEXT_USER_COMMENT,
p_req_header_id => l_req_header_id,
p_current_id => l_current_recipient_id,
p_doc_type=> l_doc_type);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
SELECT fu.USER_ID
INTO l_responder_id
FROM fnd_user fu,
wf_notifications wfn
WHERE wfn.notification_id = l_nid
AND wfn.original_recipient = fu.user_name;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
select PARENT_ITEM_TYPE, PARENT_ITEM_KEY
into p_itemtype,p_itemkey
from wf_items
where item_type = itemtype and item_key =itemkey;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
select DISPLAYED_FIELD
from po_lookup_codes
where lookup_type='REQUISITION TYPE'
and lookup_code = p_doc_subtype;
select DISPLAYED_FIELD
from po_lookup_codes
where lookup_type='DOCUMENT TYPE'
and lookup_code = p_doc_type;
select type_name
from po_document_types_tl tl, FND_LANGUAGES fl
where fl.nls_language = p_language
and tl.LANGUAGE = fl.language_code
and tl.document_type_code = 'REQUISITION'
and tl.document_subtype = p_doc_subtype;
select MEANING
from FND_LOOKUP_VALUES flv, FND_LANGUAGES fl
where
fl.nls_language = p_language
and flv.LANGUAGE = fl.language_code
and flv.lookup_type='REQUISITION TYPE'
and flv.lookup_code = p_doc_subtype
and VIEW_APPLICATION_ID = 201
and SECURITY_GROUP_ID = fnd_global.lookup_security_group('REQUISITION TYPE',201);
SELECT flv.MEANING
FROM po_requisition_headers_all prh, FND_LOOKUP_VALUES FLV, FND_LANGUAGES FL
WHERE prh.requisition_header_id = p_doc_id
AND prh.clm_mipr_type = flv.LOOKUP_CODE
AND NVL(cancel_flag,'N') = 'N'
AND FL.NLS_LANGUAGE = p_language
AND FLV.LANGUAGE = FL.LANGUAGE_CODE
AND FLV.LOOKUP_TYPE='MIPR_TYPE'
AND VIEW_APPLICATION_ID = 201;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
select currency_code into l_currency_code from
(select currency_code from
(SELECT currency_code
FROM po_requisition_lines
WHERE requisition_header_id = p_document_id
AND NVL(cancel_flag,'N') = 'N'
AND NVL(modified_by_agent_flag, 'N') = 'N'
order by line_num) a
where rownum <= l_max_lines ) b
where b.currency_code <> p_func_currency_code;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'PO_WF_REQ_NOTIFICATION.get_item_info nid='||nid);