AP Invoice Details Query (Till GL)

SELECT aida.period_name,
       aia.invoice_num ap_inv_numer,
       aia.invoice_date ap_inv_date,
       aia.invoice_amount ap_inv_amount,
       aia.amount_paid ap_inv_amt_paid,
       aia.accts_pay_code_combination_id ap_inv_accts_pay_ccid,
       aida.amount ap_inv_dist_amount,
       aida.dist_code_combination_id ap_inv_dist_ccid,
          gcc1.segment1
       || '.'
       || gcc1.segment2
       || '.'
       || gcc1.segment3
       || '.'
       || gcc1.segment4
       || '.'
       || gcc1.segment5
       || '.'
       || gcc1.segment6
       || '.'
       || gcc1.segment7
       || '.'
       || gcc1.segment8 ap_inv_dist_code_combination,
       gjh.NAME gl_je_header_name,
       gjh.description gl_je_header_desc,
       gjl.je_line_num,
       gjl.code_combination_id gl_je_line_ccid
  FROM ap.ap_invoices_all aia,
       ap.ap_invoice_distributions_all aida,
       ap.ap_invoice_payments_all aipa,
       gl.gl_code_combinations gcc1,
       gl.gl_ledgers gl,
       xla.xla_transaction_entities xte,
       xla.xla_ae_headers xah,
       xla.xla_ae_lines xal,
       xla.xla_distribution_links xdl,
       gl.gl_import_references gir,
       gl.gl_je_headers gjh,
       gl.gl_je_lines gjl
 WHERE 1 = 1
   AND aia.org_id = :P_ORG_ID
   AND aia.SOURCE = :P_INVOICE_SOURCE
   AND aida.period_name = :P_PERIOD_NAME
   AND aia.invoice_id = :P_INVOICE_ID
   AND aida.invoice_id = aia.invoice_id
   AND gcc1.code_combination_id = aida.dist_code_combination_id
   AND aipa.invoice_id = aia.invoice_id
   AND gl.short_name = :P_LEDGER_SHORT_NAME
   AND gl.chart_of_accounts_id = gcc1.chart_of_accounts_id
   AND xte.ledger_id = gl.ledger_id
   AND xte.entity_code = 'AP_INVOICES'
   AND xte.source_id_int_1 = aia.invoice_id
   AND xah.entity_id = xte.entity_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xdl.ae_header_id = xah.ae_header_id
   AND xdl.ae_line_num = xal.ae_line_num
   AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
   AND xdl.applied_to_entity_code = 'AP_INVOICES'
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gjh.je_header_id = gir.je_header_id
   AND gjl.je_header_id = gjh.je_header_id
   AND gjl.je_line_num = gir.je_line_num;
 
Pass / Comment based on available values.

AR Invoice Details Query (Till GL)

SELECT trx.trx_number,
       trx.cust_trx_type_id,
       trx.bill_to_customer_id,
       trx_dist.amount ar_inv_dist_amount,
       trx_dist.code_combination_id ar_inv_dist_ccid,
          gcc.segment1
       || '.'
       || gcc.segment2
       || '.'
       || gcc.segment3
       || '.'
       || gcc.segment4
       || '.'
       || gcc.segment5
       || '.'
       || gcc.segment6
       || '.'
       || gcc.segment7
       || '.'
       || gcc.segment8
          ar_inv_dist_code_combination,
       gjh.name gl_je_header_name,
       gjh.description gl_je_header_desc,
       gjl.je_line_num,
       gjl.code_combination_id gl_je_line_ccid
  FROM ar.ra_customer_trx_all trx,
       ar.ra_cust_trx_line_gl_dist_all trx_dist,
       gl.gl_code_combinations gcc,
       gl.gl_ledgers gl,
       xla.xla_transaction_entities xte,
       xla.xla_ae_headers xah,
       xla.xla_ae_lines xal,
       xla.xla_distribution_links xdl,
       gl.gl_import_references gir,
       gl.gl_je_headers gjh,
       gl.gl_je_lines gjl
 WHERE 1 = 1
   -- AND trx.org_id = :P_ORG_ID
   -- AND trx.batch_source_id = :P_BATCH_SOURCE_ID
   AND trx.customer_trx_id = :P_CUSTOMER_TRX_ID
   AND trx.customer_trx_id = trx_dist.customer_trx_id
   AND gcc.code_combination_id = trx_dist.code_combination_id
   --  AND gl.short_name = :P_LEDGER_SHORT_NAME
   AND gl.chart_of_accounts_id = gcc.chart_of_accounts_id
   AND xte.ledger_id = gl.ledger_id
   AND xte.entity_code = 'TRANSACTIONS'
   AND xte.application_id = 222
   AND xte.source_id_int_1 = trx.customer_trx_id
   AND xah.entity_id = xte.entity_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.accounting_class_code = 'REVENUE'
   AND xdl.ae_header_id = xah.ae_header_id
   AND xdl.ae_line_num = xal.ae_line_num
   AND xdl.source_distribution_id_num_1 = trx_dist.cust_trx_line_gl_dist_id
   AND xdl.application_id = 222
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gjh.je_header_id = gir.je_header_id
   AND gjl.je_header_id = gjh.je_header_id
   AND gjl.je_line_num = gir.je_line_num;

Pass / Comment based on available values.

How to load multiple rows into single column in SQL Loader?

Option 1

Sample Table Script

CREATE TABLE XX_SQLLDR_DEMO_STG

(

   RECORD_ID   NUMBER,

   SENDER      VARCHAR2 (120),

   RECEIVER    VARCHAR2 (120),

   TEXT        VARCHAR2 (2000)

);

Sample Control File

LOAD DATA
CHARACTERSET UTF8
INFILE '$DATA_FILE_NAME' CONTINUEIF LAST <> '|'
INTO TABLE XX_SQLLDR_DEMO_STG
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
RECORD_ID "LTRIM(RTRIM(:RECORD_ID))",
SENDER    "LTRIM(RTRIM(:SENDER))",
RECEIVER  "LTRIM(RTRIM(:RECEIVER))",
TEXT      "LTRIM(RTRIM(:TEXT))"

)

Sample Data File

Need to Add the Data / Fields delimiter at the end of each record in Data File.


1|ABCD@XYZ.COM|XYZ@ABCD.COM|Hi XYZ,

how to load Multiple lines as single record in sqlldr?

Thanks & Regards,
ABCD|
2|XYZ@ABCD.COM|ABCD@XYZ.COM|Hi ABCD,

This is test data file for multiple lines loading into a single field.

Thanks & Regards,
xyz|


It will loads the data (replacing newline character) like this...

Option 2

Sample Control File

LOAD DATA
CHARACTERSET UTF8
INFILE '$DATA_FILE_NAME' "STR '~!~'"
INTO TABLE XX_SQLLDR_DEMO_STG
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
RECORD_ID "LTRIM(RTRIM(:RECORD_ID))",
SENDER    "LTRIM(RTRIM(:SENDER))",
RECEIVER  "LTRIM(RTRIM(:RECEIVER))",
TEXT      "LTRIM(RTRIM(:TEXT))"

)

Sample Data File

Need to Add the Data / Fields delimiter with unused string (like this "~!~") at the end of each record in Data File.


1|ABCD@XYZ.COM|XYZ@ABCD.COM|Hi XYZ,

how to load Multiple lines as single record in sqlldr?

Thanks & Regards,
ABCD|~!~
2|XYZ@ABCD.COM|ABCD@XYZ.COM|Hi ABCD,

This is test data file for multiple lines loading into a single field.

Thanks & Regards,
xyz|~!~


It will loads the data as expected...

How to find the locked tables in Oracle?

Execute this Query

SELECT   owner,
         object_name,
         oracle_username || ' (' || vs.status || ')' ora_user,
         os_user_name os_user,
         machine SYSTEM,
         vlo.process unix,
         '''' || vs.SID || ',' || vs.serial# || '''' sid_serial,
         vr.NAME roll_name,
         TO_CHAR (vs.logon_time, 'YYYY/MM/DD HH24:MI:SS') TIME
    FROM v$locked_object vlo,
         dba_objects doj,
         v$session vs,
         v$transaction vt,
         v$rollname vr
   WHERE vlo.object_id = doj.object_id
     AND vs.SID = vlo.session_id
     AND vs.taddr = vt.addr
     AND vt.xidusn = vr.usn
ORDER BY os_user,
         sid_serial,
         object_name

* Pass / Add condition for Object (Table) Name If required.

How to kill a particular session?

ALTER SYSTEM KILL SESSION 'v$session.sid,v$session.serial';