Subledger-Ledger Linkage (Accounts Receivable)

Home » Ar_cash_receipts_all And Xla_ae_headers » Subledger-Ledger Linkage (Accounts Receivable)

Maybe your like

Tuesday, December 10, 2013

Subledger-Ledger Linkage (Accounts Receivable)

AR (Cash Receipt) select distinct arc.receipt_number ,arc.receipt_date ,gjl.entered_cr ,gjl.entered_dr ,gjl.accounted_cr ,gjl.accounted_dr ,gjh.name journal_name ,gjh.creation_date journal_creation_date ,glc.segment1 from ar_cash_receipt_history_all arh ,ar_distributions_all ard ,ar_cash_receipts_all arc ,ar_batches_all arb ,gl_code_combinations glc ,xla.xla_transaction_entities te ,xla_ae_headers xah ,xla_ae_lines xal ,xla_distribution_links xdl ,gl_import_references gir ,gl_je_headers gjh ,gl_je_lines gjl where arh.cash_receipt_history_id = ard.source_id and arc.cash_receipt_id = arh.cash_receipt_id and arh.batch_id = arb.batch_id (+) and ard.source_table = 'CRH' and arc.type = 'CASH' and arh.posting_control_id <> -3 and nvl (te.source_id_int_1, -99) = arc.cash_receipt_id and te.application_id = 222 and te.entity_code = 'RECEIPTS' and xah.entity_id = te.entity_id and xah.event_id = arh.event_id and xah.ae_header_id = xal.ae_header_id and xal.code_combination_id = ard.code_combination_id and xdl.ae_header_id = xah.ae_header_id and xdl.ae_header_id = xal.ae_header_id and xdl.event_id = xah.event_id and gir.gl_sl_link_id = xal.gl_sl_link_id and gjh.je_header_id = gir.je_header_id and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL' and gjh.je_source = 'Receivables' and gjh.je_category = 'Receipts' and xah.event_type_code <> 'MANUAL' and xal.gl_sl_link_table = 'XLAJEL' and gir.je_line_num = gjl.je_line_num and gjl.code_combination_id = glc.code_combination_id and gjl.je_header_id = gjh.je_header_id --and arc.receipt_number = 'MH1206201210' ;AR (Misc Receipt) select distinct acr.receipt_number ,gjl.entered_cr ,gjl.entered_dr ,gjl.accounted_cr ,gjl.accounted_dr ,gjh.name journal_name ,gjh.creation_date journal_creation_date ,gcc.segment1 from gl_code_combinations gcc ,gl_je_lines gjl ,gl_je_headers gjh ,gl_je_batches glb ,gl_import_references gir ,xla_ae_lines xal ,xla_ae_headers xah ,xla_distribution_links xdl ,ar_misc_cash_distributions_all msd ,ar_distributions_all dist ,ar_cash_receipts_all acr ,ar_receivables_trx_all art where gcc.code_combination_id = gjl.code_combination_id and gjh.je_header_id = gjl.je_header_id and glb.je_batch_id = gjh.je_batch_id and gjl.status = 'P' and gjh.actual_flag = 'A' and gir.je_header_id = gjh.je_header_id and gir.gl_sl_link_id = xal.gl_sl_link_id and xah.ae_header_id = xal.ae_header_id and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL' and gjh.je_source = 'Receivables' and gjh.je_category = 'Misc Receipts' and xdl.event_id = xah.event_id and xah.ae_header_id = xdl.ae_header_id and msd.event_id = xah.event_id and msd.posting_control_id <> -3 and msd.misc_cash_distribution_id = dist.source_id and dist.source_type = 'MISCCASH' and dist.code_combination_id = gjl.code_combination_id and acr.cash_receipt_id = msd.cash_receipt_id and acr.type = 'MISC' and art.receivables_trx_id = acr.receivables_trx_id --and acr.receipt_number = '100009 IONA GEDDES' unionselect distinct acr.receipt_number ,gjl.entered_cr ,gjl.entered_dr ,gjl.accounted_cr ,gjl.accounted_dr ,gjh.name journal_name ,gjh.creation_date journal_creation_date ,gcc.segment1 from gl_code_combinations gcc ,gl_je_lines gjl ,gl_je_headers gjh ,gl_je_batches glb ,gl_import_references gir ,xla_ae_lines xal ,xla_ae_headers xah ,xla_distribution_links xdl ,ar_cash_receipt_history_all arh ,ar_distributions_all dist ,ar_cash_receipts_all acr ,ar_receivables_trx_all art where gcc.code_combination_id = gjl.code_combination_id and gjh.je_header_id = gjl.je_header_id and glb.je_batch_id = gjh.je_batch_id and gjl.status = 'P' and gjh.actual_flag = 'A' and gir.je_header_id = gjh.je_header_id and gir.gl_sl_link_id = xal.gl_sl_link_id and xah.ae_header_id = xal.ae_header_id and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL' and gjh.je_source = 'Receivables' and gjh.je_category = 'Misc Receipts' and xdl.event_id = xah.event_id and xah.ae_header_id = xdl.ae_header_id and arh.event_id = xah.event_id and arh.posting_control_id <> -3 and arh.cash_receipt_history_id = dist.source_id and dist.code_combination_id = gjl.code_combination_id and dist.line_id = xdl.source_distribution_id_num_1 and acr.cash_receipt_id = arh.cash_receipt_id and acr.type = 'MISC' and art.receivables_trx_id = acr.receivables_trx_id --and acr.receipt_number = 'AG308260712NY' ;AR (Invoice) select distinct rat.trx_number ,rat.trx_date transaction_date ,gjl.entered_cr ,gjl.entered_dr ,gjl.accounted_cr ,gjl.accounted_dr ,gjh.name journal_name ,gjh.creation_date journal_creation_date ,glc.segment1,gjh.je_category from ra_cust_trx_line_gl_dist_all rac ,ra_customer_trx_all rat ,ra_batches_all rab ,ra_customer_trx_lines_all ral ,gl_code_combinations glc ,xla_distribution_links xdl ,xla_ae_headers xah ,xla_ae_lines xal ,gl_import_references gir ,gl_je_lines gjl ,gl_je_headers gjh where rat.customer_trx_id = rac.customer_trx_id and rac.customer_trx_line_id = ral.customer_trx_line_id (+) and rab.batch_id (+) = rat.batch_id and rac.posting_control_id <> -3 and xdl.source_distribution_id_num_1 = rac.cust_trx_line_gl_dist_id and xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL' and gjh.je_source = 'Receivables' and gjh.je_category = 'Sales Invoices' and xah.event_type_code <> ' MANUAL' and xah.ae_header_id = xdl.ae_header_id and xal.ae_header_id = xah.ae_header_id and xal.ae_line_num = xdl.ae_line_num and gir.gl_sl_link_id = xal.gl_sl_link_id and gjl.je_header_id = gir.je_header_id and gir.je_line_num = gjl.je_line_num and gjh.je_header_id = gjl.je_header_id and glc.code_combination_id = gjl.code_combination_id and rac.code_combination_id = gjl.code_combination_id and gjh.je_header_id = gir.je_header_id --and rat.trx_number = 'I0069600' ;AR (Invoice Adjustment) select distinct rat.trx_number ,rat.trx_date ,gjl.entered_cr ,gjl.entered_dr ,gjl.accounted_cr ,gjl.accounted_dr ,gjh.name journal_name ,gjh.creation_date journal_creation_date ,glc.segment1 from ar_adjustments_all ara ,ar_distributions_all ard ,ra_customer_trx_all rat ,ar_receivables_trx_all art ,gl_code_combinations_kfv glc ,xla_distribution_links xdl ,xla_ae_headers xah ,xla_ae_lines xal ,xla.xla_transaction_entities te ,gl_import_references gir ,gl_je_headers gjh ,gl_je_lines gjl where glc.code_combination_id = ard.code_combination_id and ara.adjustment_id = ard.source_id and rat.customer_trx_id = ara.customer_trx_id and ara.receivables_trx_id = art.receivables_trx_id and ard.source_table = 'ADJ' and ara.posting_control_id <> -3 and xdl.source_distribution_id_num_1 = ard.line_id and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL' and gjh.je_source = 'Receivables' and gjh.je_category = 'Adjustment' and xdl.event_id = xah.event_id and xah.ae_header_id = xdl.ae_header_id and xah.ae_header_id = xal.ae_header_id and xal.code_combination_id = ard.code_combination_id and xah.entity_id = te.entity_id and te.application_id = 222 and te.entity_code = 'ADJUSTMENTS' and nvl (te.source_id_int_1, (-99)) = ara.adjustment_id and gir.gl_sl_link_id = xal.gl_sl_link_id and gjh.je_header_id = gir.je_header_id and gjh.je_header_id = gjl.je_header_id and xal.code_combination_id = gjl.code_combination_id --and rat.trx_number = 'A0005418' ; Posted by at 8:13 AM Labels: General Ledger, Handy Scripts, Receivable

1 comment:

  1. UnknownDecember 6, 2016 at 1:06 PM

    Great!

    ReplyDeleteReplies
      Reply
Add commentLoad more... Newer Post Older Post Home Subscribe to: Post Comments (Atom)

Search This Blog

Labels

Accounts Payables (2) API Errors (2) API Scripts (39) Business Event (6) Discoverer (3) Fusion (1) General Ledger (4) Handy Scripts (34) HCM (3) HDL (5) HRMS (37) Interface/Form Errors (4) Java Scripting (1) Let's Play WIth SQL (5) OAF (8) Oracle Cloud (8) Oracle Developer (9) Purchasing (4) Receivable (5) Sales & Marketing (1) Setups (7) System Administrator (7) Talend (8) Unix (4) User Hooks (1) Web ADI (14) Workflow (5) XML Gateway (4) XML Reports (4)

Blog Archive

  • ▼  2013 (30)
    • ▼  December (3)
      • Subledger-Ledger Linkage (Accounts Receivable)
      • Subledger-Ledger Linkage (Accounts Payables)
      • Subledger-Ledger Linkage (Purchasing)

Viewers

Followers

Tag » Ar_cash_receipts_all And Xla_ae_headers