[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
param_select_control IN number,
param_bordero_id IN number,
param_bordero_status IN varchar2,
param_select_account_id IN number,
param_option IN varchar2,
param_date IN date,
param_exit OUT NOCOPY number)
IS
var_document_status VARCHAR2(30);
var_selection_status VARCHAR2(30);
var_selection_control_chk NUMBER;
/* of all borderos with the same select account id */
/* and selection control id */
/* Cursor c3 - This cursor is used to change the document status */
/* previous_doc_status, and cancellation date */
/* of all documents from the same bordero that is */
/* being canceled, since this document was not */
/* canceled yet. */
/* Cursor c4 - This cursor is used to remittance occurrences from */
/* all documents of one bordero. */
CURSOR check1 IS
SELECT bordero_status,
bordero_type
FROM jl_br_ar_borderos
WHERE bordero_id = param_bordero_id
AND bordero_status in ('SELECTED', 'FORMATTED')
FOR UPDATE NOWAIT;
SELECT selection_control_id
FROM jl_br_ar_select_controls
WHERE selection_control_id = param_select_control
FOR UPDATE NOWAIT;
SELECT bordero_status
FROM jl_br_ar_borderos
WHERE selection_control_id = param_select_control
AND select_account_id = param_select_account_id;
SELECT document_status,
payment_schedule_id,
document_id
FROM jl_br_ar_collection_docs
WHERE bordero_id = param_bordero_id
AND document_status <> 'CANCELED';
SELECT gl_date,
occurrence_id,
gl_cancel_date,
flag_post_gl,
bank_occurrence_type, -- SLA Uptake - Bug#4301543
bank_occurrence_code, -- SLA Uptake - Bug#4301543
event_id -- bug 10349927
FROM jl_br_ar_occurrence_docs
WHERE document_id = var_document_id
AND bank_occurrence_type = 'REMITTANCE_OCCURRENCE'
AND occurrence_status <> 'CANCELED';
var_selection_status := 'CANCELED';
FETCH check2 INTO var_selection_control_chk;
/* Reverse Bordero status to SELECTED */
IF param_option = 'Reverse Bordero' THEN
var_document_status := 'SELECTED';
var_status := 'SELECTED';
UPDATE jl_br_ar_borderos SET
bordero_status = var_status,
cancellation_date = var_cancel_date
WHERE bordero_id = param_bordero_id;
UPDATE jl_br_ar_collection_docs SET
previous_doc_status = var_previous_status,
document_status = var_document_status,
cancellation_date = var_cancel_date
WHERE bordero_id = param_bordero_id
AND payment_schedule_id = var_payment_schedule
AND document_id = var_document_id;
UPDATE AR_PAYMENT_SCHEDULES SET
global_attribute12 = var_document_status, --bug 12606008
global_attribute11 = 'N',
global_attribute9 = 'MANUAL_RECEIPT' --bug 8971675
WHERE payment_schedule_id = var_payment_schedule;
UPDATE AR_PAYMENT_SCHEDULES SET
global_attribute12 = var_document_status, --bug 12606008
global_attribute13 = NULL, --bug 12636807
global_attribute14 = NULL, --bug 12636807
selected_for_receipt_batch_id = NULL,
global_attribute11 = 'N',
global_attribute9 = 'MANUAL_RECEIPT' --bug 8971675
WHERE payment_schedule_id = var_payment_schedule;
UPDATE jl_br_ar_occurrence_docs SET
occurrence_status= 'CANCELED',
gl_date = var_gl_date,
gl_cancel_date = var_gl_date_cancel,
flag_post_gl = var_flag_post_gl,
cancel_event_id = var_cancel_event_id -- SLA Uptake - Bug#4301543
WHERE document_id = var_document_id
AND occurrence_id = var_occurrence_id;
IF var_bordero_status <> 'SELECTED' THEN
flag := 2;
var_selection_status := 'SELECTED';
UPDATE jl_br_ar_select_accounts SET
cancellation_date = var_cancel_date
WHERE selection_control_id = param_select_control
AND select_account_id = param_select_account_id;
UPDATE jl_br_ar_select_controls SET
selection_status = var_selection_status,
cancellation_date = var_cancel_date
WHERE selection_control_id = param_select_control;