The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_id
INTO x_org_id
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT org_id
INTO x_org_id
FROM po_system_parameters;
SELECT global_agreement_flag
INTO l_global_agreement_flag -- all documents held in PO_HEADERS
FROM po_headers -- are "owned" by the current org
WHERE po_header_id = p_po_header_id;
SELECT name
INTO l_name
FROM hr_all_organization_units_tl
WHERE organization_id = p_org_id
AND language = USERENV('LANG');
SELECT global_agreement_flag
INTO l_global_agreement_flag
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT pgoa.enabled_flag
INTO l_enabled_flag
FROM po_ga_org_assignments pgoa,
po_system_parameters psp
WHERE pgoa.po_header_id = p_po_header_id -- input GA ID
AND pgoa.organization_id = psp.org_id; -- current OU
SELECT 'Y'
INTO l_enabled_orgs_exist
FROM po_ga_org_assignments
WHERE po_header_id = p_po_header_id -- for the current GA
AND organization_id <> p_owning_org_id -- for OUs besides Owning OU
AND enabled_flag = 'Y' -- that are enabled
HAVING count(*) > 0;
SELECT count('Standard POs referencing GA line')
INTO l_count
FROM po_lines_all
WHERE from_line_id = p_po_line_id;
SELECT nvl(poh.global_agreement_flag, 'N'),
decode(poh.global_agreement_flag, -- only return values
'Y', poh.org_id, -- if Global Agreement.
NULL ), -- else, return NULL
decode(poh.global_agreement_flag,
'Y', hrou.name,
NULL )
INTO x_global_agreement_flag,
x_owning_org_id,
x_owning_org_name
FROM po_headers_all poh,
hr_all_organization_units_tl hrou
WHERE poh.po_header_id = p_po_header_id
AND hrou.organization_id = poh.org_id
AND hrou.language = USERENV('LANG');
SELECT poh.end_date,
pol.expiration_date
INTO l_header_end_date,
l_line_expiration_date
FROM po_headers_all poh,
po_lines_all pol
WHERE poh.po_header_id = pol.po_header_id -- JOIN
AND pol.po_line_id = p_po_line_id;
SELECT count('Line exists with following conditions.')
INTO l_count
FROM po_headers_all poh,
po_lines_all pol
WHERE
nvl(poh.cancel_flag, 'N') = 'N'
AND nvl(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND nvl(poh.approved_flag, 'N') = 'Y'
AND nvl(poh.user_hold_flag,'N') = 'N'
AND nvl(pol.cancel_flag, 'N') = 'N'
AND nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND nvl(pol.user_hold_flag,'N') = 'N'
AND poh.po_header_id = pol.po_header_id -- JOIN
AND pol.po_line_id = p_po_line_id;
SELECT start_date,
end_date
INTO l_start_date,
l_end_date
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT
ITEMS1.purchasing_enabled_flag,
ITEMS1.outside_operation_flag,
UOM1.uom_class,
UOM2.uom_class
INTO
l_purchasable_flag,
l_osp_flag,
l_uom_class,
l_valid_uom_class
FROM
financials_system_params_all FSP1,
financials_system_params_all FSP2, -- valid org
mtl_system_items_b ITEMS1,
mtl_system_items_b ITEMS2, -- valid org
mtl_units_of_measure_tl UOM1,
mtl_units_of_measure_tl UOM2 -- valid org
WHERE
FSP1.org_id = p_org_id
AND ITEMS1.inventory_item_id = p_item_id
AND ITEMS1.organization_id = FSP1.inventory_organization_id
AND UOM1.uom_code = ITEMS1.primary_uom_code
AND UOM1.language = USERENV('LANG')
AND FSP2.org_id = p_valid_org_id
AND ITEMS2.inventory_item_id = p_item_id
AND ITEMS2.organization_id = FSP2.inventory_organization_id
AND UOM2.uom_code = ITEMS2.primary_uom_code
AND UOM2.language = USERENV('LANG');
SELECT FSP.set_of_books_id,
OOD.set_of_books_id
INTO l_current_sob,
l_ga_sob
FROM financials_system_parameters FSP,
org_organization_definitions OOD
WHERE OOD.organization_id = p_ship_to_org_id;
SELECT pgoa.vendor_site_id
INTO x_vendor_site_id
FROM po_ga_org_assignments pgoa,
po_system_parameters psp
WHERE pgoa.po_header_id = p_po_header_id
AND pgoa.organization_id = psp.org_id
AND pgoa.enabled_flag = 'Y'; --
SELECT currency_code
INTO x_currency_code
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT set_of_books_id
INTO l_set_of_books_id
FROM financials_system_parameters;
SELECT default_rate_type
INTO x_rate_type
FROM po_system_parameters;
SELECT currency_code
INTO l_currency_code
FROM po_headers_all
WHERE po_header_id = p_po_header_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;
fnd_attached_documents2_pkg.delete_attachments('PO_IN_GA_LINES',
p_po_line_id,
'', '', '', '', 'N', 'Y');
INSERT INTO fnd_attached_documents(
ATTACHED_DOCUMENT_ID,
DOCUMENT_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SEQ_NUM,
ENTITY_NAME,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
AUTOMATICALLY_ADDED_FLAG,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
COLUMN1,
APP_SOURCE_VERSION,
CATEGORY_ID,
STATUS)
SELECT fnd_attached_documents_s.nextval,
fad.DOCUMENT_ID,
fad.CREATION_DATE,
fad.CREATED_BY,
fad.LAST_UPDATE_DATE,
fad.LAST_UPDATED_BY,
fad.LAST_UPDATE_LOGIN,
SEQ_NUM,
p_to_entity_name, --entity_name
p_to_pk1_value, --pk1 value
p_to_pk2_value, --PK2_VALUE,
p_to_pk3_value, --PK3_VALUE,
p_to_pk4_value, --PK4_VALUE,
p_to_pk5_value, --PK5_VALUE,
nvl(p_AUTOMATICALLY_ADDED_FLAG,AUTOMATICALLY_ADDED_FLAG),
fad.PROGRAM_APPLICATION_ID,
fad.PROGRAM_ID,
fad.PROGRAM_UPDATE_DATE,
fad.REQUEST_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
COLUMN1,
fad.APP_SOURCE_VERSION,
fad.CATEGORY_ID,
fad.STATUS
FROM fnd_attached_documents fad, fnd_documents fd, financials_system_parameters fsp
WHERE entity_name = p_from_entity_name
AND pk1_value = p_from_pk1_value
AND (p_from_pk2_value IS NULL
OR p_from_pk2_value = pk2_value)
AND (p_from_pk3_value IS NULL
OR p_from_pk3_value = pk3_value)
AND (p_from_pk4_value IS NULL
OR p_from_pk4_value = pk4_value)
AND (p_from_pk5_value IS NULL
OR p_from_pk5_value = pk5_value)
--
AND fad.document_id = fd.document_id
AND (fd.publish_flag = 'Y'
--Security level is Organization
OR (fd.security_type = 1 AND fd.security_id = fsp.org_id)
--Security level is Set Of Books
OR (fd.security_type = 2 AND fd.security_id = fsp.set_of_books_id)
--Security level is NONE
OR (fd.security_type = 4)
);
SELECT 'Y'
INTO l_is_enabled
FROM po_headers_all POH
WHERE POH.po_header_id = p_po_header_id
AND EXISTS (SELECT 1
FROM po_ga_org_assignments PGOA
WHERE PGOA.po_header_id = p_po_header_id
AND PGOA.vendor_site_id = p_vendor_site_id
AND PGOA.enabled_flag = 'Y');
SELECT NVL(POH.global_agreement_flag, 'N')
INTO l_global
FROM po_headers_all POH
WHERE POH.po_header_id = p_po_header_id
AND POH.type_lookup_code = p_type_lookup_code;
SELECT pgoa.purchasing_org_id
INTO x_purchasing_org_id
FROM po_ga_org_assignments pgoa, po_system_parameters psp
WHERE pgoa.po_header_id = p_po_header_id
AND pgoa.organization_id = psp.org_id
AND pgoa.enabled_flag = 'Y';
SELECT mir_ga.revision
INTO x_item_revision
FROM financials_system_params_all fspa_ga, -- GA owning org
financials_system_params_all fspa,
mtl_item_revisions_b mir_ga, -- GA revision
mtl_item_revisions_b mir
WHERE fspa_ga.org_id = p_owning_org_id
AND mir_ga.organization_id = fspa_ga.inventory_organization_id
AND mir_ga.inventory_item_id = p_item_id
AND mir_ga.revision = p_ga_item_revision
AND fspa.org_id = p_org_id
AND mir.organization_id = fspa.inventory_organization_id
AND mir.inventory_item_id = mir_ga.inventory_item_id
AND mir.revision = mir_ga.revision;
SELECT NVL(msi.revision_qty_control_code, 1)
INTO l_rev_control
FROM financials_system_params_all fspa_ga, -- GA owning org
financials_system_params_all fspa,
mtl_item_revisions_b mir_ga, -- GA revision
mtl_system_items_b msi
WHERE fspa_ga.org_id = p_owning_org_id
AND mir_ga.organization_id = fspa_ga.inventory_organization_id
AND mir_ga.inventory_item_id = p_item_id
AND mir_ga.revision = p_ga_item_revision
AND fspa.org_id = p_org_id
AND msi.inventory_item_id = mir_ga.inventory_item_id
AND msi.organization_id = fspa.inventory_organization_id;
SELECT pgoa.purchasing_org_id
INTO x_purchasing_org_id
FROM po_ga_org_assignments pgoa,
po_system_parameters psp
WHERE pgoa.po_header_id = p_po_header_id
AND pgoa.purchasing_org_id = psp.org_id
AND pgoa.enabled_flag = 'Y'
AND rownum = 1;
SELECT 'Y'
INTO l_valid_flag
FROM po_ga_org_assignments
WHERE po_header_id = p_po_header_id
AND purchasing_org_id = x_purchasing_org_id
AND enabled_flag = 'Y'
AND rownum = 1;
SELECT pgoa.purchasing_org_id,
pgoa.organization_id
INTO x_purchasing_org_id,
x_requesting_org_id
FROM po_ga_org_assignments pgoa,
po_system_parameters psp
WHERE pgoa.po_header_id = p_po_header_id
AND pgoa.organization_id = psp.org_id
AND pgoa.enabled_flag = 'Y';
SELECT purchasing_org_id
INTO x_purchasing_org_id
FROM po_ga_org_assignments
WHERE po_header_id = p_po_header_id
AND organization_id = x_requesting_org_id
AND enabled_flag = 'Y';
select from_line_id, decode(from_header_id, NULL,contract_id, from_header_id ) from po_lines_merge_v where po_header_id = p_po_header_id and draft_id = p_draft_id; --Bug 13540474
fnd_attached_documents2_pkg.delete_attachments('PO_IN_GA_LINES',
l_po_line_id,
'', '', '', '', 'N', 'Y');