[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
bld_dml_flag DBMS_SQL.VARCHAR2_TABLE,--insert,update,delete
bld_temp_event_id DBMS_SQL.VARCHAR2_TABLE
);
PROCEDURE define_arrays( p_select_c IN INTEGER,
p_xla_ev_rec IN xla_events_type,
p_ev_rec IN ev_rec_type,
p_call_point IN NUMBER);
PROCEDURE get_column_values(p_select_c IN INTEGER,
p_xla_ev_rec IN xla_events_type,
p_call_point IN NUMBER,
p_ev_rec OUT NOCOPY ev_rec_type);
FUNCTION Get_Select_Cursor(p_xla_ev_rec IN xla_events_type,
p_call_point IN NUMBER)
RETURN INTEGER;
'SELECT ae.event_id,
ae.event_date,
ae.event_status_code,
ae.event_type_code
FROM '||xla_user||'.xla_events ae
WHERE ae.event_id = :dist_event_id
AND ae.application_id = 222';
' select ae.event_id ,
ae.event_date ,
ae.event_status_code,
ae.event_type_code
from xla_events ae,
xla_transaction_entities_upg xt
where xt.source_id_int_1 = :trx_id
and xt.entity_id = ae.entity_id
and nvl(ae.event_date,
to_date(''01-01-1900'',''DD-MM-YYYY'')) = :dist_gl_date
and ae.event_status_code <> ''P''
and ae.event_type_code = :override_event
and ae.application_id = 222
and xt.application_id = 222';
| Get_Select_Cursor
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS p_ev_rec IN Event input parameter record
| p_stmt OUT Build dynamic SQL statement buffer
|
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 07-OCT-2002 Herve Add the bind_variable b_xla_mode.
|
| Need to differentiate the SQL statement by execution mode.
|
| For example in Upgrade mode or in OLTP, for a postable document,
| distribution stamped with a event_id,
| trx_status complete,
| no status for the event
| exist_dist_gl_date null is abnormal.
|
| * In OLTP, the OLTP sql will populate the event status.
| * In Upgrade mode the distribution stamped with a event_id is impossible.
| * But in Batch mode this situation can happen, when a receipt is created
| RECP_CREATE, then submit autoreceipt process in batch mode to create the
| the RECEIPT REMITTANCE record with the RECP_REMIT event.
| The previous RECP_CREATE event causes the situation described happen.
| - distribution is stamped True.
| - trx_complete
| - no status event and exist gl date is null because OLTP sql not executed.
| So by adding the clause based on :b_xla_mode, avoidance to retrieve the existing
| RECP_CREATE is accomplished so that the same situation of Upgrade happens.
|
*=======================================================================*/
PROCEDURE Build_Stmt(p_xla_ev_rec IN xla_events_type,
p_call_point IN NUMBER,
p_stmt OUT NOCOPY VARCHAR2) IS
l_select_clause VARCHAR2(10000);
l_select_clause :=
' select
tty.post_to_gl POSTTOGL ,
tty.type TRX_TYPE ,
decode(ct.complete_flag,
''Y'',''C'',
''I'') COMP_FLAG ,
ctlgd.customer_trx_id TRX_ID ,
ct.trx_number TRX_NUMBER ,
ct.org_id ORG_ID ,
decode(nvl(ctlgd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
nvl(ctlgd1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
decode(ctlgd.posting_control_id,
ctlgd1.posting_control_id, tty.type || ''_CREATE'',
tty.type || ''_UPDATE''),
tty.type || ''_UPDATE'') OVERRIDE_EVENT ,
ctlgd.posting_control_id PSTID ,
nvl(ctlgd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
ctlgd.event_id EXIST_EVENT ,
'''' EVENT_ID ,
to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
'''' EVENT_TYPE ,
''X'' EVENT_STATUS ,
ct.trx_date TRANSACTION_DATE,
ct.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
tty.type || ''_UPDATE''),
tty.type || ''_UPDATE'') ,
ctlgd.posting_control_id,
nvl(ctlgd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
ctlgd.event_id ,
ct.trx_date,
ct.legal_entity_id'|| CRLF;
l_select_clause :=
--BUG#5415512
' SELECT /*+ leading(ctlgd,evn,ev1) use_nl(evn,ev1) */
ctlgd.rowid,
ev1.event_id ';
END IF; --create or update mode
l_select_clause :=
' select
decode(tty.post_to_gl,''Y'',tty.post_to_gl,nvl(tty.adj_post_to_gl,''N'')) POSTTOGL ,
''ADJ'' TRX_TYPE ,
decode(adj.status,
''A'',''C'',
''I'') COMP_FLAG ,
adj.adjustment_id TRX_ID ,
adj.adjustment_number TRX_NUMBER ,
adj.org_id ORG_ID,
''ADJ_CREATE'' OVERRIDE_EVENT ,
adj.posting_control_id PSTID ,
nvl(adj.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
adj.event_id EXIST_EVENT ,
'''' EVENT_ID ,
to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
'''' EVENT_TYPE ,
''X'' EVENT_STATUS ,
ct.trx_date TRANSACTION_DATE,
ct.legal_entity_id LEGAL_ENTITY_ID' || CRLF;
l_select_clause :=
' SELECT adj.rowid,
ev1.event_id ';
l_select_clause :=
' select
crh.postable_flag POSTTOGL ,
''RECP'' TRX_TYPE ,
decode(crh.status,
''APPROVED'', ''I'',
''C'') COMP_FLAG ,
crh.cash_receipt_id TRX_ID ,
cr.receipt_number TRX_NUMBER ,
cr.org_id ORG_ID,
decode(cr.type,
''MISC'',''MISC_'',
'''') ||
decode(crh.created_from,
''RATE ADJUSTMENT TRIGGER'', ''RECP_RATE_ADJUST'',
decode(crh.status,
''REVERSED'',''RECP_REVERSE'',
decode(crh1.first_posted_record_flag,
'''', ''RECP_CREATE'',
decode(decode(crh.postable_flag,
''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))),
nvl(crh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
decode(crh.posting_control_id,
crh1.posting_control_id, ''RECP_CREATE'',
''RECP_UPDATE''),
''RECP_UPDATE'')))) OVERRIDE_EVENT,
crh.posting_control_id PSTID ,
decode(crh.postable_flag,
''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))) GL_DATE,
crh.event_id EXIST_EVENT,
'''' EVENT_ID ,
to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
'''' EVENT_TYPE ,
''X'' EVENT_STATUS ,
cr.receipt_date TRANSACTION_DATE,
cr.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
''RECP_UPDATE''),
''RECP_UPDATE'')))),
decode(crh.postable_flag,
''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))),
crh.event_id,
cr.receipt_date,
cr.legal_entity_id' || CRLF;
|The where clause below is appended to the Select which pulls data |
|from the inline query. Hence it appears odd that order by should have|
|a where but nevertheless it is required |
+---------------------------------------------------------------------*/
/** BUG 6660834
We can directly use the field OVERRIDE_EVENT in order by clause, s the value
itself will maintain the order we are expecting in all the cases except in case
where we have both RECP_UPDATE and RECP_REVERSE in the same call,this
is only possible in case of an upgrade and this is not used for upgrade*/
/*l_order_by_clause := l_order_by_clause ||
'ORDER BY TRX_ID,
decode(OVERRIDE_EVENT,
''RECP_CREATE'' ,1,
''RECP_UPDATE'' ,2,
''RECP_RATE_ADJUST'' ,3,
''RECP_REVERSE'' ,6,
''MISC_RECP_CREATE'' ,1,
''MISC_RECP_UPDATE'' ,2,
''MISC_RECP_RATE_ADJUST'' ,3,
''MISC_RECP_REVERSE'', 6,
7),
GL_DATE,
PSTID desc ' || CRLF;*/
log('l_select_clause = ' || l_select_clause);
l_select_clause :=
' SELECT crh.rowid,
ev1.event_id ';
END IF; --create or update mode
l_select_clause := l_select_clause || l_from_clause || l_where_clause ||
l_where_parm_clause_crh || l_group_by_clause || l_union || CRLF;
l_select_clause := l_select_clause ||
' select
''Y'' POSTTOGL ,
''RECP'' TRX_TYPE ,
''C'' COMP_FLAG ,
mcd.cash_receipt_id TRX_ID ,
cr.receipt_number TRX_NUMBER ,
cr.org_id ORG_ID,
decode(mcd.created_from,
''RATE ADJUSTMENT TRIGGER'', ''MISC_RECP_RATE_ADJUST'',
decode(SUBSTRB(mcd.created_from,1,19),
''ARP_REVERSE_RECEIPT'',''MISC_RECP_REVERSE'',
decode(nvl(crh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
decode(crh.posting_control_id,
mcd.posting_control_id, ''MISC_RECP_CREATE'',
''MISC_RECP_UPDATE''),
''MISC_RECP_UPDATE''))) OVERRIDE_EVENT,
mcd.posting_control_id PSTID ,
nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
mcd.event_id EXIST_EVENT,
'''' EVENT_ID ,
to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
'''' EVENT_TYPE ,
''X'' EVENT_STATUS,
cr.receipt_date TRANSACTION_DATE,
cr.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
log('l_select_clause ' || l_select_clause);
''MISC_RECP_UPDATE''),
''MISC_RECP_UPDATE''))),
nvl(mcd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
mcd.event_id ,
cr.receipt_date,
cr.legal_entity_id ' || CRLF;
''MISC_RECP_UPDATE'' ,2,
''MISC_RECP_RATE_ADJUST'',3,
''MISC_RECP_REVERSE'' ,6,
7),
GL_DATE,
PSTID desc ';
log('l_select_clause = ' || l_select_clause);
l_select_clause :=
' SELECT mcd.rowid,
ev1.event_id ';
END IF; --create or update mode
l_select_clause := l_select_clause || l_from_clause || l_where_clause ||
l_where_parm_clause_crh || l_group_by_clause || l_union || CRLF;
l_select_clause := l_select_clause ||
' select
decode(app.postable,
''N'',''N'',
''Y'') POSTTOGL ,
''RECP'' TRX_TYPE ,
decode(NVL(app.confirmed_flag,''Y''),
''Y'',''C'',
''N'') COMP_FLAG ,
cr.cash_receipt_id TRX_ID , --BUG#3554871
cr.receipt_number TRX_NUMBER ,
cr.org_id ORG_ID ,
decode(crh.created_from,
''RATE ADJUSTMENT TRIGGER'', ''RECP_RATE_ADJUST'',
decode(crh.status,
''REVERSED'',''RECP_REVERSE'',
decode(crh1.first_posted_record_flag,
'''', ''RECP_CREATE'',
decode(nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
nvl(crh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
decode(app.posting_control_id,
crh1.posting_control_id, ''RECP_CREATE'',
''RECP_UPDATE''),
''RECP_UPDATE'')))) OVERRIDE_EVENT,
app.posting_control_id PSTID ,
nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
app.event_id EXIST_EVENT ,
'''' EVENT_ID ,
to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
'''' EVENT_TYPE ,
''X'' EVENT_STATUS ,
cr.receipt_date TRANSACTION_DATE,
cr.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
''RECP_UPDATE''),
''RECP_UPDATE'')))),
nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
app.event_id ,
cr.receipt_date,
cr.legal_entity_id' || CRLF;
''RECP_UPDATE'' ,2,
''RECP_RATE_ADJUST'',3,
''RECP_REVERSE'' ,6,
7),
GL_DATE,
PSTID desc ';
l_select_clause :=
' SELECT app.rowid,
ev1.event_id ';
log('l_select_clause ' || l_select_clause);
END IF; --create or update mode
l_select_clause := l_select_clause || l_from_clause || l_where_clause ||
l_where_parm_clause_crh || l_group_by_clause || l_union || CRLF;
l_select_clause := l_select_clause ||
' select
decode(app.postable,
''N'',''N'',
''Y'') POSTTOGL ,
''CM'' TRX_TYPE ,
decode(NVL(app.confirmed_flag,''Y''),
''Y'',''C'',
''N'') COMP_FLAG ,
ctlgd.customer_trx_id TRX_ID, --BUG#3554871
ct.trx_number TRX_NUMBER ,
app.org_id ORG_ID ,
decode(nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
nvl(ctlgd.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
decode(app.posting_control_id,
ctlgd.posting_control_id, ''CM_CREATE'',
''CM_UPDATE''),
''CM_UPDATE'') OVERRIDE_EVENT ,
app.posting_control_id PSTID ,
nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')) GL_DATE,
app.event_id EXIST_EVENT ,
'''' EVENT_ID ,
to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
'''' EVENT_TYPE ,
''X'' EVENT_STATUS ,
ct.trx_date TRANSACTION_DATE,
ct.legal_entity_id LEGAL_ENTITY_ID ' || CRLF;
''CM_UPDATE''),
''CM_UPDATE''),
nvl(app.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
app.event_id,
ct.trx_date,
ct.legal_entity_id ' || CRLF;
l_select_clause :=
' SELECT app.rowid,
ev1.event_id ';
END IF; --create or update mode
l_select_clause :=
' select
tty.post_to_gl POSTTOGL ,
''BILL'' TRX_TYPE ,
decode(trh.status,
''INCOMPLETE'', ''I'',
''PENDING_ACCEPTANCE'',''I'',
''C'') COMP_FLAG ,
trh.customer_trx_id TRX_ID ,
ct.trx_number TRX_NUMBER ,
ct.org_id ORG_ID,
decode(trh.event,
''INCOMPLETE'' , ''BILL_CREATE'',
''ACCEPTED'' , ''BILL_CREATE'',
''COMPLETED'' , decode(trh.status,
''PENDING_ACCEPTANCE'', ''BILL_CREATE'',
''PENDING_REMITTANCE'', ''BILL_CREATE'',
''NO_EVENT''),
''CANCELLED'' , ''BILL_REVERSE'',
decode(trh1.first_posted_record_flag,
'''', ''BILL_CREATE'',
decode(nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
nvl(trh1.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY'')),
decode(trh.posting_control_id,
trh1.posting_control_id, ''BILL_CREATE'',
''BILL_UPDATE''),
''BILL_UPDATE''))) OVERRIDE_EVENT,
trh.posting_control_id PSTID ,
decode(tty.post_to_gl,
''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))) GL_DATE,
trh.event_id EXIST_EVENT ,
'''' EVENT_ID ,
to_date(''01-01-1900'',''DD-MM-YYYY'') ACCOUNTING_DATE,
'''' EVENT_TYPE ,
''X'' EVENT_STATUS ,
ct.trx_date TRANSACTION_DATE,
ct.legal_entity_id LEGAL_ENTITY_ID ';
''BILL_UPDATE''),
''BILL_UPDATE''))),
decode(tty.post_to_gl,
''N'', to_date(''01-01-1900'',''DD-MM-YYYY''),
nvl(trh.gl_date,to_date(''01-01-1900'',''DD-MM-YYYY''))),
trh.event_id ,
ct.trx_date,
ct.legal_entity_id ' || CRLF;
l_select_clause :=
' SELECT trh.rowid,
ev1.event_id ';
END IF; --create or update mode
p_stmt := l_select_clause ||
l_from_clause ||
l_where_clause ||
l_where_parm_clause ||
l_group_by_clause ||
l_order_by_clause ;
| Define positional place holders in the select list
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Get_Select_Cursor
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS p_select_c IN Cursor handle
| p_xla_ev_rec IN Events parameter record
| p_ev_rec IN Events record
|
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
*=======================================================================*/
PROCEDURE define_arrays( p_select_c IN INTEGER,
p_xla_ev_rec IN xla_events_type,
p_ev_rec IN ev_rec_type,
p_call_point IN NUMBER) IS
BEGIN
log( 'ARP_XLA_EVENTS.define_arrays()+' );
dbms_sql.define_array(p_select_c, 1 , p_ev_rec.posttogl
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 2 , p_ev_rec.trx_type
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 3 , p_ev_rec.trx_status
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 4 , p_ev_rec.trx_id
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 5 , p_ev_rec.trx_number
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 6 , p_ev_rec.org_id
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 7 , p_ev_rec.override_event
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 8 , p_ev_rec.pstid
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 9 , p_ev_rec.dist_gl_date
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 10, p_ev_rec.ev_match_event_id
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 11 , p_ev_rec.dist_event_id
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 12, p_ev_rec.ev_match_gl_date
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 13, p_ev_rec.ev_match_type
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 14, p_ev_rec.ev_match_status
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 15, p_ev_rec.transaction_date
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 16, p_ev_rec.legal_entity_id
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 1 , p_ev_rec.dist_row_id
, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array(p_select_c, 2 , p_ev_rec.dist_event_id
, MAX_ARRAY_SIZE, STARTING_INDEX );
| Gets the values in select list and stores them in the target
| event record table variable
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Get_Column_Values
| Upd_Dist
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS p_select_c IN Cursor handle
| p_xla_ev_rec IN Events parameter record
| p_ev_rec OUT Events record
|
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
*=======================================================================*/
PROCEDURE get_column_values(p_select_c IN INTEGER,
p_xla_ev_rec IN xla_events_type,
p_call_point IN NUMBER,
p_ev_rec OUT NOCOPY ev_rec_type) IS
BEGIN
log('ARP_XLA_EVENTS.get_column_values (+)');
dbms_sql.column_value(p_select_c, 1 , p_ev_rec.posttogl);
dbms_sql.column_value(p_select_c, 2 , p_ev_rec.trx_type);
dbms_sql.column_value(p_select_c, 3 , p_ev_rec.trx_status);
dbms_sql.column_value(p_select_c, 4 , p_ev_rec.trx_id);
dbms_sql.column_value(p_select_c, 5 , p_ev_rec.trx_number);
dbms_sql.column_value(p_select_c, 6 , p_ev_rec.org_id);
dbms_sql.column_value(p_select_c, 7 , p_ev_rec.override_event);
dbms_sql.column_value(p_select_c, 8 , p_ev_rec.pstid);
dbms_sql.column_value(p_select_c, 9 , p_ev_rec.dist_gl_date);
dbms_sql.column_value(p_select_c, 10 , p_ev_rec.dist_event_id);
dbms_sql.column_value(p_select_c, 11, p_ev_rec.ev_match_event_id);
dbms_sql.column_value(p_select_c, 12, p_ev_rec.ev_match_gl_date);
dbms_sql.column_value(p_select_c, 13, p_ev_rec.ev_match_type);
dbms_sql.column_value(p_select_c, 14, p_ev_rec.ev_match_status);
dbms_sql.column_value(p_select_c, 15, p_ev_rec.transaction_date);
dbms_sql.column_value(p_select_c, 16, p_ev_rec.legal_entity_id);
dbms_sql.column_value(p_select_c, 1 , p_ev_rec.dist_row_id);
dbms_sql.column_value(p_select_c, 2 , p_ev_rec.dist_event_id);
| PUBLIC PROCEDURE Get_Select_Cursor
|
| DESCRIPTION
| Builds Select statement, opens cursor, parses it, defines place
| holders for select list, binds variables and returns a cursor
| handle.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Upd_Dist
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS p_xla_ev_rec IN Events parameter record
|
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
*=======================================================================*/
FUNCTION Get_Select_Cursor(p_xla_ev_rec IN xla_events_type,
p_call_point IN NUMBER) RETURN INTEGER IS
l_select_c INTEGER;
log('ARP_XLA_EVENTS.Get_Select_Cursor ()+');
l_select_c := dbms_sql.open_cursor;
log('Parsing select stmt');
dbms_sql.parse(l_select_c, l_stmt, dbms_sql.v7);
define_arrays(p_select_c => l_select_c,
p_xla_ev_rec => p_xla_ev_rec,
p_ev_rec => l_ev_rec,
p_call_point => p_call_point);
dbms_sql.bind_variable(l_select_c, ':b_xla_mode', p_xla_ev_rec.xla_mode);
dbms_sql.bind_variable(l_select_c, ':b_xla_from_doc_id', p_xla_ev_rec.xla_from_doc_id);
dbms_sql.bind_variable(l_select_c, ':b_xla_to_doc_id', p_xla_ev_rec.xla_to_doc_id);
dbms_sql.bind_variable(l_select_c, ':b_xla_req_id', p_xla_ev_rec.xla_req_id);
dbms_sql.bind_variable(l_select_c, ':b_xla_dist_id', p_xla_ev_rec.xla_dist_id);
log('ARP_XLA_EVENTS.Get_Select_Cursor (-)');
return(l_select_c);
log('EXCEPTION: ARP_XLA_EVENTS.Get_Select_Cursor:'||SQLERRM);
END Get_Select_Cursor;
| Creates, updates and deletes events for the transactions
| INV, DM, CM, CB, GUAR, DEP
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Execute
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS
| p_xla_ev_rec IN xla_events_type
|
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 10-SEP-2003 Herve Yu
| Use the set_of_books_id for now as the ledger_id bug#3135769
| we might need to come back on this point later after the uptake
| of ledger architecture project.
*=======================================================================*/
PROCEDURE Create_All_Events(p_xla_ev_rec IN xla_events_type) IS
/*---------------------------------------------------------------------+
| Main cursor which gets transaction data, and event data for decision|
| making on which events require to be created, updated or deleted. |
+---------------------------------------------------------------------*/
TYPE get_tran_data_type IS REF CURSOR;
l_select_c BINARY_INTEGER;
SELECT 'X'
FROM xla_events_int_gt
WHERE entity_id IS NULL;
l_select_c := Get_Select_Cursor(p_xla_ev_rec => p_xla_ev_rec,
p_call_point => 1);
l_ignore := dbms_sql.execute( l_select_c );
log( 'Fetching select stmt');
l_rows_fetched := dbms_sql.fetch_rows(l_select_c);
get_column_values(p_select_c => l_select_c,
p_xla_ev_rec => p_xla_ev_rec,
p_ev_rec => ev_rec,
p_call_point => 1);
IF( dbms_sql.is_open( l_select_c) ) THEN
dbms_sql.close_cursor( l_select_c );
IF( dbms_sql.is_open( l_select_c ) ) THEN
dbms_sql.close_cursor( l_select_c );
log('Number of rows selected : '|| ev_rec.trx_id.COUNT);
select ae.event_id ,
ae.event_date ,
ae.event_status_code,
ae.event_type_code
into ev_rec.ev_match_event_id(i) ,
ev_rec.ev_match_gl_date(i) ,
ev_rec.ev_match_status(i) ,
ev_rec.ev_match_type(i)
from xla_events ae
where ev_rec.dist_event_id(i) = ae.event_id;
'INV_UPDATE','DM_UPDATE','DEP_UPDATE','CB_UPDATE','CM_UPDATE','GUAR_UPDATE')
THEN
select ae2.event_id ,
ae2.event_date ,
ae2.event_status_code,
ae2.event_type_code
into ev_rec.ev_match_event_id(i) ,
ev_rec.ev_match_gl_date(i) ,
ev_rec.ev_match_status(i) ,
ev_rec.ev_match_type(i)
from xla_events ae2
where ae2.application_id = 222
and ae2.event_id IN
( select MAX( ae.event_id )
from xla_events ae,
xla_transaction_entities_upg xt,
ra_customer_trx_all trx
where trx.customer_trx_id = ev_rec.trx_id(i)
and NVL(xt.source_id_int_1,-99) = trx.customer_trx_id
and xt.entity_code = 'TRANSACTIONS'
and xt.ledger_id = trx.set_of_books_id
and xt.entity_id = ae.entity_id
and xt.application_id = 222
and ae.application_id = 222
and nvl(ae.event_date,
to_date('01-01-1900','DD-MM-YYYY')) = ev_rec.dist_gl_date(i)
and ae.event_status_code <> 'P'
and ae.event_type_code IN
('INV_CREATE','DM_CREATE','DEP_CREATE','CB_CREATE','CM_CREATE','GUAR_CREATE',
'INV_UPDATE','DM_UPDATE','DEP_UPDATE','CB_UPDATE','CM_UPDATE','GUAR_UPDATE')
)
FOR UPDATE OF ae2.event_id NOWAIT;
('BILL_CREATE','BILL_UPDATE','BILL_REVERSE')
THEN
select ae2.event_id ,
ae2.event_date ,
ae2.event_status_code,
ae2.event_type_code
into ev_rec.ev_match_event_id(i) ,
ev_rec.ev_match_gl_date(i) ,
ev_rec.ev_match_status(i) ,
ev_rec.ev_match_type(i)
from xla_events ae2
where ae2.application_id = 222
and ae2.event_id IN
( select MAX( ae.event_id )
from xla_events ae,
xla_transaction_entities_upg xt,
ra_customer_trx_all trx
where trx.customer_trx_id = ev_rec.trx_id(i)
and NVL(xt.source_id_int_1,-99) = trx.customer_trx_id
and xt.entity_code = 'BILLS_RECEIVABLE'
and xt.ledger_id = trx.set_of_books_id
and xt.entity_id = ae.entity_id
and xt.application_id = 222
and ae.application_id = 222
and nvl(ae.event_date,
to_date('01-01-1900','DD-MM-YYYY')) = ev_rec.dist_gl_date(i)
and ae.event_status_code <> 'P'
and ev_rec.override_event(i) = ae.event_type_code )
FOR UPDATE OF ae2.event_id NOWAIT;
('RECP_CREATE','RECP_RATE_ADJUST','RECP_UPDATE','RECP_REVERSE',
'MISC_RECP_CREATE','MISC_RECP_RATE_ADJUST','MISC_RECP_UPDATE','MISC_RECP_REVERSE')
THEN
select ae2.event_id ,
ae2.event_date ,
ae2.event_status_code,
ae2.event_type_code
into ev_rec.ev_match_event_id(i) ,
ev_rec.ev_match_gl_date(i) ,
ev_rec.ev_match_status(i) ,
ev_rec.ev_match_type(i)
from xla_events ae2
where ae2.application_id = 222
and ae2.event_id IN
( select MAX( ae.event_id )
from xla_events ae,
xla_transaction_entities_upg xt,
ar_Cash_receipts_all cr
where cr.cash_receipt_id = ev_rec.trx_id(i)
and NVL(xt.source_id_int_1,-99) = cr.cash_receipt_id
and xt.entity_code = 'RECEIPTS'
and xt.ledger_id = cr.set_of_books_id
and xt.entity_id = ae.entity_id
and xt.application_id = 222
and ae.application_id = 222
and nvl(ae.event_date,
to_date('01-01-1900','DD-MM-YYYY')) = ev_rec.dist_gl_date(i)
and ae.event_status_code <> 'P'
and DECODE(ev_rec.override_event(i),
'RECP_CREATE', ae.event_type_code,
'MISC_RECP_CREATE', ae.event_type_code,
ev_rec.override_event(i) ) = ae.event_type_code )
FOR UPDATE OF ae2.event_id NOWAIT;
select ae2.event_id ,
ae2.event_date ,
ae2.event_status_code,
ae2.event_type_code
into ev_rec.ev_match_event_id(i) ,
ev_rec.ev_match_gl_date(i) ,
ev_rec.ev_match_status(i) ,
ev_rec.ev_match_type(i)
from xla_events ae2
where ae2.application_id = 222
and ae2.event_id IN
( select MAX( ae.event_id )
from xla_events ae,
xla_transaction_entities_upg xt,
ar_adjustments_all adj
where adj.adjustment_id = ev_rec.trx_id(i)
and NVL(xt.source_id_int_1,-99) = adj.adjustment_id
and xt.entity_code = 'ADJUSTMENTS'
and xt.ledger_id = adj.set_of_books_id
and xt.entity_id = ae.entity_id
and xt.application_id = 222
and ae.application_id = 222
and nvl(ae.event_date,
to_date('01-01-1900','DD-MM-YYYY')) = ev_rec.dist_gl_date(i)
and ae.event_status_code <> 'P'
and ev_rec.override_event(i) = ae.event_type_code )
FOR UPDATE OF ae2.event_id NOWAIT;
process continue and create a new event of type UPDATE.
This code flow gets triggered only in cases where the concerned document already
has an event of type CREATE,will explictly override the event_type_code with
that of event type UPDATE to ensure that there exist only one event of type CREATE
for given document*/
IF SQLCODE = -54 THEN
ev_rec.override_event(i) :=
CASE ev_rec.override_event(i)
WHEN 'INV_CREATE' THEN 'INV_UPDATE'
WHEN 'DM_CREATE' THEN 'DM_UPDATE'
WHEN 'DEP_CREATE' THEN 'DEP_UPDATE'
WHEN 'CB_CREATE' THEN 'CB_UPDATE'
WHEN 'CM_CREATE' THEN 'CM_UPDATE'
WHEN 'GUAR_CREATE' THEN 'GUAR_UPDATE'
WHEN 'RECP_CREATE' THEN 'RECP_UPDATE'
WHEN 'MISC_RECP_CREATE' THEN 'MISC_RECP_UPDATE'
END;
|need to figure out what the previous event insert did is to make |
|a decision as regards the construction of the event type as Create|
|or modify. |
+------------------------------------------------------------------*/
l_change_matrix := Change_Matrix(
ev_rec.trx_status(i) ,
ev_rec.dist_gl_date(i) ,
ev_rec.ev_match_gl_date(i) ,
ev_rec.ev_match_status(i) ,
ev_rec.posttogl(i));
|After the Bulk Insert and Updates to Events table. |
|Latching occurs in the end. |
+-----------------------------------------------------------*/
/* IF ((NOT ev_match_flg)
AND (l_change_matrix IN (1.01,1.03,1.07,1.09,1.13,1.14,
1.16,1.17))) THEN */
/*------------------------------------------------+
|Latching is implicit to ev_rec.ev_event_id for |
|Update or ev_rec.ev_temp_event_id for Insert |
|for all above change matrix values not in the |
|IF construct below. For others explicit latching|
+------------------------------------------------*/
/* IF (l_change_matrix IN (1.01, 1.09, 1.13, 1.17)) THEN
ev_rec.dist_event_id(i) := ev_rec.ev_match_event_id(i);
| GLDate needs to be updated on that event. |
| Typically when a invoice with rules arrear has some |
| new lines entered then the transaction only has modal |
| REC distributions but the GLDate will be set the last |
| FORECASt REV_RECOGNITION date. |
+---------------------------------------------------------*/
l_revrun := 'X';
|Update existing Event |
|Matrix - Dist - Description |
| Event |
|1.02 - T - Update event gl date |
|1.03 - T, F - Update Status = Incomplete |
|1.04 - T - Update gl date, Status = Incomplete |
|1.07 - T,F - Update Status = Unprocessed |
|1.08 - T - Update gl date, Status = Unprocessed |
|1.10 - T - Update gl date |
|1.14 - T,F - Status = Incomplete |
|1.16 - T,F - Status = Unprocessed |
|1.08 - F - Update gl date, Status = Unprocesse |
| if Acct_asg created for one line |
|1.02 - F - Update gl date if ACT_ASG created |
|1.04 - F - Update gl date if ACT_ASG created |
|1.10 - F - Update gl date if ACT_ASG created |
|1.09 - T - For the case of cleaning events |
| when update REV_REC event GL Dates. |
|1.23 -T,F - Update of a postable trx to be |
| unpostable Bug#3320427 |
|1.22 -T,F - Update of a unpostable trx to be |
| postable Bug#3320427 |
+---------------------------------------------------------*/
--BUG#3999572
IF (l_change_matrix IN ('1.13', '1.14', '1.15', '1.16', '1.17', '1.18', '1.19', '1.20', '1.21', '1.23'))
THEN NULL;
log('Entered Update Built event construct ');
| Typically this can happens when user update the GL_Date on a inv |
| with rules on which REV_RECOGNITION has run. In this case the |
| the xla_events table contains multiple REV_RECOGNITION events with |
| different GLDate, when user updates the GL Date on the header |
| of the document, the GLDates for all the distributions related |
| to that document are updated to the new GLDate. |
| In this case, we need : |
| Reset the event_id of the distributions to NULL. |
| Conserve only one event of one type and a GLDate. |
| The denormalise mode should restamped the distributions with the |
| correct event_id. |
+--------------------------------------------------------------------*/
keep_flag := TRUE;
xla_events_pub_pkg.delete_event
( p_event_source_info => l_event_source_info,
p_event_id => ev_rec.ev_match_event_id(i),
p_valuation_method => NULL,
p_security_context => l_security_context);
END IF; --event needs to be deleted
END IF;--if OLTP then check whether existing events need to be deleted
END IF; --Update event condition
log('Building an Event in Insert new' ||
' event construct ');
|cell needs to be populated for update, so make sure|
|contiguous null cell is created. |
+---------------------------------------------------*/
bld_ev_ent(l_build_ctr).event_id := '';
END IF; --Insert event condition
|May be in update construct above. |
+----------------------------------------------------*/
/*---------------------------------------------------------+
|Add an event to the events cache table for the current |
|transaction being processed. Get the hash index using |
|hash function and ascertain if the event has been cached |
+---------------------------------------------------------*/
/*-----------------------------------------------------------+
|Set the previous row id of the distribution and trx id |
|used to reinitialize caches or skip processing duplicate |
|rows. |
+-----------------------------------------------------------*/
--prev_distid := ev_rec.dist_id(i); same dist is not reprocessed
|Call the xla events api passing it the tables for Bulk Insert, Update|
|On return for inserted rows, the event_id will be returned and the |
|distributions will be updated with this event id using the temp event|
|id which will ascertain the mapping of internal id's to actual ids |
|for a distribution.To be replaced by call to xla events api commit |
|issued by owning product. |
+---------------------------------------------------------------------*/
/*-------------------------------------------------------------+
| Insert into Events table, to be replaced with XLA apis |
+-------------------------------------------------------------*/
IF p_xla_ev_rec.xla_mode IN ('U','B') AND test_flag = 'N' THEN
log('xla_events_pub_pkg.create_bulk_events xla_mode IN (U,B)');
INSERT INTO xla_events_int_gt (
APPLICATION_ID
, LEGAL_ENTITY_ID
, LEDGER_ID
, ENTITY_CODE
, TRANSACTION_NUMBER
, SOURCE_ID_INT_1
, EVENT_TYPE_CODE
, EVENT_STATUS_CODE
, EVENT_DATE
, SECURITY_ID_INT_1
, TRANSACTION_DATE )
VALUES (
222
, bld_ev_ent(m).reference_num_1 -- LEGAL_ENTITY_ID
, arp_standard.sysparm.set_of_books_id -- LEDGER_ID
, l_entity_code -- ENTITY_CODE
, bld_ev_ent(m).transaction_number -- TRANSACTION_NUMBER
, bld_ev_ent(m).source_id_int_1 -- SOURCE_ID_INT_1
, bld_ev_ent(m).event_type_code -- EVENT_TYPE_CODE
, bld_ev_ent(m).event_status_code -- EVENT_STATUS_CODE
, bld_ev_ent(m).event_date -- EVENT_DATE
, bld_ev_ent(m).security_id_int_1 -- SECURITY_ID_INT_1
, bld_ev_ent(m).transaction_date); -- TRANSACTION_DATE
|Bulk update the distributions already existing in the Database|
|with the modified gl date or status, if unchanged these should|
|retain their default original values in the assignments below.|
+--------------------------------------------------------------*/
IF p_xla_ev_rec.xla_mode = 'O'
--{Work around waiting for bulk mode
-- OR p_xla_ev_rec.xla_mode = 'B'
--}
THEN
IF bld_ev_ent.COUNT > 0 THEN
FOR m IN bld_ev_ent.FIRST .. bld_ev_ent.LAST LOOP
/*----------------------------------------------------------+
|Set the event source details |
+----------------------------------------------------------*/
l_event_source_info.application_id := 222;
log('XLA_EVENTS_PUB_PKG.update_event');
XLA_EVENTS_PUB_PKG.update_event(
p_event_source_info => l_event_source_info ,
p_event_id => bld_ev_ent(m).event_id ,
p_event_type_code => bld_ev_ent(m).event_type_code ,
p_event_date => bld_ev_ent(m).event_date ,
p_event_status_code => bld_ev_ent(m).event_status_code ,
p_valuation_method => '' ,
p_security_context => l_security_context );
SELECT a.transaction_number INTO trxn_number
FROM xla_transaction_entities_upg a,
xla_events b
WHERE NVL(a.source_id_int_1,-99) = bld_ev_ent(m).source_id_int_1
AND b.event_id = bld_ev_ent(m).event_id
AND a.entity_id = b.entity_id
AND a.security_id_int_1 = bld_ev_ent(m).security_id_int_1
AND a.application_id = 222;
log('EXCEPTION: XLA TRANSACTION NUMBER UPDATE');
XLA_EVENTS_PUB_PKG.UPDATE_TRANSACTION_NUMBER(
p_event_source_info => l_event_source_info,
p_transaction_number => bld_ev_ent(m).transaction_number,
p_valuation_method => '',
p_security_context => l_security_context ,
p_event_id => bld_ev_ent(m).event_id );
END IF; --end insert or update events in OLTP mode
|Denormalize the event id which has been inserted into the |
|database, and update the event id column in the dist table|
|This denormalization is used by the extract process. |
|The internal negative id i.e. temp_event_id is used to |
|ascertain as to which event. |
+----------------------------------------------------------*/
--Only used for upgrade
IF p_xla_ev_rec.xla_mode = 'U' THEN
Commit;
DELETE from xla_events_int_gt WHERE entity_id IS NOT NULL;
|Bulk update the distributions event ids with the newly created|
|event ids as part of the mark transaction data associated with|
|the event. |
+--------------------------------------------------------------*/
<>
log('ARP_XLA_EVENTS.Create_All_Events ()-');
UPDATE ra_cust_trx_line_gl_dist
SET event_id = NULL
WHERE event_id = p_event_id;
UPDATE ar_cash_receipt_history
SET event_id = NULL
WHERE event_id = p_event_id;
UPDATE ar_adjustments
SET event_id = NULL
WHERE event_id = p_event_id;
UPDATE ar_receivable_applications
SET event_id = NULL
WHERE event_id = p_event_id;
UPDATE ar_misc_cash_distributions
SET event_id = NULL
WHERE event_id = p_event_id;
UPDATE ar_transaction_history
SET event_id = NULL
WHERE event_id = p_event_id;
SELECT 'Y'
FROM ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = p_invoice_id
AND ctl.line_type = 'LINE'
AND NVL(ctl.autorule_complete_flag,'Y') <> 'N';
l_select_c INTEGER;
l_last_updated_by NUMBER := arp_standard.profile.user_id;
l_last_update_login NUMBER := arp_standard.profile.last_update_login;
l_select_c := Get_Select_Cursor(p_xla_ev_rec => p_xla_ev_rec,
p_call_point => 2);
l_ignore := dbms_sql.execute(l_select_c);
log( 'Fetching select stmt');
l_rows_fetched := dbms_sql.fetch_rows(l_select_c);
get_column_values(p_select_c => l_select_c,
p_xla_ev_rec => p_xla_ev_rec,
p_ev_rec => ev_rec,
p_call_point => 2);
IF( dbms_sql.is_open( l_select_c) ) THEN
dbms_sql.close_cursor( l_select_c );
IF( dbms_sql.is_open( l_select_c ) ) THEN
dbms_sql.close_cursor( l_select_c );
log('Commence bulk update processing');
UPDATE ra_cust_trx_line_gl_dist_all ctlgd
SET ctlgd.event_id = ev_rec.dist_event_id(m),
ctlgd.last_update_date = TRUNC(SYSDATE),
ctlgd.last_update_login = l_last_update_login,
ctlgd.last_updated_by = l_last_updated_by
WHERE ctlgd.rowid = ev_rec.dist_row_id(m);
UPDATE ar_adjustments_all adj
SET adj.event_id = ev_rec.dist_event_id(m),
adj.last_update_date = TRUNC(SYSDATE),
adj.last_update_login = l_last_update_login,
adj.last_updated_by = l_last_updated_by
WHERE adj.rowid = ev_rec.dist_row_id(m);
UPDATE ar_cash_receipt_history_all crh
SET crh.event_id = ev_rec.dist_event_id(m),
crh.last_update_date = TRUNC(SYSDATE),
crh.last_update_login = l_last_update_login,
crh.last_updated_by = l_last_updated_by
WHERE crh.rowid = ev_rec.dist_row_id(m);
UPDATE ar_misc_cash_distributions_all mcd
SET mcd.event_id = ev_rec.dist_event_id(m),
mcd.last_update_date = TRUNC(SYSDATE),
mcd.last_update_login = l_last_update_login,
mcd.last_updated_by = l_last_updated_by
WHERE mcd.rowid = ev_rec.dist_row_id(m);
UPDATE ar_receivable_applications_all app
SET app.event_id = ev_rec.dist_event_id(m),
app.last_update_date = TRUNC(SYSDATE),
app.last_update_login = l_last_update_login,
app.last_updated_by = l_last_updated_by
WHERE app.rowid = ev_rec.dist_row_id(m);
UPDATE ar_transaction_history_all trh
SET trh.event_id = ev_rec.dist_event_id(m),
trh.last_update_date = TRUNC(SYSDATE),
trh.last_update_login = l_last_update_login,
trh.last_updated_by = l_last_updated_by
WHERE trh.rowid = ev_rec.dist_row_id(m);
| Decision matix which returns a number stating whether an update,
| insert or latch to an event is required.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Execute
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS
| trx_status IN Transaction status
| dist_gl_date IN gldate of distribution
| ev_match_gl_date IN matching or existing event accountin date
| ev_match_status IN event status
| post_to_gl IN post to Gl
|
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
|
*=======================================================================*/
FUNCTION Change_Matrix(trx_status IN VARCHAR2 ,
dist_gl_date IN DATE ,
ev_match_gl_date IN DATE ,
ev_match_status IN xla_events.event_status_code%TYPE,
posttogl IN VARCHAR2) RETURN VARCHAR2 IS
l_change_matrix VARCHAR2(30);
| PUBLIC FUNCTION delete_event
|
| DESCRIPTION
| This procedure is a wrapper on the top of XLA delete_event API
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS
| p_document_id document identifier
| p_doc_table CT, APP, CMAPP, CRH, CR, ADJ, TRH
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 14-JAN-2003 Herve Yu Create
*=======================================================================*/
PROCEDURE delete_event( p_document_id IN NUMBER,
p_doc_table IN VARCHAR2)
IS
l_event_source_info xla_events_pub_pkg.t_event_source_info;
select xe.event_id event_id from
ra_customer_trx ct,
xla_transaction_entities_upg xte,
xla_events xe
where ct.customer_trx_id = p_document_id
and ct.invoicing_rule_id in (-2,-3)
and ct.set_of_books_id = xte.ledger_id
and nvl(xte.source_id_int_1,-99) = ct.customer_trx_id
and xte.entity_code = 'TRANSACTIONS'
and xte.application_id = 222
and xte.entity_id = xe.entity_id
and xe.application_id = 222
and xe.event_status_code = 'I' ;
SELECT event_id
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_id = p_document_id;
SELECT event_id
FROM ar_receivable_applications
WHERE receivable_application_id = p_document_id;
SELECT event_id
FROM ar_adjustments
WHERE adjustment_id = p_document_id;
SELECT event_id
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_document_id;
SELECT event_id, customer_trx_id
FROM ar_transaction_history
WHERE transaction_history_id = p_document_id;
log('arp_xla_events.delete_event ()+');
select invoicing_rule_id into l_invoicing_rule_id from ra_customer_trx
where customer_trx_id = p_document_id;
xla_events_pub_pkg.delete_event
( p_event_source_info => l_event_source_info,
p_event_id => l_event_id,
p_valuation_method => NULL,
p_security_context => l_security);
log('arp_xla_events.delete_event ()-');
log('EXCEPTION: arp_xla_events.delete_event'||SQLERRM);
END delete_event;
| PUBLIC FUNCTION delete_reverse_revrec_event
|
| DESCRIPTION
| This procedure is a wrapper on the top of XLA delete_event API.
| This procedure is used to delete the events from xla_events
| other than the REC event when a transaction with rule is incompleted.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS
| p_document_id document identifier
| p_doc_table CT, APP, CMAPP, CRH, CR, ADJ, TRH
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 07-Mar-2008 Bharani Suri Create
*=======================================================================*/
PROCEDURE delete_reverse_revrec_event( p_document_id IN NUMBER,
p_doc_table IN VARCHAR2)
IS
l_event_source_info xla_events_pub_pkg.t_event_source_info;
SELECT distinct event_id event_id
FROM ra_cust_trx_line_gl_dist gld
WHERE customer_trx_id = p_document_id
and account_set_flag = 'N'
AND event_id is not null
and EXISTS
( select 'x' FROM ra_cust_trx_line_gl_dist gldin
WHERE customer_trx_id = p_document_id
and account_class='REC'
and latest_rec_flag='Y'
AND event_id IS NOT NULL
AND event_id <> gld.event_id
);
log('arp_xla_events.delete_reverse_revrec_event ()+');
xla_events_pub_pkg.delete_event
( p_event_source_info => l_event_source_info,
p_event_id => l_event_id,
p_valuation_method => NULL,
p_security_context => l_security);
log('arp_xla_events.delete_reverse_revrec_event ()-');
log('EXCEPTION: arp_xla_events.delete_reverse_revrec_event'||SQLERRM);
END delete_reverse_revrec_event;
INSERT INTO RA_INTERFACE_ERRORS
( interface_line_id,
message_text,
org_id )
select l.interface_line_id,
xgt.error_msg,
l.org_id
from ra_interface_lines_gt l,
xla_events_gt xgt
where l.request_id = p_request_id
and l.customer_trx_id = xgt.source_id_int_1
and l.event_id = -9999
and xgt.event_class_code in ('INV_CREATE','DM_CREATE','CM_CREATE','ADJ_CREATE');
INSERT INTO RA_INTERFACE_ERRORS
( interface_line_id,
message_text,
org_id )
select l.interface_line_id,
xgt.error_msg,
l.org_id
from ra_interface_lines_gt l,
xla_events_gt xgt
where l.request_id = p_request_id
and l.customer_trx_id = xgt.source_id_int_1
and l.event_id = -9999
and xgt.event_class_code in ('CM_CREATE')
and l.interface_line_id NOT IN (SELECT interface_line_id FROM RA_INTERFACE_ERRORS);