[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_id
INTO l_org_id
FROM po_system_parameters;
SELECT hou.name
INTO p_org_name
FROM hr_all_organization_units hou,
hr_all_organization_units_tl hout
WHERE hou.organization_id = hout.organization_id
AND hout.LANGUAGE = UserEnv('LANG')
AND hou.organization_id = l_org_id;
SELECT vendor_name
INTO p_supplier_name
FROM po_vendors
WHERE vendor_id = p_supplier_id;
stmt_po := 'SELECT poh.segment1 PO_Number,
poh.po_header_id,
pdt.document_type_code,
pdt.document_subtype,
pdt.type_name
FROM po_headers poh,
po_document_types_vl pdt,
po_distributions pod,
po_line_locations pll
WHERE poh.type_lookup_code = pdt.document_subtype
AND poh.po_header_id = pod.po_header_id
AND poh.po_header_id = pll.po_header_id
AND pll.line_location_id = pod.line_location_id
AND (Nvl(pll.closed_code,''OPEN'') IN (''CLOSED FOR INVOICE'',''CLOSED FOR RECEIVING'') OR Nvl(pll.closed_code,''OPEN'') = ''OPEN'')
AND ( Nvl(pod.encumbered_flag,''N'') = ''Y'' OR Nvl(pod.encumbered_flag,''N'') = ''N'')
AND pll.po_release_id IS NULL
AND Nvl(pdt.document_type_code,''PO'') = ''PO''
AND Nvl(poh.authorization_status, ''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'')
AND Nvl(poh.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'')
AND ( Nvl(poh.frozen_flag,''N'') = ''Y'' OR Nvl(poh.frozen_flag,''N'') = ''N'')
AND Nvl(poh.cancel_flag,''N'') = ''N''';
stmt_po := 'SELECT poh.segment1 PO_Number,
poh.po_header_id,
pdt.document_type_code,
pdt.document_subtype,
pdt.type_name
FROM po_headers poh,
po_document_types_vl pdt
WHERE poh.type_lookup_code = pdt.document_subtype
AND Nvl(pdt.document_type_code,''PA'') = ''PA''
AND Nvl(poh.authorization_status, ''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'')
AND Nvl(poh.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'')
AND ( Nvl(poh.frozen_flag,''N'') = ''Y'' OR Nvl(poh.frozen_flag,''N'') = ''N'')
AND Nvl(poh.cancel_flag,''N'') = ''N''';
stmt_po := 'SELECT poh.segment1 PO_Number,
poh.po_header_id,
pdt.document_type_code,
pdt.document_subtype,
pdt.type_name
FROM po_headers poh,
po_document_types_vl pdt,
po_distributions pod,
po_line_locations pll
WHERE poh.type_lookup_code = pdt.document_subtype
AND poh.po_header_id = pod.po_header_id
AND poh.po_header_id = pll.po_header_id
AND pll.line_location_id = pod.line_location_id
AND (Nvl(pll.closed_code,''OPEN'') IN (''CLOSED FOR INVOICE'',''CLOSED FOR RECEIVING'') OR Nvl(pll.closed_code,''OPEN'') = ''OPEN'')
AND ( Nvl(pod.encumbered_flag,''N'') = ''Y'' OR Nvl(pod.encumbered_flag,''N'') = ''N'')
AND pll.po_release_id IS NULL
AND Nvl(pdt.document_type_code,''PO'') = ''PO''
AND Nvl(poh.authorization_status, ''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'')
AND Nvl(poh.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'')
AND ( Nvl(poh.frozen_flag,''N'') = ''Y'' OR Nvl(poh.frozen_flag,''N'') = ''N'')
AND Nvl(poh.cancel_flag,''N'') = ''N''
UNION
SELECT poh.segment1 PO_Number,
poh.po_header_id,
pdt.document_type_code,
pdt.document_subtype,
pdt.type_name FROM po_headers poh,
po_document_types_vl pdt
WHERE poh.type_lookup_code = pdt.document_subtype
AND Nvl(pdt.document_type_code,''PA'') = ''PA''
AND Nvl(poh.authorization_status, ''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'')
AND Nvl(poh.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'')
AND ( Nvl(poh.frozen_flag,''N'') = ''Y'' OR Nvl(poh.frozen_flag,''N'') = ''N'')
AND Nvl(poh.cancel_flag,''N'') = ''N''';
stmt_rel := 'SELECT poh.segment1,
por.po_release_id,
por.release_num,
pdt.document_type_code,
pdt.document_subtype,
pdt.type_name
FROM po_releases por,
po_headers poh,
po_document_types_vl pdt,
po_distributions_all pod,
po_line_locations_all pll
WHERE por.po_header_id = poh.po_header_id
AND por.po_header_id = pod.po_header_id
AND por.po_header_id = pll.po_header_id
AND pll.line_location_id = pod.line_location_id
AND pdt.document_type_code = ''RELEASE''
AND pdt.document_subtype = por.release_type
AND pod.po_release_id IS NOT NULL
AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'')
AND Nvl(por.closed_code,''OPEN'') NOT IN (''CLOSED'',''FINALLY CLOSED'')
AND (Nvl(pll.closed_code,''OPEN'') IN (''CLOSED FOR INVOICE'',''CLOSED FOR RECEIVING'') OR Nvl(pll.closed_code,''OPEN'') = ''OPEN'')
AND Nvl(por.cancel_flag,''N'') = ''N''
AND ( Nvl(por.frozen_flag,''N'') = ''Y'' OR Nvl(por.frozen_flag,''N'') = ''N'')
AND ( Nvl(pod.encumbered_flag,''N'') = ''Y'' OR Nvl(pod.encumbered_flag,''N'') = ''N'')';