Saturday, August 20, 2016

Query to get the Subledger Transfer to GL details

SELECT
    gjjlv.period_name               "Period"
  , gjb.name                        "Batch name"
  , gjjlv.header_name               "Journal Entry For"
  , gjjlv.je_source                 "Source"
  , glcc.concatenated_segments      "Accounts"
  , gjjlv.line_entered_dr           "Entered Debit"
  , gjjlv.line_entered_cr           "Entered Credit"
  , gjjlv.line_accounted_dr         "Accounted Debit"
  , gjjlv.line_accounted_cr         "Accounted Credit"
  , gjjlv.currency_code             "Currency"
  , arm.name                        "Payment Method"
  , acra.receipt_number             "Receipt Num"
  , acra.receipt_date               "Receipt Date"
  , RA.CUSTOMER_NAME                "Reference"
  , gjjlv.created_by                "Gl Transfer By"
FROM    apps.gl_je_journal_lines_v gjjlv
  , gl_je_lines gje
  , gl_je_headers gjh
  , gl_je_batches gjb
  , ar_cash_receipts_all acra
  , apps.ra_customers ra
  , apps.gl_code_combinations_kfv glcc
  , ar_receipt_methods arm
WHERE gjh.period_name IN ('OCT-2007','NOV-2007')
AND glcc.code_combination_id = gje.code_combination_id
AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
AND gjh.JE_HEADER_ID = gje.JE_HEADER_ID
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.LINE_JE_LINE_NUM  = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = acra.receipt_number   
AND ra.customer_id = acra.pay_from_customer
AND acra.receipt_method_id = arm.receipt_method_id
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_VALUE = acra.DOC_SEQUENCE_VALUE
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_id = acra.DOC_SEQUENCE_ID

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect