[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TYPE select_rec_type IS RECORD
(
customer_trx_id NUMBER,
customer_trx_line_id NUMBER,
cust_trx_line_salesrep_id NUMBER,
line_amount NUMBER,
accounted_line_amount NUMBER,
percent NUMBER,
amount NUMBER,
acctd_amount NUMBER,
account_class VARCHAR2(20),
account_set_flag VARCHAR2(1),
cust_trx_type_id BINARY_INTEGER,
allow_not_open_flag VARCHAR2(1),
concatenated_segments VARCHAR2(240),
code_combination_id BINARY_INTEGER,
gl_date VARCHAR2(12), -- Julian format
original_gl_date VARCHAR2(12), -- Julian format
ussgl_trx_code VARCHAR2(30),
ussgl_trx_code_context VARCHAR2(30),
salesrep_id NUMBER,
inventory_item_id NUMBER,
memo_line_id NUMBER,
default_tax_ccid BINARY_INTEGER,
interim_tax_ccid BINARY_INTEGER,
int_concatenated_segments VARCHAR2(240),
int_code_combination_id BINARY_INTEGER,
site_use_id NUMBER,
warehouse_id NUMBER,
link_to_cust_trx_line_id NUMBER -- 1651593
);
TYPE select_rec_tab IS RECORD
(
customer_trx_id DBMS_SQL.NUMBER_TABLE,
customer_trx_line_id DBMS_SQL.NUMBER_TABLE,
cust_trx_line_salesrep_id DBMS_SQL.NUMBER_TABLE,
line_amount DBMS_SQL.NUMBER_TABLE,
accounted_line_amount DBMS_SQL.NUMBER_TABLE,
percent DBMS_SQL.NUMBER_TABLE,
amount DBMS_SQL.NUMBER_TABLE,
acctd_amount DBMS_SQL.NUMBER_TABLE,
account_class DBMS_SQL.VARCHAR2_TABLE,
account_set_flag DBMS_SQL.VARCHAR2_TABLE,
cust_trx_type_id DBMS_SQL.NUMBER_TABLE,
allow_not_open_flag DBMS_SQL.VARCHAR2_TABLE,
concatenated_segments DBMS_SQL.VARCHAR2_TABLE,
code_combination_id DBMS_SQL.NUMBER_TABLE,
gl_date DBMS_SQL.VARCHAR2_TABLE, -- Julian format
original_gl_date DBMS_SQL.VARCHAR2_TABLE, -- Julian format
ussgl_trx_code DBMS_SQL.VARCHAR2_TABLE,
ussgl_trx_code_context DBMS_SQL.VARCHAR2_TABLE,
salesrep_id DBMS_SQL.NUMBER_TABLE,
inventory_item_id DBMS_SQL.NUMBER_TABLE,
memo_line_id DBMS_SQL.NUMBER_TABLE,
default_tax_ccid DBMS_SQL.NUMBER_TABLE,
interim_tax_ccid DBMS_SQL.NUMBER_TABLE,
int_concatenated_segments DBMS_SQL.VARCHAR2_TABLE,
int_code_combination_id DBMS_SQL.NUMBER_TABLE,
site_use_id DBMS_SQL.NUMBER_TABLE,
warehouse_id DBMS_SQL.NUMBER_TABLE,
link_to_cust_trx_line_id DBMS_SQL.NUMBER_TABLE -- 1651593
);
g_select_rec_tab select_rec_tab;
PROCEDURE insert_into_error_table(
p_interface_line_id number,
p_message_text varchar2,
p_invalid_value varchar2 ) IS
BEGIN
INSERT INTO ra_interface_errors
(interface_line_id,
message_text,
invalid_value,
org_id)
VALUES
(p_interface_line_id,
p_message_text,
p_invalid_value,
ARP_STANDARD.sysparm.org_id);
END insert_into_error_table;
-- we insert error for each invoice line.
FOR c01_rec IN (select interface_line_id from ra_interface_lines_gt
WHERE customer_trx_id = -1 * p_interface_line_id
AND request_id = p_request_id ) LOOP
insert_into_error_table(
c01_rec.interface_line_id,
p_message_text,
p_invalid_value );
insert_into_error_table(
p_interface_line_id,
p_message_text,
p_invalid_value );
FOR invRec IN ( select trx_header_id,trx_line_id
from ar_trx_lines_gt
where request_id = p_request_id
and customer_trx_line_id = p_interface_line_id
UNION
select trx_header_id, -99
from ar_trx_header_gt
where request_id = p_request_id
and customer_trx_id = -1 * p_interface_line_id ) loop
insert into ar_trx_errors_gt
(trx_header_id,
trx_line_id,
error_message,
invalid_value) values
( invRec.trx_header_id,
decode(invRec.trx_line_id,-99,null,invRec.trx_line_id),
p_message_text,
p_invalid_value);
SELECT
ad.type type,
ads.segment segment,
upper(ads.table_name) table_name,
ads.constant constant
FROM
ra_account_default_segments ads,
ra_account_defaults ad
WHERE ad.gl_default_id = ads.gl_default_id
AND ad.org_id = NVL(arp_global.sysparam.org_id, ad.org_id)
AND ad.type in
(
'REV', 'REC', 'FREIGHT', 'TAX', 'UNBILL', 'UNEARN', 'SUSPENSE'
)
ORDER BY
type,
segment_num;
autoacc_def_segment_t.delete;
autoacc_def_table_t.delete;
autoacc_def_const_t.delete;
SELECT
nvl(gl_id_rev,-1),
nvl(gl_id_rec,-1),
nvl(gl_id_freight,-1),
nvl(gl_id_tax,-1),
nvl(gl_id_unbilled,-1),
nvl(gl_id_unearned,-1),
nvl(gl_id_clearing,-1)
INTO
p_ccid_rev,
p_ccid_rec,
p_ccid_frt,
p_ccid_tax,
p_ccid_unbill,
p_ccid_unearn,
p_ccid_suspense
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_trx_type_id;
SELECT
nvl(gl_id_rev,-1),
nvl(gl_id_rec,-1),
nvl(gl_id_freight,-1),
nvl(gl_id_tax,-1),
nvl(gl_id_unbilled,-1),
nvl(gl_id_unearned,-1),
nvl(gl_id_clearing,-1)
INTO
p_ccid_rev,
p_ccid_rec,
p_ccid_frt,
p_ccid_tax,
p_ccid_unbill,
p_ccid_unearn,
p_ccid_suspense
FROM hz_cust_site_uses
WHERE site_use_id = p_site_use_id;
SELECT
nvl(gl_id_rev,-1),
nvl(gl_id_rec,-1),
nvl(gl_id_freight,-1)
INTO p_ccid_rev, p_ccid_rec, p_ccid_frt
FROM ra_salesreps
WHERE salesrep_id = p_salesrep_id;
SELECT nvl(sales_account, -1), nvl(item_type, '~')
INTO p_ccid_rev,
p_inv_item_type
FROM mtl_system_items
WHERE organization_id
= t_warehouse_id
AND inventory_item_id = p_inv_item_id;
debug( 'Inserting into the cache: ');
SELECT nvl(gl_id_rev,-1)
INTO p_ccid_rev
FROM ar_memo_lines
WHERE memo_line_id = p_memo_line_id;
SELECT nvl(c.code_combination_id,-1)
INTO p_ccid_rev
FROM
ra_customer_trx t,
ra_customer_trx_lines l,
mtl_item_categories i,
so_agreements a,
ra_account_combinations c
WHERE t.customer_trx_id = l.customer_trx_id
AND l.customer_trx_line_id = p_line_id
AND t.agreement_id = a.agreement_id(+)
AND l.inventory_item_id = i.inventory_item_id(+)
AND i.organization_id(+)
= nvl(p_warehouse_id,
to_number(oe_profile.value('SO_ORGANIZATION_ID',arp_global.sysparam.org_id))) --Bug#1639334
AND i.category_set_id(+) = 1
AND to_char(nvl(i.category_id, -1)) = c.value1
AND nvl(a.agreement_type_code, -1) = nvl(c.value2 , -1);
PROCEDURE define_columns( p_select_c IN INTEGER,
p_select_rec IN select_rec_type) IS
BEGIN
print_fcn_label2( 'arp_auto_accounting.define_columns()+' );
dbms_sql.define_column( p_select_c, 1, p_select_rec.customer_trx_id );
dbms_sql.define_column( p_select_c, 2, p_select_rec.customer_trx_line_id );
dbms_sql.define_column( p_select_c, 3,
p_select_rec.cust_trx_line_salesrep_id );
dbms_sql.define_column( p_select_c, 4, p_select_rec.line_amount );
dbms_sql.define_column( p_select_c, 5,
p_select_rec.accounted_line_amount );
dbms_sql.define_column( p_select_c, 6, p_select_rec.percent );
dbms_sql.define_column( p_select_c, 7, p_select_rec.amount );
dbms_sql.define_column( p_select_c, 8, p_select_rec.acctd_amount );
dbms_sql.define_column( p_select_c, 9, p_select_rec.account_class, 20 );
dbms_sql.define_column( p_select_c, 10, p_select_rec.account_set_flag, 1 );
dbms_sql.define_column( p_select_c, 11, p_select_rec.cust_trx_type_id );
dbms_sql.define_column( p_select_c, 12,
p_select_rec.allow_not_open_flag, 1 );
dbms_sql.define_column( p_select_c, 13,
p_select_rec.concatenated_segments, 240 );
dbms_sql.define_column( p_select_c, 14, p_select_rec.code_combination_id );
dbms_sql.define_column( p_select_c, 15, p_select_rec.gl_date, 12 );
dbms_sql.define_column( p_select_c, 16,
p_select_rec.original_gl_date, 12 );
dbms_sql.define_column( p_select_c, 17, p_select_rec.ussgl_trx_code, 30 );
dbms_sql.define_column( p_select_c, 18,
p_select_rec.ussgl_trx_code_context, 30 );
dbms_sql.define_column( p_select_c, 19, p_select_rec.salesrep_id );
dbms_sql.define_column( p_select_c, 20, p_select_rec.inventory_item_id );
dbms_sql.define_column( p_select_c, 21, p_select_rec.memo_line_id );
dbms_sql.define_column( p_select_c, 22, p_select_rec.default_tax_ccid );
dbms_sql.define_column( p_select_c, 23, p_select_rec.interim_tax_ccid );
dbms_sql.define_column( p_select_c, 24, p_select_rec.site_use_id);
dbms_sql.define_column( p_select_c, 25, p_select_rec.warehouse_id);
PROCEDURE define_arrays( p_select_c IN INTEGER,
p_select_tab IN select_rec_tab) IS
BEGIN
print_fcn_label2( 'arp_auto_accounting.define_arrays()+' );
dbms_sql.define_array( p_select_c, 1, p_select_tab.customer_trx_id, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 2, p_select_tab.customer_trx_line_id, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 3,
p_select_tab.cust_trx_line_salesrep_id, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 4, p_select_tab.line_amount, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 5,
p_select_tab.accounted_line_amount, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 6, p_select_tab.percent, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 7, p_select_tab.amount, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 8, p_select_tab.acctd_amount, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 9, p_select_tab.account_class, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 10, p_select_tab.account_set_flag, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 11, p_select_tab.cust_trx_type_id, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 12,
p_select_tab.allow_not_open_flag, MAX_ARRAY_SIZE, STARTING_INDEX);
dbms_sql.define_array( p_select_c, 13,
p_select_tab.concatenated_segments, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 14, p_select_tab.code_combination_id, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 15, p_select_tab.gl_date, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 16,
p_select_tab.original_gl_date, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 17, p_select_tab.ussgl_trx_code, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 18,
p_select_tab.ussgl_trx_code_context, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 19, p_select_tab.salesrep_id, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 20, p_select_tab.inventory_item_id, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 21, p_select_tab.memo_line_id, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 22, p_select_tab.default_tax_ccid, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 23, p_select_tab.interim_tax_ccid, MAX_ARRAY_SIZE, STARTING_INDEX );
dbms_sql.define_array( p_select_c, 24, p_select_tab.site_use_id, MAX_ARRAY_SIZE, STARTING_INDEX);
dbms_sql.define_array( p_select_c, 25, p_select_tab.warehouse_id, MAX_ARRAY_SIZE, STARTING_INDEX);
dbms_sql.define_array( p_select_c, 26, p_select_tab.link_to_cust_trx_line_id, MAX_ARRAY_SIZE, STARTING_INDEX );
FUNCTION build_select_sql( p_system_info IN
arp_trx_global.system_info_rec_type,
p_profile_info IN
arp_trx_global.profile_rec_type,
p_account_class IN VARCHAR2,
p_customer_trx_id IN BINARY_INTEGER,
p_customer_trx_line_id IN number,
p_cust_trx_line_salesrep_id IN number,
p_request_id IN BINARY_INTEGER,
p_gl_date IN DATE,
p_original_gl_date IN DATE,
p_total_trx_amount IN NUMBER,
p_code_combination_id IN BINARY_INTEGER,
p_force_account_set_no IN VARCHAR2,
p_cust_trx_type_id IN BINARY_INTEGER,
p_primary_salesrep_id IN BINARY_INTEGER,
p_inventory_item_id IN BINARY_INTEGER,
p_memo_line_id IN BINARY_INTEGER,
p_use_unearn_srep_dependency IN BOOLEAN DEFAULT FALSE)
RETURN VARCHAR2 IS
l_based_on_salesrep_flag BOOLEAN := FALSE;
l_select_stmt VARCHAR2(32767);
print_fcn_label( 'arp_auto_accounting.build_select_sql()+' );
debug(' Build select attribute strings', MSG_LEVEL_DEVELOP);
(SELECT ';
'AND ril.rowid = (SELECT /*+ no_unnest */ min(ril2.rowid)
FROM ra_interface_lines_gt ril2
WHERE ril2.customer_trx_id = ct.customer_trx_id
AND ril2.link_to_line_id is null)
AND ril.customer_trx_id = ct.customer_trx_id ';
l_select_stmt :=
'SELECT /*+ leading(ct) index(ct,RA_CUSTOMER_TRX_N15) index(ctl,RA_CUSTOMER_TRX_LINES_N4) use_hash(ctl) */ ' || CRLF;
l_select_stmt := 'SELECT' || CRLF;
l_select_stmt := l_select_stmt ||
'ct.customer_trx_id, /* customer_trx_id */
ctl.customer_trx_line_id, /* customer_trx_line_id */'
|| l_salesrep_attributes1
|| CRLF ||
'''' || p_account_class || ''', /* account class */
decode('|| l_rule_fragment ||',
NULL, ''N'',
''Y'' ), /* account_set_flag */
ct.cust_trx_type_id, /* cust_trx_type_id */
decode(ct.invoicing_rule_id,
-3, ''Y'',
''N''), /* allow_not_open_flag */
to_char(''''), /* concatenated segments */'
|| CRLF
|| l_ccid_fragment ||', /* code_combination_id */'
|| l_gl_date_attribute
|| l_orig_gl_date_attribute
|| l_salesrep_attributes2
|| l_tax_attribute
|| 'ct.bill_to_site_use_id, /* Billing site id */ '
|| l_tax_table_fragment ||'.warehouse_id /* Warehouse id */ '
|| ', ctl.link_to_cust_trx_line_id /* 1651593 - tax errors */'
|| CRLF
||'FROM'
|| l_interface_lines_table
|| l_inv_gl_dist_table
|| l_salesreps_table
|| l_tax_table
|| CRLF ||
'fnd_currencies fc_foreign,'
|| l_gl_dist_table
|| CRLF ||
'ra_cust_trx_line_gl_dist rgd,
ra_customer_trx_lines ctl,
ra_customer_trx ct
WHERE ct.customer_trx_id = ctl.customer_trx_id(+)
AND ct.invoice_currency_code = fc_foreign.currency_code'
|| l_cm_module_pred
|| l_prevent_dup_rec_pred
|| CRLF ||
'AND ct.customer_trx_id = rgd.customer_trx_id(+)
AND ''REC'' = rgd.account_class(+)
AND ''N'' = rgd.account_set_flag(+)
AND ctl.line_type '|| l_line_type_fragment
|| l_tax_pred
|| CRLF ||
'and nvl(ct.invoicing_rule_id,
-10) '|| l_rule_id_fragment
|| l_suspense_pred
|| l_based_on_salesrep_pred
|| l_interface_table_pred
|| l_request_id_pred
|| l_trx_id_pred
|| l_line_id_pred
|| l_line_salesrep_id_pred
|| l_inv_rec_pred ;
debug( l_select_stmt, MSG_LEVEL_DEBUG );
debug( ' len(l_select_stmt)=' ||
to_char(LENGTHB(l_select_stmt)), MSG_LEVEL_DEBUG );
print_fcn_label( 'arp_auto_accounting.build_select_sql()-' );
RETURN l_select_stmt;
debug('EXCEPTION: arp_auto_accounting.build_select_sql()',
MSG_LEVEL_BASIC);
END build_select_sql;
SELECT segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
DECODE(p_segment_number,
1, segment1,
2, segment2,
3, segment3,
4, segment4,
5, segment5,
6, segment6,
7, segment7,
8, segment8,
9, segment9,
10, segment10,
11, segment11,
12, segment12,
13, segment13,
14, segment14,
15, segment15,
16, segment16,
17, segment17,
18, segment18,
19, segment19,
20, segment20,
21, segment21,
22, segment22,
23, segment23,
24, segment24,
25, segment25,
26, segment26,
27, segment27,
28, segment28,
29, segment29,
30, segment30, null)
INTO l_segment1,
l_segment2,
l_segment3,
l_segment4,
l_segment5,
l_segment6,
l_segment7,
l_segment8,
l_segment9,
l_segment10,
l_segment11,
l_segment12,
l_segment13,
l_segment14,
l_segment15,
l_segment16,
l_segment17,
l_segment18,
l_segment19,
l_segment20,
l_segment21,
l_segment22,
l_segment23,
l_segment24,
l_segment25,
l_segment26,
l_segment27,
l_segment28,
l_segment29,
l_segment30,
p_desired_segment
FROM gl_code_combinations
WHERE code_combination_id = p_ccid;
| Add the selected segments to the segment cache |
| only if the cache is not already full. |
+--------------------------------------------------*/
IF ( segment1_cache.count <= MAX_SEGMENT_CACHE_SIZE )
THEN
segment1_cache(p_ccid) := l_segment1;
| Select the segments from gl_code_combinations and add them |
| to the cache if it is not already full. |
+--------------------------------------------------------------*/
add_segments_to_cache(p_ccid, p_segment_number,l_desired_segment);
SELECT detail_posting_allowed_flag,
summary_flag
INTO l_detail_posting_flag,
l_summary_flag
FROM gl_code_combinations
WHERE code_combination_id = l_ccid;
FUNCTION Get_Select_Cursor(
p_system_info IN
arp_trx_global.system_info_rec_type,
p_profile_info IN
arp_trx_global.profile_rec_type,
p_account_class IN VARCHAR2,
p_customer_trx_id IN BINARY_INTEGER,
p_customer_trx_line_id IN number,
p_cust_trx_line_salesrep_id IN number,
p_request_id IN BINARY_INTEGER,
p_gl_date IN DATE,
p_original_gl_date IN DATE,
p_total_trx_amount IN NUMBER,
p_code_combination_id IN BINARY_INTEGER,
p_force_account_set_no IN VARCHAR2,
p_cust_trx_type_id IN BINARY_INTEGER,
p_primary_salesrep_id IN BINARY_INTEGER,
p_inventory_item_id IN BINARY_INTEGER,
p_memo_line_id IN BINARY_INTEGER,
p_use_unearn_srep_dependency IN BOOLEAN DEFAULT FALSE,
p_keep_cursor_open_flag OUT NOCOPY BOOLEAN )
RETURN BINARY_INTEGER IS
l_select_rec select_rec_type;
l_select_tab select_rec_tab;
l_select_c BINARY_INTEGER;
print_fcn_label2( 'arp_auto_accounting.Get_Select_Cursor()+' );
print_fcn_label2( 'arp_auto_accounting.Get_Select_Cursor()-' );
| construct and parse the select statement. |
+----------------------------------------------*/
debug('Reparsing cursor that was not found in the cache. Key: ' ||
l_key,
MSG_LEVEL_DEBUG);
l_select_stmt VARCHAR2(32767);
l_select_c := dbms_sql.open_cursor;
cursor_cache( l_cache_index ) := l_select_c;
l_select_stmt := build_select_sql( p_system_info,
p_profile_info,
p_account_class,
p_customer_trx_id,
p_customer_trx_line_id,
p_cust_trx_line_salesrep_id,
p_request_id,
p_gl_date,
p_original_gl_date,
p_total_trx_amount,
p_code_combination_id,
p_force_account_set_no,
p_cust_trx_type_id,
p_primary_salesrep_id,
p_inventory_item_id,
p_memo_line_id,
p_use_unearn_srep_dependency);
l_select_stmt := l_select_stmt || CRLF ||
'ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12';
debug( ' Parsing select stmt', MSG_LEVEL_DEBUG );
dbms_sql.parse( l_select_c, l_select_stmt, dbms_sql.v7);
define_arrays( l_select_c, l_select_tab );
debug( 'Error constructing/parsing select cursor',
MSG_LEVEL_BASIC );
print_fcn_label2( 'arp_auto_accounting.Get_Select_Cursor()-' );
RETURN( l_select_c );
debug('EXCEPTION: arp_auto_accounting.Get_Select_Cursor()',
MSG_LEVEL_BASIC);
p_cursor := Get_Select_Cursor(
system_info,
profile_info,
p_account_class,
p_customer_trx_id,
p_customer_trx_line_id,
p_cust_trx_line_salesrep_id,
p_request_id,
p_gl_date,
p_original_gl_date,
p_total_trx_amount,
p_code_combination_id,
p_force_account_set_no,
p_cust_trx_type_id,
p_primary_salesrep_id,
p_inventory_item_id,
p_memo_line_id,
FALSE,
p_keep_cursor_open_flag);
FUNCTION build_delete_sql( p_system_info IN
arp_trx_global.system_info_rec_type,
p_profile_info IN
arp_trx_global.profile_rec_type,
p_account_class IN VARCHAR2,
p_customer_trx_id IN BINARY_INTEGER,
p_customer_trx_line_id IN NUMBER,
p_cust_trx_line_salesrep_id IN NUMBER,
p_request_id IN BINARY_INTEGER )
RETURN VARCHAR2 IS
l_delete_stmt VARCHAR2(1000);
print_fcn_label( 'arp_auto_accounting.build_delete_sql()+' );
SELECT customer_trx_id
FROM ra_customer_trx ct
WHERE (ct.customer_trx_id = '
|| to_char( p_customer_trx_id ) || CRLF ||
' AND nvl(ct.invoicing_rule_id,-10) = -2 )' || CRLF ||
')';
SELECT customer_trx_line_id
FROM ra_customer_trx_lines ctl, ra_customer_trx ct
WHERE ct.customer_trx_id=ctl.customer_trx_id AND nvl(ct.invoicing_rule_id,-10) = -2 AND (ctl.customer_trx_line_id = '
|| to_char( p_customer_trx_line_id ) || CRLF ||
' or ctl.link_to_cust_trx_line_id = '
|| to_char( p_customer_trx_line_id ) || ')' || CRLF ||
')';
SELECT customer_trx_line_id
FROM ra_customer_trx_lines ctl
WHERE (ctl.customer_trx_line_id = '
|| to_char( p_customer_trx_line_id ) || CRLF ||
' or ctl.link_to_cust_trx_line_id = '
|| to_char( p_customer_trx_line_id ) || ')' || CRLF ||
')';
l_delete_stmt :=
'DELETE from ra_cust_trx_line_gl_dist gd
WHERE gl_posted_date is null'
|| l_account_class_pred
|| l_request_id_pred
|| l_ctid_pred
|| l_ctlid_pred
|| l_ctlsid_pred
|| CRLF ||
'AND account_set_flag = (SELECT decode(ct.invoicing_rule_id,
NULL, ''N'',
''Y'')
FROM ra_customer_trx ct
WHERE ct.customer_trx_id = gd.customer_trx_id)';
debug( l_delete_stmt, MSG_LEVEL_DEBUG );
debug( ' len(l_delete_stmt)=' || to_char(LENGTHB(l_delete_stmt)),
MSG_LEVEL_DEBUG );
print_fcn_label( 'arp_auto_accounting.build_delete_sql()-' );
RETURN l_delete_stmt;
debug('EXCEPTION: arp_auto_accounting.build_delete_sql()',
MSG_LEVEL_BASIC);
END build_delete_sql;
PROCEDURE get_column_values( p_select_c IN INTEGER,
p_select_rec OUT NOCOPY select_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_auto_accounting.get_column_values()+' );
dbms_sql.column_value( p_select_c, 1, p_select_rec.customer_trx_id );
dbms_sql.column_value( p_select_c, 2, p_select_rec.customer_trx_line_id );
dbms_sql.column_value( p_select_c, 3,
p_select_rec.cust_trx_line_salesrep_id );
dbms_sql.column_value( p_select_c, 4, p_select_rec.line_amount );
dbms_sql.column_value( p_select_c, 5,
p_select_rec.accounted_line_amount );
dbms_sql.column_value( p_select_c, 6, p_select_rec.percent );
dbms_sql.column_value( p_select_c, 7, p_select_rec.amount );
dbms_sql.column_value( p_select_c, 8, p_select_rec.acctd_amount );
dbms_sql.column_value( p_select_c, 9, p_select_rec.account_class );
dbms_sql.column_value( p_select_c, 10, p_select_rec.account_set_flag );
dbms_sql.column_value( p_select_c, 11, p_select_rec.cust_trx_type_id );
dbms_sql.column_value( p_select_c, 12,
p_select_rec.allow_not_open_flag );
dbms_sql.column_value( p_select_c, 13,
p_select_rec.concatenated_segments );
dbms_sql.column_value( p_select_c, 14, p_select_rec.code_combination_id );
dbms_sql.column_value( p_select_c, 15, p_select_rec.gl_date );
dbms_sql.column_value( p_select_c, 16, p_select_rec.original_gl_date );
dbms_sql.column_value( p_select_c, 17, p_select_rec.ussgl_trx_code );
dbms_sql.column_value( p_select_c, 18,
p_select_rec.ussgl_trx_code_context );
dbms_sql.column_value( p_select_c, 19, p_select_rec.salesrep_id );
dbms_sql.column_value( p_select_c, 20, p_select_rec.inventory_item_id );
dbms_sql.column_value( p_select_c, 21, p_select_rec.memo_line_id );
dbms_sql.column_value( p_select_c, 22, p_select_rec.default_tax_ccid );
dbms_sql.column_value( p_select_c, 23, p_select_rec.interim_tax_ccid );
dbms_sql.column_value( p_select_c, 24, p_select_rec.site_use_id );
dbms_sql.column_value( p_select_c, 25, p_select_rec.warehouse_id );
dbms_sql.column_value( p_select_c, 26, p_select_rec.link_to_cust_trx_line_id);
back from SELECT as -1 */
IF p_select_rec.interim_tax_ccid = -1
THEN
p_select_rec.interim_tax_ccid := NULL;
PROCEDURE get_column_values( p_select_c IN INTEGER,
p_select_tab OUT NOCOPY select_rec_tab ) IS
BEGIN
print_fcn_label2( 'arp_auto_accounting.get_column_values(tab)+' );
dbms_sql.column_value( p_select_c, 1, p_select_tab.customer_trx_id );
dbms_sql.column_value( p_select_c, 2, p_select_tab.customer_trx_line_id );
dbms_sql.column_value( p_select_c, 3,
p_select_tab.cust_trx_line_salesrep_id );
dbms_sql.column_value( p_select_c, 4, p_select_tab.line_amount );
dbms_sql.column_value( p_select_c, 5,
p_select_tab.accounted_line_amount );
dbms_sql.column_value( p_select_c, 6, p_select_tab.percent );
dbms_sql.column_value( p_select_c, 7, p_select_tab.amount );
dbms_sql.column_value( p_select_c, 8, p_select_tab.acctd_amount );
dbms_sql.column_value( p_select_c, 9, p_select_tab.account_class );
dbms_sql.column_value( p_select_c, 10, p_select_tab.account_set_flag );
dbms_sql.column_value( p_select_c, 11, p_select_tab.cust_trx_type_id );
dbms_sql.column_value( p_select_c, 12,
p_select_tab.allow_not_open_flag );
dbms_sql.column_value( p_select_c, 13,
p_select_tab.concatenated_segments );
dbms_sql.column_value( p_select_c, 14, p_select_tab.code_combination_id );
dbms_sql.column_value( p_select_c, 15, p_select_tab.gl_date );
dbms_sql.column_value( p_select_c, 16, p_select_tab.original_gl_date );
dbms_sql.column_value( p_select_c, 17, p_select_tab.ussgl_trx_code );
dbms_sql.column_value( p_select_c, 18,
p_select_tab.ussgl_trx_code_context );
dbms_sql.column_value( p_select_c, 19, p_select_tab.salesrep_id );
dbms_sql.column_value( p_select_c, 20, p_select_tab.inventory_item_id );
dbms_sql.column_value( p_select_c, 21, p_select_tab.memo_line_id );
dbms_sql.column_value( p_select_c, 22, p_select_tab.default_tax_ccid );
dbms_sql.column_value( p_select_c, 23, p_select_tab.interim_tax_ccid );
dbms_sql.column_value( p_select_c, 24, p_select_tab.site_use_id );
dbms_sql.column_value( p_select_c, 25, p_select_tab.warehouse_id );
dbms_sql.column_value( p_select_c, 26, p_select_tab.link_to_cust_trx_line_id);
PROCEDURE correct_rounding_errors( select_record IN OUT NOCOPY select_rec_type,
total_percent IN OUT NOCOPY NUMBER,
total_amount IN OUT NOCOPY NUMBER,
total_acctd_amount IN OUT NOCOPY NUMBER) IS
BEGIN
print_fcn_label2( 'arp_auto_accounting.correct_rounding_errors()+' );
total_percent := total_percent + select_record.percent;
total_amount := total_amount + select_record.amount;
total_acctd_amount := total_acctd_amount + select_record.acctd_amount;
select_record.amount := select_record.amount +
( select_record.line_amount - total_amount );
select_record.acctd_amount :=
select_record.acctd_amount +
( select_record.accounted_line_amount -
total_acctd_amount );
select_record.amount := select_record.amount - total_amount;
select_record.acctd_amount := select_record.acctd_amount -
total_acctd_amount;
PROCEDURE insert_dist_row( p_system_info IN
arp_trx_global.system_info_rec_type,
p_profile_info IN
arp_trx_global.profile_rec_type,
p_request_id IN BINARY_INTEGER,
p_select_tab IN select_rec_tab,
p_low IN NUMBER,
p_high IN NUMBER ) IS
l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr; /* mrc */
print_fcn_label2( 'arp_auto_accounting.insert_dist_row()+' );
/* Bug 2560036 - modified insert to set rec_offset_flag in
support of directly inserted UNEARN rows for RAM-C */
FORALL i IN p_low..p_high
INSERT into ra_cust_trx_line_gl_dist
(
cust_trx_line_gl_dist_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
set_of_books_id,
request_id,
customer_trx_id,
customer_trx_line_id,
cust_trx_line_salesrep_id,
percent,
amount,
acctd_amount,
account_class,
account_set_flag,
concatenated_segments,
code_combination_id,
gl_date,
original_gl_date,
ussgl_transaction_code,
ussgl_transaction_code_context,
posting_control_id,
latest_rec_flag,
collected_tax_concat_seg,
collected_tax_ccid,
rec_offset_flag
,org_id
)
VALUES
(
ra_cust_trx_line_gl_dist_s.nextval,
p_profile_info.user_id,
sysdate,
p_profile_info.user_id,
sysdate,
p_system_info.system_parameters.set_of_books_id,
p_request_id,
p_select_tab.customer_trx_id(i),
p_select_tab.customer_trx_line_id(i),
p_select_tab.cust_trx_line_salesrep_id(i),
round(nvl(p_select_tab.percent(i), 0), 4),
decode(p_select_tab.account_set_flag(i),
'Y', null, p_select_tab.amount(i)),
decode(p_select_tab.account_set_flag(i),
'Y', null, p_select_tab.acctd_amount(i)),
p_select_tab.account_class(i),
p_select_tab.account_set_flag(i),
decode(p_select_tab.int_code_combination_id(i),
'', decode(p_select_tab.code_combination_id(i),
-1, p_select_tab.concatenated_segments(i),
NULL ),
-1, p_select_tab.int_concatenated_segments(i),
NULL),
decode(p_select_tab.int_code_combination_id(i),
'', p_select_tab.code_combination_id(i),
p_select_tab.int_code_combination_id(i)),
to_date(p_select_tab.gl_date(i), 'J'),
to_date(p_select_tab.original_gl_date(i), 'J'),
p_select_tab.ussgl_trx_code(i),
p_select_tab.ussgl_trx_code_context(i),
-3,
decode( p_select_tab.account_class(i),
'REC', 'Y',
NULL),
decode(p_select_tab.int_code_combination_id(i),
'',NULL,
decode(p_select_tab.code_combination_id(i),
-1, p_select_tab.concatenated_segments(i),
NULL)),
decode(p_select_tab.int_code_combination_id(i),
'',NULL,
p_select_tab.code_combination_id(i)),
DECODE(p_select_tab.account_set_flag(i), 'Y', NULL,
DECODE(p_select_tab.account_class(i), 'UNEARN', 'Y', NULL))
,arp_standard.sysparm.org_id --anuj
)
RETURNING cust_trx_line_gl_dist_id
BULK COLLECT INTO l_gl_dist_key_value_list;
/* only insert the MRC gl_dist data if this has been called from
forms. For autoinv this insert is handled differently by
request_id.
-- Added by Bsarkar
The g_called_from is introduced to stop the call for Invoice Creation
API. In case AUTO_ACCOUNTING is called from Tax engine for Invoice API
this variable
will have different value and won't execute the MRC call. The MRC call
for invoice creation API is handled based on request Id and this call is
not required. */
IF (p_request_id IS NULL AND g_called_from = 'FORMS' ) THEN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug('calling mrc engine for insertion of gl dist data');
inext := p_select_tab.customer_trx_id(i);
l_xla_event.xla_from_doc_id := p_select_tab.customer_trx_id(i);
l_xla_event.xla_to_doc_id := p_select_tab.customer_trx_id(i);
print_fcn_label2( 'arp_auto_accounting.insert_dist_row()-' );
debug('EXCEPTION: arp_auto_accounting.insert_dist_row()',
MSG_LEVEL_BASIC);
END insert_dist_row;
PROCEDURE dump_select_rec( p_select_rec IN select_rec_type ) IS
BEGIN
print_fcn_label2( 'arp_auto_accounting.dump_select_rec()+' );
debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.customer_trx_id ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.customer_trx_line_id ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.cust_trx_line_salesrep_id ),
MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.line_amount ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.accounted_line_amount ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.percent ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.amount ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.acctd_amount ), MSG_LEVEL_DEBUG );
debug( ' account_class=' || p_select_rec.account_class, MSG_LEVEL_DEBUG );
debug( ' account_set_flag=' || p_select_rec.account_set_flag,
MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.cust_trx_type_id ), MSG_LEVEL_DEBUG );
p_select_rec.allow_not_open_flag, MSG_LEVEL_DEBUG );
|| p_select_rec.concatenated_segments, MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.code_combination_id ), MSG_LEVEL_DEBUG );
debug( ' gl_date=' || p_select_rec.gl_date, MSG_LEVEL_DEBUG );
debug( ' original_gl_date=' || p_select_rec.original_gl_date,
MSG_LEVEL_DEBUG );
debug( ' ussgl_trx_code=' || p_select_rec.ussgl_trx_code, MSG_LEVEL_DEBUG );
|| p_select_rec.ussgl_trx_code_context, MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.salesrep_id ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.inventory_item_id ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.memo_line_id ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.default_tax_ccid ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.interim_tax_ccid ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.site_use_id ), MSG_LEVEL_DEBUG );
|| to_char( p_select_rec.warehouse_id ), MSG_LEVEL_DEBUG );
print_fcn_label2( 'arp_auto_accounting.dump_select_rec()-' );
debug('EXCEPTION: arp_auto_accounting.dump_select_rec()',
MSG_LEVEL_BASIC);
END dump_select_rec;
PROCEDURE dump_select_tab( p_select_tab IN select_rec_tab, p_low IN NUMBER, p_high IN NUMBER ) IS
BEGIN
print_fcn_label2( 'arp_auto_accounting.dump_select_tab()+' );
debug( ' Dumping select record: [' || i ||']', MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.customer_trx_id(i) ), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.customer_trx_line_id (i)), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.cust_trx_line_salesrep_id(i)),
MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.line_amount(i) ), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.accounted_line_amount (i)), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.percent(i) ), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.amount(i) ), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.acctd_amount(i) ), MSG_LEVEL_DEBUG );
debug( ' account_class=' || p_select_tab.account_class(i), MSG_LEVEL_DEBUG );
debug( ' account_set_flag=' || p_select_tab.account_set_flag(i),
MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.cust_trx_type_id(i) ), MSG_LEVEL_DEBUG );
p_select_tab.allow_not_open_flag(i), MSG_LEVEL_DEBUG );
|| p_select_tab.concatenated_segments(i), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.code_combination_id (i)), MSG_LEVEL_DEBUG );
debug( ' gl_date=' || p_select_tab.gl_date(i), MSG_LEVEL_DEBUG );
debug( ' original_gl_date=' || p_select_tab.original_gl_date(i),
MSG_LEVEL_DEBUG );
debug( ' ussgl_trx_code=' || p_select_tab.ussgl_trx_code(i), MSG_LEVEL_DEBUG );
|| p_select_tab.ussgl_trx_code_context(i), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.salesrep_id (i)), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.inventory_item_id (i)), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.memo_line_id (i)), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.default_tax_ccid(i) ), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.interim_tax_ccid(i) ), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.site_use_id (i)), MSG_LEVEL_DEBUG );
|| to_char( p_select_tab.warehouse_id (i)), MSG_LEVEL_DEBUG );
print_fcn_label2( 'arp_auto_accounting.dump_select_tab()-' );
debug('EXCEPTION: arp_auto_accounting.dump_select_tab()',
MSG_LEVEL_BASIC);
END dump_select_tab;
FUNCTION get_select_rec( p_select_tab IN select_rec_tab, p_cnt IN NUMBER )
RETURN select_rec_type AS
p_select_rec select_rec_type;
print_fcn_label2( 'arp_auto_accounting.get_select_rec(tab)+' );
p_select_rec.customer_trx_id := p_select_tab.customer_trx_id(p_cnt);
p_select_rec.customer_trx_line_id := p_select_tab.customer_trx_line_id(p_cnt);
p_select_rec.cust_trx_line_salesrep_id := p_select_tab.cust_trx_line_salesrep_id(p_cnt);
p_select_rec.line_amount := p_select_tab.line_amount(p_cnt);
p_select_rec.accounted_line_amount := p_select_tab.accounted_line_amount(p_cnt);
p_select_rec.percent := p_select_tab.percent(p_cnt);
p_select_rec.amount := p_select_tab.amount(p_cnt);
p_select_rec.acctd_amount := p_select_tab.acctd_amount(p_cnt);
p_select_rec.account_class := p_select_tab.account_class(p_cnt);
p_select_rec.account_set_flag := p_select_tab.account_set_flag(p_cnt);
p_select_rec.cust_trx_type_id := p_select_tab.cust_trx_type_id(p_cnt);
p_select_rec.allow_not_open_flag := p_select_tab.allow_not_open_flag(p_cnt);
p_select_rec.concatenated_segments := p_select_tab.concatenated_segments(p_cnt);
p_select_rec.code_combination_id := p_select_tab.code_combination_id(p_cnt);
p_select_rec.gl_date := p_select_tab.gl_date(p_cnt);
p_select_rec.original_gl_date := p_select_tab.original_gl_date(p_cnt);
p_select_rec.ussgl_trx_code := p_select_tab.ussgl_trx_code(p_cnt);
p_select_rec.ussgl_trx_code_context := p_select_tab.ussgl_trx_code_context(p_cnt);
p_select_rec.salesrep_id := p_select_tab.salesrep_id(p_cnt);
p_select_rec.inventory_item_id := p_select_tab.inventory_item_id(p_cnt);
p_select_rec.memo_line_id := p_select_tab.memo_line_id(p_cnt);
p_select_rec.default_tax_ccid := p_select_tab.default_tax_ccid(p_cnt);
IF p_select_tab.interim_tax_ccid(p_cnt) = -1
THEN
p_select_rec.interim_tax_ccid := NULL;
p_select_rec.interim_tax_ccid := p_select_tab.interim_tax_ccid(p_cnt);
p_select_rec.site_use_id := p_select_tab.site_use_id(p_cnt);
p_select_rec.warehouse_id := p_select_tab.warehouse_id(p_cnt);
p_select_rec.link_to_cust_trx_line_id := p_select_tab.link_to_cust_trx_line_id(p_cnt);
return(p_select_rec);
debug('EXCEPTION: arp_auto_accounting.get_select_rec():'|| i, MSG_LEVEL_BASIC);
p_select_rec IN OUT NOCOPY select_rec_type,
p_total_percent IN OUT NOCOPY NUMBER,
p_total_amount IN OUT NOCOPY NUMBER,
p_total_acctd_amount IN OUT NOCOPY NUMBER,
p_failure_count IN OUT NOCOPY BINARY_INTEGER,
p_mode IN VARCHAR2,
p_request_id IN BINARY_INTEGER ) IS
l_boolean BOOLEAN;
IF( p_select_rec.gl_date IS NOT NULL ) THEN
l_boolean :=
arp_standard.validate_and_default_gl_date
( to_date(p_select_rec.gl_date, 'J'),
to_date(p_select_rec.original_gl_date, 'J'),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
p_select_rec.allow_not_open_flag,
NULL,
p_system_info.system_parameters.set_of_books_id,
222,
l_default_gl_date,
l_default_rule_used,
l_error_message );
p_select_rec.gl_date := to_char( l_default_gl_date, 'J' );
correct_rounding_errors( p_select_rec,
p_total_percent,
p_total_amount,
p_total_acctd_amount );
validation_date := NVL(TO_DATE(p_select_rec.gl_date, 'J'), G_SYS_DATE);
IF (p_select_rec.account_class = 'REV' AND
p_select_rec.account_set_flag = 'N' AND
g_test_collectibility) THEN
/* Bug 3440172/3446698 - Conflict between autoaccounting
and collectibility causing imported DMs and on acct
CMs to be missing REV distributions */
/* Bug 4693399 - manually entered transactions can also be deferred
by contingencies */
/*Bug 9112739 added for run in update mode */
IF (p_mode in ( 'I' ,'U') AND
t_collect.EXISTS(p_select_rec.customer_trx_line_id))
THEN
IF (t_collect(p_select_rec.customer_trx_line_id) =
ar_revenue_management_pvt.defer) THEN
p_select_rec.account_class := 'UNEARN';
p_select_rec.code_combination_id := NULL;
IF( p_select_rec.code_combination_id IS NULL ) THEN
flex_manager( p_select_rec.account_class,
p_select_rec.customer_trx_line_id,
p_select_rec.cust_trx_type_id,
p_select_rec.salesrep_id,
p_select_rec.inventory_item_id,
p_select_rec.memo_line_id,
p_select_rec.default_tax_ccid,
p_select_rec.interim_tax_ccid,
p_select_rec.site_use_id,
p_select_rec.warehouse_id,
p_select_rec.code_combination_id,
p_select_rec.concatenated_segments,
p_select_rec.int_code_combination_id,
p_select_rec.int_concatenated_segments );
IF( p_select_rec.code_combination_id = -1 ) THEN
-- keep track of # rows where ccid was not found
-- if > 0, then need to call AOL dynamic insert
-- on the client-side
--
p_failure_count := nvl(p_failure_count, 0) + 1;
IF( p_select_rec.int_code_combination_id = -1 ) THEN
p_failure_count := nvl(p_failure_count, 0) + 1;
l_select_rec select_rec_type;
l_select_tab select_rec_tab;
l_null_rec CONSTANT select_rec_type := l_select_rec;
l_select_c INTEGER;
l_delete_c INTEGER;
select customer_trx_line_id from ra_customer_trx_lines
where customer_trx_id = p_customer_trx_id
and line_type = 'LINE';
l_select_rec := l_null_rec; -- start with null record
l_select_rec.customer_trx_line_id := p_customer_trx_line_id;
l_select_rec.account_class := p_account_class;
l_select_rec.cust_trx_type_id := p_cust_trx_type_id;
l_select_rec.salesrep_id := p_primary_salesrep_id;
l_select_rec.inventory_item_id := p_inventory_item_id;
l_select_rec.memo_line_id := p_memo_line_id;
l_select_rec.site_use_id := p_site_use_id;
l_select_rec.warehouse_id := p_warehouse_id;
dump_select_rec( l_select_rec );
l_select_rec,
l_total_percent,
l_total_amount,
l_total_acctd_amount,
p_failure_count,
p_mode,
p_request_id );
p_ccid := l_select_rec.code_combination_id;
p_concat_segments := l_select_rec.concatenated_segments;
-- Delete distributions in Update and Delete mode
--
----------------------------------------------------------------
-- Construct delete stmt
----------------------------------------------------------------
DECLARE
l_delete_stmt VARCHAR2(32767);
l_delete_c := dbms_sql.open_cursor;
l_delete_stmt := build_delete_sql( system_info,
profile_info,
p_account_class,
p_customer_trx_id,
p_customer_trx_line_id,
p_cust_trx_line_salesrep_id,
p_request_id );
l_delete_stmt := l_delete_stmt ||
' RETURNING cust_trx_line_gl_dist_id INTO :gl_dist_key_value ';
dbms_sql.parse( l_delete_c, l_delete_stmt, dbms_sql.v7 );
dbms_sql.bind_array(l_delete_c,':gl_dist_key_value',
gl_dist_array);
debug( 'Error constructing/parsing delete cursor',
MSG_LEVEL_BASIC );
l_ignore := dbms_sql.execute( l_delete_c );
debug( to_char(l_ignore) || ' row(s) deleted',
MSG_LEVEL_DEBUG );
dbms_sql.variable_value( l_delete_c, ':gl_dist_key_value',
gl_dist_array);
| call mrc engine to delete from ra_cust_trx_line_gl_dist |
+---------------------------------------------------------*/
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug('do_autoaccounting: ' || 'before calling maintain_mrc ');
p_event_mode => 'DELETE',
p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
p_mode => 'SINGLE',
p_key_value => gl_dist_array(I));
close_cursor( l_delete_c );
debug( 'Error executing delete stmt', MSG_LEVEL_BASIC );
/* Bug 2560036 - Call collectibility when in INSERT mode
only. */
/*9112739 Added for run in update mode*/
IF (p_mode IN ( 'I' ,'U') AND
p_account_class in ('REV','ALL') AND
g_test_collectibility AND
NOT g_called_collectibility) THEN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug(' testing collectibility...');
-- Insert distributions in Insert and Update mode
--
--
-- Fetch records using select stmt
--
-- Bug 853040
/*Bug 2034221:Added 'Freight' also in the clause to prevent the NULL value
passed for ccid.
*/
IF p_passed_ccid IS NOT NULL AND
p_account_class in('REC','FREIGHT') THEN
p_ccid := p_passed_ccid;
l_select_c := Get_Select_Cursor(
system_info,
profile_info,
p_account_class,
p_customer_trx_id,
p_customer_trx_line_id,
p_cust_trx_line_salesrep_id,
p_request_id,
p_gl_date,
p_original_gl_date,
p_total_trx_amount,
p_ccid,
p_force_account_set_no,
p_cust_trx_type_id,
p_primary_salesrep_id,
p_inventory_item_id,
p_memo_line_id,
l_use_unearn_srep_dependency,
l_keep_cursor_open_flag);
l_select_c,
system_info,
profile_info,
p_account_class,
p_customer_trx_id,
p_customer_trx_line_id,
p_cust_trx_line_salesrep_id,
p_request_id,
p_gl_date,
p_original_gl_date,
p_total_trx_amount,
p_passed_ccid,
p_force_account_set_no,
p_cust_trx_type_id,
p_primary_salesrep_id,
p_inventory_item_id,
p_memo_line_id,
l_keep_cursor_open_flag);
debug( ' Executing select stmt', MSG_LEVEL_DEBUG );
l_ignore := dbms_sql.execute( l_select_c );
debug( 'Error executing select cursor', MSG_LEVEL_BASIC );
debug( ' Fetching select stmt', MSG_LEVEL_DEBUG );
l_rows_fetched := dbms_sql.fetch_rows( l_select_c );
l_select_rec := l_null_rec;
get_column_values( l_select_c, l_select_tab );
dump_select_tab( l_select_tab, l_low, l_high );
close_cursor( l_select_c );
close_cursor( l_select_c );
-- No rows selected
IF (l_first_fetch) THEN
debug( ' raising NO_DATA_FOUND', MSG_LEVEL_DEBUG );
debug( 'Error fetching select cursor', MSG_LEVEL_BASIC );
l_select_rec := get_select_rec (l_select_tab, i);
l_select_rec,
l_total_percent,
l_total_amount,
l_total_acctd_amount,
p_failure_count,
p_mode,
p_request_id );
l_select_tab.int_concatenated_segments(i) := l_select_rec.int_concatenated_segments;
l_select_tab.int_code_combination_id(i) := l_select_rec.int_code_combination_id;
l_select_tab.code_combination_id(i) := l_select_rec.code_combination_id;
l_select_tab.concatenated_segments(i) := l_select_rec.concatenated_segments;
l_select_tab.account_class(i) := l_select_rec.account_class;
insert_dist_row( system_info,
profile_info,
p_request_id,
l_select_tab,
l_low,
l_high);
debug( 'Error inserting distributions', MSG_LEVEL_BASIC );
l_select_rec := get_select_rec (l_select_tab, i);
l_select_rec.int_concatenated_segments := l_select_tab.int_concatenated_segments(i);
l_select_rec.int_code_combination_id := l_select_tab.int_code_combination_id(i);
l_select_rec.code_combination_id := l_select_tab.code_combination_id(i);
l_select_rec.concatenated_segments := l_select_tab.concatenated_segments(i);
IF ( l_select_rec.code_combination_id = -1 ) THEN
IF ( p_account_class = REV ) THEN
put_message_on_stack(l_select_rec.customer_trx_line_id,
MSG_COMPLETE_REV_ACCOUNT,
l_select_rec.concatenated_segments,
p_request_id );
put_message_on_stack(-1 * l_select_rec.customer_trx_id,
MSG_COMPLETE_REC_ACCOUNT,
l_select_rec.concatenated_segments,
p_request_id );
l_select_rec.customer_trx_line_id,
MSG_COMPLETE_FRT_ACCOUNT,
l_select_rec.concatenated_segments,
p_request_id );
NVL(l_select_rec.link_to_cust_trx_line_id,
l_select_rec.customer_trx_line_id),
MSG_COMPLETE_TAX_ACCOUNT,
l_select_rec.concatenated_segments,
p_request_id );
l_select_rec.customer_trx_line_id,
MSG_COMPLETE_CHARGES_ACCOUNT,
l_select_rec.concatenated_segments,
p_request_id );
l_select_rec.customer_trx_line_id,
MSG_COMPLETE_OFFSET_ACCOUNT,
l_select_rec.concatenated_segments,
p_request_id );
IF l_select_rec.int_code_combination_id = -1 THEN
/* 1651593 - Point tax lines to parent line for error */
put_message_on_stack(
NVL(l_select_rec.link_to_cust_trx_line_id,
l_select_rec.customer_trx_line_id),
MSG_COMPLETE_INT_TAX_ACCOUNT,
l_select_rec.int_concatenated_segments,
p_request_id );
close_cursor( l_select_c );
close_cursor( l_delete_c );
THEN close_cursor( l_select_c );
close_cursor( l_delete_c );
close_cursor( l_select_c );
close_cursor( l_delete_c );
l_select_rec select_rec_type;
l_null_rec CONSTANT select_rec_type := l_select_rec;
SELECT invoicing_rule_id,
create_clearing_flag
INTO l_invoicing_rule_id,
l_create_clearing_flag
FROM ra_customer_trx t,
ra_batch_sources b
WHERE customer_trx_id = p_customer_trx_id
AND t.batch_source_id = b.batch_source_id;
SELECT line_type
INTO l_line_type
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_customer_trx_line_id;
SELECT t.bill_to_site_use_id
INTO l_bill_to_site_use_id
FROM ra_customer_trx t
WHERE t.customer_trx_id = p_customer_trx_id;
select_stmt VARCHAR2(32767);
delete_stmt VARCHAR2(32767);
select_stmt :=
build_select_sql(system_info, profile_info,
REV, 1, 2, 3, 12,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );
debug('Insert Mode');
debug('Update Mode');
debug('Delete Mode');
debug('Insert Mode: ALL');