[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(decode( pol.matching_basis
, 'AMOUNT'
, (decode( pll.closed_code
, 'FINALLY_CLOSED'
, ( decode( sign( nvl(pod.amount_delivered,0)
- nvl(pod.amount_billed,0))
, 1
, nvl(pod.amount_delivered,0)
, nvl(pod.amount_billed,0)))
, ( nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0)) ))
, (decode( pll.closed_code
, 'FINALLY_CLOSED'
, ( decode( sign( nvl(pod.quantity_delivered,0)
- nvl(pod.quantity_billed,0))
, 1
, nvl(pod.quantity_delivered,0)
, nvl(pod.quantity_billed,0)))
* nvl(pll.price_override,0)
, ( nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
* nvl(pll.price_override,0) ))
)
)
INTO v_stdpo_amt
FROM po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
po_distributions_all pod
WHERE pod.po_header_id = poh.po_header_id
and pod.line_location_id = pll.line_location_id
and pol.po_header_id = poh.po_header_id
and pll.po_line_id = pol.po_line_id
and pll.from_header_id = p_contract_id
and nvl(pll.approved_flag, 'N') = 'Y'
and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
SELECT SUM(decode( pol.matching_basis
, 'AMOUNT'
, (decode( pll.closed_code
, 'FINALLY_CLOSED'
, ( decode( sign( nvl(pod.amount_delivered,0)
- nvl(pod.amount_billed,0))
, 1
, nvl(pod.amount_delivered,0)
, nvl(pod.amount_billed,0)))
, ( nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0)) ))
, (decode( pll.closed_code
, 'FINALLY_CLOSED'
, ( decode( sign( nvl(pod.quantity_delivered,0)
- nvl(pod.quantity_billed,0))
, 1
, nvl(pod.quantity_delivered,0)
, nvl(pod.quantity_billed,0)))
* nvl(pll.price_override,0)
, ( nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
* nvl(pll.price_override,0) ))
)
)
INTO v_amt_released
FROM po_releases_all por,
po_headers_all poh,
po_line_locations_all pll,
po_distributions_all pod,
po_lines_all pol
WHERE pod.po_release_id = por.po_release_id
and pod.po_header_id = poh.po_header_id
and pod.po_line_id = pol.po_line_id
and pod.line_location_id = pll.line_location_id
and poh.po_header_id = p_contract_id
and pod.org_id = p_org_id
and nvl(pll.approved_flag,
'N') = 'Y'
and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
SELECT SUM(decode( plc.matching_basis
, 'AMOUNT'
, (decode( pll.closed_code
, 'FINALLY_CLOSED'
, (decode( sign( nvl(pod.amount_delivered,0)
- nvl(pod.amount_billed,0))
, 1
, nvl(pod.amount_delivered,0)
, nvl(pod.amount_billed,0)))
, ( nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0)) ))
, (decode( pll.closed_code
, 'FINALLY_CLOSED'
, (decode( sign( nvl(pod.quantity_delivered,0)
- nvl(pod.quantity_billed,0))
, 1
, nvl(pod.quantity_delivered,0)
, nvl(pod.quantity_billed,0)))
* nvl(pll.price_override,0)
, ( nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
* nvl(pll.price_override,0) ))
)
)
INTO v_amt_released
FROM po_headers_all poh,
po_headers_all poh2,
po_lines_all plc,
po_line_locations_all pll,
po_distributions_all pod
WHERE pod.po_header_id = poh.po_header_id
and pod.po_line_id = plc.po_line_id
and pod.line_location_id = pll.line_location_id
and plc.contract_id = poh2.po_header_id
and poh2.po_header_id = p_contract_id
and pod.org_id = p_org_id
and nvl(pll.approved_flag,
'N') = 'Y'
and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
SELECT SUM(decode( pol.matching_basis
, 'AMOUNT'
, (decode( pll.closed_code
, 'FINALLY_CLOSED'
, (decode( sign( nvl(pod.amount_delivered,0)
- nvl(pod.amount_billed,0))
, 1
, nvl(pod.amount_delivered,0)
, nvl(pod.amount_billed,0)))
, ( nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0)) ))
, (decode( pll.closed_code
, 'FINALLY_CLOSED'
, (decode( sign( nvl(pod.quantity_delivered,0)
- nvl(pod.quantity_billed,0))
, 1
, nvl(pod.quantity_delivered,0)
, nvl(pod.quantity_billed,0)))
* nvl(pll.price_override,0)
, ( nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
* nvl(pll.price_override,0) ))
)
)
INTO v_stdpo_amt
FROM po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
po_distributions_all pod
WHERE pod.po_header_id = poh.po_header_id
and pod.line_location_id = pll.line_location_id
and pol.po_header_id = poh.po_header_id
and pll.po_line_id = pol.po_line_id
and pol.from_header_id = p_contract_id
and pol.from_line_id = p_line_id
and nvl(pll.approved_flag, 'N') = 'Y'
and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
SELECT SUM(decode( pol.matching_basis
, 'AMOUNT'
, (decode( pll.closed_code
, 'FINALLY_CLOSED'
, (decode( sign( nvl(pod.amount_delivered,0)
- nvl(pod.amount_billed,0))
, 1
, nvl(pod.amount_delivered,0),nvl(pod.amount_billed,0)))
, ( nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0)) ))
, (decode( pll.closed_code
, 'FINALLY_CLOSED'
, (decode( sign( nvl(pod.quantity_delivered,0)
- nvl(pod.quantity_billed,0))
, 1
, nvl(pod.quantity_delivered,0),nvl(pod.quantity_billed,0)))
* nvl(pll.price_override,0)
, ( nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
* nvl(pll.price_override,0) ))
)
)
INTO v_amt_released
FROM po_distributions_all pod,
po_line_locations_all pll,
po_headers_all poh,
po_releases_all por,
po_lines_all pol
WHERE pod.po_release_id = por.po_release_id
and pod.po_header_id = poh.po_header_id
and pod.po_line_id = pol.po_line_id
and pod.line_location_id = pll.line_location_id
and poh.po_header_id = p_contract_id
and pod.org_id = p_org_id
and pod.po_line_id = p_line_id
and nvl(pll.approved_flag,
'N') = 'Y'
and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
SELECT SUM(decode(pll.closed_code, 'FINALLY_CLOSED',
(decode(sign(nvl(pod.quantity_delivered,0)
- nvl(pod.quantity_billed,0)),
1, nvl(pod.quantity_delivered,0),nvl(pod.quantity_billed,0)))
,
(nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
))
INTO v_stdpo_qty
FROM po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
po_distributions_all pod
WHERE pod.po_header_id = poh.po_header_id
and pod.line_location_id = pll.line_location_id
and pol.po_header_id = poh.po_header_id
and pll.po_line_id = pol.po_line_id
and pll.from_header_id = p_contract_id
and pll.from_line_id = p_line_id
and nvl(pll.approved_flag, 'N') = 'Y'
and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
SELECT SUM(decode(pll.closed_code, 'FINALLY_CLOSED',
(decode(sign(nvl(pod.quantity_delivered,0)
- nvl(pod.quantity_billed,0)),
1, nvl(pod.quantity_delivered,0), nvl(pod.quantity_billed,0))),
(nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))))
INTO v_qty_released
FROM po_distributions_all pod,
po_line_locations_all pll,
po_headers_all poh,
po_releases_all por
WHERE pod.po_release_id = por.po_release_id
and pod.po_header_id = poh.po_header_id
and pod.line_location_id = pll.line_location_id
and poh.po_header_id = p_contract_id
and pod.org_id = p_org_id
and pod.po_line_id = p_line_id
and nvl(pll.approved_flag,
'N') = 'Y'
and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
SELECT max(pah.sequence_num)
INTO v_sequence_num
FROM po_action_history pah
WHERE object_id = p_po_header_id
and object_type_code in ('PO', 'PA')
and action_code = 'APPROVE';
SELECT pah.employee_id
INTO v_employee_id
FROM po_action_history pah
WHERE pah.sequence_num = v_sequence_num
and pah.object_id = p_po_header_id
and pah.object_type_code in ('PO', 'PA')
and pah.action_code = 'APPROVE'
and rownum < 2;
SELECT max(pac.action_date)
INTO v_accept_date
FROM po_acceptances pac
WHERE pac.po_header_id = p_doc_id
and pac.accepted_flag = 'Y';
SELECT max(pac.action_date)
INTO v_accept_date
FROM po_acceptances pac
WHERE pac.po_release_id = p_doc_id
and pac.accepted_flag = 'Y';
SELECT prh.requisition_header_id
INTO v_req_header_id
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prd.distribution_id = p_req_dist_id
and prl.requisition_line_id = prd.requisition_line_id
and prh.requisition_header_id = prl.requisition_header_id;
SELECT max(pah.action_date)
INTO v_approval_date
FROM po_action_history pah
WHERE pah.object_id = v_req_header_id
and pah.object_type_code = 'REQUISITION'
and pah.object_sub_type_code = 'PURCHASE'
and pah.action_code = 'APPROVE';
SELECT decode(max('Y'), 'Y', 'Y', 'N')
INTO v_supp_approved
FROM po_distributions_all pod,
po_line_locations_all pll,
po_lines_all pol,
po_headers_all poh,
po_asl_status_rules pasr,
po_asl_statuses pas,
po_approved_supplier_list pasl
WHERE pod.po_header_id = poh.po_header_id
and pod.po_line_id = pol.po_line_id
and pod.line_location_id = pll.line_location_id
and poh.vendor_id = pasl.vendor_id
and (poh.vendor_site_id = pasl.vendor_site_id
OR
pasl.vendor_site_id is null)
and ((pll.ship_to_organization_id = pasl.using_organization_id)
OR
(pasl.using_organization_id = -1
and not exists
(SELECT 'local exists with global record'
FROM po_line_locations_all pll2,
po_lines_all pol2,
po_headers_all poh2,
po_approved_supplier_list pasl2
WHERE pll2.ship_to_organization_id =
pasl.using_organization_id
and pll2.po_header_id = poh2.po_header_id
and pol2.po_header_id = poh2.po_header_id
and poh2.vendor_id = pasl2.vendor_id
and ((pol2.item_id is not null
and pol2.item_id = pasl2.item_id)
OR
(pol2.item_id is null
and pol2.category_id = pasl2.category_id)))))
and ((pol.item_id is not null
and pol.item_id = pasl.item_id)
OR
(pol.item_id is null
and pol.category_id = pasl.category_id))
and pasl.asl_status_id = pas.status_id
and pasr.status_id = pas.status_id
and pasr.business_rule = '1_PO_APPROVAL'
and pasr.allow_action_flag = 'Y'
and pod.po_distribution_id = p_po_dist_id
and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
select decode(max('Y'), 'Y', 'Y', 'N')
into v_supp_approved
from po_asl_status_rules pasr,
po_asl_statuses pas,
po_approved_supplier_list pasl
where pasl.vendor_id = p_vendor_id
and (pasl.vendor_site_id is null or
pasl.vendor_site_id = p_vendor_site_id)
and pasl.using_organization_id in (-1,p_ship_to_org_id)
and ((p_item_id is not null and pasl.item_id = p_item_id) or
(p_item_id is null and pasl.category_id = p_category_id))
and pasl.asl_status_id = pas.status_id
and pasr.status_id = pas.status_id
and pasr.business_rule = '1_PO_APPROVAL'
having count(pasr.allow_action_flag)
= count(decode(pasr.allow_action_flag,'Y','Y',null));
select min(ack.check_date)
into cc_date
from ap_checks_all ack,
ap_invoice_payments_all aip,
ap_invoice_distributions_all aid
where aip.check_id = ack.check_id
and aip.invoice_id = aid.invoice_id
and aid.po_distribution_id = p_po_dist_id;
select min(ain.invoice_received_date)
into inv_rec_date
from ap_invoices_all ain,
ap_invoice_distributions_all aid
where ain.invoice_id = aid.invoice_id
and aid.po_distribution_id = p_po_dist_id;
select min(aid.creation_date)
into inv_creation_date
from ap_invoice_distributions_all aid
where aid.po_distribution_id = p_po_dist_id;
select trunc(min(transaction_date))
into goods_rcvd_date
from rcv_transactions rct
where transaction_type = 'RECEIVE'
and rct. po_line_location_id = p_po_line_loc_id;
select SUM(base_invoice_price_variance) into v_ipv
from ap_invoice_distributions_all
where po_distribution_id = p_po_dist_id;