[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* ******************************************SELECT pap.full_name, pap.email_address, ph.phone_number, wn.begin_date
FROM
wf_notifications wn,
wf_user_roles wlur,
fnd_user fnd,
po_requisition_headers_all prh,
per_phones ph,
per_all_people_f pap
WHERE
prh.requisition_header_id = 140331 AND
ph.parent_table(+) = 'PER_ALL_PEOPLE_F' AND
ph.parent_id (+) = fnd.employee_id AND
ph.phone_type(+) = 'W1' AND
wlur.user_name = fnd.user_name AND
pap.person_id = fnd.employee_id AND
fnd.employee_id = 57 AND
wn.recipient_role = wlur.role_name AND
wn.status = 'OPEN' AND
wn.message_type = prh.wf_item_type AND
wn.message_name IN ('PO_REQ_APPROVE',
'PO_REQ_REMINDER1',
'PO_REQ_APPROVE_WEB_MSG',
'PO_REQ_REMINDER2',
'PO_REQ_REMINDER1_WEB',
'PO_REQ_REMINDER2_WEB',
'PO_REQ_APPROVE_JRAD',
'PO_REQ_APPROVE_SIMPLE',
'PO_REQ_APPROVE_SIMPLE_JRAD',
'PO_REQ_REMINDER1_JRAD',
'PO_REQ_REMINDER2_JRAD')
AND effective_start_date < sysdate and effective_end_date > sysdate;*******************************/
select count(distinct nvl(to_person_id,0))
into no_of_values
from po_requisition_lines_all
where requisition_header_id = req_header_id;
select full_name
into value
from
per_all_people_f hre,
po_requisition_lines_all prl
where
sysdate between hre.effective_start_date AND hre.effective_end_date AND
prl.to_person_id = hre.person_id AND
prl.requisition_header_id = req_header_id AND
rownum = 1;
select count(distinct nvl(deliver_to_location_id,0))
into no_of_values
from po_requisition_lines_all
where requisition_header_id = req_header_id;
select count(*)
into no_of_values
from por_item_attribute_values
where requisition_header_id = req_header_id;
select decode(hrtl.description,null,'',hrtl.description)
into value
from
hr_locations_all_tl hrtl,
po_requisition_lines_all prl
where
hrtl.location_id = prl.deliver_to_location_id AND
prl.requisition_header_id = req_header_id AND
hrtl.language = userenv('LANG') AND
rownum = 1;
select hrl.location_id
into l_location_id
from
hr_locations hrl,
po_requisition_lines_all prl
where
hrl.location_id = prl.deliver_to_location_id AND
prl.requisition_header_id = req_header_id AND
rownum = 1;
select decode(l_address_line_1,null,'',l_address_line_1||' ')||
decode(l_address_line_2,null,'',l_address_line_2||' ')||
decode(l_address_line_3,null,'',l_address_line_3||' ')||
l_address_info
into value
from dual;
select SUM(decode(prl.matching_basis, 'AMOUNT', prl.amount,
prl.unit_price * (prl.quantity - nvl(prl.quantity_cancelled,0))))
into value
from
po_requisition_lines_all prl
where
requisition_header_id = req_header_id
and nvl(modified_by_agent_flag, 'N') = 'N'
and nvl(cancel_flag, 'N') = 'N';
select note_to_agent
into value
from po_requisition_lines_all
where
requisition_header_id = req_header_id and
rownum = 1;
select count(distinct nvl(code_combination_id,0))
into no_of_values
from po_req_distributions_all
where requisition_line_id = req_line_id;
select concatenated_segments
into value
from
po_req_distributions_all prd,
gl_code_combinations_kfv cc,
gl_sets_of_books sob,
financials_system_params_all fsp
where
prd.requisition_line_id = req_line_id and
cc.code_combination_id = prd.code_combination_id and
cc.chart_of_accounts_id = sob.chart_of_accounts_id and
sob.set_of_books_id = fsp.set_of_books_id and
rownum =1;
select count(distinct nvl(project_id,0))
into no_of_values
from po_req_distributions_all
where requisition_line_id = req_line_id;
select projects.segment1
into value
from
po_req_distributions_all prd,
pa_projects_all projects
where
prd.requisition_line_id = req_line_id and
projects.project_id = prd.project_id and
rownum = 1;
select count(distinct nvl(task_id,0))
into no_of_values
from po_req_distributions_all
where requisition_line_id = req_line_id;
select tasks.task_name
into value
from
po_req_distributions_all prd,
pa_tasks tasks
where
prd.requisition_line_id = req_line_id and
tasks.task_id = prd.task_id and
rownum = 1;
select count(distinct nvl(expenditure_type,''))
into no_of_values
from po_req_distributions_all
where requisition_line_id = req_line_id;
select expenditure_type
into value
from po_req_distributions_all
where
requisition_line_id = req_line_id and
rownum =1 ;
select to_char(OOH.ORDER_NUMBER), OOL.FLOW_STATUS_CODE, OOL.LINE_ID
INTO l_so_number, l_status_code, l_line_id
from PO_REQUISITION_LINES PRL,
PO_REQUISITION_HEADERS PRH,
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
PO_SYSTEM_PARAMETERS PSP
WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = req_line_id
AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF
AND OOL.HEADER_ID = OOH.HEADER_ID
AND OOL.ORIG_SYS_LINE_REF = to_char(PRL.LINE_NUM)
AND PSP.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID;
select count(OOL.LINE_ID)
INTO l_split_line_num
from PO_REQUISITION_LINES PRL,
PO_REQUISITION_HEADERS PRH,
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
PO_SYSTEM_PARAMETERS PSP
WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = req_line_id
AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF
AND OOL.HEADER_ID = OOH.HEADER_ID
AND OOL.ORIG_SYS_LINE_REF = to_char(PRL.LINE_NUM)
AND PSP.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID;
select to_char(OOH.ORDER_NUMBER), OOL.FLOW_STATUS_CODE, OOL.LINE_ID
INTO l_so_number, l_status_code, l_line_id
from PO_REQUISITION_LINES PRL,
PO_REQUISITION_HEADERS PRH,
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
PO_SYSTEM_PARAMETERS PSP
WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = req_line_id
AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF
AND OOL.HEADER_ID = OOH.HEADER_ID
AND OOL.ORIG_SYS_LINE_REF = to_char(PRL.LINE_NUM)
AND PSP.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID;
select to_char(OOH.ORDER_NUMBER), OOL.FLOW_STATUS_CODE, OOL.LINE_ID
INTO l_so_number, l_status_code, l_line_id
from PO_REQUISITION_LINES PRL,
PO_REQUISITION_HEADERS PRH,
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
PO_SYSTEM_PARAMETERS PSP
WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = req_line_id
AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF
AND OOL.HEADER_ID = OOH.HEADER_ID
AND OOL.ORIG_SYS_LINE_REF = to_char(PRL.LINE_NUM)
AND OOL.LINE_ID = p_line_id
AND PSP.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID;
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = p_status_code
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
SELECT sum(decode(released_status, 'Y', 1, 0)), sum(1)
INTO l_released_count, l_total_count
FROM wsh_delivery_details
WHERE source_line_id = p_line_id
AND source_code = 'OE'
AND released_status <> 'D';
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = 'PICKED'
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = 'PICKED_PARTIAL'
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = p_status_code
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
select to_char(OOH.ORDER_NUMBER), OOH.FLOW_STATUS_CODE, OOL.LINE_ID
INTO l_so_number, l_status_code, l_line_id
from PO_REQUISITION_LINES_ALL PRL,
PO_REQUISITION_HEADERS_ALL PRH,
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
PO_SYSTEM_PARAMETERS_ALL PSP
WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = req_line_id
AND NVL(OOH.ORG_ID, -99) = NVL(PSP.ORG_ID, -99)
AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF
AND OOL.HEADER_ID = OOH.HEADER_ID
AND OOL.ORIG_SYS_LINE_REF = to_char(PRL.LINE_NUM)
AND PSP.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID;
SELECT meaning
INTO l_flow_meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = l_status_code
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID =
fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
select RSH.SHIPMENT_NUM BULK COLLECT INTO p_values
from RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL
WHERE RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSL.REQUISITION_LINE_ID = req_line_id;
SELECT PRL.suggested_vendor_name, PRL.vendor_id
FROM PO_REQUISITION_LINES_ALL PRL,
po_line_types plt
where prl.requisition_header_id = c_req_header_id
and prl.source_type_code = 'VENDOR'
and prl.line_type_id = plt.line_type_id
and plt.outside_operation_flag = 'N';
SELECT prl.suggested_vendor_name, prl.requisition_line_id
FROM PO_REQUISITION_LINES_ALL PRL,
po_line_types plt
where prl.requisition_header_id = c_req_header_id
and prl.source_type_code = 'VENDOR'
and prl.line_type_id = plt.line_type_id
and plt.outside_operation_flag = 'N'
and prl.LABOR_REQ_LINE_ID is null; -- labor line only
select CONTRACTOR_REQUISITION_FLAG
into l_contractor_requisition_flag
from po_requisition_headers
where requisition_header_id = req_header_id;
select decode(count(prl.line_location_id),0,'N','Y')
into is_aPO
from po_requisition_lines prl
where prl.requisition_header_id = req_header_id
and prl.line_location_id is not null;
select prh.TRANSFERRED_TO_OE_FLAG
into is_aSO
from po_requisition_headers prh
where prh.requisition_header_id = req_header_id;
select distinct full_name
into value
from per_all_people_f hre
where hre.person_id = approver_id
and trunc(sysdate) BETWEEN effective_start_date
and effective_end_date;
select distinct email_address
into value
from per_all_people_f hre
where hre.person_id = approver_id
and trunc(sysdate) BETWEEN effective_start_date
and effective_end_date;
select plc_urg.displayed_field
into value
from
po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_lookup_codes plc_urg
where
prh.requisition_header_id = req_header_id and
prl.requisition_header_id = prh.requisition_header_id and
plc_urg.lookup_code = nvl(prl.urgent_flag, 'N') and
plc_urg.lookup_type = 'YES/NO' and
rownum = 1;
select prd.project_id, prd.task_id, prd.expenditure_type,
prd.expenditure_organization_id,
to_char(prd.expenditure_item_date, date_format)
into project_id, task_id, expenditure_type,
expenditure_org_id, expenditure_item_date
from
po_req_distributions prd
where prd.requisition_line_id = req_line_id and
rownum = 1;
select projects.segment1
into project_number
from
po_req_distributions prd,
pa_projects projects
where
prd.requisition_line_id = req_line_id and
projects.project_id = prd.project_id and
rownum = 1;
select tasks.task_number
into task_number
from
po_req_distributions prd,
pa_tasks tasks
where
prd.requisition_line_id = req_line_id and
tasks.task_id = prd.task_id and rownum = 1 ;
select orgs.name
into expenditure_org
from
po_req_distributions prd,
pa_organizations_expend_v orgs
where
prd.requisition_line_id = req_line_id and
prd.expenditure_organization_id = orgs.organization_id and
rownum = 1;
select nvl(cancel_flag, 'N')
into cancelled
from po_requisition_lines_all
where requisition_line_id = req_line_id;
select to_char(0, fnd_currency.safe_get_format_mask(currency_code, 30))
into value
from sys.dual;
select prl.unit_price * (prl.quantity - nvl(prl.quantity_cancelled,0))
into total
from po_requisition_lines_all prl
where requisition_line_id = req_line_id;
select to_char(total, fnd_currency.safe_get_format_mask(currency_code, 30))
into value
from sys.dual;
select count(*)
into num_line_modified
from po_requisition_lines_all
where requisition_header_id = reqHeaderId
and MODIFIED_BY_AGENT_FLAG = 'Y';
select distinct pb.name
into value
from PER_BUSINESS_GROUPS_PERF pb,
per_all_people_f hre
where hre.person_id = approver_id
and hre.business_group_id=pb.business_group_id
and trunc(sysdate) BETWEEN effective_start_date
and effective_end_date;
SELECT sum(nvl(prd.nonrecoverable_tax,0))
INTO total
FROM
po_req_distributions prd,
po_requisition_lines prl
WHERE
prd.requisition_line_id = prl.requisition_line_id and
prl.requisition_header_id= ReqHeaderId and
NVL(prl.cancel_flag, 'N') = 'N' and
NVL(prl.modified_by_agent_flag, 'N') = 'N';
SELECT sum(nvl(prd.nonrecoverable_tax,0))
INTO total
FROM
po_req_distributions prd,
po_requisition_lines prl
WHERE
prd.requisition_line_id = ReqLineId AND
prd.requisition_line_id = prl.requisition_line_id AND
NVL(prl.cancel_flag, 'N') = 'N' AND
NVL(prl.modified_by_agent_flag, 'N') = 'N';
SELECT sum(nvl(prd.recoverable_tax,0))
INTO total
FROM
po_req_distributions prd,
po_requisition_lines prl
WHERE
prd.requisition_line_id = ReqLineId AND
prd.requisition_line_id = prl.requisition_line_id AND
NVL(prl.cancel_flag, 'N') = 'N' AND
NVL(prl.modified_by_agent_flag, 'N') = 'N';
SELECT COUNT(*)
INTO X_CANCELS
FROM PO_REQUISITION_LINES_ALL PRL
WHERE PRL.REQUISITION_HEADER_ID = P_REQ_HEADER_ID
AND NVL(CANCEL_FLAG, 'N') = 'N';
SELECT COUNT(*)
INTO X_RETURNS
FROM RCV_TRANSACTIONS RT
WHERE RT.PARENT_TRANSACTION_ID = P_TXN_ID
AND RT.TRANSACTION_TYPE = 'RETURN TO RECEIVING';
SELECT
PH.PO_HEADER_ID, PR.PO_RELEASE_ID
FROM
PO_REQUISITION_LINES_ALL PRL,
PO_REQ_DISTRIBUTIONS_ALL PRD,
PO_DISTRIBUTIONS_ALL PD,
PO_RELEASES_ALL PR,
PO_HEADERS_ALL PH
WHERE
PD.PO_HEADER_ID = PH.PO_HEADER_ID AND
PD.PO_RELEASE_ID = PR.PO_RELEASE_ID(+) AND
PRD.DISTRIBUTION_ID = PD.REQ_DISTRIBUTION_ID AND
PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND
PRL.REQUISITION_HEADER_ID = C_REQ_HEADER_ID;
SELECT COUNT(DISTINCT(
PH.SEGMENT1 ||DECODE(PR.RELEASE_NUM, NULL,'','-'||PR.RELEASE_NUM))),
min(PRL.REQUISITION_LINE_ID)
into no_of_po, req_line_id_po
FROM
PO_REQUISITION_LINES_ALL PRL,
PO_REQUISITION_HEADERS_ALL PRH,
PO_LINE_LOCATIONS_ALL PLL,
PO_RELEASES_ALL PR,
PO_HEADERS_ALL PH
WHERE
PLL.PO_HEADER_ID = PH.PO_HEADER_ID AND
PR.PO_RELEASE_ID(+) = PLL.PO_RELEASE_ID AND
PLL.LINE_LOCATION_ID = PRL.LINE_LOCATION_ID AND
PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID AND
PRH.REQUISITION_HEADER_ID = p_req_header_id;
SELECT COUNT(DISTINCT(OOH.HEADER_ID)), min(PRL.REQUISITION_LINE_ID)
into no_of_so, req_line_id_so
FROM
PO_REQUISITION_HEADERS PRH,
PO_REQUISITION_LINES_ALL PRL,
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
PO_SYSTEM_PARAMETERS PSP
WHERE
OOH.ORDER_SOURCE_ID = PSP.ORDER_SOURCE_ID AND
OOH.ORIG_SYS_DOCUMENT_REF = PRH.SEGMENT1 AND
OOH.SOURCE_DOCUMENT_ID = PRH.REQUISITION_HEADER_ID AND
OOH.HEADER_ID = OOL.HEADER_ID AND
OOL.SOURCE_DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID AND
PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID AND
PRH.REQUISITION_HEADER_ID = p_req_header_id;
SELECT
PH.SEGMENT1 ||DECODE(PR.RELEASE_NUM, NULL,'','-'||PR.RELEASE_NUM),
PH.PO_HEADER_ID,
PRL.SOURCE_TYPE_CODE,
PR.PO_RELEASE_ID,
HOU.NAME,
'Y',
DECODE(PR.PO_RELEASE_ID,
NULL, PH.AUTHORIZATION_STATUS,
PR.AUTHORIZATION_STATUS)
into
order_number,
header_id,
order_source_type,
po_release_id,
purchasing_org,
placed_on_po_flag,
order_status
FROM
PO_REQUISITION_LINES_ALL PRL,
PO_LINE_LOCATIONS_ALL PLL,
PO_RELEASES_ALL PR,
PO_HEADERS_ALL PH,
HR_ALL_ORGANIZATION_UNITS_VL HOU
WHERE
PLL.PO_HEADER_ID = PH.PO_HEADER_ID(+) AND
PR.PO_RELEASE_ID(+) = PLL.PO_RELEASE_ID AND
PLL.LINE_LOCATION_ID(+) = PRL.LINE_LOCATION_ID AND
PRL.REQUISITION_LINE_ID = req_line_id_po AND
PH.ORG_ID = HOU.ORGANIZATION_ID (+);
SELECT
TO_CHAR(OOH.ORDER_NUMBER),
OOH.HEADER_ID,
PRL.SOURCE_TYPE_CODE,
null,
null,
'N',
get_so_number_status(prl.requisition_line_id)
into
order_number,
header_id,
order_source_type,
po_release_id,
purchasing_org,
placed_on_po_flag,
order_status
FROM
PO_REQUISITION_HEADERS PRH,
PO_REQUISITION_LINES_ALL PRL,
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
PO_SYSTEM_PARAMETERS PSP
WHERE
OOH.ORDER_SOURCE_ID = PSP.ORDER_SOURCE_ID AND
OOH.ORIG_SYS_DOCUMENT_REF = PRH.SEGMENT1 AND
OOH.HEADER_ID = OOL.HEADER_ID AND
OOL.SOURCE_DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID AND
PRL.REQUISITION_LINE_ID = req_line_id_so AND
OOH.SOURCE_DOCUMENT_ID = PRH.REQUISITION_HEADER_ID AND
PRH.REQUISITION_HEADER_ID = p_req_header_id AND
rownum = 1;
SELECT
HOU.NAME ORG_NAME, PH.ORG_ID
FROM
PO_REQUISITION_LINES_ALL PRL,
PO_REQ_DISTRIBUTIONS_ALL PRD,
PO_DISTRIBUTIONS_ALL PD,
PO_HEADERS_ALL PH,
HR_ALL_ORGANIZATION_UNITS_VL HOU
WHERE
PD.PO_HEADER_ID = PH.PO_HEADER_ID AND
PRD.DISTRIBUTION_ID = PD.REQ_DISTRIBUTION_ID AND
PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND
PRL.REQUISITION_HEADER_ID = C_REQ_HEADER_ID AND
PH.ORG_ID = HOU.ORGANIZATION_ID (+);
SELECT
HOU.NAME ORG_NAME, PH.ORG_ID
FROM
PO_REQ_DISTRIBUTIONS_ALL PRD,
PO_DISTRIBUTIONS_ALL PD,
PO_HEADERS_ALL PH,
HR_ALL_ORGANIZATION_UNITS_VL HOU
WHERE
PD.PO_HEADER_ID = PH.PO_HEADER_ID AND
PRD.DISTRIBUTION_ID = PD.REQ_DISTRIBUTION_ID AND
PRD.REQUISITION_LINE_ID = c_req_line_id AND
PH.ORG_ID = HOU.ORGANIZATION_ID (+);
select count(distinct nvl(vendor_id,0)), min(vendor_id)
into no_of_suppliers, x_vendor_id
from po_requisition_suppliers
where requisition_line_id = req_line_id;
select vendor_name
into suppliername
from po_vendors
where vendor_id = x_vendor_id;
SELECT pap.full_name, pap.email_address, (select ph.phone_number from per_phones ph where
ph.parent_table(+) = 'PER_ALL_PEOPLE_F' AND
ph.parent_id (+) = fnd.employee_id AND
ph.phone_type(+) = 'W1' AND
trunc(SYSDATE) BETWEEN nvl(PH.DATE_FROM, trunc(SYSDATE)) AND
nvl(PH.DATE_TO, trunc(SYSDATE)) ) phone_number,
wn.begin_date
INTO full_name, email_address, phone, date_notified
FROM
wf_notifications wn,
wf_notification_attributes wna,
wf_user_roles wlur,
fnd_user fnd,
po_requisition_headers_all prh,
per_all_people_f pap
WHERE
prh.requisition_header_id = req_header_id AND
wlur.user_name = fnd.user_name AND
pap.person_id = fnd.employee_id AND
fnd.employee_id = l_approver_id AND
prh.requisition_header_id = wna.number_value AND
wna.name = 'DOCUMENT_ID' AND
wna.notification_id = wn.notification_id AND
wn.recipient_role = wlur.role_name AND
wn.status = 'OPEN' AND
wn.message_type = prh.wf_item_type AND
wn.message_name IN ('PO_REQ_APPROVE',
'PO_REQ_REMINDER1',
'PO_REQ_APPROVE_WEB_MSG',
'PO_REQ_REMINDER2',
'PO_REQ_REMINDER1_WEB',
'PO_REQ_REMINDER2_WEB',
'PO_REQ_APPROVE_JRAD',
'PO_REQ_APPROVE_SIMPLE',
'PO_REQ_APPROVE_SIMPLE_JRAD',
'PO_REQ_REMINDER1_JRAD',
'PO_REQ_REMINDER2_JRAD')
AND TRUNC(sysdate) between pap.effective_start_date and pap.effective_end_date
AND rownum = 1;