SELECT
gjjlv.period_name “Period Name”,
gjb.name “Batch Name”,
gjjlv.header_name “Journal Entry For”,
gjjlv.je_source “Source”,
gjjlv.je_category “Category”,
glcc.concatenated_segments “Accounts”,
nvl(gjjlv.line_entered_dr,0) “Entered Debit”,
nvl(gjjlv.line_entered_cr,0) “Entered Credit”,
nvl(gjjlv.line_accounted_dr,0) “Accounted Debit”,
nvl(gjjlv.line_accounted_cr,0) “Accounted Credit”,
gjjlv.currency_code “Currency”,
rctype.name “Trx type”,
rcta.trx_number “Trx Number”,
rcta.trx_date “Trx Date”,
ra.customer_name “Trx Reference”,
gjh.status “Posting Status”,
trunc(gjh.date_created) “GL Transfer Dt”,
gjjlv.created_by “Transfer By”
FROM
apps.gl_je_journal_lines_v gjjlv,
gl_je_lines gje,
gl_je_headers gjh,
gl_je_batches gjb,
ra_customer_trx_all rcta,
apps.ra_customers ra,
apps.gl_code_combinations_kfv glcc,
ra_cust_trx_types_all rctype
WHERE
— gjjlv.period_name =’JAN-18′
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 gjh.je_category = gjjlv.je_category
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 = rcta.trx_number (+)
AND rcta.cust_trx_type_id = rctype.cust_trx_type_id (+)
AND gje.status = ‘P’
AND gjh.set_of_books_id =:p_set_of_books_id
AND glcc.code_combination_id IN (
SELECT
code_combination_id
FROM
gl_code_combinations
WHERE
segment4 = :p_segment
)
AND gjh.posted_date BETWEEN :p_from_date AND :p_to_date
AND rcta.org_id = rctype.org_id
AND ra.customer_id = rcta.bill_to_customer_id
UNION
SELECT
gjjlv.period_name “Period Name”,
gjb.name “Batch Name”,
gjjlv.header_name “Journal Entry For”,
gjjlv.je_source “Source”,
gjjlv.je_category “Category”,
glcc.concatenated_segments “Accounts”,
nvl(gjjlv.line_entered_dr,0) “Entered Debit”,
nvl(gjjlv.line_entered_cr,0) “Entered Credit”,
nvl(gjjlv.line_accounted_dr,0) “Accounted Debit”,
nvl(gjjlv.line_accounted_cr,0) “Accounted Credit”,
gjjlv.currency_code “Currency”,
NULL “Trx type”,
NULL “Trx Number”,
NULL “Trx Date”,
NULL “Trx Reference”,
gjh.status “Posting Status”,
trunc(gjh.date_created) “GL Transfer Dt”,
gjjlv.created_by “Transfer By”
FROM
apps.gl_je_journal_lines_v gjjlv,
gl_je_lines gje,
gl_je_headers gjh,
gl_je_batches gjb,
apps.gl_code_combinations_kfv glcc
WHERE
— gjjlv.period_name =’JAN-18′
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 gjh.je_category = gjjlv.je_category
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.je_source = ‘Spreadsheet’
AND gje.status = ‘P’
AND gjh.set_of_books_id =:p_set_of_books_id
AND gjh.posted_date BETWEEN :p_from_date AND :p_to_date
AND glcc.code_combination_id IN (
SELECT
code_combination_id
FROM
gl_code_combinations
WHERE
segment4 =:p_segment
);
Recent Posts
