[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select /*+ ORDERED */
aeh.set_of_books_id sob,
aeh.period_name per,
gp.period_year pyear,
gp.period_num pnum,
gs.period_set_name perset,
ael.code_combination_id ccid,
ael.third_party_id ven,
ael.third_party_sub_id site,
ael.currency_code cur,
aeh.accounting_date accd,
ai.invoice_num num,
ael.source_id invid, -- invoice_id
ai.invoice_date idat,
'Entrada/Estorno Docto' hist,
ael.ae_line_id inst,
decode(nvl(ael.accounted_Cr,0),0,decode(nvl(ael.entered_Cr,0),0,'D','C'),'C') isign,
decode(nvl(ael.accounted_Cr,0),0,decode(nvl(ael.entered_Cr,0),0,decode(nvl(ael.accounted_Dr,0),0,ael.entered_Dr,ael.accounted_Dr),ael.entered_Cr),ael.accounted_Cr) ival,
ab.batch_name bat,
ab.batch_id batid,
ai.org_id
From ap_ae_headers aeh,
ap_ae_lines ael,
ap_invoices ai,
gl_periods gp,
gl_sets_of_books gs,
ap_batches ab
Where
-- Validate Data Conditions
aeh.ae_category = 'Purchase Invoices'
and aeh.gl_transfer_Run_id = p_transfer_run_id -- create journals entries for those invoices being transfered.
and aeh.accounting_date between p_start_date and p_end_date
and ael.ae_line_type_code ='LIABILITY'
-- Join Conditions
and aeh.ae_header_id = ael.ae_header_id
and ael.source_id = ai.invoice_id
and aeh.set_of_books_id = gs.set_of_books_id
and gs.period_set_name = gp.period_set_name
and gp.period_name = aeh.period_name
and ai.batch_id = ab.batch_id(+)
UNION ALL
-- Extract Liability debit lines
Select /*+ ORDERED */
aeh.set_of_books_id sob,
aeh.period_name per,
gp.period_year pyear,
gp.period_num pnum,
gs.period_set_name perset,
ael.code_combination_id ccid,
ael.third_party_id ven,
ael.third_party_sub_id site,
ael.currency_code cur,
aeh.accounting_date accd,
ai.invoice_num num,
ael.source_id invid,
ai.invoice_date idat,
'Pagto/Estorno Docto' hist,
ael.ae_line_id inst,
decode(nvl(accounted_Cr,0),0,decode(nvl(entered_Cr,0),0,'D','C'),'C') isign,
decode(nvl(accounted_Cr,0),0,decode(nvl(entered_Cr,0),0,decode(nvl(accounted_Dr,0),0,entered_Dr,accounted_Dr),entered_Cr),accounted_Cr) ival,
ac.checkrun_name bat,
0 batid,
ai.org_id
From ap_ae_headers aeh,
ap_ae_lines ael,
ap_invoice_payments aip,
ap_invoices ai,
ap_checks ac,
gl_periods gp,
gl_sets_of_books gs
WHERE
-- Validate Data Conditions
aeh.ae_category = 'Payments'
and aeh.gl_transfer_Run_id = p_transfer_run_id -- create journals entries for those invoices being transfered.
and aeh.accounting_date between p_start_date and p_end_date
and ael.ae_line_type_code in ('LIABILITY','GAIN','LOSS') -- gain and loss are related to payment
and ( nvl(ac.payment_method_lookup_code, 'OLD') not in ('FUTURE DATED', 'MANUAL FUTURE DATED')
OR ( nvl(ac.payment_method_lookup_code, 'OLD') in ('FUTURE DATED', 'MANUAL FUTURE DATED')
AND nvl(aip.future_pay_posted_flag, 'N') = 'N') )
-- Join Conditons
and aeh.ae_header_id = ael.ae_header_id
and ael.source_id = aip.invoice_payment_id
and aip.invoice_id = ai.invoice_id
and aip.check_id = ac.check_id
and aeh.set_of_books_id = gs.set_of_books_id
and gs.period_set_name = gp.period_set_name
and gp.period_name = aeh.period_name;
l_debug_info:='Inserting records into jl_br_journals table...';
INSERT INTO JL_BR_JOURNALS (
APPLICATION_ID ,
SET_OF_BOOKS_ID ,
PERIOD_SET_NAME ,
PERIOD_NAME ,
CODE_COMBINATION_ID ,
PERSONNEL_ID ,
TRANS_CURRENCY_CODE ,
BATCH_ID ,
BATCH_NAME ,
ACCOUNTING_DATE ,
TRANS_ID ,
TRANS_NUM ,
TRANS_DATE ,
TRANS_DESCRIPTION ,
INSTALLMENT ,
TRANS_VALUE_SIGN ,
TRANS_VALUE ,
JOURNAL_BALANCE_FLAG,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY,
ORG_ID )
VALUES (
200,
r_bmb.sob,
r_bmb.perset,
r_bmb.per,
r_bmb.ccid,
r_bmb.ven,
r_bmb.cur,
r_bmb.batid,
r_bmb.bat,
r_bmb.accd,
r_bmb.invid,
r_bmb.num,
r_bmb.idat,
r_bmb.hist,
r_bmb.inst,
r_bmb.isign,
r_bmb.ival,
'N',
sysdate,
l_user_id,
'',
sysdate,
l_user_id,
r_bmb.org_id);