[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Duplicate'
FROM CE_STATEMENT_RECON_GT_V csr --CE_STATEMENT_RECONCILIATIONS csr
WHERE csr.statement_line_id = X_statement_line_id
AND csr.reference_type = X_reference_type
AND csr.reference_id = X_reference_id
AND csr.current_record_flag = X_current_record_flag
AND ( X_row_id is null
OR csr.row_id <> chartorowid(X_row_id));
PROCEDURE Insert_Row( X_Row_id IN OUT NOCOPY VARCHAR2,
X_statement_line_id NUMBER,
X_reference_type VARCHAR2,
X_reference_id NUMBER,
X_org_id NUMBER,
X_legal_entity_id NUMBER,
X_reference_status VARCHAR2,
X_amount NUMBER DEFAULT NULL,
X_status_flag VARCHAR2,
X_action_flag VARCHAR2,
X_current_record_flag VARCHAR2,
X_auto_reconciled_flag VARCHAR2,
X_created_by NUMBER,
X_creation_date DATE,
X_last_updated_by NUMBER,
X_last_update_date DATE,
X_request_id NUMBER DEFAULT NULL,
X_program_application_id NUMBER DEFAULT NULL,
X_program_id NUMBER DEFAULT NULL,
X_program_update_date DATE DEFAULT NULL) IS
reference_type_tmp VARCHAR2(30);
CURSOR C_ROWID IS SELECT row_id FROM CE_STATEMENT_RECON_GT_V
WHERE statement_line_id = X_Statement_Line_Id
AND reference_type = reference_type_tmp
AND reference_id = X_Reference_Id
AND current_record_flag = x_current_record_flag;
CURSOR C_STMT_LOCK IS SELECT row_id, org_id, legal_entity_id
FROM CE_STATEMENT_RECON_GT_V
WHERE reference_type = reference_type_tmp AND
reference_id = X_reference_id AND
statement_line_id = X_statement_line_id AND
current_record_flag = 'Y'
FOR UPDATE of current_record_flag NOWAIT;
CURSOR C_LOCK IS SELECT row_id, org_id, legal_entity_id
FROM CE_STATEMENT_RECON_GT_V
WHERE reference_type = reference_type_tmp AND
reference_id = X_reference_id AND
current_record_flag = 'Y'
FOR UPDATE of current_record_flag NOWAIT;
cep_standard.debug('>>CE_STATEMENT_RECONS_PKG.insert_row 1');
SELECT cash_receipt_id
INTO X_cash_receipt_id
FROM ar_cash_receipt_history_all
WHERE cash_receipt_history_id = X_reference_id;
SELECT trx_type
INTO X_statement_type
FROM ce_statement_lines
WHERE statement_line_id = X_statement_line_id;
UPDATE CE_STATEMENT_RECONCILS_ALL SET current_record_flag = 'N'
WHERE rowid = X_Row_id;
UPDATE CE_STATEMENT_RECONCILS_ALL SET current_record_flag = 'N'
WHERE rowid = X_Row_id;
cep_standard.debug('INSERT INTO CE_STATEMENT_RECON_GT_V');
INSERT INTO CE_STATEMENT_RECONCILS_ALL(
statement_line_id,
reference_type,
reference_id,
status_flag,
amount,
current_record_flag,
auto_reconciled_flag,
org_id,
legal_entity_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(X_Statement_Line_Id,
Reference_Type_Tmp,
X_Reference_Id,
X_Status_flag,
X_amount,
X_Current_Record_Flag,
X_Auto_Reconciled_Flag,
Y_org_id,
Y_legal_entity_id,
X_Created_By,
X_Creation_Date,
X_last_updated_by,
X_last_update_date,
DECODE(X_auto_reconciled_flag,'Y',
X_request_id,
NULL),
DECODE(X_auto_reconciled_flag,'Y',
X_program_application_id,
NULL),
DECODE(X_auto_reconciled_flag,'Y',
X_program_id,
NULL),
DECODE(X_auto_reconciled_flag,'Y',
X_program_update_date,
NULL));
cep_standard.debug('end INSERT INTO CE_STATEMENT_RECON_GT_V');
DELETE from CE_RECONCILIATION_ERRORS
WHERE statement_line_id = X_statement_line_id;
cep_standard.debug('<
END Insert_Row;
PROCEDURE Insert_Row( X_Row_id IN OUT NOCOPY VARCHAR2,
X_statement_line_id NUMBER,
X_reference_type VARCHAR2,
X_reference_id NUMBER,
X_je_header_id NUMBER,
X_org_id NUMBER,
X_legal_entity_id NUMBER,
X_reference_status VARCHAR2,
X_amount NUMBER DEFAULT NULL,
X_status_flag VARCHAR2,
X_action_flag VARCHAR2,
X_current_record_flag VARCHAR2,
X_auto_reconciled_flag VARCHAR2,
X_created_by NUMBER,
X_creation_date DATE,
X_last_updated_by NUMBER,
X_last_update_date DATE,
X_request_id NUMBER DEFAULT NULL,
X_program_application_id NUMBER DEFAULT NULL,
X_program_id NUMBER DEFAULT NULL,
X_program_update_date DATE DEFAULT NULL) IS
reference_type_tmp VARCHAR2(30);
CURSOR C_ROWID IS SELECT row_id FROM CE_STATEMENT_RECON_GT_V
WHERE statement_line_id = X_Statement_Line_Id
AND reference_type = reference_type_tmp
AND reference_id = X_Reference_Id
AND je_header_id = X_je_header_id
AND current_record_flag = x_current_record_flag;
CURSOR C_LOCK IS SELECT row_id, org_id, legal_entity_id
FROM CE_STATEMENT_RECON_GT_V
WHERE reference_type = reference_type_tmp AND
reference_id = X_reference_id AND
je_header_id = X_je_header_id AND
-- statement_line_id = X_Statement_Line_Id AND bug 6888494
current_record_flag = 'Y'
FOR UPDATE of current_record_flag NOWAIT;
cep_standard.debug('>>CE_STATEMENT_RECONS_PKG.insert_row 2');
SELECT cash_receipt_id
INTO X_cash_receipt_id
FROM ar_cash_receipt_history_all
WHERE cash_receipt_history_id = X_reference_id;
SELECT trx_type
INTO X_statement_type
FROM ce_statement_lines
WHERE statement_line_id = X_statement_line_id;
UPDATE CE_STATEMENT_RECONCILS_ALL SET current_record_flag = 'N'
WHERE rowid = X_Row_id;
cep_standard.debug('INSERT INTO CE_STATEMENT_RECONCILS_ALL 2');
INSERT INTO CE_STATEMENT_RECONCILS_ALL(
statement_line_id,
reference_type,
reference_id,
je_header_id,
status_flag,
amount,
current_record_flag,
auto_reconciled_flag,
org_id,
legal_entity_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(X_Statement_Line_Id,
Reference_Type_Tmp,
X_Reference_Id,
X_je_header_id,
X_Status_flag,
X_amount,
X_Current_Record_Flag,
X_Auto_Reconciled_Flag,
Y_org_id,
Y_legal_entity_id,
X_Created_By,
X_Creation_Date,
X_last_updated_by,
X_last_update_date,
DECODE(X_auto_reconciled_flag,'Y',
X_request_id,
NULL),
DECODE(X_auto_reconciled_flag,'Y',
X_program_application_id,
NULL),
DECODE(X_auto_reconciled_flag,'Y',
X_program_id,
NULL),
DECODE(X_auto_reconciled_flag,'Y',
X_program_update_date,
NULL));
cep_standard.debug('end INSERT INTO CE_STATEMENT_RECONCILS_ALL 2');
DELETE from CE_RECONCILIATION_ERRORS
WHERE statement_line_id = X_statement_line_id;
cep_standard.debug('<
END Insert_Row;
SELECT statement_line_id,
reference_type,
reference_id,
status_flag,
current_record_flag,
auto_reconciled_flag
FROM CE_STATEMENT_RECON_GT_V
WHERE statement_line_id = X_statement_line_id AND
reference_type = X_reference_type AND
reference_id = X_reference_id
FOR UPDATE of Statement_Line_Id,reference_type, reference_id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row( X_Row_id IN OUT NOCOPY VARCHAR2,
X_statement_line_id NUMBER,
X_reference_type VARCHAR2,
X_reference_id NUMBER,
X_status VARCHAR2,
X_cleared_when_matched VARCHAR2,
X_current_record_flag VARCHAR2,
X_auto_reconciled_flag VARCHAR2,
X_created_by NUMBER,
X_creation_date DATE,
X_last_updated_by NUMBER,
X_last_update_date DATE) IS
BEGIN
UPDATE CE_STATEMENT_RECONCILS_ALL
SET
statement_line_id = X_Statement_Line_Id,
reference_type = X_Reference_Type,
reference_id = X_Reference_Id,
status_flag = X_Status,
current_record_flag = X_Current_Record_Flag,
auto_reconciled_flag = X_Auto_Reconciled_Flag,
last_updated_by = X_Last_Updated_By,
last_update_date = X_Last_Update_Date
WHERE rowid = X_Row_Id;
END Update_Row;
PROCEDURE Delete_Row(X_Row_id VARCHAR2) IS
BEGIN
DELETE FROM CE_STATEMENT_RECONCILS_ALL
WHERE rowid = X_Row_Id;
END Delete_Row;