[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select po_release_id into x_po_release_id
from po_line_locations_all
where line_location_id = X_line_loc_id1;
select por.release_type,
polc.displayed_field,
por.org_id ,
poh.segment1||'-'||to_char(por.release_num),
por.agent_id
into x_Document_SubType,
x_document_type,
x_org_id,
x_document_num,
x_agent_id
from po_releases_all por,
po_lookup_codes polc,
po_headers_all poh
where po_release_id = x_po_release_id
and por.po_header_id=poh.po_header_id
and por.release_type = polc.lookup_code
and polc.lookup_type = 'DOCUMENT SUBTYPE';
select poh.segment1, poh.agent_id, poh.type_lookup_code, polc.displayed_field, poh.org_id
into x_document_num, x_agent_id, x_document_subtype, x_document_type, x_org_id
from po_headers_all poh,
po_lookup_codes polc
where
poh.po_header_id = x_document_id
and poh.type_lookup_code = polc.lookup_code
and polc.lookup_type = 'PO TYPE';
select to_person_id
into x_requester_id
from po_requisition_lines_all
where line_location_id = x_line_location_id;
update po_headers_all
set authorization_status = 'APPROVED'
where po_header_id = x_document_id;
update po_releases_all
set authorization_status = 'APPROVED'
where po_release_id = x_document_id;
select to_char(PO_WF_ITEMKEY_S.NEXTVAL) into l_seq from sys.dual;
update po_headers_all set
authorization_status = 'IN PROCESS',
revision_num = revision_num + 1,
revised_date = sysdate,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where po_header_id = document_id;
update po_releases_all set
authorization_status = 'IN PROCESS',
revision_num = revision_num + 1,
revised_date = sysdate,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where po_release_id = document_id;
select wf_approval_itemtype, wf_approval_process
into x_ItemType, WorkflowProcess
from PO_DOCUMENT_TYPES_V
where DOCUMENT_TYPE_CODE = DocumentTypeCode
and DOCUMENT_SUBTYPE = DocumentSubtype;
select poh.agent_id, pol.item_id, poll.ship_to_organization_id,pol.line_num,
pol.UNIT_MEAS_LOOKUP_CODE, pol.ITEM_DESCRIPTION,
DECODE(poll.SHIPMENT_TYPE, 'PRICE BREAK', NULL,
poll.QUANTITY - poll.QUANTITY_CANCELLED),pv.vendor_name
into x_agent_id, x_item_id, x_ship_to_organization_id,x_line_num,
x_uom,x_item_description,x_qty_ordered,x_vendor_name
from po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
po_vendors pv
where
poll.line_location_id = x_line_location_id
and pol.po_line_id = poll.po_line_id
and poll.po_header_id = poh.po_header_id
and poh.vendor_id=pv.vendor_id;
select por.agent_id, pol.item_id, poll.ship_to_organization_id,pol.line_num,
pol.UNIT_MEAS_LOOKUP_CODE, POL.ITEM_DESCRIPTION,
DECODE(poll.SHIPMENT_TYPE, 'PRICE BREAK', NULL,
poll.QUANTITY - poll.QUANTITY_CANCELLED),pv.vendor_name
into x_agent_id, x_item_id, x_ship_to_organization_id,x_line_num,
x_uom,x_item_description,x_qty_ordered,x_vendor_name
from po_releases_all por,
po_lines_all pol,
po_line_locations_all poll,
po_vendors pv,
po_headers_all poh
where
poll.line_location_id = x_line_location_id
and pol.po_line_id = poll.po_line_id
and poll.po_release_id = por.po_release_id
and poh.po_header_id = por.po_header_id
and poh.vendor_id = pv.vendor_id;
select user_name
into x_supplier_username
from fnd_user where user_id = x_supplier_userid;
select distinct planner_code
into x_planner_code
from mtl_system_items
where inventory_item_id = x_item_id
and organization_id = x_ship_to_org_id;
select distinct employee_id
into x_employee_id
from mtl_planners
where planner_code = x_planner_code
and organization_id = x_ship_to_org_id;
select distinct OUTSIDE_OPERATION_FLAG
into x_osp_item_flag
from mtl_system_items
where inventory_item_id = x_item_id
and organization_id = x_ship_to_org_id;
select distinct REPETITIVE_PLANNING_FLAG
into x_planning_item_flag
from mtl_system_items
where inventory_item_id = x_item_id
and organization_id = x_ship_to_org_id;
procedure Update_Date ( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out nocopy varchar2 )
is
x_progress varchar2(3) := '000';
UPDATE po_line_locations_all
SET promised_date = x_new_date,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
WHERE line_location_id = x_line_location_id;
-- Update the parent WF to register the acceptance.
-- The document would have to be routed for approval.
--
x_parent_WF_itemkey := wf_engine.GetItemAttrText ( itemtype => itemtype,
itemkey => itemkey,
aname => 'PARENT_WF_ITEMKEY');
aname => 'UPDATE_ACCEPTED_FLAG',
avalue => 'Y');
wf_core.context('POS_CHANGE_PROM_DATES','Update_Date',x_progress);
procedure Update_Prom_Needby_Date ( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out nocopy varchar2 )
is
x_progress varchar2(3) := '000';
UPDATE po_line_locations_all
SET promised_date = x_new_promised_date,
need_by_date = x_new_needby_date,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
WHERE line_location_id = x_line_location_id;
-- Update the parent WF to register the acceptance.
-- The document would have to be routed for approval.
--
x_parent_WF_itemkey := wf_engine.GetItemAttrText ( itemtype => itemtype,
itemkey => itemkey,
aname => 'PARENT_WF_ITEMKEY');
aname => 'UPDATE_ACCEPTED_FLAG',
avalue => 'Y');
wf_core.context('POS_CHANGE_PROM_DATES','Update_Prom_Needby_Date',x_progress);
procedure Update_Parent_WF ( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out nocopy varchar2 )
is
x_progress varchar2(3) := '000';
wf_core.context('POS_CHANGE_PROM_DATES','Update_Parent_WF',x_progress);
select 'Dist Exists', deliver_to_person_id
into x_dummy, x_deliver_to_person_id
from po_distributions_all
where line_location_id = x_line_location_id
and distribution_num = x_current_distribution_num;
-- update dist. num. item attribute
wf_engine.SetItemAttrText ( itemtype => itemtype,
itemkey => itemkey,
aname => 'CURRENT_DISTRIBUTION',
avalue => x_current_distribution_num);
SELECT FND.user_id
INTO x_user_id
FROM FND_USER FND, HR_EMPLOYEES_CURRENT_V HR
WHERE HR.EMPLOYEE_ID = x_employee_id
AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID
AND ROWNUM = 1;
select MIN(fr.responsibility_id)
into x_resp_id
from fnd_user_resp_groups fur,
fnd_responsibility fr,
financials_system_params_all fsp
where fur.user_id = x_user_id
and fur.responsibility_application_id = x_resp_appl_id
and fur.responsibility_id = fr.responsibility_id
and fr.start_date < sysdate
and nvl(fr.end_date, sysdate +1) >= sysdate
and fur.start_date < sysdate
and nvl(fur.end_date, sysdate +1) >= Sysdate
AND nvl(fnd_profile.value_specific('ORG_ID', NULL, fr.responsibility_id, fur.responsibility_application_id),-1) = nvl(x_org_id,-1)
and nvl(fsp.org_id,-1) = nvl(x_org_id,-1)
and nvl(fsp.business_group_id,-1) = nvl(fnd_profile.value_specific('PER_BUSINESS_GROUP_ID', NULL, fr.responsibility_id, fur.responsibility_application_id),-1);