[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(pod.gl_encumbered_date)
,min(pod.gl_encumbered_date)
FROM po_distributions pod,
po_lines pol,
po_line_locations poll
WHERE pol.po_header_id = p_document_id
AND NVL(pol.closed_code,'X') <> 'FINALLY CLOSED'
AND NVL(poll.closed_code,'X') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD','PLANNED')
AND pod.line_location_id = poll.line_location_id
AND pod.po_line_id = pol.po_line_id
AND poll.po_line_id = pol.po_line_id
AND nvl(poll.cancel_flag,'N') = 'N'
AND nvl(pol.cancel_flag,'N') = 'N'
AND nvl(pod.prevent_encumbrance_flag,'N') = 'N'
AND GREATEST( Decode (poll.accrue_on_receipt_flag,
'N', Nvl(pod.quantity_ordered,0) -
Greatest (nvl(pod.quantity_billed,0),
Nvl(pod.unencumbered_quantity,0)),
'Y', nvl(pod.quantity_ordered,0) -
Greatest (Nvl(pod.quantity_delivered,0),
Nvl(pod.unencumbered_quantity,0)), 0) ,0) > 0 ;
SELECT max(gl_encumbered_date)
,min(gl_encumbered_date)
FROM po_req_distributions dists
,po_requisition_lines lines
WHERE dists.requisition_line_id = lines.requisition_line_id
AND lines.requisition_header_id = p_document_id
AND NVL(lines.closed_code,'X') <> 'FINALLY CLOSED'
AND NVL(lines.cancel_flag,'N') = 'N'
AND Nvl(lines.line_location_id,-999) = -999
AND lines.source_type_code = 'VENDOR';
SELECT max(pod.gl_encumbered_date)
,min(pod.gl_encumbered_date)
FROM po_distributions pod,
po_line_locations poll
WHERE pod.po_release_id = p_document_id
AND poll.po_release_id = p_document_id
AND NVL(poll.closed_code,'X') <> 'FINALLY CLOSED'
AND pod.line_location_id = poll.line_location_id
-- ssmales 02-Apr-03 bug 2876775 cancel flag clause below needs nvl
-- AND poll.cancel_flag = 'N'
AND NVL(poll.cancel_flag,'N') = 'N'
AND poll.shipment_type IN ('BLANKET','SCHEDULED')
AND NVL(pod.prevent_encumbrance_flag,'N') = 'N'
AND GREATEST( Decode (poll.accrue_on_receipt_flag,
'N', Nvl(pod.quantity_ordered,0) -
Greatest (nvl(pod.quantity_billed,0),
Nvl(pod.unencumbered_quantity,0)),
'Y', nvl(pod.quantity_ordered,0) -
Greatest (Nvl(pod.quantity_delivered,0),
Nvl(pod.unencumbered_quantity,0)), 0) ,0) > 0 ;
SELECT encumbrance_required_flag
FROM po_headers
WHERE po_header_id = p_po_header_id;
SELECT period_year
FROM gl_periods gp,
gl_sets_of_books gsob
WHERE gp.period_set_name = gsob.period_set_name
AND gp.period_type = gsob.accounted_period_type
AND trunc(p_date) BETWEEN trunc(gp.start_date)
AND trunc(gp.end_date)
AND gsob.set_of_books_id = p_sob_id;
SELECT cbc_po_enable
FROM igc_cc_bc_enable a,
financials_system_parameters b
WHERE a.set_of_books_id = b.set_of_books_id;
SELECT set_of_books_id,req_encumbrance_flag,purch_encumbrance_flag
INTO l_sob_id,l_req_encumbrance_flag,l_purch_encumbrance_flag
FROM financials_system_parameters;
SELECT gps.closing_status
FROM gl_period_statuses gps,
fnd_application app
WHERE gps.application_id = app.application_id
AND app.application_short_name = p_appl_name
AND gps.set_of_books_id = p_sob_id
AND p_date BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
SELECT MAX(pod.gl_encumbered_date),
MIN(pod.gl_encumbered_date)
FROM po_distributions pod
WHERE pod.po_header_id = p_document_id
AND pod.distribution_type = 'AGREEMENT';
SELECT req_encumbrance_flag
,purch_encumbrance_flag
,set_of_books_id
INTO l_req_encumbrance_flag
,l_purch_encumbrance_flag
,l_sob_id
FROM financials_system_parameters;
SELECT cbc_accounting_date
INTO l_prev_cbc_acct_date
FROM po_headers
WHERE po_header_id = p_document_id;
SELECT cbc_accounting_date
INTO l_prev_cbc_acct_date
FROM po_releases
WHERE po_release_id = p_document_id;
SELECT cbc_accounting_date
INTO l_prev_cbc_acct_date
FROM po_requisition_headers
WHERE requisition_header_id = p_document_id;
SELECT max(prh.cbc_accounting_date),
min(prh.cbc_accounting_date)
INTO l_max_cbc_acc_date,
l_min_cbc_acc_date
FROM po_requisition_headers prh,
po_requisition_lines prl,
po_line_locations poll
WHERE poll.po_header_id = p_document_id
AND poll.line_location_id = prl.line_location_id
AND prl.requisition_header_id = prh.requisition_header_id
AND NVL(prl.closed_code,'X') <> 'FINALLY CLOSED'
AND NVL(prl.cancel_flag,'N') = 'N'
AND NVL(poll.closed_code,'X') <> 'FINALLY CLOSED'
AND NVL(poll.cancel_flag,'N') = 'N'
AND prl.source_type_code = 'VENDOR';
SELECT max(prh.cbc_accounting_date),
min(prh.cbc_accounting_date)
INTO l_max_cbc_acc_date,
l_min_cbc_acc_date
FROM po_requisition_headers prh,
po_requisition_lines prl,
po_line_locations poll
WHERE poll.po_release_id = p_document_id
AND prl.line_location_id = poll.line_location_id
AND prl.requisition_header_id = prh.requisition_header_id
AND NVL(prl.closed_code,'X') <> 'FINALLY CLOSED'
AND NVL(prl.cancel_flag,'N') = 'N'
AND NVL(poll.closed_code,'X') <> 'FINALLY CLOSED'
AND NVL(poll.cancel_flag,'N') = 'N'
AND prl.source_type_code = 'VENDOR';
select po.cbc_accounting_date
into l_po_cbc_acct_date
from po_headers po, po_releases por
where po.po_header_id = por.po_header_id
and por.po_release_id = p_document_id;
SELECT MAX(poh.cbc_accounting_date),
MIN(poh.cbc_accounting_date)
INTO l_max_bpa_accounting_date,
l_min_bpa_accounting_date
FROM po_headers poh,
Po_requisition_lines prl
WHERE prl.requisition_header_id = p_document_id
AND prl.blanket_po_header_id = poh.po_header_id
AND prl.blanket_po_header_id IS NOT NULL
AND poh.type_lookup_code = 'BLANKET'
AND poh.encumbrance_required_flag = 'Y'
AND NVL(prl.closed_code,'X') <> 'FINALLY CLOSED'
AND NVL(prl.cancel_flag,'N') = 'N';
SELECT MAX(bpa_h.cbc_accounting_date),
MIN(bpa_h.cbc_accounting_date)
INTO l_max_bpa_accounting_date,
l_min_bpa_accounting_date
FROM po_headers bpa_h,
Po_lines pol,
Po_line_locations poll,
Po_distributions bpa_d
WHERE pol.po_header_id = p_document_id
AND poll.po_line_id = pol.po_line_id
AND NVL(pol.closed_code,'X') <> 'FINALLY CLOSED'
AND NVL(pol.cancel_flag,'N') = 'N'
AND NVL(poll.closed_code,'X') <> 'FINALLY CLOSED'
AND NVL(poll.cancel_flag,'N') = 'N'
AND pol.from_header_id IS NOT NULL
AND pol.from_header_id = bpa_d.po_header_id
AND bpa_d.distribution_type = 'AGREEMENT'
AND bpa_d.po_header_id = bpa_h.po_header_id
AND bpa_h.type_lookup_code = 'BLANKET'
AND bpa_h.encumbrance_required_flag = 'Y';
SELECT cbc_accounting_date
FROM po_headers
WHERE po_header_id = p_document_id ;
SELECT cbc_accounting_date
FROM po_requisition_headers
WHERE requisition_header_id = p_document_id ;
SELECT cbc_accounting_date
FROM po_releases
WHERE po_release_id = p_document_id ;
SELECT start_date
FROM gl_period_statuses a,
fnd_application b
WHERE a.application_id = b.application_id
AND b.application_short_name = 'PO'
AND a.set_of_books_id = p_sob_id
AND a.closing_status = 'O'
AND a.start_date > sysdate
AND a.adjustment_period_flag = 'N'
order by start_date asc ;
SELECT max(porh.cbc_accounting_date)
FROM po_requisition_headers porh,
po_distributions pod,
po_requisition_lines porl,
po_req_distributions pord
WHERE pod.po_header_id = p_document_id
AND pod.req_distribution_id = pord.distribution_id(+)
AND pord.requisition_line_id = porl.requisition_line_id(+)
AND porl.requisition_header_id = porh.requisition_header_id;
SELECT max(porh.cbc_accounting_date)
FROM po_requisition_headers porh,
po_distributions pod,
po_requisition_lines porl,
po_req_distributions pord
WHERE pod.po_release_id = p_document_id
AND pod.req_distribution_id = pord.distribution_id(+)
AND pord.requisition_line_id = porl.requisition_line_id(+)
AND porl.requisition_header_id = porh.requisition_header_id;
SELECT poh.cbc_accounting_date
FROM po_releases por,
po_headers poh
WHERE por.po_release_id = p_document_id
AND por.po_header_id = poh.po_header_id ;
SELECT req_encumbrance_flag,
purch_encumbrance_flag,
set_of_books_id
FROM financials_system_parameters ;
SELECT max(bpa_h.cbc_accounting_date)
FROM po_headers bpa_h,
Po_lines pol,
Po_distributions bpa_d
WHERE pol.po_header_id = p_document_id
AND pol.from_header_id IS NOT NULL
AND pol.from_header_id = bpa_d.po_header_id
AND bpa_d.po_header_id = bpa_h.po_header_id
AND bpa_d.distribution_type = 'AGREEMENT'
AND bpa_h.type_lookup_code = 'BLANKET'
AND bpa_h.encumbrance_required_flag = 'Y';
SELECT MAX(bpa_h.cbc_accounting_date)
FROM po_headers bpa_h,
Po_requisition_lines prl
WHERE prl.requisition_header_id = p_document_id
AND prl.blanket_po_header_id = bpa_h.po_header_id
AND prl.blanket_po_header_id IS NOT NULL
AND bpa_h.type_lookup_code = 'BLANKET'
AND bpa_h.encumbrance_required_flag = 'Y'
AND NVL(prl.closed_code,'X') <> 'FINALLY CLOSED'
AND NVL(prl.cancel_flag,'N') = 'N';
PROCEDURE UPDATE_cbc_acct_date
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_document_id IN NUMBER,
p_document_type IN VARCHAR2,
p_document_sub_type IN VARCHAR2,
p_cbc_acct_date IN DATE
) AS
l_api_version CONSTANT NUMBER := 1.0 ;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_cbc_acct_date' ;
SELECT req_encumbrance_flag,
purch_encumbrance_flag,
set_of_books_id
FROM financials_system_parameters ;
UPDATE po_headers
-- SET cbc_accounting_date = NVL(p_cbc_acct_date, cbc_accounting_date)
SET cbc_accounting_date = p_cbc_acct_date
WHERE po_header_id = p_document_id ;
UPDATE po_releases
-- SET cbc_accounting_date = NVL(p_cbc_acct_date, cbc_accounting_date)
SET cbc_accounting_date = p_cbc_acct_date
WHERE po_release_id = p_document_id ;
UPDATE po_requisition_headers
-- SET cbc_accounting_date = NVL(p_cbc_acct_date, cbc_accounting_date)
SET cbc_accounting_date = p_cbc_acct_date
WHERE requisition_header_id = p_document_id ;
END UPDATE_cbc_acct_date ;
Select p.req_distribution_id,
p.gl_encumbered_date PO_GL_Date,
p.set_of_books_id sob_id,
h.cbc_accounting_date PO_Acct_Date
From PO_Distributions_V p,
PO_Headers h
Where p.po_header_id = p_document_id
And p.po_line_id = nvl(p_line_id, p.po_line_id)
And p.line_location_id = nvl(p_line_location_id, p.line_location_id)
And p.po_header_id = h.po_header_id
And p.requisition_header_id is not null;
Select pod.req_distribution_id,
pod.gl_encumbered_date rel_gl_date,
pod.set_of_books_id sob_id,
r.cbc_accounting_date rel_acct_date
From po_distributions pod,
po_releases r,
po_requisition_headers porh,
po_requisition_lines porl,
po_req_distributions pord
Where pod.po_release_id = p_document_id
And pod.line_location_id = nvl(p_line_location_id, pod.line_location_id)
And pod.po_release_id = r.po_release_id
And porh.requisition_header_id is not null
And pod.req_distribution_id = pord.distribution_id(+)
And pord.requisition_line_id = porl.requisition_line_id(+)
And porl.requisition_header_id = porh.requisition_header_id(+);
Select p.gl_encumbered_date rel_gl_date,
p.source_distribution_id,
p.set_of_books_id sob_id,
r.cbc_accounting_date rel_acct_date
From po_distributions_v P,
po_releases R
Where p.po_release_id = p_document_id
And p.line_location_id = nvl(p_line_location_id, p.line_location_id)
And p.po_release_id = r.po_release_id;
Select gl_encumbered_date
From po_req_distributions
Where distribution_id = p_req_dist_id;
Select gl_encumbered_date
From po_distributions
Where po_distribution_id = p_source_id;
Select distribution_id, gl_encumbered_date
From po_req_distributions
Where source_req_distribution_id = p_req_dist_id;
SELECT set_of_books_id,req_encumbrance_flag,purch_encumbrance_flag
INTO l_sob_id,l_req_encumbrance_flag,l_purch_encumbrance_flag
FROM financials_system_parameters;
UPDATE po_req_distributions
SET gl_encumbered_date = p_action_date
WHERE distribution_id = l_linked_req.distribution_id;
UPDATE po_req_distributions
SET gl_encumbered_date = p_action_date
WHERE distribution_id = l_po_dists_po.req_distribution_id;
UPDATE po_distributions
SET gl_encumbered_date = p_action_date
WHERE po_distribution_id = l_po_dists_sch_rel.source_distribution_id;
UPDATE po_req_distributions
SET gl_encumbered_date = p_action_date
WHERE distribution_id = l_linked_req.distribution_id;
UPDATE po_req_distributions
SET gl_encumbered_date = p_action_date
WHERE distribution_id = l_po_dists_bla_rel.req_distribution_id;