[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'that line number exists'
into dummy
from po_req_distributions
where requisition_line_id = x_requisition_line_id
and distribution_num = x_distribution_num
AND (rowid <> x_row_id
OR x_row_id IS NULL);
PROCEDURE NAME: check_unique_insert()
===========================================================================*/
PROCEDURE check_unique_insert(x_row_id IN OUT NOCOPY VARCHAR2, x_distribution_num NUMBER,
x_requisition_line_id NUMBER) IS
dummy varchar2(40);
select '1'
into dummy
from dual
where not exists (
select 'that line number exists'
from po_req_distributions
where requisition_line_id = x_requisition_line_id
and distribution_num = x_distribution_num
AND (rowid <> x_row_id
OR x_row_id IS NULL));
po_message_s.sql_error('check_unique_insert', x_progress, sqlcode);
END check_unique_insert;
select nvl(max(distribution_num),0)
from po_req_distributions
where requisition_line_id = x_requisition_line_id;
PROCEDURE NAME: select_summary()
===========================================================================*/
PROCEDURE select_summary(x_requisition_line_id IN OUT NOCOPY NUMBER,
x_total IN OUT NOCOPY NUMBER) IS
x_progress VARCHAR2(3) := NULL;
select nvl(sum(req_line_quantity),0)
into x_total
from po_req_distributions
where requisition_line_id = x_requisition_line_id;
po_message_s.sql_error('select_summary', x_progress, sqlcode);
END select_summary;
PROCEDURE NAME: update_reqs_distributions
===========================================================================*/
PROCEDURE update_reqs_distributions
(X_req_header_id IN NUMBER,
X_req_line_id IN NUMBER,
X_req_control_action IN VARCHAR2,
X_req_action_date IN DATE,
X_req_control_error_rc IN OUT NOCOPY VARCHAR2) IS
X_progress VARCHAR2(3) := NULL;
/* The following SQL statement is optimized to update either
** 1. all document lines - if header_id is passed or,
** 2. one document line - if both header_id and line_id are passed.
*/
X_progress := '010';
UPDATE PO_REQ_DISTRIBUTIONS
SET gl_cancelled_date = nvl(X_gl_cancelled_date, gl_cancelled_date),
gl_closed_date = nvl(X_gl_closed_date, gl_closed_date)
WHERE requisition_line_id IN
(SELECT requisition_line_id
FROM po_requisition_lines PORL
WHERE PORL.requisition_header_id = X_req_header_id
AND PORL.requisition_line_id =
nvl(X_req_line_id, PORL.requisition_line_id));
po_message_s.sql_error('update_reqs_distributions', X_progress, sqlcode);
po_message_s.sql_error('update_reqs_distributions', X_progress, sqlcode);
po_message_s.sql_error('update_reqs_distributions', X_progress, sqlcode);
END update_reqs_distributions;
SELECT distribution_id
FROM po_req_distributions
WHERE requisition_line_id = x_orig_req_line_id;
SELECT quantity
INTO x_orig_line_quantity
FROM po_requisition_lines
WHERE requisition_line_id = x_orig_req_line_id;
SELECT *
INTO dist_rec
FROM po_req_distributions
WHERE distribution_id = CREC.distribution_id;
po_req_distributions_pkg1.insert_row (x_rowid,
x_distribution_id,
dist_rec.last_update_date,
dist_rec.last_updated_by,
x_new_req_line_id,
dist_rec.set_of_books_id,
dist_rec.code_combination_id,
x_new_dist_quantity,
NULL,-- req_line_amount --
NULL,-- req_line_currency_amount--
dist_rec.last_update_login,
dist_rec.creation_date,
dist_rec.created_by,
dist_rec.encumbered_flag,
dist_rec.gl_encumbered_date,
dist_rec.gl_encumbered_period_name,
dist_rec.gl_cancelled_date,
dist_rec.failed_funds_lookup_code,
-- JFMIP, support for Req Modify when encumbrance is enabled
-- dist_rec.encumbered_amount,
0,
dist_rec.budget_account_id,
dist_rec.accrual_account_id,
dist_rec.variance_account_id,
dist_rec.prevent_encumbrance_flag,
dist_rec.attribute_category,
dist_rec.attribute1,
dist_rec.attribute2,
dist_rec.attribute3,
dist_rec.attribute4,
dist_rec.attribute5,
dist_rec.attribute6,
dist_rec.attribute7,
dist_rec.attribute8,
dist_rec.attribute9,
dist_rec.attribute10,
dist_rec.attribute11,
dist_rec.attribute12,
dist_rec.attribute13,
dist_rec.attribute14,
dist_rec.attribute15,
NULL, --
dist_rec.government_context,
dist_rec.project_id,
dist_rec.task_id,
dist_rec.expenditure_type,
dist_rec.project_accounting_context,
dist_rec.expenditure_organization_id,
dist_rec.gl_closed_date,
dist_rec.Distribution_Id, /* <> */ --dist_rec.source_req_distribution_id,
dist_rec.distribution_num,
dist_rec.project_related_flag,
dist_rec.expenditure_item_date,
dist_rec.end_item_unit_number,
dist_rec.recovery_rate,
-- JFMIP START
-- dist_rec.recoverable_tax,
-- dist_rec.nonrecoverable_tax,
l_prorated_rec_tax,
l_prorated_nonrec_tax,
-- JFMIP END
dist_rec.tax_recovery_override_flag,
-- bug4548700 added the following to forward contract details to
-- newly splitted lines.
dist_rec.award_id, --null
dist_rec.oke_contract_line_id, --null
dist_rec.oke_contract_deliverable_id, --null
dist_rec.org_id, --
dist_rec.Partial_funded_flag /* <> */
);
SELECT sum (req_line_quantity)
INTO x_total_dist_qty
FROM po_req_distributions
WHERE requisition_line_id = x_new_req_line_id;
UPDATE po_req_distributions
SET req_line_quantity = req_line_quantity + (x_new_line_quantity -
x_total_dist_qty)
WHERE distribution_id = (SELECT min(distribution_id)
FROM po_req_distributions
WHERE requisition_line_id = x_new_req_line_id);