[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select we.wip_entity_name
, we.entity_type
, msik.concatenated_segments
, msik.description
, br.purchase_item_id
, msik2.concatenated_segments
, msik2.description
into l_wip_entity_name
, l_wip_entity_type
, l_primary_item_name
, l_primary_item_desc
, l_osp_item_id
, l_osp_item
, l_osp_item_desc
from wip_entities we
, wip_operation_resources wor
, bom_resources br
, mtl_system_items_kfv msik
, mtl_system_items_kfv msik2
where we.wip_entity_id = p_wip_entity_id
and we.organization_id = p_organization_id
and msik.inventory_item_id(+) = we.primary_item_id
and msik.organization_id(+) = we.organization_id
and wor.wip_entity_id = we.wip_entity_id
and wor.organization_id = we.organization_id
and nvl(wor.repetitive_schedule_id, -1) = nvl(p_rep_sched_id, -1)
and wor.operation_seq_num = p_op_seq_num
and wor.autocharge_type = WIP_CONSTANTS.PO_MOVE
and br.resource_id = wor.resource_id
and br.organization_id = wor.organization_id
and msik2.inventory_item_id = br.purchase_item_id
and msik2.organization_id = br.organization_id;
select unit_of_measure
into l_primary_uom
from mtl_units_of_measure
where uom_code = p_primary_uom;
select wl.line_code
into l_line_name
from wip_lines wl
, wip_repetitive_schedules wrs
where wrs.repetitive_schedule_id = p_rep_sched_id
and wrs.organization_id = p_organization_id
and wl.line_id = wrs.line_id
and wl.organization_id = wrs.organization_id;
select to_number(ORG_INFORMATION3) into l_ou_id
from HR_ORGANIZATION_INFORMATION
where ORGANIZATION_ID = p_organization_id
and ORG_INFORMATION_CONTEXT = l_org_acct_ctxt;
select reqimport_group_by_code
into l_req_import
from po_system_parameters_all
where org_id = l_ou_id;
select to_char(wip_workflow_s.nextval)
into p_itemkey
from dual;
select 'REQ EXISTS'
from po_requisitions_interface_all
where wip_entity_id = p_wip_entity_id;
select ph.segment1 po_num,
ph.po_header_id po_header_id,
pd.po_distribution_id po_distribution_id,
pd.org_id org_id,
pd.quantity_ordered po_line_qty,
pl.unit_meas_lookup_code po_line_uom,
pv.vendor_name subcontractor,
pvs.vendor_site_code subcontractor_site,
ph.approved_flag approved_flag,
0 release_num,
decode(msi.outside_operation_uom_type,
'RESOURCE', decode(wor.basis_type,
WIP_CONSTANTS.PER_ITEM,
round(pd.quantity_ordered /wor.usage_rate_or_amount,
WIP_CONSTANTS.INV_MAX_PRECISION),
round(wo.scheduled_quantity,
WIP_CONSTANTS.INV_MAX_PRECISION)),
'ASSEMBLY', decode(wor.basis_type,
WIP_CONSTANTS.PER_ITEM,
round(pd.quantity_ordered,
WIP_CONSTANTS.INV_MAX_PRECISION),
round(wo.scheduled_quantity,
WIP_CONSTANTS.INV_MAX_PRECISION))) required_assy_qty
from po_headers_all ph,
po_lines_all pl,
po_distributions_all pd,
po_vendors pv,
po_vendor_sites_all pvs,
wip_operation_resources wor,
wip_operations wo,
mtl_system_items msi
where pd.wip_entity_id = l_wip_entity_id
and pd.wip_operation_seq_num = l_osp_operation_num
and nvl(pd.wip_repetitive_schedule_id, -1) = nvl(l_rep_sched_id, -1)
and pd.po_header_id = ph.po_header_id
--and ph.approved_flag = 'Y'
and ph.type_lookup_code = 'STANDARD'
and nvl(ph.cancel_flag, 'N') = 'N'
and pl.po_line_id = pd.po_line_id
and pl.po_header_id = pd.po_header_id
and pv.vendor_id = ph.vendor_id
and pvs.vendor_site_id = ph.vendor_site_id
and pvs.org_id = ph.org_id
and pd.wip_entity_id = wo.wip_entity_id
and pd.destination_organization_id = wo.organization_id
and pd.wip_operation_seq_num = wo.operation_seq_num
and (pd.wip_repetitive_schedule_id is null or
pd.wip_repetitive_schedule_id = wo.repetitive_schedule_id)
and pl.item_id = msi.inventory_item_id
-- Fixed bug 4411247. Join msi to pd.destination_organization_id instead
-- of pl.org_id because pl.org_id store operating unit organization, not
-- item organization.
and pd.destination_organization_id = msi.organization_id
and pd.wip_entity_id = wor.wip_entity_id
and pd.wip_operation_seq_num = wor.operation_seq_num
and pd.wip_resource_seq_num = wor.resource_seq_num
and pd.destination_organization_id = wor.organization_id
and (pd.wip_repetitive_schedule_id is null or
pd.wip_repetitive_schedule_id =wor.repetitive_schedule_id)
and wor.autocharge_type = WIP_CONSTANTS.PO_MOVE
union all
select ph.segment1||'-'||pr.RELEASE_NUM po_num,
ph.po_header_id po_header_id,
pd.po_distribution_id po_distribution_id,
pd.org_id org_id,
pd.quantity_ordered po_line_qty,
pl.unit_meas_lookup_code po_line_uom,
pv.vendor_name subcontractor,
pvs.vendor_site_code subcontractor_site,
pr.approved_flag approved_flag,
pr.release_num,
decode(msi.outside_operation_uom_type,
'RESOURCE', decode(wor.basis_type,
WIP_CONSTANTS.PER_ITEM,
round(pd.quantity_ordered /wor.usage_rate_or_amount,
WIP_CONSTANTS.INV_MAX_PRECISION),
round(wo.scheduled_quantity,
WIP_CONSTANTS.INV_MAX_PRECISION)),
'ASSEMBLY', decode(wor.basis_type,
WIP_CONSTANTS.PER_ITEM,
round(pd.quantity_ordered,
WIP_CONSTANTS.INV_MAX_PRECISION),
round(wo.scheduled_quantity,
WIP_CONSTANTS.INV_MAX_PRECISION))) required_assy_qty
from po_releases_all pr,
po_headers_all ph,
po_lines_all pl,
po_line_locations_all ps,
po_distributions_all pd,
po_vendors pv,
po_vendor_sites_all pvs,
wip_operation_resources wor,
wip_operations wo,
mtl_system_items msi
where ph.type_lookup_code = 'BLANKET'
and pd.wip_entity_id = l_wip_entity_id
and pd.wip_operation_seq_num = l_osp_operation_num
and nvl(pd.wip_repetitive_schedule_id, -1) = nvl(l_rep_sched_id, -1)
and pd.po_header_id = ph.po_header_id
and pr.po_release_id = pd.po_release_id
and pr.po_header_id = pd.po_header_id
--and pr.approved_flag = 'Y'
and nvl(pr.cancel_flag, 'N') = 'N'
and ps.line_location_id = pd.line_location_id
and pl.po_line_id = ps.po_line_id
and pv.vendor_id = ph.vendor_id
and pvs.vendor_site_id = ph.vendor_site_id
and pvs.org_id = ph.org_id
and pd.wip_entity_id = wo.wip_entity_id
and pd.destination_organization_id = wo.organization_id
and pd.wip_operation_seq_num = wo.operation_seq_num
and (pd.wip_repetitive_schedule_id is null or
pd.wip_repetitive_schedule_id = wo.repetitive_schedule_id)
and pl.item_id = msi.inventory_item_id
-- Fixed bug 4411247. Join msi to pd.destination_organization_id instead
-- of pl.org_id because pl.org_id store operating unit organization, not
-- item organization.
and pd.destination_organization_id = msi.organization_id
and pd.wip_entity_id = wor.wip_entity_id
and pd.wip_operation_seq_num = wor.operation_seq_num
and pd.wip_resource_seq_num = wor.resource_seq_num
and pd.destination_organization_id = wor.organization_id
and (pd.wip_repetitive_schedule_id is null or
pd.wip_repetitive_schedule_id =wor.repetitive_schedule_id)
and wor.autocharge_type = WIP_CONSTANTS.PO_MOVE;
select prh.segment1 req_num
from po_requisition_headers_all prh,
po_requisition_lines_all prl
where prl.wip_entity_id = l_wip_entity_id
and prl.wip_operation_seq_num = l_osp_operation_num
and nvl(prl.wip_repetitive_schedule_id, -1) = nvl(l_rep_sched_id, -1)
and prl.line_location_id is null
and prh.requisition_header_id = prl.requisition_header_id;
select to_char(wip_workflow_s.nextval)
into childkey
from dual;
PROCEDURE SelectShippingManager( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out nocopy varchar2) is
l_organization_id number :=
wf_engine.GetItemAttrNumber( itemtype => itemtype,
itemkey => itemkey,
aname => 'ORGANIZATION_ID');
wf_core.context('WIP_OSP_SHP_I_WF', 'SelectShippingManager', itemtype, itemkey);
END SelectShippingManager;
select ap_vendor_sites_pkg.format_address (pvs.country,
pvs.address_line1, pvs.address_line2, pvs.address_line3,
pvs.address_line4, pvs.city,pvs.county,pvs.state,
pvs.province,pvs.zip,null)
into l_address
from po_headers_all ph,
po_vendor_sites_all pvs
where ph.po_header_id = l_po_header_id
and pvs.org_id = ph.org_id
and pvs.vendor_site_id = ph.vendor_site_id;
select pd.wip_entity_id,
pd.wip_repetitive_schedule_id,
ps.ship_to_organization_id,
pd.wip_operation_seq_num
from po_distributions_all pd,
po_line_locations_all ps
where pd.po_distribution_id = p_po_distribution_id
and ps.line_location_id = pd.line_location_id;
select ph.segment1 ||
decode (pr.release_num,
NULL, NULL, '-' || pr.release_num) po_num,
ph.po_header_id po_header_id,
pd.quantity_ordered po_line_qty,
pl.unit_meas_lookup_code po_line_uom,
pv.vendor_name subcontractor,
pvs.vendor_site_code subcontractor_site,
ps.ship_to_location_id ship_to_location_id,
pr.release_num
from po_releases_all pr,
po_vendor_sites_all pvs,
po_vendors pv,
po_headers_all ph,
po_lines_all pl,
po_line_locations_all ps,
po_distributions_all pd
where pd.po_distribution_id = p_po_distribution_id
and ps.line_location_id = pd.line_location_id
and pl.po_line_id = pd.po_line_id
and ph.po_header_id = pd.po_header_id
and pr.po_release_id (+) = pd.po_release_id
and pv.vendor_id = ph.vendor_id
and pvs.vendor_site_id = ph.vendor_site_id
and pvs.org_id = ph.org_id;
select to_char(wip_workflow_s.nextval)
into l_itemkey
from dual;
select ph2.segment1 || decode (pr.release_num,
NULL, NULL, '-' || pr.release_num) po_num,
ph2.po_header_id,
pd2.po_distribution_id,
pd2.org_id,
pd2.quantity_ordered,
pl2.unit_meas_lookup_code,
pd2.wip_operation_seq_num,
pl2.item_id,
msik2.concatenated_segments,
msik2.description,
pr.release_num
from po_releases_all pr,
po_location_associations_all pla,
mtl_system_items_kfv msik2,
po_lines_all pl2,
po_line_locations_all ps1,
po_headers_all ph2,
wip_operations wo,
po_distributions_all pd2,
po_distributions_all pd1
where pd1.po_distribution_id = l_po_distribution_id
and wo.wip_entity_id = pd1.wip_entity_id
and wo.organization_id = pd1.destination_organization_id
and wo.operation_seq_num = pd1.wip_operation_seq_num
and nvl(wo.repetitive_schedule_id, -1)
= nvl(pd1.wip_repetitive_schedule_id, - 1)
and pd2.po_distribution_id <> l_po_distribution_id
and pd2.wip_entity_id = pd1.wip_entity_id
and nvl(pd2.wip_repetitive_schedule_id, -1)
= nvl(pd1.wip_repetitive_schedule_id, -1)
and pd2.wip_operation_seq_num in
(pd1.wip_operation_seq_num, wo.next_operation_seq_num)
and ph2.po_header_id = pd2.po_header_id
and ph2.approved_flag = 'Y'
and pl2.po_line_id = pd2.po_line_id
and pl2.item_id = msik2.inventory_item_id
and pl2.org_id = msik2.organization_id
and ps1.line_location_id = pd1.line_location_id
and pla.location_id = ps1.ship_to_location_id
and pla.vendor_id = ph2.vendor_id
and pla.vendor_site_id = ph2.vendor_site_id
and pr.po_release_id (+) = pd2.po_release_id
and ( (ph2.type_lookup_code = 'STANDARD'
and nvl(ph2.cancel_flag, 'N') = 'N')
OR (ph2.type_lookup_code = 'BLANKET'
and pr.po_release_id = pd2.po_release_id
and nvl(pr.cancel_flag, 'N') = 'N'))
order by pd2.wip_operation_seq_num, pd2.wip_resource_seq_num ;
select pv.vendor_name,
pvs.vendor_site_code
from po_vendors pv,
po_vendor_sites_all pvs,
po_distributions_all pd,
po_location_associations pla,
po_line_locations_all ps
where pd.po_distribution_id = l_po_distribution_id
and ps.line_location_id = pd.line_location_id
and pla.location_id = ps.ship_to_location_id
and pv.vendor_id = pla.vendor_id
and pvs.vendor_site_id = pla.vendor_site_id
and pvs.org_id = ps.org_id;
select request_id
into l_child_id
from fnd_concurrent_requests
where parent_request_id = l_parent_id;