[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select PO_HEADER_ID,COMMENTS,AUTHORIZATION_STATUS,
TYPE_LOOKUP_CODE,AGENT_ID,SEGMENT1,CLOSED_CODE,CURRENCY_CODE
from po_headers_all
where PO_HEADER_ID = p_po_header_id;
select PO_HEADER_ID,COMMENTS,AUTHORIZATION_STATUS,
TYPE_LOOKUP_CODE,AGENT_ID,SEGMENT1,CLOSED_CODE,CURRENCY_CODE
from po_headers_draft_all
where PO_HEADER_ID = p_po_header_id
and draft_id = p_draft_id;
select PORL.PO_RELEASE_ID,PORL.PO_HEADER_ID,PORL.AUTHORIZATION_STATUS,
PORL.RELEASE_TYPE,PORL.AGENT_ID,PORL.RELEASE_NUM,PORL.CLOSED_CODE,
POH.SEGMENT1, POH.CURRENCY_CODE, POH.COMMENTS
-- Bug 10140786 Selected comments to set PO_DESCRIPTION in release workflow.
from po_releases_all PORL, po_headers_all POH
where PORL.PO_RELEASE_ID = p_rel_header_id
and PORL.po_header_id = POH.po_header_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
SELECT (Nvl (comm_rev_num, -1))
INTO l_po_revision_num_orig
FROM po_headers_all
WHERE po_header_id = l_po_header_id;
SELECT (Nvl (comm_rev_num, -1))
INTO l_po_revision_num_orig
FROM po_releases_all
WHERE po_release_id = l_po_header_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
select acceptance_required_flag,acceptance_due_date
into l_acceptance_required, l_acceptance_due_date
from po_headers
where po_header_id= l_po_header_id;
select acceptance_required_flag,acceptance_due_date
into l_acceptance_required, l_acceptance_due_date
from po_releases
where po_release_id= l_po_header_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select DISPLAYED_FIELD
from po_lookup_codes
where lookup_type='AUTHORIZATION STATUS'
and lookup_code = p_auth_stat;
select DISPLAYED_FIELD
from po_lookup_codes
where lookup_type='DOCUMENT STATE'
and lookup_code = p_closed_code;
select type_name
from po_document_types
where document_type_code = p_doc_type
and document_subtype = p_doc_subtype;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
Old tax select:
select nvl(sum(NONRECOVERABLE_TAX),0)
into l_tax_amount
from po_distributions
where po_header_id = POHdr_rec.po_header_id;
SELECT nvl(sum( round (nvl(POD.nonrecoverable_tax,0) *
decode(POLL.matching_basis,
'AMOUNT',
--Bug16222308 Handling the quantity zero on distribution
(nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / Decode ( nvl(POD.amount_ordered, 1),0,1,nvl(POD.amount_ordered, 1) ),
(nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / Decode ( nvl(POD.quantity_ordered, 1),0,1,nvl(POD.quantity_ordered, 1) )
) / X_min_acct_unit
) * X_min_acct_unit
),0)
INTO l_tax_amount
FROM po_distributions_all POD,
po_line_locations_all POLL
WHERE POD.po_header_id = POHdr_rec.po_header_id
AND POD.line_location_id=POLL.line_location_id
AND POD.po_header_id=POLL.po_header_id
AND Nvl(POD.distribution_type,'STANDARD') NOT IN ('PREPAYMENT') --11876122
AND POD.line_location_id IS NOT NULL; -- 13887381
SELECT nvl(sum( round (nvl(POD.nonrecoverable_tax,0) *
decode(POLL.matching_basis,
'AMOUNT',
--Bug16222308 Handling the quantity zero on distribution
(nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / Decode ( nvl(POD.amount_ordered, 1),0,1,nvl(POD.amount_ordered, 1) ),
(nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / Decode ( nvl(POD.quantity_ordered, 1),0,1,nvl(POD.quantity_ordered, 1) )
),
X_precision
)
),0)
INTO l_tax_amount
FROM po_distributions_all POD,
po_line_locations_all POLL
WHERE POD.po_header_id = POHdr_rec.po_header_id
AND POD.line_location_id=POLL.line_location_id
AND POD.po_header_id=POLL.po_header_id
AND Nvl(distribution_type,'STANDARD') NOT IN ('PREPAYMENT') -- 11876122
AND POD.line_location_id IS NOT NULL; -- 13887381
select sum(funded_value)
into l_total_funded_amount
from po_distributions_merge_v
where po_header_id = l_doc_id
and draft_id = l_draft_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select DISPLAYED_FIELD
from po_lookup_codes
where lookup_type='AUTHORIZATION STATUS'
and lookup_code = p_auth_stat;
select DISPLAYED_FIELD
from po_lookup_codes
where lookup_type='DOCUMENT STATE'
and lookup_code = p_closed_code;
select type_name
from po_document_types
where document_type_code = p_doc_type
and document_subtype = p_doc_subtype;
select DISPLAYED_FIELD
from po_lookup_codes
where lookup_type='DOCUMENT TYPE'
and lookup_code = p_doc_type;
select DISPLAYED_FIELD
from po_lookup_codes
where lookup_type='DOCUMENT SUBTYPE'
and lookup_code = p_doc_subtype;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
Old tax select:
select nvl(sum(NONRECOVERABLE_TAX),0)
into l_tax_amount
from po_distributions
where po_release_id = RelHdr_rec.Po_Release_id;
SELECT nvl(sum( round (POD.nonrecoverable_tax *
decode(quantity_ordered,
NULL,
--Bug16222308 Handling the quantity zero on distribution
(nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / Decode ( nvl(POD.amount_ordered, 1),0,1,nvl(POD.amount_ordered, 1) ),
(nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / Decode ( nvl(POD.quantity_ordered, 1),0,1,nvl(POD.quantity_ordered, 1) )
) / X_min_acct_unit
) * X_min_acct_unit
),0)
INTO l_tax_amount
FROM po_distributions_all POD
WHERE po_release_id = RelHdr_rec.po_release_id;
SELECT nvl(sum( round (POD.nonrecoverable_tax *
decode(quantity_ordered,
NULL,
--Bug16222308 Handling the quantity zero on distribution
(nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / Decode ( nvl(POD.amount_ordered, 1),0,1,nvl(POD.amount_ordered, 1) ),
(nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / Decode ( nvl(POD.quantity_ordered, 1),0,1,nvl(POD.quantity_ordered, 1) )
),
X_precision
)
),0)
INTO l_tax_amount
FROM po_distributions_all POD
WHERE po_release_id = RelHdr_rec.po_release_id;
SELECT type_lookup_code
INTO l_doc_subtype
FROM po_headers
WHERE po_header_id = doc_header_id;
SELECT shipment_type
INTO l_doc_subtype
FROM po_line_locations
WHERE po_release_id = doc_header_id
AND ROWNUM = 1;
SELECT type_lookup_code
INTO l_doc_subtype
FROM po_requisition_headers
WHERE requisition_header_id = doc_header_id;
SELECT substrb ( part1 || decode(part2, NULL, '', part2 || ' ') || part3,
1, 2000)
INTO l_address
FROM
(SELECT hrl.address_line_1 || ', '
|| decode(hrl.address_line_2, null, '', hrl.address_line_2||', ')
|| decode(hrl.address_line_3, null, '', hrl.address_line_3||', ')
|| decode(hr_general.decode_lookup(hrl.country||'_PROVINCE',hrl.town_or_city ),
NULL, decode(hrl.town_or_city, null, '', hrl.town_or_city ||', '),
hr_general.decode_lookup(hrl.country||'_PROVINCE',hrl.town_or_city ) || ', ')
AS part1,
nvl(decode(hrl.region_1,
null, hrl.region_2,
decode(flv1.meaning,
null, decode(flv2.meaning,
null, flv3.meaning,
flv2.lookup_code),
flv1.lookup_code) ),
hrl.region_2)
AS part2,
decode(hrl.postal_code, null, '', hrl.postal_code || ', ')
|| ftv.territory_short_name
AS part3
FROM hr_locations hrl,
fnd_territories_vl ftv,
fnd_lookup_values_vl flv1,
fnd_lookup_values_vl flv2,
fnd_lookup_values_vl flv3
WHERE hrl.region_1 = flv1.lookup_code (+) and
hrl.country || '_PROVINCE' = flv1.lookup_type (+) and
hrl.region_2 = flv2.lookup_code (+) and
hrl.country || '_STATE' = flv2.lookup_type (+) and
hrl.region_1 = flv3.lookup_code (+) and
hrl.country || '_COUNTY' = flv3.lookup_type (+) and
hrl.country = ftv.territory_code (+) and
hrl.location_id = p_location_id);
SELECT PRL.to_person_id, POH.agent_id
FROM po_lines POL,
po_line_locations PLL,
-- For Shared Procurement, the destination OU may be different from
-- the Purchasing OU:
po_requisition_lines_all PRL,
po_headers POH
WHERE POL.po_line_id = p_po_line_id
AND POL.po_line_id = PLL.po_line_id -- JOIN
AND PLL.line_location_id = PRL.line_location_id (+) -- JOIN
AND POH.po_header_id = POL.po_header_id -- JOIN
ORDER BY PLL.shipment_num;
SELECT POD.deliver_to_person_id
FROM po_distributions_all POD
, po_line_locations PLL --
WHERE POD.line_location_id = PLL.line_location_id
AND PLL.po_line_id = p_po_line_id
AND POD.deliver_to_person_id IS NOT NULL
AND PLL.shipment_type <> 'PREPAYMENT'
ORDER BY PLL.shipment_num, POD.distribution_num ASC;
SELECT POL.po_line_id,
POL.contractor_first_name,
POL.contractor_last_name,
PJ.name job_name
FROM po_headers POH,
po_lines POL,
per_jobs_vl PJ
WHERE POH.po_header_id = p_po_header_id
AND POH.po_header_id = POL.po_header_id -- JOIN
AND POL.purchase_basis = 'TEMP LABOR'
AND PJ.job_id = POL.job_id -- JOIN
AND -- For a new document, we want all of the lines.
((p_is_new_document = 'Y') OR
-- For an existing document, we only want the new lines - i.e.
-- the lines that do not have any older archived revisions.
NOT EXISTS
(SELECT 1
FROM po_lines_archive PLA
WHERE PLA.po_line_id = POL.po_line_id -- JOIN
AND PLA.revision_num <> POH.revision_num));
PO_WF_DEBUG_PKG.insert_debug ( itemtype, itemkey,
g_pkg_name||'.'||l_proc_name||': document type: ' || l_document_type
|| ', document subtype: ' || l_document_subtype );
SELECT PO_WF_ITEMKEY_S.nextval
INTO l_item_key_seq
FROM dual;
PO_WF_DEBUG_PKG.insert_debug ( p_item_type, p_item_key,
substrb (
g_pkg_name||'.'||l_proc_name||': p_item_type: '||p_item_type
||', p_item_key: '||p_item_key||', p_process: '||p_process
||', p_po_line_id: '||p_po_line_id,
1, 1000 ) );
SELECT POH.po_header_id,
POH.segment1,
PRL.requisition_header_id
INTO l_po_header_id,
l_document_number,
l_req_header_id
FROM po_lines POL,
po_headers POH,
po_line_locations PLL,
po_requisition_lines_all PRL
WHERE POL.po_line_id = p_po_line_id
AND POL.po_header_id = POH.po_header_id -- JOIN
AND POL.po_line_id = PLL.po_line_id -- JOIN
AND PLL.line_location_id = PRL.line_location_id (+); -- JOIN
SELECT approved_date
INTO x_approved_date
FROM po_releases_all
WHERE po_release_id = p_doc_id;
SELECT approved_date
INTO x_approved_date
FROM po_headers_all
WHERE po_header_id= p_doc_id;