[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE PO_UPDATE_MGR(
X_errbuf OUT NOCOPY VARCHAR2,
X_retcode OUT NOCOPY VARCHAR2,
p_batch_size IN NUMBER,
p_num_workers IN NUMBER)
IS
BEGIN
--
-- Manager processing
--
AD_CONC_UTILS_PKG.submit_subrequests(
X_errbuf=>X_errbuf,
X_retcode=>X_retcode,
X_workerconc_app_shortname=>'PO',
X_workerconc_progname=>'POXUPWKR',
X_batch_size=>p_batch_size,
X_Num_Workers=>p_num_workers);
PROCEDURE PO_UPDATE_WKR(
X_errbuf OUT NOCOPY VARCHAR2,
X_retcode OUT NOCOPY VARCHAR2,
p_batch_size IN NUMBER,
p_worker_id IN NUMBER,
p_num_workers IN NUMBER)
IS
l_worker_id number;
l_update_name varchar2(30) := 'poxucpll.sql';
l_userid po_line_locations_all.last_updated_by%TYPE;
l_loginid po_line_locations_all.last_update_login%TYPE;
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_update_name,
p_worker_id,
p_num_workers,
p_batch_size, 0);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
p_batch_size,
TRUE);
UPDATE /*+ ROWID (poll) */ po_line_locations_all poll
SET poll.closed_for_receiving_date =
( select nvl(max(RT.transaction_date),
decode(poll.closed_code,
'FINALLY CLOSED',poll.closed_date,
'CLOSED',poll.closed_date,
'CLOSED FOR RECEIVING',poll.last_update_date,
NULL)
)
from rcv_transactions RT
where RT.TRANSACTION_TYPE IN ('RECEIVE','ACCEPT','CORRECT','MATCH')
and RT.po_line_location_id = poll.line_location_id
and poll.closed_code IN ('FINALLY CLOSED','CLOSED','CLOSED FOR RECEIVING')
)
,poll.closed_for_invoice_date =
( select nvl( max(AIN.invoice_date),
decode(poll.closed_code,
'FINALLY CLOSED',poll.closed_date,
'CLOSED',poll.closed_date,
'CLOSED FOR INVOICE',poll.last_update_date,
NULL)
)
from ap_invoice_distributions_all AID,
ap_invoices_all AIN,
po_distributions_all POD
where AID.invoice_id = AIN.invoice_id
and AID.po_distribution_id = POD.po_distribution_id
and POD.line_location_id = poll.line_location_id
and nvl(AID.reversal_flag,'N') NOT IN ('Y')
and poll.closed_code IN ('FINALLY CLOSED','CLOSED','CLOSED FOR INVOICE')
)
,poll.shipment_closed_date = decode(poll.closed_code,'FINALLY CLOSED',poll.closed_date,
'CLOSED',poll.closed_date,
NULL)
,last_update_date = sysdate
,last_updated_by = l_userid
,last_update_login = l_loginid
WHERE poll.rowid BETWEEN l_start_rowid and l_end_rowid
and poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
and poll.closed_code IN ('FINALLY CLOSED','CLOSED','CLOSED FOR RECEIVING','CLOSED FOR INVOICE')
and ( ( poll.closed_code IN ('CLOSED FOR RECEIVING')
AND poll.closed_for_receiving_date IS NULL)
OR (poll.closed_code IN ('CLOSED FOR INVOICE')
AND poll.closed_for_invoice_date IS NULL)
OR (poll.closed_code IN ('FINALLY CLOSED','CLOSED')
AND (poll.shipment_closed_date IS NULL
OR poll.closed_for_receiving_date IS NULL
OR poll.closed_for_invoice_date IS NULL))
);
ad_parallel_updates_pkg.processed_rowid_range(
l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
p_batch_size,
FALSE);