The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_select1 INTEGER;
X_select2 INTEGER;
X_selection INTEGER;
x_dyn_insert VARCHAR2(2) ;
/* Select the segment number, table name and constant to get the account */
CURSOR c1 IS
SELECT segment, table_name, constant
FROM ra_account_default_segments
WHERE gl_default_id = X_gl_default_id;
x_dyn_insert := 'N';
SELECT gl_default_id
INTO X_gl_default_id
FROM ra_account_defaults
WHERE type=X_account_type;
SELECT count(*)
INTO X_counter
FROM ra_account_default_segments
WHERE gl_default_id = X_gl_default_id;
SELECT gl_id_rev, gl_id_rec
INTO X_cust_rev_id, X_cust_rec_id
FROM ra_cust_trx_types
WHERE cust_trx_type_id = X_cust_trx_type_id;
SELECT gl_id_rev, gl_id_rec
INTO X_sale_rev_id, X_sale_rec_id
FROM ra_salesreps
WHERE salesrep_id = X_salesrep_id;
SELECT gl_id_rev, gl_id_rec
INTO X_site_rev_id, X_site_rec_id
FROM HZ_CUST_SITE_USES ---FROM ra_site_uses --Replaced by the R12 table
WHERE site_use_id = x_billto_site_use_id;
X_select1 := DBMS_SQL.OPEN_CURSOR;
/* Mount the select dynamically to find the segment (X) in
gl_code_combinations table */
/*Bug 2939830 - SQL Bind compliance project
DBMS_SQL.PARSE(X_select1, 'SELECT '||X_segment_name||'
FROM gl_code_combinations WHERE code_combination_id = '
|| X_gl_id, DBMS_SQL.v7);
DBMS_SQL.PARSE(X_select1, 'SELECT '||X_segment_name||'
FROM gl_code_combinations WHERE code_combination_id = :x and chart_of_accounts_id = :y ', DBMS_SQL.v7);
DBMS_SQL.BIND_VARIABLE (X_select1, ':x', X_gl_id) ;
DBMS_SQL.BIND_VARIABLE (X_select1, ':y', X_struct_num) ;
DBMS_SQL.DEFINE_COLUMN(X_select1,1,X_segment_amount,25);
X_selection := DBMS_SQL.EXECUTE(X_select1);
IF DBMS_SQL.FETCH_ROWS (X_select1) > 0 THEN
DBMS_SQL.COLUMN_VALUE(X_select1,1,X_segment_amount);
DBMS_SQL.CLOSE_CURSOR(X_select1);
/* Mount the select to get the account on gl_code_combinations */
-- Bug 2089230 following close cursor was moved above - before end loop.
--DBMS_SQL.CLOSE_CURSOR(X_select1);
X_select2 := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(X_select2,'SELECT code_combination_id FROM gl_code_combinations WHERE '||X_condition,DBMS_SQL.v7);
DBMS_SQL.DEFINE_COLUMN(X_select2,1,X_amount_id);
X_selection := DBMS_SQL.EXECUTE(X_select2);
IF DBMS_SQL.FETCH_ROWS (X_select2) > 0 THEN
DBMS_SQL.COLUMN_VALUE(X_select2,1,X_amount_id);
DBMS_SQL.CLOSE_CURSOR(X_select2);
SELECT DYNAMIC_INSERTS_ALLOWED_FLAG
INTO x_dyn_insert
FROM fnd_id_flex_Structures ffs
WHERE ffs.APPLICATION_ID = 101
AND ffs.ID_FLEX_CODE = 'GL#'
AND ffs.ID_FLEX_NUM = x_struct_num;
x_dyn_insert := 'N';
IF X_dyn_insert = 'Y' THEN
x_dummy := FND_FLEX_EXT.get_combination_id ('SQLGL', 'GL#',x_struct_num,
sysdate, x_counter,
x_segs,x_amount_id);
/* Purpose : Get all the fields to insert row in ra_batches */
/************************************************************************/
PROCEDURE ins_ra_batches (
X_batch_source_id IN NUMBER,
X_invoice_amount IN NUMBER,
X_invoice_currency_code IN VARCHAR2,
X_user_id IN NUMBER,
X_batch_id IN OUT NOCOPY NUMBER
) IS
X_batch_name VARCHAR2(50);
SELECT set_of_books_id
INTO X_set_of_books_id
FROM ar_system_parameters;
SELECT nvl(max(instr(trx_number,'-NDJ')) + 4,0) --bug 6011423
INTO X_first_position
FROM ra_customer_trx
WHERE related_customer_trx_id=X_original_customer_trx_id
AND trx_number like '%-NDJ%';
SELECT nvl(MAX(TO_NUMBER(SUBSTR(trx_number,X_first_position,LENGTH(trx_number)-
X_first_position+1)))+1,1) --bug 6011423
INTO X_next_sequence
FROM ra_customer_trx
WHERE related_customer_trx_id=X_original_customer_trx_id
AND trx_number LIKE '%-NDJ%';
/* If the selects failure, then this is the first
Interest Debit Memo to this transaction */
EXCEPTION
WHEN NO_DATA_FOUND THEN
X_next_sequence := 1;
SELECT trx_number, terms_sequence_number
INTO X_trx_number, X_terms_sequence
FROM ar_payment_schedules
WHERE payment_schedule_id = X_payment_schedule_id;
/* Purpose : Get the fields to insert into ra_customer_trx */
/************************************************************************/
PROCEDURE ins_ra_customer_trx (
X_inv_cust_trx_id IN NUMBER,
X_new_cust_trx_id IN OUT NOCOPY NUMBER,
X_set_of_books_id IN OUT NOCOPY NUMBER,
X_lastlogin IN OUT NOCOPY NUMBER,
X_primary_salesrep_id IN OUT NOCOPY NUMBER,
X_billto_customer_id IN OUT NOCOPY NUMBER,
X_billto_site_use_id IN OUT NOCOPY NUMBER,
X_invoice_currencycode IN OUT NOCOPY VARCHAR2,
X_trx_number IN OUT NOCOPY VARCHAR2,
X_termid IN OUT NOCOPY NUMBER,
X_legal_entity_id IN OUT NOCOPY NUMBER, -- Bug#7835709
X_cust_trx_type_id IN NUMBER,
X_payment_schedule_id IN NUMBER,
X_user_id IN NUMBER,
X_batch_source_id IN NUMBER,
X_receipt_method_id IN NUMBER,
X_batch_id IN NUMBER,
X_idm_date IN DATE
) IS
X_sold_to_customer_id NUMBER(15);
/* SELECT ra_customer_trx_s.nextval
INTO X_new_cust_trx_id
FROM sys.dual;
SELECT to_number(global_attribute20)
INTO X_termid
FROM ar_system_parameters;
SELECT last_update_login,
set_of_books_id,
sold_to_customer_id,
bill_to_customer_id,
bill_to_site_use_id,
ship_to_customer_id,
ship_to_site_use_id,
remit_to_address_id,
primary_salesrep_id,
printing_option,
territory_id,
invoice_currency_code,
legal_entity_id, -- Bug#7835709
attribute1,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
org_id
INTO X_lastlogin,
X_set_of_books_id,
X_sold_to_customer_id,
X_billto_customer_id,
X_billto_site_use_id,
X_ship_to_customer_id,
X_ship_to_site_use_id,
X_remit_to_address_id,
X_primary_salesrep_id,
X_printing_option,
X_territory_id,
X_invoice_currencycode,
X_legal_entity_id, -- Bug#7835709
X_attribute1,
X_global_attribute1,
X_global_attribute2,
X_global_attribute3,
X_global_attribute4,
X_global_attribute5,
X_global_attribute6,
X_global_attribute7,
X_org_id
FROM ra_customer_trx
WHERE customer_trx_id = X_inv_cust_trx_id;
/* INSERT INTO ra_customer_trx (
customer_trx_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
trx_number,
related_customer_trx_id,
cust_trx_type_id,
trx_date,
set_of_books_id,
batch_source_id,
batch_id,
sold_to_customer_id,
bill_to_customer_id,
bill_to_site_use_id,
ship_to_customer_id,
ship_to_site_use_id,
remit_to_address_id,
term_id,
primary_salesrep_id,
printing_option,
printing_pending,
territory_id,
invoice_currency_code,
attribute1,
complete_flag,
receipt_method_id,
status_trx,
default_tax_exempt_flag,
created_from,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7
) VALUES (
X_new_cust_trx_id,
sysdate,
X_user_id,
sysdate,
X_user_id,
X_lastlogin,
X_trx_number,
X_inv_cust_trx_id,
X_cust_trx_type_id,
X_idm_date,
X_set_of_books_id,
X_batch_source_id,
X_batch_id,
X_sold_to_customer_id,
X_billto_customer_id,
X_billto_site_use_id,
X_ship_to_customer_id,
X_ship_to_site_use_id,
X_remit_to_address_id,
X_termid,
X_primary_salesrep_id,
X_printing_option,
'N',
X_territory_id,
X_invoice_currencycode,
X_attribute1,
'Y',
X_receipt_method_id,
'OP',
'S',
'RAXMATRX',
X_global_attribute1,
X_global_attribute2,
X_global_attribute3,
X_global_attribute4,
X_global_attribute5,
X_global_attribute6,
X_global_attribute7
);
/* Replace Insert by AR's Table Handlers. Bug # 2249731 */
l_trx_rec.last_update_date := sysdate;
l_trx_rec.last_updated_by := X_user_id;
l_trx_rec.last_update_login := X_lastlogin;
arp_ct_pkg.insert_p(l_trx_rec, X_trx_number, X_new_cust_trx_id);
/* Purpose : Get the fields to insert into ra_customer_trx_lines */
/************************************************************************/
PROCEDURE ins_ra_customer_trx_lines (
X_new_customer_trx_id IN NUMBER,
X_invoice_amount IN NUMBER,
X_set_of_books_id IN NUMBER,
X_user_id IN NUMBER,
X_last_login IN NUMBER,
X_customertrx_line_id IN OUT NOCOPY NUMBER
) IS
l_org_id NUMBER(15);
SELECT ra_customer_trx_lines_s.nextval
INTO X_customertrx_line_id
FROM dual;
SELECT org_id into l_org_id
FROM ra_customer_trx_all
Where customer_trx_id = x_new_customer_trx_id;
INSERT INTO ra_customer_trx_lines (
customer_trx_line_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
customer_trx_id,
line_number,
set_of_books_id,
description,
quantity_invoiced,
unit_selling_price,
line_type,
extended_amount,
revenue_amount,
tax_exempt_flag,
org_id
) VALUES (
X_customertrx_line_id,
sysdate,
X_user_id,
sysdate,
X_user_id,
X_last_login,
X_new_customer_trx_id,
'1',
X_set_of_books_id,
'Nota de Debito Juros',
'1',
X_invoice_amount,
'LINE',
X_invoice_amount,
X_invoice_amount,
'S',
l_org_id
);
/* Purpose : Get the fields to insert into ra_cust_trx_line_salesreps */
/***************************************************************************/
PROCEDURE ins_ra_cust_trx_line_salesreps (
X_new_cust_trx_id IN NUMBER,
X_new_cust_trx_line_id IN NUMBER,
X_salesrep_id IN NUMBER,
X_user_id IN NUMBER,
X_last_login IN NUMBER,
X_invoice_amount IN NUMBER
) IS
l_org_id NUMBER(15);
SELECT org_id into l_org_id
FROM ra_customer_trx_all where
customer_trx_id = x_new_cust_trx_id;
INSERT INTO ra_cust_trx_line_salesreps (
cust_trx_line_salesrep_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
customer_trx_id,
salesrep_id,
revenue_percent_split,
revenue_amount_split,
org_id
) VALUES (
ra_cust_trx_line_salesreps_s.nextval,
sysdate,
X_user_id,
sysdate,
X_user_id,
X_last_login,
X_new_cust_trx_id,
X_salesrep_id,
'100',
X_invoice_amount,
l_org_id
);
INSERT INTO ra_cust_trx_line_salesreps (
cust_trx_line_salesrep_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
customer_trx_id,
customer_trx_line_id,
salesrep_id,
revenue_percent_split,
revenue_amount_split,
org_id
) VALUES (
ra_cust_trx_line_salesreps_s.nextval,
sysdate,
X_user_id,
sysdate,
X_user_id,
X_last_login,
X_new_cust_trx_id,
X_new_cust_trx_line_id,
X_salesrep_id,
'100',
X_invoice_amount,
l_org_id
);
/* Purpose : Get the fields to insert into ra_cust_trx_line_gl_dist*/
/************************************************************************/
PROCEDURE ins_ra_cust_trx_line_gl_dist (
X_customer_trx_id IN NUMBER,
X_customer_trx_line_id IN OUT NOCOPY NUMBER,
X_invoice_amount IN NUMBER,
X_set_of_books_id IN NUMBER,
X_user_id IN NUMBER,
X_batch_source_id IN NUMBER,
X_last_login IN NUMBER,
X_cust_trx_type_id IN NUMBER,
X_billto_site_use_id IN NUMBER, -- Bug#7718063
X_salesrep_id IN NUMBER,
X_account_type IN VARCHAR,
X_idm_date IN DATE,
x_int_revenue_ccid IN NUMBER,
X_invoice_currency_code IN VARCHAR2,
X_minimum_accountable_unit IN NUMBER,
X_precision IN NUMBER,
x_error_code OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2,
x_token OUT NOCOPY VARCHAR2
) IS
X_code_id NUMBER(15);
SELECT org_id into l_org_id from ra_customer_trx_all where
customer_trx_id = x_customer_trx_id;
SELECT chart_of_accounts_id into x_struct_num FROM gl_sets_of_books
WHERE set_of_books_id = x_set_of_books_id;
SELECT ra_cust_trx_line_salesreps_s.nextval
INTO X_line_salesrepid
FROM sys.dual;
SELECT post_to_gl
INTO X_post_gl
FROM ra_cust_trx_types
WHERE cust_trx_type_id = X_cust_trx_type_id;
/* INSERT INTO ra_cust_trx_line_gl_dist (
cust_trx_line_gl_dist_id,
customer_trx_line_id,
code_combination_id,
set_of_books_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
percent,
amount,
gl_date,
cust_trx_line_salesrep_id,
original_gl_date,
posting_control_id,
account_class,
customer_trx_id,
account_set_flag,
acctd_amount,
latest_rec_flag
) VALUES (
ra_cust_trx_line_gl_dist_s.nextval,
X_custtrx_line_id,
X_code_id,
X_set_of_books_id,
sysdate,
X_user_id,
sysdate,
X_user_id,
'100',
X_invoice_amount,
X_gl_date,
X_line_salesrepid,
X_gl_date,
'-3',
X_account_type,
X_customer_trx_id,
'N',
X_invoice_amount,
X_latest_rec_flag
);
ARP_CTLGD_PKG.insert_p (l_dist_rec, X_cust_trx_line_gl_dist_id,NULL,X_invoice_currency_code,X_precision,X_minimum_accountable_unit);
/* Purpose : Get the fields to insert into ar_payment_schedules */
/************************************************************************/
PROCEDURE ins_ar_payment_schedules (
X_user_id IN NUMBER,
X_last_login IN NUMBER,
X_invoice_amount IN NUMBER,
X_invoice_currency_code IN VARCHAR2,
X_cust_trx_type_id IN NUMBER,
X_customer_id IN NUMBER,
X_customer_site_use_id IN NUMBER,
X_customer_trx_id IN NUMBER,
X_term_id IN NUMBER,
X_trx_number IN VARCHAR2,
X_idm_date IN DATE
) IS
X_payment_scheduleid NUMBER(15);
SELECT ar_payment_schedules_s.nextval
INTO X_payment_scheduleid
FROM sys.dual;
/* INSERT INTO ar_payment_schedules (
payment_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
due_date,
amount_due_original,
amount_due_remaining,
number_of_due_dates,
status,
invoice_currency_code,
class,
cust_trx_type_id,
customer_id,
customer_site_use_id,
customer_trx_id,
term_id,
terms_sequence_number,
gl_date_closed,
actual_date_closed,
amount_line_items_original,
amount_line_items_remaining,
trx_number,
trx_date,
gl_date,
acctd_amount_due_remaining
) VALUES (
X_payment_scheduleid,
sysdate,
X_user_id,
sysdate,
X_user_id,
X_last_login,
X_idm_date,
X_invoice_amount,
X_invoice_amount,
'1',
'OP',
X_invoice_currency_code,
'DM',
X_cust_trx_type_id,
X_customer_id,
X_customer_site_use_id,
X_customer_trx_id,
X_term_id,
'1',
to_date('31124712','DDMMYYYY'),
to_date('31124712','DDMMYYYY'),
X_invoice_amount,
X_invoice_amount,
X_trx_number,
X_idm_date,
X_idm_date,
X_invoice_amount
);
/* Replace Insert by AR's table handler. Bug # 2249731 */
SELECT org_id into l_org_id from
ra_customer_trx_all
where customer_trx_id = X_customer_trx_id;
l_ps_rec.last_update_date := sysdate;
l_ps_rec.last_updated_by := X_user_id;
l_ps_rec.last_update_login := X_last_login;
arp_ps_pkg.insert_p(l_ps_rec, X_payment_scheduleid);
select to_date(X_interest_date,'DD-MM-YYYY')
into X_interest_DM_date
from dual;
SELECT invoice_currency_code, minimum_accountable_unit, precision
INTO X_invoice_currency_code, x_minimum_accountable_unit, x_precision
FROM ra_customer_trx, fnd_currencies_vl
WHERE customer_trx_id = X_original_customer_trx_id
AND invoice_currency_code = currency_code;
select set_of_books_id
into l_set_of_books_id
from ar_system_parameters
where org_id = nvl(l_org_id,org_id);
select count(1)
into l_cnt
from gl_period_statuses
where application_id = l_application_id
and set_of_books_id = l_set_of_books_id
and adjustment_period_flag = 'N'
and trunc(x_receipt_date) between trunc(start_date) and trunc(end_date)
and closing_status in ('O', 'F');
select count(1)
into l_cnt
from gl_period_statuses
where application_id = l_application_id
and set_of_books_id = l_set_of_books_id
and adjustment_period_flag = 'N'
and trunc(sysdate) between trunc(start_date) and trunc(end_date)
and closing_status in ('O', 'F');
select max(end_date)
into l_default_gl_date
from gl_period_statuses
where application_id = l_application_id
and set_of_books_id = l_set_of_books_id
and adjustment_period_flag = 'N'
and closing_status = 'O'
and trunc(start_date) < trunc(sysdate);
select min(start_date)
into l_default_gl_date
from gl_period_statuses
where application_id = l_application_id
and set_of_books_id = l_set_of_books_id
and adjustment_period_flag = 'N'
and closing_status = 'O'
and trunc(start_date) >= trunc(sysdate);