[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inventory_organization_id
INTO x_organization_id
FROM financials_system_parameters;
SELECT msi.buyer_id
INTO x_buyer_id
FROM mtl_system_items msi
WHERE msi.inventory_item_id = x_item_id
AND msi.organization_id = x_organization_id;
select poa.agent_id into x_buyer_id
from po_agents poa,mtl_item_categories mic
where mic.inventory_item_id=x_item_id
and mic.category_id=poa.category_id
and mic.organization_id=x_organization_id
and mic.category_set_id=(select category_set_id
from mtl_default_sets_view
where functional_area_id = 2);
SELECT vendor_site_id
INTO x_vendor_site_id
FROM po_vendor_sites_all pvs,
org_organization_definitions oog
WHERE pvs.vendor_site_code = l_vendor_site_code
AND nvl(pvs.org_id,nvl(oog.operating_unit,-1)) =
nvl(oog.operating_unit,-1)
AND oog.organization_id = x_organization_id
AND pvs.vendor_id = x_vendor_id;
SELECT 'Y'
INTO l_buyer_ok
FROM per_people_f ppf
WHERE x_buyer_id = ppf.person_id
AND trunc(sysdate) between ppf.effective_start_date
AND NVL(ppf.effective_end_date, sysdate +1);
SELECT pasl.asl_id,
paa.using_organization_id,
pasl.primary_vendor_item,
paa.purchasing_unit_of_measure,
paa.consigned_from_supplier_flag,
paa.enable_vmi_flag,
paa.last_billing_date,
paa.consigned_billing_cycle,
paa.vmi_min_qty,
paa.vmi_max_qty,
paa.enable_vmi_auto_replenish_flag,
paa.vmi_replenishment_approval
FROM po_approved_supplier_lis_val_v pasl,
po_asl_attributes paa,
po_asl_status_rules_v pasr
WHERE pasl.item_id = x_item_id --
AND pasl.vendor_id = x_vendor_id
AND nvl(pasl.vendor_site_id, -1) = nvl(x_vendor_site_id, -1)
AND pasl.using_organization_id IN (-1, x_using_organization_id)
AND pasl.asl_id = paa.asl_id
AND pasr.business_rule like '2_SOURCING'
AND pasr.allow_action_flag like 'Y'
AND pasr.status_id = pasl.asl_status_id
AND paa.using_organization_id =
(SELECT max(paa2.using_organization_id)
FROM po_asl_attributes paa2
WHERE paa2.asl_id = pasl.asl_id
AND paa2.using_organization_id IN (-1, x_using_organization_id))
ORDER BY pasl.using_organization_id DESC;
SELECT pasl.asl_id,
paa.using_organization_id,
pasl.primary_vendor_item,
paa.purchasing_unit_of_measure,
paa.consigned_from_supplier_flag,
paa.enable_vmi_flag,
paa.last_billing_date,
paa.consigned_billing_cycle,
paa.vmi_min_qty,
paa.vmi_max_qty,
paa.enable_vmi_auto_replenish_flag,
paa.vmi_replenishment_approval
FROM po_approved_supplier_lis_val_v pasl,
po_asl_attributes paa,
po_asl_status_rules_v pasr
WHERE pasl.category_id = p_category_id --
AND pasl.vendor_id = x_vendor_id
AND nvl(pasl.vendor_site_id, -1) = nvl(x_vendor_site_id, -1)
AND pasl.using_organization_id IN (-1, x_using_organization_id)
AND pasl.asl_id = paa.asl_id
AND pasr.business_rule like '2_SOURCING'
AND pasr.allow_action_flag like 'Y'
AND pasr.status_id = pasl.asl_status_id
AND paa.using_organization_id =
(SELECT max(paa2.using_organization_id)
FROM po_asl_attributes paa2
WHERE paa2.asl_id = pasl.asl_id
AND paa2.using_organization_id IN (-1, x_using_organization_id))
ORDER BY pasl.using_organization_id DESC;
SELECT
enable_vmi_flag
INTO
l_enable_vmi_flag
FROM
po_asl_attributes asl
WHERE
asl.asl_id = l_asl_id
AND asl.using_organization_id = l_using_organization_id
;
select org_id
into x_org_id
from financials_system_parameters;
SELECT inventory_organization_id
INTO x_using_organization_id
FROM financials_system_parameters;
SELECT msi.revision_qty_control_code
INTO x_item_rev_control
FROM mtl_system_items msi
WHERE msi.inventory_item_id = x_item_id
AND msi.organization_id = x_using_organization_id;
SELECT vendor_site_id
INTO x_vendor_site_id
FROM po_vendor_sites_all pvs,
org_organization_definitions oog
WHERE pvs.vendor_site_code = p_vendor_site_code
AND pvs.vendor_id = x_vendor_id --
AND nvl(pvs.org_id,nvl(oog.operating_unit,-1)) =
nvl(oog.operating_unit,-1)
AND oog.organization_id = x_using_organization_id;
SELECT pad.document_header_id,
pad.document_line_id,
pol.line_num,
pad.document_type_code,
NVL (x_vendor_site_id, poh.vendor_site_id),
NVL (x_vendor_contact_id, poh.vendor_contact_id),
NVL (x_buyer_id, poh.agent_id),
/* Bug 2348331 fixed. swapped the elements in the below
nvl statement in order that the vendor_product_num at
blanket line level takes precedence to that at ASL level.
*/
NVL (pol.vendor_product_num, x_vendor_product_num),
poh.global_agreement_flag,
poh.org_id,
-- Bug 3361128: also select the UOM on the doc
pol.unit_meas_lookup_code
FROM po_asl_documents pad,
po_approved_supplier_list pasl,
po_headers_all poh, --CTO changes FPH
po_lines_all pol --CTO changes FPH
WHERE pasl.asl_id = x_local_asl_id
AND pad.asl_id = pasl.asl_id
AND pad.using_organization_id = l_using_organization_id --
AND pad.document_header_id = poh.po_header_id
AND pol.po_line_id (+) = pad.document_line_id --
AND ( x_destination_doc_type = 'REQ'
OR x_destination_doc_type = 'REQ_NONCATALOG' --
OR x_destination_doc_type IS NULL
--
OR (x_destination_doc_type = 'STANDARD PO' and
(poh.type_lookup_code = 'QUOTATION' OR
(poh.type_lookup_code = 'BLANKET' AND nvl(poh.global_agreement_flag, 'N') = 'Y'))
)
--
--for x_destination_doc_type = 'PO'
OR poh.type_lookup_code = 'QUOTATION'
)
AND ( ( poh.type_lookup_code = 'QUOTATION'
AND poh.status_lookup_code = 'A'
AND ( NOT EXISTS (
SELECT 'no shipments exists'
FROM po_line_locations_all poll
WHERE poll.po_line_id = pol.po_line_id
) --Bug7384016 added this condition to include quotations without price breaks
OR ( poh.approval_required_flag = 'N'
AND (EXISTS (SELECT 'valid'
FROM po_line_locations_all poll
WHERE poll.po_line_id = pol.po_line_id
AND TRUNC (NVL (poll.end_date, x_sourcing_date)) >= --Bug6982267
trunc(x_sourcing_date)
)
)
)
--Bug7384016 segregated the coditions for approval_required_flag = Y/N
OR (poh.approval_required_flag = 'Y'
AND ( EXISTS (
SELECT 'quote is approved'
FROM po_quotation_approvals poqa,
po_line_locations_all poll --CTO changes FPH
WHERE poqa.approval_type IS NOT NULL
AND poqa.line_location_id = poll.line_location_id
AND poll.po_line_id = pol.po_line_id
AND TRUNC (NVL (poll.end_date, x_sourcing_date)) >= --Bug6982267
trunc(x_sourcing_date)
)
)
)
)
)
OR ( poh.type_lookup_code = 'BLANKET'
AND poh.approved_flag = 'Y'
AND NVL (poh.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED', 'CLOSED')
AND NVL (poh.cancel_flag, 'N') = 'N'
AND NVL (poh.frozen_flag, 'N') = 'N'
AND TRUNC (NVL (pol.expiration_date, x_sourcing_date)) >=
trunc(x_sourcing_date) --Bug 2695699
-- following condition (1 line) was missed when it was rewritten in FPJ
AND NVL (pol.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
AND NVL (pol.cancel_flag, 'N') = 'N'
)
--
OR ( poh.type_lookup_code = 'CONTRACT'
AND (( NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y' --
and poh.approved_date is not null --
)
or nvl(poh.approved_flag,'N') = 'Y'
)
AND NVL(poh.cancel_flag,'N') = 'N'
AND NVL(poh.frozen_flag,'N') = 'N'
AND NVL(poh.closed_code, 'OPEN') = 'OPEN'
AND p_return_contract = 'Y' --
)
--
)
AND (x_currency_code IS NULL OR poh.currency_code = x_currency_code)
AND (p_sequence_number is NULL OR --
p_sequence_number = pad.sequence_num)
AND x_sourcing_date >= NVL (poh.start_date, x_sourcing_date - 1)
AND x_sourcing_date <= NVL (poh.end_date, x_sourcing_date + 1)
--
AND (poh.type_lookup_code = 'CONTRACT' OR
(NVL(pol.item_revision, -1) = NVL(x_item_rev, -1) OR
(NVL (p_item_rev_control, 1) = 1 AND x_item_rev IS NULL)))
--
--
--This clause returns rows if document is GA or
--EITHER vendor_site_sourcing_flag is N and site_ids match
--OR vendor_site_sourcing_flag is Y and site codes match
AND
(
(NVL (poh.global_agreement_flag, 'N') = 'Y')
OR
( NVL (poh.global_agreement_flag, 'N') = 'N'
AND ( ( p_vendor_site_sourcing_flag = 'N'
AND (x_vendor_site_id IS NULL OR
poh.vendor_site_id = x_vendor_site_id)
)
OR
( p_vendor_site_sourcing_flag = 'Y'
AND (p_vendor_site_code IS NULL OR
poh.vendor_site_id =
(select pvs.vendor_site_id
from po_vendor_sites pvs
where pvs.vendor_site_code = p_vendor_site_code
and pvs.vendor_id = x_vendor_id))
)
)
)
)
--
--If document is not a GA then the operating units should match
--If document is GA and vendor site sourcing_flag is Y then
--vendor_site_code for current org(as enabled org) should match
--If the document is GA and vendor site sourcing_flag is N then
--current org should be enabled in GA
--change is requird to do proper vendor sourcing
AND ( ( NVL (poh.global_agreement_flag, 'N') = 'N'
AND (x_multi_org = 'N')
AND NVL (poh.org_id, -1) = NVL (p_org_id, -1)
)
--
OR (( NVL (poh.global_agreement_flag, 'N') = 'Y'
AND ( p_vendor_site_sourcing_flag = 'Y'
AND (p_vendor_site_code IS NULL OR
EXISTS (
SELECT 'vendor site code matches'
FROM po_ga_org_assignments poga,
po_vendor_sites_all pvsa
WHERE poh.po_header_id = poga.po_header_id
AND poga.organization_id = p_org_id
AND poga.vendor_site_id = decode( Nvl (poh.Enable_All_Sites,'N'),'N', pvsa.vendor_site_id ,poga.Vendor_Site_Id) -- pvsa.vendor_site_id
AND pvsa.vendor_site_code = p_vendor_site_code
AND poga.enabled_flag = 'Y'
AND pvsa.vendor_id = x_vendor_id))
)
)OR ( p_vendor_site_sourcing_flag = 'N'
--
AND EXISTS (
SELECT 'vendor site id matches'
FROM po_ga_org_assignments poga
WHERE poh.po_header_id = poga.po_header_id
AND poga.vendor_site_id = decode( Nvl (poh.Enable_All_Sites,'N'),'Y',poga.Vendor_Site_Id,x_vendor_site_id) --< R12 GCPA ER>
AND poga.enabled_flag = 'Y')
AND (x_destination_doc_type = 'STANDARD PO'
OR EXISTS (
SELECT 'enabled org exists'
FROM po_ga_org_assignments poga
WHERE poh.po_header_id = poga.po_header_id
AND poga.organization_id = p_org_id
AND poga.enabled_flag = 'Y'))
--
)
)
--
OR x_multi_org = 'Y'
) -- FPI GA
ORDER BY sequence_num ASC;
SELECT pad.document_header_id,
pad.document_line_id,
NULL line_num, -- Only Contracts are returned
pad.document_type_code,
NVL (x_vendor_site_id, poh.vendor_site_id),
NVL (x_vendor_contact_id, poh.vendor_contact_id),
NVL (x_buyer_id, poh.agent_id),
x_vendor_product_num,
poh.global_agreement_flag,
poh.org_id,
NULL unit_meas_lookup_code
FROM po_asl_documents pad,
po_approved_supplier_list pasl,
po_headers_all poh --CTO changes FPH
WHERE pasl.asl_id = x_local_asl_id
AND pad.asl_id = pasl.asl_id
AND pad.using_organization_id = l_using_organization_id --
AND pad.document_header_id = poh.po_header_id
AND ( x_destination_doc_type = 'REQ'
OR x_destination_doc_type = 'REQ_NONCATALOG' --
OR x_destination_doc_type IS NULL
)
AND (
--
poh.type_lookup_code = 'CONTRACT'
AND (( NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y' --
and poh.approved_date is not null)
OR
nvl(poh.approved_flag,'N') = 'Y'
)
AND NVL(poh.cancel_flag,'N') = 'N'
AND NVL(poh.frozen_flag,'N') = 'N'
AND NVL(poh.closed_code, 'OPEN') = 'OPEN'
AND p_return_contract = 'Y' --
--
)
AND (x_currency_code IS NULL OR poh.currency_code = x_currency_code)
AND (p_sequence_number is NULL OR --
p_sequence_number = pad.sequence_num)
AND x_sourcing_date >= NVL (poh.start_date, x_sourcing_date - 1)
AND x_sourcing_date <= NVL (poh.end_date, x_sourcing_date + 1)
--
AND poh.type_lookup_code = 'CONTRACT'
--
--
--This clause returns rows if document is GA or
--EITHER vendor_site_sourcing_flag is N and site_ids match
--OR vendor_site_sourcing_flag is Y and site codes match
AND
(
(NVL (poh.global_agreement_flag, 'N') = 'Y')
OR
( NVL (poh.global_agreement_flag, 'N') = 'N'
AND ( p_vendor_site_sourcing_flag = 'N'
AND (x_vendor_site_id IS NULL OR
poh.vendor_site_id = x_vendor_site_id)
)
)
)
--
--If document is not a GA then the operating units should match
--If document is GA and vendor site sourcing_flag is Y then
--vendor_site_code for current org(as enabled org) should match
--If the document is GA and vendor site sourcing_flag is N then
--current org should be enabled in GA
--change is requird to do proper vendor sourcing
AND ( ( NVL (poh.global_agreement_flag, 'N') = 'N'
AND (x_multi_org = 'N')
AND NVL (poh.org_id, -1) = NVL (p_org_id, -1)
)
--
OR ( NVL (poh.global_agreement_flag, 'N') = 'Y'
OR ( p_vendor_site_sourcing_flag = 'N'
--
AND
(
x_vendor_site_id is null
OR
EXISTS (
SELECT 'vendor site id matches'
FROM po_ga_org_assignments poga
WHERE poh.po_header_id = poga.po_header_id
AND poga.vendor_site_id = x_vendor_site_id
AND poga.enabled_flag = 'Y')
)
--
)
)
--
OR x_multi_org = 'Y'
) -- FPI GA
ORDER BY sequence_num ASC;
SELECT item_id
INTO l_item_id
FROM po_approved_supplier_list
WHERE asl_id = x_local_asl_id;
SELECT poh.po_header_id,
pol.po_line_id,
pol.line_num,
poh.type_lookup_code,
NVL (x_vendor_site_id, poh.vendor_site_id),
NVL (x_vendor_contact_id, poh.vendor_contact_id),
NVL (x_buyer_id, poh.agent_id),
/* Bug 2348331 fixed. swapped the elements in the below
nvl statement in order that the vendor_product_num at
blanket line level takes precedence to that at ASL level.
*/
NVL (pol.vendor_product_num, x_vendor_product_num),
poh.global_agreement_flag,
poh.org_id,
-- Bug 3361128: also select the UOM on the doc
pol.unit_meas_lookup_code,
decode(poh.type_lookup_code, 'BLANKET', 1, 'QUOTATION', 2) DocTypeFetchOrder,
decode(pol.unit_meas_lookup_code, p_purchasing_uom, 1,2) MatchUom,
NVL (poh.global_agreement_flag, 'N') global_flag,
poh.creation_date creation_date
FROM po_headers_all poh, --CTO changes FPH
po_lines_all pol --CTO changes FPH
WHERE pol.po_header_id = poh.po_header_id -- Bug5081434 No Outer Join
AND ( x_destination_doc_type = 'REQ'
OR x_destination_doc_type = 'REQ_NONCATALOG' --
OR x_destination_doc_type IS NULL
--
OR (x_destination_doc_type = 'STANDARD PO' and
(poh.type_lookup_code = 'QUOTATION' OR
(poh.type_lookup_code = 'BLANKET' AND nvl(poh.global_agreement_flag, 'N') = 'Y'))
)
--
--for x_dest_doc_type = 'PO'
OR poh.type_lookup_code = 'QUOTATION'
)
AND ( ( poh.type_lookup_code = 'BLANKET'
AND poh.approved_flag = 'Y'
AND NVL (poh.cancel_flag, 'N') = 'N'
AND NVL (poh.frozen_flag, 'N') = 'N'
AND TRUNC (NVL (pol.expiration_date, x_sourcing_date)) >=
trunc(x_sourcing_date) -- Bug 2695699
AND
NVL (poh.user_hold_flag, 'N') = 'N'
AND NVL (poh.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED', 'CLOSED')
--Bug5258984 (following condition was missed when they rewrote this code for FPJ)
AND NVL (pol.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED', 'CLOSED')
AND NVL (pol.cancel_flag, 'N') = 'N'
)
OR ( poh.type_lookup_code = 'QUOTATION'
AND (poh.status_lookup_code = 'A')
AND ( NOT EXISTS (
SELECT 'no shipments exists'
FROM po_line_locations_all poll
WHERE poll.po_line_id = pol.po_line_id
)--Bug7384016 added this condition to include quotations without price breaks
OR (
(poh.approval_required_flag = 'Y')
AND ( EXISTS (
SELECT *
FROM po_quotation_approvals poqa,
po_line_locations_all poll --CTO changes FPH
WHERE poqa.approval_type IS NOT NULL
AND poqa.line_location_id = poll.line_location_id
AND poll.po_line_id = pol.po_line_id
AND TRUNC (NVL (poll.end_date, x_sourcing_date)) >=
trunc(x_sourcing_date)
) --Bug6982267
)
)
OR (
(poh.approval_required_flag = 'N')
AND ( EXISTS (
SELECT 'valid'
FROM po_line_locations_all poll
WHERE poll.po_line_id = pol.po_line_id
AND TRUNC (NVL (poll.end_date, x_sourcing_date)) >=
trunc(x_sourcing_date)
) --Bug6982267
)
)
)
)
)
AND poh.vendor_id = x_vendor_id
AND poh.type_lookup_code IN ('BLANKET','QUOTATION')
--
--This clause returns rows if document is GA or
--EITHER vendor_site_sourcing_flag is N and site_ids match
--OR vendor_site_sourcing_flag is Y and site codes match
AND
(
(NVL (poh.global_agreement_flag, 'N') = 'Y')
OR
( NVL (poh.global_agreement_flag, 'N') = 'N'
AND ( ( p_vendor_site_sourcing_flag = 'N'
AND (x_vendor_site_id IS NULL OR
poh.vendor_site_id = x_vendor_site_id)
)
OR
( p_vendor_site_sourcing_flag = 'Y'
AND (p_vendor_site_code IS NULL OR
poh.vendor_site_id =
(select pvs.vendor_site_id
from po_vendor_sites pvs
where pvs.vendor_site_code = p_vendor_site_code
and pvs.vendor_id = x_vendor_id))
)
)
)
)
--
AND (x_currency_code IS NULL OR poh.currency_code = x_currency_code)
AND x_sourcing_date >= NVL (poh.start_date, x_sourcing_date - 1)
AND x_sourcing_date <= NVL (poh.end_date, x_sourcing_date + 1)
--
AND pol.item_id = x_item_id AND
(NVL(pol.item_revision, -1) = NVL(x_item_rev, -1) OR
(NVL (p_item_rev_control, 1) = 1 AND x_item_rev IS NULL))
--
--If document is not a GA then the operating units should match
--If document is GA and vendor site sourcing_flag is Y then
--vendor_site_code for current org(as enabled org) should match
--If the document is GA and vendor site sourcing_flag is N then
--current org should be enabled in GA
AND ( ( NVL (poh.global_agreement_flag, 'N') = 'N'
AND (x_multi_org = 'N')
AND poh.org_id = p_org_id
)
--
OR ( NVL (poh.global_agreement_flag, 'N') = 'Y'
AND ( ( p_vendor_site_sourcing_flag = 'Y'
AND EXISTS (
SELECT 'vendor site code matches'
FROM po_ga_org_assignments poga,
po_vendor_sites_all pvsa
WHERE poh.po_header_id = poga.po_header_id
AND poga.organization_id = p_org_id
AND poga.vendor_site_id = pvsa.vendor_site_id
AND pvsa.vendor_site_code =
p_vendor_site_code
AND poga.enabled_flag = 'Y'
AND pvsa.vendor_id = x_vendor_id)
)
OR ( p_vendor_site_sourcing_flag = 'N'
--
AND
(
x_vendor_site_id is null
OR
EXISTS (
SELECT 'vendor site id matches'
FROM po_ga_org_assignments poga
WHERE poh.po_header_id = poga.po_header_id
AND poga.vendor_site_id = x_vendor_site_id
AND poga.enabled_flag = 'Y')
)
AND (x_destination_doc_type = 'STANDARD PO'
OR EXISTS (
SELECT 'enabled org exists'
FROM po_ga_org_assignments poga
WHERE poh.po_header_id = poga.po_header_id
AND poga.organization_id = p_org_id
AND poga.enabled_flag = 'Y'))
--
)
)
)
--
OR x_multi_org = 'Y'
) -- FPI GA
UNION ALL
SELECT poh.po_header_id,
to_number(NULL),
to_number(NULL),
poh.type_lookup_code,
NVL (x_vendor_site_id, poh.vendor_site_id),
NVL (x_vendor_contact_id, poh.vendor_contact_id),
NVL (x_buyer_id, poh.agent_id),
/* Bug 2348331 fixed. swapped the elements in the below
nvl statement in order that the vendor_product_num at
blanket line level takes precedence to that at ASL level.
*/
x_vendor_product_num, --Bug5081434
poh.global_agreement_flag,
poh.org_id,
-- Bug 3361128: also select the UOM on the doc
to_char(NULL), --Bug5081434
3 DocTypeFetchOrder,
2 MatchUom,
NVL (poh.global_agreement_flag, 'N') global_flag,
poh.creation_date creation_date
FROM po_headers_all poh
WHERE ( x_destination_doc_type = 'REQ'
OR x_destination_doc_type = 'REQ_NONCATALOG' --
OR x_destination_doc_type IS NULL
)
AND (( NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y' --
and poh.approved_date is not null)
OR
nvl(poh.approved_flag,'N') = 'Y'
)
AND NVL(poh.cancel_flag,'N') = 'N'
AND NVL(poh.frozen_flag,'N') = 'N'
AND NVL(poh.closed_code, 'OPEN') = 'OPEN'
AND p_return_contract = 'Y'
AND poh.vendor_id = x_vendor_id
AND poh.type_lookup_code = 'CONTRACT'
AND
(
(NVL (poh.global_agreement_flag, 'N') = 'Y')
OR
( NVL (poh.global_agreement_flag, 'N') = 'N'
AND ( ( p_vendor_site_sourcing_flag = 'N'
AND (x_vendor_site_id IS NULL OR
poh.vendor_site_id = x_vendor_site_id)
)
OR
( p_vendor_site_sourcing_flag = 'Y'
AND (p_vendor_site_code IS NULL OR
poh.vendor_site_id =
(select pvs.vendor_site_id
from po_vendor_sites pvs
where pvs.vendor_site_code = p_vendor_site_code
and pvs.vendor_id = x_vendor_id))
)
)
)
)
--
AND (x_currency_code IS NULL OR poh.currency_code = x_currency_code)
AND x_sourcing_date >= NVL (poh.start_date, x_sourcing_date - 1)
AND x_sourcing_date <= NVL (poh.end_date, x_sourcing_date + 1)
AND ( ( NVL (poh.global_agreement_flag, 'N') = 'N'
AND (x_multi_org = 'N')
AND poh.org_id = p_org_id
)
--
OR ( NVL (poh.global_agreement_flag, 'N') = 'Y'
AND ( ( p_vendor_site_sourcing_flag = 'Y'
AND (p_vendor_site_code IS NULL OR
EXISTS (
SELECT 'vendor site code matches'
FROM po_ga_org_assignments poga,
po_vendor_sites_all pvsa
WHERE poh.po_header_id = poga.po_header_id
AND poga.organization_id = p_org_id
AND poga.vendor_site_id = Decode( Nvl (poh.Enable_All_Sites,'N'),'N',pvsa.vendor_site_id,poga.Vendor_Site_Id) -- pvsa.vendor_site_id
AND pvsa.vendor_site_code =
p_vendor_site_code
AND poga.enabled_flag = 'Y'
AND pvsa.vendor_id = x_vendor_id))
)
OR ( p_vendor_site_sourcing_flag = 'N'
--
AND
(
x_vendor_site_id is null
OR
EXISTS (
SELECT 'vendor site id matches'
FROM po_ga_org_assignments poga
WHERE poh.po_header_id = poga.po_header_id
AND poga.vendor_site_id = decode( Nvl (poh.Enable_All_Sites,'N'),'Y',poga.Vendor_Site_Id,x_vendor_site_id) --< R12 GCPA ER>
AND poga.enabled_flag = 'Y')
)
AND (x_destination_doc_type = 'STANDARD PO'
OR EXISTS (
SELECT 'enabled org exists'
FROM po_ga_org_assignments poga
WHERE poh.po_header_id = poga.po_header_id
AND poga.organization_id = p_org_id
AND poga.enabled_flag = 'Y'))
--
)
)
)
--
OR x_multi_org = 'Y'
) -- FPI GA
ORDER BY
DocTypeFetchOrder Asc,
MatchUom Asc,
global_flag Asc,
creation_date DESC;
SELECT poh.po_header_id,
NULL po_line_id,
NULL line_num, -- Only Contracts are returned
poh.type_lookup_code,
NVL (x_vendor_site_id, poh.vendor_site_id),
NVL (x_vendor_contact_id, poh.vendor_contact_id),
NVL (x_buyer_id, poh.agent_id),
/* Bug 2348331 fixed. swapped the elements in the below
nvl statement in order that the vendor_product_num at
blanket line level takes precedence to that at ASL level.
*/
x_vendor_product_num,
poh.global_agreement_flag,
poh.org_id,
NULL unit_meas_lookup_code
FROM po_headers_all poh --CTO changes FPH
WHERE
( x_destination_doc_type = 'REQ'
OR x_destination_doc_type = 'REQ_NONCATALOG' --
OR x_destination_doc_type IS NULL
--
)
AND (
( poh.type_lookup_code = 'CONTRACT'
AND (( NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y' --
and poh.approved_date is not null)
OR
nvl(poh.approved_flag,'N') = 'Y'
)
AND NVL(poh.cancel_flag,'N') = 'N'
AND NVL(poh.frozen_flag,'N') = 'N'
AND NVL(poh.closed_code, 'OPEN') = 'OPEN'
AND p_return_contract = 'Y' --
)
)
AND poh.vendor_id = x_vendor_id
--
--This clause returns rows if document is GA or
--EITHER vendor_site_sourcing_flag is N and site_ids match
--OR vendor_site_sourcing_flag is Y and site codes match
AND
(
(NVL (poh.global_agreement_flag, 'N') = 'Y')
OR
( NVL (poh.global_agreement_flag, 'N') = 'N'
AND ( ( p_vendor_site_sourcing_flag = 'N'
AND (x_vendor_site_id IS NULL OR
poh.vendor_site_id = x_vendor_site_id)
)
)
)
)
--
AND (x_currency_code IS NULL OR poh.currency_code = x_currency_code)
AND x_sourcing_date >= NVL (poh.start_date, x_sourcing_date - 1)
AND x_sourcing_date <= NVL (poh.end_date, x_sourcing_date + 1)
--If document is not a GA then the operating units should match
--If document is GA and vendor site sourcing_flag is Y then
--vendor_site_code for current org(as enabled org) should match
--If the document is GA and vendor site sourcing_flag is N then
--current org should be enabled in GA
AND ( ( NVL (poh.global_agreement_flag, 'N') = 'N'
AND (x_multi_org = 'N')
AND NVL (poh.org_id, -1) = NVL (p_org_id, -1)
)
--
OR ( NVL (poh.global_agreement_flag, 'N') = 'Y'
AND (
( p_vendor_site_sourcing_flag = 'N'
--
AND
(
x_vendor_site_id is null
OR
EXISTS (
SELECT 'vendor site id matches'
FROM po_ga_org_assignments poga
WHERE poh.po_header_id = poga.po_header_id
AND poga.vendor_site_id = decode( Nvl (poh.Enable_All_Sites,'N'),'Y',poga.Vendor_Site_Id,x_vendor_site_id) --< R12 GCPA ER>
AND poga.enabled_flag = 'Y')
)
--
)
)
)
--
OR x_multi_org = 'Y'
) -- FPI GA
ORDER BY
--
decode(poh.type_lookup_code, 'BLANKET', 1, 'QUOTATION', 2, 'CONTRACT', 3) ASC,
--
NVL (poh.global_agreement_flag, 'N') ASC,
poh.creation_date DESC; -- Bug# 1560250
SELECT pasl.asl_id, paa.using_organization_id,
pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
paa.consigned_from_supplier_flag, paa.enable_vmi_flag
FROM po_approved_supplier_lis_val_v pasl,
po_asl_attributes paa,
po_asl_status_rules_v pasr,
po_vendor_sites_all pvs
WHERE pasl.item_id = p_item_id --
AND pasl.vendor_id = p_vendor_id
AND pasl.using_organization_id in (-1, p_using_organization_id) --
AND pasl.asl_id = paa.asl_id
AND pasr.business_rule = '2_SOURCING'
AND pasr.allow_action_flag ='Y'
AND pasr.status_id = pasl.asl_status_id
AND paa.using_organization_id = p_using_organization_id
AND ( (pasl.vendor_site_id IS NULL AND p_vendor_site_code IS NULL)
OR ( pasl.vendor_site_id = pvs.vendor_site_id
AND pvs.vendor_site_code = p_vendor_site_code
AND nvl(pvs.org_id,-99) = nvl(p_org_id, -99)
AND pvs.vendor_id = p_vendor_id
)
)
ORDER BY pasl.vendor_site_id ASC;
SELECT pasl.asl_id, paa.using_organization_id,
pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
paa.consigned_from_supplier_flag, paa.enable_vmi_flag,
pad.sequence_num
FROM po_approved_supplier_lis_val_v pasl,
po_asl_attributes paa,
po_asl_status_rules_v pasr,
po_asl_documents pad,
po_headers_all poh,
po_lines_all pol
WHERE pasl.item_id = p_item_id --
AND pasl.vendor_id = p_vendor_id
AND pasl.using_organization_id in (-1, p_using_organization_id) --
AND pasl.asl_id = paa.asl_id
AND pasr.business_rule = '2_SOURCING'
AND pasr.allow_action_flag = 'Y'
AND pasr.status_id = pasl.asl_status_id
AND paa.using_organization_id = p_using_organization_id
AND pad.asl_id = pasl.asl_id
AND pad.document_header_id = poh.po_header_id
AND pol.po_line_id (+) = pad.document_line_id --
AND (( poh.type_lookup_code = 'BLANKET'
AND poh.approved_flag = 'Y'
AND NVL (poh.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED', 'CLOSED')
AND NVL (pol.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED', 'CLOSED')
AND NVL (poh.cancel_flag, 'N') = 'N'
AND NVL (poh.frozen_flag, 'N') = 'N'
AND TRUNC (NVL (pol.expiration_date, p_sourcing_date)) >=
p_sourcing_date
AND NVL (pol.cancel_flag, 'N') = 'N'
)
--
OR ( poh.type_lookup_code = 'CONTRACT'
AND (( NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y' --
and poh.approved_date is not null)
OR
nvl(poh.approved_flag,'N') = 'Y'
)
AND NVL(poh.cancel_flag,'N') = 'N'
AND NVL(poh.frozen_flag,'N') = 'N'
AND NVL(poh.closed_code, 'OPEN') = 'OPEN'
)
)
--
AND (p_currency_code IS NULL OR poh.currency_code = p_currency_code
)
AND p_sourcing_date >= NVL (poh.start_date, p_sourcing_date - 1)
AND p_sourcing_date <= NVL (poh.end_date, p_sourcing_date + 1)
--
AND (poh.type_lookup_code = 'CONTRACT' OR
(NVL(pol.item_revision, -1) = NVL(p_item_rev, -1) OR
(NVL (p_item_rev_control, 1) = 1 AND p_item_rev IS NULL)))
--
AND ((pasl.vendor_site_id IS NULL AND p_vendor_site_code IS NULL)
OR EXISTS (
SELECT 'vendor site code matches ASL'
FROM po_vendor_sites_all pvs
WHERE pasl.vendor_site_id = decode(nvl(poh.Enable_all_sites,'N'),'N',pvs.vendor_site_id,pasl.vendor_site_id) --
AND pvs.vendor_site_code = p_vendor_site_code
AND pvs.vendor_id = p_vendor_id)
)
AND ( NVL (poh.global_agreement_flag, 'N') = 'Y'
AND EXISTS (
SELECT 'vendor site code matches GA'
FROM po_ga_org_assignments poga,
po_vendor_sites_all pvs
WHERE poh.po_header_id = poga.po_header_id
AND poga.organization_id = p_org_id
AND poga.enabled_flag = 'Y'
AND pvs.vendor_site_id = decode( Nvl (poh.Enable_All_Sites,'N'),'N',poga.Vendor_Site_Id,pvs.vendor_site_id) --< R12 GCPA ER>
AND pvs.vendor_site_code = p_vendor_site_code
AND pvs.vendor_id = p_vendor_id)
)
ORDER BY poh.creation_date DESC;
SELECT pasl.asl_id, paa.using_organization_id,
pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
paa.consigned_from_supplier_flag, paa.enable_vmi_flag
FROM po_approved_supplier_lis_val_v pasl,
po_asl_attributes paa,
po_asl_status_rules_v pasr,
po_vendor_sites_all pvs
WHERE pasl.category_id = p_category_id --
AND pasl.vendor_id = p_vendor_id
AND pasl.using_organization_id in (-1, p_using_organization_id) --
AND pasl.asl_id = paa.asl_id
AND pasr.business_rule = '2_SOURCING'
AND pasr.allow_action_flag ='Y'
AND pasr.status_id = pasl.asl_status_id
AND paa.using_organization_id = p_using_organization_id
AND ( (pasl.vendor_site_id IS NULL AND p_vendor_site_code IS NULL)
OR ( pasl.vendor_site_id = pvs.vendor_site_id
AND pvs.vendor_site_code = p_vendor_site_code
AND nvl(pvs.org_id,-99) = nvl(p_org_id, -99)
AND pvs.vendor_id = p_vendor_id
)
)
ORDER BY pasl.vendor_site_id ASC;
SELECT pasl.asl_id, paa.using_organization_id,
pasl.primary_vendor_item, paa.purchasing_unit_of_measure,
paa.consigned_from_supplier_flag, paa.enable_vmi_flag,
pad.sequence_num
FROM po_approved_supplier_lis_val_v pasl,
po_asl_attributes paa,
po_asl_status_rules_v pasr,
po_asl_documents pad,
po_headers_all poh,
po_lines_all pol
WHERE pasl.category_id = p_category_id --
AND pasl.vendor_id = p_vendor_id
AND pasl.using_organization_id in (-1, p_using_organization_id) --
AND pasl.asl_id = paa.asl_id
AND pasr.business_rule = '2_SOURCING'
AND pasr.allow_action_flag = 'Y'
AND pasr.status_id = pasl.asl_status_id
AND paa.using_organization_id = p_using_organization_id
AND pad.asl_id = pasl.asl_id
AND pad.document_header_id = poh.po_header_id
AND pol.po_line_id (+) = pad.document_line_id --
AND (( poh.type_lookup_code = 'BLANKET'
AND poh.approved_flag = 'Y'
AND NVL (poh.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED', 'CLOSED')
AND NVL (pol.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED', 'CLOSED')
AND NVL (poh.cancel_flag, 'N') = 'N'
AND NVL (poh.frozen_flag, 'N') = 'N'
AND TRUNC (NVL (pol.expiration_date, p_sourcing_date)) >=
p_sourcing_date
AND NVL (pol.cancel_flag, 'N') = 'N'
)
--
OR ( poh.type_lookup_code = 'CONTRACT'
AND (( NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y' --
and poh.approved_date is not null)
OR
nvl(poh.approved_flag,'N') = 'Y'
)
AND NVL(poh.cancel_flag,'N') = 'N'
AND NVL(poh.frozen_flag,'N') = 'N'
AND NVL(poh.closed_code, 'OPEN') = 'OPEN'
)
)
--
AND (p_currency_code IS NULL OR poh.currency_code = p_currency_code
)
AND p_sourcing_date >= NVL (poh.start_date, p_sourcing_date - 1)
AND p_sourcing_date <= NVL (poh.end_date, p_sourcing_date + 1)
--
AND (poh.type_lookup_code = 'CONTRACT' OR
(NVL(pol.item_revision, -1) = NVL(p_item_rev, -1) OR
(NVL (p_item_rev_control, 1) = 1 AND p_item_rev IS NULL)))
--
AND ((pasl.vendor_site_id IS NULL AND p_vendor_site_code IS NULL)
OR EXISTS (
SELECT 'vendor site code matches ASL'
FROM po_vendor_sites_all pvs
WHERE pasl.vendor_site_id = decode(nvl(poh.Enable_All_Sites,'N'),'N',pvs.vendor_site_id,pasl.vendor_site_id) --
AND pvs.vendor_site_code = p_vendor_site_code
AND pvs.vendor_id = p_vendor_id)
)
AND ( NVL (poh.global_agreement_flag, 'N') = 'Y'
AND EXISTS (
SELECT 'vendor site code matches GA'
FROM po_ga_org_assignments poga,
po_vendor_sites_all pvs
WHERE poh.po_header_id = poga.po_header_id
AND poga.organization_id = p_org_id
AND poga.enabled_flag = 'Y'
AND pvs.vendor_site_id = decode( Nvl (poh.Enable_All_Sites,'N'),'Y',pvs.vendor_site_id, poga.Vendor_Site_Id) --< R12 GCPA ER>
AND pvs.vendor_site_code = p_vendor_site_code
AND pvs.vendor_id = p_vendor_id)
)
ORDER BY poh.creation_date DESC;
UPDATE PO_REQ_DIST_INTERFACE prdi
SET prdi.quantity = round((prdi.quantity *p_uom_conversion_rate)*
(1 + (x_quantity - l_temp_quantity) / l_temp_quantity)
, 18)
WHERE prdi.dist_sequence_id = p_req_dist_sequence_id
AND prdi.quantity is not null
AND prdi.request_id = p_request_id;
UPDATE PO_REQ_DIST_INTERFACE prdi
SET prdi.quantity = round((prdi.quantity + l_adjust), 18)
WHERE prdi.request_id = p_request_id
AND prdi.quantity is not null
AND prdi.dist_sequence_id = p_req_dist_sequence_id
AND distribution_number =
(SELECT MAX(distribution_number)
FROM PO_REQ_DIST_INTERFACE
WHERE prdi.request_id = p_request_id
AND prdi.dist_sequence_id = p_req_dist_sequence_id);
SELECT rowid,
item_id,
category_id, -- Bug 5524728
destination_organization_id,
destination_subinventory,
nvl(need_by_date, sysdate),
item_revision,
currency_code,
quantity,
rate_type,
suggested_vendor_id,
suggested_vendor_name, --Bug# 1813740
suggested_vendor_site_id,
suggested_vendor_site,
suggested_vendor_item_num,
autosource_flag,
uom_code,
unit_of_measure,
req_dist_sequence_id,
interface_source_code
--
,line_type_id
,destination_type_code
--
FROM po_requisitions_interface
WHERE autosource_flag in ('Y', 'P')
AND source_type_code = 'VENDOR'
AND item_id IS NOT NULL
AND request_id = x_request_id;
SELECT rowid,
decode(item_id, NULL, category_id, NULL),
item_id,
destination_subinventory,
destination_organization_id,
source_organization_id,
source_subinventory,
nvl(need_by_date, sysdate),
quantity,
unit_of_measure,
req_dist_sequence_id,
interface_source_code
--
,line_type_id
,destination_type_code
--
FROM po_requisitions_interface
WHERE autosource_flag in ('Y', 'P')
AND source_type_code = 'INVENTORY'
AND destination_organization_id IS NOT NULL
AND request_id = x_request_id;
SELECT NEGOTIATED_BY_PREPARER_FLAG INTO l_negotiated_by_preparer_flag FROM PO_LINES_ALL
WHERE
PO_HEADER_ID = x_document_header_id AND LINE_NUM = x_document_line_num;
SELECT inventory_organization_id
INTO x_organization_id
FROM financials_system_parameters;
SELECT msi.buyer_id
INTO x_buyer_id
FROM mtl_system_items msi
WHERE msi.inventory_item_id = x_item_id
AND msi.organization_id = x_organization_id;
SELECT 'Y'
INTO l_buyer_ok
FROM per_people_f ppf
WHERE x_buyer_id = ppf.person_id
AND trunc(sysdate) between ppf.effective_start_date
AND NVL(ppf.effective_end_date, sysdate +1);
SELECT msi.primary_unit_of_measure, msi.rounding_factor
INTO l_primary_uom, l_rounding_factor
FROM mtl_system_items msi
WHERE msi.inventory_item_id = x_item_id
AND msi.organization_id = x_organization_id;
SELECT min_order_qty, fixed_lot_multiple, purchasing_unit_of_measure
INTO l_min_ord_qty, l_fixed_lot_multiple, l_asl_purchasing_uom
FROM PO_ASL_ATTRIBUTES
WHERE ASL_ID = l_asl_id;
UPDATE po_requisitions_interface
SET suggested_vendor_id = nvl(x_vendor_id,suggested_vendor_id),
suggested_vendor_name = decode(x_vendor_id, null , suggested_vendor_name, x_vendor_name),
suggested_vendor_site_id = nvl(x_vendor_site_id,suggested_vendor_site_id),
suggested_buyer_id = nvl(suggested_buyer_id, x_buyer_id),
autosource_doc_header_id = x_document_header_id,
autosource_doc_line_num = x_document_line_num,
document_type_code = x_document_type_code,
-- Bug 4523369 START
-- If autosourcing did not return a vendor site, keep the
-- current vendor contact.
suggested_vendor_contact_id =
decode(x_vendor_site_id,
null, suggested_vendor_contact_id,
x_vendor_contact_id),
-- Bug 4523369 END
suggested_vendor_item_num =
nvl(suggested_vendor_item_num, x_vendor_product_num),
unit_of_measure = nvl(x_purchasing_uom,nvl(x_unit_of_measure,unit_of_measure)),
quantity = x_quantity, --
negotiated_by_preparer_flag = l_negotiated_by_preparer_flag -- DBI FPJ
WHERE rowid = x_rowid;
UPDATE po_Requisitions_InterFace po_Requisitions_InterFace
SET po_Requisitions_InterFace.Prevent_Encumbrance_Flag = 'Y'
WHERE po_Requisitions_InterFace.AutoSource_Doc_Header_Id IS NOT NULL
AND po_Requisitions_Interface.request_id = x_request_id
AND EXISTS (SELECT 'BPA Encumbered'
FROM po_Distributions_All d,
po_Headers_All h
WHERE h.po_Header_Id = po_Requisitions_InterFace.AutoSource_Doc_Header_Id
AND h.po_Header_Id = d.po_Header_Id
AND h.Type_LookUp_Code = 'BLANKET'
AND d.Line_Location_Id IS NULL
AND d.po_Release_Id IS NULL
AND Nvl(d.Encumbered_Flag,'N') = 'Y');
SELECT enforce_full_lot_quantities
INTO l_enforce_full_lot_qty
FROM po_system_parameters;
SELECT msi.primary_unit_of_measure, msi.rounding_factor, msi.unit_of_issue
INTO l_primary_uom, l_rounding_factor, l_unit_of_issue
FROM mtl_system_items msi
WHERE msi.inventory_item_id = x_item_id
AND msi.organization_id = x_source_organization_id;
SELECT mssi.fixed_lot_multiple, mssi.minimum_order_quantity
INTO l_fixed_lot_multiple, l_min_ord_qty
FROM MTL_ITEM_SUB_INVENTORIES mssi
WHERE mssi.secondary_inventory = x_source_subinventory
AND mssi.inventory_item_id = x_item_id
AND mssi.organization_id = x_source_organization_id;
SELECT nvl(l_fixed_lot_multiple,msi.fixed_lot_multiplier),
nvl(l_min_ord_qty, msi.minimum_order_quantity)
INTO l_fixed_lot_multiple, l_min_ord_qty
FROM MTL_SYSTEM_ITEMS msi
WHERE msi.inventory_item_id = x_item_id
AND msi.organization_id = x_source_organization_id;
UPDATE po_requisitions_interface
SET source_organization_id = x_source_organization_id,
source_subinventory = x_source_subinventory,
suggested_buyer_id = nvl(suggested_buyer_id, x_buyer_id),
quantity = x_quantity,
unit_of_measure = decode(nvl(l_enforce_full_lot_qty, 'NONE'),
'NONE',x_unit_of_measure,
nvl(l_unit_of_issue,x_unit_of_measure))
WHERE rowid = x_rowid;
SELECT pasl.vendor_id,
pasl.vendor_site_id,
pasl.asl_id,
pasl.primary_vendor_item,
paa.purchasing_unit_of_measure
FROM po_approved_supplier_lis_val_v pasl,
po_asl_attributes paa,
po_asl_status_rules_v pasr
WHERE pasl.item_id = x_item_id
AND (pasl.using_organization_id IN
(-1, x_using_organization_id))
AND pasl.asl_id = paa.asl_id
AND pasr.business_rule like '2_SOURCING'
AND pasr.allow_action_flag like 'Y'
AND pasr.status_id = pasl.asl_status_id
AND paa.using_organization_id =
(SELECT max(paa2.using_organization_id)
FROM po_asl_attributes paa2
WHERE paa2.asl_id = pasl.asl_id
AND (pasl.using_organization_id IN
(-1,x_using_organization_id)))
ORDER BY pasl.using_organization_id DESC;
x_vendor_details.DELETE;
select poh.type_lookup_code
into x_type_lookup_code
from po_headers_all poh
where poh.po_header_id = x_document_header_id;
SELECT 'Site is within OU'
INTO l_vendor_site_status
FROM po_vendor_sites_all
WHERE vendor_site_id = p_vendor_site_id
AND org_id = p_ou_id;
SELECT 'Valid supplier site'
INTO l_vendor_site_status
FROM po_vendor_sites_all
WHERE vendor_site_id = px_vendor_site_id_list(i)
AND (purchasing_site_flag = 'Y' OR rfq_only_site_flag = 'Y')
AND sysdate <= nvl(inactive_date, sysdate);
SELECT pasl.vendor_id,
pasl.vendor_site_id,
pasl.asl_id
FROM po_approved_supplier_list pasl,
po_asl_status_rules pasr,
po_vendors pov
WHERE pasl.category_id = p_category_id
AND pasl.item_id IS NULL -- as part of Bug# 3379053: For commodity based ASL's,
-- the item MUST be NULL
-- Bug# 3379053: Use destination inv org instead of the default inv org of the ROU.
AND (pasl.using_organization_id = p_dest_organization_id
OR pasl.using_organization_id = -1)
AND pasr.status_id = pasl.asl_status_id
AND pasr.business_rule like '2_SOURCING'
AND pasr.allow_action_flag like 'Y'
AND nvl(pasl.disable_flag,'N') = 'N'
-- Supplier validations (Bug# 3361784)
AND pov.vendor_id = pasl.vendor_id -- Join
AND trunc(sysdate) >= trunc(nvl(pov.start_date_active, sysdate))
AND trunc(sysdate) < trunc(nvl(pov.end_date_active, sysdate+1)) -- Bug# 3432045: Exclude end_date_active
AND pov.enabled_flag = 'Y'
AND nvl(pov.hold_flag, 'N') = 'N'
-- Bug# 3379053: Supplier site validations moved later in the flow
ORDER BY pasl.vendor_id ASC, -- Bug# 3379053: To filter out duplicates, the supplier
pasl.vendor_site_id ASC, -- and supplier-sites must be grouped together.
pasl.using_organization_id DESC; -- And Local ASL's must come above Global ASL's.
SELECT poh.vendor_contact_id,
pad.document_header_id,
pad.document_line_id,
pol.line_num,
pad.document_type_code,
nvl(pol.allow_price_override_flag,'N'),
pol.not_to_exceed_price,
pol.unit_meas_lookup_code
FROM po_asl_documents pad,
po_headers_all poh,
po_lines_all pol
WHERE pad.asl_id = l_asl_id
AND pad.document_header_id = poh.po_header_id
--
AND pol.po_line_id (+) = pad.document_line_id
AND (poh.type_lookup_code = 'CONTRACT' OR
(pol.job_id = p_job_id AND
pol.category_id = p_category_id AND
pol.line_type_id = p_line_type_id))
--
AND ( (poh.type_lookup_code = 'CONTRACT'
AND nvl(poh.global_agreement_flag,'N') = 'N') -- Bug 3262136
OR exists (select 'site in POU'
from po_ga_org_assignments poga,
po_vendor_sites_all povs
where poh.po_header_id = poga.po_header_id
and povs.vendor_site_id = l_vendor_site_id
and povs.org_id = poga.purchasing_org_id
and poga.vendor_site_id = l_vendor_site_id
and poga.organization_id = l_org_id
and poga.enabled_flag = 'Y') )
AND ((poh.type_lookup_code = 'BLANKET'
AND poh.approved_flag = 'Y'
AND nvl(poh.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED','CLOSED')
AND nvl(pol.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED','CLOSED')
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(poh.frozen_flag,'N') = 'N'
AND trunc(nvl(pol.expiration_date, sysdate))
>= trunc(sysdate)
AND nvl(pol.cancel_flag,'N') = 'N')
--
OR ( poh.type_lookup_code = 'CONTRACT'
AND poh.approved_flag = 'Y'
AND NVL(poh.cancel_flag,'N') = 'N'
AND NVL(poh.frozen_flag,'N') = 'N'
AND NVL(poh.closed_code, 'OPEN') = 'OPEN'
)
)
--
AND sysdate >= nvl(poh.start_date, sysdate)
AND sysdate <= nvl(poh.end_date, sysdate)
AND ( (poh.type_lookup_code = 'CONTRACT'
AND nvl(poh.global_agreement_flag,'N') = 'N') OR -- Bug 3262136
(nvl(poh.global_agreement_flag,'N') = 'Y'
AND EXISTS (SELECT 'enabled orgs'
FROM po_ga_org_assignments poga
WHERE poh.po_header_id = poga.po_header_id
AND poga.organization_id = l_org_id
AND poga.enabled_flag = 'Y'
) )
)
ORDER BY pad.sequence_num;
SELECT poh.vendor_contact_id,
poh.po_header_id,
pol.po_line_id,
pol.line_num,
poh.type_lookup_code,
nvl(pol.allow_price_override_flag,'N'),
pol.not_to_exceed_price,
pol.unit_meas_lookup_code
FROM po_headers_all poh,
po_lines_all pol
WHERE poh.vendor_id = l_vendor_id
AND poh.type_lookup_code IN ('BLANKET','CONTRACT')
AND ( ( poh.type_lookup_code = 'CONTRACT'
AND nvl(poh.global_agreement_flag,'N') = 'N' -- Bug 3262136
-- As part of Bug# 3379053: Local Contract must belong to ROU
AND poh.org_id = l_org_id
-- As part of Bug# 3379053: Vendor Site on Local Contract must belong to ROU
AND poh.vendor_site_id = l_vendor_site_id
AND EXISTS -- Bug# 3379053
(SELECT 'Site must be in ROU for local contracts'
FROM po_vendor_sites_all povs
WHERE povs.vendor_site_id = l_vendor_site_id
AND povs.org_id = l_org_id)
)
OR
EXISTS (SELECT 'site in POU'
FROM po_ga_org_assignments poga,
po_vendor_sites_all povs
WHERE poh.po_header_id = poga.po_header_id
AND povs.vendor_site_id = l_vendor_site_id
AND povs.org_id = poga.purchasing_org_id
AND poga.vendor_site_id = l_vendor_site_id
AND poga.organization_id = l_org_id
AND poga.enabled_flag = 'Y') )
--
AND pol.po_header_id (+) = poh.po_header_id
AND (poh.type_lookup_code = 'CONTRACT' OR
(pol.job_id = p_job_id AND
pol.category_id = p_category_id AND
pol.line_type_id = p_line_type_id))
--
AND ((poh.type_lookup_code = 'BLANKET'
AND poh.approved_flag = 'Y'
AND nvl(poh.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED','CLOSED')
AND nvl(pol.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED','CLOSED')
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(poh.frozen_flag,'N') = 'N'
AND trunc(nvl(pol.expiration_date, sysdate))
>= trunc(sysdate)
AND nvl(pol.cancel_flag,'N') = 'N')
--
OR ( poh.type_lookup_code = 'CONTRACT'
AND (( NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y' --
and poh.approved_date is not null)
OR
nvl(poh.approved_flag,'N') = 'Y'
)
AND NVL(poh.cancel_flag,'N') = 'N'
AND NVL(poh.frozen_flag,'N') = 'N'
AND NVL(poh.closed_code, 'OPEN') = 'OPEN'
AND l_use_contract = 'Y' --
)
)
--
AND sysdate >= nvl(poh.start_date, sysdate)
AND sysdate <= nvl(poh.end_date, sysdate)
AND ( (poh.type_lookup_code = 'CONTRACT'
AND nvl(poh.global_agreement_flag,'N') = 'N') OR -- Bug 3262136
(nvl(poh.global_agreement_flag,'N') = 'Y'
AND EXISTS (SELECT 'enabled orgs'
FROM po_ga_org_assignments poga
WHERE poh.po_header_id = poga.po_header_id
AND poga.organization_id = l_org_id
AND poga.enabled_flag = 'Y'
) )
)
ORDER BY --
decode(poh.type_lookup_code, 'BLANKET', 1, 'QUOTATION', 2, 'CONTRACT', 3) ASC,
NVL (poh.global_agreement_flag, 'N') ASC,
poh.creation_date DESC;
SELECT org_id,
inventory_organization_id
INTO l_org_id,
l_using_organization_id
FROM financials_system_parameters;
SELECT order_type_lookup_code
INTO l_order_type_lookup_code
FROM po_line_types_b
WHERE line_type_id = p_line_type_id;
SELECT from_header_id
, from_line_id
, from_line_location_id
INTO x_from_header_id
, x_from_line_id
, x_from_line_location_id
FROM po_lines_all
WHERE po_line_id = p_po_line_id;
SELECT poh.currency_code ,
pol.amount
INTO l_currency_code ,
l_currency_amount
FROM po_headers_all poh,
po_lines_all pol
WHERE poh.po_header_id = pol.po_header_id
AND poh.po_header_id = p_source_document_header_id
AND pol.po_line_id = p_source_document_line_id;
SELECT set_of_books_id
INTO l_sob_id
FROM financials_system_parameters;
SELECT default_rate_type
INTO l_rate_type
FROM po_system_parameters;
SELECT nvl(FND.extended_precision,5)
INTO l_base_curr_ext_precision
FROM FND_CURRENCIES FND,
FINANCIALS_SYSTEM_PARAMETERS FSP,
GL_SETS_OF_BOOKS GSB
WHERE FSP.set_of_books_id = GSB.set_of_books_id AND
FND.currency_code = GSB.currency_code;
SELECT nvl (use_contract_for_sourcing_flag, 'N'),
nvl (include_noncatalog_flag, 'N')
INTO l_use_contract_for_sourcing,
l_include_noncatalog_flag
FROM PO_DOCUMENT_TYPES_B
WHERE document_type_code = p_document_type_code
AND document_subtype = p_document_subtype;