APPS.JL_BR_AR_GENERATE_DEBIT_MEMO SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 25

X_select1		INTEGER;

Line: 26

X_select2		INTEGER;

Line: 41

X_selection		INTEGER;

Line: 48

x_dyn_insert            VARCHAR2(2) ;

Line: 51

 /* 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;

Line: 60

  x_dyn_insert := 'N';

Line: 64

  SELECT gl_default_id
  INTO	X_gl_default_id
  FROM ra_account_defaults
  WHERE type=X_account_type;

Line: 71

  SELECT count(*)
  INTO	X_counter
  FROM ra_account_default_segments
  WHERE gl_default_id = X_gl_default_id;

Line: 80

    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;

Line: 90

    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;

Line: 107

    X_select1 := DBMS_SQL.OPEN_CURSOR;

Line: 139

  /* 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);

Line: 148

      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);

Line: 151

      DBMS_SQL.BIND_VARIABLE (X_select1, ':x', X_gl_id) ;

Line: 152

      DBMS_SQL.BIND_VARIABLE (X_select1, ':y', X_struct_num) ;

Line: 157

      DBMS_SQL.DEFINE_COLUMN(X_select1,1,X_segment_amount,25);

Line: 158

      X_selection := DBMS_SQL.EXECUTE(X_select1);

Line: 159

      IF DBMS_SQL.FETCH_ROWS (X_select1) > 0 THEN
        DBMS_SQL.COLUMN_VALUE(X_select1,1,X_segment_amount);

Line: 180

    DBMS_SQL.CLOSE_CURSOR(X_select1);

Line: 185

  /* 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);

Line: 189

  X_select2 := DBMS_SQL.OPEN_CURSOR;

Line: 192

  DBMS_SQL.PARSE(X_select2,'SELECT code_combination_id FROM gl_code_combinations WHERE '||X_condition,DBMS_SQL.v7);

Line: 194

  DBMS_SQL.DEFINE_COLUMN(X_select2,1,X_amount_id);

Line: 195

  X_selection := DBMS_SQL.EXECUTE(X_select2);

Line: 196

  IF DBMS_SQL.FETCH_ROWS (X_select2) > 0 THEN
    DBMS_SQL.COLUMN_VALUE(X_select2,1,X_amount_id);

Line: 199

  DBMS_SQL.CLOSE_CURSOR(X_select2);

Line: 212

        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;

Line: 221

          x_dyn_insert := 'N';

Line: 224

      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);

Line: 258

/*	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);

Line: 273

  SELECT set_of_books_id
  INTO	X_set_of_books_id
  FROM ar_system_parameters;

Line: 309

  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%';

Line: 315

  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%';

Line: 322

  /*	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;

Line: 333

  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;

Line: 344

/*	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_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);

Line: 386

/*  SELECT ra_customer_trx_s.nextval
  INTO X_new_cust_trx_id
  FROM sys.dual;

Line: 394

  SELECT to_number(global_attribute20)
  INTO	X_termid
  FROM ar_system_parameters;

Line: 398

  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,
  	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_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;

Line: 443

/*  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
  );

Line: 524

/* Replace Insert by AR's Table Handlers. Bug # 2249731 */

    l_trx_rec.last_update_date :=   	  sysdate;

Line: 527

    l_trx_rec.last_updated_by :=   	  X_user_id;

Line: 530

    l_trx_rec.last_update_login :=   	  X_lastlogin;

Line: 565

    arp_ct_pkg.insert_p(l_trx_rec, X_trx_number, X_new_cust_trx_id);

Line: 575

/*	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);

Line: 588

  SELECT ra_customer_trx_lines_s.nextval
  INTO X_customertrx_line_id
  FROM dual;

Line: 592

  SELECT org_id into l_org_id
  FROM   ra_customer_trx_all
  Where  customer_trx_id = x_new_customer_trx_id;

Line: 596

  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
  );

Line: 637

/*	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);

Line: 649

  SELECT org_id into l_org_id
  FROM ra_customer_trx_all where
  customer_trx_id = x_new_cust_trx_id;

Line: 653

  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
  );

Line: 679

  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
  );

Line: 710

/*	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_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);

Line: 747

  SELECT org_id into l_org_id from ra_customer_trx_all where
  customer_trx_id = x_customer_trx_id;

Line: 749

  SELECT chart_of_accounts_id into x_struct_num FROM gl_sets_of_books
  WHERE  set_of_books_id = x_set_of_books_id;

Line: 767

      SELECT ra_cust_trx_line_salesreps_s.nextval
      INTO X_line_salesrepid
      FROM sys.dual;

Line: 774

  SELECT post_to_gl
  INTO	X_post_gl
  FROM	ra_cust_trx_types
  WHERE	cust_trx_type_id = X_cust_trx_type_id;

Line: 786

 /* 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
  );

Line: 843

   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);

Line: 857

/*	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);

Line: 877

  SELECT ar_payment_schedules_s.nextval
  INTO X_payment_scheduleid
  FROM sys.dual;

Line: 882

/*  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
  );

Line: 952

/* 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;

Line: 957

  l_ps_rec.last_update_date := sysdate;

Line: 958

  l_ps_rec.last_updated_by :=  X_user_id;

Line: 961

  l_ps_rec.last_update_login :=  X_last_login;

Line: 985

  arp_ps_pkg.insert_p(l_ps_rec, X_payment_scheduleid);

Line: 1043

  select to_date(X_interest_date,'DD-MM-YYYY')
  into X_interest_DM_date
  from dual;

Line: 1051

  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;